PO Location Listing

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