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:
Post a Comment