View: WIPFV_FLOW_SCHED_COMPS

Product:	WIP - Work in Process
Description:
Implementation/DBA Data:	APPS.WIPFV_FLOW_SCHED_COMPS

View Text

SELECT ORGANIZATION_CODE
, ORGANIZATION_NAME
, MFG_ORDER_NAME
, OPERATION_SEQUENCE_NUMBER
, SUM(REQUIRED_QUANTITY)
, SUM(COMPLETED_QUANTITY)
, WIP_ENTITY_ID
, COMPONENT_SEQUENCE_ID
, INVENTORY_ITEM_ID
, ORGANIZATION_ID
FROM ( SELECT PARA.ORGANIZATION_CODE ORGANIZATION_CODE
, ORG.NAME ORGANIZATION_NAME
, ENT.WIP_ENTITY_NAME MFG_ORDER_NAME
, 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
FROM MTL_PARAMETERS PARA
, HR_ALL_ORGANIZATION_UNITS ORG
, 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 PARA.ORGANIZATION_ID = FLOW.ORGANIZATION_ID
AND ORG.ORGANIZATION_ID = FLOW.ORGANIZATION_ID
AND '_SEC:FLOW.ORGANIZATION_ID' IS NOT NULL UNION ALL SELECT PARA.ORGANIZATION_CODE ORGANIZATION_CODE
, ORG.NAME ORGANIZATION_NAME
, ENT.WIP_ENTITY_NAME MFG_ORDER_NAME
, 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 MTL_PARAMETERS PARA
, HR_ALL_ORGANIZATION_UNITS ORG
, 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 PARA.ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND ORG.ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND '_SEC:MTL.ORGANIZATION_ID' IS NOT NULL ) GROUP BY ORGANIZATION_CODE
, ORGANIZATION_NAME
, MFG_ORDER_NAME
, OPERATION_SEQUENCE_NUMBER
, WIP_ENTITY_ID
, COMPONENT_SEQUENCE_ID
, INVENTORY_ITEM_ID
, ORGANIZATION_ID WITH READ ONLY

Columns