IF EXISTS (SELECT * FROM SYS.VIEWS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_EXP_VD_CONV_CABERGS]'))
	DROP VIEW [DBO].[V_EXP_VD_CONV_CABERGS]
GO

CREATE  VIEW [DBO].[V_EXP_VD_CONV_CABERGS]
-- -- SELECT * FROM  V_EXP_VD_CONV_CABERGS WHERE DATA_VENDA BETWEEN '20210701' AND '20210730'  AND CD_CONV = 2 
  
AS

SELECT * FROM  (

SELECT   IT.CD_EMP,
         IT.CD_VD                                                       ,
         RC.CD_CONV                                                     ,
         REPLACE(CONVERT(VARCHAR(10),GETDATE(),105),'-','') AS DT_ATUAL ,
         REPLACE(CONVERT(VARCHAR(5),GETDATE(),108),':','')  AS HR_ATUAL ,
         REPLACE(REPLACE(REPLACE(CGC_EMP,'/',''),'-',''),'.','') AS CNPJ,
         RZ_EMP                                                         ,
         RC.CD_FILIAL                                                   ,
         CASE
                  WHEN ISNULL(IT.CD_MEDICO_REC,0)=0
                  THEN 'N'
                  ELSE 'S'
         END                    AS PROMED                             ,
         ISNULL(CL.MATRICULA,0) AS CARTEIRA                           ,
         RC.DT_VD AS  DATA_VENDA,
         REPLACE(CONVERT(VARCHAR(10),RC.DT_VD,105),'-','') AS DT_VD   ,
         REPLACE(RC.VLR_CONV,'.','')                       AS VLR_CONV,
         (
                  CASE
                           WHEN LINHA.DS_ARV_MERC_LINHA='ETICO'
                           THEN 'E'
                           WHEN LINHA.DS_ARV_MERC_LINHA='GENERICO'
                           THEN 'G'
                           WHEN LINHA.DS_ARV_MERC_LINHA='SIMILAR'
                           THEN 'S'
                           WHEN LINHA.DS_ARV_MERC_LINHA='MANIPULADO'
                           THEN 'M'
                           ELSE 'O'
                  END) AS TP_PROD,
         IT.CD_PROD              ,
         (SELECT TOP 1 CD_BARRA
         FROM    EST_PROD_CD_BARRA BARRA
         WHERE   BARRA.CD_EMP =IT.CD_EMP
         AND     BARRA.CD_PROD=IT.CD_PROD
         ) AS CD_BARRA,
         PD.DS_PROD   ,
         IT.QT_IT     ,
		 IT.CD_IT,
         IT.TOT_IT    ,
         CASE
                  WHEN ISNULL(G.NR_DOC,0) = 0
                  THEN 0
                  ELSE G.NR_DOC
         END AS CRM       ,
         ''  AS CD_RESERVA,
         IT.NR_ECF  NR_ECF_IT
FROM     RC_VD_CONV RC
         INNER JOIN V_PDV_VD_IT IT
         ON       RC.CD_EMP   =IT.CD_EMP
         AND      RC.CD_FILIAL=IT.CD_FILIAL
         AND      RC.NR_ECF   =IT.NR_ECF
         AND      RC.DT_VD    =IT.DT_VD
         AND      RC.CD_CX    = IT.CD_CX
         INNER JOIN RC_CLI_CONV_RC_CLI CL
         ON       RC.CD_EMP =CL.CD_EMP
         AND      RC.CD_CONV=CL.CD_CONV
         AND      RC.CD_CLI = CL.CD_CLI
         INNER JOIN EST_PROD PD
         ON       PD.CD_EMP  =IT.CD_EMP
         AND      pd.CD_PROD = IT.CD_PROD
         INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV
         ON       ARV.CD_EMP  =PD.CD_EMP
         AND      Arv.CD_PROD = pd.CD_PROD
         INNER JOIN EST_ARV_MERC_LINHA LINHA
         ON       LINHA.CD_EMP            =ARV.CD_EMP
         AND      Linha.CD_ARV_MERC_LINHA = Arv.CD_ARV_MERC_LINHA
         INNER JOIN PRC_EMP
         ON       RC.CD_EMP = PRC_EMP.CD_EMP
         LEFT JOIN GLB_MEDICOS G
         ON       G.CD_EMP =IT.CD_EMP
         AND      G.Cd_Ctr = IT.CD_MEDICO_REC
WHERE    RC.ST_FECHVD_CONV=1

--RC.CD_EMP         =1
--AND      RC.DT_VD BETWEEN '08/01/2021' AND      '08/31/2021'
--AND      RC.ST_FECHVD_CONV=1
--AND      RC.CD_CONV       = 2  

) TEMP   




 