Created
March 13, 2020 06:36
-
-
Save deksoke/361e16cda41768fba68358cd96043a0c to your computer and use it in GitHub Desktop.
Procedure Auto Assign Job To Employee Order by priority
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--create or replace procedure cistp.PRC_ASSIGN_WORKJOB is | |
declare | |
vn_assign_lvl number := 0; | |
vc_empid varchar2(200) := ''; | |
vn_roundno number := 0; | |
cursor c_job | |
is | |
select j.jobid | |
from jobs j | |
where j.is_assigned = false | |
order by j.priority asc; | |
r_job c_job%rowtype; | |
procedure p_assign_work(p_in_refid in varchar2, p_in_br_code in varchar2, p_in_pstdt in date, p_in_split_lvl in number) | |
is | |
vn_diff number := 0; --µÑÇá»ÃªÑèǤÃÒÇ | |
vn_limit_row number := 700; --µÑÇá»Ã¤§·Õè const ¤×Í ¨Ó¹Ç¹á¶ÇµèÍ 1 doc | |
vn_rowcount number := 0; | |
vc_next_runno varchar2(20); --µÑÇá»ÃªÑèǤÃÒÇ ÊÓËÃѺÃѺàÅ¢àÍ¡ÊÒ÷Õè¨ÐÊÃéÒ§ãËÁè ËÃ×Í àÍ¡ÊÒõÑÇ¶Ñ´ä» | |
vn_split_lvl number := 0; --µÑÇá»ÃªÑèǤÃÒÇ ¨Ó¹Ç¹àÍ¡ÊÒ÷Õè¨ÐᵡÂèÍÂÍ͡仨ҡµé¹©ºÑº | |
begin | |
select count(1) into vn_rowcount | |
from zmart_user a | |
where job_num > loop_job_num | |
and | |
; | |
if vn_rowcount <= vn_limit_row then | |
return; | |
end if; | |
vn_split_lvl := vn_split_lvl + 1; | |
p_split_data(vc_next_runno, p_in_br_code, p_in_pstdt, vn_split_lvl); | |
end; | |
begin | |
open c_job; | |
loop | |
fetch c_job into r_job; | |
exit when c_job%notfound; | |
dbms_output.put_line(r_job.jobid); | |
select empid into vc_empid | |
from zmart_user | |
where assign_round_no = vn_assign_lvl | |
and total_job > current_job | |
and rownum = 1 | |
order by order_seq | |
; | |
update zmart_user set | |
current_job = current_job + 1 | |
, assign_round_no = assign_round_no + 1 | |
where empid = vc_empid; | |
--เช็คว่ารอบนี้จ่ายงานให้ครบทุกคนหรือยัง | |
if not exists( | |
select 1 | |
from zmart_user | |
where total_job > current_job | |
and assign_round_no = vn_assign_lvl | |
) then | |
--check next round has employee have to assign job | |
--if exists. let update current vn_assign_lvl | |
--เช็คว่าในรอบต่อไปจะยังมีใครที่ยังได้งานไม่ครบหรือเปล่า | |
if exists( | |
select 1 | |
from zmart_user | |
where assign_round_no = (vn_assign_lvl + 1) | |
and total_job > current_job | |
) then | |
--ถ้ารอบต่อไปยังเหลือคนที่มีงานไม่ครบ ก็บวกตัวแปรรอบเพิ่มไปหนึ่ง | |
vn_assign_lvl := vn_assign_lvl + 1; | |
else | |
--รอบต่อไปไม่เหลือคนที่ต้องจ่ายงานให้แล้ว ก็ออกจากลูป จบการทำงาน | |
exit; | |
end if; | |
end if; | |
end loop; | |
close c_job; | |
--commit; | |
dbms_output.put_line('Process Complete'); | |
end if; | |
exception | |
when others then | |
begin | |
--rollback; | |
dbms_output.put_line('Process Error :' || sqlcode); | |
raise; | |
end; | |
end; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment