WIE_CURRENT_SHIFTS_V
Details
-
Schema: FUSION
-
Object owner: WIE
-
Object type: VIEW
Columns
Name |
---|
ORGANIZATION_ID SHIFT_AFFINITY SHIFT_ID START_DATE_TIME END_DATE_TIME |
Query
SQL_Statement |
---|
SELECT WPS.organization_id ORGANIZATION_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_PLANT_SHIFTS_V WPS, ( SELECT WPS_B.organization_id,MIN(start_date_time) start_date_time FROM WIE_PLANT_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 )NEXT_SHIFT_PER_ORG where NEXT_SHIFT_PER_ORG.ORGANIZATION_ID = WPS.ORGANIZATION_ID and NEXT_SHIFT_PER_ORG.START_DATE_TIME = WPS.START_DATE_TIME union select WPS.ORGANIZATION_ID ORGANIZATION_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_PLANT_SHIFTS_V WPS, ( SELECT WPS_B.organization_id,MAX(start_date_time) start_date_time from WIE_PLANT_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 )PREVIOUS_SHIFT_PER_ORG where PREVIOUS_SHIFT_PER_ORG.ORGANIZATION_ID = WPS.ORGANIZATION_ID and PREVIOUS_SHIFT_PER_ORG.START_DATE_TIME = WPS.START_DATE_TIME union SELECT WPS.organization_id ORGANIZATION_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_PLANT_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, '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_PLANT_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 |