Key tables for SLA in R12 and SLA Workflow

Key tables for SLA in R12 and SLA Workflow

Oracle Sub ledger Accounting is a rules-based engine for generating accounting entries based on source transactions from ALL Oracle Applications

Sub ledger Accounting is a Service, not an Application

Ø     Rule-based accounting engine, toolset & repository to support E-Biz modules

Ø     There are no SLA responsibilities

Ø     Users do not login to SLA

Ø     SLA is a service provided to Oracle Applications

Ø     SLA forms and programs are embedded within standard Oracle Application responsibilities (e.g. Payables Manager)

Ø     SLA provides the following services to Oracle Applications:

Ø     Generation and storage of detailed accounting entries

Ø     Storage of Sub ledger balances

Ø     Sub ledger accounting entries (with Bidirectional drilldown to /from transactions)

Ø     Sub ledger reporting

Ø    Replaces various disparate 11i setups, providing single source of truth for financial and management analysis

Ø     introduces a common data model and UI across sub ledgers

Ø     Highly granular level of detail in the Sub ledger accounting model retained

Ø     Accounting Model separate from Transactional Model

Ø     Catering to custom requirements of accounting of transactions in Sub ledgers

Ø     Allows multiple accounting representations for a single business event

Ø     Optionally Post sub ledger accounting entries to Secondary Ledgers

Ø     Resolves conflicts between Corporate and Local Accounting Requirements

Ø     Accounting created in Draft or Final mode

Ø     Draft: Review Report, Correct errors

Ø    Final: Transfer to GL, Post in GL

How to join GL tables with XLA (SubLedger Accounting) tables
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)

xla.xla_transaction_entities (source_id_int_1, etc) after filtering by application_id, entity_code and ledger_id     -> subledger's table(its key columns mentioned in xla_entity_id_mappings) for that ledger_id

For Example:
xla.xla_transaction_entities (source_id_int_1) filtered by application_id 100, entity_code AP_INVOICES and ledger_id -> ap_invoices_all (invoice_id) for that set_of_books_id.
xla.xla_transaction_entities (source_id_int_1) filtered by application_id 100, entity_code AP_PAYMENTS and ledger_id -> ap_checks_all (check_id) for that set_of_books_id.

xla.xla_transaction_entities (source_id_int_1) filtered by application_id 111, entity_code TRANSACTIONS and ledger_id -> ra_customer_trx_all (customer_trx_id) for that set_of_books_id.

Scinario-I:Online Accounting: (For single entity)

DRAFT will create Journal Entries, which are NOT final, which means they are NOT ready to be transferred to GL.

• You can see the accounting in XLA_AE_HEADERS and XLA_AE_LINES. 

          XLA_AE_HEADERS.accounting_entry_status_code is  'D'
          XLA_EVENTS.process_status_code is  'D'
          XLA_EVENTS.event_status_code is   'U'

• You can run create accounting on this transaction again and again, which will delete the old journal entries and create new ones.
• You can't transfer these journal entries to GL.

FINAL will create journal entries, which can be transferred to GL.

• You can see the accounting in XLA_AE_HEADERS and XLA_AE_LINES.

          XLA_AE_HEADERS.accounting_entry_status_code is  'F'
          XLA_EVENTS.process_status_code is  'P'
          XLA_EVENTS.event_status_code is  'P' 

• Once it is finally accounted you can NOT run create accounting on the particular transaction (specifically on that event).
• You can transfer them to GL using Transfer Journal Entries to GL program.

FINAL POST will create journal entries in Final Mode, Transfer them to GL and Post them.

• You can see the accounting in XLA_AE_HEADERS and XLA_AE_LINES. 

          XLA_AE_HEADERS.accounting_entry_status_code is  'F'
          XLA_EVENTS.process_status_code is  'P'
          XLA_EVENTS.event_status_code is  'P'

• Once it is finally accounted you can NOT run create accounting on the particular transaction (specifically on that event).
• It will transfer the journal entries to GL using Journal Import and you can find the data in GL_JE_HEADERS and GL_JE_LINES.

          XLA_AE_HEADERS.transfer_status_code is Y.
          It will post to gl_balances also (GL_JE_HEADERS.status is 'P').

Scinario-II:Create Accounting (Concurrent Program): (For more entities

1. Accounting Mode: Draft
It is same as Draft online accounting.

2. Accounting Mode: Final, Transfer to GL: No
It is same as Final online accounting.

3. Accounting Mode: Final, Transfer to GL: Yes, Post to GL: No

• It will create journal entries in Final mode, transfer them to GL.
• You can see the accounting in XLA_AE_HEADERS and XLA_AE_LINES.
• Once it is finally accounted you can NOT run create accounting on the particular transaction (specifically on that event).

          XLA_AE_HEADERS.accounting_entry_status_code is 'F'
          XLA_EVENTS.process_status_code is 'P'
          XLA_EVENTS.event_status_code is 'P'

• It will transfer the journal entries to GL using Journal Import and you can find the data in GL_JE_HEADERS and GL_JE_LINES.

          XLA_AE_HEADERS.transfer_status_code is 'Y'
          GL_JE_HEADERS.status is 'U'.

4. Accounting Mode: Final, Transfer to GL: Yes, Post to GL: Yes
It is same as Final Post online accounting.

Key Tables For Sub-Ledger

 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