IF NOT OBJECT_ID('UFD_BUSCA_NFE_REFERENCIADA') IS NULL
	BEGIN 
		DROP FUNCTION UFD_BUSCA_NFE_REFERENCIADA
	END

GO

-- SELECT DBO.UFD_BUSCA_NFE_REFERENCIADA(1,2,159721,1)

CREATE FUNCTION [dbo].UFD_BUSCA_NFE_REFERENCIADA(	@CD_EMP		INT, 
													@CD_FILIAL	INT,
													@CD_NF		INT,
													@TP_ENT_SAI INT)
RETURNS VARCHAR(MAX) 
AS
BEGIN

	DECLARE @RETORNO VARCHAR(MAX)
	DECLARE @RS_NR_AUTORIZADOR VARCHAR(MAX)

	SET @RETORNO = ''

	DECLARE RS_NF_DEV CURSOR FOR
		SELECT 
	REF_NF_DEV.NR_AUTORIZADOR
		FROM EST_NF_ENT_DEVOLUCAO_NFE_CHAVE_REFERENCIA REF_NF_DEV
		WHERE 
			REF_NF_DEV.CD_EMP = @CD_EMP
			AND REF_NF_DEV.CD_FILIAL = @CD_FILIAL
			AND REF_NF_DEV.CD_NF = @CD_NF
			AND @TP_ENT_SAI = 0
		UNION 
		
		SELECT 
			REF_NF_DEV.NR_AUTORIZADOR
		FROM EST_NF_SAI_DEVOLUCAO_NFE_CHAVE_REFERENCIA REF_NF_DEV
		WHERE 
			REF_NF_DEV.CD_EMP = @CD_EMP
			AND REF_NF_DEV.CD_FILIAL = @CD_FILIAL
			AND REF_NF_DEV.CD_NF = @CD_NF
			AND @TP_ENT_SAI = 1

	 UNION ALL
      -- Nota de Ressarcimento vinculo com a entrada Fornecedor 
 SELECT NR_AUTORIZADOR COLLATE SQL_Latin1_General_CP1_CI_AS AS NR_AUTORIZADOR FROM V_EST_NF_ENT_NFE NFE INNER JOIN 
			(
			SELECT EST_PED_VD_DEV_RESSARCIMENTO.CD_NF ,
			EST_PED_VD_DEV_RESSARCIMENTO.CD_EMP,
			EST_PED_VD_DEV_RESSARCIMENTO.CD_FILIAL
			
			FROM EST_NF_SAI INNER JOIN EST_PED_VD
			ON  EST_NF_SAI.CD_EMP = EST_PED_VD.CD_EMP 
			AND  EST_NF_SAI.CD_FILIAL = EST_PED_VD.CD_FILIAL 
			AND  EST_NF_SAI.CD_PED = EST_PED_VD.CD_PED
			
			INNER JOIN EST_PED_VD_DEV_RESSARCIMENTO  
			ON  EST_PED_VD_DEV_RESSARCIMENTO.CD_EMP = EST_PED_VD.CD_EMP 
			AND  EST_PED_VD_DEV_RESSARCIMENTO.CD_FILIAL = EST_PED_VD.CD_FILIAL 
			AND  EST_PED_VD_DEV_RESSARCIMENTO.CD_PED = EST_PED_VD.CD_PED
 
			WHERE 
			EST_NF_SAI.CD_EMP = @CD_EMP
			AND EST_NF_SAI.CD_FILIAL = @CD_FILIAL
			AND EST_NF_SAI.CD_NF = @CD_NF
			AND EST_PED_VD_DEV_RESSARCIMENTO.TIPO = 1 ) TEMP_VINC 

			ON NFE.CD_EMP = TEMP_VINC.CD_EMP 
			AND NFE.CD_FILIAL = TEMP_VINC.CD_FILIAL 
			AND NFE.CD_NF = TEMP_VINC.CD_NF

    	UNION ALL	
        -- nota de adjudicaçao vinculo com a nota de devolução 
		SELECT NR_AUTORIZADOR COLLATE SQL_Latin1_General_CP1_CI_AS AS NR_AUTORIZADOR FROM EST_NF_SAI SAI INNER JOIN ( 

          SELECT VINC.CD_FILIAL,VINC.CD_EMP, VINC.CD_PED FROM EST_PED_VD_DEV_RESSARCIMENTO VINC
             INNER JOIN (  
          
            -- CD_CTR DA NOTA DE RESSARCIMENTO 
			SELECT EST_PED_VD_DEV_RESSARCIMENTO.CD_CTR ,
			EST_PED_VD_DEV_RESSARCIMENTO.CD_EMP,
			EST_PED_VD_DEV_RESSARCIMENTO.CD_FILIAL
			
			FROM EST_NF_ENT INNER JOIN EST_PED_VD
			ON  EST_NF_ENT.CD_EMP = EST_PED_VD.CD_EMP 
			AND  EST_NF_ENT.CD_FILIAL = EST_PED_VD.CD_FILIAL 
			AND  EST_NF_ENT.CD_PED = EST_PED_VD.CD_PED
			
			INNER JOIN EST_PED_VD_DEV_RESSARCIMENTO  
			ON  EST_PED_VD_DEV_RESSARCIMENTO.CD_EMP = EST_PED_VD.CD_EMP 
			AND  EST_PED_VD_DEV_RESSARCIMENTO.CD_FILIAL = EST_PED_VD.CD_FILIAL 
			AND  EST_PED_VD_DEV_RESSARCIMENTO.CD_PED = EST_PED_VD.CD_PED

			WHERE 
			EST_NF_ENT.CD_EMP = @CD_EMP
			AND EST_NF_ENT.CD_FILIAL = @CD_FILIAL
			AND EST_NF_ENT.CD_NF = @CD_NF 
					
			) TEMP_CTR
			
			ON  VINC.CD_EMP = TEMP_CTR.CD_EMP
			AND VINC.CD_FILIAL = TEMP_CTR.CD_FILIAL
			AND VINC.CD_CTR = TEMP_CTR.CD_CTR
			
			WHERE VINC.CD_EMP = @CD_EMP
			AND VINC.CD_FILIAL = @CD_FILIAL
			AND VINC.TIPO = 0 
		
			)TEMP_PRE_FATURA

			ON SAI.CD_EMP = TEMP_PRE_FATURA.CD_EMP
			AND SAI.CD_FILIAL = TEMP_PRE_FATURA.CD_FILIAL
			AND SAI.CD_PED = TEMP_PRE_FATURA.CD_PED

			INNER JOIN    EST_NF_SAI_NFE 
			ON SAI.CD_EMP = EST_NF_SAI_NFE.CD_EMP
			AND SAI.CD_FILIAL = EST_NF_SAI_NFE.CD_FILIAL
			AND SAI.CD_NF= EST_NF_SAI_NFE.CD_NF


		--Abrindo cursor
		OPEN RS_NF_DEV
			
		--Atribuindo valores do select nas variáveis
		FETCH NEXT FROM RS_NF_DEV INTO  @RS_NR_AUTORIZADOR
											
		--Iniciando laço
		WHILE @@FETCH_STATUS = 0
			BEGIN			
				IF LEN(@RETORNO) > 0 
					SET @RETORNO = @RETORNO + ';' + @RS_NR_AUTORIZADOR
				ELSE				
					SET @RETORNO = @RS_NR_AUTORIZADOR

					--Atribuindo valores do select nas variáveis
				FETCH NEXT FROM RS_NF_DEV INTO  @RS_NR_AUTORIZADOR			
			END

		CLOSE RS_NF_DEV
		DEALLOCATE RS_NF_DEV
		
		RETURN @RETORNO

END
