[Home] [Help]
PACKAGE BODY: APPS.PA_COMPETENCE_PVT
Source
1 PACKAGE BODY pa_competence_pvt AS
2 -- $Header: PACOMPVB.pls 120.1 2005/08/19 16:20:26 mwasowic 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 -- 24-JUL-2000 R. Krishnamurthy Created
12 --
13 PROCEDURE Add_competence_element
14 ( p_object_name IN per_competence_elements.object_name%TYPE := FND_API.G_MISS_CHAR,
15 p_object_id IN PA_ASSIGNMENTS_PUB.assignment_id_tbl_type,
16 p_project_id IN pa_project_assignments.project_id%TYPE := FND_API.G_MISS_NUM,
17 p_competence_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
18 p_rating_level_id IN per_competence_elements.rating_level_id%TYPE := FND_API.G_MISS_NUM,
19 p_mandatory_flag IN per_competence_elements.mandatory%TYPE := FND_API.G_MISS_CHAR,
20 p_commit IN VARCHAR2 := FND_API.G_MISS_CHAR,
21 p_validate_only IN VARCHAR2 := FND_API.G_MISS_CHAR,
22 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
23 x_element_rowid OUT NOCOPY ROWID, --File.Sql.39 bug 4440895
24 x_element_id OUT NOCOPY per_competence_elements.competence_element_id%TYPE, --File.Sql.39 bug 4440895
25 x_return_status OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
26
27 l_data VARCHAR2(500);
28 l_msg_data VARCHAR2(500);
29 l_msg_index_out NUMBER := 0;
30 l_project_id NUMBER := 0;
31 l_return_status VARCHAR2(30);
32 l_error_message_code VARCHAR2(30);
33 l_mandatory_flag VARCHAR2(1);
34 l_bg_id NUMBER := 0;
35 l_element_id NUMBER := 0;
36 l_role_id NUMBER := 0;
37 l_object_version_number NUMBER := 0;
38 l_object_id_index binary_integer;
39 l_first_assignment_id NUMBER;
40
41 CURSOR l_bg_csr IS
42 SELECT business_group_id
43 FROM per_competences
44 WHERE competence_id = p_competence_id;
45
46 CURSOR l_rowid_csr IS
47 SELECT rowid
48 FROM per_competence_elements
49 WHERE competence_element_id = l_element_id;
50
51 BEGIN
52 pa_debug.set_err_stack ('pa_competence_pvt.add_competence_element');
53 SAVEPOINT COMPETENCE_PVT_ADD_COMP_ELE;
54 x_return_status := FND_API.G_RET_STS_SUCCESS;
55
56 -- Initialize the message stack if necessary
57 IF p_init_msg_list = FND_API.G_TRUE THEN
58 fnd_msg_pub.initialize;
59 END IF;
60
61 -- Angie modified this to fix bug 1676891
62 -- get first index of p_object_id
63 l_object_id_index := p_object_id.first;
64
65 IF p_object_name = 'PROJECT_ROLE' THEN
66 -- Check whether the competency can be assigned to the role
67 pa_role_utils.validate_role_competency
68 (p_competence_id => p_competence_id
69 ,x_return_status => l_return_status
70 ,x_error_message_code => l_error_message_code );
71
72 Check_Error (p_return_status => l_return_status,
73 p_error_message_code => l_error_message_code );
74 END IF;
75
76 -- If object = ASGMT call asgmt validations
77 IF p_object_name = 'OPEN_ASSIGNMENT' THEN
78
79 -- if p_project_id has not been passed, get project_id value
80 IF p_project_id = NULL OR p_project_id = FND_API.G_MISS_NUM THEN
81 -- Get the assignment details
82 --dbms_output.put_line('p_project_id = NULL in PUB, first_index: '|| l_object_id_index);
83
84 l_first_assignment_id := p_object_id(l_object_id_index).assignment_id;
85 --dbms_output.put_line('p_project_id = NULL in PUB, l_first_assignment_id: '|| l_first_assignment_id);
86 BEGIN
87 SELECT project_id
88 INTO l_project_id
89 FROM pa_project_assignments
90 WHERE assignment_id = l_first_assignment_id;
91 EXCEPTION
92 WHEN NO_DATA_FOUND THEN
93 Check_Error (p_return_status => FND_API.G_RET_STS_ERROR
94 ,p_error_message_code => 'PA_INVALID_ASGMT_ID');
95 END;
96
97 -- if p_project_id has been passed
98 ELSE
99 l_project_id := p_project_id;
100 END IF;
101
102 -- dbms_output.put_line('before calling pa_assignment_utils.validate_asgmt_competency');
103 /* The IF condition is added for bug 2361959 */
104
105 IF l_project_id IS NOT NULL THEN
106
107 pa_assignment_utils.validate_asgmt_competency
108 (p_project_id => l_project_id
109 ,p_assignment_id => l_first_assignment_id
110 ,p_competence_id => p_competence_id
111 ,x_return_status => l_return_status
112 ,x_error_message_code => l_error_message_code );
113 -- dbms_output.put_line('after calling pa_assignment_utils.validate_asgmt_competency');
114
115 Check_Error (p_return_status => l_return_status,
116 p_error_message_code => l_error_message_code );
117
118 END IF; /*Project ID NULL */
119 END IF;
120
121 IF pa_hr_competence_utils.check_competence_exists
122 (p_object_name, l_first_assignment_id, p_competence_id) = 'Y' THEN
123 Check_Error ( p_return_status => FND_API.G_RET_STS_ERROR
124 ,p_error_message_code => 'PA_COMPETENCE_ELEMENT_EXISTS');
125 END IF;
126 --dbms_output.put_line('after calling pa_hr_competence_utils.check_competence_exists');
127
128 -- If there are errors, do not proceed to insert the data
129 IF g_noof_errors > 0 THEN
130 x_return_status := FND_API.G_RET_STS_ERROR;
131 pa_debug.reset_err_stack;
132 RETURN;
133 END IF;
134
135 -- If validate only = 'Y' then return at this point.
136 IF p_validate_only = 'Y' THEN
137 x_return_status := FND_API.G_RET_STS_SUCCESS;
138 pa_debug.reset_err_stack;
139 RETURN;
140 END IF;
141
142 -- Decode default values to null
143 l_mandatory_flag := p_mandatory_flag;
144 IF (p_mandatory_flag IS NULL or
145 p_mandatory_flag = FND_API.G_MISS_CHAR) THEN
146 l_mandatory_flag := 'N' ;
147 END IF;
148
149 -- If no errors and validate only = 'N' then
150 -- call the table handler to create the competency
151 -- and pass only relevant values
152 -- We need the business group id of the competency
153 -- Hence fetch the same from the competences table
154 OPEN l_bg_csr;
155 FETCH l_bg_csr INTO l_bg_id;
156 CLOSE l_bg_csr;
157
158 -- loop for all the object_id to create competence_element
159 FOR I in 1..p_object_id.count LOOP
160 -- Now create the competency
161 l_element_id := NULL;
162
163 hr_competence_element_api.create_competence_element
164 (
165 p_competence_element_id => l_element_id,
166 p_object_version_number => l_object_version_number,
167 p_type => p_object_name,
168 p_business_group_id => l_bg_id,
169 p_enterprise_id => Null,
170 p_competence_id => p_competence_id,
171 p_proficiency_level_id => p_rating_level_id,
172 p_high_proficiency_level_id => null,
173 p_weighting_level_id => null,
174 p_rating_level_id => null,
175 p_person_id => null,
176 p_job_id => null,
177 p_valid_grade_id => null,
178 p_position_id => null,
179 p_organization_id => null,
180 p_parent_competence_element_id => null,
181 p_activity_version_id => null,
182 p_assessment_id => null,
183 p_assessment_type_id => null,
184 p_mandatory => l_mandatory_flag,
185 p_effective_date_from => trunc(sysdate),
186 p_effective_date_to => null,
187 p_group_competence_type => null,
188 p_competence_type => null,
189 p_normal_elapse_duration => null,
190 p_normal_elapse_duration_unit => null,
191 p_sequence_number => null,
192 p_source_of_proficiency_level => null,
193 p_line_score => null,
194 p_certification_date => null,
195 p_certification_method => null,
196 p_next_certification_date => null,
197 p_comments => null,
198 p_attribute_category => null,
199 p_attribute1 => null,
200 p_attribute2 => null,
201 p_attribute3 => null,
202 p_attribute4 => null,
203 p_attribute5 => null,
204 p_attribute6 => null,
205 p_attribute7 => null,
206 p_attribute8 => null,
207 p_attribute9 => null,
208 p_attribute10 => null,
209 p_attribute11 => null,
210 p_attribute12 => null,
211 p_attribute13 => null,
212 p_attribute14 => null,
213 p_attribute15 => null,
214 p_attribute16 => null,
215 p_attribute17 => null,
216 p_attribute18 => null,
217 p_attribute19 => null,
218 p_attribute20 => null,
219 p_effective_date => trunc(sysdate),
220 p_object_id => p_object_id(l_object_id_index).assignment_id,
221 p_object_name => p_object_name
222 );
223
224 -- get next object_id_index
225 l_object_id_index := p_object_id.next(l_object_id_index);
226
227 END LOOP;
228
229 IF l_element_id IS NOT NULL THEN
230 OPEN l_rowid_csr;
231 FETCH l_rowid_csr INTO x_element_rowid;
232 CLOSE l_rowid_csr;
233 END IF;
234 x_element_id := l_element_id; /* added for bug 2084645 */
235 x_return_status := FND_API.G_RET_STS_SUCCESS;
236 pa_debug.reset_err_stack;
237
238 EXCEPTION
239 WHEN OTHERS THEN
240 IF p_commit = FND_API.G_TRUE THEN
241 ROLLBACK TO COMPETENCE_PVT_ADD_COMP_ELE;
242 END IF;
243 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
244 RAISE;
245 END Add_Competence_Element ;
246
247
248 PROCEDURE Update_competence_element
249 (p_object_name IN per_competence_elements.object_name%TYPE := FND_API.G_MISS_CHAR,
250 p_object_id IN per_competence_elements.object_id%TYPE := FND_API.G_MISS_NUM,
251 p_competence_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
252 p_element_rowid IN ROWID := FND_API.G_MISS_CHAR,
253 p_element_id IN per_competence_elements.competence_element_id%TYPE := FND_API.G_MISS_NUM,
254 p_rating_level_id IN per_competence_elements.rating_level_id%TYPE := FND_API.G_MISS_NUM,
255 p_mandatory_flag IN per_competence_elements.mandatory%TYPE := FND_API.G_MISS_CHAR,
256 p_commit IN VARCHAR2 := FND_API.G_MISS_CHAR,
257 p_validate_only IN VARCHAR2 := FND_API.G_MISS_CHAR,
258 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
259 p_object_version_number IN NUMBER,
260 x_object_version_number OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
261 x_return_status OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
262
263 l_effective_date_from DATE;
264 l_return_status VARCHAR2(30);
265 l_object_version_number NUMBER ;
266 l_error_message_code VARCHAR2(30);
267 l_mandatory_flag VARCHAR2(1);
268
269 BEGIN
270 pa_debug.set_err_stack ('pa_competence_pvt.update_competence_element');
271 SAVEPOINT COMPETENCE_PVT_UPD_COMP_ELE;
272 -- Initialize the message stack if necessary
273 IF p_init_msg_list = FND_API.G_TRUE THEN
274 fnd_msg_pub.initialize;
275 END IF;
276 x_return_status := FND_API.G_RET_STS_SUCCESS;
277 -- Check if the combination exists. If it does not, then
278 -- should not proceed with the update
279 IF pa_hr_competence_utils.check_competence_exists
280 (p_object_name,p_object_id,p_competence_id) = 'N' THEN
281 Check_Error (p_return_status => FND_API.G_RET_STS_ERROR
282 ,p_error_message_code =>'PA_COMPETENCE_ELE_NOT_EXISTS');
283 END IF;
284 -- Validate that the competence element id pertains to the
285 -- given object name , id and competence id combination
286 -- This is to ensure that we are updating the right record
287 -- Call the check element id api
288 Check_Element_id
289 (p_object_name => p_object_name,
290 p_object_id => p_object_id,
291 p_competence_id => p_competence_id,
292 p_element_id => p_element_id,
293 x_effective_date_from => l_effective_date_from,
294 x_return_status => l_return_status ,
295 x_error_message_code => l_error_message_code );
296 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
297 Check_Error (p_return_status => l_return_status
298 ,p_error_message_code => l_error_message_code );
299 END IF;
300 -- If there are errors, do not proceed to update the data
301 -- Check whether the object version number is correct
302 Check_Object_version_number
303 (p_element_id => p_element_id,
304 p_object_version_number => p_object_version_number,
305 x_return_status => l_return_status,
306 x_error_message_code => l_error_message_code );
307
308 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
309 Check_Error (p_return_status => l_return_status
310 ,p_error_message_code => l_error_message_code );
311 END IF;
312
313 IF g_noof_errors > 0 THEN
314 x_return_status := FND_API.G_RET_STS_ERROR;
315 pa_debug.reset_err_stack;
316 RETURN;
317 END IF;
318 -- If validate only = 'Y' then return at this point.
319 IF p_validate_only = 'Y' THEN
320 x_return_status := FND_API.G_RET_STS_SUCCESS;
321 pa_debug.reset_err_stack;
322 RETURN;
323 END IF;
324 -- Decode default values to null
325 l_mandatory_flag := p_mandatory_flag;
326 IF (p_mandatory_flag IS NULL or
327 p_mandatory_flag = FND_API.G_MISS_CHAR) THEN
328 l_mandatory_flag := 'N' ;
329 END IF;
330
331 -- If no errors and validate only = 'N' then
332 -- call the table handler to update the competency
333 -- and pass only relevant values
334 l_object_version_number := p_object_version_number ;
335 -- The HR api expects the effective_date as an input
336 -- Since we do not have a need to update the effective date
337 -- we will use the effective date fetched earlier for this purpose
338
339 hr_competence_element_api.update_competence_element
340 (
341 p_competence_element_id => p_element_id,
342 p_object_version_number => l_object_version_number,
343 p_proficiency_level_id => p_rating_level_id,
344 p_mandatory => l_mandatory_flag,
345 p_effective_date => l_effective_date_from );
346
347 x_object_version_number := l_object_version_number;
348 x_return_status := FND_API.G_RET_STS_SUCCESS;
349 pa_debug.reset_err_stack;
350 EXCEPTION
351 WHEN OTHERS THEN
352 IF p_commit = FND_API.G_TRUE THEN
353 ROLLBACK TO COMPETENCE_PVT_UPD_COMP_ELE;
354 END IF;
355 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
356 RAISE;
357 END Update_Competence_element;
358
359 PROCEDURE delete_competence_element
360 (p_object_name IN per_competence_elements.object_name%TYPE := FND_API.G_MISS_CHAR,
361 p_object_id IN per_competence_elements.object_id%TYPE := FND_API.G_MISS_NUM,
362 p_competence_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
363 p_element_rowid IN ROWID := FND_API.G_MISS_CHAR,
364 p_element_id IN per_competence_elements.competence_element_id%TYPE := FND_API.G_MISS_NUM,
365 p_commit IN VARCHAR2 := FND_API.G_MISS_CHAR,
366 p_validate_only IN VARCHAR2 := FND_API.G_MISS_CHAR,
367 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
368 p_object_version_number IN NUMBER,
369 x_return_status OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
370
371 l_return_status VARCHAR2(30);
372 l_effective_date_from DATE;
373 l_object_version_number NUMBER ;
374 l_error_message_code VARCHAR2(30);
375
376 BEGIN
377 pa_debug.set_err_stack ('pa_competence_pvt.delete_competence_element');
378 SAVEPOINT COMPETENCE_PVT_DEL_COMP_ELE;
379 -- Initialize the message stack if necessary
380 IF p_init_msg_list = FND_API.G_TRUE THEN
381 fnd_msg_pub.initialize;
382 END IF;
383 x_return_status := FND_API.G_RET_STS_SUCCESS;
384 -- Check if the combination exists. If it does not, then
385 -- should not proceed with the delete
386 IF pa_hr_competence_utils.check_competence_exists
387 (p_object_name,p_object_id,p_competence_id) = 'N' THEN
388 Check_Error (p_return_status => FND_API.G_RET_STS_ERROR
389 ,p_error_message_code =>'PA_COMPETENCE_ELE_NOT_EXISTS');
390 END IF;
391 -- Validate that the competence element id pertains to the
392 -- given object name , id and competence id combination
393 -- This is to ensure that we are deleting the right record
394 Check_Element_id
395 (p_object_name => p_object_name,
396 p_object_id => p_object_id,
397 p_competence_id => p_competence_id,
398 p_element_id => p_element_id,
399 x_effective_date_from => l_effective_date_from,
400 x_return_status => l_return_status ,
401 x_error_message_code => l_error_message_code);
402 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
403 Check_Error (p_return_status => l_return_status
404 ,p_error_message_code => l_error_message_code );
405 END IF;
406
407 -- Check whether the object version number is correct
408 Check_Object_version_number
409 (p_element_id => p_element_id,
410 p_object_version_number => p_object_version_number,
411 x_return_status => l_return_status,
412 x_error_message_code => l_error_message_code );
413
414 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
415 Check_Error (p_return_status => l_return_status
416 ,p_error_message_code => l_error_message_code );
417 END IF;
418
419 -- If there are errors, do not proceed to delete the data
420 IF g_noof_errors > 0 THEN
421 x_return_status := FND_API.G_RET_STS_ERROR;
422 pa_debug.reset_err_stack;
423 RETURN;
424 END IF;
425 -- If validate only = 'Y' then return at this point.
426 IF p_validate_only = 'Y' THEN
427 x_return_status := FND_API.G_RET_STS_SUCCESS;
428 pa_debug.reset_err_stack;
429 RETURN;
430 END IF;
431 hr_competence_element_api.delete_competence_element
432 (p_competence_element_id => p_element_id,
433 p_object_version_number => p_object_version_number );
434
435 x_return_status := FND_API.G_RET_STS_SUCCESS;
436 pa_debug.reset_err_stack;
437
438 EXCEPTION
439 WHEN OTHERS THEN
440 IF p_commit = FND_API.G_TRUE THEN
441 ROLLBACK TO COMPETENCE_PVT_DEL_COMP_ELE;
442 END IF;
443 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
444 RAISE;
445 END Delete_Competence_element;
446
447 PROCEDURE Check_Element_id
448 (p_object_name IN per_competence_elements.object_name%TYPE := FND_API.G_MISS_CHAR,
449 p_object_id IN per_competence_elements.object_id%TYPE := FND_API.G_MISS_NUM,
450 p_competence_id IN per_competences.competence_id%TYPE := FND_API.G_MISS_NUM,
451 p_element_id IN per_competence_elements.competence_element_id%TYPE := FND_API.G_MISS_NUM,
452 x_effective_date_from OUT NOCOPY per_competence_elements.effective_date_from%TYPE , --File.Sql.39 bug 4440895
453 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
454 x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
455
456 CURSOR l_check_element_csr IS
457 SELECT object_name,
458 object_id,
459 competence_id,
460 effective_date_from
461 FROM per_competence_elements
462 WHERE competence_element_id = p_element_id;
463 l_check_element_rec l_check_element_csr%ROWTYPE;
464 BEGIN
465 pa_debug.set_err_stack ('pa_competence_pvt.check_element_id');
466 x_return_status := FND_API.G_RET_STS_SUCCESS;
467 OPEN l_check_element_csr;
468 FETCH l_check_element_csr INTO l_check_element_rec;
469 IF l_check_element_csr%NOTFOUND
470 OR (l_check_element_rec.competence_id <> p_competence_id
471 OR
472 l_check_element_rec.object_id <> p_object_id
473 OR
474 l_check_element_rec.object_name <> p_object_name) THEN
475 x_return_status := FND_API.G_RET_STS_ERROR;
476 x_error_message_code := 'PA_INVALID_ELEMENT_ID';
477 x_effective_date_from := NULL;
478 END IF;
479 x_effective_date_from := l_check_element_rec.effective_date_from;
480 CLOSE l_check_element_csr;
481 pa_debug.reset_err_stack;
482
483 EXCEPTION
484 WHEN OTHERS THEN
485 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
486 RAISE;
487 END Check_Element_id ;
488
489 PROCEDURE Check_Error (p_return_status IN VARCHAR2,
490 p_error_message_code IN VARCHAR2) IS
491 BEGIN
492 pa_debug.set_err_stack ('pa_competence_pvt.check_error');
493 IF p_return_status = FND_API.G_RET_STS_ERROR THEN
494 g_noof_errors := g_noof_errors + 1;
495 pa_utils.add_message (p_app_short_name => 'PA',
496 p_msg_name => p_error_message_code);
497 END IF;
498 pa_debug.reset_err_stack;
499 EXCEPTION
500 WHEN OTHERS THEN
501 RAISE;
502 END check_error;
503
504 PROCEDURE Check_Object_version_number
505 (p_element_id IN per_competence_elements.competence_element_id%TYPE,
506 p_object_version_number IN NUMBER,
507 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
508 x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
509
510 CURSOR l_get_obj_vers_csr IS
511 SELECT object_version_number
512 FROM per_competence_elements
513 WHERE competence_element_id = p_element_id;
514 l_obj_version_number NUMBER := 0;
515
516 BEGIN
517 pa_debug.set_err_stack ('pa_competence_pvt.check_object_version_number');
518 OPEN l_get_obj_vers_csr;
519 FETCH l_get_obj_vers_csr INTO l_obj_version_number;
520 IF l_obj_version_number <> p_object_version_number THEN
521 x_return_status := FND_API.G_RET_STS_ERROR;
522 x_error_message_code := 'PA_COMP_OBJ_VERSION_INVALID';
523 END IF;
524 CLOSE l_get_obj_vers_csr;
525 pa_debug.reset_err_stack;
526
527 EXCEPTION
528 WHEN OTHERS THEN
529 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
530 RAISE;
531 END Check_Object_Version_Number ;
532
533 end pa_competence_pvt ;