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.
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.
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'
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'
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'
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 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'
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.
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:
Post a Comment