1 Package Body pay_ipd_upd as
2 /* $Header: pyipdrhi.pkb 120.4 2011/11/09 12:41:18 rsahai ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_ipd_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_ipd_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_ie_paye_details_f'
79 ,p_base_key_column => 'paye_details_id'
80 ,p_base_key_value => p_rec.paye_details_id
81 );
82 --
83 --
84 --
85 -- Update the pay_ie_paye_details_f Row
86 --
87 update pay_ie_paye_details_f
88 set
89 paye_details_id = p_rec.paye_details_id
90 ,object_version_number = p_rec.object_version_number
91 ,assignment_id = p_rec.assignment_id
92 ,info_source = p_rec.info_source
93 ,comm_period_no = p_rec.comm_period_no
94 ,tax_basis = p_rec.tax_basis
95 ,certificate_start_date = p_rec.certificate_start_date
96 ,certificate_end_date = p_rec.certificate_end_date
97 ,tax_assess_basis = p_rec.tax_assess_basis
98 ,weekly_tax_credit = p_rec.weekly_tax_credit
99 ,weekly_std_rate_cut_off = p_rec.weekly_std_rate_cut_off
100 ,monthly_tax_credit = p_rec.monthly_tax_credit
101 ,monthly_std_rate_cut_off = p_rec.monthly_std_rate_cut_off
102 ,request_id = p_rec.request_id
103 ,program_application_id = p_rec.program_application_id
104 ,program_id = p_rec.program_id
105 ,program_update_date = p_rec.program_update_date
106 ,certificate_issue_date = p_rec.certificate_issue_date
107 --13359423
108 ,yrly_tax_cred = p_rec.yrly_tax_cred
109 ,yrly_tax_rate_1 = p_rec.yrly_tax_rate_1
110 ,yrly_tax_rate_2 = p_rec.yrly_tax_rate_2
111 ,mthly_tax_rate_2 = p_rec.mthly_tax_rate_2
112 ,wkly_tax_rate_2 = p_rec.wkly_tax_rate_2
113 ,tax_rate_3 = p_rec.tax_rate_3
114 ,yrly_tax_rate_3 = p_rec.yrly_tax_rate_3
115 ,mthly_tax_rate_3 = p_rec.mthly_tax_rate_3
116 ,wkly_tax_rate_3 = p_rec.wkly_tax_rate_3
117 ,tax_rate_4 = p_rec.tax_rate_4
118 ,yrly_tax_rate_4 = p_rec.yrly_tax_rate_4
119 ,mthly_tax_rate_4 = p_rec.mthly_tax_rate_4
120 ,wkly_tax_rate_4 = p_rec.wkly_tax_rate_4
121 ,tax_rate_5 = p_rec.tax_rate_5
122 ,in_exempt_usc = p_rec.in_exempt_usc
123 ,total_usc_pay_todate = p_rec.total_usc_pay_todate
124 ,total_usc_tax_todate = p_rec.total_usc_tax_todate
125 ,usc_rate_1 = p_rec.usc_rate_1
126 ,usc_yrly_cutoff_1 = p_rec.usc_yrly_cutoff_1
127 ,usc_mthly_cutoff_1 = p_rec.usc_mthly_cutoff_1
128 ,usc_wkly_cutoff_1 = p_rec.usc_wkly_cutoff_1
129 ,usc_rate_2 = p_rec.usc_rate_2
130 ,usc_yrly_cutoff_2 = p_rec.usc_yrly_cutoff_2
131 ,usc_mthly_cutoff_2 = p_rec.usc_mthly_cutoff_2
132 ,usc_wkly_cutoff_2 = p_rec.usc_wkly_cutoff_2
133 ,usc_rate_3 = p_rec.usc_rate_3
134 ,usc_yrly_cutoff_3 = p_rec.usc_yrly_cutoff_3
135 ,usc_mthly_cutoff_3 = p_rec.usc_mthly_cutoff_3
136 ,usc_wkly_cutoff_3 = p_rec.usc_wkly_cutoff_3
137 ,usc_rate_4 = p_rec.usc_rate_4
138 ,usc_yrly_cutoff_4 = p_rec.usc_yrly_cutoff_4
139 ,usc_mthly_cutoff_4 = p_rec.usc_mthly_cutoff_4
140 ,usc_wkly_cutoff_4 = p_rec.usc_wkly_cutoff_4
141 ,usc_rate_5 = p_rec.usc_rate_5
142 ,usc_tax_basis = p_rec.usc_tax_basis
143 ,usc_info_source = p_rec.usc_info_source
144 --13359423
145 where paye_details_id = p_rec.paye_details_id
146 and effective_start_date = p_validation_start_date
147 and effective_end_date = p_validation_end_date;
148 --
149 --
150 --
151 -- Set the effective start and end dates
152 --
153 p_rec.effective_start_date := p_validation_start_date;
154 p_rec.effective_end_date := p_validation_end_date;
155 End If;
156 --
157 hr_utility.set_location(' Leaving:'||l_proc, 15);
158 Exception
159 When hr_api.check_integrity_violated Then
160 -- A check constraint has been violated
161 --
162 pay_ipd_shd.constraint_error
163 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
164 When hr_api.unique_integrity_violated Then
165 -- Unique integrity has been violated
166 --
167 pay_ipd_shd.constraint_error
168 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
169 When Others Then
170 --
171 Raise;
172 End dt_update_dml;
173 --
174 -- ----------------------------------------------------------------------------
175 -- |------------------------------< update_dml >------------------------------|
176 -- ----------------------------------------------------------------------------
177 -- {Start Of Comments}
178 --
179 -- Description:
180 -- This procedure calls the dt_update_dml control logic which handles
181 -- the actual datetrack dml.
182 --
183 -- Prerequisites:
184 -- This is an internal private procedure which must be called from the upd
185 -- procedure.
186 --
187 -- In Parameters:
188 -- A Pl/Sql record structre.
189 --
190 -- Post Success:
191 -- Processing contines.
192 --
193 -- Post Failure:
194 -- No specific error handling is required within this procedure.
195 --
196 -- Developer Implementation Notes:
197 -- The update 'set' arguments list should be modified if any of your
198 -- attributes are not updateable.
199 --
200 -- Access Status:
201 -- Internal Row Handler Use Only.
202 --
203 -- {End Of Comments}
204 -- ----------------------------------------------------------------------------
205 Procedure update_dml
206 (p_rec in out nocopy pay_ipd_shd.g_rec_type
207 ,p_effective_date in date
208 ,p_datetrack_mode in varchar2
209 ,p_validation_start_date in date
210 ,p_validation_end_date in date
211 ) is
212 --
213 l_proc varchar2(72) := g_package||'update_dml';
214 --
215 Begin
216 hr_utility.set_location('Entering:'||l_proc, 5);
217 --
218 pay_ipd_upd.dt_update_dml
219 (p_rec => p_rec
220 ,p_effective_date => p_effective_date
221 ,p_datetrack_mode => p_datetrack_mode
222 ,p_validation_start_date => p_validation_start_date
223 ,p_validation_end_date => p_validation_end_date
224 );
225 --
226 hr_utility.set_location(' Leaving:'||l_proc, 10);
227 End update_dml;
228 --
229 -- ----------------------------------------------------------------------------
230 -- |----------------------------< dt_pre_update >-----------------------------|
231 -- ----------------------------------------------------------------------------
232 -- {Start Of Comments}
233 --
234 -- Description:
235 -- The dt_pre_update procedure controls the execution
236 -- of dml for the datetrack modes of: UPDATE, UPDATE_OVERRIDE
237 -- and UPDATE_CHANGE_INSERT only. The execution required is as
238 -- follows:
239 --
240 -- 1) Providing the datetrack update mode is not 'CORRECTION'
241 -- then set the effective end date of the current row (this
242 -- will be the validation_start_date - 1).
243 -- 2) If the datetrack mode is 'UPDATE_OVERRIDE' then call the
244 -- corresponding delete_dml process to delete any future rows
245 -- where the effective_start_date is greater than or equal to
246 -- the validation_start_date.
247 -- 3) Call the insert_dml process to insert the new updated row
248 -- details.
249 --
250 -- Prerequisites:
251 -- This is an internal procedure which is called from the
252 -- pre_update procedure.
253 --
254 -- In Parameters:
255 --
256 -- Post Success:
257 -- Processing continues.
258 --
259 -- Post Failure:
260 -- If an error has occurred, an error message and exception will be raised
261 -- but not handled.
262 --
263 -- Developer Implementation Notes:
264 -- This is an internal procedure which is required by Datetrack. Don't
265 -- remove or modify.
266 --
267 -- Access Status:
268 -- Internal Row Handler Use Only.
269 --
270 -- {End Of Comments}
271 -- ----------------------------------------------------------------------------
272 Procedure dt_pre_update
273 (p_rec in out nocopy pay_ipd_shd.g_rec_type
274 ,p_effective_date in date
275 ,p_datetrack_mode in varchar2
276 ,p_validation_start_date in date
277 ,p_validation_end_date in date
278 ) is
279 --
280 l_proc varchar2(72) := g_package||'dt_pre_update';
281 l_dummy_version_number number;
282 --
283 Begin
284 hr_utility.set_location('Entering:'||l_proc, 5);
285 If (p_datetrack_mode <> hr_api.g_correction) then
286 --
287 -- Update the current effective end date
288 --
289 pay_ipd_shd.upd_effective_end_date
290 (p_effective_date => p_effective_date
291 ,p_base_key_value => p_rec.paye_details_id
292 ,p_new_effective_end_date => (p_validation_start_date - 1)
293 ,p_validation_start_date => p_validation_start_date
294 ,p_validation_end_date => p_validation_end_date
295 ,p_object_version_number => l_dummy_version_number
296 );
297 --
298 If (p_datetrack_mode = hr_api.g_update_override) then
299 --
300 -- As the datetrack mode is 'UPDATE_OVERRIDE' then we must
301 -- delete any future rows
302 --
303 pay_ipd_del.delete_dml
304 (p_rec => p_rec
305 ,p_effective_date => p_effective_date
306 ,p_datetrack_mode => p_datetrack_mode
307 ,p_validation_start_date => p_validation_start_date
308 ,p_validation_end_date => p_validation_end_date
309 );
310 End If;
311 --
312 -- We must now insert the updated row
313 --
314 pay_ipd_ins.insert_dml
315 (p_rec => p_rec
316 ,p_effective_date => p_effective_date
317 ,p_datetrack_mode => p_datetrack_mode
318 ,p_validation_start_date => p_validation_start_date
319 ,p_validation_end_date => p_validation_end_date
320 );
321 End If;
322 hr_utility.set_location(' Leaving:'||l_proc, 20);
323 End dt_pre_update;
324 --
325 -- ----------------------------------------------------------------------------
326 -- |------------------------------< pre_update >------------------------------|
327 -- ----------------------------------------------------------------------------
328 -- {Start Of Comments}
329 --
330 -- Description:
331 -- This private procedure contains any processing which is required before
332 -- the update dml.
333 --
334 -- Prerequisites:
335 -- This is an internal procedure which is called from the upd procedure.
336 --
337 -- In Parameters:
338 -- A Pl/Sql record structure.
339 --
340 -- Post Success:
341 -- Processing continues.
342 --
343 -- Post Failure:
344 -- If an error has occurred, an error message and exception will be raised
345 -- but not handled.
346 -- Developer Implementation Notes:
347 -- Any pre-processing required before the update dml is issued should be
348 -- coded within this procedure. It is important to note that any 3rd party
349 -- maintenance should be reviewed before placing in this procedure. The call
350 -- to the dt_update_dml procedure should NOT be removed.
351 --
352 -- Access Status:
353 -- Internal Row Handler Use Only.
354 --
355 -- {End Of Comments}
356 -- ----------------------------------------------------------------------------
357 Procedure pre_update
358 (p_rec in out nocopy pay_ipd_shd.g_rec_type
359 ,p_effective_date in date
360 ,p_datetrack_mode in varchar2
361 ,p_validation_start_date in date
362 ,p_validation_end_date in date
363 ) is
364 --
365 l_proc varchar2(72) := g_package||'pre_update';
366 --
367 Begin
368 hr_utility.set_location('Entering:'||l_proc, 5);
369 --
370 --
371 --
372 dt_pre_update
373 (p_rec => p_rec
374 ,p_effective_date => p_effective_date
375 ,p_datetrack_mode => p_datetrack_mode
376 ,p_validation_start_date => p_validation_start_date
377 ,p_validation_end_date => p_validation_end_date
378 );
379 --
380 hr_utility.set_location(' Leaving:'||l_proc, 10);
381 End pre_update;
382 --
383 -- ----------------------------------------------------------------------------
384 -- |----------------------------< post_update >-------------------------------|
385 -- ----------------------------------------------------------------------------
386 -- {Start Of Comments}
387 --
388 -- Description:
389 -- This private procedure contains any processing which is required after
390 -- the update dml.
391 --
392 -- Prerequisites:
393 -- This is an internal procedure which is called from the upd procedure.
394 --
395 -- In Parameters:
396 -- A Pl/Sql record structure.
397 --
398 -- Post Success:
399 -- Processing continues.
400 --
401 -- Post Failure:
402 -- If an error has occurred, an error message and exception will be raised
403 -- but not handled.
404 --
405 -- Developer Implementation Notes:
406 -- Any post-processing required after the update dml is issued should be
407 -- coded within this procedure. It is important to note that any 3rd party
408 -- maintenance should be reviewed before placing in this procedure.
409 --
410 -- Access Status:
411 -- Internal Row Handler Use Only.
412 --
413 -- {End Of Comments}
414 -- ----------------------------------------------------------------------------
415 Procedure post_update
416 (p_rec in pay_ipd_shd.g_rec_type
417 ,p_effective_date in date
418 ,p_datetrack_mode in varchar2
419 ,p_validation_start_date in date
420 ,p_validation_end_date in date
421 ) is
422 --
423 l_proc varchar2(72) := g_package||'post_update';
424 --
425 Begin
426 hr_utility.set_location('Entering:'||l_proc, 5);
427 begin
428 --
429 pay_ipd_rku.after_update
430 (p_effective_date
431 => p_effective_date
432 ,p_datetrack_mode
433 => p_datetrack_mode
434 ,p_validation_start_date
435 => p_validation_start_date
436 ,p_validation_end_date
437 => p_validation_end_date
438 ,p_paye_details_id
439 => p_rec.paye_details_id
440 ,p_effective_start_date
441 => p_rec.effective_start_date
442 ,p_effective_end_date
443 => p_rec.effective_end_date
444 ,p_object_version_number
445 => p_rec.object_version_number
446 ,p_assignment_id
447 => p_rec.assignment_id
448 ,p_info_source
449 => p_rec.info_source
450 ,p_comm_period_no
451 => p_rec.comm_period_no
452 ,p_tax_basis
453 => p_rec.tax_basis
454 ,p_certificate_start_date
455 => p_rec.certificate_start_date
456 ,p_certificate_end_date
457 => p_rec.certificate_end_date
458 ,p_tax_assess_basis
459 => p_rec.tax_assess_basis
460 ,p_weekly_tax_credit
461 => p_rec.weekly_tax_credit
462 ,p_weekly_std_rate_cut_off
463 => p_rec.weekly_std_rate_cut_off
464 ,p_monthly_tax_credit
465 => p_rec.monthly_tax_credit
466 ,p_monthly_std_rate_cut_off
467 => p_rec.monthly_std_rate_cut_off
468 ,p_request_id
469 => p_rec.request_id
470 ,p_program_application_id
471 => p_rec.program_application_id
472 ,p_program_id
473 => p_rec.program_id
474 ,p_program_update_date
475 => p_rec.program_update_date
476 ,p_certificate_issue_date
477 => p_rec.certificate_issue_date
478 ,p_effective_start_date_o
479 => pay_ipd_shd.g_old_rec.effective_start_date
480 ,p_effective_end_date_o
481 => pay_ipd_shd.g_old_rec.effective_end_date
482 ,p_object_version_number_o
483 => pay_ipd_shd.g_old_rec.object_version_number
484 ,p_assignment_id_o
485 => pay_ipd_shd.g_old_rec.assignment_id
486 ,p_info_source_o
487 => pay_ipd_shd.g_old_rec.info_source
488 ,p_comm_period_no_o
489 => pay_ipd_shd.g_old_rec.comm_period_no
490 ,p_tax_basis_o
491 => pay_ipd_shd.g_old_rec.tax_basis
492 ,p_certificate_start_date_o
493 => pay_ipd_shd.g_old_rec.certificate_start_date
494 ,p_certificate_end_date_o
495 => pay_ipd_shd.g_old_rec.certificate_end_date
496 ,p_tax_assess_basis_o
497 => pay_ipd_shd.g_old_rec.tax_assess_basis
498 ,p_weekly_tax_credit_o
499 => pay_ipd_shd.g_old_rec.weekly_tax_credit
500 ,p_weekly_std_rate_cut_off_o
501 => pay_ipd_shd.g_old_rec.weekly_std_rate_cut_off
502 ,p_monthly_tax_credit_o
503 => pay_ipd_shd.g_old_rec.monthly_tax_credit
504 ,p_monthly_std_rate_cut_off_o
505 => pay_ipd_shd.g_old_rec.monthly_std_rate_cut_off
506 ,p_request_id_o
507 => pay_ipd_shd.g_old_rec.request_id
508 ,p_program_application_id_o
509 => pay_ipd_shd.g_old_rec.program_application_id
510 ,p_program_id_o
511 => pay_ipd_shd.g_old_rec.program_id
512 ,p_program_update_date_o
513 => pay_ipd_shd.g_old_rec.program_update_date
514 ,p_certificate_issue_date_o
515 => pay_ipd_shd.g_old_rec.certificate_issue_date
516 );
517 --
518 exception
519 --
520 when hr_api.cannot_find_prog_unit then
521 --
522 hr_api.cannot_find_prog_unit_error
523 (p_module_name => 'PAY_IE_PAYE_DETAILS_F'
524 ,p_hook_type => 'AU');
525 --
526 end;
527 --
528 hr_utility.set_location(' Leaving:'||l_proc, 10);
529 End post_update;
530 --
531 -- ----------------------------------------------------------------------------
532 -- |-----------------------------< convert_defs >-----------------------------|
533 -- ----------------------------------------------------------------------------
534 -- {Start Of Comments}
535 --
536 -- Description:
537 -- The Convert_Defs procedure has one very important function:
538 -- It must return the record structure for the row with all system defaulted
539 -- values converted into its corresponding parameter value for update. When
540 -- we attempt to update a row through the Upd process , certain
541 -- parameters can be defaulted which enables flexibility in the calling of
542 -- the upd process (e.g. only attributes which need to be updated need to be
543 -- specified). For the upd process to determine which attributes
544 -- have NOT been specified we need to check if the parameter has a reserved
545 -- system default value. Therefore, for all parameters which have a
546 -- corresponding reserved system default mechanism specified we need to
547 -- check if a system default is being used. If a system default is being
548 -- used then we convert the defaulted value into its corresponding attribute
549 -- value held in the g_old_rec data structure.
550 --
551 -- Prerequisites:
552 -- This private function can only be called from the upd process.
553 --
554 -- In Parameters:
555 -- A Pl/Sql record structure.
556 --
557 -- Post Success:
558 -- The record structure will be returned with all system defaulted parameter
559 -- values converted into its current row attribute value.
560 --
561 -- Post Failure:
562 -- No direct error handling is required within this function. Any possible
563 -- errors within this procedure will be a PL/SQL value error due to
564 -- conversion of datatypes or data lengths.
565 --
566 -- Developer Implementation Notes:
567 -- None.
568 --
569 -- Access Status:
570 -- Internal Row Handler Use Only.
571 --
572 -- {End Of Comments}
573 -- ----------------------------------------------------------------------------
574 Procedure convert_defs
575 (p_rec in out nocopy pay_ipd_shd.g_rec_type
576 ) is
577 --
578 Begin
579 --
580 -- We must now examine each argument value in the
581 -- p_rec plsql record structure
582 -- to see if a system default is being used. If a system default
583 -- is being used then we must set to the 'current' argument value.
584 --
585 If (p_rec.assignment_id = hr_api.g_number) then
586 p_rec.assignment_id :=
587 pay_ipd_shd.g_old_rec.assignment_id;
588 End If;
589 If (p_rec.info_source = hr_api.g_varchar2) then
590 p_rec.info_source :=
591 pay_ipd_shd.g_old_rec.info_source;
592 End If;
593 If (p_rec.comm_period_no = hr_api.g_number) then
594 p_rec.comm_period_no :=
595 pay_ipd_shd.g_old_rec.comm_period_no;
596 End If;
597 If (p_rec.tax_basis = hr_api.g_varchar2) then
598 p_rec.tax_basis :=
599 pay_ipd_shd.g_old_rec.tax_basis;
600 End If;
601 If (p_rec.certificate_start_date = hr_api.g_date) then
602 p_rec.certificate_start_date :=
603 pay_ipd_shd.g_old_rec.certificate_start_date;
604 End If;
605 If (p_rec.certificate_end_date = hr_api.g_date) then
606 p_rec.certificate_end_date :=
607 pay_ipd_shd.g_old_rec.certificate_end_date;
608 End If;
609 If (p_rec.tax_assess_basis = hr_api.g_varchar2) then
610 p_rec.tax_assess_basis :=
611 pay_ipd_shd.g_old_rec.tax_assess_basis;
612 End If;
613 If (p_rec.weekly_tax_credit = hr_api.g_number) then
614 p_rec.weekly_tax_credit :=
615 pay_ipd_shd.g_old_rec.weekly_tax_credit;
616 End If;
617 If (p_rec.weekly_std_rate_cut_off = hr_api.g_number) then
618 p_rec.weekly_std_rate_cut_off :=
619 pay_ipd_shd.g_old_rec.weekly_std_rate_cut_off;
620 End If;
621 If (p_rec.monthly_tax_credit = hr_api.g_number) then
622 p_rec.monthly_tax_credit :=
623 pay_ipd_shd.g_old_rec.monthly_tax_credit;
624 End If;
625 If (p_rec.monthly_std_rate_cut_off = hr_api.g_number) then
626 p_rec.monthly_std_rate_cut_off :=
627 pay_ipd_shd.g_old_rec.monthly_std_rate_cut_off;
628 End If;
629 If (p_rec.request_id = hr_api.g_number) then
630 p_rec.request_id :=
631 pay_ipd_shd.g_old_rec.request_id;
632 End If;
633 If (p_rec.program_application_id = hr_api.g_number) then
634 p_rec.program_application_id :=
635 pay_ipd_shd.g_old_rec.program_application_id;
636 End If;
637 If (p_rec.program_id = hr_api.g_number) then
638 p_rec.program_id :=
639 pay_ipd_shd.g_old_rec.program_id;
640 End If;
641 If (p_rec.program_update_date = hr_api.g_date) then
642 p_rec.program_update_date :=
643 pay_ipd_shd.g_old_rec.program_update_date;
644 End If;
645 If (p_rec.certificate_issue_date = hr_api.g_date) then
646 p_rec.certificate_issue_date :=
647 pay_ipd_shd.g_old_rec.certificate_issue_date;
648 End If;
649 --
650
651 --13359423
652 If (p_rec.yrly_tax_cred = hr_api.g_number) then
653 p_rec.yrly_tax_cred := pay_ipd_shd.g_old_rec.yrly_tax_cred;
654 End If;
655
656 If (p_rec.yrly_tax_rate_1 = hr_api.g_number) then
657 p_rec.yrly_tax_rate_1 := pay_ipd_shd.g_old_rec.yrly_tax_rate_1;
658 End if;
659
660 If (p_rec.yrly_tax_rate_2 = hr_api.g_number) then
661 p_rec.yrly_tax_rate_2 := pay_ipd_shd.g_old_rec.yrly_tax_rate_2;
662 End if;
663
664 If (p_rec.mthly_tax_rate_2 = hr_api.g_number) then
665 p_rec.mthly_tax_rate_2 := pay_ipd_shd.g_old_rec.mthly_tax_rate_2;
666 End if;
667
668 If (p_rec.wkly_tax_rate_2 = hr_api.g_number) then
669 p_rec.wkly_tax_rate_2 := pay_ipd_shd.g_old_rec.wkly_tax_rate_2;
670 End if;
671
672 If (p_rec.tax_rate_3 = hr_api.g_number) then
673 p_rec.tax_rate_3 := pay_ipd_shd.g_old_rec.tax_rate_3;
674 End if;
675
676 If (p_rec.yrly_tax_rate_3 = hr_api.g_number) then
677 p_rec.yrly_tax_rate_3 := pay_ipd_shd.g_old_rec.yrly_tax_rate_3;
678 End if;
679
680 If (p_rec.mthly_tax_rate_3 = hr_api.g_number) then
681 p_rec.mthly_tax_rate_3 := pay_ipd_shd.g_old_rec.mthly_tax_rate_3;
682 End if;
683
684 If (p_rec.wkly_tax_rate_3 = hr_api.g_number) then
685 p_rec.wkly_tax_rate_3 := pay_ipd_shd.g_old_rec.wkly_tax_rate_3;
686 End if;
687
688 If (p_rec.tax_rate_4 = hr_api.g_number) then
689 p_rec.tax_rate_4 := pay_ipd_shd.g_old_rec.tax_rate_4;
690 End if;
691
692 If (p_rec.yrly_tax_rate_4 = hr_api.g_number) then
693 p_rec.yrly_tax_rate_4 := pay_ipd_shd.g_old_rec.yrly_tax_rate_4;
694 End if;
695
696 If (p_rec.mthly_tax_rate_4 = hr_api.g_number) then
697 p_rec.mthly_tax_rate_4 := pay_ipd_shd.g_old_rec.mthly_tax_rate_4;
698 End if;
699
700 If (p_rec.wkly_tax_rate_4 = hr_api.g_number) then
701 p_rec.wkly_tax_rate_4 := pay_ipd_shd.g_old_rec.wkly_tax_rate_4;
702 End if;
703
704 If (p_rec.tax_rate_5 = hr_api.g_number) then
705 p_rec.tax_rate_5 := pay_ipd_shd.g_old_rec.tax_rate_5;
706 End if;
707
708 If (p_rec.in_exempt_usc = hr_api.g_varchar2) then
709 p_rec.in_exempt_usc := pay_ipd_shd.g_old_rec.in_exempt_usc;
710 End if;
711
712 If (p_rec.total_usc_pay_todate = hr_api.g_number) then
713 p_rec.total_usc_pay_todate := pay_ipd_shd.g_old_rec.total_usc_pay_todate;
714 End if;
715
716 If (p_rec.total_usc_tax_todate = hr_api.g_number) then
717 p_rec.total_usc_tax_todate := pay_ipd_shd.g_old_rec.total_usc_tax_todate;
718 End if;
719
720 If (p_rec.usc_rate_1 = hr_api.g_number) then
721 p_rec.usc_rate_1 := pay_ipd_shd.g_old_rec.usc_rate_1;
722 End if;
723
724 If (p_rec.usc_yrly_cutoff_1 = hr_api.g_number) then
725 p_rec.usc_yrly_cutoff_1 := pay_ipd_shd.g_old_rec.usc_yrly_cutoff_1;
726 End if;
727
728 If (p_rec.usc_mthly_cutoff_1 = hr_api.g_number) then
729 p_rec.usc_mthly_cutoff_1 := pay_ipd_shd.g_old_rec.usc_mthly_cutoff_1;
730 End if;
731
732 If (p_rec.usc_wkly_cutoff_1 = hr_api.g_number) then
733 p_rec.usc_wkly_cutoff_1 := pay_ipd_shd.g_old_rec.usc_wkly_cutoff_1;
734 End if;
735
736 If (p_rec.usc_rate_2 = hr_api.g_number) then
737 p_rec.usc_rate_2 := pay_ipd_shd.g_old_rec.usc_rate_2;
738 End if;
739
740 If (p_rec.usc_yrly_cutoff_2 = hr_api.g_number) then
741 p_rec.usc_yrly_cutoff_2 := pay_ipd_shd.g_old_rec.usc_yrly_cutoff_2;
742 End if;
743
744 If (p_rec.usc_mthly_cutoff_2 = hr_api.g_number) then
745 p_rec.usc_mthly_cutoff_2 := pay_ipd_shd.g_old_rec.usc_mthly_cutoff_2;
746 End if;
747
748 If (p_rec.usc_wkly_cutoff_2 = hr_api.g_number) then
749 p_rec.usc_wkly_cutoff_2 := pay_ipd_shd.g_old_rec.usc_wkly_cutoff_2;
750 End if;
751
752 If (p_rec.usc_rate_3 = hr_api.g_number) then
753 p_rec.usc_rate_3 := pay_ipd_shd.g_old_rec.usc_rate_3;
754 End if;
755
756 If (p_rec.usc_yrly_cutoff_3 = hr_api.g_number) then
757 p_rec.usc_yrly_cutoff_3 := pay_ipd_shd.g_old_rec.usc_yrly_cutoff_3;
758 End if;
759
760 If (p_rec.usc_mthly_cutoff_3 = hr_api.g_number) then
761 p_rec.usc_mthly_cutoff_3 := pay_ipd_shd.g_old_rec.usc_mthly_cutoff_3;
762 End if;
763
764 If (p_rec.usc_wkly_cutoff_3 = hr_api.g_number) then
765 p_rec.usc_wkly_cutoff_3 := pay_ipd_shd.g_old_rec.usc_wkly_cutoff_3;
766 End if;
767
768 If (p_rec.usc_rate_4 = hr_api.g_number) then
769 p_rec.usc_rate_4 := pay_ipd_shd.g_old_rec.usc_rate_4;
770 End if;
771
772 If (p_rec.usc_yrly_cutoff_4 = hr_api.g_number) then
773 p_rec.usc_yrly_cutoff_4 := pay_ipd_shd.g_old_rec.usc_yrly_cutoff_4;
774 End if;
775
776 If (p_rec.usc_mthly_cutoff_4 = hr_api.g_number) then
777 p_rec.usc_mthly_cutoff_4 := pay_ipd_shd.g_old_rec.usc_mthly_cutoff_4;
778 End if;
779
780 If (p_rec.usc_wkly_cutoff_4 = hr_api.g_number) then
781 p_rec.usc_wkly_cutoff_4 := pay_ipd_shd.g_old_rec.usc_wkly_cutoff_4;
782 End if;
783
784 If (p_rec.usc_rate_5 = hr_api.g_number) then
785 p_rec.usc_rate_5 := pay_ipd_shd.g_old_rec.usc_rate_5;
786 End if;
787
788 If (p_rec.usc_tax_basis = hr_api.g_varchar2) then
789 p_rec.usc_tax_basis := pay_ipd_shd.g_old_rec.usc_tax_basis;
790 End if;
791
792 If (p_rec.usc_info_source = hr_api.g_varchar2) then
793 p_rec.usc_info_source := pay_ipd_shd.g_old_rec.usc_info_source;
794 End if;
795 --13359423
796
797 End convert_defs;
798 --
799 -- ----------------------------------------------------------------------------
800 -- |---------------------------------< upd >----------------------------------|
801 -- ----------------------------------------------------------------------------
802 Procedure upd
803 (p_effective_date in date
804 ,p_datetrack_mode in varchar2
805 ,p_rec in out nocopy pay_ipd_shd.g_rec_type
806 ) is
807 --
808 l_proc varchar2(72) := g_package||'upd';
809 l_validation_start_date date;
810 l_validation_end_date date;
811 --
812 Begin
813 hr_utility.set_location('Entering:'||l_proc, 5);
814 --
815 -- Ensure that the DateTrack update mode is valid
816 --
817 dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode);
818 --
819 -- We must lock the row which we need to update.
820 --
821 pay_ipd_shd.lck
822 (p_effective_date => p_effective_date
823 ,p_datetrack_mode => p_datetrack_mode
824 ,p_paye_details_id => p_rec.paye_details_id
825 ,p_object_version_number => p_rec.object_version_number
826 ,p_validation_start_date => l_validation_start_date
827 ,p_validation_end_date => l_validation_end_date
828 );
829 --
830 -- 1. During an update system defaults are used to determine if
831 -- arguments have been defaulted or not. We must therefore
832 -- derive the full record structure values to be updated.
833 --
834 -- 2. Call the supporting update validate operations.
835 --
836 pay_ipd_upd.convert_defs(p_rec);
837 --
838 pay_ipd_bus.update_validate
839 (p_rec => p_rec
840 ,p_effective_date => p_effective_date
841 ,p_datetrack_mode => p_datetrack_mode
842 ,p_validation_start_date => l_validation_start_date
843 ,p_validation_end_date => l_validation_end_date
844 );
845 --
846 -- Call the supporting pre-update operation
847 --
848 pre_update
849 (p_rec => p_rec
850 ,p_effective_date => p_effective_date
851 ,p_datetrack_mode => p_datetrack_mode
852 ,p_validation_start_date => l_validation_start_date
853 ,p_validation_end_date => l_validation_end_date
854 );
855 --
856 -- Update the row.
857 --
858 update_dml
859 (p_rec => p_rec
860 ,p_effective_date => p_effective_date
861 ,p_datetrack_mode => p_datetrack_mode
862 ,p_validation_start_date => l_validation_start_date
863 ,p_validation_end_date => l_validation_end_date
864 );
865 --
866 -- Call the supporting post-update operation
867 --
868 post_update
869 (p_rec => p_rec
870 ,p_effective_date => p_effective_date
871 ,p_datetrack_mode => p_datetrack_mode
872 ,p_validation_start_date => l_validation_start_date
873 ,p_validation_end_date => l_validation_end_date
874 );
875 End upd;
876 --
877 -- ----------------------------------------------------------------------------
878 -- |------------------------------< upd >-------------------------------------|
879 -- ----------------------------------------------------------------------------
880 Procedure upd
881 (p_effective_date in date
882 ,p_datetrack_mode in varchar2
883 ,p_paye_details_id in number
884 ,p_object_version_number in out nocopy number
885 ,p_info_source in varchar2 default hr_api.g_varchar2
886 ,p_tax_basis in varchar2 default hr_api.g_varchar2
887 ,p_certificate_start_date in date default hr_api.g_date
888 ,p_tax_assess_basis in varchar2 default hr_api.g_varchar2
889 ,p_certificate_end_date in date default hr_api.g_date
890 ,p_weekly_tax_credit in number default hr_api.g_number
891 ,p_weekly_std_rate_cut_off in number default hr_api.g_number
892 ,p_monthly_tax_credit in number default hr_api.g_number
893 ,p_monthly_std_rate_cut_off in number default hr_api.g_number
894 ,p_request_id in number default hr_api.g_number
895 ,p_program_application_id in number default hr_api.g_number
896 ,p_program_id in number default hr_api.g_number
897 ,p_program_update_date in date default hr_api.g_date
898 ,p_certificate_issue_date in date default hr_api.g_date
899 ,p_effective_start_date out nocopy date
900 ,p_effective_end_date out nocopy date
901 --13359423
902 ,p_yrly_tax_cred in NUMBER default hr_api.g_number
903 ,p_yrly_tax_rate_1 in number default hr_api.g_number
904 ,p_yrly_tax_rate_2 in number default hr_api.g_number
905 ,p_mthly_tax_rate_2 in number default hr_api.g_number
906 ,p_wkly_tax_rate_2 in number default hr_api.g_number
907 ,p_tax_rate_3 in number default hr_api.g_number
908 ,p_yrly_tax_rate_3 in number default hr_api.g_number
909 ,p_mthly_tax_rate_3 in number default hr_api.g_number
910 ,p_wkly_tax_rate_3 in number default hr_api.g_number
911 ,p_tax_rate_4 in number default hr_api.g_number
912 ,p_yrly_tax_rate_4 in number default hr_api.g_number
913 ,p_mthly_tax_rate_4 in number default hr_api.g_number
914 ,p_wkly_tax_rate_4 in number default hr_api.g_number
915 ,p_tax_rate_5 in number default hr_api.g_number
916 ,p_in_exempt_usc in varchar2 default hr_api.g_varchar2
917 ,p_total_usc_pay_todate in number default hr_api.g_number
918 ,p_total_usc_tax_todate in number default hr_api.g_number
919 ,p_usc_rate_1 in number default hr_api.g_number
920 ,p_usc_yrly_cutoff_1 in number default hr_api.g_number
921 ,p_usc_mthly_cutoff_1 in number default hr_api.g_number
922 ,p_usc_wkly_cutoff_1 in number default hr_api.g_number
923 ,p_usc_rate_2 in number default hr_api.g_number
924 ,p_usc_yrly_cutoff_2 in number default hr_api.g_number
925 ,p_usc_mthly_cutoff_2 in number default hr_api.g_number
926 ,p_usc_wkly_cutoff_2 in number default hr_api.g_number
927 ,p_usc_rate_3 in number default hr_api.g_number
928 ,p_usc_yrly_cutoff_3 in number default hr_api.g_number
929 ,p_usc_mthly_cutoff_3 in number default hr_api.g_number
930 ,p_usc_wkly_cutoff_3 in number default hr_api.g_number
931 ,p_usc_rate_4 in number default hr_api.g_number
932 ,p_usc_yrly_cutoff_4 in number default hr_api.g_number
933 ,p_usc_mthly_cutoff_4 in number default hr_api.g_number
934 ,p_usc_wkly_cutoff_4 in number default hr_api.g_number
935 ,p_usc_rate_5 in number default hr_api.g_number
936 ,p_usc_tax_basis in varchar2 default hr_api.g_varchar2
937 ,p_usc_info_source in varchar2 default hr_api.g_varchar2
938 --13359423
939 ) is
940 --
941 l_rec pay_ipd_shd.g_rec_type;
942 l_proc varchar2(72) := g_package||'upd';
943 --
944 Begin
945 hr_utility.set_location('Entering:'||l_proc, 5);
946 --
947 -- Call conversion function to turn arguments into the
948 -- l_rec structure.
949 --
950 l_rec :=
951 pay_ipd_shd.convert_args
952 (p_paye_details_id
953 ,null
954 ,null
955 ,p_object_version_number
956 ,hr_api.g_number
957 ,p_info_source
958 ,hr_api.g_number
959 ,p_tax_basis
960 ,p_certificate_start_date
961 ,p_certificate_end_date
962 ,p_tax_assess_basis
963 ,p_weekly_tax_credit
964 ,p_weekly_std_rate_cut_off
965 ,p_monthly_tax_credit
966 ,p_monthly_std_rate_cut_off
967 ,p_request_id
968 ,p_program_application_id
969 ,p_program_id
970 ,p_program_update_date
971 ,p_certificate_issue_date
972 --13359423
973 ,p_yrly_tax_cred
974 ,p_yrly_tax_rate_1
975 ,p_yrly_tax_rate_2
976 ,p_mthly_tax_rate_2
977 ,p_wkly_tax_rate_2
978 ,p_tax_rate_3
979 ,p_yrly_tax_rate_3
980 ,p_mthly_tax_rate_3
981 ,p_wkly_tax_rate_3
982 ,p_tax_rate_4
983 ,p_yrly_tax_rate_4
984 ,p_mthly_tax_rate_4
985 ,p_wkly_tax_rate_4
986 ,p_tax_rate_5
987 ,p_in_exempt_usc
988 ,p_total_usc_pay_todate
989 ,p_total_usc_tax_todate
990 ,p_usc_rate_1
991 ,p_usc_yrly_cutoff_1
992 ,p_usc_mthly_cutoff_1
993 ,p_usc_wkly_cutoff_1
994 ,p_usc_rate_2
995 ,p_usc_yrly_cutoff_2
996 ,p_usc_mthly_cutoff_2
997 ,p_usc_wkly_cutoff_2
998 ,p_usc_rate_3
999 ,p_usc_yrly_cutoff_3
1000 ,p_usc_mthly_cutoff_3
1001 ,p_usc_wkly_cutoff_3
1002 ,p_usc_rate_4
1003 ,p_usc_yrly_cutoff_4
1004 ,p_usc_mthly_cutoff_4
1005 ,p_usc_wkly_cutoff_4
1006 ,p_usc_rate_5
1007 ,p_usc_tax_basis
1008 ,p_usc_info_source
1009 --13359423
1010 );
1011 --
1012 -- Having converted the arguments into the
1013 -- plsql record structure we call the corresponding record
1014 -- business process.
1015 --
1016 pay_ipd_upd.upd
1017 (p_effective_date
1018 ,p_datetrack_mode
1019 ,l_rec
1020 );
1021 --
1022 -- Set the out parameters
1023 --
1024 p_object_version_number := l_rec.object_version_number;
1025 p_effective_start_date := l_rec.effective_start_date;
1026 p_effective_end_date := l_rec.effective_end_date;
1027 --
1028 --
1029 hr_utility.set_location(' Leaving:'||l_proc, 10);
1030 End upd;
1031 --
1032 end pay_ipd_upd;
[Home] [Help]