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

--EXEC P_GERA_PIS_COFINS_PDV_VD @CD_EMP,@CD_FILIAL,@DT_INI,@DT_FIM,0,0,0,@CD_VD
/*
=======================================================================================
'Job.................:  
'Função..............: 
'Objetivo............:  
'Tabelas.............: 
'Dt Modificação......: 25/05/2021
'Modificação.........: Tag #A001 Atualizar PIS_COFINS de acordo com TP_TRIB da tabela CST_PIS_COFINS
'=======================================================================================
Tag #A001 - Alteração do codigo  
Tag #I001 - Inclusão de novo codigo 
*/
CREATE PROCEDURE [dbo].[P_GERA_PIS_COFINS_PDV_VD](@CD_EMP           INT
													, @CD_FILIAL        INT
													, @DT_INI           DATETIME
													, @DT_FIM           DATETIME
													, @VISUALIZAR       INT      = 0
													, @FLAG_REPROCESSAR INT      = 0
													, @CD_PROD          INT      = 0
													, @CD_VD            INT      = 0
													)

WITH ENCRYPTION

AS

SET NOCOUNT ON 
	DECLARE @DEDUZIR_ICMS_NORMAL_BASE_PISCOFINS INT = NULL 
	DECLARE @DATADEDUZIR DATE = NULL

		SET @DEDUZIR_ICMS_NORMAL_BASE_PISCOFINS= ISNULL ( (SELECT DEDUZIR_ICMS_NORMAL_BASE_PISCOFINS FROM PRC_FILIAL WHERE CD_FILIAL = @CD_FILIAL ), 0)
		SET @DATADEDUZIR = ISNULL((SELECT CONVERT(VARCHAR,DBO.UFD_BUSCA_PARAM_SISTEMA(1, 'DT_INICIO_EXCLUSAO_ICMS_PIS_COFINS', 1, 1),103)),0)


	 BEGIN 

	 set nocount on;
	--Verifica bloqueio contábil
	IF EXISTS
	(
		SELECT TOP 1
				CD_EMP
		FROM
				CTB_CONTROLE_EXPORTACAO
		WHERE LIBERADO = 'N'
				AND DT_EXPORT >= @DT_INI
				AND CD_EMP = @CD_EMP
				AND CD_FILIAL = @CD_FILIAL
	)
    BEGIN
        PRINT 'Data base com bloqueio contabil - Time : '+CONVERT(VARCHAR, GETDATE(), 108);
        RAISERROR('Data base com bloqueio contabil', 9, 1);
        RETURN;
    END;

--Verifica bloqueio fiscal
		 IF EXISTS(SELECT TOP 1
							   CD_EMP
						FROM
							CTB_EXP_CTR
						WHERE  LIBERADO = 'N'
							   AND CD_EMP = @CD_EMP
							   AND DT_EXP >= @DT_INI
							   AND CD_FILIAL = @CD_FILIAL
							   AND TP_EXP = 0
					)
			 BEGIN
				 PRINT 'Data base com bloqueio contabil - Time : '+CONVERT(VARCHAR, GETDATE(), 108);
				 RAISERROR('Data base com bloqueio fiscal', 9, 1);
				 RETURN;
			 END;

/**************************************************************************************************************************/

		 CREATE TABLE #RsSaidasPdv	( CD_EMP                     INT
									, CD_FILIAL                  INT
									, CD_VD                      INT
									, CD_IT                      INT
									, CD_PROD                    INT
									, PIS_COFINS_RECALCULADO     INT
									, LISTA_PROD                 INT
									, TP_TRANSA                  INT
									, PIS_COFINS                 INT
									, TOTALOPERACAO              MONEY
									, TIPO_TRIBUTACAO_PIS_COFINS INT
									, CST_PIS                    INT
									, PERC_ALIQ_PIS              MONEY
									, VLR_BASE_PIS               MONEY
									, VLR_PIS                    MONEY
									, CST_COFINS                 INT
									, PERC_ALIQ_COFINS           MONEY
									, VLR_BASE_COFINS            MONEY
									, VLR_COFINS                 MONEY
									, VLR_PROD_BASE_IMPOSTO      MONEY
									, REGIME_TRIBUTARIO			 INT
									, CD_NT_OP INT
									);

