1 Package Body hr_cle_upd as
2 /* $Header: hrclerhi.pkb 115.6 2002/12/03 09:27:16 hjonnala noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_cle_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 hr_cle_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 => 'hr_de_soc_ins_contr_lvls_f'
79 ,p_base_key_column => 'soc_ins_contr_lvls_id'
80 ,p_base_key_value => p_rec.soc_ins_contr_lvls_id
81 );
82 --
83 --
84 --
85 -- Update the hr_de_soc_ins_contr_lvls_f Row
86 --
87 update hr_de_soc_ins_contr_lvls_f
88 set
89 soc_ins_contr_lvls_id = p_rec.soc_ins_contr_lvls_id
90 ,organization_id = p_rec.organization_id
91 ,normal_percentage = p_rec.normal_percentage
92 ,normal_amount = p_rec.normal_amount
93 ,increased_percentage = p_rec.increased_percentage
94 ,increased_amount = p_rec.increased_amount
95 ,reduced_percentage = p_rec.reduced_percentage
96 ,reduced_amount = p_rec.reduced_amount
97 ,attribute_category = p_rec.attribute_category
98 ,attribute1 = p_rec.attribute1
99 ,attribute2 = p_rec.attribute2
100 ,attribute3 = p_rec.attribute3
101 ,attribute4 = p_rec.attribute4
102 ,attribute5 = p_rec.attribute5
103 ,attribute6 = p_rec.attribute6
104 ,attribute7 = p_rec.attribute7
105 ,attribute8 = p_rec.attribute8
106 ,attribute9 = p_rec.attribute9
107 ,attribute10 = p_rec.attribute10
108 ,attribute11 = p_rec.attribute11
109 ,attribute12 = p_rec.attribute12
110 ,attribute13 = p_rec.attribute13
111 ,attribute14 = p_rec.attribute14
112 ,attribute15 = p_rec.attribute15
113 ,attribute16 = p_rec.attribute16
114 ,attribute17 = p_rec.attribute17
115 ,attribute18 = p_rec.attribute18
116 ,attribute19 = p_rec.attribute19
117 ,attribute20 = p_rec.attribute20
118 ,object_version_number = p_rec.object_version_number
119 ,attribute21 = p_rec.attribute21
120 ,attribute22 = p_rec.attribute22
121 ,attribute23 = p_rec.attribute23
122 ,attribute24 = p_rec.attribute24
123 ,attribute25 = p_rec.attribute25
124 ,attribute26 = p_rec.attribute26
125 ,attribute27 = p_rec.attribute27
126 ,attribute28 = p_rec.attribute28
127 ,attribute29 = p_rec.attribute29
128 ,attribute30 = p_rec.attribute30
129 ,flat_tax_limit_per_month = p_rec.flat_tax_limit_per_month
130 ,flat_tax_limit_per_year = p_rec.flat_tax_limit_per_year
131 ,min_increased_contribution = p_rec.min_increased_contribution
132 ,max_increased_contribution = p_rec.max_increased_contribution
133 ,month1 = p_rec.month1
134 ,month1_min_contribution = p_rec.month1_min_contribution
135 ,month1_max_contribution = p_rec.month1_max_contribution
136 ,month2 = p_rec.month2
137 ,month2_min_contribution = p_rec.month2_min_contribution
138 ,month2_max_contribution = p_rec.month2_max_contribution
139 ,employee_contribution = p_rec.employee_contribution
140 ,contribution_level_type = p_rec.contribution_level_type
141 where soc_ins_contr_lvls_id = p_rec.soc_ins_contr_lvls_id
142 and effective_start_date = p_validation_start_date
143 and effective_end_date = p_validation_end_date;
144 --
145 --
146 --
147 -- Set the effective start and end dates
148 --
149 p_rec.effective_start_date := p_validation_start_date;
150 p_rec.effective_end_date := p_validation_end_date;
151 End If;
152 --
153 hr_utility.set_location(' Leaving:'||l_proc, 15);
154 Exception
155 When hr_api.check_integrity_violated Then
156 -- A check constraint has been violated
157 --
158 hr_cle_shd.constraint_error
159 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
160 When hr_api.unique_integrity_violated Then
161 -- Unique integrity has been violated
162 --
163 hr_cle_shd.constraint_error
164 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
165 When Others Then
166 --
167 Raise;
168 End dt_update_dml;
169 --
170 -- ----------------------------------------------------------------------------
171 -- |------------------------------< update_dml >------------------------------|
172 -- ----------------------------------------------------------------------------
173 -- {Start Of Comments}
174 --
175 -- Description:
176 -- This procedure calls the dt_update_dml control logic which handles
177 -- the actual datetrack dml.
178 --
179 -- Prerequisites:
180 -- This is an internal private procedure which must be called from the upd
181 -- procedure.
182 --
183 -- In Parameters:
184 -- A Pl/Sql record structre.
185 --
186 -- Post Success:
187 -- Processing contines.
188 --
189 -- Post Failure:
190 -- No specific error handling is required within this procedure.
191 --
192 -- Developer Implementation Notes:
193 -- The update 'set' arguments list should be modified if any of your
194 -- attributes are not updateable.
195 --
196 -- Access Status:
197 -- Internal Row Handler Use Only.
198 --
199 -- {End Of Comments}
200 -- ----------------------------------------------------------------------------
201 Procedure update_dml
202 (p_rec in out nocopy hr_cle_shd.g_rec_type
203 ,p_effective_date in date
204 ,p_datetrack_mode in varchar2
205 ,p_validation_start_date in date
206 ,p_validation_end_date in date
207 ) is
208 --
209 l_proc varchar2(72) := g_package||'update_dml';
210 --
211 Begin
212 hr_utility.set_location('Entering:'||l_proc, 5);
213 --
214 hr_cle_upd.dt_update_dml
215 (p_rec => p_rec
216 ,p_effective_date => p_effective_date
217 ,p_datetrack_mode => p_datetrack_mode
218 ,p_validation_start_date => p_validation_start_date
219 ,p_validation_end_date => p_validation_end_date
220 );
221 --
222 hr_utility.set_location(' Leaving:'||l_proc, 10);
223 End update_dml;
224 --
225 -- ----------------------------------------------------------------------------
226 -- |----------------------------< dt_pre_update >-----------------------------|
227 -- ----------------------------------------------------------------------------
228 -- {Start Of Comments}
229 --
230 -- Description:
231 -- The dt_pre_update procedure controls the execution
232 -- of dml for the datetrack modes of: UPDATE, UPDATE_OVERRIDE
233 -- and UPDATE_CHANGE_INSERT only. The execution required is as
234 -- follows:
235 --
236 -- 1) Providing the datetrack update mode is not 'CORRECTION'
237 -- then set the effective end date of the current row (this
238 -- will be the validation_start_date - 1).
239 -- 2) If the datetrack mode is 'UPDATE_OVERRIDE' then call the
240 -- corresponding delete_dml process to delete any future rows
241 -- where the effective_start_date is greater than or equal to
242 -- the validation_start_date.
243 -- 3) Call the insert_dml process to insert the new updated row
244 -- details.
245 --
246 -- Prerequisites:
247 -- This is an internal procedure which is called from the
248 -- pre_update procedure.
249 --
250 -- In Parameters:
251 --
252 -- Post Success:
253 -- Processing continues.
254 --
255 -- Post Failure:
256 -- If an error has occurred, an error message and exception will be raised
257 -- but not handled.
258 --
259 -- Developer Implementation Notes:
260 -- This is an internal procedure which is required by Datetrack. Don't
261 -- remove or modify.
262 --
263 -- Access Status:
264 -- Internal Row Handler Use Only.
265 --
266 -- {End Of Comments}
267 -- ----------------------------------------------------------------------------
268 Procedure dt_pre_update
269 (p_rec in out nocopy hr_cle_shd.g_rec_type
270 ,p_effective_date in date
271 ,p_datetrack_mode in varchar2
272 ,p_validation_start_date in date
273 ,p_validation_end_date in date
274 ) is
275 --
276 l_proc varchar2(72) := g_package||'dt_pre_update';
277 l_dummy_version_number number;
278 --
279 Begin
280 hr_utility.set_location('Entering:'||l_proc, 5);
281 If (p_datetrack_mode <> hr_api.g_correction) then
282 --
283 -- Update the current effective end date
284 --
285 hr_cle_shd.upd_effective_end_date
286 (p_effective_date => p_effective_date
287 ,p_base_key_value => p_rec.soc_ins_contr_lvls_id
288 ,p_new_effective_end_date => (p_validation_start_date - 1)
289 ,p_validation_start_date => p_validation_start_date
290 ,p_validation_end_date => p_validation_end_date
291 ,p_object_version_number => l_dummy_version_number
292 );
293 --
294 If (p_datetrack_mode = hr_api.g_update_override) then
295 --
296 -- As the datetrack mode is 'UPDATE_OVERRIDE' then we must
297 -- delete any future rows
298 --
299 hr_cle_del.delete_dml
300 (p_rec => p_rec
301 ,p_effective_date => p_effective_date
302 ,p_datetrack_mode => p_datetrack_mode
303 ,p_validation_start_date => p_validation_start_date
304 ,p_validation_end_date => p_validation_end_date
305 );
306 End If;
307 --
308 -- We must now insert the updated row
309 --
310 hr_cle_ins.insert_dml
311 (p_rec => p_rec
312 ,p_effective_date => p_effective_date
313 ,p_datetrack_mode => p_datetrack_mode
314 ,p_validation_start_date => p_validation_start_date
315 ,p_validation_end_date => p_validation_end_date
316 );
317 End If;
318 hr_utility.set_location(' Leaving:'||l_proc, 20);
319 End dt_pre_update;
320 --
321 -- ----------------------------------------------------------------------------
322 -- |------------------------------< pre_update >------------------------------|
323 -- ----------------------------------------------------------------------------
324 -- {Start Of Comments}
325 --
326 -- Description:
327 -- This private procedure contains any processing which is required before
328 -- the update dml.
329 --
330 -- Prerequisites:
331 -- This is an internal procedure which is called from the upd procedure.
332 --
333 -- In Parameters:
334 -- A Pl/Sql record structure.
335 --
336 -- Post Success:
337 -- Processing continues.
338 --
339 -- Post Failure:
340 -- If an error has occurred, an error message and exception will be raised
341 -- but not handled.
342 -- Developer Implementation Notes:
343 -- Any pre-processing required before the update dml is issued should be
344 -- coded within this procedure. It is important to note that any 3rd party
345 -- maintenance should be reviewed before placing in this procedure. The call
346 -- to the dt_update_dml procedure should NOT be removed.
347 --
348 -- Access Status:
349 -- Internal Row Handler Use Only.
350 --
351 -- {End Of Comments}
352 -- ----------------------------------------------------------------------------
353 Procedure pre_update
354 (p_rec in out nocopy hr_cle_shd.g_rec_type
355 ,p_effective_date in date
356 ,p_datetrack_mode in varchar2
357 ,p_validation_start_date in date
358 ,p_validation_end_date in date
359 ) is
360 --
361 l_proc varchar2(72) := g_package||'pre_update';
362 --
363 Begin
364 hr_utility.set_location('Entering:'||l_proc, 5);
365 --
366 --
367 --
368 dt_pre_update
369 (p_rec => p_rec
370 ,p_effective_date => p_effective_date
371 ,p_datetrack_mode => p_datetrack_mode
372 ,p_validation_start_date => p_validation_start_date
373 ,p_validation_end_date => p_validation_end_date
374 );
375 --
376 hr_utility.set_location(' Leaving:'||l_proc, 10);
377 End pre_update;
378 --
379 -- ----------------------------------------------------------------------------
380 -- |----------------------------< post_update >-------------------------------|
381 -- ----------------------------------------------------------------------------
382 -- {Start Of Comments}
383 --
384 -- Description:
385 -- This private procedure contains any processing which is required after
386 -- the update dml.
387 --
388 -- Prerequisites:
389 -- This is an internal procedure which is called from the upd procedure.
390 --
391 -- In Parameters:
392 -- A Pl/Sql record structure.
393 --
394 -- Post Success:
395 -- Processing continues.
396 --
397 -- Post Failure:
398 -- If an error has occurred, an error message and exception will be raised
399 -- but not handled.
400 --
401 -- Developer Implementation Notes:
402 -- Any post-processing required after the update dml is issued should be
403 -- coded within this procedure. It is important to note that any 3rd party
404 -- maintenance should be reviewed before placing in this procedure.
405 --
406 -- Access Status:
407 -- Internal Row Handler Use Only.
408 --
409 -- {End Of Comments}
410 -- ----------------------------------------------------------------------------
411 Procedure post_update
412 (p_rec in hr_cle_shd.g_rec_type
413 ,p_effective_date in date
414 ,p_datetrack_mode in varchar2
415 ,p_validation_start_date in date
416 ,p_validation_end_date in date
417 ) is
418 --
419 l_proc varchar2(72) := g_package||'post_update';
420 --
421 Begin
422 hr_utility.set_location('Entering:'||l_proc, 5);
423 begin
424 --
425 hr_cle_rku.after_update
426 (p_effective_date
427 => p_effective_date
428 ,p_datetrack_mode
429 => p_datetrack_mode
430 ,p_validation_start_date
431 => p_validation_start_date
432 ,p_validation_end_date
433 => p_validation_end_date
434 ,p_soc_ins_contr_lvls_id
435 => p_rec.soc_ins_contr_lvls_id
436 ,p_organization_id
437 => p_rec.organization_id
438 ,p_normal_percentage
439 => p_rec.normal_percentage
440 ,p_normal_amount
441 => p_rec.normal_amount
442 ,p_increased_percentage
443 => p_rec.increased_percentage
444 ,p_increased_amount
445 => p_rec.increased_amount
446 ,p_reduced_percentage
447 => p_rec.reduced_percentage
448 ,p_reduced_amount
449 => p_rec.reduced_amount
450 ,p_effective_start_date
451 => p_rec.effective_start_date
452 ,p_effective_end_date
453 => p_rec.effective_end_date
454 ,p_attribute_category
455 => p_rec.attribute_category
456 ,p_attribute1
457 => p_rec.attribute1
458 ,p_attribute2
459 => p_rec.attribute2
460 ,p_attribute3
461 => p_rec.attribute3
462 ,p_attribute4
463 => p_rec.attribute4
464 ,p_attribute5
465 => p_rec.attribute5
466 ,p_attribute6
467 => p_rec.attribute6
468 ,p_attribute7
469 => p_rec.attribute7
470 ,p_attribute8
471 => p_rec.attribute8
472 ,p_attribute9
473 => p_rec.attribute9
474 ,p_attribute10
475 => p_rec.attribute10
476 ,p_attribute11
477 => p_rec.attribute11
478 ,p_attribute12
479 => p_rec.attribute12
480 ,p_attribute13
481 => p_rec.attribute13
482 ,p_attribute14
483 => p_rec.attribute14
484 ,p_attribute15
485 => p_rec.attribute15
486 ,p_attribute16
487 => p_rec.attribute16
488 ,p_attribute17
489 => p_rec.attribute17
490 ,p_attribute18
491 => p_rec.attribute18
492 ,p_attribute19
493 => p_rec.attribute19
494 ,p_attribute20
495 => p_rec.attribute20
496 ,p_object_version_number
497 => p_rec.object_version_number
498 ,p_attribute21
499 => p_rec.attribute21
500 ,p_attribute22
501 => p_rec.attribute22
502 ,p_attribute23
503 => p_rec.attribute23
504 ,p_attribute24
505 => p_rec.attribute24
506 ,p_attribute25
507 => p_rec.attribute25
508 ,p_attribute26
509 => p_rec.attribute26
510 ,p_attribute27
511 => p_rec.attribute27
512 ,p_attribute28
513 => p_rec.attribute28
514 ,p_attribute29
515 => p_rec.attribute29
516 ,p_attribute30
517 => p_rec.attribute30
518 ,p_flat_tax_limit_per_month
519 => p_rec.flat_tax_limit_per_month
520 ,p_flat_tax_limit_per_year
521 => p_rec.flat_tax_limit_per_year
522 ,p_min_increased_contribution
523 => p_rec.min_increased_contribution
524 ,p_max_increased_contribution
525 => p_rec.max_increased_contribution
526 ,p_month1
527 => p_rec.month1
528 ,p_month1_min_contribution
529 => p_rec.month1_min_contribution
530 ,p_month1_max_contribution
531 => p_rec.month1_max_contribution
532 ,p_month2
533 => p_rec.month2
534 ,p_month2_min_contribution
535 => p_rec.month2_min_contribution
536 ,p_month2_max_contribution
537 => p_rec.month2_max_contribution
538 ,p_employee_contribution
539 => p_rec.employee_contribution
540 ,p_contribution_level_type
541 => p_rec.contribution_level_type
542 ,p_organization_id_o
543 => hr_cle_shd.g_old_rec.organization_id
544 ,p_normal_percentage_o
545 => hr_cle_shd.g_old_rec.normal_percentage
546 ,p_normal_amount_o
547 => hr_cle_shd.g_old_rec.normal_amount
548 ,p_increased_percentage_o
549 => hr_cle_shd.g_old_rec.increased_percentage
550 ,p_increased_amount_o
551 => hr_cle_shd.g_old_rec.increased_amount
552 ,p_reduced_percentage_o
553 => hr_cle_shd.g_old_rec.reduced_percentage
554 ,p_reduced_amount_o
555 => hr_cle_shd.g_old_rec.reduced_amount
556 ,p_effective_start_date_o
557 => hr_cle_shd.g_old_rec.effective_start_date
558 ,p_effective_end_date_o
559 => hr_cle_shd.g_old_rec.effective_end_date
560 ,p_attribute_category_o
561 => hr_cle_shd.g_old_rec.attribute_category
562 ,p_attribute1_o
563 => hr_cle_shd.g_old_rec.attribute1
564 ,p_attribute2_o
565 => hr_cle_shd.g_old_rec.attribute2
566 ,p_attribute3_o
567 => hr_cle_shd.g_old_rec.attribute3
568 ,p_attribute4_o
569 => hr_cle_shd.g_old_rec.attribute4
570 ,p_attribute5_o
571 => hr_cle_shd.g_old_rec.attribute5
572 ,p_attribute6_o
573 => hr_cle_shd.g_old_rec.attribute6
574 ,p_attribute7_o
575 => hr_cle_shd.g_old_rec.attribute7
576 ,p_attribute8_o
577 => hr_cle_shd.g_old_rec.attribute8
578 ,p_attribute9_o
579 => hr_cle_shd.g_old_rec.attribute9
580 ,p_attribute10_o
581 => hr_cle_shd.g_old_rec.attribute10
582 ,p_attribute11_o
583 => hr_cle_shd.g_old_rec.attribute11
584 ,p_attribute12_o
585 => hr_cle_shd.g_old_rec.attribute12
586 ,p_attribute13_o
587 => hr_cle_shd.g_old_rec.attribute13
588 ,p_attribute14_o
589 => hr_cle_shd.g_old_rec.attribute14
590 ,p_attribute15_o
591 => hr_cle_shd.g_old_rec.attribute15
592 ,p_attribute16_o
593 => hr_cle_shd.g_old_rec.attribute16
594 ,p_attribute17_o
595 => hr_cle_shd.g_old_rec.attribute17
596 ,p_attribute18_o
597 => hr_cle_shd.g_old_rec.attribute18
598 ,p_attribute19_o
599 => hr_cle_shd.g_old_rec.attribute19
600 ,p_attribute20_o
601 => hr_cle_shd.g_old_rec.attribute20
602 ,p_object_version_number_o
603 => hr_cle_shd.g_old_rec.object_version_number
604 ,p_attribute21_o
605 => hr_cle_shd.g_old_rec.attribute21
606 ,p_attribute22_o
607 => hr_cle_shd.g_old_rec.attribute22
608 ,p_attribute23_o
609 => hr_cle_shd.g_old_rec.attribute23
610 ,p_attribute24_o
611 => hr_cle_shd.g_old_rec.attribute24
612 ,p_attribute25_o
613 => hr_cle_shd.g_old_rec.attribute25
614 ,p_attribute26_o
615 => hr_cle_shd.g_old_rec.attribute26
616 ,p_attribute27_o
617 => hr_cle_shd.g_old_rec.attribute27
618 ,p_attribute28_o
619 => hr_cle_shd.g_old_rec.attribute28
620 ,p_attribute29_o
621 => hr_cle_shd.g_old_rec.attribute29
622 ,p_attribute30_o
623 => hr_cle_shd.g_old_rec.attribute30
624 ,p_flat_tax_limit_per_month_o
625 => hr_cle_shd.g_old_rec.flat_tax_limit_per_month
626 ,p_flat_tax_limit_per_year_o
627 => hr_cle_shd.g_old_rec.flat_tax_limit_per_year
628 ,p_min_increased_contribution_o
629 => hr_cle_shd.g_old_rec.min_increased_contribution
630 ,p_max_increased_contribution_o
631 => hr_cle_shd.g_old_rec.max_increased_contribution
632 ,p_month1_o
633 => hr_cle_shd.g_old_rec.month1
634 ,p_month1_min_contribution_o
635 => hr_cle_shd.g_old_rec.month1_min_contribution
636 ,p_month1_max_contribution_o
637 => hr_cle_shd.g_old_rec.month1_max_contribution
638 ,p_month2_o
639 => hr_cle_shd.g_old_rec.month2
640 ,p_month2_min_contribution_o
641 => hr_cle_shd.g_old_rec.month2_min_contribution
642 ,p_month2_max_contribution_o
643 => hr_cle_shd.g_old_rec.month2_max_contribution
644 ,p_employee_contribution_o
645 => hr_cle_shd.g_old_rec.employee_contribution
646 ,p_contribution_level_type_o
647 => hr_cle_shd.g_old_rec.contribution_level_type
648 );
649 --
650 exception
651 --
652 when hr_api.cannot_find_prog_unit then
653 --
654 hr_api.cannot_find_prog_unit_error
655 (p_module_name => 'HR_DE_SOC_INS_CONTR_LVLS_F'
656 ,p_hook_type => 'AU');
657 --
658 end;
659 --
660 hr_utility.set_location(' Leaving:'||l_proc, 10);
661 End post_update;
662 --
663 -- ----------------------------------------------------------------------------
664 -- |-----------------------------< convert_defs >-----------------------------|
665 -- ----------------------------------------------------------------------------
666 -- {Start Of Comments}
667 --
668 -- Description:
669 -- The Convert_Defs procedure has one very important function:
670 -- It must return the record structure for the row with all system defaulted
671 -- values converted into its corresponding parameter value for update. When
672 -- we attempt to update a row through the Upd process , certain
673 -- parameters can be defaulted which enables flexibility in the calling of
674 -- the upd process (e.g. only attributes which need to be updated need to be
675 -- specified). For the upd process to determine which attributes
676 -- have NOT been specified we need to check if the parameter has a reserved
677 -- system default value. Therefore, for all parameters which have a
678 -- corresponding reserved system default mechanism specified we need to
679 -- check if a system default is being used. If a system default is being
680 -- used then we convert the defaulted value into its corresponding attribute
681 -- value held in the g_old_rec data structure.
682 --
683 -- Prerequisites:
684 -- This private function can only be called from the upd process.
685 --
686 -- In Parameters:
687 -- A Pl/Sql record structure.
688 --
689 -- Post Success:
690 -- The record structure will be returned with all system defaulted parameter
691 -- values converted into its current row attribute value.
692 --
693 -- Post Failure:
694 -- No direct error handling is required within this function. Any possible
695 -- errors within this procedure will be a PL/SQL value error due to
696 -- conversion of datatypes or data lengths.
697 --
698 -- Developer Implementation Notes:
699 -- None.
700 --
701 -- Access Status:
702 -- Internal Row Handler Use Only.
703 --
704 -- {End Of Comments}
705 -- ----------------------------------------------------------------------------
706 Procedure convert_defs
707 (p_rec in out nocopy hr_cle_shd.g_rec_type
708 ) is
709 --
710 Begin
711 --
712 -- We must now examine each argument value in the
713 -- p_rec plsql record structure
714 -- to see if a system default is being used. If a system default
715 -- is being used then we must set to the 'current' argument value.
716 --
717 If (p_rec.organization_id = hr_api.g_number) then
718 p_rec.organization_id :=
719 hr_cle_shd.g_old_rec.organization_id;
720 End If;
721 If (p_rec.normal_percentage = hr_api.g_number) then
722 p_rec.normal_percentage :=
723 hr_cle_shd.g_old_rec.normal_percentage;
724 End If;
725 If (p_rec.normal_amount = hr_api.g_number) then
726 p_rec.normal_amount :=
727 hr_cle_shd.g_old_rec.normal_amount;
728 End If;
729 If (p_rec.increased_percentage = hr_api.g_number) then
730 p_rec.increased_percentage :=
731 hr_cle_shd.g_old_rec.increased_percentage;
732 End If;
733 If (p_rec.increased_amount = hr_api.g_number) then
734 p_rec.increased_amount :=
735 hr_cle_shd.g_old_rec.increased_amount;
736 End If;
737 If (p_rec.reduced_percentage = hr_api.g_number) then
738 p_rec.reduced_percentage :=
739 hr_cle_shd.g_old_rec.reduced_percentage;
740 End If;
741 If (p_rec.reduced_amount = hr_api.g_number) then
742 p_rec.reduced_amount :=
743 hr_cle_shd.g_old_rec.reduced_amount;
744 End If;
745 If (p_rec.attribute_category = hr_api.g_varchar2) then
746 p_rec.attribute_category :=
747 hr_cle_shd.g_old_rec.attribute_category;
748 End If;
749 If (p_rec.attribute1 = hr_api.g_varchar2) then
750 p_rec.attribute1 :=
751 hr_cle_shd.g_old_rec.attribute1;
752 End If;
753 If (p_rec.attribute2 = hr_api.g_varchar2) then
754 p_rec.attribute2 :=
755 hr_cle_shd.g_old_rec.attribute2;
756 End If;
757 If (p_rec.attribute3 = hr_api.g_varchar2) then
758 p_rec.attribute3 :=
759 hr_cle_shd.g_old_rec.attribute3;
760 End If;
761 If (p_rec.attribute4 = hr_api.g_varchar2) then
762 p_rec.attribute4 :=
763 hr_cle_shd.g_old_rec.attribute4;
764 End If;
765 If (p_rec.attribute5 = hr_api.g_varchar2) then
766 p_rec.attribute5 :=
767 hr_cle_shd.g_old_rec.attribute5;
768 End If;
769 If (p_rec.attribute6 = hr_api.g_varchar2) then
770 p_rec.attribute6 :=
771 hr_cle_shd.g_old_rec.attribute6;
772 End If;
773 If (p_rec.attribute7 = hr_api.g_varchar2) then
774 p_rec.attribute7 :=
775 hr_cle_shd.g_old_rec.attribute7;
776 End If;
777 If (p_rec.attribute8 = hr_api.g_varchar2) then
778 p_rec.attribute8 :=
779 hr_cle_shd.g_old_rec.attribute8;
780 End If;
781 If (p_rec.attribute9 = hr_api.g_varchar2) then
782 p_rec.attribute9 :=
783 hr_cle_shd.g_old_rec.attribute9;
784 End If;
785 If (p_rec.attribute10 = hr_api.g_varchar2) then
786 p_rec.attribute10 :=
787 hr_cle_shd.g_old_rec.attribute10;
788 End If;
789 If (p_rec.attribute11 = hr_api.g_varchar2) then
790 p_rec.attribute11 :=
791 hr_cle_shd.g_old_rec.attribute11;
792 End If;
793 If (p_rec.attribute12 = hr_api.g_varchar2) then
794 p_rec.attribute12 :=
795 hr_cle_shd.g_old_rec.attribute12;
796 End If;
797 If (p_rec.attribute13 = hr_api.g_varchar2) then
798 p_rec.attribute13 :=
799 hr_cle_shd.g_old_rec.attribute13;
800 End If;
801 If (p_rec.attribute14 = hr_api.g_varchar2) then
802 p_rec.attribute14 :=
803 hr_cle_shd.g_old_rec.attribute14;
804 End If;
805 If (p_rec.attribute15 = hr_api.g_varchar2) then
806 p_rec.attribute15 :=
807 hr_cle_shd.g_old_rec.attribute15;
808 End If;
809 If (p_rec.attribute16 = hr_api.g_varchar2) then
810 p_rec.attribute16 :=
811 hr_cle_shd.g_old_rec.attribute16;
812 End If;
813 If (p_rec.attribute17 = hr_api.g_varchar2) then
814 p_rec.attribute17 :=
815 hr_cle_shd.g_old_rec.attribute17;
816 End If;
817 If (p_rec.attribute18 = hr_api.g_varchar2) then
818 p_rec.attribute18 :=
819 hr_cle_shd.g_old_rec.attribute18;
820 End If;
821 If (p_rec.attribute19 = hr_api.g_varchar2) then
822 p_rec.attribute19 :=
823 hr_cle_shd.g_old_rec.attribute19;
824 End If;
825 If (p_rec.attribute20 = hr_api.g_varchar2) then
826 p_rec.attribute20 :=
827 hr_cle_shd.g_old_rec.attribute20;
828 End If;
829 If (p_rec.attribute21 = hr_api.g_varchar2) then
830 p_rec.attribute21 :=
831 hr_cle_shd.g_old_rec.attribute21;
832 End If;
833 If (p_rec.attribute22 = hr_api.g_varchar2) then
834 p_rec.attribute22 :=
835 hr_cle_shd.g_old_rec.attribute22;
836 End If;
837 If (p_rec.attribute23 = hr_api.g_varchar2) then
838 p_rec.attribute23 :=
839 hr_cle_shd.g_old_rec.attribute23;
840 End If;
841 If (p_rec.attribute24 = hr_api.g_varchar2) then
842 p_rec.attribute24 :=
843 hr_cle_shd.g_old_rec.attribute24;
844 End If;
845 If (p_rec.attribute25 = hr_api.g_varchar2) then
846 p_rec.attribute25 :=
847 hr_cle_shd.g_old_rec.attribute25;
848 End If;
849 If (p_rec.attribute26 = hr_api.g_varchar2) then
850 p_rec.attribute26 :=
851 hr_cle_shd.g_old_rec.attribute26;
852 End If;
853 If (p_rec.attribute27 = hr_api.g_varchar2) then
854 p_rec.attribute27 :=
855 hr_cle_shd.g_old_rec.attribute27;
856 End If;
857 If (p_rec.attribute28 = hr_api.g_varchar2) then
858 p_rec.attribute28 :=
859 hr_cle_shd.g_old_rec.attribute28;
860 End If;
861 If (p_rec.attribute29 = hr_api.g_varchar2) then
862 p_rec.attribute29 :=
863 hr_cle_shd.g_old_rec.attribute29;
864 End If;
865 If (p_rec.attribute30 = hr_api.g_varchar2) then
866 p_rec.attribute30 :=
867 hr_cle_shd.g_old_rec.attribute30;
868 End If;
869 If (p_rec.flat_tax_limit_per_month= hr_api.g_number) then
870 p_rec.flat_tax_limit_per_month :=
871 hr_cle_shd.g_old_rec.flat_tax_limit_per_month;
872 End If;
873 If (p_rec.flat_tax_limit_per_year = hr_api.g_number) then
874 p_rec.flat_tax_limit_per_year :=
875 hr_cle_shd.g_old_rec.flat_tax_limit_per_year;
876 End If;
877 If (p_rec.min_increased_contribution= hr_api.g_number) then
878 p_rec.min_increased_contribution :=
879 hr_cle_shd.g_old_rec.min_increased_contribution;
880 End If;
881 If (p_rec.max_increased_contribution= hr_api.g_number) then
882 p_rec.max_increased_contribution :=
883 hr_cle_shd.g_old_rec.max_increased_contribution;
884 End If;
885 If (p_rec.month1= hr_api.g_varchar2) then
886 p_rec.month1 :=
887 hr_cle_shd.g_old_rec.month1;
888 End If;
889 If (p_rec.month1_min_contribution= hr_api.g_number) then
890 p_rec.month1_min_contribution :=
891 hr_cle_shd.g_old_rec.month1_min_contribution;
892 End If;
893 If (p_rec.month1_max_contribution= hr_api.g_number) then
894 p_rec.month1_max_contribution :=
895 hr_cle_shd.g_old_rec.month1_max_contribution;
896 End If;
897 If (p_rec.month2= hr_api.g_varchar2) then
898 p_rec.month2 :=
899 hr_cle_shd.g_old_rec.month2;
900 End If;
901 If (p_rec.month2_min_contribution = hr_api.g_number) then
902 p_rec.month2_min_contribution :=
903 hr_cle_shd.g_old_rec.month2_min_contribution;
904 End If;
905 If (p_rec.month2_max_contribution = hr_api.g_number) then
906 p_rec.month2_max_contribution :=
907 hr_cle_shd.g_old_rec.month2_max_contribution;
908 End If;
909 If (p_rec.employee_contribution= hr_api.g_number) then
910 p_rec.employee_contribution :=
911 hr_cle_shd.g_old_rec.employee_contribution;
912 End If;
913 If (p_rec.contribution_level_type = hr_api.g_varchar2) then
914 p_rec.contribution_level_type :=
915 hr_cle_shd.g_old_rec.contribution_level_type;
916 End If;
917
918 --
919 End convert_defs;
920 --
921 -- ----------------------------------------------------------------------------
922 -- |---------------------------------< upd >----------------------------------|
923 -- ----------------------------------------------------------------------------
924 Procedure upd
925 (p_effective_date in date
926 ,p_datetrack_mode in varchar2
927 ,p_rec in out nocopy hr_cle_shd.g_rec_type
928 ) is
929 --
930 l_proc varchar2(72) := g_package||'upd';
931 l_validation_start_date date;
932 l_validation_end_date date;
933 --
934 Begin
935 hr_utility.set_location('Entering:'||l_proc, 5);
936 --
937 -- Ensure that the DateTrack update mode is valid
938 --
939 dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode);
940 --
941 -- We must lock the row which we need to update.
942 --
943 hr_cle_shd.lck
944 (p_effective_date => p_effective_date
945 ,p_datetrack_mode => p_datetrack_mode
946 ,p_soc_ins_contr_lvls_id => p_rec.soc_ins_contr_lvls_id
947 ,p_object_version_number => p_rec.object_version_number
948 ,p_validation_start_date => l_validation_start_date
949 ,p_validation_end_date => l_validation_end_date
950 );
951 --
952 -- 1. During an update system defaults are used to determine if
953 -- arguments have been defaulted or not. We must therefore
954 -- derive the full record structure values to be updated.
955 --
956 -- 2. Call the supporting update validate operations.
957 --
958 hr_cle_upd.convert_defs(p_rec);
959 --
960 hr_cle_bus.update_validate
961 (p_rec => p_rec
962 ,p_effective_date => p_effective_date
963 ,p_datetrack_mode => p_datetrack_mode
964 ,p_validation_start_date => l_validation_start_date
965 ,p_validation_end_date => l_validation_end_date
966 );
967 --
968 -- Call the supporting pre-update operation
969 --
970 pre_update
971 (p_rec => p_rec
972 ,p_effective_date => p_effective_date
973 ,p_datetrack_mode => p_datetrack_mode
974 ,p_validation_start_date => l_validation_start_date
975 ,p_validation_end_date => l_validation_end_date
976 );
977 --
978 -- Update the row.
979 --
980 update_dml
981 (p_rec => p_rec
982 ,p_effective_date => p_effective_date
983 ,p_datetrack_mode => p_datetrack_mode
984 ,p_validation_start_date => l_validation_start_date
985 ,p_validation_end_date => l_validation_end_date
986 );
987 --
988 -- Call the supporting post-update operation
989 --
990 post_update
991 (p_rec => p_rec
992 ,p_effective_date => p_effective_date
993 ,p_datetrack_mode => p_datetrack_mode
994 ,p_validation_start_date => l_validation_start_date
995 ,p_validation_end_date => l_validation_end_date
996 );
997 End upd;
998 --
999 -- ----------------------------------------------------------------------------
1000 -- |------------------------------< upd >-------------------------------------|
1001 -- ----------------------------------------------------------------------------
1002 Procedure upd
1003 (p_effective_date in date
1004 ,p_datetrack_mode in varchar2
1005 ,p_soc_ins_contr_lvls_id in number
1006 ,p_object_version_number in out nocopy number
1007 ,p_organization_id in number default hr_api.g_number
1008 ,p_normal_percentage in number default hr_api.g_number
1009 ,p_increased_percentage in number default hr_api.g_number
1010 ,p_reduced_percentage in number default hr_api.g_number
1011 ,p_normal_amount in number default hr_api.g_number
1012 ,p_increased_amount in number default hr_api.g_number
1013 ,p_reduced_amount in number default hr_api.g_number
1014 ,p_attribute_category in varchar2 default hr_api.g_varchar2
1015 ,p_attribute1 in varchar2 default hr_api.g_varchar2
1016 ,p_attribute2 in varchar2 default hr_api.g_varchar2
1017 ,p_attribute3 in varchar2 default hr_api.g_varchar2
1018 ,p_attribute4 in varchar2 default hr_api.g_varchar2
1019 ,p_attribute5 in varchar2 default hr_api.g_varchar2
1020 ,p_attribute6 in varchar2 default hr_api.g_varchar2
1021 ,p_attribute7 in varchar2 default hr_api.g_varchar2
1022 ,p_attribute8 in varchar2 default hr_api.g_varchar2
1023 ,p_attribute9 in varchar2 default hr_api.g_varchar2
1024 ,p_attribute10 in varchar2 default hr_api.g_varchar2
1025 ,p_attribute11 in varchar2 default hr_api.g_varchar2
1026 ,p_attribute12 in varchar2 default hr_api.g_varchar2
1027 ,p_attribute13 in varchar2 default hr_api.g_varchar2
1028 ,p_attribute14 in varchar2 default hr_api.g_varchar2
1029 ,p_attribute15 in varchar2 default hr_api.g_varchar2
1030 ,p_attribute16 in varchar2 default hr_api.g_varchar2
1031 ,p_attribute17 in varchar2 default hr_api.g_varchar2
1032 ,p_attribute18 in varchar2 default hr_api.g_varchar2
1033 ,p_attribute19 in varchar2 default hr_api.g_varchar2
1034 ,p_attribute20 in varchar2 default hr_api.g_varchar2
1035 ,p_attribute21 in varchar2 default hr_api.g_varchar2
1036 ,p_attribute22 in varchar2 default hr_api.g_varchar2
1037 ,p_attribute23 in varchar2 default hr_api.g_varchar2
1038 ,p_attribute24 in varchar2 default hr_api.g_varchar2
1039 ,p_attribute25 in varchar2 default hr_api.g_varchar2
1040 ,p_attribute26 in varchar2 default hr_api.g_varchar2
1041 ,p_attribute27 in varchar2 default hr_api.g_varchar2
1042 ,p_attribute28 in varchar2 default hr_api.g_varchar2
1043 ,p_attribute29 in varchar2 default hr_api.g_varchar2
1044 ,p_attribute30 in varchar2 default hr_api.g_varchar2
1045 ,p_flat_tax_limit_per_month in number default hr_api.g_number
1046 ,p_flat_tax_limit_per_year in number default hr_api.g_number
1047 ,p_min_increased_contribution in number default hr_api.g_number
1048 ,p_max_increased_contribution in number default hr_api.g_number
1049 ,p_month1 in varchar2 default hr_api.g_varchar2
1050 ,p_month1_min_contribution in number default hr_api.g_number
1051 ,p_month1_max_contribution in number default hr_api.g_number
1052 ,p_month2 in varchar2 default hr_api.g_varchar2
1053 ,p_month2_min_contribution in number default hr_api.g_number
1054 ,p_month2_max_contribution in number default hr_api.g_number
1055 ,p_employee_contribution in number default hr_api.g_number
1056 ,p_contribution_level_type in varchar2 default hr_api.g_varchar2
1057 ,p_effective_start_date out nocopy date
1058 ,p_effective_end_date out nocopy date
1059 ) is
1060 --
1061 l_rec hr_cle_shd.g_rec_type;
1062 l_proc varchar2(72) := g_package||'upd';
1063 --
1064 Begin
1065 hr_utility.set_location('Entering:'||l_proc, 5);
1066 --
1067 -- Call conversion function to turn arguments into the
1068 -- l_rec structure.
1069 --
1070 l_rec :=
1071 hr_cle_shd.convert_args
1072 (p_soc_ins_contr_lvls_id
1073 ,p_organization_id
1074 ,p_normal_percentage
1075 ,p_normal_amount
1076 ,p_increased_percentage
1077 ,p_increased_amount
1078 ,p_reduced_percentage
1079 ,p_reduced_amount
1080 ,null
1081 ,null
1082 ,p_attribute_category
1083 ,p_attribute1
1084 ,p_attribute2
1085 ,p_attribute3
1086 ,p_attribute4
1087 ,p_attribute5
1088 ,p_attribute6
1089 ,p_attribute7
1090 ,p_attribute8
1091 ,p_attribute9
1092 ,p_attribute10
1093 ,p_attribute11
1094 ,p_attribute12
1095 ,p_attribute13
1096 ,p_attribute14
1097 ,p_attribute15
1098 ,p_attribute16
1099 ,p_attribute17
1100 ,p_attribute18
1101 ,p_attribute19
1102 ,p_attribute20
1103 ,p_object_version_number
1104 ,p_attribute21
1105 ,p_attribute22
1106 ,p_attribute23
1107 ,p_attribute24
1108 ,p_attribute25
1109 ,p_attribute26
1110 ,p_attribute27
1111 ,p_attribute28
1112 ,p_attribute29
1113 ,p_attribute30
1114 ,p_flat_tax_limit_per_month
1115 ,p_flat_tax_limit_per_year
1116 ,p_min_increased_contribution
1117 ,p_max_increased_contribution
1118 ,p_month1
1119 ,p_month1_min_contribution
1120 ,p_month1_max_contribution
1121 ,p_month2
1122 ,p_month2_min_contribution
1123 ,p_month2_max_contribution
1124 ,p_employee_contribution
1125 ,p_contribution_level_type
1126 );
1127 --
1128 -- Having converted the arguments into the
1129 -- plsql record structure we call the corresponding record
1130 -- business process.
1131 --
1132 hr_cle_upd.upd
1133 (p_effective_date
1134 ,p_datetrack_mode
1135 ,l_rec
1136 );
1137 --
1138 -- Set the out parameters
1139 --
1140 p_object_version_number := l_rec.object_version_number;
1141 p_effective_start_date := l_rec.effective_start_date;
1142 p_effective_end_date := l_rec.effective_end_date;
1143 --
1144 --
1145 hr_utility.set_location(' Leaving:'||l_proc, 10);
1146 End upd;
1147 --
1148 end hr_cle_upd;