select
--papf.person_id,
papf.person_number,
ppnf.full_name,
--PAPD.payroll_id,
papf_pay.payroll_name,
prrd.payroll_relationship_number,
to_char(prrd.start_date,'YYYY-MM-DD') payroll_rel_start_date,
--to_char(prrd.end_date,'YYYY-MM-DD') payroll_rel_end_date,
to_char(PAPD.FSED,'YYYY-MM-DD') FSED,
to_char(PAPD.FINC,'YYYY-MM-DD') FINC,
to_char(PAPD.LSPD,'YYYY-MM-DD') LSPD,
to_char(PAPD.LSED,'YYYY-MM-DD') LSED,
paam.assignment_number,
prgf.time_card_req as time_card_PR,
paspf.time_card_req as time_card_AP,
ptd_pr.definition_name as Overriding_Period_PR,
ptd_ap.definition_name as Overriding_Period_AP
from
pay_assigned_payrolls_dn PAPD,
pay_payroll_terms ppt,
pay_pay_relationships_dn prrd,
pay_all_payrolls_f papf_pay,
pay_rel_groups_dn prgd,
pay_rel_groups_f prgf,
per_all_people_f papf,
per_person_names_f ppnf,
per_all_assignments_m paam,
pay_assigned_payrolls_f paspf,
pay_time_definitions ptd_pr,
pay_time_definitions ptd_ap
where paam.effective_latest_change = 'Y'
and paam.assignment_type ='E'
and prgd.group_type = 'A'
and ppnf.name_type = 'GLOBAL'
and PAPD.payroll_term_id = ppt.payroll_term_id
and ppt.payroll_relationship_id = prrd.payroll_relationship_id
and PAPD.payroll_id = papf_pay.payroll_id
and prrd.payroll_relationship_id = prgd.payroll_relationship_id
and prgd.relationship_group_id = prgf.relationship_group_id
and prrd.person_id = papf.person_id
and ppnf.person_id = papf.person_id
and prgd.assignment_id = paam.assignment_id
and ptd_pr.time_definition_id(+) = prgf.overriding_period_id
and ptd_ap.time_definition_id(+) = paspf.overriding_period_id
and paspf.assigned_payroll_id(+) = PAPD.assigned_payroll_id
and trunc(sysdate) between paspf.effective_start_date(+) and paspf.effective_end_date(+)
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and trunc(sysdate) between prgf.effective_start_date and prgf.effective_end_date
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
--and papf.person_number = '12345'
order by 2,3
