VIEW: APPS.QPR_SR_CUSTOMER_HISTORY_V

Source

View Text - Preformatted

SELECT hca.cust_account_id, hps.party_site_id, fnd_profile.value('QPR_CURRENCY_CODE'), hca.account_name ,(hloc.address1 || decode(hloc.city, '', '', ('-' || hloc.city)) ||decode(hloc.postal_code, '', '', ('-' || hloc.postal_code)) ||decode(nvl(hloc.province,hloc.county), '','', ('-' ||nvl(hloc.province,hloc.county))) ||decode(nvl(hloc.province, hloc.state), '', '',('-' || nvl(hloc.province,hloc.state))) ||decode(hloc.country, '', '','-' || hloc.country)) , hca.customer_class_code ,nvl(hpa.CURR_FY_POTENTIAL_REVENUE,0) ,HZP_CUST_PKG.ARXVAMAI_OVERALL_CR_LIMIT(hca.cust_account_id, fnd_profile.value('QPR_CURRENCY_CODE'),hcsua.site_use_id) ,HZP_CUST_PKG.ARXVAMAI_ORDER_CR_LIMIT(hca.cust_account_id, fnd_profile.value('QPR_CURRENCY_CODE'),hcsua.site_use_id) ,hcp.risk_code ,hcp.account_status ,HZP_CUST_PKG.ARXVAMAI_OVERALL_CR_LIMIT(hca.cust_account_id, fnd_profile.value('QPR_CURRENCY_CODE'),hcsua.site_use_id) - nvl(ps.amount_due_remaining, 0) ,ps.past_due_transactions /*,AR_TA_CREDIT_PROFILE.get_average_days_late(hca.cust_account_id,hcsua.site_use_id) */ ,0 ,(select tbs.receipts_at_risk_value from ar_trx_bal_summary tbs where tbs.site_use_id = hcsua.site_use_id and tbs.currency = fnd_profile.value('QPR_CURRENCY_CODE') and tbs.cust_account_id = hca.cust_account_id and tbs.org_id = hcsua.org_id and rownum < 2 ) from hz_cust_accounts hca, hz_locations hloc, hz_parties hpa, hz_party_sites hps, hz_customer_profiles hcp, hz_cust_acct_sites_all hcasa, HZ_CUST_SITE_USES_ALL hcsua, (select ps1.customer_site_use_id as customer_site_use_id, sum(ps1.AMOUNT_DUE_REMAINING) as amount_due_remaining , sum(decode(ps1.status,'OP',decode(sign(trunc(sysdate) - trunc(nvl(ps1.due_date,sysdate))),1,1,0),0)) as past_due_transactions from ar_payment_schedules ps1 where nvl(ps1.receipt_confirmed_flag,'Y')='Y' group by ps1.customer_site_use_id, ps1.status) ps WHERE hca.party_id = hpa.party_id and hps.location_id = hloc.location_id and hcp.party_id = hpa.party_id and hcp.cust_account_id = hca.cust_account_id and hcp.site_use_id is null and hcsua.SITE_USE_CODE = 'BILL_TO' and hcsua.CUST_ACCT_SITE_ID = hcasa.CUST_ACCT_SITE_ID and hcasa.cust_account_id = hca.cust_account_id and hcasa.party_site_id = hps.party_site_id and ps.customer_site_use_id(+) = hcsua.site_use_id

View Text - HTML Formatted

