Customization DS1557- Extended Pricing Price Update Utility

Customization DS1557

Extended Pricing Price Update Utility

Problem Definition:

ACME uses Extended Pricing with GP. They have both universal pricelists for items as well as customer specific pricelists. ACME often makes price increases or decreases for specific customers. These price changes can be either for all items in the price sheet, or specific items.

The items on the price sheet have the following characteristics:

  1. Only one unit of measure per item.
  2. There is usually more than one price break.

The source of the item list used by the price update utility can come from both an external source, (Salesforce), or from users within GP.

Design Features:

Staging Table

The enhancement will create a staging table in the company database. This table will be used by both the Salesforce process and the UI mass update window in the next section. ACME will be responsible for populating the table from Salesforce.

The table name and final field names will be provided after project approval.

Field Function
Customer Number Required for Import. The customer must exist in GP. It is a key field of the table.
Price Sheet ID When records are added to the staging table, this field can be blank. If it is populated, the PS must be both in GP and assigned to the customer. The PS will be filled in from the GP. (See next section)
Item Number Required for Import. The customer must exist in GP, and belong to the Price Sheet ID.
Quantity From If price breaks exist, enter the starting quantity of the price break. If only one price break exists, this should be set to 1 by default
Consolidate Price Breaks True or False. Set this to True (1) if the utility should remove all extra price breaks (if they exist) and set the item to use only one price break.
Update Method 1 = Fixed Price

2 = Percent Change
Price Value If the Update Method is 1 (Fixed Price), the value entered here will be the NEW PRICE of the item on the price list.

If the Update Method is 2 (Percent Change), the value entered here will be the percentage increase or decrease of the CURRENT PRICE.

Example 1:

Current Price = $100

Update Method = 2

Price Value = (10)

This is read by the enhancement as a 10% decrease in price. Therefore, the new price is 100 – (100 x 0.10) = $90
Status This field will display the current status of the line. It will get updated by the enhancement. Lines imported from Salesforce should be imported with a value of 0. The possible values are:

  • 0 – New Line. This line has been added to the staging table but not verified and/or ready to be pushed into the Extended Pricing Tables.
  • 1 – Ready to Process. This line has had its Price Sheet added and is ready to be added to the Extended Pricing Table.
  • 2 – This line has been successfully processed and Extended Pricing module has been updated to reflect the change.
  • 3 – This line did not pass all of the validations required to update the Extended Pricing module. Error may include:
  • Customer Number does not exist in GP
  • Price Sheet is not assigned to the Customer
  • Item Number is not assigned to the Price Sheet ID
  • Price Break does not exist
Error Displays the error message if the Status = 3

Price Change Utility

Navigation: Tools >> Utilities >> Inventory >> Price Change Utility

A screenshot of a computer screen Description automatically generated

Field Function
Update Prices Runs the update utility. All lines that pass the error checking will have their prices updated based on the logic selected for the line. If the line was marked to consolidate breaks, then all price breaks greater than the marked line will be deleted from the price sheet.

The enhancement will back up all data prior to making table changes, so if the outcome needs to be reversed, the data will be available. This estimate does not include any functionality to perform the restore if required.

After processing, the Status flag for the line in the table will be set = 2. After processing, lines will not be removed from the table. ACME will need to manually remove the lines.

Since the key on the table is Customer Number – Item Number – Qty To, future price changes for this combination WILL override older updates.

The Update Process will be written as a SQL stored procedure so that if ACME wants to automate the process in the future using a SQL job, it will be available.
VALIDATE Validates the lines. This is for the lines that may have been imported from Salesforce as the UI will validate on the fly. Any lines that do not pass the validation will be marked with a warning symbol and will not be processed.

Errors may include:

  • Customer Number does not exist in GP
  • Price Sheet is not assigned to the customer
  • Item Number is not assigned to the Price Sheet ID
  • Price Break does not exist

The Validation Process will be written as a SQL stored procedure so that if ACME wants to automate the process in the future using a SQL job, it will be available.
REDISPLAY Fetches new lines from the staging table if lines were added while the window was open.
X This field is not editable. It is only a visual indicator to indicate which line is currently selected.
Customer ID Enter or select from the lookup the Customer ID to update. If there are records already in the staging table, this field will be populated with that value. If a non-customer specific price sheet is being updated, leave this field blank.

