This query extracts all the AR receipts which are applied to a particular AR invoice in Oracle cloud ERP. This query is really helpful when doing root cause analysis for a particular AR invoice issue. We have included important columns like Receipt Date, AR invoice number and Receipt Bank account name.
select rcta.TRX_NUMBER
,hou.NAME INVOICE_BU_NAME
,acra.RECEIPT_NUMBER
,acra.AMOUNT RECEIPT_AMOUNT
,acra.CURRENCY_CODE RECEIPT_CURRENCY_CODE
,to_char(acra.RECEIPT_DATE,’DD-MM-YYYY’) RECEIPT_DATE
,acra.comments Receipt_comments
,araa.AMOUNT_APPLIED
,araa.APPLICATION_TYPE
,araa.STATUS
,to_char(araa.GL_DATE,’DD-MM-YYYY’) GL_DATE
,cba.BANK_ACCOUNT_NAME RECIEPT_BANK_ACCOUNT
from RA_CUSTOMER_TRX_ALL rcta
,ar_receivable_applications_all araa
,ar_cash_receipts_all acra
,hr_operating_units hou
,CE_BANK_ACCT_USES_ALL cbaua
,CE_BANK_ACCOUNTS cba
where 1=1
AND araa.CASH_RECEIPT_ID=acra.CASH_RECEIPT_ID
AND rcta.CUSTOMER_TRX_ID=araa.APPLIED_CUSTOMER_TRX_ID
AND hou.organization_id=rcta.org_id
AND acra.REMIT_BANK_ACCT_USE_ID=cbaua.BANK_ACCT_USE_ID
AND cba.BANK_ACCOUNT_ID=cbaua.BANK_ACCOUNT_ID
AND rcta.TRX_NUMBER like ‘INV122312%’
ORDER BY rcta.TRX_NUMBER,acra.AMOUNT,acra.RECEIPT_NUMBER