AIM: This query is used for getting the data of AR Receipt in account receivable.
Script:
SELECT
ABA.NAME Receipt_Batch
,DECODE(ACRA.STATUS,’APP’,’APPLIED’,’UNID’,’UNIDENTIFIED’,’UNAPP’,’UNAPPLIED’,’REV’,’REVERSED’,ACRA.STATUS) STATE
,acra.RECEIPT_NUMBER
,ACRA.COMMENTS
,sum(unapp.amount_applied) Unapplied_Amount
,ACRA.TYPE
,ACRA.AMOUNT RECEIPT_AMOUNT
,ARM.NAME RECEIPT_MENTHOD
,acra.RECEIPT_DATE
,ACRA.CURRENCY_CODE CURRENCY
,SUM(UNID.AMOUNT_APPLIED) UNIDENTIFIED_AMOUNT
,PARTY.PARTY_NAME Customer_Name
,PARTY.PARTY_NUMBER Customer_Number
,ACRA.POSTMARK_DATE
,acra.DEPOSIT_DATE
,ACRHA.GL_DATE
,CBA.BANK_ACCOUNT_NUM
,ACRHA.STATUS
FROM AR_CASH_RECEIPTS_ALL ACRA
, AR_CASH_RECEIPT_HISTORY_ALL ACRHA
, AR_BATCHES_ALL ABA
,(select ar.amount_applied,ar.cash_receipt_id from ar_receivable_applications_all ar where ar.status = ‘UNAPP’) Unapp
, AR_RECEIPT_METHODS ARM
,(select ar.amount_applied,ar.cash_receipt_id from ar_receivable_applications_all ar where ar.status = ‘UNID’) UNID
,hz_cust_accounts_all hcca
, HZ_PARTIES PARTY
, CE_BANK_ACCT_USES_ALL CBAUA
, CE_BANK_ACCOUNTS CBA
WHERE ACRA.CASH_RECEIPT_ID = ACRHA.CASH_RECEIPT_ID(+)
and ACRHA.current_record_flag = ‘Y’
AND ACRHA.BATCH_ID = ABA.BATCH_ID(+)
and acra.cash_receipt_id = unapp.cash_receipt_id(+)
AND ACRA.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
AND ACRA.CASH_RECEIPT_ID = UNID.CASH_RECEIPT_ID(+)
and acra.pay_from_customer = hcca.cust_account_id(+)
AND hcca.PArty_id = PARTY.PARTY_ID(+)
AND ACRA.REMIT_BANK_ACCT_USE_ID = CBAUA.BANK_ACCT_USE_ID
AND ACRA.ORG_ID = CBAUA.ORG_ID
AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
GROUP BY
ABA.NAME
,ACRA.STATUS
,RECEIPT_NUMBER
,ACRA.COMMENTS
,ACRA.TYPE
,ACRA.AMOUNT
,ARM.NAME
,ACRA.RECEIPT_DATE
,ACRA.CURRENCY_CODE
,PARTY.PARTY_NAME
,PARTY.PARTY_NUMBER
,ACRA.POSTMARK_DATE
,ACRA.DEPOSIT_DATE
,ACRHA.GL_DATE
,CBA.BANK_ACCOUNT_NUM
,ACRHA.STATUS
Do drop a note by writing us at Dileep.dinesh@doyensys.com or use the comment section below to ask your questions
