1 PACKAGE BODY pay_au_terminations AS
2 /* $Header: pyauterm.pkb 120.30.12020000.10 2013/02/21 06:31:45 skshin ship $
3 **
4 ** Copyright (c) 1999 Oracle Corporation
5 ** All Rights Reserved
6 **
7 ** Procedures and functions used in AU terminations version 2
8 **
9 ** Change List
10 ** ===========
11 **
12 ** Date Author Reference Description
13 ** =========================================================
14 ** 14-AUG-2000 sclarke 115.0 Created for AU
15 ** 01-SEP-2000 sclarke 115.0 Terminations tax calculation
16 ** 27-SEP-2000 sclarke 115.1 Added LSL function
17 ** 15-NOV-2000 sclarke 115.2 Bug 1500587 Post 30 June ratio fixed
18 ** 16-NOV-2000 sclarke 115.3 Bug 1499206
19 ** 22-NOV-2000 sclarke 115.4 Bug 1510774
20 ** 04-DEC-2000 sclarke 115.5 Bug 1519569 rounding of deductions on marginal tax
21 ** 15-DEC-2000 sclarke 115.6 Bug 1544503
22 ** 20-DEC-2000 rayyadev 115.7 bug no 1547908
23 ** 11-JAN-2001 rayyadev 115.8 bug no 1553213
24 ** 19-JUL-2001 apunekar 115.9 Adjustments taken care of for accruals.Bug no. 1855872
25 ** 12-SEP-2001 shoskatt 115.11 Leave Initialise Values used for calculating the Accruals
26 ** Bug #1942971
27 ** Package containing addition processing required by
28 ** formula in AU localisatons.
29 ** 28-NOV-2001 nnaresh 115.12 Updated for GSCC Standards
30 ** 04-Dec-2002 Ragovind 115.13 Added NOCOPY to thr functions check_rollover, etp_prepost_ratios
31 ** ,get_long_service_leave, term_lsl_eligibility_years
32 ** 07-May-2003 Ragovind 115.15 Bug#2819479 - ETP Pre/Post Enhancement.
33 ** 18-May-2003 Ragovind 115.16 Added Reference Bug# for Bug#2819479
34 ** 23-Jul-2003 Nanuradh 115.17 Bug#2984390 - Added an extra parameter p_etp_service_date to the function etp_prepost_ratios
35 ** ETP Pre/post Enhancement
36 ** 26-Jul-2003 Nanuradh 115.18 Bug#2984390 - Modified the function etp_prepost_ratios.
37 ** Pre/post ratios assgined to zero when pre/post 1983 days are zero.
38 ** 23-Dec-2003 punmehta 115.19 Bug#3306112 - Modified the cursor to improve performance and added conditional debug.
39 ** 10-May-2003 Ragovind 115.20 Bug#3263690 - NGE calculation Enhancement.
40 ** 25-Jun-2004 srrajago 115.21 Bug#3603495 - Modified the cursor 'recurring_entries' in the function 'processed' - Performance fix.
41 ** 09-AUG-2004 abhkumar 115.22 Bug#2610141 - Legal Employer enhancement changes.
42 ** 12-AUG-2004 abhkumar 115.23 Bug#2610141 - Modfied cursor c_get_prev_year_max_asg_act_id to consider for action_type='V'
43 ** 08-SEP-2004 abhkumar 115.25 Bug#2610141 - Added a parameter to function calculate_term_asg_nge to support the
44 ** versioning of the tax formula.
45 ** 19-Apr-2004 ksingla 115.26 Bug#4177679 - Added an extra parameter p_le_etp_service_date to the function etp_prepost_ratios.
46 ** 03-AUG-2005 abhkumar 115.27 Bug#4538463 - Modified the function calculate_term_asg_nge to have a logic similar as
47 ** calculate_asg_prev_value
48 ** 01-SEP-2005 abhkumar 115.28 Bug#4474896 - Average Earnings Calculation enhancement
49 ** 02-Apr-2006 abhargav 115.29 Bug#5107059 - Added new function get_total_accrual_hours().
50 ** 17-Apr-2006 abhargav 115.30 Bug#5107059 - Modified to remove gscc error.
51 ** 27-Jun-2006 hnainani 115.31 Bug# 5056831 - Added Function Override_eligibility
52 ** 11-Jul-2006 priupadh 115.32 Bug# 5377591 - Changed cursor c_check_payroll_run to Function CALCULATE_TERM_ASG_NGE
53 ** 17-Jul-2006 avenkatk 115.33 Bug# 5388657 - Modified Dates passed to c_check_payroll_run in Function CALCULATE_TERM_ASG_NGE
54 **
55 ** 21-Sep-2006 hnainani 115.24 Bug# 5056831 - Removed extra param to Override_eligibility based
56 ** on review comments
57 ** 09-May-2007 priupadh 115.36 Bug# 5956223 Added new function calculate_etp_tax,get_trans_prev_etp
58 ** 16-May-2007 priupadh 115.37 Bug# 5956223 Removed function get_trans_prev_etp,added function get_fin_year_end
59 ** 31-May-2007 priupadh 115.38 Bug# 6071863 Added function get_prev_age Modified cursor csr_get_etp_tax
60 ** 31-May-2007 priupadh 115.39 Bug# 6071863 Corrected version numbers in change history .
61 ** 04-Jun-2007 priupadh 115.40 Bug# 6071863 Modified query csr_get_prev_age in function get_prev_age .
62 ** 23-Aug-2007 priupadh 115.41 Bug# 6192381 Added function au_check_trans
63 ** 17-Sep-2007 avenkatk 115.42 Bug# 6430072 Function calculate_etp_tax - Added condition to convert User Tables value using
64 ** fnd_number.canonical_to_number
65 ** 26-May-2009 dduvvuri 115.43 Bug# 8482224 Cursor csr_get_accrual_plan_id modified in Function get_accrual_plan_by_category
66 ** 20-Jul-2009 skshin 115.45 Bug# 8647962 Added Index(PURF) and ORDERED hint to get correct execution path
67 ** 30-Jul-2009 skshin 115.46 Bug# 8725341 Added Earnings_Leave_Loading balance to be retrieved effective from 01-JUL-2009 in calculate_term_asg_nge function
68 ** 07-Sep-2009 pmatamsr 115.47 Bug# 8769345 Added new input parameter to check_rollover function.
69 ** 12-May-2010 skshin 115.48 Bug# 9507714 Modified to pass l_start_date to get absence for post AUG1993 and post AUG1978 in get_long_service_leave function due to primary assignment change
70 ** 06-Oct-2010 skshin 115.49 Bug# 9775171 Added calculate_etp_lumpsum_d_2010 and calculate_etp_tax_2010 functions
71 ** 16-Nov-2010 avenkatk 115.50 Bug# 9950136 Added changes for foreign workers accruals - added function get_foreign_accrual
72 ** 24-Nov-2010 skshin 115.54 Bug# 10212532 Added function get_days_ppl_suspended to calculate suspended days of PPL for ETP calculation
73 ** 09-Dec-2010 avenkatk 115.55 Bug# 10384820 Modified function get_days_ppl_suspended for retropay case
74 ** 05-MAY-2011 dduvvuri 115.56 Bug# 12400122 Modified the cursor csr_get_etp_entry for performance improvement.
75 ** 25-MAY-2011 jmarupil 115.57 Bug# 12586038 Modified function get_days_ppl_suspended to calculate PPL suspended days for recurring element also
76 ** 25-MAY-2011 skshin 115.59 Bug# 12583457 Added function au_count_etps_sil to count ETP on Termination with Salary in Lieu of Notice input value entered
77 ** 05-Sep-2012 skshin 115.60 Bug# 14358180 Added new functions of calculate_etp_lumpsum_d_2012 and calculate_etp_tax_2012
78 ** 14-Sep-2012 skshin 115.61 Bug# 14469003 Added STAR_TRANSFORMATION hint to csr_get_days_ppl cursor for better performance
79 ** 04-Oct-2012 skshin 115.63 Bug# 14703008 Modified to use ETP Payments Lump Sum D balance
80 ** 16-Oct-2012 ruihuang 115.64 Bug# 14675129 Modified function get_long_service_leave to support more decimal places for Accrual hours in the calculation of Long Service Leave on Termination
81 ** 16-Nov-2012 ruihuang 115.65 Bug# 14675129 Modified function get_long_service_leave to support 3 decimal places for Accrual hours in the calculation of Long Service Leave on Termination
82 ** 19-Nov-2012 skshin 115.66 Bug# 15846744 Modified calculate_etp_lumpsum_d_2012 function to use new record type of g_lump_sum_d for assignment_set usage
83 ** 11-Jan-2013 skshin 115.67 Bug# 15852244 Modifed calculate_etp_tax_2012 function to reduce ETP CAP with Non Excluded amount at under cap rate
84 ** 21-Feb-2013 skshin 115.68 Bug# 16319860 Modifed calculate_marginal_tax to ignore the cents from lv_average_term_pay_period
85 */
86
87 g_debug constant boolean := hr_utility.debug_enabled;
88 g_package constant varchar2(60) := 'pay_au_terminations.';
89 --
90 -------------------------------------------------------------------------------------------------
91 --
92 -- FUNCTION get_long_service_leave
93 --
94 -- Returns :
95 -- 1 if function runs successfully
96 -- 0 otherwise
97 --
98 -- Purpose : Calculates net amount of long service leave accrual plan and breaks the
99 -- amounts into the appropriate time buckets as required for Terminations
100 -- tax calculations. Days suspended is taken into account by using the accrual
101 -- fastformula.
102 --
103 -- In : p_assignment_id - assignment which is enrolled in the accrual plan
104 -- p_payroll_id - payroll to which the assignment is enrolled
105 -- p_business_group_id
106 -- p_effective_date - date up to which accrual is to be calculated
107 --
108 -- Out : p_pre_aug_1978 - net leave amount before from start of accrual plan to 15-AUG-1978
109 -- p_post_aug_1978 - net leave amount from 16-AUG-1978 to 17-AUG-1993
110 -- p_post_aug_1993 - net leave amount from 18-AUG-1993 until effective_date
111 --
112 -- Uses : per_accrual_calc_functions
113 -- pay_au_terminations
114 -- hr_utility
115 --
116 ------------------------------------------------------------------------------------------------
117 function get_long_service_leave
118 (p_assignment_id in number
119 ,p_payroll_id in number
120 ,p_business_group_id in number
121 ,p_effective_date in date
122 ,p_pre_aug_1978 out NOCOPY number
123 ,p_post_aug_1978 out NOCOPY number
124 ,p_post_aug_1993 out NOCOPY number
125 ) return number is
126 --
127 l_procedure constant varchar2(300) := g_package||'get_long_service_leave';
128 l_plan_id number;
129 l_long_service_leave varchar2(30);
130 --
131 -- Bug #14675129 -- Start
132 l_pre78_accrual number(9,3);
133 l_pre78_absence number(9,3);
134 l_post78_accrual number(9,3);
135 l_post78_absence number(9,3);
136 l_post93_accrual number(9,3);
137 l_post93_absence number(9,3);
138
139 -- Bug #14675129 -- End
140
141 l_others_entitlement_pre78 number(9,2);
142 l_others_entitlement_post78 number(9,2);
143 l_others_entitlement_post93 number(9,2);
144 l_start_date date;
145 l_end_date date;
146 l_accrual_end_date date;
147 l_pre_16_aug_1978 constant date := to_date('16-08-1978','DD-MM-YYYY');
148 l_post_15_aug_1978 constant date := to_date('15-08-1978','DD-MM-YYYY');
149 l_post_17_aug_1993 constant date := to_date('17-08-1993','DD-MM-YYYY');
150 --
151 -- Bug #1942971 -- Start
152 l_accrual_init_pre78 number(9,2);
153 l_accrual_init_post78 number(9,2);
154 l_accrual_init_post93 number(9,2);
155 l_entitlement_init_pre78 number(9,2);
156 l_entitlement_init_post78 number(9,2);
157 l_entitlement_init_post93 number(9,2);
158 l_initialise_type varchar2(100);
159
160 -- Bug #1942971 -- End
161
162 /* Start Bug 9950136 */
163 l_fw_others_ent_post93 NUMBER(9,2);
164 l_fw_post93_absence NUMBER(9,2);
165 /* End Bug 9950136 */
166
167 begin
168 /* Initialize the variables */
169 l_long_service_leave := 'AULSL';
170
171
172 IF g_debug THEN
173 hr_utility.trace('-----------------------------------------');
174 hr_utility.set_location('Entering : '||l_procedure, 1);
175 END IF;
176 --
177 -- Get the accrual plan for long service leave
178 --
179 l_plan_id := pay_au_terminations.get_accrual_plan_by_category
180 (p_assignment_id => p_assignment_id
181 ,p_effective_date => p_effective_date
182 ,p_plan_category => l_long_service_leave
183 );
184 IF g_debug THEN
185 hr_utility.trace('plan id := '||to_char(l_plan_id));
186 END IF;
187 --
188 ---------------------------
189 -- Calculate pre aug 1978
190 ---------------------------
191 -- Accrual pre-aug-1978
192 --
193 IF g_debug THEN
194 hr_utility.set_location(l_procedure, 10);
195 END IF;
196 per_accrual_calc_functions.get_accrual
197 (p_assignment_id => p_assignment_id
198 ,p_calculation_date => l_pre_16_aug_1978 - 1
199 ,p_plan_id => l_plan_id
200 ,p_business_group_id => p_business_group_id
201 ,p_payroll_id => p_payroll_id
202 ,p_start_date => l_start_date
203 ,p_end_date => l_end_date
204 ,p_accrual_end_date => l_accrual_end_date
205 ,p_accrual => l_pre78_accrual
206 );
207
208 l_others_entitlement_pre78 := per_accrual_calc_functions.get_other_net_contribution (p_assignment_id => p_assignment_id
209 ,p_plan_id => l_plan_id
210 ,p_start_date => l_start_date
211 ,p_calculation_date => l_pre_16_aug_1978 - 1);
212
213 -- Bug #1942971 -- Start
214
215 l_initialise_type := 'Leave Accrual Initialise' ;
216
217 l_accrual_init_pre78 := (hr_au_holidays.get_leave_initialise
218 (p_assignment_id => p_assignment_id
219 ,p_accrual_plan_id => l_plan_id
220 ,p_calc_end_date => l_pre_16_aug_1978 - 1
221 ,p_initialise_type => l_initialise_type
222 ,p_start_date => l_start_date
223 ,p_end_date => l_pre_16_aug_1978 - 1
224 ));
225
226 l_initialise_type := 'Leave Entitlement Initialise' ;
227
228 l_entitlement_init_pre78 := (hr_au_holidays.get_leave_initialise
229 (p_assignment_id => p_assignment_id
230 ,p_accrual_plan_id => l_plan_id
231 ,p_calc_end_date => l_pre_16_aug_1978 - 1
232 ,p_initialise_type => l_initialise_type
233 ,p_start_date => l_start_date
234 ,p_end_date => l_pre_16_aug_1978 - 1
235 ));
236
237 l_pre78_accrual:=l_pre78_accrual + l_others_entitlement_pre78 + l_accrual_init_pre78 + l_entitlement_init_pre78 ;
238
239 -- Bug #1942971 -- End
240 IF g_debug THEN
241 hr_utility.trace('p_start_date := '||to_char(l_start_date,'dd-MM-yyyy'));
242 hr_utility.trace('p_end_date := '||to_char(l_end_date,'dd-MM-yyyy'));
243 hr_utility.trace('p_accrual_end_date := '||to_char(l_accrual_end_date,'dd-MM-yyyy'));
244 hr_utility.trace('pre78 accrual := '||to_char((l_pre78_accrual)));
245 END IF;
246 --
247 -- Absences for pre-aug-1978
248 --
249 IF g_debug THEN
250 hr_utility.set_location(l_procedure, 15);
251 END IF;
252 l_pre78_absence := per_accrual_calc_functions.get_absence
253 (p_assignment_id => p_assignment_id
254 ,p_plan_id => l_plan_id
255 ,p_calculation_date => l_pre_16_aug_1978 - 1
256 ,p_start_date => l_start_date
257 );
258 IF g_debug THEN
259 hr_utility.trace('pre78 absence := '||to_char(l_pre78_absence));
260 END IF;
261 --
262 ---------------------------
263 -- Calculate post aug 1978
264 ---------------------------
265 -- Accrual post-aug-1978
266 --
267 IF g_debug THEN
268 hr_utility.set_location(l_procedure, 30);
269 END IF;
270 --
271 if p_effective_date < l_post_17_aug_1993 and p_effective_date > l_pre_16_aug_1978 then
272 per_accrual_calc_functions.get_accrual
273 (p_assignment_id => p_assignment_id
274 ,p_calculation_date => p_effective_date
275 ,p_plan_id => l_plan_id
276 ,p_business_group_id => p_business_group_id
277 ,p_payroll_id => p_payroll_id
278 ,p_start_date => l_start_date
279 ,p_end_date => l_end_date
280 ,p_accrual_end_date => l_accrual_end_date
281 ,p_accrual => l_post78_accrual
282 );
283 else
284 per_accrual_calc_functions.get_accrual
285 (p_assignment_id => p_assignment_id
286 ,p_calculation_date => l_post_17_aug_1993
287 ,p_plan_id => l_plan_id
288 ,p_business_group_id => p_business_group_id
289 ,p_payroll_id => p_payroll_id
290 ,p_start_date => l_start_date
291 ,p_end_date => l_end_date
292 ,p_accrual_end_date => l_accrual_end_date
293 ,p_accrual => l_post78_accrual
294 );
295
296 end if;
297 --Get the adjustments made and then add it to the accruals.Bug no 1855872
298 l_others_entitlement_post78 := per_accrual_calc_functions.get_other_net_contribution (p_assignment_id => p_assignment_id
299 ,p_plan_id => l_plan_id
300 ,p_start_date => l_start_date
301 ,p_calculation_date => l_post_17_aug_1993);
302
303
304 -- Bug #1942971 -- Start
305 l_initialise_type := 'Leave Accrual Initialise' ;
306
307 l_accrual_init_post78 := (hr_au_holidays.get_leave_initialise
308 (p_assignment_id => p_assignment_id
309 ,p_accrual_plan_id => l_plan_id
310 ,p_calc_end_date => l_post_17_aug_1993
311 ,p_initialise_type => l_initialise_type
312 ,p_start_date => l_pre_16_aug_1978
313 ,p_end_date => l_post_17_aug_1993
314 ));
315
316 l_initialise_type := 'Leave Entitlement Initialise' ;
317
318 l_entitlement_init_post78 := (hr_au_holidays.get_leave_initialise
319 (p_assignment_id => p_assignment_id
320 ,p_accrual_plan_id => l_plan_id
321 ,p_calc_end_date => l_post_17_aug_1993
322 ,p_initialise_type => l_initialise_type
323 ,p_start_date => l_pre_16_aug_1978
324 ,p_end_date => l_post_17_aug_1993
325 ));
326
327 l_post78_accrual := l_post78_accrual - l_pre78_accrual + l_others_entitlement_post78 + l_accrual_init_post78 + l_entitlement_init_post78;
328 --
329 -- Bug #1942971 -- End
330 IF g_debug THEN
331 hr_utility.trace('p_start_date := '||to_char(l_start_date,'dd-MM-yyyy'));
332 hr_utility.trace('p_end_date := '||to_char(l_end_date,'dd-MM-yyyy'));
333 hr_utility.trace('p_accrual_end_date := '||to_char(l_accrual_end_date,'dd-MM-yyyy'));
334 hr_utility.trace('post78 accrual := '||to_char((l_post78_accrual)));
335 END IF;
336 --
337 -- Absences for post-aug-1978
338 --
339 IF g_debug THEN
340 hr_utility.set_location(l_procedure, 35);
341 END IF;
342 l_post78_absence := per_accrual_calc_functions.get_absence
343 (p_assignment_id => p_assignment_id
344 ,p_plan_id => l_plan_id
345 ,p_calculation_date => l_post_17_aug_1993
346 ,p_start_date => greatest(l_post_15_aug_1978 + 1, l_start_date) -- bug9507714
347 );
348 IF g_debug THEN
349 hr_utility.trace('post78 absence := '||to_char(l_post78_absence));
350 END IF;
351 --
352 ---------------------------
353 -- Calculate post aug 1993
354 ---------------------------
355 -- Accrual post-aug-1993
356 --
357 IF g_debug THEN
358 hr_utility.set_location(l_procedure, 50);
359 END IF;
360
361 per_accrual_calc_functions.get_accrual
362 (p_assignment_id => p_assignment_id
363 ,p_calculation_date => p_effective_date
364 ,p_plan_id => l_plan_id
365 ,p_business_group_id => p_business_group_id
366 ,p_payroll_id => p_payroll_id
367 ,p_start_date => l_start_date
368 ,p_end_date => l_end_date
369 ,p_accrual_end_date => l_accrual_end_date
370 ,p_accrual => l_post93_accrual
371 );
372
373
374 /* Bug 9950136 - Added changes for Foreign Worker
375 Assumption: All FW periods are after 01-Jul-2010. So It will affect only the Post 1993 Accruals
376
377 Logic used:
378 (A) l_post93_accrual := Accrual (Standard Plan) + Accrual (FW Plan)
379 => [ get_accrual(Standard Plan) - get_accrual(Standard Plan in FW periods) ] + get_accrual(FW Plan)
380 => get_accrual(Standard Plan)
381
382 [Since setup is mirrored get_accrual(Standard Plan in FW periods) = get_accrual(FW Plan)]
383 Accrual will not be fetched for Foreign Plan seperately because the of the above calculations. The FW Plan
384 eventually contributes to 0 always.
385 (B) l_others_entitlement_post93 = Other Contrib (Standard Plan) + Other Contrib ( Foreign Plan)
386 (C) l_post93_absence = Absence (Standard Plan) + Other Contrib (Foreign Plan)
387
388 */
389
390 hr_au_holidays.set_accrual_ids(l_plan_id);
391
392 IF (hr_au_holidays.g_fw_exists AND hr_au_holidays.g_fw_plan_id IS NOT NULL
393 AND hr_au_holidays.g_fw_plan_id <> l_plan_id)
394 THEN
395
396 l_fw_others_ent_post93 := per_accrual_calc_functions.get_other_net_contribution
397 (p_assignment_id => p_assignment_id
398 ,p_plan_id => hr_au_holidays.g_fw_plan_id
399 ,p_start_date => l_start_date
400 ,p_calculation_date => p_effective_date);
401
402 l_fw_post93_absence := per_accrual_calc_functions.get_absence
403 (p_assignment_id => p_assignment_id
404 ,p_plan_id => hr_au_holidays.g_fw_plan_id
405 ,p_calculation_date => p_effective_date
406 ,p_start_date => greatest(l_post_17_aug_1993 + 1, l_start_date));
407 ELSE
408 l_fw_others_ent_post93 := 0;
409 l_fw_post93_absence := 0;
410 END IF;
411
412 IF g_debug
413 THEN
414 hr_utility.set_location('l_fw_others_ent_post93 '||l_fw_others_ent_post93,2000);
415 hr_utility.set_location('l_fw_post93_absence '||l_fw_post93_absence,2000);
416 END IF;
417
418 /* End Bug 9950136 */
419
420 --Get the adjustments made and then add it to the accruals.Bug no 1855872
421 l_others_entitlement_post93 := per_accrual_calc_functions.get_other_net_contribution (p_assignment_id => p_assignment_id
422 ,p_plan_id => l_plan_id
423 ,p_start_date => l_start_date
424 ,p_calculation_date => p_effective_date);
425
426 l_others_entitlement_post93 := l_others_entitlement_post93 + l_fw_others_ent_post93; /* Bug 9950136 */
427 l_post93_accrual:=l_post93_accrual + l_others_entitlement_post93;
428
429 -- Bug #1942971 -- Start
430 l_initialise_type := 'Leave Accrual Initialise' ;
431
432 l_accrual_init_post93 := (hr_au_holidays.get_leave_initialise
433 (p_assignment_id => p_assignment_id
434 ,p_accrual_plan_id => l_plan_id
435 ,p_calc_end_date => p_effective_date
436 ,p_initialise_type => l_initialise_type
437 ,p_start_date => l_post_17_aug_1993 + 1
438 ,p_end_date => l_end_date
439 ));
440
441 l_initialise_type := 'Leave Entitlement Initialise' ;
442
443 l_entitlement_init_post93 := (hr_au_holidays.get_leave_initialise
444 (p_assignment_id => p_assignment_id
445 ,p_accrual_plan_id => l_plan_id
446 ,p_calc_end_date => p_effective_date
447 ,p_initialise_type => l_initialise_type
448 ,p_start_date => l_post_17_aug_1993 + 1
449 ,p_end_date => l_end_date
450 ));
451
452 l_post93_accrual := l_post93_accrual - l_pre78_accrual - l_post78_accrual + l_accrual_init_post93 + l_entitlement_init_post93 ;
453 --
454 -- Bug #1942971 -- End
455 IF g_debug THEN
456 hr_utility.trace('p_start_date := '||to_char(l_start_date,'dd-MM-yyyy'));
457 hr_utility.trace('p_end_date := '||to_char(l_end_date,'dd-MM-yyyy'));
458 hr_utility.trace('p_accrual_end_date := '||to_char(l_accrual_end_date,'dd-MM-yyyy'));
459 hr_utility.trace('post93 accrual := '||to_char((l_post93_accrual)));
460 END IF;
461 --
462 -- Absences for post-aug-1993
463 --
464 IF g_debug THEN
465 hr_utility.set_location(l_procedure, 55);
466 END IF;
467
468 l_post93_absence := per_accrual_calc_functions.get_absence
469 (p_assignment_id => p_assignment_id
470 ,p_plan_id => l_plan_id
471 ,p_calculation_date => p_effective_date
472 ,p_start_date => greatest(l_post_17_aug_1993 + 1, l_start_date) -- bug9507714
473 );
474 l_post93_absence := l_post93_absence + l_fw_post93_absence ; /* Bug 9950136 */
475
476 IF g_debug THEN
477 hr_utility.trace('post93 absence := '||to_char(l_post93_absence));
478 END IF;
479 --
480 -- Absence are taken from accruals LIFO
481 --
482 IF g_debug THEN
483 hr_utility.set_location(l_procedure, 70);
484 END IF;
485 if l_post93_absence > l_post93_accrual
486 then
487 IF g_debug THEN
488 hr_utility.set_location(l_procedure, 72);
489 END IF;
490 p_post_aug_1993 := 0;
491 --
492 -- Move excess absences from post aug 1993 to absence for post aug 1978, previous bucket
493 --
494 l_post78_absence := l_post78_absence + (l_post93_absence - l_post93_accrual);
495 else
496 IF g_debug THEN
497 hr_utility.set_location(l_procedure, 74);
498 END IF;
499 p_post_aug_1993 := l_post93_accrual - l_post93_absence;
500 end if;
501 --
502 IF g_debug THEN
503 hr_utility.set_location(l_procedure, 80);
504 END IF;
505 if l_post78_absence > l_post78_accrual
506 then
507 IF g_debug THEN
508 hr_utility.set_location(l_procedure, 82);
509 END IF;
510 p_post_aug_1978 := 0;
511 --
512 -- Move excess absences from post aug 1978 to absence for pre aug 1978, previous bucket
513 --
514 l_pre78_absence := l_pre78_absence + (l_post78_absence - l_post78_accrual);
515 else
516 IF g_debug THEN
517 hr_utility.set_location(l_procedure, 84);
518 hr_utility.trace('l_post78_accrual:'||l_post78_accrual);
519 hr_utility.trace('l_post78_absence:'||l_post78_absence);
520 END IF;
521 p_post_aug_1978 := l_post78_accrual - l_post78_absence;
522 end if;
523 --
524 IF g_debug THEN
525 hr_utility.set_location(l_procedure, 90);
526 END IF;
527 if l_pre78_absence > l_pre78_accrual
528 then
529 IF g_debug THEN
530 hr_utility.set_location(l_procedure, 92);
531 END IF;
532 -- Just set to zero, as we will not use negative amounts
533 p_pre_aug_1978 := 0;
534 else
535 IF g_debug THEN
536 hr_utility.set_location(l_procedure, 94);
537 END IF;
538 p_pre_aug_1978 := l_pre78_accrual - l_pre78_absence;
539 end if;
540 --
541 IF g_debug THEN
542 hr_utility.set_location('Leaving : '||l_procedure, 999);
543 hr_utility.trace('-----------------------------------------');
544 END IF;
545 return 1;
546 end get_long_service_leave;
547 --
548 --------------------------------------------------------------------------------------
549 -- Start Bug# 5056831
550 --------------------------------------------------------------------------------------
551 -- Function override_elig
552 --
553 -- Returns 'Y' if the element entry has already been selected for override_elig in a payroll
554 -- run. Used by the PAYAUTRM form views
555 --
556 function override_elig
557 (p_element_entry_id number
558 ,p_input_value_id number
559 ,p_effective_date date
560 )
561 return varchar2 is
562 --
563 override_elig VARCHAR2(1) ;
564 --
565 -- Define how to determine if the entry is Prorated
566 --
567
568 cursor get_override_elig is
569
570 select nvl(eev.screen_entry_value, 'N')
571 from pay_element_entry_values_f eev
572 where eev.input_value_id = p_input_Value_id
573 and eev.element_entry_id = p_element_entry_id
574 and p_effective_date between eev.effective_Start_Date and eev.effective_end_date;
575
576 begin
577 override_elig := 'N';
578 --
579 open get_override_elig;
580 fetch get_override_elig into override_elig;
581 close get_override_elig;
582
583 return override_elig;
584
585 end override_elig;
586
587 --------------------------------------------------------------------------------------
588 -- End Bug# 5056831
589 --------------------------------------------------------------------------------------
590
591 -- Function processed
592 --
593 -- Returns 'Y' if the element entry has already been processed in a payroll
594 -- run. Used by the PAYAUTRM form views
595 --
596 function processed
597 (p_element_entry_id number
598 ,p_original_entry_id number
599 ,p_processing_type varchar2
600 ,p_entry_type varchar2
601 ,p_effective_date date
602 )
603 return varchar2 is
604 --
605 processed VARCHAR2(1) ;
606 l_source_id NUMBER ; --Bug 3306112
607 --
608 -- Define how to determine if the entry is processed
609 --
610 cursor nonrecurring_entries is
611 select 'Y'
612 from pay_run_results
613 where source_id = p_element_entry_id
614 and status <> 'U';
615 --
616 -- Bug 522510, recurring entries are considered as processed in the Date Earned period,
617 -- not Date Paid period - where run results exists.
618 --
619
620 -- Bug No: 3603495 - Modified the following cursor - Performance Fix.
621 cursor recurring_entries is
622 select 'Y'
623 from pay_run_results RESULT
624 where result.source_id = l_source_id --Bug 3306112
625 and result.status <> 'U'
626 and exists ( select 1
627 from pay_assignment_actions ASGT_ACTION
628 , pay_payroll_actions PAY_ACTION
629 , per_time_periods PERIOD
630 where result.assignment_action_id = asgt_action.assignment_action_id
631 and asgt_action.payroll_action_id = pay_action.payroll_action_id
632 and pay_action.payroll_id = period.payroll_id
633 and pay_action.date_earned between period.start_date and period.end_date
634 and p_effective_date between period.start_date and period.end_date
635 );
636 --
637 begin
638
639 processed := 'N';
640 l_source_id := nvl(p_original_entry_id, p_element_entry_id); --Bug 3306112
641 --
642 if (p_entry_type in ('S','D','A','R') or p_processing_type = 'N')
643 then
644 --
645 open nonrecurring_entries;
646 fetch nonrecurring_entries into processed;
647 close nonrecurring_entries;
648 --
649 else
650 --
651 open recurring_entries;
652 fetch recurring_entries into processed;
653 close recurring_entries;
654 --
655 end if;
656 --
657 return processed;
658 --
659 end processed;
660 --
661 ---------------------------------------------------------------------
662 -- Function get_accrual_plan_by_category
663 --
664 -- RETURNS: accrual_plan_id if successful, 0 otherwise
665 --
666 -- PURPOSE: To retrieve accrual plan id for designated category, copy
667 -- of hr_au_holidays equivalent except that when no accrual
668 -- plan is found return 0, this is to allow for casual employees
669 -- who do not have accrual plans and are not supposed to be paid
670 -- for such on termination.
671 --
672 -- IN: assignment_id
673 -- effective_date
674 -- accrual plan category - annual leave or long service leave
675 -- OUT:
676 --
677 --
678 -- USES: hr_utility
679 -- hr_au_holidays
680 --
681 function get_accrual_plan_by_category
682 (p_assignment_id in number
683 ,p_effective_date in date
684 ,p_plan_category in varchar2)
685 return number is
686 --
687 l_proc varchar2(72);
688 l_accrual_plan_id number ;
689 l_dummy number ;
690 --
691 /* Bug 9950136 - Modified cursor to pick the non-FW
692 Standard Plan Accrual ID
693 */
694 cursor csr_get_accrual_plan_id
695 (p_assignment_id number
696 ,p_effective_date date
697 ,p_plan_category varchar2
698 ) is
699 select pap.accrual_plan_id
700 from pay_accrual_plans pap,
701 pay_element_entries_f pee,
702 pay_element_links_f pel,
703 pay_element_types_f pet
704 where pee.assignment_id = p_assignment_id
705 and p_effective_date between pee.effective_start_date and pee.effective_end_date
706 and p_effective_date between pel.effective_start_date and pel.effective_end_date /*Added for 8482224*/
707 and p_effective_date between pet.effective_start_date and pet.effective_end_date /*Added for 8482224*/
708 and pel.element_link_id = pee.element_link_id
709 and pel.element_type_id = pet.element_type_id
710 and pap.accrual_plan_element_type_id = pet.element_type_id
711 and pap.accrual_category = p_plan_category
712 AND NVL(pap.information3,'N') = 'N'; /* Bug 9950136 */
713 --
714 begin
715 l_proc := g_package||'get_accrual_plan_by_category' ;
716 IF g_debug THEN
717 hr_utility.set_location(' Entering::'||l_proc,5);
718 END IF;
719 open csr_get_accrual_plan_id(p_assignment_id, p_effective_date, p_plan_category) ;
720 fetch csr_get_accrual_plan_id
721 into l_accrual_plan_id;
722 if csr_get_accrual_plan_id%notfound
723 then
724 close csr_get_accrual_plan_id;
725 return 0;
726 end if ;
727 fetch csr_get_accrual_plan_id
728 into l_dummy;
729 if csr_get_accrual_plan_id%found
730 then
731 close csr_get_accrual_plan_id;
732 IF g_debug THEN
733 hr_utility.set_location('Enrolled in Multiple Plans '||l_proc,15);
734 END IF;
735 hr_utility.set_message(801, 'HR_AU_TOO_MANY_ACCRUAL_PLANS');
736 hr_utility.raise_error;
737 end if;
738 close csr_get_accrual_plan_id;
739 IF g_debug THEN
740 hr_utility.set_location('Leaving:'||l_proc,20);
741 END IF;
742 return l_accrual_plan_id;
743 END get_accrual_plan_by_category;
744 --
745 ------------------------------------------------------------
746 -- Function calculate_marginal_tax
747 --
748 -- RETURNS: marginal tax deduction for termination amounts
749 --
750 -- PURPOSE: used by formula function to calculate marginal tax deduction for termination amounts
751 --
752 -- IN: p_date_earned - passed as a context
753 -- p_tax_variation_type - tax variation, percentage, marginal, fixed etc
754 -- p_tax_variation_amount - holds amount for tax variation type
755 -- p_gross_termination_amount - termination earnings amount used when performing marginal rate formula
756 -- p_average_pay - average earnings to be taxed
757 -- p_a_variable - variable used in marginal rate formula
758 -- p_b_variabel - variable used in marginal rate formula
759 -- p_pay_frequency - frequency of payroll used by formula to convert back to period amount
760 -- p_tax_instalment_deduction - used by formula to convert back to period amount
761 -- OUT:
762 --
763 --
764 -- USES: hr_utility
765 -- hr_au_holidays
766 --
767 function calculate_marginal_tax
768 ( p_date_earned in date
769 , p_tax_variation_type in varchar2
770 , p_tax_variation_amount in number
771 , p_gross_termination_amount in number
772 , p_average_pay in number
773 , p_a_variable1 in number
774 , p_b_variable1 in number
775 , p_a_variable2 in number
776 , p_b_variable2 in number
777 , p_pay_freq in number
778 , p_tax_scale in number
779 ) return number
780 is
781 lc_tax_exempt constant varchar2(3) := upper('e'); -- Exempt from Tax
782 lc_tax_percentage constant varchar2(3) := upper('p'); -- Tax calculated at a fixed percentage
783 lc_tax_amount constant varchar2(3) := upper('f'); -- Taxed a Fixed Amount
784 lc_tax_marginal constant varchar2(3) := upper('n'); -- Taxed at marginal ax-b
785 --
786 l_lump_tax number(15,2);
787 l_procedure constant varchar2(100) := g_package||'calculate_marginal_tax';
788 l_average_term_pay number(15,2);
789 l_average_term_pay_tax number(15,2);
790 l_average_pay_tax number(15,2);
791 lv_average_pay number(15,2);
792 lv_average_term_pay_period number(15,2);
793 --
794 begin
795
796
797 IF g_debug THEN
798 hr_utility.set_location(l_procedure, 1);
799 hr_utility.trace('p_tax_variation_type = '||p_tax_variation_type);
800 END IF;
801 --
802 -- Which Tax method to use : Exempt, Fixed %, Fixed Amt or Marginal Rate
803 --
804 if upper( p_tax_variation_type ) = lc_tax_exempt
805 then
806 --
807 -- Exempt from Tax
808 --
809 return 0;
810 --
811 elsif upper( p_tax_variation_type ) = lc_tax_percentage
812 then
813 --
814 -- Fixed Percentage
815 --
816 --************************************************************************************
817 return p_gross_termination_amount * ( p_tax_variation_amount/100 );
818 --
819 elsif upper( p_tax_variation_type ) = lc_tax_amount
820 then
821 --
822 -- Fixed Amount
823 --
824 --************************************************************************************
825 return p_tax_variation_amount;
826 --
827 elsif upper( p_tax_variation_type ) = lc_tax_marginal
828 then
829 --
830 -- Use Marginal Calculation ax-b
831 -- The amount used here is assumed to be a weekly amount
832 -- so it must be converted back to a period amount
833 --
834 IF g_debug THEN
835 hr_utility.trace('p_a_variable1 = '||to_char(p_a_variable1));
836 hr_utility.trace('p_b_variable1 = '||to_char(p_b_variable1));
837 hr_utility.trace('average pay = '||to_char(p_average_pay));
838 END IF;
839 --
840 -- Calculate the average termination pay
841 --
842 lv_average_term_pay_period := trunc(p_gross_termination_amount/p_pay_freq); /* 16319860 - ignoring the cents */
843
844 lv_average_pay := pay_au_paye_ff.convert_to_week(p_pay_freq,p_average_pay);
845 IF g_debug THEN
846
847 hr_utility.trace('lv_average_term_pay_period = '||to_char(lv_average_term_pay_period));
848 hr_utility.trace('average term pay = '||to_char(lv_average_pay));
849 END IF;
850 --
851 -- Calculate tax on the average pay + average term pay
852 --
853 l_average_pay_tax := (p_a_variable1 * pay_au_paye_ff.convert_to_week(p_pay_freq,(p_average_pay + lv_average_term_pay_period))) - p_b_variable1;
854 if p_tax_scale <> 4
855 then
856 l_average_pay_tax := round(l_average_pay_tax, 0);
857 else
858 l_average_pay_tax := trunc(l_average_pay_tax);
859 end if;
860 IF g_debug THEN
861 hr_utility.trace('average pay tax = '||to_char(l_average_pay_tax));
862 END IF;
863 --
864 -- Calculate tax on the average pay
865 --
866 IF g_debug THEN
867 hr_utility.trace('p_a_variable2 = '||to_char(p_a_variable2));
868 hr_utility.trace('p_b_variable2 = '||to_char(p_b_variable2));
869 END IF;
870
871 l_average_term_pay_tax := (p_a_variable2 * lv_average_pay) - p_b_variable2;
872 if p_tax_scale <> 4
873 then
874 l_average_term_pay_tax := round(l_average_term_pay_tax, 0);
875 else
876 l_average_term_pay_tax := trunc(l_average_term_pay_tax);
877 end if;
878 --
879 IF g_debug THEN
880 hr_utility.trace('average term tax = '||to_char(l_average_term_pay_tax));
881 END IF;
882 --
883 -- Total tax payable equals the difference between the tax amounts multiplied by 52
884 --
885 l_lump_tax := (pay_au_paye_ff.convert_to_period_amt(p_pay_freq,l_average_pay_tax,p_tax_scale) - pay_au_paye_ff.convert_to_period_amt(p_pay_freq,l_average_term_pay_tax,p_tax_scale)) * p_pay_freq;
886
887 --
888
889 --
890 IF g_debug THEN
891 hr_utility.trace('l_lump_tax = '||to_char(l_lump_tax));
892 END IF;
893 return l_lump_tax;
894 --
895 else
896 --
897 -- Invalid Tax Type
898 --
899 raise_application_error ( -20000, 'PAY Error : HR_AU_TAX_VAR_NOT_VALID' );
900 --
901 end if;
902 --
903 end calculate_marginal_tax;
904 --
905 ---------------------------------------------------
906 --
907 -- Function max_etp_tax_free
908 --
909 -- Calculates the maximum allowable amount of the
910 -- ETP payment components which can be free of tax
911 --
912 -- RETURNS: maximum tax free amount
913 --
914 -- USES: hr_utility
915 --
916 function max_etp_tax_free
917 (p_years_of_service in number
918 ,p_lump_d_tax_free in number
919 ,p_lump_d_service_increment in number
920 )
921 return number is
922 --
923 l_procedure constant varchar2(100) := g_package||'max_etp_tax_free';
924 --
925 begin
926 --
927 IF g_debug THEN
928 hr_utility.set_location(l_procedure, 1);
929 END IF;
930 return p_lump_d_tax_free + p_lump_d_service_increment * p_years_of_service;
931 --
932 end max_etp_tax_free;
933 --
934 ---------------------------------------------------
935 --
936 -- Function check_rollover
937 --
938 -- Checks to see if the user has entered a rollover
939 -- amount which will exceed the maximum allowable
940 --
941 -- RETURNS: 1 if amount elected to rollover is within the maximum allowed
942 -- 0 if amount exceeds limit
943 --
944 -- IN: p_rollover_amount - amount user has elected to roll into a super fund
945 -- p_maximum_rollover - the maximum allowable amount a user may elect to roll into a super fund
946 /* Start 8769345 */
947 -- IN: p_etp_component - This input value is used to set 'Taxable' or 'Tax Free' text
948 -- in the Super Rollover message.
949 /* End 8769345 */
950 --
951 -- OUT: p_message - error message containing token for maximum amount to roll over
952 --
953 -- USES: hr_utility
954 -- fnd_message
955 --
956 function check_rollover
957 (p_rollover_amount in number
958 ,p_maximum_rollover in number
959 ,p_message out NOCOPY varchar2
960 ,p_etp_component in varchar2 default 'Taxable'
961 )
962 return number is
963 l_procedure constant varchar2(100) := g_package||'check_rollover';
964 l_message varchar2(500);
965 begin
966
967 IF g_debug THEN
968 hr_utility.set_location(l_procedure,1);
969 hr_utility.trace('p_rollover_amount '||to_char(p_rollover_amount));
970 END IF;
971 if p_rollover_amount > p_maximum_rollover
972 then
973 IF g_debug THEN
974 hr_utility.trace('rollover exceeded - maximum '||to_char(p_maximum_rollover));
975 END IF;
976 fnd_message.set_name('PAY','HR_AU_SUPER_ROLL_NOT_VALID');
977 fnd_message.set_token('rollover_amount', p_maximum_rollover);
978 fnd_message.set_token('etp_component', p_etp_component);
979 p_message := fnd_message.get;
980 l_message := p_message;
981 IF g_debug THEN
982 hr_utility.trace('message = '||l_message);
983 END IF;
984 return 0;
985 else
986 return 1;
987 end if;
988 end check_rollover;
989 --
990 --------------------------------------------------
991 --
992 -- Function etp_prepost_ratios
993 --
994 -- Calculates the pre 01 July 1983 ratio for calculation of ETP
995 -- and the post 30 Jun 1983 ratio for calculation ETP
996 --
997 -- RETURNS: 1 if calculation was successful
998 -- 0 otherwise
999 --
1000 -- IN: p_assignment_id - assignment
1001 -- p_hire_date - date employee started work
1002 -- p_termination_date - date employee ends employment
1003 --
1004 -- OUT: p_pre01jul1983_ratio - ratio to use when calculating the pre 01 July 1983 portion of ETP
1005 -- p_post30jun1983_ratio - ratio to use when calculating the post 30 June 1983 portion of ETP
1006 --
1007 -- USES: hr_utility
1008 -- fffunc
1009 -- hr_au_holidays
1010 --
1011 function etp_prepost_ratios
1012 (p_assignment_id in number
1013 ,p_hire_date in date
1014 ,p_termination_date in date
1015 ,p_term_form_called in varchar2 -- Bug#2819479
1016 ,p_pre01jul1983_days out NOCOPY number
1017 ,p_post30jun1983_days out NOCOPY number
1018 ,p_pre01jul1983_ratio out NOCOPY number
1019 ,p_post30jun1983_ratio out NOCOPY number
1020 ,p_etp_service_date out NOCOPY date /* Bug# 2984390 */
1021 ,p_le_etp_service_date out NOCOPY date /* Bug 4177679 */
1022 )
1023 /* Bug# 2984390 Added new parameter p_etp_service_date, which is used for payment summary reporting purpose.
1024 If ETP service date is entered then return the ETP service date else return Hiredate */
1025
1026 return number is
1027
1028 /* Bug#2819479 - ETP Pre/Post enhancment */
1029 /* Get the details of the ETP Continuous Service Date and Pre/Post Days
1030 if Entered for the calculation. These details if provided by the user
1031 will be used in the ETP payments calculation. */
1032
1033 cursor c_get_etp_information(c_assignment_id per_all_assignments_f.assignment_id%TYPE)
1034 is
1035 select peev.screen_entry_value,
1036 peev1.screen_entry_value,
1037 to_date(peev2.screen_entry_value,'YYYY/MM/DD HH24:MI:SS')
1038 from pay_element_types_f pet,
1039 pay_element_entries_f peef,
1040 pay_element_links_f pel,
1041 pay_input_values_f piv,
1042 pay_input_values_f piv1,
1043 pay_input_values_f piv2,
1044 pay_element_entry_values_f peev,
1045 pay_element_entry_values_f peev1,
1046 pay_element_entry_values_f peev2
1047 where peef.assignment_id = c_assignment_id
1048 and pet.element_name = 'ETP on Termination'
1049 and pet.legislation_code = 'AU'
1050 and pet.element_type_id = pel.element_type_id
1051 and pel.element_link_id = peef.element_link_id
1052 and peef.element_entry_id = peev.element_entry_id
1053 and peef.element_entry_id = peev1.element_entry_id
1054 and peef.element_entry_id = peev2.element_entry_id
1055 and pet.element_type_id = piv.element_type_id
1056 and pet.element_type_id = piv1.element_type_id
1057 and pet.element_type_id = piv2.element_type_id
1058 and peev.input_value_id = piv.input_value_id
1059 and peev1.input_value_id = piv1.input_value_id
1060 and peev2.input_value_id = piv2.input_value_id
1061 and piv.name = 'Pre 1983 Days'
1062 and piv1.name = 'Post 1983 Days'
1063 and piv2.name = 'ETP Service Date';
1064
1065 l_procedure constant varchar2(100) := g_package||'etp_prepost_portions';
1066 l_pre_date date ;
1067 l_post_date date ;
1068 l_days_worked_pre number(9,2);
1069 l_days_worked_post number(9,2);
1070 l_days_worked_total number(9,2);
1071 l_days_suspended_pre number(9,2);
1072 l_days_suspended_post number(9,2);
1073 l_days_suspended_total number(9,2);
1074 l_actual_pre number(9,2);
1075 l_actual_post number(9,2);
1076 l_actual_total number(9,2);
1077 l_calculation_date date;
1078 l_term_form_called varchar2(10);
1079 l_calc_date_entered varchar2(10); /* Bug 4177679 */
1080
1081 begin
1082
1083 l_pre_date := to_date('01-07-1983','DD-MM-YYYY');
1084 l_post_date := to_date('30-06-1983','DD-MM-YYYY');
1085 l_term_form_called := nvl(p_term_form_called , 'N');
1086 p_le_etp_service_date :=null; /* bug 4177679 */
1087 l_calc_date_entered := 'Y'; /* Bug 4177679 */
1088
1089 IF g_debug THEN
1090 hr_utility.set_location(l_procedure,1);
1091 hr_utility.trace('p_assignment_id :'||p_assignment_id);
1092 hr_utility.trace('p_hire_date :'||p_hire_date);
1093 END IF;
1094
1095 open c_get_etp_information(p_assignment_id);
1096 fetch c_get_etp_information
1097 into l_actual_pre, l_actual_post, l_calculation_date;
1098 IF c_get_etp_information%FOUND then
1099 close c_get_etp_information;
1100 IF g_debug THEN
1101 hr_utility.trace('l_actual_pre :'||l_actual_pre);
1102 hr_utility.trace('l_actual_post :'||l_actual_post);
1103 hr_utility.trace('l_calculation_date :'||l_calculation_date);
1104 END IF;
1105 l_actual_total := l_actual_pre + l_actual_post;
1106 END IF;
1107 IF g_debug THEN
1108 hr_utility.trace('l_calculation_date :'||l_calculation_date);
1109 END IF;
1110
1111 IF l_actual_pre is not null and l_actual_post is not null
1112 and l_term_form_called = 'N' then
1113
1114 -- Use the pre/post days for the calculation of the pre/post ratios.
1115 IF g_debug THEN
1116 hr_utility.set_location(l_procedure, 30);
1117 END IF;
1118 p_pre01jul1983_days := l_actual_pre;
1119 p_post30jun1983_days := l_actual_post;
1120 IF (p_pre01jul1983_days = 0 and p_post30jun1983_days = 0) then
1121 p_pre01jul1983_ratio := 0; /* Bug: 2984390 */
1122 p_post30jun1983_ratio := 0;
1123 ELSE
1124 p_pre01jul1983_ratio := l_actual_pre/l_actual_total;
1125 p_post30jun1983_ratio := 1 - p_pre01jul1983_ratio;
1126 END IF;
1127 IF l_calculation_date is null then /* Bug# 2984390 */
1128 p_etp_service_date := p_hire_date;
1129 p_le_etp_service_date := null; /* Bug 4177679 */
1130 ELSE
1131 p_etp_service_date := l_calculation_date;
1132 p_le_etp_service_date := l_calculation_date; /* Bug 4177679 */
1133 END IF;
1134 IF g_debug THEN
1135 hr_utility.trace('p_pre01jul1983_days:'||p_pre01jul1983_days);
1136 hr_utility.trace('p_post30jun1983_days:'||p_post30jun1983_days);
1137 hr_utility.trace('p_pre01jul1983_ratio:'||p_pre01jul1983_ratio);
1138 hr_utility.trace('p_post30jun1983_ratio:'||p_post30jun1983_ratio);
1139 END IF;
1140 return 1;
1141
1142 END IF;
1143
1144 IF l_calculation_date is null and l_term_form_called = 'N' then
1145
1146 -- Use the Hire Date for the calculation of Pre/Post days
1147 -- Use the Hire Date of the employee for the pre/post ratio calculation.
1148 l_calc_date_entered :='N' ; /* Bug 4177679 */
1149 l_calculation_date := p_hire_date;
1150 p_etp_service_date := l_calculation_date; /* Bug# 2984390 */
1151 p_le_etp_service_date := null; /* Bug 4177679 */
1152
1153 IF g_debug THEN
1154 hr_utility.trace('p_hire_date : '||p_hire_date);
1155 END IF;
1156 END IF;
1157
1158 IF l_term_form_called = 'Y' and p_hire_date is not null then
1159 -- Use the Hire Date for the calculation of the Pre/Post days
1160 -- This Hire Date can have either ETP Service Date entered at Form or
1161 -- Original Hire Date of the employee
1162 l_calculation_date := p_hire_date;
1163 p_etp_service_date := l_calculation_date; /* Bug# 2984390 */
1164 p_le_etp_service_date := null; /* Bug 4177679 */
1165 END IF;
1166 IF g_debug THEN
1167 hr_utility.trace('l_calculation_date :'||l_calculation_date);
1168 END IF;
1169 /* End of Bug#2819479 */
1170
1171 --
1172 -- Did the employee start after or on 01 July 1983
1173 --
1174 if l_calculation_date >= l_pre_date
1175 then
1176 IF g_debug THEN
1177 hr_utility.set_location(l_procedure, 15);
1178 END IF;
1179 p_pre01jul1983_days := 0;
1180 p_pre01jul1983_ratio := 0;
1181 p_post30jun1983_ratio := 1;
1182 l_days_worked_post := fffunc.days_between(p_termination_date, l_calculation_date);
1183 l_days_suspended_post := hr_au_holidays.days_suspended(p_assignment_id, l_calculation_date, p_termination_date);
1184 p_post30jun1983_days := l_days_worked_post - l_days_suspended_post;
1185 p_etp_service_date := l_calculation_date; /* Bug# 2984390 */
1186 /* Bug 4177679 Only if the calculation date is entered then the p_le_etp_service_date will be passed*/
1187 if l_calc_date_entered = 'Y' then
1188 p_le_etp_service_date := l_calculation_date;
1189 end if;
1190 return 1;
1191 --
1192 -- Did the employee termination before 30 June 1983
1193 --
1194 elsif p_termination_date <= l_post_date
1195 then
1196 IF g_debug THEN
1197 hr_utility.set_location(l_procedure, 16);
1198 END IF;
1199 p_post30jun1983_days := 0;
1200 p_post30jun1983_ratio := 0;
1201 p_pre01jul1983_ratio := 1;
1202 l_days_worked_pre := fffunc.days_between(p_termination_date, l_calculation_date);
1203 l_days_suspended_pre := hr_au_holidays.days_suspended(p_assignment_id, l_calculation_date, p_termination_date);
1204 p_pre01jul1983_days := l_days_worked_pre - l_days_suspended_pre;
1205 p_etp_service_date := l_calculation_date; /* Bug# 2984390 */
1206 /* Bug 4177679 Only if the calculation date is entered then the p_le_etp_service_date will be passed*/
1207 if l_calc_date_entered = 'Y' then
1208 p_le_etp_service_date := l_calculation_date;
1209 end if;
1210 return 1;
1211 else
1212 IF g_debug THEN
1213 hr_utility.set_location(l_procedure, 20);
1214 END IF;
1215 --
1216 -- Calculate the number of days worked for pre, post and total
1217 --
1218 l_days_worked_pre := fffunc.days_between(l_pre_date, l_calculation_date);
1219 l_days_worked_post := fffunc.days_between(p_termination_date, l_post_date);
1220 l_days_worked_total := fffunc.days_between(p_termination_date, l_calculation_date) + 1;
1221 --
1222 -- How many of these days were suspended without pay for pre, post and total
1223 --
1224 l_days_suspended_pre := hr_au_holidays.days_suspended(p_assignment_id, l_calculation_date, l_pre_date);
1225 l_days_suspended_post := hr_au_holidays.days_suspended(p_assignment_id, l_post_date, p_termination_date);
1226 l_days_suspended_total := hr_au_holidays.days_suspended(p_assignment_id, l_calculation_date, p_termination_date);
1227 --
1228 l_actual_pre := l_days_worked_pre - l_days_suspended_pre;
1229 l_actual_post := l_days_worked_post - l_days_suspended_post;
1230 l_actual_total := l_days_worked_total - l_days_suspended_total;
1231 --
1232 if (l_actual_pre < 0) or (l_actual_post < 0) or (l_actual_total < 0)
1233 then
1234 IF g_debug THEN
1235 hr_utility.set_location(l_procedure, 21);
1236 END IF;
1237 return 0;
1238 end if;
1239 --
1240
1241 p_pre01jul1983_days := l_actual_pre;
1242 p_post30jun1983_days := l_actual_post;
1243 p_pre01jul1983_ratio := l_actual_pre/l_actual_total;
1244 p_post30jun1983_ratio := 1 - p_pre01jul1983_ratio;
1245 p_etp_service_date := l_calculation_date; /* Bug# 2984390 */
1246 /* Bug 4177679 Only if the calculation date is entered then the p_le_etp_service_date will be passed*/
1247 if l_calc_date_entered = 'Y' then
1248 p_le_etp_service_date := l_calculation_date;
1249 end if;
1250
1251 IF g_debug THEN
1252 hr_utility.trace('p_pre01jul1983_days:'||p_pre01jul1983_days);
1253 hr_utility.trace('p_post30jun1983_days:'||p_post30jun1983_days);
1254 hr_utility.trace('p_pre01jul1983_ratio:'||p_pre01jul1983_ratio);
1255 hr_utility.trace('p_post30jun1983_ratio:'||p_post30jun1983_ratio);
1256 END IF;
1257 return 1;
1258
1259 END IF;
1260 end etp_prepost_ratios;
1261
1262 --
1263 --------------------------------------------------
1264 --
1265 -- Function term_lsl_eligibility_years
1266 --
1267 -- gets the number of years a person must have worked
1268 -- before they become eiligible to recieve payment for
1269 -- long service leave upon termination of employment
1270 --
1271 -- RETURNS: 1 if successful, 0 otherwise
1272 --
1273 -- IN: p_date_earned - context passed in form payroll run
1274 -- p_accrual_plan_id - id of the particular long service leave accrual plan
1275 --
1276 -- OUT: p_eligibility_years - number of years until eligible
1277 --
1278 -- USES: hr_utility
1279 --
1280 --
1281 function term_lsl_eligibility_years
1282 (p_date_earned in date
1283 ,p_accrual_plan_id in number
1284 ,p_eligibility_years out NOCOPY number
1285 )
1286 return number is
1287 --
1288 -- Must limit the element to one which is of LSL classification
1289 -- as we are getting a value from the developer flex on the element
1290 -- which uses the element classification as a context
1291 --
1292 cursor csr_get_years
1293 (p_effective_date date
1294 ,p_accrual_plan_id number
1295 ) is
1296 select to_number(hrl.description)
1297 from pay_element_types_f petf
1298 , pay_input_values_f pivf
1299 , pay_accrual_plans pap
1300 , hr_lookups hrl
1301 , pay_element_classifications pec
1302 where pap.accrual_plan_id = p_accrual_plan_id
1303 and pivf.input_value_id = pap.pto_input_value_id
1304 and petf.element_type_id = pivf.element_type_id
1305 and petf.classification_id = pec.classification_id
1306 and pec.classification_name = 'Long Service Leave'
1307 and hrl.lookup_type (+) = 'AU_TERM_LSL_ELIGIBILITY_YEARS'
1308 and hrl.lookup_code (+) = petf.element_information1
1309 and hrl.enabled_flag (+) = 'Y'
1310 and p_effective_date between petf.effective_start_date and petf.effective_end_date
1311 and p_effective_date between pivf.effective_start_date and pivf.effective_end_date;
1312 --
1313 l_years number;
1314 l_procedure constant varchar2(100) := g_package||'term_lsl_eligibility_years';
1315 --
1316 begin
1317
1318 IF g_debug THEN
1319 hr_utility.set_location(l_procedure, 10);
1320 END IF;
1321 open csr_get_years(p_date_earned, p_accrual_plan_id);
1322 fetch csr_get_years
1323 into l_years;
1324 if csr_get_years%notfound
1325 then
1326 close csr_get_years;
1327 p_eligibility_years := 999;
1328 return 0;
1329 end if;
1330 close csr_get_years;
1331 --
1332 p_eligibility_years := l_years;
1333 IF g_debug THEN
1334 hr_utility.trace('LSL eligibility years : '||to_char(l_years));
1335 END IF;
1336 return 1;
1337 --
1338 end term_lsl_eligibility_years;
1339 --
1340
1341
1342 -- Bug#3263690 - NGE calculation Enhancement.
1343 -- Function to calculate the Normal Gross Earnings for a given assignment
1344 --
1345 FUNCTION CALCULATE_TERM_ASG_NGE
1346 ( p_assignment_id in per_all_assignments_f.assignment_id%TYPE,
1347 p_business_group_id in hr_all_organization_units.organization_id%TYPE,
1348 p_date_earned in date,
1349 p_tax_unit_id in hr_all_organization_units.organization_id%TYPE,
1350 p_assignment_action_id IN number, /*Bug 4538463*/
1351 p_payroll_id IN NUMBER, /*Bug 4538463*/
1352 p_termination_date in date,
1353 p_hire_date in date,
1354 p_period_start_date in date,
1355 p_period_end_date in date,
1356 p_case out NOCOPY varchar2,
1357 p_earnings_standard out NOCOPY number, /*Bug 4474896*/
1358 p_pre_tax_spread out NOCOPY number, /*Bug 4474896*/
1359 p_pre_tax_fixed out NOCOPY number, /*Bug 4474896*/
1360 p_pre_tax_prog out NOCOPY number, /*Bug 4474896*/
1361 p_paid_periods out NOCOPY number, /*Bug 4474896*/
1362 p_use_tax_flag IN VARCHAR2 --2610141
1363 )
1364 return NUMBER is
1365 -----------------------------------------------------------------------
1366 -- Variables
1367 -----------------------------------------------------------------------
1368 g_debug boolean;
1369 l_procedure varchar2(30);
1370
1371 -- This year Financial Start and End Dates
1372 --
1373 l_fin_start_date date;
1374 l_fin_end_date date;
1375
1376 -- Last Year Financial Start and End Dates
1377 --
1378 l_prev_yr_fin_start_date date ;
1379 l_prev_yr_fin_end_date date ;
1380
1381
1382 -- Variable to store the maximum previous year assignment action id and its corresponding
1383 -- tax_unit_id (legal Employer).
1384 l_asg_act_id pay_assignment_actions.assignment_action_id%TYPE;
1385 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
1386
1387
1388 -- Total Earnings variable
1389 --
1390 l_total_earnings number;
1391
1392 -- Loop Counter variable
1393 --
1394 i number;
1395
1396
1397 -----------------------------------------------------------------------
1398 -- Cursor : c_get_prev_year_max_asg_act_id
1399 -- Description : To get the Previous Year Maximum Assignment Action ID
1400 -- for a given Assignment_id in a Financial Year.
1401 -- If there exists any LE changes, then it gets the max
1402 -- Assignment Action ID for the corresponding LE.
1403 -----------------------------------------------------------------------
1404 CURSOR c_get_prev_year_max_asg_act_id
1405 ( c_assignment_id in per_all_assignments_f.assignment_id%TYPE,
1406 c_business_group_id in hr_all_organization_units.organization_id%TYPE,
1407 c_fin_start_date in date,
1408 c_fin_end_date in date)
1409 IS
1410 SELECT paa.assignment_action_id, paa.tax_unit_id, ppa.payroll_id
1411 FROM pay_assignment_actions paa
1412 ,pay_payroll_actions ppa
1413 WHERE paa.assignment_id = c_assignment_id
1414 and ppa.payroll_action_id = paa.payroll_action_id
1415 and ppa.business_group_id = c_business_group_id
1416 AND paa.action_sequence in
1417 (
1418 SELECT MAX(paa.action_sequence)
1419 FROM pay_assignment_actions paa,
1420 pay_payroll_actions ppa,
1421 per_all_assignments_f paaf
1422 WHERE ppa.business_group_id = c_business_group_id
1423 AND paaf.assignment_id = c_assignment_id
1424 AND paa.assignment_id = paaf.assignment_id
1425 AND ppa.payroll_action_id = paa.payroll_action_id
1426 AND ppa.action_type in ('Q','R','B','I','V') --2610141
1427 AND ppa.effective_date between c_fin_start_date AND c_fin_end_date
1428 AND paa.action_status = 'C'
1429 AND paa.tax_unit_id = p_tax_unit_id --2610141
1430 )
1431 ORDER BY date_earned desc;
1432
1433 /*Bug 2610141 - Cursor introduced to give the maxmimum assignment action id of previous legal employer*/
1434 CURSOR c_get_pre_le_max_asg_act_id
1435 ( c_assignment_id in per_all_assignments_f.assignment_id%TYPE,
1436 c_business_group_id in hr_all_organization_units.organization_id%TYPE,
1437 c_fin_start_date in date,
1438 c_fin_end_date in date)
1439 IS
1440 SELECT paa.assignment_action_id, paa.tax_unit_id, ppa.payroll_id, ppa.effective_date
1441 FROM pay_assignment_actions paa
1442 ,pay_payroll_actions ppa
1443 WHERE paa.assignment_id = c_assignment_id
1444 and ppa.payroll_action_id = paa.payroll_action_id
1445 and ppa.business_group_id = c_business_group_id
1446 AND paa.action_sequence in
1447 (
1448 SELECT MAX(paa.action_sequence)
1449 FROM pay_assignment_actions paa,
1450 pay_payroll_actions ppa,
1451 per_all_assignments_f paaf
1452 WHERE ppa.business_group_id = c_business_group_id
1453 AND paaf.assignment_id = c_assignment_id
1454 AND paa.assignment_id = paaf.assignment_id
1455 AND ppa.payroll_action_id = paa.payroll_action_id
1456 AND ppa.action_type in ('Q','R','B','I','V')
1457 AND paa.action_status = 'C'
1458 AND ppa.effective_date between c_fin_start_date AND c_fin_end_date
1459 )
1460 ORDER BY date_earned desc;
1461
1462 ---
1463 -----------------------------------------------------------------------
1464 -- Cursor : c_get_periods
1465 -- Description : To get the Previous Year number of periods to the
1466 -- given Assignment_id in previous Financial Year and Tax Unit
1467 -- Assumption : No changes to the Payroll during the Financial Year.
1468 -----------------------------------------------------------------------
1469
1470 /*Bug 4474896 - Cursor c_get_paid_periods changed to c_get_periods and logic for the cursor modified
1471 to count number of pay periods between greatest of (employee's hire date, financial year start date,
1472 Legal Employer start date) and current period end date*/
1473
1474 cursor c_get_periods
1475 (c_tax_unit_id in hr_all_organization_units.organization_id%TYPE,
1476 c_payroll_id in pay_payrolls_f.payroll_id%TYPE,
1477 c_start_date in date,
1478 c_end_date in date)
1479 is
1480 select count(DISTINCT ptp.time_period_id)
1481 from per_time_periods ptp
1482 where exists (select 'EXISTS' from
1483 per_assignments_f paf,
1484 hr_soft_coding_keyflex hsck
1485 where paf.assignment_id = p_assignment_id
1486 and paf.SOFT_CODING_KEYFLEX_ID = hsck.soft_coding_keyflex_id
1487 and hsck.segment1 = c_tax_unit_id
1488 AND paf.effective_start_date <= c_end_date
1489 AND paf.effective_end_date >= c_start_date
1490 AND paf.effective_start_date <= ptp.end_date
1491 AND paf.effective_end_date >= ptp.start_date)
1492 AND ptp.payroll_id = c_payroll_id
1493 AND ptp.start_date <= c_end_date
1494 AND ptp.end_date >= c_start_date;
1495
1496
1497 /*Bug 4538463 - Two new cursors introduced*/
1498
1499 CURSOR c_get_payroll_effective_date
1500 IS
1501 SELECT ppa.effective_date
1502 FROM pay_payroll_actions ppa,
1503 pay_assignment_actions paa
1504 WHERE paa.assignment_action_id = p_assignment_action_id
1505 AND ppa.payroll_action_id = paa.payroll_action_id;
1506
1507 CURSOR c_check_payroll_run (c_assignment_id in per_all_assignments_f.assignment_id%TYPE,
1508 c_business_group_id in hr_all_organization_units.organization_id%TYPE,
1509 c_start_date in date,
1510 c_end_date in date)
1511 IS
1512 SELECT count(paa.assignment_action_id)
1513 FROM pay_assignment_actions paa,
1514 pay_payroll_actions ppa,
1515 per_assignments_f paf
1516 WHERE ppa.effective_date BETWEEN c_start_date AND c_end_date
1517 AND ppa.business_group_id = c_business_group_id
1518 AND ppa.payroll_action_id = paa.payroll_action_id
1519 AND paa.assignment_id = c_assignment_id
1520 AND paa.assignment_id = paf.assignment_id
1521 AND ppa.effective_date between paf.effective_start_date and paf.effective_end_date
1522 AND paa.action_status = 'C'
1523 AND paa.source_action_id IS NULL /*Bug 4418107 - This join added to only pick master assignment action id*/
1524 AND ppa.action_type IN ('Q','R','I','B'); /*Bug 4474896 - Introduced action_types 'B' and 'I'*/
1525
1526 c_ytd_input_table c_get_ytd_def_bal_ids%rowtype;
1527 l_use_le_balances varchar2(50);
1528 l_db_item_suffix pay_balance_dimensions.database_item_suffix%type;
1529 l_payroll_id number;
1530 l_pay_eff_date DATE;
1531 l_flag number;
1532 l_eff_date DATE; /*Bug 4538463*/
1533 l_counter NUMBER; /*Bug 4538463*/
1534 l_leave_loading number := 0; /*bug8725341*/
1535
1536 BEGIN
1537
1538 l_procedure := 'calculate_term_asg_nge';
1539 g_ytd_def_bals_populated := FALSE;
1540 p_earnings_standard := 0; /*Bug 4474896*/
1541 p_pre_tax_spread := 0; /*Bug 4474896*/
1542 p_pre_tax_fixed := 0; /*Bug 4474896*/
1543 p_pre_tax_prog := 0; /*Bug 4474896*/
1544 i := 1;
1545 g_debug := hr_utility.debug_enabled;
1546 l_flag := -1;
1547
1548 OPEN c_get_payroll_effective_date; /*Bug 4538463*/
1549 FETCH c_get_payroll_effective_date INTO l_eff_date; /*Bug 4538463*/
1550 CLOSE c_get_payroll_effective_date; /*Bug 4538463*/
1551
1552 IF g_debug THEN
1553 hr_utility.set_location(' '||l_procedure, 10);
1554 hr_utility.set_location(' p_assignment_id : ' ||p_assignment_id,10);
1555 hr_utility.set_location(' p_business_group_id ' ||p_business_group_id,10);
1556 hr_utility.set_location(' p_date_earned ' ||p_date_earned,10);
1557 hr_utility.set_location(' p_tax_unit_id ' ||p_tax_unit_id,10);
1558 hr_utility.set_location(' p_termination_date ' ||p_termination_date,10);
1559 hr_utility.set_location(' p_hire_date ' ||p_hire_date,10);
1560 hr_utility.set_location(' p_period_start_date ' ||p_period_start_date,10);
1561 hr_utility.set_location(' p_period_end_date ' ||p_period_end_date,10);
1562 hr_utility.set_location(' p_case ' ||p_case,10);
1563 hr_utility.set_location(' to_char(p_date_earned,ddmm) ' ||to_char(p_date_earned,'ddmm'),10);
1564 END IF;
1565
1566 -- Find the Financial Year Start and End Dates
1567 /*Bug 4538463 - Modified logic to get financial year start date and end dates
1568 on the basis of the payroll effective date*/
1569
1570 IF MONTHS_BETWEEN(l_eff_date,TRUNC(l_eff_date,'Y')) < 6 THEN
1571 l_fin_start_date := to_date('01-07-'||to_char(add_months(trunc(l_eff_date,'Y'),-9),'YYYY'),'DD-MM-YYYY');
1572 l_fin_end_date := to_date('30-06-'||to_char(add_months(trunc(l_eff_date,'Y'),+3),'YYYY'),'DD-MM-YYYY');
1573 -- For Previous Fin Year
1574 l_prev_yr_fin_start_date := to_date('01-07-'||to_char(add_months(trunc(l_eff_date,'Y'),-9-12),'YYYY'),'DD-MM-YYYY');
1575 l_prev_yr_fin_end_date := to_date('30-06-'||to_char(add_months(trunc(l_eff_date,'Y'),+3-12),'YYYY'),'DD-MM-YYYY');
1576 ELSE
1577 l_fin_start_date := to_date('01-JUL-'||to_char(l_eff_date,'YYYY'),'DD-MM-YYYY');
1578 l_fin_end_date := to_date('30-JUN-'||to_char(add_months(l_eff_date,12),'YYYY'),'DD-MM-YYYY');
1579 -- For Previous Fin Year
1580 l_prev_yr_fin_start_date := to_date('01-07-'||to_char(add_months(l_eff_date,-12),'YYYY'),'DD-MM-YYYY');
1581 l_prev_yr_fin_end_date := to_date('30-06-'||to_char(trunc(l_eff_date,'Y'),'YYYY'),'DD-MM-YYYY');
1582
1583 END IF;
1584
1585 IF p_hire_date >= p_period_start_date and p_hire_date <= p_period_end_date and
1586 p_termination_date >= p_period_start_date and p_termination_date <= p_period_end_date THEN
1587
1588 /* Nothing has to be done
1589 Return the flag with USE_PERIOD_EARNINGS */
1590 p_case := 'USE_PERIOD_EARNINGS';
1591 return 100;
1592 END IF;
1593
1594 -- Use the cursor to check whether any periods in this Financial Year got processed
1595 -- before this RUN
1596 IF g_debug THEN
1597 hr_utility.set_location('l_fin_start_date: '|| l_fin_start_date, 20);
1598 hr_utility.set_location('l_fin_end_date: '|| l_fin_end_date, 20);
1599
1600 hr_utility.set_location('l_prev_yr_fin_start_date: '|| l_prev_yr_fin_start_date, 20);
1601 hr_utility.set_location('l_prev_yr_fin_end_date: '|| l_prev_yr_fin_end_date, 20);
1602 END IF;
1603
1604 /*Bug 4538463 - Use the cursor below to check if this is the first run for the assignment in the
1605 current financial year*/
1606 OPEN c_check_payroll_run(p_assignment_id,
1607 p_business_group_id,
1608 l_fin_start_date,
1609 p_period_start_date - 1); /*Bug 5388657 changed l_eff_date to p_period_start_date - 1 */
1610 FETCH c_check_payroll_run INTO l_counter;
1611 CLOSE c_check_payroll_run;
1612
1613 /* Bug 2610141 - Get the Maximum assignment action id for the Previous Financial Year for the current
1614 Legal Employer or the maximum assignment action id for previous legal employer for the
1615 current year*/
1616 IF l_counter = 0 OR p_use_tax_flag = 'N' THEN
1617 OPEN c_get_prev_year_max_asg_act_id(p_assignment_id, p_business_group_id, l_prev_yr_fin_start_date, l_prev_yr_fin_end_date);
1618 FETCH c_get_prev_year_max_asg_act_id into l_asg_act_id, l_tax_unit_id, l_payroll_id;
1619 CLOSE c_get_prev_year_max_asg_act_id;
1620 IF nvl(l_asg_act_id,-99999) <> -99999 THEN /*Bug 4538463*/
1621 l_flag := 1; /* Flag is set to 1 when we take YTD earnings for previous year for the current legal employer*/
1622 END IF;
1623 hr_utility.trace('Inside 1');
1624 ELSE
1625 OPEN c_get_pre_le_max_asg_act_id(p_assignment_id, p_business_group_id, l_fin_start_date, l_eff_date);
1626 FETCH c_get_pre_le_max_asg_act_id into l_asg_act_id, l_tax_unit_id, l_payroll_id, l_pay_eff_date;
1627 CLOSE c_get_pre_le_max_asg_act_id;
1628 IF nvl(l_asg_act_id,-99999) <> -99999 THEN /*Bug 4538463*/
1629 l_flag := 2; /* Flag is set to 2 when we take YTD earnings for current year for the previous legal employer*/
1630 END IF;
1631 hr_utility.trace('Inside 2');
1632 END IF;
1633
1634 IF l_flag = -1 THEN
1635 OPEN c_get_pre_le_max_asg_act_id(p_assignment_id, p_business_group_id, l_prev_yr_fin_start_date, l_prev_yr_fin_end_date);
1636 FETCH c_get_pre_le_max_asg_act_id into l_asg_act_id, l_tax_unit_id, l_payroll_id, l_pay_eff_date;
1637 CLOSE c_get_pre_le_max_asg_act_id;
1638 l_flag := 3; /* Bug 4538463 - Flag is set to 3 when we take YTD earnings for previous year for the legal employer effective on
1639 on the last run of year*/
1640 hr_utility.trace('Inside 3');
1641 END IF;
1642
1643 IF g_debug THEN
1644 hr_utility.set_location('l_asg_act_id: '|| l_asg_act_id, 30);
1645 hr_utility.set_location('g_context_table(1).tax_unit_id: '|| l_tax_unit_id, 30);
1646 hr_utility.set_location('l_payroll_id: '||l_payroll_id, 30);
1647 hr_utility.set_location('p_tax_unit_id :'||p_tax_unit_id, 30);
1648 END IF;
1649
1650
1651 IF nvl(l_asg_act_id,-99999) = -99999 THEN
1652 /* There is no payroll actions exist in the previous financial year and also there is no
1653 actions present in the current year. This means the customer go live and this is the
1654 first payroll action
1655 For this case, need to populate message to the user in order to process the Termination
1656 Payments Manually. For this set the p_case to 'POPULATE_MSG'
1657 Average_Earnings will not be calculated.
1658 */
1659 p_case := 'POPULATE_MSG';
1660 IF g_debug THEN
1661 hr_utility.set_location('p_case: '|| p_case, 40);
1662 END IF;
1663 RETURN 110;
1664
1665 ELSE
1666
1667 /* Bug 2610141 - Get the Total Number of Paid Periods for the Previous Financial Year for the current
1668 Legal Employer or the number of paid periods of the previous legal employer for the
1669 current year*/
1670 IF l_flag = 1 OR l_flag = 3 THEN
1671 OPEN c_get_periods
1672 (l_tax_unit_id,
1673 l_payroll_id,
1674 l_prev_yr_fin_start_date,
1675 l_prev_yr_fin_end_date);
1676 FETCH c_get_periods INTO p_paid_periods;
1677 CLOSE c_get_periods;
1678 ELSE
1679 OPEN c_get_periods
1680 (l_tax_unit_id,
1681 l_payroll_id,
1682 l_fin_start_date,
1683 p_period_start_date - 1);
1684 FETCH c_get_periods INTO p_paid_periods;
1685 CLOSE c_get_periods;
1686 END IF;
1687
1688
1689
1690 IF g_debug THEN
1691 hr_utility.set_location('p_paid_periods: '|| p_paid_periods, 50);
1692 END IF;
1693
1694
1695 IF NOT g_ytd_def_bals_populated THEN
1696 -- Fetch the defined balance ids for the required balances
1697 --
1698
1699 /*bug 2610141*/
1700 IF p_use_tax_flag = 'Y' THEN
1701 l_db_item_suffix := '_ASG_LE_YTD';
1702 ELSE
1703 l_db_item_suffix := '_ASG_YTD';
1704 END IF ;
1705
1706 OPEN c_get_ytd_def_bal_ids(l_db_item_suffix);
1707 LOOP
1708 FETCH c_get_ytd_def_bal_ids into c_ytd_input_table;
1709 EXIT WHEN c_get_ytd_def_bal_ids%NOTFOUND;
1710
1711 -- Populate the Defined Balances Input Values Table
1712 g_ytd_input_table(i).defined_balance_id := c_ytd_input_table.defined_balance_id;
1713 g_ytd_input_table(i).balance_value := null;
1714
1715 -- Populate the contexts Table
1716
1717 /*bug 2610141*/
1718 IF p_use_tax_flag = 'Y' THEN
1719 g_ytd_context_table(1).tax_unit_id := l_tax_unit_id;
1720 ELSE
1721 g_ytd_context_table(1).tax_unit_id := null;
1722 END IF;
1723
1724 -- Populate the Global Defined Balances Table
1725 g_ytd_bals(i).defined_balance_id := c_ytd_input_table.defined_balance_id;
1726 g_ytd_bals(i).balance_name := c_ytd_input_table.balance_name;
1727 g_ytd_bals(i).dimension_name := c_ytd_input_table.dimension_name;
1728
1729 i := i+1;
1730 END LOOP;
1731 CLOSE c_get_ytd_def_bal_ids;
1732 g_ytd_def_bals_populated := TRUE;
1733
1734 END IF;
1735
1736 -- Use BBR for retrieving the balance values for the previous financial year.
1737 --
1738 pay_balance_pkg.get_value(P_ASSIGNMENT_ACTION_ID =>l_asg_act_id,
1739 P_DEFINED_BALANCE_LST => g_ytd_input_table,
1740 P_CONTEXT_LST => g_ytd_context_table,
1741 P_OUTPUT_TABLE => g_ytd_result_table);
1742
1743 /*Bug 4474896 - Modified the code to pick balances Earnings Standard, Pre Tax Spread, Pre Tax Progressive, Pre Tax Fixed*/
1744 FOR i in g_ytd_result_table.first .. g_ytd_result_table.last
1745 LOOP
1746 IF g_ytd_result_table(i).defined_balance_id = g_ytd_bals(i).defined_balance_id
1747 and g_ytd_bals(i).balance_name = 'Earnings_Standard'
1748 THEN
1749 p_earnings_standard := nvl(g_ytd_result_table(i).balance_value,0);
1750 IF g_debug THEN
1751 hr_utility.set_location('p_earnings_standard: '||p_earnings_standard, 60);
1752 END IF;
1753 ELSIF g_ytd_result_table(i).defined_balance_id = g_ytd_bals(i).defined_balance_id
1754 and g_ytd_bals(i).balance_name = 'Pre Tax Spread Deductions'
1755 THEN
1756 p_pre_tax_spread := nvl(g_ytd_result_table(i).balance_value,0);
1757 IF g_debug THEN
1758 hr_utility.set_location('p_pre_tax_spread_deductions: '||p_pre_tax_spread, 60);
1759 END IF;
1760
1761 ELSIF g_ytd_result_table(i).defined_balance_id = g_ytd_bals(i).defined_balance_id
1762 and g_ytd_bals(i).balance_name = 'Pre Tax Fixed Deductions' and p_use_tax_flag = 'Y'
1763 /*bug4363057*/
1764 THEN
1765 p_pre_tax_fixed := nvl(g_ytd_result_table(i).balance_value,0);
1766 IF g_debug THEN
1767 hr_utility.set_location('p_pre_tax_fixed_deductions: '||p_pre_tax_fixed, 60);
1768 END IF;
1769 ELSIF g_ytd_result_table(i).defined_balance_id = g_ytd_bals(i).defined_balance_id
1770 and g_ytd_bals(i).balance_name = 'Pre Tax Progressive Deductions' and p_use_tax_flag = 'Y'
1771 /*bug4363057*/
1772 THEN
1773 p_pre_tax_prog := nvl(g_ytd_result_table(i).balance_value,0);
1774 IF g_debug THEN
1775 hr_utility.set_location('p_pre_tax_progressive_deductions: '||p_pre_tax_prog, 60);
1776 END IF;
1777
1778 ELSIF g_ytd_result_table(i).defined_balance_id = g_ytd_bals(i).defined_balance_id
1779 and g_ytd_bals(i).balance_name = 'Earnings_Leave_Loading' and p_use_tax_flag = 'Y'
1780 /*bug8725341*/
1781 THEN
1782 l_leave_loading := nvl(g_ytd_result_table(i).balance_value,0);
1783 IF g_debug THEN
1784 hr_utility.set_location('l_earnings_leave_loading: '||l_leave_loading, 60);
1785 END IF;
1786
1787
1788 END IF;
1789 END LOOP;
1790
1791 IF to_char(l_eff_date,'dd/mm/yyyy') >= '01/07/2009' THEN /*bug8725341*/
1792 p_earnings_standard := p_earnings_standard + l_leave_loading;
1793 END IF;
1794
1795 return 1000;
1796 END IF;
1797
1798 END calculate_term_asg_nge;
1799
1800 --
1801 -- Bug 5107059 - Function to return the summed accrued hours of all accrual plan of category AU Annual Leave
1802 -- attached with the assignment
1803 --
1804
1805 FUNCTION get_total_accrual_hours
1806 ( p_assignment_id IN NUMBER
1807 ,p_business_group_id IN NUMBER
1808 , p_payroll_id IN Number
1809 ,p_plan_category IN VARCHAR2
1810 ,p_effective_date IN DATE
1811 ) RETURN NUMBER IS
1812 l_proc VARCHAR2(72) := g_package||'get_total_accrual_hours' ;
1813 l_accrual_plan_id NUMBER ;
1814 l_dummy NUMBER ;
1815 l_hours_flag char(1);
1816 l_days_flag char(1);
1817 l_error char(1);
1818 l_hours number;
1819
1820 CURSOR csr_get_accrual_plan_id(p_assignment_id NUMBER
1821 ,p_effective_date DATE
1822 ,p_plan_category VARCHAR2) IS
1823 SELECT pap.accrual_plan_id,pap.accrual_units_of_measure
1824 FROM pay_accrual_plans pap,
1825 pay_element_entries_f pee,
1826 pay_element_links_f pel,
1827 pay_element_types_f pet
1828 WHERE pee.assignment_id = p_assignment_id
1829 AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
1830 AND pel.element_link_id = pee.element_link_id
1831 AND pel.element_type_id = pet.element_type_id
1832 AND pap.accrual_plan_element_type_id = pet.element_type_id
1833 AND pap.accrual_category = p_plan_category ;
1834
1835 BEGIN
1836 hr_utility.set_location(' Entering::'||l_proc,1);
1837 l_hours_flag := 'N';
1838 l_days_flag := 'N';
1839 l_hours := 0;
1840 l_error := 'N';
1841 for csr1 in csr_get_accrual_plan_id(p_assignment_id, p_effective_date, p_plan_category)
1842 loop
1843 if (csr1.accrual_units_of_measure = 'H' and l_days_flag='Y') or (csr1.accrual_units_of_measure = 'D' and l_hours_flag='Y') then
1844 l_error := 'Y';
1845 exit;
1846 end if;
1847
1848 if csr1.accrual_units_of_measure = 'H' and l_hours_flag='N' then
1849 l_hours_flag := 'Y';
1850 end if;
1851
1852 if csr1.accrual_units_of_measure = 'D' and l_days_flag='N' then
1853 l_days_flag := 'Y';
1854 end if;
1855
1856 l_hours := l_hours + hr_au_holidays.get_net_accrual(p_assignment_id,p_payroll_id,
1857 p_business_group_id, csr1.accrual_plan_id,p_effective_date);
1858 end loop;
1859
1860 IF l_error = 'Y'
1861 THEN
1862 hr_utility.set_location('Enrolled in Multiple Plans '||l_proc,15);
1863 hr_utility.set_message(801, 'HR_AU_MULTIPLE_ACCRUAL_PLANS');
1864 hr_utility.raise_error;
1865 END IF;
1866
1867 hr_utility.set_location('Leaving:'||l_proc,2);
1868
1869 RETURN l_hours;
1870
1871 -- EXCEPTION
1872 -- WHEN OTHERS THEN
1873 -- hr_utility.set_location('Leaving:'||l_proc,99);
1874 -- RETURN NULL;
1875 END get_total_accrual_hours;
1876
1877 --------------------------------------------------
1878 --
1879 -- Function calculate_etp_tax Bug 5956223
1880 --
1881 -- Calculate ETP Tax on the ETP amount passed
1882 -- based on User Tables values
1883 --
1884 -- RETURNS: ETP Tax
1885 --
1886 -- IN: p_etp_amount Amount on which tax needs to be calculated
1887 -- p_trans_etp Transitional Or Non Transitional ETP or termination type Death
1888 -- Values can be (D Death, TRANS Transitional,NONTRANS Non Transitional )
1889 -- p_death_benefit_type Beneficiary in Death case is Dependent or Non Dependent
1890 -- p_over_pres_age Yes or No
1891 -- p_tfn_for_non_dependant In Death case TFN for Non Dependent
1892 FUNCTION calculate_etp_tax
1893 (p_business_group_id IN NUMBER
1894 ,p_date_paid IN DATE
1895 ,p_etp_amount IN NUMBER
1896 ,p_trans_etp IN VARCHAR2
1897 ,p_death_benefit_type IN VARCHAR2
1898 ,p_over_pres_age IN VARCHAR2
1899 ,p_tfn_for_non_dependent IN VARCHAR2
1900 ,p_medicare_levy IN NUMBER
1901 )
1902 RETURN NUMBER IS
1903
1904 /*This Cursor is used to compute the ETP tax amount based on the ETP Amount and User table
1905 As its a Slab based taxation , Slabs in which the amount is coming and all lower slabs
1906 needs to be taken into consideration
1907
1908 "and p_amount > purf.row_low_range_or_name "
1909
1910 The least of ETP Amount and Higher range is taken
1911 "to_number(purf.row_high_range),p_amount)"
1912
1913 and then the difference with the lower range, This Difference is then multiplied by the Tax Percentage + Medicare Levy
1914
1915 The Summation of all these values is the Net ETP Tax
1916
1917 Bug 6430072 - Added condition to convert User Table values to number using fnd_number.canonical_to_number
1918 and not to_number.
1919 */
1920
1921 CURSOR csr_get_etp_tax(p_bus_grp_id IN hr_all_organization_units.organization_id%TYPE
1922 ,p_date_paid IN DATE
1923 ,p_user_table IN VARCHAR2
1924 ,p_amount IN NUMBER
1925 ,p_med_levy IN NUMBER) IS
1926 SELECT /*+ INDEX(PURF) ORDERED */ NVL(SUM(round((least(fnd_number.canonical_to_number(purf.row_high_range),p_amount)-fnd_number.canonical_to_number(purf.row_low_range_or_name))
1927 *(fnd_number.canonical_to_number(pucif.value) + decode(fnd_number.canonical_to_number(pucif.value),0,0,p_med_levy)))),0) -- bug8647962
1928 FROM pay_user_tables put,
1929 pay_user_rows_F purf,
1930 pay_user_columns puc,
1931 pay_user_column_instances_f pucif
1932 where put.legislation_code='AU'
1933 and put.user_table_name=p_user_table
1934 and put.user_table_id=purf.user_table_id
1935 and put.user_table_id=puc.user_table_id
1936 and puc.user_column_id=pucif.user_column_id
1937 and purf.user_row_id=pucif.user_row_id
1938 and p_date_paid between purf.effective_start_date and purf.effective_end_date
1939 and p_date_paid between pucif.effective_start_date and pucif.effective_end_date
1940 and p_amount > fnd_number.canonical_to_number(purf.row_low_range_or_name) /* 6430072 */
1941 order by fnd_number.canonical_to_number(purf.row_low_range_or_name);
1942
1943 l_procedure VARCHAR2(80);
1944 lv_user_table VARCHAR2(50);
1945 lv_etp_tax NUMBER :=0;
1946 lv_medicare_levy NUMBER := 0;
1947
1948 BEGIN
1949
1950 l_procedure := 'calculate_etp_tax';
1951 IF g_debug THEN
1952 hr_utility.set_location(' '||l_procedure, 10);
1953 hr_utility.set_location('IN p_business_group_id ' ||p_business_group_id,10);
1954 hr_utility.set_location('IN p_date_paid ' ||p_date_paid,10);
1955 hr_utility.set_location('IN p_etp_amount ' ||p_etp_amount,10);
1956 hr_utility.set_location('IN p_trans_etp ' ||p_trans_etp,10);
1957 hr_utility.set_location('IN p_death_benefit_type ' ||p_death_benefit_type,10);
1958 hr_utility.set_location('IN p_over_pres_age ' ||p_over_pres_age,10);
1959 hr_utility.set_location('IN p_tfn_for_non_dependent ' ||p_tfn_for_non_dependent,10);
1960 hr_utility.set_location('IN p_medicare_levy ' ||p_medicare_levy,10);
1961
1962 END IF;
1963
1964 lv_etp_tax:=0;
1965 lv_user_table:='zzz';
1966
1967 lv_medicare_levy := p_medicare_levy;
1968
1969 IF p_trans_etp='D' THEN
1970 IF p_death_benefit_type = 'D' THEN
1971 lv_user_table := 'TAX_SCALE_ETP_DEATH_DEPENDENT';
1972 ELSE
1973 lv_user_table := 'TAX_SCALE_ETP_DEATH_NON_DEPENDENT';
1974 IF p_tfn_for_non_dependent ='N' THEN
1975 lv_medicare_levy:=0;
1976 END IF;
1977 END IF;
1978 END IF;
1979
1980 IF p_trans_etp='NONTRANS' THEN
1981 IF p_over_pres_age ='N' THEN
1982 lv_user_table := 'TAX_SCALE_NON_TRANS_UNDER_PREV_AGE';
1983 ELSIF p_over_pres_age ='Y' THEN
1984 lv_user_table := 'TAX_SCALE_NON_TRANS_OVER_PREV_AGE';
1985 END IF;
1986 END IF;
1987
1988
1989 IF p_trans_etp='TRANS' THEN
1990 IF p_over_pres_age ='N' THEN
1991 lv_user_table := 'TAX_SCALE_TRANS_UNDER_PREV_AGE';
1992 ELSIF p_over_pres_age ='Y' THEN
1993 lv_user_table := 'TAX_SCALE_TRANS_OVER_PREV_AGE';
1994 END IF;
1995 END IF;
1996
1997
1998 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,lv_user_table,p_etp_amount,lv_medicare_levy);
1999 FETCH csr_get_etp_tax into lv_etp_tax;
2000 CLOSE csr_get_etp_tax;
2001
2002 IF g_debug THEN
2003 hr_utility.set_location('lv_user_table ' ||lv_user_table,10);
2004 hr_utility.set_location('RETURN lv_etp_tax '||lv_etp_tax,10);
2005 END IF;
2006 RETURN lv_etp_tax;
2007
2008 END calculate_etp_tax;
2009 --------------------------------------------------
2010 --
2011 -- Function get_fin_year_end Bug 5956223
2012 --
2013 -- Calculate Financial Year end date based on a given date
2014 --
2015 -- RETURNS: Financial Year end date
2016 -- IN : Date
2017 FUNCTION get_fin_year_end
2018 (p_date IN DATE)
2019 RETURN DATE IS
2020
2021 ld_fin_year_end DATE;
2022 l_procedure VARCHAR2(80);
2023 BEGIN
2024
2025 l_procedure := 'get_fin_year_end';
2026 IF g_debug THEN
2027 hr_utility.set_location(' '||l_procedure, 10);
2028 hr_utility.set_location('IN p_date ' ||p_date,10);
2029 END IF;
2030
2031 ld_fin_year_end := to_date('01/01/1900','DD/MM/YYYY');
2032
2033 IF to_number(to_char(p_date,'MM')) <= 06 THEN
2034
2035 ld_fin_year_end := to_date('30/06/'||to_char(p_date,'YYYY'),'DD/MM/YYYY');
2036
2037 ELSE
2038
2039 ld_fin_year_end := ADD_MONTHS(to_date('30/06/'||to_char(p_date,'YYYY'),'DD/MM/YYYY'),12);
2040 END IF;
2041
2042 IF g_debug THEN
2043 hr_utility.set_location('RETURN ld_fin_year_end '||ld_fin_year_end,10);
2044 END IF;
2045
2046 RETURN ld_fin_year_end;
2047
2048 END get_fin_year_end;
2049
2050 --------------------------------------------------
2051 --
2052 -- Function get_prev_age Bug 6071863
2053 --
2054 -- Get the Preservation Age from User Table ETP_PRESERVATION_AGE
2055 -- based on Employee's Date of Birth
2056 --
2057 -- RETURNS: Preservation Age
2058 -- IN : Date
2059 FUNCTION get_prev_age
2060 (p_date_of_birth IN DATE,p_date_paid IN DATE)
2061 RETURN NUMBER IS
2062
2063 /* This Cursor is used to fetch Preservation Age based on Employee's Date of Birth
2064 from User table 'ETP_PRESERVATION_AGE' .
2065 From 01-Jul-2007 Date of Birth Preservation Age
2066 Before 01-Jul-1960 55
2067 01-Jul-1960 30-Jun-1961 56
2068 01-Jul-1961 30-Jun-1962 57
2069 01-Jul-1962 30-Jun-1963 58
2070 01-Jul-1963 30-Jun-1964 59
2071 After 30-Jun-1964 60
2072
2073 In User Tables as only Number DataType can be Kept dates are stored in number format as YYYYMMDD
2074 For Example 01-JUL-1960 = 19600701
2075 30-JUN-1961 = 19610601
2076
2077 In the cursor this is converted to to_date(purf.row_low_range_or_name,'YYYYMMDD')
2078 when comparing with Date of Birth */
2079 /* Avoided converting the varchar2 columns purf.row_low_range_or_name and purf.row_high_range to date
2080 instead converting p_date_of_birth to number and then comparing*/
2081
2082
2083 CURSOR csr_get_prev_age IS
2084 SELECT /*+ index(PURF) ORDERED */ to_number(pucif.value) -- bug8647962
2085 FROM pay_user_tables put,
2086 pay_user_rows_F purf,
2087 pay_user_columns puc,
2088 pay_user_column_instances_f pucif
2089 where put.legislation_code='AU'
2090 and put.user_table_name='ETP_PRESERVATION_AGE'
2091 and put.user_table_id=purf.user_table_id
2092 and put.user_table_id=puc.user_table_id
2093 and puc.user_column_id=pucif.user_column_id
2094 and purf.user_row_id=pucif.user_row_id
2095 and p_date_paid between purf.effective_start_date and purf.effective_end_date
2096 and p_date_paid between pucif.effective_start_date and pucif.effective_end_date
2097 and to_number(to_char(p_date_of_birth,'YYYYMMDD')) >= to_number(purf.row_low_range_or_name)
2098 and to_number(to_char(p_date_of_birth,'YYYYMMDD')) <= to_number(purf.row_high_range);
2099
2100 l_procedure varchar2(80);
2101 ln_prev_age Number;
2102 BEGIN
2103
2104 l_procedure := 'get_prev_age';
2105 IF g_debug THEN
2106 hr_utility.set_location(' '||l_procedure, 10);
2107 hr_utility.set_location('IN p_date_of_birth ' ||p_date_of_birth,10);
2108 hr_utility.set_location('IN p_date_paid ' ||p_date_paid,10);
2109 END IF;
2110
2111 ln_prev_age :=0;
2112
2113 open csr_get_prev_age;
2114 fetch csr_get_prev_age into ln_prev_age;
2115 close csr_get_prev_age;
2116
2117 IF g_debug THEN
2118 hr_utility.set_location('RETURN ln_prev_age '||ln_prev_age,10);
2119 END IF;
2120
2121 RETURN ln_prev_age;
2122
2123 END get_prev_age;
2124
2125 --------------------------------------------------
2126 --
2127 -- Function au_check_trans Bug 6192381
2128 --
2129 -- Check if there exists a Transitional ETP or not in the current Pay Period .
2130 --
2131 -- RETURNS: Preservation Age
2132 -- IN : Assignment Id
2133 -- : Date Earned
2134 FUNCTION au_check_trans
2135 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
2136 p_date_earned IN DATE
2137 )
2138 RETURN VARCHAR2 IS
2139
2140 cursor csr_check_trans is
2141 select 1
2142 from pay_element_types_f pet,
2143 pay_input_values_f piv,
2144 pay_element_entries_f pee,
2145 pay_element_entry_values_f peev
2146 where pee.assignment_id = p_assignment_id
2147 and pet.element_type_id = piv.element_type_id
2148 and pet.element_name = 'ETP on Termination'
2149 and piv.name = 'Transitional ETP'
2150 and pet.element_type_id = pee.element_type_id
2151 and pee.element_entry_id = peev.element_entry_id
2152 and piv.input_value_id = peev.input_value_id
2153 and peev.screen_entry_value = 'Y'
2154 and p_date_earned between pee.effective_start_date and pee.effective_end_date
2155 and p_date_earned between peev.effective_start_date and peev.effective_end_date
2156 and p_date_earned between pet.effective_start_date and pet.effective_end_date
2157 and p_date_earned between peev.effective_start_date and peev.effective_end_date;
2158
2159 l_procedure varchar2(80);
2160 lv_check_trans varchar2(1);
2161 ln_tmp number;
2162 BEGIN
2163
2164 l_procedure := 'au_check_trans';
2165 IF g_debug THEN
2166 hr_utility.set_location(' '||l_procedure, 10);
2167 hr_utility.set_location('IN p_assignment_id ' ||p_assignment_id,10);
2168 hr_utility.set_location('IN p_date_earned ' ||p_date_earned,10);
2169 END IF;
2170
2171 lv_check_trans :='N';
2172 ln_tmp := 0 ;
2173
2174 open csr_check_trans;
2175 fetch csr_check_trans into ln_tmp;
2176 if csr_check_trans%found then
2177 lv_check_trans :='Y';
2178 else
2179 lv_check_trans :='N';
2180 end if;
2181 close csr_check_trans;
2182
2183 IF g_debug THEN
2184 hr_utility.set_location('RETURN lv_check_trans '||lv_check_trans,10);
2185 END IF;
2186
2187 RETURN lv_check_trans;
2188
2189 END au_check_trans;
2190
2191 function calculate_etp_lumpsum_d_2010
2192 (p_assignment_id in number
2193 ,p_tax_unit_id in number
2194 ,p_assignment_action_id in number
2195 ,p_date_earned in date
2196 ,p_date_paid in date
2197 ,p_years_of_service in number
2198 ,p_lump_d_tax_free in number
2199 ,p_lump_d_service_increment in number
2200 ,p_etp_all_pay_ytd in number
2201 ,p_all_etp_payments in number
2202 )
2203 return number is
2204
2205 cursor csr_get_etp_entry is
2206 select peev.screen_entry_value, count(peev.screen_entry_value) etp_no
2207 from pay_element_types_f pet,
2208 pay_input_values_f piv,
2209 pay_element_entries_f pee,
2210 pay_element_entry_values_f peev
2211 where pee.assignment_id = p_assignment_id
2212 and pet.element_type_id = piv.element_type_id
2213 and pet.element_name = 'ETP on Termination'
2214 and pet.legislation_code = 'AU'
2215 and piv.name = 'Transitional ETP'
2216 and pet.element_type_id = pee.element_type_id
2217 and pee.element_entry_id = peev.element_entry_id
2218 and piv.input_value_id = peev.input_value_id
2219 and p_date_earned between pee.effective_start_date and pee.effective_end_date
2220 and p_date_earned between peev.effective_start_date and peev.effective_end_date
2221 and p_date_earned between pet.effective_start_date and pet.effective_end_date
2222 and p_date_earned between piv.effective_start_date and piv.effective_end_date
2223 and not exists ( select 1 from pay_run_results prr
2224 where prr.element_entry_id = pee.element_entry_id
2225 and prr.element_type_id = pet.element_type_id
2226 and prr.element_type_id = pee.element_type_id
2227 and prr.assignment_action_id <> p_assignment_action_id)
2228 group by peev.screen_entry_value;
2229
2230 cursor csr_get_curr_atd is
2231 select pps.actual_termination_date
2232 from per_periods_of_service pps, per_all_assignments_f paaf
2233 where paaf.assignment_id = p_assignment_id
2234 and p_date_paid between paaf.effective_start_date and paaf.effective_end_date
2235 and paaf.period_of_service_id = pps.period_of_service_id ;
2236
2237 cursor c_max_asg_action_id (c_year_start date
2238 ,c_year_end date) is
2239 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
2240 from pay_assignment_actions paa,
2241 pay_payroll_actions ppa,
2242 per_assignments_f paf
2243 where paa.assignment_id = paf.assignment_id
2244 and paf.assignment_id = p_assignment_id
2245 and ppa.payroll_action_id = paa.payroll_action_id
2246 and ppa.effective_date between c_year_start and c_year_end
2247 and ppa.payroll_id = paf.payroll_id
2248 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
2249 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
2250 and paa.action_status = 'C'
2251 and paa.tax_unit_id = p_tax_unit_id;
2252
2253 cursor csr_get_bal_value (c_balance_name varchar2,
2254 c_max_assignment_action_id pay_assignment_actions.assignment_action_id%type) IS
2255 SELECT nvl(pay_balance_pkg.get_value(pdb.defined_balance_id
2256 ,c_max_assignment_action_id
2257 ,p_tax_unit_id
2258 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL),0)
2259 FROM pay_balance_types pbt,
2260 pay_defined_balances pdb,
2261 pay_balance_dimensions pbd
2262 WHERE pbt.legislation_code = 'AU'
2263 AND pbt.balance_name = c_balance_name
2264 AND pbt.balance_type_id = pdb.balance_type_id
2265 AND pbd.balance_dimension_id = pdb.balance_dimension_id
2266 AND pbd.dimension_name = '_ASG_LE_YTD';
2267
2268
2269 l_procedure constant varchar2(100) := g_package||'calculate_etp_lumpsum_d_2010';
2270 l_max_lump_d number;
2271 l_acutal_termination_date date;
2272 l_year_start_atd date;
2273 l_year_end_atd date;
2274 l_year_start_paid date;
2275 l_year_end_paid date;
2276 l_count_year number;
2277 l_max_assignment_action_id pay_assignment_actions.assignment_action_id%type;
2278 l_add number := 0;
2279 l_prev_etp number := 0;
2280 l_tot_prev_etp number := 0;
2281 l_max number := 0;
2282
2283 l_count number := 0;
2284 l_case boolean := FALSE ;
2285 l_lump_sum_d number := 0;
2286
2287
2288 begin
2289 --
2290 IF g_debug THEN
2291 hr_utility.set_location(l_procedure, 1);
2292 hr_utility.set_location('p_assignment_id '||p_assignment_id, 1);
2293 hr_utility.set_location('p_tax_unit_id '||p_tax_unit_id, 1);
2294 hr_utility.set_location('p_assignment_action_id '||p_assignment_action_id, 1);
2295 hr_utility.set_location('p_date_earned '||p_date_earned, 1);
2296 hr_utility.set_location('p_date_paid '||p_date_paid, 1);
2297 hr_utility.set_location('p_years_of_service '||p_years_of_service, 1);
2298 hr_utility.set_location('p_lump_d_tax_free '||p_lump_d_tax_free, 1);
2299 hr_utility.set_location('p_lump_d_service_increment '||p_lump_d_service_increment, 1);
2300 hr_utility.set_location('p_etp_all_pay_ytd '||p_etp_all_pay_ytd, 1);
2301 hr_utility.set_location('p_all_etp_payments '||p_all_etp_payments, 1);
2302 END IF;
2303
2304 l_max_lump_d := p_lump_d_tax_free + p_lump_d_service_increment * p_years_of_service;
2305
2306 IF g_debug THEN
2307 hr_utility.set_location('l_max_lump_d '||l_max_lump_d, 10);
2308 END IF;
2309
2310 FOR rec in csr_get_etp_entry LOOP
2311 l_count := l_count + rec.etp_no;
2312 if l_count <> rec.etp_no then
2313 l_case := TRUE ;
2314 end if;
2315 END LOOP;
2316
2317 IF l_case THEN
2318 hr_utility.set_location('l_case is TRUE', 10);
2319 END IF;
2320
2321 open csr_get_curr_atd;
2322 fetch csr_get_curr_atd into l_acutal_termination_date;
2323 close csr_get_curr_atd;
2324
2325 l_year_start_atd := hr_au_routes.span_start(l_acutal_termination_date, 1, '01-07-');
2326 l_year_end_atd := add_months(l_year_start_atd,12) - 1;
2327 l_year_start_paid := hr_au_routes.span_start(p_date_paid, 1, '01-07-');
2328 l_year_end_paid := add_months(l_year_start_paid,12) - 1;
2329
2330 IF g_debug THEN
2331 hr_utility.set_location('l_year_start_atd '||l_year_start_atd, 20);
2332 hr_utility.set_location('l_year_end_atd '||l_year_end_atd, 20);
2333 hr_utility.set_location('l_year_start_paid '||l_year_start_paid, 20);
2334 hr_utility.set_location('l_year_end_paid '||l_year_end_paid, 20);
2335 END IF;
2336
2337 IF l_year_end_atd = l_year_end_paid THEN -- paid in the termination year
2338
2339 IF p_etp_all_pay_ytd <> 0 THEN
2340 IF p_etp_all_pay_ytd < l_max_lump_d THEN
2341
2342 IF not l_case THEN
2343 l_max := l_max_lump_d - p_etp_all_pay_ytd;
2344 ELSE
2345
2346 if nvl(pay_au_paye_ff.g_lumpsum_d,0) = 0 then
2347 l_max := l_max_lump_d - p_etp_all_pay_ytd;
2348 else
2349
2350 IF g_debug THEN
2351 hr_utility.set_location('retrieving g_lumpsum_d '||pay_au_paye_ff.g_lumpsum_d, 30);
2352 END IF;
2353
2354 IF (pay_au_paye_ff.g_lumpsum_d + p_etp_all_pay_ytd) < l_max_lump_d THEN
2355 l_max := l_max_lump_d-(pay_au_paye_ff.g_lumpsum_d+p_etp_all_pay_ytd);
2356 ELSE
2357 l_max := 0;
2358 END IF;
2359
2360 end if;
2361
2362 pay_au_paye_ff.g_lumpsum_d := nvl(pay_au_paye_ff.g_lumpsum_d,0) + least(l_max, p_all_etp_payments);
2363
2364 IF g_debug THEN
2365 hr_utility.set_location('setting g_lumpsum_d '||pay_au_paye_ff.g_lumpsum_d, 30);
2366 END IF;
2367
2368 END IF;
2369
2370 ELSE
2371 l_max := 0;
2372 END IF;
2373
2374 ELSE -- the 1st etp payment
2375
2376 IF not l_case THEN
2377 l_max := l_max_lump_d ;
2378 ELSE
2379
2380 if nvl(pay_au_paye_ff.g_lumpsum_d,0) = 0 then
2381 l_max := l_max_lump_d;
2382 else
2383 IF g_debug THEN
2384 hr_utility.set_location('retrieving g_lumpsum_d '||pay_au_paye_ff.g_lumpsum_d, 40);
2385 END IF;
2386
2387 IF pay_au_paye_ff.g_lumpsum_d < l_max_lump_d THEN
2388 l_max := l_max_lump_d - pay_au_paye_ff.g_lumpsum_d;
2389 ELSE
2390 l_max := 0;
2391 END IF;
2392
2393 end if;
2394
2395 pay_au_paye_ff.g_lumpsum_d := nvl(pay_au_paye_ff.g_lumpsum_d,0) + least(l_max, p_all_etp_payments);
2396
2397 IF g_debug THEN
2398 hr_utility.set_location('setting g_lumpsum_d '||pay_au_paye_ff.g_lumpsum_d, 40);
2399 END IF;
2400
2401 END IF;
2402 END IF;
2403
2404 ELSE -- paid in a year after the termination
2405
2406 select months_between(l_year_end_paid, l_year_end_atd)/12
2407 into l_count_year
2408 from dual;
2409
2410 for i in 0 .. (l_count_year-1) loop
2411
2412 open c_max_asg_action_id (add_months(l_year_start_atd,l_add), add_months(l_year_end_atd,l_add));
2413 fetch c_max_asg_action_id into l_max_assignment_action_id;
2414 close c_max_asg_action_id;
2415
2416 open csr_get_bal_value ('ETP All Payments', l_max_assignment_action_id);
2417 fetch csr_get_bal_value into l_prev_etp;
2418 close csr_get_bal_value;
2419
2420 l_tot_prev_etp := l_tot_prev_etp + l_prev_etp;
2421
2422 IF g_debug THEN
2423 hr_utility.set_location('l_add '||l_add, 50);
2424 hr_utility.set_location('l_max_assignment_action_id '||l_max_assignment_action_id, 50);
2425 hr_utility.set_location('l_prev_etp '||l_prev_etp, 50);
2426 hr_utility.set_location('l_tot_prev_etp '||l_tot_prev_etp, 50);
2427 END IF;
2428
2429 l_add := l_add + 12;
2430
2431 end loop;
2432
2433 l_tot_prev_etp:= l_tot_prev_etp + p_etp_all_pay_ytd;
2434
2435
2436 IF l_tot_prev_etp <> 0 THEN
2437 IF l_tot_prev_etp < l_max_lump_d THEN
2438
2439 IF not l_case THEN
2440 l_max := l_max_lump_d - l_tot_prev_etp;
2441
2442 ELSE
2443
2444 if nvl(pay_au_paye_ff.g_lumpsum_d,0) = 0 then
2445 l_max := l_max_lump_d - l_tot_prev_etp;
2446 else
2447
2448 IF g_debug THEN
2449 hr_utility.set_location('retrieving g_lumpsum_d '||pay_au_paye_ff.g_lumpsum_d, 60);
2450 END IF;
2451
2452 IF (pay_au_paye_ff.g_lumpsum_d + l_tot_prev_etp) < l_max_lump_d THEN
2453 l_max := l_max_lump_d-(pay_au_paye_ff.g_lumpsum_d+l_tot_prev_etp);
2454 ELSE
2455 l_max := 0;
2456 END IF;
2457
2458 end if;
2459
2460 pay_au_paye_ff.g_lumpsum_d := nvl(pay_au_paye_ff.g_lumpsum_d,0) + least(l_max, p_all_etp_payments);
2461
2462 IF g_debug THEN
2463 hr_utility.set_location('setting g_lumpsum_d '||pay_au_paye_ff.g_lumpsum_d, 60);
2464 END IF;
2465
2466 END IF;
2467
2468 ELSE
2469
2470 l_max := 0;
2471
2472 END IF;
2473
2474 ELSE -- the 1st etp payment after termination
2475
2476 IF not l_case THEN
2477 l_max := l_max_lump_d;
2478 ELSE
2479
2480 if nvl(pay_au_paye_ff.g_lumpsum_d,0) = 0 then
2481 l_max := l_max_lump_d;
2482 else
2483
2484 IF g_debug THEN
2485 hr_utility.set_location('retrieving g_lumpsum_d '||pay_au_paye_ff.g_lumpsum_d, 70);
2486 END IF;
2487
2488 IF pay_au_paye_ff.g_lumpsum_d < l_max_lump_d THEN
2489 l_max := l_max_lump_d - pay_au_paye_ff.g_lumpsum_d;
2490 ELSE
2491 l_max := 0;
2492 END IF;
2493 end if;
2494
2495 pay_au_paye_ff.g_lumpsum_d := nvl(pay_au_paye_ff.g_lumpsum_d,0) + least(l_max, p_all_etp_payments);
2496
2497 IF g_debug THEN
2498 hr_utility.set_location('setting g_lumpsum_d '||pay_au_paye_ff.g_lumpsum_d, 70);
2499 END IF;
2500
2501 END IF;
2502
2503 END IF;
2504
2505 END IF;
2506
2507 l_lump_sum_d := least(l_max, p_all_etp_payments);
2508
2509 IF g_debug THEN
2510 hr_utility.set_location('l_max '||l_max, 90);
2511 hr_utility.set_location('l_lump_sum_d '||l_lump_sum_d, 90);
2512 END IF;
2513
2514 return l_lump_sum_d;
2515
2516 end calculate_etp_lumpsum_d_2010;
2517
2518 FUNCTION calculate_etp_tax_2010
2519 (p_business_group_id IN NUMBER
2520 ,p_assignment_id IN NUMBER
2521 ,p_tax_unit_id IN NUMBER
2522 ,p_date_paid IN DATE
2523 ,p_trans_etp IN VARCHAR2
2524 ,p_death_benefit_type IN VARCHAR2
2525 ,p_over_pres_age IN VARCHAR2
2526 ,p_tfn_for_non_dependent IN VARCHAR2
2527 ,p_medicare_levy IN NUMBER
2528 ,p_etp_notran_noppt_ytd in NUMBER
2529 ,p_etp_notran_ppt_ytd in NUMBER
2530 ,p_etp_tran_noppt_ytd in NUMBER
2531 ,p_etp_tran_ppt_ytd in NUMBER
2532 ,p_etp_notran_noppt_run in NUMBER
2533 ,p_etp_notran_ppt_run in NUMBER
2534 ,p_etp_tran_noppt_run in NUMBER
2535 ,p_etp_tran_ppt_run in NUMBER
2536 ,p_etp_tax_notran_noppt OUT NOCOPY NUMBER
2537 ,p_etp_tax_notran_ppt OUT NOCOPY NUMBER
2538 ,p_etp_tax_tran_noppt OUT NOCOPY NUMBER
2539 ,p_etp_tax_tran_ppt OUT NOCOPY NUMBER
2540 )
2541 RETURN NUMBER IS
2542
2543 /*This Cursor is used to compute the ETP tax amount based on the ETP Amount and User table
2544 As its a Slab based taxation , Slabs in which the amount is coming and all lower slabs
2545 needs to be taken into consideration
2546
2547 "and p_amount > purf.row_low_range_or_name "
2548
2549 The least of ETP Amount and Higher range is taken
2550 "to_number(purf.row_high_range),p_amount)"
2551
2552 and then the difference with the lower range, This Difference is then multiplied by the Tax Percentage + Medicare Levy
2553
2554 The Summation of all these values is the Net ETP Tax
2555
2556 Bug 6430072 - Added condition to convert User Table values to number using fnd_number.canonical_to_number
2557 and not to_number.
2558 */
2559
2560 CURSOR csr_get_etp_tax(p_bus_grp_id IN hr_all_organization_units.organization_id%TYPE
2561 ,p_date_paid IN DATE
2562 ,p_user_table IN VARCHAR2
2563 ,p_amount IN NUMBER
2564 ,p_med_levy IN NUMBER) IS
2565 SELECT /*+ INDEX(PURF) ORDERED */ NVL(SUM(round((least(fnd_number.canonical_to_number(purf.row_high_range),p_amount)-fnd_number.canonical_to_number(purf.row_low_range_or_name))
2566 *(fnd_number.canonical_to_number(pucif.value) + decode(fnd_number.canonical_to_number(pucif.value),0,0,p_med_levy)))),0) -- bug8647962
2567 FROM pay_user_tables put,
2568 pay_user_rows_F purf,
2569 pay_user_columns puc,
2570 pay_user_column_instances_f pucif
2571 where put.legislation_code='AU'
2572 and put.user_table_name=p_user_table
2573 and put.user_table_id=purf.user_table_id
2574 and put.user_table_id=puc.user_table_id
2575 and puc.user_column_id=pucif.user_column_id
2576 and purf.user_row_id=pucif.user_row_id
2577 and p_date_paid between purf.effective_start_date and purf.effective_end_date
2578 and p_date_paid between pucif.effective_start_date and pucif.effective_end_date
2579 and p_amount > fnd_number.canonical_to_number(purf.row_low_range_or_name) /* 6430072 */
2580 order by fnd_number.canonical_to_number(purf.row_low_range_or_name);
2581
2582
2583 cursor csr_get_curr_atd is
2584 select pps.actual_termination_date
2585 from per_periods_of_service pps, per_all_assignments_f paaf
2586 where paaf.assignment_id = p_assignment_id
2587 and p_date_paid between paaf.effective_start_date and paaf.effective_end_date
2588 and paaf.period_of_service_id = pps.period_of_service_id ;
2589
2590 cursor c_max_asg_action_id (c_year_start date
2591 ,c_year_end date) is
2592 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
2593 from pay_assignment_actions paa,
2594 pay_payroll_actions ppa,
2595 per_assignments_f paf
2596 where paa.assignment_id = paf.assignment_id
2597 and paf.assignment_id = p_assignment_id
2598 and ppa.payroll_action_id = paa.payroll_action_id
2599 and ppa.effective_date between c_year_start and c_year_end
2600 and ppa.payroll_id = paf.payroll_id
2601 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
2602 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
2603 and paa.action_status = 'C'
2604 and paa.tax_unit_id = p_tax_unit_id;
2605
2606 cursor csr_get_bal_value (c_balance_name varchar2,
2607 c_max_assignment_action_id pay_assignment_actions.assignment_action_id%type) IS
2608 SELECT nvl(pay_balance_pkg.get_value(pdb.defined_balance_id
2609 ,c_max_assignment_action_id
2610 ,p_tax_unit_id
2611 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL),0)
2612 FROM pay_balance_types pbt,
2613 pay_defined_balances pdb,
2614 pay_balance_dimensions pbd
2615 WHERE pbt.legislation_code = 'AU'
2616 AND pbt.balance_name = c_balance_name
2617 AND pbt.balance_type_id = pdb.balance_type_id
2618 AND pbd.balance_dimension_id = pdb.balance_dimension_id
2619 AND pbd.dimension_name = '_ASG_LE_YTD';
2620
2621
2622 l_procedure VARCHAR2(80);
2623 ld_user_table VARCHAR2(50);
2624 ln_user_table VARCHAR2(50);
2625 lt_user_table VARCHAR2(50);
2626 lv_medicare_levy NUMBER := 0;
2627
2628 l_acutal_termination_date date;
2629 l_year_start_atd date;
2630 l_year_end_atd date;
2631 l_year_start_paid date;
2632 l_year_end_paid date;
2633 l_count_year number := 0;
2634 l_add number := 0;
2635 l_tot_prev_etp number:= 0;
2636 l_max_assignment_action_id pay_assignment_actions.assignment_action_id%type;
2637
2638
2639 l_etp_tax_notran_noppt_ytd number;
2640 l_etp_tax_notran_ppt_ytd number;
2641 l_etp_tax_tran_ppt_ytd number;
2642
2643 l_etp_tax_notran_noppt_prev number;
2644 l_etp_tax_notran_ppt_prev number;
2645 l_etp_tax_tran_ppt_prev number;
2646
2647 l_notran_noppt_prev number;
2648 l_notran_ppt_prev number;
2649 l_tran_noppt_prev number;
2650 l_tran_ppt_prev number;
2651
2652 l_etp_tax_notran_noppt number := 0;
2653 l_etp_tax_notran_ppt number := 0;
2654 l_etp_tax_tran_noppt number := 0;
2655 l_etp_tax_tran_ppt number := 0;
2656
2657
2658 BEGIN
2659
2660 l_procedure := 'calculate_etp_tax_2010';
2661
2662 IF g_debug THEN
2663 hr_utility.set_location('Entering '||l_procedure, 10);
2664 hr_utility.set_location('IN p_business_group_id ' ||p_business_group_id,10);
2665 hr_utility.set_location('IN p_assignment_id ' ||p_assignment_id,10);
2666 hr_utility.set_location('IN p_tax_unit_id ' ||p_tax_unit_id,10);
2667 hr_utility.set_location('IN p_date_paid ' ||p_date_paid,10);
2668 hr_utility.set_location('IN p_trans_etp ' ||p_trans_etp,10);
2669 hr_utility.set_location('IN p_death_benefit_type ' ||p_death_benefit_type,10);
2670 hr_utility.set_location('IN p_over_pres_age ' ||p_over_pres_age,10);
2671 hr_utility.set_location('IN p_tfn_for_non_dependent ' ||p_tfn_for_non_dependent,10);
2672 hr_utility.set_location('IN p_medicare_levy ' ||p_medicare_levy,10);
2673 hr_utility.set_location('IN p_etp_notran_noppt_ytd ' ||p_etp_notran_noppt_ytd,10);
2674 hr_utility.set_location('IN p_etp_notran_ppt_ytd ' ||p_etp_notran_ppt_ytd,10);
2675 hr_utility.set_location('IN p_etp_tran_noppt_ytd ' ||p_etp_tran_noppt_ytd,10);
2676 hr_utility.set_location('IN p_etp_tran_ppt_ytd ' ||p_etp_tran_ppt_ytd,10);
2677 hr_utility.set_location('IN p_etp_notran_noppt_run ' ||p_etp_notran_noppt_run,10);
2678 hr_utility.set_location('IN p_etp_notran_ppt_run ' ||p_etp_notran_ppt_run,10);
2679 hr_utility.set_location('IN p_etp_tran_noppt_run ' ||p_etp_tran_noppt_run,10);
2680 hr_utility.set_location('IN p_etp_tran_ppt_run ' ||p_etp_tran_ppt_run,10);
2681 END IF;
2682
2683 lv_medicare_levy := p_medicare_levy;
2684
2685
2686 IF p_trans_etp='D' THEN
2687 IF p_death_benefit_type = 'D' THEN
2688 ld_user_table := 'TAX_SCALE_ETP_DEATH_DEPENDENT';
2689 ELSE
2690 ld_user_table := 'TAX_SCALE_ETP_DEATH_NON_DEPENDENT';
2691 IF p_tfn_for_non_dependent ='N' THEN
2692 lv_medicare_levy:=0;
2693 END IF;
2694 END IF;
2695
2696 if g_debug then hr_utility.set_location('death user table '||ld_user_table, 1); end if;
2697
2698 ELSE
2699
2700 IF (p_etp_notran_noppt_run+p_etp_notran_ppt_run) <> 0 THEN
2701 IF p_over_pres_age ='N' THEN
2702 ln_user_table := 'TAX_SCALE_NON_TRANS_UNDER_PREV_AGE';
2703 ELSIF p_over_pres_age ='Y' THEN
2704 ln_user_table := 'TAX_SCALE_NON_TRANS_OVER_PREV_AGE';
2705 END IF;
2706
2707 if g_debug then hr_utility.set_location('notran user table '||ln_user_table, 1); end if;
2708
2709 END IF;
2710
2711 IF (p_etp_tran_noppt_run+p_etp_tran_ppt_run) <> 0 THEN
2712 IF p_over_pres_age ='N' THEN
2713 lt_user_table := 'TAX_SCALE_TRANS_UNDER_PREV_AGE';
2714 ELSIF p_over_pres_age ='Y' THEN
2715 lt_user_table := 'TAX_SCALE_TRANS_OVER_PREV_AGE';
2716 END IF;
2717
2718 if g_debug then hr_utility.set_location('tran user table '||lt_user_table, 1); end if;
2719
2720 END IF;
2721
2722 END IF;
2723
2724 open csr_get_curr_atd;
2725 fetch csr_get_curr_atd into l_acutal_termination_date;
2726 close csr_get_curr_atd;
2727
2728 l_year_start_atd := hr_au_routes.span_start(l_acutal_termination_date, 1, '01-07-');
2729 l_year_end_atd := add_months(l_year_start_atd,12) - 1;
2730 l_year_start_paid := hr_au_routes.span_start(p_date_paid, 1, '01-07-');
2731 l_year_end_paid := add_months(l_year_start_paid,12) - 1;
2732
2733 IF g_debug THEN
2734 hr_utility.set_location('l_year_start_atd '||l_year_start_atd, 30);
2735 hr_utility.set_location('l_year_end_atd '||l_year_end_atd, 30);
2736 hr_utility.set_location('l_year_start_paid '||l_year_start_paid, 30);
2737 hr_utility.set_location('l_year_end_paid '||l_year_end_paid, 30);
2738 END IF;
2739
2740 IF l_year_end_atd = l_year_end_paid THEN
2741
2742 IF p_trans_etp='D' THEN
2743
2744 IF p_etp_notran_noppt_run <> 0 and p_etp_notran_noppt_run = p_etp_notran_noppt_ytd THEN
2745
2746 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ld_user_table,p_etp_notran_noppt_run,lv_medicare_levy);
2747 FETCH csr_get_etp_tax into l_etp_tax_notran_noppt;
2748 CLOSE csr_get_etp_tax;
2749
2750 if g_debug then hr_utility.set_location('l_etp_tax_notran_noppt '||l_etp_tax_notran_noppt, 41); end if;
2751
2752 ELSIF p_etp_notran_noppt_run <> 0 and p_etp_notran_noppt_run < p_etp_notran_noppt_ytd THEN
2753
2754 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ld_user_table,p_etp_notran_noppt_ytd,lv_medicare_levy);
2755 FETCH csr_get_etp_tax into l_etp_tax_notran_noppt_ytd;
2756 CLOSE csr_get_etp_tax;
2757
2758 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ld_user_table,(p_etp_notran_noppt_ytd-p_etp_notran_noppt_run),lv_medicare_levy);
2759 FETCH csr_get_etp_tax into l_etp_tax_notran_noppt_prev;
2760 CLOSE csr_get_etp_tax;
2761
2762 IF g_debug THEN
2763 hr_utility.set_location('l_etp_tax_notran_noppt_ytd '||l_etp_tax_notran_noppt_ytd, 42);
2764 hr_utility.set_location('l_etp_tax_notran_noppt_prev '||l_etp_tax_notran_noppt_prev, 42);
2765 END IF;
2766
2767 l_etp_tax_notran_noppt := l_etp_tax_notran_noppt_ytd - l_etp_tax_notran_noppt_prev;
2768
2769 END IF;
2770
2771 ELSE -- Non Death type
2772
2773 IF p_etp_notran_noppt_run + p_etp_notran_ppt_run <> 0 THEN
2774 IF p_etp_notran_noppt_run <> 0 THEN
2775
2776 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,p_etp_notran_noppt_run,lv_medicare_levy);
2777 FETCH csr_get_etp_tax into l_etp_tax_notran_noppt;
2778 CLOSE csr_get_etp_tax;
2779
2780 if g_debug then hr_utility.set_location('l_etp_tax_notran_noppt '||l_etp_tax_notran_noppt, 51); end if;
2781
2782 ELSIF p_etp_notran_ppt_run <> 0 THEN
2783
2784 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,(p_etp_notran_noppt_ytd+p_etp_notran_ppt_ytd),lv_medicare_levy);
2785 FETCH csr_get_etp_tax into l_etp_tax_notran_ppt_ytd;
2786 CLOSE csr_get_etp_tax;
2787
2788 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,(p_etp_notran_noppt_ytd+p_etp_notran_ppt_ytd-p_etp_notran_ppt_run),lv_medicare_levy);
2789 FETCH csr_get_etp_tax into l_etp_tax_notran_ppt_prev;
2790 CLOSE csr_get_etp_tax;
2791
2792 IF g_debug THEN
2793 hr_utility.set_location('l_etp_tax_notran_ppt_ytd '||l_etp_tax_notran_ppt_ytd, 52);
2794 hr_utility.set_location('l_etp_tax_notran_ppt_prev '||l_etp_tax_notran_ppt_prev, 52);
2795 END IF;
2796
2797 l_etp_tax_notran_ppt := l_etp_tax_notran_ppt_ytd - l_etp_tax_notran_ppt_prev;
2798
2799 END IF;
2800 END IF; -- Notran
2801
2802 IF p_etp_tran_noppt_run + p_etp_tran_ppt_run <> 0 THEN
2803 IF p_etp_tran_noppt_run <> 0 THEN
2804
2805 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,lt_user_table,p_etp_tran_noppt_run,lv_medicare_levy);
2806 FETCH csr_get_etp_tax into l_etp_tax_tran_noppt;
2807 CLOSE csr_get_etp_tax;
2808
2809 if g_debug then hr_utility.set_location('l_etp_tax_tran_noppt '||l_etp_tax_tran_noppt, 61); end if;
2810
2811 ELSIF p_etp_tran_ppt_run <> 0 THEN
2812
2813 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,lt_user_table,(p_etp_tran_noppt_ytd+p_etp_tran_ppt_ytd),lv_medicare_levy);
2814 FETCH csr_get_etp_tax into l_etp_tax_tran_ppt_ytd;
2815 CLOSE csr_get_etp_tax;
2816
2817 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,lt_user_table,(p_etp_tran_noppt_ytd+p_etp_tran_ppt_ytd-p_etp_tran_ppt_run),lv_medicare_levy);
2818 FETCH csr_get_etp_tax into l_etp_tax_tran_ppt_prev;
2819 CLOSE csr_get_etp_tax;
2820
2821 IF g_debug THEN
2822 hr_utility.set_location('l_etp_tax_tran_ppt_ytd '||l_etp_tax_tran_ppt_ytd, 62);
2823 hr_utility.set_location('l_etp_tax_tran_ppt_prev '||l_etp_tax_tran_ppt_prev, 62);
2824 END IF;
2825
2826 l_etp_tax_tran_ppt := l_etp_tax_tran_ppt_ytd - l_etp_tax_tran_ppt_prev;
2827
2828 END IF;
2829 END IF; -- Tran
2830
2831
2832 END IF;
2833
2834 ELSE -- l_year_end_atd <> l_year_end_paid
2835
2836 select months_between(l_year_end_paid, l_year_end_atd)/12
2837 into l_count_year
2838 from dual;
2839
2840 for i in 0 .. (l_count_year-1) loop -- until the current financial year
2841
2842 open c_max_asg_action_id (add_months(l_year_start_atd,l_add), add_months(l_year_end_atd,l_add));
2843 fetch c_max_asg_action_id into l_max_assignment_action_id;
2844 close c_max_asg_action_id;
2845
2846 if p_trans_etp='D' then
2847 open csr_get_bal_value ('ETP Taxable Payments Life Benefit Not Part of Prev Term', l_max_assignment_action_id);
2848 fetch csr_get_bal_value into l_notran_noppt_prev;
2849 close csr_get_bal_value;
2850
2851 IF g_debug THEN
2852 hr_utility.set_location('l_notran_noppt_prev '||l_notran_noppt_prev, 65);
2853 END IF;
2854
2855 l_tot_prev_etp := l_tot_prev_etp + l_notran_noppt_prev;
2856
2857 else
2858 if p_etp_notran_ppt_run <> 0 then
2859 open csr_get_bal_value ('ETP Taxable Payments Life Benefit Not Part of Prev Term', l_max_assignment_action_id);
2860 fetch csr_get_bal_value into l_notran_noppt_prev;
2861 close csr_get_bal_value;
2862
2863 open csr_get_bal_value ('ETP Taxable Payments Life Benefit Part of Prev Term', l_max_assignment_action_id);
2864 fetch csr_get_bal_value into l_notran_ppt_prev;
2865 close csr_get_bal_value;
2866
2867 IF g_debug THEN
2868 hr_utility.set_location('l_notran_noppt_prev '||l_notran_noppt_prev, 65);
2869 hr_utility.set_location('l_notran_ppt_prev '||l_notran_ppt_prev, 65);
2870 END IF;
2871
2872 l_tot_prev_etp := l_tot_prev_etp + l_notran_noppt_prev + l_notran_ppt_prev ;
2873 end if;
2874
2875 if p_etp_tran_ppt_run <> 0 then
2876 open csr_get_bal_value ('ETP Taxable Payments Transitional Not Part of Prev Term', l_max_assignment_action_id);
2877 fetch csr_get_bal_value into l_tran_noppt_prev;
2878 close csr_get_bal_value;
2879
2880 open csr_get_bal_value ('ETP Taxable Payments Transitional Part of Prev Term', l_max_assignment_action_id);
2881 fetch csr_get_bal_value into l_tran_ppt_prev;
2882 close csr_get_bal_value;
2883
2884 IF g_debug THEN
2885 hr_utility.set_location('l_tran_noppt_prev '||l_tran_noppt_prev, 65);
2886 hr_utility.set_location('l_tran_ppt_prev '||l_tran_ppt_prev, 65);
2887 END IF;
2888
2889 l_tot_prev_etp := l_tot_prev_etp + l_tran_noppt_prev + l_tran_ppt_prev ;
2890 end if;
2891
2892 end if;
2893
2894 IF g_debug THEN
2895 hr_utility.set_location('l_add '||l_add, 65);
2896 hr_utility.set_location('l_max_assignment_action_id '||l_max_assignment_action_id, 65);
2897 hr_utility.set_location('l_tot_prev_etp '||l_tot_prev_etp, 65);
2898 END IF;
2899
2900 l_add := l_add + 12;
2901
2902 end loop;
2903
2904 IF p_trans_etp='D' THEN
2905
2906 IF p_etp_notran_noppt_run <> 0 and l_tot_prev_etp = 0 THEN
2907
2908 IF p_etp_notran_noppt_run = p_etp_notran_noppt_ytd THEN
2909
2910 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ld_user_table,p_etp_notran_noppt_run,lv_medicare_levy);
2911 FETCH csr_get_etp_tax into l_etp_tax_notran_noppt;
2912 CLOSE csr_get_etp_tax;
2913
2914 if g_debug then hr_utility.set_location('l_etp_tax_notran_noppt '||l_etp_tax_notran_noppt, 71); end if;
2915
2916 ELSE
2917
2918 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ld_user_table,(p_etp_notran_noppt_ytd),lv_medicare_levy);
2919 FETCH csr_get_etp_tax into l_etp_tax_notran_noppt_ytd;
2920 CLOSE csr_get_etp_tax;
2921
2922 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ld_user_table,(p_etp_notran_noppt_ytd-p_etp_notran_noppt_run),lv_medicare_levy);
2923 FETCH csr_get_etp_tax into l_etp_tax_notran_noppt_prev;
2924 CLOSE csr_get_etp_tax;
2925
2926 IF g_debug THEN
2927 hr_utility.set_location('l_etp_tax_notran_noppt_ytd '||l_etp_tax_notran_noppt_ytd, 72);
2928 hr_utility.set_location('l_etp_tax_notran_noppt_prev '||l_etp_tax_notran_noppt_prev, 72);
2929 END IF;
2930
2931 l_etp_tax_notran_noppt := l_etp_tax_notran_noppt_ytd - l_etp_tax_notran_noppt_prev;
2932
2933 END IF;
2934
2935 ELSIF p_etp_notran_noppt_run <> 0 and l_tot_prev_etp <> 0 THEN
2936
2937 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ld_user_table,(l_tot_prev_etp+p_etp_notran_noppt_ytd),lv_medicare_levy);
2938 FETCH csr_get_etp_tax into l_etp_tax_notran_noppt_ytd;
2939 CLOSE csr_get_etp_tax;
2940
2941 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ld_user_table,(l_tot_prev_etp+p_etp_notran_noppt_ytd-p_etp_notran_noppt_run),lv_medicare_levy);
2942 FETCH csr_get_etp_tax into l_etp_tax_notran_noppt_prev;
2943 CLOSE csr_get_etp_tax;
2944
2945 IF g_debug THEN
2946 hr_utility.set_location('l_etp_tax_notran_noppt_ytd '||l_etp_tax_notran_noppt_ytd, 73);
2947 hr_utility.set_location('l_etp_tax_notran_noppt_prev '||l_etp_tax_notran_noppt_prev, 73);
2948 END IF;
2949
2950 l_etp_tax_notran_noppt := l_etp_tax_notran_noppt_ytd - l_etp_tax_notran_noppt_prev;
2951
2952 END IF;
2953
2954 ELSE -- Non Death type
2955
2956 IF p_etp_notran_noppt_run + p_etp_notran_ppt_run <> 0 THEN
2957
2958 IF p_etp_notran_noppt_run <> 0 THEN
2959
2960 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,p_etp_notran_noppt_run,lv_medicare_levy);
2961 FETCH csr_get_etp_tax into l_etp_tax_notran_noppt;
2962 CLOSE csr_get_etp_tax;
2963
2964 if g_debug then hr_utility.set_location('l_etp_tax_notran_noppt '||l_etp_tax_notran_noppt, 81); end if;
2965
2966 ELSIF p_etp_notran_ppt_run <> 0 THEN
2967
2968 IF l_tot_prev_etp = 0 THEN
2969
2970 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,(p_etp_notran_noppt_ytd+p_etp_notran_ppt_ytd),lv_medicare_levy);
2971 FETCH csr_get_etp_tax into l_etp_tax_notran_ppt_ytd;
2972 CLOSE csr_get_etp_tax;
2973
2974 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,(p_etp_notran_noppt_ytd+p_etp_notran_ppt_ytd-p_etp_notran_ppt_run),lv_medicare_levy);
2975 FETCH csr_get_etp_tax into l_etp_tax_notran_ppt_prev;
2976 CLOSE csr_get_etp_tax;
2977
2978
2979 IF g_debug THEN
2980 hr_utility.set_location('l_etp_tax_notran_ppt_ytd '||l_etp_tax_notran_ppt_ytd, 82);
2981 hr_utility.set_location('l_etp_tax_notran_ppt_prev '||l_etp_tax_notran_ppt_prev, 82);
2982 END IF;
2983
2984 l_etp_tax_notran_ppt := l_etp_tax_notran_ppt_ytd - l_etp_tax_notran_ppt_prev;
2985
2986 ELSE
2987
2988 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,(l_tot_prev_etp+p_etp_notran_noppt_ytd+p_etp_notran_ppt_ytd),lv_medicare_levy);
2989 FETCH csr_get_etp_tax into l_etp_tax_notran_ppt_ytd;
2990 CLOSE csr_get_etp_tax;
2991
2992 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,(l_tot_prev_etp+p_etp_notran_noppt_ytd+p_etp_notran_ppt_ytd-p_etp_notran_ppt_run),lv_medicare_levy);
2993 FETCH csr_get_etp_tax into l_etp_tax_notran_ppt_prev;
2994 CLOSE csr_get_etp_tax;
2995
2996
2997 IF g_debug THEN
2998 hr_utility.set_location('l_etp_tax_notran_ppt_ytd '||l_etp_tax_notran_ppt_ytd, 83);
2999 hr_utility.set_location('l_etp_tax_notran_ppt_prev '||l_etp_tax_notran_ppt_prev, 83);
3000 END IF;
3001
3002 l_etp_tax_notran_ppt := l_etp_tax_notran_ppt_ytd - l_etp_tax_notran_ppt_prev;
3003
3004 END IF;
3005
3006 END IF;
3007
3008 END IF; -- Notran
3009
3010 IF p_etp_tran_noppt_run + p_etp_tran_ppt_run <> 0 THEN
3011
3012 IF p_etp_tran_noppt_run <> 0 THEN
3013
3014 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,lt_user_table,p_etp_tran_noppt_run,lv_medicare_levy);
3015 FETCH csr_get_etp_tax into l_etp_tax_tran_noppt;
3016 CLOSE csr_get_etp_tax;
3017
3018 if g_debug then hr_utility.set_location('l_etp_tax_tran_noppt '||l_etp_tax_tran_noppt, 91); end if;
3019
3020 ELSIF p_etp_tran_ppt_run <> 0 THEN
3021
3022 IF l_tot_prev_etp = 0 THEN
3023
3024 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,lt_user_table,(p_etp_tran_noppt_ytd+p_etp_tran_ppt_ytd),lv_medicare_levy);
3025 FETCH csr_get_etp_tax into l_etp_tax_tran_ppt_ytd;
3026 CLOSE csr_get_etp_tax;
3027
3028 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,lt_user_table,(p_etp_tran_noppt_ytd+p_etp_tran_ppt_ytd-p_etp_tran_ppt_run),lv_medicare_levy);
3029 FETCH csr_get_etp_tax into l_etp_tax_tran_ppt_prev;
3030 CLOSE csr_get_etp_tax;
3031
3032
3033 IF g_debug THEN
3034 hr_utility.set_location('l_etp_tax_tran_ppt_ytd '||l_etp_tax_tran_ppt_ytd, 92);
3035 hr_utility.set_location('l_etp_tax_tran_ppt_prev '||l_etp_tax_tran_ppt_prev, 92);
3036 END IF;
3037
3038 l_etp_tax_tran_ppt := l_etp_tax_tran_ppt_ytd - l_etp_tax_tran_ppt_prev;
3039
3040 ELSE
3041
3042 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,lt_user_table,(l_tot_prev_etp+p_etp_tran_noppt_ytd+p_etp_tran_ppt_ytd),lv_medicare_levy);
3043 FETCH csr_get_etp_tax into l_etp_tax_tran_ppt_ytd;
3044 CLOSE csr_get_etp_tax;
3045
3046 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,lt_user_table,(l_tot_prev_etp+p_etp_tran_noppt_ytd+p_etp_tran_ppt_ytd-p_etp_tran_ppt_run),lv_medicare_levy);
3047 FETCH csr_get_etp_tax into l_etp_tax_tran_ppt_prev;
3048 CLOSE csr_get_etp_tax;
3049
3050
3051 IF g_debug THEN
3052 hr_utility.set_location('l_etp_tax_tran_ppt_ytd '||l_etp_tax_tran_ppt_ytd, 93);
3053 hr_utility.set_location('l_etp_tax_tran_ppt_prev '||l_etp_tax_tran_ppt_prev, 93);
3054 END IF;
3055
3056 l_etp_tax_tran_ppt := l_etp_tax_tran_ppt_ytd - l_etp_tax_tran_ppt_prev;
3057
3058 END IF;
3059
3060 END IF;
3061
3062 END IF; -- Tran
3063
3064 END IF;
3065
3066
3067 END IF;
3068
3069 p_etp_tax_notran_noppt := l_etp_tax_notran_noppt;
3070 p_etp_tax_notran_ppt := l_etp_tax_notran_ppt;
3071 p_etp_tax_tran_noppt := l_etp_tax_tran_noppt;
3072 p_etp_tax_tran_ppt := l_etp_tax_tran_ppt;
3073
3074 IF g_debug THEN
3075 hr_utility.set_location('Leaving '||l_procedure, 99);
3076 hr_utility.set_location('p_etp_tax_notran_noppt '||p_etp_tax_notran_noppt, 99);
3077 hr_utility.set_location('p_etp_tax_notran_ppt '||p_etp_tax_notran_ppt, 99);
3078 hr_utility.set_location('p_etp_tax_tran_noppt '||p_etp_tax_tran_noppt, 99);
3079 hr_utility.set_location('p_etp_tax_tran_ppt '||p_etp_tax_tran_ppt, 99);
3080 END IF;
3081
3082 RETURN 0;
3083
3084 END calculate_etp_tax_2010;
3085
3086 /*
3087 Bug 9950136 - Sums up all the AL and LSL accrual plans
3088 for the given type
3089 */
3090
3091 FUNCTION get_foreign_accrual
3092 ( p_assignment_id IN NUMBER
3093 ,p_business_group_id IN NUMBER
3094 ,p_payroll_id IN NUMBER
3095 ,p_effective_date IN DATE
3096 ,p_std_accrual OUT NOCOPY NUMBER
3097 ,p_fw_accrual OUT NOCOPY NUMBER
3098 ) RETURN NUMBER
3099 IS
3100 l_proc VARCHAR2(72) := g_package||'get_fw_accrual_hours' ;
3101 l_accrual_plan_id NUMBER ;
3102 l_dummy NUMBER ;
3103 l_hours_flag CHAR(1);
3104 l_days_flag CHAR(1);
3105 l_error CHAR(1);
3106 l_std_hours NUMBER;
3107 l_fw_hours NUMBER;
3108
3109 l_abs_category VARCHAR2(10);
3110
3111 CURSOR csr_get_accrual_plan_id(c_assignment_id NUMBER
3112 ,c_effective_date DATE
3113 ,c_abs_category VARCHAR2
3114 ,c_fw_plan VARCHAR2
3115 ) IS
3116 SELECT pap.accrual_plan_id,pap.accrual_units_of_measure
3117 FROM pay_accrual_plans pap,
3118 pay_element_entries_f pee,
3119 pay_element_links_f pel,
3120 pay_element_types_f pet
3121 WHERE pee.assignment_id = c_assignment_id
3122 AND c_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
3123 AND pel.element_link_id = pee.element_link_id
3124 AND pel.element_type_id = pet.element_type_id
3125 AND pap.accrual_plan_element_type_id = pet.element_type_id
3126 AND pap.accrual_category = c_abs_category
3127 AND NVL(pap.information3,'N') = c_fw_plan;
3128
3129 BEGIN
3130 hr_utility.set_location(' Entering::'||l_proc,1);
3131 l_hours_flag := 'N';
3132 l_days_flag := 'N';
3133 l_std_hours := 0;
3134 l_fw_hours := 0;
3135 l_error := 'N';
3136
3137 FOR I IN 1..2
3138 LOOP
3139 IF i = 1
3140 THEN
3141 l_abs_category := 'AUAL';
3142 ELSE
3143 l_abs_category := 'AULSL';
3144 END IF;
3145
3146 l_error := NULL;
3147 l_hours_flag := NULL;
3148 l_days_flag := NULL;
3149
3150
3151 /* Count the Standard Accrual */
3152 FOR csr1 IN csr_get_accrual_plan_id(p_assignment_id, p_effective_date,l_abs_category,'N')
3153 LOOP
3154 IF (csr1.accrual_units_of_measure = 'H' AND l_days_flag='Y') OR (csr1.accrual_units_of_measure = 'D' AND l_hours_flag='Y')
3155 THEN
3156 l_error := 'Y';
3157 EXIT;
3158 END IF;
3159
3160 IF csr1.accrual_units_of_measure = 'H' AND l_hours_flag='N'
3161 THEN
3162 l_hours_flag := 'Y';
3163 END IF;
3164
3165 IF csr1.accrual_units_of_measure = 'D' AND l_days_flag='N'
3166 THEN
3167 l_days_flag := 'Y';
3168 END IF;
3169
3170 l_std_hours := l_std_hours + hr_au_holidays.get_net_accrual(p_assignment_id,p_payroll_id,
3171 p_business_group_id, csr1.accrual_plan_id,p_effective_date);
3172 END LOOP;
3173
3174 /* Count the Foreign Accrual */
3175 FOR csr1 IN csr_get_accrual_plan_id(p_assignment_id, p_effective_date,l_abs_category,'Y')
3176 LOOP
3177 IF (csr1.accrual_units_of_measure = 'H' AND l_days_flag='Y') OR (csr1.accrual_units_of_measure = 'D' AND l_hours_flag='Y')
3178 THEN
3179 l_error := 'Y';
3180 EXIT;
3181 END IF;
3182
3183 l_fw_hours := l_fw_hours + hr_au_holidays.get_net_accrual(p_assignment_id,p_payroll_id,
3184 p_business_group_id, csr1.accrual_plan_id,p_effective_date);
3185 END LOOP;
3186
3187 END LOOP;
3188
3189 IF l_error = 'Y'
3190 THEN
3191 hr_utility.set_location('Enrolled in Multiple Plans '||l_proc,15);
3192 hr_utility.set_message(801, 'HR_AU_MULTIPLE_ACCRUAL_PLANS');
3193 hr_utility.raise_error;
3194 END IF;
3195
3196 p_std_accrual := l_std_hours;
3197 p_fw_accrual := l_fw_hours;
3198
3199 hr_utility.set_location('Leaving:'||l_proc,2);
3200
3201 RETURN (0);
3202
3203 END get_foreign_accrual;
3204
3205
3206 /* Bug 10212532 Added function to calculate suspended days of PPL for ETP calculation */
3207 function get_days_ppl_suspended
3208 (p_assignment_id in NUMBER
3209 ,p_start_date in DATE
3210 ,p_end_date in DATE)
3211 return number is
3212
3213 /* Bug 10384820 - Added a condition to remove Retro created entires
3214 The Accrued Value and the dates should only be checked against the original entry
3215 */
3216 /* Bug 12586038 - Modified the cursor csr_get_days_ppl to calculate PPL suspended days for
3217 paid Parental leave recurring element also */
3218 /* Bug 14469003 - Added new hint of STAR_TRANSFORMATION */
3219
3220 cursor csr_get_days_ppl is
3221 select /*+ STAR_TRANSFORMATION */
3222 /*+ LEADING(paa ptp ppa pac pee) */ nvl(sum((1 + decode(pet.element_name, 'Rec Statutory PPL Payment',least(to_date(peev2.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS'), ptp.end_date),
3223 'Statutory Paid Parental Leave Payment' , to_date(peev2.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS')))-decode(pet.element_name, 'Rec Statutory PPL Payment',
3224 greatest(to_date(peev1.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS'), ptp.start_date),
3225 'Statutory Paid Parental Leave Payment' ,to_date(peev1.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS'))),0)
3226 from
3227 per_time_periods ptp,
3228 per_all_assignments_f paa,
3229 pay_assignment_actions pac,
3230 pay_payroll_actions ppa,
3231 pay_element_entries_f pee,
3232 pay_element_entry_values_f peev,
3233 pay_element_entry_values_f peev1,
3234 pay_element_entry_values_f peev2,
3235 pay_element_types_f pet,
3236 pay_input_values_f piv,
3237 pay_input_values_f piv1,
3238 pay_input_values_f piv2
3239 where
3240 ptp.payroll_id = ppa.payroll_id
3241 and ppa.payroll_action_id = pac.payroll_action_id
3242 and pee.assignment_id = p_assignment_id
3243 and pac.assignment_id = pee.assignment_id
3244 and pee.assignment_id = paa.assignment_id
3245 and pac.assignment_id = paa.assignment_id
3246 and pee.element_type_id = pet.element_type_id
3247 and pet.element_name IN ('Statutory Paid Parental Leave Payment','Rec Statutory PPL Payment')
3248 and pet.legislation_code = 'AU'
3249 and pee.element_entry_id = peev.element_entry_id
3250 and pee.effective_start_date = peev.effective_start_date
3251 and pee.effective_end_date = peev.effective_end_date
3252 and peev.input_value_id = piv.input_value_id
3253 and peev.screen_entry_value = 'N'
3254 and piv.name = 'Accrued'
3255 and pee.element_entry_id = peev1.element_entry_id
3256 and pee.effective_start_date = peev1.effective_start_date
3257 and pee.effective_end_date = peev1.effective_end_date
3258 and peev1.input_value_id = piv1.input_value_id
3259 and piv1.name = 'Start Date'
3260 and pee.element_entry_id = peev2.element_entry_id
3261 and pee.effective_start_date = peev2.effective_start_date
3262 and pee.effective_end_date = peev2.effective_end_date
3263 and peev2.input_value_id = piv2.input_value_id
3264 and piv2.name = 'End Date'
3265 and piv.element_type_id = pet.element_type_id
3266 and piv1.element_type_id = pet.element_type_id
3267 and piv2.element_type_id = pet.element_type_id
3268 and pee.effective_end_date >= greatest(to_date('01/01/2011','DD/MM/YYYY'), p_start_date)
3269 and p_end_date between piv.effective_start_date and piv.effective_end_date
3270 and p_end_date between piv1.effective_start_date and piv1.effective_end_date
3271 and p_end_date between piv2.effective_start_date and piv2.effective_end_date
3272 and p_end_date between pet.effective_start_date and pet.effective_end_date
3273 and ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
3274 and pee.effective_start_date BETWEEN paa.effective_start_date AND paa.effective_end_date
3275 and ppa.date_earned BETWEEN piv1.effective_start_date AND piv1.effective_end_date
3276 and ppa.date_earned BETWEEN piv2.effective_start_date AND piv2.effective_end_date
3277 and ppa.date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date
3278 and ppa.date_earned BETWEEN pee.effective_start_date AND pee.effective_end_date
3279 and ppa.date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
3280 and ppa.date_earned BETWEEN peev2.effective_start_date AND peev2.effective_end_date
3281 and ppa.date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date
3282 and pee.creator_type NOT IN ('EE','RR'); /* Bug 10384820 */
3283
3284
3285 l_procedure constant varchar2(100) := g_package||'get_days_ppl_suspended';
3286 l_get_days_ppl number := 0;
3287
3288
3289 begin
3290
3291 IF g_debug THEN
3292 hr_utility.set_location('Entering function '||l_procedure,1);
3293 hr_utility.set_location('p_start_date '||p_start_date,1);
3294 hr_utility.set_location('p_end_date '||p_end_date,1);
3295 END IF;
3296
3297
3298 open csr_get_days_ppl;
3299 fetch csr_get_days_ppl into l_get_days_ppl ;
3300 if csr_get_days_ppl%notfound then
3301 l_get_days_ppl := 0;
3302 end if;
3303 close csr_get_days_ppl;
3304
3305
3306 IF g_debug THEN
3307 hr_utility.set_location('l_get_days_ppl: '||l_get_days_ppl,20) ;
3308 hr_utility.set_location('Leaving: '||l_procedure,20) ;
3309 END IF;
3310
3311 return l_get_days_ppl;
3312
3313 end get_days_ppl_suspended;
3314
3315 /* bug 12583457 - to count ETP on Termination with Salary in Lieu of Notice input value entered */
3316 FUNCTION au_count_etps_sil
3317 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
3318 p_date_earned IN DATE
3319 )
3320 RETURN NUMBER IS
3321
3322 cursor au_count_etps is
3323 select count(pee.element_entry_id)
3324 from pay_element_types_f pet,
3325 pay_input_values_f piv,
3326 pay_element_entries_f pee,
3327 pay_element_entry_values_f peev,
3328 pay_input_values_f piv2,
3329 pay_element_entry_values_f peev2
3330 where pee.assignment_id = p_assignment_id
3331 and pet.element_type_id = piv.element_type_id
3332 and pet.element_name = 'ETP on Termination'
3333 and pet.element_type_id = pee.element_type_id
3334 and piv.name = 'Pay ETP Components'
3335 and pee.element_entry_id = peev.element_entry_id
3336 and piv.input_value_id = peev.input_value_id
3337 and peev.screen_entry_value = 'Y'
3338 and piv2.name = 'Salary in Lieu of Notice'
3339 and pee.element_entry_id = peev2.element_entry_id
3340 and piv2.input_value_id = peev2.input_value_id
3341 and peev2.screen_entry_value is not null
3342 and p_date_earned between pee.effective_start_date and pee.effective_end_date
3343 and p_date_earned between peev.effective_start_date and peev.effective_end_date
3344 and p_date_earned between pet.effective_start_date and pet.effective_end_date
3345 and p_date_earned between peev.effective_start_date and peev.effective_end_date;
3346
3347 l_procedure constant varchar2(100) := g_package||'.au_count_etps_sil';
3348 ln_count number;
3349
3350 BEGIN
3351
3352 IF g_debug THEN
3353 hr_utility.set_location(' '||l_procedure, 10);
3354 hr_utility.set_location('IN p_assignment_id ' ||p_assignment_id,10);
3355 hr_utility.set_location('IN p_date_earned ' ||p_date_earned,10);
3356 END IF;
3357
3358
3359 open au_count_etps;
3360 fetch au_count_etps into ln_count;
3361 if au_count_etps%notfound then
3362 ln_count := 1;
3363 end if;
3364 close au_count_etps;
3365
3366 IF g_debug THEN
3367 hr_utility.set_location('RETURN ln_count '||ln_count,10);
3368 END IF;
3369
3370 RETURN ln_count;
3371
3372 END au_count_etps_sil;
3373
3374 function calculate_etp_lumpsum_d_2012
3375 (p_assignment_id in number
3376 ,p_tax_unit_id in number
3377 ,p_assignment_action_id in number
3378 ,p_date_earned in date
3379 ,p_date_paid in date
3380 ,p_years_of_service in number
3381 ,p_lump_d_tax_free in number
3382 ,p_lump_d_service_increment in number
3383 ,p_redundancy_pay_ytd in number
3384 ,p_redundancy_pay in number
3385 )
3386 return number is
3387
3388 cursor csr_get_curr_atd is
3389 select pps.actual_termination_date
3390 from per_periods_of_service pps, per_all_assignments_f paaf
3391 where paaf.assignment_id = p_assignment_id
3392 and p_date_paid between paaf.effective_start_date and paaf.effective_end_date
3393 and paaf.period_of_service_id = pps.period_of_service_id ;
3394
3395 cursor c_max_asg_action_id (c_year_start date
3396 ,c_year_end date) is
3397 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
3398 from pay_assignment_actions paa,
3399 pay_payroll_actions ppa,
3400 per_assignments_f paf
3401 where paa.assignment_id = paf.assignment_id
3402 and paf.assignment_id = p_assignment_id
3403 and ppa.payroll_action_id = paa.payroll_action_id
3404 and ppa.effective_date between c_year_start and c_year_end
3405 and ppa.payroll_id = paf.payroll_id
3406 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
3407 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
3408 and paa.action_status = 'C'
3409 and paa.tax_unit_id = p_tax_unit_id;
3410
3411 cursor csr_get_bal_value (c_balance_name varchar2,
3412 c_max_assignment_action_id pay_assignment_actions.assignment_action_id%type) IS
3413 SELECT nvl(pay_balance_pkg.get_value(pdb.defined_balance_id
3414 ,c_max_assignment_action_id
3415 ,p_tax_unit_id
3416 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL),0)
3417 FROM pay_balance_types pbt,
3418 pay_defined_balances pdb,
3419 pay_balance_dimensions pbd
3420 WHERE pbt.legislation_code = 'AU'
3421 AND pbt.balance_name = c_balance_name
3422 AND pbt.balance_type_id = pdb.balance_type_id
3423 AND pbd.balance_dimension_id = pdb.balance_dimension_id
3424 AND pbd.dimension_name = '_ASG_LE_YTD';
3425
3426
3427 l_procedure constant varchar2(100) := g_package||'calculate_etp_lumpsum_d_2012';
3428 l_max_lump_d number;
3429 l_acutal_termination_date date;
3430 l_year_start_atd date;
3431 l_year_end_atd date;
3432 l_year_start_paid date;
3433 l_year_end_paid date;
3434 l_count_year number;
3435 l_max_assignment_action_id pay_assignment_actions.assignment_action_id%type;
3436 l_add number := 0;
3437 l_prev_etp number := 0;
3438 l_tot_prev_etp number := 0;
3439 l_max number := 0;
3440
3441 l_count number := 0;
3442 l_case boolean := FALSE ;
3443 l_lump_sum_d number := 0;
3444
3445
3446 begin
3447 --
3448 IF g_debug THEN
3449 hr_utility.set_location(l_procedure, 1);
3450 hr_utility.set_location('p_assignment_id '||p_assignment_id, 1);
3451 hr_utility.set_location('p_tax_unit_id '||p_tax_unit_id, 1);
3452 hr_utility.set_location('p_assignment_action_id '||p_assignment_action_id, 1);
3453 hr_utility.set_location('p_date_earned '||p_date_earned, 1);
3454 hr_utility.set_location('p_date_paid '||p_date_paid, 1);
3455 hr_utility.set_location('p_years_of_service '||p_years_of_service, 1);
3456 hr_utility.set_location('p_lump_d_tax_free '||p_lump_d_tax_free, 1);
3457 hr_utility.set_location('p_lump_d_service_increment '||p_lump_d_service_increment, 1);
3458 hr_utility.set_location('p_redundancy_pay_ytd '||p_redundancy_pay_ytd, 1);
3459 hr_utility.set_location('p_redundancy_pay '||p_redundancy_pay, 1);
3460 END IF;
3461
3462 l_max_lump_d := p_lump_d_tax_free + p_lump_d_service_increment * p_years_of_service;
3463
3464 IF g_debug THEN
3465 hr_utility.set_location('l_max_lump_d '||l_max_lump_d, 10);
3466 END IF;
3467
3468 open csr_get_curr_atd;
3469 fetch csr_get_curr_atd into l_acutal_termination_date;
3470 close csr_get_curr_atd;
3471
3472 l_year_start_atd := hr_au_routes.span_start(l_acutal_termination_date, 1, '01-07-');
3473 l_year_end_atd := add_months(l_year_start_atd,12) - 1;
3474 l_year_start_paid := hr_au_routes.span_start(p_date_paid, 1, '01-07-');
3475 l_year_end_paid := add_months(l_year_start_paid,12) - 1;
3476
3477 IF g_debug THEN
3478 hr_utility.set_location('l_year_start_atd '||l_year_start_atd, 20);
3479 hr_utility.set_location('l_year_end_atd '||l_year_end_atd, 20);
3480 hr_utility.set_location('l_year_start_paid '||l_year_start_paid, 20);
3481 hr_utility.set_location('l_year_end_paid '||l_year_end_paid, 20);
3482 END IF;
3483
3484 IF g_debug THEN
3485 hr_utility.set_location('previous g_lump_sum_d.assignment_id '||nvl(pay_au_terminations.g_lump_sum_d.assignment_id,0), 21);
3486 hr_utility.set_location('previous g_lump_sum_d.value '||nvl(pay_au_terminations.g_lump_sum_d.value,0), 21);
3487 END IF;
3488
3489 IF l_year_end_atd = l_year_end_paid THEN -- paid in the termination year
3490
3491 IF p_redundancy_pay_ytd = 0 THEN -- 1st redundancy payment
3492
3493 IF p_assignment_id = nvl(pay_au_terminations.g_lump_sum_d.assignment_id,0) THEN
3494 IF g_debug THEN hr_utility.set_location('retrieving g_lumpsum_d '||nvl(pay_au_terminations.g_lump_sum_d.value,0), 30); END IF;
3495 l_max := l_max_lump_d - nvl(pay_au_terminations.g_lump_sum_d.value,0);
3496 pay_au_terminations.g_lump_sum_d.value := nvl(pay_au_terminations.g_lump_sum_d.value,0) + least(l_max, p_redundancy_pay);
3497
3498 ELSE
3499 IF g_debug THEN hr_utility.set_location('not retrieving g_lumpsum_d ', 30); END IF;
3500 l_max := l_max_lump_d;
3501 pay_au_terminations.g_lump_sum_d.value := least(l_max, p_redundancy_pay);
3502
3503 END IF;
3504
3505 ELSE -- subsequent redundancy payment
3506
3507 IF p_assignment_id = nvl(pay_au_terminations.g_lump_sum_d.assignment_id,0) THEN
3508 IF g_debug THEN hr_utility.set_location('retrieving g_lumpsum_d '||nvl(pay_au_terminations.g_lump_sum_d.value,0), 31); END IF;
3509 IF p_redundancy_pay_ytd + nvl(pay_au_terminations.g_lump_sum_d.value,0) < l_max_lump_d THEN
3510 l_max := l_max_lump_d - p_redundancy_pay_ytd - nvl(pay_au_terminations.g_lump_sum_d.value,0);
3511 pay_au_terminations.g_lump_sum_d.value := nvl(pay_au_terminations.g_lump_sum_d.value,0) + least(l_max, p_redundancy_pay);
3512 ELSE
3513 l_max := 0;
3514 END IF;
3515 ELSE
3516 IF g_debug THEN hr_utility.set_location('not retrieving g_lumpsum_d ', 31); END IF;
3517 IF p_redundancy_pay_ytd < l_max_lump_d THEN
3518 l_max := l_max_lump_d - p_redundancy_pay_ytd ;
3519 pay_au_terminations.g_lump_sum_d.value := least(l_max, p_redundancy_pay);
3520 ELSE
3521 l_max := 0;
3522 END IF;
3523 END IF;
3524
3525 END IF;
3526
3527 ELSE -- l_year_end_atd <> l_year_end_paid
3528
3529 select months_between(l_year_end_paid, l_year_end_atd)/12
3530 into l_count_year
3531 from dual;
3532
3533 for i in 0 .. (l_count_year-1) loop
3534
3535 open c_max_asg_action_id (add_months(l_year_start_atd,l_add), add_months(l_year_end_atd,l_add));
3536 fetch c_max_asg_action_id into l_max_assignment_action_id;
3537 close c_max_asg_action_id;
3538
3539 open csr_get_bal_value ('ETP Payments Lump Sum D', l_max_assignment_action_id); /* 14703008 */
3540 fetch csr_get_bal_value into l_prev_etp;
3541 close csr_get_bal_value;
3542
3543 l_tot_prev_etp := l_tot_prev_etp + l_prev_etp;
3544
3545 IF g_debug THEN
3546 hr_utility.set_location('l_add '||l_add, 40);
3547 hr_utility.set_location('l_max_assignment_action_id '||l_max_assignment_action_id, 40);
3548 hr_utility.set_location('l_prev_etp '||l_prev_etp, 40);
3549 hr_utility.set_location('l_tot_prev_etp '||l_tot_prev_etp, 40);
3550 END IF;
3551
3552 l_add := l_add + 12;
3553
3554 end loop;
3555
3556 IF l_tot_prev_etp = 0 THEN -- the 1st redundancy payment after termination
3557
3558 IF p_redundancy_pay_ytd = 0 THEN -- 1st redundancy payment
3559
3560 IF p_assignment_id = nvl(pay_au_terminations.g_lump_sum_d.assignment_id,0) THEN
3561 IF g_debug THEN hr_utility.set_location('retrieving g_lumpsum_d '||nvl(pay_au_terminations.g_lump_sum_d.value,0), 60); END IF;
3562 l_max := l_max_lump_d - nvl(pay_au_terminations.g_lump_sum_d.value,0);
3563 pay_au_terminations.g_lump_sum_d.value := nvl(pay_au_terminations.g_lump_sum_d.value,0) + least(l_max, p_redundancy_pay);
3564
3565 ELSE
3566 IF g_debug THEN hr_utility.set_location('not retrieving g_lumpsum_d ', 60); END IF;
3567 l_max := l_max_lump_d;
3568 pay_au_terminations.g_lump_sum_d.value := least(l_max, p_redundancy_pay);
3569
3570 END IF;
3571
3572 ELSE -- subsequent redundancy payment
3573
3574 IF p_assignment_id = nvl(pay_au_terminations.g_lump_sum_d.assignment_id,0) THEN
3575 IF g_debug THEN hr_utility.set_location('retrieving g_lumpsum_d '||nvl(pay_au_terminations.g_lump_sum_d.value,0), 61); END IF;
3576 IF p_redundancy_pay_ytd + nvl(pay_au_terminations.g_lump_sum_d.value,0) < l_max_lump_d THEN
3577 l_max := l_max_lump_d - p_redundancy_pay_ytd - nvl(pay_au_terminations.g_lump_sum_d.value,0);
3578 pay_au_terminations.g_lump_sum_d.value := nvl(pay_au_terminations.g_lump_sum_d.value,0) + least(l_max, p_redundancy_pay);
3579 ELSE
3580 l_max := 0;
3581 END IF;
3582 ELSE
3583 IF g_debug THEN hr_utility.set_location('not retrieving g_lumpsum_d ', 61); END IF;
3584 IF p_redundancy_pay_ytd < l_max_lump_d THEN
3585 l_max := l_max_lump_d - p_redundancy_pay_ytd ;
3586 pay_au_terminations.g_lump_sum_d.value := least(l_max, p_redundancy_pay);
3587 ELSE
3588 l_max := 0;
3589 END IF;
3590 END IF;
3591
3592 END IF;
3593
3594 ELSE -- l_tot_prev_etp <> 0
3595
3596 IF p_redundancy_pay_ytd = 0 THEN -- 1st redundancy payment
3597
3598 IF p_assignment_id = nvl(pay_au_terminations.g_lump_sum_d.assignment_id,0) THEN
3599 IF g_debug THEN hr_utility.set_location('retrieving g_lumpsum_d '||nvl(pay_au_terminations.g_lump_sum_d.value,0), 62); END IF;
3600 IF l_tot_prev_etp + nvl(pay_au_terminations.g_lump_sum_d.value,0) < l_max_lump_d THEN
3601 l_max := l_max_lump_d - l_tot_prev_etp - nvl(pay_au_terminations.g_lump_sum_d.value,0);
3602 pay_au_terminations.g_lump_sum_d.value := nvl(pay_au_terminations.g_lump_sum_d.value,0) + least(l_max, p_redundancy_pay);
3603 ELSE
3604 l_max := 0;
3605 END IF;
3606
3607 ELSE
3608
3609 IF g_debug THEN hr_utility.set_location('not retrieving g_lumpsum_d ', 62); END IF;
3610 IF l_tot_prev_etp < l_max_lump_d THEN
3611 l_max := l_max_lump_d - l_tot_prev_etp;
3612 pay_au_terminations.g_lump_sum_d.value := least(l_max, p_redundancy_pay);
3613 ELSE
3614 l_max := 0;
3615 END IF;
3616
3617 END IF;
3618
3619 ELSE -- subsequent redundancy payment
3620
3621 IF p_assignment_id = nvl(pay_au_terminations.g_lump_sum_d.assignment_id,0) THEN
3622 IF g_debug THEN hr_utility.set_location('retrieving g_lumpsum_d '||nvl(pay_au_terminations.g_lump_sum_d.value,0), 63); END IF;
3623 IF l_tot_prev_etp + p_redundancy_pay_ytd + nvl(pay_au_terminations.g_lump_sum_d.value,0) < l_max_lump_d THEN
3624 l_max := l_max_lump_d - l_tot_prev_etp - p_redundancy_pay_ytd - nvl(pay_au_terminations.g_lump_sum_d.value,0);
3625 pay_au_terminations.g_lump_sum_d.value := nvl(pay_au_terminations.g_lump_sum_d.value,0) + least(l_max, p_redundancy_pay);
3626 ELSE
3627 l_max := 0;
3628 END IF;
3629
3630 ELSE
3631
3632 IF g_debug THEN hr_utility.set_location('not retrieving g_lumpsum_d ', 63); END IF;
3633 IF l_tot_prev_etp + p_redundancy_pay_ytd < l_max_lump_d THEN
3634 l_max := l_max_lump_d - l_tot_prev_etp - p_redundancy_pay_ytd;
3635 pay_au_terminations.g_lump_sum_d.value := least(l_max, p_redundancy_pay);
3636 ELSE
3637 l_max := 0;
3638 END IF;
3639
3640 END IF;
3641
3642 END IF;
3643
3644 END IF;
3645
3646 END IF;
3647
3648 l_lump_sum_d := least(l_max, p_redundancy_pay);
3649 pay_au_terminations.g_lump_sum_d.assignment_id := p_assignment_id;
3650
3651 IF g_debug THEN
3652 hr_utility.set_location('l_max '||l_max, 90);
3653 hr_utility.set_location('l_lump_sum_d '||l_lump_sum_d, 90);
3654 hr_utility.set_location('setting g_lumpsum_d assignment_id : '||pay_au_terminations.g_lump_sum_d.assignment_id||', value : '|| pay_au_terminations.g_lump_sum_d.value, 90);
3655 hr_utility.set_location('Leaving '||l_procedure, 90);
3656 END IF;
3657
3658 return l_lump_sum_d;
3659
3660 end calculate_etp_lumpsum_d_2012;
3661
3662 /* 15852244 - added p_etp_included_under_ytd IN, p_included_under_cap OUT parameters
3663 - replaced l_etp_included_prev with p_etp_included_under_ytd */
3664 FUNCTION calculate_etp_tax_2012
3665 (p_business_group_id IN NUMBER
3666 ,p_assignment_id IN NUMBER
3667 ,p_tax_unit_id IN NUMBER
3668 ,p_assignment_action_id IN NUMBER
3669 ,p_date_paid IN DATE
3670 ,p_trans_etp IN VARCHAR2
3671 ,p_death_benefit_type IN VARCHAR2
3672 ,p_over_pres_age IN VARCHAR2
3673 ,p_tfn_for_non_dependent IN VARCHAR2
3674 ,p_medicare_levy IN NUMBER
3675 ,p_current_pay_ytd IN NUMBER
3676 ,p_etp_excluded_ytd in NUMBER
3677 ,p_etp_included_ytd in NUMBER
3678 ,p_etp_excluded_run in NUMBER
3679 ,p_etp_included_run in NUMBER
3680 ,p_etp_included_under_ytd in NUMBER
3681 ,p_etp_tax_excluded OUT NOCOPY NUMBER
3682 ,p_etp_tax_excluded_pp OUT NOCOPY NUMBER
3683 ,p_etp_tax_included OUT NOCOPY NUMBER
3684 ,p_etp_tax_included_pp OUT NOCOPY NUMBER
3685 ,p_included_under_cap OUT NOCOPY NUMBER
3686 )
3687 RETURN NUMBER IS
3688
3689 CURSOR csr_get_etp_tax(p_bus_grp_id IN hr_all_organization_units.organization_id%TYPE
3690 ,p_date_paid IN DATE
3691 ,p_user_table IN VARCHAR2
3692 ,p_amount IN NUMBER
3693 ,p_med_levy IN NUMBER) IS
3694 SELECT /*+ INDEX(PURF) ORDERED */ NVL(SUM(round((least(fnd_number.canonical_to_number(purf.row_high_range),p_amount)-fnd_number.canonical_to_number(purf.row_low_range_or_name))
3695 *(fnd_number.canonical_to_number(pucif.value) + decode(fnd_number.canonical_to_number(pucif.value),0,0,p_med_levy)))),0)
3696 FROM pay_user_tables put,
3697 pay_user_rows_F purf,
3698 pay_user_columns puc,
3699 pay_user_column_instances_f pucif
3700 where put.legislation_code='AU'
3701 and put.user_table_name=p_user_table
3702 and put.user_table_id=purf.user_table_id
3703 and put.user_table_id=puc.user_table_id
3704 and puc.user_column_id=pucif.user_column_id
3705 and purf.user_row_id=pucif.user_row_id
3706 and p_date_paid between purf.effective_start_date and purf.effective_end_date
3707 and p_date_paid between pucif.effective_start_date and pucif.effective_end_date
3708 and p_amount > fnd_number.canonical_to_number(purf.row_low_range_or_name)
3709 order by fnd_number.canonical_to_number(purf.row_low_range_or_name);
3710
3711
3712 cursor csr_get_curr_atd is
3713 select pps.actual_termination_date
3714 from per_periods_of_service pps, per_all_assignments_f paaf
3715 where paaf.assignment_id = p_assignment_id
3716 and p_date_paid between paaf.effective_start_date and paaf.effective_end_date
3717 and paaf.period_of_service_id = pps.period_of_service_id ;
3718
3719 cursor c_max_asg_action_id (c_year_start date
3720 ,c_year_end date
3721 ,c_assignment_id number) is
3722 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
3723 from pay_assignment_actions paa,
3724 pay_payroll_actions ppa,
3725 per_assignments_f paf
3726 where paa.assignment_id = paf.assignment_id
3727 and paf.assignment_id = c_assignment_id
3728 and ppa.payroll_action_id = paa.payroll_action_id
3729 and ppa.effective_date between c_year_start and c_year_end
3730 and ppa.payroll_id = paf.payroll_id
3731 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
3732 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
3733 and paa.action_status = 'C'
3734 and paa.tax_unit_id = p_tax_unit_id;
3735
3736 cursor csr_get_bal_value (c_balance_name varchar2,
3737 c_max_assignment_action_id pay_assignment_actions.assignment_action_id%type) IS
3738 SELECT nvl(pay_balance_pkg.get_value(pdb.defined_balance_id
3739 ,c_max_assignment_action_id
3740 ,p_tax_unit_id
3741 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL),0)
3742 FROM pay_balance_types pbt,
3743 pay_defined_balances pdb,
3744 pay_balance_dimensions pbd
3745 WHERE pbt.legislation_code = 'AU'
3746 AND pbt.balance_name = c_balance_name
3747 AND pbt.balance_type_id = pdb.balance_type_id
3748 AND pbd.balance_dimension_id = pdb.balance_dimension_id
3749 AND pbd.dimension_name = '_ASG_LE_YTD';
3750
3751 cursor c_get_etp_cap_detail (c_user_table_name pay_user_tables.user_table_name%type) is
3752 select
3753 pr.row_low_range_or_name low_range,
3754 pr.row_high_range high_range,
3755 pci.value
3756 from pay_user_tables pt, PAY_USER_COLUMNS pc, PAY_USER_ROWS_F pr, PAY_USER_COLUMN_INSTANCES_F pci
3757 where pt.user_table_name = c_user_table_name
3758 and pt.user_table_id = pc.user_table_id
3759 and pt.user_table_id = pr.user_table_id
3760 and pr.user_row_id = pci.user_row_id
3761 and pc.user_column_id = pci.user_column_id
3762 and p_date_paid between pr.effective_start_date and pr.effective_end_date
3763 and p_date_paid between pci.effective_start_date and pci.effective_end_date
3764 order by pr.row_low_range_or_name;
3765
3766 cursor csr_get_income_cap is
3767 select global_value
3768 from ff_globals_f
3769 where global_name = 'ETP_WHOLE_OF_INCOME'
3770 and legislation_code = 'AU'
3771 and p_date_paid between effective_start_date and effective_end_date;
3772
3773 cursor csr_rehired_assignment (c_year_start_paid date) is
3774 select distinct paa.assignment_id, pps.actual_termination_date, nvl(pps.final_process_date, to_date('31/12/4712','dd/mm/yyyy')) final_process_date
3775 from per_periods_of_service pps
3776 ,per_all_assignments_f paa
3777 where pps.person_id = (
3778 select person_id
3779 from per_all_assignments_f paa1
3780 where paa1.assignment_id = p_assignment_id
3781 and rownum = 1)
3782 and paa.period_of_service_id = pps.period_of_service_id
3783 and nvl(pps.final_process_date, to_date('31/12/4712','dd/mm/yyyy')) >= c_year_start_paid
3784 and paa.assignment_id <> p_assignment_id ;
3785
3786 cursor c_rehire_max_asg_action_id (c_year_start date
3787 ,c_year_end date
3788 ,c_assignment_id number) is
3789 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
3790 from pay_assignment_actions paa,
3791 pay_payroll_actions ppa,
3792 per_assignments_f paf
3793 where paa.assignment_id = paf.assignment_id
3794 and paf.assignment_id = c_assignment_id
3795 and ppa.payroll_action_id = paa.payroll_action_id
3796 and ppa.effective_date between c_year_start and c_year_end
3797 and ppa.payroll_id = paf.payroll_id
3798 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
3799 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
3800 and paa.action_status = 'C' ;
3801
3802 cursor csr_rehire_get_bal_value (c_balance_name varchar2,
3803 c_max_assignment_action_id pay_assignment_actions.assignment_action_id%type) IS
3804 SELECT nvl(pay_balance_pkg.get_value(pdb.defined_balance_id
3805 ,c_max_assignment_action_id
3806 ,NULL
3807 ,NULL,NULL,NULL,NULL,NULL,NULL,NULL),0)
3808 FROM pay_balance_types pbt,
3809 pay_defined_balances pdb,
3810 pay_balance_dimensions pbd
3811 WHERE pbt.legislation_code = 'AU'
3812 AND pbt.balance_name = c_balance_name
3813 AND pbt.balance_type_id = pdb.balance_type_id
3814 AND pbd.balance_dimension_id = pdb.balance_dimension_id
3815 AND pbd.dimension_name = '_ASG_YTD';
3816
3817 cursor csr_get_balance_name is
3818 select pbt.balance_name
3819 from pay_balance_types pbt
3820 where pbt.balance_name in ('Earnings_Standard'
3821 ,'Earnings_Leave_Loading'
3822 ,'Earnings_Additional'
3823 ,'Earnings_Spread'
3824 ,'Leave Payments Marginal'
3825 ,'Lump Sum A Payments'
3826 ,'Lump Sum B Payments'
3827 ,'Pre Tax Fixed Deductions'
3828 ,'ETP Taxable Payments Excluded'
3829 ,'ETP Taxable Payments Non Excluded'
3830 );
3831
3832 cursor csr_get_lump_sum_b_percent is
3833 select global_value
3834 from ff_globals_f
3835 where global_name = 'LUMP_SUM_B_PERCENTAGE'
3836 and legislation_code = 'AU'
3837 and p_date_paid between effective_start_date and effective_end_date;
3838
3839
3840 l_procedure VARCHAR2(80);
3841 ld_user_table VARCHAR2(50);
3842 ln_user_table VARCHAR2(50);
3843 lt_user_table VARCHAR2(50);
3844 lv_medicare_levy NUMBER := 0;
3845
3846 l_acutal_termination_date date;
3847 l_year_start_atd date;
3848 l_year_end_atd date;
3849 l_year_start_paid date;
3850 l_year_end_paid date;
3851 l_count_year number;
3852 l_add number;
3853 l_max_assignment_action_id pay_assignment_actions.assignment_action_id%type;
3854
3855 l_etp_tax_excluded_ytd number;
3856 l_etp_tax_included_ytd number;
3857 l_etp_tax_excluded_prev number;
3858 l_etp_tax_included_prev number;
3859 l_etp_tax_excluded_ytd1 number;
3860 l_etp_tax_excluded_ytd2 number;
3861 l_etp_tax_excluded_prev1 number;
3862 l_etp_tax_excluded_prev2 number;
3863 l_excluded_prev number;
3864 l_included_prev number;
3865 l_etp_tax_excluded number := 0;
3866 l_etp_tax_excluded_pp number := 0;
3867 l_etp_tax_included number := 0;
3868 l_etp_tax_included_pp number := 0;
3869 l_included_under_cap number := 0; /* 15852244 */
3870
3871 l_etp_cap number ;
3872 l_etp_whole_income_cap number;
3873 l_included_rate_prev_age number ;
3874 l_etp_included_rate_all_age number ;
3875 l_new_etp_cap number;
3876 l_excluded_ytd number;
3877 l_excluded_run number;
3878 l_included_ytd number;
3879 l_included_run number;
3880 l_etp_excluded_prev number;
3881 l_etp_included_prev number;
3882 l_tot_excluded_prev number := 0;
3883 l_tot_included_prev number := 0;
3884
3885 l_current_pay_ytd number;
3886 l_rehire_max_asg_action_id number;
3887 l_rehire_ytd number;
3888 l_lump_sum_b_percent ff_globals_f.global_value%type;
3889
3890 BEGIN
3891
3892 l_procedure := 'calculate_etp_tax_2012';
3893
3894 IF g_debug THEN
3895 hr_utility.set_location('Entering '||l_procedure, 10);
3896 hr_utility.set_location('IN p_business_group_id ' ||p_business_group_id,10);
3897 hr_utility.set_location('IN p_assignment_id ' ||p_assignment_id,10);
3898 hr_utility.set_location('IN p_tax_unit_id ' ||p_tax_unit_id,10);
3899 hr_utility.set_location('IN p_assignment_action_id ' ||p_assignment_action_id,10);
3900 hr_utility.set_location('IN p_date_paid ' ||p_date_paid,10);
3901 hr_utility.set_location('IN p_trans_etp ' ||p_trans_etp,10);
3902 hr_utility.set_location('IN p_death_benefit_type ' ||p_death_benefit_type,10);
3903 hr_utility.set_location('IN p_over_pres_age ' ||p_over_pres_age,10);
3904 hr_utility.set_location('IN p_tfn_for_non_dependent ' ||p_tfn_for_non_dependent,10);
3905 hr_utility.set_location('IN p_medicare_levy ' ||p_medicare_levy,10);
3906 hr_utility.set_location('IN p_current_pay_ytd ' ||p_current_pay_ytd,10);
3907 hr_utility.set_location('IN p_etp_excluded_ytd ' ||p_etp_excluded_ytd,10);
3908 hr_utility.set_location('IN p_etp_included_ytd ' ||p_etp_included_ytd,10);
3909 hr_utility.set_location('IN p_etp_excluded_run ' ||p_etp_excluded_run,10);
3910 hr_utility.set_location('IN p_etp_included_run ' ||p_etp_included_run,10);
3911 hr_utility.set_location('IN p_etp_included_under_ytd ' ||p_etp_included_under_ytd,10);
3912 END IF;
3913
3914 l_excluded_ytd := p_etp_excluded_ytd;
3915 l_excluded_run := p_etp_excluded_run;
3916 l_included_ytd := p_etp_included_ytd;
3917 l_included_run := p_etp_included_run;
3918 l_etp_excluded_prev := l_excluded_ytd - l_excluded_run;
3919 l_etp_included_prev := l_included_ytd - l_included_run;
3920
3921 lv_medicare_levy := p_medicare_levy;
3922
3923 /* Fetching User Table Information an ETP Tax Rate */
3924 IF p_trans_etp='D' THEN
3925 IF p_death_benefit_type = 'D' THEN
3926 ld_user_table := 'TAX_SCALE_ETP_DEATH_DEPENDENT';
3927 ELSE
3928 ld_user_table := 'TAX_SCALE_ETP_DEATH_NON_DEPENDENT';
3929 IF p_tfn_for_non_dependent ='N' THEN
3930 lv_medicare_levy:=0;
3931 END IF;
3932 END IF;
3933
3934 if g_debug then hr_utility.set_location('death user table '||ld_user_table, 21); end if;
3935
3936 ELSE
3937
3938 IF p_over_pres_age ='N' THEN
3939 ln_user_table := 'TAX_SCALE_NON_TRANS_UNDER_PREV_AGE';
3940
3941 FOR c_etp_detail in c_get_etp_cap_detail(ln_user_table) loop
3942 IF c_etp_detail.low_range = 0 THEN
3943 l_etp_cap := c_etp_detail.high_range;
3944 l_included_rate_prev_age := c_etp_detail.value;
3945 ELSE
3946 l_etp_included_rate_all_age := c_etp_detail.value;
3947 END IF;
3948 END loop;
3949
3950 if g_debug then
3951 hr_utility.set_location('user table '||ln_user_table, 22);
3952 hr_utility.set_location('l_etp_cap original '||l_etp_cap, 22);
3953 hr_utility.set_location('l_included_rate_prev_age '||l_included_rate_prev_age, 22);
3954 hr_utility.set_location('l_etp_included_rate_all_age '||l_etp_included_rate_all_age, 22);
3955 end if;
3956
3957 ELSIF p_over_pres_age ='Y' THEN
3958 ln_user_table := 'TAX_SCALE_NON_TRANS_OVER_PREV_AGE';
3959
3960 FOR c_etp_detail in c_get_etp_cap_detail(ln_user_table) loop
3961 IF c_etp_detail.low_range = 0 THEN
3962 l_etp_cap := c_etp_detail.high_range;
3963 l_included_rate_prev_age := c_etp_detail.value;
3964 ELSE
3965 l_etp_included_rate_all_age := c_etp_detail.value;
3966 END IF;
3967 END loop;
3968
3969 if g_debug then
3970 hr_utility.set_location('user table '||ln_user_table, 23);
3971 hr_utility.set_location('l_etp_cap original '||l_etp_cap, 23);
3972 hr_utility.set_location('l_included_rate_prev_age '||l_included_rate_prev_age, 23);
3973 hr_utility.set_location('l_etp_included_rate_all_age '||l_etp_included_rate_all_age, 23);
3974 end if;
3975
3976 END IF;
3977
3978 END IF;
3979
3980 OPEN csr_get_income_cap;
3981 FETCH csr_get_income_cap INTO l_etp_whole_income_cap;
3982 CLOSE csr_get_income_cap;
3983
3984 open csr_get_curr_atd;
3985 fetch csr_get_curr_atd into l_acutal_termination_date;
3986 close csr_get_curr_atd;
3987
3988 l_year_start_atd := hr_au_routes.span_start(l_acutal_termination_date, 1, '01-07-');
3989 l_year_end_atd := add_months(l_year_start_atd,12) - 1;
3990 l_year_start_paid := hr_au_routes.span_start(p_date_paid, 1, '01-07-');
3991 l_year_end_paid := add_months(l_year_start_paid,12) - 1;
3992
3993 IF g_debug THEN
3994 hr_utility.set_location('l_year_start_atd '||l_year_start_atd, 31);
3995 hr_utility.set_location('l_year_end_atd '||l_year_end_atd, 31);
3996 hr_utility.set_location('l_year_start_paid '||l_year_start_paid, 31);
3997 hr_utility.set_location('l_year_end_paid '||l_year_end_paid, 31);
3998 END IF;
3999
4000 l_current_pay_ytd := p_current_pay_ytd;
4001
4002 /* Checking Taxable Income from previous assignments in the current financial year */
4003 FOR rec_rehire in csr_rehired_assignment(l_year_start_paid) LOOP
4004
4005 if g_debug then
4006 hr_utility.set_location('Rehire --------------------------------', 32);
4007 hr_utility.set_location('Rehire assignment_id '||rec_rehire.assignment_id, 32);
4008 hr_utility.set_location('Rehire final_process_date '||to_char(rec_rehire.final_process_date,'DD-MON-YYYY'), 32);
4009 end if;
4010
4011 l_rehire_max_asg_action_id := 0;
4012 l_rehire_ytd := 0;
4013
4014 open c_rehire_max_asg_action_id(l_year_start_paid, least(l_year_end_paid, rec_rehire.final_process_date),rec_rehire.assignment_id);
4015 fetch c_rehire_max_asg_action_id into l_rehire_max_asg_action_id;
4016 IF c_rehire_max_asg_action_id%FOUND THEN
4017
4018 if g_debug then hr_utility.set_location(' Rehire l_rehire_max_asg_action_id '||l_rehire_max_asg_action_id, 33); end if;
4019
4020 FOR rec_bal in csr_get_balance_name LOOP
4021 open csr_rehire_get_bal_value(rec_bal.balance_name,l_rehire_max_asg_action_id);
4022 fetch csr_rehire_get_bal_value into l_rehire_ytd;
4023 IF csr_rehire_get_bal_value%FOUND THEN
4024 IF rec_bal.balance_name = 'Lump Sum B Payments' THEN
4025 open csr_get_lump_sum_b_percent;
4026 fetch csr_get_lump_sum_b_percent into l_lump_sum_b_percent;
4027 close csr_get_lump_sum_b_percent;
4028 l_rehire_ytd := l_rehire_ytd * to_number(l_lump_sum_b_percent);
4029 END IF;
4030 l_current_pay_ytd := l_current_pay_ytd + l_rehire_ytd;
4031 END IF;
4032 close csr_rehire_get_bal_value;
4033
4034 if g_debug then
4035 hr_utility.set_location(' Rehire balance_name '||rec_bal.balance_name, 34);
4036 hr_utility.set_location(' Rehire balance_value '||l_rehire_ytd, 34);
4037 end if;
4038
4039 END LOOP;
4040
4041 END IF;
4042 close c_rehire_max_asg_action_id;
4043
4044 END LOOP;
4045 if g_debug then hr_utility.set_location('Rehire l_current_pay_ytd '||l_current_pay_ytd, 35); end if;
4046
4047
4048 IF l_year_end_atd = l_year_end_paid THEN /* ETP pay date is in the same financial year of Actual Termination Date */
4049
4050 IF p_trans_etp='D' THEN
4051
4052 IF l_etp_excluded_prev <> 0 THEN
4053
4054 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ld_user_table,l_excluded_ytd,lv_medicare_levy);
4055 FETCH csr_get_etp_tax into l_etp_tax_excluded_ytd;
4056 CLOSE csr_get_etp_tax;
4057
4058 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ld_user_table,l_etp_excluded_prev,lv_medicare_levy);
4059 FETCH csr_get_etp_tax into l_etp_tax_excluded_prev;
4060 CLOSE csr_get_etp_tax;
4061
4062 IF g_debug THEN
4063 hr_utility.set_location('l_etp_tax_excluded_ytd '||l_etp_tax_excluded_ytd, 42);
4064 hr_utility.set_location('l_etp_tax_excluded_prev '||l_etp_tax_excluded_prev, 42);
4065 END IF;
4066
4067 l_etp_tax_excluded := l_etp_tax_excluded_ytd - l_etp_tax_excluded_prev;
4068
4069 ELSE
4070
4071 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ld_user_table,l_excluded_run,lv_medicare_levy);
4072 FETCH csr_get_etp_tax into l_etp_tax_excluded;
4073 CLOSE csr_get_etp_tax;
4074
4075 END IF;
4076
4077 if g_debug then
4078 hr_utility.set_location('l_etp_tax_excluded '||l_etp_tax_excluded, 41);
4079 end if;
4080
4081 ELSE -- Non Death type
4082
4083 IF l_excluded_run <> 0 THEN
4084
4085 IF l_etp_excluded_prev <> 0 OR p_etp_included_under_ytd <> 0 THEN
4086
4087 IF (l_etp_excluded_prev + p_etp_included_under_ytd) <= l_etp_cap THEN
4088
4089 IF (l_etp_excluded_prev + p_etp_included_under_ytd + l_excluded_run) <= l_etp_cap THEN
4090
4091 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,l_excluded_ytd,lv_medicare_levy);
4092 FETCH csr_get_etp_tax into l_etp_tax_excluded_ytd;
4093 CLOSE csr_get_etp_tax;
4094
4095 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,l_etp_excluded_prev,lv_medicare_levy);
4096 FETCH csr_get_etp_tax into l_etp_tax_excluded_prev;
4097 CLOSE csr_get_etp_tax;
4098
4099 IF g_debug THEN
4100 hr_utility.set_location('l_etp_tax_excluded_ytd '||l_etp_tax_excluded_ytd, 52);
4101 hr_utility.set_location('l_etp_tax_excluded_prev '||l_etp_tax_excluded_prev, 52);
4102 END IF;
4103
4104 ELSE
4105
4106 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,l_etp_cap,lv_medicare_levy);
4107 FETCH csr_get_etp_tax into l_etp_tax_excluded_ytd1;
4108 CLOSE csr_get_etp_tax;
4109
4110 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,(l_etp_excluded_prev+p_etp_included_under_ytd),lv_medicare_levy);
4111 FETCH csr_get_etp_tax into l_etp_tax_excluded_prev1;
4112 CLOSE csr_get_etp_tax;
4113
4114 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,(l_excluded_run+l_etp_excluded_prev+p_etp_included_under_ytd),lv_medicare_levy);
4115 FETCH csr_get_etp_tax into l_etp_tax_excluded_ytd2;
4116 CLOSE csr_get_etp_tax;
4117
4118 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,l_etp_cap,lv_medicare_levy);
4119 FETCH csr_get_etp_tax into l_etp_tax_excluded_prev2;
4120 CLOSE csr_get_etp_tax;
4121
4122 IF g_debug THEN
4123 hr_utility.set_location('l_etp_tax_excluded_ytd1 '||l_etp_tax_excluded_ytd1, 53);
4124 hr_utility.set_location('l_etp_tax_excluded_prev1 '||l_etp_tax_excluded_prev1, 53);
4125 hr_utility.set_location('l_etp_tax_excluded_ytd2 '||l_etp_tax_excluded_ytd2, 53);
4126 hr_utility.set_location('l_etp_tax_excluded_prev2 '||l_etp_tax_excluded_prev2, 53);
4127 END IF;
4128
4129 l_etp_tax_excluded_ytd := l_etp_tax_excluded_ytd1 + l_etp_tax_excluded_ytd2;
4130 l_etp_tax_excluded_prev := l_etp_tax_excluded_prev1 + l_etp_tax_excluded_prev2;
4131
4132 END IF;
4133
4134 ELSE -- (l_etp_excluded_prev + p_etp_included_under_ytd) > l_etp_cap
4135
4136 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,(l_etp_cap+l_excluded_run),lv_medicare_levy);
4137 FETCH csr_get_etp_tax into l_etp_tax_excluded_ytd;
4138 CLOSE csr_get_etp_tax;
4139
4140 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,l_etp_cap,lv_medicare_levy);
4141 FETCH csr_get_etp_tax into l_etp_tax_excluded_prev;
4142 CLOSE csr_get_etp_tax;
4143
4144 IF g_debug THEN
4145 hr_utility.set_location('l_etp_tax_excluded_ytd '||l_etp_tax_excluded_ytd, 54);
4146 hr_utility.set_location('l_etp_tax_excluded_prev '||l_etp_tax_excluded_prev, 54);
4147 END IF;
4148
4149 END IF;
4150
4151
4152 l_etp_tax_excluded := l_etp_tax_excluded_ytd - l_etp_tax_excluded_prev;
4153
4154
4155 ELSE -- l_etp_excluded_prev = 0 AND p_etp_included_under_ytd = 0
4156
4157 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,l_excluded_run,lv_medicare_levy);
4158 FETCH csr_get_etp_tax into l_etp_tax_excluded;
4159 CLOSE csr_get_etp_tax;
4160
4161 END IF;
4162
4163 IF g_debug THEN
4164 hr_utility.set_location('l_etp_tax_excluded '||l_etp_tax_excluded, 51);
4165 END IF;
4166
4167 END IF;
4168
4169 IF l_included_run <> 0 THEN
4170
4171 if l_etp_whole_income_cap - l_current_pay_ytd - l_etp_included_prev < 0 then
4172 l_etp_whole_income_cap := 0;
4173 else
4174 l_etp_whole_income_cap := l_etp_whole_income_cap - l_current_pay_ytd - l_etp_included_prev;
4175 end if;
4176
4177 if g_debug then hr_utility.set_location('l_etp_whole_income_cap '||l_etp_whole_income_cap, 61); end if;
4178
4179 if l_etp_cap - l_excluded_ytd - p_etp_included_under_ytd < 0 then
4180 l_etp_cap := 0 ;
4181 else
4182 l_etp_cap := l_etp_cap - l_excluded_ytd - p_etp_included_under_ytd;
4183 end if;
4184
4185 if g_debug then hr_utility.set_location('l_etp_cap '||l_etp_cap, 61); end if;
4186
4187 if l_etp_whole_income_cap <= l_etp_cap then
4188 l_new_etp_cap := l_etp_whole_income_cap;
4189 else
4190 l_new_etp_cap := l_etp_cap ;
4191 end if;
4192
4193 if g_debug then hr_utility.set_location('l_new_etp_cap '||l_new_etp_cap, 61); end if;
4194
4195
4196 IF l_included_run <= l_new_etp_cap THEN
4197 l_etp_tax_included := round(l_included_run * (l_included_rate_prev_age + lv_medicare_levy));
4198 l_included_under_cap := l_included_run;
4199 ELSE
4200 l_etp_tax_included := round(l_new_etp_cap * (l_included_rate_prev_age + lv_medicare_levy)) +
4201 round((l_included_run - l_new_etp_cap) * (l_etp_included_rate_all_age + lv_medicare_levy));
4202 l_included_under_cap := l_new_etp_cap;
4203 END IF;
4204
4205 if g_debug then
4206 hr_utility.set_location('l_etp_tax_included '||l_etp_tax_included, 62);
4207 end if;
4208
4209 END IF;
4210
4211 END IF;
4212
4213 ELSE -- l_year_end_atd <> l_year_end_paid
4214
4215 l_count_year :=0;
4216 l_add :=0;
4217
4218 select months_between(l_year_end_paid, l_year_end_atd)/12
4219 into l_count_year
4220 from dual;
4221
4222 for i in 0 .. (l_count_year-1) loop -- until the current financial year
4223
4224 l_excluded_prev := 0;
4225 l_included_prev := 0;
4226
4227 open c_max_asg_action_id (add_months(l_year_start_atd,l_add), add_months(l_year_end_atd,l_add), p_assignment_id);
4228 fetch c_max_asg_action_id into l_max_assignment_action_id;
4229 if c_max_asg_action_id%found then
4230
4231 if p_trans_etp='D' then
4232 open csr_get_bal_value ('ETP Taxable Payments Excluded', l_max_assignment_action_id);
4233 fetch csr_get_bal_value into l_excluded_prev;
4234 close csr_get_bal_value;
4235
4236 IF g_debug THEN
4237 hr_utility.set_location('l_excluded_prev '||l_excluded_prev, 71);
4238 END IF;
4239
4240 l_tot_excluded_prev := l_tot_excluded_prev + l_excluded_prev;
4241
4242 else
4243
4244 open csr_get_bal_value ('ETP Taxable Payments Excluded', l_max_assignment_action_id);
4245 fetch csr_get_bal_value into l_excluded_prev;
4246 close csr_get_bal_value;
4247
4248 IF g_debug THEN
4249 hr_utility.set_location('l_excluded_prev '||l_excluded_prev, 72);
4250 END IF;
4251
4252 l_tot_excluded_prev := l_tot_excluded_prev + l_excluded_prev;
4253
4254 open csr_get_bal_value ('ETP Taxable Non Excluded Under Cap', l_max_assignment_action_id); /* 15852244 - the new element is fetched */
4255 fetch csr_get_bal_value into l_included_prev;
4256 close csr_get_bal_value;
4257
4258 IF g_debug THEN
4259 hr_utility.set_location('l_included_prev '||l_included_prev, 72);
4260 END IF;
4261
4262 l_tot_included_prev := l_tot_included_prev + l_included_prev;
4263
4264 end if;
4265
4266 end if;
4267 close c_max_asg_action_id;
4268
4269 IF g_debug THEN
4270 hr_utility.set_location('l_add '||l_add, 74);
4271 hr_utility.set_location('l_max_assignment_action_id '||l_max_assignment_action_id, 74);
4272 hr_utility.set_location('l_tot_excluded_prev '||l_tot_excluded_prev, 74);
4273 hr_utility.set_location('l_tot_included_prev '||l_tot_included_prev, 74);
4274 END IF;
4275
4276 l_add := l_add + 12;
4277
4278 end loop;
4279
4280 IF p_trans_etp='D' THEN
4281
4282 IF l_excluded_run <> 0 and l_tot_excluded_prev = 0 THEN
4283
4284 IF l_etp_excluded_prev <> 0 THEN
4285
4286 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ld_user_table,l_excluded_ytd,lv_medicare_levy);
4287 FETCH csr_get_etp_tax into l_etp_tax_excluded_ytd;
4288 CLOSE csr_get_etp_tax;
4289
4290 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ld_user_table,l_etp_excluded_prev,lv_medicare_levy);
4291 FETCH csr_get_etp_tax into l_etp_tax_excluded_prev;
4292 CLOSE csr_get_etp_tax;
4293
4294 IF g_debug THEN
4295 hr_utility.set_location('l_etp_tax_excluded_ytd '||l_etp_tax_excluded_ytd, 82);
4296 hr_utility.set_location('l_etp_tax_excluded_prev '||l_etp_tax_excluded_prev, 82);
4297 END IF;
4298
4299 l_etp_tax_excluded := l_etp_tax_excluded_ytd - l_etp_tax_excluded_prev;
4300
4301 ELSE
4302
4303 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ld_user_table,l_excluded_run,lv_medicare_levy);
4304 FETCH csr_get_etp_tax into l_etp_tax_excluded;
4305 CLOSE csr_get_etp_tax;
4306
4307 END IF;
4308
4309 if g_debug then
4310 hr_utility.set_location('l_etp_tax_excluded '||l_etp_tax_excluded, 81);
4311 end if;
4312
4313 ELSIF l_excluded_run <> 0 and l_tot_excluded_prev <> 0 and p_death_benefit_type = 'ND' THEN
4314
4315 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ld_user_table,(l_tot_excluded_prev+l_excluded_ytd),lv_medicare_levy);
4316 FETCH csr_get_etp_tax into l_etp_tax_excluded_ytd;
4317 CLOSE csr_get_etp_tax;
4318
4319 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ld_user_table,(l_tot_excluded_prev+l_etp_excluded_prev),lv_medicare_levy);
4320 FETCH csr_get_etp_tax into l_etp_tax_excluded_prev;
4321 CLOSE csr_get_etp_tax;
4322
4323 IF g_debug THEN
4324 hr_utility.set_location('l_etp_tax_excluded_ytd '||l_etp_tax_excluded_ytd, 83);
4325 hr_utility.set_location('l_etp_tax_excluded_prev '||l_etp_tax_excluded_prev, 83);
4326 END IF;
4327
4328 l_etp_tax_excluded_pp := l_etp_tax_excluded_ytd - l_etp_tax_excluded_prev;
4329
4330 if g_debug then
4331 hr_utility.set_location('l_etp_tax_excluded_pp '||l_etp_tax_excluded_pp, 83);
4332 end if;
4333
4334 END IF;
4335
4336 ELSE -- Non Death type
4337
4338 IF l_excluded_run <> 0 THEN
4339
4340 IF l_tot_excluded_prev = 0 and l_tot_included_prev = 0 and l_etp_excluded_prev = 0 and p_etp_included_under_ytd = 0 THEN
4341
4342 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,l_excluded_run,lv_medicare_levy);
4343 FETCH csr_get_etp_tax into l_etp_tax_excluded;
4344 CLOSE csr_get_etp_tax;
4345
4346 ELSE
4347
4348 IF (l_tot_excluded_prev +l_tot_included_prev + l_etp_excluded_prev + p_etp_included_under_ytd) <= l_etp_cap THEN
4349
4350 IF (l_tot_excluded_prev + l_tot_included_prev + l_etp_excluded_prev + p_etp_included_under_ytd + l_excluded_run) <= l_etp_cap THEN
4351
4352 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,l_excluded_ytd,lv_medicare_levy);
4353 FETCH csr_get_etp_tax into l_etp_tax_excluded_ytd;
4354 CLOSE csr_get_etp_tax;
4355
4356 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,l_etp_excluded_prev,lv_medicare_levy);
4357 FETCH csr_get_etp_tax into l_etp_tax_excluded_prev;
4358 CLOSE csr_get_etp_tax;
4359
4360 IF g_debug THEN
4361 hr_utility.set_location('l_etp_tax_excluded_ytd '||l_etp_tax_excluded_ytd, 92);
4362 hr_utility.set_location('l_etp_tax_excluded_prev '||l_etp_tax_excluded_prev, 92);
4363 END IF;
4364
4365 ELSE
4366
4367 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,l_etp_cap,lv_medicare_levy);
4368 FETCH csr_get_etp_tax into l_etp_tax_excluded_ytd1;
4369 CLOSE csr_get_etp_tax;
4370
4371 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,(l_etp_excluded_prev+p_etp_included_under_ytd+l_tot_excluded_prev+l_tot_included_prev),lv_medicare_levy);
4372 FETCH csr_get_etp_tax into l_etp_tax_excluded_prev1;
4373 CLOSE csr_get_etp_tax;
4374
4375 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,(l_excluded_run+l_etp_excluded_prev+p_etp_included_under_ytd+l_tot_excluded_prev+l_tot_included_prev),lv_medicare_levy);
4376 FETCH csr_get_etp_tax into l_etp_tax_excluded_ytd2;
4377 CLOSE csr_get_etp_tax;
4378
4379 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,l_etp_cap,lv_medicare_levy);
4380 FETCH csr_get_etp_tax into l_etp_tax_excluded_prev2;
4381 CLOSE csr_get_etp_tax;
4382
4383 IF g_debug THEN
4384 hr_utility.set_location('l_etp_tax_excluded_ytd1 '||l_etp_tax_excluded_ytd1, 93);
4385 hr_utility.set_location('l_etp_tax_excluded_prev1 '||l_etp_tax_excluded_prev1, 93);
4386 hr_utility.set_location('l_etp_tax_excluded_ytd2 '||l_etp_tax_excluded_ytd2, 93);
4387 hr_utility.set_location('l_etp_tax_excluded_prev2 '||l_etp_tax_excluded_prev2, 93);
4388 END IF;
4389
4390 l_etp_tax_excluded_ytd := l_etp_tax_excluded_ytd1 + l_etp_tax_excluded_ytd2;
4391 l_etp_tax_excluded_prev := l_etp_tax_excluded_prev1 + l_etp_tax_excluded_prev2;
4392
4393 END IF;
4394
4395 ELSE
4396
4397 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,(l_etp_cap+l_excluded_run),lv_medicare_levy);
4398 FETCH csr_get_etp_tax into l_etp_tax_excluded_ytd;
4399 CLOSE csr_get_etp_tax;
4400
4401 OPEN csr_get_etp_tax(p_business_group_id,p_date_paid,ln_user_table,l_etp_cap,lv_medicare_levy);
4402 FETCH csr_get_etp_tax into l_etp_tax_excluded_prev;
4403 CLOSE csr_get_etp_tax;
4404
4405 IF g_debug THEN
4406 hr_utility.set_location('l_etp_tax_excluded_ytd '||l_etp_tax_excluded_ytd, 94);
4407 hr_utility.set_location('l_etp_tax_excluded_prev '||l_etp_tax_excluded_prev, 94);
4408 END IF;
4409
4410 END IF;
4411
4412 IF l_tot_excluded_prev = 0 THEN
4413 l_etp_tax_excluded := l_etp_tax_excluded_ytd - l_etp_tax_excluded_prev;
4414 if g_debug then hr_utility.set_location('l_etp_tax_excluded '||l_etp_tax_excluded, 91); end if;
4415 ELSE
4416 l_etp_tax_excluded_pp := l_etp_tax_excluded_ytd - l_etp_tax_excluded_prev;
4417 if g_debug then hr_utility.set_location('l_etp_tax_excluded_pp '||l_etp_tax_excluded_pp, 91); end if;
4418 END IF;
4419
4420
4421 END IF;
4422
4423 END IF; -- Excluded
4424
4425 IF l_included_run <> 0 THEN
4426
4427 /* The whole-of-income cap is not reduced by any ETPs received in earlier years,
4428 so l_tot_included_prev is not deducted */
4429 if l_etp_whole_income_cap - l_current_pay_ytd - l_etp_included_prev < 0 then
4430 l_etp_whole_income_cap := 0;
4431 else
4432 l_etp_whole_income_cap := l_etp_whole_income_cap - l_current_pay_ytd - l_etp_included_prev;
4433 end if;
4434
4435 if g_debug then hr_utility.set_location('l_etp_whole_income_cap '||l_etp_whole_income_cap, 101); end if;
4436
4437 if l_etp_cap - l_tot_excluded_prev - l_tot_included_prev - l_excluded_ytd - p_etp_included_under_ytd < 0 then
4438 l_etp_cap := 0 ;
4439 else
4440 l_etp_cap := l_etp_cap - l_tot_excluded_prev - l_tot_included_prev - l_excluded_ytd - p_etp_included_under_ytd;
4441 end if;
4442
4443 if g_debug then hr_utility.set_location('l_etp_cap '||l_etp_cap, 101); end if;
4444
4445 if l_etp_whole_income_cap <= l_etp_cap then
4446 l_new_etp_cap := l_etp_whole_income_cap;
4447 else
4448 l_new_etp_cap := l_etp_cap ;
4449 end if;
4450
4451 if g_debug then hr_utility.set_location('l_new_etp_cap '||l_new_etp_cap, 101); end if;
4452
4453 IF l_included_run <= l_new_etp_cap THEN
4454 l_etp_tax_included:= round(l_included_run * (l_included_rate_prev_age + lv_medicare_levy));
4455 l_included_under_cap := l_included_run;
4456 ELSE
4457 l_etp_tax_included := round(l_new_etp_cap * (l_included_rate_prev_age + lv_medicare_levy)) +
4458 round((l_included_run - l_new_etp_cap) * (l_etp_included_rate_all_age + lv_medicare_levy));
4459 l_included_under_cap := l_new_etp_cap;
4460 END IF;
4461
4462 IF l_tot_included_prev <> 0 THEN
4463 l_etp_tax_included_pp := l_etp_tax_included;
4464 l_etp_tax_included := 0;
4465
4466 if g_debug then hr_utility.set_location('l_etp_tax_included_pp '||l_etp_tax_included_pp, 102); end if;
4467
4468 ELSE
4469
4470 if g_debug then hr_utility.set_location('l_etp_tax_included '||l_etp_tax_included, 102); end if;
4471
4472 END IF;
4473
4474 END IF; -- Included
4475
4476 END IF;
4477
4478 END IF;
4479
4480 p_etp_tax_excluded := l_etp_tax_excluded;
4481 p_etp_tax_excluded_pp := l_etp_tax_excluded_pp;
4482 p_etp_tax_included := l_etp_tax_included;
4483 p_etp_tax_included_pp := l_etp_tax_included_pp;
4484 p_included_under_cap := l_included_under_cap;
4485
4486
4487
4488 IF g_debug THEN
4489 hr_utility.set_location('p_etp_tax_excluded '||p_etp_tax_excluded, 999);
4490 hr_utility.set_location('p_etp_tax_excluded_pp '||p_etp_tax_excluded_pp, 999);
4491 hr_utility.set_location('p_etp_tax_included '||p_etp_tax_included, 999);
4492 hr_utility.set_location('p_etp_tax_included_pp '||p_etp_tax_included_pp, 999);
4493 hr_utility.set_location('p_included_under_cap '||p_included_under_cap, 999);
4494 hr_utility.set_location('Leaving '||l_procedure, 999);
4495 END IF;
4496
4497 RETURN 0;
4498
4499 END calculate_etp_tax_2012;
4500
4501
4502 end pay_au_terminations;
[Home] [Help]