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