IF OBJECT_ID('p_plug_Farma_tipoRegistro_quinze') IS NOT NULL
	BEGIN
		DROP PROCEDURE p_plug_Farma_tipoRegistro_quinze
	END
 
GO 
CREATE procedure [dbo].[p_plug_Farma_tipoRegistro_quinze] (@filial int, @data  date,  @cd_emp int=1)
as
begin
	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 * qt_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_liq_vd THEN 2 ELSE 1 END AS tipoDevolucao
			  , vd.dt_vd AS dataReferenciaVenda
			  , case when vdnfce.cd_nfce IS NULL then 4 else 5 end  AS tipoNotaVenda
			  , vd.nr_ecf AS numeroNota
			  , vd.vlr_liq_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);
end