View: AR_CUSTOMER_PAYMENT_SCHEDULES

Product:	AR - Receivables
Description:	Facilitate queries on sums across customers" invoices
Implementation/DBA Data:	APPS.AR_CUSTOMER_PAYMENT_SCHEDULES

View Text

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

Columns