November 26, 2014 at 9:53 am
Hi,
I am trying to explode Bill of materials in NAV 2013 R2 using SQL query. Here is my cursor reason. For some reason, the query does not loop through more than 2 levels of BOM for a finished good. The attached PDF shows the all the raw materials for a finished good.
My result set is below where it does not print the raw materials for BOM RCP73 which is inside BOM MDESSWHITE.
Production BOM No_No_Quantity PerUnit of Measure Code
06200RM0000141.00000000000000000000EA
06200RM0000881.06000000000000000000MT
06200RM0000951.08000000000000000000MT
06200RM0000981.60000000000000000000FT
06200RM0000991.08900000000000000000FT
06200RM0000152.00000000000000000000EA
06200RM0000432.00000000000000000000EA
06200RM0000942.00000000000000000000EA
06200RM0000940.60000000000000000000MT
06200RM0001091.00000000000000000000EA
06200RM0008032.00000000000000000000EA
Would appreciate if someone can help me. Many thanks!
Regards,
Uma
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);
SET ROWCOUNT 1
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 b.[Version Code] = ''
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
[Production BOM No_],
[No_],
[Quantity Per],
[Unit of Measure Code]
FROM [TOC$Production BOM Line]
WHERE [PRODUCTION BOM No_] = @nested_production_bom_no_
AND [Version Code] = ''
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
IF @nested_no_ LIKE 'RM%'
INSERT INTO [BOM Explosion]
VALUES
(
@production_bom_no_,
@nested_no_,
@quantity_per,
@unit_of_measure_code
)
ELSE
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_,
@quantity_per,
@unit_of_measure_code
END
CLOSE bom_explode_cursor;
DEALLOCATE bom_explode_cursor;
December 15, 2014 at 8:53 am
Uma,
It was nice to have provided detail in the PDF, but the level of effort necessary to be effective in helping you rises above the amount of time available for most folks because you didn't translate the table data into SELECT statements that we could use to do some testing with. We also have no knowledge of the table structure and I have no idea what you are referring to when you mention "NAV 2013 R2". I know that translating a large quantity of table data into SELECT statements is going to take you time to do, but it's the only way you're likely to get help quickly. Some explanation of exactly what the table structure represents would also be helpful.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 15, 2014 at 10:30 am
It seems to me that you don't need a cursor (or worse, nested cursors). This could be accomplished with direct queries.
However, as Steve told you, we need DDL and sample data to test against and to translate the data as you posted it, understand it and create the whole environment, will take time and we appreciate if you post it yourself. Read the article linked in my signature to understand what we need.
December 15, 2014 at 10:47 am
Thank you, Luis and Steve! Will read the thread on how to post sample data.
Regards,
Uma
December 30, 2014 at 6:12 am
There is a "SET ROWCOUNT 1".
Try to remove it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply