MATERIALIZED VIEW: APPS.MTH_RES_TXN_LT_MV

Source

SELECT
RES_TXN.PRODUCT_FK_KEY,
RES_TXN.LEVEL9_FK_KEY,
SUM(RES_TXN.USAGE_QTY)USAGE_QTY,
COUNT(RES_TXN.USAGE_QTY)USAGE_QTY_COUNT,
SUM(RES_TXN.USAGE_VAL)USAGE_VAL,
COUNT(RES_TXN.USAGE_VAL)USAGE_VAL_COUNT,
RES_TXN.DAY_ID,
RES_TXN.CALENDAR_MONTH_ID,
COUNT(*)TOTAL_COUNT,
GROUPING_ID (PRODUCT_FK_KEY,LEVEL9_FK_KEY,DAY_ID, CALENDAR_MONTH_ID) 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(PRODUCT_FK_KEY, LEVEL9_FK_KEY),
		1,PRODUCT_FK_KEY,
		2,LEVEL9_FK_KEY)ITEM_DIM_KEY,
DECODE (GROUPING_ID(PRODUCT_FK_KEY, LEVEL9_FK_KEY),
		1,10,
		2,9)ITEM_DIM_LEVEL
FROM
MTH_RES_TXN_IT_MV RES_TXN
GROUP BY GROUPING SETS ((PRODUCT_FK_KEY,CALENDAR_MONTH_ID),
						(LEVEL9_FK_KEY,DAY_ID),
						(LEVEL9_FK_KEY,CALENDAR_MONTH_ID))