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

-- P_PDV_VD_CONVENIO_CONSULTA @CD_EMP =1, @CD_FILIAL=914, @DT_INT = '07/02/2019', @DT_FIM = '07/02/2019',@TP_RELATORIO=3
CREATE PROCEDURE P_PDV_VD_CONVENIO_CONSULTA (@CD_EMP int =1, @CD_FILIAL int , @DT_INT date ,  @DT_FIM date, @TP_RELATORIO INT =0 )
AS 

SET NOCOUNT ON;
/****************************************************************************************************************************/
CREATE TABLE #TMP_RESULT(CD_EMP			INT, 
						   CD_FILIAL		INT, 
						   NM_FILIAL		VARCHAR(160), 
						   CD_CONV			INT, 
						   DS_CONV			VARCHAR(50),  
						   CD_CLI			INT, 
						   NM_FANT			VARCHAR(225),
						   MATRICULA		VARCHAR(50), 
						   VLR_CONV			MONEY,      
						   VLR_CONV_PBM		MONEY,	   		
						   DT_VD			DATE, 
						   CD_CX			INT, 
						   DEV				INT, 
						   SEM_CAD			INT, 
						   NR_ECF			INT, 
						   CD_CTB_CNT		INT, 
						   CD_CTB_CNT_RED	INT, 
						   ST_VD			INT, 
						   TIPO_CONV		INT, 
						   FLAG_FECHA_DIARIO INT,
						   FLAG_EXIGI_EMI_NA_VENDA	INT,
						   FLAG_REQUER_REQUISICAO	INT,
						   OBRIG_RECEIT				INT,      
						   CD_VD					INT,
						   NR_AUTORIZACAO			varchar(50),
						   CONSULTA					INT)


	  --AND V_PDV_VD_CONVENIO.CONSULTA <> 3

/****************************************************************************************************************************/      
	   
	   INSERT INTO #TMP_RESULT(CD_EMP, CD_FILIAL, NM_FILIAL, CD_CONV, DS_CONV, CD_CLI ,NM_FANT, MATRICULA, VLR_CONV, VLR_CONV_PBM, DT_VD, CD_CX, DEV, SEM_CAD, NR_ECF, CD_CTB_CNT, CD_CTB_CNT_RED,	
									ST_VD, TIPO_CONV, FLAG_FECHA_DIARIO ,FLAG_EXIGI_EMI_NA_VENDA,FLAG_REQUER_REQUISICAO, OBRIG_RECEIT, CD_VD, NR_AUTORIZACAO, CONSULTA)
       SELECT DISTINCT
              dbo.PDV_VD.CD_EMP
            , dbo.PDV_VD.CD_FILIAL
            , dbo.PRC_FILIAL.NM_FANT AS NM_FILIAL
            , ISNULL(dbo.RC_CLI_CONV.CD_CONV, 0) AS CD_CONV
            , ISNULL(dbo.RC_CLI_CONV.DS_CONV, 'SEM CLASSIFICACAO') AS DS_CONV
            , dbo.RC_CLI.CD_CLI
            , dbo.RC_CLI.NM_FANT
            , dbo.RC_CLI_CONV_RC_CLI.MATRICULA
            , dbo.PDV_VD.VLR_CONV
            , ISNULL(PDV_VD.VLR_CONV_PBM, 0) AS VLR_CONV_PBM
            , dbo.PDV_VD.DT_VD
            , dbo.PDV_VD.CD_CX
            , 0 AS DEV
            , CASE
                  WHEN RC_VD_CONV.TP_LANC = 0 THEN 0
                  ELSE 1
              END AS SEM_CAD
            , dbo.PDV_VD.NR_ECF
            , dbo.RC_CLI_CONV.CD_CTB_CNT
            , dbo.RC_CLI_CONV.CD_CTB_CNT_RED
            , dbo.PDV_VD.ST_VD
            , 0 AS TIPO_CONV
            , ISNULL(dbo.RC_CLI_CONV.FLAG_FECHA_DIARIO, 0) AS FLAG_FECHA_DIARIO
            , ISNULL(dbo.RC_CLI_CONV.FLAG_EXIGI_EMI_NA_VENDA, 0) AS FLAG_EXIGI_EMI_NA_VENDA
            , ISNULL(dbo.RC_CLI_CONV.FLAG_REQUER_REQUISICAO, 0) AS FLAG_REQUER_REQUISICAO
            , ISNULL(RC_CLI_CONV.OBRIG_RECEIT, 0) AS OBRIG_RECEIT
            , PDV_VD.CD_VD
            , RC_VD_CONV.NR_AUTORIZACAO
            , 0 AS CONSULTA
       FROM
            dbo.PDV_VD
            INNER JOIN PDV_VD_RC_VD_CONV ON PDV_VD.CD_EMP = PDV_VD_RC_VD_CONV.CD_EMP
                                            AND PDV_VD.CD_FILIAL = PDV_VD_RC_VD_CONV.CD_FILIAL
                                            AND PDV_VD.CD_VD = PDV_VD_RC_VD_CONV.CD_VD
            INNER JOIN dbo.RC_VD_CONV ON dbo.PDV_VD_RC_VD_CONV.CD_EMP = dbo.RC_VD_CONV.CD_EMP
                                         AND dbo.PDV_VD_RC_VD_CONV.CD_CTR = dbo.RC_VD_CONV.CD_CTR
            INNER JOIN dbo.RC_CLI ON dbo.RC_CLI.CD_EMP = dbo.RC_VD_CONV.CD_EMP
                                     AND dbo.RC_CLI.CD_CLI = dbo.RC_VD_CONV.CD_CLI
            INNER JOIN dbo.RC_CLI_CONV ON dbo.RC_VD_CONV.CD_EMP = dbo.RC_CLI_CONV.CD_EMP
                                          AND dbo.RC_VD_CONV.CD_CONV = dbo.RC_CLI_CONV.CD_CONV
            INNER JOIN dbo.PRC_FILIAL ON dbo.PDV_VD.CD_EMP = dbo.PRC_FILIAL.CD_EMP
                                         AND dbo.PDV_VD.CD_FILIAL = dbo.PRC_FILIAL.CD_FILIAL
            LEFT OUTER JOIN dbo.RC_CLI_CONV_RC_CLI ON dbo.PDV_VD.CD_EMP = dbo.RC_CLI_CONV_RC_CLI.CD_EMP
                                                      AND dbo.PDV_VD.CD_CLI = dbo.RC_CLI_CONV_RC_CLI.CD_CLI
                                                      AND dbo.PDV_VD.CD_CONV = dbo.RC_CLI_CONV_RC_CLI.CD_CONV
       WHERE 
			PDV_VD.CD_EMP = @CD_EMP
			AND PDV_VD.CD_FILIAL = @CD_FILIAL 
			AND PDV_VD.DT_VD BETWEEN @DT_INT AND @DT_FIM

             AND dbo.RC_CLI.ESP_CLI <> 3
             AND dbo.PDV_VD.ST_VD = 0
             AND dbo.PDV_VD.VLR_CONV > 0
             AND NOT EXISTS(SELECT CD_EMP FROM
								 dbo.V_PDV_VD_DEV_CONVENIOS
							WHERE CD_EMP = dbo.PDV_VD.CD_EMP
								  AND CD_FILIAL = dbo.PDV_VD.CD_FILIAL
								  AND CD_VD = dbo.PDV_VD.CD_VD
								  AND STS_NF = 0)
			AND NOT EXISTS ( SELECT CD_EMP FROM
								PDV_VD_IT_VD_PBM
								WHERE CD_EMP = PDV_VD.CD_EMP
								AND CD_FILIAL = PDV_VD.CD_FILIAL
								AND CD_VD = PDV_VD.CD_VD
								);
										  
