CASE Statement for BOMCAT_I in Dynamics GP Manufacturing BOM Revision Table BM010415

I have amassed a number of useful CASE statements, which can be incorporated into SQL queries run against Dynamics GP. When Querying the BOMCAT_I Field in Dynamics GP Manufacturing BOM Revision Table [BM010415].[BOMCAT_I] table: use the following case statement to return text versions of the BOM Categories in Dynamics GP:

select

CASE BM010415.BOMCAT_I

      WHEN 1 THEN 'MFG'

      WHEN 2 THEN 'ENG'

      WHEN 3 THEN 'ARCH'

      WHEN 4 THEN 'CONFIG'

      WHEN 5 THEN 'SUPER'

      ELSE 'ERROR'

END BOMCAT_I_TEXT,

Printing BOM Notes on Reports

How can we insert brief instructions or comments (i.e. Turn cap 5 times, do not over-tighten) into our Bill of Materials?  Rather than just printing an assembly with all the components necessary to build an item, we want to add a few comments to make the items consistent across all assemblers. 

Possibly the easiest way to accomplish such a task would be to add a note to the Bill of Material in the Bill of Material Entry window. There is quite a bit of room in the Notes field for instructions. 

Getting the notes to print on the desired report can be a little perplexing because the link is a bit convoluted. The notes text is stored in the Manufacturing Notes table [MN010000]. The Bill of Material Master Record is stored in the Bill of Material Header table [BM010415]. The Note Index is stored in the Item Engineering Data [IVR10015] able, so there can only be one Note for all Bill of Material (regardless of Category).

Here is a query to connect the data:

select

BH.ITEMNMBR Item#,

IM.ITEMDESC Item_Description,

MNF.NOTETEXT_I BOM_Note

from BM010415 BH 

-- BH is BOM Header BM010415

Left Join IVR10015 IED on BH.ITEMNMBR = IED.ITEMNMBR 

-- IED is Item Engineering Data IVR10015

Left Join MN010000 MNF on IED.MFGNOTEINDEX_I = MNF.NOTEINDX 

-- MNF is Manufacturing Note File MN010000

Left Join IV00101 IM on BH.ITEMNMBR = IM.ITEMNMBR 

-- IM is Item Master IV00101

Where BH.ITEMNMBR = '100XLGA' and BH.BOMCAT_I = 1 

-- BOMCAT_I = 1 is Manufacturing BOM

/* BOM Notes are linked to the BOM record through the Item Engineering Data Table (IVR10015)

The MFGNOTEINDEX_I field value maps to the NOTEINDX field in the Manufacturing Notes File MN010000*/