- 24 November 2015
- Laura Bromhead
There are certain situations in which it is necessary for the user to load or update massive amounts of data within Epicor. Standard Epicor Software offers the ability to load and update several tables and pieces of data using DMT (Data Migration Tool) which can save users and companies a lot of time on different scenarios; whether it is migrating from one environment or server to another or loading Parts into the database, the standard DMT tool is the one for the job.
There are some instances, though, in which the user has specific needs that DMT might not be able to cover. In the next example, the user needs to be able to update the prices for specific parts on ALL Quotes in the database depending on certain specifics. With DMT, you would be able to update the prices for the parts, or you might even be able to run a SQL script to update Quotes/Parts/Prices. However, in this particular scenario, the user would only like the Quotes that meet specific criteria to be updated AND have the ability to quickly choose which parts get updated.
The customer requires a program that reads an external text file which would include a list of Part Numbers and prices to update. This file will be in CSV format:
The first column of the file represents the PartNumber that will be updated, and the second column shows the Adjustment amount that will be made to price in the Quote lines for those corresponding parts.
Additionally, several requirements need to be met in order for the quote to be eligible for updating:
- The quote needs to be ’QUOTED’
- A custom checkbox in the Customer Master labeled “Quote Price Adjustable” needs to be set to TRUE
- Quotes will NOT be updated if the quote line description begins with the letters ‘OBS’ stating that this is an Obsolete quote/part
- Finally, quotes that have a quoted date AFTER a custom Cutoff Date field stored in the Company table, will NOT be updated
A custom software solution was developed by Cre8tive Technology & Design which gives the user the ability to execute all of these validations and automatically update the applicable quotes. The application has 3 different modes which can be toggled for the user to run:
- Audit Log: This option reviews all quote lines that apply, considering the validations mentioned above and creates a log CSV file without actually updating any records. The returned fields are:
- Quote Number
- Quote Line
- Part Number
- Old Price
- New Price
- Adjustment Amount
- Update: This method updates each of the quote lines that meet the previously specified criteria. The process adjusts the Quote Qty price with the adjustment information included in the import CSV file. The adjustment amount can be positive or negative.
- Undo Last Update: This action will undo the last executed update, it will take the stored Adjustment amount from a UD field in the QuoteDtl table and set back the price by subtracting the value stored.
A walkthrough example of the customization running is shown next:
If more than one Quantity Break exist, the price adjustment will be made to every single Quantity break line.
This custom program gives the user the ability to execute multiple updates to all Quote Lines in the database that meet the criteria without having to write complex and individual queries or having to update via DMT multiple times for each individual part/price combination. It also gives the user the ability to analyze the changes that will be executed BEFORE actually running the program using the ‘Audit Log only’ mode.
Follow previous postings on https://www.ctnd.com/blog/