SQL query to pull Work Schedules for Assignments/Legal Employers

In this article we will look into queries to pull the Work schedule information at employee level and legal employer level.

The actual work schedule information will be stored in ZMM_SR_SCHEDULES_TL table. However the link to assignment/legal employer resides in PER_SCHEDULE_ASSIGNMENTS with different Resource Types.

SQL Query for Work Schedules at Person level:

SELECT DISTINCT
(SELECT DISTINCT K3.PERSON_NUMBER FROM PER_ALL_PEOPLE_F K3 WHERE K3.PERSON_ID = K2.PERSON_ID) "PERSON_NUMBER",
(SELECT DISTINCT K.SCHEDULE_NAME FROM ZMM_SR_SCHEDULES_TL K WHERE K1.SCHEDULE_ID = K.SCHEDULE_ID) "SCHEDULE_NAME",
K1.RESOURCE_TYPE,
TO_CHAR(K1.START_DATE,'MM/DD/YYYY') "START_DATE",
TO_CHAR(K1.END_DATE,'MM/DD/YYYY') "END_DATE",
K1.PRIMARY_FLAG,
hou.name Legal_Employer
FROM PER_SCHEDULE_ASSIGNMENTS K1, PER_ALL_ASSIGNMENTS_M K2,
hr_all_organization_units hou
,hr_org_unit_classifications_f hoc
WHERE K1.RESOURCE_ID = K2.ASSIGNMENT_ID
and k1.RESOURCE_TYPE = 'ASSIGN'
and hou.organization_id = hoc.organization_id
and sysdate BETWEEN HOU.effective_start_date(+) AND HOU.effective_end_date(+)
and hoc.organization_id(+)=K2.legal_entity_id

select haou.name,zsct.schedule_name
from per_schedule_assignments psa
,hr_all_organization_units haou
,zmm_sr_schedules_tl zsct
where psa.resource_type = 'LEGALEMP'
and psa.resource_id = haou.organization_id
and zsct.schedule_id = psa.schedule_id