[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_PAYE_PKG
Source
1 package body PAY_IE_PAYE_PKG as
2 /* $Header: pyietax.pkb 120.20 2011/11/23 13:23:47 smeduri ship $ */
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 ** 05-Dec-2008 rrajaman 7622221 Ireland budget 2009 new formula function
78 ** 11-Dec-2008 rrajaman 7622221 get_age_payroll_period modified to check age as of 31-Dec
79 ** 23-Dec-2008 rrajaman 7665572 Levy dates advanced for Offset Payroll
80 ** 03-Dec-2009 rrajaman 9177545 added get_periods_between function.
81 ** 06-Sep-2010 vijranga 10078301 added get_ie_employer_info function.
82 ** 23-Sep-2010 vijranga 10078301 Removed gscc errors
83 ** 08-Nov-2010 vijranga 10254081 Added get_last_period function
84 ** 22-JUL-2011 rsahai 12779712 Removed vague FND_LOG messages.
85 ** 08-NOV-2011 rsahai 13359423 P2C changes 2012, proc update_ie_paye_details modified
86 ** 23-Nov-2011 smeduri 13359545 added func get_usc_details
87 -------------------------------------------------------------------------------
88 */
89 g_package varchar2(33) := 'pay_ie_paye.';
90 g_old_payroll_id per_all_assignments_f.payroll_id%TYPE; --added for update_paye_change_freq
91 /* Added cursor for Bug 3030621 */
92 cursor g_absence_dates (c_element_entry_id number) is
93 SELECT pev.SCREEN_ENTRY_VALUE
94 FROM pay_element_entries_f pee, pay_element_links_f pel, pay_element_types_f pet,pay_element_entry_values_f pev,
95 pay_input_values_f piv
96 WHERE pee.element_link_id = pel.element_link_id
97 AND pet.element_type_id = pel.element_type_id
98 AND pet.element_name = 'IE Social Benefit Option 2'
99 AND pee.element_entry_id = c_element_entry_id
100 AND pet.element_type_id = piv.element_type_id
101 AND piv.legislation_code='IE'
102 AND piv.name in ('Absence Start Date','Absence End Date')
103 AND piv.element_type_id = pel.element_type_id
104 AND piv.input_value_id=pev.input_value_id
105 AND pev.element_entry_id = c_element_entry_id
106 ORDER by piv.name desc;
107
108
109
110 Function get_paye_tax_basis(p_assignment_id in number /* 5867343 */
111 ,p_payroll_action_id in number
112 ,p_tax_basis out nocopy varchar2)
113 return number is
114
115 --Local vriables-----
116
117 l_proc varchar2(72) := g_package||'get_paye_details';
118
119
120 -- cursor to fetch tax basis
121 cursor c_paye_tax_basis is select tax_basis
122 from pay_ie_paye_details_f pipd
123 ,pay_payroll_actions ppa
124 ,per_time_periods ptp
125 where pipd.assignment_id = p_assignment_id
126 and ppa.payroll_action_id = p_payroll_action_id
127 and ppa.effective_date between pipd.effective_start_date and --Bug Fix 3227184
128 nvl(pipd.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
129 and pipd.info_source in ('IE_P45','IE_ELECTRONIC','IE_CERT_TAX_CREDITS','IE_NONE_PROVIDED');
130
131
132 procedure initialise is
133 begin
134 p_tax_basis:='zzzz' ;
135 end;
136
137 begin
138
139 hr_utility.set_location('Entering:'||l_proc, 5);
140 open c_paye_tax_basis;
141
142 fetch c_paye_tax_basis into p_tax_basis;
143
144 if c_paye_tax_basis%notfound then
145 initialise;
146 close c_paye_tax_basis;
147 return 0;
148 end if;
149
150 close c_paye_tax_basis;
151 hr_utility.set_location('Leaving:'||l_proc, 30);
152 return 1;
153
154 exception when others then
155 initialise;
156 close c_paye_tax_basis;
157 raise_application_error(-20001,l_proc||'- '||sqlerrm);
158 return 0;
159
160 end get_paye_tax_basis; /* 5867343 */
161
162 Function get_diff_tax_basis(p_assignment_id in number /* 5867343 */
163 ,p_payroll_id in number
164 ,p_date_earned in date)
165 return number is
166
167 --Local vriables-----
168
169 l_proc varchar2(72) := g_package||'get_diff_tax_basis';
170 l_sec_assignment number;
171
172 -- Cursor to check whether multiple assignment has a different tax basis
173 CURSOR chk_multi_asgn_tax_basis IS
174 SELECT 1
175 FROM per_all_assignments_f paaf
176 ,per_time_periods ptp
177 ,pay_ie_paye_details_f pipd
178 WHERE paaf.person_id = ( SELECT distinct person_id FROM per_all_assignments_f WHERE assignment_id = p_assignment_id )
179 AND paaf.assignment_id <> p_assignment_id
180 AND pipd.assignment_id(+) = paaf.assignment_id
181 AND nvl(pipd.tax_basis,'X') <> 'IE_EXCLUDE'
182 AND p_date_earned BETWEEN ptp.start_date and ptp.end_date
183 AND ptp.payroll_id = p_payroll_id
184 AND paaf.effective_start_date <= ptp.end_date
185 AND paaf.effective_end_date >= ptp.start_date;
186
187 BEGIN
188
189 hr_utility.set_location('Entering:'||l_proc, 5);
190
191 OPEN chk_multi_asgn_tax_basis;
192 FETCH chk_multi_asgn_tax_basis INTO l_sec_assignment;
193
194 IF chk_multi_asgn_tax_basis%NOTFOUND THEN
195 close chk_multi_asgn_tax_basis;
196 hr_utility.set_location('Leaving:'||l_proc, 30);
197 return 0;
198 ELSE
199 close chk_multi_asgn_tax_basis;
200 hr_utility.set_location('Leaving:'||l_proc, 31);
201 return 1;
202 END IF;
203
204 exception when others then
205 close chk_multi_asgn_tax_basis;
206 raise_application_error(-20001,l_proc||'- '||sqlerrm);
207 return 0;
208
209 end get_diff_tax_basis; /* 5867343 */
210
211
212 Function get_ie_exclude_tax_basis(p_assignment_id in number /* 5867343 */
213 ,p_payroll_id in number
214 ,p_date_earned in date)
215 return number is
216
217 --Local vriables-----
218
219 l_proc varchar2(72) := g_package||'get_ie_exclude_tax_basis';
220 l_sec_assignment number;
221
222 -- Cursor to check whether multiple assignment has a different tax basis
223 CURSOR chk_multi_asgn_tax_basis IS
224 SELECT 1
225 FROM per_all_assignments_f paaf
226 ,per_time_periods ptp
227 ,pay_ie_paye_details_f pipd
228 WHERE paaf.person_id = ( SELECT distinct person_id FROM per_all_assignments_f WHERE assignment_id = p_assignment_id )
229 AND paaf.assignment_id <> p_assignment_id
230 AND pipd.assignment_id(+) = paaf.assignment_id
231 AND nvl(pipd.tax_basis,'X') = 'IE_EXCLUDE'
232 AND p_date_earned BETWEEN ptp.start_date and ptp.end_date
233 AND ptp.payroll_id = p_payroll_id
234 AND paaf.effective_start_date <= ptp.end_date
235 AND paaf.effective_end_date >= ptp.start_date;
236
237 BEGIN
238
239 hr_utility.set_location('Entering:'||l_proc, 5);
240
241 OPEN chk_multi_asgn_tax_basis;
242 FETCH chk_multi_asgn_tax_basis INTO l_sec_assignment;
243
244 IF chk_multi_asgn_tax_basis%NOTFOUND THEN
245 close chk_multi_asgn_tax_basis;
246 hr_utility.set_location('Leaving:'||l_proc, 30);
247 return 0;
248 ELSE
249 close chk_multi_asgn_tax_basis;
250 hr_utility.set_location('Leaving:'||l_proc, 31);
251 return 1;
252 END IF;
253
254 exception when others then
255 close chk_multi_asgn_tax_basis;
256 raise_application_error(-20001,l_proc||'- '||sqlerrm);
257 return 0;
258
259 end get_ie_exclude_tax_basis; /* 5867343 */
260
261
262
263
264 Function get_paye_details(p_assignment_id in number
265 ,p_payroll_action_id in number
266 ,p_info_source out nocopy varchar2
267 ,p_tax_basis out nocopy varchar2
268 ,p_weekly_tax_credit out nocopy number
269 ,p_monthly_tax_credit out nocopy number
270 ,p_weekly_std_rate_cutoff out nocopy number
271 ,p_monthly_std_rate_cutoff out nocopy number
272 ,p_certificate_start_date out nocopy date
273 ,p_certificate_end_date out nocopy date
274 /*changes for SOE form requirements*/
275 ,p_assess_basis out nocopy varchar2
276 ,p_certificate_issue_date out nocopy date
277 /*parameters added for p45 archiving*/
278 ,p_reduced_tax_credit out nocopy number
279 ,p_reduced_std_rate_cutoff out nocopy number
280 ,p_benefit_amount out nocopy number)
281 /*************************************************/
282 return number is
283
284 --Local vriables-----
285
286 l_proc varchar2(72) := g_package||'get_paye_details';
287 l_payroll_id number;
288 l_date_earned date;
289 l_period_type varchar2(20);
290 l_soc_ben_rec pay_ie_social_benefits_f%rowtype;
291
292 -- added for getting calculated values as per period type
293 l_period_ind varchar2(3);
294 l_cal_reduced_tax_credit number;
295 l_cal_reduced_cut_off number;
296 --
297 -- Bug 2943335 - Added
298 total_benefit_amount number := 0;
299 l_benefit_amount number;
300
301 cursor c_paye_dtl is select ppa.payroll_id
302 ,ppa.date_earned
303 ,info_source
304 ,tax_basis
305 ,nvl(weekly_tax_credit,0)
306 ,nvl(monthly_tax_credit,0)
307 ,nvl(weekly_std_rate_cut_off,0)
308 ,nvl(monthly_std_rate_cut_off,0)
309 ,effective_start_date
310 ,nvl(pipd.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
311 /*changes for SOE form requirements*/
312 ,pipd.tax_assess_basis
313 ,nvl(pipd.certificate_issue_date,to_date('01-01-0001','DD-MM-YYYY'))
314 ,ptp.period_type
315 from pay_ie_paye_details_f pipd
316 ,pay_payroll_actions ppa
317 ,per_time_periods ptp
318 where pipd.assignment_id = p_assignment_id
319 and ppa.payroll_action_id = p_payroll_action_id
320 -- and ppa.date_earned between pipd.effective_start_date and
321 and ppa.effective_date between pipd.effective_start_date and --Bug Fix 3227184
322 nvl(pipd.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
323 and pipd.info_source in ('IE_P45','IE_ELECTRONIC','IE_CERT_TAX_CREDITS','IE_NONE_PROVIDED')
324 and ptp.payroll_id = ppa.payroll_id
325 and ppa.date_earned between ptp.start_date and ptp.end_date;
326 --
327 -- Bug 2943335 - commented code below to reference element entries table for data
328 --
329 /* cursor c_soc_ben(c_payroll_id number,c_date_earned date) is select calculation_option
330 ,nvl(reduced_tax_credit,0)
331 ,nvl(reduced_standard_cutoff,0)
332 ,nvl(benefit_amount,0)
333 from pay_ie_social_benefits_f psb,
334 per_time_periods ptp
335 where psb.absence_start_date between ptp.start_date and ptp.end_date
336 and ptp.payroll_id = c_payroll_id
337 and psb.assignment_id = p_assignment_id
338 and calculation_option not in('IE_OPTION0','IE_OPTION1')
339 and c_date_earned between ptp.start_date and ptp.end_date
340 order by psb.effective_start_date desc; */
341 -- SOC cahnges....
342 --
343 cursor cur_c_soc_ben
344 is
345 select NVL(SUM(TO_NUMBER(SCREEN_ENTRY_VALUE)),0)
346 from
347 PAY_INPUT_VALUES_F INPVAL,
348 PAY_ELEMENT_TYPES_F TYPE,
349 PAY_ELEMENT_LINKS_F LINK,
350 PAY_ELEMENT_ENTRY_VALUES_F VALUE,
351 PAY_ELEMENT_ENTRIES_F ENTRY,
352 PER_TIME_PERIODS PTP,
353 PAY_PAYROLL_ACTIONS PACT
354 -- ,FND_SESSIONS SESH
355 WHERE
356 PACT.PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID AND
357 --PTP.TIME_PERIOD_ID = PACT.TIME_PERIOD_ID AND -- Bug 5070091 Offset payroll change
358 PACT.PAYROLL_ID = PTP.PAYROLL_ID AND
359 PACT.DATE_EARNED BETWEEN PTP.START_DATE AND PTP.END_DATE AND
360 --SESH.SESSION_ID = USERENV ('sessionid') AND
361 TYPE.ELEMENT_NAME = 'IE Social Benefit Option 2' AND
362 -- SESH.EFFECTIVE_DATE BETWEEN TYPE.EFFECTIVE_START_DATE AND TYPE.EFFECTIVE_END_DATE AND
363 PACT.EFFECTIVE_DATE BETWEEN TYPE.EFFECTIVE_START_DATE AND TYPE.EFFECTIVE_END_DATE AND
364 TYPE.ELEMENT_TYPE_ID = LINK.ELEMENT_TYPE_ID AND
365 -- SESH.EFFECTIVE_DATE BETWEEN LINK.EFFECTIVE_START_DATE AND LINK.EFFECTIVE_END_DATE AND
366 PACT.EFFECTIVE_DATE BETWEEN LINK.EFFECTIVE_START_DATE AND LINK.EFFECTIVE_END_DATE AND
367 ENTRY.ELEMENT_LINK_ID = LINK.ELEMENT_LINK_ID AND
368 ENTRY.ASSIGNMENT_ID = P_ASSIGNMENT_ID AND
369 ENTRY.EFFECTIVE_START_DATE <= PTP.END_DATE AND
370 ENTRY.EFFECTIVE_END_DATE >= PTP.START_DATE AND
371 VALUE.ELEMENT_ENTRY_ID = ENTRY.ELEMENT_ENTRY_ID AND
372 VALUE.EFFECTIVE_START_DATE = ENTRY.EFFECTIVE_START_DATE AND
373 VALUE.EFFECTIVE_END_DATE = ENTRY.EFFECTIVE_END_DATE AND
374 INPVAL.INPUT_VALUE_ID = VALUE.INPUT_VALUE_ID AND
375 INPVAL.NAME = 'Taxable Benefit Amount' AND
376 -- SESH.EFFECTIVE_DATE BETWEEN INPVAL.EFFECTIVE_START_DATE AND INPVAL.EFFECTIVE_END_DATE;
377 PACT.EFFECTIVE_DATE BETWEEN INPVAL.EFFECTIVE_START_DATE AND INPVAL.EFFECTIVE_END_DATE;
378
379 procedure initialise is
380 begin
381 p_info_source:='zzzz' ;
382 p_tax_basis:='zzzz' ;
383 p_weekly_tax_credit:=0;
384 p_monthly_tax_credit:=0;
385 p_weekly_std_rate_cutoff:=0;
386 p_monthly_std_rate_cutoff:=0;
387 p_certificate_start_date:=to_date('01-01-0001','DD-MM-YYYY');
388 p_certificate_end_date:=to_date('01-01-0001','DD-MM-YYYY');
389 /********************************/
390 p_reduced_tax_credit:=0;
391 p_reduced_std_rate_cutoff:=0;
392 p_benefit_amount:=0;
393 /**************************/
394 end;
395
396 --end Local vriables---------
397
398 begin
399
400 hr_utility.set_location('Entering:'||l_proc, 5);
401 /********************************/
402 p_reduced_tax_credit:=0;
403 p_reduced_std_rate_cutoff:=0;
404 p_benefit_amount:=0;
405 /**************************/
406 open c_paye_dtl;
407
408 fetch c_paye_dtl into l_payroll_id
409 ,l_date_earned
410 ,p_info_source
411 ,p_tax_basis
412 ,p_weekly_tax_credit
413 ,p_monthly_tax_credit
414 ,p_weekly_std_rate_cutoff
415 ,p_monthly_std_rate_cutoff
416 ,p_certificate_start_date
417 ,p_certificate_end_date
418 /*changes for SOE form requirements*/
419 ,p_assess_basis
420 ,p_certificate_issue_date
421 ,l_period_type;
422
423 if c_paye_dtl%notfound then
424 initialise;
425 return 0;
426 end if;
427
428 /* Getting calculated values as per the period type*/
429 if (l_period_type ='Week'
430 or l_period_type ='Bi-Week'
431 or l_period_type='Lunar Month')
432 then
433 l_period_ind := 'W';
434 p_weekly_tax_credit := get_calculated_period_values(l_period_type,
435 l_period_ind,
436 p_weekly_tax_credit);
437 p_weekly_std_rate_cutoff := get_calculated_period_values(l_period_type,
438 l_period_ind,
439 p_weekly_std_rate_cutoff);
440
441 elsif (l_period_type ='Bi-Month' or
442 l_period_type ='Calendar Month' or
443 l_period_type='Quarter' or
444 l_period_type = 'Semi-Month' or
445 l_period_type = 'Semi-Year' or
446 l_period_type ='Year')
447 then
448 l_period_ind :='M';
449 p_monthly_tax_credit := get_calculated_period_values(l_period_type,
450 l_period_ind,
451 p_monthly_tax_credit);
452 p_monthly_std_rate_cutoff := get_calculated_period_values(l_period_type,
453 l_period_ind,
454 p_monthly_std_rate_cutoff);
455
456 end if;
457
458 -- Bug 2943335 - commented code below to reference element entries table for data
459 /*Social Benefits stuff*/
460 /* open c_soc_ben(l_payroll_id,l_date_earned);
461 fetch c_soc_ben into l_soc_ben_rec.calculation_option
462 ,l_soc_ben_rec.reduced_tax_credit
463 ,l_soc_ben_rec.reduced_standard_cutoff
464 ,l_soc_ben_rec.benefit_amount;
465 if c_soc_ben%found then
466
467 -- getting calculated values according to the period
468 l_cal_reduced_tax_credit := get_calculated_period_values(l_period_type,l_period_ind,l_soc_ben_rec.reduced_tax_credit);
469 l_cal_reduced_cut_off:= get_calculated_period_values(l_period_type,l_period_ind,l_soc_ben_rec.reduced_standard_cutoff);
470
471 if l_soc_ben_rec.calculation_option = 'IE_OPTION1' then
472 --
473 p_benefit_amount:=l_soc_ben_rec.benefit_amount;
474 --
475 elsif l_soc_ben_rec.calculation_option = 'IE_OPTION2' then
476 --
477 if l_period_ind = 'W'
478 then
479 p_reduced_tax_credit:=p_weekly_tax_credit - l_cal_reduced_tax_credit;
480 p_reduced_std_rate_cutoff:=p_weekly_std_rate_cutoff - l_cal_reduced_cut_off;
481 elsif l_period_ind = 'M'
482 then
483 p_reduced_tax_credit:=p_monthly_tax_credit - l_cal_reduced_tax_credit;
484 p_reduced_std_rate_cutoff:=p_monthly_std_rate_cutoff - l_cal_reduced_cut_off;
485 end if;
486
487 p_benefit_amount:=l_soc_ben_rec.benefit_amount;
488 p_weekly_tax_credit:= l_cal_reduced_tax_credit;
489 p_monthly_tax_credit:= l_cal_reduced_tax_credit;
490 p_weekly_std_rate_cutoff:= l_cal_reduced_cut_off;
491 p_monthly_std_rate_cutoff:= l_cal_reduced_cut_off;
492 --
493 elsif l_soc_ben_rec.calculation_option = 'IE_OPTION3' then
494 --
495 if (l_period_ind='W')
496 then
497 p_reduced_tax_credit:=p_weekly_tax_credit - l_cal_reduced_tax_credit;
498 p_reduced_std_rate_cutoff:=p_weekly_std_rate_cutoff - l_cal_reduced_cut_off;
499 elsif l_period_ind='M'
500 then
501 p_reduced_tax_credit:=p_monthly_tax_credit - l_cal_reduced_tax_credit;
502 p_reduced_std_rate_cutoff:=p_monthly_std_rate_cutoff - l_cal_reduced_cut_off;
503 end if;
504
505 p_benefit_amount:=l_soc_ben_rec.benefit_amount;
506 p_weekly_tax_credit:= l_cal_reduced_tax_credit;
507 p_monthly_tax_credit:= l_cal_reduced_tax_credit;
508 p_weekly_std_rate_cutoff:= l_cal_reduced_cut_off;
509 p_monthly_std_rate_cutoff:= l_cal_reduced_cut_off;
510 p_tax_basis:='IE_WEEK1_MONTH1';
511 --
512 elsif l_soc_ben_rec.calculation_option = 'IE_OPTION4' then
513 --
514 p_tax_basis:='IE_WEEK1_MONTH1';
515 p_benefit_amount:=l_soc_ben_rec.benefit_amount;
516 --
517 end if;
518 end if;
519
520 close c_soc_ben; */
521 --
522 -- Bug 2943335 - Fetch the sum of benefit amount am employee has in this period
523
524 open cur_c_soc_ben;
525 fetch cur_c_soc_ben into total_benefit_amount;
526 close cur_c_soc_ben;
527 -- @D:/Comm/IE/Social_ben/pyietax.pkb
528 p_benefit_amount := nvl(total_benefit_amount,0);
529 hr_utility.set_location('benefit amt:'||p_benefit_amount, 15);
530 hr_utility.set_location('p_monthly_tax_credit: '||p_monthly_tax_credit, 25);
531 hr_utility.set_location('p_monthly_std_rate_cutoff:'||p_monthly_std_rate_cutoff, 35);
532
533 close c_paye_dtl;
534 hr_utility.set_location('Leaving:'||l_proc, 30);
535 return 1;
536
537 exception when others then
538 initialise;
539 close c_paye_dtl;
540 raise_application_error(-20001,l_proc||'- '||sqlerrm);
541 return 0;
542
543 end get_paye_details;
544 -- 13359545
545 Function get_usc_details(p_assignment_id in number
546 ,p_payroll_action_id in number
547 ,p_tax_basis out nocopy varchar2
548 ,p_usc_tax_basis out nocopy varchar2
549 ,p_usc_rate_1 out nocopy number
550 ,p_usc_yrly_cutoff_1 out nocopy number
551 ,p_usc_mthly_cutoff_1 out nocopy number
552 ,p_usc_wkly_cutoff_1 out nocopy number
553 ,p_usc_rate_2 out nocopy number
554 ,p_usc_yrly_cutoff_2 out nocopy number
555 ,p_usc_mthly_cutoff_2 out nocopy number
556 ,p_usc_wkly_cutoff_2 out nocopy number
557 ,p_usc_rate_3 out nocopy number
558 ,p_usc_yrly_cutoff_3 out nocopy number
559 ,p_usc_mthly_cutoff_3 out nocopy number
560 ,p_usc_wkly_cutoff_3 out nocopy number
561 ,p_usc_rate_4 out nocopy number
562 ,p_usc_yrly_cutoff_4 out nocopy number
563 ,p_usc_mthly_cutoff_4 out nocopy number
564 ,p_usc_wkly_cutoff_4 out nocopy number
565 ,p_usc_rate_5 out nocopy number)
566 /*************************************************/
567 return number is
568
569
570 l_proc varchar2(72) := g_package||'get_usc_details';
571
572 cursor c_usc_dtl is select tax_basis
573 ,nvl(usc_tax_basis,'0')
574 ,nvl(usc_rate_1,0)
575 ,nvl(usc_yrly_cutoff_1 ,0)
576 ,nvl(usc_mthly_cutoff_1 ,0)
577 ,nvl(usc_wkly_cutoff_1 ,0)
578 ,nvl(usc_rate_2,0)
579 ,nvl(usc_yrly_cutoff_2 ,0)
580 ,nvl(usc_mthly_cutoff_2 ,0)
581 ,nvl(usc_wkly_cutoff_2,0)
582 ,nvl(usc_rate_3,0)
583 ,nvl(usc_yrly_cutoff_3 ,0)
584 ,nvl(usc_mthly_cutoff_3 ,0)
585 ,nvl(usc_wkly_cutoff_3 ,0)
586 ,nvl(usc_rate_4,0)
587 ,nvl(usc_yrly_cutoff_4 ,0)
588 ,nvl(usc_mthly_cutoff_4 ,0)
589 ,nvl(usc_wkly_cutoff_4 ,0)
590 ,nvl(usc_rate_5 ,0)
591 from pay_ie_paye_details_f pipd
592 ,pay_payroll_actions ppa
593 where pipd.assignment_id = p_assignment_id
594 and ppa.payroll_action_id = p_payroll_action_id
595 and ppa.effective_date between pipd.effective_start_date and
596 nvl(pipd.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
597 and pipd.info_source in ('IE_P45','IE_ELECTRONIC','IE_CERT_TAX_CREDITS','IE_NONE_PROVIDED');
598
599
600 procedure initialise is
601 begin
602 p_tax_basis:='zzzz' ;
603 p_usc_tax_basis:='zzzz' ;
604 p_usc_rate_1:=0;
605 p_usc_yrly_cutoff_1 :=0;
606 p_usc_mthly_cutoff_1 :=0;
607 p_usc_wkly_cutoff_1 :=0;
608 p_usc_rate_2 :=0;
609 p_usc_yrly_cutoff_2 :=0;
610 p_usc_mthly_cutoff_2 :=0;
611 p_usc_wkly_cutoff_2 :=0;
612 p_usc_rate_3 :=0;
613 p_usc_yrly_cutoff_3 :=0;
614 p_usc_mthly_cutoff_3 :=0;
615 p_usc_wkly_cutoff_3:=0;
616 p_usc_rate_4 :=0;
617 p_usc_yrly_cutoff_4 :=0;
618 p_usc_mthly_cutoff_4 :=0;
619 p_usc_wkly_cutoff_4 :=0;
620 p_usc_rate_5 :=0;
621
622 end;
623
624
625 begin
626
627 hr_utility.set_location('Entering:'||l_proc, 5);
628
629 open c_usc_dtl;
630
631 fetch c_usc_dtl into p_tax_basis
632 ,p_usc_tax_basis
633 ,p_usc_rate_1
634 ,p_usc_yrly_cutoff_1
635 ,p_usc_mthly_cutoff_1
636 ,p_usc_wkly_cutoff_1
637 ,p_usc_rate_2
638 ,p_usc_yrly_cutoff_2
639 ,p_usc_mthly_cutoff_2
640 ,p_usc_wkly_cutoff_2
641 ,p_usc_rate_3
642 ,p_usc_yrly_cutoff_3
643 ,p_usc_mthly_cutoff_3
644 ,p_usc_wkly_cutoff_3
645 ,p_usc_rate_4
646 ,p_usc_yrly_cutoff_4
647 ,p_usc_mthly_cutoff_4
648 ,p_usc_wkly_cutoff_4
649 ,p_usc_rate_5 ;
650
651 if c_usc_dtl%notfound then
652 initialise;
653 return 0;
654 end if;
655 close c_usc_dtl;
656 hr_utility.set_location('Leaving:'||l_proc, 30);
657 return 1;
658
659 exception when others then
660 initialise;
661 close c_usc_dtl;
662 raise_application_error(-20001,l_proc||'- '||sqlerrm);
663 return 0;
664
665 end get_usc_details;
666
667 Function get_payroll_details( p_payroll_id in number
668 ,p_payroll_action_id in number
669 ,p_period_num out nocopy number
670 ,p_payroll_type out nocopy varchar2) return number is
671
672 cursor c_payroll_details is select ptp.period_num
673 ,ptp.period_type
674 from per_time_periods ptp,
675 pay_all_payrolls pap,
676 pay_payroll_actions ppa
677 where pap.payroll_id = ptp.payroll_id
678 and pap.payroll_id=p_payroll_id
679 and ppa.payroll_id=pap.payroll_id
680 and ppa.payroll_action_id=p_payroll_action_id
681 and ppa.date_earned between ptp.start_date and ptp.end_date;
682
683 l_proc varchar2(72) := g_package||'get_payroll_details';
684
685 begin
686
687 hr_utility.set_location('Entering:'||l_proc, 35);
688
689 open c_payroll_details;
690 fetch c_payroll_details into p_period_num
691 ,p_payroll_type;
692 close c_payroll_details;
693
694 hr_utility.set_location('Leaving:'||l_proc, 50);
695
696 return 1;
697 exception when others then
698 return 0;
699
700 end;
701 --
702 FUNCTION get_calculated_period_values(p_period_type IN VARCHAR2,
703 p_period_ind IN VARCHAR2,
704 p_actual_value IN NUMBER) RETURN NUMBER IS
705
706 l_calculated_value NUMBER;
707 l_number_per_year NUMBER;
708
709 CURSOR csr_number_per_year IS
710 SELECT number_per_fiscal_year
711 FROM per_time_period_types
712 WHERE period_type =p_period_type;
713
714 BEGIN
715
716 OPEN csr_number_per_year;
717 FETCH csr_number_per_year INTO l_number_per_year;
718 CLOSE csr_number_per_year;
719
720 IF p_period_ind = 'M' THEN
721 l_calculated_value := p_actual_value * 12/l_number_per_year;
722
723 ELSIF p_period_ind='W' THEN
724 l_calculated_value := p_actual_value * 52/l_number_per_year;
725
726 END IF;
727
728 RETURN l_calculated_value;
729
730 END get_calculated_period_values;
731 --
732 --
733 Function get_pps_number(p_assignment_id IN NUMBER,
734 p_payroll_action_id IN NUMBER) RETURN NUMBER IS
735 --
736 l_pps_number VARCHAR2(30);
737 l_tax_basis VARCHAR2(30);
738 l_func VARCHAR2(14):= 'get_pps_number';
739
740 Cursor csr_pps_number IS
741 SELECT nvl(pap.national_identifier, 'X')
742 FROM per_all_people_f pap
743 ,per_all_assignments_f paa
744 ,pay_payroll_actions ppa
745 WHERE ppa.payroll_action_id = p_payroll_action_id
746 and paa.assignment_id = p_assignment_id
747 and ppa.effective_date between paa.effective_start_date and paa.effective_end_date
748 and paa.person_id = pap.person_id
749 and ppa.effective_date between pap.effective_start_date and pap.effective_end_date;
750
751 Cursor csr_emer_no_pps_basis IS
752 SELECT nvl(pipd.tax_basis, 'X')
753 from pay_ie_paye_details_f pipd,
754 pay_payroll_actions ppa
755 WHERE ppa.payroll_action_id = p_payroll_action_id
756 and pipd.assignment_id = p_assignment_id
757 and ppa.effective_date between pipd.effective_start_date and pipd.effective_end_date;
758 --
759 Begin
760 hr_utility.set_location('Entering : '||l_func, 10);
761 OPEN csr_pps_number;
762 FETCH csr_pps_number into l_pps_number;
763 CLOSE csr_pps_number;
764 --
765 IF l_pps_number = 'X' then
766 hr_utility.set_location('In : '||l_func, 20);
767 RETURN 1;
768 ELSE
769 hr_utility.set_location('In : '||l_func, 30);
770 OPEN csr_emer_no_pps_basis;
771 FETCH csr_emer_no_pps_basis into l_tax_basis;
772 CLOSE csr_emer_no_pps_basis;
773 IF l_tax_basis IS NULL THEN
774 hr_utility.set_location('In : '||l_func, 35);
775 l_tax_basis := 'X';
776 END IF;
777 IF l_tax_basis <> 'IE_EMERGENCY_NO_PPS' THEN
778 hr_utility.set_location('In : '||l_func, 40);
779 RETURN 0;
780 ELSE
781 hr_utility.set_location('In : '||l_func, 50);
782 RETURN 1;
783 END IF;
784 hr_utility.set_location('In : '||l_func, 60);
785 END IF;
786 hr_utility.set_location('In : '||l_func, 70);
787 END get_pps_number;
788 --
789 -- Bug 2943335 added function to see if work incident exist for the person
790 -- This would return true if the work incident
791 -- entered on the element entry screen exists for the person
792 --
793 function Valid_Work_incidents
794 (p_assignment_id in number
795 ,p_date_earned in date
796 ,p_reference in varchar2) return varchar2 is
797 --
798 l_valid varchar2(10);
799 cursor csr_find_match is
800 select 'TRUE'
801 from per_all_assignments_f asg,
802 per_work_incidents pwi,
803 hr_lookups hl
804 where p_date_earned between asg.effective_start_date
805 and asg.effective_end_date and
806 p_assignment_id = asg.assignment_id
807 and pwi.PERSON_ID = asg.PERSON_ID
808 and hl.lookup_type = 'INCIDENT_TYPE'
809 and pwi.INCIDENT_TYPE = hl.lookup_code
810 and hl.meaning = p_reference ;
811 --
812 BEGIN
813 open csr_find_match;
814 fetch csr_find_match into l_valid;
815 if csr_find_match%NOTFOUND then
816 l_valid := 'FALSE';
817 end if;
818 close csr_find_match;
819 return l_valid;
820 END Valid_Work_incidents;
821
822 /* Added following two procedures as user hooks for BUG 3030621 */
823 procedure insert_element_entry
824 (p_element_entry_id in number
825 )is
826 l_procedure_name varchar2(61) := 'hr_ie_element_entry_hook.insert_element_name' ;
827 l_absence_start_date varchar2(30);
828 l_absence_end_date varchar2(30);
829 --
830 begin
831 --
832 -- Added for GSI Bug 5472781
833 --
834 IF hr_utility.chk_product_install('Oracle Human Resources', 'IE') THEN
835 --
836 open g_absence_dates (p_element_entry_id);
837 for i in 1..2 loop
838 if i=1 then
839 fetch g_absence_dates
840 into l_absence_start_date;
841 elsif i=2 then
842 fetch g_absence_dates
843 into l_absence_end_date;
844 end if;
845 end loop;
846 close g_absence_dates ;
847 hr_utility.trace('In: ' || l_procedure_name) ;
848 if l_absence_start_date is not null and l_absence_end_date is not null then
849 if FND_DATE.CANONICAL_TO_DATE(l_absence_start_date) > FND_DATE.CANONICAL_TO_DATE(l_absence_end_date) then
850 hr_utility.set_message(801,'HR_IE_SOCIAL_BENEFIT_DATES');
851 hr_utility.raise_error;
852 end if;
853 end if;
854 hr_utility.trace('Out: ' || l_procedure_name) ;
855 END IF;
856 end insert_element_entry ;
857 --
858 procedure update_element_entry
859 ( p_element_entry_id in number
860 ) is
861 l_procedure_name varchar2(61) := 'hr_ie_element_entry_hook.update_element_name' ;
862 l_absence_start_date varchar2(30);
863 l_absence_end_date varchar2(30);
864 begin
865 --
866 -- Added for GSI Bug 5472781
867 --
868 IF hr_utility.chk_product_install('Oracle Human Resources', 'IE') THEN
869 --
870 open g_absence_dates (p_element_entry_id);
871 for i in 1..2 loop
872 if i=1 then
873 fetch g_absence_dates
874 into l_absence_start_date;
875 elsif i=2 then
876 fetch g_absence_dates
877 into l_absence_end_date;
878 end if;
879 end loop;
880 close g_absence_dates ;
881 hr_utility.trace('In: ' || l_procedure_name) ;
882 if l_absence_start_date is not null and l_absence_end_date is not null then
883 if FND_DATE.CANONICAL_TO_DATE(l_absence_start_date) > FND_DATE.CANONICAL_TO_DATE(l_absence_end_date) then
884 hr_utility.set_message(801,'HR_IE_SOCIAL_BENEFIT_DATES');
885 hr_utility.raise_error;
886 end if;
887 end if;
888 hr_utility.trace('Out: ' || l_procedure_name) ;
889 END IF;
890 end update_element_entry ;
891 --
892 /* End of BUG 3030621 */
893 /*ADDED FOUR FUNCTIONS FOR BUG 3030616 */
894 --
895 function get_monthly_std_rate_cut_off
896 (p_assignment_id in pay_ie_paye_details_f.ASSIGNMENT_ID%TYPE,
897 p_tax_basis in pay_ie_paye_details_f.TAX_BASIS%TYPE)
898 RETURN number
899 is
900 CURSOR get_global_val(l_name IN VARCHAR2) IS
901 SELECT global_value
902 FROM ff_globals_f,fnd_sessions ses
903 WHERE global_name = l_name
904 AND ses.session_id = userenv('SESSIONID')
905 AND ses.effective_date BETWEEN effective_start_date AND effective_end_date;
906 --
907 CURSOR get_pay_frequency_csr IS
908 SELECT pp.period_type
909 FROM pay_all_payrolls_f pp, per_all_assignments_f pa,fnd_sessions ses
910 WHERE pa.assignment_id = p_assignment_id
911 AND ses.session_id = userenv('SESSIONID')
912 AND ses.effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
913 AND pp.payroll_id = pa.payroll_id
914 AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
915 --
916 CURSOR monthly_std_rate_cut_off IS
917 SELECT nvl(pp.monthly_std_rate_cut_off,0)
918 FROM pay_ie_paye_details_f pp,fnd_sessions ses
919 WHERE pp.assignment_id=p_assignment_id
920 AND ses.session_id = userenv('SESSIONID')
921 AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
922 --
923 get_pay_frequency_rec get_pay_frequency_csr%ROWTYPE;
924 v_monthly_std_rate_cut_off number;
925 BEGIN
926 --
927 OPEN get_pay_frequency_csr;
928 FETCH get_pay_frequency_csr INTO get_pay_frequency_rec;
929 CLOSE get_pay_frequency_csr;
930 --
931 IF p_tax_basis='IE_EMERGENCY' THEN
932 IF get_pay_frequency_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year') THEN
933 OPEN get_global_val('IE_MONTHLY_STANDARD_RATE_CUT_OFF');
934 FETCH get_global_val INTO v_monthly_std_rate_cut_off;
935 CLOSE get_global_val;
936 ELSE
937 v_monthly_std_rate_cut_off:= NULL;
938 --
939 END IF;
940 ELSIF p_tax_basis='IE_EMERGENCY_NO_PPS' THEN
941 v_monthly_std_rate_cut_off:= NULL;
942 ELSE
943 IF get_pay_frequency_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year')
944 THEN
945 OPEN monthly_std_rate_cut_off;
946 FETCH monthly_std_rate_cut_off INTO v_monthly_std_rate_cut_off;
947 CLOSE monthly_std_rate_cut_off;
948 ELSE
949 v_monthly_std_rate_cut_off:= NULL;
950 END IF;
951 END IF;
952 --
953 RETURN v_monthly_std_rate_cut_off;
954 --
955 END get_monthly_std_rate_cut_off;
956 --
957 function get_monthly_tax_credit
958 (p_assignment_id in pay_ie_paye_details_f.ASSIGNMENT_ID%TYPE,
959 p_tax_basis in pay_ie_paye_details_f.TAX_BASIS%TYPE)
960 RETURN number
961 is
962 CURSOR get_global_val(l_name IN VARCHAR2) IS
963 SELECT global_value
964 FROM ff_globals_f,fnd_sessions ses
965 WHERE global_name = l_name
966 AND ses.session_id = userenv('SESSIONID')
967 AND ses.effective_date BETWEEN effective_start_date AND effective_end_date;
968 --
969 CURSOR get_pay_frequency_csr IS
970 SELECT pp.period_type
971 FROM pay_all_payrolls_f pp, per_all_assignments_f pa,fnd_sessions ses
972 WHERE pa.assignment_id = p_assignment_id
973 AND ses.session_id = userenv('SESSIONID')
974 AND ses.effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
975 AND pp.payroll_id = pa.payroll_id
976 AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
977 --
978 CURSOR monthly_tax_credit IS
979 SELECT nvl(pp.monthly_tax_credit,0)
980 FROM pay_ie_paye_details_f pp,fnd_sessions ses
981 WHERE pp.assignment_id=p_assignment_id
982 AND ses.session_id = userenv('SESSIONID')
983 AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
984 --
985 get_pay_frequency_rec get_pay_frequency_csr%ROWTYPE;
986 v_get_monthly_tax_credit number;
987 --
988 BEGIN
989 --
990 OPEN get_pay_frequency_csr;
991 FETCH get_pay_frequency_csr INTO get_pay_frequency_rec;
992 CLOSE get_pay_frequency_csr;
993 --
994 IF p_tax_basis='IE_EMERGENCY' THEN
995 IF get_pay_frequency_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year') THEN
996 --
997 OPEN get_global_val('IE_MONTHLY_TAX_CREDIT');
998 FETCH get_global_val INTO v_get_monthly_tax_credit;
999 CLOSE get_global_val;
1000 --
1001 ELSE
1002 v_get_monthly_tax_credit:= NULL;
1003 --
1004 END IF;
1005 --
1006 ELSIF p_tax_basis='IE_EMERGENCY_NO_PPS' THEN
1007 v_get_monthly_tax_credit:= NULL;
1008 --
1009 ELSE
1010 IF get_pay_frequency_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year')
1011 THEN
1012 OPEN monthly_tax_credit ;
1013 FETCH monthly_tax_credit INTO v_get_monthly_tax_credit;
1014 CLOSE monthly_tax_credit;
1015 ELSE
1016 v_get_monthly_tax_credit:= 0;
1017 END IF;
1018 END IF;
1019 --
1020 RETURN v_get_monthly_tax_credit;
1021 --
1022 END get_monthly_tax_credit;
1023 --
1024 function get_weekly_std_rate_cut_off
1025 (p_assignment_id in pay_ie_paye_details_f.ASSIGNMENT_ID%TYPE,
1026 p_tax_basis in pay_ie_paye_details_f.TAX_BASIS%TYPE)
1027 RETURN number
1028 is
1029 CURSOR get_global_val(l_name IN VARCHAR2) IS
1030 SELECT global_value
1031 FROM ff_globals_f,fnd_sessions ses
1032 WHERE global_name = l_name
1033 AND ses.session_id = userenv('SESSIONID')
1034 AND ses.effective_date BETWEEN effective_start_date AND effective_end_date;
1035 --
1036 CURSOR get_pay_frequency_csr IS
1037 SELECT pp.period_type
1038 FROM pay_all_payrolls_f pp, per_all_assignments_f pa,fnd_sessions ses
1039 WHERE pa.assignment_id = p_assignment_id
1040 AND ses.session_id = userenv('SESSIONID')
1041 AND ses.effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
1042 AND pp.payroll_id = pa.payroll_id
1043 AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
1044 --
1045 CURSOR weekly_std_rate_cut_off IS
1046 SELECT nvl(pp.weekly_std_rate_cut_off,0)
1047 FROM pay_ie_paye_details_f pp,fnd_sessions ses
1048 WHERE pp.assignment_id=p_assignment_id
1049 AND ses.session_id = userenv('SESSIONID')
1050 AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
1051 --
1052 get_pay_frequency_rec get_pay_frequency_csr%ROWTYPE;
1053 v_weekly_std_rate_cut_off number;
1054 BEGIN
1055 --
1056 OPEN get_pay_frequency_csr;
1057 FETCH get_pay_frequency_csr INTO get_pay_frequency_rec;
1058 CLOSE get_pay_frequency_csr;
1059 --
1060 IF p_tax_basis='IE_EMERGENCY' THEN
1061 IF get_pay_frequency_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year') THEN
1062 v_weekly_std_rate_cut_off:= NULL;
1063 --
1064 ELSE
1065 OPEN get_global_val('IE_WEEKLY_STANDARD_RATE_CUT_OFF');
1066 FETCH get_global_val INTO v_weekly_std_rate_cut_off;
1067 CLOSE get_global_val;
1068 --
1069 END IF;
1070 ELSIF p_tax_basis='IE_EMERGENCY_NO_PPS' THEN
1071 v_weekly_std_rate_cut_off:= NULL;
1072 ELSE
1073 IF get_pay_frequency_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year')
1074 THEN
1075 v_weekly_std_rate_cut_off:= NULL;
1076 ELSE
1077 OPEN weekly_std_rate_cut_off;
1078 FETCH weekly_std_rate_cut_off INTO v_weekly_std_rate_cut_off;
1079 CLOSE weekly_std_rate_cut_off;
1080 END IF;
1081 END IF;
1082 --
1083 RETURN v_weekly_std_rate_cut_off;
1084 --
1085 END get_weekly_std_rate_cut_off;
1086 --
1087 function get_weekly_tax_credit
1088 (p_assignment_id in pay_ie_paye_details_f.ASSIGNMENT_ID%TYPE,
1089 p_tax_basis in pay_ie_paye_details_f.TAX_BASIS%TYPE)
1090 RETURN number
1091 is
1092 CURSOR get_global_val(l_name IN VARCHAR2) IS
1093 SELECT global_value
1094 FROM ff_globals_f,fnd_sessions ses
1095 WHERE global_name = l_name
1096 AND ses.session_id = userenv('SESSIONID')
1097 AND ses.effective_date BETWEEN effective_start_date AND effective_end_date;
1098 --
1099 CURSOR get_pay_frequency_csr IS
1100 SELECT pp.period_type
1101 FROM pay_all_payrolls_f pp, per_all_assignments_f pa,fnd_sessions ses
1102 WHERE pa.assignment_id = p_assignment_id
1103 AND ses.session_id = userenv('SESSIONID')
1104 AND ses.effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
1105 AND pp.payroll_id = pa.payroll_id
1106 AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
1107 --
1108 CURSOR weekly_tax_credit IS
1109 SELECT nvl(pp.weekly_tax_credit,0)
1110 FROM pay_ie_paye_details_f pp,fnd_sessions ses
1111 WHERE pp.assignment_id=p_assignment_id
1112 AND ses.session_id = userenv('SESSIONID')
1113 AND ses.effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
1114 --
1115 get_pay_frequency_rec get_pay_frequency_csr%ROWTYPE;
1116 v_get_weekly_tax_credit number;
1117 BEGIN
1118 --
1119 OPEN get_pay_frequency_csr;
1120 FETCH get_pay_frequency_csr INTO get_pay_frequency_rec;
1121 CLOSE get_pay_frequency_csr;
1122 --
1123 IF p_tax_basis='IE_EMERGENCY' THEN
1124 IF get_pay_frequency_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year') THEN
1125 v_get_weekly_tax_credit:= NULL;
1126 --
1127 ELSE
1128 OPEN get_global_val('IE_WEEKLY_TAX_CREDIT');
1129 FETCH get_global_val INTO v_get_weekly_tax_credit;
1130 CLOSE get_global_val;
1131 END IF;
1132 ELSIF p_tax_basis='IE_EMERGENCY_NO_PPS' THEN
1133 v_get_weekly_tax_credit:= NULL;
1134 ELSE
1135 IF get_pay_frequency_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year')
1136 THEN
1137 v_get_weekly_tax_credit:= NULL;
1138 ELSE
1139 OPEN weekly_tax_credit ;
1140 FETCH weekly_tax_credit INTO v_get_weekly_tax_credit;
1141 CLOSE weekly_tax_credit;
1142 END IF;
1143 END IF;
1144 RETURN v_get_weekly_tax_credit;
1145 --
1146 END get_weekly_tax_credit;
1147 --
1148 /* End of BUG 3030616 */
1149
1150 /*-------------------- decode_value_char --------------------*/
1151 function decode_value_char(p_expression boolean,
1152 p_true varchar2,
1153 p_false varchar2) return varchar2 is
1154 begin
1155 if p_expression then
1156 return p_true;
1157 else
1158 return p_false;
1159 end if;
1160
1161 end decode_value_char;
1162
1163 /*-------------------- decode_value_date --------------------*/
1164 function decode_value_date(p_expression boolean,
1165 p_true date,
1166 p_false date) return date is
1167 begin
1168 if p_expression then
1169 return p_true;
1170 else
1171 return p_false;
1172 end if;
1173
1174 end decode_value_date;
1175
1176
1177 /*-------------------- decode_value_number --------------------*/
1178 function decode_value_number(p_expression boolean,
1179 p_true number,
1180 p_false number) return number is
1181 begin
1182 if p_expression then
1183 return p_true;
1184 else
1185 return p_false;
1186 end if;
1187
1188 end decode_value_number;
1189
1190
1191
1192 /*Bug 4080773*/
1193
1194 PROCEDURE update_paye_change_freq(p_assignment_id number
1195 ,p_effective_date date
1196 ,p_payroll_id number
1197 ,P_DATETRACK_UPDATE_MODE VARCHAR2
1198 ,p_tax_upload_flag varchar2 default 'X'
1199 ,p_tax_basis varchar2 default null
1200 ,p_cert_start_date date default null -- 17140460.6
1201 ,p_cert_end_date date default null
1202 ,p_weekly_tax_credit number default null
1203 ,p_monthly_tax_credit number default null
1204 ,p_weekly_std_rate_cut_off number default null
1205 ,p_monthly_std_rate_cut_off number default null
1206 ,p_tax_deducted_to_date number default null
1207 ,p_pay_to_date number default null
1208 ,p_cert_date date
1209 --13359423
1210 ,p_yrly_tax_cred in number default null
1211 ,p_yrly_tax_rate_1 in number default null
1212 ,p_yrly_tax_rate_2 in number default null
1213 ,p_mthly_tax_rate_2 in number default null
1214 ,p_wkly_tax_rate_2 in number default null
1215 ,p_tax_rate_3 in number default null
1216 ,p_yrly_tax_rate_3 in number default null
1217 ,p_mthly_tax_rate_3 in number default null
1218 ,p_wkly_tax_rate_3 in number default null
1219 ,p_tax_rate_4 in number default null
1220 ,p_yrly_tax_rate_4 in number default null
1221 ,p_mthly_tax_rate_4 in number default null
1222 ,p_wkly_tax_rate_4 in number default null
1223 ,p_tax_rate_5 in number default null
1224 ,p_in_exempt_usc in varchar2 default null
1225 ,p_total_usc_pay_todate in number default null
1226 ,p_total_usc_tax_todate in number default null
1227 ,p_usc_rate_1 in number default null
1228 ,p_usc_yrly_cutoff_1 in number default null
1229 ,p_usc_mthly_cutoff_1 in number default null
1230 ,p_usc_wkly_cutoff_1 in number default null
1231 ,p_usc_rate_2 in number default null
1232 ,p_usc_yrly_cutoff_2 in number default null
1233 ,p_usc_mthly_cutoff_2 in number default null
1234 ,p_usc_wkly_cutoff_2 in number default null
1235 ,p_usc_rate_3 in number default null
1236 ,p_usc_yrly_cutoff_3 in number default null
1237 ,p_usc_mthly_cutoff_3 in number default null
1238 ,p_usc_wkly_cutoff_3 in number default null
1239 ,p_usc_rate_4 in number default null
1240 ,p_usc_yrly_cutoff_4 in number default null
1241 ,p_usc_mthly_cutoff_4 in number default null
1242 ,p_usc_wkly_cutoff_4 in number default null
1243 ,p_usc_rate_5 in number default null
1244 ,p_usc_tax_basis in varchar2 default null
1245 ,p_usc_info_source in varchar2 default null
1246 --13359423
1247 ) is --4878630
1248
1249 Cursor c_effective_paye is select *
1250 from pay_ie_paye_details_f
1251 where p_effective_date between effective_start_date and effective_end_date
1252 and assignment_id = p_assignment_id
1253 order by effective_start_date asc;
1254
1255 cursor c_future_paye(p_paye_details_id number) is select *
1256 from pay_ie_paye_details_f
1257 where p_effective_date < effective_start_date
1258 and assignment_id = p_assignment_id
1259 and ((paye_details_id <> p_paye_details_id and p_paye_details_id is not null) or p_paye_details_id is null )
1260 order by effective_start_date asc;
1261
1262 /* Cusror added for tax credit upload */ --4878630
1263 Cursor c_tax_effective_paye(p_paye_id number,p_date date) is
1264 select *
1265 from pay_ie_paye_details_f
1266 where ((p_date < effective_start_date and trunc(p_date,'Y') = trunc(effective_start_date,'Y') and p_paye_id is null)
1267 or (paye_details_id <> p_paye_id and p_paye_id is not null and p_date < effective_start_date))
1268 and assignment_id = p_assignment_id
1269 order by effective_start_date desc;
1270
1271
1272 Cursor csr_get_assg(p_assignment_id in number,p_effective_date date) is
1273 SELECT payroll_id ,effective_start_date
1274 FROM per_all_assignments_f paa
1275 WHERE paa.assignment_id=p_assignment_id
1276 AND p_effective_date between paa.effective_start_date
1277 and paa.effective_end_date;
1278
1279 CURSOR get_global_val(l_name IN VARCHAR2,p_effective_date date) IS
1280 SELECT global_value
1281 FROM ff_globals_f
1282 WHERE global_name = l_name
1283 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1284
1285 Cursor csr_freq(p_payroll_id number,p_effective_date date) IS
1286 SELECT 1
1287 FROM pay_all_payrolls_f pp
1288 WHERE pp.payroll_id = p_payroll_id
1289 AND p_effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date
1290 AND period_type IN ('Calendar Month', 'Quarter', 'Bi-Month', 'Semi-Month', 'Semi-Year', 'Year');
1291
1292
1293 c_effective_paye_fetch c_effective_paye%rowtype;
1294 c_future_paye_fetch c_future_paye%rowtype;
1295 c_tax_upload_paye c_tax_effective_paye%rowtype;
1296 l_asg_effective_start_date date;
1297 l_start_date date;
1298 l_end_date date;
1299 l_api_ovn NUMBER;
1300 l_monthly_tax_credit NUMBER:=0;
1301 l_monthly_std_rate_cut_off NUMBER:=0;
1302 l_weekly_tax_credit NUMBER:=0;
1303 l_weekly_std_rate_cut_off NUMBER:=0;
1304 l_tax_basis varchar2(20):='IE_CUMULATIVE';
1305 l_info_source varchar2(20):='IE_NONE_PROVIDED';
1306 l_tax_assess_basis varchar2(20):='IE_SEP_ASSESS';
1307 l_certificate_issue_date date;
1308 l_certificate_end_date date;
1309 p_update_mode varchar2(20);
1310 l_assignment_id number;
1311 L_PRIM_PAYROLL_ID number;
1312 L_EFFECTIVE_DATE date;
1313 l_old_flag NUMBER:=0;
1314 l_new_flag NUMBER:=0;
1315 L_MIN_PAYE_ID number;
1316 L_MIN_EFFECTIVE_DATE date;
1317 L_NEW_PAYROLL_ID number;
1318 L_CERTIFICATE_START_DATE date;
1319 l_futrec_effective_end_date date;
1320 l_max_paye_id pay_ie_paye_details_f.paye_details_id%TYPE; --4878630
1321 l_max_effective_start_date date; --4878630
1322
1323 --13359423
1324 l_yrly_tax_cred number;
1325 l_yrly_tax_rate_1 number;
1326 l_yrly_tax_rate_2 number;
1327 l_mthly_tax_rate_2 number;
1328 l_wkly_tax_rate_2 number;
1329 l_tax_rate_3 number;
1330 l_yrly_tax_rate_3 number;
1331 l_mthly_tax_rate_3 number;
1332 l_wkly_tax_rate_3 number;
1333 l_tax_rate_4 number;
1334 l_yrly_tax_rate_4 number;
1335 l_mthly_tax_rate_4 number;
1336 l_wkly_tax_rate_4 number;
1337 l_tax_rate_5 number;
1338 l_in_exempt_usc varchar2(1);
1339 l_total_usc_pay_todate number;
1340 l_total_usc_tax_todate number;
1341 l_usc_rate_1 number;
1342 l_usc_yrly_cutoff_1 number;
1343 l_usc_mthly_cutoff_1 number;
1344 l_usc_wkly_cutoff_1 number;
1345 l_usc_rate_2 number;
1346 l_usc_yrly_cutoff_2 number;
1347 l_usc_mthly_cutoff_2 number;
1348 l_usc_wkly_cutoff_2 number;
1349 l_usc_rate_3 number;
1350 l_usc_yrly_cutoff_3 number;
1351 l_usc_mthly_cutoff_3 number;
1352 l_usc_wkly_cutoff_3 number;
1353 l_usc_rate_4 number;
1354 l_usc_yrly_cutoff_4 number;
1355 l_usc_mthly_cutoff_4 number;
1356 l_usc_wkly_cutoff_4 number;
1357 l_usc_rate_5 number;
1358 l_usc_tax_basis varchar2(30);
1359 l_usc_info_source varchar2(30);
1360 --13359423
1361
1362 BEGIN
1363 l_new_payroll_id := p_payroll_id;
1364 l_effective_date :=p_effective_date;
1365 l_assignment_id := p_assignment_id;
1366 /*Checking whether pay frequency is changed or not*/
1367 hr_utility.set_location('In update_paye_change_freq',840);
1368 hr_utility.set_location('effective date..'||l_effective_date,841);
1369 hr_utility.set_location('p_payroll_id..'||p_payroll_id,842);
1370 hr_utility.set_location('p_tax_upload_flag..'||p_tax_upload_flag,843);
1371 hr_utility.set_location('p_tax_basis..'|| p_tax_basis,844);
1372 hr_utility.set_location('p_cert_start_date..'|| p_cert_start_date,845);
1373 hr_utility.set_location('p_cert_end_date..'|| p_cert_end_date,846);
1374 hr_utility.set_location('p_weekly_tax_credit ..'|| p_weekly_tax_credit,847);
1375 hr_utility.set_location('p_monthly_tax_credit..'|| p_monthly_tax_credit,848);
1376 hr_utility.set_location('p_weekly_std_rate_cut_off..'|| p_weekly_std_rate_cut_off,849);
1377 hr_utility.set_location('p_monthly_std_rate_cut_off..'|| p_monthly_std_rate_cut_off,850);
1378 hr_utility.set_location('p_tax_deducted_to_date..'|| p_tax_deducted_to_date,851);
1379 hr_utility.set_location('p_pay_to_date..'|| p_pay_to_date,852);
1380
1381
1382 if l_new_payroll_id is not null then
1383 hr_utility.set_location('l_new_payroll_id is not null..'|| l_new_payroll_id,853);
1384 if p_tax_upload_flag <> 'TU' then --4878630
1385 hr_utility.set_location('p_tax_upload_flag <> TU..'|| l_new_payroll_id,854);
1386 open csr_get_assg(l_assignment_id,l_effective_date);
1387 fetch csr_get_assg into l_prim_payroll_id,l_asg_effective_start_date;
1388 close csr_get_assg;
1389
1390 if (g_old_payroll_id is not null) then -- if global var is set use global value
1391 l_prim_payroll_id := g_old_payroll_id;
1392 end if;
1393 unset_old_payroll_id;
1394
1395 open csr_freq(l_prim_payroll_id,l_effective_date);
1396 fetch csr_freq into l_old_flag;
1397 close csr_freq;
1398
1399 open csr_freq(l_new_payroll_id,l_effective_date);
1400 fetch csr_freq into l_new_flag;
1401 close csr_freq;
1402 end if; -- p_tax_upload_flag <> 'TU'
1403
1404 if ( l_new_flag <> l_old_flag or p_tax_upload_flag <> 'X' ) then -- --4878630
1405 /*Fetching global values */
1406 hr_utility.set_location('l_new_flag <> l_old_flag or p_tax_upload_flag <> X',855);
1407 if l_new_flag =1 and p_tax_upload_flag <> 'TU' then
1408 hr_utility.set_location('l_new_flag =1',856);
1409 open get_global_val('IE_MONTHLY_TAX_CREDIT',l_effective_date);
1410 fetch get_global_val into l_monthly_tax_credit;
1411 close get_global_val;
1412 open get_global_val('IE_MONTHLY_STANDARD_RATE_CUT_OFF',l_effective_date);
1413 fetch get_global_val into l_monthly_std_rate_cut_off;
1414 close get_global_val;
1415 /* For monthly payroll, weekly values must be null */
1416 l_weekly_tax_credit :=NULL;
1417 l_weekly_std_rate_cut_off :=NULL;
1418 elsif l_new_flag = 0 and p_tax_upload_flag <> 'TU' then
1419 hr_utility.set_location('l_new_flag =0',857);
1420 open get_global_val('IE_WEEKLY_TAX_CREDIT',l_effective_date);
1421 fetch get_global_val into l_weekly_tax_credit;
1422 close get_global_val;
1423 open get_global_val('IE_WEEKLY_STANDARD_RATE_CUT_OFF',l_effective_date);
1424 fetch get_global_val into l_weekly_std_rate_cut_off;
1425 close get_global_val;
1426 /* For weekly payroll, monthly values must be null */
1427 l_monthly_tax_credit :=NULL;
1428 l_monthly_std_rate_cut_off :=NULL;
1429 elsif p_tax_upload_flag = 'TU' then -- --4878630
1430 hr_utility.set_location('p_tax_upload_flag =TU',858);
1431 l_weekly_tax_credit := p_weekly_tax_credit;
1432 l_monthly_tax_credit := p_monthly_tax_credit;
1433 l_weekly_std_rate_cut_off := p_weekly_std_rate_cut_off;
1434 l_monthly_std_rate_cut_off := p_monthly_std_rate_cut_off;
1435 /* fetch values from interface table */
1436 end if;
1437
1438 open c_effective_paye ;
1439 fetch c_effective_paye into c_effective_paye_fetch;
1440 if c_effective_paye%found then
1441 hr_utility.set_location('if found',859);
1442 -- delete all future records ie diff paye_details_id
1443 open c_future_paye(c_effective_paye_fetch.paye_details_id);
1444 loop
1445 fetch c_future_paye into c_future_paye_fetch;
1446 EXIT when c_future_paye%NOTFOUND;
1447 hr_utility.set_location('if loop',860);
1448 pay_ie_paye_api.delete_ie_paye_details
1449 (p_validate => FALSE
1450 ,p_effective_date => c_future_paye_fetch.effective_start_date
1451 ,p_datetrack_delete_mode => 'ZAP'
1452 ,p_paye_details_id => c_future_paye_fetch.paye_details_id
1453 ,p_object_version_number => c_future_paye_fetch.object_version_number
1454 ,p_effective_start_date => l_start_date
1455 ,p_effective_end_date => l_end_date
1456 );
1457 end loop;
1458 close c_future_paye;
1459 -- FETCH OVN
1460 l_api_ovn := c_effective_paye_fetch.object_version_number;
1461 hr_utility.set_location('l_api_ovn..'||l_api_ovn,861);
1462 --if the start date is the effective date in the form then only mode possible should be CORRECTION
1463 if (c_effective_paye_fetch.tax_basis <> 'IE_CUMULATIVE' and c_effective_paye_fetch.tax_basis <> 'IE_EXEMPTION'
1464 and c_effective_paye_fetch.tax_basis <> 'IE_WEEK1_MONTH1' and c_effective_paye_fetch.tax_basis <> 'IE_EXEMPT_WEEK_MONTH'
1465 and p_tax_upload_flag <> 'TU' ) then
1466 hr_utility.set_location('Emergency ..',862);
1467 l_weekly_tax_credit:=NULL;
1468 l_weekly_std_rate_cut_off:=NULL;
1469 l_monthly_tax_credit:=NULL;
1470 l_monthly_std_rate_cut_off:=NULL;
1471 end if;
1472 --if there are no future changes to the paye record.
1473 if c_effective_paye_fetch.effective_end_date <> to_date('31-12-4712','DD-MM-YYYY') then
1474 --if there are future changes.Then just leave one till 4712 using mode future change
1475 hr_utility.set_location('date <> 31-12-4712',863);
1476 pay_ie_paye_api.delete_ie_paye_details
1477 (p_validate => FALSE
1478 ,p_effective_date => c_effective_paye_fetch.effective_start_date
1479 ,p_datetrack_delete_mode => 'FUTURE_CHANGE'
1480 ,p_paye_details_id => c_effective_paye_fetch.paye_details_id
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
1486 end if;
1487
1488 if c_effective_paye_fetch.effective_start_date = p_effective_date then
1489 hr_utility.set_location('c_effective_paye_fetch.effective_start_date = p_effective_date',864);
1490 --if P_DATETRACK_UPDATE_MODE = 'CORRECTION' then
1491 pay_ie_paye_api.update_ie_paye_details
1492 (p_validate => FALSE
1493 ,p_effective_date => p_effective_date
1494 ,p_datetrack_update_mode => 'CORRECTION'
1495 ,p_paye_details_id => c_effective_paye_fetch.paye_details_id
1496 ,p_info_source => decode_value_char(p_tax_upload_flag ='X',c_effective_paye_fetch.info_source,'IE_ELECTRONIC')
1497 ,p_tax_basis => decode_value_char(p_tax_upload_flag ='X',c_effective_paye_fetch.tax_basis,p_tax_basis) -- tax credit upload changes
1498 ,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
1499 ,p_tax_assess_basis => decode_value_char(p_tax_upload_flag ='X',c_effective_paye_fetch.tax_assess_basis,'IE_SEP_TREAT')
1500 ,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
1501 ,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
1502 ,p_weekly_tax_credit => l_weekly_tax_credit
1503 ,p_weekly_std_rate_cut_off => l_weekly_std_rate_cut_off
1504 ,p_monthly_tax_credit => l_monthly_tax_credit
1505 ,p_monthly_std_rate_cut_off => l_monthly_std_rate_cut_off
1506 ,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
1507 ,p_pay_to_date => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_pay_to_date) -- tax credit upload change
1508 ,p_disability_benefit => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,null) -- tax credit upload change
1509 ,p_lump_sum_payment => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,null) -- tax credit upload change
1510 ,p_object_version_number => l_api_ovn
1511 ,p_effective_start_date => l_start_date
1512 ,p_effective_end_date => l_end_date
1513 --13359423
1514 ,p_yrly_tax_cred => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_cred,p_yrly_tax_cred)
1515 ,p_yrly_tax_rate_1 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_rate_1,p_yrly_tax_rate_1)
1516 ,p_yrly_tax_rate_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_rate_2,p_yrly_tax_rate_2)
1517 ,p_mthly_tax_rate_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.mthly_tax_rate_2,p_mthly_tax_rate_2)
1518 ,p_wkly_tax_rate_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.wkly_tax_rate_2,p_wkly_tax_rate_2)
1519 ,p_tax_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.tax_rate_3,p_tax_rate_3)
1520 ,p_yrly_tax_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_rate_3,p_yrly_tax_rate_3)
1521 ,p_mthly_tax_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.mthly_tax_rate_3,p_mthly_tax_rate_3)
1522 ,p_wkly_tax_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.wkly_tax_rate_3,p_wkly_tax_rate_3)
1523 ,p_tax_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.tax_rate_4,p_tax_rate_4)
1524 ,p_yrly_tax_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_rate_4,p_yrly_tax_rate_4)
1525 ,p_mthly_tax_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.mthly_tax_rate_4,p_mthly_tax_rate_4)
1526 ,p_wkly_tax_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.wkly_tax_rate_4,p_wkly_tax_rate_4)
1527 ,p_tax_rate_5 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.tax_rate_5,p_tax_rate_5)
1528 ,p_in_exempt_usc => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.in_exempt_usc,p_in_exempt_usc)
1529 ,p_total_usc_pay_todate => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_total_usc_pay_todate)
1530 ,p_total_usc_tax_todate => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_total_usc_tax_todate)
1531 ,p_usc_rate_1 => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_1,p_usc_rate_1)
1532 ,p_usc_yrly_cutoff_1 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_yrly_cutoff_1,p_usc_yrly_cutoff_1)
1533 ,p_usc_mthly_cutoff_1 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_mthly_cutoff_1,p_usc_mthly_cutoff_1)
1534 ,p_usc_wkly_cutoff_1 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_wkly_cutoff_1,p_usc_wkly_cutoff_1)
1535 ,p_usc_rate_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_2,p_usc_rate_2)
1536 ,p_usc_yrly_cutoff_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_yrly_cutoff_2,p_usc_yrly_cutoff_2)
1537 ,p_usc_mthly_cutoff_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_mthly_cutoff_2,p_usc_mthly_cutoff_2)
1538 ,p_usc_wkly_cutoff_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_wkly_cutoff_2,p_usc_wkly_cutoff_2)
1539 ,p_usc_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_3,p_usc_rate_3)
1540 ,p_usc_yrly_cutoff_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_yrly_cutoff_3,p_usc_yrly_cutoff_3)
1541 ,p_usc_mthly_cutoff_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_mthly_cutoff_3,p_usc_mthly_cutoff_3)
1542 ,p_usc_wkly_cutoff_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_wkly_cutoff_3,p_usc_wkly_cutoff_3)
1543 ,p_usc_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_4,p_usc_rate_4)
1544 ,p_usc_yrly_cutoff_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_yrly_cutoff_4,p_usc_yrly_cutoff_4)
1545 ,p_usc_mthly_cutoff_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_mthly_cutoff_4,p_usc_mthly_cutoff_4)
1546 ,p_usc_wkly_cutoff_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_wkly_cutoff_4,p_usc_wkly_cutoff_4)
1547 ,p_usc_rate_5 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_5,p_usc_rate_5)
1548 ,p_usc_tax_basis => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.usc_tax_basis,p_usc_tax_basis)
1549 ,p_usc_info_source => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.usc_info_source,p_usc_info_source)
1550 --13359423
1551 );
1552 else --c_effective_paye_fetch.effective_start_date <> p_effective_date
1553 -- should always be update with new credits and cutoffs
1554 if P_DATETRACK_UPDATE_MODE = 'CORRECTION' then
1555 -- check with asg start date
1556 hr_utility.set_location('Correction and <> TU',865);
1557 if (l_asg_effective_start_date <= c_effective_paye_fetch.effective_start_date ) then
1558 pay_ie_paye_api.update_ie_paye_details
1559 (p_validate => FALSE
1560 ,p_effective_date => p_effective_date
1561 ,p_datetrack_update_mode => 'CORRECTION'
1562 ,p_paye_details_id => c_effective_paye_fetch.PAYE_DETAILs_ID
1563 ,p_info_source => c_effective_paye_fetch.info_source
1564 ,p_tax_basis => c_effective_paye_fetch.tax_basis
1565 ,p_certificate_start_date => c_effective_paye_fetch.certificate_start_date
1566 ,p_tax_assess_basis => c_effective_paye_fetch.tax_assess_basis
1567 ,p_certificate_issue_date => c_effective_paye_fetch.certificate_issue_date
1568 ,p_certificate_end_date => c_effective_paye_fetch.certificate_end_date
1569 ,p_weekly_tax_credit => l_weekly_tax_credit
1570 ,p_weekly_std_rate_cut_off => l_weekly_std_rate_cut_off
1571 ,p_monthly_tax_credit => l_monthly_tax_credit
1572 ,p_monthly_std_rate_cut_off => l_monthly_std_rate_cut_off
1573 ,p_tax_deducted_to_date => hr_api.g_number
1574 ,p_pay_to_date => hr_api.g_number
1575 ,p_disability_benefit => hr_api.g_number
1576 ,p_lump_sum_payment => hr_api.g_number
1577 ,p_object_version_number => l_api_ovn
1578 ,p_effective_start_date => l_start_date
1579 ,p_effective_end_date => l_end_date
1580 --13359423
1581 ,p_yrly_tax_cred => c_effective_paye_fetch.yrly_tax_cred
1582 ,p_yrly_tax_rate_1 => c_effective_paye_fetch.yrly_tax_rate_1
1583 ,p_yrly_tax_rate_2 => c_effective_paye_fetch.yrly_tax_rate_2
1584 ,p_mthly_tax_rate_2 => c_effective_paye_fetch.mthly_tax_rate_2
1585 ,p_wkly_tax_rate_2 => c_effective_paye_fetch.wkly_tax_rate_2
1586 ,p_tax_rate_3 => c_effective_paye_fetch.tax_rate_3
1587 ,p_yrly_tax_rate_3 => c_effective_paye_fetch.yrly_tax_rate_3
1588 ,p_mthly_tax_rate_3 => c_effective_paye_fetch.mthly_tax_rate_3
1589 ,p_wkly_tax_rate_3 => c_effective_paye_fetch.wkly_tax_rate_3
1590 ,p_tax_rate_4 => c_effective_paye_fetch.tax_rate_4
1591 ,p_yrly_tax_rate_4 => c_effective_paye_fetch.yrly_tax_rate_4
1592 ,p_mthly_tax_rate_4 => c_effective_paye_fetch.mthly_tax_rate_4
1593 ,p_wkly_tax_rate_4 => c_effective_paye_fetch.wkly_tax_rate_4
1594 ,p_tax_rate_5 => c_effective_paye_fetch.tax_rate_5
1595 ,p_in_exempt_usc => c_effective_paye_fetch.in_exempt_usc
1596 ,p_total_usc_pay_todate => hr_api.g_number
1597 ,p_total_usc_tax_todate => hr_api.g_number
1598 ,p_usc_rate_1 => c_effective_paye_fetch.usc_rate_1
1599 ,p_usc_yrly_cutoff_1 => c_effective_paye_fetch.usc_yrly_cutoff_1
1600 ,p_usc_mthly_cutoff_1 => c_effective_paye_fetch.usc_mthly_cutoff_1
1601 ,p_usc_wkly_cutoff_1 => c_effective_paye_fetch.usc_wkly_cutoff_1
1602 ,p_usc_rate_2 => c_effective_paye_fetch.usc_rate_2
1603 ,p_usc_yrly_cutoff_2 => c_effective_paye_fetch.usc_yrly_cutoff_2
1604 ,p_usc_mthly_cutoff_2 => c_effective_paye_fetch.usc_mthly_cutoff_2
1605 ,p_usc_wkly_cutoff_2 => c_effective_paye_fetch.usc_wkly_cutoff_2
1606 ,p_usc_rate_3 => c_effective_paye_fetch.usc_rate_3
1607 ,p_usc_yrly_cutoff_3 => c_effective_paye_fetch.usc_yrly_cutoff_3
1608 ,p_usc_mthly_cutoff_3 => c_effective_paye_fetch.usc_mthly_cutoff_3
1609 ,p_usc_wkly_cutoff_3 => c_effective_paye_fetch.usc_wkly_cutoff_3
1610 ,p_usc_rate_4 => c_effective_paye_fetch.usc_rate_4
1611 ,p_usc_yrly_cutoff_4 => c_effective_paye_fetch.usc_yrly_cutoff_4
1612 ,p_usc_mthly_cutoff_4 => c_effective_paye_fetch.usc_mthly_cutoff_4
1613 ,p_usc_wkly_cutoff_4 => c_effective_paye_fetch.usc_wkly_cutoff_4
1614 ,p_usc_rate_5 => c_effective_paye_fetch.usc_rate_5
1615 ,p_usc_tax_basis => c_effective_paye_fetch.usc_tax_basis
1616 ,p_usc_info_source => c_effective_paye_fetch.usc_info_source
1617 --13359423
1618 );
1619 else -- asg start date > paye start date then update using asg start date
1620 hr_utility.set_location('Correction and <> TU',865);
1621 pay_ie_paye_api.update_ie_paye_details
1622 (p_validate => FALSE
1623 ,p_effective_date => l_asg_effective_start_date
1624 ,p_datetrack_update_mode => 'UPDATE'
1625 ,p_paye_details_id => c_effective_paye_fetch.PAYE_DETAILs_ID
1626 ,p_info_source => c_effective_paye_fetch.info_source
1627 ,p_tax_basis => c_effective_paye_fetch.tax_basis
1628 ,p_certificate_start_date => c_effective_paye_fetch.certificate_start_date
1629 ,p_tax_assess_basis => c_effective_paye_fetch.tax_assess_basis
1630 ,p_certificate_issue_date => c_effective_paye_fetch.certificate_issue_date
1631 ,p_certificate_end_date => c_effective_paye_fetch.certificate_end_date
1632 ,p_weekly_tax_credit => l_weekly_tax_credit
1633 ,p_weekly_std_rate_cut_off => l_weekly_std_rate_cut_off
1634 ,p_monthly_tax_credit => l_monthly_tax_credit
1635 ,p_monthly_std_rate_cut_off => l_monthly_std_rate_cut_off
1636 ,p_tax_deducted_to_date => hr_api.g_number
1637 ,p_pay_to_date => hr_api.g_number
1638 ,p_disability_benefit => hr_api.g_number
1639 ,p_lump_sum_payment => hr_api.g_number
1640 ,p_object_version_number => l_api_ovn
1641 ,p_effective_start_date => l_start_date
1642 ,p_effective_end_date => l_end_date
1643 --13359423
1644 ,p_yrly_tax_cred => c_effective_paye_fetch.yrly_tax_cred
1645 ,p_yrly_tax_rate_1 => c_effective_paye_fetch.yrly_tax_rate_1
1646 ,p_yrly_tax_rate_2 => c_effective_paye_fetch.yrly_tax_rate_2
1647 ,p_mthly_tax_rate_2 => c_effective_paye_fetch.mthly_tax_rate_2
1648 ,p_wkly_tax_rate_2 => c_effective_paye_fetch.wkly_tax_rate_2
1649 ,p_tax_rate_3 => c_effective_paye_fetch.tax_rate_3
1650 ,p_yrly_tax_rate_3 => c_effective_paye_fetch.yrly_tax_rate_3
1651 ,p_mthly_tax_rate_3 => c_effective_paye_fetch.mthly_tax_rate_3
1652 ,p_wkly_tax_rate_3 => c_effective_paye_fetch.wkly_tax_rate_3
1653 ,p_tax_rate_4 => c_effective_paye_fetch.tax_rate_4
1654 ,p_yrly_tax_rate_4 => c_effective_paye_fetch.yrly_tax_rate_4
1655 ,p_mthly_tax_rate_4 => c_effective_paye_fetch.mthly_tax_rate_4
1656 ,p_wkly_tax_rate_4 => c_effective_paye_fetch.wkly_tax_rate_4
1657 ,p_tax_rate_5 => c_effective_paye_fetch.tax_rate_5
1658 ,p_in_exempt_usc => c_effective_paye_fetch.in_exempt_usc
1659 ,p_total_usc_pay_todate => hr_api.g_number
1660 ,p_total_usc_tax_todate => hr_api.g_number
1661 ,p_usc_rate_1 => c_effective_paye_fetch.usc_rate_1
1662 ,p_usc_yrly_cutoff_1 => c_effective_paye_fetch.usc_yrly_cutoff_1
1663 ,p_usc_mthly_cutoff_1 => c_effective_paye_fetch.usc_mthly_cutoff_1
1664 ,p_usc_wkly_cutoff_1 => c_effective_paye_fetch.usc_wkly_cutoff_1
1665 ,p_usc_rate_2 => c_effective_paye_fetch.usc_rate_2
1666 ,p_usc_yrly_cutoff_2 => c_effective_paye_fetch.usc_yrly_cutoff_2
1667 ,p_usc_mthly_cutoff_2 => c_effective_paye_fetch.usc_mthly_cutoff_2
1668 ,p_usc_wkly_cutoff_2 => c_effective_paye_fetch.usc_wkly_cutoff_2
1669 ,p_usc_rate_3 => c_effective_paye_fetch.usc_rate_3
1670 ,p_usc_yrly_cutoff_3 => c_effective_paye_fetch.usc_yrly_cutoff_3
1671 ,p_usc_mthly_cutoff_3 => c_effective_paye_fetch.usc_mthly_cutoff_3
1672 ,p_usc_wkly_cutoff_3 => c_effective_paye_fetch.usc_wkly_cutoff_3
1673 ,p_usc_rate_4 => c_effective_paye_fetch.usc_rate_4
1674 ,p_usc_yrly_cutoff_4 => c_effective_paye_fetch.usc_yrly_cutoff_4
1675 ,p_usc_mthly_cutoff_4 => c_effective_paye_fetch.usc_mthly_cutoff_4
1676 ,p_usc_wkly_cutoff_4 => c_effective_paye_fetch.usc_wkly_cutoff_4
1677 ,p_usc_rate_5 => c_effective_paye_fetch.usc_rate_5
1678 ,p_usc_tax_basis => c_effective_paye_fetch.usc_tax_basis
1679 ,p_usc_info_source => c_effective_paye_fetch.usc_info_source
1680 --13359423
1681 );
1682 end if; -- end of check with asg start date
1683 else -- P_DATETRACK_UPDATE_MODE <> 'CORRECTION'
1684 hr_utility.set_location('UPDATE and = TU',866);
1685 pay_ie_paye_api.update_ie_paye_details
1686 (p_validate => FALSE
1687 ,p_effective_date => p_effective_date
1688 ,p_datetrack_update_mode => 'UPDATE'
1689 ,p_paye_details_id => c_effective_paye_fetch.PAYE_DETAILs_ID
1690 ,p_info_source => decode_value_char(p_tax_upload_flag ='X',c_effective_paye_fetch.info_source,'IE_ELECTRONIC')
1691 ,p_tax_basis => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.tax_basis,p_tax_basis) -- tax credit upload changes
1692 ,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
1693 ,p_tax_assess_basis => decode_value_char(p_tax_upload_flag ='X',c_effective_paye_fetch.tax_assess_basis,'IE_SEP_TREAT')
1694 ,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
1695 ,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
1696 ,p_weekly_tax_credit => l_weekly_tax_credit
1697 ,p_weekly_std_rate_cut_off => l_weekly_std_rate_cut_off
1698 ,p_monthly_tax_credit => l_monthly_tax_credit
1699 ,p_monthly_std_rate_cut_off => l_monthly_std_rate_cut_off
1700 ,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
1701 ,p_pay_to_date => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_pay_to_date) -- tax credit upload change
1702 ,p_disability_benefit => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,null) -- tax credit upload change
1703 ,p_lump_sum_payment => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,null) -- tax credit upload change
1704 ,p_object_version_number => l_api_ovn
1705 ,p_effective_start_date => l_start_date
1706 ,p_effective_end_date => l_end_date
1707 --13359423
1708 ,p_yrly_tax_cred => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_cred,p_yrly_tax_cred)
1709 ,p_yrly_tax_rate_1 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_rate_1,p_yrly_tax_rate_1)
1710 ,p_yrly_tax_rate_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_rate_2,p_yrly_tax_rate_2)
1711 ,p_mthly_tax_rate_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.mthly_tax_rate_2,p_mthly_tax_rate_2)
1712 ,p_wkly_tax_rate_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.wkly_tax_rate_2,p_wkly_tax_rate_2)
1713 ,p_tax_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.tax_rate_3,p_tax_rate_3)
1714 ,p_yrly_tax_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_rate_3,p_yrly_tax_rate_3)
1715 ,p_mthly_tax_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.mthly_tax_rate_3,p_mthly_tax_rate_3)
1716 ,p_wkly_tax_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.wkly_tax_rate_3,p_wkly_tax_rate_3)
1717 ,p_tax_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.tax_rate_4,p_tax_rate_4)
1718 ,p_yrly_tax_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.yrly_tax_rate_4,p_yrly_tax_rate_4)
1719 ,p_mthly_tax_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.mthly_tax_rate_4,p_mthly_tax_rate_4)
1720 ,p_wkly_tax_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.wkly_tax_rate_4,p_wkly_tax_rate_4)
1721 ,p_tax_rate_5 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.tax_rate_5,p_tax_rate_5)
1722 ,p_in_exempt_usc => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.in_exempt_usc,p_in_exempt_usc)
1723 ,p_total_usc_pay_todate => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_total_usc_pay_todate)
1724 ,p_total_usc_tax_todate => decode_value_number(p_tax_upload_flag='X',hr_api.g_number,p_total_usc_tax_todate)
1725 ,p_usc_rate_1 => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_1,p_usc_rate_1)
1726 ,p_usc_yrly_cutoff_1 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_yrly_cutoff_1,p_usc_yrly_cutoff_1)
1727 ,p_usc_mthly_cutoff_1 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_mthly_cutoff_1,p_usc_mthly_cutoff_1)
1728 ,p_usc_wkly_cutoff_1 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_wkly_cutoff_1,p_usc_wkly_cutoff_1)
1729 ,p_usc_rate_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_2,p_usc_rate_2)
1730 ,p_usc_yrly_cutoff_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_yrly_cutoff_2,p_usc_yrly_cutoff_2)
1731 ,p_usc_mthly_cutoff_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_mthly_cutoff_2,p_usc_mthly_cutoff_2)
1732 ,p_usc_wkly_cutoff_2 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_wkly_cutoff_2,p_usc_wkly_cutoff_2)
1733 ,p_usc_rate_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_3,p_usc_rate_3)
1734 ,p_usc_yrly_cutoff_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_yrly_cutoff_3,p_usc_yrly_cutoff_3)
1735 ,p_usc_mthly_cutoff_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_mthly_cutoff_3,p_usc_mthly_cutoff_3)
1736 ,p_usc_wkly_cutoff_3 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_wkly_cutoff_3,p_usc_wkly_cutoff_3)
1737 ,p_usc_rate_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_4,p_usc_rate_4)
1738 ,p_usc_yrly_cutoff_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_yrly_cutoff_4,p_usc_yrly_cutoff_4)
1739 ,p_usc_mthly_cutoff_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_mthly_cutoff_4,p_usc_mthly_cutoff_4)
1740 ,p_usc_wkly_cutoff_4 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_wkly_cutoff_4,p_usc_wkly_cutoff_4)
1741 ,p_usc_rate_5 => decode_value_number(p_tax_upload_flag='X',c_effective_paye_fetch.usc_rate_5,p_usc_rate_5)
1742 ,p_usc_tax_basis => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.usc_tax_basis,p_usc_tax_basis)
1743 ,p_usc_info_source => decode_value_char(p_tax_upload_flag='X',c_effective_paye_fetch.usc_info_source,p_usc_info_source)
1744 --13359423
1745 );
1746 end if; --P_DATETRACK_UPDATE_MODE = 'CORRECTION'
1747 hr_utility.set_location('After datetrack check',867);
1748 end if; -- c_effective_paye_fetch.effective_start_date = p_effective_date
1749 else -- not found
1750 --get the first record as of effective date.This is becasue there mare reocrds only afte the effective date.Nothing as of
1751 --the effective date.this record should be extended upto 4712
1752 hr_utility.set_location('Effective date does not lie between paye start and end date',868);
1753 if p_tax_upload_flag <> 'TU' then --4878630
1754 hr_utility.set_location('<> TU',869);
1755 open c_future_paye(null);
1756 fetch c_future_paye into c_future_paye_fetch ;
1757 l_min_paye_id := c_future_paye_fetch.paye_details_id ;
1758 l_min_effective_date := c_future_paye_fetch.effective_start_date;
1759 l_api_ovn := c_future_paye_fetch.object_version_number;
1760 l_tax_basis := c_future_paye_fetch.tax_basis;
1761 l_info_source := c_future_paye_fetch.info_source;
1762 l_certificate_start_date := c_future_paye_fetch.certificate_start_date;
1763 l_certificate_end_date := c_future_paye_fetch.certificate_end_date;
1764 l_certificate_issue_date := c_future_paye_fetch.certificate_issue_date;
1765 l_tax_assess_basis := c_future_paye_fetch.tax_assess_basis;
1766 l_futrec_effective_end_date := c_future_paye_fetch.effective_end_date;
1767 --13359423
1768 l_yrly_tax_cred := c_future_paye_fetch.yrly_tax_cred;
1769 l_yrly_tax_rate_1 := c_future_paye_fetch.yrly_tax_rate_1;
1770 l_yrly_tax_rate_2 := c_future_paye_fetch.yrly_tax_rate_2;
1771 l_mthly_tax_rate_2 := c_future_paye_fetch.mthly_tax_rate_2;
1772 l_wkly_tax_rate_2 := c_future_paye_fetch.wkly_tax_rate_2;
1773 l_tax_rate_3 := c_future_paye_fetch.tax_rate_3;
1774 l_yrly_tax_rate_3 := c_future_paye_fetch.yrly_tax_rate_3;
1775 l_mthly_tax_rate_3 := c_future_paye_fetch.mthly_tax_rate_3;
1776 l_wkly_tax_rate_3 := c_future_paye_fetch.wkly_tax_rate_3;
1777 l_tax_rate_4 := c_future_paye_fetch.tax_rate_4;
1778 l_yrly_tax_rate_4 := c_future_paye_fetch.yrly_tax_rate_4;
1779 l_mthly_tax_rate_4 := c_future_paye_fetch.mthly_tax_rate_4;
1780 l_wkly_tax_rate_4 := c_future_paye_fetch.wkly_tax_rate_4;
1781 l_tax_rate_5 := c_future_paye_fetch.tax_rate_5;
1782 l_in_exempt_usc := c_future_paye_fetch.in_exempt_usc;
1783 l_total_usc_pay_todate := c_future_paye_fetch.total_usc_pay_todate;
1784 l_total_usc_tax_todate := c_future_paye_fetch.total_usc_tax_todate;
1785 l_usc_rate_1 := c_future_paye_fetch.usc_rate_1;
1786 l_usc_yrly_cutoff_1 := c_future_paye_fetch.usc_yrly_cutoff_1;
1787 l_usc_mthly_cutoff_1 := c_future_paye_fetch.usc_mthly_cutoff_1;
1788 l_usc_wkly_cutoff_1 := c_future_paye_fetch.usc_wkly_cutoff_1;
1789 l_usc_rate_2 := c_future_paye_fetch.usc_rate_2;
1790 l_usc_yrly_cutoff_2 := c_future_paye_fetch.usc_yrly_cutoff_2;
1791 l_usc_mthly_cutoff_2 := c_future_paye_fetch.usc_mthly_cutoff_2;
1792 l_usc_wkly_cutoff_2 := c_future_paye_fetch.usc_wkly_cutoff_2;
1793 l_usc_rate_3 := c_future_paye_fetch.usc_rate_3;
1794 l_usc_yrly_cutoff_3 := c_future_paye_fetch.usc_yrly_cutoff_3;
1795 l_usc_mthly_cutoff_3 := c_future_paye_fetch.usc_mthly_cutoff_3;
1796 l_usc_wkly_cutoff_3 := c_future_paye_fetch.usc_wkly_cutoff_3;
1797 l_usc_rate_4 := c_future_paye_fetch.usc_rate_4;
1798 l_usc_yrly_cutoff_4 := c_future_paye_fetch.usc_yrly_cutoff_4;
1799 l_usc_mthly_cutoff_4 := c_future_paye_fetch.usc_mthly_cutoff_4;
1800 l_usc_wkly_cutoff_4 := c_future_paye_fetch.usc_wkly_cutoff_4;
1801 l_usc_rate_5 := c_future_paye_fetch.usc_rate_5;
1802 l_usc_tax_basis := c_future_paye_fetch.usc_tax_basis;
1803 l_usc_info_source := c_future_paye_fetch.usc_info_source;
1804 --13359423
1805 close c_future_paye;
1806 else --4878630
1807 hr_utility.set_location('= TU',870);
1808 open c_tax_effective_paye(null,p_effective_date);
1809 fetch c_tax_effective_paye into c_tax_upload_paye;
1810 l_max_paye_id := c_tax_upload_paye.paye_details_id;
1811 l_max_effective_start_date := c_tax_upload_paye.effective_start_date;
1812 l_futrec_effective_end_date := c_tax_upload_paye.effective_end_date;
1813 l_api_ovn := c_tax_upload_paye.object_version_number;
1814 l_info_source := c_tax_upload_paye.info_source;
1815 --l_tax_assess_basis := c_tax_upload_paye.tax_assess_basis;
1816 CLOSE c_tax_effective_paye;
1817 hr_utility.set_location('l_max_paye_id..'|| l_max_paye_id,871);
1818 hr_utility.set_location('l_max_effective_start_date.'|| l_max_effective_start_date,872);
1819 hr_utility.set_location('l_futrec_effective_end_date '|| l_futrec_effective_end_date,873);
1820 hr_utility.set_location('l_api_ovn '|| l_api_ovn,874);
1821 hr_utility.set_location('l_info_source.'|| l_info_source,875);
1822
1823 end if;
1824 IF p_tax_upload_flag <> 'TU' then
1825 --delete any other future records ie different paye_details_id
1826 open c_future_paye(l_min_paye_id);
1827 loop
1828 fetch c_future_paye into c_future_paye_fetch;
1829 EXIT when c_future_paye%NOTFOUND;
1830 pay_ie_paye_api.delete_ie_paye_details
1831 (p_validate => FALSE
1832 ,p_effective_date => c_future_paye_fetch.effective_start_date
1833 ,p_datetrack_delete_mode => 'ZAP'
1834 ,p_paye_details_id => c_future_paye_fetch.paye_details_id
1835 ,p_object_version_number => c_future_paye_fetch.object_version_number
1836 ,p_effective_start_date => l_start_date
1837 ,p_effective_end_date => l_end_date
1838 );
1839 end loop;
1840 close c_future_paye;
1841
1842
1843 if l_futrec_effective_end_date <> to_date('31-12-4712','DD-MM-YYYY')
1844 AND (l_futrec_effective_end_date IS NOT NULL)
1845 then
1846 --extend the first record after the effective date till 4712
1847 pay_ie_paye_api.delete_ie_paye_details
1848 (p_validate => FALSE
1849 ,p_effective_date => l_min_effective_date
1850 ,p_datetrack_delete_mode => 'FUTURE_CHANGE'
1851 ,p_paye_details_id => l_min_paye_id
1852 ,p_object_version_number => l_api_ovn
1853 ,p_effective_start_date => l_start_date
1854 ,p_effective_end_date => l_end_date
1855 );
1856 end if;
1857 if (l_tax_basis <> 'IE_CUMULATIVE' and l_tax_basis <> 'IE_EXEMPTION'
1858 and l_tax_basis <> 'IE_WEEK1_MONTH1' and l_tax_basis <> 'IE_EXEMPT_WEEK_MONTH') then
1859 l_weekly_tax_credit:=NULL;
1860 l_weekly_std_rate_cut_off:=NULL;
1861 l_monthly_tax_credit:=NULL;
1862 l_monthly_std_rate_cut_off:=NULL;
1863 end if;
1864 --only mode possible should be correction, using the new credits and cutoffs
1865 IF (l_futrec_effective_end_date IS NOT NULL) THEN
1866 pay_ie_paye_api.update_ie_paye_details
1867 (p_validate => FALSE
1868 ,p_effective_date => l_min_effective_date
1869 ,p_datetrack_update_mode => 'CORRECTION'
1870 ,p_paye_details_id => l_min_paye_id
1871 ,p_info_source => l_info_source
1872 ,p_tax_basis => l_tax_basis
1873 ,p_certificate_start_date => l_certificate_start_date
1874 ,p_tax_assess_basis => l_tax_assess_basis
1875 ,p_certificate_issue_date => l_certificate_issue_date
1876 ,p_certificate_end_date => l_certificate_end_date
1877 ,p_weekly_tax_credit => l_weekly_tax_credit
1878 ,p_weekly_std_rate_cut_off => l_weekly_std_rate_cut_off
1879 ,p_monthly_tax_credit => l_monthly_tax_credit
1880 ,p_monthly_std_rate_cut_off => l_monthly_std_rate_cut_off
1881 ,p_tax_deducted_to_date => hr_api.g_number
1882 ,p_pay_to_date => hr_api.g_number
1883 ,p_disability_benefit => hr_api.g_number
1884 ,p_lump_sum_payment => hr_api.g_number
1885 ,p_object_version_number => l_api_ovn
1886 ,p_effective_start_date => l_start_date
1887 ,p_effective_end_date => l_end_date
1888 --13359423
1889 ,p_yrly_tax_cred => l_yrly_tax_cred
1890 ,p_yrly_tax_rate_1 => l_yrly_tax_rate_1
1891 ,p_yrly_tax_rate_2 => l_yrly_tax_rate_2
1892 ,p_mthly_tax_rate_2 => l_mthly_tax_rate_2
1893 ,p_wkly_tax_rate_2 => l_wkly_tax_rate_2
1894 ,p_tax_rate_3 => l_tax_rate_3
1895 ,p_yrly_tax_rate_3 => l_yrly_tax_rate_3
1896 ,p_mthly_tax_rate_3 => l_mthly_tax_rate_3
1897 ,p_wkly_tax_rate_3 => l_wkly_tax_rate_3
1898 ,p_tax_rate_4 => l_tax_rate_4
1899 ,p_yrly_tax_rate_4 => l_yrly_tax_rate_4
1900 ,p_mthly_tax_rate_4 => l_mthly_tax_rate_4
1901 ,p_wkly_tax_rate_4 => l_wkly_tax_rate_4
1902 ,p_tax_rate_5 => l_tax_rate_5
1903 ,p_in_exempt_usc => l_in_exempt_usc
1904 ,p_total_usc_pay_todate => hr_api.g_number
1905 ,p_total_usc_tax_todate => hr_api.g_number
1906 ,p_usc_rate_1 => l_usc_rate_1
1907 ,p_usc_yrly_cutoff_1 => l_usc_yrly_cutoff_1
1908 ,p_usc_mthly_cutoff_1 => l_usc_mthly_cutoff_1
1909 ,p_usc_wkly_cutoff_1 => l_usc_wkly_cutoff_1
1910 ,p_usc_rate_2 => l_usc_rate_2
1911 ,p_usc_yrly_cutoff_2 => l_usc_yrly_cutoff_2
1912 ,p_usc_mthly_cutoff_2 => l_usc_mthly_cutoff_2
1913 ,p_usc_wkly_cutoff_2 => l_usc_wkly_cutoff_2
1914 ,p_usc_rate_3 => l_usc_rate_3
1915 ,p_usc_yrly_cutoff_3 => l_usc_yrly_cutoff_3
1916 ,p_usc_mthly_cutoff_3 => l_usc_mthly_cutoff_3
1917 ,p_usc_wkly_cutoff_3 => l_usc_wkly_cutoff_3
1918 ,p_usc_rate_4 => l_usc_rate_4
1919 ,p_usc_yrly_cutoff_4 => l_usc_yrly_cutoff_4
1920 ,p_usc_mthly_cutoff_4 => l_usc_mthly_cutoff_4
1921 ,p_usc_wkly_cutoff_4 => l_usc_wkly_cutoff_4
1922 ,p_usc_rate_5 => l_usc_rate_5
1923 ,p_usc_tax_basis => l_usc_tax_basis
1924 ,p_usc_info_source => l_usc_info_source
1925 --13359423
1926 );
1927 END IF;-- futrec is null
1928 ELSE -- p_tax_upload_flag = 'TU'
1929 --delete any other future records ie different paye_details_id
1930 hr_utility.set_location('else of future paye.'|| l_info_source,876);
1931 open c_tax_effective_paye(l_max_paye_id,l_max_effective_start_date);
1932 loop
1933 fetch c_tax_effective_paye into c_tax_upload_paye;
1934 EXIT when c_tax_effective_paye%NOTFOUND;
1935 hr_utility.set_location('In loop',878);
1936 hr_utility.set_location('c_tax_upload_paye.effective_start_date '|| c_tax_upload_paye.effective_start_date ,879);
1937 hr_utility.set_location('c_tax_upload_paye.paye_details_id.'||c_tax_upload_paye.paye_details_id,880);
1938
1939 pay_ie_paye_api.delete_ie_paye_details
1940 (p_validate => FALSE
1941 ,p_effective_date => c_tax_upload_paye.effective_start_date
1942 ,p_datetrack_delete_mode => 'ZAP'
1943 ,p_paye_details_id => c_tax_upload_paye.paye_details_id
1944 ,p_object_version_number => c_tax_upload_paye.object_version_number
1945 ,p_effective_start_date => l_start_date
1946 ,p_effective_end_date => l_end_date
1947 );
1948 end loop;
1949 hr_utility.set_location('else of future paye After ZAping',881);
1950 close c_tax_effective_paye;
1951
1952 hr_utility.set_location('l_futrec_effective_end_date..'|| l_futrec_effective_end_date,879);
1953 if l_futrec_effective_end_date <> to_date('31-12-4712','DD-MM-YYYY')
1954 AND (l_futrec_effective_end_date IS NOT NULL)
1955 then
1956 --extend the first record after the effective date till 4712
1957 pay_ie_paye_api.delete_ie_paye_details
1958 (p_validate => FALSE
1959 ,p_effective_date => l_max_effective_start_date
1960 ,p_datetrack_delete_mode => 'FUTURE_CHANGE'
1961 ,p_paye_details_id => l_max_paye_id
1962 ,p_object_version_number => l_api_ovn
1963 ,p_effective_start_date => l_start_date
1964 ,p_effective_end_date => l_end_date
1965 );
1966 end if;
1967
1968 --only mode possible should be correction, using the new credits and cutoffs
1969 IF (l_futrec_effective_end_date IS NOT NULL) THEN
1970 hr_utility.set_location('The last Mode',880);
1971 pay_ie_paye_api.update_ie_paye_details
1972 (p_validate => FALSE
1973 ,p_effective_date => l_max_effective_start_date
1974 ,p_datetrack_update_mode => 'CORRECTION'
1975 ,p_paye_details_id => l_max_paye_id
1976 ,p_info_source => 'IE_ELECTRONIC'
1977 ,p_tax_basis => p_tax_basis
1978 ,p_certificate_start_date => p_cert_start_date
1979 ,p_tax_assess_basis => 'IE_SEP_TREAT'
1980 ,p_certificate_issue_date => p_effective_date
1981 ,p_certificate_end_date => p_cert_end_date
1982 ,p_weekly_tax_credit => p_weekly_tax_credit
1983 ,p_weekly_std_rate_cut_off => p_weekly_std_rate_cut_off
1984 ,p_monthly_tax_credit => p_monthly_tax_credit
1985 ,p_monthly_std_rate_cut_off => p_monthly_std_rate_cut_off
1986 ,p_tax_deducted_to_date => p_tax_deducted_to_date
1987 ,p_pay_to_date => p_pay_to_date
1988 ,p_disability_benefit => null
1989 ,p_lump_sum_payment => null
1990 ,p_object_version_number => l_api_ovn
1991 ,p_effective_start_date => l_start_date
1992 ,p_effective_end_date => l_end_date
1993 --13359423
1994 ,p_yrly_tax_cred => p_yrly_tax_cred
1995 ,p_yrly_tax_rate_1 => p_yrly_tax_rate_1
1996 ,p_yrly_tax_rate_2 => p_yrly_tax_rate_2
1997 ,p_mthly_tax_rate_2 => p_mthly_tax_rate_2
1998 ,p_wkly_tax_rate_2 => p_wkly_tax_rate_2
1999 ,p_tax_rate_3 => p_tax_rate_3
2000 ,p_yrly_tax_rate_3 => p_yrly_tax_rate_3
2001 ,p_mthly_tax_rate_3 => p_mthly_tax_rate_3
2002 ,p_wkly_tax_rate_3 => p_wkly_tax_rate_3
2003 ,p_tax_rate_4 => p_tax_rate_4
2004 ,p_yrly_tax_rate_4 => p_yrly_tax_rate_4
2005 ,p_mthly_tax_rate_4 => p_mthly_tax_rate_4
2006 ,p_wkly_tax_rate_4 => p_wkly_tax_rate_4
2007 ,p_tax_rate_5 => p_tax_rate_5
2008 ,p_in_exempt_usc => p_in_exempt_usc
2009 ,p_total_usc_pay_todate => p_total_usc_pay_todate
2010 ,p_total_usc_tax_todate => p_total_usc_tax_todate
2011 ,p_usc_rate_1 => p_usc_rate_1
2012 ,p_usc_yrly_cutoff_1 => p_usc_yrly_cutoff_1
2013 ,p_usc_mthly_cutoff_1 => p_usc_mthly_cutoff_1
2014 ,p_usc_wkly_cutoff_1 => p_usc_wkly_cutoff_1
2015 ,p_usc_rate_2 => p_usc_rate_2
2016 ,p_usc_yrly_cutoff_2 => p_usc_yrly_cutoff_2
2017 ,p_usc_mthly_cutoff_2 => p_usc_mthly_cutoff_2
2018 ,p_usc_wkly_cutoff_2 => p_usc_wkly_cutoff_2
2019 ,p_usc_rate_3 => p_usc_rate_3
2020 ,p_usc_yrly_cutoff_3 => p_usc_yrly_cutoff_3
2021 ,p_usc_mthly_cutoff_3 => p_usc_mthly_cutoff_3
2022 ,p_usc_wkly_cutoff_3 => p_usc_wkly_cutoff_3
2023 ,p_usc_rate_4 => p_usc_rate_4
2024 ,p_usc_yrly_cutoff_4 => p_usc_yrly_cutoff_4
2025 ,p_usc_mthly_cutoff_4 => p_usc_mthly_cutoff_4
2026 ,p_usc_wkly_cutoff_4 => p_usc_wkly_cutoff_4
2027 ,p_usc_rate_5 => p_usc_rate_5
2028 ,p_usc_tax_basis => p_usc_tax_basis
2029 ,p_usc_info_source => p_usc_info_source
2030 --13359423
2031 );
2032 hr_utility.set_location('After The last Mode',881);
2033 END IF;-- futrec is null
2034
2035 END If; -- p_tax_upload_flag <> 'TU'
2036 end if; --if c_effective_paye%found
2037 close c_effective_paye;
2038 end if; --(l_new_flag <> l_old_flag)
2039 end if; -- l_new_payroll_id is not null
2040 END update_paye_change_freq;
2041
2042 Procedure set_old_payroll_id(
2043 p_old_payroll_id number
2044 )
2045 IS
2046 BEGIN
2047 g_old_payroll_id:=p_old_payroll_id;
2048 END set_old_payroll_id;
2049
2050 Procedure unset_old_payroll_id
2051 IS
2052 BEGIN
2053 g_old_payroll_id:=null;
2054 END unset_old_payroll_id;
2055
2056 Function get_old_payroll_id return number is
2057 begin
2058 return g_old_payroll_id;
2059 end get_old_payroll_id;
2060 /*End of Bug 4080773*/
2061
2062 FUNCTION get_age_payroll_period(p_assignment_id IN NUMBER
2063 ,p_payroll_id IN NUMBER
2064 ,p_date_earned IN DATE) RETURN NUMBER IS
2065 --
2066 -- Local variables
2067 --
2068 l_proc VARCHAR2(120) := g_package || 'get_age_payroll_period';
2069 l_period_start_date DATE;
2070 l_period_end_date DATE;
2071 l_dob DATE;
2072 l_age_last_day_month NUMBER;
2073 l_last_day_of_year DATE;
2074 --
2075 v_last_name varchar2(100);
2076 v_asg_number varchar2(50);
2077
2078 --
2079 -- Cursor get_period_dates
2080 --
2081 CURSOR get_period_dates IS
2082 SELECT ptp.start_date start_date
2083 ,ptp.end_date end_date
2084 FROM per_time_periods ptp
2085 WHERE ptp.payroll_id=p_payroll_id
2086 AND p_date_earned BETWEEN ptp.start_date AND ptp.end_date;
2087 --
2088 -- Cursor get_db
2089 --
2090 CURSOR get_dob IS
2091 SELECT date_of_birth,per.last_name,paf.assignment_number
2092 FROM per_all_people_f per
2093 ,per_all_assignments_f paf
2094 WHERE per.person_id = paf.person_id
2095 AND paf.assignment_id = p_assignment_id
2096 AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date
2097 AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date;
2098 --
2099 BEGIN
2100 --
2101 -- hr_utility.set_location('Entering:'|| l_proc, 5);
2102
2103 --
2104 /*OPEN get_period_dates;
2105 FETCH get_period_dates INTO l_period_start_date,l_period_end_date;
2106 CLOSE get_period_dates;*/
2107 --
2108 l_last_day_of_year := to_date( '31/12/' || to_char(p_date_earned, 'YYYY'), 'DD/MM/YYYY');
2109 --
2110 --
2111 OPEN get_dob;
2112 FETCH get_dob INTO l_dob,v_last_name,v_asg_number;
2113 CLOSE get_dob;
2114
2115 hr_utility.set_location('- Name = '|| v_last_name, 5);
2116 hr_utility.set_location('- Asg No = '|| v_asg_number, 5);
2117
2118 RETURN(TRUNC(MONTHS_BETWEEN(l_last_day_of_year,l_dob)/12));
2119
2120 /*l_age_last_day_month := TRUNC(MONTHS_BETWEEN(last_day(p_date_earned),l_dob)/12);
2121
2122 IF l_dob >= l_period_start_date AND l_dob <= l_period_end_date THEN
2123 RETURN(TRUNC(MONTHS_BETWEEN(l_period_end_date,l_dob)/12));
2124 ELSE
2125 RETURN(TRUNC(MONTHS_BETWEEN(p_date_earned,l_dob)/12));
2126 END IF;*/
2127 END get_age_payroll_period;
2128 --
2129
2130 FUNCTION get_age_paid_year(p_assignment_id number,
2131 p_payroll_action_id number) RETURN NUMBER IS
2132 --
2133 -- Local variables
2134 --
2135 l_proc VARCHAR2(120) := g_package || 'get_age_payroll_period';
2136 l_period_start_date DATE;
2137 l_period_end_date DATE;
2138 l_dob DATE;
2139 l_age_last_day_month NUMBER;
2140 l_last_day_of_year DATE;
2141 --
2142 v_last_name varchar2(100);
2143 v_asg_number varchar2(50);
2144 l_date_paid date;
2145 --
2146 -- Cursor get_period_dates
2147 --
2148 CURSOR get_period_dates IS
2149 SELECT effective_date
2150 FROM pay_payroll_actions
2151 WHERE payroll_action_id = p_payroll_action_id;
2152 --
2153 -- Cursor get_db
2154 --
2155 CURSOR get_dob IS
2156 SELECT date_of_birth,per.last_name,paf.assignment_number
2157 FROM per_all_people_f per
2158 ,per_all_assignments_f paf
2159 WHERE per.person_id = paf.person_id
2160 AND paf.assignment_id = p_assignment_id
2161 AND l_date_paid BETWEEN per.effective_start_date AND per.effective_end_date
2162 AND l_date_paid BETWEEN paf.effective_start_date AND paf.effective_end_date;
2163 --
2164 BEGIN
2165 --
2166 -- hr_utility.set_location('Entering:'|| l_proc, 5);
2167
2168 --
2169 OPEN get_period_dates;
2170 FETCH get_period_dates INTO l_date_paid;
2171 CLOSE get_period_dates;
2172 --
2173 l_last_day_of_year := to_date( '31/12/' || to_char(l_date_paid, 'YYYY'), 'DD/MM/YYYY');
2174 --
2175 --
2176 OPEN get_dob;
2177 FETCH get_dob INTO l_dob,v_last_name,v_asg_number;
2178 CLOSE get_dob;
2179
2180 hr_utility.set_location('- Name = '|| v_last_name, 5);
2181 hr_utility.set_location('- Asg No = '|| v_asg_number, 5);
2182
2183 RETURN(TRUNC(MONTHS_BETWEEN(l_last_day_of_year,l_dob)/12));
2184
2185 /*l_age_last_day_month := TRUNC(MONTHS_BETWEEN(last_day(p_date_earned),l_dob)/12);
2186
2187 IF l_dob >= l_period_start_date AND l_dob <= l_period_end_date THEN
2188 RETURN(TRUNC(MONTHS_BETWEEN(l_period_end_date,l_dob)/12));
2189 ELSE
2190 RETURN(TRUNC(MONTHS_BETWEEN(p_date_earned,l_dob)/12));
2191 END IF;*/
2192 END get_age_paid_year;
2193 --
2194 FUNCTION get_periods_between(p_payroll_id number,
2195 p_start_date date,
2196 p_end_date date) RETURN NUMBER IS
2197
2198 l_num_periods NUMBER := 0;
2199
2200 CURSOR csr_get_periods_between IS
2201 SELECT COUNT (*)
2202 FROM per_time_periods
2203 WHERE payroll_id = p_payroll_id
2204 AND regular_payment_date BETWEEN p_start_date AND p_end_date;
2205
2206 BEGIN
2207
2208 OPEN csr_get_periods_between;
2209 FETCH csr_get_periods_between INTO l_num_periods;
2210 CLOSE csr_get_periods_between;
2211
2212 RETURN NVL(l_num_periods, 0);
2213
2214 END;
2215
2216 /* 10078301 fix */
2217 Function get_ie_employer_info(p_tax_unit_id in number
2218 , p_tax_dist_ref out nocopy varchar2
2219 , p_employer_paye_ref out nocopy varchar2
2220 , p_employer_trading_name out nocopy varchar2
2221 , p_emp_tax_ref_contact out nocopy varchar2
2222 , p_health_levy_refund out nocopy varchar2
2223 ) return number is
2224
2225 CURSOR csr_get_employer_info(p_tax_unit_id NUMBER) is
2226 SELECT org_information1, org_information2, org_information3, org_information4, org_information5
2227 FROM hr_organization_information
2228 WHERE organization_id = p_tax_unit_id
2229 and org_information_context = 'IE_EMPLOYER_INFO';
2230
2231 BEGIN
2232
2233 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_tax_unit_id ' || p_tax_unit_id); --12779712
2234 OPEN csr_get_employer_info(p_tax_unit_id);
2235 FETCH csr_get_employer_info INTO p_tax_dist_ref, p_employer_paye_ref, p_employer_trading_name, p_emp_tax_ref_contact, p_health_levy_refund;
2236 CLOSE csr_get_employer_info;
2237 RETURN 1;
2238 END get_ie_employer_info;
2239
2240 /* 10254081 fix */
2241 FUNCTION get_last_period( p_payroll_id in number
2242 ,p_payroll_action_id in number
2243 ,p_period_type in varchar2
2244 ,p_last_period_num out nocopy number
2245 ) return number is
2246
2247 cursor c_last_pay_period is select max(ptp.period_num)
2248 from per_time_periods ptp,
2249 pay_all_payrolls pap,
2250 pay_payroll_actions ppa
2251 where pap.payroll_id = ptp.payroll_id
2252 and pap.payroll_id=p_payroll_id
2253 and ppa.payroll_id=pap.payroll_id
2254 and ppa.payroll_action_id=p_payroll_action_id
2255 and ptp.PERIOD_NAME like '%'||to_char(ppa.date_earned, 'YYYY')||'%';
2256
2257 l_proc varchar2(72) := g_package||'get_last_period';
2258
2259 BEGIN
2260
2261 hr_utility.set_location('Entering:'||l_proc, 35);
2262
2263 open c_last_pay_period;
2264 fetch c_last_pay_period into p_last_period_num;
2265 close c_last_pay_period;
2266
2267 if p_period_type = 'Bi-Week' then
2268 p_last_period_num := p_last_period_num * 2;
2269 end if;
2270
2271 hr_utility.set_location('Leaving:'||l_proc, 50);
2272 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_last_period_num ' || p_last_period_num);
2273 RETURN 1;
2274
2275 exception when others then
2276 RETURN 0;
2277
2278 END get_last_period;
2279
2280 --
2281 end pay_ie_paye_pkg;