IF OBJECT_ID('P_PDV_VD_RC_CLI') IS NOT NULL
    BEGIN
        DROP PROCEDURE P_PDV_VD_RC_CLI;
    END;

GO


/*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 20220112
'Job.................:  
'Função..............: Relatório
'Nome................: P_PDV_VD_RC_CLI
'Objetivo............:  
'Tabelas.............: 
'Dt Criação..........:  
'Dt Ult Alteracão....: 20220112
'Alterado por........: Marcos barros
'Criado por..........: 
'=======================================================================================
Tag #A001 - Alteração do codigo  - Validar numero de cartão para evitar duplicididade no relatório 
Tag #I001 - Inclusão de novo codigo 
Tag #I002 - campo FLAG_FIDELIDADE_LINX_PROMO para validação no retorno
*/
--chamada:
	--P_PDV_VD_RC_CLI 1,'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,98,99,101,102,103','','','2021-10-31','2022-01-12',1,0,2046,0,346

/****************************************************************************************************************
										M E L H O R E S  C L I E N T E S
*****************************************************************************************************************/
CREATE  PROCEDURE [dbo].[P_PDV_VD_RC_CLI] (@CD_EMP				INT,
											 @FILIAL 			VARCHAR(MAX),
											 @CD_PROD			VARCHAR(MAX) = '',
											 @CD_ARV_MERC_LINHA VARCHAR(MAX) = '',
											 @DT_INI			DATETIME,
											 @DT_FIM			DATETIME,	
											 @FREQ				INT	,
											 @VLR_MIN			MONEY,
											 @CD_CLI			INT=0,
											 @CD_MEDICO_REC 	INT=0,
											 @CD_USU 			INT)
AS
BEGIN
SET NOCOUNT ON;
/****************************************************************************************************************
										T A B E L A S   A U X I L I A R E S
*****************************************************************************************************************/
	DECLARE @QUERY VARCHAR(MAX) = '';

	CREATE TABLE #_RS_FILIAL (CD_EMP INT, CD_FILIAL		INT)

	INSERT INTO #_RS_FILIAL 						
	SELECT 
		CD_EMP,
		CD_FILIAL
	FROM 
		PRC_FILIAL
	WHERE
		CD_EMP = @CD_EMP
		AND CD_FILIAL IN (SELECT ID FROM UFD_PRM_IN_STRING(@FILIAL))
	
	CREATE TABLE  #_RS_FREQ (	CD_EMP			INT,
								CD_CLI			INT,
								FREQ			MONEY,
								DT_ULT_CP		DATETIME)

    CREATE TABLE  #_EST_PROD (	CD_EMP			INT,
						  CD_PROD			INT,
						  DS_PROD  VARCHAR(200),
						  CD_ARV_MERC_LINHA INT,
						  DS_ARV_MERC_LINHA VARCHAR(100))

	CREATE TABLE  #_RS_CLI  (	CD_EMP				INT,
								CD_CLI				INT,
								NR_CARTAO			VARCHAR(150),
								NM_CLI				VARCHAR(250),
								RZ_CLI				VARCHAR(250),
								END_CLI				VARCHAR(200),
								BAIRRO				VARCHAR(100),
								TELEFONE01			VARCHAR(35),
								TELEFONE02			VARCHAR(35),
								TELEFONE03			VARCHAR(35),
								TELEFONE04			VARCHAR(35),
								FLAG_FIDELIDADE_LINX_PROMO	INT
								 )	
	
	CREATE TABLE  #_RS_VD  (	CD_EMP				INT,	
								CD_FILIAL			INT,	
								CD_CLI				INT,
								CD_PROD				INT,
								DS_PROD				VARCHAR(250),
								CD_LINHA			INT,
								DS_LINHA			VARCHAR(250), 
								FORMA_PGTO			VARCHAR(50),
								VLR_VD				MONEY,
								VLR_LIQ_VD			MONEY,
								ITENS				MONEY,
								CD_MEDICO_REC       INT,
								NM_MEDICO           VARCHAR(250),
								DT_VD               DATETIME,
								NR_ECF              VARCHAR(10),
								CD_CX               INT,
								VLR_DESC			MONEY,
								VLR_CUSTO			MONEY,
								VLR_CUSTO_MEDIO		MONEY,
								VLR_CUSTO_GER		MONEY,
								ORIGEM				VARCHAR(100),
							    NR_CARTAO			VARCHAR(150))



								 
