[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.16.12010000.6 2008/08/06 07:40:39 ubhat 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) then
180 l_dob_cent := '19';
181 elsif l_sex_code in (3, 4) then
182 l_dob_cent := '20';
183 else
184 --
185 -- We "GUESS" date of birth in case of sex between 5 to 0 using effective date.
186 -- In Korea, there's no exact rule to derive accurate date of birth
187 -- from NI number whose sex is between 5 to 0.
188 --
189 -- Bug 4674552: execute query only if effective date is not cached
190 if g_effective_date is null then
191 select effective_date
192 into g_effective_date
193 from fnd_sessions
194 where session_id = userenv('sessionid');
195 --
196 end if ;
197 l_effective_date := to_char(g_effective_date, 'YYYYMMDD') ;
198 -- End of 4674552
199 -- If effective_date(YYMMDD) >= date_of_birth(YYMMDD), use same century as effective_date.
200 -- else use previous century as effective_date.
201 --
202 l_effective_date_cent := substr(l_effective_date, 1, 2);
203 if substr(l_effective_date, 3) >= l_dob_yymmdd then
204 l_dob_cent := l_effective_date_cent;
205 else
206 l_dob_cent := to_char(to_number(l_effective_date_cent) - 1, 'FM09');
207 end if;
208 end if;
209 --
210 p_date_of_birth := to_date(l_dob_cent || l_dob_yymmdd, 'YYYYMMDD');
211 --
212 if mod(l_sex_code, 2) = 1 then
213 p_sex := 'M';
214 else
215 p_sex := 'F';
216 end if;
217 --
218 -- Set current NI information to global variable as cache.
219 --
220 g_ni.national_identifier := p_national_identifier;
221 g_ni.sex := p_sex;
222 g_ni.date_of_birth := p_date_of_birth;
223 exception
224 when others then
225 fnd_message.set_name('PER', 'PER_KR_INV_NI_NUMBER');
226 fnd_message.set_token('NI_NUMBER', p_national_identifier);
227 fnd_message.raise_error;
228 end;
229 end if;
230 end ni_component;
231 /*
232 --------------------------------------------------------------------------------
233 procedure ni_component(
234 p_national_identifier in varchar2,
235 p_person_name in varchar2,
236 p_sex out varchar2,
237 p_date_of_birth out date)
238 --------------------------------------------------------------------------------
239 is
240 begin
241 if p_national_identifier is null then
242 fnd_message.set_name('PER', 'PER_KR_CON_PER_NI_NUMBER_NULL');
243 fnd_message.set_token('PERSON_NAME', p_person_name);
244 fnd_message.raise_error;
245 end if;
246 --
247 begin
248 ni_component(
249 p_national_identifier => p_national_identifier,
250 p_sex => p_sex,
251 p_date_of_birth => p_date_of_birth);
252 exception
253 when others then
254 fnd_message.set_name('PER', 'PER_KR_CON_PER_INV_NI_NUMBER');
255 fnd_message.set_token('NI_NUMBER', p_national_identifier);
256 fnd_message.set_token('PERSON_NAME', p_person_name);
257 fnd_message.raise_error;
258 end;
259 end ni_component;
260 */
261 --------------------------------------------------------------------------------
262 function ni_sex(p_national_identifier in varchar2) return varchar2
263 --------------------------------------------------------------------------------
264 is
265 l_sex varchar2(1);
266 l_date_of_birth date;
267 begin
268 ni_component(
269 p_national_identifier => p_national_identifier,
270 p_sex => l_sex,
271 p_date_of_birth => l_date_of_birth);
272 --
273 return l_sex;
274 end ni_sex;
275 --------------------------------------------------------------------------------
276 function ni_date_of_birth(p_national_identifier in varchar2) return date
277 --------------------------------------------------------------------------------
278 is
279 l_sex varchar2(1);
280 l_date_of_birth date;
281 begin
282 ni_component(
283 p_national_identifier => p_national_identifier,
284 p_sex => l_sex,
285 p_date_of_birth => l_date_of_birth);
286 --
287 return l_date_of_birth;
288 end ni_date_of_birth;
289 --------------------------------------------------------------------------
290 -- Bug 3172960
291 function ni_nationality(p_national_identifier in varchar2) return varchar2
292 --------------------------------------------------------------------------
293 is
294 l_nationality varchar2(1);
295 begin
296 if to_number(substr(p_national_identifier,8,1)) >= 5 and to_number(substr(p_national_identifier,8,1)) <= 8 then
297 l_nationality := 'F';
298 else
299 l_nationality := 'K';
300 end if;
301 return l_nationality;
302 end ni_nationality;
303 ---------------------------------------------------------------------
304 -- Bug 3172960
305 function ni_nationality(p_assignment_id in number,
306 p_effective_date in date) return varchar2
307 ---------------------------------------------------------------------
308 is
309 cursor csr_ni is
310 select
311 hr_ni_chk_pkg.chk_nat_id_format(per.national_identifier, 'DDDDDD-DDDDDDD') NI
312 from per_people_f per,
313 per_assignments_f asg
314 where asg.assignment_id = p_assignment_id
315 and per.person_id = asg.person_id
316 and p_effective_date between per.effective_start_date and per.effective_end_date
317 and p_effective_date between asg.effective_start_date and asg.effective_end_date;
318
319 l_ni varchar2(20);
320 l_nationality varchar2(1);
321 begin
322 open csr_ni;
323 fetch csr_ni into l_ni;
324 close csr_ni;
325
326 l_nationality := pay_kr_ff_functions_pkg.ni_nationality(l_ni);
327 return l_nationality;
328 end;
329 ----------------------------------------------------------------------
330 -- Returns End Of Year Age
331 ----------------------------------------------------------------------
332 function eoy_age(
333 p_date_of_birth in date,
334 p_effective_date in date) return number
335 is
336 begin
337 return to_number(to_char(p_effective_date, 'YYYY')) - to_number(to_char(p_date_of_birth, 'YYYY'));
338 end eoy_age;
339 ----------------------------------------------------------------------
340 -- Dependent Spouse Tax Exemption
341 ----------------------------------------------------------------------
342 function dpnt_spouse_flag(p_contact_type in varchar2) return varchar2
343 is
344 begin
345 if p_contact_type = 'S' then
346 return 'Y';
347 else
348 return 'N';
349 end if;
350 end dpnt_spouse_flag;
351 ----------------------------------------------------------------------
352 -- Aged Dependent Tax Exemption
353 ----------------------------------------------------------------------
354 function aged_dpnt_flag(
355 p_contact_type in varchar2,
356 p_national_identifier in varchar2,
357 p_effective_date in date) return varchar2
358 is
359 l_sex varchar2(1);
360 l_date_of_birth date;
361 l_eoy_age number;
362 l_flag varchar2(1);
363 begin
364 l_flag := 'N';
365 if p_contact_type <> 'S' then
366 ni_component(
367 p_national_identifier => p_national_identifier,
368 p_sex => l_sex,
369 p_date_of_birth => l_date_of_birth);
370 l_eoy_age := eoy_age(l_date_of_birth, p_effective_date);
371 --
372 if (l_sex = 'M' and l_eoy_age >= 60) or (l_sex = 'F' and l_eoy_age >= 55) then
373 l_flag := 'Y';
374 end if;
375 end if;
376 --
377 return l_flag;
378 end aged_dpnt_flag;
379 ----------------------------------------------------------------------
380 -- Adult Dependent Tax Exemption
381 ----------------------------------------------------------------------
382 function adult_dpnt_flag(
383 p_contact_type in varchar2,
384 p_national_identifier in varchar2,
385 p_effective_date in date,
386 p_disabled_flag in varchar2,
387 p_age_exception_flag in varchar2) return varchar2
388 is
389 l_sex varchar2(1);
390 l_date_of_birth date;
391 l_eoy_age number;
392 l_flag varchar2(1);
393 begin
394 l_flag := 'N';
395 if p_contact_type <> 'S' then
396 ni_component(
397 p_national_identifier => p_national_identifier,
398 p_sex => l_sex,
399 p_date_of_birth => l_date_of_birth);
400 l_eoy_age := eoy_age(l_date_of_birth, p_effective_date);
401 --
402 if ((l_sex = 'M' and l_eoy_age > 20 and l_eoy_age < 60)
403 or (l_sex = 'F' and l_eoy_age > 20 and l_eoy_age < 55))
404 and (p_disabled_flag ='Y' or p_age_exception_flag='Y' ) then
405 -- Bug 3073424 added the above disabled and age exception check
406 l_flag := 'Y';
407 end if;
408 end if;
409 --
410 return l_flag;
411 end adult_dpnt_flag;
412 ----------------------------------------------------------------------
413 -- Underaged Dependent Tax Exemption
414 ----------------------------------------------------------------------
415 function underaged_dpnt_flag(
416 p_contact_type in varchar2,
417 p_national_identifier in varchar2,
418 p_effective_date in date) return varchar2
419 is
420 l_sex varchar2(1);
421 l_date_of_birth date;
422 l_eoy_age number;
423 l_flag varchar2(1);
424 begin
425 l_flag := 'N';
426 if p_contact_type <> 'S' then
427 ni_component(
428 p_national_identifier => p_national_identifier,
429 p_sex => l_sex,
430 p_date_of_birth => l_date_of_birth);
431 l_eoy_age := eoy_age(l_date_of_birth, p_effective_date);
432 --
433 if l_eoy_age <= 20 then
434 l_flag := 'Y';
435 end if;
436 end if;
437 --
438 return l_flag;
439 end underaged_dpnt_flag;
440 ----------------------------------------------------------------------
441 -- Aged Tax Exemption
442 ----------------------------------------------------------------------
443 function aged_flag(
444 p_national_identifier in varchar2,
445 p_effective_date in date) return varchar2
446 is
447 l_sex varchar2(1);
448 l_date_of_birth date;
449 l_eoy_age number;
450 l_flag varchar2(1);
451 begin
452 l_flag := 'N';
453 ni_component(
454 p_national_identifier => p_national_identifier,
455 p_sex => l_sex,
456 p_date_of_birth => l_date_of_birth);
457 l_eoy_age := eoy_age(l_date_of_birth, p_effective_date);
458 --
459 if l_eoy_age >= 65 then
460 l_flag := 'Y';
461 end if;
462 --
463 return l_flag;
464 end aged_flag;
465 ----------------------------------------------------------------------
466 -- Bug 3172960
467 -- Super Aged Tax Exemption
468 ----------------------------------------------------------------------
469 function super_aged_flag(
470 p_national_identifier in varchar2,
471 p_effective_date in date) return varchar2
472 is
473 l_sex varchar2(1);
474 l_date_of_birth date;
475 l_eoy_age number;
476 l_flag varchar2(1);
477 begin
478 l_flag := 'N';
479 ni_component(
480 p_national_identifier => p_national_identifier,
481 p_sex => l_sex,
482 p_date_of_birth => l_date_of_birth);
483 l_eoy_age := eoy_age(l_date_of_birth, p_effective_date);
484 --
485 if l_eoy_age >= 70 then
486 l_flag := 'Y';
487 end if;
488 --
489 return l_flag;
490 end super_aged_flag;
491 ----------------------------------------------------------------------
492 -- Disabled Tax Exemption
493 ----------------------------------------------------------------------
494 function disabled_flag(
495 p_person_id in number,
496 p_effective_date in date) return varchar2
497 is
498 l_flag varchar2(1);
499
500 -- Bug# 2657588
501 -- Cursor modified to return 'N' if dis_information3 is 'N' for any one of the record.
502
503 cursor csr_disabled(p_person_id number)
504 is
505 select nvl(dis_information3, 'Y')
506 from per_disabilities_f
507 where person_id = p_person_id
508 and dis_information_category = 'KR'
509 and p_effective_date between effective_start_date and effective_end_date
510 order by dis_information3 ;
511 begin
512 l_flag := 'N';
513 open csr_disabled(p_person_id);
514 fetch csr_disabled into l_flag;
515 if csr_disabled%NOTFOUND then
516 l_flag := 'N';
517 end if;
518 close csr_disabled;
519 --
520 return l_flag;
521 end disabled_flag;
522 ----------------------------------------------------------------------
523 -- Child Tax Exemption
524 ----------------------------------------------------------------------
525 function child_flag(
526 p_national_identifier in varchar2,
527 p_effective_date in date) return varchar2
528 is
529 l_sex varchar2(1);
530 l_date_of_birth date;
531 l_eoy_age number;
532 l_flag varchar2(1);
533 begin
534 l_flag := 'N';
535 ni_component(
536 p_national_identifier => p_national_identifier,
537 p_sex => l_sex,
538 p_date_of_birth => l_date_of_birth);
539 l_eoy_age := eoy_age(l_date_of_birth, p_effective_date);
540 --
541 if l_eoy_age <= 6 then
542 l_flag := 'Y';
543 end if;
544 --
545 return l_flag;
546 end child_flag;
547 --------------------------------------------------------------------------------
548 -- National Pension Exception Reason (Formula Function)
549 -- Skip processing of National Pension Prem element, if National Pension
550 -- exception rules are entered against the employee
551 -- Bug 2815425
552 --------------------------------------------------------------------------------
553 function get_np_exception_flag (
554 p_date_earned IN DATE
555 ,p_business_group_id IN NUMBER
556 ,p_assignment_id IN NUMBER ) RETURN VARCHAR2 IS
557 --
558 l_exception_flag per_people_extra_info.pei_information1%TYPE;
559 --
560 cursor csr_exception_flag is
561 select pei.pei_information1
562 from per_people_f pap
563 ,per_people_extra_info pei
564 ,per_assignments_f paa
565 where paa.assignment_id = p_assignment_id
569 and pei.information_type = 'PER_KR_NP_EXCEPTIONS'
566 and paa.business_group_id = p_business_group_id
567 and paa.person_id = pap.person_id
568 and pap.person_id = pei.person_id
570 and p_date_earned between paa.effective_start_date and paa.effective_end_date
571 and p_date_earned between pap.effective_start_date and pap.effective_end_date
572 and p_date_earned between fnd_date.canonical_to_date(pei.pei_information2)
573 and fnd_date.canonical_to_date(pei.pei_information3);
574 --
575 begin
576 l_exception_flag :='N';
577 open csr_exception_flag;
578 fetch csr_exception_flag into l_exception_flag;
579 close csr_exception_flag;
580 --
581 if l_exception_flag = 'N' then
582 return 'N';
583 else
584 return 'Y';
585 end if;
586 --
587 end get_np_exception_flag;
588 --------------------------------------------------------------------------------
589 /* Bug 6784288 */
590 function addtl_child_flag(
591 p_contact_type in varchar2,
592 p_national_identifier in varchar2,
593 p_effective_date in date) return varchar2
594 is
595 Cursor csr is
596 SELECT lookup_code
597 FROM hr_leg_lookups
598 WHERE lookup_type = 'CONTACT'
599 AND meaning LIKE '%Child%'
600 AND lookup_code = p_contact_type;
601
602 l_underaged_flag varchar2(2);
603 l_dummy hr_leg_lookups.lookup_code%type;
604 begin
605 l_underaged_flag := 'N';
606 l_underaged_flag := underaged_dpnt_flag(p_contact_type,p_national_identifier,p_effective_date);
607
608 open csr;
609 fetch csr into l_dummy;
610 close csr;
611
612 if l_dummy is not null and l_underaged_flag = 'Y' then
613 return 'Y';
614 else
615 return 'N';
616 end if;
617
618 end addtl_child_flag;
619 --------------------------------------------------------------------------------
620 -- Bug 3172960
621 function get_dependent_info(
622 p_assignment_id in number,
623 p_date_earned in date,
624 p_non_resident_flag in varchar2,
625 p_dpnt_spouse_flag out NOCOPY varchar2,
626 p_num_of_aged_dpnts out NOCOPY number,
627 p_num_of_adult_dpnts out NOCOPY number,
628 p_num_of_underaged_dpnts out NOCOPY number,
629 p_num_of_dpnts out NOCOPY number,
630 p_num_of_ageds out NOCOPY number,
631 p_num_of_disableds out NOCOPY number,
632 p_female_ee_flag out NOCOPY varchar2,
633 p_num_of_children out NOCOPY number) return number
634 --------------------------------------------------------------------------------
635 is
636 l_return number(10);
637 l_num_of_super_ageds number(10);
638 begin
639 l_return := get_dependent_info(
640 p_assignment_id,
641 p_date_earned,
642 p_non_resident_flag,
643 p_dpnt_spouse_flag,
644 p_num_of_aged_dpnts,
645 p_num_of_adult_dpnts,
646 p_num_of_underaged_dpnts,
647 p_num_of_dpnts,
648 p_num_of_ageds,
649 p_num_of_disableds,
650 p_female_ee_flag,
651 p_num_of_children,
652 l_num_of_super_ageds);
653
654 return l_return;
655
656 end get_dependent_info;
657 --------------------------------------------------------------------------------
658 /* Bug 6784288 */
659 function get_dependent_info(
660 p_assignment_id in number,
661 p_date_earned in date,
662 p_non_resident_flag in varchar2,
663 p_dpnt_spouse_flag out NOCOPY varchar2,
664 p_num_of_aged_dpnts out NOCOPY number,
665 p_num_of_adult_dpnts out NOCOPY number,
666 p_num_of_underaged_dpnts out NOCOPY number,
667 p_num_of_dpnts out NOCOPY number,
668 p_num_of_ageds out NOCOPY number,
669 p_num_of_disableds out NOCOPY number,
670 p_female_ee_flag out NOCOPY varchar2,
671 p_num_of_children out NOCOPY number,
672 p_num_of_super_ageds out NOCOPY number) return number
673 --------------------------------------------------------------------------------
674 is
675 l_return number(10);
676 l_num_of_addtl_child number(10); /* Bug 6784288 */
677 begin
678 l_return := get_dependent_info(
679 p_assignment_id,
680 p_date_earned,
681 p_non_resident_flag,
682 p_dpnt_spouse_flag,
683 p_num_of_aged_dpnts,
684 p_num_of_adult_dpnts,
685 p_num_of_underaged_dpnts,
686 p_num_of_dpnts,
687 p_num_of_ageds,
688 p_num_of_disableds,
689 p_female_ee_flag,
690 p_num_of_children,
691 p_num_of_super_ageds,
692 l_num_of_addtl_child); /* Bug 6784288 */
693
694 return l_return;
695
696 end get_dependent_info;
697 --------------------------------------------------------------------------------
698 /* Bug 6705170 : Function get_dependent_info() has been overloaded
699 to fetch the New Born/Adopted Child count */
700 --------------------------------------------------------------------------------
701 function get_dependent_info(
702 p_assignment_id in number,
703 p_date_earned in date,
704 p_non_resident_flag in varchar2,
705 p_dpnt_spouse_flag out NOCOPY varchar2,
706 p_num_of_aged_dpnts out NOCOPY number,
707 p_num_of_adult_dpnts out NOCOPY number,
708 p_num_of_underaged_dpnts out NOCOPY number,
709 p_num_of_dpnts out NOCOPY number,
713 p_num_of_children out NOCOPY number,
710 p_num_of_ageds out NOCOPY number,
711 p_num_of_disableds out NOCOPY number,
712 p_female_ee_flag out NOCOPY varchar2,
714 p_num_of_super_ageds out NOCOPY number,
715 p_num_of_new_born_adopted out NOCOPY number,
716 p_num_of_addtl_child out NOCOPY number) return number /* Bug 6784288 */
717 --------------------------------------------------------------------------------
718 is
719 l_return number(10);
720 type l_flag_tbl is table of varchar2(1) index by binary_integer;
721 l_new_born_adopted_flag_tbl l_flag_tbl;
722 --
723 cursor csr_new_born is
724 select
725 nvl(cei.cei_information13, 'N')
726 from per_people_f per,
727 per_contact_relationships ctr,
728 per_assignments_f asg,
729 per_contact_extra_info_f cei
730 where asg.assignment_id = p_assignment_id
731 and p_date_earned
732 between asg.effective_start_date and asg.effective_end_date
733 and ctr.person_id = asg.person_id
734 and ctr.cont_information_category = 'KR'
735 and ctr.cont_information1 = 'Y'
736 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))
737 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))
738 )
739 and per.person_id = ctr.contact_person_id
740 and cei.contact_relationship_id = ctr.contact_relationship_id
741 and cei.information_type = 'KR_DPNT_EXPENSE_INFO'
742 and to_char(cei.effective_start_date, 'YYYY') = to_char(p_date_earned,'YYYY')
743 and (
744 (
745 p_date_earned
746 between per.effective_start_date and per.effective_end_date
747 )
748 or
749 (
750 per.start_date = per.effective_start_date
751 and not exists(
752 select null
753 from per_people_f per2
754 where per2.person_id = per.person_id
755 and p_date_earned
756 between per2.effective_start_date and per2.effective_end_date)
757 )
758 );
759 begin
760 p_num_of_new_born_adopted := 0;
761 g_effective_date := p_date_earned;
762 --
763 -- Dependents
764 --
765 if p_non_resident_flag = 'N' then
766 open csr_new_born;
767 fetch csr_new_born bulk collect into l_new_born_adopted_flag_tbl;
768 close csr_new_born;
769 --
770 for i in 1..l_new_born_adopted_flag_tbl.count loop
771 if l_new_born_adopted_flag_tbl(i) = 'Y' then
772 p_num_of_new_born_adopted := p_num_of_new_born_adopted + 1;
773 end if;
774 end loop;
775 end if;
776 --
777 l_return := get_dependent_info(
778 p_assignment_id,
779 p_date_earned,
780 p_non_resident_flag,
781 p_dpnt_spouse_flag,
782 p_num_of_aged_dpnts,
783 p_num_of_adult_dpnts,
784 p_num_of_underaged_dpnts,
785 p_num_of_dpnts,
786 p_num_of_ageds,
787 p_num_of_disableds,
788 p_female_ee_flag,
789 p_num_of_children,
790 p_num_of_super_ageds,
791 p_num_of_addtl_child); /* Bug 6784288 */
792 return l_return;
793 end get_dependent_info;
794 --------------------------------------------------------------------------------
795 function get_dependent_info(
796 p_assignment_id in number,
797 p_date_earned in date,
798 p_non_resident_flag in varchar2,
799 p_dpnt_spouse_flag out NOCOPY varchar2,
800 p_num_of_aged_dpnts out NOCOPY number,
801 p_num_of_adult_dpnts out NOCOPY number,
802 p_num_of_underaged_dpnts out NOCOPY number,
803 p_num_of_dpnts out NOCOPY number,
804 p_num_of_ageds out NOCOPY number,
805 p_num_of_disableds out NOCOPY number,
806 p_female_ee_flag out NOCOPY varchar2,
807 p_num_of_children out NOCOPY number,
808 p_num_of_super_ageds out NOCOPY number,
809 p_num_of_addtl_child out NOCOPY number) return number /* Bug 6784288 */
810 --------------------------------------------------------------------------------
811 is
812 type t_flag_tbl is table of varchar2(1) index by binary_integer;
813 l_dpnt_spouse_flag_tbl t_flag_tbl;
814 l_aged_dpnt_flag_tbl t_flag_tbl;
815 l_adult_dpnt_flag_tbl t_flag_tbl;
816 l_underaged_dpnt_flag_tbl t_flag_tbl;
817 l_aged_flag_tbl t_flag_tbl;
818 l_super_aged_flag_tbl t_flag_tbl;
819 l_disabled_flag_tbl t_flag_tbl;
820 l_child_flag_tbl t_flag_tbl;
821 l_addtl_child_flag_tbl t_flag_tbl; /* Bug 6784288 */
822 l_sex varchar2(1);
823 l_date_of_birth date;
824 --
825 cursor csr_dpnt is
826 select
827 decode(ctr.cont_information2, 'Y', dpnt_spouse_flag(ctr.contact_type), 'N') DPNT_SPOUSE_FLAG,
828 decode(ctr.cont_information2, 'Y', aged_dpnt_flag(ctr.contact_type, per.national_identifier, p_date_earned), 'N') AGED_DPNT_FLAG,
829 decode(ctr.cont_information2, 'Y', adult_dpnt_flag(ctr.contact_type, per.national_identifier, p_date_earned,nvl(ctr.cont_information4, 'N'),nvl(ctr.cont_information8, 'N')), 'N') ADULT_DPNT_FLAG,
830 decode(ctr.cont_information2, 'Y', underaged_dpnt_flag(ctr.contact_type, per.national_identifier, p_date_earned), 'N') UNDERAGED_DPNT_FLAG,
831 decode(ctr.cont_information3, 'Y', aged_flag(per.national_identifier, p_date_earned), 'N') AGED_FLAG,
832 -- Bug 3172960
836 -- Bug 6784288 Bug 6825145
833 decode(ctr.cont_information3, 'Y', super_aged_flag(per.national_identifier, p_date_earned), 'N') SUPER_AGED_FLAG,
834 nvl(ctr.cont_information4, 'N') DISABLED_FLAG,
835 decode(ctr.cont_information7, 'Y', child_flag(per.national_identifier, p_date_earned), 'N') CHILD_FLAG,
837 decode(ctr.cont_information2, 'Y', decode(ctr.cont_information11,'4',underaged_dpnt_flag(ctr.contact_type, per.national_identifier, p_date_earned),addtl_child_flag(ctr.contact_type, per.national_identifier, p_date_earned)), 'N') ADDTL_CHILD
838 from per_people_f per,
839 per_contact_relationships ctr,
840 per_assignments_f asg
841 where asg.assignment_id = p_assignment_id
842 and p_date_earned
843 between asg.effective_start_date and asg.effective_end_date
844 and ctr.person_id = asg.person_id
845 and ctr.cont_information_category = 'KR'
846 and ctr.cont_information1 = 'Y'
847 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))
848 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))
849 )
850 and per.person_id = ctr.contact_person_id
851 and (
852 (
853 p_date_earned
854 between per.effective_start_date and per.effective_end_date
855 )
856 or
857 (
858 per.start_date = per.effective_start_date
859 and not exists(
860 select null
861 from per_people_f per2
862 where per2.person_id = per.person_id
863 and p_date_earned
864 between per2.effective_start_date and per2.effective_end_date)
865 )
866 );
867 cursor csr_ee is
868 select
869 per.national_identifier,
870 per.marital_status,
871 ni_sex(per.national_identifier) NI_SEX,
872 aged_flag(per.national_identifier, p_date_earned) AGED_FLAG,
873 disabled_flag(per.person_id, p_date_earned) DISABLED_FLAG,
874 child_flag(per.national_identifier, p_date_earned) CHILD_FLAG,
875 nvl(pei.pei_information3,'N') FEMALE_EMP_DOC,
876 super_aged_flag(per.national_identifier, p_date_earned) SUPER_AGED_FLAG -- Bug 4124430
877 from per_people_f per,
878 per_assignments_f asg,
879 per_people_extra_info pei
880 where asg.assignment_id = p_assignment_id
881 and p_date_earned
882 between asg.effective_start_date and asg.effective_end_date
883 and per.person_id = asg.person_id
884 and p_date_earned
885 between per.effective_start_date and per.effective_end_date
886 and per.person_id = pei.person_id(+)
887 and pei.information_type(+) = 'PER_KR_RELATED_YEA_INFORMATION';
888 l_ee csr_ee%ROWTYPE;
889 begin
890 p_dpnt_spouse_flag := 'N';
891 p_num_of_aged_dpnts := 0;
892 p_num_of_adult_dpnts := 0;
893 p_num_of_dpnts := 0;
894 p_num_of_underaged_dpnts := 0;
895 p_num_of_ageds := 0;
896 p_num_of_super_ageds := 0;
897 p_num_of_disableds := 0;
898 p_female_ee_flag := 'N';
899 p_num_of_children := 0;
900 p_num_of_addtl_child := 0; /* Bug 6784288 */
901
902 -- Bug 5080878
903 g_effective_date := p_date_earned;
904 --
905 -- Dependents
906 --
907 if p_non_resident_flag = 'N' then
908 open csr_dpnt;
909 fetch csr_dpnt bulk collect into
910 l_dpnt_spouse_flag_tbl,
911 l_aged_dpnt_flag_tbl,
912 l_adult_dpnt_flag_tbl,
913 l_underaged_dpnt_flag_tbl,
914 l_aged_flag_tbl,
915 l_super_aged_flag_tbl,
916 l_disabled_flag_tbl,
917 l_child_flag_tbl,
918 l_addtl_child_flag_tbl; /* Bug 6784288 */
919 close csr_dpnt;
920 --
921 for i in 1..l_dpnt_spouse_flag_tbl.count loop
922 if l_dpnt_spouse_flag_tbl(i) = 'Y' then
923 p_dpnt_spouse_flag := 'Y';
924 end if;
925 if l_aged_dpnt_flag_tbl(i) = 'Y' then
926 p_num_of_aged_dpnts := p_num_of_aged_dpnts + 1;
927 end if;
928 if l_adult_dpnt_flag_tbl(i) = 'Y' then
929 p_num_of_adult_dpnts := p_num_of_adult_dpnts + 1;
930 end if;
931 if l_underaged_dpnt_flag_tbl(i) = 'Y' then
932 p_num_of_underaged_dpnts := p_num_of_underaged_dpnts + 1;
933 end if;
934 -- Bug 3172960
935 if l_aged_flag_tbl(i) = 'Y' then
936 if l_super_aged_flag_tbl(i) = 'Y' then
937 p_num_of_super_ageds := p_num_of_super_ageds + 1;
938 else
939 p_num_of_ageds := p_num_of_ageds + 1;
940 end if;
941 end if;
942 -- Bug# 3637372
943 -- For 2003 and before the no of super ageds should be counted into no of ageds.
944 --
945 if p_date_earned <= to_date('31/12/2003','dd/mm/yyyy') then
946 p_num_of_ageds := p_num_of_ageds + p_num_of_super_ageds;
947 p_num_of_super_ageds := 0;
948 end if;
949
950 if l_disabled_flag_tbl(i) = 'Y' then
951 p_num_of_disableds := p_num_of_disableds + 1;
952 end if;
953 if l_child_flag_tbl(i) = 'Y' then
954 p_num_of_children := p_num_of_children + 1;
955 end if;
956 -- Bug 6784288
957 if l_addtl_child_flag_tbl(i) = 'Y' then
961 end loop;
958 p_num_of_addtl_child := p_num_of_addtl_child + 1;
959 end if;
960 -- End of Bug 6784288
962 --
963 p_num_of_dpnts := p_num_of_aged_dpnts + p_num_of_adult_dpnts + p_num_of_underaged_dpnts;
964 end if;
965 --
966 -- Employee
967 --
968 open csr_ee;
969 fetch csr_ee into l_ee;
970 close csr_ee;
971 --
972 -- Bug 4124430: Super aged exemption is given for the employee also
973 --
974 if l_ee.aged_flag = 'Y' then
975 if l_ee.super_aged_flag = 'Y' then
976 p_num_of_super_ageds := p_num_of_super_ageds + 1 ;
977 else
978 p_num_of_ageds := p_num_of_ageds + 1;
979 end if ;
980 end if;
981 -- End of bug 4124430
982 if l_ee.disabled_flag = 'Y' then
983 p_num_of_disableds := p_num_of_disableds + 1;
984 end if;
985 if l_ee.child_flag = 'Y' then
986 p_num_of_children := p_num_of_children + 1;
987 end if;
988 --
989 -- Female Employee Tax Exemption
990 -- Modified for Bug 2729763
991 --
992 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
993 p_female_ee_flag := 'Y';
994 end if;
995 --
996 return 0;
997 end get_dependent_info;
998
999 --
1000 -- Employment Insurance Exception codes
1001 -- This function checks for the employee eligibility for EI Prem deduction
1002 -- this is used for formula function KR_EI_LOSS_EXCEPTION_CODES
1003
1004 function get_ei_loss_exception_codes(
1005 p_date_earned in date
1006 ,p_business_group_id in number
1007 ,p_assignment_id in number
1008 ,p_loss_ineligible_flag out nocopy varchar2
1009 ,p_exception_flag out nocopy varchar2
1010 ,p_exception_type out nocopy varchar2
1011 ,p_overlapped_ex_flag out nocopy varchar2
1012 ) return number is
1013
1014 --
1015
1016 cursor csr_ei_loss_code
1017 is
1018 select pei_information8 loss_code
1019 ,pei_information9 loss_date
1020 from per_people_extra_info pei
1021 ,per_people_f pp
1022 ,per_assignments_f paa
1023 ,per_time_periods ptp
1024 where paa.assignment_id = p_assignment_id
1025 and paa.business_group_id = p_business_group_id
1026 and pp.person_id = paa.person_id
1027 and pei.person_id = pp.person_id
1028 and pei.information_type = 'PER_KR_EMPLOYMENT_INS_INFO'
1029 and ptp.payroll_id = paa.payroll_id
1030 and p_date_earned between paa.effective_start_date and paa.effective_end_date
1031 and p_date_earned between pp.effective_start_date and pp.effective_end_date
1032 and p_date_earned between ptp.start_date and ptp.end_date
1033 and ptp.end_date >= fnd_date.canonical_to_date(pei_information9);
1034
1035
1036 cursor csr_ei_exception_code
1037 is
1038 select pei_information1 ei_exception_code
1039 ,pei_information4 ei_exception_type
1040 from per_people_extra_info pei
1041 ,per_assignments_f paa
1042 ,per_time_periods ptp
1043 where paa.assignment_id = p_assignment_id
1044 and paa.business_group_id = p_business_group_id
1045 and pei.person_id = paa.person_id
1046 and pei.information_type ='PER_KR_EI_EXCEPTIONS'
1047 and p_date_earned between paa.effective_start_date and paa.effective_end_date
1048 and ptp.payroll_id = paa.payroll_id
1049 and p_date_earned between ptp.start_date and ptp.end_date
1050 and fnd_date.canonical_to_date(pei.pei_information2) <= ptp.end_date
1051 and fnd_date.canonical_to_date(pei.pei_information3) >= ptp.start_date
1052 order by pei.pei_information2 desc,pei.pei_information3 desc;
1053
1054 l_ei_loss_code per_people_extra_info.pei_information8%TYPE;
1055 l_ei_loss_date per_people_extra_info.pei_information9%TYPE;
1056 l_ei_exception_type per_people_extra_info.pei_information4%TYPE;
1057 l_ei_exception_code per_people_extra_info.pei_information1%TYPE;
1058 l_ei_ex_dummy1 per_people_extra_info.pei_information8%TYPE;
1059 l_ei_ex_dummy2 per_people_extra_info.pei_information9%TYPE;
1060
1061
1062 begin
1063
1064 open csr_ei_loss_code;
1065 fetch csr_ei_loss_code into l_ei_loss_code,l_ei_loss_date;
1066 close csr_ei_loss_code;
1067
1068 if l_ei_loss_code is not null then
1069
1070 p_loss_ineligible_flag := 'Y';
1071 p_exception_flag := 'N';
1072 p_exception_type := null;
1073 p_overlapped_ex_flag := null;
1074
1075 else
1076 p_loss_ineligible_flag := 'N';
1077 p_overlapped_ex_flag := 'N';
1078
1079 open csr_ei_exception_code;
1080 fetch csr_ei_exception_code into l_ei_exception_code,l_ei_exception_type;
1081
1082 if csr_ei_exception_code%FOUND then
1083
1084 p_exception_flag := 'Y';
1085 p_exception_type := l_ei_exception_type;
1086
1087 -- check for overlapped exception codes
1088 fetch csr_ei_exception_code into l_ei_ex_dummy1,l_ei_ex_dummy2;
1089
1090 if csr_ei_exception_code%ROWCOUNT >1 then
1091 p_overlapped_ex_flag := 'Y';
1092 end if;
1093
1094 else
1095
1096 p_exception_flag := 'N';
1100
1097 p_exception_type := null;
1098
1099 end if;
1101 close csr_ei_exception_code;
1102
1103 end if;
1104
1105 return 0;
1106
1107 end get_ei_loss_exception_codes;
1108
1109 -- Bug 4674552: Added function is_exempted_dependent
1110 -- Return: 'Y' if the dependent is eligible
1111 -- for any basic or additional exemption,
1112 -- 'N' otherwise.
1113 --
1114 function is_exempted_dependent(
1115 p_cont_type in per_contact_relationships.contact_type%type,
1116 p_ni in per_people_f.national_identifier%type,
1117 p_itax_dpnt_flag in per_contact_relationships.cont_information2%type,
1118 p_addl_tax_exem_flag in per_contact_relationships.cont_information3%type,
1119 p_addl_disabled_flag in per_contact_relationships.cont_information4%type,
1120 p_addl_exem_flag_child in per_contact_relationships.cont_information7%type,
1121 p_age_ckh_exp_flag in per_contact_relationships.cont_information8%type,
1122 p_eff_date in pay_payroll_actions.effective_date%type,
1123 p_ins_prem_exem_incl_flag in per_contact_relationships.cont_information10%type, -- Bug 4931542
1124 p_med_exp_exem_incl_flag in per_contact_relationships.cont_information12%type, -- Bug 4931542
1125 p_edu_exp_exem_incl_flag in per_contact_relationships.cont_information13%type, -- Bug 4931542
1126 p_card_exp_exem_incl_flag in per_contact_relationships.cont_information14%type, -- Bug 4931542
1127 p_contact_extra_info_id in per_contact_extra_info_f.contact_extra_info_id%type -- Bug 5879106
1128 ) return varchar2 is
1129 --
1130 cursor csr_contact_extra_info(p_cont_extra_info_id number) is
1131 select nvl(cei_information1,0) cei_information1,
1132 nvl(cei_information2,0) cei_information2,
1133 nvl(cei_information3,0) cei_information3,
1134 nvl(cei_information4,0) cei_information4,
1135 nvl(cei_information5,0) cei_information5,
1136 nvl(cei_information6,0) cei_information6,
1137 nvl(cei_information7,0) cei_information7,
1138 nvl(cei_information8,0) cei_information8,
1139 nvl(cei_information9,0) cei_information9,
1140 nvl(cei_information10,0) cei_information10,
1141 nvl(cei_information11,0) cei_information11
1142 --
1143 from per_contact_extra_info_f
1144 --
1145 where contact_Extra_info_id = p_cont_extra_info_id;
1146 --
1147 l_cei_record csr_contact_extra_info%rowtype;
1148 begin
1149 --
1150 g_effective_date := p_eff_date ;
1151
1152 -- Look for basic dependent exemptions: spouse_dpnt, underaged_dpnt, aged_dpnt, and adult_dpnt
1153 --
1154 if nvl(p_itax_dpnt_flag, 'N') = 'Y' then
1155 --
1156 if dpnt_spouse_flag(p_contact_type => p_cont_type) = 'Y' then
1157 return 'Y' ;
1158 end if ;
1159 --
1160 if underaged_dpnt_flag(
1161 p_contact_type => p_cont_type,
1162 p_national_identifier => p_ni,
1163 p_effective_date => p_eff_date
1164 ) = 'Y' then
1165 return 'Y' ;
1166 end if ;
1167 --
1168 if adult_dpnt_flag(
1169 p_contact_type => p_cont_type,
1170 p_national_identifier => p_ni,
1171 p_effective_date => p_eff_date,
1172 p_disabled_flag => nvl(p_addl_disabled_flag, 'N'),
1173 p_age_exception_flag => nvl(p_age_ckh_exp_flag, 'N')
1174 ) = 'Y' then
1175 return 'Y' ;
1176 end if ;
1177 --
1178 if aged_dpnt_flag(
1179 p_contact_type => p_cont_type,
1180 p_national_identifier => p_ni,
1181 p_effective_date => p_eff_date
1182 ) = 'Y' then
1183 return 'Y' ;
1184 end if ;
1185 --
1186 end if ; -- Finished looking for basic exemptions
1187 --
1188 -- Look for additional exemptions
1189 -- Look for additional exemption: aged/superaged
1190 --
1191 if nvl(p_addl_tax_exem_flag, 'N') = 'Y' and aged_flag(p_ni, p_eff_date) = 'Y' then
1192 -- Both Aged and Superaged would be caught here
1193 return 'Y' ;
1194 end if ;
1195 --
1196 -- Look for additional exemption: child
1197 --
1198 if nvl(p_addl_exem_flag_child, 'N') = 'Y' and child_flag(p_ni, p_eff_date) = 'Y' then
1199 return 'Y' ;
1200 end if ;
1201 --
1202 -- Look for additional exemption: disabled
1203 --
1204 if nvl(p_addl_disabled_flag, 'N') = 'Y' then
1205 return 'Y' ;
1206 end if ;
1207 --
1208 -- Bug 5879106. Check dependent expense amounts
1209 -- This check should always be last in this function to avoid running SQL when not required
1210 --
1211 if ((p_eff_date > to_date('31-12-2005','dd-mm-yyyy')) and (p_contact_extra_info_id is not null)) then
1212 --
1213 open csr_contact_extra_info(p_contact_extra_info_id);
1214 fetch csr_contact_extra_info into l_cei_record;
1215 --
1216 if (l_cei_record.cei_information1 + l_cei_record.cei_information2
1217 + l_cei_record.cei_information10 + l_cei_record.cei_information11) > 0
1218 then
1219 return 'Y' ;
1220 end if;
1221 --
1222 if (l_cei_record.cei_information3 + l_cei_record.cei_information4) > 0 then
1223 return 'Y' ;
1224 end if;
1225 --
1226 if (l_cei_record.cei_information5 + l_cei_record.cei_information6) > 0 then
1227 return 'Y' ;
1228 end if;
1229 --
1230 if (l_cei_record.cei_information7 + l_cei_record.cei_information8
1231 + l_cei_record.cei_information9) > 0
1232 then
1233 return 'Y' ;
1234 end if;
1235 --
1236 close csr_contact_extra_info;
1237
1238 elsif (p_eff_date <= to_date('31-12-2005','dd-mm-yyyy')) then
1239 --
1240 if nvl(p_ins_prem_exem_incl_flag, 'N') = 'Y'
1241 or nvl(p_med_exp_exem_incl_flag, 'N') = 'Y'
1242 or nvl(p_edu_exp_exem_incl_flag, 'N') = 'Y'
1243 or nvl(p_card_exp_exem_incl_flag, 'N') = 'Y'
1244 then
1245 return 'Y' ;
1246 end if ;
1247 --
1248 end if;
1249 --
1250 return 'N' ;
1251 --
1252 end is_exempted_dependent ;
1253
1254 -- Bug 4750653: Added function dpnt_eligible_for_basic_exem
1255 -- Return: 'Y' if the dependent is eligible
1256 -- for basic exemption, 'N' otherwise.
1257 --
1258
1259
1260 function dpnt_eligible_for_basic_exem(
1261 p_cont_type in per_contact_relationships.contact_type%type,
1262 p_ni in per_people_f.national_identifier%type,
1263 p_itax_dpnt_flag in per_contact_relationships.cont_information2%type,
1264 p_addl_disabled_flag in per_contact_relationships.cont_information4%type,
1265 p_age_ckh_exp_flag in per_contact_relationships.cont_information8%type,
1266 p_eff_date in pay_payroll_actions.effective_date%type
1267 ) return varchar2
1268 is
1269 begin
1270 -- Bug 5356651
1271 g_effective_date := p_eff_date ;
1272 --
1273 if nvl(p_itax_dpnt_flag, 'N') <> 'Y' then
1274 return 'N' ;
1275 end if ;
1276 --
1277 if dpnt_spouse_flag(p_contact_type => p_cont_type) = 'Y' then
1278 return 'Y' ;
1279 end if ;
1280 --
1281 if underaged_dpnt_flag(
1282 p_contact_type => p_cont_type,
1283 p_national_identifier => p_ni,
1284 p_effective_date => p_eff_date
1285 ) = 'Y' then
1286 return 'Y' ;
1287 end if ;
1288 --
1289 if adult_dpnt_flag(
1290 p_contact_type => p_cont_type,
1291 p_national_identifier => p_ni,
1292 p_effective_date => p_eff_date,
1293 p_disabled_flag => nvl(p_addl_disabled_flag, 'N'),
1294 p_age_exception_flag => nvl(p_age_ckh_exp_flag, 'N')
1295 ) = 'Y' then
1296 return 'Y' ;
1297 end if ;
1298 --
1299 if aged_dpnt_flag(
1300 p_contact_type => p_cont_type,
1301 p_national_identifier => p_ni,
1302 p_effective_date => p_eff_date
1303 ) = 'Y' then
1304 return 'Y' ;
1305 end if ;
1306 --
1307 return 'N' ;
1308 end dpnt_eligible_for_basic_exem ;
1309 --
1310 ---------------------------------------------------------------------------
1311 -- This function checks whether a dependent is eligible for
1312 -- Additional Child Exemption
1313 -- Bug: 4738717
1314 function dpnt_addl_child_exempted(
1315 p_addl_child_exem in varchar2,
1316 p_ni in varchar2,
1317 p_eff_date in date
1318 ) return varchar2
1319 is
1320 l_child_flag varchar2(5);
1321 l_ret varchar2(5);
1322 begin
1323 l_child_flag := child_flag(p_ni, p_eff_date);
1324 if( p_addl_child_exem = 'Y' and l_child_flag = 'Y') then
1325 l_ret := 'Y';
1326 else
1327 l_ret := 'N';
1328 end if;
1329
1330 return l_ret;
1331
1332 end dpnt_addl_child_exempted;
1336 -- procedure get_double_exem_amt for Bug 6716401
1333 ---------------------------------------------------------------------------
1334
1335 ---------------------------------------------------------------------------
1337 procedure get_double_exem_amt(p_assignment_id in per_assignments_f.assignment_id%type,
1338 p_effective_year in varchar2,
1339 p_double_exm_amt out nocopy number)
1340 is
1341
1342
1343 cursor csr_get_ass_act_id(p_assignment_id per_assignments_f.assignment_id%type,
1344 p_effective_year varchar2)
1345 is
1346 SELECT paa.assignment_action_id ass_act_id
1347 from pay_payroll_actions ppa,
1348 pay_assignment_actions paa
1349 where paa.assignment_id = p_assignment_id
1350 and paa.action_status = 'C'
1351 and ppa.payroll_action_id = paa.payroll_action_id
1352 and to_char(ppa.effective_date, 'YYYY') = p_effective_year
1353 and ppa.report_type = 'YEA'
1354 and ppa.report_qualifier = 'KR'
1355 and ppa.action_type in ('B','X')
1356 order by paa.action_sequence desc ;
1357
1358 l_assignment_action_id per_assignments_f.assignment_id%type;
1359
1360 Begin
1361 p_double_exm_amt := 0;
1362 Open csr_get_ass_act_id(p_assignment_id,p_effective_year);
1363 fetch csr_get_ass_act_id into l_assignment_action_id;
1364
1365 if csr_get_ass_act_id%FOUND then
1366
1367 p_double_exm_amt := nvl(pay_kr_report_pkg.get_dbitem_value(l_assignment_action_id,'X_YEA_DOUBLE_EXEM_AMT'),0);
1368 else
1369 p_double_exm_amt := 0;
1370 end if;
1371 close csr_get_ass_act_id;
1372
1373 End get_double_exem_amt;
1374 -----------------------------------------------------------------------------------
1375 -- Bug 6849941: New Validation Checks for Credit Card Fields on the Income Tax Form
1376 -----------------------------------------------------------------------------------
1377 Function enable_credit_card(
1378 p_person_id in number,
1379 p_contact_person_id in number,
1380 p_contact_relationship_id in number,
1381 p_date_earned in date) return varchar2
1382 -----------------------------------------------------------------------------------
1383 is
1384 --
1385 l_itax_law varchar2(2);
1386 l_cont_type varchar2(2);
1387 l_kr_cont_type varchar2(2);
1388 --
1389 cursor csr_dpnt is
1390 select
1391 ctr.cont_information2 itax_law,
1392 nvl(ctr.cont_information11, '0') kr_cont_type,
1393 decode(ctr.contact_type, 'P', '1', 'S', '3', 'A', '4', 'C', '4', 'R', '4', 'O', '4', 'T', '4', '6') cont_type
1394 from
1395 per_contact_relationships ctr
1396 where
1397 ctr.person_id = p_person_id
1398 and ctr.cont_information_category = 'KR'
1399 and ctr.cont_information1 = 'Y'
1400 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))
1401 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))
1402 )
1403 and ctr.contact_person_id = p_contact_person_id
1404 and ctr.contact_relationship_id = p_contact_relationship_id;
1405
1406 begin
1407 --
1408 open csr_dpnt;
1409 fetch csr_dpnt into l_itax_law, l_kr_cont_type, l_cont_type;
1410 close csr_dpnt;
1411 --
1412 if ((l_cont_type in ('1','2','3','4') or l_kr_cont_type in ('1','2','3','4')) and ( l_itax_law = 'Y')) then
1413 return 'Y';
1414 else
1415 return 'N';
1416 end if;
1417 end enable_credit_card;
1418 -----------------------------------------------------------------------------------
1419 -- Bug 7164589: Long Term Care Insurance Premium
1420 -- Bug 7228788: Added a new input parameter to the function for the Input Value Name
1421 -----------------------------------------------------------------------------------
1422 FUNCTION get_long_term_ins_skip_flag(
1423 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
1424 ,p_input_value_name in varchar2
1425 ) RETURN VARCHAR2
1426 IS
1427 --
1428 l_flag pay_run_result_values.result_value%type;
1429 -- Bug 7228788: Added a new argument for the Input Value Name to the cursor
1430 CURSOR csr(l_assignment_action_id pay_assignment_actions.assignment_action_id%type, l_input_value_name varchar2) is
1431 SELECT upper(nvl(prrv.result_value, 'N'))
1432 FROM pay_input_values_f piv
1433 ,pay_run_result_values prrv
1434 ,pay_run_results prr
1435 ,pay_payroll_actions ppa
1436 ,pay_assignment_actions paa
1437 ,pay_element_types_f pet
1438 WHERE paa.assignment_action_id = l_assignment_action_id
1439 and ppa.payroll_action_id = paa.payroll_action_id
1440 and prr.assignment_action_id = paa.assignment_action_id
1441 and prr.status in ('P', 'PA')
1442 and prr.element_type_id = pet.element_type_id
1443 and pet.element_name = 'LTCI_PREM'
1444 and piv.legislation_code = 'KR'
1445 and pet.legislation_code = 'KR'
1446 and prrv.run_result_id = prr.run_result_id
1447 and piv.input_value_id = prrv.input_value_id
1448 and piv.name = l_input_value_name
1449 and ppa.effective_date
1450 between piv.effective_start_date and piv.effective_end_date;
1451 --
1452 BEGIN
1453 --
1454 l_flag := 'N';
1455
1456 OPEN csr(p_assignment_action_id,p_input_value_name);
1457 FETCH csr into l_flag;
1458
1459 IF csr%NOTFOUND THEN
1460 l_flag := 'N';
1461 END IF;
1462
1463 CLOSE csr;
1464
1465 RETURN l_flag;
1466 --
1467 END;
1468 --
1469 -----------------------------------------------------------------------------------
1470 end pay_kr_ff_functions_pkg;