WIE_OVR_CAL_CURRENT_SHIFTS_V

Details

• Schema: FUSION

• Object owner: WIE

• Object type: VIEW

Columns

Name
ORGANIZATION_ID SCHEDULE_ID SHIFT_AFFINITY SHIFT_ID START_DATE_TIME END_DATE_TIME

Query

SQL_Statement

SELECT wps.organization_id organization_id, wps.schedule_id schedule_id, 'NEXT_SHIFT' shift_affinity, wps.shift_id shift_id, wps.start_date_time start_date_time, wps.end_date_time end_date_time FROM wie_ovr_cal_extend_shifts_v wps, ( SELECT wps_b.organization_id, wps_b.schedule_id, MIN(start_date_time) start_date_time FROM wie_ovr_cal_extend_shifts_v wps_b, wie_plant_timezone_v params WHERE wps_b.organization_id = params.organization_id AND params.plant_time < wps_b.start_date_time GROUP BY wps_b.organization_id, wps_b.schedule_id ) next_shift_per_org WHERE next_shift_per_org.organization_id = wps.organization_id AND next_shift_per_org.schedule_id = wps.schedule_id AND next_shift_per_org.start_date_time = wps.start_date_time UNION SELECT wps.organization_id organization_id, wps.schedule_id schedule_id, 'PREVIOUS_SHIFT' shift_affinity, wps.shift_id shift_id, wps.start_date_time start_date_time, wps.end_date_time end_date_time FROM wie_ovr_cal_extend_shifts_v wps, ( SELECT wps_b.organization_id, wps_b.schedule_id, MAX(start_date_time) start_date_time FROM wie_ovr_cal_extend_shifts_v wps_b, wie_plant_timezone_v params WHERE wps_b.organization_id = params.organization_id AND params.plant_time > wps_b.end_date_time GROUP BY wps_b.organization_id, wps_b.schedule_id ) previous_shift_per_org WHERE previous_shift_per_org.organization_id = wps.organization_id AND previous_shift_per_org.schedule_id = wps.schedule_id AND previous_shift_per_org.start_date_time = wps.start_date_time UNION SELECT wps.organization_id organization_id, wps.schedule_id schedule_id, 'CURRENT_SHIFT' shift_affinity, wps.shift_id shift_id, wps.start_date_time start_date_time, wps.end_date_time end_date_time FROM wie_ovr_cal_extend_shifts_v wps, wie_plant_timezone_v params WHERE wps.organization_id = params.organization_id AND params.plant_time BETWEEN wps.start_date_time AND wps.end_date_time UNION SELECT wps.organization_id organization_id, wps.schedule_id schedule_id, 'PAST_DUE_AND_CURRENT_SHIFT' shift_affinity, to_number(0) shift_id, to_date(NULL) start_date_time, wps.end_date_time end_date_time FROM wie_ovr_cal_extend_shifts_v wps, wie_plant_timezone_v params WHERE wps.organization_id = params.organization_id AND params.plant_time BETWEEN wps.start_date_time AND wps.end_date_time