Oracle Apps R12 Sub ledger Accounting Tables and joins

Oracle Apps R12 Sub ledger Accounting Tables and joins

XLA Table joins
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 (je_header_id,  je_line_num)        => GL_IMPORT_REFERENCES (je_header_id, je_line_num)
GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id)        => XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)
XLA_AE_LINES (application_id, ae_header_id)  => XLA_AE_HEADERS (application_id, ae_header_id)
XLA_AE_HEADERS (application_id, event_id)   => XLA_EVENTS (application_id, event_id)
XLA_EVENTS (application_id, entity_id)            => XLA.XLA_TRANSACTION_ENTITIES (application_id, entity_id)

Transaction Entity Codes and ids
xte.entity_code =
‘TRANSACTIONS’ or
‘RECEIPTS’ or
‘ADJUSTMENTS’ or
‘PURCHASE_ORDER’ or
‘AP_INVOICES’ or
‘AP_PAYMENTS’ or
‘MTL_ACCOUNTING_EVENTS’ or
‘WIP_ACCOUNTING_EVENTS’

xte.source_id_int_1 =
‘INVOICE_ID’ or
‘CHECK_ID’ or
‘CUSTOMER_TRX_ID’ or
‘TRANSACTION_ID’

XLA_EVENTS:
SELECT *  FROM xla_events xe
WHERE xe.application_id = 222
AND xe.entity_id IN (
SELECT xte.entity_id
FROM xla.xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = ‘TRANSACTIONS’
AND xte.source_id_int_1 = 10066)

XLA_AE_HEADERS:
SELECT *  FROM xla_ae_headers xah  WHERE xah.application_id = 222
AND xah.entity_id IN (
SELECT xte.entity_id
FROM xla.xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = ‘TRANSACTIONS’
AND xte.source_id_int_1 = 10066)

XLA_AE_LINES:
SELECT xal.*  FROM xla_ae_lines xal, xla_ae_headers xah  WHERE xal.application_id = xah.application_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.application_id = 222
AND xah.entity_id IN (
SELECT xte.entity_id
FROM xla.xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = ‘TRANSACTIONS’
AND xte.source_id_int_1 = 10066)

XLA_DISTRIBUTION_LINES:
SELECT xdl.*  FROM xla_distribution_links xdl, xla_ae_headers xah  WHERE xdl.application_id = xah.application_id
AND xdl.ae_header_id = xah.ae_header_id
AND xah.application_id = 222
AND xah.entity_id IN (
SELECT xte.entity_id
FROM xla.xla_transaction_entities xte
WHERE xte.application_id = 222
AND xte.entity_code = ‘TRANSACTIONS’
AND xte.source_id_int_1 = 10066)

XLA_AE_HEADER status Columns
Table stores important status Information
GL Transfer Status Code (GL_TRANSFER_STATUS_CODE)
The GL transfer status code of the sub-ledger journal entry has one of the following values:
Not transferred
Selected to transfer
Transferred
When a sub-ledger journal entry is created and completed, the value of this column is Not transferred. The Transfer to GL process updates this column when the subledger journal entry is transferred to General Ledger.

Subledger Journal Entry Status Code (ACCOUNTING_ENTRY_STATUS_CODE)
A subledger journal entry can have a status of Draft, Final, Incomplete, Invalid or Invalid Related Entry.
Draft: The entry status is set to Draft when the following conditions are met:
Final: The entry status is set to Final when the following conditions are met:
Incomplete: The entry status is set to Incomplete when the following condition is met:
Invalid Related Entry: The entry status is set to Invalid Related Entry when the following condition is met:

The subledger journal entry is valid, but one or more of the other subledger journal entries associated with the same accounting event are invalid.

XLA_DISTRIBUTION_LINKS table join based on Source Distribution Types
xdl.source_distribution_type = ‘AP_PMT_DIST ‘
and xdl.source_distribution_id_num_1 = AP_PAYMENT_HIST_DISTS.payment_hist_dist_id
—————
xdl.source_distribution_type = ‘AP_INV_DIST’
and xdl.source_distribution_id_num_1 = AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id
—————
xdl.source_distribution_type = ‘AR_DISTRIBUTIONS_ALL’
and xdl.source_distribution_id_num_1 = AR_DISTRIBUTIONS_ALL.line_id
and AR_DISTRIBUTIONS_ALL.source_id = AR_RECEIVABLE_APPLICATIONS_ALL.receivable_application_id
—————
xdl.source_distribution_type = ‘RA_CUST_TRX_LINE_GL_DIST_ALL’
and xdl.source_distribution_id_num_1 = RA_CUST_TRX_LINE_GL_DIST_ALL.cust_trx_line_gl_dist_id
—————
xdl.source_distribution_type = ‘MTL_TRANSACTION_ACCOUNTS’
and xdl.source_distribution_id_num_1 = MTL_TRANSACTION_ACCOUNTS.inv_sub_ledger_id
—————
xdl.source_distribution_type = ‘WIP_TRANSACTION_ACCOUNTS’
and xdl.source_distribution_id_num_1 = WIP_TRANSACTION_ACCOUNTS.wip_sub_ledger_id
—————
xdl.source_distribution_type = ‘RCV_RECEIVING_SUB_LEDGER’
and xdl.source_distribution_id_num_1 = RCV_RECEIVING_SUB_LEDGER.rcv_sub_ledger_id