ORACLE FUSION CLOUD ERP
ORDER-TO-CASH (O2C) DATA FLOW & TABLE RELATIONSHIP DOCUMENT
Business + Technical Reference
Contents
1. Purpose and scope
2. O2C End-to-End Data Flow
3. How to Read This Document
4. Step 1 — Sales Order
5. Step 2 — Order Fulfillment
6. Step 3 — Shipping & Delivery
7. Step 4 — AR Invoice & Accounting Distribution
8. Step 5 — Customer Receipt & Application
9. Step 6 — Subledger Accounting (SLA)
10. Step 7 — General Ledger (GL)
11. Step 8 — Reporting & Analytics
12. End-to-End Relationship Map
13. Key Join Paths / SQL Patterns
14. Supporting Master Data
15. Data Quality & Reconciliation Checks
16. Oracle Documentation References
1. Purpose and scope
This guide shows the Oracle Fusion Cloud ERP order‑to‑cash life cycle. It maps each step of Oracle Fusion Cloud ERP order‑to‑cash to the tables that hold order details, fulfillment, shipping, receivables and accounting data. For every Oracle Fusion Cloud ERP order‑to‑cash stage the guide lists the header, line and distribution/application tables that are important. It also shows the fields, primary and foreign keys how one stage connects to the next and how accounting data travels to Subledger Accounting and the General Ledger.
The purpose of this guide is to bridge business logic and technical work. A functional reader can quickly see which tables match business entities. A technical reader can use this document to walk through the data, with the IDs that the application creates during a transaction.
Release note: The Oracle documentation that this guide uses includes pages from 25C and 25D and current Oracle pages were picked when needed to confirm Oracle Fusion Cloud ERP order‑to‑cash relationships. Table structures and the objects that are available can change depending on the Fusion release the modules that are enabled and the way the system is set up. Always check the target environment before using this guide in production.
2. O2C End-to-End Data Flow
3. How to Read This Document
- Header tables show document-level records. Line tables show order and invoice detail. Fulfillment tables, shipment tables, distribution tables and application tables show execution or accounting detail.
- System-generated IDs such as HEADER_ID, LINE_ID, FULFILL_LINE_ID, DELIVERY_DETAIL_ID, CUSTOMER_TRX_ID and CASH_RECEIPT_ID are preferred for joins where direct keys exist.
- Some O2C relationships are foreign-key relationships. Others are business-reference relationships or accounting-lineage relationships that go through source references, XLA_DISTRIBUTION_LINKS or GL_IMPORT_REFERENCES.
- One-to-many relationships are normal. One order can have lines and many fulfillment lines. One shipment can contain delivery details. One invoice can have lines and many distributions. One receipt can be applied to one transaction or, to transactions.
4. Step 1 — Sales Order
Oracle Fusion Order Management stores sales-order information across header, line and fulfillment structures. The core order tables are DOO_HEADERS_ALL, DOO_LINES_ALL and DOO_FULFILL_LINES_ALL.
Core sales-order relationships
DOO_HEADERS_ALL.HEADER_ID = DOO_LINES_ALL.HEADER_ID
DOO_LINES_ALL.LINE_ID = DOO_FULFILL_LINES_ALL.LINE_ID
Business: the customer order establishes what is being sold, to whom, in what quantity and at what price. Technical: the header identifies the order, lines identify ordered products/services, and fulfillment lines represent the executable fulfillment units.
Downstream: FULFILL_LINE_ID and the source order identifiers are especially useful for tracing the order into shipping.
5. Step 2 — Order Fulfillment
Order Management orchestration transforms order lines into fulfillment lines and manages scheduling, promising, holds, fulfillment status and billing/shipping readiness. DOO_FULFILL_LINES_ALL is the central technical structure for this stage.
Fulfillment relationship chain
DOO_HEADERS_ALL.HEADER_ID = DOO_FULFILL_LINES_ALL.HEADER_ID
DOO_LINES_ALL.LINE_ID = DOO_FULFILL_LINES_ALL.LINE_ID
DOO_FULFILL_LINE_DETAILS.FULFILL_LINE_ID = DOO_FULFILL_LINES_ALL.FULFILL_LINE_ID
Business: fulfillment determines how an accepted sales order is scheduled and executed. Technical: the fulfillment line is the key bridge between the sales order and downstream shipping activities.
6. Step 3 — Shipping & Delivery
Shipping records the physical execution of the order: delivery creation, delivery-detail lines, assignment of shipment lines to deliveries, and ship-confirm status. The core structures are WSH_NEW_DELIVERIES, WSH_DELIVERY_DETAILS and WSH_DELIVERY_ASSIGNMENTS.
Shipping relationship chain
WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID = WSH_DELIVERY_ASSIGNMENTS.DELIVERY_DETAIL_ID
WSH_DELIVERY_ASSIGNMENTS.DELIVERY_ID = WSH_NEW_DELIVERIES.DELIVERY_ID
WSH_DELIVERY_DETAILS.SOURCE_SHIPMENT_ID = DOO_FULFILL_LINES_ALL.FULFILL_LINE_ID (commonly used for fulfillment-line lineage)
The SOURCE_SHIPMENT_ID relationship is an important O2C lineage pattern, especially where split or partial fulfillment exists. Oracle documentation confirms WSH_DELIVERY_DETAILS carries source-header, source-line and source-shipment identifiers; implementations should validate the exact source semantics in the target release.
7. Step 4 — AR Invoice & Accounting Distribution
Accounts Receivable stores customer invoices at header, line and accounting-distribution levels. Invoice lines can retain sales-order references, while the accounting distribution table stores the revenue/receivable accounting information that feeds Subledger Accounting.
Invoice relationship chain
RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID = RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID
RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_ID = RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID
RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_LINE_ID = RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_LINE_ID
Order-to-invoice lineage: RA_CUSTOMER_TRX_LINES_ALL contains SALES_ORDER and SALES_ORDER_LINE business references. These are useful for tracing an invoice back to the originating sales order, but they are not equivalent to a direct FK to DOO_HEADERS_ALL/DOO_LINES_ALL. Where a stronger fulfillment-level lineage is required, use the implementation’s source references and fulfillment/shipping relationships.
Business: invoicing converts fulfilled/billable activity into a customer receivable. Technical: the invoice header, line and GL-distribution layers provide the AR transaction and accounting foundation.
Get Nexlify Technology’s stories in your inbox
Join Medium for free to get updates from this writer.
8. Step 5 — Customer Receipt & Application
Customer cash is recorded in AR_CASH_RECEIPTS_ALL. Application rows in AR_RECEIVABLE_APPLICATIONS_ALL associate receipt amounts with customer transactions, while AR_PAYMENT_SCHEDULES_ALL provides the invoice/installment balance context.
Receipt-to-invoice relationship
AR_RECEIVABLE_APPLICATIONS_ALL.CASH_RECEIPT_ID = AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID
AR_RECEIVABLE_APPLICATIONS_ALL.APPLIED_CUSTOMER_TRX_ID = RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID
AR_RECEIVABLE_APPLICATIONS_ALL.APPLIED_PAYMENT_SCHEDULE_ID = AR_PAYMENT_SCHEDULES_ALL.PAYMENT_SCHEDULE_ID
Business: cash receipt represents money received from the customer; application represents how that cash is allocated to an invoice or other receivable. Technical: CASH_RECEIPT_ID and APPLIED_CUSTOMER_TRX_ID are the core tracing keys.
9. Step 6 — Subledger Accounting (SLA)
Subledger Accounting translates AR and other O2C source transactions into accounting entries. The accounting lineage can be followed from source distributions/events into XLA accounting entries and then toward General Ledger
O2C source distribution
↓
XLA_DISTRIBUTION_LINKS
↓ AE_HEADER_ID + AE_LINE_NUM + APPLICATION_ID
XLA_AE_HEADERS → XLA_AE_LINES
↓ GL_SL_LINK_ID + GL_SL_LINK_TABLE
GL_IMPORT_REFERENCES
SLA is not a one-to-one copy of AR transaction rows. Accounting is generated from accounting events and source distributions; the exact source-distribution mapping depends on the subledger transaction and event.
10. Step 7 — General Ledger (GL)
General Ledger receives/imports accounting information from subledgers and stores journal batches, headers, lines and subledger references.
GL_JE_BATCHES.JE_BATCH_ID = GL_JE_HEADERS.JE_BATCH_ID
GL_JE_HEADERS.JE_HEADER_ID = GL_JE_LINES.JE_HEADER_ID
GL_JE_LINES.CODE_COMBINATION_ID = GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
GL_IMPORT_REFERENCES.JE_HEADER_ID + JE_LINE_NUM = GL_JE_LINES.JE_HEADER_ID + JE_LINE_NUM
11. Step 8 — Reporting & Analytics
Reporting is a layer rather than one transactional table. The reporting model combines order management, fulfillment, shipping, receivables, receipts, SLA and GL information according to the reporting requirement.
12. End-to-End Relationship Map
This is a conceptual lineage map. A real O2C transaction can branch, split, be partially shipped, partially invoiced, credited, adjusted, reversed or applied across multiple invoices. Therefore, downstream rows are not necessarily one-to-one with upstream rows.
13. Key Join Paths / SQL Patterns
These are relationship templates for development and analysis, not production-ready queries. Add appropriate business-unit, ledger, date, status and security predicates.
Sales order header → line → fulfillment line
SELECT h.order_number, l.line_id, f.fulfill_line_id, f.status_code
FROM doo_headers_all h
JOIN doo_lines_all l
ON l.header_id = h.header_id
JOIN doo_fulfill_lines_all f
ON f.line_id = l.line_id;
Fulfillment → shipping
SELECT h.order_number, f.fulfill_line_id, wdd.delivery_detail_id,
wdd.source_shipment_id, wda.delivery_id
FROM doo_headers_all h
JOIN doo_fulfill_lines_all f
ON f.header_id = h.header_id
JOIN wsh_delivery_details wdd
ON wdd.source_shipment_id = f.fulfill_line_id
JOIN wsh_delivery_assignments wda
ON wda.delivery_detail_id = wdd.delivery_detail_id;
Invoice → order reference
SELECT rct.trx_number, rctl.customer_trx_line_id,
rctl.sales_order, rctl.sales_order_line
FROM ra_customer_trx_all rct
JOIN ra_customer_trx_lines_all rctl
ON rctl.customer_trx_id = rct.customer_trx_id;
Receipt → invoice
SELECT rct.trx_number, acr.receipt_number,
ara.receivable_application_id, ara.amount_applied
FROM ar_cash_receipts_all acr
JOIN ar_receivable_applications_all ara
ON ara.cash_receipt_id = acr.cash_receipt_id
JOIN ra_customer_trx_all rct
ON rct.customer_trx_id = ara.applied_customer_trx_id;
GL journal → import reference
SELECT gjh.je_header_id, gjl.je_line_num,
gjl.code_combination_id, gir.gl_sl_link_id, gir.gl_sl_link_table
FROM gl_je_headers gjh
JOIN gl_je_lines gjl
ON gjl.je_header_id = gjh.je_header_id
LEFT JOIN gl_import_references gir
ON gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num;
14. Supporting Master Data
15. Data Quality & Reconciliation Checks
16. Oracle Documentation References
The following Oracle documentation pages were used to verify the table names, primary keys and documented relationships included in this O2C reference.
- DOO_HEADERS_ALL — https://docs.oracle.com/en/cloud/saas/supply-chain-and-manufacturing/25c/oedsc/dooheadersall-13106.html
- DOO_LINES_ALL — https://docs.oracle.com/cd/G11809_01/trans/F88792-02/doolinesall-11110.html
- DOO_FULFILL_LINES_ALL — https://docs.oracle.com/en/cloud/saas/supply-chain-and-manufacturing/25c/oedsc/doofulfilllinesall-30784.html
- DOO_FULFILL_LINE_DETAILS — https://docs.oracle.com/en/cloud/saas/supply-chain-and-manufacturing/25c/oedsc/doofulfilllinedetails-10237.html
- WSH_NEW_DELIVERIES — https://docs.oracle.com/en/cloud/saas/supply-chain-and-manufacturing/26b/oedsc/wshnewdeliveries-17424.html
- WSH_DELIVERY_DETAILS — https://docs.oracle.com/en/cloud/saas/supply-chain-and-manufacturing/25c/oedsc/wshdeliverydetails-15087.html
- WSH_DELIVERY_ASSIGNMENTS — https://docs.oracle.com/en/cloud/saas/supply-chain-and-manufacturing/25c/oedsc/wshdeliveryassignments-20270.html
- RA_CUSTOMER_TRX_ALL — https://docs.oracle.com/en/cloud/saas/financials/25d/oedmf/racustomertrxall-5360.html
- RA_CUSTOMER_TRX_LINES_ALL — https://docs.oracle.com/en/cloud/saas/financials/25d/oedmf/racustomertrxlinesall-28421.html
- RA_CUST_TRX_LINE_GL_DIST_ALL — https://docs.oracle.com/en/cloud/saas/financials/25d/oedmf/racusttrxlinegldistall-11073.html
- AR_CASH_RECEIPTS_ALL — https://docs.oracle.com/en/cloud/saas/financials/25d/oedmf/arcashreceiptsall-24527.html
- AR_RECEIVABLE_APPLICATIONS_ALL — https://docs.oracle.com/en/cloud/saas/financials/25d/oedmf/arreceivableapplicationsall-31637.html
- AR_DISTRIBUTIONS_ALL — https://docs.oracle.com/en/cloud/saas/financials/25d/oedmf/ardistributionsall-19072.html
- XLA_EVENTS — https://docs.oracle.com/en/cloud/saas/financials/25c/oedmf/xlaevents-18440.html
- XLA_AE_LINES — https://docs.oracle.com/en/cloud/saas/financials/25d/oedmf/xlaaelines-17865.html
- XLA_DISTRIBUTION_LINKS — https://docs.oracle.com/en/cloud/saas/financials/25d/oedmf/xladistributionlinks-18526.html
- GL_JE_BATCHES — https://docs.oracle.com/en/cloud/saas/financials/25d/oedmf/gljebatches-13476.html
- GL_JE_HEADERS — https://docs.oracle.com/en/cloud/saas/financials/25d/oedmf/gljeheaders-6572.html
- GL_JE_LINES — https://docs.oracle.com/en/cloud/saas/financials/25d/oedmf/gljelines-25162.html
- GL_IMPORT_REFERENCES — https://docs.oracle.com/en/cloud/saas/financials/25d/oedmf/glimportreferences-18938.html
- GL_CODE_COMBINATIONS — https://docs.oracle.com/en/cloud/saas/financials/25d/oedmf/glcodecombinations-22130.html
