1 Package Body pay_itr_upd as
2 /* $Header: pyitrrhi.pkb 115.6 2002/12/16 17:48:51 dsaxby noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_itr_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 this
21 -- 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_itr_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_iterative_rules_f'
79 ,p_base_key_column => 'iterative_rule_id'
80 ,p_base_key_value => p_rec.iterative_rule_id
81 );
82 --
83 --
84 --
85 -- Update the pay_iterative_rules_f Row
86 --
87 update pay_iterative_rules_f
88 set
89 iterative_rule_id = p_rec.iterative_rule_id
90 ,element_type_id = p_rec.element_type_id
91 ,result_name = p_rec.result_name
92 ,iterative_rule_type = p_rec.iterative_rule_type
93 ,input_value_id = p_rec.input_value_id
94 ,severity_level = p_rec.severity_level
95 ,business_group_id = p_rec.business_group_id
96 ,legislation_code = p_rec.legislation_code
97 ,object_version_number = p_rec.object_version_number
98 where iterative_rule_id = p_rec.iterative_rule_id
99 and effective_start_date = p_validation_start_date
100 and effective_end_date = p_validation_end_date;
101 --
102 --
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 --
115 pay_itr_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 pay_itr_shd.constraint_error
121 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
122 When Others Then
123 --
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_itr_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_itr_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_itr_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_itr_shd.upd_effective_end_date
243 (p_effective_date => p_effective_date
244 ,p_base_key_value => p_rec.iterative_rule_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_itr_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_itr_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 --
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 pay_itr_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 the
344 -- 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 pay_itr_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 pay_itr_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_iterative_rule_id
393 => p_rec.iterative_rule_id
394 ,p_element_type_id
395 => p_rec.element_type_id
396 ,p_effective_start_date
397 => p_rec.effective_start_date
398 ,p_effective_end_date
399 => p_rec.effective_end_date
400 ,p_result_name
401 => p_rec.result_name
402 ,p_iterative_rule_type
403 => p_rec.iterative_rule_type
404 ,p_input_value_id
405 => p_rec.input_value_id
406 ,p_severity_level
407 => p_rec.severity_level
408 ,p_business_group_id
409 => p_rec.business_group_id
410 ,p_legislation_code
411 => p_rec.legislation_code
412 ,p_object_version_number
413 => p_rec.object_version_number
414 ,p_element_type_id_o
415 => pay_itr_shd.g_old_rec.element_type_id
416 ,p_effective_start_date_o
417 => pay_itr_shd.g_old_rec.effective_start_date
418 ,p_effective_end_date_o
419 => pay_itr_shd.g_old_rec.effective_end_date
420 ,p_result_name_o
421 => pay_itr_shd.g_old_rec.result_name
422 ,p_iterative_rule_type_o
423 => pay_itr_shd.g_old_rec.iterative_rule_type
424 ,p_input_value_id_o
425 => pay_itr_shd.g_old_rec.input_value_id
426 ,p_severity_level_o
427 => pay_itr_shd.g_old_rec.severity_level
428 ,p_business_group_id_o
429 => pay_itr_shd.g_old_rec.business_group_id
430 ,p_legislation_code_o
431 => pay_itr_shd.g_old_rec.legislation_code
432 ,p_object_version_number_o
433 => pay_itr_shd.g_old_rec.object_version_number
434 );
435 --
436 exception
437 --
438 when hr_api.cannot_find_prog_unit then
439 --
440 hr_api.cannot_find_prog_unit_error
441 (p_module_name => 'PAY_ITERATIVE_RULES_F'
442 ,p_hook_type => 'AU');
443 --
444 end;
445 --
446 hr_utility.set_location(' Leaving:'||l_proc, 10);
447 End post_update;
448 --
449 -- ----------------------------------------------------------------------------
450 -- |-----------------------------< convert_defs >-----------------------------|
451 -- ----------------------------------------------------------------------------
452 -- {Start Of Comments}
453 --
454 -- Description:
455 -- The Convert_Defs procedure has one very important function:
456 -- It must return the record structure for the row with all system defaulted
457 -- values converted into its corresponding parameter value for update. When
458 -- we attempt to update a row through the Upd process , certain
459 -- parameters can be defaulted which enables flexibility in the calling of
460 -- the upd process (e.g. only attributes which need to be updated need to be
461 -- specified). For the upd process to determine which attributes
462 -- have NOT been specified we need to check if the parameter has a reserved
463 -- system default value. Therefore, for all parameters which have a
464 -- corresponding reserved system default mechanism specified we need to
465 -- check if a system default is being used. If a system default is being
466 -- used then we convert the defaulted value into its corresponding attribute
467 -- value held in the g_old_rec data structure.
468 --
469 -- Prerequisites:
470 -- This private function can only be called from the upd process.
471 --
472 -- In Parameters:
473 -- A Pl/Sql record structure.
474 --
475 -- Post Success:
476 -- The record structure will be returned with all system defaulted parameter
477 -- values converted into its current row attribute value.
478 --
479 -- Post Failure:
480 -- No direct error handling is required within this function. Any possible
481 -- errors within this procedure will be a PL/SQL value error due to
482 -- conversion of datatypes or data lengths.
483 --
484 -- Developer Implementation Notes:
485 -- None.
486 --
487 -- Access Status:
488 -- Internal Row Handler Use Only.
489 --
490 -- {End Of Comments}
491 -- ----------------------------------------------------------------------------
492 Procedure convert_defs
493 (p_rec in out nocopy pay_itr_shd.g_rec_type
494 ) is
495 --
496 Begin
497 --
498 -- We must now examine each argument value in the
499 -- p_rec plsql record structure
500 -- to see if a system default is being used. If a system default
501 -- is being used then we must set to the 'current' argument value.
502 --
503 If (p_rec.element_type_id = hr_api.g_number) then
504 p_rec.element_type_id :=
505 pay_itr_shd.g_old_rec.element_type_id;
506 End If;
507 If (p_rec.result_name = hr_api.g_varchar2) then
508 p_rec.result_name :=
509 pay_itr_shd.g_old_rec.result_name;
510 End If;
511 If (p_rec.iterative_rule_type = hr_api.g_varchar2) then
512 p_rec.iterative_rule_type :=
513 pay_itr_shd.g_old_rec.iterative_rule_type;
514 End If;
515 If (p_rec.input_value_id = hr_api.g_number) then
516 p_rec.input_value_id :=
517 pay_itr_shd.g_old_rec.input_value_id;
518 End If;
519 If (p_rec.severity_level = hr_api.g_varchar2) then
520 p_rec.severity_level :=
521 pay_itr_shd.g_old_rec.severity_level;
522 End If;
523 If (p_rec.business_group_id = hr_api.g_number) then
524 p_rec.business_group_id :=
525 pay_itr_shd.g_old_rec.business_group_id;
526 End If;
527 If (p_rec.legislation_code = hr_api.g_varchar2) then
528 p_rec.legislation_code :=
529 pay_itr_shd.g_old_rec.legislation_code;
530 End If;
531 --
532 End convert_defs;
533 --
534 -- ----------------------------------------------------------------------------
535 -- |---------------------------------< upd >----------------------------------|
536 -- ----------------------------------------------------------------------------
537 Procedure upd
538 (p_effective_date in date
539 ,p_datetrack_mode in varchar2
540 ,p_rec in out nocopy pay_itr_shd.g_rec_type
541 ) is
542 --
543 l_proc varchar2(72) := g_package||'upd';
544 l_validation_start_date date;
545 l_validation_end_date date;
546 --
547 Begin
548 hr_utility.set_location('Entering:'||l_proc, 5);
549 --
550 -- Ensure that the DateTrack update mode is valid
551 --
552 dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode);
553 --
554 -- We must lock the row which we need to update.
555 --
556 pay_itr_shd.lck
557 (p_effective_date => p_effective_date
558 ,p_datetrack_mode => p_datetrack_mode
559 ,p_iterative_rule_id => p_rec.iterative_rule_id
560 ,p_object_version_number => p_rec.object_version_number
561 ,p_validation_start_date => l_validation_start_date
562 ,p_validation_end_date => l_validation_end_date
563 );
564 --
565 -- 1. During an update system defaults are used to determine if
566 -- arguments have been defaulted or not. We must therefore
567 -- derive the full record structure values to be updated.
568 --
569 -- 2. Call the supporting update validate operations.
570 --
571 pay_itr_upd.convert_defs(p_rec);
572 --
573 pay_itr_bus.update_validate
574 (p_rec => p_rec
575 ,p_effective_date => p_effective_date
576 ,p_datetrack_mode => p_datetrack_mode
577 ,p_validation_start_date => l_validation_start_date
578 ,p_validation_end_date => l_validation_end_date
579 );
580 --
581 -- Call the supporting pre-update operation
582 --
583 pre_update
584 (p_rec => p_rec
585 ,p_effective_date => p_effective_date
586 ,p_datetrack_mode => p_datetrack_mode
587 ,p_validation_start_date => l_validation_start_date
588 ,p_validation_end_date => l_validation_end_date
589 );
590 --
591 -- Update the row.
592 --
593 update_dml
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 -- Call the supporting post-update operation
602 --
603 post_update
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 End upd;
611 --
612 -- ----------------------------------------------------------------------------
613 -- |------------------------------< upd >-------------------------------------|
614 -- ----------------------------------------------------------------------------
615 Procedure upd
616 (p_effective_date in date
617 ,p_datetrack_mode in varchar2
618 ,p_iterative_rule_id in number
619 ,p_object_version_number in out nocopy number
620 ,p_element_type_id in number default hr_api.g_number
621 ,p_result_name in varchar2 default hr_api.g_varchar2
622 ,p_iterative_rule_type in varchar2 default hr_api.g_varchar2
623 ,p_input_value_id in number default hr_api.g_number
624 ,p_severity_level in varchar2 default hr_api.g_varchar2
625 ,p_business_group_id in number default hr_api.g_number
626 ,p_legislation_code in varchar2 default hr_api.g_varchar2
627 ,p_effective_start_date out nocopy date
628 ,p_effective_end_date out nocopy date
629 ) is
630 --
631 l_rec pay_itr_shd.g_rec_type;
632 l_proc varchar2(72) := g_package||'upd';
633 --
634 Begin
635 hr_utility.set_location('Entering:'||l_proc, 5);
636 --
637 -- Call conversion function to turn arguments into the
638 -- l_rec structure.
639 --
640 l_rec :=
641 pay_itr_shd.convert_args
642 (p_iterative_rule_id
643 ,p_element_type_id
644 ,null
645 ,null
646 ,p_result_name
647 ,p_iterative_rule_type
648 ,p_input_value_id
649 ,p_severity_level
650 ,p_business_group_id
651 ,p_legislation_code
652 ,p_object_version_number
653 );
654 --
655 -- Having converted the arguments into the
656 -- plsql record structure we call the corresponding record
657 -- business process.
658 --
659 pay_itr_upd.upd
660 (p_effective_date
661 ,p_datetrack_mode
662 ,l_rec
663 );
664 --
665 -- Set the out parameters
666 --
667 p_object_version_number := l_rec.object_version_number;
668 p_effective_start_date := l_rec.effective_start_date;
669 p_effective_end_date := l_rec.effective_end_date;
670 --
671 --
672 hr_utility.set_location(' Leaving:'||l_proc, 10);
673 End upd;
674 --
675 end pay_itr_upd;
[Home] [Help]