[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_PAYE_PKG
Source
1 package body PAY_IE_PAYE_PKG as
2 /* $Header: pyietax.pkb 120.10 2008/06/02 14:00:31 amakrish noship $ */
3 /*
4 **
5 ** Copyright (C) 1999 Oracle Corporation
6 ** All Rights Reserved
7 **
8 ** IE PAYE package
9 **
10 ** Change List
11 ** ===========
12 **
13 ** Date Author Reference Description
14 ** -----------+--------+---------+-------------
15 ** 20 JUN 2001 jmhatre N/A Created
16 ** 20 SEP 2001 jmhatre N/A Added social benefits suff
17 ** 18 OCT 2001 abhaduri N/A Changes due to SOE form requirement,
18 added out parameters p_assess_basis
19 and p_certificate_issue_date to
20 function get_paye_details to be fed
21 to PAYE details
22 ** 05 DEC 2001 gpadmasa N/A Added dbdrv Commands
23 ** 11 FEB 2002 abhaduri N/A Added input parameters Reduced Tax
24 Credit, Reduced Std Rate Cut Off and
25 Benefit amount for P45 data archiving
26 and display for get_paye_details.
27 ** 26 JUN 2002 abhaduri N/A Added function get_calculated_period_values
28 for calculating tax credits and cut offs
29 according to user entered values and
30 period types.
31 ** 09-DEC-2002 smrobins N/A Added function get_pps_number.
32 ** 16-MAY-2003 nsugavan 2943335 Added function Valid_Work_incidents and made
33 ** changes to existing social benefits cursor
34 ** to use element entry values instead of data
35 ** from table, pay_ie_social_benefits_f
36 ** 04-jul-2003 asengar 3030621 Added two procedures insert_element_entry
37 ** and update_element_entry.
38 ** 30-JUL-2003 asengar 3030616 Added four functions get_weekly_tax_credit
39 ** get_weekly_std_rate_cut_off,get_monthly_tax_credit
40 ** get_monthly_std_rate_cut_off to be called by
41 ** view pay_ie_paye_details_v.
42 ** 09-FEB-2005 aashokan 4080773 Added a new procedure to create record in new tax record
43 ** if pay frequency is changed.
44 ** 10-Feb-2005 vikgupta 4080773 Modified the proc update_paye_change_freq (included
45 ** P_DATETRACK_UPDATE_MODE)
46 ** 11-Feb-2005 vikgupta 4080773 Modified the proc update_paye_change_freq
47 ** 22-Feb-2005 skhandwa 4080773 Modified the proc update_paye_change_freq
48 ** included check if no current or future record
49 ** exists
50 ** 22-Feb-2005 skhandwa 4080773 Modified the proc update_paye_change_freq
51 ** For Cumulative, set nonapplicable
52 ** credit and cut-off values to null
53 ** 22-Feb-2005 skhandwa 4080773 Modified the proc update_paye_change_freq
54 ** Added global variable for old payroll
55 ** 22-Feb-2005 skhandwa 4080773 Modified the proc update_paye_change_freq
56 ** Changed p_effective_date for Correction cases.
57 ** 23-Feb-2005 skhandwa 4080773 Modified the proc update_paye_change_freq
58 ** Added assignment start date check for Correction cases .
59 ** 20-Apr-2005 alikhar 3227184 Changed cursor c_paye_dtl to use the payroll effective
60 ** date to fetch paye values from pay_ie_paye_details_f.
61 ** 26-Sep-2005 rrajaman 4619038 Added checks for new Tax Basis IE_EXEMPTION.
62 ** 04-Oct-2005 rrajaman 4561012 Added checks for IE_WEEK1_MONTH1.
63 ** 15-Dec-2005 vikgupta 4878630 Modified the update_paye_change_freq proc
64 ** for tax credit upload process.
65 ** 04-Jan-2006 vikgupta 4926302 added info source as IE_ELECTRONCI and asess
66 ** basis 'IE_SEP_TREAT' for Tax Credit upload
67 ** in update_paye_change_freq proc.
68 ** 01-Mar-2006 rbhardwa 5070091 Made changes to accomodate offset payrolls.
69 ** 19-Sep-2006 MGettins 5472781 Added a check to see if legislation
70 ** has been installed, as part
71 ** of the fix for GSI bug 5472781.
72 ** 19-Feb-2007 vikgupta SR 17140460.6, change the parameter passed to
73 ** update_paye_change_freq
74 ** 09-Apr-2007 rbhardwa 5867343 Modified code to include new functions get_paye_tax_basis,
75 ** get_diff_tax_basis and get_ie_exclude_tax_basis.
76 ** 05-May-2008 knadhan 6929566 Replaced p_effective_date with new parameter p_cert_date,
77 -------------------------------------------------------------------------------
78 */
79 g_package varchar2(33) := 'pay_ie_paye.';
80 g_old_payroll_id per_all_assignments_f.payroll_id%TYPE; --added for update_paye_change_freq
81 /* Added cursor for Bug 3030621 */
82 cursor g_absence_dates (c_element_entry_id number) is
83 SELECT pev.SCREEN_ENTRY_VALUE
84 FROM pay_element_entries_f pee, pay_element_links_f pel, pay_element_types_f pet,pay_element_entry_values_f pev,
85 pay_input_values_f piv
86 WHERE pee.element_link_id = pel.element_link_id
87 AND pet.element_type_id = pel.element_type_id
88 AND pet.element_name = 'IE Social Benefit Option 2'
89 AND pee.element_entry_id = c_element_entry_id
90 AND pet.element_type_id = piv.element_type_id
91 AND piv.legislation_code='IE'
92 AND piv.name in ('Absence Start Date','Absence End Date')
93 AND piv.element_type_id = pel.element_type_id
94 AND piv.input_value_id=pev.input_value_id
95 AND pev.element_entry_id = c_element_entry_id
96 ORDER by piv.name desc;
97
98
99
100 Function get_paye_tax_basis(p_assignment_id in number /* 5867343 */
101 ,p_payroll_action_id in number
102 ,p_tax_basis out nocopy varchar2)
103 return number is
104
105 --Local vriables-----
106
107 l_proc varchar2(72) := g_package||'get_paye_details';
108
109
110 -- cursor to fetch tax basis
111 cursor c_paye_tax_basis is select tax_basis
112 from pay_ie_paye_details_f pipd
113 ,pay_payroll_actions ppa
114 ,per_time_periods ptp
115 where pipd.assignment_id = p_assignment_id
116 and ppa.payroll_action_id = p_payroll_action_id
117 and ppa.effective_date between pipd.effective_start_date and --Bug Fix 3227184
118 nvl(pipd.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
119 and pipd.info_source in ('IE_P45','IE_ELECTRONIC','IE_CERT_TAX_CREDITS','IE_NONE_PROVIDED');
120
121
122 procedure initialise is
123 begin
124 p_tax_basis:='zzzz' ;
125 end;
126
127 begin
128
129 hr_utility.set_location('Entering:'||l_proc, 5);
130 open c_paye_tax_basis;
131
132 fetch c_paye_tax_basis into p_tax_basis;
133
134 if c_paye_tax_basis%notfound then
135 initialise;
136 close c_paye_tax_basis;
137 return 0;
138 end if;
139
140 close c_paye_tax_basis;
141 hr_utility.set_location('Leaving:'||l_proc, 30);
142 return 1;
143
144 exception when others then
145 initialise;
146 close c_paye_tax_basis;
147 raise_application_error(-20001,l_proc||'- '||sqlerrm);
148 return 0;
149
150 end get_paye_tax_basis; /* 5867343 */
151
152 Function get_diff_tax_basis(p_assignment_id in number /* 5867343 */
153 ,p_payroll_id in number
154 ,p_date_earned in date)
155 return number is
156
157 --Local vriables-----
158
159 l_proc varchar2(72) := g_package||'get_diff_tax_basis';
160 l_sec_assignment number;
161
162 -- Cursor to check whether multiple assignment has a different tax basis
163 CURSOR chk_multi_asgn_tax_basis IS
164 SELECT 1
165 FROM per_all_assignments_f paaf
166 ,per_time_periods ptp
167 ,pay_ie_paye_details_f pipd
168 WHERE paaf.person_id = ( SELECT distinct person_id FROM per_all_assignments_f WHERE assignment_id = p_assignment_id )
169 AND paaf.assignment_id <> p_assignment_id
170 AND pipd.assignment_id(+) = paaf.assignment_id
171 AND nvl(pipd.tax_basis,'X') <> 'IE_EXCLUDE'
172 AND p_date_earned BETWEEN ptp.start_date and ptp.end_date
173 AND ptp.payroll_id = p_payroll_id
174 AND paaf.effective_start_date <= ptp.end_date
175 AND paaf.effective_end_date >= ptp.start_date;
176
177 BEGIN
178
179 hr_utility.set_location('Entering:'||l_proc, 5);
180
181 OPEN chk_multi_asgn_tax_basis;
182 FETCH chk_multi_asgn_tax_basis INTO l_sec_assignment;
183
184 IF chk_multi_asgn_tax_basis%NOTFOUND THEN
185 close chk_multi_asgn_tax_basis;
186 hr_utility.set_location('Leaving:'||l_proc, 30);
187 return 0;
188 ELSE
189 close chk_multi_asgn_tax_basis;
190 hr_utility.set_location('Leaving:'||l_proc, 31);
191 return 1;
192 END IF;
193
194 exception when others then
195 close chk_multi_asgn_tax_basis;
196 raise_application_error(-20001,l_proc||'- '||sqlerrm);
197 return 0;
198
199 end get_diff_tax_basis; /* 5867343 */
200
201
202 Function get_ie_exclude_tax_basis(p_assignment_id in number /* 5867343 */
203 ,p_payroll_id in number
204 ,p_date_earned in date)
205 return number is
206
207 --Local vriables-----
208
209 l_proc varchar2(72) := g_package||'get_ie_exclude_tax_basis';
210 l_sec_assignment number;
211
212 -- Cursor to check whether multiple assignment has a different tax basis
213 CURSOR chk_multi_asgn_tax_basis IS
214 SELECT 1
215 FROM per_all_assignments_f paaf
216 ,per_time_periods ptp
217 ,pay_ie_paye_details_f pipd
218 WHERE paaf.person_id = ( SELECT distinct person_id FROM per_all_assignments_f WHERE assignment_id = p_assignment_id )
219 AND paaf.assignment_id <> p_assignment_id
220 AND pipd.assignment_id(+) = paaf.assignment_id
221 AND nvl(pipd.tax_basis,'X') = 'IE_EXCLUDE'
222 AND p_date_earned BETWEEN ptp.start_date and ptp.end_date
223 AND ptp.payroll_id = p_payroll_id
224 AND paaf.effective_start_date <= ptp.end_date
225 AND paaf.effective_end_date >= ptp.start_date;
226
227 BEGIN
228
229 hr_utility.set_location('Entering:'||l_proc, 5);
230
231 OPEN chk_multi_asgn_tax_basis;
232 FETCH chk_multi_asgn_tax_basis INTO l_sec_assignment;
233
234 IF chk_multi_asgn_tax_basis%NOTFOUND THEN
235 close chk_multi_asgn_tax_basis;
236 hr_utility.set_location('Leaving:'||l_proc, 30);
237 return 0;
238 ELSE
239 close chk_multi_asgn_tax_basis;
240 hr_utility.set_location('Leaving:'||l_proc, 31);
241 return 1;
242 END IF;
243
244 exception when others then
245 close chk_multi_asgn_tax_basis;
246 raise_application_error(-20001,l_proc||'- '||sqlerrm);
247 return 0;
248
249 end get_ie_exclude_tax_basis; /* 5867343 */
250
251
252
253
254 Function get_paye_details(p_assignment_id in number
255 ,p_payroll_action_id in number
256 ,p_info_source out nocopy varchar2
257 ,p_tax_basis out nocopy varchar2
258 ,p_weekly_tax_credit out nocopy number
259 ,p_monthly_tax_credit out nocopy number
260 ,p_weekly_std_rate_cutoff out nocopy number
261 ,p_monthly_std_rate_cutoff out nocopy number
262 ,p_certificate_start_date out nocopy date
263 ,p_certificate_end_date out nocopy date
264 /*changes for SOE form requirements*/
265 ,p_assess_basis out nocopy varchar2
266 ,p_certificate_issue_date out nocopy date
267 /*parameters added for p45 archiving*/
268 ,p_reduced_tax_credit out nocopy number
269 ,p_reduced_std_rate_cutoff out nocopy number
270 ,p_benefit_amount out nocopy number)
271 /*************************************************/
272 return number is
273
274 --Local vriables-----
275
276 l_proc varchar2(72) := g_package||'get_paye_details';
277 l_payroll_id number;
278 l_date_earned date;
279 l_period_type varchar2(20);
280 l_soc_ben_rec pay_ie_social_benefits_f%rowtype;
281
282 -- added for getting calculated values as per period type
283 l_period_ind varchar2(3);
284 l_cal_reduced_tax_credit number;
285 l_cal_reduced_cut_off number;
286 --
287 -- Bug 2943335 - Added
288 total_benefit_amount number := 0;
289 l_benefit_amount number;
290
291 cursor c_paye_dtl is select ppa.payroll_id
292 ,ppa.date_earned
293 ,info_source
294 ,tax_basis
295 ,nvl(weekly_tax_credit,0)
296 ,nvl(monthly_tax_credit,0)
297 ,nvl(weekly_std_rate_cut_off,0)
298 ,nvl(monthly_std_rate_cut_off,0)
299 ,effective_start_date
300 ,nvl(pipd.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
301 /*changes for SOE form requirements*/
302 ,pipd.tax_assess_basis
303 ,nvl(pipd.certificate_issue_date,to_date('01-01-0001','DD-MM-YYYY'))
304 ,ptp.period_type
305 from pay_ie_paye_details_f pipd
306 ,pay_payroll_actions ppa
307 ,per_time_periods ptp
308 where pipd.assignment_id = p_assignment_id
309 and ppa.payroll_action_id = p_payroll_action_id
310 -- and ppa.date_earned between pipd.effective_start_date and
311 and ppa.effective_date between pipd.effective_start_date and --Bug Fix 3227184
312 nvl(pipd.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
313 and pipd.info_source in ('IE_P45','IE_ELECTRONIC','IE_CERT_TAX_CREDITS','IE_NONE_PROVIDED')
314 and ptp.payroll_id = ppa.payroll_id
315 and ppa.date_earned between ptp.start_date and ptp.end_date;
316 --
317 -- Bug 2943335 - commented code below to reference element entries table for data
318 --
319 /* cursor c_soc_ben(c_payroll_id number,c_date_earned date) is select calculation_option
320 ,nvl(reduced_tax_credit,0)
321 ,nvl(reduced_standard_cutoff,0)
322 ,nvl(benefit_amount,0)
323 from pay_ie_social_benefits_f psb,
324 per_time_periods ptp
325 where psb.absence_start_date between ptp.start_date and ptp.end_date
326 and ptp.payroll_id = c_payroll_id
327 and psb.assignment_id = p_assignment_id
328 and calculation_option not in('IE_OPTION0','IE_OPTION1')
329 and c_date_earned between ptp.start_date and ptp.end_date
330 order by psb.effective_start_date desc; */
331 -- SOC cahnges....
332 --
333 cursor cur_c_soc_ben
334 is
335 select NVL(SUM(TO_NUMBER(SCREEN_ENTRY_VALUE)),0)
336 from
337 PAY_INPUT_VALUES_F INPVAL,
338 PAY_ELEMENT_TYPES_F TYPE,
339 PAY_ELEMENT_LINKS_F LINK,
340 PAY_ELEMENT_ENTRY_VALUES_F VALUE,
341 PAY_ELEMENT_ENTRIES_F ENTRY,
342 PER_TIME_PERIODS PTP,
343 PAY_PAYROLL_ACTIONS PACT
344 -- ,FND_SESSIONS SESH
345 WHERE
346 PACT.PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID AND
347 --PTP.TIME_PERIOD_ID = PACT.TIME_PERIOD_ID AND -- Bug 5070091 Offset payroll change
348 PACT.PAYROLL_ID = PTP.PAYROLL_ID AND
349 PACT.DATE_EARNED BETWEEN PTP.START_DATE AND PTP.END_DATE AND
350 --SESH.SESSION_ID = USERENV ('sessionid') AND
351 TYPE.ELEMENT_NAME = 'IE Social Benefit Option 2' AND
352 -- SESH.EFFECTIVE_DATE BETWEEN TYPE.EFFECTIVE_START_DATE AND TYPE.EFFECTIVE_END_DATE AND
353 PACT.EFFECTIVE_DATE BETWEEN TYPE.EFFECTIVE_START_DATE AND TYPE.EFFECTIVE_END_DATE AND
354 TYPE.ELEMENT_TYPE_ID = LINK.ELEMENT_TYPE_ID AND
355 -- SESH.EFFECTIVE_DATE BETWEEN LINK.EFFECTIVE_START_DATE AND LINK.EFFECTIVE_END_DATE AND
356 PACT.EFFECTIVE_DATE BETWEEN LINK.EFFECTIVE_START_DATE AND LINK.EFFECTIVE_END_DATE AND
357 ENTRY.ELEMENT_LINK_ID = LINK.ELEMENT_LINK_ID AND
358 ENTRY.ASSIGNMENT_ID = P_ASSIGNMENT_ID AND
359 ENTRY.EFFECTIVE_START_DATE <= PTP.END_DATE AND
360 ENTRY.EFFECTIVE_END_DATE >= PTP.START_DATE AND
361 VALUE.ELEMENT_ENTRY_ID = ENTRY.ELEMENT_ENTRY_ID AND
362 VALUE.EFFECTIVE_START_DATE = ENTRY.EFFECTIVE_START_DATE AND
363 VALUE.EFFECTIVE_END_DATE = ENTRY.EFFECTIVE_END_DATE AND
364 INPVAL.INPUT_VALUE_ID = VALUE.INPUT_VALUE_ID AND
365 INPVAL.NAME = 'Taxable Benefit Amount' AND
366 -- SESH.EFFECTIVE_DATE BETWEEN INPVAL.EFFECTIVE_START_DATE AND INPVAL.EFFECTIVE_END_DATE;
367 PACT.EFFECTIVE_DATE BETWEEN INPVAL.EFFECTIVE_START_DATE AND INPVAL.EFFECTIVE_END_DATE;
368
369 procedure initialise is
370 begin
371 p_info_source:='zzzz' ;
372 p_tax_basis:='zzzz' ;
373 p_weekly_tax_credit:=0;
374 p_monthly_tax_credit:=0;
375 p_weekly_std_rate_cutoff:=0;
376 p_monthly_std_rate_cutoff:=0;
377 p_certificate_start_date:=to_date('01-01-0001','DD-MM-YYYY');
378 p_certificate_end_date:=to_date('01-01-0001','DD-MM-YYYY');
379 /********************************/
380 p_reduced_tax_credit:=0;
381 p_reduced_std_rate_cutoff:=0;
382 p_benefit_amount:=0;
383 /**************************/
384 end;
385
386 --end Local vriables---------
387
388 begin
389
390 hr_utility.set_location('Entering:'||l_proc, 5);
391 /********************************/
392 p_reduced_tax_credit:=0;
393 p_reduced_std_rate_cutoff:=0;
394 p_benefit_amount:=0;
395 /**************************/
396 open c_paye_dtl;
397
398 fetch c_paye_dtl into l_payroll_id
399 ,l_date_earned
400 ,p_info_source
401 ,p_tax_basis
402 ,p_weekly_tax_credit
403 ,p_monthly_tax_credit
404 ,p_weekly_std_rate_cutoff
405 ,p_monthly_std_rate_cutoff
406 ,p_certificate_start_date
407 ,p_certificate_end_date
408 /*changes for SOE form requirements*/
409 ,p_assess_basis
410 ,p_certificate_issue_date
411 ,l_period_type;
412
413 if c_paye_dtl%notfound then
414 initialise;
415 return 0;
416 end if;
417
418 /* Getting calculated values as per the period type*/
419 if (l_period_type ='Week'
420 or l_period_type ='Bi-Week'
421 or l_period_type='Lunar Month')
422 then
423 l_period_ind := 'W';
424 p_weekly_tax_credit := get_calculated_period_values(l_period_type,
425 l_period_ind,
426 p_weekly_tax_credit);
427 p_weekly_std_rate_cutoff := get_calculated_period_values(l_period_type,
428 l_period_ind,
429 p_weekly_std_rate_cutoff);
430
431 elsif (l_period_type ='Bi-Month' or
432 l_period_type ='Calendar Month' or
433 l_period_type='Quarter' or
434 l_period_type = 'Semi-Month' or
435 l_period_type = 'Semi-Year' or
436 l_period_type ='Year')
437 then
438 l_period_ind :='M';
439 p_monthly_tax_credit := get_calculated_period_values(l_period_type,
440 l_period_ind,
441 p_monthly_tax_credit);
442 p_monthly_std_rate_cutoff := get_calculated_period_values(l_period_type,
443 l_period_ind,
444 p_monthly_std_rate_cutoff);
445
446 end if;
447
448 -- Bug 2943335 - commented code below to reference element entries table for data
449 /*Social Benefits stuff*/
450 /* open c_soc_ben(l_payroll_id,l_date_earned);
451 fetch c_soc_ben into l_soc_ben_rec.calculation_option
452 ,l_soc_ben_rec.reduced_tax_credit
453 ,l_soc_ben_rec.reduced_standard_cutoff
454 ,l_soc_ben_rec.benefit_amount;
455 if c_soc_ben%found then
456
457 -- getting calculated values according to the period
458 l_cal_reduced_tax_credit := get_calculated_period_values(l_period_type,l_period_ind,l_soc_ben_rec.reduced_tax_credit);
459 l_cal_reduced_cut_off:= get_calculated_period_values(l_period_type,l_period_ind,l_soc_ben_rec.reduced_standard_cutoff);
460
461 if l_soc_ben_rec.calculation_option = 'IE_OPTION1' then
462 --
463 p_benefit_amount:=l_soc_ben_rec.benefit_amount;
464 --
465 elsif l_soc_ben_rec.calculation_option = 'IE_OPTION2' then
466 --
467 if l_period_ind = 'W'
468 then
469 p_reduced_tax_credit:=p_weekly_tax_credit - l_cal_reduced_tax_credit;
470 p_reduced_std_rate_cutoff:=p_weekly_std_rate_cutoff - l_cal_reduced_cut_off;
471 elsif l_period_ind = 'M'
472 then
473 p_reduced_tax_credit:=p_monthly_tax_credit - l_cal_reduced_tax_credit;
474 p_reduced_std_rate_cutoff:=p_monthly_std_rate_cutoff - l_cal_reduced_cut_off;
475 end if;
476
477 p_benefit_amount:=l_soc_ben_rec.benefit_amount;
478 p_weekly_tax_credit:= l_cal_reduced_tax_credit;
479 p_monthly_tax_credit:= l_cal_reduced_tax_credit;
480 p_weekly_std_rate_cutoff:= l_cal_reduced_cut_off;
481 p_monthly_std_rate_cutoff:= l_cal_reduced_cut_off;
482 --
483 elsif l_soc_ben_rec.calculation_option = 'IE_OPTION3' then
484 --
485 if (l_period_ind='W')
486 then
487 p_reduced_tax_credit:=p_weekly_tax_credit - l_cal_reduced_tax_credit;
488 p_reduced_std_rate_cutoff:=p_weekly_std_rate_cutoff - l_cal_reduced_cut_off;
489 elsif l_period_ind='M'
490 then
491 p_reduced_tax_credit:=p_monthly_tax_credit - l_cal_reduced_tax_credit;
492 p_reduced_std_rate_cutoff:=p_monthly_std_rate_cutoff - l_cal_reduced_cut_off;
493 end if;
494
495 p_benefit_amount:=l_soc_ben_rec.benefit_amount;
496 p_weekly_tax_credit:= l_cal_reduced_tax_credit;
497 p_monthly_tax_credit:= l_cal_reduced_tax_credit;
498 p_weekly_std_rate_cutoff:= l_cal_reduced_cut_off;
499 p_monthly_std_rate_cutoff:= l_cal_reduced_cut_off;
500 p_tax_basis:='IE_WEEK1_MONTH1';
501 --
502 elsif l_soc_ben_rec.calculation_option = 'IE_OPTION4' then
503 --
504 p_tax_basis:='IE_WEEK1_MONTH1';
505 p_benefit_amount:=l_soc_ben_rec.benefit_amount;
506 --
507 end if;
508 end if;
509
510 close c_soc_ben; */
511 --
512 -- Bug 2943335 - Fetch the sum of benefit amount am employee has in this period
513
514 open cur_c_soc_ben;
515 fetch cur_c_soc_ben into total_benefit_amount;
516 close cur_c_soc_ben;
517 -- @D:/Comm/IE/Social_ben/pyietax.pkb
518 p_benefit_amount := nvl(total_benefit_amount,0);
519 hr_utility.set_location('benefit amt:'||p_benefit_amount, 15);
520 hr_utility.set_location('p_monthly_tax_credit: '||p_monthly_tax_credit, 25);
521 hr_utility.set_location('p_monthly_std_rate_cutoff:'||p_monthly_std_rate_cutoff, 35);
522
523 close c_paye_dtl;
524 hr_utility.set_location('Leaving:'||l_proc, 30);
525 return 1;
526
527 exception when others then
528 initialise;
529 close c_paye_dtl;
530 raise_application_error(-20001,l_proc||'- '||sqlerrm);
531 return 0;
532
533 end get_paye_details;
534
535 Function get_payroll_details( p_payroll_id in number
536 ,p_payroll_action_id in number
537 ,p_period_num out nocopy number
538 ,p_payroll_type out nocopy varchar2) return number is
539
540 cursor c_payroll_details is select ptp.period_num
541 ,ptp.period_type
542 from per_time_periods ptp,
543 pay_all_payrolls pap,
544 pay_payroll_actions ppa
545 where pap.payroll_id = ptp.payroll_id
546 and pap.payroll_id=p_payroll_id
547 and ppa.payroll_id=pap.payroll_id
548 and ppa.payroll_action_id=p_payroll_action_id
549 and ppa.date_earned between ptp.start_date and ptp.end_date;
550
551 l_proc varchar2(72) := g_package||'get_payroll_details';
552
553 begin
554
555 hr_utility.set_location('Entering:'||l_proc, 35);
556
557 open c_payroll_details;
558 fetch c_payroll_details into p_period_num
559 ,p_payroll_type;
560 close c_payroll_details;
561
562 hr_utility.set_location('Leaving:'||l_proc, 50);
563
564 return 1;
565 exception when others then
566 return 0;
567
568 end;
569 --
570 FUNCTION get_calculated_period_values(p_period_type IN VARCHAR2,
571 p_period_ind IN VARCHAR2,
572 p_actual_value IN NUMBER) RETURN NUMBER IS
573
574 l_calculated_value NUMBER;
575 l_number_per_year NUMBER;
576
577 CURSOR csr_number_per_year IS
578 SELECT number_per_fiscal_year
579 FROM per_time_period_types
580 WHERE period_type =p_period_type;
581
582 BEGIN
583
584 OPEN csr_number_per_year;
585 FETCH csr_number_per_year INTO l_number_per_year;
586 CLOSE csr_number_per_year;
587
588 IF p_period_ind = 'M' THEN
589 l_calculated_value := p_actual_value * 12/l_number_per_year;
590
591 ELSIF p_period_ind='W' THEN
592 l_calculated_value := p_actual_value * 52/l_number_per_year;
593
594 END IF;
595
596 RETURN l_calculated_value;
597
598 END get_calculated_period_values;
599 --
600 --
601 Function get_pps_number(p_assignment_id IN NUMBER,
602 p_payroll_action_id IN NUMBER) RETURN NUMBER IS
603 --
604 l_pps_number VARCHAR2(30);
605 l_tax_basis VARCHAR2(30);
606 l_func VARCHAR2(14):= 'get_pps_number';
607
608 Cursor csr_pps_number IS
609 SELECT nvl(pap.national_identifier, 'X')
610 FROM per_all_people_f pap
611 ,per_all_assignments_f paa
612 ,pay_payroll_actions ppa
613 WHERE ppa.payroll_action_id = p_payroll_action_id
614 and paa.assignment_id = p_assignment_id
615 and ppa.effective_date between paa.effective_start_date and paa.effective_end_date
616 and paa.person_id = pap.person_id
617 and ppa.effective_date between pap.effective_start_date and pap.effective_end_date;
618
619 Cursor csr_emer_no_pps_basis IS
620 SELECT nvl(pipd.tax_basis, 'X')
621 from pay_ie_paye_details_f pipd,
622 pay_payroll_actions ppa
623 WHERE ppa.payroll_action_id = p_payroll_action_id
624 and pipd.assignment_id = p_assignment_id
625 and ppa.effective_date between pipd.effective_start_date and pipd.effective_end_date;
626 --
627 Begin
628 hr_utility.set_location('Entering : '||l_func, 10);
629 OPEN csr_pps_number;
630 FETCH csr_pps_number into l_pps_number;
631 CLOSE csr_pps_number;
632 --
633 IF l_pps_number = 'X' then
634 hr_utility.set_location('In : '||l_func, 20);
635 RETURN 1;
636 ELSE
637 hr_utility.set_location('In : '||l_func, 30);
638 OPEN csr_emer_no_pps_basis;
639 FETCH csr_emer_no_pps_basis into l_tax_basis;
640 CLOSE csr_emer_no_pps_basis;
641 IF l_tax_basis IS NULL THEN
642 hr_utility.set_location('In : '||l_func, 35);
643 l_tax_basis := 'X';
644 END IF;
645 IF l_tax_basis <> 'IE_EMERGENCY_NO_PPS' THEN
646 hr_utility.set_location('In : '||l_func, 40);
647 RETURN 0;
648 ELSE
649 hr_utility.set_location('In : '||l_func, 50);
650 RETURN 1;
651 END IF;
652 hr_utility.set_location('In : '||l_func, 60);
653 END IF;
654 hr_utility.set_location('In : '||l_func, 70);
655 END get_pps_number;
656 --
657 -- Bug 2943335 added function to see if work incident exist for the person
658 -- This would return true if the work incident
659 -- entered on the element entry screen exists for the person
660 --
661 function Valid_Work_incidents
662 (p_assignment_id in number
663 ,p_date_earned in date
664 ,p_reference in varchar2) return varchar2 is
665 --
666 l_valid varchar2(10);
667 cursor csr_find_match is
668 select 'TRUE'
669 from per_all_assignments_f asg,
670 per_work_incidents pwi,
671 hr_lookups hl
672 where p_date_earned between asg.effective_start_date
673 and asg.effective_end_date and
674 p_assignment_id = asg.assignment_id
675 and pwi.PERSON_ID = asg.PERSON_ID
676 and hl.lookup_type = 'INCIDENT_TYPE'
677 and pwi.INCIDENT_TYPE = hl.lookup_code
678 and hl.meaning = p_reference ;
679 --
680 BEGIN
681 open csr_find_match;
682 fetch csr_find_match into l_valid;
683 if csr_find_match%NOTFOUND then
684 l_valid := 'FALSE';
685 end if;
686 close csr_find_match;
687 return l_valid;
688 END Valid_Work_incidents;
689
690 /* Added following two procedures as user hooks for BUG 3030621 */
691 procedure insert_element_entry
692 (p_element_entry_id in number
693 )is
694 l_procedure_name varchar2(61) := 'hr_ie_element_entry_hook.insert_element_name' ;
695 l_absence_start_date varchar2(30);
696 l_absence_end_date varchar2(30);
697 --
698 begin
699 --
700 -- Added for GSI Bug 5472781
701 --
702 IF hr_utility.chk_product_install('Oracle Human Resources', 'IE') THEN
703 --
704 open g_absence_dates (p_element_entry_id);
705 for i in 1..2 loop
706 if i=1 then
707 fetch g_absence_dates
708 into l_absence_start_date;
709 elsif i=2 then
710 fetch g_absence_dates
711 into l_absence_end_date;
712 end if;
713 end loop;
714 close g_absence_dates ;
715 hr_utility.trace('In: ' || l_procedure_name) ;
716 if l_absence_start_date is not null and l_absence_end_date is not null then
717 if FND_DATE.CANONICAL_TO_DATE(l_absence_start_date) > FND_DATE.CANONICAL_TO_DATE(l_absence_end_date) then
718 hr_utility.set_message(801,'HR_IE_SOCIAL_BENEFIT_DATES');
719 hr_utility.raise_error;
720 end if;
721 end if;
722 hr_utility.trace('Out: ' || l_procedure_name) ;
723 END IF;
724 end insert_element_entry ;
725 --
726 procedure update_element_entry
727 ( p_element_entry_id in number
728 ) is
729 l_procedure_name varchar2(61) := 'hr_ie_element_entry_hook.update_element_name' ;
730 l_absence_start_date varchar2(30);
731 l_absence_end_date varchar2(30);
732 begin
733 --
734 -- Added for GSI Bug 5472781
735 --
736 IF hr_utility.chk_product_install('Oracle Human Resources', 'IE') THEN
737 --
738 open g_absence_dates (p_element_entry_id);
739 for i in 1..2 loop
740 if i=1 then
741 fetch g_absence_dates
742 into l_absence_start_date;
743 elsif i=2 then
744 fetch g_absence_dates
745 into l_absence_end_date;
746 end if;
747 end loop;
748 close g_absence_dates ;
749 hr_utility.trace('In: ' || l_procedure_name) ;
750 if l_absence_start_date is not null and l_absence_end_date is not null then
751 if FND_DATE.CANONICAL_TO_DATE(l_absence_start_date) > FND_DATE.CANONICAL_TO_DATE(l_absence_end_date) then
752 hr_utility.set_message(801,'HR_IE_SOCIAL_BENEFIT_DATES');
753 hr_utility.raise_error;
754 end if;
755 end if;
756 hr_utility.trace('Out: ' || l_procedure_name) ;
757 END IF;
758 end update_element_entry ;
759 --
760 /* End of BUG 3030621 */
761 /*ADDED FOUR FUNCTIONS FOR BUG 3030616 */
762 --
763 function get_monthly_std_rate_cut_off
764 (p_assignment_id in pay_ie_paye_details_f.ASSIGNMENT_ID%TYPE,
765 p_tax_basis in pay_ie_paye_details_f.TAX_BASIS%TYPE)
766 RETURN number
767 is
768 CURSOR get_global_val(l_name IN VARCHAR2) IS
769 SELECT global_value
770 FROM ff_globals_f,fnd_sessions ses
771 WHERE global_name = l_name
772 AND ses.session_id = userenv('SESSIONID')
773 AND ses.effective_date BETWEEN effective_start_date AND effective_end_date;
774 --
775 CURSOR get_pay_frequency_csr IS
776 SELECT pp.period_type
777 FROM pay_all_payrolls_f pp, per_all_assignments_f pa,fnd_sessions ses
778 WHERE pa.assignment_id = p_assignment_id
779 AND ses.session_id = userenv('SESSIONID')
780 AND ses.effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
781 AND pp.payroll_id = pa.payroll_id
782 AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
783 --
784 CURSOR monthly_std_rate_cut_off IS
785 SELECT nvl(pp.monthly_std_rate_cut_off,0)
786 FROM pay_ie_paye_details_f pp,fnd_sessions ses
787 WHERE pp.assignment_id=p_assignment_id
788 AND ses.session_id = userenv('SESSIONID')
789 AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
790 --
791 get_pay_frequency_rec get_pay_frequency_csr%ROWTYPE;
792 v_monthly_std_rate_cut_off number;
793 BEGIN
794 --
795 OPEN get_pay_frequency_csr;
796 FETCH get_pay_frequency_csr INTO get_pay_frequency_rec;
797 CLOSE get_pay_frequency_csr;
798 --
799 IF p_tax_basis='IE_EMERGENCY' THEN
800 IF get_pay_frequency_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year') THEN
801 OPEN get_global_val('IE_MONTHLY_STANDARD_RATE_CUT_OFF');
802 FETCH get_global_val INTO v_monthly_std_rate_cut_off;
803 CLOSE get_global_val;
804 ELSE
805 v_monthly_std_rate_cut_off:= NULL;
806 --
807 END IF;
808 ELSIF p_tax_basis='IE_EMERGENCY_NO_PPS' THEN
809 v_monthly_std_rate_cut_off:= NULL;
810 ELSE
811 IF get_pay_frequency_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year')
812 THEN
813 OPEN monthly_std_rate_cut_off;
814 FETCH monthly_std_rate_cut_off INTO v_monthly_std_rate_cut_off;
815 CLOSE monthly_std_rate_cut_off;
816 ELSE
817 v_monthly_std_rate_cut_off:= NULL;
818 END IF;
819 END IF;
820 --
821 RETURN v_monthly_std_rate_cut_off;
822 --
823 END get_monthly_std_rate_cut_off;
824 --
825 function get_monthly_tax_credit
826 (p_assignment_id in pay_ie_paye_details_f.ASSIGNMENT_ID%TYPE,
827 p_tax_basis in pay_ie_paye_details_f.TAX_BASIS%TYPE)
828 RETURN number
829 is
830 CURSOR get_global_val(l_name IN VARCHAR2) IS
831 SELECT global_value
832 FROM ff_globals_f,fnd_sessions ses
833 WHERE global_name = l_name
834 AND ses.session_id = userenv('SESSIONID')
835 AND ses.effective_date BETWEEN effective_start_date AND effective_end_date;
836 --
837 CURSOR get_pay_frequency_csr IS
838 SELECT pp.period_type
839 FROM pay_all_payrolls_f pp, per_all_assignments_f pa,fnd_sessions ses
840 WHERE pa.assignment_id = p_assignment_id
841 AND ses.session_id = userenv('SESSIONID')
842 AND ses.effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
843 AND pp.payroll_id = pa.payroll_id
844 AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
845 --
846 CURSOR monthly_tax_credit IS
847 SELECT nvl(pp.monthly_tax_credit,0)
848 FROM pay_ie_paye_details_f pp,fnd_sessions ses
849 WHERE pp.assignment_id=p_assignment_id
850 AND ses.session_id = userenv('SESSIONID')
851 AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
852 --
853 get_pay_frequency_rec get_pay_frequency_csr%ROWTYPE;
854 v_get_monthly_tax_credit number;
855 --
856 BEGIN
857 --
858 OPEN get_pay_frequency_csr;
859 FETCH get_pay_frequency_csr INTO get_pay_frequency_rec;
860 CLOSE get_pay_frequency_csr;
861 --
862 IF p_tax_basis='IE_EMERGENCY' THEN
863 IF get_pay_frequency_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year') THEN
864 --
865 OPEN get_global_val('IE_MONTHLY_TAX_CREDIT');
866 FETCH get_global_val INTO v_get_monthly_tax_credit;
867 CLOSE get_global_val;
868 --
869 ELSE
870 v_get_monthly_tax_credit:= NULL;
871 --
872 END IF;
873 --
874 ELSIF p_tax_basis='IE_EMERGENCY_NO_PPS' THEN
875 v_get_monthly_tax_credit:= NULL;
876 --
877 ELSE
878 IF get_pay_frequency_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year')
879 THEN
880 OPEN monthly_tax_credit ;
881 FETCH monthly_tax_credit INTO v_get_monthly_tax_credit;
882 CLOSE monthly_tax_credit;
883 ELSE
884 v_get_monthly_tax_credit:= 0;
885 END IF;
886 END IF;
887 --
888 RETURN v_get_monthly_tax_credit;
889 --
890 END get_monthly_tax_credit;
891 --
892 function get_weekly_std_rate_cut_off
893 (p_assignment_id in pay_ie_paye_details_f.ASSIGNMENT_ID%TYPE,
894 p_tax_basis in pay_ie_paye_details_f.TAX_BASIS%TYPE)
895 RETURN number
896 is
897 CURSOR get_global_val(l_name IN VARCHAR2) IS
898 SELECT global_value
899 FROM ff_globals_f,fnd_sessions ses
900 WHERE global_name = l_name
901 AND ses.session_id = userenv('SESSIONID')
902 AND ses.effective_date BETWEEN effective_start_date AND effective_end_date;
903 --
904 CURSOR get_pay_frequency_csr IS
905 SELECT pp.period_type
906 FROM pay_all_payrolls_f pp, per_all_assignments_f pa,fnd_sessions ses
907 WHERE pa.assignment_id = p_assignment_id
908 AND ses.session_id = userenv('SESSIONID')
909 AND ses.effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
910 AND pp.payroll_id = pa.payroll_id
911 AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
912 --
913 CURSOR weekly_std_rate_cut_off IS
914 SELECT nvl(pp.weekly_std_rate_cut_off,0)
915 FROM pay_ie_paye_details_f pp,fnd_sessions ses
916 WHERE pp.assignment_id=p_assignment_id
917 AND ses.session_id = userenv('SESSIONID')
918 AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
919 --
920 get_pay_frequency_rec get_pay_frequency_csr%ROWTYPE;
921 v_weekly_std_rate_cut_off number;
922 BEGIN
923 --
924 OPEN get_pay_frequency_csr;
925 FETCH get_pay_frequency_csr INTO get_pay_frequency_rec;
926 CLOSE get_pay_frequency_csr;
927 --
928 IF p_tax_basis='IE_EMERGENCY' THEN
929 IF get_pay_frequency_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year') THEN
930 v_weekly_std_rate_cut_off:= NULL;
931 --
932 ELSE
933 OPEN get_global_val('IE_WEEKLY_STANDARD_RATE_CUT_OFF');
934 FETCH get_global_val INTO v_weekly_std_rate_cut_off;
935 CLOSE get_global_val;
936 --
937 END IF;
938 ELSIF p_tax_basis='IE_EMERGENCY_NO_PPS' THEN
939 v_weekly_std_rate_cut_off:= NULL;
940 ELSE
941 IF get_pay_frequency_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year')
942 THEN
943 v_weekly_std_rate_cut_off:= NULL;
944 ELSE
945 OPEN weekly_std_rate_cut_off;
946 FETCH weekly_std_rate_cut_off INTO v_weekly_std_rate_cut_off;
947 CLOSE weekly_std_rate_cut_off;
948 END IF;
949 END IF;
950 --
951 RETURN v_weekly_std_rate_cut_off;
952 --
953 END get_weekly_std_rate_cut_off;
954 --
955 function get_weekly_tax_credit
956 (p_assignment_id in pay_ie_paye_details_f.ASSIGNMENT_ID%TYPE,
957 p_tax_basis in pay_ie_paye_details_f.TAX_BASIS%TYPE)
958 RETURN number
959 is
960 CURSOR get_global_val(l_name IN VARCHAR2) IS
961 SELECT global_value
962 FROM ff_globals_f,fnd_sessions ses
963 WHERE global_name = l_name
964 AND ses.session_id = userenv('SESSIONID')
965 AND ses.effective_date BETWEEN effective_start_date AND effective_end_date;
966 --
967 CURSOR get_pay_frequency_csr IS
968 SELECT pp.period_type
969 FROM pay_all_payrolls_f pp, per_all_assignments_f pa,fnd_sessions ses
970 WHERE pa.assignment_id = p_assignment_id
971 AND ses.session_id = userenv('SESSIONID')
972 AND ses.effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
973 AND pp.payroll_id = pa.payroll_id
974 AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
975 --
976 CURSOR weekly_tax_credit IS
977 SELECT nvl(pp.weekly_tax_credit,0)
978 FROM pay_ie_paye_details_f pp,fnd_sessions ses
979 WHERE pp.assignment_id=p_assignment_id
980 AND ses.session_id = userenv('SESSIONID')
981 AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
982 --
983 get_pay_frequency_rec get_pay_frequency_csr%ROWTYPE;
984 v_get_weekly_tax_credit number;
985 BEGIN
986 --
987 OPEN get_pay_frequency_csr;
988 FETCH get_pay_frequency_csr INTO get_pay_frequency_rec;
989 CLOSE get_pay_frequency_csr;
990 --
991 IF p_tax_basis='IE_EMERGENCY' THEN
992 IF get_pay_frequency_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year') THEN
993 v_get_weekly_tax_credit:= NULL;
994 --
995 ELSE
996 OPEN get_global_val('IE_WEEKLY_TAX_CREDIT');
997 FETCH get_global_val INTO v_get_weekly_tax_credit;
998 CLOSE get_global_val;
999 END IF;
1000 ELSIF p_tax_basis='IE_EMERGENCY_NO_PPS' THEN
1001 v_get_weekly_tax_credit:= NULL;
1002 ELSE
1003 IF get_pay_frequency_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year')
1004 THEN
1005 v_get_weekly_tax_credit:= NULL;
1006 ELSE
1007 OPEN weekly_tax_credit ;
1008 FETCH weekly_tax_credit INTO v_get_weekly_tax_credit;
1009 CLOSE weekly_tax_credit;
1010 END IF;
1011 END IF;
1012 RETURN v_get_weekly_tax_credit;
1013 --
1014 END get_weekly_tax_credit;
1015 --
1016 /* End of BUG 3030616 */
1017
1018 /*-------------------- decode_value_char --------------------*/
1019 function decode_value_char(p_expression boolean,
1020 p_true varchar2,
1021 p_false varchar2) return varchar2 is
1022 begin
1023 if p_expression then
1024 return p_true;
1025 else
1026 return p_false;
1027 end if;
1028
1029 end decode_value_char;
1030
1031 /*-------------------- decode_value_date --------------------*/
1032 function decode_value_date(p_expression boolean,
1033 p_true date,
1034 p_false date) return date is
1035 begin
1036 if p_expression then
1037 return p_true;
1038 else
1039 return p_false;
1040 end if;
1041
1042 end decode_value_date;
1043
1044
1045 /*-------------------- decode_value_number --------------------*/
1046 function decode_value_number(p_expression boolean,
1047 p_true number,
1048 p_false number) return number is
1049 begin
1050 if p_expression then
1051 return p_true;
1052 else
1053 return p_false;
1054 end if;
1055
1056 end decode_value_number;
1057
1058
1059
1060 /*Bug 4080773*/
1061
1062 PROCEDURE update_paye_change_freq(p_assignment_id number
1063 ,p_effective_date date
1064 ,p_payroll_id number
1065 ,P_DATETRACK_UPDATE_MODE VARCHAR2
1066 ,p_tax_upload_flag varchar2 default 'X'
1067 ,p_tax_basis varchar2 default null
1068 ,p_cert_start_date date default null -- 17140460.6
1069 ,p_cert_end_date date default null
1070 ,p_weekly_tax_credit number default null
1071 ,p_monthly_tax_credit number default null
1072 ,p_weekly_std_rate_cut_off number default null
1073 ,p_monthly_std_rate_cut_off number default null
1074 ,p_tax_deducted_to_date number default null
1075 ,p_pay_to_date number default null
1076 ,p_cert_date date ) is --4878630
1077
1078 Cursor c_effective_paye is select *
1079 from pay_ie_paye_details_f
1080 where p_effective_date between effective_start_date and effective_end_date
1081 and assignment_id = p_assignment_id
1082 order by effective_start_date asc;
1083
1084 cursor c_future_paye(p_paye_details_id number) is select *
1085 from pay_ie_paye_details_f
1086 where p_effective_date < effective_start_date
1087 and assignment_id = p_assignment_id
1088 and ((paye_details_id <> p_paye_details_id and p_paye_details_id is not null) or p_paye_details_id is null )
1089 order by effective_start_date asc;
1090
1091 /* Cusror added for tax credit upload */ --4878630
1092 Cursor c_tax_effective_paye(p_paye_id number,p_date date) is
1093 select *
1094 from pay_ie_paye_details_f
1095 where ((p_date < effective_start_date and trunc(p_date,'Y') = trunc(effective_start_date,'Y') and p_paye_id is null)
1096 or (paye_details_id <> p_paye_id and p_paye_id is not null and p_date < effective_start_date))
1097 and assignment_id = p_assignment_id
1098 order by effective_start_date desc;
1099
1100
1101 Cursor csr_get_assg(p_assignment_id in number,p_effective_date date) is
1102 SELECT payroll_id ,effective_start_date
1103 FROM per_all_assignments_f paa
1104 WHERE paa.assignment_id=p_assignment_id
1105 AND p_effective_date between paa.effective_start_date
1106 and paa.effective_end_date;
1107
1108 CURSOR get_global_val(l_name IN VARCHAR2,p_effective_date date) IS
1109 SELECT global_value
1110 FROM ff_globals_f
1111 WHERE global_name = l_name
1112 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1113
1114 Cursor csr_freq(p_payroll_id number,p_effective_date date) IS
1115 SELECT 1
1116 FROM pay_all_payrolls_f pp
1117 WHERE pp.payroll_id = p_payroll_id
1118 AND p_effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
1119 AND period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year');
1120
1121
1122 c_effective_paye_fetch c_effective_paye%rowtype;
1123 c_future_paye_fetch c_future_paye%rowtype;
1124 c_tax_upload_paye c_tax_effective_paye%rowtype;
1125 l_asg_effective_start_date date;
1126 l_start_date date;
1127 l_end_date date;
1128 l_api_ovn NUMBER;
1129 l_monthly_tax_credit NUMBER:=0;
1130 l_monthly_std_rate_cut_off NUMBER:=0;
1131 l_weekly_tax_credit NUMBER:=0;
1132 l_weekly_std_rate_cut_off NUMBER:=0;
1133 l_tax_basis varchar2(20):='IE_CUMULATIVE';
1134 l_info_source varchar2(20):='IE_NONE_PROVIDED';
1135 l_tax_assess_basis varchar2(20):='IE_SEP_ASSESS';
1136 l_certificate_issue_date date;
1137 l_certificate_end_date date;
1138 p_update_mode varchar2(20);
1139 l_assignment_id number;
1140 L_PRIM_PAYROLL_ID number;
1141 L_EFFECTIVE_DATE date;
1142 l_old_flag NUMBER:=0;
1143 l_new_flag NUMBER:=0;
1144 L_MIN_PAYE_ID number;
1145 L_MIN_EFFECTIVE_DATE date;
1146 L_NEW_PAYROLL_ID number;
1147 L_CERTIFICATE_START_DATE date;
1148 l_futrec_effective_end_date date;
1149 l_max_paye_id pay_ie_paye_details_f.paye_details_id%TYPE; --4878630
1150 l_max_effective_start_date date; --4878630
1151 BEGIN
1152 l_new_payroll_id := p_payroll_id;
1153 l_effective_date :=p_effective_date;
1154 l_assignment_id := p_assignment_id;
1155 /*Checking whether pay frequency is changed or not*/
1156 hr_utility.set_location('In update_paye_change_freq',840);
1157 hr_utility.set_location('effective date..'||l_effective_date,841);
1158 hr_utility.set_location('p_payroll_id..'||p_payroll_id,842);
1159 hr_utility.set_location('p_tax_upload_flag..'||p_tax_upload_flag,843);
1160 hr_utility.set_location('p_tax_basis..'|| p_tax_basis,844);
1161 hr_utility.set_location('p_cert_start_date..'|| p_cert_start_date,845);
1162 hr_utility.set_location('p_cert_end_date..'|| p_cert_end_date,846);
1163 hr_utility.set_location('p_weekly_tax_credit ..'|| p_weekly_tax_credit,847);
1164 hr_utility.set_location('p_monthly_tax_credit..'|| p_monthly_tax_credit,848);
1165 hr_utility.set_location('p_weekly_std_rate_cut_off..'|| p_weekly_std_rate_cut_off,849);
1166 hr_utility.set_location('p_monthly_std_rate_cut_off..'|| p_monthly_std_rate_cut_off,850);
1167 hr_utility.set_location('p_tax_deducted_to_date..'|| p_tax_deducted_to_date,851);
1168 hr_utility.set_location('p_pay_to_date..'|| p_pay_to_date,852);
1169
1170
1171 if l_new_payroll_id is not null then
1172 hr_utility.set_location('l_new_payroll_id is not null..'|| l_new_payroll_id,853);
1173 if p_tax_upload_flag <> 'TU' then --4878630
1174 hr_utility.set_location('p_tax_upload_flag <> TU..'|| l_new_payroll_id,854);
1175 open csr_get_assg(l_assignment_id,l_effective_date);
1176 fetch csr_get_assg into l_prim_payroll_id,l_asg_effective_start_date;
1177 close csr_get_assg;
1178
1179 if (g_old_payroll_id is not null) then -- if global var is set use global value
1180 l_prim_payroll_id := g_old_payroll_id;
1181 end if;
1182 unset_old_payroll_id;
1183
1184 open csr_freq(l_prim_payroll_id,l_effective_date);
1185 fetch csr_freq into l_old_flag;
1186 close csr_freq;
1187
1188 open csr_freq(l_new_payroll_id,l_effective_date);
1189 fetch csr_freq into l_new_flag;
1190 close csr_freq;
1191 end if; -- p_tax_upload_flag <> 'TU'
1192
1193 if ( l_new_flag <> l_old_flag or p_tax_upload_flag <> 'X' ) then -- --4878630
1194 /*Fetching global values */
1195 hr_utility.set_location('l_new_flag <> l_old_flag or p_tax_upload_flag <> X',855);
1196 if l_new_flag =1 and p_tax_upload_flag <> 'TU' then
1197 hr_utility.set_location('l_new_flag =1',856);
1198 open get_global_val('IE_MONTHLY_TAX_CREDIT',l_effective_date);
1199 fetch get_global_val into l_monthly_tax_credit;
1200 close get_global_val;
1201 open get_global_val('IE_MONTHLY_STANDARD_RATE_CUT_OFF',l_effective_date);
1202 fetch get_global_val into l_monthly_std_rate_cut_off;
1203 close get_global_val;
1204 /* For monthly payroll, weekly values must be null */
1205 l_weekly_tax_credit :=NULL;
1206 l_weekly_std_rate_cut_off :=NULL;
1207 elsif l_new_flag = 0 and p_tax_upload_flag <> 'TU' then
1208 hr_utility.set_location('l_new_flag =0',857);
1209 open get_global_val('IE_WEEKLY_TAX_CREDIT',l_effective_date);
1210 fetch get_global_val into l_weekly_tax_credit;
1211 close get_global_val;
1212 open get_global_val('IE_WEEKLY_STANDARD_RATE_CUT_OFF',l_effective_date);
1213 fetch get_global_val into l_weekly_std_rate_cut_off;
1214 close get_global_val;
1215 /* For weekly payroll, monthly values must be null */
1216 l_monthly_tax_credit :=NULL;
1217 l_monthly_std_rate_cut_off :=NULL;
1218 elsif p_tax_upload_flag = 'TU' then -- --4878630
1219 hr_utility.set_location('p_tax_upload_flag =TU',858);
1220 l_weekly_tax_credit := p_weekly_tax_credit;
1221 l_monthly_tax_credit := p_monthly_tax_credit;
1222 l_weekly_std_rate_cut_off := p_weekly_std_rate_cut_off;
1223 l_monthly_std_rate_cut_off := p_monthly_std_rate_cut_off;
1224 /* fetch values from interface table */
1225 end if;
1226
1227 open c_effective_paye ;
1228 fetch c_effective_paye into c_effective_paye_fetch;
1229 if c_effective_paye%found then
1230 hr_utility.set_location('if found',859);
1231 -- delete all future records ie diff paye_details_id
1232 open c_future_paye(c_effective_paye_fetch.paye_details_id);
1233 loop
1234 fetch c_future_paye into c_future_paye_fetch;
1235 EXIT when c_future_paye%NOTFOUND;
1236 hr_utility.set_location('if loop',860);
1237 pay_ie_paye_api.delete_ie_paye_details
1238 (p_validate => FALSE
1239 ,p_effective_date => c_future_paye_fetch.effective_start_date
1240 ,p_datetrack_delete_mode => 'ZAP'
1241 ,p_paye_details_id => c_future_paye_fetch.paye_details_id
1242 ,p_object_version_number => c_future_paye_fetch.object_version_number
1243 ,p_effective_start_date => l_start_date
1244 ,p_effective_end_date => l_end_date
1245 );
1246 end loop;
1247 close c_future_paye;
1248 -- FETCH OVN
1249 l_api_ovn := c_effective_paye_fetch.object_version_number;
1250 hr_utility.set_location('l_api_ovn..'||l_api_ovn,861);
1251 --if the start date is the effective date in the form then only mode possible should be CORRECTION
1252 if (c_effective_paye_fetch.tax_basis <> 'IE_CUMULATIVE' and c_effective_paye_fetch.tax_basis <> 'IE_EXEMPTION'
1253 and c_effective_paye_fetch.tax_basis <> 'IE_WEEK1_MONTH1' and c_effective_paye_fetch.tax_basis <> 'IE_EXEMPT_WEEK_MONTH'
1254 and p_tax_upload_flag <> 'TU' ) then
1255 hr_utility.set_location('Emergency ..',862);
1256 l_weekly_tax_credit:=NULL;
1257 l_weekly_std_rate_cut_off:=NULL;
1258 l_monthly_tax_credit:=NULL;
1259 l_monthly_std_rate_cut_off:=NULL;
1260 end if;
1261 --if there are no future changes to the paye record.
1262 if c_effective_paye_fetch.effective_end_date <> to_date('31-12-4712','DD-MM-YYYY') then
1263 --if there are future changes.Then just leave one till 4712 using mode future change
1264 hr_utility.set_location('date <> 31-12-4712',863);
1265 pay_ie_paye_api.delete_ie_paye_details
1266 (p_validate => FALSE
1267 ,p_effective_date => c_effective_paye_fetch.effective_start_date
1268 ,p_datetrack_delete_mode => 'FUTURE_CHANGE'
1269 ,p_paye_details_id => c_effective_paye_fetch.paye_details_id
1270 ,p_object_version_number => l_api_ovn
1271 ,p_effective_start_date => l_start_date
1272 ,p_effective_end_date => l_end_date
1273 );
1274
1275 end if;
1276
1277 if c_effective_paye_fetch.effective_start_date = p_effective_date then
1278 hr_utility.set_location('c_effective_paye_fetch.effective_start_date = p_effective_date',864);
1279 --if P_DATETRACK_UPDATE_MODE = 'CORRECTION' then
1280 pay_ie_paye_api.update_ie_paye_details
1281 (p_validate => FALSE
1282 ,p_effective_date => p_effective_date
1283 ,p_datetrack_update_mode => 'CORRECTION'
1284 ,p_paye_details_id => c_effective_paye_fetch.paye_details_id
1285 ,p_info_source => decode_value_char(p_tax_upload_flag ='X',c_effective_paye_fetch.info_source,'IE_ELECTRONIC')
1286 ,p_tax_basis => decode_value_char(p_tax_upload_flag ='X',c_effective_paye_fetch.tax_basis,p_tax_basis) -- tax credit upload changes
1287 ,p_certificate_start_date => decode_value_date(p_tax_upload_flag='X',c_effective_paye_fetch.certificate_start_date,p_cert_start_date) -- tax credit upload changes 17140460.6
1288 ,p_tax_assess_basis => decode_value_char(p_tax_upload_flag ='X',c_effective_paye_fetch.tax_assess_basis,'IE_SEP_TREAT')
1289 ,p_certificate_issue_date => decode_value_date(p_tax_upload_flag='X',c_effective_paye_fetch.certificate_issue_date,p_cert_date) -- Bug 6929566 p_effective_date) -- tax credit upload changes
1290 ,p_certificate_end_date => decode_value_date(p_tax_upload_flag='X',c_effective_paye_fetch.certificate_end_date,p_cert_end_date) -- tax credit upload changes
1291 ,p_weekly_tax_credit => l_weekly_tax_credit
1292 ,p_weekly_std_rate_cut_off => l_weekly_std_rate_cut_off
1293 ,p_monthly_tax_credit => l_monthly_tax_credit
1294 ,p_monthly_std_rate_cut_off => l_monthly_std_rate_cut_off
1295 ,p_tax_deducted_to_date => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_tax_deducted_to_date) -- tax credit upload change
1296 ,p_pay_to_date => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_pay_to_date) -- tax credit upload change
1297 ,p_disability_benefit => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,null) -- tax credit upload change
1298 ,p_lump_sum_payment => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,null) -- tax credit upload change
1299 ,p_object_version_number => l_api_ovn
1300 ,p_effective_start_date => l_start_date
1301 ,p_effective_end_date => l_end_date
1302 );
1303 else --c_effective_paye_fetch.effective_start_date <> p_effective_date
1304 -- should always be update with new credits and cutoffs
1305 if P_DATETRACK_UPDATE_MODE = 'CORRECTION' then
1306 -- check with asg start date
1307 hr_utility.set_location('Correction and <> TU',865);
1308 if (l_asg_effective_start_date <= c_effective_paye_fetch.effective_start_date ) then
1309 pay_ie_paye_api.update_ie_paye_details
1310 (p_validate => FALSE
1311 ,p_effective_date => p_effective_date
1312 ,p_datetrack_update_mode => 'CORRECTION'
1313 ,p_paye_details_id => c_effective_paye_fetch.PAYE_DETAILs_ID
1314 ,p_info_source => c_effective_paye_fetch.info_source
1315 ,p_tax_basis => c_effective_paye_fetch.tax_basis
1316 ,p_certificate_start_date => c_effective_paye_fetch.certificate_start_date
1317 ,p_tax_assess_basis => c_effective_paye_fetch.tax_assess_basis
1318 ,p_certificate_issue_date => c_effective_paye_fetch.certificate_issue_date
1319 ,p_certificate_end_date => c_effective_paye_fetch.certificate_end_date
1320 ,p_weekly_tax_credit => l_weekly_tax_credit
1321 ,p_weekly_std_rate_cut_off => l_weekly_std_rate_cut_off
1322 ,p_monthly_tax_credit => l_monthly_tax_credit
1323 ,p_monthly_std_rate_cut_off => l_monthly_std_rate_cut_off
1324 ,p_tax_deducted_to_date => hr_api.g_number
1325 ,p_pay_to_date => hr_api.g_number
1326 ,p_disability_benefit => hr_api.g_number
1327 ,p_lump_sum_payment => hr_api.g_number
1328 ,p_object_version_number => l_api_ovn
1329 ,p_effective_start_date => l_start_date
1330 ,p_effective_end_date => l_end_date);
1331 else -- asg start date > paye start date then update using asg start date
1332 hr_utility.set_location('Correction and <> TU',865);
1333 pay_ie_paye_api.update_ie_paye_details
1334 (p_validate => FALSE
1335 ,p_effective_date => l_asg_effective_start_date
1336 ,p_datetrack_update_mode => 'UPDATE'
1337 ,p_paye_details_id => c_effective_paye_fetch.PAYE_DETAILs_ID
1338 ,p_info_source => c_effective_paye_fetch.info_source
1339 ,p_tax_basis => c_effective_paye_fetch.tax_basis
1340 ,p_certificate_start_date => c_effective_paye_fetch.certificate_start_date
1341 ,p_tax_assess_basis => c_effective_paye_fetch.tax_assess_basis
1342 ,p_certificate_issue_date => c_effective_paye_fetch.certificate_issue_date
1343 ,p_certificate_end_date => c_effective_paye_fetch.certificate_end_date
1344 ,p_weekly_tax_credit => l_weekly_tax_credit
1345 ,p_weekly_std_rate_cut_off => l_weekly_std_rate_cut_off
1346 ,p_monthly_tax_credit => l_monthly_tax_credit
1347 ,p_monthly_std_rate_cut_off => l_monthly_std_rate_cut_off
1348 ,p_tax_deducted_to_date => hr_api.g_number
1349 ,p_pay_to_date => hr_api.g_number
1350 ,p_disability_benefit => hr_api.g_number
1351 ,p_lump_sum_payment => hr_api.g_number
1352 ,p_object_version_number => l_api_ovn
1353 ,p_effective_start_date => l_start_date
1354 ,p_effective_end_date => l_end_date);
1355 end if; -- end of check with asg start date
1356 else -- P_DATETRACK_UPDATE_MODE <> 'CORRECTION'
1357 hr_utility.set_location('UPDATE and = TU',866);
1358 pay_ie_paye_api.update_ie_paye_details
1359 (p_validate => FALSE
1360 ,p_effective_date => p_effective_date
1361 ,p_datetrack_update_mode => 'UPDATE'
1362 ,p_paye_details_id => c_effective_paye_fetch.PAYE_DETAILs_ID
1363 ,p_info_source => decode_value_char(p_tax_upload_flag ='X',c_effective_paye_fetch.info_source,'IE_ELECTRONIC')
1364 ,p_tax_basis => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.tax_basis,p_tax_basis) -- tax credit upload changes
1365 ,p_certificate_start_date => decode_value_date(p_tax_upload_flag='X',c_effective_paye_fetch.certificate_start_date,p_cert_start_date) -- tax credit upload changes, 17140460.6
1366 ,p_tax_assess_basis => decode_value_char(p_tax_upload_flag ='X',c_effective_paye_fetch.tax_assess_basis,'IE_SEP_TREAT')
1367 ,p_certificate_issue_date => decode_value_date(p_tax_upload_flag='X',c_effective_paye_fetch.certificate_issue_date,p_cert_date) -- Bug 6929566 p_effective_date) -- tax credit upload changes
1368 ,p_certificate_end_date => decode_value_date(p_tax_upload_flag='X',c_effective_paye_fetch.certificate_end_date,p_cert_end_date) -- tax credit upload changes
1369 ,p_weekly_tax_credit => l_weekly_tax_credit
1370 ,p_weekly_std_rate_cut_off => l_weekly_std_rate_cut_off
1371 ,p_monthly_tax_credit => l_monthly_tax_credit
1372 ,p_monthly_std_rate_cut_off => l_monthly_std_rate_cut_off
1373 ,p_tax_deducted_to_date => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_tax_deducted_to_date) -- tax credit upload change
1374 ,p_pay_to_date => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_pay_to_date) -- tax credit upload change
1375 ,p_disability_benefit => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,null) -- tax credit upload change
1376 ,p_lump_sum_payment => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,null) -- tax credit upload change
1377 ,p_object_version_number => l_api_ovn
1378 ,p_effective_start_date => l_start_date
1379 ,p_effective_end_date => l_end_date);
1380 end if; --P_DATETRACK_UPDATE_MODE = 'CORRECTION'
1381 hr_utility.set_location('After datetrack check',867);
1382 end if; -- c_effective_paye_fetch.effective_start_date = p_effective_date
1383 else -- not found
1384 --get the first record as of effective date.This is becasue there mare reocrds only afte the effective date.Nothing as of
1385 --the effective date.this record should be extended upto 4712
1386 hr_utility.set_location('Effective date does not lie between paye start and end date',868);
1387 if p_tax_upload_flag <> 'TU' then --4878630
1388 hr_utility.set_location('<> TU',869);
1389 open c_future_paye(null);
1390 fetch c_future_paye into c_future_paye_fetch ;
1391 l_min_paye_id := c_future_paye_fetch.paye_details_id ;
1392 l_min_effective_date := c_future_paye_fetch.effective_start_date;
1393 l_api_ovn := c_future_paye_fetch.object_version_number;
1394 l_tax_basis := c_future_paye_fetch.tax_basis;
1395 l_info_source := c_future_paye_fetch.info_source;
1396 l_certificate_start_date := c_future_paye_fetch.certificate_start_date;
1397 l_certificate_end_date := c_future_paye_fetch.certificate_end_date;
1398 l_certificate_issue_date := c_future_paye_fetch.certificate_issue_date;
1399 l_tax_assess_basis := c_future_paye_fetch.tax_assess_basis;
1400 l_futrec_effective_end_date := c_future_paye_fetch.effective_end_date;
1401 close c_future_paye;
1402 else --4878630
1403 hr_utility.set_location('= TU',870);
1404 open c_tax_effective_paye(null,p_effective_date);
1405 fetch c_tax_effective_paye into c_tax_upload_paye;
1406 l_max_paye_id := c_tax_upload_paye.paye_details_id;
1407 l_max_effective_start_date := c_tax_upload_paye.effective_start_date;
1408 l_futrec_effective_end_date := c_tax_upload_paye.effective_end_date;
1409 l_api_ovn := c_tax_upload_paye.object_version_number;
1410 l_info_source := c_tax_upload_paye.info_source;
1411 --l_tax_assess_basis := c_tax_upload_paye.tax_assess_basis;
1412 CLOSE c_tax_effective_paye;
1413 hr_utility.set_location('l_max_paye_id..'|| l_max_paye_id,871);
1414 hr_utility.set_location('l_max_effective_start_date.'|| l_max_effective_start_date,872);
1415 hr_utility.set_location('l_futrec_effective_end_date '|| l_futrec_effective_end_date,873);
1416 hr_utility.set_location('l_api_ovn '|| l_api_ovn,874);
1417 hr_utility.set_location('l_info_source.'|| l_info_source,875);
1418
1419 end if;
1420 IF p_tax_upload_flag <> 'TU' then
1421 --delete any other future records ie different paye_details_id
1422 open c_future_paye(l_min_paye_id);
1423 loop
1424 fetch c_future_paye into c_future_paye_fetch;
1425 EXIT when c_future_paye%NOTFOUND;
1426 pay_ie_paye_api.delete_ie_paye_details
1427 (p_validate => FALSE
1428 ,p_effective_date => c_future_paye_fetch.effective_start_date
1429 ,p_datetrack_delete_mode => 'ZAP'
1430 ,p_paye_details_id => c_future_paye_fetch.paye_details_id
1431 ,p_object_version_number => c_future_paye_fetch.object_version_number
1432 ,p_effective_start_date => l_start_date
1433 ,p_effective_end_date => l_end_date
1434 );
1435 end loop;
1436 close c_future_paye;
1437
1438
1439 if l_futrec_effective_end_date <> to_date('31-12-4712','DD-MM-YYYY')
1440 AND (l_futrec_effective_end_date IS NOT NULL)
1441 then
1442 --extend the first record after the effective date till 4712
1443 pay_ie_paye_api.delete_ie_paye_details
1444 (p_validate => FALSE
1445 ,p_effective_date => l_min_effective_date
1446 ,p_datetrack_delete_mode => 'FUTURE_CHANGE'
1447 ,p_paye_details_id => l_min_paye_id
1448 ,p_object_version_number => l_api_ovn
1449 ,p_effective_start_date => l_start_date
1450 ,p_effective_end_date => l_end_date
1451 );
1452 end if;
1453 if (l_tax_basis <> 'IE_CUMULATIVE' and l_tax_basis <> 'IE_EXEMPTION'
1454 and l_tax_basis <> 'IE_WEEK1_MONTH1' and l_tax_basis <> 'IE_EXEMPT_WEEK_MONTH') then
1455 l_weekly_tax_credit:=NULL;
1456 l_weekly_std_rate_cut_off:=NULL;
1457 l_monthly_tax_credit:=NULL;
1458 l_monthly_std_rate_cut_off:=NULL;
1459 end if;
1460 --only mode possible should be correction, using the new credits and cutoffs
1461 IF (l_futrec_effective_end_date IS NOT NULL) THEN
1462 pay_ie_paye_api.update_ie_paye_details
1463 (p_validate => FALSE
1464 ,p_effective_date => l_min_effective_date
1465 ,p_datetrack_update_mode => 'CORRECTION'
1466 ,p_paye_details_id => l_min_paye_id
1467 ,p_info_source => l_info_source
1468 ,p_tax_basis => l_tax_basis
1469 ,p_certificate_start_date => l_certificate_start_date
1470 ,p_tax_assess_basis => l_tax_assess_basis
1471 ,p_certificate_issue_date => l_certificate_issue_date
1472 ,p_certificate_end_date => l_certificate_end_date
1473 ,p_weekly_tax_credit => l_weekly_tax_credit
1474 ,p_weekly_std_rate_cut_off => l_weekly_std_rate_cut_off
1475 ,p_monthly_tax_credit => l_monthly_tax_credit
1476 ,p_monthly_std_rate_cut_off => l_monthly_std_rate_cut_off
1477 ,p_tax_deducted_to_date => hr_api.g_number
1478 ,p_pay_to_date => hr_api.g_number
1479 ,p_disability_benefit => hr_api.g_number
1480 ,p_lump_sum_payment => hr_api.g_number
1481 ,p_object_version_number => l_api_ovn
1482 ,p_effective_start_date => l_start_date
1483 ,p_effective_end_date => l_end_date
1484 );
1485 END IF;-- futrec is null
1486 ELSE -- p_tax_upload_flag = 'TU'
1487 --delete any other future records ie different paye_details_id
1488 hr_utility.set_location('else of future paye.'|| l_info_source,876);
1489 open c_tax_effective_paye(l_max_paye_id,l_max_effective_start_date);
1490 loop
1491 fetch c_tax_effective_paye into c_tax_upload_paye;
1492 EXIT when c_tax_effective_paye%NOTFOUND;
1493 hr_utility.set_location('In loop',878);
1494 hr_utility.set_location('c_tax_upload_paye.effective_start_date '|| c_tax_upload_paye.effective_start_date ,879);
1495 hr_utility.set_location('c_tax_upload_paye.paye_details_id.'||c_tax_upload_paye.paye_details_id,880);
1496
1497 pay_ie_paye_api.delete_ie_paye_details
1498 (p_validate => FALSE
1499 ,p_effective_date => c_tax_upload_paye.effective_start_date
1500 ,p_datetrack_delete_mode => 'ZAP'
1501 ,p_paye_details_id => c_tax_upload_paye.paye_details_id
1502 ,p_object_version_number => c_tax_upload_paye.object_version_number
1503 ,p_effective_start_date => l_start_date
1504 ,p_effective_end_date => l_end_date
1505 );
1506 end loop;
1507 hr_utility.set_location('else of future paye After ZAping',881);
1508 close c_tax_effective_paye;
1509
1510 hr_utility.set_location('l_futrec_effective_end_date..'|| l_futrec_effective_end_date,879);
1511 if l_futrec_effective_end_date <> to_date('31-12-4712','DD-MM-YYYY')
1512 AND (l_futrec_effective_end_date IS NOT NULL)
1513 then
1514 --extend the first record after the effective date till 4712
1515 pay_ie_paye_api.delete_ie_paye_details
1516 (p_validate => FALSE
1517 ,p_effective_date => l_max_effective_start_date
1518 ,p_datetrack_delete_mode => 'FUTURE_CHANGE'
1519 ,p_paye_details_id => l_max_paye_id
1520 ,p_object_version_number => l_api_ovn
1521 ,p_effective_start_date => l_start_date
1522 ,p_effective_end_date => l_end_date
1523 );
1524 end if;
1525
1526 --only mode possible should be correction, using the new credits and cutoffs
1527 IF (l_futrec_effective_end_date IS NOT NULL) THEN
1528 hr_utility.set_location('The last Mode',880);
1529 pay_ie_paye_api.update_ie_paye_details
1530 (p_validate => FALSE
1531 ,p_effective_date => l_max_effective_start_date
1532 ,p_datetrack_update_mode => 'CORRECTION'
1533 ,p_paye_details_id => l_max_paye_id
1534 ,p_info_source => 'IE_ELECTRONIC'
1535 ,p_tax_basis => p_tax_basis
1536 ,p_certificate_start_date => p_cert_start_date
1537 ,p_tax_assess_basis => 'IE_SEP_TREAT'
1538 ,p_certificate_issue_date => p_effective_date
1539 ,p_certificate_end_date => p_cert_end_date
1540 ,p_weekly_tax_credit => p_weekly_tax_credit
1541 ,p_weekly_std_rate_cut_off => p_weekly_std_rate_cut_off
1542 ,p_monthly_tax_credit => p_monthly_tax_credit
1543 ,p_monthly_std_rate_cut_off => p_monthly_std_rate_cut_off
1544 ,p_tax_deducted_to_date => p_tax_deducted_to_date
1545 ,p_pay_to_date => p_pay_to_date
1546 ,p_disability_benefit => null
1547 ,p_lump_sum_payment => null
1548 ,p_object_version_number => l_api_ovn
1549 ,p_effective_start_date => l_start_date
1550 ,p_effective_end_date => l_end_date
1551 );
1552 hr_utility.set_location('After The last Mode',881);
1553 END IF;-- futrec is null
1554
1555 END If; -- p_tax_upload_flag <> 'TU'
1556 end if; --if c_effective_paye%found
1557 close c_effective_paye;
1558 end if; --(l_new_flag <> l_old_flag)
1559 end if; -- l_new_payroll_id is not null
1560 END update_paye_change_freq;
1561
1562 Procedure set_old_payroll_id(
1563 p_old_payroll_id number
1564 )
1565 IS
1566 BEGIN
1567 g_old_payroll_id:=p_old_payroll_id;
1568 END set_old_payroll_id;
1569
1570 Procedure unset_old_payroll_id
1571 IS
1572 BEGIN
1573 g_old_payroll_id:=null;
1574 END unset_old_payroll_id;
1575
1576 Function get_old_payroll_id return number is
1577 begin
1578 return g_old_payroll_id;
1579 end get_old_payroll_id;
1580 /*End of Bug 4080773*/
1581 end pay_ie_paye_pkg;