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.

VIEW: APPS.MTH_EQUIP_OP_SUM_DD_MV#

Source

View Text - Preformatted


SELECT
TIME_D.DAY_ID,
TIME_D.CALENDAR_MONTH_ID,
WORK_OR.STATUS_CODE,
IT_DENORM.LEVEL9_FK_KEY PRODUCT_LEVEL9_KEY,
DEPT_HIER.LEVEL8_LEVEL_KEY DEPT_KEY,
DEPT_HIER.LEVEL8_EFFECTIVE_DATE DEPT_EFF_DATE,
CUST_HIER.LEVEL9_LEVEL_KEY CUST_LEVEL9_KEY,
CUST_HIER.LEVEL9_EFFECTIVE_DATE CUST_LEVEL9_EFF_DATE,
SUM(CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END)REJECTED_QTY,
COUNT(CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END)REJECTED_QTY_COUNT,
SUM(QTY_REWORK)QTY_REWORK,
COUNT(QTY_REWORK)QTY_REWORK_COUNT,
SUM(QTY_SCRAP)QTY_SCRAP,
COUNT(QTY_SCRAP)QTY_SCRAP_COUNT,
SUM(QTY_GOOD)QTY_GOOD,
COUNT(QTY_GOOD)QTY_GOOD_COUNT,
SUM(CASE WHEN NVL(QTY_OUTPUT,0) = 0 THEN NVL(QTY_COMPLETED,0)+ CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END)OUTPUT_QTY,
COUNT(CASE WHEN NVL(QTY_OUTPUT,0) = 0 THEN NVL(QTY_COMPLETED,0)+ CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END)OUTPUT_QTY_COUNT,
SUM(CASE WHEN NVL(QTY_COMPLETED,0)=0 THEN NVL(QTY_OUTPUT,0)- CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)COMPLETED_QTY,
COUNT(CASE WHEN NVL(QTY_COMPLETED,0)=0 THEN NVL(QTY_OUTPUT,0)- CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)COMPLETED_QTY_COUNT,
SUM((CASE WHEN NVL(QTY_COMPLETED,0)=0 THEN NVL(QTY_OUTPUT,0)- CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/STANDARD_RATE_1)HRS_ACT_COMP_QTY,
COUNT((CASE WHEN NVL(QTY_COMPLETED,0)=0 THEN NVL(QTY_OUTPUT,0)- CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/STANDARD_RATE_1)HRS_ACT_COMP_QTY_COUNT,
SUM((CASE WHEN NVL(QTY_COMPLETED,0)=0 THEN NVL(QTY_OUTPUT,0)- CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)*ITEM_COST)SHIFT_ACT_PROD,
COUNT((CASE WHEN NVL(QTY_COMPLETED,0)=0 THEN NVL(QTY_OUTPUT,0)- CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)*ITEM_COST)SHIFT_ACT_PROD_COUNT,
SUM(((CASE WHEN NVL(QTY_COMPLETED,0)=0 THEN NVL(QTY_OUTPUT,0)- CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/STANDARD_RATE_1)*(1-((CASE WHEN NVL(QTY_COMPLETED,0)=0 THEN NVL(QTY_OUTPUT,0)- CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/(CASE WHEN (CASE WHEN NVL(QTY_OUTPUT,0) = 0 THEN NVL(QTY_COMPLETED,0)+ CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END) = 0 THEN NULL ELSE (CASE WHEN NVL(QTY_OUTPUT,0) = 0 THEN NVL(QTY_COMPLETED,0)+ CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END) END))))LOSS_BAD_QUALITY,
COUNT(((CASE WHEN NVL(QTY_COMPLETED,0)=0 THEN NVL(QTY_OUTPUT,0)- CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/STANDARD_RATE_1)*(1-((CASE WHEN NVL(QTY_COMPLETED,0)=0 THEN NVL(QTY_OUTPUT,0)- CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/(CASE WHEN (CASE WHEN NVL(QTY_OUTPUT,0) = 0 THEN NVL(QTY_COMPLETED,0)+ CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END) = 0 THEN NULL ELSE (CASE WHEN NVL(QTY_OUTPUT,0) = 0 THEN NVL(QTY_COMPLETED,0)+ CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END) END))))LOSS_BAD_QUALITY_COUNT,
SUM(RESOURCE_COST*((CASE WHEN NVL(QTY_COMPLETED,0)=0 THEN NVL(QTY_OUTPUT,0)- CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/STANDARD_RATE_1)*(1-((CASE WHEN NVL(QTY_COMPLETED,0)=0 THEN NVL(QTY_OUTPUT,0)- CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/(CASE WHEN (CASE WHEN NVL(QTY_OUTPUT,0) = 0 THEN NVL(QTY_COMPLETED,0)+ CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END) = 0 THEN NULL ELSE (CASE WHEN NVL(QTY_OUTPUT,0) = 0 THEN NVL(QTY_COMPLETED,0)+ CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END) END))))LOSS_BAD_QUALTY_VAL,
COUNT(RESOURCE_COST*((CASE WHEN NVL(QTY_COMPLETED,0)=0 THEN NVL(QTY_OUTPUT,0)- CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/STANDARD_RATE_1)*(1-((CASE WHEN NVL(QTY_COMPLETED,0)=0 THEN NVL(QTY_OUTPUT,0)- CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/(CASE WHEN (CASE WHEN NVL(QTY_OUTPUT,0) = 0 THEN NVL(QTY_COMPLETED,0)+ CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END) = 0 THEN NULL ELSE (CASE WHEN NVL(QTY_OUTPUT,0) = 0 THEN NVL(QTY_COMPLETED,0)+ CASE WHEN NVL((QTY_REJECTED),0) = 0 THEN NVL(QTY_REWORK,0)+NVL(QTY_SCRAP,0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END) END))))LOSS_BAD_QUALTY_VAL_COUNT,
COUNT(*)TOTAL_COUNT
FROM
MTH_EQUIP_OUTPUT_SUMMARY EQ_OUTPUT,
MTH_PRODUCTION_SCHEDULES_F WORK_OR,
MTH_SHIFT_GREGORIAN_DENORM_MV TIME_D,
MTH_ITEM_DENORM_D IT_DENORM,
MTH_EQUIPMENT_DENORM_D DEPT_HIER,
MTH_EQUIPMENT_DENORM_D CUST_HIER
WHERE
EQ_OUTPUT.SHIFT_WORKDAY_FK_KEY = TIME_D.SHIFT_WORKDAY_PK_KEY 
AND EQ_OUTPUT.WORKORDER_FK_KEY = WORK_OR.WORKORDER_PK_KEY
AND IT_DENORM.HIERARCHY_ID = -5
AND IT_DENORM.ITEM_FK_KEY = EQ_OUTPUT.ITEM_FK_KEY 
AND DEPT_HIER.EQUIPMENT_HIERARCHY_KEY = -2 
AND DEPT_HIER.EQUIPMENT_FK_KEY = EQ_OUTPUT.EQUIPMENT_FK_KEY 
AND TIME_D.FROM_DATE BETWEEN DEPT_HIER.EQUIPMENT_EFFECTIVE_DATE AND NVL(DEPT_HIER.EQUIPMENT_EXPIRATION_DATE,TO_DATE('01/01/2100','MM/DD/YYYY')) 
AND CUST_HIER.EQUIPMENT_HIERARCHY_KEY = -3 
AND CUST_HIER.EQUIPMENT_FK_KEY = EQ_OUTPUT.EQUIPMENT_FK_KEY 
AND TIME_D.FROM_DATE BETWEEN CUST_HIER.EQUIPMENT_EFFECTIVE_DATE AND NVL(CUST_HIER.EQUIPMENT_EXPIRATION_DATE,TO_DATE('01/01/2100','MM/DD/YYYY'))
GROUP BY DEPT_HIER.LEVEL8_LEVEL_KEY, DEPT_HIER.LEVEL8_EFFECTIVE_DATE, TIME_D.DAY_ID,TIME_D.CALENDAR_MONTH_ID,IT_DENORM.LEVEL9_FK_KEY, CUST_HIER.LEVEL9_LEVEL_KEY,CUST_HIER.LEVEL9_EFFECTIVE_DATE,STATUS_CODE

View Text - HTML Formatted

SELECT TIME_D.DAY_ID
, TIME_D.CALENDAR_MONTH_ID
, WORK_OR.STATUS_CODE
, IT_DENORM.LEVEL9_FK_KEY PRODUCT_LEVEL9_KEY
, DEPT_HIER.LEVEL8_LEVEL_KEY DEPT_KEY
, DEPT_HIER.LEVEL8_EFFECTIVE_DATE DEPT_EFF_DATE
, CUST_HIER.LEVEL9_LEVEL_KEY CUST_LEVEL9_KEY
, CUST_HIER.LEVEL9_EFFECTIVE_DATE CUST_LEVEL9_EFF_DATE
, SUM(CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END)REJECTED_QTY
, COUNT(CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END)REJECTED_QTY_COUNT
, SUM(QTY_REWORK)QTY_REWORK
, COUNT(QTY_REWORK)QTY_REWORK_COUNT
, SUM(QTY_SCRAP)QTY_SCRAP
, COUNT(QTY_SCRAP)QTY_SCRAP_COUNT
, SUM(QTY_GOOD)QTY_GOOD
, COUNT(QTY_GOOD)QTY_GOOD_COUNT
, SUM(CASE WHEN NVL(QTY_OUTPUT
, 0) = 0 THEN NVL(QTY_COMPLETED
, 0)+ CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END)OUTPUT_QTY
, COUNT(CASE WHEN NVL(QTY_OUTPUT
, 0) = 0 THEN NVL(QTY_COMPLETED
, 0)+ CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END)OUTPUT_QTY_COUNT
, SUM(CASE WHEN NVL(QTY_COMPLETED
, 0)=0 THEN NVL(QTY_OUTPUT
, 0)- CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)COMPLETED_QTY
, COUNT(CASE WHEN NVL(QTY_COMPLETED
, 0)=0 THEN NVL(QTY_OUTPUT
, 0)- CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)COMPLETED_QTY_COUNT
, SUM((CASE WHEN NVL(QTY_COMPLETED
, 0)=0 THEN NVL(QTY_OUTPUT
, 0)- CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/STANDARD_RATE_1)HRS_ACT_COMP_QTY
, COUNT((CASE WHEN NVL(QTY_COMPLETED
, 0)=0 THEN NVL(QTY_OUTPUT
, 0)- CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/STANDARD_RATE_1)HRS_ACT_COMP_QTY_COUNT
, SUM((CASE WHEN NVL(QTY_COMPLETED
, 0)=0 THEN NVL(QTY_OUTPUT
, 0)- CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)*ITEM_COST)SHIFT_ACT_PROD
, COUNT((CASE WHEN NVL(QTY_COMPLETED
, 0)=0 THEN NVL(QTY_OUTPUT
, 0)- CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)*ITEM_COST)SHIFT_ACT_PROD_COUNT
, SUM(((CASE WHEN NVL(QTY_COMPLETED
, 0)=0 THEN NVL(QTY_OUTPUT
, 0)- CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/STANDARD_RATE_1)*(1-((CASE WHEN NVL(QTY_COMPLETED
, 0)=0 THEN NVL(QTY_OUTPUT
, 0)- CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/(CASE WHEN (CASE WHEN NVL(QTY_OUTPUT
, 0) = 0 THEN NVL(QTY_COMPLETED
, 0)+ CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END) = 0 THEN NULL ELSE (CASE WHEN NVL(QTY_OUTPUT
, 0) = 0 THEN NVL(QTY_COMPLETED
, 0)+ CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END) END))))LOSS_BAD_QUALITY
, COUNT(((CASE WHEN NVL(QTY_COMPLETED
, 0)=0 THEN NVL(QTY_OUTPUT
, 0)- CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/STANDARD_RATE_1)*(1-((CASE WHEN NVL(QTY_COMPLETED
, 0)=0 THEN NVL(QTY_OUTPUT
, 0)- CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/(CASE WHEN (CASE WHEN NVL(QTY_OUTPUT
, 0) = 0 THEN NVL(QTY_COMPLETED
, 0)+ CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END) = 0 THEN NULL ELSE (CASE WHEN NVL(QTY_OUTPUT
, 0) = 0 THEN NVL(QTY_COMPLETED
, 0)+ CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END) END))))LOSS_BAD_QUALITY_COUNT
, SUM(RESOURCE_COST*((CASE WHEN NVL(QTY_COMPLETED
, 0)=0 THEN NVL(QTY_OUTPUT
, 0)- CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/STANDARD_RATE_1)*(1-((CASE WHEN NVL(QTY_COMPLETED
, 0)=0 THEN NVL(QTY_OUTPUT
, 0)- CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/(CASE WHEN (CASE WHEN NVL(QTY_OUTPUT
, 0) = 0 THEN NVL(QTY_COMPLETED
, 0)+ CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END) = 0 THEN NULL ELSE (CASE WHEN NVL(QTY_OUTPUT
, 0) = 0 THEN NVL(QTY_COMPLETED
, 0)+ CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END) END))))LOSS_BAD_QUALTY_VAL
, COUNT(RESOURCE_COST*((CASE WHEN NVL(QTY_COMPLETED
, 0)=0 THEN NVL(QTY_OUTPUT
, 0)- CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/STANDARD_RATE_1)*(1-((CASE WHEN NVL(QTY_COMPLETED
, 0)=0 THEN NVL(QTY_OUTPUT
, 0)- CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_COMPLETED END)/(CASE WHEN (CASE WHEN NVL(QTY_OUTPUT
, 0) = 0 THEN NVL(QTY_COMPLETED
, 0)+ CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END) = 0 THEN NULL ELSE (CASE WHEN NVL(QTY_OUTPUT
, 0) = 0 THEN NVL(QTY_COMPLETED
, 0)+ CASE WHEN NVL((QTY_REJECTED)
, 0) = 0 THEN NVL(QTY_REWORK
, 0)+NVL(QTY_SCRAP
, 0) ELSE QTY_REJECTED END ELSE QTY_OUTPUT END) END))))LOSS_BAD_QUALTY_VAL_COUNT
, COUNT(*)TOTAL_COUNT FROM MTH_EQUIP_OUTPUT_SUMMARY EQ_OUTPUT
, MTH_PRODUCTION_SCHEDULES_F WORK_OR
, MTH_SHIFT_GREGORIAN_DENORM_MV TIME_D
, MTH_ITEM_DENORM_D IT_DENORM
, MTH_EQUIPMENT_DENORM_D DEPT_HIER
, MTH_EQUIPMENT_DENORM_D CUST_HIER WHERE EQ_OUTPUT.SHIFT_WORKDAY_FK_KEY = TIME_D.SHIFT_WORKDAY_PK_KEY AND EQ_OUTPUT.WORKORDER_FK_KEY = WORK_OR.WORKORDER_PK_KEY AND IT_DENORM.HIERARCHY_ID = -5 AND IT_DENORM.ITEM_FK_KEY = EQ_OUTPUT.ITEM_FK_KEY AND DEPT_HIER.EQUIPMENT_HIERARCHY_KEY = -2 AND DEPT_HIER.EQUIPMENT_FK_KEY = EQ_OUTPUT.EQUIPMENT_FK_KEY AND TIME_D.FROM_DATE BETWEEN DEPT_HIER.EQUIPMENT_EFFECTIVE_DATE
AND NVL(DEPT_HIER.EQUIPMENT_EXPIRATION_DATE
, TO_DATE('01/01/2100'
, 'MM/DD/YYYY')) AND CUST_HIER.EQUIPMENT_HIERARCHY_KEY = -3 AND CUST_HIER.EQUIPMENT_FK_KEY = EQ_OUTPUT.EQUIPMENT_FK_KEY AND TIME_D.FROM_DATE BETWEEN CUST_HIER.EQUIPMENT_EFFECTIVE_DATE
AND NVL(CUST_HIER.EQUIPMENT_EXPIRATION_DATE
, TO_DATE('01/01/2100'
, 'MM/DD/YYYY')) GROUP BY DEPT_HIER.LEVEL8_LEVEL_KEY
, DEPT_HIER.LEVEL8_EFFECTIVE_DATE
, TIME_D.DAY_ID
, TIME_D.CALENDAR_MONTH_ID
, IT_DENORM.LEVEL9_FK_KEY
, CUST_HIER.LEVEL9_LEVEL_KEY
, CUST_HIER.LEVEL9_EFFECTIVE_DATE
, STATUS_CODE