[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_FF_FUNCTIONS_PKG
Source
1 package body pay_kr_ff_functions_pkg as
2 /* $Header: pykrfffc.pkb 120.51.12020000.12 2013/01/25 08:42:05 scireddy ship $ */
3 --
4 -- Legislative Parameters Global Variables.
5 --
6 type legislative_parameter is record(
7 parameter_name varchar2(255),
8 parameter_value varchar2(255));
9 type legislative_parameter_tbl is table of legislative_parameter index by binary_integer;
10 g_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
11 g_legislative_parameter_tbl legislative_parameter_tbl;
12 g_effective_date date ; -- Bug 4674552
13 --
14 -- NI Component Global Variables.
15 --
16 type ni is record(
17 national_identifier per_people_f.national_identifier%TYPE,
18 sex varchar2(1),
19 date_of_birth date);
20 g_ni ni;
21 --------------------------------------------------------------------------------
22 function get_legislative_parameter(
23 p_payroll_action_id in number,
24 p_parameter_name in varchar2,
25 p_default_value in varchar2,
26 p_flash_cache in varchar2) return varchar2
27 --------------------------------------------------------------------------------
28 is
29 l_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE;
30 l_str pay_payroll_actions.legislative_parameters%TYPE;
31 l_pos number;
32 l_end_pos number;
33 l_parameter_name varchar2(255);
34 l_parameter_value varchar2(255);
35 l_found boolean := FALSE;
36 l_index number;
37 --
38 cursor csr_legislative_parameters is
39 select legislative_parameters
40 from pay_payroll_actions
41 where payroll_action_id = p_payroll_action_id;
42 begin
43 if p_flash_cache = 'Y'
44 or g_payroll_action_id is null
45 or g_payroll_action_id <> p_payroll_action_id then
46 g_payroll_action_id := p_payroll_action_id;
47 g_legislative_parameter_tbl.delete;
48 --
49 open csr_legislative_parameters;
50 fetch csr_legislative_parameters into l_legislative_parameters;
51 if csr_legislative_parameters%NOTFOUND then
52 close csr_legislative_parameters;
53 raise no_data_found;
54 end if;
55 close csr_legislative_parameters;
56 --
57 l_legislative_parameters := trim(l_legislative_parameters);
58 while l_legislative_parameters is not null loop
59 l_end_pos := instr(l_legislative_parameters, ' ');
60 if l_end_pos > 0 then
61 l_str := substr(l_legislative_parameters, 1, l_end_pos - 1);
62 l_legislative_parameters := trim(substr(l_legislative_parameters, l_end_pos + 1));
63 else
64 l_str := l_legislative_parameters;
65 l_legislative_parameters := null;
66 end if;
67 --
68 l_pos := instr(l_str, '=');
69 if l_pos > 1 and l_pos < length(l_str) then
70 l_parameter_name := substr(l_str, 1, l_pos - 1);
71 l_parameter_value := substr(l_str, l_pos + 1);
72 --
73 -- If the same parameter exists, then override.
74 --
75 l_index := g_legislative_parameter_tbl.count;
76 l_found := false;
77 for i in 1..l_index loop
78 if l_parameter_name = g_legislative_parameter_tbl(i).parameter_name then
79 g_legislative_parameter_tbl(i).parameter_value := l_parameter_value;
80 l_found := true;
81 exit;
82 end if;
83 end loop;
84 --
85 -- If not exist, create new element.
86 --
87 if not l_found then
88 g_legislative_parameter_tbl(l_index + 1).parameter_name := l_parameter_name;
89 g_legislative_parameter_tbl(l_index + 1).parameter_value := l_parameter_value;
90 end if;
91 end if;
92 end loop;
93 end if;
94 --
95 -- Derive legislative parameter from global value
96 --
97 l_found := false;
98 for i in 1..g_legislative_parameter_tbl.count loop
99 if g_legislative_parameter_tbl(i).parameter_name = p_parameter_name then
100 l_parameter_value := g_legislative_parameter_tbl(i).parameter_value;
101 l_found := true;
102 exit;
103 end if;
104 end loop;
105 --
106 -- If not found, default_value is applied.
107 --
108 if not l_found then
109 l_parameter_value := p_default_value;
110 end if;
111 --
112 return l_parameter_value;
113 end get_legislative_parameter;
114 --------------------------------------------------------------------------------
115 function set_message_name(
116 p_application_short_name in varchar2,
117 p_message_name in varchar2) return number
118 --------------------------------------------------------------------------------
119 is
120 begin
121 fnd_message.set_name(p_application_short_name, p_message_name);
122 return 0;
123 exception
124 when others then
125 return -1;
126 end set_message_name;
127 --------------------------------------------------------------------------------
128 function set_message_token(
129 p_token_name in varchar2,
130 p_token_value in varchar2) return number
131 --------------------------------------------------------------------------------
132 is
133 begin
134 fnd_message.set_token(p_token_name, p_token_value);
135 return 0;
136 exception
137 when others then
138 return -1;
139 end set_message_token;
140 --------------------------------------------------------------------------------
141 function get_message return varchar2
142 --------------------------------------------------------------------------------
143 is
144 begin
145 return substrb(fnd_message.get, 1, 240);
146 end get_message;
147 --------------------------------------------------------------------------------
148 procedure ni_component(
149 p_national_identifier in varchar2,
150 p_sex out NOCOPY varchar2,
151 p_date_of_birth out NOCOPY date)
152 --------------------------------------------------------------------------------
153 is
154 l_ni varchar2(14);
155 l_dob_cent varchar2(2);
156 l_dob_yymmdd varchar2(6);
157 l_sex_code number;
158 l_effective_date_cent varchar2(2);
159 l_effective_date varchar2(8);
160 begin
161 if p_national_identifier is null then
162 fnd_message.set_name('PER', 'PER_KR_NI_NUMBER_NULL');
163 fnd_message.raise_error;
164 end if;
165 --
166 if g_ni.national_identifier = p_national_identifier then
167 p_sex := g_ni.sex;
168 p_date_of_birth := g_ni.date_of_birth;
169 else
170 begin
171 l_ni := hr_ni_chk_pkg.chk_nat_id_format(p_national_identifier, 'DDDDDD-DDDDDDD');
172 if l_ni = '0' then
173 raise no_data_found;
174 end if;
175 --
176 l_dob_yymmdd := substr(l_ni, 1, 6);
177 l_sex_code := to_number(substr(l_ni, 8, 1));
178 --
179 if l_sex_code in (1, 2, 5, 6) then -- Bug 9327240
180 l_dob_cent := '19';
181 elsif l_sex_code in (3, 4, 7, 8) then -- Bug 9327240
182 l_dob_cent := '20';
183 elsif l_sex_code in (0, 9) then -- Bug 9327240
184 l_dob_cent := '18';
185 else
186 --
187 -- We "GUESS" date of birth in case of sex between 5 to 0 using effective date.
188 -- In Korea, there's no exact rule to derive accurate date of birth
189 -- from NI number whose sex is between 5 to 0.
190 --
191 -- Bug 4674552: execute query only if effective date is not cached
192 if g_effective_date is null then
193 select effective_date
194 into g_effective_date
195 from fnd_sessions
196 where session_id = userenv('sessionid');
197 --
198 end if ;
199 l_effective_date := to_char(g_effective_date, 'YYYYMMDD') ;
200 -- End of 4674552
201 -- If effective_date(YYMMDD) >= date_of_birth(YYMMDD), use same century as effective_date.
202 -- else use previous century as effective_date.
203 --
204 l_effective_date_cent := substr(l_effective_date, 1, 2);
205 if substr(l_effective_date, 3) >= l_dob_yymmdd then
206 l_dob_cent := l_effective_date_cent;
207 else
208 l_dob_cent := to_char(to_number(l_effective_date_cent) - 1, 'FM09');
209 end if;
210 end if;
211 --
212 p_date_of_birth := to_date(l_dob_cent || l_dob_yymmdd, 'YYYYMMDD');
213 --
214 if mod(l_sex_code, 2) = 1 then
215 p_sex := 'M';
216 else
217 p_sex := 'F';
218 end if;
219 --
220 -- Set current NI information to global variable as cache.
221 --
222 g_ni.national_identifier := p_national_identifier;
223 g_ni.sex := p_sex;
224 g_ni.date_of_birth := p_date_of_birth;
225 exception
226 when others then
227 fnd_message.set_name('PER', 'PER_KR_INV_NI_NUMBER');
228 fnd_message.set_token('NI_NUMBER', p_national_identifier);
229 fnd_message.raise_error;
230 end;
231 end if;
232 end ni_component;
233 /*
234 --------------------------------------------------------------------------------
235 procedure ni_component(
236 p_national_identifier in varchar2,
237 p_person_name in varchar2,
238 p_sex out varchar2,
239 p_date_of_birth out date)
240 --------------------------------------------------------------------------------
241 is
242 begin
243 if p_national_identifier is null then
244 fnd_message.set_name('PER', 'PER_KR_CON_PER_NI_NUMBER_NULL');
245 fnd_message.set_token('PERSON_NAME', p_person_name);
246 fnd_message.raise_error;
247 end if;
248 --
249 begin
250 ni_component(
251 p_national_identifier => p_national_identifier,
252 p_sex => p_sex,
253 p_date_of_birth => p_date_of_birth);
254 exception
255 when others then
256 fnd_message.set_name('PER', 'PER_KR_CON_PER_INV_NI_NUMBER');
257 fnd_message.set_token('NI_NUMBER', p_national_identifier);
258 fnd_message.set_token('PERSON_NAME', p_person_name);
259 fnd_message.raise_error;
260 end;
261 end ni_component;
262 */
263 --------------------------------------------------------------------------------
264 function ni_sex(p_national_identifier in varchar2) return varchar2
265 --------------------------------------------------------------------------------
266 is
267 l_sex varchar2(1);
268 l_date_of_birth date;
269 begin
270 ni_component(
271 p_national_identifier => p_national_identifier,
272 p_sex => l_sex,
273 p_date_of_birth => l_date_of_birth);
274 --
275 return l_sex;
276 end ni_sex;
277 --------------------------------------------------------------------------------
278 function ni_date_of_birth(p_national_identifier in varchar2) return date
279 --------------------------------------------------------------------------------
280 is
281 l_sex varchar2(1);
282 l_date_of_birth date;
283 begin
284 ni_component(
285 p_national_identifier => p_national_identifier,
286 p_sex => l_sex,
287 p_date_of_birth => l_date_of_birth);
288 --
289 return l_date_of_birth;
290 end ni_date_of_birth;
291 --------------------------------------------------------------------------
292 -- Bug 3172960
293 function ni_nationality(p_national_identifier in varchar2) return varchar2
294 --------------------------------------------------------------------------
295 is
296 l_nationality varchar2(1);
297 begin
298 if to_number(substr(p_national_identifier,8,1)) >= 5 and to_number(substr(p_national_identifier,8,1)) <= 8 then
299 l_nationality := 'F';
300 else
301 l_nationality := 'K';
302 end if;
303 return l_nationality;
304 end ni_nationality;
305 ---------------------------------------------------------------------
306 -- Bug 3172960
307 function ni_nationality(p_assignment_id in number,
308 p_effective_date in date) return varchar2
309 ---------------------------------------------------------------------
310 is
311 cursor csr_ni is
312 select
313 hr_ni_chk_pkg.chk_nat_id_format(per.national_identifier, 'DDDDDD-DDDDDDD') NI
314 from per_people_f per,
315 per_assignments_f asg
316 where asg.assignment_id = p_assignment_id
317 and per.person_id = asg.person_id
318 and p_effective_date between per.effective_start_date and per.effective_end_date
319 and p_effective_date between asg.effective_start_date and asg.effective_end_date;
320
321 l_ni varchar2(20);
322 l_nationality varchar2(1);
323 begin
324 open csr_ni;
325 fetch csr_ni into l_ni;
326 close csr_ni;
327
328 l_nationality := pay_kr_ff_functions_pkg.ni_nationality(l_ni);
329 return l_nationality;
330 end;
331 ----------------------------------------------------------------------
332 -- Returns End Of Year Age
333 ----------------------------------------------------------------------
334 function eoy_age(
335 p_date_of_birth in date,
336 p_effective_date in date) return number
337 is
338 begin
339 return to_number(to_char(p_effective_date, 'YYYY')) - to_number(to_char(p_date_of_birth, 'YYYY'));
340 end eoy_age;
341 ----------------------------------------------------------------------
342 -- Dependent Spouse Tax Exemption
343 ----------------------------------------------------------------------
344 function dpnt_spouse_flag(p_contact_type in varchar2,
345 p_kr_cont_type in varchar2) -- Bug 7661820
346 return varchar2
347 is
348 begin
349 -- Bug 7661820: Added check for the Korean Conatct Type
350 if (p_kr_cont_type = '3' or p_contact_type = 'S') then
351 return 'Y';
352 else
353 return 'N';
354 end if;
355 end dpnt_spouse_flag;
356 ----------------------------------------------------------------------
357 -- Aged Dependent Tax Exemption
358 ----------------------------------------------------------------------
359 function aged_dpnt_flag(
360 p_contact_type in varchar2,
361 p_kr_cont_type in varchar2, -- Bug 7661820
362 p_national_identifier in varchar2,
363 p_effective_date in date) return varchar2
364 is
365 l_sex varchar2(1);
366 l_date_of_birth date;
367 l_eoy_age number;
368 l_flag varchar2(1);
369 l_var varchar2(1); -- Bug 7661820
370 l_male_age_limit number default 0; -- Bug 7676136
371 l_female_age_limit number default 0; -- Bug 7676136
372 begin
373 l_flag := 'N';
374 l_male_age_limit := get_globalvalue('KR_YEA_MALE_AGE_LIM',p_effective_date); -- Bug 7676136
375 l_female_age_limit := get_globalvalue('KR_YEA_FEMALE_AGE_LIM',p_effective_date); -- Bug 7676136
376
377 -- Bug 7661820: Added check for the Korean Conatct Type
378 if (p_kr_cont_type = '3' or p_contact_type = 'S') then
379 l_var := 'Y';
380 else
381 l_var := 'N';
382 end if;
383
384 if l_var = 'N' then
385 ni_component(
386 p_national_identifier => p_national_identifier,
387 p_sex => l_sex,
388 p_date_of_birth => l_date_of_birth);
389 l_eoy_age := eoy_age(l_date_of_birth, p_effective_date);
390 --
391 -- Bug 7676136
392 if (l_sex = 'M' and l_eoy_age >= l_male_age_limit) or (l_sex = 'F' and l_eoy_age >= l_female_age_limit) then
393 l_flag := 'Y';
394 end if;
395 end if;
396 --
397 return l_flag;
398 end aged_dpnt_flag;
399 ----------------------------------------------------------------------
400 -- Adult Dependent Tax Exemption
401 ----------------------------------------------------------------------
402 function adult_dpnt_flag(
403 p_contact_type in varchar2,
404 p_kr_cont_type in varchar2, -- Bug 7661820
405 p_national_identifier in varchar2,
406 p_effective_date in date,
407 p_disabled_flag in varchar2,
408 p_age_exception_flag in varchar2) return varchar2
409 is
410 l_sex varchar2(1);
411 l_date_of_birth date;
412 l_eoy_age number;
413 l_flag varchar2(1);
414 l_var varchar2(1); -- Bug 7661820
415 l_male_age_limit number default 0; -- Bug 7676136
416 l_female_age_limit number default 0; -- Bug 7676136
417 begin
418 l_flag := 'N';
419 l_male_age_limit := get_globalvalue('KR_YEA_MALE_AGE_LIM',p_effective_date); -- Bug 7676136
420 l_female_age_limit := get_globalvalue('KR_YEA_FEMALE_AGE_LIM',p_effective_date); -- Bug 7676136
421
422 -- Bug 7661820: Added check for the Korean Conatct Type
423 if (p_kr_cont_type = '3' or p_contact_type = 'S') then
424 l_var := 'Y';
425 else
426 l_var := 'N';
427 end if;
428
429 if l_var = 'N' then
430 ni_component(
431 p_national_identifier => p_national_identifier,
432 p_sex => l_sex,
433 p_date_of_birth => l_date_of_birth);
434 l_eoy_age := eoy_age(l_date_of_birth, p_effective_date);
435 --
436 -- Bug 7676136
437 if ((l_sex = 'M' and l_eoy_age > 20 and l_eoy_age < l_male_age_limit)
438 or (l_sex = 'F' and l_eoy_age > 20 and l_eoy_age < l_female_age_limit))
439 and (p_disabled_flag ='Y' or p_age_exception_flag='Y' ) then
440 -- Bug 3073424 added the above disabled and age exception check
441 l_flag := 'Y';
442 end if;
443 end if;
444 --
445 return l_flag;
446 end adult_dpnt_flag;
447 ----------------------------------------------------------------------
448 -- Underaged Dependent Tax Exemption
449 ----------------------------------------------------------------------
450 function underaged_dpnt_flag(
451 p_contact_type in varchar2,
452 p_kr_cont_type in varchar2, -- Bug 7661820
453 p_national_identifier in varchar2,
454 p_effective_date in date) return varchar2
455 is
456 l_sex varchar2(1);
457 l_date_of_birth date;
458 l_eoy_age number;
459 l_flag varchar2(1);
460 l_var varchar2(1); -- Bug 7661820
461 begin
462 l_flag := 'N';
463
464 -- Bug 7661820: Added check for the Korean Conatct Type
465 if (p_kr_cont_type = '3' or p_contact_type = 'S') then
466 l_var := 'Y';
467 else
468 l_var := 'N';
469 end if;
470
471 if l_var = 'N' then
472 ni_component(
473 p_national_identifier => p_national_identifier,
474 p_sex => l_sex,
475 p_date_of_birth => l_date_of_birth);
476 l_eoy_age := eoy_age(l_date_of_birth, p_effective_date);
477 --
478 if l_eoy_age <= 20 then
479 l_flag := 'Y';
480 -- Bug 9737699
481 if (p_kr_cont_type = '8' or p_contact_type = 'O') then
482 if l_eoy_age > 18 then
483 l_flag := 'N';
484 end if;
485 end if;
486 --
487 end if;
488 end if;
489 --
490 return l_flag;
491 end underaged_dpnt_flag;
492 ----------------------------------------------------------------------
493 -- Aged Tax Exemption
494 ----------------------------------------------------------------------
495 function aged_flag(
496 p_national_identifier in varchar2,
497 p_effective_date in date) return varchar2
498 is
499 l_sex varchar2(1);
500 l_date_of_birth date;
501 l_eoy_age number;
502 l_flag varchar2(1);
503 begin
504 l_flag := 'N';
505 ni_component(
506 p_national_identifier => p_national_identifier,
507 p_sex => l_sex,
508 p_date_of_birth => l_date_of_birth);
509 l_eoy_age := eoy_age(l_date_of_birth, p_effective_date);
510 --
511 if l_eoy_age >= 65 then
512 l_flag := 'Y';
513 end if;
514 --
515 return l_flag;
516 end aged_flag;
517 ----------------------------------------------------------------------
518 -- Bug 3172960
519 -- Super Aged Tax Exemption
520 ----------------------------------------------------------------------
521 function super_aged_flag(
522 p_national_identifier in varchar2,
523 p_effective_date in date) return varchar2
524 is
525 l_sex varchar2(1);
526 l_date_of_birth date;
527 l_eoy_age number;
528 l_flag varchar2(1);
529 begin
530 l_flag := 'N';
531 ni_component(
532 p_national_identifier => p_national_identifier,
533 p_sex => l_sex,
534 p_date_of_birth => l_date_of_birth);
535 l_eoy_age := eoy_age(l_date_of_birth, p_effective_date);
536 --
537 if l_eoy_age >= 70 then
538 l_flag := 'Y';
539 end if;
540 --
541 return l_flag;
542 end super_aged_flag;
543 ----------------------------------------------------------------------
544 -- Disabled Tax Exemption
545 ----------------------------------------------------------------------
546 function disabled_flag(
547 p_person_id in number,
548 p_effective_date in date) return varchar2
549 is
550 l_flag varchar2(1);
551
552 -- Bug# 2657588
553 -- Cursor modified to return 'N' if dis_information3 is 'N' for any one of the record.
554
555 cursor csr_disabled(p_person_id number)
556 is
557 select nvl(dis_information3, 'Y')
558 from per_disabilities_f
559 where person_id = p_person_id
560 and dis_information_category = 'KR'
561 and p_effective_date between effective_start_date and effective_end_date
562 order by dis_information3 ;
563 begin
564 l_flag := 'N';
565 open csr_disabled(p_person_id);
566 fetch csr_disabled into l_flag;
567 if csr_disabled%NOTFOUND then
568 l_flag := 'N';
569 end if;
570 close csr_disabled;
571 --
572 return l_flag;
573 end disabled_flag;
574 ----------------------------------------------------------------------
575 -- Child Tax Exemption
576 ----------------------------------------------------------------------
577 function child_flag(
578 p_kr_contact_type in varchar2,
579 p_contact_type in varchar2,
580 p_national_identifier in varchar2,
581 p_effective_date in date) return varchar2
582 is
583 l_sex varchar2(1);
584 l_date_of_birth date;
585 l_eoy_age number;
586 l_flag varchar2(1);
587 begin
588 l_flag := 'N';
589 ni_component(
590 p_national_identifier => p_national_identifier,
591 p_sex => l_sex,
592 p_date_of_birth => l_date_of_birth);
593 l_eoy_age := eoy_age(l_date_of_birth, p_effective_date);
594 --
595 if l_eoy_age <= 6 and (p_kr_contact_type in ('4','7','8') or p_contact_type in ('A','C','T','O'))then
596 l_flag := 'Y';
597 end if;
598 --
599 return l_flag;
600 end child_flag;
601 ----------------------------------------------------------------------
602 -- Single Parent Tax Exemption
603 ----------------------------------------------------------------------
604 function single_parent_child_flag(
605 p_kr_contact_type in varchar2,
606 p_contact_type in varchar2,
607 p_national_identifier in varchar2,
608 p_effective_date in date) return varchar2
609 is
610 Cursor csr is
611 SELECT lookup_code
612 FROM hr_leg_lookups
613 WHERE lookup_type = 'CONTACT'
614 AND meaning LIKE '%Child%'
615 AND lookup_code = p_contact_type;
616
617 l_sex varchar2(1);
618 l_date_of_birth date;
619 l_eoy_age number;
620 l_flag varchar2(1);
621 l_dummy hr_leg_lookups.lookup_code%type;
622 begin
623 l_flag := 'N';
624 ni_component(
625 p_national_identifier => p_national_identifier,
626 p_sex => l_sex,
627 p_date_of_birth => l_date_of_birth);
628
629 l_eoy_age := eoy_age(l_date_of_birth, p_effective_date);
630 --
631 open csr;
632 fetch csr into l_dummy;
633 close csr;
634
635 if (l_dummy is not null) then
636 if l_eoy_age <= 20 and (p_kr_contact_type in ('4','7'))then
637 l_flag := 'Y';
638 elsif l_eoy_age <= 18 and (p_kr_contact_type in ('8')) then
639 l_flag := 'Y' ;
640 else
641 l_flag := 'N' ;
642 end if;
643 else
644 l_flag := 'N';
645 end if;
646 --
647 return l_flag;
648 end single_parent_child_flag;
649 --------------------------------------------------------------------------------
650 -- National Pension Exception Reason (Formula Function)
651 -- Skip processing of National Pension Prem element, if National Pension
652 -- exception rules are entered against the employee
653 -- Bug 2815425
654 --------------------------------------------------------------------------------
655 function get_np_exception_flag (
656 p_date_earned IN DATE
657 ,p_business_group_id IN NUMBER
658 ,p_assignment_id IN NUMBER ) RETURN VARCHAR2 IS
659 --
660 l_exception_flag per_people_extra_info.pei_information1%TYPE;
661 --
662 cursor csr_exception_flag is
663 select pei.pei_information1
664 from per_people_f pap
665 ,per_people_extra_info pei
666 ,per_assignments_f paa
667 where paa.assignment_id = p_assignment_id
668 and paa.business_group_id = p_business_group_id
669 and paa.person_id = pap.person_id
670 and pap.person_id = pei.person_id
671 and pei.information_type = 'PER_KR_NP_EXCEPTIONS'
672 and p_date_earned between paa.effective_start_date and paa.effective_end_date
673 and p_date_earned between pap.effective_start_date and pap.effective_end_date
674 and p_date_earned between fnd_date.canonical_to_date(pei.pei_information2)
675 and fnd_date.canonical_to_date(pei.pei_information3);
676 --
677 begin
678 l_exception_flag :='N';
679 open csr_exception_flag;
680 fetch csr_exception_flag into l_exception_flag;
681 close csr_exception_flag;
682 --
683 if l_exception_flag = 'N' then
684 return 'N';
685 else
686 return 'Y';
687 end if;
688 --
689 end get_np_exception_flag;
690 --------------------------------------------------------------------------------
691 /* Bug 6784288 */
692 function addtl_child_flag(
693 p_contact_type in varchar2,
694 p_national_identifier in varchar2,
695 p_cont_information4 in varchar2, -- Bug 7615517
696 p_cont_information11 in varchar2, -- Bug 7615517
697 p_cont_information15 in varchar2, -- Bug 7661820
698 p_effective_date in date) return varchar2
699 is
700 Cursor csr is
701 SELECT lookup_code
702 FROM hr_leg_lookups
703 WHERE lookup_type = 'CONTACT'
704 AND meaning LIKE '%Child%'
705 AND lookup_code = p_contact_type;
706
707 l_flag varchar2(2);
708 l_dummy hr_leg_lookups.lookup_code%type;
709 l_sex varchar2(1);
710 l_date_of_birth date;
711 l_eoy_age number;
712 begin
713 l_flag := 'N';
714 -- Bug 7615517
715 ni_component(
716 p_national_identifier => p_national_identifier,
717 p_sex => l_sex,
718 p_date_of_birth => l_date_of_birth);
719 l_eoy_age := eoy_age(l_date_of_birth, p_effective_date);
720 --
721 if l_eoy_age <= 20 then
722 l_flag := 'Y';
723 end if;
724 --
725
726 open csr;
727 fetch csr into l_dummy;
728 close csr;
729
730 -- Bug 7615517, Bug 7661820, Bug 9213683
731 if p_cont_information11 is not null then
732 if (p_cont_information11 = '4'
733 and (l_flag = 'Y' or nvl(p_cont_information4,'N') = 'Y')
734 and (nvl(p_cont_information15,'N') = 'N')) then
735 return 'Y';
736 else
737 return 'N';
738 end if;
739 else
740 if (l_dummy is not null
741 and (l_flag = 'Y' or nvl(p_cont_information4,'N') = 'Y')
742 and (nvl(p_cont_information15,'N') = 'N')) then
743 return 'Y';
744 else
745 return 'N';
746 end if;
747 end if;
748 --
749 end addtl_child_flag;
750 --------------------------------------------------------------------------------
751 -- Bug 3172960
752 function get_dependent_info(
753 p_assignment_id in number,
754 p_date_earned in date,
755 p_non_resident_flag in varchar2,
756 p_dpnt_spouse_flag out NOCOPY varchar2,
757 p_num_of_aged_dpnts out NOCOPY number,
758 p_num_of_adult_dpnts out NOCOPY number,
759 p_num_of_underaged_dpnts out NOCOPY number,
760 p_num_of_dpnts out NOCOPY number,
761 p_num_of_ageds out NOCOPY number,
762 p_num_of_disableds out NOCOPY number,
763 p_female_ee_flag out NOCOPY varchar2,
764 p_num_of_children out NOCOPY number) return number
765 --------------------------------------------------------------------------------
766 is
767 l_return number(10);
768 l_num_of_super_ageds number(10);
769 begin
770 l_return := get_dependent_info(
771 p_assignment_id,
772 p_date_earned,
773 p_non_resident_flag,
774 p_dpnt_spouse_flag,
775 p_num_of_aged_dpnts,
776 p_num_of_adult_dpnts,
777 p_num_of_underaged_dpnts,
778 p_num_of_dpnts,
779 p_num_of_ageds,
780 p_num_of_disableds,
781 p_female_ee_flag,
782 p_num_of_children,
783 l_num_of_super_ageds);
784
785 return l_return;
786
787 end get_dependent_info;
788 --------------------------------------------------------------------------------
789 /* Bug 6784288 */
790 function get_dependent_info(
791 p_assignment_id in number,
792 p_date_earned in date,
793 p_non_resident_flag in varchar2,
794 p_dpnt_spouse_flag out NOCOPY varchar2,
795 p_num_of_aged_dpnts out NOCOPY number,
796 p_num_of_adult_dpnts out NOCOPY number,
797 p_num_of_underaged_dpnts out NOCOPY number,
798 p_num_of_dpnts out NOCOPY number,
799 p_num_of_ageds out NOCOPY number,
800 p_num_of_disableds out NOCOPY number,
801 p_female_ee_flag out NOCOPY varchar2,
802 p_num_of_children out NOCOPY number,
803 p_num_of_super_ageds out NOCOPY number) return number
804 --------------------------------------------------------------------------------
805 is
806 l_return number(10);
807 l_num_of_addtl_child number(10); /* Bug 6784288 */
808 begin
809 l_return := get_dependent_info(
810 p_assignment_id,
811 p_date_earned,
812 p_non_resident_flag,
813 p_dpnt_spouse_flag,
814 p_num_of_aged_dpnts,
815 p_num_of_adult_dpnts,
816 p_num_of_underaged_dpnts,
817 p_num_of_dpnts,
818 p_num_of_ageds,
819 p_num_of_disableds,
820 p_female_ee_flag,
821 p_num_of_children,
822 p_num_of_super_ageds,
823 l_num_of_addtl_child); /* Bug 6784288 */
824
825 return l_return;
826
827 end get_dependent_info;
828 ------------------------------------------------------------------------------
829 -- Bug 9737699 : 2010 YEA Tax Receipt Updates
830 ------------------------------------------------------------------------------
831 function get_dependent_info(
832 p_assignment_id in number,
833 p_date_earned in date,
834 p_non_resident_flag in varchar2,
835 p_dpnt_spouse_flag out NOCOPY varchar2,
836 p_num_of_aged_dpnts out NOCOPY number,
837 p_num_of_adult_dpnts out NOCOPY number,
838 p_num_of_underaged_dpnts out NOCOPY number,
839 p_num_of_dpnts out NOCOPY number,
840 p_num_of_ageds out NOCOPY number,
841 p_num_of_disableds out NOCOPY number,
842 p_female_ee_flag out NOCOPY varchar2,
843 p_num_of_children out NOCOPY number,
844 p_num_of_super_ageds out NOCOPY number,
845 p_num_of_new_born_adopted out NOCOPY number,
846 p_num_of_addtl_child out NOCOPY number) return number is
847
848 l_return number(10);
849 l_house_holder_flag varchar2(1);
850 l_tot_num_dpnts number(10);
851 l_single_parent_flag varchar2(1);
852
853 begin
854 l_return := get_dependent_info(
855 p_assignment_id,
856 p_date_earned,
857 p_non_resident_flag,
858 p_dpnt_spouse_flag,
859 p_num_of_aged_dpnts,
860 p_num_of_adult_dpnts,
861 p_num_of_underaged_dpnts,
862 p_num_of_dpnts,
863 p_num_of_ageds,
864 p_num_of_disableds,
865 p_female_ee_flag,
866 p_num_of_children,
867 p_num_of_super_ageds,
868 p_num_of_new_born_adopted,
869 p_num_of_addtl_child,
870 l_single_parent_flag, /* 16010775 */
871 l_house_holder_flag,
872 l_tot_num_dpnts );
873
874 return l_return;
875
876 end get_dependent_info;
877
878 ------------------------------------------------------------------------------
879 -- Bug 16010775 : 2013 Stat Updates
880 ------------------------------------------------------------------------------
881 function get_dependent_info(
882 p_assignment_id in number,
883 p_date_earned in date,
884 p_non_resident_flag in varchar2,
885 p_dpnt_spouse_flag out NOCOPY varchar2,
886 p_num_of_aged_dpnts out NOCOPY number,
887 p_num_of_adult_dpnts out NOCOPY number,
888 p_num_of_underaged_dpnts out NOCOPY number,
889 p_num_of_dpnts out NOCOPY number,
890 p_num_of_ageds out NOCOPY number,
891 p_num_of_disableds out NOCOPY number,
892 p_female_ee_flag out NOCOPY varchar2,
893 p_num_of_children out NOCOPY number,
894 p_num_of_super_ageds out NOCOPY number,
895 p_num_of_new_born_adopted out NOCOPY number,
896 p_num_of_addtl_child out NOCOPY number,
897 p_single_parent_flag out NOCOPY varchar2) return number is
898
899 l_return number(10);
900 l_house_holder_flag varchar2(1);
901 l_tot_num_dpnts number(10);
902 begin
903 l_return := get_dependent_info(
904 p_assignment_id,
905 p_date_earned,
906 p_non_resident_flag,
907 p_dpnt_spouse_flag,
908 p_num_of_aged_dpnts,
909 p_num_of_adult_dpnts,
910 p_num_of_underaged_dpnts,
911 p_num_of_dpnts,
912 p_num_of_ageds,
913 p_num_of_disableds,
914 p_female_ee_flag,
915 p_num_of_children,
916 p_num_of_super_ageds,
917 p_num_of_new_born_adopted,
918 p_num_of_addtl_child,
919 p_single_parent_flag, /* 16010775 */
920 l_house_holder_flag,
921 l_tot_num_dpnts);
922
923 return l_return;
924
925 end get_dependent_info;
926 --------------------------------------------------------------------------------
927 /* Bug 6705170 : Function get_dependent_info() has been overloaded
928 to fetch the New Born/Adopted Child count */
929 --------------------------------------------------------------------------------
930 function get_dependent_info(
931 p_assignment_id in number,
932 p_date_earned in date,
933 p_non_resident_flag in varchar2,
934 p_dpnt_spouse_flag out NOCOPY varchar2,
935 p_num_of_aged_dpnts out NOCOPY number,
936 p_num_of_adult_dpnts out NOCOPY number,
937 p_num_of_underaged_dpnts out NOCOPY number,
938 p_num_of_dpnts out NOCOPY number,
939 p_num_of_ageds out NOCOPY number,
940 p_num_of_disableds out NOCOPY number,
941 p_female_ee_flag out NOCOPY varchar2,
942 p_num_of_children out NOCOPY number,
943 p_num_of_super_ageds out NOCOPY number,
944 p_num_of_new_born_adopted out NOCOPY number,
945 p_num_of_addtl_child out NOCOPY number,
946 p_single_parent_flag out NOCOPY varchar2,
947 p_house_holder_flag out NOCOPY varchar2,
948 p_tot_num_dpnts out NOCOPY number) return number /* Bug 6784288 */
949 --------------------------------------------------------------------------------
950 is
951 l_return number(10);
952 type l_flag_tbl is table of varchar2(1) index by binary_integer;
953 l_new_born_adopted_flag_tbl l_flag_tbl;
954 l_single_parent_flag varchar2(1);
955 --
956 cursor csr_new_born is
957 select
958 nvl(cei.cei_information13, 'N')
959 from per_people_f per,
960 per_contact_relationships ctr,
961 per_assignments_f asg,
962 per_contact_extra_info_f cei
963 where asg.assignment_id = p_assignment_id
964 and p_date_earned
965 between asg.effective_start_date and asg.effective_end_date
966 and ctr.person_id = asg.person_id
967 and ctr.cont_information_category = 'KR'
968 and ctr.cont_information1 = 'Y'
969 and ((ctr.cont_information9 ='D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(trunc(add_months(date_end,12),'YYYY')-1,p_date_earned))
970 or (nvl(ctr.cont_information9,'XXX') <>'D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(date_end, p_date_earned))
971 )
972 and per.person_id = ctr.contact_person_id
973 and cei.contact_relationship_id = ctr.contact_relationship_id
974 and cei.information_type = 'KR_DPNT_EXPENSE_INFO'
975 and to_char(cei.effective_start_date, 'YYYY') = to_char(p_date_earned,'YYYY')
976 and (
977 (
978 p_date_earned
979 between per.effective_start_date and per.effective_end_date
980 )
981 or
982 (
983 per.start_date = per.effective_start_date
984 and not exists(
985 select null
986 from per_people_f per2
987 where per2.person_id = per.person_id
988 and p_date_earned
989 between per2.effective_start_date and per2.effective_end_date)
990 )
991 );
992 begin
993 p_num_of_new_born_adopted := 0;
994 g_effective_date := p_date_earned;
995 --
996 -- Dependents
997 --
998 if p_non_resident_flag = 'N' then
999 open csr_new_born;
1000 fetch csr_new_born bulk collect into l_new_born_adopted_flag_tbl;
1001 close csr_new_born;
1002 --
1003 for i in 1..l_new_born_adopted_flag_tbl.count loop
1004 if l_new_born_adopted_flag_tbl(i) = 'Y' then
1005 p_num_of_new_born_adopted := p_num_of_new_born_adopted + 1;
1006 end if;
1007 end loop;
1008 end if;
1009 --
1010 l_return := get_dependent_info(
1011 p_assignment_id,
1012 p_date_earned,
1013 p_non_resident_flag,
1014 p_dpnt_spouse_flag,
1015 p_num_of_aged_dpnts,
1016 p_num_of_adult_dpnts,
1017 p_num_of_underaged_dpnts,
1018 p_num_of_dpnts,
1019 p_num_of_ageds,
1020 p_num_of_disableds,
1021 p_female_ee_flag,
1022 p_num_of_children,
1023 p_num_of_super_ageds,
1024 p_num_of_addtl_child, /* Bug 6784288 */
1025 p_single_parent_flag, /* 16010775 */
1026 p_house_holder_flag,
1027 p_tot_num_dpnts ); /* Bug 9737699 */
1028 return l_return;
1029 end get_dependent_info;
1030 ------------------------------------------------------------------------------
1031 -- Bug 9737699 : 2010 YEA Tax Receipt Updates
1032 ------------------------------------------------------------------------------
1033 function get_dependent_info(
1034 p_assignment_id in number,
1035 p_date_earned in date,
1036 p_non_resident_flag in varchar2,
1037 p_dpnt_spouse_flag out NOCOPY varchar2,
1038 p_num_of_aged_dpnts out NOCOPY number,
1039 p_num_of_adult_dpnts out NOCOPY number,
1040 p_num_of_underaged_dpnts out NOCOPY number,
1041 p_num_of_dpnts out NOCOPY number,
1042 p_num_of_ageds out NOCOPY number,
1043 p_num_of_disableds out NOCOPY number,
1044 p_female_ee_flag out NOCOPY varchar2,
1045 p_num_of_children out NOCOPY number,
1046 p_num_of_super_ageds out NOCOPY number,
1047 p_num_of_addtl_child out NOCOPY number) return number
1048 is
1049 l_return number(10);
1050 l_house_holder_flag varchar2(1);
1051 l_tot_num_dpnts number(10);
1052 l_single_parent_flag varchar2(1);
1053 begin
1054 l_return :=
1055 get_dependent_info(
1056 p_assignment_id,
1057 p_date_earned,
1058 p_non_resident_flag,
1059 p_dpnt_spouse_flag,
1060 p_num_of_aged_dpnts,
1061 p_num_of_adult_dpnts,
1062 p_num_of_underaged_dpnts,
1063 p_num_of_dpnts,
1064 p_num_of_ageds,
1065 p_num_of_disableds,
1066 p_female_ee_flag,
1067 p_num_of_children,
1068 p_num_of_super_ageds,
1069 p_num_of_addtl_child,
1070 l_single_parent_flag, /* 16010775 */
1071 l_house_holder_flag,
1072 l_tot_num_dpnts );
1073 return l_return;
1074 end get_dependent_info;
1075
1076 --------------------------------------------------------------------------------
1077 function get_dependent_info(
1078 p_assignment_id in number,
1079 p_date_earned in date,
1080 p_non_resident_flag in varchar2,
1081 p_dpnt_spouse_flag out NOCOPY varchar2,
1082 p_num_of_aged_dpnts out NOCOPY number,
1083 p_num_of_adult_dpnts out NOCOPY number,
1084 p_num_of_underaged_dpnts out NOCOPY number,
1085 p_num_of_dpnts out NOCOPY number,
1086 p_num_of_ageds out NOCOPY number,
1087 p_num_of_disableds out NOCOPY number,
1088 p_female_ee_flag out NOCOPY varchar2,
1089 p_num_of_children out NOCOPY number,
1090 p_num_of_super_ageds out NOCOPY number,
1091 p_num_of_addtl_child out NOCOPY number,
1092 p_single_parent_flag out NOCOPY varchar2,
1093 p_house_holder_flag out NOCOPY varchar2,
1094 p_tot_num_dpnts out NOCOPY number) return number /* Bug 6784288 */
1095 --------------------------------------------------------------------------------
1096 is
1097 type t_flag_tbl is table of varchar2(1) index by binary_integer;
1098 l_dpnt_spouse_flag_tbl t_flag_tbl;
1099 l_aged_dpnt_flag_tbl t_flag_tbl;
1100 l_adult_dpnt_flag_tbl t_flag_tbl;
1101 l_underaged_dpnt_flag_tbl t_flag_tbl;
1102 l_aged_flag_tbl t_flag_tbl;
1103 l_super_aged_flag_tbl t_flag_tbl;
1104 l_disabled_flag_tbl t_flag_tbl;
1105 l_child_flag_tbl t_flag_tbl;
1106 l_addtl_child_flag_tbl t_flag_tbl; /* Bug 6784288 */
1107 l_single_parent_child_flag_tbl t_flag_tbl;
1108 l_sex varchar2(1);
1109 l_date_of_birth date;
1110 --
1111 -- Bug 7661820: Added check for the Korean Conatct Type
1112 cursor csr_dpnt is
1113 select
1114 decode(ctr.cont_information2, 'Y', dpnt_spouse_flag(ctr.contact_type, ctr.cont_information11), 'N') DPNT_SPOUSE_FLAG,
1115 decode(ctr.cont_information2, 'Y', aged_dpnt_flag(ctr.contact_type,ctr.cont_information11, per.national_identifier, p_date_earned), 'N') AGED_DPNT_FLAG,
1116 decode(ctr.cont_information2, 'Y', adult_dpnt_flag(ctr.contact_type, ctr.cont_information11, per.national_identifier, p_date_earned,nvl(ctr.cont_information4, 'N'),nvl(ctr.cont_information8, 'N')), 'N') ADULT_DPNT_FLAG,
1117 decode(ctr.cont_information2, 'Y', underaged_dpnt_flag(ctr.contact_type, ctr.cont_information11, per.national_identifier, p_date_earned), 'N') UNDERAGED_DPNT_FLAG,
1118 decode(ctr.cont_information3, 'Y', aged_flag(per.national_identifier, p_date_earned), 'N') AGED_FLAG,
1119 -- Bug 3172960
1120 decode(ctr.cont_information3, 'Y', super_aged_flag(per.national_identifier, p_date_earned), 'N') SUPER_AGED_FLAG,
1121 nvl(ctr.cont_information4, 'N') DISABLED_FLAG,
1122 decode(ctr.cont_information7, 'Y', child_flag(ctr.cont_information11,ctr.contact_type,per.national_identifier, p_date_earned), 'N') CHILD_FLAG,
1123 -- Bug 6784288; Bug 6825145; Bug 7615517
1124 decode(ctr.cont_information2, 'Y', decode(addtl_child_flag(ctr.contact_type, per.national_identifier, ctr.cont_information4, ctr.cont_information11, ctr.cont_information15, p_date_earned),'Y','Y','N'), 'N') ADDTL_CHILD,
1125 decode(ctr.cont_information2, 'Y', single_parent_child_flag(ctr.cont_information11,ctr.contact_type,per.national_identifier, p_date_earned), 'N') SINGLE_PARENT_CHILD_FLAG
1126 from per_people_f per,
1127 per_contact_relationships ctr,
1128 per_assignments_f asg
1129 where asg.assignment_id = p_assignment_id
1130 and p_date_earned
1131 between asg.effective_start_date and asg.effective_end_date
1132 and ctr.person_id = asg.person_id
1133 and ctr.cont_information_category = 'KR'
1134 and ctr.cont_information1 = 'Y'
1135 and ((ctr.cont_information9 ='D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(trunc(add_months(date_end,12),'YYYY')-1,p_date_earned))
1136 or (nvl(ctr.cont_information9,'XXX') <>'D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(date_end, p_date_earned))
1137 )
1138 and per.person_id = ctr.contact_person_id
1139 and (
1140 (
1141 p_date_earned
1142 between per.effective_start_date and per.effective_end_date
1143 )
1144 or
1145 (
1146 per.start_date = per.effective_start_date
1147 and not exists(
1148 select null
1149 from per_people_f per2
1150 where per2.person_id = per.person_id
1151 and p_date_earned
1152 between per2.effective_start_date and per2.effective_end_date)
1153 )
1154 );
1155 cursor csr_ee is
1156 select
1157 per.national_identifier,
1158 per.marital_status,
1159 ni_sex(per.national_identifier) NI_SEX,
1160 aged_flag(per.national_identifier, p_date_earned) AGED_FLAG,
1161 disabled_flag(per.person_id, p_date_earned) DISABLED_FLAG,
1162 child_flag('0','E',per.national_identifier, p_date_earned) CHILD_FLAG,
1163 nvl(pei.pei_information3,'N') FEMALE_EMP_DOC,
1164 super_aged_flag(per.national_identifier, p_date_earned) SUPER_AGED_FLAG -- Bug 4124430
1165 from per_people_f per,
1166 per_assignments_f asg,
1167 per_people_extra_info pei
1168 where asg.assignment_id = p_assignment_id
1169 and p_date_earned
1170 between asg.effective_start_date and asg.effective_end_date
1171 and per.person_id = asg.person_id
1172 and p_date_earned
1173 between per.effective_start_date and per.effective_end_date
1174 and per.person_id = pei.person_id(+)
1175 and pei.information_type(+) = 'PER_KR_RELATED_YEA_INFORMATION';
1176 l_ee csr_ee%ROWTYPE;
1177 l_num_single_parent_child number;
1178 begin
1179 p_dpnt_spouse_flag := 'N';
1180 p_num_of_aged_dpnts := 0;
1181 p_num_of_adult_dpnts := 0;
1182 p_num_of_dpnts := 0;
1183 p_num_of_underaged_dpnts := 0;
1184 p_num_of_ageds := 0;
1185 p_num_of_super_ageds := 0;
1186 p_num_of_disableds := 0;
1187 p_female_ee_flag := 'N';
1188 p_num_of_children := 0;
1189 p_num_of_addtl_child := 0; /* Bug 6784288 */
1190 p_house_holder_flag := 'N';
1191 p_tot_num_dpnts := 0; -- Bug 9737699
1192 p_single_parent_flag := 'N'; /* 16010775 */
1193 l_num_single_parent_child := 0;
1194
1195 -- Bug 5080878
1196 g_effective_date := p_date_earned;
1197 --
1198 -- Dependents
1199 --
1200 if p_non_resident_flag = 'N' then
1201 open csr_dpnt;
1202 fetch csr_dpnt bulk collect into
1203 l_dpnt_spouse_flag_tbl,
1204 l_aged_dpnt_flag_tbl,
1205 l_adult_dpnt_flag_tbl,
1206 l_underaged_dpnt_flag_tbl,
1207 l_aged_flag_tbl,
1208 l_super_aged_flag_tbl,
1209 l_disabled_flag_tbl,
1210 l_child_flag_tbl,
1211 l_addtl_child_flag_tbl,
1212 l_single_parent_child_flag_tbl; /* Bug 6784288 */
1213 close csr_dpnt;
1214 --
1215 for i in 1..l_dpnt_spouse_flag_tbl.count loop
1216 if l_dpnt_spouse_flag_tbl(i) = 'Y' then
1217 p_dpnt_spouse_flag := 'Y';
1218 end if;
1219 if l_aged_dpnt_flag_tbl(i) = 'Y' then
1220 p_num_of_aged_dpnts := p_num_of_aged_dpnts + 1;
1221 end if;
1222 if l_adult_dpnt_flag_tbl(i) = 'Y' then
1223 p_num_of_adult_dpnts := p_num_of_adult_dpnts + 1;
1224 end if;
1225 if l_underaged_dpnt_flag_tbl(i) = 'Y' then
1226 p_num_of_underaged_dpnts := p_num_of_underaged_dpnts + 1;
1227 end if;
1228 -- Bug 3172960
1229 if l_aged_flag_tbl(i) = 'Y' then
1230 if l_super_aged_flag_tbl(i) = 'Y' then
1231 p_num_of_super_ageds := p_num_of_super_ageds + 1;
1232 else
1233 p_num_of_ageds := p_num_of_ageds + 1;
1234 end if;
1235 end if;
1236 -- Bug# 3637372
1237 -- For 2003 and before the no of super ageds should be counted into no of ageds.
1238 --
1239 if p_date_earned <= to_date('31/12/2003','dd/mm/yyyy') then
1240 p_num_of_ageds := p_num_of_ageds + p_num_of_super_ageds;
1241 p_num_of_super_ageds := 0;
1242 end if;
1243
1244 if l_disabled_flag_tbl(i) = 'Y' then
1245 p_num_of_disableds := p_num_of_disableds + 1;
1246 end if;
1247 if l_child_flag_tbl(i) = 'Y' then
1248 p_num_of_children := p_num_of_children + 1;
1249 end if;
1250 -- Bug 6784288
1251 if l_addtl_child_flag_tbl(i) = 'Y' then
1252 p_num_of_addtl_child := p_num_of_addtl_child + 1;
1253 end if;
1254 -- End of Bug 6784288
1255 if l_single_parent_child_flag_tbl(i) = 'Y' then
1256 l_num_single_parent_child := l_num_single_parent_child + 1;
1257 end if;
1258 -- Bug 9737699
1259 p_tot_num_dpnts := p_tot_num_dpnts + 1;
1260 end loop;
1261 --
1262 p_num_of_dpnts := p_num_of_aged_dpnts + p_num_of_adult_dpnts + p_num_of_underaged_dpnts;
1263 end if;
1264 --
1265 -- Employee
1266 --
1267 open csr_ee;
1268 fetch csr_ee into l_ee;
1269 close csr_ee;
1270 --
1271 -- Bug 4124430: Super aged exemption is given for the employee also
1272 --
1273 if l_ee.aged_flag = 'Y' then
1274 if l_ee.super_aged_flag = 'Y' then
1275 p_num_of_super_ageds := p_num_of_super_ageds + 1 ;
1276 else
1277 p_num_of_ageds := p_num_of_ageds + 1;
1278 end if ;
1279 end if;
1280 -- End of bug 4124430
1281 if l_ee.disabled_flag = 'Y' then
1282 p_num_of_disableds := p_num_of_disableds + 1;
1283 end if;
1284 if l_ee.child_flag = 'Y' then
1285 p_num_of_children := p_num_of_children + 1;
1286 end if;
1287 --
1288 -- Female Employee Tax Exemption
1289 -- Modified for Bug 2729763
1290 --
1291 if l_ee.ni_sex = 'F' and (l_ee.marital_status = 'M' or p_dpnt_spouse_flag = 'Y' or (p_num_of_dpnts > 0 and l_ee.female_emp_doc = 'Y')) then
1292 p_female_ee_flag := 'Y';
1293 end if;
1294 --
1295 -- House Holder
1296 -- Bug 9737699
1297 if l_ee.female_emp_doc = 'Y' then
1298 p_house_holder_flag := 'Y';
1299 end if;
1300
1301 -- Bug 16010775 : Single Parent Exemption
1302 if (( l_ee.ni_sex = 'F' or l_ee.ni_sex = 'M') and p_dpnt_spouse_flag = 'N' and (l_num_single_parent_child > 0 ))then
1303 p_single_parent_flag := 'Y';
1304 IF l_ee.ni_sex = 'F' and p_female_ee_flag = 'Y' THEN
1305 p_single_parent_flag := 'N';
1306 END IF;
1307 end if;
1308 return 0;
1309 end get_dependent_info;
1310 --
1311 -- Employment Insurance Exception codes
1312 -- This function checks for the employee eligibility for EI Prem deduction
1313 -- this is used for formula function KR_EI_LOSS_EXCEPTION_CODES
1314
1315 function get_ei_loss_exception_codes(
1316 p_date_earned in date
1317 ,p_business_group_id in number
1318 ,p_assignment_id in number
1319 ,p_loss_ineligible_flag out nocopy varchar2
1320 ,p_exception_flag out nocopy varchar2
1321 ,p_exception_type out nocopy varchar2
1322 ,p_overlapped_ex_flag out nocopy varchar2
1323 ) return number is
1324
1325 --
1326
1327 cursor csr_ei_loss_code
1328 is
1329 select pei_information8 loss_code
1330 ,pei_information9 loss_date
1331 from per_people_extra_info pei
1332 ,per_people_f pp
1333 ,per_assignments_f paa
1334 ,per_time_periods ptp
1335 where paa.assignment_id = p_assignment_id
1336 and paa.business_group_id = p_business_group_id
1337 and pp.person_id = paa.person_id
1338 and pei.person_id = pp.person_id
1339 and pei.information_type = 'PER_KR_EMPLOYMENT_INS_INFO'
1340 and ptp.payroll_id = paa.payroll_id
1341 and p_date_earned between paa.effective_start_date and paa.effective_end_date
1342 and p_date_earned between pp.effective_start_date and pp.effective_end_date
1343 and p_date_earned between ptp.start_date and ptp.end_date
1344 and ptp.end_date >= fnd_date.canonical_to_date(pei_information9);
1345
1346
1347 cursor csr_ei_exception_code
1348 is
1349 select pei_information1 ei_exception_code
1350 ,pei_information4 ei_exception_type
1351 from per_people_extra_info pei
1352 ,per_assignments_f paa
1353 ,per_time_periods ptp
1354 where paa.assignment_id = p_assignment_id
1355 and paa.business_group_id = p_business_group_id
1356 and pei.person_id = paa.person_id
1357 and pei.information_type ='PER_KR_EI_EXCEPTIONS'
1358 and p_date_earned between paa.effective_start_date and paa.effective_end_date
1359 and ptp.payroll_id = paa.payroll_id
1360 and p_date_earned between ptp.start_date and ptp.end_date
1361 and fnd_date.canonical_to_date(pei.pei_information2) <= ptp.end_date
1362 and fnd_date.canonical_to_date(pei.pei_information3) >= ptp.start_date
1363 order by pei.pei_information2 desc,pei.pei_information3 desc;
1364
1365 l_ei_loss_code per_people_extra_info.pei_information8%TYPE;
1366 l_ei_loss_date per_people_extra_info.pei_information9%TYPE;
1367 l_ei_exception_type per_people_extra_info.pei_information4%TYPE;
1368 l_ei_exception_code per_people_extra_info.pei_information1%TYPE;
1369 l_ei_ex_dummy1 per_people_extra_info.pei_information8%TYPE;
1370 l_ei_ex_dummy2 per_people_extra_info.pei_information9%TYPE;
1371
1372
1373 begin
1374
1375 open csr_ei_loss_code;
1376 fetch csr_ei_loss_code into l_ei_loss_code,l_ei_loss_date;
1377 close csr_ei_loss_code;
1378
1379 if l_ei_loss_code is not null then
1380
1381 p_loss_ineligible_flag := 'Y';
1382 p_exception_flag := 'N';
1383 p_exception_type := null;
1384 p_overlapped_ex_flag := null;
1385
1386 else
1387 p_loss_ineligible_flag := 'N';
1388 p_overlapped_ex_flag := 'N';
1389
1390 open csr_ei_exception_code;
1391 fetch csr_ei_exception_code into l_ei_exception_code,l_ei_exception_type;
1392
1393 if csr_ei_exception_code%FOUND then
1394
1395 p_exception_flag := 'Y';
1396 p_exception_type := l_ei_exception_type;
1397
1398 -- check for overlapped exception codes
1399 fetch csr_ei_exception_code into l_ei_ex_dummy1,l_ei_ex_dummy2;
1400
1401 if csr_ei_exception_code%ROWCOUNT >1 then
1402 p_overlapped_ex_flag := 'Y';
1403 end if;
1404
1405 else
1406
1407 p_exception_flag := 'N';
1408 p_exception_type := null;
1409
1410 end if;
1411
1412 close csr_ei_exception_code;
1413
1414 end if;
1415
1416 return 0;
1417
1418 end get_ei_loss_exception_codes;
1419
1420 -- Bug 4674552: Added function is_exempted_dependent
1421 -- Return: 'Y' if the dependent is eligible
1422 -- for any basic or additional exemption,
1423 -- 'N' otherwise.
1424 --
1425 --
1426 function is_exempted_dependent(
1427 p_cont_type in per_contact_relationships.contact_type%type,
1428 p_kr_cont_typ in per_contact_relationships.cont_information11%type, -- Bug 7661820
1429 p_ni in per_people_f.national_identifier%type,
1430 p_itax_dpnt_flag in per_contact_relationships.cont_information2%type,
1431 p_addl_tax_exem_flag in per_contact_relationships.cont_information3%type,
1432 p_addl_disabled_flag in per_contact_relationships.cont_information4%type,
1433 p_addl_exem_flag_child in per_contact_relationships.cont_information7%type,
1434 p_age_ckh_exp_flag in per_contact_relationships.cont_information8%type,
1435 p_eff_date in pay_payroll_actions.effective_date%type,
1436 p_ins_prem_exem_incl_flag in per_contact_relationships.cont_information10%type, -- Bug 4931542
1437 p_med_exp_exem_incl_flag in per_contact_relationships.cont_information12%type, -- Bug 4931542
1438 p_edu_exp_exem_incl_flag in per_contact_relationships.cont_information13%type, -- Bug 4931542
1439 p_card_exp_exem_incl_flag in per_contact_relationships.cont_information14%type, -- Bug 4931542
1440 p_contact_extra_info_id in per_contact_extra_info_f.contact_extra_info_id%type -- Bug 5879106
1441 ) return varchar2 is
1442 --
1443 cursor csr_contact_extra_info(p_cont_extra_info_id number) is
1444 select nvl(cei_information1,0) cei_information1,
1445 nvl(cei_information2,0) cei_information2,
1446 nvl(cei_information3,0) cei_information3,
1447 nvl(cei_information4,0) cei_information4,
1448 nvl(cei_information5,0) cei_information5,
1449 nvl(cei_information6,0) cei_information6,
1450 nvl(cei_information7,0) cei_information7,
1451 nvl(cei_information8,0) cei_information8,
1452 nvl(cei_information9,0) cei_information9,
1453 nvl(cei_information10,0) cei_information10,
1454 nvl(cei_information11,0) cei_information11,
1455 nvl(cei_information16,0) cei_information16,
1456 nvl(cei_information17,0) cei_information17,
1457 nvl(cei_information18,0) cei_information18, -- Bug 14219478
1458 nvl(cei_information19,0) cei_information19, -- Bug 14219478
1459 nvl(cei_information20,0) cei_information20, -- Bug 14219478
1460 nvl(cei_information21,0) cei_information21, -- Bug 14219478
1461 nvl(cei_information22,0) cei_information22, -- Bug 14219478
1462 nvl(cei_information23,0) cei_information23, -- Bug 14219478
1463 nvl(cei_information24,0) cei_information24 -- Bug 14219478
1464 --
1465 from per_contact_extra_info_f
1466 --
1467 where contact_Extra_info_id = p_cont_extra_info_id;
1468 --
1469 l_cei_record csr_contact_extra_info%rowtype;
1470 begin
1471 --
1472 g_effective_date := p_eff_date ;
1473
1474 -- Look for basic dependent exemptions: spouse_dpnt, underaged_dpnt, aged_dpnt, and adult_dpnt
1475 --
1476 if nvl(p_itax_dpnt_flag, 'N') = 'Y' then
1477 --
1478 if dpnt_spouse_flag(p_contact_type => p_cont_type,
1479 p_kr_cont_type => p_kr_cont_typ) = 'Y' then -- Bug 7661820
1480 return 'Y' ;
1481 end if ;
1482 --
1483 if underaged_dpnt_flag(
1484 p_contact_type => p_cont_type,
1485 p_kr_cont_type => p_kr_cont_typ, -- Bug 7661820
1486 p_national_identifier => p_ni,
1487 p_effective_date => p_eff_date
1488 ) = 'Y' then
1489 return 'Y' ;
1490 end if ;
1491 --
1492 if adult_dpnt_flag(
1493 p_contact_type => p_cont_type,
1494 p_kr_cont_type => p_kr_cont_typ, -- Bug 7661820
1495 p_national_identifier => p_ni,
1496 p_effective_date => p_eff_date,
1497 p_disabled_flag => nvl(p_addl_disabled_flag, 'N'),
1498 p_age_exception_flag => nvl(p_age_ckh_exp_flag, 'N')
1499 ) = 'Y' then
1500 return 'Y' ;
1501 end if ;
1502 --
1503 if aged_dpnt_flag(
1504 p_contact_type => p_cont_type,
1505 p_kr_cont_type => p_kr_cont_typ, -- Bug 7661820
1506 p_national_identifier => p_ni,
1507 p_effective_date => p_eff_date
1508 ) = 'Y' then
1509 return 'Y' ;
1510 end if ;
1511 --
1512 end if ; -- Finished looking for basic exemptions
1513 --
1514 -- Look for additional exemptions
1515 -- Look for additional exemption: aged/superaged
1516 --
1517 if nvl(p_addl_tax_exem_flag, 'N') = 'Y' and aged_flag(p_ni, p_eff_date) = 'Y' then
1518 -- Both Aged and Superaged would be caught here
1519 return 'Y' ;
1520 end if ;
1521 --
1522 -- Look for additional exemption: child
1523 --
1524 if nvl(p_addl_exem_flag_child, 'N') = 'Y' and child_flag(p_kr_cont_typ,p_cont_type,p_ni, p_eff_date) = 'Y' then
1525 return 'Y' ;
1526 end if ;
1527 --
1528 -- Look for additional exemption: disabled
1529 --
1530 if nvl(p_addl_disabled_flag, 'N') = 'Y' then
1531 return 'Y' ;
1532 end if ;
1533 --
1534 -- Bug 5879106. Check dependent expense amounts
1535 -- This check should always be last in this function to avoid running SQL when not required
1536 --
1537 if ((p_eff_date > to_date('31-12-2005','dd-mm-yyyy')) and (p_contact_extra_info_id is not null)) then
1538 --
1539 open csr_contact_extra_info(p_contact_extra_info_id);
1540 fetch csr_contact_extra_info into l_cei_record;
1541 --
1542 if (l_cei_record.cei_information1 + l_cei_record.cei_information2
1543 + l_cei_record.cei_information10 + l_cei_record.cei_information11) > 0
1544 then
1545 return 'Y' ;
1546 end if;
1547 --
1548 if (l_cei_record.cei_information3 + l_cei_record.cei_information4) > 0 then
1549 return 'Y' ;
1550 end if;
1551 --
1552 if (l_cei_record.cei_information5 + l_cei_record.cei_information6) > 0 then
1553 return 'Y' ;
1554 end if;
1555 --
1556 if (l_cei_record.cei_information7 + l_cei_record.cei_information8
1557 + l_cei_record.cei_information9) > 0
1558 then
1559 return 'Y' ;
1560 end if;
1561 -- Bug 11740079
1562 if (l_cei_record.cei_information16 + l_cei_record.cei_information17) > 0
1563 then
1564 return 'Y' ;
1565 end if;
1566 -- Bug 14219478
1567 if (l_cei_record.cei_information18 + l_cei_record.cei_information19
1568 + l_cei_record.cei_information20 + l_cei_record.cei_information21
1569 + l_cei_record.cei_information22 + l_cei_record.cei_information23 + l_cei_record.cei_information24 ) > 0
1570 then
1571 return 'Y' ;
1572 end if;
1573 --
1574 close csr_contact_extra_info;
1575
1576 elsif (p_eff_date <= to_date('31-12-2005','dd-mm-yyyy')) then
1577 --
1578 if nvl(p_ins_prem_exem_incl_flag, 'N') = 'Y'
1579 or nvl(p_med_exp_exem_incl_flag, 'N') = 'Y'
1580 or nvl(p_edu_exp_exem_incl_flag, 'N') = 'Y'
1581 or nvl(p_card_exp_exem_incl_flag, 'N') = 'Y'
1582 then
1583 return 'Y' ;
1584 end if ;
1585 --
1586 end if;
1587 --
1588 return 'N' ;
1589 --
1590 end is_exempted_dependent ;
1591
1592 -- Bug 4750653: Added function dpnt_eligible_for_basic_exem
1593 -- Return: 'Y' if the dependent is eligible
1594 -- for basic exemption, 'N' otherwise.
1595 --
1596 function dpnt_eligible_for_basic_exem(
1597 p_cont_type in per_contact_relationships.contact_type%type,
1598 p_kr_cont_typ in per_contact_relationships.cont_information11%type, -- Bug 7661820
1599 p_ni in per_people_f.national_identifier%type,
1600 p_itax_dpnt_flag in per_contact_relationships.cont_information2%type,
1601 p_addl_disabled_flag in per_contact_relationships.cont_information4%type,
1602 p_age_ckh_exp_flag in per_contact_relationships.cont_information8%type,
1603 p_eff_date in pay_payroll_actions.effective_date%type
1604 ) return varchar2
1605 is
1606 begin
1607 -- Bug 5356651
1608 g_effective_date := p_eff_date ;
1609 --
1610 if nvl(p_itax_dpnt_flag, 'N') <> 'Y' then
1611 return 'N' ;
1612 end if ;
1613 --
1614 if dpnt_spouse_flag(p_contact_type => p_cont_type,
1615 p_kr_cont_type => p_kr_cont_typ) = 'Y' then -- Bug 7661820
1616 return 'Y' ;
1617 end if ;
1618 --
1619 if underaged_dpnt_flag(
1620 p_contact_type => p_cont_type,
1621 p_kr_cont_type => p_kr_cont_typ, -- Bug 7661820
1622 p_national_identifier => p_ni,
1623 p_effective_date => p_eff_date
1624 ) = 'Y' then
1625 return 'Y' ;
1626 end if ;
1627 --
1628 if adult_dpnt_flag(
1629 p_contact_type => p_cont_type,
1630 p_kr_cont_type => p_kr_cont_typ, -- Bug 7661820
1631 p_national_identifier => p_ni,
1632 p_effective_date => p_eff_date,
1633 p_disabled_flag => nvl(p_addl_disabled_flag, 'N'),
1634 p_age_exception_flag => nvl(p_age_ckh_exp_flag, 'N')
1635 ) = 'Y' then
1636 return 'Y' ;
1637 end if ;
1638 --
1639 if aged_dpnt_flag(
1640 p_contact_type => p_cont_type,
1641 p_kr_cont_type => p_kr_cont_typ, -- Bug 7661820
1642 p_national_identifier => p_ni,
1643 p_effective_date => p_eff_date
1644 ) = 'Y' then
1645 return 'Y' ;
1646 end if ;
1647 --
1648 return 'N' ;
1649 end dpnt_eligible_for_basic_exem ;
1650 --
1651 ---------------------------------------------------------------------------
1652 -- This function checks whether a dependent is eligible for
1653 -- Additional Child Exemption
1654 -- Bug: 4738717
1655 function dpnt_addl_child_exempted(
1656 p_addl_child_exem in varchar2,
1657 p_ni in varchar2,
1658 p_eff_date in date
1659 ) return varchar2
1660 is
1661 l_child_flag varchar2(5);
1662 l_ret varchar2(5);
1663 begin
1664 l_child_flag := child_flag('4','C',p_ni, p_eff_date);
1665 if( p_addl_child_exem = 'Y' and l_child_flag = 'Y') then
1666 l_ret := 'Y';
1667 else
1668 l_ret := 'N';
1669 end if;
1670
1671 return l_ret;
1672
1673 end dpnt_addl_child_exempted;
1674 ---------------------------------------------------------------------------
1675
1676 ---------------------------------------------------------------------------
1677 -- procedure get_double_exem_amt for Bug 6716401
1678 procedure get_double_exem_amt(p_assignment_id in per_assignments_f.assignment_id%type,
1679 p_effective_year in varchar2,
1680 p_double_exm_amt out nocopy number)
1681 is
1682
1683
1684 cursor csr_get_ass_act_id(p_assignment_id per_assignments_f.assignment_id%type,
1685 p_effective_year varchar2)
1686 is
1687 SELECT paa.assignment_action_id ass_act_id
1688 from pay_payroll_actions ppa,
1689 pay_assignment_actions paa
1690 where paa.assignment_id = p_assignment_id
1691 and paa.action_status = 'C'
1692 and ppa.payroll_action_id = paa.payroll_action_id
1693 and to_char(ppa.effective_date, 'YYYY') = p_effective_year
1694 and ppa.report_type = 'YEA'
1695 and ppa.report_qualifier = 'KR'
1696 and ppa.action_type in ('B','X')
1697 order by paa.action_sequence desc ;
1698
1699 l_assignment_action_id per_assignments_f.assignment_id%type;
1700
1701 Begin
1702 p_double_exm_amt := 0;
1703 Open csr_get_ass_act_id(p_assignment_id,p_effective_year);
1704 fetch csr_get_ass_act_id into l_assignment_action_id;
1705
1706 if csr_get_ass_act_id%FOUND then
1707
1708 p_double_exm_amt := nvl(pay_kr_report_pkg.get_dbitem_value(l_assignment_action_id,'X_YEA_DOUBLE_EXEM_AMT'),0);
1709 else
1710 p_double_exm_amt := 0;
1711 end if;
1712 close csr_get_ass_act_id;
1713
1714 End get_double_exem_amt;
1715 -----------------------------------------------------------------------------------
1716 -- Bug 6849941: New Validation Checks for Credit Card Fields on the Income Tax Form
1717 -----------------------------------------------------------------------------------
1718 Function enable_credit_card(
1719 p_person_id in number,
1720 p_contact_person_id in number,
1721 p_contact_relationship_id in number,
1722 p_date_earned in date) return varchar2
1723 -----------------------------------------------------------------------------------
1724 is
1725 --
1726 l_itax_law varchar2(2);
1727 l_cont_type varchar2(2);
1728 l_kr_cont_type varchar2(2);
1729 --
1730 cursor csr_dpnt is
1731 select
1732 ctr.cont_information2 itax_law,
1733 nvl(ctr.cont_information11, '0') kr_cont_type,
1734 decode(ctr.contact_type, 'P', '1', 'S', '3', 'A', '4', 'C', '4', 'R', '4', 'T', '4', '6') cont_type
1735 from
1736 per_contact_relationships ctr
1737 where
1738 ctr.person_id = p_person_id
1739 and ctr.cont_information_category = 'KR'
1740 and ctr.cont_information1 = 'Y'
1741 and ((ctr.cont_information9 ='D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(trunc(add_months(date_end,12),'YYYY')-1,p_date_earned))
1742 or (nvl(ctr.cont_information9,'XXX') <>'D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(date_end, p_date_earned))
1743 )
1744 and ctr.contact_person_id = p_contact_person_id
1745 and ctr.contact_relationship_id = p_contact_relationship_id;
1746
1747 begin
1748 --
1749 open csr_dpnt;
1750 fetch csr_dpnt into l_itax_law, l_kr_cont_type, l_cont_type;
1751 close csr_dpnt;
1752 --
1753 -- Bug 8644512
1754 if ((l_cont_type in ('1','2','3','4') or l_kr_cont_type in ('1','2','3','4','7')) and ( l_itax_law = 'Y')) then
1755 return 'Y';
1756 else
1757 return 'N';
1758 end if;
1759 end enable_credit_card;
1760 -----------------------------------------------------------------------------------
1761 -- Bug 7164589: Long Term Care Insurance Premium
1762 -- Bug 7228788: Added a new input parameter to the function for the Input Value Name
1763 -----------------------------------------------------------------------------------
1764 FUNCTION get_long_term_ins_skip_flag(
1765 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
1766 ,p_input_value_name in varchar2
1767 ) RETURN VARCHAR2
1768 IS
1769 --
1770 l_flag pay_run_result_values.result_value%type;
1771 -- Bug 7228788: Added a new argument for the Input Value Name to the cursor
1772 CURSOR csr(l_assignment_action_id pay_assignment_actions.assignment_action_id%type, l_input_value_name varchar2) is
1773 SELECT upper(nvl(prrv.result_value, 'N'))
1774 FROM pay_input_values_f piv
1775 ,pay_run_result_values prrv
1776 ,pay_run_results prr
1777 ,pay_payroll_actions ppa
1778 ,pay_assignment_actions paa
1779 ,pay_element_types_f pet
1780 WHERE paa.assignment_action_id = l_assignment_action_id
1781 and ppa.payroll_action_id = paa.payroll_action_id
1782 and prr.assignment_action_id = paa.assignment_action_id
1783 and prr.status in ('P', 'PA')
1784 and prr.element_type_id = pet.element_type_id
1785 and pet.element_name = 'LTCI_PREM'
1786 and piv.legislation_code = 'KR'
1787 and pet.legislation_code = 'KR'
1788 and prrv.run_result_id = prr.run_result_id
1789 and piv.input_value_id = prrv.input_value_id
1790 and piv.name = l_input_value_name
1791 and ppa.effective_date
1792 between piv.effective_start_date and piv.effective_end_date;
1793 --
1794 BEGIN
1795 --
1796 l_flag := 'N';
1797
1798 OPEN csr(p_assignment_action_id,p_input_value_name);
1799 FETCH csr into l_flag;
1800
1801 IF csr%NOTFOUND THEN
1802 l_flag := 'N';
1803 END IF;
1804
1805 CLOSE csr;
1806
1807 RETURN l_flag;
1808 --
1809 END;
1810 --
1811 ----------------------------------------------------------------------------------------------------
1812 -- Bug 7361372: FUNCTION chk_id_format() checks if the argument1 is in the same format as argument2.
1813 -- If not then an error is raised. Else the same string as argument1 is returned.
1814 ----------------------------------------------------------------------------------------------------
1815 FUNCTION chk_id_format(
1816 p_chk_string IN VARCHAR2,
1817 p_format_string IN VARCHAR2) RETURN VARCHAR2
1818 IS
1819 l_dummy varchar2(30);
1820 --
1821 BEGIN
1822 --
1823 l_dummy := hr_ni_chk_pkg.chk_nat_id_format(p_chk_string,p_format_string);
1824 --
1825 IF l_dummy = '0' THEN
1826 fnd_message.set_name('PAY', 'PAY_KR_YEA_INV_TAX_GRP_REGNO');
1827 fnd_message.set_token('REGNO',p_chk_string);
1828 fnd_message.raise_error;
1829 END IF;
1830 --
1831 RETURN l_dummy;
1832 --
1833 END;
1834 --
1835 -----------------------------------------------------------------------------------
1836 -- Bug : 7142612
1837 -----------------------------------------------------------------------------------
1838 FUNCTION enable_donation_fields(
1839 p_person_id in number,
1840 p_contact_person_id in number,
1841 p_contact_relationship_id in number,
1842 p_date_earned in date) return varchar2
1843 -----------------------------------------------------------------------------------
1844 IS
1845 --
1846 l_itax_law varchar2(2);
1847 l_cont_type varchar2(2);
1848 l_kr_cont_type varchar2(2);
1849 --
1850 cursor csr_dpnt is
1851 select
1852 ctr.cont_information2 itax_law,
1853 nvl(ctr.cont_information11, '0') kr_cont_type,
1854 decode(ctr.contact_type, 'P', '1', 'S', '3', 'A', '4', 'C', '4', 'R', '4', 'T', '4', '6') cont_type
1855 from
1856 per_contact_relationships ctr
1857 where
1858 ctr.person_id = p_person_id
1859 and ctr.cont_information_category = 'KR'
1860 and ctr.cont_information1 = 'Y'
1861 and ((ctr.cont_information9 ='D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(trunc(add_months(date_end,12),'YYYY')-1,p_date_earned))
1862 or (nvl(ctr.cont_information9,'XXX') <>'D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(date_end, p_date_earned))
1863 )
1864 and ctr.contact_person_id = p_contact_person_id
1865 and ctr.contact_relationship_id = p_contact_relationship_id;
1866
1867 begin
1868 --
1869 open csr_dpnt;
1870 fetch csr_dpnt into l_itax_law, l_kr_cont_type, l_cont_type;
1871 close csr_dpnt;
1872 --
1873 -- Bug 8644512
1874 if ((l_cont_type in ('1','3','4','5') or l_kr_cont_type in ('3','4','7','1','5','2')) and ( l_itax_law = 'Y')) then
1875 return 'Y';
1876 else
1877 return 'N';
1878 end if;
1879 --
1880 END;
1881 --
1882 -----------------------------------------------------------------------------------
1883 -- Bug 7526435 FUNCTION validate_bus_reg_num() checks the validation logic for provider reg.
1884 -- no. of medical service provider and returns false if validation fails
1885 -----------------------------------------------------------------------------------
1886 FUNCTION validate_bus_reg_num(p_national_identifier IN VARCHAR2) RETURN VARCHAR2 IS
1887 l_return_bool varchar2(30);
1888 l_dummy varchar2(30);
1889 sum1 integer;
1890 dummy integer;
1891 dummychk integer;
1892 type getlist_var is varray(10) of integer;
1893 getlist getlist_var := getlist_var();
1894 chkvalue getlist_var := getlist_var(1,3,7,1,3,7,1,3,5);
1895 BEGIN
1896 sum1 := 0;
1897 l_dummy := replace (p_national_identifier,'-','');
1898 for i in 1..10 loop
1899 getlist.extend;
1900 getlist(i) := substr(l_dummy,i,1);
1901 end loop;
1902 for i in 1..9 loop
1903 sum1 := sum1 + (getlist(i) * chkvalue(i));
1904 end loop;
1905 sum1 := sum1 + trunc((getlist(9) * 5)/10);
1906 dummy := sum1 - (trunc(sum1/10)* 10);
1907 dummychk := 0;
1908 if(dummy <> 0) then
1909 dummychk := 10-dummy;
1910 end if;
1911 if(dummychk <> getlist(10)) then
1912 l_return_bool := 'false';
1913 else
1914 l_return_bool := 'true';
1915 end if;
1916 RETURN l_return_bool;
1917 END;
1918 --------------------------------------------------------------------------
1919 -- Bug 7676136: Function to get the Global value
1920 --------------------------------------------------------------------------
1921 function get_globalvalue(p_glbvar in varchar2,p_process_date in date) return number
1922 is
1923 --
1924 cursor csr_ff_global
1925 is
1926 select to_number(glb.global_value,'99999999999999999999.99999')
1927 from ff_globals_f glb
1928 where glb.global_name = p_glbvar
1929 and p_process_date between glb.effective_start_date and glb.effective_end_date;
1930 --
1931 l_glbvalue number default 0;
1932 begin
1933 Open csr_ff_global;
1934 fetch csr_ff_global into l_glbvalue;
1935 close csr_ff_global;
1936 --
1937 if l_glbvalue is null then
1938 l_glbvalue := 0;
1939 end if;
1940 --
1941 return l_glbvalue;
1942 end;
1943 --------------------------------------------------------------------------
1944 -----------------------------------------------------------------------------------
1945 -- Bug 8341054: Gets the Flag for Input Value of an Element
1946 -----------------------------------------------------------------------------------
1947 FUNCTION get_element_input_value(
1948 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
1949 ,p_input_value_name in varchar2
1950 ,p_element_name in varchar2
1951 ) RETURN VARCHAR2
1952 IS
1953 --
1954 l_flag pay_run_result_values.result_value%type;
1955 -- Bug 7228788: Added a new argument for the Input Value Name to the cursor
1956 CURSOR csr(l_assignment_action_id pay_assignment_actions.assignment_action_id%type, l_input_value_name varchar2, l_element_name varchar2) is
1957 SELECT upper(nvl(prrv.result_value, 'N'))
1958 FROM pay_input_values_f piv
1959 ,pay_run_result_values prrv
1960 ,pay_run_results prr
1961 ,pay_payroll_actions ppa
1962 ,pay_assignment_actions paa
1963 ,pay_element_types_f pet
1964 WHERE paa.assignment_action_id = l_assignment_action_id
1965 and ppa.payroll_action_id = paa.payroll_action_id
1966 and prr.assignment_action_id = paa.assignment_action_id
1967 and prr.status in ('P', 'PA')
1968 and prr.element_type_id = pet.element_type_id
1969 and pet.element_name = l_element_name
1970 and piv.legislation_code = 'KR'
1971 and pet.legislation_code = 'KR'
1972 and prrv.run_result_id = prr.run_result_id
1973 and piv.input_value_id = prrv.input_value_id
1974 and piv.name = l_input_value_name
1975 and ppa.effective_date
1976 between piv.effective_start_date and piv.effective_end_date;
1977 --
1978 BEGIN
1979 --
1980 l_flag := 'N';
1981
1982 OPEN csr(p_assignment_action_id,p_input_value_name,p_element_name);
1983 FETCH csr into l_flag;
1984
1985 IF csr%NOTFOUND THEN
1986 l_flag := 'N';
1987 END IF;
1988
1989 CLOSE csr;
1990
1991 RETURN l_flag;
1992 --
1993 END;
1994 --
1995 -----------------------------------------------------------------------------------
1996 -- Bug 8466662: Gets the Run Result Value for an Input Value of Type Money
1997 -----------------------------------------------------------------------------------
1998 FUNCTION get_element_rr_value(
1999 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
2000 ,p_input_value_name in varchar2
2001 ,p_element_name in varchar2
2002 ) RETURN number
2003 IS
2004 --
2005 l_dummy pay_run_result_values.result_value%type;
2006 CURSOR csr(l_assignment_action_id pay_assignment_actions.assignment_action_id%type, l_input_value_name varchar2, l_element_name varchar2) is
2007 SELECT nvl(prrv.result_value, 0)
2008 FROM pay_input_values_f piv
2009 ,pay_run_result_values prrv
2010 ,pay_run_results prr
2011 ,pay_payroll_actions ppa
2012 ,pay_assignment_actions paa
2013 ,pay_element_types_f pet
2014 WHERE paa.assignment_action_id = l_assignment_action_id
2015 and ppa.payroll_action_id = paa.payroll_action_id
2016 and prr.assignment_action_id = paa.assignment_action_id
2017 and prr.status in ('P', 'PA')
2018 and prr.element_type_id = pet.element_type_id
2019 and pet.element_name = l_element_name
2020 and piv.legislation_code = 'KR'
2021 and pet.legislation_code = 'KR'
2022 and prrv.run_result_id = prr.run_result_id
2023 and piv.input_value_id = prrv.input_value_id
2024 and piv.name = l_input_value_name
2025 and ppa.effective_date
2026 between piv.effective_start_date and piv.effective_end_date;
2027 --
2028 BEGIN
2029 --
2030 l_dummy := 0;
2031
2032 OPEN csr(p_assignment_action_id,p_input_value_name,p_element_name);
2033 FETCH csr into l_dummy;
2034
2035 IF csr%NOTFOUND THEN
2036 l_dummy := 0;
2037 END IF;
2038
2039 CLOSE csr;
2040
2041 RETURN l_dummy;
2042 --
2043 END;
2044 --
2045
2046 --------------------------------------------------------------------------------------------------
2047 -- Bug 8466662: Gets the Flag for Input Value of an Element and returns 'yes' if value not found
2048 --------------------------------------------------------------------------------------------------
2049 FUNCTION get_element_input_value_y(
2050 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
2051 ,p_input_value_name in varchar2
2052 ,p_element_name in varchar2
2053 ) RETURN VARCHAR2
2054 IS
2055 --
2056 l_flag pay_run_result_values.result_value%type;
2057 -- Bug 7228788: Added a new argument for the Input Value Name to the cursor
2058 CURSOR csr(l_assignment_action_id pay_assignment_actions.assignment_action_id%type, l_input_value_name varchar2, l_element_name varchar2) is
2059 SELECT upper(nvl(prrv.result_value, 'Y'))
2060 FROM pay_input_values_f piv
2061 ,pay_run_result_values prrv
2062 ,pay_run_results prr
2063 ,pay_payroll_actions ppa
2064 ,pay_assignment_actions paa
2065 ,pay_element_types_f pet
2066 WHERE paa.assignment_action_id = l_assignment_action_id
2067 and ppa.payroll_action_id = paa.payroll_action_id
2068 and prr.assignment_action_id = paa.assignment_action_id
2069 and prr.status in ('P', 'PA')
2070 and prr.element_type_id = pet.element_type_id
2071 and pet.element_name = l_element_name
2072 and piv.legislation_code = 'KR'
2073 and pet.legislation_code = 'KR'
2074 and prrv.run_result_id = prr.run_result_id
2075 and piv.input_value_id = prrv.input_value_id
2076 and piv.name = l_input_value_name
2077 and ppa.effective_date
2078 between piv.effective_start_date and piv.effective_end_date;
2079 --
2080 BEGIN
2081 --
2082 l_flag := 'Y';
2083
2084 OPEN csr(p_assignment_action_id,p_input_value_name,p_element_name);
2085 FETCH csr into l_flag;
2086
2087 IF csr%NOTFOUND THEN
2088 l_flag := 'Y';
2089 END IF;
2090
2091 CLOSE csr;
2092
2093 RETURN l_flag;
2094 --
2095 END;
2096 --
2097 -----------------------------------------------------------------------------------
2098 -- Bug 8466662: Gets the Run Result Value for an Input Value of Date
2099 -----------------------------------------------------------------------------------
2100 FUNCTION get_element_rr_date_value(
2101 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
2102 ,p_input_value_name in varchar2
2103 ,p_element_name in varchar2
2104 ) RETURN date
2105 IS
2106 --
2107 l_dummy pay_run_result_values.result_value%type;
2108 CURSOR csr(l_assignment_action_id pay_assignment_actions.assignment_action_id%type, l_input_value_name varchar2, l_element_name varchar2) is
2109 SELECT nvl(fnd_date.canonical_to_date(prrv.result_value), to_date('01-01-1900','dd-mm-yyyy'))
2110 FROM pay_input_values_f piv
2111 ,pay_run_result_values prrv
2112 ,pay_run_results prr
2113 ,pay_payroll_actions ppa
2114 ,pay_assignment_actions paa
2115 ,pay_element_types_f pet
2116 WHERE paa.assignment_action_id = l_assignment_action_id
2117 and ppa.payroll_action_id = paa.payroll_action_id
2118 and prr.assignment_action_id = paa.assignment_action_id
2119 and prr.status in ('P', 'PA')
2120 and prr.element_type_id = pet.element_type_id
2121 and pet.element_name = l_element_name
2122 and piv.legislation_code = 'KR'
2123 and pet.legislation_code = 'KR'
2124 and prrv.run_result_id = prr.run_result_id
2125 and piv.input_value_id = prrv.input_value_id
2126 and piv.name = l_input_value_name
2127 and ppa.effective_date
2128 between piv.effective_start_date and piv.effective_end_date;
2129 --
2130 BEGIN
2131 --
2132 l_dummy := to_date('01-01-1900','dd-mm-yyyy');
2133
2134 OPEN csr(p_assignment_action_id,p_input_value_name,p_element_name);
2135 FETCH csr into l_dummy;
2136
2137 IF csr%NOTFOUND THEN
2138 l_dummy := to_date('01-01-1900','dd-mm-yyyy');
2139 END IF;
2140
2141 CLOSE csr;
2142
2143 RETURN l_dummy;
2144 --
2145 END;
2146 --
2147 ---------------------------------------------------------------------------------------------
2148 -- Bug 8466662: This function will be called from the TAX formula to fetch the individual
2149 -- Calculated Taxes. Based on the value for the input p_class it will return
2150 -- the calculated tax values
2151 -- (p_class = 1 => individual calculated tax for each working place irrespective
2152 -- of their eligiblity for the Post tax deduction.
2153 -- p_class = 2 => individual calculated tax values for all the eligible working
2154 -- places.
2155 ---------------------------------------------------------------------------------------------
2156 function SepPayPostTax( p_assignment_id in number,
2157 p_business_group_id in number,
2158 p_date_earned in date,
2159 p_assignment_action_id in number,
2160 p_total_taxable_earnings in number,
2161 p_nst_taxable_earnings in number,
2162 p_wkpd_int_sep_pay in number,
2163 p_sep_pay_income_exem_rate in number,
2164 p_class in number,
2165 p_sep_cal_mode in varchar2,
2166 p_sep_lump_sum_amount in number,
2167 p_emp_eligibility_flag in varchar2,
2168 p_st_emp_hire_date in date,
2169 p_st_emp_leaving_date in date,
2170 p_nst_emp_hire_date in date,
2171 p_nst_emp_leaving_date in date,
2172 p_sep_max_post_tax_deduc in number,
2173 p_amount_expected in number,
2174 p_personal_contribution in number,
2175 p_pension_exemption in number,
2176 p_principal_interest in number,
2177 p_nst_amount_expected in number,
2178 p_prev_sep_lump_sum_amt in number,
2179 p_nst_sep_calc_tax out NOCOPY number,
2180 p_sep_calc_tax out NOCOPY number,
2181 p_st_max_lim out NOCOPY number,
2182 p_nst_max_lim out NOCOPY number
2183 ) return number
2184 is
2185
2186 /* Cursor to fetch the sum of the Statutory Sep pay Earnings */
2187 cursor get_prev_stat_sp_earn(l_effective_date in date, l_assignment_id in number, l_element_entry_id in number) is
2188 SELECT peevf.element_entry_id element_entry_id,
2189 sum(peevf.screen_entry_value) prev_earnings
2190 FROM pay_element_entry_values_f peevf,
2191 pay_element_entries_f peef,
2192 pay_element_types_f petf,
2193 pay_input_values_f pivf
2194 WHERE peevf.element_entry_id = peef.element_entry_id
2195 and peevf.element_entry_id = nvl(l_element_entry_id,peevf.element_entry_id)
2196 and peef.element_type_id = petf.element_type_id
2197 and petf.element_name LIKE 'PREV_ER_INFO'
2198 and petf.legislation_code = 'KR'
2199 and peef.assignment_id = l_assignment_id
2200 and peevf.input_value_id = pivf.input_value_id
2201 and pivf.element_type_id = petf.element_type_id
2202 and pivf.name IN ('SEP_INS', 'SEP_PAY', 'SP_SEP_ALW')
2203 and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
2204 and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
2205 and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
2206 and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
2207 group by peevf.element_entry_id;
2208
2209 /* Cursor to fetch the sum of the Non-Statutory Sep pay Earnings */
2210 cursor get_prev_non_stat_sp_earn(l_effective_date in date, l_assignment_id in number, l_element_entry_id in number) is
2211 SELECT peevf.screen_entry_value prev_earnings
2212 FROM pay_element_entry_values_f peevf,
2213 pay_element_entries_f peef,
2214 pay_element_types_f petf,
2215 pay_input_values_f pivf
2216 WHERE peevf.element_entry_id = peef.element_entry_id
2217 and peevf.element_entry_id = nvl(l_element_entry_id,peevf.element_entry_id)
2218 and peef.element_type_id = petf.element_type_id
2219 and petf.element_name LIKE 'PREV_ER_INFO'
2220 and petf.legislation_code = 'KR'
2221 and peef.assignment_id = l_assignment_id
2222 and peevf.input_value_id = pivf.input_value_id
2223 and pivf.element_type_id = petf.element_type_id
2224 and pivf.name = 'SP_SEP_ALW'
2225 and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
2226 and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
2227 and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
2228 and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
2229
2230 /* Cursor to find the eligible working place entries for the Sep Pay Post Tax Deduction */
2231 cursor get_eligible_earnings(l_effective_date in date, l_assignment_id in number) is
2232 SELECT peevf.element_entry_id element_entry_id
2233 FROM pay_element_entry_values_f peevf,
2234 pay_element_entries_f peef,
2235 pay_element_types_f petf,
2236 pay_input_values_f pivf
2237 WHERE peevf.element_entry_id = peef.element_entry_id
2238 and peef.element_type_id = petf.element_type_id
2239 and petf.element_name LIKE 'PREV_ER_INFO'
2240 and petf.legislation_code = 'KR'
2241 and peef.assignment_id = l_assignment_id
2242 and peevf.input_value_id = pivf.input_value_id
2243 and pivf.element_type_id = petf.element_type_id
2244 and pivf.name IN ('ELIGIBLE_POST_TAX_DEDUC_FLAG')
2245 and nvl(peevf.screen_entry_value,'N') = 'Y'
2246 and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
2247 and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
2248 and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
2249 and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
2250
2251 /* Cursor to fetch the Hiring, Leaving and Final Interim Separation Pay date for the previous
2252 employer to calculate the Service Period and Overlap Periods */
2253 cursor get_prev_hire_leave_dt(l_effective_date in date, l_assignment_id in number, l_element_entry_id in number) is
2254 SELECT fnd_date.canonical_to_date(peevf.screen_entry_value) dt_value
2255 FROM pay_element_entry_values_f peevf,
2256 pay_element_entries_f peef,
2257 pay_element_types_f petf,
2258 pay_input_values_f pivf
2259 WHERE peevf.element_entry_id = peef.element_entry_id
2260 and peevf.element_entry_id = nvl(l_element_entry_id,peevf.element_entry_id)
2261 and peef.element_type_id = petf.element_type_id
2262 and petf.element_name LIKE 'PREV_ER_INFO'
2263 and petf.legislation_code = 'KR'
2264 and peef.assignment_id = l_assignment_id
2265 and peevf.input_value_id = pivf.input_value_id
2266 and pivf.element_type_id = petf.element_type_id
2267 and pivf.name IN ('H_DATE','L_DATE','FINAL_INT_DATE')
2268 and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
2269 and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
2270 and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
2271 and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
2272 order by pivf.name;
2273
2274 /* Start of Bug 8525925 */
2275 /* Cursor to get the business Registration number of Separation Pension */
2276 cursor get_prev_bus_reg_num_sep (l_effective_date in date, l_assignment_id in number, l_element_entry_id in number) is
2277 SELECT peevf.element_entry_id element_entry_id
2278 FROM pay_element_entry_values_f peevf,
2279 pay_element_entries_f peef,
2280 pay_element_types_f petf,
2281 pay_input_values_f pivf
2282 WHERE peevf.element_entry_id = peef.element_entry_id
2283 and peef.element_type_id = petf.element_type_id
2284 and petf.element_name LIKE 'PREV_SEP_PENS_DTLS'
2285 and petf.legislation_code = 'KR'
2286 and peef.assignment_id = l_assignment_id
2287 and peevf.input_value_id = pivf.input_value_id
2288 and pivf.element_type_id = petf.element_type_id
2289 and pivf.name IN ('BP_NUMBER')
2290 and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
2291 and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
2292 and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
2293 and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
2294 and peevf.screen_entry_value in (SELECT peevf.screen_entry_value bus_reg_num
2295 FROM pay_element_entry_values_f peevf,
2296 pay_element_entries_f peef,
2297 pay_element_types_f petf,
2298 pay_input_values_f pivf
2299 WHERE peevf.element_entry_id = peef.element_entry_id
2300 and peevf.element_entry_id = nvl(l_element_entry_id,peevf.element_entry_id)
2301 and peef.element_type_id = petf.element_type_id
2302 and petf.element_name LIKE 'PREV_ER_INFO'
2303 and petf.legislation_code = 'KR'
2304 and peef.assignment_id = l_assignment_id
2305 and peevf.input_value_id = pivf.input_value_id
2306 and pivf.element_type_id = petf.element_type_id
2307 and pivf.name IN ('BP_NUMBER')
2308 and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
2309 and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
2310 and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
2311 and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
2312 group by peevf.screen_entry_value)
2313
2314 group by peevf.element_entry_id;
2315
2316 /* Cursor to get the Total Amount Received */
2317 cursor get_prev_emp_amt (l_effective_date in date, l_assignment_id in number, l_element_entry_id in number) is
2318 SELECT nvl(peevf.screen_entry_value,0) entry_value
2319 FROM pay_element_entry_values_f peevf,
2320 pay_element_entries_f peef,
2321 pay_element_types_f petf,
2322 pay_input_values_f pivf
2323 WHERE peevf.element_entry_id = peef.element_entry_id
2324 and peevf.element_entry_id = nvl(l_element_entry_id,peevf.element_entry_id)
2325 and peef.element_type_id = petf.element_type_id
2326 and petf.element_name LIKE 'PREV_SEP_PENS_DTLS'
2327 and petf.legislation_code = 'KR'
2328 and peef.assignment_id = l_assignment_id
2329 and peevf.input_value_id = pivf.input_value_id
2330 and pivf.element_type_id = petf.element_type_id
2331 and pivf.name IN ('PRINCIPAL_INTRST','PERS_CONTRIBUTION','PENS_EXEM','AMT_EXP_STAT_SEP','AMT_EXP_NONSTAT_SEP','TOTAL_RECEIVED')
2332 and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
2333 and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
2334 and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
2335 and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
2336 order by pivf.name;
2337
2338 /* Bug 8601387: 2009 Non Statutory Separation Pay Tax Receipt Layout Updates */
2339 cursor csr_prev_lsa(l_element_entry_id in number,l_assignment_action_id in number) is
2340 select nvl(result_value,0) result_value
2341 from pay_element_types_f petf,
2342 pay_run_results prr,
2343 pay_run_result_values prrv,
2344 pay_element_entries_f peef
2345 where petf.element_name = 'PREV_EMP_SEP_LUMP_SUM_AMOUNT'
2346 and petf.element_type_id = prr.element_type_id
2347 and prr.assignment_action_id = l_assignment_action_id
2348 and prrv.run_result_id = prr.run_result_id
2349 and prr.source_id = l_element_entry_id
2350 and petf.legislation_code = 'KR';
2351
2352 /* End of Bug 8525925 */
2353 -- Local Variables --
2354 l_taxable_earnings number;
2355 l_sep_taxable_earnings number;
2356 l_receivable_sep_pay number;
2357 l_element_entry_id number;
2358 l_dummy number;
2359 l_total_eligible_earnings number;
2360 l_nst_prev_earnings number;
2361 l_total_nst_eligible_earnings number;
2362 l_sep_calc_tax number;
2363 l_nst_sep_calc_tax number;
2364 l_nst_taxable_earnings number;
2365 l_emp_hire_date date;
2366 l_emp_leaving_date date;
2367 l_nst_emp_hire_date date;
2368 l_nst_emp_leaving_date date;
2369 l_prev_hire_date date;
2370 l_prev_int_date date;
2371 l_prev_leaving_date date;
2372 l_st_overlap_period number;
2373 l_st_overlap_date1 date;
2374 l_st_overlap_date2 date;
2375 l_nst_overlap_period number;
2376 l_nst_overlap_date1 date;
2377 l_nst_overlap_date2 date;
2378 l_curr_max_lim number;
2379 l_prev_max_lim number;
2380 l_st_curr_max_lim number;
2381 l_nst_curr_max_lim number;
2382 l_st_prev_max_lim number;
2383 l_nst_prev_max_lim number;
2384 l_count number;
2385 /* Bug 8525925 */
2386 l_sep_tax_conversion_reqd varchar2(1);
2387 prev_principal_interest number;
2388 prev_pension_exemption number;
2389 prev_personal_contibution number;
2390 prev_total_received number;
2391 prev_amt_exp_stat_sep number;
2392 prev_total_lump_sum_amount number;
2393 l_nst_receivable_sep_pay number;
2394 l_nst_sep_taxable_earnings number;
2395 prev_amt_nonstat_sep number;
2396 l_total_lump_sum_amount number;
2397 prev_sep_lump_sum_amt number;
2398
2399 begin
2400 --
2401 hr_utility.trace('Entering Function SepPayPostTax.......');
2402 hr_utility.trace('p_assignment_id = '||p_assignment_id);
2403 --
2404 p_nst_sep_calc_tax := 0;
2405 p_sep_calc_tax := 0;
2406 p_st_max_lim := 0;
2407 p_nst_max_lim := 0;
2408
2409 -- First check if the function call is for Statutory Sep Pay or
2410 -- for Non-Statutory Sep Pay
2411 hr_utility.trace('Sep Pay Mode = '||p_sep_cal_mode);
2412 --
2413 if p_sep_cal_mode = 'NORMAL_SEP_PAY' then
2414
2415 -- New variable used to find the type of the total tax to be returned
2416 -- p_class = 1 means that the function call will return the total of the
2417 -- individual working place calculated tax amount
2418 -- p_class = 2 means that the function call will return the total of the
2419 -- calculated tax for the eligible working places
2420
2421 if p_class = 1 then /* if we have to return the total tax for the individual working places */
2422
2423 l_taxable_earnings := 0;
2424 l_sep_taxable_earnings := 0;
2425 l_receivable_sep_pay := 0;
2426 p_sep_calc_tax := 0;
2427 l_dummy := 0;
2428 l_emp_hire_date := null;
2429 l_emp_leaving_date := null;
2430 l_prev_hire_date := null;
2431 l_prev_int_date := null;
2432 l_prev_leaving_date := null;
2433 -- Bug 8525925
2434 l_sep_tax_conversion_reqd := null;
2435 l_total_lump_sum_amount := 0 ;
2436
2437 -- For Current Employer
2438 l_taxable_earnings := p_total_taxable_earnings;
2439 l_sep_taxable_earnings := p_total_taxable_earnings;
2440
2441 -- Bug 8525925
2442 -- If Tax Conversion is required then calculate the receivable_sep_pay and
2443 -- modify the taxable earnings
2444 if p_amount_expected > 0 then
2445 l_sep_tax_conversion_reqd := 'Y';
2446 -- Bug 8996756
2447 if p_principal_interest > 0 then
2448 l_total_lump_sum_amount := p_amount_expected *
2449 ( 1 - (p_personal_contribution - p_pension_exemption) / p_principal_interest );
2450 else
2451 l_total_lump_sum_amount := p_amount_expected;
2452 end if;
2453 -- End of 8996756
2454 l_total_lump_sum_amount := greatest(0,trunc(l_total_lump_sum_amount)) ;
2455 l_receivable_sep_pay := l_taxable_earnings - p_sep_lump_sum_amount + l_total_lump_sum_amount;
2456 l_taxable_earnings := greatest(trunc(l_receivable_sep_pay),0);
2457 end if;
2458
2459 l_emp_hire_date := p_st_emp_hire_date;
2460 l_emp_leaving_date := p_st_emp_leaving_date;
2461
2462 -- Call the function to calculate the Statutory Separation Pay Tax
2463 -- for the current employer
2464 p_sep_calc_tax := SepPayTaxCalc(
2465 ceil(months_between(l_emp_leaving_date,l_emp_hire_date)/12),
2466 0,
2467 l_taxable_earnings,
2468 l_sep_taxable_earnings,
2469 l_receivable_sep_pay,
2470 l_sep_tax_conversion_reqd,
2471 p_business_group_id,
2472 p_date_earned,
2473 p_sep_pay_income_exem_rate);
2474
2475 -- For Previous Employers
2476 l_element_entry_id := null;
2477 l_taxable_earnings := 0;
2478 l_sep_taxable_earnings := 0;
2479 l_receivable_sep_pay := 0;
2480 l_dummy := 0;
2481
2482
2483 for i in get_prev_stat_sp_earn(p_date_earned, p_assignment_id, l_element_entry_id) loop
2484 /* Bug 8525925 */
2485 l_sep_tax_conversion_reqd := null;
2486 prev_principal_interest := 0;
2487 prev_pension_exemption := 0;
2488 prev_personal_contibution := 0;
2489 prev_amt_exp_stat_sep := 0;
2490 prev_total_received := 0;
2491 prev_total_lump_sum_amount := 0;
2492 l_count := 0;
2493 prev_sep_lump_sum_amt := 0;
2494
2495 l_taxable_earnings := i.prev_earnings ;
2496 l_taxable_earnings := nvl(l_taxable_earnings,0);
2497 l_sep_taxable_earnings := l_taxable_earnings;
2498
2499 for j in get_prev_bus_reg_num_sep(p_date_earned, p_assignment_id,i.element_entry_id) loop
2500
2501 /* Bug 8601387*/
2502
2503 open csr_prev_lsa(j.element_entry_id,p_assignment_action_id);
2504 fetch csr_prev_lsa into prev_sep_lump_sum_amt;
2505 close csr_prev_lsa;
2506 l_taxable_earnings := l_taxable_earnings + nvl(prev_sep_lump_sum_amt,0);
2507
2508 for k in get_prev_emp_amt (p_date_earned, p_assignment_id,j.element_entry_id) loop
2509
2510 if l_count = 0 then
2511 prev_amt_nonstat_sep := k.entry_value;
2512 l_count := l_count + 1;
2513 elsif l_count = 1 then
2514 prev_amt_exp_stat_sep := k.entry_value;
2515 l_count := l_count + 1;
2516 elsif l_count = 2 then
2517 prev_pension_exemption := k.entry_value;
2518 l_count := l_count + 1;
2519 elsif l_count = 3 then
2520 prev_personal_contibution := k.entry_value;
2521 l_count := l_count + 1;
2522 elsif l_count = 4 then
2523 prev_principal_interest := k.entry_value;
2524 l_count := l_count + 1;
2525 elsif l_count = 5 then
2526 prev_total_received := k.entry_value;
2527 end if;
2528
2529 end loop;
2530
2531 end loop;
2532
2533 -- If Tax Conversion is required then calculate the receivable_sep_pay and
2534 -- modify the taxable earnings
2535 if (nvl(prev_amt_exp_stat_sep,0) > 0 and nvl(prev_total_received,0) > 0) then
2536
2537 l_sep_tax_conversion_reqd := 'Y';
2538 if prev_principal_interest > 0 then
2539 prev_total_lump_sum_amount := nvl(prev_amt_exp_stat_sep,0) *
2540 ( 1 - (nvl(prev_personal_contibution,0) - nvl(prev_pension_exemption,0)) / prev_principal_interest);
2541 else
2542 prev_total_lump_sum_amount := nvl(prev_amt_exp_stat_sep,0);
2543 end if;
2544 prev_total_lump_sum_amount := greatest(0,trunc(prev_total_lump_sum_amount));
2545 l_receivable_sep_pay := l_taxable_earnings + prev_total_lump_sum_amount - p_prev_sep_lump_sum_amt;
2546 l_taxable_earnings := greatest(l_receivable_sep_pay,0);
2547
2548 elsif nvl(prev_amt_exp_stat_sep,0) > 0 then
2549
2550 l_sep_tax_conversion_reqd := 'Y';
2551 prev_total_lump_sum_amount := greatest(0,trunc(prev_amt_exp_stat_sep));
2552 l_receivable_sep_pay := l_taxable_earnings + prev_total_lump_sum_amount - p_prev_sep_lump_sum_amt;
2553 l_taxable_earnings := greatest(l_receivable_sep_pay,0);
2554
2555 end if;
2556
2557 /* End of Bug 8525925 */
2558 l_prev_hire_date := null;
2559 l_prev_int_date := null;
2560 l_prev_leaving_date := null;
2561 l_st_overlap_date1 := null;
2562 l_st_overlap_date2 := null;
2563 l_st_overlap_period := 0;
2564 l_count := 0;
2565
2566 -- Loop to fetch the date for the previous employer to calculate the Service Period and Overlap Periods
2567 for prev in get_prev_hire_leave_dt(p_date_earned, p_assignment_id, i.element_entry_id) loop
2568 if l_count = 0 then
2569 l_prev_int_date := prev.dt_value;
2570 l_count := l_count + 1;
2571 elsif l_count = 1 then
2572 l_prev_hire_date:= prev.dt_value;
2573 l_count := l_count + 1;
2574 elsif l_count = 2 then
2575 l_prev_leaving_date := prev.dt_value;
2576 l_count := l_count + 1;
2577 end if;
2578
2579 end loop;
2580
2581 if l_prev_int_date is null then
2582 l_prev_int_date := l_prev_hire_date;
2583 end if;
2584
2585 if (l_prev_hire_date is null) or (l_prev_leaving_date is null) then
2586 fnd_message.set_name('PAY','PAY_KR_PREV_SEP_DATE_REQ');
2587 fnd_message.raise_error;
2588 end if;
2589
2590 if l_emp_hire_date > l_prev_int_date then
2591 l_st_overlap_date1 := l_emp_hire_date;
2592 else
2593 l_st_overlap_date1 := l_prev_int_date;
2594 end if;
2595
2596 if l_emp_leaving_date > l_prev_leaving_date then
2597 l_st_overlap_date2 := l_prev_leaving_date;
2598 else
2599 l_st_overlap_date2 := l_emp_leaving_date;
2600 end if;
2601
2602 if ceil(months_between(l_st_overlap_date2,l_st_overlap_date1)/12) <= 0 then
2603 l_st_overlap_period := 0;
2604 else
2605 l_st_overlap_period := ceil(months_between(l_st_overlap_date2,l_st_overlap_date1)/12);
2606 end if;
2607
2608 -- Call the function to calculate the Statutory Separation Pay Tax
2609 -- for the previous employer
2610 l_dummy := SepPayTaxCalc(
2611 ceil(months_between(l_prev_leaving_date,l_prev_int_date)/12),
2612 l_st_overlap_period,
2613 l_taxable_earnings,
2614 l_sep_taxable_earnings,
2615 l_receivable_sep_pay,
2616 l_sep_tax_conversion_reqd,
2617 p_business_group_id,
2618 p_date_earned,
2619 p_sep_pay_income_exem_rate);
2620
2621 p_sep_calc_tax := p_sep_calc_tax + l_dummy;
2622
2623 end loop;
2624 --
2625 return 0;
2626
2627 elsif p_class = 2 then /* if we have to return the total tax for the eligible working places */
2628 --
2629 hr_utility.trace('Inside the loop to return the Calculated tax for the Eligible Working places ....class 2');
2630 --
2631 l_taxable_earnings := 0;
2632 l_sep_taxable_earnings := 0;
2633 l_receivable_sep_pay := 0;
2634 p_sep_calc_tax := 0;
2635 l_element_entry_id := 0;
2636 l_total_eligible_earnings := 0;
2637 l_dummy := 0;
2638 l_emp_hire_date := null;
2639 l_emp_leaving_date := null;
2640 l_prev_hire_date := null;
2641 l_prev_int_date := null;
2642 l_prev_leaving_date := null;
2643 l_st_curr_max_lim := 0;
2644 l_st_prev_max_lim := 0;
2645 -- Bug 8525925
2646 l_sep_tax_conversion_reqd := null;
2647 l_total_lump_sum_amount := 0 ;
2648
2649 -- For Current Employer
2650 if p_emp_eligibility_flag = 'Y' then
2651 l_taxable_earnings := p_total_taxable_earnings;
2652 l_sep_taxable_earnings := p_total_taxable_earnings;
2653 end if;
2654
2655 -- Bug 8525925
2656 -- If Tax Conversion is required then calculate the receivable_sep_pay and
2657 -- modify the taxable earnings
2658 if p_amount_expected > 0 then
2659 l_sep_tax_conversion_reqd := 'Y';
2660 -- Bug 8996756
2661 if p_principal_interest > 0 then
2662 l_total_lump_sum_amount := p_amount_expected *
2663 ( 1 - (p_personal_contribution - p_pension_exemption) / p_principal_interest );
2664 else
2665 l_total_lump_sum_amount := p_amount_expected;
2666 end if;
2667 -- End of Bug 8996756
2668 l_total_lump_sum_amount := greatest(0,trunc(l_total_lump_sum_amount)) ;
2669 l_receivable_sep_pay := l_taxable_earnings - p_sep_lump_sum_amount + l_total_lump_sum_amount;
2670 l_taxable_earnings := greatest(trunc(l_receivable_sep_pay),0);
2671 end if;
2672
2673 l_emp_hire_date := p_st_emp_hire_date;
2674 l_emp_leaving_date := p_st_emp_leaving_date;
2675
2676 -- Call the function to calculate the Statutory Separation Pay Tax
2677 -- for the current employer
2678 p_sep_calc_tax := SepPayTaxCalc(
2679 ceil(months_between(l_emp_leaving_date,l_emp_hire_date)/12),
2680 0,
2681 l_taxable_earnings,
2682 l_sep_taxable_earnings,
2683 l_receivable_sep_pay,
2684 l_sep_tax_conversion_reqd,
2685 p_business_group_id,
2686 p_date_earned,
2687 p_sep_pay_income_exem_rate);
2688
2689 if p_emp_eligibility_flag = 'Y' then
2690 l_st_curr_max_lim := ceil(months_between(l_emp_leaving_date,l_emp_hire_date)/12) * p_sep_max_post_tax_deduc;
2691 end if;
2692
2693 if (l_st_curr_max_lim - l_st_prev_max_lim) > 0 then
2694 p_st_max_lim := l_st_curr_max_lim;
2695 else
2696 p_st_max_lim := l_st_prev_max_lim;
2697 end if;
2698
2699 -- For Previous Employers
2700 l_taxable_earnings := 0;
2701 l_sep_taxable_earnings := 0;
2702 l_receivable_sep_pay := 0;
2703 l_dummy := 0;
2704
2705
2706 for i in get_eligible_earnings(p_date_earned, p_assignment_id) loop
2707 /* Bug 8525925 */
2708 l_sep_tax_conversion_reqd := null;
2709 prev_principal_interest := 0;
2710 prev_pension_exemption := 0;
2711 prev_personal_contibution := 0;
2712 prev_amt_exp_stat_sep := 0;
2713 prev_total_lump_sum_amount := 0;
2714 prev_total_received := 0;
2715 prev_sep_lump_sum_amt := 0;
2716 l_count := 0;
2717
2718 for j in get_prev_stat_sp_earn(p_date_earned, p_assignment_id, i.element_entry_id) loop
2719 l_taxable_earnings := j.prev_earnings;
2720 l_taxable_earnings := nvl(l_taxable_earnings,0);
2721 l_sep_taxable_earnings := l_taxable_earnings;
2722 for k in get_prev_bus_reg_num_sep(p_date_earned, p_assignment_id,i.element_entry_id) loop
2723
2724 open csr_prev_lsa(k.element_entry_id,p_assignment_action_id);
2725 fetch csr_prev_lsa into prev_sep_lump_sum_amt;
2726 close csr_prev_lsa;
2727 l_taxable_earnings := l_taxable_earnings + nvl(prev_sep_lump_sum_amt,0);
2728
2729 for m in get_prev_emp_amt (p_date_earned, p_assignment_id,k.element_entry_id) loop
2730
2731 if l_count = 0 then
2732 prev_amt_nonstat_sep := m.entry_value;
2733 l_count := l_count + 1;
2734 elsif l_count = 1 then
2735 prev_amt_exp_stat_sep := m.entry_value;
2736 l_count := l_count + 1;
2737 elsif l_count = 2 then
2738 prev_pension_exemption := m.entry_value;
2739 l_count := l_count + 1;
2740 elsif l_count = 3 then
2741 prev_personal_contibution := m.entry_value;
2742 l_count := l_count + 1;
2743 elsif l_count = 4 then
2744 prev_principal_interest := m.entry_value;
2745 l_count := l_count + 1;
2746 elsif l_count = 5 then
2747 prev_total_received := m.entry_value;
2748 end if;
2749
2750 end loop;
2751 end loop;
2752
2753 -- If Tax Conversion is required then calculate the receivable_sep_pay and
2754 -- modify the taxable earnings
2755 if (nvl(prev_amt_exp_stat_sep,0) > 0 and nvl(prev_total_received,0) >0) then
2756
2757 l_sep_tax_conversion_reqd := 'Y';
2758 if prev_principal_interest >0 then
2759 prev_total_lump_sum_amount := nvl(prev_amt_exp_stat_sep,0) *
2760 ( 1 - (nvl(prev_personal_contibution,0) - nvl(prev_pension_exemption,0)) / prev_principal_interest);
2761 else
2762 prev_total_lump_sum_amount := nvl(prev_amt_exp_stat_sep,0);
2763 end if;
2764 prev_total_lump_sum_amount := greatest(0,trunc(prev_total_lump_sum_amount));
2765 l_receivable_sep_pay := l_taxable_earnings + prev_total_lump_sum_amount - p_prev_sep_lump_sum_amt;
2766 l_taxable_earnings := greatest(l_receivable_sep_pay,0);
2767 elsif nvl(prev_amt_exp_stat_sep,0) > 0 then
2768 l_sep_tax_conversion_reqd := 'Y';
2769 prev_total_lump_sum_amount := greatest(0,trunc(prev_amt_exp_stat_sep));
2770 l_receivable_sep_pay := l_taxable_earnings + prev_total_lump_sum_amount - p_prev_sep_lump_sum_amt;
2771 l_taxable_earnings := greatest(l_receivable_sep_pay,0);
2772 end if;
2773
2774 /* End of Bug 8525925 */
2775
2776 l_prev_hire_date := null;
2777 l_prev_int_date := null;
2778 l_prev_leaving_date := null;
2779 l_st_overlap_date1 := null;
2780 l_st_overlap_date2 := null;
2781 l_st_overlap_period := 0;
2782 l_count := 0;
2783
2784 -- Loop to fetch the date for the previous employer to calculate the Service Period and Overlap Periods
2785 for prev in get_prev_hire_leave_dt(p_date_earned, p_assignment_id, i.element_entry_id) loop
2786 if l_count = 0 then
2787 l_prev_int_date := prev.dt_value;
2788 l_count := l_count + 1;
2789 elsif l_count = 1 then
2790 l_prev_hire_date:= prev.dt_value;
2791 l_count := l_count + 1;
2792 elsif l_count = 2 then
2793 l_prev_leaving_date := prev.dt_value;
2794 l_count := l_count + 1;
2795 end if;
2796
2797 end loop;
2798
2799 if l_prev_int_date is null then
2800 l_prev_int_date := l_prev_hire_date;
2801 end if;
2802
2803 if (l_prev_hire_date is null) or (l_prev_leaving_date is null) then
2804 fnd_message.set_name('PAY','PAY_KR_PREV_SEP_DATE_REQ');
2805 fnd_message.raise_error;
2806 end if;
2807
2808 if l_emp_hire_date > l_prev_int_date then
2809 l_st_overlap_date1 := l_emp_hire_date;
2810 else
2811 l_st_overlap_date1 := l_prev_int_date;
2812 end if;
2813
2814 if l_emp_leaving_date > l_prev_leaving_date then
2815 l_st_overlap_date2 := l_prev_leaving_date;
2816 else
2817 l_st_overlap_date2 := l_emp_leaving_date;
2818 end if;
2819
2820 if ceil(months_between(l_st_overlap_date2,l_st_overlap_date1)/12) <= 0 then
2821 l_st_overlap_period := 0;
2822 else
2823 l_st_overlap_period := ceil(months_between(l_st_overlap_date2,l_st_overlap_date1)/12);
2824 end if;
2825
2826 -- Call the function to calculate the Statutory Separation Pay Tax
2827 -- for the previous employer
2828 l_dummy := SepPayTaxCalc(
2829 ceil(months_between(l_prev_leaving_date,l_prev_int_date)/12),
2830 l_st_overlap_period,
2831 l_taxable_earnings,
2832 l_sep_taxable_earnings,
2833 l_receivable_sep_pay,
2834 l_sep_tax_conversion_reqd,
2835 p_business_group_id,
2836 p_date_earned,
2837 p_sep_pay_income_exem_rate);
2838
2839 -- Code to calculate the maximum limit for the Statutory Separation Pay Post Tax deduction
2840 l_st_prev_max_lim := ceil(months_between(l_prev_leaving_date,l_prev_int_date)/12) * p_sep_max_post_tax_deduc;
2841
2842 if (l_st_curr_max_lim - l_st_prev_max_lim) > 0 then
2843 p_st_max_lim := l_st_curr_max_lim;
2844 else
2845 p_st_max_lim := l_st_prev_max_lim;
2846 end if;
2847
2848 p_sep_calc_tax := p_sep_calc_tax + l_dummy;
2849
2850 end loop;
2851
2852 end loop;
2853
2854 hr_utility.trace('Total Calculated Tax for the Eligible Earnings for Post Tax = '||p_sep_calc_tax);
2855
2856 return 0;
2857
2858 end if;
2859
2860 elsif p_sep_cal_mode = 'NON_STAT_SEP_PAY' then
2861 --
2862 hr_utility.trace('Inside the Non-Statutory Separation Pay Process...');
2863 --
2864 if p_class = 1 then /* if we have to return the total tax for the individual working places */
2865 --
2866 hr_utility.trace('Inside the loop to return the Individual Calculated tax for all working places...');
2867 --
2868 l_taxable_earnings := 0;
2869 l_sep_taxable_earnings := 0;
2870 l_receivable_sep_pay := 0;
2871 p_sep_calc_tax := 0;
2872 p_nst_sep_calc_tax := 0;
2873 l_sep_calc_tax := 0;
2874 l_nst_sep_calc_tax := 0;
2875 l_nst_taxable_earnings := 0;
2876 l_dummy := 0;
2877 l_emp_hire_date := null;
2878 l_emp_leaving_date := null;
2879 l_nst_emp_hire_date := null;
2880 l_nst_emp_leaving_date := null;
2881 l_prev_hire_date := null;
2882 l_prev_int_date := null;
2883 l_prev_leaving_date := null;
2884 -- Bug 8525925
2885 l_nst_receivable_sep_pay := 0;
2886 l_nst_sep_taxable_earnings := 0;
2887 l_sep_tax_conversion_reqd := null;
2888 l_total_lump_sum_amount := 0 ;
2889
2890 -- For Current Employer
2891 l_nst_taxable_earnings := p_nst_taxable_earnings;
2892 l_nst_sep_taxable_earnings := p_nst_taxable_earnings;
2893 l_taxable_earnings := p_total_taxable_earnings;
2894 l_sep_taxable_earnings := l_taxable_earnings;
2895
2896 hr_utility.trace('Entering Class 1 current employer');
2897
2898 -- Bug 8525925
2899 -- If Tax Conversion is required then calculate the receivable_sep_pay and
2900 -- modify the taxable earnings
2901 if p_amount_expected > 0 then
2902 l_sep_tax_conversion_reqd := 'Y';
2903 -- Bug 8996756
2904 if p_principal_interest > 0 then
2905 l_total_lump_sum_amount := p_amount_expected *
2906 ( 1 - (p_personal_contribution - p_pension_exemption) / p_principal_interest );
2907 else
2908 l_total_lump_sum_amount := p_amount_expected;
2909 end if;
2910 -- End of Bug 8996756
2911 l_total_lump_sum_amount := greatest(0,trunc(l_total_lump_sum_amount)) ;
2912 l_receivable_sep_pay := l_taxable_earnings - p_sep_lump_sum_amount + l_total_lump_sum_amount;
2913 l_taxable_earnings := greatest(trunc(l_receivable_sep_pay),0);
2914 end if;
2915
2916 if p_nst_amount_expected > 0 then
2917 l_nst_receivable_sep_pay := l_nst_taxable_earnings + p_nst_amount_expected ;
2918 l_nst_taxable_earnings := greatest(l_nst_receivable_sep_pay,0);
2919 end if;
2920
2921 -- End of Bug 8525925
2922 l_emp_hire_date := p_st_emp_hire_date;
2923 l_emp_leaving_date := p_st_emp_leaving_date;
2924 l_nst_emp_hire_date := p_nst_emp_hire_date;
2925 l_nst_emp_leaving_date := p_nst_emp_leaving_date;
2926
2927 NonStatTaxCalc(
2928 ceil(months_between(l_emp_leaving_date,l_emp_hire_date)/12),
2929 ceil(months_between(l_nst_emp_leaving_date,l_nst_emp_hire_date)/12),
2930 0,
2931 0,
2932 l_sep_taxable_earnings ,
2933 l_taxable_earnings ,
2934 l_nst_taxable_earnings ,
2935 p_wkpd_int_sep_pay ,
2936 l_sep_tax_conversion_reqd ,
2937 l_receivable_sep_pay ,
2938 p_date_earned ,
2939 p_business_group_id ,
2940 p_sep_pay_income_exem_rate,
2941 l_nst_sep_calc_tax ,
2942 l_sep_calc_tax ,
2943 l_nst_receivable_sep_pay,
2944 l_nst_sep_taxable_earnings);
2945
2946 p_sep_calc_tax := p_sep_calc_tax + l_sep_calc_tax;
2947 p_nst_sep_calc_tax := p_nst_sep_calc_tax + l_nst_sep_calc_tax;
2948 --
2949 --
2950 -- For Previous Employers
2951 l_element_entry_id := null;
2952 l_taxable_earnings := 0;
2953 l_sep_taxable_earnings := 0;
2954 l_receivable_sep_pay := 0;
2955 l_nst_taxable_earnings := 0;
2956 l_nst_sep_calc_tax := 0;
2957 l_sep_calc_tax := 0;
2958
2959 hr_utility.trace('Entering Class 1 Previous employer.......');
2960
2961 for i in get_prev_stat_sp_earn(p_date_earned, p_assignment_id, l_element_entry_id) loop
2962
2963 /* Bug 8525925 */
2964 l_sep_tax_conversion_reqd := null;
2965 prev_principal_interest := 0;
2966 prev_pension_exemption := 0;
2967 prev_personal_contibution := 0;
2968 prev_amt_exp_stat_sep := 0;
2969 prev_total_lump_sum_amount := 0;
2970 prev_amt_nonstat_sep := 0;
2971 prev_total_received := 0;
2972 l_nst_receivable_sep_pay := 0;
2973 l_nst_sep_taxable_earnings := 0;
2974 l_count := 0;
2975 prev_sep_lump_sum_amt := 0;
2976
2977 l_taxable_earnings := i.prev_earnings ;
2978 l_taxable_earnings := nvl(l_taxable_earnings,0);
2979
2980 for j in get_prev_non_stat_sp_earn(p_date_earned, p_assignment_id, i.element_entry_id) loop
2981
2982 l_nst_taxable_earnings := j.prev_earnings;
2983 l_nst_sep_taxable_earnings := j.prev_earnings; -- Bug 8525925
2984
2985 end loop;
2986 /* Start of Bug 8525925 */
2987 for k in get_prev_bus_reg_num_sep(p_date_earned, p_assignment_id,i.element_entry_id) loop
2988
2989 open csr_prev_lsa(k.element_entry_id,p_assignment_action_id);
2990 fetch csr_prev_lsa into prev_sep_lump_sum_amt;
2991 close csr_prev_lsa;
2992 l_taxable_earnings := l_taxable_earnings + nvl(prev_sep_lump_sum_amt,0);
2993
2994 for m in get_prev_emp_amt (p_date_earned, p_assignment_id,k.element_entry_id) loop
2995
2996 if l_count = 0 then
2997 prev_amt_nonstat_sep := m.entry_value;
2998 l_count := l_count + 1;
2999 elsif l_count = 1 then
3000 prev_amt_exp_stat_sep := m.entry_value;
3001 l_count := l_count + 1;
3002 elsif l_count = 2 then
3003 prev_pension_exemption := m.entry_value;
3004 l_count := l_count + 1;
3005 elsif l_count = 3 then
3006 prev_personal_contibution := m.entry_value;
3007 l_count := l_count + 1;
3008 elsif l_count = 4 then
3009 prev_principal_interest := m.entry_value;
3010 l_count := l_count + 1;
3011 elsif l_count = 5 then
3012 prev_total_received := m.entry_value;
3013 end if;
3014 end loop;
3015
3016 end loop;
3017
3018 l_taxable_earnings := l_taxable_earnings - l_nst_taxable_earnings;
3019 l_sep_taxable_earnings := l_taxable_earnings;
3020
3021 -- If Tax Conversion is required then calculate the receivable_sep_pay and
3022 -- modify the taxable earnings
3023 if (nvl(prev_amt_exp_stat_sep,0) > 0 and nvl(prev_total_received,0) > 0) then
3024
3025 l_sep_tax_conversion_reqd := 'Y';
3026 if prev_principal_interest > 0 then
3027 prev_total_lump_sum_amount := nvl(prev_amt_exp_stat_sep,0) *
3028 ( 1 - (nvl(prev_personal_contibution,0) - nvl(prev_pension_exemption,0)) / prev_principal_interest);
3029 else
3030 prev_total_lump_sum_amount := nvl(prev_amt_exp_stat_sep,0);
3031 end if;
3032 prev_total_lump_sum_amount := greatest(0,trunc(prev_total_lump_sum_amount));
3033 l_receivable_sep_pay := l_taxable_earnings + prev_total_lump_sum_amount - p_prev_sep_lump_sum_amt;
3034 l_taxable_earnings := greatest(l_receivable_sep_pay,0);
3035
3036 elsif nvl(prev_amt_exp_stat_sep,0) > 0 then
3037 l_sep_tax_conversion_reqd := 'Y';
3038 prev_total_lump_sum_amount := greatest(0,trunc(prev_amt_exp_stat_sep));
3039 l_receivable_sep_pay := l_taxable_earnings + prev_total_lump_sum_amount - p_prev_sep_lump_sum_amt;
3040 l_taxable_earnings := greatest(l_receivable_sep_pay,0);
3041 end if;
3042
3043 if (nvl(prev_amt_nonstat_sep,0) > 0 ) then
3044 l_nst_receivable_sep_pay := l_nst_taxable_earnings + prev_amt_nonstat_sep ;
3045 l_nst_taxable_earnings := greatest(l_nst_receivable_sep_pay,0);
3046 end if;
3047
3048 /* End of Bug 8525925 */
3049 l_prev_hire_date := null;
3050 l_prev_int_date := null;
3051 l_prev_leaving_date := null;
3052 l_st_overlap_date1 := null;
3053 l_st_overlap_date2 := null;
3054 l_nst_overlap_date1 := null;
3055 l_nst_overlap_date2 := null;
3056 l_st_overlap_period := 0;
3057 l_nst_overlap_period := 0;
3058 l_count := 0;
3059
3060 -- Loop to fetch the dates for the previous employer to calculate the Service Period and Overlap Periods
3061 for prev in get_prev_hire_leave_dt(p_date_earned, p_assignment_id, i.element_entry_id) loop
3062 if l_count = 0 then
3063 l_prev_int_date := prev.dt_value;
3064 l_count := l_count + 1;
3065 elsif l_count = 1 then
3066 l_prev_hire_date:= prev.dt_value;
3067 l_count := l_count + 1;
3068 elsif l_count = 2 then
3069 l_prev_leaving_date := prev.dt_value;
3070 l_count := l_count + 1;
3071 end if;
3072
3073 end loop;
3074
3075 if l_prev_int_date is null then
3076 l_prev_int_date := l_prev_hire_date;
3077 end if;
3078
3079 if (l_prev_hire_date is null) or (l_prev_leaving_date is null) then
3080 fnd_message.set_name('PAY','PAY_KR_PREV_SEP_DATE_REQ');
3081 fnd_message.raise_error;
3082 end if;
3083
3084 if l_emp_hire_date > l_prev_int_date then
3085 l_st_overlap_date1 := l_emp_hire_date;
3086 else
3087 l_st_overlap_date1 := l_prev_int_date;
3088 end if;
3089
3090 if l_emp_leaving_date > l_prev_leaving_date then
3091 l_st_overlap_date2 := l_prev_leaving_date;
3092 else
3093 l_st_overlap_date2 := l_emp_leaving_date;
3094 end if;
3095
3096 if ceil(months_between(l_st_overlap_date2,l_st_overlap_date1)/12) <= 0 then
3097 l_st_overlap_period := 0;
3098 else
3099 l_st_overlap_period := ceil(months_between(l_st_overlap_date2,l_st_overlap_date1)/12);
3100 end if;
3101
3102 if l_nst_emp_hire_date > l_prev_hire_date then
3103 l_nst_overlap_date1 := l_nst_emp_hire_date;
3104 else
3105 l_nst_overlap_date1 := l_prev_hire_date;
3106 end if;
3107
3108 if l_nst_emp_leaving_date > l_prev_leaving_date then
3109 l_nst_overlap_date2 := l_prev_leaving_date;
3110 else
3111 l_nst_overlap_date2 := l_nst_emp_leaving_date;
3112 end if;
3113
3114 if ceil(months_between(l_nst_overlap_date2,l_nst_overlap_date1)/12) <= 0 then
3115 l_nst_overlap_period := 0;
3116 else
3117 l_nst_overlap_period := ceil(months_between(l_nst_overlap_date2,l_nst_overlap_date1)/12);
3118 end if;
3119
3120 NonStatTaxCalc(
3121 ceil(months_between(l_prev_leaving_date,l_prev_int_date)/12),
3122 ceil(months_between(l_prev_leaving_date,l_prev_hire_date)/12),
3123 l_st_overlap_period ,
3124 l_nst_overlap_period ,
3125 l_sep_taxable_earnings ,
3126 l_taxable_earnings ,
3127 l_nst_taxable_earnings ,
3128 p_wkpd_int_sep_pay ,
3129 l_sep_tax_conversion_reqd ,
3130 l_receivable_sep_pay ,
3131 p_date_earned ,
3132 p_business_group_id ,
3133 p_sep_pay_income_exem_rate,
3134 l_nst_sep_calc_tax ,
3135 l_sep_calc_tax,
3136 l_nst_receivable_sep_pay,
3137 l_nst_sep_taxable_earnings
3138 );
3139
3140 p_sep_calc_tax := p_sep_calc_tax + l_sep_calc_tax;
3141 p_nst_sep_calc_tax := p_nst_sep_calc_tax + l_nst_sep_calc_tax;
3142 --
3143 end loop;
3144 return 0;
3145
3146 elsif p_class = 2 then /* if we have to return the total tax for the eligible working places */
3147 --
3148 hr_utility.trace('Inside the loop to return the Calculated tax for the Eligible Working places ....');
3149 --
3150 l_taxable_earnings := 0;
3151 l_sep_taxable_earnings := 0;
3152 l_receivable_sep_pay := 0;
3153 p_sep_calc_tax := 0;
3154 p_nst_sep_calc_tax := 0;
3155 l_element_entry_id := 0;
3156 l_nst_prev_earnings := 0;
3157 l_nst_taxable_earnings := 0;
3158 l_sep_calc_tax := 0;
3159 l_nst_sep_calc_tax := 0;
3160 l_curr_max_lim := 0;
3161 l_prev_max_lim := 0;
3162 l_st_curr_max_lim := 0;
3163 l_nst_curr_max_lim := 0;
3164 l_st_prev_max_lim := 0;
3165 l_nst_prev_max_lim := 0;
3166 -- Bug 8525925
3167 l_nst_receivable_sep_pay := 0;
3168 l_nst_sep_taxable_earnings := 0;
3169 l_sep_tax_conversion_reqd := null;
3170
3171 -- For Current Employer
3172 if p_emp_eligibility_flag = 'Y' then
3173 l_nst_taxable_earnings := p_nst_taxable_earnings;
3174 l_nst_sep_taxable_earnings := p_nst_taxable_earnings;
3175 l_taxable_earnings := p_total_taxable_earnings;
3176 l_sep_taxable_earnings := l_taxable_earnings;
3177 end if;
3178
3179 -- Bug 8525925
3180 -- If Tax Conversion is required then calculate the receivable_sep_pay and
3181 -- modify the taxable earnings
3182 if p_amount_expected > 0 then
3183 l_sep_tax_conversion_reqd := 'Y';
3184 -- Bug 8996756
3185 if p_principal_interest > 0 then
3186 l_total_lump_sum_amount := p_amount_expected *
3187 ( 1 - (p_personal_contribution - p_pension_exemption) / p_principal_interest );
3188 else
3189 l_total_lump_sum_amount := p_amount_expected;
3190 end if;
3191 -- End of Bug 8996756
3192 l_total_lump_sum_amount := greatest(0,trunc(l_total_lump_sum_amount)) ;
3193 l_receivable_sep_pay := l_taxable_earnings - p_sep_lump_sum_amount + l_total_lump_sum_amount;
3194 l_taxable_earnings := greatest(trunc(l_receivable_sep_pay),0);
3195 end if;
3196
3197 if p_nst_amount_expected > 0 then
3198 l_nst_receivable_sep_pay := l_nst_taxable_earnings + p_nst_amount_expected ;
3199 l_nst_taxable_earnings := greatest(l_nst_receivable_sep_pay,0);
3200 end if;
3201
3202 -- End of Bug 8525925
3203
3204 l_emp_hire_date := p_st_emp_hire_date;
3205 l_emp_leaving_date := p_st_emp_leaving_date;
3206 l_nst_emp_hire_date := p_nst_emp_hire_date;
3207 l_nst_emp_leaving_date := p_nst_emp_leaving_date;
3208
3209
3210 NonStatTaxCalc(
3211 ceil(months_between(l_emp_leaving_date,l_emp_hire_date)/12),
3212 ceil(months_between(l_nst_emp_leaving_date,l_nst_emp_hire_date)/12),
3213 0,
3214 0,
3215 l_sep_taxable_earnings ,
3216 l_taxable_earnings ,
3217 l_nst_taxable_earnings ,
3218 p_wkpd_int_sep_pay ,
3219 l_sep_tax_conversion_reqd ,
3220 l_receivable_sep_pay ,
3221 p_date_earned ,
3222 p_business_group_id ,
3223 p_sep_pay_income_exem_rate,
3224 l_nst_sep_calc_tax ,
3225 l_sep_calc_tax ,
3226 l_nst_receivable_sep_pay,
3227 l_nst_sep_taxable_earnings);
3228
3229 p_sep_calc_tax := p_sep_calc_tax + l_sep_calc_tax;
3230 p_nst_sep_calc_tax := p_nst_sep_calc_tax + l_nst_sep_calc_tax;
3231 --
3232 -- Code to calculate the maximum limits for the Non-Statutory Process
3233 if p_emp_eligibility_flag = 'Y' then
3234 l_st_curr_max_lim := ceil(months_between(l_emp_leaving_date,l_emp_hire_date)/12) * p_sep_max_post_tax_deduc;
3235 l_nst_curr_max_lim := ceil(months_between(l_nst_emp_leaving_date,l_nst_emp_hire_date)/12) * p_sep_max_post_tax_deduc;
3236 l_curr_max_lim := greatest(l_st_curr_max_lim,l_nst_curr_max_lim);
3237 end if;
3238
3239 if (l_curr_max_lim - l_prev_max_lim) > 0 then
3240 p_st_max_lim := l_st_curr_max_lim;
3241 p_nst_max_lim := l_curr_max_lim - l_st_curr_max_lim;
3242 else
3243 p_st_max_lim := l_st_prev_max_lim;
3244 p_nst_max_lim := l_nst_prev_max_lim;
3245 end if;
3246 --
3247 -- For Previous Employers
3248 l_element_entry_id := null;
3249 l_taxable_earnings := 0;
3250 l_sep_taxable_earnings := 0;
3251 l_receivable_sep_pay := 0;
3252 l_nst_taxable_earnings := 0;
3253 l_nst_sep_calc_tax := 0;
3254 l_sep_calc_tax := 0;
3255
3256
3257 hr_utility.trace('Entering Class 2 for previous employer');
3258
3259 for i in get_eligible_earnings(p_date_earned, p_assignment_id) loop
3260 /* Bug 8525925 */
3261 l_sep_tax_conversion_reqd := null;
3262 prev_principal_interest := 0;
3263 prev_pension_exemption := 0;
3264 prev_personal_contibution := 0;
3265 prev_amt_exp_stat_sep := 0;
3266 prev_total_lump_sum_amount := 0;
3267 prev_amt_nonstat_sep := 0;
3268 prev_total_received := 0;
3269 l_nst_receivable_sep_pay := 0;
3270 l_nst_sep_taxable_earnings := 0;
3271 l_count := 0;
3272 prev_sep_lump_sum_amt := 0;
3273
3274 for j in get_prev_stat_sp_earn(p_date_earned, p_assignment_id, i.element_entry_id) loop
3275
3276 l_taxable_earnings := j.prev_earnings ;
3277 l_taxable_earnings := nvl(l_taxable_earnings,0);
3278
3279 for k in get_prev_non_stat_sp_earn(p_date_earned, p_assignment_id, i.element_entry_id) loop
3280
3281 l_nst_taxable_earnings := k.prev_earnings;
3282 l_nst_sep_taxable_earnings := k.prev_earnings;
3283 end loop;
3284 /* Start of Bug 8525925 */
3285 for m in get_prev_bus_reg_num_sep(p_date_earned, p_assignment_id,i.element_entry_id) loop
3286
3287 open csr_prev_lsa(m.element_entry_id,p_assignment_action_id);
3288 fetch csr_prev_lsa into prev_sep_lump_sum_amt;
3289 close csr_prev_lsa;
3290 l_taxable_earnings := l_taxable_earnings + nvl(prev_sep_lump_sum_amt,0);
3291
3292 for n in get_prev_emp_amt (p_date_earned, p_assignment_id,m.element_entry_id) loop
3293
3294 if l_count = 0 then
3295 prev_amt_nonstat_sep := n.entry_value;
3296 l_count := l_count + 1;
3297 elsif l_count = 1 then
3298 prev_amt_exp_stat_sep := n.entry_value;
3299 l_count := l_count + 1;
3300 elsif l_count = 2 then
3301 prev_pension_exemption := n.entry_value;
3302 l_count := l_count + 1;
3303 elsif l_count = 3 then
3304 prev_personal_contibution := n.entry_value;
3305 l_count := l_count + 1;
3306 elsif l_count = 4 then
3307 prev_principal_interest := n.entry_value;
3308 l_count := l_count + 1;
3309 elsif l_count = 5 then
3310 prev_total_received := n.entry_value;
3311 end if;
3312
3313 end loop;
3314
3315 end loop;
3316
3317
3318 l_taxable_earnings := l_taxable_earnings - l_nst_taxable_earnings;
3319 l_sep_taxable_earnings := l_taxable_earnings;
3320
3321 -- If Tax Conversion is required then calculate the receivable_sep_pay and
3322 -- modify the taxable earnings
3323 if (nvl(prev_amt_exp_stat_sep,0) > 0 and nvl(prev_total_received,0) > 0) then
3324
3325 l_sep_tax_conversion_reqd := 'Y';
3326 if prev_principal_interest > 0 then
3327 prev_total_lump_sum_amount := nvl(prev_amt_exp_stat_sep,0) *
3328 ( 1 - (nvl(prev_personal_contibution,0) - nvl(prev_pension_exemption,0)) / prev_principal_interest);
3329 else
3330 prev_total_lump_sum_amount := nvl(prev_amt_exp_stat_sep,0);
3331 end if;
3332 prev_total_lump_sum_amount := greatest(0,trunc(prev_total_lump_sum_amount));
3333 l_receivable_sep_pay := l_taxable_earnings + prev_total_lump_sum_amount - p_prev_sep_lump_sum_amt;
3334 l_taxable_earnings := greatest(l_receivable_sep_pay,0);
3335
3336 elsif nvl(prev_amt_exp_stat_sep,0) > 0 then
3337 l_sep_tax_conversion_reqd := 'Y';
3338 prev_total_lump_sum_amount := greatest(0,trunc(prev_amt_exp_stat_sep));
3339 l_receivable_sep_pay := l_taxable_earnings + prev_total_lump_sum_amount - p_prev_sep_lump_sum_amt;
3340 l_taxable_earnings := greatest(l_receivable_sep_pay,0);
3341
3342 end if;
3343
3344 if (nvl(prev_amt_nonstat_sep,0) > 0 ) then
3345 l_nst_receivable_sep_pay := l_nst_taxable_earnings + prev_amt_nonstat_sep ;
3346 l_nst_taxable_earnings := greatest(l_nst_receivable_sep_pay,0);
3347 end if;
3348
3349 /* End of Bug 8525925 */
3350
3351 l_prev_hire_date := null;
3352 l_prev_int_date := null;
3353 l_prev_leaving_date := null;
3354 l_st_overlap_date1 := null;
3355 l_st_overlap_date2 := null;
3356 l_nst_overlap_date1 := null;
3357 l_nst_overlap_date2 := null;
3358 l_st_overlap_period := 0;
3359 l_nst_overlap_period := 0;
3360 l_count := 0;
3361
3362 -- Loop to fetch the dates for the previous employer to calculate the Service Period
3363 for prev in get_prev_hire_leave_dt(p_date_earned, p_assignment_id, i.element_entry_id) loop
3364 if l_count = 0 then
3365 l_prev_int_date := prev.dt_value;
3366 l_count := l_count + 1;
3367 elsif l_count = 1 then
3368 l_prev_hire_date:= prev.dt_value;
3369 l_count := l_count + 1;
3370 elsif l_count = 2 then
3371 l_prev_leaving_date := prev.dt_value;
3372 l_count := l_count + 1;
3373 end if;
3374
3375 end loop;
3376
3377 if l_prev_int_date is null then
3378 l_prev_int_date := l_prev_hire_date;
3379 end if;
3380
3381 if (l_prev_hire_date is null) or (l_prev_leaving_date is null) then
3382 fnd_message.set_name('PAY','PAY_KR_PREV_SEP_DATE_REQ');
3383 fnd_message.raise_error;
3384 end if;
3385
3386 if l_emp_hire_date > l_prev_int_date then
3387 l_st_overlap_date1 := l_emp_hire_date;
3388 else
3389 l_st_overlap_date1 := l_prev_int_date;
3390 end if;
3391
3392 if l_emp_leaving_date > l_prev_leaving_date then
3393 l_st_overlap_date2 := l_prev_leaving_date;
3394 else
3395 l_st_overlap_date2 := l_emp_leaving_date;
3396 end if;
3397
3398 if ceil(months_between(l_st_overlap_date2,l_st_overlap_date1)/12) <= 0 then
3399 l_st_overlap_period := 0;
3400 else
3401 l_st_overlap_period := ceil(months_between(l_st_overlap_date2,l_st_overlap_date1)/12);
3402 end if;
3403
3404 if l_nst_emp_hire_date > l_prev_hire_date then
3405 l_nst_overlap_date1 := l_nst_emp_hire_date;
3406 else
3407 l_nst_overlap_date1 := l_prev_hire_date;
3408 end if;
3409
3410 if l_nst_emp_leaving_date > l_prev_leaving_date then
3411 l_nst_overlap_date2 := l_prev_leaving_date;
3412 else
3413 l_nst_overlap_date2 := l_nst_emp_leaving_date;
3414 end if;
3415
3416 if ceil(months_between(l_nst_overlap_date2,l_nst_overlap_date1)/12) <= 0 then
3417 l_nst_overlap_period := 0;
3418 else
3419 l_nst_overlap_period := ceil(months_between(l_nst_overlap_date2,l_nst_overlap_date1)/12);
3420 end if;
3421
3422 NonStatTaxCalc(
3423 ceil(months_between(l_prev_leaving_date,l_prev_int_date)/12),
3424 ceil(months_between(l_prev_leaving_date,l_prev_hire_date)/12),
3425 l_st_overlap_period ,
3426 l_nst_overlap_period ,
3427 l_sep_taxable_earnings ,
3428 l_taxable_earnings ,
3429 l_nst_taxable_earnings ,
3430 p_wkpd_int_sep_pay ,
3431 l_sep_tax_conversion_reqd ,
3432 l_receivable_sep_pay ,
3433 p_date_earned ,
3434 p_business_group_id ,
3435 p_sep_pay_income_exem_rate,
3436 l_nst_sep_calc_tax ,
3437 l_sep_calc_tax,
3438 l_nst_receivable_sep_pay ,
3439 l_nst_sep_taxable_earnings );
3440
3441 p_sep_calc_tax := p_sep_calc_tax + l_sep_calc_tax;
3442 p_nst_sep_calc_tax := p_nst_sep_calc_tax + l_nst_sep_calc_tax;
3443
3444 -- Code to calculate the maximum limits for the Non-Statutory Process
3445 l_st_prev_max_lim := ceil(months_between(l_prev_leaving_date,l_prev_int_date)/12) * p_sep_max_post_tax_deduc;
3446 l_nst_prev_max_lim := ceil(months_between(l_prev_leaving_date,l_prev_hire_date)/12) * p_sep_max_post_tax_deduc;
3447 l_prev_max_lim := greatest(l_st_prev_max_lim,l_nst_prev_max_lim);
3448
3449 if (l_curr_max_lim - l_prev_max_lim) > 0 then
3450 p_st_max_lim := l_st_curr_max_lim;
3451 p_nst_max_lim := l_curr_max_lim - l_st_curr_max_lim;
3452 else
3453 p_st_max_lim := l_st_prev_max_lim;
3454 p_nst_max_lim := l_nst_prev_max_lim;
3455 end if;
3456 --
3457 end loop;
3458
3459 end loop;
3460 --
3461 return 0;
3462
3463 end if;
3464
3465 end if;
3466
3467
3468 exception
3469 when others then
3470 raise;
3471
3472 end;
3473 -----------------------------------------------------------------------------------------------
3474 -- Bug 8466662: This function simulates the Statutory Separation Pay Process and returns the
3475 -- Statutory Calculated Tax value.
3476 -----------------------------------------------------------------------------------------------
3477 function SepPayTaxCalc(
3478 p_service_period in number,
3479 p_overlap_period in number,
3480 p_taxable_earnings in number,
3481 p_sep_taxable_earnings in number,
3482 p_receivable_sep_pay in number,
3483 p_sep_tax_conversion_reqd in varchar2,
3484 p_business_group_id in number,
3485 p_effective_date in date,
3486 p_sep_pay_income_exem_rate in number) return number
3487 is
3488
3489 l_addend number;
3490 l_multiplier number;
3491 l_subtrahend number;
3492 l_svpd_income_exem number;
3493 l_sep_pay_income_exem number;
3494 l_income_exem number;
3495 l_sep_taxation_base number;
3496 l_ytaxation_base number;
3497 l_taxation_base number;
3498 l_ycalc_tax number;
3499 l_calc_tax number;
3500 l_sep_calc_tax number;
3501
3502
3503 begin
3504 l_svpd_income_exem := 0;
3505 if (p_service_period - p_overlap_period) > 0 then
3506 l_addend := to_number(hruserdt.get_table_value(
3507 p_bus_group_id => p_business_group_id,
3508 p_table_name => 'SVPD_INCOME_EXEM',
3509 p_col_name => 'ADDEND',
3510 p_row_value => to_char(p_service_period - p_overlap_period),
3511 p_effective_date => p_effective_date));
3512 l_multiplier := to_number(hruserdt.get_table_value(
3513 p_bus_group_id => p_business_group_id,
3514 p_table_name => 'SVPD_INCOME_EXEM',
3515 p_col_name => 'MULTIPLIER',
3516 p_row_value => to_char(p_service_period - p_overlap_period),
3517 p_effective_date => p_effective_date));
3518 l_subtrahend := to_number(hruserdt.get_table_value(
3519 p_bus_group_id => p_business_group_id,
3520 p_table_name => 'SVPD_INCOME_EXEM',
3521 p_col_name => 'SUBTRAHEND',
3522 p_row_value => to_char(p_service_period - p_overlap_period),
3523 p_effective_date => p_effective_date));
3524 l_svpd_income_exem := l_addend + trunc(l_multiplier * (p_service_period - l_subtrahend));
3525 end if;
3526 l_sep_pay_income_exem := 0;
3527 if p_taxable_earnings > 0 then
3528 l_sep_pay_income_exem := trunc(p_taxable_earnings * p_sep_pay_income_exem_rate/ 100);
3529 end if;
3530 l_income_exem := l_svpd_income_exem + l_sep_pay_income_exem;
3531
3532 /******************/
3533 /* Taxable Income */
3534 /******************/
3535 l_sep_taxation_base := greatest(greatest(p_taxable_earnings, 0) - l_income_exem, 0);
3536 l_ytaxation_base := 0;
3537 if p_service_period >= 1 then
3538 l_ytaxation_base := trunc(l_sep_taxation_base / p_service_period);
3539 end if;
3540 /*****************/
3541 /* Taxation Base */
3542 /*****************/
3543 l_taxation_base := greatest(l_ytaxation_base, 0);
3544 /******************/
3545 /* Calculated Tax */
3546 /******************/
3547 l_ycalc_tax := 0;
3548 l_calc_tax := 0;
3549 l_sep_calc_tax := 0;
3550 if l_taxation_base > 0 then
3551 l_multiplier := to_number(hruserdt.get_table_value(
3552 p_bus_group_id => p_business_group_id,
3553 p_table_name => 'CALC_TAX',
3554 p_col_name => 'MULTIPLIER',
3555 p_row_value => to_char(l_taxation_base),
3556 p_effective_date => p_effective_date));
3557 l_subtrahend := to_number(hruserdt.get_table_value(
3558 p_bus_group_id => p_business_group_id,
3559 p_table_name => 'CALC_TAX',
3560 p_col_name => 'SUBTRAHEND',
3561 p_row_value => to_char(l_taxation_base),
3562 p_effective_date => p_effective_date));
3563 l_ycalc_tax := trunc(l_taxation_base * l_multiplier / 100) - l_subtrahend;
3564 /* To avoid to return calc tax result in other process */
3565 l_calc_tax := l_ycalc_tax;
3566 if p_service_period >= 1 then
3567 l_sep_calc_tax := l_calc_tax * p_service_period;
3568 end if;
3569 end if;
3570
3571 /******************************************/
3572 /* Converted Separation Tax Calculation */
3573 /******************************************/
3574 if p_sep_tax_conversion_reqd = 'Y' and p_receivable_sep_pay > 0 then
3575 /* assign converted tax to o_sep_calc_tax for further processing */
3576 l_sep_calc_tax := trunc(l_ycalc_tax * p_service_period
3577 * (p_sep_taxable_earnings / p_receivable_sep_pay));
3578 end if;
3579 return l_sep_calc_tax;
3580 exception
3581 when others then
3582 raise;
3583
3584 end;
3585 -----------------------------------------------------------------------------------------------
3586 -- Bug 8466662: This procedure simulates the Non-Statutory Separation Pay Process and returns the
3587 -- Statutory and Non-Statutory Calculated Tax values.
3588 -----------------------------------------------------------------------------------------------
3589 Procedure NonStatTaxCalc(
3590 p_service_period in number,
3591 p_nst_service_period in number,
3592 p_st_overlap_period in number,
3593 p_nst_overlap_period in number,
3594 p_sep_taxable_earnings in number,
3595 p_taxable_earnings in number,
3596 p_nst_taxable_earnings in number,
3597 p_wkpd_int_sep_pay in number,
3598 p_sep_tax_conversion_reqd in varchar2,
3599 p_receivable_sep_pay in number,
3600 p_effective_date in date,
3601 p_business_group_id in number,
3602 p_sep_pay_income_exem_rate in number,
3603 l_nst_sep_calc_tax out NOCOPY number,
3604 l_sep_calc_tax out NOCOPY number,
3605 p_nst_receivable_sep_pay in number,
3606 p_nst_sep_taxable_earnings in number)
3607 is
3608
3609 l_addend number;
3610 l_multiplier number;
3611 l_subtrahend number;
3612 l_nst_addend number;
3613 l_nst_multiplier number;
3614 l_nst_subtrahend number;
3615 l_svpd_income_exem number;
3616 l_sep_pay_income_exem number;
3617 l_income_exem number;
3618 l_sep_taxation_base number;
3619 l_ytaxation_base number;
3620 l_taxation_base number;
3621 l_nst_sep_taxable_earnings number;
3622 l_nst_svpd_income_exem number;
3623 l_nst_int_service_period number;
3624 l_wkpd_int_sep_pay number;
3625 l_nst_ent_svpd_income_exem number;
3626 l_nst_int_svpd_income_exem number;
3627 l_nst_st_svpd_income_exem number;
3628 l_nst_st_service_period number;
3629 l_nst_sep_pay_income_exem number;
3630 l_nst_income_exem number;
3631 l_nst_sep_taxation_base number;
3632 l_nst_ytaxation_base number;
3633 l_nst_taxation_base number;
3634 l_total_taxation_base number;
3635 l_total_ycalc_tax number;
3636 l_tot_multiplier number;
3637 l_tot_subtrahend number;
3638 l_ycalc_tax number;
3639 l_nst_ycalc_tax number;
3640 -- Bug 8525925
3641 l_nst_receivable_sep_pay number;
3642
3643 begin
3644 /* 1.Service Period Income Exemption */
3645 /* 2.Separation Pay Income Exemption */
3646 l_svpd_income_exem := 0;
3647
3648
3649 if (p_service_period - p_st_overlap_period) > 0 then
3650 l_addend := to_number(hruserdt.get_table_value(
3651 p_bus_group_id => p_business_group_id,
3652 p_table_name => 'SVPD_INCOME_EXEM',
3653 p_col_name => 'ADDEND',
3654 p_row_value => to_char(p_service_period - p_st_overlap_period),
3655 p_effective_date => p_effective_date));
3656 l_multiplier := to_number(hruserdt.get_table_value(
3657 p_bus_group_id => p_business_group_id,
3658 p_table_name => 'SVPD_INCOME_EXEM',
3659 p_col_name => 'MULTIPLIER',
3660 p_row_value => to_char(p_service_period - p_st_overlap_period),
3661 p_effective_date => p_effective_date));
3662 l_subtrahend := to_number(hruserdt.get_table_value(
3663 p_bus_group_id => p_business_group_id,
3664 p_table_name => 'SVPD_INCOME_EXEM',
3665 p_col_name => 'SUBTRAHEND',
3666 p_row_value => to_char(p_service_period - p_st_overlap_period),
3667 p_effective_date => p_effective_date));
3668 l_svpd_income_exem := l_addend + trunc(l_multiplier * ((p_service_period - p_st_overlap_period) - l_subtrahend));
3669 end if;
3670
3671 l_sep_pay_income_exem := 0;
3672 if p_taxable_earnings > 0 then
3673 l_sep_pay_income_exem := trunc(p_taxable_earnings * p_sep_pay_income_exem_rate/ 100);
3674 end if;
3675 l_income_exem := l_svpd_income_exem + l_sep_pay_income_exem;
3676 /* Taxable Income */
3677
3678 l_sep_taxation_base := greatest(greatest(p_taxable_earnings, 0) - l_income_exem, 0);
3679 l_ytaxation_base := 0;
3680 if p_service_period >= 1 then
3681 l_ytaxation_base := greatest(trunc(l_sep_taxation_base / p_service_period), 0);
3682 end if;
3683 /* To avoid to return taxation base result in other process */
3684 l_taxation_base := l_ytaxation_base;
3685
3686
3687 /* ------------- Non Statutory Sep Pay ------------------*/
3688 -- Bug 8525925
3689 l_nst_sep_taxable_earnings := p_nst_sep_taxable_earnings;
3690 l_nst_receivable_sep_pay := p_nst_receivable_sep_pay;
3691
3692 /* 1.Service Period Income Exemption */
3693 /* 2.Separation Pay Income Exemption */
3694 l_nst_svpd_income_exem := 0;
3695 l_nst_int_service_period := 0 ;
3696 l_wkpd_int_sep_pay := 0 ;
3697 l_nst_ent_svpd_income_exem := 0;
3698 l_nst_int_svpd_income_exem := 0;
3699 if (p_nst_service_period - p_nst_overlap_period) > 0 then
3700 l_nst_st_service_period := p_service_period;
3701
3702 if p_wkpd_int_sep_pay > 0 and p_wkpd_int_sep_pay <= (p_nst_service_period - p_nst_overlap_period) then
3703
3704 l_nst_int_service_period := p_wkpd_int_sep_pay;
3705
3706 else
3707 l_nst_int_service_period := (p_nst_service_period - p_nst_overlap_period) - l_nst_st_service_period ;
3708 end if;
3709 /* Deductions for entire working Period */
3710 l_nst_addend := to_number(hruserdt.get_table_value(
3711 p_bus_group_id => p_business_group_id,
3712 p_table_name => 'SVPD_INCOME_EXEM',
3713 p_col_name => 'ADDEND',
3714 p_row_value => to_char(p_nst_service_period - p_nst_overlap_period),
3715 p_effective_date => p_effective_date));
3716 l_nst_multiplier := to_number(hruserdt.get_table_value(
3717 p_bus_group_id => p_business_group_id,
3718 p_table_name => 'SVPD_INCOME_EXEM',
3719 p_col_name => 'MULTIPLIER',
3720 p_row_value => to_char(p_nst_service_period - p_nst_overlap_period),
3721 p_effective_date => p_effective_date));
3722 l_nst_subtrahend := to_number(hruserdt.get_table_value(
3723 p_bus_group_id => p_business_group_id,
3724 p_table_name => 'SVPD_INCOME_EXEM',
3725 p_col_name => 'SUBTRAHEND',
3726 p_row_value => to_char(p_nst_service_period - p_nst_overlap_period),
3727 p_effective_date => p_effective_date));
3728 l_nst_ent_svpd_income_exem := l_nst_addend + trunc(l_nst_multiplier * ((p_nst_service_period - p_nst_overlap_period) - l_nst_subtrahend));
3729 /* Deductions for the working period of interim separation pay */
3730 l_nst_addend := to_number(hruserdt.get_table_value(
3731 p_bus_group_id => p_business_group_id,
3732 p_table_name => 'SVPD_INCOME_EXEM',
3733 p_col_name => 'ADDEND',
3734 p_row_value => to_char(l_nst_int_service_period),
3735 p_effective_date => p_effective_date));
3736 l_nst_multiplier := to_number(hruserdt.get_table_value(
3737 p_bus_group_id => p_business_group_id,
3738 p_table_name => 'SVPD_INCOME_EXEM',
3739 p_col_name => 'MULTIPLIER',
3740 p_row_value => to_char(l_nst_int_service_period),
3741 p_effective_date => p_effective_date));
3742 l_nst_subtrahend := to_number(hruserdt.get_table_value(
3743 p_bus_group_id => p_business_group_id,
3744 p_table_name => 'SVPD_INCOME_EXEM',
3745 p_col_name => 'SUBTRAHEND',
3746 p_row_value => to_char(l_nst_int_service_period),
3747 p_effective_date => p_effective_date));
3748 l_nst_int_svpd_income_exem := l_nst_addend + trunc(l_nst_multiplier * (l_nst_int_service_period - l_nst_subtrahend));
3749 /* Deductions for the working period of statutory separation pay */
3750 l_nst_addend := to_number(hruserdt.get_table_value(
3751 p_bus_group_id => p_business_group_id,
3752 p_table_name => 'SVPD_INCOME_EXEM',
3753 p_col_name => 'ADDEND',
3754 p_row_value => to_char(l_nst_st_service_period),
3755 p_effective_date => p_effective_date));
3756
3757 l_nst_multiplier := to_number(hruserdt.get_table_value(
3758 p_bus_group_id => p_business_group_id,
3759 p_table_name => 'SVPD_INCOME_EXEM',
3760 p_col_name => 'MULTIPLIER',
3761 p_row_value => to_char(l_nst_st_service_period),
3762 p_effective_date => p_effective_date));
3763
3764 l_nst_subtrahend := to_number(hruserdt.get_table_value(
3765 p_bus_group_id => p_business_group_id,
3766 p_table_name => 'SVPD_INCOME_EXEM',
3767 p_col_name => 'SUBTRAHEND',
3768 p_row_value => to_char(l_nst_st_service_period),
3769 p_effective_date => p_effective_date));
3770
3771 l_nst_st_svpd_income_exem := l_nst_addend + trunc(l_nst_multiplier * (l_nst_st_service_period - l_nst_subtrahend));
3772 l_nst_svpd_income_exem := l_nst_ent_svpd_income_exem - l_nst_int_svpd_income_exem - l_nst_st_svpd_income_exem;
3773 end if;
3774 l_nst_sep_pay_income_exem := 0;
3775 if p_nst_taxable_earnings > 0 then
3776 l_nst_sep_pay_income_exem := trunc(p_nst_taxable_earnings * p_sep_pay_income_exem_rate/ 100);
3777 end if;
3778 l_nst_income_exem := l_nst_svpd_income_exem + l_nst_sep_pay_income_exem;
3779 /* Taxable Income */
3780
3781 l_nst_sep_taxation_base := greatest(greatest(p_nst_taxable_earnings, 0) - l_nst_income_exem, 0);
3782 l_nst_ytaxation_base := 0;
3783
3784 if p_nst_service_period >= 1 then
3785 l_nst_ytaxation_base := greatest(trunc(l_nst_sep_taxation_base / p_nst_service_period), 0);
3786 end if;
3787
3788 /* To avoid to return taxation base result in other process */
3789 l_nst_taxation_base := l_nst_ytaxation_base;
3790
3791 /* -------- Tax Calculation for Stat and Non Stat Sep Pay ---------*/
3792 /* Total taxation Base */
3793 l_total_taxation_base := l_taxation_base + l_nst_taxation_base;
3794 /* Total yearly calculated tax */
3795 l_total_ycalc_tax := 0;
3796 if l_total_taxation_base > 0 then
3797 l_tot_multiplier := to_number(hruserdt.get_table_value(
3798 p_bus_group_id => p_business_group_id,
3799 p_table_name => 'CALC_TAX',
3800 p_col_name => 'MULTIPLIER',
3801 p_row_value => to_char(l_total_taxation_base),
3802 p_effective_date => p_effective_date));
3803
3804 l_tot_subtrahend := to_number(hruserdt.get_table_value(
3805 p_bus_group_id => p_business_group_id,
3806 p_table_name => 'CALC_TAX',
3807 p_col_name => 'SUBTRAHEND',
3808 p_row_value => to_char(l_total_taxation_base),
3809 p_effective_date => p_effective_date));
3810
3811 l_total_ycalc_tax := trunc(l_total_taxation_base * l_tot_multiplier / 100) - l_tot_subtrahend;
3812 end if;
3813 l_ycalc_tax := 0;
3814 l_nst_ycalc_tax := 0;
3815 if l_total_taxation_base > 0 then
3816 /* Yearly Calculated Tax for Stat and non stat sep pay */
3817 l_ycalc_tax := trunc(l_total_ycalc_tax *( l_taxation_base/l_total_taxation_base ));
3818 l_nst_ycalc_tax := trunc(l_total_ycalc_tax *( l_nst_taxation_base/l_total_taxation_base ));
3819 end if;
3820
3821 /* Calculated Tax for Stat and non stat sep pay */
3822 l_sep_calc_tax := 0;
3823 if p_service_period >= 1 then
3824 /* Converted Stat Sep Tax Calculation */
3825 if p_sep_tax_conversion_reqd = 'Y' and p_receivable_sep_pay > 0then
3826 l_sep_calc_tax := trunc(l_ycalc_tax * p_service_period
3827 * (p_sep_taxable_earnings / p_receivable_sep_pay));
3828
3829 else
3830 l_sep_calc_tax := l_ycalc_tax * p_service_period;
3831 end if;
3832 end if;
3833
3834 -- Bug 8525925
3835 l_nst_sep_calc_tax := 0;
3836 if p_nst_service_period >= 1 then
3837 /* Converted non-stat tax calculation is identical to */
3838 /* Normal non-stat calculation */
3839 if l_nst_receivable_sep_pay > 0 then
3840 l_nst_sep_calc_tax := trunc(l_nst_ycalc_tax * p_nst_service_period
3841 * (l_nst_sep_taxable_earnings / l_nst_receivable_sep_pay));
3842 else
3843 l_nst_sep_calc_tax := l_nst_ycalc_tax * p_nst_service_period;
3844 end if;
3845
3846 end if;
3847
3848
3849 exception
3850 when others then
3851 raise;
3852 --
3853 end;
3854
3855 ---------------------------------------------------------------------------------------------------
3856 -- Bug 8644512
3857 ---------------------------------------------------------------------------------------------------
3858
3859 function get_cont_lookup_code (p_lookup_code in varchar2, p_target_year in number) return varchar2
3860 IS
3861 lookup_code varchar2(1);
3862
3863 BEGIN
3864
3865 lookup_code := null;
3866
3867 if p_target_year <= 2008
3868 then
3869 if (p_lookup_code = '7') then
3870 lookup_code := '6' ;
3871 else
3872 lookup_code := p_lookup_code;
3873 end if;
3874
3875 elsif p_target_year > 2008
3876 then
3877 if (p_lookup_code = '7') then
3878 lookup_code := '5';
3879 elsif (p_lookup_code = '5') then
3880 lookup_code := '6';
3881 elsif (p_lookup_code = '6') then
3882 lookup_code := '7';
3883 else
3884 lookup_code := p_lookup_code;
3885 end if;
3886
3887 end if ;
3888
3889 return lookup_code;
3890
3891 END;
3892
3893 ------------------------------------------------------------------------------------------------
3894 -- Bug 9756392: Function to return the eligible Spl Sep Allowance for Previous Employer
3895 ------------------------------------------------------------------------------------------------
3896 function get_prev_sep_pay_details( p_assignment_id in number,
3897 p_date_earned in date,
3898 p_prev_stat_sep_total out NOCOPY number,
3899 p_prev_nst_sep_total out NOCOPY number,
3900 p_prev_stat_sep_calc_amt out NOCOPY number,
3901 p_prev_nst_sep_calc_amt out NOCOPY number) return number is
3902
3903 cursor get_element_entry_id (l_effective_date in date, l_assignment_id in number) is
3904 SELECT peevf.element_entry_id element_entry_id,
3905 peevf.screen_entry_value interim_date
3906 FROM pay_element_entry_values_f peevf,
3907 pay_element_entries_f peef,
3908 pay_element_types_f petf,
3909 pay_input_values_f pivf
3910 WHERE peevf.element_entry_id = peef.element_entry_id
3911 and peef.element_type_id = petf.element_type_id
3912 and petf.element_name LIKE 'PREV_ER_INFO'
3913 and petf.legislation_code = 'KR'
3914 and peef.assignment_id = l_assignment_id
3915 and peevf.input_value_id = pivf.input_value_id
3916 and pivf.element_type_id = petf.element_type_id
3917 and pivf.name IN ('FINAL_INT_DATE')
3918 and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
3919 and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
3920 and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
3921 and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
3922
3923 cursor prev_spl_sep_alw(l_effective_date in date, l_assignment_id in number, l_element_entry_id number) is
3924 SELECT sum(nvl(peevf.screen_entry_value,0)) non_stat_earnings
3925 FROM pay_element_entry_values_f peevf,
3926 pay_element_entries_f peef,
3927 pay_element_types_f petf,
3928 pay_input_values_f pivf
3929 WHERE peevf.element_entry_id = peef.element_entry_id
3930 and peevf.element_entry_id = l_element_entry_id
3931 and peef.element_type_id = petf.element_type_id
3932 and petf.element_name LIKE 'PREV_ER_INFO'
3933 and petf.legislation_code = 'KR'
3934 and peef.assignment_id = l_assignment_id
3935 and peevf.input_value_id = pivf.input_value_id
3936 and pivf.element_type_id = petf.element_type_id
3937 and pivf.name in ('SP_SEP_ALW')
3938 and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
3939 and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
3940 and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
3941 and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
3942
3943 cursor get_prev_sep_bus_reg_num (l_effective_date in date, l_assignment_id in number, l_element_entry_id in number) is
3944 SELECT peevf.element_entry_id element_entry_id
3945 FROM pay_element_entry_values_f peevf,
3946 pay_element_entries_f peef,
3947 pay_element_types_f petf,
3948 pay_input_values_f pivf
3949 WHERE peevf.element_entry_id = peef.element_entry_id
3950 and peef.element_type_id = petf.element_type_id
3951 and petf.element_name LIKE 'PREV_SEP_PENS_DTLS'
3952 and petf.legislation_code = 'KR'
3953 and peef.assignment_id = l_assignment_id
3954 and peevf.input_value_id = pivf.input_value_id
3955 and pivf.element_type_id = petf.element_type_id
3956 and pivf.name IN ('BP_NUMBER')
3957 and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
3958 and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
3959 and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
3960 and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
3961 and peevf.screen_entry_value in (SELECT peevf.screen_entry_value bus_reg_num
3962 FROM pay_element_entry_values_f peevf,
3963 pay_element_entries_f peef,
3964 pay_element_types_f petf,
3965 pay_input_values_f pivf
3966 WHERE peevf.element_entry_id = peef.element_entry_id
3967 and peevf.element_entry_id = nvl(l_element_entry_id,peevf.element_entry_id)
3968 and peef.element_type_id = petf.element_type_id
3969 and petf.element_name LIKE 'PREV_ER_INFO'
3970 and petf.legislation_code = 'KR'
3971 and peef.assignment_id = l_assignment_id
3972 and peevf.input_value_id = pivf.input_value_id
3973 and pivf.element_type_id = petf.element_type_id
3974 and pivf.name IN ('BP_NUMBER')
3975 and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
3976 and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
3977 and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
3978 and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
3979 group by peevf.screen_entry_value);
3980
3981 cursor get_prev_emp_amt (l_effective_date in date, l_assignment_id in number, l_element_entry_id in number) is
3982 SELECT nvl(peevf.screen_entry_value,0) entry_value
3983 FROM pay_element_entry_values_f peevf,
3984 pay_element_entries_f peef,
3985 pay_element_types_f petf,
3986 pay_input_values_f pivf
3987 WHERE peevf.element_entry_id = peef.element_entry_id
3988 and peevf.element_entry_id = nvl(l_element_entry_id,peevf.element_entry_id)
3989 and peef.element_type_id = petf.element_type_id
3990 and petf.element_name LIKE 'PREV_SEP_PENS_DTLS'
3991 and petf.legislation_code = 'KR'
3992 and peef.assignment_id = l_assignment_id
3993 and peevf.input_value_id = pivf.input_value_id
3994 and pivf.element_type_id = petf.element_type_id
3995 and pivf.name IN ('PRINCIPAL_INTRST','PERS_CONTRIBUTION','RECEIVED_AMOUNT_NONSTAT','DEFERRED_AMOUNT_NONSTAT','PENS_EXEM','AMT_EXP_NONSTAT_SEP','TOTAL_RECEIVED','LUMP_SUM_AMT_NST')
3996 and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
3997 and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
3998 and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
3999 and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
4000 order by pivf.name;
4001
4002 total_prev_emp_amt number;
4003 l_prev_spl_sep_alw number;
4004 prev_amt_exp_nonstat_sep number;
4005 prev_deferred_amt_nst number;
4006 prev_pension_exemption number;
4007 prev_personal_contibution number;
4008 prev_principal_interest number;
4009 prev_received_amt_nst number;
4010 prev_total_received number;
4011 l_count number;
4012 prev_lump_sum_amt_nst number;
4013 begin
4014
4015 p_prev_stat_sep_total := 0 ;
4016 p_prev_nst_sep_total := 0 ;
4017 p_prev_stat_sep_calc_amt := 0;
4018 p_prev_nst_sep_calc_amt := 0;
4019
4020 for i in get_element_entry_id (p_date_earned, p_assignment_id) loop
4021
4022 total_prev_emp_amt := 0;
4023 l_prev_spl_sep_alw := 0;
4024 prev_amt_exp_nonstat_sep := 0;
4025 prev_deferred_amt_nst := 0;
4026 prev_pension_exemption := 0;
4027 prev_personal_contibution := 0;
4028 prev_principal_interest := 0;
4029 prev_received_amt_nst := 0;
4030 prev_total_received := 0;
4031 prev_lump_sum_amt_nst := 0;
4032 l_count := 0;
4033
4034 for j in get_prev_sep_bus_reg_num(p_date_earned, p_assignment_id, i.element_entry_id) loop
4035
4036 for k in get_prev_emp_amt (p_date_earned, p_assignment_id,j.element_entry_id) loop
4037
4038 if l_count = 0 then
4039 prev_amt_exp_nonstat_sep := k.entry_value;
4040 l_count := l_count + 1;
4041 elsif l_count = 1 then
4042 prev_deferred_amt_nst := k.entry_value;
4043 l_count := l_count + 1;
4044 elsif l_count = 2 then
4045 prev_lump_sum_amt_nst := k.entry_value;
4046 l_count := l_count + 1;
4047 elsif l_count = 3 then
4048 prev_pension_exemption := k.entry_value;
4049 l_count := l_count + 1;
4050 elsif l_count = 4 then
4051 prev_personal_contibution := k.entry_value;
4052 l_count := l_count + 1;
4053 elsif l_count = 5 then
4054 prev_principal_interest := k.entry_value;
4055 l_count := l_count + 1;
4056 elsif l_count = 6 then
4057 prev_received_amt_nst := k.entry_value;
4058 l_count := l_count + 1;
4059 elsif l_count = 7 then
4060 prev_total_received := k.entry_value;
4061 end if;
4062 end loop;
4063 end loop;
4064 if prev_amt_exp_nonstat_sep > 0 then
4065 if prev_principal_interest > 0 then
4066 total_prev_emp_amt := prev_amt_exp_nonstat_sep *
4067 ( 1 - (prev_personal_contibution - prev_pension_exemption) / prev_principal_interest);
4068
4069 else
4070 total_prev_emp_amt := prev_amt_exp_nonstat_sep;
4071
4072 end if;
4073 end if;
4074
4075 open prev_spl_sep_alw(p_date_earned, p_assignment_id, i.element_entry_id);
4076 fetch prev_spl_sep_alw into l_prev_spl_sep_alw;
4077 close prev_spl_sep_alw;
4078
4079 hr_utility.trace('interim_date = '||i.interim_date);
4080
4081 if i.interim_date is null then
4082 p_prev_stat_sep_calc_amt := p_prev_stat_sep_calc_amt + prev_lump_sum_amt_nst + l_prev_spl_sep_alw;
4083 p_prev_stat_sep_total := p_prev_stat_sep_total + total_prev_emp_amt + l_prev_spl_sep_alw + prev_deferred_amt_nst
4084 + prev_received_amt_nst ;
4085 else
4086 p_prev_nst_sep_calc_amt := p_prev_nst_sep_calc_amt + prev_lump_sum_amt_nst + l_prev_spl_sep_alw;
4087 p_prev_nst_sep_total := p_prev_nst_sep_total + total_prev_emp_amt + l_prev_spl_sep_alw + prev_deferred_amt_nst
4088 + prev_received_amt_nst;
4089 end if;
4090
4091 end loop;
4092
4093 return 0;
4094 end;
4095
4096 ------------------------------------------------------------------------------------------------
4097 -- Bug 9079450: Function to return the lookup meaning for the dependent education expense region
4098 ------------------------------------------------------------------------------------------------
4099 function decode_lookup(
4100 p_effective_date in varchar2,
4101 p_code in varchar2) return varchar2 is
4102 --
4103 l_meaning varchar2(80) := null;
4104 l_year number := null;
4105 --
4106 begin
4107 --
4108 l_year := to_number(to_char(fnd_date.canonical_to_date(p_effective_date),'YYYY'));
4109 if l_year < 2009 then
4110 l_meaning := hr_general.decode_lookup('CONTACT',p_code);
4111 else
4112 l_meaning := hr_general.decode_lookup('KR_CONTACT_RELATIONSHIPS',p_code);
4113 end if;
4114 --
4115 return l_meaning;
4116 end;
4117
4118 ------------------------------------------------------------------------------------------------
4119 -- Bug 10082074: Function to return Children under the age of 20
4120 ------------------------------------------------------------------------------------------------
4121 function underaged_children_flag(
4122 p_contact_type in varchar2,
4123 p_kr_cont_type in varchar2,
4124 p_national_identifier in varchar2,
4125 p_effective_date in date) return varchar2 is
4126
4127 l_sex varchar2(1);
4128 l_date_of_birth date;
4129 l_eoy_age number;
4130 l_flag varchar2(1);
4131 begin
4132 l_flag := 'N';
4133 if p_kr_cont_type in ('4','7','8') or p_contact_type in ('A','C','T','O') then
4134 ni_component(
4135 p_national_identifier => p_national_identifier,
4136 p_sex => l_sex,
4137 p_date_of_birth => l_date_of_birth);
4138 l_eoy_age := eoy_age(l_date_of_birth, p_effective_date);
4139 --
4140 if l_eoy_age < 20 then
4141 l_flag := 'Y';
4142 end if;
4143 end if;
4144 return l_flag;
4145 end underaged_children_flag;
4146 -------------------------------------------------------------------------------------------------
4147 --
4148 -- Bug 9393732: Function to check and return if the YEA Process has been run for an assignment
4149 -- for a target year.
4150 -------------------------------------------------------------------------------------------------
4151 function display_yea_info(p_assignment_id in number,
4152 p_effective_date in date) return varchar2
4153 -------------------------------------------------------------------------------------------------
4154 is
4155 --
4156 l_dummy varchar2(2);
4157 --
4158 cursor csr is
4159 SELECT 'Y'
4160 from pay_payroll_actions ppa,
4161 pay_assignment_actions paa
4162 where paa.assignment_id = p_assignment_id
4163 and paa.action_status = 'C'
4164 and ppa.payroll_action_id = paa.payroll_action_id
4165 and ppa.report_type = 'YEA'
4166 and ppa.report_qualifier = 'KR'
4167 and to_char(ppa.effective_date, 'YYYY') = to_char(p_effective_date, 'YYYY')
4168 and ppa.action_type in ('B','X');
4169 --
4170 begin
4171 --
4172 l_dummy := 'N';
4173 --
4174 open csr;
4175 fetch csr into l_dummy;
4176 if csr%NOTFOUND then
4177 l_dummy := 'N';
4178 end if;
4179 close csr;
4180
4181 return l_dummy;
4182 --
4183 end;
4184 -------------------------------------------------------------------------------------------------
4185 -- Bug 11867156
4186 -------------------------------------------------------------------------------------------------
4187 function get_avg_taxable_earnings(p_taxable_earnings in number,
4188 p_addtl_child_exem_govt_flag in varchar2,
4189 p_foreign_fixed_tax_rate in varchar2) return number
4190 is
4191 l_low_limit number;
4192 l_higher_limit number;
4193
4194 begin
4195
4196 l_low_limit := 0;
4197 l_higher_limit := 0;
4198 if p_addtl_child_exem_govt_flag = 'Y' and p_foreign_fixed_tax_rate = 'N' then
4199 if p_taxable_earnings < 790000 then
4200
4201 return p_taxable_earnings;
4202
4203 elsif p_taxable_earnings < 1500000 then
4204
4205 l_low_limit := trunc(p_taxable_earnings/(5*1000)) * 5 * 1000;
4206 l_higher_limit := l_low_limit + 5000;
4207 return (round((l_low_limit + l_higher_limit)/2));
4208
4209 elsif p_taxable_earnings < 3000000 then
4210
4211 l_low_limit := trunc(p_taxable_earnings/(10*1000)) * 10 * 1000;
4212 l_higher_limit := l_low_limit + 10000;
4213 return (round((l_low_limit + l_higher_limit)/2));
4214
4215 elsif p_taxable_earnings < 10000000 then
4216
4217 l_low_limit := trunc(p_taxable_earnings/(20*1000)) * 20 * 1000;
4218 l_higher_limit := l_low_limit + 20000;
4219 return (round((l_low_limit + l_higher_limit)/2));
4220 else
4221 return p_taxable_earnings;
4222 end if;
4223
4224 else
4225 return p_taxable_earnings;
4226 end if;
4227
4228 end get_avg_taxable_earnings;
4229
4230 -- Bug# 13990960 Starts
4231 --------------------------------------------------------------------------------
4232 function get_kr_address_line1(p_postal_code_id in varchar2) return varchar2
4233 --------------------------------------------------------------------------------
4234 is
4235 --
4236 l_postal_code_id number;
4237 l_address_line1 varchar2(200);
4238 --
4239 cursor csr_kr_address
4240 is
4241 select
4242 decode(ROAD_NAME, null, rtrim(CITY_PROVINCE||' '||DISTRICT||' '||TOWN_VILLAGE||' '||HOUSE_NUMBER),
4243 RTRIM(CITY_PROVINCE||' '||DISTRICT||' '||TOWN_VILLAGE||' '||ROAD_NAME||' '||HOUSE_NUMBER||' '||
4244 decode((LEGISLATIVE_DISTRICT || PUBLIC_HOUSING_NAME) , null, ' ', '('||LEGISLATIVE_DISTRICT ||
4245 decode(LEGISLATIVE_DISTRICT, null, PUBLIC_HOUSING_NAME,decode(PUBLIC_HOUSING_NAME, null, null,', '||PUBLIC_HOUSING_NAME)) || ')' )))
4246 from per_kr_addresses
4247 where postal_code_id = l_postal_code_id;
4248 --
4249 begin
4250 --
4251 l_postal_code_id := to_number(p_postal_code_id);
4252 --
4253 open csr_kr_address;
4254 fetch csr_kr_address into l_address_line1;
4255 close csr_kr_address;
4256 --
4257 return l_address_line1;
4258 --
4259 end get_kr_address_line1;
4260
4261 -- Bug# 13990960 Ends
4262 /* BUG 14754822 START */
4263 function tax_reduction_calc(p_assignment_id in number,
4264 p_information_type in varchar2,
4265 p_hire_date in date,
4266 p_term_date in date,
4267 p_pay_proc_period_date in date,
4268 p_calc_tax in number,
4269 p_taxable_earnings in number,
4270 p_eligible_tax_reduc_amnt in number
4271 ) return number
4272 is
4273 cursor get_dff_segment_values
4274 is
4275 select fnd_date.canonical_to_date(aei.AEI_INFORMATION1),
4276 fnd_date.canonical_to_date(aei.AEI_INFORMATION2)
4277 from per_assignment_extra_info aei
4278 where aei.information_type = p_information_type
4279 and aei.assignment_id = p_assignment_id;
4280
4281 p_start_date date := null;
4282 p_end_date date := null;
4283 p_earnings_value number;
4284 l_term_date date := null;
4285 l_calc_start_date date := null;
4286 l_calc_end_date date := null;
4287 l_tax_reduc number := 1;
4288 Begin
4289 hr_utility.trace('p_pay_proc_period_date = '||p_pay_proc_period_date);
4290 hr_utility.trace('p_calc_tax = '||p_calc_tax);
4291
4292 l_term_date := to_date('4712/12/31','YYYY/MM/DD');
4293 open get_dff_segment_values;
4294 fetch get_dff_segment_values into p_start_date,p_end_date;
4295 if get_dff_segment_values%NOTFOUND then
4296 p_start_date := null;
4297 p_end_date := null;
4298 end if;
4299 if p_start_date is null then
4300 l_calc_start_date := p_hire_date;
4301 else
4302 l_calc_start_date := p_start_date;
4303 end if;
4304 if p_end_date is null then
4305 l_calc_end_date := least(l_term_date, last_day(add_months(to_date(l_calc_start_date,'DD-MM-YYYY'), 3*12)-1));
4306 else
4307 l_calc_end_date := p_end_date;
4308 end if;
4309 close get_dff_segment_values;
4310 if to_date(p_pay_proc_period_date,'DD-MM-YYYY') <= to_date(l_calc_end_date,'DD-MM-YYYY') then
4311 l_tax_reduc := p_eligible_tax_reduc_amnt*12;
4312 else
4313 l_tax_reduc := 0;
4314 end if;
4315 hr_utility.trace('l_calc_start_date = '||l_calc_start_date);
4316 hr_utility.trace('l_calc_end_date = '||l_calc_end_date);
4317 hr_utility.trace('l_tax_reduc = '||l_tax_reduc);
4318 return l_tax_reduc;
4319 End tax_reduction_calc; -- tax_reduction_calc
4320 /* BUG 14754822 END */
4321 end pay_kr_ff_functions_pkg;