INV Unit Of Measure Conversion Upload
INV Unit Of Measure Conversion Upload creates and updates Oracle Inventory unit-of-measure conversions – standard, intra-class, inter-class and lot-specific inter-class – for an inventory organization, from Excel. Updates, and standard and lot conversions, require Oracle EBS R12.2.3 or later.
When to use it
- Mass-load new unit-of-measure conversions when setting up or extending an organization.
- Add standard, item-specific or lot-specific conversions.
- Update conversion rates or set inactive dates in bulk (R12.2.3 and later).
- Review and export existing conversions for an organization.
Before you start
- Blitz Report is installed and you are signed in to an Inventory responsibility with access to the organization.
- The units of measure, classes, and any items and lots already exist.
- Updates, and standard and lot conversions, require Oracle EBS R12.2.3 or later; on R12.1 only new item-specific conversions can be created.
Step 1 – Choose a mode, set the parameters and download
In Blitz Report, open INV Unit Of Measure Conversion Upload, choose an Upload Mode (Create, or Create, Update to also update existing conversions), and set the parameters:
| Parameter | Purpose |
|---|---|
| Organization Code | The inventory organization. |
| Upload Mode | Create or Create, Update. Default is Create. |
| Conversion Type | Optionally download one type: Standard, Intra-class, Inter-class or Lot Inter-class. |
| From / To Unit of Measure (Class) / Item / Lot | Restrict which conversions are downloaded. |
Run the upload to download and open the Excel file. Each row is automatically handled as a create or an update depending on whether the conversion already exists.
Step 2 – Enter the conversions
On each row choose the From Uom and To Uom (their classes are derived automatically) and enter the Uom Conversion Rate. For an item-specific conversion enter the Item; for a lot conversion enter the Lot Number and the lot update options. The Conversion Type is derived from what you enter. Use the Inactive Date to retire a conversion.
Step 3 – Validate and Save
Click Validate and Save. This checks for missing required values (both units, their classes and a rate) and runs the upload’s validation, then saves the file. Correct anything it flags before continuing.
Step 4 – Upload and view the result
Back in Blitz Report, click Upload and select your saved file. This submits the Blitz Upload request, which creates or updates each conversion. When it finishes, a result report opens showing each row as success or error.
What’s produced
- Created and updated unit-of-measure conversions in the organization.
- A result report listing every row with a status (success or error) and a message.
Common questions
Can I delete a conversion with this upload?
No. There is no delete – set an Inactive Date to retire a conversion.
Why can’t I update a conversion, or create a standard or lot conversion?
Your environment is on R12.1, where the Oracle API only supports creating item-specific intra-class and inter-class conversions. Update, standard and lot conversions require R12.2.3 or later.
Do I have to fill in the Conversion Type?
It is derived from your data: a lot makes it Lot Inter-class; differing from and to classes make it Inter-class; an item with the same class makes it Intra-class; otherwise Standard.
What goes in From/To UOM versus From/To UOM Class?
Pick the units of measure; their classes are derived automatically.
When are the lot update columns used?
Only for Lot Inter-class conversions (when a Lot Number is supplied, R12.2.3+). They tell the system how to recalculate on-hand balances for the lot.
Troubleshooting
| Message | Cause | What to do |
|---|---|---|
| Specify both the From/To UOM and class and a conversion rate | One of the from/to units, their classes, or the rate is missing. | Fill in all the required fields on the row. |
| New conversions cannot be created in Update mode / existing cannot be updated in Create mode | The row’s detected action conflicts with the chosen Upload Mode. | Run with Create, Update mode, or split rows by action. |
| Item is required for lot conversions / standard conversions cannot be created in R12.1 | A lot conversion without an item, or a standard conversion on R12.1. | Supply a valid item; on R12.1 only item-specific conversions can be created. |
| Item is invalid | The item does not exist in the chosen organization. | Pick the item from the list for the correct organization. |
| API error on a row | The Oracle unit-of-measure API rejected the row. | Read the message in the result, correct the row, and re-upload. |
with uom_conv_qry as ( -- -- standard and intra-class -- select decode(muc.inventory_item_id,0,'Standard','Intra-class') conversion_type, -- msiv.concatenated_segments item, msiv.description item_description, null lot_number, -- muomvf.unit_of_measure_tl from_uom, mucv.uom_class_tl from_uom_class, muc.conversion_rate uom_conversion_rate, muomvt.unit_of_measure_tl to_uom, mucv.uom_class_tl to_uom_class, muc.disable_date inactive_date, -- '1 ' || muomvt.unit_of_measure_tl || ' = ' || muc.conversion_rate || ' x ' || muomvf.unit_of_measure_tl conversion_text, -- muc.last_update_date, muc.last_updated_by from mtl_uom_conversions muc, mtl_units_of_measure_vl muomvf, mtl_units_of_measure_vl muomvt, mtl_uom_classes_vl mucv, mtl_system_items_vl msiv, mtl_parameters mp where muc.uom_class = muomvf.uom_class and muomvf.base_uom_flag = 'Y' and muc.uom_class = muomvt.uom_class and muc.uom_code = muomvt.uom_code and muomvt.base_uom_flag != 'Y' and muc.uom_class = mucv.uom_class and muc.inventory_item_id = msiv.inventory_item_id (+) and msiv.organization_id = mp.organization_id (+) and (muc.inventory_item_id = 0 or mp.organization_id = mp.master_organization_id ) union all -- -- inter-class -- select -- 'Inter-class' conversion_type, -- msiv.concatenated_segments item, msiv.description item_description, null lot_number, -- muomvf.unit_of_measure_tl from_uom, mucvf.uom_class_tl from_uom_class, mucc.conversion_rate uom_conversion_rate, muomvt.unit_of_measure_tl to_uom, mucvt.uom_class_tl to_uom_class, mucc.disable_date inactive_date, -- '1 ' || muomvt.unit_of_measure_tl || ' = ' || mucc.conversion_rate || ' x ' || muomvf.unit_of_measure_tl conversion_text, -- mucc.last_update_date, mucc.last_updated_by from mtl_uom_class_conversions mucc, mtl_units_of_measure_vl muomvf, mtl_units_of_measure_vl muomvt, mtl_uom_classes_vl mucvf, mtl_uom_classes_vl mucvt, mtl_system_items_vl msiv, mtl_parameters mp where mucc.from_uom_class = muomvf.uom_class and mucc.from_uom_code = muomvf.uom_code and mucc.to_uom_class = muomvt.uom_class and mucc.to_uom_code = muomvt.uom_code and mucc.from_uom_class = mucvf.uom_class and mucc.to_uom_class = mucvt.uom_class and mucc.inventory_item_id = msiv.inventory_item_id and msiv.organization_id = mp.organization_id and mp.organization_id = mp.master_organization_id union all -- -- lot inter-class -- select -- 'Lot Inter-class' conversion_type, -- msiv.concatenated_segments item, msiv.description item_description, mlucc.lot_number, -- muomvf.unit_of_measure_tl from_uom, mucvf.uom_class_tl from_uom_class, mlucc.conversion_rate uom_conversion_rate, muomvt.unit_of_measure_tl to_uom, mucvt.uom_class_tl to_uom_class, mlucc.disable_date inactive_date, -- '1 ' || muomvt.unit_of_measure_tl || ' = ' || mlucc.conversion_rate || ' x ' || muomvf.unit_of_measure_tl conversion_text, -- mlucc.last_update_date, mlucc.last_updated_by from mtl_lot_uom_class_conversions mlucc, mtl_units_of_measure_vl muomvf, mtl_units_of_measure_vl muomvt, mtl_uom_classes_vl mucvf, mtl_uom_classes_vl mucvt, mtl_system_items_vl msiv, mtl_parameters mp where mlucc.from_uom_class = muomvf.uom_class and mlucc.from_uom_code = muomvf.uom_code and mlucc.to_uom_class = muomvt.uom_class and mlucc.to_uom_code = muomvt.uom_code and mlucc.from_uom_class = mucvf.uom_class and mlucc.to_uom_class = mucvt.uom_class and mlucc.inventory_item_id = msiv.inventory_item_id and mlucc.organization_id = msiv.organization_id and mlucc.organization_id = mp.organization_id and mp.organization_code = :p_organization_code ) -- -- Main Query Starts Here -- select /*+ push_pred(uomc) */ null action_, null status_, null message_, null request_id_, null modified_columns_, to_date(null) timestamp_, :p_upload_mode upload_mode_, :p_organization_code organization, -- uomc.item, uomc.item_description, uomc.lot_number, -- uomc.from_uom, uomc.from_uom_class, uomc.to_uom, uomc.to_uom_class, uomc.uom_conversion_rate, uomc.inactive_date, -- null lot_update_type, null lot_update_reason, -- uomc.conversion_text, uomc.conversion_type, to_number(null) upload_row from uom_conv_qry uomc where nvl(:p_r122_api_exists,'N') = nvl(:p_r122_api_exists,'N') and 1=1 |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Upload Mode |
| LOV | |
| Conversion Type |
| LOV | |
| From Unit of Measure Class |
| LOV | |
| From Unit of Measure |
| LOV | |
| To Unit of Measure Class |
| LOV | |
| To Unit of Measure |
| LOV | |
| Item |
| LOV | |
| Lot Number |
| LOV |