[Home] [Help]
PACKAGE BODY: APPS.PAY_ZA_UIF_REFUND_MARCH_2008
Source
1 PACKAGE BODY PAY_ZA_UIF_REFUND_MARCH_2008 as
2 /* $Header: pyzauifr.pkb 120.5 2010/12/08 10:59:45 bkeshary noship $ */
3 -----------------------------------------------------------------------------------------
4 ----------------- function to set ZA_PAY_PERIODS_PER_YEAR dbi--------------------------
5 -----------------------------------------------------------------------------------------
6 function get_py_prd_per_yr(p_payroll_action_id number,
7 p_payroll_id number) return number is
8 l_py_prd_per_yr number;
9 begin
10 select count(ptp.end_date)
11 into l_py_prd_per_yr
12 from per_time_periods PTP
13 where ptp.prd_information1 =
14 (select tperiod.prd_information1
15 from per_time_periods tperiod,
16 pay_payroll_actions paction
17 where paction.payroll_action_id = p_payroll_action_id
18 and tperiod.time_period_id = paction.time_period_id)
19 and ptp.payroll_id = p_payroll_id;
20
21 return l_py_prd_per_yr;
22
23 end get_py_prd_per_yr;
24
25 -----------------------------------------------------------------------------------------
26 ----------------- function to set ZA_PAY_MONTH_PERIOD_NUMBER dbi--------------------------
27 -----------------------------------------------------------------------------------------
28 function get_za_pay_mnth_prd_num (p_payroll_action_id number,
29 p_payroll_id number) return number is
30 l_za_pay_mnth_prd_num number ;
31 begin
32 select count(ptp.end_date)
33 into l_za_pay_mnth_prd_num
34 from per_time_periods ptp
35 where ptp.pay_advice_date =
36 (select tperiod.pay_advice_date
37 from per_time_periods tperiod,
38 pay_payroll_actions paction
39 where paction.payroll_action_id = p_payroll_action_id
40 and tperiod.time_period_id = paction.time_period_id
41 )
42 and ptp.end_date <=
43 (select tperiod.end_date
44 from per_time_periods tperiod,
45 pay_payroll_actions paction
46 where paction.payroll_action_id = p_payroll_action_id
47 and tperiod.time_period_id = paction.time_period_id
48 )
49 and ptp.payroll_id = p_payroll_id;
50
51 return l_za_pay_mnth_prd_num ;
52 end get_za_pay_mnth_prd_num;
53
54 -----------------------------------------------------------------------------------------
55 ----------------- function to set global values --------------------------------------
56 -----------------------------------------------------------------------------------------
57 function get_global_value (p_global_name varchar2, p_effective_date date) return varchar2 is
58 l_glb_value ff_globals_f.global_value%type;
59 begin
60 select global_value
61 into l_glb_value
62 from ff_globals_f
63 where global_name = p_global_name
64 and p_effective_date between effective_start_date
65 and effective_end_date
66 and legislation_code = 'ZA';
67
68 return l_glb_value;
69
70 end get_global_value;
71 -----------------------------------------------------------------------------------------
72 ----------------- function get_balance_value ----------------------------------------
73 -----------------------------------------------------------------------------------------
74 function get_balance_value (p_bal_name varchar2,
75 p_dim_name varchar2,
76 p_asg_act_id number)
77 return number is
78 cursor c_get_def_bal_id is
79 select pdb.defined_balance_id
80 from pay_balance_types pbt
81 , pay_balance_dimensions pbd
82 , pay_defined_balances pdb
83 where pbt.balance_name = p_bal_name
84 and pbd.dimension_name = p_dim_name
85 and pbd.legislation_code = 'ZA'
86 and pdb.balance_type_id = pbt.balance_type_id
87 and pdb.balance_dimension_id = pbd.balance_dimension_id;
88
89 cursor c_get_bal_value( p_def_bal_id in number) is
90 select pay_balance_pkg.get_value(p_def_bal_id, --p_def_bal_id
91 p_asg_act_id, --assignment_action_id
92 null,
93 null,
94 null,
95 null,
96 null,
97 null,
98 null,
99 'TRUE')
100 from dual;
101
102 l_def_bal_id number;
103 l_bal_val number;
104 begin
105 open c_get_def_bal_id;
106 fetch c_get_def_bal_id into l_def_bal_id ;
107 close c_get_def_bal_id ;
108
109 open c_get_bal_value(l_def_bal_id);
110 fetch c_get_bal_value into l_bal_val;
111 close c_get_bal_value;
112
113 return l_bal_val;
114 end get_balance_value;
115
116 -----------------------------------------------------------------------------------------
117 ----------------- function get_ele_dtls ---------------------------------------------
118 -----------------------------------------------------------------------------------------
119 procedure get_ele_dtls(p_element_name in varchar2,
120 p_effective_date in date,
121 p_ele_type_id out nocopy number ,
122 p_ip_value_id out nocopy number)is
123 cursor c_get_ele_dtls is
124 select pet.element_type_id
125 , piv.input_value_id
126 from pay_element_types_f pet
127 , pay_input_values_f piv
128 where pet.element_name = p_element_name
129 and p_effective_date between pet.effective_start_date
130 and pet.effective_end_date
131 and piv.element_type_id = pet.element_type_id
132 and piv.name = 'Pay Value'
133 and p_effective_date between piv.effective_start_date
134 and piv.effective_end_date;
135
136 begin
137 open c_get_ele_dtls;
138 fetch c_get_ele_dtls into p_ele_type_id,p_ip_value_id;
139 close c_get_ele_dtls;
140 end get_ele_dtls;
141
142
143 /*******************************************************************************
144 **************** Procedure populate_assact_tab ***************
145 ******************************************************************************/
146
147 procedure populate_assact_tab( rec_assact in out nocopy tab_assact,
148 p_assignment_id number,
149 p_assignment_number varchar2,
150 p_rec_count in out nocopy number,
151 l_ee_contr_ele_type_id in number
152 ) is
153 l_row_found varchar2(1);
154 l_Oct_act_seq number;
155 l_Nov_act_seq number;
156 l_Dec_act_seq number;
157 l_Jan_act_seq number;
158 begin
159 l_row_found := 'Y';
160
161 -- Oct 2007
162 begin
163 select max(paa.action_sequence)
164 into l_Oct_act_seq
165 from pay_assignment_actions paa,
166 pay_payroll_actions ppa,
167 per_time_periods ptp
168 where paa.assignment_id = p_assignment_id
169 and paa.payroll_action_id = ppa.payroll_action_id
170 and ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
171 and paa.action_status = 'C'
172 and ppa.time_period_id = ptp.time_period_id
173 and ptp.end_date between to_date('1-10-2007','DD-MM-YYYY')
174 and to_date('31-10-2007','DD-MM-YYYY')
175 and exists (select 1
176 from pay_run_results prr
177 where element_type_id =l_ee_contr_ele_type_id
178 and prr.assignment_action_id = paa.assignment_action_id
179 ) ;
180 exception
181 when others then
182 l_row_found := 'N';
183 hr_utility.trace('Row not found for Oct');
184 end ;
185
186 if l_row_found = 'Y' and (l_Oct_act_seq is not null) then
187 hr_utility.trace('Inserting row for Assignment :'||p_assignment_id||' month :'||'Oct-2007');
188 rec_assact(p_rec_count).assignment_id := p_assignment_id;
189 rec_assact(p_rec_count).assignment_number := p_assignment_number;
190 rec_assact(p_rec_count).month_yr := 'Oct-2007';
191 rec_assact(p_rec_count).action_seq := l_Oct_act_seq;
192 p_rec_count := p_rec_count + 1 ;
193 end if;
194
195
196 -- Nov 2007
197 l_row_found := 'Y';
198
199 begin
200 select max(paa.action_sequence)
201 into l_Nov_act_seq
202 from pay_assignment_actions paa,
203 pay_payroll_actions ppa,
204 per_time_periods ptp
205 where paa.assignment_id = p_assignment_id
206 and paa.payroll_action_id = ppa.payroll_action_id
207 and ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
208 and paa.action_status = 'C'
209 and ppa.time_period_id = ptp.time_period_id
210 and ptp.end_date between to_date('1-11-2007','DD-MM-YYYY')
211 and to_date('30-11-2007','DD-MM-YYYY')
212 and exists (select 1
213 from pay_run_results prr
214 where element_type_id =l_ee_contr_ele_type_id
215 and prr.assignment_action_id = paa.assignment_action_id
216 ) ;
217 exception
218 when others then
219 l_row_found := 'N';
220 hr_utility.trace('Row not found for Nov');
221 end ;
222
223 if l_row_found = 'Y' and (l_Nov_act_seq is not null) then
224 hr_utility.trace('Inserting row for Assignment :'||p_assignment_id||' month :'||'Nov-2007');
225 rec_assact(p_rec_count).assignment_id := p_assignment_id;
226 rec_assact(p_rec_count).assignment_number := p_assignment_number;
227 rec_assact(p_rec_count).month_yr := 'Nov-2007';
228 rec_assact(p_rec_count).action_seq := l_Nov_act_seq;
229 p_rec_count := p_rec_count + 1 ;
230 end if;
231
232
233 -- Dec 2007
234 l_row_found := 'Y';
235
236 begin
237 select max(paa.action_sequence)
238 into l_Dec_act_seq
239 from pay_assignment_actions paa,
240 pay_payroll_actions ppa,
241 per_time_periods ptp
242 where paa.assignment_id = p_assignment_id
243 and paa.payroll_action_id = ppa.payroll_action_id
244 and ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
245 and paa.action_status = 'C'
246 and ppa.time_period_id = ptp.time_period_id
247 and ptp.end_date between to_date('1-12-2007','DD-MM-YYYY')
248 and to_date('31-12-2007','DD-MM-YYYY')
249 and exists (select 1
250 from pay_run_results prr
251 where element_type_id =l_ee_contr_ele_type_id
252 and prr.assignment_action_id = paa.assignment_action_id
253 ) ;
254 exception
255 when others then
256 l_row_found := 'N';
257 hr_utility.trace('Row not found for Dec');
258 end ;
259
260 if l_row_found = 'Y' and (l_Dec_act_seq is not null) then
261 hr_utility.trace('Inserting row for Assignment :'||p_assignment_id||' month :'||'Dec-2007');
262 rec_assact(p_rec_count).assignment_id := p_assignment_id;
263 rec_assact(p_rec_count).assignment_number := p_assignment_number;
264 rec_assact(p_rec_count).month_yr := 'Dec-2007';
265 rec_assact(p_rec_count).action_seq := l_Dec_act_seq;
266 p_rec_count := p_rec_count + 1 ;
267 end if;
268
269 -- Jan 2008
270 l_row_found := 'Y';
271
272 begin
273 select max(paa.action_sequence)
274 into l_Jan_act_seq
275 from pay_assignment_actions paa,
276 pay_payroll_actions ppa,
277 per_time_periods ptp
278 where paa.assignment_id = p_assignment_id
279 and paa.payroll_action_id = ppa.payroll_action_id
280 and ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
281 and paa.action_status = 'C'
282 and ppa.time_period_id = ptp.time_period_id
283 and ptp.end_date between to_date('1-01-2008','DD-MM-YYYY')
284 and to_date('31-01-2008','DD-MM-YYYY')
285 and exists (select 1
286 from pay_run_results prr
287 where element_type_id =l_ee_contr_ele_type_id
288 and prr.assignment_action_id = paa.assignment_action_id
289 ) ;
290 exception
291 when others then
292 l_row_found := 'N';
293 hr_utility.trace('Row not found for Jan');
294 end ;
295
296 if l_row_found = 'Y' and (l_Jan_act_seq is not null) then
297 hr_utility.trace('Inserting row for Assignment :'||p_assignment_id||' month :'||'Jan-2007');
298 rec_assact(p_rec_count).assignment_id := p_assignment_id;
299 rec_assact(p_rec_count).assignment_number := p_assignment_number;
300 rec_assact(p_rec_count).month_yr := 'Jan-2008';
301 rec_assact(p_rec_count).action_seq := l_Jan_act_seq;
302 p_rec_count := p_rec_count + 1 ;
303 end if;
304
305 hr_utility.trace('Completed row population for assignment '||p_assignment_id);
306 end populate_assact_tab;
307
308
309 -----------------------------------------------------------------------------------------
310 ----------------- function get_rrv_dtls ---------------------------------------------
311 -----------------------------------------------------------------------------------------
312 procedure get_rrv_dtls (p_asg_act_id number,
313 p_ee_contr_ele_type_id number , p_ee_contr_ip_value_id number,
314 p_er_contr_ele_type_id number , p_er_contr_ip_value_id number,
315 p_excs_er_contr_ele_type_id number, p_excs_er_contr_ip_value_id number,
316 p_ee_contr_rrval out nocopy number , p_ee_contr_ee_id out nocopy number,
317 p_er_contr_rrval out nocopy number , p_er_contr_ee_id out nocopy number,
318 p_excs_er_contr_rrval out nocopy number , p_excs_er_contr_ee_id out nocopy number
319 ) is
320 cursor c_get_rrv_dtls (p_ele_type_id number, p_ip_value_id number) is
321 select prrv.result_value
322 , prr.element_entry_id
323 from pay_run_results prr
324 , pay_run_result_values prrv
325 where prr.assignment_action_id = p_asg_act_id
326 and prr.element_type_id = p_ele_type_id
327 and prrv.run_result_id = prr.run_result_id
328 and prrv.input_value_id = p_ip_value_id;
329
330 begin
331 open c_get_rrv_dtls(p_ee_contr_ele_type_id,p_ee_contr_ip_value_id);
332 fetch c_get_rrv_dtls into p_ee_contr_rrval,p_ee_contr_ee_id;
333 close c_get_rrv_dtls ;
334
335 open c_get_rrv_dtls(p_er_contr_ele_type_id,p_er_contr_ip_value_id);
336 fetch c_get_rrv_dtls into p_er_contr_rrval,p_er_contr_ee_id;
337 close c_get_rrv_dtls ;
338
339 open c_get_rrv_dtls(p_excs_er_contr_ele_type_id,p_excs_er_contr_ip_value_id);
340 fetch c_get_rrv_dtls into p_excs_er_contr_rrval,p_excs_er_contr_ee_id;
341 close c_get_rrv_dtls ;
342 end get_rrv_dtls;
343
344
345
346 /*******************************************************************************
347 **************** Procedure calc_UIF_contribution **********************
348 ******************************************************************************/
349
350 procedure calc_UIF_contribution (p_payroll_action_id number,
351 p_payroll_id number,
352 p_eff_date in date,
353 p_asact_id in number,
354 p_pay_value out nocopy number,
355 p_empr_contr out nocopy number,
356 p_ARREAR_UIF out nocopy number,
357 p_UIF_ee_contr_ASG_TAX_MTD out nocopy number,
358 p_UIF_er_contr_ASG_TAX_MTD out nocopy number,
359 p_excs_er_UIF_cntr_ASG_TAX_PTD out nocopy number) is
360 -- dbis
361 l_ZA_PAY_MONTH_PERIOD_NUMBER number ;
362 l_ZA_PAY_PERIODS_PER_YEAR number ;
363
364 -- balances
365 l_Tot_UIFable_Inc_ASG_TAX_MTD number;
366 -- l_UIF_ee_contr_ASG_TAX_MTD number;
367 l_UIF_ee_contr_ASG_RUN number;
368 --l_UIF_er_contr_ASG_TAX_MTD number;
369 --l_excs_er_UIF_cntr_ASG_TAX_PTD number;
370 l_UIF_er_contr_ASG_RUN number;
371 l_NET_PAY_ASG_RUN number;
372
373 -- globals
374 l_ZA_UIF_ANN_LIM number;
375 l_ZA_UIF_EMPY_PERC number ;
376 l_ZA_UIF_EMPR_PERC number ;
377
378 -- variables
379 l_period_limit number;
380 l_ee_contr number;
381 l_refu_ee_contr number;
382
383 begin
384 -- initialise balances
385 l_Tot_UIFable_Inc_ASG_TAX_MTD := get_balance_value('Total UIFable Income','_ASG_TAX_MTD',p_asact_id);
386 p_UIF_ee_contr_ASG_TAX_MTD := get_balance_value('UIF Employee Contribution','_ASG_TAX_MTD',p_asact_id);
387 -- l_UIF_ee_contr_ASG_RUN := get_balance_value('UIF Employee Contribution','_ASG_RUN',p_asact_id);
388 p_UIF_er_contr_ASG_TAX_MTD := get_balance_value('UIF Employer Contribution','_ASG_TAX_MTD',p_asact_id);
389 p_excs_er_UIF_cntr_ASG_TAX_PTD:= get_balance_value('Excess Employer UIF Contrib','_ASG_TAX_PTD',p_asact_id);
390 -- l_UIF_er_contr_ASG_RUN := get_balance_value('UIF Employer Contribution','_ASG_RUN',p_asact_id);
391 l_NET_PAY_ASG_RUN := get_balance_value('Net Pay','_ASG_RUN',p_asact_id);
392
393 -- changed as while testing weekly payrolls, ASG_RUN should be zero.
394 l_UIF_ee_contr_ASG_RUN := 0;
395 l_UIF_er_contr_ASG_RUN := 0;
396
397 hr_utility.trace('Balances :');
398 hr_utility.trace('l_Tot_UIFable_Inc_ASG_TAX_MTD :'||l_Tot_UIFable_Inc_ASG_TAX_MTD);
399 hr_utility.trace('p_UIF_ee_contr_ASG_TAX_MTD :'||p_UIF_ee_contr_ASG_TAX_MTD);
400 hr_utility.trace('l_UIF_ee_contr_ASG_RUN :'||l_UIF_ee_contr_ASG_RUN);
401 hr_utility.trace('p_UIF_er_contr_ASG_TAX_MTD :'||p_UIF_er_contr_ASG_TAX_MTD);
402 hr_utility.trace('p_excs_er_UIF_cntr_ASG_TAX_PTD :'||p_excs_er_UIF_cntr_ASG_TAX_PTD);
403 hr_utility.trace('l_UIF_er_contr_ASG_RUN :'||l_UIF_er_contr_ASG_RUN);
404 hr_utility.trace('l_NET_PAY_ASG_RUN :'||l_NET_PAY_ASG_RUN);
405 hr_utility.trace(' ');
406
407
408 -- initialise dbis
409 l_ZA_PAY_MONTH_PERIOD_NUMBER := get_za_pay_mnth_prd_num(p_payroll_action_id, p_payroll_id);
410 l_ZA_PAY_PERIODS_PER_YEAR := get_py_prd_per_yr(p_payroll_action_id, p_payroll_id) ;
411 hr_utility.trace('DBIs :');
412 hr_utility.trace('l_ZA_PAY_MONTH_PERIOD_NUMBER :'||l_ZA_PAY_MONTH_PERIOD_NUMBER);
413 hr_utility.trace('l_ZA_PAY_PERIODS_PER_YEAR :'||l_ZA_PAY_PERIODS_PER_YEAR);
414 hr_utility.trace(' ');
415
416 -- initialse global values
417 l_ZA_UIF_ANN_LIM := get_global_value('ZA_UIF_ANN_LIM', p_eff_date);
418 l_ZA_UIF_EMPY_PERC := get_global_value('ZA_UIF_EMPY_PERC', p_eff_date);
419 l_ZA_UIF_EMPR_PERC := get_global_value('ZA_UIF_EMPR_PERC', p_eff_date);
420 hr_utility.trace('Globals :');
421 hr_utility.trace('l_ZA_UIF_ANN_LIM :'||l_ZA_UIF_ANN_LIM);
422 hr_utility.trace('l_ZA_UIF_EMPY_PERC :'||l_ZA_UIF_EMPY_PERC);
423 hr_utility.trace('l_ZA_UIF_EMPR_PERC :'||l_ZA_UIF_EMPR_PERC);
424 hr_utility.trace(' ');
425
426 -- compute UIF contribution
427 p_ARREAR_UIF := 0 ;
428
429 /* periodic limit of UIFable income */
430 l_period_limit := round(l_ZA_PAY_MONTH_PERIOD_NUMBER * l_ZA_UIF_ANN_LIM / l_ZA_PAY_PERIODS_PER_YEAR,2) ;
431
432 if l_Tot_UIFable_Inc_ASG_TAX_MTD > l_period_limit then
433 /* limit UIFable Income to period limit, and calculate UIF on that */
434 l_ee_contr := round((l_period_limit * l_ZA_UIF_EMPY_PERC) / 100,2);
435 p_empr_contr := round((l_period_limit * l_ZA_UIF_EMPR_PERC) / 100,2);
436 l_ee_contr := l_ee_contr - (p_UIF_ee_contr_ASG_TAX_MTD - l_UIF_ee_contr_ASG_RUN);
437 p_empr_contr := p_empr_contr - (p_UIF_er_contr_ASG_TAX_MTD - p_excs_er_UIF_cntr_ASG_TAX_PTD -
438 l_UIF_er_contr_ASG_RUN);
439 hr_utility.trace('1) p_empr_contr = '||p_empr_contr);
440 else
441 /* calculate UIF on the period UIFable income */
442 l_ee_contr := round((l_Tot_UIFable_Inc_ASG_TAX_MTD * l_ZA_UIF_EMPY_PERC) / 100,2);
443 p_empr_contr := round((l_Tot_UIFable_Inc_ASG_TAX_MTD * l_ZA_UIF_EMPR_PERC) / 100,2);
444 l_ee_contr := l_ee_contr - (p_UIF_ee_contr_ASG_TAX_MTD - l_UIF_ee_contr_ASG_RUN);
445 p_empr_contr := p_empr_contr - (p_UIF_er_contr_ASG_TAX_MTD - p_excs_er_UIF_cntr_ASG_TAX_PTD
446 - l_UIF_er_contr_ASG_RUN);
447 hr_utility.trace('2) p_empr_contr = '||p_empr_contr);
448 end if ;
449
450 if l_ee_contr > 0 then
451 /* check if Net Pay is zero or less */
452 if l_NET_PAY_ASG_RUN <= 0 then
453 hr_utility.trace('3) Entered '||p_empr_contr);
454 p_ARREAR_UIF := l_ee_contr;
455 l_ee_contr := 0;
456 else
457 /* check if Net Pay is insufficient to deduct the full UIF contribution */
458 if l_ee_contr > l_NET_PAY_ASG_RUN then
459 hr_utility.trace('4) Entered '||p_empr_contr);
460 p_ARREAR_UIF := l_ee_contr - l_NET_PAY_ASG_RUN ;
461 l_ee_contr := l_NET_PAY_ASG_RUN;
462 end if ;
463 end if ;
464 else
465 /* Maximum refundable SUM for Employee Contribution */
466 hr_utility.trace('5) Entered '||p_empr_contr);
467 l_refu_ee_contr := p_UIF_ee_contr_ASG_TAX_MTD ;
468 /* Check whether current run is to refud and refund limit is more than allowable amount */
469 IF (l_ee_contr + l_refu_ee_contr) < 0
470 Then
471 /* It's refund, thus, it should be negative */
472 l_ee_contr := 0 - l_refu_ee_contr ;
473 end if;
474 end if ;
475
476 /* Maximum refundable SUM for Employer Contribution */
477 l_refu_ee_contr := p_UIF_er_contr_ASG_TAX_MTD - p_excs_er_UIF_cntr_ASG_TAX_PTD ;
478 hr_utility.trace('6) l_refu_ee_contr : '||l_refu_ee_contr);
479
480 /* Check whether current run is to refud and refund limit is more than allowable amount */
481 if (p_empr_contr + l_refu_ee_contr) < 0 then
482 /* It's refund, thus, it should be negative */
483 p_empr_contr := 0 - l_refu_ee_contr;
484 hr_utility.trace('7) p_empr_contr : '||p_empr_contr);
485 end if;
486
487 /* the UIF contribution is deducted, taking into account whether there was enough Net Pay */
488 p_pay_value := l_ee_contr ;
489
490 /* Adjust Excess Employer Contrib already made in the period */
491 p_ARREAR_UIF := p_ARREAR_UIF - p_excs_er_UIF_cntr_ASG_TAX_PTD;
492 hr_utility.trace('8) p_ARREAR_UIF : '||p_ARREAR_UIF);
493
494 end calc_UIF_contribution ;
495
496
497
498
499 /*******************************************************************************
500 **************** Procedure create_retro_ele_entry *********************
501 ******************************************************************************/
502 procedure create_retro_ele_entry (p_ee_contr_ele_type_id number
503 ,p_retro_ee_contr_ele_type_id number
504 ,p_retro_ee_contr_ip_value_id number
505 ,p_payroll_id number
506 ,p_assact_id number
507 ,p_asg_id number
508 ,p_eff_date date
509 ,p_reflection_date date
510 ,p_time_prd_id number
511 ,p_diff_ee_contr number
512 ,p_ee_contr_ee_id number
513 ) is
514
515 cursor csr_ee_end_date(p_payroll_id number) is
516 select ptp.end_date
517 from per_time_periods ptp
518 where ptp.payroll_id = p_payroll_id
519 and p_reflection_date between start_date and end_date ;
520
521 cursor csr_ele_link_id (p_ele_type_id number) is
522 -- Changed for Bug 7229385
523 -- to pick up element_link more accurately depending on people groups, job, grade
524 -- organization, etc
525 select pel.element_link_id
526 from per_assignments_f ASG,
527 pay_element_links_f PEL
528 where P_REFLECTION_DATE between pel.effective_start_date
529 and pel.effective_end_date
530 and P_REFLECTION_DATE between asg.effective_start_date
531 and asg.effective_end_date
532 -- and pel.element_link_id = P_ELEMENT_LINK_ID
533 and pel.element_type_id = P_ELE_TYPE_ID
534 and asg.assignment_id = P_ASG_ID
535 and ((pel.payroll_id is not null
536 and asg.payroll_id = pel.payroll_id)
537 or (pel.link_to_all_payrolls_flag = 'Y'
538 and asg.payroll_id is not null)
539 or (pel.payroll_id is null
540 and pel.link_to_all_payrolls_flag = 'N'))
541 and (pel.organization_id = asg.organization_id
542 or pel.organization_id is null)
543 and (pel.position_id = asg.position_id
544 or pel.position_id is null)
545 and (pel.job_id = asg.job_id
546 or pel.job_id is null)
547 and (pel.grade_id = asg.grade_id
548 or pel.grade_id is null)
549 and (pel.location_id = asg.location_id
550 or pel.location_id is null)
551 and (
552 pel.pay_basis_id = asg.pay_basis_id
553 or
554 --
555 -- if EL is associated with a pay basis then this clause fails
556 --
557 pel.pay_basis_id is null and
558 NOT EXISTS
559 (SELECT pb.pay_basis_id
560 FROM PER_PAY_BASES pb,
561 PAY_INPUT_VALUES_F iv
562 WHERE iv.element_type_id = pel.element_type_id
563 and P_REFLECTION_DATE between
564 iv.effective_start_date and iv.effective_end_date
565 and pb.input_value_id =
566 iv.input_value_id
567 and pb.business_group_id = asg.business_group_id
568 )
569 or
570 --
571 -- if EL is associated with a pay basis then the associated
572 -- PB_ID must match the PB_ID on ASG
573 --
574 pel.pay_basis_id is null and
575 EXISTS
576 (SELECT pb.pay_basis_id
577 FROM PER_PAY_BASES pb,
578 PAY_INPUT_VALUES_F iv
579 WHERE iv.element_type_id = pel.element_type_id
580 and P_REFLECTION_DATE between
581 iv.effective_start_date and iv.effective_end_date
582 and pb.input_value_id =
583 iv.input_value_id
584 and pb.pay_basis_id = asg.pay_basis_id
585 )
586 or
587 pel.pay_basis_id is null and
588 asg.pay_basis_id is null and
589 EXISTS
590 (SELECT pb.pay_basis_id
591 FROM PER_PAY_BASES pb,
592 PAY_INPUT_VALUES_F iv
593 WHERE iv.element_type_id = pel.element_type_id
594 and P_REFLECTION_DATE between
595 iv.effective_start_date and iv.effective_end_date
596 and pb.input_value_id =
597 iv.input_value_id
598 and pb.business_group_id = asg.business_group_id
599 )
600 )
601 and (pel.employment_category = asg.employment_category
602 or pel.employment_category is null)
603 and (pel.people_group_id is null
604 or exists
605 (select 1
606 from pay_assignment_link_usages_f palu
607 where palu.assignment_id = P_ASG_ID
608 and palu.element_link_id = pel.element_link_id
609 and P_REFLECTION_DATE between palu.effective_start_date
610 and palu.effective_end_date))
611 ;
612
613
614 CURSOR c_get_ee_dtls ( p_element_entry_id IN number
615 , p_eff_dt IN date
616 ) is
617 SELECT original_entry_id,
618 entry_type,
619 cost_allocation_keyflex_id,
620 updating_action_id,
621 updating_action_type,
622 comment_id,
623 reason,
624 target_entry_id,
625 subpriority,
626 date_earned,
627 personal_payment_method_id,
628 attribute_category,
629 attribute1,
630 attribute2,
631 attribute3,
632 attribute4,
633 attribute5,
634 attribute6,
635 attribute7,
636 attribute8,
637 attribute9,
638 attribute10,
639 attribute11,
640 attribute12,
641 attribute13,
642 attribute14,
643 attribute15,
644 attribute16,
645 attribute17,
646 attribute18,
647 attribute19,
648 attribute20,
649 label_identifier
650 FROM pay_element_entries_f pee
651 WHERE pee.element_entry_id = p_element_entry_id
652 AND p_eff_dt BETWEEN pee.effective_start_date
653 AND pee.effective_end_date;
654
655 l_original_entry_id pay_element_entries_f.original_entry_id%TYPE;
656 l_entry_type pay_element_entries_f.entry_type%TYPE;
657 l_cost_allocation_keyflex_id pay_element_entries_f.cost_allocation_keyflex_id%TYPE;
658 l_updating_action_id pay_element_entries_f.updating_action_id%TYPE;
659 l_updating_action_type pay_element_entries_f.updating_action_type%TYPE;
660 l_comment_id pay_element_entries_f.comment_id%TYPE;
661 l_reason pay_element_entries_f.reason%TYPE;
662 l_target_entry_id pay_element_entries_f.target_entry_id%TYPE;
663 l_subpriority pay_element_entries_f.subpriority%TYPE;
664 l_date_earned pay_element_entries_f.date_earned%TYPE;
665 l_personal_payment_method_id pay_element_entries_f.personal_payment_method_id%TYPE;
666 l_attribute_category pay_element_entries_f.attribute_category%TYPE;
667 l_attribute1 pay_element_entries_f.attribute1%TYPE;
668 l_attribute2 pay_element_entries_f.attribute2%TYPE;
669 l_attribute3 pay_element_entries_f.attribute3%TYPE;
670 l_attribute4 pay_element_entries_f.attribute4%TYPE;
671 l_attribute5 pay_element_entries_f.attribute5%TYPE;
672 l_attribute6 pay_element_entries_f.attribute6%TYPE;
673 l_attribute7 pay_element_entries_f.attribute7%TYPE;
674 l_attribute8 pay_element_entries_f.attribute8%TYPE;
675 l_attribute9 pay_element_entries_f.attribute9%TYPE;
676 l_attribute10 pay_element_entries_f.attribute10%TYPE;
677 l_attribute11 pay_element_entries_f.attribute11%TYPE;
678 l_attribute12 pay_element_entries_f.attribute12%TYPE;
679 l_attribute13 pay_element_entries_f.attribute13%TYPE;
680 l_attribute14 pay_element_entries_f.attribute14%TYPE;
681 l_attribute15 pay_element_entries_f.attribute15%TYPE;
682 l_attribute16 pay_element_entries_f.attribute16%TYPE;
683 l_attribute17 pay_element_entries_f.attribute17%TYPE;
684 l_attribute18 pay_element_entries_f.attribute18%TYPE;
685 l_attribute19 pay_element_entries_f.attribute19%TYPE;
686 l_attribute20 pay_element_entries_f.attribute20%TYPE;
687 l_label_identifier pay_element_entries_f.label_identifier%TYPE;
688
689 l_rtr_ee_cntr_ele_link_id number;
690
691 l_prev_entry_start_date date ;
692 l_prev_entry_end_date date ;
693
694 l_element_entry_id number;
695 l_ee_end_date date;
696
697 l_reflection_date date;
698
699 l_proc_name varchar2(30);
700 begin
701
702 l_proc_name := 'create_retro_ele_entry';
703 l_reflection_date := p_reflection_date ;
704
705 hr_utility.trace('Entering ' ||l_proc_name);
706 hr_utility.trace('p_ee_contr_ele_type_id :' || p_ee_contr_ele_type_id);
707
708 -- get end date of the new element entry
709 open csr_ee_end_date (p_payroll_id);
710 fetch csr_ee_end_date into l_ee_end_date;
711 close csr_ee_end_date;
712
713 hr_utility.trace('End date_earned of new element_link_id entry_type :'||to_char(l_ee_end_date));
714
715 -- get element_link_id for the retro_element
716 open csr_ele_link_id (p_retro_ee_contr_ele_type_id);
717 fetch csr_ele_link_id into l_rtr_ee_cntr_ele_link_id;
718 close csr_ele_link_id;
719
720 hr_utility.trace('Element link id for Retro Element :'||l_rtr_ee_cntr_ele_link_id);
721
722 hr_utility.set_location(l_proc_name,20);
723
724 open c_get_ee_dtls (p_ee_contr_ee_id, p_eff_date);
725 fetch c_get_ee_dtls into
726 l_original_entry_id,
727 l_entry_type,
728 l_cost_allocation_keyflex_id,
729 l_updating_action_id,
730 l_updating_action_type,
731 l_comment_id,
732 l_reason,
733 l_target_entry_id,
734 l_subpriority,
735 l_date_earned,
736 l_personal_payment_method_id,
737 l_attribute_category,
738 l_attribute1,
739 l_attribute2,
740 l_attribute3,
741 l_attribute4,
742 l_attribute5,
743 l_attribute6,
744 l_attribute7,
745 l_attribute8,
746 l_attribute9,
747 l_attribute10,
748 l_attribute11,
749 l_attribute12,
750 l_attribute13,
751 l_attribute14,
752 l_attribute15,
753 l_attribute16,
754 l_attribute17,
755 l_attribute18,
756 l_attribute19,
757 l_attribute20,
758 l_label_identifier;
759 close c_get_ee_dtls;
760
761 hr_utility.trace('Values obtained from prev element entry :');
762 hr_utility.trace('Original_entry_id :'||l_original_entry_id);
763 hr_utility.trace('entry_type :'||l_entry_type);
764 hr_utility.trace('updating_action_id :'||l_updating_action_id);
765 hr_utility.trace('target_entry_id :'||l_target_entry_id);
766 hr_utility.trace('date_earned :'||to_char(l_date_earned));
767
768 if l_entry_type in ('R','A') then
769 -- Replacement or Additive Adjustment done to element entry
770 raise excp_uif_manipulated ;
771 end if ;
772 hr_utility.set_location(l_proc_name,30);
773
774 hr_utility.trace('Creating element entry ');
775 hr_entry_api.insert_element_entry(
776 --
777 -- Common Parameters
778 --
779 p_effective_start_date => l_reflection_date,
780 p_effective_end_date => l_ee_end_date,
781 --
782 -- Element Entry Table
783 --
784 p_element_entry_id => l_element_entry_id,
785 p_original_entry_id => l_original_entry_id,
786 p_assignment_id => p_asg_id,
787 p_element_link_id => l_rtr_ee_cntr_ele_link_id,
788 p_creator_type => 'RR',
789 p_entry_type => 'E', -- for Bug 7229385
790 p_cost_allocation_keyflex_id => l_cost_allocation_keyflex_id,
791 p_updating_action_id => l_updating_action_id,
792 p_updating_action_type => l_updating_action_type,
793 p_comment_id => l_comment_id,
794 p_creator_id => null ,-- assignemnt_action_id of retropay run goes here
795 p_reason => l_reason,
796 p_target_entry_id => null, -- for Bug 7229385
797 p_subpriority => l_subpriority,
798 p_date_earned => l_date_earned,
799 p_personal_payment_method_id => l_personal_payment_method_id,
800 p_attribute_category => l_attribute_category,
801 p_attribute1 => l_attribute1,
802 p_attribute2 => l_attribute2,
803 p_attribute3 => l_attribute3,
804 p_attribute4 => l_attribute4,
805 p_attribute5 => l_attribute5,
806 p_attribute6 => l_attribute6,
807 p_attribute7 => l_attribute7,
808 p_attribute8 => l_attribute8,
809 p_attribute9 => l_attribute9,
810 p_attribute10 => l_attribute10,
811 p_attribute11 => l_attribute11,
812 p_attribute12 => l_attribute12,
813 p_attribute13 => l_attribute13,
814 p_attribute14 => l_attribute14,
815 p_attribute15 => l_attribute15,
816 p_attribute16 => l_attribute16,
817 p_attribute17 => l_attribute17,
818 p_attribute18 => l_attribute18,
819 p_attribute19 => l_attribute19,
820 p_attribute20 => l_attribute20,
821 --
822 -- Element Entry Values Table
823 --
824 p_input_value_id1 => p_retro_ee_contr_ip_value_id,
825 p_entry_value1 => p_diff_ee_contr,
826 -- p_override_user_ent_chk varchar2 default 'N',
827 p_label_identifier => l_label_identifier
828 ) ;
829
830 hr_utility.trace('New element entry id :'||l_element_entry_id);
831 hr_utility.set_location(l_proc_name,40);
832
833 select start_date,end_date
834 into l_prev_entry_start_date, l_prev_entry_end_date
835 from per_time_periods
836 where time_period_id = p_time_prd_id ;
837
838 hr_utility.trace('Prev entry Start Date:'||to_char(l_prev_entry_start_date));
839 hr_utility.trace('Prev entry End Date:'||to_char(l_prev_entry_end_date));
840
841 update pay_element_entries_f
842 set source_asg_action_id = p_assact_id
843 ,source_start_date = l_prev_entry_start_date
844 ,source_end_date = l_prev_entry_end_date
845 where element_entry_id = l_element_entry_id;
846
847 hr_utility.trace('SQL%ROWCOUNT :'||SQL%ROWCOUNT);
848 if SQL%ROWCOUNT = 0 then
849 hr_utility.trace('Error : No element entry created');
850 end if ;
851 hr_utility.set_location(l_proc_name,50);
852 end create_retro_ele_entry;
853
854
855
856 /******************************************************************************
857 **************** Procedure create_uif_backdated_entries **************
858 ******************************************************************************/
859
860 procedure create_uif_backdated_entries(errbuf out nocopy varchar2,
861 retcode out nocopy number,
862 p_payroll_id number,
863 p_reflection_date_char varchar2,
864 p_asg_set_id number)
865 is
866
867 cursor c_all_asg_ids(p_payroll_id number,
868 p_effective_date date) is
869 select assignment_id,
870 assignment_number
871 from per_all_assignments_f paaf
872 where payroll_id = p_payroll_id
873 and assignment_status_type_id in (1,3) -- pick active and terminated ( whose final process date is left) assignments
874 and p_effective_date between effective_start_date and effective_end_date ;
875
876 cursor c_get_ee_id (p_ele_type_id number
877 ,p_asact_id number) is
878 select prr.element_entry_id
879 from pay_run_results prr
880 where prr.assignment_action_id = p_asact_id
881 and prr.element_type_id = p_ele_type_id ;
882
883 l_asg_id number;
884 l_pact_id number;
885 l_assact_id number;
886 l_eff_date date;
887 l_time_prd_id number ;
888 l_rec_count number ;
889
890 l_proc_name varchar2(30);
891
892 l_ee_contr_ele_type_id number;
893 l_ee_contr_ip_value_id number;
894 l_er_contr_ele_type_id number;
895 l_er_contr_ip_value_id number;
896 l_excs_er_contr_ele_type_id number;
897 l_excs_er_contr_ip_value_id number;
898
899 l_retro_ee_contr_ele_type_id number;
900 l_retro_ee_contr_ip_value_id number;
901 l_retro_er_contr_ele_type_id number;
902 l_retro_er_contr_ip_value_id number;
903 l_retro_excs_er_cntr_ele_tp_id number;
904 l_retro_excs_er_cntr_ip_val_id number;
905
906 rec_assact tab_assact ;
907 unprocessed_assignments tab_assact ;
908 unprocessed_asgn_count number := 0 ;
909
910 l_prev_ee_cntr number;
911 l_prev_er_cntr number;
912 l_prev_excs_er_cntr number;
913
914 l_process_run_count number;
915
916 l_calc_ee_contr number;
917 l_calc_empr_contr number;
918 l_calc_ARREAR_UIF number;
919
920 l_diff_ee_contr number;
921 l_diff_er_contr number;
922 l_diff_excs_er_contr number;
923
924 p_reflection_date date ;
925 l_ee_contr_ee_id number;
926
927 l_last_printed_asg number;
928
929 v_incl_sw char;
930 asg_include boolean;
931 l_action_type varchar2(10);
932 l_header_printed boolean := false;
933
934 begin
935 -- hr_utility.trace_on(null,'ZA_UIF');
936
937 hr_utility.trace('Starting Trace for UIF Refund March 2008');
938 p_reflection_date := to_date(p_reflection_date_char,'YYYY/MM/DD HH24:MI:SS');
939 l_proc_name := 'create_uif_backdated_entries' ;
940
941 hr_utility.set_location('Entering '||l_proc_name,10);
942 hr_utility.set_location('payroll_id : ' || p_payroll_id,1);
943 hr_utility.set_location('Effective_date : ' || p_reflection_date,1);
944 hr_utility.set_location('Assignment Set Id : ' ||p_asg_set_id,1);
945
946
947 ---------------------------------------------------------------------------
948 -------------- Get Element Details -------------------------
949 ---------------------------------------------------------------------------
950 begin
951 get_ele_dtls ('ZA_UIF_Employee_Contribution',sysdate,l_ee_contr_ele_type_id,l_ee_contr_ip_value_id);
952 get_ele_dtls ('ZA_UIF_Employer_Contribution',sysdate,l_er_contr_ele_type_id,l_er_contr_ip_value_id);
953 get_ele_dtls ('ZA Excess Employer UIF Contribution',sysdate,l_excs_er_contr_ele_type_id,l_excs_er_contr_ip_value_id);
954
955 get_ele_dtls ('ZA_Retro_UIF_Employee_Contribution',sysdate,l_retro_ee_contr_ele_type_id,l_retro_ee_contr_ip_value_id);
956 get_ele_dtls ('ZA_Retro_UIF_Employer_Contribution',sysdate,l_retro_er_contr_ele_type_id,l_retro_er_contr_ip_value_id);
957 get_ele_dtls ('ZA Retro Excess Employer UIF Contribution',sysdate,l_retro_excs_er_cntr_ele_tp_id,l_retro_excs_er_cntr_ip_val_id);
958
959 hr_utility.set_location('l_ee_contr_ele_type_id : ' || l_ee_contr_ele_type_id,2);
960 hr_utility.set_location('l_ee_contr_ip_value_id : ' || l_ee_contr_ip_value_id,2);
961 hr_utility.set_location('l_er_contr_ele_type_id : ' || l_er_contr_ele_type_id,2);
962 hr_utility.set_location('l_er_contr_ip_value_id : ' || l_er_contr_ip_value_id,2);
963 hr_utility.set_location('l_excs_er_contr_ele_type_id : ' || l_excs_er_contr_ele_type_id,2);
964 hr_utility.set_location('l_excs_er_contr_ip_value_id : ' || l_excs_er_contr_ip_value_id,2);
965 exception
966 WHEN others then
967 hr_utility.set_location('ERROR while getting element_details ',9999);
968 hr_utility.set_location('Error code is ' || SQLCODE, 9999);
969 hr_utility.set_location('Error Messages' || substr(SQLERRM,1,255), 9999);
970 RAISE;
971 end;
972
973 ---------------------------------------------------------------------------
974 -------------- Start Processing -------------------------
975 ---------------------------------------------------------------------------
976
977 l_rec_count := 0 ;
978
979 if p_asg_set_id is not null then
980 begin
981 select distinct include_or_exclude
982 into v_incl_sw
983 from hr_assignment_set_amendments
984 where assignment_set_id = p_asg_set_id;
985 exception
986 when no_data_found then
987 v_incl_sw := 'I';
988 end;
989 end if;
990
991 -- Get All 'ACTIVE' and 'TERMINATED' ( whose FinalProcessDate >= reflection date) Assignments for the payroll
992 for rec_all_asg_ids in c_all_asg_ids ( p_payroll_id, p_reflection_date)
993 loop
994 -- Check the Assignment set to see if the assignment should be
995 -- processed or not
996 asg_include := TRUE;
997 if p_asg_set_id is not null then
998 declare
999 inc_flag varchar2(5);
1000 begin
1001 select include_or_exclude
1002 into inc_flag
1003 from hr_assignment_set_amendments
1004 where assignment_set_id = p_asg_set_id
1005 and assignment_id = rec_all_asg_ids.assignment_id;
1006
1007 if inc_flag = 'E' then
1008 asg_include := FALSE;
1009 hr_utility.set_location('Excluding Assignment '||rec_all_asg_ids.assignment_id,10);
1010 else
1011 hr_utility.set_location('Including Assignment '||rec_all_asg_ids.assignment_id,20);
1012 end if;
1013 exception
1014 when no_data_found then
1015 if v_incl_sw = 'I' then
1016 asg_include := FALSE;
1017 hr_utility.set_location('Excluding Assignment '||rec_all_asg_ids.assignment_id,30);
1018 else
1019 asg_include := TRUE;
1020 hr_utility.set_location('Including Assignment '||rec_all_asg_ids.assignment_id,40);
1021 end if;
1022 end ;
1023 end if;
1024
1025 if asg_include = TRUE then
1026 -- Populate table rec_assact with max(action_sequence)
1027 -- for all the assignment for the months Oct2007 - Jan2008.
1028 -- The table will contain 4 rows (for each month) per assignment
1029 hr_utility.trace('Populating table for assignment_id :'||rec_all_asg_ids.assignment_id||' assignment_number :'||rec_all_asg_ids.assignment_number) ;
1030 populate_assact_tab (rec_assact,
1031 rec_all_asg_ids.assignment_id,
1032 rec_all_asg_ids.assignment_number,
1033 l_rec_count,
1034 l_ee_contr_ele_type_id) ;
1035 end if ;
1036 end loop ;
1037
1038 -- Loop through all the assignments per month
1039 hr_utility.trace('rec_assact.count :'||rec_assact.count);
1040
1041 -- Checking if there is any row to process
1042 if rec_assact.first is null then
1043 hr_utility.trace('No assignment to process... Exitting');
1044 return ;
1045 end if ;
1046
1047 l_last_printed_asg := -1 ;
1048
1049 for rec_count in rec_assact.first .. rec_assact.last
1050 loop
1051 -- Get payroll_action and assignment_action details
1052 -- for the action_sequence
1053 begin
1054
1055 hr_utility.trace('Processing Assignment_ID : '||rec_assact(rec_count).assignment_id);
1056 hr_utility.trace('Action Sequence : '||rec_assact(rec_count).action_seq);
1057
1058
1059 begin
1060 select ppa.payroll_action_id
1061 ,paa.assignment_action_id
1062 ,ppa.effective_date
1063 ,ppa.time_period_id
1064 ,paa.assignment_id
1065 ,ppa.action_type
1066 into l_pact_id
1067 ,l_assact_id
1068 ,l_eff_date
1069 ,l_time_prd_id
1070 ,l_asg_id
1071 ,l_action_type
1072 from pay_payroll_actions ppa
1073 ,pay_assignment_actions paa
1074 where ppa.payroll_action_id = paa.payroll_action_id
1075 and paa.action_sequence = rec_assact(rec_count).action_seq
1076 and paa.assignment_id = rec_assact(rec_count).assignment_id ;
1077 exception
1078 when others then
1079 hr_utility.trace('Error while fetching payroll_action/assignment_action details for assignment_id '||rec_assact(rec_count).assignment_id);
1080 RAISE;
1081 end ;
1082
1083 hr_utility.trace('Month_Year : '||rec_assact(rec_count).month_yr);
1084 hr_utility.trace('Payroll_action_id : '||l_pact_id);
1085 hr_utility.trace('Assignment_action_id : '||l_assact_id);
1086 hr_utility.trace('Effective_Date : '||l_eff_date);
1087 hr_utility.trace('Time_Period_ID : '||l_time_prd_id);
1088 hr_utility.trace('Action_Type : '||l_action_type);
1089
1090 -- Get MTD balances for UIF contributions
1091 -- commented while testing for weekly payrolls
1092 -- l_prev_ee_cntr := nvl(get_balance_value('UIF Employee Contribution','_ASG_TAX_MTD',l_assact_id),0);
1093 -- l_prev_er_cntr := nvl(get_balance_value('UIF Employer Contribution','_ASG_TAX_MTD',l_assact_id),0);
1094 -- l_prev_excs_er_cntr := nvl(get_balance_value('Excess Employer UIF Contrib','_ASG_TAX_MTD',l_assact_id),0);
1095
1096 -- Check if retro entries have already been created
1097 -- which would mean that the customer has already run the process
1098 select count(1)
1099 into l_process_run_count
1100 from pay_element_entries_f
1101 where source_asg_action_id = l_assact_id
1102 and element_type_id in (l_retro_ee_contr_ele_type_id,
1103 l_retro_er_contr_ele_type_id,
1104 l_retro_excs_er_cntr_ele_tp_id);
1105
1106 hr_utility.trace('Process run count :'||l_process_run_count);
1107
1108 if l_process_run_count = 0 then
1109 if l_action_type in ('B','V','I') then
1110 raise excp_uif_manipulated ;
1111 end if ;
1112 -- Calculate the UIF amounts as per the latest global values
1113 calc_UIF_contribution(l_pact_id, p_payroll_id, l_eff_date, l_assact_id,
1114 l_calc_ee_contr, l_calc_empr_contr, l_calc_ARREAR_UIF,
1115 l_prev_ee_cntr, l_prev_er_cntr, l_prev_excs_er_cntr );
1116
1117 l_calc_ee_contr := nvl(l_calc_ee_contr,0);
1118 l_calc_empr_contr := nvl(l_calc_empr_contr,0);
1119 l_calc_ARREAR_UIF := nvl(l_calc_ARREAR_UIF,0);
1120
1121 hr_utility.trace('Calculated UIF Contributions :');
1122 hr_utility.trace('Employee Contribution :'|| l_calc_ee_contr);
1123 hr_utility.trace('Employer Contribution :'|| l_calc_empr_contr);
1124 hr_utility.trace('Arrears :'|| l_calc_ARREAR_UIF);
1125 hr_utility.trace('Balance UIF_Employee_Contribution_ASG_TAX_MTD :'||l_prev_ee_cntr);
1126 hr_utility.trace('Balance UIF_Employer_Contribution_ASG_TAX_MTD :'||l_prev_er_cntr);
1127 hr_utility.trace('Balance Excess_Employer_UIF_Contrib_ASG_TAX_PTD :'||l_prev_excs_er_cntr);
1128
1129 -- Calculate differences between the previous balances and the
1130 -- the newly calculated UIF contributions
1131
1132 -- commented while testing for weekly payrolls
1133 -- l_diff_ee_contr := l_calc_ee_contr - l_prev_ee_cntr ;
1134 -- l_diff_er_contr := l_calc_empr_contr - l_prev_er_cntr ;
1135 -- l_diff_excs_er_contr := l_calc_ARREAR_UIF - l_prev_excs_er_cntr ;
1136
1137 l_diff_ee_contr := l_calc_ee_contr ;
1138 l_diff_er_contr := l_calc_empr_contr ;
1139 l_diff_excs_er_contr := l_calc_ARREAR_UIF ;
1140
1141 hr_utility.trace('Differences :'||l_diff_ee_contr|| ' '|| l_diff_er_contr||' '||l_diff_excs_er_contr);
1142
1143 -- get element_entry_id for previous UIF contribution element
1144 open c_get_ee_id(l_ee_contr_ele_type_id, l_assact_id);
1145 fetch c_get_ee_id into l_ee_contr_ee_id ;
1146 close c_get_ee_id ;
1147
1148 hr_utility.trace('Element entry ID of prev UIF contri elem :'||l_ee_contr_ee_id);
1149
1150 -- Create element entries for the differences
1151 hr_utility.trace('Creating element entry for Employee COntribution');
1152 if l_diff_ee_contr <> 0 then
1153 create_retro_ele_entry(l_ee_contr_ele_type_id
1154 ,l_retro_ee_contr_ele_type_id
1155 ,l_retro_ee_contr_ip_value_id
1156 ,p_payroll_id
1157 ,l_assact_id
1158 ,l_asg_id
1159 ,l_eff_date
1160 ,p_reflection_date
1161 ,l_time_prd_id
1162 ,l_diff_ee_contr
1163 ,l_ee_contr_ee_id
1164 );
1165 hr_utility.set_location(l_proc_name,100);
1166 end if ;
1167
1168 hr_utility.trace('Creating element entry for Employer COntribution');
1169 if l_diff_er_contr <> 0 then
1170 create_retro_ele_entry(l_er_contr_ele_type_id
1171 ,l_retro_er_contr_ele_type_id
1172 ,l_retro_er_contr_ip_value_id
1173 ,p_payroll_id
1174 ,l_assact_id
1175 ,l_asg_id
1176 ,l_eff_date
1177 ,p_reflection_date
1178 ,l_time_prd_id
1179 ,l_diff_er_contr
1180 ,l_ee_contr_ee_id
1181 );
1182 hr_utility.set_location(l_proc_name,110);
1183 end if ;
1184
1185 hr_utility.trace('Creating element entry for Excess Employer COntribution');
1186 if l_diff_excs_er_contr <> 0 then
1187 create_retro_ele_entry(l_excs_er_contr_ele_type_id
1188 ,l_retro_excs_er_cntr_ele_tp_id
1189 ,l_retro_excs_er_cntr_ip_val_id
1190 ,p_payroll_id
1191 ,l_assact_id
1192 ,l_asg_id
1193 ,l_eff_date
1194 ,p_reflection_date
1195 ,l_time_prd_id
1196 ,l_diff_excs_er_contr
1197 ,l_ee_contr_ee_id
1198 );
1199 hr_utility.set_location(l_proc_name,120);
1200 end if ;
1201
1202 /**********************************
1203 ***** Printing the report ********
1204 **********************************/
1205
1206 if l_diff_ee_contr <>0 or l_diff_er_contr <>0 or l_diff_excs_er_contr <>0 then
1207 if l_header_printed = false then
1208 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1209 FND_FILE.PUT_LINE(FND_FILE.LOG,'The following Assignments have been processed for the below mentioned calendar months -');
1210 l_header_printed := true ;
1211 end if;
1212 if l_last_printed_asg <> l_asg_id then
1213 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1214 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------');
1215 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1216 FND_FILE.PUT_LINE(FND_FILE.LOG,'Assignment Number : '||rec_assact(rec_count).assignment_number);
1217 FND_FILE.PUT_LINE(FND_FILE.LOG,'Assignment ID : '||l_asg_id);
1218 end if ;
1219 l_last_printed_asg := l_asg_id ;
1220 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1221 FND_FILE.PUT_LINE(FND_FILE.LOG,'Duration :' || rec_assact(rec_count).month_yr);
1222 FND_FILE.PUT_LINE(FND_FILE.LOG,lpad(' ',32,' ')|| lpad('Existing Value',25,' ')||lpad('Expected Value',25,' ')||lpad('Retro Element Entry Amount',30,' ') );
1223 end if ;
1224
1225 if l_diff_ee_contr<>0 then
1226 FND_FILE.PUT_LINE(FND_FILE.LOG,rpad('Employee Contributions :',32,' ') ||lpad(l_prev_ee_cntr,25,' ')|| lpad((l_prev_ee_cntr+l_diff_ee_contr),25,' ')|| lpad(l_diff_ee_contr,30,' '));
1227 end if ;
1228
1229 if l_diff_er_contr <> 0 then
1230 -- Bug 7175221
1231 -- Empr Contr balance = empr contribution + Excess empr contribution
1232 -- hence subtract excess empr contribution from Empr Contr Bal to get the actual Empr Contribution
1233 l_prev_er_cntr := l_prev_er_cntr - nvl(l_prev_excs_er_cntr,0);
1234 FND_FILE.PUT_LINE(FND_FILE.LOG,rpad('Employer Contributions :',32,' ')||lpad(l_prev_er_cntr,25,' ')|| lpad((l_prev_er_cntr+l_diff_er_contr),25,' ')|| lpad(l_diff_er_contr,30,' '));
1235 end if ;
1236
1237 if l_diff_excs_er_contr <> 0 then
1238 FND_FILE.PUT_LINE(FND_FILE.LOG,rpad('Excess Employer Contributions :',32,' ')||lpad(l_prev_excs_er_cntr,25,' ')|| lpad((l_prev_excs_er_cntr+l_diff_excs_er_contr),25,' ')|| lpad(l_diff_excs_er_contr,30,' '));
1239 end if ;
1240
1241 else
1242 hr_utility.trace('Process already run for assignment_id :'||l_asg_id);
1243 end if ;
1244
1245 hr_utility.trace('Completed processing for Assignment :'||rec_assact(rec_count).assignment_id||' Month Year :'||rec_assact(rec_count).month_yr);
1246 exception
1247 when excp_uif_manipulated then
1248 unprocessed_asgn_count := unprocessed_asgn_count + 1 ;
1249 unprocessed_assignments(unprocessed_asgn_count).assignment_id := rec_assact(rec_count).assignment_id;
1250 unprocessed_assignments(unprocessed_asgn_count).assignment_number := rec_assact(rec_count).assignment_number;
1251 unprocessed_assignments(unprocessed_asgn_count).month_yr := rec_assact(rec_count).month_yr;
1252 end ;
1253 end loop;
1254
1255 -- Print unprocessed assignments in the log file
1256 if unprocessed_asgn_count > 0 then
1257 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1258 FND_FILE.PUT_LINE(FND_FILE.LOG,rpad('-',120,'-'));
1259 FND_FILE.PUT_LINE(FND_FILE.LOG,rpad('-',120,'-'));
1260 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1261 FND_FILE.PUT_LINE(FND_FILE.LOG,'The following Assignments were not processed for the below mentioned calendar months,');
1262 FND_FILE.PUT_LINE(FND_FILE.LOG,'as UIF contributions for these have been manually adjusted.');
1263 FND_FILE.PUT_LINE(FND_FILE.LOG,'Please review the same and perform the adjustments as required.');
1264 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1265 FND_FILE.PUT_LINE(FND_FILE.LOG,rpad('Assignment Number',50)|| rpad('Calendar Month',20));
1266 FND_FILE.PUT_LINE(FND_FILE.LOG,rpad('-',45,'-')|| rpad(' ',5,' ')||rpad('-',20,'-'));
1267 for asgn_count in unprocessed_assignments.first .. unprocessed_assignments.last
1268 loop
1269 FND_FILE.PUT_LINE(FND_FILE.LOG,rpad(unprocessed_assignments(asgn_count).assignment_number,50)|| rpad(unprocessed_assignments(asgn_count).month_yr,'20'));
1270 end loop ;
1271 end if;
1272
1273 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1274 -- Clear PL/SQL table
1275 --rec_assact.DELETE ;
1276 commit ;
1277
1278 hr_utility.trace('Exiting '||l_proc_name);
1279 hr_utility.trace('Trace for UIF Refund March 2008 ends');
1280 end create_uif_backdated_entries;
1281
1282 end PAY_ZA_UIF_REFUND_MARCH_2008;