------start-----

PROCEDURE `budget_App`(IN reqid VARCHAR(50), IN empid VARCHAR(50), IN deptfk VARCHAR(50), out outp text)
BEGIN

declare vv, hh, supver, rolefk, orderrole, stafffk, cnfk, submit int;
declare role, statu varchar(50);

#get the roles for approvals
declare cur1 cursor for SELECT a.rolefk, b.role, priority, empfk FROM admin_hris.budgetapprovalsetuptbl a left join rolestbl b on a.rolefk=b.id  where a.cnamefk=(select cnamefk from depttbl where id=deptfk) and a.status='Active' order by priority asc;

#insert the first person which is the intitator
insert into budgetapprovaltbl(budgetfk, empfk,role,status,datecreated,timecreated, actiontaken, takenby) values(reqid, empid, 'Initiator', 'Completed', curdate(), DATE_FORMAT(NOW(),'%h:%i:%s %p'),'Insert', empid);

#Get the first line manager
set supver = (SELECT empsupervisorfk FROM empsupervisortbl where empfk=empid);

#insert for the supervisor
insert into budgetapprovaltbl(budgetfk, empfk,role,status,datecreated,timecreated, actiontaken, takenby) values(reqid, supver, 'Authoriser', 'Pending', curdate(), DATE_FORMAT(NOW(),'%h:%i:%s %p'),'Insert', empid);

open cur1;
set vv = 0;
set hh = (SELECT count(a.rolefk) FROM admin_hris.budgetapprovalsetuptbl a left join rolestbl b on a.rolefk=b.id  where a.cnamefk=(select cnamefk from depttbl where id=deptfk) and a.status='Active' order by priority asc);
while vv < hh do

fetch cur1 into rolefk, role, orderrole,stafffk;

insert into budgetapprovaltbl(budgetfk, empfk,role,status,datecreated,timecreated, actiontaken, takenby) values(reqid, stafffk, role, '', curdate(), DATE_FORMAT(NOW(),'%h:%i:%s %p'), 'Insert', empid);

set vv = vv + 1;
end while;


close cur1;

set submit = (select count(*) from budgetapprovaltbl where budgetfk=reqid);

if(submit > 0)then
set outp = 'Request submitted';
end if;
END

///end