VIEW: APPS.HR_LOCATIONS_V

Source

View Text - Preformatted

SELECT L.TP_HEADER_ID , L.ECE_TP_LOCATION_CODE , L.ROWID ROW_ID , L.LOCATION_ID , TL.LOCATION_CODE , L.BILL_TO_SITE_FLAG , L.COUNTRY ,TL.DESCRIPTION , L.DESIGNATED_RECEIVER_ID , L.IN_ORGANIZATION_FLAG , L.INACTIVE_DATE , L.INVENTORY_ORGANIZATION_ID , L.OFFICE_SITE_FLAG , L.RECEIVING_SITE_FLAG , L.SHIP_TO_LOCATION_ID , L.SHIP_TO_SITE_FLAG , L.TAX_NAME , L.STYLE , HR1.MEANING SHIP_TO_SITE , HR2.MEANING BILL_TO_SITE , HR3.MEANING OFFICE_SITE , HR4.MEANING RECEIVING_SITE , HR5.MEANING INTERNAL_SITE , L2TL.LOCATION_CODE SHIP_TO_LOCATION , FND.DESCRIPTIVE_FLEX_CONTEXT_NAME , L.ENTERED_BY , L.ADDRESS_LINE_1 , L.ADDRESS_LINE_2 , L.ADDRESS_LINE_3 , 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.TOWN_OR_CITY , 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.LOC_INFORMATION13 , L.LOC_INFORMATION14 , L.LOC_INFORMATION15 , L.LOC_INFORMATION16 , L.LOC_INFORMATION17 , L.LOC_INFORMATION18 , L.LOC_INFORMATION19 , L.LOC_INFORMATION20 , L.OBJECT_VERSION_NUMBER , L.BUSINESS_GROUP_ID+0 ,L.LEGAL_ADDRESS_FLAG ,L.TIMEZONE_CODE ,DECODE(L.TIMEZONE_CODE, NULL, NULL, '(GMT ' || TO_CHAR(TRUNC(TZVL.GMT_OFFSET),'S09') || ':' || TO_CHAR(ABS(TZVL.GMT_OFFSET - TRUNC(TZVL.GMT_OFFSET)) * 60, 'FM900') || ') ' || TZVL.NAME) FROM HR_LOCATIONS_ALL L , HR_LOCATIONS_ALL_TL TL , HR_LOOKUPS HR1 , HR_LOOKUPS HR2 , HR_LOOKUPS HR3 , HR_LOOKUPS HR4 , HR_LOOKUPS HR5 , HR_LOCATIONS_ALL_TL L2TL , FND_DESCR_FLEX_CONTEXTS_TL FND , FND_TIMEZONES_VL TZVL WHERE HR1.LOOKUP_TYPE(+) = 'YES_NO' AND HR1.LOOKUP_CODE(+) = L.SHIP_TO_SITE_FLAG AND HR2.LOOKUP_TYPE(+) = 'YES_NO' AND HR2.LOOKUP_CODE(+) = L.BILL_TO_SITE_FLAG AND HR3.LOOKUP_TYPE(+) = 'YES_NO' AND HR3.LOOKUP_CODE(+) = L.OFFICE_SITE_FLAG AND HR4.LOOKUP_TYPE(+) = 'YES_NO' AND HR4.LOOKUP_CODE(+) = L.RECEIVING_SITE_FLAG AND HR5.LOOKUP_TYPE(+) = 'YES_NO' AND HR5.LOOKUP_CODE(+) = L.IN_ORGANIZATION_FLAG AND L2TL.LOCATION_ID (+) = L.SHIP_TO_LOCATION_ID AND L.STYLE = FND.DESCRIPTIVE_FLEX_CONTEXT_CODE (+) AND TZVL.TIMEZONE_CODE(+) = L.TIMEZONE_CODE AND TZVL.ENABLED_FLAG(+) = 'Y' AND FND.APPLICATION_ID (+) = 800 AND FND.DESCRIPTIVE_FLEXFIELD_NAME (+) = 'Address Location' AND FND.LANGUAGE (+) = userenv('LANG') AND L.LOCATION_ID = TL.LOCATION_ID AND TL.LANGUAGE = USERENV ('LANG') AND NVL( L.BUSINESS_GROUP_ID, NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID, -99) ) = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID,NVL( L.BUSINESS_GROUP_ID, -99)) AND DECODE(L2TL.location_id,null,'1',l2tl.language) =DECODE(L2tl.location_id,null,'1',userenv('LANG'))

View Text - HTML Formatted

