VIEW: APPS.MTH_SHIFT_REFERENCE_MV#

Source

View Text - Preformatted

SELECT mws.plant_fk_key,
    MAX(mws.shift_date) shift_date,
    Greatest(MAX(mws.shift_date),MAX(mws.To_Date)) to_date
  FROM
    (SELECT DISTINCT shift_workday_fk_key
    FROM MTH_ENTITY_SUSTAIN_ASPECT
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_ENTITY_SUSTAIN_ASPECT_HOUR
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_EQUIPMENTS_EXT_B
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_EQUIP_OUTPUT
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_EQUIP_OUTPUT_SUMMARY
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_EQUIP_PROD_PERFORMANCE_F
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_EQUIP_PROD_PERF_DETAIL_F
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_EQUIP_PROD_SCHEDULE_F
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_EQUIP_STANDARD_RATES_F
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_EQUIP_STATUSES
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_EQUIP_STATUS_SUMMARY
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_EVENTS
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_PROD_MTL_CONSUMED_TXN_F
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_PROD_MTL_PRODUCED_TXN_F
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_PROD_SEGMENTS_TXN_F
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_RESOURCE_TXN_F
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_TAG_READINGS
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_TAG_READINGS_ERR
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_TAG_READINGS_T_STG
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    UNION ALL
    SELECT DISTINCT shift_workday_fk_key
    FROM MTH_USER_ENTITIES_EXT_B
    WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL
    ) shift_key,
    mth_workday_shifts_d mws
  WHERE shift_key.shift_workday_fk_key = mws.SHIFT_WORKDAY_PK_KEY
  GROUP BY mws.plant_fk_key

View Text - HTML Formatted

SELECT MWS.PLANT_FK_KEY
, MAX(MWS.SHIFT_DATE) SHIFT_DATE
, GREATEST(MAX(MWS.SHIFT_DATE)
, MAX(MWS.TO_DATE)) TO_DATE FROM (SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_ENTITY_SUSTAIN_ASPECT
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_ENTITY_SUSTAIN_ASPECT_HOUR
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_EQUIPMENTS_EXT_B
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_EQUIP_OUTPUT
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_EQUIP_OUTPUT_SUMMARY
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_EQUIP_PROD_PERFORMANCE_F
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_EQUIP_PROD_PERF_DETAIL_F
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_EQUIP_PROD_SCHEDULE_F
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_EQUIP_STANDARD_RATES_F
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_EQUIP_STATUSES
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_EQUIP_STATUS_SUMMARY
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_EVENTS
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_PROD_MTL_CONSUMED_TXN_F
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_PROD_MTL_PRODUCED_TXN_F
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_PROD_SEGMENTS_TXN_F
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_RESOURCE_TXN_F
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_TAG_READINGS
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_TAG_READINGS_ERR
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_TAG_READINGS_T_STG
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL UNION ALL SELECT DISTINCT SHIFT_WORKDAY_FK_KEY
FROM MTH_USER_ENTITIES_EXT_B
WHERE SHIFT_WORKDAY_FK_KEY IS NOT NULL ) SHIFT_KEY
, MTH_WORKDAY_SHIFTS_D MWS
WHERE SHIFT_KEY.SHIFT_WORKDAY_FK_KEY = MWS.SHIFT_WORKDAY_PK_KEY GROUP BY MWS.PLANT_FK_KEY