/****************************************************************************************************************************/
	--Convenio que tem registro PBM 

	INSERT INTO #TMP_RESULT(CD_EMP, CD_FILIAL, NM_FILIAL, CD_CONV, DS_CONV, CD_CLI ,NM_FANT, MATRICULA, VLR_CONV, VLR_CONV_PBM, DT_VD, CD_CX, DEV, SEM_CAD, NR_ECF, CD_CTB_CNT, CD_CTB_CNT_RED,	
							ST_VD, TIPO_CONV, FLAG_FECHA_DIARIO ,FLAG_EXIGI_EMI_NA_VENDA,FLAG_REQUER_REQUISICAO, OBRIG_RECEIT, CD_VD, NR_AUTORIZACAO, CONSULTA)
		SELECT DISTINCT
				dbo.PDV_VD.CD_EMP
			, dbo.PDV_VD.CD_FILIAL
			, dbo.PRC_FILIAL.NM_FANT AS NM_FILIAL
			, ISNULL(dbo.RC_CLI_CONV.CD_CONV, 0) AS CD_CONV
			, ISNULL(dbo.RC_CLI_CONV.DS_CONV, 'SEM CLASSIFICACAO') AS DS_CONV
			, dbo.RC_CLI.CD_CLI
			, dbo.RC_CLI.NM_FANT
			, dbo.RC_CLI_CONV_RC_CLI.MATRICULA
			, dbo.PDV_VD.VLR_CONV
			, CASE
					WHEN PDV_VD.CD_CLI_PBM > 0 THEN 0
					ELSE ISNULL(PDV_VD.VLR_CONV_PBM, 0)
				END AS VLR_CONV_PBM
			, dbo.PDV_VD.DT_VD
			, dbo.PDV_VD.CD_CX
			, 0 AS DEV
			, CASE
					WHEN RC_VD_CONV.TP_LANC = 0 THEN 0
					ELSE 1
				END AS SEM_CAD
			, dbo.PDV_VD.NR_ECF
			, dbo.RC_CLI_CONV.CD_CTB_CNT
			, dbo.RC_CLI_CONV.CD_CTB_CNT_RED
			, dbo.PDV_VD.ST_VD
			, 0 AS TIPO_CONV
			, ISNULL(dbo.RC_CLI_CONV.FLAG_FECHA_DIARIO, 0) AS FLAG_FECHA_DIARIO
			, ISNULL(dbo.RC_CLI_CONV.FLAG_EXIGI_EMI_NA_VENDA, 0) AS FLAG_EXIGI_EMI_NA_VENDA
			, ISNULL(dbo.RC_CLI_CONV.FLAG_REQUER_REQUISICAO, 0) AS FLAG_REQUER_REQUISICAO
			, ISNULL(RC_CLI_CONV.OBRIG_RECEIT, 0) AS OBRIG_RECEIT
			, PDV_VD.CD_VD
			, RC_VD_CONV.NR_AUTORIZACAO
			, 1 AS CONSULTA
		FROM
			dbo.PDV_VD
			INNER JOIN PDV_VD_RC_VD_CONV ON PDV_VD.CD_EMP = PDV_VD_RC_VD_CONV.CD_EMP
											AND PDV_VD.CD_FILIAL = PDV_VD_RC_VD_CONV.CD_FILIAL
											AND PDV_VD.CD_VD = PDV_VD_RC_VD_CONV.CD_VD
			INNER JOIN dbo.RC_VD_CONV ON dbo.PDV_VD_RC_VD_CONV.CD_EMP = dbo.RC_VD_CONV.CD_EMP
											AND dbo.PDV_VD_RC_VD_CONV.CD_CTR = dbo.RC_VD_CONV.CD_CTR
			INNER JOIN dbo.RC_CLI ON dbo.RC_CLI.CD_EMP = dbo.RC_VD_CONV.CD_EMP
										AND dbo.RC_CLI.CD_CLI = dbo.RC_VD_CONV.CD_CLI
			INNER JOIN dbo.RC_CLI_CONV ON dbo.RC_VD_CONV.CD_EMP = dbo.RC_CLI_CONV.CD_EMP
											AND dbo.RC_VD_CONV.CD_CONV = dbo.RC_CLI_CONV.CD_CONV
			INNER JOIN dbo.PRC_FILIAL ON dbo.PDV_VD.CD_EMP = dbo.PRC_FILIAL.CD_EMP
											AND dbo.PDV_VD.CD_FILIAL = dbo.PRC_FILIAL.CD_FILIAL
			LEFT OUTER JOIN dbo.RC_CLI_CONV_RC_CLI ON dbo.RC_VD_CONV.CD_EMP = dbo.RC_CLI_CONV_RC_CLI.CD_EMP
														AND dbo.RC_VD_CONV.CD_CLI = dbo.RC_CLI_CONV_RC_CLI.CD_CLI
														AND RC_VD_CONV.CD_CONV = RC_CLI_CONV_RC_CLI.CD_CONV
		WHERE 
			PDV_VD.CD_EMP = @CD_EMP
			AND PDV_VD.CD_FILIAL = @CD_FILIAL 
			AND PDV_VD.DT_VD BETWEEN @DT_INT AND @DT_FIM

			AND dbo.RC_CLI.ESP_CLI IN(2, 6)
			AND dbo.PDV_VD.ST_VD = 0
			AND dbo.PDV_VD.VLR_CONV > 0
			AND NOT EXISTS( SELECT CD_EMP FROM
									dbo.V_PDV_VD_DEV_CONVENIOS
							WHERE CD_EMP = dbo.PDV_VD.CD_EMP
									AND CD_FILIAL = dbo.PDV_VD.CD_FILIAL
									AND CD_VD = dbo.PDV_VD.CD_VD
									AND STS_NF = 0
							)
			AND EXISTS( SELECT CD_EMP FROM
								PDV_VD_IT_VD_PBM
						WHERE CD_EMP = PDV_VD.CD_EMP
								AND CD_FILIAL = PDV_VD.CD_FILIAL
								AND CD_VD = PDV_VD.CD_VD
						);


