MATERIALIZED VIEW: APPS.MTH_EQUIP_ST_SUM_PM_MV

Source

SELECT
DAY_ID,
CALENDAR_MONTH_ID,
DEPT_KEY,
DEPT_EFF_DATE,
CUST_LEVEL9_KEY,
CUST_LEVEL9_EFF_DATE,
SUM(DOWN_HOURS)DOWN_HOURS,
COUNT(DOWN_HOURS)DOWN_HOURS_COUNT,
SUM(DOWN_HOURS_VAL)DOWN_HOURS_VAL,
COUNT(DOWN_HOURS_VAL)DOWN_HOURS_VAL_COUNT,
SUM(RUN_HOURS)RUN_HOURS,
COUNT(RUN_HOURS)RUN_HOURS_COUNT,
SUM(RUN_HOURS_VAL)RUN_HOURS_VAL,
COUNT(RUN_HOURS_VAL)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(MAC_EFF_SUM)MAC_EFF_SUM,
COUNT(MAC_EFF_SUM)MAC_EFF_COUNT,
SUM(WO_ITEM_SUM)WO_ITEM_SUM,
COUNT(WO_ITEM_SUM)WO_ITEM_COUNT,
SUM(LOSS_INEFF)LOSS_INEFF,
COUNT(LOSS_INEFF)LOSS_INEFF_COUNT,
COUNT(*)TOTAL_COUNT,
GROUPING_ID(DAY_ID,CALENDAR_MONTH_ID,DEPT_KEY,DEPT_EFF_DATE,CUST_LEVEL9_KEY,CUST_LEVEL9_EFF_DATE)GROUPING_ID,
DECODE (GROUPING_ID(DAY_ID, CALENDAR_MONTH_ID),
		1,DAY_ID,
		2,CALENDAR_MONTH_ID)TIME_DIM_KEY,
DECODE (GROUPING_ID(DAY_ID, CALENDAR_MONTH_ID),
		1,10,
		2,9)TIME_DIM_LEVEL,
DECODE (GROUPING_ID(DEPT_KEY, CUST_LEVEL9_KEY),
		1,-2,
		2,-3)EQUIP_HIER,
DECODE (GROUPING_ID(DEPT_KEY, CUST_LEVEL9_KEY),
		1,DEPT_KEY,
		2,CUST_LEVEL9_KEY)EQUIP_HIER_KEY,
DECODE (GROUPING_ID(DEPT_EFF_DATE, CUST_LEVEL9_EFF_DATE),
		1,DEPT_EFF_DATE,
		2,CUST_LEVEL9_EFF_DATE)EQUIP_HIER_EFF_DATE
FROM
MTH_EQUIP_ST_SUM_DD_MV
GROUP BY GROUPING SETS ((DEPT_KEY,CALENDAR_MONTH_ID,DEPT_EFF_DATE),
						(CUST_LEVEL9_KEY,DAY_ID,CUST_LEVEL9_EFF_DATE),
						(CUST_LEVEL9_KEY,CALENDAR_MONTH_ID,CUST_LEVEL9_EFF_DATE))