EXEMPLO_1212310 Database

Parameters

Name	Type	Mode
@CODCOLIGADA	int	IN
@IDMOV	int	IN

Definition

CREATE FUNCTION RASTREIAMOVIMENTOS ( @CODCOLIGADA INT, @IDMOV INT )
RETURNS @TAB TABLE (	CODCOLIGADA 	INT,
			CODCOLIGADAPAI	INT,
			IDMOV 		INT,
			IDMOVPAI	INT,
			NSEQITMMOV	INT,
			NSEQITMMOVPAI	INT,
			NIVEL		VARCHAR (255),
			ORDEM		INT IDENTITY 		)
AS
BEGIN
	IF @IDMOV IS NULL
	BEGIN
		RETURN
	END
	DECLARE @IDMOVORIGINAL INT
	DECLARE @NIVEL VARCHAR (255)
	DECLARE @I INT
	DECLARE @J INT
	INSERT INTO @TAB ( CODCOLIGADA, CODCOLIGADAPAI, IDMOV, IDMOVPAI, NSEQITMMOV, NSEQITMMOVPAI )
	SELECT	TITMMOVRELAC.CODCOLORIGEM,
		TITMMOVRELAC.CODCOLDESTINO,
		TITMMOVRELAC.IDMOVORIGEM,
		TITMMOVRELAC.IDMOVDESTINO,
		TITMMOVRELAC.NSEQITMMOVORIGEM,
		TITMMOVRELAC.NSEQITMMOVDESTINO
	FROM	TITMMOVRELAC  WITH (NOLOCK)
	WHERE	TITMMOVRELAC.IDMOVDESTINO = @IDMOV
	  AND	TITMMOVRELAC.CODCOLDESTINO = @CODCOLIGADA
	  AND	NOT EXISTS (	SELECT	TOP 1 1
				FROM	@TAB T
				WHERE	T.IDMOV = TITMMOVRELAC.IDMOVORIGEM
				  AND	T.CODCOLIGADA = TITMMOVRELAC.CODCOLORIGEM
				  AND	T.NSEQITMMOV = TITMMOVRELAC.NSEQITMMOVORIGEM	)
	INSERT INTO @TAB ( CODCOLIGADA, CODCOLIGADAPAI, IDMOV, IDMOVPAI, NSEQITMMOV, NSEQITMMOVPAI )
	SELECT	TITMMOV.CODCOLIGADA,
		NULL,
		TITMMOV.IDMOV,
		NULL,
		TITMMOV.NSEQITMMOV,
		NULL
	FROM	TITMMOV  WITH (NOLOCK)
	WHERE	TITMMOV.IDMOV = @IDMOV
	  AND	TITMMOV.CODCOLIGADA = @CODCOLIGADA
	  AND	NOT EXISTS (	SELECT	TOP 1 1
				FROM	TITMMOVRELAC
				WHERE	( TITMMOVRELAC.IDMOVORIGEM = TITMMOV.IDMOV
				  AND	TITMMOVRELAC.CODCOLORIGEM = TITMMOV.CODCOLIGADA
				  AND	TITMMOVRELAC.NSEQITMMOVORIGEM = TITMMOV.NSEQITMMOV )
				 OR	( TITMMOVRELAC.IDMOVDESTINO = TITMMOV.IDMOV
				  AND	TITMMOVRELAC.CODCOLDESTINO = TITMMOV.CODCOLIGADA
				  AND	TITMMOVRELAC.NSEQITMMOVDESTINO = TITMMOV.NSEQITMMOV )	)
	WHILE EXISTS (SELECT	TITMMOVRELAC.CODCOLORIGEM,
				TITMMOVRELAC.CODCOLDESTINO,
				TITMMOVRELAC.IDMOVORIGEM,
				TITMMOVRELAC.IDMOVDESTINO,
				TITMMOVRELAC.NSEQITMMOVORIGEM,
				TITMMOVRELAC.NSEQITMMOVDESTINO
				FROM	TITMMOVRELAC  WITH (NOLOCK)
					INNER JOIN @TAB T ON
					TITMMOVRELAC.IDMOVDESTINO = T.IDMOV
					AND TITMMOVRELAC.CODCOLDESTINO = T.CODCOLIGADA
					AND TITMMOVRELAC.NSEQITMMOVDESTINO = T.NSEQITMMOV
				WHERE	NOT EXISTS (	SELECT	TOP 1 1
							FROM	@TAB T
							WHERE	T.IDMOV = TITMMOVRELAC.IDMOVORIGEM
							  AND	T.CODCOLIGADA = TITMMOVRELAC.CODCOLORIGEM
							  AND	T.NSEQITMMOV = TITMMOVRELAC.NSEQITMMOVORIGEM	)	)
	BEGIN
		INSERT INTO @TAB ( CODCOLIGADA, CODCOLIGADAPAI, IDMOV, IDMOVPAI, NSEQITMMOV, NSEQITMMOVPAI )
		SELECT	TITMMOVRELAC.CODCOLORIGEM,
			TITMMOVRELAC.CODCOLDESTINO,
			TITMMOVRELAC.IDMOVORIGEM,
			TITMMOVRELAC.IDMOVDESTINO,
			TITMMOVRELAC.NSEQITMMOVORIGEM,
			TITMMOVRELAC.NSEQITMMOVDESTINO
		FROM	TITMMOVRELAC  WITH (NOLOCK)
			INNER JOIN @TAB T ON
			TITMMOVRELAC.IDMOVDESTINO = T.IDMOV
			AND TITMMOVRELAC.CODCOLDESTINO = T.CODCOLIGADA
			AND TITMMOVRELAC.NSEQITMMOVDESTINO = T.NSEQITMMOV
		WHERE	NOT EXISTS (	SELECT	TOP 1 1
					FROM	@TAB T
					WHERE	T.IDMOV = TITMMOVRELAC.IDMOVORIGEM
					  AND	T.CODCOLIGADA = TITMMOVRELAC.CODCOLORIGEM
					  AND	T.NSEQITMMOV = TITMMOVRELAC.NSEQITMMOVORIGEM	)
	END
	SET @IDMOVORIGINAL = (	SELECT MAX ( T.ORDEM ) FROM @TAB T )
	DELETE	@TAB
	FROM	@TAB T
	WHERE	T.ORDEM <> @IDMOVORIGINAL
	SET @IDMOVORIGINAL = ( SELECT T.IDMOV FROM @TAB T)
	INSERT INTO @TAB ( CODCOLIGADA, CODCOLIGADAPAI, IDMOV, IDMOVPAI, NSEQITMMOV, NSEQITMMOVPAI )
	SELECT	TITMMOVRELAC.CODCOLORIGEM,
		TITMMOVRELAC.CODCOLDESTINO,
		TITMMOVRELAC.IDMOVORIGEM,
		TITMMOVRELAC.IDMOVDESTINO,
		TITMMOVRELAC.NSEQITMMOVORIGEM,
		TITMMOVRELAC.NSEQITMMOVDESTINO
	FROM	TITMMOVRELAC  WITH (NOLOCK)
	WHERE	TITMMOVRELAC.IDMOVORIGEM = @IDMOV
	  AND	TITMMOVRELAC.CODCOLORIGEM = @CODCOLIGADA
	  AND	NOT EXISTS (	SELECT	TOP 1 1
				FROM	TITMMOVRELAC T
				WHERE	T.IDMOVDESTINO = TITMMOVRELAC.IDMOVORIGEM
				  AND	T.CODCOLDESTINO = TITMMOVRELAC.CODCOLORIGEM
				  AND	T.NSEQITMMOVDESTINO = TITMMOVRELAC.NSEQITMMOVORIGEM	)
	INSERT INTO @TAB ( CODCOLIGADA, CODCOLIGADAPAI, IDMOV, IDMOVPAI, NSEQITMMOV, NSEQITMMOVPAI )
	SELECT	TITMMOVRELAC.CODCOLORIGEM,
		TITMMOVRELAC.CODCOLDESTINO,
		TITMMOVRELAC.IDMOVORIGEM,
		TITMMOVRELAC.IDMOVDESTINO,
		TITMMOVRELAC.NSEQITMMOVORIGEM,
		TITMMOVRELAC.NSEQITMMOVDESTINO
	FROM	TITMMOVRELAC  WITH (NOLOCK)
	WHERE	TITMMOVRELAC.IDMOVORIGEM = @IDMOVORIGINAL
	  AND	TITMMOVRELAC.CODCOLORIGEM = @CODCOLIGADA
	  AND	NOT EXISTS (	SELECT	TOP 1 1
				FROM	@TAB T
				WHERE	T.IDMOV = TITMMOVRELAC.IDMOVORIGEM
				  AND	T.CODCOLIGADA = TITMMOVRELAC.CODCOLORIGEM
				  AND	T.NSEQITMMOV = TITMMOVRELAC.NSEQITMMOVORIGEM	)
	UPDATE @TAB SET CODCOLIGADAPAI = NULL, IDMOVPAI = NULL, NSEQITMMOVPAI = NULL, NIVEL = '01'
	WHILE EXISTS (SELECT	TITMMOVRELAC.CODCOLORIGEM,
				TITMMOVRELAC.CODCOLDESTINO,
				TITMMOVRELAC.IDMOVORIGEM,
				TITMMOVRELAC.IDMOVDESTINO,
				TITMMOVRELAC.NSEQITMMOVORIGEM,
				TITMMOVRELAC.NSEQITMMOVDESTINO
				FROM	TITMMOVRELAC  WITH (NOLOCK)
					INNER JOIN @TAB T ON
					TITMMOVRELAC.IDMOVORIGEM = T.IDMOV
					AND TITMMOVRELAC.CODCOLORIGEM = T.CODCOLIGADA
					AND TITMMOVRELAC.NSEQITMMOVORIGEM = T.NSEQITMMOV
				WHERE	NOT EXISTS (	SELECT	TOP 1 1
							FROM	@TAB T
							WHERE	T.IDMOV = TITMMOVRELAC.IDMOVDESTINO
							  AND	T.CODCOLIGADA = TITMMOVRELAC.CODCOLDESTINO
							  AND	T.NSEQITMMOV = TITMMOVRELAC.NSEQITMMOVDESTINO	)	)
	BEGIN
		INSERT INTO @TAB ( CODCOLIGADA, CODCOLIGADAPAI, IDMOV, IDMOVPAI, NSEQITMMOV, NSEQITMMOVPAI )
		SELECT	TITMMOVRELAC.CODCOLDESTINO,
			TITMMOVRELAC.CODCOLORIGEM,
			TITMMOVRELAC.IDMOVDESTINO,
			TITMMOVRELAC.IDMOVORIGEM,
			TITMMOVRELAC.NSEQITMMOVDESTINO,
			TITMMOVRELAC.NSEQITMMOVORIGEM
		FROM	TITMMOVRELAC  WITH (NOLOCK)
			INNER JOIN @TAB T ON
			TITMMOVRELAC.IDMOVORIGEM = T.IDMOV
			AND TITMMOVRELAC.CODCOLORIGEM = T.CODCOLIGADA
			AND TITMMOVRELAC.NSEQITMMOVORIGEM = T.NSEQITMMOV
		WHERE	NOT EXISTS (	SELECT	TOP 1 1
					FROM	@TAB T
					WHERE	T.IDMOV = TITMMOVRELAC.IDMOVDESTINO
					  AND	T.CODCOLIGADA = TITMMOVRELAC.CODCOLDESTINO
					  AND	T.NSEQITMMOV = TITMMOVRELAC.NSEQITMMOVDESTINO	)
	END
	SET @I = 2
	SET @IDMOVORIGINAL = (	SELECT	TOP 1 T.IDMOV
				FROM	@TAB T
				WHERE	T.NIVEL = '01'
				ORDER BY T.ORDEM		)
	WHILE EXISTS (	SELECT	TOP 1 1
			FROM	@TAB T
			WHERE	T.NIVEL = '01'
			  AND	EXISTS (	SELECT	TOP 1 1
						FROM	@TAB TT
						WHERE	TT.IDMOV <> T.IDMOV
						  AND	TT.NIVEL = '01'		)	)
	BEGIN
		UPDATE @TAB SET NIVEL = REPLICATE ( '0', 2 - LEN ( CONVERT ( VARCHAR, @I ) ) ) + CONVERT ( VARCHAR, @I )
		FROM	@TAB T
		WHERE	T.NIVEL = '01'
		  AND	T.IDMOV <> @IDMOVORIGINAL
		  AND	EXISTS (	SELECT	TOP 1 1
					FROM	@TAB TT
					WHERE	TT.NIVEL = '01'
					  AND	TT.IDMOV <> T.IDMOV	)
	END
	SET @I = 1
	WHILE EXISTS (	SELECT	TOP 1 1
			FROM	@TAB T
			WHERE	T.NIVEL IS NULL	)
	BEGIN
		SET @IDMOVORIGINAL = (	SELECT	TOP 1 T.IDMOVPAI
					FROM	@TAB T
						INNER JOIN @TAB TT ON
						TT.IDMOV = T.IDMOVPAI
						AND TT.NSEQITMMOV = T.NSEQITMMOVPAI
						AND TT.CODCOLIGADA = T.CODCOLIGADAPAI
						AND TT.NIVEL IS NOT NULL
					WHERE	T.NIVEL IS NULL
					ORDER BY T.ORDEM			)
		SET @NIVEL = (	SELECT	TOP 1 T.NIVEL
				FROM	@TAB T
				WHERE	T.IDMOV = @IDMOVORIGINAL
				  AND	T.NIVEL IS NOT NULL	)
		SET @I = 1
		WHILE EXISTS (	SELECT	TOP 1 1
				FROM	@TAB T
				WHERE	T.IDMOVPAI = @IDMOVORIGINAL
				  AND	T.NIVEL IS NULL			)
		BEGIN
			UPDATE	@TAB SET NIVEL = @NIVEL + '.' + REPLICATE ( '0', 2 - LEN ( CONVERT ( VARCHAR, @I ) ) ) + CONVERT ( VARCHAR, @I )
			FROM	@TAB T
			WHERE	T.IDMOV = (	SELECT	TOP 1 TT.IDMOV
						FROM	@TAB TT
						WHERE	TT.NIVEL IS NULL
						  AND	TT.IDMOVPAI = @IDMOVORIGINAL	)
			  AND	T.IDMOVPAI = @IDMOVORIGINAL
			SET @I = @I + 1
		END
	END
	RETURN
END