Useful Month End Process Queries – Operations

1. Transaction Struck in Material Transactions Open Interface:
Transaction in Material Transaction Open Interface
   
SELECT COUNT(1), mp.organization_code
  FROM mtl_transactions_interface_v mtiv,
       mtl_parameters mp
 WHERE 1 = 1
   AND mtiv.organization_id=mp.organization_id
   AND (process_flag = 3 or lock_flag = 1)
 GROUP BY mp.organization_code;

2. Pending Material Transactions:
Pending Material Transactions
   
SELECT COUNT(1), mp.organization_code
  FROM mtl_transactions_temp_all_v mtta,
       mtl_parameters mp
 WHERE 1 = 1
   AND mtta.organization_id = mp.organization_id
 GROUP BY mp.organization_code;

3. Pending Move Transactions(WIP):
Pending Move Transactions WIP

   
SELECT COUNT(1), mp.organization_code
  FROM wip_move_txn_interface_v wmt,
       mtl_parameters mp
 WHERE wmt.organization_id = mp.organization_id
 GROUP BY mp.organization_code;

4. Pending WIP uncosted:
Pending WIP Uncosted

   
SELECT COUNT(1), mp.organization_code
  FROM wip_cost_txn_interface_v wct,
       mtl_parameters mp
 WHERE wct.organization_id = mp.organization_id
 GROUP BY mp.organization_code;

5. Error Costed Transactions:
Error Costed Transactions
   
SELECT COUNT(1), mp.organization_code
  FROM mtl_material_transactions mmt,
       mtl_parameters mp
 WHERE mmt.organization_id=mp.organization_id
   AND costed_flag = 'E'
 GROUP BY mp.organization_code;

6. Not Costed Transactions:
Not Costed Transactions

   
SELECT COUNT(1), mp.organization_code
  FROM mtl_material_transactions mmt,
       mtl_parameters mp
 WHERE mmt.organization_id = mp.organization_id
   AND costed_flag = 'N'
 GROUP BY mp.organization_code;

7. Pending Receiving Transactions:
Pending Receiving Transactions
   
SELECT COUNT(1), mp.organization_code
  FROM rcv_transactions_interface rti,
       mtl_parameters mp
 WHERE rti.to_organization_id = mp.organization_id
   AND rti.processing_status_code = 'PENDING'
 GROUP BY mp.organization_code;

8: unprocessed shipping transactions:
unprocessed shipping transactions
   
SELECT COUNT(1), mp.organization_code
  FROM wsh_delivery_details     wdd
      ,oe_order_headers_all     ooh
      ,oe_order_lines_all       ool
      ,wsh_delivery_assignments wda
      ,wsh_new_deliveries       wnd
      ,mtl_parameters           mp
 WHERE wdd.source_header_id = ooh.header_id
   AND wdd.source_line_id = ool.line_id
   AND ooh.header_id = ool.header_id
   AND wdd.delivery_detail_id = wda.delivery_detail_id
   AND wda.delivery_id = wnd.delivery_id
   AND wdd.source_code = 'OE'
   AND wdd.released_status = 'C'
   AND wdd.inv_interfaced_flag IN ('N', 'P')
   AND wnd.status_code IN ('CL', 'IT')
   AND wdd.organization_id =mp.organization_id
 GROUP BY mp.organization_code;

9. Pending or Error Transactions:
Pending or Error transactions
   
SELECT transaction_id
      ,consumption_processed_flag
      ,ERROR_CODE
      ,net_qty
      ,batch_id
      ,creation_date
      ,consumption_release_id
      ,consumption_po_header_id
 FROM mtl_consumption_transactions
WHERE consumption_processed_flag IN ('E', 'N');

10. Unprocessed Shipping Transactions:
Unprocessed shipping Transactions
   
SELECT b.organization_code,
       b.organization_id  ,
       b.operating_unit   ,
       count(*)
  FROM wsh_delivery_details     wdd
      ,oe_order_headers_all     ooh
      ,oe_order_lines_all       ool
      ,wsh_delivery_assignments wda
      ,wsh_new_deliveries       wnd
      ,wsh_delivery_legs        wdl
      ,wsh_trip_stops           wts
      ,org_organization_definitions b
 WHERE wdd.source_code = 'OE'
   AND wdd.released_status = 'C'
   AND wdd.inv_interfaced_flag IN ('N', 'P')
   AND wdd.organization_id = b.organization_id
   AND wdd.delivery_detail_id = wda.delivery_detail_id
   AND wda.delivery_id = wnd.delivery_id
   AND wnd.status_code IN ('CL', 'IT')
   AND wdl.delivery_id         = wnd.delivery_id
   AND wdl.pick_up_stop_id     = wts.stop_id
 GROUP BY b.organization_code,
          b.organization_id  ,
          b.operating_unit;