[Home] [Help]
PACKAGE BODY: APPS.PAY_IPD_SHD
Source
1 Package Body pay_ipd_shd 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_shd.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< constraint_error >---------------------------|
12 -- ----------------------------------------------------------------------------
13 Procedure constraint_error
14 (p_constraint_name in all_constraints.constraint_name%TYPE
15 ) Is
16 --
17 l_proc varchar2(72) := g_package||'constraint_error';
18 --
19 Begin
20 --
21 If (p_constraint_name = 'PAY_IE_PAYE_DETAILS_F_PK') Then
22 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
23 fnd_message.set_token('PROCEDURE', l_proc);
24 fnd_message.set_token('STEP','5');
25 fnd_message.raise_error;
26 Else
27 fnd_message.set_name('PAY', 'HR_7877_API_INVALID_CONSTRAINT');
28 fnd_message.set_token('PROCEDURE', l_proc);
29 fnd_message.set_token('CONSTRAINT_NAME', p_constraint_name);
30 fnd_message.raise_error;
31 End If;
32 --
33 End constraint_error;
34 --
35 -- ----------------------------------------------------------------------------
36 -- |-----------------------------< api_updating >-----------------------------|
37 -- ----------------------------------------------------------------------------
38 Function api_updating
39 (p_effective_date in date
40 ,p_paye_details_id in number
41 ,p_object_version_number in number
42 ) Return Boolean Is
43 --
44 -- Cursor selects the 'current' row from the HR Schema
45 --
46 Cursor C_Sel1 is
47 select
48 paye_details_id
49 ,effective_start_date
50 ,effective_end_date
51 ,object_version_number
52 ,assignment_id
53 ,info_source
54 ,comm_period_no
55 ,tax_basis
56 ,certificate_start_date
57 ,certificate_end_date
58 ,tax_assess_basis
59 ,weekly_tax_credit
60 ,weekly_std_rate_cut_off
61 ,monthly_tax_credit
62 ,monthly_std_rate_cut_off
63 ,request_id
64 ,program_application_id
65 ,program_id
66 ,program_update_date
67 ,certificate_issue_date
68 --13359423
69 ,yrly_tax_cred
70 ,yrly_tax_rate_1
71 ,yrly_tax_rate_2
72 ,mthly_tax_rate_2
73 ,wkly_tax_rate_2
74 ,tax_rate_3
75 ,yrly_tax_rate_3
76 ,mthly_tax_rate_3
77 ,wkly_tax_rate_3
78 ,tax_rate_4
79 ,yrly_tax_rate_4
80 ,mthly_tax_rate_4
84 ,total_usc_pay_todate
81 ,wkly_tax_rate_4
82 ,tax_rate_5
83 ,in_exempt_usc
85 ,total_usc_tax_todate
86 ,usc_rate_1
87 ,usc_yrly_cutoff_1
88 ,usc_mthly_cutoff_1
89 ,usc_wkly_cutoff_1
90 ,usc_rate_2
91 ,usc_yrly_cutoff_2
92 ,usc_mthly_cutoff_2
93 ,usc_wkly_cutoff_2
94 ,usc_rate_3
95 ,usc_yrly_cutoff_3
96 ,usc_mthly_cutoff_3
97 ,usc_wkly_cutoff_3
98 ,usc_rate_4
99 ,usc_yrly_cutoff_4
100 ,usc_mthly_cutoff_4
101 ,usc_wkly_cutoff_4
102 ,usc_rate_5
103 ,usc_tax_basis
104 ,usc_info_source
105 --13359423
106 from pay_ie_paye_details_f
107 where paye_details_id = p_paye_details_id
108 and p_effective_date
109 between effective_start_date and effective_end_date;
110 --
111 l_fct_ret boolean;
112 --
113 Begin
114 --
115 If (p_effective_date is null or
116 p_paye_details_id is null or
117 p_object_version_number is null) Then
118 --
119 -- One of the primary key arguments is null therefore we must
120 -- set the returning function value to false
121 --
122 l_fct_ret := false;
123 Else
124 If (p_paye_details_id =
125 pay_ipd_shd.g_old_rec.paye_details_id and
126 p_object_version_number =
127 pay_ipd_shd.g_old_rec.object_version_number
128 ) Then
129 --
130 -- The g_old_rec is current therefore we must
131 -- set the returning function to true
132 --
133 l_fct_ret := true;
134 Else
135 --
136 -- Select the current row
137 --
138 Open C_Sel1;
139 Fetch C_Sel1 Into pay_ipd_shd.g_old_rec;
140 If C_Sel1%notfound Then
141 Close C_Sel1;
142 --
143 -- The primary key is invalid therefore we must error
144 --
145 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
146 fnd_message.raise_error;
147 End If;
148 Close C_Sel1;
149 If (p_object_version_number
150 <> pay_ipd_shd.g_old_rec.object_version_number) Then
151 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
152 fnd_message.raise_error;
153 End If;
154 l_fct_ret := true;
155 End If;
156 End If;
157 Return (l_fct_ret);
158 --
159 End api_updating;
160 --
161 -- ----------------------------------------------------------------------------
162 -- |---------------------------< find_dt_upd_modes >--------------------------|
163 -- ----------------------------------------------------------------------------
164 Procedure find_dt_upd_modes
165 (p_effective_date in date
166 ,p_base_key_value in number
167 ,p_correction out nocopy boolean
168 ,p_update out nocopy boolean
169 ,p_update_override out nocopy boolean
170 ,p_update_change_insert out nocopy boolean
171 ) is
172 --
173 l_proc varchar2(72) := g_package||'find_dt_upd_modes';
174 --
175 Begin
176 hr_utility.set_location('Entering:'||l_proc, 5);
177 --
178 -- Call the corresponding datetrack api
179 --
180 dt_api.find_dt_upd_modes
181 (p_effective_date => p_effective_date
182 ,p_base_table_name => 'pay_ie_paye_details_f'
183 ,p_base_key_column => 'paye_details_id'
184 ,p_base_key_value => p_base_key_value
185 ,p_correction => p_correction
186 ,p_update => p_update
187 ,p_update_override => p_update_override
188 ,p_update_change_insert => p_update_change_insert
189 );
190 --
191 hr_utility.set_location(' Leaving:'||l_proc, 10);
192 End find_dt_upd_modes;
193 --
194 -- ----------------------------------------------------------------------------
195 -- |---------------------------< find_dt_del_modes >--------------------------|
196 -- ----------------------------------------------------------------------------
197 Procedure find_dt_del_modes
198 (p_effective_date in date
199 ,p_base_key_value in number
200 ,p_zap out nocopy boolean
201 ,p_delete out nocopy boolean
202 ,p_future_change out nocopy boolean
203 ,p_delete_next_change out nocopy boolean
204 ) is
205 --
206 l_proc varchar2(72) := g_package||'find_dt_del_modes';
207 --
208 l_parent_key_value1 number;
209 --
210 Cursor C_Sel1 Is
211 select
212 t.assignment_id
213 from pay_ie_paye_details_f t
214 where t.paye_details_id = p_base_key_value
215 and p_effective_date
216 between t.effective_start_date and t.effective_end_date;
217 --
218 Begin
219 hr_utility.set_location('Entering:'||l_proc, 5);
220 Open C_sel1;
221 Fetch C_Sel1 Into
222 l_parent_key_value1;
223 If C_Sel1%NOTFOUND then
224 Close C_Sel1;
225 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
226 fnd_message.set_token('PROCEDURE',l_proc);
227 fnd_message.set_token('STEP','10');
228 fnd_message.raise_error;
229 End If;
230 Close C_Sel1;
231 --
232 -- Call the corresponding datetrack api
233 --
234 dt_api.find_dt_del_modes
235 (p_effective_date => p_effective_date
236 ,p_base_table_name => 'pay_ie_paye_details_f'
237 ,p_base_key_column => 'paye_details_id'
238 ,p_base_key_value => p_base_key_value
242 ,p_zap => p_zap
239 ,p_parent_table_name1 => 'per_all_assignments_f'
240 ,p_parent_key_column1 => 'assignment_id'
241 ,p_parent_key_value1 => l_parent_key_value1
243 ,p_delete => p_delete
244 ,p_future_change => p_future_change
245 ,p_delete_next_change => p_delete_next_change
246 );
247 --
248 hr_utility.set_location(' Leaving:'||l_proc, 10);
249 End find_dt_del_modes;
250 --
251 -- ----------------------------------------------------------------------------
252 -- |-----------------------< upd_effective_end_date >-------------------------|
253 -- ----------------------------------------------------------------------------
254 Procedure upd_effective_end_date
255 (p_effective_date in date
256 ,p_base_key_value in number
257 ,p_new_effective_end_date in date
258 ,p_validation_start_date in date
259 ,p_validation_end_date in date
260 ,p_object_version_number out nocopy number
261 ) is
262 --
263 l_proc varchar2(72) := g_package||'upd_effective_end_date';
264 l_object_version_number number;
265 --
266 Begin
267 hr_utility.set_location('Entering:'||l_proc, 5);
268 --
269 -- Because we are updating a row we must get the next object
270 -- version number.
271 --
272 l_object_version_number :=
273 dt_api.get_object_version_number
274 (p_base_table_name => 'pay_ie_paye_details_f'
275 ,p_base_key_column => 'paye_details_id'
276 ,p_base_key_value => p_base_key_value
277 );
278 --
279 hr_utility.set_location(l_proc, 10);
280 --
281 --
282 -- Update the specified datetrack row setting the effective
283 -- end date to the specified new effective end date.
284 --
285 update pay_ie_paye_details_f t
286 set t.effective_end_date = p_new_effective_end_date
287 , t.object_version_number = l_object_version_number
288 where t.paye_details_id = p_base_key_value
289 and p_effective_date
290 between t.effective_start_date and t.effective_end_date;
291 --
292 --
293 p_object_version_number := l_object_version_number;
294 hr_utility.set_location(' Leaving:'||l_proc, 15);
295 --
296 End upd_effective_end_date;
297 --
298 -- ----------------------------------------------------------------------------
299 -- |---------------------------------< lck >----------------------------------|
300 -- ----------------------------------------------------------------------------
301 Procedure lck
302 (p_effective_date in date
303 ,p_datetrack_mode in varchar2
304 ,p_paye_details_id in number
305 ,p_object_version_number in number
306 ,p_validation_start_date out nocopy date
307 ,p_validation_end_date out nocopy date
308 ) is
309 --
310 l_proc varchar2(72) := g_package||'lck';
311 l_validation_start_date date;
312 l_validation_end_date date;
313 l_argument varchar2(30);
314 --
315 -- Cursor C_Sel1 selects the current locked row as of session date
316 -- ensuring that the object version numbers match.
317 --
318 Cursor C_Sel1 is
319 select
320 paye_details_id
321 ,effective_start_date
322 ,effective_end_date
323 ,object_version_number
324 ,assignment_id
325 ,info_source
326 ,comm_period_no
327 ,tax_basis
328 ,certificate_start_date
329 ,certificate_end_date
330 ,tax_assess_basis
331 ,weekly_tax_credit
332 ,weekly_std_rate_cut_off
333 ,monthly_tax_credit
334 ,monthly_std_rate_cut_off
335 ,request_id
336 ,program_application_id
337 ,program_id
338 ,program_update_date
339 ,certificate_issue_date
340 --13359423
341 ,yrly_tax_cred
342 ,yrly_tax_rate_1
343 ,yrly_tax_rate_2
344 ,mthly_tax_rate_2
345 ,wkly_tax_rate_2
346 ,tax_rate_3
347 ,yrly_tax_rate_3
348 ,mthly_tax_rate_3
349 ,wkly_tax_rate_3
350 ,tax_rate_4
351 ,yrly_tax_rate_4
352 ,mthly_tax_rate_4
353 ,wkly_tax_rate_4
354 ,tax_rate_5
355 ,in_exempt_usc
356 ,total_usc_pay_todate
357 ,total_usc_tax_todate
358 ,usc_rate_1
359 ,usc_yrly_cutoff_1
360 ,usc_mthly_cutoff_1
361 ,usc_wkly_cutoff_1
362 ,usc_rate_2
363 ,usc_yrly_cutoff_2
364 ,usc_mthly_cutoff_2
365 ,usc_wkly_cutoff_2
366 ,usc_rate_3
367 ,usc_yrly_cutoff_3
368 ,usc_mthly_cutoff_3
369 ,usc_wkly_cutoff_3
370 ,usc_rate_4
371 ,usc_yrly_cutoff_4
372 ,usc_mthly_cutoff_4
373 ,usc_wkly_cutoff_4
374 ,usc_rate_5
375 ,usc_tax_basis
376 ,usc_info_source
377 --13359423
378 from pay_ie_paye_details_f
379 where paye_details_id = p_paye_details_id
380 and p_effective_date
381 between effective_start_date and effective_end_date
382 for update nowait;
383 --
384 --
385 --
386 Begin
387 hr_utility.set_location('Entering:'||l_proc, 5);
388 --
389 -- Ensure that all the mandatory arguments are not null
390 --
391 hr_api.mandatory_arg_error(p_api_name => l_proc
392 ,p_argument => 'effective_date'
393 ,p_argument_value => p_effective_date
394 );
395 --
396 hr_api.mandatory_arg_error(p_api_name => l_proc
400 --
397 ,p_argument => 'datetrack_mode'
398 ,p_argument_value => p_datetrack_mode
399 );
401 hr_api.mandatory_arg_error(p_api_name => l_proc
402 ,p_argument => 'paye_details_id'
403 ,p_argument_value => p_paye_details_id
404 );
405 --
406 hr_api.mandatory_arg_error(p_api_name => l_proc
407 ,p_argument => 'object_version_number'
408 ,p_argument_value => p_object_version_number
409 );
410 --
411 -- Check to ensure the datetrack mode is not INSERT.
412 --
413 If (p_datetrack_mode <> hr_api.g_insert) then
414 --
415 -- We must select and lock the current row.
416 --
417 Open C_Sel1;
418 Fetch C_Sel1 Into pay_ipd_shd.g_old_rec;
419 If C_Sel1%notfound then
420 Close C_Sel1;
421 --
422 -- The primary key is invalid therefore we must error
423 --
424 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
425 fnd_message.raise_error;
426 End If;
427 Close C_Sel1;
428 If (p_object_version_number
429 <> pay_ipd_shd.g_old_rec.object_version_number) Then
430 fnd_message.set_name('PAY', 'HR_7155_OBJECT_INVALID');
431 fnd_message.raise_error;
432 End If;
433 --
434 --
435 -- Validate the datetrack mode mode getting the validation start
436 -- and end dates for the specified datetrack operation.
437 --
438 dt_api.validate_dt_mode
439 (p_effective_date => p_effective_date
440 ,p_datetrack_mode => p_datetrack_mode
441 ,p_base_table_name => 'pay_ie_paye_details_f'
442 ,p_base_key_column => 'paye_details_id'
443 ,p_base_key_value => p_paye_details_id
444 ,p_parent_table_name1 => 'per_all_assignments_f'
445 ,p_parent_key_column1 => 'assignment_id'
446 ,p_parent_key_value1 => pay_ipd_shd.g_old_rec.assignment_id
447 ,p_enforce_foreign_locking => true
448 ,p_validation_start_date => l_validation_start_date
449 ,p_validation_end_date => l_validation_end_date
450 );
451 Else
452 --
453 -- We are doing a datetrack 'INSERT' which is illegal within this
454 -- procedure therefore we must error (note: to lck on insert the
455 -- private procedure ins_lck should be called).
456 --
457 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
458 fnd_message.set_token('PROCEDURE', l_proc);
459 fnd_message.set_token('STEP','20');
460 fnd_message.raise_error;
461 End If;
462 --
463 -- Set the validation start and end date OUT arguments
464 --
465 p_validation_start_date := l_validation_start_date;
466 p_validation_end_date := l_validation_end_date;
467 --
468 hr_utility.set_location(' Leaving:'||l_proc, 30);
469 --
470 -- We need to trap the ORA LOCK exception
471 --
472 Exception
473 When HR_Api.Object_Locked then
474 --
475 -- The object is locked therefore we need to supply a meaningful
476 -- error message.
477 --
478 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
479 fnd_message.set_token('TABLE_NAME', 'pay_ie_paye_details_f');
480 fnd_message.raise_error;
481 End lck;
482 --
483 -- ----------------------------------------------------------------------------
484 -- |-----------------------------< convert_args >-----------------------------|
485 -- ----------------------------------------------------------------------------
486 Function convert_args
487 (p_paye_details_id in number
488 ,p_effective_start_date in date
489 ,p_effective_end_date in date
490 ,p_object_version_number in number
491 ,p_assignment_id in number
492 ,p_info_source in varchar2
493 ,p_comm_period_no in number
494 ,p_tax_basis in varchar2
495 ,p_certificate_start_date in date
496 ,p_certificate_end_date in date
497 ,p_tax_assess_basis in varchar2
498 ,p_weekly_tax_credit in number
499 ,p_weekly_std_rate_cut_off in number
500 ,p_monthly_tax_credit in number
501 ,p_monthly_std_rate_cut_off in number
502 ,p_request_id in number
503 ,p_program_application_id in number
504 ,p_program_id in number
505 ,p_program_update_date in date
506 ,p_certificate_issue_date in date
507 --13359423
508 ,p_yrly_tax_cred in NUMBER
509 ,p_yrly_tax_rate_1 in number
510 ,p_yrly_tax_rate_2 in number
511 ,p_mthly_tax_rate_2 in number
512 ,p_wkly_tax_rate_2 in number
513 ,p_tax_rate_3 in number
514 ,p_yrly_tax_rate_3 in number
515 ,p_mthly_tax_rate_3 in number
516 ,p_wkly_tax_rate_3 in number
517 ,p_tax_rate_4 in number
518 ,p_yrly_tax_rate_4 in number
519 ,p_mthly_tax_rate_4 in number
520 ,p_wkly_tax_rate_4 in number
521 ,p_tax_rate_5 in number
522 ,p_in_exempt_usc in varchar2
523 ,p_total_usc_pay_todate in number
524 ,p_total_usc_tax_todate in number
525 ,p_usc_rate_1 in number
526 ,p_usc_yrly_cutoff_1 in number
527 ,p_usc_mthly_cutoff_1 in number
528 ,p_usc_wkly_cutoff_1 in number
529 ,p_usc_rate_2 in number
530 ,p_usc_yrly_cutoff_2 in number
531 ,p_usc_mthly_cutoff_2 in number
532 ,p_usc_wkly_cutoff_2 in number
533 ,p_usc_rate_3 in number
537 ,p_usc_rate_4 in number
534 ,p_usc_yrly_cutoff_3 in number
535 ,p_usc_mthly_cutoff_3 in number
536 ,p_usc_wkly_cutoff_3 in number
538 ,p_usc_yrly_cutoff_4 in number
539 ,p_usc_mthly_cutoff_4 in number
540 ,p_usc_wkly_cutoff_4 in number
541 ,p_usc_rate_5 in number
542 ,p_usc_tax_basis in varchar2
543 ,p_usc_info_source in varchar2
544 --13359423
545 )
546 Return g_rec_type is
547 --
548 l_rec g_rec_type;
549 --
550 Begin
551 --
552 -- Convert arguments into local l_rec structure.
553 --
554 l_rec.paye_details_id := p_paye_details_id;
555 l_rec.effective_start_date := p_effective_start_date;
556 l_rec.effective_end_date := p_effective_end_date;
557 l_rec.object_version_number := p_object_version_number;
558 l_rec.assignment_id := p_assignment_id;
559 l_rec.info_source := p_info_source;
560 l_rec.comm_period_no := p_comm_period_no;
561 l_rec.tax_basis := p_tax_basis;
562 l_rec.certificate_start_date := p_certificate_start_date;
563 l_rec.certificate_end_date := p_certificate_end_date;
564 l_rec.tax_assess_basis := p_tax_assess_basis;
565 l_rec.weekly_tax_credit := p_weekly_tax_credit;
566 l_rec.weekly_std_rate_cut_off := p_weekly_std_rate_cut_off;
567 l_rec.monthly_tax_credit := p_monthly_tax_credit;
568 l_rec.monthly_std_rate_cut_off := p_monthly_std_rate_cut_off;
569 l_rec.request_id := p_request_id;
570 l_rec.program_application_id := p_program_application_id;
571 l_rec.program_id := p_program_id;
572 l_rec.program_update_date := p_program_update_date;
573 l_rec.certificate_issue_date := p_certificate_issue_date;
574 --13359423
575 l_rec.yrly_tax_cred := p_yrly_tax_cred;
576 l_rec.yrly_tax_rate_1 := p_yrly_tax_rate_1;
577 l_rec.yrly_tax_rate_2 := p_yrly_tax_rate_2;
578 l_rec.mthly_tax_rate_2 := p_mthly_tax_rate_2;
579 l_rec.wkly_tax_rate_2 := p_wkly_tax_rate_2;
580 l_rec.tax_rate_3 := p_tax_rate_3;
581 l_rec.yrly_tax_rate_3 := p_yrly_tax_rate_3;
582 l_rec.mthly_tax_rate_3 := p_mthly_tax_rate_3;
583 l_rec.wkly_tax_rate_3 := p_wkly_tax_rate_3;
584 l_rec.tax_rate_4 := p_tax_rate_4;
585 l_rec.yrly_tax_rate_4 := p_yrly_tax_rate_4;
586 l_rec.mthly_tax_rate_4 := p_mthly_tax_rate_4;
587 l_rec.wkly_tax_rate_4 := p_wkly_tax_rate_4;
588 l_rec.tax_rate_5 := p_tax_rate_5;
589 l_rec.in_exempt_usc := p_in_exempt_usc;
590 l_rec.total_usc_pay_todate := p_total_usc_pay_todate;
591 l_rec.total_usc_tax_todate := p_total_usc_tax_todate;
592 l_rec.usc_rate_1 := p_usc_rate_1;
593 l_rec.usc_yrly_cutoff_1 := p_usc_yrly_cutoff_1;
594 l_rec.usc_mthly_cutoff_1 := p_usc_mthly_cutoff_1;
595 l_rec.usc_wkly_cutoff_1 := p_usc_wkly_cutoff_1;
596 l_rec.usc_rate_2 := p_usc_rate_2;
597 l_rec.usc_yrly_cutoff_2 := p_usc_yrly_cutoff_2;
598 l_rec.usc_mthly_cutoff_2 := p_usc_mthly_cutoff_2;
599 l_rec.usc_wkly_cutoff_2 := p_usc_wkly_cutoff_2;
600 l_rec.usc_rate_3 := p_usc_rate_3;
601 l_rec.usc_yrly_cutoff_3 := p_usc_yrly_cutoff_3;
602 l_rec.usc_mthly_cutoff_3 := p_usc_mthly_cutoff_3;
603 l_rec.usc_wkly_cutoff_3 := p_usc_wkly_cutoff_3;
604 l_rec.usc_rate_4 := p_usc_rate_4;
605 l_rec.usc_yrly_cutoff_4 := p_usc_yrly_cutoff_4;
606 l_rec.usc_mthly_cutoff_4 := p_usc_mthly_cutoff_4;
607 l_rec.usc_wkly_cutoff_4 := p_usc_wkly_cutoff_4;
608 l_rec.usc_rate_5 := p_usc_rate_5;
609 l_rec.usc_tax_basis := p_usc_tax_basis;
610 l_rec.usc_info_source := p_usc_info_source;
611 --13359423
612 --
613 -- Return the plsql record structure.
614 --
615 Return(l_rec);
616 --
617 End convert_args;
618 --
619 end pay_ipd_shd;