Search This Blog

Saturday, November 25, 2017

Work Schedules Query

SELECT to_char(psa.start_date,'YYYY/MM/DD') START_DATE,to_char(psa.end_date,'YYYY/MM/DD') END_DATE,papf.person_number,zsst.schedule_name,zsst.SCHEDULE_ID,psa.SCHEDULE_ASSIGNMENT_ID
FROM   zmm_sr_schedules_tl zsst
      ,per_schedule_assignments psa
      ,per_all_assignments_m paam1
      ,per_all_people_f papf
WHERE      1 = 1
       AND zsst.language = 'US'
       AND zsst.schedule_id = psa.schedule_id
       AND TRUNC (SYSDATE) BETWEEN psa.start_date AND psa.end_date
       AND psa.resource_id = paam1.assignment_id
       AND psa.resource_type = 'ASSIGN'
       AND TRUNC (SYSDATE) BETWEEN paam1.effective_start_date
                               AND paam1.effective_end_date
       AND paam1.primary_flag = 'Y'
         AND paam1.effective_latest_change = 'Y'
       AND paam1.assignment_type = 'E'
       AND papf.person_id = paam1.person_id
       AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                               AND papf.effective_end_date
--and person_number = '17'
ORDER BY LPAD(PAPF.PERSON_NUMBER,20,'0')

2 comments: