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

> Source: https://netray.co/tools/oracle-ebs-order-to-cash-o2c-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Build a query that follows each Order Management line through Shipping, Receivables invoicing and cash application in Oracle EBS R12.

## Category

oracle

## Erp

Oracle E-Business Suite

## Audience

- Oracle EBS developer
- OM functional consultant
- AR analyst
- ERP support analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Operating unit ORG_ID

**Key:** org_id

**Type:** number

**Default:** 204

**Help:** OE_ORDER_HEADERS_ALL.ORG_ID of the operating unit.

#### Order number (optional)

**Key:** order_number

**Type:** text

**Placeholder:** e.g. 69321

**Help:** Leave blank to return all orders in the date range.

#### Customer name LIKE

**Key:** customer_like

**Type:** text

**Default:** %

**Help:** Matched case-insensitively against HZ_PARTIES.PARTY_NAME, use % as wildcard.

#### Ordered date from

**Key:** date_from

**Type:** date

**Default:** 2026-09-01

#### Ordered date to

**Key:** date_to

**Type:** date

**Default:** 2026-09-30

#### Line transaction flexfield context

**Key:** context

**Type:** text

**Default:** ORDER ENTRY

**Help:** The value OM writes to INTERFACE_LINE_CONTEXT, controlled by the profile OM: Source Code. ORDER ENTRY is the seeded value.

**Blocks:**

#### 1. Order line to delivery, invoice and receipt

**Body:** -- Oracle EBS R12: Order-to-Cash trace for operating unit {{org_id}}
-- 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 = '{{context}}'
      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 = {{org_id}}
AND    h.order_number = NVL(TO_NUMBER('{{order_number}}'), h.order_number)
AND    UPPER(p.party_name) LIKE UPPER('{{customer_like}}')
AND    h.ordered_date >= TO_DATE('{{date_from}}', 'YYYY-MM-DD')
AND    h.ordered_date <  TO_DATE('{{date_to}}', 'YYYY-MM-DD') + 1
ORDER  BY h.order_number, l.line_number, l.shipment_number, rct.trx_number;

#### 2. Lines shipped but not invoiced: check the AutoInvoice interface

**Body:** -- 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 = '{{context}}'
AND    ril.org_id = {{org_id}}
AND    ril.interface_line_attribute1 = NVL('{{order_number}}', ril.interface_line_attribute1)
ORDER  BY ril.interface_line_attribute1, ril.interface_line_id;

**Notes:** Built from the standard R12 table relationships. Verify on your instance: the INTERFACE_LINE_CONTEXT value (profile OM: Source Code), that ATTRIBUTE1 holds the order number and ATTRIBUTE6 the OE line_id for that context, and the columns in your release. Rows multiply when a line has several deliveries, installments or receipt applications; aggregate before summing amounts. Run in a reporting or cloned instance for large date ranges.

## Content

**Intro:** This generator writes a single SQL statement that follows each Oracle EBS R12 sales order line from Order Management through Shipping Execution, Receivables invoicing and cash application. A second query shows lines that are sitting in the AutoInvoice interface instead of on an invoice.

**Sections:**

#### How to use it

**Body:** Enter the operating unit ORG_ID, an optional order number, a customer name pattern and the ordered date range, then copy the SQL into SQL Developer or TOAD connected as APPS (or a read-only reporting schema with grants and synonyms). Because the joins to shipping, invoicing and receipts are LEFT JOINs, every order line is returned even when it has not shipped or invoiced yet, so the null columns tell you where the line stopped. Read a row left to right: FLOW_STATUS_CODE and RELEASED_STATUS show the order and shipping stage, TRX_NUMBER appears once AutoInvoice has created the invoice, AMOUNT_DUE_REMAINING drops as cash is applied, and RECEIPT_NUMBER identifies the receipt.

**Bullets:**

- RELEASED_STATUS: R ready to release, S released to warehouse, Y staged, C shipped, B backordered, D cancelled.
- INVOICE_INTERFACE_STATUS_CODE YES means OM has passed the line to Receivables.
- Blank TRX_NUMBER with a shipped line: run query 2.

#### Where the links live in Oracle EBS

