View: WIP_REQUIREMENT_OPS_INQ_V

Product:	WIP - Work in Process
Description:	Additional data for WIP_REQUIREMENT_OPERATIONS
Implementation/DBA Data:	APPS.WIP_REQUIREMENT_OPS_INQ_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
, WRO.ORGANIZATION_ID
, WRO.WIP_ENTITY_ID
, WE.ENTITY_TYPE
, NULL WIP_ENTITY_NAME
, WE.PRIMARY_ITEM_ID
, DECODE(WE.ENTITY_TYPE
, 2
, WRS.DAILY_PRODUCTION_RATE * WRS.PROCESSING_WORK_DAYS
, WDJ.START_QUANTITY) SCHEDULED_QUANTITY
, WRS.STATUS_TYPE STATUS_TYPE
, ML2.MEANING WIP_JOB_STATUS
, WRS.FIRST_UNIT_START_DATE SCHEDULED_START_DATE
, WRS.LAST_UNIT_COMPLETION_DATE SCHEDULED_COMPLETION_DATE
, WRO.OPERATION_SEQ_NUM
, WRO.REPETITIVE_SCHEDULE_ID
, WRS.LINE_ID
, WRS.DAILY_PRODUCTION_RATE
, WRS.PROCESSING_WORK_DAYS
, WL.LINE_CODE
, 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
, 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.QUANTITY_PER_ASSEMBLY QUANTITY_PER_ASSEMBLY_R
, 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_PER_ASSEMBLY
, 0
, NULL
, NULL
, NULL
, 1/WRO.QUANTITY_PER_ASSEMBLY) INVERSE_USAGE
, NVL(WRO.BASIS_TYPE
, 1) BASIS_TYPE
, ML3.MEANING BASIS_TYPE_MEANING
, WRO.COMPONENT_YIELD_FACTOR
FROM MTL_SYSTEM_ITEMS_KFV MSIK
, MFG_LOOKUPS ML2
, WIP_OPERATIONS WOP
, WIP_ENTITIES WE
, WIP_DISCRETE_JOBS WDJ
, WIP_LINES WL
, WIP_REPETITIVE_SCHEDULES WRS
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS ML1
, WIP_REQUIREMENT_OPERATIONS WRO
, MFG_LOOKUPS ML3
WHERE WE.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WE.ENTITY_TYPE = 2
AND WRO.ORGANIZATION_ID = WOP.ORGANIZATION_ID(+)
AND WRO.WIP_ENTITY_ID = WOP.WIP_ENTITY_ID(+)
AND NVL(WRO.REPETITIVE_SCHEDULE_ID
, '-1') = WOP.REPETITIVE_SCHEDULE_ID(+)
AND WRO.OPERATION_SEQ_NUM = WOP.OPERATION_SEQ_NUM(+)
AND WDJ.WIP_ENTITY_ID(+) = WRO.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID(+) = WRO.ORGANIZATION_ID
AND WRS.REPETITIVE_SCHEDULE_ID(+) = WRO.REPETITIVE_SCHEDULE_ID
AND WRS.ORGANIZATION_ID(+) = WRO.ORGANIZATION_ID
AND WL.LINE_ID(+) = WRS.LINE_ID
AND WL.ORGANIZATION_ID(+) = WRS.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.WIP_SUPPLY_TYPE != 6
AND ML2.LOOKUP_CODE = WRS.STATUS_TYPE
AND ML2.LOOKUP_TYPE ='WIP_JOB_STATUS'
AND WRO.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND WRO.ORGANIZATION_ID = MSIK.ORGANIZATION_ID
AND ((WRO.BASIS_TYPE IS NULL
AND ML3.LOOKUP_CODE = 1) OR(WRO.BASIS_TYPE IS NOT NULL
AND ML3.LOOKUP_CODE = WRO.BASIS_TYPE))
AND ML3.LOOKUP_TYPE = 'BOM_BASIS_TYPE' 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
, WRO.ORGANIZATION_ID
, WRO.WIP_ENTITY_ID
, WE.ENTITY_TYPE
, WE.WIP_ENTITY_NAME WIP_ENTITY_NAME
, WE.PRIMARY_ITEM_ID
, WDJ.START_QUANTITY SCHEDULED_QUANTITY
, WDJ.STATUS_TYPE STATUS_TYPE
, ML2.MEANING WIP_JOB_STATUS
, WDJ.SCHEDULED_START_DATE SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE SCHEDULED_COMPLETION_DATE
, WRO.OPERATION_SEQ_NUM
, WRO.REPETITIVE_SCHEDULE_ID
, WRS.LINE_ID
, WRS.DAILY_PRODUCTION_RATE
, WRS.PROCESSING_WORK_DAYS
, WL.LINE_CODE
, 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
, 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
, NULL QUANTITY_PER_ASSEMBLY_R
, 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_PER_ASSEMBLY
, 0
, NULL
, NULL
, NULL
, 1/WRO.QUANTITY_PER_ASSEMBLY) INVERSE_USAGE
, NVL(WRO.BASIS_TYPE
, 1) BASIS_TYPE
, ML3.MEANING BASIS_TYPE_MEANING
, WRO.COMPONENT_YIELD_FACTOR
FROM MTL_SYSTEM_ITEMS_KFV MSIK
, ( SELECT /*+ CARDINALITY (ML2 10) */ *
FROM MFG_LOOKUPS ML2 ) ML2
, WIP_OPERATIONS WOP
, WIP_ENTITIES WE
, WIP_DISCRETE_JOBS WDJ
, WIP_LINES WL
, WIP_REPETITIVE_SCHEDULES WRS
, BOM_DEPARTMENTS BD
, ( SELECT /*+ CARDINALITY (ML1 10) */ *
FROM MFG_LOOKUPS ML1 ) ML1
, WIP_REQUIREMENT_OPERATIONS WRO
, ( SELECT /*+ CARDINALITY (ML3 10) */ *
FROM MFG_LOOKUPS ML3 ) ML3
WHERE WE.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND WE.ENTITY_TYPE NOT IN (2
, 6
, 7)
AND WRO.ORGANIZATION_ID = WOP.ORGANIZATION_ID(+)
AND WRO.WIP_ENTITY_ID = WOP.WIP_ENTITY_ID(+)
AND NVL(WRO.REPETITIVE_SCHEDULE_ID
, '-1') = WOP.REPETITIVE_SCHEDULE_ID(+)
AND WRO.OPERATION_SEQ_NUM = WOP.OPERATION_SEQ_NUM(+)
AND WDJ.WIP_ENTITY_ID(+) = WRO.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID(+) = WRO.ORGANIZATION_ID
AND WRS.REPETITIVE_SCHEDULE_ID(+) = WRO.REPETITIVE_SCHEDULE_ID
AND WRS.ORGANIZATION_ID(+) = WRO.ORGANIZATION_ID
AND WL.LINE_ID(+) = WRS.LINE_ID
AND WL.ORGANIZATION_ID(+) = WRS.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.WIP_SUPPLY_TYPE != 6
AND ML2.LOOKUP_CODE = WDJ.STATUS_TYPE
AND ML2.LOOKUP_TYPE ='WIP_JOB_STATUS'
AND WRO.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND WRO.ORGANIZATION_ID = MSIK.ORGANIZATION_ID
AND ((WRO.BASIS_TYPE IS NULL
AND ML3.LOOKUP_CODE = 1) OR(WRO.BASIS_TYPE IS NOT NULL
AND ML3.LOOKUP_CODE = WRO.BASIS_TYPE))
AND ML3.LOOKUP_TYPE = 'BOM_BASIS_TYPE'

Columns