/****************************************************************************************************************************/
-- vendas com valores em convenio pbm e com vendas feitas para convenio normal

	INSERT INTO #TMP_RESULT(CD_EMP, CD_FILIAL, NM_FILIAL, CD_CONV, DS_CONV, CD_CLI ,NM_FANT, MATRICULA, VLR_CONV, VLR_CONV_PBM, DT_VD, CD_CX, DEV, SEM_CAD, NR_ECF, CD_CTB_CNT, CD_CTB_CNT_RED,	
							ST_VD, TIPO_CONV, FLAG_FECHA_DIARIO ,FLAG_EXIGI_EMI_NA_VENDA,FLAG_REQUER_REQUISICAO, OBRIG_RECEIT, CD_VD, NR_AUTORIZACAO, CONSULTA)
	
	SELECT DISTINCT
		   dbo.PDV_VD.CD_EMP
		 , dbo.PDV_VD.CD_FILIAL
		 , dbo.PRC_FILIAL.NM_FANT AS NM_FILIAL
		 , ISNULL(dbo.RC_CLI_CONV.CD_CONV, 0) AS CD_CONV
		 , ISNULL(dbo.RC_CLI_CONV.DS_CONV, 'SEM CLASSIFICACAO') AS DS_CONV
		 , dbo.PDV_VD.CD_CLI_PBM AS CD_CLI
		 , dbo.RC_CLI.NM_FANT
		 , RC_CLI_CONV_RC_CLI.MATRICULA
		 , CASE
			   WHEN PDV_VD.VLR_CONV > 0 THEN 0
			   ELSE dbo.PDV_VD.VLR_CONV
		   END AS VLR_CONV
		 , ISNULL(PDV_VD.VLR_CONV_PBM, 0) AS VLR_CONV_PBM
		 , dbo.PDV_VD.DT_VD
		 , dbo.PDV_VD.CD_CX
		 , 0 AS DEV
		 , 0 AS SEM_CAD
		 , dbo.PDV_VD.NR_ECF
		 , dbo.RC_CLI_CONV.CD_CTB_CNT
		 , dbo.RC_CLI_CONV.CD_CTB_CNT_RED
		 , dbo.PDV_VD.ST_VD
		 , 1 AS TIPO_CONV
		 , ISNULL(dbo.RC_CLI_CONV.FLAG_FECHA_DIARIO, 0) AS FLAG_FECHA_DIARIO
		 , ISNULL(dbo.RC_CLI_CONV.FLAG_EXIGI_EMI_NA_VENDA, 0) AS FLAG_EXIGI_EMI_NA_VENDA
		 , ISNULL(dbo.RC_CLI_CONV.FLAG_REQUER_REQUISICAO, 0) AS FLAG_REQUER_REQUISICAO
		 , ISNULL(RC_CLI_CONV.OBRIG_RECEIT, 0) AS OBRIG_RECEIT
		 , PDV_VD.CD_VD
		 , '' AS NR_AUTORIZACAO
		 , 2 AS CONSULTA
	FROM
		 dbo.PDV_VD
		 INNER JOIN dbo.RC_CLI ON dbo.PDV_VD.CD_EMP = dbo.RC_CLI.CD_EMP
								  AND dbo.PDV_VD.CD_CLI_PBM = dbo.RC_CLI.CD_CLI
		 INNER JOIN dbo.PRC_FILIAL ON dbo.PDV_VD.CD_EMP = dbo.PRC_FILIAL.CD_EMP
									  AND dbo.PDV_VD.CD_FILIAL = dbo.PRC_FILIAL.CD_FILIAL
		 LEFT OUTER JOIN dbo.RC_CLI_CONV_RC_CLI ON dbo.PDV_VD.CD_EMP = dbo.RC_CLI_CONV_RC_CLI.CD_EMP
												   AND dbo.PDV_VD.CD_CLI_PBM = dbo.RC_CLI_CONV_RC_CLI.CD_CLI
		 LEFT OUTER JOIN dbo.RC_CLI_CONV ON dbo.RC_CLI_CONV_RC_CLI.CD_EMP = dbo.RC_CLI_CONV.CD_EMP
											AND dbo.RC_CLI_CONV_RC_CLI.CD_CONV = dbo.RC_CLI_CONV.CD_CONV
		 LEFT OUTER JOIN dbo.PDV_VD_RC_VD_CONV ON dbo.PDV_VD.CD_EMP = dbo.PDV_VD_RC_VD_CONV.CD_EMP
												  AND dbo.PDV_VD.CD_FILIAL = dbo.PDV_VD_RC_VD_CONV.CD_FILIAL
												  AND dbo.PDV_VD.CD_VD = dbo.PDV_VD_RC_VD_CONV.CD_VD
	WHERE 
	
		PDV_VD.CD_EMP = @CD_EMP
		AND PDV_VD.CD_FILIAL = @CD_FILIAL 
		AND PDV_VD.DT_VD BETWEEN @DT_INT AND @DT_FIM
		AND PDV_VD.ST_VD = 0
		AND dbo.PDV_VD.VLR_CONV_PBM > 0
		AND dbo.PDV_VD.VLR_CONV > 0
	    AND NOT EXISTS( SELECT CD_EMP FROM V_PDV_VD_DEV_CONVENIOS
							WHERE CD_EMP = dbo.PDV_VD.CD_EMP
								  AND CD_FILIAL = dbo.PDV_VD.CD_FILIAL
								  AND CD_VD = dbo.PDV_VD.CD_VD
								  AND STS_NF = 0
						)
		AND EXISTS ( SELECT CD_EMP FROM PDV_VD_IT_VD_PBM
						WHERE CD_EMP = PDV_VD.CD_EMP
			  AND CD_FILIAL = PDV_VD.CD_FILIAL
			  AND CD_VD = PDV_VD.CD_VD);            


	
/****************************************************************************************************************************/

	INSERT INTO #TMP_RESULT(CD_EMP, CD_FILIAL, NM_FILIAL, CD_CONV, DS_CONV, CD_CLI ,NM_FANT, MATRICULA, VLR_CONV, VLR_CONV_PBM, DT_VD, CD_CX, DEV, SEM_CAD, NR_ECF, CD_CTB_CNT, CD_CTB_CNT_RED,	
							ST_VD, TIPO_CONV, FLAG_FECHA_DIARIO ,FLAG_EXIGI_EMI_NA_VENDA,FLAG_REQUER_REQUISICAO, OBRIG_RECEIT, CD_VD, NR_AUTORIZACAO, CONSULTA)

	SELECT DISTINCT
		   PDV_VD.CD_EMP
		 , PDV_VD.CD_FILIAL
		 , PRC_FILIAL.NM_FANT AS NM_FILIAL
		 , RC_CLI_CONV.CD_CONV
		 , RC_CLI_CONV.DS_CONV
		 , RC_CLI.CD_CLI
		 , RC_CLI.NM_FANT
		 , RC_CLI_CONV_RC_CLI.MATRICULA
		 , PDV_VD.VLR_CONV
		 , ISNULL(PDV_VD.VLR_CONV_PBM, 0) AS VLR_CONV_PBM
		 , PDV_VD.DT_VD
		 , PDV_VD.CD_CX
		 , 1 AS DEV
		 , 1 AS SEM_CAD
		 , PDV_VD.NR_ECF
		 , RC_CLI_CONV.CD_CTB_CNT
		 , RC_CLI_CONV.CD_CTB_CNT_RED
		 , PDV_VD.ST_VD
		 , 0 AS TIPO_CONV
		 , ISNULL(RC_CLI_CONV.FLAG_FECHA_DIARIO, 0) AS FLAG_FECHA_DIARIO
		 , ISNULL(RC_CLI_CONV.FLAG_EXIGI_EMI_NA_VENDA, 0) AS FLAG_EXIGI_EMI_NA_VENDA
		 , ISNULL(RC_CLI_CONV.FLAG_REQUER_REQUISICAO, 0) AS FLAG_REQUER_REQUISICAO
		 , ISNULL(RC_CLI_CONV.OBRIG_RECEIT, 0) AS OBRIG_RECEIT
		 , PDV_VD.CD_VD
		 , RC_VD_CONV.NR_AUTORIZACAO
		 , 3 AS CONSULTA
	FROM
		 dbo.PDV_VD
		 INNER JOIN dbo.RC_CLI ON PDV_VD.CD_EMP = RC_CLI.CD_EMP
								  AND PDV_VD.CD_CLI = RC_CLI.CD_CLI
		 INNER JOIN dbo.RC_CLI_CONV_RC_CLI ON PDV_VD.CD_EMP = RC_CLI_CONV_RC_CLI.CD_EMP
											  AND PDV_VD.CD_CLI = RC_CLI_CONV_RC_CLI.CD_CLI
											  AND PDV_VD.CD_CONV = RC_CLI_CONV_RC_CLI.CD_CONV
		 INNER JOIN dbo.RC_CLI_CONV ON PDV_VD.CD_EMP = RC_CLI_CONV.CD_EMP
									   AND PDV_VD.CD_CONV = RC_CLI_CONV.CD_CONV
		 INNER JOIN dbo.PRC_FILIAL ON PDV_VD.CD_EMP = PRC_FILIAL.CD_EMP
									  AND PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL
		 LEFT OUTER JOIN dbo.PDV_VD_RC_VD_CONV ON PDV_VD.CD_EMP = PDV_VD_RC_VD_CONV.CD_EMP
												  AND PDV_VD.CD_FILIAL = PDV_VD_RC_VD_CONV.CD_FILIAL
												  AND PDV_VD.CD_VD = PDV_VD_RC_VD_CONV.CD_VD
		 INNER JOIN dbo.RC_VD_CONV AS RC_VD_CONV ON PDV_VD_RC_VD_CONV.CD_EMP = RC_VD_CONV.CD_EMP
													AND PDV_VD_RC_VD_CONV.CD_CTR = RC_VD_CONV.CD_CTR
													AND RC_VD_CONV.CD_CONV = RC_CLI_CONV.CD_CONV
	WHERE 
		PDV_VD.CD_EMP = @CD_EMP
		AND PDV_VD.CD_FILIAL = @CD_FILIAL 
		AND PDV_VD.DT_VD BETWEEN @DT_INT AND @DT_FIM
		AND RC_CLI.ESP_CLI <> 3
		AND PDV_VD.ST_VD = 0
		AND EXISTS( SELECT V_PDV_VD_DEV_CONVENIOS.CD_EMP
					FROM
							dbo.V_PDV_VD_DEV_CONVENIOS
					WHERE V_PDV_VD_DEV_CONVENIOS.CD_EMP = PDV_VD.CD_EMP
							AND V_PDV_VD_DEV_CONVENIOS.CD_FILIAL = PDV_VD.CD_FILIAL
							AND V_PDV_VD_DEV_CONVENIOS.CD_VD = PDV_VD.CD_VD
							AND V_PDV_VD_DEV_CONVENIOS.STS_NF = 0
							AND CANCEL_CONVENIO = 1)
		  AND NOT EXISTS( SELECT CD_EMP FROM
						 PDV_VD_IT_VD_PBM
					WHERE CD_EMP = PDV_VD.CD_EMP
						  AND CD_FILIAL = PDV_VD.CD_FILIAL
						  AND CD_VD = PDV_VD.CD_VD
				);                            


	
