1 PACKAGE pay_au_paye_ff AS
2 /* $Header: pyaufmla.pkh 120.11.12010000.4 2008/08/06 06:48:47 ubhat ship $
3 **
4 ** Copyright (c) 1999 Oracle Corporation
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 */
56
57 /*
58 * round_to_5c = rounds values to nearest 5c using
59 * ATO rules
60 */
61
62 cursor c_get_creator_type(c_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
63 c_date_earned in pay_payroll_actions.date_earned%TYPE
64 ) is
65 SELECT creator_type
66 FROM pay_element_entries_f pee
67 WHERE pee.element_entry_id=c_element_entry_id
68 and c_date_earned between pee.effective_start_date and pee.effective_end_date;
69
70
71
72 function round_to_5c
73 (
74 p_actual_amt in number
75 )
76 return number;
77
78 /*
79 * convert_to_period - converts weekly equivalents
80 * back to the period amounts using ATO rules.
81 */
82
83 function convert_to_period
84 (
85 p_ann_freq in number,
86 p_amt_week in number
87 )
88 return number;
89 /*
90 * convert_to_week - converts period amounts to equivalents
91 * weekly equivalents using ATO rules.
92 */
93
94 function convert_to_week
95 (
96 p_ann_freq in number,
97 p_amt_period in number
98 )
99 return number;
100 /*
101 * periods_since_hire_date - returns the number of periods in the
102 * current tax year since the hire date.
103 */
104
105 /* Bug:2900253. Added the extra context parameter p_assignment_id */
106 function periods_since_hire_date
107 (
108 p_payroll_id in number,
109 p_assignment_id in per_all_assignments_f.assignment_id%type,
110 p_tax_unit_id in pay_assignment_actions.tax_unit_id%type, --2610141
111 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type, /*Bug 4451088 */
112 p_period_num in number,
113 p_period_start in date,
114 p_emp_hire_date in date,
115 p_use_tax_flag IN VARCHAR2 --2610141
116 )
117 return number;
118
119 function paid_periods_since_hire_date
120 (
121 p_payroll_id in number,
122 p_assignment_id in number,
123 p_tax_unit_id in number, --2610141
124 p_assignment_action_id IN number, /*Bug 4438644 */
125 p_period_num in number,
126 p_period_start in date,
127 p_emp_hire_date in date,
128 p_use_tax_flag IN VARCHAR2 --2610141
129 )
130 return number;
131
132 /* Bug 4456720 - Added a new function to calculate the earnings_total and
133 per tax spread deductions for the previous year when total average earnings are negative */
134 /* Bug#4467198 - Modified the function to take care of legal employer changes. Introduced following
135 parameters in the function p_use_tax_flag, p_payroll_id, p_assignment_action_id*/
136 FUNCTION calculate_asg_prev_value
137 ( p_assignment_id in per_all_assignments_f.assignment_id%TYPE,
138 p_business_group_id in hr_all_organization_units.organization_id%TYPE,
139 p_date_earned in date,
140 p_tax_unit_id in hr_all_organization_units.organization_id%TYPE,
141 p_assignment_action_id IN number, /* Bug#4467198*/
142 p_payroll_id IN NUMBER, /* Bug#4467198*/
143 p_period_start_date in date,
144 p_case out NOCOPY varchar2,
145 p_earnings_standard out NOCOPY number,
146 p_pre_tax_spread out NOCOPY number,
147 p_pre_tax_fixed out NOCOPY number, /*bug4363057*/
148 p_pre_tax_prog out NOCOPY number, /*bug4363057*/
149 p_paid_periods out NOCOPY number,
150 p_use_tax_flag IN VARCHAR2 /* Bug#4467198*/
151 )
152 return NUMBER ;
153
154 -----------------------------------------------------------------------
155 -- Cursor : c_get_ytd_def_bal_ids
156 -- Description : To get the YTD defined balance ids for the balances
157 -- Earnings_Standard and Pre Tax Deductions
158 --
159 -----------------------------------------------------------------------
160 CURSOR c_get_ytd_def_bal_ids (c_db_item_suffix IN pay_balance_dimensions.DATABASE_ITEM_SUFFIX%type)
161 IS
162 SELECT pdb.defined_balance_id, pbt.balance_name, pbd.DIMENSION_NAME
163 FROM pay_balance_types pbt,
164 pay_balance_dimensions pbd,
165 pay_defined_balances pdb
166 WHERE pbt.balance_name in ( 'Earnings_Standard'
167 ,'Pre Tax Spread Deductions'
168 ,'Pre Tax Fixed Deductions' /*bug4363057*/
169 ,'Pre Tax Progressive Deductions')
170 AND pbt.balance_type_id = pdb.balance_type_id
171 AND pdb.balance_dimension_id = pbd.balance_dimension_id
172 AND pbd.DATABASE_ITEM_SUFFIX = c_db_item_suffix --2610141
173 AND pbt.legislation_code = 'AU'
174 and pbt.legislation_code = pbd.legislation_code
175 AND pbd.legislation_code = 'AU';
176
177
178 TYPE g_ytd_tab_bals IS TABLE OF c_get_ytd_def_bal_ids%rowtype INDEX BY BINARY_INTEGER;
179 g_ytd_bals g_ytd_tab_bals;
180
181 g_ytd_def_bals_populated BOOLEAN;
182
183 -- BBR Tables to store YTD balance details
184 --
185 g_ytd_input_table pay_balance_pkg.t_balance_value_tab;
186 g_ytd_result_table pay_balance_pkg.t_detailed_bal_out_tab;
187 g_ytd_context_table pay_balance_pkg.t_context_tab;
188
189
190
191
192
193
194 /*
195 * round_amount rounds values to nearest dollar
196 * new ATO Rounding rules effective from year 2000
197 */
198
199 function round_amt
200 (
201 p_actual_amt in number,
202 p_tax_scale in number
203 )
204 return number;
205
206 /*
207 * convert_to_period - converts weekly equivalents
208 * back to the period amounts using new ATO rules effective from year 2000
209 */
210
211 function convert_to_period_amt
212 (
213 p_ann_freq in number,
214 p_amt_week in number,
215 p_tax_scale in number
216 )
217 return number;
218
219 function check_if_retro
220 (
221 p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
222 p_date_earned in pay_payroll_actions.date_earned%TYPE
223
224 )return varchar2;
225
226
227 function get_retro_period
228 (
229 p_element_entry_id in pay_element_entries_f.element_entry_id%TYPE,
230 p_date_earned in pay_payroll_actions.date_earned%TYPE,
231 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE, /*Bug 4418107*/
232 p_retro_start_date out NOCOPY date,
233 p_retro_end_date out NOCOPY date
234 )return number;
235
236
237 function count_retro_periods
238 (
239 p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
240 p_date_earned in pay_payroll_actions.date_earned%TYPE,
241 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE, /*Bug 4418107*/
242 p_use_tax_flag IN VARCHAR2 --4415795
243 )return number;
244
245
246 function calculate_tax(p_date_earned in pay_payroll_actions.date_earned%TYPE,
247 p_period_amount in number,
248 p_period_frequency in number,
249 p_tax_scale in number,
250 p_a1_variable in number,
251 p_b1_variable in number
252 )return number;
253
254
255 function check_fixed_deduction(p_assignment_id in per_all_assignments_f.assignment_id%TYPE, p_date_earned in date)
256 return varchar2;
257
258 /* Bug No : 2977425 - Added the new formula function */
259 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)
260 RETURN VARCHAR2;
261
262 /* Bug No : 3091834 - Added the new formula function below */
263
264 FUNCTION validate_data_magtape
265 (
266 p_data varchar2
267 ) RETURN varchar2;
268
269
270 /* Bug No : 3306112 - The new function will be called from view "pay_au_asg_element_payments_v"
271 It return value of Hours in case the element_id passed is attached to the Salary Basis
272 */
273 FUNCTION get_salary_basis_hours
274 (
275 p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE,
276 p_element_type_id in pay_element_entries_f.element_entry_id%TYPE,
277 p_pay_bases_id in per_all_assignments_f.pay_basis_id%TYPE
278 )
279 RETURN NUMBER;
280
281 /* Bug :3245909 - This function wil be used in AU_PAYMENTs route to get the pre payemnt actin id
282 */
283 FUNCTION get_pp_action_id
284 (
285 p_action_type in varchar2,
286 p_action_id in number
287 ) RETURN number;
288
289 /*Bug# 3935471*/
290 FUNCTION check_tax_unit_id
291 (
292 p_assignment_action_id NUMBER,
293 p_tax_unit_id NUMBER
294 ) RETURN VARCHAR2;
295
296 /* Bug 5556260 - In Enhanced Retropay, this function will be called to get the retro period
297 and other related information for an element entry
298 */
299 FUNCTION get_enhanced_retro_period
300 (
301 p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE,
302 p_date_earned IN pay_payroll_actions.date_earned%TYPE,
303 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
304 p_retro_start_date OUT NOCOPY date,
305 p_retro_end_date OUT NOCOPY date,
306 p_orig_effective_date OUT NOCOPY date,
307 p_retro_effective_date OUT NOCOPY date,
308 p_time_span OUT NOCOPY varchar2
309 )return number;
310
311
312 /* Bug 5846272 - Function checks if Enhanced Retropay is enabled in system.
313 */
314
315 FUNCTION check_if_enhanced_retro
316 (
317 p_business_group_id IN per_business_groups.business_group_id%TYPE
318 )RETURN VARCHAR2;
319
320 /* Bug 5846272 - Function checks and returns the Retro time span
321 for element entry for Enhanced Retropay
322 */
323
324 FUNCTION get_retro_time_span
325 (
326 p_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE,
327 p_date_earned IN pay_payroll_actions.date_earned%TYPE,
328 p_tax_unit_id IN pay_assignment_actions.tax_unit_id%TYPE,
329 p_retro_start_date OUT NOCOPY date,
330 p_retro_end_date OUT NOCOPY date,
331 p_orig_effective_date OUT NOCOPY date,
332 p_retro_effective_date OUT NOCOPY date,
333 p_time_span OUT NOCOPY varchar2,
334 p_retro_type OUT NOCOPY varchar2
335 )return number;
336 /* Bug#5934468 */
337 function get_spread_earning
338 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
339 p_date_paid in date,
340 p_pre_tax in number,
341 p_spread_earning in number) return number;
342
343 function get_retro_spread_earning
344 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
345 p_date_paid in date,
346 p_pre_tax in number,
347 p_spread_earning in number) return number;
348
349 function get_etp_pay_component
350 ( p_assignment_id in per_all_assignments_f.assignment_id%type,
351 p_date_earned in date) return varchar2;
352
353 end pay_au_paye_ff;
[Home] [Help]