select 
	pp.id,
	pp.default_code,
	pt.name,
	div.name as division,
	pt.generic_name,
	coalesce((CASE WHEN CAST(pt.default_code as int)>=5000
                THEN (select mrp 
                 from purchase_order_line 
                 where product_id = (select id from product_product where product_tmpl_id = pt.id) 
                        and state = 'purchase' 
                 order by id desc limit 1)
                ELSE mrp
        END),0)AS  MRP,
	coalesce((CASE WHEN CAST(pt.default_code as int)>=5000
                THEN (select price_unit 
                 from purchase_order_line 
                 where product_id = (select id from product_product where product_tmpl_id = pt.id) 
                        and state = 'purchase' 
                 order by id desc limit 1)
                ELSE 0 END
        ),0) as Purchase_price,
	coalesce((CASE WHEN CAST(pt.default_code as int)>=5000
                THEN (select mrp 
                 from stock_production_lot 
                 where product_id = (select id from product_product where product_tmpl_id = pt.id)  
                 order by id desc limit 1)
                ELSE 0
        END),0)AS open_mrp,
	CASE WHEN pp.active='t' then 'Yes' else 'No' end AS activestatus
from product_product as pp
left join product_template as pt on pt.id = pp.product_tmpl_id
left join division as div on div.id = pt.division
where div.name not in ('DAVAINDIA', 'PROMOTIONAL MATERIALS','N/A') AND pp.active='t'