SQL Script – Creating Price Level Based On Existing Price Level
In the current economic times, collections have become a real burden for our company. We are fortunate to have very tight credit control but there are still the customers who always take FOREVER to collect from. We have recently decided to start charging these customers a 10% ‘premium’, over what they would normally pay, to cover our collections cost that we assume we are going to incur. The method we chose to take was to:
- Create a new Price Level for these customers
- Copy the normal Price Level to this new Price Level
- Add 10% to the new Price Level
- Change the customer’s Price Level from the old to the new
I figured with Price List Utilities (PLU), this would be a no brainer…I was quite wrong. The creation of the new Price Level is easy using PLU, but copying another price level and adding the 10% was where I ran into the problem. It just didn’t seem like it could be done using PLU, so I went the SQL route.
This script assumes you already have a new Price Level, let’s call that PREMIUM, created for your items that you want to charge the premium on and that the percentage is set to 0% for the PREMIUM Price Level (which means you would be selling the item at cost). Let’s call the Price Level we are going to copy from the STANDARD Price Level.
Widget A has an STANDARD Price Level of 30% for STANDARD Price Level customers. We want to create a PREMIUM Price Level and copy the 30% + 10% (total of 40%) to the PREMIUM Price Level and charge that 40% to customers set to the PREMIUM Price Level.
Once you have the PREMIUM Price Level created and assigned to your items at 0% Mark Up/Margin then we are ready to run the script. After running the script, update your customer’s Price Level to PREMIUM in GP and you are now charging a premium to those customers.
This script has code that will update data in the Dynamics GP database. While this is not “illegal” it may or may not be supported by Microsoft. I have not had any issues with this in our environment, but make sure you test this in your non production environment first, before deploying it to your production environment.
The USE [TRAIN] tells the script to only execute on my database named TRAIN. You will need to modify the [TRAIN] to be whatever your database name is.
I would recommend running this in your test environment first…
The SET @premium and SET @standard statements will allow you to replace PREMIUM and STANDARD Price Levels with the names of the Price Levels you actually use.
The SET @percent allows you to define the premium precentage that you want to add to the customer.
Just replace the text between the ‘ ‘ and this script will work for your company.
DECLARE @premium VARCHAR(15)
DECLARE @standard VARCHAR(15)
DECLARE @percent VARCHAR(2)
set a.uomprice = b.uomprice + @percent
from iv00108 a
inner join iv00108 b on a.itemnmbr = b.itemnmbr
where a.prclevel = @premium and b.prclevel = @standard