VIEW: APPS.MSC_WT_UOM_CONVERSIONS_VIEW

Source

View Text - Preformatted

SELECT MSI.INVENTORY_ITEM_ID, MSI.ORGANIZATION_ID, MSI.WEIGHT_UOM, MSI.SR_INSTANCE_ID, 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, null))) FROM MSC_UOM_CONVERSIONS CONV1, MSC_UOM_CONVERSIONS CONV2, MSC_UOM_CONVERSIONS CONV1_2, MSC_UNITS_OF_MEASURE UOM, MSC_UNITS_OF_MEASURE UOM2, MSC_UOM_CLASS_CONVERSIONS CLASS, MSC_SYSTEM_ITEMS MSI WHERE MSI.PLAN_ID = -1 AND UOM.UOM_CODE = MSI.WEIGHT_UOM 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 msc_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.WEIGHT_UOM and conv1_2.uom_code(+) = MSI.WEIGHT_UOM 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, null)) 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 msc_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.WEIGHT_UOM
, MSI.SR_INSTANCE_ID
, 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
, NULL)))
FROM MSC_UOM_CONVERSIONS CONV1
, MSC_UOM_CONVERSIONS CONV2
, MSC_UOM_CONVERSIONS CONV1_2
, MSC_UNITS_OF_MEASURE UOM
, MSC_UNITS_OF_MEASURE UOM2
, MSC_UOM_CLASS_CONVERSIONS CLASS
, MSC_SYSTEM_ITEMS MSI
WHERE MSI.PLAN_ID = -1
AND UOM.UOM_CODE = MSI.WEIGHT_UOM
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 MSC_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.WEIGHT_UOM
AND CONV1_2.UOM_CODE(+) = MSI.WEIGHT_UOM
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
, NULL)) 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 MSC_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)