SELECT
papf.attribute2 legacy_employee_number,
papf.last_name,
papf.first_name,
papf.title,
papf.pre_name_adjunct,
papf.suffix,
papf.middle_names,
hl_sex.meaning gender,
pptt.user_person_type,
papf.employee_number,
papf.national_identifier,
papf.effective_start_date,
papf.effective_end_date,
papf.start_date original_hire_date,
papf.date_of_birth,
papf.town_of_birth,
papf.region_of_birth,
papf.country_of_birth,
TRUNC (MONTHS_BETWEEN (SYSDATE,
papf.date_of_birth) / 12) age,
papf.nationality,
hl_dis.meaning registered_disabled,
hl_vet.meaning veteran_menaing,
papf.office_number,
papf.internal_location,
papf.mailstop,
papf.email_address,
papf.known_as,
papf.previous_last_name,
papf.correspondence_language,
ppos.adjusted_svc_date,
hl_mlt.meaning mail_to,
hl_eth.meaning ethnic_origin,
papf.per_information2 i9_status,
papf.per_information3 i9_expiration_date,
hl_mar.meaning marital_status,
papf.attribute1 nt_login,
pa.address_line1,
pa.address_line2,
pa.address_line3,
pa.town_or_city,
pa.region_2 STATE,
pa.region_1 county,
pa.postal_code,
pa.country,
pa.telephone_number_1,
pa.telephone_number_2,
pa.primary_flag,
pa.date_from,
pa.date_to,
pa.style address_style,
hou.NAME organization_name,
pjt.NAME job_name,
pgt.NAME grade_name,
hla.location_code,
sup_papf.full_name supervisor_full_name,
sup_papf.employee_number supervisor_employee_number,
paaf.normal_hours,
paaf.frequency,
hl_cr.meaning change_reason,
paaf.manager_flag,
pap.payroll_name,
past.user_status assignment_status,
hl_ac.meaning assignment_category,
ppg.group_name,
gre.NAME gre,
paaf.ass_attribute4 ttp_yr,
ppp.change_date,
ppp.proposed_salary_n,
ppp.proposal_reason,
ppp.approved,
ppp.next_sal_review_date,
hl_pay.meaning salary_basis_meaning,
hl_pb.meaning pay_basis_meaning,
ppb.pay_annualization_factor,
paaf.ass_attribute1 shift,
paaf.ass_attribute2 hrs_per_day,
pp.phone_number home_phone_number,
pp1.phone_number pager_number
FROM
per_all_people_f papf,
per_all_assignments_f paaf,
per_addresses pa,
per_pay_proposals ppp,
per_person_types_tl pptt,
pay_people_groups ppg,
hr_lookups hl_sex,
apps.hr_lookups hl_dis,
apps.hr_lookups hl_vet,
apps.hr_lookups hl_eth,
apps.hr_lookups hl_mar,
apps.hr_lookups hl_mlt,
apps.hr_lookups hl_cr,
apps.hr_lookups hl_ac,
apps.hr_lookups hl_pay,
apps.hr_lookups hl_pb,
hr.per_periods_of_service ppos,
apps.hr_organization_units hou,
hr.per_jobs_tl pjt,
hr.per_grades_tl pgt,
hr.hr_locations_all hla,
pay_all_payrolls_f pap,
hr.per_pay_bases ppb,
per_assignment_status_types_tl past,
hr_soft_coding_keyflex hsc,
holx_emp_conv_stg stg,
hr.per_all_people_f sup_papf,
per_phones pp,
per_phones pp1,
hr_organization_units gre
WHERE
papf.person_id = stg.new_person_id
AND pptt.person_type_id(+) = papf.person_type_id
AND hsc.segment1 = gre.organization_id
AND paaf.assignment_status_type_id = past.assignment_status_type_id(+)
AND past.LANGUAGE = 'US'
AND ppg.people_group_id(+) = paaf.people_group_id
AND hou.organization_id(+) = paaf.organization_id
AND pjt.job_id(+) = paaf.job_id
AND pjt.LANGUAGE(+) = USERENV ('LANG')
AND pgt.grade_id(+) = paaf.grade_id
AND pgt.LANGUAGE(+) = USERENV ('LANG')
AND hla.location_id(+) = paaf.location_id --
AND hla.business_group_id(+) = paaf.business_group_id
AND pap.payroll_id(+) = paaf.payroll_id
AND pptt.LANGUAGE(+) = USERENV ('LANG')
AND hl_sex.lookup_code(+) = papf.sex
AND hl_sex.lookup_type(+) = 'SEX'
AND hl_pay.lookup_code(+) = ppp.proposal_reason
AND hl_pay.lookup_type(+) = 'PROPOSAL_REASON'
AND ppb.pay_basis_id(+) = paaf.pay_basis_id
AND hl_pb.lookup_code(+) = ppb.pay_basis
AND hl_pb.lookup_type(+) = 'PAY_BASIS'
AND hl_ac.lookup_code(+) = paaf.employment_category
AND hl_ac.lookup_type(+) = 'EMP_CAT'
AND hl_dis.lookup_code(+) = papf.registered_disabled_flag
AND hl_dis.lookup_type(+) = 'REGISTERED_DISABLED'
AND hl_vet.lookup_code(+) = papf.per_information5
AND hl_vet.lookup_type(+) = 'US_VETERAN_STATUS'
AND hl_eth.lookup_code(+) = papf.per_information1
AND hl_eth.lookup_type(+) = 'US_ETHNIC_GROUP'
AND hl_mar.lookup_code(+) = papf.marital_status
AND hl_mar.lookup_type(+) = 'MAR_STATUS'
AND hl_mlt.lookup_code(+) = papf.expense_check_send_to_address
AND hl_mlt.lookup_type(+) = 'HOME_OFFICE'
AND hl_cr.lookup_code(+) = paaf.change_reason
AND hl_cr.lookup_type(+) = 'EMP_ASSIGN_REASON'
AND ppos.person_id(+) = papf.person_id
AND stg.status_stg LIKE 'S%'
AND paaf.person_id = papf.person_id
AND paaf.assignment_id = stg.assignment_id
AND paaf.person_id = pa.person_id
AND papf.person_id = pa.person_id
AND pa.address_id = stg.address_id
AND ppp.assignment_id = paaf.assignment_id
AND hsc.soft_coding_keyflex_id(+) = paaf.soft_coding_keyflex_id
AND papf.effective_end_date > SYSDATE
AND paaf.effective_end_date > SYSDATE
AND sup_papf.person_id(+) = paaf.supervisor_id
AND sup_papf.effective_end_date(+) > TRUNC (SYSDATE)
AND pp.parent_id(+) = papf.person_id
AND pp.phone_type(+) = 'H1'
AND pp1.parent_id(+) = papf.person_id
AND pp1.phone_type(+) = 'P'
