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