Sometimes we want to add or remove Dynamics GP bills of material in bulk. The standard advice seems to be, use GP macros to help, but I haven’t run across any specific examples. So when I had to write something, I thought maybe someone else could also benefit by looking at what I wrote. Maybe someone will improve on it.

We wanted to merge a couple dozen pairs of part numbers, but all the part numbers have attached bills of material. I ended up writing a SQL procedure that will generate a GP macro to remove the bills of material, and another SQL procedure that will generate a GP macro to rebuild the bills of material.

This worked for us but your mileage may vary. Please test this on a dummy database if possible, and back up your data before trying this.

Assembling Things

Assembling Things

The procedure to generate the GP rebuild macro is longer but slightly simpler. We look at the BM00111 table in the Dynamics GP database, which contains the components for the bills of material.

Here’s the stored procedure.

[sql]
ALTER PROCEDURE GP_RebuildBOM
AS
BEGIN
SET NOCOUNT ON;

— First we’ll list all the assemblies we want to rebuild, plus all the components
declare @AssembliesToRebuild table(ITEMNMBR char(31));

insert into @AssembliesToRebuild
select distinct ITEMNMBR
from BM00111
where ITEMNMBR in (‘6-037-30075-EA’,’6-037-30075SP6-EA’); — These are the assemblies to rebuild

— The following relies on the these substitutions:
— __line__ is row_number() over (partition by ITEMNMBR order by ORD)
— __CMPTITNM__
— __Component_Status__
— __Component_Status_Description__ is
— case
— when Component_Status=1 then ‘Active’
— when Component_Status=2 then ‘Pending’
— when Component_Status=3 then ‘Obsolete’
— end
— __Cost_Type__
— __Cost_Type_Description__ is
— case
— when Cost_Type=1 then ‘Per Unit’
— when Cost_Type=2 then ‘Setup’
— end
— __Design_Qty_Entry__ is CAST(Design_Qty * 1E5 AS INTEGER)
— __Effective_Date_Entry__ is ” for now
— __ITEMNMBR__
— __Obsolete_Date_Entry__ is ” for now
— __Scrap_Percentage_Entry__ is ‘0’ for now

select *
from (

select
bom.ITEMNMBR,
-1 as ORD,
0 as SortOrder,
‘# DEXVERSION=11.00.0354.000 2 2’ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom

union

select
bom.ITEMNMBR,
-1 as ORD,
1 as SortOrder,
‘ CommandExec dictionary ”default” form ”Command_Inventory” command bmBillMaintenance’
from DataWarehouse.dbo.BM00111 bom

union

select
bom.ITEMNMBR,
-1 as ORD,
2 as SortOrder,
‘NewActiveWin dictionary ”default” form bmBillMaintenance window bmBillMaintenance ‘ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom

union

select
bom.ITEMNMBR,
-1 as ORD,
3 as SortOrder,
replace(‘ TypeTo field ”Item Number” , ”__ITEMNMBR__”’,’__ITEMNMBR__’,bom.ITEMNMBR) as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom

union

select
bom.ITEMNMBR,
-1 as ORD,
4 as SortOrder,
‘ MoveTo field ”Bill Status” item 0 ‘ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom

union

select
bom.ITEMNMBR,
-1 as ORD,
5 as SortOrder,
replace(replace(
‘ ClickHit field ”Bill Status” item __Bill_Status__ # ”__Bill_Status_Description__” ‘
,’__Bill_Status__’,cast(bom.Bill_Status as varchar(12)))
,’__Bill_Status_Description__’,
case bom.Bill_Status
when 1 then ‘Active’
when 2 then ‘Pending’
when 3 then ‘Obsolete’
end)
as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom

union

select
bom.ITEMNMBR,
999999999 as ORD,
101 as SortOrder,
‘ ScrollByLine down scrollwin bmBillMaintenanceCompEntry ‘ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom

union

select
bom.ITEMNMBR,
999999999 as ORD,
102 as SortOrder,
‘# Key 1: ‘ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom

union

select
bom.ITEMNMBR,
999999999 as ORD,
104 as SortOrder,
‘ MoveTo field ”Save Button” ‘ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom

union

select
bom.ITEMNMBR,
999999999 as ORD,
105 as SortOrder,
‘ ClickHit field ”Save Button” ‘ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom

union

select
bom.ITEMNMBR,
999999999 as ORD,
106 as SortOrder,
‘CloseWindow dictionary ”default” form bmBillMaintenance window bmBillMaintenance ‘ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom

union

select
bom.ITEMNMBR,
999999999 as ORD,
107 as SortOrder,
‘NewActiveWin dictionary ”default” form sheLL window sheLL ‘ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom

union

select
bom.ITEMNMBR,
bom.ORD,
cmp.SortOrder,
— Unless the format is changed, there should be one "replace" on the following line
— for every leading comma in the clause that follows
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
cmp.MacroLine
,’__line__’,bom.MacroLineNumber)
,’__ITEMNMBR__’, bom.ITEMNMBR)
,’__CMPTITNM__’, bom.CMPTITNM)
,’__Component_Status__’, cast(bom.Component_Status as varchar(12)))
,’__Component_Status_Description__’,
case bom.Component_Status
when 1 then ‘Active’
when 2 then ‘Pending’
when 3 then ‘Obsolete’
end)
,’__Cost_Type__’, cast(bom.Cost_Type as varchar(12)))
,’__Cost_Type_Description__’,
case bom.Cost_Type
when 1 then ‘Per Unit’
when 2 then ‘Setup’
end)
,’__Design_Qty_Entry__’,cast(cast(bom.Design_Qty as integer) as varchar(12)))
,’__Effective_Date_Entry__’,”)
,’__Obsolete_Date_Entry__’,”)
,’__Scrap_Percentage_Entry__’,cast(bom.Scrap_Percentage as varchar(12)))
as FinalMacroLine

