SELECT
MTL_CONS.DAY_ID,
MTL_CONS.CALENDAR_MONTH_ID,
MTL_CONS.PRODUCT_FK_KEY,
MTL_CONS.PRODUCT_LEVEL9_KEY,
MTL_CONS.ITEM_LEVEL9_KEY,
SUM(MTL_CONS.PLANNED_USAGE_QTY_G)PLANNED_USAGE_QTY_G,
COUNT(MTL_CONS.PLANNED_USAGE_QTY_G) PLANNED_QTY_G_COUNT,
SUM(MTL_CONS.PLANNED_USAGE_QTY_SG)PLANNED_USAGE_QTY_SG,
COUNT(MTL_CONS.PLANNED_USAGE_QTY_SG) PLANNED_USAGE_QTY_SG_COUNT,
SUM(MTL_CONS.PLANNED_USAGE_VAL)PLANNED_USAGE_VAL,
COUNT(MTL_CONS.PLANNED_USAGE_VAL)PLANNED_USAGE_VAL_COUNT,
SUM(MTL_CONS.ACTUAL_USAGE_QTY_G)ACTUAL_USAGE_QTY_G,
COUNT(MTL_CONS.ACTUAL_USAGE_QTY_G)ACTUAL_USAGE_QTY_G_COUNT,
SUM(MTL_CONS.ACTUAL_USAGE_QTY_SG)ACTUAL_USAGE_QTY_SG,
COUNT(MTL_CONS.ACTUAL_USAGE_QTY_SG)ACTUAL_USAGE_QTY_SG_COUNT,
SUM(MTL_CONS.ACTUAL_USAGE_VAL)ACTUAL_USAGE_VAL,
COUNT(MTL_CONS.ACTUAL_USAGE_VAL)ACTUAL_USAGE_VAL_COUNT,
SUM(MTL_CONS.UNIT_STANDARD_QTY_G)UNIT_STANDARD_QTY_G,
COUNT(MTL_CONS.UNIT_STANDARD_QTY_G)UNIT_STANDARD_QTY_G_COUNT,
SUM(MTL_CONS.UNIT_STANDARD_QTY_SG)UNIT_STANDARD_QTY_SG,
COUNT(MTL_CONS.UNIT_STANDARD_QTY_SG)UNIT_STANDARD_QTY_SG_COUNT,
COUNT(*) AS TOTAL_COUNT,
GROUPING_ID(DAY_ID,CALENDAR_MONTH_ID,PRODUCT_FK_KEY,PRODUCT_LEVEL9_KEY,ITEM_LEVEL9_KEY) 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, PRODUCT_LEVEL9_KEY),
1,PRODUCT_FK_KEY,
2,PRODUCT_LEVEL9_KEY)PRODUCT_DIM_KEY,
DECODE (GROUPING_ID(PRODUCT_FK_KEY, PRODUCT_LEVEL9_KEY),
1,10,
2,9)PRODUCT_DIM_LEVEL
FROM
MTH_MTL_CONS_IT_MV MTL_CONS
GROUP BY GROUPING SETS ((ITEM_LEVEL9_KEY,PRODUCT_FK_KEY,CALENDAR_MONTH_ID),
(ITEM_LEVEL9_KEY,PRODUCT_LEVEL9_KEY,DAY_ID),
(ITEM_LEVEL9_KEY,PRODUCT_LEVEL9_KEY,CALENDAR_MONTH_ID))
SELECT
MTL_CONS.DAY_ID
,
MTL_CONS.CALENDAR_MONTH_ID
,
MTL_CONS.PRODUCT_FK_KEY
,
MTL_CONS.PRODUCT_LEVEL9_KEY
,
MTL_CONS.ITEM_LEVEL9_KEY
,
SUM(MTL_CONS.PLANNED_USAGE_QTY_G)PLANNED_USAGE_QTY_G
,
COUNT(MTL_CONS.PLANNED_USAGE_QTY_G) PLANNED_QTY_G_COUNT
,
SUM(MTL_CONS.PLANNED_USAGE_QTY_SG)PLANNED_USAGE_QTY_SG
,
COUNT(MTL_CONS.PLANNED_USAGE_QTY_SG) PLANNED_USAGE_QTY_SG_COUNT
,
SUM(MTL_CONS.PLANNED_USAGE_VAL)PLANNED_USAGE_VAL
,
COUNT(MTL_CONS.PLANNED_USAGE_VAL)PLANNED_USAGE_VAL_COUNT
,
SUM(MTL_CONS.ACTUAL_USAGE_QTY_G)ACTUAL_USAGE_QTY_G
,
COUNT(MTL_CONS.ACTUAL_USAGE_QTY_G)ACTUAL_USAGE_QTY_G_COUNT
,
SUM(MTL_CONS.ACTUAL_USAGE_QTY_SG)ACTUAL_USAGE_QTY_SG
,
COUNT(MTL_CONS.ACTUAL_USAGE_QTY_SG)ACTUAL_USAGE_QTY_SG_COUNT
,
SUM(MTL_CONS.ACTUAL_USAGE_VAL)ACTUAL_USAGE_VAL
,
COUNT(MTL_CONS.ACTUAL_USAGE_VAL)ACTUAL_USAGE_VAL_COUNT
,
SUM(MTL_CONS.UNIT_STANDARD_QTY_G)UNIT_STANDARD_QTY_G
,
COUNT(MTL_CONS.UNIT_STANDARD_QTY_G)UNIT_STANDARD_QTY_G_COUNT
,
SUM(MTL_CONS.UNIT_STANDARD_QTY_SG)UNIT_STANDARD_QTY_SG
,
COUNT(MTL_CONS.UNIT_STANDARD_QTY_SG)UNIT_STANDARD_QTY_SG_COUNT
,
COUNT(*) AS TOTAL_COUNT
,
GROUPING_ID(DAY_ID
, CALENDAR_MONTH_ID
, PRODUCT_FK_KEY
, PRODUCT_LEVEL9_KEY
, ITEM_LEVEL9_KEY) 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
, PRODUCT_LEVEL9_KEY)
,
1
, PRODUCT_FK_KEY
,
2
, PRODUCT_LEVEL9_KEY)PRODUCT_DIM_KEY
,
DECODE (GROUPING_ID(PRODUCT_FK_KEY
, PRODUCT_LEVEL9_KEY)
,
1
, 10
,
2
, 9)PRODUCT_DIM_LEVEL
FROM
MTH_MTL_CONS_IT_MV MTL_CONS
GROUP BY GROUPING SETS ((ITEM_LEVEL9_KEY
, PRODUCT_FK_KEY
, CALENDAR_MONTH_ID)
,
(ITEM_LEVEL9_KEY
, PRODUCT_LEVEL9_KEY
, DAY_ID)
,
(ITEM_LEVEL9_KEY
, PRODUCT_LEVEL9_KEY
, CALENDAR_MONTH_ID))
|
|
|