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