CREATE PROCEDURE TmSp_BOM_IsFather_Group ( IN son nvarchar(50) )
LANGUAGE SQLSCRIPT 
SQL SECURITY INVOKER
as

	 	
begin
	-- procedure uses temporary table #TmpInSpIsFatherGrp as source of father nodes to be compared
	-- RETURNS: sigle row <isFatherFound>, <Line Number of Found Father>

	DECLARE actualLayer integer;					-- current layer of the tree nodes
	DECLARE newRows integer;				-- number of new nodes 		
	DECLARE foundLineV integer;

	actualLayer := 0;
	newRows := 1;	
		
	-- check for identical father and son
	foundLineV := 0;
	IF (:foundLineV > 0)
	THEN
		CREATE LOCAL TEMPORARY TABLE "#TmpInSpIsFatherGrp" ( id nvarchar(50), "Line" integer);
	END IF;
			
	SELECT count(*) into foundLineV FROM "#TmpInSpIsFatherGrp" WHERE id = :son;	
	
	IF (:foundLineV > 0)
	THEN
 		SELECT TOP 1 1, "Line" FROM "#TmpInSpIsFatherGrp" WHERE id = :son;
 		RETURN;
	END IF;

	-- list of reached nodes with the layer where it was found for the first time
	CREATE LOCAL TEMPORARY TABLE #reached ( id nvarchar(50), layer integer);
	DELETE FROM #reached WHERE 1=1;

	-- insert root node id, start with child on layer 0
	INSERT INTO #reached VALUES (:son, :actualLayer); 

	-- continu until new layer adds newly discovered nodes
	WHILE (:newRows > 0) 
	DO

		actualLayer := :actualLayer + 1;
		
		INSERT INTO #reached 	-- insert new nodes found on this layer
			SELECT DISTINCT id, :actualLayer 
			FROM 
			(
				SELECT DISTINCT "Father" AS id FROM "ITT1"	
					WHERE "Code" IN 
						(SELECT id FROM #reached WHERE layer = :actualLayer-1)	-- select nodes from previous layer
			
				UNION ALL

				SELECT DISTINCT A."ItemCode" FROM "OWOR" A 
					INNER JOIN "WOR1" B ON A."DocEntry" = B."DocEntry" 
					WHERE B."ItemCode" in 
						(SELECT id FROM #reached WHERE layer = :actualLayer-1)	-- select nodes from previous layer
					AND (A."Status" = ('P ') OR A."Status" = ('R ') ) 
			) 	Fathers		
				
			WHERE id NOT IN (SELECT id FROM #reached);   -- remove already discovered nodes present in the @reached table  
				
		-- chek if node is present in currently added layer
		SELECT count(*) into newRows FROM "#TmpInSpIsFatherGrp" WHERE id IN (SELECT id FROM #reached WHERE layer = :actualLayer);	
		IF :newRows > 0 
		THEN
			SELECT TOP 1 1, "Line" FROM "#TmpInSpIsFatherGrp" WHERE id IN (SELECT id FROM #reached WHERE layer = :actualLayer);			 
			DROP TABLE #reached;	
			RETURN;
		END IF;
					
		SELECT count(*) into newRows FROM #reached WHERE layer = :actualLayer;	
							
	END WHILE;

	-- node not found in any of the layers
	DROP TABLE #reached;		
	SELECT 0, NULL FROM dummy;
	
end;
