Query: Job Work Preferences

select
    pjt.name                         "Job",
    --
    pdf.work_any_location            "All Locations",
    pdf.passport_required            "Passport Required",
    pdf.relocation_required          "Relocation Required",
    pdf.relocate_domestically        "Relocation for Position",
    pdf.travel_required              "Travel Required",
    pdf.work_duration                "Work Duration",
    pdf.work_hours                   "Work Hours",
    pdf.service_minimum              "Minimum Service",     -- lookup :per_lengths_of_service
    pdf.work_schedule                "Work Schedule",       -- lookup :per_work_schedule
    pdf.fte_capacity                 "FTE Capacity",        -- lookup :per_work_hours
    pdf.work_any_country             "All Countries",
    pdf.relocate_internationally     "Willing to Relocate ",
    pdf.other_requirements           "Required Locations",
    ptv1.territory_short_name        "Rquired Countries1",
    hrl1tl.location_code             "Location1",
    ptv2.territory_short_name        "Rquired Countries2",
    hrl2tl.location_code             "Location2",
    ptv3.territory_short_name        "Rquired Countries3",
    hrl3tl.location_code             "Location3",
    --
    pj.job_id
from
    per_deployment_factors pdf,
    per_jobs pj,
    per_jobs_tl pjt,
    fnd_territories_vl ptv1,
    fnd_territories_vl ptv2,
    fnd_territories_vl ptv3,
    hr_locations_no_join hrl1,
    hr_locations_no_join hrl2,
    hr_locations_no_join hrl3,
    hr_locations_all_tl hrl1tl,
    hr_locations_all_tl hrl2tl,
    hr_locations_all_tl hrl3tl
where
    1=1
and pdf.job_id = pj.job_id
and pj.job_id = pjt.job_id
and pjt.language = userenv('LANG')
and ptv1.territory_code(+) = pdf.country1
and ptv2.territory_code(+) = pdf.country2
and ptv3.territory_code(+) = pdf.country3
and hrl1.location_id(+) = pdf.location1
and hrl2.location_id(+) = pdf.location2
and hrl3.location_id(+) = pdf.location3
and hrl1.location_id = hrl1tl.location_id(+)
and hrl2.location_id = hrl2tl.location_id(+)
and hrl3.location_id = hrl3tl.location_id(+)
and decode (hrl1tl.location_id, null, '1', hrl1tl.language) = decode (hrl1tl.location_id, null, '1', userenv ('LANG'))
and decode (hrl2tl.location_id, null, '1', hrl2tl.language) = decode (hrl2tl.location_id, null, '1', userenv ('LANG'))
and decode (hrl3tl.location_id, null, '1', hrl3tl.language) = decode (hrl3tl.location_id, null, '1', userenv ('LANG'))
--
and pjt.name = 'AA900.Administrative Assistant'
order by
    1,2,3;