**Body:** OE_ORDER_HEADERS_ALL.SOLD_TO_ORG_ID points to HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID, and the account points to HZ_PARTIES for the customer name. Shipping creates WSH_DELIVERY_DETAILS rows with SOURCE_CODE 'OE' and SOURCE_LINE_ID equal to the order line_id; WSH_DELIVERY_ASSIGNMENTS ties each detail to a WSH_NEW_DELIVERIES delivery. When the line workflow reaches the Invoice Interface activity, OM inserts into RA_INTERFACE_LINES_ALL using the line transaction flexfield: context ORDER ENTRY by default, ATTRIBUTE1 the order number, ATTRIBUTE6 the line_id. AutoInvoice copies those attributes onto RA_CUSTOMER_TRX_LINES_ALL, which is why the invoice join uses INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(line_id). AR_PAYMENT_SCHEDULES_ALL holds the open balance and AR_RECEIVABLE_APPLICATIONS_ALL (STATUS 'APP') links the invoice to AR_CASH_RECEIPTS_ALL.

#### Why a shipped line has no invoice

**Body:** After ship confirm and Interface Trip Stop, the OEOL line workflow usually defers the invoice interface step, so nothing reaches Receivables until the Workflow Background Process runs for item type OEOL with deferred activities. Then AutoInvoice Master Program must run for the OM transaction source to turn the interface rows into invoices. If query 2 returns rows with messages in RA_INTERFACE_ERRORS_ALL, AutoInvoice rejected them: common causes are a closed GL period, a missing revenue or receivable account from AutoAccounting, an invalid or inactive bill-to site, or a missing transaction type on the order type. Fix the cause and rerun AutoInvoice. If there are no interface rows and the line status is still SHIPPED or AWAITING_FULFILLMENT, look at the line workflow in Workflow Status Monitor instead.

#### Gotchas and version notes

**Body:** Each join can multiply rows: split shipments, partial backorders, invoices with several installments and receipts applied in pieces all add rows, so aggregate amounts at the right grain before summing. The DISPLAY = 'Y' filter keeps only the current application row and hides reversed pairs. Credit memos created from RMAs also come through the same context with the RMA line_id, and freight lines carry LINE_TYPE 'FREIGHT', which the filter excludes. Some sites change OM: Source Code or use a custom transaction source, so confirm the context name in the Line Transaction Flexfield before trusting empty invoice columns. The query is the same for 12.1 and 12.2.

**Cta:** If your order desk wants to ask where an order is in plain English and get this trace back from EBS on your own servers, we can set that up.

**Faq:**

#### What is the order to cash process flow in Oracle EBS?

**Answer:** Enter and book the sales order in Order Management, pick release and ship confirm in Shipping Execution, run Interface Trip Stop, let the line workflow and Workflow Background Process interface the line to Receivables, run AutoInvoice to create the invoice, then enter or import a receipt and apply it. The tables follow the same path: OE, WSH, RA, AR_PAYMENT_SCHEDULES, AR_RECEIVABLE_APPLICATIONS and AR_CASH_RECEIPTS.

#### How do I join a sales order line to its AR invoice line?

**Answer:** Join RA_CUSTOMER_TRX_LINES_ALL on INTERFACE_LINE_CONTEXT = 'ORDER ENTRY' (or your OM: Source Code value) and INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OE_ORDER_LINES_ALL.LINE_ID). INTERFACE_LINE_ATTRIBUTE1 holds the order number if you only need the header. Keep the context filter or you can match lines from other sources that use attribute6 differently.

#### Which table stores sales orders in Oracle EBS?

**Answer:** Headers are in OE_ORDER_HEADERS_ALL and lines in OE_ORDER_LINES_ALL, both striped by ORG_ID. Price adjustments are in OE_PRICE_ADJUSTMENTS and holds in OE_ORDER_HOLDS_ALL. The order number is ORDER_NUMBER on the header, and the user-visible line number is LINE_NUMBER plus SHIPMENT_NUMBER, shown as 1.1, 1.2 and so on.

#### Why does my O2C query return duplicate rows?

**Answer:** Each one-to-many step multiplies rows: one order line can have several delivery details after a split or backorder, one invoice can have several payment schedules, and one invoice can be paid by several receipts. Either report at that grain, or aggregate in an inline view (for example SUM(amount_applied) per customer_trx_id) before joining back to the order line.

## Search terms

oracle ebs order to cash process flow, oracle ebs order to cash query, sales order in oracle ebs, oracle ebs om, oracle e business suite order management, oe_order_lines_all ra_customer_trx_lines_all join, interface_line_attribute6 line_id, o2c query oracle apps r12

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json