VIEW: APPS.MSD_SR_LOC_REG_V

Source

View Text - Preformatted

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, 8, -777, NULL system_attribute1, NULL system_attribute2, 1 dp_enabled_flag FROM dual UNION ALL SELECT to_char(hcs.site_use_id), substrb(party.party_name, 1, 50) || ':' || cust_acct.account_number || ':' || hcs.location || ':' || OTL.NAME || decode (cust_acct.customer_type, 'I', decode(mp.organization_code, NULL, NULL, ':' || mp.organization_code), NULL) level_value, substrb(party.party_name, 1, 50) || ':' || cust_acct.account_number || ':' || hcs.location || ':' || OTL.NAME || decode (cust_acct.customer_type, 'I', decode(mp.organization_code, NULL, NULL, ':' || mp.organization_code), NULL) level_value_desc, 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)))), NULL, NULL, NULL, NULL, NULL, 8, nvl(hcs.org_id, msd_sr_util.get_null_pk), flv.meaning system_attribute1, NULL system_attribute2, 1 dp_enabled_flag FROM fnd_lookup_values_vl flv, hz_cust_site_uses_all hcs, hz_parties party, 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, po_location_associations_all pla, mtl_parameters mp, /* Bug# 5370284 */ HR_ORGANIZATION_INFORMATION O, HR_ALL_ORGANIZATION_UNITS_TL OTL, (SELECT parameter_value FROM msd_setup_parameters WHERE parameter_name = 'MSD_CUSTOMER_ATTRIBUTE') filtercust WHERE flv.lookup_type = 'MSD_LEVEL_VALUE_DESC' AND flv.lookup_code = decode (cust_acct.customer_type, 'I', 'I', 'C') AND cust_acct.party_id = party.party_id 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) AND 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' /* Bug# 5370284 AND nvl(cust_acct.customer_type, 'C') = 'I' */ 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 hcs.cust_acct_site_id = pla.address_id(+) AND hcs.site_use_id = pla.site_use_id(+) AND pla.organization_id = mp.organization_id(+) AND OTL.ORGANIZATION_ID = hcs.ORG_ID AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID AND O.ORG_INFORMATION_CONTEXT = 'Operating Unit Information' AND OTL.LANGUAGE = userenv('LANG')

View Text - HTML Formatted

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
, 8
, -777
, NULL SYSTEM_ATTRIBUTE1
, NULL SYSTEM_ATTRIBUTE2
, 1 DP_ENABLED_FLAG
FROM DUAL UNION ALL SELECT TO_CHAR(HCS.SITE_USE_ID)
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) || ':' || CUST_ACCT.ACCOUNT_NUMBER || ':' || HCS.LOCATION || ':' || OTL.NAME || DECODE (CUST_ACCT.CUSTOMER_TYPE
, 'I'
, DECODE(MP.ORGANIZATION_CODE
, NULL
, NULL
, ':' || MP.ORGANIZATION_CODE)
, NULL) LEVEL_VALUE
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) || ':' || CUST_ACCT.ACCOUNT_NUMBER || ':' || HCS.LOCATION || ':' || OTL.NAME || DECODE (CUST_ACCT.CUSTOMER_TYPE
, 'I'
, DECODE(MP.ORGANIZATION_CODE
, NULL
, NULL
, ':' || MP.ORGANIZATION_CODE)
, NULL) LEVEL_VALUE_DESC
, 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))))
, NULL
, NULL
, NULL
, NULL
, NULL
, 8
, NVL(HCS.ORG_ID
, MSD_SR_UTIL.GET_NULL_PK)
, FLV.MEANING SYSTEM_ATTRIBUTE1
, NULL SYSTEM_ATTRIBUTE2
, 1 DP_ENABLED_FLAG
FROM FND_LOOKUP_VALUES_VL FLV
, HZ_CUST_SITE_USES_ALL HCS
, HZ_PARTIES PARTY
, 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
, PO_LOCATION_ASSOCIATIONS_ALL PLA
, MTL_PARAMETERS MP
, /* BUG# 5370284 */ HR_ORGANIZATION_INFORMATION O
, HR_ALL_ORGANIZATION_UNITS_TL OTL
, (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
WHERE FLV.LOOKUP_TYPE = 'MSD_LEVEL_VALUE_DESC'
AND FLV.LOOKUP_CODE = DECODE (CUST_ACCT.CUSTOMER_TYPE
, 'I'
, 'I'
, 'C')
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
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)
AND 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' /* BUG# 5370284
AND NVL(CUST_ACCT.CUSTOMER_TYPE
, 'C') = 'I' */
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 HCS.CUST_ACCT_SITE_ID = PLA.ADDRESS_ID(+)
AND HCS.SITE_USE_ID = PLA.SITE_USE_ID(+)
AND PLA.ORGANIZATION_ID = MP.ORGANIZATION_ID(+)
AND OTL.ORGANIZATION_ID = HCS.ORG_ID
AND O.ORGANIZATION_ID = OTL.ORGANIZATION_ID
AND O.ORG_INFORMATION_CONTEXT = 'OPERATING UNIT INFORMATION'
AND OTL.LANGUAGE = USERENV('LANG')