1 package body PAY_NL_TAX_PKG as
2 /* $Header: pynltax.pkb 120.1 2006/07/18 05:04:19 niljain noship $ */
3
4 g_package varchar2(33) := ' PAY_NL_TAX_PKG.';
5
6 FUNCTION get_age_payroll_period(p_assignment_id IN NUMBER
7 ,p_payroll_id IN NUMBER
8 ,p_date_earned IN DATE) RETURN NUMBER IS
9 --
10 -- Local variables
11 --
12 l_proc VARCHAR2(120) := g_package || 'get_age_payroll_period';
13 l_period_start_date DATE;
14 l_period_end_date DATE;
15 l_dob DATE;
16 l_age_last_day_month NUMBER;
17 --
18 v_last_name varchar2(100);
19 v_asg_number varchar2(50);
20
21 --
22 -- Cursor get_period_dates
23 --
24 CURSOR get_period_dates IS
25 SELECT ptp.start_date start_date
26 ,ptp.end_date end_date
27 FROM per_time_periods ptp
28 WHERE ptp.payroll_id=p_payroll_id
29 AND p_date_earned BETWEEN ptp.start_date AND ptp.end_date;
30 --
31 -- Cursor get_db
32 --
33 CURSOR get_dob IS
34 SELECT date_of_birth,per.last_name,paf.assignment_number
35 FROM per_all_people_f per
36 ,per_all_assignments_f paf
37 WHERE per.person_id = paf.person_id
38 AND paf.assignment_id = p_assignment_id
39 AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date
40 AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date;
41 --
42 BEGIN
43 --
44 -- hr_utility.set_location('Entering:'|| l_proc, 5);
45
46 --
47 OPEN get_period_dates;
48 FETCH get_period_dates INTO l_period_start_date,l_period_end_date;
49 CLOSE get_period_dates;
50 --
51 --
52 --
53 OPEN get_dob;
54 FETCH get_dob INTO l_dob,v_last_name,v_asg_number;
55 CLOSE get_dob;
56
57 hr_utility.set_location('- Name = '|| v_last_name, 5);
58 hr_utility.set_location('- Asg No = '|| v_asg_number, 5);
59
60 l_age_last_day_month := TRUNC(MONTHS_BETWEEN(last_day(p_date_earned),l_dob)/12);
61
62 IF l_dob >= l_period_start_date AND l_dob <= l_period_end_date THEN
63 RETURN(TRUNC(MONTHS_BETWEEN(l_period_end_date,l_dob)/12));
64 ELSE
65 IF l_age_last_day_month >= 65 THEN
66 RETURN l_age_last_day_month;
67 ELSE
68 RETURN(TRUNC(MONTHS_BETWEEN(p_date_earned,l_dob)/12));
69 END IF;
70 END IF;
71 --
72 -- hr_utility.set_location('-l_dob = '|| l_dob, 5);
73 --
74 END get_age_payroll_period;
75 --
76 --
77
78
79 FUNCTION check_age_payroll_period(p_person_id IN NUMBER
80 ,p_payroll_id IN NUMBER
81 ,p_date_earned IN DATE) RETURN NUMBER IS
82 --
83 -- Local variables
84 --
85 l_proc VARCHAR2(120) := g_package || 'get_age_payroll_period';
86 l_period_start_date DATE;
87 l_period_end_date DATE;
88 l_dob DATE;
89 --
90 -- Cursor get_period_dates
91 --
92 CURSOR get_period_dates IS
93 SELECT ptp.start_date start_date
94 ,ptp.end_date end_date
95 FROM per_time_periods ptp
96 WHERE ptp.payroll_id=p_payroll_id
97 AND p_date_earned BETWEEN ptp.start_date AND ptp.end_date;
98 --
99 -- Cursor get_db
100 --
101 CURSOR get_dob IS
102 SELECT date_of_birth
103 FROM per_all_people_f per
104 WHERE per.person_id = p_person_id
105 AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date;
106
107 --
108 BEGIN
109
110 OPEN get_period_dates;
111 FETCH get_period_dates INTO l_period_start_date,l_period_end_date;
112 CLOSE get_period_dates;
113 --
114 --
115 --
116 OPEN get_dob;
117 FETCH get_dob INTO l_dob;
118 CLOSE get_dob;
119
120
121 IF l_dob >= l_period_start_date AND l_dob <= l_period_end_date THEN
122 RETURN(TRUNC(MONTHS_BETWEEN(l_period_end_date,l_dob)/12));
123 ELSE
124 RETURN(TRUNC(MONTHS_BETWEEN(p_date_earned,l_dob)/12));
125 END IF;
126 --
127 --
128 END check_age_payroll_period;
129 --
130 -- Function get_age_calendar_year determines the age of an employee on their birthday,
131 -- which occurs in a specified calendar year and return the value.
132 --
133 FUNCTION get_age_calendar_year(p_assignment_id IN NUMBER
134 ,p_date_earned IN DATE) RETURN NUMBER IS
135 --
136 -- Local variables
137 --
138 l_proc VARCHAR2(120) := g_package || 'get_age_calendar_year';
139 l_dob DATE;
140 l_last_date_tax_year VARCHAR2(12):='31-12-';
141 --
142 -- Cursor get_db
143 --
144 CURSOR get_dob IS
145 SELECT date_of_birth
146 FROM per_all_people_f per
147 ,per_all_assignments_f paf
148 WHERE per.person_id = paf.person_id
149 AND paf.assignment_id = p_assignment_id
150 AND p_date_earned BETWEEN per.effective_start_date AND per.effective_end_date
151 AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date;
152
153 --
154 BEGIN
155 --
156 hr_utility.set_location('Entering:'|| l_proc, 5);
157 --
158 OPEN get_dob;
159 FETCH get_dob INTO l_dob;
160 CLOSE get_dob;
161 --
162 --Added Code to default DOB to current Date
163 l_dob := NVL(l_dob,p_date_earned);
164 l_last_date_tax_year := l_last_date_tax_year || to_char(p_date_earned,' YYYY');
165 --
166 RETURN(TRUNC(MONTHS_BETWEEN(to_date(l_last_date_tax_year,'DD-MM-YYYY'),l_dob)/12));
167 --
168 END get_age_calendar_year;
169 --
170 --
171 FUNCTION get_age_system_date(p_assignment_id IN NUMBER
172 ,p_date_earned IN DATE) RETURN NUMBER IS
173 --
174 -- Local variables
175 --
176 l_dob DATE;
177 l_system_date DATE :=p_date_earned;
178 --
179 -- Cursor get_db
180 --
181 CURSOR get_dob IS
182 SELECT date_of_birth
183 FROM per_all_people_f per
184 ,per_all_assignments_f paf
185 WHERE per.person_id = paf.person_id
186 AND paf.assignment_id = p_assignment_id
187 AND l_system_date BETWEEN per.effective_start_date AND per.effective_end_date
188 AND l_system_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
189
190 --
191 BEGIN
192 --
193 --Code to default DOB to current Date
194 l_system_date := NVL(l_system_date,sysdate);
195 --
196 OPEN get_dob;
197 FETCH get_dob INTO l_dob;
198 CLOSE get_dob;
199 --
200 l_dob := NVL(l_dob,l_system_date);
201 --
202 RETURN(TRUNC(MONTHS_BETWEEN(l_system_date,l_dob)/12));
203 --
204 END get_age_system_date;
205 --
206 --
207 FUNCTION chk_lbr_tx_indicator (p_person_id number,p_assignment_id number)
208 return boolean is
209 --
210 --
211 -- Cursor get_lbr_tx_red_ind
212 --
213 CURSOR get_lbr_tx_red_ind(p_person_id number,p_assignment_id number) IS
214 SELECT scl.segment7
215 FROM hr_soft_coding_keyflex scl,per_all_assignments_f paa,fnd_sessions ses
216 WHERE
217 paa.person_id=p_person_id and
218 paa.assignment_id <> nvl(p_assignment_id,-1) and
219 scl.soft_coding_keyflex_id=paa.soft_coding_keyflex_id and
220 scl.segment7='Y' and
221 ses.effective_date between nvl (paa.effective_start_date,sysdate) and
222 nvl(paa.effective_end_date,sysdate) and
223 ses.session_id = userenv ('sessionid');
224 --
225 -- Local variables
226 --
227 l_proc varchar2(72) := g_package || '.get_org_data_items';
228 l_indicator varchar2(6);
229 l_person_id number;
230 l_found boolean:=TRUE;
231 --
232 --
233 BEGIN
234 OPEN get_lbr_tx_red_ind(p_person_id,p_assignment_id);
235 FETCH get_lbr_tx_red_ind into l_indicator;
236 IF get_lbr_tx_red_ind%NOTFOUND THEN
237 l_found:=FALSE;
238 END IF;
239 close get_lbr_tx_red_ind;
240 return l_found;
241 EXCEPTION
242 WHEN NO_DATA_FOUND THEN
243 l_found:=FALSE;
244 return l_found;
245 when others then
246 hr_utility.set_location('Exception :'||l_proc||SQLERRM(SQLCODE),999);
247 END chk_lbr_tx_indicator;
248
249 FUNCTION get_payroll_prd(p_payroll_id number)
250 RETURN VARCHAR2 IS
251 --
252 --
253 -- Cursor get_pay_prd
254 --
255 CURSOR get_pay_prd(p_payroll_id NUMBER) IS
256 SELECT pp.period_type
257 FROM pay_payrolls_f pp,fnd_sessions ses
258 WHERE
259 pp.payroll_id=p_payroll_id and
260 ses.effective_date between nvl(pp.effective_start_date,ses.effective_date) and
261 nvl(pp.effective_end_date,ses.effective_date) and
262 ses.session_id=userenv('sessionid');
263 --
264 -- Local variables
265 --
266 l_pay_prd VARCHAR2(50);
267 l_proc varchar2(72) := g_package || '.get_org_data_items';
268 --
269 BEGIN
270 OPEN get_pay_prd(p_payroll_id);
271 FETCH get_pay_prd into l_pay_prd;
272 return l_pay_prd;
273 EXCEPTION
274 when others then
275 hr_utility.set_location('Exception :'||l_proc||SQLERRM(SQLCODE),999);
276 --
277 END get_payroll_prd;
278
279 PROCEDURE chk_tax_code (p_tax_code in varchar2,
280 p_pay_num in number,
281 p_1_digit out nocopy varchar2,
282 p_2_digit out nocopy varchar2,
283 p_3_digit out nocopy varchar2,
284 p_valid out nocopy boolean
285 ) IS
286 --
287 -- Local variables
288 --
289 l_proc varchar2(72);
290 l_temp number:= p_tax_code;
291 --
292 BEGIN
293
294 p_valid:=TRUE;
295 --hr_utility.set_location('tax code'||p_tax_code||'len'||length(p_tax_code),999);
296 IF length(p_tax_code) <> 3 then
297 p_valid:=false;
298 ELSE
299 p_1_digit:=to_number(substr(p_tax_code,1,1));
300 p_2_digit:=to_number(substr(p_tax_code,2,1));
301 p_3_digit:=to_number(substr(p_tax_code,3,1));
302
303 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
304 p_valid:=FALSE;
305 end if;
306 if p_1_digit in (2,9) then
307 p_valid:=TRUE;
308 end if;
309 END IF;
310 END chk_tax_code;
311
312 PROCEDURE get_period_type_code(p_payroll_prd in varchar2,p_period_type out nocopy varchar2,p_period_code out nocopy number) is
313 --
314 BEGIN
315 --
316 IF p_payroll_prd = 'Quarter' THEN
317 p_period_type :='1 - Quarterly';
318 p_period_code :=1;
319 ELSIF p_payroll_prd ='Calendar Month' THEN
320 p_period_type :='2 - Monthly';
321 p_period_code :=2;
322 ELSIF p_payroll_prd = 'Week' THEN
323 p_period_type :='3 - Weekly';
324 p_period_code :=3;
325 ELSIF p_payroll_prd = 'Lunar Month' THEN
326 p_period_type :='4 - Four Weekly';
327 p_period_code :=4;
328 END IF;
329 END get_period_type_code;
330 --
331 PROCEDURE set_spl_inds( p_spl_ind1 in varchar2
332 ,p_spl_ind2 in varchar2
333 ,p_spl_ind3 in varchar2
334 ,p_spl_ind4 in varchar2
335 ,p_spl_ind5 in varchar2
336 ,p_spl_ind6 in varchar2
337 ,p_spl_ind7 in varchar2
338 ,p_spl_ind8 in varchar2
339 ,p_spl_ind9 in varchar2
340 ,p_spl_ind10 in varchar2
341 ,p_spl_ind11 in varchar2
342 ,p_spl_ind12 in varchar2
343 ,p_spl_ind13 in varchar2
344 ,l_set out nocopy boolean
345 ,p_spl_ind out nocopy varchar2) IS
346 --
347 -- Local variables
348 --
349 l_spl_ind varchar2(40):=null;
350 i number:=1;
351 --
352 --Function to check if the special indicator is entered more than once
353 --
354 FUNCTION chk_not_exists (p_segment varchar2)RETURN BOOLEAN Is
355 l_flag BOOLEAN:=true;
356 i number:=1;
357 BEGIN
358 WHILE i < 26 LOOP
359 if substr(p_spl_ind,i,i) is not null then
360 if p_segment=substr(p_spl_ind,i,2) then
361 l_flag:=false;
362 end if;
363 end if;
364 i:=i+2;
365 END LOOP;
366 return l_flag;
367 END chk_not_exists;
368 --
369 BEGIN
370 if (p_spl_ind1 is not null) then
371 if(chk_not_exists(p_spl_ind1)) then
372 p_spl_ind:=p_spl_ind||p_spl_ind1;
373 else
374 l_set:=true;
375 end if;
376 end if;
377 --
378 if (p_spl_ind2 is not null) then
379 if(chk_not_exists(p_spl_ind2)) then
380 p_spl_ind:=p_spl_ind||p_spl_ind2;
381 else
382 l_set:=true;
383 end if;
384 end if;
385 --
386 if (p_spl_ind3 is not null) then
387 if(chk_not_exists(p_spl_ind3)) then
388 p_spl_ind:=p_spl_ind||p_spl_ind3;
389 else
390 l_set:=true;
391 end if;
392 end if;
393 --
394 if (p_spl_ind4 is not null) then
395 if(chk_not_exists(p_spl_ind4)) then
396 p_spl_ind:=p_spl_ind||p_spl_ind4;
397 else
398 l_set:=true;
399 end if;
400 end if;
401 --
402 --
403 if (p_spl_ind5 is not null) then
404 if(chk_not_exists(p_spl_ind5)) then
405 p_spl_ind:=p_spl_ind||p_spl_ind5;
406 else
407 l_set:=true;
408 end if;
409 end if;
410 --
411 if (p_spl_ind6 is not null) then
412 if(chk_not_exists(p_spl_ind6)) then
413 p_spl_ind:=p_spl_ind||p_spl_ind6;
414 else
415 l_set:=true;
416 end if;
417 end if;
418 --
419 if (p_spl_ind7 is not null) then
420 if(chk_not_exists(p_spl_ind7)) then
421 p_spl_ind:=p_spl_ind||p_spl_ind7;
422 else
423 l_set:=true;
424 end if;
425 end if;
426 --
427 if (p_spl_ind8 is not null) then
428 if(chk_not_exists(p_spl_ind8)) then
429 p_spl_ind:=p_spl_ind||p_spl_ind8;
430 else
431 l_set:=true;
432 end if;
433 end if;
434 --
435 if (p_spl_ind9 is not null) then
436 if(chk_not_exists(p_spl_ind9)) then
437 p_spl_ind:=p_spl_ind||p_spl_ind9;
438 else
439 l_set:=true;
440 end if;
441 end if;
442 --
443 if (p_spl_ind10 is not null) then
444 if(chk_not_exists(p_spl_ind10)) then
445 p_spl_ind:=p_spl_ind||p_spl_ind10;
446 else
447 l_set:=true;
448 end if;
449 end if;
450 --
451 if (p_spl_ind11 is not null) then
452 if(chk_not_exists(p_spl_ind11)) then
453 p_spl_ind:=p_spl_ind||p_spl_ind11;
454 else
455 l_set:=true;
456 end if;
457 end if;
458 --
459 if (p_spl_ind12 is not null) then
460 if(chk_not_exists(p_spl_ind12)) then
464 end if;
461 p_spl_ind:=p_spl_ind||p_spl_ind12;
462 else
463 l_set:=true;
465 end if;
466 --
467 if (p_spl_ind13 is not null) then
468 if(chk_not_exists(p_spl_ind13)) then
469 p_spl_ind:=p_spl_ind||p_spl_ind13;
470 else
471 l_set:=true;
472 end if;
473 end if;
474 --
475 --
476
477 END set_spl_inds;
478
479 PROCEDURE get_spl_inds( p_spl_ind in varchar2
480 ,p_spl_ind1 out nocopy varchar2
481 ,p_spl_ind2 out nocopy varchar2
482 ,p_spl_ind3 out nocopy varchar2
483 ,p_spl_ind4 out nocopy varchar2
484 ,p_spl_ind5 out nocopy varchar2
485 ,p_spl_ind6 out nocopy varchar2
486 ,p_spl_ind7 out nocopy varchar2
487 ,p_spl_ind8 out nocopy varchar2
488 ,p_spl_ind9 out nocopy varchar2
489 ,p_spl_ind10 out nocopy varchar2
490 ,p_spl_ind11 out nocopy varchar2
491 ,p_spl_ind12 out nocopy varchar2
492 ,p_spl_ind13 out nocopy varchar2
493 ) IS
494 --
495 -- Local variables
496 --
497 l_spl_ind varchar2(40):=p_spl_ind;
498 --
499 begin
500 p_spl_ind1:=substr(p_spl_ind,1,2);
501 p_spl_ind2:=substr(p_spl_ind,3,2);
502 p_spl_ind3:=substr(p_spl_ind,5,2);
503 p_spl_ind4:=substr(p_spl_ind,7,2);
504 p_spl_ind5:=substr(p_spl_ind,9,2);
505 p_spl_ind6:=substr(p_spl_ind,11,2);
506 p_spl_ind7:=substr(p_spl_ind,13,2);
507 p_spl_ind8:=substr(p_spl_ind,15,2);
508 p_spl_ind9:=substr(p_spl_ind,17,2);
509 p_spl_ind10:=substr(p_spl_ind,19,2);
510 p_spl_ind11:=substr(p_spl_ind,21,2);
511 p_spl_ind12:=substr(p_spl_ind,23,2);
512 p_spl_ind13:=substr(p_spl_ind,25,2);
513 END get_spl_inds;
514 --
515
516 FUNCTION get_age_hire_date(p_business_group_id IN NUMBER
517 ,p_assignment_id IN NUMBER
518 ,p_date_earned IN DATE) RETURN NUMBER IS
519 --
520 -- Local variables
521 --
522 l_dob DATE;
523 l_hire_date DATE;
524
525 --Cursor to get hire date
526
527 CURSOR get_hire_date IS
528 SELECT date_start from
529 per_periods_of_service pps,
530 per_all_assignments_f paa
531 where pps.person_id = paa.person_id
532 and paa.assignment_id = p_assignment_id
533 and pps.business_group_id = p_business_group_id
534 and paa.business_group_id = p_business_group_id
535 and p_date_earned between date_start and nvl(actual_termination_date,hr_general.end_of_time)
536 and p_date_earned between paa.effective_start_date and paa.effective_end_date;
537 --
538 -- Cursor to get_dob
539 --
540 CURSOR get_dob IS
541 SELECT date_of_birth
542 FROM per_all_people_f pap,
543 per_all_assignments_f paa
544 where paa.person_id = pap.person_id
545 and paa.assignment_id = p_assignment_id
546 And p_date_earned between pap.effective_start_date and pap.effective_end_date
547 and p_date_earned between paa.effective_start_date and paa.effective_end_date
548 And pap.business_Group_id = p_business_group_id
549 and paa.business_Group_id = p_business_group_id;
550
551 l_age varchar2(10);
552
553
554 --
555 BEGIN
556 OPEN get_dob;
557 FETCH get_dob INTO l_dob;
558 CLOSE get_dob;
559 --
560 OPEN get_hire_date;
561 FETCH get_hire_date INTO l_hire_date;
562 CLOSE get_hire_date;
563 --
564 RETURN (TRUNC(MONTHS_BETWEEN(l_hire_date,l_dob)/12));
565 --
566 END get_age_hire_date;
567
568
569 END PAY_NL_TAX_PKG;
[Home] [Help]