/****************************************************************************************************************************/

	INSERT INTO #TMP_RESULT(CD_EMP, CD_FILIAL, NM_FILIAL, CD_CONV, DS_CONV, CD_CLI ,NM_FANT, MATRICULA, VLR_CONV, VLR_CONV_PBM, DT_VD, CD_CX, DEV, SEM_CAD, NR_ECF, CD_CTB_CNT, CD_CTB_CNT_RED,	
							ST_VD, TIPO_CONV, FLAG_FECHA_DIARIO ,FLAG_EXIGI_EMI_NA_VENDA,FLAG_REQUER_REQUISICAO, OBRIG_RECEIT, CD_VD, NR_AUTORIZACAO, CONSULTA)
	SELECT DISTINCT
		   PDV_VD.CD_EMP
		 , PDV_VD.CD_FILIAL
		 , PRC_FILIAL.NM_FANT AS NM_FILIAL
		 , ISNULL(RC_CLI_CONV.CD_CONV, 0) AS CD_CONV
		 , ISNULL(RC_CLI_CONV.DS_CONV, 'SEM CLASSIFICACAO') AS DS_CONV
		 , RC_CLI.CD_CLI
		 , RC_CLI.NM_FANT
		 , RC_CLI_CONV_RC_CLI.MATRICULA
		 , PDV_VD.VLR_CONV
		 , ISNULL(PDV_VD.VLR_CONV_PBM, 0) AS VLR_CONV_PBM
		 , PDV_VD.DT_VD
		 , PDV_VD.CD_CX
		 , 1 AS DEV
		 , -1 AS SEM_CAD
		 , PDV_VD.NR_ECF
		 , RC_CLI_CONV.CD_CTB_CNT
		 , RC_CLI_CONV.CD_CTB_CNT_RED
		 , PDV_VD.ST_VD
		 , 0 AS TIPO_CONV
		 , ISNULL(RC_CLI_CONV.FLAG_FECHA_DIARIO, 0) AS FLAG_FECHA_DIARIO
		 , ISNULL(RC_CLI_CONV.FLAG_EXIGI_EMI_NA_VENDA, 0) AS FLAG_EXIGI_EMI_NA_VENDA
		 , ISNULL(RC_CLI_CONV.FLAG_REQUER_REQUISICAO, 0) AS FLAG_REQUER_REQUISICAO
		 , ISNULL(RC_CLI_CONV.OBRIG_RECEIT, 0) AS OBRIG_RECEIT
		 , PDV_VD.CD_VD
		 , PDV_VD.NR_AUTORIZACAO
		 , 4 AS CONSULTA
	FROM
		 dbo.PDV_VD
		 INNER JOIN dbo.RC_CLI ON PDV_VD.CD_EMP = RC_CLI.CD_EMP
								  AND PDV_VD.CD_CLI = RC_CLI.CD_CLI
		 INNER JOIN dbo.PRC_FILIAL ON PDV_VD.CD_EMP = PRC_FILIAL.CD_EMP
									  AND PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL
		 INNER JOIN dbo.RC_CLI_CONV ON PDV_VD.CD_EMP = RC_CLI_CONV.CD_EMP
									   AND PDV_VD.CD_CONV = RC_CLI_CONV.CD_CONV
		 INNER JOIN dbo.RC_CLI_CONV_RC_CLI ON PDV_VD.CD_EMP = RC_CLI_CONV_RC_CLI.CD_EMP
											  AND PDV_VD.CD_CLI = RC_CLI_CONV_RC_CLI.CD_CLI
											  AND PDV_VD.CD_CONV = RC_CLI_CONV_RC_CLI.CD_CONV
	WHERE 
		PDV_VD.CD_EMP = @CD_EMP
		AND PDV_VD.CD_FILIAL = @CD_FILIAL 
		AND PDV_VD.DT_VD BETWEEN @DT_INT AND @DT_FIM
		AND RC_CLI.ESP_CLI <> 3
		AND PDV_VD.ST_VD = 0
		AND PDV_VD.VLR_CONV > 0
		AND EXISTS ( SELECT V_PDV_VD_DEV_CONVENIOS.CD_EMP FROM
					dbo.V_PDV_VD_DEV_CONVENIOS
					WHERE V_PDV_VD_DEV_CONVENIOS.CD_EMP = PDV_VD.CD_EMP
					AND V_PDV_VD_DEV_CONVENIOS.CD_FILIAL = PDV_VD.CD_FILIAL
					AND V_PDV_VD_DEV_CONVENIOS.CD_VD = PDV_VD.CD_VD
					AND V_PDV_VD_DEV_CONVENIOS.STS_NF = 0
					AND CANCEL_CONVENIO = 0
					)
		AND NOT EXISTS ( SELECT CD_EMP FROM PDV_VD_IT_VD_PBM
						WHERE CD_EMP = PDV_VD.CD_EMP
						AND CD_FILIAL = PDV_VD.CD_FILIAL
						AND CD_VD = PDV_VD.CD_VD );                  


