


 IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[P_GERA_PIS_COFINS_EST_NF_ENT]') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [DBO].[P_GERA_PIS_COFINS_EST_NF_ENT]
GO
/*
=======================================================================================
'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 
*/

--EXEC P_GERA_PIS_COFINS_EST_NF_ENT 1,904,'20150525','20150525',0,1

--exec  [P_GERA_PIS_COFINS_EST_NF_ENT] 1,5, '2025-09-30', '2025-09-30', 0, 0, 0

CREATE PROCEDURE [DBO].[P_GERA_PIS_COFINS_EST_NF_ENT] (@CD_EMP INT, 
													   @CD_FILIAL INT, 
													   @DT_INI DATETIME,
													   @DT_FIM DATETIME, 
													   @VISUALIZAR INT = 0, 
													   @FLAG_REPROCESSAR INT=0,
													   @CD_PROD INT=0)  WITH ENCRYPTION  AS 

SET NOCOUNT ON 

--DECLARE @DEDUZIR_ICMS_ST_BASE_PISCOFINS INT = NULL 
--DECLARE @DEDUZIR_ICMS_NORMAL_BASE_PISCOFINS INT = NULL 
--DECLARE @DATADEDUZIR DATE = NULL

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

 
--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;
-- Tag #A001
CREATE TABLE #RSENTRADASPIS (CD_EMP INT,
							CD_FILIAL INT,
							CD_NF INT,
							DT_ENT DATETIME,
							SEQ_ITEM INT,
							CD_PROD INT,
							QT_IT MONEY,
							VLR_IT MONEY,
							VLR_DESC MONEY,
							VLR_BASE_ICMS MONEY,
							ISENTO INT,
							SUBS_TRIB_ANT INT,
							ICMS INT,
							SUBS_TRIB INT,
							ICMS_RD INT,
							PIS_COFINS_RECALCULADO 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,
							PIS_COFINS	INT,
							VLR_FRETE MONEY,
							VLR_OUTROS MONEY,
							VLR_FECOP MONEY,
							VLR_ICMS MONEY)







--GERANDO TABELA INICIAL COM O CALCULO PADRAO DE PIS/COFINS

PRINT 'P_GERA_PIS_COFINS_EST_NF_ENT - INSERT 1 ' + CONVERT(VARCHAR, GETDATE(), 108);
INSERT INTO #RSENTRADASPIS
SELECT
	EST_NF_ENT_IT.CD_EMP,
	EST_NF_ENT_IT.CD_FILIAL,
	EST_NF_ENT_IT.CD_NF,
	EST_NF_ENT_IT.DT_ENT,
	EST_NF_ENT_IT.SEQ_ITEM,
	EST_NF_ENT_IT.CD_PROD,
	EST_NF_ENT_IT.QT_IT,
	EST_NF_ENT_IT.VLR_IT,
	EST_NF_ENT_IT.VLR_DESC,
	EST_NF_ENT_IT.VLR_BASE_ICMS,
	EST_TRIB.ISENTO,
	EST_TRIB.SUBS_TRIB_ANT,
	EST_TRIB.ICMS,
	EST_TRIB.SUBS_TRIB,
	EST_TRIB.ICMS_RD,
	1 AS PIS_COFINS_RECALCULADO,
	B.CD_CST_PIS,
	B.PERC_PIS,
	B.VLR_BASE_PIS,
	B.VLR_PIS,
	B.CD_CST_COFINS,
	B.PERC_COFINS,
	B.VLR_BASE_COFINS,
	B.VLR_COFINS,
	B.CST_TP_TRIB_PIS,
	EST_NF_ENT_IT.VLR_PROD_FRETE,
	EST_NF_ENT_IT.VLR_OUTROS,
	EST_NF_ENT_IT.VLR_FECOP,
	EST_NF_ENT_IT.VLR_ICMS_IT 
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 EST_TRIB ON
	EST_NF_ENT_IT.CD_TRIB = EST_TRIB.CD_TRIB

	--OUTER APPLY DBO.UFD_EST_PROD_TRIB_PIS_COFINS(EST_NF_ENT.CD_EMP,EST_NF_ENT.CD_FILIAL,
	--			    EST_NF_ENT.CD_NT_MOV,
	--			    CD_PROD,
	--			    EST_NF_ENT_IT.CD_NT_OP, 10,0)b
				    --((VLR_TOT_IT+EST_NF_ENT_IT.VLR_IPI)-(EST_NF_ENT_IT.VLR_DESC + ISNULL((CASE WHEN @DEDUZIR_ICMS_ST_BASE_PISCOFINS = 0 THEN 0 
								--															   ELSE EST_NF_ENT_IT.VLR_ICMS_RET
								--														   END),0)  )),0) B
		--OUTER APPLY (SELECT 1 as VALOR
		--			 FROM GLB_MOV_OP GLB
		--			 INNER JOIN GLB_MOV_OP_TP_MOV TP ON TP.TP_MOV = GLB.TP_MOV
		--			 WHERE EST_NF_ENT.CD_NT_MOV = GLB.CD_MOV_OP
		--			 AND TP.DS_MOV LIKE '%DEVOLU%' -- Verificando se é devolução
		--			 AND EXISTS (SELECT 1 
		--						 FROM PRC_FILIAL PRC 
		--						 WHERE PRC.CD_FILIAL = EST_NF_ENT.CD_FILIAL
		--						 AND ISNULL(DEDUZIR_ICMS_NORMAL_BASE_PISCOFINS,0) = 1) -- DEDUZIR_ICMS_NORMAL_BASE_PISCOFINS  = 1
		--			 AND EXISTS (SELECT 1
		--						 FROM GLB_NT_OP OP
		--						 WHERE OP.CD_NT_OP = EST_NF_ENT_IT.CD_NT_OP
		--						 AND ISNULL(FLAG_NAO_DEDUZ_ICMS_BASE_PIS_COFINS,0) = 0) -- FLAG_NAO_DEDUZ_ICMS_BASE_PIS_COFINS = 0
		--			 ) VALIDACOES
		OUTER APPLY DBO.UFD_EST_PROD_TRIB_PIS_COFINS(EST_NF_ENT.CD_EMP,EST_NF_ENT.CD_FILIAL,
				    EST_NF_ENT.CD_NT_MOV,
				    CD_PROD,
				    EST_NF_ENT_IT.CD_NT_OP,
				    (   
					(VLR_TOT_IT) - (EST_NF_ENT_IT.VLR_DESC)),0, EST_NF_ENT.CD_NF, 0, 0) B 
