VIEW: APPS.QPR_SR_CG_HIER_V

Source

View Text - Preformatted

SELECT to_char(hca.cust_account_id) , nvl(substrb(hpa.party_name, 1, 50), qpr_sr_util.get_null_desc) || ':' || hca.account_number , hca.customer_class_code, hpa.curr_fy_potential_revenue, hpa.category_code, hgpc.name, hcp.credit_rating, to_char(qpr_sr_util.get_null_pk), qpr_sr_util.get_null_desc, null, null, null, null, null, to_char(QPR_SR_UTIL.get_all_cus_pk) , QPR_SR_UTIL.get_all_cus_desc , null, null, null, null, null, qpr_sr_util.get_max_date(qpr_sr_util.get_max_date(qpr_sr_util.get_max_date( trunc(hca.last_update_date,'dd'), trunc(hpa.last_update_date, 'dd')), trunc(hcp.last_update_date,'dd')), trunc(hgpc.last_update_date, 'dd')) FROM hz_cust_accounts hca, hz_parties hpa, hz_customer_profiles hcp, hz_cust_profile_classes hgpc WHERE hca.party_id = hpa.party_id AND ((nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'N' and nvl(hca.customer_type, 'C') <> 'I' ) or nvl(fnd_profile.value('QPR_INTERNAL_CUST'), 'N') = 'Y') AND hca.cust_account_id = hcp.cust_account_id (+) AND hca.party_id = hcp.party_id (+) AND hcp.site_use_id is null AND hcp.profile_class_id = hgpc.profile_class_id(+) AND decode(nvl(LOWER(fnd_profile.value('QPR_CUSTOMER_ATTRIBUTE')), '1'), '1', '1', 'attribute1', hca.attribute1, 'attribute2', hca.attribute2, 'attribute3', hca.attribute3, 'attribute4', hca.attribute4, 'attribute5', hca.attribute5, 'attribute6', hca.attribute6, 'attribute7', hca.attribute7, 'attribute8', hca.attribute8, 'attribute9', hca.attribute9, 'attribute10', hca.attribute10, 'attribute11', hca.attribute11, 'attribute12', hca.attribute12, 'attribute13', hca.attribute13, 'attribute14', hca.attribute14, 'attribute15', hca.attribute15, '2') = '1'

View Text - HTML Formatted

SELECT TO_CHAR(HCA.CUST_ACCOUNT_ID)
, NVL(SUBSTRB(HPA.PARTY_NAME
, 1
, 50)
, QPR_SR_UTIL.GET_NULL_DESC) || ':' || HCA.ACCOUNT_NUMBER
, HCA.CUSTOMER_CLASS_CODE
, HPA.CURR_FY_POTENTIAL_REVENUE
, HPA.CATEGORY_CODE
, HGPC.NAME
, HCP.CREDIT_RATING
, TO_CHAR(QPR_SR_UTIL.GET_NULL_PK)
, QPR_SR_UTIL.GET_NULL_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_CHAR(QPR_SR_UTIL.GET_ALL_CUS_PK)
, QPR_SR_UTIL.GET_ALL_CUS_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
, QPR_SR_UTIL.GET_MAX_DATE(QPR_SR_UTIL.GET_MAX_DATE(QPR_SR_UTIL.GET_MAX_DATE( TRUNC(HCA.LAST_UPDATE_DATE
, 'DD')
, TRUNC(HPA.LAST_UPDATE_DATE
, 'DD'))
, TRUNC(HCP.LAST_UPDATE_DATE
, 'DD'))
, TRUNC(HGPC.LAST_UPDATE_DATE
, 'DD'))
FROM HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HPA
, HZ_CUSTOMER_PROFILES HCP
, HZ_CUST_PROFILE_CLASSES HGPC
WHERE HCA.PARTY_ID = HPA.PARTY_ID
AND ((NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'N'
AND NVL(HCA.CUSTOMER_TYPE
, 'C') <> 'I' ) OR NVL(FND_PROFILE.VALUE('QPR_INTERNAL_CUST')
, 'N') = 'Y')
AND HCA.CUST_ACCOUNT_ID = HCP.CUST_ACCOUNT_ID (+)
AND HCA.PARTY_ID = HCP.PARTY_ID (+)
AND HCP.SITE_USE_ID IS NULL
AND HCP.PROFILE_CLASS_ID = HGPC.PROFILE_CLASS_ID(+)
AND DECODE(NVL(LOWER(FND_PROFILE.VALUE('QPR_CUSTOMER_ATTRIBUTE'))
, '1')
, '1'
, '1'
, 'ATTRIBUTE1'
, HCA.ATTRIBUTE1
, 'ATTRIBUTE2'
, HCA.ATTRIBUTE2
, 'ATTRIBUTE3'
, HCA.ATTRIBUTE3
, 'ATTRIBUTE4'
, HCA.ATTRIBUTE4
, 'ATTRIBUTE5'
, HCA.ATTRIBUTE5
, 'ATTRIBUTE6'
, HCA.ATTRIBUTE6
, 'ATTRIBUTE7'
, HCA.ATTRIBUTE7
, 'ATTRIBUTE8'
, HCA.ATTRIBUTE8
, 'ATTRIBUTE9'
, HCA.ATTRIBUTE9
, 'ATTRIBUTE10'
, HCA.ATTRIBUTE10
, 'ATTRIBUTE11'
, HCA.ATTRIBUTE11
, 'ATTRIBUTE12'
, HCA.ATTRIBUTE12
, 'ATTRIBUTE13'
, HCA.ATTRIBUTE13
, 'ATTRIBUTE14'
, HCA.ATTRIBUTE14
, 'ATTRIBUTE15'
, HCA.ATTRIBUTE15
, '2') = '1'