1 PACKAGE BODY pay_sg_deductions AS
2 /* $Header: pysgdedn.pkb 120.10.12010000.3 2008/08/06 08:21:51 ubhat ship $
3 **
4 ** Copyright (c) 2002 Oracle Corporation
5 ** All Rights Reserved
6 **
7 ** Procedures and functions used in SG deduction formula
8 **
9 ** Change List
10 ** ===========
11 **
12 ** Date Author Reference Bug Number Description
13 ** =========== ======== ========= ========== =====================
14 ** 26 Jun 2000 makelly 115.0 Initial
15 ** 6 Apr 2002 Ragovind 115.4 Added Sg_get_Prorator function
16 ** 10 Apr 2002 Ragovind 115.6 Modified the sg_get_prorator function.
17 ** 10 apr 2002 Ragovind 115.7 Added comments to sg_get_prorator about functionality of CPF Proration calculation.
18 ** 28 Jun 2002 SRussell 115.9 Added CPF Retropay functions.
19 ** 26 Jul 2002 SRussell 115.10 Changed exception clause and Table order.
20 ** 06 Aug 2002 Kaverma 115.11 2494173 modified sg_get_prorator function(Bug No - 2494173).
21 ** 07 Aug 2002 Kaverma 115.12 Modified fwl_amount function
22 ** 02 Sep 2002 Ragoivnd 115.13 Added Function get_prev_year_ord_ytd
23 ** 19 Sep 2002 Ragovind 115.14 Modified the cursor c_tax_unit_id
24 ** 04 Oct 2002 vgsriniv 115.15 Modified fwl_amount function
25 ** 17 Oct 2002 apunekar 115.16 Modified cursor c_get_dates to add distinct clause
26 ** 17 Oct 2002 apunekar 115.17 Added comments
27 ** 06 Nov 2002 Ragovind 115.18 Modified the cursor c_get_per_start_end_dates for CPF Calculation
28 ** 11 Dec 2002 Apunekar 115.19 Added nocopy to out or in out parameters
29 ** 20 Dec 2002 Ragovind 115.20 2475324 Added CPF Report Coding.
30 ** 29 Jan 2002 JLin 115.21 2772106 Modified cursor c_get_per_start_end_dates
31 ** 11 Feb 2003 Ragovind 115.22 2796093 Modified the CPF Report code for Correct CPF for Terminated Employees.
32 ** 19 Mar 2003 Ragovind 115.23 2858065 Corrected CPF prorator factor for Termination and Rehire employee in the same month
33 ** 27 Mar 2003 Ragovind 115.24 2873083 Corrected CPF prorator factor for Term/Rehire in same month and run Quickpay for the
34 ** Terminated Employee Assignment.
35 ** 02 Jan 2004 Nanuradh 115.25 3331018 Removed the cursor c_get_prev_ord_ytd, instead used
36 ** pay_balance.get_value to get prev year ordinary earnings ytd value.
37 ** 02 Jan 2004 Nanuradh 115.26 3331018 Modified the function get_prev_year_ord_ytd by initializing the
38 ** variable l_prev_ord_ytd to zerio.
39 ** 21 Jan 2004 agore 115.27 3279235 Modified the function get_prev_year_ord_ytd ( ) to refer monthly balance
40 ** values of following balances to arrive at Annual CPF eligible OW with monthly ceiling
41 ** of 5500.CPF_ORDINARY_EARNINGS_ELIGIBLE_COMP, ORDINARY_EARNINGS_INELIGIBLE_FOR_CPF
42 ** and RETRO_ORD_RETRO_PERIOD
43 ** Added new functions get_cur_year_ord_ytd ( ) and get_retro_earnings( )
44 ** 18 May 2004 Nanuradh 115.28 3595103 Added new function spl_amount( ) to calculate S Pass Levy for permit type SP.
45 ** 18 May 2004 Nanuradh 115.32 3595103 Modified function sg_get_prorator() for permit type 'SP'
46 ** 24 Jun 2004 abhargav 115.35 Undo the changes of the Bug#3677801
47 ** 31 Jan 2005 snimmala 115.36 4149190 Modified the function sg_get_prorator() to calculate total days as number of
48 ** working days instead of days in the payroll period.
49 ** 27 Jun 2005 JLin 115.37 4267196 Performance issue, modified the function get_prev_year_ord_ytd
50 ** and get_cur_year_ord_ytd to replace ppa.date_earned with ppa.effective_date
51 ** 09 Jun 2006 JLin 115.39 5298298 Modified the function
52 ** get_prev_year_ord_ytd and
53 ** get_cur_year_ord_ytd to
54 ** include all assignments.
55 ** (eg.,rehire to include original assignment)
56 ** 13 Jun 2006 JLin 115.40 5298298 To include the Legal Entity
57 ** check for previous fix
58 ** 14 Jun 2006 JLin 115.41,42 5298298 To include the multi-assignments
59 ** 23 Jun 2006 snimmala 115.43 5353558 Modified the sql query of the cursor get_retro_method
60 ** in the function which_retro_method.
61 ** 27 Jun 2006 snimmala 115.44 5353558 Removed the check for 'Information' classification
62 ** in the function which_retro_method.
63 ** 14 Sep 2006 snimmala 115.45 5410589 Function fwl_amount() has been modified to check whether
64 ** permit category is valid for pay period or not.
65 ** 21 May 2007 snimmala 115.46 6046808 Modified the cursor c_get_dates in the function fwl_amount
66 ** to move order by clause to outer query.
67 ** 02 Jul 2007 jalin 115.47 6158284 Modified the cursor c_get_dates in the function fwl_amount
68 ** to add currect employee check
69 ** 22 Feb 2008 jalin 115.48 6815874 Modified calling function
70 ** get_retro_earnings to use
71 ** l_effective as parameter
72 ** Modified cursor c_pay_element_entries
73 ** to get correct retro values
74 ** Removed parameter ass_act_id from get_retro_earnings function
75 ** 27 Mar 2008 jalin 115.49 6815874 Added fix if retro ord
76 ** is neg
77
78 ** ============== Formula Fuctions ====================
79 ** Package containing addition processing required by
80 ** formula in SG localisation
81 */
82
83 /*
84 ** fwl_amount - returns the amount of foreign workers levy
85 ** due in a month
86 **
87 ** Error return codes used - messages raised in fast formula
88 **
89 ** -77 Invalid dates used for Work Permit
90 ** -88 Work Permit Category is null
91 ** -99 Unhandled Exception
92 */
93
94 function fwl_amount ( p_business_group_id in number
95 , p_date_earned in date
96 , p_assignment_id in number
97 , p_start_date in date
98 , p_end_date in date )
99 return number is
100
101
102 TYPE t_permit_dates_rec is record ( permit_category varchar2(60)
103 , date_start date
104 , date_end date
105 , date_cancel date
106 , effective_start_date date) ;
107
108 TYPE t_permit_dates_tab is table of t_permit_dates_rec index by binary_integer ;
109
110 l_permit t_permit_dates_tab;
111 l_counter number;
112 l_amt number := 0;
113 l_mth_amt number;
114 l_dly_amt number;
115 l_days number := 0;
116 l_tot_days number := 0;
117 l_max_days number := 0;
118 l_category varchar2(60);
119 l_same_category boolean := TRUE;
120 l_sot date;
121 l_eot date;
122 l_proc varchar2(60);
123 l_start date;
124 l_end date;
125 l_months number;
126 l_value number;
127
128 /*Bug#2626075-Distinct added in c_get_dates cursor*/
129 /*Bug#6046808 - Moved Order By clause to Outer Query */
130 /*Bug#6158284 - Added current employee flag check */
131
132 cursor c_get_dates ( p_assignment_id NUMBER
133 , p_start_date DATE
134 , p_end_date DATE ) is
135 select distinct * from
136 (
137 select per_information8
138 , to_date(per_information9, 'YYYY/MM/DD HH24:MI:SS')
139 , to_date(per_information10, 'YYYY/MM/DD HH24:MI:SS')
140 , to_date(per_information11, 'YYYY/MM/DD HH24:MI:SS')
141 , effective_start_date
142 from per_all_people_f per
143 where per.person_id = (select max(paf.person_id)
144 from per_all_assignments_f paf
145 where paf.assignment_id = p_assignment_id)
146 and per.per_information6 = 'WP'
147 and nvl(per.current_employee_flag,'N') = 'Y' /* Bug 6158284 */
148 and per.effective_start_date <= p_end_date
149 and per.effective_end_date >= p_start_date)
150 order by effective_start_date;
151
152 /*Bug#5410589 - Following cursor has been added to check whether permit category is valid
153 for this pay period or not*/
154
155 cursor c_check_permit_type(p_permit_category per_all_people_f.per_information8%type
156 ,p_date_earned DATE)
157 is
158 select CINST.value
159 from pay_user_tables tab
160 ,pay_user_columns col
161 ,pay_user_rows_f r
162 ,pay_user_column_instances_f cinst
163 where tab.user_table_name = 'FWL_RATES'
164 and col.user_table_id = tab.user_table_id
165 and upper(col.user_column_name)= upper('Daily Rate')
166 and cinst.user_column_id = col.user_column_id
167 and r.user_table_id = tab.user_table_id
168 and r.ROW_LOW_RANGE_OR_NAME = p_permit_category
169 and cinst.user_row_id = r.user_row_id
170 and p_date_earned between cinst.effective_start_date and cinst.effective_end_date;
171
172 begin
173 l_sot := to_date('0001/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS');
174 l_eot := to_date('4712/12/31 00:00:00', 'YYYY/MM/DD HH24:MI:SS');
175 l_proc := 'pay_sg_deductions.fwl_amount';
176
177
178 hr_utility.set_location('Entering : '||l_proc, 5);
179
180 l_counter := 1;
181
182 open c_get_dates( p_assignment_id, p_start_date, p_end_date) ;
183 fetch c_get_dates into l_permit(l_counter);
184 if c_get_dates%notfound then
185 hr_utility.set_location('Leaving - No fwl in month : '||l_proc, 10);
186 return 0;
187 end if;
188
189 l_counter := l_counter + 1;
190
191 fetch c_get_dates into l_permit(l_counter);
192
193 while c_get_dates%found loop
194 l_counter := l_counter + 1;
195 fetch c_get_dates into l_permit(l_counter);
196 end loop;
197
198 close c_get_dates;
199
200 l_max_days := least (nvl(l_permit(l_permit.last).date_end, l_eot), nvl(l_permit(l_permit.last).date_cancel, l_eot)) - p_start_date + 1;
201 l_category := l_permit(1).permit_category;
202
203 if l_category is not null then
204 /*Bug#5410589 */
205 open c_check_permit_type(l_category,p_date_earned);
206 fetch c_check_permit_type into l_value;
207 if c_check_permit_type%notfound then
208 return(-66);
209 end if;
210 close c_check_permit_type;
211
212 l_mth_amt := to_number(hruserdt.get_table_value (p_bus_group_id => p_business_group_id
213 ,p_table_name => 'FWL_RATES'
214 ,p_col_name => 'Monthly Rate'
215 ,p_row_value => l_category
216 ,p_effective_date => p_date_earned ));
217
218 l_dly_amt := to_number(hruserdt.get_table_value (p_bus_group_id => p_business_group_id
219 ,p_table_name => 'FWL_RATES'
220 ,p_col_name => 'Daily Rate'
221 ,p_row_value => l_category
222 ,p_effective_date => p_date_earned ));
223
224 end if;
225
226
227 FOR i in 1..l_permit.last LOOP
228
229 if l_permit(i).permit_category is null then
230
231 hr_utility.set_location('Error - WP Category is null : '||l_proc, 88);
232 return (-88);
233
234 else
235
236 if (l_permit(i).date_start > l_permit(i).date_end)
237 OR (l_permit(i).date_start > l_permit(i).date_cancel) THEN
238
239 hr_utility.set_location('Invalid Date Ranges Within Month: '||l_proc, 77);
240 return (-77);
241
242
243 elsif (l_permit(i).date_start > p_end_date )
244 OR (l_permit(i).date_end < p_start_date)
245 OR (l_permit(i).date_cancel < p_start_date) THEN
246
247 null;
248
249 else
250 /*Bug#5410589 */
251 open c_check_permit_type(l_permit(i).permit_category,p_date_earned);
252 fetch c_check_permit_type into l_value;
253 if c_check_permit_type%notfound then
254 return(-66);
255 end if;
256 close c_check_permit_type;
257
258 if l_permit(i).permit_category <> l_category then
259
260 l_same_category := FALSE;
261
262 end if;
263 l_start := greatest (nvl(l_permit(i).date_start, l_sot), p_start_date);
264 l_end := least (nvl(l_permit(i).date_end, l_eot), nvl(l_permit(i).date_cancel, l_eot));
265 /* Bug 2610156 : Least of l_end and Pay Period end date should be
266 used to calculate number of days(i.e., l_days)
267 l_end is the least of expiry date and cancellation date */
268 l_end := least(l_end,p_end_date);
269
270 l_days := greatest ((l_end - l_start)+1, 0 );
271 l_months := round(months_between(l_end + 1,l_start),2);
272
273
274 l_days := greatest ((l_end - l_start)+1, 0 );
275
276 l_tot_days := l_tot_days + l_days;
277
278
279 if l_tot_days > l_max_days then
280
281 hr_utility.set_location('Invalid Date Ranges Within Month : '||l_proc, 77);
282 return (-77);
283
284 end if;
285
286 if l_same_category then
287 if l_months >= 1 then
288 l_amt := l_mth_amt;
289 else
290 l_amt := least(l_mth_amt, (l_amt + (l_dly_amt * l_days)));
291 end if;
292
293 else
294
295 l_mth_amt := to_number(hruserdt.get_table_value (p_bus_group_id => p_business_group_id
296 ,p_table_name => 'FWL_RATES'
297 ,p_col_name => 'Monthly Rate'
298 ,p_row_value => l_permit(i).permit_category
299 ,p_effective_date => p_date_earned ));
300
301 l_dly_amt := to_number(hruserdt.get_table_value (p_bus_group_id => p_business_group_id
302 ,p_table_name => 'FWL_RATES'
303 ,p_col_name => 'Daily Rate'
304 ,p_row_value => l_permit(i).permit_category
305 ,p_effective_date => p_date_earned ));
306 if l_months >= 1 then
307 l_amt := l_mth_amt;
308 else
309 l_amt := l_amt + least(l_mth_amt, (l_dly_amt * l_days));
310 end if;
311
312 end if;
313
314 end if;
315
316 end if;
317
318 END LOOP;
319
320 hr_utility.set_location('Leaving:'||l_proc, 20);
321 return l_amt;
322
323 EXCEPTION
324 WHEN others THEN
325 hr_utility.set_location('Unhandled Exception in function call fwl_amount : '||l_proc, 99);
326 RETURN -99;
327
328 end fwl_amount;
329
330 /* Bug: 3595103 - New function to calculate S Pass Levy */
331 function spl_amount ( p_business_group_id in number
332 , p_date_earned in date
333 , p_assignment_id in number
334 , p_start_date in date
335 , p_end_date in date )
336 return number is
337
338 TYPE t_permit_dates_rec is record (permit_type varchar2(5)
339 , date_start date
340 , date_end date
341 , date_cancel date ) ;
342
343 TYPE t_permit_dates_tab is table of t_permit_dates_rec index by binary_integer ;
344
345 l_permit t_permit_dates_tab;
346 l_counter number;
347 l_amt number := 0;
348 l_mth_amt number;
349 l_dly_amt number;
350 l_days number := 0;
351 l_tot_days number := 0;
352 l_max_days number := 0;
353 l_category varchar2(60);
354 l_same_category boolean := TRUE;
355 l_sot date;
356 l_eot date;
357 l_proc varchar2(60);
358 l_start date;
359 l_end date;
360 l_months number;
361
362 cursor c_get_dates ( p_assignment_id NUMBER
363 , p_start_date DATE
364 , p_end_date DATE ) is
365 select distinct * from
366 (
367 select per_information6
368 , to_date(per_information9, 'YYYY/MM/DD HH24:MI:SS')
369 , to_date(per_information10, 'YYYY/MM/DD HH24:MI:SS')
370 , to_date(per_information11, 'YYYY/MM/DD HH24:MI:SS')
371 from per_all_people_f per
372 where per.person_id = (select max(paf.person_id)
373 from per_all_assignments_f paf
374 where paf.assignment_id = p_assignment_id)
375 and per.per_information6 = 'SP'
376 and per.effective_start_date <= p_end_date
377 and per.effective_end_date >= p_start_date
378 order by per.effective_start_date);
379
380
381 begin
382 l_sot := to_date('0001/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS');
383 l_eot := to_date('4712/12/31 00:00:00', 'YYYY/MM/DD HH24:MI:SS');
384 l_proc := 'pay_sg_deductions.spl_amount';
385
386 hr_utility.set_location('Entering : '||l_proc, 5);
387
388 l_counter := 1;
389
390 open c_get_dates( p_assignment_id, p_start_date, p_end_date) ;
391 fetch c_get_dates into l_permit(l_counter);
392 if c_get_dates%notfound then
393 hr_utility.set_location('Leaving - No fwl in month : '||l_proc, 10);
394 return 0;
395 end if;
396
397 l_counter := l_counter + 1;
398
399 fetch c_get_dates into l_permit(l_counter);
400
401 while c_get_dates%found loop
402 l_counter := l_counter + 1;
403 fetch c_get_dates into l_permit(l_counter);
404 end loop;
405
406 close c_get_dates;
407
408 l_max_days := least (nvl(l_permit(l_permit.last).date_end, l_eot), nvl(l_permit(l_permit.last).date_cancel, l_eot)) - p_start_date + 1;
409 l_category := 'SP';
410 if l_category is not null then
411
412 l_mth_amt := to_number(hruserdt.get_table_value (p_bus_group_id => p_business_group_id
413 ,p_table_name => 'FWL_RATES'
414 ,p_col_name => 'Monthly Rate'
415 ,p_row_value => l_category
419 ,p_table_name => 'FWL_RATES'
416 ,p_effective_date => p_date_earned ));
417
418 l_dly_amt := to_number(hruserdt.get_table_value (p_bus_group_id => p_business_group_id
420 ,p_col_name => 'Daily Rate'
421 ,p_row_value => l_category
422 ,p_effective_date => p_date_earned ));
423
424 end if;
425
426
427 FOR i in 1..l_permit.last LOOP
428 if (l_permit(i).date_start > l_permit(i).date_end)
429 OR (l_permit(i).date_start > l_permit(i).date_cancel) THEN
430
431 hr_utility.set_location('Invalid Date Ranges Within Month: '||l_proc, 77);
432 return (-77);
433
434
435 elsif (l_permit(i).date_start > p_end_date )
436 OR (l_permit(i).date_end < p_start_date)
437 OR (l_permit(i).date_cancel < p_start_date) THEN
438
439 null;
440 else
441 l_start := greatest (nvl(l_permit(i).date_start, l_sot), p_start_date);
442 l_end := least (nvl(l_permit(i).date_end, l_eot), nvl(l_permit(i).date_cancel, l_eot));
443 l_end := least(l_end,p_end_date);
444
445 l_days := greatest ((l_end - l_start)+1, 0 );
446 l_months := round(months_between(l_end + 1,l_start),2);
447
448
449 l_tot_days := l_tot_days + l_days;
450
451 if l_tot_days > l_max_days then
452 hr_utility.set_location('Invalid Date Ranges Within Month : '||l_proc, 77);
453 return (-77);
454 end if;
455
456 if l_months >= 1 then
457 l_amt := l_mth_amt;
458 else
459 l_amt := least(l_mth_amt, (l_amt + (l_dly_amt * l_days)));
460 end if;
461
462 end if;
463
464 END LOOP;
465
466 hr_utility.set_location('Leaving:'||l_proc, 20);
467 return l_amt;
468
469 EXCEPTION
470 WHEN others THEN
471 hr_utility.set_location('Unhandled Exception in function call spl_amount : '||l_proc, 99);
472 RETURN -99;
473
474 end spl_amount;
475
476
477 function sg_get_prorator ( p_assignment_id in number,
478 p_date_earned in date,
479 p_pay_proc_start_date in date,
480 p_pay_proc_end_date in date,
481 p_wac in varchar2,
482 p_cpf_calc_type out nocopy varchar2
483 ) return number is
484
485 l_wac varchar2(2);
486 l_effective_date date;
487 start_date date;
488 start_wac varchar2(2);
489 prorate_date date;
490 prorate_wac varchar2(2);
491 l_prorator number;
492 l_days number;
493 l_total_days number;
494 l_assign_start_date date;
495 l_assign_end_date date;
496 l_proc_start_date date;
497 l_proc_end_date date;
498 l_emp_start_bet_period varchar2(1);
499 l_emp_end_bet_period varchar2(1);
500 l_proc varchar2(60);
501
502 /* Cursor declaration */
503 cursor c_get_per_start_end_dates (c_assignment_id number ,
504 c_pay_proc_start_date date,
505 c_pay_proc_end_date date)
506 is
507 select min(pap.effective_start_date),max(pap.effective_end_date) /*bug 2772106 */
508 from per_all_people_f pap,
509 per_all_assignments_f target
510 where target.assignment_id = c_assignment_id
511 and pap.person_id = target.person_id
512 and nvl(pap.current_employee_flag,'N') = 'Y';
513
514 cursor c_get_wac ( c_assignment_id NUMBER,
515 c_date DATE )
516 is
517 select target.PER_INFORMATION6,
518 target.EFFECTIVE_START_DATE
519 from per_all_people_f target
520 where target.person_id = (select paf.person_id
521 from per_all_assignments_f paf
522 where paf.assignment_id = c_assignment_id
523 and c_date between paf.effective_start_date and paf.effective_end_date )
524 and c_date between target.effective_start_date and target.effective_end_date;
525
526 begin
527 l_prorator := -1.0;
528 l_emp_start_bet_period := 'N';
529 l_emp_end_bet_period := 'N';
530 l_proc := 'pay_sg_deductions.sg_get_prorator';
531
532 /* Get the WAC effective at start of the pay period */
533 hr_utility.set_location('Entering : '||l_proc, 5);
534 hr_utility.trace('p_assignment_id : '||p_assignment_id);
535 hr_utility.trace('p_pay_proc_start_date : '||p_pay_proc_start_date );
536 hr_utility.trace('p_pay_proc_end_date : '||p_pay_proc_end_date );
537 hr_utility.trace('p_date_earned : '||p_date_earned );
538 hr_utility.trace('p_wac : '||p_wac);
539
540 p_cpf_calc_type := p_wac ; /* assign the default value of WAC for the person as WAC exist at period end date */
541
545 if c_get_per_start_end_dates%NOTFOUND then
542 open c_get_per_start_end_dates(p_assignment_id , p_pay_proc_start_date, p_pay_proc_end_date);
543 fetch c_get_per_start_end_dates into l_assign_start_date,l_assign_end_date;
544
546 close c_get_per_start_end_dates;
547 hr_utility.set_location('Error : Assignment does not exist in the pay process period'||l_proc,5);
548 else
549 if (l_assign_start_date > p_pay_proc_start_date ) then
550 l_proc_start_date := l_assign_start_date;
551 else
552 l_proc_start_date := p_pay_proc_start_date;
553 end if;
554 if (l_assign_end_date < p_pay_proc_end_date ) then
555 l_proc_end_date := l_assign_end_date;
556 else
557 l_proc_end_date := p_pay_proc_end_date;
558 end if;
559 end if;
560
561 open c_get_wac( p_assignment_id , l_proc_start_date );
562 fetch c_get_wac into l_wac, l_effective_date;
563 if c_get_wac%NOTFOUND then
564 close c_get_wac;
565 hr_utility.set_location('Error : Assignment doesnot exist at the pay proc start date'||l_proc,10);
566 return 1; /* Bug#2858065 */
567 end if;
568 close c_get_wac;
569 /* store the wac at the start of the period */
570 start_wac := l_wac;
571 /* store the wac effective start date */
572 start_date := l_effective_date;
573
574 /* if wac effective start date is less than pay proc start date,then
575 set the pay proc start date as the start_date */
576 if (l_effective_date < p_pay_proc_start_date) then
577 start_date := p_pay_proc_start_date;
578 end if;
579
580 hr_utility.trace('start_wac : '||start_wac);
581 hr_utility.trace('start_date : '||start_date);
582
583 /* Get the WAC and at the period end date*/
584
585 open c_get_wac( p_assignment_id,l_proc_end_date);
586 fetch c_get_wac into l_wac, l_effective_date;
587 if c_get_wac%NOTFOUND then
588 close c_get_wac;
589 hr_utility.set_location('Error : Assignment doesnot exist at the pay proc end date (ie terminated)'||l_proc,20);
590 return 1; /* Bug#2873083 */
591 end if;
592 close c_get_wac;
593
594 /* store the wac at the end of the pay period*/
595 prorate_wac := l_wac;
596 /* store the effective start date for the above wac*/
597 prorate_date := l_effective_date;
598
599 /* if wac effective start date is less than pay proc start date ,then
600 set the pay proc start date as the prorate_date */
601
602 if (l_effective_date < p_pay_proc_start_date ) then
603 prorate_date := p_pay_proc_start_date;
604 end if;
605
606 hr_utility.trace('prorate_wac : '||prorate_wac);
607 hr_utility.trace('prorate_date : '||prorate_date);
608
609 if (p_date_earned <> start_date ) then
610 l_total_days := fffunc.days_between(l_proc_end_date , l_proc_start_date)+1;
611 -------------------------------------------------------------------------------------------------
612 --Bug# 4149190
613 --p_pay_proc_start_date, p_pay_proc_end_date are replaced by l_proc_start_date,
614 --l_proc_end_date respectively.
615 -------------------------------------------------------------------------------------------------
616 hr_utility.trace('l_total_days : '||l_total_days );
617 end if;
618
619 /* Proration Calculation Block */
620 /* If the employee start date or end date is in between the pay period then
621 for proration we have to take the employee start / end date instead of period
622 start/end date .That is , if the employee has started in between the pay period,then
623 we will take this date for the calculation of proration instead of pay period start date.
624 Similarly if the emplyee is terminated in between then we will take the termination date
625 instead of period end date.In such case the proration is calculated for example
626 from employee start date to period end date divided by the number of days in the period*/
627
628 If (l_proc_start_date > p_pay_proc_start_date and l_proc_start_date < p_pay_proc_end_date) then
629 l_emp_start_bet_period := 'Y';
630 end if;
631
632 If (l_proc_end_date > p_pay_proc_start_date and l_proc_end_date < p_pay_proc_end_date) then
633 l_emp_end_bet_period := 'Y';
634 end if;
635
636 if (start_wac = prorate_wac and (start_wac = 'PR' or start_wac = 'SG')) then
637 p_cpf_calc_type := prorate_wac; /*2494173*/
638 l_prorator := 1.0;
639 else
640 /* Proration need to be accounted for the eligible duration of pay period */
641 /* Bug: 3595103 - Modified get_sg_prorator for Permit type S Pass - SP */
642
643 if ((start_wac = 'PR' or start_wac = 'SG') and (prorate_wac = 'EP' or prorate_wac = 'WP' or start_wac = 'SP'))then
644 /* Need to calculate the proration for the first period, since the wac in the second period is
645 not eligible for CPF*/
646 l_days := fffunc.days_between(prorate_date,start_date);
647 p_cpf_calc_type := start_wac;
648 l_prorator := l_days / l_total_days;
649 hr_utility.trace('p_cpf_calc_type :'||p_cpf_calc_type);
650 hr_utility.trace('l_days : '||l_days );
651
652 elsif ((start_wac = 'WP' or start_wac = 'EP' or start_wac = 'SP') and (prorate_wac = 'PR' or prorate_wac = 'SG')) then
653 /* Need to calculate the proration for the second period and first period does not have
654 eligible for the CPF Proration */
658 p_cpf_calc_type := prorate_wac;
655 l_days := fffunc.days_between(l_proc_end_date,prorate_date)+1;
656 /* Added +1 to include prorate date also */
657 hr_utility.trace('l_days : '||l_days );
659 l_prorator := l_days / l_total_days;
660
661 elsif ((start_wac = 'SG' or start_wac = 'PR') and (prorate_wac = 'SG' or prorate_wac = 'PR')) then
662 /* Need not calculate the CPF Calculation. Hence setting the l_prorator value to 1 and the
663 CPF Calcualtion type to prorate type */
664 if (l_emp_start_bet_period = 'Y' and l_emp_end_bet_period = 'Y' ) then
665 l_days := fffunc.days_between(l_proc_end_date, l_proc_start_date)+1;
666 l_prorator := l_days/l_total_days;
667
668 elsIf (l_emp_start_bet_period = 'Y') then
669 l_days := fffunc.days_between(p_pay_proc_end_date, l_proc_start_date)+1;
670 l_prorator := l_days/l_total_days;
671
672 elsif (l_emp_end_bet_period = 'Y') then
673 l_days := fffunc.days_between(l_proc_end_date,p_pay_proc_start_date)+1;
674 l_prorator := l_days/l_total_days;
675
676 else
677 l_prorator := 1.0;
678 end if;
679 p_cpf_calc_type := prorate_wac;
680
681 elsif ((start_wac = 'WP' or start_wac = 'EP' or start_wac = 'SP') and
682 (prorate_wac = 'WP' or prorate_wac = 'EP' or start_wac = 'SP')) then
683 /* Need not calculate the CPF Calculation. Hence setting the l_prorator value to -1 and the
684 CPF Calcualtion type to prorate type */
685 l_prorator := -1.0;
686 p_cpf_calc_type := prorate_wac;
687
688 end if;
689 end if;
690 /* End of Proration Calculation */
691 hr_utility.trace('p_cpf_calc_type :'||p_cpf_calc_type);
692 hr_utility.trace('l_prorator : '||l_prorator);
693 hr_utility.set_location('Leaving : '||l_proc, 5);
694
695 return l_prorator;
696 end sg_get_prorator;
697
698 /*
699 ** This function will identify if the element being processed is a retropay element.
700 ** If it is a flag set to Y is returned.
701 */
702
703 function check_if_retro
704 (
705 p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
706 p_date_earned in pay_payroll_actions.date_earned%TYPE
707 ) return varchar2 IS
708
709 cursor c_get_creator_type(
710 c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
711 c_date_earned in pay_payroll_actions.date_earned%TYPE
712 ) is
713 SELECT creator_type
714 FROM pay_element_entries_f pee
715 WHERE pee.element_entry_id = c_element_entry_id
716 AND c_date_earned between pee.effective_start_date and pee.effective_end_date;
717
718 l_creator_type pay_element_entries_f.creator_type%TYPE;
719 IS_retro_payment varchar2(10);
720
721 begin
722
723 /* Check creator_type to identify if its a retropay element.
724 ** Creator_type of RR (updated element entry) or EE (new element entry) indicates
725 ** it's a retropay element.
726 */
727
728 OPEN c_get_creator_type(p_element_entry_id,p_date_earned);
729 FETCH c_get_creator_type INTO l_creator_type ;
730 CLOSE c_get_creator_type;
731 if l_creator_type = 'RR' or l_creator_type = 'EE' then
732 IS_retro_payment:='Y';
733 else
734 IS_retro_payment:='N';
735 end if;
736
737 return IS_retro_payment;
738
739 EXCEPTION
740 when others then
741 IS_retro_payment:='N';
742
743 end check_if_retro;
744
745
746 /*
747 ** This function will identify which retropay method the retropay element was
748 ** created under.
749 ** Eg. Retropay method A indicates that CPF calculations are to be performed in the
750 ** current payroll period and therefore the Retropay By Element run had no CPF
751 ** elements in the element set.
752 ** Retropay method B indicates that CPF calculations are to be performed in the
753 ** period the retrospective payment was earnt therefore the Retropay By Element run
754 ** DID have CPF elements in the element set.
755 **
756 */
757
758 function which_retro_method
759 (
760 p_assignment_id in pay_assignment_actions.assignment_id%TYPE,
761 p_date_earned in pay_payroll_actions.date_earned%TYPE,
762 p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE
763 ) return varchar2 IS
764
765 /*
766 ** Bug#5353558 Cursor to look for any CPF elements exists in the Element Set of
767 ** the retro pay, which has created this retro element.
768 */
769
770 cursor get_retro_method
771 ( c_assignment_id in pay_element_entries_f.element_entry_id%TYPE,
772 c_date_earned in pay_payroll_actions.date_earned%TYPE,
773 c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE
774 ) is
775 select pet.element_name
776 from pay_element_entries_f pee,
777 pay_assignment_actions paa,
778 pay_payroll_actions ppa,
779 pay_element_sets pes,
780 pay_element_type_rules petr,
781 pay_element_types_f pet
782 where pee.creator_id = paa.assignment_action_id
783 and pee.assignment_id = c_assignment_id
784 and pee.creator_type in ('EE', 'RR')
788 and pes.element_set_id = petr.element_set_id
785 and pee.element_entry_id = c_element_entry_id
786 and paa.payroll_action_id = ppa.payroll_action_id
787 and ppa.element_set_id = pes.element_set_id
789 and petr.element_type_id = pet.element_type_id
790 and petr.include_or_exclude = 'I'
791 and c_date_earned between pet.effective_start_date and pet.effective_end_date
792 and c_date_earned between pee.effective_start_date and pee.effective_end_date
793 and pet.element_name like '%CPF%'
794 and pet.legislation_code = 'SG'
795 union all
796 select pec.classification_name
797 from pay_element_entries_f pee,
798 pay_assignment_actions paa,
799 pay_payroll_actions ppa,
800 pay_element_sets pes,
801 pay_ele_classification_rules pecr,
802 pay_element_classifications pec
803 where pee.creator_id = paa.assignment_action_id
804 and pee.assignment_id = c_assignment_id
805 and pee.creator_type in ('EE', 'RR')
806 and pee.element_entry_id = c_element_entry_id
807 and paa.payroll_action_id = ppa.payroll_action_id
808 and ppa.element_set_id = pes.element_set_id
809 and pes.element_set_id = pecr.element_set_id
810 and pecr.classification_id = pec.classification_id
811 and c_date_earned between pee.effective_start_date and pee.effective_end_date
812 and pec.classification_name in ('Statutory Deductions', 'Employer Liabilities')
813 and pec.legislation_code = 'SG';
814
815 l_ele_rec get_retro_method%ROWTYPE;
816 l_retro_method varchar2(10);
817
818 begin
819
820 /* Default to method A. */
821
822 l_retro_method := 'A';
823
824 /*
825 ** Bug# 5353558 If any of the elements processed in the retro pay were CPF elements and
826 ** seeded then the retropay process must have been for Method B.
827 */
828
829 open get_retro_method(p_assignment_id,p_date_earned,p_element_entry_id);
830 fetch get_retro_method into l_ele_rec;
831 if get_retro_method%FOUND then
832 l_retro_method := 'B';
833 close get_retro_method;
834 end if;
835
836 return l_retro_method;
837
838 end which_retro_method;
839
840 /*
841 ** This function will identify the earnings type of the retropay element.
842 ** If the balance it feeds is 'CPF Ordinary Earnings Eligible Comp' then it
843 ** must be classed as Ordinary Earnings (type O).
844 ** If the balance it feeds is 'CPF Additional Earnings Eligible Comp' then it
845 ** must be classed as Additional Earnings (type A).
846 ** If neither of these then leave blank.
847 */
848
849 function earnings_type
850 (
851 p_element_type_id in pay_element_types_f.element_type_id%TYPE
852 ) return varchar2 IS
853
854 cursor c_earnings_type
855 ( c_element_type_id in pay_element_types_f.element_type_id%TYPE,
856 c_balance_name in pay_balance_types.balance_name%TYPE
857 ) is
858 select decode(pbt.balance_name,
859 'CPF Ordinary Earnings Eligible Comp', 'O',
860 'CPF Additional Earnings Eligible Comp', 'A', ' ')
861 from pay_balance_types pbt,
862 pay_balance_feeds_f pbf,
863 pay_input_values_f pivf,
864 pay_element_types_f petf
865 where pbt.balance_type_id = pbf.balance_type_id
866 and pbf.input_value_id = pivf.input_value_id
867 and pivf.element_type_id = petf.element_type_id
868 and pbt.balance_name = c_balance_name
869 and petf.element_type_id = c_element_type_id;
870
871 l_earnings_type varchar2(10);
872 l_balance_name pay_balance_types.balance_name%TYPE;
873
874 begin
875 l_earnings_type := ' ';
876 hr_utility.set_location('Entering Earnings Type : ', 5);
877 hr_utility.set_location('Element Type Id : ' || p_element_type_id, 10);
878
879 l_balance_name := 'CPF Ordinary Earnings Eligible Comp';
880 open c_earnings_type(p_element_type_id, l_balance_name);
881 fetch c_earnings_type into l_earnings_type;
882 close c_earnings_type;
883
884 if l_earnings_type <> 'O' then
885 l_balance_name := 'CPF Additional Earnings Eligible Comp';
886 open c_earnings_type(p_element_type_id, l_balance_name);
887 fetch c_earnings_type into l_earnings_type;
888 close c_earnings_type;
889 end if;
890
891 hr_utility.set_location('Earnings Type : ' || l_earnings_type, 15);
892
893 return l_earnings_type;
894
895 EXCEPTION
896 when others then
897 l_earnings_type := ' ';
898 hr_utility.set_location('Exception Earnings Type : ', 20);
899
900 end earnings_type;
901 ---------------------------------------------------------------------------
902 -- Function returns Previous Year Ordinary Earnings total with
903 -- Monthly ceiling of 5,500
904 ---------------------------------------------------------------------------
905 function get_prev_year_ord_ytd
906 (
907 p_assignment_id in pay_assignment_actions.assignment_id%TYPE,
908 p_date_earned in pay_payroll_actions.date_earned%TYPE
909 )
910 return number is
911 --
912 cursor c_tax_unit_id( c_assignment_id number,
913 c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
914 is
915 select paa.tax_unit_id
916 from pay_assignment_actions paa
920 --------------------------------------------------------------------
917 where paa.assignment_id = c_assignment_id
918 and paa.assignment_action_id = c_assignment_action_id;
919 --
921 -- Bug 5298298, need to get action sequence to include all the assignment
922 -- for the re-hire employee, it will include the original assignment. For
923 -- multi-assignments with the same LE will be included.
924 --------------------------------------------------------------------
925 cursor c_month_year_action_sequence( c_assignment_id number,
926 c_date_earned date )
927 is
928 select max(paa.action_sequence),
929 to_number(to_char(ppa.effective_date,'MM')),
930 max(pas.person_id)
931 from per_assignments_f pas,
932 pay_assignment_actions paa,
933 pay_payroll_actions ppa
934 where (pas.person_id, paa.tax_unit_id)
935 IN (select pas1.person_id,
936 hsc1.segment1
937 from per_assignments_f pas1,
938 hr_soft_coding_keyflex hsc1
939 where pas1.assignment_id = c_assignment_id
940 and pas1.soft_coding_keyflex_id = hsc1.soft_coding_keyflex_id
941 and c_date_earned between pas1.effective_start_date and pas1.effective_end_date) /* Bug 5298298 */
942 and pas.assignment_id = paa.assignment_id
943 and ppa.payroll_action_id = paa.payroll_action_id
944 and ppa.action_type in ('R','Q','B','V','I')
945 and ppa.effective_date between trunc(add_months(c_date_earned,-12),'Y') /* Bug 4267196 */
946 and trunc(c_date_earned,'Y') - 1
947 group by to_number(to_char(ppa.effective_date,'MM'))
948 order by to_number(to_char(ppa.effective_date,'MM')) desc;
949 --
950 cursor c_month_year_action ( c_person_id number,
951 c_date_earned date,
952 c_action_sequence number )
953 is
954 select paa.assignment_action_id,
955 ppa.effective_date,
956 paa.assignment_id
957 from per_assignments_f pas,
958 pay_assignment_actions paa,
959 pay_payroll_actions ppa
960 where pas.person_id = c_person_id /* Bug 5298298 */
961 and paa.assignment_id = pas.assignment_id
962 and ppa.payroll_action_id = paa.payroll_action_id
963 and paa.action_sequence = c_action_sequence
964 and ppa.effective_date between trunc(add_months(c_date_earned,-12),'Y') /* Bug 4267196 */
965 and trunc(c_date_earned,'Y') - 1;
966 --
967 cursor c_defined_bal_id ( p_balance_name in varchar2,
968 p_dimension_name in varchar2 )
969 is
970 select pdb.defined_balance_id
971 from pay_defined_balances pdb,
972 pay_balance_types pbt,
973 pay_balance_dimensions pbd
974 where pbt.balance_name = p_balance_name
975 and pbd.dimension_name = p_dimension_name
976 and pbt.balance_type_id = pdb.balance_type_id
977 and pdb.balance_dimension_id = pbd.balance_dimension_id
978 and pdb.legislation_code = 'SG';
979 --
980 cursor c_globals
981 is
982 select global_value
983 from ff_globals_f
984 where global_name = 'CPF_ORD_MONTH_CAP_AMT'
985 and p_date_earned between effective_start_date and effective_end_date;
986 --
987 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
988 g_context_tab pay_balance_pkg.t_context_tab;
989 g_detailed_bal_out_tab pay_balance_pkg.t_detailed_bal_out_tab;
990 --
991 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
992 l_action_sequence pay_assignment_actions.action_sequence%TYPE;
993 l_assignment_id pay_assignment_actions.assignment_id%TYPE;
994 l_person_id per_assignments_f.person_id%TYPE;
995 l_month number;
996 l_effective_date date;
997 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
998 l_defined_bal_id number;
999 l_prev_ord_ytd number;
1000 l_ord_mon_cap_amt number;
1001 l_retro_exist boolean := FALSE ;
1002 l_retro_ele number;
1003 l_retro_date date;
1004 begin
1005 l_prev_ord_ytd := 0;
1006 --
1007 open c_globals;
1008 fetch c_globals into l_ord_mon_cap_amt;
1009 close c_globals ;
1010 --
1011 g_balance_value_tab.delete;
1012 --
1013 open c_defined_bal_id('CPF Ordinary Earnings Eligible Comp','_PER_LE_MONTH');
1014 fetch c_defined_bal_id into g_balance_value_tab(1).defined_balance_id;
1015 close c_defined_bal_id;
1016 --
1017 open c_defined_bal_id('Ordinary Earnings ineligible for CPF','_PER_LE_MONTH');
1018 fetch c_defined_bal_id into g_balance_value_tab(2).defined_balance_id;
1019 close c_defined_bal_id;
1020 --
1021 open c_defined_bal_id('Retro Ord Retro Period','_ASG_PTD');
1022 fetch c_defined_bal_id into g_balance_value_tab(3).defined_balance_id;
1023 close c_defined_bal_id;
1024 --
1025 open c_month_year_action_sequence( p_assignment_id, p_date_earned );
1026 loop
1030 --
1027 fetch c_month_year_action_sequence into l_action_sequence,l_month,l_person_id;
1028
1029 exit when c_month_year_action_sequence%NOTFOUND;
1031 open c_month_year_action( l_person_id, p_date_earned, l_action_sequence );
1032 fetch c_month_year_action into l_assignment_action_id,l_effective_date,l_assignment_id;
1033
1034 --
1035 if c_month_year_action%FOUND then
1036 open c_tax_unit_id(l_assignment_id , l_assignment_action_id );
1037 fetch c_tax_unit_id into l_tax_unit_id;
1038 close c_tax_unit_id;
1039 --
1040 g_context_tab.delete;
1041 g_detailed_bal_out_tab.delete;
1042 --
1043 g_context_tab(1).tax_unit_id := l_tax_unit_id;
1044 g_context_tab(2).tax_unit_id := l_tax_unit_id;
1045 g_context_tab(3).tax_unit_id := l_tax_unit_id;
1046 --
1047 pay_balance_pkg.get_value ( l_assignment_action_id,
1048 g_balance_value_tab,
1049 g_context_tab,
1050 false,
1051 false,
1052 g_detailed_bal_out_tab
1053 );
1054 --
1055 if l_retro_exist
1056 or nvl(g_detailed_bal_out_tab(3).balance_value,0) <> 0 then /* Bug 6815874 */
1057 l_retro_ele := get_retro_earnings( p_assignment_id , l_effective_date ); /* Bug 6815874 */
1058 if l_retro_ele = 0 then /* Bug 6815874 */
1059 l_retro_exist := FALSE;
1060 end if;
1061 l_prev_ord_ytd := l_prev_ord_ytd + least( (nvl( g_detailed_bal_out_tab(1).balance_value,0 )
1062 - nvl( g_detailed_bal_out_tab(2).balance_value,0 )
1063 - nvl( g_detailed_bal_out_tab(3).balance_value,0 )
1064 + nvl(l_retro_ele,0)),l_ord_mon_cap_amt );
1065 else
1066 l_prev_ord_ytd := l_prev_ord_ytd + least( (nvl( g_detailed_bal_out_tab(1).balance_value,0 )
1067 - nvl( g_detailed_bal_out_tab(2).balance_value,0 )
1068 - nvl( g_detailed_bal_out_tab(3).balance_value,0 )),l_ord_mon_cap_amt );
1069 end if;
1070 --
1071 if nvl( g_detailed_bal_out_tab(3).balance_value,0 ) <> 0 then
1072 l_retro_exist := TRUE;
1073 end if;
1074 --
1075 end if;
1076 --
1077 close c_month_year_action;
1078 end loop;
1079 --
1080 close c_month_year_action_sequence;
1081 --
1082 return l_prev_ord_ytd;
1083 --
1084 end get_prev_year_ord_ytd;
1085
1086 ---------------------------------------------------------------------------
1087 -- Function returns Current Year Ordinary Earnings total with
1088 -- Monthly ceiling of 5,500
1089 ---------------------------------------------------------------------------
1090 function get_cur_year_ord_ytd
1091 (
1092 p_assignment_id in pay_assignment_actions.assignment_id%TYPE,
1093 p_date_earned in pay_payroll_actions.date_earned%TYPE
1094 )
1095 return number is
1096 --
1097 cursor c_tax_unit_id( c_assignment_id number,
1098 c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
1099 is
1100 select paa.tax_unit_id
1101 from pay_assignment_actions paa
1102 where paa.assignment_id = c_assignment_id
1103 and paa.assignment_action_id = c_assignment_action_id;
1104 --
1105 --------------------------------------------------------------------
1106 -- Bug 5298298, need to get action sequence to include all the assignment
1107 -- for the re-hire employee, it will include the original assignment. For
1108 -- multi-assignments with the same LE will be included.
1109 --------------------------------------------------------------------
1110 cursor c_month_year_action_sequence( c_assignment_id number,
1111 c_date_earned date )
1112 is
1113 select max(paa.action_sequence),
1114 to_number(to_char(ppa.effective_date,'MM')),
1115 max(pas.person_id)
1116 from per_assignments_f pas,
1117 pay_assignment_actions paa,
1118 pay_payroll_actions ppa
1119 where (pas.person_id, paa.tax_unit_id)
1120 IN (select pas1.person_id,
1121 hsc1.segment1
1122 from per_assignments_f pas1,
1123 hr_soft_coding_keyflex hsc1
1124 where pas1.assignment_id = c_assignment_id
1125 and pas1.soft_coding_keyflex_id = hsc1.soft_coding_keyflex_id
1126 and c_date_earned between pas1.effective_start_date and pas1.effective_end_date) /* Bug 5298298 */
1127 and pas.assignment_id = paa.assignment_id
1128 and ppa.payroll_action_id = paa.payroll_action_id
1129 and ppa.action_type in ('R','Q','B','V','I')
1133 order by to_number(to_char(ppa.effective_date,'MM')) desc;
1130 and ppa.effective_date between trunc(c_date_earned,'Y') /* Bug 4267196 */
1131 and last_day(add_months(c_date_earned,-1))
1132 group by to_number(to_char(ppa.effective_date,'MM'))
1134 --
1135 cursor c_month_year_action ( c_person_id number,
1136 c_date_earned date,
1137 c_action_sequence number )
1138 is
1139 select paa.assignment_action_id,
1140 ppa.effective_date,
1141 pas.assignment_id
1142 from per_assignments_f pas,
1143 pay_assignment_actions paa,
1144 pay_payroll_actions ppa
1145 where pas.person_id = c_person_id /* Bug 5298298 */
1146 and paa.assignment_id = pas.assignment_id
1147 and ppa.payroll_action_id = paa.payroll_action_id
1148 and paa.action_sequence = c_action_sequence
1149 and ppa.effective_date between trunc(c_date_earned,'Y') /* Bug 4267196 */
1150 and last_day(add_months(c_date_earned,-1)) ;
1151 --
1152 cursor c_defined_bal_id ( p_balance_name in varchar2,
1153 p_dimension_name in varchar2 )
1154 is
1155 select pdb.defined_balance_id
1156 from pay_defined_balances pdb,
1157 pay_balance_types pbt,
1158 pay_balance_dimensions pbd
1159 where pbt.balance_name = p_balance_name
1160 and pbd.dimension_name = p_dimension_name
1161 and pbt.balance_type_id = pdb.balance_type_id
1162 and pdb.balance_dimension_id = pbd.balance_dimension_id
1163 and pdb.legislation_code = 'SG';
1164 --
1165 cursor c_globals
1166 is
1167 select global_value
1168 from ff_globals_f
1169 where global_name = 'CPF_ORD_MONTH_CAP_AMT'
1170 and p_date_earned between effective_start_date and effective_end_date;
1171 --
1172 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
1173 g_context_tab pay_balance_pkg.t_context_tab;
1174 g_detailed_bal_out_tab pay_balance_pkg.t_detailed_bal_out_tab;
1175 --
1176 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
1177 l_action_sequence pay_assignment_actions.action_sequence%TYPE;
1178 l_assignment_id pay_assignment_actions.assignment_id%TYPE;
1179 l_person_id per_assignments_f.person_id%TYPE;
1180 l_month number;
1181 l_effective_date date;
1182 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
1183 l_defined_bal_id number;
1184 l_cur_ord_ytd number;
1185 l_ord_mon_cap_amt number;
1186 l_retro_exist boolean := FALSE ;
1187 l_retro_ele number;
1188 l_retro_date date;
1189 begin
1190 l_cur_ord_ytd := 0;
1191 --
1192 open c_globals;
1193 fetch c_globals into l_ord_mon_cap_amt;
1194 close c_globals ;
1195 --
1196 g_balance_value_tab.delete;
1197 --
1198 open c_defined_bal_id('CPF Ordinary Earnings Eligible Comp','_PER_LE_MONTH');
1199 fetch c_defined_bal_id into g_balance_value_tab(1).defined_balance_id;
1200 close c_defined_bal_id;
1201 --
1202 open c_defined_bal_id('Ordinary Earnings ineligible for CPF','_PER_LE_MONTH');
1203 fetch c_defined_bal_id into g_balance_value_tab(2).defined_balance_id;
1204 close c_defined_bal_id;
1205 --
1206 open c_defined_bal_id('Retro Ord Retro Period','_ASG_PTD');
1207 fetch c_defined_bal_id into g_balance_value_tab(3).defined_balance_id;
1208 close c_defined_bal_id;
1209 --
1210 open c_month_year_action_sequence( p_assignment_id, p_date_earned );
1211 loop
1212 fetch c_month_year_action_sequence into l_action_sequence,l_month,l_person_id;
1213 exit when c_month_year_action_sequence%NOTFOUND;
1214 --
1215 open c_month_year_action( l_person_id, p_date_earned, l_action_sequence );
1216 fetch c_month_year_action into l_assignment_action_id,l_effective_date,l_assignment_id;
1217 --
1218 if c_month_year_action%FOUND then
1219 open c_tax_unit_id( l_assignment_id, l_assignment_action_id );
1220 fetch c_tax_unit_id into l_tax_unit_id;
1221 close c_tax_unit_id;
1222 --
1223 g_context_tab.delete;
1224 g_detailed_bal_out_tab.delete;
1225 --
1226 g_context_tab(1).tax_unit_id := l_tax_unit_id;
1227 g_context_tab(2).tax_unit_id := l_tax_unit_id;
1228 g_context_tab(3).tax_unit_id := l_tax_unit_id;
1229 --
1230 pay_balance_pkg.get_value ( l_assignment_action_id,
1231 g_balance_value_tab,
1232 g_context_tab,
1233 false,
1234 false,
1235 g_detailed_bal_out_tab
1236 );
1237 --
1238 if l_retro_exist
1239 or nvl(g_detailed_bal_out_tab(3).balance_value,0) <> 0 then /* Bug 6815874 */
1240 l_retro_ele := get_retro_earnings( p_assignment_id , l_effective_date ); /* Bug 6815874 */
1244 l_cur_ord_ytd := l_cur_ord_ytd + least( (nvl( g_detailed_bal_out_tab(1).balance_value,0 )
1241 if l_retro_ele = 0 then /* Bug 6815874 */
1242 l_retro_exist := FALSE;
1243 end if;
1245 - nvl( g_detailed_bal_out_tab(2).balance_value,0 )
1246 - nvl( g_detailed_bal_out_tab(3).balance_value,0 )
1247 + nvl(l_retro_ele,0)),l_ord_mon_cap_amt );
1248 else
1249 l_cur_ord_ytd := l_cur_ord_ytd + least( (nvl( g_detailed_bal_out_tab(1).balance_value,0 )
1250 - nvl( g_detailed_bal_out_tab(2).balance_value,0 )
1251 - nvl( g_detailed_bal_out_tab(3).balance_value,0 )),l_ord_mon_cap_amt );
1252 end if;
1253 --
1254 if nvl( g_detailed_bal_out_tab(3).balance_value,0 ) <> 0 then
1255 l_retro_exist := TRUE;
1256 end if;
1257
1258 --
1259 end if;
1260 --
1261 close c_month_year_action;
1262 end loop;
1263 --
1264 close c_month_year_action_sequence;
1265 --
1266 return l_cur_ord_ytd;
1267 --
1268 end get_cur_year_ord_ytd;
1269 --
1270 function get_retro_earnings( p_assignment_id in pay_assignment_actions.assignment_id%TYPE,
1271 p_date_earned in date ) return number
1272 is
1273 cursor c_pay_element_entries
1274 is
1275 select sum(peev.screen_entry_value)
1276 from pay_element_entry_values_f peev,
1277 pay_element_entries_f pee,
1278 pay_element_types_f pet,
1279 pay_input_values_f piv,
1280 pay_element_classifications pec
1281 where pee.assignment_id = p_assignment_id
1282 and pee.source_asg_action_id in
1283 (select paa1.assignment_action_id
1284 from pay_assignment_actions paa1,
1285 pay_payroll_actions ppa1
1286 where paa1.assignment_id = pee.assignment_id
1287 and ppa1.payroll_action_id = paa1.payroll_action_id
1288 and ppa1.action_type in ('R','Q','B','V','I')
1289 and to_char(ppa1.effective_date,'MM') = to_char(p_date_earned,'MM'))
1290 and pee.creator_type in ('EE','RR')
1291 and pee.element_type_id = pet.element_type_id
1292 and pet.classification_id = pec.classification_id
1293 and pec.classification_name = 'Ordinary Earnings'
1294 and pec.legislation_code = 'SG'
1295 and pee.element_entry_id = peev.element_entry_id
1296 and peev.input_value_id = piv.input_value_id
1297 and piv.name = 'Pay Value'
1298 and p_date_earned between pee.source_start_date
1299 and pee.source_end_date
1300 and p_date_earned between pet.effective_start_date
1301 and pet.effective_end_date
1302 and p_date_earned between piv.effective_start_date
1303 and piv.effective_end_date ;
1304 --
1305 l_retro_value number;
1306 begin
1307 open c_pay_element_entries;
1308 fetch c_pay_element_entries into l_retro_value;
1309 close c_pay_element_entries ;
1310 --
1311 return l_retro_value;
1312 --
1313 end get_retro_earnings;
1314
1315 /*****************************************
1316 CPF Report section : bugno 2475324
1317 *****************************************/
1318
1319 /* Initialize all the contexts required for SG_STAT*/
1320 Procedure init_formula (p_formula_name in varchar2,
1321 p_effective_date in date ) is
1322 --
1323 l_effective_date date;
1324 l_start_date date;
1325 l_formula_id number;
1326 --
1327 cursor c_formula_id (c_formula_name varchar2, c_effective_date date) is
1328 select formula_id, effective_start_date
1329 from ff_formulas_f
1330 where formula_name = c_formula_name
1331 and legislation_code = 'SG'
1332 and c_effective_date between effective_start_date and effective_end_date;
1333
1334 Begin
1335 l_effective_date := to_date('1-10-2003','dd-mm-yyyy');
1336 l_start_date := to_date('1-10-2003','dd-mm-yyyy');
1337
1338 /* This function call returns -1 if the formula was not found */
1339 hr_utility.set_location('Starting init',5);
1340 hr_utility.trace('Formula_id:'||l_formula_id);
1341
1342 open c_formula_id(p_formula_name, p_effective_date);
1343 fetch c_formula_id into l_formula_id, l_start_date;
1344 if c_formula_id%NOTFOUND then
1345 close c_formula_id;
1346 else
1347
1348 hr_utility.trace('Formula_id:'||l_formula_id);
1349 ff_exec.init_formula (l_formula_id,
1350 l_start_date,
1351 g_inputs,
1352 g_outputs);
1353 end if;
1354 --
1355 hr_utility.set_location('Leaving init',10);
1356
1357 End init_formula;
1358
1359 Function calc_cpf_add_YTD (p_date_earned in date
1360 ,p_assignment_id in number
1361 ,p_process_type in varchar2
1362 ,p_tax_unit_id in number
1363 ,p_asg_action_id in number
1367 ,p_balance_date in date
1364 ,p_business_group_id in number
1365 ,p_payroll_action_id in number
1366 ,p_payroll_id in number
1368 ) return number is
1369
1370 l_cpf_add_YTD number;
1371
1372 Begin
1373 --
1374 hr_utility.set_location('Entering get_bal',7);
1375 --
1376 init_formula('SG_STAT',p_date_earned);
1377 --
1378 -- Set up contexts for the formula
1379 for i in g_inputs.first..g_inputs.last loop
1380 --
1381
1382 if g_inputs(i).name = 'DATE_EARNED' then
1383 hr_utility.trace('setting date earned '||p_date_earned);
1384 g_inputs(i).value := fnd_date.date_to_canonical(p_date_earned);
1385 elsif g_inputs(i).name = 'ASSIGNMENT_ID' then
1386 g_inputs(i).value := p_assignment_id;
1387 elsif g_inputs(i).name = 'SOURCE_TEXT' then
1388 hr_utility.trace('setting source text '||p_process_type);
1389 g_inputs(i).value := p_process_type;
1390 elsif g_inputs(i).name = 'PROCESS_TYPE' then
1391 g_inputs(i).value := p_process_type;
1392 elsif g_inputs(i).name = 'TAX_UNIT_ID' then
1393 g_inputs(i).value := p_tax_unit_id;
1394 elsif g_inputs(i).name = 'ASSIGNMENT_ACTION_ID' then
1395 g_inputs(i).value := p_asg_action_id;
1396 elsif g_inputs(i).name = 'BUSINESS_GROUP_ID' then
1397 g_inputs(i).value := p_business_group_id;
1398 elsif g_inputs(i).name = 'PAYROLL_ACTION_ID' then
1399 g_inputs(i).value := p_payroll_action_id;
1400 elsif g_inputs(i).name = 'PAYROLL_ID' then
1401 g_inputs(i).value := p_payroll_id;
1402 elsif g_inputs(i).name = 'BALANCE_DATE' then
1403 g_inputs(i).value := fnd_date.date_to_canonical(p_balance_date);
1404 else
1405 hr_utility.set_location('ERROR value = '||g_inputs(i).name ,7);
1406 end if;
1407 --
1408 hr_utility.trace('g_inputs(i).name : '||g_inputs(i).name);
1409 hr_utility.trace('g_inputs(i).value : '||g_inputs(i).value);
1410 end loop;
1411 --
1412 -- Run the formula
1413 --
1414 hr_utility.set_location('Prior to execute the formula',8);
1415 ff_exec.run_formula (g_inputs ,
1416 g_outputs );
1417 --
1418 hr_utility.set_location('End run formula',9);
1419 --
1420 for l_out_cnt in g_outputs.first..g_outputs.last loop
1421 -- only store the output of L_CPF_ADD_CALC_YEAR , ignoe others
1422 if g_outputs(l_out_cnt).name = 'L_CPF_ADD_CALC_YEAR' then
1423 l_cpf_add_YTD := g_outputs(l_out_cnt).value;
1424 hr_utility.trace('l_cpf_add_YTD:'|| g_outputs(l_out_cnt).value);
1425 end if;
1426 --
1427 hr_utility.trace('Outputs:'||g_outputs(l_out_cnt).name);
1428 hr_utility.trace('Outputs(values):'||g_outputs(l_out_cnt).value);
1429 end loop;
1430 --
1431 return l_cpf_add_YTD;
1432 --
1433 End calc_cpf_add_YTD;
1434 /* End of Function */
1435
1436 /* Returns whether the SG_STAT is called from the REPORT or PAYROLL Run*/
1437 Function get_SG_STAT_CALLED_FROM return varchar2
1438 is
1439 begin
1440 return g_sgstat_called_from;
1441 end;
1442
1443 /* In the before report trigger of PAYSGCPF the global g_sgstat_called_from
1444 is set to REPORT*/
1445
1446 procedure set_SG_STAT_CALLED_FROM (p_running in varchar2)
1447 is
1448 begin
1449 g_sgstat_called_from := p_running;
1450 end;
1451
1452 /* Populates the pl/sql table with assignment id and difference of CPF paid (values of the balances)
1453 and calculated CPF from SG_STAT with SAEOY*/
1454
1455 procedure populate_cpf_table (p_person_id in number,
1456 p_cpf_diff number ) is
1457 l_person_id binary_integer;
1458 begin
1459 l_person_id:= p_person_id;
1460 cpf_inputs_t(l_person_id).person_id:= p_person_id;
1461 cpf_inputs_t(l_person_id).cpf_diff := p_cpf_diff;
1462 end;
1463
1464 /* If the assignment exists in the cpf pl/sql table (populated by populate_cpf_table)
1465 ,return 1 else 0. Used in the where clause of the report query*/
1466
1467 function get_assignment_from_cpf_table(p_person_id in number) return number is
1468 l_person_id binary_integer;
1469 begin
1470 l_person_id:= p_person_id;
1471 if cpf_inputs_t.exists(l_person_id) then
1472 if (cpf_inputs_t(l_person_id).person_id= p_person_id) then
1473 return (1);
1474 end if;
1475 end if;
1476 return (0);
1477 end;
1478
1479 /* Get the overpaid value for the assignment passed from the pl/sql table populated by
1480 populate_cpf_table above*/
1481 function get_cpf_difference(p_person_id in number) return number is
1482 l_cpf_diff number;
1483 l_person_id binary_integer;
1484 begin
1485 l_cpf_diff := 0;
1486 l_person_id:= p_person_id;
1487 if cpf_inputs_t.exists(l_person_id) then
1488 if (cpf_inputs_t(l_person_id).person_id= p_person_id) then
1489 l_cpf_diff := cpf_inputs_t(l_person_id).cpf_diff;
1490 end if;
1491 end if;
1492 return l_cpf_diff;
1493 end;
1494
1495 /* Return last date of the year (stored in g_year_end_date_for_cpf_report)
1496 , used in the SG_STAT*/
1497
1498 function GET_YEAR_END_DATE return date is
1499 begin
1500 return g_year_end_date_for_cpf_report;
1501 end;
1502
1503 /* set the g_year_end_date_for_cpf_report as the last date of the year*/
1504 procedure set_year_end_date(p_year_end_date in date) is
1505 begin
1506 g_year_end_date_for_cpf_report := p_year_end_date;
1507 end;
1508
1509 begin
1510
1511 g_sgstat_called_from := 'PAYROLL';
1512
1513 end pay_sg_deductions;
[Home] [Help]