----------------------------------------------------------------------------------------------------------------
		--Gerando tabela inicial com o calculo padrao de PIS/COFINS
		 PRINT 'P_GERA_PIS_COFINS_PDV_VD - INSERT 1 '+CONVERT(VARCHAR, GETDATE(), 108);
		 IF @FLAG_REPROCESSAR = 1
			 BEGIN
				 INSERT INTO #RsSaidasPdv
				 SELECT
						PDV_VD_IT.CD_EMP
					  , PDV_VD_IT.CD_FILIAL
					  , PDV_VD_IT.CD_VD
					  , PDV_VD_IT.CD_IT
					  , PDV_VD_IT.CD_PROD
					  , 1 AS PIS_COFINS_RECALCULADO
					  , PDV_VD_IT.LISTA_PROD
					  , 1 AS TP_MOV_OP_TRANSA
					  , PDV_VD_IT.PIS_COFINS
					  , (TOT_IT - VLR_DESC_TOT_RAT) AS TOTALOPERACAO
					  , ISNULL(PDV_VD_IT.TIPO_TRIBUTACAO_PIS_COFINS, 0)
					  , 0 CD_CST_PIS
					  , 0 PERC_PIS
					  , 0 VLR_BASE_PIS
					  , 0 VLR_PIS
					  , 0 CD_CST_COFINS
					  , 0 PERC_COFINS
					  , 0 VLR_BASE_COFINS
					  , 0 VLR_COFINS
					  , CASE
							WHEN(PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT) < 0 THEN 0
							ELSE (PDV_VD_IT.TOT_IT - ( PDV_VD_IT.VLR_DESC_TOT_RAT + ISNULL((CASE WHEN @DEDUZIR_ICMS_NORMAL_BASE_PISCOFINS = 1 AND ISNULL(FLAG_NAO_DEDUZ_ICMS_BASE_PIS_COFINS,0) = 0 AND PDV_VD_IT.DT_VD > =  @DATADEDUZIR THEN ROUND(((PDV_VD_IT.TOT_IT - ISNULL(PDV_VD_IT.VLR_DESC_TOT_RAT,0)) * PDV_VD_IT.PERC_ICMS/100),4) ELSE 0 END),0) ) )
						END VLR_PROD_BASE_IMPOSTO,
						ISNULL(PDV_VD.REGIME_TRIBUTARIO,0)
					  ,ISNULL(PDV_VD_IT.CD_NT_OP,0) AS CD_NT_OP
				 FROM
					  PDV_VD_IT INNER JOIN PDV_VD ON 
						PDV_VD.CD_EMP = PDV_VD_IT.CD_EMP AND
						PDV_VD.CD_FILIAL = PDV_VD_IT.CD_FILIAL AND
						PDV_VD.CD_VD = PDV_VD_IT.CD_VD AND
						PDV_VD.DT_VD = PDV_VD_IT.DT_VD
						LEFT JOIN GLB_NT_OP OP ON OP.CD_NT_OP = PDV_VD_IT.CD_NT_OP
				 WHERE  PDV_VD_IT.CD_EMP = @CD_EMP
						AND PDV_VD_IT.CD_FILIAL = @CD_FILIAL
						AND PDV_VD_IT.DT_VD BETWEEN @DT_INI AND @DT_FIM
						AND PDV_VD_IT.CD_PROD = CASE
													WHEN @CD_PROD > 0 THEN @CD_PROD
													ELSE PDV_VD_IT.CD_PROD
												END
						AND PDV_VD_IT.CD_VD = CASE
												  WHEN @CD_VD > 0 THEN @CD_VD
												  ELSE PDV_VD_IT.CD_VD
											  END OPTION(
														 RECOMPILE);


