SELECT hrlt1.location_code Location_code
, hrlt1.description Description
, decode(hrl1.ship_to_site_flag,'Y', :yes, :no) Ship_to
, decode(hrl1.bill_to_site_flag,'Y', :yes, :no) Bill_to
, decode(hrl1.office_site_flag,'Y', :yes, :no) Office
, decode(hrl1.in_organization_flag,'N', :no, :yes) Internal
, decode(hrl1.receiving_site_flag,'Y', :yes, :no) Receiving_site
, hrl1.inactive_date Inactive_date
, ood.organization_name Inventory_Organization
, hrlt2.location_code Ship_to_location
, hrl1.tax_name Tax
, hrl1.address_line_1 Address
, hrl1.address_line_2 Address2
, hrl1.address_line_3 Address3
, hrl1.town_or_city City
, hrl1.region_1 State
, hrl1.region_2 Province
, hrl1.postal_code Zip
, hrl1.country Country
FROM org_organization_definitions ood
, hr_locations_all hrl2
, hr_locations_all_tl hrlt2
, hr_locations_all hrl1
, hr_locations_all_tl hrlt1
WHERE (decode(:P_active_inactive,'ACTIVE',nvl(hrl1.inactive_date,sysdate+1)-sysdate,
'INACTIVE',sysdate-nvl(hrl1.inactive_date,sysdate+1),1) > 0
OR :P_active_inactive = 'BOTH')
AND ood.organization_id (+) = hrl1.inventory_organization_id
AND nvl (hrl1.business_group_id, nvl(hr_general.get_business_group_id, -99) ) =
nvl (hr_general.get_business_group_id, -99)
AND hrl1.location_id = hrlt1.location_id
AND hrlt1.language = userenv('LANG')
AND hrl2.location_id (+) = hrl1.ship_to_location_id
AND nvl (hrl2.business_group_id, nvl(hr_general.get_business_group_id, -99) ) =
nvl (hr_general.get_business_group_id, -99)
AND hrl2.location_id = hrlt2.location_id (+)
AND hrlt2.language (+) = userenv('LANG')
AND ((hrl1.ship_to_site_flag = decode(:P_site,'SHIP-TO','Y','FAIL'))
OR (hrl1.bill_to_site_flag = decode(:P_site,'BILL-TO','Y','FAIL'))
OR (hrl1.office_site_flag = decode(:P_site,'OFFICE','Y','FAIL'))
OR (hrl1.in_organization_flag = decode(:P_site,'EXTERNAL','N','FAIL'))
OR (hrl1.receiving_site_flag = decode(:P_site, 'RECEIVING','Y','FAIL'))
OR (nvl(:P_site,'ALL') = 'ALL'))
ORDER BY &orderby_clause |
