View: MRP_I2_WIP_V

View Text

SELECT ENT.WIP_ENTITY_NAME
, WO.ORGANIZATION_ID
, WO.WIP_ENTITY_ID
, FIRSTOP.OPERATION_SEQ_NUM
, LU.MEANING
, NULL
, PMV.PART_NUMBER
, MRP_I2_ERP.GET_START_DATE(WO.ORGANIZATION_ID)
, GREATEST(MRP_I2_ERP.CALCULATE_NET_ISSUED ( WO.WIP_ENTITY_ID
, SUM(WO.QUANTITY_COMPLETED)
, SUM(JR.QUANTITY_ISSUED)
, SUM(JR.QUANTITY_PER_ASSEMBLY)
, WO.ORGANIZATION_ID)
, 0)
, 0
, 'N'
, SUBSTR(MRP_I2_ERP.GENERATE_ROUTING( PM.PART_NUMBER
, WO.ALTERNATE_BOM_DESIGNATOR
, WO.ALTERNATE_ROUTING_DESIGNATOR
, DECODE(TO_NUMBER ( DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER ( 'MRP_I2_P_JC_ATTR_NUM'
, WO.ORGANIZATION_ID ))
, 1
, WO.ATTRIBUTE1
, 2
, WO.ATTRIBUTE2
, 3
, WO.ATTRIBUTE3
, 4
, WO.ATTRIBUTE4
, 5
, WO.ATTRIBUTE5
, 6
, WO.ATTRIBUTE6
, 7
, WO.ATTRIBUTE7
, 8
, WO.ATTRIBUTE8
, 9
, WO.ATTRIBUTE9
, 10
, WO.ATTRIBUTE10
, 11
, WO.ATTRIBUTE11
, 12
, WO.ATTRIBUTE12
, 13
, WO.ATTRIBUTE13
, 14
, WO.ATTRIBUTE14
, 15
, WO.ATTRIBUTE15 ))
, 1
, DECODE(MRP_I2_ERP.GET_PARAMETER( 'CONSIDER_JOB_BOM_RTG'
, WO.ORGANIZATION_ID)
, 'Y'
, ENT.WIP_ENTITY_NAME
, NULL)
, NULL )
, WO.ORGANIZATION_ID)
, 1
, 240)
, DECODE(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_ENABLE_ABP'
, WO.ORGANIZATION_ID)
, 'Y'
, DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_BOM_AC_ATTR_NUM'
, WO.ORGANIZATION_ID))
, 1
, JR.ATTRIBUTE1
, 2
, JR.ATTRIBUTE2
, 3
, JR.ATTRIBUTE3
, 4
, JR.ATTRIBUTE4
, 5
, JR.ATTRIBUTE5
, 6
, JR.ATTRIBUTE6
, 7
, JR.ATTRIBUTE7
, 8
, JR.ATTRIBUTE8
, 9
, JR.ATTRIBUTE9
, 10
, JR.ATTRIBUTE10
, 11
, JR.ATTRIBUTE11
, 12
, JR.ATTRIBUTE12
, 13
, JR.ATTRIBUTE13
, 14
, JR.ATTRIBUTE14
, 15
, JR.ATTRIBUTE15)
, 'N'
, NULL)
FROM MFG_LOOKUPS LU
, MRP_RHX_PLANNED_JOBS_INTRAOP_V FIRSTOP
, MRP_I2_PART_MASTER_V PM
, MRP_I2_PART_MASTER_V PMV
, WIP_ENTITIES ENT
, WIP_DISCRETE_JOBS WO
, WIP_REQUIREMENT_OPERATIONS JR
WHERE LU.LOOKUP_TYPE='WIP_INTRAOPERATION_STEP'
AND LU.LOOKUP_CODE=1
AND PM.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND PM.INVENTORY_ITEM_ID = WO.PRIMARY_ITEM_ID
AND WO.FIRM_PLANNED_FLAG IN (1
, 2)
AND WO.STATUS_TYPE = 3
AND WO.START_QUANTITY - NVL(WO.QUANTITY_COMPLETED
, 0) - NVL(WO.QUANTITY_SCRAPPED
, 0) > 0
AND FIRSTOP.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND FIRSTOP.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND FIRSTOP.INTRAOPERATION_STEP = 1
AND FIRSTOP.PREVIOUS_OPERATION_SEQ_NUM IS NULL
AND ENT.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND JR.REQUIRED_QUANTITY > 0
AND JR.WIP_SUPPLY_TYPE NOT IN (5
, 6)
AND JR.MRP_NET_FLAG = 1
AND JR.QUANTITY_ISSUED <= JR.REQUIRED_QUANTITY
AND JR.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND JR.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND PMV.ORGANIZATION_ID = JR.ORGANIZATION_ID
AND PMV.INVENTORY_ITEM_ID = JR.INVENTORY_ITEM_ID GROUP BY ENT.WIP_ENTITY_NAME
, WO.ORGANIZATION_ID
, WO.WIP_ENTITY_ID
, FIRSTOP.OPERATION_SEQ_NUM
, PMV.PART_NUMBER
, SUBSTR( MRP_I2_ERP.GENERATE_ROUTING( PM.PART_NUMBER
, WO.ALTERNATE_BOM_DESIGNATOR
, WO.ALTERNATE_ROUTING_DESIGNATOR
, DECODE ( TO_NUMBER ( DECODE ( TO_NUMBER ( MRP_I2_ERP.GET_PARAMETER ( 'MRP_I2_P_JC_ATTR_NUM'
, WO.ORGANIZATION_ID ) )
, 1
, WO.ATTRIBUTE1
, 2
, WO.ATTRIBUTE2
, 3
, WO.ATTRIBUTE3
, 4
, WO.ATTRIBUTE4
, 5
, WO.ATTRIBUTE5
, 6
, WO.ATTRIBUTE6
, 7
, WO.ATTRIBUTE7
, 8
, WO.ATTRIBUTE8
, 9
, WO.ATTRIBUTE9
, 10
, WO.ATTRIBUTE10
, 11
, WO.ATTRIBUTE11
, 12
, WO.ATTRIBUTE12
, 13
, WO.ATTRIBUTE13
, 14
, WO.ATTRIBUTE14
, 15
, WO.ATTRIBUTE15 ) )
, 1
, DECODE(MRP_I2_ERP.GET_PARAMETER ('CONSIDER_JOB_BOM_RTG'
, WO.ORGANIZATION_ID)
, 'Y'
, ENT.WIP_ENTITY_NAME
, NULL )
, NULL )
, WO.ORGANIZATION_ID)
, 1
, 240)
, LU.MEANING
, DECODE(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_ENABLE_ABP'
, WO.ORGANIZATION_ID)
, 'Y'
, DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_BOM_AC_ATTR_NUM'
, WO.ORGANIZATION_ID))
, 1
, JR.ATTRIBUTE1
, 2
, JR.ATTRIBUTE2
, 3
, JR.ATTRIBUTE3
, 4
, JR.ATTRIBUTE4
, 5
, JR.ATTRIBUTE5
, 6
, JR.ATTRIBUTE6
, 7
, JR.ATTRIBUTE7
, 8
, JR.ATTRIBUTE8
, 9
, JR.ATTRIBUTE9
, 10
, JR.ATTRIBUTE10
, 11
, JR.ATTRIBUTE11
, 12
, JR.ATTRIBUTE12
, 13
, JR.ATTRIBUTE13
, 14
, JR.ATTRIBUTE14
, 15
, JR.ATTRIBUTE15)
, 'N'
, NULL) UNION ALL SELECT ENT.WIP_ENTITY_NAME
, WO.ORGANIZATION_ID
, WO.WIP_ENTITY_ID
, WIP.OPERATION_SEQ_NUM
, LU.MEANING
, DECODE(WIP.FINISHED_QUANTITY
, 0
, NULL
, PMV.PART_NUMBER)
, DECODE(WIP.UNFINISHED_QUANTITY
, 0
, NULL
, PMV.PART_NUMBER)
, DECODE( WIP.INTRAOPERATION_STEP
, 1
, DECODE( NVL(WIP.UNFINISHED_QUANTITY
, 0)
, 0
, TO_DATE(NULL)
, MRP_I2_ERP.GET_START_DATE(WO.ORGANIZATION_ID))
, 2
, GREATEST(WIP.START_DATE
, TO_DATE('01-01-1970'
, 'DD-MM-YYYY'))
, 3
, DECODE( NVL(WIP.FINISHED_QUANTITY
, 0)
, 0
, TO_DATE(NULL)
, MRP_I2_ERP.GET_START_DATE(WO.ORGANIZATION_ID)))
, WIP.UNFINISHED_QUANTITY
, WIP.FINISHED_QUANTITY
, DECODE(WIP.COMPLETE_FLAG
, 1
, 'Y'
, 'N')
, SUBSTR(MRP_I2_ERP.GENERATE_ROUTING( PMV.PART_NUMBER
, WO.ALTERNATE_BOM_DESIGNATOR
, WO.ALTERNATE_ROUTING_DESIGNATOR
, DECODE( TO_NUMBER ( DECODE( TO_NUMBER ( MRP_I2_ERP.GET_PARAMETER ( 'MRP_I2_P_JC_ATTR_NUM'
, WO.ORGANIZATION_ID ) )
, 1
, WO.ATTRIBUTE1
, 2
, WO.ATTRIBUTE2
, 3
, WO.ATTRIBUTE3
, 4
, WO.ATTRIBUTE4
, 5
, WO.ATTRIBUTE5
, 6
, WO.ATTRIBUTE6
, 7
, WO.ATTRIBUTE7
, 8
, WO.ATTRIBUTE8
, 9
, WO.ATTRIBUTE9
, 10
, WO.ATTRIBUTE10
, 11
, WO.ATTRIBUTE11
, 12
, WO.ATTRIBUTE12
, 13
, WO.ATTRIBUTE13
, 14
, WO.ATTRIBUTE14
, 15
, WO.ATTRIBUTE15 ) )
, 1
, DECODE(MRP_I2_ERP.GET_PARAMETER ('CONSIDER_JOB_BOM_RTG'
, WO.ORGANIZATION_ID)
, 'Y'
, ENT.WIP_ENTITY_NAME
, NULL)
, NULL )
, WO.ORGANIZATION_ID)
, 1
, 240)
, NULL
FROM MFG_LOOKUPS LU
, MRP_RHX_PLANNED_JOBS_INTRAOP_V WIP
, MRP_I2_PART_MASTER_V PMV
, WIP_ENTITIES ENT
, WIP_DISCRETE_JOBS WO
WHERE LU.LOOKUP_TYPE='WIP_INTRAOPERATION_STEP'
AND LU.LOOKUP_CODE=WIP.INTRAOPERATION_STEP
AND PMV.ORGANIZATION_ID = ENT.ORGANIZATION_ID
AND PMV.INVENTORY_ITEM_ID = WO.PRIMARY_ITEM_ID
AND WO.FIRM_PLANNED_FLAG IN (1
, 2)
AND WO.STATUS_TYPE = 3
AND WO.START_QUANTITY - NVL(WO.QUANTITY_COMPLETED
, 0) - NVL(WO.QUANTITY_SCRAPPED
, 0) > 0
AND ENT.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WIP.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND WIP.WIP_ENTITY_ID = WO.WIP_ENTITY_ID AND( (WIP.INTRAOPERATION_STEP = 1
AND WIP.PREVIOUS_OPERATION_SEQ_NUM IS NOT NULL) OR WIP.INTRAOPERATION_STEP = 2 OR WIP.INTRAOPERATION_STEP = 3)

Columns