View: MRP_I2_WORK_ORDERS_V

View Text

SELECT WJ.WIP_ENTITY_ID
, WJ.WIP_ENTITY_NAME
, PM.PART_NUMBER
, WJ.INVENTORY_ITEM_ID
, WJ.ORGANIZATION_ID
, SUBSTR( MRP_I2_ERP.GENERATE_ROUTING( PM.PART_NUMBER
, WJ.ALTERNATE_BOM_DESIGNATOR
, WJ.ALTERNATE_ROUTING_DESIGNATOR
, DECODE(TO_NUMBER( DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER( 'MRP_I2_P_JC_ATTR_NUM'
, PM.ORGANIZATION_ID))
, 1
, WJ.ATTRIBUTE1
, 2
, WJ.ATTRIBUTE2
, 3
, WJ.ATTRIBUTE3
, 4
, WJ.ATTRIBUTE4
, 5
, WJ.ATTRIBUTE5
, 6
, WJ.ATTRIBUTE6
, 7
, WJ.ATTRIBUTE7
, 8
, WJ.ATTRIBUTE8
, 9
, WJ.ATTRIBUTE9
, 10
, WJ.ATTRIBUTE10
, 11
, WJ.ATTRIBUTE11
, 12
, WJ.ATTRIBUTE12
, 13
, WJ.ATTRIBUTE13
, 14
, WJ.ATTRIBUTE14
, 15
, WJ.ATTRIBUTE15 ))
, 1
, DECODE(MRP_I2_ERP.GET_PARAMETER('CONSIDER_JOB_BOM_RTG'
, PM.ORGANIZATION_ID)
, 'Y'
, WJ.WIP_ENTITY_NAME
, NULL)
, NULL)
, PM.ORGANIZATION_ID)
, 1
, 240)
, GREATEST(WJ.START_DATE
, TO_DATE('01-01-1970'
, 'DD-MM-YYYY'))
, GREATEST(WJ.COMPLETION_DATE
, TO_DATE('01-01-1970'
, 'DD-MM-YYYY'))
, DECODE(WJ.STATUS_TYPE
, 3
, 1
, 2)
, LU.MEANING
, GREATEST(0
, WJ.START_QUANTITY - NVL(WJ.QUANTITY_COMPLETED
, 0) - NVL(WJ.QUANTITY_SCRAPPED
, 0))
, GREATEST(0
, (LEAST(WJ.START_QUANTITY
, WJ.START_QUANTITY - NVL(WO.QUANTITY_IN_QUEUE
, WJ.START_QUANTITY))) - NVL(WJ.QUANTITY_COMPLETED
, 0) - NVL(WJ.QUANTITY_SCRAPPED
, 0))
, NVL(WJ.QUANTITY_COMPLETED
, 0)
, NVL(WJ.QUANTITY_SCRAPPED
, 0)
, MRP_I2.CALCULATE_PRIORITY('RESERVED_SO'
, WJ.COMPLETION_DATE
, PM.ORGANIZATION_ID)
, DECODE(WJ.FIRM_PLANNED_STATUS_TYPE
, 1
, 1
, DECODE(WJ.STATUS_TYPE
, 1
, 2
, DECODE(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_RELEASED_AS_FIRM'
, PM.ORGANIZATION_ID)
, 'Y'
, 1
, 2)))
, WJ.JOB_TYPE_MEANING
, MRP_I2_ERP.GET_CUMUL_YIELD( WJ.ALTERNATE_ROUTING_DESIGNATOR
, WJ.INVENTORY_ITEM_ID
, DECODE(TO_NUMBER( DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER( 'MRP_I2_P_JC_ATTR_NUM'
, PM.ORGANIZATION_ID))
, 1
, WJ.ATTRIBUTE1
, 2
, WJ.ATTRIBUTE2
, 3
, WJ.ATTRIBUTE3
, 4
, WJ.ATTRIBUTE4
, 5
, WJ.ATTRIBUTE5
, 6
, WJ.ATTRIBUTE6
, 7
, WJ.ATTRIBUTE7
, 8
, WJ.ATTRIBUTE8
, 9
, WJ.ATTRIBUTE9
, 10
, WJ.ATTRIBUTE10
, 11
, WJ.ATTRIBUTE11
, 12
, WJ.ATTRIBUTE12
, 13
, WJ.ATTRIBUTE13
, 14
, WJ.ATTRIBUTE14
, 15
, WJ.ATTRIBUTE15 ))
, 1
, DECODE(MRP_I2_ERP.GET_PARAMETER('CONSIDER_JOB_BOM_RTG'
, PM.ORGANIZATION_ID)
, 'Y'
, WJ.WIP_ENTITY_ID
, NULL)
, NULL )
, PM.ORGANIZATION_ID )
, MRP_I2_ERP.GET_CUMUL_YIELD( WJ.ALTERNATE_ROUTING_DESIGNATOR
, WJ.INVENTORY_ITEM_ID
, DECODE(TO_NUMBER( DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER( 'MRP_I2_P_JC_ATTR_NUM'
, PM.ORGANIZATION_ID))
, 1
, WJ.ATTRIBUTE1
, 2
, WJ.ATTRIBUTE2
, 3
, WJ.ATTRIBUTE3
, 4
, WJ.ATTRIBUTE4
, 5
, WJ.ATTRIBUTE5
, 6
, WJ.ATTRIBUTE6
, 7
, WJ.ATTRIBUTE7
, 8
, WJ.ATTRIBUTE8
, 9
, WJ.ATTRIBUTE9
, 10
, WJ.ATTRIBUTE10
, 11
, WJ.ATTRIBUTE11
, 12
, WJ.ATTRIBUTE12
, 13
, WJ.ATTRIBUTE13
, 14
, WJ.ATTRIBUTE14
, 15
, WJ.ATTRIBUTE15 ))
, 1
, DECODE(MRP_I2_ERP.GET_PARAMETER('CONSIDER_JOB_BOM_RTG'
, PM.ORGANIZATION_ID)
, 'Y'
, WJ.WIP_ENTITY_ID
, NULL)
, NULL )
, PM.ORGANIZATION_ID )*(GREATEST(0
, WJ.START_QUANTITY - NVL(WJ.QUANTITY_COMPLETED
, 0) - NVL(WJ.QUANTITY_SCRAPPED
, 0)))
, WJ.DISCRETE_OR_FLOW
FROM MFG_LOOKUPS LU
, WIP_OPERATIONS WO
, MRP_I2_PART_MASTER_V PM
, MRP_RHX_PLANNED_JOBS_V WJ
WHERE LU.LOOKUP_TYPE='SYS_YES_NO'
AND LU.LOOKUP_CODE=DECODE(WJ.DISCRETE_OR_FLOW
, 1
, DECODE(WJ.STATUS_TYPE
, 3
, 1
, 2)
, 2
, 1)
AND WJ.ORGANIZATION_ID = PM.ORGANIZATION_ID
AND WJ.INVENTORY_ITEM_ID = PM.INVENTORY_ITEM_ID
AND WJ.START_QUANTITY - NVL(WJ.QUANTITY_COMPLETED
, 0) - NVL(WJ.QUANTITY_SCRAPPED
, 0) > 0
AND WJ.STATUS_TYPE IN (1
, 2
, 3
, 6)
AND WJ.ORGANIZATION_ID = WO.ORGANIZATION_ID (+)
AND WJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID (+)
AND WO.PREVIOUS_OPERATION_SEQ_NUM IS NULL
AND WJ.INVENTORY_ITEM_ID IS NOT NULL
AND DECODE(WJ.DISCRETE_OR_FLOW
, 1
, TRUNC(MRP_I2_ERP.GET_START_DATE(WJ.ORGANIZATION_ID))
, 2
, TRUNC(WJ.COMPLETION_DATE) ) >= TRUNC(MRP_I2_ERP.GET_START_DATE(WJ.ORGANIZATION_ID))

Columns