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