-- Tag #A001														 
				 UPDATE u
				   SET
					   CST_PIS = b.CD_CST_PIS
					 , PERC_ALIQ_PIS = b.PERC_PIS
					 , VLR_BASE_PIS = b.VLR_BASE_PIS
					 , VLR_PIS = b.VLR_PIS
					 , CST_COFINS = b.CD_CST_COFINS
					 , PERC_ALIQ_COFINS = b.PERC_COFINS
					 , VLR_BASE_COFINS = b.VLR_BASE_COFINS
					 , VLR_COFINS = b.VLR_COFINS
					 --, PIS_COFINS = B.PROD_PIS_COFINS
					 , PIS_COFINS = B.CST_TP_TRIB_PIS
				 FROM   #RsSaidasPdv u
						OUTER APPLY UFD_EST_PROD_TRIB_PIS_COFINS(u.CD_EMP, u.CD_FILIAL, 0, u.CD_PROD, U.CD_NT_OP, u.VLR_PROD_BASE_IMPOSTO, u.REGIME_TRIBUTARIO) B;
			 END;
			 ELSE
			 BEGIN
				 INSERT INTO #RsSaidasPdv
				 SELECT
						PDV_VD_IT.CD_EMP
					  , PDV_VD_IT.CD_FILIAL
					  , PDV_VD_IT.CD_VD
					  , PDV_VD_IT.CD_IT
					  , PDV_VD_IT.CD_PROD
					  , 1 AS PIS_COFINS_RECALCULADO
					  , PDV_VD_IT.LISTA_PROD
					  , 1 AS TP_MOV_OP_TRANSA
					  , PDV_VD_IT.PIS_COFINS
					  , (TOT_IT - VLR_DESC_TOT_RAT) AS TOTALOPERACAO
					  , ISNULL(PDV_VD_IT.TIPO_TRIBUTACAO_PIS_COFINS, 0) TIPO_TRIBUTACAO_PIS_COFINS
					  , 0 CD_CST_PIS
					  , 0 PERC_PIS
					  , 0 VLR_BASE_PIS
					  , 0 VLR_PIS
					  , 0 CD_CST_COFINS
					  , 0 PERC_COFINS
					  , 0 VLR_BASE_COFINS
					  , 0 VLR_COFINS
					  , CASE
							WHEN(PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT) < 0 THEN 0
							ELSE(PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT) 
						END VLR_PROD_BASE_IMPOSTO,
						ISNULL(PDV_VD.REGIME_TRIBUTARIO,0)
					  ,ISNULL(PDV_VD_IT.CD_NT_OP,0) AS CD_NT_OP
				 FROM
					 PDV_VD_IT INNER JOIN PDV_VD ON 
						PDV_VD.CD_EMP = PDV_VD_IT.CD_EMP AND
						PDV_VD.CD_FILIAL = PDV_VD_IT.CD_FILIAL AND
						PDV_VD.CD_VD = PDV_VD_IT.CD_VD AND
						PDV_VD.DT_VD = PDV_VD_IT.DT_VD
				 WHERE  PDV_VD_IT.CD_EMP = @CD_EMP
						AND PDV_VD_IT.CD_FILIAL = @CD_FILIAL
						AND NOT ISNULL(PDV_VD_IT.PIS_COFINS_RECALCULADO,0) = 1
						AND PDV_VD_IT.DT_VD BETWEEN @DT_INI AND @DT_FIM
						AND PDV_VD_IT.CD_PROD = CASE
													WHEN @CD_PROD > 0 THEN @CD_PROD
													ELSE PDV_VD_IT.CD_PROD
												END
						AND PDV_VD_IT.CD_VD = CASE
												  WHEN @CD_VD > 0 THEN @CD_VD
												  ELSE PDV_VD_IT.CD_VD
											  END OPTION(
														 RECOMPILE);
