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