IF EXISTS( SELECT 1 FROM sys.procedures WHERE name = 'p_plug_Farma_tipoRegistro_cinco' )
    BEGIN
        DROP PROCEDURE  p_plug_Farma_tipoRegistro_cinco;
    END;
GO

--p_plug_Farma_tipoRegistro_cinco 3,'20190319'

CREATE PROCEDURE p_plug_Farma_tipoRegistro_cinco (@filial INT , @data   DATE , @cd_emp INT  = 1)
AS
set nocount on;
/*
	Esse SQL tem como objetivo trazer os dados de todos os prescritores que apareceram no movimento do dia
 */

SELECT
       ROW_NUMBER() OVER(ORDER BY numeroregistroconselho ASC) AS numeroRegistro
     , *
FROM
(
    SELECT DISTINCT
           5 AS tipoRegistro
         , 1 AS tipoConselhoRegistro
         , REPLACE(MEDICO.nr_doc,'''','')  AS numeroRegistroConselho
         , MEDICO.uf_doc AS ufConselho
         , MEDICO.nm_medico AS nomePrescritor
         , COALESCE(CID.cd_ibge, 0) AS codigoIbgeCidadePrescritor
         , LOGRA.ds_cid_logra AS enderecoLogradouro
         , MEDICO.nr_end AS enderecoNumero
         , MEDICO.complemento AS enderecoComplemento
         , LOGRA.bairro AS enderecoBairro
         , MEDICO.cep AS enderecoCep
         , MEDICO.telefone AS telefoneFixo
         , '' AS telefoneCelular
         , REC.cd_medico_rec
    FROM
         pdv_vd_it_rec REC
         INNER JOIN pdv_vd VD ON VD.cd_emp = REC.cd_emp
                                 AND VD.cd_filial = REC.cd_filial
                                 AND VD.cd_vd = REC.cd_vd
		
         INNER JOIN glb_medicos MEDICO ON MEDICO.cd_emp = REC.cd_emp
                                          AND MEDICO.cd_ctr = REC.cd_medico_rec
         INNER JOIN prc_filial FILIAL ON FILIAL.cd_emp = REC.cd_emp
                                         AND FILIAL.cd_filial = REC.cd_filial
         INNER JOIN glb_cid CID ON CID.cd_cid = FILIAL.cd_cid
         LEFT JOIN glb_cid_logra LOGRA ON LOGRA.cd_cid_logra = MEDICO.cd_cid_logra
    WHERE 
		vd.cd_emp=@cd_emp
		and vd.dt_vd=@data
        and vd.st_vd=0
		AND vd.cd_filial = @filial
) AS PRESCRITORES;