Oracle Apps(EBS) - AR Receipt Register Query with Bank statement Header and Line Details

Below query is useful when you required  Non Misc Receipts Along with Bank Statement Header , Line Details and Activity name ( like Receipt Write off)

SELECT ACRA.RECEIPT_DATE
,( select distinct CSH.STATEMENT_NUMBER from  apps.ce_statement_reconcils_all CSRA,
                                    apps.ce_statement_lines CSL,
                                    apps.ce_statement_headers CSH 
                            where  CSRA.REFERENCE_ID=ACRHA.CASH_RECEIPT_HISTORY_ID
                                    AND CSRA.STATEMENT_LINE_ID=CSL.STATEMENT_LINE_ID
                                    AND CSL.STATEMENT_HEADER_ID=CSH.STATEMENT_HEADER_ID)  STATEMENT_NUMBER
,( select CSL.LINE_NUMBER from  apps.ce_statement_reconcils_all CSRA,
                                    apps.ce_statement_lines CSL,
                                    apps.ce_statement_headers CSH 
                            where  CSRA.REFERENCE_ID=ACRHA.CASH_RECEIPT_HISTORY_ID
                                    AND CSRA.STATEMENT_LINE_ID=CSL.STATEMENT_LINE_ID
                                    AND CSL.STATEMENT_HEADER_ID=CSH.STATEMENT_HEADER_ID)  LINE_NUMBER
,ACRA.RECEIPT_NUMBER
,DECODE(ARCAA.applied_payment_schedule_id, -1,NVL(SUBSTR(HCA_ONACC.ACCOUNT_NUMBER, INSTR(HCA_ONACC.ACCOUNT_NUMBER, '.')+1), HCA_ONACC.ACCOUNT_NUMBER), NVL(SUBSTR(HCA.ACCOUNT_NUMBER, INSTR(HCA.ACCOUNT_NUMBER, '.')+1), HCA.ACCOUNT_NUMBER)) CUSTOMER_NUMBER
,DECODE(ARCAA.applied_payment_schedule_id, -1,HP_ONACC.PARTY_NAME,HP.PARTY_NAME ) CUSTOMER_NAME
,DECODE(ARCAA.applied_payment_schedule_id, -1, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'ON_ACC'), -3,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'RCPT_WRITE_OFF'), -4,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CLAIM_INV'), -6,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_REFUND'), -8,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'REFUND'), -9,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_CHARGEBACK') ,RCTA.TRX_NUMBER) APPLIED_TO
,ARCAA.APPLY_DATE
,art.name ACTIVITY_NAME
,ARCAA.AMOUNT_APPLIED
,ACRA.AMOUNT RECEIPT_AMOUNT
,ACRA.CURRENCY_CODE TRANSACTION_CURRENCY
,FU.USER_NAME APPLIED_USER
,ACRA.CREATION_DATE
                    FROM apps.ar_receivable_applications_all ARCAA,
apps.ar_cash_receipts_all ACRA,
apps.ar_cash_receipt_history_all ACRHA,
apps.ra_customer_trx_all RCTA,
apps.hz_cust_accounts HCA,
apps.hz_parties HP,
apps.hz_cust_accounts HCA_ONACC,
apps.hz_parties HP_ONACC,
apps.fnd_user FU
,ar_receivables_trx_ALL art                         
                  WHERE ARCAA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID(+)
