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