IF EXISTS( SELECT 1 FROM sys.procedures WHERE name = 'p_plug_Farma_tipoRegistro_dezenove' )
    BEGIN
        DROP PROCEDURE p_plug_Farma_tipoRegistro_dezenove;
    END;
GO

--p_plug_Farma_tipoRegistro_dezenove 9,'20190319'
CREATE PROCEDURE p_plug_Farma_tipoRegistro_dezenove ( @filial INT , @data   DATE , @cd_emp INT  = 1 )
AS
/*
--Esse SQL tem como objetivo trazer as informações de itens das devoluções de compras
*/  
SELECT
       19 AS tipoRegistro
     , DENSE_RANK () OVER(ORDER BY ENT.cd_nf ASC) AS numeroRegistroDevolucaoCompra
	 , ROW_NUMBER() OVER(ORDER BY ENT.cd_nf ASC) AS  numeroRegistroItemDevolucaoCompra     
     , COALESCE((SELECT TOP 1 cd_barra
						FROM
							 est_prod_cd_barra
						WHERE ean_caixa_fechada = 0
							  AND ean_valido=1
							  AND cd_emp = PROD.cd_emp
							  AND cd_prod = PROD.cd_prod order by cd_barra), (Case When IT.tp_prod = 10 then '88888888888888' else
																'99999999999999' end)) AS codigoBarras
     , IT.qt_it AS quantidade
     , (IT.vlr_tot_it / IT.qt_it) AS valorLiquidoItemUnitario
     , prod.cd_prod
FROM
     est_nf_ent ENT
     JOIN est_nf_sai_nf_ent SAIENT ON SAIENT.cd_emp = ENT.cd_emp
                                      AND SAIENT.cd_filial = ENT.cd_filial
                                      AND SAIENT.cd_nf_ent = ENT.cd_nf
     JOIN est_nf_sai SAI ON SAIENT.cd_filial = SAI.cd_filial
                            AND SAIENT.cd_nf_sai = SAI.cd_nf
     INNER JOIN est_nf_sai_it IT ON IT.cd_emp = SAI.cd_emp
                                    AND IT.cd_filial = SAI.cd_filial
                                    AND IT.cd_nf = SAI.cd_nf
     INNER JOIN est_prod PROD ON PROD.cd_emp = IT.cd_emp
                                 AND PROD.cd_prod = IT.cd_prod
                                 AND PROD.cd_filial = IT.cd_filial
     JOIN glb_mov_op o ON SAI.cd_nt_mov = o.cd_mov_op
     JOIN est_nf_serie SERIE ON ENT.cd_nf_serie = SERIE.cd_nf_serie
     JOIN pg_forn F ON ENT.cd_forn = F.cd_forn
WHERE it.cd_emp = @cd_emp
		--and (o.dest_orig = 1 OR o.dest_orig = 2)
      AND o.tp_mov IN(11, 15, 17, 18, 19, 20, 31, 41, 57, 59, 60, 62)
     AND it.cd_filial = @filial
     AND it.dt_emi_nf = @data;

