select  Codigo, 
		grprod,
		Descricao, 
		Fabricante, 
		Tipo, 
		Cod_Estado,
        Flag_ImprClassif1,
		Qtd_Fisico, 
		Qtd_Solicitado, 
		Qtd_Quarentena, 
		Qtd_Disponivel,
        sum(prc_CusLiqEnt) as prc_CusLiqEnt,
        sum(Prc_Venda) as Prc_Venda,
        sum(Prc_CusMedPra) as Prc_CusMedPra,
        sum(Prc_CustoMedio) as Prc_CustoMedio,
		sum(Prc_UltEnt) as Prc_UltEnt,  
        sum(qtdAvencerNosProx180Dias) as qtdAvencerNosProx180Dias, 
        (sum(qtdAvencerNosProx180Dias)*sum(Prc_CusMedPra)) as valorAvencer180,
        sum(qtdAvencerNosProx360Dias) as qtdAvencerNosProx360Dias, 
        sum(qtdAvencerMaiorQ360Dias) as qtdAvencerMaiorQ360Dias,
        sum(TotalprcCustoMedioPra) as TotalprcCustoMedioPra,
        sum(qtdNF) as qtdNF,
        sum(vlrUnit) as vlrUnit,
        sum(ticketMedioVenda) as ticketMedioVenda,
        sum(Qtd_UnidVenda) as Qtd_UnidVenda,
        sum(vlrTotalVenda) as vlrTotalVenda,
        sum(part) as part
from (

select 	
        p.Codigo, 
		p.grprod,
		p.Descricao, 
		p.Fabricante, 
		p.Tipo,        
		p.Cod_Estado,
        case
			when p.Flag_ImprClassif1 = 'D' then 'ATIVO' 
            else 'INATIVO'
		end as Flag_ImprClassif1,
		p.Qtd_Fisico, 
		p.Qtd_Solicitado, 
		p.Qtd_Quarentena, 
		p.Qtd_Disponivel,
        ifnull(p.prc_CusLiqEnt,0) as prc_CusLiqEnt,
        ifnull(p.Prc_Venda,0) as Prc_Venda,
        ifnull(p.Prc_CusMedPra,0) as Prc_CusMedPra,
        ifnull(p.Prc_CustoMedio,0) as Prc_CustoMedio,
		ifnull(p.Prc_UltEnt,0) as Prc_UltEnt,  
        0 as qtdAvencerNosProx180Dias, 
        0 as qtdAvencerNosProx360Dias, 
        0 as qtdAvencerMaiorQ360Dias,
        (ifnull(p.Prc_CusMedPra,0) * p.Qtd_Disponivel)  as TotalprcCustoMedioPra,
        0 as qtdNF,
        0 as vlrUnit,
        0 as ticketMedioVenda,
        0 as Qtd_UnidVenda,
        (ifnull(p.Prc_Venda,0) * p.Qtd_Disponivel) as vlrTotalVenda,
        0 as part
        -- diasNoEstoque,
        -- diasParaVencer
		-- ifnull(round(sum(qtdFisicoTotal * diasNoEstoque)/sum(qtdFisicoTotal),0),0) as diasnoestoque
from produ p
where
(Flag_ImprClassif1 = 'D' or Qtd_Disponivel > 0) 

UNION ALL
select 	
        Codigo, 
		grprod,
		Descricao, 
		Fabricante, 
		Tipo, 
		Cod_Estado,
        case
			when Flag_ImprClassif1 = 'D' then 'ATIVO' 
            else 'INATIVO'
		end as Flag_ImprClassif1,
		Qtd_Fisico, 
		Qtd_Solicitado, 
		Qtd_Quarentena, 
		Qtd_Disponivel,
        ifnull(prc_CusLiqEnt,0) as prc_CusLiqEnt,
        ifnull(Prc_Venda,0) as Prc_Venda,
        ifnull(Prc_CusMedPra,0) as Prc_CusMedPra,
        ifnull(Prc_CustoMedio,0) as Prc_CustoMedio,
		ifnull(Prc_UltEnt,0) as Prc_UltEnt,  
        case
			when (diasParaVencer <= 180) then sum(qtdFisicoTotal)
            else 0
		end as qtdAvencerNosProx180Dias, 
        case
			when ((diasParaVencer > 180) and (diasParaVencer < 361)) then sum(qtdFisicoTotal)
            else 0
		end as qtdAvencerNosProx360Dias, 
        case
			when ((diasParaVencer > 360)) then sum(qtdFisicoTotal)
            else 0
		end as qtdAvencerMaiorQ360Dias,
        0 as TotalprcCustoMedioPra,
        0 as qtdNF,
        0 as vlrUnit,
        0 as ticketMedioVenda,
        0 as Qtd_UnidVenda,
        0 as vlrTotalVenda,
        0 as part
        -- diasNoEstoque,
        -- diasParaVencer
		-- ifnull(round(sum(qtdFisicoTotal * diasNoEstoque)/sum(qtdFisicoTotal),0),0) as diasnoestoque
from loteproddinamicoerp
left join produ on produ.codigo = loteproddinamicoerp.cod_produ
-- where produ.codigo = 2
group by Codigo, 
		grprod,
		Descricao, 
		Fabricante, 
		Tipo, 
		Cod_Estado,
        Flag_ImprClassif1,
		Qtd_Fisico, 
		Qtd_Solicitado, 
		Qtd_Quarentena, 
		Qtd_Disponivel,
        prc_CusLiqEnt,
        Prc_Venda,
        Prc_CusMedPra,
        Prc_CustoMedio,
		Prc_UltEnt
        
) produtos
group by Codigo, 
		grprod,
		Descricao, 
		Fabricante, 
		Tipo, 
		Cod_Estado,
        Flag_ImprClassif1,
		Qtd_Fisico, 
		Qtd_Solicitado, 
		Qtd_Quarentena, 
		Qtd_Disponivel,
        prc_CusLiqEnt,
        Prc_Venda,
        Prc_CusMedPra,
        Prc_CustoMedio,
		Prc_UltEnt
