VIEW: APPS.WSH_HR_LOCATIONS_V

Source

View Text - Preformatted

SELECT L.LOCATION_ID, TL.LOCATION_CODE, 'HR' LOCATION_USE, L.BUSINESS_GROUP_ID, TL.DESCRIPTION, L.SHIP_TO_LOCATION_ID, L.SHIP_TO_SITE_FLAG, L.RECEIVING_SITE_FLAG, L.BILL_TO_SITE_FLAG, L.IN_ORGANIZATION_FLAG, L.OFFICE_SITE_FLAG, L.DESIGNATED_RECEIVER_ID, L.INVENTORY_ORGANIZATION_ID, L.TAX_NAME, L.INACTIVE_DATE, L.STYLE , L.ADDRESS_LINE_1, L.ADDRESS_LINE_2, L.ADDRESS_LINE_3, L.TOWN_OR_CITY, L.COUNTRY, L.POSTAL_CODE, L.REGION_1, L.REGION_2, L.REGION_3, L.TELEPHONE_NUMBER_1, L.TELEPHONE_NUMBER_2, L.TELEPHONE_NUMBER_3, L.LOC_INFORMATION13, L.LOC_INFORMATION14, L.LOC_INFORMATION15, L.LOC_INFORMATION16, L.LOC_INFORMATION17, L.LOC_INFORMATION18, L.LOC_INFORMATION19, L.LOC_INFORMATION20, L.ATTRIBUTE_CATEGORY, L.ATTRIBUTE1, L.ATTRIBUTE2, L.ATTRIBUTE3, L.ATTRIBUTE4, L.ATTRIBUTE5, L.ATTRIBUTE6, L.ATTRIBUTE7, L.ATTRIBUTE8, L.ATTRIBUTE9, L.ATTRIBUTE10, L.ATTRIBUTE11, L.ATTRIBUTE12, L.ATTRIBUTE13, L.ATTRIBUTE14, L.ATTRIBUTE15, L.ATTRIBUTE16, L.ATTRIBUTE17, L.ATTRIBUTE18, L.ATTRIBUTE19, L.ATTRIBUTE20, L.GLOBAL_ATTRIBUTE_CATEGORY, L.GLOBAL_ATTRIBUTE1, L.GLOBAL_ATTRIBUTE2, L.GLOBAL_ATTRIBUTE3, L.GLOBAL_ATTRIBUTE4, L.GLOBAL_ATTRIBUTE5, L.GLOBAL_ATTRIBUTE6, L.GLOBAL_ATTRIBUTE7, L.GLOBAL_ATTRIBUTE8, L.GLOBAL_ATTRIBUTE9, L.GLOBAL_ATTRIBUTE10, L.GLOBAL_ATTRIBUTE11, L.GLOBAL_ATTRIBUTE12, L.GLOBAL_ATTRIBUTE13, L.GLOBAL_ATTRIBUTE14, L.GLOBAL_ATTRIBUTE15, L.GLOBAL_ATTRIBUTE16, L.GLOBAL_ATTRIBUTE17, L.GLOBAL_ATTRIBUTE18, L.GLOBAL_ATTRIBUTE19, L.GLOBAL_ATTRIBUTE20, L.LAST_UPDATE_DATE, L.LAST_UPDATED_BY, L.LAST_UPDATE_LOGIN, L.CREATED_BY, L.CREATION_DATE, L.ENTERED_BY, L.TP_HEADER_ID, L.ECE_TP_LOCATION_CODE, L.OBJECT_VERSION_NUMBER, substrb(nvl(town_or_city, address_line_1), 1,59-lengthb(to_char(L.location_id))) ||':'|| to_char(L.location_id) UI_LOCATION_CODE FROM HR_LOCATIONS_ALL L, HR_LOCATIONS_ALL_TL TL WHERE nvl (L.business_group_id, nvl(hr_general.get_business_group_id, -99) ) = nvl (hr_general.get_business_group_id, -99) and L.location_id = TL.location_id and TL.language = userenv('LANG') UNION select hz.location_id location_id, substr(hz.location_id,1,15) location_code , 'TCA' location_use, to_number(null) business_group_id, substr(hz.address1,1,50) description, hz.location_id ship_to_location_id, 'Y' ship_to_site_flag, 'Y' receiving_site_flag, 'N' bill_to_site_flag, 'N' in_organization_flag, 'N' office_site_flag, to_number(null) designated_receiver_id, to_number(null) inventory_organization_id, null tax_name, hz.address_expiration_date inactive_date , decode (hz.address_style, 'US', 'US', 'GENERIC') style, hz.address1 address_line_1, hz.address2 address_line_2, hz.address3 address_line_3, hz.city town_or_city, hz.country country, hz.postal_code postal_code, hz.county region_1, hz.state region_2, hz.province region_3, null telephone_number_1, null telephone_number_2, null telephone_number_3, hz.address4 loc_information13, null loc_information14, null loc_information15, null loc_information16, null loc_information17, null loc_information18, null loc_information19, null loc_information20 , null attribute_category, null attribute1, null attribute2 , null attribute3 , null attribute4, null attribute5, null attribute6, null attribute7, null attribute8, null attribute9, null attribute10, null attribute11, null attribute12, null attribute13, null attribute14, null attribute15 , null attribute16, null attribute17, null attribute18, null attribute19, null attribute20, null global_attribute_category, null global_attribute1, null global_attribute2, null global_attribute3, null global_attribute4, null global_attribute5, null global_attribute6, null global_attribute7, null global_attribute8 , null global_attribute9 , null global_attribute10, null global_attribute11 , null global_attribute12, null global_attribute13, null global_attribute14, null global_attribute15, null global_attribute16, null global_attribute17, null global_attribute18, null global_attribute19, null global_attribute20, hz.last_update_date last_update_date, hz.last_updated_by last_updated_by, hz.last_update_login last_update_login, hz.created_by created_by, hz.creation_date creation_date, hz.created_by entered_by, to_number(null) tp_header_id, null ece_tp_location_code, 1 object_version_number, substrb(nvl(hz.city, hz.address1), 1,59-lengthb(to_char(location_id))) ||':'|| to_char(location_id) UI_LOCATION_CODE from hz_locations hz

