eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AR_CUSTOMER_ACCOUNTS_CE

Source

View Text - Preformatted


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

View Text - HTML Formatted

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