1 Package Body pay_rom_upd as
2 /* $Header: pyromrhi.pkb 115.3 2002/12/09 15:04:01 divicker noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_rom_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:
41 -- On the update dml failure it is important to note that we always reset the
38 -- The specified row will be updated in the schema.
39 --
40 -- Post Failure:
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_rom_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_run_type_org_methods_f'
79 ,p_base_key_column => 'run_type_org_method_id'
80 ,p_base_key_value => p_rec.run_type_org_method_id
81 );
82 --
83 --
84 --
85 -- Update the pay_run_type_org_methods_f Row
86 --
87 update pay_run_type_org_methods_f
88 set
89 run_type_id = p_rec.run_type_id
90 ,org_payment_method_id = p_rec.org_payment_method_id
91 ,priority = p_rec.priority
92 ,percentage = p_rec.percentage
93 ,amount = p_rec.amount
94 ,object_version_number = p_rec.object_version_number
95 ,run_type_org_method_id = p_rec.run_type_org_method_id
96 ,business_group_id = p_rec.business_group_id
97 ,legislation_code = p_rec.legislation_code
98 where run_type_org_method_id = p_rec.run_type_org_method_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_rom_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_rom_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_rom_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_rom_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 >-----------------------------|
187 -- Description:
184 -- ----------------------------------------------------------------------------
185 -- {Start Of Comments}
186 --
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_rom_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_rom_shd.upd_effective_end_date
243 (p_effective_date => p_effective_date
244 ,p_base_key_value => p_rec.run_type_org_method_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_rom_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_rom_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_rom_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 --
327 (p_rec => p_rec
324 --
325 --
326 dt_pre_update
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 --
340 -- {Start Of Comments}
337 -- ----------------------------------------------------------------------------
338 -- |----------------------------< post_update >-------------------------------|
339 -- ----------------------------------------------------------------------------
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_rom_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_rom_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_effective_start_date
393 => p_rec.effective_start_date
394 ,p_effective_end_date
395 => p_rec.effective_end_date
396 ,p_priority
397 => p_rec.priority
398 ,p_percentage
399 => p_rec.percentage
400 ,p_amount
401 => p_rec.amount
402 ,p_object_version_number
403 => p_rec.object_version_number
404 ,p_run_type_org_method_id
405 => p_rec.run_type_org_method_id
406 ,p_run_type_id_o
407 => pay_rom_shd.g_old_rec.run_type_id
408 ,p_org_payment_method_id_o
409 => pay_rom_shd.g_old_rec.org_payment_method_id
410 ,p_effective_start_date_o
411 => pay_rom_shd.g_old_rec.effective_start_date
412 ,p_effective_end_date_o
413 => pay_rom_shd.g_old_rec.effective_end_date
414 ,p_priority_o
415 => pay_rom_shd.g_old_rec.priority
416 ,p_percentage_o
417 => pay_rom_shd.g_old_rec.percentage
418 ,p_amount_o
419 => pay_rom_shd.g_old_rec.amount
420 ,p_object_version_number_o
421 => pay_rom_shd.g_old_rec.object_version_number
422 ,p_business_group_id_o
423 => pay_rom_shd.g_old_rec.business_group_id
424 ,p_legislation_code_o
425 => pay_rom_shd.g_old_rec.legislation_code
426 );
427 --
428 exception
429 --
430 when hr_api.cannot_find_prog_unit then
431 --
432 hr_api.cannot_find_prog_unit_error
433 (p_module_name => 'PAY_RUN_TYPE_ORG_METHODS_F'
434 ,p_hook_type => 'AU');
435 --
436 end;
437 --
438 hr_utility.set_location(' Leaving:'||l_proc, 10);
439 End post_update;
440 --
441 -- ----------------------------------------------------------------------------
442 -- |-----------------------------< convert_defs >-----------------------------|
443 -- ----------------------------------------------------------------------------
444 -- {Start Of Comments}
445 --
446 -- Description:
447 -- The Convert_Defs procedure has one very important function:
448 -- It must return the record structure for the row with all system defaulted
449 -- values converted into its corresponding parameter value for update. When
453 -- specified). For the upd process to determine which attributes
450 -- we attempt to update a row through the Upd process , certain
451 -- parameters can be defaulted which enables flexibility in the calling of
452 -- the upd process (e.g. only attributes which need to be updated need to be
454 -- have NOT been specified we need to check if the parameter has a reserved
455 -- system default value. Therefore, for all parameters which have a
456 -- corresponding reserved system default mechanism specified we need to
457 -- check if a system default is being used. If a system default is being
458 -- used then we convert the defaulted value into its corresponding attribute
459 -- value held in the g_old_rec data structure.
460 --
461 -- Prerequisites:
462 -- This private function can only be called from the upd process.
463 --
464 -- In Parameters:
465 -- A Pl/Sql record structure.
466 --
467 -- Post Success:
468 -- The record structure will be returned with all system defaulted parameter
469 -- values converted into its current row attribute value.
470 --
471 -- Post Failure:
472 -- No direct error handling is required within this function. Any possible
473 -- errors within this procedure will be a PL/SQL value error due to
474 -- conversion of datatypes or data lengths.
475 --
476 -- Developer Implementation Notes:
477 -- None.
478 --
479 -- Access Status:
480 -- Internal Row Handler Use Only.
481 --
482 -- {End Of Comments}
483 -- ----------------------------------------------------------------------------
484 Procedure convert_defs
485 (p_rec in out nocopy pay_rom_shd.g_rec_type
486 ) is
487 --
488 Begin
489 --
490 -- We must now examine each argument value in the
491 -- p_rec plsql record structure
492 -- to see if a system default is being used. If a system default
493 -- is being used then we must set to the 'current' argument value.
494 --
495 If (p_rec.run_type_id = hr_api.g_number) then
496 p_rec.run_type_id :=
497 pay_rom_shd.g_old_rec.run_type_id;
498 End If;
499 If (p_rec.org_payment_method_id = hr_api.g_number) then
500 p_rec.org_payment_method_id :=
501 pay_rom_shd.g_old_rec.org_payment_method_id;
502 End If;
503 If (p_rec.priority = hr_api.g_number) then
504 p_rec.priority :=
508 p_rec.percentage :=
505 pay_rom_shd.g_old_rec.priority;
506 End If;
507 If (p_rec.percentage = hr_api.g_number) then
509 pay_rom_shd.g_old_rec.percentage;
510 End If;
511 If (p_rec.amount = hr_api.g_number) then
512 p_rec.amount :=
513 pay_rom_shd.g_old_rec.amount;
514 End If;
515 If (p_rec.business_group_id = hr_api.g_number) then
516 p_rec.business_group_id :=
517 pay_rom_shd.g_old_rec.business_group_id;
518 End If;
519 If (p_rec.legislation_code = hr_api.g_varchar2) then
520 p_rec.legislation_code :=
521 pay_rom_shd.g_old_rec.legislation_code;
522 End If;
523 --
524 End convert_defs;
525 --
526 -- ----------------------------------------------------------------------------
527 -- |---------------------------------< upd >----------------------------------|
528 -- ----------------------------------------------------------------------------
529 Procedure upd
530 (p_effective_date in date
531 ,p_datetrack_mode in varchar2
532 ,p_rec in out nocopy pay_rom_shd.g_rec_type
533 ) is
534 --
535 l_proc varchar2(72) := g_package||'upd';
536 l_validation_start_date date;
537 l_validation_end_date date;
538 --
539 Begin
543 --
540 hr_utility.set_location('Entering:'||l_proc, 5);
541 --
542 -- Ensure that the DateTrack update mode is valid
544 dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode);
545 --
546 -- We must lock the row which we need to update.
547 --
548 pay_rom_shd.lck
549 (p_effective_date => p_effective_date
550 ,p_datetrack_mode => p_datetrack_mode
551 ,p_run_type_org_method_id => p_rec.run_type_org_method_id
552 ,p_object_version_number => p_rec.object_version_number
553 ,p_validation_start_date => l_validation_start_date
554 ,p_validation_end_date => l_validation_end_date
555 );
556 --
557 -- 1. During an update system defaults are used to determine if
558 -- arguments have been defaulted or not. We must therefore
559 -- derive the full record structure values to be updated.
560 --
561 -- 2. Call the supporting update validate operations.
562 --
563 pay_rom_upd.convert_defs(p_rec);
564 --
565 pay_rom_bus.update_validate
566 (p_rec => p_rec
567 ,p_effective_date => p_effective_date
568 ,p_datetrack_mode => p_datetrack_mode
569 ,p_validation_start_date => l_validation_start_date
570 ,p_validation_end_date => l_validation_end_date
571 );
572 --
573 -- Call the supporting pre-update operation
574 --
575 pre_update
576 (p_rec => p_rec
577 ,p_effective_date => p_effective_date
578 ,p_datetrack_mode => p_datetrack_mode
579 ,p_validation_start_date => l_validation_start_date
580 ,p_validation_end_date => l_validation_end_date
581 );
582 --
583 -- Update the row.
584 --
585 update_dml
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 the supporting post-update operation
594 --
595 post_update
596 (p_rec => p_rec
597 ,p_effective_date => p_effective_date
598 ,p_datetrack_mode => p_datetrack_mode
599 ,p_validation_start_date => l_validation_start_date
600 ,p_validation_end_date => l_validation_end_date
601 );
602 End upd;
603 --
604 -- ----------------------------------------------------------------------------
605 -- |------------------------------< upd >-------------------------------------|
606 -- ----------------------------------------------------------------------------
607 Procedure upd
608 (p_effective_date in date
609 ,p_datetrack_mode in varchar2
610 ,p_run_type_org_method_id in number
611 ,p_object_version_number in out nocopy number
612 ,p_priority in number default hr_api.g_number
613 ,p_percentage in number default hr_api.g_number
614 ,p_amount in number default hr_api.g_number
615 ,p_effective_start_date out nocopy date
616 ,p_effective_end_date out nocopy date
617 ) is
618 --
619 l_rec pay_rom_shd.g_rec_type;
620 l_proc varchar2(72) := g_package||'upd';
621 --
622 Begin
623 hr_utility.set_location('Entering:'||l_proc, 5);
624 --
625 -- Call conversion function to turn arguments into the
626 -- l_rec structure.
627 --
628 l_rec :=
629 pay_rom_shd.convert_args
630 (hr_api.g_number
631 ,hr_api.g_number
632 ,null
633 ,null
634 ,p_priority
635 ,p_percentage
636 ,p_amount
637 ,p_object_version_number
638 ,p_run_type_org_method_id
639 ,hr_api.g_number
640 ,hr_api.g_varchar2
641 );
642 --
643 -- Having converted the arguments into the
644 -- plsql record structure we call the corresponding record
645 -- business process.
646 --
647 pay_rom_upd.upd
648 (p_effective_date
649 ,p_datetrack_mode
650 ,l_rec
651 );
652 --
653 -- Set the out parameters
654 --
655 p_object_version_number := l_rec.object_version_number;
656 p_effective_start_date := l_rec.effective_start_date;
657 p_effective_end_date := l_rec.effective_end_date;
658 --
659 --
660 hr_utility.set_location(' Leaving:'||l_proc, 10);
661 End upd;
662 --
663 end pay_rom_upd;