--OM: Sales Order not going Material Transactions
SELECT hdr.order_number,line.line_id, line.org_id, line.shippable_flag,hdr.order_type_id
,hdr.ordered_date,hdr.booked_date, hdr.cancelled_flag,hdr.booked_flag,hdr.open_flag,hdr.flow_status_code
FROM oe_workflow_assignments wf_assign,
oe_order_lines_all line,
oe_order_headers_all hdr
WHERE process_name = 'R_BILL_ONLY_INV_INTERFACE'
AND line.line_type_id = wf_assign.line_type_id
AND line.header_id = hdr.header_id
AND hdr.order_type_id = wf_assign.order_type_id
AND line.open_flag = 'N'
AND line.flow_status_code <> 'CANCELLED'
AND NVL (line.cancelled_flag, 'N') <> 'Y'
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions
WHERE trx_source_line_id = line.line_id AND transaction_source_type_id = 2)
AND NOT EXISTS (SELECT 1
FROM mtl_transactions_interface
WHERE trx_source_line_id = line.line_id AND transaction_source_type_id = 2)
Order by ORDERED_DATE