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

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

CREATE PROCEDURE P_RECALCULAR_APURACAO_PIS_COFINS ( @CD_EMP INT,
													@CD_FILIAL VARCHAR(MAX), 
													@DATAINICIAL DATETIME, 
													@DATAFINAL DATETIME, 
													@TIPOMOVIMENTACAO INT,
													@CD_USU INT = NULL,
													@EXIBIRVALORES INT = 0)
AS
BEGIN

SET NOCOUNT ON;


/*Parametrizações iniciais*/

declare @startDate dateTIME;
declare @endDate dateTIME;

select @startDate = @DATAINICIAL;
select @endDate = @DATAFINAL;

with dateRange as
(
  select dt = @startDate
  where @startDate < @endDate
  union all
  select dateadd(dd, 1, dt)
  from dateRange
  where dateadd(dd, 1, dt) <= @endDate
)
select distinct  DATEADD (DAY, 1, EOMONTH (CONVERT(DATE, dt), -1))  AS DT
INTO #Periodo
from dateRange
option (maxrecursion 0)

SELECT
		CD_EMP,
		CD_FILIAL 
	 INTO #PRC_FILIAL  
	 FROM PRC_FILIAL
	 WHERE
		CD_EMP = @CD_EMP
		AND CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@CD_FILIAL)) ;


CREATE TABLE #CTB_APURA_PIS_COFINS (
	[CD_EMP] [int] NOT NULL,
	[CD_FILIAL] [int] NOT NULL,
	[COD_CRED] [int] NOT NULL,
	[TIPO_REGISTRO] [int] NOT NULL,
	[MES_APURAC] [int] NOT NULL,
	[ANO_APURAC] [int] NOT NULL,
	[CST_PIS] [int] NOT NULL,
	[CST_COFINS] [int] NOT NULL,
	[VLR_RECEITA_BRUTA] [money] NULL,
	[VLR_BASE_PIS] [money] NULL,
	[PERC_ALIQ_PIS] [money] NULL,
	[VLR_PIS] [money] NULL,
	[VLR_BASE_COFINS] [money] NULL,
	[PERC_ALIQ_COFINS] [money] NULL,
	[VLR_COFINS] [money] NULL,
	[QUANT_BC_TOT] [money] NULL,
	[VLR_PIS_REDUC] [money] NULL,
	[VLR_COFINS_REDUC] [money] NULL,
	[VLR_ISENTAS] [money] NULL,
	[NAT_BC_CRED] [int] NULL,
	[DT_CAD] [datetime] NULL,
	[CD_USU] [int] NULL,
)

CREATE TABLE #CTB_APURA_PIS_COFINS_SALDO_CRED (
	[CD_EMP] [int] NOT NULL,
	[CD_GRP_ECON] [int] NOT NULL,
	[COD_CRED] [int] NOT NULL,
	[MES_APURAC] [int] NOT NULL,
	[ANO_APURAC] [int] NOT NULL,
	[ORIGEM_CRED] [int] NOT NULL,
	[CNPJ_SUC] [varchar](25) NULL,
	[VLR_CREDITO_PIS] [money] NULL,
	[VLR_CREDITO_COFINS] [money] NULL,
	[DT_CAD] [datetime] NULL,
	[CD_USU] [int] NULL
)

