View: MTL_UOM_CONVERSIONS_VIEW

Product:	INV - Inventory
Description:	View giving unit of measure conversion rates between the primary unit of measure and another unit of measure
Implementation/DBA Data:	APPS.MTL_UOM_CONVERSIONS_VIEW

View Text

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)

Columns