[Home] [Help]
PACKAGE BODY: APPS.PA_COMP_PROFILE_PVT
Source
1 PACKAGE BODY PA_COMP_PROFILE_PVT AS
2 -- $Header: PARPRFVB.pls 120.5 2005/11/20 20:29:10 sunkalya ship $
3
4 g_profile_id NUMBER := 0;
5 g_person_id NUMBER := 0;
6
7 PROCEDURE Validate_Competency
8 (p_person_id IN NUMBER,
9 p_competence_id IN NUMBER,
10 x_return_status OUT NOCOPY VARCHAR2, -- 4537865 Added the nocopy hint
11 x_error_message_code OUT NOCOPY VARCHAR2); -- 4537865 Added the nocopy hint
12
16 RETURN VARCHAR2;
13 FUNCTION Awaiting_Approval
14 ( p_person_id IN NUMBER,
15 p_competence_id IN NUMBER)
17
18 FUNCTION Awaiting_Approval
19 ( p_element_id IN NUMBER)
20 RETURN VARCHAR2;
21
22 FUNCTION check_competence_exists
23 ( p_person_id IN NUMBER,
24 p_competence_id IN NUMBER)
25 RETURN VARCHAR2;
26
27 Procedure create_competence_element
28 ( p_profile_id IN NUMBER,
29 p_person_id IN NUMBER,
30 p_competence_id IN NUMBER,
31 p_object_version_number IN NUMBER,
32 p_business_group_id IN NUMBER,
33 p_proficiency_level_id IN NUMBER,
34 p_effective_date_from IN DATE
35 );
36
37 Procedure Update_competence_element
38 (p_profile_id IN NUMBER,
39 p_person_id IN NUMBER,
40 p_competence_id IN NUMBER,
41 p_competence_element_id IN NUMBER,
42 p_object_version_number IN NUMBER,
43 p_rating_level_id IN NUMBER,
44 p_effective_date_from IN DATE);
45
46 PROCEDURE Add_Competence_Element
47 ( p_person_id IN per_competence_elements.person_id%TYPE,
48 p_competence_id IN per_competences.competence_id%TYPE,
49 p_rating_level_id IN per_competence_elements.rating_level_id%TYPE := FND_API.G_MISS_NUM,
50 p_effective_date_from IN DATE,
51 p_commit IN VARCHAR2 := FND_API.G_MISS_CHAR,
52 p_validate_only IN VARCHAR2 := FND_API.G_MISS_CHAR,
53 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
54 x_return_status OUT NOCOPY VARCHAR2 ) IS -- 4537865 Added the nocopy hint
55
56 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
57 l_data VARCHAR2(500);
58 l_msg_data VARCHAR2(500);
59 l_msg_index_out NUMBER := 0;
60 l_project_id NUMBER := 0;
61 l_return_status VARCHAR2(30);
62 l_error_message_code VARCHAR2(30);
63 l_bg_id NUMBER := 0;
64 l_element_id NUMBER := 0;
65 l_role_id NUMBER := 0;
66 l_object_version_number NUMBER := 0;
67 CURSOR l_bg_csr IS
68 SELECT business_group_id
69 FROM per_competences
70 WHERE competence_id = p_competence_id;
71
72 BEGIN
73 IF l_enable_log = 'Y' THEN
74 pa_debug.set_err_stack ('PA_COMP_PROFILE_PVT.add_competence_element');
75 END IF;
76
77 SAVEPOINT COMPETENCE_PVT_ADD_COMP_ELE;
78
79 x_return_status := FND_API.G_RET_STS_SUCCESS;
80
81 -- Initialize the message stack if necessary
82 IF p_init_msg_list = FND_API.G_TRUE THEN
83 fnd_msg_pub.initialize;
84 END IF;
85
86 -- Check where the competency can be asssigned to the person
87 Validate_Competency
88 (p_person_id => p_person_id
89 ,p_competence_id => p_competence_id
90 ,x_return_status => l_return_status
91 ,x_error_message_code => l_error_message_code );
92
93 Check_Error(p_return_status => l_return_status,
94 p_error_message_code => l_error_message_code );
95
96 -- Check whether the competency already exists for the person
97 IF check_competence_exists(p_person_id,p_competence_id) = 'Y' THEN
98 Check_Error ( p_return_status => FND_API.G_RET_STS_ERROR
99 ,p_error_message_code => 'PA_PRM_COMP_PROFILE_EXISTS');
100 END IF;
101
102 -- Check whether the competency already exists but is waiting for approval
103 IF Awaiting_Approval(p_person_id,p_competence_id) = 'Y' THEN
104 Check_Error ( p_return_status => FND_API.G_RET_STS_ERROR
105 ,p_error_message_code => 'PA_PRM_COMP_AWAITING_APPROVAL');
106 END IF;
107
108 -- If there are errors, do not proceed to insert the data
109 IF g_noof_errors > 0 THEN
110 x_return_status := FND_API.G_RET_STS_ERROR;
111 IF l_enable_log = 'Y' THEN
112 pa_debug.reset_err_stack;
113 END IF;
114 RETURN;
115 END IF;
116
117 -- If validate only = 'Y' then return at this point.
118 IF p_validate_only = 'Y' THEN
119 x_return_status := FND_API.G_RET_STS_SUCCESS;
120 IF l_enable_log = 'Y' THEN
121 pa_debug.reset_err_stack;
122 END IF;
123 RETURN;
124 END IF;
125
126 -- If no errors and validate only = 'N' then
127 -- call the table handler to create the competency in the temporary
128 -- table
129
130 -- We need the business group id of the competency
131 -- Hence fetch the same from the competences table
132
133 OPEN l_bg_csr;
134 FETCH l_bg_csr INTO l_bg_id;
135 CLOSE l_bg_csr;
136
137 IF g_profile_id = 0 THEN
138 SELECT pa_competence_profiles_s.nextval
139 INTO g_profile_id
140 FROM dual;
141 END IF;
142
143 IF g_person_id = 0 THEN
144 g_person_id := p_person_id;
145 END IF;
146
147 -- Now create the competency in the temporary table
148 create_competence_element
149 (
150 p_profile_id => g_profile_id,
151 p_person_id => p_person_id,
152 p_competence_id => p_competence_id,
153 p_object_version_number => l_object_version_number,
154 p_business_group_id => l_bg_id,
155 p_proficiency_level_id => p_rating_level_id,
156 p_effective_date_from => p_effective_date_from
157 ) ;
158
159 x_return_status := FND_API.G_RET_STS_SUCCESS;
160 IF l_enable_log = 'Y' THEN
161 pa_debug.reset_err_stack;
162 END IF;
163 EXCEPTION
164 WHEN OTHERS THEN
165 IF p_commit = FND_API.G_TRUE THEN
166 ROLLBACK TO COMPETENCE_PVT_ADD_COMP_ELE;
167 END IF;
168 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
169 RAISE;
170 END Add_Competence_Element ;
171
172 PROCEDURE Update_competence_element
173 (p_person_id IN per_competence_elements.person_id%TYPE := FND_API.G_MISS_NUM,
174 p_competence_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
175 p_element_id IN per_competence_elements.competence_element_id%TYPE := FND_API.G_MISS_NUM,
176 p_rating_level_id IN per_competence_elements.rating_level_id%TYPE := FND_API.G_MISS_NUM,
177 p_effective_date_from IN DATE,
178 p_commit IN VARCHAR2 := FND_API.G_MISS_CHAR,
179 p_validate_only IN VARCHAR2 := FND_API.G_MISS_CHAR,
180 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
181 p_object_version_number IN NUMBER,
182 x_object_version_number OUT NOCOPY NUMBER , -- 4537865 Added the nocopy hint
183 x_return_status OUT NOCOPY VARCHAR2 ) IS -- 4537865 Added the nocopy hint
184
185 l_effective_date_from DATE;
186 l_return_status VARCHAR2(30);
187 l_object_version_number NUMBER ;
188 l_error_message_code VARCHAR2(30);
189 l_approval_required BOOLEAN := TRUE;
190 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
191
192 BEGIN
193 -- dbms_output.put_line('Entering Update_competence_element');
194 -- dbms_output.put_line('Effective Date From ' || p_effective_date_from);
195 IF l_enable_log = 'Y' THEN
196 pa_debug.set_err_stack ('PA_COMP_PROFILE_PVT.update_competence_element');
197 END IF;
198 SAVEPOINT COMPETENCE_PVT_UPD_COMP_ELE;
199
203 END IF;
200 -- Initialize the message stack if necessary
201 IF p_init_msg_list = FND_API.G_TRUE THEN
202 fnd_msg_pub.initialize;
204
205 x_return_status := FND_API.G_RET_STS_SUCCESS;
206
207 -- If there are errors, do not proceed to update the data
208 -- Check whether the object version number is correct
209
210 Check_Object_version_number
211 (p_element_id => p_element_id,
212 p_object_version_number => p_object_version_number,
213 x_return_status => l_return_status,
214 x_error_message_code => l_error_message_code );
215
216 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
217 Check_Error (p_return_status => l_return_status
218 ,p_error_message_code => l_error_message_code );
219 END IF;
220
221 -- Check to see if the user is trying to update something which
222 -- is yet not approved.
223 IF p_element_id is not null THEN
224 IF Awaiting_Approval(p_element_id) = 'Y' THEN
225 Check_Error ( p_return_status => FND_API.G_RET_STS_ERROR
226 ,p_error_message_code => 'PA_PRM_CANNOT_UPDATE');
227 END IF;
228 ELSE
229 IF Awaiting_Approval(p_person_id,p_competence_id) = 'Y' THEN
230 Check_Error ( p_return_status => FND_API.G_RET_STS_ERROR
231 ,p_error_message_code => 'PA_PRM_CANNOT_UPDATE');
232 END IF;
233 END IF;
234
235 IF g_noof_errors > 0 THEN
236 x_return_status := FND_API.G_RET_STS_ERROR;
237 IF l_enable_log = 'Y' THEN
238 pa_debug.reset_err_stack;
239 END IF;
240 RETURN;
241 END IF;
242
243 -- If validate only = 'Y' then return at this point.
244 IF p_validate_only = 'Y' THEN
245 x_return_status := FND_API.G_RET_STS_SUCCESS;
246 IF l_enable_log = 'Y' THEN
247 pa_debug.reset_err_stack;
248 END IF;
249 RETURN;
250 END IF;
251
252 IF g_profile_id = 0 THEN
253 SELECT pa_competence_profiles_s.nextval
254 INTO g_profile_id
255 FROM dual;
256 END IF;
257
258 IF g_person_id = 0 THEN
259 g_person_id := p_person_id;
260 END IF;
261
262
263 update_competence_element
264 (p_profile_id => g_profile_id,
265 p_person_id => p_person_id,
266 p_competence_id => p_competence_id,
267 p_competence_element_id => p_element_id,
268 p_object_version_number => p_object_version_number,
269 p_rating_level_id => p_rating_level_id,
270 p_effective_date_from => p_effective_date_from);
271
272 x_return_status := FND_API.G_RET_STS_SUCCESS;
273 IF l_enable_log = 'Y' THEN
274 pa_debug.reset_err_stack;
275 END IF;
276 EXCEPTION
277 WHEN OTHERS THEN
278 IF p_commit = FND_API.G_TRUE THEN
279 ROLLBACK TO COMPETENCE_PVT_UPD_COMP_ELE;
280 END IF;
281
282 -- 4537865
283 x_object_version_number := NULL ;
284 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
285 RAISE;
286 END Update_Competence_element;
287
288 Procedure delete_competence_element
289 ( p_person_id IN NUMBER,
290 p_competence_id IN NUMBER,
291 p_element_id IN NUMBER,
292 p_object_version_number IN NUMBER,
293 p_commit IN VARCHAR2 := FND_API.G_MISS_CHAR,
294 x_return_status OUT NOCOPY VARCHAR2) -- 4537865 Added the nocopy hint
295 IS
296 l_competence_alias VARCHAR2(30);
297 l_competence_name VARCHAR2(240);
298 l_bg_id NUMBER;
299 BEGIN
300
301 -- Check to see if the user is trying to update something which
302 -- is yet not approved.
303 IF p_element_id is not null THEN
304 IF Awaiting_Approval(p_element_id) = 'Y' THEN
305 Check_Error ( p_return_status => FND_API.G_RET_STS_ERROR
306 ,p_error_message_code => 'PA_PRM_CANNOT_DELETE');
307 END IF;
308 ELSE
309 IF Awaiting_Approval(p_person_id,p_competence_id) = 'Y' THEN
310 Check_Error ( p_return_status => FND_API.G_RET_STS_ERROR
311 ,p_error_message_code => 'PA_PRM_CANNOT_DELETE');
312 END IF;
313 END IF;
314
315 hr_competence_element_api.delete_competence_element
316 (p_competence_element_id => p_element_id,
317 p_object_version_number => p_object_version_number);
318 -- 4537865
319 EXCEPTION
320 WHEN OTHERS THEN
321 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
322 fnd_msg_pub.add_exc_msg
323 (p_pkg_name => 'PA_COMP_PROFILE_PVT',
324 p_procedure_name => 'delete_competence_element');
325 -- RAISE is not included as the caller of this API doesnt RAISE
326
327 END delete_competence_element;
328
329 PROCEDURE Check_Element_id
330 (p_object_name IN per_competence_elements.object_name%TYPE := FND_API.G_MISS_CHAR,
331 p_object_id IN per_competence_elements.object_id%TYPE := FND_API.G_MISS_NUM,
332 p_competence_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
333 p_element_id IN per_competence_elements.competence_element_id%TYPE := FND_API.G_MISS_NUM,
334 x_effective_date_from OUT NOCOPY per_competence_elements.effective_date_from%TYPE , -- 4537865 Added the nocopy hint
335 x_return_status OUT NOCOPY VARCHAR2, -- 4537865 Added the nocopy hint
336 x_error_message_code OUT NOCOPY VARCHAR2) IS -- 4537865 Added the nocopy hint
337
338 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
339
340 CURSOR l_check_element_csr IS
341 SELECT object_name,
342 object_id,
343 competence_id,
344 effective_date_from
345 FROM per_competence_elements
346 WHERE competence_element_id = p_element_id;
347 l_check_element_rec l_check_element_csr%ROWTYPE;
348 BEGIN
349 IF l_enable_log = 'Y' THEN
350 pa_debug.set_err_stack ('PA_COMP_PROFILE_PVT.check_element_id');
351 END IF;
352 x_return_status := FND_API.G_RET_STS_SUCCESS;
353 OPEN l_check_element_csr;
354 FETCH l_check_element_csr INTO l_check_element_rec;
355 IF l_check_element_csr%NOTFOUND
356 OR (l_check_element_rec.competence_id <> p_competence_id
357 OR
358 l_check_element_rec.object_id <> p_object_id
359 OR
360 l_check_element_rec.object_name <> p_object_name) THEN
361 x_return_status := FND_API.G_RET_STS_ERROR;
362 x_error_message_code := 'PA_INVALID_ELEMENT_ID';
363 x_effective_date_from := NULL;
364 END IF;
365 CLOSE l_check_element_csr;
366 x_effective_date_from := l_check_element_rec.effective_date_from;
367 IF l_enable_log = 'Y' THEN
368 pa_debug.reset_err_stack;
369 END IF;
370
371 EXCEPTION
372 WHEN OTHERS THEN
373 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
374
375 -- Start : 4537865
376 x_effective_date_from := NULL ;
377 x_error_message_code := SQLERRM;
378 -- ENd : 4537865
379
380 RAISE;
381 END Check_Element_id ;
382
383 PROCEDURE Check_Error (p_return_status IN VARCHAR2,
384 p_error_message_code IN VARCHAR2) IS
385 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
386 BEGIN
387 IF l_enable_log = 'Y' THEN
388 pa_debug.set_err_stack ('PA_COMP_PROFILE_PVT.check_error');
389 END IF;
390 IF p_return_status = FND_API.G_RET_STS_ERROR THEN
391 g_noof_errors := g_noof_errors + 1;
392 pa_utils.add_message (p_app_short_name => 'PA',
393 p_msg_name => p_error_message_code);
394 END IF;
395 IF l_enable_log = 'Y' THEN
396 pa_debug.reset_err_stack;
397 END IF;
398 EXCEPTION
399 WHEN OTHERS THEN
400 RAISE;
401 END check_error;
402
403 PROCEDURE Check_Object_version_number
404 (p_element_id IN per_competence_elements.competence_element_id%TYPE,
405 p_object_version_number IN NUMBER,
406 x_return_status OUT NOCOPY VARCHAR2, -- 4537865 Added the nocopy hint
407 x_error_message_code OUT NOCOPY VARCHAR2) IS -- 4537865 Added the nocopy hint
408
409 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
410
411 CURSOR l_get_obj_vers_csr IS
412 SELECT object_version_number
413 FROM per_competence_elements
414 WHERE competence_element_id = p_element_id;
415 l_obj_version_number NUMBER := 0;
416
417 BEGIN
418 IF l_enable_log = 'Y' THEN
419 pa_debug.set_err_stack ('PA_COMP_PROFILE_PVT.check_object_version_number');
420 END IF;
421 OPEN l_get_obj_vers_csr;
422 FETCH l_get_obj_vers_csr INTO l_obj_version_number;
423 -- dbms_output.put_line (' l obj is '||l_obj_version_number ||
424 -- ' p obj is '||p_object_version_number );
425 IF l_obj_version_number <> p_object_version_number THEN
426 x_return_status := FND_API.G_RET_STS_ERROR;
427 x_error_message_code := 'PA_COMP_OBJ_VERSION_INVALID';
428 END IF;
429 CLOSE l_get_obj_vers_csr;
430 IF l_enable_log = 'Y' THEN
431 pa_debug.reset_err_stack;
432 END IF;
433
434 EXCEPTION
435 WHEN OTHERS THEN
436 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
437
438 -- 4537865 : Start
439 x_error_message_code := SQLERRM;
440 -- 4537865 : End
441
442 RAISE;
443 END Check_Object_Version_Number ;
444
445 Procedure create_competence_element
446 ( p_profile_id IN NUMBER,
447 p_person_id IN NUMBER,
448 p_competence_id IN NUMBER,
449 p_object_version_number IN NUMBER,
450 p_business_group_id IN NUMBER,
451 p_proficiency_level_id IN NUMBER,
452 p_effective_date_from IN DATE
453 )
454 IS
455 l_competence_alias VARCHAR2(30);
456 l_competence_name VARCHAR2(240);
457 l_rating_level_value NUMBER;
458 BEGIN
459
460 IF p_competence_id is not null THEN
461 BEGIN
462 SELECT name,competence_alias
463 INTO l_competence_name,l_competence_alias
464 FROM per_competences
465 WHERE competence_id = p_competence_id;
466 EXCEPTION
467 WHEN OTHERS THEN
468 l_competence_name := null;
469 l_competence_alias := null;
470 END;
471 END IF;
472
473 IF p_proficiency_level_id is not null THEN
474 BEGIN
475 SELECT step_value
476 INTO l_rating_level_value
477 FROM per_rating_levels
478 WHERE rating_level_id = p_proficiency_level_id;
479 EXCEPTION
480 WHEN OTHERS THEN
481 l_rating_level_value := null;
482 END;
483 END IF;
484
485 INSERT INTO PA_COMPETENCE_PROFILES
486 (PROFILE_ID,
487 PERSON_ID,
488 OBJECT_VERSION_NUMBER,
489 BUSINESS_GROUP_ID,
490 COMPETENCE_ID,
491 COMPETENCE_ELEMENT_ID,
492 EFFECTIVE_DATE_FROM,
493 COMPETENCE_ALIAS,
494 COMPETENCE_NAME,
495 DESCRIPTION,
496 RATING_LEVEL_ID,
497 RATING_LEVEL_VALUE,
498 LAST_UPDATED_BY,
499 LAST_UPDATE_DATE,
500 CREATED_BY,
501 CREATION_DATE,
502 OPERATION)
503 VALUES
504 (p_profile_id,
505 p_person_id,
506 p_object_version_number,
507 p_business_group_id,
508 p_competence_id,
509 null,
510 p_effective_date_from,
511 l_competence_alias,
512 l_competence_name,
513 null,
514 p_proficiency_level_id,
515 l_rating_level_value,
516 null,
517 null,
518 null,
519 null,
520 'INSERT');
521
522
523 EXCEPTION
524 WHEN OTHERS THEN
525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
526 end create_competence_element;
527
528 Procedure Update_competence_element
529 (p_profile_id IN NUMBER,
530 p_person_id IN NUMBER,
531 p_competence_id IN NUMBER,
532 p_competence_element_id IN NUMBER,
533 p_object_version_number IN NUMBER,
534 p_rating_level_id IN NUMBER,
535 p_effective_date_from IN DATE)
536 IS
537 l_competence_alias VARCHAR2(30);
538 l_competence_name VARCHAR2(240);
539 l_rating_level_value NUMBER;
540 l_bg_id NUMBER;
541 l_old_rating_level_id NUMBER;
542 l_old_rating_level_value NUMBER;
543 l_old_effective_date_from DATE;
544 BEGIN
545
546 IF p_competence_id is not null THEN
547 BEGIN
548 SELECT name,competence_alias,business_group_id
549 INTO l_competence_name,l_competence_alias,l_bg_id
550 FROM per_competences
551 WHERE competence_id = p_competence_id;
552 EXCEPTION
553 WHEN OTHERS THEN
554 l_competence_name := null;
555 l_competence_alias := null;
556 l_bg_id := null;
557 END;
558 END IF;
559
560 IF p_competence_element_id is not null THEN
561 BEGIN
562 SELECT proficiency_level_id,effective_date_from
563 INTO l_old_rating_level_id,l_old_effective_date_from
564 FROM per_competence_elements
565 WHERE competence_element_id = p_competence_element_id;
566 EXCEPTION
567 WHEN OTHERS THEN
568 l_old_rating_level_id := null;
569 l_old_effective_date_from := null;
570 END;
571 END IF;
572
573 IF l_old_rating_level_id is not null THEN
574 BEGIN
575 SELECT step_value
576 INTO l_old_rating_level_value
577 FROM per_rating_levels
578 WHERE rating_level_id = l_old_rating_level_id;
579 EXCEPTION
580 WHEN OTHERS THEN
581 l_old_rating_level_value := null;
582 END;
583 END IF;
584
585 IF p_rating_level_id is not null THEN
586 BEGIN
587 SELECT step_value
588 INTO l_rating_level_value
589 FROM per_rating_levels
590 WHERE rating_level_id = p_rating_level_id;
591 EXCEPTION
592 WHEN OTHERS THEN
593 l_rating_level_value := null;
594 END;
595 END IF;
596
597 INSERT INTO PA_COMPETENCE_PROFILES
598 (PROFILE_ID,
599 PERSON_ID,
600 OBJECT_VERSION_NUMBER,
601 BUSINESS_GROUP_ID,
602 COMPETENCE_ID,
603 COMPETENCE_ELEMENT_ID,
604 EFFECTIVE_DATE_FROM,
605 RATING_LEVEL_ID,
606 RATING_LEVEL_VALUE,
607 OLD_RATING_LEVEL_ID,
608 OLD_RATING_LEVEL_VALUE,
609 OLD_EFFECTIVE_DATE_FROM,
610 COMPETENCE_ALIAS,
611 COMPETENCE_NAME,
612 DESCRIPTION,
613 LAST_UPDATED_BY,
614 LAST_UPDATE_DATE,
615 CREATED_BY,
616 CREATION_DATE,
617 OPERATION)
618 VALUES
619 (p_profile_id,
620 p_person_id,
621 p_object_version_number,
622 l_bg_id,
623 p_competence_id,
624 p_competence_element_id,
625 p_effective_date_from,
626 p_rating_level_id,
627 l_rating_level_value,
628 l_old_rating_level_id,
629 l_old_rating_level_value,
630 l_old_effective_date_from,
631 l_competence_alias,
632 l_competence_name,
633 null,
634 null,
635 null,
636 null,
637 null,
638 'UPDATE');
639 END;
640
641 Procedure Start_Approval_Process
642 (x_return_status OUT NOCOPY VARCHAR2, -- 4537865 Added the nocopy hint
643 x_msg_count OUT NOCOPY NUMBER, -- 4537865 Added the nocopy hint
644 x_msg_data OUT NOCOPY VARCHAR2) -- 4537865 Added the nocopy hint
645 IS
646
647
648 /* Bug 2697431 - Increased the length of variables l_manager_name, l_manager_display_name,
649 l_employee_name, l_employee_display_name to 360 from 30 */
650
651 l_manager_name VARCHAR2(360);
652 l_manager_display_name VARCHAR2(360);
653 l_manager_id NUMBER;
654
655 l_employee_name VARCHAR2(360) := '';
656 l_employee_display_name VARCHAR2(360) := '';
657 l_employee_id NUMBER;
658 l_message_url VARCHAR2(200);
659
660 /* Bug 2697431 - Increased length of l_data, l_msg_data to 2000 from 500 */
661
662 l_data VARCHAR2(2000);
663 l_msg_data VARCHAR2(2000);
664 l_msg_index_out NUMBER := 0;
665 l_return_status VARCHAR2(30);
666
667 CURSOR c_manager IS
668 SELECT hremp.supervisor_id
669 FROM hr_employees_current_v hremp
670 WHERE hremp.employee_id = g_person_id;
671
672 BEGIN
673
674 --The API Start_Approval_Process has been stubbed out for Bug:4665696 as the workflow "PA Competence Profile Approval Workflow" is to be absoleted.
675
676 /*
677 x_return_status := FND_API.G_RET_STS_SUCCESS;
678 x_msg_count := g_profile_id;
679 x_msg_data := null;
680
681 -- dbms_output.put_line('g_profile_id: ' || g_profile_id);
682 IF g_profile_id = 0 THEN
683 RETURN;
684 END IF;
685
686
687 WF_DIRECTORY.GetUserName('PER',
688 g_person_id,
689 l_employee_name,
690 l_employee_display_name);
691 BEGIN
692
693 OPEN c_manager;
694
695 FETCH c_manager INTO l_manager_id;
696
697 CLOSE c_manager;
698
699 EXCEPTION
700 WHEN NO_DATA_FOUND THEN
701 null;
702 END;
703
704 WF_DIRECTORY.GetUserName('PER',
705 l_manager_id,
706 l_manager_name,
707 l_manager_display_name);
708
709 IF l_manager_name is null THEN
710
711 pa_utils.add_message (p_app_short_name => 'PA',
712 p_msg_name => 'PA_PRM_NO_MANAGER');
713
714 RAISE FND_API.G_EXC_ERROR;
715 END IF;
716
717 WF_ENGINE.CreateProcess('PACOPR',
718 to_char(g_profile_id),
719 'PRO_PROFILE_APPROVAL_FLOW');
720
721 wf_engine.SetItemAttrText( 'PACOPR'
722 , g_profile_id
723 , 'MANAGER_NAME'
727 wf_engine.SetItemAttrNumber( 'PACOPR'
724 , l_manager_name
725 );
726
728 , g_profile_id
729 , 'MANAGER_ID'
730 , l_manager_id
731 );
732
733 wf_engine.SetItemAttrText( 'PACOPR'
734 , g_profile_id
735 , 'MANAGER_DISP_NAME'
739 wf_engine.SetItemAttrText( 'PACOPR'
736 , l_manager_display_name
737 );
738
740 , g_profile_id
741 , 'EMPLOYEE_NAME'
742 , l_employee_name
743 );
744
745 wf_engine.SetItemAttrNumber( 'PACOPR'
746 , g_profile_id
747 , 'EMPLOYEE_ID'
748 , g_person_id
749 );
750
751 wf_engine.SetItemAttrText( 'PACOPR'
752 , g_profile_id
753 , 'EMPLOYEE_DISP_NAME'
754 , l_employee_display_name
755 );
756
757 l_message_url := 'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=275&akRegionCode=PA_MSG_LAYOUT&paProfileId=' || g_profile_id || '&retainAM=Y';
758
759 wf_engine.SetItemAttrText( 'PACOPR'
760 , g_profile_id
761 , 'ATTR_APPROVAL_MESSAGE_URL'
762 , l_message_url
763 );
764
765 WF_ENGINE.StartProcess('PACOPR', g_profile_id);
766
767 -- Set g_profile_id and g_person_id back to 0 .
768 g_profile_id := 0;
769 g_person_id := 0;
770
771 commit;
772
773 EXCEPTION
774 WHEN FND_API.G_EXC_ERROR THEN
775 x_return_status := FND_API.G_RET_STS_ERROR;
776
777 pa_interface_utils_pub.get_messages
778 (p_encoded => FND_API.G_TRUE,
779 p_msg_index => 1,
780 p_msg_count => x_msg_count ,
781 p_msg_data => l_msg_data ,
782 p_data => l_data,
783 p_msg_index_out => l_msg_index_out );
784
785 x_msg_data := l_data;
786 x_msg_count := 1;
787 IF g_profile_id > 0 THEN
788 BEGIN
789 DELETE from pa_competence_profiles
790 where profile_id=g_profile_id;
791 EXCEPTION
792 WHEN OTHERS THEN NULL;
793 END;
794 END IF;
795 -- Set g_profile_id and g_person_id back to 0 .
796 g_profile_id := 0;
797 g_person_id := 0;
798
799 WHEN OTHERS THEN
800 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
801
802 -- 4537865 : Start
803 x_msg_count := 1 ;
804 -- 4537865 : End
805
806 -- Bug 2697431 - Added debug to write proper error message
807 pa_debug.write('PA_COMP_PROFILE_PVT.Start_Approval_Process',SUBSTRB( SQLERRM,1,1900),3); -- 4537865 Replaced substr with substrb
808 x_msg_data:=SUBSTRB( SQLERRM,1,1900); -- 4537865 Replaced substr with substrb
809
810 IF g_profile_id > 0 THEN
811 BEGIN
812 DELETE from pa_competence_profiles
813 where profile_id=g_profile_id;
814 EXCEPTION
815 WHEN OTHERS THEN NULL;
816 END;
817 END IF;
818 -- Set g_profile_id and g_person_id back to 0 .
819 g_profile_id := 0;
820 g_person_id := 0;
821 -- Set g_profile_id and g_person_id back to 0 .
822 g_profile_id := 0;
823 g_person_id := 0;
824 --The API Start_Approval_Process has been stubbed out for Bug:4665696 as the workflow "PA Competence Profile Approval Workflow" is to be absoleted.
825
826 */
827 null; -- added for Bug:4665696
828
829 END Start_Approval_Process;
830
831 PROCEDURE Update_HR
832 (p_profile_id IN NUMBER,
833 x_return_status OUT NOCOPY VARCHAR2, -- 4537865 Added the nocopy hint
834 x_msg_count OUT NOCOPY NUMBER, -- 4537865 Added the nocopy hint
835 x_msg_data OUT NOCOPY VARCHAR2) -- 4537865 Added the nocopy hint
836 IS
837 l_file_val VARCHAR2(100);
838 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
839
840 cursor insert_profile IS
841 SELECT PERSON_ID,
842 OBJECT_VERSION_NUMBER,
843 BUSINESS_GROUP_ID,
844 COMPETENCE_ID ,
845 EFFECTIVE_DATE_FROM,
846 RATING_LEVEL_ID,
847 CREATED_BY,
848 CREATION_DATE
849 FROM PA_COMPETENCE_PROFILES
850 WHERE profile_id = p_profile_id
851 AND operation = 'INSERT';
852
853 cursor update_profile IS
854 SELECT OBJECT_VERSION_NUMBER,
855 COMPETENCE_ELEMENT_ID,
856 EFFECTIVE_DATE_FROM,
857 RATING_LEVEL_ID,
858 LAST_UPDATED_BY,
859 LAST_UPDATE_DATE
860 FROM PA_COMPETENCE_PROFILES
861 WHERE profile_id = p_profile_id
862 AND operation = 'UPDATE';
863
864 cursor delete_profile IS
865 SELECT COMPETENCE_ELEMENT_ID,
866 OBJECT_VERSION_NUMBER
867 FROM PA_COMPETENCE_PROFILES
868 WHERE profile_id = p_profile_id
869 AND operation = 'DELETE';
870
871 l_object_version_number NUMBER := 0;
872 l_bg_id NUMBER := 0;
873 l_version_number NUMBER := 0;
874 l_element_id NUMBER;
875 l_date DATE;
876 l_competence_element_id NUMBER;
877 l_effective_date_from DATE;
878 l_rating_level_id NUMBER;
879 l_last_updated_by NUMBER(15);
880 l_last_update_date DATE;
881 BEGIN
882
883 -- dbms_output.put_line('profile_id : ' || p_profile_id);
884
885 FOR c1 in insert_profile LOOP
886 l_element_id := NULL;
890 -- get the business group id for the person. We cannot
887 l_object_version_number := c1.object_version_number;
888
889 -- If the business group id for the competence is null,
891 -- create a competence element for a person without the
892 -- business group id
893
894 l_bg_id := c1.business_group_id;
895
896 IF c1.business_group_id is null THEN
897 BEGIN
898 select business_group_id
899 into l_bg_id
900 from per_people_x
901 where person_id=c1.person_id;
902 EXCEPTION
903 WHEN OTHERS THEN
904 l_bg_id := null;
905 END;
906 END IF;
907
908 hr_competence_element_api.create_competence_element
909 (
910 p_competence_element_id => l_element_id,
911 p_object_version_number => l_object_version_number,
912 p_type => 'PERSONAL',
913 p_business_group_id => l_bg_id,
914 p_enterprise_id => null,
915 p_competence_id => c1.competence_id,
916 p_proficiency_level_id => c1.rating_level_id,
917 p_high_proficiency_level_id => null,
918 p_weighting_level_id => null,
919 p_rating_level_id => null,
920 p_person_id => c1.person_id,
921 p_job_id => null,
922 p_valid_grade_id => null,
923 p_position_id => null,
924 p_organization_id => null,
925 p_parent_competence_element_id => null,
926 p_activity_version_id => null,
927 p_assessment_id => null,
928 p_assessment_type_id => null,
929 p_mandatory => null,
930 p_effective_date_from => c1.effective_date_from,
931 p_effective_date_to => null,
932 p_group_competence_type => null,
933 p_competence_type => null,
934 p_normal_elapse_duration => null,
935 p_normal_elapse_duration_unit => null,
936 p_sequence_number => null,
937 p_source_of_proficiency_level => null,
938 p_line_score => null,
939 p_certification_date => null,
940 p_certification_method => null,
941 p_next_certification_date => null,
942 p_comments => null,
943 p_attribute_category => null,
944 p_attribute1 => null,
945 p_attribute2 => null,
946 p_attribute3 => null,
947 p_attribute4 => null,
948 p_attribute5 => null,
949 p_attribute6 => null,
950 p_attribute7 => null,
951 p_attribute8 => null,
952 p_attribute9 => null,
953 p_attribute10 => null,
954 p_attribute11 => null,
955 p_attribute12 => null,
956 p_attribute13 => null,
957 p_attribute14 => null,
958 p_attribute15 => null,
959 p_attribute16 => null,
960 p_attribute17 => null,
961 p_attribute18 => null,
962 p_attribute19 => null,
963 p_attribute20 => null,
964 p_effective_date => trunc(sysdate),
965 p_object_id => null,
966 p_object_name => null
967 ) ;
968 END LOOP;
969
970 -- dbms_output.put_line('Opening update_profile');
971
972 for c2 in update_profile LOOP
973
974 -- dbms_output.put_line('In Update');
975 -- dbms_output.put_line('Element Id :' || c2.competence_element_id);
976 -- dbms_output.put_line('Version :' || c2.object_version_number);
977 -- dbms_output.put_line('effective_date_from :' || c2.effective_date_from);
978 -- dbms_output.put_line('rating_level_id :' || c2.rating_level_id);
979
980 -- dbms_output.put_line('Calling update_competence_element');
981 hr_competence_element_api.update_competence_element
982 (p_competence_element_id => c2.competence_element_id,
983 p_object_version_number => c2.object_version_number,
984 p_proficiency_level_id => c2.rating_level_id,
985 p_effective_date_from => c2.effective_date_from,
986 p_effective_date => c2.effective_date_from);
987 -- dbms_output.put_line('After Calling update_competence_element');
988
989 END LOOP;
990
991 /*
992 -- We do not need the code below since we have decided not to start
993 -- the approval process for deletes
994
995 FOR c3 in delete_profile LOOP
996 hr_competence_element_api.delete_competence_element
997 (p_competence_element_id => c3.competence_element_id,
998 p_object_version_number => c3.object_version_number );
999 END LOOP;
1000 */
1001
1002 -- We should delete the rows from pa_competence_profile table since
1003 -- the HR tables have been updated with this information.
1004
1005 BEGIN
1006 DELETE from pa_competence_profiles
1007 where profile_id=p_profile_id;
1008 EXCEPTION
1009 WHEN OTHERS THEN
1010 RAISE;
1011 END;
1012
1013 EXCEPTION
1014 WHEN OTHERS THEN
1015
1016 -- 4537865
1017 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1018 x_msg_count := 1;
1019 x_msg_data := SQLERRM;
1020
1021 Fnd_Msg_Pub.add_exc_msg
1022 ( p_pkg_name => 'PA_COMP_PROFILE_PVT'
1023 , p_procedure_name => 'Validate_Competency'
1024 , p_error_text => x_msg_data);
1025 -- End : 4537865
1026
1027 RAISE;
1028 END Update_HR;
1029
1030 PROCEDURE Validate_Competency
1031 (p_person_id IN NUMBER,
1032 p_competence_id IN NUMBER,
1033 x_return_status OUT NOCOPY VARCHAR2, -- Added the nocopy hint for 4537865
1034 x_error_message_code OUT NOCOPY VARCHAR2) IS -- Added the nocopy hint for 4537865
1035 l_comp_bg_id NUMBER := null;
1036 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1037 BEGIN
1038 BEGIN
1039 SELECT business_group_id
1040 INTO l_comp_bg_id
1041 FROM per_competences
1042 WHERE competence_id = p_competence_id;
1043 EXCEPTION
1044 WHEN NO_DATA_FOUND THEN
1045 x_return_status := FND_API.G_RET_STS_ERROR;
1046 x_error_message_code := 'PA_COMPETENCY_INVALID_AMBIGOUS';
1047 END;
1048
1049 IF (l_comp_bg_id IS NOT NULL
1050 AND l_comp_bg_id <> pa_utils.business_group_id)
1051 THEN
1052 x_return_status := FND_API.G_RET_STS_ERROR;
1053 x_error_message_code := 'PA_PRM_BG_COMP_INVALID';
1054 IF l_enable_log = 'Y' THEN
1055 pa_debug.reset_err_stack;
1056 END IF;
1057 RETURN;
1058 END IF;
1059
1060 x_return_status := FND_API.G_RET_STS_SUCCESS;
1061 --4537865
1062 EXCEPTION
1063 WHEN OTHERS THEN
1064 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1065 x_error_message_code := SQLERRM;
1066
1067 fnd_msg_pub.add_exc_msg
1068 (p_pkg_name => 'PA_COMP_PROFILE_PVT',
1069 p_procedure_name => 'Validate_Competency',
1070 p_error_text => SUBSTRB(SQLERRM,1,240));
1071
1072 RAISE ; -- Included RAISE because Caller of this API needs RAISE
1073 END Validate_Competency;
1074
1075 /*----------------------------------------------------------------------
1076 FUNCTION: Awaiting_Approval
1077 DESCRIPTION: Checks whether there is already an existing competence
1078 for the person waiting for approval.
1079 ----------------------------------------------------------------------*/
1080 FUNCTION Awaiting_Approval
1081 ( p_person_id IN NUMBER,
1082 p_competence_id IN NUMBER)
1083 RETURN VARCHAR2
1084 IS
1085 l_dummy VARCHAR2(1);
1086 BEGIN
1087
1088 SELECT 'x' INTO l_dummy
1089 FROM pa_competence_profiles
1090 WHERE person_id = p_person_id
1091 AND competence_id = p_competence_id ;
1092
1093 RETURN 'Y';
1094
1095 EXCEPTION
1096 WHEN no_data_found THEN
1097 RETURN 'N';
1098 -- Too many rows should not occur in normal cases; Still
1099 -- we will return 'Y' if that happens
1100 WHEN too_many_rows THEN
1101 RETURN 'Y';
1102 WHEN OTHERS THEN
1103 RAISE;
1104 END Awaiting_Approval;
1105
1106 /*----------------------------------------------------------------------
1107 FUNCTION: Awaiting_Approval
1108 DESCRIPTION: This is an overloaded function. Checks whether a particular
1109 competence element is waiting for approval.
1110 ----------------------------------------------------------------------*/
1111 FUNCTION Awaiting_Approval
1112 ( p_element_id IN NUMBER)
1113 RETURN VARCHAR2
1114 IS
1115 l_dummy VARCHAR2(1);
1116 BEGIN
1117
1118 SELECT 'x' INTO l_dummy
1119 FROM pa_competence_profiles
1120 WHERE competence_element_id = p_element_id;
1121
1122 RETURN 'Y';
1123
1124 EXCEPTION
1125 WHEN no_data_found THEN
1126 RETURN 'N';
1127 -- Too many rows should not occur in normal cases; Still
1128 -- we will return 'Y' if that happens
1129 WHEN too_many_rows THEN
1130 RETURN 'Y';
1131 WHEN OTHERS THEN
1132 RAISE;
1133 END Awaiting_Approval;
1134
1135 FUNCTION Check_Competence_Exists
1136 ( p_person_id IN NUMBER,
1137 p_competence_id IN NUMBER)
1138 RETURN VARCHAR2
1139 IS
1140 l_dummy VARCHAR2(1);
1141 BEGIN
1142 SELECT 'x' INTO l_dummy
1143 FROM per_competence_elements
1144 WHERE person_id = p_person_id
1145 AND competence_id = p_competence_id ;
1146
1147 RETURN 'Y';
1148
1149 EXCEPTION
1150 WHEN no_data_found THEN
1151 RETURN 'N';
1152 -- Too many rows should not occur in normal cases; Still
1153 -- we will return 'Y' if that happens
1154 WHEN too_many_rows THEN
1155 RETURN 'Y';
1156 WHEN OTHERS THEN
1157 RAISE;
1158 END Check_Competence_Exists;
1159
1160 Procedure Approval_Message_Body
1161 (document_id in varchar2,
1162 display_type in varchar2,
1163 document in out nocopy varchar2, --4537865 Added the nocopy hint
1164 document_type in out nocopy varchar2) --4537865 Added the nocopy hint
1165 IS
1166 l_profile_id NUMBER;
1167
1168 /* Bug 2697431 Increased lenght of variables l_employee_disp_name and l_manager_disp_name to 360 from 80 */
1169
1170 l_employee_disp_name VARCHAR2(360);
1171 l_manager_disp_name VARCHAR2(360);
1172 l_count NUMBER;
1173
1174 cursor insert_profile
1175 IS
1176 SELECT PERSON_ID,
1177 OBJECT_VERSION_NUMBER,
1178 BUSINESS_GROUP_ID,
1179 COMPETENCE_NAME,
1180 EFFECTIVE_DATE_FROM,
1181 RATING_LEVEL_ID,
1182 RATING_LEVEL_VALUE
1183 FROM PA_COMPETENCE_PROFILES
1184 WHERE profile_id = l_profile_id
1185 AND operation = 'INSERT';
1186
1187 cursor update_profile
1188 IS
1189 SELECT PERSON_ID,
1190 OBJECT_VERSION_NUMBER,
1191 BUSINESS_GROUP_ID,
1192 COMPETENCE_NAME,
1193 EFFECTIVE_DATE_FROM,
1194 RATING_LEVEL_ID,
1195 RATING_LEVEL_VALUE
1196 FROM PA_COMPETENCE_PROFILES
1197 WHERE profile_id = l_profile_id
1198 AND operation = 'UPDATE';
1199
1200 cursor delete_profile
1201 IS
1202 SELECT PERSON_ID,
1203 OBJECT_VERSION_NUMBER,
1204 BUSINESS_GROUP_ID,
1205 COMPETENCE_NAME,
1206 EFFECTIVE_DATE_FROM,
1207 RATING_LEVEL_ID,
1208 RATING_LEVEL_VALUE
1209 FROM PA_COMPETENCE_PROFILES
1210 WHERE profile_id = l_profile_id
1211 AND operation = 'DELETE';
1212
1213 BEGIN
1214 BEGIN
1215 -- if viewing method is through URL
1216 SELECT item_key
1217 INTO l_profile_id
1218 FROM wf_item_activity_statuses
1219 where notification_id = to_number(document_id);
1220 EXCEPTION
1221 WHEN NO_DATA_FOUND THEN
1222 -- if viewing method is email
1223 l_profile_id := to_number(wf_engine.setctx_itemkey);
1224 END;
1225
1226 l_employee_disp_name := wf_engine.GetItemAttrText('PACOPR',
1227 to_char(l_profile_id),
1228 'EMPLOYEE_DISP_NAME');
1229
1230 l_manager_disp_name := wf_engine.GetItemAttrText( 'PACOPR'
1231 , l_profile_id
1232 , 'MANAGER_DISP_NAME'
1233 );
1234
1235 document := '<html>';
1236 document := document || '<body>';
1237 document := document || '<p> ' || l_manager_disp_name || ', </p>';
1238 document := document || '<p> These changes to competency profile requires your approval. Review the changes and select the action for these changes. </p>';
1239
1240 document := document || '<p> Employee Name: ' ||
1241 l_employee_disp_name || ' </p>';
1242
1243 l_count := 0;
1244 FOR c1 in insert_profile LOOP
1245 l_count := l_count + 1;
1246 IF l_count = 1 THEN
1247 document := document || ' <p> <b> New Competencies : </b> </p>';
1248 END IF;
1249 document := document || ' <p> Competence Name: ' || c1.competence_name;
1250 document := document || ' Start Date: ' ||
1251 c1.effective_date_from;
1252 document := document || ' Rating Level: ' ||
1253 c1.rating_level_value || ' </p>';
1254 END LOOP;
1255
1256 l_count := 0;
1257 FOR c2 in update_profile LOOP
1258 l_count := l_count + 1;
1259 IF l_count = 1 THEN
1260 document := document || ' <p> <b> Updated Competencies : </b> </p>';
1261 END IF;
1262 document := document || ' <p> Competence Name: ' || c2.competence_name;
1263 document := document || ' Start Date: ' ||
1264 c2.effective_date_from;
1265 document := document || ' Rating Level: ' ||
1266 c2.rating_level_value || ' </p>';
1267 END LOOP;
1268
1269 /*
1270 -- We do not need the code below since we have decided not to start
1271 -- the approval process for deletes
1272
1273 l_count := 0;
1274 FOR c3 in delete_profile LOOP
1275 l_count := l_count + 1;
1276 IF l_count = 1 THEN
1277 document := document || ' <p> <b> Deleted Competencies : </b> </p>';
1278 END IF;
1279 document := document || ' <p> Competence Name: ' || c3.competence_name;
1280 document := document || ' Start Date: ' ||
1281 c3.effective_date_from;
1282 document := document || ' Rating Level: ' ||
1283 c3.rating_level_value || ' </p>';
1284 END LOOP;
1285
1286 document := document || ' </body> ' || ' </html> ';
1287 document_type := 'text/html';
1288 */
1289 -- 4537865
1290 EXCEPTION
1291 WHEN OTHERS THEN
1292 document_type := 'text/html';
1293 document :=' <html> <body> An Unexpected error has occured ' || SUBSTRB(SQLERRM ,1,240) || ' </body> </html> ';
1294 -- Havent included RAISE because ,this is a message which will be sent as Email .
1295 -- Let the Unexpected Error be notified .
1296 END;
1297
1298 Procedure Clear_Temp_Table
1299 (p_profile_id IN NUMBER,
1300 x_return_status OUT NOCOPY VARCHAR2, -- 4537865 Added the nocopy hint
1301 x_msg_count OUT NOCOPY NUMBER, -- 4537865 Added the nocopy hint
1302 x_msg_data OUT NOCOPY VARCHAR2) -- 4537865 Added the nocopy hint
1303 IS
1304 BEGIN
1305 DELETE FROM pa_competence_profiles
1306 WHERE profile_id=p_profile_id;
1307
1308 x_return_status := FND_API.G_RET_STS_SUCCESS;
1309 x_msg_count := 0;
1310 x_msg_data := null;
1311
1312 EXCEPTION
1313 WHEN OTHERS THEN
1314 null;
1315 END Clear_Temp_Table;
1316
1317 end PA_COMP_PROFILE_PVT ;