1 PACKAGE BODY pay_au_paye_ff AS
2 /* $Header: pyaufmla.pkb 120.32.12010000.5 2008/10/08 22:55:00 skshin ship $
3 **
4 ** Copyright (c) 1999 Oracle Corporation
5 ** All Rights Reserved
6 **
7 ** Procedures and functions used in AU tax calculations
8 **
9 ** Change List
10 ** ===========
11 **
12 ** Date Author Reference Description
13 ** =========================================================
14 ** 24-SEP-1999 makelly 115.0 Created for AU
15 ** 31-JAN-2000 sclarke 115.1 Termination Taxation added
16 ** 03-MAR-2000 sclarke 115.2 Changes to Terminations after testing
17 ** 22-MAR-2000 sclarke 115.3 Changed calculate_marginal_tax to use
18 ** correct amount when tax variation is
19 ** percentage.
20 ** 16-SEP-2000 sclarke 115.1 Removed Terminations, now in pay_au_terminations
21 ** 20-DEC-2000 srikrish 115.5 Created function paid_periods_since_hire_date
22 ** which returns number of paid pay periods
23 ** 19-DEC-2000 abajpai 115.6 Added new function convert_to_period_amt, round_amount
24 ** 02-JAN-2001 abajpai 115.8 Added new parameter tax_scale for convert_to_period_amt, round_amount
25 ** function to check for existance of another current
26 ** payroll assignment for the employee
27 **
28 ** Package containing addition processing required by
29 ** formula in AU localisatons.
30 **
31 ** round_to_5c = rounds values to nearest 5c using
32 ** ATO rules
33 ** 28-NOV-2001 115.10 Updated for GSCC Standards
34 ** 07-DEC-2001 rsirigir 115.12 Update for GSCC Standards, added
35 ** REM checkfile:~PROD:~PATH:~FILE
36 ** 8-JAN-2002 apunekar 115.13 Added new functions
37 ** 18-May-2002 apunekar 115.14 Added new function
38 ** 20-May-2002 apunekar 115.15 Updated function due to review
39 ** 13-Jun-2002 nanuradh 115.21 Changed the cursor get_retro_period_ee (Bug 2415213)
40 ** 10-Jul-2002 srussell 115.22 Change periods_since_hire_date to allow for
41 ** payrolls which go across financial year.
42 ** Bug 2450059.
43 ** 06-Aug-2002 shoskatt 115.23 Cursor check_fixed_deduction has been tuned for
44 ** improving performance. Bug #2491328
45 ** 17-Sep-2002 Ragovind 115.24 Modified the cursor check_fixed_deduction for performance. Bug#2563515
46 ** 03-Dec-2002 Ragovind 115.25 Added NOCOPY for the function get_retro_period.
47 ** 14-Apr-2003 Vgsriniv 115.26 Modified the function periods_since_hire_date. Bug:2900253
48 ** 19-Aug-2003 punmehta 115.27 Modified conversion functions to support Quarterly Payroll. Bug:2888114
49 ** 22-Aug-2003 srrajago 115.28 Added the new function 'validate_data_magtape'. Refer to Bug no : 3091834
50 ** This function will be used by Payment Summary Data File.
51 ** 26-Aug-2003 srrajago 115.29 Modified function 'validate_data_magtape'.If the return value is null then
52 ** space is returned.
53 ** 27-Aug-2003 srrajago 115.30 Function 'validate_data_magtape' has been modified to return ' '(space)
54 ** if value of the input string 'p_data' passed is Null.
55 ** 03-Nov-2003 punmehta 115.31 Bug# 2977425 - Added the new formula function
56 ** 19-Nov-2003 punmehta 115.32 Bug# 2977425 - Modified message name.
57 ** 11-Dec-2003 jkarouza 115.33 Bug# 3172950 - Removed blank spaces from addresses when two or more
58 ** spaces between words.
59 ** 23-Dec-2003 punmehta 115.34 Bug# 3306112 - Added the new formula function
60 ** 24-Dec-2003 punmehta 115.35 Bug# 3306112 - Used cursor in the new function 'get_salary_basis_hours'
61 ** 06-Feb-2004 punmehta 115.36 Bug# 3245909 - Added a new function get_pp_action for AU_Payments route
62 ** 09-Feb-2004 punmehta 115.37 Bug# 3245909 - Coding Standards in get_pp_action
63 ** 02-FEB-2004 abhkumar 115.38 Bug# 3665680 - Coding Standards in Cr_element_type_id
64 ** 02-FEB-2004 abhkumar 115.39 Bug# 3665680 - Modfied Code to remove gscc warnings
65 ** 07-JUL-2004 srrajago 115.40 Bug# 3724089 - Modified the cursor 'c_get_unprocessed_periods_num' to include table
66 ** per_assignments_f and its joins in the sub-query - Performance Fix.
67 ** 09-AUG-2004 abhkumar 115.41 Bug# 2610141 - Modfied the code to support Legal Employer changes for an assignment.
68 ** 12-AUG-2004 abhkumar 115.42 Bug# 2610141 - Modfied the code to use cursors instead of select query
69 ** 08-SEP-2004 abhkumar 115.43 Bug# 2610141 - Added a flag "p_use_tax_flag" to function periods_since_hire_date and paid_periods_since_hire_date
70 ** to support the versioning of the payroll tax formula.
71 *** 26-Apr-2005 abhkumar 115.44 Bug#3935471 - Changes due to Retro Tax enhancement.
72 *** 05-May-2005 abhkumar 115.45 Bug#3935471 - File Modified to put proper comments.
73 *** 10-May-2005 abhkumar 115.46 Bug#4357306 - Modified function count_retro_periods.
74 *** 06-Jun-1005 srussell 115.47 Bug#4412537 - Modified count_retro_periods
75 so that INDEX BY is binary_integer, not varchar2 so that it
76 doesnt get compile error on 8.1.7.4 data bases.
77 *** 06-Jun-1005 srussell 115.48 Bug#4412537 - Updated comments.
78 *** 06-Jun-2005 avenkatk 115.49 Bug#4412537 - Changed to_number to to_number(to_char()) to get l_retro_end_date.
79 *** 06-Jun-2005 avenkatk 115.50 Bug#4412537 - Removed commented code and Removed redundant to_date() to resolve gscc errors.
80 *** 07-Jun-2005 abhkumar 115.51 Bug#4415795 - Added new parameter to function count_retro_periods.
81 *** 23-Jun-2005 abhkumar 115.52 Bug#4438644 - Modified function paid_periods_since_hire_date
82 *** 26-Jun-2005 avenkatk 115.53 Bug#4451088 - Modified function periods_since_hire_date
83 *** 26-Jun-2005 avenkatk 115.54 Bug#4451088 - Removed the trace fucntion call.
84 *** 27-Jun-2005 abhkumar 115.55 Bug#4456941 - Modified function count_retro_periods
85 *** 27-Jun-2005 ksingla 115.56 Bug#4456720 - Added a new function CALCULATE_ASG_PREV_VALUE for negative retro earnings
86 *** 05-JuL-2005 abhkumar 115.57 Bug#4467198 - Modified function CALCULATE_ASG_PREV_VALUE for zero average earnings
87 *** 05-JuL-2005 abhkumar 115.58 Bug#4467198 - Modified cursor c_get_paid_periods and c_check_payroll_run for performance fix.
88 *** 13-Jul-2005 abhargav 115.59 Bug#4363057 - Modified function CALCULATE_ASG_PREV_VALUE to include fix for bug# 3855355 .
89 *** 14-Jul-2005 abhkumar 115.60 Bug#4418107 - Modified function count_retro_periods and get_retro_periods to consider Legal Employer changes
90 *** 08-Aug-2005 abhargav 115.62 Bug#4521653 - Modified the function CALCULATE_ASG_PREV_VALUE .
91 *** 01-SEP-2005 abhkumar 115.63 Bug#4474896 - Average Earnings enhancement
92 *** 09-Sep-2005 avenkatk 115.64 Bug#4374115 - Added check in check_fixed_deduction for Reverse Runs.
93 *** 05-Oct-2005 abhargav 115.65 Bug#4588483 - Modified Cursor check_fixed_deduction.
94 *** 05-Jul-2006 srussell 115.66 Bug#5374076 - Modified function count_retro_periods to check the retro amounts for each
95 *** period. If they're zero then don't count the period.
96 ***10-JUL-2006 hnainani 115.67 Bug#5371901 Removed Date_Earned check from function Periods_Since_Hire_date to force
97 *** code to use Effective Date to calculate numberof periods
98 *** 11-JUl-2006 hnainani 115.68 Bug#5371901 Modified Comments in function Periods_Since_Hire_Date to correctly
99 **
100 ** reflect reason for changes.
101 *** 19-Jul-2006 hnainani 115.69 Bug#5397711 Changed tot_period_amount_type in Countr_Retro_Periods to Number
102 instead of Number(10) to cater for decimals
103 *** 09-Oct-2006 avenkatk 115.71 Bug#5586445 Included function get_enhanced_retro_period.
104 *** 01-Dec-2006 priupadh 115.72 Bug#5676709 Added debug messages to functions
105 *** 01-Dec-2006 priupadh 115.73 Bug#5676709 removed the occurence of to_date and dd-mon-yyyy format from debug message for GSCC compliance.
106 *** 17-Jan-2006 avenkatk 115.74 Bug#5846272 Introduced new functions,
107 ** i. check_if_enhanced_retro
108 ** ii. get_retro_time_span
109 ** 16-FEB-2006 priupadh 115.75 N/A Version to restore triple maintanence between 11i,R12 Branch and R12 Mainline
110 ** 10-Apr-2007 abhargav 115.77 Bug#5934468 Added new function get_spread_earning() this function gets called from
111 formula AU_HECS_DEDUCTION and AU_SFSS_DEDUCTION.
112 ** 18-Apr-2007 avenkatk 115.78 Bug#6001930 Modified Function periods_since_hire_date for
113 ** Postive Offset Payrolls.
114 ** 18-Jun-2007 avenkatk 115.79 Bug#6139035 Modified Function count_retro_periods and get_enhanced_retro_perio - Function
115 ** modified to mark retro time spans based on Date Paid(Effective Date) of
116 ** Payroll run/Quickpay.
117 ** 17-Jan-2008 skshin 115.80 Bug#6669058 Modified function get_spread_earning and added new function get_retro_spread_earning.
118 ** 18-FEB-2008 skshin 115.81 Bug#6809877 Added new function get_etp_pay_component.
119 ** 09-OCT-2008 skshin 115.83 Bug#7228256 Removed DISTINCT from c_get_le_period_num cursor
120 */
121
122 g_debug boolean;
123
124 function round_to_5c
125 (
126 p_actual_amt in number
127 )
128 return number is
129
130
131 l_cents number;
132 l_rnd_amt number;
133
134
135 begin
136 g_debug := hr_utility.debug_enabled;
137 l_cents := p_actual_amt - trunc(p_actual_amt,1);
138
139 if l_cents <= 0.025 then
140 l_rnd_amt := 0;
141 elsif l_cents > 0.075 then
142 l_rnd_amt := 0.1;
143 else
144 l_rnd_amt := 0.05;
145 end if;
146
147 return (trunc(p_actual_amt,1) + l_rnd_amt);
148
149 exception
150 when others then
151 null;
152
153 end round_to_5c;
154
155
156 /*
157 * convert_to_period - converts weekly equivalents
158 * back to the period amounts using ATO rules.
159 */
160
161 function convert_to_period
162 (
163 p_ann_freq in number,
164 p_amt_week in number
165 )
166 return number is
167
168 l_amt_period number;
169
170 begin
171 g_debug := hr_utility.debug_enabled;
172 if p_ann_freq = 52 then
173 l_amt_period := p_amt_week;
174 elsif p_ann_freq = 26 then
175 l_amt_period := (p_amt_week * 2);
176 elsif p_ann_freq = 24 then
177 l_amt_period := round_to_5c (p_amt_week * 13 / 6);
178 elsif p_ann_freq = 12 then
179 l_amt_period := round_to_5c (p_amt_week * 13 / 3);
180 elsif p_ann_freq = 4 then /*Bug : 2888114*/
181 l_amt_period := p_amt_week * 13;
182 end if;
183
184 return (l_amt_period);
185
186 exception
187 when others then
188 null;
189
190 end convert_to_period;
191
192
193 /*
194 * convert_to_week - converts period amounts to equivalents
195 * weekly equivalents using ATO rules.
196 */
197
198 function convert_to_week
199 (
200 p_ann_freq in number,
201 p_amt_period in number
202 )
203 return number is
204
205 l_amt_week number := 0;
206 l_new_amt number;
207
208 begin
209 g_debug := hr_utility.debug_enabled;
210 if p_ann_freq = 52 then
211 l_amt_week := trunc (p_amt_period) + 0.99;
212 elsif p_ann_freq = 26 then
213 l_amt_week := trunc (p_amt_period / 2) + 0.99;
214 elsif p_ann_freq = 24 then
215 if (p_amt_period - trunc (p_amt_period)) = 0.33 then
216 l_new_amt := p_amt_period + 0.01;
217 else
218 l_new_amt := p_amt_period;
219 end if;
220 l_amt_week := trunc (l_new_amt * 6 / 13) + 0.99;
221 elsif p_ann_freq = 12 then
222 if (p_amt_period - trunc (p_amt_period)) = 0.33 then
223 l_new_amt := p_amt_period + 0.01;
224 else
225 l_new_amt := p_amt_period;
226 end if;
227 l_amt_week := trunc (l_new_amt * 3 / 13) + 0.99;
228 elsif p_ann_freq = 4 then /*Bug : 2888114*/
229 l_amt_week := trunc (p_amt_period/13) + 0.99;
230 end if;
231
232 return (l_amt_week);
233
234 exception
235 when others then
236 null;
237
238 end convert_to_week;
239
240
241 /*
242 * periods_since_hire_date - returns the number of periods in the
243 * current tax year since the hire date.
244 */
245
246 function periods_since_hire_date
247 (
248 p_payroll_id in number,
249 p_assignment_id in per_all_assignments_f.assignment_id%type,
250 p_tax_unit_id in pay_assignment_actions.tax_unit_id%type, --2610141
251 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type, /*Bug 4451088 */
252 p_period_num in number,
253 p_period_start in date,
254 p_emp_hire_date in date,
255 p_use_tax_flag IN VARCHAR2 --2610141
256 )
257 return number is
258
259 l_year_start date;
260 l_month_no number;
261 l_year number;
262 l_period_num number;
263 /* Bug#2900253 */
264 l_leg_emp_date date;
265 l_check_date date;
266 l_period_end date;
267 l_procedure varchar2(80);
268 /* Bug:2900253 Added following cursor to get the date on which this assignment is
269 Enrolled into the legal employer existing as of this period start date */
273 cursor get_legal_emp_start_date (c_assignment_id per_all_assignments_f.assignment_id%type) is
270
271 /*Bug 4474896 - Modified cursor to pick the correct effective start date for the legal employer*/
272
274 select min(effective_start_date)
275 from per_all_assignments_f paf,
276 hr_soft_coding_keyflex hsck
277 where paf.assignment_id = c_assignment_id
278 and paf.SOFT_CODING_KEYFLEX_ID = hsck.soft_coding_keyflex_id
279 and hsck.segment1 = p_tax_unit_id
280 AND paf.effective_start_date <= l_period_end
281 AND paf.effective_end_date >= l_year_start;
282
283 /*Bug 2610141 - Cursor added to get the number of periods in the current year for an assignment assigned to a legal
284 employer */
285
286 /*Bug# 4474896 - Cursor modified to count periods on the basis of current payroll id
287 Bug# 6001930 - Modified Cursor for postive offset payrolls. Now periods will be
288 counted based on the Regular Payment Date. */
289
290 cursor c_get_le_period_num is
291 select count(ptp1.time_period_id) /*Bug 4438644, 6001930, 7228256*/
292 from per_time_periods ptp
293 ,per_time_periods ptp1
294 where exists (select 'EXISTS' from
295 per_assignments_f paf,
296 hr_soft_coding_keyflex hsck
297 where paf.assignment_id = p_assignment_id
298 and paf.SOFT_CODING_KEYFLEX_ID = hsck.soft_coding_keyflex_id
299 and hsck.segment1 = p_tax_unit_id
300 AND paf.effective_start_date <= l_period_end
301 AND paf.effective_end_date >= l_year_start
302 AND paf.effective_start_date <= ptp.end_date
303 AND paf.effective_end_date >= ptp.start_date)
304 AND ptp.payroll_id = p_payroll_id
305 AND ptp.start_date <= l_period_end
306 -- AND ptp.end_date >= l_year_start /* Commented Bug 6001930 */
307 /* Bug 6001930 - Start Changes */
308 AND ptp.regular_payment_date >= l_year_start
309 AND ptp.payroll_id = ptp1.payroll_id
310 AND ptp.regular_payment_date BETWEEN ptp1.start_date AND ptp1.end_date
311 -- AND ptp1.start_date >= l_year_start /* Commented Bug 6139035 */
312 AND ptp1.end_date <= l_period_end;
313 /* Bug 6001930 - End Changes */
314
315
316 cursor c_get_period_num (v_payroll_id number,
317 v_hire_date date) is
318 select period_num
319 from per_time_periods
320 where payroll_id = v_payroll_id
321 and v_hire_date between start_date and end_date;
322
323
324 /* Bug 2610141 - Cursor to Get the Period END date
325 Bug 4451088 - Changed Cursor to return end dates based in Input Date
326 */
327
328 CURSOR c_get_period_end_date(c_date date)
329 IS
330 select ptp.end_date
331 from per_time_periods ptp
332 where ptp.payroll_id = p_payroll_id
333 and c_date between
334 ptp.start_date and ptp.end_date;
335
336 /* Bug 4451088 - The following cursors have been introduced */
337
338 l_date_earned date;
339 l_effective_date date;
340 l_date_earn_yr_start date;
341
342
343 CURSOR csr_get_pay_dates
344 IS
345 select ppa.date_earned,
346 ppa.effective_date
347 from pay_payroll_actions ppa,
348 pay_assignment_actions paa
349 where ppa.payroll_action_id = paa.payroll_action_id
350 and paa.assignment_action_id = p_assignment_action_id;
351
352
353 cursor get_period_num(c_start_date date,
354 c_end_date date)
355 is
356 select count(*)
357 from per_time_periods ptp
358 where payroll_id = p_payroll_id
359 and ptp.end_date
360 between c_start_date and c_end_date;
361
362 begin
363 g_debug := hr_utility.debug_enabled;
364
365 if g_debug then
366 l_procedure :='pay_au_paye_ff.periods_since_hire_date';
367 hr_utility.set_location('Entering '||l_procedure,10);
368 hr_utility.set_location('IN p_payroll_id '||p_payroll_id,20);
369 hr_utility.set_location('IN p_assignment_id '||p_assignment_id,25);
370 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,30);
371 hr_utility.set_location('IN p_assignment_action_id'||p_assignment_action_id,35);
372 hr_utility.set_location('IN p_period_num '||p_period_num,40);
373 hr_utility.set_location('IN p_period_start '||to_char(p_period_start,'dd/mm/yyyy'),45);
374 hr_utility.set_location('IN p_emp_hire_date '||to_char(p_emp_hire_date,'dd/mm/yyyy'),50);
375 hr_utility.set_location('IN p_use_tax_flag '||p_use_tax_flag,55);
376
377 end if;
378
379 l_check_date := p_emp_hire_date;
380 /* Bug# 2450059 Always return 1 if payroll period is 1 */
381
382 /* Bug 4451088 - Introduced the following logic to get number of period ,
383 1. IF Date Earrned and Eff Date in Same Fin Year,
384 Start Date : Financial Year Start Date (Effective Date)
385 End Date : End Date ( Date Earned)
386 2. IF Date Earrned and Eff Date in Different Fin Year,
387 Start Date : Financial Year Start Date (Effective Date)
388 End Date : End Date ( Effective Date)
389
390 Count No of periods in per_time_periods between Start Date/End Date
391 */
392
393 /* Bug# 5371901 - Changed the above logic to take out "Date_Earned" if Condition
397 their Effective Date still falls in the current Year (due to Offsets) -
394 and instead use only "Effective Date"
395 Checked with PM , In AU for all Payments we should only look at Effective Date .
396 We can have cases wherein the Date_Earned Falls in the Previous Year -
398
399 e.g Pay Period : 17-Jun-2006 to 30-JUN-2006 (Offsets +6)
400 Date_Earned :- 30-JUN-2006
401 Effective Date:- 06-JUL-2006
402
403 This means that the Earnings should be considered for the Current Year.
404 */
405
406
407 open csr_get_pay_dates;
408 fetch csr_get_pay_dates into l_date_earned,l_effective_date;
409 close csr_get_pay_dates;
410
411 l_month_no := to_number(to_char(l_effective_date,'MM'));
412 l_year := to_number(to_char(l_effective_date,'YYYY'));
413
414 if l_month_no > 6 then
415 l_year_start := to_date('01-07-'||to_char(l_year),'DD-MM-YYYY');
416 else
417 l_year_start := to_date('01-07-'||to_char(l_year - 1),'DD-MM-YYYY');
418 end if;
419
420 l_month_no := to_number(to_char(l_date_earned,'MM'));
421 l_year := to_number(to_char(l_date_earned,'YYYY'));
422
423 if l_month_no > 6 then
424 l_date_earn_yr_start := to_date('01-07-'||to_char(l_year),'DD-MM-YYYY');
425 else
426 l_date_earn_yr_start := to_date('01-07-'||to_char(l_year - 1),'DD-MM-YYYY');
427 end if;
428
429 /* Bug# 5371901 */
430 /* if (l_year_start = l_date_earn_yr_start)
431 then
432 open c_get_period_end_date(l_date_earned);
433 fetch c_get_period_end_date into l_period_end;
434 close c_get_period_end_date;
435 else*/
436 open c_get_period_end_date(l_effective_date);
437 fetch c_get_period_end_date into l_period_end;
438 close c_get_period_end_date;
439 -- end if;
440
441 /* Bug:2900253 Get the Legal Employer start date. If the employee
442 changes Legal Employer then, Period number should be counted
443 starting from Legal Employer start date instead of Hire Date */
444
445 open get_legal_emp_start_date(p_assignment_id);
446 fetch get_legal_emp_start_date into l_leg_emp_date;
447 close get_legal_emp_start_date;
448
449 if l_leg_emp_date > p_emp_hire_date then
450 l_check_date := l_leg_emp_date;
451 else
452 l_check_date := p_emp_hire_date;
453 end if;
454
455 /* End of Bug:2900253 */
456
457 IF p_use_tax_flag = 'N' THEN
458
459 open get_period_num(greatest(l_check_date,l_year_start),
460 l_period_end);
461 fetch get_period_num into l_period_num;
462 close get_period_num;
463
464 if g_debug then
465 hr_utility.trace('Value to be returned l_period_num =='||l_period_num);
466 hr_utility.set_location('Exiting '||l_procedure,60);
467 end if;
468
469 return l_period_num;
470
471 ELSE
472 open c_get_le_period_num; /*Bug 2610141 - It will give us the correct pay periods for a particular le
473 legal employer change has taken place in the year.*/
474 fetch c_get_le_period_num into l_period_num;
475 close c_get_le_period_num;
476
477 if g_debug then
478 hr_utility.trace('Value to be returned l_period_num =='||l_period_num);
479 hr_utility.set_location('Exiting '||l_procedure,60);
480 end if;
481
482 return l_period_num;
483 end if;
484
485 exception
486 when others then
487 null;
488
489 end periods_since_hire_date;
490
491
492
493 /* Bug 4456720 - Added a new function to calculate the earnings_total and
494 per tax spread deductions for the previous year when total average earnings are negative */
495 /* Bug#4467198 - Modified the function to take care of legal employer changes. Introduced following
496 parameters in the function p_use_tax_flag, p_payroll_id, p_assignment_action_id*/
497 FUNCTION calculate_asg_prev_value
498 ( p_assignment_id in per_all_assignments_f.assignment_id%TYPE,
499 p_business_group_id in hr_all_organization_units.organization_id%TYPE,
500 p_date_earned in date,
501 p_tax_unit_id in hr_all_organization_units.organization_id%TYPE,
502 p_assignment_action_id IN number,
503 p_payroll_id IN NUMBER,
504 p_period_start_date in date,
505 p_case out NOCOPY varchar2,
506 p_earnings_standard out NOCOPY number,
507 p_pre_tax_spread out NOCOPY number,
508 p_pre_tax_fixed out NOCOPY number, /*bug4363057*/
509 p_pre_tax_prog out NOCOPY number, /*bug4363057*/
510 p_paid_periods out NOCOPY number,
511 p_use_tax_flag IN VARCHAR2 --2610141
512 )
513 return NUMBER is
514 -----------------------------------------------------------------------
515 -- Variables
516 -----------------------------------------------------------------------
517 g_debug boolean;
518 l_procedure varchar2(80);
519
520 -- This year Financial Start and End Dates
521 --
522 l_fin_start_date date;
523 l_fin_end_date date;
524
525 -- Last Year Financial Start and End Dates
526 --
530
527 l_prev_yr_fin_start_date date ;
528 l_prev_yr_fin_end_date date ;
529 l_eff_date DATE; /* Bug#4467198*/
531
532 -- Variable to store the maximum previous year assignment action id and its corresponding
533 -- tax_unit_id (legal Employer).
534 l_asg_act_id pay_assignment_actions.assignment_action_id%TYPE;
535 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
536
537
538 -- Total Earnings variable
539 --
540 l_total_earnings number;
541
542 -- Loop Counter variable
543 --
544 i number;
545
546
547 -----------------------------------------------------------------------
548 -- Cursor : c_get_prev_year_max_asg_act_id
549 -- Description : To get the Previous Year Maximum Assignment Action ID
550 -- for a given Assignment_id in a Financial Year.
551 -- If there exists any LE changes, then it gets the max
552 -- Assignment Action ID .
553 -----------------------------------------------------------------------
554 CURSOR c_get_prev_year_max_asg_act_id
555 ( c_assignment_id in per_all_assignments_f.assignment_id%TYPE,
556 c_business_group_id in hr_all_organization_units.organization_id%TYPE,
557 c_fin_start_date in date,
558 c_fin_end_date in date)
559 IS
560 SELECT paa.assignment_action_id, paa.tax_unit_id, ppa.payroll_id
561 FROM pay_assignment_actions paa
562 ,pay_payroll_actions ppa
563 WHERE paa.assignment_id = c_assignment_id
564 and ppa.payroll_action_id = paa.payroll_action_id
565 and ppa.business_group_id = c_business_group_id
566 AND paa.action_sequence in
567 (
568 SELECT MAX(paa.action_sequence)
569 FROM pay_assignment_actions paa,
570 pay_payroll_actions ppa,
571 per_all_assignments_f paaf
572 WHERE ppa.business_group_id = c_business_group_id
573 AND paaf.assignment_id = c_assignment_id
574 AND paa.assignment_id = paaf.assignment_id
575 AND paa.action_status='C'
576 AND ppa.payroll_action_id = paa.payroll_action_id
577 AND ppa.action_type in ('Q','R','B','I','V') --2610141
578 AND ppa.effective_date between c_fin_start_date AND c_fin_end_date /*4521653 replaced the date_earned with effective date*/
579 AND paa.tax_unit_id = p_tax_unit_id --2610141
580 )
581 ORDER BY date_earned desc;
582
583
584 CURSOR c_get_pre_le_max_asg_act_id
585 ( c_assignment_id in per_all_assignments_f.assignment_id%TYPE,
586 c_business_group_id in hr_all_organization_units.organization_id%TYPE,
587 c_fin_start_date in date,
588 c_fin_end_date in date)
589 IS
590 SELECT paa.assignment_action_id, paa.tax_unit_id, ppa.payroll_id, ppa.effective_date
591 FROM pay_assignment_actions paa
592 ,pay_payroll_actions ppa
593 WHERE paa.assignment_id = c_assignment_id
594 and ppa.payroll_action_id = paa.payroll_action_id
595 and ppa.business_group_id = c_business_group_id
596 AND paa.action_sequence in
597 (
598 SELECT MAX(paa.action_sequence)
599 FROM pay_assignment_actions paa,
600 pay_payroll_actions ppa,
601 per_all_assignments_f paaf
602 WHERE ppa.business_group_id = c_business_group_id
603 AND paaf.assignment_id = c_assignment_id
604 AND paa.action_status='C'
605 AND paa.assignment_id = paaf.assignment_id
606 AND ppa.payroll_action_id = paa.payroll_action_id
607 AND ppa.action_type in ('Q','R','B','I','V')
608 AND ppa.effective_date between c_fin_start_date AND c_fin_end_date /*4521653 replaced the date_earned with effective date*/
609 )
610 ORDER BY date_earned desc;
611
612
613 ---
614 -----------------------------------------------------------------------
615 -- Cursor : c_get_paid_period_no_prev_year
616 -- Description : To get the Previous Year number of periods paid to the
617 -- given Assignment_id in previous Financial Year
618 /* Bug#4467198 - Modified the name of cursor to c_get_paid_periods
619 Logic of the cursor has been changed to pick correct paid
620 periods on the basis of the current payroll id*/
621 -----------------------------------------------------------------------
622
623
624 /*Bug 4474896 - Cursor c_get_paid_periods changed to c_get_periods and logic for the cursor modified
625 to count number of pay periods between greatest of (employee's hire date, financial year start date,
626 Legal Employer start date) and current period end date*/
627
628 cursor c_get_periods
629 (c_tax_unit_id in hr_all_organization_units.organization_id%TYPE,
630 c_payroll_id in pay_payrolls_f.payroll_id%TYPE,
631 c_start_date in date,
632 c_end_date in date)
633 is
634 select count(DISTINCT ptp.time_period_id)
635 from per_time_periods ptp
636 where exists (select 'EXISTS' from
637 per_assignments_f paf,
638 hr_soft_coding_keyflex hsck
639 where paf.assignment_id = p_assignment_id
640 and paf.SOFT_CODING_KEYFLEX_ID = hsck.soft_coding_keyflex_id
641 and hsck.segment1 = c_tax_unit_id
642 AND paf.effective_start_date <= c_end_date
643 AND paf.effective_end_date >= c_start_date
644 AND paf.effective_start_date <= ptp.end_date
645 AND paf.effective_end_date >= ptp.start_date)
646 AND ptp.payroll_id = c_payroll_id
647 AND ptp.start_date <= c_end_date
648 AND ptp.end_date >= c_start_date;
652 CURSOR c_get_payroll_effective_date
649
650 /* Bug#4467198 - Cursor below gives the payroll effective date for the current payroll run*/
651
653 IS
654 SELECT ppa.effective_date
655 FROM pay_payroll_actions ppa,
656 pay_assignment_actions paa
657 WHERE paa.assignment_action_id = p_assignment_action_id
658 AND ppa.payroll_action_id = paa.payroll_action_id;
659
660 /* Bug#4467198 - Use the below cursor to check if this is the first for the assignment in this year*/
661
662 CURSOR c_check_payroll_run (c_assignment_id in per_all_assignments_f.assignment_id%TYPE,
663 c_business_group_id in hr_all_organization_units.organization_id%TYPE,
664 c_start_date in date,
665 c_end_date in date)
666 IS
667 SELECT count(paa.assignment_action_id)
668 FROM pay_assignment_actions paa,
669 pay_payroll_actions ppa,
670 per_assignments_f paf
671 WHERE ppa.effective_date BETWEEN c_start_date AND c_end_date
672 AND ppa.business_group_id = c_business_group_id
673 AND ppa.payroll_action_id = paa.payroll_action_id
674 AND paa.assignment_id = c_assignment_id
675 AND paa.assignment_id = paf.assignment_id
676 AND ppa.effective_date between paf.effective_start_date and paf.effective_end_date
677 AND paa.action_status = 'C'
678 AND paa.source_action_id IS NULL /*Bug 4418107 - This join added to only pick master assignment action id*/
679 AND ppa.action_type IN ('Q','R');
680
681
682
683 c_ytd_input_table c_get_ytd_def_bal_ids%rowtype;
684 l_use_le_balances varchar2(50);
685 l_db_item_suffix pay_balance_dimensions.database_item_suffix%type;
686 l_payroll_id number;
687 l_pay_eff_date DATE;
688 l_flag number;
689 l_counter number;
690
691 BEGIN
692
693
694 g_ytd_def_bals_populated := FALSE;
695 l_flag := -1;
696 p_earnings_standard := 0;
697 p_pre_tax_spread := 0;
698 p_pre_tax_fixed := 0; /*bug4363057*/
699 p_pre_tax_prog := 0;
700 i := 1;
701 p_case :='USE_PREV_EARNINGS';
702 g_debug := hr_utility.debug_enabled;
703
704 OPEN c_get_payroll_effective_date; /* Bug#4467198 */
705 FETCH c_get_payroll_effective_date INTO l_eff_date; /* Bug#4467198 */
706 CLOSE c_get_payroll_effective_date; /* Bug#4467198 */
707
708 IF g_debug THEN
709 l_procedure := 'pay_au_paye_ff.calculate_asg_prev_value';
710 hr_utility.set_location('Entering '||l_procedure, 10);
711 hr_utility.set_location('IN p_assignment_id : ' ||p_assignment_id,20);
712 hr_utility.set_location('IN p_business_group_id ' ||p_business_group_id,25);
713 hr_utility.set_location('IN p_date_earned ' ||p_date_earned,30);
714 hr_utility.set_location('IN p_tax_unit_id ' ||p_tax_unit_id,35);
715 hr_utility.set_location('IN p_period_start_date ' ||p_period_start_date,40);
716 hr_utility.set_location('IN p_case ' ||p_case,45);
717 hr_utility.set_location('IN p_date_earned ' ||to_char(p_date_earned,'dd/mm/yyyy'),50);
718 hr_utility.set_location('IN p_use_tax_flag ' ||p_use_tax_flag,50);
719 END IF;
720
721 /*Bug#4467198 Find the Financial Year Start and End Dates on the basis of effective date of the current payroll run.*/
722
723 IF MONTHS_BETWEEN(l_eff_date,TRUNC(l_eff_date,'Y')) < 6 THEN
724 l_fin_start_date := to_date('01-07-'||to_char(add_months(trunc(l_eff_date,'Y'),-9),'YYYY'),'DD-MM-YYYY');
725 l_fin_end_date := to_date('30-06-'||to_char(add_months(trunc(l_eff_date,'Y'),+3),'YYYY'),'DD-MM-YYYY');
726 -- For Previous Fin Year
727 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');
728 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');
729 ELSE
730 l_fin_start_date := to_date('01-JUL-'||to_char(l_eff_date,'YYYY'),'DD-MM-YYYY');
731 l_fin_end_date := to_date('30-JUN-'||to_char(add_months(l_eff_date,12),'YYYY'),'DD-MM-YYYY');
732 -- For Previous Fin Year
733 l_prev_yr_fin_start_date := to_date('01-07-'||to_char(add_months(l_eff_date,-12),'YYYY'),'DD-MM-YYYY');
734 l_prev_yr_fin_end_date := to_date('30-06-'||to_char(trunc(l_eff_date,'Y'),'YYYY'),'DD-MM-YYYY');
735
736 END IF;
737
738 IF g_debug THEN
739 hr_utility.set_location('l_fin_start_date: '|| l_fin_start_date, 55);
740 hr_utility.set_location('l_fin_end_date: '|| l_fin_end_date, 60);
741
742 hr_utility.set_location('l_prev_yr_fin_start_date: '|| l_prev_yr_fin_start_date, 65);
743 hr_utility.set_location('l_prev_yr_fin_end_date: '|| l_prev_yr_fin_end_date, 70);
744 END IF;
745
746 /* Bug#4467198 - Use the below cursor to check if this is the first for the assignment in this year*/
747 OPEN c_check_payroll_run(p_assignment_id,
748 p_business_group_id,
749 l_fin_start_date,
750 l_eff_date);
751 FETCH c_check_payroll_run INTO l_counter;
752 CLOSE c_check_payroll_run;
753
754
755
756
757 /* Bug 2610141 - Get the Maximum assignment action id for the Previous Financial Year for the current
758 Legal Employer or the maximum assignment action id for previous legal employer for the
759 current year*/
760
761 IF l_counter = 0 OR p_use_tax_flag = 'N' THEN
765 IF nvl(l_asg_act_id,-99999) <> -99999 THEN /*Bug 4418107*/
762 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);
763 FETCH c_get_prev_year_max_asg_act_id into l_asg_act_id, l_tax_unit_id, l_payroll_id;
764 CLOSE c_get_prev_year_max_asg_act_id;
766 l_flag := 1; /* Flag is set to 1 when we take YTD earnings for previous year for the current legal employer*/
767 END IF;
768 ELSE
769 OPEN c_get_pre_le_max_asg_act_id(p_assignment_id, p_business_group_id, l_fin_start_date, l_eff_date - 1 ); /*4521653 replaced p_period_start_date with l_eff_date */
770 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;
771 CLOSE c_get_pre_le_max_asg_act_id;
772 IF nvl(l_asg_act_id,-99999) <> -99999 THEN /*Bug 4418107*/
773 l_flag := 2; /* Flag is set to 2 when we take YTD earnings for current year for the previous legal employer*/
774 END IF;
775 END IF;
776
777
778 IF l_flag <> 1 AND l_flag <> 2 THEN
779 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);
780 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;
781 CLOSE c_get_pre_le_max_asg_act_id;
782 l_flag := 3; /* Flag is set to 3 when we take YTD earnings for previous year for the legal employer effective on on the last run of year*/
783 END IF;
784
785
786 IF g_debug THEN
787 hr_utility.set_location('l_asg_act_id: '|| l_asg_act_id, 75);
788 hr_utility.set_location('g_context_table(1).tax_unit_id: '|| l_tax_unit_id, 80);
789 hr_utility.set_location('l_payroll_id: '||l_payroll_id, 85);
790 hr_utility.set_location('p_tax_unit_id : '||p_tax_unit_id, 90);
791 END IF;
792
793
794 IF nvl(l_asg_act_id,-99999) = -99999 THEN
795 /* There is no payroll actions exist in the previous financial year and also there is no
796 actions present in the current year. This means the customer go live and this is the
797 first payroll action
798 For this case, need to populate message to the user in order to process the Termination
799 Payments Manually. For this set the p_case to 'POPULATE_MSG'
800 Average_Earnings will not be calculated.
801 */
802 p_case := 'POPULATE_MSG';
803 IF g_debug THEN
804 hr_utility.set_location('p_case: '|| p_case, 95);
805 hr_utility.set_location('Exiting '||l_procedure,105);
806 END IF;
807 RETURN 110;
808
809 ELSE
810
811 /* Bug 2610141 - Get the Total Number of Paid Periods for the Previous Financial Year for the current
812 Legal Employer or the number of paid periods of the previous legal employer for the
813 current year*/
814 IF l_flag = 1 OR l_flag = 3 THEN
815 OPEN c_get_periods
816 (l_tax_unit_id,
817 l_payroll_id,
818 l_prev_yr_fin_start_date,
819 l_prev_yr_fin_end_date);
820 FETCH c_get_periods INTO p_paid_periods;
821 CLOSE c_get_periods;
822 ELSE
823 OPEN c_get_periods
824 (l_tax_unit_id,
825 l_payroll_id,
826 l_fin_start_date,
827 p_period_start_date - 1);
828 FETCH c_get_periods INTO p_paid_periods;
829 CLOSE c_get_periods;
830 END IF;
831
832 IF g_debug THEN
833 hr_utility.set_location('p_paid_periods: '|| p_paid_periods, 100);
834 END IF;
835
836
837 IF NOT g_ytd_def_bals_populated THEN
838 -- Fetch the defined balance ids for the required balances
839 --
840
841 IF p_use_tax_flag = 'Y' THEN
842 l_db_item_suffix := '_ASG_LE_YTD';
843 ELSE
844 l_db_item_suffix := '_ASG_YTD';
845 END IF ;
846
847 OPEN c_get_ytd_def_bal_ids(l_db_item_suffix);
848 LOOP
849 FETCH c_get_ytd_def_bal_ids into c_ytd_input_table;
850 EXIT WHEN c_get_ytd_def_bal_ids%NOTFOUND;
851
852 -- Populate the Defined Balances Input Values Table
853 g_ytd_input_table(i).defined_balance_id := c_ytd_input_table.defined_balance_id;
854 g_ytd_input_table(i).balance_value := null;
855
856 -- Populate the contexts Table
857
858 /*bug 2610141*/
859 IF p_use_tax_flag = 'Y' THEN
860 g_ytd_context_table(1).tax_unit_id := l_tax_unit_id;
861 ELSE
862 g_ytd_context_table(1).tax_unit_id := null;
863 END IF;
864
865 -- Populate the Global Defined Balances Table
866 g_ytd_bals(i).defined_balance_id := c_ytd_input_table.defined_balance_id;
867 g_ytd_bals(i).balance_name := c_ytd_input_table.balance_name;
868 g_ytd_bals(i).dimension_name := c_ytd_input_table.dimension_name;
869
870 i := i+1;
871 END LOOP;
872 CLOSE c_get_ytd_def_bal_ids;
873 g_ytd_def_bals_populated := TRUE;
874
875 END IF;
876
877 -- Use BBR for retrieving the balance values for the previous financial year.
878 --
879 pay_balance_pkg.get_value(P_ASSIGNMENT_ACTION_ID =>l_asg_act_id,
880 P_DEFINED_BALANCE_LST => g_ytd_input_table,
881 P_CONTEXT_LST => g_ytd_context_table,
882 P_OUTPUT_TABLE => g_ytd_result_table);
883
887 IF g_ytd_result_table(i).defined_balance_id = g_ytd_bals(i).defined_balance_id
884
885 FOR i in g_ytd_result_table.first .. g_ytd_result_table.last
886 LOOP
888 and g_ytd_bals(i).balance_name = 'Earnings_Standard'
889 THEN
890 p_earnings_standard := nvl(g_ytd_result_table(i).balance_value,0);
891 IF g_debug THEN
892 hr_utility.set_location('p_earnings_standard: '||p_earnings_standard, 60);
893 END IF;
894 ELSIF g_ytd_result_table(i).defined_balance_id = g_ytd_bals(i).defined_balance_id
895 and g_ytd_bals(i).balance_name = 'Pre Tax Spread Deductions'
896 THEN
897 p_pre_tax_spread := nvl(g_ytd_result_table(i).balance_value,0);
898 IF g_debug THEN
899 hr_utility.set_location('p_pre_tax_spread_deductions: '||p_pre_tax_spread, 60);
900 END IF;
901
902 ELSIF g_ytd_result_table(i).defined_balance_id = g_ytd_bals(i).defined_balance_id
903 and g_ytd_bals(i).balance_name = 'Pre Tax Fixed Deductions' and p_use_tax_flag = 'Y'
904 /*bug4363057*/
905 THEN
906 p_pre_tax_fixed := nvl(g_ytd_result_table(i).balance_value,0);
907 IF g_debug THEN
908 hr_utility.set_location('p_pre_tax_fixed_deductions: '||p_pre_tax_fixed, 60);
909 END IF;
910 ELSIF g_ytd_result_table(i).defined_balance_id = g_ytd_bals(i).defined_balance_id
911 and g_ytd_bals(i).balance_name = 'Pre Tax Progressive Deductions' and p_use_tax_flag = 'Y'
912 /*bug4363057*/
913 THEN
914 p_pre_tax_prog := nvl(g_ytd_result_table(i).balance_value,0);
915 IF g_debug THEN
916 hr_utility.set_location('p_pre_tax_progressive_deductions: '||p_pre_tax_prog, 60);
917 END IF;
918
919 END IF;
920 END LOOP;
921
922 if g_debug then
923 hr_utility.set_location('OUT p_paid_periods: '|| p_paid_periods, 100);
924 hr_utility.set_location('OUT p_earnings_standard: '||p_earnings_standard, 60);
925 hr_utility.set_location('OUT p_pre_tax_spread_deductions: '||p_pre_tax_spread, 60);
926 hr_utility.set_location('OUT p_pre_tax_fixed_deductions: '||p_pre_tax_fixed, 60);
927 hr_utility.set_location('OUT p_pre_tax_progressive_deductions: '||p_pre_tax_prog, 60);
928 hr_utility.set_location('Exiting '||l_procedure,105);
929 end if;
930
931 return 1000;
932 END IF;
933
934
935 END calculate_asg_prev_value;
936
937
938
939
940
941 /*
942 * paid_periods_since_hire_date - returns the number of periods in the
943 * current tax year since the hire date.
944 */
945
946 function paid_periods_since_hire_date
947 (
948 p_payroll_id in number,
949 p_assignment_id in number,
950 p_tax_unit_id in number, --2610141
951 p_assignment_action_id IN number, /*Bug 4438644*/
952 p_period_num in number,
953 p_period_start in date,
954 p_emp_hire_date in date,
955 p_use_tax_flag IN VARCHAR2 --2610141
956 )
957 return number is
958
959 l_year_start date;
960 l_month_no number;
961 l_year number;
962 l_period_num number;
963 l_time_period_id number;
964 l_start_date date;
965 l_eff_date DATE;
966 l_count_period NUMBER;
967 l_eff_period_num NUMBER;
968 v_curr_time_period_id NUMBER;
969 l_procedure varchar2(80);
970
971 /*Bug 4438644 - Cursor introduced to return time period of the current payroll period*/
972 cursor c_get_period_id
973 is
974 select time_period_id
975 from per_time_periods
976 where payroll_id = p_payroll_id
977 and start_Date = p_period_start;
978
979 /* Bug: 3724089 - Performance Fix in the Cursor below. Added table per_assignments_f and its joins in the inner sub-query */
980
981 cursor c_get_processed_periods_num (v_payroll_id number,
982 v_start_date date,
983 v_end_date date,
984 v_assignment_id number,
985 v_tax_unit_id number --2610141
986 ) is
987 select DISTINCT ptp.time_period_id, ptp.period_num /*Bug 4438644*/
988 from per_time_periods ptp
989 where exists (select 'EXISTS' from
990 per_assignments_f paf,
991 pay_payroll_actions ppa,
992 pay_assignment_actions paa
993 where ppa.payroll_id = v_payroll_id
994 and ppa.action_type in ('R','Q')
995 and paa.action_status = 'C'
996 and ppa.payroll_action_id = paa.payroll_action_id
997 and paf.assignment_id = v_assignment_id
998 and paa.assignment_id = paf.assignment_id
999 and paa.tax_unit_id = decode(p_use_tax_flag,'N',paa.tax_unit_id,v_tax_unit_id) --2610141
1000 AND ppa.effective_date BETWEEN v_start_date and v_end_date /*Bug 4438644*/
1001 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date /*Bug 4438644*/
1002 and ppa.date_earned between paf.effective_start_date and paf.effective_end_date)
1003 and ptp.payroll_id = v_payroll_id;
1007 /* Bug 4438644 - Two new cursors introduced, c_get_payroll_effective_date gives the effective date of the payroll
1004 -- and ptp.regular_payment_date between v_start_date and v_end_date; /*Bug 4438644*/
1005
1006
1008 bein run.*/
1009
1010 CURSOR c_get_payroll_effective_date
1011 IS
1012 SELECT ppa.effective_date
1013 FROM pay_payroll_actions ppa,
1014 pay_assignment_actions paa
1015 WHERE paa.assignment_action_id = p_assignment_action_id
1016 AND ppa.payroll_action_id = paa.payroll_action_id;
1017
1018
1019
1020 begin
1021 g_debug := hr_utility.debug_enabled;
1022
1023 if g_debug then
1024 l_procedure :='pay_au_paye_ff.paid_periods_since_hire_date';
1025 hr_utility.set_location('Entering '||l_procedure,10);
1026 hr_utility.set_location('IN p_payroll_id '||p_payroll_id,20);
1027 hr_utility.set_location('IN p_assignment_id '||p_assignment_id,25);
1028 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,30);
1029 hr_utility.set_location('IN p_assignment_action_id '||p_assignment_action_id,35);
1030 hr_utility.set_location('IN p_period_num '||p_period_num,40);
1031 hr_utility.set_location('IN p_period_start '||to_char(p_period_start,'dd/mm/yyyy'),45);
1032 hr_utility.set_location('IN p_emp_hire_date '||to_char(p_emp_hire_date,'dd/mm/yyyy'),50);
1033 hr_utility.set_location('IN p_use_tax_flag '||p_use_tax_flag,55);
1034
1035 end if;
1036
1037 OPEN c_get_payroll_effective_date; /*Bug 4438644*/
1038 FETCH c_get_payroll_effective_date INTO l_eff_date; /*Bug 4438644*/
1039 CLOSE c_get_payroll_effective_date; /*Bug 4438644*/
1040
1041 /*Bug 4438644 - Code given below gets the current time period id based on date earned*/
1042 OPEN c_get_period_id;
1043 FETCH c_get_period_id INTO v_curr_time_period_id;
1044 CLOSE c_get_period_id;
1045
1046 /*Bug 4438644 - Financial year now gets calculated on the basis of the payroll effective date*/
1047 l_month_no := to_number(to_char(l_eff_date,'MM'));
1048 l_year := to_number(to_char(l_eff_date,'YYYY'));
1049
1050 /* Bug# 2166742 Added the following if clause */
1051 /* Bug 4438644 - This piece of code has been removed*/
1052 /* if p_period_num = 1 then
1053 return 1;
1054 end if; */
1055
1056 if l_month_no > 6 then
1057 l_year_start := to_date('01-07-'||to_char(l_year),'DD-MM-YYYY');
1058 else
1059 l_year_start := to_date('01-07-'||to_char(l_year - 1),'DD-MM-YYYY');
1060 end if;
1061
1062 if p_emp_hire_date <= l_year_start then
1063 l_start_date := l_year_start;
1064 else
1065 l_start_date := p_emp_hire_date;
1066 end if;
1067
1068
1069 l_count_period := 0;
1070
1071 open c_get_processed_periods_num (p_payroll_id
1072 ,l_start_date
1073 ,l_eff_date /*Bug 4438644 -Payroll effective date as argument*/
1074 ,p_assignment_id
1075 ,p_tax_unit_id); --2610141
1076 LOOP
1077 fetch c_get_processed_periods_num into l_time_period_id,l_period_num;
1078 EXIT WHEN c_get_processed_periods_num%NOTFOUND;
1079 IF l_time_period_id <> v_curr_time_period_id THEN /*Bug 4438644 - This condition put to exclude the increment
1080 for current payroll period.*/
1081 l_count_period := l_count_period + 1;
1082 END IF;
1083 END LOOP;
1084
1085 close c_get_processed_periods_num;
1086
1087 l_count_period := l_count_period + 1; /*Bug 4438644 - Increment done for current payroll period*/
1088
1089 if g_debug then
1090 hr_utility.set_location('Return l_count_period '||l_count_period,60);
1091 hr_utility.set_location('Exiting '||l_procedure,70);
1092 end if ;
1093
1094 RETURN l_count_period;
1095
1096 exception
1097 when others then
1098 null;
1099
1100 end paid_periods_since_hire_date;
1101
1102
1103 /*
1104 * convert_to_period - converts weekly equivalents
1105 * back to the period amounts using new ATO rules
1106 */
1107
1108 function convert_to_period_amt
1109 (
1110 p_ann_freq in number,
1111 p_amt_week in number,
1112 p_tax_scale in number
1113 )
1114 return number is
1115
1116 l_amt_period number;
1117
1118 begin
1119 g_debug := hr_utility.debug_enabled;
1120 If(p_tax_scale <> 4) then
1121 if p_ann_freq = 52 then
1122 l_amt_period := p_amt_week;
1123 elsif p_ann_freq = 26 then
1124 l_amt_period := (p_amt_week * 2);
1125 elsif p_ann_freq = 24 then
1126 l_amt_period := round_amt (p_amt_week * 13 / 6,p_tax_scale);
1127 elsif p_ann_freq = 12 then
1128 l_amt_period := round_amt (p_amt_week * 13 / 3,p_tax_scale);
1129 elsif p_ann_freq = 4 then /*Bug : 2888114*/
1130 l_amt_period := round_amt (p_amt_week * 13 ,p_tax_scale);
1131 end if;
1132 else
1133 if p_ann_freq = 52 then
1134 l_amt_period := p_amt_week;
1135 elsif p_ann_freq = 26 then
1136 l_amt_period := (p_amt_week * 2);
1137 elsif p_ann_freq = 24 then
1138 l_amt_period := trunc (p_amt_week * 13 / 6);
1139 elsif p_ann_freq = 12 then
1140 l_amt_period := trunc (p_amt_week * 13 / 3);
1141 elsif p_ann_freq = 4 then /*Bug : 2888114*/
1142 l_amt_period := trunc (p_amt_week * 13) ;
1143 end if;
1144 end if;
1145 return (l_amt_period);
1146
1147 exception
1148 when others then
1149 null;
1150
1151 end convert_to_period_amt;
1152
1153
1154 function round_amt
1155 (
1156 p_actual_amt in number,
1157 p_tax_scale in number
1158 )
1159 return number is
1160
1161 begin
1162 g_debug := hr_utility.debug_enabled;
1163 If(p_tax_scale <> 4) then
1164 return (round(p_actual_amt));
1165 else
1166 return (trunc(p_actual_amt));
1167 end if;
1168
1169 exception
1170 when others then
1171 null;
1172
1173 end round_amt;
1174
1175
1176 function check_if_retro
1177 (
1178 p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1179 p_date_earned in pay_payroll_actions.date_earned%TYPE
1180
1181 )return varchar2 is
1182
1183
1184 l_creator_type pay_element_entries_f.creator_type%TYPE;
1185 IS_retro_payment varchar2(10);
1186 l_procedure varchar2(80);
1187 begin
1188 g_debug := hr_utility.debug_enabled;
1189
1190 if g_debug then
1191 l_procedure :='pay_au_paye_ff.check_if_retro';
1192 hr_utility.set_location('Entering '||l_procedure,10);
1193 hr_utility.set_location('IN p_element_entry_id '||p_element_entry_id,20);
1194 hr_utility.set_location('IN p_date_earned '||to_char(p_date_earned,'dd/mm/yyyy'),30);
1195 end if;
1196
1197 OPEN c_get_creator_type(p_element_entry_id,p_date_earned);
1198 FETCH c_get_creator_type INTO l_creator_type ;
1199 CLOSE c_get_creator_type;
1200 if l_creator_type = 'RR' or l_creator_type = 'EE' then
1201 IS_retro_payment:='Y';
1202 else
1203 IS_retro_payment:='N';
1204 end if;
1205
1206 if g_debug then
1207 hr_utility.set_location('Return IS_retro_payment '||IS_retro_payment,40);
1208 hr_utility.set_location('Exiting '||l_procedure,50);
1209 end if;
1210
1211 return IS_retro_payment;
1212
1213 EXCEPTION
1214 when others then
1215 null;
1216
1217
1218 end check_if_retro;
1219
1220
1221
1222 function get_retro_period
1223 (
1224 p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1225 p_date_earned in pay_payroll_actions.date_earned%TYPE,
1226 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,/*Bug 4418107*/
1227 p_retro_start_date out NOCOPY date,
1228 p_retro_end_date out NOCOPY date
1229 )return number is
1230
1231
1232 cursor get_retro_period_rr
1233 ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1234 c_date_earned in pay_payroll_actions.date_earned%TYPE
1235 ) is
1236 SELECT ptp.start_date,ptp.end_date
1237 FROM per_time_periods ptp,
1238 pay_payroll_actions ppa,
1239 pay_assignment_actions paa,
1240 pay_run_results prr,
1241 pay_element_entries_f pee
1242 WHERE pee.element_entry_id=c_element_entry_id
1243 and prr.run_result_id = pee.source_id
1244 and paa.assignment_action_id=prr.assignment_action_id
1245 AND paa.tax_unit_id = p_tax_unit_id /*Bug 4418107*/
1246 and ppa.payroll_action_id=paa.payroll_action_id
1247 and ptp.payroll_id=ppa.payroll_id
1248 and pee.creator_type='RR'
1249 and ppa.date_earned between ptp.start_date and ptp.end_date
1250 and c_date_earned between pee.effective_start_date and pee.effective_end_date;
1251
1252
1253
1254 cursor get_retro_period_ee
1255 ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1259 FROM per_time_periods ptp,
1256 c_date_earned in pay_payroll_actions.date_earned%TYPE
1257 ) is
1258 SELECT ptp.start_date,ptp.end_date
1260 pay_payroll_actions ppa,
1261 pay_assignment_actions paa,
1262 pay_element_entries_f pee
1263 WHERE pee.element_entry_id=c_element_entry_id
1264 and paa.assignment_action_id=pee.source_asg_action_id
1265 AND paa.tax_unit_id = p_tax_unit_id /*Bug 4418107*/
1266 and ppa.payroll_action_id=paa.payroll_action_id
1267 and ptp.payroll_id=ppa.payroll_id
1268 and pee.creator_type='EE'
1269 and ppa.date_earned between ptp.start_date and ptp.end_date
1270 and c_date_earned between pee.effective_start_date and pee.effective_end_date;
1271
1272 l_creator_type pay_element_entries_f.creator_type%TYPE;
1273 l_period_obtained_flag number;
1274 l_procedure varchar2(80);
1275
1276
1277 begin
1278 g_debug := hr_utility.debug_enabled;
1279
1280 IF g_debug THEN
1281 l_procedure :='pay_au_paye_ff.get_retro_period';
1282 hr_utility.set_location('Entering '||l_procedure,10);
1283 hr_utility.set_location('IN p_element_entry_id '||p_element_entry_id,20);
1284 hr_utility.set_location('IN p_date_earned '||to_char(p_date_earned,'dd/mm/yyyy'),30);
1285 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id ,40);
1286
1287 END IF;
1288
1289
1290 l_period_obtained_flag:=1;
1291 IF g_debug THEN
1292 hr_utility.set_location('l_period_obtained_flag '||l_period_obtained_flag,45);
1293 END IF;
1294
1295 OPEN c_get_creator_type(p_element_entry_id,p_date_earned);
1296 FETCH c_get_creator_type INTO l_creator_type ;
1297 CLOSE c_get_creator_type;
1298
1299
1300 if l_creator_type = 'RR' then
1301 OPEN get_retro_period_rr(p_element_entry_id,p_date_earned);
1302 FETCH get_retro_period_rr into p_retro_start_date,p_retro_end_date;
1303 CLOSE get_retro_period_rr;
1304 l_period_obtained_flag:=1;
1305 end if;
1306
1307 if l_creator_type = 'EE' then
1308 OPEN get_retro_period_ee(p_element_entry_id,p_date_earned);
1309 FETCH get_retro_period_ee into p_retro_start_date,p_retro_end_date;
1310 CLOSE get_retro_period_ee;
1311 l_period_obtained_flag:=1;
1312 end if;
1313
1314 IF g_debug THEN
1315
1316 hr_utility.set_location('OUT p_retro_start_date '||to_char(p_retro_start_date,'dd/mm/yyyy'),50);
1317 hr_utility.set_location('OUT p_retro_end_date '||to_char(p_retro_end_date,'dd/mm/yyyy'),55);
1318 hr_utility.set_location('OUT l_period_obtained_flag '||l_period_obtained_flag,60);
1319 hr_utility.set_location('Exiting '||l_procedure,70);
1320
1321 END IF;
1322
1323 return l_period_obtained_flag;
1324
1325
1326
1327 end get_retro_period;
1328
1329 /* Bug 5586445
1330 Function : get_enhanced_retro_period
1331 Description : This function is to be used for Enhanced Retropay implementation.
1332 Function returns details about Retro Element entry and the retropay time
1333 span for which the entry is created.
1334 Inputs : p_element_entry_id - Element Entry ID
1335 p_date_earned - Date Earned of the Run
1336 p_tax_unit_id - Tax Unit ID of Assignment
1337 Outputs : p_retro_start_date - Period Start Date of Original period
1338 p_retro_end_date - Period End Date of Original period
1339 p_orig_effective_date - Effective Date of Original Run
1340 p_retro_effective_date - Effective Date of Retropay Run that created element entry
1341 p_time_span - Character String indicating the retro time span. Values are,
1342 'LT12 Curr','LT12 Prev','GT12'
1343 */
1344
1345 FUNCTION get_enhanced_retro_period
1346 (
1347 p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE,
1348 p_date_earned IN pay_payroll_actions.date_earned%TYPE,
1349 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1350 p_retro_start_date OUT NOCOPY date,
1351 p_retro_end_date OUT NOCOPY date,
1352 p_orig_effective_date OUT NOCOPY date,
1353 p_retro_effective_date OUT NOCOPY date,
1354 p_time_span OUT NOCOPY varchar2
1355 )return number
1356 IS
1357
1358 /* Bug 5586445 - Cursor get_retropay_run_details
1359 Get Effective Date of the Enhanced Retropay process
1360 */
1361 CURSOR get_retropay_run_details
1362 (c_element_entry_id pay_element_entries_f.element_entry_id%TYPE)
1363 IS
1364 SELECT pee.creator_type,
1365 ppa.effective_date
1366 FROM pay_payroll_actions ppa,
1367 pay_assignment_actions paa,
1368 pay_element_entries_f pee
1369 WHERE ppa.payroll_action_id = paa.payroll_action_id
1370 AND paa.assignment_action_id = pee.creator_id
1371 AND pee.element_entry_id = c_element_entry_id
1372 AND ppa.action_type ='L';
1373
1374 CURSOR get_retro_period_rr
1375 ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1376 c_date_earned in pay_payroll_actions.date_earned%TYPE
1377 )
1378 IS
1379 SELECT ptp.start_date
1380 ,ptp.end_date
1384 pay_assignment_actions paa,
1381 ,ppa.effective_date
1382 FROM per_time_periods ptp,
1383 pay_payroll_actions ppa,
1385 pay_run_results prr,
1386 pay_element_entries_f pee
1387 WHERE pee.element_entry_id=c_element_entry_id
1388 AND prr.run_result_id = pee.source_id
1389 AND paa.assignment_action_id=prr.assignment_action_id
1390 AND paa.tax_unit_id = p_tax_unit_id
1391 AND ppa.payroll_action_id=paa.payroll_action_id
1392 AND ptp.payroll_id=ppa.payroll_id
1393 AND pee.creator_type='RR'
1394 AND ppa.date_earned between ptp.start_date and ptp.end_date
1395 AND c_date_earned between pee.effective_start_date and pee.effective_end_date;
1396
1397
1398 CURSOR get_retro_period_ee
1399 ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1400 c_date_earned in pay_payroll_actions.date_earned%TYPE
1401 )
1402 IS
1403 SELECT ptp.start_date
1404 ,ptp.end_date
1405 ,ppa.effective_date
1406 FROM per_time_periods ptp,
1407 pay_payroll_actions ppa,
1408 pay_assignment_actions paa,
1409 pay_element_entries_f pee
1410 WHERE pee.element_entry_id=c_element_entry_id
1411 AND paa.assignment_action_id=pee.source_asg_action_id
1412 AND paa.tax_unit_id = p_tax_unit_id
1413 AND ppa.payroll_action_id=paa.payroll_action_id
1414 AND ptp.payroll_id=ppa.payroll_id
1415 AND pee.creator_type='EE'
1416 AND ppa.date_earned between ptp.start_date and ptp.end_date
1417 AND c_date_earned between pee.effective_start_date and pee.effective_end_date;
1418
1419 l_period_obtained_flag number;
1420
1421 l_procedure VARCHAR2(80);
1422 l_creator_type pay_element_entries_f.creator_type%TYPE;
1423 l_fin_year_start DATE;
1424 l_month_start DATE;
1425 l_orig_month_start DATE;
1426 l_time_span VARCHAR2(80);
1427
1428
1429 BEGIN
1430
1431 g_debug := hr_utility.debug_enabled;
1432 l_period_obtained_flag := 1;
1433
1434 IF g_debug THEN
1435 l_procedure := 'pay_au_payee_ff.get_enhanced_retro_period';
1436 hr_utility.set_location('Entering '||l_procedure,10);
1437 hr_utility.set_location('IN p_element_entry_id '||p_element_entry_id,20);
1438 hr_utility.set_location('IN p_date_earned '||p_date_earned,30);
1439 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,30);
1440 END IF;
1441
1442 OPEN get_retropay_run_details(p_element_entry_id);
1443 FETCH get_retropay_run_details INTO l_creator_type,p_retro_effective_date;
1444 CLOSE get_retropay_run_details;
1445
1446 l_fin_year_start := hr_au_routes.span_start(p_retro_effective_date,1,'01-07');
1447 l_month_start := hr_au_routes.span_start(p_retro_effective_date,12,'01-01');
1448
1449 IF l_creator_type = 'RR'
1450 THEN
1451 OPEN get_retro_period_rr(p_element_entry_id,p_date_earned);
1452 FETCH get_retro_period_rr INTO p_retro_start_date,p_retro_end_date,p_orig_effective_date;
1453 IF get_retro_period_rr%FOUND
1454 THEN
1455 l_orig_month_start := hr_au_routes.span_start(p_orig_effective_date,12,'01-01');
1456 l_period_obtained_flag:=1;
1457 END IF;
1458 CLOSE get_retro_period_rr;
1459 END IF;
1460
1461 IF l_creator_type = 'EE' then
1462 OPEN get_retro_period_ee(p_element_entry_id,p_date_earned);
1463 FETCH get_retro_period_ee INTO p_retro_start_date,p_retro_end_date,p_orig_effective_date;
1464 IF get_retro_period_ee%FOUND
1465 THEN
1466 l_orig_month_start := hr_au_routes.span_start(p_orig_effective_date,12,'01-01');
1467 l_period_obtained_flag:=1;
1468 END IF;
1469 CLOSE get_retro_period_ee;
1470 END IF;
1471
1472 p_time_span := NULL;
1473 /* Set Time Span */
1474
1475 IF (p_orig_effective_date >= l_fin_year_start)
1476 THEN
1477 l_time_span := 'LT12 Curr';
1478 ELSIF (p_orig_effective_date < l_fin_year_start) AND
1479 (trunc(months_between(l_month_start,l_orig_month_start)) <= 12)
1480 THEN
1481 l_time_span := 'LT12 Prev';
1482 ELSIF (p_orig_effective_date < l_fin_year_start) AND
1483 (trunc(months_between(l_month_start,l_orig_month_start)) > 12)
1484 THEN
1485 l_time_span := 'GT12';
1486 END IF;
1487
1488 p_time_span := l_time_span;
1489
1490 IF g_debug
1491 THEN
1492 hr_utility.set_location('OUT p_retro_start_date '||p_retro_start_date,40);
1493 hr_utility.set_location('OUT p_retro_end_date '||p_retro_end_date,50);
1494 hr_utility.set_location('OUT p_orig_effective_date '||p_orig_effective_date,60);
1495 hr_utility.set_location('OUT p_retro_effective_date '||p_retro_effective_date,70);
1496 hr_utility.set_location('OUT p_time_span '||p_time_span,80);
1497 hr_utility.set_location('Exiting '||l_procedure,90);
1498 END IF;
1499
1500 RETURN l_period_obtained_flag;
1501
1502 END get_enhanced_retro_period;
1503
1504
1505 /* Bug 5846272 - Functions added for Enhanced Retropay in 11i.
1506 Function : check_if_enhanced_retro
1507 Description : This function checks the Legislation Rule for Enhanced Retropay and
1508 returns value indicating if Enhanced Retropay is enabled in system or not.
1509 Inputs : p_business_group_id - Business Group ID
1510 */
1511
1512 FUNCTION check_if_enhanced_retro
1513 (
1517
1514 p_business_group_id IN per_business_groups.business_group_id%TYPE
1515 )RETURN VARCHAR2
1516 IS
1518 CURSOR get_legislation_rule
1519 (c_business_group_id IN per_business_groups.business_group_id%TYPE)
1520 IS
1521 SELECT rule_mode
1522 FROM pay_legislation_rules plr
1523 ,per_business_groups pbg
1524 WHERE plr.legislation_code = pbg.legislation_code
1525 AND pbg.business_group_id = c_business_group_id
1526 AND plr.rule_type = 'ADVANCED_RETRO'
1527 AND pbg.legislation_code = 'AU';
1528
1529 l_return VARCHAR2(10);
1530 l_proc_name VARCHAR2(80);
1531
1532 BEGIN
1533
1534 g_debug := hr_utility.debug_enabled;
1535
1536 IF g_debug THEN
1537 l_proc_name := 'pay_au_paye_ff.check_if_enhanced_retro';
1538 hr_utility.set_location('Entering '||l_proc_name,10);
1539 hr_utility.set_location('IN p_business_group_id => '||p_business_group_id,20);
1540 END IF;
1541
1542 OPEN get_legislation_rule(p_business_group_id);
1543 FETCH get_legislation_rule INTO l_return;
1544 CLOSE get_legislation_rule;
1545
1546 IF g_debug THEN
1547 hr_utility.set_location('OUT Return Value =>'||l_return,30);
1548 hr_utility.set_location('Exiting '||l_proc_name,40);
1549 END IF;
1550
1551 RETURN NVL(l_return,'N');
1552
1553 END check_if_enhanced_retro;
1554
1555
1556 /* Bug 5846272
1557 Function : get_retro_time_span
1558 Description : This function is to be used for Enhanced Retropay implementation.
1559 Function returns details about Retro Element entry and the retropay time
1560 span for which the entry is created.
1561 Inputs : p_element_entry_id - Element Entry ID
1562 p_date_earned - Date Earned of the Run
1563 p_tax_unit_id - Tax Unit ID of Assignment
1564 Outputs : p_retro_start_date - Period Start Date of Original period
1565 p_retro_end_date - Period End Date of Original period
1566 p_orig_effective_date - Effective Date of Original Run
1567 p_retro_effective_date - Effective Date of Retropay Run that created element entry
1568 p_time_span - Character String indicating the retro time span. Values are,
1569 'LT12 Curr','LT12 Prev','GT12'
1570 p_retro_type - String indicating the type of retropay used to create element.
1571 Values are - 'RETRO_ELE' and 'ADVANCED_RETRO'
1572 */
1573
1574
1575 FUNCTION get_retro_time_span
1576 (
1577 p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE,
1578 p_date_earned IN pay_payroll_actions.date_earned%TYPE,
1579 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1580 p_retro_start_date OUT NOCOPY date,
1581 p_retro_end_date OUT NOCOPY date,
1582 p_orig_effective_date OUT NOCOPY date,
1583 p_retro_effective_date OUT NOCOPY date,
1584 p_time_span OUT NOCOPY varchar2,
1585 p_retro_type OUT NOCOPY varchar2
1586 )RETURN NUMBER
1587 IS
1588
1589 CURSOR get_retro_entry_details
1590 (c_element_entry_id pay_element_entries_f.element_entry_id%TYPE
1591 ,c_date_earned pay_payroll_actions.date_earned%TYPE)
1592 IS
1593 SELECT pee.element_entry_id
1594 ,ppa.retro_definition_id
1595 ,pepd.retro_component_id
1596 FROM pay_element_entries_f pee
1597 ,pay_assignment_actions paa
1598 ,pay_payroll_actions ppa
1599 ,pay_entry_process_details pepd
1600 WHERE pee.element_entry_id = c_element_entry_id
1601 AND pee.element_entry_id = pepd.element_entry_id
1602 AND pee.creator_id = paa.assignment_action_id
1603 AND paa.payroll_action_id = ppa.payroll_action_id
1604 AND ppa.action_type = 'L'
1605 AND c_date_earned between pee.effective_start_date and pee.effective_end_date;
1606
1607 l_retro_type VARCHAR2(80);
1608 l_proc_name VARCHAR2(100);
1609 l_retro_period NUMBER;
1610 l_fin_year_start DATE;
1611
1612 l_entry_details get_retro_entry_details%ROWTYPE;
1613
1614 l_temp NUMBER;
1615
1616 BEGIN
1617
1618 g_debug := hr_utility.debug_enabled;
1619
1620 IF g_debug THEN
1621 l_proc_name := 'pay_au_paye_ff.get_retro_time_span';
1622 hr_utility.set_location('Entering '||l_proc_name,10);
1623 hr_utility.set_location('IN p_element_entry_id '||p_element_entry_id,30);
1624 hr_utility.set_location('IN p_date_earned '||p_date_earned,30);
1625 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,30);
1626 END IF;
1627
1628 l_retro_type := NULL;
1629
1630 OPEN get_retro_entry_details(p_element_entry_id,p_date_earned);
1631 FETCH get_retro_entry_details INTO l_entry_details;
1632 CLOSE get_retro_entry_details;
1633
1634 IF (l_entry_details.retro_definition_id IS NOT NULL
1635 AND l_entry_details.retro_component_id IS NOT NULL)
1636 THEN
1637 /* Entry Created using Enhanced Retropay */
1638 IF g_debug THEN
1639 hr_utility.set_location('Entry created by Enhanced Retropay ',40);
1640 hr_utility.set_location('Retro Component ID =>'||l_entry_details.retro_component_id,40);
1641 END IF;
1645 p_element_entry_id => p_element_entry_id,
1642 l_retro_type := 'ADVANCED_RETRO';
1643 l_temp := get_enhanced_retro_period
1644 (
1646 p_date_earned => p_date_earned,
1647 p_tax_unit_id => p_tax_unit_id,
1648 p_retro_start_date => p_retro_start_date,
1649 p_retro_end_date => p_retro_end_date,
1650 p_orig_effective_date => p_orig_effective_date,
1651 p_retro_effective_date => p_retro_effective_date,
1652 p_time_span => p_time_span);
1653 ELSE
1654 /* Entry Created Using Retropay by Element
1655 The Effective Dates are set to NULL as its irrelevent and not required
1656 for Retropay by element processing */
1657
1658 IF g_debug THEN
1659 hr_utility.set_location('Entry created by Retropay by Element',50);
1660 END IF;
1661
1662 l_retro_type := 'RETRO_ELE';
1663 p_orig_effective_date := NULL;
1664 p_retro_effective_date := NULL;
1665
1666 l_temp := get_retro_period
1667 (
1668 p_element_entry_id => p_element_entry_id,
1669 p_date_earned => p_date_earned,
1670 p_tax_unit_id => p_tax_unit_id,
1671 p_retro_start_date => p_retro_start_date,
1672 p_retro_end_date => p_retro_end_date
1673 );
1674
1675 l_retro_period := months_between(p_date_earned,p_retro_end_date);
1676 IF l_retro_period > 12
1677 THEN
1678 p_time_span := 'GT12';
1679 ELSE
1680 l_fin_year_start := hr_au_routes.span_start(p_date_earned,1,'01-07');
1681 IF p_retro_end_date < l_fin_year_start
1682 THEN
1683 p_time_span := 'LT12 Prev';
1684 ELSE
1685 p_time_span := 'LT12 Curr';
1686 END IF;
1687 END IF;
1688 END IF;
1689
1690 p_retro_type := l_retro_type;
1691
1692 IF g_debug
1693 THEN
1694 hr_utility.set_location('OUT p_retro_start_date '||p_retro_start_date,80);
1695 hr_utility.set_location('OUT p_retro_end_date '||p_retro_end_date,80);
1696 hr_utility.set_location('OUT p_orig_effective_date '||p_orig_effective_date,80);
1697 hr_utility.set_location('OUT p_retro_effective_date '||p_retro_effective_date,80);
1698 hr_utility.set_location('OUT p_time_span '||p_time_span,80);
1699 hr_utility.set_location('OUT l_retro_type '||l_retro_type,80);
1700 hr_utility.set_location('Exiting '||l_proc_name,90);
1701 END IF;
1702
1703 return 1;
1704 END get_retro_time_span;
1705
1706
1707 function count_retro_periods
1708 (
1709 p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
1710 p_date_earned in pay_payroll_actions.date_earned%TYPE,
1711 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE, /*Bug 4418107*/
1712 p_use_tax_flag IN VARCHAR2 --4415795
1713 )return number
1714 is
1715
1716 -- Need to get the pay value amount so we can check totals in each period.
1717 /*
1718 cursor get_element_entries
1719 is
1720 select pee.element_entry_id from pay_element_entries_f pee,
1721 pay_assignment_actions paa
1722 where paa.assignment_action_id=p_assignment_action_id
1723 and pee.assignment_id=paa.assignment_id
1724 and pee.creator_type IN ('EE','RR')
1725 and p_date_earned between pee.effective_start_date and pee.effective_end_date;
1726 */
1727 cursor get_element_entries
1728 is
1729 select pee.element_entry_id,
1730 peev.screen_entry_value retro_amount,
1731 pec.classification_name
1732 from pay_element_entries_f pee,
1733 pay_element_entry_values_f peev,
1734 pay_element_links_f pelf,
1735 pay_element_types_f pet,
1736 pay_element_classifications pec,
1737 pay_input_values_f piv,
1738 pay_assignment_actions paa
1739 where paa.assignment_action_id = p_assignment_action_id
1740 and pee.assignment_id = paa.assignment_id
1741 and pee.creator_type IN ('EE','RR')
1742 and p_date_earned between pee.effective_start_date and pee.effective_end_date
1743 -- Only Earnings.
1744 and pelf.element_link_id = pee.element_link_id
1745 and p_date_earned between pelf.effective_start_date and pelf.effective_end_date
1746 and pet.element_type_id = pelf.element_type_id
1747 and p_date_earned between pet.effective_start_date and pet.effective_end_date
1748 and pec.classification_id = pet.classification_id
1749 and pec.classification_name in ('Earnings', 'Pre Tax Deductions')
1750 -- Only Pay Value
1751 and peev.element_entry_id = pee.element_entry_id
1752 and p_date_earned between peev.effective_start_date and peev.effective_end_date
1753 and peev.input_value_id = piv.input_value_id
1754 and p_date_earned between piv.effective_start_date and piv.effective_end_date
1755 and piv.name = 'Pay Value';
1756
1757 /* Bug 5846272 - Cursor to read the Advanced Retropay Legislation Rule */
1758 CURSOR get_legislation_rule
1759 IS
1760 SELECT plr.rule_mode
1761 FROM pay_legislation_rules plr
1762 WHERE plr.legislation_code = 'AU'
1766 CURSOR c_get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
1763 AND plr.rule_type ='ADVANCED_RETRO';
1764
1765 /* Bug 6139035 - Cursor to fetch Effective Date */
1767 IS
1768 SELECT ppa.effective_date
1769 FROM pay_payroll_actions ppa
1770 ,pay_assignment_actions paa
1771 WHERE paa.assignment_action_id = c_assignment_action_id
1772 AND paa.payroll_action_id = ppa.payroll_action_id;
1773
1774 l_count number;
1775 l_retro_periods number;
1776 is_retro_payment varchar2(10);
1777 retro_start_date date;
1778 retro_end_date date;
1779 financial_year_span_start date;
1780 l_retro_end_date number;
1781 x varchar2(100);
1782 l_procedure varchar2(80);
1783 TYPE num_tab_type IS TABLE OF NUMBER(10) INDEX BY binary_integer; -- Bug 4412537
1784 num_tab num_tab_type;
1785
1786 -- This table introduced to allow totalling of the retro amounts in each retro period. Bug 5374076.
1787 TYPE tot_period_amount_type IS TABLE OF NUMBER INDEX BY binary_integer; /* Bug# 5397711*/
1788 tot_period_amount tot_period_amount_type;
1789
1790 /* Bug 5846272 - Introduced variables for Enhanced Retropay processing */
1791 l_adv_retro_flag VARCHAR2(10);
1792 l_retro_eff_date DATE;
1793 l_orig_effective_date DATE;
1794 l_time_span varchar2(80);
1795 l_retro_type varchar2(80);
1796
1797 /* Bug 6139035 - Introduced variables for Effective Date */
1798 l_eff_date_yr_start DATE;
1799 l_pay_effective_date DATE;
1800
1801 --
1802 begin
1803
1804 g_debug := hr_utility.debug_enabled;
1805
1806 if g_debug then
1807 l_procedure :='pay_au_paye_ff.count_retro_periods';
1808 hr_utility.set_location('Entering '||l_procedure,10);
1809 hr_utility.set_location('IN p_assignment_action_id '||p_assignment_action_id,20);
1810 hr_utility.set_location('IN p_date_earned '||to_char(p_date_earned,'dd/mm/yyyy'),30);
1811 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,40);
1812 hr_utility.set_location('IN p_use_tax_flag '||p_use_tax_flag,50);
1813 end if;
1814
1815 /* Bug 5846272 - Read the Legislation Rule value for Enhanced Retropay. If
1816 rule not found,set the flag to 'N' */
1817 OPEN get_legislation_rule;
1818 FETCH get_legislation_rule INTO l_adv_retro_flag;
1819 IF get_legislation_rule%NOTFOUND THEN
1820 l_adv_retro_flag := 'N';
1821 END IF;
1822 CLOSE get_legislation_rule;
1823
1824 IF g_debug THEN
1825 hr_utility.set_location('Enhanced Retropay Rule Value =>'||l_adv_retro_flag,50);
1826 END IF;
1827
1828 l_retro_periods:=0;
1829 financial_year_span_start:=hr_au_routes.span_start(p_date_earned,1,'01-07');
1830
1831 /* Bug 6139035 - Get Financial Year Information based on Effective Date of run */
1832 OPEN c_get_effective_date(p_assignment_action_id);
1833 FETCH c_get_effective_date INTO l_pay_effective_date;
1834 CLOSE c_get_effective_date;
1835
1836 l_eff_date_yr_start := hr_au_routes.span_start(l_pay_effective_date,1,'01-07');
1837
1838 for process_element in get_element_entries
1839 loop
1840 /* Bug 5846272 - Use Existing Logic if Enh Retro Rule is 'N' ELSE
1841 use Enhanced Retropay functionality
1842 */
1843 IF l_adv_retro_flag = 'N'
1844 THEN
1845 -- is_retro_payment:=check_if_retro(process_element.element_entry_id,p_date_earned);
1846 -- if is_retro_payment='Y' then
1847 -- Note this processing is only done for retro less than 12 months, in prev fin year.
1848
1849 /*Bug 4418107 - The following piece of code has been introduced to count retro periods on the basis
1850 of Legal Employer.*/
1851
1852 x:=get_retro_period(process_element.element_entry_id,p_date_earned,p_tax_unit_id, retro_start_date,retro_end_date); /*Bug 4418107*/
1853
1854 if p_use_tax_flag = 'Y' then
1855 /* Bug 6139035 - Commented code - Check for Retro LT12 Prev should be based on Date Paid(Effective Date)
1856 and not Date Earned
1857 if months_between(p_date_earned,retro_end_date) <= 12 and p_date_earned >= financial_year_span_start
1858 and retro_end_date < financial_year_span_start */
1859 IF months_between(l_pay_effective_date,retro_end_date) <= 12 AND l_pay_effective_date >=
1860 l_eff_date_yr_start AND retro_end_date < l_eff_date_yr_start
1861 THEN
1862 l_retro_end_date := to_number(to_char(retro_end_date,'DDMM')); -- Bug 4412537
1863 /*Bug 4357306 - Logic given below has been implemented to count the retro end dates.
1864 If the customer pays for two different retro elements for the same retro
1865 period then the dates were counted twice, so to avoid that a table has been
1866 created where the values get stored in the index. If there is already a index
1867 for retro end dates the counter l_retro_periods is not incremented and a new index
1868 is not created, but if a new retro end date is being processed then the counter is
1869 incremented and index is also created in the table num_tab.*/
1870 IF num_tab.EXISTS(l_retro_end_date) THEN
1871 num_tab(l_retro_end_date) := num_tab(l_retro_end_date) + 1;
1872 ELSE
1873 num_tab(l_retro_end_date) := 1;
1874 l_retro_periods:=l_retro_periods + 1;
1875 END IF;
1876
1880 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) - to_number(process_element.retro_amount);
1877 -- Add up the amounts. If it's a pre tax deduction then need to subtract.
1878 IF tot_period_amount.EXISTS(l_retro_end_date) THEN
1879 if process_element.classification_name = 'Pre Tax Deductions' then
1881 else
1882 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) + to_number(process_element.retro_amount);
1883 end if;
1884 ELSE
1885 if process_element.classification_name = 'Pre Tax Deductions' then
1886 tot_period_amount(l_retro_end_date) := 0 - to_number(process_element.retro_amount);
1887 else
1888 tot_period_amount(l_retro_end_date) := to_number(process_element.retro_amount);
1889 end if;
1890 END IF;
1891
1892 end if;
1893 elsif p_use_tax_flag = 'N' then
1894 /*Bug 4415795 - This portion has been introduced so that the count_retro_periods
1895 return the value for current year case before 01-JUL-2005 and for
1896 less than 12 months previous year case after 01-JUL-2005*/
1897
1898 if months_between(p_date_earned,retro_end_date) <= 12 and p_date_earned >= financial_year_span_start
1899 and retro_end_date >= financial_year_span_start
1900 then
1901 l_retro_end_date := to_number(to_char(retro_end_date,'DDMM')); -- Bug 4412537
1902 IF num_tab.EXISTS(l_retro_end_date) THEN
1903 num_tab(l_retro_end_date) := num_tab(l_retro_end_date) + 1;
1904 ELSE
1905 num_tab(l_retro_end_date) := 1;
1906 l_retro_periods:=l_retro_periods + 1;
1907 END IF;
1908
1909 -- Add up the amounts. If it's a pre tax deduction then need to subtract.
1910 IF tot_period_amount.EXISTS(l_retro_end_date) THEN
1911 if process_element.classification_name = 'Pre Tax Deductions' then
1912 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) - to_number(process_element.retro_amount);
1913 else
1914 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) + to_number(process_element.retro_amount);
1915 end if;
1916 ELSE
1917 if process_element.classification_name = 'Pre Tax Deductions' then
1918 tot_period_amount(l_retro_end_date) := 0 - to_number(process_element.retro_amount);
1919 else
1920 tot_period_amount(l_retro_end_date) := to_number(process_element.retro_amount);
1921 end if;
1922 END IF;
1923
1924 end if;
1925 end if;
1926 ELSE
1927 /* Bug 5846272
1928 Section for Enhanced Retropay - l_adv_retro_flag = 'Y'
1929 Logic check for p_use_tax_flag is not implemented here because Enhanced Retropay
1930 was not supported prior to Jul-2005.
1931 */
1932
1933 x := get_retro_time_span
1934 (
1935 p_element_entry_id => process_element.element_entry_id,
1936 p_date_earned => p_date_earned,
1937 p_tax_unit_id => p_tax_unit_id,
1938 p_retro_start_date => retro_start_date,
1939 p_retro_end_date => retro_end_date,
1940 p_orig_effective_date => l_orig_effective_date,
1941 p_retro_effective_date => l_retro_eff_date,
1942 p_time_span => l_time_span,
1943 p_retro_type => l_retro_type
1944 );
1945
1946 IF l_time_span = 'LT12 Prev'
1947 THEN
1948 l_retro_end_date := to_number(to_char(retro_end_date,'DDMM')); -- Bug 4412537
1949
1950 IF num_tab.EXISTS(l_retro_end_date) THEN
1951 num_tab(l_retro_end_date) := num_tab(l_retro_end_date) + 1;
1952 ELSE
1953 num_tab(l_retro_end_date) := 1;
1954 l_retro_periods:=l_retro_periods + 1;
1955 END IF;
1956
1957 -- Add up the amounts. If it's a pre tax deduction then need to subtract.
1958 IF tot_period_amount.EXISTS(l_retro_end_date) THEN
1959 IF process_element.classification_name = 'Pre Tax Deductions' then
1960 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) - to_number(process_element.retro_amount);
1961 ELSE
1962 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) + to_number(process_element.retro_amount);
1963 END IF;
1964 ELSE
1965 IF process_element.classification_name = 'Pre Tax Deductions' THEN
1966 tot_period_amount(l_retro_end_date) := 0 - to_number(process_element.retro_amount);
1967 ELSE
1968 tot_period_amount(l_retro_end_date) := to_number(process_element.retro_amount);
1969 END IF;
1970 END IF;
1971 END IF;
1972
1973 END IF; /* End of Enhanced Retropay part */
1974
1975 end loop;
1976
1977 -- Check if any periods have 0 total retro amount. If they do then don't count the retro period.
1978
1979 l_count := tot_period_amount.FIRST;
1980 while l_count is not null loop
1981
1982 if tot_period_amount(l_count) = 0 then
1986 end loop;
1983 l_retro_periods := l_retro_periods - 1;
1984 end if;
1985 l_count := tot_period_amount.NEXT(l_count);
1987
1988 if g_debug then
1989 hr_utility.set_location('Return l_retro_periods '||l_retro_periods,60);
1990 hr_utility.set_location('Exiting '||l_procedure,70);
1991 end if;
1992
1993 return l_retro_periods;
1994
1995 end count_retro_periods;
1996
1997
1998
1999 function calculate_tax(p_date_earned in pay_payroll_actions.date_earned%TYPE,
2000 p_period_amount in number,
2001 p_period_frequency in number,
2002 p_tax_scale in number,
2003 p_a1_variable in number,
2004 p_b1_variable in number
2005 )return number is
2006
2007 pay_per_week number;
2008 tax_on_weekly number;
2009 tax_on_total_period number;
2010 l_procedure varchar2(80);
2011
2012 begin
2013 g_debug := hr_utility.debug_enabled;
2014
2015 if g_debug then
2016 l_procedure :='pay_au_paye_ff.calculate_tax';
2017 hr_utility.set_location('Entering '||l_procedure,10);
2018 hr_utility.set_location('IN p_date_earned '||to_char(p_date_earned,'dd/mm/yyyy'),20);
2019 hr_utility.set_location('IN p_period_amount '||p_period_amount,25);
2020 hr_utility.set_location('IN p_period_frequency '||p_period_frequency,30);
2021 hr_utility.set_location('IN p_tax_scale '||p_tax_scale,35);
2022 hr_utility.set_location('IN p_al_variable '||p_a1_variable,40);
2023 hr_utility.set_location('IN p_bl_variable '||p_a1_variable,45);
2024
2025 end if;
2026
2027 pay_per_week:=convert_to_week(p_period_frequency,p_period_amount);
2028
2029 tax_on_weekly:=(p_a1_variable*pay_per_week)- p_b1_variable;
2030
2031 if p_tax_scale <> 4 then
2032 tax_on_weekly:=round(tax_on_weekly);
2033
2034 else
2035 tax_on_weekly:=trunc(tax_on_weekly);
2036
2037 end if;
2038
2039
2040 tax_on_total_period:=convert_to_period_amt(p_period_frequency,tax_on_weekly,p_tax_scale);
2041
2042 if g_debug then
2043 hr_utility.set_location('Return tax_on_total_period '||tax_on_total_period,50);
2044 hr_utility.set_location('Exiting '||l_procedure,60);
2045 end if ;
2046
2047 return tax_on_total_period;
2048
2049 end calculate_tax;
2050
2051
2052
2053 function check_fixed_deduction(p_assignment_id in per_all_assignments_f.assignment_id%TYPE, p_date_earned in date)
2054 return varchar2 is
2055 /* Bug 4374115 - Added check for Reverse Runs */
2056 cursor check_fixed_deduction(p_assignment_id in number, p_date_earned in date)
2057 is
2058 select 'Y'
2059 from
2060 pay_element_types_f pet,
2061 pay_input_values_f piv,
2062 pay_run_result_values prrv,
2063 pay_run_results prr,
2064 pay_assignment_actions paa,
2065 pay_payroll_actions ppa,
2066 per_time_periods ptp,
2067 per_all_assignments_f paaf
2068 where pet.element_name = 'Extra Withholding Payments'
2069 and piv.name='Withholding Amount'
2070 and pet.element_type_id=piv.element_type_id
2071 and piv.input_value_id=prrv.input_value_id
2072 and prrv.run_result_id=prr.run_result_id
2073 and nvl(prrv.result_value,'0') > '0' /*Bug 4588483 */
2074 and prr.assignment_action_id=paa.assignment_action_id
2075 and paa.payroll_action_id=ppa.payroll_action_id
2076 and ptp.payroll_id = ppa.payroll_id
2077 and paa.assignment_id = p_assignment_id /* Bug#2563515 */
2078 and paa.assignment_id = paaf.assignment_id /* Bug#2563515 */
2079 and p_date_earned between paaf.effective_start_date and paaf.effective_end_date /* Bug#2563515 */
2080 and p_date_earned between ptp.start_date and ptp.end_date
2081 and ppa.date_earned between ptp.start_date and ptp.end_date
2082 /* Bug - 2491328 Join added for improving the performance */
2083 and pet.element_type_id = prr.element_type_id
2084 and pet.legislation_code = 'AU'
2085 and piv.legislation_code = 'AU'
2086 /* Bug - 2491328 Join added for improving the performance */
2087 /* Bug 4374115 - Start */
2088 and not exists(
2089 select pai.locking_action_id
2090 from pay_assignment_actions paa1,
2091 pay_payroll_actions ppa1,
2092 pay_action_interlocks pai
2093 where ppa1.payroll_action_id = paa1.payroll_action_id
2094 and ppa1.action_type = 'V'
2095 and paa1.assignment_id = p_assignment_id
2096 and pai.locking_action_id = paa1.assignment_action_id
2097 and pai.locked_action_id = paa.assignment_action_id
2098 )
2099 /* Bug 4374115 - End */
2100 and not exists(
2101 select piv.name
2102 from
2103 pay_element_types_f pet,
2104 pay_input_values_f piv,
2105 pay_input_values_f piv1,
2106 pay_element_links_f pel, /* Bug#2563515 */
2107 pay_element_entries_f peef, /* Bug#2563515 */
2108 pay_element_entry_values_f peev,
2109 pay_element_entry_values_f peev1
2110 where pet.element_name = 'Extra Withholding Payments'
2111 and pet.element_type_id= piv.element_type_id
2112 and pet.element_type_id = pel.element_type_id /* Bug#2563515 */
2113 and pel.element_link_id = peef.element_link_id /* Bug#2563515 */
2114 and peef.element_entry_id = peev.element_entry_id /* Bug#2563515 */
2115 and peef.element_entry_id = peev1.element_entry_id /* Bug#2563515 */
2119 and nvl(peev1.screen_entry_value,'0') ='0'
2116 and piv.name='Withholding Percentage'
2117 and piv.input_value_id=peev.input_value_id
2118 and piv1.name='Withholding Amount'
2120 and piv1.input_value_id=peev1.input_value_id
2121 and peev.screen_entry_value is not null
2122 and peef.assignment_id = paaf.assignment_id /* Bug#2563515 */
2123 and p_date_earned between pet.effective_start_date and pet.effective_end_date
2124 and p_date_earned between peef.effective_start_date and peef.effective_end_date /* Bug#2563515 */
2125 and p_date_earned between pel.effective_start_date and pel.effective_end_date /* Bug#2563515 */
2126 and p_date_earned between peev1.effective_start_date and peev1.effective_end_date
2127 and p_date_earned between peev.effective_start_date and peev.effective_end_date
2128 /*Bug - 2491328 Join added for improving the performance */
2129 and pet.element_type_id=piv1.element_type_id
2130 );
2131
2132 l_deduction_flag varchar2(10);
2133
2134 l_procedure varchar2(80);
2135
2136 begin
2137 g_debug := hr_utility.debug_enabled;
2138
2139 if g_debug then
2140 l_procedure :='pay_au_paye_ff.check_fixed_deduction';
2141 hr_utility.set_location('Entering '||l_procedure,10);
2142 hr_utility.set_location('IN p_assignment_id '||p_assignment_id,20);
2143 hr_utility.set_location('IN p_date_earned '||to_char(p_date_earned,'dd/mm/yyyy'),30);
2144 end if ;
2145
2146 OPEN check_fixed_deduction(p_assignment_id , p_date_earned);
2147 FETCH check_fixed_deduction into l_deduction_flag;
2148 CLOSE check_fixed_deduction;
2149
2150 if l_deduction_flag is null then
2151 l_deduction_flag:='N';
2152 end if;
2153
2154 if g_debug then
2155 hr_utility.set_location('OUT l_deduction_flag '||l_deduction_flag,35);
2156 hr_utility.set_location('Exiting '||l_procedure,40);
2157 end if ;
2158
2159 return l_deduction_flag;
2160
2161 end check_fixed_deduction;
2162
2163 FUNCTION validate_data_magtape
2164 (p_data in varchar2)
2165 RETURN varchar2 is
2166
2167 l_pos_value VARCHAR2(1) := NULL ;
2168 l_data VARCHAR2(300):= NULL ;
2169 l_data_substr VARCHAR2(300):= NULL ;
2170 l_ins_result NUMBER := 0 ;
2171 l_counter NUMBER := 0 ;
2172 l_length NUMBER := 0 ;
2173 l_blank_counter NUMBER := 0 ;
2174
2175 BEGIN
2176 g_debug := hr_utility.debug_enabled;
2177 IF g_debug THEN
2178 hr_utility.trace('Entered function pay_au_paye_ff.validate_data_magtape');
2179 hr_utility.trace('IN p_data '||p_data);
2180 END IF;
2181
2182 IF (p_data IS NULL) THEN
2183 IF g_debug THEN
2184 hr_utility.trace('Exiting function pay_au_paye_ff.validate_data_magtape');
2185 END IF;
2186
2187 RETURN ' ';
2188 END IF;
2189
2190 IF g_debug THEN
2191 hr_utility.trace('Value of the in parameter p_data ==>' || p_data);
2192 END IF;
2193 l_data := replace(p_data,'_','-');
2194
2195 l_length := length(p_data);
2196 IF g_debug THEN
2197 hr_utility.trace('Length of the input data passed ==>' || l_length);
2198 END IF;
2199 FOR l_counter IN 1..l_length
2200 LOOP
2201 IF g_debug THEN
2202 hr_utility.trace('Counter value ==>' || l_counter);
2203 END IF;
2204 l_pos_value := upper(substr(l_data,l_counter,1));
2205 IF g_debug THEN
2206 hr_utility.trace('Value at position ' || l_counter || '==>' || l_pos_value);
2207 END IF;
2208 IF (l_pos_value = ' ' and l_counter > 1) THEN /* No need to check first character */
2209 IF (l_blank_counter = l_counter - 1) THEN
2210 IF g_debug THEN
2211 hr_utility.trace('Value ' || l_pos_value || 'is invalid. More than one space between words.');
2212 END IF;
2213 /* Remove all blank spaces after the first. */
2214 l_data_substr := substr(l_data, l_counter, l_length);
2215 l_data := substr(l_data, 1, l_blank_counter);
2216 l_data_substr := ltrim(l_data_substr);
2217 l_data := concat(l_data, l_data_substr);
2218 /* We have now reduced the length of the string therefore we need to reset
2219 l_length and check the new character in the current value of l_counter
2220 as we cannot reassign value of l_counter. */
2221 l_pos_value := upper(substr(l_data,l_counter,1));
2222 l_length := length(l_data);
2223 END IF;
2224 l_blank_counter := l_counter;
2225 END IF;
2226
2227 l_ins_result := instr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ()&/"''-',l_pos_value);
2228
2229 IF l_ins_result = 0 THEN
2230 IF g_debug THEN
2231 hr_utility.trace('Value ' || l_pos_value || 'is invalid');
2232 END IF;
2233 l_data := replace(l_data,l_pos_value,' ');
2234 ELSE
2235 IF g_debug THEN
2236 hr_utility.trace('Value ' || l_pos_value || 'is valid');
2237 END IF;
2238 END IF;
2239 END LOOP;
2240
2241 IF (ltrim(l_data) IS NULL) THEN
2242 IF g_debug THEN
2243 hr_utility.trace('Exiting function pay_au_paye_ff.validate_data_magtape');
2244 END IF;
2245 RETURN ' ';
2246 ELSE
2247 IF g_debug THEN
2251 RETURN ltrim(l_data);
2248 hr_utility.trace('Final validated value ==>' || ltrim(l_data));
2249 hr_utility.trace('Exiting function pay_au_paye_ff.validate_data_magtape');
2250 END IF;
2252 END IF;
2253
2254 END validate_data_magtape;
2255
2256 /* Bug No : 2977425 - Added the new formula function */
2257 FUNCTION get_table_value (BUSINESS_GROUP_ID IN hr_organization_units.business_group_id%TYPE,EARN_NAME IN VARCHAR2, scale IN varchar2,EARNING_VALUE IN number,PERIOD_DATE in date,a OUT NOCOPY varchar2, b OUT NOCOPY varchar2)
2258 RETURN VARCHAR2 IS
2259 msg varchar2(1000);
2260 l_procedure varchar2(80);
2261 BEGIN
2262 g_debug := hr_utility.debug_enabled;
2263
2264 if g_debug then
2265 l_procedure :='pay_au_paye_ff.get_table_value';
2266 hr_utility.set_location('Entering '||l_procedure,10);
2267 hr_utility.set_location('IN BUSINESS_GROUP_ID '||BUSINESS_GROUP_ID ,20);
2268 hr_utility.set_location('IN EARN_NAME '||EARN_NAME,25);
2269 hr_utility.set_location('IN scale '||scale,30);
2270 hr_utility.set_location('IN EARNING_VALUE '||EARNING_VALUE,35);
2271 hr_utility.set_location('IN PERIOD_DATE '||to_char(PERIOD_DATE,'dd/mm/yyyy'),40);
2272
2273 end if;
2274
2275 IF EARNING_VALUE < 0 then
2276 msg := fffunc.gfm('PAY', 'HR_AU_NEGATIVE_EARNINGS','EARN_NAME',EARN_NAME, 'EARNING_VALUE',to_char(EARNING_VALUE));
2277 if g_debug then
2278 hr_utility.set_location('Return msg '||msg,50);
2279 hr_utility.set_location('Exiting '||l_procedure,60);
2280 end if;
2281
2282 RETURN msg;
2283 ELSE
2284 a := hruserdt.get_table_value (BUSINESS_GROUP_ID, scale, scale||'a', TO_CHAR(EARNING_VALUE), PERIOD_DATE);
2285 b := hruserdt.get_table_value (BUSINESS_GROUP_ID, scale, scale||'b', TO_CHAR(EARNING_VALUE), PERIOD_DATE);
2286
2287 if g_debug then
2288 hr_utility.set_location('OUT a '||a,45);
2289 hr_utility.set_location('OUT b '||b,50);
2290 hr_utility.set_location('Exiting '||l_procedure,60);
2291 end if;
2292
2293 RETURN 'ZZZ';
2294 END IF;
2295
2296
2297 END;
2298
2299
2300 /* Bug No : 3306112 - The new function will be called from view "pay_au_asg_element_payments_v"
2301 It return value of Hours in case the element_id passed is attached to the Salary Basis
2302 */
2303 FUNCTION get_salary_basis_hours
2304 (
2305 p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
2306 p_element_type_id in pay_element_entries_f.element_entry_id%TYPE,
2307 p_pay_bases_id in per_all_assignments_f.pay_basis_id%TYPE
2308 )
2309 RETURN NUMBER IS
2310
2311 l_element_type_id pay_element_entries_f.element_entry_id%TYPE;
2312 l_result number := NULL;
2313 l_procedure varchar2(80);
2314
2315 CURSOR Cr_value IS (
2316 SELECT prv.result_value
2317 from pay_run_results prr,
2318 pay_run_result_values prv,
2319 pay_element_types_f pet,
2320 pay_input_values_f piv
2321 where prr.assignment_action_id = p_assignment_action_id
2322 and prv.run_result_id = prr.run_result_id
2323 and prv.input_value_id = piv.input_value_id
2324 and prr.element_type_id = pet.element_type_id
2325 and piv.uom like 'H_%'
2326 and piv.element_type_id= pet.element_type_id
2327 and pet.element_name= 'Normal Hours');
2328
2329 CURSOR Cr_element_type_id IS (
2330 SELECT pivf.element_type_id /*Bug# 3665680*/
2331 FROM pay_input_values_f pivf, per_pay_bases ppb
2332 WHERE pivf.input_value_id = ppb.input_value_id
2333 AND ppb.pay_basis_id = p_pay_bases_id);
2334
2335 BEGIN
2336 g_debug := hr_utility.debug_enabled;
2337
2338 if g_debug then
2339 l_procedure :='pay_au_paye_ff.get_salary_basis_hours';
2340 hr_utility.set_location('Entering '||l_procedure,10);
2341 hr_utility.set_location('IN p_assignment_action_id '||p_assignment_action_id,20);
2342 hr_utility.set_location('IN p_element_type_id '||p_element_type_id,30);
2343 hr_utility.set_location('IN p_pay_bases_id '||p_pay_bases_id,40);
2344 end if ;
2345
2346 OPEN Cr_element_type_id;
2347 FETCH Cr_element_type_id INTO l_element_type_id;
2348 CLOSE Cr_element_type_id;
2349
2350 IF p_element_type_id = l_element_type_id THEN
2351 OPEN Cr_value;
2352 FETCH Cr_value INTO l_result;
2353 CLOSE Cr_value;
2354 END IF;
2355
2356 if g_debug then
2357 hr_utility.set_location('OUT l_result '||l_result,50);
2358 hr_utility.set_location('Exiting '||l_procedure,60);
2359 end if ;
2360
2361 RETURN l_result;
2362
2363
2364
2365 END;
2366
2367
2368 /* Bug No : 3245909 - To get prepayment locking action_id for AU_PAYMENTS route */
2369 function get_pp_action_id(p_action_type in varchar2,
2370 p_action_id in number
2371 ) return number
2372 is
2373 CURSOR Cr_action IS
2374 select INTLK.locking_action_id
2375 from pay_action_interlocks INTLK,
2376 pay_assignment_actions paa,
2377 pay_payroll_actions ppa
2378 where INTLK.locked_action_id = p_action_id
2379 and INTLK.locking_action_id = paa.assignment_action_id
2380 and paa.payroll_action_id = ppa.payroll_action_id
2381 and ppa.action_type in ('P', 'U')
2382 and paa.source_action_id is null;
2386
2383
2384 l_action_id number;
2385 l_procedure varchar2(80);
2387 begin
2388 g_debug := hr_utility.debug_enabled;
2389 if g_debug then
2390 l_procedure:='pay_au_paye_ff.get_pp_action_id';
2391 hr_utility.set_location('Entering '||l_procedure,10);
2392 hr_utility.set_location('IN p_action_type '||p_action_type,20);
2393 hr_utility.set_location('IN p_action_id '|| p_action_id,30);
2394 end if;
2395
2396 --
2397 if (p_action_type in ('P', 'U')) then
2398 l_action_id := p_action_id;
2399 elsif (p_action_type in ('R', 'Q','I','B')) then
2400 --
2401 -- Always return the master prepayment action.
2402 --
2403 OPEN Cr_action;
2404 FETCH Cr_action INTO l_action_id;
2405 CLOSE Cr_action;
2406 else
2407 l_action_id := null;
2408 end if;
2409
2410 if g_debug then
2411 hr_utility.set_location('OUT l_action_id '||l_action_id,40);
2412 hr_utility.set_location('Exiting '||l_procedure,50);
2413 end if;
2414 --
2415 return l_action_id;
2416 --
2417
2418 end get_pp_action_id;
2419
2420 /*Bug# 3935471
2421 The purpose of this function is to check whether child assignment action has the same tax unit id as compared
2422 to the master assignment action id. If the tax unit id is same it returns 'Y' else it returns 'N'.
2423 If the assignment action id passed to this function dosen't have a child then this function returns 'Y'.
2424 */
2425
2426 FUNCTION check_tax_unit_id
2427 (
2428 p_assignment_action_id in NUMBER,
2429 p_tax_unit_id IN NUMBER
2430 )
2431 RETURN VARCHAR2 IS
2432
2433 CURSOR c_get_master_tax_unit_id
2434 IS
2435 SELECT paa_master.tax_unit_id
2436 FROM pay_assignment_actions paa_child,
2437 pay_assignment_actions paa_master
2438 WHERE paa_child.assignment_action_id = p_assignment_action_id
2439 AND paa_master.assignment_action_id = paa_child.source_action_id;
2440
2441 l_flag VARCHAR2(10);
2442 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
2443 l_procedure varchar2(80);
2444
2445 BEGIN
2446 g_debug := hr_utility.debug_enabled;
2447 if g_debug then
2448 l_procedure :='pay_au_paye_ff.check_tax_unit_id';
2449 hr_utility.set_location('Entering '||l_procedure,10);
2450 hr_utility.set_location('IN p_assignment_action_id '||p_assignment_action_id,20);
2451 hr_utility.set_location('IN p_tax_unit_id '|| p_tax_unit_id,30);
2452 end if;
2453 OPEN c_get_master_tax_unit_id;
2454 FETCH c_get_master_tax_unit_id INTO l_tax_unit_id;
2455 IF c_get_master_tax_unit_id%NOTFOUND THEN
2456 l_flag := 'Y';
2457
2458 if g_debug then
2459 hr_utility.set_location('Return l_flag '||l_flag,40);
2460 hr_utility.set_location('Exiting '||l_procedure,50);
2461 end if;
2462 RETURN l_flag;
2463 ELSE
2464 IF l_tax_unit_id <> p_tax_unit_id THEN
2465 l_flag := 'N';
2466
2467 if g_debug then
2468 hr_utility.set_location('Return l_flag '||l_flag,40);
2469 hr_utility.set_location('Exiting '||l_procedure,50);
2470 end if;
2471
2472 RETURN l_flag;
2473 ELSE
2474 l_flag := 'Y';
2475
2476 if g_debug then
2477 hr_utility.set_location('Return l_flag '||l_flag,40);
2478 hr_utility.set_location('Exiting '||l_procedure,50);
2479 end if;
2480
2481 RETURN l_flag;
2482 END IF;
2483 END IF;
2484
2485 CLOSE c_get_master_tax_unit_id;
2486
2487 end check_tax_unit_id;
2488
2489 /* Bug#5934468 Function returns ths spread earning. This earning gets used in
2490 formula AU_HECS_DEDUCTION and AU_SFSS_DEDUCTION
2491 */
2492
2493 function get_spread_earning
2494 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2495 p_date_paid in date,
2496 p_pre_tax in number,
2497 p_spread_earning in number) return number is
2498
2499 cursor get_period_spread_over is
2500 select prv.RESULT_VALUE period_spread_over, prr.run_result_id, pee.creator_type
2501 from pay_element_types_f pet,
2502 pay_input_values_f piv,
2503 pay_run_result_values prv,
2504 pay_run_results prr,
2505 pay_element_entries_f pee
2506 where prr.assignment_action_id=p_assignment_action_id
2507 and prr.RUN_RESULT_ID = prv.RUN_RESULT_ID
2508 and prv.input_value_id = piv.input_value_id
2509 and piv.name ='Periods Spread Over'
2510 and piv.legislation_code='AU'
2511 and piv.element_type_id = pet.element_type_id
2512 and pet.legislation_code='AU'
2513 and pet.element_type_id = prr.element_type_id
2514 and pet.element_name='Spread Deduction'
2515 and prr.source_id = pee.element_entry_id
2516 and pee.creator_type not in ('EE','RR')
2517 and p_date_paid between pet.effective_start_date and pet.effective_end_date
2518 and p_date_paid between piv.effective_start_date and piv.effective_end_date
2519 and p_date_paid between pee.effective_start_date and pee.effective_end_date;
2520
2521 cursor get_spread_earning(p_run_result_id pay_run_results.run_result_id%type) is
2522 select prv.RESULT_VALUE
2523 from pay_input_values_f piv,
2524 pay_run_result_values prv,
2525 pay_run_results prr
2526 where prr.RUN_RESULT_ID = p_run_result_id
2527 and prr.RUN_RESULT_ID = prv.RUN_RESULT_ID
2528 and prv.input_value_id = piv.input_value_id
2529 and piv.name ='Total Payment'
2530 and piv.legislation_code='AU'
2534 cursor get_retro_spread_earning is
2531 and p_date_paid between piv.effective_start_date and piv.effective_end_date;
2532
2533 /* new cursor for bug 6669058 */
2535 select nvl(sum(prv.RESULT_VALUE),0)
2536 from pay_element_types_f pet,
2537 pay_input_values_f piv,
2538 pay_run_result_values prv,
2539 pay_run_results prr,
2540 pay_element_entries_f pee
2541 where prr.assignment_action_id=p_assignment_action_id
2542 and prr.RUN_RESULT_ID = prv.RUN_RESULT_ID
2543 and prv.input_value_id = piv.input_value_id
2544 and piv.name ='Total Payment'
2545 and piv.legislation_code='AU'
2546 and piv.element_type_id = pet.element_type_id
2547 and pet.legislation_code='AU'
2548 and pet.element_type_id = prr.element_type_id
2549 and pet.element_name='Spread Deduction'
2553 and p_date_paid between piv.effective_start_date and piv.effective_end_date
2550 and prr.source_id = pee.element_entry_id
2551 and pee.creator_type in ('EE','RR')
2552 and p_date_paid between pet.effective_start_date and pet.effective_end_date
2554 and p_date_paid between pee.effective_start_date and pee.effective_end_date;
2555
2556 l_total_spread_earning number;
2557 l_spread_earning number;
2558 l_spread_percent number;
2559 l_retro_spread_earning number;
2560 l_spread_earning_total number;
2561
2562 l_procedure varchar2(80);
2563
2564 begin
2565
2566 g_debug := hr_utility.debug_enabled;
2567 if g_debug then
2568 l_procedure :='pay_au_paye_ff.get_spread_earning';
2569 hr_utility.set_location('Entering '||l_procedure,10);
2570 hr_utility.set_location('IN p_assignment_action_id '||p_assignment_action_id,20);
2571 hr_utility.set_location('IN p_date_paid '|| p_date_paid,30);
2572 hr_utility.set_location('IN p_pre_tax '|| p_pre_tax,30);
2573 hr_utility.set_location('IN p_spread_earning '|| p_spread_earning,30);
2574 end if;
2575
2576 l_total_spread_earning := 0;
2577 l_spread_earning := 0;
2578 l_retro_spread_earning := 0;
2579 l_spread_earning_total := 0;
2580
2581 /* Calculate spread earning for current period only - bug 6669058 */
2582 open get_retro_spread_earning;
2583 fetch get_retro_spread_earning into l_retro_spread_earning;
2584 close get_retro_spread_earning;
2585
2586 l_spread_earning_total := p_spread_earning -l_retro_spread_earning;
2587
2588 if l_spread_earning_total = 0 then
2589 return l_total_spread_earning;
2590 else
2591 for rec in get_period_spread_over
2592 loop
2593
2594 open get_spread_earning(rec.run_result_id);
2595 fetch get_spread_earning into l_spread_earning;
2596 close get_spread_earning;
2597
2598 l_spread_percent := l_spread_earning/l_spread_earning_total;
2599 l_spread_earning := (l_spread_earning- p_pre_tax * l_spread_percent )/rec.period_spread_over ;
2600 l_total_spread_earning := l_total_spread_earning + l_spread_earning;
2601
2602 end loop;
2603 end if;
2604
2605 if g_debug then
2606 hr_utility.set_location('OUT p_spread_earning '|| l_total_spread_earning,30);
2607 hr_utility.set_location('Leaving '||l_procedure,10);
2608 end if;
2609
2610
2611 return l_total_spread_earning;
2612 end;
2613
2614 /* new function for bug#6669058 */
2615 function get_retro_spread_earning
2616 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2617 p_date_paid in date,
2618 p_pre_tax in number,
2619 p_spread_earning in number) return number is
2620
2621 cursor get_retro_spread_earning is
2622 select nvl(sum(prv.RESULT_VALUE),0)
2623 from pay_element_types_f pet,
2624 pay_input_values_f piv,
2625 pay_run_result_values prv,
2626 pay_run_results prr,
2627 pay_element_entries_f pee
2628 where prr.assignment_action_id=p_assignment_action_id
2629 and prr.RUN_RESULT_ID = prv.RUN_RESULT_ID
2630 and prv.input_value_id = piv.input_value_id
2631 and piv.name ='Total Payment'
2632 and piv.legislation_code='AU'
2633 and piv.element_type_id = pet.element_type_id
2634 and pet.legislation_code='AU'
2635 and pet.element_type_id = prr.element_type_id
2636 and pet.element_name='Spread Deduction'
2637 and prr.source_id = pee.element_entry_id
2638 and pee.creator_type in ('EE','RR')
2639 and p_date_paid between pet.effective_start_date and pet.effective_end_date
2640 and p_date_paid between piv.effective_start_date and piv.effective_end_date
2641 and p_date_paid between pee.effective_start_date and pee.effective_end_date;
2642
2643 l_retro_spread_earning number;
2644
2645 l_procedure varchar2(80);
2646
2647 begin
2648
2649 g_debug := hr_utility.debug_enabled;
2650 if g_debug then
2651 l_procedure :='pay_au_paye_ff.get_spread_earning';
2652 hr_utility.set_location('Entering '||l_procedure,10);
2653 hr_utility.set_location('IN p_assignment_action_id '||p_assignment_action_id,20);
2654 hr_utility.set_location('IN p_date_paid '|| p_date_paid,30);
2655 hr_utility.set_location('IN p_pre_tax '|| p_pre_tax,30);
2656 hr_utility.set_location('IN p_spread_earning '|| p_spread_earning,30);
2657 end if;
2658
2659 l_retro_spread_earning := 0;
2660
2661 open get_retro_spread_earning;
2662 fetch get_retro_spread_earning into l_retro_spread_earning;
2663 close get_retro_spread_earning;
2664
2665 return l_retro_spread_earning;
2666 end;
2667
2668 /* bug6809877 - Adeed new function get_etp_pay_component */
2669 function get_etp_pay_component
2670 ( p_assignment_id in per_all_assignments_f.assignment_id%type,
2671 p_date_earned in date) return varchar2 is
2672
2673 cursor etp_pay_csr ( c_assignment_id per_all_assignments_f.assignment_id%type,
2674 c_date_earned date) is
2675 select peev.screen_entry_value
2676 from pay_element_types_f pet,
2677 pay_input_values_f piv,
2678 pay_element_entries_f pee,
2679 pay_element_entry_values_f peev
2680 where pee.assignment_id = c_assignment_id
2681 and piv.name ='Pay ETP Components'
2682 and piv.legislation_code='AU'
2683 and piv.element_type_id = pet.element_type_id
2684 and pet.legislation_code='AU'
2685 and pet.element_name='ETP on Termination'
2686 and piv.input_value_id = peev.input_value_id
2687 and peev.element_entry_id = pee.element_entry_id
2688 and c_date_earned between pet.effective_start_date and pet.effective_end_date
2689 and c_date_earned between piv.effective_start_date and piv.effective_end_date
2690 and c_date_earned between pee.effective_start_date and pee.effective_end_date
2691 and c_date_earned between peev.effective_start_date and peev.effective_end_date
2692 and rownum = 1;
2693
2694 l_etp_pay pay_element_entry_values_f.screen_entry_value%type;
2695
2696 begin
2697
2698 open etp_pay_csr (p_assignment_id, p_date_earned);
2699 fetch etp_pay_csr into l_etp_pay;
2700 if etp_pay_csr%notfound then
2701 l_etp_pay := 'zzz';
2702 end if;
2703 close etp_pay_csr;
2704
2705 return l_etp_pay;
2706
2707 end;
2708
2709 end pay_au_paye_ff;
[Home] [Help]