Infolinks

Sunday, 24 June 2012

How to Drilldown from General Ledger(GL) to Account Receivables(AR) [or] AR-> GL in R12

How to Drilldown from General Ledger(GL) to Account Receivables(AR) [or] AR-> GL in R12


It can be used to drilldown GL to AR (or) in the reverse way. Here let us try to collect the General Ledger information from the Account receivables information (Receipt Number). All the queries used below were tested in R12.1.1 instance. 

Collecting the sub ledger information (Receipt Information)
Here we collect all the information about the Receipt number from sub-ledger (AR) tables. The most important information and used to link with GL tables is AR_DISTRIBUTIONS_ALL.line_id



SELECT acr.receipt_number
      ,acr.cash_receipt_id
      ,ard.line_id  -- This will form a link with GL
      ,ard.source_id
      ,ard.source_table
FROM   ar_cash_receipts_all        acr
      ,ar_cash_receipt_history_all acrh
      ,ar_distributions_all        ard
WHERE  acr.cash_receipt_id          = acrh.cash_receipt_id
AND    acrh.cash_receipt_history_id = ard.source_id
AND    ard.source_table             ='CRH'
AND    acr.receipt_number           ='65465486';


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


SELECT 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
      ,xdl.source_distribution_type
      ,xdl.source_distribution_id_num_1
      ,xdl.accounting_line_code
      ,xdl.event_class_code
FROM   xla_ae_headers         xah
      ,xla_ae_lines           xal
      ,xla_distribution_links xdl
WHERE xah.ae_header_id = xal.ae_header_id
AND   xah.ae_header_id = xdl.ae_header_id
AND   xal.ae_line_num  = xdl.ae_line_num
AND   xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND   xdl.source_distribution_id_num_1 = 3298;


Collecting the information from GL
Below query collects the information from the GL.
In the below query you the see the linking columns in the first five places and others were some 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    = 235004
AND    glimp.gl_sl_link_table = 'XLAJEL';

No comments:

Post a Comment