IF @TIPOMOVIMENTACAO = 1
BEGIN

	INSERT INTO #CTB_APURA_PIS_COFINS (CD_EMP, CD_FILIAL, COD_CRED, TIPO_REGISTRO, MES_APURAC, ANO_APURAC
										,CST_PIS, CST_COFINS, VLR_RECEITA_BRUTA, VLR_BASE_PIS, PERC_ALIQ_PIS
										,VLR_PIS, VLR_BASE_COFINS, PERC_ALIQ_COFINS, VLR_COFINS, QUANT_BC_TOT
										,VLR_PIS_REDUC, VLR_COFINS_REDUC, VLR_ISENTAS, NAT_BC_CRED, DT_CAD, CD_USU)
	SELECT CD_EMP, CD_FILIAL, COD_CRED, TP_REGISTRO AS TIPO_REGISTRO, MES_APURAC, ANO_APURAC, CST_PIS, CST_COFINS,
			SUM(VLR_RECEITA_BRUTA), SUM(VLR_BASE_PIS), MAX(PERC_ALIQ_PIS), SUM(VLR_PIS), SUM(VLR_BASE_COFINS),
			MAX(PERC_ALIQ_COFINS), SUM(VLR_COFINS), SUM(QUANT_BC_TOT), SUM(VLR_PIS_REDUC), SUM(VLR_COFINS_REDUC), 
			SUM(VLR_ISENTAS), NAT_BC_CRED, GETDATE() AS DT_CAD, @CD_USU
         FROM (
        SELECT EST_NF_SAI_IT.CD_FILIAL, 
				EST_NF_SAI_IT.CD_EMP, 
				0 AS COD_CRED, 
				1 AS TP_REGISTRO,  
				MONTH(EST_NF_SAI_IT.DT_EMI_NF) AS MES_APURAC,
				YEAR(EST_NF_SAI_IT.DT_EMI_NF) AS ANO_APURAC,
				EST_NF_SAI_IT.CST_PIS,
				EST_NF_SAI_IT.CST_COFINS,
				(EST_NF_SAI_IT.QT_IT * EST_NF_SAI_IT.VLR_IT) AS VLR_RECEITA_BRUTA,
				EST_NF_SAI_IT.VLR_BASE_PIS,
				EST_NF_SAI_IT.PERC_ALIQ_PIS,
				EST_NF_SAI_IT.VLR_PIS,
				EST_NF_SAI_IT.VLR_BASE_COFINS,
				EST_NF_SAI_IT.PERC_ALIQ_COFINS, 
				EST_NF_SAI_IT.VLR_COFINS,
				0 AS QUANT_BC_TOT,
				0 AS VLR_PIS_REDUC,
				0 AS VLR_COFINS_REDUC,
				0 AS VLR_ISENTAS,
				0 AS NAT_BC_CRED,
				GETDATE() AS DT_CAD
         FROM EST_NF_SAI 
              INNER JOIN EST_NF_SAI_IT ON EST_NF_SAI_IT.CD_EMP = EST_NF_SAI.CD_EMP 
                                          And EST_NF_SAI_IT.CD_FILIAL = EST_NF_SAI.CD_FILIAL 
                                          And EST_NF_SAI_IT.CD_NF = EST_NF_SAI.CD_NF 
              INNER JOIN GLB_MOV_OP ON GLB_MOV_OP.CD_MOV_OP = EST_NF_SAI.CD_NT_MOV 

          INNER JOIN PRC_FILIAL ON  PRC_FILIAL.CD_EMP = EST_NF_SAI.CD_EMP  
           And PRC_FILIAL.CD_FILIAL = EST_NF_SAI.CD_FILIAL 
		   INNER JOIN #PRC_FILIAL FIL ON FIL.CD_FILIAL = PRC_FILIAL.CD_FILIAL /*Buscar as filiais*/
											AND FIL.CD_EMP = PRC_FILIAL.CD_EMP
			INNER JOIN #Periodo P ON MONTH(P.DT) = MONTH(EST_NF_SAI.DT_EMI_NF) /*Buscar o Período*/
									 AND YEAR(P.DT) = YEAR(EST_NF_SAI.DT_EMI_NF)
         WHERE EST_NF_SAI.CD_EMP = 1 
            And EST_NF_SAI.STS_NF = 0 
            And  GLB_MOV_OP.FLAG_NAO_GERAR_ARQ_SPED = 0 
            And EST_NF_SAI_IT.VLR_PIS >  0
            AND TP_REGIME_APURAC_PIS_COFINS = 0 

           UNION ALL  

         Select  
              V_PDV_VD_IT.CD_FILIAL, 
				V_PDV_VD_IT.CD_EMP, 
				0 AS COD_CRED, 
				1 AS TP_REGISTRO,  
				MONTH(V_PDV_VD_IT.DT_VD) AS MES_APURAC,
				YEAR(V_PDV_VD_IT.DT_VD) AS ANO_APURAC,
				V_PDV_VD_IT.CST_PIS,
				V_PDV_VD_IT.CST_COFINS,
				(V_PDV_VD_IT.QT_IT * V_PDV_VD_IT.VLR_IT) AS VLR_RECEITA_BRUTA,
				V_PDV_VD_IT.VLR_BASE_PIS,
				V_PDV_VD_IT.PERC_ALIQ_PIS,
				V_PDV_VD_IT.VLR_PIS,
				V_PDV_VD_IT.VLR_BASE_COFINS,
				V_PDV_VD_IT.PERC_ALIQ_COFINS,
				V_PDV_VD_IT.VLR_COFINS,
				0 AS QUANT_BC_TOT,
				0 AS VLR_PIS_REDUC,
				0 AS VLR_COFINS_REDUC,
				0 AS VLR_ISENTAS,
				0 AS NAT_BC_CRED,
				GETDATE() AS DT_CAD
         FROM V_PDV_VD_IT  
          INNER JOIN PRC_FILIAL ON  PRC_FILIAL.CD_EMP = V_PDV_VD_IT.CD_EMP  
           And PRC_FILIAL.CD_FILIAL = V_PDV_VD_IT.CD_FILIAL 
		   INNER JOIN #PRC_FILIAL FIL ON FIL.CD_FILIAL = PRC_FILIAL.CD_FILIAL /*Buscar as filiais*/
											AND FIL.CD_EMP = PRC_FILIAL.CD_EMP
			INNER JOIN #Periodo P ON MONTH(P.DT) = MONTH(V_PDV_VD_IT.DT_VD) /*Buscar o Período*/
									 AND YEAR(P.DT) = YEAR(V_PDV_VD_IT.DT_VD)
         WHERE V_PDV_VD_IT.CD_EMP  = 1  
            And  V_PDV_VD_IT.VLR_PIS >  0) RS_SH
		GROUP BY CD_EMP, CD_FILIAL, COD_CRED, TP_REGISTRO, MES_APURAC, ANO_APURAC, CST_PIS, CST_COFINS, NAT_BC_CRED

