GL to AR receipts drill down query

GL to AR receipts drill down query

In this post , We will be discuss about the GL to AR receipt drill down SQL query in oracle apps. To find out the AR receipt information from AR to GL we need to have this GL to AR receipt drill down sql query. To write any drill down sql query from Subledger to GL , we need to have the XLA tables , which helps to link the AR subledger module with GL module. Here below , i share the detail GL to AR receipt drill down sql query in oracle apps.

Important tables of GL to AR receipts drill down SQL query

1.gl_je_headers
2.gl_je_batches
3.gl_balances
4.gl_ledgers
5.gl_je_lines
6.gl_import_references
7.gl_code_combinations
8.xla_ae_lines
9.xla_ae_headers
10.xla_distribution_links
11.ar_distributions_all
12.ar_cash_receipt_history_all
13.ar_cash_receipts_all

Detail GL to AR receipts drill down SQL query in oracle apps

SELECT xal.accounting_date,
       gjh.je_source,
       gjh.je_category CATEGORY,
       gjb.NAME batch,
       gjh.NAME "JOURNAL name",
       gjl.je_line_num "JE LINE",
       gjl.description "JE LINE DESCR",
       gjl.accounted_cr gl_cr,
       gjl.accounted_dr gl_dr,
       acra.receipt_number ,
       xal.accounting_class_code "TRANSACTION TYPE",
       xal.accounted_cr xla_cr,
       xal.accounted_dr xla_dr,
       gjh.period_name period,
       gl.NAME ledger_name,
       glcc.segment1||'-'||glcc.segment2||'-'||glcc.segment3||'-'||glcc.segment4||'-'||glcc.segment5||'-'||glcc.segment6 GL_ACCOUNT
  FROM apps.gl_je_headers gjh,
       apps.gl_je_batches gjb,
       apps.gl_balances GL_BANC,
       apps.gl_ledgers gl,
       apps.gl_je_lines gjl,
       apps.gl_import_references gir,
       apps.gl_code_combinations glcc,
       apps.xla_ae_lines xal,
       apps.xla_ae_headers xah,
       apps.xla_distribution_links xdl,
       apps.ar_distributions_all ada,
       apps.ar_cash_receipt_history_all acrha,
       apps.ar_cash_receipts_all acra
 WHERE 1 = 1
   AND gjh.ledger_id = gl.ledger_id
   AND gjh.je_batch_id = gjb.je_batch_id
   AND gjl.je_header_id = gjh.je_header_id
   AND gjl.je_header_id = gir.je_header_id
   AND gjh.period_name = 'APR-14'
      AND gjh.je_source = 'Receivables'
   AND gjl.je_line_num = gir.je_line_num
   AND gjl.code_combination_id = glcc.code_combination_id
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND gir.gl_sl_link_table = xal.gl_sl_link_table
   AND xal.ae_header_id = xah.ae_header_id
   AND xal.ae_header_id = xdl.ae_header_id
   AND xal.application_id = xah.application_id
   AND xal.ae_line_num = xdl.ae_line_num
   AND xal.application_id = xdl.application_id
   AND xdl.source_distribution_type(+) = 'AR_DISTRIBUTIONS_ALL'
   AND xdl.source_distribution_id_num_1(+) = ada.line_id
   AND ada.source_id = acrha.cash_receipt_history_id
   AND acrha.cash_receipt_id = acra.cash_receipt_id
   AND GL_BANC.code_combination_id = glcc.code_combination_id
   AND GL_BANC.period_name = gjh.period_name
   AND GL_BANC.currency_code = gl.currency_code
   AND glcc.segment4 = :P_ACCOUNT