1 PACKAGE BODY IRC_SKILLS_MATCHING_PKG AS
2 /* $Header: irsklpkg.pkb 120.0 2005/07/26 15:18:32 mbocutt noship $ */
3
4 --GLOBAL CONSTANTS gco_...
5 gco_delim varchar2(1) :=',';
6 gco_delim2 varchar2(1) :='>';
7
8 --GLOBAL TYPES gt_...
9 --Tie together the corresponding 3 related ID's
10 TYPE gt_skill_rec_type IS RECORD (
11 skill_id PER_COMPETENCES.competence_id%type,
12 max_id PER_RATING_LEVELS.rating_level_id%TYPE,
13 min_id PER_RATING_LEVELS.rating_level_id%TYPE,
14 essential PER_COMPETENCE_ELEMENTS.mandatory%TYPE );
15
16 --Type to hold the set of records tying in the sets of three related input strings
17 TYPE gt_skills_tab_type IS TABLE OF gt_skill_rec_type index by binary_integer;
18
19
20 --GLOBAL CURSORS gt_...
21 -- Cursor to get a list of skills and levels for a given set of scopes
22 CURSOR gc_skills_reqd_list (cp_org PER_COMPETENCE_ELEMENTS.enterprise_id%TYPE,
23 cp_bgp PER_COMPETENCE_ELEMENTS.organization_id%TYPE,
24 cp_job PER_COMPETENCE_ELEMENTS.position_id%TYPE,
25 cp_pos PER_COMPETENCE_ELEMENTS.position_id%TYPE,
26 cp_eff_date PER_COMPETENCE_ELEMENTS.effective_date_to%TYPE
27 ) IS
28 SELECT pc.name,
29 pc.competence_id,
30 pc.rating_scale_id,
31 MAX(minpr.STEP_VALUE) highest_min_level,
32 MIN(maxpr.STEP_VALUE) lowest_max_level,
33 pce.MANDATORY
34 FROM per_competences pc,
35 per_competence_elements pce,
36 per_rating_levels minpr,
37 per_rating_levels maxpr
38 WHERE pc.competence_id = pce.competence_id
39 AND pce.PROFICIENCY_LEVEL_ID = minpr.rating_level_id(+)
40 AND pce.HIGH_PROFICIENCY_LEVEL_ID = maxpr.rating_level_id(+)
41 AND pce.type = 'REQUIREMENT'
42 AND trunc(CP_EFF_DATE) between trunc(pce.effective_date_from) and trunc (nvl(pce.effective_date_to,CP_EFF_DATE))
43 AND ( pce.job_id = CP_JOB
44 or pce.POSITION_ID = CP_POS
45 or pce.ORGANIZATION_ID = CP_ORG
46 or pce.ENTERPRISE_ID = CP_BGP)
47 AND pc.business_group_id is null
48 GROUP BY pc.competence_id,
49 pc.name,
50 pce.MANDATORY,
51 pc.rating_scale_id;
52
53
54 CURSOR gc_rating_level_details(cp_rate_scale PER_RATING_LEVELS.rating_scale_id%TYPE,
55 cp_step_value1 PER_RATING_LEVELS.step_value%TYPE,
56 cp_step_value2 PER_RATING_LEVELS.step_value%TYPE) IS
57 SELECT rating_level_id --, step_value, name as level_name
58 FROM per_rating_levels
59 WHERE rating_scale_id = CP_RATE_SCALE
60 AND ( step_value = CP_STEP_VALUE1 or step_value = CP_STEP_VALUE2)
61 ORDER BY step_value;
62
63 CURSOR gc_comp_rating_level_details(cp_competence_id PER_RATING_LEVELS.competence_id%TYPE,
64 cp_step_value1 PER_RATING_LEVELS.step_value%TYPE,
65 cp_step_value2 PER_RATING_LEVELS.step_value%TYPE) IS
66 SELECT rating_level_id --, step_value, name as level_name
67 FROM per_rating_levels
68 WHERE competence_id = CP_COMPETENCE_ID
69 AND ( step_value = CP_STEP_VALUE1 or step_value = CP_STEP_VALUE2)
70 ORDER BY step_value;
71
72 CURSOR gc_get_vac_scopes(cp_vacancy per_all_vacancies.vacancy_id%TYPE,
73 cp_date per_all_vacancies.date_to%TYPE) IS
74 SELECT organization_id,business_group_id,position_id,job_id
75 FROM per_all_vacancies
76 WHERE vacancy_id = CP_VACANCY
77 AND CP_DATE between nvl(date_from,trunc(SYSDATE))
78 and nvl(date_to,hr_api.g_eot);
79
80 -- ----------------------------------------------------------------------------
81 -- |-----------------------< SKILLS_MATCH_PERCENT >--------------------------|
82 -- ----------------------------------------------------------------------------
83
84 --Take a list of esse[ntial] and pref[erred] skills, (and min/max low level_id for each)
85 --and return a percentage figure for how well a given person matches.
86 --if not all essential skills matched then return -1.
87 FUNCTION SKILLS_MATCH_PERCENT ( p_esse_sk_list_str VARCHAR2,
88 p_esse_sk_mins_str VARCHAR2,
89 p_esse_sk_maxs_str VARCHAR2,
90 p_pref_sk_list_str VARCHAR2,
91 p_pref_sk_mins_str VARCHAR2,
92 p_pref_sk_maxs_str VARCHAR2,
93 p_person_id number) RETURN VARCHAR2
94
95
96 IS
97
98 BEGIN
99 -- Call main program setting get_value to true, i.e. interested in getting a final numerical result
100 return skills_match( p_esse_sk_list_str,
101 p_esse_sk_mins_str,
102 p_esse_sk_maxs_str,
103 p_pref_sk_list_str,
104 p_pref_sk_mins_str,
105 p_pref_sk_maxs_str,
106 p_person_id,
107 true);
108 END SKILLS_MATCH_PERCENT;
109
110
111 -- ----------------------------------------------------------------------------
112 -- |-----------------------< SKILLS_MATCH_TEST >--------------------------|
113 -- ----------------------------------------------------------------------------
114
115 --Take a list of esse[ntial] and pref[erred] skills, (and min/max low level_id for each)
116 --and return a percentage figure for how well a given person matches.
117 --if not all essentall skills matched then return -1.
118 FUNCTION SKILLS_MATCH_TEST ( p_esse_sk_list_str VARCHAR2,
119 p_esse_sk_mins_str VARCHAR2,
120 p_esse_sk_maxs_str VARCHAR2,
121 p_person_id number) RETURN BOOLEAN
122
123
124 IS
125 l_value VARCHAR2(3);
126 BEGIN
127 -- Call main program setting get_value to false, i.e. quit as soon as we know person has got the essential skills
128 l_value := skills_match( p_esse_sk_list_str,
129 p_esse_sk_mins_str,
130 p_esse_sk_maxs_str,
131 '',
132 '',
133 '',
134 p_person_id,
135 false);
136
137 if to_number(l_value) = -1 then return false;
138 else return true;
139 end if;
140 END SKILLS_MATCH_TEST;
141
142 -- ----------------------------------------------------------------------------
143 -- |----------------------------< SKILLS_MATCH >-----------------------------|
144 -- ----------------------------------------------------------------------------
145
146 --Take a list of esse[ntial] and pref[erred] skills, (and min/max low level_id for each)
147 --and return a percentage figure for how well a given person matches.
148 --if not all essentall skills matched then return -1.
149 FUNCTION SKILLS_MATCH ( p_esse_sk_list_str VARCHAR2,
150 p_esse_sk_mins_str VARCHAR2,
151 p_esse_sk_maxs_str VARCHAR2,
152 p_pref_sk_list_str VARCHAR2,
153 p_pref_sk_mins_str VARCHAR2,
154 p_pref_sk_maxs_str VARCHAR2,
155 p_person_id NUMBER,
156 p_get_percent_flag BOOLEAN) RETURN VARCHAR2
157
158
159 IS
160
161 --Start of definition
162 --Weakly defined cursor
163 TYPE lt_dyn_query_cur_type is ref cursor;
164 --Instances
165 lc_dyn_query_cur lt_dyn_query_cur_type;
166
167 --Store for the valid numbers used to ease the latter dynamic queries
168 TYPE lt_rating_level_tab_type is table of per_rating_levels.step_value%type index by binary_integer;
169 --Instances
170 l_rating_level_tab lt_rating_level_tab_type;
171
172 --Tie together the corresponding 3 related ID's
173 TYPE lr_input_rec_type IS RECORD (
174 skill_id PER_COMPETENCES.competence_id%type,
175 max_level_num PER_RATING_LEVELS.step_value%TYPE,
176 min_level_num PER_RATING_LEVELS.step_value%TYPE );
177
178 --Type to hold the set of records tying in the sets of three related input strings
179 TYPE lt_input_tab_type IS TABLE OF lr_input_rec_type index by binary_integer;
180 --Instances
181 l_essential_tab lt_input_tab_type;
182 l_preferred_tab lt_input_tab_type;
183
184
185 --Type for temp store used to eliminate repeating skills
186 Type lt_temp_tab_type is table of number index by binary_integer;
187 l_temp_tab lt_temp_tab_type;
188
189 l_t1 lt_temp_tab_type;
190 l_t2 lt_temp_tab_type;
191 l_t3 lt_temp_tab_type;
192 l_len binary_integer := 0;
193
194 -- Reads parameters in to global variables
195 l_esse_sk_list_str varchar2(240) := p_esse_sk_list_str;
196 l_esse_sk_mins_str varchar2(240) := p_esse_sk_mins_str;
197 l_esse_sk_maxs_str varchar2(240) := p_esse_sk_maxs_str;
198 l_pref_sk_list_str varchar2(240) := p_pref_sk_list_str;
199 l_pref_sk_mins_str varchar2(240) := p_pref_sk_mins_str;
200 l_pref_sk_maxs_str varchar2(240) := p_pref_sk_maxs_str;
201 l_person_id number := p_person_id;
202
203
204 --Dynamic SQL variables (will be used 3 times)
205 l_sql_select VARCHAR2(32000) :='';
206 l_sql_from VARCHAR2(32000) :='';
207 l_sql_where VARCHAR2(32000):='';
208 l_sql_query VARCHAR2(32000):='';
209
210 --Total holders
211 l_tot_esse_skills NUMBER := 0;
212 l_tot_pref_skills NUMBER := 0;
213 l_tot_skills NUMBER := 0;
214 l_tot_esse_skills_held NUMBER := 0;
215 l_tot_pref_skills_held NUMBER := 0;
216 l_tot_skills_held NUMBER := 0;
217 l_tot_percentage_match NUMBER(3,0) := -1;
218
219 --Miscellaneous
220 l_concats VARCHAR2(1000):='';
221 l_rowcount number;
222 l_rating_level_id_temp per_rating_levels.rating_level_id%type;
223 l_step_value_temp per_rating_levels.step_value%type;
224
225 INCONSISTENT_INPUT_LENGTHS EXCEPTION;
226
227 --SUB FUNCTION, turn input comma-delimeted list in to table
228 FUNCTION string_to_table ( id_list IN VARCHAR2)
229 RETURN lt_temp_tab_type
230 IS
231 i NUMBER := 1;
232 l_value VARCHAR2(100) :='';
233 l_pos_of_ith_comma NUMBER := -1;
234 l_pos_last_comma NUMBER := 0;
235 l_temp_table lt_temp_tab_type;
236
237 BEGIN
238 <<next_comma_loop>>
239 WHILE l_pos_of_ith_comma <> 0
240 LOOP
241 l_pos_of_ith_comma := nvl(instr(id_list,',',1,i),0);
242
243 -- Take substring between commas or to end if no last comma
244 if (l_pos_of_ith_comma <> 0) then
245 l_value := SUBSTR(id_list,l_pos_last_comma +1,
246 l_pos_of_ith_comma - l_pos_last_comma -1 );
247 else l_value := SUBSTR(id_list,l_pos_last_comma +1);
248 end if;
249 if (l_value = '' or l_value is null) then l_value := '-1'; end if;
250 l_temp_table(i) := to_number(l_value);
251 l_pos_last_comma := l_pos_of_ith_comma;
252 i := i + 1;
253
254 l_pos_last_comma := l_pos_of_ith_comma;
255 END LOOP next_comma_loop;
256 RETURN l_temp_table;
257
258 RETURN l_temp_table;
259
260 END;
261
262 --SUB FUNCTION
263 --This is called twice, once for essential, once for preferred
264 --sees how many skills a person matches on given a list and a min/max level
265 FUNCTION get_number_of_skill_matches ( table_of_ids IN lt_input_tab_type)
266 RETURN NUMBER
267 IS
268 BEGIN
269 l_rowcount := 0;
270 l_sql_query :='';
271 l_sql_select :=' SELECT count(*)';
272 l_sql_from :=' FROM per_competence_elements pce';
273 l_sql_where :=' WHERE pce.person_id = :1 '
274 ||' AND trunc(sysdate) between pce.effective_date_from and nvl(pce.effective_date_to,sysdate)'
275 ||' AND ( ( ';
276 FOR l IN table_of_ids.first .. table_of_ids.last
277 LOOP
278 l_sql_where := l_sql_where||
279 ' pce.competence_id='||table_of_ids(l).skill_id;
280
281 if table_of_ids(l).min_level_num <> '-1'
282 and table_of_ids(l).max_level_num <> '-1'
283 then
284 l_sql_where := l_sql_where|| ' AND exists (select 1 from per_rating_levels where rating_level_id=pce.proficiency_level_id'||
285 ' and step_value between ' || table_of_ids(l).min_level_num || ' and ' ||
286 table_of_ids(l).max_level_num || ' ) )';
287 elsif table_of_ids(l).min_level_num <> '-1'
288 then
289 l_sql_where := l_sql_where|| ' AND exists (select 1 from per_rating_levels where rating_level_id=pce.proficiency_level_id'||
290 ' and ' || '(step_value >= ' || table_of_ids(l).min_level_num || ' ) ) )';
291 elsif table_of_ids(l).max_level_num <> '-1'
292 then
293 l_sql_where := l_sql_where|| ' AND exists (select 1 from per_rating_levels where rating_level_id=pce.proficiency_level_id'||
294 ' and ' || '(step_value <= ' || table_of_ids(l).max_level_num || ' ) ) )';
295 else
296 l_sql_where := l_sql_where|| ' )';
297 end if;
298 if l <> table_of_ids.last then l_sql_where := l_sql_where||' or ('; end if;
299 --
300 END LOOP;
301 l_sql_where := l_sql_where||' ) ';
302 l_sql_query := l_sql_select||l_sql_from||l_sql_where;
303 execute immediate l_sql_query into l_rowcount using l_person_id ;
304
305 RETURN l_rowcount;
306 END;
307
308
309 BEGIN
310 -- MAIN FUNCTION LOGIC
311 -- [1] Get neat table of all step levels referenced by a passed in rating_level_id
312 -- (dynamic sql as cant use "IN 'string_list'" in cursor)
313 -- [2] Get two neat table of inputs, (one for essential skills, one for preferable, turning ID's into step values using [1]
314 -- [3] Test user has all essential skills at required levels -Get count of total- dynamic sql based on [1] and [2]
315 -- if no value then end function, return -1
316 -- [4] Count number of preferable skills user has at required levels -dynamic sql based on [1] and [2]
317 -- [5] Calculate % Figure depending on totals from sizes in [1], and user attained in [3] and [4]
318 -- Return x %, where x = (100 / total_skills) * number_of_skills_held. 0 <= x <= 100
319
320 -- ########
321 -- ######## Phase [1] Get table of step levels
322 -- ########
323
324 --Build Query; Use cursor to populate table of skill_levels indexed by id
325 -- Get decent comma-delimited list of ids referenced
326 if p_esse_sk_mins_str is not null then
327 l_concats := p_esse_sk_mins_str||',';
328 else
329 l_concats := '-1,';
330 end if;
331 --
332 if p_esse_sk_maxs_str is not null then
333 l_concats := l_concats || p_esse_sk_maxs_str||',';
334 else
335 l_concats := l_concats || '-1,';
336 end if;
337 --
338 if p_pref_sk_mins_str is not null then
339 l_concats := l_concats || p_pref_sk_mins_str||',';
340 else
341 l_concats := l_concats || '-1,';
342 end if;
343 --
344 if p_pref_sk_maxs_str is not null then
345 l_concats := l_concats || p_pref_sk_maxs_str;
346 else
347 l_concats := l_concats || '-1';
348 end if;
349 -- Got list, now build dynamic query
350
351 l_sql_select :='SELECT rl.rating_level_id,rl.step_value';
352 l_sql_from :=' FROM per_rating_levels rl';
353 l_sql_where :=' WHERE rl.rating_level_id IN ('||l_concats||') ';
354 l_sql_query := l_sql_select||l_sql_from||l_sql_where;
355 --NB Appears cant put :1 in where clause and use "open l_rating_cur for g_sql_query using g_concats;"
356 open lc_dyn_query_cur for l_sql_query;
357 LOOP
358 fetch lc_dyn_query_cur into l_rating_level_id_temp,l_step_value_temp;
359 exit when lc_dyn_query_cur%NOTFOUND;
363 --Can now reference the value of a step given its ID by using g_rating_level_tab(id);
360 l_rating_level_tab(l_rating_level_id_temp):=l_step_value_temp;
361 END LOOP;
362 close lc_dyn_query_cur;
364 --If no level is used, an arbitrary value of -1 is used so add this to this quick lookup table
365 l_rating_level_tab(-1):=-1;
366
367 --End of Phase 1
368
369
370 -- ######## Phase [2] Get tables of input
371 -- ########
372 --Turn 3 input strings (related to ESSENTIAL skills) in to 3 tables
373 IF l_esse_sk_list_str is not null
374 THEN
375 l_t1 := string_to_table(l_esse_sk_list_str);
376 l_len := l_t1.count;
377 l_t2 := string_to_table(l_esse_sk_mins_str);
378 if l_len <> l_t2.count then raise INCONSISTENT_INPUT_LENGTHS; end if;
379 l_t3 := string_to_table(l_esse_sk_maxs_str);
380 if l_len <> l_t3.count then raise INCONSISTENT_INPUT_LENGTHS; end if;
381 l_temp_tab.delete;
382
383 --Consolidate 3 tables in to one, if duplicates then restrict to tightest range
384 for j in 1..l_len loop
385 -- If ID for skill exists then replace min/max to form tightest boundaries
386 -- else insert new information in g_essential_tab (if previous min was -1 defo use new min level)
387 if l_temp_tab.exists(l_t1(j)) then
388 if ( l_essential_tab( l_temp_tab(l_t1(j)) ).min_level_num < l_rating_level_tab(l_t2(j))
389 OR l_rating_level_tab(l_t2(j)) = -1) then
390 l_essential_tab( l_temp_tab(l_t1(j)) ).min_level_num := l_rating_level_tab( l_t2(j) );
391 end if;
392 if l_essential_tab( l_temp_tab(l_t1(j)) ).max_level_num > l_rating_level_tab( l_t3(j) ) then
393 l_essential_tab( l_temp_tab(l_t1(j)) ).max_level_num := l_rating_level_tab( l_t3(j) );
394 end if;
395 else
396 l_temp_tab(l_t1(j)):=j; --make record of anything, index by skill_id
397 l_essential_tab(j).skill_id := l_t1(j);
398 l_essential_tab(j).min_level_num := l_rating_level_tab( l_t2(j) );
399 l_essential_tab(j).max_level_num := l_rating_level_tab( l_t3(j) );
400 end if;
401 end loop;
402 if l_essential_tab(1).skill_id <> '-1' then
403 l_tot_esse_skills:= l_essential_tab.count;
404 end if;
405
406 END IF;
407
408 IF l_pref_sk_list_str is not null
409 THEN
410 --Turn 3 input strings (related to PREFERRED skills) in to 3 tables
411 l_t1 := string_to_table(l_pref_sk_list_str);
412 l_len := nvl(l_t1.count,0);
413 l_t2 := string_to_table(l_pref_sk_mins_str);
414 if l_len <> l_t2.count then raise INCONSISTENT_INPUT_LENGTHS; end if;
415 l_t3 := string_to_table(l_pref_sk_maxs_str);
416 if l_len <> l_t3.count then raise INCONSISTENT_INPUT_LENGTHS; end if;
417 l_temp_tab.delete;
418
419 for j in 1..l_len loop
420 if l_temp_tab.exists(l_t1(j)) then
421 if l_preferred_tab( l_temp_tab(l_t1(j)) ).min_level_num < l_rating_level_tab( l_t2(j) ) then
422 l_preferred_tab( l_temp_tab(l_t1(j)) ).min_level_num := l_rating_level_tab( l_t2(j) );
423 end if;
424 if l_preferred_tab( l_temp_tab(l_t1(j)) ).max_level_num > l_rating_level_tab( l_t3(j) ) then
425 l_preferred_tab( l_temp_tab(l_t1(j)) ).max_level_num := l_rating_level_tab( l_t3(j) );
426 end if;
427 else
428 l_temp_tab(l_t1(j)):=j;
429 l_preferred_tab(j).skill_id := l_t1(j);
430 l_preferred_tab(j).min_level_num := l_rating_level_tab( l_t2(j) );
431 l_preferred_tab(j).max_level_num := l_rating_level_tab( l_t3(j) );
432 end if;
433 end loop;
434 if l_preferred_tab(1).skill_id <> '-1' then
435 l_tot_pref_skills:= l_preferred_tab.count;
436 end if;
437 END IF;
438 -- Set totals for tracking and scoring
439 l_tot_skills := l_tot_esse_skills + l_tot_pref_skills;
440
441 -- ########
442 -- ######## Phase [3] Test user has essential skills AND they're at the right level
443 -- ########
444 if l_tot_esse_skills <> 0 then
445 l_tot_esse_skills_held := get_number_of_skill_matches(l_essential_tab);
446 end if;
447
448 -- ######## Phase [4] Count number of preferred skills held at correct level
449 -- ########
450 -- only need to do the remaining code (getting preferred results and scoring) if person
451 -- has ALL of the essential skills
452 IF l_tot_esse_skills_held = l_tot_esse_skills
453 THEN
454 if p_get_percent_flag then --Want exact percentage so find out about preferred skills
455 if l_tot_pref_skills <> 0
456 then
457 l_tot_pref_skills_held := get_number_of_skill_matches(l_preferred_tab);
458 end if;
459 --End of Phase 4
460
461 -- ######## Phase [5] Calculate percentage figure
462 -- ########
463 l_tot_skills_held := l_tot_esse_skills_held + l_tot_pref_skills_held;
464 if l_tot_skills <> 0 then
465 l_tot_percentage_match := (100 / l_tot_skills) *l_tot_skills_held;
466 else l_tot_percentage_match := 100;
467 end if;
468 else --Dont want exact percent, so skip the extra calculations
469 l_tot_percentage_match := 1;
470 end if;
471 ELSE
472 l_tot_percentage_match := -1;
473 END IF;
474
475
476 RETURN l_tot_percentage_match;
477
478 EXCEPTION
479 WHEN INCONSISTENT_INPUT_LENGTHS then
480 hr_utility.set_message(800, 'IRC_INCONSISTENT_LIST_LENGTHS');
484
481 hr_utility.raise_error;
482
483 END SKILLS_MATCH; --end main function
485
486 -- ----------------------------------------------------------------------------
487 -- |-----------------------< GET_SKILLS_FOR_SCOPE_TABLE >--------------------------|
488 -- ----------------------------------------------------------------------------
489
490 -- Take a set of ID's and return a completed table, then calling function can manipulate as required.
491 -- This is called by get_skills_for_scope and vacancy_match_percent
492 FUNCTION GET_SKILLS_FOR_SCOPE_TABLE (
493 p_org_id number default 0,
494 p_bgp_id number default 0,
495 p_job_id number default 0,
496 p_pos_id number default 0,
497 p_eff_date date default sysdate) RETURN gt_skills_tab_type
498
499
500 IS
501 -- Reads parameters in to local variables
502 l_org_id number := p_org_id;
503 l_bgp_id number := p_bgp_id;
504 l_job_id number := p_job_id;
505 l_pos_id number := p_pos_id;
506 l_eff_date date := p_eff_date;
507 l_counter number := 1;
508
509 l_table gt_skills_tab_type;
510 BEGIN
511 -- MAIN FUNCTION LOGIC
512
513
514 FOR l_skill_rec IN gc_skills_reqd_list(l_org_id,l_bgp_id,l_job_id,l_pos_id,l_eff_date) LOOP
515 --Set first information, eg competence_id and mandatory
516 l_table(l_counter).skill_id := l_skill_rec.competence_id;
517 l_table(l_counter).essential := l_skill_rec.mandatory;
518 if l_skill_rec.rating_scale_id is NULL then
519 open gc_comp_rating_level_details(l_skill_rec.competence_id,l_skill_rec.highest_min_level,l_skill_rec.lowest_max_level);
520 --set min val (result of first fetch), and max (2nd row)
521 fetch gc_comp_rating_level_details into l_table(l_counter).min_id;
522 fetch gc_comp_rating_level_details into l_table(l_counter).max_id;
523 close gc_comp_rating_level_details;
524 else
525 open gc_rating_level_details(l_skill_rec.rating_scale_id,l_skill_rec.highest_min_level,l_skill_rec.lowest_max_level);
526 --set min val (result of first fetch), and max (2nd row)
527 fetch gc_rating_level_details into l_table(l_counter).min_id;
528 fetch gc_rating_level_details into l_table(l_counter).max_id;
529 close gc_rating_level_details;
530 end if;
531
532 if l_skill_rec.highest_min_level = l_skill_rec.lowest_max_level then l_table(l_counter).max_id := l_table(l_counter).min_id; end if;
533
534 if l_table(l_counter).min_id is null then l_table(l_counter).min_id := -1; end if;
535 if l_table(l_counter).max_id is null then l_table(l_counter).max_id := -1; end if;
536
537 l_counter := l_counter + 1;
538 END LOOP;
539 RETURN l_table;
540
541 END GET_SKILLS_FOR_SCOPE_TABLE;
542
543
544 -- ----------------------------------------------------------------------------
545 -- |-----------------------< GET_SKILLS_FOR_SCOPE >--------------------------|
546 -- ----------------------------------------------------------------------------
547
548 --Take a set of ID's and return the skill requirements
549 FUNCTION GET_SKILLS_FOR_SCOPE (
550 p_org_id number default 0,
551 p_bgp_id number default 0,
552 p_job_id number default 0,
553 p_pos_id number default 0,
554 p_eff_date date default sysdate) RETURN VARCHAR2
555
556
557 IS
558 l_got_skill_ids_tab gt_skills_tab_type;
559 l_result_str VARCHAR2(2000) := '';
560
561 BEGIN
562 -- MAIN FUNCTION LOGIC
563 --Get table of skills, and then format as required
564 l_got_skill_ids_tab := GET_SKILLS_FOR_SCOPE_TABLE(p_org_id,p_bgp_id,p_job_id,p_pos_id,p_eff_date);
565
566 -- Format to return is
567 -- "competence_id,mandatory,min-level_id,max-level_id>competence_id,mandatory,min-level_id,max-level_id>..."
568
569 FOR item IN 1..l_got_skill_ids_tab.count LOOP
570 if item <> 1 then l_result_str := l_result_str ||gco_delim2;
571 end if;
572 l_result_str := l_result_str||l_got_skill_ids_tab(item).skill_id||gco_delim||l_got_skill_ids_tab(item).essential
573 ||gco_delim||l_got_skill_ids_tab(item).min_id||gco_delim||l_got_skill_ids_tab(item).max_id;
574 END LOOP;
575
576 if (l_result_str = '') then l_result_str := '-1'; end if;
577
578 RETURN l_result_str;
579
580 END GET_SKILLS_FOR_SCOPE;
581
582 -- ----------------------------------------------------------------------------
583 -- |-----------------------< GET_SKILLS_FOR_VAC >--------------------------|
584 -- ----------------------------------------------------------------------------
585
586 --Take a set of ID's and return the requirements
587 FUNCTION GET_SKILLS_FOR_VAC (
588 p_vacancy_id varchar2 default 0,
589 p_eff_date date default sysdate) RETURN VARCHAR2
590
591
592 IS
593 -- Reads parameters in to local variables
594 l_org_id varchar2(240) := '0';
595 l_bgp_id varchar2(240) := '0';
596 l_job_id varchar2(240) := '0';
597 l_pos_id varchar2(240) := '0';
598 l_result_str VARCHAR2(2000) := '';
599 l_got_skill_ids_tab gt_skills_tab_type;
600
604 -- [2] Get skills required for scopes
601 BEGIN
602 -- MAIN FUNCTION LOGIC
603 -- [1] Get scopes associated with vacancy
605 -- [3] Turn results in to required format
606
607 -- ######## Phase [1] Get scopes associated with vacancy
608 -- ########
609 open gc_get_vac_scopes(p_vacancy_id, p_eff_date);
610 fetch gc_get_vac_scopes into l_org_id,l_bgp_id,l_pos_id,l_job_id;
611 close gc_get_vac_scopes;
612
613 -- ######## Phase [2] Get skills required for scopes
614 -- ########
615 l_got_skill_ids_tab := GET_SKILLS_FOR_SCOPE_TABLE(l_org_id,l_bgp_id,l_job_id,l_pos_id,p_eff_date);
616
617 -- ######## Phase [3] Turn table in to preferred format
618 -- ########
619 -- "competence_id,mandatory,min-level_id,max-level_id>competence_id,mandatory,min-level_id,max-level_id>..."
620 FOR item IN 1..l_got_skill_ids_tab.count LOOP
621 if item <> 1 then l_result_str := l_result_str ||gco_delim2;
622 end if;
623 l_result_str := l_result_str||l_got_skill_ids_tab(item).skill_id||gco_delim||l_got_skill_ids_tab(item).essential
624 ||gco_delim||l_got_skill_ids_tab(item).min_id||gco_delim||l_got_skill_ids_tab(item).max_id;
625 END LOOP;
626
627 RETURN l_result_str;
628
629 END GET_SKILLS_FOR_VAC;
630
631 -- ----------------------------------------------------------------------------
632 -- |-----------------------< VACANCY_MATCH_PERCENT >--------------------------|
633 -- ----------------------------------------------------------------------------
634
635 FUNCTION VACANCY_MATCH_PERCENT (
636 p_person_id number,
637 p_vacancy_id varchar2,
638 p_eff_date date default sysdate) RETURN VARCHAR2
639
640 IS
641
642 CURSOR lc_get_vac_requirements(cp_vacancy_id per_all_vacancies.vacancy_id%TYPE) IS
643 select competence_id,
644 nvl(mandatory,'N') as mandatory,
645 nvl(proficiency_level_id,-1) as proficiency_level_id,
646 nvl(high_proficiency_level_id,-1) as high_proficiency_level_id
647 from per_competence_elements
648 where object_name = 'VACANCY'
649 and object_id = CP_VACANCY_ID;
650
651 l_esse_sk_list_str varchar2(240);
652 l_esse_sk_mins_str varchar2(240);
653 l_esse_sk_maxs_str varchar2(240);
654 l_pref_sk_list_str varchar2(240);
655 l_pref_sk_mins_str varchar2(240);
656 l_pref_sk_maxs_str varchar2(240);
657
658 l_delim varchar2(1) :='';
659 l_delimp varchar2(1) :='';
660
661 l_percentage varchar2(3);
662
663 BEGIN
664 -- MAIN FUNCTION LOGIC -VACANCY_MATCH_PERCENT
665 -- [1] Get skills required for vacancy
666 -- [2] Reformat in to strings so can...
667 -- [3] call skills_match_percent
668
669 -- ######## Phase [1], [2] Get skills associated with vacancy and place in strings
670 -- ########
671 l_percentage := -1;
672
673 FOR l_skill_requirement_rec IN lc_get_vac_requirements(p_vacancy_id) LOOP
674 if l_skill_requirement_rec.mandatory = 'Y' then
675 l_esse_sk_list_str := l_esse_sk_list_str||l_delim||l_skill_requirement_rec.competence_id;
676 l_esse_sk_mins_str := l_esse_sk_mins_str||l_delim||l_skill_requirement_rec.proficiency_level_id;
677 l_esse_sk_maxs_str := l_esse_sk_maxs_str||l_delim||l_skill_requirement_rec.high_proficiency_level_id;
678 l_delim := gco_delim;
679 else
680 l_pref_sk_list_str := l_pref_sk_list_str||l_delimp||l_skill_requirement_rec.competence_id;
681 l_pref_sk_mins_str := l_pref_sk_mins_str||l_delimp||l_skill_requirement_rec.proficiency_level_id;
682 l_pref_sk_maxs_str := l_pref_sk_maxs_str||l_delimp||l_skill_requirement_rec.high_proficiency_level_id;
683 l_delimp := gco_delim;
684 end if;
685 END LOOP;
686
687
688 -- ######## Phase [3] Get Percentage using lists of skills and levels
689 -- ########
690 l_percentage := IRC_SKILLS_MATCHING_PKG.skills_match_percent(
691 l_esse_sk_list_str,l_esse_sk_mins_str,l_esse_sk_maxs_str,
692 l_pref_sk_list_str,l_pref_sk_mins_str,l_pref_sk_maxs_str, p_person_id);
693 return l_percentage;
694
695 END VACANCY_MATCH_PERCENT;
696
697 END IRC_SKILLS_MATCHING_PKG;
[Home] [Help]