WIE_OVR_CAL_EXTEND_SHIFTS

Details

Schema: FUSION
Object owner: WIE
Object type: VIEW

Columns

Name
ORGANIZATION_ID SCHEDULE_ID PLANT_SHIFT_EXC_ID SHIFT_ID START_DATE_TIME END_DATE_TIME

Name

ORGANIZATION_ID

SCHEDULE_ID

PLANT_SHIFT_EXC_ID

SHIFT_ID

START_DATE_TIME

END_DATE_TIME

Query

SQL_Statement
SELECT shifts.organization_id, shifts.schedule_id, shifts.schedule_detail_id, shifts.shift_id, shifts.start_date_time, shifts.end_date_time FROM wie_half_year_ovr_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_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_ovr_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, schedule_id, plant_shift_exc_id, associated_shift_id 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.schedule_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_id,

shifts.schedule_detail_id,

shifts.shift_id,

shifts.start_date_time,

shifts.end_date_time

FROM

wie_half_year_ovr_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_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_ovr_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,

schedule_id,

plant_shift_exc_id,

associated_shift_id 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.schedule_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