• Post category:Payroll
  • Post comments:0 Comments
  • Post last modified:June 9, 2022
  • Reading time:3 mins read
You are currently viewing How to pull Time Card Required and Overriding Period Details?
How to pull Time Card Required and Overriding Period Details?
- How to pull Time Card Required and Overriding Period Details?
Total Visits: 6

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.

image 1024x466 - How to pull Time Card Required and Overriding Period Details?

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,3

Hope this is useful.