SELECT L.TP_HEADER_ID
, L.ECE_TP_LOCATION_CODE
, L.ROWID ROW_ID
, L.LOCATION_ID
, TL.LOCATION_CODE
, L.BILL_TO_SITE_FLAG
, L.COUNTRY
, TL.DESCRIPTION
, L.DESIGNATED_RECEIVER_ID
, L.IN_ORGANIZATION_FLAG
, L.INACTIVE_DATE
, L.INVENTORY_ORGANIZATION_ID
, L.OFFICE_SITE_FLAG
, L.RECEIVING_SITE_FLAG
, L.SHIP_TO_LOCATION_ID
, L.SHIP_TO_SITE_FLAG
, L.TAX_NAME
, L.STYLE
, HR1.MEANING SHIP_TO_SITE
, HR2.MEANING BILL_TO_SITE
, HR3.MEANING OFFICE_SITE
, HR4.MEANING RECEIVING_SITE
, HR5.MEANING INTERNAL_SITE
, L2TL.LOCATION_CODE SHIP_TO_LOCATION
, FND.DESCRIPTIVE_FLEX_CONTEXT_NAME
, L.ENTERED_BY
, L.ADDRESS_LINE_1
, L.ADDRESS_LINE_2
, L.ADDRESS_LINE_3
, 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.TOWN_OR_CITY
, 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.LOC_INFORMATION13
, L.LOC_INFORMATION14
, L.LOC_INFORMATION15
, L.LOC_INFORMATION16
, L.LOC_INFORMATION17
, L.LOC_INFORMATION18
, L.LOC_INFORMATION19
, L.LOC_INFORMATION20
, L.OBJECT_VERSION_NUMBER
, L.BUSINESS_GROUP_ID+0
, L.LEGAL_ADDRESS_FLAG
, L.TIMEZONE_CODE
, DECODE(L.TIMEZONE_CODE
, NULL
, NULL
, '(GMT ' || TO_CHAR(TRUNC(TZVL.GMT_OFFSET)
, 'S09') || ':' || TO_CHAR(ABS(TZVL.GMT_OFFSET - TRUNC(TZVL.GMT_OFFSET)) * 60
, 'FM900') || ') ' || TZVL.NAME)
FROM HR_LOCATIONS_ALL L
, HR_LOCATIONS_ALL_TL TL
, HR_LOOKUPS HR1
, HR_LOOKUPS HR2
, HR_LOOKUPS HR3
, HR_LOOKUPS HR4
, HR_LOOKUPS HR5
, HR_LOCATIONS_ALL_TL L2TL
, FND_DESCR_FLEX_CONTEXTS_TL FND
, FND_TIMEZONES_VL TZVL
WHERE HR1.LOOKUP_TYPE(+) = 'YES_NO'
AND HR1.LOOKUP_CODE(+) = L.SHIP_TO_SITE_FLAG
AND HR2.LOOKUP_TYPE(+) = 'YES_NO'
AND HR2.LOOKUP_CODE(+) = L.BILL_TO_SITE_FLAG
AND HR3.LOOKUP_TYPE(+) = 'YES_NO'
AND HR3.LOOKUP_CODE(+) = L.OFFICE_SITE_FLAG
AND HR4.LOOKUP_TYPE(+) = 'YES_NO'
AND HR4.LOOKUP_CODE(+) = L.RECEIVING_SITE_FLAG
AND HR5.LOOKUP_TYPE(+) = 'YES_NO'
AND HR5.LOOKUP_CODE(+) = L.IN_ORGANIZATION_FLAG
AND L2TL.LOCATION_ID (+) = L.SHIP_TO_LOCATION_ID
AND L.STYLE = FND.DESCRIPTIVE_FLEX_CONTEXT_CODE (+)
AND TZVL.TIMEZONE_CODE(+) = L.TIMEZONE_CODE
AND TZVL.ENABLED_FLAG(+) = 'Y'
AND FND.APPLICATION_ID (+) = 800
AND FND.DESCRIPTIVE_FLEXFIELD_NAME (+) = 'ADDRESS LOCATION'
AND FND.LANGUAGE (+) = USERENV('LANG')
AND L.LOCATION_ID = TL.LOCATION_ID
AND TL.LANGUAGE = USERENV ('LANG')
AND NVL( L.BUSINESS_GROUP_ID
, NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, -99) ) = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, NVL( L.BUSINESS_GROUP_ID
, -99))
AND DECODE(L2TL.LOCATION_ID
, NULL
, '1'
, L2TL.LANGUAGE) =DECODE(L2TL.LOCATION_ID
, NULL
, '1'
, USERENV('LANG'))