1 PACKAGE BODY pay_au_term_REP As
2 /* $Header: pyautrm.pkb 120.1 2007/11/29 12:23:45 tbakashi noship $ */
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 */
34 --
35 -------------------------------------------------------------------------------------------------
36 --
37 -- FUNCTION ETP_prepayment_information
38 --
39 -- Returns :
40 -- 1 if function runs successfully
41 -- 0 otherwise
42 --
43 -- Purpose : Return the Values of ETP Prepayment information
44 --
45 -- In : p_assignment_id - assignment which is terminated for
46 -- which report is requiered
47 -- p_Hire_date - date Of commencement of the assignment
48 -- p_Termination_date - date Of Termination Date
49 --
50 -- Out : p_pre_01Jul1983_days - no Of Days in the Pre Jul 1983
51 -- p_post_30jun1983_days - no Of Days in the Post Jul 1983
52 -- p_pre_01jul1983_ratio -ratio Of Days in the Pre Jul 1983
53 -- p_post_30jun1983_ratio -ratio Of Days in the Post Jul 1983
54 -- P_Gross_ETP -gross ETP With out super annuation
55 -- P_Maximum_Rollover -Maximum rollover amount
56 -- p_Lump_sum_d -Lump sum D Tax free amount
57
58 --
59 -- Uses :
60 -- pay_au_terminations
61 -- hr_utility
62 --
63 ------------------------------------------------------------------------------------------------
64 function ETP_prepayment_information
65 (p_assignment_id in number
66 ,P_hire_date in Date
67 ,p_Termination_date in date
68 ,P_Assignment_action_id in Number
69 ,p_pre_01Jul1983_days out NOCOPY number
70 ,p_post_30jun1983_days out NOCOPY number
71 ,p_pre_01jul1983_ratio out NOCOPY number
72 ,p_post_30jun1983_ratio out NOCOPY number
73 ,P_Gross_ETP out NOCOPY number
74 ,P_Maximum_Rollover out NOCOPY number
75 ,p_Lump_sum_d out NOCOPY number
76 ,p_invalidity_component out NOCOPY number
77 ,p_etp_service_date out NOCOPY date /* Bug#2984390 */
78 )
79 return number
80 is
81
82 l_procedure constant varchar2(100) := 'ETP_prepayment_information';
83 Lv_Result number;
84 Lv_Element_Name Varchar2(100);
85 Lv_Input_Name Varchar2(30);
86 l_le_etp_service_date date ; /* Bug 4177679 */
87
88
89 cursor Term(p_Assignment_id In Number,P_Assignment_action_id in Number,Lv_Element_Name In varchar2,Lv_Input_Name In varchar2) is
90 select
91 To_Number( prrv.result_value)
92 from
93 pay_run_result_values prrv,
94 pay_input_values_f piv,
95 pay_element_types_f pet,
96 pay_run_results prr,
97 pay_assignment_Actions paa
98 where
99 prrv.input_value_id=piv.input_value_id
100 and piv.element_type_id=pet.element_type_id
101 and prr.element_type_id = pet.element_type_id
102 and prr.run_result_id = prrv.run_result_id
103 and prr.assignment_action_id = paa.assignment_Action_id
104 and paa.Assignment_action_id = P_Assignment_action_id
105 and paa.assignment_id = P_assignment_id
106 and pet.element_name=Lv_Element_Name
107 and piv.name = Lv_Input_Name
108 and piv.legislation_code = 'AU'
109 and pet.legislation_code = piv.legislation_code
110 and P_Termination_Date between piv.effective_start_date and piv.effective_end_date
111 and P_Termination_Date between pet.effective_start_date and Pet.effective_end_date; /* 6470561 */
112
113
114
115 /* 6470561 */
116 cursor Term2(p_Assignment_id In Number,P_Assignment_action_id in Number,Lv_Element_Name In varchar2,Lv_Input_Name In varchar2) is
117 select
118 sum(To_Number( prrv.result_value))
119 from
120 pay_run_result_values prrv,
121 pay_input_values_f piv,
122 pay_element_types_f pet,
123 pay_run_results prr,
124 pay_assignment_Actions paa
125 where
126 prrv.input_value_id=piv.input_value_id
127 and piv.element_type_id=pet.element_type_id
128 and prr.element_type_id = pet.element_type_id
129 and prr.run_result_id = prrv.run_result_id
130 and prr.assignment_action_id = paa.assignment_Action_id
131 and paa.Assignment_action_id = p_assignment_action_id
132 and paa.assignment_id = p_assignment_id
133 and pet.element_name= Lv_Element_Name
134 and piv.name = Lv_Input_Name
135 and piv.legislation_code ='AU'
136 and piv.legislation_code = pet.legislation_code
137 and p_termination_date between piv.effective_start_date and piv.effective_end_date
138 and p_termination_date between pet.effective_start_date and Pet.effective_end_date
139 and prr.run_result_id in (
140 select unique(prr3.run_result_id)
141 from pay_run_results prr2,
142 pay_input_values_f piv2,
143 pay_element_entries_f pee2,
144 pay_run_result_values prrv2,
145 pay_assignment_actions paa2,
146 pay_run_results prr3
147 where
148 prr2.element_type_id = pee2.element_type_id and
149 piv2.element_type_id = pee2.element_type_id and
150 prr2.run_result_id = prrv2.run_result_id and
151 prrv2.input_value_id = piv2.input_value_id and
152 paa2.assignment_action_id = prr2.assignment_action_id and
153 piv2.name = 'Transitional ETP' and
154 prrv2.result_value = 'Y' and
155 paa2.assignment_id = p_assignment_id and
156 prr2.source_id = prr3.source_id ) ;
157
158 begin
159
160 Begin
161
162
163 Lv_Element_Name := 'ETP Payment';
164 Lv_Input_Name := 'Pay Value';
165
166
167 open Term2(p_Assignment_id,P_Assignment_action_id,Lv_Element_Name,Lv_Input_Name);
168 Fetch Term2 into P_Gross_ETP;
169 If term2%notfound then
170 P_Gross_ETP := 0;
171 close term2;
172 end If;
173 If term2%ISOPEN then
174 close term2;
175 End if;
176 Exception
177 When No_Data_Found then
178 P_Gross_ETP := 0;
179 When Others then
180 P_Gross_ETP := 0;
181 raise;
182 End;
183
184 /* 6470561 */
185 Begin
186
187 Lv_Element_Name := 'Lump Sum D Payment';
188 Lv_Input_Name := 'Pay Value';
189
190 open Term2(p_Assignment_id,P_Assignment_action_id,Lv_Element_Name,Lv_Input_Name);
191 Fetch Term2 into p_Lump_sum_d;
192 If term2%notfound then
193 P_Lump_sum_D := 0;
194 close term2;
195 end If;
196 If term2%ISOPEN then
197 close term2;
198 End if;
199
200 Exception
201 When No_Data_Found then
202 P_Lump_sum_D := 0;
203 When Others then
204 P_Lump_sum_D := 0;
205 raise;
206 End;
207
208 /* 6470561 */
209 Begin
210 Lv_Element_Name := 'Superannuation Rollover on Termination';
211 Lv_Input_Name := 'Pay Value';
212
213
214 open Term(p_Assignment_id,P_Assignment_action_id,Lv_Element_Name,Lv_Input_Name);
215 Fetch Term into P_Maximum_Rollover;
216 If term%notfound then
217 P_Maximum_Rollover := 0;
218 close term;
219 end If;
220 If term%ISOPEN then
221 close term;
222 End if;
223 Exception
224 When No_Data_Found then
225 P_Maximum_Rollover := 0;
226 When Others then
227 P_Maximum_Rollover := 0;
228 raise;
229 End;
230
231
232 /* 6470561 */
233
234
235
236 begin
237 hr_utility.trace('-----------------------------------------');
238 hr_utility.set_location('Entering : '||l_procedure, 1);
239
240 Lv_result:= pay_au_Terminations.etp_prepost_ratios
241 (p_assignment_id
242 ,p_hire_date
243 ,p_termination_date
244 ,'N' -- Bug#2819479 Flag to check whether the function is called from Termination Form.
245 ,p_pre_01Jul1983_days
246 ,p_post_30jun1983_days
247 ,p_pre_01jul1983_ratio
248 ,p_post_30jun1983_ratio
249 ,p_etp_service_date /* Bug#2984390 */
250 ,l_le_etp_service_date /* Bug# 4177679 */
251 );
252 end;
253
254 /* 6470561 */
255 -- the element 'ETP Prepayment Information' is not getting populated for all the 4 etp elements processed,
256 -- its just getting populated for the first etp element processed
257
258 /* 6470561 */
259
260 hr_utility.set_location('Leaving : '||l_procedure, 1);
261 return(1);
262
263 Exception
264 when Others then
265 return(0);
266 end ETP_prepayment_information;
267
268 ------------------------------------- ETP Payment Information --------------------------
269
270 function ETP_payment_information
271 (p_assignment_id in number
272 ,P_hire_date in Date
273 ,p_Termination_date in date
274 ,P_Assignment_action_id in Number
275 ,P_transitional in varchar2
276 ,P_ETP_Payment out NOCOPY number
277 ,P_superAnnuation_rollover out NOCOPY number
278 ,p_Lump_sum_d out NOCOPY number
279 ,P_ETP_TAX out NOCOPY number
280 ,p_invalidity_component out NOCOPY number
281 )
282 return number
283 is
284
285 l_procedure constant varchar2(100) := 'ETP_payment_information';
286 Lv_Result number;
287 Lv_balance_type_id number;
288 Lv_balance_type_id_1 number;
289 Lv_balance_type_id_2 number;
290 Lv_Element_Name Varchar2(100);
291 Lv_Input_Name Varchar2(30);
292 l_end_date date;
293 lv_transitional varchar2(1);
294
295
296
297
298
299 cursor Term(p_Assignment_id In Number,P_Assignment_action_id in Number,Lv_Element_Name In varchar2,Lv_Input_Name In varchar2) is
300 select
301 sum(To_Number( prrv.result_value)) /* 6470561 */
302 from
303 pay_run_result_values prrv,
304 pay_input_values_f piv,
305 pay_element_types_f pet,
306 pay_run_results prr,
307 pay_assignment_Actions paa
308 where
309 prrv.input_value_id=piv.input_value_id
310 and piv.element_type_id=pet.element_type_id
311 and prr.element_type_id = pet.element_type_id
312 and prr.run_result_id = prrv.run_result_id
313 and prr.assignment_action_id = paa.assignment_Action_id
314 and paa.Assignment_action_id = P_Assignment_action_id
315 and paa.assignment_id = P_assignment_id
316 and pet.element_name=Lv_Element_Name
317 and piv.name = Lv_Input_Name
318 and piv.legislation_code ='AU'
319 and piv.legislation_code = pet.legislation_code
320 and p_termination_date between piv.effective_start_date and piv.effective_end_date
321 and p_termination_date between pet.effective_start_date and Pet.effective_end_date;
322
323 /* 6470561 */
324 cursor Term2(p_Assignment_id In Number,P_Assignment_action_id in Number,Lv_Element_Name In varchar2,Lv_Input_Name In varchar2,p_transitional in varchar2) is
325 select
326 sum(To_Number( prrv.result_value))
327 from
328 pay_run_result_values prrv,
329 pay_input_values_f piv,
330 pay_element_types_f pet,
331 pay_run_results prr,
332 pay_assignment_Actions paa
333 where
334 prrv.input_value_id=piv.input_value_id
335 and piv.element_type_id=pet.element_type_id
336 and prr.element_type_id = pet.element_type_id
337 and prr.run_result_id = prrv.run_result_id
338 and prr.assignment_action_id = paa.assignment_Action_id
339 and paa.Assignment_action_id = p_assignment_action_id
340 and paa.assignment_id = p_assignment_id
341 and pet.element_name= Lv_Element_Name
342 and piv.name = Lv_Input_Name
343 and piv.legislation_code ='AU'
344 and piv.legislation_code = pet.legislation_code
345 and p_termination_date between piv.effective_start_date and piv.effective_end_date
346 and p_termination_date between pet.effective_start_date and Pet.effective_end_date
347 and prr.run_result_id in (
348 select unique(prr3.run_result_id)
349 from pay_run_results prr2,
350 pay_input_values_f piv2,
351 pay_element_entries_f pee2,
352 pay_run_result_values prrv2,
353 pay_assignment_actions paa2,
354 pay_run_results prr3
355 where
356 prr2.element_type_id = pee2.element_type_id and
357 piv2.element_type_id = pee2.element_type_id and
358 prr2.run_result_id = prrv2.run_result_id and
359 prrv2.input_value_id = piv2.input_value_id and
360 paa2.assignment_action_id = prr2.assignment_action_id and
361 piv2.name = 'Transitional ETP' and
362 prrv2.result_value = p_transitional and
363 paa2.assignment_id = p_assignment_id and
364 prr2.source_id = prr3.source_id ) ;
365
366
367
368
369
370 cursor get_date_earned is select date_earned
371 from pay_payroll_actions ppa
372 ,pay_assignment_actions paa
373 where paa.assignment_action_id=p_assignment_action_id
374 and paa.payroll_action_id=ppa.payroll_action_id;
375 begin
376
377 begin
378 hr_utility.trace('-----------------------------------------');
379 hr_utility.set_location('Entering : '||l_procedure, 1);
380
381
382
383 Lv_Element_Name := 'Superannuation Rollover on Termination';
384 Lv_Input_Name := 'Pay Value';
385 if P_transitional = 'N' then
386
387 P_superAnnuation_rollover := 0;
388 else
389
390 open Term(p_Assignment_id,P_Assignment_action_id,Lv_Element_Name,Lv_Input_Name);
391 Fetch Term into P_superAnnuation_rollover;
392 If term%notfound then
393 P_superAnnuation_rollover := 0;
394 close term;
395 end If;
396 If term%ISOPEN then
397 close term;
398 End if;
399 End if;
400 End;
401
402
403 Begin
404
405 /* 6470561 */
406 -- 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
407
408 Lv_Element_Name := 'Lump Sum D Payment'; /* 6470561 */
409 Lv_Input_Name := 'Pay Value';
410
411
412 open Term2(p_Assignment_id,P_Assignment_action_id,Lv_Element_Name,Lv_Input_Name,p_transitional);
413 Fetch Term2 into P_Lump_sum_D;
414
415 If term2%notfound then
416
417 P_Lump_sum_D := 0;
418 close term2;
419 end If;
420
421 If term2%ISOPEN then
422 close term2;
423 End if;
424
425
426
427 Exception
428 When No_Data_Found then
429 P_Lump_sum_D := 0;
430 When Others then
431 P_Lump_sum_D := 0;
432 raise;
433 End;
434
435 /* 6470561 */
436 begin
437
438 Begin
439 /*
440 select Balance_Type_id Into Lv_Balance_Type_id
441 from Pay_Balance_Types
442 Where Balance_Name = 'Lump Sum C Deductions'
443 and Legislation_code = 'AU';
444
445
446 */
447
448 if p_transitional = 'Y'
449 then
450 select Balance_Type_id Into Lv_Balance_Type_id_1
451 from Pay_Balance_Types
452 Where Balance_Name = 'ETP Deductions Transitional Not Part of Prev Term'
453 and Legislation_code = 'AU';
454
455 select Balance_Type_id Into Lv_Balance_Type_id_2
456 from Pay_Balance_Types
457 Where Balance_Name = 'ETP Deductions Transitional Part of Prev Term'
458 and Legislation_code = 'AU';
459 else
460 select Balance_Type_id Into Lv_Balance_Type_id_1
461 from Pay_Balance_Types
462 Where Balance_Name = 'ETP Deductions Life Benefit Not Part of Prev Term'
463 and Legislation_code = 'AU';
464
465 select Balance_Type_id Into Lv_Balance_Type_id_2
466 from Pay_Balance_Types
467 Where Balance_Name = 'ETP Deductions Life Benefit Part of Prev Term'
468 and Legislation_code = 'AU';
469
470 end if;
471
472
473 Exception
474 When others then
475 Null;
476 End;
477
478
479 Begin
480 /* Added code to get the period end date for terminated employee
481 Bug#2042529 */
482
483 open get_date_earned;
484 fetch get_date_earned into l_end_date;
485 close get_date_earned;
486
487 /* P_ETP_TAX := hr_aubal.calc_asg_ptd_date
488 (P_Assignment_id
489 ,Lv_balance_type_id
490 ,l_end_date
491 ); */
492 /* Bug# 4322599
493 Modified the call for hr_aubal package by calling it in action mode rather then date mode */
494 /* 6470561 */ P_ETP_TAX := hr_aubal.calc_asg_ptd_action
495 (P_Assignment_Action_id
496 ,Lv_balance_type_id_1
497 ,l_end_date)
498 +
499 hr_aubal.calc_asg_ptd_action
500 (P_Assignment_Action_id
501 ,Lv_balance_type_id_2
502 ,l_end_date);
503 End;
504 end;
505
506
507 /* 6470561 */
508 begin
509
510 Begin
511
512 if p_transitional = 'Y'
513 then
514 select Balance_Type_id Into Lv_Balance_Type_id_1
515 from Pay_Balance_Types
516 Where Balance_Name = 'Invalidity Payments Transitional Not Part of Prev Term'
517 and Legislation_code = 'AU';
518
519 select Balance_Type_id Into Lv_Balance_Type_id_2
520 from Pay_Balance_Types
521 Where Balance_Name = 'Invalidity Payments Transitional Part of Prev Term'
522 and Legislation_code = 'AU';
523 else
524 select Balance_Type_id Into Lv_Balance_Type_id_1
525 from Pay_Balance_Types
526 Where Balance_Name = 'Invalidity Payments Life Benefit Not Part of Prev Term'
527 and Legislation_code = 'AU';
528
529 select Balance_Type_id Into Lv_Balance_Type_id_2
530 from Pay_Balance_Types
531 Where Balance_Name = 'Invalidity Payments Life Benefit Part of Prev Term'
532 and Legislation_code = 'AU';
533
534 end if;
535
536
537 Exception
538 When others then
539 Null;
540 End;
541
542
543 Begin
544
545 open get_date_earned;
546 fetch get_date_earned into l_end_date;
547 close get_date_earned;
548
549 p_invalidity_component := hr_aubal.calc_asg_ptd_action
550 (P_Assignment_Action_id
551 ,Lv_balance_type_id_1
552 ,l_end_date)
553 +
554 hr_aubal.calc_asg_ptd_action
555 (P_Assignment_Action_id
556 ,Lv_balance_type_id_2
557 ,l_end_date);
558 End;
559 end;
560
561 /* 6470561 */
562
563
564
565 Begin
566 Lv_Element_Name := 'ETP Payment';
567 Lv_Input_Name := 'Pay Value';
568
569
570 open Term2(p_Assignment_id,P_Assignment_action_id,Lv_Element_Name,Lv_Input_Name,p_transitional);
571 Fetch Term2 into p_ETP_Payment;
572
573
574 If term2%notfound then
575 p_ETP_Payment := 0;
576 close term2;
577 end If;
578
579 If term2%ISOPEN then
580 close term2;
581 End if;
582
583 exception
584 when No_DatA_Found Then
585 hr_utility.trace('is cursor not found tarun ');
586 p_ETP_Payment := 0;
587 end;
588 /* 6470561 */
589 hr_utility.set_location('Leaving : '||l_procedure, 1);
590
591 return(1);
592 Exception
593 When Others then
594 hr_utility.set_location('exception in Leaving : '||l_procedure, 1);
595 return(0);
596 end ETP_payment_information;
597
598 ------------------------------Function to get Invalidity Balances----------------------------------------
599
600 function get_invalidity_pay_bal(p_assignment_action_id in number,
601 p_assignment_id in number
602 ) return number is
603 Lv_balance_type_id_1 number;
604 Lv_balance_type_id_2 number;
605 lv_invalidity_component number;
606 l_end_date date;
607 /* 6470561 */
608
609 cursor get_date_earned is select date_earned
610 from pay_payroll_actions ppa
611 ,pay_assignment_actions paa
612 where paa.assignment_action_id=p_assignment_action_id
613 and paa.payroll_action_id=ppa.payroll_action_id;
614
615 begin
616 select Balance_Type_id Into Lv_Balance_Type_id_1
617 from Pay_Balance_Types
618 Where Balance_Name = 'Invalidity Payments Transitional Not Part of Prev Term'
619 and Legislation_code = 'AU';
620
621 select Balance_Type_id Into Lv_Balance_Type_id_2
622 from Pay_Balance_Types
623 Where Balance_Name = 'Invalidity Payments Transitional Part of Prev Term'
624 and Legislation_code = 'AU';
625
626
627 open get_date_earned;
628 fetch get_date_earned into l_end_date;
629 close get_date_earned;
630
631
632
633 lv_invalidity_component := hr_aubal.calc_asg_ptd_action
634 (P_Assignment_Action_id
635 ,Lv_balance_type_id_1
636 ,l_end_date)
637 +
638 hr_aubal.calc_asg_ptd_action
639 (P_Assignment_Action_id
640 ,Lv_balance_type_id_2
641 ,l_end_date);
642
643
644 return lv_invalidity_component;
645
646
647 Exception when others then
648 return(0);
649 raise_application_error(-20001,sqlerrm);
650
651
652 /* 6470561 */
653 end get_invalidity_pay_bal;
654
655 end pay_au_term_rep;
[Home] [Help]