WIE_PLANT_SHIFTS_V
Details
-
Schema: FUSION
-
Object owner: WIE
-
Object type: VIEW
Columns
Name |
---|
ORGANIZATION_ID SCHEDULE_DETAIL_ID SHIFT_ID START_DATE_TIME END_DATE_TIME |
Query
SQL_Statement |
---|
SELECT shifts.organization_id, shifts.schedule_detail_id, shifts.SHIFT_ID, shifts.START_DATE_TIME, shifts.END_DATE_TIME FROM WIE_HALF_YEAR_CAL_SHIFTS_V SHIFTS WHERE NOT EXISTS (SELECT 1 FROM rcs_plant_shift_exc_vl EXCEPTIONS WHERE EXCEPTIONS.ORGANIZATION_ID = SHIFTS.ORGANIZATION_ID AND EXCEPTIONS.SCHEDULE_ID = SHIFTS.SCHEDULE_ID AND EXCEPTIONS.ASSOCIATED_SHIFT_ID = SHIFTS.SHIFT_ID AND EXCEPTIONS.EXCEPTION_TYPE IN ('RemoveShift','ChangeShift') AND TRUNC(START_DATE_TIME) BETWEEN start_date AND end_date ) UNION SELECT SHIFTS.ORGANIZATION_ID, shifts.schedule_detail_id, shifts.SHIFT_ID, CAST(TRUNC(START_DATE_TIME)+(1/24)*(exc_start_time/3600000) AS DATE), CAST(TRUNC(END_DATE_TIME) +(1/24)*(exc_end_time/3600000) AS DATE) FROM WIE_HALF_YEAR_CAL_SHIFTS_V SHIFTS, rcs_plant_shift_exc_vl EXCPTION WHERE EXCPTION.ORGANIZATION_ID = SHIFTS.ORGANIZATION_ID AND EXCPTION.SCHEDULE_ID = SHIFTS.SCHEDULE_ID AND EXCPTION.ASSOCIATED_SHIFT_ID = SHIFTS.SHIFT_ID AND EXCPTION.EXCEPTION_TYPE = 'ChangeShift' AND TRUNC(START_DATE_TIME) BETWEEN start_date AND end_date UNION SELECT ORGANIZATION_ID, PLANT_SHIFT_EXC_ID, ASSOCIATED_SHIFT_ID AS SHIFT_ID, CAST(TRUNC(START_DATE+LEVEL-1)+(1/24)*(EXC_START_TIME/3600000) AS DATE)START_DATE_TIME, CAST(TRUNC(START_DATE+LEVEL-1)+(1/24)*(EXC_END_TIME/3600000) AS DATE)END_DATE_TIME FROM (SELECT RECUR_ADD_SHIFT_EXCEPS.PLANT_SHIFT_EXC_ID, RECUR_ADD_SHIFT_EXCEPS.ORGANIZATION_ID, RECUR_ADD_SHIFT_EXCEPS.ASSOCIATED_SHIFT_ID, RECUR_ADD_SHIFT_EXCEPS.START_DATE, RECUR_ADD_SHIFT_EXCEPS.EXC_START_TIME, RECUR_ADD_SHIFT_EXCEPS.END_DATE, RECUR_ADD_SHIFT_EXCEPS.EXC_END_TIME FROM RCS_PLANT_SHIFT_EXC_VL RECUR_ADD_SHIFT_EXCEPS WHERE RECUR_ADD_SHIFT_EXCEPS.EXCEPTION_TYPE = 'AddShift' ) ADDED_SHIFTS CONNECT BY LEVEL <= (ADDED_SHIFTS.END_DATE - ADDED_SHIFTS.START_DATE+1) AND PRIOR ADDED_SHIFTS.PLANT_SHIFT_EXC_ID = ADDED_SHIFTS.PLANT_SHIFT_EXC_ID AND prior SYS_GUID() IS NOT NULL |