SQL query to extract all the open AR invoices for a particular BU in oracle cloud ERP

This query helps in extracting all the open AR invoices for a particular BU in oracle cloud ERP. This is extremely helpful in sending the extract to dunning softwares.

 

SELECT

apsa.TRX_NUMBER TRANSACTION_NUMBER ,

hou.NAME BU_NAME,

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 ,

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 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 hou.NAME like ‘%BU_NAME%’

AND hou.organization_id=rcta.org_id