View: MRP_I2_ROUTING_V

View Text

SELECT BOM.ASSEMBLY_ITEM_ID
, SUBSTR( MRP_I2_ERP.GENERATE_ROUTING( PM.PART_NUMBER
, BOM.ALTERNATE_BOM_DESIGNATOR
, BRV.ALT_ROUTING_DESIGNATOR
, NULL
, PM.ORGANIZATION_ID)
, 1
, 240)
, BRV.OPERATION
, BRV.DESCRIPTION
, SUBSTR( BR.DEPARTMENT_CODE || MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_DELIMITER'
, BR.ORGANIZATION_ID) || BR.RESOURCE_CODE
, 1
, 240)
, DECODE(BRV.RESOURCE_RATE
, 0
, 0.000001
, BRV.RESOURCE_RATE)
, BRV.RESOURCE_SETUP
, TO_NUMBER(DECODE(TO_NUMBER( MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_RES_CDT_ATTR_NUM'
, PM.ORGANIZATION_ID))
, 1
, BRV.OP_RES_ATTRIBUTE1
, 2
, BRV.OP_RES_ATTRIBUTE2
, 3
, BRV.OP_RES_ATTRIBUTE3
, 4
, BRV.OP_RES_ATTRIBUTE4
, 5
, BRV.OP_RES_ATTRIBUTE5
, 6
, BRV.OP_RES_ATTRIBUTE6
, 7
, BRV.OP_RES_ATTRIBUTE7
, 8
, BRV.OP_RES_ATTRIBUTE8
, 9
, BRV.OP_RES_ATTRIBUTE9
, 10
, BRV.OP_RES_ATTRIBUTE10
, 11
, BRV.OP_RES_ATTRIBUTE11
, 12
, BRV.OP_RES_ATTRIBUTE12
, 13
, BRV.OP_RES_ATTRIBUTE13
, 14
, BRV.OP_RES_ATTRIBUTE14
, 15
, BRV.OP_RES_ATTRIBUTE15
, '0'))
, BRV.UNIT_OF_MEASURE
, BRV.OPERATION_YIELD
, BRV.DEPARTMENT_ID
, BRV.RESOURCE_ID
, BRV.MINIMUM_TRANSFER_QUANTITY
, PM.ORGANIZATION_ID
, BOM.BILL_SEQUENCE_ID
, BOM.COMMON_BILL_SEQUENCE_ID
, BOM.ALTERNATE_BOM_DESIGNATOR
, BRV.ALT_ROUTING_DESIGNATOR
, NVL(DECODE( TO_NUMBER(MRP_I2_ERP.GET_PARAMETER( 'MRP_I2_P_RES_TYP_ATTR_NUM'
, PM.ORGANIZATION_ID))
, 1
, BRV.OP_RES_ATTRIBUTE1
, 2
, BRV.OP_RES_ATTRIBUTE2
, 3
, BRV.OP_RES_ATTRIBUTE3
, 4
, BRV.OP_RES_ATTRIBUTE4
, 5
, BRV.OP_RES_ATTRIBUTE5
, 6
, BRV.OP_RES_ATTRIBUTE6
, 7
, BRV.OP_RES_ATTRIBUTE7
, 8
, BRV.OP_RES_ATTRIBUTE8
, 9
, BRV.OP_RES_ATTRIBUTE9
, 10
, BRV.OP_RES_ATTRIBUTE10
, 11
, BRV.OP_RES_ATTRIBUTE11
, 12
, BRV.OP_RES_ATTRIBUTE12
, 13
, BRV.OP_RES_ATTRIBUTE13
, 14
, BRV.OP_RES_ATTRIBUTE14
, 15
, BRV.OP_RES_ATTRIBUTE15)
, 'MACHINE')
, BRV.OPERATION_SEQUENCE_ID
, BRV.RESOURCE_SEQ_NUM
, BRV.ROUTING_SEQUENCE_ID
, BRV.BASIS_TYPE
, LU.MEANING
, RPAD(NVL(BRV.ALT_ROUTING_DESIGNATOR
, ' ')
, 10) || RPAD(NVL(BOM.ALTERNATE_BOM_DESIGNATOR
, ' ')
, 10) || TO_CHAR(BRV.OPERATION
, '00009')
, BRV.CUMULATIVE_YIELD
FROM MFG_LOOKUPS LU
, MRP_RHX_BOM_DEPT_RESOURCES_V BR
, MRP_I2_PART_MASTER_V PM
, BOM_BILL_OF_MATERIALS BOM
, MRP_RHX_BOM_ROUTING_V BRV
WHERE LU.LOOKUP_TYPE='CST_BASIS'
AND LU.LOOKUP_CODE=BRV.BASIS_TYPE
AND BR.SHARE_CAPACITY_FLAG = 2
AND BR.SHARE_FROM_DEPT_ID IS NULL
AND BRV.RESOURCE_ID = BR.RESOURCE_ID
AND BRV.DEPARTMENT_ID = BR.DEPARTMENT_ID
AND BR.ORGANIZATION_ID = PM.ORGANIZATION_ID
AND BRV.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
AND BRV.ORGANIZATION_ID = BOM.ORGANIZATION_ID
AND BRV.SCHEDULE_FLAG IN (1
, 3
, 4)
AND NVL(BRV.SEQ_DISABLE_DATE
, MRP_I2_ERP.GET_START_DATE(PM.ORGANIZATION_ID) +1) > MRP_I2_ERP.GET_START_DATE(PM.ORGANIZATION_ID)
AND BRV.SEQ_EFFECTIVE_DATE <= MRP_I2_ERP.GET_START_DATE(PM.ORGANIZATION_ID)
AND BOM.ORGANIZATION_ID = PM.ORGANIZATION_ID
AND BOM.ASSEMBLY_ITEM_ID = PM.INVENTORY_ITEM_ID UNION ALL SELECT WJRRV.INVENTORY_ITEM_ID
, SUBSTR( MRP_I2_ERP.GENERATE_ROUTING( WJRRV.PART_NUMBER
, WJRRV.ALT_BOM_DESIGNATOR
, WJRRV.ALT_ROUTING_DESIGNATOR
, WE.WIP_ENTITY_NAME
, WJRRV.ORGANIZATION_ID)
, 1
, 240)
, WJRRV.OPERATION_SEQ_NUM
, WJRRV.DESCRIPTION
, SUBSTR( BR.DEPARTMENT_CODE || MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_DELIMITER'
, BR.ORGANIZATION_ID) || BR.RESOURCE_CODE
, 1
, 240)
, DECODE(WJRRV.BASIS_TYPE
, 1
, WJRRV.USAGE_RATE
, 0.000001)
, WJRRV.RESOURCE_SETUP
, TO_NUMBER(DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_RES_CDT_ATTR_NUM'
, WJRRV.ORGANIZATION_ID))
, 1
, WJRRV.WOR_ATTRIBUTE1
, 2
, WJRRV.WOR_ATTRIBUTE2
, 3
, WJRRV.WOR_ATTRIBUTE3
, 4
, WJRRV.WOR_ATTRIBUTE4
, 5
, WJRRV.WOR_ATTRIBUTE5
, 6
, WJRRV.WOR_ATTRIBUTE6
, 7
, WJRRV.WOR_ATTRIBUTE7
, 8
, WJRRV.WOR_ATTRIBUTE8
, 9
, WJRRV.WOR_ATTRIBUTE9
, 10
, WJRRV.WOR_ATTRIBUTE10
, 11
, WJRRV.WOR_ATTRIBUTE11
, 12
, WJRRV.WOR_ATTRIBUTE12
, 13
, WJRRV.WOR_ATTRIBUTE13
, 14
, WJRRV.WOR_ATTRIBUTE14
, 15
, WJRRV.WOR_ATTRIBUTE15
, '0'))
, BR.UNIT_OF_MEASURE
, NVL(TO_NUMBER( DECODE( TO_NUMBER(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_OP_YIELD_ATTR_NUM'
, WJRRV .ORGANIZATION_ID))
, 1
, WJRRV.WO_ATTRIBUTE1
, 2
, WJRRV.WO_ATTRIBUTE2
, 3
, WJRRV.WO_ATTRIBUTE3
, 4
, WJRRV.WO_ATTRIBUTE4
, 5
, WJRRV.WO_ATTRIBUTE5
, 6
, WJRRV.WO_ATTRIBUTE6
, 7
, WJRRV.WO_ATTRIBUTE7
, 8
, WJRRV.WO_ATTRIBUTE8
, 9
, WJRRV.WO_ATTRIBUTE9
, 10
, WJRRV.WO_ATTRIBUTE10
, 11
, WJRRV.WO_ATTRIBUTE11
, 12
, WJRRV.WO_ATTRIBUTE12
, 13
, WJRRV.WO_ATTRIBUTE13
, 14
, WJRRV.WO_ATTRIBUTE14
, 15
, WJRRV.WO_ATTRIBUTE15))
, 1)
, WJRRV.DEPARTMENT_ID
, WJRRV.RESOURCE_ID
, 0
, WJRRV.ORGANIZATION_ID
, 0
, 0
, WJRRV.ALT_BOM_DESIGNATOR
, WJRRV.ALT_ROUTING_DESIGNATOR
, NVL(DECODE( TO_NUMBER(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_RES_TYP_ATTR_NUM'
, WJRRV. ORGANIZATION_ID))
, 1
, WJRRV.WOR_ATTRIBUTE1
, 2
, WJRRV.WOR_ATTRIBUTE2
, 3
, WJRRV.WOR_ATTRIBUTE3
, 4
, WJRRV.WOR_ATTRIBUTE4
, 5
, WJRRV.WOR_ATTRIBUTE5
, 6
, WJRRV.WOR_ATTRIBUTE6
, 7
, WJRRV.WOR_ATTRIBUTE7
, 8
, WJRRV.WOR_ATTRIBUTE8
, 9
, WJRRV.WOR_ATTRIBUTE9
, 10
, WJRRV.WOR_ATTRIBUTE10
, 11
, WJRRV.WOR_ATTRIBUTE11
, 12
, WJRRV.WOR_ATTRIBUTE12
, 13
, WJRRV.WOR_ATTRIBUTE13
, 14
, WJRRV.WOR_ATTRIBUTE14
, 15
, WJRRV.WOR_ATTRIBUTE15)
, 'MACHINE')
, WJRRV.OPERATION_SEQUENCE_ID
, WJRRV.RESOURCE_SEQ_NUM
, 0
, WJRRV.BASIS_TYPE
, LU.MEANING
, 'ZZZZ' || RPAD(NVL(WJRRV.ALT_ROUTING_DESIGNATOR
, ' ')
, 10) || RPAD(NVL(WJRRV.ALT_BOM_DESIGNATOR
, ' ')
, 10) || TO_CHAR(WJRRV.OPERATION_SEQ_NUM
, '00009')
, 1.0
FROM MFG_LOOKUPS LU
, WIP_ENTITIES WE
, MRP_RHX_BOM_DEPT_RESOURCES_V BR
, MRP_RHX_JOBS_RESOURCES_REQ_V WJRRV
WHERE LU.LOOKUP_TYPE='CST_BASIS'
AND LU.LOOKUP_CODE=WJRRV.BASIS_TYPE
AND BR.SHARE_CAPACITY_FLAG = 2
AND BR.SHARE_FROM_DEPT_ID IS NULL
AND WJRRV.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WJRRV.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WJRRV.ORGANIZATION_ID = BR.ORGANIZATION_ID
AND WJRRV.DEPARTMENT_ID = BR.DEPARTMENT_ID
AND WJRRV.RESOURCE_ID = BR.RESOURCE_ID
AND MRP_I2_ERP.GET_PARAMETER('CONSIDER_JOB_BOM_RTG'
, WJRRV.ORGANIZATION_ID ) = 'Y'
AND DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER( 'MRP_I2_P_JC_ATTR_NUM'
, WJRRV.ORGANIZATION_ID))
, 1
, WJRRV.WDJ_ATTRIBUTE1
, 2
, WJRRV.WDJ_ATTRIBUTE2
, 3
, WJRRV.WDJ_ATTRIBUTE3
, 4
, WJRRV.WDJ_ATTRIBUTE4
, 5
, WJRRV.WDJ_ATTRIBUTE5
, 6
, WJRRV.WDJ_ATTRIBUTE6
, 7
, WJRRV.WDJ_ATTRIBUTE7
, 8
, WJRRV.WDJ_ATTRIBUTE8
, 9
, WJRRV.WDJ_ATTRIBUTE9
, 10
, WJRRV.WDJ_ATTRIBUTE10
, 11
, WJRRV.WDJ_ATTRIBUTE11
, 12
, WJRRV.WDJ_ATTRIBUTE12
, 13
, WJRRV.WDJ_ATTRIBUTE13
, 14
, WJRRV.WDJ_ATTRIBUTE14
, 15
, WJRRV.WDJ_ATTRIBUTE15) = 1 UNION ALL SELECT BOM.ASSEMBLY_ITEM_ID
, SUBSTR( MRP_I2_ERP.GENERATE_ROUTING( PM.PART_NUMBER
, BOM.ALTERNATE_BOM_DESIGNATOR
, NULL
, NULL
, BOM.ORGANIZATION_ID)
, 1
, 240)
, -1
, ' '
, 'DUMMY_RES'
, 0.000001
, TO_NUMBER(0)
, 0
, TO_CHAR(NULL)
, 1
, -1
, -1
, TO_NUMBER(NULL)
, BOM.ORGANIZATION_ID
, BOM.BILL_SEQUENCE_ID
, BOM.COMMON_BILL_SEQUENCE_ID
, BOM.ALTERNATE_BOM_DESIGNATOR
, ' '
, 'MACHINE'
, -1
, -1
, -1
, 2
, LU.MEANING
, ' ' || RPAD(NVL(BOM.ALTERNATE_BOM_DESIGNATOR
, ' ')
, 10) || TO_CHAR(-1
, '00009')
, 1.0
FROM MFG_LOOKUPS LU
, MRP_I2_PART_MASTER_V PM
, BOM_BILL_OF_MATERIALS BOM
WHERE LU.LOOKUP_TYPE = 'CST_BASIS'
AND LU.LOOKUP_CODE = 2
AND BOM.ASSEMBLY_ITEM_ID = PM.INVENTORY_ITEM_ID
AND BOM.ORGANIZATION_ID = PM.ORGANIZATION_ID
AND NOT EXISTS (SELECT NULL
FROM BOM_OPERATIONAL_ROUTINGS RTG
WHERE RTG.ORGANIZATION_ID = BOM.ORGANIZATION_ID
AND RTG.ASSEMBLY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID)

Columns