


 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
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 )







--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
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

IF @VISUALIZAR =0 
BEGIN
--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

PRINT 'P_GERA_PIS_COFINS_EST_NF_ENT FINALIZADA!'  