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