VIEW: APPS.MTH_EQUIP_SHIFT_HR_V

Source

View Text - Preformatted

SELECT s.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY, s.AVAILABILITY_DATE AVAILABILITY_DATE, s.SHIFT_WORKDAY_FK_KEY SHIFT_WORKDAY_FK_KEY, h.HOUR_PK_KEY HOUR_PK_KEY, Greatest(h.FROM_TIME, s.FROM_DATE) FROM_DATE, Least(h.TO_TIME, s.To_Date) To_Date, s.LINE_NUM LINE_NUM, s.SHIFT_TYPE SHIFT_TYPE FROM (SELECT s.EQUIPMENT_FK_KEY, Decode(m.SHIFT_TYPE, 1, s.SHIFT_WORKDAY_FK_KEY, First_Value(s.SHIFT_WORKDAY_FK_KEY) OVER (PARTITION BY s.EQUIPMENT_FK_KEY, s.AVAILABILITY_DATE ORDER BY Decode(m.SHIFT_TYPE, 1, s.SHIFT_TYPE, 2+s.SHIFT_TYPE))) SHIFT_WORKDAY_FK_KEY, s.LINE_NUM, s.AVAILABILITY_DATE, Decode(m.SHIFT_TYPE, 1, s.FROM_DATE, s.To_Date + 1/(24*60*60)) FROM_DATE, Decode(m.SHIFT_TYPE, 1, s.TO_DATE, Lead(s.FROM_DATE, 1, Decode(s.SHIFT_TYPE,0,Trunc(s.From_Date),Trunc(s.To_Date))+1) OVER (PARTITION BY s.EQUIPMENT_FK_KEY ORDER BY s.FROM_DATE, Decode(m.SHIFT_TYPE, 1, s.SHIFT_TYPE, 2+s.SHIFT_TYPE)) - 1/(24*60*60)) TO_DATE, Decode(m.SHIFT_TYPE, 1, s.SHIFT_TYPE, 2) SHIFT_TYPE FROM (SELECT s.EQUIPMENT_FK_KEY, s.SHIFT_WORKDAY_FK_KEY, s.LINE_NUM, Nvl(s.FROM_DATE, Greatest(Nvl(Lag(s.TO_DATE) over (PARTITION BY s.EQUIPMENT_FK_KEY ORDER BY Nvl(s.FROM_DATE, s.AVAILABILITY_DATE)) + 1/(24*60*60), s.AVAILABILITY_DATE), s.AVAILABILITY_DATE)) FROM_DATE, Nvl(s.TO_DATE, Greatest(Nvl(Lag(s.TO_DATE) over (PARTITION BY s.EQUIPMENT_FK_KEY ORDER BY Nvl(s.FROM_DATE, s.AVAILABILITY_DATE)) + 1/(24*60*60), s.AVAILABILITY_DATE), s.AVAILABILITY_DATE) - 1/(24*60*60)) TO_DATE, Decode(s.FROM_DATE,NULL,0,1) SHIFT_TYPE, s.AVAILABILITY_DATE FROM mth_equipment_shifts_d s, (SELECT r.EQUIPMENT_FK_KEY, Trunc(Min(r.READING_TIME)) MIN_READING_DATE, Trunc(Max(r.READING_TIME))+1 MAX_READING_DATE FROM (SELECT readings.EQUIPMENT_FK_KEY, readings.READING_TIME, readings.mth_entity FROM mth_tag_readings readings where readings.EQUIPMENT_FK_KEY IS NOT NULL AND readings.HOUR_FK_KEY IS NOT NULL AND readings.SHIFT_WORKDAY_FK_KEY IS NOT NULL AND readings.TAG_DATA IS NOT NULL UNION all SELECT err.EQUIPMENT_FK_KEY, err.READING_TIME, err.mth_entity FROM mth_tag_readings_err err where err.EQUIPMENT_FK_KEY IS NOT NULL AND err.HOUR_FK_KEY IS NOT NULL AND err.TAG_DATA IS NOT NULL)r, MTH_ENTITIES e WHERE e.mth_alias = 'Status' AND e.id = r.mth_entity GROUP BY r.EQUIPMENT_FK_KEY) r WHERE s.EQUIPMENT_FK_KEY = r.EQUIPMENT_FK_KEY AND s.ENTITY_TYPE = 'EQUIPMENT' AND s.AVAILABILITY_DATE BETWEEN r.MIN_READING_DATE AND r.MAX_READING_DATE) s, (SELECT ROWNUM SHIFT_TYPE FROM dual CONNECT BY LEVEL <= 2) m) s, mth_hour_d h WHERE s.FROM_DATE <= To_Date AND (h.from_time BETWEEN s.from_date AND s.to_date OR s.from_date BETWEEN h.from_time AND h.to_time)

View Text - HTML Formatted

