AP Invoice liability (Trial Balance) Query: R12 AP GL PO RCV XLA

AP Invoice liability (Trial Balance) Query: R12 AP GL PO RCV XLA

SELECT   NVL (f.currency_code, d.invoice_currency_code) currency_code,
         d.invoice_amount, d.wfapproval_status, d.invoice_num, d.invoice_id,
         i.segment1 v_code, i.vendor_name,
         NVL (d.invoice_amount, 0) - NVL (d.amount_paid, 0) amt,
         SUM (a.acctd_rounded_cr) - SUM (a.acctd_rounded_dr) amount,
         b.accounting_date, f.segment1 po_num, f.comments descr,
         h.receipt_num rct_num, c.segment6 loc, c.segment2 cost_centre,
         d.doc_sequence_value ap_voucher, j.employee_number, j.full_name
    FROM xla_transaction_entities xte,
         ap.ap_invoice_distributions_all b,
         ap.ap_invoice_lines_all k,
         gl.gl_code_combinations c,
         ap.ap_invoices_all d,
         po.po_distributions_all e,
         po.po_headers_all f,
         apps.rcv_transactions g,
         apps.rcv_shipment_headers h,
         apps.po_vendors i,
         apps.per_all_people_f j,
         (SELECT   /*+ index(tb XLA_TRIAL_BALANCES_N2) */
                   tb.code_combination_id, tb.ledger_id, tb.party_id party_id,
                   tb.gl_date, tb.ae_header_id,
                   NVL (tb.applied_to_entity_id,
                        tb.source_entity_id
                       ) entity_id,
                   tb.definition_code, tb.record_type_code,
                   SUM (NVL (tb.acctd_rounded_cr, 0)) acctd_rounded_cr,
                   SUM (NVL (tb.acctd_rounded_dr, 0)) acctd_rounded_dr,
                     SUM (NVL (tb.acctd_rounded_cr, 0))
                   - SUM (NVL (tb.acctd_rounded_dr, 0)) diff,
                   party_id
              FROM xla_trial_balances tb
             WHERE tb.definition_code = 'AP_200_1001'
               AND tb.gl_date <= :acc_date --GL Date Value               
               AND tb.code_combination_id IN (SELECT code_combination_id
                                              FROM gl_code_combinations a
                                              WHERE a.segment3 = :acc_code)--GL CC Value
          GROUP BY tb.code_combination_id,
                   tb.ledger_id,
                   tb.party_id,
                   tb.party_id,
                   NVL(tb.applied_to_entity_id,tb.source_entity_id),
                   tb.record_type_code,
                   tb.party_id,
                   tb.gl_date,
                   tb.definition_code,
                   tb.ae_header_id) a
   WHERE a.code_combination_id = c.code_combination_id
     AND d.INVOICE_NUM = :invoice_num   --Invoice Num    
     AND a.definition_code = 'AP_200_1001'
     AND a.entity_id = xte.entity_id
     AND xte.application_id = 200
     AND xte.ledger_id = a.ledger_id
     AND NVL (source_id_int_1, (-99)) = d.invoice_id
     AND d.invoice_id = k.invoice_id
     AND k.line_number = b.invoice_line_number
     AND c.segment3 = :acc_code               --GL CC Code     
     AND a.gl_date <= :acc_date               --Gl Date 
     AND k.invoice_id = b.invoice_id
     AND b.distribution_line_number = '1'
     AND k.line_number = '1'
     AND b.po_distribution_id = e.po_distribution_id(+)
     AND e.po_header_id = f.po_header_id(+)
     AND b.rcv_transaction_id = g.transaction_id(+)
     AND g.shipment_header_id = h.shipment_header_id(+)
     AND f.agent_id = j.person_id(+)
     AND i.vendor_id = d.vendor_id
     AND d.set_of_books_id = a.ledger_id
     AND d.org_id = :l_org_id           --Invoice Org Id   
     AND TRUNC (SYSDATE) BETWEEN j.effective_start_date(+) 
     AND j.effective_end_date(+)
  HAVING SUM (a.acctd_rounded_cr) - SUM (a.acctd_rounded_dr) <> 0
  GROUP BY f.currency_code,
         d.invoice_amount,
         b.project_id,
         d.wfapproval_status,
         d.invoice_num,
         d.invoice_id,
         b.accounting_date,
         f.segment1,
         h.receipt_num,
         i.segment1,
         i.vendor_name,
         c.segment6,
         d.doc_sequence_value,
         c.segment2,
         j.employee_number,
         j.full_name,
         f.agent_id,
         f.comments,
         d.invoice_currency_code,
         b.posted_flag,
         d.amount_paid,
         d.invoice_id

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

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

R12 SLA (Sub ledger Accounting)

Key tables for SLA in R12 and SLA Workflow
Oracle Sub ledger Accounting is a rulesbased
engine for generating accounting entries based on source transactions from
ALL Oracle Applications
Sub ledger Accounting is a Service, not an Application
Ø Rulebased
accounting engine, toolset & repository to support EBiz
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)

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_AE_HEADER status Columns
 Table stores important status Information
GL Transfer Status Code (GL_TRANSFER_STATUS_CODE)
The GL transfer status code of the sub-ledger journal entry has one of the following values:
Not transferred
Selected to transfer
Transferred
When a sub-ledger journal entry is created and completed, the value of this column is Not transferred. The Transfer to GL process updates this column when the subledger journal entry is transferred to General Ledger.

Subledger Journal Entry Status Code (ACCOUNTING_ENTRY_STATUS_CODE)
A subledger journal entry can have a status of Draft, Final, Incomplete, Invalid or Invalid Related Entry.
Draft: The entry status is set to Draft when the following conditions are met:
Final: The entry status is set to Final when the following conditions are met:
Incomplete: The entry status is set to Incomplete when the following condition is met:
Invalid Related Entry: The entry status is set to Invalid Related Entry when the following condition is met:

The subledger journal entry is valid, but one or more of the other subledger journal entries associated with the same accounting event are invalid.


ScinarioI:
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').
ScinarioII:
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.

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 = 166014 ) in Sub-Ledger Accounting (XLA). All the queries given in this post and their related posts were tested in R12.1.1 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;