[Home] [Help]
PACKAGE BODY: APPS.HR_SUIT_MATCH_UTILITY_WEB
Source
1 PACKAGE BODY hr_suit_match_utility_web AS
2 /* $Header: hrsmutlw.pkb 120.3 2005/12/13 13:44:26 svittal noship $ */
3
4 g_package constant varchar2(31) := 'hr_suit_match_utility_web.';
5 g_region_application_id constant integer
6 := hr_util_misc_web.g_region_application_id;
7
8 c_title hr_util_misc_web.g_title%TYPE;
9 g_prompts hr_util_misc_web.g_prompts%TYPE;
10 c_person_id per_people_f.person_id%type;
11 c_language_code varchar2(5);
12 c_legislation_code varchar2(5);
13 g_person_rec per_people_f%ROWTYPE;
14
15 -- ---------------------------------------------------------------------------
16 -- get_option_header
17 -- ---------------------------------------------------------------------------
18 FUNCTION get_option_header(p_mode in varchar2)
19 RETURN varchar2 IS
20 BEGIN
21 IF p_mode = hr_suit_match_utility_web.g_select_people_work_mode THEN
22 fnd_message.set_name('PER', 'HR_WEB_SM_MENU_1');
23 ELSIF p_mode = hr_suit_match_utility_web.g_match_peope_role_mode THEN
24 fnd_message.set_name('PER', 'HR_WEB_SM_MENU_2');
25 ELSIF p_mode = hr_suit_match_utility_web.g_match_successors_pos_mode THEN
26 fnd_message.set_name('PER', 'HR_WEB_SM_MENU_3');
27 ELSIF p_mode = hr_suit_match_utility_web.g_match_applicants_van_mode THEN
28 fnd_message.set_name('PER', 'HR_WEB_SM_MENU_4');
29 ELSIF p_mode = hr_suit_match_utility_web.g_work_vacancies_fast_path THEN
30 fnd_message.set_name('PER', 'HR_WEB_SM_MENU_5');
31 ELSIF p_mode = hr_suit_match_utility_web.g_work_successions_fast_path THEN
32 fnd_message.set_name('PER', 'HR_WEB_SM_MENU_6');
33 ELSIF p_mode = hr_suit_match_utility_web.g_work_deployments_fast_path THEN
34 fnd_message.set_name('PER', 'HR_WEB_SM_MENU_7');
35 ELSIF p_mode = hr_suit_match_utility_web.g_match_work_mode THEN
36 fnd_message.set_name('PER', 'HR_WEB_SM_MENU_8');
37 ELSE
38 RETURN null;
39 END IF;
40 RETURN fnd_message.get;
41 END get_option_header;
42
43 -- ---------------------------------------------------------------------------
44 -- get_lookup_meaning
45 -- ---------------------------------------------------------------------------
46 FUNCTION get_lookup_meaning
47 (p_lookup_type in varchar2
48 ,p_lookup_code in varchar2
49 ,p_schema in varchar2 default 'HR')
50 RETURN varchar2 IS
51
52 CURSOR csr_hr_lookup IS
53 SELECT meaning
54 FROM hr_lookups
55 WHERE lookup_type = p_lookup_type
56 AND lookup_code = p_lookup_code;
57
58 CURSOR csr_fnd_lookup IS
59 SELECT meaning
60 FROM fnd_common_lookups
61 WHERE lookup_type = p_lookup_type
62 AND lookup_code = p_lookup_code;
63
64 l_meaning varchar2(2000);
65
66 BEGIN
67
68 IF p_schema = 'HR' THEN
69 OPEN csr_hr_lookup;
70 FETCH csr_hr_lookup INTO l_meaning;
71 CLOSE csr_hr_lookup;
72 ELSIF p_schema = 'FND' THEN
73 OPEN csr_fnd_lookup;
74 FETCH csr_fnd_lookup INTO l_meaning;
75 CLOSE csr_fnd_lookup;
76 END IF;
77
78 RETURN l_meaning;
79
80 END get_lookup_meaning;
81 -- ---------------------------------------------------------------------------
82 -- get_max_step_value
83 -- ---------------------------------------------------------------------------
84
85 FUNCTION get_max_step_value
86 (p_competence_id in hr_util_misc_web.g_varchar2_tab_type)
87
88 RETURN number IS
89
90 l_dynamic_sql varchar2(32000);
91 l_sql_cursor integer;
92 l_rows integer;
93 l_max_step_value number;
94 c_id number;
95 l_ids varchar2(32000);
96 c_name varchar2(240);
97
98 BEGIN
99
100 l_ids := build_items(p_id => p_competence_id);
101
102 l_dynamic_sql :=
103 'SELECT max(count(step_value))'
104 ||' FROM per_competence_levels_v'
105 ||' WHERE competence_id IN ('||l_ids||')'
106 ||' GROUP BY competence_id';
107
108 l_sql_cursor := dbms_sql.open_cursor;
109 dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7);
110 dbms_sql.define_column(l_sql_cursor, 1, c_id, 15);
111 l_rows := dbms_sql.execute(l_sql_cursor);
112
113 IF dbms_sql.fetch_rows(l_sql_cursor) > 0 THEN
114 dbms_sql.column_value(l_sql_cursor, 1, c_name);
115 l_max_step_value := to_number(c_name);
116 END IF;
117
118 dbms_sql.close_cursor(l_sql_cursor);
119
120 RETURN l_max_step_value;
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 IF dbms_sql.is_open(l_sql_cursor) THEN
125 dbms_sql.close_cursor(l_sql_cursor);
126 END IF;
127 RETURN 0;
128 END get_max_step_value;
129 -- ---------------------------------------------------------------------------
130 -- encode_competence_table
131 -- ---------------------------------------------------------------------------
132 PROCEDURE encode_competence_table
133 (p_competence_id in hr_util_misc_web.g_varchar2_tab_type
134 ,p_competence_name in hr_util_misc_web.g_varchar2_tab_type
135 ,p_low_rating_level_id in hr_util_misc_web.g_varchar2_tab_type
136 ,p_high_rating_level_id in hr_util_misc_web.g_varchar2_tab_type
137 ,p_mandatory in hr_util_misc_web.g_varchar2_tab_type
138 ,p_competence_table out nocopy g_competence_table
139 ,p_essential_count out nocopy number
140 ,p_desirable_count out nocopy number) IS
141 BEGIN
142 p_essential_count := 0;
143 p_desirable_count := 0;
144
145 FOR i IN 1..NVL(p_competence_id.count,0) LOOP
146 p_competence_table(i).competence_id := p_competence_id(i);
147 p_competence_table(i).competence_name := p_competence_name(i);
148 p_competence_table(i).low_rating_level_id := p_low_rating_level_id(i);
149 p_competence_table(i).high_rating_level_id := p_high_rating_level_id(i);
150 p_competence_table(i).low_step_value :=
151 hr_suit_match_utility_web.get_step_value
152 (p_rating_level_id => p_low_rating_level_id(i));
153 p_competence_table(i).high_step_value :=
154 hr_suit_match_utility_web.get_step_value
155 (p_rating_level_id => p_high_rating_level_id(i));
156 p_competence_table(i).mandatory := p_mandatory(i);
157 IF p_mandatory(i) = 'Y' THEN
158 p_essential_count := p_essential_count + 1;
159 ELSE
160 p_desirable_count := p_desirable_count + 1;
161 END IF;
162 END LOOP;
163 END encode_competence_table;
164 -- ---------------------------------------------------------------------------
165 -- decode_competence_table
166 -- ---------------------------------------------------------------------------
167 PROCEDURE decode_competence_table
168 (p_competence_table in g_competence_table
169 ,p_competence_id out nocopy hr_util_misc_web.g_varchar2_tab_type
170 ,p_competence_name out nocopy hr_util_misc_web.g_varchar2_tab_type
171 ,p_low_rating_level_id out nocopy hr_util_misc_web.g_varchar2_tab_type
172 ,p_high_rating_level_id out nocopy hr_util_misc_web.g_varchar2_tab_type
173 ,p_mandatory out nocopy hr_util_misc_web.g_varchar2_tab_type) IS
174 BEGIN
175 FOR i IN 1..NVL(p_competence_table.count,0) LOOP
176 p_competence_id(i) := p_competence_table(i).competence_id;
177 p_competence_name(i) := p_competence_table(i).competence_name;
178 p_low_rating_level_id(i) := p_competence_table(i).low_rating_level_id;
179 p_high_rating_level_id(i) := p_competence_table(i).high_rating_level_id;
180 p_mandatory(i) := p_competence_table(i).mandatory;
181 END LOOP;
182 END decode_competence_table;
183
184 -- ---------------------------------------------------------------------------
185 -- get_work_detail_name
186 -- ---------------------------------------------------------------------------
187
188 FUNCTION get_work_detail_name
189 (p_search_type in varchar2
190 ,p_search_id in varchar2)
191 RETURN varchar2 IS
192
193 l_dynamic_sql varchar2(32000);
194 l_id hr_util_misc_web.g_varchar2_tab_type;
195 l_name hr_util_misc_web.g_varchar2_tab_type;
196 l_count number default 0;
197
198 BEGIN
199 l_dynamic_sql := hr_suit_match_utility_web.build_sql
200 (p_search_type => p_search_type
201 ,p_ids => p_search_id);
202 hr_suit_match_utility_web.get_id_name
203 (p_dynamic_sql => l_dynamic_sql
204 ,p_id => l_id
205 ,p_name => l_name
206 ,p_count => l_count);
207
208 IF l_count > 0 THEN
209 RETURN l_name(1);
210 ELSE
211 RETURN null;
212 END IF;
213
214 END get_work_detail_name;
215 -- ---------------------------------------------------------------------------
216 -- get_person_info
217 -- ---------------------------------------------------------------------------
218 PROCEDURE get_person_info
219 (p_id in number
220 ,p_person_table out nocopy g_person_table) IS
221
222 l_id hr_util_misc_web.g_varchar2_tab_type;
223 l_count number;
224
225 BEGIN
226
227 l_id(1) := p_id;
228 get_people_info(p_id => l_id
229 ,p_person_table => p_person_table
230 ,p_count => l_count);
231
232 END get_person_info;
233
234 -- ---------------------------------------------------------------------------
235 -- get_people_info
236 -- ---------------------------------------------------------------------------
237
238 PROCEDURE get_people_info
239 (p_id in hr_util_misc_web.g_varchar2_tab_type
240 ,p_person_table out nocopy g_person_table
241 ,p_count out nocopy number) IS
242
243 l_dynamic_sql varchar2(32000);
244 l_sql_cursor integer;
245 l_rows integer;
246 l_index number;
247 c_id varchar2(15);
248 c_name varchar2(240);
249 c_type varchar2(240);
250 c_phone varchar2(240);
251 c_location varchar2(240);
252 c_employee_number varchar2(30);
253 c_applicant_number varchar2(30);
254 c_hire_date varchar2(30);
255 l_ids varchar2(32000);
256
257 BEGIN
258
259 hr_util_misc_web.validate_session(p_person_id => c_person_id);
260 c_legislation_code := hr_misc_web.get_legislation_code
261 (p_person_id => c_person_id);
262
263
264 l_ids := build_items(p_id => p_id);
265
266 -- bug# 2447224.
267 -- Bug#3374753 begin
268 l_dynamic_sql :=
269 'SELECT distinct ppf.person_id';
270 l_dynamic_sql := l_dynamic_sql
271 ||',ppf.full_name name';
272 l_dynamic_sql := l_dynamic_sql
273 ||',hr_person_type_usage_info.get_user_person_type(trunc(sysdate),ppf.person_id) user_person_type'
274 ||',hr_general.get_work_phone(ppf.person_id)'
275 ||',ppf.internal_location'
276 ||',ppf.employee_number'
277 ||',ppf.applicant_number'
278 ||',decode (ppf.current_employee_flag ,''Y'',ppos.date_start,null)'
279 ||',ppf.order_name'
280 ||' FROM per_people_f ppf'
281 ||',per_periods_of_service ppos'
282 ||' WHERE ppf.person_id IN ('||l_ids||')'
283 -- Bug # 2670769 fix begins.
284 -- Bug#3282680 start
285 -- ||' AND (ppf.current_employee_flag = ''Y'''
286 -- ||' OR ppf.current_applicant_flag = ''Y'')'
287 ||' AND TRUNC(sysdate) BETWEEN ppf.effective_start_date '
288 ||' AND ppf.effective_end_date'
289 ||' AND ppos.person_id(+) = ppf.person_id'
290 ||' AND ((ppf.current_employee_flag = ''Y'''
291 ||' AND ppf.effective_start_date between ppos.date_start'
292 ||' AND nvl(ppos.actual_termination_date,ppf.effective_start_date))'
293 ||' OR ((ppf.current_employee_flag is null)'
294 ||' AND (ppf.current_applicant_flag = ''Y'')))'
295 -- ||' AND ppf.effective_start_date between ppos.date_start(+)'
296 -- ||' AND nvl(ppos.actual_termination_date,ppf.effective_start_date)'
297 -- Bug#32382680 end
298 -- Bug#3374753 end
299 -- Bug #2667216 Fix begins
300 -- ||' AND ppos.date_start(+) BETWEEN ppf.effective_start_date '
301 -- ||' AND ppf.effective_end_date'
302 -- Bug #2667216 Fix ends.
303 -- Bug # 2670769 fix ends.
304 -- Bug# 2388754 Fix
305 --||' AND (ppos.actual_termination_date is null '
306 --||' or ppos.actual_termination_date > trunc(sysdate))'
307 -- Bug# 2388754 Fix
308 ||' AND ppos.business_group_id(+) = ppf.business_group_id';
309 IF c_legislation_code = g_japan_legislation_code THEN
310 l_dynamic_sql := l_dynamic_sql
311 ||' ORDER BY ppf.last_name,ppf.first_name';
312 ELSE
313 l_dynamic_sql := l_dynamic_sql
314 ||' ORDER BY NVL(ppf.order_name, ppf.full_name)';
315 END IF;
316
317 l_sql_cursor := dbms_sql.open_cursor;
318 dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7);
319 dbms_sql.define_column(l_sql_cursor, 1, c_id, 15);
320 dbms_sql.define_column(l_sql_cursor, 2, c_name, 240);
321 dbms_sql.define_column(l_sql_cursor, 3, c_type, 2000);
322 dbms_sql.define_column(l_sql_cursor, 4, c_phone, 240);
323 dbms_sql.define_column(l_sql_cursor, 5, c_location, 240);
324 dbms_sql.define_column(l_sql_cursor, 6, c_employee_number, 30);
325 dbms_sql.define_column(l_sql_cursor, 7, c_applicant_number, 30);
326 dbms_sql.define_column(l_sql_cursor, 8, c_hire_date, 30);
327 l_rows := dbms_sql.execute(l_sql_cursor);
328 l_index := 0;
329 WHILE dbms_sql.fetch_rows(l_sql_cursor) > 0 LOOP
330 l_index := l_index + 1;
331 dbms_sql.column_value(l_sql_cursor, 1, c_id);
332 dbms_sql.column_value(l_sql_cursor, 2, p_person_table(l_index).name);
333 dbms_sql.column_value(l_sql_cursor, 3, p_person_table(l_index).person_type);
334 dbms_sql.column_value(l_sql_cursor, 4, p_person_table(l_index).work_phone);
335 dbms_sql.column_value
336 (l_sql_cursor, 5, p_person_table(l_index).location_code);
337 dbms_sql.column_value
338 (l_sql_cursor, 6, p_person_table(l_index).employee_number);
339 dbms_sql.column_value
340 (l_sql_cursor, 7, p_person_table(l_index).applicant_number);
341 dbms_sql.column_value
342 (l_sql_cursor, 8, c_hire_date);
343 p_person_table(l_index).person_id := c_id;
344 p_person_table(l_index).hire_date := c_hire_date;
345 END LOOP;
346
347 dbms_sql.close_cursor(l_sql_cursor);
348
349 p_count := l_index;
350
351 EXCEPTION
352 WHEN others THEN
353 IF dbms_sql.is_open(l_sql_cursor) THEN
354 dbms_sql.close_cursor(l_sql_cursor);
355 END IF;
356 END get_people_info;
357
358 -- ---------------------------------------------------------------------------
359 -- keyflex_select_where_clause
360 -- ---------------------------------------------------------------------------
361 PROCEDURE keyflex_select_where_clause
362 (p_business_group_id in number
363 ,p_keyflex_code in varchar2
364 ,p_filter_clause in varchar2 default null
365 ,p_select_clause out nocopy varchar2
366 ,p_where_clause out nocopy varchar2) IS
367
368 l_mapped_col_names hr_util_misc_web.g_varchar2_tab_type;
369 l_segment_separator varchar2(10);
370 l_count number;
371 l_table_short_name varchar2(10);
372
373 BEGIN
374
375 IF p_keyflex_code = 'JOB' THEN
376 l_table_short_name := 'pjd';
377 ELSIF p_keyflex_code = 'POS' THEN
378 l_table_short_name := 'ppd';
379 ELSIF p_keyflex_code = 'GRD' THEN
380 l_table_short_name := 'pgd';
381 END IF;
382
383 get_keyflex_mapped_column_name
384 (p_business_group_id => p_business_group_id
385 ,p_keyflex_code => p_keyflex_code
386 ,p_mapped_col_names => l_mapped_col_names
387 ,p_segment_separator => l_segment_separator
388 ,p_count => l_count);
389
390
391 FOR i IN 1..l_count LOOP
392 IF p_select_clause IS null THEN
393 p_select_clause := l_table_short_name
394 ||l_segment_separator
395 ||l_mapped_col_names(i);
396 IF p_filter_clause IS NOT null THEN
397 p_where_clause := 'UPPER('|| l_table_short_name
398 ||l_segment_separator
399 ||l_mapped_col_names(i) ||')'
400 ||' '
401 ||p_filter_clause;
402 END IF;
403 ELSE
404 p_select_clause := p_select_clause
405 ||'||'
406 ||l_table_short_name
407 ||l_segment_separator
408 ||l_mapped_col_names(i);
409 IF p_filter_clause IS NOT null THEN
410 p_where_clause := p_where_clause
411 ||' OR '
412 ||'UPPER('||l_table_short_name
413 ||l_segment_separator
414 ||l_mapped_col_names(i) || ')'
415 ||' '
416 ||p_filter_clause;
417 END IF;
418 END IF;
419 END LOOP;
420
421 IF p_select_clause IS NOT null THEN
422 p_select_clause := p_select_clause ||' name';
423 ELSE
424 p_select_clause := substr(l_table_short_name,1,2)||'.name name';
425 END IF;
426 IF p_filter_clause IS NOT null THEN
427 IF p_where_clause IS NOT null THEN
428 p_where_clause := '(' || p_where_clause || ')';
429 ELSE
430 p_where_clause := 'UPPER('||substr(l_table_short_name,1,2)||'.name) '
431 ||p_filter_clause;
432 END IF;
433 END IF;
434
435
436
437 END keyflex_select_where_clause;
438 -- ---------------------------------------------------------------------------
439 -- get_keyflex_mapped_column_name
440 -- ---------------------------------------------------------------------------
441 PROCEDURE get_keyflex_mapped_column_name
442 (p_business_group_id in number
443 ,p_keyflex_code in varchar2
444 ,p_mapped_col_names out nocopy hr_util_misc_web.g_varchar2_tab_type
445 ,p_segment_separator out nocopy varchar2
446 ,p_count out nocopy number) IS
447
448 l_mapped_col_name1 varchar2(60);
449 l_mapped_col_name2 varchar2(60);
450 l_id_flex_num number;
451
452 l_warning varchar2(2000);
453
454 BEGIN
455
456 p_count := 0;
457
458 hr_util_flex_web.get_keyflex_mapped_column_name
459 (p_business_group_id => p_business_group_id
460 ,p_keyflex_code => p_keyflex_code
461 ,p_mapped_col_name1 => l_mapped_col_name1
462 ,p_mapped_col_name2 => l_mapped_col_name2
463 ,p_keyflex_id_flex_num => l_id_flex_num
464 ,p_segment_separator => p_segment_separator
465 ,p_warning => l_warning);
466
467 IF l_warning IS NOT null THEN
468 return;
469 END IF;
470
471 IF l_mapped_col_name1 IS NOT null THEN
472 p_mapped_col_names(1) := l_mapped_col_name1;
473 p_count := p_count + 1;
474 END IF;
475
476 IF l_mapped_col_name2 IS NOT null THEN
477 p_mapped_col_names(2) := l_mapped_col_name2;
478 p_count := p_count + 1;
479 END IF;
480
481 EXCEPTION
482 WHEN others THEN
483 return;
484
485 END get_keyflex_mapped_column_name;
486 -- ---------------------------------------------------------------------------
487 -- get_search_count
488 -- ---------------------------------------------------------------------------
489
490 FUNCTION get_search_count
491 (p_mode in varchar2
492 ,p_person_type_id in varchar2 default null
493 ,p_assignment_type in varchar2 default null
494 ,p_pre_search_type in varchar2
495 ,p_pre_search_ids in varchar2
496 ,p_search_type in varchar2
497 ,p_filer_match in varchar2
498 ,p_search_criteria in varchar2)
499 RETURN number IS
500
501 l_dynamic_sql varchar2(32000);
502 l_id hr_util_misc_web.g_varchar2_tab_type;
503 l_name hr_util_misc_web.g_varchar2_tab_type;
504 l_count number default 0;
505
506 BEGIN
507
508 l_dynamic_sql := hr_suit_match_utility_web.build_sql
509 (p_mode => p_mode
510 ,p_person_type_id => p_person_type_id
511 ,p_assignment_type => p_assignment_type
512 ,p_pre_search_type => p_pre_search_type
513 ,p_pre_search_ids => p_pre_search_ids
514 ,p_search_type => p_search_type
515 ,p_filer_match => p_filer_match
516 ,p_search_criteria => p_search_criteria);
517
518 hr_suit_match_utility_web.get_id_name
519 (p_dynamic_sql => l_dynamic_sql
520 ,p_id => l_id
521 ,p_name => l_name
522 ,p_count => l_count);
523
524 RETURN l_count;
525
526 END get_search_count;
527 -- ---------------------------------------------------------------------------
528 -- get_step_value
529 -- ---------------------------------------------------------------------------
530
531 FUNCTION get_step_value
532 (p_rating_level_id in number)
533 RETURN per_rating_levels.step_value%TYPE IS
534
535 CURSOR csr_step_value IS
536 SELECT step_value
537 FROM per_competence_levels_v
538 WHERE rating_level_id = p_rating_level_id;
539
540 l_step_value per_rating_levels.step_value%TYPE;
541
542 BEGIN
543
544 OPEN csr_step_value;
545 FETCH csr_step_value INTO l_step_value;
546 CLOSE csr_step_value;
547
548 RETURN l_step_value;
549
550 END get_step_value;
551 -- ---------------------------------------------------------------------------
552 -- get_competence_name
553 -- ---------------------------------------------------------------------------
554
555 FUNCTION get_competence_name
556 (p_competence_id in number)
557 RETURN per_competences_tl.name%type IS
558
559 CURSOR csr_competence_name IS
560 SELECT name
561 FROM per_competences_tl
562 WHERE competence_id = p_competence_id
563 AND language = userenv('LANG') ;
564
565 l_name per_competences_tl.name%TYPE;
566
567 BEGIN
568
569 OPEN csr_competence_name;
570 FETCH csr_competence_name INTO l_name;
571 CLOSE csr_competence_name;
572
573 RETURN l_name;
574
575 END get_competence_name;
576
577 -- ---------------------------------------------------------------------------
578 -- get_drived_org_job
579 -- ---------------------------------------------------------------------------
580
581 PROCEDURE get_drived_org_job
582 (p_pos_id in number
583 ,p_org_id out nocopy number
584 ,p_job_id out nocopy number) IS
585
586 CURSOR csr_position IS
587 SELECT organization_id, job_id
588 FROM hr_positions_f
589 WHERE position_id = p_pos_id
590 AND TRUNC(SYSDATE) BETWEEN effective_start_date
591 AND effective_end_date;
592
593 BEGIN
594
595 OPEN csr_position;
596 FETCH csr_position INTO p_org_id, p_job_id;
597 CLOSE csr_position;
598
599 END get_drived_org_job;
600 -- ---------------------------------------------------------------------------
601 -- get_item
602 -- ---------------------------------------------------------------------------
603
604 FUNCTION get_item
605 (p_ids in varchar2
606 ,p_index in number)
607 RETURN varchar2 IS
608 l_value varchar2(2000);
609 l_start integer;
610 l_end integer;
611 l_temp varchar2(32000);
612 BEGIN
613 l_temp := ','||p_ids||',';
614 l_start := instr (l_temp, ',', 1, p_index);
615 l_end := instr (l_temp, ',', 1, p_index+1);
616 IF l_start = 0 or l_end = 0 THEN
617 RETURN null;
618 ELSE
619 l_value := (rtrim (ltrim(substr(l_temp, l_start + 1,
620 l_end - l_start - 1))));
621 END IF;
622
623
624 RETURN (l_value);
625
626
627 END get_item;
628 -- ---------------------------------------------------------------------------
629 -- build_items
630 -- ---------------------------------------------------------------------------
631
632 FUNCTION build_items
633 (p_id in hr_util_misc_web.g_varchar2_tab_type
634 ,p_start_index in number default 1)
635 RETURN varchar2 IS
636 l_ids varchar2(32000);
637 l_index number;
638 l_temp varchar2(2000);
639 BEGIN
640 l_index := p_start_index;
641 BEGIN
642 LOOP
643 IF p_id(l_index) IS null THEN
644 l_temp := 'null';
645 ELSE
646 l_temp := p_id(l_index);
647 END IF;
648 IF l_ids IS null THEN
649 l_ids := l_temp;
650 ELSIF (length(l_ids)) < (32000 - 30) THEN
651 l_ids := l_ids ||','||l_temp;
652 ELSE
653 EXIT;
654 END IF;
655 l_index := l_index + 1;
656 END LOOP;
657 EXCEPTION
658 WHEN NO_DATA_FOUND THEN
659 null;
660 END;
661
662 RETURN l_ids;
663
664 END build_items;
665
666 -- ---------------------------------------------------------------------------
667 -- build_grade_sql
668 -- ---------------------------------------------------------------------------
669
670 FUNCTION build_grade_sql
671 (p_search_type in varchar2
672 ,p_id in number)
673 RETURN varchar2 IS
674
675 l_dynamic_sql varchar2(32000);
676 l_flex_select_clause varchar2(2000);
677 l_flex_where_clause varchar2(2000);
678
679 l_business_group varchar2(2000);
680
681 BEGIN
682
683 l_business_group := hr_util_misc_web.get_business_group_id;
684
685 keyflex_select_where_clause
686 (p_business_group_id => l_business_group
687 ,p_keyflex_code => 'GRD'
688 ,p_select_clause => l_flex_select_clause
689 ,p_where_clause => l_flex_where_clause);
690
691 IF p_search_type = g_job_type THEN
692 l_dynamic_sql := 'SELECT distinct(pvg.valid_grade_id),'
693 ||l_flex_select_clause
694 ||' FROM per_valid_grades pvg,'
695 ||' per_grades pg, per_grade_definitions pgd'
696 ||' WHERE pvg.job_id = '||p_id
697 ||' AND pvg.grade_id = pg.grade_id'
698 ||' AND pg.grade_definition_id = pgd.grade_definition_id';
699 ELSIF p_search_type = g_position_type THEN
700 l_dynamic_sql := 'SELECT distinct(pvg.valid_grade_id),'
701 ||l_flex_select_clause
702 ||' FROM per_valid_grades pvg,'
703 ||' per_grades pg, per_grade_definitions pgd'
704 ||' WHERE pvg.position_id = '||p_id
705 ||' AND pvg.grade_id = pg.grade_id'
706 ||' AND pg.grade_definition_id = pgd.grade_definition_id';
707 ELSE
708 RETURN null;
709 END IF;
710 l_dynamic_sql := l_dynamic_sql || ' ORDER BY 2';
711 RETURN l_dynamic_sql;
712 END build_grade_sql;
713 -- ---------------------------------------------------------------------------
714 -- build_sql
715 -- ---------------------------------------------------------------------------
716
717 FUNCTION build_sql
718 (p_search_type in varchar2
719 ,p_ids in varchar2)
720 RETURN varchar2 IS
721
722 l_dynamic_sql varchar2(32000);
723 l_flex_select_clause varchar2(2000);
724 l_flex_where_clause varchar2(2000);
725
726 l_business_group varchar2(2000);
727
728 BEGIN
729
730 hr_util_misc_web.validate_session(p_person_id => c_person_id);
731 c_legislation_code := hr_misc_web.get_legislation_code
732 (p_person_id => c_person_id);
733
734 l_business_group := hr_util_misc_web.get_business_group_id;
735
736 IF p_search_type = g_location_type THEN --location
737 l_dynamic_sql := 'SELECT location_id, location_code'
738 ||' FROM hr_locations_all'
739 ||' WHERE location_id IN ('
740 ||p_ids
741 ||')';
742 ELSIF p_search_type = g_organization_type THEN --org
743 l_dynamic_sql := 'SELECT organization_id, name'
744 ||' FROM hr_organization_units'
745 ||' WHERE organization_id IN ('
746 ||p_ids
747 ||')';
748 ELSIF p_search_type = g_job_type THEN --job
749
750 keyflex_select_where_clause
751 (p_business_group_id => l_business_group
752 ,p_keyflex_code => 'JOB'
753 ,p_select_clause => l_flex_select_clause
754 ,p_where_clause => l_flex_where_clause);
755
756 l_dynamic_sql := 'SELECT pj.job_id,'
757 ||l_flex_select_clause
758 ||' FROM per_jobs_vl pj, per_job_definitions pjd'
759 ||' WHERE pj.job_definition_id = pjd.job_definition_id'
760 ||' AND pj.job_id IN ('
761 ||p_ids
762 ||')';
763 ELSIF p_search_type = g_position_type THEN
764
765 keyflex_select_where_clause
766 (p_business_group_id => l_business_group
767 ,p_keyflex_code => 'POS'
768 ,p_select_clause => l_flex_select_clause
769 ,p_where_clause => l_flex_where_clause);
770
771 l_dynamic_sql := 'SELECT pp.position_id,'
772 ||l_flex_select_clause
773 ||' FROM hr_positions_f pp, per_position_definitions ppd'
774 ||' WHERE TRUNC(SYSDATE) BETWEEN pp.effective_start_date'
775 ||' AND pp.effective_end_date'
776 ||' AND pp.position_definition_id = ppd.position_definition_id'
777 ||' AND pp.position_id IN ('
778 ||p_ids
779 ||')';
780 ELSIF p_search_type = g_vacancy_type THEN --vacancy
781 l_dynamic_sql := 'SELECT pv.vacancy_id'
782 ||',pv.name || '' (''||pr.name||'')'' name'
783 ||' FROM per_vacancies pv, per_requisitions pr'
784 ||' WHERE pv.vacancy_id IN ('
785 ||p_ids
786 ||')'
787 ||' AND pv.requisition_id = pr.requisition_id';
788 ELSIF p_search_type = g_grade_type THEN --grade
789
790 keyflex_select_where_clause
791 (p_business_group_id => l_business_group
792 ,p_keyflex_code => 'GRD'
793 ,p_select_clause => l_flex_select_clause
794 ,p_where_clause => l_flex_where_clause);
795
796 l_dynamic_sql := 'SELECT pg.grade_id,'
797 ||l_flex_select_clause
798 ||' FROM per_grades pg, per_grade_definitions pgd'
799 ||' WHERE pg.grade_definition_id = pgd.grade_definition_id'
800 ||' AND pg.grade_id IN ('
801 ||p_ids
802 ||')';
803 ELSIF p_search_type = g_class_type THEN --class
804 l_dynamic_sql := 'SELECT activity_version_id, version_name'
805 ||' FROM ota_activity_versions'
806 ||' WHERE activity_version_id IN ('
807 ||p_ids
808 ||')';
809 ELSIF p_search_type = g_people_type THEN --people
810 l_dynamic_sql := 'SELECT ppf.person_id';
811 l_dynamic_sql := l_dynamic_sql
812 ||',ppf.full_name name';
813 l_dynamic_sql := l_dynamic_sql
814 ||' FROM per_people_f ppf'
815 ||' WHERE ppf.person_id IN ('
816 ||p_ids
817 ||')';
818 ELSE
819 RETURN null;
820 END IF;
821
822 l_dynamic_sql := l_dynamic_sql || ' ORDER BY 2';
823 RETURN l_dynamic_sql;
824
825 END build_sql;
826
827 FUNCTION get_system_person_type(p_person_type_id in number)
828 RETURN varchar2 IS
829
830 CURSOR csr_sys_person_type IS
831 SELECT system_person_type
832 FROM per_person_types
833 WHERE person_type_id = p_person_type_id;
834
835 l_sys_person_type varchar2(2000);
836
837 BEGIN
838
839 OPEN csr_sys_person_type;
840 FETCH csr_sys_person_type INTO l_sys_person_type;
841 CLOSE csr_sys_person_type;
842
843 RETURN l_sys_person_type;
844
845 END get_system_person_type;
846 -- ---------------------------------------------------------------------------
847 -- build_sql
848 -- ---------------------------------------------------------------------------
849
850 FUNCTION build_sql
851 (p_mode in varchar2
852 ,p_person_type_id in varchar2 default null
853 ,p_assignment_type in varchar2 default null
854 ,p_pre_search_type in varchar2
855 ,p_pre_search_ids in varchar2
856 ,p_search_type in varchar2
857 ,p_filer_match in varchar2
858 ,p_search_criteria in varchar2)
859 RETURN varchar2 IS
860
861
862 CURSOR csr_all_emp_person_type_id(p_business_group_id in number) IS
863 SELECT person_type_id
864 FROM per_person_types
865 WHERE (system_person_type = 'EMP' )
866 AND business_group_id = p_business_group_id;
867
868 CURSOR csr_all_apl_person_type_id(p_business_group_id in number) IS
869 SELECT person_type_id
870 FROM per_person_types
871 WHERE system_person_type = 'APL'
872 AND business_group_id = p_business_group_id;
873
874
875
876 l_dynamic_sql varchar2(32000);
877 l_filter_clause varchar2(2000);
878 l_business_group varchar2(2000);
879 l_job_flex_select_clause varchar2(2000);
880 l_job_flex_where_clause varchar2(2000);
881 l_pos_flex_select_clause varchar2(2000);
882 l_pos_flex_where_clause varchar2(2000);
883 l_grd_flex_select_clause varchar2(2000);
884 l_grd_flex_where_clause varchar2(2000);
885
886 l_assignment_type varchar2(2000);
887 l_sys_person_type varchar2(2000);
888 l_person_type_ids varchar2(2000);
889
890
891 BEGIN
892
893 l_business_group := hr_util_misc_web.get_business_group_id;
894
895 l_filter_clause := hr_suit_match_utility_web.process_filter
896 (p_filter_match => p_filer_match
897 ,p_search_criteria => p_search_criteria);
898
899 keyflex_select_where_clause
900 (p_business_group_id => l_business_group
901 ,p_keyflex_code => 'JOB'
902 ,p_filter_clause => l_filter_clause
903 ,p_select_clause => l_job_flex_select_clause
904 ,p_where_clause => l_job_flex_where_clause);
905
906 keyflex_select_where_clause
907 (p_business_group_id => l_business_group
908 ,p_keyflex_code => 'POS'
909 ,p_filter_clause => l_filter_clause
910 ,p_select_clause => l_pos_flex_select_clause
911 ,p_where_clause => l_pos_flex_where_clause);
912
913 keyflex_select_where_clause
914 (p_business_group_id => l_business_group
915 ,p_keyflex_code => 'GRD'
916 ,p_filter_clause => l_filter_clause
917 ,p_select_clause => l_grd_flex_select_clause
918 ,p_where_clause => l_grd_flex_where_clause);
919
920 IF p_mode = g_person_search_mode THEN
921 IF p_person_type_id = '-1' THEN --all employees
922 l_person_type_ids := '(-1'; --'-1' is dummy number;
923 FOR v_person_type_id IN csr_all_emp_person_type_id(l_business_group) LOOP
924 l_person_type_ids := l_person_type_ids ||','
925 ||v_person_type_id.person_type_id;
926 END LOOP;
927 l_person_type_ids := l_person_type_ids ||')';
928 l_assignment_type := 'paf.assignment_type';
929 ELSIF p_person_type_id = '-2' THEN --all applicants
930 l_person_type_ids := '(-1'; --'-1' is dummy number;
931 FOR v_person_type_id IN csr_all_apl_person_type_id(l_business_group) LOOP
932 l_person_type_ids := l_person_type_ids ||','
933 ||v_person_type_id.person_type_id;
934 END LOOP;
935 l_person_type_ids := l_person_type_ids ||')';
936 l_assignment_type := 'paf.assignment_type';
937 ELSE
938 l_person_type_ids := '('||p_person_type_id||')';
939 l_sys_person_type := get_system_person_type(p_person_type_id);
940 IF l_sys_person_type = 'EMP_APL' THEN
941 l_assignment_type := 'paf.assignment_type';
942 ELSE
943 l_assignment_type := ''''|| p_assignment_type ||'''';
944 END IF;
945 END IF;
946 IF p_search_type = g_location_type THEN
947 l_dynamic_sql := 'SELECT DISTINCT(paf.location_id) id'
948 ||' ,hl.location_code name'
949 ||' FROM per_assignments_f paf'
950 ||' ,per_people_f ppf'
951 ||' ,per_person_type_usages_f ptu'
952 ||' ,hr_locations_all hl'
953 ||' WHERE paf.business_group_id = '
954 ||l_business_group
955 ||' AND paf.assignment_type = '||l_assignment_type
956 ||' AND ppf.person_id = ptu.person_id '
957 ||' AND ppf.person_type_id IN '||l_person_type_ids
958 ||' AND TRUNC(sysdate) BETWEEN ptu.effective_start_date '
959 ||' AND ptu.effective_end_date'
960 ||' AND TRUNC(sysdate) BETWEEN ppf.effective_start_date'
961 ||' AND ppf.effective_end_date'
962 ||' AND ppf.person_id = paf.person_id'
963 ||' AND TRUNC(sysdate) BETWEEN paf.effective_start_date'
964 ||' AND paf.effective_end_date'
965 ||' AND paf.location_id = hl.location_id'
966 ||' AND UPPER(hl.location_code) '
967 || l_filter_clause;
968 IF p_pre_search_type IS NOT null THEN
969 IF p_pre_search_type = g_organization_type THEN
970 l_dynamic_sql := l_dynamic_sql
971 || ' AND paf.organization_id IN '
972 || '('||p_pre_search_ids||')';
973 ELSIF p_pre_search_type = g_job_type THEN
974 l_dynamic_sql := l_dynamic_sql
975 || ' AND paf.job_id IN '
976 || '('||p_pre_search_ids||')';
977 ELSIF p_pre_search_type = g_grade_type THEN
978 l_dynamic_sql := l_dynamic_sql
979 || ' AND paf.grade_id IN '
980 || '('||p_pre_search_ids||')';
981 END IF;
982 END IF;
983 ELSIF p_search_type = g_organization_type THEN
984 l_dynamic_sql := 'SELECT DISTINCT(paf.organization_id) id'
985 ||' ,hou.name name'
986 ||' FROM per_assignments_f paf'
987 ||' ,per_people_f ppf'
988 ||' ,per_person_type_usages_f ptu'
989 ||' ,hr_organization_units hou'
990 ||' WHERE paf.business_group_id = '
991 ||l_business_group
992 ||' AND paf.assignment_type = '||l_assignment_type
993 ||' AND ppf.person_id = ptu.person_id '
994 ||' AND ppf.person_type_id IN '||l_person_type_ids
995 ||' AND TRUNC(sysdate) between ptu.effective_start_date'
996 ||' AND ptu.effective_end_date'
997 ||' AND TRUNC(sysdate) BETWEEN paf.effective_start_date'
998 ||' AND paf.effective_end_date'
999 ||' AND ppf.person_id = paf.person_id'
1000 ||' AND TRUNC(sysdate) BETWEEN ppf.effective_start_date'
1001 ||' AND ppf.effective_end_date'
1002 ||' AND paf.organization_id = hou.organization_id'
1003 ||' AND UPPER(hou.name) '
1004 || l_filter_clause;
1005 IF p_pre_search_type IS NOT null THEN
1006 IF p_pre_search_type = g_location_type THEN
1007 l_dynamic_sql := l_dynamic_sql
1008 || ' AND paf.location_id IN '
1009 || '('||p_pre_search_ids||')';
1010 ELSIF p_pre_search_type = g_job_type THEN
1011 l_dynamic_sql := l_dynamic_sql
1012 || ' AND paf.job_id IN '
1013 || '('||p_pre_search_ids||')';
1014 ELSIF p_pre_search_type = g_grade_type THEN
1015 l_dynamic_sql := l_dynamic_sql
1016 || ' AND paf.grade_id IN '
1017 || '('||p_pre_search_ids||')';
1018 END IF;
1019 END IF;
1020 ELSIF p_search_type = g_job_type THEN
1021 l_dynamic_sql := 'SELECT DISTINCT(paf.job_id) id'
1022 ||','|| l_job_flex_select_clause
1023 ||' FROM per_assignments_f paf'
1024 ||' ,per_people_f ppf'
1025 ||' ,per_person_type_usages_f ptu'
1026 ||' ,per_jobs_vl pj'
1027 ||' ,per_job_definitions pjd'
1028 ||' WHERE paf.business_group_id = '
1029 ||l_business_group
1030 ||' AND paf.assignment_type = '||l_assignment_type
1031 ||' AND ppf.person_id = ptu.person_id '
1032 ||' AND ppf.person_type_id IN '||l_person_type_ids
1033 ||' AND TRUNC(sysdate) between ptu.effective_start_date'
1034 ||' AND ptu.effective_end_date'
1035 ||' AND TRUNC(sysdate) BETWEEN paf.effective_start_date'
1036 ||' AND paf.effective_end_date'
1037 ||' AND ppf.person_id = paf.person_id'
1038 ||' AND TRUNC(sysdate) BETWEEN ppf.effective_start_date'
1039 ||' AND ppf.effective_end_date'
1040 ||' AND paf.job_id = pj.job_id'
1041 ||' AND pj.job_definition_id = pjd.job_definition_id'
1042 ||' AND '
1043 ||l_job_flex_where_clause;
1044 IF p_pre_search_type IS NOT null THEN
1045 IF p_pre_search_type = g_location_type THEN
1046 l_dynamic_sql := l_dynamic_sql
1047 || ' AND paf.location_id IN '
1048 || '('||p_pre_search_ids||')';
1049 ELSIF p_pre_search_type = g_organization_type THEN
1050 l_dynamic_sql := l_dynamic_sql
1051 || ' AND paf.organization_id IN '
1052 || '('||p_pre_search_ids||')';
1053 ELSIF p_pre_search_type = g_grade_type THEN
1054 l_dynamic_sql := l_dynamic_sql
1055 || ' AND paf.grade_id IN '
1056 || '('||p_pre_search_ids||')';
1057 END IF;
1058 END IF;
1059
1060 ELSIF p_search_type = g_position_type THEN
1061 l_dynamic_sql := 'SELECT DISTINCT(paf.position_id) id'
1062 ||','|| l_pos_flex_select_clause
1063 ||' FROM per_assignments_f paf'
1064 ||' ,per_people_f ppf'
1065 ||' ,per_person_type_usages_f ptu'
1066 ||' ,hr_positions_f pp'
1067 ||' ,per_position_definitions ppd'
1068 ||' WHERE paf.business_group_id = '
1069 ||l_business_group
1070 ||' AND paf.assignment_type = '||l_assignment_type
1071 ||' AND ppf.person_id = ptu.person_id '
1072 ||' AND ppf.person_type_id IN '||l_person_type_ids
1073 ||' AND TRUNC(sysdate) between ptu.effective_start_date'
1074 ||' AND ptu.effective_end_date'
1075 ||' AND TRUNC(sysdate) BETWEEN paf.effective_start_date'
1076 ||' AND paf.effective_end_date'
1077 ||' AND ppf.person_id = paf.person_id'
1078 ||' AND TRUNC(sysdate) BETWEEN ppf.effective_start_date'
1079 ||' AND ppf.effective_end_date'
1080 ||' AND paf.position_id = pp.position_id'
1081 ||' AND TRUNC(SYSDATE) BETWEEN pp.effective_start_date'
1082 ||' AND pp.effective_end_date'
1083 ||' AND pp.position_definition_id = ppd.position_definition_id'
1084 ||' AND (pp.status is null OR pp.status <> ''INVALID'')'
1085 ||' AND '
1086 ||l_pos_flex_where_clause;
1087 IF p_pre_search_type IS NOT null THEN
1088 IF p_pre_search_type = g_location_type THEN
1089 l_dynamic_sql := l_dynamic_sql
1090 || ' AND paf.location_id IN '
1091 || '('||p_pre_search_ids||')';
1092 ELSIF p_pre_search_type = g_organization_type THEN
1093 l_dynamic_sql := l_dynamic_sql
1094 || ' AND paf.organization_id IN '
1095 || '('||p_pre_search_ids||')';
1096 ELSIF p_pre_search_type = g_job_type THEN
1097 l_dynamic_sql := l_dynamic_sql
1098 || ' AND paf.job_id IN '
1099 || '('||p_pre_search_ids||')';
1100 ELSIF p_pre_search_type = g_grade_type THEN
1101 l_dynamic_sql := l_dynamic_sql
1102 || ' AND paf.grade_id IN '
1103 || '('||p_pre_search_ids||')';
1104 END IF;
1105 END IF;
1106 ELSIF p_search_type = g_vacancy_type THEN
1107 l_dynamic_sql := 'SELECT DISTINCT(paf.vacancy_id) id'
1108 ||',pv.name || '' (''||pr.name||'')'' name'
1109 ||' FROM per_assignments_f paf'
1110 ||' ,per_people_f ppf'
1111 ||' ,per_person_type_usages_f ptu'
1112 ||' ,per_vacancies pv'
1113 ||' ,per_requisitions pr'
1114 ||' WHERE paf.business_group_id = '
1115 ||l_business_group
1116 ||' AND paf.assignment_type = '||l_assignment_type
1117 ||' AND ppf.person_id = ptu.person_id '
1118 ||' AND ppf.person_type_id IN '||l_person_type_ids
1119 ||' AND TRUNC(sysdate) between ptu.effective_start_date'
1120 ||' AND ptu.effective_end_date'
1121 ||' AND TRUNC(sysdate) BETWEEN paf.effective_start_date'
1122 ||' AND paf.effective_end_date'
1123 ||' AND ppf.person_id = paf.person_id'
1124 ||' AND TRUNC(sysdate) BETWEEN ppf.effective_start_date'
1125 ||' AND ppf.effective_end_date'
1126 ||' AND paf.vacancy_id = pv.vacancy_id'
1127 ||' AND pv.requisition_id = pr.requisition_id'
1128 ||' AND UPPER(pv.name) '
1129 || l_filter_clause;
1130 IF p_pre_search_type IS NOT null THEN
1131 IF p_pre_search_type = g_location_type THEN
1132 l_dynamic_sql := l_dynamic_sql
1133 || ' AND paf.location_id IN '
1134 || '('||p_pre_search_ids||')';
1135 ELSIF p_pre_search_type = g_organization_type THEN
1136 l_dynamic_sql := l_dynamic_sql
1137 || ' AND paf.organization_id IN '
1138 || '('||p_pre_search_ids||')';
1139 ELSIF p_pre_search_type = g_job_type THEN
1140 l_dynamic_sql := l_dynamic_sql
1141 || ' AND paf.job_id IN '
1142 || '('||p_pre_search_ids||')';
1143 ELSIF p_pre_search_type = g_position_type THEN
1144 l_dynamic_sql := l_dynamic_sql
1145 || ' AND paf.position_id IN '
1146 || '('||p_pre_search_ids||')';
1147 ELSIF p_pre_search_type = g_grade_type THEN
1148 l_dynamic_sql := l_dynamic_sql
1149 || ' AND paf.grade_id IN '
1150 || '('||p_pre_search_ids||')';
1151 END IF;
1152 END IF;
1153 ELSIF p_search_type = g_grade_type THEN
1154 l_dynamic_sql := 'SELECT DISTINCT(paf.grade_id) id'
1155 ||','|| l_grd_flex_select_clause
1156 ||' FROM per_assignments_f paf'
1157 ||' ,per_people_f ppf'
1158 ||' ,per_person_type_usages_f ptu'
1159 ||' ,per_grades pg'
1160 ||' ,per_grade_definitions pgd'
1161 ||' WHERE paf.business_group_id = '
1162 ||l_business_group
1163 ||' AND paf.assignment_type = '||l_assignment_type
1164 ||' AND ppf.person_id = ptu.person_id '
1165 ||' AND ppf.person_type_id IN '||l_person_type_ids
1166 ||' AND TRUNC(sysdate) between ptu.effective_start_date'
1167 ||' AND ptu.effective_end_date'
1168 ||' AND TRUNC(sysdate) BETWEEN paf.effective_start_date'
1169 ||' AND paf.effective_end_date'
1170 ||' AND ppf.person_id = paf.person_id'
1171 ||' AND TRUNC(sysdate) BETWEEN ppf.effective_start_date'
1172 ||' AND ppf.effective_end_date'
1173 ||' AND paf.grade_id = pg.grade_id'
1174 ||' AND pg.grade_definition_id = pgd.grade_definition_id'
1175 ||' AND '
1176 ||l_grd_flex_where_clause;
1177 IF p_pre_search_type IS NOT null THEN
1178 IF p_pre_search_type = g_location_type THEN
1179 l_dynamic_sql := l_dynamic_sql
1180 || ' AND paf.location_id IN '
1181 || '('||p_pre_search_ids||')';
1182 ELSIF p_pre_search_type = g_organization_type THEN
1183 l_dynamic_sql := l_dynamic_sql
1184 || ' AND paf.organization_id IN '
1185 || '('||p_pre_search_ids||')';
1186 ELSIF p_pre_search_type = g_job_type THEN
1187 l_dynamic_sql := l_dynamic_sql
1188 || ' AND paf.job_id IN '
1189 || '('||p_pre_search_ids||')';
1190 ELSIF p_pre_search_type = g_position_type THEN
1191 l_dynamic_sql := l_dynamic_sql
1192 || ' AND paf.position_id IN '
1193 || '('||p_pre_search_ids||')';
1194 ELSIF p_pre_search_type = g_vacancy_type THEN
1195 l_dynamic_sql := l_dynamic_sql
1196 || ' AND paf.vacancy_id IN '
1197 || '('||p_pre_search_ids||')';
1198 END IF;
1199 END IF;
1200 END IF;
1201 ELSE
1202 IF p_search_type = g_location_type THEN
1203 l_dynamic_sql := 'SELECT location_id, location_code'
1204 ||' FROM hr_locations_all'
1205 ||' WHERE UPPER(location_code) '
1206 || l_filter_clause;
1207 ELSIF p_search_type = g_organization_type THEN
1208 l_dynamic_sql := 'SELECT distinct(hou.organization_id), hou.name'
1209 ||' FROM hr_organization_units hou,'
1210 ||' hr_organization_information hoi'
1211 ||' WHERE hou.business_group_id = '
1212 ||l_business_group
1213 ||' AND TRUNC(sysdate) BETWEEN hou.date_from AND'
1214 ||' NVL(hou.date_to, TRUNC(sysdate))'
1215 ||' AND hou.organization_id = hoi.organization_id'
1216 ||' AND hoi.org_information_context = ''CLASS'''
1217 ||' AND hoi.org_information1 IN (''HR_BG'',''HR_ORG'')'
1218 ||' AND UPPER(hou.name) '
1219 || l_filter_clause;
1220 IF p_pre_search_type IS NOT null THEN
1221 IF p_pre_search_type = g_location_type THEN
1222 l_dynamic_sql := l_dynamic_sql
1223 || ' AND location_id IN '
1224 || '('||p_pre_search_ids||')';
1225 END IF;
1226 END IF;
1227 ELSIF p_search_type = g_job_type THEN --job
1228 l_dynamic_sql := 'SELECT job_id, name'
1229 ||' FROM per_jobs_vl'
1230 ||' WHERE business_group_id = '
1231 ||l_business_group
1232 ||' AND TRUNC(sysdate) BETWEEN date_from AND'
1233 ||' NVL(date_to,TRUNC(sysdate))'
1234 ||' AND UPPER(name) '
1235 || l_filter_clause;
1236 ELSIF p_search_type = g_position_type THEN
1237 l_dynamic_sql := 'SELECT position_id, name'
1238 ||' FROM hr_positions_f'
1239 ||' WHERE business_group_id = '
1240 ||l_business_group
1241 ||' AND TRUNC(sysdate) BETWEEN effective_start_date'
1242 ||' AND effective_end_date'
1243 ||' AND TRUNC(sysdate) BETWEEN date_effective AND'
1244 ||' NVL(date_end, TRUNC(sysdate))'
1245 ||' AND (status is null OR status <> ''INVALID'')'
1246 ||' AND UPPER(name) '
1247 || l_filter_clause;
1248 IF p_pre_search_type IS NOT null THEN
1249 IF p_pre_search_type = g_location_type THEN
1250 l_dynamic_sql := l_dynamic_sql
1251 || ' AND location_id IN '
1252 || '('||p_pre_search_ids||')';
1253 ELSIF p_pre_search_type = g_job_type THEN
1254 l_dynamic_sql := l_dynamic_sql
1255 || ' AND job_id IN '
1256 || '('||p_pre_search_ids||')';
1257 ELSIF p_pre_search_type = g_organization_type THEN
1258 l_dynamic_sql := l_dynamic_sql
1259 || ' AND organization_id IN '
1260 || '('||p_pre_search_ids||')';
1261 END IF;
1262 END IF;
1263 ELSIF p_search_type = g_vacancy_type THEN
1264 l_dynamic_sql := 'SELECT pv.vacancy_id'
1265 ||',pv.name || '' (''||pr.name||'')'' name'
1266 ||' FROM per_vacancies pv, per_requisitions pr'
1267 ||' WHERE pv.business_group_id = '
1268 ||l_business_group
1269 ||' AND TRUNC(sysdate) BETWEEN pv.date_from AND'
1270 ||' NVL(pv.date_to, TRUNC(sysdate))'
1271 ||' AND UPPER(pv.name) '
1272 || l_filter_clause
1273 ||' AND pv.requisition_id = pr.requisition_id';
1274 IF p_pre_search_type IS NOT null THEN
1275 IF p_pre_search_type = g_location_type THEN
1276 l_dynamic_sql := l_dynamic_sql
1277 || ' AND location_id IN '
1278 || '('||p_pre_search_ids||')';
1279 ELSIF p_pre_search_type = g_job_type THEN -- job
1280 l_dynamic_sql := l_dynamic_sql
1281 || ' AND job_id IN '
1282 || '('||p_pre_search_ids||')';
1283 ELSIF p_pre_search_type = g_organization_type THEN
1284 l_dynamic_sql := l_dynamic_sql
1285 || ' AND organization_id IN '
1286 || '('||p_pre_search_ids||')';
1287 ELSIF p_pre_search_type = g_position_type THEN
1288 l_dynamic_sql := l_dynamic_sql
1289 || ' AND position_id IN '
1290 || '('||p_pre_search_ids||')';
1291 END IF;
1292 END IF;
1293 ELSE
1294 RETURN null;
1295 END IF;
1296 END IF;
1297
1298 l_dynamic_sql := l_dynamic_sql || ' ORDER BY 2';
1299
1300 RETURN l_dynamic_sql;
1301
1302 END build_sql;
1303
1304 -- ---------------------------------------------------------------------------
1305 -- get_id_name
1306 -- ---------------------------------------------------------------------------
1307
1308 PROCEDURE get_id_name
1309 (p_dynamic_sql in varchar2
1310 ,p_id out nocopy hr_util_misc_web.g_varchar2_tab_type
1311 ,p_name out nocopy hr_util_misc_web.g_varchar2_tab_type
1312 ,p_count out nocopy number) IS
1313
1314 l_sql_cursor integer;
1315 l_rows integer;
1316 l_index number;
1317 c_id number;
1318 c_name varchar2(240);
1319
1320 BEGIN
1321 l_sql_cursor := dbms_sql.open_cursor;
1322 dbms_sql.parse(l_sql_cursor, p_dynamic_sql, dbms_sql.v7);
1323 dbms_sql.define_column(l_sql_cursor, 1, c_id, 15);
1324 dbms_sql.define_column(l_sql_cursor, 2, c_name, 240);
1325 l_rows := dbms_sql.execute(l_sql_cursor);
1326 l_index := 0;
1327 WHILE dbms_sql.fetch_rows(l_sql_cursor) > 0 LOOP
1328 l_index := l_index + 1;
1329 dbms_sql.column_value(l_sql_cursor, 1, p_id(l_index));
1330 dbms_sql.column_value(l_sql_cursor, 2, p_name(l_index));
1331 END LOOP;
1332 p_count := l_index;
1333
1334 dbms_sql.close_cursor(l_sql_cursor);
1335
1336 EXCEPTION
1337 WHEN others THEN
1338 IF dbms_sql.is_open(l_sql_cursor) THEN
1339 dbms_sql.close_cursor(l_sql_cursor);
1340 END IF;
1341 END get_id_name;
1342
1343 -- ---------------------------------------------------------------------------
1344 -- get_header
1345 -- ---------------------------------------------------------------------------
1346
1347 PROCEDURE get_job_info
1348 (p_search_type in varchar2
1349 ,p_id in varchar2
1350 ,p_name out nocopy varchar2
1351 ,p_org_name out nocopy varchar2
1352 ,p_location_code out nocopy varchar2) IS
1353
1354 CURSOR csr_org_info IS
1355 SELECT hou.name, hl.location_code
1356 FROM hr_organization_units hou,
1357 hr_locations_all hl
1358 WHERE hou.organization_id = p_id
1359 AND hou.location_id = hl.location_id(+);
1360
1361 CURSOR csr_pos_info IS
1362 SELECT hou.name, hl.location_code
1363 FROM hr_positions_f pp,
1364 hr_organization_units hou,
1365 hr_locations_all hl
1366 WHERE pp.position_id = p_id
1367 AND TRUNC(SYSDATE) BETWEEN pp.effective_start_date
1368 AND pp.effective_end_date
1369 AND pp.organization_id = hou.organization_id
1370 AND hou.location_id = hl.location_id(+);
1371
1372 CURSOR csr_vac_info IS
1373 SELECT pv.name || ' (' || pr.name ||')'
1374 ,hou.name
1375 ,hl.location_code
1376 FROM per_vacancies pv,
1377 per_requisitions pr,
1378 hr_organization_units hou,
1379 hr_locations_all hl
1380 WHERE pv.vacancy_id = p_id
1381 AND pv.requisition_id = pr.requisition_id
1382 AND pv.organization_id = hou.organization_id(+)
1383 AND hou.location_id = hl.location_id(+);
1384
1385 l_dynamic_sql varchar2(32000);
1386 l_id hr_util_misc_web.g_varchar2_tab_type;
1387 l_name hr_util_misc_web.g_varchar2_tab_type;
1388 l_count number;
1389
1390 BEGIN
1391
1392 IF p_search_type IS null THEN
1393 return;
1394 END IF;
1395
1396 l_dynamic_sql := hr_suit_match_utility_web.build_sql
1397 (p_search_type => p_search_type
1398 ,p_ids => p_id);
1399
1400 IF p_search_type = g_organization_type THEN
1401 OPEN csr_org_info;
1402 FETCH csr_org_info INTO p_name, p_location_code;
1403 CLOSE csr_org_info;
1404 ELSIF p_search_type = g_position_type THEN
1405 hr_suit_match_utility_web.get_id_name
1406 (p_dynamic_sql => l_dynamic_sql
1407 ,p_id => l_id
1408 ,p_name => l_name
1409 ,p_count => l_count);
1410 IF l_count > 0 THEN
1411 p_name := l_name(1);
1412 OPEN csr_pos_info;
1413 FETCH csr_pos_info INTO p_org_name, p_location_code;
1414 CLOSE csr_pos_info;
1415 END IF;
1416 ELSIF p_search_type = g_job_type THEN
1417 hr_suit_match_utility_web.get_id_name
1418 (p_dynamic_sql => l_dynamic_sql
1419 ,p_id => l_id
1420 ,p_name => l_name
1421 ,p_count => l_count);
1422 IF l_count > 0 THEN
1423 p_name := l_name(1);
1424 END IF;
1425 ELSIF p_search_type = g_vacancy_type THEN
1426 OPEN csr_vac_info;
1427 FETCH csr_vac_info INTO p_name, p_org_name, p_location_code;
1428 CLOSE csr_vac_info;
1429 END IF;
1430
1431 END get_job_info;
1432 FUNCTION process_filter
1433 (p_filter_match in varchar2
1434 ,p_search_criteria in varchar2)
1435 RETURN varchar2 IS
1436
1437 l_filter_clause varchar2(2000);
1438 l_search_criteria varchar2(2000);
1439
1440 BEGIN
1441
1442 l_search_criteria := REPLACE(p_search_criteria, '''', '''''');
1443
1444 IF p_filter_match = 1 THEN --is
1445 l_filter_clause := 'like '''||UPPER(l_search_criteria)||'''';
1446 ELSIF p_filter_match = 2 THEN --is not
1447 IF p_search_criteria is null THEN
1448 l_filter_clause := 'is not null';
1449 ELSE
1450 l_filter_clause := 'not like '''||UPPER(l_search_criteria)||'''';
1451 END IF;
1452 ELSIF p_filter_match = 3 THEN --contains
1453 l_filter_clause := 'like '''||'%'||UPPER(l_search_criteria)||'%''';
1454 ELSIF p_filter_match = 4 THEN --starts with
1455 l_filter_clause := 'like '''||UPPER(l_search_criteria)||'%''';
1456 ELSIF p_filter_match = 5 THEN --ends with
1457 l_filter_clause := 'like '''||'%'||UPPER(l_search_criteria)||'''';
1458 END IF;
1459
1460 RETURN l_filter_clause;
1461
1462 END process_filter;
1463
1464 -- ---------------------------------------------------------------------------
1465 -- get_vac_competencies
1466 -- ---------------------------------------------------------------------------
1467
1468 PROCEDURE get_vac_competencies
1469 (p_vacancy_id in number
1470 ,p_effective_date in date default sysdate
1471 ,p_include_core_competencies in varchar2 default 'N'
1472 ,p_competence_table out nocopy g_competence_table
1473 ,p_competence_count out nocopy number
1474 ,p_essential_count out nocopy number
1475 ,p_desirable_count out nocopy number) IS
1476
1477 CURSOR csr_vacancy IS
1478 SELECT business_group_id
1479 ,organization_id
1480 ,job_id
1481 ,position_id
1482 ,grade_id
1483 FROM per_vacancies
1484 WHERE vacancy_id = p_vacancy_id;
1485
1486 l_vacancy_rec csr_vacancy%rowtype;
1487
1488 BEGIN
1489
1490 OPEN csr_vacancy;
1491 FETCH csr_vacancy INTO l_vacancy_rec;
1492 CLOSE csr_vacancy;
1493
1494 IF l_vacancy_rec.position_id IS NOT null THEN
1495 get_all_pos_competencies(p_pos_id => l_vacancy_rec.position_id
1496 ,p_grade_id => l_vacancy_rec.grade_id
1497 ,p_include_core_competencies => p_include_core_competencies
1498 ,p_competence_table => p_competence_table
1499 ,p_competence_count => p_competence_count
1500 ,p_essential_count => p_essential_count
1501 ,p_desirable_count => p_desirable_count);
1502 ELSIF l_vacancy_rec.job_id IS NOT null THEN
1503 get_job_competencies(p_job_id => l_vacancy_rec.job_id
1504 ,p_grade_id => l_vacancy_rec.grade_id
1505 ,p_include_core_competencies => p_include_core_competencies
1506 ,p_competence_table => p_competence_table
1507 ,p_competence_count => p_competence_count
1508 ,p_essential_count => p_essential_count
1509 ,p_desirable_count => p_desirable_count);
1510 ELSIF l_vacancy_rec.organization_id IS NOT null THEN
1511 get_org_competencies(p_org_id => l_vacancy_rec.organization_id
1512 ,p_include_core_competencies => p_include_core_competencies
1513 ,p_competence_table => p_competence_table
1514 ,p_competence_count => p_competence_count
1515 ,p_essential_count => p_essential_count
1516 ,p_desirable_count => p_desirable_count);
1517 ELSIF l_vacancy_rec.business_group_id IS NOT null THEN
1518 get_org_competencies(p_org_id => l_vacancy_rec.business_group_id
1519 ,p_include_core_competencies => p_include_core_competencies
1520 ,p_competence_table => p_competence_table
1521 ,p_competence_count => p_competence_count
1522 ,p_essential_count => p_essential_count
1523 ,p_desirable_count => p_desirable_count);
1524 END IF;
1525
1526 END get_vac_competencies;
1527
1528 -- ---------------------------------------------------------------------------
1529 -- get_core_competencies
1530 -- ---------------------------------------------------------------------------
1531
1532 PROCEDURE get_core_competencies
1533 (p_business_group_id in number default null
1534 ,p_effective_date in date default sysdate
1535 ,p_competence_table out nocopy g_competence_table
1536 ,p_competence_count out nocopy number
1537 ,p_essential_count out nocopy number
1538 ,p_desirable_count out nocopy number) IS
1539
1540 CURSOR csr_competencies_by_core(p_core_id in number) IS
1541 SELECT pce.competence_element_id,
1542 pce.effective_date_from,
1543 pce.proficiency_level_id,
1544 pce.high_proficiency_level_id,
1545 pce.mandatory,
1546 cpl.competence_id,
1547 cpl.name,
1548 r1.step_value low_step_value,
1549 r2.step_value high_step_value,
1550 rtx1.name low_step_name,
1551 rtx2.name high_step_name
1552 FROM per_competence_elements pce,
1553 per_competences_tl cpl,
1554 per_rating_levels r1,
1555 per_rating_levels r2,
1556 per_rating_levels_tl rtx1,
1557 per_rating_levels_tl rtx2
1558 WHERE pce.ENTERPRISE_ID = p_core_id
1559 AND pce.type = 'REQUIREMENT'
1560 AND pce.organization_id IS null
1561 AND pce.job_id IS null
1562 AND pce.position_id IS null
1563 AND p_effective_date BETWEEN pce.effective_date_from AND
1564 NVL(pce.effective_date_to, p_effective_date)
1565 AND pce.competence_id = cpl.competence_id
1566 AND cpl.language = userenv('LANG')
1567 AND pce.proficiency_level_id = r1.rating_level_id(+)
1568 AND pce.high_proficiency_level_id = r2.rating_level_id(+)
1569 AND pce.proficiency_level_id = rtx1.rating_level_id(+)
1570 AND pce.high_proficiency_level_id = rtx2.rating_level_id(+)
1571 AND rtx1.language(+) = userenv('LANG')
1572 AND rtx2.language(+) = userenv('LANG')
1573 ORDER BY cpl.name;
1574
1575 l_business_group_id hr_organization_units.business_group_id%type;
1576
1577 l_core_competence_table g_competence_table;
1578 l_core_count number;
1579 l_core_e_count number;
1580 l_core_d_count number;
1581
1582 BEGIN
1583
1584 IF p_business_group_id IS null THEN
1585 l_business_group_id := hr_util_misc_web.get_business_group_id;
1586 ELSE
1587 l_business_group_id := p_business_group_id;
1588 END IF;
1589
1590 p_competence_count := 0;
1591 p_essential_count := 0;
1592 p_desirable_count := 0;
1593
1594 FOR v_competence IN csr_competencies_by_core(l_business_group_id) LOOP
1595
1596 p_competence_count := p_competence_count +1;
1597 p_competence_table(p_competence_count).competence_element_id :=
1598 v_competence.competence_element_id;
1599 p_competence_table(p_competence_count).effective_date_from :=
1600 v_competence.effective_date_from;
1601 p_competence_table(p_competence_count).competence_id :=
1602 v_competence.competence_id;
1603 p_competence_table(p_competence_count).competence_name :=
1604 v_competence.name;
1605 p_competence_table(p_competence_count).low_rating_level_id :=
1606 v_competence.proficiency_level_id;
1607 p_competence_table(p_competence_count).high_rating_level_id :=
1608 v_competence.high_proficiency_level_id;
1609 p_competence_table(p_competence_count).low_step_value :=
1610 v_competence.low_step_value;
1611 p_competence_table(p_competence_count).low_step_name :=
1612 v_competence.low_step_name;
1613 p_competence_table(p_competence_count).high_step_value :=
1614 v_competence.high_step_value;
1615 p_competence_table(p_competence_count).high_step_name :=
1616 v_competence.high_step_name;
1617 p_competence_table(p_competence_count).mandatory :=
1618 v_competence.mandatory;
1619 IF v_competence.mandatory = 'Y' THEN
1620 p_essential_count := p_essential_count + 1;
1621 ELSE
1622 p_desirable_count := p_desirable_count + 1;
1623 END IF;
1624
1625 END LOOP;
1626
1627 END get_core_competencies;
1628 -- ---------------------------------------------------------------------------
1629 -- get_org_competencies
1630 -- ---------------------------------------------------------------------------
1631 PROCEDURE get_org_competencies
1632 (p_org_id in number
1633 ,p_effective_date in date
1634 ,p_include_core_competencies in varchar2 default 'N'
1635 ,p_competence_table out nocopy g_competence_table
1636 ,p_competence_count out nocopy number
1637 ,p_essential_count out nocopy number
1638 ,p_desirable_count out nocopy number) IS
1639
1640 CURSOR csr_competencies_by_org IS
1641 SELECT pce.competence_element_id,
1642 pce.effective_date_from,
1643 pce.proficiency_level_id,
1644 pce.high_proficiency_level_id,
1645 pce.mandatory,
1646 cpl.competence_id,
1647 cpl.name,
1648 r1.step_value low_step_value,
1649 rtx1.name low_step_name,
1650 r2.step_value high_step_value,
1651 rtx2.name high_step_name
1652 FROM per_competence_elements pce,
1653 per_competences_tl cpl,
1654 per_rating_levels r1,
1655 per_rating_levels r2,
1656 per_rating_levels_tl rtx1,
1657 per_rating_levels_tl rtx2
1658 WHERE pce.organization_id = p_org_id
1659 AND pce.type = 'REQUIREMENT'
1660 AND p_effective_date BETWEEN pce.effective_date_from AND
1661 NVL(pce.effective_date_to, p_effective_date)
1662 AND pce.competence_id = cpl.competence_id
1663 AND cpl.language = userenv('LANG')
1664 AND pce.proficiency_level_id = r1.rating_level_id(+)
1665 AND pce.high_proficiency_level_id = r2.rating_level_id(+)
1666 AND pce.proficiency_level_id = rtx1.rating_level_id(+)
1667 AND pce.high_proficiency_level_id = rtx2.rating_level_id(+)
1668 AND rtx1.language(+) = userenv('LANG')
1669 AND rtx2.language(+) = userenv('LANG')
1670 ORDER BY cpl.name;
1671
1672 l_core_competence_table g_competence_table;
1673 l_core_count number;
1674 l_core_e_count number;
1675 l_core_d_count number;
1676
1677 BEGIN
1678
1679 p_competence_count := 0;
1680 p_essential_count := 0;
1681 p_desirable_count := 0;
1682
1683 FOR v_competence IN csr_competencies_by_org LOOP
1684
1685 p_competence_count := p_competence_count +1;
1686 p_competence_table(p_competence_count).competence_element_id :=
1687 v_competence.competence_element_id;
1688 p_competence_table(p_competence_count).effective_date_from :=
1689 v_competence.effective_date_from;
1690 p_competence_table(p_competence_count).competence_id :=
1691 v_competence.competence_id;
1692 p_competence_table(p_competence_count).competence_name :=
1693 v_competence.name;
1694 p_competence_table(p_competence_count).low_rating_level_id :=
1695 v_competence.proficiency_level_id;
1696 p_competence_table(p_competence_count).high_rating_level_id :=
1697 v_competence.high_proficiency_level_id;
1698 p_competence_table(p_competence_count).low_step_value :=
1699 v_competence.low_step_value;
1700 p_competence_table(p_competence_count).low_step_name :=
1701 v_competence.low_step_name;
1702 p_competence_table(p_competence_count).high_step_value :=
1703 v_competence.high_step_value;
1704 p_competence_table(p_competence_count).high_step_name :=
1705 v_competence.high_step_name;
1706 p_competence_table(p_competence_count).mandatory :=
1707 v_competence.mandatory;
1708 IF v_competence.mandatory = 'Y' THEN
1709 p_essential_count := p_essential_count + 1;
1710 ELSE
1711 p_desirable_count := p_desirable_count + 1;
1712 END IF;
1713
1714 END LOOP;
1715
1716 IF p_include_core_competencies = 'Y' THEN
1717 get_core_competencies
1718 (p_competence_table => l_core_competence_table
1719 ,p_competence_count => l_core_count
1720 ,p_essential_count => l_core_e_count
1721 ,p_desirable_count => l_core_d_count);
1722
1723 process_exclusive_competence
1724 (p_checked_competence_table => l_core_competence_table
1725 ,p_against_competence_table => p_competence_table
1726 ,p_competence_count => p_competence_count
1727 ,p_essential_count => p_essential_count
1728 ,p_desirable_count => p_desirable_count);
1729
1730 END IF;
1731
1732 END get_org_competencies;
1733 -- ---------------------------------------------------------------------------
1734 -- get_job_competencies
1735 -- ---------------------------------------------------------------------------
1736 PROCEDURE get_job_competencies
1737 (p_job_id in number
1738 ,p_grade_id in number
1739 ,p_effective_date in date
1740 ,p_include_core_competencies in varchar2 default 'N'
1741 ,p_competence_table out nocopy g_competence_table
1742 ,p_competence_count out nocopy number
1743 ,p_essential_count out nocopy number
1744 ,p_desirable_count out nocopy number) IS
1745
1746 CURSOR csr_competencies_by_job IS
1747 SELECT pce.competence_element_id,
1748 pce.effective_date_from,
1749 pce.proficiency_level_id,
1750 pce.high_proficiency_level_id,
1751 pce.mandatory,
1752 cpl.competence_id,
1753 cpl.name,
1754 r1.step_value low_step_value,
1755 rtx1.name low_step_name,
1756 r2.step_value high_step_value,
1757 rtx2.name high_step_name
1758 FROM per_competence_elements pce,
1759 per_competences_tl cpl,
1760 per_rating_levels r1,
1761 per_rating_levels r2,
1762 per_rating_levels_tl rtx1,
1763 per_rating_levels_tl rtx2
1764 WHERE pce.job_id = p_job_id
1765 AND (pce.valid_grade_id IS null
1766 -- OR pce.valid_grade_id = p_grade_id)
1767 OR pce.valid_grade_id in (select pvg.valid_grade_id
1768 from per_valid_grades pvg
1769 where pvg.grade_id = p_grade_id
1770 and trunc(sysdate) between pvg.DATE_FROM and
1771 nvl(pvg.DATE_TO, trunc(sysdate))))
1772 AND pce.type = 'REQUIREMENT'
1773 AND p_effective_date BETWEEN pce.effective_date_from AND
1774 NVL(pce.effective_date_to, p_effective_date)
1775 AND pce.competence_id = cpl.competence_id
1776 AND cpl.language = userenv('LANG')
1777 AND pce.proficiency_level_id = r1.rating_level_id(+)
1778 AND pce.high_proficiency_level_id = r2.rating_level_id(+)
1779 AND pce.proficiency_level_id = rtx1.rating_level_id(+)
1780 AND pce.high_proficiency_level_id = rtx2.rating_level_id(+)
1781 AND rtx1.language(+) = userenv('LANG')
1782 AND rtx2.language(+) = userenv('LANG')
1783 ORDER BY cpl.name;
1784
1785 l_core_competence_table g_competence_table;
1786 l_core_count number;
1787 l_core_e_count number;
1788 l_core_d_count number;
1789
1790 BEGIN
1791
1792 p_competence_count := 0;
1793 p_essential_count := 0;
1794 p_desirable_count := 0;
1795
1796 FOR v_competence IN csr_competencies_by_job LOOP
1797
1798 p_competence_count := p_competence_count +1;
1799 p_competence_table(p_competence_count).competence_element_id :=
1800 v_competence.competence_element_id;
1801 p_competence_table(p_competence_count).effective_date_from :=
1802 v_competence.effective_date_from;
1803 p_competence_table(p_competence_count).competence_id :=
1804 v_competence.competence_id;
1805 p_competence_table(p_competence_count).competence_name :=
1806 v_competence.name;
1807 p_competence_table(p_competence_count).low_rating_level_id :=
1808 v_competence.proficiency_level_id;
1809 p_competence_table(p_competence_count).high_rating_level_id :=
1810 v_competence.high_proficiency_level_id;
1811 p_competence_table(p_competence_count).low_step_value :=
1812 v_competence.low_step_value;
1813 p_competence_table(p_competence_count).low_step_name :=
1814 v_competence.low_step_name;
1815 p_competence_table(p_competence_count).high_step_value :=
1816 v_competence.high_step_value;
1817 p_competence_table(p_competence_count).high_step_name :=
1818 v_competence.high_step_name;
1819 p_competence_table(p_competence_count).mandatory :=
1820 v_competence.mandatory;
1821 IF v_competence.mandatory = 'Y' THEN
1822 p_essential_count := p_essential_count + 1;
1823 ELSE
1824 p_desirable_count := p_desirable_count + 1;
1825 END IF;
1826
1827 END LOOP;
1828
1829 IF p_include_core_competencies = 'Y' THEN
1830
1831 get_core_competencies
1832 (p_competence_table => l_core_competence_table
1833 ,p_competence_count => l_core_count
1834 ,p_essential_count => l_core_e_count
1835 ,p_desirable_count => l_core_d_count);
1836
1837 process_exclusive_competence
1838 (p_checked_competence_table => l_core_competence_table
1839 ,p_against_competence_table => p_competence_table
1840 ,p_competence_count => p_competence_count
1841 ,p_essential_count => p_essential_count
1842 ,p_desirable_count => p_desirable_count);
1843
1844 END IF;
1845
1846 END get_job_competencies;
1847 -- ---------------------------------------------------------------------------
1848 -- get_pos_competencies
1849 -- ---------------------------------------------------------------------------
1850 PROCEDURE get_pos_competencies
1851 (p_pos_id in number
1852 ,p_grade_id in number
1853 ,p_effective_date in date
1854 ,p_competence_table out nocopy g_competence_table
1855 ,p_competence_count out nocopy number
1856 ,p_essential_count out nocopy number
1857 ,p_desirable_count out nocopy number) IS
1858
1859 CURSOR csr_competencies_by_pos IS
1860 SELECT pce.competence_element_id,
1861 pce.effective_date_from,
1862 pce.proficiency_level_id,
1863 pce.high_proficiency_level_id,
1864 pce.mandatory,
1865 cpl.competence_id,
1866 cpl.name,
1867 r1.step_value low_step_value,
1868 rtx1.name low_step_name,
1869 r2.step_value high_step_value,
1870 rtx2.name high_step_name
1871 FROM per_competence_elements pce,
1872 per_competences_tl cpl,
1873 per_rating_levels r1,
1874 per_rating_levels r2,
1875 per_rating_levels_tl rtx1,
1876 per_rating_levels_tl rtx2
1877 WHERE pce.position_id = p_pos_id
1878 AND (pce.valid_grade_id IS null
1879 -- OR pce.valid_grade_id = p_grade_id)
1880 OR pce.valid_grade_id in (select pvg.valid_grade_id
1881 from per_valid_grades pvg
1882 where pvg.grade_id = p_grade_id
1883 and trunc(sysdate) between pvg.DATE_FROM and
1884 nvl(pvg.DATE_TO, trunc(sysdate))))
1885 AND pce.type = 'REQUIREMENT'
1886 AND p_effective_date BETWEEN pce.effective_date_from AND
1887 NVL(pce.effective_date_to, p_effective_date)
1888 AND pce.competence_id = cpl.competence_id
1889 AND cpl.language = userenv('LANG')
1890 AND pce.proficiency_level_id = r1.rating_level_id(+)
1891 AND pce.high_proficiency_level_id = r2.rating_level_id(+)
1892 AND pce.proficiency_level_id = rtx1.rating_level_id(+)
1893 AND pce.high_proficiency_level_id = rtx2.rating_level_id(+)
1894 AND rtx1.language(+) = userenv('LANG')
1895 AND rtx2.language(+) = userenv('LANG')
1896 ORDER BY cpl.name;
1897
1898 BEGIN
1899
1900 p_competence_count := 0;
1901 p_essential_count := 0;
1902 p_desirable_count := 0;
1903
1904 FOR v_competence IN csr_competencies_by_pos LOOP
1905 p_competence_count := p_competence_count +1;
1906 p_competence_table(p_competence_count).competence_element_id :=
1907 v_competence.competence_element_id;
1908 p_competence_table(p_competence_count).effective_date_from :=
1909 v_competence.effective_date_from;
1910 p_competence_table(p_competence_count).competence_id :=
1911 v_competence.competence_id;
1912 p_competence_table(p_competence_count).competence_name :=
1913 v_competence.name;
1914 p_competence_table(p_competence_count).low_rating_level_id :=
1915 v_competence.proficiency_level_id;
1916 p_competence_table(p_competence_count).high_rating_level_id :=
1917 v_competence.high_proficiency_level_id;
1918 p_competence_table(p_competence_count).low_step_value :=
1919 v_competence.low_step_value;
1920 p_competence_table(p_competence_count).low_step_name :=
1921 v_competence.low_step_name;
1922 p_competence_table(p_competence_count).high_step_value :=
1923 v_competence.high_step_value;
1924 p_competence_table(p_competence_count).high_step_name :=
1925 v_competence.high_step_name;
1926 p_competence_table(p_competence_count).mandatory :=
1927 v_competence.mandatory;
1928 IF v_competence.mandatory = 'Y' THEN
1929 p_essential_count := p_essential_count + 1;
1930 ELSE
1931 p_desirable_count := p_desirable_count + 1;
1932 END IF;
1933
1934 END LOOP;
1935
1936 END get_pos_competencies;
1937 -- ---------------------------------------------------------------------------
1938 -- get_all_pos_competencies
1939 --
1940 -- For each competence required by the position then
1941 -- use the low, high and matching level from the position requirements.
1942 --
1943 -- For each competence required by the position's organization or position's
1944 -- job that is not
1945 -- explicitely required by the position then
1946 --
1947 -- 1. if the competence is required by job only, then
1948 -- use the low, high and matching level from the job requirements.
1949 -- 2. if the competence is required by org only, then
1950 -- use the low, high and matching level from the org requirements.
1951 -- 3. if the competence is essential for org and job or desirable for org
1952 -- and job, then
1953 -- use greatest (org low, job low), least (org high, job high) and
1954 -- matching level.
1955 -- 4. if the competence is essential for org and desirable for job or
1956 -- essential for job and desirable
1957 -- for org, then 2 requirements are used. One from the org requirements
1958 -- and one from job requirements.
1959 -- ---------------------------------------------------------------------------
1960 PROCEDURE get_all_pos_competencies
1961 (p_pos_id in number
1962 ,p_grade_id in number
1963 ,p_effective_date in date
1964 ,p_include_core_competencies in varchar2 default 'N'
1965 ,p_competence_table out nocopy g_competence_table
1966 ,p_competence_count out nocopy number
1967 ,p_essential_count out nocopy number
1968 ,p_desirable_count out nocopy number) IS
1969
1970 l_org_id hr_organization_units.organization_id%TYPE;
1971 l_job_id per_jobs.job_id%TYPE;
1972
1973 l_core_competence_table g_competence_table;
1974 l_core_count number;
1975 l_core_e_count number;
1976 l_core_d_count number;
1977
1978 l_org_competence_table g_competence_table;
1979 l_org_count number;
1980 l_org_e_count number;
1981 l_org_d_count number;
1982
1983 l_job_competence_table g_competence_table;
1984 l_job_count number;
1985 l_job_e_count number;
1986 l_job_d_count number;
1987
1988 l_pos_competence_table g_competence_table;
1989 l_pos_count number;
1990 l_pos_e_count number;
1991 l_pos_d_count number;
1992
1993 l_found BOOLEAN;
1994
1995 BEGIN
1996
1997 IF p_include_core_competencies = 'Y' THEN
1998 get_core_competencies
1999 (p_competence_table => l_core_competence_table
2000 ,p_competence_count => l_core_count
2001 ,p_essential_count => l_core_e_count
2002 ,p_desirable_count => l_core_d_count);
2003 END IF;
2004
2005 get_drived_org_job(p_pos_id => p_pos_id
2006 ,p_org_id => l_org_id
2007 ,p_job_id => l_job_id);
2008
2009 get_org_competencies(p_org_id => l_org_id
2010 ,p_competence_table => l_org_competence_table
2011 ,p_competence_count => l_org_count
2012 ,p_essential_count => l_org_e_count
2013 ,p_desirable_count => l_org_d_count);
2014
2015 get_job_competencies(p_job_id => l_job_id
2016 ,p_competence_table => l_job_competence_table
2017 ,p_competence_count => l_job_count
2018 ,p_essential_count => l_job_e_count
2019 ,p_desirable_count => l_job_d_count);
2020 get_pos_competencies(p_pos_id => p_pos_id
2021 ,p_grade_id => p_grade_id
2022 ,p_competence_table => l_pos_competence_table
2023 ,p_competence_count => l_pos_count
2024 ,p_essential_count => l_pos_e_count
2025 ,p_desirable_count => l_pos_d_count);
2026
2027 --
2028 --check duplicate competence: core vs pos,core vs org,core vs job
2029 --
2030 IF p_include_core_competencies = 'Y' THEN
2031 get_core_competencies
2032 (p_competence_table => l_core_competence_table
2033 ,p_competence_count => l_core_count
2034 ,p_essential_count => l_core_e_count
2035 ,p_desirable_count => l_core_d_count);
2036 process_duplicate_competence
2037 (p_checked_competence_table => l_core_competence_table
2038 ,p_against_competence_table => l_pos_competence_table);
2039 process_duplicate_competence
2040 (p_checked_competence_table => l_core_competence_table
2041 ,p_against_competence_table => l_org_competence_table);
2042 process_duplicate_competence
2043 (p_checked_competence_table => l_core_competence_table
2044 ,p_against_competence_table => l_job_competence_table);
2045 END IF;
2046 --
2047 --check duplicate competence: org vs pos
2048 --
2049 process_duplicate_competence
2050 (p_checked_competence_table => l_org_competence_table
2051 ,p_against_competence_table => l_pos_competence_table);
2052
2053 --
2054 --check duplicate competence: job vs pos
2055 --
2056 process_duplicate_competence
2057 (p_checked_competence_table => l_job_competence_table
2058 ,p_against_competence_table => l_pos_competence_table);
2059
2060 --
2061 --check exclusive competence: org vs job
2062 --add exclusive org competencies to pos
2063 --
2064 p_competence_count := l_pos_count;
2065 p_essential_count := l_pos_e_count;
2066 p_desirable_count := l_pos_d_count;
2067
2068 FOR v_org_index IN 1..l_org_count LOOP
2069 IF l_org_competence_table(v_org_index).checked IS null THEN
2070 l_found := FALSE;
2071 FOR v_job_index IN 1..l_job_count LOOP
2072 IF l_job_competence_table(v_job_index).checked IS null THEN
2073 IF l_org_competence_table(v_org_index).competence_id =
2074 l_job_competence_table(v_job_index).competence_id then
2075 l_found := TRUE;
2076 EXIT;
2077 END IF;
2078 END IF;
2079 END LOOP;
2080 IF l_found = FALSE THEN
2081
2082 FOR i IN 1..NVL(l_pos_competence_table.count,0) LOOP
2083 IF l_org_competence_table(v_org_index).competence_name <=
2084 l_pos_competence_table(i).competence_name THEN
2085 FOR j IN REVERSE i..NVL(l_pos_competence_table.count,0) LOOP
2086 l_pos_competence_table(j+1) := l_pos_competence_table(j);
2087 END LOOP;
2088 l_pos_competence_table(i) := l_org_competence_table(v_org_index);
2089 l_found := TRUE;
2090 EXIT;
2091 END IF;
2092 END LOOP;
2093 IF l_found = FALSE THEN
2094 l_pos_competence_table(NVL(l_pos_competence_table.count,0)+1) :=
2095 l_org_competence_table(v_org_index);
2096 END IF;
2097
2098 p_competence_count := p_competence_count + 1;
2099
2100 IF l_org_competence_table(v_org_index).mandatory = 'Y' THEN
2101 p_essential_count := p_essential_count + 1;
2102 ELSE
2103 p_desirable_count := p_desirable_count + 1;
2104 END IF;
2105 l_org_competence_table(v_org_index).checked := 'Y';
2106 END IF;
2107 END IF;
2108 END LOOP;
2109 --
2110 --check exclusive competence: job vs org
2111 --add exclusive job competencies to pos
2112 --
2113 FOR v_job_index IN 1..l_job_count LOOP
2114 IF l_job_competence_table(v_job_index).checked IS null THEN
2115 l_found := FALSE;
2116 FOR v_org_index IN 1..l_org_count LOOP
2117 IF l_org_competence_table(v_org_index).checked IS null THEN
2118 IF l_org_competence_table(v_org_index).competence_id =
2119 l_job_competence_table(v_job_index).competence_id then
2120 l_found := TRUE;
2121 EXIT;
2122 END IF;
2123 END IF;
2124 END LOOP;
2125 IF l_found = FALSE THEN
2126
2127 FOR i IN 1..NVL(l_pos_competence_table.count,0) LOOP
2128 IF l_job_competence_table(v_job_index).competence_name <=
2129 l_pos_competence_table(i).competence_name THEN
2130 FOR j IN REVERSE i..NVL(l_pos_competence_table.count,0) LOOP
2131 l_pos_competence_table(j+1) := l_pos_competence_table(j);
2132 END LOOP;
2133 l_pos_competence_table(i) := l_job_competence_table(v_job_index);
2134 l_found := TRUE;
2135 EXIT;
2136 END IF;
2137 END LOOP;
2138 IF l_found = FALSE THEN
2139 l_pos_competence_table(NVL(l_pos_competence_table.count,0)+1) :=
2140 l_job_competence_table(v_job_index);
2141 END IF;
2142
2143 p_competence_count := p_competence_count + 1;
2144
2145 IF l_job_competence_table(v_job_index).mandatory = 'Y' THEN
2146 p_essential_count := p_essential_count + 1;
2147 ELSE
2148 p_desirable_count := p_desirable_count + 1;
2149 END IF;
2150
2151 l_job_competence_table(v_job_index).checked := 'Y';
2152
2153 END IF;
2154 END IF;
2155 END LOOP;
2156 --
2157 --check duplicate competence: org vs job
2158 --
2159 FOR v_org_index IN 1..l_org_count LOOP
2160 IF l_org_competence_table(v_org_index).checked IS null THEN
2161 FOR v_job_index IN 1..l_job_count LOOP
2162 IF l_job_competence_table(v_job_index).checked IS null THEN
2163 IF l_org_competence_table(v_org_index).competence_id =
2164 l_job_competence_table(v_job_index).competence_id THEN
2165 IF l_org_competence_table(v_org_index).mandatory =
2166 l_job_competence_table(v_job_index).mandatory THEN
2167 p_competence_count := p_competence_count + 1;
2168 l_pos_competence_table(p_competence_count) :=
2169 l_org_competence_table(v_org_index);
2170 IF l_org_competence_table(v_org_index).low_step_value >
2171 l_job_competence_table(v_job_index).low_step_value THEN
2172 l_pos_competence_table(p_competence_count).low_rating_level_id :=
2173 l_org_competence_table(v_org_index).low_rating_level_id;
2174 l_pos_competence_table(p_competence_count).low_step_value :=
2175 l_org_competence_table(v_org_index).low_step_value;
2176 ELSE
2177 l_pos_competence_table(p_competence_count).low_rating_level_id :=
2178 l_job_competence_table(v_job_index).low_rating_level_id;
2179 l_pos_competence_table(p_competence_count).low_step_value :=
2180 l_job_competence_table(v_job_index).low_step_value;
2181 END IF;
2182 IF l_org_competence_table(v_org_index).high_step_value <
2183 l_job_competence_table(v_job_index).high_step_value THEN
2184 l_pos_competence_table(p_competence_count).high_rating_level_id :=
2185 l_org_competence_table(v_org_index).high_rating_level_id;
2186 l_pos_competence_table(p_competence_count).high_step_value :=
2187 l_org_competence_table(v_org_index).high_step_value;
2188 ELSE
2189 l_pos_competence_table(p_competence_count).high_rating_level_id :=
2190 l_job_competence_table(v_job_index).high_rating_level_id;
2191 l_pos_competence_table(p_competence_count).high_step_value :=
2192 l_job_competence_table(v_job_index).high_step_value;
2193 END IF;
2194 IF l_org_competence_table(v_org_index).mandatory = 'Y' THEN
2195 p_essential_count := p_essential_count + 1;
2196 ELSE
2197 p_desirable_count := p_desirable_count + 1;
2198 END IF;
2199 ELSE
2200 p_competence_count := p_competence_count + 1;
2201 l_pos_competence_table(p_competence_count) :=
2202 l_org_competence_table(v_org_index);
2203 IF l_org_competence_table(v_org_index).mandatory = 'Y' THEN
2204 p_essential_count := p_essential_count + 1;
2205 ELSE
2206 p_desirable_count := p_desirable_count + 1;
2207 END IF;
2208 p_competence_count := p_competence_count + 1;
2209 l_pos_competence_table(p_competence_count) :=
2210 l_job_competence_table(v_job_index);
2211 IF l_job_competence_table(v_job_index).mandatory = 'Y' THEN
2212 p_essential_count := p_essential_count + 1;
2213 ELSE
2214 p_desirable_count := p_desirable_count + 1;
2215 END IF;
2216 END IF;
2217 l_job_competence_table(v_job_index).checked := 'Y';
2218 l_org_competence_table(v_org_index).checked := 'Y';
2219 EXIT;
2220 END IF;
2221 END IF;
2222 END LOOP;
2223 END IF;
2224 END LOOP;
2225 --
2226 --process exclusive competence: core vs pos
2227 --add exclusive core competencies to pos
2228 --
2229 IF p_include_core_competencies = 'Y' THEN
2230 process_exclusive_competence
2231 (p_checked_competence_table => l_core_competence_table
2232 ,p_against_competence_table => l_pos_competence_table
2233 ,p_competence_count => p_competence_count
2234 ,p_essential_count => p_essential_count
2235 ,p_desirable_count => p_desirable_count);
2236 END IF;
2237
2238 p_competence_table := l_pos_competence_table;
2239
2240 END get_all_pos_competencies;
2241 -- ---------------------------------------------------------------------------
2242 -- get_person_competencies
2243 -- ---------------------------------------------------------------------------
2244 PROCEDURE get_person_competencies
2245 (p_person_id in number
2246 ,p_effective_date in date
2247 ,p_competence_table out nocopy g_competence_table
2248 ,p_competence_count out nocopy number) IS
2249
2250 CURSOR csr_competencies_by_person IS
2251 SELECT cpl.competence_id,
2252 pce.competence_element_id,
2253 pce.effective_date_from,
2254 pce.proficiency_level_id,
2255 pce.high_proficiency_level_id,
2256 pce.mandatory,
2257 cpl.name,
2258 r1.step_value low_step_value,
2259 rtx1.name low_step_name,
2260 r2.step_value high_step_value,
2261 rtx2.name high_step_name
2262 FROM per_competence_elements pce,
2263 per_competences_tl cpl,
2264 per_rating_levels r1,
2265 per_rating_levels r2,
2266 per_rating_levels_tl rtx1,
2267 per_rating_levels_tl rtx2
2268 WHERE pce.person_id = p_person_id
2269 AND pce.type = 'PERSONAL'
2270 AND p_effective_date BETWEEN pce.effective_date_from AND
2271 NVL(pce.effective_date_to, p_effective_date)
2272 AND pce.competence_id = cpl.competence_id
2273 AND cpl.language = userenv('LANG')
2274 AND pce.proficiency_level_id = r1.rating_level_id(+)
2275 AND pce.high_proficiency_level_id = r2.rating_level_id(+)
2276 AND pce.proficiency_level_id = rtx1.rating_level_id(+)
2277 AND pce.high_proficiency_level_id = rtx2.rating_level_id(+)
2278 AND rtx1.language(+) = userenv('LANG')
2279 AND rtx2.language(+) = userenv('LANG')
2280 ORDER BY cpl.name;
2281
2282 BEGIN
2283
2284 p_competence_count := 0;
2285
2286 FOR v_competence IN csr_competencies_by_person LOOP
2287
2288 p_competence_count := p_competence_count +1;
2289 p_competence_table(p_competence_count).competence_element_id :=
2290 v_competence.competence_element_id;
2291 p_competence_table(p_competence_count).competence_id :=
2292 v_competence.competence_id;
2293 p_competence_table(p_competence_count).effective_date_from :=
2294 v_competence.effective_date_from;
2295 p_competence_table(p_competence_count).competence_name :=
2296 v_competence.name;
2297 p_competence_table(p_competence_count).low_rating_level_id :=
2298 v_competence.proficiency_level_id;
2299 p_competence_table(p_competence_count).high_rating_level_id :=
2300 v_competence.high_proficiency_level_id;
2301 p_competence_table(p_competence_count).low_step_value :=
2302 v_competence.low_step_value;
2303 p_competence_table(p_competence_count).low_step_name :=
2304 v_competence.low_step_name;
2305 p_competence_table(p_competence_count).high_step_value :=
2306 v_competence.high_step_value;
2307 p_competence_table(p_competence_count).high_step_name :=
2308 v_competence.high_step_name;
2309 p_competence_table(p_competence_count).mandatory :=
2310 v_competence.mandatory;
2311
2312 END LOOP;
2313
2314 END get_person_competencies;
2315
2316 -- ---------------------------------------------------------------------------
2317 -- process_duplicate_competence
2318 -- ---------------------------------------------------------------------------
2319 PROCEDURE process_duplicate_competence
2320 (p_checked_competence_table in out nocopy g_competence_table
2321 ,p_against_competence_table in out nocopy g_competence_table) IS
2322 BEGIN
2323
2324 FOR v_checked_index IN 1..NVL(p_checked_competence_table.count,0) LOOP
2325 FOR v_against_index IN 1..NVL(p_against_competence_table.count,0) LOOP
2326 IF p_checked_competence_table(v_checked_index).competence_id =
2327 p_against_competence_table(v_against_index).competence_id then
2328 p_checked_competence_table(v_checked_index).checked := 'Y';
2329 EXIT;
2330 END IF;
2331 END LOOP;
2332 END LOOP;
2333
2334 END process_duplicate_competence;
2335
2336 -- ---------------------------------------------------------------------------
2337 -- process_exclusive_competence
2338 -- ---------------------------------------------------------------------------
2339
2340 PROCEDURE process_exclusive_competence
2341 (p_checked_competence_table in out nocopy g_competence_table
2342 ,p_against_competence_table in out nocopy g_competence_table
2343 ,p_competence_count in out nocopy number
2344 ,p_essential_count in out nocopy number
2345 ,p_desirable_count in out nocopy number) IS
2346
2347 l_found boolean;
2348
2349 BEGIN
2350 FOR v_checked_index IN 1..NVL(p_checked_competence_table.count,0) LOOP
2351 IF p_checked_competence_table(v_checked_index).checked IS null THEN
2352 l_found := FALSE;
2353 FOR v_against_index IN 1..NVL(p_against_competence_table.count,0) LOOP
2354 IF p_checked_competence_table(v_checked_index).competence_id =
2355 p_against_competence_table(v_against_index).competence_id then
2356 l_found := TRUE;
2357 EXIT;
2358 END IF;
2359 END LOOP;
2360 IF l_found = FALSE THEN
2361 FOR i IN 1..NVL(p_against_competence_table.count,0) LOOP
2362 IF p_checked_competence_table(v_checked_index).competence_name <=
2363 p_against_competence_table(i).competence_name THEN
2364 FOR j IN REVERSE i..NVL(p_against_competence_table.count,0) LOOP
2365 p_against_competence_table(j+1) := p_against_competence_table(j);
2366 END LOOP;
2367 p_against_competence_table(i) :=
2368 p_checked_competence_table(v_checked_index);
2369 l_found := TRUE;
2370 EXIT;
2371 END IF;
2372 END LOOP;
2373 IF l_found = FALSE THEN
2374 p_against_competence_table
2375 (NVL(p_against_competence_table.count,0)+1) :=
2376 p_checked_competence_table(v_checked_index);
2377 END IF;
2378 p_competence_count := p_competence_count + 1;
2379
2380 IF p_checked_competence_table(v_checked_index).mandatory = 'Y' THEN
2381 p_essential_count := p_essential_count + 1;
2382 ELSE
2383 p_desirable_count := p_desirable_count + 1;
2384 END IF;
2385 p_checked_competence_table(v_checked_index).checked := 'Y';
2386 END IF;
2387 END IF;
2388 END LOOP;
2389 END process_exclusive_competence;
2390
2391
2392 -- ---------------------------------------------------------------------------
2393 -- ranking:
2394 --
2395 --For any competence requirement the following must be true for a match to occur
2396 --
2397 -- if the requirement low and high levels are both null then
2398 -- the person must have an effective competence entry for the competence
2399 --
2400 -- if the requirement low level is not null and the high level is null
2401 -- the person must have an effective competence entry for the competence with
2402 -- level >= required low level
2403 --
2404 -- if the requirement low level is null and the high level is not null
2405 -- the person must have an effective competence entry for the competence with
2406 -- level <= required high level
2407 --
2408 -- if the requirement low level is not null and the high level is not null
2409 -- the person must have an effective competence entry for the competence with
2410 -- required low level <= level <= required high level
2411 --
2412 -- The match is either essential or desirable depending upon the matching level
2413 -- of the requirement.
2414 -- ---------------------------------------------------------------------------
2415
2416 PROCEDURE ranking
2417 (p_person_id in number
2418 ,p_effective_date in date
2419 ,p_competence_table in g_competence_table
2420 ,p_competence_count in number
2421 ,p_match_essential_count out nocopy number
2422 ,p_match_desirable_count out nocopy number) IS
2423
2424 l_person_competence_table g_competence_table;
2425 l_person_count number;
2426
2427 BEGIN
2428
2429 p_match_essential_count := 0;
2430 p_match_desirable_count := 0;
2431
2432 get_person_competencies(p_person_id => p_person_id
2433 ,p_competence_table => l_person_competence_table
2434 ,p_competence_count => l_person_count);
2435 --
2436 --matching competence
2437 --
2438 FOR v_person_index IN 1..l_person_count LOOP
2439 FOR v_index IN 1..p_competence_count LOOP
2440 IF l_person_competence_table(v_person_index).competence_id =
2441 p_competence_table(v_index).competence_id then
2442 IF p_competence_table(v_index).low_rating_level_id IS null AND
2443 p_competence_table(v_index).high_rating_level_id IS null THEN
2444 IF p_competence_table(v_index).mandatory = 'Y' THEN
2445 p_match_essential_count := p_match_essential_count + 1;
2446 ELSE
2447 p_match_desirable_count := p_match_desirable_count + 1;
2448 END IF;
2449 ELSIF p_competence_table(v_index).low_rating_level_id IS NOT null AND
2450 p_competence_table(v_index).high_rating_level_id IS null THEN
2451 IF l_person_competence_table(v_person_index).low_step_value >=
2452 p_competence_table(v_index).low_step_value THEN
2453 IF p_competence_table(v_index).mandatory = 'Y' THEN
2454 p_match_essential_count := p_match_essential_count + 1;
2455 ELSE
2456 p_match_desirable_count := p_match_desirable_count + 1;
2457 END IF;
2458 END IF;
2459 ELSIF p_competence_table(v_index).low_rating_level_id IS null AND
2460 p_competence_table(v_index).high_rating_level_id IS NOT null THEN
2461 IF l_person_competence_table(v_person_index).low_step_value <=
2462 p_competence_table(v_index).high_step_value THEN
2463 IF p_competence_table(v_index).mandatory = 'Y' THEN
2464 p_match_essential_count := p_match_essential_count + 1;
2465 ELSE
2466 p_match_desirable_count := p_match_desirable_count + 1;
2467 END IF;
2468 END IF;
2469 ELSIF p_competence_table(v_index).low_rating_level_id IS NOT null AND
2470 p_competence_table(v_index).high_rating_level_id IS NOT null THEN
2471 IF l_person_competence_table(v_person_index).low_step_value >=
2472 p_competence_table(v_index).low_step_value AND
2473 l_person_competence_table(v_person_index).low_step_value <=
2474 p_competence_table(v_index).high_step_value THEN
2475 IF p_competence_table(v_index).mandatory = 'Y' THEN
2476 p_match_essential_count := p_match_essential_count + 1;
2477 ELSE
2478 p_match_desirable_count := p_match_desirable_count + 1;
2479 END IF;
2480 END IF;
2481 END IF;
2482 EXIT;
2483 END IF;
2484 END LOOP;
2485 END LOOP;
2486
2487 END ranking;
2488
2489 -- ---------------------------------------------------------------------------
2490 -- ranking
2491 -- ---------------------------------------------------------------------------
2492
2493 PROCEDURE ranking
2494 (p_type in varchar2
2495 ,p_id in number
2496 ,p_grade_id in number
2497 ,p_person_id in number
2498 ,p_effective_date in date default sysdate
2499 ,p_essential_count out nocopy number
2500 ,p_desirable_count out nocopy number
2501 ,p_match_essential_count out nocopy number
2502 ,p_match_desirable_count out nocopy number) IS
2503
2504 l_competence_table g_competence_table;
2505 l_competence_count number;
2506
2507 BEGIN
2508
2509 IF p_type = g_organization_type THEN
2510 get_org_competencies(p_org_id => p_id
2511 ,p_effective_date => p_effective_date
2512 ,p_competence_table => l_competence_table
2513 ,p_competence_count => l_competence_count
2514 ,p_essential_count => p_essential_count
2515 ,p_desirable_count => p_desirable_count);
2516 ELSIF p_type = g_job_type THEN
2517 get_job_competencies(p_job_id => p_id
2518 ,p_grade_id => p_grade_id
2519 ,p_effective_date => p_effective_date
2520 ,p_competence_table => l_competence_table
2521 ,p_competence_count => l_competence_count
2522 ,p_essential_count => p_essential_count
2523 ,p_desirable_count => p_desirable_count);
2524 ELSIF p_type = g_position_type THEN
2525 get_all_pos_competencies(p_pos_id => p_id
2526 ,p_grade_id => p_grade_id
2527 ,p_effective_date => p_effective_date
2528 ,p_competence_table => l_competence_table
2529 ,p_competence_count => l_competence_count
2530 ,p_essential_count => p_essential_count
2531 ,p_desirable_count => p_desirable_count);
2532 ELSIF p_type = g_vacancy_type THEN
2533 get_vac_competencies(p_vacancy_id => p_id
2534 ,p_effective_date => p_effective_date
2535 ,p_competence_table => l_competence_table
2536 ,p_competence_count => l_competence_count
2537 ,p_essential_count => p_essential_count
2538 ,p_desirable_count => p_desirable_count);
2539 END IF;
2540
2541 ranking(p_person_id => p_person_id
2542 ,p_effective_date => p_effective_date
2543 ,p_competence_table => l_competence_table
2544 ,p_competence_count => l_competence_count
2545 ,p_match_essential_count => p_match_essential_count
2546 ,p_match_desirable_count => p_match_desirable_count);
2547
2548 END ranking;
2549
2550 -- ---------------------------------------------------------------------------
2551 -- sort_rank_list
2552 -- ---------------------------------------------------------------------------
2553
2554 PROCEDURE sort_rank_list
2555 (p_rank_table in out nocopy g_rank_table
2556 ,p_rank_table_count in number) IS
2557
2558 l_rank_table g_rank_table;
2559 l_found boolean;
2560 l_temp_str1 varchar2(2000);
2561 l_temp_str2 varchar2(2000);
2562
2563 BEGIN
2564
2565 FOR i IN 1..NVL(p_rank_table.count, 0) LOOP
2566 l_temp_str1 := lpad(to_char(p_rank_table(i).match_e_count),4)
2567 ||lpad(to_char(p_rank_table(i).match_d_count),4);
2568 l_found := FALSE;
2569 FOR j IN 1..NVL(l_rank_table.count, 0) LOOP
2570 l_temp_str2 := lpad(to_char(l_rank_table(j).match_e_count),4)
2571 ||lpad(to_char(l_rank_table(j).match_d_count),4);
2572 IF l_temp_str1 > l_temp_str2 THEN
2573 FOR k IN REVERSE j..l_rank_table.count LOOP
2574 l_rank_table(k+1) := l_rank_table(k);
2575 END LOOP;
2576 l_rank_table(j) := p_rank_table(i);
2577 l_found := TRUE;
2578 EXIT;
2579 END IF;
2580 END LOOP;
2581 IF l_found = FALSE THEN
2582 l_rank_table(i) := p_rank_table(i);
2583 END IF;
2584 END LOOP;
2585
2586 p_rank_table := l_rank_table;
2587
2588 END sort_rank_list;
2589 -- ---------------------------------------------------------------------------
2590 -- get_people_by_vacancy
2591 -- ---------------------------------------------------------------------------
2592
2593 PROCEDURE get_people_by_vacancy
2594 (p_vacancy_id in number
2595 ,p_effective_date in date
2596 ,p_person_name out nocopy hr_util_misc_web.g_varchar2_tab_type
2597 ,p_person_id out nocopy hr_util_misc_web.g_varchar2_tab_type
2598 ,p_person_type out nocopy hr_util_misc_web.g_varchar2_tab_type
2599 ,p_count out nocopy number) IS
2600
2601 CURSOR csr_people_by_vacancy IS
2602 SELECT distinct(paf.person_id) person_id
2603 ,ppf.full_name name
2604 ,hr_person_type_usage_info.get_user_person_type
2605 (p_effective_date, paf.person_id) type
2606 ,ppf.order_name
2607 FROM per_assignments_f paf
2608 ,per_people_f ppf
2609 WHERE paf.vacancy_id = p_vacancy_id
2610 AND p_effective_date BETWEEN paf.effective_start_date
2611 AND NVL(paf.effective_end_date, p_effective_date)
2612 AND paf.person_id = ppf.person_id
2613 AND p_effective_date BETWEEN ppf.effective_start_date
2614 AND NVL(ppf.effective_end_date, p_effective_date)
2615 ORDER BY NVL(ppf.order_name,ppf.full_name);
2616
2617 --legislatioon_code = 'JP'
2618 CURSOR csr_jp_people_by_vacancy IS
2619 SELECT distinct(paf.person_id) person_id
2620 ,ppf.full_name name
2621 ,hr_person_type_usage_info.get_user_person_type
2622 (p_effective_date, paf.person_id) type
2623 ,ppf.last_name
2624 ,ppf.first_name
2625 FROM per_assignments_f paf
2626 ,per_people_f ppf
2627 WHERE paf.vacancy_id = p_vacancy_id
2628 AND p_effective_date BETWEEN paf.effective_start_date
2629 AND NVL(paf.effective_end_date, p_effective_date)
2630 AND paf.person_id = ppf.person_id
2631 AND p_effective_date BETWEEN ppf.effective_start_date
2632 AND NVL(ppf.effective_end_date, p_effective_date)
2633 ORDER BY ppf.last_name, ppf.first_name;
2634
2635 BEGIN
2636
2637 hr_util_misc_web.validate_session(p_person_id => c_person_id);
2638 c_legislation_code := hr_misc_web.get_legislation_code
2639 (p_person_id => c_person_id);
2640
2641 p_count := 0;
2642 IF c_legislation_code = g_japan_legislation_code THEN
2643 FOR v_people_by_vacancy IN csr_jp_people_by_vacancy LOOP
2644 p_count := p_count + 1;
2645 p_person_id(p_count) := v_people_by_vacancy.person_id;
2646 p_person_name(p_count) := v_people_by_vacancy.name;
2647 p_person_type(p_count) := v_people_by_vacancy.type;
2648 END LOOP;
2649 ELSE
2650 FOR v_people_by_vacancy IN csr_people_by_vacancy LOOP
2651 p_count := p_count + 1;
2652 p_person_id(p_count) := v_people_by_vacancy.person_id;
2653 p_person_name(p_count) := v_people_by_vacancy.name;
2654 p_person_type(p_count) := v_people_by_vacancy.type;
2655 END LOOP;
2656 END IF;
2657
2658 END get_people_by_vacancy;
2659
2660 -- ---------------------------------------------------------------------------
2661 -- get_vacancies_by_person
2662 -- ---------------------------------------------------------------------------
2663
2664 PROCEDURE get_vacancies_by_person
2665 (p_person_id in number
2666 ,p_effective_date in date
2667 ,p_vacancy_id out nocopy hr_util_misc_web.g_varchar2_tab_type
2668 ,p_name out nocopy hr_util_misc_web.g_varchar2_tab_type
2669 ,p_count out nocopy number) IS
2670
2671 CURSOR csr_vacanies_by_person IS
2672 SELECT pasf.vacancy_id
2673 ,pv.name ||' ('||pr.name||')' name
2674 FROM per_assignments_f pasf,
2675 per_assignment_status_types past,
2676 per_vacancies pv,
2677 per_requisitions pr
2678 WHERE pasf.person_id = p_person_id
2679 AND p_effective_date BETWEEN pasf.effective_start_date
2680 AND pasf.effective_end_date
2681 AND pasf.vacancy_id = pv.vacancy_id
2682 AND pv.requisition_id = pr.requisition_id
2683 AND pasf.assignment_status_type_id = past.assignment_status_type_id
2684 AND pasf.assignment_type = 'A'
2685 AND past.per_system_status in ('ACTIVE_APL');
2686
2687 BEGIN
2688
2689 p_count := 0;
2690 FOR v_vacanies_by_person IN csr_vacanies_by_person LOOP
2691 p_count := p_count + 1;
2692 p_vacancy_id(p_count) := v_vacanies_by_person.vacancy_id;
2693 p_name(p_count) := v_vacanies_by_person.name;
2694 END LOOP;
2695
2696 END get_vacancies_by_person;
2697
2698 -- ---------------------------------------------------------------------------
2699 -- get_succession_by_person
2700 -- ---------------------------------------------------------------------------
2701
2702 PROCEDURE get_succession_by_person
2703 (p_person_id in number
2704 ,p_effective_date in date default sysdate
2705 ,p_position_id out nocopy hr_util_misc_web.g_varchar2_tab_type
2706 ,p_name out nocopy hr_util_misc_web.g_varchar2_tab_type
2707 ,p_count out nocopy number) IS
2708
2709 CURSOR csr_succession_by_person IS
2710 SELECT * FROM (
2711 SELECT psp.position_id, pp.name
2712 FROM per_succession_planning psp,
2713 hr_positions_f pp
2714 WHERE psp.person_id = p_person_id
2715 AND p_effective_date <= NVL(psp.end_date, p_effective_date)
2716 AND psp.position_id = pp.position_id
2717 AND TRUNC(p_effective_date) BETWEEN pp.effective_start_date
2718 AND pp.effective_end_date
2719 UNION
2720 SELECT pp.position_id, pp.name
2721 FROM hr_positions_f pp
2722 WHERE pp.position_id = (SELECT pp2.successor_position_id
2723 FROM hr_positions_f pp2,
2724 per_assignments_f paf
2725 WHERE paf.person_id = p_person_id
2726 AND p_effective_date BETWEEN paf.effective_start_date
2727 AND paf.effective_end_date
2728 AND paf.position_id = pp2.position_id
2729 AND p_effective_date BETWEEN pp2.effective_start_date
2730 AND pp2.effective_end_date)
2731 AND TRUNC(p_effective_date) BETWEEN pp.effective_start_date
2732 AND pp.effective_end_date)
2733 ORDER BY name;
2734
2735 BEGIN
2736
2737 p_count := 0;
2738 FOR v_succession_by_person IN csr_succession_by_person LOOP
2739 p_count := p_count + 1;
2740 p_position_id(p_count) := v_succession_by_person.position_id;
2741 p_name(p_count) := v_succession_by_person.name;
2742 END LOOP;
2743
2744 END get_succession_by_person;
2745
2746 -- ---------------------------------------------------------------------------
2747 -- get_deployment_by_person
2748 -- ---------------------------------------------------------------------------
2749
2750 PROCEDURE get_deployment_by_person
2751 (p_person_id in number
2752 ,p_effective_date in date
2753 ,p_assignment_id out nocopy hr_util_misc_web.g_varchar2_tab_type
2754 ,p_position_id out nocopy hr_util_misc_web.g_varchar2_tab_type
2755 ,p_name out nocopy hr_util_misc_web.g_varchar2_tab_type
2756 ,p_grade_id out nocopy hr_util_misc_web.g_varchar2_tab_type
2757 ,p_count out nocopy number) IS
2758
2759 CURSOR csr_deployment_by_person IS
2760 SELECT pasf.assignment_id, pasf.position_id, pasf.grade_id, pp.name
2761 FROM per_assignments_f pasf,
2762 per_assignment_status_types past,
2763 hr_positions_f pp
2764 WHERE pasf.person_id = p_person_id
2765 AND p_effective_date BETWEEN pasf.effective_start_date
2766 AND pasf.effective_end_date
2767 AND pasf.position_id = pp.position_id
2768 AND TRUNC(SYSDATE) BETWEEN pp.effective_start_date
2769 AND pp.effective_end_date
2770 --AND pasf.vacancy_id is null
2771 AND pasf.assignment_status_type_id = past.assignment_status_type_id
2772 AND (pasf.assignment_type = 'E' AND
2773 past.per_system_status in ('ACTIVE_ASSIGN')
2774 OR pasf.assignment_type = 'A' AND
2775 past.per_system_status in ('ACCEPTED', 'OFFER', 'ACTIVE_APL'));
2776
2777 BEGIN
2778
2779 p_count := 0;
2780 FOR v_deployment_by_person IN csr_deployment_by_person LOOP
2781 p_count := p_count + 1;
2782 p_assignment_id(p_count) := v_deployment_by_person.assignment_id;
2783 p_position_id(p_count) := v_deployment_by_person.position_id;
2784 p_grade_id(p_count) := v_deployment_by_person.grade_id;
2785 p_name(p_count) := v_deployment_by_person.name;
2786 END LOOP;
2787
2788 END get_deployment_by_person;
2789
2790 -- ---------------------------------------------------------------------------
2791 -- get_job_deployment_by_person
2792 -- ---------------------------------------------------------------------------
2793
2794 PROCEDURE get_job_deployment_by_person
2795 (p_person_id in number
2796 ,p_effective_date in date
2797 ,p_assignment_id out nocopy hr_util_misc_web.g_varchar2_tab_type
2798 ,p_job_id out nocopy hr_util_misc_web.g_varchar2_tab_type
2799 ,p_name out nocopy hr_util_misc_web.g_varchar2_tab_type
2800 ,p_grade_id out nocopy hr_util_misc_web.g_varchar2_tab_type
2801 ,p_count out nocopy number) IS
2802
2803 CURSOR csr_deployment_by_person IS
2804 SELECT pasf.assignment_id, pasf.job_id, pasf.grade_id, pj.name
2805 FROM per_assignments_f pasf,
2806 per_assignment_status_types past,
2807 per_jobs_vl pj
2808 WHERE pasf.person_id = p_person_id
2809 AND p_effective_date BETWEEN pasf.effective_start_date
2810 AND pasf.effective_end_date
2811 AND pasf.job_id = pj.job_id
2812 AND pasf.position_id is null
2813 -- AND pasf.vacancy_id is null
2814 AND pasf.assignment_status_type_id = past.assignment_status_type_id
2815 AND (pasf.assignment_type = 'E' AND
2816 past.per_system_status in ('ACTIVE_ASSIGN')
2817 OR pasf.assignment_type = 'A' AND
2818 past.per_system_status in ('ACCEPTED', 'OFFER', 'ACTIVE_APL'));
2819
2820 BEGIN
2821
2822 p_count := 0;
2823 FOR v_deployment_by_person IN csr_deployment_by_person LOOP
2824 p_count := p_count + 1;
2825 p_assignment_id(p_count) := v_deployment_by_person.assignment_id;
2826 p_job_id(p_count) := v_deployment_by_person.job_id;
2827 p_grade_id(p_count) := v_deployment_by_person.grade_id;
2828 p_name(p_count) := v_deployment_by_person.name;
2829 END LOOP;
2830
2831 END get_job_deployment_by_person;
2832
2833 -- ---------------------------------------------------------------------------
2834 -- get_org_deployment_by_person
2835 -- ---------------------------------------------------------------------------
2836
2837 PROCEDURE get_org_deployment_by_person
2838 (p_person_id in number
2839 ,p_effective_date in date default sysdate
2840 ,p_assignment_id out nocopy hr_util_misc_web.g_varchar2_tab_type
2841 ,p_org_id out nocopy hr_util_misc_web.g_varchar2_tab_type
2842 ,p_name out nocopy hr_util_misc_web.g_varchar2_tab_type
2843 ,p_count out nocopy number) IS
2844
2845 CURSOR csr_deployment_by_person IS
2846 SELECT pasf.assignment_id, pasf.organization_id, hou.name
2847 FROM per_assignments_f pasf,
2848 per_assignment_status_types past,
2849 hr_organization_units hou
2850 WHERE pasf.person_id = p_person_id
2851 AND p_effective_date BETWEEN pasf.effective_start_date
2852 AND pasf.effective_end_date
2853 AND pasf.organization_id = hou.organization_id
2854 AND pasf.position_id is null
2855 AND pasf.job_id is null
2856 -- AND pasf.vacancy_id is null
2857 AND pasf.assignment_status_type_id = past.assignment_status_type_id
2858 AND (pasf.assignment_type = 'E' AND
2859 past.per_system_status in ('ACTIVE_ASSIGN')
2860 OR pasf.assignment_type = 'A' AND
2861 past.per_system_status in ('ACCEPTED', 'OFFER', 'ACTIVE_APL'));
2862
2863 BEGIN
2864
2865 p_count := 0;
2866 FOR v_deployment_by_person IN csr_deployment_by_person LOOP
2867 p_count := p_count + 1;
2868 p_assignment_id(p_count) := v_deployment_by_person.assignment_id;
2869 p_org_id(p_count) := v_deployment_by_person.organization_id;
2870 p_name(p_count) := v_deployment_by_person.name;
2871 END LOOP;
2872 END get_org_deployment_by_person;
2873
2874 -- ---------------------------------------------------------------------------
2875 -- get_vac_deployment_by_person
2876 -- ---------------------------------------------------------------------------
2877
2878 PROCEDURE get_vac_deployment_by_person
2879 (p_person_id in number
2880 ,p_effective_date in date default sysdate
2881 ,p_assignment_id out nocopy hr_util_misc_web.g_varchar2_tab_type
2882 ,p_vac_id out nocopy hr_util_misc_web.g_varchar2_tab_type
2883 ,p_name out nocopy hr_util_misc_web.g_varchar2_tab_type
2884 ,p_count out nocopy number) IS
2885
2886 CURSOR csr_deployment_by_person IS
2887 SELECT pasf.assignment_id
2888 ,pasf.vacancy_id
2889 ,pv.name||' ('||pr.name||')' name
2890 FROM per_assignments_f pasf,
2891 per_assignment_status_types past,
2892 per_vacancies pv,
2893 per_requisitions pr
2894 WHERE pasf.person_id = p_person_id
2895 AND p_effective_date BETWEEN pasf.effective_start_date
2896 AND pasf.effective_end_date
2897 AND pasf.vacancy_id = pv.vacancy_id
2898 AND pv.requisition_id = pr.requisition_id
2899 AND pasf.assignment_status_type_id = past.assignment_status_type_id
2900 AND pasf.assignment_type = 'A'
2901 AND past.per_system_status in ('ACCEPTED', 'OFFER', 'ACTIVE_APL');
2902
2903 BEGIN
2904
2905 p_count := 0;
2906 FOR v_deployment_by_person IN csr_deployment_by_person LOOP
2907 p_count := p_count + 1;
2908 p_assignment_id(p_count) := v_deployment_by_person.assignment_id;
2909 p_vac_id(p_count) := v_deployment_by_person.vacancy_id;
2910 p_name(p_count) := v_deployment_by_person.name;
2911 END LOOP;
2912
2913 END get_vac_deployment_by_person;
2914
2915 -- ---------------------------------------------------------------------------
2916 -- get_succesors_by_position
2917 -- ---------------------------------------------------------------------------
2918
2919 PROCEDURE get_succesors_by_position
2920 (p_pos_id in number
2921 ,p_effective_date in date default sysdate
2922 ,p_person_id out nocopy hr_util_misc_web.g_varchar2_tab_type
2923 ,p_person_name out nocopy hr_util_misc_web.g_varchar2_tab_type
2924 ,p_person_type out nocopy hr_util_misc_web.g_varchar2_tab_type
2925 ,p_count out nocopy number) IS
2926
2927 -- Bug# 2447224.
2928
2929 CURSOR csr_succesors_by_position IS
2930 SELECT * FROM (
2931 SELECT distinct(psp.person_id) person_id
2932 ,ppf.full_name name
2933 ,hr_person_type_usage_info.get_user_person_type
2934 (p_effective_date, ppf.person_id) type
2935 ,ppf.order_name
2936 FROM per_succession_planning psp
2937 ,per_people_f ppf
2938 WHERE psp.position_id = p_pos_id
2939 AND p_effective_date <=
2940 NVL(psp.end_date, p_effective_date)
2941 AND psp.person_id = ppf.person_id
2942 AND p_effective_date BETWEEN ppf.effective_start_date
2943 AND ppf.effective_end_date
2944 UNION
2945 select paf.person_id
2946 ,ppf.full_name name
2947 ,hr_person_type_usage_info.get_user_person_type
2948 (p_effective_date, ppf.person_id) type
2949 ,ppf.order_name
2950 from hr_positions_f ps,
2951 per_assignments_f paf
2952 ,per_people_f ppf
2953 where ps.successor_position_id = p_pos_id
2954 AND p_effective_date BETWEEN ps.effective_start_date
2955 AND ps.effective_end_date
2956 and paf.position_id = ps.position_id
2957 AND paf.person_id = ppf.person_id
2958 AND p_effective_date BETWEEN ppf.effective_start_date
2959 AND ppf.effective_end_date
2960 AND p_effective_date BETWEEN paf.effective_start_date
2961 AND paf.effective_end_date)
2962 ORDER BY NVL(order_name,name);
2963
2964 CURSOR csr_jp_succesors_by_position IS
2965 SELECT * FROM (
2966 SELECT distinct(psp.person_id) person_id
2967 ,ppf.full_name name
2968 ,hr_person_type_usage_info.get_user_person_type
2969 (p_effective_date, ppf.person_id) type
2970 ,ppf.last_name
2971 ,ppf.first_name
2972 FROM per_succession_planning psp
2973 ,per_people_f ppf
2974 WHERE psp.position_id = p_pos_id
2975 AND p_effective_date <=
2976 NVL(psp.end_date, p_effective_date)
2977 AND psp.person_id = ppf.person_id
2978 AND p_effective_date BETWEEN ppf.effective_start_date
2979 AND NVL(ppf.effective_end_date, p_effective_date)
2980 UNION
2981 select paf.person_id
2982 ,ppf.full_name name
2983 ,hr_person_type_usage_info.get_user_person_type
2984 (p_effective_date, ppf.person_id) type
2985 ,ppf.last_name
2986 ,ppf.first_name
2987 from hr_positions_f ps,
2988 per_assignments_f paf
2989 ,per_people_f ppf
2990 where ps.successor_position_id = p_pos_id
2991 AND p_effective_date BETWEEN ps.effective_start_date
2992 AND ps.effective_end_date
2993 and paf.position_id = ps.position_id
2994 AND paf.person_id = ppf.person_id
2995 AND p_effective_date BETWEEN ppf.effective_start_date
2996 AND ppf.effective_end_date
2997 AND p_effective_date BETWEEN paf.effective_start_date
2998 AND paf.effective_end_date)
2999 ORDER BY last_name, first_name;
3000
3001 BEGIN
3002
3003 hr_util_misc_web.validate_session(p_person_id => c_person_id);
3004 c_legislation_code := hr_misc_web.get_legislation_code
3005 (p_person_id => c_person_id);
3006
3007 p_count := 0;
3008 IF c_legislation_code = g_japan_legislation_code THEN
3009 FOR v_succesors_by_position IN csr_jp_succesors_by_position LOOP
3010 p_count := p_count + 1;
3011 p_person_id(p_count) := v_succesors_by_position.person_id;
3012 p_person_name(p_count) := v_succesors_by_position.name;
3013 p_person_type(p_count) := v_succesors_by_position.type;
3014 END LOOP;
3015 ELSE
3016 FOR v_succesors_by_position IN csr_succesors_by_position LOOP
3017 p_count := p_count + 1;
3018 p_person_id(p_count) := v_succesors_by_position.person_id;
3019 p_person_name(p_count) := v_succesors_by_position.name;
3020 p_person_type(p_count) := v_succesors_by_position.type;
3021 END LOOP;
3022 END IF;
3023
3024 END get_succesors_by_position;
3025
3026 -- ---------------------------------------------------------------------------
3027 -- get_people_by_role
3028 -- ---------------------------------------------------------------------------
3029
3030 PROCEDURE get_people_by_role
3031 (p_pre_search_type in varchar2
3032 ,p_pre_search_id in varchar2
3033 ,p_search_type in varchar2
3034 ,p_search_id in varchar2
3035 ,p_grade_id in number default null
3036 ,p_person_id out nocopy hr_util_misc_web.g_varchar2_tab_type
3037 ,p_person_name out nocopy hr_util_misc_web.g_varchar2_tab_type
3038 ,p_person_type out nocopy hr_util_misc_web.g_varchar2_tab_type
3039 ,p_count out nocopy number) IS
3040
3041 l_dynamic_sql varchar2(32000);
3042 l_business_group varchar2(2000);
3043 l_grade_id number;
3044
3045 l_sql_cursor integer;
3046 l_rows integer;
3047 l_index number;
3048 c_id number;
3049 c_name varchar2(240);
3050 c_type varchar2(80);
3051
3052 BEGIN
3053
3054 hr_util_misc_web.validate_session(p_person_id => c_person_id);
3055 c_legislation_code := hr_misc_web.get_legislation_code
3056 (p_person_id => c_person_id);
3057
3058 l_business_group := hr_util_misc_web.get_business_group_id;
3059
3060 -- Bug# 2447224.
3061
3062 l_dynamic_sql :=
3063 'SELECT distinct(paf.person_id) person_id';
3064 l_dynamic_sql := l_dynamic_sql
3065 ||',ppf.full_name name';
3066 l_dynamic_sql := l_dynamic_sql
3067 ||',hr_person_type_usage_info.get_user_person_type(trunc(sysdate),paf.person_id) type'
3068 ||' FROM per_assignments_f paf'
3069 ||' ,per_people_f ppf'
3070 ||' ,per_periods_of_service ppos'
3071 ||' WHERE paf.business_group_id = '
3072 ||l_business_group
3073 ||' AND paf.assignment_type = ''E'''
3074 ||' AND TRUNC(sysdate) BETWEEN paf.effective_start_date'
3075 ||' AND NVL(paf.effective_end_date,sysdate)'
3076 ||' AND paf.person_id = ppf.person_id'
3077 ||' AND ppos.person_id(+) = ppf.person_id'
3078 ||' AND (ppos.actual_termination_date is null '
3079 ||' or ppos.actual_termination_date > trunc(sysdate))'
3080 ||' AND TRUNC(sysdate) BETWEEN ppf.effective_start_date'
3081 ||' AND NVL(ppf.effective_end_date,sysdate)';
3082
3083 IF p_search_type = g_location_type THEN
3084 l_dynamic_sql := l_dynamic_sql
3085 ||' AND paf.location_id = '
3086 ||p_search_id;
3087 ELSIF p_search_type = g_organization_type THEN
3088 l_dynamic_sql := l_dynamic_sql
3089 ||' AND paf.organization_id = '
3090 ||p_search_id;
3091 ELSIF p_search_type = g_job_type THEN
3092 l_dynamic_sql := l_dynamic_sql
3093 ||' AND paf.job_id = '
3094 ||p_search_id;
3095 IF p_grade_id IS NOT null THEN
3096 l_dynamic_sql := l_dynamic_sql
3097 ||' AND paf.grade_id = '
3098 ||' (SELECT pvg.grade_id'
3099 ||' FROM per_valid_grades pvg'
3100 ||' WHERE pvg.valid_grade_id = '
3101 ||p_grade_id||')';
3102 END IF;
3103 ELSIF p_search_type = g_position_type THEN
3104 l_dynamic_sql := l_dynamic_sql
3105 ||' AND paf.position_id = '
3106 ||p_search_id;
3107 IF p_grade_id IS NOT null THEN
3108 l_dynamic_sql := l_dynamic_sql
3109 ||' AND paf.grade_id = '
3110 ||' (SELECT pvg.grade_id'
3111 ||' FROM per_valid_grades pvg'
3112 ||' WHERE pvg.valid_grade_id = '
3113 ||p_grade_id||')';
3114 END IF;
3115 END IF;
3116
3117 IF p_pre_search_type = g_location_type THEN
3118 l_dynamic_sql := l_dynamic_sql
3119 ||' AND paf.location_id = '
3120 ||p_pre_search_id;
3121 ELSIF p_pre_search_type = g_organization_type THEN
3122 l_dynamic_sql := l_dynamic_sql
3123 ||' AND paf.organization_id = '
3124 ||p_pre_search_id;
3125 ELSIF p_pre_search_type = g_job_type THEN
3126 l_dynamic_sql := l_dynamic_sql
3127 ||' AND paf.job_id = '
3128 ||p_pre_search_id;
3129 IF p_grade_id IS NOT null THEN
3130 l_dynamic_sql := l_dynamic_sql
3131 ||' AND paf.grade_id = '
3132 ||' (SELECT pvg.grade_id'
3133 ||' FROM per_valid_grades pvg'
3134 ||' WHERE pvg.valid_grade_id = '
3135 ||p_grade_id||')';
3136 END IF;
3137 ELSIF p_pre_search_type = g_position_type THEN
3138 l_dynamic_sql := l_dynamic_sql
3139 ||' AND paf.position_id = '
3140 ||p_pre_search_id;
3141 IF p_grade_id IS NOT null THEN
3142 l_dynamic_sql := l_dynamic_sql
3143 ||' AND paf.grade_id = '
3144 ||' (SELECT pvg.grade_id'
3145 ||' FROM per_valid_grades pvg'
3146 ||' WHERE pvg.valid_grade_id = '
3147 ||p_grade_id||')';
3148 END IF;
3149 END IF;
3150
3151 l_dynamic_sql := l_dynamic_sql || ' ORDER BY 2';
3152
3153 l_sql_cursor := dbms_sql.open_cursor;
3154 dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7);
3155 dbms_sql.define_column(l_sql_cursor, 1, c_id, 15);
3156 dbms_sql.define_column(l_sql_cursor, 2, c_name, 240);
3157 dbms_sql.define_column(l_sql_cursor, 3, c_name, 80);
3158 l_rows := dbms_sql.execute(l_sql_cursor);
3159 l_index := 0;
3160 WHILE dbms_sql.fetch_rows(l_sql_cursor) > 0 LOOP
3161 l_index := l_index + 1;
3162 dbms_sql.column_value(l_sql_cursor, 1, p_person_id(l_index));
3163 dbms_sql.column_value(l_sql_cursor, 2, p_person_name(l_index));
3164 dbms_sql.column_value(l_sql_cursor, 3, p_person_type(l_index));
3165 END LOOP;
3166 p_count := l_index;
3167
3168 dbms_sql.close_cursor(l_sql_cursor);
3169
3170 EXCEPTION
3171 WHEN others THEN
3172 IF dbms_sql.is_open(l_sql_cursor) THEN
3173 dbms_sql.close_cursor(l_sql_cursor);
3174 END IF;
3175
3176
3177 END get_people_by_role;
3178 -- ---------------------------------------------------------------------------
3179 -- get_people_by_role_org
3180 -- ---------------------------------------------------------------------------
3181
3182 PROCEDURE get_people_by_role_org
3183 (p_org_id in number
3184 ,p_effective_date in date
3185 ,p_person_id out nocopy hr_util_misc_web.g_varchar2_tab_type
3186 ,p_person_name out nocopy hr_util_misc_web.g_varchar2_tab_type
3187 ,p_person_type out nocopy hr_util_misc_web.g_varchar2_tab_type
3188 ,p_count out nocopy number) IS
3189
3190 CURSOR csr_people_by_role_org IS
3191 SELECT distinct(paf.person_id) person_id
3192 ,ppf.full_name name
3193 ,hr_person_type_usage_info.get_user_person_type
3194 (p_effective_date, ppf.person_id) type
3195 ,ppf.order_name
3196 FROM per_assignments_f paf
3197 ,per_people_f ppf
3198 WHERE paf.organization_id = p_org_id
3199 AND paf.assignment_type = 'E'
3200 AND p_effective_date BETWEEN paf.effective_start_date
3201 AND NVL(paf.effective_end_date, p_effective_date)
3202 AND paf.person_id = ppf.person_id
3203 AND p_effective_date BETWEEN ppf.effective_start_date
3204 AND NVL(ppf.effective_end_date, p_effective_date)
3205 ORDER BY NVL(ppf.order_name,ppf.full_name);
3206
3207 CURSOR csr_jp_people_by_role_org IS
3208 SELECT distinct(paf.person_id) person_id
3209 ,ppf.full_name name
3210 ,hr_person_type_usage_info.get_user_person_type
3211 (p_effective_date,ppf.person_id) type
3212 ,ppf.last_name
3213 ,ppf.first_name
3214 FROM per_assignments_f paf
3215 ,per_people_f ppf
3216 WHERE paf.organization_id = p_org_id
3217 AND paf.assignment_type = 'E'
3218 AND p_effective_date BETWEEN paf.effective_start_date
3219 AND NVL(paf.effective_end_date, p_effective_date)
3220 AND paf.person_id = ppf.person_id
3221 AND p_effective_date BETWEEN ppf.effective_start_date
3222 AND NVL(ppf.effective_end_date, p_effective_date)
3223 ORDER BY ppf.last_name, ppf.first_name;
3224
3225 BEGIN
3226
3227 hr_util_misc_web.validate_session(p_person_id => c_person_id);
3228 c_legislation_code := hr_misc_web.get_legislation_code
3229 (p_person_id => c_person_id);
3230
3231 p_count := 0;
3232 IF c_legislation_code = g_japan_legislation_code THEN
3233 FOR v_people_by_role_org IN csr_jp_people_by_role_org LOOP
3234 p_count := p_count + 1;
3235 p_person_id(p_count) := v_people_by_role_org.person_id;
3236 p_person_name(p_count) := v_people_by_role_org.name;
3237 p_person_type(p_count) := v_people_by_role_org.type;
3238 END LOOP;
3239 ELSE
3240 FOR v_people_by_role_org IN csr_people_by_role_org LOOP
3241 p_count := p_count + 1;
3242 p_person_id(p_count) := v_people_by_role_org.person_id;
3243 p_person_name(p_count) := v_people_by_role_org.name;
3244 p_person_type(p_count) := v_people_by_role_org.type;
3245 END LOOP;
3246 END IF;
3247
3248 END get_people_by_role_org;
3249
3250 -- ---------------------------------------------------------------------------
3251 -- get_people_by_role_job
3252 -- ---------------------------------------------------------------------------
3253
3254 PROCEDURE get_people_by_role_job
3255 (p_job_id in number
3256 ,p_grade_id in number
3257 ,p_effective_date in date
3258 ,p_person_id out nocopy hr_util_misc_web.g_varchar2_tab_type
3259 ,p_person_name out nocopy hr_util_misc_web.g_varchar2_tab_type
3260 ,p_person_type out nocopy hr_util_misc_web.g_varchar2_tab_type
3261 ,p_count out nocopy number) IS
3262
3263 CURSOR csr_people_by_role_job IS
3264 SELECT distinct(paf.person_id) person_id
3265 ,ppf.full_name name
3266 ,hr_person_type_usage_info.get_user_person_type
3267 (p_effective_date,ppf.person_id) type
3268 ,ppf.order_name
3269 FROM per_assignments_f paf
3270 ,per_people_f ppf
3271 WHERE paf.job_id = p_job_id
3272 AND paf.assignment_type = 'E'
3273 AND (p_grade_id is null or paf.grade_id = p_grade_id)
3274 AND p_effective_date BETWEEN paf.effective_start_date
3275 AND NVL(paf.effective_end_date, p_effective_date)
3276 AND paf.person_id = ppf.person_id
3277 AND p_effective_date BETWEEN ppf.effective_start_date
3278 AND NVL(ppf.effective_end_date, p_effective_date)
3279 ORDER BY NVL(ppf.order_name,ppf.full_name);
3280
3281 CURSOR csr_jp_people_by_role_job IS
3282 SELECT distinct(paf.person_id) person_id
3283 ,ppf.full_name name
3284 ,hr_person_type_usage_info.get_user_person_type
3285 (p_effective_date,ppf.person_id) type
3286 ,ppf.last_name
3287 ,ppf.first_name
3288 FROM per_assignments_f paf
3289 ,per_people_f ppf
3290 WHERE paf.job_id = p_job_id
3291 AND paf.assignment_type = 'E'
3292 AND (p_grade_id is null or paf.grade_id = p_grade_id)
3293 AND p_effective_date BETWEEN paf.effective_start_date
3294 AND NVL(paf.effective_end_date, p_effective_date)
3295 AND paf.person_id = ppf.person_id
3296 AND p_effective_date BETWEEN ppf.effective_start_date
3297 AND NVL(ppf.effective_end_date, p_effective_date)
3298 ORDER BY ppf.last_name, ppf.first_name;
3299
3300 BEGIN
3301
3302 hr_util_misc_web.validate_session(p_person_id => c_person_id);
3303 c_legislation_code := hr_misc_web.get_legislation_code
3304 (p_person_id => c_person_id);
3305
3306 p_count := 0;
3307 IF c_legislation_code = g_japan_legislation_code THEN
3308 FOR v_people_by_role_job IN csr_jp_people_by_role_job LOOP
3309 p_count := p_count + 1;
3310 p_person_id(p_count) := v_people_by_role_job.person_id;
3311 p_person_name(p_count) := v_people_by_role_job.name;
3312 p_person_type(p_count) := v_people_by_role_job.type;
3313 END LOOP;
3314 ELSE
3315 FOR v_people_by_role_job IN csr_people_by_role_job LOOP
3316 p_count := p_count + 1;
3317 p_person_id(p_count) := v_people_by_role_job.person_id;
3318 p_person_name(p_count) := v_people_by_role_job.name;
3319 p_person_type(p_count) := v_people_by_role_job.type;
3320 END LOOP;
3321 END IF;
3322
3323 END get_people_by_role_job;
3324
3325 -- ---------------------------------------------------------------------------
3326 -- get_people_by_role_pos
3327 -- ---------------------------------------------------------------------------
3328
3329 PROCEDURE get_people_by_role_pos
3330 (p_pos_id in number
3331 ,p_grade_id in number
3332 ,p_effective_date in date
3333 ,p_person_id out nocopy hr_util_misc_web.g_varchar2_tab_type
3334 ,p_person_name out nocopy hr_util_misc_web.g_varchar2_tab_type
3335 ,p_person_type out nocopy hr_util_misc_web.g_varchar2_tab_type
3336 ,p_count out nocopy number) IS
3337
3338 CURSOR csr_people_by_role_pos IS
3339 SELECT distinct(paf.person_id) person_id
3340 ,ppf.full_name name
3341 ,hr_person_type_usage_info.get_user_person_type
3342 (p_effective_date,ppf.person_id) type
3343 ,ppf.order_name
3344 FROM per_assignments_f paf
3345 ,per_people_f ppf
3346 WHERE paf.position_id = p_pos_id
3347 AND paf.assignment_type = 'E'
3348 AND (p_grade_id is null or paf.grade_id = p_grade_id)
3349 AND p_effective_date BETWEEN paf.effective_start_date
3350 AND NVL(paf.effective_end_date, p_effective_date)
3351 AND paf.person_id = ppf.person_id
3352 AND p_effective_date BETWEEN ppf.effective_start_date
3353 AND NVL(ppf.effective_end_date, p_effective_date)
3354 ORDER BY NVL(ppf.order_name,ppf.full_name);
3355
3356 CURSOR csr_jp_people_by_role_pos IS
3357 SELECT distinct(paf.person_id) person_id
3358 ,ppf.full_name name
3359 ,hr_person_type_usage_info.get_user_person_type
3360 (p_effective_date,ppf.person_id) type
3361 ,ppf.last_name
3362 ,ppf.first_name
3363 FROM per_assignments_f paf
3364 ,per_people_f ppf
3365 WHERE paf.position_id = p_pos_id
3366 AND paf.assignment_type = 'E'
3367 AND (p_grade_id is null or paf.grade_id = p_grade_id)
3368 AND p_effective_date BETWEEN paf.effective_start_date
3369 AND NVL(paf.effective_end_date, p_effective_date)
3370 AND paf.person_id = ppf.person_id
3371 AND p_effective_date BETWEEN ppf.effective_start_date
3372 AND NVL(ppf.effective_end_date, p_effective_date)
3373 ORDER BY ppf.last_name, ppf.first_name;
3374
3375 BEGIN
3376
3377 hr_util_misc_web.validate_session(p_person_id => c_person_id);
3378 c_legislation_code := hr_misc_web.get_legislation_code
3379 (p_person_id => c_person_id);
3380
3381 p_count := 0;
3382 IF c_legislation_code = g_japan_legislation_code THEN
3383 FOR v_people_by_role_pos IN csr_jp_people_by_role_pos LOOP
3384 p_count := p_count + 1;
3385 p_person_id(p_count) := v_people_by_role_pos.person_id;
3386 p_person_name(p_count) := v_people_by_role_pos.name;
3387 p_person_type(p_count) := v_people_by_role_pos.type;
3388 END LOOP;
3389 ELSE
3390 FOR v_people_by_role_pos IN csr_people_by_role_pos LOOP
3391 p_count := p_count + 1;
3392 p_person_id(p_count) := v_people_by_role_pos.person_id;
3393 p_person_name(p_count) := v_people_by_role_pos.name;
3394 p_person_type(p_count) := v_people_by_role_pos.type;
3395 END LOOP;
3396 END IF;
3397
3398 END get_people_by_role_pos;
3399
3400 -- ---------------------------------------------------------------------------
3401 -- get_course_by_person
3402 -- ---------------------------------------------------------------------------
3403
3404 PROCEDURE get_course_by_person
3405 (p_person_id in number
3406 ,p_activity_version_id out nocopy hr_util_misc_web.g_varchar2_tab_type
3407 ,p_name out nocopy hr_util_misc_web.g_varchar2_tab_type
3408 ,p_count out nocopy number) IS
3409
3410 CURSOR csr_course_by_person IS
3411 SELECT distinct(oe.activity_version_id) activity_version_id
3412 ,oav.version_name
3413 FROM ota_events oe
3414 ,ota_delegate_bookings odb
3415 ,ota_activity_versions oav
3416 WHERE odb.delegate_person_id = p_person_id
3417 AND odb.event_id = oe.event_id
3418 AND oe.activity_version_id = oav.activity_version_id;
3419
3420 BEGIN
3421
3422 p_count := 0;
3423 FOR v_course_by_person IN csr_course_by_person LOOP
3424 p_count := p_count + 1;
3425 p_activity_version_id(p_count) := v_course_by_person.activity_version_id;
3426 p_name(p_count) := v_course_by_person.version_name;
3427 END LOOP;
3428
3429 END get_course_by_person;
3430
3431 -- ---------------------------------------------------------------------------
3432 -- get_people_by_course
3433 -- ---------------------------------------------------------------------------
3434
3435 PROCEDURE get_people_by_course
3436 (p_activity_version_id in number
3437 ,p_person_id out nocopy hr_util_misc_web.g_varchar2_tab_type
3438 ,p_count out nocopy number) IS
3439
3440 CURSOR csr_people_by_course IS
3441 SELECT odb.delegate_person_id person_id
3442 FROM ota_delegate_bookings odb
3443 WHERE odb.event_id IN
3444 (SELECT oe.event_id
3445 FROM ota_events oe
3446 WHERE oe.activity_version_id = p_activity_version_id);
3447
3448 BEGIN
3449
3450 p_count := 0;
3451 FOR v_people_by_course IN csr_people_by_course LOOP
3452 p_count := p_count + 1;
3453 p_person_id(p_count) := v_people_by_course.person_id;
3454 END LOOP;
3455
3456 END get_people_by_course;
3457
3458 -- ---------------------------------------------------------------------------
3459 -- get_rating_scale_by_competence
3460 -- ---------------------------------------------------------------------------
3461
3462 PROCEDURE get_rating_scale_by_competence
3463 (p_competence_id in number
3464 ,p_rating_level_id out nocopy hr_util_misc_web.g_varchar2_tab_type
3465 ,p_step_value out nocopy hr_util_misc_web.g_varchar2_tab_type
3466 ,p_name out nocopy hr_util_misc_web.g_varchar2_tab_type
3467 ,p_count out nocopy number) IS
3468
3469 CURSOR csr_rating_level_by_scale IS
3470 SELECT prl.rating_level_id, prl.step_value, rtx.name
3471 FROM per_rating_levels prl
3472 ,per_competences pc
3473 ,per_rating_levels_tl rtx
3474 WHERE pc.competence_id = p_competence_id
3475 AND pc.rating_scale_id = prl.rating_scale_id
3476 AND prl.rating_level_id = rtx.rating_level_id
3477 AND rtx.language = userenv('LANG')
3478 ORDER BY step_value;
3479
3480 CURSOR csr_rating_level_by_competence IS
3481 SELECT prl.rating_level_id, prl.step_value, rtx.name
3482 FROM per_rating_levels prl
3483 ,per_competences pc
3484 ,per_rating_levels_tl rtx
3485 WHERE pc.competence_id = p_competence_id
3486 AND pc.competence_id = prl.competence_id
3487 AND prl.rating_level_id = rtx.rating_level_id
3488 AND rtx.language = userenv('LANG')
3489 ORDER BY step_value;
3490
3491 BEGIN
3492
3493 p_count := 0;
3494 FOR v_rating_level_by_scale IN csr_rating_level_by_scale LOOP
3495 p_count := p_count + 1;
3496 p_rating_level_id(p_count) := v_rating_level_by_scale.rating_level_id;
3497 p_step_value(p_count) := v_rating_level_by_scale.step_value;
3498 p_name(p_count) := v_rating_level_by_scale.name;
3499 END LOOP;
3500 IF p_count = 0 THEN
3501 FOR v_rating_level_by_competence IN csr_rating_level_by_competence LOOP
3502 p_count := p_count + 1;
3503 p_rating_level_id(p_count) := v_rating_level_by_competence.rating_level_id;
3504 p_step_value(p_count) := v_rating_level_by_competence.step_value;
3505 p_name(p_count) := v_rating_level_by_competence.name;
3506 END LOOP;
3507 END IF;
3508
3509 END get_rating_scale_by_competence;
3510 ------------------------------------------------------------------------------
3511
3512 END hr_suit_match_utility_web;