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

/*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 20230824
'Job.................:  
'Nome................: P_GERA_PIS_COFINS_PDV_VD
'Objetivo............: 
'Tabelas.............: 
'Dt Criação..........: 24/08/2023
'Dt Ult Alteracão....: 28/08/2023
'Criado por..........: Marcos Barros
'Alterado por........: Gerson Reis
'=======================================================================================
*/

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
									, FLAG_NFCE INT
									, VLR_DEDUCAO_PROT_ICMS_ST MONEY
									);

----------------------------------------------------------------------------------------------------------------
		--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 ) )
						END VLR_PROD_BASE_IMPOSTO,
						ISNULL(PDV_VD.REGIME_TRIBUTARIO,0)
					  ,ISNULL(PDV_VD_IT.CD_NT_OP,0) AS CD_NT_OP
					  ,ISNULL(PDV_VD.FLAG_NFCE,0) AS FLAG_NFCE
					  ,ISNULL(((mov.VLR_ICMS_ST_DARE +
								 mov.VLR_ICMS_EFETIVO +
								 mov.VLR_ICMS_SUBS +
								 mov.VLR_ICMS_RET +
								 mov.VLR_FECOP +
								 mov.VLR_FECOP_RET +
								 mov.VLR_FECOP_ST) * MOV.QT_IT),0) AS VLR_DEDUCAO_PROT_ICMS_ST
					  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
						LEFT JOIN EST_NF_ENT_ENTRADA_ICMS_ST_MOV mov on PDV_VD_IT.prot_icms_st = MOV.prot_icms_st
																		AND MOV.STATUS = 1
				 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, u.CD_VD, 1, 0) 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
					  ,ISNULL(PDV_VD.FLAG_NFCE,0) AS FLAG_NFCE
					  ,ISNULL(((mov.VLR_ICMS_ST_DARE +
								 mov.VLR_ICMS_EFETIVO +
								 mov.VLR_ICMS_SUBS +
								 mov.VLR_ICMS_RET +
								 mov.VLR_FECOP +
								 mov.VLR_FECOP_RET +
								 mov.VLR_FECOP_ST) * MOV.QT_IT),0) AS VLR_DEDUCAO_PROT_ICMS_ST
				 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 EST_NF_ENT_ENTRADA_ICMS_ST_MOV mov on PDV_VD_IT.prot_icms_st = MOV.prot_icms_st
																		AND MOV.STATUS = 1
				 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), u.CD_VD, u.FLAG_NFCE, 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 = CASE WHEN snv.VLR_DEDUCAO_PROT_ICMS_ST < snv.VLR_BASE_PIS THEN snv.VLR_BASE_PIS - snv.VLR_DEDUCAO_PROT_ICMS_ST
											ELSE snv.VLR_BASE_PIS END
					 , s.VLR_PIS = CASE WHEN snv.VLR_DEDUCAO_PROT_ICMS_ST < snv.VLR_BASE_PIS THEN
											CASE WHEN snv.VLR_DEDUCAO_PROT_ICMS_ST > 0
													THEN ROUND((((snv.VLR_BASE_PIS - snv.VLR_DEDUCAO_PROT_ICMS_ST)) * snv.PERC_ALIQ_PIS) / 100.00, 2)
												ELSE snv.VLR_PIS END
										ELSE snv.VLR_PIS END
					 , s.PERC_ALIQ_PIS = snv.PERC_ALIQ_PIS
					 , s.VLR_BASE_COFINS = CASE WHEN snv.VLR_DEDUCAO_PROT_ICMS_ST < snv.VLR_BASE_COFINS THEN snv.VLR_BASE_COFINS - snv.VLR_DEDUCAO_PROT_ICMS_ST
												ELSE snv.VLR_BASE_COFINS END
					 , s.VLR_COFINS = CASE WHEN snv.VLR_DEDUCAO_PROT_ICMS_ST < snv.VLR_BASE_COFINS THEN 
												CASE WHEN snv.VLR_DEDUCAO_PROT_ICMS_ST > 0
														THEN ROUND((((snv.VLR_BASE_COFINS - snv.VLR_DEDUCAO_PROT_ICMS_ST)) * snv.PERC_ALIQ_COFINS) / 100.00, 2)
													ELSE snv.VLR_COFINS END
										ELSE snv.VLR_COFINS END
					 , 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;

-------------------------------------INCLUIR PENDENCIA DE PROCESSAMENTO "EST_MOVIMENTO_VENDAS"---------------------------------------------------------
-- 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
DECLARE @CD_CTR_PENDENCIA INT

WHILE @DT_INI <=@DT_FIM
BEGIN
	SET @CD_CTR_PENDENCIA = 0;
	
	SET @CD_CTR_PENDENCIA=ISNULL((SELECT
								 TOP 1 CD_CTR 
							FROM 
								GLB_DT_MOVIMENTO INNER JOIN EST_PROD_CPL_VD_LOTE_PROC_PENDENTE ON
								GLB_DT_MOVIMENTO.CD_CTR_DT_MOVIMENTO = EST_PROD_CPL_VD_LOTE_PROC_PENDENTE.CD_CTR_DT_MOVIMENTO
							WHERE
								GLB_DT_MOVIMENTO.DT_MOVIMENTO=@DT_INI),0)	
	IF @CD_CTR_PENDENCIA=0 
	BEGIN
		 INSERT INTO EST_PROD_CPL_VD_LOTE_PROC_PENDENTE
		 (CD_CTR_DT_MOVIMENTO,
		  DT_CAD,
		  ANO_MES,
		  ANO,
		  MES
		  ,ROTINA
		 )
		SELECT 
			GLB_DT_MOVIMENTO.CD_CTR_DT_MOVIMENTO,
			GETDATE(),
			GLB_DT_MOVIMENTO.ANO_MES_MOVIMENTO,
			GLB_DT_MOVIMENTO.ANO_MOVIMENTO,
			GLB_DT_MOVIMENTO.MES_MOVIMENTO,
			OBJECT_NAME(@@PROCID)
		FROM
			 GLB_DT_MOVIMENTO 
		WHERE
			GLB_DT_MOVIMENTO.DT_MOVIMENTO=@DT_INI

		SET @CD_CTR_PENDENCIA=IDENT_CURRENT('EST_PROD_CPL_VD_LOTE_PROC_PENDENTE');
	END

	INSERT INTO EST_PROD_CPL_VD_LOTE_PROC_PRC_FILIAL
				(CD_CTR,
				CD_EMP,
				CD_FILIAL
				)
				SELECT 
					DISTINCT @CD_CTR_PENDENCIA,
					PRC_FILIAL.CD_EMP,
					PRC_FILIAL.CD_FILIAL
				FROM 
					PRC_FILIAL LEFT OUTER JOIN EST_PROD_CPL_VD_LOTE_PROC_PRC_FILIAL ON
					PRC_FILIAL.CD_EMP = EST_PROD_CPL_VD_LOTE_PROC_PRC_FILIAL.CD_EMP AND
					PRC_FILIAL.CD_FILIAL = EST_PROD_CPL_VD_LOTE_PROC_PRC_FILIAL.CD_FILIAL AND
					@CD_CTR_PENDENCIA = EST_PROD_CPL_VD_LOTE_PROC_PRC_FILIAL.CD_CTR
				WHERE
					PRC_FILIAL.CD_FILIAL=@CD_FILIAL
					AND EST_PROD_CPL_VD_LOTE_PROC_PRC_FILIAL.CD_CTR IS NULL;

	SET @DT_INI = DATEADD(DAY,1,@DT_INI);
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;
