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

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

 --declare @filial int=12, @data  date='2019-10-23',  @cd_emp int=1

 declare @Comand as varchar (max);

 set @Comand =';WITH Devo AS (SELECT CD_EMP , CD_FILIAL , CD_VD , SUM(vlr_it) VlrDev FROM
			  pdv_vd_dev_est_prod_imp dev
		 WHERE dev.cd_emp = ' +convert(varchar, @cd_emp) + ' AND dev.cd_filial = '+convert(varchar, @filial)+' AND dev.dt_devo = '''+ convert(varchar, @data,112) +''' GROUP BY CD_EMP , CD_FILIAL, CD_VD)
     SELECT 15 AS tipoRegistro, ROW_NUMBER() OVER(ORDER BY vd.cd_vd ASC) AS numeroRegistroDevolucao
          , CASE WHEN Dev.VlrDev < vd.vlr_vd THEN 2 ELSE 1 END AS tipoDevolucao
          , vd.dt_vd AS dataReferenciaVenda
          , case when vdnfce.cd_nfce IS NULL then 3 else 4 end  AS tipoNotaVenda
          , vd.nr_ecf AS numeroNota
          , vd.vlr_vd AS valorNotaOriginal
          , Dev.VlrDev AS valorTotalDevolvido
          , COALESCE(SERIE.serie, ''--'') AS serieNota
          , CX.nr_serie_imp AS serieEcf
          , 1 AS vinculoVenda
     FROM
          pdv_vd vd
          INNER JOIN pdv_cx CX ON CX.cd_emp = vd.cd_emp
                                  AND CX.cd_filial = vd.cd_filial
                                  AND CX.cd_cx = vd.cd_cx
          INNER JOIN Devo Dev ON vd.cd_emp = Dev.cd_emp
                                 AND vd.cd_filial = Dev.cd_filial
                                 AND vd.cd_vd = Dev.cd_vd
          LEFT JOIN pdv_vd_pdv_vd_nfce vdnfce ON vdnfce.cd_emp = vd.cd_emp
                                                 AND vdnfce.cd_filial = vd.cd_filial
                                                 AND vdnfce.cd_vd = vd.cd_vd
          LEFT JOIN pdv_vd_nfce nfce ON nfce.cd_nfce = vdnfce.cd_nfce
          LEFT JOIN est_nf_serie SERIE ON serie.cd_nf_serie = nfce.cd_nfce
     WHERE  vd.st_vd = 0
		   AND vd.cd_emp = ' + convert(varchar, @cd_emp) + ' AND vd.cd_filial =' +convert(varchar, @filial)+' ;'
	EXEC  (@Comand);