/****************************************************************************************************************************/


	INSERT INTO #TMP_RESULT(CD_EMP, CD_FILIAL, NM_FILIAL, CD_CONV, DS_CONV, CD_CLI ,NM_FANT, MATRICULA, VLR_CONV, VLR_CONV_PBM, DT_VD, CD_CX, DEV, SEM_CAD, NR_ECF, CD_CTB_CNT, CD_CTB_CNT_RED,	
							ST_VD, TIPO_CONV, FLAG_FECHA_DIARIO ,FLAG_EXIGI_EMI_NA_VENDA,FLAG_REQUER_REQUISICAO, OBRIG_RECEIT, CD_VD, NR_AUTORIZACAO, CONSULTA)
	SELECT DISTINCT
		   PDV_VD.CD_EMP
		 , PDV_VD.CD_FILIAL
		 , PRC_FILIAL.NM_FANT AS NM_FILIAL
		 , RC_CLI_CONV.CD_CONV
		 , RC_CLI_CONV.DS_CONV
		 , RC_CLI.CD_CLI
		 , RC_CLI.NM_FANT
		 , RC_CLI_CONV_RC_CLI.MATRICULA
		 , PDV_VD.VLR_CONV
		 , ISNULL(PDV_VD.VLR_CONV_PBM, 0) AS VLR_CONV_PBM
		 , PDV_VD.DT_VD
		 , PDV_VD.CD_CX
		 , 1 AS DEV
		 , -1 AS SEM_CAD
		 , PDV_VD.NR_ECF
		 , RC_CLI_CONV.CD_CTB_CNT
		 , RC_CLI_CONV.CD_CTB_CNT_RED
		 , PDV_VD.ST_VD
		 , 0 AS TIPO_CONV
		 , ISNULL(RC_CLI_CONV.FLAG_FECHA_DIARIO, 0) AS FLAG_FECHA_DIARIO
		 , ISNULL(RC_CLI_CONV.FLAG_EXIGI_EMI_NA_VENDA, 0) AS FLAG_EXIGI_EMI_NA_VENDA
		 , ISNULL(RC_CLI_CONV.FLAG_REQUER_REQUISICAO, 0) AS FLAG_REQUER_REQUISICAO
		 , ISNULL(RC_CLI_CONV.OBRIG_RECEIT, 0) AS OBRIG_RECEIT
		 , PDV_VD.CD_VD
		 , PDV_VD.NR_AUTORIZACAO
		 , 5 AS CONSULTA
	FROM
		 dbo.PDV_VD
		 INNER JOIN dbo.RC_CLI ON PDV_VD.CD_EMP = RC_CLI.CD_EMP
								  AND PDV_VD.CD_CLI = RC_CLI.CD_CLI
		 INNER JOIN dbo.RC_CLI_CONV_RC_CLI ON PDV_VD.CD_EMP = RC_CLI_CONV_RC_CLI.CD_EMP
											  AND PDV_VD.CD_CLI = RC_CLI_CONV_RC_CLI.CD_CLI
											  AND PDV_VD.CD_CONV = RC_CLI_CONV_RC_CLI.CD_CONV
		 INNER JOIN dbo.RC_CLI_CONV ON PDV_VD.CD_EMP = RC_CLI_CONV.CD_EMP
									   AND PDV_VD.CD_CONV = RC_CLI_CONV.CD_CONV
		 INNER JOIN dbo.PRC_FILIAL ON PDV_VD.CD_EMP = PRC_FILIAL.CD_EMP
									  AND PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL
	WHERE 
		PDV_VD.CD_EMP = @CD_EMP
		AND PDV_VD.CD_FILIAL = @CD_FILIAL 
		AND PDV_VD.DT_VD BETWEEN @DT_INT AND @DT_FIM
		AND RC_CLI.ESP_CLI <> 3
		AND PDV_VD.ST_VD = 0
		AND EXISTS( SELECT CD_EMP FROM
						dbo.V_PDV_VD_DEV_CONVENIOS
				WHERE CD_EMP = PDV_VD.CD_EMP
						AND CD_FILIAL = PDV_VD.CD_FILIAL
						AND CD_VD = PDV_VD.CD_VD
						AND STS_NF = 0
						AND CANCEL_CONVENIO NOT IN(0, 1))
		AND NOT EXISTS( SELECT CD_EMP FROM
								PDV_VD_IT_VD_PBM
						WHERE CD_EMP = PDV_VD.CD_EMP
								AND CD_FILIAL = PDV_VD.CD_FILIAL
								AND CD_VD = PDV_VD.CD_VD
						);          


/****************************************************************************************************************************/
--Venda para conveniado que possui vinculo com PBM

	INSERT INTO #TMP_RESULT(CD_EMP, CD_FILIAL, NM_FILIAL, CD_CONV, DS_CONV, CD_CLI ,NM_FANT, MATRICULA, VLR_CONV, VLR_CONV_PBM, DT_VD, CD_CX, DEV, SEM_CAD, NR_ECF, CD_CTB_CNT, CD_CTB_CNT_RED,	
							ST_VD, TIPO_CONV, FLAG_FECHA_DIARIO ,FLAG_EXIGI_EMI_NA_VENDA,FLAG_REQUER_REQUISICAO, OBRIG_RECEIT, CD_VD, NR_AUTORIZACAO, CONSULTA)
	SELECT DISTINCT
		   PDV_VD.CD_EMP
		 , PDV_VD.CD_FILIAL
		 , PRC_FILIAL.NM_FANT AS NM_FILIAL
		 , RC_CLI_CONV.CD_CONV
		 , RC_CLI_CONV.DS_CONV
		 , RC_CLI.CD_CLI
		 , RC_CLI.NM_FANT
		 , RC_CLI_CONV_RC_CLI.MATRICULA
		 , PDV_VD.VLR_CONV
		 , ISNULL(PDV_VD.VLR_CONV_PBM, 0) AS VLR_CONV_PBM
		 , PDV_VD.DT_VD
		 , PDV_VD.CD_CX
		 , 1 AS DEV
		 , -1 AS SEM_CAD
		 , PDV_VD.NR_ECF
		 , RC_CLI_CONV.CD_CTB_CNT
		 , RC_CLI_CONV.CD_CTB_CNT_RED
		 , PDV_VD.ST_VD
		 , 0 AS TIPO_CONV
		 , ISNULL(RC_CLI_CONV.FLAG_FECHA_DIARIO, 0) AS FLAG_FECHA_DIARIO
		 , ISNULL(RC_CLI_CONV.FLAG_EXIGI_EMI_NA_VENDA, 0) AS FLAG_EXIGI_EMI_NA_VENDA
		 , ISNULL(RC_CLI_CONV.FLAG_REQUER_REQUISICAO, 0) AS FLAG_REQUER_REQUISICAO
		 , ISNULL(RC_CLI_CONV.OBRIG_RECEIT, 0) AS OBRIG_RECEIT
		 , PDV_VD.CD_VD
		 , PDV_VD.NR_AUTORIZACAO
		 , 6 AS CONSULTA
	FROM
		 dbo.PDV_VD
		 INNER JOIN dbo.RC_CLI ON PDV_VD.CD_EMP = RC_CLI.CD_EMP
								  AND PDV_VD.CD_CLI = RC_CLI.CD_CLI
		 INNER JOIN dbo.RC_CLI_CONV_RC_CLI ON PDV_VD.CD_EMP = RC_CLI_CONV_RC_CLI.CD_EMP
											  AND PDV_VD.CD_CLI = RC_CLI_CONV_RC_CLI.CD_CLI
											  AND PDV_VD.CD_CONV = RC_CLI_CONV_RC_CLI.CD_CONV
		 INNER JOIN dbo.RC_CLI_CONV ON PDV_VD.CD_EMP = RC_CLI_CONV.CD_EMP
									   AND PDV_VD.CD_CONV = RC_CLI_CONV.CD_CONV
		 INNER JOIN dbo.PRC_FILIAL ON PDV_VD.CD_EMP = PRC_FILIAL.CD_EMP
									  AND PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL
	WHERE
		PDV_VD.CD_EMP = @CD_EMP
		AND PDV_VD.CD_FILIAL = @CD_FILIAL 
		AND PDV_VD.DT_VD BETWEEN @DT_INT AND @DT_FIM
	 
		AND RC_CLI.ESP_CLI <> 3
		AND PDV_VD.ST_VD = 0
		AND EXISTS( SELECT CD_EMP FROM
					dbo.V_PDV_VD_DEV_CONVENIOS
					WHERE CD_EMP = PDV_VD.CD_EMP
					AND CD_FILIAL = PDV_VD.CD_FILIAL
					AND CD_VD = PDV_VD.CD_VD
					AND STS_NF = 0
					AND CANCEL_CONVENIO <> 1
					)
		AND EXISTS( SELECT CD_EMP
						FROM
						PDV_VD_IT_VD_PBM
						WHERE CD_EMP = PDV_VD.CD_EMP
						AND CD_FILIAL = PDV_VD.CD_FILIAL
						AND CD_VD = PDV_VD.CD_VD
						);

	