END
ELSE IF (@TIPOMOVIMENTACAO = 0)
BEGIN
	
	INSERT INTO #CTB_APURA_PIS_COFINS (CD_EMP, CD_FILIAL, COD_CRED, TIPO_REGISTRO, MES_APURAC, ANO_APURAC
										,CST_PIS, CST_COFINS, VLR_RECEITA_BRUTA, VLR_BASE_PIS, PERC_ALIQ_PIS
										,VLR_PIS, VLR_BASE_COFINS, PERC_ALIQ_COFINS, VLR_COFINS, QUANT_BC_TOT
										,VLR_PIS_REDUC, VLR_COFINS_REDUC, VLR_ISENTAS, NAT_BC_CRED, DT_CAD, CD_USU)
	SELECT CD_EMP, CD_FILIAL, COD_CRED, TP_REGISTRO AS TIPO_REGISTRO, MES_APURAC, ANO_APURAC, CST_PIS, CST_COFINS,
			SUM(VLR_RECEITA_BRUTA), SUM(VLR_BASE_PIS), MAX(PERC_ALIQ_PIS), SUM(VLR_PIS), SUM(VLR_BASE_COFINS),
			MAX(PERC_ALIQ_COFINS), SUM(VLR_COFINS), SUM(QUANT_BC_TOT), SUM(VLR_PIS_REDUC), SUM(VLR_COFINS_REDUC), 
			SUM(VLR_ISENTAS), NAT_BC_CRED, GETDATE() AS DT_CAD, @CD_USU
	FROM
	(SELECT EST_NF_ENT_IT.CD_FILIAL, 
				EST_NF_ENT_IT.CD_EMP, 
				case when ( EST_NF_ENT_IT.CST_PIS IN (51,53)) THEN 201 ELSE 101 end AS COD_CRED, 
				0 AS TP_REGISTRO,  
				MONTH(EST_NF_ENT_IT.DT_ENT) AS MES_APURAC,
				YEAR(EST_NF_ENT_IT.DT_ENT) AS ANO_APURAC,
				EST_NF_ENT_IT.CST_PIS,
				EST_NF_ENT_IT.CST_COFINS,
				--(EST_NF_ENT_IT.QT_IT * EST_NF_ENT_IT.VLR_IT) AS VLR_RECEITA_BRUTA,
				0 AS VLR_RECEITA_BRUTA, -- Só calcula na saída
				EST_NF_ENT_IT.VLR_BASE_PIS,
				EST_NF_ENT_IT.PERC_ALIQ_PIS,
				EST_NF_ENT_IT.VLR_PIS,
				EST_NF_ENT_IT.VLR_BASE_COFINS,
				EST_NF_ENT_IT.PERC_ALIQ_COFINS,
				EST_NF_ENT_IT.VLR_COFINS,
				EST_NF_ENT_IT.QT_IT AS QUANT_BC_TOT,
				0 AS VLR_PIS_REDUC, 
				0 AS VLR_COFINS_REDUC,
				0 AS VLR_ISENTAS, 
				NT.CD_CRED_PIS_COFINS AS NAT_BC_CRED,
				GETDATE() AS DT_CAD
         FROM EST_NF_ENT 
              INNER JOIN EST_NF_ENT_IT ON EST_NF_ENT_IT.CD_EMP = EST_NF_ENT.CD_EMP 
                                          And EST_NF_ENT_IT.CD_FILIAL = EST_NF_ENT.CD_FILIAL 
                                          And EST_NF_ENT_IT.CD_NF = EST_NF_ENT.CD_NF 
              INNER JOIN GLB_MOV_OP ON GLB_MOV_OP.CD_MOV_OP = EST_NF_ENT.CD_NT_MOV 

          INNER JOIN PRC_FILIAL ON  PRC_FILIAL.CD_EMP = EST_NF_ENT.CD_EMP  
           And PRC_FILIAL.CD_FILIAL = EST_NF_ENT.CD_FILIAL 
		   INNER JOIN GLB_NT_OP NT ON NT.CD_NT_OP = EST_NF_ENT_IT.CD_NT_OP
		   INNER JOIN #PRC_FILIAL FIL ON FIL.CD_FILIAL = PRC_FILIAL.CD_FILIAL /*Buscar as filiais*/
											AND FIL.CD_EMP = PRC_FILIAL.CD_EMP
			INNER JOIN #Periodo P ON MONTH(P.DT) = MONTH(EST_NF_ENT.DT_ENT) /*Buscar o Período*/
									 AND YEAR(P.DT) = YEAR(EST_NF_ENT.DT_ENT)
         WHERE EST_NF_ENT.CD_EMP = 1 
            And EST_NF_ENT.STS_NF = 0 
            And  GLB_MOV_OP.FLAG_NAO_GERAR_ARQ_SPED = 0 
            And EST_NF_ENT_IT.VLR_PIS >  0
			AND (EST_NF_ENT_IT.CST_PIS BETWEEN 50 AND 66
			AND EST_NF_ENT_IT.CST_COFINS BETWEEN 50 AND 66)
		) QUERY
