Oracle apps R12 Query to get the Credit Memo Balance Due

Posted by

The below given query provides the SQL Data extract for Credit Memo Balance Due.

SELECT HZ_ACCT.ACCOUNT_NUMBER, NVL (SUM (AR_PAY_SCH.AMOUNT_DUE_REMAINING), 0) CM_BALANCE_DUE
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 = 'CM'
AND AR_PAY_SCH.status = 'OP'
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