This is used to determine any part categories that may be incorrectly set in a circular loop.

This means that a sub-category has accidentally been set as the parent group for the group that it is in itself.

An example of this is explained below:

Using the above picture as the example, if you accidentally set the Category (in Part Setup) for Cabinet Components to Cabinet Extruded Corners, you would now be in a loop.

The reason is that the Category for Cabinet Extruded Corners is Cabinet Components.

None. This is simply a SQL that will determine which part categories are affected by this issue.

DECLARE @AllSubCats
TABLE(
ID int,
ParentID int
)
--subcategories that are part of a loop
DECLARE @Blacklist TABLE
(
ID int
)
--subcategories whose ancestry ends at a root node
DECLARE @Whitelist TABLE
(
ID int
)
--iterator
DECLARE @i int;
SET @i = 0;
DECLARE @parent int;
DECLARE @id int;
DECLARE @nextAncestor int;
INSERT into @AllSubCats select ID,ParentID from PricingElement where ClassTypeID = 12035 and parentID is not null and ParentClassTypeID is not null
DECLARE @l int;
SET @l = (select COUNT(*) from @AllSubCats);
--iterate over all categories
WHILE @i < @l
BEGIN
 SET @i = @i + 1;
 DECLARE @visited TABLE
 (
Node int
 );
 SET @parent = (select top 1 ParentID from @AllSubCats);
 SET @id = (select top 1 ID from @AllSubCats);
 --pop this from all categories
 DELETE from @AllSubCats where ParentID = @parent and ID = @id;
 INSERT into @visited values (@id);
 --seed the traversal with the parent
 SET @nextAncestor = @parent;
 if exists(select ID from @Whitelist where ID = @parent)
 BEGIN
     INSERT into @Whitelist values (@id);
 END
 --skip this if we have already been blacklisted
 ELSE if not exists(select ID from @Blacklist where ID = @id)
 BEGIN
     --loop until we have either reached the top or determined this is a loop
 while @nextAncestor is not null
 BEGIN
if not exists(select Node from @visited where Node = @nextAncestor)
BEGIN
 --continuing traversing upwards
 INSERT into @visited values (@nextAncestor);
 SET @nextAncestor = (select ParentID from PricingElement where ClassTypeID = 12035 and ID = @nextAncestor);
 --unless we have reached a root node
 if @nextAncestor is null
 BEGIN
   --then whitelist the entire traversed chain
   INSERT into @Whitelist select Node from @visited v LEFT JOIN @Blacklist t on v.Node = t.ID where t.ID IS NULL
 END
END
ELSE
BEGIN
 --all the nodes we visited are in a loop, so copy our table, INSERTing any IDs into output table that have not been INSERTed already
 INSERT into @Blacklist select Node from @visited v LEFT JOIN @Blacklist t on v.Node = t.ID where t.ID IS NULL
 --debugging prints
 --DECLARE @cycleIDString varchar(100) = null;
 --SELECT @cycleIDString = COALESCE(@cycleIDString + '', '', '''') + CAST(Node AS varchar(5)) FROM @visited
 --Print(''Found cycle IDs: ''+@cycleIDString);
 --DECLARE @cycleString varchar(100) = null;
 --SELECT @cycleString = COALESCE(@cycleString + '' -> '', '''') + p.ElementName FROM @visited v inner join PricingElement p on p.ID = v.Node;
 --Print(''Found cycle: ''+@cycleString);
 --escape the while loop
 SET @nextAncestor = null;
END
 END
 END
 DELETE from @visited where Node is not null; --cleanup the visited table
END
--return the blacklist
select t.ID, p.ElementName from @Blacklist t inner join PricingElement p on t.ID = p.ID order by ElementName

Note: This issue has been resolved in 5.1, and additional code has been put into place to auto correct this situation if it is detected.