Join AR Invoice to Subledger Accounting (SLA)
Connects an AR transaction (RA_CUSTOMER_TRX_ALL) through XLA_TRANSACTION_ENTITIES — the pivot that identifies the source document — and the accounting event (XLA_EVENTS) to the Subledger Accounting entry (XLA_AE_HEADERS, XLA_AE_LINES).
Sample join
SELECT trx.customer_trx_id, trx.trx_number, te.entity_code, ev.event_id, ah.ae_header_id, ah.accounting_entry_status_code, al.ae_line_num, al.accounted_dr, al.accounted_cr FROM ra_customer_trx_all trx JOIN xla_transaction_entities te ON te.source_id_int_1 = trx.customer_trx_id AND te.application_id = 222 AND te.entity_code = 'TRANSACTIONS' JOIN xla_events ev ON ev.entity_id = te.entity_id AND ev.application_id = te.application_id JOIN xla_ae_headers ah ON ah.event_id = ev.event_id AND ah.entity_id = te.entity_id AND ah.application_id = te.application_id JOIN xla_ae_lines al ON al.ae_header_id = ah.ae_header_id WHERE trx.trx_number = :p_trx_number;
Key tables
RA_CUSTOMER_TRX_ALL
CuratedAR transaction header representing the invoice, credit memo, or debit memo.
Key columns
Common joins
XLA_TRANSACTION_ENTITIES via CUSTOMER_TRX_ID = SOURCE_ID_INT_1
The first hop into Subledger Accounting — SOURCE_ID_INT_1 holds the transaction's key, with ENTITY_CODE = 'TRANSACTIONS' and APPLICATION_ID = 222.
XLA_TRANSACTION_ENTITIES
CuratedThe Subledger Accounting pivot — one row per source document. Every accounting entry resolves back to its originating transaction through this table. Never skip it.
Key columns
Common joins
XLA_EVENTS via ENTITY_ID
The accounting event(s) raised for this entity once Create Accounting runs.
XLA_EVENTS
CuratedAccounting event generated by a subledger transaction, before the accounting entry itself is created.
Key columns
Common joins
XLA_TRANSACTION_ENTITIES via ENTITY_ID
The source-document pivot this event belongs to — resolves the event back to the AR/AP/FA transaction.
XLA_AE_HEADERS via EVENT_ID
The accounting entry header actually generated from this event once Create Accounting runs.
XLA_AE_HEADERS
CuratedSubledger accounting entry header grouping the accounting lines for an event.
Key columns
Common joins
XLA_EVENTS via EVENT_ID
The accounting event this entry was generated from.
XLA_AE_LINES via AE_HEADER_ID
The individual debit/credit lines that make up this accounting entry.
XLA_AE_LINES
CuratedSubledger accounting entry lines — the debit/credit detail.
Key columns
Common joins
XLA_AE_HEADERS via AE_HEADER_ID
The accounting entry header this line belongs to.
GL_CODE_COMBINATIONS via CODE_COMBINATION_ID
The GL account (segment combination) this debit/credit line posted to.
Key columns
| Table | Key columns |
|---|---|
| RA_CUSTOMER_TRX_ALL | CUSTOMER_TRX_IDTRX_NUMBERBILL_TO_CUSTOMER_IDTRX_DATE |
| XLA_TRANSACTION_ENTITIES | ENTITY_IDAPPLICATION_IDLEDGER_IDENTITY_CODESOURCE_ID_INT_1SOURCE_ID_CHAR_1 |
| XLA_EVENTS | EVENT_IDENTITY_IDEVENT_TYPE_CODEEVENT_DATEEVENT_STATUS_CODEPROCESS_STATUS_CODEEVENT_NUMBER |
| XLA_AE_HEADERS | AE_HEADER_IDAPPLICATION_IDEVENT_IDLEDGER_IDACCOUNTING_DATE |
| XLA_AE_LINES | AE_HEADER_IDAE_LINE_NUMCODE_COMBINATION_IDACCOUNTED_DRACCOUNTED_CR |
Relationships
- RA_CUSTOMER_TRX_ALLAR transaction header representing the invoice, credit memo, or debit memo.CUSTOMER_TRX_ID = SOURCE_ID_INT_1
- XLA_TRANSACTION_ENTITIESThe Subledger Accounting pivotENTITY_ID
- XLA_EVENTSAccounting event generated by a subledger transaction, before the accounting entry itself is created.EVENT_ID
- XLA_AE_HEADERSSubledger accounting entry header grouping the accounting lines for an event.AE_HEADER_ID
- XLA_AE_LINESSubledger accounting entry lines
Notes
XLA_TRANSACTION_ENTITIES is the pivot — never skip it; it is what ties an accounting entry back to its source document. APPLICATION_ID identifies the subledger (AP=200, AR=222, FA=140, Cost Management=707) and ENTITY_CODE the document type ('TRANSACTIONS' for AR invoices, 'AP_INVOICES' for AP). XLA_ is Oracle's shared Subledger Accounting engine — this exact chain applies to every subledger; only APPLICATION_ID and ENTITY_CODE change. To continue to the posted GL journal: XLA_AE_LINES.GL_SL_LINK_ID + GL_SL_LINK_TABLE -> GL_IMPORT_REFERENCES -> GL_JE_LINES (JE_HEADER_ID + JE_LINE_NUM) -> GL_JE_HEADERS -> GL_JE_BATCHES.
Related questions
Model-generated, grounded against the curated knowledge layer. Check specifics against your instance.