[Home] [Help]
SELECT bgrT.name business_group_name ,locT.location_code location_name ,locT.description location_description ,loc.address_line_1 address_line_1 ,loc.address_line_2 address_line_2 ,loc.address_line_3 address_line_3 ,loc.town_or_city city ,loc.region_1 county ,hr_bis.bis_decode_lookup('US_STATE',loc.region_2) state_name ,loc.region_2 state_code ,loc.country country ,loc.postal_code zip_code ,loc.telephone_number_1 telephone_number_1 ,loc.telephone_number_2 telephone_number_2 ,loc.telephone_number_3 telephone_number_3 ,loc.inactive_date inactive_date ,loc.style address_style ,loc.tax_name default_tax_code ,loc2T.location_code ship_to_location ,peo.full_name designated_receiver ,orgT.name inventory_organization ,'_LA:loc.bill_to_site_flag:HR_LOOKUPS:YES_NO:MEANING' bill_to_site_flag ,'_LA:loc.in_organization_flag:HR_LOOKUPS:YES_NO:MEANING' internal_site_flag ,'_LA:loc.receiving_site_flag:HR_LOOKUPS:YES_NO:MEANING' receiving_site_flag ,'_LA:loc.office_site_flag:HR_LOOKUPS:YES_NO:MEANING' office_site_flag ,'_LA:loc.ship_to_site_flag:HR_LOOKUPS:YES_NO:MEANING' ship_to_location_flag ,'_DF:PER:HR_LOCATIONS:loc' ,loc.business_group_id business_group_id ,loc.designated_receiver_id designated_receiver_id ,loc.inventory_organization_id inventory_organization_id ,loc.location_id location_id ,loc.ship_to_location_id ship_to_location_id ,loc.timezone_code timezone_code FROM hr_all_organization_units bgr ,hr_all_organization_units_tl bgrT ,hr_all_organization_units_tl orgT ,per_people_x peo ,hr_locations_all_tl loc2T ,hr_locations_all_tl locT ,hr_locations_all loc WHERE loc.location_id = locT.location_id AND locT.language = userenv('LANG') AND loc.business_group_id = bgr.organization_id (+) AND bgr.organization_id = bgrT.organization_id (+) AND decode(bgrT.organization_id, null,'X', bgrT.language) = decode(bgrT.organization_id, null,'X', userenv('LANG')) AND loc.ship_to_location_id = loc2T.location_id (+) AND loc2T.language (+) = userenv('LANG') AND loc.designated_receiver_id = peo.person_id (+) AND loc.inventory_organization_id = orgT.organization_id (+) AND orgT.language (+) = userenv('LANG') AND loc.style = 'US' AND (loc.business_group_id IS NULL OR hr_bis.get_sec_profile_bg_id IS NULL OR loc.business_group_id = hr_bis.get_sec_profile_bg_id) WITH READ ONLY
SELECT BGRT.NAME BUSINESS_GROUP_NAME
, LOCT.LOCATION_CODE LOCATION_NAME
, LOCT.DESCRIPTION LOCATION_DESCRIPTION
, LOC.ADDRESS_LINE_1 ADDRESS_LINE_1
, LOC.ADDRESS_LINE_2 ADDRESS_LINE_2
, LOC.ADDRESS_LINE_3 ADDRESS_LINE_3
, LOC.TOWN_OR_CITY CITY
, LOC.REGION_1 COUNTY
, HR_BIS.BIS_DECODE_LOOKUP('US_STATE'
, LOC.REGION_2) STATE_NAME
, LOC.REGION_2 STATE_CODE
, LOC.COUNTRY COUNTRY
, LOC.POSTAL_CODE ZIP_CODE
, LOC.TELEPHONE_NUMBER_1 TELEPHONE_NUMBER_1
, LOC.TELEPHONE_NUMBER_2 TELEPHONE_NUMBER_2
, LOC.TELEPHONE_NUMBER_3 TELEPHONE_NUMBER_3
, LOC.INACTIVE_DATE INACTIVE_DATE
, LOC.STYLE ADDRESS_STYLE
, LOC.TAX_NAME DEFAULT_TAX_CODE
, LOC2T.LOCATION_CODE SHIP_TO_LOCATION
, PEO.FULL_NAME DESIGNATED_RECEIVER
, ORGT.NAME INVENTORY_ORGANIZATION
, '_LA:LOC.BILL_TO_SITE_FLAG:HR_LOOKUPS:YES_NO:MEANING' BILL_TO_SITE_FLAG
, '_LA:LOC.IN_ORGANIZATION_FLAG:HR_LOOKUPS:YES_NO:MEANING' INTERNAL_SITE_FLAG
, '_LA:LOC.RECEIVING_SITE_FLAG:HR_LOOKUPS:YES_NO:MEANING' RECEIVING_SITE_FLAG
, '_LA:LOC.OFFICE_SITE_FLAG:HR_LOOKUPS:YES_NO:MEANING' OFFICE_SITE_FLAG
, '_LA:LOC.SHIP_TO_SITE_FLAG:HR_LOOKUPS:YES_NO:MEANING' SHIP_TO_LOCATION_FLAG
, '_DF:PER:HR_LOCATIONS:LOC'
, LOC.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, LOC.DESIGNATED_RECEIVER_ID DESIGNATED_RECEIVER_ID
, LOC.INVENTORY_ORGANIZATION_ID INVENTORY_ORGANIZATION_ID
, LOC.LOCATION_ID LOCATION_ID
, LOC.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, LOC.TIMEZONE_CODE TIMEZONE_CODE
FROM HR_ALL_ORGANIZATION_UNITS BGR
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, PER_PEOPLE_X PEO
, HR_LOCATIONS_ALL_TL LOC2T
, HR_LOCATIONS_ALL_TL LOCT
, HR_LOCATIONS_ALL LOC
WHERE LOC.LOCATION_ID = LOCT.LOCATION_ID
AND LOCT.LANGUAGE = USERENV('LANG')
AND LOC.BUSINESS_GROUP_ID = BGR.ORGANIZATION_ID (+)
AND BGR.ORGANIZATION_ID = BGRT.ORGANIZATION_ID (+)
AND DECODE(BGRT.ORGANIZATION_ID
, NULL
, 'X'
, BGRT.LANGUAGE) = DECODE(BGRT.ORGANIZATION_ID
, NULL
, 'X'
, USERENV('LANG'))
AND LOC.SHIP_TO_LOCATION_ID = LOC2T.LOCATION_ID (+)
AND LOC2T.LANGUAGE (+) = USERENV('LANG')
AND LOC.DESIGNATED_RECEIVER_ID = PEO.PERSON_ID (+)
AND LOC.INVENTORY_ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND LOC.STYLE = 'US'
AND (LOC.BUSINESS_GROUP_ID IS NULL OR HR_BIS.GET_SEC_PROFILE_BG_ID IS NULL OR LOC.BUSINESS_GROUP_ID = HR_BIS.GET_SEC_PROFILE_BG_ID) WITH READ ONLY
|
|
|
|