View: WIP_REQUIREMENT_OPERATIONS_V

Product:	WIP - Work in Process
Description:	Foreign-key data for WIP_REQUIREMENT_OPERATIONS
Implementation/DBA Data:	APPS.WIP_REQUIREMENT_OPERATIONS_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.REPETITIVE_SCHEDULE_ID
, WRS.LINE_ID
, 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
, NVL(WRO.BASIS_TYPE
, 1) BASIS_TYPE
, ML2.MEANING BASIS_TYPE_MEANING
, WRO.COMPONENT_YIELD_FACTOR
, DECODE(WRO.QUANTITY_ISSUED
, 0
, NULL
, WRO.QUANTITY_ISSUED) QUANTITY_ISSUED
, DECODE((WRO.REQUIRED_QUANTITY-WRO.QUANTITY_ISSUED)
, 0
, NULL
, DECODE(SIGN(WRO.REQUIRED_QUANTITY)
, -1*SIGN(WRO.QUANTITY_ISSUED)
, (WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED)
, DECODE(SIGN(ABS(WRO.REQUIRED_QUANTITY) - ABS(WRO.QUANTITY_ISSUED))
, -1
, NULL
, (WRO.REQUIRED_QUANTITY-WRO.QUANTITY_ISSUED)))) 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
, NULL
, 0
, 1) ROUTING_EXISTS_FLAG
, WOP.FIRST_UNIT_START_DATE
, WOP.FIRST_UNIT_COMPLETION_DATE
, WOP.LAST_UNIT_COMPLETION_DATE
, WRO.ATTRIBUTE_CATEGORY
, WRO.ATTRIBUTE1
, WRO.ATTRIBUTE2
, WRO.ATTRIBUTE3
, WRO.ATTRIBUTE4
, WRO.ATTRIBUTE5
, WRO.ATTRIBUTE6
, WRO.ATTRIBUTE7
, WRO.ATTRIBUTE8
, WRO.ATTRIBUTE9
, WRO.ATTRIBUTE10
, WRO.ATTRIBUTE11
, WRO.ATTRIBUTE12
, WRO.ATTRIBUTE13
, WRO.ATTRIBUTE14
, WRO.ATTRIBUTE15
, DECODE(WRO.QUANTITY_ALLOCATED
, 0
, TO_NUMBER(NULL)
, WRO.QUANTITY_ALLOCATED) QUANTITY_ALLOCATED
, WRO.AUTO_REQUEST_MATERIAL
, WRO.RELEASED_QUANTITY
, WRO.SUGGESTED_VENDOR_NAME
, WRO.VENDOR_ID
, WRO.UNIT_PRICE
FROM MTL_SYSTEM_ITEMS_VL MSIK /*BUG 5175101/5040132 - CHANGED
FROM MSI_KFV TO MSI_VL */
, WIP_OPERATIONS WOP
, WIP_REPETITIVE_SCHEDULES WRS
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, WIP_REQUIREMENT_OPERATIONS WRO
WHERE MSIK.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSIK.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND WRO.ORGANIZATION_ID = WOP.ORGANIZATION_ID(+)
AND WRO.WIP_ENTITY_ID = WOP.WIP_ENTITY_ID(+)
AND WRO.REPETITIVE_SCHEDULE_ID IS NULL
AND NVL(WRO.REPETITIVE_SCHEDULE_ID
, '-1') = NVL(WOP.REPETITIVE_SCHEDULE_ID
, '-1')
AND WRO.OPERATION_SEQ_NUM = WOP.OPERATION_SEQ_NUM(+)
AND WRS.REPETITIVE_SCHEDULE_ID(+) = WRO.REPETITIVE_SCHEDULE_ID
AND WRS.ORGANIZATION_ID(+) = WRO.ORGANIZATION_ID
AND BD.DEPARTMENT_ID(+) = WRO.DEPARTMENT_ID
AND ML1.LOOKUP_CODE = WRO.WIP_SUPPLY_TYPE
AND ML1.LOOKUP_TYPE = 'WIP_SUPPLY'
AND ((WRO.BASIS_TYPE IS NULL
AND ML2.LOOKUP_CODE = 1) OR(WRO.BASIS_TYPE IS NOT NULL
AND ML2.LOOKUP_CODE = WRO.BASIS_TYPE))
AND ML2.LOOKUP_TYPE = 'BOM_BASIS_TYPE'
AND MSIK.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSIK.ORGANIZATION_ID = WRO.ORGANIZATION_ID UNION ALL 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.REPETITIVE_SCHEDULE_ID
, WRS.LINE_ID
, 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
, NVL(WRO.BASIS_TYPE
, 1) BASIS_TYPE
, ML2.MEANING BASIS_TYPE_MEANING
, WRO.COMPONENT_YIELD_FACTOR
, DECODE(WRO.QUANTITY_ISSUED
, 0
, NULL
, WRO.QUANTITY_ISSUED) QUANTITY_ISSUED
, DECODE((WRO.REQUIRED_QUANTITY-WRO.QUANTITY_ISSUED)
, 0
, NULL
, DECODE(SIGN(WRO.REQUIRED_QUANTITY)
, -1*SIGN(WRO.QUANTITY_ISSUED)
, (WRO.REQUIRED_QUANTITY-WRO.QUANTITY_ISSUED)
, DECODE(SIGN(ABS(WRO.REQUIRED_QUANTITY) - ABS(WRO.QUANTITY_ISSUED))
, -1
, NULL
, (WRO.REQUIRED_QUANTITY-WRO.QUANTITY_ISSUED)))) 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
, NULL
, 0
, 1) ROUTING_EXISTS_FLAG
, WOP.FIRST_UNIT_START_DATE
, WOP.FIRST_UNIT_COMPLETION_DATE
, WOP.LAST_UNIT_COMPLETION_DATE
, WRO.ATTRIBUTE_CATEGORY
, WRO.ATTRIBUTE1
, WRO.ATTRIBUTE2
, WRO.ATTRIBUTE3
, WRO.ATTRIBUTE4
, WRO.ATTRIBUTE5
, WRO.ATTRIBUTE6
, WRO.ATTRIBUTE7
, WRO.ATTRIBUTE8
, WRO.ATTRIBUTE9
, WRO.ATTRIBUTE10
, WRO.ATTRIBUTE11
, WRO.ATTRIBUTE12
, WRO.ATTRIBUTE13
, WRO.ATTRIBUTE14
, WRO.ATTRIBUTE15
, DECODE(WRO.QUANTITY_ALLOCATED
, 0
, TO_NUMBER(NULL)
, WRO.QUANTITY_ALLOCATED) QUANTITY_ALLOCATED
, WRO.AUTO_REQUEST_MATERIAL
, WRO.RELEASED_QUANTITY
, WRO.SUGGESTED_VENDOR_NAME
, WRO.VENDOR_ID
, WRO.UNIT_PRICE
FROM MTL_SYSTEM_ITEMS_VL MSIK /*BUG 5175101/5040132 - CHANGED
FROM MSI_KFV TO MSI_VL */
, WIP_OPERATIONS WOP
, WIP_REPETITIVE_SCHEDULES WRS
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, WIP_REQUIREMENT_OPERATIONS WRO
WHERE MSIK.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSIK.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND WRO.ORGANIZATION_ID = WOP.ORGANIZATION_ID(+)
AND WRO.WIP_ENTITY_ID = WOP.WIP_ENTITY_ID(+)
AND WRO.REPETITIVE_SCHEDULE_ID IS NOT NULL
AND WRO.REPETITIVE_SCHEDULE_ID = WOP.REPETITIVE_SCHEDULE_ID(+)
AND WRO.OPERATION_SEQ_NUM = WOP.OPERATION_SEQ_NUM(+)
AND WRS.REPETITIVE_SCHEDULE_ID(+) = WRO.REPETITIVE_SCHEDULE_ID
AND WRS.ORGANIZATION_ID(+) = WRO.ORGANIZATION_ID
AND BD.DEPARTMENT_ID(+) = WRO.DEPARTMENT_ID
AND ML1.LOOKUP_CODE = WRO.WIP_SUPPLY_TYPE
AND ML1.LOOKUP_TYPE = 'WIP_SUPPLY'
AND ((WRO.BASIS_TYPE IS NULL
AND ML2.LOOKUP_CODE = 1) OR(WRO.BASIS_TYPE IS NOT NULL
AND ML2.LOOKUP_CODE = WRO.BASIS_TYPE))
AND ML2.LOOKUP_TYPE = 'BOM_BASIS_TYPE'
AND MSIK.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSIK.ORGANIZATION_ID = WRO.ORGANIZATION_ID

Columns