VIEW: APPS.MTH_EQUIP_SHIFT_REF_V

Source

View Text - Preformatted

SELECT txn.entity_fk_key entity_fk_key, txn.entity_type entity_type, MAX(wsd.shift_date) shift_date, GREATEST(MAX(wsd.shift_date),MAX(wsd.to_date)) to_date FROM mth_workday_shifts_d wsd, (SELECT entity_fk_key, entity_type, shift_workday_fk_key FROM MTH_ENTITY_SUSTAIN_ASPECT UNION ALL SELECT equipment_pk_key, 'EQUIPMENT', shift_workday_fk_key FROM MTH_EQUIPMENTS_EXT_B UNION ALL SELECT equipment_fk_key, 'EQUIPMENT', shift_workday_fk_key FROM MTH_EQUIP_OUTPUT UNION ALL SELECT equipment_fk_key, 'EQUIPMENT', shift_workday_fk_key FROM MTH_EQUIP_PROD_SCHEDULE_F UNION ALL SELECT equipment_fk_key, 'EQUIPMENT', shift_workday_fk_key FROM MTH_EQUIP_STATUSES UNION ALL SELECT plant_fk_key, 'SITE', shift_workday_fk_key FROM MTH_PROD_MTL_CONSUMED_TXN_F UNION ALL SELECT plant_fk_key, 'SITE', shift_workday_fk_key FROM MTH_PROD_MTL_PRODUCED_TXN_F UNION ALL SELECT entity_pk_key, entity_type, shift_workday_fk_key FROM MTH_USER_ENTITIES_EXT_B UNION ALL SELECT equipment_fk_key, 'EQUIPMENT', shift_workday_fk_key FROM MTH_TAG_READINGS UNION ALL SELECT CASE WHEN b.rowno = 1 THEN a. resource_fk_key WHEN b.rowno = 2 THEN a.plant_fk_key END ENTITY_KEY, CASE WHEN b.rowno = 1 THEN 'RESOURCE' WHEN b.rowno = 2 THEN 'SITE' END TYPE, a.shift_workday_fk_key FROM MTH_RESOURCE_TXN_F a, (SELECT ROWNUM rowno FROM dual CONNECT BY LEVEL <= 2)b UNION ALL SELECT CASE WHEN b.rowno = 1 THEN a. equipment_fk_key WHEN b.rowno = 2 THEN a.plant_fk_key END ENTITY_KEY, CASE WHEN b.rowno = 1 THEN 'EQUIPMENT' WHEN b.rowno = 2 THEN 'SITE' END TYPE, a.shift_workday_fk_key FROM mth_equip_standard_rates_f a, (SELECT ROWNUM rowno FROM dual CONNECT BY LEVEL <= 2)b) txn WHERE wsd.shift_workday_pk_key = txn.shift_workday_fk_key GROUP BY txn.entity_fk_key, txn.entity_type

View Text - HTML Formatted

SELECT TXN.ENTITY_FK_KEY ENTITY_FK_KEY
, TXN.ENTITY_TYPE ENTITY_TYPE
, MAX(WSD.SHIFT_DATE) SHIFT_DATE
, GREATEST(MAX(WSD.SHIFT_DATE)
, MAX(WSD.TO_DATE)) TO_DATE
FROM MTH_WORKDAY_SHIFTS_D WSD
, (SELECT ENTITY_FK_KEY
, ENTITY_TYPE
, SHIFT_WORKDAY_FK_KEY
FROM MTH_ENTITY_SUSTAIN_ASPECT UNION ALL SELECT EQUIPMENT_PK_KEY
, 'EQUIPMENT'
, SHIFT_WORKDAY_FK_KEY
FROM MTH_EQUIPMENTS_EXT_B UNION ALL SELECT EQUIPMENT_FK_KEY
, 'EQUIPMENT'
, SHIFT_WORKDAY_FK_KEY
FROM MTH_EQUIP_OUTPUT UNION ALL SELECT EQUIPMENT_FK_KEY
, 'EQUIPMENT'
, SHIFT_WORKDAY_FK_KEY
FROM MTH_EQUIP_PROD_SCHEDULE_F UNION ALL SELECT EQUIPMENT_FK_KEY
, 'EQUIPMENT'
, SHIFT_WORKDAY_FK_KEY
FROM MTH_EQUIP_STATUSES UNION ALL SELECT PLANT_FK_KEY
, 'SITE'
, SHIFT_WORKDAY_FK_KEY
FROM MTH_PROD_MTL_CONSUMED_TXN_F UNION ALL SELECT PLANT_FK_KEY
, 'SITE'
, SHIFT_WORKDAY_FK_KEY
FROM MTH_PROD_MTL_PRODUCED_TXN_F UNION ALL SELECT ENTITY_PK_KEY
, ENTITY_TYPE
, SHIFT_WORKDAY_FK_KEY
FROM MTH_USER_ENTITIES_EXT_B UNION ALL SELECT EQUIPMENT_FK_KEY
, 'EQUIPMENT'
, SHIFT_WORKDAY_FK_KEY
FROM MTH_TAG_READINGS UNION ALL SELECT CASE WHEN B.ROWNO = 1 THEN A. RESOURCE_FK_KEY WHEN B.ROWNO = 2 THEN A.PLANT_FK_KEY END ENTITY_KEY
, CASE WHEN B.ROWNO = 1 THEN 'RESOURCE' WHEN B.ROWNO = 2 THEN 'SITE' END TYPE
, A.SHIFT_WORKDAY_FK_KEY
FROM MTH_RESOURCE_TXN_F A
, (SELECT ROWNUM ROWNO
FROM DUAL CONNECT BY LEVEL <= 2)B UNION ALL SELECT CASE WHEN B.ROWNO = 1 THEN A. EQUIPMENT_FK_KEY WHEN B.ROWNO = 2 THEN A.PLANT_FK_KEY END ENTITY_KEY
, CASE WHEN B.ROWNO = 1 THEN 'EQUIPMENT' WHEN B.ROWNO = 2 THEN 'SITE' END TYPE
, A.SHIFT_WORKDAY_FK_KEY
FROM MTH_EQUIP_STANDARD_RATES_F A
, (SELECT ROWNUM ROWNO
FROM DUAL CONNECT BY LEVEL <= 2)B) TXN
WHERE WSD.SHIFT_WORKDAY_PK_KEY = TXN.SHIFT_WORKDAY_FK_KEY GROUP BY TXN.ENTITY_FK_KEY
, TXN.ENTITY_TYPE