-- Tag #A002														 
				 UPDATE u
				   SET
					   CST_PIS = b.CD_CST_PIS
					 , PERC_ALIQ_PIS = b.PERC_PIS
					 , VLR_BASE_PIS = b.VLR_BASE_PIS
					 , VLR_PIS = b.VLR_PIS
					 , CST_COFINS = b.CD_CST_COFINS
					 , PERC_ALIQ_COFINS = b.PERC_COFINS
					 , VLR_BASE_COFINS = b.VLR_BASE_COFINS
					 , VLR_COFINS = b.VLR_COFINS
					 --, PIS_COFINS = B.PROD_PIS_COFINS
					 , PIS_COFINS = B.CST_TP_TRIB_PIS
				 FROM   #RsSaidasPdv u
						OUTER APPLY UFD_EST_PROD_TRIB_PIS_COFINS(u.CD_EMP, u.CD_FILIAL, 0, u.CD_PROD, U.CD_NT_OP, u.VLR_PROD_BASE_IMPOSTO, ISNULL(u.REGIME_TRIBUTARIO,0)) B;
			 END;
----------------------------------------------------------------------------------------------------------------

		--Alterando a entrada de acordo com o novo calculo criado
		 IF @VISUALIZAR = 0
			 BEGIN
				 PRINT 'UPDATE 2 '+CONVERT(VARCHAR, GETDATE(), 108);
				 UPDATE s
				   SET
					   s.VLR_BASE_PIS = snv.VLR_BASE_PIS
					 , s.VLR_PIS = snv.VLR_PIS
					 , s.PERC_ALIQ_PIS = snv.PERC_ALIQ_PIS
					 , s.VLR_BASE_COFINS = snv.VLR_BASE_COFINS
					 , s.VLR_COFINS = snv.VLR_COFINS
					 , s.PERC_ALIQ_COFINS = snv.PERC_ALIQ_COFINS
					 , s.CST_PIS = snv.CST_PIS
					 , s.CST_COFINS = snv.CST_COFINS
					 , s.PIS_COFINS_RECALCULADO = snv.PIS_COFINS_RECALCULADO
					 , s.PIS_COFINS = snv.PIS_COFINS
				 FROM   #RsSaidasPdv snv
						INNER JOIN PDV_VD_IT s ON snv.CD_EMP = s.CD_EMP
												  AND snv.CD_FILIAL = s.CD_FILIAL
												  AND snv.CD_VD = s.CD_VD
												  AND snv.CD_IT = s.CD_IT;
			 END;

----------------------------------------------------------------------------------------------------------------
-- Retirado porque roda em job.
--IF  @VISUALIZAR =0
--	BEGIN
--		WHILE @DT_INI <=@DT_FIM
--			BEGIN
--				EXEC P_EST_MOVIMENTO_VENDAS 1, @DT_INI, @CD_FILIAL ,1
--				SET @DT_INI = DATEADD(DAY,1,@DT_INI) 
--			END
--	END
----------------------------------------------------------------------------------------------------------------
	IF @VISUALIZAR = 1
		BEGIN
			PRINT 'SELECT 3 '+CONVERT(VARCHAR, GETDATE(), 108);
		--Alterando a entrada de acordo com o novo calculo criado
			SELECT CD_EMP                    
					,CD_FILIAL                 
					,CD_VD                     
					,CD_IT                     
					,CD_PROD                   
					,PIS_COFINS_RECALCULADO    
					,LISTA_PROD                
					,TP_TRANSA                 
					,PIS_COFINS                
					,TOTALOPERACAO             
					,TIPO_TRIBUTACAO_PIS_COFINS
					,CST_PIS                   
					,PERC_ALIQ_PIS             
					,VLR_BASE_PIS              
					,VLR_PIS                   
					,CST_COFINS                
					,PERC_ALIQ_COFINS          
					,VLR_BASE_COFINS           
					,VLR_COFINS                
					,VLR_PROD_BASE_IMPOSTO     
					,REGIME_TRIBUTARIO
			FROM
				#RsSaidasPdv;
		END;
	DROP TABLE #RsSaidasPdv;
	PRINT 'P_GERA_PIS_COFINS_PDV_VD FINALIZADA!';
END;
