WIE_PLANT_SHIFTS

Details

Schema: FUSION
Object owner: WIE
Object type: VIEW

Columns

Name
ORGANIZATION_ID SCHEDULE_DETAIL_ID SHIFT_ID START_DATE_TIME END_DATE_TIME

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

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