SELECT
MTL_PROD.ITEM_FK_KEY,
MTL_PROD.LEVEL9_FK_KEY,
MTL_PROD.STATUS_CODE,
MTL_PROD.DAY_ID DAY_ID,
MTL_PROD.CALENDAR_MONTH_ID CALENDAR_MONTH_ID,
SUM(MTL_PROD.START_QUANTITY_G) START_QUANTITY_G,
COUNT(MTL_PROD.START_QUANTITY_G) START_QUANTITY_G_COUNT,
SUM(MTL_PROD.EXPECTED_YIELD)EXPECTED_YIELD,
COUNT(MTL_PROD.EXPECTED_YIELD)EXPECTED_YIELD_COUNT,
SUM(MTL_PROD.START_QUANTITY_SG) START_QUANTITY_SG,
COUNT(MTL_PROD.START_QUANTITY_SG) START_QUANTITY_SG_COUNT,
SUM(MTL_PROD.ACTUAL_COMP_QTY_G) ACTUAL_COMP_QTY_G,
COUNT(MTL_PROD.ACTUAL_COMP_QTY_G) ACTUAL_COMP_QTY_G_COUNT,
SUM(MTL_PROD.ACTUAL_COMP_QTY_SG) ACTUAL_COMP_QTY_SG,
COUNT(MTL_PROD.ACTUAL_COMP_QTY_SG) ACTUAL_COMP_QTY_SG_COUNT,
SUM(ACTUAL_REJECTED_QTY_G) ACTUAL_REJECTED_QTY_G,
COUNT(ACTUAL_REJECTED_QTY_G) ACTUAL_REJECTED_QTY_G_COUNT,
SUM(ACTUAL_REJECTED_QTY_SG) ACTUAL_REJECTED_QTY_SG,
COUNT(ACTUAL_REJECTED_QTY_SG) ACTUAL_REJECTED_QTY_SG_COUNT,
SUM(MTL_PROD.ACTUAL_REWORKED_QTY_G)ACTUAL_REWORKED_QTY_G,
COUNT(MTL_PROD.ACTUAL_REWORKED_QTY_G)ACTUAL_REWORKED_QTY_G_COUNT,
SUM(MTL_PROD.ACTUAL_REWORKED_QTY_SG)ACTUAL_REWORKED_QTY_SG,
COUNT(MTL_PROD.ACTUAL_REWORKED_QTY_SG)ACTUAL_REWORKED_QTY_SG_COUNT,
SUM(MTL_PROD.ACTUAL_SCRAP_QTY_G)ACTUAL_SCRAP_QTY_G,
COUNT(MTL_PROD.ACTUAL_SCRAP_QTY_G)ACTUAL_SCRAP_QTY_G_COUNT,
SUM(MTL_PROD.ACTUAL_SCRAP_QTY_SG)ACTUAL_SCRAP_QTY_SG,
COUNT(MTL_PROD.ACTUAL_SCRAP_QTY_SG)ACTUAL_SCRAP_QTY_SG_COUNT,
SUM(MTL_PROD.BATCH_CYCLE_TIME_SUM)BATCH_CYCLE_TIME_SUM,
COUNT(MTL_PROD.BATCH_CYCLE_TIME_SUM)BATCH_CTS_COUNT,
SUM(MTL_PROD.BATCH_CYCLE_TIME_COUNT)BATCH_CYCLE_TIME_COUNT,
COUNT(MTL_PROD.BATCH_CYCLE_TIME_COUNT)BATCH_CTC_COUNT,
SUM(FIRST_TIME_QTY) FIRST_TIME_QTY,
COUNT(FIRST_TIME_QTY)FTS_COUNT,
SUM(FIRST_TIME_QTY_COUNT)FIRST_TIME_QTY_COUNT,
COUNT(FIRST_TIME_QTY_COUNT)FTC_COUNT,
COUNT(*)TOTAL_COUNT,
GROUPING_ID (ITEM_FK_KEY,LEVEL9_FK_KEY,DAY_ID,CALENDAR_MONTH_ID,STATUS_CODE) 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(ITEM_FK_KEY, LEVEL9_FK_KEY),
1,ITEM_FK_KEY,
2,LEVEL9_FK_KEY)ITEM_DIM_KEY,
DECODE (GROUPING_ID(ITEM_FK_KEY, LEVEL9_FK_KEY),
1,10,
2,9)ITEM_DIM_LEVEL
FROM
MTH_MTL_PROD_IT_MV MTL_PROD
GROUP BY GROUPING SETS ((ITEM_FK_KEY,STATUS_CODE, CALENDAR_MONTH_ID),
(LEVEL9_FK_KEY,STATUS_CODE,DAY_ID),
(LEVEL9_FK_KEY,STATUS_CODE,CALENDAR_MONTH_ID))
SELECT
MTL_PROD.ITEM_FK_KEY
,
MTL_PROD.LEVEL9_FK_KEY
,
MTL_PROD.STATUS_CODE
,
MTL_PROD.DAY_ID DAY_ID
,
MTL_PROD.CALENDAR_MONTH_ID CALENDAR_MONTH_ID
,
SUM(MTL_PROD.START_QUANTITY_G) START_QUANTITY_G
,
COUNT(MTL_PROD.START_QUANTITY_G) START_QUANTITY_G_COUNT
,
SUM(MTL_PROD.EXPECTED_YIELD)EXPECTED_YIELD
,
COUNT(MTL_PROD.EXPECTED_YIELD)EXPECTED_YIELD_COUNT
,
SUM(MTL_PROD.START_QUANTITY_SG) START_QUANTITY_SG
,
COUNT(MTL_PROD.START_QUANTITY_SG) START_QUANTITY_SG_COUNT
,
SUM(MTL_PROD.ACTUAL_COMP_QTY_G) ACTUAL_COMP_QTY_G
,
COUNT(MTL_PROD.ACTUAL_COMP_QTY_G) ACTUAL_COMP_QTY_G_COUNT
,
SUM(MTL_PROD.ACTUAL_COMP_QTY_SG) ACTUAL_COMP_QTY_SG
,
COUNT(MTL_PROD.ACTUAL_COMP_QTY_SG) ACTUAL_COMP_QTY_SG_COUNT
,
SUM(ACTUAL_REJECTED_QTY_G) ACTUAL_REJECTED_QTY_G
,
COUNT(ACTUAL_REJECTED_QTY_G) ACTUAL_REJECTED_QTY_G_COUNT
,
SUM(ACTUAL_REJECTED_QTY_SG) ACTUAL_REJECTED_QTY_SG
,
COUNT(ACTUAL_REJECTED_QTY_SG) ACTUAL_REJECTED_QTY_SG_COUNT
,
SUM(MTL_PROD.ACTUAL_REWORKED_QTY_G)ACTUAL_REWORKED_QTY_G
,
COUNT(MTL_PROD.ACTUAL_REWORKED_QTY_G)ACTUAL_REWORKED_QTY_G_COUNT
,
SUM(MTL_PROD.ACTUAL_REWORKED_QTY_SG)ACTUAL_REWORKED_QTY_SG
,
COUNT(MTL_PROD.ACTUAL_REWORKED_QTY_SG)ACTUAL_REWORKED_QTY_SG_COUNT
,
SUM(MTL_PROD.ACTUAL_SCRAP_QTY_G)ACTUAL_SCRAP_QTY_G
,
COUNT(MTL_PROD.ACTUAL_SCRAP_QTY_G)ACTUAL_SCRAP_QTY_G_COUNT
,
SUM(MTL_PROD.ACTUAL_SCRAP_QTY_SG)ACTUAL_SCRAP_QTY_SG
,
COUNT(MTL_PROD.ACTUAL_SCRAP_QTY_SG)ACTUAL_SCRAP_QTY_SG_COUNT
,
SUM(MTL_PROD.BATCH_CYCLE_TIME_SUM)BATCH_CYCLE_TIME_SUM
,
COUNT(MTL_PROD.BATCH_CYCLE_TIME_SUM)BATCH_CTS_COUNT
,
SUM(MTL_PROD.BATCH_CYCLE_TIME_COUNT)BATCH_CYCLE_TIME_COUNT
,
COUNT(MTL_PROD.BATCH_CYCLE_TIME_COUNT)BATCH_CTC_COUNT
,
SUM(FIRST_TIME_QTY) FIRST_TIME_QTY
,
COUNT(FIRST_TIME_QTY)FTS_COUNT
,
SUM(FIRST_TIME_QTY_COUNT)FIRST_TIME_QTY_COUNT
,
COUNT(FIRST_TIME_QTY_COUNT)FTC_COUNT
,
COUNT(*)TOTAL_COUNT
,
GROUPING_ID (ITEM_FK_KEY
, LEVEL9_FK_KEY
, DAY_ID
, CALENDAR_MONTH_ID
, STATUS_CODE) 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(ITEM_FK_KEY
, LEVEL9_FK_KEY)
,
1
, ITEM_FK_KEY
,
2
, LEVEL9_FK_KEY)ITEM_DIM_KEY
,
DECODE (GROUPING_ID(ITEM_FK_KEY
, LEVEL9_FK_KEY)
,
1
, 10
,
2
, 9)ITEM_DIM_LEVEL
FROM
MTH_MTL_PROD_IT_MV MTL_PROD
GROUP BY GROUPING SETS ((ITEM_FK_KEY
, STATUS_CODE
, CALENDAR_MONTH_ID)
,
(LEVEL9_FK_KEY
, STATUS_CODE
, DAY_ID)
,
(LEVEL9_FK_KEY
, STATUS_CODE
, CALENDAR_MONTH_ID))
|
|
|