• Post category:BI Publisher
  • Post comments:2 Comments
  • Post last modified:September 10, 2020
  • Reading time:2 mins read
You are currently viewing SQL query to pull Work Schedules for Assignments/Legal Employers
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.

Table of Contents

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

  • 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)..

  • 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

  • In visible box by plugintheme