1 PACKAGE BODY pa_hr_competence_utils AS
2 -- $Header: PACOMUTB.pls 120.1 2005/08/19 16:20:35 mwasowic noship $
3
4 --
5 -- PROCEDURE
6 -- Check_Rating_Level_Or_Id
7 -- PURPOSE
8 -- This procedure does the following
9 -- If Rating Level (Step Value) is passed converts it to the id
10 -- If Rating Level id is passed,
11 -- based on the check_id_flag validates it
12 -- HISTORY
13 -- 27-JUN-2000 R. Krishnamurthy Created
14 --
15
16 procedure Check_Rating_Level_Or_Id
17 ( p_competence_id IN per_competences.competence_id%TYPE
18 ,p_rating_level_id IN per_rating_levels.rating_level_id%TYPE
19 ,p_rating_level IN per_rating_levels.step_value%TYPE
20 ,p_check_id_flag IN VARCHAR2
21 ,x_rating_level_id OUT NOCOPY per_rating_levels.rating_level_id%TYPE --File.Sql.39 bug 4440895
22 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
23 ,x_error_msg_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
24
25 l_current_id NUMBER := NULL;
26 l_num_ids NUMBER := 0;
27 l_id_found_flag VARCHAR(1) := 'N';
28
29 CURSOR r_ids IS
30 SELECT rating_level_id
31 FROM per_competence_levels_v
32 WHERE competence_id = p_competence_id
33 AND step_value = p_rating_level;
34 BEGIN
35 pa_debug.init_err_stack ('pa_competence_utils.check_rating_level_or_id');
36 IF (p_rating_level_id is not null) THEN
37 IF (p_check_id_flag = 'Y') THEN
38 SELECT rating_level_id
39 INTO x_rating_level_id
40 FROM per_competence_levels_v
41 WHERE competence_id = p_competence_id
42 AND rating_level_id = p_rating_level_id;
43 ELSIF (p_check_id_flag = 'N') THEN
44 x_rating_level_id := p_rating_level_id;
45 ELSIF (p_check_id_flag = 'A') THEN
46 IF (p_rating_level IS NULL) THEN
47 -- Return a null ID since the level is null.
48 x_rating_level_id := NULL;
49 ELSE
50 -- Find the ID which matches the level passed
51 OPEN r_ids;
52 LOOP
53 FETCH r_ids INTO l_current_id;
54 EXIT WHEN r_ids%NOTFOUND;
55 IF (l_current_id = p_rating_level_id) THEN
56 l_id_found_flag := 'Y';
57 x_rating_level_id := p_rating_level_id;
58 END IF;
59 END LOOP;
60 l_num_ids := r_ids%ROWCOUNT;
61 CLOSE r_ids;
62
63 IF (l_num_ids = 0) THEN
64 -- No IDs for level
65 RAISE NO_DATA_FOUND;
66 ELSIF (l_num_ids = 1) THEN
67 -- Since there is only one ID for the level use it.
68 x_rating_level_id := l_current_id;
69 ELSIF (l_id_found_flag = 'N') THEN
70 -- More than one ID for the level and none of the IDs matched
71 -- the ID passed in.
72 RAISE TOO_MANY_ROWS;
73 END IF;
74 END IF;
75 END IF;
76 ELSE -- Find ID since it was not passed.
77 IF (p_rating_level IS NOT NULL) THEN
78 SELECT rating_level_id
79 INTO x_rating_level_id
80 FROM per_competence_levels_v
81 WHERE competence_id = p_competence_id
82 AND step_value = p_rating_level;
83 ELSE
84 x_rating_level_id := NULL;
85 END IF;
86 END IF;
87 x_return_status:= FND_API.G_RET_STS_SUCCESS;
88 pa_debug.reset_err_stack;
89 EXCEPTION
90 WHEN no_data_found THEN
91 x_rating_level_id := NULL;
92 x_return_status:= FND_API.G_RET_STS_ERROR;
93 x_error_msg_code:= 'PA_RATING_INVALID_AMBIGOUS';
94 WHEN too_many_rows THEN
95 x_rating_level_id := NULL;
96 x_return_status:= FND_API.G_RET_STS_ERROR;
97 x_error_msg_code:= 'PA_RATING_INVALID_AMBIGOUS';
98 WHEN OTHERS THEN
99 x_rating_level_id := NULL;
100 fnd_msg_pub.add_exc_msg
101 (p_pkg_name => 'PA_COMPETENCE_UTILS',
102 p_procedure_name => pa_debug.g_err_stack );
103 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
104 RAISE;
105 END Check_Rating_Level_Or_Id;
106
107 PROCEDURE Check_CompName_Or_Id
108 ( p_competence_id IN per_competences.competence_id%TYPE
109 ,p_competence_alias IN per_competences.competence_alias%TYPE
110 ,p_competence_name IN per_competences.name%TYPE := null
111 ,p_check_id_flag IN VARCHAR2
112 ,x_competence_id OUT NOCOPY per_competences.competence_id%TYPE --File.Sql.39 bug 4440895
113 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
114 ,x_error_msg_code OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
115
116 l_current_id NUMBER := NULL;
117 l_num_ids NUMBER := 0;
118 l_id_found_flag VARCHAR(1) := 'N';
119
120 CURSOR c_ids IS
121 SELECT competence_id
122 FROM per_competences
123 WHERE name = p_competence_name;
124 BEGIN
125 pa_debug.init_err_stack ('pa_competence_utils.check_compName_or_id');
126 IF p_competence_id IS NOT NULL THEN
127 IF (p_check_id_flag = 'Y') THEN
128 SELECT competence_id
129 INTO x_competence_id
130 FROM per_competences
131 WHERE competence_id = p_competence_id;
132 ELSIF (p_check_id_flag = 'N') THEN
133 x_competence_id := p_competence_id;
134 ELSIF (p_check_id_flag = 'A') THEN
135 IF (p_competence_name IS NULL) THEN
136 -- Return a null ID since the name is null.
137 x_competence_id := NULL;
138 ELSE
139 -- Find the ID which matches the Name passed
140 OPEN c_ids;
141 LOOP
142 FETCH c_ids INTO l_current_id;
143 EXIT WHEN c_ids%NOTFOUND;
144 IF (l_current_id = p_competence_id) THEN
145 l_id_found_flag := 'Y';
146 x_competence_id := p_competence_id;
147 END IF;
148 END LOOP;
149 l_num_ids := c_ids%ROWCOUNT;
150 CLOSE c_ids;
151
152 IF (l_num_ids = 0) THEN
153 -- No IDs for name
154 RAISE NO_DATA_FOUND;
155 ELSIF (l_num_ids = 1) THEN
156 -- Since there is only one ID for the name use it.
157 x_competence_id := l_current_id;
158 ELSIF (l_id_found_flag = 'N') THEN
159 -- More than one ID for the name and none of the IDs matched
160 -- the ID passed in.
161 RAISE TOO_MANY_ROWS;
162 END IF;
163 END IF;
164 END IF;
165 ELSIF (p_competence_alias is not null) THEN
166 SELECT competence_id
167 INTO x_competence_id
168 FROM per_competences
169 WHERE competence_alias = p_competence_alias;
170 ELSE
171 IF (p_competence_name IS NOT NULL) THEN
172 SELECT competence_id
173 INTO x_competence_id
174 FROM per_competences
175 WHERE name = p_competence_name;
176 ELSE
177 x_competence_id := NULL;
178 END IF;
179 END IF;
180 x_return_status:= FND_API.G_RET_STS_SUCCESS;
181 pa_debug.reset_err_stack;
182 EXCEPTION
183 WHEN no_data_found THEN
184 x_competence_id := NULL;
185 x_return_status:= FND_API.G_RET_STS_ERROR;
186 x_error_msg_code:= 'PA_COMP_INVALID_AMBIGOUS';
187 WHEN too_many_rows THEN
188 x_competence_id := NULL;
189 x_return_status:= FND_API.G_RET_STS_ERROR;
190 x_error_msg_code:= 'PA_COMP_INVALID_AMBIGOUS';
191 WHEN OTHERS THEN
192 x_competence_id := NULL;
193 fnd_msg_pub.add_exc_msg
194 (p_pkg_name => 'PA_COMPETENCE_UTILS',
195 p_procedure_name => pa_debug.g_err_stack );
196 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
197 RAISE;
198 END Check_CompName_Or_Id ;
199
200 PROCEDURE Get_KFF_Structure_Num
201 (p_competency_structure_type IN VARCHAR2
202 ,p_business_group_id IN NUMBER
203 ,x_kff_structure_num OUT NOCOPY fnd_id_flex_structures_vl.id_flex_num%TYPE --File.Sql.39 bug 4440895
204 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
205 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
206 BEGIN
207 NULL;
208 END Get_KFF_Structure_Num ;
209
210 PROCEDURE Get_KFF_SegmentInfo
211 ( p_kff_structure_num IN fnd_id_flex_structures_vl.id_flex_num%TYPE
212 ,x_segment_name1 OUT NOCOPY fnd_id_flex_segments_vl.segment_name%TYPE --File.Sql.39 bug 4440895
213 ,x_segment_prompt1 OUT NOCOPY fnd_id_flex_segments_vl.form_left_prompt%TYPE --File.Sql.39 bug 4440895
214 ,x_column_name1 OUT NOCOPY fnd_id_flex_segments_vl.application_column_name%TYPE --File.Sql.39 bug 4440895
215 ,x_segment_number1 OUT NOCOPY fnd_id_flex_segments_vl.segment_num%TYPE --File.Sql.39 bug 4440895
216 ,x_value_set_id1 OUT NOCOPY fnd_id_flex_segments_vl.flex_value_set_id%TYPE --File.Sql.39 bug 4440895
217 ,x_segment_name2 OUT NOCOPY fnd_id_flex_segments_vl.segment_name%TYPE --File.Sql.39 bug 4440895
218 ,x_segment_prompt2 OUT NOCOPY fnd_id_flex_segments_vl.form_left_prompt%TYPE --File.Sql.39 bug 4440895
219 ,x_column_name2 OUT NOCOPY fnd_id_flex_segments_vl.application_column_name%TYPE --File.Sql.39 bug 4440895
220 ,x_segment_number2 OUT NOCOPY fnd_id_flex_segments_vl.segment_num%TYPE --File.Sql.39 bug 4440895
221 ,x_value_set_id2 OUT NOCOPY fnd_id_flex_segments_vl.flex_value_set_id%TYPE --File.Sql.39 bug 4440895
222 ,x_segment_name3 OUT NOCOPY fnd_id_flex_segments_vl.segment_name%TYPE --File.Sql.39 bug 4440895
223 ,x_segment_prompt3 OUT NOCOPY fnd_id_flex_segments_vl.form_left_prompt%TYPE --File.Sql.39 bug 4440895
224 ,x_column_name3 OUT NOCOPY fnd_id_flex_segments_vl.application_column_name%TYPE --File.Sql.39 bug 4440895
225 ,x_segment_number3 OUT NOCOPY fnd_id_flex_segments_vl.segment_num%TYPE --File.Sql.39 bug 4440895
226 ,x_value_set_id3 OUT NOCOPY fnd_id_flex_segments_vl.flex_value_set_id%TYPE --File.Sql.39 bug 4440895
227 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
228 ,x_return_status OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
229
230 BEGIN
231 NULL;
232 END Get_KFF_SegmentInfo ;
233
234 -- Get Competencies : This procedure returns the competencies defined
235 -- for a given object. Currently , the following object names are
236 -- supported . PROJECT_ROLE, OPEN_ASSIGNMENT , JOB
237 -- PROJECT_ROLE and OPEN_ASSIGNMENT are fetched with the object_id
238 -- while for JOB, the record is fetched with the job_id
239 -- No other values for object_name are supported currently
240 PROCEDURE get_competencies
241 ( p_object_name IN per_competence_elements.object_name%TYPE
242 ,p_object_id IN per_competence_elements.object_id%TYPE
243 ,x_competency_tbl OUT NOCOPY competency_tbl_typ /* Added NOCOPY for bug#2674619 */
244 ,x_no_of_competencies OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
245 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
246 ,x_return_status OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
247 CURSOR l_comp_csr IS
248 SELECT
249 pce.object_id,
250 pce.competence_id,
251 pce.proficiency_level_id rating_level_id,
252 pce.competence_element_id,
253 pce.rowid,
254 pce.mandatory,
255 pc.name ,
256 pc.competence_alias ,
257 Decode(pc.business_group_id ,NULL, 'Y','N') global_flag,
258 pce.object_version_number
259 FROM
260 per_competence_elements pce,
261 per_competences pc
262 WHERE pce.object_name = p_object_name
263 AND pce.object_id = p_object_id
264 AND pce.competence_id = pc.competence_id;
265
266 CURSOR l_job_comp_csr IS
267 SELECT
268 pce.object_id,
269 pce.competence_id,
270 pce.proficiency_level_id rating_level_id,
271 pce.competence_element_id,
272 pce.rowid,
273 pce.mandatory,
274 pc.name ,
275 pc.competence_alias ,
276 Decode(pc.business_group_id ,NULL, 'Y','N') global_flag,
277 pce.object_version_number
278 FROM per_competence_elements pce,
279 per_competences pc
280 WHERE job_id = p_object_id
281 AND pce.competence_id = pc.competence_id;
282 l_index NUMBER := 0;
283 l_competence_exists VARCHAR2(1);
284 l_comp_csr_rec l_comp_csr%ROWTYPE;
285 l_competency_tbl competency_tbl_typ;
286 BEGIN
287 pa_debug.init_err_stack ('pa_competence_utils.get_competencies');
288 x_return_status:= FND_API.G_RET_STS_SUCCESS;
289 IF p_object_name IN ('OPEN_ASSIGNMENT','PROJECT_ROLE') THEN
290 OPEN l_comp_csr; LOOP
291 FETCH l_comp_csr INTO l_comp_csr_rec;
292 EXIT WHEN l_comp_csr%NOTFOUND;
293 l_index := l_index + 1;
294 x_competency_tbl(l_index).object_id := p_object_id ;
295 x_competency_tbl(l_index).competence_id := l_comp_csr_rec.competence_id;
296 x_competency_tbl(l_index).rating_level_id :=
297 l_comp_csr_rec.rating_level_id;
298 x_competency_tbl(l_index).competence_element_id :=
299 l_comp_csr_rec.competence_element_id;
300 x_competency_tbl(l_index).row_id := l_comp_csr_rec.rowid;
301 x_competency_tbl(l_index).mandatory := l_comp_csr_rec.mandatory;
302 x_competency_tbl(l_index).competence_name := l_comp_csr_rec.name ;
303 x_competency_tbl(l_index).competence_alias :=
304 l_comp_csr_rec.competence_alias ;
305 x_competency_tbl(l_index).global_flag := l_comp_csr_rec.global_flag;
306 x_competency_tbl(l_index).object_version_number :=
307 l_comp_csr_rec.object_version_number;
308 END LOOP;
309 CLOSE l_comp_csr;
310 ELSIF
311 p_object_name = 'JOB' THEN
312 OPEN l_job_comp_csr;
313 LOOP
314 FETCH l_job_comp_csr INTO l_comp_csr_rec;
315 EXIT WHEN l_job_comp_csr%NOTFOUND;
316 l_competence_exists := 'N';
317 IF l_competency_tbl.EXISTS(1) THEN
318 FOR i IN 1..l_competency_tbl.COUNT LOOP
319 IF l_competency_tbl(i).competence_id = l_comp_csr_rec.competence_id THEN
320 l_competence_exists := 'Y';
321 EXIT;
322 END IF;
323 END LOOP;
324 END IF; -- If competency tbl EXISTS
325 IF l_competence_exists = 'N' THEN
326 l_index := l_index + 1;
327 l_competency_tbl(l_index).object_id := p_object_id;
328 l_competency_tbl(l_index).competence_id := l_comp_csr_rec.competence_id;
329 l_competency_tbl(l_index).rating_level_id :=
330 l_comp_csr_rec.rating_level_id;
331 l_competency_tbl(l_index).competence_element_id :=
332 l_comp_csr_rec.competence_element_id;
333 l_competency_tbl(l_index).row_id := l_comp_csr_rec.rowid;
334 l_competency_tbl(l_index).mandatory := l_comp_csr_rec.mandatory;
335 l_competency_tbl(l_index).competence_name := l_comp_csr_rec.name ;
336 l_competency_tbl(l_index).competence_alias :=
337 l_comp_csr_rec.competence_alias ;
338 l_competency_tbl(l_index).global_flag := l_comp_csr_rec.global_flag;
339 l_competency_tbl(l_index).object_version_number :=
340 l_comp_csr_rec.object_version_number;
341 END IF; --end if l_competence_exists
342 END LOOP; -- End loop for the cursor
343 CLOSE l_job_comp_csr;
344 x_competency_tbl := l_competency_tbl;
345 END IF; -- End if p_object_name = 'JOB'
346 x_no_of_competencies := l_index;
347 pa_debug.reset_err_stack;
348 EXCEPTION
349 WHEN OTHERS THEN
350 fnd_msg_pub.add_exc_msg
351 (p_pkg_name => 'PA_COMPETENCE_UTILS',
352 p_procedure_name => pa_debug.g_err_stack );
353 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
354 END get_competencies;
355
356 -- Get Person Competencies : This procedure returns the competencies
357 -- defined for a person
358 PROCEDURE get_person_competencies
359 ( p_person_id IN NUMBER
360 ,x_competency_tbl OUT NOCOPY competency_tbl_typ /* Added NOCOPY for bug#2674619 */
361 ,x_no_of_competencies OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
362 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
363 ,x_return_status OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
364
365 CURSOR l_comp_csr IS
366 SELECT
367 pce.person_id,
368 pce.competence_id,
369 pce.proficiency_level_id rating_level_id,
370 pce.competence_element_id,
371 pce.rowid,
372 pce.mandatory,
373 pc.name ,
374 pc.competence_alias ,
375 Decode(pc.business_group_id ,NULL, 'Y','N') global_flag,
376 pce.object_version_number
377 FROM
378 per_competence_elements pce,
379 per_competences pc
380 WHERE pce.person_id = p_person_id
381 AND pce.competence_id = pc.competence_id;
382
383 l_index NUMBER := 0;
384 l_competence_exists VARCHAR2(1);
385 l_comp_csr_rec l_comp_csr%ROWTYPE;
386 l_competency_tbl competency_tbl_typ;
387 BEGIN
388
389 pa_debug.init_err_stack ('pa_competence_utils.get_person_competencies');
390
391 x_return_status:= FND_API.G_RET_STS_SUCCESS;
392
393 OPEN l_comp_csr;
394 LOOP
395
396 FETCH l_comp_csr INTO l_comp_csr_rec;
397 EXIT WHEN l_comp_csr%NOTFOUND;
398
399 l_index := l_index + 1;
400
401 x_competency_tbl(l_index).object_id := p_person_id;
402 x_competency_tbl(l_index).competence_id := l_comp_csr_rec.competence_id;
403 x_competency_tbl(l_index).rating_level_id :=
404 l_comp_csr_rec.rating_level_id;
405 x_competency_tbl(l_index).competence_element_id :=
406 l_comp_csr_rec.competence_element_id;
407 x_competency_tbl(l_index).row_id := l_comp_csr_rec.rowid;
408 x_competency_tbl(l_index).mandatory := l_comp_csr_rec.mandatory;
409 x_competency_tbl(l_index).competence_name := l_comp_csr_rec.name ;
410 x_competency_tbl(l_index).competence_alias :=
411 l_comp_csr_rec.competence_alias ;
412 x_competency_tbl(l_index).global_flag := l_comp_csr_rec.global_flag;
413 x_competency_tbl(l_index).object_version_number :=
414 l_comp_csr_rec.object_version_number;
415
416 END LOOP;
417 CLOSE l_comp_csr;
418
419 x_no_of_competencies := l_index;
420
421 pa_debug.reset_err_stack;
422
423 EXCEPTION
424 WHEN OTHERS THEN
425 fnd_msg_pub.add_exc_msg
426 (p_pkg_name => 'PA_COMPETENCE_UTILS',
427 p_procedure_name => pa_debug.g_err_stack );
428 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
429 END get_person_competencies;
430
431 FUNCTION check_competence_exists
432 ( p_object_name IN per_competence_elements.object_name%TYPE
433 ,p_object_id IN per_competence_elements.object_id%TYPE
434 ,p_competence_id IN per_competences.competence_id%TYPE )
435 RETURN VARCHAR2 IS
436 l_dummy VARCHAR2(1);
437 BEGIN
438 SELECT 'x' INTO l_dummy
439 FROM per_competence_elements
440 WHERE object_id = p_object_id
441 AND object_name = p_object_name
442 AND competence_id = p_competence_id ;
443 RETURN 'Y';
444 EXCEPTION
445 WHEN no_data_found THEN
446 RETURN 'N';
447 -- Too many rows should not occur in normal cases; Still
448 -- we will return 'Y' if that happens
449 WHEN too_many_rows THEN
450 RETURN 'Y';
451 WHEN OTHERS THEN
452 RAISE;
453 END check_competence_exists;
454
455 FUNCTION Get_Res_Competences
456 (p_person_id IN pa_resources_denorm.person_id%TYPE)
457 RETURN VARCHAR2 IS
458
459 TYPE number_tbl IS TABLE OF NUMBER(15);
460 TYPE varchar240_tbl IS TABLE OF VARCHAR(240);
461 TYPE varchar30_tbl IS TABLE OF VARCHAR(30);
462 l_competences_list VARCHAR2(4500) := ''; /* Bug 3439566: Changed the size of the variable from 2000 to 4500 */
463 l_competence_name varchar240_tbl;
464 l_competence_alias varchar30_tbl;
465 l_competence_level number_tbl;
466
467 BEGIN
468 SELECT comp.name,
469 comp.competence_alias,
470 rl.step_value
471 BULK COLLECT INTO
472 l_competence_name,
473 l_competence_alias,
474 l_competence_level
475 FROM PER_COMPETENCE_ELEMENTS comp_ele,
476 PER_COMPETENCES comp,
477 PER_RATING_LEVELS rl
478 WHERE comp_ele.competence_id = comp.competence_id
479 AND comp_ele.person_id = p_person_id
480 AND comp_ele.proficiency_level_id = rl.rating_level_id (+);
481
482 IF l_competence_name.count > 0 THEN
483 FOR i in l_competence_name.FIRST .. l_competence_name.LAST LOOP
484 IF l_competence_alias(i) IS NOT NULL THEN
485 l_competences_list := l_competences_list || l_competence_level(i) || l_competence_alias(i);
486 /* Code addition for Bug 3439566 starts */
487 If length(l_competences_list) > 4000 then
488 exit;
489 end if;
490 /* Code addition for Bug 3439566 ends */
491 ELSE
492 l_competences_list := l_competences_list || l_competence_level(i) || l_competence_name(i);
493 /* Code addition for Bug 3439566 starts */
494 If length(l_competences_list) > 4000 then
495 exit;
496 end if;
497 /* Code addition for Bug 3439566 ends */
498 END IF;
499
500 IF i <> l_competence_name.count THEN
501 l_competences_list := l_competences_list || ',';
502 END IF;
503 END LOOP;
504 END IF;
505
506 RETURN substrb(l_competences_list,1,4000); /* Bug 3439566: Added substrb */
507 EXCEPTION
508 WHEN OTHERS THEN
509 RAISE;
510
511 END Get_Res_Competences;
512
513 FUNCTION Get_Res_Competences_Count
514 (p_person_id IN pa_resources_denorm.person_id%TYPE)
515 RETURN NUMBER IS
516 l_count NUMBER;
517 BEGIN
518 SELECT count(*)
519 INTO l_count
520 FROM PER_COMPETENCE_ELEMENTS
521 WHERE person_id = p_person_id;
522
523 RETURN l_count;
524 EXCEPTION
525 WHEN NO_DATA_FOUND THEN
526 RETURN 0;
527 WHEN OTHERS THEN
528 RAISE;
529 END Get_Res_Competences_Count;
530
531 FUNCTION Get_Res_Comp_Last_Updated
532 (p_person_id IN pa_resources_denorm.person_id%TYPE)
533 RETURN DATE IS
534 l_date DATE;
535 BEGIN
536 SELECT MAX(last_update_date)
537 INTO l_date
538 FROM PER_COMPETENCE_ELEMENTS
539 WHERE person_id = p_person_id;
540
541 RETURN l_date;
542 EXCEPTION
543 WHEN NO_DATA_FOUND THEN
544 RETURN null;
545 WHEN OTHERS THEN
546 RAISE;
547 END Get_Res_Comp_Last_Updated;
548
549 FUNCTION Get_Req_Competences
550 (p_assignment_id IN pa_project_assignments.assignment_id%TYPE)
551 RETURN VARCHAR2 IS
552
553 TYPE number_tbl IS TABLE OF NUMBER(15);
554 TYPE varchar240_tbl IS TABLE OF VARCHAR(240);
555 TYPE varchar30_tbl IS TABLE OF VARCHAR(30);
556 l_competences_list VARCHAR2(4500) := ''; /* Bug 3439566: Changed the size of the variable from 2000 to 4500 */
557 l_competence_name varchar240_tbl;
558 l_competence_alias varchar30_tbl;
559 l_competence_level number_tbl;
560
561 BEGIN
562 SELECT competence_alias,
563 competence_name,
564 rating_level_value
565 BULK COLLECT INTO
566 l_competence_alias,
567 l_competence_name,
568 l_competence_level
569 FROM pa_open_asgmt_competences_v
570 WHERE assignment_id = p_assignment_id;
571
572 IF l_competence_name.count > 0 THEN
573 FOR i in l_competence_name.FIRST .. l_competence_name.LAST LOOP
574 IF l_competence_alias(i) IS NOT NULL THEN
575 l_competences_list := l_competences_list || l_competence_level(i) || l_competence_alias(i);
576 /* Code addition for Bug 3439566 starts */
577 If length(l_competences_list) > 4000 then
578 exit;
579 end if;
580 /* Code addition for Bug 3439566 ends */
581 ELSE
582 l_competences_list := l_competences_list || l_competence_level(i) || l_competence_name(i);
583 /* Code addition for Bug 3439566 starts */
584 If length(l_competences_list) > 4000 then
585 exit;
586 end if;
587 /* Code addition for Bug 3439566 ends */
588 END IF;
589
590 IF i <> l_competence_name.count THEN
591 l_competences_list := l_competences_list || ',';
592 END IF;
593 END LOOP;
594 END IF;
595
596 RETURN substrb(l_competences_list,1,4000); /* Bug 3439566: Added substrb */
597 EXCEPTION
598 WHEN OTHERS THEN
599 RAISE;
600
601 END Get_Req_Competences;
602
603 end pa_hr_competence_utils ;
[Home] [Help]