DECLARE @Versao VARCHAR(4)

SET @Versao = CASE 
                 WHEN LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR), 2) = '15' THEN '2019'
                 WHEN LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR), 2) = '14' THEN '2017'
                 WHEN LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR), 2) = '13' THEN '2016'
                 WHEN LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR), 2) = '12' THEN '2014'
                 WHEN LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR), 2) = '11' THEN '2012'
                 WHEN LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR), 2) = '10' THEN '2008'
                 WHEN LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR), 2) = '09' THEN '2005'
                 ELSE '2000'
              END
			  
IF CONVERT(INT,@Versao) >= 2012
BEGIN

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

	CREATE PROCEDURE P_ESTORNA_CREDITO_ICMS_ST_PIS_COFINS
		@PROT_ICMS_ST INT,
		@CD_EMP INT,
		@CD_FILIAL INT,
		@TIPO_DOC INT,
		@CD_DOC INT,
		@QT_IT INT,
		@CD_PROD INT
	AS
	BEGIN

		IF EXISTS (SELECT 1 FROM EST_NF_ENT_ENTRADA_ICMS_ST_MOV 
					WHERE PROT_ICMS_ST = @PROT_ICMS_ST 
					AND STATUS = 1)
		BEGIN

			DECLARE @QT_CREDITO_USADO INT = 0 
			DECLARE @QT_CREDITO_JA_DEVOLVIDO INT = 0 

			SET @QT_CREDITO_USADO = ISNULL((select SUM(QT_IT) FROM EST_NF_ENT_ENTRADA_ICMS_ST_MOV WHERE PROT_ICMS_ST = @PROT_ICMS_ST AND STATUS = 1),0)
			SET @QT_CREDITO_JA_DEVOLVIDO = ISNULL((select -SUM(QT_IT) FROM EST_NF_ENT_ENTRADA_ICMS_ST_MOV WHERE PROT_ICMS_ST_SAIDA = @PROT_ICMS_ST AND STATUS = 3),0)

			IF (@QT_CREDITO_USADO > @QT_CREDITO_JA_DEVOLVIDO AND @QT_IT <= (@QT_CREDITO_USADO - @QT_CREDITO_JA_DEVOLVIDO))
			BEGIN
					DECLARE @NovoID BIGINT
					DECLARE @QT_SALDO_FINAL INT = @QT_IT
					DECLARE @QT_AUX INT = @QT_IT
					
					DECLARE cursor_icms CURSOR FOR
													SELECT CD_NF_ENT_CRED,
														   MOV.QT_IT - ISNULL(QT_DEV.QT_IT, 0) AS QT_IT,
														   VLR_ICMS_ST_DARE,
														   VLR_ICMS_EFETIVO,
														   VLR_ICMS_SUBS,
														   VLR_ICMS_RET,
														   VLR_FECOP,
														   VLR_FECOP_RET,
														   VLR_FECOP_ST,
														   PROT_ICMS_ST AS PROT_ICMS_ST_SAIDA
													FROM EST_NF_ENT_ENTRADA_ICMS_ST_MOV MOV
													OUTER APPLY (SELECT -SUM(MOV2.QT_IT) AS QT_IT
																 FROM EST_NF_ENT_ENTRADA_ICMS_ST_MOV MOV2
																 WHERE MOV2.PROT_ICMS_ST_SAIDA = @PROT_ICMS_ST 
																 AND MOV2.CD_NF_ENT_CRED = MOV.CD_NF_ENT_CRED
																 AND STATUS = 3) AS QT_DEV /*Credito ja DEVOLVIDO daquela Nota*/
													WHERE PROT_ICMS_ST = @PROT_ICMS_ST
													AND MOV.QT_IT - ISNULL(QT_DEV.QT_IT, 0) > 0
													ORDER BY CD_NF_ENT_CRED ASC;
													
					DECLARE @CD_NF_ENT_CRED 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),
							@PROT_ICMS_ST_SAIDA INT;

					OPEN cursor_icms;
					FETCH NEXT FROM cursor_icms INTO @CD_NF_ENT_CRED, @QT_IT_ITERACAO,
													@VLR_ICMS_ST_DARE, @VLR_ICMS_EFETIVO, @VLR_ICMS_SUBS,
													@VLR_ICMS_RET, @VLR_FECOP, @VLR_FECOP_RET, @VLR_FECOP_ST,
													@PROT_ICMS_ST_SAIDA;

					WHILE @@FETCH_STATUS = 0
					BEGIN
						
						IF @QT_SALDO_FINAL <= 0
						BEGIN
							BREAK;
						END

						IF @QT_IT_ITERACAO >= @QT_SALDO_FINAL
						BEGIN
							SET @QT_AUX = @QT_SALDO_FINAL
							SET @QT_SALDO_FINAL = 0
						END
						ELSE 
						BEGIN
							SET @QT_SALDO_FINAL = @QT_SALDO_FINAL - @QT_IT_ITERACAO
							SET @QT_AUX = @QT_IT_ITERACAO
						END
						
						IF @NovoID IS NULL
						BEGIN
							SET @NovoID = NEXT VALUE FOR SequenceProtocoloICMS;
						END
						
						INSERT INTO EST_NF_ENT_ENTRADA_ICMS_ST_MOV (PROT_ICMS_ST
																			,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)
								SELECT  @NovoID
										,@CD_EMP
										,@CD_FILIAL
										,@CD_NF_ENT_CRED
										,@TIPO_DOC
										,@CD_DOC
										,@CD_PROD
										,@QT_AUX * (-1)
										,@VLR_ICMS_ST_DARE
										,@VLR_ICMS_EFETIVO
										,@VLR_ICMS_SUBS
										,@VLR_ICMS_RET
										,@VLR_FECOP
										,@VLR_FECOP_RET
										,@VLR_FECOP_ST
										,3
										,@PROT_ICMS_ST_SAIDA
										,GETDATE()
										
										
								UPDATE EST_NF_ENT_CREDITO_ICMS_ST_PIS_COFINS 
									SET SALDO_ZERADO = 0
								WHERE CD_NF = @CD_NF_ENT_CRED 
									AND CD_FILIAL = @CD_FILIAL 
									AND CD_EMP = @CD_EMP;

						FETCH NEXT FROM cursor_icms INTO  @CD_NF_ENT_CRED, @QT_IT_ITERACAO,
														@VLR_ICMS_ST_DARE, @VLR_ICMS_EFETIVO, @VLR_ICMS_SUBS,
														@VLR_ICMS_RET, @VLR_FECOP, @VLR_FECOP_RET, @VLR_FECOP_ST,
														@PROT_ICMS_ST_SAIDA;
					END

					CLOSE cursor_icms;
					DEALLOCATE cursor_icms;
					
			END
			ELSE
			BEGIN
				RAISERROR ('Já existe um estorno para este protocolo OU a quantidade devolvida não é maior que o Saldo utilizado!', 16, 1);
			END
		END
		ELSE
		BEGIN
			RAISERROR ('O protocolo não está confirmado!', 16, 1);
		END
	END