AND ACRHA.CASH_RECEIPT_ID(+)=ACRA.CASH_RECEIPT_ID
AND ARCAA.APPLIED_CUSTOMER_TRX_ID=RCTA.CUSTOMER_TRX_ID(+)
AND RCTA.BILL_TO_CUSTOMER_ID=HCA.CUST_ACCOUNT_ID(+)
AND HCA.PARTY_ID=HP.PARTY_ID(+)
AND HCA_ONACC.CUST_ACCOUNT_ID(+)=ARCAA.ON_ACCT_CUST_ID
AND HCA_ONACC.PARTY_ID=HP_ONACC.PARTY_ID(+)
AND FU.USER_ID(+)=ARCAA.CREATED_BY
AND ARCAA.STATUS NOT IN ('UNAPP','UNID')
                            AND art.receivables_trx_id(+) = ARCAA.receivables_trx_id
                            AND ARCAA.GL_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_gl_date_from,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE) AND NVL(TO_DATE(SUBSTR(:p_gl_date_to,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE)
                            AND ARCAA.APPLY_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_apply_date_from,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE) AND NVL(TO_DATE(SUBSTR(:p_apply_date_to,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE)
                            AND(( HP.PARTY_NAME between NVL(:p_customer_name_low,HP.PARTY_NAME) AND NVL(:p_customer_name_high,HP.PARTY_NAME)) or
(HP_ONACC.PARTY_NAME between NVL(:p_customer_name_low,HP_ONACC.PARTY_NAME) AND NVL(:p_customer_name_high,HP_ONACC.PARTY_NAME))
   )
                            AND( ( HCA.ACCOUNT_NUMBER between NVL(:p_customer_number_low,HCA.ACCOUNT_NUMBER) AND NVL(:p_customer_number_high,HCA.ACCOUNT_NUMBER) or
   (HCA_ONACC.ACCOUNT_NUMBER between NVL(:p_customer_number_low,HCA_ONACC.ACCOUNT_NUMBER) AND NVL(:p_customer_number_high,HCA_ONACC.ACCOUNT_NUMBER))
  )
   )
                            AND ARCAA.ORG_ID=:p_org AND
                            ACRA.ORG_ID=:p_org AND
                            ACRHA.ORG_ID=:p_org
AND ACRA.TYPE != 'MISC'
AND ARCAA.REVERSAL_GL_DATE is NULL
and ACRHA.REVERSAL_GL_DATE is NULL
            UNION
                    SELECT ACRA.RECEIPT_DATE
                            ,NULL STATEMENT_NUMBER
                            ,NULL LINE_NUMBER
                            ,ACRA.RECEIPT_NUMBER
                            ,NVL(SUBSTR(HCA_ONACC.ACCOUNT_NUMBER, INSTR(HCA_ONACC.ACCOUNT_NUMBER, '.')+1), HCA_ONACC.ACCOUNT_NUMBER) CUSTOMER_NUMBER
                            ,HP_ONACC.PARTY_NAME CUSTOMER_NAME
                            ,DECODE(ARCAA.applied_payment_schedule_id, -1, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'ON_ACC'), -3,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'RCPT_WRITE_OFF'), -4,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CLAIM_INV'), -6,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_REFUND'), -8,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'REFUND'), -9,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_CHARGEBACK') ,null) APPLIED_TO
                            ,ARCAA.APPLY_DATE
                            ,art.name ACTIVITY_NAME
                            ,ARCAA.AMOUNT_APPLIED
                            ,ACRA.AMOUNT RECEIPT_AMOUNT
                            ,ACRA.CURRENCY_CODE TRANSACTION_CURRENCY
                            ,FU.USER_NAME APPLIED_USER
                            ,ACRA.CREATION_DATE
                    FROM apps.ar_receivable_applications_all ARCAA,
                            apps.ar_cash_receipts_all ACRA,
                            apps.ar_cash_receipt_history_all ACRHA,
                            apps.hz_cust_accounts HCA_ONACC,
                            apps.hz_parties HP_ONACC,
                            apps.fnd_user FU
                            ,ar_receivables_trx_ALL art
                    WHERE ARCAA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID
AND ACRHA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID
AND ARCAA.APPLIED_CUSTOMER_TRX_ID IS NULL
AND (HCA_ONACC.CUST_ACCOUNT_ID=ARCAA.ON_ACCT_CUST_ID or HCA_ONACC.CUST_ACCOUNT_ID=ACRA.PAY_FROM_CUSTOMER )
AND HCA_ONACC.PARTY_ID=HP_ONACC.PARTY_ID(+)
AND FU.USER_ID(+)=ARCAA.CREATED_BY
                            AND ARCAA.STATUS NOT IN ('UNAPP','UNID')
                            AND art.receivables_trx_id(+) = ARCAA.receivables_trx_id AND
ARCAA.ORG_ID=:p_org
AND ACRA.ORG_ID=:p_org
AND ACRHA.ORG_ID=:p_org
AND ACRA.TYPE != 'MISC'                         
                            AND ARCAA.APPLIED_PAYMENT_SCHEDULE_ID <0
                            AND ARCAA.AMOUNT_APPLIED >0
                            AND ARCAA.REVERSAL_GL_DATE IS NULL
                            AND ACRHA.REVERSAL_GL_DATE IS NULL
                            AND ARCAA.GL_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_gl_date_from,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE) AND NVL(TO_DATE(SUBSTR(:p_gl_date_to,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE)
                            AND ARCAA.APPLY_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_apply_date_from,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE) AND NVL(TO_DATE(SUBSTR(:p_apply_date_to,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE)
                            AND HP_ONACC.PARTY_NAME between NVL(:p_customer_name_low,HP_ONACC.PARTY_NAME) AND NVL(:p_customer_name_high,HP_ONACC.PARTY_NAME)
                            AND HCA_ONACC.ACCOUNT_NUMBER between NVL(:p_customer_number_low,HCA_ONACC.ACCOUNT_NUMBER) AND NVL(:p_customer_number_high,HCA_ONACC.ACCOUNT_NUMBER)
                            order by RECEIPT_NUMBER,CREATION_DATE ASC  ;