Loading

Tuesday, May 25, 2010

General Ledger to Account Payables [or] AP-> GL transactions Drilldown in Oracle apps R12

It can be used to drilldown GL to AP (or) in the reverse way. Here let us try to collect the General Ledger information from the Account Payables data (Invoice id). All the queries used below were tested in R12.1.1 instance. 

Collecting the sub ledger AP information (Invoice Information)
Here we collect all the data about the Invoice number from sub-ledger (AP) tables. The most important information and used to link with GL tables is AP_INVOICES_ALL.invoice_id


SELECT  aia.invoice_id -- This will form a link with SLA Tables
       ,aia.invoice_num
       ,aia.invoice_amount
       ,aia.payment_status_flag
       ,aia.doc_category_code
       ,aila.line_type_lookup_code
       ,aila.line_source
       ,aila.match_type
FROM   ap_invoices_all aia
      ,ap_invoice_lines_all aila
WHERE  aia.invoice_id = aila.invoice_id
AND    aia.org_id = aila.org_id
AND    aia.invoice_id = 11844
AND    aia.org_id = 82;


Checking the sub-ledger Tables to General-ledger link tables for GL information
In R12, we have tables that holds link between any sub ledger data to General ledger data.
The below query gives you the important columns available in the XLA tables and GL linking columns

SELECT xte.legal_entity_id
      ,xte.source_id_int_1 Invoice_id
      ,xte.transaction_number
      ,xal.gl_sl_link_id     -- Connects to GL tables
      ,xal.gl_sl_link_table  -- Connects to GL tables
      ,xah.ae_header_id
      ,xah.event_id
      ,xah.entity_id
      ,xah.event_type_code
      ,xah.description
      ,xal.ae_line_num
      ,xal.code_combination_id
      ,xal.accounting_class_code
      ,xal.party_id
      ,xal.party_site_id
      ,xal.party_type_code    
FROM   xla_transaction_entities xte
      ,xla_events             xev
      ,xla_ae_headers         xah
      ,xla_ae_lines           xal     
WHERE xte.application_id  = 200
AND xte.source_id_int_1   = 11844
AND xte.transaction_number= '1001'
AND xev.entity_id         = xte.entity_id
AND xev.application_id    = xte.application_id
AND xah.application_id    = xev.application_id
AND xev.event_id          = xah.event_id
AND xal.application_id    = xah.application_id
AND xal.ae_header_id      = xah.ae_header_id;


Collecting the data from GL
Below query collects the data from GL.
In the below query, you the see the linking columns in the first five places and others were other important ones.

SELECT glimp.reference_5 ENTITY_ID
      ,glimp.reference_6 EVENT_ID
      ,glimp.reference_7 AE_HEADER_ID
      ,glimp.gl_sl_link_id
      ,glimp.gl_sl_link_table
      ,glb.je_batch_id
      ,glb.name
      ,glb.status
      ,glb.description
      ,glh.je_header_id
      ,glh.je_category
      ,glh.je_source
      ,glh.name
      ,glh.description
      ,glh.running_total_accounted_cr
      ,glh.running_total_accounted_dr
      ,gll.je_line_num
      ,gll.code_combination_id
FROM   gl_import_references glimp
      ,gl_je_batches glb
      ,gl_je_headers glh
      ,gl_je_lines   gll
WHERE  glimp.je_header_id     = glh.je_header_id
AND    glimp.je_line_num      = gll.je_line_num
AND    glimp.je_batch_id      = glb.je_batch_id
AND    glh.je_header_id       = gll.je_header_id
AND    glh.je_batch_id        = glb.je_batch_id
AND    glimp.gl_sl_link_id    = 508000         
AND    glimp.gl_sl_link_table = 'XLAJEL';

2 Responses to “General Ledger to Account Payables [or] AP-> GL transactions Drilldown in Oracle apps R12”

fm said...
August 28, 2012 at 3:12 AM

Thank you.


Sridevi K said...
October 8, 2016 at 12:50 AM

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.


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.