Oracle EBS Order-to-Cash SQL Generator: Sales Order to Delivery, Invoice and Receipt

-- Oracle EBS R12: Order-to-Cash trace for operating unit 204
-- One row per order line x delivery detail x invoice line x payment schedule x receipt application
SELECT h.order_number,
       l.line_number || '.' || l.shipment_number         AS line_no,
       l.ordered_item,
       l.ordered_quantity,
       l.flow_status_code                                 AS line_status,
       l.invoice_interface_status_code,
       p.party_name                                       AS customer,
       ca.account_number,
       wdd.delivery_detail_id,
       wdd.released_status,
       wnd.name                                           AS delivery_name,
       rct.trx_number,
       rct.trx_date,
       ps.amount_due_original,
       ps.amount_due_remaining,
       cr.receipt_number,
       ra.amount_applied
FROM   oe_order_headers_all h
JOIN   oe_order_lines_all l
       ON l.header_id = h.header_id
JOIN   hz_cust_accounts ca
       ON ca.cust_account_id = h.sold_to_org_id
JOIN   hz_parties p
       ON p.party_id = ca.party_id
LEFT JOIN wsh_delivery_details wdd
       ON wdd.source_line_id = l.line_id
      AND wdd.source_code = 'OE'
LEFT JOIN wsh_delivery_assignments wda
       ON wda.delivery_detail_id = wdd.delivery_detail_id
LEFT JOIN wsh_new_deliveries wnd
       ON wnd.delivery_id = wda.delivery_id
LEFT JOIN ra_customer_trx_lines_all rctl
       ON rctl.interface_line_context = 'ORDER ENTRY'
      AND rctl.interface_line_attribute6 = TO_CHAR(l.line_id)
      AND rctl.line_type = 'LINE'
LEFT JOIN ra_customer_trx_all rct
       ON rct.customer_trx_id = rctl.customer_trx_id
LEFT JOIN ar_payment_schedules_all ps
       ON ps.customer_trx_id = rct.customer_trx_id
LEFT JOIN ar_receivable_applications_all ra
       ON ra.applied_customer_trx_id = rct.customer_trx_id
      AND ra.status = 'APP'
      AND ra.display = 'Y'
LEFT JOIN ar_cash_receipts_all cr
       ON cr.cash_receipt_id = ra.cash_receipt_id
WHERE  h.org_id = 204
AND    h.order_number = NVL(TO_NUMBER('[Order number (optional)]'), h.order_number)
AND    UPPER(p.party_name) LIKE UPPER('%')
AND    h.ordered_date >= TO_DATE('2026-09-01', 'YYYY-MM-DD')
AND    h.ordered_date <  TO_DATE('2026-09-30', 'YYYY-MM-DD') + 1
ORDER  BY h.order_number, l.line_number, l.shipment_number, rct.trx_number;

-- Rows still in RA_INTERFACE_LINES_ALL were interfaced by OM but not yet imported by AutoInvoice
SELECT ril.interface_line_id,
       ril.interface_line_attribute1   AS order_number,
       ril.interface_line_attribute6   AS oe_line_id,
       ril.batch_source_name,
       ril.request_id,
       ril.interface_status,
       rie.message_text,
       rie.invalid_value
FROM   ra_interface_lines_all ril
LEFT JOIN ra_interface_errors_all rie
       ON rie.interface_line_id = ril.interface_line_id
WHERE  ril.interface_line_context = 'ORDER ENTRY'
AND    ril.org_id = 204
AND    ril.interface_line_attribute1 = NVL('[Order number (optional)]', ril.interface_line_attribute1)
ORDER  BY ril.interface_line_attribute1, ril.interface_line_id;