Search This Blog

Wednesday, May 24, 2017

Fusion Payroll Query to get Payroll Relationship and BU Name

select p.person_number,
py.payroll_name,haou.name BUName
from PAY_ASSIGNED_PAYROLLS_DN ap
,    pay_PAYROLL_TERMS pt
,    PAY_PAY_RELATIONSHIPS_DN pr
,    PAY_ALL_PAYROLLS_F py
,    PER_ALL_PEOPLE_F p
,    per_all_assignments_m paam
,    hr_all_organization_units haou
,    (select distinct person_id from per_person_secured_list_v) ppsl
where ap.payroll_term_id = pt.payroll_term_id
and   pt.payroll_relationship_id = pr.payroll_relationship_id
and   ap.payroll_id = py.payroll_id
and   pr.person_id = p.person_id
--and p.person_number = '2824'
and ap.lsed is null
and paam.person_id=p.person_id
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
and trunc(sysdate) between p.effective_start_date and p.effective_end_date
and paam.assignment_type = 'E'
and paam.primary_flag = 'Y'
and haou.organization_id = paam.business_unit_id
and ppsl.person_id = p.person_id