Microsoft Dynamics Blog

Welcome to Armanino’s Microsoft Dynamics Blog, where you’ll find information on topics across Microsoft Dynamics 365 ERP and CRM, Dynamics GP, Power BI, PowerApps, Flow and more. Use these tips, tricks and insights to get the most out of your applications! Want these articles delivered straight to your inbox? Subscribe to our newsletter below.

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’
Update BM010415
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:
*ITEMNMBR
*BOMCAT_I (which is labeled as BOM Type on the window)
*BOMNAME_I

BM010416 (BOM Header Hist) is:
*ITEMNMBR
*BOMCAT_I
*BOMNAME_I
*REVISIONLEVEL_I

BM010115 (BOM Lines) is:
*PPN_I
*BOMCAT_I
*BOMNAME_I
*CPN_I
*BOMSEQ_I

And in BM010116 (BOM Lines Hist) is:
*PPN_I
*BOMCAT_I
*BOMNAME_I
*CPN_I
*REVISIONLEVEL_I
*BOMSEQ_I

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)

Thanks again,

Chastidy

COMMENTS

comments powered by Disqus
« | »