1 Package Body ame_rlu_upd as
2 /* $Header: amrlurhi.pkb 120.5 2005/11/22 03:19 santosin noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ame_rlu_upd.'; -- Global package name
9 g_old_start_date date;
10 --
11 -- ----------------------------------------------------------------------------
12 -- |----------------------------< dt_update_dml >-----------------------------|
13 -- ----------------------------------------------------------------------------
14 -- {Start Of Comments}
15 --
16 -- Description:
17 -- This procedure controls the execution of dml from the datetrack mode
18 -- of CORRECTION only. It is important to note that the object version
19 -- number is only increment by 1 because the datetrack correction is
20 -- soley for one datetracked row.
21 -- This procedure controls the actual dml update logic. The functions of
22 -- this procedure are as follows:
23 -- 1) Get the next object_version_number.
24 -- 2) To set and unset the g_api_dml status as required (as we are about to
25 -- perform dml).
26 -- 3) To update the specified row in the schema using the primary key in
27 -- the predicates.
28 -- 4) To trap any constraint violations that may have occurred.
29 -- 5) To raise any other errors.
30 --
31 -- Prerequisites:
32 -- This is an internal private procedure which must be called from the
33 -- update_dml procedure.
34 --
35 -- In Parameters:
36 -- A Pl/Sql record structure.
37 --
38 -- Post Success:
39 -- The specified row will be updated in the schema.
40 --
41 -- Post Failure:
42 -- On the update dml failure it is important to note that we always reset the
43 -- g_api_dml status to false.
44 -- If a check or unique integrity constraint violation is raised the
45 -- constraint_error procedure will be called.
46 -- If any other error is reported, the error will be raised after the
47 -- g_api_dml status is reset.
48 --
49 -- Developer Implementation Notes:
50 -- The update 'set' arguments list should be modified if any of your
51 -- attributes are not updateable.
52 --
53 -- Access Status:
54 -- Internal Row Handler Use Only.
55 --
56 -- {End Of Comments}
57 -- ----------------------------------------------------------------------------
58 Procedure dt_update_dml
59 (p_rec in out nocopy ame_rlu_shd.g_rec_type
60 ,p_effective_date in date
61 ,p_datetrack_mode in varchar2
62 ,p_validation_start_date in date
63 ,p_validation_end_date in date
64 ) is
65 --
66 l_proc varchar2(72) := g_package||'dt_update_dml';
67 --
68 Begin
69 hr_utility.set_location('Entering:'||l_proc, 5);
70 --
71 If (p_datetrack_mode = hr_api.g_correction) then
72 hr_utility.set_location(l_proc, 10);
73 --
74 -- Because we are updating a row we must get the next object
75 -- version number.
76 --
77 p_rec.object_version_number :=
78 ame_rlu_shd.get_object_version_number
79 (p_rule_id => p_rec.rule_id
80 ,p_item_id => p_rec.item_id
81 ,p_old_start_date => p_rec.old_start_date
82 );
83 --
84 --
85 --
86 -- Update the ame_rule_usages Row
87 --
88 update ame_rule_usages
89 set
90 item_id = p_rec.item_id
91 ,usage_type = p_rec.usage_type
92 ,rule_id = p_rec.rule_id
93 ,security_group_id = p_rec.security_group_id
94 ,priority = p_rec.priority
95 ,approver_category = p_rec.approver_category
96 ,object_version_number = p_rec.object_version_number
97 where rule_id = p_rec.rule_id
98 and item_id = p_rec.item_id
99 and start_date = p_validation_start_date
100 and ((p_validation_end_date is null and end_date is null) or
101 (end_date = p_validation_end_date));
102 --
103 --
104 --
105 -- Set the effective start and end dates
106 --
107 p_rec.start_date := p_validation_start_date;
108 p_rec.end_date := p_validation_end_date;
109 End If;
110 --
111 hr_utility.set_location(' Leaving:'||l_proc, 15);
112 Exception
113 When hr_api.check_integrity_violated Then
114 -- A check constraint has been violated
115 --
116 ame_rlu_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 ame_rlu_shd.constraint_error
122 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
123 When Others Then
124 --
125 Raise;
126 End dt_update_dml;
127 --
128 -- ----------------------------------------------------------------------------
129 -- |------------------------------< update_dml >------------------------------|
130 -- ----------------------------------------------------------------------------
131 -- {Start Of Comments}
132 --
133 -- Description:
134 -- This procedure calls the dt_update_dml control logic which handles
135 -- the actual datetrack dml.
136 --
137 -- Prerequisites:
138 -- This is an internal private procedure which must be called from the upd
139 -- procedure.
140 --
141 -- In Parameters:
142 -- A Pl/Sql record structre.
143 --
144 -- Post Success:
145 -- Processing contines.
146 --
147 -- Post Failure:
148 -- No specific error handling is required within this procedure.
149 --
150 -- Developer Implementation Notes:
151 -- The update 'set' arguments list should be modified if any of your
152 -- attributes are not updateable.
153 --
154 -- Access Status:
155 -- Internal Row Handler Use Only.
156 --
157 -- {End Of Comments}
158 -- ----------------------------------------------------------------------------
159 Procedure update_dml
160 (p_rec in out nocopy ame_rlu_shd.g_rec_type
161 ,p_effective_date in date
162 ,p_datetrack_mode in varchar2
163 ,p_validation_start_date in date
164 ,p_validation_end_date in date
165 ) is
166 --
167 l_proc varchar2(72) := g_package||'update_dml';
168 --
169 Begin
170 hr_utility.set_location('Entering:'||l_proc, 5);
171 --
172 ame_rlu_upd.dt_update_dml
173 (p_rec => p_rec
174 ,p_effective_date => p_effective_date
175 ,p_datetrack_mode => p_datetrack_mode
176 ,p_validation_start_date => p_validation_start_date
177 ,p_validation_end_date => p_validation_end_date
178 );
179 --
180 hr_utility.set_location(' Leaving:'||l_proc, 10);
181 End update_dml;
182 --
183 -- ----------------------------------------------------------------------------
184 -- |----------------------------< dt_pre_update >-----------------------------|
185 -- ----------------------------------------------------------------------------
186 -- {Start Of Comments}
187 --
188 -- Description:
189 -- The dt_pre_update procedure controls the execution
190 -- of dml for the datetrack modes of: UPDATE, UPDATE_OVERRIDE
191 -- and UPDATE_CHANGE_INSERT only. The execution required is as
192 -- follows:
193 --
194 -- 1) Providing the datetrack update mode is not 'CORRECTION'
195 -- then set the effective end date of the current row (this
196 -- will be the validation_start_date - 1).
197 -- 2) If the datetrack mode is 'UPDATE_OVERRIDE' then call the
198 -- corresponding delete_dml process to delete any future rows
199 -- where the effective_start_date is greater than or equal to
200 -- the validation_start_date.
201 -- 3) Call the insert_dml process to insert the new updated row
202 -- details.
203 --
204 -- Prerequisites:
205 -- This is an internal procedure which is called from the
206 -- pre_update procedure.
207 --
208 -- In Parameters:
209 --
210 -- Post Success:
211 -- Processing continues.
212 --
213 -- Post Failure:
214 -- If an error has occurred, an error message and exception will be raised
215 -- but not handled.
216 --
217 -- Developer Implementation Notes:
218 -- This is an internal procedure which is required by Datetrack. Don't
219 -- remove or modify.
220 --
221 -- Access Status:
222 -- Internal Row Handler Use Only.
223 --
224 -- {End Of Comments}
225 -- ----------------------------------------------------------------------------
226 Procedure dt_pre_update
227 (p_rec in out nocopy ame_rlu_shd.g_rec_type
228 ,p_effective_date in date
229 ,p_datetrack_mode in varchar2
230 ,p_validation_start_date in date
231 ,p_validation_end_date in date
232 ) is
233 --
234 l_proc varchar2(72) := g_package||'dt_pre_update';
235 l_dummy_version_number number;
236 --
237 Begin
238 hr_utility.set_location('Entering:'||l_proc, 5);
239 If (p_datetrack_mode <> hr_api.g_correction) then
240 --
241 -- Update the current effective end date
242 --
243 ame_rlu_shd.upd_end_date
244 (p_effective_date => p_effective_date
245 ,p_rule_id => p_rec.rule_id
246 ,p_item_id => p_rec.item_id
247 ,p_old_start_date => p_rec.old_start_date
248 ,p_new_end_date => p_validation_start_date
249 ,p_object_version_number => l_dummy_version_number
250 );
251 --
252 If (p_datetrack_mode = hr_api.g_update_override) then
253 --
254 -- As the datetrack mode is 'UPDATE_OVERRIDE' then we must
255 -- delete any future rows
256 --
257 ame_rlu_del.delete_dml
258 (p_rec => p_rec
259 ,p_effective_date => p_effective_date
260 ,p_datetrack_mode => p_datetrack_mode
261 ,p_validation_start_date => p_validation_start_date
262 ,p_validation_end_date => p_validation_end_date
263 );
264 End If;
265 --
266 -- We must now insert the updated row
267 --
268 ame_rlu_ins.insert_dml
269 (p_rec => p_rec
270 ,p_effective_date => p_effective_date
271 ,p_datetrack_mode => p_datetrack_mode
272 ,p_validation_start_date => p_validation_start_date
273 ,p_validation_end_date => p_validation_end_date
274 );
275 End If;
276 hr_utility.set_location(' Leaving:'||l_proc, 20);
277 End dt_pre_update;
278 --
279 -- ----------------------------------------------------------------------------
280 -- |------------------------------< pre_update >------------------------------|
281 -- ----------------------------------------------------------------------------
282 -- {Start Of Comments}
283 --
284 -- Description:
285 -- This private procedure contains any processing which is required before
286 -- the update dml.
287 --
288 -- Prerequisites:
289 -- This is an internal procedure which is called from the upd procedure.
290 --
291 -- In Parameters:
292 -- A Pl/Sql record structure.
293 --
294 -- Post Success:
295 -- Processing continues.
296 --
297 -- Post Failure:
298 -- If an error has occurred, an error message and exception will be raised
299 -- but not handled.
300 -- Developer Implementation Notes:
301 -- Any pre-processing required before the update dml is issued should be
302 -- coded within this procedure. It is important to note that any 3rd party
303 -- maintenance should be reviewed before placing in this procedure. The call
304 -- to the dt_update_dml procedure should NOT be removed.
305 --
306 -- Access Status:
307 -- Internal Row Handler Use Only.
308 --
309 -- {End Of Comments}
310 -- ----------------------------------------------------------------------------
311 Procedure pre_update
312 (p_rec in out nocopy ame_rlu_shd.g_rec_type
313 ,p_effective_date in date
314 ,p_datetrack_mode in varchar2
315 ,p_validation_start_date in date
316 ,p_validation_end_date in date
317 ) is
318 --
319 l_proc varchar2(72) := g_package||'pre_update';
320 --
321 Begin
322 hr_utility.set_location('Entering:'||l_proc, 5);
323 --
324 --
325 --
326 dt_pre_update
327 (p_rec => p_rec
328 ,p_effective_date => p_effective_date
329 ,p_datetrack_mode => p_datetrack_mode
330 ,p_validation_start_date => p_validation_start_date
331 ,p_validation_end_date => p_validation_end_date
332 );
333 --
334 hr_utility.set_location(' Leaving:'||l_proc, 10);
335 End pre_update;
336 --
337 -- ----------------------------------------------------------------------------
338 -- |----------------------------< post_update >-------------------------------|
339 -- ----------------------------------------------------------------------------
340 -- {Start Of Comments}
341 --
342 -- Description:
343 -- This private procedure contains any processing which is required after
344 -- the update dml.
345 --
346 -- Prerequisites:
347 -- This is an internal procedure which is called from the upd procedure.
348 --
349 -- In Parameters:
350 -- A Pl/Sql record structure.
351 --
352 -- Post Success:
353 -- Processing continues.
354 --
355 -- Post Failure:
356 -- If an error has occurred, an error message and exception will be raised
357 -- but not handled.
358 --
359 -- Developer Implementation Notes:
360 -- Any post-processing required after the update dml is issued should be
361 -- coded within this procedure. It is important to note that any 3rd party
362 -- maintenance should be reviewed before placing in this procedure.
363 --
364 -- Access Status:
365 -- Internal Row Handler Use Only.
366 --
367 -- {End Of Comments}
368 -- ----------------------------------------------------------------------------
369 Procedure post_update
370 (p_rec in ame_rlu_shd.g_rec_type
371 ,p_effective_date in date
372 ,p_datetrack_mode in varchar2
373 ,p_validation_start_date in date
374 ,p_validation_end_date in date
375 ) is
376 --
377 l_proc varchar2(72) := g_package||'post_update';
378 --
379 Begin
380 hr_utility.set_location('Entering:'||l_proc, 5);
381 begin
382 --
383 ame_rlu_rku.after_update
384 (p_effective_date
385 => p_effective_date
386 ,p_datetrack_mode
387 => p_datetrack_mode
388 ,p_validation_start_date
389 => p_validation_start_date
390 ,p_validation_end_date
391 => p_validation_end_date
392 ,p_item_id
393 => p_rec.item_id
394 ,p_usage_type
395 => p_rec.usage_type
396 ,p_rule_id
397 => p_rec.rule_id
398 ,p_start_date
399 => p_rec.start_date
400 ,p_end_date
401 => p_rec.end_date
402 ,p_security_group_id
403 => p_rec.security_group_id
404 ,p_priority
405 => p_rec.priority
406 ,p_approver_category
407 => p_rec.approver_category
408 ,p_object_version_number
409 => p_rec.object_version_number
410 ,p_usage_type_o
411 => ame_rlu_shd.g_old_rec.usage_type
412 ,p_start_date_o
413 => ame_rlu_shd.g_old_rec.start_date
414 ,p_end_date_o
415 => ame_rlu_shd.g_old_rec.end_date
416 ,p_security_group_id_o
417 => ame_rlu_shd.g_old_rec.security_group_id
418 ,p_priority_o
419 => ame_rlu_shd.g_old_rec.priority
420 ,p_approver_category_o
421 => ame_rlu_shd.g_old_rec.approver_category
422 ,p_object_version_number_o
423 => ame_rlu_shd.g_old_rec.object_version_number
424 );
425 --
426 exception
427 --
428 when hr_api.cannot_find_prog_unit then
429 --
430 hr_api.cannot_find_prog_unit_error
431 (p_module_name => 'AME_RULE_USAGES'
432 ,p_hook_type => 'AU');
433 --
434 end;
435 --
436 hr_utility.set_location(' Leaving:'||l_proc, 10);
437 End post_update;
438 --
439 -- ----------------------------------------------------------------------------
440 -- |-----------------------------< convert_defs >-----------------------------|
441 -- ----------------------------------------------------------------------------
442 -- {Start Of Comments}
443 --
444 -- Description:
445 -- The Convert_Defs procedure has one very important function:
446 -- It must return the record structure for the row with all system defaulted
447 -- values converted into its corresponding parameter value for update. When
448 -- we attempt to update a row through the Upd process , certain
449 -- parameters can be defaulted which enables flexibility in the calling of
450 -- the upd process (e.g. only attributes which need to be updated need to be
451 -- specified). For the upd process to determine which attributes
452 -- have NOT been specified we need to check if the parameter has a reserved
453 -- system default value. Therefore, for all parameters which have a
454 -- corresponding reserved system default mechanism specified we need to
455 -- check if a system default is being used. If a system default is being
456 -- used then we convert the defaulted value into its corresponding attribute
457 -- value held in the g_old_rec data structure.
458 --
459 -- Prerequisites:
460 -- This private function can only be called from the upd process.
461 --
462 -- In Parameters:
463 -- A Pl/Sql record structure.
464 --
465 -- Post Success:
466 -- The record structure will be returned with all system defaulted parameter
467 -- values converted into its current row attribute value.
468 --
469 -- Post Failure:
470 -- No direct error handling is required within this function. Any possible
471 -- errors within this procedure will be a PL/SQL value error due to
472 -- conversion of datatypes or data lengths.
473 --
474 -- Developer Implementation Notes:
475 -- None.
476 --
477 -- Access Status:
478 -- Internal Row Handler Use Only.
479 --
480 -- {End Of Comments}
481 -- ----------------------------------------------------------------------------
482 Procedure convert_defs
483 (p_rec in out nocopy ame_rlu_shd.g_rec_type
484 ) is
485 --
486 Begin
487 --
488 -- We must now examine each argument value in the
489 -- p_rec plsql record structure
490 -- to see if a system default is being used. If a system default
491 -- is being used then we must set to the 'current' argument value.
492 --
493 If (p_rec.usage_type = hr_api.g_varchar2) then
494 p_rec.usage_type :=
495 ame_rlu_shd.g_old_rec.usage_type;
496 End If;
497 If (p_rec.security_group_id = hr_api.g_number) then
498 p_rec.security_group_id :=
499 ame_rlu_shd.g_old_rec.security_group_id;
500 End If;
501 If (p_rec.priority = hr_api.g_number) then
502 p_rec.priority :=
503 ame_rlu_shd.g_old_rec.priority;
504 End If;
505 If (p_rec.approver_category = hr_api.g_varchar2) then
506 p_rec.approver_category :=
507 ame_rlu_shd.g_old_rec.approver_category;
508 End If;
509 If (p_rec.start_date = hr_api.g_date) then
510 p_rec.start_date := ame_rlu_shd.g_old_rec.start_date;
511 End If;
512 If (p_rec.end_date = hr_api.g_date) then
513 p_rec.end_date := ame_rlu_shd.g_old_rec.end_date;
514 End If;
515 --
516 End convert_defs;
517 --
518 -- ----------------------------------------------------------------------------
519 -- |---------------------------------< upd >----------------------------------|
520 -- ----------------------------------------------------------------------------
521 Procedure upd
522 (p_effective_date in date
523 ,p_datetrack_mode in varchar2
524 ,p_rec in out nocopy ame_rlu_shd.g_rec_type
525 ) is
526 --
527 l_proc varchar2(72) := g_package||'upd';
528 l_validation_start_date date;
529 l_validation_end_date date;
530 --
531 Begin
532 hr_utility.set_location('Entering:'||l_proc, 5);
533 --
534 -- Ensure that the DateTrack update mode is valid
535 --
536 dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode);
537 --
538 -- set the start and end dates if not set
539 --
540 if p_rec.start_date is null then
541 p_rec.start_date := hr_api.g_date;
542 end if;
543 if p_rec.end_date is null then
544 p_rec.end_date := hr_api.g_date;
545 end if;
546 --
547 -- We must lock the row which we need to update.
548 --
549 ame_rlu_shd.lck
550 (p_effective_date => p_effective_date
551 ,p_datetrack_mode => p_datetrack_mode
552 ,p_rule_id => p_rec.rule_id
553 ,p_item_id => p_rec.item_id
554 ,p_old_start_date => p_rec.old_start_date
555 ,p_object_version_number => p_rec.object_version_number
556 ,p_validation_start_date => l_validation_start_date
557 ,p_validation_end_date => l_validation_end_date
558 );
559 --
560 -- 1. During an update system defaults are used to determine if
561 -- arguments have been defaulted or not. We must therefore
562 -- derive the full record structure values to be updated.
563 --
564 -- 2. Call the supporting update validate operations.
565 --
566 ame_rlu_upd.convert_defs(p_rec);
567 --
568 -- Make sure that the Rule start_date and end_date are correct
569 --
570 -- p_validation_start_date is sysdate, so if the start_date is greater
571 -- than sysdate then we should use the p_start_date else use sysdate.
572 --
573 if p_rec.start_date < l_validation_start_date then
574 p_rec.start_date := l_validation_start_date;
575 end if;
576 --
577 -- p_validation_end_date is ame_util.endOfTime, so if the end_date is less
578 -- than ame_util.endOfTime then we should use the p_end_date else use
579 -- p_validation_end_date.
580 --
581 /*if p_rec.end_date <> l_validation_end_date then
582 p_rec.end_date := l_validation_end_date;
583 end if;*/
584 --
585 ame_rlu_bus.update_validate
586 (p_rec => p_rec
587 ,p_effective_date => p_effective_date
588 ,p_datetrack_mode => p_datetrack_mode
589 ,p_validation_start_date => l_validation_start_date
590 ,p_validation_end_date => l_validation_end_date
591 );
592 --
593 -- Call to raise any errors on multi-message list
594 hr_multi_message.end_validation_set;
595 --
596 -- Call the supporting pre-update operation
597 --
598 pre_update
599 (p_rec => p_rec
600 ,p_effective_date => p_effective_date
601 ,p_datetrack_mode => p_datetrack_mode
602 ,p_validation_start_date => l_validation_start_date
603 ,p_validation_end_date => l_validation_end_date
604 );
605 --
606 -- Update the row.
607 --
608 update_dml
609 (p_rec => p_rec
610 ,p_effective_date => p_effective_date
611 ,p_datetrack_mode => p_datetrack_mode
612 ,p_validation_start_date => l_validation_start_date
613 ,p_validation_end_date => l_validation_end_date
614 );
615 --
616 -- Call the supporting post-update operation
617 --
618 post_update
619 (p_rec => p_rec
620 ,p_effective_date => p_effective_date
621 ,p_datetrack_mode => p_datetrack_mode
622 ,p_validation_start_date => l_validation_start_date
623 ,p_validation_end_date => l_validation_end_date
624 );
625 --
626 -- Call to raise any errors on multi-message list
627 hr_multi_message.end_validation_set;
628 End upd;
629 --
630 -- ----------------------------------------------------------------------------
631 -- |------------------------------< upd >-------------------------------------|
632 -- ----------------------------------------------------------------------------
633 Procedure upd
634 (p_effective_date in date
635 ,p_datetrack_mode in varchar2
636 ,p_item_id in number
637 ,p_rule_id in number
638 ,p_old_start_date in date
639 ,p_object_version_number in out nocopy number
640 ,p_usage_type in varchar2 default hr_api.g_varchar2
641 ,p_security_group_id in number default hr_api.g_number
642 ,p_priority in number default hr_api.g_number
643 ,p_approver_category in varchar2 default hr_api.g_varchar2
644 ,p_start_date in out nocopy date
645 ,p_end_date in out nocopy date
646 ) is
647 --
648 l_rec ame_rlu_shd.g_rec_type;
649 l_proc varchar2(72) := g_package||'upd';
650 --
651 Begin
652 hr_utility.set_location('Entering:'||l_proc, 5);
653 --
654 -- Call conversion function to turn arguments into the
655 -- l_rec structure.
656 --
657 l_rec :=
658 ame_rlu_shd.convert_args
659 (p_item_id
660 ,p_usage_type
661 ,p_rule_id
662 ,p_start_date
663 ,p_end_date
664 ,p_security_group_id
665 ,p_priority
666 ,p_approver_category
667 ,p_object_version_number
668 ,p_old_start_date
669 );
670 --
671 -- Having converted the arguments into the
672 -- plsql record structure we call the corresponding record
673 -- business process.
674 --
675 ame_rlu_upd.upd
676 (p_effective_date
677 ,p_datetrack_mode
678 ,l_rec
679 );
680 --
681 -- Set the out parameters
682 --
683 p_object_version_number := l_rec.object_version_number;
684 p_start_date := l_rec.start_date;
685 p_end_date := l_rec.end_date;
686 --
687 --
688 hr_utility.set_location(' Leaving:'||l_proc, 10);
689 End upd;
690 --
691 end ame_rlu_upd;
[Home] [Help]