Hi,
I am trying to explode BOM ( to list all raw materials for finished goods) in NAV 2013 R2 using a SQL query. The BOM Component table is empty and hence I cannot refer that to find the parent item number. I am using Production BOM Header, Production BOM Line and Item tables instead.
Here is my cursor below. For some reason, it is not listing all the raw materials when it jumps to more than one level for a finished good.
The attached PDF shows the explosion for a finished good.
Would appreciate if someone can help me. Thank you!
DECLARE
@production_bom_no_
nvarchar(20
),
@no_
nvarchar(20
),
@quantity_per
decimal(38,20
),
@unit_of_measure_code
nvarchar(10
);
DECLARE
@nested_production_bom_no_
nvarchar(20
),
@nested_no_
nvarchar(20
),
@nested_quantity_per
decimal(38,20
),
@nested_unit_of_measure_code
nvarchar(10
);
DECLARE
@new_nested_no_
nvarchar(20
);
DECLARE
bom_explode_cursor CURSOR
FOR
SELECT
b
.[PRODUCTION BOM No_]
,
b
.[No_]
,
b
.[Quantity Per]
,
b
.
[Unit of Measure Code]
FROM
[TOC$Production BOM Header] a
INNER
JOIN
[TOC$Production BOM Line] b
on
a.[No_] = b.
[Production BOM No_]
–and a.[Low-Level Code] = 1
and
a.[No_] NOT LIKE
‘IMP%’
INNER
JOIN
TOC$Item d
on
a.No_ = d.
No_
and
d.[Item Category Code] =
‘Finished G’
OPEN
bom_explode_cursor
FETCH
NEXT FROM
bom_explode_cursor
INTO
@production_bom_no_
,
@no_
,
@quantity_per
,
@unit_of_measure_code
WHILE
@@FETCH_STATUS =
0
BEGIN
IF @no_ LIKE ‘RM%’
INSERT INTO
[BOM Explosion]
VALUES
(
@production_bom_no_
,
@no_
,
@quantity_per
,
@unit_of_measure_code
)
ELSE
BEGIN
/** Here’s where we need to have the nested cursor - loop through **/
SET @nested_production_bom_no_ =
@no_
DECLARE nested_bom_cursor CURSOR
FOR
/*SELECT
b.[Production BOM No_],
b.[No_],
b.[Quantity Per],
b.[Unit of Measure Code]
FROM [TOC$Production BOM Header] a
INNER JOIN [TOC$Production BOM Line] b
on a.No_ = b.[Production BOM No_]
and b.[Production BOM No_] = @nested_production_bom_no_
and a.[Low-level Code] <> 1
*/
SELECT
[Production BOM No_]
,
[No_]
,
[Quantity Per]
,
[Unit of Measure Code]
FROM
[TOC$Production BOM Line]
WHERE [PRODUCTION BOM No_] =
@nested_production_bom_no_
AND [No_] NOT LIKE
‘RM%’
OPEN
nested_bom_cursor
FETCH NEXT FROM
nested_bom_cursor
INTO
@nested_production_bom_no_
,
@nested_no_
,
@nested_quantity_per
,
@nested_unit_of_measure_code
WHILE @@FETCH_STATUS =
0
BEGIN
SET @nested_production_bom_no_ =
@nested_no_
FETCH NEXT FROM
nested_bom_cursor
INTO
@nested_production_bom_no_
,
@nested_no_
,
@nested_quantity_per
,
@nested_unit_of_measure_code
END
INSERT INTO
[BOM Explosion]
VALUES
(
@production_bom_no_
,
/* The root production BOM No */
@nested_no_
,
@nested_quantity_per
,
@nested_unit_of_measure_code
)
CLOSE
nested_bom_cursor
DEALLOCATE
nested_bom_cursor
END
FETCH NEXT FROM
bom_explode_cursor
INTO
@production_bom_no_
,
@no_