if exists(select  1 from sys.procedures where name = 'p_plug_Farma_tipoRegistro_dezeseis')
    begin 
		drop procedure p_plug_Farma_tipoRegistro_dezeseis;
    end;
go
--p_plug_Farma_tipoRegistro_quinze 9,'20190816'
--p_plug_Farma_tipoRegistro_dezeseis 9,'20190816'
create procedure p_plug_Farma_tipoRegistro_dezeseis (@filial int, @data  date,  @cd_emp int=1)
as
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;