View: OPI_EDW_UOM_CONV_FCV

Product:	OPI - Operations Intelligence
Description:	Source view for the Unit of Measure Conversion Fact table
Implementation/DBA Data:	APPS.OPI_EDW_UOM_CONV_FCV

View Text

SELECT DECODE(MUC.INVENTORY_ITEM_ID
, 0
, 'STANDARD'
, TO_CHAR(MUC.INVENTORY_ITEM_ID))||'-'||MUC.UOM_CODE||'-'||ELI.INSTANCE_CODE
, NULL
, NULL
, MUC.INVENTORY_ITEM_ID
, MUC.UOM_CODE
, BUOM.UOM_CODE
, MUC.CONVERSION_RATE
, TO_NUMBER(NULL)
, ELI.INSTANCE_CODE
, NULL
, NULL
, NULL
, NULL
, NULL
, MSIK.CONCATENATED_SEGMENTS
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, MUC.LAST_UPDATE_DATE
, NULL
, MUC.CREATION_DATE
, NULL
, NULL
, NULL
, TO_NUMBER( NULL)
, TO_NUMBER( NULL)
, TO_NUMBER( NULL)
, TO_NUMBER( NULL)
, TO_NUMBER( NULL)
, 'N'
FROM EDW_LOCAL_INSTANCE ELI
, MTL_UOM_CONVERSIONS MUC
, MTL_UNITS_OF_MEASURE MUOM
, MTL_UNITS_OF_MEASURE BUOM
, MTL_SYSTEM_ITEMS_KFV MSIK
WHERE MUOM.UOM_CODE = MUC.UOM_CODE
AND MUOM.UOM_CLASS = MUC.UOM_CLASS
AND MUOM.UOM_CLASS = BUOM.UOM_CLASS
AND BUOM.BASE_UOM_FLAG='Y'
AND MSIK.INVENTORY_ITEM_ID (+) = MUC.INVENTORY_ITEM_ID UNION SELECT DECODE(MUCC.INVENTORY_ITEM_ID
, 0
, 'STANDARD'
, TO_CHAR(MUCC.INVENTORY_ITEM_ID)) ||'-' ||MUCC.FROM_UOM_CODE||'-'||MUCC.TO_UOM_CODE||'-'||ELI.INSTANCE_CODE
, NULL
, NULL
, MUCC.INVENTORY_ITEM_ID
, MUCC.FROM_UOM_CODE
, MUCC.TO_UOM_CODE
, MUCC.CONVERSION_RATE
, TO_NUMBER(NULL)
, ELI.INSTANCE_CODE
, NULL
, NULL
, NULL
, NULL
, NULL
, MAX(MSIK.CONCATENATED_SEGMENTS||'-'||MSIK.ORGANIZATION_ID)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, MUCC.LAST_UPDATE_DATE
, NULL
, MUCC.CREATION_DATE
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER( NULL)
, TO_NUMBER( NULL)
, TO_NUMBER( NULL)
, TO_NUMBER( NULL)
, 'Y'
FROM EDW_LOCAL_INSTANCE ELI
, MTL_UOM_CLASS_CONVERSIONS MUCC
, MTL_SYSTEM_ITEMS_KFV MSIK
WHERE MUCC.FROM_UOM_CODE <> MUCC.TO_UOM_CODE
AND MSIK.INVENTORY_ITEM_ID = MUCC.INVENTORY_ITEM_ID GROUP BY DECODE(MUCC.INVENTORY_ITEM_ID
, 0
, 'STANDARD'
, TO_CHAR(MUCC.INVENTORY_ITEM_ID)) ||'-' ||MUCC.FROM_UOM_CODE||'-'||MUCC.TO_UOM_CODE||'-'||ELI.INSTANCE_CODE
, MUCC.INVENTORY_ITEM_ID
, MUCC.FROM_UOM_CODE
, MUCC.TO_UOM_CODE
, MUCC.CONVERSION_RATE
, ELI.INSTANCE_CODE
, MUCC.LAST_UPDATE_DATE
, MUCC.CREATION_DATE

Columns