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

CREATE PROCEDURE P_CREDITOS_PIS_COFINS (@CD_EMP INT, @CD_FILIAL VARCHAR(100), @DT_PERIODO DATETIME, @CD_USU INT, @CD_AGRUPAR INT) AS  
BEGIN  
	SET NOCOUNT ON;  

	DECLARE @DT_INI AS DATETIME  
	DECLARE @DT_FIM AS DATETIME  
  
	CREATE TABLE #CREDITO (
		[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 #APURACAO (
		[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,  
		[VLR_PIS_DESC]		[money] NULL,
		[VLR_PIS_SALDO]		[money] NULL,
		[VLR_COFINS_DESC]	[money] NULL,
		[VLR_COFINS_SALDO]	[money] NULL
	)
   
	SET @DT_INI = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @DT_PERIODO), 0))
	SET @DT_FIM = (SELECT EOMONTH(@DT_INI))

	INSERT INTO #CREDITO
	EXEC P_RECALCULAR_APURACAO_PIS_COFINS @CD_EMP, @CD_FILIAL, @DT_INI, @DT_FIM, 1, @CD_USU, 1  

	INSERT INTO #CREDITO
	EXEC P_RECALCULAR_APURACAO_PIS_COFINS @CD_EMP, @CD_FILIAL, @DT_INI, @DT_FIM, 0, @CD_USU, 1  

	IF @CD_AGRUPAR = 0
	BEGIN
		INSERT #APURACAO (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)
		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  
		FROM #CREDITO  
		WHERE COD_CRED IN (101, 201)  
		AND CD_FILIAL IN (SELECT VALUE FROM STRING_SPLIT(@CD_FILIAL, ','))

		UPDATE #APURACAO
		  SET #APURACAO.VLR_PIS_DESC = CASE WHEN #APURACAO.COD_CRED = 101 THEN ISNULL(CONTRIBUICAO.VLR_PIS, 0) ELSE 0 END
			, #APURACAO.VLR_COFINS_DESC = CASE WHEN #APURACAO.COD_CRED = 101 THEN ISNULL(CONTRIBUICAO.VLR_COFINS, 0) ELSE 0 END
		FROM #APURACAO
		INNER JOIN (SELECT CD_EMP, CD_FILIAL, MES_APURAC, ANO_APURAC, SUM(VLR_PIS) AS VLR_PIS, SUM(VLR_COFINS) AS VLR_COFINS
					FROM #CREDITO
					WHERE COD_CRED = 0
					GROUP BY CD_EMP, CD_FILIAL, MES_APURAC, ANO_APURAC
					) AS CONTRIBUICAO ON CONTRIBUICAO.CD_EMP = #APURACAO.CD_EMP
			AND CONTRIBUICAO.CD_FILIAL = #APURACAO.CD_FILIAL
			AND CONTRIBUICAO.MES_APURAC = #APURACAO.MES_APURAC
			AND CONTRIBUICAO.ANO_APURAC = #APURACAO.ANO_APURAC

	END  
	ELSE  
	BEGIN
		INSERT #APURACAO (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)
		SELECT SUB.CD_EMP  
		, SUB.CD_FILIAL  
		, SUB.COD_CRED  
		, 1 AS TIPO_REGISTRO  
		, SUB.MES_APURAC  
		, SUB.ANO_APURAC  
		, SUB.CST_PIS  
		, SUB.CST_COFINS  
		, 0 AS VLR_RECEITA_BRUTA  
		, SUM(SUB.VLR_BASE_PIS) AS VLR_BASE_PIS  
		, SUB.PERC_ALIQ_PIS PERC_ALIQ_PIS  
		, SUM(SUB.VLR_PIS) AS VLR_PIS  
		, SUM(SUB.VLR_BASE_COFINS) AS VLR_BASE_COFINS  
		, SUB.PERC_ALIQ_COFINS AS PERC_ALIQ_COFINS  
		, SUM(SUB.VLR_COFINS) AS VLR_COFINS  
		, SUM(SUB.QUANT_BC_TOT) AS QUANT_BC_TOT  
		, SUM(SUB.VLR_PIS_REDUC) AS VLR_PIS_REDUC  
		, SUM(SUB.VLR_COFINS_REDUC) AS VLR_COFINS_REDUC  
		, SUM(SUB.VLR_ISENTAS) AS VLR_ISENTAS  
		, SUM(SUB.NAT_BC_CRED) AS NAT_BC_CRED  
		FROM( SELECT CD_EMP  
		, 0 AS CD_FILIAL  
		, COD_CRED  
		, TIPO_REGISTRO  
		, MES_APURAC  
		, ANO_APURAC  
		, CST_PIS  
		, CST_COFINS  
		, 0 AS 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  
		FROM #CREDITO  
		WHERE COD_CRED IN (101, 201)
		AND CD_FILIAL IN (SELECT VALUE FROM STRING_SPLIT(@CD_FILIAL, ','))) SUB  
		GROUP BY SUB.CD_EMP, SUB.CD_FILIAL, SUB.COD_CRED, SUB.MES_APURAC, SUB.ANO_APURAC, SUB.CST_PIS, SUB.CST_COFINS, SUB.PERC_ALIQ_PIS, SUB.PERC_ALIQ_COFINS

		UPDATE #APURACAO
		  SET #APURACAO.VLR_PIS_DESC = CASE WHEN #APURACAO.COD_CRED = 101 THEN ISNULL(CONTRIBUICAO.VLR_PIS, 0) ELSE 0 END
			, #APURACAO.VLR_COFINS_DESC = CASE WHEN #APURACAO.COD_CRED = 101 THEN ISNULL(CONTRIBUICAO.VLR_COFINS, 0) ELSE 0 END
		FROM #APURACAO
		INNER JOIN (SELECT CD_EMP, 0 AS CD_FILIAL, MES_APURAC, ANO_APURAC, SUM(VLR_PIS) AS VLR_PIS, SUM(VLR_COFINS) AS VLR_COFINS
					FROM #CREDITO
					WHERE COD_CRED = 0
					GROUP BY CD_EMP, MES_APURAC, ANO_APURAC
					) AS CONTRIBUICAO ON CONTRIBUICAO.CD_EMP = #APURACAO.CD_EMP
			AND CONTRIBUICAO.CD_FILIAL = #APURACAO.CD_FILIAL
			AND CONTRIBUICAO.MES_APURAC = #APURACAO.MES_APURAC
			AND CONTRIBUICAO.ANO_APURAC = #APURACAO.ANO_APURAC
	END

	UPDATE #APURACAO
	SET VLR_PIS_DESC = 0
	WHERE VLR_PIS_DESC IS NULL

	UPDATE #APURACAO
	SET VLR_COFINS_DESC = 0
	WHERE VLR_COFINS_DESC IS NULL

	UPDATE #APURACAO
		SET VLR_PIS_SALDO = CASE WHEN VLR_PIS < VLR_PIS_DESC THEN 0 ELSE VLR_PIS - VLR_PIS_DESC END
		, VLR_COFINS_SALDO = CASE WHEN VLR_COFINS < VLR_COFINS_DESC THEN 0 ELSE VLR_COFINS - VLR_COFINS_DESC END

	SELECT * FROM #APURACAO
END;  