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