• Post category:BI Publisher
  • Post comments:2 Comments
  • Post last modified:September 10, 2020
  • Reading time:2 mins read
SQL query to pull Work Schedules for Assignments/Legal Employers
SQL query to pull Work Schedules for Assignments/Legal Employers
- SQL query to pull Work Schedules for Assignments/Legal Employers
Total Visits: 31

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

  • 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