from (
select ‘# Key 1: ‘ as MacroLine, 0 as SortOrder union
select ‘ MoveTo line __line__ scrollwin bmBillMaintenanceCompEntry field ”Component Item Number”’, 1 union
select ‘# Key 1: ””, ”0”, ”0”, ”0”’, 2 union
select ‘ TypeTo line __line__ scrollwin bmBillMaintenanceCompEntry field ”Component Item Number” , ”__CMPTITNM__”’, 3 union
select ‘# Key 1: ””, ”0”, ”0”, ”0”’, 4 union
select ‘ MoveTo line __line__ scrollwin bmBillMaintenanceCompEntry field ”Component Status” item __Component_Status__ # ”__Component_Status_Description__” ‘, 5 union
select ‘# Key 1: ””, ”0”, ”0”, ”0”’, 6 union
select ‘ ClickHit line __line__ scrollwin bmBillMaintenanceCompEntry field ”Component Status” item __Component_Status__ # ”__Component_Status_Description__” ‘, 7 union
select ‘# Key 1: ””, ”0”, ”0”, ”0”’, 8 union
select ‘ MoveTo line __line__ scrollwin bmBillMaintenanceCompEntry field ”Cost Type” item __Cost_Type__ # ”__Cost_Type_Description__” ‘, 9 union
select ‘# Key 1: ””, ”0”, ”0”, ”0”’, 10 union
select ‘ ClickHit line __line__ scrollwin bmBillMaintenanceCompEntry field ”Cost Type” item __Cost_Type__ # ”__Cost_Type_Description__” ‘, 11 union
select ‘# Key 1: ””, ”0”, ”0”, ”0”’, 12 union
select ‘ MoveTo line __line__ scrollwin bmBillMaintenanceCompEntry field ”Design Qty” ‘, 13 union
select ‘# Key 1: ””, ”0”, ”0”, ”0”’, 14 union
select ‘ TypeTo line __line__ scrollwin bmBillMaintenanceCompEntry field ”Design Qty” , ”__Design_Qty_Entry__”’, 15 union
select ‘# Key 1: ””, ”0”, ”0”, ”0”’, 16 union
select ‘ MoveTo line __line__ scrollwin bmBillMaintenanceCompEntry field ”Effective Date” ‘, 17 union
select ‘# Key 1: ””, ”0”, ”0”, ”0”’, 18 union
select ‘ TypeTo line __line__ scrollwin bmBillMaintenanceCompEntry field ”Effective Date” , ”__Effective_Date_Entry__”’, 19 union
select ‘# Key 1: ””, ”0”, ”0”, ”0”’, 20 union
select ‘ MoveTo line __line__ scrollwin bmBillMaintenanceCompEntry field ”Obsolete Date” ‘, 21 union
select ‘# Key 1: ””, ”0”, ”0”, ”0”’, 22 union
select ‘ TypeTo line __line__ scrollwin bmBillMaintenanceCompEntry field ”Obsolete Date” , ”__Obsolete_Date_Entry__”’, 23 union
select ‘# Key 1: ””, ”0”, ”0”, ”0”’, 24 union
select ‘ MoveTo line __line__ scrollwin bmBillMaintenanceCompEntry field ”Scrap Percentage” ‘, 25 union
select ‘# Key 1: ””, ”0”, ”0”, ”0”’, 26 union
select ‘ TypeTo line __line__ scrollwin bmBillMaintenanceCompEntry field ”Scrap Percentage” , ”__Scrap_Percentage_Entry__”’, 27 union
select ‘ TransLinePrepare scrollwin bmBillMaintenanceCompEntry ‘, 28
) cmp
cross join (
select
*,cast(row_number() over (partition by ITEMNMBR order by ORD) as varchar(12)) as MacroLineNumber
from DataWarehouse.dbo.BM00111
) bom

) boms

