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

--p_plug_Farma_tipoRegistro_dezoito 3,'20190319'
create procedure p_plug_Farma_tipoRegistro_dezoito (@filial int, @data  date,  @cd_emp int=1)
as

IF Object_id('tempDB..#TmpDev', 'U') IS NOT NULL
	DROP TABLE #TmpDev;

/*********************************************************************************************/

CREATE TABLE #TmpDev (tiporegistro			TINYINT
						, numeroRegistroDevolucao	INT
						, tipoDevolucao				TINYINT
						, dataReferenciaCompra		DATE	 
						, tipoNotaCompra			TINYINT
						, numeroNota				INT
						, valorNotaOriginal			MONEY
						, valorTotalDevolvido		MONEY
						, tipoIdentificacaoFornecedor	TINYINT
						, cnpjCpfFornecedor				VARCHAR(20)
						, numeroSerie					INT
						, vinculoCompra					TINYINT)
      
	/* Esse SQL tem como objetivo trazer as informações de devolução de compras */
  
	INSERT INTO #TmpDev
	SELECT
		18 AS tipoRegistro
		, ROW_NUMBER() OVER(ORDER BY ENT.cd_nf ASC) AS numeroRegistroDevolucao
		, 1 AS tipoDevolucao
		, ENT.dt_ent AS dataReferenciaCompra
		, 1 AS tipoNotaCompra
		, ENT.nr_nf AS numeroNota
		, ENT.vlr_nf AS valorNotaOriginal
		, SAI.vlr_nf AS valorTotalDevolvido
		, 1 AS tipoIdentificacaoFornecedor
		, F.cgc_cpf AS cnpjCpfFornecedor
		, SERIE.serie AS numeroSerie
		, 1 AS vinculoCompra
	FROM
		est_nf_ent ENT
		JOIN est_nf_sai_nf_ent SAIENT ON SAIENT.cd_emp = ENT.cd_emp
											AND SAIENT.cd_filial = ENT.cd_filial
											AND SAIENT.cd_nf_ent = ENT.cd_nf

		JOIN est_nf_sai SAI ON SAIENT.cd_filial = SAI.cd_filial
									AND SAIENT.cd_nf_sai = SAI.cd_nf
		join glb_mov_op o on
			sai.cd_nt_mov = o.cd_mov_op
		JOIN est_nf_serie SERIE ON 
		ENT.cd_nf_serie = SERIE.cd_nf_serie			 
		JOIN pg_forn F ON 
		ENT.cd_forn = F.cd_forn
	WHERE
			sai.cd_emp = @cd_emp
		--and (o.dest_orig = 1 OR o.dest_orig = 2)
		and o.tp_mov in (11,15,17,18,19,20,31,41,57,59,60,62)
		AND sai.cd_filial = @filial
		AND SAI.dt_emi_nf = @data;

	SELECT * FROM #TmpDev