if exists(select  1 from sys.procedures where name = 'p_plug_Farma_tipoRegistro_dois')
    begin 
		drop procedure p_plug_Farma_tipoRegistro_dois;
    end;
go

--p_plug_Farma_tipoRegistro_dois 3,'20190319'
create procedure p_plug_Farma_tipoRegistro_dois (@filial int, @data  date, @cd_emp int=1)
as
set nocount on;

	
	if exists(select * from integra_farmarcas_eventos	
				WHERE [status]='PROCESSANDO' and data_evento < dateadd(hour, -2, getdate()))
	begin
		UPDATE integra_farmarcas_eventos SET STATUS = 'ERRO' 
			WHERE [status]='PROCESSANDO' and data_evento < dateadd(hour, -2, getdate());
	end

	Create table #tmpForn (Cd_emp int, Cd_Forn int, DestOrig Tinyint)

	Create table #tmpFornecedor (
								   tiporegistro			tinyint
								 , tipoidentificacao	tinyint
								 , cnpj					varchar(20)
								 , razaosocial			varchar(200)
								 , nomefantasia			varchar(200)
								 , codigoibgecidade		int
								 , inscricaoestadual	varchar(100)
								 , enderecologradouro	varchar(100)
								 , endereconumero		varchar(100)
								 , enderecocomplemento	varchar(100)
								 , cep					varchar(20)
								 , telefone				varchar(20)
								 , fax					varchar(20)
								 , email				varchar(200)
								 )

	/* Esse SQL deve trazer todos os fornecedores que apareceram no movimento do dia */ 
	insert into #tmpForn
	SELECT DISTINCT
		 e.cd_emp
		 , E.cd_forn 
		 , o.dest_orig
	FROM
		 est_nf_ent e
		 JOIN glb_mov_op o ON e.cd_nt_mov = o.cd_mov_op
	WHERE(o.dest_orig = 1
		  OR o.dest_orig = 2)
		 AND e.cd_emp = @cd_emp
		 AND e.dt_ent = @data
		 AND e.cd_filial = @filial;		
		    
	insert into #tmpForn
	SELECT DISTINCT
		   s.cd_emp
		 , s.cd_cli
		 , o.dest_orig
	FROM
		 est_nf_sai s
		 JOIN glb_mov_op o ON s.cd_nt_mov = o.cd_mov_op
	WHERE(o.dest_orig = 1
		  OR o.dest_orig = 2)
		 AND s.dt_emi_nf = @data
		 AND s.cd_filial = @filial;


	insert into #tmpFornecedor
	SELECT DISTINCT
		2 AS tiporegistro
		, 1 AS tipoidentificacao
		, Replace(Replace(Replace(forn.cgc_cpf, '-', ''), '.', ''), '/', '') AS cnpj
		, forn.rz_forn AS razaosocial
		, forn.nm_fant AS nomefantasia
		, forn.cd_cid AS codigoibgecidade
		, forn.insc_est AS inscricaoestadual
		, forn.end_forn AS enderecologradouro
		, forn.nr_end_forn AS endereconumero
		, forn.comple_end AS enderecocomplemento
		, forn.cep AS cep
		, forn.tel AS telefone
		, IIF(forn.fax = '(  )     -', '00000000000', forn.fax) AS fax
		, IIF(forn.email = '', 'SEM EMAIL', forn.email) AS email
	FROM
		pg_forn FORN 
		join #tmpForn t on 
		forn.cd_emp = t.Cd_emp and
		forn.cd_forn = t.cd_forn 
	where
		t.DestOrig = 1
		and forn.cd_emp=@cd_emp;

	insert into #tmpFornecedor
	SELECT DISTINCT
		2 AS tiporegistro
		, 1 AS tipoidentificacao
		, Replace(Replace(Replace(forn.cgc , '-', ''), '.', ''), '/', '') AS cnpj
		, forn.rz_filial AS razaosocial
		, forn.nm_fant	AS nomefantasia
		, forn.cd_cid	AS codigoibgecidade
		, forn.insc_est AS inscricaoestadual
		, forn.END_FILILAL  AS enderecologradouro
		, 'sn' AS endereconumero
		, forn.BAIRRO   AS enderecocomplemento
		, forn.cep AS cep
		, forn.tel AS telefone
		, IIF(forn.fax = '(  )     -', '00000000000', forn.fax) AS fax
		, IIF(forn.email = '', 'SEM EMAIL', forn.email) AS email
	FROM
		Prc_filial FORN 
		join #tmpForn t on 
		forn.cd_emp = t.Cd_emp and
		forn.cd_filial  = t.cd_forn 
	where
		t.DestOrig = 2
		and forn.cd_emp=@cd_emp;
		
	select distinct * from #tmpfornecedor 