This query extracts all the AR invoices for a particular customer in oracle cloud ERP. This helps in checking which invoices have been closed and which are still open for a particular customer.
SELECT
apsa.TRX_NUMBER TRANSACTION_NUMBER
,hou.NAME BU_NAME
,CST.ACCOUNT_NUMBER CUSTOMER_NUMBER
,rcta.BILL_TEMPLATE_NAME
,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
,rcta.DELIVERY_METHOD_CODE
,rcta.DEL_CONTACT_EMAIL_ADDRESS
,decode(apsa.STATUS,’OP’,’OPEN’,’CL’,’CLOSED’,apsa.STATUS) STATUS
,rcta.CREATED_BY
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 ,
hr_operating_units hou,
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 hp.PARTY_NAME like ‘%ABC ORG%’
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 hou.organization_id=rcta.org_id