AR Recipts to GL SQL including XLA

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

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(+)