GROUP BY CD_EMP, CD_FILIAL, COD_CRED, TP_REGISTRO, MES_APURAC, ANO_APURAC, CST_PIS, CST_COFINS, NAT_BC_CRED

			--CONSULTA DOS REDUC

			SELECT cd_filial, MES_APURAC, ANO_APURAC,
					Isnull(Sum(pis_unit * qt_it), 0) VLR_PIS_REDUC,
				   Isnull(Sum(cofins_unit * qt_it), 0) VLR_COFINS_REDUC
				   INTO #Reduc
			FROM
			  (
			     SELECT 
				 est_nf_ent.cd_filial,
				 EST_NF_SAI_IT.QT_IT,
				 MONTH(EST_NF_ENT.DT_ENT) AS MES_APURAC,
				 YEAR(EST_NF_ENT.DT_ENT) AS ANO_APURAC,
				 (SELECT (vlr_pis / qt_it)
				  FROM est_nf_ent_it IT
				  WHERE IT.cd_emp = est_nf_sai.cd_emp
					AND IT.cd_filial = est_nf_sai.cd_filial
					AND IT.cd_nf = est_nf_ent_nf_sai.cd_nf_ent
					AND est_nf_sai_it.cd_prod = IT.cd_prod
					AND IT.vlr_pis > 0
					AND est_nf_sai_it.vlr_pis = 0) PIS_UNIT,
				 (SELECT (vlr_cofins / qt_it)
				  FROM est_nf_ent_it IT
				  WHERE IT.cd_emp = est_nf_sai.cd_emp
					AND IT.cd_filial = est_nf_sai.cd_filial
					AND IT.cd_nf = est_nf_ent_nf_sai.cd_nf_ent
					AND est_nf_sai_it.cd_prod = IT.cd_prod
					AND IT.vlr_cofins > 0
					AND est_nf_sai_it.vlr_cofins = 0) COFINS_UNIT
			   FROM est_nf_sai
			   INNER JOIN est_nf_sai_it ON est_nf_sai_it.cd_emp = est_nf_sai.cd_emp
			   AND est_nf_sai_it.cd_filial = est_nf_sai.cd_filial
			   AND est_nf_sai_it.cd_nf = est_nf_sai.cd_nf
			   INNER JOIN glb_mov_op ON glb_mov_op.cd_mov_op = est_nf_sai.cd_nt_mov
			   INNER JOIN est_nf_ent_nf_sai ON est_nf_ent_nf_sai.cd_emp = est_nf_sai.cd_emp
			   AND est_nf_ent_nf_sai.cd_filial = est_nf_sai.cd_filial
			   AND est_nf_ent_nf_sai.cd_nf_sai = est_nf_sai.cd_nf
			   INNER JOIN est_nf_ent ON est_nf_ent.cd_emp = est_nf_ent_nf_sai.cd_emp
			   AND est_nf_ent.cd_filial = est_nf_ent_nf_sai.cd_filial
			   AND est_nf_ent.cd_nf = est_nf_ent_nf_sai.cd_nf_ent
			INNER JOIN #PRC_FILIAL FIL ON FIL.CD_FILIAL = est_nf_ent.CD_FILIAL /*Buscar as filiais*/
											AND FIL.CD_EMP = est_nf_ent.CD_EMP
			INNER JOIN #Periodo P ON MONTH(P.DT) = MONTH(EST_NF_ENT.DT_ENT) /*Buscar o Período*/
									 AND YEAR(P.DT) = YEAR(EST_NF_ENT.DT_ENT)
			   WHERE est_nf_sai.cd_emp = 1
				 AND est_nf_sai.sts_nf = 0
				 AND glb_mov_op.tp_mov = 11
				 AND est_nf_sai_it.vlr_pis = 0) RS
			GROUP BY cd_filial, MES_APURAC, ANO_APURAC


