VIEW: APPS.WIP_REQUIREMENT_OPS_INQ_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'