WHERE
	EST_NF_ENT_IT.CD_EMP = @CD_EMP 
	AND EST_NF_ENT_IT.CD_FILIAL = @CD_FILIAL 
	AND EST_NF_ENT_IT.DT_ENT BETWEEN @DT_INI AND @DT_FIM
	AND EST_NF_ENT_IT.CD_PROD= CASE WHEN @CD_PROD > 0 THEN @CD_PROD ELSE EST_NF_ENT_IT.CD_PROD END
	AND NOT EXISTS (SELECT 1 
					FROM EST_NF_ENT_NFE VALID
					INNER JOIN EST_NF_ENT_IT_IBS_CBS IT ON IT.CD_EMP = VALID.CD_EMP
															AND IT.CD_FILIAL = VALID.CD_FILIAL
															AND IT.CD_NF = VALID.CD_NF
					INNER JOIN EST_NF_ENT_EF_NF_CTR_NF EMI_PROP ON EMI_PROP.CD_EMP = VALID.CD_EMP
																AND EMI_PROP.CD_FILIAL = VALID.CD_FILIAL
																AND EMI_PROP.CD_NF_ENT = VALID.CD_NF
					WHERE VALID.CD_EMP = EST_NF_ENT.CD_EMP
					AND VALID.CD_FILIAL = EST_NF_ENT.CD_FILIAL
					AND VALID.CD_NF = EST_NF_ENT.CD_NF
					AND ISNULL(NR_PROTOCOLO,'') <> '')

IF @VISUALIZAR =0 
BEGIN



	UPDATE IBS
	SET VLR_BC_IBS_CBS = ISNULL(CALC.VLR_BC_IBS_CBS,0),
		VLR_IBS_UF = ISNULL(CALC.VLR_IBS_UF,0),
		VLR_IBS_MUN = ISNULL(CALC.VLR_IBS_MUN,0),
		VLR_IBS = ISNULL(CALC.VLR_IBS,0),
		VLR_CBS = ISNULL(CALC.VLR_CBS,0)
	FROM #RSENTRADASPIS R
	INNER JOIN EST_NF_ENT_IT_IBS_CBS IBS ON IBS.CD_EMP = R.CD_EMP
											AND IBS.CD_FILIAL = R.CD_FILIAL
											AND IBS.CD_NF = R.CD_NF
											AND IBS.CD_PROD = R.CD_PROD
											AND IBS.CD_IT = R.SEQ_ITEM
	OUTER APPLY dbo.UFD_EST_PROD_TRIB_IBS_CBS_RECALCULO (r.CD_EMP,
														 r.cd_filial,
														 r.cd_prod,
														 r.QT_IT,
														 r.vlr_it,
														 isnull(r.vlr_desc,0),
														 isnull(r.vlr_frete,0),
														 isnull(r.vlr_outros,0),
														 isnull(r.vlr_fecop,0),
														 isnull(r.VLR_ICMS,0),
														 isnull(r.vlr_pis,0),
														 isnull(r.vlr_cofins,0)
														) calc




