SELECT
HZP.PARTY_NAME CUSTOMER_NAME ,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER ,
HCPA.OVERALL_CREDIT_LIMIT ,
RAT.NAME TERM_NAME ,
ARPS.INVOICE_CURRENCY_CODE ,
SUM(ARPS.AMOUNT_DUE_REMAINING) OUTSTANDAING_AMT ,
(HCPA.OVERALL_CREDIT_LIMIT-SUM(ARPS.AMOUNT_DUE_REMAINING)) EXPOSURE,
SUM (NVL ((
CASE
WHEN ARPS.DUE_DATE BETWEEN TRUNC(SYSDATE - 30) AND TRUNC(SYSDATE)
AND ARPS.CLASS <> 'PMT'
THEN AMOUNT_DUE_REMAINING
END ), 0 ) ) "0 - 30 days"
FROM HZ_CUST_PROFILE_AMTS HCPA,
HZ_CUSTOMER_PROFILES HCP,
HZ_CUST_ACCOUNTS_ALL HCA,
HZ_CUST_ACCT_SITES_ALL HCAS,
HZ_CUST_SITE_USES_ALL HCSU,
HZ_PARTIES HZP,
RA_TERMS_TL RAT,
AR_PAYMENT_SCHEDULES_ALL ARPS
WHERE 1 = 1
AND HCA.CUST_ACCOUNT_ID = HCP.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID = HCPA.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND HCSU.SITE_USE_CODE ='BILL_TO'
AND HCSU.STATUS ='A'
AND HCPA.SITE_USE_ID IS NULL
AND HCP.SITE_USE_ID IS NULL
AND HCPA.OVERALL_CREDIT_LIMIT <>0
AND ARPS.PAYMENT_SCHEDULE_ID >0
AND ARPS.AMOUNT_DUE_REMAINING <>0
AND HZP.PARTY_ID = HCA.PARTY_ID
AND HCSU.PAYMENT_TERM_ID = RAT.TERM_ID
AND HCA.CUST_ACCOUNT_ID = ARPS.CUSTOMER_ID
AND HCA.ACCOUNT_NUMBER IN ('138877','142380')
GROUP BY HCA.ACCOUNT_NUMBER ,
HZP.PARTY_NAME,
ARPS.INVOICE_CURRENCY_CODE,
RAT.NAME,
HCPA.OVERALL_CREDIT_LIMIT
HZP.PARTY_NAME CUSTOMER_NAME ,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER ,
HCPA.OVERALL_CREDIT_LIMIT ,
RAT.NAME TERM_NAME ,
ARPS.INVOICE_CURRENCY_CODE ,
SUM(ARPS.AMOUNT_DUE_REMAINING) OUTSTANDAING_AMT ,
(HCPA.OVERALL_CREDIT_LIMIT-SUM(ARPS.AMOUNT_DUE_REMAINING)) EXPOSURE,
SUM (NVL ((
CASE
WHEN ARPS.DUE_DATE BETWEEN TRUNC(SYSDATE - 30) AND TRUNC(SYSDATE)
AND ARPS.CLASS <> 'PMT'
THEN AMOUNT_DUE_REMAINING
END ), 0 ) ) "0 - 30 days"
FROM HZ_CUST_PROFILE_AMTS HCPA,
HZ_CUSTOMER_PROFILES HCP,
HZ_CUST_ACCOUNTS_ALL HCA,
HZ_CUST_ACCT_SITES_ALL HCAS,
HZ_CUST_SITE_USES_ALL HCSU,
HZ_PARTIES HZP,
RA_TERMS_TL RAT,
AR_PAYMENT_SCHEDULES_ALL ARPS
WHERE 1 = 1
AND HCA.CUST_ACCOUNT_ID = HCP.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID = HCPA.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID
AND HCSU.SITE_USE_CODE ='BILL_TO'
AND HCSU.STATUS ='A'
AND HCPA.SITE_USE_ID IS NULL
AND HCP.SITE_USE_ID IS NULL
AND HCPA.OVERALL_CREDIT_LIMIT <>0
AND ARPS.PAYMENT_SCHEDULE_ID >0
AND ARPS.AMOUNT_DUE_REMAINING <>0
AND HZP.PARTY_ID = HCA.PARTY_ID
AND HCSU.PAYMENT_TERM_ID = RAT.TERM_ID
AND HCA.CUST_ACCOUNT_ID = ARPS.CUSTOMER_ID
AND HCA.ACCOUNT_NUMBER IN ('138877','142380')
GROUP BY HCA.ACCOUNT_NUMBER ,
HZP.PARTY_NAME,
ARPS.INVOICE_CURRENCY_CODE,
RAT.NAME,
HCPA.OVERALL_CREDIT_LIMIT
0 comments