SQL Query to get the list of work schedule in the system

select zmm.*

from PER_ALL_ASSIGNMENTS_F PAAF, PER_SCHEDULE_ASSIGNMENTS PSA, zmm_sr_schedules_vl zmm, ZMM_SR_SCHEDULE_PATTERNS ZSP, ZMM_SR_PATTERN_DTLS ZSPD, ZMM_SR_SHIFT_DTLS ZSSD

WHERE 1=1

AND paaf.assignment_type='E'

and ZSPD.PATTERN_ID=ZSP.PATTERN_ID

and ZSPD.CHILD_SHIFT_ID=ZSSD.SHIFT_ID

and ZSP.SCHEDULE_ID=PSA.SCHEDULE_ID

and paaf.assignment_status_type='ACTIVE'

AND PSA.RESOURCE_ID=PAAF.ASSIGNMENT_ID

AND PSA.RESOURCE_TYPE='ASSIGN'

and psa.schedule_id=zmm.schedule_id

and TRUNC(SYSDATE) between TRUNC(PSA.START_DATE) and TRUNC(PSA.END_DATE)

And TRUNC(SYSDATE) between TRUNC(zmm.effective_from_date) and TRUNC(zmm.effective_to_date)

AND PSA.PRIMARY_FLAG='Y'

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.

Technical Contents - SQL Query

Posted by : Mohammad

Follow on Linkedin