Create BOM levels
The Bill-Of-Materials (BOM) is a detailed list that consists of all the component parts with the correct quantity required to produce the end product. In using the BOM, understanding the list of components it takes to produce the end goods, will assist the user buying the right amount of stock and keep track of the inventory management.
The trickiest part of implementing BOM into an AGR system is creating levels between BOM.
Out of Navision, the BOM table gives the data structured as:
| Parent item no | Child item no | Qty used in recipe |
|---|---|---|
| 1 | 4 | 0.5 |
| 2 | 5 | 0.4 |
| 3 | 4 | 0.5 |
| 3 | 6 | 0.5 |
| 4 | 9 | 0.7 |
| 4 | 10 | 0.8 |
Item 1 is made from item 4, and item 4 is made from items 9 and 10. That is a three level BOM, and AGR needs to have the information available in levels.
Step 1
Before implementing the BOM levels make sure to have the table rep.bom_components and the procedure rep.bom_components_stg_to_ready on STG. If not please add it by running this SQL query:
Rep.bom_components
USE [agr_stg]
GO
/****** Object: Table [rep].[bom_components] Script Date: 5.2.2021 10:06:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [rep].[bom_components](
[id] [int] IDENTITY(1,1) NOT NULL,
[item_id_component] [int] NOT NULL,
[item_id_product] [int] NOT NULL,
[qty_in_product] [decimal](18, 4) NOT NULL,
[level] [int] NOT NULL,
[created_at] [datetime] NOT NULL,
[updated_at] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
--rep.bom_components_stg_to_ready
USE [agr_stg]
GO
/****** Object: StoredProcedure [rep].[bom_components_stg_to_ready] Script Date: 5.2.2021 10:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Rep.bom_components_stg_to_ready
-- ===============================================================================
-- Author: Arndis Yr Hafthorsdottir
-- Create date: 25/08/2020
-- Description: Transfer the bom_components view to table
--
-- 25/08/2020.AYH Created
-- ===============================================================================
CREATE PROCEDURE [rep].[bom_components_stg_to_ready]
(
@lId as int
)
AS
BEGIN
EXEC log.log_transfer @log_id = @lId, @proc_id = @@PROCID
SET NOCOUNT ON;
Begin try
MERGE rep.[bom_components] AS TARGET
USING (
SELECT
[item_id_component]
,[item_id_product]
,[qty_in_product]
,[level]
,[created_at]
,[updated_at]
FROM [rep].[v_bom_components]
) AS SOURCE
ON (
TARGET.[item_id_component] = SOURCE.[item_id_component] AND
TARGET.[item_id_product] = SOURCE.[item_id_product]
)
WHEN MATCHED AND
TARGET.[qty_in_product] <> SOURCE.[qty_in_product] OR
TARGET.[level] <> SOURCE.[level]
THEN
UPDATE SET
TARGET.[qty_in_product] = SOURCE.[qty_in_product],
TARGET.[level] = SOURCE.[level],
TARGET.[updated_at] = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[item_id_component], [item_id_product], [qty_in_product], [level], [created_at], [updated_at]
)
VALUES (
SOURCE.[item_id_component], SOURCE.[item_id_product], SOURCE.[qty_in_product], SOURCE.[level], GETDATE(), GETDATE()
)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
END TRY
BEGIN CATCH
EXEC log.log_error @lId;
END CATCH;
EXEC log.log_transfer_step_duration @log_id = @lId, @proc_id = @@PROCID
END
Step 2
Get the data from BOMComponents table in Navision by filling in the table [ctr].[tables_erp_to_stg] and running [erp].[generate_erp_datatransfer]. That will create the table [nav].[BOMComponents], the procedure [nav].[BOMComponent_Nav_To_Stg] and the synonym [erp_nav].[BOMComponent].
Step 3
Create [rep].[v_bom_components] on STG.
Item_id_Component is the same as Child and item_id_parent is the same as Parent in the explanation above.
SQL Query:
USE [agr_stg]
GO
/****** Object: View [rep].[v_bom_components] Script Date: 28.10.2020 10:38:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Arndis Yr
-- Create date: 25.08.2020
-- Description: PLACEHOLDER VIEW FOR v_bom_components
-- =============================================
CREATE VIEW [rep].[v_bom_components]
AS
SELECT icomp.id AS [item_id_component]
,iproduct.id AS [item_id_product]
,bom.[Quantity per] * iuom.[Qty_ per Unit of Measure] AS [qty_in_product]
,bom.type AS [level]
,GETDATE() AS [created_at]
,GETDATE() AS [updated_at]
FROM nav.bomcomponent bom
INNER JOIN nav.ItemUnitOfMeasure iuom ON iuom. [Item No_]=bom.[No_] AND iuom.code=bom.[Unit of Measure Code]
INNER JOIN rep.items iproduct ON iproduct.item_no=bom.[Parent Item No_]
INNER JOIN rep.items icomp ON icomp.item_no=bom.[No_] AND icomp.location_id=iproduct.location_id
WHERE iproduct.active=1 AND icomp.active=1
GO
Step 4
Create synonym for BOM Components table that are already in PROD:
SQL Query:
USE [agr_stg]
GO
/****** Object: Synonym [prod].[bom_components] Script Date: 28.10.2020 10:48:31 ******/
CREATE SYNONYM [prod].[bom_components] FOR [agr5_prod].[dbo].[bom_components]
GO
Step 5
Create procedure that moves the BOM_compnents data from STG to PROD
SQL Query:
USE [agr_stg]
GO
/****** Object: StoredProcedure [stg_prod].[bom_components_ready_to_prod] Script Date: 28.10.2020 10:45:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===============================================================================
-- Author:Arndis Yr Hafthorsdottir
-- Create date: 25/08/2020
-- Description:Transfer the bom_components view from stg to prod
--
-- 25/08/2020.AYH Created
-- ===============================================================================
ALTER PROCEDURE [stg_prod].[bom_components_ready_to_prod]
(
@lId as int
)
AS
BEGIN
EXEC log.log_transfer @log_id = @lId, @proc_id = @@PROCID
SET NOCOUNT ON;
Begin try
MERGE [prod].[bom_components] AS TARGET
USING (
SELECT
[id]
, [item_id_component]
,[item_id_product]
,[qty_in_product]
,[level]
,[created_at]
,[updated_at]
FROM [rep].[bom_components]
) AS SOURCE
ON (
TARGET.[id] = SOURCE.[id]
)
WHEN MATCHED AND
TARGET.[item_id_component] <>SOURCE.[item_id_component] OR
TARGET.[item_id_product] <>SOURCE.[item_id_product] OR
TARGET.[qty_in_product] <> SOURCE.[qty_in_product] OR
TARGET.[level] <> SOURCE.[level]
THEN
UPDATE SET
TARGET.[qty_in_product] = SOURCE.[qty_in_product],
TARGET.[level] = SOURCE.[level],
TARGET.[updated_at] = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[id], [item_id_component], [item_id_product], [qty_in_product], [level], [created_at], [updated_at]
)
VALUES (
SOURCE.[id], SOURCE.[item_id_component], SOURCE.[item_id_product], SOURCE.[qty_in_product], SOURCE.[level], GETDATE(), GETDATE()
)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
END TRY
BEGIN CATCH
EXEC log.log_error @lId;
END CATCH;
EXEC log.log_transfer_step_duration @log_id = @lId, @proc_id = @@PROCID
END
Step 6
The procedure [dbo].[demand_processing_calc_run] is calculating the BOM demand and the output goes to dbo.bom_demand table. Go into data_element table and add ISNULL([bom_demand],0) to the value field_calculated.
Step 7 - Optional
By adding the demand data serie to the chart will make the user understand more what the actual demand is because if demand contains both forecast and BOM demand this extra chart element will help them visualize it.
[highcharts_series_options] = {“id”:“demand”,“name”:“DEMAND”,“data”:[],“color”:"#F1B925",“type”:“line”,“index”:1,“legendIndex”:2,“zIndex”:10,“marker”:{“enabled”:true,“symbol”:“diamond”,“radius”:5}}
Step 8 - Test the BOM calculation
BOM demand is the demand for the components which are in dbo.BOM_demand table. The BOM demand is calculated in the procedure [dbo].[demand_processing_calc_run], first creating the purchase plan for the parent item(product) and then using the BOM to let each component get the right BOM demand.
In this example the component item 3405 is part of the product item 337 (also called parent item). To create item 337 there are 60 units of component item 3405 used. The BOM demand are created based on purchase plan of the parent item. So the next order day of item 337 is 1.02.2021 of 13.343 units. The BOM demand for component item 3405 is then calculated as this:
2021-02-01 -13.343 [purchase plan parent item 337] * 60 [qty_in_product]= 800.580
In following chart are the parent item (337) with the Purchase Plan Calculated visible.
In following chart are component item (3405) with the BOM Demand.
The final step is to test the BOM calculation using this method.
Step 9 - Create BOM mbe columns
Create two columns in tables cus.item_extra_info and mbe_columns: BOM_component_item_no and BOM_parent_item_no.
Add this piece of code in the procedure cus.item_extra_info_populate:
UPDATE iei
SET iei.BOM_component_item_no = LEFT(c.[BOMComponent Item No_],90)
FROM cus.item_extra_info iei
INNER JOIN rep.items i ON i.id = iei.item_id
INNER JOIN (
SELECT Main.[Parent Item No_],
LEFT(Main.BOMComp,Len(Main.BOMComp)-1) As 'BOMComponent Item No_'
FROM
(
SELECT DISTINCT ST2.[Parent Item No_],
(
SELECT ST1.No_ + '; ' AS [text()]
FROM nav.BOMComponents ST1
WHERE ST1.[Parent Item No_] = ST2.[Parent Item No_] AND No_ != ''
ORDER BY ST1.[Parent Item No_]
FOR XML PATH ('')
) [BOMComp]
FROM nav.BOMComponents ST2
) [Main]
) c ON c.[Parent Item No_] = i.item_no
UPDATE iei
SET iei.BOM_parent_item_no = LEFT(c.[BOM Parent Item No_], 90)
FROM cus.item_extra_info iei
INNER JOIN rep.items i ON i.id = iei.item_id
INNER JOIN (
--BOM Parent Item No
SELECT Main.No_,
LEFT(Main.Parent,Len(Main.Parent)-1) As 'BOM Parent Item No_' --stilla รก max 100 char? Lengsti er ekki nema ca 70 char
FROM
(
SELECT DISTINCT ST2.No_,
(
SELECT ST1.[Parent Item No_] + '; ' AS [text()]
FROM nav.BOMComponents ST1
WHERE ST1.No_ = ST2.No_
ORDER BY ST1.No_
FOR XML PATH ('')
) Parent
FROM nav.BOMComponents ST2
WHERE No_ != ''
) [Main]
)c ON c.[BOM Parent Item No_] = i.item_no
To populate them, run cus.item_extra_info_populate, cus.generate_extra_info_datatransfer and stg_prod.item_extra_info_ready_to_prod.