VIEW: APPS.MSD_SR_REG_COUNTRY_V

Source

View Text - Preformatted

SELECT distinct nvl(LOC.STATE, nvl(LOC.PROVINCE, nvl(LOC.COUNTY, nvl(LOC.CITY, LOC.COUNTRY)))) || '-' || LOC.COUNTRY, nvl(LOC.STATE, nvl(LOC.PROVINCE, nvl(LOC.COUNTY, nvl(LOC.CITY, LOC.COUNTRY)))), nvl(LOC.STATE, nvl(LOC.PROVINCE, nvl(LOC.COUNTY, nvl(LOC.CITY, LOC.COUNTRY)))), LOC.COUNTRY, nvl(bis.territory_short_name, LOC.COUNTRY), nvl(bis.description, LOC.COUNTRY), null, null, null, null, null FROM HZ_CUST_SITE_USES_ALL HCS, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN, HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE, fnd_territories_vl bis, (select parameter_value from msd_setup_parameters WHERE parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust WHERE ACCT_SITE.CUST_ACCT_SITE_ID = HCS.CUST_ACCT_SITE_ID and ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID and HCS.site_use_code = 'SHIP_TO' and bis.territory_code = LOC.COUNTRY and decode(nvl(lower(filtercust.parameter_value), '1'), '1', '1', 'attribute1', CUST_ACCT.ATTRIBUTE1, 'attribute2', CUST_ACCT.ATTRIBUTE2, 'attribute3', CUST_ACCT.ATTRIBUTE3, 'attribute4', CUST_ACCT.ATTRIBUTE4, 'attribute5', CUST_ACCT.ATTRIBUTE5, 'attribute6', CUST_ACCT.ATTRIBUTE6, 'attribute7', CUST_ACCT.ATTRIBUTE7, 'attribute8', CUST_ACCT.ATTRIBUTE8, 'attribute9', CUST_ACCT.ATTRIBUTE9, 'attribute10', CUST_ACCT.ATTRIBUTE10, 'attribute11', CUST_ACCT.ATTRIBUTE11, 'attribute12', CUST_ACCT.ATTRIBUTE12, 'attribute13', CUST_ACCT.ATTRIBUTE13, 'attribute14', CUST_ACCT.ATTRIBUTE14, 'attribute15', CUST_ACCT.ATTRIBUTE15, '2') = '1'and ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99) union select to_char(msd_sr_util.get_null_pk) , msd_sr_util.get_null_desc, msd_sr_util.get_null_desc, to_char(msd_sr_util.get_null_pk) , msd_sr_util.get_null_desc, msd_sr_util.get_null_desc, null, null, null, null, null from dual

View Text - HTML Formatted

SELECT DISTINCT NVL(LOC.STATE
, NVL(LOC.PROVINCE
, NVL(LOC.COUNTY
, NVL(LOC.CITY
, LOC.COUNTRY)))) || '-' || LOC.COUNTRY
, NVL(LOC.STATE
, NVL(LOC.PROVINCE
, NVL(LOC.COUNTY
, NVL(LOC.CITY
, LOC.COUNTRY))))
, NVL(LOC.STATE
, NVL(LOC.PROVINCE
, NVL(LOC.COUNTY
, NVL(LOC.CITY
, LOC.COUNTRY))))
, LOC.COUNTRY
, NVL(BIS.TERRITORY_SHORT_NAME
, LOC.COUNTRY)
, NVL(BIS.DESCRIPTION
, LOC.COUNTRY)
, NULL
, NULL
, NULL
, NULL
, NULL
FROM HZ_CUST_SITE_USES_ALL HCS
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOC_ASSIGNMENTS LOC_ASSIGN
, HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, FND_TERRITORIES_VL BIS
, (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
WHERE ACCT_SITE.CUST_ACCT_SITE_ID = HCS.CUST_ACCT_SITE_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND HCS.SITE_USE_CODE = 'SHIP_TO'
AND BIS.TERRITORY_CODE = LOC.COUNTRY
AND DECODE(NVL(LOWER(FILTERCUST.PARAMETER_VALUE)
, '1')
, '1'
, '1'
, 'ATTRIBUTE1'
, CUST_ACCT.ATTRIBUTE1
, 'ATTRIBUTE2'
, CUST_ACCT.ATTRIBUTE2
, 'ATTRIBUTE3'
, CUST_ACCT.ATTRIBUTE3
, 'ATTRIBUTE4'
, CUST_ACCT.ATTRIBUTE4
, 'ATTRIBUTE5'
, CUST_ACCT.ATTRIBUTE5
, 'ATTRIBUTE6'
, CUST_ACCT.ATTRIBUTE6
, 'ATTRIBUTE7'
, CUST_ACCT.ATTRIBUTE7
, 'ATTRIBUTE8'
, CUST_ACCT.ATTRIBUTE8
, 'ATTRIBUTE9'
, CUST_ACCT.ATTRIBUTE9
, 'ATTRIBUTE10'
, CUST_ACCT.ATTRIBUTE10
, 'ATTRIBUTE11'
, CUST_ACCT.ATTRIBUTE11
, 'ATTRIBUTE12'
, CUST_ACCT.ATTRIBUTE12
, 'ATTRIBUTE13'
, CUST_ACCT.ATTRIBUTE13
, 'ATTRIBUTE14'
, CUST_ACCT.ATTRIBUTE14
, 'ATTRIBUTE15'
, CUST_ACCT.ATTRIBUTE15
, '2') = '1'AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
, -99) = NVL(LOC_ASSIGN.ORG_ID
, -99) UNION SELECT TO_CHAR(MSD_SR_UTIL.GET_NULL_PK)
, MSD_SR_UTIL.GET_NULL_DESC
, MSD_SR_UTIL.GET_NULL_DESC
, TO_CHAR(MSD_SR_UTIL.GET_NULL_PK)
, MSD_SR_UTIL.GET_NULL_DESC
, MSD_SR_UTIL.GET_NULL_DESC
, NULL
, NULL
, NULL
, NULL
, NULL
FROM DUAL