1 PACKAGE BODY pay_nl_lss_functions AS
2 /* $Header: pynllssf.pkb 120.3.12020000.2 2012/12/20 13:46:44 mkuppuch ship $ */
3
4
5 --
6 -- ----------------------------------------------------------------------------
7 -- |---------------------< Get_Day_of_Week >--------------------------|
8 -- ----------------------------------------------------------------------------
9 --
10 FUNCTION Get_Day_of_Week(p_date DATE) RETURN NUMBER IS
11 l_reference_date date:=to_date('01/01/1984','DD/MM/YYYY');
12 v_index number;
13 BEGIN
14 hr_utility.set_location('Inside Get_Day_of_Week Function', 1110);
15 v_index:=abs(p_date - l_reference_date);
16 v_index:=mod(v_index,7);
17 hr_utility.set_location('v_index: '||v_index, 1120);
18 RETURN v_index+1;
19 END Get_Day_of_Week;
20
21 -- ----------------------------------------------------------------------------
22 -- |-------------------------< Get_Wage_Days >-------------------------------|
23 -- ----------------------------------------------------------------------------
24 --
25 FUNCTION Get_Wage_Days(p_start_date DATE,
26 p_end_date DATE) RETURN NUMBER IS
27 v_st_date date := p_start_date;
28 v_en_date date := p_end_date;
29 v_beg_of_week date;
30 v_end_of_week date;
31 v_days number := 0;
32 BEGIN
33
34 hr_utility.set_location('Inside Get_Wage_Days Function', 1130);
35 hr_utility.set_location('p_start_date: '||p_start_date, 1140);
36 hr_utility.set_location('p_end_date: '||p_end_date, 1150);
37
38 IF p_start_date > p_end_date THEN
39 RETURN v_days;
40 END IF;
41 --Determine the Beginning of Week Date for Start Date
42 --and End of Week Date for End Date
43 v_beg_of_week := v_st_date - (get_day_of_week(v_st_date)-1);
44 v_end_of_week := v_en_date;
45
46 hr_utility.set_location('v_beg_of_week: '||v_beg_of_week, 1160);
47
48
49 IF get_day_of_week(v_en_date) NOT IN('1') THEN
50 v_end_of_week := v_en_date + (7- get_day_of_week(v_en_date)+1);
51 END IF;
52
53 hr_utility.set_location('v_end_of_week: '||v_end_of_week, 1170);
54
55 --Calculate the Total Week Days @ of 5 per week
56 v_days := ((v_end_of_week-v_beg_of_week)/7)*5;
57
58 --Adjust the Total Week Days by subtracting
59 --No of Days before the Start Date
60 IF (v_st_date > (v_beg_of_week+1)) THEN
61 v_days := v_days - (v_st_date - (v_beg_of_week+1)) ;
62 END IF;
63 IF v_end_of_week <> v_en_date THEN
64 v_end_of_week := v_end_of_week -2;
65 ELSE
66 IF v_st_date = v_en_date THEN
67 v_days := 0;
68 END IF;
69 END IF;
70 hr_utility.set_location('v_days: '||v_days, 1180);
71
72 --Adjust the Total Week Days by subtracting
73 --No of Days After the End Date
74 IF (v_end_of_week - v_en_date) >= 0 THEN
75 v_days := v_days - (v_end_of_week - v_en_date) ;
76 END IF;
77 RETURN (v_days);
78 hr_utility.set_location('Final v_days: '||v_days, 1190);
79 END Get_Wage_Days;
80
81 -- ----------------------------------------------------------------------------
82 -- |--------------------------< Get_Prorate_Amount >--------------------------------|
83 -- ----------------------------------------------------------------------------
84 --
85 Function Get_Prorate_Amount(p_assignment_id IN NUMBER
86 ,p_business_group IN NUMBER
87 ,p_application_date IN DATE
88 ,p_period_start_date IN DATE
89 ,p_period_end_date IN DATE
90 ,p_pay_periods_per_year IN NUMBER
91 ,p_amount IN OUT NOCOPY NUMBER)
92 RETURN NUMBER IS
93
94 l_error_flag number := 1; /* 0 means success, 1 means error */
95 l_prorated_amount number := 0;
96 l_proration_start_date date;
97 l_proration_end_date date;
98 l_assignment_start_date date;
99 l_assignment_end_date date;
100 l_total_wage_days_per_period number := 0;
101 /* Number of wage days in a pay period */
102 l_prorated_wage_days_temp number:= 0;
103 l_prorated_wage_days number:= 0;
104 /* Number of prorated wage days */
105 l_count number := 0;
106
107 /* Cursor for getting start and end dates for an assignment. Returns multiple rows if the assignment is upadted
108 is suspended for multiple times in the given pay_period. */
109 CURSOR csr_asg_dates (p_other_assignment_id NUMBER) IS
110 SELECT asg.effective_start_date asg_start_date
111 ,asg.effective_end_date asg_end_date
112 FROM PER_ALL_ASSIGNMENTS_F asg
113 ,PER_ASSIGNMENT_STATUS_TYPES past
114 WHERE asg.assignment_id = p_other_assignment_id
115 AND past.per_system_status = 'ACTIVE_ASSIGN'
116 AND asg.assignment_status_type_id = past.assignment_status_type_id
117 -- AND asg.business_group_id = p_business_group
118 AND asg.effective_start_date <= p_period_end_date
119 AND NVL(asg.effective_end_date,p_period_end_date) >= p_period_start_date;
120
121
122 /* Cursor to access Global vlaues for Wage days in a pay_period depanding on Payroll Type. */
123 CURSOR csr_globals_nl_average_days IS
124 SELECT global_name g_name, fnd_number.canonical_to_number(global_value) g_val FROM FF_GLOBALS_F
125 WHERE legislation_code = 'NL' AND
126 global_name in ('NL_AVERAGE_DAYS_WEEKLY', 'NL_AVERAGE_DAYS_4WEEKLY', 'NL_AVERAGE_DAYS_MONTHLY',
127 'NL_AVERAGE_DAYS_QUARTERLY') AND
128 (EFFECTIVE_START_DATE <= p_application_date and EFFECTIVE_END_DATE >= p_application_date);
129
130 BEGIN
131
132 hr_utility.set_location('Inside Get_Prorate_Amount Function ' , 1200);
133
134 /* This loop checks for Assignment Suspension of a given assignment in a given pay period.
135 l_count = 1 means no suspension. */
136 FOR csr_asg_dates_rec in csr_asg_dates(p_assignment_id) LOOP
137 l_assignment_start_date := csr_asg_dates_rec.asg_start_date;
138 l_assignment_end_date := csr_asg_dates_rec.asg_end_date;
139 l_proration_start_date := Greatest(p_period_start_date, l_assignment_start_date);
140 l_proration_end_date := Least(p_period_end_date, l_assignment_end_date);
141 l_prorated_wage_days_temp := Get_Wage_Days(l_proration_start_date, l_proration_end_date);
142 l_prorated_wage_days := l_prorated_wage_days + l_prorated_wage_days_temp;
143
144 hr_utility.set_location('l_assignment_start_date: '||l_assignment_start_date, 1210);
145 hr_utility.set_location('l_assignment_end_date: '||l_assignment_end_date, 1220);
146 hr_utility.set_location('l_proration_start_date: '||l_proration_start_date, 1230);
147 hr_utility.set_location('l_proration_end_date: '||l_proration_end_date, 1240);
148 hr_utility.set_location('l_prorated_wage_days_temp: '||l_prorated_wage_days_temp , 1250);
149 hr_utility.set_location('l_prorated_wage_days: '||l_prorated_wage_days, 1260);
150
151 l_count := l_count+1;
152
153 END LOOP;
154
155 hr_utility.set_location('l_count: '||l_count, 1270);
156 hr_utility.set_location('l_prorated_wage_days outside loop: '||l_prorated_wage_days, 1280);
157
158 /* This loop fetches number of Wage Days in a pay period using Globals */
159 FOR crs_rec_globals in csr_globals_nl_average_days
160 LOOP
161 IF (p_pay_periods_per_year = 52 and crs_rec_globals.g_name = 'NL_AVERAGE_DAYS_WEEKLY') THEN
162 l_total_wage_days_per_period := crs_rec_globals.g_val;
163 END IF;
164 IF (p_pay_periods_per_year = 13 and crs_rec_globals.g_name = 'NL_AVERAGE_DAYS_4WEEKLY') THEN
165 l_total_wage_days_per_period := crs_rec_globals.g_val;
166 END IF;
167 IF (p_pay_periods_per_year = 12 and crs_rec_globals.g_name = 'NL_AVERAGE_DAYS_MONTHLY') THEN
168 l_total_wage_days_per_period := crs_rec_globals.g_val;
169 END IF;
170 IF (p_pay_periods_per_year = 4 and crs_rec_globals.g_name = 'NL_AVERAGE_DAYS_QUARTERLY') THEN
171 l_total_wage_days_per_period := crs_rec_globals.g_val;
172 END IF;
173 END LOOP;
174
175 hr_utility.set_location('l_total_wage_days_per_period: '||l_total_wage_days_per_period, 1290);
176
177 /* Checks that Prorated wage days should not cross maximum number of wage days for that pay period.
178 Also checks for the pay periods in Feb month, if an assignment is active right from the beginning
179 till end of the Feb pay period, then also prorate wage days may come less than the desired value,
180 so it makes them equal to maximum number of wage days for that pay period */
181
182 IF (l_prorated_wage_days > l_total_wage_days_per_period) OR (l_prorated_wage_days < l_total_wage_days_per_period AND p_period_start_date = l_proration_start_date AND p_period_end_date = l_proration_end_date AND l_count = 1) THEN
183 l_prorated_wage_days := l_total_wage_days_per_period; /* Proration not Required */
184 END IF;
185
186 hr_utility.set_location('l_prorated_wage_days: '||l_prorated_wage_days, 1300);
187 /* Total Prorated Amount */
188 p_amount := (p_amount) *
189 (l_prorated_wage_days/l_total_wage_days_per_period);
190 p_amount := round(p_amount,2);
191 hr_utility.set_location('p_amount: '||p_amount, 1310);
192
193 l_error_flag := 0; /* Success Status */
194 RETURN l_error_flag;
195 EXCEPTION
196 WHEN OTHERS THEN
197 l_error_flag := 1; /* Reports Failure */
198 RETURN l_error_flag;
199
200 END Get_Prorate_Amount ;
201
202 -- ----------------------------------------------------------------------------
203 -- |---------------------------< Get_Prev_Yr_Sal >----------------------------|
204 -- ----------------------------------------------------------------------------
205 FUNCTION Get_Previous_Year_Sal (p_assignment_id IN NUMBER,
206 p_business_group IN NUMBER,
207 p_date_earned IN DATE,
208 p_previous_er_column_6 IN NUMBER,
209 p_prev_year_sal OUT NOCOPY NUMBER,
210 p_error_msg OUT NOCOPY VARCHAR2,
211 p_opt_num_in IN NUMBER DEFAULT 0,
212 p_opt_date_in IN DATE DEFAULT NULL)
213 RETURN NUMBER
214 IS
215 l_end_date_last_yr DATE;
216 l_end_date_asg DATE;
217 l_end_month VARCHAR2(20);
218 l_end_yr NUMBER := 0;
219 l_for_last_yr NUMBER := 0;
220 l_balance_amount NUMBER := 0;
221 l_wage_days NUMBER := 0;
222 l_bal_total_amt NUMBER := 0;
223 l_min_date DATE;
224 l_start_date_last_yr DATE;
225 l_element_type_id NUMBER := 0;
226 l_input_value_name VARCHAR2(80);
227 l_asg_ytd VARCHAR2(100) := 'Assignment Year To Date';
228 p_bal_total_amt NUMBER := 0;
229 p_person_id NUMBER := 0;
230
231
232 TYPE dim_tbl IS TABLE OF pay_defined_balances.DEFINED_BALANCE_ID%TYPE
233 INDEX BY BINARY_INTEGER;
234 L_DEF_BAL_TYPE_ID dim_tbl;
235 CURSOR csr_get_def_bal_type_id
236 (c_balance_name VARCHAR2
237 ,c_dimension_name VARCHAR2) IS
238 SELECT defined_balance_id
239 FROM pay_defined_balances pdb,
240 pay_balance_types pbt,
241 pay_balance_dimensions pbd
242 WHERE pbt.balance_name = c_balance_name
243 AND pbd.legislation_code = 'NL'
244 AND pbd.DIMENSION_NAME = c_dimension_name
245 AND pdb.balance_type_id = pbt.balance_type_id
246 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
247
248 CURSOR csr_min_date (assg_id number) IS
249 select min(effective_start_date) from
250 per_all_assignments_f
251 where assignment_id = assg_id;
252
253 --Active Assignments
254 --
255 -- Assignments that are active as of 31st of the last year.
256 --
257 CURSOR csr_active_asg(l_date IN DATE) IS
258 SELECT assignment_id
259 FROM per_all_assignments_f
260 WHERE person_id = p_person_id
261 AND assignment_status_type_id IN (SELECT assignment_status_type_id
262 FROM per_assignment_status_types
263 WHERE per_system_status = 'ACTIVE_ASSIGN'
264 AND active_flag = 'Y')
265 AND l_date BETWEEN effective_start_date and effective_end_date
266 AND payroll_id IS NOT NULL
267 AND NOT EXISTS (SELECT 1 from per_all_assignments_f
268 WHERE person_id = p_person_id
269 AND assignment_status_type_id IN (SELECT assignment_status_type_id
270 FROM per_assignment_status_types
271 WHERE per_system_status = 'TERM_ASSIGN'
272 AND active_flag = 'Y')
273 AND l_date BETWEEN effective_start_date and effective_end_date);
274
275 --
276 -- Assignments that have a Last Standard Process Date
277 -- Greater than the termination Date
278 --
279 -- Secondary assignments and LSP date equal to the
280 -- termination date ( cases where the assignment is end dated as within the year
281 -- AND there are no assignment records after that
282 CURSOR csr_term_asg(l_start_date IN DATE
283 ,l_end_date IN DATE) IS
284 SELECT effective_start_date - 1 term_dt
285 ,assignment_id
286 FROM per_all_assignments_f
287 WHERE person_id = p_person_id
288 AND assignment_status_type_id IN (SELECT assignment_status_type_id
289 FROM per_assignment_status_types
290 WHERE per_system_status = 'TERM_ASSIGN'
291 AND active_flag = 'Y')
292 AND effective_start_date BETWEEN l_start_date and l_end_date
293 AND payroll_id IS NOT NULL
294 UNION
295 SELECT effective_end_date ,
296 assignment_id
297 FROM per_all_assignments_f asg
298 WHERE person_id = p_person_id
299 AND effective_end_date BETWEEN l_start_date and l_end_date
300 AND payroll_id IS NOT NULL
301 AND NOT EXISTS( SELECT 1
302 FROM per_all_assignments_f asg1
303 WHERE person_id = p_person_id
304 AND effective_start_date = asg.effective_end_date + 1
305 AND asg.assignment_id = asg1.assignment_id );
306
307 CURSOR csr_get_person IS
308 SELECT person_id
309 FROM per_all_assignments_f
310 WHERE assignment_id = p_assignment_id
311 AND business_group_id = p_business_group;
312
313 CURSOR csr_cur_yr_col6 IS
314 SELECT pet.element_type_id, piv.name
315 FROM pay_element_types_f pet, pay_input_values_f piv
316 WHERE pet.element_name = 'Life Savings Scheme General Information'
317 AND pet.element_type_id = piv.element_type_id
318 AND piv.legislation_code = 'NL'
319 AND upper(piv.name) = 'CURRENT YEAR COLUMN 6';
320
321 BEGIN
322
323 hr_utility.set_location('Inside Get_Previous_Year_Sal Function ', 1320);
324
325 -- Getting Person_id for the Assignment
326 OPEN csr_get_person;
327 FETCH csr_get_person INTO p_person_id;
328 CLOSE csr_get_person;
329
330 OPEN csr_cur_yr_col6;
331 FETCH csr_cur_yr_col6 INTO l_element_type_id,l_input_value_name;
332 CLOSE csr_cur_yr_col6;
333
334 hr_utility.set_location('p_person_id: '||p_person_id, 1330);
335 hr_utility.set_location('l_element_type_id: '||l_element_type_id, 1340);
336 hr_utility.set_location('l_input_value_name: '||l_input_value_name, 1350);
337
338 -- Calculation for last year's dates from current year's date.
339 -- Get previous year by subtracting 1 from current year
340 -- and then get first and last date of that year */
341
342 l_for_last_yr := to_number(to_char(p_date_earned,'YYYY')) - 1;
343 l_end_date_last_yr := to_date('31-12-'||to_char(l_for_last_yr),'DD-MM-YYYY');
344 l_start_date_last_yr := to_date('01-01-'||to_char(l_for_last_yr),'DD-MM-YYYY');
345
346 hr_utility.set_location('l_for_last_yr: '||l_for_last_yr, 1360);
347 hr_utility.set_location('l_end_date_last_yr: '||l_end_date_last_yr, 1370);
348 hr_utility.set_location('l_start_date_last_yr: '||l_start_date_last_yr, 1380);
349
350 -- Getting Defined_Balance_Type_Id of 12 balances required to calculate
351 -- Remunuration Report Col6 Value
352
353 OPEN csr_get_def_bal_type_id
354 ('Wage In Money Standard Tax SI',l_asg_ytd);
355 FETCH csr_get_def_bal_type_id
356 INTO l_def_bal_type_id(1);
357 CLOSE csr_get_def_bal_type_id;
358
359 OPEN csr_get_def_bal_type_id
360 ('Wage In Money Special Tax SI',l_asg_ytd);
361 FETCH csr_get_def_bal_type_id
362 INTO l_def_bal_type_id(2);
363 CLOSE csr_get_def_bal_type_id;
364
365 OPEN csr_get_def_bal_type_id
366 ('Retro Wage In Money Standard Tax SI',l_asg_ytd);
367 FETCH csr_get_def_bal_type_id
368 INTO l_def_bal_type_id(3);
369 CLOSE csr_get_def_bal_type_id;
370
371 OPEN csr_get_def_bal_type_id
372 ('Retro Wage In Money Special Tax SI',l_asg_ytd);
373 FETCH csr_get_def_bal_type_id
374 INTO l_def_bal_type_id(4);
375 CLOSE csr_get_def_bal_type_id;
376
377 OPEN csr_get_def_bal_type_id
378 ('Wage In Kind Standard Tax SI',l_asg_ytd);
379 FETCH csr_get_def_bal_type_id
380 INTO l_def_bal_type_id(5);
381 CLOSE csr_get_def_bal_type_id;
382
383 OPEN csr_get_def_bal_type_id
384 ('Wage In Kind Special Tax SI',l_asg_ytd);
385 FETCH csr_get_def_bal_type_id
386 INTO l_def_bal_type_id(6);
387 CLOSE csr_get_def_bal_type_id;
388
389 OPEN csr_get_def_bal_type_id
390 ('Retro Wage In Kind Standard Tax SI',l_asg_ytd);
391 FETCH csr_get_def_bal_type_id
392 INTO l_def_bal_type_id(7);
393 CLOSE csr_get_def_bal_type_id;
394
395 OPEN csr_get_def_bal_type_id
396 ('Retro Wage In Kind Special Tax SI',l_asg_ytd);
397 FETCH csr_get_def_bal_type_id
398 INTO l_def_bal_type_id(8);
399 CLOSE csr_get_def_bal_type_id;
400
401 OPEN csr_get_def_bal_type_id
402 ('Tips and Fund Payments Standard Tax SI',l_asg_ytd);
403 FETCH csr_get_def_bal_type_id
404 INTO l_def_bal_type_id(9);
405 CLOSE csr_get_def_bal_type_id;
406
407 OPEN csr_get_def_bal_type_id
408 ('Tips and Fund Payments Special Tax SI',l_asg_ytd);
409 FETCH csr_get_def_bal_type_id
410 INTO l_def_bal_type_id(10);
411 CLOSE csr_get_def_bal_type_id;
412
413 OPEN csr_get_def_bal_type_id
414 ('Retro Tips and Fund Payments Standard Tax SI',l_asg_ytd);
415 FETCH csr_get_def_bal_type_id
416 INTO l_def_bal_type_id(11);
417 CLOSE csr_get_def_bal_type_id;
418
419 OPEN csr_get_def_bal_type_id
420 ('Retro Tips and Fund Payments Special Tax SI',l_asg_ytd);
421 FETCH csr_get_def_bal_type_id
422 INTO l_def_bal_type_id(12);
423 CLOSE csr_get_def_bal_type_id;
424
425 --Get the values of ASG_YTD vlaues of
426 --Active assignments of each balance out
427 --of 12 balances and sum them up for all the
428 --assignments of a person in last year
429
430 For def_bal_count in 1..12 LOOP
431 IF l_def_bal_type_id(def_bal_count) IS NOT NULL THEN
432 --Checking for Active Assignments
433 FOR act_assg in csr_active_asg(l_end_date_last_yr)
434 LOOP
435
436 l_balance_amount := Pay_Balance_Pkg.Get_Value
437 (p_defined_balance_id => l_def_bal_type_id(def_bal_count),
438 p_assignment_id => act_assg.assignment_id,
439 p_virtual_date => l_end_date_last_yr );
440 -- Derive Annual Value
441 OPEN csr_min_date(act_assg.assignment_id);
442 FETCH csr_min_date into l_min_date;
443 IF (l_min_date > l_start_date_last_yr) THEN
444 l_wage_days := pay_nl_lss_functions.Get_Wage_Days
445 (l_min_date, l_end_date_last_yr);
446 -- l_balance_amount := l_balance_amount * (261/l_wage_days);
447 END IF;
448 CLOSE csr_min_date;
449 hr_utility.set_location('l_balance_amount: '||l_balance_amount, 1390);
450 l_bal_total_amt := l_bal_total_amt + NVL(l_balance_amount,0);
451
452 END LOOP; -- assg
453 END IF;
454 END LOOP; -- def_bal_count
455 hr_utility.set_location('l_bal_total_amt: '||l_bal_total_amt, 1400);
456
457 -- Checking for Terminated Assignments
458 For def_bal_count in 1..12 LOOP
459 IF l_def_bal_type_id(def_bal_count) IS NOT NULL THEN
460 FOR term_assg in csr_term_asg(l_start_date_last_yr,l_end_date_last_yr)
461 LOOP
462
463 l_balance_amount := Pay_Balance_Pkg.Get_Value
464 (p_defined_balance_id => l_def_bal_type_id(def_bal_count),
465 p_assignment_id => term_assg.assignment_id,
466 p_virtual_date => term_assg.term_dt);
467 -- Reverse Proration
468 OPEN csr_min_date(term_assg.assignment_id);
469 FETCH csr_min_date into l_min_date;
470 IF (l_min_date > l_start_date_last_yr) THEN
471 l_wage_days := pay_nl_lss_functions.Get_Wage_Days
472 (l_min_date, l_end_date_last_yr);
473 -- l_balance_amount := l_balance_amount * (261/l_wage_days);
474 END IF;
475 CLOSE csr_min_date;
476 hr_utility.set_location('l_balance_amount: '||l_balance_amount, 1410);
477 l_bal_total_amt := l_bal_total_amt + NVL(l_balance_amount,0);
478 END LOOP; -- assg
479 END IF;
480 END LOOP; -- def_bal_count
481
482 hr_utility.set_location('l_bal_total_amt before calling ABP function: '||l_bal_total_amt, 1420);
483 -- Final Amount
484
485 l_bal_total_amt := l_bal_total_amt +
486 pqp_pension_functions.get_abp_entry_value(p_business_group
487 ,l_end_date_last_yr
488 ,p_assignment_id
489 ,l_element_type_id
490 ,l_input_value_name);
491
492 hr_utility.set_location('l_bal_total_amt after calling ABP function: '||l_bal_total_amt, 1430);
493 hr_utility.set_location('p_previous_er_column_6: '||p_previous_er_column_6, 1440);
494
495 p_bal_total_amt := round((l_bal_total_amt + p_previous_er_column_6),2);
496
497 p_prev_year_sal := p_bal_total_amt;
498 hr_utility.set_location('p_prev_year_sal: '||p_prev_year_sal, 1450);
499
500 Return 0;
501
502 EXCEPTION
503 WHEN OTHERS THEN
504 p_error_msg :='SQL-ERRM :'||SQLERRM;
505 RETURN 1;
506 End Get_Previous_Year_Sal;
507
508 -- ----------------------------------------------------------------------------
509 -- |--------------------------< Get_Or_Life_Savings_Basis >-------------------|
510 -- ----------------------------------------------------------------------------
511 Function Get_Or_Life_Savings_Basis
512 (p_assignment_id IN NUMBER,
513 p_business_group IN NUMBER,
514 p_date_earned IN DATE,
515 p_override_basis OUT NOCOPY NUMBER,
516 p_error_message OUT NOCOPY VARCHAR)
517 RETURN NUMBER IS
518
519 l_element_type_id NUMBER;
520
521 CURSOR c_ele_cur IS
522 SELECT element_type_id
523 FROM pay_element_types_f
524 WHERE element_name = 'Life Savings Scheme General Information'
525 AND legislation_code = 'NL'
526 AND p_date_earned BETWEEN effective_start_date AND
527 effective_end_date;
528 BEGIN
529
530 hr_utility.set_location('Inside Get_Or_Life_Savings_Basis function ', 1460);
531
532 -- Funtion to derive the override savings basis
533 -- for Life Savings. This is applicable only
534 -- to the basis calculation method Pre Defined Balances
535 -- The value can be overridden via the general information
536 -- element only on the 1st of Jan of each year
537 -- or the hire date.
538
539 --
540 -- Derive the element_type_id for the life savings gen info
541 -- element
542 --
543 OPEN c_ele_cur;
544 FETCH c_ele_cur INTO l_element_type_id;
545 IF c_ele_cur%NOTFOUND THEN
546 CLOSE c_ele_cur;
547 RETURN -1;
548 ELSE
549 CLOSE c_ele_cur;
550 END IF;
551
552 hr_utility.set_location('l_element_type_id: '||l_element_type_id, 1470);
553 --
554 -- Derive the value of the input as of 1 Jan or the hire date
555 --
556 p_override_basis := pqp_pension_functions.get_abp_entry_value
557 (p_business_group_id => p_business_group
558 ,p_date_earned => p_date_earned
559 ,p_assignment_id => p_assignment_id
560 ,p_element_type_id => l_element_type_id
561 ,p_input_value_name => 'Override Annual Savings Basis');
562
563 hr_utility.set_location('p_override_basis: '||p_override_basis, 1480);
564
565 IF p_override_basis > 0 THEN
566 -- Indicator that the override has been done and that
567 -- the overriden value is > 0
568 p_error_message := 'Annual Live Savings Basis Overridden';
569 RETURN 0;
570 ELSE
571 -- Indicator that the override has not been done
572 RETURN -1;
573 END IF;
574
575 END Get_Or_Life_Savings_Basis;
576
577 -- ----------------------------------------------------------------------------
578 -- |--------------------------< Get_LCLD_Limit >-------------------|
579 -- ----------------------------------------------------------------------------
580
581 FUNCTION Get_LCLD_Limit ( p_date_earned IN DATE,
582 p_assignment_id IN NUMBER,
583 p_num_saved_yrs IN Number,
584 p_lcld_limit IN OUT NOCOPY NUMBER,
585 p_error_msg IN OUT NOCOPY VARCHAR2)
586 RETURN NUMBER IS
587
588 l_current_year number := fnd_number.canonical_to_number(to_char(p_date_earned,'YYYY'));
589 l_num_saved_yrs number := p_num_saved_yrs;
590 l_lcld_limit number := 0;
591 l_gbl_value number := 0;
592 l_gbl_value_temp ff_globals_f.GLOBAL_VALUE%TYPE;
593 l_balance_amount NUMBER := 0;
594 l_lss_ytd number := 0;
595 l_date date;
596 l_per_ytd VARCHAR2(100) := 'Person Year to Date';
597 l_def_bal_type_id pay_defined_balances.DEFINED_BALANCE_ID%TYPE;
598 l_assignment_exists number := 0; --0 means it doesnot exist, 1 means it exists
599 l_assignment_id_temp number;
600
601 Cursor c_asg_exists_year(p_year NUMBER, p_assg_id NUMBER) IS
602 select unique(assignment_id) from per_all_assignments_f paaf, PER_ASSIGNMENT_STATUS_TYPES past
603 where paaf.assignment_id = p_assg_id
604 AND past.per_system_status = 'ACTIVE_ASSIGN'
605 AND paaf.assignment_status_type_id = past.assignment_status_type_id
606 and (to_char(effective_start_date,'YYYY') = p_year
607 OR to_char(effective_end_date,'YYYY') = p_year);
608
609
610 Cursor c_gbl_value(p_year number) is
611 select GLOBAL_VALUE from ff_globals_f
612 where global_name = 'PAY_NL_MAX_LCLD_PER_YEAR'
613 and legislation_code = 'NL'
614 and p_year between fnd_number.canonical_to_number(to_char(EFFECTIVE_START_DATE,'YYYY'))
615 and fnd_number.canonical_to_number(to_char(EFFECTIVE_END_DATE,'YYYY'));
616
617 CURSOR csr_get_def_bal_type_id
618 (c_balance_name VARCHAR2
619 ,c_dimension_name VARCHAR2) IS
620 SELECT defined_balance_id
621 FROM pay_defined_balances pdb,
622 pay_balance_types pbt,
623 pay_balance_dimensions pbd
624 WHERE pbt.balance_name = c_balance_name
625 AND pbd.legislation_code = 'NL'
626 AND pbd.DIMENSION_NAME = c_dimension_name
627 AND pdb.balance_type_id = pbt.balance_type_id
628 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
629
630 begin
631
632 --hr_utility.trace_on(NULL,'NJ');
633 hr_utility.set_location('Inside LCLD Function', 100);
634 hr_utility.set_location('p_date_earned: '||p_date_earned, 200);
635 hr_utility.set_location('p_assignment_id: '||p_assignment_id, 300);
636 hr_utility.set_location('p_num_saved_yrs: '||p_num_saved_yrs, 400);
637 hr_utility.set_location('p_lcld_limit: '||p_lcld_limit, 500);
638 hr_utility.set_location('l_current_year: '||l_current_year, 900);
639
640 -- Fetch the value of global in the present year and
641 -- multiply it with number of years saved.
642 open c_gbl_value(l_current_year);
643 fetch c_gbl_value into l_gbl_value_temp;
644 close c_gbl_value;
645
646 hr_utility.set_location('l_gbl_value_temp: '||l_gbl_value_temp, 1200);
647
648 l_gbl_value := fnd_number.canonical_to_number(l_gbl_value_temp);
649
650 hr_utility.set_location('l_gbl_value: '||l_gbl_value, 800);
651
652 l_lcld_limit := p_num_saved_yrs * l_gbl_value;
653
654 -- Round off the value to 2 decimal places.
655 p_lcld_limit := round(l_lcld_limit,2);
656
657 hr_utility.set_location('p_lcld_limit: '||p_lcld_limit, 1100);
658 return 0; /* 0 means success, and l_lcld_limit has to be an out variable.*/
659
660 hr_utility.trace_off();
661
662 Exception
663 WHEN OTHERS THEN
664 p_error_msg :='SQL-ERRM :'||SQLERRM;
665 RETURN -1;
666
667 end Get_LCLD_Limit;
668
669 END pay_nl_lss_functions;
[Home] [Help]