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

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