create or replace function refresh_product_usage() returns void as  $$declare	rec record;	sub_rec record;	init_pro_id integer;	parent_product_id integer;	now_bom_id integer;	total_product_qty float;	cinsider_efficiency boolean:=true;	begin        TRUNCATE TABLE  product_usage;  	for rec in select id,bom_id,product_id,product_qty,product_efficiency from mrp_bom where bom_id is not null loop		now_bom_id:=rec.bom_id;		total_product_qty:= rec.product_qty;		if cinsider_efficiency then			total_product_qty = total_product_qty/rec.product_efficiency;		end if;		loop				for sub_rec in select product_id as parent_product_id from mrp_bom where id =now_bom_id loop				parent_product_id:=sub_rec.parent_product_id;			end loop;			if not exists(select id from mrp_bom where bom_id is not null and product_id = parent_product_id ) then --(no record)-->root bom				if exists(select id from product_usage where bom_id = now_bom_id and product_id = rec.product_id) then					update product_usage set product_qty = product_qty + total_product_qty where  bom_id = now_bom_id and product_id = rec.product_id;				else					insert into product_usage(bom_id,product_id,product_qty) values(now_bom_id, rec.product_id, total_product_qty);				end if;				exit;			else				for sub_rec in select bom_id,product_qty,product_efficiency from mrp_bom where bom_id is not null and product_id = parent_product_id limit 1 loop					now_bom_id:=sub_rec.bom_id;					total_product_qty = total_product_qty* sub_rec.product_qty;					if cinsider_efficiency then						total_product_qty = total_product_qty/sub_rec.product_efficiency;					end if;				end loop;			end if;										end loop;			end loop;end;$$ LANGUAGE plpgsql;

declare	rec record;	sub_rec record;	init_pro_id integer;	parent_product_id integer;	now_bom_id integer;	total_product_qty float;	cinsider_efficiency boolean:=true;	begin        TRUNCATE TABLE  product_usage;  	for rec in select id,bom_id,product_id,product_qty,product_efficiency from mrp_bom where bom_id is not null loop		now_bom_id:=rec.bom_id;		total_product_qty:= rec.product_qty;		if cinsider_efficiency then			total_product_qty = total_product_qty/rec.product_efficiency;		end if;		loop				for sub_rec in select product_id as parent_product_id from mrp_bom where id =now_bom_id loop				parent_product_id:=sub_rec.parent_product_id;			end loop;			if not exists(select id from mrp_bom where bom_id is not null and product_id = parent_product_id ) then --(no record)-->root bom				if exists(select id from product_usage where bom_id = now_bom_id and product_id = rec.product_id) then					update product_usage set product_qty = product_qty + total_product_qty where  bom_id = now_bom_id and product_id = rec.product_id;				else					insert into product_usage(bom_id,product_id,product_qty) values(now_bom_id, rec.product_id, total_product_qty);				end if;				exit;			else				for sub_rec in select bom_id,product_qty,product_efficiency from mrp_bom where bom_id is not null and product_id = parent_product_id limit 1 loop					now_bom_id:=sub_rec.bom_id;					total_product_qty = total_product_qty* sub_rec.product_qty;					if cinsider_efficiency then						total_product_qty = total_product_qty/sub_rec.product_efficiency;					end if;				end loop;			end if;										end loop;			end loop;end;