pottsm - Wednesday, December 15, 2010 1:58 PM:
I ran into a bug when writing an ECN that had two assemblies with the same sub-assembly on both of the assemblies on the ECN.
For example:
ECN - 10001
Part - 1234
BOM - SUB_ASSEMBLY
Part - 5678
BOM - SUB_ASSEMBLY
will cause a Primay Key error in the procedure during the leaf loop here:
INSERT INTO #TMP_BOMS_TO_USE(id, source_id, related_id, obj_affected_item_id)
SELECT pb.id, pb.source_id, pb.related_id, lf.obj_affected_item_id
FROM PART_BOM pb, @LEAF_PARTS lf
WHERE pb.source_id = lf.id
AND lf.partLevel = @PART_LEVEL
AND pb.related_id IS NOT NULL
AND NOT EXISTS (SELECT TOP 1 id FROM @LEAF_PARTS WHERE id = pb.related_id AND obj_affected_item_id = lf.obj_affected_item_id)
To fix the problem, I created a duplicate of the #TMP_BOMS_TO_USE table, without the primay key constraint to be used for the leaf loop. I did not change the #TMP_BOMS_TO_USE table because it seems it should keep the primary key constraint to ensure BOMs don't have duplicate part numbers on the part assemblies, but for checking the levels, this is not needed. My new table is called #TMP_LEAF_BOMS
Here is the full source code to the procedure, please let me know if you think there may be any issues with this implementation.
--------------------------------------------------------------------------------------------------------------
USE [InnovatorSolutions]
GO
/****** Object: StoredProcedure [innovator].[PE_GetAffectedItemHandlingOrder] Script Date: 12/15/2010 12:38:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- PE_GetAffectedItemHandlingOrder procedure
/*
Purpose:
Finds affected items for object with id=@objId in appropriate order.
Arguments:
@objId - ID of ECN or Simple ECO object
*/
ALTER PROCEDURE [innovator].[PE_GetAffectedItemHandlingOrder](@objId char(32))
AS
BEGIN
DECLARE @PART_LEVEL INT;
DECLARE @TEMP_ID INT;
SET @PART_LEVEL = 0;
SET @TEMP_ID = 0;
/* Create temporary table to store affected_id(part_id) and obj_affected_item_id */
CREATE TABLE #OBJ_AFFECTED_ITEMS(affected_id CHAR(32) collate database_default, obj_affected_item_id char(32) collate database_default, cci_type_name nvarchar(128))
INSERT INTO #OBJ_AFFECTED_ITEMS(affected_id, obj_affected_item_id, cci_type_name)
SELECT ai.affected_id, obj_ai.id, it.name
FROM (SELECT id,source_id,related_id FROM ecn_affected_item UNION ALL SELECT id,source_id,related_id FROM simple_eco_affected_item) obj_ai INNER JOIN
affected_item ai ON obj_ai.source_id = @objId AND ai.id = obj_ai.related_id INNER JOIN
change_controlled_item cci ON ai.affected_id = cci.id INNER JOIN
itemtype it ON cci.itemtype=it.id
/* If OBJECT has only one OBJECT Affected Item relationship - return, because no need to calculate order */
IF (@@ROWCOUNT = 1)
BEGIN
SELECT obj_affected_item_id FROM #OBJ_AFFECTED_ITEMS;
RETURN;
END
CREATE TABLE #TMP_BOMS_TO_USE(id CHAR(32) COLLATE database_default PRIMARY KEY,
source_id CHAR(32) COLLATE database_default,
related_id CHAR(32) COLLATE database_default,
obj_affected_item_id CHAR(32) COLLATE database_default);
INSERT INTO #TMP_BOMS_TO_USE(id, source_id, related_id, obj_affected_item_id)
SELECT id, source_id, related_id, obj_ai.obj_affected_item_id
FROM PART_BOM pb, #OBJ_AFFECTED_ITEMS obj_ai
WHERE pb.source_id = obj_ai.affected_id
AND pb.related_id IS NOT NULL
/* There are Part BOMs in DB */
IF (@@ROWCOUNT > 0)
BEGIN
CREATE INDEX ind_tmp_BOMS_TO_USE_source_id ON #TMP_BOMS_TO_USE(source_id);
CREATE INDEX ind_tmp_BOMS_TO_USE_related_id ON #TMP_BOMS_TO_USE(related_id);
END
/** BUGFIX
#TMP_BOMS_TO_USE SHOULD HAVE A PRIMARY KEY TO ENSURE THERE ISN'T MULTIPLE OF THE SAME
PARTS ON THE SINGLE BOM, BUT... IF THERE IS A SUB-ASSEMBLY USED TO TWO **DIFFERENT**
PARTS ON A BOM, USING #TMP_BOMS_TO_USE FOR CHECKING FOR LEAF LEVEL **WILL FAIL**
CREATING ANOTHER TABLE WITH THE SAME DATA, **WITHOUT** THE PRIMARY KEY CONSTRAINT
FIXES THE PROBLEM
**/
CREATE TABLE #TMP_LEAF_BOMS(id CHAR(32) COLLATE database_default,
source_id CHAR(32) COLLATE database_default,
related_id CHAR(32) COLLATE database_default,
obj_affected_item_id CHAR(32) COLLATE database_default);
INSERT INTO #TMP_LEAF_BOMS(id, source_id, related_id, obj_affected_item_id)
SELECT id, source_id, related_id, obj_ai.obj_affected_item_id
FROM PART_BOM pb, #OBJ_AFFECTED_ITEMS obj_ai
WHERE pb.source_id = obj_ai.affected_id
AND pb.related_id IS NOT NULL
/* There are Part BOMs in DB */
IF (@@ROWCOUNT > 0)
BEGIN
CREATE INDEX ind_tmp_BOMS_TO_USE_source_id ON #TMP_LEAF_BOMS(source_id);
CREATE INDEX ind_tmp_BOMS_TO_USE_related_id ON #TMP_LEAF_BOMS(related_id);
END
/* Iterate over leaf Parts. */
DECLARE @LEAF_PARTS TABLE(id CHAR(128) collate database_default, partLevel INT, obj_affected_item_id char(32) collate database_default);
INSERT INTO @LEAF_PARTS(id, partLevel, obj_affected_item_id)
SELECT affected_id, @PART_LEVEL, obj_affected_item_id
FROM #OBJ_AFFECTED_ITEMS
WHERE cci_type_name=N'Part'
WHILE (EXISTS(SELECT TOP 1 id FROM #TMP_LEAF_BOMS))
BEGIN
SET @PART_LEVEL = @PART_LEVEL + 1;
INSERT INTO @LEAF_PARTS(id, partLevel, obj_affected_item_id)
SELECT related_id, @PART_LEVEL, obj_affected_item_id
FROM #TMP_LEAF_BOMS
/* delete BOMs pointing to leaf Parts */
DELETE FROM #TMP_LEAF_BOMS
INSERT INTO #TMP_LEAF_BOMS(id, source_id, related_id, obj_affected_item_id)
SELECT pb.id, pb.source_id, pb.related_id, lf.obj_affected_item_id
FROM PART_BOM pb, @LEAF_PARTS lf
WHERE pb.source_id = lf.id
AND lf.partLevel = @PART_LEVEL
AND pb.related_id IS NOT NULL
AND NOT EXISTS (SELECT TOP 1 id FROM @LEAF_PARTS WHERE id = pb.related_id AND obj_affected_item_id = lf.obj_affected_item_id)
END
SELECT obj_ai.obj_affected_item_id obj_affected_item_id
FROM #OBJ_AFFECTED_ITEMS obj_ai LEFT OUTER JOIN @LEAF_PARTS lp ON obj_ai.affected_id = lp.id
WHERE obj_ai.cci_type_name!=N'Part'
OR (lp.id IS NOT NULL AND lp.partLevel = (SELECT MAX(partLevel) FROM @LEAF_PARTS WHERE id = lp.id))
ORDER BY partLevel DESC
END
--------------------------------------------------------------------------------------------------------------
pottsm - Friday, January 14, 2011 3:25 PM:
Just out of curiosity, does anyone know if Aras employee's actually look at this forum at all or is it just community? I just find it interesting there wasn't any verification/validation on a post with a bug AND a proposed solution. I can see them overlooking a question about a potential bug, but when someone in the Aras community goes to the extra mile to actually fix the bug it would be nice to know if the bug fix has been verified on the Aras end and if the fix will get integrated into the next release so I don't have to fix it again if we do an upgrade.
Brian - Saturday, January 15, 2011 8:05 AM:
There are several Aras employees who visit the forum's on a regular basis including the Aras President - Peter Schroer.
pottsm - Thursday, May 22, 2014 6:00 PM:
Dear Aras People....
We just upgraded to Version:9.4.0; Build:5804
I can't believe this is STILL a BUG that hasn't been fixed. I just ran into the same damn problem with the new release and had to fix this stupid bug again. I can't tell you frustrating this is as I PROVIDED YOU THE FIX FOR THIS BUG 3+ YEARS AGO!!
Anyhow, to anyone else in the community, the bug fix above STILL applies and STILL works.
