IF NOT OBJECT_ID('P_EST_NF_ENT_IT_SLD_DEV_FORN') IS NULL
	BEGIN
		DROP PROCEDURE P_EST_NF_ENT_IT_SLD_DEV_FORN
	END
GO

CREATE PROCEDURE P_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))
AS
	BEGIN
		
SET NOCOUNT ON;
		DECLARE @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))
		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,
								  DT_NF			DATETIME)
		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,
				EST_NF_ENT.DT_ENT
			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
				EST_NF_ENT.DT_ENT DESC,
				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.DT_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.DT_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,
						EST_NF_ENT.DT_ENT
					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;
					--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
					DELETE FROM
						@RS_NF_DEV
					WHERE
						QT_SLD <= 0;
					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
		SET NOCOUNT OFF;
		SELECT * FROM @RS_RETURN;
	END


