VIEW: APPS.AR_CUSTOMER_PAYMENT_SCHEDULES

Source

View Text - Preformatted

SELECT cust_acct.cust_account_id , decode(al.lookup_code, 'Y', ps.customer_site_use_id, '') , decode(al.lookup_code, 'Y', su.location, '') , substrb(party.party_name,1,50) , cust_acct.account_number , min(cust_acct.status) , ps.invoice_currency_code , nvl(sum(ps.amount_due_remaining)/ count(distinct cp.cust_account_profile_id),0) , nvl(sum(decode(sign(trunc(sysdate)- trunc(nvl(ps.due_date,sysdate))), 1, nvl(ps.amount_due_remaining,0), -1, 0, 0))/count(distinct cp.cust_account_profile_id) ,0) , al.lookup_code , cp.collector_id FROM ar_lookups al, hz_cust_site_uses su, ar_payment_schedules ps, hz_cust_accounts cust_acct, hz_parties party, hz_customer_profiles cp WHERE cust_acct.cust_account_id = cp.cust_account_id and cust_acct.party_id = party.party_id and cust_acct.cust_account_id = ps.customer_id and ps.customer_site_use_id = su.site_use_id(+) and al.lookup_type = 'YES/NO' group by cust_acct.cust_account_id , party.party_name , cust_acct.account_number , al.lookup_code , decode(al.lookup_code, 'Y', ps.customer_site_use_id, '') , decode(al.lookup_code, 'Y', su.location, '') , ps.invoice_currency_code , cp.collector_id

View Text - HTML Formatted

SELECT CUST_ACCT.CUST_ACCOUNT_ID
, DECODE(AL.LOOKUP_CODE
, 'Y'
, PS.CUSTOMER_SITE_USE_ID
, '')
, DECODE(AL.LOOKUP_CODE
, 'Y'
, SU.LOCATION
, '')
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, CUST_ACCT.ACCOUNT_NUMBER
, MIN(CUST_ACCT.STATUS)
, PS.INVOICE_CURRENCY_CODE
, NVL(SUM(PS.AMOUNT_DUE_REMAINING)/ COUNT(DISTINCT CP.CUST_ACCOUNT_PROFILE_ID)
, 0)
, NVL(SUM(DECODE(SIGN(TRUNC(SYSDATE)- TRUNC(NVL(PS.DUE_DATE
, SYSDATE)))
, 1
, NVL(PS.AMOUNT_DUE_REMAINING
, 0)
, -1
, 0
, 0))/COUNT(DISTINCT CP.CUST_ACCOUNT_PROFILE_ID)
, 0)
, AL.LOOKUP_CODE
, CP.COLLECTOR_ID
FROM AR_LOOKUPS AL
, HZ_CUST_SITE_USES SU
, AR_PAYMENT_SCHEDULES PS
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUSTOMER_PROFILES CP
WHERE CUST_ACCT.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = PS.CUSTOMER_ID
AND PS.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID(+)
AND AL.LOOKUP_TYPE = 'YES/NO' GROUP BY CUST_ACCT.CUST_ACCOUNT_ID
, PARTY.PARTY_NAME
, CUST_ACCT.ACCOUNT_NUMBER
, AL.LOOKUP_CODE
, DECODE(AL.LOOKUP_CODE
, 'Y'
, PS.CUSTOMER_SITE_USE_ID
, '')
, DECODE(AL.LOOKUP_CODE
, 'Y'
, SU.LOCATION
, '')
, PS.INVOICE_CURRENCY_CODE
, CP.COLLECTOR_ID