The blog provides the Oracle EBS R12 SQL to check Customer Transaction Balances. The Oracle EBS R12 Account Receivable AR Invoices with Outstanding Customer Transaction Balances, AR Invoices which Over Due date are retrieved for AR Reconciliation

The below given SQL Query provides the data extract to check Customer Transaction Balances

SELECT HZ_ACCT.ACCOUNT_NUMBER, NVL (SUM (AR_PAY_SCH.AMOUNT_DUE_REMAINING), 0) INVOICE_BALANCE
FROM
HZ_CUST_ACCOUNTS_ALL HZ_ACCT,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_CUST_SITE_USES_ALL HCSU,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS LOC,
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUST_TRX_LINE_GL_DIST_ALL RA_CUS_TRX_GL_DIST,
AR_PAYMENT_SCHEDULES_ALL AR_PAY_SCH,
GL_CODE_COMBINATIONS CC
WHERE 1=1
AND AR_PAY_SCH.CUSTOMER_ID = HZ_ACCT.CUST_ACCOUNT_ID
AND AR_PAY_SCH.CUST_TRX_TYPE_ID = RCTA.CUST_TRX_TYPE_ID
AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND LOC.LOCATION_ID = HPS.LOCATION_ID
AND HZ_ACCT.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
AND HCASA.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND AR_PAY_SCH.CUSTOMER_ID = HCASA.CUST_ACCOUNT_ID
AND AR_PAY_SCH.CUSTOMER_SITE_USE_ID = HCSU.SITE_USE_ID
AND RCTA.CUSTOMER_TRX_ID = AR_PAY_SCH.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID = RA_CUS_TRX_GL_DIST.CUSTOMER_TRX_ID
AND RA_CUS_TRX_GL_DIST.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
AND RA_CUS_TRX_GL_DIST.ACCOUNT_CLASS = 'REV'
AND NVL (HCSU.STATUS, 'A') = 'A'
AND AR_PAY_SCH.CLASS NOT IN ('CM', 'PMT')
AND HCSU.SITE_USE_CODE = 'BILL_TO'
AND TRUNC (AR_PAY_SCH.GL_DATE) <= :P_ENTER_DATE
AND AR_PAY_SCH.TRX_DATE <= :P_ENTER_DATE
AND HZ_ACCT.ACCOUNT_NUMBER = :P_ENTER_ACCT_NUMBER
GROUP BY HZ_ACCT.ACCOUNT_NUMBER,AR_PAY_SCH.AMOUNT_DUE_REMAINING