[Home] [Help]
PACKAGE BODY: APPS.PA_COMPETENCE_PUB
Source
1 PACKAGE BODY pa_competence_pub AS
2 -- $Header: PACOMPPB.pls 120.4 2005/08/23 04:31:18 sunkalya noship $
3
4 --
5 -- PROCEDURE
6 -- Add_Competence_Element
7 -- PURPOSE
8 -- This procedure creates the competence elements for
9 -- a project role or an open assignment
10 -- HISTORY
11 -- 11-JUL-2000 R. Krishnamurthy Created
12 --
13
14 PROCEDURE Add_competence_element
15 ( p_object_name IN per_competence_elements.object_name%TYPE := FND_API.G_MISS_CHAR,
16 p_object_id IN per_competence_elements.object_id%TYPE := FND_API.G_MISS_NUM,
17 p_competence_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
18 p_competence_alias IN per_competences.competence_alias%TYPE := FND_API.G_MISS_CHAR,
19 p_competence_name IN per_competences.name%TYPE := FND_API.G_MISS_CHAR,
20 p_rating_level_id IN per_competence_elements.rating_level_id%TYPE := FND_API.G_MISS_NUM,
21 p_rating_level_value IN per_rating_levels.step_value%TYPE := FND_API.G_MISS_NUM,
22 p_mandatory_flag IN per_competence_elements.mandatory%TYPE := FND_API.G_MISS_CHAR,
23 p_init_msg_list IN VARCHAR2 := FND_API.G_MISS_CHAR,
24 p_commit IN VARCHAR2 := FND_API.G_MISS_CHAR,
25 p_validate_only IN VARCHAR2 := FND_API.G_MISS_CHAR,
26 x_element_rowid OUT NOCOPY ROWID, --File.Sql.39 bug 4440895
27 x_element_id OUT NOCOPY per_competence_elements.competence_element_id%TYPE, --File.Sql.39 bug 4440895
28 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
29 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
30 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
31
32 l_data VARCHAR2(500);
33 l_msg_data VARCHAR2(500);
34 l_msg_index_out NUMBER := 0;
35 l_return_status VARCHAR2(30);
36 l_error_message_code VARCHAR2(30);
37 l_competence_id NUMBER := 0;
38 l_rating_level_id NUMBER := 0;
39 l_mandatory_flag VARCHAR2(1);
40 l_project_id NUMBER := 0;
41 l_role_id NUMBER := 0;
42 l_object_id PA_ASSIGNMENTS_PUB.assignment_id_tbl_type;
43
44 BEGIN
45 pa_debug.init_err_stack ('pa_competence_pub.add_competence_element');
46 SAVEPOINT COMPETENCE_PUB_ADD_COMP_ELE;
47 x_return_status := FND_API.G_RET_STS_SUCCESS;
48
49 -- Initialize the PL/SQL message stack
50 pa_competence_pvt.g_noof_errors := 0;
51 IF p_init_msg_list = FND_API.G_TRUE THEN
52 fnd_msg_pub.initialize;
53 END IF;
54
55 -- Do the standard public api checks
56 standard_pub_checks
57 (p_object_name => p_object_name,
58 p_object_id => p_object_id,
59 p_competence_id => p_competence_id,
60 p_competence_alias => p_competence_alias,
61 p_competence_name => p_competence_name,
62 p_rating_level_id => p_rating_level_id,
63 p_rating_level_value => p_rating_level_value,
64 p_operation => 'INSERT',
65 x_return_status => l_return_status,
66 x_competence_id => l_competence_id,
67 x_rating_level_id => l_rating_level_id );
68 -- If there are errors at this stage, there is no point
69 -- in proceeding further since the competence id or rating level
70 -- values are invalid
71
72 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
73 x_return_status:= FND_API.G_RET_STS_ERROR;
74 IF pa_competence_pvt.g_noof_errors = 1 THEN
75 pa_interface_utils_pub.get_messages
76 (p_encoded => FND_API.G_TRUE,
77 p_msg_index => 1,
78 p_msg_count => x_msg_count ,
79 p_msg_data => l_msg_data ,
80 p_data => l_data,
81 p_msg_index_out => l_msg_index_out );
82 x_msg_data := l_data;
83 x_msg_count := pa_competence_pvt.g_noof_errors;
84 ELSE
85 x_msg_count := pa_competence_pvt.g_noof_errors;
86 END IF;
87 pa_debug.reset_err_stack;
88 RETURN;
89 END IF;
90
91 IF p_object_name = 'PROJECT_ROLE' THEN
92 -- Check whether it is a valid role
93 pa_role_utils.Check_Role_Name_Or_Id
94 ( p_role_id => p_object_id
95 ,p_role_name => NULL
96 ,p_check_id_flag => pa_startup.g_check_id_flag
97 ,x_role_id => l_role_id
98 ,x_return_status => l_return_status
99 ,x_error_message_code => l_error_message_code );
100
101 pa_competence_pvt.Check_Error
102 (p_return_status => l_return_status,
103 p_error_message_code => l_error_message_code );
104 END IF;
105
106 l_object_id(1).assignment_id := p_object_id;
107
108 pa_competence_pvt.Add_competence_element
109 ( p_object_name => p_object_name,
110 p_object_id => l_object_id,
111 p_competence_id => l_competence_id,
112 p_rating_level_id => l_rating_level_id,
113 p_mandatory_flag => p_mandatory_flag,
114 p_commit => p_commit,
115 p_validate_only => p_validate_only,
116 x_element_rowid => x_element_rowid,
117 x_element_id => x_element_id,
118 x_return_status => l_return_status );
119
120 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
121 x_return_status:= FND_API.G_RET_STS_ERROR;
122
123 IF pa_competence_pvt.g_noof_errors = 1 THEN
124 pa_interface_utils_pub.get_messages
125 (p_encoded => FND_API.G_TRUE,
126 p_msg_index => 1,
127 p_msg_count => x_msg_count ,
128 p_msg_data => l_msg_data ,
129 p_data => l_data,
130 p_msg_index_out => l_msg_index_out );
131 x_msg_data := l_data;
132 x_msg_count := pa_competence_pvt.g_noof_errors;
133 ELSE
134 x_msg_count := pa_competence_pvt.g_noof_errors;
135 END IF;
136
137 pa_debug.reset_err_stack;
138 RETURN;
139 ELSE
140 x_return_status:= FND_API.G_RET_STS_SUCCESS;
141 END IF;
142
143 IF p_commit = FND_API.G_TRUE THEN
144 COMMIT;
145 END IF;
146
147 EXCEPTION
148 WHEN OTHERS THEN
149
150 IF p_commit = FND_API.G_TRUE THEN
151 ROLLBACK TO COMPETENCE_PUB_ADD_COMP_ELE;
152 END IF;
153
154 fnd_msg_pub.add_exc_msg
155 (p_pkg_name => 'PA_COMPETENCE_PUB',
156 p_procedure_name => pa_debug.g_err_stack );
157
158 x_msg_count := 1;
159
160 IF x_msg_count = 1 THEN
161 pa_interface_utils_pub.get_messages
162 (p_encoded => FND_API.G_TRUE,
163 p_msg_index => 1,
164 p_msg_count => x_msg_count ,
165 p_msg_data => l_msg_data ,
166 p_data => l_data,
167 p_msg_index_out => l_msg_index_out );
168
169 x_msg_data := l_data;
170 END IF;
171
172 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
173
174 -- RESET the other OUT PARAMS also : 4537865
175
176 x_element_rowid := NULL ;
177 x_element_id := NULL ;
178
179 END Add_Competence_Element;
180
181
182 PROCEDURE Update_competence_element
183 (p_object_name IN per_competence_elements.object_name%TYPE := FND_API.G_MISS_CHAR,
184 p_object_id IN per_competence_elements.object_id%TYPE := FND_API.G_MISS_NUM,
185 p_competence_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
186 p_competence_alias IN per_competences.competence_alias%TYPE := FND_API.G_MISS_CHAR,
187 p_competence_name IN per_competences.name%TYPE := FND_API.G_MISS_CHAR,
188 p_element_rowid IN ROWID := FND_API.G_MISS_CHAR,
189 p_element_id IN per_competence_elements.competence_element_id%TYPE := FND_API.G_MISS_NUM,
190 p_rating_level_id IN per_competence_elements.rating_level_id%TYPE := FND_API.G_MISS_NUM,
191 p_rating_level_value IN per_rating_levels.step_value%TYPE := FND_API.G_MISS_NUM,
192 p_mandatory_flag IN per_competence_elements.mandatory%TYPE := FND_API.G_MISS_CHAR,
193 p_init_msg_list IN VARCHAR2 := FND_API.G_MISS_CHAR,
194 p_commit IN VARCHAR2 := FND_API.G_MISS_CHAR,
195 p_validate_only IN VARCHAR2 := FND_API.G_MISS_CHAR,
196 p_object_version_number IN NUMBER,
197 x_object_version_number OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
198 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
199 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
200 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
201
202 l_data VARCHAR2(500);
203 l_msg_data VARCHAR2(500);
204 l_msg_index_out NUMBER := 0;
205 l_return_status VARCHAR2(30);
206 l_error_message_code VARCHAR2(30);
207 l_competence_id NUMBER := 0;
208 l_rating_level_id NUMBER := 0;
209 l_mandatory_flag VARCHAR2(1);
210 l_err VARCHAR2(2000);
211 l_element_id NUMBER := null;
212 BEGIN
213 pa_debug.init_err_stack ('pa_competence_pub.update_competence_element');
214 SAVEPOINT COMPETENCE_PUB_UPD_COMP_ELE;
215 x_return_status := FND_API.G_RET_STS_SUCCESS;
216 -- Initialize the PL/SQL message stack
217 pa_competence_pvt.g_noof_errors := 0;
218 IF p_init_msg_list = FND_API.G_TRUE THEN
219 fnd_msg_pub.initialize;
220 END IF;
221
222 IF p_element_id = FND_API.G_MISS_NUM THEN
223 l_element_id := null;
224 ELSE
225 l_element_id := p_element_id;
226 END IF;
227
228 -- Do the standard public api checks
229 standard_pub_checks
230 (p_element_id => l_element_id,
231 p_object_name => p_object_name,
232 p_object_id => p_object_id,
233 p_competence_id => p_competence_id,
234 p_competence_alias => p_competence_alias,
235 p_competence_name => p_competence_name,
236 p_rating_level_id => p_rating_level_id,
237 p_rating_level_value => p_rating_level_value,
238 p_operation => 'UPDATE',
239 x_return_status => l_return_status,
240 x_competence_id => l_competence_id,
241 x_rating_level_id => l_rating_level_id );
242
243 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
244 x_return_status:= FND_API.G_RET_STS_ERROR;
245 IF pa_competence_pvt.g_noof_errors = 1 THEN
246 pa_interface_utils_pub.get_messages
247 (p_encoded => FND_API.G_TRUE,
248 p_msg_index => 1,
249 p_msg_count => x_msg_count ,
250 p_msg_data => l_msg_data ,
251 p_data => l_data,
252 p_msg_index_out => l_msg_index_out );
253 x_msg_data := l_data;
254 x_msg_count := pa_competence_pvt.g_noof_errors;
255 ELSE
256 x_msg_count := pa_competence_pvt.g_noof_errors;
257 END IF;
258 pa_debug.reset_err_stack;
259 RETURN;
260 END IF;
261
262 pa_competence_pvt.update_competence_element
263 ( p_object_name => p_object_name
264 ,p_object_id => p_object_id
265 ,p_competence_id => l_competence_id
266 ,p_element_rowid => p_element_rowid
267 ,p_element_id => p_element_id
268 ,p_rating_level_id => l_rating_level_id
269 ,p_mandatory_flag => p_mandatory_flag
270 ,p_commit => p_commit
271 ,p_validate_only => p_validate_only
272 ,p_object_version_number => p_object_version_number
273 ,x_object_version_number => x_object_version_number
274 ,x_return_status => l_return_status );
275
276 x_return_status := l_return_status;
277
278 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
279 x_return_status:= FND_API.G_RET_STS_ERROR;
280 IF pa_competence_pvt.g_noof_errors = 1 THEN
281 pa_interface_utils_pub.get_messages
282 (p_encoded => FND_API.G_TRUE,
283 p_msg_index => 1,
284 p_msg_count => x_msg_count ,
285 p_msg_data => l_msg_data ,
286 p_data => l_data,
287 p_msg_index_out => l_msg_index_out );
288
289 x_msg_data := l_data;
290 x_msg_count := pa_competence_pvt.g_noof_errors;
291 ELSE
292 x_msg_count := pa_competence_pvt.g_noof_errors;
293 END IF;
294 pa_debug.reset_err_stack;
295 RETURN;
296 ELSE
297 x_return_status:= FND_API.G_RET_STS_SUCCESS;
298 END IF;
299
300 IF p_commit = FND_API.G_TRUE THEN
301 COMMIT;
302 END IF;
303 pa_debug.reset_err_stack;
304
305 EXCEPTION
306 WHEN OTHERS THEN
307
308 l_err := SQLERRM;
309
310 IF p_commit = FND_API.G_TRUE THEN
311 ROLLBACK TO COMPETENCE_PUB_UPD_COMP_ELE;
312 END IF;
313
314 fnd_msg_pub.add_exc_msg
315 (p_pkg_name => 'PA_COMPETENCE_PUB',
316 p_procedure_name => pa_debug.g_err_stack );
317
318 x_msg_count := 1;
319
320 IF x_msg_count = 1 THEN
321 pa_interface_utils_pub.get_messages
322 (p_encoded => FND_API.G_TRUE,
323 p_msg_index => 1,
324 p_msg_count => x_msg_count ,
325 p_msg_data => l_msg_data ,
326 p_data => l_data,
327 p_msg_index_out => l_msg_index_out );
328
329 x_msg_data := l_data;
330 END IF;
331
332 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
333
334 -- RESET the other OUT PARAMS also : 4537865
335 x_object_version_number := NULL ;
336
337 END update_competence_element ;
338
339 PROCEDURE delete_competence_element
340 (p_object_name IN per_competence_elements.object_name%TYPE := FND_API.G_MISS_CHAR,
341 p_object_id IN per_competence_elements.object_id%TYPE := FND_API.G_MISS_NUM,
342 p_competence_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
343 p_competence_alias IN per_competences.competence_alias%TYPE := FND_API.G_MISS_CHAR,
344 p_competence_name IN per_competences.name%TYPE := chr(0),
345 p_element_rowid IN ROWID := FND_API.G_MISS_CHAR,
346 p_element_id IN per_competence_elements.competence_element_id%TYPE := FND_API.G_MISS_NUM,
347 p_init_msg_list IN VARCHAR2 := FND_API.G_MISS_CHAR,
348 p_commit IN VARCHAR2 := FND_API.G_MISS_CHAR,
349 p_validate_only IN VARCHAR2 := FND_API.G_MISS_CHAR,
350 p_object_version_number IN NUMBER,
351 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
352 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
353 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
354
355 l_data VARCHAR2(500);
356 l_msg_data VARCHAR2(500);
357 l_msg_index_out NUMBER := 0;
358 l_return_status VARCHAR2(30);
359 l_error_message_code VARCHAR2(30);
360 l_competence_id NUMBER := 0;
361 l_rating_level_id NUMBER := 0;
362 BEGIN
363 pa_debug.init_err_stack ('pa_competence_pub.delete_competence_element');
364 SAVEPOINT COMPETENCE_PUB_DEL_COMP_ELE;
365 x_return_status := FND_API.G_RET_STS_SUCCESS;
366 -- Initialize the PL/SQL message stack
367 pa_competence_pvt.g_noof_errors := 0;
368 IF p_init_msg_list = FND_API.G_TRUE THEN
369 fnd_msg_pub.initialize;
370 END IF;
371
372 -- Do the standard public api checks
373 standard_pub_checks
374 (p_object_name => p_object_name,
375 p_object_id => p_object_id,
376 p_competence_id => p_competence_id,
377 p_competence_alias => p_competence_alias,
378 p_competence_name => p_competence_name,
379 p_operation => 'DELETE',
380 x_return_status => l_return_status,
381 x_competence_id => l_competence_id,
382 x_rating_level_id => l_rating_level_id );
383
384 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
385 x_return_status:= FND_API.G_RET_STS_ERROR;
386 IF pa_competence_pvt.g_noof_errors = 1 THEN
387 pa_interface_utils_pub.get_messages
388 (p_encoded => FND_API.G_TRUE,
389 p_msg_index => 1,
390 p_msg_count => x_msg_count ,
394 x_msg_data := l_data;
391 p_msg_data => l_msg_data ,
392 p_data => l_data,
393 p_msg_index_out => l_msg_index_out );
395 x_msg_count := pa_competence_pvt.g_noof_errors;
396 ELSE
397 x_msg_count := pa_competence_pvt.g_noof_errors;
398 END IF;
399 pa_debug.reset_err_stack;
400 RETURN;
401 END IF;
402 -- Call the pvt delete competence element api now
403 pa_competence_pvt.delete_competence_element
404 (p_object_name => p_object_name,
405 p_object_id => p_object_id,
406 p_competence_id => l_competence_id,
407 p_element_rowid => p_element_rowid,
408 p_element_id => p_element_id,
409 p_commit => p_commit,
410 p_validate_only => p_validate_only,
411 p_object_version_number => p_object_version_number,
412 x_return_status => l_return_status );
413 x_return_status := l_return_status;
414
415 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
416 x_return_status := FND_API.G_RET_STS_ERROR;
417 IF pa_competence_pvt.g_noof_errors = 1 THEN
418 pa_interface_utils_pub.get_messages
419 (p_encoded => FND_API.G_TRUE,
420 p_msg_index => 1,
421 p_msg_count => x_msg_count ,
422 p_msg_data => l_msg_data ,
423 p_data => l_data,
424 p_msg_index_out => l_msg_index_out );
425
426 x_msg_data := l_data;
427 x_msg_count := pa_competence_pvt.g_noof_errors;
428 ELSE
429 x_msg_count := pa_competence_pvt.g_noof_errors;
430 END IF;
431 pa_debug.reset_err_stack;
432 RETURN;
433 ELSE
434 x_return_status:= FND_API.G_RET_STS_SUCCESS;
435 END IF;
436
437 IF p_commit = FND_API.G_TRUE THEN
438 COMMIT;
439 END IF;
440 pa_debug.reset_err_stack;
441
442 EXCEPTION
443 WHEN OTHERS THEN
444
445 IF p_commit = FND_API.G_TRUE THEN
446 ROLLBACK TO COMPETENCE_PUB_DEL_COMP_ELE;
447 END IF;
448
449 fnd_msg_pub.add_exc_msg
450 (p_pkg_name => 'PA_COMPETENCE_PUB',
451 p_procedure_name => pa_debug.g_err_stack );
452
453 x_msg_count := 1;
454
455 IF x_msg_count = 1 THEN
456 pa_interface_utils_pub.get_messages
457 (p_encoded => FND_API.G_TRUE,
458 p_msg_index => 1,
459 p_msg_count => x_msg_count ,
460 p_msg_data => l_msg_data ,
461 p_data => l_data,
462 p_msg_index_out => l_msg_index_out );
463
464 x_msg_data := l_data;
465 END IF;
466
467 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
468 END delete_competence_element;
469
470 PROCEDURE standard_pub_checks
471 (
472 p_element_id IN NUMBER := null,
473 p_object_name IN per_competence_elements.object_name%TYPE := FND_API.G_MISS_CHAR,
474 p_object_id IN per_competence_elements.object_id%TYPE := FND_API.G_MISS_NUM,
475 p_competence_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
476 p_competence_alias IN per_competences.competence_alias%TYPE := FND_API.G_MISS_CHAR,
477 p_competence_name IN per_competences.name%TYPE := FND_API.G_MISS_CHAR,
478 p_rating_level_id IN per_competence_elements.rating_level_id%TYPE := FND_API.G_MISS_NUM,
479 p_rating_level_value IN per_rating_levels.step_value%TYPE := FND_API.G_MISS_NUM,
480 p_operation IN VARCHAR2,
481 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
482 x_competence_id OUT NOCOPY per_competences.competence_id%TYPE, --File.Sql.39 bug 4440895
483 x_rating_level_id OUT NOCOPY per_competence_elements.rating_level_id%TYPE ) IS --File.Sql.39 bug 4440895
484
485 l_return_status VARCHAR2(30);
486 l_error_message_code VARCHAR2(30);
487 l_in_competence_id NUMBER := 0;
488 -- angie changed back to original to fix bug 1569499
489 l_in_rating_level_id NUMBER := 0;
490 l_in_competence_alias VARCHAR2(30);
491 l_in_competence_name VARCHAR2(240);
492 l_in_rating_level_value NUMBER := 0;
493 l_competence_id NUMBER := 0;
494 l_rating_level_id NUMBER := 0;
495 l_old_competence_id NUMBER;
496 l_old_comp_name VARCHAR2(240);
497 l_old_comp_alias VARCHAR2(30);
498 l_exists VARCHAR2(1);
499 BEGIN
500 pa_debug.init_err_stack ('pa_competence_pub.standard_pub_checks');
501 x_return_status:= FND_API.G_RET_STS_SUCCESS;
502 -- Check whether the object name is one of the supported names
503 IF (p_object_name = FND_API.G_MISS_CHAR) OR
504 (p_object_name IS NULL) OR
505 (p_object_name NOT IN ('PROJECT_ROLE','OPEN_ASSIGNMENT')) THEN
506 pa_competence_pvt.Check_Error
507 (p_return_status => FND_API.G_RET_STS_ERROR,
508 p_error_message_code => 'PA_INVALID_COMP_OBJECT_NAME');
509 END IF;
510
511 -- Check whether competence alias or id is passed and call competence utils
512 -- to validate
513 l_in_competence_id := p_competence_id;
517
514 IF l_in_competence_id = FND_API.G_MISS_NUM THEN
515 l_in_competence_id := NULL;
516 END IF;
518 -- angie had commented out following three lines to fix bug 1569499 which was not
519 -- correct. So uncommented out again.
520 l_in_rating_level_id := p_rating_level_id;
521 IF l_in_rating_level_id = FND_API.G_MISS_NUM THEN
522 l_in_rating_level_id := NULL;
523 END IF;
524
525 l_in_competence_alias := p_competence_alias;
526 IF l_in_competence_alias = FND_API.G_MISS_CHAR THEN
527 l_in_competence_alias := NULL;
528 END IF;
529 l_in_competence_name := p_competence_name;
530 IF l_in_competence_name = FND_API.G_MISS_CHAR THEN
531 l_in_competence_name := NULL;
532 END IF;
533 l_in_rating_level_value := p_rating_level_value;
534 IF l_in_rating_level_value = FND_API.G_MISS_NUM THEN
535 l_in_rating_level_value := NULL;
536 END IF;
537
538 IF p_operation = 'INSERT' THEN
539 IF l_in_competence_name is not null AND
540 l_in_competence_alias is not null THEN
541 BEGIN
542 SELECT 'Y'
543 INTO l_exists
544 FROM per_competences
545 WHERE name = l_in_competence_name
546 AND competence_alias = l_in_competence_alias;
547 EXCEPTION
548 WHEN NO_DATA_FOUND THEN
549 l_return_status := FND_API.G_RET_STS_ERROR;
550 pa_competence_pvt.Check_Error
551 (p_return_status => l_return_status,
552 p_error_message_code => 'PA_PRM_INVALID_ALIAS');
553
554 END;
555 END IF;
556 END IF;
557
558 IF p_operation = 'UPDATE' THEN
559 IF p_element_id is not null THEN
560 SELECT comp_ele.competence_id,
561 comp.name,
562 comp.competence_alias
563 INTO l_old_competence_id,
564 l_old_comp_name,
565 l_old_comp_alias
566 FROM per_competence_elements comp_ele,
567 per_competences comp
568 WHERE COMPETENCE_ELEMENT_ID = p_element_id
569 AND comp.competence_id = comp_ele.competence_id;
570
571 IF (nvl(l_in_competence_name,l_old_comp_name) <> l_old_comp_name) OR
572 (nvl(l_in_competence_alias,l_old_comp_alias) <> l_old_comp_alias) OR
573 (nvl(l_in_competence_id,l_old_competence_id) <> l_old_competence_id)
574 THEN
575 l_return_status := FND_API.G_RET_STS_ERROR;
576 pa_competence_pvt.Check_Error
577 (p_return_status => l_return_status,
578 p_error_message_code => 'PA_PRM_CANNOT_UPD_COMP');
579 END IF;
580 END IF;
581 END IF;
582
583 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
584 x_return_status:= FND_API.G_RET_STS_ERROR;
585 pa_debug.reset_err_stack;
586 RETURN;
587 END IF;
588
589 pa_hr_competence_utils.Check_CompName_Or_Id
590 ( p_competence_id => l_in_competence_id
591 ,p_competence_alias => l_in_competence_alias
592 ,p_competence_name => l_in_competence_name
593 ,p_check_id_flag => pa_startup.g_check_id_flag
594 ,x_competence_id => l_competence_id
595 ,x_return_status => l_return_status
596 ,x_error_msg_code => l_error_message_code);
597
598 pa_competence_pvt.Check_Error
599 (p_return_status => l_return_status,
600 p_error_message_code => l_error_message_code );
601
602 --If the return status is invalid, we cannot proceed further
603 -- All further validations require a competency id to be present
604
605 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
606 x_return_status:= FND_API.G_RET_STS_ERROR;
607 pa_debug.reset_err_stack;
608 RETURN;
609 END IF;
610
611 x_competence_id := l_competence_id;
612
613 -- Check whether rating level or value is passed and call competence utils
614 -- to validate . Do this only if either rating level id or rating level
615 -- value is passed. In certain cases (like in a public delete api)
616 -- these values will not be passed , hence do not validate if both the
617 -- values are not passed
618 IF (l_in_rating_level_id IS NULL AND l_in_rating_level_value
619 IS NULL ) THEN
620 pa_debug.reset_err_stack;
621 RETURN;
622 END IF;
623
624 pa_hr_competence_utils.Check_Rating_Level_Or_Id
625 ( p_competence_id => l_competence_id
626 ,p_rating_level_id => l_in_rating_level_id
627 ,p_rating_level => l_in_rating_level_value
628 ,p_check_id_flag => pa_startup.g_check_id_flag
629 ,x_rating_level_id => l_rating_level_id
630 ,x_return_status => l_return_status
631 ,x_error_msg_code => l_error_message_code) ;
632 pa_competence_pvt.Check_Error
633 (p_return_status => l_return_status,
634 p_error_message_code => l_error_message_code );
635 IF pa_competence_pvt.g_noof_errors > 0 THEN
636 x_return_status:= FND_API.G_RET_STS_ERROR;
637 ELSE
638 x_return_status:= FND_API.G_RET_STS_SUCCESS;
639 END IF;
640 pa_debug.reset_err_stack;
641 x_rating_level_id := l_rating_level_id;
645
642 EXCEPTION
643 WHEN OTHERS THEN
644 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
646 -- RESET the other OUT PARAMS also : 4537865
647 x_competence_id := NULL ;
648 x_rating_level_id := NULL ;
649
650 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_COMPETENCE_PUB',
651 p_procedure_name => 'standard_pub_checks',
652 P_ERROR_TEXT => SUBSTRB(SQLERRM,1,240));
653 -- 4537865 : End
654 RAISE;
655 END standard_pub_checks;
656
657 /* --------------------------------------------------------------------
658 PROCEDURE: Mass_Exec_Process_Competences
659 PURPOSE : This API is called from the client side when competences
660 are added in Mass Mode for requirements. It validates
661 the competences and calles the Mass Transaction API to start
662 the Mass Transaction Workflow.
663 -------------------------------------------------------------------- */
664 PROCEDURE Mass_Exec_Process_Competences
665 ( p_asgn_update_mode IN VARCHAR2 := FND_API.G_MISS_CHAR
666 ,p_project_id IN pa_project_assignments.project_id%TYPE
667 ,p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type
668 ,p_competence_id_tbl IN SYSTEM.pa_num_tbl_type
669 ,p_competence_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type
670 ,p_competence_alias_tbl IN SYSTEM.pa_varchar2_30_tbl_type
671 ,p_rating_level_id_tbl IN SYSTEM.pa_num_tbl_type
672 ,p_rating_level_value_tbl IN SYSTEM.pa_num_tbl_type
673 ,p_mandatory_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type
674 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
675 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
676 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
677 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
678 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
679 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
680 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
681 )
682
683 IS
684
685 l_wf_mode VARCHAR2(200);
686 l_validate_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
687 l_element_id NUMBER;
688 l_competence_id NUMBER;
689 l_rating_level_id NUMBER;
690 l_msg_index_out NUMBER;
691 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
692 l_rating_level_id_tbl SYSTEM.pa_num_tbl_type := p_rating_level_id_tbl;
693 l_rating_level_value_tbl SYSTEM.pa_num_tbl_type := p_rating_level_value_tbl;
694 l_competence_id_tbl SYSTEM.pa_num_tbl_type := p_competence_id_tbl;
695
696 BEGIN
697
698 --This API is only called to do standard validations
699
700 x_return_status := FND_API.G_RET_STS_SUCCESS;
701
702 -- Initialize the PL/SQL message stack
703 pa_competence_pvt.g_noof_errors := 0;
704
705 IF p_init_msg_list = FND_API.G_TRUE THEN
706 fnd_msg_pub.initialize;
707 END IF;
708
709 IF l_rating_level_id_tbl.count > 0 THEN
710 FOR i in 1..l_rating_level_id_tbl.count LOOP
711 IF l_rating_level_id_tbl(i) = 0 THEN
712 l_rating_level_id_tbl(i) := NULL;
713 END IF;
714 IF l_competence_id_tbl(i) = 0 THEN
715 l_competence_id_tbl(i) := NULL;
716 END IF;
717 IF l_rating_level_value_tbl(i) = 0 THEN
718 l_rating_level_value_tbl(i) := NULL;
719 END IF;
720 END LOOP;
721 END IF;
722
723 IF p_competence_id_tbl.count > 0 THEN
724 FOR j in 1..p_assignment_id_tbl.count LOOP
725 FOR i in 1..p_competence_id_tbl.count LOOP
726 standard_pub_checks
727 (p_element_id => l_element_id,
728 p_object_name => 'OPEN_ASSIGNMENT',
729 p_object_id => p_assignment_id_tbl(J),
730 p_competence_id => l_competence_id_tbl(I),
731 p_competence_alias => p_competence_alias_tbl(I),
732 p_competence_name => p_competence_name_tbl(I),
733 p_rating_level_id => l_rating_level_id_tbl(I),
734 p_rating_level_value => l_rating_level_value_tbl(I),
735 p_operation => 'UPDATE',
736 x_return_status => l_return_status,
737 x_competence_id => l_competence_id,
738 x_rating_level_id => l_rating_level_id_tbl(I));
739 END LOOP;
740 END LOOP;
741 END IF;
742
743 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
744 RAISE FND_API.G_EXC_ERROR;
745 END IF;
746
747 -- if p_validate_only=false and there are no errors then start the
748 -- workflow process.
749
750 IF p_validate_only = FND_API.G_FALSE AND
751 l_validate_status = FND_API.G_RET_STS_SUCCESS
752 THEN
753
754 l_wf_mode := PA_MASS_ASGMT_TRX.G_MASS_UPDATE_COMPETENCIES;
755
756 --start the mass WF
757 PA_MASS_ASGMT_TRX.Start_Mass_Asgmt_Trx_Wf(
758 p_mode => l_wf_mode
759 ,p_project_id => p_project_id
760 ,p_action => PA_MASS_ASGMT_TRX.G_SAVE
761 ,p_assignment_id_tbl => p_assignment_id_tbl
762 ,p_competence_id_tbl => l_competence_id_tbl
766 ,p_mandatory_flag_tbl => p_mandatory_flag_tbl
763 ,p_competence_name_tbl => p_competence_name_tbl
764 ,p_competence_alias_tbl => p_competence_alias_tbl
765 ,p_rating_level_id_tbl => l_rating_level_id_tbl
767 ,x_return_status => x_return_status
768 );
769
770
771 END IF;
772
773 EXCEPTION
774 WHEN FND_API.G_EXC_ERROR THEN
775 x_return_status := FND_API.G_RET_STS_ERROR;
776 x_msg_count := FND_MSG_PUB.Count_Msg;
777
778 IF x_msg_count = 1 THEN
779 pa_interface_utils_pub.get_messages
780 (p_encoded => FND_API.G_TRUE,
781 p_msg_index => 1,
782 p_data => x_msg_data,
783 p_msg_index_out => l_msg_index_out );
784 END IF;
785 -- 4537865 : WHEN OTHERS Block Included.
786 WHEN OTHERS THEN
787 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
788 x_msg_count := 1;
789 x_msg_data := SUBSTRB(SQLERRM,1,240);
790
791 Fnd_Msg_Pub.add_exc_msg(p_pkg_name => 'PA_COMPETENCE_PUB',
792 p_procedure_name => 'Mass_Exec_Process_Competences',
793 p_error_text => x_msg_data);
794 RAISE ;
795
796 END Mass_Exec_Process_Competences;
797
798 /* --------------------------------------------------------------------
799 PROCEDURE: Mass_Process_Competences
800 PURPOSE : This API is called from the Mass Transaction Workflow to
801 process the competences. It passes to the API the table
802 of assignments and the list of competences to be processed.
803 The list of competences will be process for each and every
804 assignment. If the competence exists for the assignment
805 then the Update API is called to update the HR Competence
806 Element record. If the competence does not exist, the
807 INSERT API is called to create the new competence element.
808 If even one competence processing errors out for a given
809 assignment, then all the competence changes are rolled back
810 for that assignment.
811 -------------------------------------------------------------------- */
812
813 PROCEDURE Mass_Process_Competences
814 ( p_project_id IN pa_project_assignments.project_id%TYPE,
815 p_assignment_tbl IN SYSTEM.pa_num_tbl_type,
816 p_competence_id_tbl IN SYSTEM.pa_num_tbl_type,
817 p_competence_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type,
818 p_competence_alias_tbl IN SYSTEM.pa_varchar2_30_tbl_type,
819 p_rating_level_id_tbl IN SYSTEM.pa_num_tbl_type,
820 p_mandatory_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type,
821 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
822 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
823 p_commit IN VARCHAR2 := FND_API.G_FALSE,
824 x_success_assignment_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, /* Added NOCOPY for bug#2674619 */
825 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
826 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
827 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
828 IS
829 l_element_rowid ROWID;
830 l_element_id NUMBER;
831 l_assignment_id NUMBER := -1;
832 l_assignment_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
833 l_msg_data VARCHAR2(500);
834 l_msg_count NUMBER := 0;
835 l_return_status VARCHAR2(30);
836 l_object_version_number NUMBER;
837 --added for bug: 4537865
838 l_new_object_version_number NUMBER;
839 --added for bug: 4537865
840 l_competence_element_id NUMBER;
841 l_mode VARCHAR2(30);
842 BEGIN
843
844 x_success_assignment_id_tbl := p_assignment_tbl;
845 x_return_status := FND_API.G_RET_STS_SUCCESS;
846
847 IF p_assignment_tbl.count > 0 THEN
848 FOR I in 1..p_assignment_tbl.count LOOP
849
850 IF p_assignment_tbl(I) <> l_assignment_id THEN
851 -- Assignment Id has changed.
852 -- Create a new savepoint. Save the new assigment_id
853
854 IF l_assignment_return_status <> FND_API.G_RET_STS_SUCCESS
855 THEN
856 ROLLBACK TO PROCESS_COMPETENCE_ELEMENTS;
857 PA_MESSAGE_UTILS.save_messages
858 (p_user_id => PA_MASS_ASGMT_TRX.G_SUBMITTER_USER_ID,
859 p_source_type1 => PA_MASS_ASGMT_TRX.G_SOURCE_TYPE1,
860 p_source_type2 => PA_MASS_ASGMT_TRX.G_MASS_UPDATE_COMPETENCIES,
861 p_source_identifier1 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_TYPE,
862 p_source_identifier2 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_KEY,
863 p_context1 => p_project_id,
864 p_context2 => p_assignment_tbl(I-1),
865 p_context3 => NULL,
866 p_commit => FND_API.G_TRUE,
867 x_return_status => l_return_status);
868 l_assignment_return_status := FND_API.G_RET_STS_SUCCESS;
869 x_success_assignment_id_tbl(I-1) := null;
870
871 ELSE
872 -- Assignment Status is success. Commit the assignment
876 COMMIT;
873 -- changes.
874
875 IF p_commit = FND_API.G_TRUE THEN
877 END IF;
878 END IF;
879
880 l_assignment_id := p_assignment_tbl(I);
881
882 SAVEPOINT PROCESS_COMPETENCE_ELEMENTS;
883
884 END IF;
885 IF p_competence_id_tbl.count > 0 THEN
886 FOR J in 1..p_competence_id_tbl.count LOOP
887 BEGIN
888 SELECT object_version_number,
889 competence_element_id
890 INTO l_object_version_number,
891 l_competence_element_id
892 FROM per_competence_elements
893 WHERE object_id = p_assignment_tbl(I)
894 AND object_name = 'OPEN_ASSIGNMENT'
895 AND competence_id = p_competence_id_tbl(J);
896 l_mode := 'UPDATE';
897 EXCEPTION
898 WHEN NO_DATA_FOUND THEN
899 l_mode := 'INSERT';
900 END;
901 IF (l_mode = 'UPDATE')
902 THEN
903
904 Update_competence_element
905 (p_element_id => l_competence_element_id,
906 p_object_name => 'OPEN_ASSIGNMENT',
907 p_object_id => p_assignment_tbl(I),
908 p_competence_id => p_competence_id_tbl(J),
909 p_competence_alias => p_competence_alias_tbl(J),
910 p_rating_level_id => p_rating_level_id_tbl(J),
911 p_mandatory_flag => p_mandatory_flag_tbl(J),
912 p_object_version_number => l_object_version_number,
913 -- x_object_version_number => l_object_version_number, * commented for bug: 4537865
914 x_object_version_number => l_new_object_version_number, -- added for bug: 4537865
915 x_return_status => l_return_status,
916 x_msg_count => l_msg_count,
917 x_msg_data => l_msg_data);
918
919 --added for bug: 4537865
920 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
921 l_object_version_number := l_new_object_version_number;
922 END IF;
923 --added for bug: 4537865
924
925 ELSE
926 Add_competence_element
927 (p_object_name => 'OPEN_ASSIGNMENT',
928 p_object_id => p_assignment_tbl(I),
929 p_competence_id => p_competence_id_tbl(J),
930 p_competence_alias => p_competence_alias_tbl(J),
931 p_rating_level_id => p_rating_level_id_tbl(J),
932 p_mandatory_flag => p_mandatory_flag_tbl(J),
933 p_init_msg_list => FND_API.G_FALSE,
934 p_commit => FND_API.G_FALSE,
935 p_validate_only => 'N',
936 x_element_rowid => l_element_rowid,
937 x_element_id => l_element_id,
938 x_return_status => l_return_status,
939 x_msg_count => l_msg_count,
940 x_msg_data => l_msg_data);
941 END IF;
942
943 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
944 l_assignment_return_status := l_return_status;
945 END IF;
946
947 END LOOP;
948 END IF;
949
950 END LOOP;
951
952 -- Check if the last assignment got updated
953 IF l_assignment_return_status <> FND_API.G_RET_STS_SUCCESS THEN
954 ROLLBACK TO PROCESS_COMPETENCE_ELEMENTS;
955 PA_MESSAGE_UTILS.save_messages
956 (p_user_id => PA_MASS_ASGMT_TRX.G_SUBMITTER_USER_ID,
957 p_source_type1 => PA_MASS_ASGMT_TRX.G_SOURCE_TYPE1,
958 p_source_type2 => PA_MASS_ASGMT_TRX.G_MASS_UPDATE_COMPETENCIES,
959 p_source_identifier1 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_TYPE,
960 p_source_identifier2 => PA_MASS_ASGMT_TRX.G_WORKFLOW_ITEM_KEY,
961 p_context1 => p_project_id,
962 p_context2 => p_assignment_tbl(p_assignment_tbl.count),
963 p_context3 => NULL,
964 p_commit => FND_API.G_TRUE,
965 x_return_status => l_return_status);
966 x_success_assignment_id_tbl(p_assignment_tbl.count) := null;
967
968 ELSE
969 -- Assignment Status is success. Commit the assignment
970 -- changes.
971 IF p_commit = FND_API.G_TRUE THEN
972 COMMIT;
973 END IF;
974 END IF;
975
976 END IF;
977 EXCEPTION
978 WHEN OTHERS THEN
979 -- 4537865 : RESET OUT PARAMS
980 x_success_assignment_id_tbl := NULL ;
981 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
982 x_msg_data := SUBSTRB(SQLERRM ,1,240);
983 x_msg_count := 1;
984
985 Fnd_Msg_Pub.add_exc_msg(p_pkg_name => 'PA_COMPETENCE_PUB',
986 p_procedure_name => 'Mass_Process_Competences',
987 p_error_text => x_msg_data);
988 -- ENd : 4537865
989 RAISE;
990 END;
991 end pa_competence_pub ;