IF EXISTS (SELECT * FROM sys.objects WHERE  object_id = OBJECT_ID(N'[P_RESERVA_CREDITO_ICMS_ST_PIS_COFINS]') AND type IN(N'P', N'PC')
)
	DROP PROCEDURE [P_RESERVA_CREDITO_ICMS_ST_PIS_COFINS];
GO

CREATE PROCEDURE [P_RESERVA_CREDITO_ICMS_ST_PIS_COFINS]
    @CD_EMP INT,
    @CD_FILIAL INT,
    @TIPO_DOC INT,
    @CD_DOC INT,
    @QT_IT INT,
    @CD_PROD BIGINT,
	@CD_IT INT = NULL
AS
BEGIN

		DELETE FROM EST_NF_ENT_ENTRADA_ICMS_ST_MOV 
		WHERE CD_EMP = @CD_EMP
		and CD_FILIAL = @CD_FILIAL
		and CD_DOC = @CD_DOC
		and CD_PROD = @CD_PROD
		AND CD_IT = COALESCE(@CD_IT,CD_IT)/*O PDV passa pela mesma procedure varias vezes para a MESMA sessão. Seja ao aplicar um desconto, seja ao alterar quantidade.
											Com isso, aplicamos essa rotina para não abater do saldo, quando se trata da mesma "venda" para o mesmo "produto"*/


		DECLARE cursor_nfe CURSOR FOR
				SELECT 
					CD_NF, 
					SUM(QT_IT - ISNULL(Utilizado.QT_IT_UTILIZADO,0)) AS QT_IT, 
					SUM(VLR_ICMS_ST_DARE) / SUM(QT_IT) AS VLR_ICMS_ST_DARE,  
					SUM(VLR_ICMS_EFETIVO) / SUM(QT_IT) AS VLR_ICMS_EFETIVO, 
					SUM(VLR_ICMS_SUBS) / SUM(QT_IT) AS VLR_ICMS_SUBS, 
					SUM(VLR_ICMS_RET) / SUM(QT_IT) AS VLR_ICMS_RET,
					SUM(VLR_FECOP) / SUM(QT_IT) AS VLR_FECOP,
					SUM(VLR_FECOP_RET) / SUM(QT_IT) AS VLR_FECOP_RET,
					SUM(VLR_FECOP_ST) / SUM(QT_IT) AS VLR_FECOP_ST
				FROM EST_NF_ENT_CREDITO_ICMS_ST_PIS_COFINS P
				OUTER APPLY (SELECT SUM(QT_IT) as QT_IT_UTILIZADO
							 FROM EST_NF_ENT_ENTRADA_ICMS_ST_MOV MOV
							 WHERE MOV.CD_EMP = P.CD_EMP
							 AND MOV.CD_FILIAL = P.CD_FILIAL
							 AND MOV.CD_PROD = P.CD_PROD
							 AND MOV.CD_NF_ENT_CRED = P.CD_NF
							 AND MOV.[STATUS] IN (0, 1, 3) /*Confirmado, Reservado e Estornado*/) Utilizado
				WHERE CD_FILIAL = @CD_FILIAL
				AND CD_PROD = @CD_PROD
				AND CD_EMP = @CD_EMP
				AND SALDO_ZERADO = 0
				GROUP BY CD_NF
				HAVING SUM(QT_IT - ISNULL(Utilizado.QT_IT_UTILIZADO,0)) > 0
				ORDER BY CD_NF ASC


	-- Abertura do cursor
	OPEN cursor_nfe
	DECLARE 
		@CD_NF_ITERACAO INT, 
		@QT_IT_ITERACAO DECIMAL(18, 2), 
		@VLR_ICMS_ST_DARE DECIMAL(18, 2),  
		@VLR_ICMS_EFETIVO DECIMAL(18, 2), 
		@VLR_ICMS_SUBS DECIMAL(18, 2), 
		@VLR_ICMS_RET DECIMAL(18, 2),
		@VLR_FECOP DECIMAL(18, 2),
		@VLR_FECOP_RET DECIMAL(18, 2),
		@VLR_FECOP_ST DECIMAL(18, 2)

	DECLARE @SALDO_FINAL INT = @QT_IT; /*10*/
	DECLARE @QTD_IT_UTILIZADO INT;
	DECLARE @TEMP_TABLE AS TABLE (ID INT)

	-- Fetch a primeira linha
	FETCH NEXT FROM cursor_nfe INTO 
		@CD_NF_ITERACAO, 
		@QT_IT_ITERACAO, 
		@VLR_ICMS_ST_DARE,  
		@VLR_ICMS_EFETIVO, 
		@VLR_ICMS_SUBS, 
		@VLR_ICMS_RET,
		@VLR_FECOP,
		@VLR_FECOP_RET,
		@VLR_FECOP_ST

	-- Loop através das linhas retornadas pelo cursor
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Faça o processamento necessário para cada linha aqui

		SET @QTD_IT_UTILIZADO = @QT_IT_ITERACAO;
	
		IF @QTD_IT_UTILIZADO > @SALDO_FINAL
		BEGIN
			SET @QTD_IT_UTILIZADO = @SALDO_FINAL;
		END

		SET @SALDO_FINAL = @SALDO_FINAL - @QTD_IT_UTILIZADO;
	

			INSERT INTO EST_NF_ENT_ENTRADA_ICMS_ST_MOV (CD_EMP
														,CD_FILIAL
														,CD_NF_ENT_CRED
														,TIPO_DOC
														,CD_DOC
														,CD_PROD
														,QT_IT
														,VLR_ICMS_ST_DARE
														,VLR_ICMS_EFETIVO
														,VLR_ICMS_SUBS
														,VLR_ICMS_RET
														,VLR_FECOP
														,VLR_FECOP_RET
														,VLR_FECOP_ST
														,STATUS
														,PROT_ICMS_ST_SAIDA
														,DT_CAD
														,CD_IT)
			VALUES (@CD_EMP, @CD_FILIAL, @CD_NF_ITERACAO, @TIPO_DOC, @CD_DOC, @CD_PROD, @QTD_IT_UTILIZADO, @VLR_ICMS_ST_DARE,  
					@VLR_ICMS_EFETIVO,
					@VLR_ICMS_SUBS, 
					@VLR_ICMS_RET,
					@VLR_FECOP,
					@VLR_FECOP_RET,
					@VLR_FECOP_ST, 0, null, GETDATE(), @CD_IT)


			INSERT INTO @TEMP_TABLE
			SELECT SCOPE_IDENTITY()

			IF @SALDO_FINAL <= 0
			BEGIN
				BREAK;
			END

		-- Fetch a próxima linha
		FETCH NEXT FROM cursor_nfe INTO 
			@CD_NF_ITERACAO, 
			@QT_IT_ITERACAO, 
			@VLR_ICMS_ST_DARE,  
			@VLR_ICMS_EFETIVO, 
			@VLR_ICMS_SUBS, 
			@VLR_ICMS_RET,
			@VLR_FECOP,
			@VLR_FECOP_RET,
			@VLR_FECOP_ST
	END

	CLOSE cursor_nfe
	DEALLOCATE cursor_nfe


	SELECT MOV.PROT_ICMS_ST, 
	(SUM(MOV.VLR_ICMS_ST_DARE) * SUM(MOV.QT_IT)   
	+ (SUM(MOV.VLR_ICMS_EFETIVO) * SUM(MOV.QT_IT))
	+ (SUM(MOV.VLR_ICMS_SUBS) * SUM(MOV.QT_IT) )
	+ (SUM(MOV.VLR_ICMS_RET) * SUM(MOV.QT_IT))
	+ (SUM(MOV.VLR_FECOP) * SUM(MOV.QT_IT))
	+ (SUM(MOV.VLR_FECOP_RET) * SUM(MOV.QT_IT) )
	+ (SUM(MOV.VLR_FECOP_ST) * SUM(MOV.QT_IT))
	) AS VLR_DEDUZIR
	FROM @TEMP_TABLE T
	INNER JOIN EST_NF_ENT_ENTRADA_ICMS_ST_MOV MOV ON T.ID = MOV.PROT_ICMS_ST
	GROUP BY MOV.PROT_ICMS_ST
END;
