VIEW: APPS.MTL_UOM_CONVERSIONS_VIEW

Source

View Text - Preformatted

SELECT MSI.INVENTORY_ITEM_ID, MSI.ORGANIZATION_ID, MSI.PRIMARY_UNIT_OF_MEASURE, MSI.PRIMARY_UOM_CODE, UOM.UOM_CLASS, UOM2.UNIT_OF_MEASURE, UOM2.UOM_CODE, CONV2.UOM_CLASS, (decode(UOM.UOM_CLASS, UOM2.UOM_CLASS, 1, CLASS.CONVERSION_RATE)*CONV2.CONVERSION_RATE/ decode(CONV1.INVENTORY_ITEM_ID, null, decode(least(nvl(CONV1_2.DISABLE_DATE, sysdate), sysdate), sysdate, CONV1_2.CONVERSION_RATE, null), decode(least(nvl(CONV1.DISABLE_DATE, sysdate), sysdate), sysdate, CONV1.CONVERSION_RATE, CONV1_2.CONVERSION_RATE))) FROM MTL_UOM_CONVERSIONS CONV1,MTL_UOM_CONVERSIONS CONV2,MTL_UOM_CONVERSIONS CONV1_2,MTL_UNITS_OF_MEASURE_VL UOM,MTL_UNITS_OF_MEASURE_VL UOM2,MTL_UOM_CLASS_CONVERSIONS CLASS,MTL_SYSTEM_ITEMS_B MSI WHERE UOM.UOM_CODE = MSI.PRIMARY_UOM_CODE and uom2.uom_class = decode(uom.uom_class, uom2.uom_class, uom2.uom_class, class.to_uom_class) and (class.to_uom_class is null or class.to_uom_class = (select class2.to_uom_class from mtl_uom_class_conversions class2 where class2.inventory_item_id = msi.inventory_item_id and uom2.uom_class = decode(uom.uom_class, uom2.uom_class, uom2.uom_class, class2.to_uom_class) and uom.uom_class = decode(uom.uom_class, uom2.uom_class, uom.uom_class, class2.from_uom_class) and rownum = 1)) and conv1.inventory_item_id(+) = msi.inventory_item_id and conv1_2.inventory_item_id(+) = 0 and conv1.uom_code(+) = MSI.PRIMARY_UOM_CODE and conv1_2.uom_code(+) = MSI.PRIMARY_UOM_CODE and decode(CONV1.INVENTORY_ITEM_ID, null, decode(least(nvl(CONV1_2.DISABLE_DATE, sysdate), sysdate), sysdate, CONV1_2.CONVERSION_RATE, null), decode(least(nvl(CONV1.DISABLE_DATE, sysdate), sysdate), sysdate, CONV1.CONVERSION_RATE, CONV1_2.CONVERSION_RATE)) is not null and decode(uom.uom_class, uom2.uom_class, 1, decode(least(nvl(class.DISABLE_DATE, sysdate), sysdate), sysdate, 1, null)) is not null and class.inventory_item_id(+) = msi.inventory_item_id and uom.uom_class = decode(uom.uom_class, uom2.uom_class, uom.uom_class, class.from_uom_class) and (conv2.inventory_item_id, conv2.uom_code) = (select max(conv_sub2.inventory_item_id), uom2.uom_code from mtl_uom_conversions conv_sub2 where conv_sub2.uom_code = uom2.uom_code and conv_sub2.inventory_item_id in (msi.inventory_item_id, 0) and nvl(class.inventory_item_id, 1) is not null and NVL(conv_sub2.disable_date, SYSDATE+1) > SYSDATE)

View Text - HTML Formatted

