VIEW: APPS.MSD_SR_CUSTZONE_ZONE_V

Source

View Text - Preformatted

SELECT /* Bug# 5367784 to_char(hca.cust_account_id) || ':' || to_char(wrv.region_id) level_value_pk, nvl(substrb(hpa.party_name, 1, 50), msd_sr_util.get_null_desc) || ':' || hca.account_number || ':' || nvl(wrv.ZONE, msd_sr_util.get_null_desc) level_value, nvl(substrb(hpa.party_name, 1, 50), msd_sr_util.get_null_desc) || ':' || hca.account_number || ':' || nvl(wrv.ZONE, msd_sr_util.get_null_desc) level_value_desc, wrv.region_id, nvl(wrv.ZONE, msd_sr_util.get_null_desc), nvl(wrv.ZONE, msd_sr_util.get_null_desc), */ msd_sr_util.get_sr_custzone_pk(hlo.location_id, hca.cust_account_id, filterzone.parameter_value) level_value_pk, msd_sr_util.get_sr_custzone_desc(hlo.location_id, nvl(substrb(hpa.party_name, 1, 50) , msd_sr_util.get_null_desc) || ':' || hca.account_number, filterzone.parameter_value) level_value, msd_sr_util.get_sr_custzone_desc(hlo.location_id, nvl(substrb(hpa.party_name, 1, 50) , msd_sr_util.get_null_desc) || ':' || hca.account_number, filterzone.parameter_value) level_value_desc, msd_sr_util.get_sr_zone_pk1(hlo.location_id, filterzone.parameter_value) parent_value_pk, msd_sr_util.get_sr_zone_desc(hlo.location_id, filterzone.parameter_value) parent_value, msd_sr_util.get_sr_zone_desc(hlo.location_id, filterzone.parameter_value) parent_value_desc, NULL attribute1, NULL attribute2, NULL attribute3, NULL attribute4, NULL attribute5, to_char(NULL) system_attribute1, to_char(NULL) system_attribute2, 1 dp_enabled_flag FROM hz_cust_site_uses_all hcs, hz_cust_accounts hca, hz_parties hpa, hz_party_sites hps, hz_loc_assignments hla, hz_locations hlo, hz_cust_acct_sites_all hcl, /* Bug# 5367784 wsh_region_locations wrl, wsh_regions_v wrv, wsh_zone_regions wzr, */ (SELECT parameter_value FROM msd_setup_parameters WHERE parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust, (SELECT msd_sr_util.get_zone_attr parameter_value FROM dual) filterzone WHERE hca.party_id = hpa.party_id AND hcl.party_site_id = hps.party_site_id AND hlo.location_id = hps.location_id AND hlo.location_id = hla.location_id AND nvl(hcl.org_id, -99) = nvl(hla.org_id, -99) AND hcl.cust_acct_site_id = hcs.cust_acct_site_id AND hcl.cust_account_id = hca.cust_account_id AND hcs.site_use_code = 'SHIP_TO' /* Bug# 5367784 AND wrl.location_id(+) = hlo.location_id AND wrl.region_id = wzr.region_id(+) AND wzr.parent_region_id = wrv.region_id(+) AND wrv.region_type(+) = 10 */ AND decode(nvl(LOWER(filtercust.parameter_value), '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' /* Bug# 5367784 AND decode(nvl(LOWER(filterzone.parameter_value), '2'), 'attribute1', wrv.attribute1, 'attribute2', wrv.attribute2, 'attribute3', wrv.attribute3, 'attribute4', wrv.attribute4, 'attribute5', wrv.attribute5, 'attribute6', wrv.attribute6, 'attribute7', wrv.attribute7, 'attribute8', wrv.attribute8, 'attribute9', wrv.attribute9, 'attribute10', wrv.attribute10, 'attribute11', wrv.attribute11, 'attribute12', wrv.attribute12, 'attribute13', wrv.attribute13, 'attribute14', wrv.attribute14, 'attribute15', wrv.attribute15, '2') = '1' */ UNION SELECT to_char(msd_sr_util.get_null_pk) level_value_pk, msd_sr_util.get_null_desc level_value, NULL, msd_sr_util.get_null_pk parent_value_pk, msd_sr_util.get_null_desc parent_value, NULL, NULL attribute1, NULL attribute2, NULL attribute3, NULL attribute4, NULL attribute5, to_char(NULL) system_attribute1, to_char(NULL) system_attribute2, 1 dp_enabled_flag FROM dual

View Text - HTML Formatted

SELECT /* BUG# 5367784 TO_CHAR(HCA.CUST_ACCOUNT_ID) || ':' || TO_CHAR(WRV.REGION_ID) LEVEL_VALUE_PK
, NVL(SUBSTRB(HPA.PARTY_NAME
, 1
, 50)
, MSD_SR_UTIL.GET_NULL_DESC) || ':' || HCA.ACCOUNT_NUMBER || ':' || NVL(WRV.ZONE
, MSD_SR_UTIL.GET_NULL_DESC) LEVEL_VALUE
, NVL(SUBSTRB(HPA.PARTY_NAME
, 1
, 50)
, MSD_SR_UTIL.GET_NULL_DESC) || ':' || HCA.ACCOUNT_NUMBER || ':' || NVL(WRV.ZONE
, MSD_SR_UTIL.GET_NULL_DESC) LEVEL_VALUE_DESC
, WRV.REGION_ID
, NVL(WRV.ZONE
, MSD_SR_UTIL.GET_NULL_DESC)
, NVL(WRV.ZONE
, MSD_SR_UTIL.GET_NULL_DESC)
, */ MSD_SR_UTIL.GET_SR_CUSTZONE_PK(HLO.LOCATION_ID
, HCA.CUST_ACCOUNT_ID
, FILTERZONE.PARAMETER_VALUE) LEVEL_VALUE_PK
, MSD_SR_UTIL.GET_SR_CUSTZONE_DESC(HLO.LOCATION_ID
, NVL(SUBSTRB(HPA.PARTY_NAME
, 1
, 50)
, MSD_SR_UTIL.GET_NULL_DESC) || ':' || HCA.ACCOUNT_NUMBER
, FILTERZONE.PARAMETER_VALUE) LEVEL_VALUE
, MSD_SR_UTIL.GET_SR_CUSTZONE_DESC(HLO.LOCATION_ID
, NVL(SUBSTRB(HPA.PARTY_NAME
, 1
, 50)
, MSD_SR_UTIL.GET_NULL_DESC) || ':' || HCA.ACCOUNT_NUMBER
, FILTERZONE.PARAMETER_VALUE) LEVEL_VALUE_DESC
, MSD_SR_UTIL.GET_SR_ZONE_PK1(HLO.LOCATION_ID
, FILTERZONE.PARAMETER_VALUE) PARENT_VALUE_PK
, MSD_SR_UTIL.GET_SR_ZONE_DESC(HLO.LOCATION_ID
, FILTERZONE.PARAMETER_VALUE) PARENT_VALUE
, MSD_SR_UTIL.GET_SR_ZONE_DESC(HLO.LOCATION_ID
, FILTERZONE.PARAMETER_VALUE) PARENT_VALUE_DESC
, NULL ATTRIBUTE1
, NULL ATTRIBUTE2
, NULL ATTRIBUTE3
, NULL ATTRIBUTE4
, NULL ATTRIBUTE5
, TO_CHAR(NULL) SYSTEM_ATTRIBUTE1
, TO_CHAR(NULL) SYSTEM_ATTRIBUTE2
, 1 DP_ENABLED_FLAG
FROM HZ_CUST_SITE_USES_ALL HCS
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HPA
, HZ_PARTY_SITES HPS
, HZ_LOC_ASSIGNMENTS HLA
, HZ_LOCATIONS HLO
, HZ_CUST_ACCT_SITES_ALL HCL
, /* BUG# 5367784 WSH_REGION_LOCATIONS WRL
, WSH_REGIONS_V WRV
, WSH_ZONE_REGIONS WZR
, */ (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
, (SELECT MSD_SR_UTIL.GET_ZONE_ATTR PARAMETER_VALUE
FROM DUAL) FILTERZONE
WHERE HCA.PARTY_ID = HPA.PARTY_ID
AND HCL.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HLO.LOCATION_ID = HPS.LOCATION_ID
AND HLO.LOCATION_ID = HLA.LOCATION_ID
AND NVL(HCL.ORG_ID
, -99) = NVL(HLA.ORG_ID
, -99)
AND HCL.CUST_ACCT_SITE_ID = HCS.CUST_ACCT_SITE_ID
AND HCL.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCS.SITE_USE_CODE = 'SHIP_TO' /* BUG# 5367784
AND WRL.LOCATION_ID(+) = HLO.LOCATION_ID
AND WRL.REGION_ID = WZR.REGION_ID(+)
AND WZR.PARENT_REGION_ID = WRV.REGION_ID(+)
AND WRV.REGION_TYPE(+) = 10 */
AND DECODE(NVL(LOWER(FILTERCUST.PARAMETER_VALUE)
, '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' /* BUG# 5367784
AND DECODE(NVL(LOWER(FILTERZONE.PARAMETER_VALUE)
, '2')
, 'ATTRIBUTE1'
, WRV.ATTRIBUTE1
, 'ATTRIBUTE2'
, WRV.ATTRIBUTE2
, 'ATTRIBUTE3'
, WRV.ATTRIBUTE3
, 'ATTRIBUTE4'
, WRV.ATTRIBUTE4
, 'ATTRIBUTE5'
, WRV.ATTRIBUTE5
, 'ATTRIBUTE6'
, WRV.ATTRIBUTE6
, 'ATTRIBUTE7'
, WRV.ATTRIBUTE7
, 'ATTRIBUTE8'
, WRV.ATTRIBUTE8
, 'ATTRIBUTE9'
, WRV.ATTRIBUTE9
, 'ATTRIBUTE10'
, WRV.ATTRIBUTE10
, 'ATTRIBUTE11'
, WRV.ATTRIBUTE11
, 'ATTRIBUTE12'
, WRV.ATTRIBUTE12
, 'ATTRIBUTE13'
, WRV.ATTRIBUTE13
, 'ATTRIBUTE14'
, WRV.ATTRIBUTE14
, 'ATTRIBUTE15'
, WRV.ATTRIBUTE15
, '2') = '1' */ UNION SELECT TO_CHAR(MSD_SR_UTIL.GET_NULL_PK) LEVEL_VALUE_PK
, MSD_SR_UTIL.GET_NULL_DESC LEVEL_VALUE
, NULL
, MSD_SR_UTIL.GET_NULL_PK PARENT_VALUE_PK
, MSD_SR_UTIL.GET_NULL_DESC PARENT_VALUE
, NULL
, NULL ATTRIBUTE1
, NULL ATTRIBUTE2
, NULL ATTRIBUTE3
, NULL ATTRIBUTE4
, NULL ATTRIBUTE5
, TO_CHAR(NULL) SYSTEM_ATTRIBUTE1
, TO_CHAR(NULL) SYSTEM_ATTRIBUTE2
, 1 DP_ENABLED_FLAG
FROM DUAL