IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_PRC_FILIAL_AGRUPADA]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_PRC_FILIAL_AGRUPADA]
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[UFD_PRC_FILIAL_AGRUPADA](@CD_EMP INT, @CD_FILIAL INT)
RETURNS @RS_PRC_FILIAL_AGRUPADA TABLE(CD_FILIAL INT)
AS
BEGIN

	DECLARE @CD_GRUPO INT;

	/* LISTAR O CODIGO DO GRUPO A QUE A FILIAL ESTA VINCULADA */
	SET @CD_GRUPO = 
				(SELECT TOP 1 COALESCE(CD_GRUPO, 0) AS CD_GRUPO FROM EST_SNGPC_PRC_FILIAL_GRUPO
					WHERE CD_EMP = @CD_EMP AND CD_FILIAL = @CD_FILIAL
				);

	/* CRIAR TABELA COM TODAS AS FILIAIS PERTENCENTES AO GRUPO */
	WITH
		TMP AS (
			SELECT @CD_FILIAL AS CD_FILIAL
			UNION 
			SELECT CD_FILIAL FROM EST_SNGPC_PRC_FILIAL_GRUPO_CPL
			WHERE CD_GRUPO = @CD_GRUPO
		)
		
		INSERT INTO @RS_PRC_FILIAL_AGRUPADA
		SELECT 
			CD_FILIAL
		FROM TMP
		
	RETURN;
		
END
GO


