[Home] [Help]
PACKAGE BODY: APPS.PA_COMP_PROFILE_PUB
Source
1 PACKAGE BODY PA_COMP_PROFILE_PUB AS
2 -- $Header: PARPRFPB.pls 120.4 2005/11/29 00:41:23 avaithia noship $
3
4 -- Forward declaration of validate_attributes procedure
5 PROCEDURE validate_attributes
6 (
7 p_element_id IN NUMBER := null,
8 p_person_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
9 p_competence_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
10 p_competence_alias IN per_competences.competence_alias%TYPE := FND_API.G_MISS_CHAR,
11 p_competence_name IN per_competences.name%TYPE := FND_API.G_MISS_CHAR,
12 p_rating_level_id IN per_competence_elements.rating_level_id%TYPE := FND_API.G_MISS_NUM,
13 p_rating_level_value IN per_rating_levels.step_value%TYPE := FND_API.G_MISS_NUM,
14 p_effective_date_from IN DATE := FND_API.G_MISS_DATE,
15 p_operation IN VARCHAR2,
16 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
17 x_competence_id OUT NOCOPY per_competences.competence_id%TYPE, --File.Sql.39 bug 4440895
18 x_rating_level_id OUT NOCOPY per_competence_elements.rating_level_id%TYPE, --File.Sql.39 bug 4440895
19 x_effective_date_from OUT NOCOPY DATE); --File.Sql.39 bug 4440895
20
21 /* --------------------------------------------------------------------
22 PROCEDURE Add_Competence_Element
23 PURPOSE This procedure inserts a competence element for a person
24 -------------------------------------------------------------------- */
25
26 PROCEDURE Add_competence_element
27 (
28 p_person_id IN per_competence_elements.person_id%TYPE,
29 p_competence_id IN per_competences.competence_id%TYPE,
30 p_competence_alias IN per_competences.competence_alias%TYPE := FND_API.G_MISS_CHAR,
31 p_competence_name IN per_competences.name%TYPE := FND_API.G_MISS_CHAR,
32 p_rating_level_id IN per_competence_elements.rating_level_id%TYPE := FND_API.G_MISS_NUM,
33 p_rating_level_value IN per_rating_levels.step_value%TYPE := FND_API.G_MISS_NUM,
34 p_effective_date_from IN DATE := FND_API.G_MISS_DATE,
35 p_init_msg_list IN VARCHAR2 := FND_API.G_MISS_CHAR,
36 p_commit IN VARCHAR2 := FND_API.G_MISS_CHAR,
37 p_validate_only IN VARCHAR2 := FND_API.G_MISS_CHAR,
38 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
39 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
40 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
41
42 l_data VARCHAR2(500);
43 l_msg_data VARCHAR2(500);
44 l_msg_index_out NUMBER := 0;
45 l_return_status VARCHAR2(30);
46 l_error_message_code VARCHAR2(30);
47 l_competence_id NUMBER := 0;
48 l_rating_level_id NUMBER := 0;
49 l_mandatory_flag VARCHAR2(1);
50 l_project_id NUMBER := 0;
51 l_role_id NUMBER := 0;
52 l_effective_date_from DATE;
53 l_file_val VARCHAR2(100);
54 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
55
56 BEGIN
57
58 IF l_enable_log = 'Y' THEN
59 pa_debug.init_err_stack ('PA_COMP_PROFILE_PUB.add_competence_element');
60 END IF;
61
62 SAVEPOINT COMPETENCE_PUB_ADD_COMP_ELE;
63
64
65 -- dbms_output.put_line('Person ID : ' || p_person_id);
66 -- dbms_output.put_line('Date : ' || p_effective_date_from);
67
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69
70 -- Initialize the PL/SQL message stack
71 PA_COMP_PROFILE_PVT.g_noof_errors := 0;
72
73 IF p_init_msg_list = FND_API.G_TRUE THEN
74 fnd_msg_pub.initialize;
75 END IF;
76
77 -- Do the standard public api checks
78 validate_attributes
79 (p_person_id => p_person_id,
80 p_competence_id => p_competence_id,
81 p_competence_alias => p_competence_alias,
82 p_competence_name => p_competence_name,
83 p_rating_level_id => p_rating_level_id,
84 p_rating_level_value => p_rating_level_value,
85 p_effective_date_from => p_effective_date_from,
86 p_operation => 'INSERT',
87 x_return_status => l_return_status,
88 x_competence_id => l_competence_id,
89 x_rating_level_id => l_rating_level_id,
90 x_effective_date_from => l_effective_date_from);
91
92 -- dbms_output.put_line('Date 1: ' || l_effective_date_from);
93 -- If there are errors at this stage, there is no point
94 -- in proceeding further since the competence id or rating level
95 -- values are invalid
96
97 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
98 x_return_status:= FND_API.G_RET_STS_ERROR;
99 IF PA_COMP_PROFILE_PVT.g_noof_errors = 1 THEN
100 pa_interface_utils_pub.get_messages
101 (p_encoded => FND_API.G_TRUE,
102 p_msg_index => 1,
103 p_msg_count => x_msg_count ,
104 p_msg_data => l_msg_data ,
105 p_data => l_data,
106 p_msg_index_out => l_msg_index_out );
107 x_msg_data := l_data;
108 x_msg_count := PA_COMP_PROFILE_PVT.g_noof_errors;
109 ELSE
110 x_msg_count := PA_COMP_PROFILE_PVT.g_noof_errors;
111 END IF;
112
113 IF l_enable_log = 'Y' THEN
114 pa_debug.reset_err_stack;
115 END IF;
116
117 RETURN;
118 END IF;
119
120 PA_COMP_PROFILE_PVT.Add_competence_element
121 ( p_person_id => p_person_id,
122 p_competence_id => l_competence_id,
123 p_rating_level_id => l_rating_level_id,
124 p_effective_date_from => l_effective_date_from,
125 p_commit => p_commit,
126 p_validate_only => p_validate_only,
127 x_return_status => l_return_status );
128
129
130 x_msg_count := pa_comp_profile_pvt.g_noof_errors;
131
132 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
133 x_return_status:= FND_API.G_RET_STS_ERROR;
134 IF pa_comp_profile_pvt.g_noof_errors = 1 THEN
135 pa_interface_utils_pub.get_messages
136 (p_encoded => FND_API.G_TRUE,
137 p_msg_index => 1,
138 p_msg_count => x_msg_count ,
139 p_msg_data => l_msg_data ,
140 p_data => l_data,
141 p_msg_index_out => l_msg_index_out );
142 x_msg_data := l_data;
143 x_msg_count := pa_comp_profile_pvt.g_noof_errors;
144 ELSE
145 x_msg_count := pa_comp_profile_pvt.g_noof_errors;
146 END IF;
147
148 IF l_enable_log = 'Y' THEN
149 pa_debug.reset_err_stack;
150 END IF;
151 RETURN;
152 ELSE
153 x_return_status:= FND_API.G_RET_STS_SUCCESS;
154 END IF;
155
156 IF p_commit = FND_API.G_TRUE THEN
157 COMMIT;
158 END IF;
159
160 IF l_enable_log = 'Y' THEN
161 pa_debug.reset_err_stack;
162 END IF;
163
164 EXCEPTION
165 WHEN OTHERS THEN
166 IF p_commit = FND_API.G_TRUE THEN
167 ROLLBACK TO COMPETENCE_PUB_ADD_COMP_ELE;
168 END IF;
169
170 fnd_msg_pub.add_exc_msg
171 (p_pkg_name => 'PA_COMP_PROFILE_PUB',
172 p_procedure_name => pa_debug.g_err_stack );
173
174 x_msg_count := 1;
175
176 IF x_msg_count = 1 THEN
177 pa_interface_utils_pub.get_messages
178 (p_encoded => FND_API.G_TRUE,
179 p_msg_index => 1,
180 p_msg_count => x_msg_count ,
181 p_msg_data => l_msg_data ,
182 p_data => l_data,
183 p_msg_index_out => l_msg_index_out );
184
185 x_msg_data := l_data;
186 END IF;
187
188 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
189
190 END Add_Competence_Element;
191
192 /* --------------------------------------------------------------------
193 PROCEDURE Update_competence_element
194 PURPOSE This procedure updates the competence elements for a person
195 -------------------------------------------------------------------- */
196
197 PROCEDURE Update_competence_element
198 (
199 p_person_id IN per_competence_elements.person_id%TYPE := FND_API.G_MISS_NUM,
200 p_competence_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
201 p_competence_alias IN per_competences.competence_alias%TYPE := FND_API.G_MISS_CHAR,
202 p_competence_name IN per_competences.name%TYPE := FND_API.G_MISS_CHAR,
203 p_element_id IN per_competence_elements.competence_element_id%TYPE := FND_API.G_MISS_NUM,
204 p_rating_level_id IN per_competence_elements.rating_level_id%TYPE := FND_API.G_MISS_NUM,
205 p_rating_level_value IN per_rating_levels.step_value%TYPE := FND_API.G_MISS_NUM,
206 p_effective_date_from IN DATE := FND_API.G_MISS_DATE,
207 p_init_msg_list IN VARCHAR2 := FND_API.G_MISS_CHAR,
208 p_commit IN VARCHAR2 := FND_API.G_MISS_CHAR,
209 p_validate_only IN VARCHAR2 := FND_API.G_MISS_CHAR,
210 p_object_version_number IN NUMBER,
211 x_object_version_number OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
212 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
213 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
214 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
215
216 l_data VARCHAR2(500);
217 l_msg_data VARCHAR2(500);
218 l_msg_index_out NUMBER := 0;
219 l_return_status VARCHAR2(30);
220 l_error_message_code VARCHAR2(30);
221 l_competence_id NUMBER := 0;
222 l_rating_level_id NUMBER := 0;
223 l_mandatory_flag VARCHAR2(1);
224 l_err VARCHAR2(2000);
225 l_effective_date_from DATE;
226 l_file_val VARCHAR2(100);
227 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
228 BEGIN
229
230 -- dbms_output.put_line('Person ID : ' || p_person_id);
231 -- dbms_output.put_line('Entering PUB Update_competence_element');
232 -- dbms_output.put_line('Effective Date From ' || p_effective_date_from);
233
234 IF l_enable_log = 'Y' THEN
235 pa_debug.init_err_stack ('PA_COMP_PROFILE_PUB.update_competence_element');
236 END IF;
237
238 SAVEPOINT COMPETENCE_PUB_UPD_COMP_ELE;
239 x_return_status := FND_API.G_RET_STS_SUCCESS;
240
241 -- Initialize the PL/SQL message stack
242
243 PA_COMP_PROFILE_PVT.g_noof_errors := 0;
244
245 IF p_init_msg_list = FND_API.G_TRUE THEN
246 fnd_msg_pub.initialize;
247 END IF;
248
249 -- Validate the atttributes
250
251 -- dbms_output.put_line('Calling Validate Attributes');
252
253 validate_attributes
254 (p_element_id => p_element_id,
255 p_person_id => p_person_id,
256 p_competence_id => p_competence_id,
257 p_competence_alias => p_competence_alias,
258 p_competence_name => p_competence_name,
259 p_rating_level_id => p_rating_level_id,
260 p_rating_level_value => p_rating_level_value,
261 p_effective_date_from => p_effective_date_from,
262 p_operation => 'UPDATE',
263 x_return_status => l_return_status,
264 x_competence_id => l_competence_id,
265 x_rating_level_id => l_rating_level_id,
266 x_effective_date_from => l_effective_date_from);
267
268
269 -- dbms_output.put_line('After Calling Validate Attributes');
270 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
271
272 -- dbms_output.put_line('Error in Validate Attributes');
273 x_return_status:= FND_API.G_RET_STS_ERROR;
274 IF PA_COMP_PROFILE_PVT.g_noof_errors = 1 THEN
275 pa_interface_utils_pub.get_messages
276 (p_encoded => FND_API.G_TRUE,
277 p_msg_index => 1,
278 p_msg_count => x_msg_count ,
279 p_msg_data => l_msg_data ,
280 p_data => l_data,
281 p_msg_index_out => l_msg_index_out );
282 x_msg_data := l_data;
283 x_msg_count := PA_COMP_PROFILE_PVT.g_noof_errors;
284 ELSE
285 x_msg_count := PA_COMP_PROFILE_PVT.g_noof_errors;
286 END IF;
287 IF l_enable_log = 'Y' THEN
288 pa_debug.reset_err_stack;
289 END IF;
290 RETURN;
291 END IF;
292
293 PA_COMP_PROFILE_PVT.update_competence_element
294 ( p_person_id => p_person_id
295 ,p_competence_id => l_competence_id
296 ,p_element_id => p_element_id
297 ,p_rating_level_id => l_rating_level_id
298 ,p_effective_date_from => l_effective_date_from
299 ,p_commit => p_commit
300 ,p_validate_only => p_validate_only
301 ,p_object_version_number => p_object_version_number
302 ,x_object_version_number => x_object_version_number
303 ,x_return_status => l_return_status );
304
305 x_return_status := l_return_status;
306
307 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
308 x_return_status:= FND_API.G_RET_STS_ERROR;
309 IF pa_comp_profile_pvt.g_noof_errors = 1 THEN
310 pa_interface_utils_pub.get_messages
311 (p_encoded => FND_API.G_TRUE,
312 p_msg_index => 1,
313 p_msg_count => x_msg_count ,
314 p_msg_data => l_msg_data ,
315 p_data => l_data,
316 p_msg_index_out => l_msg_index_out );
317 x_msg_data := l_data;
318 x_msg_count := pa_comp_profile_pvt.g_noof_errors;
319 ELSE
320 x_msg_count := pa_comp_profile_pvt.g_noof_errors;
321 END IF;
322
323 IF l_enable_log = 'Y' THEN
324 pa_debug.reset_err_stack;
325 END IF;
326 RETURN;
327 ELSE
328 x_return_status:= FND_API.G_RET_STS_SUCCESS;
329 END IF;
330
331 IF p_commit = FND_API.G_TRUE THEN
332 COMMIT;
333 END IF;
334
335 IF l_enable_log = 'Y' THEN
336 pa_debug.reset_err_stack;
337 END IF;
338
339 EXCEPTION
340 WHEN OTHERS THEN
341
342 l_err := SQLERRM;
343
344 IF p_commit = FND_API.G_TRUE THEN
345 ROLLBACK TO COMPETENCE_PUB_UPD_COMP_ELE;
346 END IF;
347
348 fnd_msg_pub.add_exc_msg
349 (p_pkg_name => 'PA_COMP_PROFILE_PUB',
350 p_procedure_name => pa_debug.g_err_stack );
351
352 x_msg_count := 1;
353
354 IF x_msg_count = 1 THEN
355 pa_interface_utils_pub.get_messages
356 (p_encoded => FND_API.G_TRUE,
357 p_msg_index => 1,
358 p_msg_count => x_msg_count ,
359 p_msg_data => l_msg_data ,
360 p_data => l_data,
361 p_msg_index_out => l_msg_index_out );
362
363 x_msg_data := l_data;
364 END IF;
365
366 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
367
368 END update_competence_element ;
369
370 PROCEDURE delete_competence_element
371 (p_person_id IN per_competence_elements.person_id%TYPE := FND_API.G_MISS_NUM,
372 p_competence_id IN per_competence_elements.competence_id%TYPE := FND_API.G_MISS_NUM,
373 p_element_id IN per_competence_elements.competence_element_id%TYPE := FND_API.G_MISS_NUM,
374 p_init_msg_list IN VARCHAR2 := FND_API.G_MISS_CHAR,
375 p_commit IN VARCHAR2 := FND_API.G_MISS_CHAR,
376 p_validate_only IN VARCHAR2 := FND_API.G_MISS_CHAR,
377 p_object_version_number IN NUMBER,
378 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
379 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
380 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
381 IS
382 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
383 l_err VARCHAR2(2000);
384 l_data VARCHAR2(500);
385 l_msg_data VARCHAR2(500);
386 l_msg_count NUMBER;
387 l_msg_index_out NUMBER := 0;
388 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
389 BEGIN
390 PA_COMP_PROFILE_PVT.delete_competence_element
391 ( p_person_id => p_person_id,
392 p_competence_id => p_competence_id,
393 p_element_id => p_element_id,
394 p_object_version_number => p_object_version_number,
395 p_commit => p_commit,
396 x_return_status => l_return_status );
397 x_return_status := l_return_status ; -- 4537865
398 EXCEPTION
399 WHEN OTHERS THEN
400
401 l_err := SQLERRM;
402
403 fnd_msg_pub.add_exc_msg
404 (p_pkg_name => 'PA_COMP_PROFILE_PUB',
405 p_procedure_name => 'delete_competence_element');
406
407 x_msg_count := 1;
408
409 IF x_msg_count = 1 THEN
410 pa_interface_utils_pub.get_messages
411 (p_encoded => FND_API.G_TRUE,
412 p_msg_index => 1,
413 p_msg_count => l_msg_count ,
414 p_msg_data => l_msg_data ,
415 p_data => l_data,
416 p_msg_index_out => l_msg_index_out );
417
418 x_msg_data := l_data;
419 END IF;
420
421 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
422 END;
423
424 /* --------------------------------------------------------------------
425 PROCEDURE validate_attributes
426 PURPOSE This procedure validates the change
427 Validates :
428 Rating Level and Level Id: It returns back the rating level
429 id if rating level value is passed.
430
431 Competence name and Id: It returns back the Competence_Id
432 if Competence Name is passed.
433
434 If the operation is update, you are not allowed to
435 update the competence_id on the record.
436
437 Effective Date From: If returns back the sysdate is the date
438 is null or missing. Validates that the effective date is
439 in the within limits of the dates the competence itself is
440 valid
441 -------------------------------------------------------------------- */
442 PROCEDURE validate_attributes
443 (
444 p_element_id IN NUMBER := null,
445 p_person_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
446 p_competence_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
447 p_competence_alias IN per_competences.competence_alias%TYPE := FND_API.G_MISS_CHAR,
448 p_competence_name IN per_competences.name%TYPE := FND_API.G_MISS_CHAR,
449 p_rating_level_id IN per_competence_elements.rating_level_id%TYPE := FND_API.G_MISS_NUM,
450 p_rating_level_value IN per_rating_levels.step_value%TYPE := FND_API.G_MISS_NUM,
451 p_effective_date_from IN DATE := FND_API.G_MISS_DATE,
452 p_operation IN VARCHAR2,
453 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
454 x_competence_id OUT NOCOPY per_competences.competence_id%TYPE, --File.Sql.39 bug 4440895
455 x_rating_level_id OUT NOCOPY per_competence_elements.rating_level_id%TYPE, --File.Sql.39 bug 4440895
456 x_effective_date_from OUT NOCOPY DATE) --File.Sql.39 bug 4440895
457 IS
458 l_return_status VARCHAR2(30);
459 l_error_message_code VARCHAR2(30);
460 l_in_competence_id NUMBER := 0;
461
462 -- angie changed back to original to fix bug 1569499
463 l_in_rating_level_id NUMBER := 0;
464
465 l_in_competence_alias VARCHAR2(30);
466 l_in_competence_name VARCHAR2(240);
467 l_in_rating_level_value NUMBER := 0;
468 l_competence_id NUMBER := 0;
469 l_rating_level_id NUMBER := 0;
470 l_valid VARCHAR2(1);
471 l_old_competence_id NUMBER;
472 l_old_comp_name VARCHAR2(240);
473 l_old_comp_alias VARCHAR2(30);
474 l_exists VARCHAR2(1);
475 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
476 BEGIN
477
478 IF l_enable_log = 'Y' THEN
479 pa_debug.init_err_stack ('PA_COMP_PROFILE_PUB.standard_pub_checks');
480 END IF;
481 x_return_status:= FND_API.G_RET_STS_SUCCESS;
482
483 -- Check whether competence alias or id is passed and call competence utils
484 -- to validate
485
486 l_in_competence_id := p_competence_id;
487 IF l_in_competence_id = FND_API.G_MISS_NUM THEN
488 l_in_competence_id := NULL;
489 END IF;
490
491 -- angie had commented out following three lines to fix bug 1569499 which was not
492 -- correct. So uncommented out again.
493 l_in_rating_level_id := p_rating_level_id;
494
495 IF l_in_rating_level_id = FND_API.G_MISS_NUM THEN
496 l_in_rating_level_id := NULL;
497 END IF;
498
499 l_in_competence_alias := p_competence_alias;
500
501 IF l_in_competence_alias = FND_API.G_MISS_CHAR THEN
502 l_in_competence_alias := NULL;
503 END IF;
504
505 l_in_competence_name := p_competence_name;
506
507 IF l_in_competence_name = FND_API.G_MISS_CHAR THEN
508 l_in_competence_name := NULL;
509 END IF;
510
511 l_in_rating_level_value := p_rating_level_value;
512
513 IF l_in_rating_level_value = FND_API.G_MISS_NUM THEN
514 l_in_rating_level_value := NULL;
515 END IF;
516
517 IF p_operation = 'INSERT' THEN
518 IF l_in_competence_name is not null AND
519 l_in_competence_alias is not null THEN
520 BEGIN
521 SELECT 'Y'
522 INTO l_exists
523 FROM per_competences
524 WHERE name = l_in_competence_name
525 AND competence_alias = l_in_competence_alias;
526 EXCEPTION
527 WHEN NO_DATA_FOUND THEN
528 l_return_status := FND_API.G_RET_STS_ERROR;
529 PA_COMP_PROFILE_PVT.Check_Error
530 (p_return_status => l_return_status,
531 p_error_message_code => 'PA_PRM_INVALID_ALIAS');
532
533 END;
534 END IF;
535 END IF;
536
537 IF p_operation = 'UPDATE' THEN
538 IF p_element_id is not null THEN
539 SELECT comp_ele.competence_id,
540 comp.name,
541 comp.competence_alias
542 INTO l_old_competence_id,
543 l_old_comp_name,
544 l_old_comp_alias
545 FROM per_competence_elements comp_ele,
546 per_competences comp
547 WHERE COMPETENCE_ELEMENT_ID = p_element_id
548 AND comp.competence_id = comp_ele.competence_id;
549
550 IF (nvl(l_in_competence_name,l_old_comp_name) <> l_old_comp_name) OR
551 (nvl(l_in_competence_alias,l_old_comp_alias) <> l_old_comp_alias) OR
552 (nvl(l_in_competence_id,l_old_competence_id) <> l_old_competence_id)
553 THEN
554 l_return_status := FND_API.G_RET_STS_ERROR;
555 PA_COMP_PROFILE_PVT.Check_Error
556 (p_return_status => l_return_status,
557 p_error_message_code => 'PA_PRM_CANNOT_UPD_COMP');
558 END IF;
559 END IF;
560 END IF;
561
562 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
563 x_return_status:= FND_API.G_RET_STS_ERROR;
564 IF l_enable_log = 'Y' THEN
565 pa_debug.reset_err_stack;
566 END IF;
567 RETURN;
568 END IF;
569
570 -- dbms_output.put_line('Calling Check_CompName_Or_Id');
571
572 pa_hr_competence_utils.Check_CompName_Or_Id
573 ( p_competence_id => l_in_competence_id
574 ,p_competence_alias => l_in_competence_alias
575 ,p_competence_name => l_in_competence_name
576 ,p_check_id_flag => pa_startup.g_check_id_flag
577 ,x_competence_id => l_competence_id
578 ,x_return_status => l_return_status
579 ,x_error_msg_code => l_error_message_code);
580
581
582 -- dbms_output.put_line('After Calling Check_CompName_Or_Id: ' || l_return_status);
583 PA_COMP_PROFILE_PVT.Check_Error
584 (p_return_status => l_return_status,
585 p_error_message_code => l_error_message_code );
586
587 --If the return status is invalid, we cannot proceed further
588 -- All further validations require a competency id to be present
589
590 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
591 x_return_status:= FND_API.G_RET_STS_ERROR;
592 IF l_enable_log = 'Y' THEN
593 pa_debug.reset_err_stack;
594 END IF;
595 RETURN;
596 END IF;
597
598 -- Check to see if the operation is UPDATE and the user is trying to
599 -- update the competency. You are not allowed to update the competency
600
601 IF p_operation = 'UPDATE' THEN
602 IF p_element_id is not null THEN
603 BEGIN
604 SELECT competence_id
605 INTO l_old_competence_id
606 FROM per_competence_elements
607 WHERE competence_element_id = p_element_id;
608
609 IF l_old_competence_id <> l_competence_id THEN
610 PA_COMP_PROFILE_PVT.Check_Error
611 (p_return_status => FND_API.G_RET_STS_ERROR,
612 p_error_message_code => 'PA_PRM_CANNOT_UPD_COMP');
613 END IF;
614 EXCEPTION
615 WHEN OTHERS THEN
616 null;
617 END;
618 END IF;
619 END IF;
620
621 x_competence_id := l_competence_id;
622
623 IF p_effective_date_from is null OR
624 p_effective_date_from = FND_API.G_MISS_DATE THEN
625 x_effective_date_from := trunc(sysdate);
626 ELSE
627 x_effective_date_from := p_effective_date_from;
628 END IF;
629
630 -- Verify if the x_effective_date_from is within the date limits
631 -- of the competences itself.
632 -- dbms_output.put_line('Effective Date: ' || x_effective_date_from);
633 -- dbms_output.put_line('Co Id : ' || x_competence_id);
634
635 BEGIN
636 SELECT 'Y'
637 INTO l_valid
638 FROM per_competences
639 WHERE x_effective_date_from BETWEEN date_from AND NVL(date_to,x_effective_date_from)
640 AND competence_id=x_competence_id;
641 EXCEPTION
642 WHEN NO_DATA_FOUND THEN
643 l_return_status := FND_API.G_RET_STS_ERROR;
644 PA_COMP_PROFILE_PVT.Check_Error
645 (p_return_status => l_return_status,
646 p_error_message_code => 'PA_PRM_INVALID_DATE');
647 END;
648
649 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
650 x_return_status:= FND_API.G_RET_STS_ERROR;
651 IF l_enable_log = 'Y' THEN
652 pa_debug.reset_err_stack;
653 END IF;
654 RETURN;
655 END IF;
656
657 -- Check whether rating level or value is passed and call competence utils
658 -- to validate . Do this only if either rating level id or rating level
659 -- value is passed. In certain cases (like in a public delete api)
660 -- these values will not be passed , hence do not validate if both the
661 -- values are not passed
662
663 IF (l_in_rating_level_id IS NULL AND l_in_rating_level_value
664 IS NULL ) THEN
665 IF l_enable_log = 'Y' THEN
666 pa_debug.reset_err_stack;
667 END IF;
668 RETURN;
669 END IF;
670
671 -- dbms_output.put_line('Calling Check_Rating_Level_Or_Id');
672
673 pa_hr_competence_utils.Check_Rating_Level_Or_Id
674 ( p_competence_id => l_competence_id
675 ,p_rating_level_id => l_in_rating_level_id
676 ,p_rating_level => l_in_rating_level_value
677 ,p_check_id_flag => pa_startup.g_check_id_flag
678 ,x_rating_level_id => l_rating_level_id
679 ,x_return_status => l_return_status
680 ,x_error_msg_code => l_error_message_code) ;
681
682 -- dbms_output.put_line('After Calling Check_Rating_Level_Or_Id: ' || l_return_status);
683
684 PA_COMP_PROFILE_PVT.Check_Error
685 (p_return_status => l_return_status,
686 p_error_message_code => l_error_message_code );
687
688 IF PA_COMP_PROFILE_PVT.g_noof_errors > 0 THEN
689 x_return_status:= FND_API.G_RET_STS_ERROR;
690 ELSE
691 x_return_status:= FND_API.G_RET_STS_SUCCESS;
692 END IF;
693 IF l_enable_log = 'Y' THEN
694 pa_debug.reset_err_stack;
695 END IF;
696 x_rating_level_id := l_rating_level_id;
697
698
699 EXCEPTION
700 WHEN OTHERS THEN
701 -- 4537865 : RESET Other OUT params too.
702 x_rating_level_id := NULL ;
703 x_effective_date_from := NULL ;
704 x_competence_id := NULL ;
705
706 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
707 RAISE;
708 END validate_attributes;
709
710 Procedure Start_Approval_Process
711 (x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
712 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
713 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
714 IS
715 BEGIN
716 /* --this API has been stubbed out for Bug:4665696
717 PA_COMP_PROFILE_PVT.Start_Approval_Process
718 (x_return_status => x_return_status,
719 x_msg_count => x_msg_count,
720 x_msg_data => x_msg_data);
721 --this API has been stubbed out for Bug:4665696
722 */
723 null; --added for bug:4665696
724 END;
725
726 PROCEDURE Update_HR(
727 itemtype in varchar2,
728 itemkey in varchar2,
729 actid in number,
730 funcmode in varchar2,
731 resultout in out NOCOPY varchar2) --File.Sql.39 bug 4440895
732 IS
733 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
734 l_msg_count NUMBER;
735 l_msg_data VARCHAR2(2000);
736 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
737 BEGIN
738 --
739 -- RUN mode - normal process execution
740 --
741
742 IF (funcmode = 'RUN') THEN
743
744 PA_COMP_PROFILE_PVT.Update_HR
745 (p_profile_id => to_number(itemkey),
746 x_return_status => l_return_status,
747 x_msg_count => l_msg_count,
748 x_msg_data => l_msg_data);
749
750 -- dbms_output.put_line('After PA_COMP_PROFILE_PVT.Update_H');
751 resultout := 'COMPLETE';
752 return;
753
754 END IF;
755
756 IF (funcmode = 'CANCEL') THEN
757 null;
758 return;
759 END IF;
760
761 EXCEPTION
762 WHEN OTHERS THEN
763 -- dbms_output.put_line('Exception Raised');
764 wf_core.context('PA_COMP_PROFILE_PUB', 'Update_HR',
765 itemtype, itemkey, to_char(actid), funcmode);
766 raise;
767 END Update_HR;
768
769 PROCEDURE Clear_Temp_Table(
770 itemtype in varchar2,
771 itemkey in varchar2,
772 actid in number,
773 funcmode in varchar2,
774 resultout in out NOCOPY varchar2) --File.Sql.39 bug 4440895
775 IS
776 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
777 l_msg_count NUMBER;
778 l_msg_data VARCHAR2(2000);
779 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
780 BEGIN
781 --
782 -- RUN mode - normal process execution
783 --
784
785
786 IF (funcmode = 'RUN') THEN
787
788 PA_COMP_PROFILE_PVT.Clear_Temp_Table
789 (p_profile_id => to_number(itemkey),
790 x_return_status => l_return_status,
791 x_msg_count => l_msg_count,
792 x_msg_data => l_msg_data);
793
794 -- dbms_output.put_line('After PA_COMP_PROFILE_PVT.Update_H');
795 resultout := 'COMPLETE';
796 return;
797
798 END IF;
799
800 IF (funcmode = 'CANCEL') THEN
801 null;
802 return;
803 END IF;
804
805 EXCEPTION
806 WHEN OTHERS THEN
807 -- dbms_output.put_line('Exception Raised');
808 wf_core.context('PA_COMP_PROFILE_PUB', 'Update_HR',
809 itemtype, itemkey, to_char(actid), funcmode);
810 raise;
811 END;
812
813 procedure approval_message_body (
814 document_id in varchar2,
815 display_type in varchar2,
816 document in out NOCOPY varchar2, --File.Sql.39 bug 4440895
817 document_type in out NOCOPY varchar2) --File.Sql.39 bug 4440895
818 IS
819 BEGIN
820 PA_COMP_PROFILE_PVT.approval_message_body
821 (document_id => document_id,
822 display_type => display_type,
823 document => document,
824 document_type => document_type);
825 END;
826
827 Procedure Set_Person(p_person_id IN NUMBER)
828 IS
829 BEGIN
830 g_assignment_id := 0;
831 g_person_id := p_person_id;
832 END;
833
834 Procedure Set_Assignment(p_assignment_id IN NUMBER)
835 IS
836 BEGIN
837 g_person_id := 0;
838 g_assignment_id := p_assignment_id;
839 END;
840
841 /* --------------------------------------------------------------------
842 FUNCTION Get_Select_Flag
843 PURPOSE This function is called from the view PA_ALL_COMPETENCES_LOV_V.
844 It returns 'Y' for the global person (g_person_id) or
845 assignment (g_assignment_id) if a competence
846 exists for it. Returns 'N' if it does not.
847 -------------------------------------------------------------------- */
848
849 Function Get_Select_Flag(p_competence_id IN NUMBER)
850 RETURN VARCHAR2
851 IS
852 l_exists VARCHAR2(1) := 'N';
853 BEGIN
854 IF (g_person_id = 0 or g_person_id is null) AND
855 (g_assignment_id = 0 or g_assignment_id is null) THEN
856 RETURN l_exists;
857 END IF;
858
859 IF g_person_id > 0 THEN
860
861 SELECT 'Y'
862 INTO l_exists
863 FROM per_competence_elements
864 WHERE person_id = g_person_id
865 AND competence_id = p_competence_id;
866
867 ELSIF g_assignment_id > 0 THEN
868
869 SELECT 'Y'
870 INTO l_exists
871 FROM per_competence_elements
872 WHERE object_id = g_assignment_id
873 AND competence_id = p_competence_id
874 and OBJECT_NAME = 'OPEN_ASSIGNMENT' ; -- Included for 4765876
875
876 END IF;
877
878 RETURN l_exists;
879
880 EXCEPTION
881 WHEN NO_DATA_FOUND THEN
882 RETURN l_exists;
883
884 WHEN OTHERS THEN
885 RETURN l_exists;
886 END;
887
888 Procedure Get_User_Info(p_user_id IN VARCHAR2,
889 x_Person_id OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
890 x_Resource_id OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
891 x_resource_name OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
892 IS
893 l_employee_id VARCHAR2(30);
894 l_resource_id VARCHAR2(30);
895 l_resource_name VARCHAR(240);
896
897 BEGIN
898 SELECT employee_id
899 INTO l_employee_id
900 from fnd_user
901 where user_id=to_number(p_user_id);
902 x_Person_id := l_employee_id;
903
904 BEGIN
905 SELECT resource_id
906 INTO l_resource_id
907 FROM pa_resource_txn_attributes
908 WHERE person_id=l_employee_id;
909 x_Resource_id := l_resource_id;
910 EXCEPTION
911 WHEN NO_DATA_FOUND THEN
912 x_resource_id := '';
913 END;
914
915 SELECT full_name
916 INTO l_resource_name
917 FROM per_all_people_f
918 WHERE person_id = l_employee_id
919 and trunc(sysdate) between trunc(effective_start_date)
920 and trunc(effective_end_date);
921 x_resource_name := l_resource_name;
922
923 EXCEPTION
924 WHEN NO_DATA_FOUND THEN
925 x_Person_id := '';
926 x_resource_id := '';
927 x_resource_name := '';
928 WHEN OTHERS THEN
929 x_Person_id := '';
930 x_resource_id := '';
931 x_resource_name := '';
932 END;
933
934 Procedure Get_User_Info(p_user_id IN VARCHAR2,
935 x_Person_id OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
936 x_Resource_id OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
937 IS
938 l_employee_id VARCHAR2(30);
939 l_resource_id VARCHAR2(30);
940 BEGIN
941 SELECT employee_id
942 INTO l_employee_id
943 from fnd_user
944 where user_id=to_number(p_user_id);
945 x_Person_id := l_employee_id;
946
947 SELECT resource_id
948 INTO l_resource_id
949 FROM pa_resource_txn_attributes
950 WHERE person_id=l_employee_id;
951 x_Resource_id := l_resource_id;
952 EXCEPTION
953 WHEN NO_DATA_FOUND THEN
954 x_Person_id := '';
955 x_resource_id := '';
956 WHEN OTHERS THEN
957 x_Person_id := '';
958 x_resource_id := '';
959 END;
960
961
962 FUNCTION Get_person_business_group
963 (P_Person_id IN NUMBER
964 )
965 RETURN NUMBER
966 IS
967 l_bg_id NUMBER := 0;
968 BEGIN
969
970 SELECT BUSINESS_GROUP_ID
971 into l_bg_id
972 FROM PER_PEOPLE_X
973 WHERE person_id=P_Person_id;
974
975 RETURN l_bg_id;
976 EXCEPTION
977 WHEN OTHERS THEN
978 RETURN -999;
979 END;
980
981 end PA_COMP_PROFILE_PUB;