Loading

Monday, January 10, 2011

How Payable Invoices Data is stored in Sub-ledger Accounting of Oracle Apps R12? (Oracle Sub-Ledger Accounting, Oracle Payables)

                                                                                                                      

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;

2 Responses to “How Payable Invoices Data is stored in Sub-ledger Accounting of Oracle Apps R12? (Oracle Sub-Ledger Accounting, Oracle Payables)”

Anonymous said...
March 27, 2013 at 11:41 AM

Whenever someone uses DISTINCT, it means that developer doesn't fully understand the data model and is missing an important foreign key definition.


Sridevi Koduru said...
July 7, 2016 at 12:54 AM

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.