eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

MATERIALIZED VIEW: APPS.MTH_MTL_PROD_IT_MV

Source


SELECT 
MTL_PROD.ITEM_FK_KEY,
IT_DENORM.LEVEL9_FK_KEY,
MTL_PROD.STATUS_CODE,
TIME_D.DAY_ID DAY_ID,
TIME_D.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(EXPECTED_YIELD)EXPECTED_YIELD,
COUNT(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(CASE  WHEN  NVL(MTL_PROD.ACTUAL_REJECTED_QTY_G, 0) = 0 THEN  NVL(MTL_PROD.ACTUAL_REWORKED_QTY_G, 0) +  NVL(MTL_PROD.ACTUAL_SCRAP_QTY_G, 0) ELSE MTL_PROD.ACTUAL_REJECTED_QTY_G END)ACTUAL_REJECTED_QTY_G,
COUNT(CASE  WHEN  NVL(MTL_PROD.ACTUAL_REJECTED_QTY_G, 0) = 0 THEN  NVL(MTL_PROD.ACTUAL_REWORKED_QTY_G, 0) +  NVL(MTL_PROD.ACTUAL_SCRAP_QTY_G, 0) ELSE MTL_PROD.ACTUAL_REJECTED_QTY_G END)ACTUAL_REJECTED_QTY_G_COUNT,
SUM(CASE  WHEN  NVL(MTL_PROD.ACTUAL_REJECTED_QTY_SG, 0) = 0 THEN  NVL(MTL_PROD.ACTUAL_REWORKED_QTY_SG, 0) +  NVL(MTL_PROD.ACTUAL_SCRAP_QTY_SG, 0) ELSE MTL_PROD.ACTUAL_REJECTED_QTY_SG END)ACTUAL_REJECTED_QTY_SG,
COUNT(CASE  WHEN  NVL(MTL_PROD.ACTUAL_REJECTED_QTY_SG, 0) = 0 THEN  NVL(MTL_PROD.ACTUAL_REWORKED_QTY_SG, 0) +  NVL(MTL_PROD.ACTUAL_SCRAP_QTY_SG, 0) ELSE MTL_PROD.ACTUAL_REJECTED_QTY_SG END)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.ACTUAL_COMPLETION_DATE-MTL_PROD.ACTUAL_START_DATE)*24)BATCH_CYCLE_TIME_SUM,
COUNT((MTL_PROD.ACTUAL_COMPLETION_DATE-MTL_PROD.ACTUAL_START_DATE)*24)BATCH_CYCLE_TIME_COUNT,
SUM(NVL(MTL_PROD.ACTUAL_COMP_QTY_G,0)/(CASE WHEN (MTL_PROD.ACTUAL_COMP_QTY_G + (CASE  WHEN  NVL(MTL_PROD.ACTUAL_REJECTED_QTY_G, 0) = 0 THEN  NVL(MTL_PROD.ACTUAL_REWORKED_QTY_G,0) +  NVL(MTL_PROD.ACTUAL_SCRAP_QTY_G,0) ELSE MTL_PROD.ACTUAL_REJECTED_QTY_G END)) = 0 THEN NULL ELSE (MTL_PROD.ACTUAL_COMP_QTY_G + (CASE  WHEN  NVL(MTL_PROD.ACTUAL_REJECTED_QTY_G, 0) = 0 THEN  NVL(MTL_PROD.ACTUAL_REWORKED_QTY_G,0) +  NVL(MTL_PROD.ACTUAL_SCRAP_QTY_G,0) ELSE MTL_PROD.ACTUAL_REJECTED_QTY_G END))END))FIRST_TIME_QTY,
COUNT(NVL(MTL_PROD.ACTUAL_COMP_QTY_G,0)/(CASE WHEN (MTL_PROD.ACTUAL_COMP_QTY_G + (CASE  WHEN  NVL(MTL_PROD.ACTUAL_REJECTED_QTY_G, 0) = 0 THEN  NVL(MTL_PROD.ACTUAL_REWORKED_QTY_G,0) +  NVL(MTL_PROD.ACTUAL_SCRAP_QTY_G,0) ELSE MTL_PROD.ACTUAL_REJECTED_QTY_G END)) = 0 THEN NULL ELSE (MTL_PROD.ACTUAL_COMP_QTY_G + (CASE  WHEN  NVL(MTL_PROD.ACTUAL_REJECTED_QTY_G, 0) = 0 THEN  NVL(MTL_PROD.ACTUAL_REWORKED_QTY_G,0) +  NVL(MTL_PROD.ACTUAL_SCRAP_QTY_G,0) ELSE MTL_PROD.ACTUAL_REJECTED_QTY_G END))END))FIRST_TIME_QTY_COUNT,
COUNT(*)TOTAL_COUNT
FROM
MTH_PROD_MTL_PRODUCED_F MTL_PROD,
MTH_GREGORIAN_CALENDAR TIME_D,
MTH_ITEM_DENORM_D IT_DENORM
WHERE
TRUNC(MTL_PROD.PLANNED_COMPLETION_DATE) = TIME_D.DAY 
AND IT_DENORM.HIERARCHY_ID = -5
AND MTL_PROD.ITEM_FK_KEY = IT_DENORM.ITEM_FK_KEY
GROUP BY IT_DENORM.LEVEL9_FK_KEY, MTL_PROD.ITEM_FK_KEY, STATUS_CODE, CALENDAR_MONTH_ID, DAY_ID