View: AR_CUSTOMER_ACCOUNTS

Product:	AR - Receivables
Description:	(Release 11.5 Only)
Implementation/DBA Data:	APPS.AR_CUSTOMER_ACCOUNTS

View Text

SELECT 'L'
, /* CUSTOMER_OR_LOCATION */ 'A'
, /* ACCTD_OR_ENTERED */ CUST_ACCT.CUST_ACCOUNT_ID
, /* CUSTOMER_ID */ SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, /* CUSTOMER_NAME */ CUST_ACCT.ACCOUNT_NUMBER
, /* CUSTOMER_NUMBER */ CUST_ACCT.STATUS
, /* CUSTOMER_STATUS */ CP_SITE.ACCOUNT_STATUS
, /* ACCOUNT_STATUS */ CPC.NAME
, /* PROFILE_CLASS */ CP_SITE.RISK_CODE
, /* RISK_CODE */ COL.NAME
, /* COLLECTOR_NAME */ ROUND( ( (SUM( DECODE(PS.CLASS
, 'INV'
, 1
, 'DM'
, 1
, 'CB'
, 1
, 'DEP'
, 1
, 'BR'
, 1
, /* 22-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */ 0) * PS.ACCTD_AMOUNT_DUE_REMAINING ) * MAX(SP.CER_DSO_DAYS) ) /* PICK AMOUNT_DUE_ORIGINAL ONLY WHEN THE TRX_DATE LIES BETWEEN SYSDATE
AND DSO_DAYS */ /* THE DENOMINATOR WOULD BE 1 WHEN NO RECORDS ARE FOUND MEETING THE DECODE CRITE RIA */ / DECODE( SUM( DECODE(PS.CLASS
, 'INV'
, 1
, 'DM'
, 1
, 'CB'
, 1
, 'DEP'
, 1
, 'BR'
, 1
, /* 22-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */ 0) * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS)
, - 1
, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED
, 0)) * NVL( PS.EXCHANGE_RATE
, 1 )
, 0))
, 0
, 1
, SUM( DECODE(PS.CLASS
, 'INV'
, 1
, 'DM'
, 1
, 'CB'
, 1
, 'DEP'
, 1
, 'BR'
, 1
, /* 22-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */ 0) * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS)
, -1
, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED
, 0)) * NVL(PS.EXCHANGE_RATE
, 1 )
, 0) ) ) )
, 0)
, /* DSO */ SU.SITE_USE_ID
, /* CUSTOMER_SITE_USE_ID */ SU.LOCATION
, /* LOCATION */ NULL
, /* CURRENCY_CODE */ 0
, /* OVERALL_CREDIT_LIMIT */ 0
, /* ORDER_CREDIT_LIMIT */ 0
, /* CREDIT_AVAILABLE */ SUM(DECODE(PS.STATUS
, 'OP'
, DECODE(SIGN(TRUNC(SYSDATE) - TRUNC(NVL(PS.DUE_DATE
, SYSDATE)))
, 1
, 1
, 0)
, 0))
, /* PASTDUE_INVOICES */ 0
, /* BALANCE */ SUM(NVL(PS.ACCTD_AMOUNT_DUE_REMAINING
, 0))
, /* ACCTD_BALANCE */ 0
, /* PASTDUE_BALANCE */ SUM(DECODE(SIGN(TRUNC(SYSDATE) - TRUNC(NVL(PS.DUE_DATE
, SYSDATE)))
, 1
, PS.ACCTD_AMOUNT_DUE_REMAINING
, 0))
, /* ACCTD_PASTDUE_BALANCE */ 0
, /* OPEN_CREDIT */ SUM(DECODE(SIGN(PS.ACCTD_AMOUNT_DUE_REMAINING)
, -1
, (PS.ACCTD_AMOUNT_DUE_REMAINING * -1)
, DECODE(PS.CLASS
, 'PMT'
, PS.ACCTD_AMOUNT_DUE_REMAINING
, 0))) /* ACCTD_OPEN_CREDIT */
FROM AR_SYSTEM_PARAMETERS SP
, AR_COLLECTORS COL
, HZ_CUST_PROFILE_CLASSES CPC
, HZ_CUSTOMER_PROFILES CP_CUST
, HZ_CUSTOMER_PROFILES CP_SITE
, AR_PAYMENT_SCHEDULES PS
, HZ_CUST_SITE_USES SU
, HZ_CUST_ACCT_SITES A
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
WHERE CUST_ACCT.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND A.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND SU.SITE_USE_ID = PS.CUSTOMER_SITE_USE_ID(+)
AND SU.SITE_USE_CODE IN ('BILL_TO'
, 'DRAWEE')
AND CP_CUST.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CP_CUST.SITE_USE_ID IS NULL
AND CP_SITE.SITE_USE_ID(+) = SU.SITE_USE_ID
AND COL.COLLECTOR_ID = NVL(CP_SITE.COLLECTOR_ID
, CP_CUST.COLLECTOR_ID)
AND CPC.PROFILE_CLASS_ID(+) = CP_SITE.PROFILE_CLASS_ID
AND NVL(PS.RECEIPT_CONFIRMED_FLAG(+)
, 'Y') = 'Y' GROUP BY CUST_ACCT.CUST_ACCOUNT_ID
, PARTY.PARTY_NAME
, CUST_ACCT.ACCOUNT_NUMBER
, CUST_ACCT.STATUS
, CP_SITE.ACCOUNT_STATUS
, CPC.NAME
, CP_SITE.RISK_CODE
, COL.NAME
, SU.SITE_USE_ID
, SU.LOCATION UNION ALL SELECT 'C'
, /* CUSTOMER_OR_LOCATION */ 'A'
, /* ACCTD_OR_ENTERED */ CUST_ACCT.CUST_ACCOUNT_ID
, /* CUSTOMER_ID */ SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, /* CUSTOMER_NAME */ CUST_ACCT.ACCOUNT_NUMBER
, /* CUSTOMER_NUMBER */ CUST_ACCT.STATUS
, /* CUSTOMER_STATUS */ CP.ACCOUNT_STATUS
, /* ACCOUNT_STATUS */ CPC.NAME
, /* PROFILE_CLASS */ CP.RISK_CODE
, /* RISK_CODE */ COL.NAME
, /* COLLECTOR_NAME */ ROUND( ( (SUM( DECODE(PS.CLASS
, 'INV'
, 1
, 'DM'
, 1
, 'CB'
, 1
, 'DEP'
, 1
, 'BR'
, 1
, /* 22-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */ 0) * PS.ACCTD_AMOUNT_DUE_REMAINING ) * MAX(SP.CER_DSO_DAYS) ) / DECODE( SUM( DECODE(PS.CLASS
, 'INV'
, 1
, 'DM'
, 1
, 'CB'
, 1
, 'DEP'
, 1
, 'BR'
, 1
, /* 22-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */ 0) * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS)
, -1
, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED
, 0)) * NVL(PS.EXCHANGE_RATE
, 1 )
, 0))
, 0
, 1
, SUM( DECODE(PS.CLASS
, 'INV'
, 1
, 'DM'
, 1
, 'CB'
, 1
, 'DEP'
, 1
, 'BR'
, 1
, /* 22-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */ 0) * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS)
, -1
, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED
, 0)) * NVL(PS.EXCHANGE_RATE
, 1 )
, 0) ) ) )
, 0)
, /* DSO */ -10
, /* CUSTOMER_SITE_USE_ID */ NULL
, /* LOCATION */ NULL
, /* CURRENCY_CODE */ 0
, /* OVERALL_CREDIT_LIMIT */ 0
, /* ORDER_CREDIT_LIMIT */ 0
, /* CREDIT_AVAILABLE */ SUM(DECODE(PS.STATUS
, 'OP'
, DECODE(SIGN(TRUNC(SYSDATE) - TRUNC(NVL(PS.DUE_DATE
, SYSDATE)) )
, 1
, 1
, 0)
, 0))
, /* PASTDUE_INVOICES */ 0
, /* BALANCE */ SUM(NVL(PS.ACCTD_AMOUNT_DUE_REMAINING
, 0))
, /* ACCTD_BALANCE */ 0
, /* PASTDUE_BALANCE */ SUM(DECODE(SIGN(TRUNC(SYSDATE) - TRUNC(NVL(PS.DUE_DATE
, SYSDATE)))
, 1
, PS.ACCTD_AMOUNT_DUE_REMAINING
, 0))
, /* ACCTD_PASTDUE_BALANCE */ 0
, /* OPEN_CREDIT */ SUM(DECODE(SIGN(PS.ACCTD_AMOUNT_DUE_REMAINING)
, -1
, (PS.ACCTD_AMOUNT_DUE_REMAINING * -1)
, DECODE(PS.CLASS
, 'PMT'
, PS.ACCTD_AMOUNT_DUE_REMAINING
, 0))) /* ACCTD_OPEN_CREDIT */
FROM AR_SYSTEM_PARAMETERS SP
, HZ_CUST_PROFILE_CLASSES CPC
, HZ_CUSTOMER_PROFILES CP
, AR_COLLECTORS COL
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_PAYMENT_SCHEDULES PS
WHERE CUST_ACCT.CUST_ACCOUNT_ID = PS.CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND CP.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CP.SITE_USE_ID IS NULL
AND COL.COLLECTOR_ID = CP.COLLECTOR_ID
AND CPC.PROFILE_CLASS_ID(+) = CP.PROFILE_CLASS_ID
AND NVL(PS.RECEIPT_CONFIRMED_FLAG(+)
, 'Y') = 'Y' GROUP BY CUST_ACCT.CUST_ACCOUNT_ID
, PARTY.PARTY_NAME
, CUST_ACCT.ACCOUNT_NUMBER
, CUST_ACCT.STATUS
, CP.ACCOUNT_STATUS
, CPC.NAME
, CP.RISK_CODE
, COL.NAME UNION ALL SELECT 'L'
, /* CUSTOMER_OR_LOCATION */ 'E'
, /* ACCTD_OR_ENTERED */ CUST_ACCT.CUST_ACCOUNT_ID
, /* CUSTOMER_ID */ SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, /* CUSTOMER_NAME */ CUST_ACCT.ACCOUNT_NUMBER
, /* CUSTOMER_NUMBER */ CUST_ACCT.STATUS
, /* CUSTOMER_STATUS */ CP_SITE.ACCOUNT_STATUS
, /* ACCOUNT_STATUS */ CPC.NAME
, /* PROFILE_CLASS */ CP_SITE.RISK_CODE
, /* RISK_CODE */ COL.NAME
, /* COLLECTOR_NAME */ ROUND(( (SUM( DECODE(PS.CLASS
, 'INV'
, 1
, 'DM'
, 1
, 'CB'
, 1
, 'DEP'
, 1
, 'BR'
, 1
, /* 22-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */ 0) * PS.ACCTD_AMOUNT_DUE_REMAINING ) * MAX(SP.CER_DSO_DAYS)) / DECODE(SUM(DECODE(PS.CLASS
, 'INV'
, 1
, 'DM'
, 1
, 'CB'
, 1
, 'DEP'
, 1
, 'BR'
, 1
, /* 22-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */ 0) * DECODE(SIGN(TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS)
, - 1
, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED
, 0)) * NVL(PS.EXCHANGE_RATE
, 1 )
, 0))
, 0
, 1
, SUM(DECODE(PS.CLASS
, 'INV'
, 1
, 'DM'
, 1
, 'CB'
, 1
, 'DEP'
, 1
, 'BR'
, 1
, /* 22-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */ 0) * DECODE(SIGN(TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS)
, -1
, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED
, 0)) * NVL(PS.EXCHANGE_RATE
, 1 )
, 0) )))
, 0)
, /* DSO */ SU.SITE_USE_ID
, /* CUSTOMER_SITE_USE_ID */ SU.LOCATION
, /* LOCATION */ PS.INVOICE_CURRENCY_CODE
, /* CURRENCY_CODE */ HZP_CUST_PKG.ARXVAMAI_OVERALL_CR_LIMIT (CUST_ACCT.CUST_ACCOUNT_ID
, PS.INVOICE_CURRENCY_CODE
, SU.SITE_USE_ID)
, /* OVERALL_CREDIT_LIMIT */ HZP_CUST_PKG.ARXVAMAI_ORDER_CR_LIMIT (CUST_ACCT.CUST_ACCOUNT_ID
, PS.INVOICE_CURRENCY_CODE
, SU.SITE_USE_ID)
, /* ORDER_CREDIT_LIMIT */ HZP_CUST_PKG.ARXVAMAI_OVERALL_CR_LIMIT (CUST_ACCT.CUST_ACCOUNT_ID
, PS.INVOICE_CURRENCY_CODE
, SU.SITE_USE_ID) - SUM(PS.AMOUNT_DUE_REMAINING)
, /* CREDIT_AVAILABLE */ SUM(DECODE (PS.STATUS
, 'OP'
, DECODE(SIGN(TRUNC(SYSDATE) - TRUNC(NVL(PS.DUE_DATE
, SYSDATE)))
, 1
, 1
, 0)
, 0))
, /* PASTDUE_INVOICES */ SUM(NVL(PS.AMOUNT_DUE_REMAINING
, 0))
, /* BALANCE */ SUM(NVL(PS.ACCTD_AMOUNT_DUE_REMAINING
, 0))
, /* ACCTD_BALANCE */ SUM(DECODE(SIGN(TRUNC(SYSDATE) - TRUNC(NVL(PS.DUE_DATE
, SYSDATE)))
, 1
, PS.AMOUNT_DUE_REMAINING
, 0))
, /* PASTDUE_BALANCE */ SUM(DECODE(SIGN(TRUNC(SYSDATE) - TRUNC(NVL(PS.DUE_DATE
, SYSDATE)))
, 1
, PS.ACCTD_AMOUNT_DUE_REMAINING
, 0))
, /* ACCTD_PASTDUE_BALANCE */ SUM(DECODE(SIGN(PS.AMOUNT_DUE_REMAINING)
, -1
, (PS.AMOUNT_DUE_REMAINING * -1)
, DECODE(PS.CLASS
, 'PMT'
, PS.AMOUNT_DUE_REMAINING
, 0)))
, /* OPEN_CREDIT */ SUM(DECODE(SIGN(PS.ACCTD_AMOUNT_DUE_REMAINING)
, -1
, (PS.ACCTD_AMOUNT_DUE_REMAINING * -1)
, DECODE(PS.CLASS
, 'PMT'
, PS.ACCTD_AMOUNT_DUE_REMAINING
, 0))) /* ACCTD_OPEN_CREDIT */
FROM AR_SYSTEM_PARAMETERS SP
, AR_COLLECTORS COL
, HZ_CUST_PROFILE_CLASSES CPC
, HZ_CUSTOMER_PROFILES CP_CUST
, HZ_CUSTOMER_PROFILES CP_SITE
, AR_PAYMENT_SCHEDULES PS
, HZ_CUST_SITE_USES SU
, HZ_CUST_ACCT_SITES A
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
WHERE CUST_ACCT.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND A.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND SU.SITE_USE_ID = PS.CUSTOMER_SITE_USE_ID(+)
AND SU.SITE_USE_CODE IN ('BILL_TO'
, 'DRAWEE') /* 22-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */
AND CP_CUST.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CP_CUST.SITE_USE_ID IS NULL
AND CP_SITE.SITE_USE_ID(+) = SU.SITE_USE_ID
AND COL.COLLECTOR_ID = NVL(CP_SITE.COLLECTOR_ID
, CP_CUST.COLLECTOR_ID)
AND CPC.PROFILE_CLASS_ID(+) = CP_SITE.PROFILE_CLASS_ID
AND NVL(PS.RECEIPT_CONFIRMED_FLAG(+)
, 'Y') = 'Y' GROUP BY CUST_ACCT.CUST_ACCOUNT_ID
, PARTY.PARTY_NAME
, CUST_ACCT.ACCOUNT_NUMBER
, CUST_ACCT.STATUS
, CP_SITE.ACCOUNT_STATUS
, CPC.NAME
, CP_SITE.RISK_CODE
, COL.NAME
, SU.SITE_USE_ID
, SU.LOCATION
, PS.INVOICE_CURRENCY_CODE UNION ALL SELECT 'C'
, /* CUSTOMER_OR_LOCATION */ 'E'
, /* ACCTD_OR_ENTERED */ CUST_ACCT.CUST_ACCOUNT_ID
, /* CUSTOMER_ID */ SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, /* CUSTOMER_NAME */ CUST_ACCT.ACCOUNT_NUMBER
, /* CUSTOMER_NUMBER */ CUST_ACCT.STATUS
, /* CUSTOMER_STATUS */ CP.ACCOUNT_STATUS
, /* ACCOUNT_STATUS */ CPC.NAME
, /* PROFILE_CLASS */ CP.RISK_CODE
, /* RISK_CODE */ COL.NAME
, /* COLLECTOR_NAME */ ROUND(((SUM(DECODE(PS.CLASS
, 'INV'
, 1
, 'DM'
, 1
, 'CB'
, 1
, 'DEP'
, 1
, 'BR'
, 1
, /* 22-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */ 0) * PS.ACCTD_AMOUNT_DUE_REMAINING) * MAX(SP.CER_DSO_DAYS)) / DECODE(SUM(DECODE(PS.CLASS
, 'INV'
, 1
, 'DM'
, 1
, 'CB'
, 1
, 'DEP'
, 1
, 'BR'
, 1
, /* 22-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */ 0) * DECODE(SIGN(TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS)
, -1
, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED
, 0)) * NVL( PS.EXCHANGE_RATE
, 1 )
, 0))
, 0
, 1
, SUM( DECODE(PS.CLASS
, 'INV'
, 1
, 'DM'
, 1
, 'CB'
, 1
, 'DEP'
, 1
, 'BR'
, 1
, /* 22-JUL-2000 J RAUTIAINEN BR IMPLEMENTATION */ 0) * DECODE(SIGN(TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS)
, -1
, (PS.AMOUNT_DUE_ORIGINAL + NVL(PS.AMOUNT_ADJUSTED
, 0)) * NVL(PS.EXCHANGE_RATE
, 1 )
, 0) ) ))
, 0)
, /* DSO */ -10
, /* CUSTOMER_SITE_USE_ID */ NULL
, /* LOCATION */ PS.INVOICE_CURRENCY_CODE
, /* CURRENCY_CODE */ HZP_CUST_PKG.ARXVAMAI_OVERALL_CR_LIMIT (CUST_ACCT.CUST_ACCOUNT_ID
, PS.INVOICE_CURRENCY_CODE
, NULL)
, /* OVERALL_CREDIT_LIMIT */ HZP_CUST_PKG.ARXVAMAI_ORDER_CR_LIMIT (CUST_ACCT.CUST_ACCOUNT_ID
, PS.INVOICE_CURRENCY_CODE
, NULL)
, /* ORDER_CREDIT_LIMIT */ HZP_CUST_PKG.ARXVAMAI_OVERALL_CR_LIMIT (CUST_ACCT.CUST_ACCOUNT_ID
, PS.INVOICE_CURRENCY_CODE
, NULL) - SUM(PS.AMOUNT_DUE_REMAINING)
, /* CREDIT_AVAILABLE */ SUM(DECODE(PS.STATUS
, 'OP'
, DECODE(SIGN(TRUNC(SYSDATE) - TRUNC(NVL(PS.DUE_DATE
, SYSDATE)))
, 1
, 1
, 0)
, 0))
, /* PASTDUE_INVOICES */ SUM( NVL( PS.AMOUNT_DUE_REMAINING
, 0))
, /* BALANCE */ SUM( NVL( PS.ACCTD_AMOUNT_DUE_REMAINING
, 0))
, /* ACCTD_BALANCE */ SUM( DECODE(SIGN(TRUNC(SYSDATE) - TRUNC(NVL(PS.DUE_DATE
, SYSDATE)))
, 1
, PS.AMOUNT_DUE_REMAINING
, 0))
, /* PASTDUE_BALANCE */ SUM(DECODE(SIGN(TRUNC(SYSDATE) - TRUNC(NVL(PS.DUE_DATE
, SYSDATE)))
, 1
, PS.ACCTD_AMOUNT_DUE_REMAINING
, 0))
, /* ACCTD_PASTDUE_BALANCE */ SUM(DECODE(SIGN(PS.AMOUNT_DUE_REMAINING)
, -1
, (PS.AMOUNT_DUE_REMAINING * -1)
, DECODE(PS.CLASS
, 'PMT'
, PS.AMOUNT_DUE_REMAINING
, 0)))
, /* OPEN_CREDIT */ SUM(DECODE(SIGN(PS.ACCTD_AMOUNT_DUE_REMAINING)
, -1
, (PS.ACCTD_AMOUNT_DUE_REMAINING * -1)
, DECODE(PS.CLASS
, 'PMT'
, PS.ACCTD_AMOUNT_DUE_REMAINING
, 0))) /* ACCTD_OPEN_CREDIT */
FROM AR_SYSTEM_PARAMETERS SP
, HZ_CUST_PROFILE_CLASSES CPC
, HZ_CUSTOMER_PROFILES CP
, AR_COLLECTORS COL
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_PAYMENT_SCHEDULES PS
WHERE CUST_ACCT.CUST_ACCOUNT_ID = PS.CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND CP.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CP.SITE_USE_ID IS NULL
AND COL.COLLECTOR_ID = CP.COLLECTOR_ID
AND CPC.PROFILE_CLASS_ID(+) = CP.PROFILE_CLASS_ID
AND NVL(PS.RECEIPT_CONFIRMED_FLAG(+)
, 'Y') = 'Y' GROUP BY CUST_ACCT.CUST_ACCOUNT_ID
, PARTY.PARTY_NAME
, CUST_ACCT.ACCOUNT_NUMBER
, CUST_ACCT.STATUS
, CP.ACCOUNT_STATUS
, CPC.NAME
, CP.RISK_CODE
, COL.NAME
, PS.INVOICE_CURRENCY_CODE

Columns