After the customer is selected, the enhancement will attempt to locate the customer specific price sheet. The customer specific price sheet uses the logic described in the setup window. If one is found, it will automatically be populated.
Price Sheet ID Enter or select from the lookup the Price Sheet ID to update. If the Customer ID is blank, the lookup will display ALL Price Sheets, otherwise it will only display Price Sheets that are assigned to the Customer.

This field is required in order to use the Export, Import, and Find Items buttons.
EXPORT TO EXCEL Sends the contents of the scrolling window to Excel so that the Value can be updated.
IMPORT FROM EXCEL Imports lines from an Excel spreadsheet. Any lines that already exist in the scrolling window will be overridden with the import. The import file must be formatted with data beginning on Row 2 and columns as follows:

  1. A = Customer Number
  2. B = Price Sheet ID
  3. C = Item Number
  4. D = Qty To
  5. E = Update Method – 1 = Fixed Amount, 2 = Percent Change
  6. F = Value of the change
FIND ITEMS Opens the Find Items window. This is used to add items to the scrolling window. See Below.
Item Number Displays the Item Number that will be updated for the selected Price Sheet.
Qty From Displays the Qty From Price Break.
Qty To Displays the Qty To Price Break
Update Method Displays the method that will be used to calculate the new price. This will either be Fixed Amount or Percent Change. The Update Method is set when:

  1. The item is added to the staging table from Salesforce
  2. The item is selected using the Add Items window

The scrolling window could be a mix of Fixed Amount AND Percent Change update methods for a given Price Sheet.
Amount This field displays the amount of the change. It is displayed as a unitless value, however, depending on the Update Method:

  1. Fixed Amount – Value is expressed in dollars. This will be the NEW price of the item.
  2. Percent Changed – Value is expressed in percent. A value of 10 will cause a 10% increase in price. A value of – 10 will cause a 10% decrease in price.
Consolidation Breaks This checkbox is marked if all price breaks AFTER the currently marked break should be deleted from the price list. For example, if ABC-001 has 3 breaks:

1-10 = $10

11-50 = $8

51-99999 = $6

If the Break for 1-10 is added to the window with this check box marked with a $2 increase, the new Price Sheet will be:

1-99999 = $12

If the Break for 11-50 is added to the window with this checkbox marked with a $2 increase, the new Price Sheet will be:

1-10 = $10

11-99999 = $10

Find Items

A screenshot of a computer screen Description automatically generated

Field Function
DONE Closes the window and returns the Price Change Utility with the selected items loaded.
Customer ID Displays the Customer ID from the PS Utility window.
Pricesheet ID Displays the Pricesheet ID from the PS Utility window.
Find Items Where Field Dropdown options:

  • Item Number
  • Item Class

Conditions Dropdown Options:

  • Is equal to
  • Is not equal to
  • Is greater than
  • Is less than
  • Is greater than or equal to
  • Is less than or equal to
  • Contains
  • Does not contain
  • Begins with

Use the filters to build a selection set of items to add.
Display Choose to filter the added items to all items, only those active on the price sheet, or only inactive items.
ADD Adds the filter set chosen from above to the text field below the buttons.
REMOVE Removes a filter set from the text field below the buttons.
RUN QUERY Uses the filters to build a SQL query that will select items from the pricesheet ID to add to the PS Utility window.

Assumptions/Requirements

  1. The functionality described above will not work with eConnect, any software that uses eConnect, or any software that directly writes to, updates, or deletes from SQL tables.
  2. The functionality described above is intended for the GP Desktop client.
  3. National Accounts functionality is NOT used.
  4. Advanced Distribution functionality is NOT used.
  5. Unless otherwise noted in this document, reporting is not included in this estimate.
  6. Unless otherwise noted in this document, Word Template functionality is not addressed.
  7. Unless otherwise noted in this document, the enhancement will not integrate with 3rd party products. Some examples of 3rd party products would be:
  • An ISV plug-in product including WilloWare products
  • A dexterity customization designed by another developer
  • Dynamics GP Modules including, but not limited to:
  • Project Accounting
  • MDA
  • Analytical Accounting
  • Copy functionality found in SOP, POP and Inventory
  • Field Service
  • Manufacturing

For information on this design, or any other WilloWare customization or product, please contact us:

www.willoware.com/contact-me/