This query helps in extracting all the Open AR invoices which are still open for a particular customer in oracle cloud ERP. This is particularly helpful in dunning operations when sending data to third parties which expect to send the dunning letters to customer for unpaid invoices from their end.
SELECT
apsa.TRX_NUMBER TRANSACTION_NUMBER ,
CST.ACCOUNT_NUMBER CUSTOMER_NUMBER ,
hp.PARTY_NAME CUSTOMER_NAME ,
apsa.CLASS TRANSACTION_CLASS ,
rctta.NAME TRANSACTION_TYPE ,
rctta.DESCRIPTION TRANSACTION_TYPE_DESC,
apsa.INVOICE_CURRENCY_CODE TRX_CURRENCY ,
apsa.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING,
apsa.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL ,
rcta.TRX_DATE TRANSACTION_DATE,
decode(apsa.STATUS,’OP’,’OPEN’,apsa.STATUS) STATUS
FROM
RA_CUSTOMER_TRX_ALL rcta,
RA_CUST_TRX_TYPES_ALL rctta ,
HZ_CUST_SITE_USES_ALL hcsua ,
HZ_CUST_ACCT_SITES_ALL hcasa,
HZ_CUST_ACCOUNTS CST ,
HZ_PARTIES hp ,
AR_PAYMENT_SCHEDULES_ALL apsa
WHERE 1=1
AND rcta.CUSTOMER_TRX_ID = apsa.CUSTOMER_TRX_ID
AND rctta.CUST_TRX_TYPE_SEQ_ID = apsa.CUST_TRX_TYPE_SEQ_ID
AND CST.CUST_ACCOUNT_ID = apsa.CUSTOMER_ID
AND CST.PARTY_ID = hp.PARTY_ID
AND hcsua.SITE_USE_ID = apsa.CUSTOMER_SITE_USE_ID
AND hcsua.CUST_ACCT_SITE_ID = hcasa.CUST_ACCT_SITE_ID
AND hcasa.CUST_ACCOUNT_ID = CST.CUST_ACCOUNT_ID
AND rcta.COMPLETE_FLAG = ‘Y’
AND apsa.STATUS!=’CL’
AND hp.PARTY_NAME like ‘%BRIDGE%’