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_LE

Source

View Text - Preformatted


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

View Text - HTML Formatted

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