Recently faced a small problem in my project. My team and I were coming with some code to verify if a person can be granted "confirmation". In our organization, every employee must complete a set of examination in order to be absorbed into the permanent cadre. Since our ERP is Oracle, we had to use PL/SQL to manipulated data to get results.
Having never used PL/SQL before, I was not privy to loops, arrays, cursors and structures. (to now I don't know if you can use these in PL/SQL). Severely handicapped with my lack of syntax, I sought to come up with a juvenile but functioning logic that would serve our purpose. As a wise man once told me: "Logic is logic, period !!!" The requirement was simple:
There were a set of examinations that were considered requisite for an employee to complete in order for him/her to be granted confirmation. There were 12 such examinations. Some examinations were not relevant for some employees based on their chosen field and level in hierarchy in the organization. What we had to do was
- Figure out what exams were relevant for the employee out of the 12 examinations
- Find out if the employee had completed all the examinations identified in step 1
The logic that I came up was actually something I used for my FYP. Initially I declared 12 variables that would hold a 1 or a 0 depending on whether the examination was relevant. Next, I counted the number of variables that had a 1 assigned to them. For these variables assigned 1, I checked if the corresponding examination was complete. If it were complete and alternate counter was continually incremented. At the end, if the two counters matched apart for the exception of (0=0) , we can safely assume that the employee has completed all of the examinations requisite for confirmation.
------------------------------------------------------------------------------------------------------------
Let me demonstrate this logic with snippets for 6 examinations (as opposed to 12 examinations)
employee_number = 123
var_requisites_counter = 0;
var_requisites_completed_counter =0;
var_1 =0;
var_2 =1;
var_3 =0;
var_4 =0;
var_5 =1;
var_6 =0;
------ The employee 123 has to complete course 2 and 5 in order to be qualified for confirmation.
if (var_1=1) then
var_requisites_counter = var_requisites_counter +1;
if (course_1='Complete') then
var_requisites_completed_counter = var_requisites_completed_counter +1;
end if;
end if;
if (var_2=1) then
var_requisites_counter = var_requisites_counter +1;
if (course_2='Complete') then
var_requisites_completed_counter = var_requisites_completed_counter +1;
end if;
end if;
if (var_3=1) then
var_requisites_counter = var_requisites_counter +1;
if (course_3='Complete') then
var_requisites_completed_counter = var_requisites_completed_counter +1;
end if;
end if;
if (var_4=1) then
var_requisites_counter = var_requisites_counter +1;
if (course_4='Complete') then
var_requisites_completed_counter = var_requisites_completed_counter +1;
end if;
end if;
if (var_5=1) then
var_requisites_counter = var_requisites_counter +1;
if (course_5='Complete') then
var_requisites_completed_counter = var_requisites_completed_counter +1;
end if;
end if;
if (var_6=1) then
var_requisites_counter = var_requisites_counter +1;
if (course_6='Complete') then
var_requisites_completed_counter = var_requisites_completed_counter +1;
end if;
end if;
----- If the course is relevant our "requisites" counter is incremented. If the course is requisite and completed the "requisites completed" counter is incremented.
if var_requisites_counter = var_requisites_completed_counter and ( var_requisites_counter and var_requisites_completed_counter > 0 ) then
return 1;
else
return 0;
end if;
------ With the exception for the 0=0 condition for the two counters, we compare the two counters. If they are equal the employee is granted confirmation. If not the employee is not granted confirmation.
Please comment on any improvements you can make this logic; I'm always eager for input. It's small, simple and juvenile, I know. But, it sure did get the job done. At EAG, (my department @ Virtusa) if it works, then that is all that matters.
All in all it was nice getting my hands dirty with some coding, as opposed to doing monotonous management stuff. Déjà vu !!!
No comments:
Post a Comment