Query to find GL to AR Receiving Transactions

The following query finds GL to AR Receiving Transactions. This query is written by Mr. Anil Patil and can also be found in this link:

http://oracle.anilrpatil.com/2009/12/12/drilldown-from-gl-to-receiving-transactions-2/

I have reposted this for sharing purpose only.

-------------------------------------------------------------------------------

-- Query to find GL to AR Receiving Transactions

-------------------------------------------------------------------------------

SELECT

       b.NAME                        je_batch_name,

       b.description                 je_batch_description,

       b.running_total_accounted_dr  je_batch_total_dr,

       b.running_total_accounted_cr  je_batch_total_cr,

       b.status                      je_batch_status,

       b.default_effective_date      je_batch_effective_date,

       b.default_period_name         je_batch_period_name,

       b.creation_date               je_batch_creation_date,

       u.user_name                   je_batch_created_by,

       h.je_category                 je_header_category,

       h.je_source                   je_header_source,

       h.period_name                 je_header_period_name,

       h.NAME                        je_header_journal_name,

       h.status                      je_header_journal_status,

       h.creation_date               je_header_created_date,

       u1.user_name                  je_header_created_by,

       h.description                 je_header_description,

       h.running_total_accounted_dr  je_header_total_acctd_dr,

       h.running_total_accounted_cr  je_header_total_acctd_cr,

       l.je_line_num                 je_lines_line_number,

       l.ledger_id                   je_lines_ledger_id,

       glcc.concatenated_segments    je_lines_ACCOUNT,

       l.entered_dr                  je_lines_entered_dr,

       l.entered_cr                  je_lines_entered_cr,

       l.accounted_dr                je_lines_accounted_dr,

       l.accounted_cr                je_lines_accounted_cr,

       l.description                 je_lines_description,

       glcc1.concatenated_segments   xla_lines_account,

       xlal.accounting_class_code    xla_lines_acct_class_code,

       xlal.accounted_dr             xla_lines_accounted_dr,

       xlal.accounted_cr             xla_lines_accounted_cr,

       xlal.description              xla_lines_description,

       xlal.accounting_date          xla_lines_accounting_date,

       xlate.entity_code             xla_trx_entity_code,

       xlate.source_id_int_1         xla_trx_source_id_int_1,

       xlate.source_id_int_2         xla_trx_source_id_int_2,

       xlate.source_id_int_3         xla_trx_source_id_int_3,

       xlate.security_id_int_1       xla_trx_security_id_int_1,

       xlate.security_id_int_2       xla_trx_security_id_int_2,

       xlate.transaction_number      xla_trx_transaction_number,

       rcvt.transaction_type         rcv_trx_transaction_type,

       rcvt.transaction_date         rcv_trx_transaction_date,

       rcvt.quantity                 rcv_trx_quantity,

       rcvt.shipment_header_id       rcv_trx_shipment_header_id,

       rcvt.shipment_line_id         rcv_trx_shipment_line_id,

       rcvt.destination_type_code    rcv_trx_destination_type_code,

       rcvt.po_header_id             rcv_trx_po_header_id,

       rcvt.po_line_id               rcv_trx_po_line_id,

       rcvt.po_line_location_id      rcv_trx_po_line_location_id,

       rcvt.po_distribution_id       rcv_trx_po_distribution_id,

       rcvt.vendor_id                rcv_trx_vendor_id,

       rcvt.vendor_site_id           rcv_trx_vendor_site_id

  FROM

       gl_je_batches                 b,

       gl_je_headers                 h,

       gl_je_lines                   l,

       fnd_user                      u,

       fnd_user                      u1,

       gl_code_combinations_kfv      glcc,

       gl_code_combinations_kfv      glcc1,

       gl_import_references          gir,

       xla_ae_lines                  xlal,

       xla_ae_headers                xlah,

       xla_events                    xlae,

       xla.xla_transaction_entities  xlate,

       rcv_transactions              rcvt

 WHERE

       1=1

   AND b.created_by              =  u.user_id

   AND h.created_by              =  u1.user_id

   AND b.je_batch_id             =  h.je_batch_id

   AND h.je_header_id            =  l.je_header_id

   AND l.code_combination_id     =  glcc.code_combination_id

   AND l.je_header_id            =  gir.je_header_id

   AND l.je_line_num             =  gir.je_line_num

   AND gir.gl_sl_link_table      =  xlal.gl_sl_link_table

   AND gir.gl_sl_link_id         =  xlal.gl_sl_link_id

   AND xlal.application_id       =  xlah.application_id

   AND xlal.ae_header_id         =  xlah.ae_header_id

   AND xlal.code_combination_id  =  glcc1.code_combination_id

   AND xlah.application_id       =  xlae.application_id

   AND xlah.event_id             =  xlae.event_id

   AND xlae.application_id       =  xlate.application_id

   AND xlae.entity_id            =  xlate.entity_id

   AND xlate.source_id_int_1     =  rcvt.transaction_id

   AND h.je_category             =  'Receiving'

   AND b.default_period_name     =  'DEC-12'

 ORDER BY h.je_category;