IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[P_RC_CLI_CONV_FECHA_CONSULTA]') AND type in (N'P', N'PC'))
begin
	DROP PROCEDURE P_RC_CLI_CONV_FECHA_CONSULTA
end
GO
--EXEC P_RC_CLI_CONV_FECHA_CONSULTA 1,'210,3,132,141,245,120,97,2,243,262,142,161,265,249,266,119,130,259','1,2,3,4,5,6,7,8,9','2017-11-20'

CREATE PROCEDURE [dbo].[P_RC_CLI_CONV_FECHA_CONSULTA] 
	@CD_EMP INT, 
	@CD_CONV VARCHAR(MAX)='',
	@CD_GRP_ECON VARCHAR(MAX)='',
	@DT_FECHA DATETIME,
	@FLAG_CONVENIADO_DEMISSAO INT=0,
	@CD_PLANO_VENDA INT=0,
	@TIPO_PROC_PLANO_VENDA INT=0,
	@CONV_HABILITADO INT=0
AS
/*
	@TIPO_PROC_PLANO_VENDA
	0 - Nao participa
	1 - Igual
	2 - Diferente
*/

DECLARE @EOMonth int SET @EOMonth =  DATEPART(DAY,(SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()+1),0))));
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;
BEGIN

	CREATE TABLE #RsConv (CD_EMP INT, CD_CONV INT);
	CREATE TABLE #RsGrupoEcon (CD_GRP_ECON INT);
	CREATE TABLE #RsNCCPendente (CD_CTR INT, VLR_NCC MONEY);
	CREATE TABLE #RsPrcFilial (CD_EMP INT, CD_FILIAL INT);

	CREATE TABLE #RsResultado (
		CD_CONV INT,
		CD_CLI INT,
		RZ_CLI VARCHAR(300),
		MATRICULA VARCHAR(100),
		CD_CTR INT,
		VLR_CONV_BRUTO MONEY,
		VLR_CONV MONEY,
		VLR_NCC MONEY,
		DS_CONV VARCHAR(100),
		PERC_CONV MONEY,
		DT_VENCTO DATETIME,
		VLR_DESC MONEY,
		CD_CLI_DEPEND INT,
		RZ_TITULAR VARCHAR(300),
		MES_VENCTO VARCHAR(300),
		CD_VD INT,
		NR_DOCUMENTO INT,
		DEVOLUCAO VARCHAR(100),
		CD_FILIAL INT,
		FLAG_CONVENIADO_DEMISSAO INT,
		CD_PLANO_VENDA INT,
		CONV_HABILITADO INT);

	INSERT INTO #RsConv
		SELECT
			@CD_EMP,
			ID 
		FROM UFD_PRM_IN_STRING(@CD_CONV); 
		
	IF @CD_GRP_ECON<>''
	BEGIN
		INSERT INTO #RsGrupoEcon
			SELECT ID FROM UFD_PRM_IN_STRING(@CD_GRP_ECON);   
	END;

	IF @CD_GRP_ECON=''
	BEGIN
		INSERT INTO #RsGrupoEcon
			SELECT CD_GRP_ECON FROM PRC_GRP_ECON;   
	END

	INSERT INTO #RsPrcFilial
	SELECT
		PRC_GRP_ECON_PRC_FILIAL.CD_EMP,
		PRC_GRP_ECON_PRC_FILIAL.CD_FILIAL
	FROM
		PRC_GRP_ECON_PRC_FILIAL INNER JOIN #RsGrupoEcon e on
		PRC_GRP_ECON_PRC_FILIAL.CD_GRP_ECON = E.CD_GRP_ECON;

	UPDATE
		RC_VD_CONV
	SET
		ST_FECHVD_CONV=1
	FROM
		RC_VD_CONV_RC_DEB INNER JOIN RC_VD_CONV ON
		RC_VD_CONV_RC_DEB.CD_CTR = RC_VD_CONV.CD_CTR 
	WHERE
		ST_FECHVD_CONV<>1;

	UPDATE
		PDV_VD_DEV_NCC
	SET 
		PDV_VD_DEV_NCC.STS_NCC=1
	FROM
		PDV_VD_DEV_NCC INNER JOIN PDV_VD_DEV_NCC_PDV_VD ON
		PDV_VD_DEV_NCC.CD_EMP = PDV_VD_DEV_NCC_PDV_VD.CD_EMP AND 
		PDV_VD_DEV_NCC.CD_FILIAL = PDV_VD_DEV_NCC_PDV_VD.CD_FILIAL AND 
		PDV_VD_DEV_NCC.CD_CTR_NCC = PDV_VD_DEV_NCC_PDV_VD.CD_CTR_NCC
	WHERE
		PDV_VD_DEV_NCC.STS_NCC = 0	
		AND PDV_VD_DEV_NCC.DT_CAD <=GETDATE()-30;
	
	INSERT INTO #RsResultado
		(CD_CONV,
		CD_CLI,
		RZ_CLI,
		MATRICULA,
		CD_CTR,
		VLR_CONV_BRUTO,
		VLR_CONV,
		VLR_NCC,
		DS_CONV,
		PERC_CONV,
		DT_VENCTO,
		VLR_DESC,
		CD_CLI_DEPEND,
		RZ_TITULAR,
		MES_VENCTO,
		CD_VD,
		NR_DOCUMENTO,
		DEVOLUCAO,
		CD_FILIAL,
		FLAG_CONVENIADO_DEMISSAO,
		CD_PLANO_VENDA,
		CONV_HABILITADO)
	SELECT 
		RC_CLI_CONV.CD_CONV,
		RC_CLI_CONV_RC_CLI.CD_CLI,
		RC_CLI.RZ_CLI,
		RC_CLI_CONV_RC_CLI.MATRICULA,
		RC_VD_CONV.CD_CTR,
		SUM(RC_VD_CONV.VLR_CONV) AS VLR_CONV,  
		(SUM(DISTINCT RC_VD_CONV.VLR_CONV) - ISNULL((SELECT SUM(RC_VD_CONV_CRED.VLR_CRED) FROM RC_VD_CONV_CRED  WHERE RC_VD_CONV_CRED.CD_EMP = RC_VD_CONV.CD_EMP AND RC_VD_CONV_CRED.CD_CTR = RC_VD_CONV.CD_CTR), 0)) AS VLR_CONV,	
		0 AS VLR_NCC,
		RC_CLI_CONV.DS_CONV,
		RC_CLI_CONV.PERC_CONV,
		CASE
			WHEN RC_CLI_CONV.MES_VENCTO = 0
			THEN CASE
				WHEN DATEPART(DAY, @DT_FECHA) < @EOMonth AND DATEPART(DAY, @DT_FECHA) > RC_CLI_CONV.DIA_VENCTO
				THEN DATEADD(DAY, RC_CLI_CONV.DIA_VENCTO, (DATEADD(DAY, -DATEPART(DAY, DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))), DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112)))))
				ELSE DATEADD(MONTH, -1, DATEADD(DAY, RC_CLI_CONV.DIA_VENCTO, (DATEADD(DAY, -DATEPART(DAY, DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))), DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))))))
			END
			WHEN RC_CLI_CONV.MES_VENCTO = 1
			THEN DATEADD(DAY, RC_CLI_CONV.DIA_VENCTO, (DATEADD(DAY, -DATEPART(DAY, DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))), DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112)))))
			ELSE DATEADD(MONTH, 1, DATEADD(DAY, RC_CLI_CONV.DIA_VENCTO, (DATEADD(DAY, -DATEPART(DAY, DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))), DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))))))
		END AS DT_VENCTO,
		SUM(CASE
				WHEN ISNULL(CASE
    				WHEN(PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT) > 0
					THEN PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT
					ELSE 0 END, 0) > 0
				THEN CASE  
					WHEN ISNULL((PDV_VD.VLR_CONV + ISNULL(PDV_VD.VLR_CONV_PBM, 0)), 0) > 0
					THEN((ISNULL(CASE
								  WHEN(PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT) > 0
								  THEN PDV_VD_IT.TOT_IT - PDV_VD_IT.VLR_DESC_TOT_RAT
								ELSE 0 END, 0)/ ISNULL((PDV_VD.VLR_CONV + ISNULL(PDV_VD.VLR_CONV_PBM, 0)), 0)) * RC_VD_CONV.VLR_CONV) * (ISNULL(RC_CLI_CONV_DESC_FAT.PERC_DESC, 0) / 100)
								ELSE 0
								END
					ELSE 0
			END) AS VLR_DESC,
		   0 AS CD_CLI_DEPEND,
		   '' AS RZ_TITULAR,
		   CASE
			   WHEN RC_CLI_CONV.MES_VENCTO = 0
			   THEN '0 - VENCIMENTO MES CORRENTE'
			   WHEN RC_CLI_CONV.MES_VENCTO = 1
			   THEN '1 - VENCIMENTO MES SEGUINTE'
			   WHEN RC_CLI_CONV.MES_VENCTO = 2
			   THEN '2 - VENCIMENTO MES POSTERIOR AO SEGUINTE'
		   END AS MES_VENCTO,
		   PDV_VD_RC_VD_CONV.CD_VD,
		   PDV_VD.NR_ECF AS NR_DOCUMENTO,
		   '' AS DEVOLUCAO,
		   RC_VD_CONV.CD_FILIAL,
		   RC_CLI.FLAG_CONVENIADO_DEMISSAO,
		   RC_CLI.CD_PLANO_VENDA,
		   RC_CLI_CONV.CONV_HABILITADO 
	FROM 
		RC_VD_CONV 
		INNER JOIN RC_CLI_CONV ON 
			RC_VD_CONV.CD_EMP = RC_CLI_CONV.CD_EMP AND 
			RC_VD_CONV.CD_CONV = RC_CLI_CONV.CD_CONV
		INNER JOIN RC_CLI ON 
			RC_VD_CONV.CD_EMP = RC_CLI.CD_EMP AND 
			RC_VD_CONV.CD_CLI = RC_CLI.CD_CLI
		INNER JOIN RC_CLI_CONV_RC_CLI ON 
			RC_CLI.CD_EMP = RC_CLI_CONV_RC_CLI.CD_EMP AND 
			RC_CLI.CD_CLI = RC_CLI_CONV_RC_CLI.CD_CLI AND 
			RC_VD_CONV.CD_CONV = RC_CLI_CONV_RC_CLI.CD_CONV
		INNER JOIN PDV_VD_RC_VD_CONV ON 
			RC_VD_CONV.CD_EMP = PDV_VD_RC_VD_CONV.CD_EMP AND 
			RC_VD_CONV.CD_FILIAL = PDV_VD_RC_VD_CONV.CD_FILIAL AND 
			RC_VD_CONV.CD_CTR = PDV_VD_RC_VD_CONV.CD_CTR
		INNER JOIN PDV_VD ON 
			PDV_VD_RC_VD_CONV.CD_EMP = PDV_VD.CD_EMP AND 
			PDV_VD_RC_VD_CONV.CD_FILIAL = PDV_VD.CD_FILIAL AND 
			PDV_VD_RC_VD_CONV.CD_VD = PDV_VD.CD_VD
		INNER JOIN PDV_VD_IT ON 
			PDV_VD.CD_EMP = PDV_VD_IT.CD_EMP AND 
			PDV_VD.CD_FILIAL = PDV_VD_IT.CD_FILIAL AND 
			PDV_VD.CD_VD = PDV_VD_IT.CD_VD and 
			PDV_VD.DT_VD = PDV_VD_IT.DT_VD
		LEFT OUTER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON 
			PDV_VD_IT.CD_EMP = ARV.CD_EMP AND 
			PDV_VD_IT.CD_PROD = ARV.CD_PROD
		LEFT JOIN RC_CLI_CONV_DESC_FAT ON 
			RC_CLI_CONV.CD_EMP = RC_CLI_CONV_DESC_FAT.CD_EMP AND 
			RC_CLI_CONV.CD_CONV = RC_CLI_CONV_DESC_FAT.CD_CONV AND 
			ARV.CD_ARV_MERC_LINHA = RC_CLI_CONV_DESC_FAT.CD_ARV_MERC_LINHA
		INNER JOIN #RsPrcFilial f on
			RC_VD_CONV.CD_EMP = f.CD_EMP AND
			RC_VD_CONV.CD_FILIAL = f.CD_FILIAL 
		INNER JOIN #RsConv C on
			RC_VD_CONV.CD_EMP = C.CD_EMP AND
			RC_VD_CONV.CD_CONV = C.CD_CONV
	WHERE 
		PDV_VD.CD_EMP = @CD_EMP
		AND RC_VD_CONV.VLR_CONV > 0
		AND ISNULL(RC_VD_CONV.FLAG_CONV_AFASTADO, 0) = 0
		AND RC_VD_CONV.DT_FECHA <= (CASE   -- CASE IMPLEMENTADO PARA RETORNAR TODAS AS VENDAS DE FUCNIONÁRIOS DEMITIDOS 
										  WHEN ISNULL(RC_CLI.FLAG_CONVENIADO_DEMISSAO,0) <> 1 THEN @DT_FECHA
										  ELSE EOMONTH (@DT_FECHA, 180)
									END)
		AND RC_VD_CONV.ST_FECHVD_CONV IN(5, 0)
		AND PDV_VD.ST_VD = 0
	GROUP BY RC_CLI_CONV.CD_CONV,
		RC_CLI_CONV_RC_CLI.CD_CLI,
		RC_CLI.RZ_CLI,
		RC_CLI_CONV_RC_CLI.MATRICULA,
		RC_VD_CONV.CD_CTR,
		RC_CLI_CONV.DS_CONV,
		PERC_CONV,
		CASE
		 WHEN RC_CLI_CONV.MES_VENCTO = 0
		 THEN CASE
				  WHEN DATEPART(DAY, @DT_FECHA) < @EOMonth AND DATEPART(DAY, @DT_FECHA) > RC_CLI_CONV.DIA_VENCTO
				  THEN DATEADD(DAY, RC_CLI_CONV.DIA_VENCTO, (DATEADD(DAY, -DATEPART(DAY, DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))), DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112)))))
				  ELSE DATEADD(MONTH, -1, DATEADD(DAY, RC_CLI_CONV.DIA_VENCTO, (DATEADD(DAY, -DATEPART(DAY, DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))), DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))))))
			  END
		 WHEN RC_CLI_CONV.MES_VENCTO = 1
		 THEN DATEADD(DAY, RC_CLI_CONV.DIA_VENCTO, (DATEADD(DAY, -DATEPART(DAY, DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))), DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112)))))
		 ELSE DATEADD(MONTH, 1, DATEADD(DAY, RC_CLI_CONV.DIA_VENCTO, (DATEADD(DAY, -DATEPART(DAY, DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))), DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))))))
		END,
		RC_VD_CONV.CD_EMP,
		RC_CLI_CONV.MES_VENCTO,
		PDV_VD_RC_VD_CONV.CD_VD,
		PDV_VD.NR_ECF,
	 -- DEVOLUCAO,
		RC_VD_CONV.CD_FILIAL,
		RC_CLI.FLAG_CONVENIADO_DEMISSAO,
		RC_CLI.CD_PLANO_VENDA,
		RC_CLI_CONV.CONV_HABILITADO
	UNION ALL
	SELECT 
		RC_CLI_CONV.CD_CONV,
		RC_CLI_CONV_RC_CLI.CD_CLI,
		RC_CLI.RZ_CLI,
		RC_CLI_CONV_RC_CLI.MATRICULA,
		RC_VD_CONV.CD_CTR,
		SUM(RC_VD_CONV.VLR_CONV) AS VLR_CONV,  
		(SUM (DISTINCT RC_VD_CONV.VLR_CONV) - ISNULL((SELECT SUM(RC_VD_CONV_CRED.VLR_CRED) FROM RC_VD_CONV_CRED	WHERE RC_VD_CONV_CRED.CD_EMP = RC_VD_CONV.CD_EMP AND RC_VD_CONV_CRED.CD_CTR = RC_VD_CONV.CD_CTR), 0)) AS VLR_CONV,	
		0 AS VLR_NCC,
		RC_CLI_CONV.DS_CONV,
		RC_CLI_CONV.PERC_CONV,
		CASE
		  WHEN RC_CLI_CONV.MES_VENCTO = 0
		  THEN CASE
				WHEN DATEPART(DAY, @DT_FECHA) < @EOMonth AND DATEPART(DAY, @DT_FECHA) > RC_CLI_CONV.DIA_VENCTO
				THEN DATEADD(DAY, RC_CLI_CONV.DIA_VENCTO, (DATEADD(DAY, -DATEPART(DAY, DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))), DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112)))))
				ELSE DATEADD(MONTH, -1, DATEADD(DAY, RC_CLI_CONV.DIA_VENCTO, (DATEADD(DAY, -DATEPART(DAY, DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))), DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))))))
			END
		  WHEN RC_CLI_CONV.MES_VENCTO = 1
		  THEN DATEADD(DAY, RC_CLI_CONV.DIA_VENCTO, (DATEADD(DAY, -DATEPART(DAY, DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))), DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112)))))
		  ELSE DATEADD(MONTH, 1, DATEADD(DAY, RC_CLI_CONV.DIA_VENCTO, (DATEADD(DAY, -DATEPART(DAY, DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))), DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))))))
		END AS DT_VENCTO,
		SUM(EST_NF_SAI_IT.VLR_DESC_IT) AS VLR_DESC,
		0 AS CD_CLI_DEPEND,
		'' AS RZ_TITULAR,
		CASE
		  WHEN RC_CLI_CONV.MES_VENCTO = 0
		  THEN '0 - VENCIMENTO MES CORRENTE'
		  WHEN RC_CLI_CONV.MES_VENCTO = 1
		  THEN '1 - VENCIMENTO MES SEGUINTE'
		  WHEN RC_CLI_CONV.MES_VENCTO = 2
		  THEN '2 - VENCIMENTO MES POSTERIOR AO SEGUINTE'
		END AS MES_VENCTO,
		EST_NF_SAI_RC_VD_CONV.CD_NF,
		EST_NF_SAI.NF_NF AS NR_DOCUMENTO,
		CASE 
			WHEN 
				EST_NF_SAI_NF_ENT.CD_NF_SAI IS NOT NULL	 
				AND EST_NF_SAI.VLR_NF = EST_NF_ENT.VLR_NF 
			THEN 'DEV TOTAL' 
			WHEN EST_NF_SAI_NF_ENT.CD_NF_SAI IS NOT NULL
				AND  EST_NF_SAI.VLR_NF <> EST_NF_ENT.VLR_NF 
			THEN 'DEV PARCIAL'
			WHEN EST_NF_SAI_NF_ENT.CD_NF_SAI IS NULL 
			THEN 'N'  
		END AS DEVOLUCAO,
		RC_VD_CONV.CD_FILIAL,
		RC_CLI.FLAG_CONVENIADO_DEMISSAO,
		RC_CLI.CD_PLANO_VENDA,
		RC_CLI_CONV.CONV_HABILITADO 
	FROM 
			RC_VD_CONV INNER JOIN RC_CLI_CONV ON 
			RC_VD_CONV.CD_EMP	= RC_CLI_CONV.CD_EMP AND 
			RC_VD_CONV.CD_CONV	= RC_CLI_CONV.CD_CONV
		INNER JOIN RC_CLI ON 
			RC_VD_CONV.CD_EMP = RC_CLI.CD_EMP AND 
			RC_VD_CONV.CD_CLI = RC_CLI.CD_CLI
		INNER JOIN RC_CLI_CONV_RC_CLI ON 
			RC_CLI.CD_EMP = RC_CLI_CONV_RC_CLI.CD_EMP AND 
			RC_CLI.CD_CLI = RC_CLI_CONV_RC_CLI.CD_CLI AND 
			RC_VD_CONV.CD_CONV = RC_CLI_CONV_RC_CLI.CD_CONV
		INNER JOIN EST_NF_SAI_RC_VD_CONV ON 
			RC_VD_CONV.CD_EMP = EST_NF_SAI_RC_VD_CONV.CD_EMP AND 
			RC_VD_CONV.CD_FILIAL = EST_NF_SAI_RC_VD_CONV.CD_FILIAL AND 
			RC_VD_CONV.CD_CTR = EST_NF_SAI_RC_VD_CONV.CD_CTR
		INNER JOIN EST_NF_SAI ON 
			EST_NF_SAI_RC_VD_CONV.CD_EMP = EST_NF_SAI.CD_EMP AND 
			EST_NF_SAI_RC_VD_CONV.CD_FILIAL = EST_NF_SAI.CD_FILIAL AND 
			EST_NF_SAI_RC_VD_CONV.CD_NF = EST_NF_SAI.CD_NF
		INNER JOIN EST_NF_SAI_IT ON 
			EST_NF_SAI.CD_EMP = EST_NF_SAI_IT.CD_EMP AND 
			EST_NF_SAI.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL AND 
			EST_NF_SAI.CD_NF = EST_NF_SAI_IT.CD_NF
		LEFT OUTER JOIN  EST_NF_SAI_NF_ENT ON 
			EST_NF_SAI.CD_EMP = EST_NF_SAI_NF_ENT.CD_EMP
			AND EST_NF_SAI.CD_FILIAL = EST_NF_SAI_NF_ENT.CD_FILIAL
			AND EST_NF_SAI.CD_NF = EST_NF_SAI_NF_ENT.CD_NF_SAI
		LEFT OUTER JOIN EST_NF_ENT ON 
			EST_NF_SAI_NF_ENT.CD_EMP = EST_NF_ENT.CD_EMP
			AND EST_NF_SAI_NF_ENT.CD_FILIAL = EST_NF_ENT.CD_FILIAL
			AND EST_NF_SAI_NF_ENT.CD_NF_ENT = EST_NF_ENT.CD_NF
		LEFT OUTER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON 
			EST_NF_SAI_IT.CD_EMP = ARV.CD_EMP AND 
			EST_NF_SAI_IT.CD_PROD = ARV.CD_PROD
		LEFT JOIN RC_CLI_CONV_DESC_FAT ON 
			RC_CLI_CONV.CD_EMP = RC_CLI_CONV_DESC_FAT.CD_EMP AND 
			RC_CLI_CONV.CD_CONV = RC_CLI_CONV_DESC_FAT.CD_CONV AND 
			ARV.CD_ARV_MERC_LINHA = RC_CLI_CONV_DESC_FAT.CD_ARV_MERC_LINHA
		INNER JOIN #RsPrcFilial f on
			RC_VD_CONV.CD_EMP	= f.CD_EMP AND
			RC_VD_CONV.CD_FILIAL = f.CD_FILIAL 
		INNER JOIN #RsConv C on
			RC_VD_CONV.CD_EMP = C.CD_EMP AND
			RC_VD_CONV.CD_CONV = C.CD_CONV
	WHERE 
		 EST_NF_SAI.CD_EMP = @CD_EMP
		 AND RC_VD_CONV.VLR_CONV > 0
		 AND ISNULL(RC_VD_CONV.FLAG_CONV_AFASTADO, 0) = 0
		 AND RC_VD_CONV.DT_FECHA <= (CASE   -- CASE IMPLEMENTADO PARA RETORNAR TODAS AS VENDAS DE FUCNIONÁRIOS DEMITIDOS 
										  WHEN ISNULL(RC_CLI.FLAG_CONVENIADO_DEMISSAO,0) <> 1 THEN @DT_FECHA
										  ELSE EOMONTH (@DT_FECHA, 180)
									 END)
		 AND RC_VD_CONV.ST_FECHVD_CONV IN(5, 0)
		 AND EST_NF_SAI.STS_NF = 0
	GROUP BY 
		RC_CLI_CONV.CD_CONV,
		RC_CLI_CONV_RC_CLI.CD_CLI,
		RC_CLI.RZ_CLI,
		RC_CLI_CONV_RC_CLI.MATRICULA,
		RC_VD_CONV.CD_CTR,
		RC_CLI_CONV.DS_CONV,
		PERC_CONV,
		CASE
		 WHEN RC_CLI_CONV.MES_VENCTO = 0
		 THEN CASE
				  WHEN DATEPART(DAY, @DT_FECHA) < @EOMonth AND DATEPART(DAY, @DT_FECHA) > RC_CLI_CONV.DIA_VENCTO
				  THEN DATEADD(DAY, RC_CLI_CONV.DIA_VENCTO, (DATEADD(DAY, -DATEPART(DAY, DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))), DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112)))))
				  ELSE DATEADD(MONTH, -1, DATEADD(DAY, RC_CLI_CONV.DIA_VENCTO, (DATEADD(DAY, -DATEPART(DAY, DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))), DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))))))
			  END
		 WHEN RC_CLI_CONV.MES_VENCTO = 1
		 THEN DATEADD(DAY, RC_CLI_CONV.DIA_VENCTO, (DATEADD(DAY, -DATEPART(DAY, DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))), DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112)))))
		 ELSE DATEADD(MONTH, 1, DATEADD(DAY, RC_CLI_CONV.DIA_VENCTO, (DATEADD(DAY, -DATEPART(DAY, DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))), DATEADD(MONTH, 1, CONVERT(CHAR(10), GETDATE(), 112))))))
		END,
		RC_VD_CONV.CD_EMP,
		RC_CLI_CONV.MES_VENCTO,
		EST_NF_SAI_RC_VD_CONV.CD_NF,
		EST_NF_SAI_NF_ENT.CD_NF_SAI,
		EST_NF_SAI.VLR_NF,
		EST_NF_ENT.VLR_NF,
		EST_NF_SAI.NF_NF,
		RC_VD_CONV.CD_FILIAL,
		RC_CLI.FLAG_CONVENIADO_DEMISSAO,
		RC_CLI.CD_PLANO_VENDA,
		RC_CLI_CONV.CONV_HABILITADO
	IF @CONV_HABILITADO=0
	BEGIN
		DELETE FROM 
			#RsResultado
		WHERE
			CONV_HABILITADO <> 0
	END
	IF @CONV_HABILITADO=1
	BEGIN
		DELETE FROM 
			#RsResultado
		WHERE
			CONV_HABILITADO <> 1
	END
	IF @FLAG_CONVENIADO_DEMISSAO=1
	BEGIN
		DELETE FROM 
			#RsResultado
		WHERE
			FLAG_CONVENIADO_DEMISSAO=0
	END
	IF @TIPO_PROC_PLANO_VENDA <> 0
	BEGIN
		--Somente o escolhido
		IF @TIPO_PROC_PLANO_VENDA = 1
			BEGIN
				DELETE FROM 
					#RsResultado
				WHERE
					CD_PLANO_VENDA<>@CD_PLANO_VENDA
			END
		--Exceto o escolhido
		IF @TIPO_PROC_PLANO_VENDA = 2
			BEGIN
				DELETE FROM 
					#RsResultado
				WHERE
					CD_PLANO_VENDA=@CD_PLANO_VENDA
			END
	END
	/*********************************
	--Trecho removido pois j esta abatendo na linha 146 ( ISNULL((SELECT SUM(RC_VD_CONV_CRED.VLR_CRED)  )
	UPDATE 
		P
	SET
		P.VLR_CONV = P.VLR_CONV - C.VLR_CRED
	FROM 
		#RsResultado p INNER JOIN (SELECT 
										RC_VD_CONV_CRED.CD_CTR,
										SUM(RC_VD_CONV_CRED.VLR_CRED) AS VLR_CRED
									FROM		
										#RsResultado p INNER JOIN RC_VD_CONV_CRED ON
										P.CD_CTR = RC_VD_CONV_CRED.CD_CTR
									GROUP BY
										RC_VD_CONV_CRED.CD_CTR) C ON
		P.CD_CTR = C.CD_CTR
	*********************************/
	INSERT INTO #RsNCCPendente
	(CD_CTR,
	VLR_NCC)
	SELECT 
		R.CD_CTR,
		SUM(DISTINCT PDV_VD_DEV_NCC.VLR_NCC)
	FROM 
		PDV_VD_DEV_NCC INNER JOIN PDV_VD_DEV_NCC_PDV_VD_DEV_EST_PROD_IMP IMP ON 
		PDV_VD_DEV_NCC.CD_CTR_NCC = IMP.CD_CTR_NCC
		INNER JOIN #RsResultado R ON 
		@CD_EMP = IMP.CD_EMP AND 
		R.CD_FILIAL = IMP.CD_FILIAL AND 
		R.CD_VD = IMP.CD_VD AND
		PDV_VD_DEV_NCC.CD_CLI = R.CD_CLI
	WHERE 
		PDV_VD_DEV_NCC.STS_NCC = 0
	GROUP BY
		R.CD_CTR;

	UPDATE
		R
	SET 
		R.VLR_NCC = P.VLR_NCC
	FROM
		#RsResultado R INNER JOIN #RsNCCPendente p ON
		r.CD_CTR = p.CD_CTR;

	UPDATE
		R
	SET
		R.CD_CLI_DEPEND=RC_CLI_CONV_DEPENDENTE.CD_CLI_DEPEND,
		R.RZ_TITULAR=RC_CLI_TITULAR.RZ_CLI
	FROM
		#RsResultado R INNER JOIN RC_CLI_CONV_DEPENDENTE ON 
		@CD_EMP = RC_CLI_CONV_DEPENDENTE.CD_EMP AND 
		R.CD_CLI = RC_CLI_CONV_DEPENDENTE.CD_CLI_DEPEND 
		INNER JOIN RC_CLI AS RC_CLI_TITULAR ON 
		RC_CLI_CONV_DEPENDENTE.CD_EMP = RC_CLI_TITULAR.CD_EMP AND 
		RC_CLI_CONV_DEPENDENTE.CD_CLI_TITULAR = RC_CLI_TITULAR.CD_CLI
		SELECT
			CD_CONV,
			CD_CLI,
			RZ_CLI,
			MATRICULA,
			CD_CTR,
			VLR_CONV_BRUTO,
			VLR_CONV,
			VLR_NCC,
			DS_CONV,
			PERC_CONV,
			DT_VENCTO,
			VLR_DESC,
			CD_CLI_DEPEND,
			RZ_TITULAR,
			MES_VENCTO,
			CD_VD,
			NR_DOCUMENTO,
			DEVOLUCAO,
			CD_FILIAL
		FROM
			#RsResultado
		ORDER BY
			DS_CONV,
			CD_CONV,
			RZ_CLI,
			MATRICULA;
	END
	DROP TABLE #RsConv;
	DROP TABLE #RsGrupoEcon;
	DROP TABLE #RsPrcFilial;
	DROP TABLE #RsResultado;
	DROP TABLE #RsNCCPendente;