/****************************************************************************************************************************/

	INSERT INTO #TMP_RESULT(CD_EMP, CD_FILIAL, NM_FILIAL, CD_CONV, DS_CONV, CD_CLI ,NM_FANT, MATRICULA, VLR_CONV, VLR_CONV_PBM, DT_VD, CD_CX, DEV, SEM_CAD, NR_ECF, CD_CTB_CNT, CD_CTB_CNT_RED,	
							ST_VD, TIPO_CONV, FLAG_FECHA_DIARIO ,FLAG_EXIGI_EMI_NA_VENDA,FLAG_REQUER_REQUISICAO, OBRIG_RECEIT, CD_VD, NR_AUTORIZACAO, CONSULTA)

	SELECT DISTINCT
		   PDV_VD.CD_EMP
		 , PDV_VD.CD_FILIAL
		 , PRC_FILIAL.NM_FANT AS NM_FILIAL
		 , RC_CLI_CONV.CD_CONV
		 , RC_CLI_CONV.DS_CONV
		 , RC_CLI.CD_CLI
		 , RC_CLI.NM_FANT
		 , RC_CLI_CONV_RC_CLI.MATRICULA
		 , PDV_VD.VLR_CONV
		 , ISNULL(PDV_VD.VLR_CONV_PBM, 0) AS VLR_CONV_PBM
		 , PDV_VD.DT_VD
		 , PDV_VD.CD_CX
		 , 1 AS DEV
		 , 1 AS SEM_CAD
		 , PDV_VD.NR_ECF
		 , RC_CLI_CONV.CD_CTB_CNT
		 , RC_CLI_CONV.CD_CTB_CNT_RED
		 , PDV_VD.ST_VD
		 , 1 AS TIPO_CONV
		 , ISNULL(RC_CLI_CONV.FLAG_FECHA_DIARIO, 0) AS FLAG_FECHA_DIARIO
		 , ISNULL(RC_CLI_CONV.FLAG_EXIGI_EMI_NA_VENDA, 0) AS FLAG_EXIGI_EMI_NA_VENDA
		 , ISNULL(RC_CLI_CONV.FLAG_REQUER_REQUISICAO, 0) AS FLAG_REQUER_REQUISICAO
		 , ISNULL(RC_CLI_CONV.OBRIG_RECEIT, 0) AS OBRIG_RECEIT
		 , PDV_VD.CD_VD
		 , PDV_VD.NR_AUTORIZACAO
		 , 7 AS CONSULTA
	FROM
		 dbo.PDV_VD
		 INNER JOIN PDV_VD_IT_VD_PBM ON PDV_VD_IT_VD_PBM.CD_EMP = PDV_VD.CD_EMP
										AND PDV_VD_IT_VD_PBM.CD_FILIAL = PDV_VD.CD_FILIAL
										AND PDV_VD_IT_VD_PBM.CD_VD = PDV_VD.CD_VD
		 INNER JOIN dbo.RC_CLI AS RC_CLI ON PDV_VD_IT_VD_PBM.CD_EMP = RC_CLI.CD_EMP
											AND PDV_VD_IT_VD_PBM.CD_CLI_PBM = RC_CLI.CD_CLI
		 LEFT OUTER JOIN dbo.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
		 LEFT OUTER JOIN dbo.RC_CLI_CONV AS RC_CLI_CONV ON RC_CLI_CONV_RC_CLI.CD_EMP = RC_CLI_CONV.CD_EMP
														   AND RC_CLI_CONV_RC_CLI.CD_CONV = RC_CLI_CONV.CD_CONV
		 INNER JOIN dbo.PRC_FILIAL AS PRC_FILIAL ON PDV_VD_IT_VD_PBM.CD_EMP = PRC_FILIAL.CD_EMP
													AND PDV_VD_IT_VD_PBM.CD_FILIAL = PRC_FILIAL.CD_FILIAL
	WHERE 
		PDV_VD.CD_EMP = @CD_EMP
		AND PDV_VD.CD_FILIAL = @CD_FILIAL 
		AND PDV_VD.DT_VD BETWEEN @DT_INT AND @DT_FIM
		AND RC_CLI.ESP_CLI = 3
		AND PDV_VD.ST_VD = 0
		AND EXISTS( SELECT CD_EMP FROM dbo.V_PDV_VD_DEV_CONVENIOS
					WHERE CD_EMP = PDV_VD_IT_VD_PBM.CD_EMP
					AND CD_FILIAL = PDV_VD_IT_VD_PBM.CD_FILIAL
					AND CD_VD = PDV_VD_IT_VD_PBM.CD_VD
					AND STS_NF = 0
					AND CANCEL_CONVENIO <> 1);       

						
