1 Package Body per_prt_upd as
2 /* $Header: peprtrhi.pkb 120.2 2006/05/03 18:37:44 kandra noship $ */
3
4 -- ---------------------------------------------------------------------------+
5 -- | Private Global Definitions |
6 -- ---------------------------------------------------------------------------+
7
8 g_package varchar2(33) := ' per_prt_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_prt_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
66 -- Update the per_performance_ratings Row
67
68 update per_performance_ratings
69 set
70 performance_rating_id = p_rec.performance_rating_id,
71 person_id = p_rec.person_id,
72 object_version_number = p_rec.object_version_number,
73 performance_level_id = p_rec.performance_level_id,
74 comments = p_rec.comments,
75 attribute_category = p_rec.attribute_category,
76 attribute1 = p_rec.attribute1,
77 attribute2 = p_rec.attribute2,
78 attribute3 = p_rec.attribute3,
79 attribute4 = p_rec.attribute4,
80 attribute5 = p_rec.attribute5,
81 attribute6 = p_rec.attribute6,
82 attribute7 = p_rec.attribute7,
83 attribute8 = p_rec.attribute8,
84 attribute9 = p_rec.attribute9,
85 attribute10 = p_rec.attribute10,
86 attribute11 = p_rec.attribute11,
87 attribute12 = p_rec.attribute12,
88 attribute13 = p_rec.attribute13,
89 attribute14 = p_rec.attribute14,
90 attribute15 = p_rec.attribute15,
91 attribute16 = p_rec.attribute16,
92 attribute17 = p_rec.attribute17,
93 attribute18 = p_rec.attribute18,
94 attribute19 = p_rec.attribute19,
95 attribute20 = p_rec.attribute20,
96 appr_line_score = p_rec.appr_line_score
97 where performance_rating_id = p_rec.performance_rating_id;
98
99 hr_utility.set_location(' Leaving:'||l_proc, 10);
100
101 Exception
102 When hr_api.check_integrity_violated Then
103 -- A check constraint has been violated
104 per_prt_shd.constraint_error
105 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
106 When hr_api.parent_integrity_violated Then
107 -- Parent integrity has been violated
108 per_prt_shd.constraint_error
109 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
110 When hr_api.unique_integrity_violated Then
111 -- Unique integrity has been violated
112 per_prt_shd.constraint_error
113 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
114 When Others Then
115 Raise;
116 End update_dml;
117
118 -- ---------------------------------------------------------------------------+
119 -- |------------------------------< pre_update >------------------------------|
120 -- ---------------------------------------------------------------------------+
121 -- {Start Of Comments}
122
123 -- Description:
124 -- This private procedure contains any processing which is required before
125 -- the update dml.
126
127 -- Pre Conditions:
128 -- This is an internal procedure which is called from the upd procedure.
129
130 -- In Parameters:
131 -- A Pl/Sql record structre.
132
133 -- Post Success:
134 -- Processing continues.
135
136 -- Post Failure:
137 -- If an error has occurred, an error message and exception will be raised
138 -- but not handled.
139
140 -- Developer Implementation Notes:
141 -- Any pre-processing required before the update dml is issued should be
142 -- coded within this procedure. It is important to note that any 3rd party
143 -- maintenance should be reviewed before placing in this procedure.
144
145 -- Access Status:
146 -- Internal Table Handler Use Only.
147
148 -- {End Of Comments}
149 -- ---------------------------------------------------------------------------+
150 Procedure pre_update(p_rec in per_prt_shd.g_rec_type) is
151
152 l_proc varchar2(72) := g_package||'pre_update';
153
154 Begin
155 hr_utility.set_location('Entering:'||l_proc, 5);
156
157 hr_utility.set_location(' Leaving:'||l_proc, 10);
158 End pre_update;
159
160 -- ---------------------------------------------------------------------------+
161 -- |-----------------------------< post_update >------------------------------|
162 -- ---------------------------------------------------------------------------+
163 -- {Start Of Comments}
164
165 -- Description:
166 -- This private procedure contains any processing which is required after the
167 -- update dml.
168
169 -- Pre Conditions:
170 -- This is an internal procedure which is called from the upd procedure.
171
172 -- In Parameters:
173 -- A Pl/Sql record structre.
174
175 -- Post Success:
176 -- Processing continues.
177
178 -- Post Failure:
179 -- If an error has occurred, an error message and exception will be raised
180 -- but not handled.
181
182 -- Developer Implementation Notes:
183 -- Any post-processing required after the update dml is issued should be
184 -- coded within this procedure. It is important to note that any 3rd party
185 -- maintenance should be reviewed before placing in this procedure.
186
187 -- Access Status:
188 -- Internal Table Handler Use Only.
189
190 -- {End Of Comments}
191 -- ---------------------------------------------------------------------------+
192 Procedure post_update(p_rec in per_prt_shd.g_rec_type) is
193
194 l_proc varchar2(72) := g_package||'post_update';
195
196 Begin
197 hr_utility.set_location('Entering:'||l_proc, 5);
198
199 -- This is a hook point and the user hook for post_update is called here.
200
201 begin
202 per_prt_rku.after_update (
203 p_performance_rating_id => p_rec.performance_rating_id ,
204 p_person_id =>p_rec.person_id,
205 p_objective_id => p_rec.objective_id ,
206 p_object_version_number => p_rec.object_version_number ,
207 p_appraisal_id => p_rec.appraisal_id ,
208 p_performance_level_id => p_rec.performance_level_id ,
209 p_comments => p_rec.comments ,
210 p_attribute_category => p_rec.attribute_category ,
211 p_attribute1 => p_rec.attribute1 ,
212 p_attribute2 => p_rec.attribute2 ,
213 p_attribute3 => p_rec.attribute3 ,
214 p_attribute4 => p_rec.attribute4 ,
215 p_attribute5 => p_rec.attribute5 ,
216 p_attribute6 => p_rec.attribute6 ,
217 p_attribute7 => p_rec.attribute7 ,
218 p_attribute8 => p_rec.attribute8 ,
219 p_attribute9 => p_rec.attribute9 ,
220 p_attribute10 => p_rec.attribute10 ,
221 p_attribute11 => p_rec.attribute11 ,
222 p_attribute12 => p_rec.attribute12 ,
223 p_attribute13 => p_rec.attribute13 ,
224 p_attribute14 => p_rec.attribute14 ,
225 p_attribute15 => p_rec.attribute15 ,
226 p_attribute16 => p_rec.attribute16 ,
227 p_attribute17 => p_rec.attribute17 ,
228 p_attribute18 => p_rec.attribute18 ,
229 p_attribute19 => p_rec.attribute19 ,
230 p_attribute20 => p_rec.attribute20 ,
231 p_appr_line_score => p_rec.appr_line_score ,
232 p_objective_id_o => per_prt_shd.g_old_rec.objective_id ,
233 p_object_version_number_o => per_prt_shd.g_old_rec.object_version_number,
234 p_appraisal_id_o => per_prt_shd.g_old_rec.appraisal_id ,
235 p_performance_level_id_o => per_prt_shd.g_old_rec.performance_level_id ,
236 p_comments_o => per_prt_shd.g_old_rec.comments ,
237 p_attribute_category_o => per_prt_shd.g_old_rec.attribute_category ,
238 p_attribute1_o => per_prt_shd.g_old_rec.attribute1 ,
239 p_attribute2_o => per_prt_shd.g_old_rec.attribute2 ,
240 p_attribute3_o => per_prt_shd.g_old_rec.attribute3 ,
241 p_attribute4_o => per_prt_shd.g_old_rec.attribute4 ,
242 p_attribute5_o => per_prt_shd.g_old_rec.attribute5 ,
243 p_attribute6_o => per_prt_shd.g_old_rec.attribute6 ,
244 p_attribute7_o => per_prt_shd.g_old_rec.attribute7 ,
245 p_attribute8_o => per_prt_shd.g_old_rec.attribute8 ,
246 p_attribute9_o => per_prt_shd.g_old_rec.attribute9 ,
247 p_attribute10_o => per_prt_shd.g_old_rec.attribute10 ,
248 p_attribute11_o => per_prt_shd.g_old_rec.attribute11 ,
249 p_attribute12_o => per_prt_shd.g_old_rec.attribute12 ,
250 p_attribute13_o => per_prt_shd.g_old_rec.attribute13 ,
251 p_attribute14_o => per_prt_shd.g_old_rec.attribute14 ,
252 p_attribute15_o => per_prt_shd.g_old_rec.attribute15 ,
253 p_attribute16_o => per_prt_shd.g_old_rec.attribute16 ,
254 p_attribute17_o => per_prt_shd.g_old_rec.attribute17 ,
255 p_attribute18_o => per_prt_shd.g_old_rec.attribute18 ,
256 p_attribute19_o => per_prt_shd.g_old_rec.attribute19 ,
257 p_attribute20_o => per_prt_shd.g_old_rec.attribute20 ,
258 p_appr_line_score_o => per_prt_shd.g_old_rec.appr_line_score);
259 exception
260 when hr_api.cannot_find_prog_unit then
261 hr_api.cannot_find_prog_unit_error
262 ( p_module_name => 'PER_PERFORMANCE_RATINGS'
263 ,p_hook_type => 'AU'
264 );
265 end;
266 -- End of API User Hook for post_update
267
268 hr_utility.set_location(' Leaving:'||l_proc, 10);
269 End post_update;
270
271 -- ---------------------------------------------------------------------------+
272 -- |-----------------------------< convert_defs >-----------------------------|
273 -- ---------------------------------------------------------------------------+
274 -- {Start Of Comments}
275
276 -- Description:
277 -- The Convert_Defs procedure has one very important function:
278 -- It must return the record structure for the row with all system defaulted
279 -- values converted into its corresponding parameter value for update. When
280 -- we attempt to update a row through the Upd process , certain
281 -- parameters can be defaulted which enables flexibility in the calling of
282 -- the upd process (e.g. only attributes which need to be updated need to be
283 -- specified). For the upd process to determine which attributes
284 -- have NOT been specified we need to check if the parameter has a reserved
285 -- system default value. Therefore, for all parameters which have a
286 -- corresponding reserved system default mechanism specified we need to
287 -- check if a system default is being used. If a system default is being
288 -- used then we convert the defaulted value into its corresponding attribute
289 -- value held in the g_old_rec data structure.
290
291 -- Pre Conditions:
292 -- This private function can only be called from the upd process.
293
294 -- In Parameters:
295 -- A Pl/Sql record structre.
296
297 -- Post Success:
298 -- The record structure will be returned with all system defaulted parameter
299 -- values converted into its current row attribute value.
300
301 -- Post Failure:
302 -- No direct error handling is required within this function. Any possible
303 -- errors within this procedure will be a PL/SQL value error due to conversion
304 -- of datatypes or data lengths.
305
306 -- Developer Implementation Notes:
307 -- None.
308
309 -- Access Status:
310 -- Internal Table Handler Use Only.
311
312 -- {End Of Comments}
313 -- ---------------------------------------------------------------------------+
314 Procedure convert_defs(p_rec in out nocopy per_prt_shd.g_rec_type) is
315
316 l_proc varchar2(72) := g_package||'convert_defs';
317
318 Begin
319
320 hr_utility.set_location('Entering:'||l_proc, 5);
321
322 -- We must now examine each argument value in the
323 -- p_rec plsql record structure
324 -- to see if a system default is being used. If a system default
325 -- is being used then we must set to the 'current' argument value.
326
327 If (p_rec.objective_id = hr_api.g_number) then
328 p_rec.objective_id :=
329 per_prt_shd.g_old_rec.objective_id;
330 End If;
331 If (p_rec.appraisal_id = hr_api.g_number) then
332 p_rec.appraisal_id :=
333 per_prt_shd.g_old_rec.appraisal_id;
334 End If;
335 If (p_rec.performance_level_id = hr_api.g_number) then
336 p_rec.performance_level_id :=
337 per_prt_shd.g_old_rec.performance_level_id;
338 End If;
339 If (p_rec.comments = hr_api.g_varchar2) then
340 p_rec.comments :=
341 per_prt_shd.g_old_rec.comments;
342 End If;
343 If (p_rec.attribute_category = hr_api.g_varchar2) then
344 p_rec.attribute_category :=
345 per_prt_shd.g_old_rec.attribute_category;
346 End If;
347 If (p_rec.attribute1 = hr_api.g_varchar2) then
348 p_rec.attribute1 :=
349 per_prt_shd.g_old_rec.attribute1;
350 End If;
351 If (p_rec.attribute2 = hr_api.g_varchar2) then
352 p_rec.attribute2 :=
353 per_prt_shd.g_old_rec.attribute2;
354 End If;
355 If (p_rec.attribute3 = hr_api.g_varchar2) then
356 p_rec.attribute3 :=
357 per_prt_shd.g_old_rec.attribute3;
358 End If;
359 If (p_rec.attribute4 = hr_api.g_varchar2) then
360 p_rec.attribute4 :=
361 per_prt_shd.g_old_rec.attribute4;
362 End If;
363 If (p_rec.attribute5 = hr_api.g_varchar2) then
364 p_rec.attribute5 :=
365 per_prt_shd.g_old_rec.attribute5;
366 End If;
367 If (p_rec.attribute6 = hr_api.g_varchar2) then
368 p_rec.attribute6 :=
369 per_prt_shd.g_old_rec.attribute6;
370 End If;
371 If (p_rec.attribute7 = hr_api.g_varchar2) then
372 p_rec.attribute7 :=
373 per_prt_shd.g_old_rec.attribute7;
374 End If;
375 If (p_rec.attribute8 = hr_api.g_varchar2) then
376 p_rec.attribute8 :=
377 per_prt_shd.g_old_rec.attribute8;
378 End If;
379 If (p_rec.attribute9 = hr_api.g_varchar2) then
380 p_rec.attribute9 :=
381 per_prt_shd.g_old_rec.attribute9;
382 End If;
383 If (p_rec.attribute10 = hr_api.g_varchar2) then
384 p_rec.attribute10 :=
385 per_prt_shd.g_old_rec.attribute10;
386 End If;
387 If (p_rec.attribute11 = hr_api.g_varchar2) then
388 p_rec.attribute11 :=
389 per_prt_shd.g_old_rec.attribute11;
390 End If;
391 If (p_rec.attribute12 = hr_api.g_varchar2) then
392 p_rec.attribute12 :=
393 per_prt_shd.g_old_rec.attribute12;
394 End If;
395 If (p_rec.attribute13 = hr_api.g_varchar2) then
396 p_rec.attribute13 :=
397 per_prt_shd.g_old_rec.attribute13;
398 End If;
399 If (p_rec.attribute14 = hr_api.g_varchar2) then
400 p_rec.attribute14 :=
401 per_prt_shd.g_old_rec.attribute14;
402 End If;
403 If (p_rec.attribute15 = hr_api.g_varchar2) then
404 p_rec.attribute15 :=
405 per_prt_shd.g_old_rec.attribute15;
406 End If;
407 If (p_rec.attribute16 = hr_api.g_varchar2) then
408 p_rec.attribute16 :=
409 per_prt_shd.g_old_rec.attribute16;
410 End If;
411 If (p_rec.attribute17 = hr_api.g_varchar2) then
412 p_rec.attribute17 :=
413 per_prt_shd.g_old_rec.attribute17;
414 End If;
415 If (p_rec.attribute18 = hr_api.g_varchar2) then
416 p_rec.attribute18 :=
417 per_prt_shd.g_old_rec.attribute18;
418 End If;
419 If (p_rec.attribute19 = hr_api.g_varchar2) then
420 p_rec.attribute19 :=
421 per_prt_shd.g_old_rec.attribute19;
422 End If;
423 If (p_rec.attribute20 = hr_api.g_varchar2) then
424 p_rec.attribute20 :=
425 per_prt_shd.g_old_rec.attribute20;
426 End If;
427 If (p_rec.person_id = hr_api.g_number) then
428 p_rec.person_id :=
429 per_prt_shd.g_old_rec.person_id;
430 End If;
431 If (p_rec.appr_line_score = hr_api.g_number) then
432 p_rec.appr_line_score :=
433 per_prt_shd.g_old_rec.appr_line_score;
434 End If;
435
436 hr_utility.set_location(' Leaving:'||l_proc, 10);
437
438 End convert_defs;
439
440 -- ---------------------------------------------------------------------------+
441 -- |---------------------------------< upd >----------------------------------|
442 -- ---------------------------------------------------------------------------+
443 Procedure upd
444 (
445 p_rec in out nocopy per_prt_shd.g_rec_type,
446 p_validate in boolean default false,
447 p_effective_date in date
448 ) is
449
450 l_proc varchar2(72) := g_package||'upd';
451
452 Begin
453 hr_utility.set_location('Entering:'||l_proc, 5);
454
455 -- Determine if the business process is to be validated.
456
457 If p_validate then
458
459 -- Issue the savepoint.
460
461 SAVEPOINT upd_per_prt;
462 End If;
463
464 -- We must lock the row which we need to update.
465
466 per_prt_shd.lck
467 (
468 p_rec.performance_rating_id,
469 p_rec.object_version_number
470 );
471
472 -- 1. During an update system defaults are used to determine if
473 -- arguments have been defaulted or not. We must therefore
474 -- derive the full record structure values to be updated.
475
476 -- 2. Call the supporting update validate operations.
477
478 convert_defs(p_rec);
479 per_prt_bus.update_validate(p_rec, p_effective_date);
480
481 --
482 hr_multi_message.end_validation_set;
483 --
484
485 -- Call the supporting pre-update operation
486
487 pre_update(p_rec);
488
489 -- Update the row.
490
491 update_dml(p_rec);
492
493 -- Call the supporting post-update operation
494
495 post_update(p_rec);
496
497 --
498 hr_multi_message.end_validation_set;
499 --
500
501 --
502 hr_multi_message.end_validation_set;
503 --
504
505 -- If we are validating then raise the Validate_Enabled exception
506
507 If p_validate then
508 Raise HR_Api.Validate_Enabled;
509 End If;
510
511 hr_utility.set_location(' Leaving:'||l_proc, 10);
512 Exception
513 When HR_Api.Validate_Enabled Then
514
515 -- As the Validate_Enabled exception has been raised
516 -- we must rollback to the savepoint
517
518 ROLLBACK TO upd_per_prt;
519 End upd;
520
521 -- ---------------------------------------------------------------------------+
522 -- |---------------------------------< upd >----------------------------------|
523 -- ---------------------------------------------------------------------------+
524 Procedure upd
525 (
526 p_performance_rating_id in number,
527 p_person_id in number,
528 p_object_version_number in out nocopy number,
529 p_performance_level_id in number default hr_api.g_number,
530 p_comments in varchar2 default hr_api.g_varchar2,
531 p_attribute_category in varchar2 default hr_api.g_varchar2,
532 p_attribute1 in varchar2 default hr_api.g_varchar2,
533 p_attribute2 in varchar2 default hr_api.g_varchar2,
534 p_attribute3 in varchar2 default hr_api.g_varchar2,
535 p_attribute4 in varchar2 default hr_api.g_varchar2,
536 p_attribute5 in varchar2 default hr_api.g_varchar2,
537 p_attribute6 in varchar2 default hr_api.g_varchar2,
538 p_attribute7 in varchar2 default hr_api.g_varchar2,
539 p_attribute8 in varchar2 default hr_api.g_varchar2,
540 p_attribute9 in varchar2 default hr_api.g_varchar2,
541 p_attribute10 in varchar2 default hr_api.g_varchar2,
542 p_attribute11 in varchar2 default hr_api.g_varchar2,
543 p_attribute12 in varchar2 default hr_api.g_varchar2,
544 p_attribute13 in varchar2 default hr_api.g_varchar2,
545 p_attribute14 in varchar2 default hr_api.g_varchar2,
546 p_attribute15 in varchar2 default hr_api.g_varchar2,
547 p_attribute16 in varchar2 default hr_api.g_varchar2,
548 p_attribute17 in varchar2 default hr_api.g_varchar2,
549 p_attribute18 in varchar2 default hr_api.g_varchar2,
550 p_attribute19 in varchar2 default hr_api.g_varchar2,
551 p_attribute20 in varchar2 default hr_api.g_varchar2,
552 p_validate in boolean default false,
553 p_effective_date in date,
554 p_appr_line_score in number default hr_api.g_number
555 ) is
556
557 l_rec per_prt_shd.g_rec_type;
558 l_proc varchar2(72) := g_package||'upd';
559
560 Begin
561 hr_utility.set_location('Entering:'||l_proc, 5);
562
563 -- Call conversion function to turn arguments into the
564 -- l_rec structure.
565
566 l_rec :=
567 per_prt_shd.convert_args
568 (
569 p_performance_rating_id,
570 p_person_id,
571 hr_api.g_number,
572 p_object_version_number,
573 hr_api.g_number,
574 p_performance_level_id,
575 p_comments,
576 p_attribute_category,
577 p_attribute1,
578 p_attribute2,
579 p_attribute3,
580 p_attribute4,
581 p_attribute5,
582 p_attribute6,
583 p_attribute7,
584 p_attribute8,
585 p_attribute9,
586 p_attribute10,
587 p_attribute11,
588 p_attribute12,
589 p_attribute13,
590 p_attribute14,
591 p_attribute15,
592 p_attribute16,
593 p_attribute17,
594 p_attribute18,
595 p_attribute19,
596 p_attribute20,
597 p_appr_line_score
598 );
599
600 -- Having converted the arguments into the
601 -- plsql record structure we call the corresponding record
602 -- business process.
603
604 upd(l_rec, p_validate, p_effective_date);
605 p_object_version_number := l_rec.object_version_number;
606
607 hr_utility.set_location(' Leaving:'||l_proc, 10);
608 End upd;
609
610 end per_prt_upd;