WIE_CURRENT_SHIFTS

Details

Schema: FUSION
Object owner: WIE
Object type: VIEW

Columns

Name
ORGANIZATION_ID SHIFT_AFFINITY SHIFT_ID START_DATE_TIME END_DATE_TIME

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

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