U of M Schedule Change Utility
HelpID: 502622049
The U of M Schedule Change utility converts all Master Records and Transactions for an Item to use a new U of M Schedule and Unit of Measure, where the Quantity Decimal Places is the same or larger and the base quantity does not change.
Without U of M Schedule Change, manually changing an Item’s Unit of Measure Schedule on the Item Maintenance window deletes the Item Purchasing Options and the Price List. The new unit of measure (i.e. the new Base Unit of Measure) does not flow through to the rest of GP, and affected records need to be manually changed. For example, an affected sales transaction will not post until a user manually changes the U of M on the sales line to be a new, valid Unit of Measure.
The U of M Schedule Change utility automatically updates all affected tables throughout the system. It has two methods of performing updates:
- First, it always tries to map the old U of M to a new U of M by the conversion quantity to the base unit. For example, if the old Schedule has Case = 10 Each, the new Schedule might have CS = 10 EA. If it can find a map-able unit of measure, transactions will be updated to CS.
- Second, if no mapping can be found, it converts the quantity to the quantity in base, and changes the U of M to the base. For example, if the old Schedule has Case = 10 Each but the new schedule only has the base conversion (EA = 1 EA), or it has a new Case conversion (CA = 12 EA), any affected transactions will be converted to the base quantity using the old Schedule (Case = 10 Each), and the U of M will be set to the new base (EA).
The U of M Change Utility updates the Item Master. Do NOT change the Unit of Measure Schedule in the Item Maintenance window.
The U of M Schedule Change Utility can change the Quantity Decimals during the conversion process. The new U of M Schedule can have a LARGER number of quantity decimals (i.e. going from 2-decimals to 5-decimals). The new U of M Schedule must have the SAME or GREATER quantity decimals as the original U of M Schedule.
The U of M Schedule Change Utility cannot change the quantity or cost of an item. For example, you set up an item with the incorrect measurement for the base quantity, such as Feet instead of Inches. Making such a change requires the quantity on GP to go from 1 Foot in stock to 12 inches in stock with a corresponding per unit cost conversion. The ONLY way to perform such a change is as follows:
- Remove the item from all open transactions.
- Create an Inventory Adjustment to remove ALL inventory of the item (adjust it to zero).
- Change the U of M Schedule
- Create an Inventory Adjustment to put the new quantity of the item back into stock at the new cost.
- Create a new Price List
- Put the item back on to open transactions at the new Cost/Price.
Serial Numbered items must have 0-quantity decimals. This setting cannot be changed.
Testing the Utility. We strongly encourage you to test the utility in a test company before trying it on live data. 3rd Party Products and/or customizations may cause unexpected effects on the conversion process, so you should test carefully and validate the conversion occurred successfully in a test system.
SQL Table Triggers. The utility was developed and tested using a standard GP deployment with all of the GP modules installed from the installation media. 3rd Party Products and/or customizations might add sql table triggers which may interfere with the U of M Schedule change process.
For example, as shown below for the Price List table (IV00108), Scribe has added several triggers. The “zDT” triggers are part of normal out-of-the-box installation of Dynamics GP.
If you encounter problems with the conversion, the first thing to check for are non-standard SQL Table Triggers on the tables where data did not convert correctly.
IMPORTANT: 3RD Party Products, customizations, and table triggers added by a 3rd Party Product may cause the utility to fail. You must take care to test the utility in a test company prior to running it against live data. Furthermore, the product cannot update 3rd Party Products. Any SQL tables containing Unit of Measure information that are NOT part of core Dynamics GP, must be updated manually. See the UPDATED TABLES list for the tables covered by this utility.
All users must be out of the system. The utility does NOT check for activity on records and it assumes no other users will be in the system when the update is performed. The utility can be run by any user (it does not need to be run by SA).
Using U of M Schedule Change Utility
Navigation: Tools >> Setup >> Company >> Setup GP PowerPack
Mark the box next to MODULE: UM Change Utility.
You will need to restart Dynamics GP to enable this feature.
Navigation: Tools >> Utilities >> Inventory >> UM Change
-
- Select the Item Number to update
- Select the new Unit of Measure Schedule
- Click Process
Be sure to test the utility on actual company data in a TEST Company database before trying it in the live database. Make sure you understand the impact the tool will have on price lists, purchasing options, and extended pricing.
As part of the update process, if you increase the Quantity Decimals, the utility will adjust the quantity breaks in the Price List. For example, a price list is shown below for an item with zero quantity decimals.
The same Price List is shown below, after changing the item to a new U of M Schedule with 2-decimal places.
Note that the lowest end of the quantity range was changed to 0.01 from 1. And the upper end of each range was extended to account for the additional decimals (i.e. 9 became 9.99).
Suppress Report: when the update process completes, a report prints showing all of the records that were updated. This can be a very long report. When this box is marked the system will perform the updates, but not print the report. You might do this if using a macro to update a large number of items.
Mass Updates
A GP Macro can be used to process a large number of items automatically. The utility will not generate error messages which interfere with a macro, as all errors are sent to the report. Mark the Suppress Report box to keep the report from printing after every item.
IV Price List Reconcile Utility
From the GoTo button select Reconcile IV Price List. NOTE: the utility is run automatically during the conversion process and should not normally need to be run manually.
Before running the utility ensure that no users are in Sales Transaction Entry. Also, make a backup of the company database. When you click the reconcile button you will be asked to confirm no users are in Sales Transaction Entry, and that you have made a backup. Progress will be displayed in the Status Box at the bottom of the window.
IV30300 Error
In some cases it is possible to create a transaction in GP with a Unit of Measure that is subsequently removed from the Unit of Measure Schedule. This leaves records in IV30300 that have a U of M that does not exist on the Item’s UM Schedule and an attempt to change the Item’s UM Schedule will result in errors and a failure in the update utility. To prevent this, the utility will check for invalid U of Ms and warn you. If invalid U of Ms exists they must be added to the Item’s U of M Schedule before running the UM Schedule Change Utility.
The query below will return the Item Number, invalid U of M, and the DEX_ROW_ID of the record with the bad data. Add the U of M to the Item’s Unit of Measure Schedule.
Updated Tables
The U of M Schedule Change utility updates table in GP, Manufacturing and Service.
The chart below lists all of the tables updated by the U of M Schedule Change utility. Unless otherwise noted, all tables are updated using the logic described above.
Some tables in GP store the transaction quantity and unit of measure (i.e. 1-Case), and any other quantities related to the transaction are in the base quantity (i.e. 10 Each). Sales Transaction Lines are stored in this manner (SOP10200). Other tables store every quantity using the base quantity (i.e. 10 Each), but display in the user interface based on the U of M selected (i.e. Case). A Manufacturing table (BMMU0001) uses this method. When checking the results of an update using this tool, keep in mind that different tables store quantities and units of measure in different ways that may appear inconsistent, but are correct when viewed from inside GP.
| Physical Name | Display Name | Product | Update/Comment |
| BM00101 | Bill of Materials Header | GP | |
| BM010115 | Bill of Material Line File | MFG | |
| BM010117 | BOM Mass Changes Line Defaults | MFG | |
| BM10300 | Assembly Component | GP | |
| BMMU0001 | BOM Mass Changes Work | MFG | |
| CT00102 | IC Cost Item Master | MFG | Updates the U of M Schedule |
| IS010210 | Shipping Line Work | MFG | |
| IV00101 | Item Master | GP | Changes the Unit of Measure Schedule |
| IV00103 | Item Vendor Master | GP | Default Purchasing UofM |
| IV00104 | Item Kit Master | GP | |
| IV00106 | Item Purchasing | GP | The Item Purchasing options are normally deleted when you change the U of M Schedule on Item Maintenance. The Utility will delete the old purchasing options, but it will automatically create new purchasing items (all units set to Whole if there are no quantity decimals, or Whole and Fractional if there are quantity decimals). The default purchasing U of M will be set to the base unit. |
| IV00107 | Item Price List Options | GP | This table is normally completely deleted when changing the UofM Schedule through Item Maintenance. The Utility keeps as many records as possible by mapping to the new Schedule. It deletes all units that cannot be mapped. The remaining records are changed to the new mapped unit. |
| IV00108 | Item Price List | GP | See IV00107 |
| IV10001 | Inventory Transaction Amounts Work | GP | |
| IV10303 | Stock Count U of M | GP | Converts all UofM counted quantities to base. |
| IV10402 | Extended Pricing Price Sheet UofM work | GP | Converts mapped units, deletes all others. |
| IV10403 | Extended Pricing Promotion Free Item | GP | Converts mapped units, deletes all others. |
| IVC10101 | Invoicing Transaction Amounts Work | GP | |
| MOP1210 | MOP Pick Doc Line | MFG | |
| MRP0100 | MRP Items List | MFG | Updates base unit |
| MRP1600 | MRP Planned Orders | MFG | Updates base unit |
| OC010100 | Option Category Line | MFG | Updates base unit |
| PK010033 | Picklist File | MFG | |
| POP10110 | Purchase Order Line | GP | |
| POP10310 | Purchasing Receipt Line | GP | |
| POP10500 | Purchasing Receipt Line Quantities | GP | |
| SOP10200 | Sales Transaction Amounts Work | GP | |
| SOP10204 | Extended Pricing Promotion Work | GP | There are no quantities in this table. UofM is changed with to a mapped unit, or to the base unit. This will match the same change made to the Sales Line. |
| SOP60100 | SOP-POP Link | GP | |
| SVC00203 | Service Call Line Detail | SVC | |
| SVC00601 | Service Contract Line | SVC | |
| SVC00701 | Service Transfer Line | SVC | |
| SVC00710 | Service Inventory Requirements | SVC | |
| SVC00851 | ECO Line | SVC | |
| SVC05015 | RMA Receiving Work | SVC | |
| SVC05200 | Returns Line Master | SVC | |
| SVC05601 | RTV Line | SVC | |
| SVC06101 | Depot Master Line | SVC | |
| SVC07511 | Expense Form Line | SVC | |
| SVC8001 | Maintenance Detail Line | SVC |