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 |