SELECT HCA.CUST_ACCOUNT_ID
, HPS.PARTY_SITE_ID
, FND_PROFILE.VALUE('QPR_CURRENCY_CODE')
, HCA.ACCOUNT_NAME
, (HLOC.ADDRESS1 || DECODE(HLOC.CITY
, ''
, ''
, ('-' || HLOC.CITY)) ||DECODE(HLOC.POSTAL_CODE
, ''
, ''
, ('-' || HLOC.POSTAL_CODE)) ||DECODE(NVL(HLOC.PROVINCE
, HLOC.COUNTY)
, ''
, ''
, ('-' ||NVL(HLOC.PROVINCE
, HLOC.COUNTY))) ||DECODE(NVL(HLOC.PROVINCE
, HLOC.STATE)
, ''
, ''
, ('-' || NVL(HLOC.PROVINCE
, HLOC.STATE))) ||DECODE(HLOC.COUNTRY
, ''
, ''
, '-' || HLOC.COUNTRY))
, HCA.CUSTOMER_CLASS_CODE
, NVL(HPA.CURR_FY_POTENTIAL_REVENUE
, 0)
, HZP_CUST_PKG.ARXVAMAI_OVERALL_CR_LIMIT(HCA.CUST_ACCOUNT_ID
, FND_PROFILE.VALUE('QPR_CURRENCY_CODE')
, HCSUA.SITE_USE_ID)
, HZP_CUST_PKG.ARXVAMAI_ORDER_CR_LIMIT(HCA.CUST_ACCOUNT_ID
, FND_PROFILE.VALUE('QPR_CURRENCY_CODE')
, HCSUA.SITE_USE_ID)
, HCP.RISK_CODE
, HCP.ACCOUNT_STATUS
, HZP_CUST_PKG.ARXVAMAI_OVERALL_CR_LIMIT(HCA.CUST_ACCOUNT_ID
, FND_PROFILE.VALUE('QPR_CURRENCY_CODE')
, HCSUA.SITE_USE_ID) - NVL(PS.AMOUNT_DUE_REMAINING
, 0)
, PS.PAST_DUE_TRANSACTIONS /*
, AR_TA_CREDIT_PROFILE.GET_AVERAGE_DAYS_LATE(HCA.CUST_ACCOUNT_ID
, HCSUA.SITE_USE_ID) */
, 0
, (SELECT TBS.RECEIPTS_AT_RISK_VALUE
FROM AR_TRX_BAL_SUMMARY TBS
WHERE TBS.SITE_USE_ID = HCSUA.SITE_USE_ID
AND TBS.CURRENCY = FND_PROFILE.VALUE('QPR_CURRENCY_CODE')
AND TBS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND TBS.ORG_ID = HCSUA.ORG_ID
AND ROWNUM < 2 )
FROM HZ_CUST_ACCOUNTS HCA
, HZ_LOCATIONS HLOC
, HZ_PARTIES HPA
, HZ_PARTY_SITES HPS
, HZ_CUSTOMER_PROFILES HCP
, HZ_CUST_ACCT_SITES_ALL HCASA
, HZ_CUST_SITE_USES_ALL HCSUA
, (SELECT PS1.CUSTOMER_SITE_USE_ID AS CUSTOMER_SITE_USE_ID
, SUM(PS1.AMOUNT_DUE_REMAINING) AS AMOUNT_DUE_REMAINING
, SUM(DECODE(PS1.STATUS
, 'OP'
, DECODE(SIGN(TRUNC(SYSDATE) - TRUNC(NVL(PS1.DUE_DATE
, SYSDATE)))
, 1
, 1
, 0)
, 0)) AS PAST_DUE_TRANSACTIONS
FROM AR_PAYMENT_SCHEDULES PS1
WHERE NVL(PS1.RECEIPT_CONFIRMED_FLAG
, 'Y')='Y' GROUP BY PS1.CUSTOMER_SITE_USE_ID
, PS1.STATUS) PS
WHERE HCA.PARTY_ID = HPA.PARTY_ID
AND HPS.LOCATION_ID = HLOC.LOCATION_ID
AND HCP.PARTY_ID = HPA.PARTY_ID
AND HCP.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCP.SITE_USE_ID IS NULL
AND HCSUA.SITE_USE_CODE = 'BILL_TO'
AND HCSUA.CUST_ACCT_SITE_ID = HCASA.CUST_ACCT_SITE_ID
AND HCASA.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCASA.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND PS.CUSTOMER_SITE_USE_ID(+) = HCSUA.SITE_USE_ID