Subledger Accounting (SLA) in R12

Subledger Accounting (SLA) in R12

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_EVENTS xae
XLA_TRANSACTION_ENTITIES xte
XLA_DISTRIBUTION_LINKS xdlGL_IMPORT_REFERENCES gir
Below are the possible joins between these XLA Tables
xah.ae_header_id = xal.ae_header_id
xah.entity_id = xte.entity_id

xae.entity_id = xte.entity_id

xah.event_id = xlae.event_id
xah.ae_header_id = xdl.ae_header_id
xah.ae_line_num = xdl.ae_line_num
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 = xal.application_id

xal.application_id = xte.application_id

xte.application_id = xdl.application_id

xae.application_id = xte.application_id

xah.application_id = xae.application_id
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
'CUSTOMER_TRX_ID' or

'TRANSACTION_ID'

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

-------------------Prepayment Balance report

SELECT SUP.VENDOR_NAME,
API.INVOICE_NUM,
SUM (NVL (AEL.ACCOUNTED_DR, 0) - NVL (AEL.ACCOUNTED_CR, 0)) "BALANCE"
FROM xla.xla_ae_lines AEL,
xla.xla_ae_headers AEH,
---------------------------------------------------------
(SELECT XDL.AE_HEADER_ID,
XDL.AE_LINE_NUM,
XDL.APPLIED_TO_SOURCE_ID_NUM_1,
XDL.ALLOC_TO_SOURCE_ID_NUM_1,
APSI.INVOICE_NUM "ALLOC_TO_INVOICE_NUM",
XDL.EVENT_CLASS_CODE
FROM APPS.XLA_DISTRIBUTION_LINKS XDL, AP.AP_INVOICES_ALL APSI
WHERE XDL.APPLICATION_ID = 200
AND XDL.EVENT_CLASS_CODE IN
('PREPAYMENTS', 'PREPAYMENT APPLICATIONS')
AND XDL.ROUNDING_CLASS_CODE = 'PREPAID_EXPENSE'
AND XDL.ALLOC_TO_SOURCE_ID_NUM_1 = APSI.INVOICE_ID
GROUP BY XDL.AE_HEADER_ID,
XDL.AE_LINE_NUM,
XDL.APPLIED_TO_SOURCE_ID_NUM_1,
XDL.ALLOC_TO_SOURCE_ID_NUM_1,
APSI.INVOICE_NUM,
XDL.EVENT_CLASS_CODE
) dl, --------------VERY IMPORTANT---------------
-----------------------------------------------------------
GL.GL_CODE_COMBINATIONS CC,
AP.AP_SUPPLIERS SUP,
AP.AP_INVOICES_ALL API
WHERE aeh.balance_type_code = 'A'
AND aeh.ae_header_id = ael.ae_header_id
---hh
AND aeh.ae_header_id = DL.ae_header_id
AND ael.ae_line_num = DL.ae_line_num
--
AND ael.ledger_id = 2022
AND ael.code_combination_id = CC.CODE_COMBINATION_ID
AND DL.APPLIED_TO_SOURCE_ID_NUM_1 = API.INVOICE_ID --------------VERY IMPORTANT-----------------
AND CC.SEGMENT1 = '131061'
AND AEL.PARTY_ID = SUP.VENDOR_ID
AND SUP.VENDOR_NAME = 'BETEKO GROUP MMC'
AND TRUNC (AEH.ACCOUNTING_DATE) <= TO_DATE ('31-DEC-2012', 'DD-MON-YYYY')
GROUP BY API.INVOICE_NUM, SUP.VENDOR_NAME
HAVING SUM (NVL (AEL.ACCOUNTED_DR, 0) - NVL (AEL.ACCOUNTED_CR, 0)) <> 0

Here, they used a different method. Two tables, AP_INVOICES_ALL and XLA_DISTRIBUTION_LINKS are first joined using ALLOC_TO_SOURCE_ID_NUM_1 in the inline query. Afterwards, in the main query, APPLIED_TO_SOURCE_ID_NUM is used. 

http://www.oracleerp4u.com/2010/06/r12-sla-subledger-accounting.html