SELECT
papf.person_number,
ppnf.full_name employee_full_name,
ppnf.first_name,
ppnf.last_name,
to_char(ppos.date_start, ‘MM/DD/YYYY’) date_start,
paam.assignment_number,
paam.primary_flag,
paam.assignment_status_type active_status,
paam.bargaining_unit_code,
aapf.payroll_name,
PAAM.ASS_ATTRIBUTE6 attribute_value
FROM
per_all_people_f papf,
per_all_assignments_m paam,
per_person_names_f ppnf,
per_periods_of_service ppos,
pay_payroll_assignments ppa,
pay_pay_relationships_dn pprd,
pay_payroll_terms ppt,
pay_assigned_payrolls_dn aapd,
pay_all_payrolls_f aapf
WHERE
papf.person_id = paam.person_id
AND trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND trunc(sysdate) BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.assignment_status_type IN ( ‘ACTIVE’, ‘SUSPENDED’ )
AND paam.effective_latest_change = ‘Y’
AND paam.assignment_type IN ( ‘E’, ‘C’, ‘N’, ‘O’, ‘P’ )
AND papf.person_id = ppnf.person_id
AND trunc(sysdate) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND ppnf.name_type = ‘GLOBAL’
AND paam.period_of_service_id = ppos.period_of_service_id
AND paam.assignment_id = ppa.hr_assignment_id (+)
AND trunc(sysdate) BETWEEN ppa.start_date (+) AND ppa.end_date (+)
AND paam.work_terms_assignment_id = ppa.hr_term_id (+)
AND ppa.payroll_relationship_id = pprd.payroll_relationship_id (+)
AND trunc(sysdate) BETWEEN pprd.start_date (+) AND pprd.end_date (+)
AND pprd.payroll_relationship_id = ppt.payroll_relationship_id (+)
AND trunc(sysdate) BETWEEN ppt.start_date (+) AND ppt.end_date (+)
AND ppt.payroll_term_id = aapd.payroll_term_id (+)
AND trunc(sysdate) BETWEEN aapd.start_date (+) AND aapd.end_date (+)
AND aapd.payroll_id = aapf.payroll_id (+)
AND trunc(sysdate) BETWEEN aapf.effective_start_date (+) AND aapf.effective_end_date (+)
AND ppa.payroll_term_id = ppt.payroll_term_id (+)
ORDER BY
papf.person_number,
paam.assignment_number

Share this post
Recent Posts

Leave a Comment

Start typing and press Enter to search