U of M Schedule Change

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.

cid:image001.jpg@01D1AC2F.6F92D830

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