IF OBJECT_ID('p_plug_Farma_tipoRegistro_dezeseis') IS NOT NULL
	BEGIN
		DROP PROCEDURE p_plug_Farma_tipoRegistro_dezeseis
	END
 
GO 
CREATE procedure [dbo].[p_plug_Farma_tipoRegistro_dezeseis] (@filial int, @data  date,  @cd_emp int=1)
as
begin
	set nocount on;
	/*
	--Esse SQL tem como objetivo trazer os itens das devoluções de vendas do dia
	*/
	 SELECT
		   16 AS tipoRegistro
		 , DENSE_RANK () OVER(ORDER BY vd.cd_vd ASC) AS numeroRegistroDevolucaoVenda
		 , COALESCE((SELECT TOP 1 cd_barra FROM
								 est_prod_cd_barra
							WHERE ean_caixa_fechada = 0
								  AND cd_emp = DEV.cd_emp
								  AND cd_prod = DEV.cd_prod
								  AND EAN_VALIDO= 1
						order by cd_barra), case when cpl.tp_prod = 10 then '88888888888888' else '99999999999999' end ) AS codigoBarras
		 , dev.qt_it AS quantidade
		 , dev.vlr_it AS valorLiquidoItemUnitario
		 , dev.cd_prod
	FROM
		 pdv_vd vd 
		 INNER JOIN pdv_vd_dev_est_prod_imp dev ON dev.cd_emp = vd.cd_emp
												   AND dev.cd_filial = vd.cd_filial
												   AND dev.cd_vd = vd.cd_vd

		 INNER JOIN est_prod_cpl cpl ON				   dev.cd_emp	 = cpl.cd_emp	
												   AND dev.cd_filial = cpl.cd_filial
												   AND dev.CD_PROD	 = cpl.CD_PROD
	WHERE vd.st_vd = 0
		  AND vd.cd_emp = @cd_emp
		  AND vd.cd_filial = @filial
		  AND dev.dt_devo = @data
		  AND convert(date,dev.dt_devo) <> convert(date,@data);
end