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

CREATE PROCEDURE P_CONTRIBUICAO_PIS_COFINS (
	@CD_EMP INT
	,@CD_GRP_ECON INT
	,@CD_FILIAL VARCHAR(50)
	,@DT_PERIODO DATETIME
	)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @DT_INI AS DATETIME
	DECLARE @DT_FIM AS DATETIME

	SET @DT_INI = (
			SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @DT_PERIODO), 0)
			)
	SET @DT_FIM = (
			SELECT EOMONTH(@DT_INI)
			)

	CREATE TABLE #RSCONTRIBUICAO (
		CD_GRP_ECON INT
		,CD_FILIAL INT
		,DS_TRIB VARCHAR(10)
		,VLR_TOTAL_CONTR MONEY
		,VLR_CRED_DESC_ANTERIOR MONEY
		,VLR_CRED_DESC_ATUAL MONEY
		,VLR_CONTR_DEVIDA MONEY
		,VLR_CONTR_RET MONEY
		,VLR_OUTRAS MONEY
		,VLR_CONTR_RECOLHER MONEY
		)

	--CONTRIBUIÇÃO POR GRUPO ECONÔMICO
	IF @CD_FILIAL = ''
	BEGIN
		INSERT #RSCONTRIBUICAO (
			CD_GRP_ECON
			,CD_FILIAL
			,DS_TRIB
			)
		SELECT @CD_GRP_ECON
			,0
			,'PIS'

		INSERT #RSCONTRIBUICAO (
			CD_GRP_ECON
			,CD_FILIAL
			,DS_TRIB
			)
		SELECT @CD_GRP_ECON
			,0
			,'COFINS'

		UPDATE #RSCONTRIBUICAO
		SET VLR_TOTAL_CONTR = CASE 
				WHEN #RSCONTRIBUICAO.DS_TRIB = 'PIS'
					THEN ISNULL(TOTAL_CONTRIBUICAO.VLR_CONTRIB_PIS, 0)
				WHEN #RSCONTRIBUICAO.DS_TRIB = 'COFINS'
					THEN ISNULL(TOTAL_CONTRIBUICAO.VLR_CONTRIB_COFINS, 0)
				END
		FROM (
			SELECT PIS_COFINS.CD_GRP_ECON
				,ISNULL(SUM(ISNULL(PIS_COFINS.VLR_PIS_SAIDA_PIS, 0)), 0) VLR_CONTRIB_PIS
				,ISNULL(SUM(ISNULL(PIS_COFINS.VLR_PIS_SAIDA_COFINS, 0)), 0) VLR_CONTRIB_COFINS
			FROM (
				SELECT PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON
					,ISNULL(EST_NF_SAI_IT.VLR_PIS, 0) AS VLR_PIS_SAIDA_PIS
					,ISNULL(EST_NF_SAI_IT.VLR_COFINS, 0) AS VLR_PIS_SAIDA_COFINS
				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_GRP_ECON_PRC_FILIAL ON PRC_FILIAL.CD_EMP = PRC_GRP_ECON_PRC_FILIAL.CD_EMP
					AND PRC_FILIAL.CD_FILIAL = PRC_GRP_ECON_PRC_FILIAL.CD_FILIAL
				WHERE EST_NF_SAI.CD_EMP = @CD_EMP
					AND PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON = @CD_GRP_ECON
					AND EST_NF_SAI.DT_EMI_NF BETWEEN @DT_INI
						AND @DT_FIM
					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 PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON
					,ISNULL(SUM(ISNULL(V_PDV_VD_IT.VLR_PIS, 0)), 0) VLR_PIS_SAIDA_PIS
					,ISNULL(SUM(ISNULL(V_PDV_VD_IT.VLR_COFINS, 0)), 0) VLR_PIS_SAIDA_COFINS
				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_GRP_ECON_PRC_FILIAL ON PRC_FILIAL.CD_EMP = PRC_GRP_ECON_PRC_FILIAL.CD_EMP
					AND PRC_FILIAL.CD_FILIAL = PRC_GRP_ECON_PRC_FILIAL.CD_FILIAL
				WHERE V_PDV_VD_IT.CD_EMP = @CD_EMP
					AND PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON = @CD_GRP_ECON
					AND V_PDV_VD_IT.DT_VD BETWEEN @DT_INI
						AND @DT_FIM
					AND V_PDV_VD_IT.VLR_PIS > 0
					AND TP_REGIME_APURAC_PIS_COFINS = 0
				GROUP BY PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON
				) PIS_COFINS
			GROUP BY PIS_COFINS.CD_GRP_ECON
			) AS TOTAL_CONTRIBUICAO
		WHERE #RSCONTRIBUICAO.CD_GRP_ECON = TOTAL_CONTRIBUICAO.CD_GRP_ECON

		UPDATE #RSCONTRIBUICAO
		SET VLR_CRED_DESC_ATUAL = CASE 
				WHEN #RSCONTRIBUICAO.DS_TRIB = 'PIS'
					THEN ISNULL(CREDITO.VLR_PIS, 0)
				WHEN #RSCONTRIBUICAO.DS_TRIB = 'COFINS'
					THEN ISNULL(CREDITO.VLR_COFINS, 0)
				END
			,VLR_CRED_DESC_ANTERIOR = 0
		FROM (
			SELECT PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON
				,ISNULL(SUM(VLR_PIS), 0) AS VLR_PIS
				,ISNULL(SUM(VLR_COFINS), 0) AS VLR_COFINS
			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_NT_OP ON EST_NF_ENT_IT.CD_NT_OP = GLB_NT_OP.CD_NT_OP
			INNER JOIN PRC_FILIAL ON EST_NF_ENT.CD_EMP = PRC_FILIAL.CD_EMP
				AND EST_NF_ENT.CD_FILIAL = PRC_FILIAL.CD_FILIAL
			INNER JOIN PRC_GRP_ECON_PRC_FILIAL ON PRC_FILIAL.CD_EMP = PRC_GRP_ECON_PRC_FILIAL.CD_EMP
				AND PRC_FILIAL.CD_FILIAL = PRC_GRP_ECON_PRC_FILIAL.CD_FILIAL
			WHERE EST_NF_ENT.CD_EMP = @CD_EMP
				AND PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON = @CD_GRP_ECON
				AND EST_NF_ENT.STS_NF = 0
				AND EST_NF_ENT_it.VLR_BASE_PIS > 0
				AND EST_NF_ENT_it.VLR_BASE_COFINS > 0
				AND EST_NF_ENT_IT.CST_PIS BETWEEN 50
					AND 66
				AND EST_NF_ENT_IT.CST_PIS <> 53
				AND EST_NF_ENT_IT.CST_COFINS BETWEEN 50
					AND 66
				AND EST_NF_ENT_IT.CST_COFINS <> 53
				AND EST_NF_ENT.DT_ENT BETWEEN @DT_INI
					AND @DT_FIM
			GROUP BY PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON
			) AS CREDITO
		WHERE #RSCONTRIBUICAO.CD_GRP_ECON = CREDITO.CD_GRP_ECON
	END
	ELSE
	BEGIN
		INSERT #RSCONTRIBUICAO (
			CD_GRP_ECON
			,CD_FILIAL
			,DS_TRIB
			)
		SELECT @CD_GRP_ECON
			,ITEM
			,'PIS'
		FROM dbo.UFD_STRING_SPLIT(@CD_FILIAL, ',')

		INSERT #RSCONTRIBUICAO (
			CD_GRP_ECON
			,CD_FILIAL
			,DS_TRIB
			)
		SELECT @CD_GRP_ECON
			,ITEM
			,'COFINS'
		FROM dbo.UFD_STRING_SPLIT(@CD_FILIAL, ',')

		UPDATE #RSCONTRIBUICAO
		SET VLR_TOTAL_CONTR = CASE 
				WHEN #RSCONTRIBUICAO.DS_TRIB = 'PIS'
					THEN ISNULL(TOTAL_CONTRIBUICAO.VLR_CONTRIB_PIS, 0)
				WHEN #RSCONTRIBUICAO.DS_TRIB = 'COFINS'
					THEN ISNULL(TOTAL_CONTRIBUICAO.VLR_CONTRIB_COFINS, 0)
				END
		FROM (
			SELECT PIS_COFINS.CD_GRP_ECON
				,PIS_COFINS.CD_FILIAL
				,SUM(PIS_COFINS.VLR_PIS_SAIDA_PIS) VLR_CONTRIB_PIS
				,SUM(PIS_COFINS.VLR_PIS_SAIDA_COFINS) VLR_CONTRIB_COFINS
			FROM (
				SELECT PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON
					,PRC_FILIAL.CD_FILIAL
					,EST_NF_SAI_IT.VLR_PIS AS VLR_PIS_SAIDA_PIS
					,EST_NF_SAI_IT.VLR_COFINS AS VLR_PIS_SAIDA_COFINS
				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_GRP_ECON_PRC_FILIAL ON PRC_FILIAL.CD_EMP = PRC_GRP_ECON_PRC_FILIAL.CD_EMP
					AND PRC_FILIAL.CD_FILIAL = PRC_GRP_ECON_PRC_FILIAL.CD_FILIAL
				WHERE EST_NF_SAI.CD_EMP = @CD_EMP
					AND EST_NF_SAI.CD_FILIAL IN (
						SELECT ITEM
						FROM dbo.UFD_STRING_SPLIT(@CD_FILIAL, ',')
						)
					AND EST_NF_SAI.DT_EMI_NF BETWEEN @DT_INI
						AND @DT_FIM
					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 PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON
					,PRC_FILIAL.CD_FILIAL
					,SUM(V_PDV_VD_IT.VLR_PIS) VLR_PIS_SAIDA_PIS
					,SUM(V_PDV_VD_IT.VLR_COFINS) VLR_PIS_SAIDA_COFINS
				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_GRP_ECON_PRC_FILIAL ON PRC_FILIAL.CD_EMP = PRC_GRP_ECON_PRC_FILIAL.CD_EMP
					AND PRC_FILIAL.CD_FILIAL = PRC_GRP_ECON_PRC_FILIAL.CD_FILIAL
				WHERE V_PDV_VD_IT.CD_EMP = @CD_EMP
					AND V_PDV_VD_IT.CD_FILIAL IN (
						SELECT ITEM
						FROM dbo.UFD_STRING_SPLIT(@CD_FILIAL, ',')
						)
					AND V_PDV_VD_IT.DT_VD BETWEEN @DT_INI
						AND @DT_FIM
					AND V_PDV_VD_IT.VLR_PIS > 0
					AND TP_REGIME_APURAC_PIS_COFINS = 0
				GROUP BY PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON
					,PRC_FILIAL.CD_FILIAL
				) PIS_COFINS
			GROUP BY PIS_COFINS.CD_GRP_ECON
				,PIS_COFINS.CD_FILIAL
			) AS TOTAL_CONTRIBUICAO
		WHERE #RSCONTRIBUICAO.CD_GRP_ECON = TOTAL_CONTRIBUICAO.CD_GRP_ECON
			AND #RSCONTRIBUICAO.CD_FILIAL = TOTAL_CONTRIBUICAO.CD_FILIAL

		UPDATE #RSCONTRIBUICAO
		SET VLR_CRED_DESC_ATUAL = CASE 
				WHEN #RSCONTRIBUICAO.DS_TRIB = 'PIS'
					THEN ISNULL(CREDITO.VLR_PIS, 0)
				WHEN #RSCONTRIBUICAO.DS_TRIB = 'COFINS'
					THEN ISNULL(CREDITO.VLR_COFINS, 0)
				END
			,VLR_CRED_DESC_ANTERIOR = 0
		FROM (
			SELECT PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON
				,ISNULL(SUM(VLR_PIS), 0) AS VLR_PIS
				,ISNULL(SUM(VLR_COFINS), 0) AS VLR_COFINS
			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_NT_OP ON EST_NF_ENT_IT.CD_NT_OP = GLB_NT_OP.CD_NT_OP
			INNER JOIN PRC_FILIAL ON EST_NF_ENT.CD_EMP = PRC_FILIAL.CD_EMP
				AND EST_NF_ENT.CD_FILIAL = PRC_FILIAL.CD_FILIAL
			INNER JOIN PRC_GRP_ECON_PRC_FILIAL ON PRC_FILIAL.CD_EMP = PRC_GRP_ECON_PRC_FILIAL.CD_EMP
				AND PRC_FILIAL.CD_FILIAL = PRC_GRP_ECON_PRC_FILIAL.CD_FILIAL
			WHERE EST_NF_ENT.CD_EMP = @CD_EMP
				AND EST_NF_ENT.STS_NF = 0
				AND EST_NF_ENT_it.VLR_BASE_PIS > 0
				AND EST_NF_ENT_it.VLR_BASE_COFINS > 0
				AND EST_NF_ENT_IT.CST_PIS BETWEEN 50
					AND 66
				AND EST_NF_ENT_IT.CST_PIS <> 53
				AND EST_NF_ENT_IT.CST_COFINS BETWEEN 50
					AND 66
				AND EST_NF_ENT_IT.CST_COFINS <> 53
				AND EST_NF_ENT.CD_FILIAL IN (
					SELECT ITEM
					FROM dbo.UFD_STRING_SPLIT(@CD_FILIAL, ',')
					)
				AND EST_NF_ENT.DT_ENT BETWEEN @DT_INI
					AND @DT_FIM
			GROUP BY PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON
			) AS CREDITO
		WHERE #RSCONTRIBUICAO.CD_GRP_ECON = CREDITO.CD_GRP_ECON
	END

	UPDATE #RSCONTRIBUICAO
	SET VLR_CRED_DESC_ATUAL = VLR_TOTAL_CONTR
	WHERE VLR_CRED_DESC_ATUAL > VLR_TOTAL_CONTR

	UPDATE #RSCONTRIBUICAO
	SET VLR_CONTR_DEVIDA = VLR_TOTAL_CONTR - ISNULL(VLR_CRED_DESC_ATUAL, 0)
		,VLR_CONTR_RET = 0
		,VLR_OUTRAS = 0

	UPDATE #RSCONTRIBUICAO
	SET VLR_CONTR_RECOLHER = CASE 
			WHEN VLR_CONTR_DEVIDA = 0
				THEN 0
			ELSE VLR_CONTR_DEVIDA - VLR_CONTR_RET - VLR_OUTRAS
			END

	SELECT CD_GRP_ECON
		,CD_FILIAL
		,DS_TRIB
		,VLR_TOTAL_CONTR
		,ISNULL(VLR_CRED_DESC_ANTERIOR, 0) AS VLR_CRED_DESC_ANTERIOR
		,ISNULL(VLR_CRED_DESC_ATUAL, 0) AS VLR_CRED_DESC_ATUAL
		,VLR_CONTR_DEVIDA
		,VLR_CONTR_RET
		,VLR_OUTRAS
		,VLR_CONTR_RECOLHER
	FROM #RSCONTRIBUICAO
	WHERE ISNULL(VLR_TOTAL_CONTR, 0) > 0
	ORDER BY CD_GRP_ECON
		,CD_FILIAL
END
