1 PACKAGE BODY pay_au_term_REP As
2 /* $Header: pyautrm.pkb 120.5.12020000.2 2012/09/05 05:36:19 skshin ship $ */
3 /*
4 **
5 ** Copyright (c) 1999 Oracle Corporation
6 ** All Rights Reserved
7 **
8 ** Procedures and functions used in AU terminations reporting
9 **
10 ** Change List
11 ** ===========
12 **
13 ** Date Author Reference Description
14 ** ====================================================
15 ** 05-NOV-2000 rayyadev 115.0 Created.
16 ** 08-NOV-2000 rayyadev 115.1 changed the package name
17 ** 08-NOV-2000 rayyadev 115.2 updated the sql for payment.
18 ** 11-NOV-2000 rayyadev 115.3 added legislation code
19 ** 07-JUL-2001 apunekar 115.4 added function to calculate invalidity balance.
20 ** 03-OCT-2001 apunekar 115.5 Made Changes for Bug2021219
21 ** 10-OCT-2001 ragovind 115.6 Added parameter p_invalidity_component to the** ETP_Prepayment_information function
22 ** 05-DEC-2001 nnaresh 115.9 Updated for GSCC Standards.
23 ** 04-DEC-2002 Ragovind 115.10 Added NOCOPY for the functions etp_payment_information,etp_prepayment_information
24 ** 15-May-2003 Ragovind 115.11 Bug#2819479 - ETP Pre/Post Enhancement.
25 ** 23-JUL-2003 Nanuradh 115.12 Bug#2984390 - Added an extra parameter to the function call etp_prepost_ratios - ETP Pre/post Enhancement
26 ** 22-Apr-2005 ksingla 115.13 Bug#4177679 -Added an extra parameter to the function call etp_prepost_ratios .
27 ** 25-Apr-2005 abhargav 115.14 Bug#4322599 - For ETP Tax modified the package hr_aubal call,
28 ** now calling the package in action mode rather then date mode
29 ** 21-Nov-2007 tbakashi 115.15 Bug#6470561 - STATUTORY UPDATE: MUTIPLE ETP IMPACT ON TERMINATION REPORT
30 ** ============== Formula Fuctions ====================
31 ** Package contains Reporting Details for the Termination
32 ** report in AU localisatons.
33 ** 07-Sep-2009 pmatamsr 115.16 Bug#8769345 - Added a new function get_etp_pre_post_components
34 ** as part of Statutory changes to ETP Super rollover
35 ** 07-Sep-2009 pmatamsr 115.17 Bug#8769345 - Added code in ETP_prepayment_information and ETP_payment_information functions for
36 ** fetching the taxable and tax free Super Rollover amounts.
37 ** 28-Jan-2009 pmatamsr 115.18 Bug#9322314 - Added logic to support reporting of values in termination report
38 ** for the terminated employees processed before applying the patch 8769345.
39 ** 20-Jul-2011 skshin 115.21 Bug#12583457 - Added p_etp_pretax_SIL parameter to ETP_payment_information function
40 ** ETP_gross is deducted by ETP Pre Tax on Slary in Lieu amount
41 ** 05-Sep-2012 skshin 115.22 Bug#14358180 - Modifed ETP_payment_information function get_etp_pre_post_components function to return new element results for Excluded and Non Excluded
42 */
43 --
44 -------------------------------------------------------------------------------------------------
45 --
46 -- FUNCTION ETP_prepayment_information
47 --
48 -- Returns :
49 -- 1 if function runs successfully
50 -- 0 otherwise
51 --
52 -- Purpose : Return the Values of ETP Prepayment information
53 --
54 -- In : p_assignment_id - assignment which is terminated for
55 -- which report is requiered
56 -- p_Hire_date - date Of commencement of the assignment
57 -- p_Termination_date - date Of Termination Date
58 --
59 -- Out : p_pre_01Jul1983_days - no Of Days in the Pre Jul 1983
60 -- p_post_30jun1983_days - no Of Days in the Post Jul 1983
61 -- p_pre_01jul1983_ratio -ratio Of Days in the Pre Jul 1983
62 -- p_post_30jun1983_ratio -ratio Of Days in the Post Jul 1983
63 -- P_Gross_ETP -gross ETP With out super annuation
64 -- P_Maximum_Rollover -Maximum rollover amount
65 -- p_Lump_sum_d -Lump sum D Tax free amount
66
67 --
68 -- Uses :
69 -- pay_au_terminations
70 -- hr_utility
71 --
72 ------------------------------------------------------------------------------------------------
73 function ETP_prepayment_information
74 (p_assignment_id in number
75 ,P_hire_date in Date
76 ,p_Termination_date in date
77 ,P_Assignment_action_id in Number
78 ,p_pre_01Jul1983_days out NOCOPY number
79 ,p_post_30jun1983_days out NOCOPY number
80 ,p_pre_01jul1983_ratio out NOCOPY number
81 ,p_post_30jun1983_ratio out NOCOPY number
82 ,P_Gross_ETP out NOCOPY number
83 ,P_Maximum_Rollover out NOCOPY number
84 ,p_Lump_sum_d out NOCOPY number
85 ,p_invalidity_component out NOCOPY number
86 ,p_etp_service_date out NOCOPY date /* Bug#2984390 */
87 ,p_taxable_max_rollover out NOCOPY number /* Start 8769345 */
88 ,p_tax_free_max_rollover out NOCOPY number /* End 8769345 */
89 )
90 return number
91 is
92
93 l_procedure constant varchar2(100) := 'ETP_prepayment_information';
94 Lv_Result number;
95 Lv_Element_Name Varchar2(100);
96 Lv_Input_Name Varchar2(30);
97 l_le_etp_service_date date ; /* Bug 4177679 */
98 /* Start 8769345 */
99 l_taxable_max_rollover_1 number;
100 l_taxable_max_rollover_2 number;
101 /* End 8769345 */
102 l_etp_pretax_amount number; -- bug 12583457
103
104
105 /* Bug 9322314 - Assignment_action_id join condition is removed from cursors Term and Term2 ,
106 so that ETP payments processsed in multiple runs are fetched correctly */
107
108 cursor Term(p_Assignment_id In Number,Lv_Element_Name In varchar2,Lv_Input_Name In varchar2) is
109 select
110 nvl(To_Number( prrv.result_value),0)
111 from
112 pay_run_result_values prrv,
113 pay_input_values_f piv,
114 pay_element_types_f pet,
115 pay_run_results prr,
116 pay_assignment_Actions paa
117 where
118 prrv.input_value_id=piv.input_value_id
119 and piv.element_type_id=pet.element_type_id
120 and prr.element_type_id = pet.element_type_id
121 and prr.run_result_id = prrv.run_result_id
122 and prr.assignment_action_id = paa.assignment_Action_id
123 and paa.assignment_id = P_assignment_id
124 and pet.element_name=Lv_Element_Name
125 and piv.name = Lv_Input_Name
126 and piv.legislation_code = 'AU'
127 and pet.legislation_code = piv.legislation_code
128 and P_Termination_Date between piv.effective_start_date and piv.effective_end_date
129 and P_Termination_Date between pet.effective_start_date and Pet.effective_end_date; /* 6470561 */
130
131
132
133 /* 6470561 */
134 cursor Term2(p_Assignment_id In Number,Lv_Element_Name In varchar2,Lv_Input_Name In varchar2) is
135 select
136 sum(To_Number( prrv.result_value))
137 from
138 pay_run_result_values prrv,
139 pay_input_values_f piv,
140 pay_element_types_f pet,
141 pay_run_results prr,
142 pay_assignment_Actions paa
143 where
144 prrv.input_value_id=piv.input_value_id
145 and piv.element_type_id=pet.element_type_id
146 and prr.element_type_id = pet.element_type_id
147 and prr.run_result_id = prrv.run_result_id
148 and prr.assignment_action_id = paa.assignment_Action_id
149 and paa.assignment_id = p_assignment_id
150 and pet.element_name= Lv_Element_Name
151 and piv.name = Lv_Input_Name
152 and piv.legislation_code ='AU'
153 and piv.legislation_code = pet.legislation_code
154 and p_termination_date between piv.effective_start_date and piv.effective_end_date
155 and p_termination_date between pet.effective_start_date and Pet.effective_end_date
156 and prr.run_result_id in (
157 select unique(prr3.run_result_id)
158 from pay_run_results prr2,
159 pay_input_values_f piv2,
160 pay_element_entries_f pee2,
161 pay_run_result_values prrv2,
162 pay_assignment_actions paa2,
163 pay_run_results prr3
164 where
165 prr2.element_type_id = pee2.element_type_id and
166 piv2.element_type_id = pee2.element_type_id and
167 prr2.run_result_id = prrv2.run_result_id and
168 prrv2.input_value_id = piv2.input_value_id and
169 paa2.assignment_action_id = prr2.assignment_action_id and
170 piv2.name = 'Transitional ETP' and
171 prrv2.result_value = 'Y' and
172 paa2.assignment_id = p_assignment_id and
173 prr2.source_id = prr3.source_id ) ;
174
175 begin
176
177 Begin
178
179
180 Lv_Element_Name := 'ETP Payment';
181 Lv_Input_Name := 'Pay Value';
182
183
184 open Term2(p_Assignment_id,Lv_Element_Name,Lv_Input_Name);
185 Fetch Term2 into P_Gross_ETP;
186 If term2%notfound then
187 P_Gross_ETP := 0;
188 close term2;
189 end If;
190 If term2%ISOPEN then
191 close term2;
192 End if;
193
194 /* bug 12583457 */
195 if P_Gross_ETP <> 0 then
196 Lv_Element_Name := 'ETP Pre Tax on Salary in Lieu';
197 open Term2(p_Assignment_id,Lv_Element_Name,Lv_Input_Name);
198 Fetch Term2 into l_etp_pretax_amount;
199 IF l_etp_pretax_amount is not null THEN
200 P_Gross_ETP := P_Gross_ETP - l_etp_pretax_amount;
201 END IF;
202 close Term2;
203 end if;
204
205 Exception
206 When No_Data_Found then
207 P_Gross_ETP := 0;
208 When Others then
209 P_Gross_ETP := 0;
210 raise;
211 End;
212
213 /* 6470561 */
214 Begin
215
216 Lv_Element_Name := 'Lump Sum D Payment';
217 Lv_Input_Name := 'Pay Value';
218
219 open Term2(p_Assignment_id,Lv_Element_Name,Lv_Input_Name);
220 Fetch Term2 into p_Lump_sum_d;
221 If term2%notfound then
222 P_Lump_sum_D := 0;
223 close term2;
224 end If;
225 If term2%ISOPEN then
226 close term2;
227 End if;
228
229 Exception
230 When No_Data_Found then
231 P_Lump_sum_D := 0;
232 When Others then
233 P_Lump_sum_D := 0;
234 raise;
235 End;
236
237 /* 6470561 */
238 Begin
239 Lv_Element_Name := 'Superannuation Rollover on Termination';
240 Lv_Input_Name := 'Pay Value';
241
242
243 open Term(p_Assignment_id,Lv_Element_Name,Lv_Input_Name);
244 Fetch Term into P_Maximum_Rollover;
245 If term%notfound then
246 P_Maximum_Rollover := 0;
247 close term;
248 end If;
249 If term%ISOPEN then
250 close term;
251 End if;
252
253 /* Start 8769345 - Added code for fetching the values of taxable and tax free super rollover amounts */
254
255 Lv_Input_Name := 'Amount Part Prev ETP';
256
257 open Term(p_Assignment_id,Lv_Element_Name,Lv_Input_Name);
258 Fetch Term into l_taxable_max_rollover_1;
259
260 If term%notfound then
261 l_taxable_max_rollover_1 := 0;
262 close term;
263 end If;
264
265 If term%ISOPEN then
266 close term;
267 End if;
268
269 Lv_Input_Name := 'Amount Not Part Prev ETP';
270
271 open Term(p_Assignment_id,Lv_Element_Name,Lv_Input_Name);
272 Fetch Term into l_taxable_max_rollover_2;
273
274 If term%notfound then
275 l_taxable_max_rollover_2 := 0;
276 close term;
277 end If;
278
279 If term%ISOPEN then
280 close term;
281 End if;
282
283 p_taxable_max_rollover := l_taxable_max_rollover_1 + l_taxable_max_rollover_2;
284 p_tax_free_max_rollover := P_Maximum_Rollover - p_taxable_max_rollover ;
285
286 /* End 8769345 */
287
288 Exception
289 When No_Data_Found then
290 P_Maximum_Rollover := 0;
291 p_tax_free_max_rollover := 0;
292 p_taxable_max_rollover := 0;
293 When Others then
294 P_Maximum_Rollover := 0;
295 p_tax_free_max_rollover := 0;
296 p_taxable_max_rollover := 0;
297 raise;
298 End;
299
300
301 /* 6470561 */
302
303
304
305 begin
306 hr_utility.trace('-----------------------------------------');
307 hr_utility.set_location('Entering : '||l_procedure, 1);
308
309 Lv_result:= pay_au_Terminations.etp_prepost_ratios
310 (p_assignment_id
311 ,p_hire_date
312 ,p_termination_date
313 ,'N' -- Bug#2819479 Flag to check whether the function is called from Termination Form.
314 ,p_pre_01Jul1983_days
315 ,p_post_30jun1983_days
316 ,p_pre_01jul1983_ratio
317 ,p_post_30jun1983_ratio
318 ,p_etp_service_date /* Bug#2984390 */
319 ,l_le_etp_service_date /* Bug# 4177679 */
320 );
321 end;
322
323 /* 6470561 */
324 -- the element 'ETP Prepayment Information' is not getting populated for all the 4 etp elements processed,
325 -- its just getting populated for the first etp element processed
326
327 /* 6470561 */
328
329 hr_utility.set_location('Leaving : '||l_procedure, 1);
330 return(1);
331
332 Exception
333 when Others then
334 return(0);
335 end ETP_prepayment_information;
336
337 ------------------------------------- ETP Payment Information --------------------------
338
339 function ETP_payment_information
340 (p_assignment_id in number
341 ,P_hire_date in Date
342 ,p_Termination_date in date
343 ,P_Assignment_action_id in Number
344 ,P_transitional in varchar2
345 ,p_etp_type in varchar2
346 ,P_ETP_Payment out NOCOPY number
347 ,P_superAnnuation_rollover out NOCOPY number
348 ,p_Lump_sum_d out NOCOPY number
349 ,P_ETP_TAX out NOCOPY number
350 ,p_invalidity_component out NOCOPY number
351 ,p_taxable_rollover out NOCOPY number /* Start 8769345 */
352 ,p_tax_free_rollover out NOCOPY number /* End 8769345 */
353 ,p_etp_pretax_SIL out NOCOPY number /* bug 12583457 */
354 )
355 return number
356 is
357
358 l_procedure constant varchar2(100) := 'ETP_payment_information';
359 Lv_Result number;
360 Lv_balance_type_id number;
361 Lv_balance_type_id_1 number;
362 Lv_balance_type_id_2 number;
363 Lv_Element_Name Varchar2(100);
364 Lv_Input_Name Varchar2(30);
365 l_end_date date;
366 lv_transitional varchar2(1);
367 /* Start 8769345 */
368 l_taxable_rollover_1 number;
369 l_taxable_rollover_2 number;
370 /* End 8769345 */
371 --l_etp_pretax_amount number; -- bug 12583457
372
373 l_etp_tax_1 number;
374 l_etp_tax_2 number;
375 l_etp_pretax_SIL number;
376
377 /* Bug 9322314 - Assignment_action_id join condition is removed from cursors Term and Term2 ,
378 so that ETP payments processsed in multiple runs are fetched correctly */
379
380 cursor Term(p_Assignment_id In Number,Lv_Element_Name In varchar2,Lv_Input_Name In varchar2) is
381 select
382 nvl(sum(To_Number( prrv.result_value)),0) /* 6470561 */
383 from
384 pay_run_result_values prrv,
385 pay_input_values_f piv,
386 pay_element_types_f pet,
387 pay_run_results prr,
388 pay_assignment_Actions paa
389 where
390 prrv.input_value_id=piv.input_value_id
391 and piv.element_type_id=pet.element_type_id
392 and prr.element_type_id = pet.element_type_id
393 and prr.run_result_id = prrv.run_result_id
394 and prr.assignment_action_id = paa.assignment_Action_id
395 and paa.assignment_id = P_assignment_id
396 and pet.element_name=Lv_Element_Name
397 and piv.name = Lv_Input_Name
398 and piv.legislation_code ='AU'
399 and piv.legislation_code = pet.legislation_code
400 and p_termination_date between piv.effective_start_date and piv.effective_end_date
401 and p_termination_date between pet.effective_start_date and Pet.effective_end_date;
402
403 /* 6470561 */
404 cursor Term2(p_Assignment_id In Number,Lv_Element_Name In varchar2,Lv_Input_Name In varchar2,p_transitional in varchar2) is
405 select
406 sum(To_Number( prrv.result_value))
407 from
408 pay_run_result_values prrv,
409 pay_input_values_f piv,
410 pay_element_types_f pet,
411 pay_run_results prr,
412 pay_assignment_Actions paa
413 where
414 prrv.input_value_id=piv.input_value_id
415 and piv.element_type_id=pet.element_type_id
416 and prr.element_type_id = pet.element_type_id
417 and prr.run_result_id = prrv.run_result_id
418 and prr.assignment_action_id = paa.assignment_Action_id
419 and paa.assignment_id = p_assignment_id
420 and pet.element_name= Lv_Element_Name
421 and piv.name = Lv_Input_Name
422 and piv.legislation_code ='AU'
423 and piv.legislation_code = pet.legislation_code
424 and p_termination_date between piv.effective_start_date and piv.effective_end_date
425 and p_termination_date between pet.effective_start_date and Pet.effective_end_date
426 and prr.run_result_id in (
427 select unique(prr3.run_result_id)
428 from pay_run_results prr2,
429 pay_input_values_f piv2,
430 pay_element_entries_f pee2,
431 pay_run_result_values prrv2,
432 pay_assignment_actions paa2,
433 pay_run_results prr3
434 where
435 prr2.element_type_id = pee2.element_type_id and
436 piv2.element_type_id = pee2.element_type_id and
437 prr2.run_result_id = prrv2.run_result_id and
438 prrv2.input_value_id = piv2.input_value_id and
439 paa2.assignment_action_id = prr2.assignment_action_id and
440 piv2.name = 'Transitional ETP' and
441 prrv2.result_value = p_transitional and
442 paa2.assignment_id = p_assignment_id and
443 prr2.source_id = prr3.source_id ) ;
444
445 cursor Term3(p_Assignment_id In Number,Lv_Element_Name In varchar2,Lv_Input_Name In varchar2) is
446 select
447 nvl(To_Number( prrv.result_value),0) result_value, nvl(pee.entry_information4,'N') entry_information
448 from
449 pay_run_result_values prrv,
450 pay_input_values_f piv,
451 pay_element_types_f pet,
452 pay_run_results prr,
453 pay_assignment_actions paa,
454 pay_element_entries_f pee,
455 pay_element_types_f pet2
456 where
457 prrv.input_value_id=piv.input_value_id
458 and piv.element_type_id=pet.element_type_id
459 and prr.element_type_id = pet.element_type_id
460 and prr.run_result_id = prrv.run_result_id
461 and prr.assignment_action_id = paa.assignment_Action_id
462 and paa.assignment_id = p_assignment_id
463 and pet.element_name=Lv_Element_Name
464 and piv.name = Lv_Input_Name
465 and piv.legislation_code ='AU'
466 and piv.legislation_code = pet.legislation_code
467 and p_termination_date between piv.effective_start_date and piv.effective_end_date
468 and p_termination_date between pet.effective_start_date and Pet.effective_end_date
469 and prr.source_id = pee.element_entry_id
470 and pee.element_type_id = pet2.element_type_id
471 and pet2.element_name = 'ETP on Termination'
472 --and p_termination_date between pee.effective_start_date and pee.effective_end_date
473 and p_termination_date between pet2.effective_start_date and pet2.effective_end_date;
474
475
476
477 cursor get_date_earned is select date_earned
478 from pay_payroll_actions ppa
479 ,pay_assignment_actions paa
480 where paa.assignment_action_id=p_assignment_action_id
481 and paa.payroll_action_id=ppa.payroll_action_id;
482 begin
483
484 begin
485 hr_utility.trace('-----------------------------------------');
486 hr_utility.set_location('Entering : '||l_procedure, 1);
487
488
489
490 Lv_Element_Name := 'Superannuation Rollover on Termination';
491 Lv_Input_Name := 'Pay Value';
492 if P_transitional = 'N' then
493
494 P_superAnnuation_rollover := 0;
495 else
496
497 open Term(p_Assignment_id,Lv_Element_Name,Lv_Input_Name);
498 Fetch Term into P_superAnnuation_rollover;
499 If term%notfound then
500 P_superAnnuation_rollover := 0;
501 close term;
502 end If;
503 If term%ISOPEN then
504 close term;
505 End if;
506
507 /* Start 8769345 - Added code for fetching the taxable and tax free Super rollover amounts */
508
509 Lv_Input_Name := 'Amount Part Prev ETP';
510
511 open Term(p_Assignment_id,Lv_Element_Name,Lv_Input_Name);
512 Fetch Term into l_taxable_rollover_1;
513 If term%notfound then
514 l_taxable_rollover_1 := 0;
515 close term;
516 end If;
517 If term%ISOPEN then
518 close term;
519 End if;
520
521 Lv_Input_Name := 'Amount Not Part Prev ETP';
522
523 open Term(p_Assignment_id,Lv_Element_Name,Lv_Input_Name);
524 Fetch Term into l_taxable_rollover_2;
525 If term%notfound then
526 l_taxable_rollover_2 := 0;
527 close term;
528 end If;
529 If term%ISOPEN then
530 close term;
531 End if;
532
533 p_taxable_rollover := l_taxable_rollover_1 + l_taxable_rollover_2;
534 p_tax_free_rollover := P_superAnnuation_rollover - p_taxable_rollover;
535
536 /*End 8769345*/
537 End if;
538 End;
539
540
541 Begin
542
543 if p_etp_type = 'I' then
544 P_Lump_sum_D := 0;
545 else
546 /* 6470561 */
547 -- there if no individual balance for the two ETP types so we would have to fetch he value from run results for lump sum D
548
549 Lv_Element_Name := 'Lump Sum D Payment'; /* 6470561 */
550 Lv_Input_Name := 'Pay Value';
551
552
553 open Term2(p_Assignment_id,Lv_Element_Name,Lv_Input_Name,p_transitional);
554 Fetch Term2 into P_Lump_sum_D;
555
556 P_Lump_sum_D := nvl(P_Lump_sum_D,0);
557 If term2%notfound then
558 P_Lump_sum_D := 0;
559 close term2;
560 end If;
561
562 If term2%ISOPEN then
563 close term2;
564 End if;
565
566 end if;
567
568 Exception
569 When No_Data_Found then
570 P_Lump_sum_D := 0;
571 When Others then
572 P_Lump_sum_D := 0;
573 raise;
574 End;
575
576 /* bug 12583457 */
577 Begin
578
579 Lv_Element_Name := 'ETP Pre Tax on Salary in Lieu';
580 Lv_Input_Name := 'Pay Value';
581
582
583 l_etp_pretax_SIL := 0;
584 FOR rec in Term3(p_Assignment_id,Lv_Element_Name,Lv_Input_Name) LOOP
585
586 IF p_etp_type = 'E' THEN
587 if rec.entry_information = 'N' then
588 l_etp_pretax_SIL := l_etp_pretax_SIL + rec.result_value;
589 end if;
590 ELSE
591 if rec.entry_information = 'Y' then
592 l_etp_pretax_SIL := l_etp_pretax_SIL + rec.result_value;
593 end if;
594 END IF;
595
596 END LOOP;
597
598 p_etp_pretax_SIL := l_etp_pretax_SIL;
599
600
601 End;
602
603 /* 6470561, 14358180 */
604 begin
605
606 -- Begin
607 /*
608 select Balance_Type_id Into Lv_Balance_Type_id
609 from Pay_Balance_Types
610 Where Balance_Name = 'Lump Sum C Deductions'
611 and Legislation_code = 'AU';
612
613
614 */
615
616 if p_etp_type = 'E'
617 then
618
619 Lv_Element_Name := 'ETP Deduction Excluded';
620 Lv_Input_Name := 'Pay Value';
621
622
623 open Term2(p_Assignment_id,Lv_Element_Name,Lv_Input_Name,p_transitional);
624 Fetch Term2 into l_etp_tax_1;
625
626 l_etp_tax_1 := nvl(l_etp_tax_1,0);
627 If term2%notfound then
628 l_etp_tax_1 := 0;
629 close term2;
630 end If;
631
632 If term2%ISOPEN then
633 close term2;
634 End if;
635
636 Lv_Element_Name := 'ETP Deduction Excluded Part of Prev';
637 Lv_Input_Name := 'Pay Value';
638
639
640 open Term2(p_Assignment_id,Lv_Element_Name,Lv_Input_Name,p_transitional);
641 Fetch Term2 into l_etp_tax_2;
642
643 l_etp_tax_2 := nvl(l_etp_tax_2,0);
644 If term2%notfound then
645 l_etp_tax_2 := 0;
646 close term2;
647 end If;
648
649 If term2%ISOPEN then
650 close term2;
651 End if;
652
653 else
654
655 Lv_Element_Name := 'ETP Deduction Non Excluded';
656 Lv_Input_Name := 'Pay Value';
657
658
659 open Term2(p_Assignment_id,Lv_Element_Name,Lv_Input_Name,p_transitional);
660 Fetch Term2 into l_etp_tax_1;
661
662 l_etp_tax_1 := nvl(l_etp_tax_1,0);
663 If term2%notfound then
664 l_etp_tax_1 := 0;
665 close term2;
666 end If;
667
668 If term2%ISOPEN then
669 close term2;
670 End if;
671
672 Lv_Element_Name := 'ETP Deduction Non Excluded Part of Prev';
673 Lv_Input_Name := 'Pay Value';
674
675
676 open Term2(p_Assignment_id,Lv_Element_Name,Lv_Input_Name,p_transitional);
677 Fetch Term2 into l_etp_tax_2;
678
679 l_etp_tax_2 := nvl(l_etp_tax_2,0);
680 If term2%notfound then
681 l_etp_tax_2 := 0;
682 close term2;
683 end If;
684
685 If term2%ISOPEN then
686 close term2;
687 End if;
688
689 end if;
690
691 P_ETP_TAX := l_etp_tax_1 + l_etp_tax_2;
692
693 Exception
694 When others then
695 Null;
696 --End;
697
698 end;
699
700
701
702 /* 6470561, 14358180*/
703 begin
704
705 -- Begin
706
707 if p_etp_type = 'E'
708 then
709
710 Lv_Element_Name := 'ETP Invalidity Component';
711 Lv_Input_Name := 'Pay Value';
712
713
714 open Term2(p_Assignment_id,Lv_Element_Name,Lv_Input_Name,p_transitional);
715 Fetch Term2 into p_invalidity_component;
716
717 p_invalidity_component := nvl(p_invalidity_component,0);
718 If term2%notfound then
719 p_invalidity_component := 0;
720 close term2;
721 end If;
722
723 If term2%ISOPEN then
724 close term2;
725 End if;
726
727 end if;
728
729
730 Exception
731 When others then
732 Null;
733 --End;
734
735
736 end;
737
738
739 /* 6470561, 14358180 */
740 Begin
741
742 if p_etp_type = 'I' then
743
744 Lv_Element_Name := 'ETP Payments Non Excluded';
745 Lv_Input_Name := 'Pay Value';
746
747
748 open Term2(p_Assignment_id,Lv_Element_Name,Lv_Input_Name,p_transitional);
749 Fetch Term2 into p_ETP_Payment;
750
751 p_ETP_Payment := nvl(p_ETP_Payment,0);
752 If term2%notfound then
753 p_ETP_Payment := 0;
754 close term2;
755 end If;
756
757 If term2%ISOPEN then
758 close term2;
759 End if;
760
761 else
762 Lv_Element_Name := 'ETP Payments Excluded';
763 Lv_Input_Name := 'Pay Value';
764
765
766 open Term2(p_Assignment_id,Lv_Element_Name,Lv_Input_Name,p_transitional);
767 Fetch Term2 into p_ETP_Payment;
768
769 p_ETP_Payment := nvl(p_ETP_Payment,0);
770 If term2%notfound then
771 p_ETP_Payment := 0;
772 close term2;
773 end If;
774
775 If term2%ISOPEN then
776 close term2;
777 End if;
778
779 end if;
780
781 exception
782 when No_DatA_Found Then
783 hr_utility.trace('is cursor not found tarun ');
784 p_ETP_Payment := 0;
785 end;
786 /* 6470561 */
787 hr_utility.set_location('Leaving : '||l_procedure, 1);
788
789 return(1);
790 Exception
791 When Others then
792 hr_utility.set_location('exception in Leaving : '||l_procedure, 1);
793 return(0);
794 end ETP_payment_information;
795
796 ------------------------------Function to get Invalidity Balances----------------------------------------
797
798 function get_invalidity_pay_bal(p_assignment_action_id in number,
799 p_assignment_id in number
800 ) return number is
801 Lv_balance_type_id_1 number;
802 Lv_balance_type_id_2 number;
803 lv_invalidity_component number;
804 l_end_date date;
805 /* 6470561 */
806
807 cursor get_date_earned is select date_earned
808 from pay_payroll_actions ppa
809 ,pay_assignment_actions paa
810 where paa.assignment_action_id=p_assignment_action_id
811 and paa.payroll_action_id=ppa.payroll_action_id;
812
813 begin
814 select Balance_Type_id Into Lv_Balance_Type_id_1
815 from Pay_Balance_Types
816 Where Balance_Name = 'Invalidity Payments Transitional Not Part of Prev Term'
817 and Legislation_code = 'AU';
818
819 select Balance_Type_id Into Lv_Balance_Type_id_2
820 from Pay_Balance_Types
821 Where Balance_Name = 'Invalidity Payments Transitional Part of Prev Term'
822 and Legislation_code = 'AU';
823
824
825 open get_date_earned;
826 fetch get_date_earned into l_end_date;
827 close get_date_earned;
828
829
830
831 lv_invalidity_component := hr_aubal.calc_asg_ptd_action
832 (P_Assignment_Action_id
833 ,Lv_balance_type_id_1
834 ,l_end_date)
835 +
836 hr_aubal.calc_asg_ptd_action
837 (P_Assignment_Action_id
838 ,Lv_balance_type_id_2
839 ,l_end_date);
840
841
842 return lv_invalidity_component;
843
844
845 Exception when others then
846 return(0);
847 raise_application_error(-20001,sqlerrm);
848
849
850 /* 6470561 */
851 end get_invalidity_pay_bal;
852
853 /* Start 8769345 - A new function is added in order to calculate ETP taxable and tax-free components
854 after super rollover for transitional ETP and return the values to termination report. */
855
856 /* Bug 9322314 - Added two input parameters to the function for passing the pre and post 83 ratios for
857 computing the taxable and tax free ETP components */
858
859 /* Bug 14358180 - Changed run result element name for Excluded etp type */
860
861 function get_etp_pre_post_components(p_assignment_action_id in number,
862 p_assignment_id in number,
863 p_pre_jul83_ratio in number,
864 p_post_jun83_ratio in number,
865 p_etp_tax_free_amt out nocopy number,
866 p_etp_taxable_amt out nocopy number
867 ) return number
868 is
869 Lv_balance_type_id_1 number;
870 Lv_balance_type_id_2 number;
871 Lv_balance_type_id_3 number;
872
873 /* Start 9322314 - Added variables to store the etp payments */
874 l_etp_excluded_amt number;
875 l_etp_excluded_tfree_amt number;
876 l_etp_excluded_taxable_amt number;
877
878 l_excluded_tfree_amt number;
879 l_excluded_taxable_amt number;
880
881 /* End 9322314 */
882
883
884 l_end_date date;
885
886 cursor get_date_earned is
887 select date_earned
888 from pay_payroll_actions ppa
889 ,pay_assignment_actions paa
890 where paa.assignment_action_id = p_assignment_action_id
891 and paa.payroll_action_id = ppa.payroll_action_id;
892
893
894 begin
895
896 select Balance_Type_id Into Lv_Balance_Type_id_1
897 from Pay_Balance_Types
898 Where Balance_Name = 'ETP Tax Free Payments Excluded'
899 and Legislation_code = 'AU';
900
901 select Balance_Type_id Into Lv_Balance_Type_id_2
902 from Pay_Balance_Types
903 Where Balance_Name = 'ETP Taxable Payments Excluded'
904 and Legislation_code = 'AU';
905
906 /* Start 9322314 - Modified the logic in the function for calculating the taxable and tax free ETP components
907 for terminated employees processed before and after applying the patch 8769345, such that
908 the all values are reported correctly in the termination report */
909
910 select Balance_Type_id Into Lv_Balance_Type_id_3
911 from Pay_Balance_Types
912 Where Balance_Name = 'ETP Payments Excluded'
913 and Legislation_code = 'AU';
914
915
916 open get_date_earned;
917 fetch get_date_earned into l_end_date;
918 close get_date_earned;
919
920
921 l_etp_excluded_tfree_amt := nvl(hr_aubal.calc_asg_ptd_action
922 (P_Assignment_Action_id
923 ,Lv_balance_type_id_1
924 ,l_end_date),0);
925
926 l_etp_excluded_taxable_amt := nvl(hr_aubal.calc_asg_ptd_action
927 (P_Assignment_Action_id
928 ,Lv_balance_type_id_2
929 ,l_end_date),0);
930
931 l_etp_excluded_amt := nvl(hr_aubal.calc_asg_ptd_action
932 (P_Assignment_Action_id
933 ,Lv_balance_type_id_3
934 ,l_end_date),0);
935
936
937 if (l_etp_excluded_amt - (l_etp_excluded_tfree_amt + l_etp_excluded_taxable_amt ) > 0 ) then
938
939 l_etp_excluded_tfree_amt := (l_etp_excluded_amt - (l_etp_excluded_tfree_amt + l_etp_excluded_taxable_amt))* p_pre_jul83_ratio +
940 l_etp_excluded_tfree_amt;
941
942 l_etp_excluded_taxable_amt := (l_etp_excluded_amt - (l_etp_excluded_tfree_amt + l_etp_excluded_taxable_amt))* p_post_jun83_ratio +
943 l_etp_excluded_taxable_amt ;
944
945 elsif (l_etp_excluded_amt - (l_etp_excluded_tfree_amt + l_etp_excluded_taxable_amt ) = 0 ) then
946
947 l_excluded_tfree_amt := l_etp_excluded_tfree_amt ;
948 l_excluded_taxable_amt := l_etp_excluded_taxable_amt;
949
950 end if;
951
952 p_etp_tax_free_amt := l_excluded_tfree_amt ;
953 p_etp_taxable_amt := l_excluded_taxable_amt;
954
955 /* End 9322314 */
956 return (1);
957
958 Exception when others then
959 return(0);
960 raise_application_error(-20001,sqlerrm);
961
962 end get_etp_pre_post_components;
963
964 /* End 8769345 */
965
966 end pay_au_term_rep;
[Home] [Help]