IF EXISTS ( SELECT * fROM INFORMATION_SCHEMA.TABLES
				WHERE
					TABLE_NAME ='V_PDV_VD_CONVENIO')
BEGIN
	DROP VIEW  [dbo].[V_PDV_VD_CONVENIO] 
END

GO

CREATE VIEW  [dbo].[V_PDV_VD_CONVENIO] 
AS

SELECT DISTINCT                      
       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
FROM         
(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,
	   CASE ISNULL(RC_VD_CONV.NR_AUTORIZACAO,'') WHEN '' THEN 0 ELSE RC_VD_CONV.NR_AUTORIZACAO END AS 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   
       dbo.PDV_VD.CD_EMP = 1
	  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)     
UNION ALL
--Convenio que tem registro PBM                      
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,
	   CASE ISNULL(RC_VD_CONV.NR_AUTORIZACAO,'') WHEN '' THEN 0 ELSE RC_VD_CONV.NR_AUTORIZACAO END AS 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      
       dbo.PDV_VD.CD_EMP =1
	  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)
UNION ALL
-- vendas com valores em convenio pbm e com vendas feitas para convenio normal
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,
	0 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 
	dbo.PDV_VD.CD_EMP =1
	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)                                          
UNION ALL
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,
	   CASE ISNULL(RC_VD_CONV.NR_AUTORIZACAO,'') WHEN '' THEN 0 ELSE RC_VD_CONV.NR_AUTORIZACAO END AS 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     
       dbo.PDV_VD.CD_EMP =1
	  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)                             
UNION ALL
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,
	   CASE ISNULL(PDV_VD.NR_AUTORIZACAO,'') WHEN '' THEN 0 ELSE PDV_VD.NR_AUTORIZACAO END AS 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     
       dbo.PDV_VD.CD_EMP =1
	  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)                      
UNION ALL
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,
	   CASE ISNULL(PDV_VD.NR_AUTORIZACAO,'') WHEN '' THEN 0 ELSE PDV_VD.NR_AUTORIZACAO END AS 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     
       dbo.PDV_VD.CD_EMP =1
	  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)                      
UNION ALL
--Venda para conveniado que possui vinculo com PBM
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,
	   CASE ISNULL(PDV_VD.NR_AUTORIZACAO,'') WHEN '' THEN 0 ELSE PDV_VD.NR_AUTORIZACAO END AS 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     
       dbo.PDV_VD.CD_EMP =1
	  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) 
UNION ALL
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,
	   CASE ISNULL(PDV_VD.NR_AUTORIZACAO,'') WHEN '' THEN 0 ELSE PDV_VD.NR_AUTORIZACAO END AS 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            
       dbo.PDV_VD.CD_EMP =1
	  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)               
UNION ALL
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,
	   CASE ISNULL(PDV_VD.NR_AUTORIZACAO,'') WHEN '' THEN 0 ELSE PDV_VD.NR_AUTORIZACAO END AS 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 = 1
	  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)



UNION ALL
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,
	   CASE ISNULL(PDV_VD.NR_AUTORIZACAO,'') WHEN '' THEN 0 ELSE PDV_VD.NR_AUTORIZACAO END AS 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    
       dbo.PDV_VD.CD_EMP =1
	  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)


) AS RS

GO


