View: MRP_RHX_BOM_ROUTING_V

View Text

SELECT MSI.INVENTORY_ITEM_ID
, MSI.ITEM_NUMBER
, SEQ.OPERATION_SEQ_NUM
, SEQ.OPERATION_DESCRIPTION
, R.RESOURCE_CODE
, R.DEPARTMENT_CODE
, R.SHARE_CAPACITY_FLAG
, DECODE(BR.BASIS_TYPE
, 1
, BR.USAGE_RATE_OR_AMOUNT
, 0)
, DECODE(BR2.BASIS_TYPE
, 2
, BR2.USAGE_RATE_OR_AMOUNT
, 0)
, BR.SCHEDULE_FLAG
, R.UNIT_OF_MEASURE
, BR.ASSIGNED_UNITS
, SEQ.YIELD
, R.DEPARTMENT_ID
, R.RESOURCE_ID
, SEQ.MINIMUM_TRANSFER_QUANTITY
, MSI.ORGANIZATION_ID
, RTG.ALTERNATE_ROUTING_DESIGNATOR
, BR.ATTRIBUTE_CATEGORY
, BR.ATTRIBUTE1
, BR.ATTRIBUTE2
, BR.ATTRIBUTE3
, BR.ATTRIBUTE4
, BR.ATTRIBUTE5
, BR.ATTRIBUTE6
, BR.ATTRIBUTE7
, BR.ATTRIBUTE8
, BR.ATTRIBUTE9
, BR.ATTRIBUTE10
, BR.ATTRIBUTE11
, BR.ATTRIBUTE12
, BR.ATTRIBUTE13
, BR.ATTRIBUTE14
, BR.ATTRIBUTE15
, SEQ.OPERATION_SEQUENCE_ID
, SEQ.EFFECTIVITY_DATE
, SEQ.DISABLE_DATE
, BR.RESOURCE_SEQ_NUM
, BR.RESOURCE_OFFSET_PERCENT
, RTG.ROUTING_SEQUENCE_ID
, BR.BASIS_TYPE
, MSI.BUILD_IN_WIP_FLAG
, MSI.BOM_ITEM_TYPE
, NVL(SEQ.CUMULATIVE_YIELD
, 1.0)
FROM MRP_RHX_BOM_DEPT_RESOURCES_V R
, BOM_OPERATION_RESOURCES BR
, BOM_OPERATION_RESOURCES BR2
, BOM_OPERATION_SEQUENCES SEQ
, BOM_OPERATIONAL_ROUTINGS RTG
, MRP_RHX_PLANNED_ITEMS_V MSI
WHERE R.RESOURCE_ID = BR.RESOURCE_ID
AND R.DEPARTMENT_ID = SEQ.DEPARTMENT_ID
AND BR.SCHEDULE_FLAG IN (1
, 3
, 4)
AND BR.BASIS_TYPE IN (1
, 2)
AND BR.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
AND SEQ.ROUTING_SEQUENCE_ID = RTG.COMMON_ROUTING_SEQUENCE_ID
AND TRUNC(NVL(SEQ.DISABLE_DATE
, SYSDATE+1)) > TRUNC(SYSDATE - MRP_RHX_EXT_APS.GET_CONSTANT)
AND (MSI.BUILD_IN_WIP_FLAG = 'Y' OR MSI.BOM_ITEM_TYPE IN (1
, 2))
AND RTG.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND RTG.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND BR.OPERATION_SEQUENCE_ID = BR2.OPERATION_SEQUENCE_ID
AND BR.RESOURCE_ID = BR2.RESOURCE_ID
AND BR.BASIS_TYPE <> BR2.BASIS_TYPE
AND BR2.SCHEDULE_FLAG IN (1
, 3
, 4)
AND BR2.BASIS_TYPE IN (1
, 2)
AND DECODE(BR.BASIS_TYPE
, 1
, BR.USAGE_RATE_OR_AMOUNT
, 0)> 0
AND DECODE(BR2.BASIS_TYPE
, 2
, BR2.USAGE_RATE_OR_AMOUNT
, 0)> 0 UNION ALL SELECT MSI.INVENTORY_ITEM_ID
, MSI.ITEM_NUMBER
, SEQ.OPERATION_SEQ_NUM
, SEQ.OPERATION_DESCRIPTION
, R.RESOURCE_CODE
, R.DEPARTMENT_CODE
, R.SHARE_CAPACITY_FLAG
, DECODE(BR.BASIS_TYPE
, 1
, BR.USAGE_RATE_OR_AMOUNT
, 0)
, 0
, BR.SCHEDULE_FLAG
, R.UNIT_OF_MEASURE
, BR.ASSIGNED_UNITS
, SEQ.YIELD
, R.DEPARTMENT_ID
, R.RESOURCE_ID
, SEQ.MINIMUM_TRANSFER_QUANTITY
, MSI.ORGANIZATION_ID
, RTG.ALTERNATE_ROUTING_DESIGNATOR
, BR.ATTRIBUTE_CATEGORY
, BR.ATTRIBUTE1
, BR.ATTRIBUTE2
, BR.ATTRIBUTE3
, BR.ATTRIBUTE4
, BR.ATTRIBUTE5
, BR.ATTRIBUTE6
, BR.ATTRIBUTE7
, BR.ATTRIBUTE8
, BR.ATTRIBUTE9
, BR.ATTRIBUTE10
, BR.ATTRIBUTE11
, BR.ATTRIBUTE12
, BR.ATTRIBUTE13
, BR.ATTRIBUTE14
, BR.ATTRIBUTE15
, SEQ.OPERATION_SEQUENCE_ID
, SEQ.EFFECTIVITY_DATE
, SEQ.DISABLE_DATE
, BR.RESOURCE_SEQ_NUM
, BR.RESOURCE_OFFSET_PERCENT
, RTG.ROUTING_SEQUENCE_ID
, BR.BASIS_TYPE
, MSI.BUILD_IN_WIP_FLAG
, MSI.BOM_ITEM_TYPE
, NVL(SEQ.CUMULATIVE_YIELD
, 1.0)
FROM MRP_RHX_BOM_DEPT_RESOURCES_V R
, BOM_OPERATION_RESOURCES BR
, BOM_OPERATION_SEQUENCES SEQ
, BOM_OPERATIONAL_ROUTINGS RTG
, MRP_RHX_PLANNED_ITEMS_V MSI
WHERE R.RESOURCE_ID = BR.RESOURCE_ID
AND R.DEPARTMENT_ID = SEQ.DEPARTMENT_ID
AND BR.SCHEDULE_FLAG IN (1
, 3
, 4)
AND BR.BASIS_TYPE = 1
AND BR.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
AND SEQ.ROUTING_SEQUENCE_ID = RTG.COMMON_ROUTING_SEQUENCE_ID
AND TRUNC(NVL(SEQ.DISABLE_DATE
, SYSDATE+1)) > TRUNC(SYSDATE - MRP_RHX_EXT_APS.GET_CONSTANT)
AND (MSI.BUILD_IN_WIP_FLAG = 'Y' OR MSI.BOM_ITEM_TYPE IN (1
, 2))
AND RTG.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND RTG.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND NOT EXISTS (SELECT 'X'
FROM BOM_OPERATION_RESOURCES BR2
WHERE BR2.OPERATION_SEQUENCE_ID = BR.OPERATION_SEQUENCE_ID
AND BR2.RESOURCE_ID = BR.RESOURCE_ID
AND BR2.BASIS_TYPE = 2
AND BR2.SCHEDULE_FLAG IN (1
, 3
, 4) ) UNION ALL SELECT MSI.INVENTORY_ITEM_ID
, MSI.ITEM_NUMBER
, SEQ.OPERATION_SEQ_NUM
, SEQ.OPERATION_DESCRIPTION
, R.RESOURCE_CODE
, R.DEPARTMENT_CODE
, R.SHARE_CAPACITY_FLAG
, 0
, DECODE(BR.BASIS_TYPE
, 2
, BR.USAGE_RATE_OR_AMOUNT
, 0)
, BR.SCHEDULE_FLAG
, R.UNIT_OF_MEASURE
, BR.ASSIGNED_UNITS
, SEQ.YIELD
, R.DEPARTMENT_ID
, R.RESOURCE_ID
, SEQ.MINIMUM_TRANSFER_QUANTITY
, MSI.ORGANIZATION_ID
, RTG.ALTERNATE_ROUTING_DESIGNATOR
, BR.ATTRIBUTE_CATEGORY
, BR.ATTRIBUTE1
, BR.ATTRIBUTE2
, BR.ATTRIBUTE3
, BR.ATTRIBUTE4
, BR.ATTRIBUTE5
, BR.ATTRIBUTE6
, BR.ATTRIBUTE7
, BR.ATTRIBUTE8
, BR.ATTRIBUTE9
, BR.ATTRIBUTE10
, BR.ATTRIBUTE11
, BR.ATTRIBUTE12
, BR.ATTRIBUTE13
, BR.ATTRIBUTE14
, BR.ATTRIBUTE15
, SEQ.OPERATION_SEQUENCE_ID
, SEQ.EFFECTIVITY_DATE
, SEQ.DISABLE_DATE
, BR.RESOURCE_SEQ_NUM
, BR.RESOURCE_OFFSET_PERCENT
, RTG.ROUTING_SEQUENCE_ID
, BR.BASIS_TYPE
, MSI.BUILD_IN_WIP_FLAG
, MSI.BOM_ITEM_TYPE
, NVL(SEQ.CUMULATIVE_YIELD
, 1.0)
FROM MRP_RHX_BOM_DEPT_RESOURCES_V R
, BOM_OPERATION_RESOURCES BR
, BOM_OPERATION_SEQUENCES SEQ
, BOM_OPERATIONAL_ROUTINGS RTG
, MRP_RHX_PLANNED_ITEMS_V MSI
WHERE R.RESOURCE_ID = BR.RESOURCE_ID
AND R.DEPARTMENT_ID = SEQ.DEPARTMENT_ID
AND BR.SCHEDULE_FLAG IN (1
, 3
, 4)
AND BR.BASIS_TYPE = 2
AND BR.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
AND SEQ.ROUTING_SEQUENCE_ID = RTG.COMMON_ROUTING_SEQUENCE_ID
AND TRUNC(NVL(SEQ.DISABLE_DATE
, SYSDATE+1)) > TRUNC(SYSDATE - MRP_RHX_EXT_APS.GET_CONSTANT)
AND (MSI.BUILD_IN_WIP_FLAG = 'Y' OR MSI.BOM_ITEM_TYPE IN (1
, 2))
AND RTG.ASSEMBLY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND RTG.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND NOT EXISTS (SELECT 'X'
FROM BOM_OPERATION_RESOURCES BR2
WHERE BR2.OPERATION_SEQUENCE_ID = BR.OPERATION_SEQUENCE_ID
AND BR2.RESOURCE_ID = BR.RESOURCE_ID
AND BR2.BASIS_TYPE = 1
AND BR2.SCHEDULE_FLAG IN (1
, 3
, 4) )

Columns