Wednesday, June 24, 2015

Resetting Pending Standard Cost Changes in Dynamics GP Manufacturing.

Awhile back, a dynamics community post posed an interesting question:

Running 2013 SP2 with Manufacturing.  In process of setting up to use Standard Costing updating after it not being maintained in 4 years.
Issue is we have thousands of pending changes in table ICIV0323 that need to be cleared out. Additionally, many have an Override set. I understand the interaction with CT00003 and the 3 windows controlling entries of pending, proposed, and override updates.
Not sure of proper fields to clear in ICIV0323 and what is the relationship between the various effective dates and the current cost effective date field "_1".
Here is a link to the post:

I have since seen a number of posts regarding this same issue, so a blog post seemed like a good idea. So, let's get down to it. There are two tables, CT00003 and ICIV0323 in Dynamics GP, which contain pending standard cost changes. 
The Standard Cost Maintenance Window displays data from the ICIV0323 table. See image one below.
Image 1 - Standard Cost Maintenance Window
The Standard Cost Changes Window displays data from the CT00003 table. See image two below.
Image 2 - Standard Cost Changes Window
Attached is an image, which shows the relationships of the windows above to the tables, which display their data. See image three below.
Image 3 - SQL Query on CT00003 and ICIV0323 Tables
If you were to take the following Knowledge Base article at its word, you would believe clearing pending standard costs was a simple matter.
Part of the article is correct, in that table CT00003 can be quickly cleaned up by clicking on the Delete All button, which will confirm you want to delete all pending standard cost changes (just click yes). 
On the other hand table ICIV0323 cannot be rapidly cleaned up. If you write a macro for the window, you can check the Override boxes for any pending standard cost tables, and replace them with the original values, and the same effective date, and you will Override these recommended changes when you Roll and Revalue. See image four below.

There have been multiple reports of users rebuilding standard costs using SQL. Likely, folks rebuilding standard costs, are updating the MATCOSTI_# fields with 0.00000 and MATCOSTEFFDATEI_# fields with 1900-01-01 00:00:00.000. 
Since it is possible to use a Macro in the Standard Cost Maintenance window, I am reluctant to recommend someone use a SQL script to rebuild pending standard costs (reset to zero) in the ICIV0323 table.

In the end, the choice is up to you; however, should you proceed with either approach to the problem at hand, the first step would be the same. This is as ever, make a back up! The second step is to refresh your test company and test the fix there, prior to implementing it in your live database.

I believe Microsoft might also be of assistance, should you desire to pursue a fix using SQL, based on some of the posts I have reviewed online.

No comments:

Post a Comment