4 ** Copyright (c) 1999 Oracle Corporation
1 PACKAGE pay_au_paye_ff AUTHID CURRENT_USER AS
2 /* $Header: pyaufmla.pkh 120.23.12020000.5 2012/07/04 21:06:04 amnaraya ship $
3 **
5 ** All Rights Reserved
6 **
7 ** Procedures and functions used in NZ 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.0 Added Terminations
16 ** 16-SEP-2000 sclarke 115.1 Removed Terminations, now in pay_au_terminations
17 ** 20-DEC-2000 srikrish 115.2 Created function paid_periods_since_hire_date
18 ** which returns number of paid pay periods
19 ** 19-DEC-2000 abajpai 115.2 Added new function convert_to_period_amt, round_amount
20 ** ============== Formula Fuctions ====================
21 ** Package containing addition processing required by
22 ** formula in AU localisatons.
23 ** 28-NOV-2001 nnaresh 115.5 Updated for GSCC Standards
24 ** 8-JAN-2002 apunekar 115.6 Additional functions added.
25 ** 18-May-2002 apunekar 115.6 Additional function added.
26 ** 17-Sep-2002 Ragovind 115.13 Modified the function check_fixed_deduction declaration
27 ** 03-Dec-2002 Ragovind 115.14 Added NOCOPY for the function get_retro_period.
28 ** 14-Apr-2003 Vgsriniv 115.15 Added the extra parameter to the function
29 ** periods_since_hire_date
30 ** 22-Aug-2003 srrajago 115.16 Added a new formula function 'validate_data_magtape'.
31 ** Bug reference : 3091834
32 ** 03-Nov-2003 punmehta 115.17 Bug# 2977425 - Added the new formula function
33 ** 23-Dec-2003 punmehta 115.18 Bug# 3306112 - Added the new formula function
34 ** 06-Feb-2004 punmehta 115.19 Bug# 3245909 - Added a new function get_pp_action for AU_Payments route
35 ** 09-AUG-2004 abhkumar 115.20 Bug# 2610141 - Modfied the code to support Legal Employer changes for an assignment.
36 ** 08-SEP-2004 abhkumar 115.21 Bug# 2610141 - Added a new parameter to functions periods_since_hire_date and paid_periods_since_hire_date
37 *** 26-Apr-2005 abhkumar 115.22 Bug# 3935471 - Changes due to Retro Tax enhancement.
38 ** 07-Jun-2005 abhkumar 115.23 Bug# 4415795 - Added new parameter to count_retro_periods.
39 *** 23-Jun-2005 abhkumar 115.24 Bug#4438644 - Modified function paid_periods_since_hire_date
40 *** 26-Jun-2005 avenkatk 115.25 Bug#4451088 - Modified function paid_periods_since_hire_date
41 *** 27-Jun-2005 ksingla 115.26 Bug#4456720 - Added a new function CALCULATE_ASG_PREV_VALUE for negative retro earnings
42 *** 05-JuL-2005 abhkumar 115.27 Bug#4467198 - Modified function CALCULATE_ASG_PREV_VALUE for zero average earnings
43 *** 13-Jul-2005 abhargav 115.28 Bug#4363057 - Modified function CALCULATE_ASG_PREV_VALUE to include fix for bug# 3855355 .
44 ** 14-Jul-2005 abhkumar 115.29 Bug#4418107 - Added new context (tax_unit_id) to function count_retro_periods and get_retro_periods
45 ** 05-Oct-2006 avenkatk 115.30 Bug#5556260 - Introduced new function - get_enhanced_retro_period to get the dates and time spans
46 ** for Enhanced Retropay.
47 ** 17-Jan-2006 avenkatk 115.31 Bug#5846272 - Introduced new functions,
48 ** i. check_if_enhanced_retro
49 ** ii. get_retro_time_span
50 ** 10-Apr-2007 abhargav 115.33 Bug#5934468 Added new function get_spread_earning() this function gets called from
51 formula AU_HECS_DEDUCTION and AU_SFSS_DEDUCTION.
52 ** 17-Jan-2008 skshin 115.34 Bug#6669058 Added new function get_retro_spread_earning() this function gets called from
53 formula AU_HECS_DEDUCTION and AU_SFSS_DEDUCTION.
54 ** 15-FEB-2008 skshin 115.35 Bug#6809877 Added new function get_etp_pay_component.
55 ** 29-APR-2009 skshin 115.38 Bug#7665727 Created count_retro_periods_2009 function
56 ** 21-MAY-2009 skshin 115.39 Bug#8406009 Added calc_average_earnings and calc_lt12_prev_spread_tax functions
57 ** 30-JUL-2009 skshin 115.40 Bug#8725341 Added Earnings_Leave_Loading balance to c_get_ytd_def_bal_ids cursor
58 ** 08-Oct-2009 avenkatk 115.41 Bug#8765082 Added New Function get_retro_leave_load
59 ** 06-Oct-2010 avenkatk 115.42 Bug#9775171 Added new global variable g_lumpsum_d
63 ** 17-Apr-2012 skshin 115.46 Bug#13879232 Added a new function get_addtl_message
60 ** 24-Aug-2011 skshin 115.43 Bug#12873456 Added new function get_retro_tax_var_fixed
61 ** 21-Sep-2011 skshin 115.44 Bug#12989692 Modified calc_lt12_prev_spread_tax function for retro lt12_prev flood levy
62 ** 11-Oct-2011 dduvvuri 115.45 Bug#12767287 Added a new formula function get_earns_flood_levy for Qantas special case.
64 ** 14-May-2012 dduvvuri 115.47 Bug#13906256 Added a new function get_mls_rate
65 ** 22-May-2012 dduvvuri 115.48 Bug#14100809 Added new function get_tax_scale_on_dp and new procedure upgrade_tax_scale
66 */
67
68 g_lumpsum_d number; -- bug 9775171
69
70 cursor c_get_creator_type(c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
71 c_date_earned in pay_payroll_actions.date_earned%TYPE
72 ) is
73 SELECT creator_type
74 FROM pay_element_entries_f pee
75 WHERE pee.element_entry_id=c_element_entry_id
76 and c_date_earned between pee.effective_start_date and pee.effective_end_date;
77
78
79 function get_mls_rate
80 ( p_element_entry_id IN NUMBER
81 ,p_date IN DATE
82 ,p_spouse IN VARCHAR2
83 )
84 return number;
85
86 /* Function to get the tax scale as of date paid */
87 function get_tax_scale_on_dp
88 ( p_element_entry_id IN NUMBER
89 ,p_date IN DATE
90 )
91 return varchar2;
92
93 /*
94 * round_to_5c = rounds values to nearest 5c using
95 * ATO rules
96 */
97
98 function round_to_5c
99 (
100 p_actual_amt in number
101 )
102 return number;
103
104 /*
105 * convert_to_period - converts weekly equivalents
106 * back to the period amounts using ATO rules.
107 */
108
109 function convert_to_period
110 (
111 p_ann_freq in number,
112 p_amt_week in number
113 )
114 return number;
115 /*
116 * convert_to_week - converts period amounts to equivalents
117 * weekly equivalents using ATO rules.
118 */
119
120 function convert_to_week
121 (
122 p_ann_freq in number,
123 p_amt_period in number
124 )
125 return number;
126 /*
127 * periods_since_hire_date - returns the number of periods in the
128 * current tax year since the hire date.
129 */
130
131 /* Bug:2900253. Added the extra context parameter p_assignment_id */
132 function periods_since_hire_date
133 (
134 p_payroll_id in number,
135 p_assignment_id in per_all_assignments_f.assignment_id%type,
136 p_tax_unit_id in pay_assignment_actions.tax_unit_id%type, --2610141
137 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type, /*Bug 4451088 */
138 p_period_num in number,
139 p_period_start in date,
140 p_emp_hire_date in date,
141 p_use_tax_flag IN VARCHAR2 --2610141
142 )
143 return number;
144
145 function paid_periods_since_hire_date
146 (
147 p_payroll_id in number,
148 p_assignment_id in number,
149 p_tax_unit_id in number, --2610141
150 p_assignment_action_id IN number, /*Bug 4438644 */
151 p_period_num in number,
152 p_period_start in date,
153 p_emp_hire_date in date,
154 p_use_tax_flag IN VARCHAR2 --2610141
155 )
156 return number;
157
158 /* Bug 4456720 - Added a new function to calculate the earnings_total and
159 per tax spread deductions for the previous year when total average earnings are negative */
160 /* Bug#4467198 - Modified the function to take care of legal employer changes. Introduced following
161 parameters in the function p_use_tax_flag, p_payroll_id, p_assignment_action_id*/
162 FUNCTION calculate_asg_prev_value
163 ( p_assignment_id in per_all_assignments_f.assignment_id%TYPE,
164 p_business_group_id in hr_all_organization_units.organization_id%TYPE,
165 p_date_earned in date,
166 p_tax_unit_id in hr_all_organization_units.organization_id%TYPE,
167 p_assignment_action_id IN number, /* Bug#4467198*/
168 p_payroll_id IN NUMBER, /* Bug#4467198*/
169 p_period_start_date in date,
170 p_case out NOCOPY varchar2,
171 p_earnings_standard out NOCOPY number,
172 p_pre_tax_spread out NOCOPY number,
173 p_pre_tax_fixed out NOCOPY number, /*bug4363057*/
174 p_pre_tax_prog out NOCOPY number, /*bug4363057*/
175 p_paid_periods out NOCOPY number,
176 p_use_tax_flag IN VARCHAR2 /* Bug#4467198*/
177 )
178 return NUMBER ;
179
180 -----------------------------------------------------------------------
181 -- Cursor : c_get_ytd_def_bal_ids
182 -- Description : To get the YTD defined balance ids for the balances
183 -- Earnings_Standard and Pre Tax Deductions
184 --
185 -----------------------------------------------------------------------
186 CURSOR c_get_ytd_def_bal_ids (c_db_item_suffix IN pay_balance_dimensions.DATABASE_ITEM_SUFFIX%type)
187 IS
188 SELECT pdb.defined_balance_id, pbt.balance_name, pbd.DIMENSION_NAME
189 FROM pay_balance_types pbt,
190 pay_balance_dimensions pbd,
191 pay_defined_balances pdb
192 WHERE pbt.balance_name in ( 'Earnings_Standard'
193 ,'Pre Tax Spread Deductions'
197 AND pbt.balance_type_id = pdb.balance_type_id
194 ,'Pre Tax Fixed Deductions' /*bug4363057*/
195 ,'Pre Tax Progressive Deductions'
196 ,'Earnings_Leave_Loading') /*bug8725341*/
198 AND pdb.balance_dimension_id = pbd.balance_dimension_id
199 AND pbd.DATABASE_ITEM_SUFFIX = c_db_item_suffix --2610141
200 AND pbt.legislation_code = 'AU'
201 and pbt.legislation_code = pbd.legislation_code
202 AND pbd.legislation_code = 'AU';
203
204
205 TYPE g_ytd_tab_bals IS TABLE OF c_get_ytd_def_bal_ids%rowtype INDEX BY BINARY_INTEGER;
206 g_ytd_bals g_ytd_tab_bals;
207
208 g_ytd_def_bals_populated BOOLEAN;
209
210 -- BBR Tables to store YTD balance details
211 --
212 g_ytd_input_table pay_balance_pkg.t_balance_value_tab;
213 g_ytd_result_table pay_balance_pkg.t_detailed_bal_out_tab;
214 g_ytd_context_table pay_balance_pkg.t_context_tab;
215
216
217
218
219
220
221 /*
222 * round_amount rounds values to nearest dollar
223 * new ATO Rounding rules effective from year 2000
224 */
225
226 function round_amt
227 (
228 p_actual_amt in number,
229 p_tax_scale in number
230 )
231 return number;
232
233 /*
234 * convert_to_period - converts weekly equivalents
235 * back to the period amounts using new ATO rules effective from year 2000
236 */
237
238 function convert_to_period_amt
239 (
240 p_ann_freq in number,
241 p_amt_week in number,
242 p_tax_scale in number
243 )
244 return number;
245
246 function check_if_retro
247 (
248 p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
249 p_date_earned in pay_payroll_actions.date_earned%TYPE
250
251 )return varchar2;
252
253
254 function get_retro_period
255 (
256 p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
257 p_date_earned in pay_payroll_actions.date_earned%TYPE,
258 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE, /*Bug 4418107*/
259 p_retro_start_date out NOCOPY date,
260 p_retro_end_date out NOCOPY date
261 )return number;
262
263 function count_retro_periods
264 (
265 p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
266 p_date_earned in pay_payroll_actions.date_earned%TYPE,
267 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE, /*Bug 4418107*/
268 p_use_tax_flag IN VARCHAR2, --4415795
269 p_mode IN VARCHAR2 DEFAULT 'E' --7665727
270 )return number;
271
272 /*bug 7665727*/
273 function count_retro_periods_2009
274 (
275 p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
276 p_date_earned in pay_payroll_actions.date_earned%TYPE,
277 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE, /*Bug 4418107*/
281
278 p_use_tax_flag IN VARCHAR2, --4415795
279 p_mode IN VARCHAR2 --7665727
280 )return number;
282
283
284 function calculate_tax(p_date_earned in pay_payroll_actions.date_earned%TYPE,
285 p_period_amount in number,
286 p_period_frequency in number,
287 p_tax_scale in number,
288 p_a1_variable in number,
289 p_b1_variable in number
290 )return number;
291
292
293 function check_fixed_deduction(p_assignment_id in per_all_assignments_f.assignment_id%TYPE, p_date_earned in date)
294 return varchar2;
295
296 /* Bug No : 2977425 - Added the new formula function */
297 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)
298 RETURN VARCHAR2;
299
300 /* Bug No : 3091834 - Added the new formula function below */
301
302 FUNCTION validate_data_magtape
303 (
304 p_data varchar2
305 ) RETURN varchar2;
306
307
308 /* Bug No : 3306112 - The new function will be called from view "pay_au_asg_element_payments_v"
309 It return value of Hours in case the element_id passed is attached to the Salary Basis
310 */
311 FUNCTION get_salary_basis_hours
312 (
313 p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
314 p_element_type_id in pay_element_entries_f.element_entry_id%TYPE,
315 p_pay_bases_id in per_all_assignments_f.pay_basis_id%TYPE
316 )
317 RETURN NUMBER;
318
319 /* Bug :3245909 - This function wil be used in AU_PAYMENTs route to get the pre payemnt actin id
320 */
321 FUNCTION get_pp_action_id
322 (
323 p_action_type in varchar2,
324 p_action_id in number
325 ) RETURN number;
326
327 /*Bug# 3935471*/
328 FUNCTION check_tax_unit_id
329 (
330 p_assignment_action_id NUMBER,
331 p_tax_unit_id NUMBER
332 ) RETURN VARCHAR2;
333
334 /* Bug 5556260 - In Enhanced Retropay, this function will be called to get the retro period
335 and other related information for an element entry
336 */
337 FUNCTION get_enhanced_retro_period
338 (
339 p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE,
340 p_date_earned IN pay_payroll_actions.date_earned%TYPE,
341 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
342 p_retro_start_date OUT NOCOPY date,
343 p_retro_end_date OUT NOCOPY date,
344 p_orig_effective_date OUT NOCOPY date,
345 p_retro_effective_date OUT NOCOPY date,
346 p_time_span OUT NOCOPY varchar2
347 )return number;
348
349
350 /* Bug 5846272 - Function checks if Enhanced Retropay is enabled in system.
351 */
352
353 FUNCTION check_if_enhanced_retro
354 (
355 p_business_group_id IN per_business_groups.business_group_id%TYPE
356 )RETURN VARCHAR2;
357
358 /* Bug 5846272 - Function checks and returns the Retro time span
359 for element entry for Enhanced Retropay
360 */
361
365 p_date_earned IN pay_payroll_actions.date_earned%TYPE,
362 FUNCTION get_retro_time_span
363 (
364 p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE,
366 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
367 p_retro_start_date OUT NOCOPY date,
368 p_retro_end_date OUT NOCOPY date,
369 p_orig_effective_date OUT NOCOPY date,
370 p_retro_effective_date OUT NOCOPY date,
371 p_time_span OUT NOCOPY varchar2,
372 p_retro_type OUT NOCOPY varchar2
373 )return number;
374 /* Bug#5934468 */
375 function get_spread_earning
376 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
377 p_date_paid in date,
378 p_pre_tax in number,
379 p_spread_earning in number) return number;
380
381 function get_retro_spread_earning
382 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
383 p_date_paid in date,
384 p_pre_tax in number,
385 p_spread_earning in number) return number;
386
387 function get_etp_pay_component
388 ( p_assignment_id in per_all_assignments_f.assignment_id%type,
389 p_date_earned in date) return varchar2;
390
391 FUNCTION calc_average_earnings
392 (p_assignment_id IN pay_assignment_actions.assignment_id%TYPE
393 ,p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
394 ,p_payroll_id IN pay_payroll_actions.payroll_id%TYPE
395 ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
396 ,p_business_group_id IN per_business_groups.business_group_id%TYPE
397 ,p_date_earned IN pay_payroll_actions.date_earned%TYPE
398 ,p_period_start_date IN DATE
399 ,p_emp_hire_date IN DATE
400 ,p_earnings_std_ytd IN NUMBER
401 ,p_earnings_std_ptd IN NUMBER
402 ,p_taxable_value IN NUMBER
403 ,p_average_earnings OUT NOCOPY NUMBER
404 ,p_case OUT NOCOPY VARCHAR2)
405 RETURN NUMBER;
406
407 FUNCTION calc_lt12_prev_spread_tax
408 (p_assignment_id IN pay_assignment_actions.assignment_id%TYPE
409 ,p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
410 ,p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE
411 ,p_date_earned IN pay_payroll_actions.date_earned%TYPE
412 ,p_business_group_id IN pay_payroll_actions.business_group_id%TYPE
413 ,p_average_earnings IN NUMBER
414 ,p_tax_scale IN VARCHAR2
415 ,p_period_frequency IN NUMBER
416 ,p_spread_tax OUT NOCOPY NUMBER
417 ,p_fl_earnings in number default 0 -- bug12989692
418 ,p_flood_levy out nocopy number -- bug12989692
419 )
420 RETURN VARCHAR2;
421
422
423 FUNCTION get_retro_leave_load
424 (p_assignment_action_id IN NUMBER
425 ,p_tax_unit_id IN NUMBER
426 ,p_retro_adj_leave_load OUT NOCOPY NUMBER)
427 RETURN NUMBER;
428
429 /* bug 12873456 - AU_GET_RETRO_TAXVAR_FIXED formula function in AU_SPREAD_DEDUCTION formula for fixed tax_variation_type = 'Y' with bonus 'Y' */
430 FUNCTION get_retro_tax_var_fixed
431 (p_assignment_id number,
432 p_assignment_action_id number,
433 p_date_earned date,
434 p_mode varchar2,
435 p_retro out nocopy varchar2)
436 RETURN NUMBER;
437
438 /* 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 */
439 FUNCTION get_earns_flood_levy(ASSIGNMENT_ID IN NUMBER
440 , TAX_UNIT_ID IN NUMBER
441 , P_DATE IN DATE
442 , P_ETP_EARN_NN OUT NOCOPY NUMBER
443 , P_ETP_EARN_YN OUT NOCOPY NUMBER
444 , P_ETP_EARN_NY OUT NOCOPY NUMBER
445 , P_ETP_EARN_YY OUT NOCOPY NUMBER
446 , P_LS_A OUT NOCOPY NUMBER
447 , P_LS_B OUT NOCOPY NUMBER
448 , P_LP_M OUT NOCOPY NUMBER
449 , P_FW_LP_M OUT NOCOPY NUMBER
450 , P_FW_LS_A OUT NOCOPY NUMBER )
451 RETURN NUMBER;
452
453 /* bug 13879232 - Added for AU_GET_ADDTL_MESSAGE formula function */
454 FUNCTION get_addtl_message (TOKEN_NAME IN VARCHAR2, TOKEN_VALUE IN VARCHAR2)
455 RETURN VARCHAR2;
456
457 PROCEDURE upgrade_tax_scale(errbuf out NOCOPY varchar2,
458 retcode out NOCOPY varchar2,
459 p_business_group_id in HR_ALL_ORGANIZATION_UNITS.organization_id%type,
460 p_mode in varchar2);
461
462 end pay_au_paye_ff;
463
[Home] [Help]