R12 SLA Tables connection to AP, AR, INV,Payments, Receiving

R12 SLA (Sub ledger Accounting)

1) All accounting performed before transfer to the GL. Accounting data generated and stored in “Accounting Events” tables prior to transfer to GL

2) Run “Create Accounting” to populate accounting events (SLA) tables. User can “View Accounting” only after “Create Accounting” is run. Create Accounting process

– Applies accounting rules

– Loads SLA tables, GL tables

– Creates detailed data per accounting rules, stores in SLA “distribution links” table

3) Below are the key tables for SLA in R12

XLA_AE_HEADERS xah

XLA_AE_LINES xal

XLA_TRANSACTION_ENTITIES xte

XLA_DISTRIBUTION_LINKS xdl

GL_IMPORT_REFERENCES gir

Below are the possible joins between these XLA Tables

xah.ae_header_id = xal.ae_header_id

xah.application_id = xal.application_id

xal.application_id = xte.application_id

xte.application_id = xdl.application_id

xah.entity_id = xte.entity_id

xah.ae_header_id = xdl.ae_header_id

xah.event_id = xdl.event_id

xal.gl_sl_link_id = gir.gl_sl_link_id

xal.gl_sl_link_table = gir.gl_sl_link_table

xah.application_id = (Different value based on Module)

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

'TRX_NUMBER'

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

Queries:

In this post, we will check the Data related to the   Payable INVOICE ( Invoice_id = 235452 ) in Sub-Ledger Accounting (XLA). All the queries given in this post and their related posts were tested in R12.1.3 Instance.

XLA_EVENTS

SELECT DISTINCT xe.*

FROM   ap_invoices_all ai,

       xla_events xe,

       xla.xla_transaction_entities xte

WHERE  xte.application_id = 200

AND    xte.application_id   = xe.application_id

AND    ai.invoice_id        = '166014'

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id

AND    xte.entity_id        = xe.entity_id

ORDER BY

       xe.entity_id,

       xe.event_number;

XLA_AE_HEADERS 

 

SELECT DISTINCT xeh.*

FROM   xla_ae_headers xeh,

       ap_invoices_all ai,

       xla.xla_transaction_entities xte

WHERE  xte.application_id = 200

AND    xte.application_id   = xeh.application_id

AND    ai.invoice_id        = '166014'

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id

AND    xte.entity_id        = xeh.entity_id

ORDER BY

       xeh.event_id,

       xeh.ae_header_id ASC; 

XLA_AE_LINES

SELECT DISTINCT xel.*,

       fnd_flex_ext.get_segs('SQLGL','GL#', '50577' , xel.code_combination_id) "Account"

FROM   xla_ae_lines xel,

       xla_ae_headers xeh,

       ap_invoices_all ai,

       xla.xla_transaction_entities xte

WHERE  xte.application_id = 200

AND    xel.application_id   = xeh.application_id

AND    xte.application_id   = xeh.application_id

AND    ai.invoice_id        = '166014'

AND    xel.ae_header_id     = xeh.ae_header_id

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id

AND    xte.entity_id        = xeh.entity_id

ORDER BY

       xel.ae_header_id,

       xel.ae_line_num ASC;

XLA_DISTRIBUTION_LINKS

SELECT DISTINCT xdl.*

FROM   xla_distribution_links xdl,

       xla_ae_headers xeh,

       ap_invoices_all ai,

       xla.xla_transaction_entities xte

WHERE  xte.application_id = 200

AND    xdl.application_id   = xeh.application_id

AND    xte.application_id   = xeh.application_id

AND    ai.invoice_id        = '166014'

AND    xdl.ae_header_id     = xeh.ae_header_id

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id

AND    xte.entity_id        = xeh.entity_id

ORDER BY

       xdl.event_id,

       xdl.a_header_id,

       xdl.ae_line_num ASC;

XLA_TRANSACTION_ENTITIES 

SELECT DISTINCT xte.*

FROM   ap_invoices_all ai,

       xla.xla_transaction_entities xte

WHERE  xte.application_id = 200

AND    ai.invoice_id        = '166014'

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id;

XLA_ACCOUNTING_ERRORS

SELECT DISTINCT xae.*

FROM   ap_invoices_all ai,

       xla_events xe,

       xla.xla_transaction_entities xte,

       xla_accounting_errors xae

WHERE  xte.application_id = 200

AND    xae.application_id   = xte.application_id

AND    xte.application_id   = xe.application_id

AND    ai.invoice_id        = '166014'

AND    xe.event_id          = xae.event_id

AND    xte.entity_code      = 'AP_INVOICES'

AND    xte.source_id_int_1  = ai.invoice_id

AND    xte.entity_id        = xe.entity_id;