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.

***End Warning***

We had an issue just recently where one of our Customer Service folks quoted a price on an item that was several years outdated.  The pricing was outdated in our system because we had not sold or purchased the particular item in over 2 years.  We have over 60,000 SKUs in our system, and it is quite a beast to try and make sure they are all up to date price-wise, so our rule of thumb is that if it is in stock or purchased recently, pricing will be accurate. 

To try and eliminate this from happening again, I decided to update all of our outdated pricing to show a current and standard cost of $0.00.  This will force our reps to look up new pricing from their price books.  Our algorithm to identify outdated pricing is the following:

If stock quantity is 0 (across all locations) and the item has not been purchased in more than 3 months, then the pricing is outdated.

The 3 months just makes sure that we have enough time to get products, that we may have unintentional stock outs on, back on our shelf before changing the pricing to $0.00.

I use Task Centre (link below) to run the following update statement every night.  This statement analyzes all of our items, to first see if we have them in stock.  If not, then we move on and check to make sure the item has not been purchase within the last 3 months.  Finally, we make sure that we haven’t already changed the item’s price to $0.00 (this is for performance only) already, and then we update the item’s current and standard cost to $0.00.

Here is the script:

UPDATE  dbo.IV00101
SET     STNDCOST = ‘0.00′,
        CURRCOST = ‘0.00′
FROM    dbo.tspvInventory a
        JOIN dbo.tspvItemMaster b ON a.Item_Number = b.Item_Number
WHERE   Avail <= ‘0′
        AND a.Item_Number NOT IN ( SELECT   Item_Number
                                   FROM     dbo.tspvPurchaseLineItem
                                   WHERE    Required_Date > GETDATE() – 90 )
        AND ( b.Standard_Cost <> ‘0.00′
              AND Current_Cost <> ‘0.00′

This script will not update LIST PRICE.  It will only update current and standard cost.  If you want to update LIST PRICE, that can be found in the IV00105 table and will need to be JOINED with this statement.

Views needed for this script:

- tspvInventory

- tspvItemMaster

- tspvPurchaseOrderLineItem

Tools Used To Automate Script

- Task Centre (however you could just use SQL’s job agent)