In this article, we will try to retrieve the Time Card Required and Overriding Period details from the Payroll Relationship Screen.
If we navigate to an employee-related payroll relationship screen, there will be two sets of fields for Time Card Required and Overriding Period and it is confusing for the users to differentiate and query these values. One of the values is at the Assigned Payroll level and the other one at the Payroll Relationship level.
These flags are mainly used by Payroll to calculate the earnings for hourly employees.
SQL Query to fetch this information:
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 -- Payroll Relationship level
,paspf.time_card_req as time_card_AP -- Assigned Payroll level
,ptd_pr.definition_name as Overriding_Period_PR -- Payroll Relationship level
,ptd_ap.definition_name as Overriding_Period_AP -- Assigned Payroll level
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,3Hope this is useful.
