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

--p_plug_Farma_tipoRegistro_dezesete 9,'20190816'
create procedure p_plug_Farma_tipoRegistro_dezesete (@filial int, @data  date,  @cd_emp int=1)
as
set nocount on;
/*
	--Esse SQL tem como objetivo trazer as informações de cancelamento de compras
*/;

SELECT
       CANCEL.cd_nf
     , 17 AS tipoRegistro
     , ENT.dt_ent AS dataReferenciaCompra
     , 1 AS tipoNotaCompra
     , ENT.nr_nf AS numeroNota
     , ENT.vlr_nf AS valorNota
     , 1 AS tipoIdentificacaoFornecedor
     , F.cgc_cpf AS cnpjCpfFornecedor
     , SERIE.serie AS numeroSerie
     , 1 AS vinculoCompra
FROM
     est_nf_ent ENT
     INNER JOIN est_nf_serie SERIE ON ENT.cd_emp = SERIE.cd_emp
                                      AND ENT.cd_nf_serie = SERIE.cd_nf_serie
     LEFT JOIN dbo.est_nf_ent_cancel CANCEL ON CANCEL.cd_emp = ENT.cd_nf
                                               AND CANCEL.cd_filial = ENT.cd_filial
                                               AND CANCEL.cd_nf = ENT.cd_nf
     LEFT JOIN est_nf_ent_ef_nf_ctr_nf EMISSAOPROPRIA ON EMISSAOPROPRIA.cd_emp = ENT.cd_emp
                                                         AND EMISSAOPROPRIA.cd_filial = ENT.cd_filial
                                                         AND EMISSAOPROPRIA.cd_nf_ent = ENT.cd_nf
     JOIN pg_forn F ON ENT.cd_forn = F.cd_forn
WHERE EMISSAOPROPRIA.cd_nf_ent IS NOT NULL
      AND ENT.cd_emp = @cd_emp
      AND ENT.cd_filial = @filial 
      AND ENT.dt_ent = @data
      AND CANCEL.cd_nf IS NOT NULL;