View: WIP_EAM_REQUIREMENTS_ALL_V

Product:	WIP - Work in Process
Description:	This view provides all materials for a work order
Implementation/DBA Data:	APPS.WIP_EAM_REQUIREMENTS_ALL_V

View Text

SELECT WRO.ROWID ROW_ID
, WRO.INVENTORY_ITEM_ID
, MSIK.CONCATENATED_SEGMENTS
, MSIK.DESCRIPTION ITEM_DESCRIPTION
, MSIK.PRIMARY_UOM_CODE ITEM_PRIMARY_UOM_CODE
, MSIK.INVENTORY_ASSET_FLAG
, MSIK.LOCATION_CONTROL_CODE
, MSIK.RESTRICT_LOCATORS_CODE
, MSIK.RESTRICT_SUBINVENTORIES_CODE
, WRO.ORGANIZATION_ID
, WRO.WIP_ENTITY_ID
, WRO.OPERATION_SEQ_NUM
, WRO.LAST_UPDATE_DATE
, WRO.LAST_UPDATED_BY
, WRO.CREATION_DATE
, WRO.CREATED_BY
, WRO.LAST_UPDATE_LOGIN
, WRO.REQUEST_ID
, WRO.PROGRAM_APPLICATION_ID
, WRO.PROGRAM_ID
, WRO.PROGRAM_UPDATE_DATE
, WRO.DEPARTMENT_ID
, BD.DEPARTMENT_CODE
, WRO.WIP_SUPPLY_TYPE
, ML1.MEANING WIP_SUPPLY_MEANING
, WRO.DATE_REQUIRED
, WRO.REQUIRED_QUANTITY
, DECODE(WRO.QUANTITY_ISSUED
, 0
, TO_NUMBER(NULL)
, WRO.QUANTITY_ISSUED) QUANTITY_ISSUED
, (WRO.REQUIRED_QUANTITY-NVL(WRO.QUANTITY_ISSUED
, 0) - EAM_MATERIAL_ALLOCQTY_PKG.ALLOCATED_QUANTITY(WRO.WIP_ENTITY_ID
, WRO.OPERATION_SEQ_NUM
, WRO.ORGANIZATION_ID
, WRO.INVENTORY_ITEM_ID)) QUANTITY_OPEN
, WRO.QUANTITY_PER_ASSEMBLY
, WRO.COMMENTS
, WRO.SUPPLY_SUBINVENTORY
, WRO.SUPPLY_LOCATOR_ID
, WRO.MRP_NET_FLAG
, WRO.MPS_REQUIRED_QUANTITY
, WRO.MPS_DATE_REQUIRED
, DECODE(WOP.OPERATION_SEQ_NUM
, TO_NUMBER(NULL)
, 0
, 1) ROUTING_EXISTS_FLAG
, WOP.FIRST_UNIT_START_DATE
, WOP.FIRST_UNIT_COMPLETION_DATE
, WOP.LAST_UNIT_COMPLETION_DATE
, EAM_MATERIAL_ALLOCQTY_PKG.ALLOCATED_QUANTITY(WRO.WIP_ENTITY_ID
, WRO.OPERATION_SEQ_NUM
, WRO.ORGANIZATION_ID
, WRO.INVENTORY_ITEM_ID) QUANTITY_ALLOCATED
, WRO.AUTO_REQUEST_MATERIAL
FROM MTL_SYSTEM_ITEMS_KFV MSIK
, WIP_OPERATIONS WOP
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS ML1
, WIP_REQUIREMENT_OPERATIONS WRO
WHERE MSIK.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSIK.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND NVL(MSIK.STOCK_ENABLED_FLAG
, 'N') = 'Y'
AND WRO.ORGANIZATION_ID = WOP.ORGANIZATION_ID(+)
AND WRO.WIP_ENTITY_ID = WOP.WIP_ENTITY_ID(+)
AND WRO.REPETITIVE_SCHEDULE_ID IS NULL
AND WRO.OPERATION_SEQ_NUM = WOP.OPERATION_SEQ_NUM(+)
AND WRO.ORGANIZATION_ID = WOP.ORGANIZATION_ID (+)
AND WRO.WIP_ENTITY_ID = WOP.WIP_ENTITY_ID (+)
AND BD.DEPARTMENT_ID(+) = WRO.DEPARTMENT_ID
AND ML1.LOOKUP_CODE = WRO.WIP_SUPPLY_TYPE
AND ML1.LOOKUP_TYPE = 'WIP_SUPPLY'
AND MSIK.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSIK.ORGANIZATION_ID = WRO.ORGANIZATION_ID UNION SELECT CHARTOROWID(NULL) ROW_ID
, EDIR.INVENTORY_ITEM_ID
, EDIR.ITEM_DESCRIPTION
, EDIR.DESCRIPTION
, EDIR.UOM_CODE
, NULL INVENTORY_ASSET_FLAG
, TO_NUMBER(NULL) LOCATION_CONTROL_CODE
, TO_NUMBER(NULL) RESTRICT_LOCATORS_CODE
, TO_NUMBER(NULL) RESTRICT_SUBINVENTORIES_CODE
, EDIR.ORGANIZATION_ID
, EDIR.WIP_ENTITY_ID
, EDIR.TASK_NUMBER
, TO_DATE(NULL) LAST_UPDATE_DATE
, TO_NUMBER(NULL) LAST_UPDATED_BY
, TO_DATE(NULL) CREATION_DATE
, TO_NUMBER(NULL) CREATED_BY
, TO_NUMBER(NULL) LAST_UPDATE_LOGIN
, TO_NUMBER(NULL) REQUEST_ID
, TO_NUMBER(NULL) PROGRAM_APPLICATION_ID
, TO_NUMBER(NULL) PROGRAM_ID
, TO_DATE(NULL) PROGRAM_UPDATE_DATE
, TO_NUMBER(NULL) DEPARTMENT_ID
, NULL DEPARTMENT_CODE
, TO_NUMBER(NULL) WIP_SUPPLY_TYPE
, NULL WIP_SUPPLY_MEANING
, TO_DATE(NULL) DATE_REQUIRED
, EDIR.REQUIRED_QUANTITY REQUIRED_QUANTITY
, EDIR.QUANTITY_RECEIVED QUANTITY_ISSUED
, NVL(NVL(EDIR.REQUIRED_QUANTITY
, 0) - GREATEST(NVL(EDIR.PO_QUANTITY_ORDERED
, 0)
, NVL(EDIR.RQL_QUANTITY_ORDERED
, 0))
, 0) QUANTITY_OPEN
, TO_NUMBER(NULL) QUANTITY_PER_ASSEMBLY
, NULL COMMENTS
, NULL SUPPLY_SUBINVENTORY
, TO_NUMBER(NULL) SUPPLY_LOCATOR_ID
, TO_NUMBER(NULL) MRP_NET_FLAG
, TO_NUMBER(NULL) MPS_REQUIRED_QUANTITY
, TO_DATE(NULL) MPS_DATE_REQUIRED
, TO_NUMBER(NULL) ROUTING_EXISTS_FLAG
, TO_DATE(NULL) FIRST_UNIT_START_DATE
, TO_DATE(NULL) FIRST_UNIT_COMPLETION_DATE
, TO_DATE(NULL) LAST_UNIT_COMPLETION_DATE
, TO_NUMBER(NULL) QUANTITY_ALLOCATED
, EDIR.AUTO_REQUEST_MATERIAL AUTO_REQUEST_MATERIAL
FROM EAM_DIRECT_ITEM_RECS_V EDIR

Columns