[Home] [Help]
PACKAGE BODY: APPS.PAY_AU_SGC_PKG
Source
1 package body pay_au_sgc_pkg as
2 /* $Header: pyausgc.pkb 120.0 2005/05/29 01:56:08 appldev noship $ */
3 /*
4 **
5 ** Copyright (C) 1999 Oracle Corporation
6 ** All Rights Reserved
7 **
8 ** Australia SGC Report
9 **
10 ** Change List
11 ** ===========
12 **
13 ** Date Author Reference Description
14 ** -----------+--------+---------+-------------
15 ** 07-DEC-2000 RSINGHAL N/A Created
16 ** 10-JAN-2001 RSINGHAL bug#1574764 Modifications for performance issue.
17 ** 07-FEB-2001 RSINGHAL bug#1560081 p_business_group_id parameter added in c** ursor assign_work_hrs.
18 ** 04-FEB-2002 VGSRINIV Bug#2197813 sgc contribution calculated in procedure
19 ** employee_super_details is rouned to 5cents
20 ** and added dbdrv commands
21 ** 14-MAR-2002 SHOSKATT 2197813 Added the round to 5cents function when
22 ** displaying the amount in the messages
23 ** 03-DEC-2002 RAGOVIND 2689226 Added NOCOPY for the function employee_super_details
24 ** 09-AUG-2004 ABHKUMAR 2610141 Legal Employer enhancement changes
25 ** 12-AUG-2004 ABHKUMAR 2610141 Modified the code to pick the correct defined balance id for calculating _ASG_LE_QTD balance value
26 */
27
28 -------------------------------------------------------------------------------
29
30 ----------------/* procedure global_super_values */-----------------------
31
32 procedure global_super_values(
33 p_effective_date in date,
34 p_legislation_code in pay_balance_types.
35 legislation_code%type
36 )
37 as
38 cursor c_global(c_effective_date date,
39 c_legislation_code pay_balance_types.legislation_code%type) is
40 select global_name,global_value from
41 ff_globals_f where global_name in ('SUPER_MONTHLY_EARNINGS',
42 'SUPER_MAX_AGE',
43 'SUPER_MIN_AGE',
44 'SUPER_MIN_HOURS',
45 'SUPER_MAX_BASE_QTR')
46 and c_effective_date between effective_start_date
47 and effective_end_date
48 and legislation_code=c_legislation_code;
49
50 /*Bug 2610141----Modfied the cursor to return defined_balanace_id */
51
52 cursor c_super_bal_id(c_legislation_code pay_balance_types.legislation_code%type,
53 c_dimension_name pay_balance_dimensions.database_item_suffix%type)
54 is
55 select pdb.defined_balance_id
56 FROM pay_balance_types pbt,
57 pay_balance_dimensions pbd,
58 pay_defined_balances pdb
59 where pbt.balance_name='Super_Guarantee'
60 AND pbt.legislation_code=c_legislation_code
61 AND pbd.database_item_suffix = c_dimension_name
62 AND pbt.balance_type_id = pdb.balance_type_id
63 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
64
65
66 l_name ff_globals_f.global_name%type;
67 l_value ff_globals_f.global_value%type;
68
69
70
71 begin
72 hr_utility.set_location('Entering : global_super_values',1);
73 hr_utility.trace('p_effective_date'||p_effective_date);
74
75
76 -- /* select all global value(legislative) */
77
78 open c_global(p_effective_date,p_legislation_code);
79 loop
80 exit when c_global%notfound;
81 fetch c_global into l_name,l_value;
82 If l_name = 'SUPER_MONTHLY_EARNINGS' then
83 g_monthly_threshold := l_value;
84 elsif l_name = 'SUPER_MAX_AGE' then
85 g_age := l_value;
86 elsif l_name = 'SUPER_MIN_AGE' then
87 g_age_min := l_value;
88 elsif l_name = 'SUPER_MIN_HOURS' then
89 g_min_hrs_worked := l_value;
90 elsif l_name = 'SUPER_MAX_BASE_QTR' then
91 g_qtd_threshold := l_value;
92 end if;
93 end loop;
94 close c_global;
95
96 --
97 -- /* get the balance id for 'Super Guarantee' Balance */
98 --
99
100 open c_super_bal_id(p_legislation_code,'_ASG_LE_MTD');
101 fetch c_super_bal_id into g_super_guarantee_bal_id_mtd; --2610141
102 close c_super_bal_id;
103
104
105 Exception
106 when others then
107 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL') ;
108 hr_utility.set_message_token('PROCEDURE', 'pay_au_sgc_pkg.gobal_super_values ');
109 hr_utility.set_message_token('STEP','body') ;
110 hr_utility.raise_error ;
111
112 end global_super_values;
113
114
115
116 /*----------------------Compliance Mesg ------------------------------*/
117
118
119
120 function compliance_mesg
121 (p_assignment_id in per_all_assignments_f.assignment_id%type,
122 p_employee_age in number,
123 p_effective_date in date,
124 p_sgc_rate in number,
125 p_business_group_id in per_all_people_f.business_group_id%type,
126 p_registered_employer in NUMBER, --2610141
127 p_legislation_code in pay_balance_types.legislation_code%type--2610141
128 ) return varchar2
129 IS
130
131 /*Bug 2610141 - Portion added to get the latest assignment action id*/
132 cursor get_latest_id ( c_assignment_id in number --Bug#2610141
133 , c_effective_date in date
134 ) is
135 select /*+ORDERED*/ to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
136 from pay_assignment_actions paa
137 , pay_payroll_actions ppa
138 where paa.assignment_id = c_assignment_id
139 and ppa.payroll_action_id = paa.payroll_action_id
140 and ppa.effective_date <= c_effective_date
141 and ppa.effective_date >= trunc(c_effective_date,'MM')
142 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
143 and paa.action_status='C'
144 and ppa.action_status='C'
145 and paa.tax_unit_id = p_registered_employer;
146
147
148
149 l_age number;
150 l_sgc_rate number;
151 l_compliance_mesg varchar2(100);
152 l_hrs_worked number ;
153 l_min_superable_salary number;
154 l_SGC_qtd number;
155 l_sgc_contribution number;
156 l_superable_sal number;
157 l_bal_id pay_balance_types.balance_type_id%type;
158 l_bal_id_mtd pay_balance_types.balance_type_id%type; --2610141
159 l_salary number;
160 l_bal_id_min_superable_sal pay_balance_types.balance_type_id%type;
161 l_assignment_action_id pay_assignment_actions.assignment_action_id%type; --2610141
162 l_super_guarantee_bal_id_qtd pay_defined_balances.defined_balance_id%type; --2610141
163
164
165
166 begin
167 hr_utility.set_location('Entering: function compliance_mesg',2);
168 /*Bug 2610141 - Portion added to get the latest assignment action id*/
169 open get_latest_id(p_assignment_id,p_effective_date);
170 fetch get_latest_id into l_assignment_action_id;
171 close get_latest_id;
172
173 l_sgc_rate:=(p_sgc_rate/100);
174
175
176
177 ------------ /* to get the normal hours worked */--------------------
178
179
180 open assign_work_hrs(p_effective_date,p_assignment_id,p_business_group_id,p_registered_employer); --2610141
181 fetch assign_work_hrs into l_hrs_worked;
182 If assign_work_hrs%notfound then
183 l_hrs_worked:=null ;
184 End if;
185 close assign_work_hrs;
186
187
188
189 --------/* to ensure that the employee is paid super even when hours worked is not defined */---------
190 /*
191 If l_hrs_worked is null then
192 l_hrs_worked:= g_min_hrs_worked + 1;
193 end if;
194 */
195
196 -------/* to ensure that the employee is paid super even when age is not defined */----------
197
198
199 If p_employee_age is null then
200 l_age:= g_age_min + 1;
201 else
202 l_age:=p_employee_age;
203 end if;
204
205
206 hr_utility.trace('hrs_worked = '||l_hrs_worked);
207 hr_utility.trace('employee_age = '||l_age);
208
209
210 ------------------ /* to get the SGC Contribution for the month */--------------------------------
211 /*Bug 2610141 --- Added this portion to accomodate for the BRA changes */
212 l_sgc_contribution := pay_balance_pkg.get_value(g_super_guarantee_bal_id_mtd,
213 l_assignment_action_id,
214 p_registered_employer,null,null,null,null);
215
216
217
218 /*Bug 2610141 --- Added portion for the BRA changes ends here*/
219
220
221 -------------- /* get minimum superable salary and balance_id */----------------------
222
223
224 open bal(p_business_group_id,p_assignment_id,p_effective_date);
225 loop
226 exit when bal%notfound;
227 fetch bal into l_bal_id_min_superable_sal; /*Bug 2610141 */
228 open bal_id_mtd(l_bal_id_min_superable_sal,p_legislation_code); /*Bug 2610141 */
229 fetch bal_id_mtd into l_bal_id_mtd;
230 l_salary := pay_balance_pkg.get_value(l_bal_id_mtd,
231 l_assignment_action_id,
232 p_registered_employer,null,null,null,null); /*Bug 2610141 */
233 IF bal%rowcount = 1 then
234 l_bal_id := l_bal_id_min_superable_sal;
235 l_min_superable_salary := l_salary;
236 ELSIF l_salary < l_min_superable_salary THEN
237 l_bal_id := l_bal_id_min_superable_sal;
238 l_min_superable_salary := l_salary;
239 END IF;
240 close bal_id_mtd; /*Bug 2610141 */
241 end loop;
242 close bal;
243
244 l_superable_sal := l_min_superable_salary;
245
246
247 -------------/* get QTD *Superannuation Salary*/----------------------------
248
249 /*Bug 2610141 --- Added this portion to accomodate for the BRA changes */
250 OPEN bal_id_qtd(l_bal_id,p_legislation_code);
251 FETCH bal_id_qtd INTO l_super_guarantee_bal_id_qtd;
252 CLOSE bal_id_qtd;
253
254 l_SGC_qtd := pay_balance_pkg.get_value(l_super_guarantee_bal_id_qtd,
255 l_assignment_action_id,
256 p_registered_employer,null,null,null,null);
257
258 /*Bug 2610141 --- Added portion for the BRA changes ends here*/
259
260
261 ---------------------------/* get compliance message */----------------------------
262
263
264 If (l_superable_sal * l_sgc_rate) <= l_sgc_contribution then
265 l_compliance_mesg :=null;
266 ELSE
267 l_compliance_mesg := 'EXCEPTION' ;
268 END IF;
269
270
271 hr_utility.trace('Out : function compliance_mesg');
272
273
274 return l_compliance_mesg;
275 --
276 Exception
277 when others then
278 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL') ;
279 hr_utility.set_message_token('PROCEDURE', 'pay_au_sgc_pkg.compliance_mesg') ;
280 hr_utility.set_message_token('STEP','body') ;
281 hr_utility.raise_error ;
282
283 END compliance_mesg;
284
285
286 /*----------------------------EMPLOYEE_SUPER_DETAILS------------------------*/
287
288 procedure employee_super_details
289 (p_assignment_id in per_all_assignments_f.assignment_id%type,
290 p_registered_employer in NUMBER, --2610141
291 p_employee_age in number,
292 p_effective_date in date,
293 p_sgc_rate in number,
294 p_business_group_id in per_all_people_f.business_group_id%type,
295 p_legislation_code in pay_balance_types.legislation_code%type,--2610141
296 p_superable_sal out NOCOPY number,
297 p_sgc_contribution out NOCOPY number,
298 p_compliance_mesg out NOCOPY varchar2,
299 p_warning_mesg out NOCOPY varchar2
300 )
301 IS
302
303 /*Bug 2610141 - Portion added to get the latest assignment action id*/
304 cursor get_latest_id ( c_assignment_id in number --Bug#2610141
305 , c_effective_date in date
306 ) is
307 select /*+ORDERED*/ to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
308 from pay_assignment_actions paa
309 , pay_payroll_actions ppa
310 where paa.assignment_id = c_assignment_id
311 and ppa.payroll_action_id = paa.payroll_action_id
312 and ppa.effective_date <= c_effective_date
313 and ppa.effective_date >= trunc(c_effective_date,'MM')
314 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
315 and paa.action_status='C'
316 and ppa.action_status='C'
317 and paa.tax_unit_id = p_registered_employer;
318
319
320 l_age number;
321 l_sgc_rate number;
322 l_hrs_worked number ;
323 l_min_superable_salary number;
324 l_max_superable_salary number;
325 l_SGC_qtd number;
326 l_bal_id pay_balance_types.balance_type_id%type;
327 l_bal_id_mtd pay_balance_types.balance_type_id%type; --2610141
328 l_salary number;
329 l_bal_id_min_superable_sal pay_balance_types.balance_type_id%type;
330 l_assignment_action_id pay_assignment_actions.assignment_action_id%type; --2610141
331 l_super_guarantee_bal_id_qtd pay_defined_balances.defined_balance_id%type; --2610141
332
333
334 begin
335 hr_utility.set_location(' Entering : employee_super_details',3);
336 hr_utility.trace('p_assgnment_id ='||p_assignment_id);
337 hr_utility.trace('p_effective_date ='||p_effective_date);
338
339 /*Bug 2610141 - Portion added to get the latest assignment action id*/
340 open get_latest_id(p_assignment_id,p_effective_date);
341 fetch get_latest_id into l_assignment_action_id;
342 close get_latest_id;
343
344 hr_utility.trace('Assgmt Action Id ='||l_assignment_action_id);
345 l_sgc_rate:=(p_sgc_rate/100);
346
347
348
349 ----------------/* to get the normal hours worked */------------------------
350
351
352 open assign_work_hrs(p_effective_date,p_assignment_id,p_business_group_id,p_registered_employer); --2610141
353 fetch assign_work_hrs into l_hrs_worked;
354 If assign_work_hrs%notfound then
355 l_hrs_worked:=null ;
356 End if;
357 close assign_work_hrs;
358
359
360 -------/* to ensure that the employee is paid super even when hours worked is not defined */--------
361 /*
362 If l_hrs_worked is null then
363 l_hrs_worked:= g_min_hrs_worked + 1;
364 end if;
365 */
366 -------------/* to ensure that the employee is paid super even when age is not defined */-------
367
368 If p_employee_age is null then
369 l_age:= g_age_min + 1;
370 else
371 l_age:=p_employee_age;
372 end if;
373
374
375
376 -------------- /* to get the SGC Contribution for the month */----------------------
377 /*Bug 2610141 --- Added this portion to accomodate for the BRA changes */
378 p_sgc_contribution := pay_balance_pkg.get_value(g_super_guarantee_bal_id_mtd,
379 l_assignment_action_id,
380 p_registered_employer,null,null,null,null);
381
382 /*Bug 2610141 --- Added portion for the BRA changes ends here*/
383
384
385
386 -------------------- /* get minimum superable salary */------------------------------
387
388
389 open bal(p_business_group_id,p_assignment_id,p_effective_date);
390 loop
391 exit when bal%notfound;
392 fetch bal into l_bal_id_min_superable_sal;
393 open bal_id_mtd(l_bal_id_min_superable_sal,p_legislation_code); /*Bug 2610141 */
394 fetch bal_id_mtd into l_bal_id_mtd;
395 l_salary := pay_balance_pkg.get_value(l_bal_id_mtd,
396 l_assignment_action_id,
397 p_registered_employer,null,null,null,null); /*Bug 2610141 */
398 IF bal%rowcount = 1 then
399 l_bal_id := l_bal_id_min_superable_sal;
400 l_min_superable_salary := l_salary;
401 l_max_superable_salary:=l_salary;
402 ELSIF l_salary < l_min_superable_salary THEN
403 l_bal_id := l_bal_id_min_superable_sal;
404 l_min_superable_salary := l_salary;
405 END IF;
406 IF l_salary > l_max_superable_salary THEN
407 l_max_superable_salary:=l_salary;
408 END IF;
409 close bal_id_mtd; /*Bug 2610141 */
410 end loop;
411 close bal;
412
413
414 p_superable_sal:= l_min_superable_salary;
415
416
417 hr_utility.trace('balance_id :'||l_bal_id);
418 hr_utility.trace('superable_salary :'||l_salary);
419
420
421 -------- /* get warning message if superable salary across funds are different */-----------
422
423 IF l_max_superable_salary <> l_min_superable_salary then
424 hr_utility.set_message(801,'HR_AU_SGC_WARNING_MESG');
425 p_warning_mesg := hr_utility.get_message;
426 ELSE
427 p_warning_mesg := '';
428 END IF;
429
430
431 -------------/* get QTD employer SGC Contribution */--------------------------------
432
433
434
435 /*Bug 2610141 --- Added this portion to accomodate for the BRA changes */
436 OPEN bal_id_qtd(l_bal_id,'AU');
437 FETCH bal_id_qtd INTO l_super_guarantee_bal_id_qtd;
438 CLOSE bal_id_qtd;
439
440 l_SGC_qtd := pay_balance_pkg.get_value(l_super_guarantee_bal_id_qtd,
441 l_assignment_action_id,
442 p_registered_employer,null,null,null,null);
443
444 /*Bug 2610141 --- Added portion for the BRA changes ends here*/
445
446
447 ------------------------/* get compliance message */-------------------------
448
449 /* Bug# 2197813 p_superable_sal*l_sgc_rate is rounded to 5cents */
450 /* Also amount when displayed in message is rounded to 5cents */
451
452 If pay_au_paye_ff.round_to_5c(p_superable_sal * l_sgc_rate) <= p_sgc_contribution then
453 p_compliance_mesg :=null;
454 ELSIF
455 pay_au_paye_ff.round_to_5c( p_superable_sal * l_sgc_rate) > p_sgc_contribution then
456 IF p_superable_sal < g_monthly_threshold then
457
458 hr_utility.set_message(801,'HR_AU_SGC_MONTHLY_THRESHOLD');
459 hr_utility.set_message_token('MONTHLY',g_monthly_threshold);
460 hr_utility.set_message_token('AMOUNT',pay_au_paye_ff.round_to_5c(p_superable_sal * l_sgc_rate));
461 p_compliance_mesg := hr_utility.get_message;
462
463 ELSIF l_age > g_age then
464
465 hr_utility.set_message(801,'HR_AU_SGC_MAX_AGE');
466 hr_utility.set_message_token('AGE',g_age);
467 p_compliance_mesg := hr_utility.get_message;
468
469 ELSIF l_age < g_age_min and l_hrs_worked < g_min_hrs_worked then
470
471 hr_utility.set_message(801,'HR_AU_SGC_AGE_HRS_WORKED');
472 hr_utility.set_message_token('MINAGE',g_age_min);
473 hr_utility.set_message_token('HOURS',g_min_hrs_worked);
474 p_compliance_mesg := hr_utility.get_message;
475
476
477 ELSIF l_SGC_qtd > g_qtd_threshold then
478
479 hr_utility.set_message(801,'HR_AU_SGC_YTD_EMPLOYER_SGC');
480 hr_utility.set_message_token('VALUE',g_qtd_threshold * 4);
481 hr_utility.set_message_token('AMOUNT',pay_au_paye_ff.round_to_5c(p_superable_sal * l_sgc_rate));
482 p_compliance_mesg := hr_utility.get_message;
483
484
485 ELSE
486 hr_utility.set_message(801,'HR_AU_SGC_NON_COMPLIANT');
487 hr_utility.set_message_token('AMOUNT',pay_au_paye_ff.round_to_5c(p_superable_sal * l_sgc_rate));
488 p_compliance_mesg := hr_utility.get_message;
489
490
491 END IF;
492 END IF;
493
494
495 -- hr_utility.trace('Out : employee_super_details');
496
497 Exception
498 when others then
499 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL') ;
500 hr_utility.set_message_token('PROCEDURE', 'pay_au_sgc_pkg.employee_super_details') ;
501 hr_utility.set_message_token('STEP','body') ;
502 hr_utility.raise_error ;
503
504 END employee_super_details;
505
506
507 BEGIN
508
509 g_end_date :='4712/12/31 00:00:00'; /*Bug 2610141- Modfication done to removed gscc warnings */
510
511 END pay_au_sgc_pkg;