VIEW: APPS.MTH_EQUIP_ST_SUM_DD_MV#

Source

View Text - Preformatted

SELECT
TIME_D.DAY_ID,
TIME_D.CALENDAR_MONTH_ID,
DEPT_HIER.LEVEL8_LEVEL_KEY DEPT_KEY,
DEPT_HIER.LEVEL8_EFFECTIVE_DATE DEPT_EFF_DATE,
CUST_HIER.LEVEL9_LEVEL_KEY CUST_LEVEL9_KEY,
CUST_HIER.LEVEL9_EFFECTIVE_DATE CUST_LEVEL9_EFF_DATE,
SUM(DOWN_HOURS)DOWN_HOURS,
COUNT(DOWN_HOURS)DOWN_HOURS_COUNT,
SUM(RESOURCE_COST*DOWN_HOURS)DOWN_HOURS_VAL,
COUNT(RESOURCE_COST*DOWN_HOURS)DOWN_HOURS_VAL_COUNT,
SUM(RUN_HOURS)RUN_HOURS,
COUNT(RUN_HOURS)RUN_HOURS_COUNT,
SUM(RESOURCE_COST*RUN_HOURS)RUN_HOURS_VAL,
COUNT(RESOURCE_COST*RUN_HOURS)RUN_HOURS_VAL_COUNT,
SUM(IDLE_HOURS)IDLE_HOURS,
COUNT(IDLE_HOURS)IDLE_HOURS_COUNT,
SUM(UP_HOURS)UP_HOURS,
COUNT(UP_HOURS)UP_HOURS_COUNT,
SUM(REQUIRED_HOURS)MAC_EFF_SUM,
COUNT(REQUIRED_HOURS)MAC_EFF_COUNT,
SUM(WO_ITEM_COUNT)WO_ITEM_SUM,
COUNT(WO_ITEM_COUNT)WO_ITEM_COUNT,
SUM(CASE WHEN (RUN_HOURS-REQUIRED_HOURS) < 0 THEN 0 ELSE RESOURCE_COST*(RUN_HOURS-REQUIRED_HOURS)END)LOSS_INEFF,
COUNT(CASE WHEN (RUN_HOURS-REQUIRED_HOURS) < 0 THEN 0 ELSE RESOURCE_COST*(RUN_HOURS-REQUIRED_HOURS)END)LOSS_INEFF_COUNT,
COUNT(*)TOTAL_COUNT
FROM
MTH_EQUIP_STATUS_SUMMARY EQ_STATUS,
MTH_SHIFT_GREGORIAN_DENORM_MV TIME_D,
MTH_EQUIPMENT_DENORM_D DEPT_HIER,
MTH_EQUIPMENT_DENORM_D CUST_HIER
WHERE
EQ_STATUS.SHIFT_WORKDAY_FK_KEY = TIME_D.SHIFT_WORKDAY_PK_KEY 
AND DEPT_HIER.EQUIPMENT_HIERARCHY_KEY = -2 
AND DEPT_HIER.EQUIPMENT_FK_KEY = EQ_STATUS.EQUIPMENT_FK_KEY 
AND TIME_D.FROM_DATE BETWEEN DEPT_HIER.EQUIPMENT_EFFECTIVE_DATE AND NVL(DEPT_HIER.EQUIPMENT_EXPIRATION_DATE,TO_DATE('01/01/2100','MM/DD/YYYY')) 
AND CUST_HIER.EQUIPMENT_HIERARCHY_KEY = -3 
AND CUST_HIER.EQUIPMENT_FK_KEY = EQ_STATUS.EQUIPMENT_FK_KEY 
AND TIME_D.FROM_DATE BETWEEN CUST_HIER.EQUIPMENT_EFFECTIVE_DATE AND NVL(CUST_HIER.EQUIPMENT_EXPIRATION_DATE,TO_DATE('01/01/2100','MM/DD/YYYY'))
GROUP BY DEPT_HIER.LEVEL8_LEVEL_KEY, DEPT_HIER.LEVEL8_EFFECTIVE_DATE, TIME_D.DAY_ID,TIME_D.CALENDAR_MONTH_ID,CUST_HIER.LEVEL9_LEVEL_KEY,CUST_HIER.LEVEL9_EFFECTIVE_DATE

