1 package body PAY_NL_TAX_PKG as
2 /* $Header: pynltax.pkb 120.6.12020000.5 2012/12/26 14:50:51 sgmaram ship $ */
3
4 g_package varchar2(33) := ' PAY_NL_TAX_PKG.';
5
6 FUNCTION chk_contribution_exempt (p_assignment_id IN NUMBER
7 ,p_date_earned IN DATE
8 ,p_assignment_action_id IN NUMBER
9 ,p_marginal_flag OUT nocopy VARCHAR2
10 ,p_influence_flag OUT nocopy VARCHAR2
11 ,p_warning OUT nocopy VARCHAR2
12 ) RETURN NUMBER IS
13
14 CURSOR csr_extra_info(c_flex_context IN VARCHAR2) is
15 Select TARGET.*
16 From
17 per_assignment_extra_info TARGET
18 ,pay_assignment_actions PAA
19 ,pay_payroll_actions PACT
20 ,per_time_periods TPERIOD
21 where PAA.assignment_action_id = p_assignment_action_id
22 AND target.information_type = c_flex_context
23 AND target.assignment_id = PAA.assignment_id
24 AND PAA.payroll_action_id = PACT.payroll_action_id
25 AND PACT.payroll_id = TPERIOD.payroll_id
26 AND PACT.date_earned between TPERIOD.start_date and TPERIOD.end_date
27 AND TPERIOD.end_date >= FND_DATE.CANONICAL_TO_DATE(target.AEI_INFORMATION1)
28 AND TPERIOD.start_date <= nvl(FND_DATE.CANONICAL_TO_DATE(target.AEI_INFORMATION2), TPERIOD.start_date);
29
30 /*
31 CURSOR csr_extra_info_Sm_job(c_flex_context IN VARCHAR2) is
32 Select TARGET.*
33 From
34 per_assignment_extra_info TARGET
35 ,pay_assignment_actions PAA
36 ,pay_payroll_actions PACT
37 ,per_time_periods TPERIOD
38 where PAA.assignment_action_id = p_assignment_action_id
39 AND target.information_type = c_flex_context
40 AND target.assignment_id = PAA.assignment_id
41 AND PAA.payroll_action_id = PACT.payroll_action_id
42 AND PACT.payroll_id = TPERIOD.payroll_id
43 AND PACT.date_earned between TPERIOD.start_date and TPERIOD.end_date
44 AND TPERIOD.end_date >= FND_DATE.CANONICAL_TO_DATE(target.AEI_INFORMATION1)
45 AND TPERIOD.start_date <= nvl(FND_DATE.CANONICAL_TO_DATE(target.AEI_INFORMATION2), TPERIOD.start_date)
46 AND to_char(TARGET.aei_information3) = 'F';
47 */
48
49 /*
50 SELECT *
51 FROM per_assignment_extra_info
52 WHERE assignment_id = p_assignment_id
53 AND aei_information_category = c_flex_context;
54 --AND p_date_earned between fnd_date.canonical_to_date(aei_information1)
55 --and nvl(fnd_date.canonical_to_date(aei_information2),to_Date('31/12/4712','dd/mm/yyyy'));
56 */
57
58 l_extra_info csr_extra_info%ROWTYPE;
59 l_marginal_flag varchar2(1):='N';
60 --l_influence_code varchar2(1):='N';
61 l_influence_code varchar2(1):='X';
62 l_zvw_insure varchar2(1):='N';
63
64 BEGIN
65
66 OPEN csr_extra_info('NL_MEI');
67 FETCH csr_extra_info INTO l_extra_info;
68 IF csr_extra_info%FOUND THEN
69 l_marginal_flag:=l_extra_info.AEI_INFORMATION3;
70 END IF;
71 CLOSE csr_extra_info;
72 /*
73 FOR rec in csr_extra_info_Sm_job('NL_INF')
74 LOOP
75 IF csr_extra_info_Sm_job%FOUND THEN
76 IF rec.AEI_INFORMATION3='F' THEN
77 l_influence_code:='Y';
78 END IF;
79 END IF;
80 END LOOP;
81 */
82
83 FOR rec in csr_extra_info('NL_INF')
84 LOOP
85 IF csr_extra_info%FOUND THEN
86 IF rec.AEI_INFORMATION4='Y' THEN
87 l_influence_code:='Y';
88 ELSIF rec.AEI_INFORMATION4='N' THEN
89 l_influence_code:='N';
90 END IF;
91 END IF;
92 END LOOP;
93
94 FOR rec in csr_extra_info('NL_SII')
95 LOOP
96 IF csr_extra_info%FOUND THEN
97 IF rec.AEI_INFORMATION15='J' THEN
98 l_zvw_insure:='Y';
99 END IF;
100 END IF;
101 END LOOP;
102
103 IF l_influence_code='Y' and l_zvw_insure<>'Y'
104 THEN
105 p_warning:=' Code ZVW Insured should be Insured with 0% due to small jobs';
106 END IF;
107
108 p_marginal_flag:= l_marginal_flag;
109 p_influence_flag:= l_influence_code;
110
111 return 1;
112 END chk_contribution_exempt;
113
114 FUNCTION get_age_payroll_period(p_assignment_id IN NUMBER
115 ,p_payroll_id IN NUMBER
116 ,p_date_earned IN DATE) RETURN NUMBER IS
117 --
118 -- Local variables
119 --
120 l_proc VARCHAR2(120) := g_package || 'get_age_payroll_period';
121 l_period_start_date DATE;
122 l_period_end_date DATE;
123 l_dob DATE;
124 l_age_last_day_month NUMBER;
125 --
126 v_last_name varchar2(100);
127 v_asg_number varchar2(50);
128
129 --
130 -- Cursor get_period_dates
131 --
132 CURSOR get_period_dates IS
133 SELECT ptp.start_date start_date
134 ,ptp.end_date end_date
135 FROM per_time_periods ptp
136 WHERE ptp.payroll_id=p_payroll_id
137 AND p_date_earned BETWEEN ptp.start_date AND ptp.end_date;
138 --
139 -- Cursor get_db
140 --
141 CURSOR get_dob IS
142 SELECT date_of_birth,per.last_name,paf.assignment_number
143 FROM per_all_people_f per
144 ,per_all_assignments_f paf
145 WHERE per.person_id = paf.person_id
146 AND paf.assignment_id = p_assignment_id
147 AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date
148 AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date;
149 --
150 BEGIN
151 --
152 -- hr_utility.set_location('Entering:'|| l_proc, 5);
153
154 --
155 OPEN get_period_dates;
156 FETCH get_period_dates INTO l_period_start_date,l_period_end_date;
157 CLOSE get_period_dates;
158 --
159 --
160 --
161 OPEN get_dob;
162 FETCH get_dob INTO l_dob,v_last_name,v_asg_number;
163 CLOSE get_dob;
164
165 hr_utility.set_location('- Name = '|| v_last_name, 5);
166 hr_utility.set_location('- Asg No = '|| v_asg_number, 5);
167
168 l_age_last_day_month := TRUNC(MONTHS_BETWEEN(last_day(p_date_earned),l_dob)/12);
169
170 IF l_dob >= l_period_start_date AND l_dob <= l_period_end_date THEN
171 RETURN(TRUNC(MONTHS_BETWEEN(l_period_end_date,l_dob)/12));
172 ELSE
173 IF l_age_last_day_month >= 65 THEN
174 RETURN l_age_last_day_month;
175 ELSE
176 RETURN(TRUNC(MONTHS_BETWEEN(p_date_earned,l_dob)/12));
177 END IF;
178 END IF;
179 --
180 -- hr_utility.set_location('-l_dob = '|| l_dob, 5);
181 --
182 END get_age_payroll_period;
183 --
184 --
185
186 /* 14629113: Added function to get the age of employee in months as on payroll run */
187 FUNCTION get_age_payroll_period_months(p_assignment_id IN NUMBER
188 ,p_payroll_id IN NUMBER
189 ,p_date_earned IN DATE) RETURN NUMBER IS
190 --
191 -- Local variables
192 --
193 l_proc VARCHAR2(120) := g_package || 'get_age_payroll_period_months';
194 l_period_start_date DATE;
195 l_period_end_date DATE;
196 l_dob DATE;
197 l_age_last_day_month NUMBER;
198 l_standard_pension_age NUMBER;
199 l_current_year NUMBER;
200 --
201 v_last_name varchar2(100);
202 v_asg_number varchar2(50);
203
204 --
205 -- Cursor get_period_dates
206 --
207 CURSOR get_period_dates IS
208 SELECT ptp.start_date start_date
209 ,ptp.end_date end_date
210 FROM per_time_periods ptp
211 WHERE ptp.payroll_id=p_payroll_id
212 AND p_date_earned BETWEEN ptp.start_date AND ptp.end_date;
213 --
214 -- Cursor get_db
215 --
216 CURSOR get_dob IS
217 SELECT date_of_birth,per.last_name,paf.assignment_number
218 FROM per_all_people_f per
219 ,per_all_assignments_f paf
220 WHERE per.person_id = paf.person_id
221 AND paf.assignment_id = p_assignment_id
222 AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date
223 AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date;
224 --
225
226 -- Global to get pension age limit
227 CURSOR get_pension_age_limit(v_period_end_date DATE) IS
228 SELECT fnd_number.canonical_to_number(gbl.global_value)
229 FROM ff_globals_f gbl
230 WHERE gbl.LEGISLATION_CODE = 'NL'
231 AND gbl.GLOBAL_NAME = 'NL_TAX_SENIOR_AGE_MONTHS'
232 AND v_period_end_date between gbl.effective_start_date and gbl.effective_end_date;
233 --
234 BEGIN
235 --
236 -- hr_utility.set_location('Entering:'|| l_proc, 5);
237
238 --
239 OPEN get_period_dates;
240 FETCH get_period_dates INTO l_period_start_date,l_period_end_date;
241 CLOSE get_period_dates;
242 --
243 --
244 OPEN get_pension_age_limit(l_period_end_date);
245 FETCH get_pension_age_limit INTO l_standard_pension_age;
246 CLOSE get_pension_age_limit;
247 --
248 OPEN get_dob;
249 FETCH get_dob INTO l_dob,v_last_name,v_asg_number;
250 CLOSE get_dob;
251
252 hr_utility.set_location('- Name = '|| v_last_name, 5);
253 hr_utility.set_location('- Asg No = '|| v_asg_number, 5);
254
255 l_age_last_day_month := TRUNC(MONTHS_BETWEEN(last_day(p_date_earned),l_dob));
256
257 IF l_dob >= l_period_start_date AND l_dob <= l_period_end_date THEN
258 RETURN(TRUNC(MONTHS_BETWEEN(l_period_end_date,l_dob)));
259 ELSE
260 IF l_age_last_day_month >= l_standard_pension_age THEN
261 RETURN l_age_last_day_month;
262 ELSE
263 RETURN(TRUNC(MONTHS_BETWEEN(p_date_earned,l_dob)));
264 END IF;
265 END IF;
266 --
267 -- hr_utility.set_location('-l_dob = '|| l_dob, 5);
268 --
269 END get_age_payroll_period_months;
270
271 --
272 --
273
274 FUNCTION check_age_payroll_period(p_person_id IN NUMBER
275 ,p_payroll_id IN NUMBER
276 ,p_date_earned IN DATE) RETURN NUMBER IS
277 --
278 -- Local variables
279 --
280 l_proc VARCHAR2(120) := g_package || 'get_age_payroll_period';
281 l_period_start_date DATE;
282 l_period_end_date DATE;
283 l_dob DATE;
284 --
285 -- Cursor get_period_dates
286 --
287 CURSOR get_period_dates IS
288 SELECT ptp.start_date start_date
289 ,ptp.end_date end_date
290 FROM per_time_periods ptp
291 WHERE ptp.payroll_id=p_payroll_id
292 AND p_date_earned BETWEEN ptp.start_date AND ptp.end_date;
293 --
294 -- Cursor get_db
295 --
296 CURSOR get_dob IS
297 SELECT date_of_birth
298 FROM per_all_people_f per
299 WHERE per.person_id = p_person_id
300 AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date;
301
302 --
303 BEGIN
304
305 OPEN get_period_dates;
306 FETCH get_period_dates INTO l_period_start_date,l_period_end_date;
307 CLOSE get_period_dates;
308 --
309 --
310 --
311 OPEN get_dob;
312 FETCH get_dob INTO l_dob;
313 CLOSE get_dob;
314
315
316 IF l_dob >= l_period_start_date AND l_dob <= l_period_end_date THEN
317 RETURN(TRUNC(MONTHS_BETWEEN(l_period_end_date,l_dob)/12));
318 ELSE
319 RETURN(TRUNC(MONTHS_BETWEEN(p_date_earned,l_dob)/12));
320 END IF;
321 --
322 --
323 END check_age_payroll_period;
324 --
325
326 --
327 /* 14629113: Added function to check if employee reached pension age */
328 FUNCTION reached_pension_age(p_person_id IN NUMBER
329 ,p_payroll_id IN NUMBER
330 ,p_date_earned IN DATE) RETURN VARCHAR2 IS
331 --
332 -- Local variables
333 --
334 l_proc VARCHAR2(120) := g_package || 'reached_pension_age';
335 l_period_start_date DATE;
336 l_period_end_date DATE;
337 l_dob DATE;
338 l_age_months NUMBER;
339 l_global_age_limit NUMBER;
340 l_reached_pension_flag varchar2(1);
341 --
342 -- Cursor get_period_dates
343 --
344 CURSOR get_period_dates IS
345 SELECT ptp.start_date start_date
346 ,ptp.end_date end_date
347 FROM per_time_periods ptp
348 WHERE ptp.payroll_id=p_payroll_id
349 AND p_date_earned BETWEEN ptp.start_date AND ptp.end_date;
350 --
351 -- Cursor get_db
352 --
353 CURSOR get_dob IS
354 SELECT date_of_birth
355 FROM per_all_people_f per
356 WHERE per.person_id = p_person_id
357 AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date;
358
359 -- Global to get pension age limit
360 CURSOR get_pension_age_limit IS
361 SELECT fnd_number.canonical_to_number(gbl.global_value)
362 FROM ff_globals_f gbl
363 WHERE gbl.LEGISLATION_CODE = 'NL'
364 AND gbl.GLOBAL_NAME = 'NL_TAX_SENIOR_AGE_MONTHS'
365 AND p_date_earned between gbl.effective_start_date and gbl.effective_end_date;
366 --
367 BEGIN
368
369 OPEN get_period_dates;
370 FETCH get_period_dates INTO l_period_start_date,l_period_end_date;
371 CLOSE get_period_dates;
372 --
373 --
374 --
375 OPEN get_dob;
376 FETCH get_dob INTO l_dob;
377 CLOSE get_dob;
378
379
380 IF l_dob >= l_period_start_date AND l_dob <= l_period_end_date THEN
381 l_age_months := TRUNC(MONTHS_BETWEEN(l_period_end_date,l_dob));
382 ELSE
383 l_age_months := TRUNC(MONTHS_BETWEEN(p_date_earned,l_dob));
384 END IF;
385 --
386 OPEN get_pension_age_limit;
387 FETCH get_pension_age_limit INTO l_global_age_limit;
388 CLOSE get_pension_age_limit;
389
390 IF l_age_months < l_global_age_limit THEN
391 l_reached_pension_flag := 'N';
392 ELSE
393 l_reached_pension_flag := 'Y';
394 END IF;
395
396 RETURN l_reached_pension_flag;
397 --
398 END reached_pension_age;
399 --
400
401 -- Function get_age_calendar_year determines the age of an employee on their birthday,
402 -- which occurs in a specified calendar year and return the value.
403 --
404 FUNCTION get_age_calendar_year(p_assignment_id IN NUMBER
405 ,p_date_earned IN DATE) RETURN NUMBER IS
406 --
407 -- Local variables
408 --
409 l_proc VARCHAR2(120) := g_package || 'get_age_calendar_year';
410 l_dob DATE;
411 l_last_date_tax_year VARCHAR2(12):='31-12-';
412 --
413 -- Cursor get_db
414 --
415 CURSOR get_dob IS
416 SELECT date_of_birth
417 FROM per_all_people_f per
418 ,per_all_assignments_f paf
419 WHERE per.person_id = paf.person_id
420 AND paf.assignment_id = p_assignment_id
421 AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date
422 AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date;
423
424 --
425 BEGIN
426 --
427 hr_utility.set_location('Entering:'|| l_proc, 5);
428 --
429 OPEN get_dob;
430 FETCH get_dob INTO l_dob;
431 CLOSE get_dob;
432 --
433 --Added Code to default DOB to current Date
434 l_dob := NVL(l_dob,p_date_earned);
435 l_last_date_tax_year := l_last_date_tax_year || to_char(p_date_earned,' YYYY');
436 --
437 RETURN(TRUNC(MONTHS_BETWEEN(to_date(l_last_date_tax_year,'DD-MM-YYYY'),l_dob)/12));
438 --
439 END get_age_calendar_year;
440 --
441 --
442
443 /*14629113: Added function to get the age of employee in months as on Tax year */
444 FUNCTION get_age_calendar_year_months(p_assignment_id IN NUMBER
445 ,p_date_earned IN DATE) RETURN NUMBER IS
446 --
447 -- Local variables
448 --
449 l_proc VARCHAR2(120) := g_package || 'get_age_calendar_year_months';
450 l_dob DATE;
451 l_last_date_tax_year VARCHAR2(12):='31-12-';
452 --
453 -- Cursor get_db
454 --
455 CURSOR get_dob IS
456 SELECT date_of_birth
457 FROM per_all_people_f per
458 ,per_all_assignments_f paf
459 WHERE per.person_id = paf.person_id
460 AND paf.assignment_id = p_assignment_id
461 AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date
462 AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date;
463
464 --
465 BEGIN
466 --
467 hr_utility.set_location('Entering:'|| l_proc, 5);
468 --
469 OPEN get_dob;
470 FETCH get_dob INTO l_dob;
471 CLOSE get_dob;
472 --
473 --Added Code to default DOB to current Date
474 l_dob := NVL(l_dob,p_date_earned);
475 l_last_date_tax_year := l_last_date_tax_year || to_char(p_date_earned,' YYYY');
476 --
477 RETURN(TRUNC(MONTHS_BETWEEN(to_date(l_last_date_tax_year,'DD-MM-YYYY'),l_dob)));
478 --
479 END get_age_calendar_year_months;
480 --
481 --
482
483 FUNCTION get_age_system_date(p_assignment_id IN NUMBER
484 ,p_date_earned IN DATE) RETURN NUMBER IS
485 --
486 -- Local variables
487 --
488 l_dob DATE;
489 l_system_date DATE :=p_date_earned;
490 --
491 -- Cursor get_db
492 --
493 CURSOR get_dob IS
494 SELECT date_of_birth
495 FROM per_all_people_f per
496 ,per_all_assignments_f paf
497 WHERE per.person_id = paf.person_id
498 AND paf.assignment_id = p_assignment_id
499 AND l_system_date BETWEEN per.effective_start_date AND per.effective_end_date
500 AND l_system_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
501
502 --
503 BEGIN
504 --
505 --Code to default DOB to current Date
506 l_system_date := NVL(l_system_date,sysdate);
507 --
508 OPEN get_dob;
509 FETCH get_dob INTO l_dob;
510 CLOSE get_dob;
511 --
512 l_dob := NVL(l_dob,l_system_date);
513 --
514 RETURN(TRUNC(MONTHS_BETWEEN(l_system_date,l_dob)/12));
515 --
516 END get_age_system_date;
517 --
518 --
519 FUNCTION chk_lbr_tx_indicator (p_person_id number,p_assignment_id number)
520 return boolean is
521 --
522 --
523 -- Cursor get_lbr_tx_red_ind
524 --
525 CURSOR get_lbr_tx_red_ind(p_person_id number,p_assignment_id number) IS
526 SELECT scl.segment7
527 FROM hr_soft_coding_keyflex scl,per_all_assignments_f paa,fnd_sessions ses
528 WHERE
529 paa.person_id=p_person_id and
530 paa.assignment_id <> nvl(p_assignment_id,-1) and
531 scl.soft_coding_keyflex_id=paa.soft_coding_keyflex_id and
532 scl.segment7='Y' and
533 ses.effective_date between nvl (paa.effective_start_date,sysdate) and
534 nvl(paa.effective_end_date,sysdate) and
535 ses.session_id = userenv ('sessionid');
536 --
537 -- Local variables
538 --
539 l_proc varchar2(72) := g_package || '.get_org_data_items';
540 l_indicator varchar2(6);
541 l_person_id number;
542 l_found boolean:=TRUE;
543 --
544 --
545 BEGIN
546 OPEN get_lbr_tx_red_ind(p_person_id,p_assignment_id);
547 FETCH get_lbr_tx_red_ind into l_indicator;
548 IF get_lbr_tx_red_ind%NOTFOUND THEN
549 l_found:=FALSE;
550 END IF;
551 close get_lbr_tx_red_ind;
552 return l_found;
553 EXCEPTION
554 WHEN NO_DATA_FOUND THEN
555 l_found:=FALSE;
556 return l_found;
557 when others then
558 hr_utility.set_location('Exception :'||l_proc||SQLERRM(SQLCODE),999);
559 END chk_lbr_tx_indicator;
560
561 FUNCTION get_payroll_prd(p_payroll_id number)
562 RETURN VARCHAR2 IS
563 --
564 --
565 -- Cursor get_pay_prd
566 --
567 CURSOR get_pay_prd(p_payroll_id NUMBER) IS
568 SELECT pp.period_type
569 FROM pay_payrolls_f pp,fnd_sessions ses
570 WHERE
571 pp.payroll_id=p_payroll_id and
572 ses.effective_date between nvl(pp.effective_start_date,ses.effective_date) and
573 nvl(pp.effective_end_date,ses.effective_date) and
574 ses.session_id=userenv('sessionid');
575 --
576 -- Local variables
577 --
578 l_pay_prd VARCHAR2(50);
579 l_proc varchar2(72) := g_package || '.get_org_data_items';
580 --
581 BEGIN
582 OPEN get_pay_prd(p_payroll_id);
583 FETCH get_pay_prd into l_pay_prd;
584 return l_pay_prd;
585 EXCEPTION
586 when others then
587 hr_utility.set_location('Exception :'||l_proc||SQLERRM(SQLCODE),999);
588 --
589 END get_payroll_prd;
590
591 PROCEDURE chk_tax_code (p_tax_code in varchar2,
592 p_pay_num in number,
593 p_1_digit out nocopy varchar2,
594 p_2_digit out nocopy varchar2,
595 p_3_digit out nocopy varchar2,
596 p_valid out nocopy boolean
597 ) IS
598 --
599 -- Local variables
600 --
601 l_proc varchar2(72);
602 l_temp number:= p_tax_code;
603 --
604 BEGIN
605
606 p_valid:=TRUE;
607 --hr_utility.set_location('tax code'||p_tax_code||'len'||length(p_tax_code),999);
608 IF length(p_tax_code) <> 3 then
609 p_valid:=false;
610 ELSE
611 p_1_digit:=to_number(substr(p_tax_code,1,1));
612 p_2_digit:=to_number(substr(p_tax_code,2,1));
613 p_3_digit:=to_number(substr(p_tax_code,3,1));
614
615 if p_1_digit not in(0,3,5,6,7,2,9) or p_2_digit not in(1,2) or p_3_digit<>p_pay_num then
616 p_valid:=FALSE;
617 end if;
618 if p_1_digit in (2,9) then
619 p_valid:=TRUE;
620 end if;
621 END IF;
622 END chk_tax_code;
623
624 PROCEDURE get_period_type_code(p_payroll_prd in varchar2,p_period_type out nocopy varchar2,p_period_code out nocopy number) is
625 --
626 BEGIN
627 --
628 IF p_payroll_prd = 'Quarter' THEN
629 p_period_type :='1 - Quarterly';
630 p_period_code :=1;
631 ELSIF p_payroll_prd ='Calendar Month' THEN
632 p_period_type :='2 - Monthly';
633 p_period_code :=2;
634 ELSIF p_payroll_prd = 'Week' THEN
635 p_period_type :='3 - Weekly';
636 p_period_code :=3;
637 ELSIF p_payroll_prd = 'Lunar Month' THEN
638 p_period_type :='4 - Four Weekly';
639 p_period_code :=4;
640 END IF;
641 END get_period_type_code;
642 --
643 PROCEDURE set_spl_inds( p_spl_ind1 in varchar2
644 ,p_spl_ind2 in varchar2
645 ,p_spl_ind3 in varchar2
646 ,p_spl_ind4 in varchar2
647 ,p_spl_ind5 in varchar2
648 ,p_spl_ind6 in varchar2
649 ,p_spl_ind7 in varchar2
650 ,p_spl_ind8 in varchar2
651 ,p_spl_ind9 in varchar2
652 ,p_spl_ind10 in varchar2
653 ,p_spl_ind11 in varchar2
654 ,p_spl_ind12 in varchar2
655 ,p_spl_ind13 in varchar2
656 ,l_set out nocopy boolean
657 ,p_spl_ind out nocopy varchar2) IS
658 --
659 -- Local variables
660 --
661 l_spl_ind varchar2(40):=null;
662 i number:=1;
663 --
664 --Function to check if the special indicator is entered more than once
665 --
666 FUNCTION chk_not_exists (p_segment varchar2)RETURN BOOLEAN Is
667 l_flag BOOLEAN:=true;
668 i number:=1;
669 BEGIN
670 WHILE i < 26 LOOP
671 if substr(p_spl_ind,i,i) is not null then
672 if p_segment=substr(p_spl_ind,i,2) then
673 l_flag:=false;
674 end if;
675 end if;
676 i:=i+2;
677 END LOOP;
678 return l_flag;
679 END chk_not_exists;
680 --
681 BEGIN
682 if (p_spl_ind1 is not null) then
683 if(chk_not_exists(p_spl_ind1)) then
684 p_spl_ind:=p_spl_ind||p_spl_ind1;
685 else
686 l_set:=true;
687 end if;
688 end if;
689 --
690 if (p_spl_ind2 is not null) then
691 if(chk_not_exists(p_spl_ind2)) then
692 p_spl_ind:=p_spl_ind||p_spl_ind2;
693 else
694 l_set:=true;
695 end if;
696 end if;
697 --
698 if (p_spl_ind3 is not null) then
699 if(chk_not_exists(p_spl_ind3)) then
700 p_spl_ind:=p_spl_ind||p_spl_ind3;
701 else
702 l_set:=true;
703 end if;
704 end if;
705 --
706 if (p_spl_ind4 is not null) then
707 if(chk_not_exists(p_spl_ind4)) then
708 p_spl_ind:=p_spl_ind||p_spl_ind4;
709 else
710 l_set:=true;
711 end if;
712 end if;
713 --
714 --
715 if (p_spl_ind5 is not null) then
716 if(chk_not_exists(p_spl_ind5)) then
717 p_spl_ind:=p_spl_ind||p_spl_ind5;
718 else
719 l_set:=true;
720 end if;
721 end if;
722 --
723 if (p_spl_ind6 is not null) then
724 if(chk_not_exists(p_spl_ind6)) then
725 p_spl_ind:=p_spl_ind||p_spl_ind6;
726 else
727 l_set:=true;
728 end if;
729 end if;
730 --
731 if (p_spl_ind7 is not null) then
732 if(chk_not_exists(p_spl_ind7)) then
733 p_spl_ind:=p_spl_ind||p_spl_ind7;
734 else
735 l_set:=true;
736 end if;
737 end if;
738 --
739 if (p_spl_ind8 is not null) then
740 if(chk_not_exists(p_spl_ind8)) then
741 p_spl_ind:=p_spl_ind||p_spl_ind8;
742 else
743 l_set:=true;
744 end if;
745 end if;
746 --
747 if (p_spl_ind9 is not null) then
748 if(chk_not_exists(p_spl_ind9)) then
749 p_spl_ind:=p_spl_ind||p_spl_ind9;
750 else
751 l_set:=true;
752 end if;
753 end if;
754 --
755 if (p_spl_ind10 is not null) then
756 if(chk_not_exists(p_spl_ind10)) then
757 p_spl_ind:=p_spl_ind||p_spl_ind10;
758 else
759 l_set:=true;
760 end if;
761 end if;
762 --
763 if (p_spl_ind11 is not null) then
764 if(chk_not_exists(p_spl_ind11)) then
765 p_spl_ind:=p_spl_ind||p_spl_ind11;
766 else
767 l_set:=true;
768 end if;
769 end if;
770 --
771 if (p_spl_ind12 is not null) then
772 if(chk_not_exists(p_spl_ind12)) then
773 p_spl_ind:=p_spl_ind||p_spl_ind12;
774 else
775 l_set:=true;
776 end if;
777 end if;
778 --
779 if (p_spl_ind13 is not null) then
780 if(chk_not_exists(p_spl_ind13)) then
781 p_spl_ind:=p_spl_ind||p_spl_ind13;
782 else
783 l_set:=true;
784 end if;
785 end if;
786 --
787 --
788
789 END set_spl_inds;
790
791 PROCEDURE get_spl_inds( p_spl_ind in varchar2
792 ,p_spl_ind1 out nocopy varchar2
793 ,p_spl_ind2 out nocopy varchar2
794 ,p_spl_ind3 out nocopy varchar2
795 ,p_spl_ind4 out nocopy varchar2
796 ,p_spl_ind5 out nocopy varchar2
797 ,p_spl_ind6 out nocopy varchar2
798 ,p_spl_ind7 out nocopy varchar2
799 ,p_spl_ind8 out nocopy varchar2
800 ,p_spl_ind9 out nocopy varchar2
801 ,p_spl_ind10 out nocopy varchar2
802 ,p_spl_ind11 out nocopy varchar2
803 ,p_spl_ind12 out nocopy varchar2
804 ,p_spl_ind13 out nocopy varchar2
805 ) IS
806 --
807 -- Local variables
808 --
809 l_spl_ind varchar2(40):=p_spl_ind;
810 --
811 begin
812 p_spl_ind1:=substr(p_spl_ind,1,2);
813 p_spl_ind2:=substr(p_spl_ind,3,2);
814 p_spl_ind3:=substr(p_spl_ind,5,2);
815 p_spl_ind4:=substr(p_spl_ind,7,2);
816 p_spl_ind5:=substr(p_spl_ind,9,2);
817 p_spl_ind6:=substr(p_spl_ind,11,2);
818 p_spl_ind7:=substr(p_spl_ind,13,2);
819 p_spl_ind8:=substr(p_spl_ind,15,2);
820 p_spl_ind9:=substr(p_spl_ind,17,2);
821 p_spl_ind10:=substr(p_spl_ind,19,2);
822 p_spl_ind11:=substr(p_spl_ind,21,2);
823 p_spl_ind12:=substr(p_spl_ind,23,2);
824 p_spl_ind13:=substr(p_spl_ind,25,2);
825 END get_spl_inds;
826 --
827
828 FUNCTION get_age_hire_date(p_business_group_id IN NUMBER
829 ,p_assignment_id IN NUMBER
830 ,p_date_earned IN DATE) RETURN NUMBER IS
831 --
832 -- Local variables
833 --
834 l_dob DATE;
835 l_hire_date DATE;
836
837 --Cursor to get hire date
838
839 CURSOR get_hire_date IS
840 SELECT date_start from
841 per_periods_of_service pps,
842 per_all_assignments_f paa
843 where pps.person_id = paa.person_id
844 and paa.assignment_id = p_assignment_id
845 and pps.business_group_id = p_business_group_id
846 and paa.business_group_id = p_business_group_id
847 and p_date_earned between date_start and nvl(actual_termination_date,hr_general.end_of_time)
848 and p_date_earned between paa.effective_start_date and paa.effective_end_date;
849 --
850 -- Cursor to get_dob
851 --
852 CURSOR get_dob IS
853 SELECT date_of_birth
854 FROM per_all_people_f pap,
855 per_all_assignments_f paa
856 where paa.person_id = pap.person_id
857 and paa.assignment_id = p_assignment_id
858 And p_date_earned between pap.effective_start_date and pap.effective_end_date
859 and p_date_earned between paa.effective_start_date and paa.effective_end_date
860 And pap.business_Group_id = p_business_group_id
861 and paa.business_Group_id = p_business_group_id;
862
863 l_age varchar2(10);
864
865
866 --
867 BEGIN
868 OPEN get_dob;
869 FETCH get_dob INTO l_dob;
870 CLOSE get_dob;
871 --
872 OPEN get_hire_date;
873 FETCH get_hire_date INTO l_hire_date;
874 CLOSE get_hire_date;
875 --
876 RETURN (TRUNC(MONTHS_BETWEEN(l_hire_date,l_dob)/12));
877 --
878 END get_age_hire_date;
879
880 FUNCTION check_age_date_paid(p_assignment_id IN NUMBER
881 ,p_payroll_id IN NUMBER
882 ,p_payroll_action_id IN NUMBER) RETURN NUMBER IS
883 --
884 -- Local variables
885 --
886 l_proc VARCHAR2(120) := g_package || 'check_age_date_paid';
887 l_period_start_date DATE;
888 l_period_end_date DATE;
889 l_dob DATE;
890 l_date_paid DATE;
891 --
892 -- Cursor to get the date paid
893 --
894 Cursor get_paid_date IS
895 SELECT effective_date
896 FROM PAY_PAYROLL_ACTIONS
897 WHERE payroll_action_id = p_payroll_action_id;
898
899 --
900 -- Cursor get_period_dates
901 --
902 CURSOR get_period_dates IS
903 SELECT ptp.start_date start_date
904 ,ptp.end_date end_date
905 FROM per_time_periods ptp
906 WHERE ptp.payroll_id=p_payroll_id
907 AND l_date_paid BETWEEN ptp.start_date AND ptp.end_date;
908 --
909 -- Cursor get_db
910 --
911 CURSOR get_dob IS
912 SELECT date_of_birth
913 FROM per_all_people_f per
914 ,per_all_assignments_f paf
915 WHERE per.person_id = paf.person_id
916 AND paf.assignment_id = p_assignment_id
917 AND l_date_paid BETWEEN per.effective_start_date AND per.effective_end_date
918 AND l_date_paid BETWEEN paf.effective_start_date AND paf.effective_end_date;
919
920 --
921 BEGIN
922
923 OPEN get_paid_date;
924 FETCH get_paid_date INTO l_date_paid;
925 CLOSE get_paid_date;
926
927 OPEN get_period_dates;
928 FETCH get_period_dates INTO l_period_start_date,l_period_end_date;
929 CLOSE get_period_dates;
930 --
931 --
932 --
933 OPEN get_dob;
934 FETCH get_dob INTO l_dob;
935 CLOSE get_dob;
936
937
938 IF l_dob >= l_period_start_date AND l_dob <= l_period_end_date THEN
939 RETURN(TRUNC(MONTHS_BETWEEN(l_period_end_date,l_dob)/12));
940 ELSE
941 RETURN(TRUNC(MONTHS_BETWEEN(l_date_paid,l_dob)/12));
942 END IF;
943 --
944 --
945 END check_age_date_paid;
946
947 /* Function to get Temporary Tax Discount Type */
948 FUNCTION get_temporary_tax_discount
949 (p_assignment_id IN NUMBER
950 ,p_date_earned IN DATE) RETURN NUMBER IS
951
952 CURSOR Cur_ttd_details IS
953 SELECT paei.AEI_INFORMATION3 Discount_Type
954 FROM
955 per_assignment_extra_info paei
956 WHERE
957 paei.assignment_id = p_assignment_id
958 AND p_date_earned between
959 FND_DATE.CANONICAL_TO_DATE(paei.AEI_INFORMATION1)
960 and nvl(FND_DATE.CANONICAL_TO_DATE(paei.AEI_INFORMATION2),hr_general.END_OF_TIME)
961 and paei.INFORMATION_TYPE = 'NL_TTD';
962
963 l_discount_type NUMBER :=-1;
964 l_proc varchar2(100) := g_package||'get_temporary_tax_discount';
965
966 BEGIN
967 hr_utility.set_location('Entering ' || l_proc, 100);
968 hr_utility.set_location('p_assignment_id ' || p_assignment_id, 110);
969 hr_utility.set_location('p_date_earned ' || p_date_earned, 110);
970
971 Open Cur_ttd_details;
972 Fetch Cur_ttd_details INTO l_discount_type;
973
974 IF Cur_ttd_details%NOTFOUND THEN
975 l_discount_type :=-1;
976 END IF;
977
978 Close Cur_ttd_details;
979
980 RETURN l_discount_type;
981 END get_temporary_tax_discount;
982
983 END PAY_NL_TAX_PKG;
[Home] [Help]