/****************************************************************************************************************
									P R O D U T O S
*****************************************************************************************************************/
 IF EXISTS(
			SELECT
				TOP 1
				CD_PROD
			FROM
				GLB_ARV_MERC_FILTROS
			WHERE
				CD_USU=@CD_USU
			) 
	BEGIN
	SET @QUERY = '
		INSERT INTO #_EST_PROD
		SELECT 
			P.CD_EMP, 
			P.CD_PROD, 
			P.DS_PROD,
			EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA,
			EST_ARV_MERC_LINHA.DS_ARV_MERC_LINHA
		FROM
			EST_PROD P INNER JOIN GLB_ARV_MERC_FILTROS T ON
			P.CD_EMP = T.CD_EMP AND
			P.CD_PROD = T.CD_PROD 
			INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON 
			P.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP AND 
			P.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD 
			INNER JOIN EST_ARV_MERC_LINHA ON 
			EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA = EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA AND 
			EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP = EST_ARV_MERC_LINHA.CD_EMP 
		WHERE
			T.CD_USU = '+CONVERT(VARCHAR,@CD_USU);
		IF (@CD_PROD <> '')
		BEGIN
			SET @QUERY = @QUERY + ' AND p.CD_PROD in (SELECT ID FROM UFD_PRM_IN_STRING('''+CONVERT(VARCHAR,@CD_PROD)+'''))';
		END
		IF(@CD_ARV_MERC_LINHA <> '')
		BEGIN
			SET @QUERY = @QUERY + ' AND EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA IN (SELECT ID FROM UFD_PRM_IN_STRING('''+CONVERT(VARCHAR,@CD_ARV_MERC_LINHA)+'''));';
		END

		EXEC (@QUERY);
	END
ELSE
	BEGIN
		
		SET @QUERY  = 'INSERT INTO #_EST_PROD 
		SELECT 
			P.CD_EMP, 
			P.CD_PROD, 
			P.DS_PROD,
			EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA,
			EST_ARV_MERC_LINHA.DS_ARV_MERC_LINHA
		FROM	  
		   EST_PROD P INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ON 
			P.CD_EMP = EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP AND 
			P.CD_PROD = EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD 
			INNER JOIN EST_ARV_MERC_LINHA ON 
			EST_PROD_EST_ARV_MERCADOLOGICA.CD_ARV_MERC_LINHA = EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA AND 
			EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP = EST_ARV_MERC_LINHA.CD_EMP 
		WHERE
		   P.CD_EMP='+ CONVERT(VARCHAR,@CD_EMP);
		IF (@CD_PROD <> '')
		BEGIN
			SET @QUERY = @QUERY + ' AND p.CD_PROD in (SELECT ID FROM UFD_PRM_IN_STRING('''+CONVERT(VARCHAR,@CD_PROD)+'''))';
		END
		IF(@CD_ARV_MERC_LINHA <> '')
		BEGIN
			SET @QUERY = @QUERY + ' AND EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA IN (SELECT ID FROM UFD_PRM_IN_STRING('''+CONVERT(VARCHAR,@CD_ARV_MERC_LINHA)+'''));';
		END

		EXEC (@QUERY);
	END

/****************************************************************************************************************
									D A D O S  D O  C L I E N T E
*****************************************************************************************************************/
	
	CREATE TABLE #_RcCliMov
	(CD_EMP INT,
	 CD_CLI INT)
	
	CREATE TABLE #_EstProdMov
	(CD_EMP INT,
	CD_CLI INT,
	VLR_VD MONEY,
	DT_VD DATETIME,
	QT_VD INT)

IF @CD_CLI=0
BEGIN
	INSERT INTO #_RcCliMov
	SELECT  
		CD_EMP,
		CD_CLI 
	FROM 
		PDV_VD V
	WHERE 		   
		V.CD_EMP = @CD_EMP
		AND V.DT_VD BETWEEN @DT_INI AND @DT_FIM						 
		AND V.ST_VD = 0
	GROUP BY
		CD_EMP,
		CD_CLI 
END
ELSE
BEGIN
	INSERT INTO #_RcCliMov
	SELECT  
		@CD_EMP,
		@CD_CLI 
END	
	
	INSERT INTO #_RS_CLI
	SELECT  
		C.CD_EMP,
		C.CD_CLI,
		LEFT(ISNULL(M.NR_CARTAO,''),150) AS NR_CARTAO,
		C.NM_FANT,
		LEFT(C.RZ_CLI,250),
		LEFT(C.END_CLI,250),
		LEFT(C.BAIRRO,100),
		C.TEL TELEFONE01,
		C.TELEFONE1 TELEFONE02,
		C.TELEFONE2 TELEFONE03,
		C.TELEFONE3 TELEFONE04,
		M.FLAG_FIDELIDADE_LINX_PROMO
	FROM
		RC_CLI C INNER JOIN  #_RcCliMov CLI ON
		C.CD_EMP = CLI.CD_EMP AND 
		C.CD_CLI = CLI.CD_CLI 
		LEFT JOIN RC_CLI_FIDELI_CARTAO M ON
		C.CD_EMP = M.CD_EMP AND 
		C.CD_CLI = M.CD_CLI
	group by
		C.CD_EMP,
		C.CD_CLI,
		LEFT(ISNULL(M.NR_CARTAO,''),150),
		C.NM_FANT,
		LEFT(C.RZ_CLI,250),
		LEFT(C.END_CLI,250),
		LEFT(C.BAIRRO,100),
		C.TEL,
		C.TELEFONE1,
		C.TELEFONE2,
		C.TELEFONE3,
		FLAG_FIDELIDADE_LINX_PROMO

	INSERT INTO #_EstProdMov
	 SELECT 
		V.CD_EMP,
		V.CD_CLI,
		SUM(I.TOT_IT - I.VLR_DESC_TOT_RAT) TOT,
		MAX(V.DT_VD),
		COUNT(DISTINCT V.DT_VD) QTD							
	FROM 
		PDV_VD V 
		INNER JOIN PDV_VD_IT I ON
		V.CD_EMP = I.CD_EMP AND
		V.CD_FILIAL = I.CD_FILIAL AND
		V.CD_VD = I.CD_VD and
		v.DT_VD = I.DT_VD
		INNER JOIN #_RS_FILIAL F ON 
		V.CD_EMP	= F.CD_EMP AND
		V.CD_FILIAL = F.CD_FILIAL 

		INNER JOIN #_EST_PROD p ON 
		i.CD_EMP	= p.CD_EMP AND
		i.CD_PROD	= p.CD_PROD
					   
		INNER JOIN #_RS_CLI c ON 
		V.CD_EMP  = c.CD_EMP AND
		V.CD_CLI  = c.CD_CLI  
	WHERE 
		V.CD_EMP = @CD_EMP
		AND V.DT_VD BETWEEN @DT_INI AND @DT_FIM						 
		AND V.ST_VD = 0
		AND I.ST_IT = 1
	GROUP BY 
		V.CD_EMP, 
		V.CD_CLI	
	HAVING
		SUM(I.TOT_IT - I.VLR_DESC_TOT_RAT) >= @VLR_MIN

	INSERT INTO #_EstProdMov
	SELECT 
		V.CD_EMP,
		V.CD_CLI,
		SUM(I.VLR_TOT_IT - I.VLR_DESC_IT) TOT,
		MAX(V.DT_EMI_NF),
		COUNT(DISTINCT V.DT_EMI_NF) QTD							
	FROM 
		EST_NF_SAI V 
		INNER JOIN EST_NF_SAI_IT I ON
		V.CD_EMP = I.CD_EMP AND
		V.CD_FILIAL = I.CD_FILIAL AND
		V.CD_NF = I.CD_NF 

		INNER JOIN EST_NF_SAI_VENDAS VV ON
		V.CD_EMP = VV.CD_EMP AND
		V.CD_FILIAL = VV.CD_FILIAL AND
		V.CD_NF = VV.CD_NF 

		INNER JOIN #_RS_FILIAL F ON 
		V.CD_EMP	= F.CD_EMP AND
		V.CD_FILIAL = F.CD_FILIAL 

		INNER JOIN #_EST_PROD p ON 
		i.CD_EMP	= p.CD_EMP AND
		i.CD_PROD	= p.CD_PROD
					   
		INNER JOIN #_RS_CLI c ON 
		V.CD_EMP  = c.CD_EMP AND
		V.CD_CLI  = c.CD_CLI  
	WHERE 
		V.CD_EMP = @CD_EMP
		AND V.DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM						 
		AND V.STS_NF = 0
	GROUP BY 
		V.CD_EMP, 
		V.CD_CLI	
	HAVING
		SUM(VLR_TOT_IT - VLR_DESC_IT) >= @VLR_MIN		
		 	
/****************************************************************************************************************
								F R E Q U E N C I A   D E   C O M P R A
*****************************************************************************************************************/
	INSERT INTO #_RS_FREQ
	SELECT 
		M.CD_EMP,
		M.CD_CLI, 
		COUNT(DISTINCT M.DT_VD) QTD,
		MAX(M.DT_VD) DT
	FROM 
		#_EstProdMov M  
	GROUP BY
		M.CD_EMP,
		M.CD_CLI
	HAVING 
		COUNT(DISTINCT M.DT_VD) +0 >= @FREQ;
/****************************************************************************************************************
												V E N D A S
*****************************************************************************************************************/

--SELECT ' #_RS_CLI', * FROM #_RS_CLI

	INSERT INTO #_RS_VD
	SELECT
		V.CD_EMP,
		V.CD_FILIAL,
		V.CD_CLI,
		IT.CD_PROD,
		P.DS_PROD,
		P.CD_ARV_MERC_LINHA,
		P.DS_ARV_MERC_LINHA,
        
		CASE WHEN  V.VLR_DINH  > V.VLR_CHQS AND   V.VLR_DINH  >  V.VLR_CARTAO   AND V.VLR_DINH  >  V.VLR_TICKETS    AND V.VLR_DINH  >  V.VLR_DUP  AND  V.VLR_DINH  >   V.VLR_CONV THEN
				'DINHEIRO'
			WHEN   V.VLR_CHQS >  V.VLR_DINH  AND  V.VLR_CHQS >  V.VLR_CARTAO   AND V.VLR_CHQS  >  V.VLR_TICKETS    AND V.VLR_CHQS  >  V.VLR_DUP  AND  V.VLR_CHQS  >   V.VLR_CONV THEN
				'CHEQUE'
			WHEN  V.VLR_CARTAO > V.VLR_CHQS  AND  V.VLR_CARTAO > V.VLR_DINH   AND V.VLR_CARTAO >  V.VLR_TICKETS    AND  V.VLR_CARTAO >  V.VLR_DUP  AND  V.VLR_CARTAO >   V.VLR_CONV THEN
				'CARTAO'
			WHEN  V.VLR_TICKETS  > V.VLR_CHQS AND   V.VLR_TICKETS  >  V.VLR_CARTAO   AND V.VLR_TICKETS  >  V.VLR_DINH AND V.VLR_TICKETS  >  V.VLR_DUP  AND V.VLR_TICKETS  >  V.VLR_CONV THEN
				'TICKET'
			WHEN  V.VLR_DUP  > V.VLR_CHQS AND   V.VLR_DUP  >  V.VLR_CARTAO   AND V.VLR_DUP  >  V.VLR_TICKETS    AND V.VLR_DUP  >  V.VLR_DINH  AND  V.VLR_DUP  >   V.VLR_CONV THEN
				'DUPLICATA'
			WHEN  V.VLR_CONV  > V.VLR_CHQS AND   V.VLR_CONV  >  V.VLR_CARTAO   AND V.VLR_CONV  >  V.VLR_TICKETS    AND V.VLR_CONV  >  V.VLR_DUP  AND  V.VLR_CONV  >   V.VLR_DINH THEN
				'CONVENIO'
			ELSE
				'NAO DEFINIDO' 
		END AS FORMA_PGTO,

		SUM	(IT.QT_IT * IT.VLR_IT) VLR_VD,
		SUM	(IT.TOT_IT) VLR_VD_LIQ,
		SUM	(IT.QT_IT) ITENS,
		ISNULL(R.CD_MEDICO_REC,0)			AS CD_MEDICO_REC,
		ISNULL(M.NM_MEDICO,'NAO INFORMADO') AS NM_MEDICO,
		V.DT_VD,
		0	AS NR_ECF,
		0	AS CD_CX,
		SUM(ROUND(IT.QT_IT * IT.VLR_IT * IT.TX_DESC / 100, 2))  AS VLR_DESC_IT,
        SUM(CASE WHEN IT.TP_PROD IN(10, 11) THEN 
				IT.TOT_IT - (IT.TOT_IT * (ISNULL(IT.PERC_COMISSAO_SERVICO,0) / 100))
            ELSE 
				ISNULL(IT.VLR_CUSTO, 0) * IT.QT_IT
			END) AS VLR_CUSTO,
        SUM(CASE WHEN IT.TP_PROD IN(10, 11) THEN 
				IT.TOT_IT - (IT.TOT_IT * (ISNULL(IT.PERC_COMISSAO_SERVICO,0) / 100))
			ELSE 
				ISNULL(IT.VLR_CUSTO_MEDIO, 0) * IT.QT_IT
			END) AS VLR_CUSTO_MEDIO,
        SUM(CASE WHEN IT.TP_PROD IN(10, 11) THEN 
				IT.TOT_IT - (IT.TOT_IT * (ISNULL(IT.PERC_COMISSAO_SERVICO,0) / 100))
            ELSE 
				ISNULL(IT.VLR_CUSTO_GER, 0) * IT.QT_IT
        END) AS VLR_CUSTO_GER,
		'VENDAS CUPOM FISCAL'	AS ORIGEM
		,V.NR_CARTAO_MARKA		AS NR_CARTAO 
	FROM
		PDV_VD V INNER JOIN PDV_VD_IT IT ON
		V.CD_EMP = IT.CD_EMP AND
		V.CD_FILIAL = IT.CD_FILIAL AND
		V.CD_VD = IT.CD_VD AND
		V.DT_VD = IT.DT_VD

	   INNER JOIN #_EST_PROD P ON
	   IT.CD_EMP = P.CD_EMP AND
	   IT.CD_PROD = P.CD_PROD
 		
	   INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA A ON
	   P.CD_EMP = A.CD_EMP AND
	   P.CD_PROD = A.CD_PROD
 		
	   INNER JOIN #_RS_FREQ F ON 
	   V.CD_EMP	 = F.CD_EMP 
	   AND V.CD_CLI = F.CD_CLI   
		
	   LEFT JOIN PDV_VD_IT_REC R ON 
	   R.CD_EMP    = IT.CD_EMP AND
	   R.CD_FILIAL = IT.CD_FILIAL AND
	   R.CD_VD = IT.CD_VD  AND
	   R.CD_IT = IT.CD_IT 

		
	   INNER JOIN #_RS_FILIAL FL ON 
	   V.CD_EMP	= FL.CD_EMP AND
	   V.CD_FILIAL = FL.CD_FILIAL 
		
	   LEFT JOIN GLB_MEDICOS M ON 
	   R.CD_EMP	    = M.CD_EMP AND
	   R.CD_MEDICO_REC = M.CD_CTR 	    
	WHERE
	   V.CD_EMP = @CD_EMP 
	   AND V.DT_VD BETWEEN @DT_INI AND @DT_FIM  
	   AND V.ST_VD=0
	   AND IT.ST_IT=1
	GROUP BY
		V.CD_EMP,
		V.CD_FILIAL,
		V.CD_CLI,
		IT.CD_PROD,
		P.DS_PROD,
		P.CD_ARV_MERC_LINHA,
		P.DS_ARV_MERC_LINHA,
		V.VLR_CONV,
		V.VLR_CHQS ,
		V.VLR_CARTAO,
		V.VLR_TICKETS,
		V.VLR_DUP,
		V.VLR_DINH,
		R.CD_MEDICO_REC,
		M.NM_MEDICO,
		V.DT_VD
	 
	   ,V.NR_CARTAO_MARKA	

--SELECT '#_RS_VD', * FROM #_RS_VD


	INSERT INTO #_RS_VD
	SELECT
		V.CD_EMP,
		V.CD_FILIAL,
		V.CD_CLI,
		IT.CD_PROD,
		P.DS_PROD,
		P.CD_ARV_MERC_LINHA,
		P.DS_ARV_MERC_LINHA,
		'DUPLICATA' AS FORMA_PGTO,
		SUM(IT.VLR_TOT_IT) VLR_VD,
		SUM(IT.VLR_TOT_IT - IT.VLR_DESC_IT) VLR_VD_LIQ,
		SUM	(IT.QT_IT) ITENS,
		0 AS CD_MEDICO_REC,
		'NAO INFORMADO' AS NM_MEDICO,
		V.DT_EMI_NF,
		0 AS NR_ECF,
		0 AS CD_CX,
		SUM( IT.VLR_DESC_IT)  AS VLR_DESC_IT,
        SUM(ISNULL(IT.VLR_CUSTO_MD, 0) * IT.QT_IT) AS VLR_CUSTO,
        SUM(ISNULL(IT.VLR_CUSTO_MD, 0) * IT.QT_IT) AS VLR_CUSTO_MEDIO,
        SUM(ISNULL(IT.VLR_CUSTO_MD, 0) * IT.QT_IT) AS VLR_CUSTO_GER,
		'VENDAS NOTA FISCAL' AS ORIGEM
		,'0' NR_CARTAO 

	FROM
		EST_NF_SAI V INNER JOIN EST_NF_SAI_IT IT ON
		V.CD_EMP = IT.CD_EMP AND
		V.CD_FILIAL = IT.CD_FILIAL AND
		V.CD_NF = IT.CD_NF 
		
		INNER JOIN EST_NF_SAI_VENDAS VV ON
		V.CD_EMP = VV.CD_EMP AND
		V.CD_FILIAL = VV.CD_FILIAL AND
		V.CD_NF = VV.CD_NF 

	    INNER JOIN #_EST_PROD P ON
	    IT.CD_EMP = P.CD_EMP AND
	    IT.CD_PROD = P.CD_PROD
 		
	    INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA A ON
	    P.CD_EMP = A.CD_EMP AND
	    P.CD_PROD = A.CD_PROD
 		
	    INNER JOIN #_RS_FREQ F ON 
	    V.CD_EMP	 = F.CD_EMP 
	    AND V.CD_CLI = F.CD_CLI   
		
		
	    INNER JOIN #_RS_FILIAL FL ON 
	    V.CD_EMP	= FL.CD_EMP AND
	    V.CD_FILIAL = FL.CD_FILIAL 
		
	WHERE
	   V.CD_EMP = @CD_EMP 
	   AND V.DT_EMI_NF BETWEEN @DT_INI AND @DT_FIM  
	   AND V.STS_NF=0
	GROUP BY
		V.CD_EMP,
		V.CD_FILIAL,
		V.CD_CLI,
		IT.CD_PROD,
		P.DS_PROD,
		P.CD_ARV_MERC_LINHA,
		P.DS_ARV_MERC_LINHA,
		V.DT_EMI_NF
	

--SELECT '#_RS_VD', * FROM #_RS_VD	

IF isnull(@CD_MEDICO_REC,0) > 0
BEGIN
	DELETE FROM #_RS_VD WHERE CD_MEDICO_REC <> @CD_MEDICO_REC
END

/****************************************************************************************************************
												R E T O R N O
*****************************************************************************************************************/
IF (SELECT 1 FROM #_RS_CLI WHERE  FLAG_FIDELIDADE_LINX_PROMO=1) = 1
BEGIN
	SELECT
		V.CD_FILIAL,
		V.CD_CLI,
		C.NR_CARTAO,
		C.NM_CLI,
		C.RZ_CLI,
		C.END_CLI,
		C.BAIRRO,
		C.TELEFONE01,
		C.TELEFONE02,
		C.TELEFONE03,
		C.TELEFONE04,
		V.DT_VD,
		f.DT_ULT_CP,
		V.CD_PROD,
		V.DS_PROD,
		V.CD_LINHA,
		V.DS_LINHA,
		V.FORMA_PGTO,
		V.VLR_VD,
		V.VLR_LIQ_VD,
		V.ITENS,
		F.FREQ,
		V.CD_MEDICO_REC,
		V.NM_MEDICO,
		VLR_DESC,
		VLR_CUSTO,
		VLR_CUSTO_MEDIO,
		VLR_CUSTO_GER,
		0 AS NR_ECF,
		0 AS CD_CX,
		ORIGEM
	FROM
		#_RS_VD V
		INNER JOIN #_RS_CLI C ON V.CD_EMP     = C.CD_EMP
							 AND V.CD_CLI     = C.CD_CLI	
		 
		INNER JOIN #_RS_FREQ F ON V.CD_EMP  = F.CD_EMP 
							  AND V.CD_CLI = F.CD_CLI  
END
ELSE 
BEGIN
	SELECT
		V.CD_FILIAL,
		V.CD_CLI,
		C.NR_CARTAO,
		C.NM_CLI,
		C.RZ_CLI,
		C.END_CLI,
		C.BAIRRO,
		C.TELEFONE01,
		C.TELEFONE02,
		C.TELEFONE03,
		C.TELEFONE04,
		V.DT_VD,
		f.DT_ULT_CP,
		V.CD_PROD,
		V.DS_PROD,
		V.CD_LINHA,
		V.DS_LINHA,
		V.FORMA_PGTO,
		V.VLR_VD,
		V.VLR_LIQ_VD,
		V.ITENS,
		F.FREQ,
		V.CD_MEDICO_REC,
		V.NM_MEDICO,
		VLR_DESC,
		VLR_CUSTO,
		VLR_CUSTO_MEDIO,
		VLR_CUSTO_GER,
		0 AS NR_ECF,
		0 AS CD_CX,
		ORIGEM
	FROM
		#_RS_VD V
		INNER JOIN #_RS_CLI C ON V.CD_EMP     = C.CD_EMP
							 AND V.CD_CLI     = C.CD_CLI	
							 AND (V.NR_CARTAO = C.NR_CARTAO OR V.NR_CARTAO = '0' )

		INNER JOIN #_RS_FREQ F ON V.CD_EMP  = F.CD_EMP 
							  AND V.CD_CLI = F.CD_CLI  
END
/****************************************************************************************************************
												F    I    M 
*****************************************************************************************************************/

RETURN

DROP TABLE #_RcCliMov
DROP TABLE #_EstProdMov
DROP TABLE #_RS_VD
DROP TABLE #_RS_CLI
DROP TABLE #_RS_FREQ
END


