HR Workshift Schdule Script

SELECT distinct

      A.SCHEDULE_NAME,

      d.FULL_NAME,

      d.employee_number,

   B.START_DATE_ACTIVE,

      B.END_DATE_ACTIVE

      FROM CAC_SR_SCHEDULES_VL A, CAC_SR_SCHDL_OBJECTS B,

      PER_ALL_ASSIGNMENTS_F C, per_all_people_f d

WHERE A.SCHEDULE_ID = B.SCHEDULE_ID

AND B.OBJECT_ID = C.ASSIGNMENT_ID

AND B.OBJECT_TYPE = 'PERSON_ASSIGNMENT'

AND C.PERSON_ID = D.PERSON_ID

and sysdate between a.START_DATE_ACTIVE and a.END_DATE_ACTIVE

and sysdate between c.effective_start_date and c.effective_end_date

and sysdate between d.effective_start_date and d.effective_end_date