Restrict

Thursday, November 22, 2012

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















No comments: