IF EXISTS (select * from INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='BUSCA_TIPO_DEVOLUCAO_NOTA' AND ROUTINE_TYPE= 'FUNCTION')
drop FUNCTION BUSCA_TIPO_DEVOLUCAO_NOTA
GO

/*
	Retorna tipo devolução de uma nota de entrada
	EMPRESA: CORPORATE BUSINES
	DATA INICIO:  15/12/2022
	DATA TERMINO: 15/12/2022 
	PROFISSIONAL: Eduardo Nunes
*/
--SELECT DBO.TST_EST_NF_ENT_STATUS(1,1,5)
CREATE FUNCTION [dbo].[BUSCA_TIPO_DEVOLUCAO_NOTA]
											(@CD_EMP    INT
										   , @CD_FILIAL INT
										   , @CD_NF     INT)
RETURNS NVARCHAR(30)
AS
	 BEGIN
		
		 DECLARE @STS_NF NVARCHAR(30);


	SET @STS_NF = (SELECT TOP 1 CASE 
			WHEN 
				EST_NF_SAI_NF_ENT.CD_NF_SAI IS NOT NULL	 
				AND EST_NF_SAI.VLR_NF = EST_NF_ENT.VLR_NF 
			THEN 'DEV TOTAL' 
			WHEN EST_NF_SAI_NF_ENT.CD_NF_SAI IS NOT NULL
				AND  EST_NF_SAI.VLR_NF <> EST_NF_ENT.VLR_NF 
			THEN 'DEV PARCIAL'
			WHEN EST_NF_SAI_NF_ENT.CD_NF_SAI IS NULL 
			THEN 'N'  
		END AS DEVOLUCAO
		FROM 

		EST_NF_SAI 
		
		
		INNER JOIN  EST_NF_SAI_NF_ENT ON 
			EST_NF_SAI.CD_EMP = EST_NF_SAI_NF_ENT.CD_EMP
			AND EST_NF_SAI.CD_FILIAL = EST_NF_SAI_NF_ENT.CD_FILIAL
			AND EST_NF_SAI.CD_NF = EST_NF_SAI_NF_ENT.CD_NF_SAI
		
		INNER  JOIN EST_NF_ENT ON 
			EST_NF_SAI_NF_ENT.CD_EMP = EST_NF_ENT.CD_EMP
			AND EST_NF_SAI_NF_ENT.CD_FILIAL = EST_NF_ENT.CD_FILIAL
			AND EST_NF_SAI_NF_ENT.CD_NF_ENT = EST_NF_ENT.CD_NF
		WHERE EST_NF_SAI_NF_ENT.CD_EMP = @CD_EMP
		AND EST_NF_SAI_NF_ENT.CD_FILIAL = @CD_FILIAL
		AND EST_NF_SAI_NF_ENT.CD_NF_SAI  =@CD_NF


			)




		-- SET @QT_ENT =( SELECT
		--					SUM(EST_NF_ENT_IT.QT_IT) AS QT_IT_MOV
		--				FROM
		--					EST_NF_ENT
		--					INNER JOIN EST_NF_ENT_IT ON EST_NF_ENT.CD_EMP = EST_NF_ENT_IT.CD_EMP
		--												AND EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL
		--												AND EST_NF_ENT.CD_NF = EST_NF_ENT_IT.CD_NF
		--												AND EST_NF_ENT.DT_ENT = EST_NF_ENT_IT.DT_ENT 
		--				WHERE  EST_NF_ENT_IT.CD_EMP = @CD_EMP
		--					   AND EST_NF_ENT_IT.CD_FILIAL = @CD_FILIAL
		--					   AND EST_NF_ENT_IT.CD_NF = @CD_NF);


		--SET @QT_DEV =( SELECT
		--			SUM(EST_NF_SAI_IT.QT_IT) AS QT_IT_MOV
		--			FROM
		--			EST_NF_ENT_NF_SAI
		--			INNER JOIN EST_NF_SAI ON EST_NF_ENT_NF_SAI.CD_EMP = EST_NF_SAI.CD_EMP
		--										AND EST_NF_ENT_NF_SAI.CD_FILIAL = EST_NF_SAI.CD_FILIAL
		--										AND EST_NF_ENT_NF_SAI.CD_NF_SAI = EST_NF_SAI.CD_NF
		--			INNER JOIN EST_NF_SAI_IT ON EST_NF_SAI.CD_EMP = EST_NF_SAI_IT.CD_EMP
		--										AND EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL
		--										AND EST_NF_SAI.CD_NF = EST_NF_SAI_IT.CD_NF
		--										AND EST_NF_SAI.DT_EMI_NF = EST_NF_SAI_IT.DT_EMI_NF
		--		WHERE	   EST_NF_SAI_IT.CD_EMP = @CD_EMP
		--				AND EST_NF_SAI_IT.CD_FILIAL = @CD_FILIAL
		--				AND EST_NF_ENT_NF_SAI.CD_NF_ENT = @CD_NF
		--				AND EST_NF_SAI.STS_NF = 0);

					   
		-- SET @QT_DEV_CANCEL =( SELECT SUM(EST_NF_ENT_IT.QT_IT) AS QT_IT_MOV_CANCEL
		--						FROM
		--							EST_NF_ENT
		--							INNER JOIN EST_NF_ENT_IT ON EST_NF_ENT.CD_EMP		 = EST_NF_ENT_IT.CD_EMP
		--														AND EST_NF_ENT.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL
		--														AND EST_NF_ENT.CD_NF	 = EST_NF_ENT_IT.CD_NF
		--														AND EST_NF_ENT.DT_ENT	 = EST_NF_ENT_IT.DT_ENT

		--							INNER JOIN EST_NF_SAI_NF_ENT ON EST_NF_ENT.CD_EMP = EST_NF_SAI_NF_ENT.CD_EMP
		--															AND EST_NF_ENT.CD_FILIAL = EST_NF_SAI_NF_ENT.CD_FILIAL
		--															AND EST_NF_ENT.CD_NF = EST_NF_SAI_NF_ENT.CD_NF_ENT

		--							INNER JOIN EST_NF_ENT_NF_SAI ON EST_NF_SAI_NF_ENT.CD_EMP = EST_NF_ENT_NF_SAI.CD_EMP
		--															AND EST_NF_SAI_NF_ENT.CD_FILIAL = EST_NF_ENT_NF_SAI.CD_FILIAL
		--															AND EST_NF_SAI_NF_ENT.CD_NF_SAI = EST_NF_ENT_NF_SAI.CD_NF_SAI
		--						WHERE		EST_NF_ENT_IT.CD_EMP	= @CD_EMP
		--								AND EST_NF_ENT_IT.CD_FILIAL = @CD_FILIAL
		--								AND EST_NF_ENT_IT.CD_NF		= @CD_NF
		--								AND EST_NF_ENT.STS_NF = 0);


		-- SET @QT_DEV = ISNULL(@QT_DEV,0) - ISNULL(@QT_DEV_CANCEL,0);
		 
		-- SET @STS_NF = CASE
		--				   WHEN @QT_DEV = 0 THEN 'NORMAL'
		--				   WHEN ISNULL(@QT_ENT,0) - @QT_DEV = 0 THEN 'DEVOLUÇÃO TOTAL'
		--				   ELSE 'DEVOLUÇÃO PARCIAL'
		--			   END;


		 RETURN(@STS_NF);
	 END;
GO


