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