UPDATE C
SET VLR_PIS_REDUC = R.VLR_PIS_REDUC,
	VLR_COFINS_REDUC = R.VLR_COFINS_REDUC
FROM #CTB_APURA_PIS_COFINS C
INNER JOIN #REDUC R ON R.CD_FILIAL = C.CD_FILIAL
						AND R.MES_APURAC = C.MES_APURAC
						AND R.ANO_APURAC = C.ANO_APURAC


END

	IF (@ExibirValores = 1)
	BEGIN
		SELECT * FROM #CTB_APURA_PIS_COFINS;
	END
	ELSE
	BEGIN

	/*UPDATE*/

	UPDATE ORIG
		SET VLR_BASE_PIS = P.VLR_BASE_PIS,
			PERC_ALIQ_PIS = P.PERC_ALIQ_PIS,
			VLR_PIS = P.VLR_PIS,
			VLR_BASE_COFINS = P.VLR_BASE_COFINS,
			PERC_ALIQ_COFINS = P.PERC_ALIQ_COFINS,
			VLR_COFINS = P.VLR_COFINS,
			QUANT_BC_TOT = P.QUANT_BC_TOT,
			VLR_PIS_REDUC = P.VLR_PIS_REDUC,
			VLR_COFINS_REDUC = P.VLR_COFINS_REDUC,
			CD_USU_ALT = @CD_USU,
			DT_ALT = GETDATE()
	FROM #CTB_APURA_PIS_COFINS P
	INNER JOIN CTB_APURA_PIS_COFINS ORIG ON ORIG.ANO_APURAC = P.ANO_APURAC
											 AND ORIG.MES_APURAC = P.MES_APURAC
											 AND ORIG.CD_FILIAL = P.CD_FILIAL
											 AND ORIG.COD_CRED = P.COD_CRED
											 AND ORIG.TIPO_REGISTRO = P.TIPO_REGISTRO
											 AND ORIG.CST_PIS = P.CST_PIS
											 AND ORIG.CST_COFINS = P.CST_COFINS
											 AND ORIG.NAT_BC_CRED = P.NAT_BC_CRED
	WHERE (ORIG.VLR_BASE_PIS <> P.VLR_BASE_PIS
			OR ORIG.PERC_ALIQ_PIS <> P.PERC_ALIQ_PIS
			OR ORIG.VLR_PIS <> P.VLR_PIS
			OR ORIG.VLR_BASE_COFINS <> P.VLR_BASE_COFINS
			OR ORIG.PERC_ALIQ_COFINS <> P.PERC_ALIQ_COFINS
			OR ORIG.VLR_COFINS <> P.VLR_COFINS
			OR ORIG.QUANT_BC_TOT <> P.QUANT_BC_TOT
			OR ORIG.VLR_PIS_REDUC <> P.VLR_PIS_REDUC
			OR ORIG.VLR_COFINS_REDUC <> P.VLR_COFINS_REDUC)

	/*INSERT*/


	INSERT INTO CTB_APURA_PIS_COFINS (CD_EMP, CD_FILIAL, COD_CRED, TIPO_REGISTRO, MES_APURAC, ANO_APURAC
											,CST_PIS, CST_COFINS, VLR_RECEITA_BRUTA, VLR_BASE_PIS, PERC_ALIQ_PIS
											,VLR_PIS, VLR_BASE_COFINS, PERC_ALIQ_COFINS, VLR_COFINS, QUANT_BC_TOT
											,VLR_PIS_REDUC, VLR_COFINS_REDUC, VLR_ISENTAS, NAT_BC_CRED, DT_CAD, CD_USU)

	SELECT CD_EMP, CD_FILIAL, COD_CRED, TIPO_REGISTRO, MES_APURAC, ANO_APURAC
			,CST_PIS, CST_COFINS, VLR_RECEITA_BRUTA, VLR_BASE_PIS, PERC_ALIQ_PIS
			,VLR_PIS, VLR_BASE_COFINS, PERC_ALIQ_COFINS, VLR_COFINS, QUANT_BC_TOT
			,VLR_PIS_REDUC, VLR_COFINS_REDUC, VLR_ISENTAS, NAT_BC_CRED, DT_CAD, CD_USU 
	FROM #CTB_APURA_PIS_COFINS P
	WHERE NOT EXISTS (SELECT 1 FROM CTB_APURA_PIS_COFINS ORIG
					  WHERE ORIG.ANO_APURAC = P.ANO_APURAC
					  AND ORIG.MES_APURAC = P.MES_APURAC
					  AND ORIG.CD_FILIAL = P.CD_FILIAL
					  AND ORIG.COD_CRED = P.COD_CRED
					  AND ORIG.TIPO_REGISTRO = P.TIPO_REGISTRO
					  AND ORIG.CST_PIS = P.CST_PIS
					  AND ORIG.CST_COFINS = P.CST_COFINS
					  AND ORIG.NAT_BC_CRED = P.NAT_BC_CRED)

	/*EXCLUSÃO*/

	DELETE ORIG
	FROM CTB_APURA_PIS_COFINS ORIG
	INNER JOIN #PRC_FILIAL FIL ON FIL.CD_FILIAL = ORIG.CD_FILIAL /*Buscar as filiais*/
												AND FIL.CD_EMP = ORIG.CD_EMP
	INNER JOIN #Periodo P ON MONTH(P.DT) = MES_APURAC /*Buscar o Período*/
							 AND YEAR(P.DT) = ANO_APURAC
	WHERE ORIG.TIPO_REGISTRO = @TIPOMOVIMENTACAO
	AND NOT EXISTS (SELECT 1 FROM #CTB_APURA_PIS_COFINS P
					  WHERE ORIG.ANO_APURAC = P.ANO_APURAC
					  AND ORIG.MES_APURAC = P.MES_APURAC
					  AND ORIG.CD_FILIAL = P.CD_FILIAL
					  AND ORIG.COD_CRED = P.COD_CRED
					  AND ORIG.TIPO_REGISTRO = P.TIPO_REGISTRO
					  AND ORIG.CST_PIS = P.CST_PIS
					  AND ORIG.CST_COFINS = P.CST_COFINS
					  AND ORIG.NAT_BC_CRED = P.NAT_BC_CRED)



	/*Tabela CTB_APURA_PIS_COFINS_SALDO_CRED */

	select DISTINCT GRP2.CD_FILIAL, GRP2.CD_GRP_ECON, GRP2.CD_EMP
	INTO #PRC_FILIAL_SALDO_CRED
	from [dbo].[PRC_GRP_ECON_PRC_FILIAL] GRP 
	INNER JOIN #PRC_FILIAL FIL ON FIL.CD_FILIAL = GRP.CD_FILIAL /*Buscar as filiais*/
								  AND FIL.CD_EMP = GRP.CD_EMP
	INNER JOIN [dbo].[PRC_GRP_ECON_PRC_FILIAL] GRP2 ON GRP2.CD_GRP_ECON = GRP.CD_GRP_ECON


	INSERT INTO #CTB_APURA_PIS_COFINS_SALDO_CRED (CD_EMP,	CD_GRP_ECON, COD_CRED, MES_APURAC, ANO_APURAC, ORIGEM_CRED,
													CNPJ_SUC,	VLR_CREDITO_PIS, VLR_CREDITO_COFINS, DT_CAD, CD_USU)
	SELECT CD_EMP, CD_GRP_ECON, COD_CRED, MES_APURAC, ANO_APURAC, ORIGEM_CRED, CNPJ_SUC, SUM(VLR_PIS) as VLR_CREDITO_PIS, 
		   SUM(VLR_COFINS) AS VLR_CREDITO_COFINS, DT_CAD, @CD_USU AS CD_USU
	FROM(
			SELECT EST_NF_ENT.CD_EMP, 
					FIL.CD_GRP_ECON AS CD_GRP_ECON, --GRUPO ECONOMICO AQUI 
					201 AS COD_CRED, 
					MONTH(EST_NF_ENT_IT.DT_ENT) AS MES_APURAC,
					YEAR(EST_NF_ENT_IT.DT_ENT) AS ANO_APURAC,
					1 AS ORIGEM_CRED,
					'' AS CNPJ_SUC,
					EST_NF_ENT_IT.VLR_PIS,
					EST_NF_ENT_IT.VLR_COFINS,
					GETDATE() AS DT_CAD
			 FROM EST_NF_ENT 
				  INNER JOIN EST_NF_ENT_IT ON EST_NF_ENT_IT.CD_EMP = EST_NF_ENT.CD_EMP 
											  And EST_NF_ENT_IT.CD_FILIAL = EST_NF_ENT.CD_FILIAL 
											  And EST_NF_ENT_IT.CD_NF = EST_NF_ENT.CD_NF 
				  INNER JOIN GLB_MOV_OP ON GLB_MOV_OP.CD_MOV_OP = EST_NF_ENT.CD_NT_MOV 

			  INNER JOIN PRC_FILIAL ON  PRC_FILIAL.CD_EMP = EST_NF_ENT.CD_EMP  
			   And PRC_FILIAL.CD_FILIAL = EST_NF_ENT.CD_FILIAL 
			   INNER JOIN GLB_NT_OP NT ON NT.CD_NT_OP = EST_NF_ENT_IT.CD_NT_OP
			   INNER JOIN #PRC_FILIAL_SALDO_CRED FIL ON FIL.CD_FILIAL = PRC_FILIAL.CD_FILIAL /*Buscar as filiais*/
												AND FIL.CD_EMP = PRC_FILIAL.CD_EMP
				INNER JOIN #Periodo P ON MONTH(P.DT) = MONTH(EST_NF_ENT.DT_ENT) /*Buscar o Período*/
										 AND YEAR(P.DT) = YEAR(EST_NF_ENT.DT_ENT)
			 WHERE EST_NF_ENT.CD_EMP = 1 
				And EST_NF_ENT.STS_NF = 0 
				And  GLB_MOV_OP.FLAG_NAO_GERAR_ARQ_SPED = 0 
				And EST_NF_ENT_IT.VLR_PIS >  0
				AND EST_NF_ENT_IT.CST_PIS IN (51,53) --201
				) QUERY
	GROUP BY CD_EMP, CD_GRP_ECON, COD_CRED, MES_APURAC, ANO_APURAC, ORIGEM_CRED, CNPJ_SUC, DT_CAD	


	/*INSERT*/


	INSERT INTO CTB_APURA_PIS_COFINS_SALDO_CRED (CD_EMP, CD_GRP_ECON, COD_CRED, MES_APURAC, ANO_APURAC, ORIGEM_CRED, CNPJ_SUC
												 ,VLR_CREDITO_PIS, VLR_CREDITO_UTILIZADO_PIS, SALDO_CREDITO_PIS, VLR_CREDITO_COFINS
												 ,VLR_CREDITO_UTILIZADO_COFINS, SALDO_CREDITO_COFINS, REG_SALDO_ANTERIOR, DT_CAD, CD_USU)
	SELECT CD_EMP, CD_GRP_ECON, COD_CRED, MES_APURAC, ANO_APURAC, ORIGEM_CRED, CNPJ_SUC
			,VLR_CREDITO_PIS, 0 AS VLR_CREDITO_UTILIZADO_PIS, VLR_CREDITO_PIS AS SALDO_CREDITO_PIS, 
			 VLR_CREDITO_COFINS, 0 AS VLR_CREDITO_UTILIZADO_COFINS, VLR_CREDITO_COFINS AS SALDO_CREDITO_COFINS, 
			 0 AS REG_SALDO_ANTERIOR, DT_CAD, CD_USU 
	FROM #CTB_APURA_PIS_COFINS_SALDO_CRED P
	WHERE NOT EXISTS (SELECT 1 FROM CTB_APURA_PIS_COFINS_SALDO_CRED ORIG
					  WHERE ORIG.CD_EMP = P.CD_EMP
						  AND ORIG.CD_GRP_ECON = P.CD_GRP_ECON
						  AND ORIG.COD_CRED = P.COD_CRED
						  AND ORIG.MES_APURAC = P.MES_APURAC
						  AND ORIG.ANO_APURAC = P.ANO_APURAC
						  AND ORIG.ORIGEM_CRED = P.ORIGEM_CRED
						  AND ORIG.CNPJ_SUC = P.CNPJ_SUC)
	/*UPDATE*/

	UPDATE ORIG
		SET VLR_CREDITO_PIS = P.VLR_CREDITO_PIS,
			SALDO_CREDITO_PIS = CASE WHEN (P.VLR_CREDITO_PIS - ORIG.VLR_CREDITO_UTILIZADO_PIS) > 0 
									THEN (P.VLR_CREDITO_PIS - ORIG.VLR_CREDITO_UTILIZADO_PIS)
									ELSE 0
								END,
			VLR_CREDITO_COFINS = P.VLR_CREDITO_COFINS,
			SALDO_CREDITO_COFINS = CASE WHEN (P.VLR_CREDITO_COFINS - ORIG.VLR_CREDITO_UTILIZADO_COFINS) > 0 
									THEN (P.VLR_CREDITO_COFINS - ORIG.VLR_CREDITO_UTILIZADO_COFINS)
									ELSE 0
								END,
			CD_USU_ALT = @CD_USU,
			DT_ALT = GETDATE()
	FROM #CTB_APURA_PIS_COFINS_SALDO_CRED P
	INNER JOIN CTB_APURA_PIS_COFINS_SALDO_CRED ORIG ON ORIG.CD_EMP = P.CD_EMP
													  AND ORIG.CD_GRP_ECON = P.CD_GRP_ECON
													  AND ORIG.COD_CRED = P.COD_CRED
													  AND ORIG.MES_APURAC = P.MES_APURAC
													  AND ORIG.ANO_APURAC = P.ANO_APURAC
													  AND ORIG.ORIGEM_CRED = P.ORIGEM_CRED
													  AND ORIG.CNPJ_SUC = P.CNPJ_SUC
	WHERE (ORIG.VLR_CREDITO_PIS <> P.VLR_CREDITO_PIS
			OR ORIG.VLR_CREDITO_COFINS <> P.VLR_CREDITO_COFINS)
		AND ORIG.REG_SALDO_ANTERIOR = 0

	/*EXCLUSÃO*/

	DELETE ORIG
	FROM CTB_APURA_PIS_COFINS_SALDO_CRED ORIG
	INNER JOIN (SELECT DISTINCT CD_GRP_ECON, FIL.CD_EMP
				 FROM #PRC_FILIAL_SALDO_CRED FIL) FIL ON FIL.CD_GRP_ECON = ORIG.CD_GRP_ECON /*Buscar as filiais*/
														AND FIL.CD_EMP = ORIG.CD_EMP
	INNER JOIN #Periodo P ON MONTH(P.DT) = MES_APURAC /*Buscar o Período*/
							 AND YEAR(P.DT) = ANO_APURAC
	WHERE NOT EXISTS (SELECT 1 FROM #CTB_APURA_PIS_COFINS_SALDO_CRED P
					  WHERE ORIG.CD_EMP = P.CD_EMP
							AND ORIG.CD_GRP_ECON = P.CD_GRP_ECON
							AND ORIG.COD_CRED = P.COD_CRED
							AND ORIG.MES_APURAC = P.MES_APURAC
							AND ORIG.ANO_APURAC = P.ANO_APURAC
							AND ORIG.ORIGEM_CRED = P.ORIGEM_CRED
							AND ORIG.CNPJ_SUC = P.CNPJ_SUC)
	AND ORIG.REG_SALDO_ANTERIOR = 0
	AND (ORIG.VLR_CREDITO_UTILIZADO_PIS = 0
		AND ORIG.VLR_CREDITO_UTILIZADO_COFINS = 0)

	END
END


 

              