--ALTERANDO A ENTRADA DE ACORDO COM O NOVO CALCULO CRIADO
	PRINT 'UPDATE 2 ' + CONVERT(VARCHAR, GETDATE(), 108);
	UPDATE
		E
	SET 
		E.VLR_BASE_PIS = ENV.VLR_BASE_PIS, 
		E.VLR_PIS=ENV.VLR_PIS,		
		E.PERC_ALIQ_PIS=ENV.PERC_ALIQ_PIS,	
		E.VLR_BASE_COFINS=ENV.VLR_BASE_COFINS,
		E.VLR_COFINS=ENV.VLR_COFINS,
		E.PERC_ALIQ_COFINS=ENV.PERC_ALIQ_COFINS, 
		E.CST_PIS=ENV.CST_PIS,		
		E.CST_COFINS=ENV.CST_COFINS,
		E.PIS_COFINS_RECALCULADO=ENV.PIS_COFINS_RECALCULADO,
		E.VLR_ISENTO = CASE WHEN ENV.ISENTO = 1 THEN
						   CASE WHEN ((E.QT_IT * E.VLR_IT) - E.VLR_DESC) > 0 THEN
								(E.QT_IT * E.VLR_IT) - E.VLR_DESC
							ELSE
								0
							END
					   ELSE
							0
					   END,
		E.VLR_OUTROS =CASE WHEN ENV.SUBS_TRIB_ANT = 1 THEN 
							CASE WHEN ((E.QT_IT * E.VLR_IT) - E.VLR_DESC)> 0 THEN
								((E.QT_IT * E.VLR_IT) - E.VLR_DESC)
							ELSE
								0
							END
					  ELSE
						CASE WHEN (ENV.ICMS + ENV.SUBS_TRIB + ENV.ICMS_RD)  > 1 THEN 
										CASE WHEN ((E.QT_IT * E.VLR_IT) - E.VLR_DESC) - E.VLR_BASE_ICMS > 0 THEN
											((E.QT_IT * E.VLR_IT) - E.VLR_DESC) - E.VLR_BASE_ICMS
										ELSE
											0
										END
									
						ELSE
							0
						END
					  END
	     ,E.PIS_COFINS = ENV.PIS_COFINS 
	FROM
		#RSENTRADASPIS ENV 
		INNER JOIN EST_NF_ENT_IT E ON
		ENV.CD_EMP	= E.CD_EMP AND
		ENV.CD_FILIAL	= E.CD_FILIAL AND 
		ENV.CD_NF		= E.CD_NF AND
		ENV.CD_PROD	= E.CD_PROD AND
		ENV.DT_ENT = E.DT_ENT AND
		ENV.SEQ_ITEM = E.SEQ_ITEM 
END


IF @VISUALIZAR =1 
BEGIN
	--ALTERANDO A ENTRADA DE ACORDO COM O NOVO CALCULO CRIADO
	PRINT 'SELECT 3 ' + CONVERT(VARCHAR, GETDATE(), 108);
	SELECT *
	FROM
		#RSENTRADASPIS  
END
DROP TABLE #RSENTRADASPIS;

PRINT 'EXEC 4 ' + CONVERT(VARCHAR, GETDATE(), 108);
EXEC P_EST_NF_ENT_IT_RECALCULO_CUSTO  @CD_EMP, @CD_FILIAL, 0,0,1,@DT_INI,@DT_FIM,0


-------------------------------------INCLUIR PENDENCIA DE PROCESSAMENTO "EST_MOVIMENTO_VENDAS"---------------------------------------------------------

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

----------------------------------------------------------------------------------------------------------------



PRINT 'P_GERA_PIS_COFINS_EST_NF_ENT FINALIZADA!'  