View Text - HTML Formatted

SELECT L.LOCATION_ID
, TL.LOCATION_CODE
, 'HR' LOCATION_USE
, L.BUSINESS_GROUP_ID
, TL.DESCRIPTION
, L.SHIP_TO_LOCATION_ID
, L.SHIP_TO_SITE_FLAG
, L.RECEIVING_SITE_FLAG
, L.BILL_TO_SITE_FLAG
, L.IN_ORGANIZATION_FLAG
, L.OFFICE_SITE_FLAG
, L.DESIGNATED_RECEIVER_ID
, L.INVENTORY_ORGANIZATION_ID
, L.TAX_NAME
, L.INACTIVE_DATE
, L.STYLE
, L.ADDRESS_LINE_1
, L.ADDRESS_LINE_2
, L.ADDRESS_LINE_3
, L.TOWN_OR_CITY
, L.COUNTRY
, L.POSTAL_CODE
, L.REGION_1
, L.REGION_2
, L.REGION_3
, L.TELEPHONE_NUMBER_1
, L.TELEPHONE_NUMBER_2
, L.TELEPHONE_NUMBER_3
, L.LOC_INFORMATION13
, L.LOC_INFORMATION14
, L.LOC_INFORMATION15
, L.LOC_INFORMATION16
, L.LOC_INFORMATION17
, L.LOC_INFORMATION18
, L.LOC_INFORMATION19
, L.LOC_INFORMATION20
, L.ATTRIBUTE_CATEGORY
, L.ATTRIBUTE1
, L.ATTRIBUTE2
, L.ATTRIBUTE3
, L.ATTRIBUTE4
, L.ATTRIBUTE5
, L.ATTRIBUTE6
, L.ATTRIBUTE7
, L.ATTRIBUTE8
, L.ATTRIBUTE9
, L.ATTRIBUTE10
, L.ATTRIBUTE11
, L.ATTRIBUTE12
, L.ATTRIBUTE13
, L.ATTRIBUTE14
, L.ATTRIBUTE15
, L.ATTRIBUTE16
, L.ATTRIBUTE17
, L.ATTRIBUTE18
, L.ATTRIBUTE19
, L.ATTRIBUTE20
, L.GLOBAL_ATTRIBUTE_CATEGORY
, L.GLOBAL_ATTRIBUTE1
, L.GLOBAL_ATTRIBUTE2
, L.GLOBAL_ATTRIBUTE3
, L.GLOBAL_ATTRIBUTE4
, L.GLOBAL_ATTRIBUTE5
, L.GLOBAL_ATTRIBUTE6
, L.GLOBAL_ATTRIBUTE7
, L.GLOBAL_ATTRIBUTE8
, L.GLOBAL_ATTRIBUTE9
, L.GLOBAL_ATTRIBUTE10
, L.GLOBAL_ATTRIBUTE11
, L.GLOBAL_ATTRIBUTE12
, L.GLOBAL_ATTRIBUTE13
, L.GLOBAL_ATTRIBUTE14
, L.GLOBAL_ATTRIBUTE15
, L.GLOBAL_ATTRIBUTE16
, L.GLOBAL_ATTRIBUTE17
, L.GLOBAL_ATTRIBUTE18
, L.GLOBAL_ATTRIBUTE19
, L.GLOBAL_ATTRIBUTE20
, L.LAST_UPDATE_DATE
, L.LAST_UPDATED_BY
, L.LAST_UPDATE_LOGIN
, L.CREATED_BY
, L.CREATION_DATE
, L.ENTERED_BY
, L.TP_HEADER_ID
, L.ECE_TP_LOCATION_CODE
, L.OBJECT_VERSION_NUMBER
, SUBSTRB(NVL(TOWN_OR_CITY
, ADDRESS_LINE_1)
, 1
, 59-LENGTHB(TO_CHAR(L.LOCATION_ID))) ||':'|| TO_CHAR(L.LOCATION_ID) UI_LOCATION_CODE
FROM HR_LOCATIONS_ALL L
, HR_LOCATIONS_ALL_TL TL
WHERE NVL (L.BUSINESS_GROUP_ID
, NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, -99) ) = NVL (HR_GENERAL.GET_BUSINESS_GROUP_ID
, -99)
AND L.LOCATION_ID = TL.LOCATION_ID
AND TL.LANGUAGE = USERENV('LANG') UNION SELECT HZ.LOCATION_ID LOCATION_ID
, SUBSTR(HZ.LOCATION_ID
, 1
, 15) LOCATION_CODE
, 'TCA' LOCATION_USE
, TO_NUMBER(NULL) BUSINESS_GROUP_ID
, SUBSTR(HZ.ADDRESS1
, 1
, 50) DESCRIPTION
, HZ.LOCATION_ID SHIP_TO_LOCATION_ID
, 'Y' SHIP_TO_SITE_FLAG
, 'Y' RECEIVING_SITE_FLAG
, 'N' BILL_TO_SITE_FLAG
, 'N' IN_ORGANIZATION_FLAG
, 'N' OFFICE_SITE_FLAG
, TO_NUMBER(NULL) DESIGNATED_RECEIVER_ID
, TO_NUMBER(NULL) INVENTORY_ORGANIZATION_ID
, NULL TAX_NAME
, HZ.ADDRESS_EXPIRATION_DATE INACTIVE_DATE
, DECODE (HZ.ADDRESS_STYLE
, 'US'
, 'US'
, 'GENERIC') STYLE
, HZ.ADDRESS1 ADDRESS_LINE_1
, HZ.ADDRESS2 ADDRESS_LINE_2
, HZ.ADDRESS3 ADDRESS_LINE_3
, HZ.CITY TOWN_OR_CITY
, HZ.COUNTRY COUNTRY
, HZ.POSTAL_CODE POSTAL_CODE
, HZ.COUNTY REGION_1
, HZ.STATE REGION_2
, HZ.PROVINCE REGION_3
, NULL TELEPHONE_NUMBER_1
, NULL TELEPHONE_NUMBER_2
, NULL TELEPHONE_NUMBER_3
, HZ.ADDRESS4 LOC_INFORMATION13
, NULL LOC_INFORMATION14
, NULL LOC_INFORMATION15
, NULL LOC_INFORMATION16
, NULL LOC_INFORMATION17
, NULL LOC_INFORMATION18
, NULL LOC_INFORMATION19
, NULL LOC_INFORMATION20
, NULL ATTRIBUTE_CATEGORY
, NULL ATTRIBUTE1
, NULL ATTRIBUTE2
, NULL ATTRIBUTE3
, NULL ATTRIBUTE4
, NULL ATTRIBUTE5
, NULL ATTRIBUTE6
, NULL ATTRIBUTE7
, NULL ATTRIBUTE8
, NULL ATTRIBUTE9
, NULL ATTRIBUTE10
, NULL ATTRIBUTE11
, NULL ATTRIBUTE12
, NULL ATTRIBUTE13
, NULL ATTRIBUTE14
, NULL ATTRIBUTE15
, NULL ATTRIBUTE16
, NULL ATTRIBUTE17
, NULL ATTRIBUTE18
, NULL ATTRIBUTE19
, NULL ATTRIBUTE20
, NULL GLOBAL_ATTRIBUTE_CATEGORY
, NULL GLOBAL_ATTRIBUTE1
, NULL GLOBAL_ATTRIBUTE2
, NULL GLOBAL_ATTRIBUTE3
, NULL GLOBAL_ATTRIBUTE4
, NULL GLOBAL_ATTRIBUTE5
, NULL GLOBAL_ATTRIBUTE6
, NULL GLOBAL_ATTRIBUTE7
, NULL GLOBAL_ATTRIBUTE8
, NULL GLOBAL_ATTRIBUTE9
, NULL GLOBAL_ATTRIBUTE10
, NULL GLOBAL_ATTRIBUTE11
, NULL GLOBAL_ATTRIBUTE12
, NULL GLOBAL_ATTRIBUTE13
, NULL GLOBAL_ATTRIBUTE14
, NULL GLOBAL_ATTRIBUTE15
, NULL GLOBAL_ATTRIBUTE16
, NULL GLOBAL_ATTRIBUTE17
, NULL GLOBAL_ATTRIBUTE18
, NULL GLOBAL_ATTRIBUTE19
, NULL GLOBAL_ATTRIBUTE20
, HZ.LAST_UPDATE_DATE LAST_UPDATE_DATE
, HZ.LAST_UPDATED_BY LAST_UPDATED_BY
, HZ.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, HZ.CREATED_BY CREATED_BY
, HZ.CREATION_DATE CREATION_DATE
, HZ.CREATED_BY ENTERED_BY
, TO_NUMBER(NULL) TP_HEADER_ID
, NULL ECE_TP_LOCATION_CODE
, 1 OBJECT_VERSION_NUMBER
, SUBSTRB(NVL(HZ.CITY
, HZ.ADDRESS1)
, 1
, 59-LENGTHB(TO_CHAR(LOCATION_ID))) ||':'|| TO_CHAR(LOCATION_ID) UI_LOCATION_CODE
FROM HZ_LOCATIONS HZ