where boms.ITEMNMBR in (select ITEMNMBR from @AssembliesToRebuild)

order by
boms.ITEMNMBR,
boms.ORD,
boms.SortOrder
;

END;
[/sql]

This generates a result set with 4 columns. The fourth column is FinalMacroLine. Each element of this column is a line in the GP macro.

The generated macro looks like this.

[shell]
# DEXVERSION=11.00.0354.000 2 2
CommandExec dictionary ‘default’ form ‘Command_Inventory’ command bmBillMaintenance
NewActiveWin dictionary ‘default’ form bmBillMaintenance window bmBillMaintenance
TypeTo field ‘Item Number’ , ‘6-037-30075-EA ‘
MoveTo field ‘Bill Status’ item 0
ClickHit field ‘Bill Status’ item 1 # ‘Active’
# Key 1:
MoveTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Component Item Number’
# Key 1: ”, ‘0’, ‘0’, ‘0’
TypeTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Component Item Number’ , ‘.120M ‘
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Component Status’ item 1 # ‘Active’
# Key 1: ”, ‘0’, ‘0’, ‘0’
ClickHit line 1 scrollwin bmBillMaintenanceCompEntry field ‘Component Status’ item 1 # ‘Active’
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Cost Type’ item 1 # ‘Per Unit’
# Key 1: ”, ‘0’, ‘0’, ‘0’
ClickHit line 1 scrollwin bmBillMaintenanceCompEntry field ‘Cost Type’ item 1 # ‘Per Unit’
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Design Qty’
# Key 1: ”, ‘0’, ‘0’, ‘0’
TypeTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Design Qty’ , ’20’
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Effective Date’
# Key 1: ”, ‘0’, ‘0’, ‘0’
TypeTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Effective Date’ , ”
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Obsolete Date’
# Key 1: ”, ‘0’, ‘0’, ‘0’
TypeTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Obsolete Date’ , ”
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Scrap Percentage’
# Key 1: ”, ‘0’, ‘0’, ‘0’
TypeTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Scrap Percentage’ , ‘0’
TransLinePrepare scrollwin bmBillMaintenanceCompEntry
ScrollByLine down scrollwin bmBillMaintenanceCompEntry
# Key 1:
MoveTo field ‘Save Button’
ClickHit field ‘Save Button’
CloseWindow dictionary ‘default’ form bmBillMaintenance window bmBillMaintenance
NewActiveWin dictionary ‘default’ form sheLL window sheLL
# DEXVERSION=11.00.0354.000 2 2
CommandExec dictionary ‘default’ form ‘Command_Inventory’ command bmBillMaintenance
NewActiveWin dictionary ‘default’ form bmBillMaintenance window bmBillMaintenance
TypeTo field ‘Item Number’ , ‘6-037-30075SP6-EA ‘
MoveTo field ‘Bill Status’ item 0
ClickHit field ‘Bill Status’ item 1 # ‘Active’
# Key 1:
MoveTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Component Item Number’
# Key 1: ”, ‘0’, ‘0’, ‘0’
TypeTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Component Item Number’ , ‘6-037-30075-EA ‘
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Component Status’ item 1 # ‘Active’
# Key 1: ”, ‘0’, ‘0’, ‘0’
ClickHit line 1 scrollwin bmBillMaintenanceCompEntry field ‘Component Status’ item 1 # ‘Active’
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Cost Type’ item 1 # ‘Per Unit’
# Key 1: ”, ‘0’, ‘0’, ‘0’
ClickHit line 1 scrollwin bmBillMaintenanceCompEntry field ‘Cost Type’ item 1 # ‘Per Unit’
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Design Qty’
# Key 1: ”, ‘0’, ‘0’, ‘0’
TypeTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Design Qty’ , ‘1’
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Effective Date’
# Key 1: ”, ‘0’, ‘0’, ‘0’
TypeTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Effective Date’ , ”
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Obsolete Date’
# Key 1: ”, ‘0’, ‘0’, ‘0’
TypeTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Obsolete Date’ , ”
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Scrap Percentage’
# Key 1: ”, ‘0’, ‘0’, ‘0’
TypeTo line 1 scrollwin bmBillMaintenanceCompEntry field ‘Scrap Percentage’ , ‘0’
TransLinePrepare scrollwin bmBillMaintenanceCompEntry
# Key 1:
MoveTo line 2 scrollwin bmBillMaintenanceCompEntry field ‘Component Item Number’
# Key 1: ”, ‘0’, ‘0’, ‘0’
TypeTo line 2 scrollwin bmBillMaintenanceCompEntry field ‘Component Item Number’ , ‘E4001 ‘
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 2 scrollwin bmBillMaintenanceCompEntry field ‘Component Status’ item 1 # ‘Active’
# Key 1: ”, ‘0’, ‘0’, ‘0’
ClickHit line 2 scrollwin bmBillMaintenanceCompEntry field ‘Component Status’ item 1 # ‘Active’
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 2 scrollwin bmBillMaintenanceCompEntry field ‘Cost Type’ item 1 # ‘Per Unit’
# Key 1: ”, ‘0’, ‘0’, ‘0’
ClickHit line 2 scrollwin bmBillMaintenanceCompEntry field ‘Cost Type’ item 1 # ‘Per Unit’
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 2 scrollwin bmBillMaintenanceCompEntry field ‘Design Qty’
# Key 1: ”, ‘0’, ‘0’, ‘0’
TypeTo line 2 scrollwin bmBillMaintenanceCompEntry field ‘Design Qty’ , ‘1’
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 2 scrollwin bmBillMaintenanceCompEntry field ‘Effective Date’
# Key 1: ”, ‘0’, ‘0’, ‘0’
TypeTo line 2 scrollwin bmBillMaintenanceCompEntry field ‘Effective Date’ , ”
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 2 scrollwin bmBillMaintenanceCompEntry field ‘Obsolete Date’
# Key 1: ”, ‘0’, ‘0’, ‘0’
TypeTo line 2 scrollwin bmBillMaintenanceCompEntry field ‘Obsolete Date’ , ”
# Key 1: ”, ‘0’, ‘0’, ‘0’
MoveTo line 2 scrollwin bmBillMaintenanceCompEntry field ‘Scrap Percentage’
# Key 1: ”, ‘0’, ‘0’, ‘0’
TypeTo line 2 scrollwin bmBillMaintenanceCompEntry field ‘Scrap Percentage’ , ‘0’
TransLinePrepare scrollwin bmBillMaintenanceCompEntry
ScrollByLine down scrollwin bmBillMaintenanceCompEntry
# Key 1:
MoveTo field ‘Save Button’
ClickHit field ‘Save Button’
CloseWindow dictionary ‘default’ form bmBillMaintenance window bmBillMaintenance
NewActiveWin dictionary ‘default’ form sheLL window sheLL
[/shell]

Just run this macro to rebuild the bills of material.

In a future posting we’ll look at the logic to remove bills of material. Hope this is helpful!

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available