SELECT OPERATION_SEQUENCE_NUMBER , SUM(REQUIRED_QUANTITY) , SUM(COMPLETED_QUANTITY) , WIP_ENTITY_ID , COMPONENT_SEQUENCE_ID , INVENTORY_ITEM_ID , ORGANIZATION_ID FROM ( SELECT EXPL.OPERATION_SEQ_NUM OPERATION_SEQUENCE_NUMBER , EXPL.COMPONENT_QUANTITY * FLOW.PLANNED_QUANTITY REQUIRED_QUANTITY , 0 COMPLETED_QUANTITY , FLOW.WIP_ENTITY_ID WIP_ENTITY_ID , EXPL.COMPONENT_SEQUENCE_ID COMPONENT_SEQUENCE_ID , EXPL.COMPONENT_ITEM_ID INVENTORY_ITEM_ID , FLOW.ORGANIZATION_ID ORGANIZATION_ID FROM WIP_ENTITIES ENT , WIP_FLOW_SCHEDULES FLOW , MTL_SYSTEM_ITEMS SYS , BOM_INVENTORY_COMPONENTS COMP , BOM_EXPLOSIONS EXPL , BOM_BILL_OF_MATERIALS BBM WHERE SYS.PLANNING_MAKE_BUY_CODE = 1 AND BBM.ORGANIZATION_ID = SYS.ORGANIZATION_ID AND COMP.COMPONENT_ITEM_ID = SYS.INVENTORY_ITEM_ID AND COMP.COMPONENT_ITEM_ID = EXPL.COMPONENT_ITEM_ID AND COMP.BILL_SEQUENCE_ID = EXPL.BILL_SEQUENCE_ID AND BBM.ORGANIZATION_ID = FLOW.ORGANIZATION_ID AND BBM.ASSEMBLY_ITEM_ID = FLOW.PRIMARY_ITEM_ID AND ( NVL(BBM.ALTERNATE_BOM_DESIGNATOR , 'ABD756FHH466') = NVL(FLOW.ALTERNATE_BOM_DESIGNATOR , 'ABD756FHH466') OR ( BBM.ALTERNATE_BOM_DESIGNATOR IS NULL AND NOT EXISTS (SELECT NULL FROM BOM_BILL_OF_MATERIALS BBM1 WHERE BBM1.ALTERNATE_BOM_DESIGNATOR = FLOW.ALTERNATE_BOM_DESIGNATOR AND BBM1.ORGANIZATION_ID = BBM.ORGANIZATION_ID AND BBM1.ASSEMBLY_ITEM_ID = BBM.ASSEMBLY_ITEM_ID)) ) AND BBM.COMMON_BILL_SEQUENCE_ID = EXPL.BILL_SEQUENCE_ID AND EXPL.ASSEMBLY_ITEM_ID IS NOT NULL AND EXPL.EFFECTIVITY_DATE <= TRUNC(FLOW.SCHEDULED_START_DATE) AND EXPL.DISABLE_DATE > TRUNC(FLOW.SCHEDULED_START_DATE) AND EXPL.EXPLOSION_TYPE = 'ALL' AND FLOW.WIP_ENTITY_ID = ENT.WIP_ENTITY_ID AND '_SEC:FLOW.ORGANIZATION_ID' IS NOT NULL UNION ALL SELECT MTL.OPERATION_SEQ_NUM OPERATION_SEQUENCE_NUMBER , 0 REQUIRED_QUANTITY , (MTL.TRANSACTION_QUANTITY * -1) COMPLETED_QUANTITY , FLOW.WIP_ENTITY_ID WIP_ENTITY_ID , COMP.COMPONENT_SEQUENCE_ID COMPONENT_SEQUENCE_ID , MTL.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , FLOW.ORGANIZATION_ID ORGANIZATION_ID FROM BOM_INVENTORY_COMPONENTS COMP , MTL_SYSTEM_ITEMS SYS , MTL_MATERIAL_TRANSACTIONS MTL , WIP_ENTITIES ENT , WIP_FLOW_SCHEDULES FLOW WHERE FLOW.WIP_ENTITY_ID = ENT.WIP_ENTITY_ID AND MTL.TRANSACTION_SOURCE_ID = ENT.WIP_ENTITY_ID AND MTL.TRANSACTION_ACTION_ID IN (1 , 27) AND COMP.BILL_SEQUENCE_ID(+) = MTL.COMMON_BOM_SEQ_ID AND COMP.OPERATION_SEQ_NUM(+) = MTL.OPERATION_SEQ_NUM AND COMP.COMPONENT_ITEM_ID(+) = MTL.INVENTORY_ITEM_ID AND SYS.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID AND SYS.ORGANIZATION_ID = MTL.ORGANIZATION_ID AND '_SEC:MTL.ORGANIZATION_ID' IS NOT NULL ) GROUP BY OPERATION_SEQUENCE_NUMBER , WIP_ENTITY_ID , COMPONENT_SEQUENCE_ID , INVENTORY_ITEM_ID , ORGANIZATION_ID WITH READ ONLY