Changing Revision Levels On Manufacturing BOM in Dynamics GP

 

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

3 Responses to “Changing Revision Levels On Manufacturing BOM in Dynamics GP”

  • Doug:

    You sure look like a manufacturing expect. I’ll pass all manufacturing calls over to you from now on.

  • Hi Doug-

    I would like to add something to this post. 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.

    As a side note, the need to execute this type of ad hoc script comes up pretty often with Manufacturing. We have a 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/

    Regards,
    Brenner

Leave a Reply

*

Subscription Options:
Subscribe via RSS
Articles Categories