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