--SELECT * FROM UFD_EST_NF_ENT_IT_SLD_DEV_FORN(1,2,11652,3,12497,288)

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

CREATE FUNCTION UFD_EST_NF_ENT_IT_SLD_DEV_FORN( @CD_EMP			INT,
												@CD_FILIAL		INT,
												@CD_PROD		INT,
												@QT_PROD		MONEY,
												@CD_ORIG		INT,
												@CD_MOV_OP_DEV	INT,
												@NR_LOTE		VARCHAR(MAX))
RETURNS @RS_RETURN TABLE (CD_EMP			INT,
						  CD_FILIAL			INT,
						  CD_NF				INT,
						  CD_PROD			INT,
						  QT_IT_NF_ENT		MONEY,
						  QT_SLD			MONEY,
						  NR_AUTORIZADOR	VARCHAR(50))
AS
	BEGIN
		DECLARE @TP_MOV_ENT INT
		DECLARE @TP_MOV_DEV INT
		DECLARE @CD_NF_ANT INT 
		DECLARE @IGNORA_SALDO_DEVOLUCAO INT
		DECLARE @CD_NF_AUX INT
		DECLARE @QTDE_AJUSTAR MONEY

		SET @IGNORA_SALDO_DEVOLUCAO = COALESCE((SELECT VALOR FROM PRC_EMP_CONFIG WHERE CD_EMP = 1 AND CD_CHAVE = 'FLAG_IGNORA_SALDO_DEVOLUCAO'), 0)

		DECLARE @RS_NF_DEV TABLE (CD_EMP		INT,
								  CD_FILIAL		INT,
								  CD_NF			INT,
								  CD_PROD		INT,
								  QT_IT			MONEY,
								  QT_SLD		MONEY)

		SET @TP_MOV_DEV = ISNULL((SELECT TP_MOV FROM GLB_MOV_OP WHERE CD_MOV_OP = @CD_MOV_OP_DEV),0) 

		SET @TP_MOV_ENT = (CASE @TP_MOV_DEV WHEN 11 THEN 0
											WHEN 15 THEN 10
											WHEN 31 THEN 0
											ELSE -1 END)

		SET @CD_NF_ANT = ISNULL((SELECT
									MAX(CD_NF)
								 FROM
									EST_NF_ENT
								 WHERE
									CD_EMP 			= @CD_EMP
									AND CD_FILIAL 	= @CD_FILIAL
									AND CD_FORN 	= @CD_ORIG), 0) + 1
 
		DECLARE @GLB_MOV_OP TABLE (CD_MOV_OP INT)

		INSERT INTO
			@GLB_MOV_OP
		SELECT
			CD_MOV_OP
		FROM
			GLB_MOV_OP
		WHERE
			GLB_MOV_OP.TP_MOV = @TP_MOV_ENT
			AND GLB_MOV_OP.TP_MOV_OP_TRANSA = 0;
		
		IF @IGNORA_SALDO_DEVOLUCAO = 2
		BEGIN

			INSERT INTO
				@RS_NF_DEV
			SELECT
				EST_NF_ENT_IT.CD_EMP,
				EST_NF_ENT_IT.CD_FILIAL,
				EST_NF_ENT_IT.CD_NF,
				EST_NF_ENT_IT.CD_PROD,
				EST_NF_ENT_IT.QT_IT,
				@QT_PROD AS QT_SLD

			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 @GLB_MOV_OP GLB_MOV_OP ON
					EST_NF_ENT.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
				LEFT JOIN EST_NF_ENT_IT_EST_PROD_CPL_LOTE ON
					EST_NF_ENT_IT.CD_EMP = EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP
					AND EST_NF_ENT_IT.CD_FILIAL = EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL
					AND EST_NF_ENT_IT.CD_NF = EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_NF
					AND EST_NF_ENT_IT.CD_PROD = EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_PROD
				LEFT JOIN EST_PROD_CPL_LOTE ON
					EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_PROD_CPL_LOTE.CD_EMP
					AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
					AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_LOTE = EST_PROD_CPL_LOTE.CD_LOTE
			WHERE
				EST_NF_ENT.CD_EMP = @CD_EMP
				AND EST_NF_ENT.CD_FILIAL = @CD_FILIAL
				AND EST_NF_ENT.CD_FORN = @CD_ORIG
				AND EST_NF_ENT_IT.CD_PROD = @CD_PROD
				AND EST_NF_ENT_IT.CD_NF < @CD_NF_ANT
				AND CASE WHEN @NR_LOTE = '' THEN '' ELSE COALESCE(EST_PROD_CPL_LOTE.NR_LOTE, '') END = @NR_LOTE
			ORDER BY
				DBO.BUSCA_EST_NF_ENT_IT_QT_DEV(EST_NF_ENT_IT.CD_EMP, EST_NF_ENT_IT.CD_FILIAL, EST_NF_ENT_IT.CD_NF, EST_NF_ENT_IT.CD_PROD) DESC;
			
			INSERT INTO 
				@RS_RETURN
			SELECT 
				TOP 1 RS_NF_DEV.CD_EMP,
				RS_NF_DEV.CD_FILIAL,
				RS_NF_DEV.CD_NF,
				RS_NF_DEV.CD_PROD,
				RS_NF_DEV.QT_IT,
				RS_NF_DEV.QT_SLD,
				EST_NF_ENT_DANFE_PROVISORIO.NR_AUTORIZADOR
			FROM 
				@RS_NF_DEV RS_NF_DEV
				INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT ON 
					RS_NF_DEV.CD_EMP		= EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_EMP
					AND RS_NF_DEV.CD_FILIAL = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_FILIAL
					AND RS_NF_DEV.CD_NF		= EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_NF
				INNER JOIN EST_NF_ENT_DANFE_PROVISORIO ON 
					EST_NF_ENT_DANFE_PROVISORIO.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_CTR
			ORDER BY RS_NF_DEV.CD_NF DESC


			INSERT INTO 
				@RS_RETURN
			SELECT 
				TOP 1  RS_NF_DEV.CD_EMP,
				RS_NF_DEV.CD_FILIAL,
				RS_NF_DEV.CD_NF,
				RS_NF_DEV.CD_PROD,
				RS_NF_DEV.QT_IT,
				RS_NF_DEV.QT_SLD,
				EST_NF_ENT_NFE_FORN_VALID.NR_AUTORIZADOR
			FROM
				@RS_NF_DEV RS_NF_DEV
				INNER JOIN EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID ON
					RS_NF_DEV.CD_EMP		= EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_EMP
					AND RS_NF_DEV.CD_FILIAL = EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_FILIAL
					AND RS_NF_DEV.CD_NF		= EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_NF
				INNER JOIN EST_NF_ENT_NFE_FORN_VALID ON
					EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_CTR = EST_NF_ENT_NFE_FORN_VALID.CD_CTR
			WHERE
				NOT EXISTS (
					SELECT
						RS.CD_EMP
					FROM
						@RS_RETURN RS
					WHERE
						RS.CD_EMP = RS_NF_DEV.CD_EMP
						AND RS.CD_FILIAL = RS_NF_DEV.CD_FILIAL
						AND RS.CD_PROD = RS_NF_DEV.CD_PROD)
			ORDER BY RS_NF_DEV.CD_NF DESC

		END ELSE
		BEGIN

			WHILE ISNULL((SELECT COUNT(1) FROM @RS_NF_DEV HAVING SUM(QT_SLD) >= @QT_PROD),0) = 0
				BEGIN
				
					INSERT INTO
						@RS_NF_DEV
					SELECT
						TOP 1
						EST_NF_ENT_IT.CD_EMP,
						EST_NF_ENT_IT.CD_FILIAL,
						EST_NF_ENT_IT.CD_NF,
						EST_NF_ENT_IT.CD_PROD,
						EST_NF_ENT_IT.QT_IT,

						CASE WHEN @NR_LOTE = '' THEN
							EST_NF_ENT_IT.QT_IT
						ELSE
							EST_NF_ENT_IT_EST_PROD_CPL_LOTE.QTDE_PROD
						END - DBO.BUSCA_EST_NF_ENT_IT_QT_DEV(EST_NF_ENT_IT.CD_EMP, EST_NF_ENT_IT.CD_FILIAL, EST_NF_ENT_IT.CD_NF, EST_NF_ENT_IT.CD_PROD) AS QT_SLD

					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 @GLB_MOV_OP GLB_MOV_OP ON
							EST_NF_ENT.CD_NT_MOV = GLB_MOV_OP.CD_MOV_OP
						LEFT JOIN EST_NF_ENT_IT_EST_PROD_CPL_LOTE ON
							EST_NF_ENT_IT.CD_EMP = EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP
							AND EST_NF_ENT_IT.CD_FILIAL = EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL
							AND EST_NF_ENT_IT.CD_NF = EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_NF
							AND EST_NF_ENT_IT.CD_PROD = EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_PROD
						LEFT JOIN EST_PROD_CPL_LOTE ON
							EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_PROD_CPL_LOTE.CD_EMP
							AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
							AND EST_NF_ENT_IT_EST_PROD_CPL_LOTE.CD_LOTE = EST_PROD_CPL_LOTE.CD_LOTE
					WHERE
						EST_NF_ENT.CD_EMP = @CD_EMP
						AND EST_NF_ENT.CD_FILIAL = @CD_FILIAL
						AND EST_NF_ENT.CD_FORN = @CD_ORIG
						AND EST_NF_ENT_IT.CD_PROD = @CD_PROD
						AND EST_NF_ENT_IT.CD_NF < @CD_NF_ANT
						AND CASE WHEN @NR_LOTE = '' THEN '' ELSE COALESCE(EST_PROD_CPL_LOTE.NR_LOTE, '') END = @NR_LOTE
						--AND EST_PROD_CPL_LOTE.NR_LOTE = CASE WHEN @NR_LOTE = '' THEN EST_PROD_CPL_LOTE.NR_LOTE ELSE @NR_LOTE END 
					ORDER BY
						EST_NF_ENT_IT.CD_NF DESC;
						
					DELETE FROM
						@RS_NF_DEV
					WHERE
						QT_SLD <= 0;
								
					--SAI DO LOOP QUANDO O INSERT ACIMA NAO ENCONTRA MAIS NOTAS DO EMITENTE
					IF @CD_NF_ANT = (SELECT MIN(CD_NF) FROM @RS_NF_DEV)
						BREAK
					ELSE
						SET @CD_NF_ANT = ISNULL((SELECT MIN(CD_NF) FROM @RS_NF_DEV),0)
				
					IF @CD_NF_ANT = 0
						BREAK
				
					

					IF (SELECT 1 AS RET FROM @RS_NF_DEV HAVING SUM(QT_SLD) >= @QT_PROD) > 0
						BREAK
					ELSE
						CONTINUE
				END
		


			DELETE FROM @RS_NF_DEV WHERE QT_SLD = 0
		 
			IF ISNULL((SELECT 1 AS RET FROM @RS_NF_DEV HAVING SUM(QT_SLD) >= @QT_PROD),0) = 0 AND @IGNORA_SALDO_DEVOLUCAO = 0
				BEGIN
					DELETE FROM @RS_NF_DEV
				END

			UPDATE 
				RS_NF_DEV 
			SET
				QT_SLD = QT_SLD - (ISNULL((SELECT SUM(QT_SLD) FROM @RS_NF_DEV),0) - @QT_PROD)
			FROM 
				@RS_NF_DEV RS_NF_DEV
			WHERE 
				CD_NF = @CD_NF_ANT
		
			INSERT INTO 
				@RS_RETURN
			SELECT 
				RS_NF_DEV.CD_EMP,
				RS_NF_DEV.CD_FILIAL,
				RS_NF_DEV.CD_NF,
				RS_NF_DEV.CD_PROD,
				RS_NF_DEV.QT_IT,
				RS_NF_DEV.QT_SLD,
				EST_NF_ENT_DANFE_PROVISORIO.NR_AUTORIZADOR
			FROM 
				@RS_NF_DEV RS_NF_DEV
				INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT ON 
					RS_NF_DEV.CD_EMP		= EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_EMP
					AND RS_NF_DEV.CD_FILIAL = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_FILIAL
					AND RS_NF_DEV.CD_NF		= EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_NF
				INNER JOIN EST_NF_ENT_DANFE_PROVISORIO ON 
					EST_NF_ENT_DANFE_PROVISORIO.CD_CTR = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_CTR

			INSERT INTO 
				@RS_RETURN
			SELECT 
				RS_NF_DEV.CD_EMP,
				RS_NF_DEV.CD_FILIAL,
				RS_NF_DEV.CD_NF,
				RS_NF_DEV.CD_PROD,
				RS_NF_DEV.QT_IT,
				RS_NF_DEV.QT_SLD,
				EST_NF_ENT_NFE_FORN_VALID.NR_AUTORIZADOR
			FROM
				@RS_NF_DEV RS_NF_DEV
				INNER JOIN EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID ON
					RS_NF_DEV.CD_EMP		= EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_EMP
					AND RS_NF_DEV.CD_FILIAL = EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_FILIAL
					AND RS_NF_DEV.CD_NF		= EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_NF
				INNER JOIN EST_NF_ENT_NFE_FORN_VALID ON
					EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_CTR = EST_NF_ENT_NFE_FORN_VALID.CD_CTR
			WHERE
				NOT EXISTS (
					SELECT
						RS.CD_EMP
					FROM
						@RS_RETURN RS
					WHERE
						RS.CD_EMP = RS_NF_DEV.CD_EMP
						AND RS.CD_FILIAL = RS_NF_DEV.CD_FILIAL
						AND RS.CD_NF = RS_NF_DEV.CD_NF)

			IF ISNULL((SELECT 1 AS RET FROM @RS_RETURN HAVING SUM(QT_SLD) >= @QT_PROD), 0) = 0
			BEGIN
				IF @IGNORA_SALDO_DEVOLUCAO = 1
				BEGIN
					SELECT
						@CD_NF_AUX = MAX(CD_NF),
						@QTDE_AJUSTAR = @QT_PROD - SUM(QT_SLD)
					FROM
						@RS_RETURN
				
					UPDATE
						@RS_RETURN
					SET
						QT_SLD = @QTDE_AJUSTAR
					WHERE
						CD_EMP = 1
						AND CD_FILIAL = @CD_FILIAL
						AND CD_NF = @CD_NF_AUX
				END ELSE
				BEGIN
					DELETE FROM @RS_RETURN
				END
			END

			DELETE FROM @RS_RETURN WHERE QT_SLD <= 0;

		END

		RETURN

	END