/****************************************************************************************************************************/

	INSERT INTO #TMP_RESULT(CD_EMP, CD_FILIAL, NM_FILIAL, CD_CONV, DS_CONV, CD_CLI ,NM_FANT, MATRICULA, VLR_CONV, VLR_CONV_PBM, DT_VD, CD_CX, DEV, SEM_CAD, NR_ECF, CD_CTB_CNT, CD_CTB_CNT_RED,	
							ST_VD, TIPO_CONV, FLAG_FECHA_DIARIO ,FLAG_EXIGI_EMI_NA_VENDA,FLAG_REQUER_REQUISICAO, OBRIG_RECEIT, CD_VD, NR_AUTORIZACAO, CONSULTA)

	SELECT DISTINCT
		   PDV_VD.CD_EMP
		 , PDV_VD.CD_FILIAL
		 , PRC_FILIAL.NM_FANT AS NM_FILIAL
		 , RC_CLI_CONV.CD_CONV
		 , RC_CLI_CONV.DS_CONV
		 , RC_CLI.CD_CLI
		 , RC_CLI.NM_FANT
		 , '' AS MATRICULA
		 , PDV_VD.VLR_CONV
		 , ISNULL(PDV_VD.VLR_CONV_PBM, 0) AS VLR_CONV_PBM
		 , PDV_VD.DT_VD
		 , PDV_VD.CD_CX
		 , 0 AS DEV
		 , -1 AS SEM_CAD
		 , PDV_VD.NR_ECF
		 , RC_CLI_CONV.CD_CTB_CNT
		 , RC_CLI_CONV.CD_CTB_CNT_RED
		 , PDV_VD.ST_VD
		 , 1 AS TIPO_CONV
		 , ISNULL(RC_CLI_CONV.FLAG_FECHA_DIARIO, 0) AS FLAG_FECHA_DIARIO
		 , ISNULL(RC_CLI_CONV.FLAG_EXIGI_EMI_NA_VENDA, 0) AS FLAG_EXIGI_EMI_NA_VENDA
		 , ISNULL(RC_CLI_CONV.FLAG_REQUER_REQUISICAO, 0) AS FLAG_REQUER_REQUISICAO
		 , ISNULL(RC_CLI_CONV.OBRIG_RECEIT, 0) AS OBRIG_RECEIT
		 , PDV_VD.CD_VD
		 , PDV_VD.NR_AUTORIZACAO
		 , 8 AS CONSULTA
	FROM
		 dbo.PDV_VD
		 INNER JOIN PDV_VD_IT_VD_PBM ON PDV_VD_IT_VD_PBM.CD_EMP = PDV_VD.CD_EMP
										AND PDV_VD_IT_VD_PBM.CD_FILIAL = PDV_VD.CD_FILIAL
										AND PDV_VD_IT_VD_PBM.CD_VD = PDV_VD.CD_VD
		 INNER JOIN dbo.RC_CLI ON PDV_VD_IT_VD_PBM.CD_EMP = RC_CLI.CD_EMP
								  AND PDV_VD_IT_VD_PBM.CD_CLI_PBM = RC_CLI.CD_CLI
		 LEFT OUTER JOIN dbo.RC_CLI_CONV_VINC_RC_CLI ON RC_CLI.CD_EMP = RC_CLI_CONV_VINC_RC_CLI.CD_EMP
														AND RC_CLI.CD_CLI = RC_CLI_CONV_VINC_RC_CLI.CD_CLI
		 LEFT OUTER JOIN dbo.RC_CLI_CONV ON RC_CLI_CONV_VINC_RC_CLI.CD_EMP = RC_CLI_CONV.CD_EMP
											AND RC_CLI_CONV_VINC_RC_CLI.CD_CONV = RC_CLI_CONV.CD_CONV
		 INNER JOIN dbo.PRC_FILIAL AS PRC_FILIAL ON PDV_VD_IT_VD_PBM.CD_EMP = PRC_FILIAL.CD_EMP
													AND PDV_VD_IT_VD_PBM.CD_FILIAL = PRC_FILIAL.CD_FILIAL
	WHERE 
		PDV_VD.CD_EMP = @CD_EMP
		AND PDV_VD.CD_FILIAL = @CD_FILIAL 
		AND PDV_VD.DT_VD BETWEEN @DT_INT AND @DT_FIM
		AND RC_CLI.ESP_CLI = 3
		AND PDV_VD.ST_VD = 0
		AND PDV_VD_IT_VD_PBM.STS_VD_PBM = 0
		AND NOT EXISTS( SELECT CD_EMP FROM dbo.V_PDV_VD_DEV_CONVENIOS
							WHERE CD_EMP = PDV_VD_IT_VD_PBM.CD_EMP
							AND CD_FILIAL = PDV_VD_IT_VD_PBM.CD_FILIAL
							AND CD_VD = PDV_VD_IT_VD_PBM.CD_VD 
									--AND DT_ENT = PDV_VD.DT_VD 
							AND STS_NF = 0)
		AND NOT EXISTS( SELECT CD_EMP FROM
							dbo.PDV_VD_RC_VD_CONV
							WHERE PDV_VD_IT_VD_PBM.CD_EMP = PDV_VD_RC_VD_CONV.CD_EMP
							AND PDV_VD_IT_VD_PBM.CD_FILIAL = PDV_VD_RC_VD_CONV.CD_FILIAL
							AND PDV_VD_IT_VD_PBM.CD_VD = PDV_VD_RC_VD_CONV.CD_VD
							);

/****************************************************************************************************************************/


	INSERT INTO #TMP_RESULT(CD_EMP, CD_FILIAL, NM_FILIAL, CD_CONV, DS_CONV, CD_CLI ,NM_FANT, MATRICULA, VLR_CONV, VLR_CONV_PBM, DT_VD, CD_CX, DEV, SEM_CAD, NR_ECF, CD_CTB_CNT, CD_CTB_CNT_RED,	
							ST_VD, TIPO_CONV, FLAG_FECHA_DIARIO ,FLAG_EXIGI_EMI_NA_VENDA,FLAG_REQUER_REQUISICAO, OBRIG_RECEIT, CD_VD, NR_AUTORIZACAO, CONSULTA)
	SELECT DISTINCT
		   PDV_VD.CD_EMP
		 , PDV_VD.CD_FILIAL
		 , PRC_FILIAL.NM_FANT AS NM_FILIAL
		 , ISNULL(RC_CLI_CONV.CD_CONV, 0) AS CD_CONV
		 , ISNULL(RC_CLI_CONV.DS_CONV, 'SEM CLASSIFICACAO') AS DS_CONV
		 , RC_CLI.CD_CLI
		 , RC_CLI.NM_FANT
		 , RC_CLI_CONV_RC_CLI.MATRICULA
		 , PDV_VD.VLR_CONV
		 , ISNULL(PDV_VD.VLR_CONV_PBM, 0) AS VLR_CONV_PBM
		 , PDV_VD.DT_VD
		 , PDV_VD.CD_CX
		 , 1 AS DEV
		 , -2 AS SEM_CAD
		 , PDV_VD.NR_ECF
		 , RC_CLI_CONV.CD_CTB_CNT
		 , RC_CLI_CONV.CD_CTB_CNT_RED
		 , PDV_VD.ST_VD
		 , 0 AS TIPO_CONV
		 , ISNULL(RC_CLI_CONV.FLAG_FECHA_DIARIO, 0) AS FLAG_FECHA_DIARIO
		 , ISNULL(RC_CLI_CONV.FLAG_EXIGI_EMI_NA_VENDA, 0) AS FLAG_EXIGI_EMI_NA_VENDA
		 , ISNULL(RC_CLI_CONV.FLAG_REQUER_REQUISICAO, 0) AS FLAG_REQUER_REQUISICAO
		 , ISNULL(RC_CLI_CONV.OBRIG_RECEIT, 0) AS OBRIG_RECEIT
		 , PDV_VD.CD_VD
		 , PDV_VD.NR_AUTORIZACAO
		 , 9 AS CONSULTA
	FROM
		 dbo.PDV_VD
		 INNER JOIN dbo.RC_CLI ON PDV_VD.CD_EMP = RC_CLI.CD_EMP
								  AND PDV_VD.CD_CLI = RC_CLI.CD_CLI
		 INNER JOIN dbo.PRC_FILIAL ON PDV_VD.CD_EMP = PRC_FILIAL.CD_EMP
									  AND PDV_VD.CD_FILIAL = PRC_FILIAL.CD_FILIAL
		 LEFT OUTER JOIN dbo.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
		 LEFT OUTER JOIN dbo.RC_CLI_CONV ON RC_CLI_CONV_RC_CLI.CD_EMP = RC_CLI_CONV.CD_EMP
											AND RC_CLI_CONV_RC_CLI.CD_CONV = RC_CLI_CONV.CD_CONV
	WHERE 
		PDV_VD.CD_EMP = @CD_EMP
		AND PDV_VD.CD_FILIAL = @CD_FILIAL 
		AND PDV_VD.DT_VD BETWEEN @DT_INT AND @DT_FIM
		AND RC_CLI.ESP_CLI <> 3
		AND PDV_VD.ST_VD = 0
		AND PDV_VD.VLR_CONV > 0
		AND NOT EXISTS( SELECT CD_EMP FROM dbo.V_PDV_VD_DEV_CONVENIOS
						WHERE CD_EMP = PDV_VD.CD_EMP
						AND CD_FILIAL = PDV_VD.CD_FILIAL
						AND CD_VD = PDV_VD.CD_VD
						AND STS_NF = 0)
		AND NOT EXISTS( SELECT CD_EMP
							FROM
							dbo.PDV_VD_RC_VD_CONV
							WHERE PDV_VD.CD_EMP = CD_EMP
							AND PDV_VD.CD_FILIAL = CD_FILIAL
							AND PDV_VD.CD_VD = CD_VD)
		AND NOT EXISTS ( SELECT CD_EMP FROM PDV_VD_IT_VD_PBM
						WHERE CD_EMP = PDV_VD.CD_EMP
						AND CD_FILIAL = PDV_VD.CD_FILIAL
						AND CD_VD = PDV_VD.CD_VD);

