[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_TAX_UTILS
Source
1 PACKAGE BODY pay_in_tax_utils AS
2 /* $Header: pyintxut.pkb 120.53.12020000.9 2013/04/09 19:27:57 pthummal ship $ */
3
4 type t_rent_paid is table of number index by binary_integer ;
5 type t_assact is table of number index by binary_integer ;
6 type t_month is table of varchar2(30) index by binary_integer ;
7 type t_bal_value is table of number index by binary_integer ;
8 type t_eff_date is table of date index by binary_integer ;
9
10 g_debug boolean ;
11 g_package CONSTANT VARCHAR2(20):= 'pay_in_tax_utils.';
12
13 --------------------------------------------------------------------------
14 -- Name : get_financial_year_start --
15 -- Type : Function --
16 -- Access : Public --
17 -- Description : Function to return the beginning of a tax year --
18 -- Parameters : --
19 -- IN : p_date DATE --
20 -- --
21 --------------------------------------------------------------------------
22 -- Bug 12401381
23 FUNCTION get_tax_year_start (p_payroll_id in number, p_date in date ) return date is
24 l_year varchar2(4);
25 l_procedure VARCHAR2(100);
26 l_message VARCHAR2(250);
27 l_date_offset number;
28 l_pub_sect_flag VARCHAR2(20);
29 BEGIN
30 l_procedure := g_package||'get_tax_year_start';
31 g_debug := hr_utility.debug_enabled;
32 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
33
34 select to_number(nvl(PAY_DATE_OFFSET,0)),PRL_INFORMATION1 into l_date_offset,l_pub_sect_flag
35 from pay_payrolls_f
36 where payroll_id = p_payroll_id
37 and p_date between EFFECTIVE_START_DATE and EFFECTIVE_end_date;
38
39
40
41 pay_in_utils.trace('l_pub_sect_flag ',l_pub_sect_flag);
42
43 if nvl(l_pub_sect_flag,'N') <>'Y' then
44
45
46 if l_date_offset <= 0 then
47
48 if to_number(to_char(p_date,'MM')) >=4 then
49 l_year := to_char(p_date,'YYYY');
50 pay_in_utils.set_location(g_debug,l_procedure,20);
51 else
52 l_year := to_number(to_char(p_date,'YYYY')) -1 ;
53 pay_in_utils.set_location(g_debug,l_procedure,30);
54 end if ;
55 return (to_date('01-04-'||l_year,'DD-MM-YYYY'));
56 else
57 if to_number(to_char(p_date,'MM')) >=3 then
58 l_year := to_char(p_date,'YYYY');
59 pay_in_utils.set_location(g_debug,l_procedure,40);
60 else
61 l_year := to_number(to_char(p_date,'YYYY')) -1 ;
62 pay_in_utils.set_location(g_debug,l_procedure,50);
63 end if ;
64 return (to_date('01-03-'||l_year,'DD-MM-YYYY'));
65 end if;
66
67 else
68 if to_number(to_char(p_date,'MM')) >=3 then
69 l_year := to_char(p_date,'YYYY');
70 pay_in_utils.set_location(g_debug,l_procedure,20);
71 else
72 l_year := to_number(to_char(p_date,'YYYY')) -1 ;
73 pay_in_utils.set_location(g_debug,l_procedure,30);
74 end if ;
75
76 pay_in_utils.trace('tax_yr strt ','01-03-'||l_year);
77 return (to_date('01-03-'||l_year,'DD-MM-YYYY'));
78 end if;
79 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,60);
80
81 END get_tax_year_start;
82
83
84 FUNCTION get_financial_year_start (p_date in date ) return date is
85 l_year varchar2(4);
86 l_procedure VARCHAR2(100);
87 l_message VARCHAR2(250);
88
89 BEGIN
90 l_procedure := g_package||'get_financial_year_start';
91 g_debug := hr_utility.debug_enabled;
92 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
93
94 if to_number(to_char(p_date,'MM')) >=4 then
95 l_year := to_char(p_date,'YYYY');
96 pay_in_utils.set_location(g_debug,l_procedure,20);
97 else
98 l_year := to_number(to_char(p_date,'YYYY')) -1 ;
99 pay_in_utils.set_location(g_debug,l_procedure,30);
100 end if ;
101
102 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
103 return (to_date('01-04-'||l_year,'DD-MM-YYYY'));
104 END get_financial_year_start;
105
106 --------------------------------------------------------------------------
107 -- Name : get_financial_year_end --
108 -- Type : Function --
109 -- Access : Public --
110 -- Description : Function to return the end of a tax year --
111 -- Parameters : --
112 -- IN : p_date DATE --
113 -- --
114 --------------------------------------------------------------------------
115 FUNCTION get_tax_year_end (p_payroll_id in number, p_date in date ) return date is
116 l_year varchar2(4);
117 l_procedure VARCHAR2(100);
118 l_message VARCHAR2(250);
119 l_date_offset number;
120 l_end_date varchar2(10);
121 l_pub_sect_flag VARCHAR2(20);
122 BEGIN
123 l_procedure := g_package||'get_tax_year_end';
124 g_debug := hr_utility.debug_enabled;
125 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
126 l_end_date := '01-FEB-';
127 select to_number(nvl(PAY_DATE_OFFSET,0)),PRL_INFORMATION1 into l_date_offset,l_pub_sect_flag
128 from pay_payrolls_f
129 where payroll_id = p_payroll_id
130 and p_date between EFFECTIVE_START_DATE and EFFECTIVE_end_date;
131
132
133 pay_in_utils.trace('l_date_offset',l_date_offset);
134 pay_in_utils.trace('l_pub_sect_flag ',l_pub_sect_flag);
135
136 if nvl(l_pub_sect_flag,'N') <>'Y' then
137
138 if l_date_offset <= 0 then
139 if to_number(to_char(p_date,'MM')) <=3 then
140 l_year := to_char(p_date,'YYYY');
141 pay_in_utils.set_location(g_debug,l_procedure,20);
142 else
143 l_year := to_number(to_char(p_date,'YYYY')) +1 ;
144 pay_in_utils.set_location(g_debug,l_procedure,30);
145 end if ;
146 return (to_date('31-03-'||l_year,'DD-MM-YYYY')) ;
147 else
148 if to_number(to_char(p_date,'MM')) <=2 then
149 l_year := to_char(p_date,'YYYY');
150 pay_in_utils.set_location(g_debug,l_procedure,40);
151 else
152 l_year := to_number(to_char(p_date,'YYYY')) +1 ;
153 pay_in_utils.set_location(g_debug,l_procedure,50);
154 end if ;
155 return (to_date(to_char(last_day(l_end_date||l_year),'DD-MM-YYYY'),'DD-MM-YYYY')) ;
156 end if;
157
158
159 else
160 if to_number(to_char(p_date,'MM')) <=2 then
161 l_year := to_char(p_date,'YYYY');
162 pay_in_utils.set_location(g_debug,l_procedure,60);
163 else
164 l_year := to_number(to_char(p_date,'YYYY')) +1 ;
165 pay_in_utils.set_location(g_debug,l_procedure,70);
166 end if ;
167 return (to_date(to_char(last_day(l_end_date||l_year),'DD-MM-YYYY'),'DD-MM-YYYY')) ;
168 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
169 end if;
170
171 END get_tax_year_end;
172
173
174
175 -- Bug 12401381
176
177 FUNCTION get_financial_year_end (p_date in date ) return date is
178 l_year varchar2(4);
179 l_procedure VARCHAR2(100);
180 l_message VARCHAR2(250);
181 BEGIN
182 l_procedure := g_package||'get_financial_year_end';
183 g_debug := hr_utility.debug_enabled;
184 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
185
186 if to_number(to_char(p_date,'MM')) <=3 then
187 l_year := to_char(p_date,'YYYY');
188 pay_in_utils.set_location(g_debug,l_procedure,20);
189 else
190 l_year := to_number(to_char(p_date,'YYYY')) +1 ;
191 pay_in_utils.set_location(g_debug,l_procedure,30);
192 end if ;
193
194 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
195 return (to_date('31-03-'||l_year,'DD-MM-YYYY')) ;
196
197 END get_financial_year_end;
198
199 --------------------------------------------------------------------------
200 -- Name : get_metro_status --
201 -- Type : Function --
202 -- Access : Public --
203 -- Description : Function to get the metro status of the assignment --
204 -- Parameters : --
205 -- IN : p_assignment_id NUMBER --
206 -- p_effective_date DATE --
207 -- --
208 --------------------------------------------------------------------------
209 FUNCTION get_metro_status (p_assignment_id in number,
210 p_effective_date in date)
211 return Varchar2 is
212 /*Bug:3907894 Added the date effective check on per_addresses table */
213 cursor c_metro_status
214 is
215 select pad.add_information16
216 from per_addresses pad,
217 per_all_assignments_f paa
218 where paa.assignment_id = p_assignment_id
219 and pad.person_id = paa.person_id
220 and pad.primary_flag = 'Y'
221 and pad.style = 'IN'
222 and p_effective_date between paa.effective_start_date and paa.effective_end_date
223 and p_effective_date between pad.date_from and nvl(pad.date_to,to_date('31-12-4712','DD-MM-YYYY'));
224
225 l_status VARCHAR(2);
226 l_procedure VARCHAR2(250);
227 l_message VARCHAR2(250);
228 BEGIN
229 g_debug := hr_utility.debug_enabled;
230 l_procedure := g_package ||'get_metro_status';
231 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
232
233 IF (g_debug)
234 THEN
235 pay_in_utils.trace('**************************************************','********************');
236 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
237 pay_in_utils.trace('p_assignment_id ',p_assignment_id);
238 pay_in_utils.trace('p_effective_date',p_effective_date);
239 pay_in_utils.trace('**************************************************','********************');
240 END IF;
241
242 l_status :='N';
243
244 open c_metro_status;
245 fetch c_metro_status into l_status;
246 /* Bug 3899924 Added the following IF condition */
247 if c_metro_status%notfound then
248 l_status := 'X';
249 pay_in_utils.set_location(g_debug,l_procedure,20);
250 end if;
251 close c_metro_status;
252
253 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
254 return l_status ;
255
256 END get_metro_status;
257
258 --------------------------------------------------------------------------
259 -- Name : get_period_number --
260 -- Type : Function --
261 -- Access : Public --
262 -- Description : Function to get the payroll period number on a given--
263 -- date --
264 -- Parameters : --
265 -- IN : p_payroll_id NUMBER --
266 -- p_date DATE --
267 --------------------------------------------------------------------------
268 FUNCTION get_period_number
269 ( p_payroll_id in pay_all_payrolls_f.payroll_id%type,
270 p_date in date )
271 return number is
272 l_start_date date;
273 l_end_date date;
274 l_period_num number ;
275
276 cursor csr_get_payroll_period is
277 select start_date
278 , end_date
279 , period_num
280 from per_time_periods
281 where payroll_id = p_payroll_id
282 and p_date between start_date and end_date;
283
284 l_procedure VARCHAR2(250);
285 l_message VARCHAR2(250);
286 BEGIN
287 g_debug := hr_utility.debug_enabled;
288 l_procedure := g_package ||'get_period_number';
289 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
290
291 l_period_num :=-99;
292 open csr_get_payroll_period;
293 fetch csr_get_payroll_period into l_start_date, l_end_date , l_period_num;
294 close csr_get_payroll_period;
295
296 IF (g_debug)
297 THEN
298 pay_in_utils.trace('**************************************************','********************');
299 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
300 pay_in_utils.trace('l_start_date',l_start_date);
301 pay_in_utils.trace('l_end_date',l_end_date);
302 pay_in_utils.trace('l_period_num',l_period_num);
303 END IF;
304 pay_in_utils.trace('**************************************************','********************');
305 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
306 return l_period_num ;
307
308 END get_period_number;
309
310 --------------------------------------------------------------------------
311 -- Name : get_house_rent_info_entry_id --
312 -- Type : Function --
313 -- Access : Private --
314 -- Description : Function to get the EE ID of House Rent Info Element--
315 -- Parameters : --
316 -- IN : --
317 -- --
318 --------------------------------------------------------------------------
319 FUNCTION get_house_rent_info_entry_id
320 (p_assact in pay_assignment_actions.assignment_Action_id%type,
321 p_effective_date in date,
322 p_year_start in date,
323 p_year_end in date,
324 p_entry_id_type_flag out nocopy varchar2 ,
325 p_entry_end_date out nocopy date ) return number
326 is
327 l_houserentinfo_entry_id pay_element_entries_f.element_entry_id%type :=-999;
328
329 l_entry_id_type_flag varchar2(2) ;
330 --
331 -- Note on usage of this flag
332 -- 'E' - entry exists in this pay period
333 -- 'DP' - entry does not exist in this pay period
334 -- but exists in this tax year
335 -- 'DT' - entry does not exist in this tax year
336
337
338 cursor c_ele_id
339 is
340 select pee.element_entry_id,pee.effective_end_date
341 from pay_element_types_f pet
342 ,pay_input_values_f piv
343 ,pay_element_entries_f pee
344 ,pay_element_entry_values_f pev
345 ,pay_assignment_actions pac
346 where pet.element_name ='House Rent Information'
347 and piv.name = 'JAN'
348 and pet.legislation_code ='IN'
349 and pet.element_type_id = piv.element_type_id
350 and piv.input_value_id = pev.input_value_id
351 and pee.element_entry_id = pev.element_entry_id
352 and pee.assignment_id = pac.assignment_id
353 and pac.assignment_action_id = p_assact
354 and p_effective_date between pet.effective_start_date and pet.effective_end_date
355 and p_effective_date between piv.effective_start_date and piv.effective_end_date
356 and p_effective_date between pee.effective_start_date and pee.effective_end_date ;
357
358 cursor c_ele_id_latest
359 is
360 select pee.element_entry_id,pee.effective_end_date
361 from pay_element_types_f pet
362 ,pay_input_values_f piv
363 ,pay_element_entries_f pee
364 ,pay_element_entry_values_f pev
365 ,pay_assignment_actions pac
366 where pet.element_name ='House Rent Information'
367 and piv.name = 'JAN'
368 and pet.legislation_code ='IN'
369 and pet.element_type_id = piv.element_type_id
370 and piv.input_value_id = pev.input_value_id
371 and pee.element_entry_id = pev.element_entry_id
372 and pee.assignment_id = pac.assignment_id
373 and pac.assignment_action_id = p_assact
374 and p_effective_date between pet.effective_start_date and pet.effective_end_date
375 and p_effective_date between piv.effective_start_date and piv.effective_end_date
376 and pee.effective_end_date < p_effective_date
377 and pee.effective_end_date > p_year_start
378 order by pee.effective_end_date desc ;
379
380 l_procedure VARCHAR2(100);
381 l_message VARCHAR2(250);
382 BEGIN
383 l_procedure := g_package||'get_house_rent_info_entry_id';
384 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
385 hr_utility.trace('p_effective_date'||p_effective_date);
386 hr_utility.trace('p_assact'||p_assact);
387
388 l_entry_id_type_flag :='E';
389 open c_ele_id ;
390 fetch c_ele_id into l_houserentinfo_entry_id,p_entry_end_date ;
391 close c_ele_id ;
392
393 hr_utility.trace('p_entry_end_date'||p_entry_end_date);
394
395 pay_in_utils.set_location(g_debug,l_procedure,20);
396
397 if l_houserentinfo_entry_id = -999 then
398 --
399 -- element entry does not exist in the current pay period
400 -- get the latest element entry id
401 --
402 open c_ele_id_latest ;
403 fetch c_ele_id_latest into l_houserentinfo_entry_id,p_entry_end_date ;
404 close c_ele_id_latest ;
405 pay_in_utils.set_location(g_debug,l_procedure,30);
406
407 hr_utility.trace('p_entry_end_date'||p_entry_end_date);
408
409 if l_houserentinfo_entry_id = -999 then
410 --
411 -- ok. entry id still not found in this financial year.
412 -- Set the rent paid value to 0 for all months in this tax year
413 --
414 p_entry_id_type_flag := 'DT';
415 pay_in_utils.set_location(g_debug,l_procedure,40);
416 else
417 p_entry_id_type_flag := 'DP';
418 pay_in_utils.set_location(g_debug,l_procedure,50);
419 end if;
420 else
421 p_entry_id_type_flag := 'E';
422 pay_in_utils.set_location(g_debug,l_procedure,60);
423 end if ;
424
425 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,70);
426 return l_houserentinfo_entry_id ;
427
428 END get_house_rent_info_entry_id;
429
430 --------------------------------------------------------------------------
431 -- Name : get_defined_balance --
432 -- Type : Function --
433 -- Access : Public --
434 -- Description : Function to return the defined balance id --
435 -- Parameters : --
436 -- IN : --
437 -- --
438 --------------------------------------------------------------------------
439 FUNCTION get_defined_balance
440 (p_balance_type in pay_balance_types.balance_name%type
441 , p_dimension_name in pay_balance_dimensions.dimension_name%type)
442 return number
443 is
444 CURSOR csr_def_bal_id
445 IS
446 SELECT pdb.defined_balance_id
447 FROM pay_defined_balances pdb
448 ,pay_balance_types pbt
449 ,pay_balance_dimensions pbd
450 WHERE pbt.balance_name = p_balance_type
451 AND pbd.dimension_name = p_dimension_name
452 AND pdb.balance_type_id = pbt.balance_type_id
453 AND ( pbt.legislation_code = 'IN' OR pbt.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
454 AND ( pbd.legislation_code = 'IN' OR pbd.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
455 AND ( pdb.legislation_code = 'IN' OR pdb.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
456 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
457
458 l_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
459 l_message VARCHAR2(255);
460 l_procedure VARCHAR2(100);
461
462 BEGIN
463 g_debug := hr_utility.debug_enabled;
464 l_procedure := g_package ||'get_defined_balance';
465 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure, 10);
466 l_message := 'SUCCESS';
467
468 OPEN csr_def_bal_id;
469 FETCH csr_def_bal_id
470 INTO l_def_bal_id;
471 CLOSE csr_def_bal_id;
472
473 pay_in_utils.set_location(g_debug,l_procedure, 20);
474
475 IF g_debug THEN
476 hr_utility.trace ('. '||RPAD(TRIM(p_balance_type||p_dimension_name),35,' ')||' : '||l_def_bal_id);
477 END IF;
478
479 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
480
481 RETURN l_def_bal_id;
482
483 EXCEPTION
484 WHEN OTHERS THEN
485 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
486 hr_utility.trace(l_message);
487 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 40);
488 RETURN -1;
489
490 END get_defined_balance ;
491
492 --------------------------------------------------------------------------
493 -- Name : get_monthly_rent --
494 -- Type : Procedure --
495 -- Access : Private --
496 -- Description : Procedure to fetch the monthly rents into a table --
497 -- Parameters : --
498 -- IN : --
499 -- --
500 --------------------------------------------------------------------------
501 PROCEDURE get_monthly_rent
502 (p_element_entry_id in pay_element_entries_f.element_entry_id%type ,
503 p_effective_date in date ,
504 p_entry_type_flag in varchar2,
505 p_entry_end_date in date ,
506 p_payroll_id in pay_all_payrolls_f.payroll_id%type,
507 p_assact_id in pay_assignment_actions.assignment_Action_id%type,
508 p_rent_paid out nocopy t_rent_paid,
509 p_month out nocopy t_month )
510 is
511
512 cursor get_house_rent_entry_id(l_prev_effective_date in date) is
513 select pee.element_entry_id
514 from pay_element_types_f pet
515 ,pay_input_values_f piv
516 ,pay_element_entries_f pee
517 ,pay_element_entry_values_f pev
518 ,pay_assignment_actions pac
519 where pet.element_name ='House Rent Information'
520 and piv.name = 'JAN'
521 and pet.legislation_code ='IN'
522 and pet.element_type_id = piv.element_type_id
523 and piv.input_value_id = pev.input_value_id
524 and pee.element_entry_id = pev.element_entry_id
525 and pee.assignment_id = pac.assignment_id
526 and pac.assignment_action_id = p_assact_id
527 and l_prev_effective_date between pet.effective_start_date and pet.effective_end_date
528 and l_prev_effective_date between piv.effective_start_date and piv.effective_end_date
529 and l_prev_effective_date between pee.effective_start_date and pee.effective_end_date ;
530
531
532 cursor get_mar_month_rent (l_effective_date in date, p_element_entry_id in pay_element_entries_f.element_entry_id%type) is
533 select nvl(pev.screen_entry_value,0)
534 ,piv.name
535 from pay_element_entries_f pee,
536 pay_element_entry_values_f pev,
537 pay_input_values_f piv
538 where pee.element_entry_id = p_element_entry_id
539 and pev.element_entry_id = pee.element_entry_id
540 and pee.element_type_id = piv.element_type_id
541 and piv.name = 'MAR'
542 and pev.input_value_id = piv.input_value_id
543 and l_effective_date between piv.effective_start_date and piv.effective_end_date
544 and l_effective_date between pee.effective_start_date and pee.effective_end_date
545 and l_effective_date between pev.effective_start_date and pev.effective_end_date;
546
547 l_effective_date date;
548 l_procedure VARCHAR2(100);
549 l_message VARCHAR2(250);
550 l_date_offset number;
551 l_rent_paid number;
552 l_month varchar2(6);
553 l_prev_house_rent_entry_date date;
554 l_prev_element_entry_id pay_element_entries_f.element_entry_id%type :=-999;
555 l_pub_sect_flag VARCHAR2(20);
556
557 BEGIN
558 l_procedure := g_package ||'get_monthly_rent';
559 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure, 10);
560 hr_utility.trace('p_element_entry_id'||p_element_entry_id);
561
562
563 if p_entry_end_date < p_effective_date then
564 l_effective_date := p_entry_end_date ;
565 else
566 l_effective_date := p_effective_date ;
567 end if ;
568
569 pay_in_utils.set_location(g_debug,l_procedure, 20);
570 hr_utility.trace('l_effective_date'||l_effective_date);
571
572 select to_number(nvl(PAY_DATE_OFFSET,0)),PRL_INFORMATION1 into l_date_offset,l_pub_sect_flag
573 from pay_payrolls_f
574 where payroll_id = p_payroll_id
575 and l_effective_date between EFFECTIVE_START_DATE and EFFECTIVE_end_date;
576
577 pay_in_utils.set_location(g_debug,l_procedure, 30);
578
579 if l_date_offset <= 0 then
580
581
582 if to_number(to_char(l_effective_date,'MM')) = 3 and nvl(l_pub_sect_flag,'N') ='Y' then
583
584 pay_in_utils.set_location(g_debug,l_procedure, 35);
585
586 open get_mar_month_rent(l_effective_date,p_element_entry_id);
587 fetch get_mar_month_rent into l_rent_paid,l_month;
588 if get_mar_month_rent%found then
589 p_rent_paid(1) := l_rent_paid;
590 p_month(1) := l_month;
591 else
592 p_rent_paid(1) := 0;
593 end if;
594 close get_mar_month_rent;
595 for j in 2..12 loop
596 p_rent_paid(j) := 0 ;
597 end loop;
598 else
599 pay_in_utils.set_location(g_debug,l_procedure, 40);
600
601 select nvl(pev.screen_entry_value,0)
602 ,piv.name
603 bulk collect into
604 p_rent_paid ,
605 p_month
606 from pay_element_entries_f pee,
607 pay_element_entry_values_f pev,
608 pay_input_values_f piv
609 where pee.element_entry_id = p_element_entry_id
610 and pev.element_entry_id = pee.element_entry_id
611 and pee.element_type_id = piv.element_type_id
612 and pev.input_value_id = piv.input_value_id
613 and l_effective_date between piv.effective_start_date and piv.effective_end_date
614 and l_effective_date between pee.effective_start_date and pee.effective_end_date
615 and l_effective_date between pev.effective_start_date and pev.effective_end_date
616 order by decode( piv.name , 'APR',1
617 , 'MAY',2
618 , 'JUN',3
619 , 'JUL',4
620 , 'AUG',5
621 , 'SEP',6
622 , 'OCT',7
623 , 'NOV',8
624 , 'DEC',9
625 , 'JAN',10
626 , 'FEB',11
627 , 'MAR',12
628 );
629 end if;
630 else
631
632 pay_in_utils.set_location(g_debug,l_procedure, 50);
633
634 if to_number(to_char(l_effective_date,'MM')) = 3 then
635
636 pay_in_utils.set_location(g_debug,l_procedure, 60);
637
638 open get_mar_month_rent(l_effective_date,p_element_entry_id);
639 fetch get_mar_month_rent into l_rent_paid,l_month;
640 if get_mar_month_rent%found then
641 p_rent_paid(1) := l_rent_paid;
642 p_month(1) := l_month;
643 else
644 p_rent_paid(1) := 0;
645 end if;
646 close get_mar_month_rent;
647 for j in 2..12 loop
648 p_rent_paid(j) := 0 ;
649 end loop;
650 else
651
652 pay_in_utils.set_location(g_debug,l_procedure, 70);
653
654 select nvl(pev.screen_entry_value,0)
655 ,piv.name
656 bulk collect into
657 p_rent_paid ,
658 p_month
659 from pay_element_entries_f pee,
660 pay_element_entry_values_f pev,
661 pay_input_values_f piv
662 where pee.element_entry_id = p_element_entry_id
663 and pev.element_entry_id = pee.element_entry_id
664 and pee.element_type_id = piv.element_type_id
665 and pev.input_value_id = piv.input_value_id
666 and l_effective_date between piv.effective_start_date and piv.effective_end_date
667 and l_effective_date between pee.effective_start_date and pee.effective_end_date
668 and l_effective_date between pev.effective_start_date and pev.effective_end_date
669 order by decode( piv.name , 'MAR',1
670 , 'APR',2
671 , 'MAY',3
672 , 'JUN',4
673 , 'JUL',5
674 , 'AUG',6
675 , 'SEP',7
676 , 'OCT',8
677 , 'NOV',9
678 , 'DEC',10
679 , 'JAN',11
680 , 'FEB',12
681 );
682
683 pay_in_utils.set_location(g_debug,l_procedure, 80);
684
685 l_prev_house_rent_entry_date := get_tax_year_start(p_payroll_id,l_effective_date);
686
687 open get_house_rent_entry_id(l_prev_house_rent_entry_date);
688 fetch get_house_rent_entry_id into l_prev_element_entry_id;
689 close get_house_rent_entry_id;
690
691 hr_utility.trace('prev_element_entry_id'||l_prev_element_entry_id);
692 p_rent_paid(1) := 0;
693 if l_prev_element_entry_id <> -999 then
694 open get_mar_month_rent(l_prev_house_rent_entry_date,l_prev_element_entry_id);
695 fetch get_mar_month_rent into p_rent_paid(1), p_month(1);
696 close get_mar_month_rent;
697 end if;
698 pay_in_utils.set_location(g_debug,l_procedure, 90);
699 end if;
700 end if;
701
702 if l_date_offset <= 0 then
703
704 if nvl(l_pub_sect_flag,'N') ='Y' then
705 p_month(1) :='MAR';
706 p_month(2) :='APR';
707 p_month(3) :='MAY';
708 p_month(4) :='JUN';
709 p_month(5) :='JUL';
710 p_month(6) :='AUG';
711 p_month(7) :='SEP';
712 p_month(8) :='OCT';
713 p_month(9) :='NOV';
714 p_month(10):='DEC';
715 p_month(11):='JAN';
716 p_month(12):='FEB';
717 else
718 p_month(1) :='APR';
719 p_month(2) :='MAY';
720 p_month(3) :='JUN';
721 p_month(4) :='JUL';
722 p_month(5) :='AUG';
723 p_month(6) :='SEP';
724 p_month(7) :='OCT';
725 p_month(8) :='NOV';
726 p_month(9) :='DEC';
727 p_month(10):='JAN';
728 p_month(11):='FEB';
729 p_month(12):='MAR';
730 end if;
731 else
732 p_month(1) :='MAR';
733 p_month(2) :='APR';
734 p_month(3) :='MAY';
735 p_month(4) :='JUN';
736 p_month(5) :='JUL';
737 p_month(6) :='AUG';
738 p_month(7) :='SEP';
739 p_month(8) :='OCT';
740 p_month(9) :='NOV';
741 p_month(10):='DEC';
742 p_month(11):='JAN';
743 p_month(12):='FEB';
744
745 end if;
746
747 if p_entry_type_flag = 'DT' then
748
749 pay_in_utils.set_location(g_debug,l_procedure, 100);
750
751 for i in 1..12 loop
752 p_rent_paid(i) :=0;
753 end loop;
754 elsif p_entry_type_flag = 'DP' then
755 pay_in_utils.set_location(g_debug,l_procedure, 110);
756 --
757 -- set rent paid for months after the end date as 0
758 --
759 for i in get_period_number(p_payroll_id,p_entry_end_date)+1..12 loop
760 p_rent_paid(i) :=0;
761 end loop ;
762 end if ;
763
764 if g_debug then
765 hr_utility.trace('----------House Rent Information ----------');
766 for i in p_rent_paid.first..p_rent_paid.last loop
767 hr_utility.trace(p_month(i)||'------------------'||p_rent_paid(i));
768 end loop ;
769
770 hr_utility.trace('-------------------------------------------');
771 end if ;
772
773 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure, 120);
774
775 END get_monthly_rent ;
776
777 --------------------------------------------------------------------------
778 -- Name : get_monthly_max_assact --
779 -- Type : Procedure --
780 -- Access : Private --
781 -- Description : This procedure will return the maximum assignment --
782 -- action ids for each month. This will be used to get --
783 -- the HRA related balance values if there is a --
784 -- historical update on the rent paid information. --
785 -- Parameters : --
786 -- IN : --
787 -- --
788 --------------------------------------------------------------------------
789 PROCEDURE get_monthly_max_assact
790 ( p_assignment_id in per_all_assignments_f.assignment_id%type,
791 p_year_start in date ,
792 p_year_end in date ,
793 p_assact_tbl out nocopy t_assact,
794 p_eff_date_tbl out nocopy t_eff_date)
795 IS
796 idx number ;
797 l_month_number_tbl t_Assact; -- number type pl/sql table
798 l_assact_tbl t_Assact;
799 l_eff_date_tbl t_eff_date;
800
801 l_procedure VARCHAR2(100);
802 l_message VARCHAR2(250);
803 BEGIN
804 l_procedure := g_package ||'get_monthly_max_assact';
805 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure, 10);
806
807 /*Bug:3907894 Added ppa.effective_date in the select statement and fetched it in the table l_eff_date_tbl */
808 select paa.assignment_action_id,pay_in_tax_utils.get_period_number(ppa.payroll_id,ppa.date_earned),ppa.date_earned
809 bulk collect into l_assact_tbl,l_month_number_tbl,l_eff_date_tbl
810 from pay_payroll_Actions ppa,
811 pay_assignment_Actions paa,
812 per_assignments_f asg -- Added to remove NMV as per bug 4774108
813 where ppa.payroll_Action_id = paa.payroll_Action_id
814 and paa.assignment_id = p_assignment_id
815 and paa.assignment_id = asg.assignment_id-- Added to remove NMV as per bug 4774108
816 and asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
817 and ppa.action_type in ('B','V','I','R','Q')
818 -- and paa.source_action_id is not null -- Commented for bug 4774514
819 and ppa.date_earned between p_year_start and p_year_end
820 and ppa.date_earned between asg.effective_start_date and asg.effective_end_date
821 and paa.action_sequence = ( select max(pac.action_sequence)
822 from pay_assignment_actions pac
823 ,pay_payroll_actions ppa1
824 where pac.assignment_id = paa.assignment_id
825 and pac.payroll_action_id = ppa1.payroll_action_id
826 and ppa1.date_earned between p_year_start and p_year_end
827 and trunc(ppa.date_earned,'MM') = trunc(ppa1.date_earned,'MM')
828 and ppa1.action_type in ('B','V','I','R','Q')
829 -- and pac.source_action_id is not null -- Commented for bug 4774514
830 )
831 order by decode(to_number(to_char(ppa.date_earned,'MM'))
832 , 4,1
833 , 5,2
834 , 6,3
835 , 7,4
836 , 8,5
837 , 9,6
838 , 10,7
839 , 11,8
840 , 12,9
841 , 1,10
842 , 2,11
843 , 3,12 ) ;
844 --
845 -- reorder the assignment action table for each month if the employee
846 -- joins in the middle of the year.
847 -- assignment action id and effective date for a particular month should be held at the same
848 -- index . eg . APR - 1, MAY 2 ...MAR-12
849 -- for months where no assignment action exists assact will be set to -99
850 -- Bug:3907894 for months where no effective date exists, it would be set to 31/12/4712
851 --
852 if l_month_number_tbl.count >0 then
853 if l_month_number_tbl(1) <> 1 then
854 for i in 1..l_month_number_tbl.last loop
855 p_assact_tbl(l_month_number_tbl(i)):=l_assact_tbl(i) ;
856 p_eff_date_tbl(l_month_number_tbl(i)):=l_eff_date_tbl(i) ;
857 end loop;
858 idx := p_assact_tbl.last ;
859 while idx>= 1 loop
860 if not p_assact_tbl.exists(idx) then
861 p_assact_tbl(idx):= -99;
862 p_eff_date_tbl(idx):= to_date('31/12/4712','dd/mm/yyyy');
863 end if;
864 idx:= idx-1;
865 end loop ;
866 else
867 p_assact_tbl := l_assact_tbl ;
868 p_eff_date_tbl := l_eff_date_tbl;
869 end if;
870 end if;
871
872
873 if g_debug then
874 hr_utility.trace('----------Maximum Assignment action ----------');
875 for i in 1..p_assact_tbl.count loop
876 hr_utility.trace(p_assact_tbl(i));
877 end loop ;
878 hr_utility.trace('-------------------------------------------');
879 end if ;
880
881 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure, 10);
882 END get_monthly_max_assact;
883
884 --------------------------------------------------------------------------
885 -- Name : hra_tax_rule --
886 -- Type : Procedure --
887 -- Access : Private --
888 -- Description : This procedure encapsulates the actual hra rule --
889 -- Parameters : --
890 -- IN : --
891 -- --
892 --------------------------------------------------------------------------
893 PROCEDURE hra_tax_rule( hra_received in number ,
894 rent_paid in number ,
895 hra_salary in number ,
896 metro_flag in varchar2 ,
897 taxable_hra out nocopy number ,
898 exempt_hra out nocopy number )
899 IS
900 l_percent number;
901 l_procedure VARCHAR2(100);
902 l_message VARCHAR2(250);
903
904 BEGIN
905 l_procedure := g_package ||'hra_tax_rule';
906 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure, 10);
907
908 if g_debug then
909 hr_utility.trace('---------------------------------------');
910 hr_utility.trace('hra received -'||hra_received);
911 hr_utility.trace('hra salary - '||hra_salary);
912 hr_utility.trace('rent paid - '||rent_paid);
913 end if ;
914
915 if metro_flag = 'Y' then
916 l_percent :=0.5 ;
917 else
918 l_percent :=0.4 ;
919 end if ;
920
921 exempt_hra := least ( hra_salary*l_percent , hra_received, greatest((rent_paid - 0.10 * hra_salary ),0)) ;
922 taxable_hra := greatest((hra_received - exempt_hra),0) ;
923
924 if g_debug then
925 hr_utility.trace('exempt_hra - '||exempt_hra);
926 hr_utility.trace('taxable hra -'||taxable_hra);
927 hr_utility.trace('---------------------------------------');
928 END IF;
929 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure, 20);
930
931 END hra_tax_rule;
932
933 --------------------------------------------------------------------------
934 -- Name : historical_update_exists --
935 -- Type : Function --
936 -- Access : Private --
937 -- Description : Function to check if there is any update on House --
938 -- Rent Information element in this tax year --
939 -- Parameters : --
940 -- IN : --
941 -- --
942 --------------------------------------------------------------------------
943 FUNCTION historical_update_exists( p_element_entry_id in number ,
944 p_year_start in date ,
945 p_year_end in date ,
946 p_effective_date in date )
947 return boolean
948 is
949
950 l_exists varchar2(1) ;
951
952 cursor c_exists
953 is
954 select 'Y'
955 from dual
956 where exists
957 (
958 select element_entry_id
959 from pay_element_entries_f
960 where element_entry_id=p_element_entry_id
961 and effective_start_date between p_year_start
962 and p_year_end
963 having count(element_entry_id) > 1
964 group by element_entry_id
965 union
966 select element_entry_id
967 from pay_element_entries_f
968 where element_entry_id=p_element_entry_id
969 and effective_start_date > p_year_start
970 and effective_start_date < p_year_end
971 );
972 l_procedure VARCHAR2(100);
973 l_message VARCHAR2(250);
974
975 BEGIN
976 l_procedure := g_package ||'historical_update_exists';
977 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure, 10);
978
979 l_exists :='N' ;
980
981 open c_exists ;
982 fetch c_exists into l_exists ;
983 close c_exists;
984
985 if l_exists ='Y' then
986 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure, 20);
987 return true ;
988 else
989 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure, 30);
990 return false ;
991 end if;
992 end historical_update_exists;
993
994 --------------------------------------------------------------------------
995 -- Name : get_hra_bal_information --
996 -- Type : Procedure --
997 -- Access : Private --
998 -- Description : Procedure to calculate the monthly house rent --
999 -- allowance received and the monthly house rent salary--
1000 -- for an employee.This procedure will be called when --
1001 -- there are historical updates on rent paid information.
1002 -- Parameters : --
1003 -- IN : --
1004 -- --
1005 --------------------------------------------------------------------------
1006 PROCEDURE get_hra_bal_information
1007 ( p_assignment_id in per_all_assignments_f.assignment_id%type,
1008 p_year_start in date ,
1009 p_year_end in date ,
1010 p_hra_tbl out nocopy t_bal_value,
1011 p_hra_sal_tbl out nocopy t_bal_value,
1012 p_eff_date_tbl out nocopy t_eff_date )
1013 is
1014 l_assact_tbl t_assact ;
1015 l_eff_date_tbl t_eff_date;
1016 l_hra_sal_bal_id pay_defined_balances.defined_balance_id%type;
1017 l_hra_alw_bal_id pay_defined_balances.defined_balance_id%type;
1018 l_hra_advance_alw_bal_id pay_defined_balances.defined_balance_id%type;
1019 l_procedure VARCHAR2(100);
1020 l_message VARCHAR2(250);
1021
1022 BEGIN
1023 l_procedure := g_package ||'get_defined_balance';
1024 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure, 10);
1025
1026 --
1027 -- get defined_balance_id
1028 --
1029 l_hra_alw_bal_id := get_defined_balance('House Rent Allowance','_ASG_DE_PTD');
1030 l_hra_advance_alw_bal_id := get_defined_balance( 'Adjusted Advance for HRA','_ASG_DE_PTD');
1031 l_hra_sal_bal_id := get_defined_balance( 'Salary for HRA and Related Exemptions','_ASG_DE_PTD');
1032
1033 /* Bug:3907894 pass p_eff_date_tbl as the out parameter */
1034 get_monthly_max_assact( p_assignment_id , p_year_start, p_year_end , l_assact_tbl,p_eff_date_tbl);
1035
1036 if g_debug then
1037 hr_utility.trace('-----------------------------------------');
1038 end if ;
1039
1040 for i in 1..l_assact_tbl.count loop
1041 IF l_assact_tbl(i) > 0 THEN
1042 p_hra_tbl(i) := pay_balance_pkg.get_value(l_hra_alw_bal_id , l_assact_tbl(i)) +
1043 pay_balance_pkg.get_value(l_hra_advance_alw_bal_id , l_assact_tbl(i));
1044 p_hra_sal_tbl(i) := pay_balance_pkg.get_value(l_hra_sal_bal_id , l_assact_tbl(i)) ;
1045 ELSE
1046 p_hra_tbl(i) := 0 ;
1047 p_hra_sal_tbl(i) := 0 ;
1048 END IF;
1049
1050 if g_debug then
1051 hr_utility.trace('Assact Id -----HRA Allow----HRA Salary--');
1052 hr_utility.trace(l_assact_tbl(i)||'-----'||p_hra_tbl(i)||'------'||p_hra_sal_tbl(i));
1053 end if ;
1054
1055 end loop;
1056
1057 if g_debug then
1058 hr_utility.trace('-----------------------------------------');
1059 end if ;
1060 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure, 10);
1061
1062 end get_hra_bal_information ;
1063
1064 --------------------------------------------------------------------------
1065 -- Name : taxable_hra --
1066 -- Type : Function --
1067 -- Access : Public --
1068 -- Description : Main Function to calculate taxable portion of HRA --
1069 -- This is called from FF IN_CALCULATE_TAXABLE_HRA --
1070 -- Parameters : --
1071 -- IN : --
1072 -- --
1073 --------------------------------------------------------------------------
1074 -- Rev# Date Userid Description --
1075 --------------------------------------------------------------------------
1076 -- 1.1 04-Oct-05 Sukukuma modified this procedure(4638402) --
1077 -- 1.2 02-Feb-07 lnagaraj Used Std value for projection(5859435) --
1078 -- --
1079 --------------------------------------------------------------------------
1080 FUNCTION taxable_hra( p_assact_id in number
1081 ,p_element_entry_id in number
1082 ,p_effective_date in date
1083 ,p_pay_period_num in number
1084 ,p_hra_salary in number
1085 ,p_std_hra_salary in number
1086 ,p_hra_allowance_asg_run in number
1087 ,p_hra_allowance_asg_ytd in number
1088 ,p_std_hra_allow_asg_run in number
1089 ,p_std_hra_allow_asg_ytd in number
1090 ,p_hra_taxable_mth out nocopy number
1091 ,p_hra_taxable_annual out nocopy number
1092 ,p_message out nocopy varchar2)
1093 RETURN NUMBER
1094 IS
1095 /**** Scenarios:
1096 => HRA can be updated in between a year for previous months.
1097 => An employee can claim 80 GG / Rent free accomodation in between the year.
1098 Assumption is - if the employee gets House Rent allowance then
1099 Value of Rent Free accomodation becomes entirely taxable
1100 also the employee can not claim exemption under section 80GG
1101 Logic :
1102 => If there is no date track update on the HRA element in this tax year then there is no
1103 use calculating the hra individually for each month. Balances can be safely used.
1104 calcualte taxable hra only for current month.
1105 => But if there is any date track update on the House Rent Information element then
1106 => we need to recalculate the taxable amount for HRA for the entire tax year.
1107 => Also taxable HRA will be recalculated for the entire tax year in the last month of
1108 of the tax year or termination date
1109 ****/
1110
1111 l_assignment_id per_all_assignments_f.assignment_id%type ;
1112 l_asg_end_date DATE ;
1113 l_last_period_num NUMBER ;
1114 l_payroll_id pay_all_payrolls_f.payroll_id%type;
1115 l_current_month_rent NUMBER ;
1116 l_rent_paid_tbl t_rent_paid ;
1117 l_month_tbl t_month ;
1118 l_hra_tbl t_bal_value ;
1119 l_hra_sal_tbl t_bal_value ;
1120 l_eff_date_tbl t_eff_date;
1121 l_year_start DATE ;
1122 l_year_end DATE ;
1123 l_current_gre_end_date DATE;
1124 l_effective_start_date DATE;
1125 l_hra_salary NUMBER;
1126 l_hra_allowance NUMBER;
1127 l_ret_tax_hra_asg_ytd NUMBER; -- Bug 11821281
1128 l_taxable_hra_asg_ytd NUMBER;
1129 l_taxable_hra_ASG_DE_PTD NUMBER;
1130 l_taxable_hra_proj_ptd NUMBER;
1131 l_taxable_hra NUMBER ;
1132 l_taxable_hra_curr NUMBER ;
1133 l_taxable_hra_proj NUMBER ;
1134 l_exemption_on_hra NUMBER ;
1135 l_metro_status varchar2(1);
1136 l_hri_entry_id pay_element_entries_f.element_entry_id%type;
1137 l_ret_tax_hra_def_bal_id pay_defined_balances.defined_balance_id%type; -- Bug 11821281
1138 l_taxable_hra_def_bal_id pay_defined_balances.defined_balance_id%type;
1139 l_taxable_hra_ptd_bal_id pay_defined_balances.defined_balance_id%type;
1140 l_taxable_hra_proj_bal_id pay_defined_balances.defined_balance_id%type;
1141 l_current_gre hr_soft_coding_keyflex.segment1%type;
1142 l_gre hr_soft_coding_keyflex.segment1%type;
1143 l_entry_type_flag varchar2(2) ;
1144 l_entry_end_date DATE;
1145 l_check_date DATE;
1146 l_terminate_date DATE;
1147 l_last_month DATE;
1148
1149 CURSOR csr_get_current_gre( p_assignment_id per_all_assignments_f.assignment_id%type)
1150 IS
1151 SELECT scl.segment1
1152 FROM hr_soft_coding_keyflex scl
1153 ,per_all_assignments_f paf
1154 WHERE paf.assignment_id=p_assignment_id
1155 AND paf.SOFT_CODING_KEYFLEX_ID=scl.SOFT_CODING_KEYFLEX_ID
1156 AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
1157
1158 CURSOR csr_get_date( p_assignment_id per_all_assignments_f.assignment_id%type
1159 ,p_check_date DATE)
1160 IS
1161 SELECT scl.segment1
1162 ,paf.effective_start_date
1163 FROM hr_soft_coding_keyflex scl,
1164 per_all_assignments_f paf
1165 WHERE paf.assignment_id=p_assignment_id
1166 AND paf.SOFT_CODING_KEYFLEX_ID=scl.SOFT_CODING_KEYFLEX_ID
1167 AND paf.effective_start_date BETWEEN p_effective_date AND p_check_date ;
1168
1169 l_procedure VARCHAR2(100);
1170 l_message VARCHAR2(250);
1171
1172 BEGIN
1173
1174 g_debug := hr_utility.debug_enabled;
1175 l_procedure := g_package ||'taxable_hra';
1176 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure, 10);
1177
1178 IF (g_debug)
1179 THEN
1180 pay_in_utils.trace('**************************************************','********************');
1181 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
1182 pay_in_utils.trace('p_element_entry_id ',p_element_entry_id );
1183 pay_in_utils.trace('p_effective_date ',p_effective_date );
1184 pay_in_utils.trace('p_pay_period_num ',p_pay_period_num );
1185 pay_in_utils.trace('p_hra_salary ',p_hra_salary );
1186 pay_in_utils.trace('p_std_hra_salary ',p_std_hra_salary );
1187 pay_in_utils.trace('p_hra_allowance_asg_run',p_hra_allowance_asg_run);
1188 pay_in_utils.trace('p_hra_allowance_asg_ytd',p_hra_allowance_asg_ytd);
1189 pay_in_utils.trace('p_std_hra_allow_asg_run',p_std_hra_allow_asg_run);
1190 pay_in_utils.trace('p_std_hra_allow_asg_ytd',p_std_hra_allow_asg_ytd);
1191 pay_in_utils.trace('**************************************************','********************');
1192 END IF;
1193
1194
1195 l_current_month_rent :=0;
1196 l_hra_salary :=0;
1197 l_hra_allowance :=0;
1198 l_ret_tax_hra_asg_ytd :=0; -- Bug 11821281
1199 l_taxable_hra_asg_ytd :=0;
1200 l_taxable_hra :=0;
1201 l_taxable_hra_curr :=0;
1202 l_taxable_hra_proj :=0;
1203 l_exemption_on_hra :=0;
1204 p_message := 'TRUE';
1205
1206 --
1207 -- set global variables like assignment id , year start etc
1208 --
1209
1210 SELECT assignment_id
1211 INTO l_assignment_id
1212 FROM pay_assignment_actions
1213 WHERE assignment_action_id = p_assact_id ;
1214
1215 -- Get the termination date of the employee
1216 SELECT SERVICE.actual_termination_date
1217 INTO l_terminate_date
1218 FROM per_assignments_f ASSIGN,
1219 per_periods_of_service SERVICE
1220 WHERE p_effective_date BETWEEN ASSIGN.effective_start_date AND ASSIGN.effective_end_date
1221 AND ASSIGN.assignment_id = l_assignment_id
1222 AND SERVICE.period_of_Service_id = ASSIGN.period_of_service_id;
1223
1224 SELECT nvl(pps.actual_termination_date,paa.effective_end_Date),payroll_id
1225 INTO l_asg_end_date,l_payroll_id
1226 FROM per_Assignments_f paa,-- Modified this for 4774108 to remove NMV
1227 per_periods_of_Service pps
1228 WHERE paa.assignment_id = l_assignment_id
1229 AND paa.period_of_service_id =pps.period_of_service_id
1230 AND paa.effective_end_date = ( SELECT MAX (b.effective_end_date)
1231 FROM per_all_assignments_f b
1232 WHERE paa.assignment_id=b.assignment_id );
1233
1234 -- get tax year start ,tax year end and assignment end date in case of terminations
1235 --
1236
1237
1238 -- Bug 12401381
1239 l_year_start := pay_in_tax_utils.get_tax_year_start(l_payroll_id,p_effective_date );
1240 l_year_end := pay_in_tax_utils.get_tax_year_end(l_payroll_id,p_effective_date );
1241 l_check_date :=LEAST (l_asg_end_date,l_year_end);
1242
1243 ------------------------------
1244 /*To get current gre_id */
1245 ------------------------------
1246 OPEN csr_get_current_gre(l_assignment_id);
1247 FETCH csr_get_current_gre INTO l_current_gre;
1248 CLOSE csr_get_current_gre;
1249 hr_utility.trace('INHRA:l_current_gre : '||l_current_gre);
1250
1251 --------------------------------------------------------
1252 /*To get the end date of GRE in which payroll is run */
1253 --------------------------------------------------------
1254 OPEN csr_get_date(l_assignment_id,l_check_date);
1255 LOOP
1256 FETCH csr_get_date INTO l_gre,l_effective_start_date;
1257 IF(l_gre<>l_current_gre) THEN
1258 l_current_gre_end_date:=l_effective_start_date-1;
1259 EXIT;
1260 END IF ;
1261 EXIT WHEN csr_get_date%NOTFOUND;
1262 END LOOP;
1263 CLOSE csr_get_date;
1264
1265 hr_utility.trace('INHRA:l_current_gre_end_date : '||l_current_gre_end_date);
1266
1267 pay_in_utils.set_location(g_debug,l_procedure, 20);
1268
1269 --------------------------------
1270 /*IF employee gets terminated*/
1271 --------------------------------
1272 IF l_asg_end_date < l_year_end THEN
1273 --------------------------
1274 /*If GRE gets changed */
1275 --------------------------
1276 IF l_current_gre_end_date IS NOT NULL THEN
1277 l_last_period_num:=get_period_number(l_payroll_id,l_current_gre_end_date);
1278 ELSE
1279 l_last_period_num := get_period_number(l_payroll_id,l_asg_end_date);
1280 END IF;
1281 ELSE
1282 --------------------------
1283 /*If GRE gets changed */
1284 --------------------------
1285 IF l_current_gre_end_date IS NOT NULL THEN
1286 l_last_period_num:=get_period_number(l_payroll_id,l_current_gre_end_date);
1287 ELSE
1288 l_last_period_num := 12;
1289 END IF;
1290 END IF ;
1291
1292 pay_in_utils.set_location(g_debug,l_procedure, 30);
1293
1294 IF g_debug THEN
1295 hr_utility.trace('INHRA: Last Period Number : '||l_last_period_num);
1296 hr_utility.trace('INHRA: current no : '||p_pay_period_num);
1297 hr_utility.trace('INHRA: Assgn End Date : '||l_asg_end_date);
1298 END IF ;
1299
1300 --
1301 -- get metro status of the employee
1302 --
1303
1304 l_metro_status := get_metro_status(l_assignment_id, p_effective_date );
1305 /* Bug 3899924 Added the following condition */
1306 IF l_metro_status = 'X' THEN
1307 p_message := 'FALSE';
1308 END IF;
1309
1310 --
1311 -- get defined balance for Balance Taxable HRA
1312 --
1313
1314 l_taxable_hra_def_bal_id := get_defined_balance('Taxable House Rent Allowance', '_ASG_YTD') ;
1315 l_ret_tax_hra_def_bal_id := get_defined_balance('Retro Taxable House Rent Allowance', '_ASG_YTD') ; -- Bug 11821281
1316
1317 pay_in_utils.set_location(g_debug,l_procedure, 40);
1318
1319 --
1320 -- get element entry id for House Rent information element
1321 --
1322 l_hri_entry_id := get_house_rent_info_entry_id(p_assact_id,
1323 p_effective_date,
1324 l_year_start,
1325 l_year_end,
1326 l_entry_type_flag,
1327 l_entry_end_date );
1328
1329 --
1330 -- get monthly rent paid for each month in the current tax year
1331 -- April = 1 , May =2 ...March = 12
1332 --
1333 pay_in_utils.set_location(g_debug,l_procedure, 50);
1334
1335 get_monthly_rent( l_hri_entry_id,
1336 p_effective_date ,
1337 l_entry_type_flag ,
1338 l_entry_end_Date,
1339 l_payroll_id,
1340 p_assact_id,
1341 l_rent_paid_tbl ,
1342 l_month_tbl ) ;
1343
1344 pay_in_utils.set_location(g_debug,l_procedure, 60);
1345 --
1346 -- Calculate annual value of taxable hra before this run
1347 --
1348
1349 l_last_month := last_day(p_effective_date);
1350
1351 -- Recalculating the HRA for enitre tax year
1352
1353 /* IF ( historical_update_exists(l_hri_entry_id,l_year_start,l_year_end,p_effective_date)
1354 OR l_last_month = l_year_end OR l_terminate_date is NOT NULL )
1355 THEN
1356 p_hra_taxable_annual := 0;
1357 pay_in_utils.set_location(g_debug,l_procedure, 70);
1358
1359 --
1360 -- get monthly balance values for 'House Rent Allowance' and 'HRA Salary'
1361 -- for all the pay periods prior to this run
1362 -- Bug:3907894 Get the effective dates for each run in the table l_eff_date_tbl
1363
1364 get_hra_bal_information ( l_assignment_id,
1365 l_year_start,
1366 l_year_end,
1367 l_hra_tbl,
1368 l_hra_sal_tbl,
1369 l_eff_date_tbl);
1370
1371 IF g_debug THEN
1372 hr_utility.trace('INHRA: ------- HRA Amount--------');
1373 FOR i in 1..l_hra_tbl.count LOOP
1374 hr_utility.trace('INHRA: '||l_hra_tbl(i));
1375 END LOOP ;
1376 hr_utility.trace('INHRA: ------- HRA Salary--------');
1377 FOR i in 1..l_hra_sal_tbl.count loop
1378 hr_utility.trace('INHRA: '||l_hra_sal_tbl(i));
1379 END LOOP ;
1380 END IF ;
1381
1382 -- Bug:3907894 Get the metro status as of the payroll period
1383 For i in 1..l_hra_tbl.count-1 LOOP
1384 hra_tax_rule( l_hra_tbl(i) ,
1385 l_rent_paid_tbl(i),
1386 l_hra_sal_tbl(i) ,
1387 get_metro_status(l_assignment_id, l_eff_date_tbl(i) ),
1388 l_taxable_hra ,
1389 l_exemption_on_hra ) ;
1390 l_taxable_hra_asg_ytd := l_taxable_hra_asg_ytd + l_taxable_hra ;
1391 END LOOP;
1392 ELSE
1393 */
1394 pay_in_utils.set_location(g_debug,l_procedure, 80);
1395
1396 --
1397 -- There is no update on the rent paid information this year
1398 hr_utility.trace('INHRA: --tax_unit_id =>'||l_current_gre);
1399
1400 l_taxable_hra_asg_ytd := pay_balance_pkg.get_value(l_taxable_hra_def_bal_id,
1401 p_assact_id ,
1402 null,
1403 null,
1404 null,
1405 null,
1406 null,
1407 null,
1408 null,
1409 'TRUE');
1410 -- Start of Bug 11821281
1411
1412 l_ret_tax_hra_asg_ytd := pay_balance_pkg.get_value(l_ret_tax_hra_def_bal_id,
1413 p_assact_id ,
1414 null,
1415 null,
1416 null,
1417 null,
1418 null,
1419 null,
1420 null,
1421 'TRUE');
1422 --
1423 l_taxable_hra_asg_ytd := l_taxable_hra_asg_ytd + l_ret_tax_hra_asg_ytd;
1424 -- End of Bug 11821281
1425
1426 -- END IF ;
1427
1428 IF g_debug THEN
1429 hr_utility.trace('INHRA: No update to HRI element. Hence direct results');
1430 hr_utility.trace ('INHRA: Taxable HRA_ASG_YTD : '||l_taxable_hra_asg_ytd);
1431 hr_utility.trace ('INHRA: Retro Taxable HRA_ASG_YTD : '||l_ret_tax_hra_asg_ytd);
1432 END IF;
1433 --
1434 -- Calculate current month's taxable hra
1435 --
1436
1437 l_current_month_rent := l_rent_paid_tbl( p_pay_period_num ) ;
1438
1439
1440 hra_tax_rule( p_hra_allowance_asg_run,
1441 l_current_month_rent ,
1442 p_hra_salary ,
1443 l_metro_status,
1444 l_taxable_hra_curr ,
1445 l_exemption_on_hra ) ;
1446
1447 --
1448 -- calculate projected value of taxable hra for future months in this tax year
1449 --
1450 IF g_debug THEN
1451 hr_utility.trace ('INHRA: Taxable HRA_ASG_DE_PTD : '||l_taxable_hra_curr);
1452 hr_utility.trace ('INHRA: Exemption_on_HRA : '||l_exemption_on_hra);
1453 END IF;
1454
1455 pay_in_utils.set_location(g_debug,l_procedure, 90);
1456 --
1457 -- use only std value for projection
1458 --
1459
1460 l_hra_salary := p_std_hra_salary;
1461 l_hra_allowance := p_std_hra_allow_asg_run;
1462
1463
1464 pay_in_utils.set_location(g_debug,l_procedure, 100);
1465
1466 FOR i in p_pay_period_num+1..l_last_period_num LOOP
1467 hra_tax_rule( l_hra_allowance ,
1468 l_rent_paid_tbl(i),
1469 l_hra_salary,
1470 l_metro_status,
1471 l_taxable_hra,
1472 l_exemption_on_hra ) ;
1473 l_taxable_hra_proj := l_taxable_hra_proj+l_taxable_hra ;
1474 END LOOP ;
1475
1476 IF (l_asg_end_date < l_year_end AND l_asg_end_date < l_year_start)THEN
1477 l_taxable_hra_proj := 0;
1478 END IF;
1479
1480 l_taxable_hra_ptd_bal_id := get_defined_balance('Taxable House Rent Allowance', '_ASG_DE_PTD') ;
1481
1482 l_taxable_hra_ASG_DE_PTD := pay_balance_pkg.get_value(l_taxable_hra_ptd_bal_id,
1483 p_assact_id ,
1484 null,
1485 null,
1486 null,
1487 null,
1488 null,
1489 null,
1490 null,
1491 'TRUE');
1492
1493 l_taxable_hra_proj_bal_id := get_defined_balance('Taxable House Rent Allowance for Projection', '_ASG_DE_PTD') ;
1494
1495 l_taxable_hra_proj_ptd := pay_balance_pkg.get_value(l_taxable_hra_proj_bal_id,
1496 p_assact_id ,
1497 null,
1498 null,
1499 null,
1500 null,
1501 null,
1502 null,
1503 null,
1504 'TRUE');
1505 pay_in_utils.set_location(g_debug,l_procedure, 110);
1506
1507 p_hra_taxable_annual := (l_taxable_hra_curr - l_taxable_hra_ASG_DE_PTD ) +
1508 (l_taxable_hra_proj - (l_taxable_hra_proj_ptd- l_taxable_hra_asg_ytd));
1509
1510 p_hra_taxable_mth := l_taxable_hra_curr - l_taxable_hra_ASG_DE_PTD;
1511
1512 IF g_debug THEN
1513 hr_utility.trace ('INHRA: p_hra_taxable_annual : '||p_hra_taxable_annual);
1514 hr_utility.trace ('INHRA: p_hra_taxable_mth : '||p_hra_taxable_mth);
1515 END IF;
1516
1517 RETURN 0 ;
1518
1519 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure, 120);
1520 END taxable_hra;
1521
1522 --------------------------------------------------------------------------
1523 -- Name : prev_emplr_details --
1524 -- Type : Function --
1525 -- Access : Public --
1526 -- Description : Function to get the Previous Employment Details --
1527 -- Parameters : --
1528 -- IN : --
1529 -- --
1530 --------------------------------------------------------------------------
1531 FUNCTION prev_emplr_details(p_assignment_id in number
1532 ,p_date_earned in date
1533 ,p_prev_sal out nocopy number
1534 ,p_prev_tds out nocopy number
1535 ,p_prev_pt out nocopy number
1536 ,p_prev_ent_alw out NOCOPY number
1537 ,p_prev_pf OUT NOCOPY number
1538 ,p_prev_super OUT NOCOPY number
1539 ,p_prev_govt_ent_alw out nocopy number
1540 ,p_prev_grat OUT NOCOPY NUMBER
1541 ,p_prev_leave_encash OUT NOCOPY NUMBER
1542 ,p_prev_retr_amt OUT NOCOPY NUMBER
1543 ,p_designation OUT NOCOPY VARCHAR2
1544 ,p_annual_sal OUT NOCOPY NUMBER
1545 ,p_pf_number OUT NOCOPY VARCHAR2
1546 ,p_pf_estab_code OUT NOCOPY VARCHAR2
1547 ,p_epf_number OUT NOCOPY VARCHAR2
1548 ,p_emplr_class OUT NOCOPY VARCHAR2
1549 ,p_ltc_curr_block OUT NOCOPY NUMBER
1550 ,p_vrs_amount OUT NOCOPY NUMBER
1551 ,p_prev_sc OUT NOCOPY NUMBER
1552 ,p_prev_cess OUT NOCOPY NUMBER
1553 ,p_prev_exemp_80gg OUT NOCOPY NUMBER
1554 ,p_prev_med_reimburse_amt OUT NOCOPY NUMBER
1555 ,p_prev_sec_and_he_cess OUT NOCOPY NUMBER
1556 ,p_prev_exemp_80ccd OUT NOCOPY NUMBER
1557 ,p_prev_cghs_exemp_80D OUT NOCOPY NUMBER)
1558 Return Number is
1559 /*Bug:3919215 Modified the cursor. selected Employer classification of prev emplr */
1560 Cursor c_prev_emp_details is
1561 select nvl(ppm.pem_information1,'X'), -- Designation
1562 fnd_number.canonical_to_number(nvl(ppm.pem_information2,0)), -- Annual Salary
1563 nvl(ppm.pem_information3,'X'), -- PF Number
1564 nvl(ppm.pem_information4,'X'), -- PF Establishment Code
1565 nvl(ppm.pem_information5,'X'), -- EPF Number
1566 nvl(ppm.pem_information6,'X'), -- Emplr class
1567 fnd_number.canonical_to_number(nvl(ppm.pem_information8,0)), -- LTC Curr
1568 fnd_number.canonical_to_number(nvl(ppm.pem_information9,0)), -- Leave Encashment
1569 fnd_number.canonical_to_number(nvl(ppm.pem_information10,0)), -- Gratuity
1570 fnd_number.canonical_to_number(nvl(ppm.pem_information11,0)), -- Retrenchment Amount
1571 fnd_number.canonical_to_number(nvl(ppm.pem_information12,0)), -- VRS
1572 fnd_number.canonical_to_number(nvl(ppm.pem_information13,0)), -- Gross Sal
1573 fnd_number.canonical_to_number(nvl(ppm.pem_information14,0)), -- PF
1574 fnd_number.canonical_to_number(nvl(ppm.pem_information15,0)), -- Ent Alw
1575 fnd_number.canonical_to_number(nvl(ppm.pem_information16,0)), -- PT
1576 fnd_number.canonical_to_number(nvl(ppm.pem_information17,0)), -- TDS
1577 fnd_number.canonical_to_number(nvl(ppm.pem_information18,0)), -- Superannuation
1578 fnd_number.canonical_to_number(nvl(ppm.pem_information19,0)), -- Prev Surcharge
1579 fnd_number.canonical_to_number(nvl(ppm.pem_information20,0)), -- Prev Cess
1580 fnd_number.canonical_to_number(nvl(ppm.pem_information21,0)), -- Exemption under 80gg
1581 fnd_number.canonical_to_number(nvl(ppm.pem_information22,0)), -- Medical Reimbursement
1582 fnd_number.canonical_to_number(nvl(ppm.pem_information23,0)), -- Sec and HE Cess
1583 fnd_number.canonical_to_number(nvl(ppm.pem_information24,0)), -- Exemption under 80ccd
1584 fnd_number.canonical_to_number(nvl(ppm.pem_information25,0)), -- CGHS Exemption under 80D
1585 ppm.end_date
1586 from per_previous_employers ppm,
1587 per_all_assignments_f paa
1588 where paa.assignment_id = p_assignment_id
1589 and paa.person_id = ppm.person_id
1590 and p_date_earned between paa.effective_start_date and paa.effective_end_date;
1591
1592 l_start DATE;
1593 l_end DATE;
1594 l_sal NUMBER;
1595 l_ent NUMBER;
1596 l_pt NUMBER;
1597 l_tds NUMBER;
1598 l_pf NUMBER;
1599 l_super NUMBER;
1600 l_grat NUMBER;
1601 l_leave_encash NUMBER;
1602 l_retr_amt NUMBER;
1603 l_emplr_class VARCHAR2(10);
1604 l_end_date DATE;
1605 l_designation VARCHAR2(100);
1606 l_annual_sal NUMBER;
1607 l_pf_number VARCHAR2(30);
1608 l_pf_estab_code VARCHAR2(15);
1609 l_epf_number VARCHAR2(30);
1610 l_ltc_curr NUMBER;
1611 l_vrs_amount NUMBER;
1612 l_prev_sc NUMBER;
1613 l_prev_cess NUMBER;
1614 l_prev_sec_and_he_cess NUMBER;
1615 l_prev_exemp_80gg NUMBER;
1616 l_prev_med_reimburse_amt NUMBER;
1617 l_prev_exemp_80ccd NUMBER ;
1618 l_prev_cghs_exemp_80d NUMBER;
1619
1620
1621 l_procedure VARCHAR2(250);
1622 l_message VARCHAR2(250);
1623 BEGIN
1624
1625 g_debug := hr_utility.debug_enabled;
1626 l_procedure := g_package ||'prev_emplr_details';
1627 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1628 IF (g_debug)
1629 THEN
1630 pay_in_utils.trace('**************************************************','********************');
1631 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
1632 pay_in_utils.trace('p_assignment_id',p_assignment_id);
1633 pay_in_utils.trace('p_date_earned',p_date_earned);
1634 END IF;
1635
1636 p_prev_sal := 0;
1637 p_prev_ent_alw := 0;
1638 p_prev_pt := 0;
1639 p_prev_tds := 0;
1640 p_prev_pf := 0;
1641 p_prev_super := 0;
1642 p_prev_govt_ent_alw := 0;
1643 p_prev_grat := 0;
1644 p_prev_leave_encash := 0;
1645 p_prev_retr_amt := 0;
1646 p_designation := 'X';
1647 p_annual_sal := 0;
1648 p_pf_number := 'X';
1649 p_pf_estab_code := 'X';
1650 p_epf_number := 'X';
1651 p_emplr_class := 'X';
1652 p_ltc_curr_block := 0;
1653 p_vrs_amount := 0;
1654 p_prev_sc := 0;
1655 p_prev_cess := 0;
1656 p_prev_sec_and_he_cess := 0;
1657 p_prev_exemp_80gg := 0;
1658 p_prev_med_reimburse_amt := 0;
1659 p_prev_exemp_80ccd :=0;
1660 p_prev_cghs_exemp_80d := 0;
1661
1662 l_start := get_financial_year_start(p_date_earned);
1663 l_end := get_financial_year_end(p_date_earned);
1664
1665 Open c_prev_emp_details;
1666 Loop
1667
1668 Fetch c_prev_emp_details
1669 Into l_designation,l_annual_sal,l_pf_number,l_pf_estab_code,l_epf_number,l_emplr_class,
1670 l_ltc_curr,l_leave_encash,l_grat,l_retr_amt,l_vrs_amount,l_sal,l_pf,l_ent,l_pt,l_tds,
1671 l_super,l_prev_sc, l_prev_cess,l_prev_exemp_80gg,l_prev_med_reimburse_amt,l_prev_sec_and_he_cess,l_prev_exemp_80ccd,
1672 l_prev_cghs_exemp_80d,l_end_date;
1673 If c_prev_emp_details%NotFound Then
1674 Close c_prev_emp_details;
1675 Return 0;
1676 End if;
1677
1678 If l_end_date BETWEEN l_start AND l_end then
1679 p_prev_sal := p_prev_sal + l_sal;
1680 p_prev_pt := p_prev_pt + l_pt;
1681 p_prev_tds := p_prev_tds + l_tds;
1682 p_prev_pf := p_prev_pf + l_pf;
1683 p_prev_super := p_prev_super + l_super;
1684 p_prev_sc := p_prev_sc + l_prev_sc;
1685 p_prev_cess := p_prev_cess + l_prev_cess;
1686 p_prev_sec_and_he_cess := p_prev_sec_and_he_cess + l_prev_sec_and_he_cess;
1687 p_prev_exemp_80gg :=p_prev_exemp_80gg + l_prev_exemp_80gg;
1688 p_prev_med_reimburse_amt := p_prev_med_reimburse_amt + l_prev_med_reimburse_amt;
1689 p_prev_exemp_80ccd := p_prev_exemp_80ccd + l_prev_exemp_80ccd;
1690 IF ( l_emplr_class = 'CG' OR l_emplr_class = 'CGC' OR l_emplr_class = 'SG' OR l_emplr_class = 'SGC') THEN
1691 p_prev_cghs_exemp_80d := p_prev_cghs_exemp_80d + l_prev_cghs_exemp_80d;
1692 END IF;
1693 If (l_emplr_class = 'CG' or l_emplr_class = 'SG') Then
1694 p_prev_govt_ent_alw := p_prev_govt_ent_alw + l_ent;
1695 Else
1696 p_prev_ent_alw := p_prev_ent_alw + l_ent;
1697 End if;
1698 End if;
1699
1700 p_prev_retr_amt := p_prev_retr_amt + l_retr_amt;
1701 If (l_emplr_class <> 'CG' and l_emplr_class <> 'SG') Then
1702 p_prev_leave_encash := p_prev_leave_encash + l_leave_encash;
1703 End If;
1704 -- Fix for bug 3980777 starts
1705 If (l_emplr_class NOT IN ('CG','SG','LA')) Then
1706 p_prev_grat := p_prev_grat + l_grat;
1707 End If;
1708 -- Fix for bug 3980777 ends
1709 p_vrs_amount := p_vrs_amount + l_vrs_amount;
1710 p_emplr_class := l_emplr_class;
1711 End Loop;
1712 Close c_prev_emp_details;
1713
1714 IF (g_debug)
1715 THEN
1716 pay_in_utils.trace('p_assignment_id ',p_assignment_id );
1717 pay_in_utils.trace('p_date_earned ',p_date_earned );
1718 pay_in_utils.trace('p_prev_sal ',p_prev_sal );
1719 pay_in_utils.trace('p_prev_tds ',p_prev_tds );
1720 pay_in_utils.trace('p_prev_pt ',p_prev_pt );
1721 pay_in_utils.trace('p_prev_ent_alw ',p_prev_ent_alw );
1722 pay_in_utils.trace('p_prev_pf ',p_prev_pf );
1723 pay_in_utils.trace('p_prev_super ',p_prev_super );
1724 pay_in_utils.trace('p_prev_govt_ent_alw',p_prev_govt_ent_alw);
1725 pay_in_utils.trace('p_prev_grat ',p_prev_grat );
1726 pay_in_utils.trace('p_prev_leave_encash',p_prev_leave_encash);
1727 pay_in_utils.trace('p_prev_retr_amt ',p_prev_retr_amt );
1728 pay_in_utils.trace('p_designation ',p_designation );
1729 pay_in_utils.trace('p_annual_sal ',p_annual_sal );
1730 pay_in_utils.trace('p_pf_number ',p_pf_number );
1731 pay_in_utils.trace('p_pf_estab_code ',p_pf_estab_code );
1732 pay_in_utils.trace('p_epf_number ',p_epf_number );
1733 pay_in_utils.trace('p_emplr_class ',p_emplr_class );
1734 pay_in_utils.trace('p_ltc_curr_block ',p_ltc_curr_block );
1735 pay_in_utils.trace('p_vrs_amount ',p_vrs_amount );
1736 pay_in_utils.trace('p_prev_sc ',p_prev_sc );
1737 pay_in_utils.trace('p_prev_cess ',p_prev_cess );
1738 pay_in_utils.trace('p_prev_exemp_80gg ',p_prev_exemp_80gg );
1739 pay_in_utils.trace('p_prev_med_reimburse_amt',p_prev_med_reimburse_amt);
1740 pay_in_utils.trace('p_prev_exemp_80ccd' ,p_prev_exemp_80ccd);
1741 pay_in_utils.trace('p_prev_cghs_exemp_80d' ,p_prev_cghs_exemp_80d);
1742 END IF;
1743 pay_in_utils.trace('**************************************************','********************');
1744 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1745 Return 0;
1746
1747 End prev_emplr_details;
1748
1749 --------------------------------------------------------------------------
1750 -- Name : get_residential_status --
1751 -- Type : Function --
1752 -- Access : Public --
1753 -- Description : Function to get details for Residential Status --
1754 -- Parameters : --
1755 -- IN : --
1756 -- --
1757 --------------------------------------------------------------------------
1758
1759 FUNCTION get_residential_status (p_assignment_id in number,
1760 p_effective_date in date)
1761 return Varchar2 is
1762
1763 l_procedure VARCHAR2(250);
1764 l_message VARCHAR2(250);
1765 l_res_status VARCHAR2(30);
1766 BEGIN
1767 g_debug := hr_utility.debug_enabled;
1768 l_procedure := g_package ||'get_residential_status';
1769 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1770 IF (g_debug)
1771 THEN
1772 pay_in_utils.trace('**************************************************','********************');
1773 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
1774 pay_in_utils.trace('p_assignment_id',p_assignment_id);
1775 pay_in_utils.trace('p_effective_date',p_effective_date);
1776 END IF;
1777
1778 select people.per_information7
1779 into l_res_status
1780 from per_all_people_f people,
1781 per_all_assignments_f paa
1782 where people.person_id = paa.person_id
1783 and paa.assignment_id= p_assignment_id
1784 and p_effective_date between paa.effective_start_date and paa.effective_end_date
1785 and p_effective_date between people.effective_start_date and people.effective_end_date;
1786 IF (g_debug)
1787 THEN
1788 pay_in_utils.trace('l_res_status',l_res_status);
1789 pay_in_utils.trace('**************************************************','********************');
1790 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1791 END IF;
1792 RETURN l_res_status;
1793 END;
1794
1795
1796
1797 --------------------------------------------------------------------------
1798 -- Name : other_allowance_details --
1799 -- Type : Function --
1800 -- Access : Public --
1801 -- Description : Function to get details for Other Allowances --
1802 -- Parameters : --
1803 -- IN : --
1804 -- --
1805 --------------------------------------------------------------------------
1806 FUNCTION other_allowance_details
1807 ( p_element_type_id in number
1808 ,p_date_earned in date
1809 ,p_allowance_name out NOCOPY varchar2
1810 ,p_allowance_category out NOCOPY varchar2
1811 ,p_max_exemption_amount out NOCOPY number
1812 ,p_nature_of_expense OUT NOCOPY VARCHAR2 )
1813 Return Number is
1814
1815 Cursor c_alw_details is
1816 Select element_information1,
1817 element_information2,
1818 element_information3,
1819 element_information4
1820 From pay_element_types_f
1821 Where element_type_id = p_element_type_id
1822 and p_date_earned between effective_start_date and effective_end_date;
1823
1824 l_procedure VARCHAR2(250);
1825 l_message VARCHAR2(250);
1826 BEGIN
1827 g_debug := hr_utility.debug_enabled;
1828 l_procedure := g_package ||'other_allowance_details';
1829 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1830 IF (g_debug)
1831 THEN
1832 pay_in_utils.trace('**************************************************','********************');
1833 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
1834 pay_in_utils.trace('p_element_type_id',p_element_type_id);
1835 pay_in_utils.trace('p_date_earned',p_date_earned);
1836 END IF;
1837
1838
1839 OPEN c_alw_details;
1840 FETCH c_alw_details
1841 INTO p_allowance_name,
1842 p_allowance_category,
1843 p_max_exemption_amount,
1844 p_nature_of_expense;
1845 CLOSE c_alw_details;
1846
1847 IF (g_debug)
1848 THEN
1849 pay_in_utils.set_location(g_debug,'Out Paramters value is',20);
1850 pay_in_utils.trace('p_allowance_name ',p_allowance_name);
1851 pay_in_utils.trace('p_allowance_category ',p_allowance_category);
1852 pay_in_utils.trace('p_max_exemption_amount',p_max_exemption_amount);
1853 pay_in_utils.trace('p_nature_of_expense ',p_nature_of_expense);
1854 END IF;
1855
1856 pay_in_utils.trace('**************************************************','********************');
1857 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1858 Return 0;
1859 END other_allowance_details;
1860
1861 --------------------------------------------------------------------------
1862 -- Name : get_disability_details --
1863 -- Type : Function --
1864 -- Access : Public --
1865 -- Description : Function to get the Disability Details of a person --
1866 -- Parameters : --
1867 -- IN : --
1868 -- --
1869 --------------------------------------------------------------------------
1870 FUNCTION get_disability_details( p_assignment_id in number
1871 ,p_date_earned in date
1872 ,p_disable_catg out nocopy varchar2
1873 ,p_disable_degree out nocopy number
1874 ,p_disable_proof out NOCOPY varchar2)
1875 Return Number is
1876
1877 Cursor c_disab_details is
1878 select pdf.category,pdf.degree,pdf.dis_information1
1879 from per_disabilities_f pdf,
1880 per_all_assignments_f paa
1881 where paa.assignment_id = p_assignment_id
1882 and paa.person_id = pdf.person_id
1883 and p_date_earned between paa.effective_start_date and paa.effective_end_date
1884 and p_date_earned between pdf.effective_start_date and pdf.effective_end_date
1885 order by nvl(pdf.dis_information1,'N') desc;
1886
1887 l_catg Varchar2(10);
1888 l_degree number;
1889 l_proof Varchar2(10);
1890 l_procedure VARCHAR2(250);
1891 l_message VARCHAR2(250);
1892 BEGIN
1893 g_debug := hr_utility.debug_enabled;
1894 l_procedure := g_package ||'get_disability_details';
1895 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1896
1897 IF (g_debug)
1898 THEN
1899 pay_in_utils.trace('**************************************************','********************');
1900 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
1901 pay_in_utils.trace('p_assignment_id',p_assignment_id);
1902 pay_in_utils.trace('p_date_earned',p_date_earned);
1903 END IF;
1904
1905 l_catg := 'XX';
1906 l_degree := 0;
1907 l_proof := 'N';
1908
1909 Open c_disab_details;
1910 Fetch c_disab_details into l_catg,l_degree,l_proof;
1911 Close c_disab_details;
1912
1913 p_disable_catg := l_catg;
1914 p_disable_degree := l_degree;
1915 p_disable_proof := l_proof;
1916
1917 IF (g_debug)
1918 THEN
1919 pay_in_utils.set_location(g_debug,'Out Paramters value is',20);
1920 pay_in_utils.trace('p_disable_catg ',p_disable_catg);
1921 pay_in_utils.trace('p_disable_degree ',p_disable_degree);
1922 pay_in_utils.trace('p_disable_proof',p_disable_proof);
1923 END IF;
1924
1925 pay_in_utils.trace('**************************************************','********************');
1926 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1927 Return 0;
1928
1929 END get_disability_details;
1930
1931 --------------------------------------------------------------------------
1932 -- Name : get_age --
1933 -- Type : Function --
1934 -- Access : Public --
1935 -- Description : Function to check the age of the employee --
1936 -- Parameters : --
1937 -- IN : --
1938 -- --
1939 --------------------------------------------------------------------------
1940 FUNCTION get_age(p_assignment_id in number,p_date_earned in date)
1941 Return number is
1942
1943 Cursor c_dob is
1944 select pap.date_of_birth
1945 from per_all_people_f pap,
1946 per_all_assignments_f paa
1947 where paa.assignment_id = p_assignment_id
1948 and pap.person_id = paa.person_id
1949 and p_date_earned between paa.effective_start_date and paa.effective_end_date
1950 and p_date_earned between pap.effective_start_date and pap.effective_end_date;
1951
1952 l_dob date;
1953 l_cur_fin_year_end date;
1954 l_age number;
1955
1956 l_procedure VARCHAR2(250);
1957 l_message VARCHAR2(250);
1958 BEGIN
1959 g_debug := hr_utility.debug_enabled;
1960 l_procedure := g_package ||'get_age';
1961 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1962 IF (g_debug)
1963 THEN
1964 pay_in_utils.trace('**************************************************','********************');
1965 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
1966 pay_in_utils.trace('p_assignment_id',p_assignment_id);
1967 pay_in_utils.trace('p_date_earned',p_date_earned);
1968 END IF;
1969
1970
1971 Open c_dob;
1972 Fetch c_dob into l_dob;
1973 Close c_dob;
1974
1975 l_cur_fin_year_end := get_financial_year_end(p_date_earned);
1976
1977 l_age := FLOOR( MONTHS_BETWEEN( l_cur_fin_year_end, l_dob) / 12 );
1978
1979 IF (g_debug)
1980 THEN
1981 pay_in_utils.trace('l_cur_fin_year_end',l_cur_fin_year_end);
1982 pay_in_utils.trace('l_age',l_age);
1983 END IF;
1984
1985 pay_in_utils.trace('**************************************************','********************');
1986 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1987
1988 Return l_age;
1989
1990 END get_age;
1991
1992 --------------------------------------------------------------------------
1993 -- Name : act_rent_paid --
1994 -- Type : Function --
1995 -- Access : Public --
1996 -- Description : Function to get the actual rent paid value --
1997 -- Parameters : --
1998 -- IN : --
1999 -- --
2000 --------------------------------------------------------------------------
2001 FUNCTION act_rent_paid(p_assignment_action_id IN number
2002 ,p_date_earned IN date)
2003 Return NUMBER is
2004
2005 Cursor c_act_rent_paid(l_element_entry_id IN number,l_curr_mon IN Varchar2) is
2006 select pev.screen_entry_value
2007 from pay_element_entries_f pee,
2008 pay_element_entry_values_f pev,
2009 pay_input_values_f piv
2010 where pee.element_entry_id = l_element_entry_id
2011 and pev.element_entry_id = pee.element_entry_id
2012 and pee.element_type_id = piv.element_type_id
2013 and pev.input_value_id = piv.input_value_id
2014 and piv.name = l_curr_mon
2015 and p_date_earned between pev.effective_start_date and pev.effective_end_date
2016 and p_date_earned between pee.effective_start_date and pee.effective_end_date
2017 and p_date_earned between piv.effective_start_date and piv.effective_end_date;
2018
2019
2020 l_hri_entry_id pay_element_entries_f.element_entry_id%type;
2021 l_rent_paid varchar2(10);
2022 l_year_start date;
2023 l_year_end date;
2024 l_entry_type_flag varchar2(2) ;
2025 l_entry_end_date date;
2026 l_curr_mon varchar2(3);
2027 l_procedure VARCHAR2(250);
2028 l_message VARCHAR2(250);
2029
2030 BEGIN
2031 g_debug := hr_utility.debug_enabled;
2032 l_procedure := g_package ||'act_rent_paid';
2033 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2034 IF (g_debug)
2035 THEN
2036 pay_in_utils.trace('**************************************************','********************');
2037 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
2038 pay_in_utils.trace('p_assignment_action_id',p_assignment_action_id);
2039 pay_in_utils.trace('p_date_earned',p_date_earned);
2040 END IF;
2041
2042 l_year_start := pay_in_tax_utils.get_financial_year_start(p_date_earned);
2043 l_year_end := pay_in_tax_utils.get_financial_year_end(p_date_earned);
2044
2045 l_curr_mon := to_char(p_date_earned,'MON');
2046
2047 l_hri_entry_id := get_house_rent_info_entry_id(p_assignment_action_id,
2048 p_date_earned,
2049 l_year_start,
2050 l_year_end,
2051 l_entry_type_flag,
2052 l_entry_end_date );
2053
2054 IF (g_debug)
2055 THEN
2056 pay_in_utils.trace('l_year_start',l_year_start);
2057 pay_in_utils.trace('l_year_end',l_year_end);
2058 pay_in_utils.trace('l_curr_mon',l_curr_mon);
2059 pay_in_utils.trace('l_hri_entry_id',l_hri_entry_id);
2060 END IF;
2061
2062 If l_entry_type_flag = 'E' Then
2063 Open c_act_rent_paid(l_hri_entry_id,l_curr_mon);
2064 Fetch c_act_rent_paid INTO l_rent_paid;
2065 Close c_act_rent_paid;
2066
2067 pay_in_utils.set_location(g_debug,'Rent paid is' || l_rent_paid,20);
2068 pay_in_utils.trace('**************************************************','********************');
2069 Return fnd_number.canonical_to_number(l_rent_paid);
2070 Else
2071 pay_in_utils.trace('**************************************************','********************');
2072 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
2073 Return 0;
2074 End If;
2075
2076 END act_rent_paid;
2077
2078 --------------------------------------------------------------------------
2079 -- Name : check_ee_exists --
2080 -- Type : Function --
2081 -- Access : Public --
2082 -- Description : Function to check if EE exists --
2083 -- Parameters : --
2084 -- IN : --
2085 -- --
2086 --------------------------------------------------------------------------
2087 FUNCTION check_ee_exists(p_element_name IN VARCHAR2
2088 ,p_assignment_id IN NUMBER
2089 ,p_effective_date IN DATE
2090 ,p_element_entry_id OUT NOCOPY NUMBER
2091 ,p_start_date OUT NOCOPY DATE
2092 ,p_ee_ovn OUT NOCOPY NUMBER)
2093 RETURN BOOLEAN
2094 IS
2095 CURSOR csr_asg_details
2096 IS
2097 SELECT asg.business_group_id
2098 ,asg.payroll_id
2099 FROM per_assignments_f asg
2100 WHERE asg.assignment_id = p_assignment_id
2101 AND asg.primary_flag = 'Y'
2102 AND p_effective_date BETWEEN asg.effective_start_date
2103 AND asg.effective_end_date ;
2104
2105 CURSOR csr_element_link (l_business_group_id IN NUMBER,
2106 l_payroll_id IN NUMBER)
2107 IS
2108 SELECT pel.element_link_id
2109 FROM pay_element_links_f pel,
2110 pay_element_types_f pet
2111 WHERE pet.element_name = p_element_name
2112 AND pet.element_type_id = pel.element_type_id
2113 AND (pel.payroll_id = l_payroll_id
2114 OR (pel.payroll_id IS NULL
2115 AND pel.link_to_all_payrolls_flag = 'Y' ) )
2116 AND pel.business_group_id = l_business_group_id
2117 AND p_effective_date BETWEEN pet.effective_start_date
2118 AND pet.effective_end_date
2119 AND p_effective_date BETWEEN pel.effective_start_date
2120 AND pel.effective_end_date ;
2121
2122
2123 CURSOR csr_element_entry (c_element_link_id IN NUMBER)
2124 IS
2125 SELECT element_entry_id
2126 ,object_version_number
2127 ,effective_start_date
2128 FROM pay_element_entries_f
2129 WHERE assignment_id = p_assignment_id
2130 AND element_link_id = c_element_link_id
2131 AND p_effective_date BETWEEN effective_start_date
2132 AND effective_end_date ;
2133
2134 l_business_group_id NUMBER;
2135 l_element_link_id NUMBER;
2136 l_payroll_id NUMBER;
2137 l_procedure VARCHAR2(250);
2138 l_message VARCHAR2(250);
2139 BEGIN
2140 g_debug := hr_utility.debug_enabled;
2141 l_procedure := g_package ||'check_ee_exists';
2142 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2143 IF (g_debug)
2144 THEN
2145 pay_in_utils.trace('**************************************************','********************');
2146 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
2147 pay_in_utils.trace('p_element_name ',p_element_name );
2148 pay_in_utils.trace('p_assignment_id ',p_assignment_id );
2149 pay_in_utils.trace('p_effective_date',p_effective_date);
2150 END IF;
2151 p_element_entry_id := NULL;
2152 p_ee_ovn := NULL;
2153
2154 OPEN csr_asg_details;
2155 FETCH csr_asg_details
2156 INTO l_business_group_id, l_payroll_id;
2157 CLOSE csr_asg_details;
2158
2159 IF g_debug THEN
2160 hr_utility.trace('Business Group ID : '||l_business_group_id);
2161 hr_utility.trace('Payroll ID : '||l_payroll_id);
2162 END IF;
2163
2164 OPEN csr_element_link (l_business_group_id, l_payroll_id);
2165 FETCH csr_element_link INTO l_element_link_id;
2166
2167 IF csr_element_link%NOTFOUND OR l_element_link_id IS NULL THEN
2168 CLOSE csr_element_link;
2169 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2170 RETURN FALSE;
2171 ELSE
2172 IF g_debug THEN
2173 hr_utility.trace('Element Link ID : '||l_element_link_id);
2174 END IF;
2175
2176 CLOSE csr_element_link;
2177 --
2178 OPEN csr_element_entry(l_element_link_id) ;
2179 FETCH csr_element_entry INTO p_element_entry_id, p_ee_ovn, p_start_date ;
2180 IF g_debug then
2181 hr_utility.trace('Element Entry ID : '||p_element_entry_id);
2182 END IF;
2183
2184 IF p_element_entry_id IS NULL OR csr_element_entry%NOTFOUND
2185 THEN
2186 CLOSE csr_element_entry;
2187 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
2188 RETURN FALSE;
2189 END IF;
2190 END IF;
2191 pay_in_utils.trace('**************************************************','********************');
2192 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
2193 RETURN TRUE;
2194 --
2195 END check_ee_exists;
2196 --------------------------------------------------------------------------
2197 -- Name : get_entry_earliest_start_date --
2198 -- Type : Function --
2199 -- Access : Public --
2200 -- Description : Function to find the earliest start date of an --
2201 -- element entry --
2202 -- Parameters : --
2203 -- IN : --
2204 -- --
2205 --------------------------------------------------------------------------
2206 FUNCTION get_entry_earliest_start_date(p_element_entry_id IN NUMBER
2207 ,p_element_type_id IN NUMBER
2208 ,p_assignment_id IN NUMBER
2209 )
2210 RETURN DATE IS
2211
2212 CURSOR c_get_earliest_start_Date
2213 IS
2214 SELECT MIN(pee.effective_start_date)
2215 FROM pay_element_entries_f pee
2216 WHERE pee.element_entry_id =p_element_entry_id
2217 AND pee.assignment_id =p_assignment_id
2218 AND pee.element_type_id =p_element_type_id;
2219
2220 l_date DATE;
2221 l_procedure VARCHAR2(250);
2222 l_message VARCHAR2(250);
2223 BEGIN
2224 g_debug := hr_utility.debug_enabled;
2225 l_procedure := g_package ||'get_date_earned';
2226 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2227 IF (g_debug)
2228 THEN
2229 pay_in_utils.trace('**************************************************','********************');
2230 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
2231 pay_in_utils.trace('p_element_entry_id',p_element_entry_id);
2232 pay_in_utils.trace('p_element_type_id ',p_element_type_id);
2233 pay_in_utils.trace('p_assignment_id ',p_assignment_id);
2234 END IF;
2235
2236
2237 OPEN c_get_earliest_start_Date;
2238 FETCH c_get_earliest_start_Date INTO l_date;
2239 CLOSE c_get_earliest_start_Date;
2240
2241 pay_in_utils.trace('**************************************************','********************');
2242 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2243
2244 IF (g_debug)
2245 THEN
2246 pay_in_utils.trace('l_date',l_date);
2247 END IF;
2248
2249 RETURN l_date;
2250 END get_entry_earliest_start_date;
2251
2252 --------------------------------------------------------------------------
2253 -- Name : get_projected_loan_perquisite --
2254 -- Type : Function --
2255 -- Access : Public --
2256 -- Description : Function to get the Projected Loan perquisite value --
2257 -- for the rest of the tax year --
2258 -- Parameters : --
2259 -- IN : --
2260 -- --
2261 --------------------------------------------------------------------------
2262 FUNCTION get_projected_loan_perquisite(p_outstanding_balance IN NUMBER
2263 ,p_remaining_period IN NUMBER
2264 ,p_employee_contribution IN NUMBER
2265 ,p_interest IN NUMBER
2266 ,p_concessional_interest IN NUMBER
2267 )
2268 RETURN NUMBER IS
2269 p_value number;
2270 l_procedure VARCHAR2(250);
2271 l_message VARCHAR2(250);
2272 BEGIN
2273 g_debug := hr_utility.debug_enabled;
2274 l_procedure := g_package ||'get_projected_loan_perquisite';
2275 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2276 IF (g_debug)
2277 THEN
2278 pay_in_utils.trace('**************************************************','********************');
2279 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
2280 pay_in_utils.trace('p_outstanding_balance ',p_outstanding_balance );
2281 pay_in_utils.trace('p_remaining_period ',p_remaining_period );
2282 pay_in_utils.trace('p_employee_contribution',p_employee_contribution);
2283 pay_in_utils.trace('p_interest ',p_interest );
2284 pay_in_utils.trace('p_concessional_interest',p_concessional_interest);
2285 END IF;
2286
2287
2288
2289 p_value :=0;
2290
2291 FOR i in 1..p_remaining_period LOOP
2292 -- Added additional check for Bugfix 3956926
2293 IF (p_outstanding_balance - (i* p_employee_contribution)) >=0 THEN
2294 p_value := p_value + ((p_outstanding_balance - (i* p_employee_contribution))
2295 *(p_interest - p_concessional_interest)/(12*100));
2296 END IF;
2297 END LOOP;
2298
2299 IF (g_debug)
2300 THEN
2301 pay_in_utils.trace('p_value',p_value);
2302 END IF;
2303
2304 pay_in_utils.trace('**************************************************','********************');
2305 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2306
2307 RETURN p_value;
2308
2309 END get_projected_loan_perquisite;
2310
2311 --------------------------------------------------------------------------
2312 -- Name : get_perquisite_details --
2313 -- Type : Function --
2314 -- Access : Public --
2315 -- Description : Function to get the exemption amount of Other Perks --
2316 -- Parameters : --
2317 -- IN : --
2318 -- --
2319 --------------------------------------------------------------------------
2320 FUNCTION get_perquisite_details (p_element_type_id IN NUMBER
2321 ,p_date_earned IN DATE
2322 ,p_assignment_action_id IN NUMBER
2323 ,p_assignment_id IN NUMBER
2324 ,p_business_group_id IN NUMBER
2325 ,p_element_entry_id IN NUMBER
2326 ,p_emp_status IN VARCHAR2
2327 ,p_taxable_flag OUT NOCOPY VARCHAR2
2328 ,p_exemption_amount OUT NOCOPY NUMBER
2329 )
2330 RETURN NUMBER IS
2331 CURSOR c_get_perk_details IS
2332 SELECT element_information1
2333 ,NVL(element_information6,'Y')
2334 FROM pay_element_types_f
2335 WHERE element_type_id = p_element_type_id
2336 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
2337
2338 CURSOR c_exemption (p_perk_name IN VARCHAR2) IS
2339 SELECT fnd_number.canonical_to_number(exemption_amount)
2340 FROM pay_in_other_perquisites_v
2341 WHERE perquisite_name = p_perk_name;
2342
2343 l_perk_name pay_element_types_f.element_information1%TYPE;
2344 l_procedure VARCHAR2(100);
2345 l_message VARCHAR2(255);
2346
2347 BEGIN
2348 g_debug := hr_utility.debug_enabled ;
2349 l_procedure := 'pay_in_tax_utils.get_perquisite_details' ;
2350 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2351 p_taxable_flag := 'Y';
2352
2353 OPEN c_get_perk_details;
2354 FETCH c_get_perk_details INTO l_perk_name ,p_taxable_flag;
2355 CLOSE c_get_perk_details;
2356
2357 pay_in_utils.set_location(g_debug,l_procedure,20);
2358
2359 OPEN c_exemption (l_perk_name);
2360 FETCH c_exemption INTO p_exemption_amount;
2361 CLOSE c_exemption;
2362
2363 IF p_exemption_amount IS NULL
2364 THEN
2365 p_exemption_amount := 0;
2366 END IF ;
2367 RETURN 0;
2368
2369 EXCEPTION
2370 WHEN NO_DATA_FOUND THEN
2371 RETURN -1;
2372 WHEN OTHERS THEN
2373 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2374 pay_in_utils.set_location(g_debug,' Leaving : '||l_procedure, 40);
2375 hr_utility.trace(l_message);
2376 RETURN -1;
2377
2378 END get_perquisite_details;
2379
2380 --------------------------------------------------------------------------
2381 -- Name : calculate_80gg_exemption --
2382 -- Type : Function --
2383 -- Access : Public --
2384 -- Description : Function to calculate Sec 80GG Exemption --
2385 -- Parameters : --
2386 -- IN : --
2387 -- --
2388 --------------------------------------------------------------------------
2389 FUNCTION calculate_80gg_exemption (p_assact_id IN NUMBER
2390 ,p_assignment_id IN NUMBER
2391 ,p_payroll_id IN NUMBER
2392 ,p_effective_date IN DATE
2393 ,p_std_exemption IN NUMBER
2394 ,p_adj_tot_income IN NUMBER
2395 ,p_std_exem_percent IN NUMBER
2396 ,p_start_period_num IN NUMBER
2397 ,p_last_period_number IN NUMBER
2398 ,p_flag IN VARCHAR2)
2399 RETURN NUMBER IS
2400
2401 l_rent_paid_tbl t_rent_paid ;
2402 l_month_tbl t_month ;
2403 l_year_start date;
2404 l_year_end date;
2405 l_hri_entry_id pay_element_entries_f.element_entry_id%type;
2406 l_entry_type_flag varchar2(2) ;
2407 l_entry_end_date date;
2408
2409 l3 number;
2410 l_80_exem number;
2411 l_10percent_adj_tot_inc number;
2412 l_25percent_adj_tot_inc number;
2413 l_adj_tot_inc number;
2414 l_def_bal_id pay_defined_balances.defined_balance_id%type;
2415 l_def_bal_id_80gg pay_defined_balances.defined_balance_id%type;
2416 l_def_bal_id_advance pay_defined_balances.defined_balance_id%type;
2417 l_assact_tbl t_assact ;
2418 l_eff_date_tbl t_eff_date;
2419 l_hra number;
2420 l_curr_period_num number;
2421 l_80gg_flag VARCHAR2(3);
2422 l_element_name pay_element_types_f.element_name %TYPE;
2423 l_input_name pay_input_values_f.name%TYPE;
2424
2425 CURSOR c_claim_80gg_flag(c_assignment_action_id NUMBER
2426 ,c_element_name IN VARCHAR2
2427 ,c_input_name IN VARCHAR2) IS
2428 SELECT prv.result_value
2429 FROM pay_run_result_values prv,
2430 pay_run_results prr,
2431 pay_input_values_f piv,
2432 pay_element_types_f pet
2433 WHERE prv.run_result_id = prr.run_result_id
2434 AND prr.assignment_action_id = c_assignment_action_id
2435 AND prr.element_type_id = pet.element_type_id
2436 AND pet.element_name = c_element_name
2437 AND piv.element_type_id = pet.element_type_id
2438 AND piv.name = c_input_name
2439 AND piv.input_value_id = prv.input_value_id
2440 AND pet.legislation_code = 'IN'
2441 AND piv.legislation_code = 'IN';
2442
2443 -- Bug No: 13837918 Start
2444 Cursor c_80gg_flag(c_assignment_action_id NUMBER
2445 ,c_effective_date DATE
2446 ,c_element_name IN VARCHAR2
2447 ,c_input_name IN VARCHAR2) IS
2448 select pev.screen_entry_value
2449 from pay_element_entries_f pee,
2450 pay_element_entry_values_f pev,
2451 pay_input_values_f piv,
2452 pay_element_types_f pet
2453 ,pay_assignment_actions paa
2454 where pev.element_entry_id = pee.element_entry_id
2455 and pee.element_type_id = piv.element_type_id
2456 and pev.input_value_id = piv.input_value_id
2457 AND piv.element_type_id = pet.element_type_id
2458 and pee.assignment_id = paa.assignment_id
2459 and paa.assignment_action_id = c_assignment_action_id
2460 AND pet.element_name = c_element_name
2461 and piv.name = c_input_name
2462 and c_effective_date between pev.effective_start_date and pev.effective_end_date
2463 and c_effective_date between pee.effective_start_date and pee.effective_end_date
2464 and c_effective_date between piv.effective_start_date and piv.effective_end_date;
2465 -- Bug No: 13837918 End
2466
2467 l_procedure VARCHAR2(250);
2468 l_message VARCHAR2(250);
2469
2470 BEGIN
2471 g_debug := hr_utility.debug_enabled;
2472 l_procedure := g_package ||'calculate_80gg_exemption';
2473 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2474 IF (g_debug)
2475 THEN
2476 pay_in_utils.trace('**************************************************','********************');
2477 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
2478 pay_in_utils.trace('p_assact_id ',p_assact_id );
2479 pay_in_utils.trace('p_assignment_id ',p_assignment_id );
2480 pay_in_utils.trace('p_payroll_id ',p_payroll_id );
2481 pay_in_utils.trace('p_effective_date ',p_effective_date );
2482 pay_in_utils.trace('p_std_exemption ',p_std_exemption );
2483 pay_in_utils.trace('p_adj_tot_income ',p_adj_tot_income );
2484 pay_in_utils.trace('p_std_exem_percent ',p_std_exem_percent );
2485 pay_in_utils.trace('p_start_period_num ',p_start_period_num );
2486 pay_in_utils.trace('p_last_period_number',p_last_period_number);
2487 pay_in_utils.trace('p_flag ',p_flag );
2488 END IF;
2489
2490 l3 := 0;
2491 l_80_exem := 0;
2492 l_adj_tot_inc := 0;
2493
2494 l_curr_period_num := get_period_number(p_payroll_id,p_effective_date);
2495 l_def_bal_id := get_defined_balance('House Rent Allowance','_ASG_DE_PTD');
2496 l_def_bal_id_advance := get_defined_balance('Adjusted Advance for HRA','_ASG_DE_PTD');
2497 l_def_bal_id_80gg := get_defined_balance('Adjusted Total Income for 80GG','_ASG_DE_PTD');
2498 l_year_start := get_tax_year_start(p_payroll_id, p_effective_date );
2499 l_year_end := get_tax_year_end(p_payroll_id, p_effective_date );
2500
2501 IF l_year_start = to_date('01-04-2004','dd-mm-yyyy') THEN
2502 l_element_name :='Deductions under Chapter VI A';
2503 l_input_name := 'Claim Exemption Sec 80GG';
2504 ELSE
2505 l_element_name := 'Deduction under Section 80GG';
2506 l_input_name := 'Claim Exemption';
2507 END IF;
2508
2509 IF (g_debug)
2510 THEN
2511 pay_in_utils.trace('l_element_name',l_element_name);
2512 pay_in_utils.trace('l_input_name',l_input_name);
2513 END IF;
2514
2515 l_hri_entry_id := get_house_rent_info_entry_id(p_assact_id,
2516 p_effective_date,
2517 l_year_start,
2518 l_year_end,
2519 l_entry_type_flag,
2520 l_entry_end_date );
2521
2522 IF (g_debug)
2523 THEN
2524 pay_in_utils.trace('l_hri_entry_id',l_hri_entry_id);
2525 END IF;
2526
2527 get_monthly_rent( l_hri_entry_id,
2528 p_effective_date,
2529 l_entry_type_flag,
2530 l_entry_end_Date,
2531 p_payroll_id,
2532 p_assact_id,
2533 l_rent_paid_tbl,
2534 l_month_tbl );
2535
2536 get_monthly_max_assact(p_assignment_id,l_year_start,l_year_end,l_assact_tbl,l_eff_date_tbl);
2537
2538
2539 IF l_assact_tbl.COUNT > 0 THEN
2540
2541 FOR i IN p_start_period_num..p_last_period_number LOOP
2542 l3 := 0;
2543 l_25percent_adj_tot_inc := 0.25 * p_adj_tot_income;
2544 l_10percent_adj_tot_inc := p_std_exem_percent * p_adj_tot_income;
2545
2546 IF i < l_curr_period_num THEN
2547
2548 IF i <= l_assact_tbl.COUNT AND l_assact_tbl(i) > 0 THEN
2549 OPEN c_80gg_flag(l_assact_tbl(i),l_eff_date_tbl(i),l_element_name,l_input_name);
2550 FETCH c_80gg_flag INTO l_80gg_flag;
2551 /* Bug 4224201 Starts */
2552 IF c_80gg_flag%NOTFOUND THEN
2553 l_80gg_flag := 'N';
2554 END IF;
2555 /* Bug 4224201 Ends */
2556 CLOSE c_80gg_flag;
2557
2558 l_hra := pay_balance_pkg.get_value(l_def_bal_id,l_assact_tbl(i))
2559 + pay_balance_pkg.get_value(l_def_bal_id_advance,l_assact_tbl(i));
2560
2561 l_adj_tot_inc := pay_balance_pkg.get_value(l_def_bal_id_80gg,l_assact_tbl(i));
2562 l_25percent_adj_tot_inc := 0.25 * l_adj_tot_inc;
2563 l_10percent_adj_tot_inc := p_std_exem_percent * l_adj_tot_inc;
2564
2565 IF l_hra = 0 AND l_80gg_flag = 'Y' THEN
2566 l3 := GREATEST (l_rent_paid_tbl(i) - l_10percent_adj_tot_inc,0);
2567 END IF;
2568 ELSE
2569 l3:= 0;
2570 END IF;
2571
2572 ELSIF p_flag = 'Y' THEN
2573
2574 l3 := GREATEST (l_rent_paid_tbl(i) - l_10percent_adj_tot_inc,0);
2575
2576 END IF;
2577
2578 l_80_exem := l_80_exem + LEAST (p_std_exemption,l_25percent_adj_tot_inc,l3);
2579
2580 END LOOP;
2581
2582 END IF;
2583
2584 pay_in_utils.trace('**************************************************','********************');
2585 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2586
2587 RETURN l_80_exem;
2588 END calculate_80gg_exemption;
2589
2590 --------------------------------------------------------------------------
2591 -- Name : check_ltc_exemption --
2592 -- Type : Function --
2593 -- Access : Public --
2594 -- Description : Function to check the LTC Exemptions --
2595 -- Parameters : --
2596 -- IN : --
2597 -- --
2598 --------------------------------------------------------------------------
2599 FUNCTION check_ltc_exemption(p_element_type_id IN NUMBER
2600 ,p_date_earned IN DATE
2601 ,p_assignment_action_id IN NUMBER
2602 ,p_assignment_id IN NUMBER
2603 ,p_element_entry_id IN NUMBER
2604 ,p_carry_over_flag IN OUT NOCOPY VARCHAR2
2605 ,p_exempted_flag IN OUT NOCOPY VARCHAR2
2606 )
2607 RETURN NUMBER IS
2608 /* Cursor to find the LTC Block at the given effective Date */
2609 CURSOR c_ltc_block(p_date DATE)
2610 IS
2611 SELECT hrl.lookup_code
2612 ,hrl.meaning
2613 FROM hr_lookups hrl
2614 WHERE hrl.lookup_type ='IN_LTC_BLOCK'
2615 AND to_number(to_char(p_date,'YYYY')) BETWEEN
2616 to_number(SUBSTR(HRL.LOOKUP_CODE,1,4)) AND to_number(SUBSTR(HRL.LOOKUP_CODE,8,4)) ;
2617
2618
2619 /* Cursor to find the LTC Availed in Previous employment given the LTC Block Start and End Dates */
2620 CURSOR c_prev_employer_ltc_availed(p_start_date date
2621 ,p_end_date date
2622 ,p_assignment_id NUMBER)
2623 IS
2624 SELECT sum(nvl(ppm.pem_information8,0))
2625 FROM per_previous_employers ppm,
2626 per_all_assignments_f paa
2627 WHERE paa.assignment_id = p_assignment_id
2628 AND p_date_earned BETWEEN paa.effective_start_date AND paa.effective_end_date
2629 AND paa.person_id =ppm.person_id
2630 AND ppm.end_date BETWEEN p_start_date and p_end_date;
2631
2632
2633 /* LTC element entries processed in current payroll run for a given 'carry over from previous block' flag */
2634
2635 CURSOR c_entry_id (p_input_value_id NUMBER
2636 , p_flag_value VARCHAR2
2637 )
2638 IS
2639 SELECT ee.element_entry_id
2640 FROM pay_assignment_actions aa,
2641 pay_payroll_actions pa,
2642 pay_element_entries_f ee,
2643 pay_element_links_f el,
2644 pay_element_types_f et,
2645 pay_element_entry_values_f peev
2646 WHERE aa.payroll_action_id = pa.payroll_action_id
2647 AND aa.assignment_id = ee.assignment_id
2648 and ee.element_entry_id = peev.element_entry_id
2649 and peev.input_value_id = p_input_value_id
2650 and nvl(peev.screen_entry_value,'N') = p_flag_value
2651 AND pa.date_earned BETWEEN ee.effective_start_date
2652 AND ee.effective_end_date
2653 AND pa.date_earned BETWEEN peev.effective_start_date
2654 AND peev.effective_end_date
2655 AND ee.element_link_id = el.element_link_id
2656 AND pa.date_earned BETWEEN el.effective_start_date
2657 AND el.effective_end_date
2658 AND el.element_type_id = et.element_type_id
2659 AND et.element_type_id= p_element_type_id
2660 AND pa.date_earned BETWEEN et.effective_start_date
2661 AND et.effective_end_date
2662 AND aa.assignment_action_id = p_assignment_action_id
2663 AND NOT EXISTS (SELECT 1 FROM pay_quickpay_exclusions pqe
2664 WHERE pqe.assignment_action_id =nvl(aa.source_action_id,aa.assignment_Action_id)
2665 AND pqe.element_entry_id = ee.element_entry_id)
2666 ORDER BY ee.element_entry_id ;
2667
2668 /* Cursor to find the screen entry value */
2669 CURSOR c_entry_values(l_entry_id NUMBER
2670 ,l_input_value_id NUMBER) IS
2671 SELECT peev.screen_entry_value
2672 FROM pay_element_entry_values_f peev
2673 WHERE peev.element_entry_id = l_entry_id
2674 AND peev.input_value_id = l_input_value_id
2675 AND p_date_earned between peev.effective_start_date and peev.effective_end_date;
2676
2677 /* Cursor to find input value id given the element and input value name*/
2678 CURSOR c_input_value_id(p_input_name VARCHAR2)
2679 IS
2680 SELECT piv.input_value_id
2681 FROM pay_input_values_f piv
2682 WHERE piv.element_type_id = p_element_type_id
2683 AND piv.NAME = p_input_name
2684 AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date;
2685
2686 /* Cursor to find the the global value as on date earned */
2687 CURSOR c_global_value(l_global_name VARCHAR2) IS
2688 SELECT global_value
2689 from ff_globals_f ffg
2690 WHERE ffg.global_name = l_global_name
2691 AND p_date_earned BETWEEN ffg.effective_start_date AND ffg.effective_end_date;
2692
2693 /* Cursor to find the count of LTC entries already processed in an LTC block,given the block start and end dates, the value of carry over flag and the value of exempted flag */
2694 CURSOR c_curr_emplr_ltc_block(p_start_date DATE
2695 ,p_end_date DATE
2696 ,p_carry_over VARCHAR2
2697 ,p_exempted VARCHAR2
2698 ,p_carry_over_id NUMBER
2699 ,p_exempted_id NUMBER)
2700 IS
2701 SELECT count(*)
2702 FROM pay_run_results prr
2703 ,pay_run_result_values prrv1
2704 ,pay_run_result_values prrv2
2705 ,pay_assignment_actions paa
2706 ,pay_payroll_actions ppa
2707 where prr.run_result_id =prrv1.run_result_id
2708 and prrv1.input_value_id = p_exempted_id
2709 and prrv2.input_value_id = p_carry_over_id
2710 and prr.run_result_id =prrv2.run_result_id
2711 and prrv1.result_value = p_exempted
2712 and nvl(prrv2.result_value,'N') = p_carry_over
2713 and prr.element_type_id =p_element_type_id
2714 and prr.assignment_action_id =paa.assignment_action_id
2715 AND paa.assignment_action_id <= p_assignment_action_id
2716 and paa.assignment_id = p_assignment_id
2717 and prr.status in ('P','PA')
2718 and paa.payroll_action_id =ppa.payroll_action_id
2719 and ppa.date_earned BETWEEN p_start_date and p_end_date;
2720
2721 TYPE tab_entry_id IS TABLE OF pay_element_entries_f.element_entry_id%TYPE INDEX BY BINARY_INTEGER;
2722 l_element_entry_id tab_entry_id;
2723 l_curr_element_entry tab_entry_id;
2724
2725 l_max_ltc NUMBER;
2726 l_carry_over_entry_count NUMBER;
2727 l_curr_block HR_LOOKUPS.LOOKUP_CODE%TYPE;
2728 l_curr_period HR_LOOKUPS.meaning%TYPE;
2729 l_curr_end_date DATE;
2730 l_curr_start_date DATE;
2731
2732 l_prev_blk_date DATE;
2733 l_prev_block HR_LOOKUPS.LOOKUP_CODE%TYPE;
2734 l_prev_period HR_LOOKUPS.meaning%TYPE;
2735 l_prev_end_date DATE;
2736 l_prev_start_date DATE;
2737
2738
2739 i number;
2740 j number;
2741 k number;
2742 l_count number;
2743 l_procedure VARCHAR2(100);
2744 l_carry_over_id number;
2745 l_exempted_id number;
2746
2747 l_prev_emplr_curr_blk NUMBER;
2748 l_curr_emplr_prev_blk NUMBER;
2749 l_prev_emplr_prev_blk NUMBER;
2750 l_curr_emplr_curr_blk_exempted NUMBER;
2751 l_message VARCHAR2(250);
2752 BEGIN
2753 g_debug := hr_utility.debug_enabled;
2754 l_procedure := g_package ||'check_ltc_exemption';
2755 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2756
2757 /*1.Find the LTC entries which have been carried over from previous LTC Block in the current run and store it in a PL/SQL table.
2758 2.When such entries exist and the user enters the previous employment information after making element entries, we need to validate if the user can still opt for carry over. So,we find the LTC journeys made
2759 in previous block in both current employment and previous employment and validate the entry.
2760 3.If it is exempted, set the exempted flag and return .Else, copy this element entry in another PL/SQL table.
2761 For an invalid entry ,we need to set the carry over flag to 'No' .However ,this may or may not be exempted.
2762 4.In case, carry over is not opted, we need to find the number of LTC exemptions already availed in current block
2763 in both current and previous employment and then decide if the current journey is exempted or not.
2764 */
2765
2766 k:=0;
2767 OPEN c_input_value_id('Carryover from Prev Block');
2768 FETCH c_input_value_id INTO l_carry_over_id;
2769 CLOSE c_input_value_id;
2770
2771 OPEN c_input_value_id('Exempted');
2772 FETCH c_input_value_id INTO l_exempted_id;
2773 CLOSE c_input_value_id;
2774
2775 OPEN c_entry_id(l_carry_over_id, 'Y');
2776 LOOP
2777 FETCH c_entry_id into l_element_entry_id(k);
2778 EXIT WHEN c_entry_id%NOTFOUND;
2779 pay_in_utils.set_location(g_debug,'Entry id with carry over as Yes in current run '|| l_element_entry_id(k),10);
2780 k := k+1;
2781 END LOOP;
2782 CLOSE c_entry_id;
2783
2784 pay_in_utils.set_location(g_debug,'ASSIGNMENT ACTION ID '||p_assignment_action_id,20);
2785
2786 OPEN c_global_value('IN_MAX_JOURNEY_BLOCK_LTC');
2787 FETCH c_global_value INTO l_max_ltc;
2788 CLOSE c_global_value;
2789
2790 l_carry_over_entry_count := l_element_entry_id.COUNT;
2791 pay_in_utils.set_location(g_debug,'count is '||l_carry_over_entry_count,30);
2792
2793 --------------------------
2794 --Carry over is opted
2795 --------------------------
2796
2797 IF l_carry_over_entry_count >0 THEN
2798 /* CHECK IF THIS CARRY OVER IS VALID -- Get the Previous Block start and End Dates*/
2799 l_prev_blk_date := ADD_MONTHS(p_date_earned,-48);
2800
2801 OPEN c_ltc_block(l_prev_blk_date);
2802 FETCH c_ltc_block INTO l_prev_block,l_prev_period;
2803 CLOSE c_ltc_block;
2804
2805 l_prev_start_date := to_date(substr(l_prev_period,1,11),'DD-MM-YYYY');
2806 l_prev_end_date := to_date(substr(l_prev_period,15,11),'DD-MM-YYYY');
2807
2808 -- Previous Block Previous Employment
2809 OPEN c_prev_employer_ltc_availed(l_prev_start_date
2810 ,l_prev_end_date
2811 ,p_assignment_id );
2812 FETCH c_prev_employer_ltc_availed INTO l_prev_emplr_prev_blk;
2813 CLOSE c_prev_employer_ltc_availed;
2814
2815 pay_in_utils.set_location(g_debug,'LTC in previous blk,previous employment '||l_prev_emplr_prev_blk,40);
2816
2817 -- Previous Block Current Employment
2818 OPEN c_curr_emplr_ltc_block(l_prev_start_date
2819 ,l_prev_end_date
2820 ,'N' -- carry over
2821 ,'Y' -- exempted
2822 ,l_carry_over_id
2823 ,l_exempted_id );
2824 FETCH c_curr_emplr_ltc_block INTO l_curr_emplr_prev_blk;
2825 CLOSE c_curr_emplr_ltc_block;
2826
2827 pay_in_utils.set_location(g_debug,'LTC in previous blk,current employment '||l_curr_emplr_prev_blk,50);
2828 j := 0;
2829
2830 --
2831 -- Start - Set the carry over flag appropriately
2832 --
2833 FOR i IN 0..l_carry_over_entry_count-1 LOOP
2834 IF (nvl(l_curr_emplr_prev_blk,0) + nvl(l_prev_emplr_prev_blk,0) +i < l_max_ltc) THEN
2835
2836 IF (l_element_entry_id(i) = p_element_entry_id) THEN
2837 p_carry_over_flag := 'Y';
2838 p_exempted_flag := 'Y';
2839
2840 IF l_curr_element_entry.COUNT > 0 THEN l_curr_element_entry.delete; END IF;
2841 IF l_element_entry_id.COUNT > 0 THEN l_element_entry_id.delete; END IF;
2842
2843 RETURN 0;
2844
2845 END IF;
2846 pay_in_utils.set_location(g_debug,'valid carryover ',60);
2847 ELSE
2848 pay_in_utils.set_location(g_debug,'invalid carryover ',70);
2849 l_curr_element_entry(j) := l_element_entry_id(i);
2850 j:=j+1;
2851 END IF;
2852 END LOOP;
2853 --
2854 -- End - Set the carry over flag appropriately
2855 --
2856
2857 IF l_element_entry_id.COUNT > 0 THEN l_element_entry_id.delete; END IF;
2858
2859
2860 END IF;
2861
2862 --------------------------
2863 --Carry over is not opted
2864 --------------------------
2865 l_count := l_curr_element_entry.COUNT;
2866
2867 pay_in_utils.set_location(g_debug, 'not the carry over stuff',80);
2868
2869 OPEN c_ltc_block(p_date_earned);
2870 FETCH c_ltc_block INTO l_curr_block,l_curr_period;
2871 CLOSE c_ltc_block;
2872
2873 l_curr_start_date := to_date(substr(l_curr_period,1,11),'DD-MM-YYYY');
2874 l_curr_end_date := to_date(substr(l_curr_period,15,11),'DD-MM-YYYY');
2875
2876 -- Current Block Previous Employment
2877 OPEN c_prev_employer_ltc_availed(l_curr_start_date
2878 ,l_curr_end_date
2879 ,p_assignment_id );
2880 FETCH c_prev_employer_ltc_availed INTO l_prev_emplr_curr_blk;
2881 CLOSE c_prev_employer_ltc_availed;
2882
2883 pay_in_utils.set_location(g_debug,'Previous Employer Current block '||l_prev_emplr_curr_blk,90);
2884
2885 -- Current Block Current Employment Exempted LTC entries that have been processed
2886 OPEN c_curr_emplr_ltc_block(l_curr_start_date
2887 ,l_curr_end_date
2888 ,'N' --carry over
2889 ,'Y' -- exempted
2890 ,l_carry_over_id
2891 ,l_exempted_id);
2892 FETCH c_curr_emplr_ltc_block INTO l_curr_emplr_curr_blk_exempted;
2893 CLOSE c_curr_emplr_ltc_block;
2894
2895 pay_in_utils.set_location(g_debug,'l_count '||l_count||' '||l_curr_emplr_curr_blk_exempted||' '||l_prev_emplr_curr_blk ,100);
2896
2897 /* Start - Find if the entries with invalid carry over are exempted in current block or not */
2898 IF l_count>0 then
2899 FOR i IN 0..l_count-1 LOOP
2900 IF(nvl(l_prev_emplr_curr_blk,0) +nvl(l_curr_emplr_curr_blk_exempted,0) + i < l_max_ltc ) THEN
2901 IF l_curr_element_entry(i)= p_element_entry_id THEN
2902 p_exempted_flag := 'Y';
2903 p_carry_over_flag := 'N';
2904 END IF;
2905 END IF;
2906 END LOOP;
2907 pay_in_utils.set_location(g_debug,'find exemption for invalid carry overs ',110);
2908 END IF;
2909 /* End - Find if the entries with invalid carry over are exempted in current block or not */
2910
2911 k := l_count;
2912 OPEN c_entry_id(l_carry_over_id, 'N');
2913 LOOP
2914 FETCH c_entry_id into l_curr_element_entry(k);
2915 EXIT WHEN c_entry_id%NOTFOUND;
2916 pay_in_utils.set_location(g_debug,'Entry id with carry over as No in current run '|| l_curr_element_entry(k),77);
2917 k := k+1;
2918 END LOOP;
2919 CLOSE c_entry_id;
2920
2921 k := l_curr_element_entry.COUNT;
2922 pay_in_utils.set_location(g_debug,'Entry Count '||k,70);
2923
2924 FOR i IN 0..k-1 LOOP
2925 IF(nvl(l_prev_emplr_curr_blk,0) +nvl(l_curr_emplr_curr_blk_exempted,0) + i < l_max_ltc ) THEN
2926 IF l_curr_element_entry(i)= p_element_entry_id THEN
2927 p_exempted_flag := 'Y';
2928 END IF;
2929 END IF;
2930 END LOOP;
2931
2932 pay_in_utils.trace('**************************************************','********************');
2933 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2934
2935 RETURN 0;
2936
2937 END check_ltc_exemption;
2938
2939 ----------------------------------------------------------------------------
2940 -- --
2941 -- Name : GET_BALANCE_VALUE --
2942 -- Type : Function --
2943 -- Access : Public --
2944 -- Description : Function to get the balance value --
2945 -- --
2946 -- Parameters : --
2947 -- IN : p_assignment_action_id NUMBER --
2948 -- p_balance_name VARCHAR2 --
2949 -- p_dimension_name VARCHAR2 --
2950 -- p_context_name VARCHAR2 --
2951 -- p_context_value VARCHAR2 --
2952 -- RETURN : NUMBER --
2953 -- --
2954 -- Change History : --
2955 ----------------------------------------------------------------------------
2956 -- Rev# Date Userid Description --
2957 ----------------------------------------------------------------------------
2958 -- 1.0 06-Apr-04 statkar Created this function --
2959 ----------------------------------------------------------------------------
2960 FUNCTION get_balance_value
2961 (p_assignment_action_id IN NUMBER
2962 ,p_balance_name IN pay_balance_types.balance_name%TYPE
2963 ,p_dimension_name IN pay_balance_dimensions.dimension_name%TYPE
2964 ,p_context_name IN ff_contexts.context_name%TYPE
2965 ,p_context_value IN VARCHAR2
2966 )
2967 RETURN NUMBER
2968 IS
2969 l_balance_value NUMBER ;
2970 l_message VARCHAR2(255);
2971 l_procedure VARCHAR2(100);
2972 l_def_bal_id NUMBER ;
2973 BEGIN
2974 g_debug := hr_utility.debug_enabled;
2975 l_procedure := g_package ||'get_balance_value';
2976 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure, 10);
2977 l_message := 'SUCCESS';
2978
2979 l_def_bal_id := get_defined_balance
2980 (p_balance_type => p_balance_name
2981 ,p_dimension_name => p_dimension_name);
2982
2983 pay_in_utils.set_location(g_debug,l_procedure, 20);
2984
2985 IF l_def_bal_id = -1 THEN
2986 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure, 25);
2987 RETURN -1;
2988 END IF;
2989
2990 IF g_debug THEN
2991 hr_utility.trace ('INDIA: Assignment Action Id : '||p_assignment_action_id);
2992 hr_utility.trace ('INDIA: Balance Name : '||p_balance_name);
2993 hr_utility.trace ('INDIA: Dimension_name : '||p_dimension_name);
2994 hr_utility.trace ('INDIA: Defined Balance Id : '||l_def_bal_id);
2995 hr_utility.trace ('INDIA: Context Name : '||p_context_name);
2996 hr_utility.trace ('INDIA: Context Value : '||p_context_value);
2997 END IF;
2998
2999 IF p_context_name = 'NULL' THEN
3000 pay_in_utils.set_location(g_debug,l_procedure, 30);
3001 l_balance_value := pay_balance_pkg.get_value
3002 (p_assignment_action_id => p_assignment_action_id
3003 ,p_defined_balance_id => l_def_bal_id
3004 ,p_tax_unit_id => null
3005 ,p_jurisdiction_code => null
3006 ,p_source_id => null
3007 ,p_source_text => null
3008 ,p_tax_group => null
3009 ,p_date_earned => null
3010 ,p_get_rr_route => null
3011 ,p_get_rb_route => 'TRUE'
3012 ,p_source_text2 => null
3013 ,p_source_number => null
3014 );
3015 ELSE
3016 pay_in_utils.set_location(g_debug,l_procedure, 40);
3017 IF p_context_name NOT IN ('SOURCE_ID'
3018 ,'SOURCE_TEXT'
3019 ,'SOURCE_TEXT2'
3020 ,'JURISDICTION_CODE'
3021 ,'TAX_UNIT_ID')
3022 THEN
3023 pay_in_utils.set_location(g_debug,l_procedure, 50);
3024 l_balance_value := -1;
3025 ELSE
3026 pay_in_utils.set_location(g_debug,l_procedure, 60);
3027 pay_balance_pkg.set_context(p_context_name, p_context_value);
3028 IF p_context_name = 'SOURCE_ID' THEN
3029 pay_in_utils.set_location(g_debug,l_procedure, 70);
3030 l_balance_value := pay_balance_pkg.get_value
3031 (p_assignment_action_id => p_assignment_action_id
3032 ,p_defined_balance_id => l_def_bal_id
3033 ,p_tax_unit_id => null
3034 ,p_jurisdiction_code => null
3035 ,p_source_id => TO_NUMBER(p_context_value)
3036 ,p_source_text => null
3037 ,p_tax_group => null
3038 ,p_date_earned => null
3039 ,p_get_rr_route => null
3040 ,p_get_rb_route => 'TRUE'
3041 ,p_source_text2 => null
3042 ,p_source_number => null
3043 );
3044 ELSIF p_context_name = 'SOURCE_TEXT' THEN
3045 pay_in_utils.set_location(g_debug,l_procedure, 80);
3046 l_balance_value := pay_balance_pkg.get_value
3047 (p_assignment_action_id => p_assignment_action_id
3048 ,p_defined_balance_id => l_def_bal_id
3049 ,p_tax_unit_id => null
3050 ,p_jurisdiction_code => null
3051 ,p_source_id => null
3052 ,p_source_text => p_context_value
3053 ,p_tax_group => null
3054 ,p_date_earned => null
3055 ,p_get_rr_route => null
3056 ,p_get_rb_route => 'TRUE'
3057 ,p_source_text2 => null
3058 ,p_source_number => null
3059 );
3060 ELSIF p_context_name = 'SOURCE_TEXT2' THEN
3061 pay_in_utils.set_location(g_debug,l_procedure, 90);
3062 l_balance_value := pay_balance_pkg.get_value
3063 (p_assignment_action_id => p_assignment_action_id
3064 ,p_defined_balance_id => l_def_bal_id
3065 ,p_tax_unit_id => null
3066 ,p_jurisdiction_code => null
3067 ,p_source_id => null
3068 ,p_source_text => null
3069 ,p_tax_group => null
3070 ,p_date_earned => null
3071 ,p_get_rr_route => null
3072 ,p_get_rb_route => 'TRUE'
3073 ,p_source_text2 => p_context_value
3074 ,p_source_number => null
3075 );
3076 ELSIF p_context_name = 'JURISDICTION_CODE' THEN
3077 pay_in_utils.set_location(g_debug,l_procedure, 100);
3078 l_balance_value := pay_balance_pkg.get_value
3079 (p_assignment_action_id => p_assignment_action_id
3080 ,p_defined_balance_id => l_def_bal_id
3081 ,p_tax_unit_id => null
3082 ,p_jurisdiction_code => p_context_value
3083 ,p_source_id => null
3084 ,p_source_text => null
3085 ,p_tax_group => null
3086 ,p_date_earned => null
3087 ,p_get_rr_route => null
3088 ,p_get_rb_route => 'TRUE'
3089 ,p_source_text2 => null
3090 ,p_source_number => null
3091 );
3092 ELSIF p_context_name = 'TAX_UNIT_ID' THEN
3093 pay_in_utils.set_location(g_debug,l_procedure, 110);
3094 l_balance_value := pay_balance_pkg.get_value
3095 (p_assignment_action_id => p_assignment_action_id
3096 ,p_defined_balance_id => l_def_bal_id
3097 ,p_tax_unit_id => TO_NUMBER(p_context_value)
3098 ,p_jurisdiction_code => null
3099 ,p_source_id => null
3100 ,p_source_text => null
3101 ,p_tax_group => null
3102 ,p_date_earned => null
3103 ,p_get_rr_route => null
3104 ,p_get_rb_route => 'TRUE'
3105 ,p_source_text2 => null
3106 ,p_source_number => null
3107 );
3108 END IF;
3109 END IF;
3110 END IF;
3111
3112 hr_utility.trace ('INDIA: Balance Value : '||to_char(l_balance_value));
3113 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure, 120);
3114 RETURN l_balance_value;
3115
3116 EXCEPTION
3117 WHEN OTHERS THEN
3118 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
3119 hr_utility.trace(l_message);
3120 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 130);
3121 RETURN -1;
3122 END get_balance_value;
3123
3124
3125 --------------------------------------------------------------------------
3126 -- Name : get_org_id --
3127 -- Type : Function --
3128 -- Access : Public --
3129 -- Description : Function to get the Org Id of PF/ESI/PT Organization--
3130 -- on a particular date --
3131 -- Parameters : --
3132 -- IN : p_assignment_id IN NUMBER --
3133 -- p_business_group_id IN NUMBER --
3134 -- p_date IN DATE --
3135 -- p_org_type IN VARCHAR2 --
3136 -- Change History : --
3137 --------------------------------------------------------------------------
3138 -- Rev# Date Userid Description --
3139 --------------------------------------------------------------------------
3140 -- 1.0 08-Apr-05 abhjain Created this function to get the org id --
3141 --------------------------------------------------------------------------
3142 FUNCTION get_org_id(p_assignment_id IN NUMBER
3143 ,p_business_group_id IN NUMBER
3144 ,p_date IN DATE
3145 ,p_org_type IN VARCHAR2)
3146 RETURN NUMBER
3147 IS
3148 CURSOR cur_org (p_assignment_id NUMBER
3149 ,p_business_group_id NUMBER
3150 ,p_date DATE)
3151 IS
3152 SELECT hsc.segment2
3153 ,hsc.segment3
3154 ,hsc.segment4
3155 FROM per_assignments_f paf
3156 ,hr_soft_coding_keyflex hsc
3157 WHERE paf.assignment_id = p_assignment_id
3158 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3159 AND paf.business_group_id = p_business_group_id
3160 AND p_date BETWEEN paf.effective_start_date
3161 AND paf.effective_end_date;
3162
3163 l_segment2 hr_soft_coding_keyflex.segment1%TYPE;
3164 l_segment3 hr_soft_coding_keyflex.segment1%TYPE;
3165 l_segment4 hr_soft_coding_keyflex.segment1%TYPE;
3166 l_message VARCHAR2(255);
3167 l_procedure VARCHAR2(100);
3168
3169 BEGIN
3170
3171 l_procedure := g_package||'get_org_id';
3172 g_debug := hr_utility.debug_enabled;
3173
3174 pay_in_utils.set_location(g_debug,'Entering : '||l_procedure, 10);
3175
3176 OPEN cur_org (p_assignment_id
3177 ,p_business_group_id
3178 ,p_date);
3179 FETCH cur_org into l_segment2
3180 ,l_segment3
3181 ,l_segment4;
3182 pay_in_utils.set_location (g_debug,'l_segment2 = '||l_segment2,20);
3183 pay_in_utils.set_location (g_debug,'l_segment3 = '||l_segment3,30);
3184 pay_in_utils.set_location (g_debug,'l_segment4 = '||l_segment4,40);
3185 CLOSE cur_org;
3186
3187 IF p_org_type = 'PF' THEN
3188 RETURN to_number(l_segment2);
3189 ELSIF p_org_type = 'PT' THEN
3190 RETURN to_number(l_segment3);
3191 ELSIF p_org_type = 'ESI' THEN
3192 RETURN to_number(l_segment4);
3193 END IF;
3194
3195 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
3196
3197 EXCEPTION
3198 WHEN OTHERS THEN
3199 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
3200 pay_in_utils.set_location(g_debug,' Leaving : '||l_procedure, 30);
3201 hr_utility.trace(l_message);
3202 RETURN NULL;
3203
3204
3205 END get_org_id;
3206
3207 --------------------------------------------------------------------------
3208 -- Name : le_start_date --
3209 -- Type : Function --
3210 -- Access : Private --
3211 -- Description : Function to get the LE start date --
3212 -- Parameters : --
3213 -- IN : p_tax_unit_id IN NUMBER --
3214 -- p_assignment_id IN NUMBER --
3215 -- p_effective_date IN DATE --
3216 -- Change History : --
3217 --------------------------------------------------------------------------
3218 -- Rev# Date Userid Description --
3219 --------------------------------------------------------------------------
3220 -- 1.0 19-Jul-05 statkar Created this function --
3221 --------------------------------------------------------------------------
3222 FUNCTION le_start_date(p_tax_unit_id IN NUMBER
3223 ,p_assignment_id IN NUMBER
3224 ,p_effective_date IN DATE
3225 )
3226 RETURN DATE
3227 IS
3228 l_le_asg_start DATE;
3229
3230 CURSOR csr_asg_start IS
3231 SELECT MAX(asg.effective_end_date) + 1
3232 FROM per_all_assignments_f asg
3233 , hr_soft_coding_keyflex scl
3234 WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
3235 AND nvl(scl.segment1,'-1')<> TO_CHAR(p_tax_unit_id)
3236 AND asg.assignment_id = p_assignment_id
3237 AND asg.effective_end_date < p_effective_date;
3238
3239 CURSOR csr_asg_start_le
3240 IS
3241 select min(asg.effective_start_date)
3242 from per_all_assignments_f asg
3243 , hr_soft_coding_keyflex scl
3244 WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
3245 AND scl.segment1 = TO_CHAR(p_tax_unit_id)
3246 AND asg.assignment_id = p_assignment_id
3247 AND asg.effective_start_date < p_effective_date;
3248
3249
3250 l_procedure VARCHAR2(250);
3251 l_message VARCHAR2(250);
3252 BEGIN
3253 g_debug := hr_utility.debug_enabled;
3254 l_procedure := g_package ||'le_start_date';
3255 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3256 IF (g_debug)
3257 THEN
3258 pay_in_utils.trace('**************************************************','********************');
3259 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
3260 pay_in_utils.trace('p_tax_unit_id',p_tax_unit_id);
3261 pay_in_utils.trace('p_assignment_id',p_assignment_id);
3262 pay_in_utils.trace('p_effective_date',p_effective_date);
3263 END IF;
3264
3265 OPEN csr_asg_start;
3266 FETCH csr_asg_start INTO l_le_asg_start;
3267 CLOSE csr_asg_start;
3268
3269 IF (g_debug)
3270 THEN
3271 pay_in_utils.trace('l_le_asg_start',l_le_asg_start);
3272 END IF;
3273
3274
3275 IF l_le_asg_start IS NULL THEN
3276 OPEN csr_asg_start_le;
3277 FETCH csr_asg_start_le INTO l_le_asg_start;
3278 CLOSE csr_asg_start_le;
3279 END IF;
3280
3281 IF (g_debug)
3282 THEN
3283 pay_in_utils.trace('l_le_asg_start',l_le_asg_start);
3284 END IF;
3285
3286 RETURN l_le_asg_start;
3287
3288 pay_in_utils.trace('**************************************************','********************');
3289 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3290
3291 END le_start_date;
3292
3293 --------------------------------------------------------------------------
3294 -- Name : le_end_date --
3295 -- Type : Function --
3296 -- Access : Private --
3297 -- Description : Function to get the LE end date --
3298 -- Parameters : --
3299 -- IN : p_tax_unit_id IN NUMBER --
3300 -- p_assignment_id IN NUMBER --
3301 -- p_effective_date IN DATE --
3302 -- Change History : --
3303 --------------------------------------------------------------------------
3304 -- Rev# Date Userid Description --
3305 --------------------------------------------------------------------------
3306 -- 1.0 19-Jul-05 statkar Created this function --
3307 --------------------------------------------------------------------------
3308 FUNCTION le_end_date(p_tax_unit_id IN NUMBER
3309 ,p_assignment_id IN NUMBER
3310 ,p_effective_date IN DATE
3311 )
3312 RETURN DATE
3313 IS
3314 l_le_asg_end DATE;
3315
3316 CURSOR csr_asg_end IS
3317 SELECT MIN(asg.effective_start_date) -1
3318 FROM per_all_assignments_f asg
3319 , hr_soft_coding_keyflex scl
3320 WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
3321 AND NVL(scl.segment1,'-1')<> TO_CHAR(p_tax_unit_id)
3322 AND asg.assignment_id = p_assignment_id
3323 AND asg.effective_start_date > p_effective_date;
3324
3325 CURSOR csr_asg_end_le
3326 IS
3327 select max(asg.effective_end_date)
3328 from per_all_assignments_f asg
3329 , hr_soft_coding_keyflex scl
3330 WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
3331 AND scl.segment1 = TO_CHAR(p_tax_unit_id)
3332 AND asg.assignment_id = p_assignment_id
3333 AND asg.effective_end_date >= p_effective_date;
3334
3335 l_procedure VARCHAR2(250);
3336 l_message VARCHAR2(250);
3337 BEGIN
3338 g_debug := hr_utility.debug_enabled;
3339 l_procedure := g_package ||'le_end_date';
3340 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3341
3342 IF (g_debug)
3343 THEN
3344 pay_in_utils.trace('**************************************************','********************');
3345 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
3346 pay_in_utils.trace('p_tax_unit_id',p_tax_unit_id);
3347 pay_in_utils.trace('p_assignment_id',p_assignment_id);
3348 pay_in_utils.trace('p_effective_date',p_effective_date);
3349 END IF;
3350
3351
3352 OPEN csr_asg_end;
3353 FETCH csr_asg_end INTO l_le_asg_end;
3354 CLOSE csr_asg_end;
3355
3356 IF l_le_asg_end IS NULL THEN
3357 OPEN csr_asg_end_le;
3358 FETCH csr_asg_end_le INTO l_le_asg_end;
3359 CLOSE csr_asg_end_le;
3360 END IF;
3361
3362 IF (g_debug)
3363 THEN
3364 pay_in_utils.trace('l_le_asg_start',l_le_asg_end);
3365 END IF;
3366
3367 pay_in_utils.trace('**************************************************','********************');
3368 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3369
3370 RETURN l_le_asg_end;
3371
3372 END le_end_date;
3373
3374 --------------------------------------------------------------------------
3375 -- Name : get_pay_periods --
3376 -- Type : Function --
3377 -- Access : Public --
3378 -- Description : Function to get the balance periods in the current --
3379 -- tax year --
3380 -- Parameters : --
3381 -- IN : p_payroll_id IN NUMBER --
3382 -- p_tax_unit_id IN NUMBER --
3383 -- p_assignment_id IN NUMBER --
3384 -- p_period_end_date IN DATE --
3385 -- p_termination_date IN DATE --
3386 -- p_period_number IN NUMBER --
3387 -- p_condition IN VARCHAR2 --
3388 -- Change History : --
3389 --------------------------------------------------------------------------
3390 -- Rev# Date Userid Description --
3391 --------------------------------------------------------------------------
3392 -- 1.0 27-Apr-05 lnagaraj Created this function --
3393 -- 2.0 18-Jul-05 statkar Added LE change functionality --
3394 -- 3.0 04-Jun-07 rsaharay TO calculate LRPP correctly FOR --
3395 -- employees terminated --
3396 -- IN previous financial year. --
3397 --------------------------------------------------------------------------
3398 FUNCTION get_pay_periods (p_payroll_id IN NUMBER
3399 ,p_tax_unit_id IN NUMBER
3400 ,p_assignment_id IN NUMBER
3401 ,p_date_earned IN DATE
3402 ,p_period_end_date IN DATE
3403 ,p_termination_date IN DATE
3404 ,p_period_number IN NUMBER
3405 ,p_condition IN VARCHAR2
3406 )
3407 RETURN NUMBER IS
3408
3409 l_rem_pay_periods NUMBER;
3410 l_le_end DATE;
3411 l_tot_pay_periods NUMBER;
3412 l_year_end DATE;
3413 l_year_start DATE;
3414 l_term DATE;
3415 l_end_date DATE;
3416 l_procedure VARCHAR2(250);
3417 l_message VARCHAR2(250);
3418
3419 BEGIN
3420 g_debug := hr_utility.debug_enabled;
3421 l_procedure := g_package ||'get_pay_periods';
3422 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3423 IF (g_debug)
3424 THEN
3425 pay_in_utils.trace('**************************************************','********************');
3426 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
3427 pay_in_utils.trace('p_payroll_id ',p_payroll_id );
3428 pay_in_utils.trace('p_tax_unit_id ',p_tax_unit_id );
3429 pay_in_utils.trace('p_assignment_id ',p_assignment_id );
3430 pay_in_utils.trace('p_date_earned ',p_date_earned );
3431 pay_in_utils.trace('p_period_end_date ',p_period_end_date );
3432 pay_in_utils.trace('p_termination_date',p_termination_date);
3433 pay_in_utils.trace('p_period_number ',p_period_number );
3434 pay_in_utils.trace('p_condition ',p_condition );
3435 END IF;
3436
3437 l_tot_pay_periods :=12;
3438 -- Bug 12401381
3439 l_year_end := get_tax_year_end(p_payroll_id, p_period_end_date);
3440 l_year_start := get_tax_year_start(p_payroll_id, p_period_end_date);
3441
3442 hr_utility.trace('p_payroll_id = '||to_char(p_payroll_id));
3443 hr_utility.trace('p_tax_unit_id = '||to_char(p_tax_unit_id));
3444 hr_utility.trace('p_period_number = '||to_char(p_period_number));
3445 hr_utility.trace('l_year_end = '||to_char(l_year_end,'DD-MM-YYYY'));
3446 hr_utility.trace('l_year_start = '||to_char(l_year_start,'DD-MM-YYYY'));
3447
3448 IF p_condition = 'GRE' THEN
3449 l_le_end := le_end_date(p_tax_unit_id, p_assignment_id, p_date_earned);
3450 ELSE
3451 l_le_end := l_year_end;
3452 END IF;
3453 hr_utility.trace('l_le_end = '||to_char(l_le_end,'DD-MM-YYYY'));
3454
3455 l_term := GREATEST(p_termination_date, l_year_start);
3456
3457 l_end_date := LEAST(l_year_end, l_le_end, l_term);
3458
3459 hr_utility.trace('l_end_date = '||to_char(l_end_date,'DD-MM-YYYY'));
3460
3461 l_tot_pay_periods := get_period_number(p_payroll_id,l_end_date);
3462 l_rem_pay_periods := GREATEST(l_tot_pay_periods - p_period_number, 0);
3463
3464 pay_in_utils.trace('**************************************************','********************');
3465 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3466
3467 RETURN l_rem_pay_periods;
3468
3469 END get_pay_periods;
3470
3471 --------------------------------------------------------------------------
3472 -- Name : get_income_tax --
3473 -- Type : Function --
3474 -- Access : Public --
3475 -- Description : Function to get the income tax,surcharge, education --
3476 -- cess --
3477 -- Parameters : --
3478 -- IN : p_business_group_id IN NUMBER --
3479 -- p_total_income IN NUMBER --
3480 -- p_gender IN VARCHAR2 --
3481 -- p_age IN NUMBER --
3482 -- p_pay_end_date IN DATE --
3483 -- p_marginal_relief OUT NUMBER --
3484 -- p_surcharge OUT NUMBER --
3485 -- p_education_cess OUT NUMBER --
3486 -- p_message OUT VARCHAR2 --
3487 -- Change History : --
3488 --------------------------------------------------------------------------
3489 -- Rev# Date Userid Description --
3490 --------------------------------------------------------------------------
3491 -- 1.0 27-Apr-05 lnagaraj Created this function --
3492 --------------------------------------------------------------------------
3493 FUNCTION get_income_tax(p_business_group_id IN NUMBER
3494 ,p_assignment_id IN NUMBER
3495 ,p_total_income IN NUMBER
3496 ,p_gender IN VARCHAR2
3497 ,p_age IN NUMBER
3498 ,p_pay_end_date IN DATE
3499 ,p_marginal_relief OUT NOCOPY NUMBER
3500 ,p_surcharge OUT NOCOPY NUMBER
3501 ,p_education_cess OUT NOCOPY NUMBER
3502 ,p_message OUT NOCOPY VARCHAR2
3503 ,p_sec_and_he_cess OUT NOCOPY NUMBER
3504 ,p_pay_date_paid IN DATE)
3505 RETURN NUMBER
3506 IS
3507
3508 l_tax_slab NUMBER;
3509 l_additional_amount NUMBER;
3510 l_reduced_amount NUMBER;
3511 l_income_tax NUMBER;
3512
3513 --Variables declared for Bug 12376004
3514 l_tax_slab_wo_pan NUMBER;
3515 l_additional_amount_wo_pan NUMBER;
3516 l_reduced_amount_wo_pan NUMBER;
3517 l_income_tax_wo_pan NUMBER;
3518 --End of Bug 12376004
3519
3520 l_surcharge_applicable_amt NUMBER;
3521 l_relief_ceiling ff_globals_f.global_name%TYPE;
3522 l_relief_limit NUMBER;
3523 tax_on_mr_ceiling NUMBER;
3524
3525 l_cess_percent NUMBER;
3526 l_sec_and_he_cess_percent NUMBER;
3527
3528 l_table_name VARCHAR2(100);
3529 p_tax_on_income NUMBER;
3530 l_pan per_all_people_f.per_information4%TYPE;
3531
3532 l_rebate_amount NUMBER;
3533 l_minimum_limit NUMBER;
3534 l_maximum_limit NUMBER; /*budget 2013 changes */
3535
3536 CURSOR csr_global_value(p_global_name IN VARCHAR2
3537 ,p_date IN DATE)
3538 IS
3539 SELECT fnd_number.canonical_to_number(glb.global_value)
3540 FROM ff_globals_f glb
3541 WHERE glb.global_name = p_global_name
3542 AND p_date BETWEEN glb.effective_start_date
3543 AND glb.effective_end_date
3544 AND glb.legislation_code='IN';
3545
3546 l_procedure VARCHAR2(250);
3547 l_message VARCHAR2(250);
3548 BEGIN
3549 g_debug := hr_utility.debug_enabled;
3550 l_procedure := g_package ||'get_income_tax';
3551 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3552 IF (g_debug)
3553 THEN
3554 pay_in_utils.trace('**************************************************','********************');
3555 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
3556 pay_in_utils.trace('p_business_group_id',p_business_group_id);
3557 pay_in_utils.trace('p_assignment_id ',p_assignment_id );
3558 pay_in_utils.trace('p_total_income ',p_total_income );
3559 pay_in_utils.trace('p_gender ',p_gender );
3560 pay_in_utils.trace('p_age ',p_age );
3561 pay_in_utils.trace('p_pay_end_date ',p_pay_end_date );
3562 pay_in_utils.trace('p_pay_date_paid ',p_pay_date_paid );
3563 END IF;
3564
3565 l_income_tax:=0;
3566
3567 -- Start of Bug 11821281
3568 SELECT pep.per_information4 INTO l_pan
3569 FROM per_all_people_f pep,
3570 per_all_assignments_f asg,
3571 per_periods_of_service pos
3572 WHERE asg.assignment_id = p_assignment_id
3573 AND asg.person_id = pep.person_id
3574 AND pos.person_id = asg.person_id
3575 AND pos.period_of_service_id = asg.period_of_service_id
3576 AND decode(pos.actual_termination_date,'',p_pay_end_date,pos.actual_termination_date) BETWEEN asg.effective_start_date AND asg.effective_end_date
3577 AND decode(pos.actual_termination_date,'',p_pay_end_date,pos.actual_termination_date) BETWEEN pep.effective_start_date AND pep.effective_end_date ;
3578 -- End of Bug 11821281
3579
3580 /* Commented the code for 12376004*/
3581 -- Start of 11838969
3582 /* IF (p_pay_date_paid >= TO_DATE('01-04-2011','DD-MM-YYYY')) THEN
3583 IF (l_pan is null)
3584 THEN
3585 IF p_age >=80 THEN
3586 l_table_name :='India Income Tax Rates for Very Senior Citizen';
3587 ELSIF p_age >=60 THEN
3588 l_table_name :='Income Tax Rates for Senior Citizen without PAN';
3589 ELSIF p_gender = 'F' THEN
3590 l_table_name := 'Income Tax Rates for Women without PAN';
3591 ELSE
3592 l_table_name := 'Income Tax Rates without PAN';
3593 END IF;
3594 ELSE
3595 IF p_age >=80 THEN
3596 l_table_name :='India Income Tax Rates for Very Senior Citizen';
3597 ELSIF p_age >=60 THEN
3598 l_table_name :='India Income Tax Rates for Senior Citizen';
3599 ELSIF p_gender = 'F' THEN
3600 l_table_name := 'India Income Tax Rates for Women';
3601 ELSE
3602 l_table_name := 'India Income Tax Rates';
3603 END IF;
3604 END IF;
3605 ELSE -- End of 11838969
3606 IF ( (l_pan is null) AND (p_pay_date_paid >= TO_DATE('01-04-2010','DD-MM-YYYY')))
3607 THEN
3608 IF p_age >=65 THEN
3609 l_table_name :='Income Tax Rates for Senior Citizen without PAN';
3610 ELSIF p_gender = 'F' THEN
3611 l_table_name := 'Income Tax Rates for Women without PAN';
3612 ELSE
3613 l_table_name := 'Income Tax Rates without PAN';
3614 END IF;
3615 ELSE
3616 IF p_age >=65 THEN
3617 l_table_name :='India Income Tax Rates for Senior Citizen';
3618 ELSIF p_gender = 'F' THEN
3619 l_table_name := 'India Income Tax Rates for Women';
3620 ELSE
3621 l_table_name := 'India Income Tax Rates';
3622 END IF;
3623 END IF;
3624 END IF; --Added for 11838969
3625 */
3626 --End of code commented for 12376004
3627 --Start of Bug 12376004
3628 IF (p_pay_date_paid >= TO_DATE('01-04-2011','DD-MM-YYYY')) THEN
3629 IF p_age >=80 THEN
3630 l_table_name :='India Income Tax Rates for Very Senior Citizen';
3631 ELSIF p_age >=60 THEN
3632 l_table_name :='India Income Tax Rates for Senior Citizen';
3633 ELSIF p_gender = 'F' THEN
3634 l_table_name := 'India Income Tax Rates for Women';
3635 ELSE
3636 l_table_name := 'India Income Tax Rates';
3637 END IF;
3638 ELSE
3639 IF p_age >=65 THEN
3640 l_table_name :='India Income Tax Rates for Senior Citizen';
3641 ELSIF p_gender = 'F' THEN
3642 l_table_name := 'India Income Tax Rates for Women';
3643 ELSE
3644 l_table_name := 'India Income Tax Rates';
3645 END IF;
3646 END IF;
3647 --End of Bug 12376004
3648
3649 IF (g_debug)
3650 THEN
3651 pay_in_utils.trace('l_table_name',l_table_name);
3652 END IF;
3653
3654 l_tax_slab := fnd_number.canonical_to_number(pay_in_utils.get_user_table_value
3655 (p_business_group_id => p_business_group_id
3656 ,p_table_name => l_table_name
3657 ,p_column_name => 'Tax Rate'
3658 ,p_row_name => 'Tax Slabs'
3659 ,p_row_value => p_total_income
3660 ,p_effective_date => p_pay_date_paid
3661 ,p_message => p_message
3662 ));
3663
3664 l_additional_amount := fnd_number.canonical_to_number(pay_in_utils.get_user_table_value
3665 (p_business_group_id => p_business_group_id
3666 ,p_table_name => l_table_name
3667 ,p_column_name => 'Additional Amount'
3668 ,p_row_name => 'Tax Slabs'
3669 ,p_row_value => p_total_income
3670 ,p_effective_date => p_pay_date_paid
3671 ,p_message => p_message
3672 ));
3673
3674 l_reduced_amount := fnd_number.canonical_to_number(pay_in_utils.get_user_table_value
3675 (p_business_group_id => p_business_group_id
3676 ,p_table_name => l_table_name
3677 ,p_column_name => 'Reduced Amount'
3678 ,p_row_name => 'Tax Slabs'
3679 ,p_row_value => p_total_income
3680 ,p_effective_date => p_pay_date_paid
3681 ,p_message => p_message
3682 ));
3683
3684 IF (g_debug)
3685 THEN
3686 pay_in_utils.trace('l_tax_slab',l_tax_slab);
3687 pay_in_utils.trace('l_additional_amount',l_additional_amount);
3688 pay_in_utils.trace('l_reduced_amount',l_reduced_amount);
3689 END IF;
3690
3691 l_income_tax := l_tax_slab * (p_total_income - l_reduced_amount) + l_additional_amount;
3692
3693 /* start of budget 2013 change */
3694
3695 OPEN csr_global_value('IN_SECTION87A_REBATE',p_pay_date_paid);
3696 FETCH csr_global_value INTO l_rebate_amount;
3697 CLOSE csr_global_value;
3698
3699 OPEN csr_global_value('IN_SECTION87A_MIN_LIMIT',p_pay_date_paid);
3700 FETCH csr_global_value INTO l_minimum_limit;
3701 CLOSE csr_global_value;
3702
3703 OPEN csr_global_value('IN_SECTION87A_MAX_LIMIT',p_pay_date_paid);
3704 FETCH csr_global_value INTO l_maximum_limit;
3705 CLOSE csr_global_value;
3706
3707 IF(g_debug)
3708 THEN
3709 pay_in_utils.trace('l_rebate_amount',l_rebate_amount);
3710 pay_in_utils.trace('l_minimum_limit',l_minimum_limit);
3711 pay_in_utils.trace('l_maximum_limit',l_maximum_limit);
3712 pay_in_utils.trace('l_income_tax',l_income_tax);
3713 END IF;
3714
3715
3716 IF (p_total_income >=l_minimum_limit
3717 AND p_total_income <= l_maximum_limit
3718 AND p_pay_date_paid >= TO_DATE('01-04-2013','DD-MM-YYYY')
3719 AND l_pan IS NOT NULL)
3720 THEN
3721 l_income_tax := greatest(l_income_tax - l_rebate_amount,0);
3722
3723 END IF;
3724 /*end of Budget 2013 changes*/
3725
3726 p_marginal_relief := 0;
3727 p_surcharge := 0;
3728 p_education_cess := 0;
3729 p_sec_and_he_cess := 0;
3730
3731 OPEN csr_global_value('IN_SURCHARGE_APPLICABLE_AMOUNT',p_pay_date_paid);
3732 FETCH csr_global_value INTO l_surcharge_applicable_amt;
3733 CLOSE csr_global_value;
3734
3735 IF (g_debug)
3736 THEN
3737 pay_in_utils.trace('l_income_tax_new',l_income_tax);
3738 pay_in_utils.trace('l_surcharge_applicable_amt',l_surcharge_applicable_amt);
3739 END IF;
3740
3741
3742 /* Calculate Surcharge and marginal relief */
3743 IF p_total_income > l_surcharge_applicable_amt THEN
3744 p_surcharge := 0.1 * l_income_tax;
3745
3746 IF p_age >= 65 THEN
3747 l_relief_ceiling := 'IN_MARGINAL_RELIEF_SENIORS';
3748 ELSIF p_gender = 'F' THEN
3749 l_relief_ceiling := 'IN_MARGINAL_RELIEF_FEMALES';
3750 ELSE
3751 l_relief_ceiling := 'IN_MARGINAL_RELIEF';
3752 END IF;
3753
3754 OPEN csr_global_value(l_relief_ceiling,p_pay_date_paid);
3755 FETCH csr_global_value INTO l_relief_limit;
3756 CLOSE csr_global_value;
3757
3758 IF (g_debug)
3759 THEN
3760 pay_in_utils.trace('l_relief_ceiling',l_relief_ceiling);
3761 pay_in_utils.trace('l_relief_limit',l_relief_limit);
3762 pay_in_utils.trace('p_pay_end_date',p_pay_end_date);
3763 END IF;
3764
3765 IF p_total_income <= l_relief_limit THEN
3766 tax_on_mr_ceiling := l_additional_amount
3767 + (l_surcharge_applicable_amt - l_reduced_amount) * l_tax_slab;
3768
3769 p_marginal_relief := l_income_tax
3770 + p_surcharge
3771 - tax_on_mr_ceiling
3772 - (p_total_income - l_surcharge_applicable_amt);
3773 END IF;
3774
3775 END IF;
3776
3777
3778 p_tax_on_income := l_income_tax + GREATEST (p_surcharge - p_marginal_relief,0);
3779
3780 OPEN csr_global_value('IN_EDUCATION_CESS_PERCENTAGE',p_pay_date_paid);
3781 FETCH csr_global_value INTO l_cess_percent;
3782 CLOSE csr_global_value;
3783
3784 p_education_cess := l_cess_percent * p_tax_on_income ;
3785
3786 OPEN csr_global_value('IN_SEC_AND_HE_CESS_PERCENTAGE',p_pay_date_paid);
3787 IF csr_global_value%NOTFOUND THEN
3788 l_sec_and_he_cess_percent:=0;
3789 END IF ;
3790 FETCH csr_global_value INTO l_sec_and_he_cess_percent;
3791 CLOSE csr_global_value;
3792
3793 p_sec_and_he_cess := l_sec_and_he_cess_percent * p_tax_on_income ;
3794
3795 p_marginal_relief := GREATEST(p_marginal_relief,0);
3796 p_surcharge := GREATEST(p_surcharge,0);
3797 p_education_cess := GREATEST(p_education_cess,0);
3798 p_sec_and_he_cess := GREATEST(p_sec_and_he_cess,0);
3799 l_income_tax := GREATEST(l_income_tax,0);
3800
3801 IF (g_debug)
3802 THEN
3803 pay_in_utils.trace('l_cess_percent',l_cess_percent);
3804 pay_in_utils.trace('p_marginal_relief',p_marginal_relief);
3805 pay_in_utils.trace('p_surcharge',p_surcharge);
3806 pay_in_utils.trace('p_education_cess',p_education_cess);
3807 pay_in_utils.trace('p_sec_and_he_cess',p_sec_and_he_cess);
3808 pay_in_utils.trace('l_income_tax',l_income_tax);
3809 pay_in_utils.trace('p_tax_on_income',p_tax_on_income);
3810 END IF;
3811
3812 /* Added code for Bug 12376004*/
3813 /* Checking if PAN is not available where Income Tax is applicable */
3814 IF ( p_pay_date_paid >= TO_DATE('01-04-2010','DD-MM-YYYY') AND l_pan is null AND l_income_tax > 0 ) THEN
3815
3816 l_table_name := 'India Income Tax Rates without PAN';
3817 IF (g_debug) THEN
3818 pay_in_utils.trace('l_table_name',l_table_name);
3819 END IF;
3820
3821 l_tax_slab_wo_pan := fnd_number.canonical_to_number(pay_in_utils.get_user_table_value
3822 (p_business_group_id => p_business_group_id
3823 ,p_table_name => l_table_name
3824 ,p_column_name => 'Tax Rate'
3825 ,p_row_name => 'Tax Slabs'
3826 ,p_row_value => p_total_income
3827 ,p_effective_date => p_pay_date_paid
3828 ,p_message => p_message
3829 ));
3830
3831 l_additional_amount_wo_pan := fnd_number.canonical_to_number(pay_in_utils.get_user_table_value
3832 (p_business_group_id => p_business_group_id
3833 ,p_table_name => l_table_name
3834 ,p_column_name => 'Additional Amount'
3835 ,p_row_name => 'Tax Slabs'
3836 ,p_row_value => p_total_income
3837 ,p_effective_date => p_pay_date_paid
3838 ,p_message => p_message
3839 ));
3840
3841 l_reduced_amount_wo_pan := fnd_number.canonical_to_number(pay_in_utils.get_user_table_value
3842 (p_business_group_id => p_business_group_id
3843 ,p_table_name => l_table_name
3844 ,p_column_name => 'Reduced Amount'
3845 ,p_row_name => 'Tax Slabs'
3846 ,p_row_value => p_total_income
3847 ,p_effective_date => p_pay_date_paid
3848 ,p_message => p_message
3849 ));
3850
3851 l_income_tax_wo_pan := l_tax_slab_wo_pan * (p_total_income - l_reduced_amount_wo_pan) + l_additional_amount_wo_pan;
3852
3853 IF (g_debug) THEN
3854 pay_in_utils.trace('**************************************************','********************');
3855 pay_in_utils.trace('l_tax_slab_wo_pan',l_tax_slab_wo_pan);
3856 pay_in_utils.trace('l_additional_amount_wo_pan',l_additional_amount_wo_pan);
3857 pay_in_utils.trace('l_reduced_amount_wo_pan',l_reduced_amount_wo_pan);
3858 pay_in_utils.trace('l_cess_percent',l_cess_percent);
3859 pay_in_utils.trace('p_marginal_relief',p_marginal_relief);
3860 pay_in_utils.trace('p_surcharge',p_surcharge);
3861 pay_in_utils.trace('p_education_cess',p_education_cess);
3862 pay_in_utils.trace('p_sec_and_he_cess',p_sec_and_he_cess);
3863 pay_in_utils.trace('l_income_tax',l_income_tax);
3864 pay_in_utils.trace('l_income_tax_wo_pan',l_income_tax_wo_pan);
3865 pay_in_utils.trace('p_tax_on_income',p_tax_on_income);
3866 END IF;
3867
3868 IF ( l_income_tax_wo_pan > (l_income_tax + p_education_cess + p_sec_and_he_cess )) THEN
3869 l_income_tax := l_income_tax_wo_pan;
3870 p_education_cess :=0;
3871 p_sec_and_he_cess := 0;
3872 END IF;
3873
3874 IF (g_debug) THEN
3875 pay_in_utils.trace('**************************************************','********************');
3876 pay_in_utils.trace('l_tax_slab_wo_pan',l_tax_slab_wo_pan);
3877 pay_in_utils.trace('l_additional_amount_wo_pan',l_additional_amount_wo_pan);
3878 pay_in_utils.trace('l_reduced_amount_wo_pan',l_reduced_amount_wo_pan);
3879 pay_in_utils.trace('p_education_cess',p_education_cess);
3880 pay_in_utils.trace('p_sec_and_he_cess',p_sec_and_he_cess);
3881 pay_in_utils.trace('l_income_tax',l_income_tax);
3882 END IF;
3883
3884 END IF;
3885 /* End of Bug 12376004*/
3886
3887 pay_in_utils.trace('**************************************************','********************');
3888 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3889
3890 RETURN l_income_tax;
3891 END get_income_tax;
3892
3893
3894 FUNCTION get_income_tax(p_business_group_id IN NUMBER
3895 ,p_assignment_id IN NUMBER
3896 ,p_total_income IN NUMBER
3897 ,p_gender IN VARCHAR2
3898 ,p_age IN NUMBER
3899 ,p_pay_end_date IN DATE
3900 ,p_marginal_relief OUT NOCOPY NUMBER
3901 ,p_surcharge OUT NOCOPY NUMBER
3902 ,p_education_cess OUT NOCOPY NUMBER
3903 ,p_message OUT NOCOPY VARCHAR2
3904 ,p_sec_and_he_cess OUT NOCOPY NUMBER
3905 ,p_pay_date_paid IN DATE
3906 ,p_sec87_rebate OUT NOCOPY NUMBER )
3907 RETURN NUMBER
3908 IS
3909
3910 l_tax_slab NUMBER;
3911 l_additional_amount NUMBER;
3912 l_reduced_amount NUMBER;
3913 l_income_tax NUMBER;
3914
3915 --Variables declared for Bug 12376004
3916 l_tax_slab_wo_pan NUMBER;
3917 l_additional_amount_wo_pan NUMBER;
3918 l_reduced_amount_wo_pan NUMBER;
3919 l_income_tax_wo_pan NUMBER;
3920 --End of Bug 12376004
3921
3922 l_surcharge_applicable_amt NUMBER;
3923 l_relief_ceiling ff_globals_f.global_name%TYPE;
3924 l_relief_limit NUMBER;
3925 tax_on_mr_ceiling NUMBER;
3926
3927 l_cess_percent NUMBER;
3928 l_sec_and_he_cess_percent NUMBER;
3929
3930 l_table_name VARCHAR2(100);
3931 p_tax_on_income NUMBER;
3932 l_pan per_all_people_f.per_information4%TYPE;
3933
3934 l_rebate_amount NUMBER;
3935 l_minimum_limit NUMBER;
3936 l_maximum_limit NUMBER; /*budget 2013 changes */
3937
3938 CURSOR csr_global_value(p_global_name IN VARCHAR2
3939 ,p_date IN DATE)
3940 IS
3941 SELECT fnd_number.canonical_to_number(glb.global_value)
3942 FROM ff_globals_f glb
3943 WHERE glb.global_name = p_global_name
3944 AND p_date BETWEEN glb.effective_start_date
3945 AND glb.effective_end_date
3946 AND glb.legislation_code='IN';
3947
3948 l_procedure VARCHAR2(250);
3949 l_message VARCHAR2(250);
3950 BEGIN
3951 g_debug := hr_utility.debug_enabled;
3952 l_procedure := g_package ||'get_income_tax';
3953 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3954 IF (g_debug)
3955 THEN
3956 pay_in_utils.trace('**************************************************','********************');
3957 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
3958 pay_in_utils.trace('p_business_group_id',p_business_group_id);
3959 pay_in_utils.trace('p_assignment_id ',p_assignment_id );
3960 pay_in_utils.trace('p_total_income ',p_total_income );
3961 pay_in_utils.trace('p_gender ',p_gender );
3962 pay_in_utils.trace('p_age ',p_age );
3963 pay_in_utils.trace('p_pay_end_date ',p_pay_end_date );
3964 pay_in_utils.trace('p_pay_date_paid ',p_pay_date_paid );
3965 END IF;
3966
3967 l_income_tax:=0;
3968
3969 -- Start of Bug 11821281
3970 SELECT pep.per_information4 INTO l_pan
3971 FROM per_all_people_f pep,
3972 per_all_assignments_f asg,
3973 per_periods_of_service pos
3974 WHERE asg.assignment_id = p_assignment_id
3975 AND asg.person_id = pep.person_id
3976 AND pos.person_id = asg.person_id
3977 AND pos.period_of_service_id = asg.period_of_service_id
3978 AND decode(pos.actual_termination_date,'',p_pay_end_date,pos.actual_termination_date) BETWEEN asg.effective_start_date AND asg.effective_end_date
3979 AND decode(pos.actual_termination_date,'',p_pay_end_date,pos.actual_termination_date) BETWEEN pep.effective_start_date AND pep.effective_end_date ;
3980 -- End of Bug 11821281
3981
3982 /* Commented the code for 12376004*/
3983 -- Start of 11838969
3984 /* IF (p_pay_date_paid >= TO_DATE('01-04-2011','DD-MM-YYYY')) THEN
3985 IF (l_pan is null)
3986 THEN
3987 IF p_age >=80 THEN
3988 l_table_name :='India Income Tax Rates for Very Senior Citizen';
3989 ELSIF p_age >=60 THEN
3990 l_table_name :='Income Tax Rates for Senior Citizen without PAN';
3991 ELSIF p_gender = 'F' THEN
3992 l_table_name := 'Income Tax Rates for Women without PAN';
3993 ELSE
3994 l_table_name := 'Income Tax Rates without PAN';
3995 END IF;
3996 ELSE
3997 IF p_age >=80 THEN
3998 l_table_name :='India Income Tax Rates for Very Senior Citizen';
3999 ELSIF p_age >=60 THEN
4000 l_table_name :='India Income Tax Rates for Senior Citizen';
4001 ELSIF p_gender = 'F' THEN
4002 l_table_name := 'India Income Tax Rates for Women';
4003 ELSE
4004 l_table_name := 'India Income Tax Rates';
4005 END IF;
4006 END IF;
4007 ELSE -- End of 11838969
4008 IF ( (l_pan is null) AND (p_pay_date_paid >= TO_DATE('01-04-2010','DD-MM-YYYY')))
4009 THEN
4010 IF p_age >=65 THEN
4011 l_table_name :='Income Tax Rates for Senior Citizen without PAN';
4012 ELSIF p_gender = 'F' THEN
4013 l_table_name := 'Income Tax Rates for Women without PAN';
4014 ELSE
4015 l_table_name := 'Income Tax Rates without PAN';
4016 END IF;
4017 ELSE
4018 IF p_age >=65 THEN
4019 l_table_name :='India Income Tax Rates for Senior Citizen';
4020 ELSIF p_gender = 'F' THEN
4021 l_table_name := 'India Income Tax Rates for Women';
4022 ELSE
4023 l_table_name := 'India Income Tax Rates';
4024 END IF;
4025 END IF;
4026 END IF; --Added for 11838969
4027 */
4028 --End of code commented for 12376004
4029 --Start of Bug 12376004
4030 IF (p_pay_date_paid >= TO_DATE('01-04-2011','DD-MM-YYYY')) THEN
4031 IF p_age >=80 THEN
4032 l_table_name :='India Income Tax Rates for Very Senior Citizen';
4033 ELSIF p_age >=60 THEN
4034 l_table_name :='India Income Tax Rates for Senior Citizen';
4035 ELSIF p_gender = 'F' THEN
4036 l_table_name := 'India Income Tax Rates for Women';
4037 ELSE
4038 l_table_name := 'India Income Tax Rates';
4039 END IF;
4040 ELSE
4041 IF p_age >=65 THEN
4042 l_table_name :='India Income Tax Rates for Senior Citizen';
4043 ELSIF p_gender = 'F' THEN
4044 l_table_name := 'India Income Tax Rates for Women';
4045 ELSE
4046 l_table_name := 'India Income Tax Rates';
4047 END IF;
4048 END IF;
4049 --End of Bug 12376004
4050
4051 IF (g_debug)
4052 THEN
4053 pay_in_utils.trace('l_table_name',l_table_name);
4054 END IF;
4055
4056 l_tax_slab := fnd_number.canonical_to_number(pay_in_utils.get_user_table_value
4057 (p_business_group_id => p_business_group_id
4058 ,p_table_name => l_table_name
4059 ,p_column_name => 'Tax Rate'
4060 ,p_row_name => 'Tax Slabs'
4061 ,p_row_value => p_total_income
4062 ,p_effective_date => p_pay_date_paid
4063 ,p_message => p_message
4064 ));
4065
4066 l_additional_amount := fnd_number.canonical_to_number(pay_in_utils.get_user_table_value
4067 (p_business_group_id => p_business_group_id
4068 ,p_table_name => l_table_name
4069 ,p_column_name => 'Additional Amount'
4070 ,p_row_name => 'Tax Slabs'
4071 ,p_row_value => p_total_income
4072 ,p_effective_date => p_pay_date_paid
4073 ,p_message => p_message
4074 ));
4075
4076 l_reduced_amount := fnd_number.canonical_to_number(pay_in_utils.get_user_table_value
4077 (p_business_group_id => p_business_group_id
4078 ,p_table_name => l_table_name
4079 ,p_column_name => 'Reduced Amount'
4080 ,p_row_name => 'Tax Slabs'
4081 ,p_row_value => p_total_income
4082 ,p_effective_date => p_pay_date_paid
4083 ,p_message => p_message
4084 ));
4085
4086 IF (g_debug)
4087 THEN
4088 pay_in_utils.trace('l_tax_slab',l_tax_slab);
4089 pay_in_utils.trace('l_additional_amount',l_additional_amount);
4090 pay_in_utils.trace('l_reduced_amount',l_reduced_amount);
4091 END IF;
4092
4093 l_income_tax := l_tax_slab * (p_total_income - l_reduced_amount) + l_additional_amount;
4094
4095 /* start of budget 2013 change */
4096
4097 OPEN csr_global_value('IN_SECTION87A_REBATE',p_pay_date_paid);
4098 FETCH csr_global_value INTO l_rebate_amount;
4099 CLOSE csr_global_value;
4100
4101 OPEN csr_global_value('IN_SECTION87A_MIN_LIMIT',p_pay_date_paid);
4102 FETCH csr_global_value INTO l_minimum_limit;
4103 CLOSE csr_global_value;
4104
4105 OPEN csr_global_value('IN_SECTION87A_MAX_LIMIT',p_pay_date_paid);
4106 FETCH csr_global_value INTO l_maximum_limit;
4107 CLOSE csr_global_value;
4108
4109 p_sec87_rebate :=0;
4110
4111 IF(g_debug)
4112 THEN
4113 pay_in_utils.trace('l_rebate_amount',l_rebate_amount);
4114 pay_in_utils.trace('l_minimum_limit',l_minimum_limit);
4115 pay_in_utils.trace('l_maximum_limit',l_maximum_limit);
4116 pay_in_utils.trace('l_income_tax',l_income_tax);
4117 END IF;
4118
4119
4120 IF (p_total_income >=l_minimum_limit
4121 AND p_total_income <= l_maximum_limit
4122 AND p_pay_date_paid >= TO_DATE('01-04-2013','DD-MM-YYYY')
4123 AND l_pan IS NOT NULL)
4124 THEN
4125 p_sec87_rebate := least(l_income_tax,l_rebate_amount);
4126 l_income_tax := greatest(l_income_tax - l_rebate_amount,0);
4127
4128 IF (g_debug)
4129 THEN
4130 pay_in_utils.trace('Section 87 Rebate',p_sec87_rebate);
4131 pay_in_utils.trace('Income Tax Post Section 87 Rebate',l_income_tax);
4132 END IF;
4133
4134 END IF;
4135 /*end of Budget 2013 changes*/
4136
4137 p_marginal_relief := 0;
4138 p_surcharge := 0;
4139 p_education_cess := 0;
4140 p_sec_and_he_cess := 0;
4141
4142 OPEN csr_global_value('IN_SURCHARGE_APPLICABLE_AMOUNT',p_pay_date_paid);
4143 FETCH csr_global_value INTO l_surcharge_applicable_amt;
4144 CLOSE csr_global_value;
4145
4146 IF (g_debug)
4147 THEN
4148 pay_in_utils.trace('l_income_tax_new',l_income_tax);
4149 pay_in_utils.trace('l_surcharge_applicable_amt',l_surcharge_applicable_amt);
4150 END IF;
4151
4152
4153 /* Calculate Surcharge and marginal relief */
4154 IF p_total_income > l_surcharge_applicable_amt THEN
4155 p_surcharge := 0.1 * l_income_tax;
4156
4157 IF p_age >= 65 THEN
4158 l_relief_ceiling := 'IN_MARGINAL_RELIEF_SENIORS';
4159 ELSIF p_gender = 'F' THEN
4160 l_relief_ceiling := 'IN_MARGINAL_RELIEF_FEMALES';
4161 ELSE
4162 l_relief_ceiling := 'IN_MARGINAL_RELIEF';
4163 END IF;
4164
4165 OPEN csr_global_value(l_relief_ceiling,p_pay_date_paid);
4166 FETCH csr_global_value INTO l_relief_limit;
4167 CLOSE csr_global_value;
4168
4169 IF (g_debug)
4170 THEN
4171 pay_in_utils.trace('l_relief_ceiling',l_relief_ceiling);
4172 pay_in_utils.trace('l_relief_limit',l_relief_limit);
4173 pay_in_utils.trace('p_pay_end_date',p_pay_end_date);
4174 END IF;
4175
4176 IF p_total_income <= l_relief_limit THEN
4177 tax_on_mr_ceiling := l_additional_amount
4178 + (l_surcharge_applicable_amt - l_reduced_amount) * l_tax_slab;
4179
4180 p_marginal_relief := l_income_tax
4181 + p_surcharge
4182 - tax_on_mr_ceiling
4183 - (p_total_income - l_surcharge_applicable_amt);
4184 END IF;
4185
4186 END IF;
4187
4188
4189 p_tax_on_income := l_income_tax + GREATEST (p_surcharge - p_marginal_relief,0);
4190
4191 OPEN csr_global_value('IN_EDUCATION_CESS_PERCENTAGE',p_pay_date_paid);
4192 FETCH csr_global_value INTO l_cess_percent;
4193 CLOSE csr_global_value;
4194
4195 p_education_cess := l_cess_percent * p_tax_on_income ;
4196
4197 OPEN csr_global_value('IN_SEC_AND_HE_CESS_PERCENTAGE',p_pay_date_paid);
4198 IF csr_global_value%NOTFOUND THEN
4199 l_sec_and_he_cess_percent:=0;
4200 END IF ;
4201 FETCH csr_global_value INTO l_sec_and_he_cess_percent;
4202 CLOSE csr_global_value;
4203
4204 p_sec_and_he_cess := l_sec_and_he_cess_percent * p_tax_on_income ;
4205
4206 p_marginal_relief := GREATEST(p_marginal_relief,0);
4207 p_surcharge := GREATEST(p_surcharge,0);
4208 p_education_cess := GREATEST(p_education_cess,0);
4209 p_sec_and_he_cess := GREATEST(p_sec_and_he_cess,0);
4210 l_income_tax := GREATEST(l_income_tax,0);
4211
4212 IF (g_debug)
4213 THEN
4214 pay_in_utils.trace('l_cess_percent',l_cess_percent);
4215 pay_in_utils.trace('p_marginal_relief',p_marginal_relief);
4216 pay_in_utils.trace('p_surcharge',p_surcharge);
4217 pay_in_utils.trace('p_education_cess',p_education_cess);
4218 pay_in_utils.trace('p_sec_and_he_cess',p_sec_and_he_cess);
4219 pay_in_utils.trace('l_income_tax',l_income_tax);
4220 pay_in_utils.trace('p_tax_on_income',p_tax_on_income);
4221 END IF;
4222
4223 /* Added code for Bug 12376004*/
4224 /* Checking if PAN is not available where Income Tax is applicable */
4225 IF ( p_pay_date_paid >= TO_DATE('01-04-2010','DD-MM-YYYY') AND l_pan is null AND l_income_tax > 0 ) THEN
4226
4227 l_table_name := 'India Income Tax Rates without PAN';
4228 IF (g_debug) THEN
4229 pay_in_utils.trace('l_table_name',l_table_name);
4230 END IF;
4231
4232 l_tax_slab_wo_pan := fnd_number.canonical_to_number(pay_in_utils.get_user_table_value
4233 (p_business_group_id => p_business_group_id
4234 ,p_table_name => l_table_name
4235 ,p_column_name => 'Tax Rate'
4236 ,p_row_name => 'Tax Slabs'
4237 ,p_row_value => p_total_income
4238 ,p_effective_date => p_pay_date_paid
4239 ,p_message => p_message
4240 ));
4241
4242 l_additional_amount_wo_pan := fnd_number.canonical_to_number(pay_in_utils.get_user_table_value
4243 (p_business_group_id => p_business_group_id
4244 ,p_table_name => l_table_name
4245 ,p_column_name => 'Additional Amount'
4246 ,p_row_name => 'Tax Slabs'
4247 ,p_row_value => p_total_income
4248 ,p_effective_date => p_pay_date_paid
4249 ,p_message => p_message
4250 ));
4251
4252 l_reduced_amount_wo_pan := fnd_number.canonical_to_number(pay_in_utils.get_user_table_value
4253 (p_business_group_id => p_business_group_id
4254 ,p_table_name => l_table_name
4255 ,p_column_name => 'Reduced Amount'
4256 ,p_row_name => 'Tax Slabs'
4257 ,p_row_value => p_total_income
4258 ,p_effective_date => p_pay_date_paid
4259 ,p_message => p_message
4260 ));
4261
4262 l_income_tax_wo_pan := l_tax_slab_wo_pan * (p_total_income - l_reduced_amount_wo_pan) + l_additional_amount_wo_pan;
4263
4264 IF (g_debug) THEN
4265 pay_in_utils.trace('**************************************************','********************');
4266 pay_in_utils.trace('l_tax_slab_wo_pan',l_tax_slab_wo_pan);
4267 pay_in_utils.trace('l_additional_amount_wo_pan',l_additional_amount_wo_pan);
4268 pay_in_utils.trace('l_reduced_amount_wo_pan',l_reduced_amount_wo_pan);
4269 pay_in_utils.trace('l_cess_percent',l_cess_percent);
4270 pay_in_utils.trace('p_marginal_relief',p_marginal_relief);
4271 pay_in_utils.trace('p_surcharge',p_surcharge);
4272 pay_in_utils.trace('p_education_cess',p_education_cess);
4273 pay_in_utils.trace('p_sec_and_he_cess',p_sec_and_he_cess);
4274 pay_in_utils.trace('l_income_tax',l_income_tax);
4275 pay_in_utils.trace('l_income_tax_wo_pan',l_income_tax_wo_pan);
4276 pay_in_utils.trace('p_tax_on_income',p_tax_on_income);
4277 END IF;
4278
4279 IF ( l_income_tax_wo_pan > (l_income_tax + p_education_cess + p_sec_and_he_cess )) THEN
4280 l_income_tax := l_income_tax_wo_pan;
4281 p_education_cess :=0;
4282 p_sec_and_he_cess := 0;
4283 END IF;
4284
4285 IF (g_debug) THEN
4286 pay_in_utils.trace('**************************************************','********************');
4287 pay_in_utils.trace('l_tax_slab_wo_pan',l_tax_slab_wo_pan);
4288 pay_in_utils.trace('l_additional_amount_wo_pan',l_additional_amount_wo_pan);
4289 pay_in_utils.trace('l_reduced_amount_wo_pan',l_reduced_amount_wo_pan);
4290 pay_in_utils.trace('p_education_cess',p_education_cess);
4291 pay_in_utils.trace('p_sec_and_he_cess',p_sec_and_he_cess);
4292 pay_in_utils.trace('l_income_tax',l_income_tax);
4293 END IF;
4294
4295 END IF;
4296 /* End of Bug 12376004*/
4297
4298 pay_in_utils.trace('**************************************************','********************');
4299 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
4300
4301 RETURN l_income_tax;
4302 END get_income_tax;
4303
4304
4305
4306
4307
4308 FUNCTION set_context(p_context_name IN VARCHAR2
4309 ,p_context_value IN VARCHAR2
4310 )
4311 RETURN NUMBER
4312 IS
4313
4314 l_procedure VARCHAR2(250);
4315 l_message VARCHAR2(250);
4316 BEGIN
4317 g_debug := hr_utility.debug_enabled;
4318 l_procedure := g_package ||'set_context';
4319 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4320 IF (g_debug)
4321 THEN
4322 pay_in_utils.trace('**************************************************','********************');
4323 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
4324 pay_in_utils.trace('p_context_name',p_context_name);
4325 pay_in_utils.trace('p_context_value',p_context_value);
4326 END IF;
4327
4328 pay_balance_pkg.set_context('IN',p_context_name, p_context_value);
4329
4330 pay_in_utils.trace('**************************************************','********************');
4331 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
4332
4333 RETURN 0;
4334 END set_context;
4335
4336 --------------------------------------------------------------------------
4337 -- --
4338 -- Name : get_value_on_le_start --
4339 -- Type : FUNCTION --
4340 -- Access : Public --
4341 -- Description : Function to return balance value as on the --
4342 -- le start. This will be accessed while processing --
4343 -- Actual Expecnditure type of allowances and can be --
4344 -- safely used during payroll run as it fetches --
4345 -- previous runs values only --
4346 -- --
4347 -- Parameters : --
4348 -- IN : p_assignment_id NUMBER --
4349 -- p_tax_unit_id NUMBER --
4350 -- p_effective_date DATE --
4351 -- p_balance_name VARCHAR2 --
4352 -- p_dimension_name VARCHAR2 --
4353 -- p_context_name VARCHAR2 --
4354 -- p_context_value VARCHAR2 --
4355 -- --
4356 -- Change History : --
4357 --------------------------------------------------------------------------
4358 -- Rev# Date Userid Description --
4359 --------------------------------------------------------------------------
4360 -- 1.0 11-Oct-05 lnagaraj Created this function --
4361 --------------------------------------------------------------------------
4362 FUNCTION get_value_on_le_start
4363 (p_assignment_id IN NUMBER
4364 ,p_tax_unit_id IN NUMBER
4365 ,p_effective_date IN DATE
4366 ,p_balance_name IN pay_balance_types.balance_name%TYPE
4367 ,p_dimension_name IN pay_balance_dimensions.dimension_name%TYPE
4368 ,p_context_name IN ff_contexts.context_name%TYPE
4369 ,p_context_value IN VARCHAR2
4370 ,p_success OUT NOCOPY VARCHAR2
4371 )
4372 RETURN NUMBER
4373 IS
4374
4375 CURSOR c_max_asact(l_le_end_date DATE) IS
4376 SELECT MAX(paa.assignment_action_id)
4377 FROM pay_payroll_Actions ppa
4378 ,pay_assignment_actions paa
4379 WHERE paa.assignment_id =p_assignment_id
4380 AND paa.payroll_action_id = ppa.payroll_Action_id
4381 AND ppa.action_type in('R','Q')
4382 AND TRUNC(ppa.date_earned,'MM') = TRUNC(l_le_end_date,'MM')
4383 AND paa.source_action_id IS NULL;
4384
4385 CURSOR csr_cyclic_gre(p_start_date DATE,p_pre_le_end_date DATE) IS
4386 SELECT 1
4387 FROM per_assignments_f paf,
4388 hr_soft_coding_keyflex scl
4389 WHERE paf.assignment_id = p_assignment_id
4390 AND scl.segment1 = TO_CHAR(p_tax_unit_id)
4391 AND paf.SOFT_CODING_KEYFLEX_ID=scl.SOFT_CODING_KEYFLEX_ID
4392 AND paf.effective_end_date BETWEEN p_start_date AND p_pre_le_end_date;
4393
4394 l_year_start DATE;
4395 l_pre_le_end_date DATE;
4396 l_le_start_date DATE;
4397 l_exists NUMBER;
4398 p_assignment_action_id NUMBER;
4399 l_def_bal_id NUMBER;
4400 l_balance_value NUMBER :=0 ;
4401 l_proc VARCHAR2(200);
4402 l_message VARCHAR2(250);
4403
4404 BEGIN
4405 --
4406 g_debug := hr_utility.debug_enabled;
4407 l_proc := g_package||'get_value_on_le_start';
4408
4409 IF (g_debug)
4410 THEN
4411 pay_in_utils.trace('**************************************************','********************');
4412 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
4413 pay_in_utils.trace('p_assignment_id ',p_assignment_id );
4414 pay_in_utils.trace('p_tax_unit_id ',p_tax_unit_id );
4415 pay_in_utils.trace('p_effective_date',p_effective_date);
4416 pay_in_utils.trace('p_context_value',p_context_value);
4417 END IF;
4418
4419
4420 l_year_start := pay_in_tax_utils.get_financial_year_start(p_effective_date );
4421
4422 l_le_start_date := le_start_date(p_tax_unit_id
4423 ,p_assignment_id
4424 ,p_effective_date);
4425 l_pre_le_end_date := l_le_start_date - 1;
4426
4427 IF (g_debug)
4428 THEN
4429 pay_in_utils.trace('l_year_start ',l_year_start );
4430 pay_in_utils.trace('l_le_start_date ',l_le_start_date );
4431 pay_in_utils.trace('l_pre_le_end_date',l_pre_le_end_date);
4432 END IF;
4433
4434
4435 OPEN csr_cyclic_gre(l_year_start,l_pre_le_end_date);
4436 FETCH csr_cyclic_gre INTO l_exists;
4437 IF csr_cyclic_gre%NOTFOUND THEN
4438 CLOSE csr_cyclic_gre;
4439 p_success := 'N';
4440 RETURN 0;
4441 END IF;
4442 CLOSE csr_cyclic_gre;
4443
4444 l_def_bal_id := pay_in_tax_utils.get_defined_balance(p_balance_name, p_dimension_name);
4445 pay_in_utils.set_location(g_debug, ' INDIA:l_def_bal_id '||l_def_bal_id,30);
4446
4447 OPEN c_max_asact(l_pre_le_end_date);
4448 FETCH c_max_asact INTO p_assignment_action_id;
4449 CLOSE c_max_asact;
4450
4451 pay_in_utils.set_location(g_debug, ' INDIA:l_asg_action_id '||p_assignment_action_id,50);
4452
4453
4454 IF p_context_name = 'SOURCE_TEXT2' THEN
4455
4456 l_balance_value := pay_balance_pkg.get_value
4457 (p_assignment_action_id => p_assignment_action_id
4458 ,p_defined_balance_id => l_def_bal_id
4459 ,p_tax_unit_id => p_tax_unit_id
4460 ,p_jurisdiction_code => null
4461 ,p_source_id => null
4462 ,p_source_text => null
4463 ,p_tax_group => null
4464 ,p_date_earned => null
4465 ,p_get_rr_route => null
4466 ,p_get_rb_route => 'TRUE'
4467 ,p_source_text2 => p_context_value
4468 ,p_source_number => null
4469 );
4470 ELSIF p_context_name = 'TAX_UNIT_ID' THEN
4471
4472 l_balance_value := pay_balance_pkg.get_value
4473 (p_assignment_action_id => p_assignment_action_id
4474 ,p_defined_balance_id => l_def_bal_id
4475 ,p_tax_unit_id => p_tax_unit_id
4476 ,p_jurisdiction_code => null
4477 ,p_source_id => null
4478 ,p_source_text => null
4479 ,p_tax_group => null
4480 ,p_date_earned => null
4481 ,p_get_rr_route => null
4482 ,p_get_rb_route => 'TRUE'
4483 ,p_source_text2 => null
4484 ,p_source_number => null
4485 );
4486 END IF;
4487
4488 pay_in_utils.set_location(g_debug, ' INDIA:l_value '||l_balance_value,60);
4489 pay_in_utils.trace('**************************************************','********************');
4490 pay_in_utils.set_location(g_debug,'Leaving: '||l_proc,70);
4491 p_success := 'Y';
4492 RETURN l_balance_value;
4493 --
4494 END get_value_on_le_start;
4495
4496
4497 --------------------------------------------------------------------------
4498 -- Name : prev_med_reimbursement --
4499 -- Type : Function --
4500 -- Access : Public --
4501 -- Description : Function to get the Medical Reimbursement provided --
4502 -- by the Previous Employer --
4503 -- Parameters : --
4504 -- IN : --
4505 -- --
4506 --------------------------------------------------------------------------
4507 FUNCTION prev_med_reimbursement(p_assignment_id IN NUMBER
4508 ,p_date_earned IN DATE
4509 )
4510 RETURN NUMBER IS
4511
4512 CURSOR c_prev_emp_details is
4513 SELECT NVL(ppm.pem_information22,0), -- Medical Reimbursement
4514 ppm.end_date
4515 FROM per_previous_employers ppm,
4516 per_all_assignments_f paa
4517 WHERE paa.assignment_id = p_assignment_id
4518 AND paa.person_id = ppm.person_id
4519 AND p_date_earned BETWEEN paa.effective_start_date AND paa.effective_end_date;
4520
4521 l_start DATE;
4522 l_end DATE;
4523 l_end_date DATE;
4524 p_prev_med_reimburse_amt NUMBER;
4525 l_prev_med_reimburse_amt NUMBER;
4526
4527
4528 l_procedure VARCHAR2(250);
4529 l_message VARCHAR2(250);
4530 BEGIN
4531
4532 g_debug := hr_utility.debug_enabled;
4533 l_procedure := g_package ||'prev_med_reimbursement';
4534 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4535 IF (g_debug)
4536 THEN
4537 pay_in_utils.trace('**************************************************','********************');
4538 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
4539 pay_in_utils.trace('p_assignment_id',p_assignment_id);
4540 pay_in_utils.trace('p_date_earned',p_date_earned);
4541
4542 END IF;
4543
4544 p_prev_med_reimburse_amt := 0;
4545
4546 l_start := get_financial_year_start(p_date_earned);
4547 l_end := get_financial_year_end(p_date_earned);
4548
4549 OPEN c_prev_emp_details;
4550 LOOP
4551
4552 FETCH c_prev_emp_details
4553 INTO l_prev_med_reimburse_amt,l_end_date;
4554 IF c_prev_emp_details%NOTFOUND THEN
4555 CLOSE c_prev_emp_details;
4556 RETURN p_prev_med_reimburse_amt;
4557 END IF;
4558
4559 IF l_end_date BETWEEN l_start AND l_end THEN
4560 p_prev_med_reimburse_amt := p_prev_med_reimburse_amt + TO_NUMBER(l_prev_med_reimburse_amt);
4561 END IF;
4562
4563 END LOOP;
4564 CLOSE c_prev_emp_details;
4565
4566 IF (g_debug)
4567 THEN
4568 pay_in_utils.trace('p_prev_med_reimburse_amt',p_prev_med_reimburse_amt);
4569 END IF;
4570 pay_in_utils.trace('**************************************************','********************');
4571 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
4572
4573 RETURN p_prev_med_reimburse_amt;
4574
4575 END prev_med_reimbursement;
4576
4577 --------------------------------------------------------------------------
4578 -- Name : get_value_prev_period --
4579 -- Type : Function --
4580 -- Access : Public --
4581 -- Description : Function to get value in the previous run that --
4582 -- processed the tax information element --
4583 -- Parameters : --
4584 -- IN : p_assignment_id NUMBER --
4585 -- p_assignment_action_id NUMBER --
4586 -- p_payroll_action_id NUMBER --
4587 -- p_tax_unit_id NUMBER --
4588 -- p_balance_name VARCAHR2 --
4589 -- p_le_start_date DATE --
4590 -- --
4591
4592 --------------------------------------------------------------------------
4593 FUNCTION get_value_prev_period
4594 (p_assignment_id IN NUMBER
4595 ,p_assignment_action_id IN NUMBER
4596 ,p_payroll_action_id IN NUMBER
4597 ,p_tax_unit_id IN NUMBER
4598 ,p_balance_name IN pay_balance_types.balance_name%TYPE
4599 ,p_le_start_date IN DATE
4600 )
4601 RETURN NUMBER IS
4602
4603 /* In case of suspension, the run assignment action exists, but no elements
4604 are picked up during payroll run. So, we need the exists clause.Be it suspension
4605 or Otherwise, this cursor picks the most recent payroll run that populated the
4606 tax information elements in the current le in this tax year*/
4607
4608 CURSOR c_recent_run_action IS
4609 SELECT to_number(substr(max(lpad(prev_asg.action_sequence,15,'0')||prev_asg.assignment_action_id),16))
4610 FROM pay_assignment_actions prev_asg,
4611 pay_payroll_actions prev_pay,
4612 per_time_periods ptp,
4613 pay_assignment_actions cur_asg,
4614 pay_payroll_actions cur_pay
4615 WHERE prev_asg.assignment_id = p_assignment_id
4616 AND prev_asg.payroll_action_id = prev_pay.payroll_action_id
4617 AND prev_pay.action_type IN('R','Q')
4618 AND prev_asg.source_action_id IS NOT NULL
4619 AND prev_pay.effective_date < ptp.start_date
4620 AND cur_asg.assignment_action_id = p_assignment_action_id
4621 AND cur_asg.payroll_action_id = cur_pay.payroll_action_id
4622 AND prev_asg.action_sequence <= cur_asg.action_sequence
4623 AND cur_pay.effective_date between ptp.start_date and ptp.end_date
4624 AND ptp.payroll_id = cur_pay.payroll_id
4625 AND EXISTS (SELECT ''
4626 FROM pay_run_results prr,
4627 pay_element_types_f pet
4628 WHERE prr.assignment_action_id = prev_asg.assignment_action_id
4629 AND prr.element_type_id = pet.element_type_id
4630 AND pet.legislation_code ='IN'
4631 AND pet.element_name ='Form16 Income Tax Information')
4632 AND prev_pay.date_earned >= p_le_start_date;
4633
4634 l_suspend_end_date DATE;
4635 l_balance_value NUMBER;
4636 l_assignment_action_id NUMBER;
4637 l_def_bal_id NUMBER;
4638 l_procedure VARCHAR2(250);
4639
4640 BEGIN
4641
4642 g_debug := hr_utility.debug_enabled;
4643 l_procedure := g_package ||'get_value_prev_period';
4644 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4645
4646
4647 IF (g_debug)
4648 THEN
4649 pay_in_utils.trace('**************************************************','********************');
4650 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
4651 pay_in_utils.trace('p_assignment_id ',p_assignment_id );
4652 pay_in_utils.trace('p_assignment_action_id ',p_assignment_action_id );
4653 pay_in_utils.trace('p_payroll_action_id',p_payroll_action_id);
4654 pay_in_utils.trace('p_tax_unit_id',p_tax_unit_id);
4655 pay_in_utils.trace('p_balance_name',p_balance_name);
4656 pay_in_utils.trace('p_le_start_date',p_le_start_date);
4657 END IF;
4658
4659 OPEN c_recent_run_action;
4660 FETCH c_recent_run_action INTO l_assignment_action_id ;
4661 CLOSE c_recent_run_action;
4662
4663 IF l_assignment_action_id IS NULL THEN
4664 l_balance_value :=0;
4665 ELSE
4666 l_def_bal_id := pay_in_tax_utils.get_defined_balance(p_balance_name, '_ASG_LE_DE_PTD');
4667
4668 l_balance_value := pay_balance_pkg.get_value
4669 (p_assignment_action_id => l_assignment_action_id
4670 ,p_defined_balance_id => l_def_bal_id
4671 ,p_tax_unit_id => p_tax_unit_id
4672 ,p_jurisdiction_code => null
4673 ,p_source_id => null
4674 ,p_source_text => null
4675 ,p_tax_group => null
4676 ,p_date_earned => null
4677 ,p_get_rr_route => null
4678 ,p_get_rb_route => 'TRUE'
4679 ,p_source_text2 => null
4680 ,p_source_number => null
4681 );
4682 IF (g_debug)
4683 THEN
4684 pay_in_utils.trace('l_balance_value',l_balance_value);
4685 END IF;
4686
4687 END IF;
4688 pay_in_utils.trace('**************************************************','********************');
4689 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
4690
4691 RETURN l_balance_value;
4692 END get_value_prev_period;
4693
4694 --------------------------------------------------------------------------
4695 -- Name : get_regular_run_exists --
4696 -- Type : Function --
4697 -- Access : Public --
4698 -- Description : Function to find if a regular run has already been --
4699 -- run in the current period --
4700 -- Parameters : --
4701 -- IN : p_assignment_action_id NUMBER --
4702 --------------------------------------------------------------------------
4703
4704 FUNCTION get_regular_run_exists
4705 (p_assignment_action_id NUMBER)
4706 RETURN VARCHAR2
4707 IS
4708 CURSOR csr_regular_run IS
4709 SELECT 'Y' FROM
4710 per_time_periods ptp,
4711 pay_payroll_actions pact,
4712 pay_assignment_actions assact,
4713 pay_payroll_actions bact,
4714 pay_assignment_actions bal_assact,
4715 pay_run_types_f prt
4716 WHERE bal_assact.assignment_action_id = p_assignment_action_id
4717 AND bal_assact.payroll_action_id = bact.payroll_action_id
4718 AND assact.payroll_action_id = pact.payroll_action_id
4719 AND assact.action_sequence <= bal_assact.action_sequence
4720 AND assact.assignment_id = bal_assact.assignment_id + DECODE(ptp.start_date, null, 0, 0)
4721 AND bact.effective_date BETWEEN ptp.start_date AND ptp.end_date
4722 AND ptp.payroll_id = bact.payroll_id
4723 AND pact.effective_date >= ptp.start_date
4724 AND pact.effective_date <= ptp.end_date
4725 AND pact.action_type in('R','Q')
4726 AND prt.run_type_id = ASSACT.run_type_id
4727 AND prt.run_type_name ='Regular Run'
4728 AND EXISTS ( SELECT '1' FROM
4729 pay_run_results prr,
4730 pay_element_types_f pet
4731 WHERE prr.assignment_action_id = ASSACT.assignment_action_id
4732 AND prr.element_type_id = pet.element_type_id
4733 AND pet.legislation_code ='IN'
4734 AND pet.element_name ='Form16 Income Information');
4735
4736 l_exists VARCHAR2(10);
4737 l_procedure VARCHAR2(250);
4738
4739 BEGIN
4740 g_debug := hr_utility.debug_enabled;
4741 l_procedure := g_package ||'get_regular_run_exists';
4742 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4743
4744 IF (g_debug)
4745 THEN
4746 pay_in_utils.trace('**************************************************','********************');
4747 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
4748 pay_in_utils.trace('p_assignment_action_id ',p_assignment_action_id );
4749 END IF;
4750
4751
4752 l_exists := 'N';
4753
4754 OPEN csr_regular_run;
4755 FETCH csr_regular_run INTO l_exists;
4756 CLOSE csr_regular_run;
4757
4758 pay_in_utils.set_location(g_debug, ' INDIA:l_exists '||l_exists,30);
4759 pay_in_utils.trace('**************************************************','********************');
4760 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
4761
4762 RETURN l_exists;
4763
4764 END get_regular_run_exists;
4765
4766 --------------------------------------------------------------------------
4767 -- Name : bon_section_89_relief --
4768 -- Type : Function --
4769 -- Access : Public --
4770 -- Description : Function to find Section 89 relief in the Bonus --
4771 -- formula --
4772 -- Parameters : --
4773 -- IN : p_business_group_id NUMBER --
4774 -- p_total_income NUMBER --
4775 -- p_retro_earnings_py NUMBER --
4776 -- p_retro_allw_exempt_py NUMBER --
4777 -- p_emplr_class VARCHAR2 --
4778 -- p_retro_ent_allw_py NUMBER --
4779 -- p_pay_end_date DATE --
4780 -- p_tax_section_89 NUMBER --
4781 -- p_tax_Pyble_Curr_Yr NUMBER --
4782 -- p_gender VARCHAR2 --
4783 -- p_age NUMBER --
4784 --------------------------------------------------------------------------
4785
4786 FUNCTION bon_section_89_relief(p_business_group_id IN NUMBER
4787 ,p_assignment_id IN NUMBER
4788 ,p_total_income IN NUMBER
4789 ,p_retro_earnings_py IN NUMBER
4790 ,p_retro_allw_exempt_py IN NUMBER
4791 ,p_emplr_class IN VARCHAR2
4792 ,p_retro_ent_allw_py IN NUMBER
4793 ,p_pay_end_date IN DATE
4794 ,p_tax_section_89 IN NUMBER
4795 ,p_tax_Pyble_Curr_Yr IN NUMBER
4796 ,p_gender IN VARCHAR2
4797 ,p_age IN NUMBER
4798 ,p_pay_date_paid IN DATE) -- Bug 12401381
4799 RETURN NUMBER IS
4800
4801 Total_Income_wo_arrears NUMBER;
4802 Tax_Payable_cy_wo_arrears NUMBER;
4803 l_sec89_relief_bon NUMBER;
4804 tax_payable_wo_arrears NUMBER;
4805 Tax_Difference_Curr_Year NUMBER;
4806 Tax_Difference_Prev_Year NUMBER;
4807 relief_wo_arrears NUMBER;
4808 surcharge_wo_arrears NUMBER;
4809 edu_cess_wo_arrears NUMBER;
4810 sec_and_he_cess_wo_arrears NUMBER;
4811 p_messsage VARCHAR2(40);
4812 l_procedure VARCHAR2(250);
4813
4814
4815
4816 BEGIN
4817
4818 g_debug := hr_utility.debug_enabled;
4819 l_procedure := g_package ||'bon_section_89_relief';
4820 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4821
4822 IF (g_debug)
4823 THEN
4824 pay_in_utils.trace('**************************************************','********************');
4825 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
4826 pay_in_utils.trace('p_business_group_id ',p_business_group_id );
4827 pay_in_utils.trace('p_assignment_id ',p_assignment_id );
4828 pay_in_utils.trace('p_retro_earnings_py ',p_retro_earnings_py );
4829 pay_in_utils.trace('p_retro_allw_exempt_py',p_retro_allw_exempt_py);
4830 pay_in_utils.trace('p_emplr_class',p_emplr_class);
4831 pay_in_utils.trace('p_retro_ent_allw_py',p_retro_ent_allw_py);
4832 pay_in_utils.trace('p_pay_end_date',p_pay_end_date);
4833 pay_in_utils.trace('p_tax_section_89',p_tax_section_89);
4834 pay_in_utils.trace('p_tax_Pyble_Curr_Yr',p_tax_Pyble_Curr_Yr);
4835 pay_in_utils.trace('p_gender',p_gender);
4836 pay_in_utils.trace('p_age',p_age);
4837 END IF;
4838
4839 Total_Income_wo_arrears := p_total_income
4840 - p_retro_earnings_py
4841 + p_retro_allw_exempt_py;
4842
4843 IF (p_emplr_class = 'CG' OR p_emplr_class = 'SG') THEN
4844 Total_Income_wo_arrears := Total_Income_wo_arrears
4845 + p_retro_allw_exempt_py;
4846 END IF;
4847
4848 pay_in_utils.set_location(g_debug, ' INDIA:Total_Income_wo_arrears '||Total_Income_wo_arrears,30);
4849
4850 tax_payable_wo_arrears := get_income_tax( p_business_group_id
4851 ,p_assignment_id
4852 ,Total_Income_wo_arrears
4853 ,p_gender
4854 ,p_age
4855 ,p_pay_end_date
4856 ,relief_wo_arrears
4857 ,surcharge_wo_arrears
4858 ,edu_cess_wo_arrears
4859 ,p_messsage
4860 ,sec_and_he_cess_wo_arrears
4861 ,p_pay_date_paid -- Bug 12401381
4862 );
4863
4864
4865 Tax_Payable_cy_wo_arrears := tax_payable_wo_arrears
4866 - relief_wo_arrears
4867 + surcharge_wo_arrears
4868 + edu_cess_wo_arrears
4869 + sec_and_he_cess_wo_arrears;
4870
4871
4872 Tax_Difference_Curr_Year := p_tax_Pyble_Curr_Yr - Tax_Payable_cy_wo_arrears;
4873 Tax_Difference_Prev_Year := p_tax_section_89;
4874
4875 IF (g_debug)
4876 THEN
4877 pay_in_utils.trace('Tax_Difference_Curr_Year ',Tax_Difference_Curr_Year );
4878 pay_in_utils.trace('Tax_Difference_Prev_Year ',Tax_Difference_Prev_Year );
4879 END IF;
4880
4881 l_sec89_relief_bon := ROUND(GREATEST(Tax_Difference_Curr_Year - Tax_Difference_Prev_Year,0),0);
4882
4883 pay_in_utils.set_location(g_debug, ' INDIA:l_sec89_relief_bon '||l_sec89_relief_bon,40);
4884 pay_in_utils.trace('**************************************************','********************');
4885 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,50);
4886
4887
4888 RETURN l_sec89_relief_bon;
4889
4890 END bon_section_89_relief;
4891
4892 --------------------------------------------------------------------------
4893 -- Name : bon_calculate_80g_gg --
4894 -- Type : Function --
4895 -- Access : Public --
4896 -- Description : Function to find Section 80G and 80GG exemptions in --
4897 -- the Bonus formula --
4898 -- Parameters : --
4899 -- IN : p_assact_id NUMBER --
4900 -- p_assignment_id NUMBER --
4901 -- p_payroll_id NUMBER --
4902 -- p_effective_date DATE --
4903 -- p_gross_Total_Income NUMBER --
4904 -- p_tot_via_exc_80gg_g NUMBER --
4905 -- p_oth_inc NUMBER --
4906 -- p_80gg_periods NUMBER --
4907 -- p_start_period NUMBER --
4908 -- p_end_period NUMBER --
4909 -- p_flag VARCHAR2 --
4910 -- p_exemptions_80g_ue NUMBER --
4911 -- p_exemptions_80g_le NUMBER --
4912 -- p_exemptions_80g_fp NUMBER --
4913 -- --
4914 -- OUT : p_dedn_Sec_80GG NUMBER --
4915 -- p_dedn_Sec_80G NUMBER --
4916 -- p_dedn_Sec_80G_UE NUMBER --
4917 -- p_dedn_Sec_80G_LE NUMBER --
4918 -- p_Dedn_Sec_80G_FP NUMBER --
4919 -- p_adj_total_income NUMBER --
4920
4921 --------------------------------------------------------------------------
4922
4923
4924 FUNCTION bon_calculate_80g_gg(p_assact_id IN NUMBER,
4925 p_assignment_id IN NUMBER,
4926 p_payroll_id IN NUMBER,
4927 p_effective_date IN DATE,
4928 p_gross_Total_Income IN NUMBER,
4929 p_tot_via_exc_80gg_g IN NUMBER,
4930 p_oth_inc IN NUMBER,
4931 p_80gg_periods IN NUMBER,
4932 p_start_period IN NUMBER,
4933 p_end_period IN NUMBER,
4934 p_flag IN VARCHAR2,
4935 p_exemptions_80g_ue IN NUMBER,
4936 p_exemptions_80g_le IN NUMBER,
4937 p_exemptions_80g_fp IN NUMBER,
4938 p_dedn_Sec_80GG OUT NOCOPY NUMBER,
4939 p_dedn_Sec_80G OUT NOCOPY NUMBER,
4940 p_dedn_Sec_80G_UE OUT NOCOPY NUMBER,
4941 p_dedn_Sec_80G_LE OUT NOCOPY NUMBER,
4942 p_Dedn_Sec_80G_FP OUT NOCOPY NUMBER,
4943 p_adj_total_income OUT NOCOPY NUMBER )
4944 RETURN NUMBER
4945 IS
4946
4947 CURSOR csr_global_value(p_global_name IN VARCHAR2
4948 ,p_date IN DATE)
4949 IS
4950 SELECT fnd_number.canonical_to_number(glb.global_value)
4951 FROM ff_globals_f glb
4952 WHERE glb.global_name = p_global_name
4953 AND p_date BETWEEN glb.effective_start_date
4954 AND glb.effective_end_date
4955 AND glb.legislation_code='IN';
4956
4957 l_don_charity_80g NUMBER;
4958 l_tot_VI_A_ded_except_80g NUMBER;
4959 l_total_income NUMBER;
4960 elig_amt NUMBER;
4961 adj_tot_income number;
4962 l_std_exemption NUMBER;
4963 l_std_exem_percent NUMBER;
4964 l_procedure VARCHAR2(250);
4965
4966
4967 BEGIN
4968
4969 /* 80gg Starts without bonus */
4970 g_debug := hr_utility.debug_enabled;
4971 l_procedure := g_package ||'bon_calculate_80g_gg';
4972 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4973
4974
4975 IF (g_debug)
4976 THEN
4977 pay_in_utils.trace('**************************************************','********************');
4978 pay_in_utils.set_location(g_debug,'Input Paramters value is',20);
4979 pay_in_utils.trace('p_assact_id ',p_assact_id );
4980 pay_in_utils.trace('p_assignment_id ',p_assignment_id );
4981 pay_in_utils.trace('p_payroll_id ',p_payroll_id);
4982 pay_in_utils.trace('p_effective_date ',p_effective_date);
4983 pay_in_utils.trace('p_gross_Total_Income',p_gross_Total_Income);
4984 pay_in_utils.trace('p_tot_via_exc_80gg_g',p_tot_via_exc_80gg_g);
4985 pay_in_utils.trace('p_oth_inc ',p_oth_inc);
4986 pay_in_utils.trace('p_80gg_periods ',p_80gg_periods);
4987 pay_in_utils.trace('p_start_period ',p_start_period);
4988 pay_in_utils.trace('p_end_period ',p_end_period);
4989 pay_in_utils.trace('p_flag ',p_flag);
4990 pay_in_utils.trace('p_exemptions_80g_ue ',p_exemptions_80g_ue);
4991 pay_in_utils.trace('p_exemptions_80g_le ',p_exemptions_80g_le);
4992 pay_in_utils.trace('p_exemptions_80g_fp ',p_exemptions_80g_fp);
4993
4994 END IF;
4995
4996 p_dedn_Sec_80GG := 0;
4997 p_dedn_Sec_80G := 0;
4998 p_dedn_Sec_80G_UE := 0;
4999 p_dedn_Sec_80G_LE := 0;
5000 p_Dedn_Sec_80G_FP := 0;
5001 p_adj_total_income := 0;
5002
5003 p_adj_total_income := GREATEST (0,
5004 (p_gross_Total_Income
5005 - p_tot_via_exc_80gg_g
5006 - p_oth_inc));
5007
5008
5009 p_adj_total_income := p_adj_total_income /p_80gg_periods ;
5010
5011 OPEN csr_global_value('IN_RENT_PAID_PERCENT_80GG_EXEMPTION',p_effective_date);
5012 FETCH csr_global_value INTO l_std_exem_percent;
5013 CLOSE csr_global_value;
5014
5015 OPEN csr_global_value('IN_RENT_PAID_AMOUNT_80GG_EXEMPTION',p_effective_date);
5016 FETCH csr_global_value INTO l_std_exemption;
5017 CLOSE csr_global_value;
5018
5019 p_dedn_Sec_80GG := calculate_80gg_exemption(p_assact_id
5020 ,p_assignment_id
5021 ,p_payroll_id
5022 ,p_effective_date
5023 ,l_std_exemption
5024 ,p_adj_total_income
5025 ,l_std_exem_percent
5026 ,p_start_period
5027 ,p_end_period
5028 ,p_flag);
5029 pay_in_utils.set_location(g_debug, ' INDIA:p_dedn_Sec_80GG '||p_dedn_Sec_80GG,30);
5030
5031 /* Sec 80GG Ends */
5032
5033 /* Sec 80G Starts with bonus */
5034
5035 OPEN csr_global_value('IN_DONATION_TO_CHARITABLE_INSTITUTIONS_80G',p_effective_date);
5036 FETCH csr_global_value INTO l_don_charity_80g;
5037 CLOSE csr_global_value;
5038
5039
5040 l_tot_VI_A_ded_except_80g := p_tot_via_exc_80gg_g
5041 + p_dedn_Sec_80GG;
5042
5043 IF p_exemptions_80g_ue <> 0 THEN
5044 p_dedn_Sec_80G_UE := p_exemptions_80g_ue;
5045 END IF;
5046
5047 l_total_income := p_gross_Total_Income - l_tot_VI_A_ded_except_80g;
5048
5049 IF l_total_income < 0
5050 THEN
5051 l_total_income := 0;
5052 END IF;
5053
5054 IF p_exemptions_80g_le <> 0 THEN
5055
5056 adj_tot_income := GREATEST(l_total_income - p_oth_inc,0) ;
5057
5058 pay_in_utils.set_location(g_debug, ' INDIA:adj_tot_income '||adj_tot_income,40);
5059
5060 elig_amt := LEAST((p_exemptions_80g_le + p_exemptions_80g_fp),
5061 l_don_charity_80g * adj_tot_income) ;
5062
5063 pay_in_utils.set_location(g_debug, ' INDIA:elig_amt '||elig_amt,40);
5064
5065 IF elig_amt < p_exemptions_80g_fp THEN
5066 p_dedn_Sec_80G_LE := elig_amt;
5067 ELSE
5068 p_dedn_Sec_80G_LE := p_exemptions_80g_fp
5069 + 0.5 * (elig_amt - p_exemptions_80g_fp) ;
5070 END IF;
5071 ELSIF p_exemptions_80g_fp <> 0 THEN
5072
5073 adj_tot_income := GREATEST(l_total_income - p_oth_inc,0) ;
5074
5075 elig_amt := LEAST(p_exemptions_80g_fp,
5076 l_don_charity_80g * adj_tot_income);
5077 p_dedn_Sec_80G_FP := elig_amt;
5078
5079 END IF;
5080
5081 p_dedn_Sec_80G := p_dedn_Sec_80G_UE
5082 + p_dedn_Sec_80G_LE
5083 + p_dedn_Sec_80G_FP;
5084
5085 IF (g_debug)
5086 THEN
5087 pay_in_utils.trace('p_dedn_Sec_80GG ',p_dedn_Sec_80GG);
5088 pay_in_utils.trace('p_dedn_Sec_80G ',p_dedn_Sec_80G);
5089 pay_in_utils.trace('p_dedn_Sec_80G_UE ',p_dedn_Sec_80G_UE);
5090 pay_in_utils.trace('p_dedn_Sec_80G_LE ',p_dedn_Sec_80G_LE);
5091 pay_in_utils.trace('p_Dedn_Sec_80G_FP ',p_Dedn_Sec_80G_FP);
5092 pay_in_utils.trace('p_adj_total_income ',p_adj_total_income);
5093 pay_in_utils.trace('**************************************************','********************');
5094 END IF;
5095 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,50);
5096
5097 RETURN 0;
5098
5099 END bon_calculate_80g_gg;
5100
5101 END pay_in_tax_utils ;