A previous post gave a stored procedure that will generate a GP macro to rebuild some or all of the bills of material in a GP database.

The stored procedure in this post will generate a GP macro to remove some or all bills of material. The parts will remain but they’ll no longer be linked to a bill 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 idea is, we needed to eliminate, then rebuild, some bills of material, so that we could merge the parts to which the bills were linked. So we wrote stored procedures in SQL Server to generate Dynamics GP scripts that would do what we needed.

Here’s the stored procedure to generate a script to remove the bills. The only trickiness is, there’s an extra prompt if the bill of material being deleted is used as a component in another bill of material. The stored procedure checks for that situation and adds the extra responses when needed.

We were using GP 2010 when we wrote these.

[sql]
ALTER PROCEDURE [dbo].[GP_RemoveBOM]
AS
BEGIN
SET NOCOUNT ON;

— First we’ll list all the assemblies we want to delete, plus all the components
declare @AssembliesToDelete table(ITEMNMBR char(31), ExpectComponentPrompt bit);

insert into @AssembliesToDelete
select distinct ITEMNMBR, 0
from BM00111
where ITEMNMBR in (‘6-037-30075-EA’,’6-037-30075SP6-EA’,’6-037-30075SP8-EA’);

— Now we’ll decide which of these assemblies will be components in another assembly when we try to delete them,
— assuming we delete the listed assemblies in order by ITEMNMBR
update @AssembliesToDelete
set ExpectComponentPrompt = 1
where exists (
select CMPTITNM
from BM00111 x
where x.CMPTITNM=[@AssembliesToDelete].ITEMNMBR
and (x.ITEMNMBR > [@AssembliesToDelete].ITEMNMBR
or x.ITEMNMBR not in (select ITEMNMBR from @AssembliesToDelete))
)
;

— Now we’ll generate the macro
select *
from (

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

union

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

union

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

union

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

union

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

union

select
bom.ITEMNMBR,
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,
6 as SortOrder,
‘ MoveTo field ”Delete Button” ‘ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom

union

select
bom.ITEMNMBR,
7 as SortOrder,
‘ ClickHit field ”Delete Button” ‘ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom

union

select
bom.ITEMNMBR,
8 as SortOrder,
‘# Are you sure you want to delete this bill of materials?’ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom

union

select
bom.ITEMNMBR,
9 as SortOrder,
‘NewActiveWin dictionary ”default” form DiaLog window DiaLog ‘ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom

union

select
bom.ITEMNMBR,
10 as SortOrder,
‘ ClickHit field OK ‘ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom

union

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

union

select
bom.ITEMNMBR,
12 as SortOrder,
‘# This bill of materials is a component of another bill of materials. Future assemblies of the other item must use in-stock quantities for this component. Are you sure you want to delete this bill of materials?’ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom
where bom.ITEMNMBR in (select ITEMNMBR from @AssembliesToDelete where ExpectComponentPrompt<>0)

union

select
bom.ITEMNMBR,
13 as SortOrder,
‘NewActiveWin dictionary ”default” form DiaLog window DiaLog ‘ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom
where bom.ITEMNMBR in (select ITEMNMBR from @AssembliesToDelete where ExpectComponentPrompt<>0)

union

select
bom.ITEMNMBR,
14 as SortOrder,
‘ ClickHit field OK ‘ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom
where bom.ITEMNMBR in (select ITEMNMBR from @AssembliesToDelete where ExpectComponentPrompt<>0)

union

select
bom.ITEMNMBR,
15 as SortOrder,
‘NewActiveWin dictionary ”default” form bmBillMaintenance window bmBillMaintenance ‘ as FinalMacroLine
from DataWarehouse.dbo.BM00111 bom
where bom.ITEMNMBR in (select ITEMNMBR from @AssembliesToDelete where ExpectComponentPrompt<>0)

union

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

union

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

) boms

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

order by
boms.ITEMNMBR,
boms.SortOrder
;
[/sql]

This procedure generates a result set with 2 columns. The second column, FinalMacroLine, has one row for every line in the GP macro. Just copy that column and paste it into NOtepad or some other editor, check it over, save it, then execute it from GP.

The macro looks something 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’
MoveTo field ‘Delete Button’
ClickHit field ‘Delete Button’
# Are you sure you want to delete this bill of materials?
NewActiveWin dictionary ‘default’ form DiaLog window DiaLog
ClickHit field OK
NewActiveWin dictionary ‘default’ form bmBillMaintenance window bmBillMaintenance
# This bill of materials is a component of another bill of materials. Future assemblies of the other item must use in-stock quantities for this component. Are you sure you want to delete this bill of materials?
NewActiveWin dictionary ‘default’ form DiaLog window DiaLog
ClickHit field OK
NewActiveWin dictionary ‘default’ form bmBillMaintenance window bmBillMaintenance
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’
MoveTo field ‘Delete Button’
ClickHit field ‘Delete Button’
# Are you sure you want to delete this bill of materials?
NewActiveWin dictionary ‘default’ form DiaLog window DiaLog
ClickHit field OK
NewActiveWin dictionary ‘default’ form bmBillMaintenance window bmBillMaintenance
CloseWindow dictionary ‘default’ form bmBillMaintenance window bmBillMaintenance
NewActiveWin dictionary ‘default’ form sheLL window sheLL
[/shell]

That’s all there is to it. Hope this is helpful.

Leave a Reply

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

 characters available