View: MRP_I2_OP_RESOURCE_V

View Text

SELECT SUBSTR( MRP_I2_ERP.GENERATE_ROUTING( PM.PART_NUMBER
, BOM.ALTERNATE_BOM_DESIGNATOR
, OBR.ALT_ROUTING_DESIGNATOR
, NULL
, BOM.ORGANIZATION_ID)
, 1
, 240)
, PM.ORGANIZATION_ID
, OBR.OPERATION
, DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER( 'MRP_I2_P_RES_TYP_ATTR_NUM'
, BOM.ORGANIZATION_ID))
, 1
, OBR.OP_RES_ATTRIBUTE1
, 2
, OBR.OP_RES_ATTRIBUTE2
, 3
, OBR.OP_RES_ATTRIBUTE3
, 4
, OBR.OP_RES_ATTRIBUTE4
, 5
, OBR.OP_RES_ATTRIBUTE5
, 6
, OBR.OP_RES_ATTRIBUTE6
, 7
, OBR.OP_RES_ATTRIBUTE7
, 8
, OBR.OP_RES_ATTRIBUTE8
, 9
, OBR.OP_RES_ATTRIBUTE9
, 10
, OBR.OP_RES_ATTRIBUTE10
, 11
, OBR.OP_RES_ATTRIBUTE11
, 12
, OBR.OP_RES_ATTRIBUTE12
, 13
, OBR.OP_RES_ATTRIBUTE13
, 14
, OBR.OP_RES_ATTRIBUTE14
, 15
, OBR.OP_RES_ATTRIBUTE15)
, SUBSTR( BR.DEPARTMENT_CODE || MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_DELIMITER'
, BR.ORGANIZATION_ID) || BR.RESOURCE_CODE
, 1
, 240)
, MBR.RESOURCE_SEQ_NUM
, OBR.RESOURCE_RATE
, DECODE(OBR.BASIS_TYPE
, 1
, DECODE(DECODE(MBR.BASIS_TYPE
, 1
, MBR.USAGE_RATE_OR_AMOUNT
, 0)
, 0
, 1
, NULL
, 1
, OBR.RESOURCE_RATE/ DECODE(MBR.BASIS_TYPE
, 1
, MBR.USAGE_RATE_OR_AMOUNT
, 0))
, DECODE(DECODE(MBR.BASIS_TYPE
, 2
, MBR.USAGE_RATE_OR_AMOUNT
, 0)
, 0
, 1
, NULL
, 1
, OBR.RESOURCE_SETUP/ DECODE(MBR.BASIS_TYPE
, 2
, MBR.USAGE_RATE_OR_AMOUNT
, 0)))
, OBR.INVENTORY_ITEM_ID
, OBR.ALT_ROUTING_DESIGNATOR
, BOM.ALTERNATE_BOM_DESIGNATOR
, RPAD(NVL(OBR.ALT_ROUTING_DESIGNATOR
, ' ')
, 10) || RPAD(NVL(BOM.ALTERNATE_BOM_DESIGNATOR
, ' ')
, 10) || TO_CHAR(OBR.OPERATION
, '00009') || LPAD(OBR.RESOURCE_SEQ_NUM
, 5
, '0')
, OBR.ASSIGNED_UNITS
FROM BOM_BILL_OF_MATERIALS BOM
, BOM_OPERATION_RESOURCES MBR
, MRP_RHX_BOM_DEPT_RESOURCES_V BR
, MRP_RHX_BOM_ROUTING_V OBR
, MRP_I2_PART_MASTER_V PM
WHERE DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_RES_TYP_ATTR_NUM'
, BOM.ORGANIZATION_ID))
, 1
, OBR.OP_RES_ATTRIBUTE1
, 2
, OBR.OP_RES_ATTRIBUTE2
, 3
, OBR.OP_RES_ATTRIBUTE3
, 4
, OBR.OP_RES_ATTRIBUTE4
, 5
, OBR.OP_RES_ATTRIBUTE5
, 6
, OBR.OP_RES_ATTRIBUTE6
, 7
, OBR.OP_RES_ATTRIBUTE7
, 8
, OBR.OP_RES_ATTRIBUTE8
, 9
, OBR.OP_RES_ATTRIBUTE9
, 10
, OBR.OP_RES_ATTRIBUTE10
, 11
, OBR.OP_RES_ATTRIBUTE11
, 12
, OBR.OP_RES_ATTRIBUTE12
, 13
, OBR.OP_RES_ATTRIBUTE13
, 14
, OBR.OP_RES_ATTRIBUTE14
, 15
, OBR.OP_RES_ATTRIBUTE15) IN ('OPERATORS'
, 'AUX1'
, 'AUX2'
, 'ALTERNATE')
AND OBR.OPERATION_SEQUENCE_ID = MBR.OPERATION_SEQUENCE_ID
AND OBR.RESOURCE_SEQ_NUM <> MBR.RESOURCE_SEQ_NUM
AND OBR.BASIS_TYPE = MBR.BASIS_TYPE
AND OBR.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
AND OBR.ORGANIZATION_ID = BOM.ORGANIZATION_ID
AND OBR.RESOURCE_ID = BR.RESOURCE_ID
AND OBR.DEPARTMENT_ID = BR.DEPARTMENT_ID
AND BR.SHARE_CAPACITY_FLAG = 2
AND BR.SHARE_FROM_DEPT_ID IS NULL
AND BR.ORGANIZATION_ID = PM.ORGANIZATION_ID
AND OBR.INVENTORY_ITEM_ID = PM.INVENTORY_ITEM_ID
AND OBR.ORGANIZATION_ID = PM.ORGANIZATION_ID
AND OBR.SCHEDULE_FLAG IN (1
, 3
, 4)
AND NVL(OBR.SEQ_DISABLE_DATE
, MRP_I2_ERP.GET_START_DATE(PM.ORGANIZATION_ID)+1) > MRP_I2_ERP.GET_START_DATE(PM.ORGANIZATION_ID)
AND OBR.SEQ_EFFECTIVE_DATE <= MRP_I2_ERP.GET_START_DATE(PM.ORGANIZATION_ID)
AND NVL(DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_RES_TYP_ATTR_NUM'
, BOM.ORGANIZATION_ID))
, 1
, MBR.ATTRIBUTE1
, 2
, MBR.ATTRIBUTE2
, 3
, MBR.ATTRIBUTE3
, 4
, MBR.ATTRIBUTE4
, 5
, MBR.ATTRIBUTE5
, 6
, MBR.ATTRIBUTE6
, 7
, MBR.ATTRIBUTE7
, 8
, MBR.ATTRIBUTE8
, 9
, MBR.ATTRIBUTE9
, 10
, MBR.ATTRIBUTE10
, 11
, MBR.ATTRIBUTE11
, 12
, MBR.ATTRIBUTE12
, 13
, MBR.ATTRIBUTE13
, 14
, MBR.ATTRIBUTE14
, 15
, MBR.ATTRIBUTE15)
, 'MACHINE') = 'MACHINE'
AND MBR.SCHEDULE_FLAG IN (1
, 3
, 4) UNION ALL SELECT SUBSTR( MRP_I2_ERP.GENERATE_ROUTING( WIP_RES.PART_NUMBER
, WIP_RES.ALT_BOM_DESIGNATOR
, WIP_RES.ALT_ROUTING_DESIGNATOR
, WE.WIP_ENTITY_NAME
, WIP_RES.ORGANIZATION_ID)
, 1
, 240)
, WIP_RES.ORGANIZATION_ID
, WIP_RES.OPERATION_SEQ_NUM
, DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_RES_TYP_ATTR_NUM'
, WIP_RES.ORGANIZATION_ID))
, 1
, WIP_RES.WOR_ATTRIBUTE1
, 2
, WIP_RES.WOR_ATTRIBUTE2
, 3
, WIP_RES.WOR_ATTRIBUTE3
, 4
, WIP_RES.WOR_ATTRIBUTE4
, 5
, WIP_RES.WOR_ATTRIBUTE5
, 6
, WIP_RES.WOR_ATTRIBUTE6
, 7
, WIP_RES.WOR_ATTRIBUTE7
, 8
, WIP_RES.WOR_ATTRIBUTE8
, 9
, WIP_RES.WOR_ATTRIBUTE9
, 10
, WIP_RES.WOR_ATTRIBUTE10
, 11
, WIP_RES.WOR_ATTRIBUTE11
, 12
, WIP_RES.WOR_ATTRIBUTE12
, 13
, WIP_RES.WOR_ATTRIBUTE13
, 14
, WIP_RES.WOR_ATTRIBUTE14
, 15
, WIP_RES.WOR_ATTRIBUTE15)
, SUBSTR( BR.DEPARTMENT_CODE || MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_DELIMITER'
, BR.ORGANIZATION_ID) || BR.RESOURCE_CODE
, 1
, 240)
, WIP_RES.RESOURCE_SEQ_NUM
, WIP_RES.USAGE_RATE
, DECODE(WIP_RES.BASIS_TYPE
, 1
, DECODE(DECODE(WOR.BASIS_TYPE
, 1
, WOR.USAGE_RATE_OR_AMOUNT
, 0)
, 0
, 1
, NULL
, 1
, WIP_RES.USAGE_RATE/ DECODE(WOR.BASIS_TYPE
, 1
, WOR.USAGE_RATE_OR_AMOUNT
, 0))
, DECODE(DECODE(WOR.BASIS_TYPE
, 2
, WOR.USAGE_RATE_OR_AMOUNT
, 0)
, 0
, 1
, NULL
, 1
, WIP_RES.RESOURCE_SETUP/ DECODE(WOR.BASIS_TYPE
, 2
, WOR.USAGE_RATE_OR_AMOUNT
, 0)))
, WIP_RES.INVENTORY_ITEM_ID
, WIP_RES.ALT_ROUTING_DESIGNATOR
, WIP_RES.ALT_BOM_DESIGNATOR
, RPAD(NVL(WIP_RES.ALT_ROUTING_DESIGNATOR
, ' ')
, 10) || RPAD(NVL(WIP_RES.ALT_BOM_DESIGNATOR
, ' ')
, 10) || TO_CHAR(WIP_RES.OPERATION_SEQ_NUM
, '00009') || LPAD(WIP_RES.RESOURCE_SEQ_NUM
, 5
, '0')
, WIP_RES.ASSIGNED_UNITS
FROM WIP_ENTITIES WE
, WIP_OPERATION_RESOURCES WOR
, MRP_RHX_BOM_DEPT_RESOURCES_V BR
, MRP_RHX_JOBS_RESOURCES_REQ_V WIP_RES
WHERE BR.SHARE_CAPACITY_FLAG = 2
AND BR.SHARE_FROM_DEPT_ID IS NULL
AND DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_RES_TYP_ATTR_NUM'
, WIP_RES.ORGANIZATION_ID))
, 1
, WIP_RES.WOR_ATTRIBUTE1
, 2
, WIP_RES.WOR_ATTRIBUTE2
, 3
, WIP_RES.WOR_ATTRIBUTE3
, 4
, WIP_RES.WOR_ATTRIBUTE4
, 5
, WIP_RES.WOR_ATTRIBUTE5
, 6
, WIP_RES.WOR_ATTRIBUTE6
, 7
, WIP_RES.WOR_ATTRIBUTE7
, 8
, WIP_RES.WOR_ATTRIBUTE8
, 9
, WIP_RES.WOR_ATTRIBUTE9
, 10
, WIP_RES.WOR_ATTRIBUTE10
, 11
, WIP_RES.WOR_ATTRIBUTE11
, 12
, WIP_RES.WOR_ATTRIBUTE12
, 13
, WIP_RES.WOR_ATTRIBUTE13
, 14
, WIP_RES.WOR_ATTRIBUTE14
, 15
, WIP_RES.WOR_ATTRIBUTE15) IN ('AUX1'
, 'AUX2'
, 'OPERATORS'
, 'ALTERNATE')
AND WIP_RES.ORGANIZATION_ID = WOR.ORGANIZATION_ID
AND WIP_RES.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
AND WIP_RES.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM
AND WIP_RES.RESOURCE_SEQ_NUM <> WOR.RESOURCE_SEQ_NUM
AND WIP_RES.BASIS_TYPE = WOR.BASIS_TYPE
AND WIP_RES.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WIP_RES.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WIP_RES.ORGANIZATION_ID = BR.ORGANIZATION_ID
AND WIP_RES.DEPARTMENT_ID = BR.DEPARTMENT_ID
AND WIP_RES.RESOURCE_ID = BR.RESOURCE_ID
AND MRP_I2_ERP.GET_PARAMETER('CONSIDER_JOB_BOM_RTG'
, WIP_RES.ORGANIZATION_ID) = 'Y'
AND DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER( 'MRP_I2_P_JC_ATTR_NUM'
, WIP_RES.ORGANIZATION_ID))
, 1
, WIP_RES.WDJ_ATTRIBUTE1
, 2
, WIP_RES.WDJ_ATTRIBUTE2
, 3
, WIP_RES.WDJ_ATTRIBUTE3
, 4
, WIP_RES.WDJ_ATTRIBUTE4
, 5
, WIP_RES.WDJ_ATTRIBUTE5
, 6
, WIP_RES.WDJ_ATTRIBUTE6
, 7
, WIP_RES.WDJ_ATTRIBUTE7
, 8
, WIP_RES.WDJ_ATTRIBUTE8
, 9
, WIP_RES.WDJ_ATTRIBUTE9
, 10
, WIP_RES.WDJ_ATTRIBUTE10
, 11
, WIP_RES.WDJ_ATTRIBUTE11
, 12
, WIP_RES.WDJ_ATTRIBUTE12
, 13
, WIP_RES.WDJ_ATTRIBUTE13
, 14
, WIP_RES.WDJ_ATTRIBUTE14
, 15
, WIP_RES.WDJ_ATTRIBUTE15) = 1
AND NVL(DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_RES_TYP_ATTR_NUM '
, WOR.ORGANIZATION_ID))
, 1
, WOR.ATTRIBUTE1
, 2
, WOR.ATTRIBUTE2
, 3
, WOR.ATTRIBUTE3
, 4
, WOR.ATTRIBUTE4
, 5
, WOR.ATTRIBUTE5
, 6
, WOR.ATTRIBUTE6
, 7
, WOR.ATTRIBUTE7
, 8
, WOR.ATTRIBUTE8
, 9
, WOR.ATTRIBUTE9
, 10
, WOR.ATTRIBUTE10
, 11
, WOR.ATTRIBUTE11
, 12
, WOR.ATTRIBUTE12
, 13
, WOR.ATTRIBUTE13
, 14
, WOR.ATTRIBUTE14
, 15
, WOR.ATTRIBUTE15)
, 'MACHINE') = 'MACHINE'
AND WOR.SCHEDULED_FLAG IN (1
, 3
, 4)

Columns