IF OBJECT_ID('p_plug_Farma_tipoRegistro_treze') IS NOT NULL
	BEGIN
		DROP PROCEDURE p_plug_Farma_tipoRegistro_treze
	END
 
GO 
CREATE PROCEDURE [dbo].[p_plug_Farma_tipoRegistro_treze] ( @filial INT , @data   DATE , @cd_emp INT  = 1) AS
BEGIN

set nocount on;

IF Object_id('tempDB..#TmpResVendas', 'U') IS NOT NULL
	DROP TABLE #TmpResVendas;
/*********************************************************************************************/
	CREATE TABLE #TmpResVendas (tiporegistro			INT
								, numeroRegistroVenda	INT
								, datareferenciavenda		DATETIME
								, valortotalvendaliquido	MONEY
								, horareferenciavenda	DATETIME
								, numeronota			INT
								, tipopagamento			INT
								, numeroRegistroPessoa	INT
								, tiponotavenda			INT
								, serienota				VARCHAR(50)
								, serieecf				VARCHAR(50)
								, cd_vd					INT
								, uf					VARCHAR(2)
								, cod_cli				INT
								, cd_vend				int
								, delivery varchar(100)
								, ecommerce varchar(100)
								, canalEcommerce varchar(100)
								, detalheCanalEcommerce varchar(100)
								, numeroCaixa varchar(50))
	INSERT INTO  #TmpResVendas			
	EXEC p_plug_Farma_tipoRegistro_nove	@filial, @data, @cd_emp;
	
;With Base as ( SELECT
            13 AS tipoRegistro
          , Concat(IT.cd_prod, '-', REC.cd_it, '-', REC.cd_vd) AS numeroRegistroItemVenda
          , REC.cd_medico_rec AS numeroRegistroPrescritor
          , convert(varchar(30), REC.nr_receita) AS numeroReceita
          , REC.medicamento_receita_tipo AS tipoReceita
          , REC.medicamento_receita_tipo AS medicamentoUsoProlongado
          , REC.comprador_doc_uf AS ufReceita
          , LOTE.nr_lote AS loteMedicamento
          , VD.cod_cli  AS numeroRegistroPaciente
          , REC.dt_receita AS dataReceita
          , uf AS ufFilial
		  , it.cd_prod
		  , IT.cd_vd
     FROM
          pdv_vd_it_rec REC
          INNER JOIN #TmpResVendas VD ON	REC.cd_emp = @cd_emp
										AND REC.cd_filial = @filial
										AND REC.cd_vd = VD.cd_vd 
          JOIN pdv_vd_it IT ON IT.cd_emp = REC.cd_emp
                               AND IT.cd_filial = REC.cd_filial
                               AND IT.cd_vd = REC.cd_vd
                               AND IT.cd_it = REC.cd_it 
          INNER JOIN est_prod PROD ON PROD.cd_prod = IT.cd_prod          
		  INNER JOIN pdv_vd_it_lote ITSNGPC ON ITSNGPC.cd_emp = REC.cd_emp
                                                     AND ITSNGPC.cd_it = REC.cd_it
                                                     AND ITSNGPC.cd_vd = REC.cd_vd
                                                     AND ITSNGPC.cd_filial = REC.cd_filial
          INNER JOIN est_prod_cpl_lote LOTE ON LOTE.cd_emp = ITSNGPC.cd_emp
                                                AND LOTE.cd_lote = ITSNGPC.cd_lote
												AND LOTE.CD_PROD = IT.CD_PROD
     WHERE  it.cd_filial = @filial
		   AND it.cd_emp=@cd_emp
		   and it.dt_vd = @data
           AND it.st_it = 1
           AND (PROD.controle_sngpc = 1
					or PROD.antibiotico =1))
		select 
			 b.tipoRegistro	
			,b.numeroRegistroItemVenda	
			,b.numeroRegistroPrescritor	
			,b.numeroReceita	
			,b.tipoReceita	
			,b.medicamentoUsoProlongado	
			,b.ufReceita	
			,b.loteMedicamento	
			,isnull(c.cd_cli,1 ) numeroRegistroPaciente	
			,b.dataReceita	
			,b.ufFilial	
			,b.cd_prod	
			,b.cd_vd
		from Base b
			left join rc_cli c on 
			b.numeroRegistroPaciente = c.cd_cli
			and c.cd_emp =@cd_emp
END