AR to GL link in oracle apps

Many times we might need to get data for AR Receipts and link them to GL via the XLA

Below SQL can be used to get that information and also get details of Receipt applications too

/* Formatted on 11/15/2016 2:30:57 PM (QP5 v5.114.809.3010) */
SELECT   ard.source_id,
         gjh.period_name,
         gjh.name journal,
         gjh.JE_CATEGORY,
         gjh.JE_SOURCE,
         gcc.concatenated_segments                                     --xdl.*
                                  ,
         gcc.SEGMENT2 sbu,
         gcc.segment5 natural_account,
         gcc.segment6 sub_account,
         gcc.SEGMENT8 region,
         gjl.accounted_dr Debit_amount,
         gjl.accounted_cr credit_amount,
         xdl.SOURCE_DISTRIBUTION_TYPE,
         xal.ACCOUNTING_CLASS_CODE,
         ard.SOURCE_ID,
         ard.SOURCE_TABLE,
         ard.SOURCE_TYPE,
         gjl.DESCRIPTION,
         (SELECT   party_name
            FROM   apps.hz_cust_accounts hca, apps.hz_parties hp
           WHERE   hca.cust_account_id = acr.PAY_FROM_CUSTOMER
                   AND hca.party_id = hp.party_id)
            customer_name,
         gjh.POSTED_DATE,
         NULL trx_number,
         arm.name receipt_method,
         NULL transaction_type,
         NULL purchase_order
  FROM   apps.gl_je_headers gjh,
         apps.gl_je_lines gjl,
         apps.xla_ae_lines xal,
         apps.xla_distribution_links xdl,
         apps.xla_ae_headers xah,
         apps.gl_code_combinations_kfv gcc,
         apps.AR_DISTRIBUTIONS_ALL ard,
         apps.AR_CASH_RECEIPT_HISTORY_ALL acrh,
         apps.ar_cash_receipts_all acr,
         apps.ar_receipt_methods arm
 WHERE       1 = 1
         AND gjl.code_combination_id = gcc.code_combination_id
         AND gjh.ledger_id = gjl.ledger_id
         AND gjh.je_header_id = gjl.je_header_id
         AND gjl.gl_sl_link_id = xal.gl_sl_link_id
         AND xal.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 xah.ae_header_id = xal.ae_header_id
         AND ARD.LINE_ID = XDL.source_distribution_id_num_1
         --AND ard.source_type = 'CRH'
         AND ard.SOURCE_ID = acrh.CASH_RECEIPT_HISTORY_ID
         AND acr.cash_receipt_id = acrh.cash_receipt_id
         AND acr.RECEIPT_METHOD_ID = arm.RECEIPT_METHOD_ID(+)
UNION
SELECT   ard.source_id,
         gjh.period_name,
         gjh.name journal,
         gjh.JE_CATEGORY,
         gjh.JE_SOURCE,
         gcc.concatenated_segments                                     --xdl.*
                                  ,
         gcc.SEGMENT2 sbu,
         gcc.segment5 natural_account,
         gcc.segment6 sub_account,
         gcc.SEGMENT8 region,
         gjl.accounted_dr Debit_amount,
         gjl.accounted_cr credit_amount,
         xdl.SOURCE_DISTRIBUTION_TYPE,
         xal.ACCOUNTING_CLASS_CODE,
         ard.SOURCE_ID,
         ard.SOURCE_TABLE,
         ard.SOURCE_TYPE,
         gjl.DESCRIPTION,
         (SELECT   party_name
            FROM   apps.hz_cust_accounts hca, apps.hz_parties hp
           WHERE   hca.cust_account_id = acr.PAY_FROM_CUSTOMER
                   AND hca.party_id = hp.party_id)
            customer_name,
         gjh.POSTED_DATE,
         rcta.trx_number,
         arm.name receipt_method,
         (SELECT   name
            FROM   apps.ra_cust_trx_types_all
           WHERE   cust_trx_type_id = rcta.cust_trx_type_id)
            transaction_type,
         rcta.purchase_order
  FROM   apps.gl_je_headers gjh,
         apps.gl_je_lines gjl,
         apps.xla_ae_lines xal,
         apps.xla_distribution_links xdl,
         apps.xla_ae_headers xah,
         apps.gl_code_combinations_kfv gcc,
         apps.AR_DISTRIBUTIONS_ALL ard,
         AR_RECEIVABLE_APPLICATIONS_ALL ara,
         apps.ar_cash_receipts_all acr,
         apps.ra_customer_trx_all rcta,
         apps.ar_receipt_methods arm
 WHERE       1 = 1
         AND gjl.code_combination_id = gcc.code_combination_id
         AND gjh.ledger_id = gjl.ledger_id
         AND gjh.je_header_id = gjl.je_header_id
         AND gjl.gl_sl_link_id = xal.gl_sl_link_id
         AND xal.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 xah.ae_header_id = xal.ae_header_id
         AND ARD.LINE_ID = XDL.source_distribution_id_num_1
         --AND ard.source_type = 'RA'
         AND ard.SOURCE_ID = ara.RECEIVABLE_APPLICATION_ID
         AND ara.CASH_RECEIPT_ID = acr.CASH_RECEIPT_ID
         AND ara.APPLIED_CUSTOMER_TRX_ID = rcta.customer_trx_id(+)
         AND acr.RECEIPT_METHOD_ID = arm.RECEIPT_METHOD_ID(+)