1 PACKAGE BODY pay_au_paye_ff AS
2 /* $Header: pyaufmla.pkb 120.58.12020000.15 2012/07/04 21:01:42 amnaraya 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 ** 20-APR-2009 skshin 115.85 Bug#7665727 Created count_retro_periods_2009 called from count_retro_periods and modified get_spread_earning function for HECS/SFSS calculation after 01-JUL-2009.
121 ** 20-APR-2009 skshin 115.87 Bug#8406009 Added new function calc_average_earnings and calc_lt12_prev_spread_tax
122 ** 15-JUL-2009 skshin 115.89 Bug#8630738 Modified cursor get_element_entries in count_retro_periods_2009 to return rows for Pre Tax Deduction
123 ** 15-JUL-2009 skshin 115.90 Bug#8682739 Modified cursor c_get_le_period_num in periods_since_hire_date to be based on date_earned
124 ** 30-JUL-2009 skshin 115.91 Bug#8725341 Added Earnings_Leave_Loading balance to be retrieved effective from 01-JUL-2009 in calculate_asg_prev_value function
125 ** 01-SEP-2009 skshin 115.92 Bug#8847457 Added check to select assignment action_status = 'C' to check_fixed_deduction cursor in check_fixed_deduction function
126 ** 08-Oct-2009 avenkatk 115.93 Bug#8765082 Added New Function get_retro_leave_load
127 ** 22-Jul-2010 avenkatk 115.94 Bug#9922156 Modified count_retro_periods_2009 for Leave Loading Tax free case in first period
128 ** 30-Mar-2011 skshin 115.96 Bug#11681524 Modified to fetch original average earnings for retro spread calculation
129 ** 28-Jul-2011 skshin 115.99 Bug#12753997 Modifed c_get_periods cursor and add c_get_periods2 cursor to fetch number of pay periods based on payment date
130 ** 24-Aug-2011 skshin 115.101 Bug#12873456 Added new function get_retro_tax_var_fixed
131 ** 21-Sep-2011 skshin 115.102 Bug#12989692 Modifed calc_lt12_prev_spread_tax function for flood levy deduction
132 ** 11-Oct-2011 dduvvuri 115.103 Bug#12767287 Added a new formula function get_earns_flood_levy for Qantas special case.
133 ** 14-Oct-2011 dduvvuri 115.105 Bug#12767287 Resolved gscc errors with the function get_earns_flood_levy
134 ** 02-Mar-2012 rrajaman 115.106 Bug#13793176 Added number_to_canonical conversion in cursor get_legal_emp_start_date
135 ** 26-Mar-2012 skshin 115.107 Bug#13885520 Made l_period_number to 1 when zero is returned when no date_earned exists in the new financial year
136 ** 17-Apr-2012 skshin 115.108 Bug#13879232 Added a new function get_addtl_message
137 ** 14-May-2012 dduvvuri 115.109 Bug#13906256 Added a new function get_mls_rate
138 ** 22-May-2012 dduvvuri 115.110 Bug#14100809 Added new function get_tax_scale_on_dp and new procedure upgrade_tax_scale
139 ** 23-May-2012 dduvvuri 115.112 Bug#14100809 Fixed GSCC error in package
140 ** 24-May-2012 dduvvuri 115.113 Bug#14100809 Fixed a QA issue in upgrade_tax_scale procedure
141 ** 29-May-2012 dduvvuri 115.115 Bug#14100809 Fixed GSCC error in package
142 ** 01-JUN-2012 dduvvuri 115.116 BUg#14100809 modified the cursor csr_asg_curr_scale to display only tax scale upgraded employees from 7 to 2
143 ** 01-JUN-2012 dduvvuri 115.117 Bug#14155616 resolved errors in upgrade for employees in multiple secured business groups
144 ** 01-JUN-2012 dduvvuri 115.118 BUg#14164637 added functionality for upgrading future or end-dated employee records from tax scale 7 to 2
145 ** 25-JUN-2012 skshin 115.119 BUg#14207669 Modified c_get_le_period_num cursor to compare assignment record with ptp.regular_payment_date because of assignment update on 01-JUL
146 */
147
148 g_debug boolean;
149
150 function round_to_5c
151 (
152 p_actual_amt in number
153 )
154 return number is
155
156
157 l_cents number;
158 l_rnd_amt number;
159
160
161 begin
162 g_debug := hr_utility.debug_enabled;
163 l_cents := p_actual_amt - trunc(p_actual_amt,1);
164
165 if l_cents <= 0.025 then
166 l_rnd_amt := 0;
167 elsif l_cents > 0.075 then
168 l_rnd_amt := 0.1;
169 else
170 l_rnd_amt := 0.05;
171 end if;
172
173 return (trunc(p_actual_amt,1) + l_rnd_amt);
174
175 exception
176 when others then
177 null;
178
179 end round_to_5c;
180
181
182 /*
183 * convert_to_period - converts weekly equivalents
184 * back to the period amounts using ATO rules.
185 */
186
187 function convert_to_period
188 (
189 p_ann_freq in number,
190 p_amt_week in number
191 )
192 return number is
193
194 l_amt_period number;
195
196 begin
197 g_debug := hr_utility.debug_enabled;
198 if p_ann_freq = 52 then
199 l_amt_period := p_amt_week;
200 elsif p_ann_freq = 26 then
201 l_amt_period := (p_amt_week * 2);
202 elsif p_ann_freq = 24 then
203 l_amt_period := round_to_5c (p_amt_week * 13 / 6);
204 elsif p_ann_freq = 12 then
205 l_amt_period := round_to_5c (p_amt_week * 13 / 3);
206 elsif p_ann_freq = 4 then /*Bug : 2888114*/
207 l_amt_period := p_amt_week * 13;
208 end if;
209
210 return (l_amt_period);
211
212 exception
213 when others then
214 null;
215
216 end convert_to_period;
217
218
219 /*
220 * convert_to_week - converts period amounts to equivalents
221 * weekly equivalents using ATO rules.
222 */
223
224 function convert_to_week
225 (
226 p_ann_freq in number,
227 p_amt_period in number
228 )
229 return number is
230
231 l_amt_week number := 0;
232 l_new_amt number;
233
234 begin
235 g_debug := hr_utility.debug_enabled;
236 if p_ann_freq = 52 then
237 l_amt_week := trunc (p_amt_period) + 0.99;
238 elsif p_ann_freq = 26 then
239 l_amt_week := trunc (p_amt_period / 2) + 0.99;
240 elsif p_ann_freq = 24 then
241 if (p_amt_period - trunc (p_amt_period)) = 0.33 then
242 l_new_amt := p_amt_period + 0.01;
243 else
244 l_new_amt := p_amt_period;
245 end if;
246 l_amt_week := trunc (l_new_amt * 6 / 13) + 0.99;
247 elsif p_ann_freq = 12 then
248 if (p_amt_period - trunc (p_amt_period)) = 0.33 then
249 l_new_amt := p_amt_period + 0.01;
250 else
251 l_new_amt := p_amt_period;
252 end if;
253 l_amt_week := trunc (l_new_amt * 3 / 13) + 0.99;
254 elsif p_ann_freq = 4 then /*Bug : 2888114*/
255 l_amt_week := trunc (p_amt_period/13) + 0.99;
256 end if;
257
258 return (l_amt_week);
259
260 exception
261 when others then
262 null;
263
264 end convert_to_week;
265
266
267
268 function get_mls_rate
269 ( p_element_entry_id IN NUMBER
270 ,p_date IN DATE
271 ,p_spouse IN VARCHAR2
272 )
273 return number IS
274
275 l_rate number := 0 ;
276 l_code varchar2(1);
277 l_element_type_id pay_element_types_f.element_type_id%TYPE;
278 l_input_value_id pay_input_values_f.input_value_id%TYPE;
279 l_spouse VARCHAR2(10);
280
281 CURSOR csr_paye_tax_element IS
282 SELECT pet.element_type_id
283 FROM pay_element_types_f pet
284 WHERE pet.element_name = 'Tax Information'
285 AND p_date BETWEEN pet.effective_start_date AND pet.effective_end_date
286 AND legislation_code = 'AU';
287
288 CURSOR csr_paye_spouse_input_value(p_element_type_id pay_input_values_f.element_type_id%TYPE) IS
289 SELECT piv.input_value_id
290 FROM pay_input_values_f piv
291 WHERE piv.element_type_id = p_element_type_id
292 AND p_date BETWEEN piv.effective_start_date AND piv.effective_end_date
293 AND piv.name = 'Spouse';
294
295 CURSOR get_spouse_value_2012(p_element_entry_id NUMBER,
296 p_inp_value_id IN NUMBER
297 )
298 IS
299 select
300 eev0.screen_entry_value
301 FROM
302 pay_element_entries_f pee,
303 pay_element_entry_values_f eev0
304 WHERE
305 eev0.element_entry_id = pee.element_entry_id
306 and eev0.input_value_id = p_inp_value_id
307 and pee.element_entry_id = p_element_entry_id
308 AND p_date between eev0.effective_start_date and eev0.effective_end_date;
309
310
311 begin
312 OPEN csr_paye_tax_element;
313 FETCH csr_paye_tax_element INTO l_element_type_id;
314 CLOSE csr_paye_tax_element;
315
316 OPEN csr_paye_spouse_input_value(l_element_type_id);
317 FETCH csr_paye_spouse_input_value INTO l_input_value_id;
318 CLOSE csr_paye_spouse_input_value;
319
320 OPEN get_spouse_value_2012(p_element_entry_id,l_input_value_id);
321 FETCH get_spouse_value_2012 INTO l_spouse;
322 CLOSE get_spouse_value_2012;
323
324 IF (l_spouse = 'YY' or l_spouse = 'NY' or l_spouse='YYA' or l_spouse='NYA') THEN
325 l_code := 'A';
326 ELSIF (l_spouse = 'YY' or l_spouse = 'NY' or l_spouse='YYB' or l_spouse='NYB') THEN
327 l_code := 'B';
328 ELSIF (l_spouse = 'YY' or l_spouse = 'NY' or l_spouse='YYC' or l_spouse='NYC') THEN
329 l_code := 'C';
330 END IF;
331
332 SELECT MEANING
333 INTO l_rate
334 FROM HR_LOOKUPS
335 WHERE lookup_type = 'AU_MLS_RATES'
336 AND lookup_code = l_code
337 AND enabled_flag = 'Y'
338 AND p_date between START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE,to_date('31-12-4712','DD-MM-YYYY'));
339
340 return l_rate/100 ;
341
342 end get_mls_rate;
343
344
345 function get_tax_scale_on_dp
346 ( p_element_entry_id IN NUMBER
347 ,p_date IN DATE
348 )
349 return varchar2 IS
350
351 l_element_type_id pay_element_types_f.element_type_id%TYPE;
352 l_input_value_id pay_input_values_f.input_value_id%TYPE;
353 l_tax_scale VARCHAR2(10);
354
355 CURSOR csr_paye_tax_element IS
356 SELECT pet.element_type_id
357 FROM pay_element_types_f pet
358 WHERE pet.element_name = 'Tax Information'
359 AND p_date BETWEEN pet.effective_start_date AND pet.effective_end_date
360 AND legislation_code = 'AU';
361
362 CURSOR csr_paye_txscale_input_value(p_element_type_id pay_input_values_f.element_type_id%TYPE) IS
363 SELECT piv.input_value_id
364 FROM pay_input_values_f piv
365 WHERE piv.element_type_id = p_element_type_id
366 AND p_date BETWEEN piv.effective_start_date AND piv.effective_end_date
367 AND piv.name = 'Tax Scale';
368
369 CURSOR get_txscale_value_2012(p_element_entry_id NUMBER,
370 p_inp_value_id IN NUMBER
371 )
372 IS
373 select
374 eev0.screen_entry_value
375 FROM
376 pay_element_entries_f pee,
377 pay_element_entry_values_f eev0
378 WHERE
379 eev0.element_entry_id = pee.element_entry_id
380 and eev0.input_value_id = p_inp_value_id
381 and pee.element_entry_id = p_element_entry_id
382 AND p_date between eev0.effective_start_date and eev0.effective_end_date;
383
384 begin
385
386 OPEN csr_paye_tax_element;
387 FETCH csr_paye_tax_element INTO l_element_type_id;
388 CLOSE csr_paye_tax_element;
389
390 OPEN csr_paye_txscale_input_value(l_element_type_id);
391 FETCH csr_paye_txscale_input_value INTO l_input_value_id;
392 CLOSE csr_paye_txscale_input_value;
393
394 OPEN get_txscale_value_2012(p_element_entry_id,l_input_value_id);
395 FETCH get_txscale_value_2012 INTO l_tax_scale;
396 CLOSE get_txscale_value_2012;
397
398 return l_tax_scale;
399
400 end get_tax_scale_on_dp;
401 /*
402 * periods_since_hire_date - returns the number of periods in the
403 * current tax year since the hire date.
404 */
405
406
407 function periods_since_hire_date
408 (
409 p_payroll_id in number,
410 p_assignment_id in per_all_assignments_f.assignment_id%type,
411 p_tax_unit_id in pay_assignment_actions.tax_unit_id%type, --2610141
412 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type, /*Bug 4451088 */
413 p_period_num in number,
414 p_period_start in date,
415 p_emp_hire_date in date,
416 p_use_tax_flag IN VARCHAR2 --2610141
417 )
418 return number is
419
420 l_year_start date;
421 l_month_no number;
422 l_year number;
423 l_period_num number;
424 /* Bug#2900253 */
425 l_leg_emp_date date;
426 l_check_date date;
427 l_period_end date;
428 l_procedure varchar2(80);
429 /* Bug:2900253 Added following cursor to get the date on which this assignment is
430 Enrolled into the legal employer existing as of this period start date */
431
432 /*Bug 4474896 - Modified cursor to pick the correct effective start date for the legal employer*/
433
434 cursor get_legal_emp_start_date (c_assignment_id per_all_assignments_f.assignment_id%type) is
435 select min(effective_start_date)
436 from per_all_assignments_f paf,
437 hr_soft_coding_keyflex hsck
438 where paf.assignment_id = c_assignment_id
439 and paf.SOFT_CODING_KEYFLEX_ID = hsck.soft_coding_keyflex_id
440 and hsck.segment1 = fnd_number.number_to_canonical(p_tax_unit_id) -- Bug 13793176
441 AND paf.effective_start_date <= l_period_end
442 AND paf.effective_end_date >= l_year_start;
443
444 /*Bug 2610141 - Cursor added to get the number of periods in the current year for an assignment assigned to a legal
445 employer */
446
447 /*Bug# 4474896 - Cursor modified to count periods on the basis of current payroll id
448 Bug# 6001930 - Modified Cursor for postive offset payrolls. Now periods will be
449 counted based on the Regular Payment Date.
450 Bug 8682739 - Modified for positve offset payrolls as l_period_end is now based on
451 date_earned. */
452
453 cursor c_get_le_period_num is
454 select count(ptp1.time_period_id) /*Bug 4438644, 6001930, 7228256*/
455 from per_time_periods ptp
456 ,per_time_periods ptp1
457 where exists (select 'EXISTS' from
458 per_assignments_f paf,
459 hr_soft_coding_keyflex hsck
460 where paf.assignment_id = p_assignment_id
461 and paf.SOFT_CODING_KEYFLEX_ID = hsck.soft_coding_keyflex_id
462 and hsck.segment1 = p_tax_unit_id
463 AND paf.effective_start_date <= l_period_end
464 AND paf.effective_end_date >= l_year_start
465 AND paf.effective_start_date <= ptp.regular_payment_date /* bug 14207669 */
466 AND paf.effective_end_date >= ptp.start_date)
467 AND ptp.payroll_id = p_payroll_id
468 AND ptp.start_date <= l_period_end
469 -- AND ptp.end_date >= l_year_start /* Commented Bug 6001930 */
470 /* Bug 6001930 - Start Changes */
471 AND ptp.regular_payment_date >= l_year_start
472 AND ptp.payroll_id = ptp1.payroll_id
473 AND ptp.regular_payment_date BETWEEN ptp1.start_date AND ptp1.end_date;
474 -- AND ptp1.start_date >= l_year_start /* Commented Bug 6139035 */
475 -- AND ptp1.end_date <= l_period_end; /* Commented bug 8682739 */
476 /* Bug 6001930 - End Changes */
477
478 cursor c_get_period_num (v_payroll_id number,
479 v_hire_date date) is
480 select period_num
481 from per_time_periods
482 where payroll_id = v_payroll_id
483 and v_hire_date between start_date and end_date;
484
485
486 /* Bug 2610141 - Cursor to Get the Period END date
487 Bug 4451088 - Changed Cursor to return end dates based in Input Date
488 */
489
490 CURSOR c_get_period_end_date(c_date date)
491 IS
492 select ptp.end_date
493 from per_time_periods ptp
494 where ptp.payroll_id = p_payroll_id
495 and c_date between
496 ptp.start_date and ptp.end_date;
497
498 /* Bug 4451088 - The following cursors have been introduced */
499
500 l_date_earned date;
501 l_effective_date date;
502 l_date_earn_yr_start date;
503
504
505 CURSOR csr_get_pay_dates
506 IS
507 select ppa.date_earned,
508 ppa.effective_date
509 from pay_payroll_actions ppa,
510 pay_assignment_actions paa
511 where ppa.payroll_action_id = paa.payroll_action_id
512 and paa.assignment_action_id = p_assignment_action_id;
513
514
515 cursor get_period_num(c_start_date date,
516 c_end_date date)
517 is
518 select count(*)
519 from per_time_periods ptp
520 where payroll_id = p_payroll_id
521 and ptp.end_date
522 between c_start_date and c_end_date;
523
524 begin
525 g_debug := hr_utility.debug_enabled;
526
527 if g_debug then
528 l_procedure :='pay_au_paye_ff.periods_since_hire_date';
529 hr_utility.set_location('Entering '||l_procedure,10);
530 hr_utility.set_location('IN p_payroll_id '||p_payroll_id,20);
531 hr_utility.set_location('IN p_assignment_id '||p_assignment_id,25);
532 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,30);
533 hr_utility.set_location('IN p_assignment_action_id'||p_assignment_action_id,35);
534 hr_utility.set_location('IN p_period_num '||p_period_num,40);
535 hr_utility.set_location('IN p_period_start '||to_char(p_period_start,'dd/mm/yyyy'),45);
536 hr_utility.set_location('IN p_emp_hire_date '||to_char(p_emp_hire_date,'dd/mm/yyyy'),50);
537 hr_utility.set_location('IN p_use_tax_flag '||p_use_tax_flag,55);
538
539 end if;
540
541 l_check_date := p_emp_hire_date;
542 /* Bug# 2450059 Always return 1 if payroll period is 1 */
543
544 /* Bug 4451088 - Introduced the following logic to get number of period ,
545 1. IF Date Earrned and Eff Date in Same Fin Year,
546 Start Date : Financial Year Start Date (Effective Date)
547 End Date : End Date ( Date Earned)
548 2. IF Date Earrned and Eff Date in Different Fin Year,
549 Start Date : Financial Year Start Date (Effective Date)
550 End Date : End Date ( Effective Date)
551
552 Count No of periods in per_time_periods between Start Date/End Date
553 */
554
555 /* Bug# 5371901 - Changed the above logic to take out "Date_Earned" if Condition
556 and instead use only "Effective Date"
557 Checked with PM , In AU for all Payments we should only look at Effective Date .
558 We can have cases wherein the Date_Earned Falls in the Previous Year -
559 their Effective Date still falls in the current Year (due to Offsets) -
560
561 e.g Pay Period : 17-Jun-2006 to 30-JUN-2006 (Offsets +6)
562 Date_Earned :- 30-JUN-2006
563 Effective Date:- 06-JUL-2006
564
565 This means that the Earnings should be considered for the Current Year.
566 */
567
568
569 open csr_get_pay_dates;
570 fetch csr_get_pay_dates into l_date_earned,l_effective_date;
571 close csr_get_pay_dates;
572
573 l_month_no := to_number(to_char(l_effective_date,'MM'));
574 l_year := to_number(to_char(l_effective_date,'YYYY'));
575
576 if l_month_no > 6 then
577 l_year_start := to_date('01-07-'||to_char(l_year),'DD-MM-YYYY');
578 else
579 l_year_start := to_date('01-07-'||to_char(l_year - 1),'DD-MM-YYYY');
580 end if;
581
582 l_month_no := to_number(to_char(l_date_earned,'MM'));
583 l_year := to_number(to_char(l_date_earned,'YYYY'));
584
585 if l_month_no > 6 then
586 l_date_earn_yr_start := to_date('01-07-'||to_char(l_year),'DD-MM-YYYY');
587 else
588 l_date_earn_yr_start := to_date('01-07-'||to_char(l_year - 1),'DD-MM-YYYY');
589 end if;
590
591
592 /* Bug# 5371901 */
593 /* if (l_year_start = l_date_earn_yr_start) then */
594 /* bug 8682739
595 when Date Paid in Quickpay is entered within the period for postive offset payroll,
596 number of periods is returned as one less than the expected.
597 Now it is changed to return period end date based on Date Earned instead of Date Paid */
598 open c_get_period_end_date(l_date_earned);
599 fetch c_get_period_end_date into l_period_end;
600 close c_get_period_end_date;
601 -- else
602 /* bug 8682739
603 open c_get_period_end_date(l_effective_date);
604 fetch c_get_period_end_date into l_period_end;
605 close c_get_period_end_date; */
606 -- end if;
607
608 /* Bug:2900253 Get the Legal Employer start date. If the employee
609 changes Legal Employer then, Period number should be counted
610 starting from Legal Employer start date instead of Hire Date */
611
612 open get_legal_emp_start_date(p_assignment_id);
613 fetch get_legal_emp_start_date into l_leg_emp_date;
614 close get_legal_emp_start_date;
615
616 if l_leg_emp_date > p_emp_hire_date then
617 l_check_date := l_leg_emp_date;
618 else
619 l_check_date := p_emp_hire_date;
620 end if;
621
622 /* End of Bug:2900253 */
623
624
625 IF p_use_tax_flag = 'N' THEN
626
627 open get_period_num(greatest(l_check_date,l_year_start),
628 l_period_end);
629 fetch get_period_num into l_period_num;
630 close get_period_num;
631
632 ELSE
633
634 open c_get_le_period_num; /*Bug 2610141 - It will give us the correct pay periods for a particular le
635 legal employer change has taken place in the year.*/
636 fetch c_get_le_period_num into l_period_num;
637 close c_get_le_period_num;
638
639 end if;
640
641 if g_debug then
642 hr_utility.trace('Value fetched l_period_num =='||l_period_num);
643 end if;
644
645 IF nvl(l_period_num,0) = 0 and l_period_end < l_year_start THEN /*bug 13885520*/
646 l_period_num := 1;
647 END IF;
648
649 if g_debug then
650 hr_utility.trace('Value to be returned l_period_num =='||l_period_num);
651 hr_utility.set_location('Exiting '||l_procedure,70);
652 end if;
653
654 return l_period_num;
655
656 exception
657 when others then
658 null;
659
660 end periods_since_hire_date;
661
662
663 /* Bug 4456720 - Added a new function to calculate the earnings_total and
664 per tax spread deductions for the previous year when total average earnings are negative */
665 /* Bug#4467198 - Modified the function to take care of legal employer changes. Introduced following
666 parameters in the function p_use_tax_flag, p_payroll_id, p_assignment_action_id*/
667 FUNCTION calculate_asg_prev_value
668 ( p_assignment_id in per_all_assignments_f.assignment_id%TYPE,
669 p_business_group_id in hr_all_organization_units.organization_id%TYPE,
670 p_date_earned in date,
671 p_tax_unit_id in hr_all_organization_units.organization_id%TYPE,
672 p_assignment_action_id IN number,
673 p_payroll_id IN NUMBER,
674 p_period_start_date in date,
675 p_case out NOCOPY varchar2,
676 p_earnings_standard out NOCOPY number,
677 p_pre_tax_spread out NOCOPY number,
678 p_pre_tax_fixed out NOCOPY number, /*bug4363057*/
679 p_pre_tax_prog out NOCOPY number, /*bug4363057*/
680 p_paid_periods out NOCOPY number,
681 p_use_tax_flag IN VARCHAR2 --2610141
682 )
683 return NUMBER is
684 -----------------------------------------------------------------------
685 -- Variables
686 -----------------------------------------------------------------------
687 g_debug boolean;
688 l_procedure varchar2(80);
689
690 -- This year Financial Start and End Dates
691 --
692 l_fin_start_date date;
693 l_fin_end_date date;
694
695 -- Last Year Financial Start and End Dates
696 --
697 l_prev_yr_fin_start_date date ;
698 l_prev_yr_fin_end_date date ;
699 l_eff_date DATE; /* Bug#4467198*/
700
701
702 -- Variable to store the maximum previous year assignment action id and its corresponding
703 -- tax_unit_id (legal Employer).
704 l_asg_act_id pay_assignment_actions.assignment_action_id%TYPE;
705 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
706
707
708 -- Total Earnings variable
709 --
710 l_total_earnings number;
711
712 -- Loop Counter variable
713 --
714 i number;
715
716
717 -----------------------------------------------------------------------
718 -- Cursor : c_get_prev_year_max_asg_act_id
719 -- Description : To get the Previous Year Maximum Assignment Action ID
720 -- for a given Assignment_id in a Financial Year.
721 -- If there exists any LE changes, then it gets the max
722 -- Assignment Action ID .
723 -----------------------------------------------------------------------
724 CURSOR c_get_prev_year_max_asg_act_id
725 ( c_assignment_id in per_all_assignments_f.assignment_id%TYPE,
726 c_business_group_id in hr_all_organization_units.organization_id%TYPE,
727 c_fin_start_date in date,
728 c_fin_end_date in date)
729 IS
730 SELECT paa.assignment_action_id, paa.tax_unit_id, ppa.payroll_id
731 FROM pay_assignment_actions paa
732 ,pay_payroll_actions ppa
733 WHERE paa.assignment_id = c_assignment_id
734 and ppa.payroll_action_id = paa.payroll_action_id
735 and ppa.business_group_id = c_business_group_id
736 AND paa.action_sequence in
737 (
738 SELECT MAX(paa.action_sequence)
739 FROM pay_assignment_actions paa,
740 pay_payroll_actions ppa,
741 per_all_assignments_f paaf
742 WHERE ppa.business_group_id = c_business_group_id
743 AND paaf.assignment_id = c_assignment_id
744 AND paa.assignment_id = paaf.assignment_id
745 AND paa.action_status='C'
746 AND ppa.payroll_action_id = paa.payroll_action_id
747 AND ppa.action_type in ('Q','R','B','I','V') --2610141
748 AND ppa.effective_date between c_fin_start_date AND c_fin_end_date /*4521653 replaced the date_earned with effective date*/
749 AND paa.tax_unit_id = p_tax_unit_id --2610141
750 )
751 ORDER BY date_earned desc;
752
753
754 CURSOR c_get_pre_le_max_asg_act_id
755 ( c_assignment_id in per_all_assignments_f.assignment_id%TYPE,
756 c_business_group_id in hr_all_organization_units.organization_id%TYPE,
757 c_fin_start_date in date,
758 c_fin_end_date in date)
759 IS
760 SELECT paa.assignment_action_id, paa.tax_unit_id, ppa.payroll_id, ppa.effective_date
761 FROM pay_assignment_actions paa
762 ,pay_payroll_actions ppa
763 WHERE paa.assignment_id = c_assignment_id
764 and ppa.payroll_action_id = paa.payroll_action_id
765 and ppa.business_group_id = c_business_group_id
766 AND paa.action_sequence in
767 (
768 SELECT MAX(paa.action_sequence)
769 FROM pay_assignment_actions paa,
770 pay_payroll_actions ppa,
771 per_all_assignments_f paaf
772 WHERE ppa.business_group_id = c_business_group_id
773 AND paaf.assignment_id = c_assignment_id
774 AND paa.action_status='C'
775 AND paa.assignment_id = paaf.assignment_id
776 AND ppa.payroll_action_id = paa.payroll_action_id
777 AND ppa.action_type in ('Q','R','B','I','V')
778 AND ppa.effective_date between c_fin_start_date AND c_fin_end_date /*4521653 replaced the date_earned with effective date*/
779 )
780 ORDER BY date_earned desc;
781
782
783 ---
784 -----------------------------------------------------------------------
785 -- Cursor : c_get_paid_period_no_prev_year
786 -- Description : To get the Previous Year number of periods paid to the
787 -- given Assignment_id in previous Financial Year
788 /* Bug#4467198 - Modified the name of cursor to c_get_paid_periods
789 Logic of the cursor has been changed to pick correct paid
790 periods on the basis of the current payroll id*/
791 -----------------------------------------------------------------------
792
793
794 /*Bug 4474896 - Cursor c_get_paid_periods changed to c_get_periods and logic for the cursor modified
795 to count number of pay periods between greatest of (employee's hire date, financial year start date,
796 Legal Employer start date) and current period end date*/
797
798 cursor c_get_periods
799 (c_tax_unit_id in hr_all_organization_units.organization_id%TYPE,
800 c_payroll_id in pay_payrolls_f.payroll_id%TYPE,
801 c_start_date in date,
802 c_end_date in date)
803 is
804 select count(DISTINCT ptp.time_period_id)
805 from per_time_periods ptp
806 where exists (select 'EXISTS' from
807 per_assignments_f paf,
808 hr_soft_coding_keyflex hsck
809 where paf.assignment_id = p_assignment_id
810 and paf.SOFT_CODING_KEYFLEX_ID = hsck.soft_coding_keyflex_id
811 and hsck.segment1 = c_tax_unit_id
812 AND paf.effective_start_date <= c_end_date
813 AND paf.effective_end_date >= c_start_date
814 AND paf.effective_start_date <= ptp.end_date
815 AND paf.effective_end_date >= ptp.start_date)
816 AND ptp.payroll_id = c_payroll_id
817 AND ptp.start_date <= c_end_date
818 AND ptp.end_date >= c_start_date;
819
820 /* bug 12753997 - New c_get_periods_1n3 and c_get_periods_2 cursors are added to be based on regular payment date */
821
822 cursor c_get_periods_1n3
823 (c_tax_unit_id in hr_all_organization_units.organization_id%TYPE,
824 c_payroll_id in pay_payrolls_f.payroll_id%TYPE,
825 c_start_date in date,
826 c_end_date in date)
827 is
828 select count(DISTINCT ptp.time_period_id)
829 from per_time_periods ptp
830 where exists (select 'EXISTS' from
831 per_assignments_f paf,
832 hr_soft_coding_keyflex hsck
833 where paf.assignment_id = p_assignment_id
834 and paf.SOFT_CODING_KEYFLEX_ID = hsck.soft_coding_keyflex_id
835 and hsck.segment1 = c_tax_unit_id
836 AND paf.effective_start_date <= c_end_date
837 AND paf.effective_end_date >= c_start_date
838 AND paf.effective_start_date <= ptp.end_date
839 AND paf.effective_end_date >= ptp.start_date)
840 AND ptp.payroll_id = c_payroll_id
841 AND ptp.regular_payment_date BETWEEN c_start_date AND c_end_date; -- bug12753997
842
843 cursor c_get_periods_2
844 (c_tax_unit_id in hr_all_organization_units.organization_id%TYPE,
845 c_payroll_id in pay_payrolls_f.payroll_id%TYPE,
846 c_start_date in date,
847 c_end_date in date)
848 is
849 select count(DISTINCT ptp.time_period_id)
850 from per_time_periods ptp
851 where exists (select 'EXISTS' from
852 per_assignments_f paf,
853 hr_soft_coding_keyflex hsck
854 where paf.assignment_id = p_assignment_id
855 and paf.SOFT_CODING_KEYFLEX_ID = hsck.soft_coding_keyflex_id
856 and hsck.segment1 = c_tax_unit_id
857 AND paf.effective_start_date <= c_end_date
858 AND paf.effective_end_date >= c_start_date
859 AND paf.effective_start_date <= ptp.end_date
860 AND paf.effective_end_date >= ptp.start_date)
861 AND ptp.payroll_id = c_payroll_id
862 AND ptp.end_date <= c_end_date
863 AND ptp.regular_payment_date >= c_start_date;
864
865 /* Bug#4467198 - Cursor below gives the payroll effective date for the current payroll run*/
866
867 CURSOR c_get_payroll_effective_date
868 IS
869 SELECT ppa.effective_date
870 FROM pay_payroll_actions ppa,
871 pay_assignment_actions paa
872 WHERE paa.assignment_action_id = p_assignment_action_id
873 AND ppa.payroll_action_id = paa.payroll_action_id;
874
875 /* Bug#4467198 - Use the below cursor to check if this is the first for the assignment in this year*/
876
877 CURSOR c_check_payroll_run (c_assignment_id in per_all_assignments_f.assignment_id%TYPE,
878 c_business_group_id in hr_all_organization_units.organization_id%TYPE,
879 c_start_date in date,
880 c_end_date in date)
881 IS
882 SELECT count(paa.assignment_action_id)
883 FROM pay_assignment_actions paa,
884 pay_payroll_actions ppa,
885 per_assignments_f paf
886 WHERE ppa.effective_date BETWEEN c_start_date AND c_end_date
887 AND ppa.business_group_id = c_business_group_id
888 AND ppa.payroll_action_id = paa.payroll_action_id
889 AND paa.assignment_id = c_assignment_id
890 AND paa.assignment_id = paf.assignment_id
891 AND ppa.effective_date between paf.effective_start_date and paf.effective_end_date
892 AND paa.action_status = 'C'
893 AND paa.source_action_id IS NULL /*Bug 4418107 - This join added to only pick master assignment action id*/
894 AND ppa.action_type IN ('Q','R');
895
896
897
898 c_ytd_input_table c_get_ytd_def_bal_ids%rowtype;
899 l_use_le_balances varchar2(50);
900 l_db_item_suffix pay_balance_dimensions.database_item_suffix%type;
901 l_payroll_id number;
902 l_pay_eff_date DATE;
903 l_flag number;
904 l_counter number;
905 l_leave_loading number := 0; /*bug8725341*/
906
907 BEGIN
908
909
910 g_ytd_def_bals_populated := FALSE;
911 l_flag := -1;
912 p_earnings_standard := 0;
913 p_pre_tax_spread := 0;
914 p_pre_tax_fixed := 0; /*bug4363057*/
915 p_pre_tax_prog := 0;
916 i := 1;
917 p_case :='USE_PREV_EARNINGS';
918 g_debug := hr_utility.debug_enabled;
919
920 OPEN c_get_payroll_effective_date; /* Bug#4467198 */
921 FETCH c_get_payroll_effective_date INTO l_eff_date; /* Bug#4467198 */
922 CLOSE c_get_payroll_effective_date; /* Bug#4467198 */
923
924 IF g_debug THEN
925 l_procedure := 'pay_au_paye_ff.calculate_asg_prev_value';
926 hr_utility.set_location('Entering '||l_procedure, 10);
927 hr_utility.set_location('IN p_assignment_id : ' ||p_assignment_id,20);
928 hr_utility.set_location('IN p_business_group_id ' ||p_business_group_id,25);
929 hr_utility.set_location('IN p_date_earned ' ||p_date_earned,30);
930 hr_utility.set_location('IN p_tax_unit_id ' ||p_tax_unit_id,35);
931 hr_utility.set_location('IN p_period_start_date ' ||p_period_start_date,40);
932 hr_utility.set_location('IN p_case ' ||p_case,45);
933 hr_utility.set_location('IN p_date_earned ' ||to_char(p_date_earned,'dd/mm/yyyy'),50);
934 hr_utility.set_location('IN p_use_tax_flag ' ||p_use_tax_flag,50);
935 END IF;
936
937 /*Bug#4467198 Find the Financial Year Start and End Dates on the basis of effective date of the current payroll run.*/
938
939 IF MONTHS_BETWEEN(l_eff_date,TRUNC(l_eff_date,'Y')) < 6 THEN
940 l_fin_start_date := to_date('01-07-'||to_char(add_months(trunc(l_eff_date,'Y'),-9),'YYYY'),'DD-MM-YYYY');
941 l_fin_end_date := to_date('30-06-'||to_char(add_months(trunc(l_eff_date,'Y'),+3),'YYYY'),'DD-MM-YYYY');
942 -- For Previous Fin Year
943 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');
944 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');
945 ELSE
946 l_fin_start_date := to_date('01-JUL-'||to_char(l_eff_date,'YYYY'),'DD-MM-YYYY');
947 l_fin_end_date := to_date('30-JUN-'||to_char(add_months(l_eff_date,12),'YYYY'),'DD-MM-YYYY');
948 -- For Previous Fin Year
949 l_prev_yr_fin_start_date := to_date('01-07-'||to_char(add_months(l_eff_date,-12),'YYYY'),'DD-MM-YYYY');
950 l_prev_yr_fin_end_date := to_date('30-06-'||to_char(trunc(l_eff_date,'Y'),'YYYY'),'DD-MM-YYYY');
951
952 END IF;
953
954 IF g_debug THEN
955 hr_utility.set_location('l_fin_start_date: '|| l_fin_start_date, 55);
956 hr_utility.set_location('l_fin_end_date: '|| l_fin_end_date, 60);
957
958 hr_utility.set_location('l_prev_yr_fin_start_date: '|| l_prev_yr_fin_start_date, 65);
959 hr_utility.set_location('l_prev_yr_fin_end_date: '|| l_prev_yr_fin_end_date, 70);
960 END IF;
961
962 /* Bug#4467198 - Use the below cursor to check if this is the first for the assignment in this year*/
963 OPEN c_check_payroll_run(p_assignment_id,
964 p_business_group_id,
965 l_fin_start_date,
966 l_eff_date);
967 FETCH c_check_payroll_run INTO l_counter;
968 CLOSE c_check_payroll_run;
969
970
971
972
973 /* Bug 2610141 - Get the Maximum assignment action id for the Previous Financial Year for the current
974 Legal Employer or the maximum assignment action id for previous legal employer for the
975 current year*/
976
977 IF l_counter = 0 OR p_use_tax_flag = 'N' THEN
978 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);
979 FETCH c_get_prev_year_max_asg_act_id into l_asg_act_id, l_tax_unit_id, l_payroll_id;
980 CLOSE c_get_prev_year_max_asg_act_id;
981 IF nvl(l_asg_act_id,-99999) <> -99999 THEN /*Bug 4418107*/
982 l_flag := 1; /* Flag is set to 1 when we take YTD earnings for previous year for the current legal employer*/
983 END IF;
984 ELSE
985 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 */
986 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;
987 CLOSE c_get_pre_le_max_asg_act_id;
988 IF nvl(l_asg_act_id,-99999) <> -99999 THEN /*Bug 4418107*/
989 l_flag := 2; /* Flag is set to 2 when we take YTD earnings for current year for the previous legal employer*/
990 END IF;
991 END IF;
992
993
994 IF l_flag <> 1 AND l_flag <> 2 THEN
995 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);
996 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;
997 CLOSE c_get_pre_le_max_asg_act_id;
998 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*/
999 END IF;
1000
1001
1002 IF g_debug THEN
1003 hr_utility.set_location('l_flag: '|| l_flag, 75);
1004 hr_utility.set_location('l_asg_act_id: '|| l_asg_act_id, 75);
1005 hr_utility.set_location('g_context_table(1).tax_unit_id: '|| l_tax_unit_id, 80);
1006 hr_utility.set_location('l_payroll_id: '||l_payroll_id, 85);
1007 hr_utility.set_location('p_tax_unit_id : '||p_tax_unit_id, 90);
1008 END IF;
1009
1010
1011 IF nvl(l_asg_act_id,-99999) = -99999 THEN
1012 /* There is no payroll actions exist in the previous financial year and also there is no
1013 actions present in the current year. This means the customer go live and this is the
1014 first payroll action
1015 For this case, need to populate message to the user in order to process the Termination
1016 Payments Manually. For this set the p_case to 'POPULATE_MSG'
1017 Average_Earnings will not be calculated.
1018 */
1019 p_case := 'POPULATE_MSG';
1020 IF g_debug THEN
1021 hr_utility.set_location('p_case: '|| p_case, 95);
1022 hr_utility.set_location('Exiting '||l_procedure,105);
1023 END IF;
1024 RETURN 110;
1025
1026 ELSE
1027
1028 /* Bug 2610141 - Get the Total Number of Paid Periods for the Previous Financial Year for the current
1029 Legal Employer or the number of paid periods of the previous legal employer for the
1030 current year*/
1031 /* Bug 12753997 - Get the Total Number based on regular payment date effective from 01-JUL-2011 */
1032 IF to_char(l_eff_date,'dd/mm/yyyy') >= '01/07/2011' THEN
1033 IF l_flag = 1 OR l_flag = 3 THEN
1034 OPEN c_get_periods_1n3
1035 (l_tax_unit_id,
1036 l_payroll_id,
1037 l_prev_yr_fin_start_date,
1038 l_prev_yr_fin_end_date);
1039 FETCH c_get_periods_1n3 INTO p_paid_periods;
1040 CLOSE c_get_periods_1n3;
1041 ELSE
1042 OPEN c_get_periods_2
1043 (l_tax_unit_id,
1044 l_payroll_id,
1045 l_fin_start_date,
1046 p_period_start_date - 1);
1047 FETCH c_get_periods_2 INTO p_paid_periods;
1048 CLOSE c_get_periods_2;
1049 END IF;
1050 ELSE
1051 IF l_flag = 1 OR l_flag = 3 THEN
1052 OPEN c_get_periods
1053 (l_tax_unit_id,
1054 l_payroll_id,
1055 l_prev_yr_fin_start_date,
1056 l_prev_yr_fin_end_date);
1057 FETCH c_get_periods INTO p_paid_periods;
1058 CLOSE c_get_periods;
1059 ELSE
1060 OPEN c_get_periods
1061 (l_tax_unit_id,
1062 l_payroll_id,
1063 l_fin_start_date,
1064 p_period_start_date - 1);
1065 FETCH c_get_periods INTO p_paid_periods;
1066 CLOSE c_get_periods;
1067 END IF;
1068 END IF;
1069
1070 IF g_debug THEN
1071 hr_utility.set_location('p_paid_periods: '|| p_paid_periods, 100);
1072 END IF;
1073
1074
1075 IF NOT g_ytd_def_bals_populated THEN
1076 -- Fetch the defined balance ids for the required balances
1077 --
1078
1079 IF p_use_tax_flag = 'Y' THEN
1080 l_db_item_suffix := '_ASG_LE_YTD';
1081 ELSE
1082 l_db_item_suffix := '_ASG_YTD';
1083 END IF ;
1084
1085 OPEN c_get_ytd_def_bal_ids(l_db_item_suffix);
1086 LOOP
1087 FETCH c_get_ytd_def_bal_ids into c_ytd_input_table;
1088 EXIT WHEN c_get_ytd_def_bal_ids%NOTFOUND;
1089
1090 -- Populate the Defined Balances Input Values Table
1091 g_ytd_input_table(i).defined_balance_id := c_ytd_input_table.defined_balance_id;
1092 g_ytd_input_table(i).balance_value := null;
1093
1094 -- Populate the contexts Table
1095
1096 /*bug 2610141*/
1097 IF p_use_tax_flag = 'Y' THEN
1098 g_ytd_context_table(1).tax_unit_id := l_tax_unit_id;
1099 ELSE
1100 g_ytd_context_table(1).tax_unit_id := null;
1101 END IF;
1102
1103 -- Populate the Global Defined Balances Table
1104 g_ytd_bals(i).defined_balance_id := c_ytd_input_table.defined_balance_id;
1105 g_ytd_bals(i).balance_name := c_ytd_input_table.balance_name;
1106 g_ytd_bals(i).dimension_name := c_ytd_input_table.dimension_name;
1107
1108 i := i+1;
1109 END LOOP;
1110 CLOSE c_get_ytd_def_bal_ids;
1111 g_ytd_def_bals_populated := TRUE;
1112
1113 END IF;
1114
1115 -- Use BBR for retrieving the balance values for the previous financial year.
1116 --
1117 pay_balance_pkg.get_value(P_ASSIGNMENT_ACTION_ID =>l_asg_act_id,
1118 P_DEFINED_BALANCE_LST => g_ytd_input_table,
1119 P_CONTEXT_LST => g_ytd_context_table,
1120 P_OUTPUT_TABLE => g_ytd_result_table);
1121
1122
1123 FOR i in g_ytd_result_table.first .. g_ytd_result_table.last
1124 LOOP
1125 IF g_ytd_result_table(i).defined_balance_id = g_ytd_bals(i).defined_balance_id
1126 and g_ytd_bals(i).balance_name = 'Earnings_Standard'
1127 THEN
1128 p_earnings_standard := nvl(g_ytd_result_table(i).balance_value,0);
1129 IF g_debug THEN
1130 hr_utility.set_location('p_earnings_standard: '||p_earnings_standard, 60);
1131 END IF;
1132 ELSIF g_ytd_result_table(i).defined_balance_id = g_ytd_bals(i).defined_balance_id
1133 and g_ytd_bals(i).balance_name = 'Pre Tax Spread Deductions'
1134 THEN
1135 p_pre_tax_spread := nvl(g_ytd_result_table(i).balance_value,0);
1136 IF g_debug THEN
1137 hr_utility.set_location('p_pre_tax_spread_deductions: '||p_pre_tax_spread, 60);
1138 END IF;
1139
1140 ELSIF g_ytd_result_table(i).defined_balance_id = g_ytd_bals(i).defined_balance_id
1141 and g_ytd_bals(i).balance_name = 'Pre Tax Fixed Deductions' and p_use_tax_flag = 'Y'
1142 /*bug4363057*/
1143 THEN
1144 p_pre_tax_fixed := nvl(g_ytd_result_table(i).balance_value,0);
1145 IF g_debug THEN
1146 hr_utility.set_location('p_pre_tax_fixed_deductions: '||p_pre_tax_fixed, 60);
1147 END IF;
1148 ELSIF g_ytd_result_table(i).defined_balance_id = g_ytd_bals(i).defined_balance_id
1149 and g_ytd_bals(i).balance_name = 'Pre Tax Progressive Deductions' and p_use_tax_flag = 'Y'
1150 /*bug4363057*/
1151 THEN
1152 p_pre_tax_prog := nvl(g_ytd_result_table(i).balance_value,0);
1153 IF g_debug THEN
1154 hr_utility.set_location('p_pre_tax_progressive_deductions: '||p_pre_tax_prog, 60);
1155 END IF;
1156
1157 ELSIF g_ytd_result_table(i).defined_balance_id = g_ytd_bals(i).defined_balance_id
1158 and g_ytd_bals(i).balance_name = 'Earnings_Leave_Loading' and p_use_tax_flag = 'Y'
1159 /*bug8725341*/
1160 THEN
1161 l_leave_loading := nvl(g_ytd_result_table(i).balance_value,0);
1162 IF g_debug THEN
1163 hr_utility.set_location('l_earnings_leave_loading: '||l_leave_loading, 60);
1164 END IF;
1165
1166 END IF;
1167 END LOOP;
1168
1169 IF to_char(l_eff_date,'dd/mm/yyyy') >= '01/07/2009' THEN /*bug8725341*/
1170 p_earnings_standard := p_earnings_standard + l_leave_loading;
1171 END IF;
1172
1173 if g_debug then
1174 hr_utility.set_location('OUT p_paid_periods: '|| p_paid_periods, 100);
1175 hr_utility.set_location('OUT p_earnings_standard: '||p_earnings_standard, 60);
1176 hr_utility.set_location('OUT p_pre_tax_spread_deductions: '||p_pre_tax_spread, 60);
1177 hr_utility.set_location('OUT p_pre_tax_fixed_deductions: '||p_pre_tax_fixed, 60);
1178 hr_utility.set_location('OUT p_pre_tax_progressive_deductions: '||p_pre_tax_prog, 60);
1179 hr_utility.set_location('Exiting '||l_procedure,105);
1180 end if;
1181
1182 return 1000;
1183 END IF;
1184
1185
1186 END calculate_asg_prev_value;
1187
1188
1189
1190
1191
1192 /*
1193 * paid_periods_since_hire_date - returns the number of periods in the
1194 * current tax year since the hire date.
1195 */
1196
1197 function paid_periods_since_hire_date
1198 (
1199 p_payroll_id in number,
1200 p_assignment_id in number,
1201 p_tax_unit_id in number, --2610141
1202 p_assignment_action_id IN number, /*Bug 4438644*/
1203 p_period_num in number,
1204 p_period_start in date,
1205 p_emp_hire_date in date,
1206 p_use_tax_flag IN VARCHAR2 --2610141
1207 )
1208 return number is
1209
1210 l_year_start date;
1211 l_month_no number;
1212 l_year number;
1213 l_period_num number;
1214 l_time_period_id number;
1215 l_start_date date;
1216 l_eff_date DATE;
1217 l_count_period NUMBER;
1218 l_eff_period_num NUMBER;
1219 v_curr_time_period_id NUMBER;
1220 l_procedure varchar2(80);
1221
1222 /*Bug 4438644 - Cursor introduced to return time period of the current payroll period*/
1223 cursor c_get_period_id
1224 is
1225 select time_period_id
1226 from per_time_periods
1227 where payroll_id = p_payroll_id
1228 and start_Date = p_period_start;
1229
1230 /* Bug: 3724089 - Performance Fix in the Cursor below. Added table per_assignments_f and its joins in the inner sub-query */
1231
1232 cursor c_get_processed_periods_num (v_payroll_id number,
1233 v_start_date date,
1234 v_end_date date,
1235 v_assignment_id number,
1236 v_tax_unit_id number --2610141
1237 ) is
1238 select DISTINCT ptp.time_period_id, ptp.period_num /*Bug 4438644*/
1239 from per_time_periods ptp
1240 where exists (select 'EXISTS' from
1241 per_assignments_f paf,
1242 pay_payroll_actions ppa,
1243 pay_assignment_actions paa
1244 where ppa.payroll_id = v_payroll_id
1245 and ppa.action_type in ('R','Q')
1246 and paa.action_status = 'C'
1247 and ppa.payroll_action_id = paa.payroll_action_id
1248 and paf.assignment_id = v_assignment_id
1249 and paa.assignment_id = paf.assignment_id
1250 and paa.tax_unit_id = decode(p_use_tax_flag,'N',paa.tax_unit_id,v_tax_unit_id) --2610141
1251 AND ppa.effective_date BETWEEN v_start_date and v_end_date /*Bug 4438644*/
1252 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date /*Bug 4438644*/
1253 and ppa.date_earned between paf.effective_start_date and paf.effective_end_date)
1254 and ptp.payroll_id = v_payroll_id;
1255 -- and ptp.regular_payment_date between v_start_date and v_end_date; /*Bug 4438644*/
1256
1257
1258 /* Bug 4438644 - Two new cursors introduced, c_get_payroll_effective_date gives the effective date of the payroll
1259 bein run.*/
1260
1261 CURSOR c_get_payroll_effective_date
1262 IS
1263 SELECT ppa.effective_date
1264 FROM pay_payroll_actions ppa,
1265 pay_assignment_actions paa
1266 WHERE paa.assignment_action_id = p_assignment_action_id
1267 AND ppa.payroll_action_id = paa.payroll_action_id;
1268
1269
1270
1271 begin
1272 g_debug := hr_utility.debug_enabled;
1273
1274 if g_debug then
1275 l_procedure :='pay_au_paye_ff.paid_periods_since_hire_date';
1276 hr_utility.set_location('Entering '||l_procedure,10);
1277 hr_utility.set_location('IN p_payroll_id '||p_payroll_id,20);
1278 hr_utility.set_location('IN p_assignment_id '||p_assignment_id,25);
1279 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,30);
1280 hr_utility.set_location('IN p_assignment_action_id '||p_assignment_action_id,35);
1281 hr_utility.set_location('IN p_period_num '||p_period_num,40);
1282 hr_utility.set_location('IN p_period_start '||to_char(p_period_start,'dd/mm/yyyy'),45);
1283 hr_utility.set_location('IN p_emp_hire_date '||to_char(p_emp_hire_date,'dd/mm/yyyy'),50);
1284 hr_utility.set_location('IN p_use_tax_flag '||p_use_tax_flag,55);
1285
1286 end if;
1287
1288 OPEN c_get_payroll_effective_date; /*Bug 4438644*/
1289 FETCH c_get_payroll_effective_date INTO l_eff_date; /*Bug 4438644*/
1290 CLOSE c_get_payroll_effective_date; /*Bug 4438644*/
1291
1292 /*Bug 4438644 - Code given below gets the current time period id based on date earned*/
1293 OPEN c_get_period_id;
1294 FETCH c_get_period_id INTO v_curr_time_period_id;
1295 CLOSE c_get_period_id;
1296
1297 /*Bug 4438644 - Financial year now gets calculated on the basis of the payroll effective date*/
1298 l_month_no := to_number(to_char(l_eff_date,'MM'));
1299 l_year := to_number(to_char(l_eff_date,'YYYY'));
1300
1301 /* Bug# 2166742 Added the following if clause */
1302 /* Bug 4438644 - This piece of code has been removed*/
1303 /* if p_period_num = 1 then
1304 return 1;
1305 end if; */
1306
1307 if l_month_no > 6 then
1308 l_year_start := to_date('01-07-'||to_char(l_year),'DD-MM-YYYY');
1309 else
1310 l_year_start := to_date('01-07-'||to_char(l_year - 1),'DD-MM-YYYY');
1311 end if;
1312
1313 if p_emp_hire_date <= l_year_start then
1314 l_start_date := l_year_start;
1315 else
1316 l_start_date := p_emp_hire_date;
1317 end if;
1318
1319
1320 l_count_period := 0;
1321
1322 open c_get_processed_periods_num (p_payroll_id
1323 ,l_start_date
1324 ,l_eff_date /*Bug 4438644 -Payroll effective date as argument*/
1325 ,p_assignment_id
1326 ,p_tax_unit_id); --2610141
1327 LOOP
1328 fetch c_get_processed_periods_num into l_time_period_id,l_period_num;
1329 EXIT WHEN c_get_processed_periods_num%NOTFOUND;
1330 IF l_time_period_id <> v_curr_time_period_id THEN /*Bug 4438644 - This condition put to exclude the increment
1331 for current payroll period.*/
1332 l_count_period := l_count_period + 1;
1333 END IF;
1334 END LOOP;
1335
1336 close c_get_processed_periods_num;
1337
1338 l_count_period := l_count_period + 1; /*Bug 4438644 - Increment done for current payroll period*/
1339
1340 if g_debug then
1341 hr_utility.set_location('Return l_count_period '||l_count_period,60);
1342 hr_utility.set_location('Exiting '||l_procedure,70);
1343 end if ;
1344
1345 RETURN l_count_period;
1346
1347 exception
1348 when others then
1349 null;
1350
1351 end paid_periods_since_hire_date;
1352
1353
1354 /*
1355 * convert_to_period - converts weekly equivalents
1356 * back to the period amounts using new ATO rules
1357 */
1358
1359 function convert_to_period_amt
1360 (
1361 p_ann_freq in number,
1362 p_amt_week in number,
1363 p_tax_scale in number
1364 )
1365 return number is
1366
1367 l_amt_period number;
1368
1369 begin
1370 g_debug := hr_utility.debug_enabled;
1371 If(p_tax_scale <> 4) then
1372 if p_ann_freq = 52 then
1373 l_amt_period := p_amt_week;
1374 elsif p_ann_freq = 26 then
1375 l_amt_period := (p_amt_week * 2);
1376 elsif p_ann_freq = 24 then
1377 l_amt_period := round_amt (p_amt_week * 13 / 6,p_tax_scale);
1378 elsif p_ann_freq = 12 then
1379 l_amt_period := round_amt (p_amt_week * 13 / 3,p_tax_scale);
1380 elsif p_ann_freq = 4 then /*Bug : 2888114*/
1381 l_amt_period := round_amt (p_amt_week * 13 ,p_tax_scale);
1382 end if;
1383 else
1384 if p_ann_freq = 52 then
1385 l_amt_period := p_amt_week;
1386 elsif p_ann_freq = 26 then
1387 l_amt_period := (p_amt_week * 2);
1388 elsif p_ann_freq = 24 then
1389 l_amt_period := trunc (p_amt_week * 13 / 6);
1390 elsif p_ann_freq = 12 then
1391 l_amt_period := trunc (p_amt_week * 13 / 3);
1392 elsif p_ann_freq = 4 then /*Bug : 2888114*/
1393 l_amt_period := trunc (p_amt_week * 13) ;
1394 end if;
1395 end if;
1396 return (l_amt_period);
1397
1398 exception
1399 when others then
1400 null;
1401
1402 end convert_to_period_amt;
1403
1404
1405 function round_amt
1406 (
1407 p_actual_amt in number,
1408 p_tax_scale in number
1409 )
1410 return number is
1411
1412 begin
1413 g_debug := hr_utility.debug_enabled;
1414 If(p_tax_scale <> 4) then
1415 return (round(p_actual_amt));
1416 else
1417 return (trunc(p_actual_amt));
1418 end if;
1419
1420 exception
1421 when others then
1422 null;
1423
1424 end round_amt;
1425
1426
1427 function check_if_retro
1428 (
1429 p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1430 p_date_earned in pay_payroll_actions.date_earned%TYPE
1431
1432 )return varchar2 is
1433
1434
1435 l_creator_type pay_element_entries_f.creator_type%TYPE;
1436 IS_retro_payment varchar2(10);
1437 l_procedure varchar2(80);
1438 begin
1439 g_debug := hr_utility.debug_enabled;
1440
1441 if g_debug then
1442 l_procedure :='pay_au_paye_ff.check_if_retro';
1443 hr_utility.set_location('Entering '||l_procedure,10);
1444 hr_utility.set_location('IN p_element_entry_id '||p_element_entry_id,20);
1445 hr_utility.set_location('IN p_date_earned '||to_char(p_date_earned,'dd/mm/yyyy'),30);
1446 end if;
1447
1448 OPEN c_get_creator_type(p_element_entry_id,p_date_earned);
1449 FETCH c_get_creator_type INTO l_creator_type ;
1450 CLOSE c_get_creator_type;
1451 if l_creator_type = 'RR' or l_creator_type = 'EE' then
1452 IS_retro_payment:='Y';
1453 else
1454 IS_retro_payment:='N';
1455 end if;
1456
1457 if g_debug then
1458 hr_utility.set_location('Return IS_retro_payment '||IS_retro_payment,40);
1459 hr_utility.set_location('Exiting '||l_procedure,50);
1460 end if;
1461
1462 return IS_retro_payment;
1463
1464 EXCEPTION
1465 when others then
1466 null;
1467
1468
1469 end check_if_retro;
1470
1471
1472
1473 function get_retro_period
1474 (
1475 p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1476 p_date_earned in pay_payroll_actions.date_earned%TYPE,
1477 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,/*Bug 4418107*/
1478 p_retro_start_date out NOCOPY date,
1479 p_retro_end_date out NOCOPY date
1480 )return number is
1481
1482
1483 cursor get_retro_period_rr
1484 ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1485 c_date_earned in pay_payroll_actions.date_earned%TYPE
1486 ) is
1487 SELECT ptp.start_date,ptp.end_date
1488 FROM per_time_periods ptp,
1489 pay_payroll_actions ppa,
1490 pay_assignment_actions paa,
1491 pay_run_results prr,
1492 pay_element_entries_f pee
1493 WHERE pee.element_entry_id=c_element_entry_id
1494 and prr.run_result_id = pee.source_id
1495 and paa.assignment_action_id=prr.assignment_action_id
1496 AND paa.tax_unit_id = p_tax_unit_id /*Bug 4418107*/
1497 and ppa.payroll_action_id=paa.payroll_action_id
1498 and ptp.payroll_id=ppa.payroll_id
1499 and pee.creator_type='RR'
1500 and ppa.date_earned between ptp.start_date and ptp.end_date
1501 and c_date_earned between pee.effective_start_date and pee.effective_end_date;
1502
1503
1504
1505 cursor get_retro_period_ee
1506 ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1507 c_date_earned in pay_payroll_actions.date_earned%TYPE
1508 ) is
1509 SELECT ptp.start_date,ptp.end_date
1510 FROM per_time_periods ptp,
1511 pay_payroll_actions ppa,
1512 pay_assignment_actions paa,
1513 pay_element_entries_f pee
1514 WHERE pee.element_entry_id=c_element_entry_id
1515 and paa.assignment_action_id=pee.source_asg_action_id
1516 AND paa.tax_unit_id = p_tax_unit_id /*Bug 4418107*/
1517 and ppa.payroll_action_id=paa.payroll_action_id
1518 and ptp.payroll_id=ppa.payroll_id
1519 and pee.creator_type='EE'
1520 and ppa.date_earned between ptp.start_date and ptp.end_date
1521 and c_date_earned between pee.effective_start_date and pee.effective_end_date;
1522
1523 l_creator_type pay_element_entries_f.creator_type%TYPE;
1524 l_period_obtained_flag number;
1525 l_procedure varchar2(80);
1526
1527
1528 begin
1529 g_debug := hr_utility.debug_enabled;
1530
1531 IF g_debug THEN
1532 l_procedure :='pay_au_paye_ff.get_retro_period';
1533 hr_utility.set_location('Entering '||l_procedure,10);
1534 hr_utility.set_location('IN p_element_entry_id '||p_element_entry_id,20);
1535 hr_utility.set_location('IN p_date_earned '||to_char(p_date_earned,'dd/mm/yyyy'),30);
1536 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id ,40);
1537
1538 END IF;
1539
1540
1541 l_period_obtained_flag:=1;
1542 IF g_debug THEN
1543 hr_utility.set_location('l_period_obtained_flag '||l_period_obtained_flag,45);
1544 END IF;
1545
1546 OPEN c_get_creator_type(p_element_entry_id,p_date_earned);
1547 FETCH c_get_creator_type INTO l_creator_type ;
1548 CLOSE c_get_creator_type;
1549
1550
1551 if l_creator_type = 'RR' then
1552 OPEN get_retro_period_rr(p_element_entry_id,p_date_earned);
1553 FETCH get_retro_period_rr into p_retro_start_date,p_retro_end_date;
1554 CLOSE get_retro_period_rr;
1555 l_period_obtained_flag:=1;
1556 end if;
1557
1558 if l_creator_type = 'EE' then
1559 OPEN get_retro_period_ee(p_element_entry_id,p_date_earned);
1560 FETCH get_retro_period_ee into p_retro_start_date,p_retro_end_date;
1561 CLOSE get_retro_period_ee;
1562 l_period_obtained_flag:=1;
1563 end if;
1564
1565 IF g_debug THEN
1566
1567 hr_utility.set_location('OUT p_retro_start_date '||to_char(p_retro_start_date,'dd/mm/yyyy'),50);
1568 hr_utility.set_location('OUT p_retro_end_date '||to_char(p_retro_end_date,'dd/mm/yyyy'),55);
1569 hr_utility.set_location('OUT l_period_obtained_flag '||l_period_obtained_flag,60);
1570 hr_utility.set_location('Exiting '||l_procedure,70);
1571
1572 END IF;
1573
1574 return l_period_obtained_flag;
1575
1576
1577
1578 end get_retro_period;
1579
1580 /* Bug 5586445
1581 Function : get_enhanced_retro_period
1582 Description : This function is to be used for Enhanced Retropay implementation.
1583 Function returns details about Retro Element entry and the retropay time
1584 span for which the entry is created.
1585 Inputs : p_element_entry_id - Element Entry ID
1586 p_date_earned - Date Earned of the Run
1587 p_tax_unit_id - Tax Unit ID of Assignment
1588 Outputs : p_retro_start_date - Period Start Date of Original period
1589 p_retro_end_date - Period End Date of Original period
1590 p_orig_effective_date - Effective Date of Original Run
1591 p_retro_effective_date - Effective Date of Retropay Run that created element entry
1592 p_time_span - Character String indicating the retro time span. Values are,
1593 'LT12 Curr','LT12 Prev','GT12'
1594 */
1595
1596 FUNCTION get_enhanced_retro_period
1597 (
1598 p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE,
1599 p_date_earned IN pay_payroll_actions.date_earned%TYPE,
1600 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1601 p_retro_start_date OUT NOCOPY date,
1602 p_retro_end_date OUT NOCOPY date,
1603 p_orig_effective_date OUT NOCOPY date,
1604 p_retro_effective_date OUT NOCOPY date,
1605 p_time_span OUT NOCOPY varchar2
1606 )return number
1607 IS
1608
1609 /* Bug 5586445 - Cursor get_retropay_run_details
1610 Get Effective Date of the Enhanced Retropay process
1611 */
1612 CURSOR get_retropay_run_details
1613 (c_element_entry_id pay_element_entries_f.element_entry_id%TYPE)
1614 IS
1615 SELECT pee.creator_type,
1616 ppa.effective_date
1617 FROM pay_payroll_actions ppa,
1618 pay_assignment_actions paa,
1619 pay_element_entries_f pee
1620 WHERE ppa.payroll_action_id = paa.payroll_action_id
1621 AND paa.assignment_action_id = pee.creator_id
1622 AND pee.element_entry_id = c_element_entry_id
1623 AND ppa.action_type ='L';
1624
1625 CURSOR get_retro_period_rr
1626 ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1627 c_date_earned in pay_payroll_actions.date_earned%TYPE
1628 )
1629 IS
1630 SELECT ptp.start_date
1631 ,ptp.end_date
1632 ,ppa.effective_date
1633 FROM per_time_periods ptp,
1634 pay_payroll_actions ppa,
1635 pay_assignment_actions paa,
1636 pay_run_results prr,
1637 pay_element_entries_f pee
1638 WHERE pee.element_entry_id=c_element_entry_id
1639 AND prr.run_result_id = pee.source_id
1640 AND paa.assignment_action_id=prr.assignment_action_id
1641 AND paa.tax_unit_id = p_tax_unit_id
1642 AND ppa.payroll_action_id=paa.payroll_action_id
1643 AND ptp.payroll_id=ppa.payroll_id
1644 AND pee.creator_type='RR'
1645 AND ppa.date_earned between ptp.start_date and ptp.end_date
1646 AND c_date_earned between pee.effective_start_date and pee.effective_end_date;
1647
1648
1649 CURSOR get_retro_period_ee
1650 ( c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
1651 c_date_earned in pay_payroll_actions.date_earned%TYPE
1652 )
1653 IS
1654 SELECT ptp.start_date
1655 ,ptp.end_date
1656 ,ppa.effective_date
1657 FROM per_time_periods ptp,
1658 pay_payroll_actions ppa,
1659 pay_assignment_actions paa,
1660 pay_element_entries_f pee
1661 WHERE pee.element_entry_id=c_element_entry_id
1662 AND paa.assignment_action_id=pee.source_asg_action_id
1663 AND paa.tax_unit_id = p_tax_unit_id
1664 AND ppa.payroll_action_id=paa.payroll_action_id
1665 AND ptp.payroll_id=ppa.payroll_id
1666 AND pee.creator_type='EE'
1667 AND ppa.date_earned between ptp.start_date and ptp.end_date
1668 AND c_date_earned between pee.effective_start_date and pee.effective_end_date;
1669
1670 l_period_obtained_flag number;
1671
1672 l_procedure VARCHAR2(80);
1673 l_creator_type pay_element_entries_f.creator_type%TYPE;
1674 l_fin_year_start DATE;
1675 l_month_start DATE;
1676 l_orig_month_start DATE;
1677 l_time_span VARCHAR2(80);
1678
1679
1680 BEGIN
1681
1682 g_debug := hr_utility.debug_enabled;
1683 l_period_obtained_flag := 1;
1684
1685 IF g_debug THEN
1686 l_procedure := 'pay_au_payee_ff.get_enhanced_retro_period';
1687 hr_utility.set_location('Entering '||l_procedure,10);
1688 hr_utility.set_location('IN p_element_entry_id '||p_element_entry_id,20);
1689 hr_utility.set_location('IN p_date_earned '||p_date_earned,30);
1690 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,30);
1691 END IF;
1692
1693 OPEN get_retropay_run_details(p_element_entry_id);
1694 FETCH get_retropay_run_details INTO l_creator_type,p_retro_effective_date;
1695 CLOSE get_retropay_run_details;
1696
1697 l_fin_year_start := hr_au_routes.span_start(p_retro_effective_date,1,'01-07');
1698 l_month_start := hr_au_routes.span_start(p_retro_effective_date,12,'01-01');
1699
1700 IF l_creator_type = 'RR'
1701 THEN
1702 OPEN get_retro_period_rr(p_element_entry_id,p_date_earned);
1703 FETCH get_retro_period_rr INTO p_retro_start_date,p_retro_end_date,p_orig_effective_date;
1704 IF get_retro_period_rr%FOUND
1705 THEN
1706 l_orig_month_start := hr_au_routes.span_start(p_orig_effective_date,12,'01-01');
1707 l_period_obtained_flag:=1;
1708 END IF;
1709 CLOSE get_retro_period_rr;
1710 END IF;
1711
1712 IF l_creator_type = 'EE' then
1713 OPEN get_retro_period_ee(p_element_entry_id,p_date_earned);
1714 FETCH get_retro_period_ee INTO p_retro_start_date,p_retro_end_date,p_orig_effective_date;
1715 IF get_retro_period_ee%FOUND
1716 THEN
1717 l_orig_month_start := hr_au_routes.span_start(p_orig_effective_date,12,'01-01');
1718 l_period_obtained_flag:=1;
1719 END IF;
1720 CLOSE get_retro_period_ee;
1721 END IF;
1722
1723 p_time_span := NULL;
1724 /* Set Time Span */
1725
1726 IF (p_orig_effective_date >= l_fin_year_start)
1727 THEN
1728 l_time_span := 'LT12 Curr';
1729 ELSIF (p_orig_effective_date < l_fin_year_start) AND
1730 (trunc(months_between(l_month_start,l_orig_month_start)) <= 12)
1731 THEN
1732 l_time_span := 'LT12 Prev';
1733 ELSIF (p_orig_effective_date < l_fin_year_start) AND
1734 (trunc(months_between(l_month_start,l_orig_month_start)) > 12)
1735 THEN
1736 l_time_span := 'GT12';
1737 END IF;
1738
1739 p_time_span := l_time_span;
1740
1741 IF g_debug
1742 THEN
1743 hr_utility.set_location('OUT p_retro_start_date '||p_retro_start_date,40);
1744 hr_utility.set_location('OUT p_retro_end_date '||p_retro_end_date,50);
1745 hr_utility.set_location('OUT p_orig_effective_date '||p_orig_effective_date,60);
1746 hr_utility.set_location('OUT p_retro_effective_date '||p_retro_effective_date,70);
1747 hr_utility.set_location('OUT p_time_span '||p_time_span,80);
1748 hr_utility.set_location('Exiting '||l_procedure,90);
1749 END IF;
1750
1751 RETURN l_period_obtained_flag;
1752
1753 END get_enhanced_retro_period;
1754
1755
1756 /* Bug 5846272 - Functions added for Enhanced Retropay in 11i.
1757 Function : check_if_enhanced_retro
1758 Description : This function checks the Legislation Rule for Enhanced Retropay and
1759 returns value indicating if Enhanced Retropay is enabled in system or not.
1760 Inputs : p_business_group_id - Business Group ID
1761 */
1762
1763 FUNCTION check_if_enhanced_retro
1764 (
1765 p_business_group_id IN per_business_groups.business_group_id%TYPE
1766 )RETURN VARCHAR2
1767 IS
1768
1769 CURSOR get_legislation_rule
1770 (c_business_group_id IN per_business_groups.business_group_id%TYPE)
1771 IS
1772 SELECT rule_mode
1773 FROM pay_legislation_rules plr
1774 ,per_business_groups pbg
1775 WHERE plr.legislation_code = pbg.legislation_code
1776 AND pbg.business_group_id = c_business_group_id
1777 AND plr.rule_type = 'ADVANCED_RETRO'
1778 AND pbg.legislation_code = 'AU';
1779
1780 l_return VARCHAR2(10);
1781 l_proc_name VARCHAR2(80);
1782
1783 BEGIN
1784
1785 g_debug := hr_utility.debug_enabled;
1786
1787 IF g_debug THEN
1788 l_proc_name := 'pay_au_paye_ff.check_if_enhanced_retro';
1789 hr_utility.set_location('Entering '||l_proc_name,10);
1790 hr_utility.set_location('IN p_business_group_id => '||p_business_group_id,20);
1791 END IF;
1792
1793 OPEN get_legislation_rule(p_business_group_id);
1794 FETCH get_legislation_rule INTO l_return;
1795 CLOSE get_legislation_rule;
1796
1797 IF g_debug THEN
1798 hr_utility.set_location('OUT Return Value =>'||l_return,30);
1799 hr_utility.set_location('Exiting '||l_proc_name,40);
1800 END IF;
1801
1802 RETURN NVL(l_return,'N');
1803
1804 END check_if_enhanced_retro;
1805
1806
1807 /* Bug 5846272
1808 Function : get_retro_time_span
1809 Description : This function is to be used for Enhanced Retropay implementation.
1810 Function returns details about Retro Element entry and the retropay time
1811 span for which the entry is created.
1812 Inputs : p_element_entry_id - Element Entry ID
1813 p_date_earned - Date Earned of the Run
1814 p_tax_unit_id - Tax Unit ID of Assignment
1815 Outputs : p_retro_start_date - Period Start Date of Original period
1816 p_retro_end_date - Period End Date of Original period
1817 p_orig_effective_date - Effective Date of Original Run
1818 p_retro_effective_date - Effective Date of Retropay Run that created element entry
1819 p_time_span - Character String indicating the retro time span. Values are,
1820 'LT12 Curr','LT12 Prev','GT12'
1821 p_retro_type - String indicating the type of retropay used to create element.
1822 Values are - 'RETRO_ELE' and 'ADVANCED_RETRO'
1823 */
1824
1825
1826 FUNCTION get_retro_time_span
1827 (
1828 p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE,
1829 p_date_earned IN pay_payroll_actions.date_earned%TYPE,
1830 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
1831 p_retro_start_date OUT NOCOPY date,
1832 p_retro_end_date OUT NOCOPY date,
1833 p_orig_effective_date OUT NOCOPY date,
1834 p_retro_effective_date OUT NOCOPY date,
1835 p_time_span OUT NOCOPY varchar2,
1836 p_retro_type OUT NOCOPY varchar2
1837 )RETURN NUMBER
1838 IS
1839
1840 CURSOR get_retro_entry_details
1841 (c_element_entry_id pay_element_entries_f.element_entry_id%TYPE
1842 ,c_date_earned pay_payroll_actions.date_earned%TYPE)
1843 IS
1844 SELECT pee.element_entry_id
1845 ,ppa.retro_definition_id
1846 ,pepd.retro_component_id
1847 FROM pay_element_entries_f pee
1848 ,pay_assignment_actions paa
1849 ,pay_payroll_actions ppa
1850 ,pay_entry_process_details pepd
1851 WHERE pee.element_entry_id = c_element_entry_id
1852 AND pee.element_entry_id = pepd.element_entry_id
1853 AND pee.creator_id = paa.assignment_action_id
1854 AND paa.payroll_action_id = ppa.payroll_action_id
1855 AND ppa.action_type = 'L'
1856 AND c_date_earned between pee.effective_start_date and pee.effective_end_date;
1857
1858 l_retro_type VARCHAR2(80);
1859 l_proc_name VARCHAR2(100);
1860 l_retro_period NUMBER;
1861 l_fin_year_start DATE;
1862
1863 l_entry_details get_retro_entry_details%ROWTYPE;
1864
1865 l_temp NUMBER;
1866
1867 BEGIN
1868
1869 g_debug := hr_utility.debug_enabled;
1870
1871 IF g_debug THEN
1872 l_proc_name := 'pay_au_paye_ff.get_retro_time_span';
1873 hr_utility.set_location('Entering '||l_proc_name,10);
1874 hr_utility.set_location('IN p_element_entry_id '||p_element_entry_id,30);
1875 hr_utility.set_location('IN p_date_earned '||p_date_earned,30);
1876 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,30);
1877 END IF;
1878
1879 l_retro_type := NULL;
1880
1881 OPEN get_retro_entry_details(p_element_entry_id,p_date_earned);
1882 FETCH get_retro_entry_details INTO l_entry_details;
1883 CLOSE get_retro_entry_details;
1884
1885 IF (l_entry_details.retro_definition_id IS NOT NULL
1886 AND l_entry_details.retro_component_id IS NOT NULL)
1887 THEN
1888 /* Entry Created using Enhanced Retropay */
1889 IF g_debug THEN
1890 hr_utility.set_location('Entry created by Enhanced Retropay ',40);
1891 hr_utility.set_location('Retro Component ID =>'||l_entry_details.retro_component_id,40);
1892 END IF;
1893 l_retro_type := 'ADVANCED_RETRO';
1894 l_temp := get_enhanced_retro_period
1895 (
1896 p_element_entry_id => p_element_entry_id,
1897 p_date_earned => p_date_earned,
1898 p_tax_unit_id => p_tax_unit_id,
1899 p_retro_start_date => p_retro_start_date,
1900 p_retro_end_date => p_retro_end_date,
1901 p_orig_effective_date => p_orig_effective_date,
1902 p_retro_effective_date => p_retro_effective_date,
1903 p_time_span => p_time_span);
1904 ELSE
1905 /* Entry Created Using Retropay by Element
1906 The Effective Dates are set to NULL as its irrelevent and not required
1907 for Retropay by element processing */
1908
1909 IF g_debug THEN
1910 hr_utility.set_location('Entry created by Retropay by Element',50);
1911 END IF;
1912
1913 l_retro_type := 'RETRO_ELE';
1914 p_orig_effective_date := NULL;
1915 p_retro_effective_date := NULL;
1916
1917 l_temp := get_retro_period
1918 (
1919 p_element_entry_id => p_element_entry_id,
1920 p_date_earned => p_date_earned,
1921 p_tax_unit_id => p_tax_unit_id,
1922 p_retro_start_date => p_retro_start_date,
1923 p_retro_end_date => p_retro_end_date
1924 );
1925
1926 l_retro_period := months_between(p_date_earned,p_retro_end_date);
1927 IF l_retro_period > 12
1928 THEN
1929 p_time_span := 'GT12';
1930 ELSE
1931 l_fin_year_start := hr_au_routes.span_start(p_date_earned,1,'01-07');
1932 IF p_retro_end_date < l_fin_year_start
1933 THEN
1934 p_time_span := 'LT12 Prev';
1935 ELSE
1936 p_time_span := 'LT12 Curr';
1937 END IF;
1938 END IF;
1939 END IF;
1940
1941 p_retro_type := l_retro_type;
1942
1943 IF g_debug
1944 THEN
1945 hr_utility.set_location('OUT p_retro_start_date '||p_retro_start_date,80);
1946 hr_utility.set_location('OUT p_retro_end_date '||p_retro_end_date,80);
1947 hr_utility.set_location('OUT p_orig_effective_date '||p_orig_effective_date,80);
1948 hr_utility.set_location('OUT p_retro_effective_date '||p_retro_effective_date,80);
1949 hr_utility.set_location('OUT p_time_span '||p_time_span,80);
1950 hr_utility.set_location('OUT l_retro_type '||l_retro_type,80);
1951 hr_utility.set_location('Exiting '||l_proc_name,90);
1952 END IF;
1953
1954 return 1;
1955 END get_retro_time_span;
1956
1957 function count_retro_periods
1958 (
1959 p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
1960 p_date_earned in pay_payroll_actions.date_earned%TYPE,
1961 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE, /*Bug 4418107*/
1962 p_use_tax_flag IN VARCHAR2, --4415795
1963 p_mode IN VARCHAR2 DEFAULT 'E' --7665727
1964 )return number
1965 is
1966
1967 -- Need to get the pay value amount so we can check totals in each period.
1968 /*
1969 cursor get_element_entries
1970 is
1971 select pee.element_entry_id from pay_element_entries_f pee,
1972 pay_assignment_actions paa
1973 where paa.assignment_action_id=p_assignment_action_id
1974 and pee.assignment_id=paa.assignment_id
1975 and pee.creator_type IN ('EE','RR')
1976 and p_date_earned between pee.effective_start_date and pee.effective_end_date;
1977 */
1978 cursor get_element_entries
1979 is
1980 select pee.element_entry_id,
1981 peev.screen_entry_value retro_amount,
1982 pec.classification_name
1983 from pay_element_entries_f pee,
1984 pay_element_entry_values_f peev,
1985 pay_element_links_f pelf,
1986 pay_element_types_f pet,
1987 pay_element_classifications pec,
1988 pay_input_values_f piv,
1989 pay_assignment_actions paa
1990 where paa.assignment_action_id = p_assignment_action_id
1991 and pee.assignment_id = paa.assignment_id
1992 and pee.creator_type IN ('EE','RR')
1993 and p_date_earned between pee.effective_start_date and pee.effective_end_date
1994 -- Only Earnings.
1995 and pelf.element_link_id = pee.element_link_id
1996 and p_date_earned between pelf.effective_start_date and pelf.effective_end_date
1997 and pet.element_type_id = pelf.element_type_id
1998 and p_date_earned between pet.effective_start_date and pet.effective_end_date
1999 and pec.classification_id = pet.classification_id
2000 and pec.classification_name in ('Earnings', 'Pre Tax Deductions')
2001 -- Only Pay Value
2002 and peev.element_entry_id = pee.element_entry_id
2003 and p_date_earned between peev.effective_start_date and peev.effective_end_date
2004 and peev.input_value_id = piv.input_value_id
2005 and p_date_earned between piv.effective_start_date and piv.effective_end_date
2006 and piv.name = 'Pay Value';
2007
2008 /* Bug 5846272 - Cursor to read the Advanced Retropay Legislation Rule */
2009 CURSOR get_legislation_rule
2010 IS
2011 SELECT plr.rule_mode
2012 FROM pay_legislation_rules plr
2013 WHERE plr.legislation_code = 'AU'
2014 AND plr.rule_type ='ADVANCED_RETRO';
2015
2016 /* Bug 6139035 - Cursor to fetch Effective Date */
2017 CURSOR c_get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
2018 IS
2019 SELECT ppa.effective_date
2020 FROM pay_payroll_actions ppa
2021 ,pay_assignment_actions paa
2022 WHERE paa.assignment_action_id = c_assignment_action_id
2023 AND paa.payroll_action_id = ppa.payroll_action_id;
2024
2025 l_count number;
2026 l_retro_periods number;
2027 is_retro_payment varchar2(10);
2028 retro_start_date date;
2029 retro_end_date date;
2030 financial_year_span_start date;
2031 l_retro_end_date number;
2032 x varchar2(100);
2033 l_procedure varchar2(80);
2034 TYPE num_tab_type IS TABLE OF NUMBER(10) INDEX BY binary_integer; -- Bug 4412537
2035 num_tab num_tab_type;
2036
2037 -- This table introduced to allow totalling of the retro amounts in each retro period. Bug 5374076.
2038 TYPE tot_period_amount_type IS TABLE OF NUMBER INDEX BY binary_integer; /* Bug# 5397711*/
2039 tot_period_amount tot_period_amount_type;
2040
2041 /* Bug 5846272 - Introduced variables for Enhanced Retropay processing */
2042 l_adv_retro_flag VARCHAR2(10);
2043 l_retro_eff_date DATE;
2044 l_orig_effective_date DATE;
2045 l_time_span varchar2(80);
2046 l_retro_type varchar2(80);
2047
2048 /* Bug 6139035 - Introduced variables for Effective Date */
2049 l_eff_date_yr_start DATE;
2050 l_pay_effective_date DATE;
2051
2052 --
2053 begin
2054
2055 g_debug := hr_utility.debug_enabled;
2056
2057 if g_debug then
2058 l_procedure :='pay_au_paye_ff.count_retro_periods';
2059 hr_utility.set_location('Entering '||l_procedure,10);
2060 hr_utility.set_location('IN p_assignment_action_id '||p_assignment_action_id,20);
2061 hr_utility.set_location('IN p_date_earned '||to_char(p_date_earned,'dd/mm/yyyy'),20);
2062 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,20);
2063 hr_utility.set_location('IN p_use_tax_flag '||p_use_tax_flag,20);
2064 end if;
2065
2066
2067 OPEN c_get_effective_date(p_assignment_action_id);
2068 FETCH c_get_effective_date INTO l_pay_effective_date;
2069 CLOSE c_get_effective_date;
2070
2071 IF g_debug THEN
2072 hr_utility.set_location('Pay Effective Date =>'||to_char(l_pay_effective_date,'dd/mm/yyyy'),30);
2073 END IF;
2074
2075 /*bug7665727 - Based on Effective Date because +ve Offset payrolls may have Pay Date in the next
2076 financial year for a period in current financial year. */
2077 IF to_char(l_pay_effective_date,'dd/mm/yyyy') >= '01/07/2009' THEN
2078
2079 l_retro_periods := count_retro_periods_2009
2080 ( p_assignment_action_id => p_assignment_action_id,
2081 p_date_earned => p_date_earned,
2082 p_tax_unit_id => p_tax_unit_id,
2083 p_use_tax_flag => p_use_tax_flag,
2084 p_mode => p_mode);
2085
2086 ELSE
2087
2088 l_retro_periods:=0;
2089
2090 /* Bug 6139035 - Get Financial Year Information based on Effective Date of run */
2091 l_eff_date_yr_start := hr_au_routes.span_start(l_pay_effective_date,1,'01-07');
2092 financial_year_span_start:=hr_au_routes.span_start(p_date_earned,1,'01-07');
2093
2094 /* Bug 5846272 - Read the Legislation Rule value for Enhanced Retropay. If
2095 rule not found,set the flag to 'N' */
2096 OPEN get_legislation_rule;
2097 FETCH get_legislation_rule INTO l_adv_retro_flag;
2098 IF get_legislation_rule%NOTFOUND THEN
2099 l_adv_retro_flag := 'N';
2100 END IF;
2101 CLOSE get_legislation_rule;
2102
2103 IF g_debug THEN
2104 hr_utility.set_location('Enhanced Retropay Rule Value =>'||l_adv_retro_flag,50);
2105 END IF;
2106
2107
2108 for process_element in get_element_entries
2109 loop
2110 /* Bug 5846272 - Use Existing Logic if Enh Retro Rule is 'N' ELSE
2111 use Enhanced Retropay functionality
2112 */
2113 IF l_adv_retro_flag = 'N'
2114 THEN
2115 -- is_retro_payment:=check_if_retro(process_element.element_entry_id,p_date_earned);
2116 -- if is_retro_payment='Y' then
2117 -- Note this processing is only done for retro less than 12 months, in prev fin year.
2118
2119 /*Bug 4418107 - The following piece of code has been introduced to count retro periods on the basis
2120 of Legal Employer.*/
2121
2122 x:=get_retro_period(process_element.element_entry_id,p_date_earned,p_tax_unit_id, retro_start_date,retro_end_date); /*Bug 4418107*/
2123
2124 if p_use_tax_flag = 'Y' then
2125 /* Bug 6139035 - Commented code - Check for Retro LT12 Prev should be based on Date Paid(Effective Date)
2126 and not Date Earned
2127 if months_between(p_date_earned,retro_end_date) <= 12 and p_date_earned >= financial_year_span_start
2128 and retro_end_date < financial_year_span_start */
2129 IF months_between(l_pay_effective_date,retro_end_date) <= 12 AND l_pay_effective_date >=
2130 l_eff_date_yr_start AND retro_end_date < l_eff_date_yr_start
2131 THEN
2132 l_retro_end_date := to_number(to_char(retro_end_date,'DDMM')); -- Bug 4412537
2133 /*Bug 4357306 - Logic given below has been implemented to count the retro end dates.
2134 If the customer pays for two different retro elements for the same retro
2135 period then the dates were counted twice, so to avoid that a table has been
2136 created where the values get stored in the index. If there is already a index
2137 for retro end dates the counter l_retro_periods is not incremented and a new index
2138 is not created, but if a new retro end date is being processed then the counter is
2139 incremented and index is also created in the table num_tab.*/
2140 IF num_tab.EXISTS(l_retro_end_date) THEN
2141 num_tab(l_retro_end_date) := num_tab(l_retro_end_date) + 1;
2142 ELSE
2143 num_tab(l_retro_end_date) := 1;
2144 l_retro_periods:=l_retro_periods + 1;
2145 END IF;
2146
2147 -- Add up the amounts. If it's a pre tax deduction then need to subtract.
2148 IF tot_period_amount.EXISTS(l_retro_end_date) THEN
2149 if process_element.classification_name = 'Pre Tax Deductions' then
2150 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) - to_number(process_element.retro_amount);
2151 else
2152 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) + to_number(process_element.retro_amount);
2153 end if;
2154 ELSE
2155 if process_element.classification_name = 'Pre Tax Deductions' then
2156 tot_period_amount(l_retro_end_date) := 0 - to_number(process_element.retro_amount);
2157 else
2158 tot_period_amount(l_retro_end_date) := to_number(process_element.retro_amount);
2159 end if;
2160 END IF;
2161
2162 end if;
2163 elsif p_use_tax_flag = 'N' then
2164 /*Bug 4415795 - This portion has been introduced so that the count_retro_periods
2165 return the value for current year case before 01-JUL-2005 and for
2166 less than 12 months previous year case after 01-JUL-2005*/
2167
2168 if months_between(p_date_earned,retro_end_date) <= 12 and p_date_earned >= financial_year_span_start
2169 and retro_end_date >= financial_year_span_start
2170 then
2171 l_retro_end_date := to_number(to_char(retro_end_date,'DDMM')); -- Bug 4412537
2172 IF num_tab.EXISTS(l_retro_end_date) THEN
2173 num_tab(l_retro_end_date) := num_tab(l_retro_end_date) + 1;
2174 ELSE
2175 num_tab(l_retro_end_date) := 1;
2176 l_retro_periods:=l_retro_periods + 1;
2177 END IF;
2178
2179 -- Add up the amounts. If it's a pre tax deduction then need to subtract.
2180 IF tot_period_amount.EXISTS(l_retro_end_date) THEN
2181 if process_element.classification_name = 'Pre Tax Deductions' then
2182 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) - to_number(process_element.retro_amount);
2183 else
2184 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) + to_number(process_element.retro_amount);
2185 end if;
2186 ELSE
2187 if process_element.classification_name = 'Pre Tax Deductions' then
2188 tot_period_amount(l_retro_end_date) := 0 - to_number(process_element.retro_amount);
2189 else
2190 tot_period_amount(l_retro_end_date) := to_number(process_element.retro_amount);
2191 end if;
2192 END IF;
2193
2194 end if;
2195 end if;
2196 ELSE
2197 /* Bug 5846272
2198 Section for Enhanced Retropay - l_adv_retro_flag = 'Y'
2199 Logic check for p_use_tax_flag is not implemented here because Enhanced Retropay
2200 was not supported prior to Jul-2005.
2201 */
2202
2203 x := get_retro_time_span
2204 (
2205 p_element_entry_id => process_element.element_entry_id,
2206 p_date_earned => p_date_earned,
2207 p_tax_unit_id => p_tax_unit_id,
2208 p_retro_start_date => retro_start_date,
2209 p_retro_end_date => retro_end_date,
2210 p_orig_effective_date => l_orig_effective_date,
2211 p_retro_effective_date => l_retro_eff_date,
2212 p_time_span => l_time_span,
2213 p_retro_type => l_retro_type
2214 );
2215
2216 IF l_time_span = 'LT12 Prev'
2217 THEN
2218 l_retro_end_date := to_number(to_char(retro_end_date,'DDMM')); -- Bug 4412537
2219
2220 IF num_tab.EXISTS(l_retro_end_date) THEN
2221 num_tab(l_retro_end_date) := num_tab(l_retro_end_date) + 1;
2222 ELSE
2223 num_tab(l_retro_end_date) := 1;
2224 l_retro_periods:=l_retro_periods + 1;
2225 END IF;
2226
2227 -- Add up the amounts. If it's a pre tax deduction then need to subtract.
2228 IF tot_period_amount.EXISTS(l_retro_end_date) THEN
2229 IF process_element.classification_name = 'Pre Tax Deductions' then
2230 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) - to_number(process_element.retro_amount);
2231 ELSE
2232 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) + to_number(process_element.retro_amount);
2233 END IF;
2234 ELSE
2235 IF process_element.classification_name = 'Pre Tax Deductions' THEN
2236 tot_period_amount(l_retro_end_date) := 0 - to_number(process_element.retro_amount);
2237 ELSE
2238 tot_period_amount(l_retro_end_date) := to_number(process_element.retro_amount);
2239 END IF;
2240 END IF;
2241 END IF;
2242
2243 END IF; /* End of Enhanced Retropay part */
2244
2245 end loop;
2246
2247 -- Check if any periods have 0 total retro amount. If they do then don't count the retro period.
2248
2249 l_count := tot_period_amount.FIRST;
2250 while l_count is not null loop
2251
2252 if tot_period_amount(l_count) = 0 then
2253 l_retro_periods := l_retro_periods - 1;
2254 end if;
2255 l_count := tot_period_amount.NEXT(l_count);
2256 end loop;
2257
2258 END IF;
2259
2260 if g_debug then
2261 hr_utility.set_location('Return l_retro_periods '||l_retro_periods,60);
2262 hr_utility.set_location('Exiting '||l_procedure,70);
2263 end if;
2264
2265 return l_retro_periods;
2266
2267 end count_retro_periods;
2268
2269 function count_retro_periods_2009
2270 (
2271 p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
2272 p_date_earned in pay_payroll_actions.date_earned%TYPE,
2273 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
2274 p_use_tax_flag IN VARCHAR2,
2275 p_mode IN VARCHAR2 --7665727
2276 )return number
2277 is
2278
2279 /*bug7665727 - element entries for each p_mode case
2280 9922156 - Added pec2 to get the secondary classification of elements
2281 - Modified check to ignore earnings spread elements */
2282 cursor get_element_entries /* retro earnings only */
2283 is
2284 select pee.element_entry_id,
2285 peev.screen_entry_value retro_amount,
2286 pec.classification_name,
2287 DECODE(pec.classification_name,'Earnings',pec2.classification_name,NULL) label
2288 from pay_element_entries_f pee,
2289 pay_element_entry_values_f peev,
2290 pay_element_links_f pelf,
2291 pay_element_types_f pet,
2292 pay_element_classifications pec,
2293 pay_input_values_f piv,
2294 pay_assignment_actions paa,
2295 pay_sub_classification_rules_f psc,
2296 pay_element_classifications pec2 /* Bug 9922156 */
2297 where paa.assignment_action_id = p_assignment_action_id
2298 and pee.assignment_id = paa.assignment_id
2299 and pee.creator_type IN ('EE','RR')
2300 and p_date_earned between pee.effective_start_date and pee.effective_end_date
2301 and pelf.element_link_id = pee.element_link_id
2302 and p_date_earned between pelf.effective_start_date and pelf.effective_end_date
2303 and pet.element_type_id = pelf.element_type_id
2304 and p_date_earned between pet.effective_start_date and pet.effective_end_date
2305 and pec.classification_id = pet.classification_id
2306 and pec.classification_name in ('Earnings', 'Pre Tax Deductions')
2307 and pet.element_type_id = psc.element_type_id(+)
2308 /* Start Bug 9922156 */
2309 and psc.classification_id = pec2.classification_id(+)
2310 and NVL(pec2.classification_name,'Standard') <> 'Spread'
2311 /* End Bug 9922156 */
2312 -- Only Pay Value
2313 and peev.element_entry_id = pee.element_entry_id
2314 and p_date_earned between peev.effective_start_date and peev.effective_end_date
2315 and peev.input_value_id = piv.input_value_id
2316 and p_date_earned between piv.effective_start_date and piv.effective_end_date
2317 and piv.name = 'Pay Value';
2318
2319 cursor get_element_entries_spread /*retro earnings spread only*/
2320 is
2321 select pee.element_entry_id,
2322 peev.screen_entry_value retro_amount,
2323 pec.classification_name
2324 from pay_element_entries_f pee,
2325 pay_element_entry_values_f peev,
2326 pay_element_links_f pelf,
2327 pay_element_types_f pet,
2328 pay_element_classifications pec,
2329 pay_input_values_f piv,
2330 pay_assignment_actions paa,
2331 pay_sub_classification_rules_f psc,
2332 pay_element_classifications pec2
2333 where paa.assignment_action_id = p_assignment_action_id
2334 and pee.assignment_id = paa.assignment_id
2335 and pee.creator_type IN ('EE','RR')
2336 and p_date_earned between pee.effective_start_date and pee.effective_end_date
2337 -- Only Earnings Spread
2338 and pelf.element_link_id = pee.element_link_id
2339 and p_date_earned between pelf.effective_start_date and pelf.effective_end_date
2340 and pet.element_type_id = pelf.element_type_id
2341 and p_date_earned between pet.effective_start_date and pet.effective_end_date
2342 and pec.classification_id = pet.classification_id
2343 and pec.classification_name = 'Earnings'
2344 and pet.element_type_id = psc.element_type_id
2345 and psc.classification_id = pec2.classification_id
2346 and pec2.classification_name = 'Spread'
2347 -- Only Pay Value
2348 and peev.element_entry_id = pee.element_entry_id
2349 and p_date_earned between peev.effective_start_date and peev.effective_end_date
2350 and peev.input_value_id = piv.input_value_id
2351 and p_date_earned between piv.effective_start_date and piv.effective_end_date
2352 and piv.name = 'Pay Value';
2353
2354 cursor get_element_entries_all /* retro earnings+retro earnings spread */
2355 is
2356 select pee.element_entry_id,
2357 peev.screen_entry_value retro_amount,
2358 pec.classification_name
2359 from pay_element_entries_f pee,
2360 pay_element_entry_values_f peev,
2361 pay_element_links_f pelf,
2362 pay_element_types_f pet,
2363 pay_element_classifications pec,
2364 pay_input_values_f piv,
2365 pay_assignment_actions paa
2366 where paa.assignment_action_id = p_assignment_action_id
2367 and pee.assignment_id = paa.assignment_id
2368 and pee.creator_type IN ('EE','RR')
2369 and p_date_earned between pee.effective_start_date and pee.effective_end_date
2370 and pelf.element_link_id = pee.element_link_id
2371 and p_date_earned between pelf.effective_start_date and pelf.effective_end_date
2372 and pet.element_type_id = pelf.element_type_id
2373 and p_date_earned between pet.effective_start_date and pet.effective_end_date
2374 and pec.classification_id = pet.classification_id
2375 and pec.classification_name in ('Earnings', 'Pre Tax Deductions')
2376 -- Only Pay Value
2377 and peev.element_entry_id = pee.element_entry_id
2378 and p_date_earned between peev.effective_start_date and peev.effective_end_date
2379 and peev.input_value_id = piv.input_value_id
2380 and p_date_earned between piv.effective_start_date and piv.effective_end_date
2381 and piv.name = 'Pay Value';
2382
2383
2384 CURSOR get_legislation_rule
2385 IS
2386 SELECT plr.rule_mode
2387 FROM pay_legislation_rules plr
2388 WHERE plr.legislation_code = 'AU'
2389 AND plr.rule_type ='ADVANCED_RETRO';
2390
2391
2392 CURSOR c_get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
2393 IS
2394 SELECT ppa.effective_date
2395 FROM pay_payroll_actions ppa
2396 ,pay_assignment_actions paa
2397 WHERE paa.assignment_action_id = c_assignment_action_id
2398 AND paa.payroll_action_id = ppa.payroll_action_id;
2399
2400 l_count number;
2401 l_retro_periods number;
2402 is_retro_payment varchar2(10);
2403 retro_start_date date;
2404 retro_end_date date;
2405 financial_year_span_start date;
2406 l_retro_end_date number;
2407 x varchar2(100);
2408 l_procedure varchar2(80);
2409 TYPE num_tab_type IS TABLE OF NUMBER(10) INDEX BY binary_integer;
2410 num_tab num_tab_type;
2411
2412
2413 TYPE tot_period_amount_type IS TABLE OF NUMBER INDEX BY binary_integer;
2414 tot_period_amount tot_period_amount_type;
2415
2416
2417 l_adv_retro_flag VARCHAR2(10);
2418 l_retro_eff_date DATE;
2419 l_orig_effective_date DATE;
2420 l_time_span varchar2(80);
2421 l_retro_type varchar2(80);
2422
2423 l_eff_date_yr_start DATE;
2424 l_pay_effective_date DATE;
2425
2426 l_leaveload_mode VARCHAR2(2);
2427
2428 --
2429 begin
2430
2431 g_debug := hr_utility.debug_enabled;
2432
2433 if g_debug then
2434 l_procedure :='pay_au_paye_ff.count_retro_periods_2009';
2435 hr_utility.set_location('Entering '||l_procedure,10);
2436 hr_utility.set_location('IN p_assignment_action_id '||p_assignment_action_id,20);
2437 hr_utility.set_location('IN p_date_earned '||to_char(p_date_earned,'dd/mm/yyyy'),30);
2438 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,40);
2439 hr_utility.set_location('IN p_use_tax_flag '||p_use_tax_flag,50);
2440 hr_utility.set_location('IN p_mode '||p_mode,60);
2441 end if;
2442
2443
2444 l_leaveload_mode := NVL(SUBSTR(p_mode,2,1),'Y');
2445
2446 IF g_debug THEN
2447 hr_utility.set_location('l_leaveload_mode '||l_leaveload_mode,70);
2448 END IF;
2449
2450 OPEN get_legislation_rule;
2451 FETCH get_legislation_rule INTO l_adv_retro_flag;
2452 IF get_legislation_rule%NOTFOUND THEN
2453 l_adv_retro_flag := 'N';
2454 END IF;
2455 CLOSE get_legislation_rule;
2456
2457 IF g_debug THEN
2458 hr_utility.set_location('Enhanced Retropay Rule Value =>'||l_adv_retro_flag,70);
2459 END IF;
2460
2461 l_retro_periods:=0;
2462 financial_year_span_start:=hr_au_routes.span_start(p_date_earned,1,'01-07');
2463
2464 OPEN c_get_effective_date(p_assignment_action_id);
2465 FETCH c_get_effective_date INTO l_pay_effective_date;
2466 CLOSE c_get_effective_date;
2467
2468 l_eff_date_yr_start := hr_au_routes.span_start(l_pay_effective_date,1,'01-07');
2469
2470 /* bug7665727 - if clause as per p_mode */
2471 IF SUBSTR(p_mode,1,1) = 'E' THEN /*retro earnings only */
2472
2473 for process_element in get_element_entries
2474 loop
2475
2476 IF l_adv_retro_flag = 'N'
2477 THEN
2478
2479 x:=get_retro_period(process_element.element_entry_id,p_date_earned,p_tax_unit_id, retro_start_date,retro_end_date);
2480
2481 if p_use_tax_flag = 'Y' then
2482
2483 IF months_between(l_pay_effective_date,retro_end_date) <= 12 AND l_pay_effective_date >=
2484 l_eff_date_yr_start AND retro_end_date < l_eff_date_yr_start
2485 THEN
2486 l_retro_end_date := to_number(to_char(retro_end_date,'DDMM'));
2487
2488 IF num_tab.EXISTS(l_retro_end_date) THEN
2489 num_tab(l_retro_end_date) := num_tab(l_retro_end_date) + 1;
2490 ELSE
2491 num_tab(l_retro_end_date) := 1;
2492 l_retro_periods:=l_retro_periods + 1;
2493 END IF;
2494
2495 -- Add up the amounts. If it's a pre tax deduction then need to subtract.
2496 IF tot_period_amount.EXISTS(l_retro_end_date)
2497 THEN
2498 IF process_element.classification_name = 'Pre Tax Deductions' THEN
2499 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) - to_number(process_element.retro_amount);
2500 ELSIF ((l_leaveload_mode = 'Y') OR (l_leaveload_mode = 'N' AND process_element.label <> 'Leave Loading'))
2501 THEN
2502 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) + to_number(process_element.retro_amount);
2503 END IF;
2504 ELSE
2505 IF process_element.classification_name = 'Pre Tax Deductions' then
2506 tot_period_amount(l_retro_end_date) := 0 - to_number(process_element.retro_amount);
2507 ELSIF ((l_leaveload_mode = 'Y') OR (l_leaveload_mode = 'N' AND process_element.label <> 'Leave Loading'))
2508 THEN
2509 tot_period_amount(l_retro_end_date) := to_number(process_element.retro_amount);
2510 END IF;
2511 END IF;
2512
2513 end if;
2514 end if;
2515
2516 ELSE
2517
2518 x := get_retro_time_span
2519 (
2520 p_element_entry_id => process_element.element_entry_id,
2521 p_date_earned => p_date_earned,
2522 p_tax_unit_id => p_tax_unit_id,
2523 p_retro_start_date => retro_start_date,
2524 p_retro_end_date => retro_end_date,
2525 p_orig_effective_date => l_orig_effective_date,
2526 p_retro_effective_date => l_retro_eff_date,
2527 p_time_span => l_time_span,
2528 p_retro_type => l_retro_type
2529 );
2530
2531 IF l_time_span = 'LT12 Prev'
2532 THEN
2533 l_retro_end_date := to_number(to_char(retro_end_date,'DDMM'));
2534
2535 IF num_tab.EXISTS(l_retro_end_date) THEN
2536 num_tab(l_retro_end_date) := num_tab(l_retro_end_date) + 1;
2537 ELSE
2538 num_tab(l_retro_end_date) := 1;
2539 l_retro_periods:=l_retro_periods + 1;
2540 END IF;
2541
2542 -- Add up the amounts. If it's a pre tax deduction then need to subtract.
2543 IF tot_period_amount.EXISTS(l_retro_end_date) THEN
2544 IF process_element.classification_name = 'Pre Tax Deductions' then
2545 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) - to_number(process_element.retro_amount);
2546 ELSIF (l_leaveload_mode = 'Y') OR (l_leaveload_mode = 'N' AND process_element.label <> 'Leave Loading')
2547 THEN
2548 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) + to_number(process_element.retro_amount);
2549 END IF;
2550 ELSE
2551 IF process_element.classification_name = 'Pre Tax Deductions' THEN
2552 tot_period_amount(l_retro_end_date) := 0 - to_number(process_element.retro_amount);
2553 ELSIF (l_leaveload_mode = 'Y') OR (l_leaveload_mode = 'N' AND process_element.label <> 'Leave Loading')
2554 THEN
2555 tot_period_amount(l_retro_end_date) := to_number(process_element.retro_amount);
2556 END IF;
2557 END IF;
2558 END IF;
2559
2560 END IF; /* End of Enhanced Retropay part */
2561
2562 end loop;
2563
2564 ELSIF SUBSTR(p_mode,1,1) = 'S' THEN /* retro earnings spread only */
2565
2566 for process_element in get_element_entries_spread
2567 loop
2568
2569 IF l_adv_retro_flag = 'N'
2570 THEN
2571
2572 x:=get_retro_period(process_element.element_entry_id,p_date_earned,p_tax_unit_id, retro_start_date,retro_end_date);
2573
2574 if p_use_tax_flag = 'Y' then /* p_use_tax_flag = 'Y' only */
2575
2576 IF months_between(l_pay_effective_date,retro_end_date) <= 12 AND l_pay_effective_date >=
2577 l_eff_date_yr_start AND retro_end_date < l_eff_date_yr_start
2578 THEN
2579 l_retro_end_date := to_number(to_char(retro_end_date,'DDMM'));
2580
2581 IF num_tab.EXISTS(l_retro_end_date) THEN
2582 num_tab(l_retro_end_date) := num_tab(l_retro_end_date) + 1;
2583 ELSE
2584 num_tab(l_retro_end_date) := 1;
2585 l_retro_periods:=l_retro_periods + 1;
2586 END IF;
2587
2588
2589 IF tot_period_amount.EXISTS(l_retro_end_date) THEN
2590 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) + to_number(process_element.retro_amount);
2591 ELSE
2592 tot_period_amount(l_retro_end_date) := to_number(process_element.retro_amount);
2593 END IF;
2594
2595 end if;
2596 end if;
2597
2598 ELSE
2599
2600 x := get_retro_time_span
2601 (
2602 p_element_entry_id => process_element.element_entry_id,
2603 p_date_earned => p_date_earned,
2604 p_tax_unit_id => p_tax_unit_id,
2605 p_retro_start_date => retro_start_date,
2606 p_retro_end_date => retro_end_date,
2607 p_orig_effective_date => l_orig_effective_date,
2608 p_retro_effective_date => l_retro_eff_date,
2609 p_time_span => l_time_span,
2610 p_retro_type => l_retro_type
2611 );
2612
2613 IF l_time_span = 'LT12 Prev'
2614 THEN
2615 l_retro_end_date := to_number(to_char(retro_end_date,'DDMM'));
2616
2617 IF num_tab.EXISTS(l_retro_end_date) THEN
2618 num_tab(l_retro_end_date) := num_tab(l_retro_end_date) + 1;
2619 ELSE
2620 num_tab(l_retro_end_date) := 1;
2621 l_retro_periods:=l_retro_periods + 1;
2622 END IF;
2623
2624 -- Add up the amounts. If it's a pre tax deduction then need to subtract.
2625 IF tot_period_amount.EXISTS(l_retro_end_date) THEN
2626 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) + to_number(process_element.retro_amount);
2627 ELSE
2628 tot_period_amount(l_retro_end_date) := to_number(process_element.retro_amount);
2629 END IF;
2630 END IF;
2631
2632 END IF; /* End of Enhanced Retropay part */
2633
2634 end loop;
2635
2636 ELSE /* retro earnings + retro earnings spread */
2637
2638 for process_element in get_element_entries_all
2639 loop
2640
2641 IF l_adv_retro_flag = 'N'
2642 THEN
2643
2644 x:=get_retro_period(process_element.element_entry_id,p_date_earned,p_tax_unit_id, retro_start_date,retro_end_date);
2645
2646 if p_use_tax_flag = 'Y' then /* p_use_tax_flag = 'Y' only */
2647
2648 IF months_between(l_pay_effective_date,retro_end_date) <= 12 AND l_pay_effective_date >=
2649 l_eff_date_yr_start AND retro_end_date < l_eff_date_yr_start
2650 THEN
2651 l_retro_end_date := to_number(to_char(retro_end_date,'DDMM'));
2652
2653 IF num_tab.EXISTS(l_retro_end_date) THEN
2654 num_tab(l_retro_end_date) := num_tab(l_retro_end_date) + 1;
2655 ELSE
2656 num_tab(l_retro_end_date) := 1;
2657 l_retro_periods:=l_retro_periods + 1;
2658 END IF;
2659
2660 -- Add up the amounts. If it's a pre tax deduction then need to subtract.
2661 IF tot_period_amount.EXISTS(l_retro_end_date) THEN
2662 if process_element.classification_name = 'Pre Tax Deductions' then
2663 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) - to_number(process_element.retro_amount);
2664 else
2665 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) + to_number(process_element.retro_amount);
2666 end if;
2667 ELSE
2668 if process_element.classification_name = 'Pre Tax Deductions' then
2669 tot_period_amount(l_retro_end_date) := 0 - to_number(process_element.retro_amount);
2670 else
2671 tot_period_amount(l_retro_end_date) := to_number(process_element.retro_amount);
2672 end if;
2673 END IF;
2674
2675 end if;
2676 end if;
2677
2678 ELSE
2679
2680 x := get_retro_time_span
2681 (
2682 p_element_entry_id => process_element.element_entry_id,
2683 p_date_earned => p_date_earned,
2684 p_tax_unit_id => p_tax_unit_id,
2685 p_retro_start_date => retro_start_date,
2686 p_retro_end_date => retro_end_date,
2687 p_orig_effective_date => l_orig_effective_date,
2688 p_retro_effective_date => l_retro_eff_date,
2689 p_time_span => l_time_span,
2690 p_retro_type => l_retro_type
2691 );
2692
2693 IF l_time_span = 'LT12 Prev'
2694 THEN
2695 l_retro_end_date := to_number(to_char(retro_end_date,'DDMM'));
2696
2697 IF num_tab.EXISTS(l_retro_end_date) THEN
2698 num_tab(l_retro_end_date) := num_tab(l_retro_end_date) + 1;
2699 ELSE
2700 num_tab(l_retro_end_date) := 1;
2701 l_retro_periods:=l_retro_periods + 1;
2702 END IF;
2703
2704 -- Add up the amounts. If it's a pre tax deduction then need to subtract.
2705 IF tot_period_amount.EXISTS(l_retro_end_date) THEN
2706 IF process_element.classification_name = 'Pre Tax Deductions' then
2707 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) - to_number(process_element.retro_amount);
2708 ELSE
2709 tot_period_amount(l_retro_end_date) := tot_period_amount(l_retro_end_date) + to_number(process_element.retro_amount);
2710 END IF;
2711 ELSE
2712 IF process_element.classification_name = 'Pre Tax Deductions' THEN
2713 tot_period_amount(l_retro_end_date) := 0 - to_number(process_element.retro_amount);
2714 ELSE
2715 tot_period_amount(l_retro_end_date) := to_number(process_element.retro_amount);
2716 END IF;
2717 END IF;
2718 END IF;
2719
2720 END IF; /* End of Enhanced Retropay part */
2721
2722 end loop;
2723
2724 END IF;
2725
2726 -- Check if any periods have 0 total retro amount. If they do then don't count the retro period.
2727
2728 l_count := tot_period_amount.FIRST;
2729 while l_count is not null loop
2730
2731 if tot_period_amount(l_count) = 0 then
2732 l_retro_periods := l_retro_periods - 1;
2733 end if;
2734 l_count := tot_period_amount.NEXT(l_count);
2735 end loop;
2736
2737 if g_debug then
2738 hr_utility.set_location('Return l_retro_periods '||l_retro_periods,80);
2739 hr_utility.set_location('Exiting '||l_procedure,90);
2740 end if;
2741
2742 return l_retro_periods;
2743
2744 end count_retro_periods_2009;
2745
2746
2747 function calculate_tax(p_date_earned in pay_payroll_actions.date_earned%TYPE,
2748 p_period_amount in number,
2749 p_period_frequency in number,
2750 p_tax_scale in number,
2751 p_a1_variable in number,
2752 p_b1_variable in number
2753 )return number is
2754
2755 pay_per_week number;
2756 tax_on_weekly number;
2757 tax_on_total_period number;
2758 l_procedure varchar2(80);
2759
2760 begin
2761 g_debug := hr_utility.debug_enabled;
2762
2763 if g_debug then
2764 l_procedure :='pay_au_paye_ff.calculate_tax';
2765 hr_utility.set_location('Entering '||l_procedure,10);
2766 hr_utility.set_location('IN p_date_earned '||to_char(p_date_earned,'dd/mm/yyyy'),20);
2767 hr_utility.set_location('IN p_period_amount '||p_period_amount,25);
2768 hr_utility.set_location('IN p_period_frequency '||p_period_frequency,30);
2769 hr_utility.set_location('IN p_tax_scale '||p_tax_scale,35);
2770 hr_utility.set_location('IN p_al_variable '||p_a1_variable,40);
2771 hr_utility.set_location('IN p_bl_variable '||p_a1_variable,45);
2772
2773 end if;
2774
2775 pay_per_week:=convert_to_week(p_period_frequency,p_period_amount);
2776
2777 tax_on_weekly:=(p_a1_variable*pay_per_week)- p_b1_variable;
2778
2779 if p_tax_scale <> 4 then
2780 tax_on_weekly:=round(tax_on_weekly);
2781
2782 else
2783 tax_on_weekly:=trunc(tax_on_weekly);
2784
2785 end if;
2786
2787
2788 tax_on_total_period:=convert_to_period_amt(p_period_frequency,tax_on_weekly,p_tax_scale);
2789
2790 if g_debug then
2791 hr_utility.set_location('Return tax_on_total_period '||tax_on_total_period,50);
2792 hr_utility.set_location('Exiting '||l_procedure,60);
2793 end if ;
2794
2795 return tax_on_total_period;
2796
2797 end calculate_tax;
2798
2799
2800
2801 function check_fixed_deduction(p_assignment_id in per_all_assignments_f.assignment_id%TYPE, p_date_earned in date)
2802 return varchar2 is
2803 /* Bug 4374115 - Added check for Reverse Runs */
2804 cursor check_fixed_deduction(p_assignment_id in number, p_date_earned in date)
2805 is
2806 select 'Y'
2807 from
2808 pay_element_types_f pet,
2809 pay_input_values_f piv,
2810 pay_run_result_values prrv,
2811 pay_run_results prr,
2812 pay_assignment_actions paa,
2813 pay_payroll_actions ppa,
2814 per_time_periods ptp,
2815 per_all_assignments_f paaf
2816 where pet.element_name = 'Extra Withholding Payments'
2817 and piv.name='Withholding Amount'
2818 and pet.element_type_id=piv.element_type_id
2819 and piv.input_value_id=prrv.input_value_id
2820 and prrv.run_result_id=prr.run_result_id
2821 and nvl(prrv.result_value,'0') > '0' /*Bug 4588483 */
2822 and prr.assignment_action_id=paa.assignment_action_id
2823 and paa.payroll_action_id=ppa.payroll_action_id
2824 and ptp.payroll_id = ppa.payroll_id
2825 and paa.assignment_id = p_assignment_id /* Bug#2563515 */
2826 and paa.assignment_id = paaf.assignment_id /* Bug#2563515 */
2827 and p_date_earned between paaf.effective_start_date and paaf.effective_end_date /* Bug#2563515 */
2828 and p_date_earned between ptp.start_date and ptp.end_date
2829 and ppa.date_earned between ptp.start_date and ptp.end_date
2830 /* Bug - 2491328 Join added for improving the performance */
2831 and pet.element_type_id = prr.element_type_id
2832 and pet.legislation_code = 'AU'
2833 and piv.legislation_code = 'AU'
2834 and paa.action_status = 'C' /*bug 8847457*/
2835 /* Bug - 2491328 Join added for improving the performance */
2836 /* Bug 4374115 - Start */
2837 and not exists(
2838 select pai.locking_action_id
2839 from pay_assignment_actions paa1,
2840 pay_payroll_actions ppa1,
2841 pay_action_interlocks pai
2842 where ppa1.payroll_action_id = paa1.payroll_action_id
2843 and ppa1.action_type = 'V'
2844 and paa1.assignment_id = p_assignment_id
2845 and pai.locking_action_id = paa1.assignment_action_id
2846 and pai.locked_action_id = paa.assignment_action_id
2847 )
2848 /* Bug 4374115 - End */
2849 and not exists(
2850 select piv.name
2851 from
2852 pay_element_types_f pet,
2853 pay_input_values_f piv,
2854 pay_input_values_f piv1,
2855 pay_element_links_f pel, /* Bug#2563515 */
2856 pay_element_entries_f peef, /* Bug#2563515 */
2857 pay_element_entry_values_f peev,
2858 pay_element_entry_values_f peev1
2859 where pet.element_name = 'Extra Withholding Payments'
2860 and pet.element_type_id= piv.element_type_id
2861 and pet.element_type_id = pel.element_type_id /* Bug#2563515 */
2862 and pel.element_link_id = peef.element_link_id /* Bug#2563515 */
2863 and peef.element_entry_id = peev.element_entry_id /* Bug#2563515 */
2864 and peef.element_entry_id = peev1.element_entry_id /* Bug#2563515 */
2865 and piv.name='Withholding Percentage'
2866 and piv.input_value_id=peev.input_value_id
2867 and piv1.name='Withholding Amount'
2868 and nvl(peev1.screen_entry_value,'0') ='0'
2869 and piv1.input_value_id=peev1.input_value_id
2870 and peev.screen_entry_value is not null
2871 and peef.assignment_id = paaf.assignment_id /* Bug#2563515 */
2872 and p_date_earned between pet.effective_start_date and pet.effective_end_date
2873 and p_date_earned between peef.effective_start_date and peef.effective_end_date /* Bug#2563515 */
2874 and p_date_earned between pel.effective_start_date and pel.effective_end_date /* Bug#2563515 */
2875 and p_date_earned between peev1.effective_start_date and peev1.effective_end_date
2876 and p_date_earned between peev.effective_start_date and peev.effective_end_date
2877 /*Bug - 2491328 Join added for improving the performance */
2878 and pet.element_type_id=piv1.element_type_id
2879 );
2880
2881 l_deduction_flag varchar2(10);
2882
2883 l_procedure varchar2(80);
2884
2885 begin
2886 g_debug := hr_utility.debug_enabled;
2887
2888 if g_debug then
2889 l_procedure :='pay_au_paye_ff.check_fixed_deduction';
2890 hr_utility.set_location('Entering '||l_procedure,10);
2891 hr_utility.set_location('IN p_assignment_id '||p_assignment_id,20);
2892 hr_utility.set_location('IN p_date_earned '||to_char(p_date_earned,'dd/mm/yyyy'),30);
2893 end if ;
2894
2895 OPEN check_fixed_deduction(p_assignment_id , p_date_earned);
2896 FETCH check_fixed_deduction into l_deduction_flag;
2897 CLOSE check_fixed_deduction;
2898
2899 if l_deduction_flag is null then
2900 l_deduction_flag:='N';
2901 end if;
2902
2903 if g_debug then
2904 hr_utility.set_location('OUT l_deduction_flag '||l_deduction_flag,35);
2905 hr_utility.set_location('Exiting '||l_procedure,40);
2906 end if ;
2907
2908 return l_deduction_flag;
2909
2910 end check_fixed_deduction;
2911
2912 FUNCTION validate_data_magtape
2913 (p_data in varchar2)
2914 RETURN varchar2 is
2915
2916 l_pos_value VARCHAR2(1) := NULL ;
2917 l_data VARCHAR2(300):= NULL ;
2918 l_data_substr VARCHAR2(300):= NULL ;
2919 l_ins_result NUMBER := 0 ;
2920 l_counter NUMBER := 0 ;
2921 l_length NUMBER := 0 ;
2922 l_blank_counter NUMBER := 0 ;
2923
2924 BEGIN
2925 g_debug := hr_utility.debug_enabled;
2926 IF g_debug THEN
2927 hr_utility.trace('Entered function pay_au_paye_ff.validate_data_magtape');
2928 hr_utility.trace('IN p_data '||p_data);
2929 END IF;
2930
2931 IF (p_data IS NULL) THEN
2932 IF g_debug THEN
2933 hr_utility.trace('Exiting function pay_au_paye_ff.validate_data_magtape');
2934 END IF;
2935
2936 RETURN ' ';
2937 END IF;
2938
2939 IF g_debug THEN
2940 hr_utility.trace('Value of the in parameter p_data ==>' || p_data);
2941 END IF;
2942 l_data := replace(p_data,'_','-');
2943
2944 l_length := length(p_data);
2945 IF g_debug THEN
2946 hr_utility.trace('Length of the input data passed ==>' || l_length);
2947 END IF;
2948 FOR l_counter IN 1..l_length
2949 LOOP
2950 IF g_debug THEN
2951 hr_utility.trace('Counter value ==>' || l_counter);
2952 END IF;
2953 l_pos_value := upper(substr(l_data,l_counter,1));
2954 IF g_debug THEN
2955 hr_utility.trace('Value at position ' || l_counter || '==>' || l_pos_value);
2956 END IF;
2957 IF (l_pos_value = ' ' and l_counter > 1) THEN /* No need to check first character */
2958 IF (l_blank_counter = l_counter - 1) THEN
2959 IF g_debug THEN
2960 hr_utility.trace('Value ' || l_pos_value || 'is invalid. More than one space between words.');
2961 END IF;
2962 /* Remove all blank spaces after the first. */
2963 l_data_substr := substr(l_data, l_counter, l_length);
2964 l_data := substr(l_data, 1, l_blank_counter);
2965 l_data_substr := ltrim(l_data_substr);
2966 l_data := concat(l_data, l_data_substr);
2967 /* We have now reduced the length of the string therefore we need to reset
2968 l_length and check the new character in the current value of l_counter
2969 as we cannot reassign value of l_counter. */
2970 l_pos_value := upper(substr(l_data,l_counter,1));
2971 l_length := length(l_data);
2972 END IF;
2973 l_blank_counter := l_counter;
2974 END IF;
2975
2976 l_ins_result := instr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ()&/"''-',l_pos_value);
2977
2978 IF l_ins_result = 0 THEN
2979 IF g_debug THEN
2980 hr_utility.trace('Value ' || l_pos_value || 'is invalid');
2981 END IF;
2982 l_data := replace(l_data,l_pos_value,' ');
2983 ELSE
2984 IF g_debug THEN
2985 hr_utility.trace('Value ' || l_pos_value || 'is valid');
2986 END IF;
2987 END IF;
2988 END LOOP;
2989
2990 IF (ltrim(l_data) IS NULL) THEN
2991 IF g_debug THEN
2992 hr_utility.trace('Exiting function pay_au_paye_ff.validate_data_magtape');
2993 END IF;
2994 RETURN ' ';
2995 ELSE
2996 IF g_debug THEN
2997 hr_utility.trace('Final validated value ==>' || ltrim(l_data));
2998 hr_utility.trace('Exiting function pay_au_paye_ff.validate_data_magtape');
2999 END IF;
3000 RETURN ltrim(l_data);
3001 END IF;
3002
3003 END validate_data_magtape;
3004
3005 /* Bug No : 2977425 - Added the new formula function */
3006 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)
3007 RETURN VARCHAR2 IS
3008 msg varchar2(1000);
3009 l_procedure varchar2(80);
3010 BEGIN
3011 g_debug := hr_utility.debug_enabled;
3012
3013 if g_debug then
3014 l_procedure :='pay_au_paye_ff.get_table_value';
3015 hr_utility.set_location('Entering '||l_procedure,10);
3016 hr_utility.set_location('IN BUSINESS_GROUP_ID '||BUSINESS_GROUP_ID ,20);
3017 hr_utility.set_location('IN EARN_NAME '||EARN_NAME,25);
3018 hr_utility.set_location('IN scale '||scale,30);
3019 hr_utility.set_location('IN EARNING_VALUE '||EARNING_VALUE,35);
3020 hr_utility.set_location('IN PERIOD_DATE '||to_char(PERIOD_DATE,'dd/mm/yyyy'),40);
3021
3022 end if;
3023
3024 IF EARNING_VALUE < 0 then
3025 msg := fffunc.gfm('PAY', 'HR_AU_NEGATIVE_EARNINGS','EARN_NAME',EARN_NAME, 'EARNING_VALUE',to_char(EARNING_VALUE));
3026 if g_debug then
3027 hr_utility.set_location('Return msg '||msg,50);
3028 hr_utility.set_location('Exiting '||l_procedure,60);
3029 end if;
3030
3031 RETURN msg;
3032 ELSE
3033 a := hruserdt.get_table_value (BUSINESS_GROUP_ID, scale, scale||'a', TO_CHAR(EARNING_VALUE), PERIOD_DATE);
3034 b := hruserdt.get_table_value (BUSINESS_GROUP_ID, scale, scale||'b', TO_CHAR(EARNING_VALUE), PERIOD_DATE);
3035
3036 if g_debug then
3037 hr_utility.set_location('OUT a '||a,45);
3038 hr_utility.set_location('OUT b '||b,50);
3039 hr_utility.set_location('Exiting '||l_procedure,60);
3040 end if;
3041
3042 RETURN 'ZZZ';
3043 END IF;
3044
3045
3046 END;
3047
3048
3049 /* Bug No : 3306112 - The new function will be called from view "pay_au_asg_element_payments_v"
3050 It return value of Hours in case the element_id passed is attached to the Salary Basis
3051 */
3052 FUNCTION get_salary_basis_hours
3053 (
3054 p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
3055 p_element_type_id in pay_element_entries_f.element_entry_id%TYPE,
3056 p_pay_bases_id in per_all_assignments_f.pay_basis_id%TYPE
3057 )
3058 RETURN NUMBER IS
3059
3060 l_element_type_id pay_element_entries_f.element_entry_id%TYPE;
3061 l_result number := NULL;
3062 l_procedure varchar2(80);
3063
3064 CURSOR Cr_value IS (
3065 SELECT prv.result_value
3066 from pay_run_results prr,
3067 pay_run_result_values prv,
3068 pay_element_types_f pet,
3069 pay_input_values_f piv
3070 where prr.assignment_action_id = p_assignment_action_id
3071 and prv.run_result_id = prr.run_result_id
3072 and prv.input_value_id = piv.input_value_id
3073 and prr.element_type_id = pet.element_type_id
3074 and piv.uom like 'H_%'
3075 and piv.element_type_id= pet.element_type_id
3076 and pet.element_name= 'Normal Hours');
3077
3078 CURSOR Cr_element_type_id IS (
3079 SELECT pivf.element_type_id /*Bug# 3665680*/
3080 FROM pay_input_values_f pivf, per_pay_bases ppb
3081 WHERE pivf.input_value_id = ppb.input_value_id
3082 AND ppb.pay_basis_id = p_pay_bases_id);
3083
3084 BEGIN
3085 g_debug := hr_utility.debug_enabled;
3086
3087 if g_debug then
3088 l_procedure :='pay_au_paye_ff.get_salary_basis_hours';
3089 hr_utility.set_location('Entering '||l_procedure,10);
3090 hr_utility.set_location('IN p_assignment_action_id '||p_assignment_action_id,20);
3091 hr_utility.set_location('IN p_element_type_id '||p_element_type_id,30);
3092 hr_utility.set_location('IN p_pay_bases_id '||p_pay_bases_id,40);
3093 end if ;
3094
3095 OPEN Cr_element_type_id;
3096 FETCH Cr_element_type_id INTO l_element_type_id;
3097 CLOSE Cr_element_type_id;
3098
3099 IF p_element_type_id = l_element_type_id THEN
3100 OPEN Cr_value;
3101 FETCH Cr_value INTO l_result;
3102 CLOSE Cr_value;
3103 END IF;
3104
3105 if g_debug then
3106 hr_utility.set_location('OUT l_result '||l_result,50);
3107 hr_utility.set_location('Exiting '||l_procedure,60);
3108 end if ;
3109
3110 RETURN l_result;
3111
3112
3113
3114 END;
3115
3116
3117 /* Bug No : 3245909 - To get prepayment locking action_id for AU_PAYMENTS route */
3118 function get_pp_action_id(p_action_type in varchar2,
3119 p_action_id in number
3120 ) return number
3121 is
3122 CURSOR Cr_action IS
3123 select INTLK.locking_action_id
3124 from pay_action_interlocks INTLK,
3125 pay_assignment_actions paa,
3126 pay_payroll_actions ppa
3127 where INTLK.locked_action_id = p_action_id
3128 and INTLK.locking_action_id = paa.assignment_action_id
3129 and paa.payroll_action_id = ppa.payroll_action_id
3130 and ppa.action_type in ('P', 'U')
3131 and paa.source_action_id is null;
3132
3133 l_action_id number;
3134 l_procedure varchar2(80);
3135
3136 begin
3137 g_debug := hr_utility.debug_enabled;
3138 if g_debug then
3139 l_procedure:='pay_au_paye_ff.get_pp_action_id';
3140 hr_utility.set_location('Entering '||l_procedure,10);
3141 hr_utility.set_location('IN p_action_type '||p_action_type,20);
3142 hr_utility.set_location('IN p_action_id '|| p_action_id,30);
3143 end if;
3144
3145 --
3146 if (p_action_type in ('P', 'U')) then
3147 l_action_id := p_action_id;
3148 elsif (p_action_type in ('R', 'Q','I','B')) then
3149 --
3150 -- Always return the master prepayment action.
3151 --
3152 OPEN Cr_action;
3153 FETCH Cr_action INTO l_action_id;
3154 CLOSE Cr_action;
3155 else
3156 l_action_id := null;
3157 end if;
3158
3159 if g_debug then
3160 hr_utility.set_location('OUT l_action_id '||l_action_id,40);
3161 hr_utility.set_location('Exiting '||l_procedure,50);
3162 end if;
3163 --
3164 return l_action_id;
3165 --
3166
3167 end get_pp_action_id;
3168
3169 /*Bug# 3935471
3170 The purpose of this function is to check whether child assignment action has the same tax unit id as compared
3171 to the master assignment action id. If the tax unit id is same it returns 'Y' else it returns 'N'.
3172 If the assignment action id passed to this function dosen't have a child then this function returns 'Y'.
3173 */
3174
3175 FUNCTION check_tax_unit_id
3176 (
3177 p_assignment_action_id in NUMBER,
3178 p_tax_unit_id IN NUMBER
3179 )
3180 RETURN VARCHAR2 IS
3181
3182 CURSOR c_get_master_tax_unit_id
3183 IS
3184 SELECT paa_master.tax_unit_id
3185 FROM pay_assignment_actions paa_child,
3186 pay_assignment_actions paa_master
3187 WHERE paa_child.assignment_action_id = p_assignment_action_id
3188 AND paa_master.assignment_action_id = paa_child.source_action_id;
3189
3190 l_flag VARCHAR2(10);
3191 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
3192 l_procedure varchar2(80);
3193
3194 BEGIN
3195 g_debug := hr_utility.debug_enabled;
3196 if g_debug then
3197 l_procedure :='pay_au_paye_ff.check_tax_unit_id';
3198 hr_utility.set_location('Entering '||l_procedure,10);
3199 hr_utility.set_location('IN p_assignment_action_id '||p_assignment_action_id,20);
3200 hr_utility.set_location('IN p_tax_unit_id '|| p_tax_unit_id,30);
3201 end if;
3202 OPEN c_get_master_tax_unit_id;
3203 FETCH c_get_master_tax_unit_id INTO l_tax_unit_id;
3204 IF c_get_master_tax_unit_id%NOTFOUND THEN
3205 l_flag := 'Y';
3206
3207 if g_debug then
3208 hr_utility.set_location('Return l_flag '||l_flag,40);
3209 hr_utility.set_location('Exiting '||l_procedure,50);
3210 end if;
3211 RETURN l_flag;
3212 ELSE
3213 IF l_tax_unit_id <> p_tax_unit_id THEN
3214 l_flag := 'N';
3215
3216 if g_debug then
3217 hr_utility.set_location('Return l_flag '||l_flag,40);
3218 hr_utility.set_location('Exiting '||l_procedure,50);
3219 end if;
3220
3221 RETURN l_flag;
3222 ELSE
3223 l_flag := 'Y';
3224
3225 if g_debug then
3226 hr_utility.set_location('Return l_flag '||l_flag,40);
3227 hr_utility.set_location('Exiting '||l_procedure,50);
3228 end if;
3229
3230 RETURN l_flag;
3231 END IF;
3232 END IF;
3233
3234 CLOSE c_get_master_tax_unit_id;
3235
3236 end check_tax_unit_id;
3237
3238 /* Bug#5934468 Function returns ths spread earning. This earning gets used in
3239 formula AU_HECS_DEDUCTION and AU_SFSS_DEDUCTION
3240 */
3241
3242 function get_spread_earning
3243 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3244 p_date_paid in date,
3245 p_pre_tax in number,
3246 p_spread_earning in number) return number is
3247
3248 cursor get_period_spread_over is
3249 select prv.RESULT_VALUE period_spread_over, prr.run_result_id, pee.creator_type
3250 from pay_element_types_f pet,
3251 pay_input_values_f piv,
3252 pay_run_result_values prv,
3253 pay_run_results prr,
3254 pay_element_entries_f pee
3255 where prr.assignment_action_id=p_assignment_action_id
3256 and prr.RUN_RESULT_ID = prv.RUN_RESULT_ID
3257 and prv.input_value_id = piv.input_value_id
3258 and piv.name ='Periods Spread Over'
3259 and piv.legislation_code='AU'
3260 and piv.element_type_id = pet.element_type_id
3261 and pet.legislation_code='AU'
3262 and pet.element_type_id = prr.element_type_id
3263 and pet.element_name='Spread Deduction'
3264 and prr.source_id = pee.element_entry_id
3265 and prr.status in ('P','PA') /* bug7665727 to ensure to pick up processed run results only */
3266 and pee.creator_type not in ('EE','RR')
3267 and p_date_paid between pet.effective_start_date and pet.effective_end_date
3268 and p_date_paid between piv.effective_start_date and piv.effective_end_date
3269 and p_date_paid between pee.effective_start_date and pee.effective_end_date;
3270
3271 cursor get_spread_earning(p_run_result_id pay_run_results.run_result_id%type) is
3272 select prv.RESULT_VALUE
3273 from pay_input_values_f piv,
3274 pay_run_result_values prv,
3275 pay_run_results prr
3276 where prr.RUN_RESULT_ID = p_run_result_id
3277 and prr.RUN_RESULT_ID = prv.RUN_RESULT_ID
3278 and prv.input_value_id = piv.input_value_id
3279 and piv.name ='Total Payment'
3280 and piv.legislation_code='AU'
3281 and p_date_paid between piv.effective_start_date and piv.effective_end_date;
3282
3283 /* new cursor for bug 6669058 */
3284 cursor get_retro_spread_earning is
3285 select nvl(sum(prv.RESULT_VALUE),0)
3286 from pay_element_types_f pet,
3287 pay_input_values_f piv,
3288 pay_run_result_values prv,
3289 pay_run_results prr,
3290 pay_element_entries_f pee
3291 where prr.assignment_action_id=p_assignment_action_id
3292 and prr.RUN_RESULT_ID = prv.RUN_RESULT_ID
3293 and prv.input_value_id = piv.input_value_id
3294 and piv.name ='Total Payment'
3295 and piv.legislation_code='AU'
3296 and piv.element_type_id = pet.element_type_id
3297 and pet.legislation_code='AU'
3298 and pet.element_type_id = prr.element_type_id
3299 and pet.element_name='Spread Deduction'
3300 and prr.source_id = pee.element_entry_id
3301 and pee.creator_type in ('EE','RR')
3302 and p_date_paid between pet.effective_start_date and pet.effective_end_date
3303 and p_date_paid between piv.effective_start_date and piv.effective_end_date
3304 and p_date_paid between pee.effective_start_date and pee.effective_end_date;
3305
3306 l_total_spread_earning number;
3307 l_spread_earning number;
3308 l_spread_percent number;
3309 l_retro_spread_earning number;
3310 l_spread_earning_total number;
3311
3312 l_procedure varchar2(80);
3313
3314 begin
3315
3316 g_debug := hr_utility.debug_enabled;
3317 if g_debug then
3318 l_procedure :='pay_au_paye_ff.get_spread_earning';
3319 hr_utility.set_location('Entering '||l_procedure,10);
3320 hr_utility.set_location('IN p_assignment_action_id '||p_assignment_action_id,20);
3321 hr_utility.set_location('IN p_date_paid '|| p_date_paid,20);
3322 hr_utility.set_location('IN p_pre_tax '|| p_pre_tax,20);
3323 hr_utility.set_location('IN p_spread_earning '|| p_spread_earning,20);
3324 end if;
3325
3326 l_total_spread_earning := 0;
3327 l_spread_earning := 0;
3328 l_retro_spread_earning := 0;
3329 l_spread_earning_total := 0;
3330
3331 if to_char(p_date_paid, 'dd/mm/yyyy') < '01/07/2009' then
3332
3333 /* Calculate spread earning for current period only - bug 6669058 */
3334 open get_retro_spread_earning;
3335 fetch get_retro_spread_earning into l_retro_spread_earning;
3336 close get_retro_spread_earning;
3337
3338 l_spread_earning_total := p_spread_earning -l_retro_spread_earning;
3339
3340 if l_spread_earning_total = 0 then
3341 return l_total_spread_earning;
3342 else
3343 for rec in get_period_spread_over
3344 loop
3345
3346 open get_spread_earning(rec.run_result_id);
3347 fetch get_spread_earning into l_spread_earning;
3348 close get_spread_earning;
3349
3350 l_spread_percent := l_spread_earning/l_spread_earning_total;
3351 l_spread_earning := (l_spread_earning- p_pre_tax * l_spread_percent )/rec.period_spread_over ;
3352 l_total_spread_earning := l_total_spread_earning + l_spread_earning;
3353
3354 end loop;
3355 end if;
3356
3357 else /* bug 7665727 - after 01-JUL-2009 */
3358
3359 l_spread_earning_total := p_spread_earning;
3360
3361 if l_spread_earning_total = 0 then
3362 return l_total_spread_earning;
3363 else
3364 for rec in get_period_spread_over
3365 loop
3366
3367 open get_spread_earning(rec.run_result_id);
3368 fetch get_spread_earning into l_spread_earning;
3369 close get_spread_earning;
3370
3371 l_spread_percent := l_spread_earning/l_spread_earning_total;
3372 l_spread_earning := (l_spread_earning- p_pre_tax * l_spread_percent )/rec.period_spread_over ;
3373 l_total_spread_earning := l_total_spread_earning + l_spread_earning;
3374
3375 end loop;
3376 end if;
3377
3378 end if;
3379
3380 if g_debug then
3381 hr_utility.set_location('OUT p_spread_earning '|| l_total_spread_earning,30);
3382 hr_utility.set_location('Leaving '||l_procedure,10);
3383 end if;
3384
3385
3386 return l_total_spread_earning;
3387 end;
3388
3389 /* new function for bug#6669058 */
3390 function get_retro_spread_earning
3391 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3392 p_date_paid in date,
3393 p_pre_tax in number,
3394 p_spread_earning in number) return number is
3395
3396 cursor get_retro_spread_earning is
3397 select nvl(sum(prv.RESULT_VALUE),0)
3398 from pay_element_types_f pet,
3399 pay_input_values_f piv,
3400 pay_run_result_values prv,
3401 pay_run_results prr,
3402 pay_element_entries_f pee
3403 where prr.assignment_action_id=p_assignment_action_id
3404 and prr.RUN_RESULT_ID = prv.RUN_RESULT_ID
3405 and prv.input_value_id = piv.input_value_id
3406 and piv.name ='Total Payment'
3407 and piv.legislation_code='AU'
3408 and piv.element_type_id = pet.element_type_id
3409 and pet.legislation_code='AU'
3410 and pet.element_type_id = prr.element_type_id
3411 and pet.element_name='Spread Deduction'
3412 and prr.source_id = pee.element_entry_id
3413 and pee.creator_type in ('EE','RR')
3414 and p_date_paid between pet.effective_start_date and pet.effective_end_date
3415 and p_date_paid between piv.effective_start_date and piv.effective_end_date
3416 and p_date_paid between pee.effective_start_date and pee.effective_end_date;
3417
3418 l_retro_spread_earning number;
3419
3420 l_procedure varchar2(80);
3421
3422 begin
3423
3424 g_debug := hr_utility.debug_enabled;
3425 if g_debug then
3426 l_procedure :='pay_au_paye_ff.get_retro_spread_earning';
3427 hr_utility.set_location('Entering '||l_procedure,10);
3428 hr_utility.set_location('IN p_assignment_action_id '||p_assignment_action_id,20);
3429 hr_utility.set_location('IN p_date_paid '|| p_date_paid,20);
3430 hr_utility.set_location('IN p_pre_tax '|| p_pre_tax,20);
3431 hr_utility.set_location('IN p_spread_earning '|| p_spread_earning,20);
3432 end if;
3433
3434 l_retro_spread_earning := 0;
3435
3436 open get_retro_spread_earning;
3437 fetch get_retro_spread_earning into l_retro_spread_earning;
3438 close get_retro_spread_earning;
3439
3440 if g_debug then
3441 hr_utility.set_location('OUT p_retro_spread_earning '|| l_retro_spread_earning,30);
3442 hr_utility.set_location('Leaving '||l_procedure,10);
3443 end if;
3444
3445
3446 return l_retro_spread_earning;
3447 end;
3448
3449 /* bug6809877 - Adeed new function get_etp_pay_component */
3450 function get_etp_pay_component
3451 ( p_assignment_id in per_all_assignments_f.assignment_id%type,
3452 p_date_earned in date) return varchar2 is
3453
3454 cursor etp_pay_csr ( c_assignment_id per_all_assignments_f.assignment_id%type,
3455 c_date_earned date) is
3456 select peev.screen_entry_value
3457 from pay_element_types_f pet,
3458 pay_input_values_f piv,
3459 pay_element_entries_f pee,
3460 pay_element_entry_values_f peev
3461 where pee.assignment_id = c_assignment_id
3462 and piv.name ='Pay ETP Components'
3463 and piv.legislation_code='AU'
3464 and piv.element_type_id = pet.element_type_id
3465 and pet.legislation_code='AU'
3466 and pet.element_name='ETP on Termination'
3467 and piv.input_value_id = peev.input_value_id
3468 and peev.element_entry_id = pee.element_entry_id
3469 and c_date_earned between pet.effective_start_date and pet.effective_end_date
3470 and c_date_earned between piv.effective_start_date and piv.effective_end_date
3471 and c_date_earned between pee.effective_start_date and pee.effective_end_date
3472 and c_date_earned between peev.effective_start_date and peev.effective_end_date
3473 and rownum = 1;
3474
3475 l_etp_pay pay_element_entry_values_f.screen_entry_value%type;
3476
3477 begin
3478
3479 open etp_pay_csr (p_assignment_id, p_date_earned);
3480 fetch etp_pay_csr into l_etp_pay;
3481 if etp_pay_csr%notfound then
3482 l_etp_pay := 'zzz';
3483 end if;
3484 close etp_pay_csr;
3485
3486 return l_etp_pay;
3487
3488 end;
3489
3490 /*bug8406009 - calc_average_earnings function to calculate the average earnings.*/
3491 FUNCTION calc_average_earnings
3492 (p_assignment_id IN pay_assignment_actions.assignment_id%TYPE
3493 ,p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
3494 ,p_payroll_id IN pay_payroll_actions.payroll_id%TYPE
3495 ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
3496 ,p_business_group_id IN per_business_groups.business_group_id%TYPE
3497 ,p_date_earned IN pay_payroll_actions.date_earned%TYPE
3498 ,p_period_start_date IN DATE
3499 ,p_emp_hire_date IN DATE
3500 ,p_earnings_std_ytd IN NUMBER
3501 ,p_earnings_std_ptd IN NUMBER
3502 ,p_taxable_value IN NUMBER
3503 ,p_average_earnings OUT NOCOPY NUMBER
3504 ,p_case OUT NOCOPY VARCHAR2)
3505 RETURN NUMBER
3506 AS
3507
3508 l_procedure VARCHAR2(80);
3509 l_periods NUMBER;
3510 l_average_earnings NUMBER;
3511 l_check_tax_unit VARCHAR2(10);
3512 l_total_avg_earn NUMBER;
3513 l_case VARCHAR2(80);
3514 l_error VARCHAR2(80);
3515
3516 l_prev_earnings_standard NUMBER;
3517 l_prev_pre_tax_spread NUMBER;
3518 l_prev_pre_tax_fixed NUMBER;
3519 l_prev_pre_tax_prog NUMBER;
3520 l_prev_paid_periods NUMBER;
3521
3522 l_dummy NUMBER;
3523
3524 /* bug11681524 retrieving original average earnings used for retro spread calculation */
3525
3526 CURSOR check_retro_run(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
3527 IS
3528 SELECT COUNT(*)
3529 FROM pay_assignment_actions paa
3530 WHERE paa.assignment_action_id = c_assignment_action_id
3531 AND paa.action_status = 'B';
3532
3533
3534 CURSOR check_avg_earnings(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
3535 IS
3536 select prv.result_value
3537 from pay_run_results prr,
3538 pay_run_result_values prv,
3539 pay_element_types_f pet,
3540 pay_input_values_f piv
3541 where prr.assignment_action_id = c_assignment_action_id
3542 and prr.element_type_id = pet.element_type_id
3543 and pet.element_name = 'AU Average Earnings'
3544 and pet.legislation_code = 'AU'
3545 and pet.element_type_id = piv.element_type_id
3546 and piv.name = 'Spread'
3547 and piv.input_value_id = prv.input_value_id
3548 and prv.run_result_id = prr.run_result_id
3549 and prr.status = 'B';
3550
3551 CURSOR c_get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
3552 IS
3553 SELECT ppa.effective_date
3554 FROM pay_payroll_actions ppa
3555 ,pay_assignment_actions paa
3556 WHERE paa.assignment_action_id = c_assignment_action_id
3557 AND paa.payroll_action_id = ppa.payroll_action_id;
3558
3559 l_retro_exists NUMBER;
3560 l_avg_earn_rr number;
3561 l_pay_effective_date DATE;
3562
3563 BEGIN
3564
3565 g_debug := hr_utility.debug_enabled;
3566 l_case := NULL;
3567 l_average_earnings := 0;
3568
3569 IF g_debug
3570 THEN
3571 l_procedure := 'pay_au_paye_ff.calc_average_earnings';
3572 hr_utility.set_location('Entering procedure '||l_procedure,1000);
3573 hr_utility.set_location('p_assignment_id '||p_assignment_id,1000);
3574 hr_utility.set_location('p_assignment_action_id '||p_assignment_action_id,1000);
3575 hr_utility.set_location('p_payroll_id '||p_payroll_id,1000);
3576 hr_utility.set_location('p_tax_unit_id '||p_tax_unit_id,1000);
3577 hr_utility.set_location('p_earnings_std_ytd '||p_earnings_std_ytd,1000);
3578 hr_utility.set_location('p_earnings_std_ptd '||p_earnings_std_ptd,1000);
3579 hr_utility.set_location('p_taxable_value '||p_taxable_value,1000);
3580 END IF;
3581
3582 l_periods := pay_au_paye_ff.periods_since_hire_date(p_payroll_id
3583 ,p_assignment_id
3584 ,p_tax_unit_id
3585 ,p_assignment_action_id
3586 ,1 /* p_period_num defaulted to 1 */
3587 ,p_period_start_date
3588 ,p_emp_hire_date
3589 ,'Y');
3590
3591 IF ((l_periods <= 1) OR (p_earnings_std_ytd = p_earnings_std_ptd))
3592 THEN
3593 l_average_earnings := 0;
3594 ELSE
3595 l_check_tax_unit := pay_au_paye_ff.check_tax_unit_id(p_assignment_action_id,p_tax_unit_id);
3596 IF (l_check_tax_unit = 'Y')
3597 THEN
3598 l_periods := l_periods - 1;
3599 END IF;
3600 l_average_earnings := (p_earnings_std_ytd - p_earnings_std_ptd)/l_periods;
3601 END IF;
3602
3603 /* bug11681524 retrieving original average earnings used for retro spread calculation */
3604 OPEN check_retro_run(p_assignment_action_id);
3605 FETCH check_retro_run INTO l_retro_exists;
3606 CLOSE check_retro_run;
3607
3608 IF (l_retro_exists <> 0)
3609 THEN
3610
3611 OPEN c_get_effective_date(p_assignment_action_id);
3612 FETCH c_get_effective_date INTO l_pay_effective_date;
3613 CLOSE c_get_effective_date;
3614
3615 IF g_debug THEN
3616 hr_utility.set_location('Pay Effective Date =>'||to_char(l_pay_effective_date,'dd/mm/yyyy'),1000);
3617 END IF;
3618
3619
3620 IF to_char(l_pay_effective_date,'dd/mm/yyyy') >= '01/07/2011' THEN
3621
3622 OPEN check_avg_earnings(p_assignment_action_id);
3623 FETCH check_avg_earnings INTO l_avg_earn_rr;
3624 IF check_avg_earnings%FOUND THEN
3625 l_average_earnings := l_avg_earn_rr;
3626
3627 IF g_debug THEN
3628 hr_utility.set_location('Point 0 ',1100);
3629 hr_utility.set_location('l_average_earnings '||l_average_earnings,1100);
3630 END IF;
3631
3632 END IF;
3633 CLOSE check_avg_earnings;
3634
3635 END IF;
3636 END IF;
3637
3638
3639 IF g_debug
3640 THEN
3641 hr_utility.set_location('Point 1 ',1100);
3642 hr_utility.set_location('l_average_earnings '||l_average_earnings,1100);
3643 hr_utility.set_location('l_periods '||l_periods,1100);
3644 END IF;
3645
3646 l_total_avg_earn := l_average_earnings + p_taxable_value;
3647 IF ( (l_total_avg_earn < 0) OR l_average_earnings = 0)
3648 THEN
3649
3650 l_dummy := pay_au_paye_ff.calculate_asg_prev_value
3651 (p_assignment_id
3652 ,p_business_group_id
3653 ,p_date_earned
3654 ,p_tax_unit_id
3655 ,p_assignment_action_id
3656 ,p_payroll_id
3657 ,p_period_start_date
3658 ,l_case
3659 ,l_prev_earnings_standard
3660 ,l_prev_pre_tax_spread
3661 ,l_prev_pre_tax_fixed
3662 ,l_prev_pre_tax_prog
3663 ,l_prev_paid_periods
3664 ,'Y');
3665
3666 IF (l_prev_paid_periods > 0)
3667 THEN
3668 l_average_earnings := (l_prev_earnings_standard + l_prev_pre_tax_spread + l_prev_pre_tax_fixed + l_prev_pre_tax_prog)/l_prev_paid_periods;
3669 END IF;
3670 END IF;
3671
3672 IF g_debug
3673 THEN
3674 hr_utility.set_location('Point 2 ',1100);
3675 hr_utility.set_location('l_average_earnings '||l_average_earnings,1200);
3676 hr_utility.set_location('l_prev_earnings_standard '||l_prev_earnings_standard,1200);
3677 hr_utility.set_location('l_prev_paid_periods '||l_prev_paid_periods,1200);
3678 END IF;
3679
3680 p_average_earnings := l_average_earnings;
3681 p_case := l_case;
3682
3683 IF g_debug
3684 THEN
3685 hr_utility.set_location('p_average_earnings '||p_average_earnings,1300);
3686 hr_utility.set_location('p_case '||p_case,1300);
3687 hr_utility.set_location('Leaving function '||l_procedure,1300);
3688 END IF;
3689
3690 RETURN (1);
3691
3692 END calc_average_earnings;
3693
3694 /*bug8406009 - calc_lt12_prev_spread_tax function is added for retro spread deduction in previous financial year less than 12 months*/
3695 FUNCTION calc_lt12_prev_spread_tax
3696 (p_assignment_id IN pay_assignment_actions.assignment_id%TYPE
3697 ,p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
3698 ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
3699 ,p_date_earned IN pay_payroll_actions.date_earned%TYPE
3700 ,p_business_group_id IN pay_payroll_actions.business_group_id%TYPE
3701 ,p_average_earnings IN NUMBER
3702 ,p_tax_scale IN VARCHAR2
3703 ,p_period_frequency IN NUMBER
3704 ,p_spread_tax OUT NOCOPY NUMBER
3705 ,p_fl_earnings in number default 0
3706 ,p_flood_levy out nocopy number
3707 )
3708 RETURN VARCHAR2
3709 IS
3710 CURSOR get_retro_spread_entries
3711 (c_assignment_id IN pay_assignment_actions.assignment_id%TYPE
3712 ,c_date_earned IN pay_payroll_actions.date_earned%TYPE)
3713 IS
3714 SELECT pee.element_entry_id,
3715 peev.screen_entry_value retro_amount,
3716 pee.creator_type
3717 FROM pay_element_entries_f pee,
3718 pay_element_entry_values_f peev,
3719 pay_element_links_f pelf,
3720 pay_element_types_f pet,
3721 pay_element_classifications pec,
3722 pay_input_values_f piv,
3723 pay_sub_classification_rules_f psc,
3724 pay_element_classifications pec2
3725 WHERE pee.assignment_id = c_assignment_id
3726 AND pee.creator_type IN ('EE','RR')
3727 AND c_date_earned BETWEEN pee.effective_start_date AND pee.effective_end_date
3728 AND pelf.element_link_id = pee.element_link_id
3729 AND c_date_earned BETWEEN pelf.effective_start_date AND pelf.effective_end_date
3730 AND pet.element_type_id = pelf.element_type_id
3731 AND c_date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date
3732 AND pec.classification_id = pet.classification_id
3733 AND pec.classification_name = 'Earnings'
3734 AND pet.element_type_id = psc.element_type_id
3735 AND psc.classification_id = pec2.classification_id
3736 AND pec2.classification_name = 'Spread'
3737 AND peev.element_entry_id = pee.element_entry_id
3738 AND c_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
3739 AND peev.input_value_id = piv.input_value_id
3740 AND c_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date
3741 AND piv.name = 'Pay Value';
3742
3743
3744 CURSOR get_spread_over_ee
3745 (c_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
3746 ,c_assignment_id IN pay_assignment_actions.assignment_id%TYPE
3747 ,c_date_earned IN DATE )
3748 IS
3749 SELECT peev.screen_entry_value
3750 FROM pay_element_entries_f pee
3751 ,pay_element_entry_values_f peev
3752 ,pay_input_values_f pivf
3753 WHERE pee.element_entry_id = c_element_entry_id
3754 AND pee.assignment_id = c_assignment_id
3755 AND pee.creator_type = 'EE'
3756 AND pee.element_entry_id = peev.element_entry_id
3757 AND pee.element_type_id = pivf.element_type_id
3758 AND pivf.name = 'Periods Spread Over'
3759 AND peev.input_value_id = pivf.input_value_id
3760 AND c_date_earned BETWEEN pee.effective_start_date AND pee.effective_end_date
3761 AND c_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
3762 AND c_date_earned BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
3763
3764 CURSOR get_spread_over_rr
3765 (c_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
3766 ,c_assignment_id IN pay_assignment_actions.assignment_id%TYPE
3767 ,c_date_earned IN DATE )
3768 IS
3769 SELECT prrv.result_value
3770 FROM pay_run_results prr
3771 ,pay_run_result_values prrv
3772 ,pay_input_values_f pivf
3773 ,pay_element_entries_f pee
3774 WHERE pee.element_entry_id = c_element_entry_id
3775 AND pee.assignment_id = c_assignment_id
3776 AND pee.creator_type = 'RR'
3777 AND prr.run_result_id = pee.source_id
3778 AND prr.run_result_id = prrv.run_result_id
3779 AND prr.assignment_action_id = pee.source_asg_action_id
3780 AND prrv.input_value_id = pivf.input_value_id
3781 AND pivf.name = 'Periods Spread Over'
3782 AND c_date_earned BETWEEN pee.effective_start_date and pee.effective_end_date;
3783
3784 CURSOR get_legislation_rule
3785 IS
3786 SELECT plr.rule_mode
3787 FROM pay_legislation_rules plr
3788 WHERE plr.legislation_code = 'AU'
3789 AND plr.rule_type ='ADVANCED_RETRO';
3790
3791
3792 CURSOR c_get_effective_date(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
3793 IS
3794 SELECT ppa.effective_date
3795 FROM pay_payroll_actions ppa
3796 ,pay_assignment_actions paa
3797 WHERE paa.assignment_action_id = c_assignment_action_id
3798 AND paa.payroll_action_id = ppa.payroll_action_id;
3799
3800
3801 l_procedure VARCHAR2(80);
3802 l_adv_retro_flag VARCHAR2(10);
3803 l_retro_start_date DATE;
3804 l_retro_end_date DATE;
3805 l_dummy VARCHAR2(1000) := 'ZZZ';
3806 l_pay_effective_date DATE;
3807 l_eff_date_yr_start DATE;
3808 l_retro_eff_date DATE;
3809 l_orig_effective_date DATE;
3810 l_time_span VARCHAR2(80);
3811 l_retro_type VARCHAR2(80);
3812 l_weekly_earnings NUMBER;
3813 l_period_earnings NUMBER;
3814
3815 l_avg_tax NUMBER;
3816 l_spread_tax NUMBER;
3817 l_spred_tax_period NUMBER;
3818 l_total_spread_tax NUMBER;
3819
3820
3821 TYPE num_tab_type IS TABLE OF NUMBER INDEX BY binary_integer;
3822 TYPE char_tab_type IS TABLE OF VARCHAR2(10) INDEX BY binary_integer;
3823
3824 l_element_entry_tab num_tab_type;
3825 l_entry_value_tab num_tab_type;
3826 l_creator_type_tab char_tab_type;
3827
3828 i_index NUMBER;
3829 l_spread_over NUMBER;
3830 l_a1 NUMBER;
3831 l_b1 NUMBER;
3832 l_a2 NUMBER;
3833 l_b2 NUMBER;
3834
3835 /* bug 12989692 flood levy */
3836 l_weekly_fl_earnings number;
3837 l_combined_fl_earnings number;
3838 l_combined_weekly_fl_earnings number;
3839 l_normal_fl_ded number;
3840 l_combined_fl_ded number;
3841 l_flood_levy_period number;
3842 l_total_flood_levy number;
3843
3844 BEGIN
3845
3846 g_debug := hr_utility.debug_enabled;
3847
3848 IF g_debug
3849 THEN
3850 l_procedure := 'pay_au_paye_ff.calc_lt12_prev_spread_tax';
3851 hr_utility.set_location('Entering procedure '||l_procedure,1000);
3852 hr_utility.set_location('p_assignment_id '||p_assignment_id,1000);
3853 hr_utility.set_location('p_assignment_action_id '||p_assignment_action_id,1000);
3854 hr_utility.set_location('p_date_earned '||p_date_earned,1000);
3855 hr_utility.set_location('p_average_earnings '||p_average_earnings,1000);
3856 hr_utility.set_location('p_fl_earnings '||p_fl_earnings,1000);
3857 END IF;
3858
3859
3860
3861 OPEN get_legislation_rule;
3862 FETCH get_legislation_rule INTO l_adv_retro_flag;
3863 IF get_legislation_rule%NOTFOUND THEN
3864 l_adv_retro_flag := 'N';
3865 END IF;
3866 CLOSE get_legislation_rule;
3867
3868 OPEN c_get_effective_date(p_assignment_action_id);
3869 FETCH c_get_effective_date INTO l_pay_effective_date;
3870 CLOSE c_get_effective_date;
3871
3872 l_eff_date_yr_start := hr_au_routes.span_start(l_pay_effective_date,1,'01-07');
3873
3874 IF g_debug THEN
3875 hr_utility.set_location('Enhanced Retropay Rule Value =>'||l_adv_retro_flag,70);
3876 END IF;
3877
3878 i_index := 0;
3879
3880 FOR csr_rec IN get_retro_spread_entries(p_assignment_id,p_date_earned)
3881 LOOP
3882
3883 IF (l_adv_retro_flag = 'N')
3884 THEN
3885 /* Retropay by element scenario */
3886 l_dummy := pay_au_paye_ff.get_retro_period
3887 (csr_rec.element_entry_id
3888 ,p_date_earned
3889 ,p_tax_unit_id
3890 ,l_retro_start_date
3891 ,l_retro_end_date);
3892
3893 IF (months_between(l_pay_effective_date,l_retro_end_date) <= 12
3894 AND l_pay_effective_date >= l_eff_date_yr_start
3895 AND l_retro_end_date < l_eff_date_yr_start)
3896 THEN
3897 l_element_entry_tab(i_index) := csr_rec.element_entry_id;
3898 l_entry_value_tab(i_index) := csr_rec.retro_amount;
3899 l_creator_type_tab(i_index) := csr_rec.creator_type;
3900 i_index := i_index + 1;
3901 END IF;
3902 ELSE
3903 /* Enhanced Retropay Scenario */
3904 l_dummy := pay_au_paye_ff.get_retro_time_span
3905 (
3906 p_element_entry_id => csr_rec.element_entry_id,
3907 p_date_earned => p_date_earned,
3908 p_tax_unit_id => p_tax_unit_id,
3909 p_retro_start_date => l_retro_start_date,
3910 p_retro_end_date => l_retro_end_date,
3911 p_orig_effective_date => l_orig_effective_date,
3912 p_retro_effective_date => l_retro_eff_date,
3913 p_time_span => l_time_span,
3914 p_retro_type => l_retro_type
3915 );
3916
3917 IF l_time_span = 'LT12 Prev'
3918 THEN
3919 l_element_entry_tab(i_index) := csr_rec.element_entry_id;
3920 l_entry_value_tab(i_index) := csr_rec.retro_amount;
3921 l_creator_type_tab(i_index) := csr_rec.creator_type;
3922 i_index := i_index + 1;
3923 END IF;
3924 END IF;
3925
3926 END LOOP;
3927
3928 IF (l_element_entry_tab.COUNT > 0)
3929 THEN
3930 l_total_spread_tax := 0;
3931 l_weekly_earnings := pay_au_paye_ff.convert_to_week(p_period_frequency,p_average_earnings);
3932 l_dummy := pay_au_paye_ff.get_table_value
3933 (p_business_group_id
3934 ,'Average Earnings'
3935 ,'TAX_SCALE_'||p_tax_scale
3936 ,l_weekly_earnings
3937 ,l_pay_effective_date
3938 ,l_a1
3939 ,l_b1);
3940 IF l_dummy <> 'ZZZ' THEN
3941 return l_dummy;
3942 END IF;
3943
3944 l_avg_tax := pay_au_paye_ff.calculate_tax
3945 (p_date_earned
3946 ,p_average_earnings
3947 ,p_period_frequency
3948 ,p_tax_scale
3949 ,l_a1
3950 ,l_b1);
3951
3952 IF g_debug THEN
3953 hr_utility.set_location('l_avg_tax '||l_avg_tax,1001);
3954 END IF;
3955
3956 /* bug12989692 - Flood Levy */
3957 IF p_fl_earnings <> 0 THEN
3958
3959 l_total_flood_levy := 0;
3960 l_weekly_fl_earnings := pay_au_paye_ff.convert_to_week(p_period_frequency,p_fl_earnings);
3961
3962 if p_tax_scale = 1 then
3963 l_a1 := hruserdt.get_table_value (p_business_group_id, 'FLOOD_LEVY_NO_THRESHOLD', 'FLOOD_LEVY_A', TO_CHAR(l_weekly_fl_earnings), l_pay_effective_date);
3964 l_b1 := hruserdt.get_table_value (p_business_group_id, 'FLOOD_LEVY_NO_THRESHOLD', 'FLOOD_LEVY_B', TO_CHAR(l_weekly_fl_earnings), l_pay_effective_date);
3965 else
3966 l_a1 := hruserdt.get_table_value (p_business_group_id, 'FLOOD_LEVY_THRESHOLD', 'FLOOD_LEVY_A', TO_CHAR(l_weekly_fl_earnings), l_pay_effective_date);
3967 l_b1 := hruserdt.get_table_value (p_business_group_id, 'FLOOD_LEVY_THRESHOLD', 'FLOOD_LEVY_B', TO_CHAR(l_weekly_fl_earnings), l_pay_effective_date);
3968 end if;
3969
3970 l_normal_fl_ded := pay_au_paye_ff.calculate_tax
3971 (p_date_earned
3972 ,p_fl_earnings
3973 ,p_period_frequency
3974 ,p_tax_scale
3975 ,l_a1
3976 ,l_b1);
3977
3978 IF g_debug THEN
3979 hr_utility.set_location('l_normal_fl_ded '||l_normal_fl_ded,1001);
3980 END IF;
3981
3982 END IF;
3983
3984 FOR i IN l_element_entry_tab.FIRST..l_element_entry_tab.LAST
3985 LOOP
3986 IF l_creator_type_tab(i) ='EE'
3987 THEN
3988 /* New entry comes from an Inserted entry, get the
3989 Periods spread over from retro entry
3990 Assumption: The retro entry has the input 'Periods Spread Over' */
3991 OPEN get_spread_over_ee(l_element_entry_tab(i)
3992 ,p_assignment_id
3993 ,p_date_earned);
3994 FETCH get_spread_over_ee INTO l_spread_over;
3995 CLOSE get_spread_over_ee;
3996 ELSE /* Creator Case 'RR' - fetch periods from run results */
3997 OPEN get_spread_over_rr(l_element_entry_tab(i)
3998 ,p_assignment_id
3999 ,p_date_earned);
4000 FETCH get_spread_over_rr INTO l_spread_over;
4001 CLOSE get_spread_over_rr;
4002 END IF;
4003
4004 IF l_spread_over = 0
4005 THEN
4006 l_spread_over := 1; /* Avoid Divide by 0 errors. If we do not find any periods, we tax them as paid in a period */
4007 END IF;
4008 l_period_earnings := p_average_earnings + TRUNC(l_entry_value_tab(i)/l_spread_over);
4009 l_weekly_earnings := pay_au_paye_ff.convert_to_week(p_period_frequency,l_period_earnings);
4010 l_dummy := pay_au_paye_ff.get_table_value
4011 (p_business_group_id
4012 ,'Total Average Earnings'
4013 ,'TAX_SCALE_'||p_tax_scale
4014 ,l_weekly_earnings
4015 ,l_pay_effective_date
4016 ,l_a2
4017 ,l_b2);
4018
4019 IF l_dummy <> 'ZZZ' THEN
4020 return l_dummy;
4021 END IF;
4022
4023 l_spread_tax := pay_au_paye_ff.calculate_tax
4024 (p_date_earned
4025 ,l_period_earnings
4026 ,p_period_frequency
4027 ,p_tax_scale
4028 ,l_a2
4029 ,l_b2);
4030 l_spred_tax_period := (l_spread_tax - l_avg_tax) * l_spread_over;
4031 l_total_spread_tax := l_total_spread_tax + l_spred_tax_period;
4032
4033 /* bug12989692 Flood Levy */
4034 IF p_fl_earnings <> 0 THEN
4035
4036 l_combined_fl_earnings := p_fl_earnings + TRUNC(l_entry_value_tab(i)/l_spread_over);
4037 l_combined_weekly_fl_earnings := pay_au_paye_ff.convert_to_week(p_period_frequency,l_combined_fl_earnings);
4038
4039 if p_tax_scale = 1 then
4040 l_a2 := hruserdt.get_table_value (p_business_group_id, 'FLOOD_LEVY_NO_THRESHOLD', 'FLOOD_LEVY_A', TO_CHAR(l_combined_weekly_fl_earnings), l_pay_effective_date);
4041 l_b2 := hruserdt.get_table_value (p_business_group_id, 'FLOOD_LEVY_NO_THRESHOLD', 'FLOOD_LEVY_B', TO_CHAR(l_combined_weekly_fl_earnings), l_pay_effective_date);
4042 else
4043 l_a2 := hruserdt.get_table_value (p_business_group_id, 'FLOOD_LEVY_THRESHOLD', 'FLOOD_LEVY_A', TO_CHAR(l_combined_weekly_fl_earnings), l_pay_effective_date);
4044 l_b2 := hruserdt.get_table_value (p_business_group_id, 'FLOOD_LEVY_THRESHOLD', 'FLOOD_LEVY_B', TO_CHAR(l_combined_weekly_fl_earnings), l_pay_effective_date);
4045 end if;
4046
4047 l_combined_fl_ded := pay_au_paye_ff.calculate_tax
4048 (p_date_earned
4049 ,l_combined_fl_earnings
4050 ,p_period_frequency
4051 ,p_tax_scale
4052 ,l_a2
4053 ,l_b2);
4054
4055 l_flood_levy_period := (l_combined_fl_ded - l_normal_fl_ded) * l_spread_over;
4056 l_total_flood_levy := l_total_flood_levy + l_flood_levy_period;
4057
4058 END IF;
4059
4060 IF g_debug
4061 THEN
4062 hr_utility.set_location('Element Entry ID '||l_element_entry_tab(i),1000);
4063 hr_utility.set_location('Entry Value '||l_entry_value_tab(i),1000);
4064 hr_utility.set_location('Creator Type '||l_creator_type_tab(i),1000);
4065 hr_utility.set_location('Periods Spread Over '||l_spread_over,1000);
4066 hr_utility.set_location('l_period_earnings '||l_period_earnings,1000);
4067 hr_utility.set_location('l_spread_tax '||l_spread_tax,1000);
4068 hr_utility.set_location('l_spred_tax_period '||l_spred_tax_period,1000);
4069 hr_utility.set_location('l_combined_fl_ded '||l_combined_fl_ded,1000);
4070 hr_utility.set_location('l_flood_levy_period '||l_flood_levy_period,1000);
4071 END IF;
4072 END LOOP;
4073 END IF;
4074
4075 p_spread_tax := l_total_spread_tax;
4076 p_flood_levy := l_total_flood_levy;
4077
4078 IF g_debug
4079 THEN
4080 hr_utility.set_location('p_spread_tax '||p_spread_tax,1200);
4081 hr_utility.set_location('p_flood_levy '||p_flood_levy,1200);
4082 hr_utility.set_location('Leaving function '||l_procedure,1200);
4083 END IF;
4084
4085 return l_dummy;
4086
4087 END calc_lt12_prev_spread_tax;
4088
4089 /* Bug 8765082 - Functions added for Retropay Leave Loading Taxation
4090 Function : get_retro_leave_load
4091 Description : This function is to be used during a Retropay recalculation run.
4092 AU has RETRO_DELETE legislation rule enabled, therefore all results are marked
4093 with status 'B' during Retropay recalculation.
4094 This function checks if any Retro Leave Loading GT12, LT12 Prev have been processed
4095 until now and if so, adjust it against the Retro Tax Free annual amount ($320)
4096 Inputs : p_assignment_action_id - Assignment Action ID
4097 p_tax_unit_id - Tax Unit ID
4098 Outputs : p_retro_adj_leave_load - Retro Leave Loading Amount
4099 */
4100
4101 FUNCTION get_retro_leave_load
4102 (p_assignment_action_id IN NUMBER
4103 ,p_tax_unit_id IN NUMBER
4104 ,p_retro_adj_leave_load OUT NOCOPY NUMBER)
4105 RETURN NUMBER
4106 AS
4107 /* Cursor replicates RR Route for _ASG_LE_YTD with status = 'B' check added */
4108 CURSOR get_retro_ytd_results(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE
4109 ,c_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE
4110 ,c_balance_type_id pay_balance_types.balance_type_id%TYPE
4111 )
4112 IS
4113 SELECT SUM(NVL(TARGET.result_value,0))
4114 FROM
4115 pay_balance_feeds_f FEED
4116 , pay_run_result_values TARGET
4117 , pay_run_results prr
4118 , pay_payroll_actions ppa
4119 , pay_assignment_actions paa
4120 , pay_payroll_actions ppas
4121 , pay_assignment_actions paas
4122 where paas.assignment_action_id = c_assignment_action_id
4123 and paas.payroll_action_id = ppas.payroll_action_id
4124 and FEED.input_value_id = TARGET.input_value_id
4125 and TARGET.run_result_id = prr.run_result_id
4126 and nvl(TARGET.result_value,'0') <> '0'
4127 and prr.assignment_action_id = paa.assignment_action_id
4128 and paa.payroll_action_id = ppa.payroll_action_id
4129 and ppa.effective_date between FEED.effective_start_date and FEED.effective_end_date
4130 and prr.status = 'B'
4131 and ppa.effective_date >= hr_au_routes.span_start(ppas.effective_date, 1, '01-07-')
4132 and paa.action_sequence <= paas.action_sequence
4133 and paa.assignment_id = paas.assignment_id
4134 and paa.tax_unit_id = c_tax_unit_id
4135 and FEED.balance_type_id = c_balance_type_id ;
4136
4137 CURSOR c_get_balance_details
4138 IS
4139 SELECT PBT.balance_name
4140 ,PBT.balance_type_id
4141 FROM pay_balance_types PBT
4142 WHERE PBT.balance_name IN ('Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount'
4143 ,'Retro Earnings Leave Loading GT 12 Mths Amount')
4144 AND PBT.legislation_code ='AU';
4145
4146 CURSOR check_retro_run(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
4147 IS
4148 SELECT COUNT(*)
4149 FROM pay_assignment_actions paa
4150 WHERE paa.assignment_action_id = c_assignment_action_id
4151 AND paa.action_status = 'B';
4152
4153 l_adj_retro_value NUMBER;
4154 l_fetched_value NUMBER;
4155 l_gen NUMBER;
4156 l_exists NUMBER;
4157 l_procedure VARCHAR2(80);
4158
4159
4160 BEGIN
4161
4162 g_debug := hr_utility.debug_enabled;
4163
4164 IF g_debug
4165 THEN
4166 l_procedure := 'pay_au_paye_ff.get_retro_leave_load';
4167 hr_utility.set_location('Entering procedure '||l_procedure,1000);
4168 hr_utility.set_location('IN p_assignment_action_id '||p_assignment_action_id,1000);
4169 hr_utility.set_location('IN p_tax_unit_id '||p_tax_unit_id,1000);
4170 END IF;
4171
4172 l_adj_retro_value := 0;
4173
4174 /* Since we are running the data intensive Run Result route, confirm this is a Retro Run */
4175 OPEN check_retro_run(p_assignment_action_id);
4176 FETCH check_retro_run INTO l_exists;
4177 CLOSE check_retro_run;
4178
4179 IF l_exists <> 0
4180 THEN
4181
4182 FOR csr_rec IN c_get_balance_details
4183 LOOP
4184
4185 OPEN get_retro_ytd_results(p_assignment_action_id
4186 ,p_tax_unit_id
4187 ,csr_rec.balance_type_id
4188 );
4189 FETCH get_retro_ytd_results INTO l_fetched_value;
4190 CLOSE get_retro_ytd_results;
4191
4192 IF g_debug THEN
4193 hr_utility.set_location('Balance Name '||csr_rec.balance_name,1000);
4194 hr_utility.set_location('l_fetched_value '||l_fetched_value,1000);
4195 END IF;
4196
4197 l_adj_retro_value := l_adj_retro_value + NVL(l_fetched_value,0);
4198 END LOOP;
4199 END IF;
4200
4201 p_retro_adj_leave_load := l_adj_retro_value;
4202
4203 IF g_debug THEN
4204 hr_utility.set_location('p_retro_adj_leave_load '||p_retro_adj_leave_load,1000);
4205 hr_utility.set_location('Leaving procedure '||l_procedure,1000);
4206 END IF;
4207
4208 RETURN 0;
4209
4210 END get_retro_leave_load;
4211
4212 /* bug 12873456 - AU_GET_RETRO_TAXVAR_FIXED formula function in AU_SPREAD_DEDUCTION formula for fixed tax_variation_type = 'Y' with bonus 'Y' */
4213 function get_retro_tax_var_fixed
4214 (p_assignment_id number,
4215 p_assignment_action_id number,
4216 p_date_earned date,
4217 p_mode varchar2,
4218 p_retro out nocopy varchar2)
4219 return number IS
4220
4221 cursor get_retro_spread is
4222 select prv.result_value
4223 from pay_run_results prr,
4224 pay_run_result_values prv,
4225 pay_input_values_f piv,
4226 pay_element_types_f pet
4227 where prr.assignment_action_id = p_assignment_action_id
4228 and prr.run_result_id = prv.run_result_id
4229 and prv.input_value_id = piv.input_value_id
4230 and piv.name = 'Spread'
4231 and pet.element_name = 'Retropay Tax Variation Amount'
4232 and pet.element_type_id = piv.element_type_id
4233 and prr.element_type_id = piv.element_type_id
4234 and pet.element_type_id = prr.element_type_id
4235 and p_date_earned between pet.effective_start_date and pet.effective_end_date
4236 and p_date_earned between piv.effective_start_date and piv.effective_end_date ;
4237
4238 CURSOR check_retro_run
4239 IS
4240 SELECT COUNT(*)
4241 FROM pay_assignment_actions paa
4242 WHERE paa.assignment_action_id = p_assignment_action_id
4243 AND paa.action_status = 'B';
4244
4245 cursor get_retro_total is
4246 select prv.result_value
4247 from pay_run_results prr,
4248 pay_run_result_values prv,
4249 pay_input_values_f piv,
4250 pay_element_types_f pet
4251 where prr.assignment_action_id = p_assignment_action_id
4252 and prr.run_result_id = prv.run_result_id
4253 and prv.input_value_id = piv.input_value_id
4254 and piv.name = 'Total'
4255 and pet.element_name = 'Retropay Tax Variation Amount'
4256 and prr.status = 'B'
4257 and pet.element_type_id = piv.element_type_id
4258 and prr.element_type_id = piv.element_type_id
4259 and pet.element_type_id = prr.element_type_id
4260 and p_date_earned between pet.effective_start_date and pet.effective_end_date
4261 and p_date_earned between piv.effective_start_date and piv.effective_end_date ;
4262
4263 l_retro_exist number := 0;
4264 l_retro_amount number := 0;
4265 l_procedure VARCHAR2(80);
4266
4267 begin
4268
4269 g_debug := hr_utility.debug_enabled;
4270
4271 if g_debug then
4272 l_procedure := 'pay_au_paye_ff.get_retro_tax_var_fixed';
4273 hr_utility.set_location('Entering function '||l_procedure,1000);
4274 hr_utility.set_location('p_assignment_id '||p_assignment_id,1000);
4275 hr_utility.set_location('p_assignment_action_id '||p_assignment_action_id,1000);
4276 hr_utility.set_location('p_date_earned '||to_char(p_date_earned,'DD-MON-YYYY'),1000);
4277 hr_utility.set_location('p_mode '||p_mode,1000);
4278 end if;
4279
4280 p_retro := 'N';
4281
4282 if p_mode = 'CU' then
4283
4284 open get_retro_spread;
4285 fetch get_retro_spread into l_retro_amount;
4286 close get_retro_spread;
4287
4288 elsif p_mode = 'EX' then
4289
4290 open check_retro_run;
4291 fetch check_retro_run into l_retro_exist;
4292 close check_retro_run;
4293
4294 if l_retro_exist <> 0 then
4295 open get_retro_total;
4296 fetch get_retro_total into l_retro_amount;
4297 close get_retro_total;
4298 p_retro := 'Y';
4299 end if;
4300
4301 end if;
4302
4303 l_retro_amount := nvl(l_retro_amount,0);
4304
4305 if g_debug then
4306 hr_utility.set_location('l_retro_amount '||l_retro_amount,1000);
4307 hr_utility.set_location('p_retro '||p_retro,1000);
4308 hr_utility.set_location('Leaving function '||l_procedure,1000);
4309 end if;
4310
4311 return l_retro_amount;
4312
4313 end get_retro_tax_var_fixed;
4314
4315 /* Added for bug 12767287 to fetch all the earnings as of date 30-jun-2011 for the spanning payroll period in 2 different financial years */
4316 FUNCTION get_earns_flood_levy(ASSIGNMENT_ID IN NUMBER
4317 , TAX_UNIT_ID IN NUMBER
4318 , P_DATE IN DATE
4319 , P_ETP_EARN_NN OUT NOCOPY NUMBER
4320 , P_ETP_EARN_YN OUT NOCOPY NUMBER
4321 , P_ETP_EARN_NY OUT NOCOPY NUMBER
4322 , P_ETP_EARN_YY OUT NOCOPY NUMBER
4323 , P_LS_A OUT NOCOPY NUMBER
4324 , P_LS_B OUT NOCOPY NUMBER
4325 , P_LP_M OUT NOCOPY NUMBER
4326 , P_FW_LP_M OUT NOCOPY NUMBER
4327 , P_FW_LS_A OUT NOCOPY NUMBER )
4328 RETURN NUMBER IS
4329
4330 g_context_table pay_balance_pkg.t_context_tab;
4331 g_input_group_details_table pay_balance_pkg.t_balance_value_tab;
4332 g_result_group_details_table pay_balance_pkg.t_detailed_bal_out_tab;
4333 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
4334 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
4335 l_assignment_id pay_assignment_actions.assignment_id%TYPE;
4336 l_earnings NUMBER;
4337
4338 CURSOR csr_max_assgt_action(p_assignment_id IN pay_assignment_actions.assignment_id%TYPE)
4339 IS
4340 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
4341 from pay_assignment_actions paa
4342 , pay_payroll_actions ppa
4343 , per_assignments_f paf
4344 , per_time_periods ptp
4345 where paa.assignment_id = paf.assignment_id
4346 and paf.assignment_id = p_assignment_id
4347 and ppa.payroll_action_id = paa.payroll_action_id
4348 and ppa.payroll_id = paf.payroll_id
4349 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
4350 and ppa.effective_date between to_date('01-07-2010','dd-mm-yyyy') and to_date('30-06-2011','dd-mm-yyyy')
4351 and ppa.time_period_id = ptp.time_period_id
4352 and P_DATE between ptp.start_date and ptp.end_date
4353 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
4354 and paa.action_status='C'
4355 AND paa.tax_unit_id = TAX_UNIT_ID;
4356
4357 CURSOR get_def_bal_id (p_bal_name IN pay_balance_types.balance_name%TYPE)
4358 IS
4359 select pdb.defined_balance_id defined_balance_id
4360 from pay_balance_types pbt
4361 , pay_balance_dimensions pbd
4362 , pay_defined_balances pdb
4363 where pbt.balance_name = p_bal_name
4364 and pbd.database_item_suffix = '_ASG_LE_PTD'
4365 and pbt.balance_type_id = pdb.balance_type_id
4366 and pbd.balance_dimension_id = pdb.balance_dimension_id
4367 and pbt.legislation_code = 'AU';
4368
4369 CURSOR c_get_bal_id IS
4370 SELECT decode(pbt.balance_name,'Earnings_Standard',1
4371 , 'Earnings_Progressive',2
4372 , 'Earnings_Fixed',3
4373 , 'Pre Tax Fixed Deductions',4
4374 , 'Pre Tax Progressive Deductions',5
4375 , 'Earnings_Leave_Loading',6
4376 , 'Retro Earnings Leave Loading GT 12 Mths Amount',7
4377 , 'Retro Earnings Leave Loading LT 12 Mths Curr Yr Amount',8
4378 , 'Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount',9
4379 , 'Retro LT 12 Mths Curr Yr Amount',10
4380 , 'Retro LT 12 Mths Prev Yr Amount',11
4381 , 'Lump Sum E Payments',12
4382 , 'ETP Taxable Payments Life Benefit Not Part of Prev Term',13
4383 , 'ETP Taxable Payments Life Benefit Part of Prev Term',14
4384 , 'ETP Taxable Payments Transitional Not Part of Prev Term',15
4385 , 'ETP Taxable Payments Transitional Part of Prev Term',16
4386 , 'Lump Sum A Payments',17
4387 , 'Lump Sum B Payments',18
4388 , 'Leave Payments Marginal',19
4389 , 'Foreign Leave Payments Marginal',20
4390 , 'Foreign Lump Sum A Payments',21
4391 ) sort_index,
4392 pdb.defined_balance_id def_bal_id
4393 FROM pay_balance_types pbt,
4394 pay_defined_balances pdb,
4395 pay_balance_dimensions pbd
4396 WHERE pbt.legislation_code = 'AU'
4397 AND pdb.balance_type_id = pbt.balance_type_id
4398 AND pdb.balance_dimension_id = pbd.balance_dimension_id
4399 AND pbd.legislation_code = 'AU'
4400 AND pdb.legislation_code = 'AU'
4401 AND pbd.dimension_name = '_ASG_LE_PTD'
4402 and pbt.balance_name in ('Earnings_Standard'
4403 , 'Earnings_Progressive'
4404 , 'Earnings_Fixed'
4405 , 'Pre Tax Fixed Deductions'
4406 , 'Pre Tax Progressive Deductions'
4407 , 'Earnings_Leave_Loading'
4408 , 'Retro Earnings Leave Loading GT 12 Mths Amount'
4409 , 'Retro Earnings Leave Loading LT 12 Mths Curr Yr Amount'
4410 , 'Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount'
4411 , 'Retro LT 12 Mths Curr Yr Amount'
4412 , 'Retro LT 12 Mths Prev Yr Amount'
4413 , 'Lump Sum E Payments'
4414 , 'ETP Taxable Payments Life Benefit Not Part of Prev Term'
4415 , 'ETP Taxable Payments Life Benefit Part of Prev Term'
4416 , 'ETP Taxable Payments Transitional Not Part of Prev Term'
4417 , 'ETP Taxable Payments Transitional Part of Prev Term'
4418 , 'Lump Sum A Payments'
4419 , 'Lump Sum B Payments'
4420 , 'Leave Payments Marginal'
4421 , 'Foreign Leave Payments Marginal'
4422 , 'Foreign Lump Sum A Payments'
4423 )
4424 order by sort_index;
4425
4426 BEGIN
4427
4428 g_context_table(1).tax_unit_id := TAX_UNIT_ID;
4429 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(P_DATE));
4430
4431 FOR csr_rec IN c_get_bal_id LOOP
4432
4433 g_input_group_details_table(csr_rec.sort_index).defined_balance_id := csr_rec.def_bal_id;
4434
4435 END LOOP;
4436 l_assignment_id := ASSIGNMENT_ID;
4437 OPEN csr_max_assgt_action(l_assignment_id);
4438 FETCH csr_max_assgt_action INTO l_assignment_action_id;
4439 CLOSE csr_max_assgt_action;
4440
4441 pay_balance_pkg.get_value(p_assignment_action_id => l_assignment_action_id,
4442 p_defined_balance_lst => g_input_group_details_table,
4443 p_context_lst => g_context_table,
4444 p_output_table => g_result_group_details_table);
4445
4446
4447 l_earnings := g_result_group_details_table(1).balance_value + g_result_group_details_table(2).balance_value
4448 + g_result_group_details_table(3).balance_value - g_result_group_details_table(4).balance_value
4449 - g_result_group_details_table(5).balance_value + g_result_group_details_table(6).balance_value + g_result_group_details_table(7).balance_value
4450 + g_result_group_details_table(8).balance_value + g_result_group_details_table(9).balance_value
4451 - g_result_group_details_table(10).balance_value - g_result_group_details_table(11).balance_value
4452 - g_result_group_details_table(12).balance_value;
4453
4454 P_ETP_EARN_NN := g_result_group_details_table(13).balance_value;
4455 P_ETP_EARN_NY := g_result_group_details_table(14).balance_value;
4456 P_ETP_EARN_YN := g_result_group_details_table(15).balance_value;
4457 P_ETP_EARN_YY := g_result_group_details_table(16).balance_value;
4458 P_LS_A := g_result_group_details_table(17).balance_value;
4459 P_LS_B := g_result_group_details_table(18).balance_value;
4460 P_LP_M := g_result_group_details_table(19).balance_value;
4461 P_FW_LP_M := g_result_group_details_table(20).balance_value;
4462 P_FW_LS_A := g_result_group_details_table(21).balance_value;
4463
4464 RETURN l_earnings;
4465 END;
4466
4467 /* bug 13879232 - Added for formula function AU_GET_ADDTL_MESSAGE */
4468 FUNCTION get_addtl_message (TOKEN_NAME IN VARCHAR2, TOKEN_VALUE IN VARCHAR2)
4469 RETURN VARCHAR2 IS
4470
4471 msg varchar2(1000);
4472 l_procedure varchar2(80);
4473
4474 BEGIN
4475
4476 g_debug := hr_utility.debug_enabled;
4477
4478 if g_debug then
4479 l_procedure :='pay_au_paye_ff.get_addtl_message';
4480 hr_utility.set_location('Entering '||l_procedure,10);
4481 hr_utility.set_location('IN TOKEN_NAME '||TOKEN_NAME,10);
4482 hr_utility.set_location('IN TOKEN_VALUE '||TOKEN_VALUE,10);
4483
4484 end if;
4485
4486 IF TOKEN_NAME = 'AMOUNT' then
4487 msg := fffunc.gfm('PAY', 'HR_AU_RETRO_PREV_NEGATIVE_WARN',TOKEN_NAME, TOKEN_VALUE);
4488 if g_debug then
4489 hr_utility.set_location('Return msg '||msg,90);
4490 hr_utility.set_location('Exiting '||l_procedure,90);
4491 end if;
4492
4493 RETURN msg;
4494 ELSE
4495 msg := fffunc.gfm('PAY', 'HR_AU_NEGATIVE_ADDTL_DEDUCTION','NAME', TOKEN_VALUE);
4496 if g_debug then
4497 hr_utility.set_location('Return msg '||msg,90);
4498 hr_utility.set_location('Exiting '||l_procedure,90);
4499 end if;
4500
4501 RETURN msg;
4502
4503 END IF;
4504
4505 END;
4506
4507 PROCEDURE upgrade_tax_scale(errbuf out NOCOPY varchar2,
4508 retcode out NOCOPY varchar2,
4509 p_business_group_id in HR_ALL_ORGANIZATION_UNITS.organization_id%type,
4510 p_mode in varchar2)
4511 IS
4512
4513 error_code number;
4514 error_message varchar2(255);
4515 l_business_group_name per_business_groups.name%type;
4516 l_run_mode VARCHAR2(60);
4517
4518 type number_table is table of number not null index by binary_integer;
4519 type screen_entry_values is table of pay_element_entry_values_f.screen_entry_value%TYPE index by binary_integer;
4520 l_inp_value_id_table number_table;
4521 l_element_type_id NUMBER :=0;
4522 l_effective_start_date DATE;
4523 l_effective_end_date DATE;
4524 l_update_warning BOOLEAN;
4525 l_business_group_id NUMBER;
4526 l_mode VARCHAR2(60);
4527 l_screen_entry_values screen_entry_values;
4528 g_date DATE := to_date('01-07-2012','dd-mm-yyyy');
4529 l_new_value varchar2(60);
4530 l_object_version_number NUMBER := -1;
4531 l_full_name VARCHAR2(100);
4532 l_entry_information pay_element_entries_f.entry_information1%type;
4533 l_count NUMBER := 0;
4534 l_asg_exist NUMBER := 0;
4535 l_upgraded NUMBER := 0;
4536 l_emp_num per_all_people_f.employee_number%TYPE;
4537 l_ovn NUMBER := -1;
4538 l_start DATE;
4539 l_asg_mode varchar2(60);
4540 l_soft_code_id per_all_assignments_f.SOFT_CODING_KEYFLEX_ID%TYPE := -1;
4541 l_legal_employer_id NUMBER := -1;
4542 l_le_id NUMBER := -1;
4543 l_load_flag VARCHAR2(10);
4544
4545 l_cagr_grade_def_id number;
4546 l_cagr_concatenated_segments varchar2(2000);
4547 l_comment_id number;
4548 l_soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%type;
4549 l_concatenated_segments hr_soft_coding_keyflex.concatenated_segments%TYPE;
4550 l_no_managers_warning boolean;
4551 l_other_manager_warning boolean;
4552 not_inserted boolean := TRUE;
4553
4554 CURSOR csr_paye_tax_input_values(p_element_type_id pay_input_values_f.element_type_id%TYPE) IS
4555 SELECT piv.input_value_id
4556 ,piv.name
4557 FROM pay_input_values_f piv
4558 WHERE piv.element_type_id = p_element_type_id
4559 AND g_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
4560
4561 CURSOR csr_paye_tax_element IS
4562 SELECT pet.element_type_id
4563 FROM pay_element_types_f pet
4564 WHERE pet.element_name = 'Tax Information'
4565 AND g_date BETWEEN pet.effective_start_date AND pet.effective_end_date
4566 AND legislation_code = 'AU';
4567
4568 CURSOR csr_ele_entries(p_element_type_id IN pay_element_types_f.element_type_id%TYPE,p_input_value_id IN NUMBER) IS
4569 select pee.assignment_id
4570 ,pee.element_entry_id
4571 ,pee.effective_start_date
4572 ,pee.effective_end_date
4573 ,pee.object_version_number
4574 from pay_element_entries_f pee
4575 where pee.ELEMENT_TYPE_ID = p_element_type_id
4576 and g_date between pee.effective_start_date and pee.effective_end_date
4577 and pee.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
4578 and exists (
4579 select 1 from pay_element_entry_values_f peev
4580 where peev.element_entry_id = pee.element_entry_id
4581 and peev.INPUT_VALUE_ID = p_input_value_id and peev.SCREEN_ENTRY_VALUE = '7'
4582 and peev.effective_start_date = pee.effective_start_date
4583 and peev.effective_end_date = pee.effective_end_date
4584 )
4585 order by assignment_id;
4586
4587 CURSOR csr_asg_curr_scale(p_element_type_id IN pay_element_types_f.element_type_id%TYPE,p_input_value_id IN NUMBER) IS
4588 select pee.assignment_id
4589 from pay_element_entries_f pee
4590 where pee.ELEMENT_TYPE_ID = p_element_type_id
4591 and g_date between pee.effective_start_date and pee.effective_end_date
4592 and pee.effective_start_date = g_date
4593 and exists (
4594 select 1 from pay_element_entry_values_f peev
4595 where peev.element_entry_id = pee.element_entry_id
4596 and peev.INPUT_VALUE_ID = p_input_value_id and peev.SCREEN_ENTRY_VALUE = '2'
4597 and peev.effective_start_date = pee.effective_start_date
4598 and peev.effective_end_date = pee.effective_end_date
4599 )
4600 and exists (
4601 select 1 from
4602 pay_element_entry_values_f peev1,
4603 pay_element_entries_f pee1
4604 where pee.ELEMENT_TYPE_ID = p_element_type_id
4605 and g_date-1 between pee1.effective_start_date and pee1.effective_end_date
4606 and peev1.element_entry_id = pee1.element_entry_id
4607 and pee1.element_entry_id = pee.element_entry_id
4608 and peev1.INPUT_VALUE_ID = p_input_value_id and peev1.SCREEN_ENTRY_VALUE = '7'
4609 and peev1.effective_start_date = pee1.effective_start_date
4610 and peev1.effective_end_date = pee1.effective_end_date
4611 )
4612 order by assignment_id;
4613
4614 CURSOR csr_fut_ele_entries(p_element_type_id IN pay_element_types_f.element_type_id%TYPE,p_input_value_id IN NUMBER) IS
4615 select pee.assignment_id,pee.element_entry_id,pee.effective_start_date,pee.effective_end_date
4616 from pay_element_entries_f pee
4617 where pee.ELEMENT_TYPE_ID = p_element_type_id
4618 and g_date between pee.effective_start_date and pee.effective_end_date
4619 and exists (
4620 select 1 from pay_element_entry_values_f peev
4621 where peev.element_entry_id = pee.element_entry_id
4622 and peev.INPUT_VALUE_ID = p_input_value_id and peev.SCREEN_ENTRY_VALUE = '7'
4623 and peev.effective_start_date = pee.effective_start_date
4624 and peev.effective_end_date = pee.effective_end_date
4625 )
4626 and exists ( select 1 from pay_element_entries_f pee1
4627 where pee1.element_type_id = p_element_type_id
4628 and pee1.assignment_id = pee.assignment_id
4629 and pee1.element_entry_id = pee.element_entry_id
4630 and ( pee1.effective_start_date > pee.effective_start_date
4631 or (pee1.effective_end_date > g_date and pee1.effective_end_date <> to_date('31-12-4712','dd-mm-yyyy')))
4632 )
4633 order by assignment_id;
4634
4635
4636 CURSOR get_prev_database_tax_fields(p_element_entry_id NUMBER,
4637 p_inp_value_id_table number_table,
4638 p_effective_start_date DATE
4639 )
4640 IS
4641 select
4642 eev0.screen_entry_value,
4643 eev1.screen_entry_value,
4644 eev2.screen_entry_value,
4645 eev3.screen_entry_value,
4646 eev4.screen_entry_value,
4647 eev5.screen_entry_value,
4648 eev6.screen_entry_value,
4649 eev7.screen_entry_value,
4650 eev8.screen_entry_value,
4651 eev9.screen_entry_value,
4652 eev10.screen_entry_value,
4653 eev11.screen_entry_value,
4654 eev12.screen_entry_value,
4655 eev13.screen_entry_value,
4656 eev14.screen_entry_value,
4657 pee.entry_information1
4658 FROM
4659 pay_element_entries_f pee,
4660 pay_element_entry_values_f eev0 ,
4661 pay_element_entry_values_f eev1 ,
4662 pay_element_entry_values_f eev2 ,
4663 pay_element_entry_values_f eev3 ,
4664 pay_element_entry_values_f eev4 ,
4665 pay_element_entry_values_f eev5 ,
4666 pay_element_entry_values_f eev6 ,
4667 pay_element_entry_values_f eev7 ,
4668 pay_element_entry_values_f eev8 ,
4669 pay_element_entry_values_f eev9 ,
4670 pay_element_entry_values_f eev10 ,
4671 pay_element_entry_values_f eev11 ,
4672 pay_element_entry_values_f eev12 ,
4673 pay_element_entry_values_f eev13 ,
4674 pay_element_entry_values_f eev14
4675 WHERE
4676 eev0.element_entry_id = pee.element_entry_id
4677 and eev0.input_value_id = p_inp_value_id_table(1)
4678 and eev1.element_entry_id = pee.element_entry_id
4679 and eev1.input_value_id = p_inp_value_id_table(2)
4680 and eev2.element_entry_id = pee.element_entry_id
4681 and eev2.input_value_id = p_inp_value_id_table(3)
4682 and eev3.element_entry_id = pee.element_entry_id
4683 and eev3.input_value_id = p_inp_value_id_table(4)
4684 and eev4.element_entry_id = pee.element_entry_id
4685 and eev4.input_value_id = p_inp_value_id_table(5)
4686 and eev5.element_entry_id = pee.element_entry_id
4687 and eev5.input_value_id = p_inp_value_id_table(6)
4688 and eev6.element_entry_id = pee.element_entry_id
4689 and eev6.input_value_id = p_inp_value_id_table(7)
4690 and eev7.element_entry_id = pee.element_entry_id
4691 and eev7.input_value_id = p_inp_value_id_table(8)
4692 and eev8.element_entry_id = pee.element_entry_id
4693 and eev8.input_value_id = p_inp_value_id_table(9)
4694 and eev9.element_entry_id = pee.element_entry_id
4695 and eev9.input_value_id = p_inp_value_id_table(10)
4696 and eev10.element_entry_id = pee.element_entry_id
4697 and eev10.input_value_id = p_inp_value_id_table(11)
4698 and eev11.element_entry_id = pee.element_entry_id
4699 and eev11.input_value_id = p_inp_value_id_table(12)
4700 and eev12.element_entry_id = pee.element_entry_id
4701 and eev12.input_value_id = p_inp_value_id_table(13)
4702 and eev13.element_entry_id = pee.element_entry_id
4703 and eev13.input_value_id = p_inp_value_id_table(14)
4704 and eev14.element_entry_id = pee.element_entry_id
4705 and eev14.input_value_id = p_inp_value_id_table(15)
4706 and pee.element_entry_id = p_element_entry_id
4707 AND p_effective_start_date between eev0.effective_start_date and eev0.effective_end_date
4708 AND p_effective_start_date between eev1.effective_start_date and eev1.effective_end_date
4709 AND p_effective_start_date between eev2.effective_start_date and eev2.effective_end_date
4710 AND p_effective_start_date between eev3.effective_start_date and eev3.effective_end_date
4711 AND p_effective_start_date between eev4.effective_start_date and eev4.effective_end_date
4712 AND p_effective_start_date between eev5.effective_start_date and eev5.effective_end_date
4713 AND p_effective_start_date between eev6.effective_start_date and eev6.effective_end_date
4714 AND p_effective_start_date between eev7.effective_start_date and eev7.effective_end_date
4715 AND p_effective_start_date between eev8.effective_start_date and eev8.effective_end_date
4716 AND p_effective_start_date between eev9.effective_start_date and eev9.effective_end_date
4717 AND p_effective_start_date between eev10.effective_start_date and eev10.effective_end_date
4718 AND p_effective_start_date between eev11.effective_start_date and eev11.effective_end_date
4719 AND p_effective_start_date between eev12.effective_start_date and eev12.effective_end_date
4720 AND p_effective_start_date between eev13.effective_start_date and eev13.effective_end_date
4721 AND p_effective_start_date between eev14.effective_start_date and eev14.effective_end_date
4722 AND p_effective_start_date between pee.effective_start_date and pee.effective_end_date;
4723
4724
4725 CURSOR get_bg_id(p_assignment_id IN NUMBER)
4726 IS
4727 select distinct business_group_id
4728 from per_all_assignments_f
4729 where assignment_id = p_assignment_id;
4730
4731 CURSOR get_emp_details(p_assignment_id IN NUMBER)
4732 IS
4733 SELECT pap.FULL_NAME,pap.EMPLOYEE_NUMBER
4734 from per_all_people_f pap
4735 where pap.person_id in
4736 ( select distinct person_id
4737 from per_all_assignments_f
4738 where assignment_id = p_assignment_id
4739 )
4740 and pap.effective_end_date = ( select max(papf.effective_end_date)
4741 from per_all_people_f papf
4742 where papf.person_id in
4743 ( select distinct person_id
4744 from per_all_assignments_f
4745 where assignment_id = p_assignment_id
4746 ));
4747 -- and g_date between pap.effective_start_date and pap.effective_end_date;
4748
4749
4750 CURSOR csr_fut_asg_rec(p_assignment_id IN NUMBER)
4751 IS
4752 SELECT COUNT(*)
4753 FROM per_all_assignments_f
4754 where assignment_id = p_assignment_id
4755 and ( effective_start_date > g_date or ( effective_end_date > g_date and effective_end_date <> to_date('31-12-4712','dd-mm-yyyy'))) ;
4756
4757 CURSOR csr_asg_det(p_assignment_id IN NUMBER)
4758 IS
4759 SELECT object_version_number,effective_start_date,soft_coding_keyflex_id
4760 from per_all_assignments_f
4761 where assignment_id = p_assignment_id
4762 and g_date between effective_start_date and effective_end_date
4763 and effective_end_date = to_date('31-12-4712','dd-mm-yyyy');
4764
4765 CURSOR csr_get_le_id(p_hr_soft_id IN NUMBER)
4766 IS
4767 SELECT SEGMENT1
4768 from hr_soft_coding_keyflex
4769 where SOFT_CODING_KEYFLEX_ID = p_hr_soft_id;
4770
4771 CURSOR csr_get_loading_flag(p_hr_soft_id IN NUMBER)
4772 IS
4773 SELECT SEGMENT2
4774 from hr_soft_coding_keyflex
4775 where SOFT_CODING_KEYFLEX_ID = p_hr_soft_id;
4776
4777 -- cursor for first record of assignment
4778 cursor fut_fir_asg_rec(p_assignment_id IN NUMBER) IS
4779 SELECT assignment_id,effective_start_date,effective_end_date,object_version_number,soft_coding_keyflex_id
4780 FROM per_all_assignments_f
4781 where assignment_id = p_assignment_id
4782 and g_date between effective_start_date and effective_end_date;
4783
4784 -- cursor for all future records of assignment post 01-jul-2012
4785 cursor fut_all_next_asg_rec(p_assignment_id IN NUMBER) IS
4786 SELECT assignment_id,effective_start_date,effective_end_date,object_version_number,soft_coding_keyflex_id
4787 FROM per_all_assignments_f
4788 where assignment_id = p_assignment_id
4789 and effective_start_date > g_date;
4790
4791 -- cursor for number of future records of assignment post 01-jul-2012
4792 cursor cnt_fut_asg_rec(p_assignment_id IN NUMBER) IS
4793 SELECT count(*)
4794 FROM per_all_assignments_f
4795 where assignment_id = p_assignment_id
4796 and effective_start_date > g_date;
4797
4798 -- cursor for first record of element entry
4799 cursor fut_fir_ele_rec(p_element_entry_id IN NUMBER) IS
4800 select assignment_id,element_entry_id,effective_start_date,effective_end_date,object_version_number
4801 from pay_element_entries_f
4802 where element_entry_id = p_element_entry_id
4803 and g_date between effective_start_date and effective_end_date;
4804
4805 -- cursor for future records of element entry post 01-jul-2012
4806 cursor fut_all_next_ele_rec(p_element_entry_id IN NUMBER) IS
4807 select assignment_id,element_entry_id,effective_start_date,effective_end_date,object_version_number
4808 from pay_element_entries_f
4809 where element_entry_id = p_element_entry_id
4810 and effective_start_date > g_date;
4811
4812 -- cursor for number of future records of element entries post 01-jul-2012
4813 cursor cnt_fut_ele_rec(p_element_entry_id IN NUMBER) IS
4814 select count(*)
4815 from pay_element_entries_f
4816 where element_entry_id = p_element_entry_id
4817 and effective_start_date > g_date;
4818
4819 BEGIN
4820
4821 select distinct name into l_business_group_name
4822 from per_business_groups
4823 where business_group_id = p_business_group_id;
4824
4825 select decode(p_mode, 'L', 'Listing Mode', 'A','All','U','Upgrading Mode','Future Upgrade') into l_run_mode from dual;
4826
4827 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business Group Name : '|| l_business_group_name);
4828 FND_FILE.PUT_LINE(FND_FILE.LOG,'Running Mode : '|| l_run_mode);
4829 FND_FILE.NEW_LINE(FND_FILE.LOG);
4830 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------------------------------------------');
4831 FND_FILE.NEW_LINE(FND_FILE.LOG);
4832
4833 OPEN csr_paye_tax_element;
4834 FETCH csr_paye_tax_element into l_element_type_id;
4835 CLOSE csr_paye_tax_element;
4836
4837 FOR rec_paye_tax_element in csr_paye_tax_input_values(l_element_type_id) LOOP
4838 IF UPPER(rec_paye_tax_element.name) = 'AUSTRALIAN RESIDENT' THEN
4839 l_inp_value_id_table(1) := rec_paye_tax_element.input_value_id;
4840
4841 ELSIF UPPER(rec_paye_tax_element.name) = 'TAX FREE THRESHOLD' THEN
4842 l_inp_value_id_table(2) := rec_paye_tax_element.input_value_id;
4843
4844 ELSIF UPPER(rec_paye_tax_element.name) = 'REBATE AMOUNT' THEN
4845 l_inp_value_id_table(3) := rec_paye_tax_element.input_value_id;
4846
4847 ELSIF UPPER(rec_paye_tax_element.name) = 'FTA CLAIM' THEN
4848 l_inp_value_id_table(4) := rec_paye_tax_element.input_value_id;
4849
4850 ELSIF UPPER(rec_paye_tax_element.name) = 'SAVINGS REBATE' THEN
4851 l_inp_value_id_table(5) := rec_paye_tax_element.input_value_id;
4852
4853 ELSIF UPPER(rec_paye_tax_element.name) = 'HECS' THEN
4854 l_inp_value_id_table(6) := rec_paye_tax_element.input_value_id;
4855
4856 ELSIF UPPER(rec_paye_tax_element.name) = 'DATE DECLARATION SIGNED' THEN
4857 l_inp_value_id_table(7) := rec_paye_tax_element.input_value_id;
4858
4859 ELSIF UPPER(rec_paye_tax_element.name) = 'MEDICARE LEVY VARIATION' THEN
4860 l_inp_value_id_table(8) := rec_paye_tax_element.input_value_id;
4861
4862 ELSIF UPPER(rec_paye_tax_element.name) = 'SPOUSE' THEN
4863 l_inp_value_id_table(9) := rec_paye_tax_element.input_value_id;
4864
4865 ELSIF UPPER(rec_paye_tax_element.name) = 'DEPENDENT CHILDREN' THEN
4866 l_inp_value_id_table(10) := rec_paye_tax_element.input_value_id;
4867
4868 ELSIF UPPER(rec_paye_tax_element.name) = 'TAX VARIATION TYPE' THEN
4869 l_inp_value_id_table(11) := rec_paye_tax_element.input_value_id;
4870
4871 ELSIF UPPER(rec_paye_tax_element.name) = 'TAX VARIATION AMOUNT' THEN
4872 l_inp_value_id_table(12) := rec_paye_tax_element.input_value_id;
4873
4874 ELSIF UPPER(rec_paye_tax_element.name) = 'TAX SCALE' THEN
4875 l_inp_value_id_table(13) := rec_paye_tax_element.input_value_id;
4876
4877 ELSIF UPPER(rec_paye_tax_element.name) = 'TAX FILE NUMBER' THEN
4878 l_inp_value_id_table(14) := rec_paye_tax_element.input_value_id;
4879
4880 ELSIF UPPER(rec_paye_tax_element.name) = 'EXEMPT FLOOD LEVY' THEN
4881 l_inp_value_id_table(15) := rec_paye_tax_element.input_value_id;
4882
4883 END IF;
4884 END LOOP;
4885
4886
4887 IF l_run_mode = 'Listing Mode' THEN
4888
4889 fnd_file.put_line(FND_FILE.LOG,'Listing of Employees with current tax scale = 7 and eligible for upgrade to tax scale = 2' );
4890 fnd_file.put_line(FND_FILE.LOG,'since they have open ended element entry for Tax Information element as of 01-JUL-2012' );
4891 fnd_file.put_line(FND_FILE.LOG,'and open ended assignment entry record as of 01-JUL-2012' );
4892 FND_FILE.NEW_LINE(FND_FILE.LOG);
4893
4894 FOR asgrec IN csr_ele_entries(l_element_type_id,l_inp_value_id_table(13))
4895 LOOP
4896
4897 l_asg_exist := 0;
4898 l_business_group_id := null;
4899
4900 OPEN csr_fut_asg_rec(asgrec.assignment_id);
4901 FETCH csr_fut_asg_rec INTO l_asg_exist;
4902 CLOSE csr_fut_asg_rec;
4903
4904 OPEN get_bg_id(asgrec.assignment_id);
4905 FETCH get_bg_id INTO l_business_group_id;
4906 CLOSE get_bg_id;
4907
4908
4909 IF l_asg_exist = 0 THEN
4910
4911 OPEN get_emp_details(asgrec.assignment_id) ;
4912 FETCH get_emp_details INTO l_full_name , l_emp_num;
4913 CLOSE get_emp_details;
4914
4915 IF l_full_name is not null and l_emp_num is not null and l_business_group_id = p_business_group_id THEN
4916 fnd_file.put_line(FND_FILE.LOG,'Assignment ID '||asgrec.assignment_id || ' (Employee name / Employee number - ' || l_full_name ||' / ' || l_emp_num || ')' );
4917 l_count := l_count + 1;
4918 END IF;
4919
4920 END IF;
4921 -- FND_FILE.NEW_LINE(FND_FILE.LOG);
4922
4923 END LOOP;
4924
4925 IF l_count = 0 THEN
4926 FND_FILE.NEW_LINE(FND_FILE.LOG);
4927 fnd_file.put_line(FND_FILE.LOG,'No Employees exist under this category' );
4928 END IF;
4929
4930 IF l_count > 0 THEN
4931 FND_FILE.NEW_LINE(FND_FILE.LOG);
4932 fnd_file.put_line(FND_FILE.LOG,'Total Number of Employees eligible for upgrade = '||l_count);
4933 END IF;
4934
4935 FND_FILE.NEW_LINE(FND_FILE.LOG);
4936 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------------------------------------------');
4937 FND_FILE.NEW_LINE(FND_FILE.LOG);
4938
4939 fnd_file.put_line(FND_FILE.LOG,'Listing of Employees with current tax scale = 7 and eligible for upgrade to tax scale = 2');
4940 fnd_file.put_line(FND_FILE.LOG,'but having future-dated/end-dated element entries for Tax Information element and (or) having future-dated/end-dated assignment changes post 01-JUL-2012' );
4941 fnd_file.put_line(FND_FILE.LOG,'Please delete the future-dated/end-dated element entries and (or) future-dated/end-dated assignment changes before running program in UPGRADE mode');
4942 fnd_file.put_line(FND_FILE.LOG,'OR');
4943 fnd_file.put_line(FND_FILE.LOG,'Modify the tax scale of these employees manually from 7 to 2 effective 01-JUL-2012');
4944 fnd_file.put_line(FND_FILE.LOG,'Manually Changing the Assignments Leave Loading Flag from "No" to "Yes" effective 01-JUL-2012 ');
4945 fnd_file.put_line(FND_FILE.LOG,'changes the existing Tax Scale of these employees from 7 to 2');
4946 FND_FILE.NEW_LINE(FND_FILE.LOG);
4947
4948 l_count := 0;
4949
4950 FOR asgrec IN csr_fut_ele_entries(l_element_type_id,l_inp_value_id_table(13))
4951 LOOP
4952 l_asg_exist := 0;
4953 l_business_group_id := null;
4954
4955 OPEN get_bg_id(asgrec.assignment_id);
4956 FETCH get_bg_id INTO l_business_group_id;
4957 CLOSE get_bg_id;
4958
4959 IF l_business_group_id = p_business_group_id THEN
4960
4961 OPEN get_emp_details(asgrec.assignment_id) ;
4962 FETCH get_emp_details INTO l_full_name,l_emp_num;
4963 CLOSE get_emp_details;
4964
4965 fnd_file.put_line(FND_FILE.LOG,'Assignment ID '||asgrec.assignment_id || ' (Employee name / Employee number - ' || l_full_name ||' / ' || l_emp_num || ') has future-dated/end-dated element entries' );
4966 FND_FILE.NEW_LINE(FND_FILE.LOG);
4967
4968 OPEN csr_fut_asg_rec(asgrec.assignment_id);
4969 FETCH csr_fut_asg_rec INTO l_asg_exist;
4970 CLOSE csr_fut_asg_rec;
4971
4972 IF l_asg_exist > 0 THEN
4973 fnd_file.put_line(FND_FILE.LOG,'Assignment ID '||asgrec.assignment_id || ' (Employee name / Employee number - ' || l_full_name ||' / ' || l_emp_num || ') has future-dated/end-dated assignment records' );
4974 FND_FILE.NEW_LINE(FND_FILE.LOG);
4975 END IF;
4976
4977 -- FND_FILE.NEW_LINE(FND_FILE.LOG);
4978 l_count := l_count + 1;
4979 END IF;
4980 END LOOP;
4981
4982 FND_FILE.NEW_LINE(FND_FILE.LOG);
4983
4984 FOR asgrec IN csr_ele_entries(l_element_type_id,l_inp_value_id_table(13))
4985 LOOP
4986
4987 l_asg_exist := 0;
4988 l_full_name := null;
4989 l_emp_num := null;
4990 l_business_group_id := null;
4991
4992 OPEN csr_fut_asg_rec(asgrec.assignment_id);
4993 FETCH csr_fut_asg_rec INTO l_asg_exist;
4994 CLOSE csr_fut_asg_rec;
4995
4996 OPEN get_emp_details(asgrec.assignment_id) ;
4997 FETCH get_emp_details INTO l_full_name , l_emp_num;
4998 CLOSE get_emp_details;
4999
5000 OPEN get_bg_id(asgrec.assignment_id);
5001 FETCH get_bg_id INTO l_business_group_id;
5002 CLOSE get_bg_id;
5003
5004 IF l_business_group_id = p_business_group_id THEN
5005 IF l_asg_exist > 0 THEN
5006
5007 fnd_file.put_line(FND_FILE.LOG,'Assignment ID '||asgrec.assignment_id || ' (Employee name / Employee number - ' || l_full_name ||' / ' || l_emp_num || ') has future-dated/end-dated assignment records' );
5008 FND_FILE.NEW_LINE(FND_FILE.LOG);
5009 l_count := l_count + 1;
5010
5011 ELSIF l_full_name is null and l_emp_num is null THEN
5012 fnd_file.put_line(FND_FILE.LOG,'Assignment ID '||asgrec.assignment_id || ' (Employee name / Employee number - ' || l_full_name ||' / ' || l_emp_num || ') does not have any assignment record, but has some element entries attached to assignment');
5013 fnd_file.put_line(FND_FILE.LOG,'Please check the data setup for the assignment on Assignment Form and insert the assignment details appropriately' );
5014 fnd_file.put_line(FND_FILE.LOG,'This could probably be a case of data corruption' );
5015 FND_FILE.NEW_LINE(FND_FILE.LOG);
5016 l_count := l_count + 1;
5017
5018 END IF;
5019 END IF;
5020
5021
5022
5023 END LOOP;
5024
5025 IF l_count = 0 THEN
5026 FND_FILE.NEW_LINE(FND_FILE.LOG);
5027 fnd_file.put_line(FND_FILE.LOG,'No Employees exist under this category' );
5028 END IF;
5029
5030 IF l_count > 0 THEN
5031 FND_FILE.NEW_LINE(FND_FILE.LOG);
5032 fnd_file.put_line(FND_FILE.LOG,'Total Number of Employees ineligible for upgrade = '||l_count);
5033 END IF;
5034
5035 FND_FILE.NEW_LINE(FND_FILE.LOG);
5036 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------------------------------------------');
5037 FND_FILE.NEW_LINE(FND_FILE.LOG);
5038
5039 ELSIF l_run_mode = 'All' THEN
5040
5041 fnd_file.put_line(FND_FILE.LOG,'Listing of all upgraded employees with tax scale = 2 as of 01-JUL-2012' );
5042 FND_FILE.NEW_LINE(FND_FILE.LOG);
5043
5044 l_count := 0;
5045 FOR csritr IN csr_asg_curr_scale(l_element_type_id,l_inp_value_id_table(13))
5046 LOOP
5047
5048 l_business_group_id := null;
5049
5050 OPEN get_emp_details(csritr.assignment_id);
5051 FETCH get_emp_details INTO l_full_name,l_emp_num;
5052 CLOSE get_emp_details;
5053
5054 OPEN get_bg_id(csritr.assignment_id);
5055 FETCH get_bg_id INTO l_business_group_id;
5056 CLOSE get_bg_id;
5057
5058 IF l_business_group_id = p_business_group_id THEN
5059 fnd_file.put_line(FND_FILE.LOG,'Assignment ID '||csritr.assignment_id || ' (Employee name / Employee number - ' || l_full_name ||' / ' || l_emp_num || ')' );
5060 l_count := l_count + 1;
5061 END IF;
5062
5063 END LOOP;
5064
5065 IF l_count > 0 THEN
5066 FND_FILE.NEW_LINE(FND_FILE.LOG);
5067 fnd_file.put_line(FND_FILE.LOG,'Total Number of upgraded Employees with tax scale = 2 are '||l_count);
5068 END IF;
5069
5070 FND_FILE.NEW_LINE(FND_FILE.LOG);
5071 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------------------------------------------');
5072 FND_FILE.NEW_LINE(FND_FILE.LOG);
5073 ELSIF l_run_mode = 'Upgrading Mode' THEN
5074 fnd_file.put_line(FND_FILE.LOG,'Upgrading Tax Scales for Employee from 7 to 2 ' );
5075 fnd_file.put_line(FND_FILE.LOG,'This mode will update the Assignment Leave Loading Flag from No to Yes effective 01-JUL-2012' );
5076 fnd_file.put_line(FND_FILE.LOG,'This mode will also update the Assignment Tax Scale in element entries from 7 to 2 effective 01-JUL-2012' );
5077 fnd_file.put_line(FND_FILE.LOG,'UPGRADE is only done for those employees having open ended element entry for Tax Information element as of 01-JUL-2012' );
5078 fnd_file.put_line(FND_FILE.LOG,'and having open ended assignment entry record as of 01-JUL-2012');
5079 FND_FILE.NEW_LINE(FND_FILE.LOG);
5080
5081 l_count := 0;
5082
5083 FOR asgrec IN csr_ele_entries(l_element_type_id,l_inp_value_id_table(13))
5084 LOOP
5085
5086 l_asg_exist := 0;
5087 l_business_group_id := null;
5088
5089 OPEN csr_fut_asg_rec(asgrec.assignment_id);
5090 FETCH csr_fut_asg_rec INTO l_asg_exist;
5091 CLOSE csr_fut_asg_rec;
5092
5093 OPEN get_bg_id(asgrec.assignment_id);
5094 FETCH get_bg_id INTO l_business_group_id;
5095 CLOSE get_bg_id;
5096
5097 IF l_asg_exist = 0 and l_business_group_id = p_business_group_id THEN
5098
5099 l_ovn := -1;
5100 l_legal_employer_id := -1;
5101 l_soft_code_id := -1;
5102 l_object_version_number := -1;
5103
5104 OPEN csr_asg_det(asgrec.assignment_id);
5105 FETCH csr_asg_det INTO l_ovn,l_start,l_soft_code_id;
5106 CLOSE csr_asg_det;
5107
5108 OPEN get_emp_details(asgrec.assignment_id);
5109 FETCH get_emp_details INTO l_full_name,l_emp_num;
5110 CLOSE get_emp_details;
5111
5112
5113 IF l_start = g_date THEN
5114 l_asg_mode := 'CORRECTION';
5115 ELSE
5116 l_asg_mode := 'UPDATE';
5117 END IF;
5118
5119 IF asgrec.effective_start_date = g_date THEN
5120 l_mode := 'CORRECTION';
5121 ELSE
5122 l_mode := 'UPDATE';
5123 END IF;
5124
5125
5126 OPEN get_prev_database_tax_fields(asgrec.element_entry_id,l_inp_value_id_table,asgrec.effective_start_date);
5127 FETCH get_prev_database_tax_fields INTO
5128 l_screen_entry_values(1)
5129 ,l_screen_entry_values(2)
5130 ,l_screen_entry_values(3)
5131 ,l_screen_entry_values(4)
5132 ,l_screen_entry_values(5)
5133 ,l_screen_entry_values(6)
5134 ,l_screen_entry_values(7)
5135 ,l_screen_entry_values(8)
5136 ,l_screen_entry_values(9)
5137 ,l_screen_entry_values(10)
5138 ,l_screen_entry_values(11)
5139 ,l_screen_entry_values(12)
5140 ,l_screen_entry_values(13)
5141 ,l_screen_entry_values(14)
5142 ,l_screen_entry_values(15),l_entry_information;
5143 CLOSE get_prev_database_tax_fields;
5144
5145 l_new_value := '2';
5146 l_object_version_number := asgrec.object_version_number;
5147
5148
5149 IF l_ovn <> -1 and l_soft_code_id <> -1 and l_business_group_id = p_business_group_id THEN
5150
5151
5152 OPEN csr_get_le_id(l_soft_code_id);
5153 FETCH csr_get_le_id INTO l_legal_employer_id;
5154 CLOSE csr_get_le_id;
5155
5156 IF l_legal_employer_id <> -1 THEN
5157
5158 IF not_inserted THEN
5159 INSERT INTO FND_SESSIONS(SESSION_ID,EFFECTIVE_DATE) VALUES(USERENV('SESSIONID'),SYSDATE);
5160 COMMIT;
5161 not_inserted := false;
5162 END IF;
5163
5164 l_upgraded := 1;
5165
5166 hr_au_assignment_api.update_au_emp_asg
5167 (p_validate => false
5168 ,p_effective_date => g_date
5169 ,p_datetrack_update_mode => l_asg_mode
5170 ,p_assignment_id => asgrec.assignment_id
5171 ,p_object_version_number => l_ovn -- in/out parameter
5172 ,p_legal_employer_id => l_legal_employer_id
5173 ,p_lev_lod_flg => 'Y'
5174 ,p_cagr_grade_def_id => l_cagr_grade_def_id -- out parameter
5175 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments -- out parameter
5176 ,p_comment_id => l_comment_id -- out parameter
5177 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id -- out parameter
5178 ,p_effective_start_date => l_effective_start_date -- out parameter
5179 ,p_effective_end_date => l_effective_end_date -- out parameter
5180 ,p_concatenated_segments => l_concatenated_segments -- out parameter
5181 ,p_no_managers_warning => l_no_managers_warning -- out parameter
5182 ,p_other_manager_warning => l_other_manager_warning -- out parameter
5183 );
5184
5185 COMMIT;
5186
5187 pay_element_entry_api.update_element_entry
5188 (p_validate => false
5189 ,p_datetrack_update_mode => l_mode
5190 ,p_effective_date => g_date
5191 ,p_business_group_id => l_business_group_id
5192 ,p_element_entry_id => asgrec.element_entry_id
5193 ,p_object_version_number => l_object_version_number
5194 ,p_input_value_id1 => l_inp_value_id_table(1)
5195 ,p_input_value_id2 => l_inp_value_id_table(2)
5196 ,p_input_value_id3 => l_inp_value_id_table(3)
5197 ,p_input_value_id4 => l_inp_value_id_table(4)
5198 ,p_input_value_id5 => l_inp_value_id_table(5)
5199 ,p_input_value_id6 => l_inp_value_id_table(6)
5200 ,p_input_value_id7 => l_inp_value_id_table(7)
5201 ,p_input_value_id8 => l_inp_value_id_table(8)
5202 ,p_input_value_id9 => l_inp_value_id_table(9)
5203 ,p_input_value_id10 => l_inp_value_id_table(10)
5204 ,p_input_value_id11 => l_inp_value_id_table(11)
5205 ,p_input_value_id12 => l_inp_value_id_table(12)
5206 ,p_input_value_id13 => l_inp_value_id_table(13)
5207 ,p_input_value_id14 => l_inp_value_id_table(14)
5208 ,p_input_value_id15 => l_inp_value_id_table(15)
5209 ,p_entry_value1 => l_screen_entry_values(1)
5210 ,p_entry_value2 => l_screen_entry_values(2)
5211 ,p_entry_value3 => l_screen_entry_values(3)
5212 ,p_entry_value4 => l_screen_entry_values(4)
5213 ,p_entry_value5 => l_screen_entry_values(5)
5214 ,p_entry_value6 => l_screen_entry_values(6)
5215 ,p_entry_value7 => fnd_date.canonical_to_date(l_screen_entry_values(7))
5216 ,p_entry_value8 => l_screen_entry_values(8)
5217 ,p_entry_value9 => l_screen_entry_values(9)
5218 ,p_entry_value10 => l_screen_entry_values(10)
5219 ,p_entry_value11 => l_screen_entry_values(11)
5220 ,p_entry_value12 => l_screen_entry_values(12)
5221 ,p_entry_value13 => l_new_value
5222 ,p_entry_value14 => l_screen_entry_values(14)
5223 ,p_entry_value15 => l_screen_entry_values(15)
5224 ,p_effective_start_date => l_effective_start_date
5225 ,p_effective_end_date => l_effective_end_date
5226 ,p_update_warning => l_update_warning
5227 ,p_override_user_ent_chk => 'Y'
5228 );
5229
5230 COMMIT;
5231
5232 fnd_file.put_line(FND_FILE.LOG,'Assignment ID '||asgrec.assignment_id || ' (Employee name / Employee number - ' || l_full_name ||' / ' || l_emp_num ||') succesfully upgraded from Tax Scale 7 to 2' );
5233 FND_FILE.NEW_LINE(FND_FILE.LOG);
5234
5235 l_count := l_count + 1;
5236
5237 ELSE
5238 fnd_file.put_line(FND_FILE.LOG,'Assignment ID '||asgrec.assignment_id || ' (Employee name / Employee number - ' || l_full_name ||' / ' || l_emp_num ||') not upgraded from Tax Scale 7 to 2' );
5239 fnd_file.put_line(FND_FILE.LOG,'due to non-availability of valid legal employer as of 01-JUL-2012' );
5240 FND_FILE.NEW_LINE(FND_FILE.LOG);
5241 END IF;
5242 ELSE
5243 fnd_file.put_line(FND_FILE.LOG,'Assignment ID '||asgrec.assignment_id || ' (Employee name / Employee number - ' || l_full_name ||' / ' || l_emp_num ||') not upgraded from Tax Scale 7 to 2' );
5244 fnd_file.put_line(FND_FILE.LOG,'due to non-availability of valid SCL Information for Assignment as of 01-JUL-2012' );
5245 fnd_file.put_line(FND_FILE.LOG,'Please correct assignment SCL Information and then rerun the upgrade process for this assignment' );
5246 FND_FILE.NEW_LINE(FND_FILE.LOG);
5247 END IF;
5248 END IF;
5249 END LOOP;
5250
5251 IF l_upgraded = 0 THEN
5252 FND_FILE.NEW_LINE(FND_FILE.LOG);
5253 fnd_file.put_line(FND_FILE.LOG,'No assignments upgraded from tax scale = 7 to tax scale = 2' );
5254 END IF;
5255
5256 IF l_count > 0 THEN
5257 FND_FILE.NEW_LINE(FND_FILE.LOG);
5258 fnd_file.put_line(FND_FILE.LOG,'Total Number of succesfully upgraded Employees = '||l_count);
5259 END IF;
5260
5261 ELSE /* upgrading future or end dated assignments */
5262
5263 fnd_file.put_line(FND_FILE.LOG,'Upgrading Tax Scales for Future or End Dated Employee from 7 to 2 ' );
5264 fnd_file.put_line(FND_FILE.LOG,'This mode will update the Assignment Leave Loading Flag from No to Yes effective 01-JUL-2012' );
5265 fnd_file.put_line(FND_FILE.LOG,'This mode will also update the Assignment Tax Scale in element entries from 7 to 2 effective 01-JUL-2012' );
5266
5267 -- below code is for scenarios where single/multiple future dated element entries for tax information, single/multiple future dated records for assignment
5268 FOR asgrec IN csr_fut_ele_entries(l_element_type_id,l_inp_value_id_table(13)) LOOP
5269 l_le_id := -1;
5270 l_object_version_number := -1;
5271
5272 OPEN get_bg_id(asgrec.assignment_id);
5273 FETCH get_bg_id INTO l_business_group_id;
5274 CLOSE get_bg_id;
5275
5276 OPEN get_emp_details(asgrec.assignment_id) ;
5277 FETCH get_emp_details INTO l_full_name , l_emp_num;
5278 CLOSE get_emp_details;
5279
5280 IF l_business_group_id = p_business_group_id THEN
5281
5282 -- below loop is for updating all first assignment records ( first means record which contains 01-jul-2012)
5283 FOR fir IN fut_fir_asg_rec(asgrec.assignment_id) LOOP
5284
5285 l_object_version_number := fir.object_version_number;
5286 l_soft_code_id := fir.soft_coding_keyflex_id;
5287 l_load_flag := null;
5288
5289 OPEN csr_get_le_id(l_soft_code_id);
5290 FETCH csr_get_le_id INTO l_le_id;
5291 CLOSE csr_get_le_id;
5292
5293 OPEN csr_get_loading_flag(l_soft_code_id);
5294 FETCH csr_get_loading_flag INTO l_load_flag;
5295 CLOSE csr_get_loading_flag;
5296
5297 IF l_le_id <> -1 and l_object_version_number <> -1 THEN
5298 IF fir.effective_end_date = to_date('31-12-4712','dd-mm-yyyy') THEN
5299 IF fir.effective_start_date = g_date THEN
5300 l_mode := 'CORRECTION';
5301 ELSE
5302 l_mode := 'UPDATE';
5303 END IF;
5304 ELSE
5305 IF fir.effective_start_date = g_date THEN
5306 l_mode := 'CORRECTION';
5307 ELSE
5308 l_count := 0;
5309 OPEN cnt_fut_asg_rec(fir.assignment_id);
5310 FETCH cnt_fut_asg_rec INTO l_count;
5311 CLOSE cnt_fut_asg_rec;
5312
5313 IF l_count > 0 THEN
5314 l_mode := 'UPDATE_CHANGE_INSERT';
5315 ELSE
5316 l_mode := 'UPDATE';
5317 END IF;
5318
5319 END IF;
5320 END IF;
5321
5322 IF not_inserted THEN
5323 INSERT INTO FND_SESSIONS(SESSION_ID,EFFECTIVE_DATE) VALUES(USERENV('SESSIONID'),SYSDATE);
5324 COMMIT;
5325 not_inserted := false;
5326 END IF;
5327
5328 IF nvl(l_load_flag,'N') = 'N' THEN
5329 hr_au_assignment_api.update_au_emp_asg
5330 (p_validate => false
5331 ,p_effective_date => g_date
5332 ,p_datetrack_update_mode => l_mode
5333 ,p_assignment_id => fir.assignment_id
5334 ,p_object_version_number => l_object_version_number -- out parameter
5335 ,p_legal_employer_id => l_le_id
5336 ,p_lev_lod_flg => 'Y'
5337 ,p_cagr_grade_def_id => l_cagr_grade_def_id -- out parameter
5338 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments -- out parameter
5339 ,p_comment_id => l_comment_id -- out parameter
5340 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id -- out parameter
5341 ,p_effective_start_date => l_effective_start_date -- out parameter
5342 ,p_effective_end_date => l_effective_end_date -- out parameter
5343 ,p_concatenated_segments => l_concatenated_segments -- out parameter
5344 ,p_no_managers_warning => l_no_managers_warning -- out parameter
5345 ,p_other_manager_warning => l_other_manager_warning -- out parameter
5346 );
5347 COMMIT;
5348 fnd_file.new_line(FND_FILE.LOG);
5349 fnd_file.put_line(FND_FILE.LOG,'succesfully changed leave loading flag for assignment record as of 01-JUL-2012 for assignment id '||asgrec.assignment_id||' (Employee name / Employee number - ' || l_full_name ||' / ' || l_emp_num ||')');
5350 END IF;
5351 END IF;
5352 END LOOP;
5353
5354
5355 l_object_version_number := -1;
5356
5357 -- below loop is for updating all future dated assignment records
5358 FOR nxt IN fut_all_next_asg_rec(asgrec.assignment_id) LOOP
5359 l_le_id := -1;
5360 l_object_version_number := nxt.object_version_number;
5361 l_soft_code_id := nxt.soft_coding_keyflex_id;
5362 l_load_flag := null;
5363
5364 OPEN csr_get_le_id(l_soft_code_id);
5365 FETCH csr_get_le_id INTO l_le_id;
5366 CLOSE csr_get_le_id;
5367
5368 OPEN csr_get_loading_flag(l_soft_code_id);
5369 FETCH csr_get_loading_flag INTO l_load_flag;
5370 CLOSE csr_get_loading_flag;
5371
5372 IF l_le_id <> -1 and l_object_version_number <> -1 THEN
5373
5374 IF not_inserted THEN
5375 INSERT INTO FND_SESSIONS(SESSION_ID,EFFECTIVE_DATE) VALUES(USERENV('SESSIONID'),SYSDATE);
5376 COMMIT;
5377 not_inserted := false;
5378 END IF;
5379
5380 IF nvl(l_load_flag,'N') = 'N' THEN
5381
5382 hr_au_assignment_api.update_au_emp_asg
5383 (p_validate => false
5384 ,p_effective_date => nxt.effective_start_date
5385 ,p_datetrack_update_mode => 'CORRECTION'
5386 ,p_assignment_id => nxt.assignment_id
5387 ,p_object_version_number => l_object_version_number -- out parameter
5388 ,p_legal_employer_id => l_le_id
5389 ,p_lev_lod_flg => 'Y'
5390 ,p_cagr_grade_def_id => l_cagr_grade_def_id -- out parameter
5391 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments -- out parameter
5392 ,p_comment_id => l_comment_id -- out parameter
5393 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id -- out parameter
5394 ,p_effective_start_date => l_effective_start_date -- out parameter
5395 ,p_effective_end_date => l_effective_end_date -- out parameter
5396 ,p_concatenated_segments => l_concatenated_segments -- out parameter
5397 ,p_no_managers_warning => l_no_managers_warning -- out parameter
5398 ,p_other_manager_warning => l_other_manager_warning -- out parameter
5399 );
5400 COMMIT;
5401 fnd_file.new_line(FND_FILE.LOG);
5402 fnd_file.put_line(FND_FILE.LOG,'succesfully changed leave loading flag for future assignment records for assignment id '||asgrec.assignment_id||' (Employee name / Employee number - ' || l_full_name ||' / ' || l_emp_num ||')');
5403 END IF;
5404 END IF;
5405 END LOOP;
5406
5407
5408 OPEN get_prev_database_tax_fields(asgrec.element_entry_id,l_inp_value_id_table,asgrec.effective_start_date);
5409 FETCH get_prev_database_tax_fields INTO
5410 l_screen_entry_values(1)
5411 ,l_screen_entry_values(2)
5412 ,l_screen_entry_values(3)
5413 ,l_screen_entry_values(4)
5414 ,l_screen_entry_values(5)
5415 ,l_screen_entry_values(6)
5416 ,l_screen_entry_values(7)
5417 ,l_screen_entry_values(8)
5418 ,l_screen_entry_values(9)
5419 ,l_screen_entry_values(10)
5420 ,l_screen_entry_values(11)
5421 ,l_screen_entry_values(12)
5422 ,l_screen_entry_values(13)
5423 ,l_screen_entry_values(14)
5424 ,l_screen_entry_values(15),l_entry_information;
5425 CLOSE get_prev_database_tax_fields;
5426
5427
5428 -- below loop is for updating all first element entry records for tax information ( first means record which contains 01-jul-2012)
5429 FOR elerec IN fut_fir_ele_rec(asgrec.element_entry_id) LOOP
5430 l_object_version_number := -1;
5431 l_business_group_id := -1;
5432
5433 IF elerec.effective_end_date = to_date('31-12-4712','dd-mm-yyyy') THEN
5434 IF elerec.effective_start_date = g_date THEN
5435 l_mode := 'CORRECTION';
5436 ELSE
5437 l_mode := 'UPDATE';
5438 END IF;
5439 ELSE
5440 IF elerec.effective_start_date = g_date THEN
5441 l_mode := 'CORRECTION';
5442 ELSE
5443 l_count := 0;
5444 OPEN cnt_fut_ele_rec(elerec.element_entry_id);
5445 FETCH cnt_fut_ele_rec INTO l_count;
5446 CLOSE cnt_fut_ele_rec;
5447
5448 IF l_count > 0 THEN
5449 l_mode := 'UPDATE_CHANGE_INSERT';
5450 ELSE
5451 l_mode := 'UPDATE';
5452 END IF;
5453 END IF;
5454 END IF;
5455
5456 OPEN get_bg_id(elerec.assignment_id);
5457 FETCH get_bg_id INTO l_business_group_id;
5458 CLOSE get_bg_id;
5459
5460 l_object_version_number := elerec.object_version_number;
5461
5462 IF l_object_version_number <> -1 and l_business_group_id <> -1 THEN
5463 pay_element_entry_api.update_element_entry
5464 (p_validate => false
5465 ,p_datetrack_update_mode => l_mode
5466 ,p_effective_date => g_date
5467 ,p_business_group_id => l_business_group_id
5468 ,p_element_entry_id => elerec.element_entry_id
5469 ,p_object_version_number => l_object_version_number
5470 ,p_input_value_id1 => l_inp_value_id_table(1)
5471 ,p_input_value_id2 => l_inp_value_id_table(2)
5472 ,p_input_value_id3 => l_inp_value_id_table(3)
5473 ,p_input_value_id4 => l_inp_value_id_table(4)
5474 ,p_input_value_id5 => l_inp_value_id_table(5)
5475 ,p_input_value_id6 => l_inp_value_id_table(6)
5476 ,p_input_value_id7 => l_inp_value_id_table(7)
5477 ,p_input_value_id8 => l_inp_value_id_table(8)
5478 ,p_input_value_id9 => l_inp_value_id_table(9)
5479 ,p_input_value_id10 => l_inp_value_id_table(10)
5480 ,p_input_value_id11 => l_inp_value_id_table(11)
5481 ,p_input_value_id12 => l_inp_value_id_table(12)
5482 ,p_input_value_id13 => l_inp_value_id_table(13)
5483 ,p_input_value_id14 => l_inp_value_id_table(14)
5484 ,p_input_value_id15 => l_inp_value_id_table(15)
5485 ,p_entry_value1 => l_screen_entry_values(1)
5486 ,p_entry_value2 => l_screen_entry_values(2)
5487 ,p_entry_value3 => l_screen_entry_values(3)
5488 ,p_entry_value4 => l_screen_entry_values(4)
5489 ,p_entry_value5 => l_screen_entry_values(5)
5490 ,p_entry_value6 => l_screen_entry_values(6)
5491 ,p_entry_value7 => fnd_date.canonical_to_date(l_screen_entry_values(7))
5492 ,p_entry_value8 => l_screen_entry_values(8)
5493 ,p_entry_value9 => l_screen_entry_values(9)
5494 ,p_entry_value10 => l_screen_entry_values(10)
5495 ,p_entry_value11 => l_screen_entry_values(11)
5496 ,p_entry_value12 => l_screen_entry_values(12)
5497 ,p_entry_value13 => '2'
5498 ,p_entry_value14 => l_screen_entry_values(14)
5499 ,p_entry_value15 => l_screen_entry_values(15)
5500 ,p_effective_start_date => l_effective_start_date
5501 ,p_effective_end_date => l_effective_end_date
5502 ,p_update_warning => l_update_warning
5503 ,p_override_user_ent_chk => 'Y'
5504 );
5505
5506 COMMIT;
5507 fnd_file.new_line(FND_FILE.LOG);
5508 fnd_file.put_line(FND_FILE.LOG,'succesfully changed tax scale for element record as of 01-JUL-2012 for assignment id '||asgrec.assignment_id||' (Employee name / Employee number - ' || l_full_name ||' / ' || l_emp_num ||')');
5509 END IF;
5510
5511 END LOOP;
5512
5513 -- below loop is for updating all future dated element entry records for tax information
5514 FOR nxtele IN fut_all_next_ele_rec(asgrec.element_entry_id) LOOP
5515 l_object_version_number := -1;
5516 l_business_group_id := -1;
5517
5518 OPEN get_bg_id(nxtele.assignment_id);
5519 FETCH get_bg_id INTO l_business_group_id;
5520 CLOSE get_bg_id;
5521
5522 l_object_version_number := nxtele.object_version_number;
5523
5524 IF l_object_version_number <> -1 and l_business_group_id <> -1 THEN
5525 IF l_screen_entry_values(13) = '7' THEN
5526
5527 pay_element_entry_api.update_element_entry
5528 (p_validate => false
5529 ,p_datetrack_update_mode => 'CORRECTION'
5530 ,p_effective_date => nxtele.effective_start_date
5531 ,p_business_group_id => l_business_group_id
5532 ,p_element_entry_id => nxtele.element_entry_id
5533 ,p_object_version_number => l_object_version_number
5534 ,p_input_value_id1 => l_inp_value_id_table(1)
5535 ,p_input_value_id2 => l_inp_value_id_table(2)
5536 ,p_input_value_id3 => l_inp_value_id_table(3)
5537 ,p_input_value_id4 => l_inp_value_id_table(4)
5538 ,p_input_value_id5 => l_inp_value_id_table(5)
5539 ,p_input_value_id6 => l_inp_value_id_table(6)
5540 ,p_input_value_id7 => l_inp_value_id_table(7)
5541 ,p_input_value_id8 => l_inp_value_id_table(8)
5542 ,p_input_value_id9 => l_inp_value_id_table(9)
5543 ,p_input_value_id10 => l_inp_value_id_table(10)
5544 ,p_input_value_id11 => l_inp_value_id_table(11)
5545 ,p_input_value_id12 => l_inp_value_id_table(12)
5546 ,p_input_value_id13 => l_inp_value_id_table(13)
5547 ,p_input_value_id14 => l_inp_value_id_table(14)
5548 ,p_input_value_id15 => l_inp_value_id_table(15)
5549 ,p_entry_value1 => l_screen_entry_values(1)
5550 ,p_entry_value2 => l_screen_entry_values(2)
5551 ,p_entry_value3 => l_screen_entry_values(3)
5552 ,p_entry_value4 => l_screen_entry_values(4)
5553 ,p_entry_value5 => l_screen_entry_values(5)
5554 ,p_entry_value6 => l_screen_entry_values(6)
5555 ,p_entry_value7 => fnd_date.canonical_to_date(l_screen_entry_values(7))
5556 ,p_entry_value8 => l_screen_entry_values(8)
5557 ,p_entry_value9 => l_screen_entry_values(9)
5558 ,p_entry_value10 => l_screen_entry_values(10)
5559 ,p_entry_value11 => l_screen_entry_values(11)
5560 ,p_entry_value12 => l_screen_entry_values(12)
5561 ,p_entry_value13 => '2'
5562 ,p_entry_value14 => l_screen_entry_values(14)
5563 ,p_entry_value15 => l_screen_entry_values(15)
5564 ,p_effective_start_date => l_effective_start_date
5565 ,p_effective_end_date => l_effective_end_date
5566 ,p_update_warning => l_update_warning
5567 ,p_override_user_ent_chk => 'Y'
5568 );
5569
5570 COMMIT;
5571 fnd_file.new_line(FND_FILE.LOG);
5572 fnd_file.put_line(FND_FILE.LOG,'succesfully changed tax scale for future element records for assignment id '||asgrec.assignment_id||' (Employee name / Employee number - ' || l_full_name ||' / ' || l_emp_num ||')');
5573 END IF;
5574 END IF;
5575 END LOOP;
5576
5577
5578 END IF;
5579
5580 END LOOP;
5581
5582 -- below code is for scenarios where open ended element entry , single/multiple future dated records for assignment
5583 FOR asgrec IN csr_ele_entries(l_element_type_id,l_inp_value_id_table(13))
5584 LOOP
5585 l_asg_exist := 0;
5586 l_full_name := null;
5587 l_emp_num := null;
5588 l_load_flag := null;
5589
5590 OPEN get_bg_id(asgrec.assignment_id);
5591 FETCH get_bg_id INTO l_business_group_id;
5592 CLOSE get_bg_id;
5593
5594 IF l_business_group_id = p_business_group_id THEN
5595
5596 OPEN csr_fut_asg_rec(asgrec.assignment_id);
5597 FETCH csr_fut_asg_rec INTO l_asg_exist;
5598 CLOSE csr_fut_asg_rec;
5599
5600 OPEN get_emp_details(asgrec.assignment_id) ;
5601 FETCH get_emp_details INTO l_full_name , l_emp_num;
5602 CLOSE get_emp_details;
5603
5604 IF l_asg_exist > 0 THEN
5605 l_le_id := -1;
5606 l_object_version_number := -1;
5607
5608 -- below loop is for updating all first assignment records ( first means record which contains 01-jul-2012)
5609 FOR fir IN fut_fir_asg_rec(asgrec.assignment_id) LOOP
5610
5611 l_object_version_number := fir.object_version_number;
5612 l_soft_code_id := fir.soft_coding_keyflex_id;
5613 l_load_flag := null;
5614
5615 OPEN csr_get_le_id(l_soft_code_id);
5616 FETCH csr_get_le_id INTO l_le_id;
5617 CLOSE csr_get_le_id;
5618
5619 OPEN csr_get_loading_flag(l_soft_code_id);
5620 FETCH csr_get_loading_flag INTO l_load_flag;
5621 CLOSE csr_get_loading_flag;
5622
5623 IF l_le_id <> -1 and l_object_version_number <> -1 THEN
5624 IF fir.effective_end_date = to_date('31-12-4712','dd-mm-yyyy') THEN
5625 IF fir.effective_start_date = g_date THEN
5626 l_mode := 'CORRECTION';
5627 ELSE
5628 l_mode := 'UPDATE';
5629 END IF;
5630 ELSE
5631 IF fir.effective_start_date = g_date THEN
5632 l_mode := 'CORRECTION';
5633 ELSE
5634 l_count := 0;
5635 OPEN cnt_fut_asg_rec(fir.assignment_id);
5636 FETCH cnt_fut_asg_rec INTO l_count;
5637 CLOSE cnt_fut_asg_rec;
5638
5639 IF l_count > 0 THEN
5640 l_mode := 'UPDATE_CHANGE_INSERT';
5641 ELSE
5642 l_mode := 'UPDATE';
5643 END IF;
5644
5645 END IF;
5646 END IF;
5647
5648 IF not_inserted THEN
5649 INSERT INTO FND_SESSIONS(SESSION_ID,EFFECTIVE_DATE) VALUES(USERENV('SESSIONID'),SYSDATE);
5650 COMMIT;
5651 not_inserted := false;
5652 END IF;
5653
5654 IF nvl(l_load_flag,'N') = 'N' THEN
5655
5656 hr_au_assignment_api.update_au_emp_asg
5657 (p_validate => false
5658 ,p_effective_date => g_date
5659 ,p_datetrack_update_mode => l_mode
5660 ,p_assignment_id => fir.assignment_id
5661 ,p_object_version_number => l_object_version_number -- out parameter
5662 ,p_legal_employer_id => l_le_id
5663 ,p_lev_lod_flg => 'Y'
5664 ,p_cagr_grade_def_id => l_cagr_grade_def_id -- out parameter
5665 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments -- out parameter
5666 ,p_comment_id => l_comment_id -- out parameter
5667 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id -- out parameter
5668 ,p_effective_start_date => l_effective_start_date -- out parameter
5669 ,p_effective_end_date => l_effective_end_date -- out parameter
5670 ,p_concatenated_segments => l_concatenated_segments -- out parameter
5671 ,p_no_managers_warning => l_no_managers_warning -- out parameter
5672 ,p_other_manager_warning => l_other_manager_warning -- out parameter
5673 );
5674 COMMIT;
5675 fnd_file.new_line(FND_FILE.LOG);
5676 fnd_file.put_line(FND_FILE.LOG,'succesfully changed leave loading flag for assignment record as of 01-JUL-2012 for assignment id '||asgrec.assignment_id||' (Employee name / Employee number - ' || l_full_name ||' / ' || l_emp_num ||')');
5677 END IF;
5678 END IF;
5679 END LOOP;
5680
5681 l_object_version_number := -1;
5682
5683 -- below loop is for updating all future dated assignment records
5684 FOR nxt IN fut_all_next_asg_rec(asgrec.assignment_id) LOOP
5685 l_le_id := -1;
5686 l_object_version_number := nxt.object_version_number;
5687 l_soft_code_id := nxt.soft_coding_keyflex_id;
5688 l_load_flag := null;
5689
5690 OPEN csr_get_le_id(l_soft_code_id);
5691 FETCH csr_get_le_id INTO l_le_id;
5692 CLOSE csr_get_le_id;
5693
5694 OPEN csr_get_loading_flag(l_soft_code_id);
5695 FETCH csr_get_loading_flag INTO l_load_flag;
5696 CLOSE csr_get_loading_flag;
5697
5698 IF l_le_id <> -1 and l_object_version_number <> -1 THEN
5699
5700 IF not_inserted THEN
5701 INSERT INTO FND_SESSIONS(SESSION_ID,EFFECTIVE_DATE) VALUES(USERENV('SESSIONID'),SYSDATE);
5702 COMMIT;
5703 not_inserted := false;
5704 END IF;
5705
5706 IF nvl(l_load_flag,'N') = 'N' THEN
5707
5708 hr_au_assignment_api.update_au_emp_asg
5709 (p_validate => false
5710 ,p_effective_date => nxt.effective_start_date
5711 ,p_datetrack_update_mode => 'CORRECTION'
5712 ,p_assignment_id => nxt.assignment_id
5713 ,p_object_version_number => l_object_version_number -- out parameter
5714 ,p_legal_employer_id => l_le_id
5715 ,p_lev_lod_flg => 'Y'
5716 ,p_cagr_grade_def_id => l_cagr_grade_def_id -- out parameter
5717 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments -- out parameter
5718 ,p_comment_id => l_comment_id -- out parameter
5719 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id -- out parameter
5720 ,p_effective_start_date => l_effective_start_date -- out parameter
5721 ,p_effective_end_date => l_effective_end_date -- out parameter
5722 ,p_concatenated_segments => l_concatenated_segments -- out parameter
5723 ,p_no_managers_warning => l_no_managers_warning -- out parameter
5724 ,p_other_manager_warning => l_other_manager_warning -- out parameter
5725 );
5726 COMMIT;
5727
5728 fnd_file.new_line(FND_FILE.LOG);
5729 fnd_file.put_line(FND_FILE.LOG,'succesfully changed leave loading flag for future assignment records for assignment id '||asgrec.assignment_id||' (Employee name / Employee number - ' || l_full_name ||' / ' || l_emp_num ||')');
5730 END IF;
5731 END IF;
5732 END LOOP;
5733
5734
5735
5736 -- now write the code below for upgrading the open ended element entry
5737
5738 OPEN get_prev_database_tax_fields(asgrec.element_entry_id,l_inp_value_id_table,asgrec.effective_start_date);
5739 FETCH get_prev_database_tax_fields INTO
5740 l_screen_entry_values(1)
5741 ,l_screen_entry_values(2)
5742 ,l_screen_entry_values(3)
5743 ,l_screen_entry_values(4)
5744 ,l_screen_entry_values(5)
5745 ,l_screen_entry_values(6)
5746 ,l_screen_entry_values(7)
5747 ,l_screen_entry_values(8)
5748 ,l_screen_entry_values(9)
5749 ,l_screen_entry_values(10)
5750 ,l_screen_entry_values(11)
5751 ,l_screen_entry_values(12)
5752 ,l_screen_entry_values(13)
5753 ,l_screen_entry_values(14)
5754 ,l_screen_entry_values(15),l_entry_information;
5755 CLOSE get_prev_database_tax_fields;
5756
5757 IF asgrec.effective_start_date = g_date THEN
5758 l_mode := 'CORRECTION';
5759 ELSE
5760 l_mode := 'UPDATE';
5761 END IF;
5762
5763 OPEN get_bg_id(asgrec.assignment_id);
5764 FETCH get_bg_id INTO l_business_group_id;
5765 CLOSE get_bg_id;
5766
5767 l_object_version_number := asgrec.object_version_number;
5768
5769 pay_element_entry_api.update_element_entry
5770 (p_validate => false
5771 ,p_datetrack_update_mode => l_mode
5772 ,p_effective_date => g_date
5773 ,p_business_group_id => l_business_group_id
5774 ,p_element_entry_id => asgrec.element_entry_id
5775 ,p_object_version_number => l_object_version_number
5776 ,p_input_value_id1 => l_inp_value_id_table(1)
5777 ,p_input_value_id2 => l_inp_value_id_table(2)
5778 ,p_input_value_id3 => l_inp_value_id_table(3)
5779 ,p_input_value_id4 => l_inp_value_id_table(4)
5780 ,p_input_value_id5 => l_inp_value_id_table(5)
5781 ,p_input_value_id6 => l_inp_value_id_table(6)
5782 ,p_input_value_id7 => l_inp_value_id_table(7)
5783 ,p_input_value_id8 => l_inp_value_id_table(8)
5784 ,p_input_value_id9 => l_inp_value_id_table(9)
5785 ,p_input_value_id10 => l_inp_value_id_table(10)
5786 ,p_input_value_id11 => l_inp_value_id_table(11)
5787 ,p_input_value_id12 => l_inp_value_id_table(12)
5788 ,p_input_value_id13 => l_inp_value_id_table(13)
5789 ,p_input_value_id14 => l_inp_value_id_table(14)
5790 ,p_input_value_id15 => l_inp_value_id_table(15)
5791 ,p_entry_value1 => l_screen_entry_values(1)
5792 ,p_entry_value2 => l_screen_entry_values(2)
5793 ,p_entry_value3 => l_screen_entry_values(3)
5794 ,p_entry_value4 => l_screen_entry_values(4)
5795 ,p_entry_value5 => l_screen_entry_values(5)
5796 ,p_entry_value6 => l_screen_entry_values(6)
5797 ,p_entry_value7 => fnd_date.canonical_to_date(l_screen_entry_values(7))
5798 ,p_entry_value8 => l_screen_entry_values(8)
5799 ,p_entry_value9 => l_screen_entry_values(9)
5800 ,p_entry_value10 => l_screen_entry_values(10)
5801 ,p_entry_value11 => l_screen_entry_values(11)
5802 ,p_entry_value12 => l_screen_entry_values(12)
5803 ,p_entry_value13 => '2'
5804 ,p_entry_value14 => l_screen_entry_values(14)
5805 ,p_entry_value15 => l_screen_entry_values(15)
5806 ,p_effective_start_date => l_effective_start_date
5807 ,p_effective_end_date => l_effective_end_date
5808 ,p_update_warning => l_update_warning
5809 ,p_override_user_ent_chk => 'Y'
5810 );
5811
5812 COMMIT;
5813
5814 fnd_file.new_line(FND_FILE.LOG);
5815 fnd_file.put_line(FND_FILE.LOG,'succesfully changed tax scale for element record as of 01-JUL-2012 for assignment id '||asgrec.assignment_id||' (Employee name / Employee number - ' || l_full_name ||' / ' || l_emp_num ||')');
5816
5817 ELSIF l_full_name is null and l_emp_num is null THEN
5818 fnd_file.new_line(FND_FILE.LOG);
5819 fnd_file.put_line(FND_FILE.LOG,'assignment record does not exist for assignment id '||asgrec.assignment_id|| '. probably a case of data corruption. please insert record and then upgrade');
5820 fnd_file.new_line(FND_FILE.LOG);
5821 END IF;
5822
5823 END IF;
5824 END LOOP;
5825
5826 END IF;
5827
5828 exception
5829 when others then
5830
5831 error_code :=SQLCODE;
5832 error_message := SQLERRM ;
5833
5834 FND_FILE.PUT_LINE(FND_FILE.LOG,' ***** ERRORS ***** ');
5835 FND_FILE.PUT_LINE(FND_FILE.LOG,' Report to Oracle Support.');
5836 FND_FILE.PUT_LINE(FND_FILE.LOG,error_code ||' : '||error_message);
5837
5838 raise;
5839
5840 END upgrade_tax_scale;
5841
5842 end pay_au_paye_ff;
[Home] [Help]