VIEW: APPS.OPI_EDW_UOM_CONV_FCV

Source

View Text - Preformatted

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

View Text - HTML Formatted

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