SELECT S.EQUIPMENT_FK_KEY EQUIPMENT_FK_KEY
, S.AVAILABILITY_DATE AVAILABILITY_DATE
, S.SHIFT_WORKDAY_FK_KEY SHIFT_WORKDAY_FK_KEY
, H.HOUR_PK_KEY HOUR_PK_KEY
, GREATEST(H.FROM_TIME
, S.FROM_DATE) FROM_DATE
, LEAST(H.TO_TIME
, S.TO_DATE) TO_DATE
, S.LINE_NUM LINE_NUM
, S.SHIFT_TYPE SHIFT_TYPE
FROM (SELECT S.EQUIPMENT_FK_KEY
, DECODE(M.SHIFT_TYPE
, 1
, S.SHIFT_WORKDAY_FK_KEY
, FIRST_VALUE(S.SHIFT_WORKDAY_FK_KEY) OVER (PARTITION BY S.EQUIPMENT_FK_KEY
, S.AVAILABILITY_DATE ORDER BY DECODE(M.SHIFT_TYPE
, 1
, S.SHIFT_TYPE
, 2+S.SHIFT_TYPE))) SHIFT_WORKDAY_FK_KEY
, S.LINE_NUM
, S.AVAILABILITY_DATE
, DECODE(M.SHIFT_TYPE
, 1
, S.FROM_DATE
, S.TO_DATE + 1/(24*60*60)) FROM_DATE
, DECODE(M.SHIFT_TYPE
, 1
, S.TO_DATE
, LEAD(S.FROM_DATE
, 1
, DECODE(S.SHIFT_TYPE
, 0
, TRUNC(S.FROM_DATE)
, TRUNC(S.TO_DATE))+1) OVER (PARTITION BY S.EQUIPMENT_FK_KEY ORDER BY S.FROM_DATE
, DECODE(M.SHIFT_TYPE
, 1
, S.SHIFT_TYPE
, 2+S.SHIFT_TYPE)) - 1/(24*60*60)) TO_DATE
, DECODE(M.SHIFT_TYPE
, 1
, S.SHIFT_TYPE
, 2) SHIFT_TYPE
FROM (SELECT S.EQUIPMENT_FK_KEY
, S.SHIFT_WORKDAY_FK_KEY
, S.LINE_NUM
, NVL(S.FROM_DATE
, GREATEST(NVL(LAG(S.TO_DATE) OVER (PARTITION BY S.EQUIPMENT_FK_KEY ORDER BY NVL(S.FROM_DATE
, S.AVAILABILITY_DATE)) + 1/(24*60*60)
, S.AVAILABILITY_DATE)
, S.AVAILABILITY_DATE)) FROM_DATE
, NVL(S.TO_DATE
, GREATEST(NVL(LAG(S.TO_DATE) OVER (PARTITION BY S.EQUIPMENT_FK_KEY ORDER BY NVL(S.FROM_DATE
, S.AVAILABILITY_DATE)) + 1/(24*60*60)
, S.AVAILABILITY_DATE)
, S.AVAILABILITY_DATE) - 1/(24*60*60)) TO_DATE
, DECODE(S.FROM_DATE
, NULL
, 0
, 1) SHIFT_TYPE
, S.AVAILABILITY_DATE
FROM MTH_EQUIPMENT_SHIFTS_D S
, (SELECT R.EQUIPMENT_FK_KEY
, TRUNC(MIN(R.READING_TIME)) MIN_READING_DATE
, TRUNC(MAX(R.READING_TIME))+1 MAX_READING_DATE
FROM (SELECT READINGS.EQUIPMENT_FK_KEY
, READINGS.READING_TIME
, READINGS.MTH_ENTITY
FROM MTH_TAG_READINGS READINGS
WHERE READINGS.EQUIPMENT_FK_KEY IS NOT NULL
AND READINGS.HOUR_FK_KEY IS NOT NULL
AND READINGS.SHIFT_WORKDAY_FK_KEY IS NOT NULL
AND READINGS.TAG_DATA IS NOT NULL UNION ALL SELECT ERR.EQUIPMENT_FK_KEY
, ERR.READING_TIME
, ERR.MTH_ENTITY
FROM MTH_TAG_READINGS_ERR ERR
WHERE ERR.EQUIPMENT_FK_KEY IS NOT NULL
AND ERR.HOUR_FK_KEY IS NOT NULL
AND ERR.TAG_DATA IS NOT NULL)R
, MTH_ENTITIES E
WHERE E.MTH_ALIAS = 'STATUS'
AND E.ID = R.MTH_ENTITY GROUP BY R.EQUIPMENT_FK_KEY) R
WHERE S.EQUIPMENT_FK_KEY = R.EQUIPMENT_FK_KEY
AND S.ENTITY_TYPE = 'EQUIPMENT'
AND S.AVAILABILITY_DATE BETWEEN R.MIN_READING_DATE
AND R.MAX_READING_DATE) S
, (SELECT ROWNUM SHIFT_TYPE
FROM DUAL CONNECT BY LEVEL <= 2) M) S
, MTH_HOUR_D H
WHERE S.FROM_DATE <= TO_DATE
AND (H.FROM_TIME BETWEEN S.FROM_DATE
AND S.TO_DATE OR S.FROM_DATE BETWEEN H.FROM_TIME
AND H.TO_TIME)