Wednesday, March 30, 2016

SQL-order lines exist where invoice is generated but inventory not reduced

Following script can be used to know if such order lines exist where invoice is generated but inventory not reduced:

SELECT
  (SELECT h.order_number
  FROM oe_order_headers_all h
  WHERE h.header_id = l.header_id
  ) Order_Num,
  Oe_Order_Misc_Pub.Get_Concat_Line_Number(l.line_id) Line_Num,
  l.line_id,
  l.org_id operating_unit,
  l.creation_date
FROM oe_order_lines_all l
WHERE l.open_flag = 'N'
AND l.flow_status_code = 'CLOSED'
AND l.invoice_interface_status_code = 'YES'
AND l.ordered_quantity > 0
AND l.item_type_code IN ('STANDARD', 'INCLUDED')
AND NOT EXISTS
  (SELECT 'Inventory Interface Record in MTI, to be processed later.'
  FROM mtl_transactions_interface mti
  WHERE mti.trx_source_line_id = l.line_id
  AND mti.transaction_type_id = 33
  AND mti.source_code = 'ORDER ENTRY'
  )
AND NOT EXISTS
  (SELECT 'Record of a completed material transaction for SO Issue.'
  FROM mtl_material_transactions mmt
  WHERE mmt.trx_source_line_id = l.line_id
  AND mmt.transaction_type_id = 33
  AND mmt.source_code = 'ORDER ENTRY'
  )
AND NOT EXISTS
  (SELECT 'Delivery details because this is a ship-only line.'
  FROM wsh_delivery_details wdd
  WHERE wdd.source_code = 'OE'
  AND wdd.source_line_id = l.line_id
  )
ORDER BY Order_Num ASC;

No comments: