IF OBJECT_ID('p_plug_Farma_tipoRegistro_oito') IS NOT NULL
	BEGIN
		DROP PROCEDURE p_plug_Farma_tipoRegistro_oito
	END
 
GO 
CREATE PROCEDURE [dbo].[p_plug_Farma_tipoRegistro_oito] (@filial int, @data  date,  @cd_emp int=1)
as
BEGIN 
 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
     , CONVERT(INT,ITSNGPC.QTDE_PROD) AS quantidade
     , LOTE.DT_VALID AS vencimentoLote
     , LOTE.DT_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 VISO 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_lote 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_prod_cpl_lote LOTE ON LOTE.cd_emp = ITSNGPC.cd_emp
                                                AND LOTE.cd_lote = ITSNGPC.cd_lote
												AND LOTE.CD_PROD = IT.CD_PROD
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;
END