SELECT MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, MSI.PRIMARY_UNIT_OF_MEASURE
, MSI.PRIMARY_UOM_CODE
, UOM.UOM_CLASS
, UOM2.UNIT_OF_MEASURE
, UOM2.UOM_CODE
, CONV2.UOM_CLASS
, (DECODE(UOM.UOM_CLASS
, UOM2.UOM_CLASS
, 1
, CLASS.CONVERSION_RATE)*CONV2.CONVERSION_RATE/ DECODE(CONV1.INVENTORY_ITEM_ID
, NULL
, DECODE(LEAST(NVL(CONV1_2.DISABLE_DATE
, SYSDATE)
, SYSDATE)
, SYSDATE
, CONV1_2.CONVERSION_RATE
, NULL)
, DECODE(LEAST(NVL(CONV1.DISABLE_DATE
, SYSDATE)
, SYSDATE)
, SYSDATE
, CONV1.CONVERSION_RATE
, CONV1_2.CONVERSION_RATE)))
FROM MTL_UOM_CONVERSIONS CONV1
, MTL_UOM_CONVERSIONS CONV2
, MTL_UOM_CONVERSIONS CONV1_2
, MTL_UNITS_OF_MEASURE_VL UOM
, MTL_UNITS_OF_MEASURE_VL UOM2
, MTL_UOM_CLASS_CONVERSIONS CLASS
, MTL_SYSTEM_ITEMS_B MSI
WHERE UOM.UOM_CODE = MSI.PRIMARY_UOM_CODE
AND UOM2.UOM_CLASS = DECODE(UOM.UOM_CLASS
, UOM2.UOM_CLASS
, UOM2.UOM_CLASS
, CLASS.TO_UOM_CLASS)
AND (CLASS.TO_UOM_CLASS IS NULL OR CLASS.TO_UOM_CLASS = (SELECT CLASS2.TO_UOM_CLASS
FROM MTL_UOM_CLASS_CONVERSIONS CLASS2
WHERE CLASS2.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND UOM2.UOM_CLASS = DECODE(UOM.UOM_CLASS
, UOM2.UOM_CLASS
, UOM2.UOM_CLASS
, CLASS2.TO_UOM_CLASS)
AND UOM.UOM_CLASS = DECODE(UOM.UOM_CLASS
, UOM2.UOM_CLASS
, UOM.UOM_CLASS
, CLASS2.FROM_UOM_CLASS)
AND ROWNUM = 1))
AND CONV1.INVENTORY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID
AND CONV1_2.INVENTORY_ITEM_ID(+) = 0
AND CONV1.UOM_CODE(+) = MSI.PRIMARY_UOM_CODE
AND CONV1_2.UOM_CODE(+) = MSI.PRIMARY_UOM_CODE
AND DECODE(CONV1.INVENTORY_ITEM_ID
, NULL
, DECODE(LEAST(NVL(CONV1_2.DISABLE_DATE
, SYSDATE)
, SYSDATE)
, SYSDATE
, CONV1_2.CONVERSION_RATE
, NULL)
, DECODE(LEAST(NVL(CONV1.DISABLE_DATE
, SYSDATE)
, SYSDATE)
, SYSDATE
, CONV1.CONVERSION_RATE
, CONV1_2.CONVERSION_RATE)) IS NOT NULL
AND DECODE(UOM.UOM_CLASS
, UOM2.UOM_CLASS
, 1
, DECODE(LEAST(NVL(CLASS.DISABLE_DATE
, SYSDATE)
, SYSDATE)
, SYSDATE
, 1
, NULL)) IS NOT NULL
AND CLASS.INVENTORY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID
AND UOM.UOM_CLASS = DECODE(UOM.UOM_CLASS
, UOM2.UOM_CLASS
, UOM.UOM_CLASS
, CLASS.FROM_UOM_CLASS)
AND (CONV2.INVENTORY_ITEM_ID
, CONV2.UOM_CODE) = (SELECT MAX(CONV_SUB2.INVENTORY_ITEM_ID)
, UOM2.UOM_CODE
FROM MTL_UOM_CONVERSIONS CONV_SUB2
WHERE CONV_SUB2.UOM_CODE = UOM2.UOM_CODE
AND CONV_SUB2.INVENTORY_ITEM_ID IN (MSI.INVENTORY_ITEM_ID
, 0)
AND NVL(CLASS.INVENTORY_ITEM_ID
, 1) IS NOT NULL
AND NVL(CONV_SUB2.DISABLE_DATE
, SYSDATE+1) > SYSDATE)