/****************************************************************************************************************************/
	IF @TP_RELATORIO = 1 --GeraRelatorioConvenioAnalitico
	BEGIN 
		SELECT 
			T.CD_FILIAL
			, T.NM_FILIAL
			, T.DS_CONV
			, T.CD_CLI
			, T.NR_ECF
			, T.CD_CX
			, T.NM_FANT
			, T.MATRICULA
			, SUM(VLR_CONV) + SUM(ISNULL(VLR_CONV_PBM, 0)) AS VLR_CONV
			, T.DT_VD
			, T.DEV
			, T.CD_CX
			, CASE WHEN T.DEV <> 0 THEN 
					CASE WHEN T.SEM_CAD = -1 THEN 'VENDA COM DEVOLUÇÃO EFETUADA SEM CONTROLE - COM VINCULO RECEBTO. CONVÊNIO'
						WHEN T.SEM_CAD = -2 THEN 'VENDA PARA CONVÊNIO SEM VINCULO COM O RECEBIMENTO DE CONVÊNIO'
						ELSE 'VENDA COM DEVOLUÇÃO EFETUADA'
					END
					WHEN T.SEM_CAD = 1 THEN 'VENDA PARA CONVÊNIO REALIZADA MANUALMENTE'
					WHEN T.TIPO_CONV = 1 THEN 'VENDA PARA CONVÊNIO PBM'
					WHEN T.TIPO_CONV = 2 THEN 'NÃO IDENTIFICADO'
					ELSE 'VENDA CONVÊNIO.  OBS.:  AS DEVOLUÇÕES ESTÃO EXCLUIDAS DESTA RELAÇÃO'
				END STS_VD 
			, CASE WHEN(T.DEV <> 0) AND (T.SEM_CAD <> -1) AND (T.SEM_CAD <> -2) THEN 'VENDAS DEVOLVIDAS'
				ELSE 'VENDAS VIGENTES' END SITUACAO 
			, CASE WHEN ISNULL(T.FLAG_EXIGI_EMI_NA_VENDA, 0) = 0 THEN '' ELSE 'X' END FLAG_REQUER_NOTA_FISCAL 
			, CASE WHEN T.FLAG_REQUER_REQUISICAO = 0 THEN '' ELSE 'X' END FLAG_REQUER_REQUISICAO
			, CASE WHEN T.OBRIG_RECEIT = 0 THEN '' ELSE 'X' END  OBRIG_RECEIT
		FROM #TMP_RESULT T
		WHERE
			CONSULTA <>3 
		GROUP BY 
				T.CD_FILIAL
			, T.NM_FILIAL
			, T.DS_CONV
			, T.CD_CLI
			, T.NR_ECF
			, T.CD_CX
			, T.NM_FANT
			, T.MATRICULA
			, T.DT_VD
			, T.DEV
			, T.CD_CX
			, T.SEM_CAD
			, T.TIPO_CONV
			, T.FLAG_EXIGI_EMI_NA_VENDA
			, T.FLAG_REQUER_REQUISICAO
			, T.OBRIG_RECEIT
			, T.DS_CONV
			, T.NM_FANT
		ORDER BY
			  SITUACAO DESC
			, STS_VD DESC
			, T.DS_CONV
			, T.NM_FANT;
	END

	IF @TP_RELATORIO = 2 --GeraRelatorioConvenioSintetico
	BEGIN 
		SELECT
			   T.CD_FILIAL
			 , T.NM_FILIAL
			 , T.DS_CONV
			 , SUM(VLR_CONV + ISNULL(VLR_CONV_PBM, 0)) AS VLR_CONV
			 , T.DT_VD
			 , CASE
				   WHEN T.DEV <> 0 THEN CASE
										  WHEN T.SEM_CAD = -1 THEN 'VENDA COM DEVOLUÇÃO EFETUADA SEM CONTROLE'
										  WHEN T.SEM_CAD = -2 THEN 'VENDA PARA CONVÊNIO SEM VINCULO COM O RECEBIMENTO DE CONVÊNIO'
										  ELSE 'VENDA COM DEVOLUÇÃO EFETUADA'
									  END
				   WHEN T.SEM_CAD = 1 THEN 'VENDA SEM CADASTRAMENTO'
				   WHEN T.TIPO_CONV = 2 THEN 'NÃO IDENTIFICADO'
				   ELSE 'VENDA CONVÊNIO OBS.: AS DEVOLUÇÕES ESTÃO EXCLUIDAS DESTA RELAÇÃO'
			   END STS_VD
		FROM
			 #TMP_RESULT T
		GROUP BY
				 T.CD_FILIAL
			   , T.NM_FILIAL
			   , T.DS_CONV
			   , T.DT_VD
			   , T.DEV
			   , T.SEM_CAD
			   , T.TIPO_CONV
		ORDER BY
				 STS_VD DESC
			   , T.DS_CONV;
	END

	IF @TP_RELATORIO = 3 --GerarFechamentoVendasConvenio
	BEGIN 
		SELECT
			   T.CD_CONV
			 , T.DS_CONV
			 , SUM(VLR_CONV) AS VLR_CONV
		FROM
			 #TMP_RESULT T
			 LEFT OUTER JOIN RC_VD_CONV_FECHA_LOTE ON T.CD_EMP = RC_VD_CONV_FECHA_LOTE.CD_EMP
													  AND T.CD_FILIAL = RC_VD_CONV_FECHA_LOTE.CD_FILIAL
													  AND T.CD_CONV = RC_VD_CONV_FECHA_LOTE.CD_CONV
													  AND T.DT_VD = RC_VD_CONV_FECHA_LOTE.DT_MOV
		WHERE(T.TIPO_CONV = 0
			  OR T.FLAG_FECHA_DIARIO = 1)
			 AND T.DEV = 0
		GROUP BY
				 T.CD_CONV
			   , T.DS_CONV
		HAVING SUM(VLR_CONV) > 0
		ORDER BY
				 T.DS_CONV;
	END