View Text - HTML Formatted

SELECT TIME_D.DAY_ID
, TIME_D.CALENDAR_MONTH_ID
, DEPT_HIER.LEVEL8_LEVEL_KEY DEPT_KEY
, DEPT_HIER.LEVEL8_EFFECTIVE_DATE DEPT_EFF_DATE
, CUST_HIER.LEVEL9_LEVEL_KEY CUST_LEVEL9_KEY
, CUST_HIER.LEVEL9_EFFECTIVE_DATE CUST_LEVEL9_EFF_DATE
, SUM(DOWN_HOURS)DOWN_HOURS
, COUNT(DOWN_HOURS)DOWN_HOURS_COUNT
, SUM(RESOURCE_COST*DOWN_HOURS)DOWN_HOURS_VAL
, COUNT(RESOURCE_COST*DOWN_HOURS)DOWN_HOURS_VAL_COUNT
, SUM(RUN_HOURS)RUN_HOURS
, COUNT(RUN_HOURS)RUN_HOURS_COUNT
, SUM(RESOURCE_COST*RUN_HOURS)RUN_HOURS_VAL
, COUNT(RESOURCE_COST*RUN_HOURS)RUN_HOURS_VAL_COUNT
, SUM(IDLE_HOURS)IDLE_HOURS
, COUNT(IDLE_HOURS)IDLE_HOURS_COUNT
, SUM(UP_HOURS)UP_HOURS
, COUNT(UP_HOURS)UP_HOURS_COUNT
, SUM(REQUIRED_HOURS)MAC_EFF_SUM
, COUNT(REQUIRED_HOURS)MAC_EFF_COUNT
, SUM(WO_ITEM_COUNT)WO_ITEM_SUM
, COUNT(WO_ITEM_COUNT)WO_ITEM_COUNT
, SUM(CASE WHEN (RUN_HOURS-REQUIRED_HOURS) < 0 THEN 0 ELSE RESOURCE_COST*(RUN_HOURS-REQUIRED_HOURS)END)LOSS_INEFF
, COUNT(CASE WHEN (RUN_HOURS-REQUIRED_HOURS) < 0 THEN 0 ELSE RESOURCE_COST*(RUN_HOURS-REQUIRED_HOURS)END)LOSS_INEFF_COUNT
, COUNT(*)TOTAL_COUNT FROM MTH_EQUIP_STATUS_SUMMARY EQ_STATUS
, MTH_SHIFT_GREGORIAN_DENORM_MV TIME_D
, MTH_EQUIPMENT_DENORM_D DEPT_HIER
, MTH_EQUIPMENT_DENORM_D CUST_HIER WHERE EQ_STATUS.SHIFT_WORKDAY_FK_KEY = TIME_D.SHIFT_WORKDAY_PK_KEY AND DEPT_HIER.EQUIPMENT_HIERARCHY_KEY = -2 AND DEPT_HIER.EQUIPMENT_FK_KEY = EQ_STATUS.EQUIPMENT_FK_KEY AND TIME_D.FROM_DATE BETWEEN DEPT_HIER.EQUIPMENT_EFFECTIVE_DATE
AND NVL(DEPT_HIER.EQUIPMENT_EXPIRATION_DATE
, TO_DATE('01/01/2100'
, 'MM/DD/YYYY')) AND CUST_HIER.EQUIPMENT_HIERARCHY_KEY = -3 AND CUST_HIER.EQUIPMENT_FK_KEY = EQ_STATUS.EQUIPMENT_FK_KEY AND TIME_D.FROM_DATE BETWEEN CUST_HIER.EQUIPMENT_EFFECTIVE_DATE
AND NVL(CUST_HIER.EQUIPMENT_EXPIRATION_DATE
, TO_DATE('01/01/2100'
, 'MM/DD/YYYY')) GROUP BY DEPT_HIER.LEVEL8_LEVEL_KEY
, DEPT_HIER.LEVEL8_EFFECTIVE_DATE
, TIME_D.DAY_ID
, TIME_D.CALENDAR_MONTH_ID
, CUST_HIER.LEVEL9_LEVEL_KEY
, CUST_HIER.LEVEL9_EFFECTIVE_DATE