[Home] [Help]
PACKAGE BODY: APPS.PER_CEL_UPD
Source
1 Package Body per_cel_upd as
2 /* $Header: pecelrhi.pkb 120.3 2006/03/28 05:27:21 arumukhe noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_cel_upd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< update_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml update logic. The processing of
17 -- this procedure is:
18 -- 1) Increment the object_version_number by 1 if the object_version_number
19 -- is defined as an attribute for this entity.
20 -- 2) To set and unset the g_api_dml status as required (as we are about to
21 -- perform dml).
22 -- 3) To update the specified row in the schema using the primary key in
23 -- the predicates.
24 -- 4) To trap any constraint violations that may have occurred.
25 -- 5) To raise any other errors.
26 --
27 -- Pre Conditions:
28 -- This is an internal private procedure which must be called from the upd
29 -- procedure.
30 --
31 -- In Parameters:
32 -- A Pl/Sql record structre.
33 --
34 -- Post Success:
35 -- The specified row will be updated in the schema.
36 --
37 -- Post Failure:
38 -- On the update dml failure it is important to note that we always reset the
39 -- g_api_dml status to false.
40 -- If a check, unique or parent integrity constraint violation is raised the
41 -- constraint_error procedure will be called.
42 -- If any other error is reported, the error will be raised after the
43 -- g_api_dml status is reset.
44 --
45 -- Developer Implementation Notes:
46 -- The update 'set' attribute list should be modified if any of your
47 -- attributes are not updateable.
48 --
49 -- Access Status:
50 -- Internal Table Handler Use Only.
51 --
52 -- {End Of Comments}
53 -- ----------------------------------------------------------------------------
54 Procedure update_dml(p_rec in out nocopy per_cel_shd.g_rec_type) is
55 --
56 l_proc varchar2(72) := g_package||'update_dml';
57 --
58 Begin
59 hr_utility.set_location('Entering:'||l_proc, 5);
60 --
61 -- Increment the object version
62 --
63 p_rec.object_version_number := p_rec.object_version_number + 1;
64 --
65 per_cel_shd.g_api_dml := true; -- Set the api dml status
66 --
67 -- Update the per_competence_elements Row
68 --
69 -- bug fix 3991608
70 -- Update statement modified to update the columns
71 -- information_category,information1..information20 with
72 -- values in p_rec.information_category and p_rec.information1
73 -- .. p_rec.information20.
74
75 update per_competence_elements
76 set
77 competence_element_id = p_rec.competence_element_id,
78 object_version_number = p_rec.object_version_number,
79 proficiency_level_id = p_rec.proficiency_level_id,
80 high_proficiency_level_id = p_rec.high_proficiency_level_id,
81 weighting_level_id = p_rec.weighting_level_id,
82 rating_level_id = p_rec.rating_level_id,
83 mandatory = p_rec.mandatory,
84 effective_date_from = p_rec.effective_date_from,
85 effective_date_to = p_rec.effective_date_to,
86 group_competence_type = p_rec.group_competence_type,
87 competence_type = p_rec.competence_type,
88 normal_elapse_duration = p_rec.normal_elapse_duration,
89 normal_elapse_duration_unit = p_rec.normal_elapse_duration_unit,
90 sequence_number = p_rec.sequence_number,
91 source_of_proficiency_level = p_rec.source_of_proficiency_level,
92 line_score = p_rec.line_score,
93 certification_date = p_rec.certification_date,
94 certification_method = p_rec.certification_method,
95 next_certification_date = p_rec.next_certification_date,
96 comments = p_rec.comments,
97 attribute_category = p_rec.attribute_category,
98 attribute1 = p_rec.attribute1,
99 attribute2 = p_rec.attribute2,
100 attribute3 = p_rec.attribute3,
101 attribute4 = p_rec.attribute4,
102 attribute5 = p_rec.attribute5,
103 attribute6 = p_rec.attribute6,
104 attribute7 = p_rec.attribute7,
105 attribute8 = p_rec.attribute8,
106 attribute9 = p_rec.attribute9,
107 attribute10 = p_rec.attribute10,
108 attribute11 = p_rec.attribute11,
109 attribute12 = p_rec.attribute12,
110 attribute13 = p_rec.attribute13,
111 attribute14 = p_rec.attribute14,
112 attribute15 = p_rec.attribute15,
113 attribute16 = p_rec.attribute16,
114 attribute17 = p_rec.attribute17,
115 attribute18 = p_rec.attribute18,
116 attribute19 = p_rec.attribute19,
117 attribute20 = p_rec.attribute20,
118 party_id = p_rec.party_id ,
119 qualification_type_id = p_rec.qualification_type_id ,
120 unit_standard_type = p_rec.unit_standard_type ,
121 status = p_rec.status ,
122 information_category = p_rec.information_category,
123 information1 = p_rec.information1,
124 information2 = p_rec.information2,
125 information3 = p_rec.information3,
126 information4 = p_rec.information4,
127 information5 = p_rec.information5,
128 information6 = p_rec.information6,
129 information7 = p_rec.information7,
130 information8 = p_rec.information8,
131 information9 = p_rec.information9,
132 information10 = p_rec.information10,
133 information11 = p_rec.information11,
134 information12 = p_rec.information12,
135 information13 = p_rec.information13,
136 information14 = p_rec.information14,
137 information15 = p_rec.information15,
138 information16 = p_rec.information16,
139 information17 = p_rec.information17,
140 information18 = p_rec.information18,
141 information19 = p_rec.information19,
142 information20 = p_rec.information20,
143 achieved_date = p_rec.achieved_date,
144 appr_line_score = p_rec.appr_line_score
145 where competence_element_id = p_rec.competence_element_id;
146 --
147 per_cel_shd.g_api_dml := false; -- Unset the api dml status
148 --
149 hr_utility.set_location(' Leaving:'||l_proc, 10);
150 --
151 Exception
152 When hr_api.check_integrity_violated Then
153 -- A check constraint has been violated
154 per_cel_shd.g_api_dml := false; -- Unset the api dml status
155 per_cel_shd.constraint_error
156 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
157 When hr_api.parent_integrity_violated Then
158 -- Parent integrity has been violated
159 per_cel_shd.g_api_dml := false; -- Unset the api dml status
160 per_cel_shd.constraint_error
161 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
162 When hr_api.unique_integrity_violated Then
163 -- Unique integrity has been violated
164 per_cel_shd.g_api_dml := false; -- Unset the api dml status
165 per_cel_shd.constraint_error
166 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
167 When Others Then
168 per_cel_shd.g_api_dml := false; -- Unset the api dml status
169 Raise;
170 End update_dml;
171 --
172 -- ----------------------------------------------------------------------------
173 -- |------------------------------< pre_update >------------------------------|
174 -- ----------------------------------------------------------------------------
175 -- {Start Of Comments}
176 --
177 -- Description:
178 -- This private procedure contains any processing which is required before
179 -- the update dml.
180 --
181 -- Pre Conditions:
182 -- This is an internal procedure which is called from the upd procedure.
183 --
184 -- In Parameters:
185 -- A Pl/Sql record structre.
186 --
187 -- Post Success:
188 -- Processing continues.
189 --
190 -- Post Failure:
191 -- If an error has occurred, an error message and exception will be raised
192 -- but not handled.
193 --
194 -- Developer Implementation Notes:
195 -- Any pre-processing required before the update dml is issued should be
196 -- coded within this procedure. It is important to note that any 3rd party
197 -- maintenance should be reviewed before placing in this procedure.
198 --
199 -- Access Status:
200 -- Internal Table Handler Use Only.
201 --
202 -- {End Of Comments}
203 -- ----------------------------------------------------------------------------
204 Procedure pre_update(p_rec in per_cel_shd.g_rec_type) is
205 --
206 l_proc varchar2(72) := g_package||'pre_update';
207 --
208 Begin
209 hr_utility.set_location('Entering:'||l_proc, 5);
210 --
211 hr_utility.set_location(' Leaving:'||l_proc, 10);
212 End pre_update;
213 --
214 -- ----------------------------------------------------------------------------
215 -- |-----------------------------< post_update >------------------------------|
216 -- ----------------------------------------------------------------------------
217 -- {Start Of Comments}
218 --
219 -- Description:
220 -- This private procedure contains any processing which is required after the
221 -- update dml.
222 --
223 -- Pre Conditions:
224 -- This is an internal procedure which is called from the upd procedure.
225 --
226 -- In Parameters:
227 -- A Pl/Sql record structre.
228 --
229 -- Post Success:
230 -- Processing continues.
231 --
232 -- Post Failure:
233 -- If an error has occurred, an error message and exception will be raised
234 -- but not handled.
235 --
236 -- Developer Implementation Notes:
237 -- Any post-processing required after the update dml is issued should be
238 -- coded within this procedure. It is important to note that any 3rd party
239 -- maintenance should be reviewed before placing in this procedure.
240 --
241 -- Access Status:
242 -- Internal Table Handler Use Only.
243 --
244 -- {End Of Comments}
245 -- ----------------------------------------------------------------------------
246 Procedure post_update(p_rec in per_cel_shd.g_rec_type) is
247 --
248 l_proc varchar2(72) := g_package||'post_update';
249 --
250 Begin
251 hr_utility.set_location('Entering:'||l_proc, 5);
252 --
253 -- This is a hook point and the user hook for post_update is called here.
254 --
255 begin
256 per_cel_rku.after_update (
257 p_competence_element_id => p_rec.competence_element_id ,
258 p_business_group_id => p_rec.business_group_id ,
259 p_object_version_number => p_rec.object_version_number ,
260 p_type => p_rec.type ,
261 p_competence_id => p_rec.competence_id ,
262 -- p_member_competence_set_id => p_rec.member_competence_set_id ,
263 p_proficiency_level_id => p_rec.proficiency_level_id ,
264 p_high_proficiency_level_id => p_rec.high_proficiency_level_id ,
265 p_weighting_level_id => p_rec.weighting_level_id ,
266 p_rating_level_id => p_rec.rating_level_id ,
267 p_person_id => p_rec.person_id ,
268 p_enterprise_id => p_rec.enterprise_id ,
269 p_job_id => p_rec.job_id ,
270 p_valid_grade_id => p_rec.valid_grade_id ,
271 p_position_id => p_rec.position_id ,
272 p_organization_id => p_rec.organization_id ,
273 -- p_work_item_id => p_rec.work_item_id ,
274 -- p_competence_set_id => p_rec.competence_set_id ,
275 p_parent_competence_element_id => p_rec.parent_competence_element_id,
276 p_activity_version_id => p_rec.activity_version_id ,
277 p_assessment_id => p_rec.assessment_id ,
278 p_assessment_type_id => p_rec.assessment_type_id ,
279 p_mandatory => p_rec.mandatory ,
280 p_effective_date_from => p_rec.effective_date_from ,
281 p_effective_date_to => p_rec.effective_date_to ,
282 p_group_competence_type => p_rec.group_competence_type ,
283 p_competence_type => p_rec.competence_type ,
284 p_sequence_number => p_rec.sequence_number ,
285 p_normal_elapse_duration => p_rec.normal_elapse_duration ,
286 p_normal_elapse_duration_unit => p_rec.normal_elapse_duration_unit ,
287 p_source_of_proficiency_level => p_rec.source_of_proficiency_level ,
288 p_line_score => p_rec.line_score ,
289 p_certification_date => p_rec.certification_date ,
290 p_certification_method => p_rec.certification_method ,
291 p_next_certification_date => p_rec.next_certification_date ,
292 p_comments => p_rec.comments ,
293 p_attribute_category => p_rec.attribute_category ,
294 p_attribute1 => p_rec.attribute1 ,
295 p_attribute2 => p_rec.attribute2 ,
296 p_attribute3 => p_rec.attribute3 ,
297 p_attribute4 => p_rec.attribute4 ,
298 p_attribute5 => p_rec.attribute5 ,
299 p_attribute6 => p_rec.attribute6 ,
300 p_attribute7 => p_rec.attribute7 ,
301 p_attribute8 => p_rec.attribute8 ,
302 p_attribute9 => p_rec.attribute9 ,
303 p_attribute10 => p_rec.attribute10 ,
304 p_attribute11 => p_rec.attribute11 ,
305 p_attribute12 => p_rec.attribute12 ,
306 p_attribute13 => p_rec.attribute13 ,
307 p_attribute14 => p_rec.attribute14 ,
308 p_attribute15 => p_rec.attribute15 ,
309 p_attribute16 => p_rec.attribute16 ,
310 p_attribute17 => p_rec.attribute17 ,
311 p_attribute18 => p_rec.attribute18 ,
312 p_attribute19 => p_rec.attribute19 ,
313 p_attribute20 => p_rec.attribute20 ,
314 p_object_id => p_rec.object_id ,
315 p_object_name => p_rec.object_name ,
316 p_party_id => p_rec.party_id , -- HR/TCA merge
317 -- BUG3356369
318 p_qualification_type_id => p_rec.qualification_type_id,
319 p_unit_standard_type => p_rec.unit_standard_type,
320 p_status => p_rec.status,
321 p_information_category => p_rec.information_category ,
322 p_information1 => p_rec.information1 ,
323 p_information2 => p_rec.information2 ,
324 p_information3 => p_rec.information3 ,
325 p_information4 => p_rec.information4 ,
326 p_information5 => p_rec.information5 ,
327 p_information6 => p_rec.information6 ,
328 p_information7 => p_rec.information7 ,
329 p_information8 => p_rec.information8 ,
330 p_information9 => p_rec.information9 ,
331 p_information10 => p_rec.information10 ,
332 p_information11 => p_rec.information11 ,
333 p_information12 => p_rec.information12 ,
334 p_information13 => p_rec.information13 ,
335 p_information14 => p_rec.information14 ,
336 p_information15 => p_rec.information15 ,
337 p_information16 => p_rec.information16 ,
338 p_information17 => p_rec.information17 ,
339 p_information18 => p_rec.information18 ,
340 p_information19 => p_rec.information19 ,
341 p_information20 => p_rec.information20 ,
342 p_achieved_date => p_rec.achieved_date ,
343 p_appr_line_score => p_rec.appr_line_score,
344 p_business_group_id_o => per_cel_shd.g_old_rec.business_group_id ,
345 p_object_version_number_o => per_cel_shd.g_old_rec.object_version_number,
346 p_type_o => per_cel_shd.g_old_rec.type ,
347 p_competence_id_o => per_cel_shd.g_old_rec.competence_id ,
348 -- p_member_competence_set_id_o =>
349 -- per_cel_shd.g_old_rec.member_competence_set_id ,
350 p_proficiency_level_id_o => per_cel_shd.g_old_rec.proficiency_level_id ,
351 p_high_proficiency_level_id_o =>
352 per_cel_shd.g_old_rec.high_proficiency_level_id ,
353 p_weighting_level_id_o => per_cel_shd.g_old_rec.weighting_level_id ,
354 p_rating_level_id_o => per_cel_shd.g_old_rec.rating_level_id ,
355 p_person_id_o => per_cel_shd.g_old_rec.person_id ,
356 p_enterprise_id_o => per_cel_shd.g_old_rec.enterprise_id ,
357 p_job_id_o => per_cel_shd.g_old_rec.job_id ,
358 p_valid_grade_id_o => per_cel_shd.g_old_rec.valid_grade_id ,
359 p_position_id_o => per_cel_shd.g_old_rec.position_id ,
360 p_organization_id_o => per_cel_shd.g_old_rec.organization_id ,
361 -- p_work_item_id_o => per_cel_shd.g_old_rec.work_item_id ,
362 -- p_competence_set_id_o => per_cel_shd.g_old_rec.competence_set_id ,
363 p_parent_competence_element_o =>
364 per_cel_shd.g_old_rec.parent_competence_element_id ,
365 p_activity_version_id_o => per_cel_shd.g_old_rec.activity_version_id ,
366 p_assessment_id_o => per_cel_shd.g_old_rec.assessment_id ,
367 p_assessment_type_id_o => per_cel_shd.g_old_rec.assessment_type_id ,
368 p_mandatory_o => per_cel_shd.g_old_rec.mandatory ,
369 p_effective_date_from_o => per_cel_shd.g_old_rec.effective_date_from ,
370 p_effective_date_to_o => per_cel_shd.g_old_rec.effective_date_to ,
371 p_group_competence_type_o => per_cel_shd.g_old_rec.group_competence_type,
372 p_competence_type_o => per_cel_shd.g_old_rec.competence_type ,
373 p_sequence_number_o => per_cel_shd.g_old_rec.sequence_number ,
374 p_normal_elapse_duration_o =>
375 per_cel_shd.g_old_rec.normal_elapse_duration ,
376 p_normal_elapse_duration_uni_o =>
377 per_cel_shd.g_old_rec.normal_elapse_duration_unit ,
378 p_source_of_proficiency_leve_o =>
379 per_cel_shd.g_old_rec.source_of_proficiency_level ,
380 p_line_score_o => per_cel_shd.g_old_rec.line_score ,
381 p_certification_date_o => per_cel_shd.g_old_rec.certification_date ,
382 p_certification_method_o => per_cel_shd.g_old_rec.certification_method ,
383 p_next_certification_date_o =>
384 per_cel_shd.g_old_rec.next_certification_date ,
385 p_comments_o => per_cel_shd.g_old_rec.comments ,
386 p_attribute_category_o => per_cel_shd.g_old_rec.attribute_category ,
387 p_attribute1_o => per_cel_shd.g_old_rec.attribute1 ,
388 p_attribute2_o => per_cel_shd.g_old_rec.attribute2 ,
389 p_attribute3_o => per_cel_shd.g_old_rec.attribute3 ,
390 p_attribute4_o => per_cel_shd.g_old_rec.attribute4 ,
391 p_attribute5_o => per_cel_shd.g_old_rec.attribute5 ,
392 p_attribute6_o => per_cel_shd.g_old_rec.attribute6 ,
393 p_attribute7_o => per_cel_shd.g_old_rec.attribute7 ,
394 p_attribute8_o => per_cel_shd.g_old_rec.attribute8 ,
395 p_attribute9_o => per_cel_shd.g_old_rec.attribute9 ,
396 p_attribute10_o => per_cel_shd.g_old_rec.attribute10 ,
397 p_attribute11_o => per_cel_shd.g_old_rec.attribute11 ,
398 p_attribute12_o => per_cel_shd.g_old_rec.attribute12 ,
399 p_attribute13_o => per_cel_shd.g_old_rec.attribute13 ,
400 p_attribute14_o => per_cel_shd.g_old_rec.attribute14 ,
401 p_attribute15_o => per_cel_shd.g_old_rec.attribute15 ,
402 p_attribute16_o => per_cel_shd.g_old_rec.attribute16 ,
403 p_attribute17_o => per_cel_shd.g_old_rec.attribute17 ,
404 p_attribute18_o => per_cel_shd.g_old_rec.attribute18 ,
405 p_attribute19_o => per_cel_shd.g_old_rec.attribute19 ,
406 p_attribute20_o => per_cel_shd.g_old_rec.attribute20 ,
407 p_object_id_o => per_cel_shd.g_old_rec.object_id ,
408 p_object_name_o => per_cel_shd.g_old_rec.object_name ,
409 p_party_id_o => per_cel_shd.g_old_rec.party_id, -- HR/TCA merge
410 -- BUG3356369
411 p_qualification_type_id_o => per_cel_shd.g_old_rec.qualification_type_id,
412 p_unit_standard_type_o => per_cel_shd.g_old_rec.unit_standard_type,
413 p_status_o => per_cel_shd.g_old_rec.status,
414 p_information_category_o => per_cel_shd.g_old_rec.information_category ,
415 p_information1_o => per_cel_shd.g_old_rec.information1 ,
416 p_information2_o => per_cel_shd.g_old_rec.information2 ,
417 p_information3_o => per_cel_shd.g_old_rec.information3 ,
418 p_information4_o => per_cel_shd.g_old_rec.information4 ,
419 p_information5_o => per_cel_shd.g_old_rec.information5 ,
420 p_information6_o => per_cel_shd.g_old_rec.information6 ,
421 p_information7_o => per_cel_shd.g_old_rec.information7 ,
422 p_information8_o => per_cel_shd.g_old_rec.information8 ,
423 p_information9_o => per_cel_shd.g_old_rec.information9 ,
424 p_information10_o => per_cel_shd.g_old_rec.information10 ,
425 p_information11_o => per_cel_shd.g_old_rec.information11 ,
426 p_information12_o => per_cel_shd.g_old_rec.information12 ,
427 p_information13_o => per_cel_shd.g_old_rec.information13 ,
428 p_information14_o => per_cel_shd.g_old_rec.information14 ,
429 p_information15_o => per_cel_shd.g_old_rec.information15 ,
430 p_information16_o => per_cel_shd.g_old_rec.information16 ,
431 p_information17_o => per_cel_shd.g_old_rec.information17 ,
432 p_information18_o => per_cel_shd.g_old_rec.information18 ,
433 p_information19_o => per_cel_shd.g_old_rec.information19 ,
434 p_information20_o => per_cel_shd.g_old_rec.information20 ,
435 p_achieved_date_o => per_cel_shd.g_old_rec.achieved_date ,
436 p_appr_line_score_o => per_cel_shd.g_old_rec.appr_line_score
437 );
438 exception
439 when hr_api.cannot_find_prog_unit then
440 hr_api.cannot_find_prog_unit_error
441 ( p_module_name => 'PER_COMPETENCE_ELEMENTS'
442 ,p_hook_type => 'AU'
443 );
444 end;
445 -- End of API User Hook for post_update
446 --
447 hr_utility.set_location(' Leaving:'||l_proc, 10);
448 End post_update;
449 --
450 -- ----------------------------------------------------------------------------
451 -- |-----------------------------< convert_defs >-----------------------------|
452 -- ----------------------------------------------------------------------------
453 -- {Start Of Comments}
454 --
455 -- Description:
456 -- The Convert_Defs procedure has one very important function:
457 -- It must return the record structure for the row with all system defaulted
458 -- values converted into its corresponding parameter value for update. When
459 -- we attempt to update a row through the Upd process , certain
460 -- parameters can be defaulted which enables flexibility in the calling of
461 -- the upd process (e.g. only attributes which need to be updated need to be
462 -- specified). For the upd process to determine which attributes
463 -- have NOT been specified we need to check if the parameter has a reserved
464 -- system default value. Therefore, for all parameters which have a
465 -- corresponding reserved system default mechanism specified we need to
466 -- check if a system default is being used. If a system default is being
467 -- used then we convert the defaulted value into its corresponding attribute
468 -- value held in the g_old_rec data structure.
469 --
470 -- Pre Conditions:
471 -- This private function can only be called from the upd process.
472 --
473 -- In Parameters:
474 -- A Pl/Sql record structre.
475 --
476 -- Post Success:
477 -- The record structure will be returned with all system defaulted parameter
478 -- values converted into its current row attribute value.
479 --
480 -- Post Failure:
481 -- No direct error handling is required within this function. Any possible
482 -- errors within this procedure will be a PL/SQL value error due to conversion
483
484 -- of datatypes or data lengths.
485 --
486 -- Developer Implementation Notes:
487 -- None.
488 --
489 -- Access Status:
490 -- Internal Table Handler Use Only.
491 --
492 -- {End Of Comments}
493 -- ----------------------------------------------------------------------------
494 Procedure convert_defs(p_rec in out nocopy per_cel_shd.g_rec_type) is
495 --
496 l_proc varchar2(72) := g_package||'convert_defs';
497 --
498 Begin
499 --
500 hr_utility.set_location('Entering:'||l_proc, 5);
501 --
502 -- We must now examine each argument value in the
503 -- p_rec plsql record structure
504 -- to see if a system default is being used. If a system default
505 -- is being used then we must set to the 'current' argument value.
506 --
507 If (p_rec.type = hr_api.g_varchar2) then
508 p_rec.type :=
509 per_cel_shd.g_old_rec.type;
510 End If;
511 If (p_rec.business_group_id = hr_api.g_number) then
512 p_rec.business_group_id :=
513 per_cel_shd.g_old_rec.business_group_id;
514 End If;
515 If (p_rec.competence_id = hr_api.g_number) then
516 p_rec.competence_id :=
517 per_cel_shd.g_old_rec.competence_id;
518 End If;
519 If (p_rec.proficiency_level_id = hr_api.g_number) then
520 p_rec.proficiency_level_id :=
521 per_cel_shd.g_old_rec.proficiency_level_id;
522 End If;
523 If (p_rec.high_proficiency_level_id = hr_api.g_number) then
524 p_rec.high_proficiency_level_id :=
525 per_cel_shd.g_old_rec.high_proficiency_level_id;
526 End If;
527 If (p_rec.weighting_level_id = hr_api.g_number) then
528 p_rec.weighting_level_id :=
529 per_cel_shd.g_old_rec.weighting_level_id;
530 End If;
531 If (p_rec.rating_level_id = hr_api.g_number) then
532 p_rec.rating_level_id :=
533 per_cel_shd.g_old_rec.rating_level_id;
534 End If;
535 If (p_rec.person_id = hr_api.g_number) then
536 p_rec.person_id :=
537 per_cel_shd.g_old_rec.person_id;
538 End If;
539 If (p_rec.job_id = hr_api.g_number) then
540 p_rec.job_id :=
541 per_cel_shd.g_old_rec.job_id;
542 End If;
543 If (p_rec.valid_grade_id = hr_api.g_number) then
544 p_rec.valid_grade_id :=
545 per_cel_shd.g_old_rec.valid_grade_id;
546 end if;
547 If (p_rec.enterprise_id = hr_api.g_number) then
548 p_rec.enterprise_id :=
549 per_cel_shd.g_old_rec.enterprise_id;
550 End If;
551 If (p_rec.position_id = hr_api.g_number) then
552 p_rec.position_id :=
553 per_cel_shd.g_old_rec.position_id;
554 End If;
555 If (p_rec.organization_id = hr_api.g_number) then
556 p_rec.organization_id :=
557 per_cel_shd.g_old_rec.organization_id;
558 End If;
559 If (p_rec.parent_competence_element_id = hr_api.g_number) then
560 p_rec.parent_competence_element_id :=
561 per_cel_shd.g_old_rec.parent_competence_element_id;
562 End If;
563 If (p_rec.activity_version_id = hr_api.g_number) then
564 p_rec.activity_version_id :=
565 per_cel_shd.g_old_rec.activity_version_id;
566 End If;
567 If (p_rec.assessment_id = hr_api.g_number) then
568 p_rec.assessment_id :=
569 per_cel_shd.g_old_rec.assessment_id;
570 End If;
571 If (p_rec.assessment_type_id = hr_api.g_number) then
572 p_rec.assessment_type_id :=
573 per_cel_shd.g_old_rec.assessment_type_id;
574 End If;
575 If (p_rec.mandatory = hr_api.g_varchar2) then
576 p_rec.mandatory :=
577 per_cel_shd.g_old_rec.mandatory;
578 End If;
579 If (p_rec.effective_date_from = hr_api.g_date) then
580 p_rec.effective_date_from :=
581 per_cel_shd.g_old_rec.effective_date_from;
582 End If;
583 If (p_rec.effective_date_to = hr_api.g_date) then
584 p_rec.effective_date_to :=
585 per_cel_shd.g_old_rec.effective_date_to;
586 End If;
587 If (p_rec.group_competence_type = hr_api.g_varchar2) then
588 p_rec.group_competence_type :=
589 per_cel_shd.g_old_rec.group_competence_type;
590 End If;
591 If (p_rec.competence_type = hr_api.g_varchar2) then
592 p_rec.competence_type :=
593 per_cel_shd.g_old_rec.competence_type;
594 End If;
595 If (p_rec.normal_elapse_duration = hr_api.g_number) then
596 p_rec.normal_elapse_duration :=
597 per_cel_shd.g_old_rec.normal_elapse_duration;
598 End If;
599 If (p_rec.normal_elapse_duration_unit = hr_api.g_varchar2) then
600 p_rec.normal_elapse_duration_unit :=
601 per_cel_shd.g_old_rec.normal_elapse_duration_unit;
602 End If;
603 If (p_rec.sequence_number = hr_api.g_number) then
604 p_rec.sequence_number :=
605 per_cel_shd.g_old_rec.sequence_number;
606 End If;
607 If (p_rec.source_of_proficiency_level = hr_api.g_varchar2) then
608 p_rec.source_of_proficiency_level :=
609 per_cel_shd.g_old_rec.source_of_proficiency_level;
610 End If;
611 If (p_rec.line_score = hr_api.g_number) then
612 p_rec.line_score :=
613 per_cel_shd.g_old_rec.line_score;
614 End If;
615 If (p_rec.certification_date = hr_api.g_date) then
616 p_rec.certification_date :=
617 per_cel_shd.g_old_rec.certification_date;
618 End If;
619 If (p_rec.certification_method = hr_api.g_varchar2) then
620 p_rec.certification_method :=
621 per_cel_shd.g_old_rec.certification_method;
622 End If;
623 If (p_rec.next_certification_date = hr_api.g_date) then
624 p_rec.next_certification_date :=
625 per_cel_shd.g_old_rec.next_certification_date;
626 End If;
627 If (p_rec.comments = hr_api.g_varchar2) then
628 p_rec.comments :=
629 per_cel_shd.g_old_rec.comments;
630 End If;
631 If (p_rec.attribute_category = hr_api.g_varchar2) then
632 p_rec.attribute_category :=
633 per_cel_shd.g_old_rec.attribute_category;
634 End If;
635 If (p_rec.attribute1 = hr_api.g_varchar2) then
636 p_rec.attribute1 :=
637 per_cel_shd.g_old_rec.attribute1;
638 End If;
639 If (p_rec.attribute2 = hr_api.g_varchar2) then
640 p_rec.attribute2 :=
641 per_cel_shd.g_old_rec.attribute2;
642 End If;
643 If (p_rec.attribute3 = hr_api.g_varchar2) then
644 p_rec.attribute3 :=
645 per_cel_shd.g_old_rec.attribute3;
646 End If;
647 If (p_rec.attribute4 = hr_api.g_varchar2) then
648 p_rec.attribute4 :=
649 per_cel_shd.g_old_rec.attribute4;
650 End If;
651 If (p_rec.attribute5 = hr_api.g_varchar2) then
652 p_rec.attribute5 :=
653 per_cel_shd.g_old_rec.attribute5;
654 End If;
655 If (p_rec.attribute6 = hr_api.g_varchar2) then
656 p_rec.attribute6 :=
657 per_cel_shd.g_old_rec.attribute6;
658 End If;
659 If (p_rec.attribute7 = hr_api.g_varchar2) then
660 p_rec.attribute7 :=
661 per_cel_shd.g_old_rec.attribute7;
662 End If;
663 If (p_rec.attribute8 = hr_api.g_varchar2) then
664 p_rec.attribute8 :=
665 per_cel_shd.g_old_rec.attribute8;
666 End If;
667 If (p_rec.attribute9 = hr_api.g_varchar2) then
668 p_rec.attribute9 :=
669 per_cel_shd.g_old_rec.attribute9;
670 End If;
671 If (p_rec.attribute10 = hr_api.g_varchar2) then
672 p_rec.attribute10 :=
673 per_cel_shd.g_old_rec.attribute10;
674 End If;
675 If (p_rec.attribute11 = hr_api.g_varchar2) then
676 p_rec.attribute11 :=
677 per_cel_shd.g_old_rec.attribute11;
678 End If;
679 If (p_rec.attribute12 = hr_api.g_varchar2) then
680 p_rec.attribute12 :=
681 per_cel_shd.g_old_rec.attribute12;
682 End If;
683 If (p_rec.attribute13 = hr_api.g_varchar2) then
684 p_rec.attribute13 :=
685 per_cel_shd.g_old_rec.attribute13;
686 End If;
687 If (p_rec.attribute14 = hr_api.g_varchar2) then
688 p_rec.attribute14 :=
689 per_cel_shd.g_old_rec.attribute14;
690 End If;
691 If (p_rec.attribute15 = hr_api.g_varchar2) then
692 p_rec.attribute15 :=
693 per_cel_shd.g_old_rec.attribute15;
694 End If;
695 If (p_rec.attribute16 = hr_api.g_varchar2) then
696 p_rec.attribute16 :=
697 per_cel_shd.g_old_rec.attribute16;
698 End If;
699 If (p_rec.attribute17 = hr_api.g_varchar2) then
700 p_rec.attribute17 :=
701 per_cel_shd.g_old_rec.attribute17;
702 End If;
703 If (p_rec.attribute18 = hr_api.g_varchar2) then
704 p_rec.attribute18 :=
705 per_cel_shd.g_old_rec.attribute18;
706 End If;
707 If (p_rec.attribute19 = hr_api.g_varchar2) then
708 p_rec.attribute19 :=
709 per_cel_shd.g_old_rec.attribute19;
710 End If;
711 If (p_rec.attribute20 = hr_api.g_varchar2) then
712 p_rec.attribute20 :=
713 per_cel_shd.g_old_rec.attribute20;
714 End If;
715 if (p_rec.object_id = hr_api.g_number) then
716 p_rec.object_id :=
717 per_cel_shd.g_old_rec.object_id;
718 End If;
719
720 If (p_rec.object_name = hr_api.g_varchar2) then
721 p_rec.object_name :=
722 per_cel_shd.g_old_rec.object_name;
723 End if;
724 -- ngundura added last two if conditions for object_id and name
725 If (p_rec.party_id = hr_api.g_number) then -- HR/TCA merge
726 p_rec.party_id :=
727 per_cel_shd.g_old_rec.party_id;
728 End If;
729 If (p_rec.qualification_type_id = hr_api.g_number) then
730 p_rec.qualification_type_id :=
731 per_cel_shd.g_old_rec.qualification_type_id;
732 End If;
733 If (p_rec.unit_standard_type = hr_api.g_varchar2) then
734 p_rec.unit_standard_type :=
735 per_cel_shd.g_old_rec.unit_standard_type;
736 End If;
737 If (p_rec.status = hr_api.g_varchar2) then
738 p_rec.status :=
739 per_cel_shd.g_old_rec.status;
740 End If;
741 If (p_rec.information_category = hr_api.g_varchar2) then
742 p_rec.information_category :=
743 per_cel_shd.g_old_rec.information_category;
744 End If;
745 If (p_rec.information1 = hr_api.g_varchar2) then
746 p_rec.information1 :=
747 per_cel_shd.g_old_rec.information1;
748 End If;
749 If (p_rec.information2 = hr_api.g_varchar2) then
750 p_rec.information2 :=
751 per_cel_shd.g_old_rec.information2;
752 End If;
753 If (p_rec.information3 = hr_api.g_varchar2) then
754 p_rec.information3 :=
755 per_cel_shd.g_old_rec.information3;
756 End If;
757 If (p_rec.information4 = hr_api.g_varchar2) then
758 p_rec.information4 :=
759 per_cel_shd.g_old_rec.information4;
760 End If;
761 If (p_rec.information5 = hr_api.g_varchar2) then
762 p_rec.information5 :=
763 per_cel_shd.g_old_rec.information5;
764 End If;
765 If (p_rec.information6 = hr_api.g_varchar2) then
766 p_rec.information6 :=
767 per_cel_shd.g_old_rec.information6;
768 End If;
769 If (p_rec.information7 = hr_api.g_varchar2) then
770 p_rec.information7 :=
771 per_cel_shd.g_old_rec.information7;
772 End If;
773 If (p_rec.information8 = hr_api.g_varchar2) then
774 p_rec.information8 :=
775 per_cel_shd.g_old_rec.information8;
776 End If;
777 If (p_rec.information9 = hr_api.g_varchar2) then
778 p_rec.information9 :=
779 per_cel_shd.g_old_rec.information9;
780 End If;
781 If (p_rec.information10 = hr_api.g_varchar2) then
782 p_rec.information10 :=
783 per_cel_shd.g_old_rec.information10;
784 End If;
785 If (p_rec.information11 = hr_api.g_varchar2) then
786 p_rec.information11 :=
787 per_cel_shd.g_old_rec.information11;
788 End If;
789 If (p_rec.information12 = hr_api.g_varchar2) then
790 p_rec.information12 :=
791 per_cel_shd.g_old_rec.information12;
792 End If;
793 If (p_rec.information13 = hr_api.g_varchar2) then
794 p_rec.information13 :=
795 per_cel_shd.g_old_rec.information13;
796 End If;
797 If (p_rec.information14 = hr_api.g_varchar2) then
798 p_rec.information14 :=
799 per_cel_shd.g_old_rec.information14;
800 End If;
801 If (p_rec.information15 = hr_api.g_varchar2) then
802 p_rec.information15 :=
803 per_cel_shd.g_old_rec.information15;
804 End If;
805 If (p_rec.information16 = hr_api.g_varchar2) then
806 p_rec.information16 :=
807 per_cel_shd.g_old_rec.information16;
808 End If;
809 If (p_rec.information17 = hr_api.g_varchar2) then
810 p_rec.information17 :=
811 per_cel_shd.g_old_rec.information17;
812 End If;
813 If (p_rec.information18 = hr_api.g_varchar2) then
814 p_rec.information18 :=
815 per_cel_shd.g_old_rec.information18;
816 End If;
817 If (p_rec.information19 = hr_api.g_varchar2) then
818 p_rec.information19 :=
819 per_cel_shd.g_old_rec.information19;
820 End If;
821 If (p_rec.information20 = hr_api.g_varchar2) then
822 p_rec.information20 :=
823 per_cel_shd.g_old_rec.information20;
824 End If;
825 If (p_rec.achieved_date = hr_api.g_date) then
826 p_rec.achieved_date :=
827 per_cel_shd.g_old_rec.achieved_date;
828 End If;
829 If (p_rec.appr_line_score = hr_api.g_number) then
830 p_rec.appr_line_score :=
831 per_cel_shd.g_old_rec.appr_line_score;
832 End If;
833 --
834 hr_utility.set_location(' Leaving:'||l_proc, 10);
835 --
836 End convert_defs;
837 --
838 -- ----------------------------------------------------------------------------
839 -- |---------------------------------< upd >----------------------------------|
840 -- ----------------------------------------------------------------------------
841 Procedure upd
842 (
843 p_rec in out nocopy per_cel_shd.g_rec_type,
844 p_validate in boolean default false,
845 p_effective_date in date
846 ) is
847 --
848 l_proc varchar2(72) := g_package||'upd';
849 --
850 Begin
851 hr_utility.set_location('Entering:'||l_proc, 5);
852 --
853 -- Determine if the business process is to be validated.
854 --
855 If p_validate then
856 --
857 -- Issue the savepoint.
858 --
859 SAVEPOINT upd_per_cel;
860 End If;
861 --
862 -- We must lock the row which we need to update.
863 --
864 per_cel_shd.lck
865 (
866 p_rec.competence_element_id,
867 p_rec.object_version_number
868 );
869 --
870 -- 1. During an update system defaults are used to determine if
871 -- arguments have been defaulted or not. We must therefore
872 -- derive the full record structure values to be updated.
873 --
874 -- 2. Call the supporting update validate operations.
875 --
876 convert_defs(p_rec);
877
878 per_cel_bus.update_validate(p_rec,p_effective_date);
879 --
880 -- Call to raise any errors on multi-message list
881 hr_multi_message.end_validation_set;
882 --
883 -- Call the supporting pre-update operation
884 --
885 pre_update(p_rec);
886 --
887 -- Update the row.
888 --
889 update_dml(p_rec);
890 --
891 -- Call the supporting post-update operation
892 --
893 post_update(p_rec);
894 --
895 -- Call to raise any errors on multi-message list
896 hr_multi_message.end_validation_set;
897 --
898 -- If we are validating then raise the Validate_Enabled exception
899 --
900 If p_validate then
901 Raise HR_Api.Validate_Enabled;
902 End If;
903 --
904 hr_utility.set_location(' Leaving:'||l_proc, 10);
905 Exception
906 When HR_Api.Validate_Enabled Then
907 --
908 -- As the Validate_Enabled exception has been raised
909 -- we must rollback to the savepoint
910 --
911 ROLLBACK TO upd_per_cel;
912 End upd;
913 --
914 -- ----------------------------------------------------------------------------
915 -- |---------------------------------< upd >----------------------------------|
916 -- ----------------------------------------------------------------------------
917 Procedure upd
918 (
919 p_competence_element_id in number,
920 p_object_version_number in out nocopy number,
921 p_proficiency_level_id in number default hr_api.g_number,
922 p_high_proficiency_level_id in number default hr_api.g_number,
923 p_weighting_level_id in number default hr_api.g_number,
924 p_rating_level_id in number default hr_api.g_number,
925 p_mandatory in varchar2 default hr_api.g_varchar2,
926 p_effective_date_from in date default hr_api.g_date,
927 p_effective_date_to in date default hr_api.g_date,
928 p_group_competence_type in varchar2 default hr_api.g_varchar2,
929 p_competence_type in varchar2 default hr_api.g_varchar2,
930 p_normal_elapse_duration in number default hr_api.g_number,
931 p_normal_elapse_duration_unit in varchar2 default hr_api.g_varchar2,
932 p_sequence_number in number default hr_api.g_number,
933 p_source_of_proficiency_level in varchar2 default hr_api.g_varchar2,
934 p_line_score in number default hr_api.g_number,
935 p_certification_date in date default hr_api.g_date,
936 p_certification_method in varchar2 default hr_api.g_varchar2,
937 p_next_certification_date in date default hr_api.g_date,
938 p_comments in varchar2 default hr_api.g_varchar2,
939 p_attribute_category in varchar2 default hr_api.g_varchar2,
940 p_attribute1 in varchar2 default hr_api.g_varchar2,
941 p_attribute2 in varchar2 default hr_api.g_varchar2,
942 p_attribute3 in varchar2 default hr_api.g_varchar2,
943 p_attribute4 in varchar2 default hr_api.g_varchar2,
944 p_attribute5 in varchar2 default hr_api.g_varchar2,
945 p_attribute6 in varchar2 default hr_api.g_varchar2,
946 p_attribute7 in varchar2 default hr_api.g_varchar2,
947 p_attribute8 in varchar2 default hr_api.g_varchar2,
948 p_attribute9 in varchar2 default hr_api.g_varchar2,
949 p_attribute10 in varchar2 default hr_api.g_varchar2,
950 p_attribute11 in varchar2 default hr_api.g_varchar2,
951 p_attribute12 in varchar2 default hr_api.g_varchar2,
952 p_attribute13 in varchar2 default hr_api.g_varchar2,
953 p_attribute14 in varchar2 default hr_api.g_varchar2,
954 p_attribute15 in varchar2 default hr_api.g_varchar2,
955 p_attribute16 in varchar2 default hr_api.g_varchar2,
956 p_attribute17 in varchar2 default hr_api.g_varchar2,
957 p_attribute18 in varchar2 default hr_api.g_varchar2,
958 p_attribute19 in varchar2 default hr_api.g_varchar2,
959 p_attribute20 in varchar2 default hr_api.g_varchar2,
960 p_effective_date in date,
961 p_party_id in number default hr_api.g_number,
962 p_validate in boolean default false,
963 -- p_object_id in number default hr_api.g_number,
964 -- p_object_name in varchar2 default hr_api.g_varchar2
965 p_qualification_type_id in number default hr_api.g_number,
966 p_unit_standard_type in varchar2 default hr_api.g_varchar2,
967 p_status in varchar2 default hr_api.g_varchar2,
968 p_information_category in varchar2 default hr_api.g_varchar2,
969 p_information1 in varchar2 default hr_api.g_varchar2,
970 p_information2 in varchar2 default hr_api.g_varchar2,
971 p_information3 in varchar2 default hr_api.g_varchar2,
972 p_information4 in varchar2 default hr_api.g_varchar2,
973 p_information5 in varchar2 default hr_api.g_varchar2,
974 p_information6 in varchar2 default hr_api.g_varchar2,
975 p_information7 in varchar2 default hr_api.g_varchar2,
976 p_information8 in varchar2 default hr_api.g_varchar2,
977 p_information9 in varchar2 default hr_api.g_varchar2,
978 p_information10 in varchar2 default hr_api.g_varchar2,
979 p_information11 in varchar2 default hr_api.g_varchar2,
980 p_information12 in varchar2 default hr_api.g_varchar2,
981 p_information13 in varchar2 default hr_api.g_varchar2,
982 p_information14 in varchar2 default hr_api.g_varchar2,
983 p_information15 in varchar2 default hr_api.g_varchar2,
984 p_information16 in varchar2 default hr_api.g_varchar2,
985 p_information17 in varchar2 default hr_api.g_varchar2,
986 p_information18 in varchar2 default hr_api.g_varchar2,
987 p_information19 in varchar2 default hr_api.g_varchar2,
988 p_information20 in varchar2 default hr_api.g_varchar2,
989 p_achieved_date in date default hr_api.g_date,
990 p_appr_line_score in number default hr_api.g_number
991 ) is
992 --
993 l_rec per_cel_shd.g_rec_type;
994 l_proc varchar2(72) := g_package||'upd';
995 --
996 Begin
997 hr_utility.set_location('Entering:'||l_proc, 5);
998 --
999 -- Call conversion function to turn arguments into the
1000 -- l_rec structure.
1001 --
1002 l_rec :=
1003 per_cel_shd.convert_args
1004 (
1005 p_competence_element_id,
1006 p_object_version_number,
1007 hr_api.g_varchar2,
1008 hr_api.g_number,
1009 hr_api.g_number,
1010 hr_api.g_number,
1011 p_proficiency_level_id,
1012 p_high_proficiency_level_id,
1013 p_weighting_level_id,
1014 p_rating_level_id,
1015 hr_api.g_number,
1016 hr_api.g_number,
1017 hr_api.g_number,
1018 hr_api.g_number,
1019 hr_api.g_number,
1020 hr_api.g_number,
1021 hr_api.g_number,
1022 hr_api.g_number,
1023 hr_api.g_number,
1024 p_mandatory,
1025 p_effective_date_from,
1026 p_effective_date_to,
1027 p_group_competence_type,
1028 p_competence_type,
1029 p_normal_elapse_duration,
1030 p_normal_elapse_duration_unit,
1031 p_sequence_number,
1032 p_source_of_proficiency_level,
1033 p_line_score,
1034 p_certification_date,
1035 p_certification_method,
1036 p_next_certification_date,
1037 p_comments,
1038 p_attribute_category,
1039 p_attribute1,
1040 p_attribute2,
1041 p_attribute3,
1042 p_attribute4,
1043 p_attribute5,
1044 p_attribute6,
1045 p_attribute7,
1046 p_attribute8,
1047 p_attribute9,
1048 p_attribute10,
1049 p_attribute11,
1050 p_attribute12,
1051 p_attribute13,
1052 p_attribute14,
1053 p_attribute15,
1054 p_attribute16,
1055 p_attribute17,
1056 p_attribute18,
1057 p_attribute19,
1058 p_attribute20,
1059 hr_api.g_number,
1060 hr_api.g_varchar2,
1061 p_party_id, -- HR/TCA merge
1062 p_qualification_type_id,
1063 p_unit_standard_type,
1064 p_status,
1065 p_information_category,
1066 p_information1,
1067 p_information2,
1068 p_information3,
1069 p_information4,
1070 p_information5,
1071 p_information6,
1072 p_information7,
1073 p_information8,
1074 p_information9,
1075 p_information10,
1076 p_information11,
1077 p_information12,
1078 p_information13,
1079 p_information14,
1080 p_information15,
1081 p_information16,
1082 p_information17,
1083 p_information18,
1084 p_information19,
1085 p_information20,
1086 p_achieved_date,
1087 p_appr_line_score
1088 );
1089 --
1090 -- Having converted the arguments into the
1091 -- plsql record structure we call the corresponding record
1092 -- business process.
1093 --
1094 upd(l_rec, p_validate,p_effective_date);
1095 p_object_version_number := l_rec.object_version_number;
1096 --
1097 hr_utility.set_location(' Leaving:'||l_proc, 10);
1098 End upd;
1099 --
1100 end per_cel_upd;