eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: MRP_I2_BOM_V

Product:	MRP - Master Scheduling/MRP
Description:	A Rhythm view supporting information regarding bill of materials
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT BC.ORGANIZATION_ID
, ASSY.PART_NUMBER
, SUBSTR(MRP_I2_ERP.GENERATE_ROUTING ( ASSY.PART_NUMBER
, BC.ALTERNATE_BOM_DESIGNATOR
, RTG.ALTERNATE_ROUTING_DESIGNATOR
, NULL
, ASSY.ORGANIZATION_ID)
, 1
, 240)
, ASSY.INVENTORY_ITEM_ID
, COMP.PART_NUMBER
, COMP.INVENTORY_ITEM_ID
, DECODE(DL.DUMMY
, 1
, (BC.COMPONENT_QUANTITY/BC.COMPONENT_YIELD_FACTOR)
, 1)
, DECODE(DL.DUMMY
, 1
, (DECODE(SIGN(MRP_I2_ERP.GET_START_DATE(ASSY.ORGANIZATION_ID) - BC. EFFECTIVITY_DATE)
, 1
, TO_DATE(NULL)
, BC.EFFECTIVITY_DATE) )
, 2
, ( DECODE(BC.DISABLE_DATE
, NULL
, TO_DATE(NULL)
, BC.DISABLE_DATE) ) )
, BC.DISABLE_DATE
, ASSY.MINIMUM_ORDER_QUANTITY
, ASSY.MAXIMUM_ORDER_QUANTITY
, ASSY.INCREMENTAL_LOT_SIZE
, BC.OPERATION_SEQ_NUM
, BC.ALTERNATE_BOM_DESIGNATOR
, RTG.ALTERNATE_ROUTING_DESIGNATOR
, DECODE(DL.DUMMY
, 1
, (DECODE(SIGN(MRP_I2_ERP.GET_START_DATE(ASSY.ORGANIZATION_ID) - BC. EFFECTIVITY_DATE)
, 1
, NULL
, 'A'))
, 2
, ( DECODE(BC.DISABLE_DATE
, NULL
, NULL
, 'D')) )
, ASSY.PART_NUMBER || RPAD(NVL(RTG.ALTERNATE_ROUTING_DESIGNATOR
, ' ')
, 10) || RPAD(NVL(BC.ALTERNATE_BOM_DESIGNATOR
, ' ')
, 10) || LPAD(TO_CHAR( DECODE(DL.DUMMY
, 1
, DECODE(SIGN(MRP_I2_ERP.GET_START_DATE(ASSY.ORGANIZATION_ID) - BC.EFFECTIVITY_DATE)
, 1
, TO_DATE('01-01-1970'
, 'DD-MM-YYYY')
, BC.EFFECTIVITY_DATE)
, BC.DISABLE_DATE)
, 'J')
, 10) || DECODE(DL.DUMMY
, 1
, DECODE(SIGN(MRP_I2_ERP.GET_START_DATE( ASSY.ORGANIZATION_ID) - BC.EFFECTIVITY_DATE)
, 1
, 'A'
, 'A')
, 'D')
, BC.COMPONENT_YIELD_FACTOR
, BC.ACD_TYPE
, DECODE(BC.CHANGE_NOTICE
, NULL
, NULL
, SUBSTR( BC.CHANGE_NOTICE || MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_DELIMITER'
, BC.ORGANIZATION_ID) || BC.REVISED_ITEM_SEQUENCE_ID
, 1
, 240))
, BC.USE_UP
, NULL
, DL.DUMMY
, DECODE(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_ENABLE_ABP'
, BC.ORGANIZATION_ID)
, 'Y'
, DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_BOM_AC_ATTR_NUM'
, BC.ORGANIZATION_ID))
, 1
, BC.BOM_COMP_ATTRIBUTE1
, 2
, BC.BOM_COMP_ATTRIBUTE2
, 3
, BC.BOM_COMP_ATTRIBUTE3
, 4
, BC.BOM_COMP_ATTRIBUTE4
, 5
, BC.BOM_COMP_ATTRIBUTE5
, 6
, BC.BOM_COMP_ATTRIBUTE6
, 7
, BC.BOM_COMP_ATTRIBUTE7
, 8
, BC.BOM_COMP_ATTRIBUTE8
, 9
, BC.BOM_COMP_ATTRIBUTE9
, 10
, BC.BOM_COMP_ATTRIBUTE10
, 11
, BC.BOM_COMP_ATTRIBUTE11
, 12
, BC.BOM_COMP_ATTRIBUTE12
, 13
, BC.BOM_COMP_ATTRIBUTE13
, 14
, BC.BOM_COMP_ATTRIBUTE14
, 15
, BC.BOM_COMP_ATTRIBUTE15)
, 'N'
, NULL)
, DECODE(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_ENABLE_ABP'
, RTG.ORGANIZATION_ID)
, 'Y'
, DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_BOM_AP_ATTR_NUM'
, RTG.ORGANIZATION_ID))
, 1
, RTG.ATTRIBUTE1
, 2
, RTG.ATTRIBUTE2
, 3
, RTG.ATTRIBUTE3
, 4
, RTG.ATTRIBUTE4
, 5
, RTG.ATTRIBUTE5
, 6
, RTG.ATTRIBUTE6
, 7
, RTG.ATTRIBUTE7
, 8
, RTG.ATTRIBUTE8
, 9
, RTG.ATTRIBUTE9
, 10
, RTG.ATTRIBUTE10
, 11
, RTG.ATTRIBUTE11
, 12
, RTG.ATTRIBUTE12
, 13
, RTG.ATTRIBUTE13
, 14
, RTG.ATTRIBUTE14
, 15
, RTG.ATTRIBUTE15)
, 'N'
, NULL)
FROM BOM_OPERATIONAL_ROUTINGS RTG
, MRP_I2_DUAL DL
, MRP_I2_DUMMY_PART_MASTER_V COMP
, MRP_RHX_BOM_COMPONENTS_V BC
, MRP_I2_DUMMY_PART_MASTER_V ASSY
WHERE ASSY.ORGANIZATION_ID = RTG.ORGANIZATION_ID(+)
AND ASSY.INVENTORY_ITEM_ID = RTG.ASSEMBLY_ITEM_ID(+)
AND ASSY.INVENTORY_ITEM_ID = BC.ASSEMBLY_ITEM_ID
AND ASSY.ORGANIZATION_ID = BC.ORGANIZATION_ID
AND COMP.INVENTORY_ITEM_ID = BC.COMPONENT_ITEM_ID
AND COMP.ORGANIZATION_ID = BC.ORGANIZATION_ID
AND NVL(BC.DISABLE_DATE
, MRP_I2_ERP.GET_START_DATE(ASSY.ORGANIZATION_ID) + 1) >= MRP_I2_ERP.GET_START_DATE(ASSY.ORGANIZATION_ID)
AND (BC.DISABLE_DATE >= MRP_I2_ERP.GET_START_DATE(ASSY.ORGANIZATION_ID) OR DL.DUMMY = 1)
AND (NVL(BC.DISABLE_DATE
, BC.EFFECTIVITY_DATE+1) > BC.EFFECTIVITY_DATE OR DL.DUMMY = 2)
AND BC.COMPONENT_QUANTITY >= 0
AND MRP_I2_ERP.GET_PARAMETER ('SCHEDULING_ONLY'
, ASSY.ORGANIZATION_ID) <> 'Y'
AND BC.ASSEMBLY_ITEM_ID <> BC.COMPONENT_ITEM_ID
AND NVL(BC.USE_UP
, 2) = 2
AND (BC.ECN_STATUS_TYPE IS NULL OR BC.ECN_STATUS_TYPE IN (4
, 6
, 7) ) UNION ALL SELECT ASSY.ORGANIZATION_ID
, ASSY.PART_NUMBER
, SUBSTR(MRP_I2_ERP.GENERATE_ROUTING ( ASSY.PART_NUMBER
, BC.ALTERNATE_BOM_DESIGNATOR
, RTG.ALTERNATE_ROUTING_DESIGNATOR
, NULL
, ASSY.ORGANIZATION_ID)
, 1
, 240)
, ASSY.INVENTORY_ITEM_ID
, DECODE(DL.DUMMY
, 1
, USEUP.PART_NUMBER
, COMP.PART_NUMBER)
, DECODE(DL.DUMMY
, 1
, USEUP.INVENTORY_ITEM_ID
, COMP.INVENTORY_ITEM_ID)
, DECODE(BC.ACD_TYPE
, 1
, (BC.COMPONENT_QUANTITY/BC.COMPONENT_YIELD_FACTOR)
, (BIC.COMPONENT_QUANTITY/BIC.COMPONENT_YIELD_FACTOR) )
, TO_DATE(NULL)
, TO_DATE(NULL)
, ASSY.MINIMUM_ORDER_QUANTITY
, ASSY.MAXIMUM_ORDER_QUANTITY
, ASSY.INCREMENTAL_LOT_SIZE
, BC.OPERATION_SEQ_NUM
, BC.ALTERNATE_BOM_DESIGNATOR
, RTG.ALTERNATE_ROUTING_DESIGNATOR
, DECODE(DL.DUMMY
, 1
, 'U'
, DECODE(BC.ACD_TYPE
, 1
, 'T'
, 2
, 'X'
, 3
, 'Y'))
, ASSY.PART_NUMBER || RPAD(NVL(RTG.ALTERNATE_ROUTING_DESIGNATOR
, ' ')
, 10) || RPAD(NVL(BC.ALTERNATE_BOM_DESIGNATOR
, ' ')
, 10) || LPAD(TO_CHAR( DECODE(DL.DUMMY
, 1
, DECODE(SIGN(MRP_I2_ERP.GET_START_DATE(ASSY.ORGANIZATION_ID) - BC.EFFECTIVITY_DATE)
, 1
, TO_DATE('01-01-1970'
, 'DD-MM-YYYY')
, BC.EFFECTIVITY_DATE)
, BC.DISABLE_DATE)
, 'J')
, 10)|| DECODE(DL.DUMMY
, 1
, DECODE(SIGN(MRP_I2_ERP.GET_START_DATE(ASSY.ORGANIZATION_ID) - BC.EFFECTIVITY_DATE)
, 1
, 'A'
, 'A')
, 'D')
, BC.COMPONENT_YIELD_FACTOR
, BC.ACD_TYPE
, DECODE(BC.CHANGE_NOTICE
, NULL
, NULL
, SUBSTR( BC.CHANGE_NOTICE || MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_DELIMITER'
, BC.ORGANIZATION_ID) || BC.REVISED_ITEM_SEQUENCE_ID
, 1
, 240))
, BC.USE_UP
, BC.USE_UP_ITEM_ID
, DL.DUMMY
, DECODE(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_ENABLE_ABP'
, BC.ORGANIZATION_ID)
, 'Y'
, DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_BOM_AC_ATTR_NUM'
, BC.ORGANIZATION_ID))
, 1
, BC.BOM_COMP_ATTRIBUTE1
, 2
, BC.BOM_COMP_ATTRIBUTE2
, 3
, BC.BOM_COMP_ATTRIBUTE3
, 4
, BC.BOM_COMP_ATTRIBUTE4
, 5
, BC.BOM_COMP_ATTRIBUTE5
, 6
, BC.BOM_COMP_ATTRIBUTE6
, 7
, BC.BOM_COMP_ATTRIBUTE7
, 8
, BC.BOM_COMP_ATTRIBUTE8
, 9
, BC.BOM_COMP_ATTRIBUTE9
, 10
, BC.BOM_COMP_ATTRIBUTE10
, 11
, BC.BOM_COMP_ATTRIBUTE11
, 12
, BC.BOM_COMP_ATTRIBUTE12
, 13
, BC.BOM_COMP_ATTRIBUTE13
, 14
, BC.BOM_COMP_ATTRIBUTE14
, 15
, BC.BOM_COMP_ATTRIBUTE15)
, 'N'
, NULL)
, DECODE(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_ENABLE_ABP'
, RTG.ORGANIZATION_ID)
, 'Y'
, DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_BOM_AP_ATTR_NUM'
, RTG.ORGANIZATION_ID))
, 1
, RTG.ATTRIBUTE1
, 2
, RTG.ATTRIBUTE2
, 3
, RTG.ATTRIBUTE3
, 4
, RTG.ATTRIBUTE4
, 5
, RTG.ATTRIBUTE5
, 6
, RTG.ATTRIBUTE6
, 7
, RTG.ATTRIBUTE7
, 8
, RTG.ATTRIBUTE8
, 9
, RTG.ATTRIBUTE9
, 10
, RTG.ATTRIBUTE10
, 11
, RTG.ATTRIBUTE11
, 12
, RTG.ATTRIBUTE12
, 13
, RTG.ATTRIBUTE13
, 14
, RTG.ATTRIBUTE14
, 15
, RTG.ATTRIBUTE15)
, 'N'
, NULL)
FROM BOM_OPERATIONAL_ROUTINGS RTG
, MRP_I2_DUAL DL
, MRP_I2_DUMMY_PART_MASTER_V COMP
, MRP_I2_DUMMY_PART_MASTER_V USEUP
, MRP_I2_DUMMY_PART_MASTER_V ASSY
, BOM_INVENTORY_COMPONENTS BIC
, MRP_RHX_BOM_COMPONENTS_V BC
WHERE ASSY.ORGANIZATION_ID = RTG.ORGANIZATION_ID(+)
AND ASSY.INVENTORY_ITEM_ID = RTG.ASSEMBLY_ITEM_ID(+)
AND ASSY.INVENTORY_ITEM_ID = BC.ASSEMBLY_ITEM_ID
AND ASSY.ORGANIZATION_ID = BC.ORGANIZATION_ID
AND COMP.INVENTORY_ITEM_ID = BC.COMPONENT_ITEM_ID
AND COMP.ORGANIZATION_ID = BC.ORGANIZATION_ID
AND USEUP.INVENTORY_ITEM_ID = BC.USE_UP_ITEM_ID
AND USEUP.ORGANIZATION_ID = BC.ORGANIZATION_ID
AND (NVL(BC.DISABLE_DATE
, MRP_I2_ERP.GET_START_DATE(ASSY.ORGANIZATION_ID) + 1) > MRP_I2_ERP.GET_START_DATE(ASSY.ORGANIZATION_ID) OR DL.DUMMY=1)
AND BC.COMPONENT_QUANTITY >= 0
AND MRP_I2_ERP.GET_PARAMETER('SCHEDULING_ONLY'
, ASSY.ORGANIZATION_ID ) <> 'Y'
AND BC.USE_UP=1
AND ((DL.DUMMY = 1
AND USEUP.INVENTORY_ITEM_ID = COMP.INVENTORY_ITEM_ID) OR DL.DUMMY = 2
AND BC.ACD_TYPE <> 3)
AND BC.OLD_COMPONENT_SEQUENCE_ID = BIC.COMPONENT_SEQUENCE_ID
AND EXISTS (SELECT CHK.COMPONENT_ITEM_ID
FROM MRP_RHX_BOM_COMPONENTS_V CHK
WHERE BC.USE_UP_ITEM_ID = CHK.COMPONENT_ITEM_ID
AND BC.ORGANIZATION_ID = CHK.ORGANIZATION_ID
AND BC.ASSEMBLY_ITEM_ID = CHK.ASSEMBLY_ITEM_ID
AND BC.CHANGE_NOTICE = CHK.CHANGE_NOTICE
AND BC.REVISED_ITEM_ID = CHK.REVISED_ITEM_ID
AND BC.USE_UP_ITEM_ID = CHK.USE_UP_ITEM_ID )
AND (BC.ECN_STATUS_TYPE IS NULL OR BC.ECN_STATUS_TYPE IN (4
, 6
, 7) ) UNION ALL SELECT ASSY.ORGANIZATION_ID
, ASSY.PART_NUMBER
, SUBSTR( MRP_I2_ERP.GENERATE_ROUTING( ASSY.PART_NUMBER
, WJPRV.ALTERNATE_BOM_DESIGNATOR
, WJPRV.ALTERNATE_ROUTING_DESIGNATOR
, WJPRV.WIP_ENTITY_NAME
, ASSY.ORGANIZATION_ID)
, 1
, 240)
, ASSY.INVENTORY_ITEM_ID
, COMP.PART_NUMBER
, COMP.INVENTORY_ITEM_ID
, WJPRV.QUANTITY_PER_ASSEMBLY
, MRP_I2_ERP.GET_START_DATE(ASSY.ORGANIZATION_ID)
, MRP_I2_ERP.GET_END_DATE(ASSY.ORGANIZATION_ID)
, ASSY.MINIMUM_ORDER_QUANTITY
, ASSY.MAXIMUM_ORDER_QUANTITY
, ASSY.INCREMENTAL_LOT_SIZE
, WJPRV.OPERATION_SEQ_NUM
, WJPRV.ALTERNATE_BOM_DESIGNATOR
, WJPRV.ALTERNATE_ROUTING_DESIGNATOR
, NULL
, 'ZZZZ' || RPAD(WJPRV.ALTERNATE_ROUTING_DESIGNATOR
, ''
, 10) || RPAD(WJPRV.ALTERNATE_BOM_DESIGNATOR
, ''
, 10)
, 1
, 1
, 'DUMMY'
, '2'
, NULL
, 3
, DECODE(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_ENABLE_ABP'
, WJPRV.ORGANIZATION_ID)
, 'Y'
, DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_BOM_AC_ATTR_NUM'
, WJPRV.ORGANIZATION_ID))
, 1
, WJPRV.JOB_REQ_ATTRIBUTE1
, 2
, WJPRV.JOB_REQ_ATTRIBUTE2
, 3
, WJPRV.JOB_REQ_ATTRIBUTE3
, 4
, WJPRV.JOB_REQ_ATTRIBUTE4
, 5
, WJPRV.JOB_REQ_ATTRIBUTE5
, 6
, WJPRV.JOB_REQ_ATTRIBUTE6
, 7
, WJPRV.JOB_REQ_ATTRIBUTE7
, 8
, WJPRV.JOB_REQ_ATTRIBUTE8
, 9
, WJPRV.JOB_REQ_ATTRIBUTE9
, 10
, WJPRV.JOB_REQ_ATTRIBUTE10
, 11
, WJPRV.JOB_REQ_ATTRIBUTE11
, 12
, WJPRV.JOB_REQ_ATTRIBUTE12
, 13
, WJPRV.JOB_REQ_ATTRIBUTE13
, 14
, WJPRV.JOB_REQ_ATTRIBUTE14
, 15
, WJPRV.JOB_REQ_ATTRIBUTE15)
, 'N'
, NULL)
, DECODE(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_ENABLE_ABP'
, RTG.ORGANIZATION_ID)
, 'Y'
, DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER('MRP_I2_P_BOM_AP_ATTR_NUM'
, RTG.ORGANIZATION_ID))
, 1
, RTG.ATTRIBUTE1
, 2
, RTG.ATTRIBUTE2
, 3
, RTG.ATTRIBUTE3
, 4
, RTG.ATTRIBUTE4
, 5
, RTG.ATTRIBUTE5
, 6
, RTG.ATTRIBUTE6
, 7
, RTG.ATTRIBUTE7
, 8
, RTG.ATTRIBUTE8
, 9
, RTG.ATTRIBUTE9
, 10
, RTG.ATTRIBUTE10
, 11
, RTG.ATTRIBUTE11
, 12
, RTG.ATTRIBUTE12
, 13
, RTG.ATTRIBUTE13
, 14
, RTG.ATTRIBUTE14
, 15
, RTG.ATTRIBUTE15)
, 'N'
, NULL)
FROM MRP_I2_DUMMY_PART_MASTER_V COMP
, MRP_RHX_JOBS_PLANNED_REQ_V WJPRV
, MRP_I2_DUMMY_PART_MASTER_V ASSY
, BOM_OPERATIONAL_ROUTINGS RTG
, WIP_DISCRETE_JOBS JOB
WHERE WJPRV.JOB_REFERENCE_ITEM_ID = ASSY.INVENTORY_ITEM_ID
AND WJPRV.INVENTORY_ITEM_ID = COMP.INVENTORY_ITEM_ID
AND ASSY.INVENTORY_ITEM_ID <> COMP.INVENTORY_ITEM_ID
AND WJPRV.ORGANIZATION_ID = ASSY.ORGANIZATION_ID
AND WJPRV.ORGANIZATION_ID = COMP.ORGANIZATION_ID
AND WJPRV.JOB_REFERENCE_ITEM_ID <> WJPRV.INVENTORY_ITEM_ID
AND JOB.WIP_ENTITY_ID = WJPRV.WIP_ENTITY_ID
AND JOB.ORGANIZATION_ID = WJPRV.ORGANIZATION_ID
AND RTG.ROUTING_SEQUENCE_ID = JOB.COMMON_ROUTING_SEQUENCE_ID
AND MRP_I2_ERP.GET_PARAMETER('SCHEDULING_ONLY'
, WJPRV.ORGANIZATION_ID) <> 'Y'
AND MRP_I2_ERP.GET_PARAMETER('CONSIDER_JOB_BOM_RTG'
, WJPRV.ORGANIZATION_ID)= 'Y'
AND DECODE(TO_NUMBER(MRP_I2_ERP.GET_PARAMETER( 'MRP_I2_P_JC_ATTR_NUM'
, WJPRV.ORGANIZATION_ID))
, 1
, WJPRV.ATTRIBUTE1
, 2
, WJPRV.ATTRIBUTE2
, 3
, WJPRV.ATTRIBUTE3
, 4
, WJPRV.ATTRIBUTE4
, 5
, WJPRV.ATTRIBUTE5
, 6
, WJPRV.ATTRIBUTE6
, 7
, WJPRV.ATTRIBUTE7
, 8
, WJPRV.ATTRIBUTE8
, 9
, WJPRV.ATTRIBUTE9
, 10
, WJPRV.ATTRIBUTE10
, 11
, WJPRV.ATTRIBUTE11
, 12
, WJPRV.ATTRIBUTE12
, 13
, WJPRV.ATTRIBUTE13
, 14
, WJPRV.ATTRIBUTE14
, 15
, WJPRV.ATTRIBUTE15) = 1

Columns

Name
ORGANIZATION_ID
ASSEMBLY_PART_NUMBER
ROUTING
ASSEMBLY_ITEM_ID
COMPONENT_PART_NUMBER
COMPONENT_ITEM_ID
COMPONENT_QUANTITY
EFFECTIVITY_DATE
DISABLE_DATE
MINIMUM_ORDER_QUANTITY
MAXIMUM_ORDER_QUANTITY
FIXED_LOT_MULTIPLIER
OPERATION
ALT_BOM_DESIG
ALT_ROUTING_DESIG
ECN_CODE
SORT_STRING
COMPONENT_YIELD_FACTOR
ACD_TYPE
CHANGE_NOTICE
USE_UP
USE_UP_ITEM_ID
DUMMEE
ATTRIBUTE_CONSUMED
ATTRIBUTE_PRODUCED