AR SQL Query to get Open Invoices where Customer payment status is Open

Hi All,

The below given SQL Query for Oracle Apps Receivables (AR)  provides the main columns from the table – RA_CUSTOMER_TRX_ALL and AR_PAYMENT_SCHEDULES_ALL to get the details for the Open Invoices for a particular Customer ( enter TRX_NUMBER) or for all Customers.

SELECT
RA_CUST_TRX.STATUS_TRX,
RA_CUST_TRX.TRX_NUMBER,
RA_CUST_TRX.TRX_DATE,
RA_CUST_TRX.BILLING_DATE,
RA_CUST_TRX.BATCH_ID,
RA_CUST_TRX.BATCH_SOURCE_ID,
RA_TERMS.NAME || '-' || RA_TERMS.DESCRIPTION   TERM_NAME,
RA_CUST_TRX.INVOICE_CURRENCY_CODE TRX_CURR,
APS.ORG_ID,
APS.CUSTOMER_ID,
APS.CUSTOMER_TRX_ID,
--APS.TRX_NUMBER,
--APS.TRX_DATE,
APS.GL_DATE,
APS.CLASS,
APS.GL_DATE_CLOSED,
APS.STATUS PAYMENT_STATUS,
APS.INVOICE_CURRENCY_CODE,
APS.EXCHANGE_DATE,
APS.EXCHANGE_RATE_TYPE,
APS.EXCHANGE_RATE,
APS.DUE_DATE,
APS.AMOUNT_DUE_ORIGINAL,
APS.AMOUNT_DUE_REMAINING,
APS.AMOUNT_LINE_ITEMS_REMAINING,
APS.AMOUNT_APPLIED,
APS.NUMBER_OF_DUE_DATES
FROM
HZ_CUST_ACCOUNTS HZ_ACCT,
HZ_PARTIES HP,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_CUST_SITE_USES_ALL HCSUA,
HZ_PARTY_SITES HPS_BILL,
RA_CUST_TRX_LINE_GL_DIST_ALL RCT,
RA_TERMS RA_TERMS,
RA_CUSTOMER_TRX_ALL RA_CUST_TRX,
RA_CUSTOMER_TRX_LINES_ALL RA_TRX_LINES,
RA_CUST_TRX_TYPES_ALL RT,
AR_PAYMENT_SCHEDULES_ALL APS
WHERE 1 = 1
AND RA_CUST_TRX.CUSTOMER_TRX_ID = RA_TRX_LINES.CUSTOMER_TRX_ID
AND RA_CUST_TRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND RA_CUST_TRX.CUST_TRX_TYPE_ID = RT.CUST_TRX_TYPE_ID
AND RA_CUST_TRX.BILL_TO_CUSTOMER_ID = HZ_ACCT.CUST_ACCOUNT_ID
AND RA_CUST_TRX.ORG_ID = APS.ORG_ID
AND RA_CUST_TRX.TERM_ID = RA_TERMS.TERM_ID
AND RCT.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND RCT.CUSTOMER_TRX_ID = RA_CUST_TRX.CUSTOMER_TRX_ID
AND RCT.CUSTOMER_TRX_ID = RA_TRX_LINES.CUSTOMER_TRX_ID
AND RCT.CUSTOMER_TRX_LINE_ID = RA_TRX_LINES.CUSTOMER_TRX_LINE_ID
AND HP.PARTY_ID = HZ_ACCT.PARTY_ID
AND HCASA.CUST_ACCOUNT_ID = RA_CUST_TRX.BILL_TO_CUSTOMER_ID
AND HCASA.CUST_ACCT_SITE_ID = HCSUA.CUST_ACCT_SITE_ID
AND HCSUA.SITE_USE_ID = RA_CUST_TRX.BILL_TO_SITE_USE_ID
AND HPS_BILL.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
AND APS.AMOUNT_DUE_REMAINING <> 0
AND APS.STATUS = 'OP'
AND HCSUA.SITE_USE_CODE = 'BILL_TO'
AND HZ_ACCT.STATUS = 'A'
AND HCASA.STATUS = 'A'
AND HCSUA.STATUS = 'A'
AND RA_CUST_TRX.COMPLETE_FLAG = 'Y'
AND RA_TRX_LINES.LINE_TYPE IN ('FREIGHT', 'LINE')
--AND HZ_ACCT.ACCOUNT_NUMBER = :P_ENTER_CUSTOMER_NUMBER
ORDER BY RA_CUST_TRX.TRX_NUMBER DESC,
RA_CUST_TRX.TRX_DATE DESC
AR SQL Query to get Open Invoices where Customer payment status is Open
Scroll to top
Copy Protected by Chetan's WP-Copyprotect.