SQL query to pull Work Schedules for Assignments/Legal Employers
SQL query to pull Schedule Assignments

 360 total views

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

SQL Query for Work schedules at Legal Employer level:

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

Sricharan

Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 2 Comments

  1. Naveen

    Is there a SQL that pulls primary assignment of an employee for a given date? Ideally it should look at the work schedule based on priority ( assignment,location,department ….so on legal entity)..

  2. Sricharan

    Hi Naveen,
    I haven’t developed one based on priority. However the query in the article should be a good starting point for you in developing the one as per your requirement.
    Thanks,
    Sricharan

Leave a Reply