Wednesday, June 19, 2013
Changing Revision Levels On Manufacturing BOM in Dynamics GP
Posted by Armanino Dynamics Team
Recently we had a client who accidentally changed their revision level on their Manufacturing BOM. They did not want to GP to show that they were on their 7th revision of the item because they were really on their 5th and just didn’t know how to use the BOM correctly in GP. They contacted us at Rose and asked us to fix it. Not knowing much about Manufacturing I searched all the usual areas for a way to change the revision level with no success. I don’t believe there is a way to “go back” within GP. I decided to fix on the back end. The two tables I used were BM010415 for BOM Revision and BM 010416 for BOM Revision History. On BM010415 I ran an UPDATE sql script on SSMS to change the REVISIONLEVEL_I column to the revision level I wanted, in my case this was 5. On BM010416 I ran a DELETE sql script to remove the two unwanted lines, again from the REVISIONLEVEL_I column, so in my case I deleted revision level 5 and 6 from the history table. This seemed to fix their issue and they have been happy ever since.
Here are the scripts I ran,
To update current table BM010415:
Select * from BM010415 where ITEMNMBR=’xxx’
Set REVISIONLEVEL_I=’xxx’ where REVISIONLEVEL_I=’xxx’
To update history table BM010416:
Select * from BM010416 where ITEMNMBR=’xxx’
Delete from BM010416 where REVISIONLEVEL_I=’xxx’
This next portion was graciously shared by Brenner Klenzman at Willoware.com
“The key on BM010415 (BOM Header) is:
*BOMCAT_I (which is labeled as BOM Type on the window)
BM010416 (BOM Header Hist) is:
BM010115 (BOM Lines) is:
And in BM010116 (BOM Lines Hist) is:
The script above is missing BM010116. There should also be a delete script as follows:
delete BM010116 where
PPN_I = ‘xxx’ and
BOMCAT_I = 1 and
BOMNAME_I = ”
Also, it should be restricting on BOMCAT_I and BOMNAME_I. For the client in question they probably had only BOM’s using BOMCAT_I = 1 (which is MFG BOM). However, for other people wishing to use the script above, they should consider further restricting it using BOMCAT_I and BOMNAME_I to ensure only the desired BOM is updated.”
Thanks Brenner for the input. We do not want anyone out there wrecking havoc on their data!
And because Brenner was gracious enough to share with the community I wanted to give Willoware a shameless plug. Please read below.
Willoware has module for GP called Stored Queries which allows you (the consultant) to create a query like the one above that can be executed by an end user from inside GP. The user is prompted to enter parameters (such as an Item Number), but they have no ability to edit the actual script. Take a look at Stored Queries in our Consulting Toolkit: http://willoware.com/products/consulting-toolkit/
(Brenner’s full comment is below)
We enable an organization by supporting each stage of your company’s growth by implementing scalable Microsoft Dynamics solutions. As Microsoft Certified solutions experts and Inner Circle Partners, we work to eliminate redundant systems and leverage existing software investments, while keeping your goals for the future in mind.