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

--p_plug_Farma_tipoRegistro_oito 3,'20190319'
create procedure p_plug_Farma_tipoRegistro_oito (@filial int, @data  date,  @cd_emp int=1)
as

/*
--Esse SQL traz as informações de lotes de produtos que apareceram no movimento do dia
*/  
  
 SELECT
       8 AS tipoRegistro 
	 , COALESCE((SELECT TOP 1 cd_barra FROM est_prod_cd_barra WHERE ean_caixa_fechada = 0 
											AND EAN_VALIDO=1
					AND		cd_emp  = it.cd_emp 
						AND cd_prod = it.cd_prod ORDER BY cd_barra), case when it.tp_prod=10 then '88888888888888' else '99999999999999' end ) AS codigoBarrasProduto

     , COALESCE(e.ean02, '') AS codigoBarrasAuxiliar1
     , COALESCE(e.ean03, '') AS codigoBarrasAuxiliar2
     , COALESCE(e.ean04, '') AS codigoBarrasAuxiliar3
     , COALESCE(e.ean05, '') AS codigoBarrasAuxiliar4
     , LOTE.nr_lote AS nomeLote
     , SNGPC.qt_lote AS quantidade
     , LOTE.validade AS vencimentoLote
     , LOTE.dt_ult_alt AS dataAlteracao
FROM
     pdv_vd v
     INNER JOIN pdv_vd_it IT ON IT.cd_emp = v.cd_emp
                                AND IT.cd_filial = v.cd_filial
                                AND IT.cd_vd = v.cd_vd
                                AND IT.dt_vd = v.dt_vd

	left join V_EST_PROD_EAN_PLUGFARMA  e on -- ESTÁ PUXANDO UMA NOVA VISÃO COM EAN_VALIDO=1
				it.cd_emp  = e.cd_emp 
            AND it.cd_prod = e.cd_prod

     INNER JOIN pdv_vd_it_rec REC ON IT.cd_emp = REC.cd_emp
                                     AND IT.cd_filial = REC.cd_filial
                                     AND IT.cd_vd = REC.cd_vd
                                     AND IT.cd_it = REC.cd_it
     INNER JOIN pdv_vd_it_est_sngpc_saida ITSNGPC ON ITSNGPC.cd_emp = REC.cd_emp
                                                     AND ITSNGPC.cd_it = REC.cd_it
                                                     AND ITSNGPC.cd_vd = REC.cd_vd
                                                     AND ITSNGPC.cd_filial = REC.cd_filial
     INNER JOIN est_sngpc_saida SNGPC ON SNGPC.cd_sai_sngpc = ITSNGPC.cd_sai_sngpc
     INNER JOIN est_prod_est_sngpc_lote LOTE ON LOTE.cd_emp = SNGPC.cd_emp
                                                AND LOTE.cd_lote = SNGPC.cd_lote
 
WHERE 
	it.cd_emp=@cd_emp 
	and it.dt_vd = @data
	and it.cd_filial =@filial
	and v.st_vd=0
	and it.st_it=1
