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