[Home] [Help]
PACKAGE BODY: APPS.PAY_ZA_COIDA_ARCHIVE
Source
1 PACKAGE BODY PAY_ZA_COIDA_ARCHIVE AS
2 /* $Header: pyzacoia.pkb 120.12.12020000.4 2012/07/13 04:07:47 mkuppuch ship $ */
3
4 g_package varchar2(100) :='PAY_ZA_COIDA_ARCHIVE.';
5 g_arch_effective_date date;
6 g_start_period_date date;
7 g_end_period_date date;
8 g_coida_limit1 number;
9 g_coida_limit2 number;
10 g_coida_start1 date;
11 g_coida_start2 date;
12 g_coida_end1 date;
13 g_coida_end2 date;
14 g_split_year varchar2(1):='N';
15 g_num_of_days number;
16
17 /*--------------------------------------------------------------------------
18 Name : get_act_earnings
19 --------------------------------------------------------------------------*/
20 procedure get_act_earnings
21 (
22 p_total_return in number,
23 p_days_worked in number,
24 p_date in date,
25 p_act_earn1 out nocopy number,
26 p_act_earn2 out nocopy number
27 )
28 is
29 begin
30 --Calculate the actual earnings
31 if g_split_year='Y' then
32 if p_days_worked <> 0 then
33 if p_date <= g_coida_end1 then
34 if (p_total_return * g_num_of_days)/p_days_worked < g_coida_limit1 then
35 p_act_earn1 := trunc(p_total_return);
36 else
37 p_act_earn1 := trunc((g_coida_limit1/g_num_of_days) * p_days_worked);
38 end if;
39 else
40 if (p_total_return * g_num_of_days)/p_days_worked < g_coida_limit2 then
41 p_act_earn2 := trunc(p_total_return);
42 else
43 p_act_earn2 := trunc((g_coida_limit2/g_num_of_days) * p_days_worked);
44 end if;
45 end if;
46 else
47 p_act_earn1 :=0;
48 p_act_earn2 :=0;
49 end if;
50 else
51 if p_days_worked <> 0 then
52 if (p_total_return * g_num_of_days)/p_days_worked < g_coida_limit1 then
53 p_act_earn1 := trunc(p_total_return);
54 else
55 p_act_earn1 := trunc((g_coida_limit1/g_num_of_days) * p_days_worked);
56 end if;
57 else
58 p_act_earn1 :=0;
59 end if;
60 end if;
61 end get_act_earnings;
62
63 /*--------------------------------------------------------------------------
64 Name : get_parameter
65 Purpose : Returns a legislative parameter
66 Arguments :
67 Notes : The legislative parameter field must be of the form:
68 PARAMETER_NAME=PARAMETER_VALUE. No spaces is allowed in either
69 the PARAMETER_NAME or the PARAMETER_VALUE.
70 --------------------------------------------------------------------------*/
71 function get_parameter
72 (
73 name in varchar2,
74 parameter_list varchar2
75 ) return varchar2 is
76 start_ptr number;
77 end_ptr number;
78 token_val pay_payroll_actions.legislative_parameters%type;
79 par_value pay_payroll_actions.legislative_parameters%type;
80
81 begin
82
83 token_val := name || '=';
84
85 start_ptr := instr(parameter_list, token_val) + length(token_val);
86 end_ptr := instr(parameter_list, ' ', start_ptr);
87
88 /* if there is no spaces, then use the length of the string */
89 if end_ptr = 0 then
90 end_ptr := length(parameter_list) + 1;
91 end if;
92
93 /* Did we find the token */
94 if instr(parameter_list, token_val) = 0 then
95 par_value := NULL;
96 else
97 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
98 end if;
99
100 return par_value;
101
102 end get_parameter;
103
104 procedure archinit(p_payroll_action_id in number)
105 is
106 begin
107 null;
108 end archinit;
109
110
111
112 -----------------------------------------------------------------------
113 --procedure set_globals
114 -----------------------------------------------------------------------
115 procedure set_globals(p_payroll_action_id number)
116 is
117 cursor csr_coida_payroll_info
118 is
119 select fnd_date.canonical_to_date(pai.action_information4) start_date
120 , fnd_date.canonical_to_date(pai.action_information5) end_date
121 , pai.action_information8 limit1
122 , pai.action_information9 limit2
123 , fnd_date.canonical_to_date(pai.action_information11) split_start_date1
124 , fnd_date.canonical_to_date(pai.action_information12) split_end_date1
125 , fnd_date.canonical_to_date(pai.action_information13) split_start_date2
126 , fnd_date.canonical_to_date(pai.action_information14) split_end_date2
127 , pai.action_information7 num_of_days
128 from pay_action_information pai
129 where action_context_id = p_payroll_action_id
130 and action_information_category = 'ZA_COID_PAYROLL_INFO';
131
132 begin
133 open csr_coida_payroll_info;
134 fetch csr_coida_payroll_info into g_start_period_date, g_end_period_date, g_coida_limit1
135 , g_coida_limit2, g_coida_start1, g_coida_end1, g_coida_start2
136 , g_coida_end2, g_num_of_days;
137 close csr_coida_payroll_info;
138
139 if nvl(g_coida_limit2,0) <> 0 then
140 g_split_year:='Y';
141 end if;
142
143 end set_globals;
144
145 -----------------------------------------------------------------------
146 --procedure range_cursor
147 -----------------------------------------------------------------------
148
149 procedure range_cursor
150 (
151 pactid in number,
152 sqlstr out nocopy varchar2
153 )
154 is
155
156 CURSOR csr_global (p_start_period_date date,p_end_period_date date) IS
157 Select
158 global_value,
159 effective_start_date,
160 effective_end_date
161 From
162 ff_globals_f
163 Where
164 global_name = 'ZA_COIDA_LIMIT'
165 And legislation_code = 'ZA'
166 And (p_start_period_date between effective_start_date and effective_end_date
167 Or p_end_period_date between effective_start_date and effective_end_date)
168 Order By effective_start_date;
169
170 cursor csr_archive_effective_date is
171 select effective_date
172 from pay_payroll_actions
173 where payroll_action_id = pactid;
174
175 l_proc varchar2(200);
176 l_payroll_id number;
177 l_business_grp_id number;
178 l_effective_date date;
179 l_org_name varchar2(240);
180 l_payroll_name varchar2(80);
181 l_split1 varchar2(200);
182 l_split2 varchar2(200);
183 l_coida_limit_display varchar2(200);
184 l_pay_period_display varchar2(200);
185 l_action_info_id number;
186 l_ovn number;
187 l_num_of_months1 number;
188 l_num_of_months2 number;
189 leg_param varchar2(2000);
190 l_strt_prd_id number;
191 l_end_prd_id number;
192
193 begin
194
195 l_proc := g_package||'range_cursor';
196 fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
197 hr_utility.set_location('Entering '||l_proc,10);
198
199 open csr_archive_effective_date;
200 fetch csr_archive_effective_date into g_arch_effective_date;
201 close csr_archive_effective_date;
202
203 -- Archive Payroll Level Information
204
205 -- Retrieve legislative parameters from the archiver payroll action
206 select legislative_parameters
207 into leg_param
208 from pay_payroll_actions
209 where payroll_action_id = pactid;
210
211 l_payroll_id := get_parameter('PAYROLL_ID', leg_param);
212 l_business_grp_id := get_parameter('BUS_GRP_ID', leg_param);
213 l_strt_prd_id := get_parameter('STRT_PERIOD_ID', leg_param);
214 l_end_prd_id := get_parameter('END_PERIOD_ID', leg_param);
215
216 -- Retrieve the report start and end dates
217 SELECT ptp.start_date
218 INTO g_start_period_date
219 FROM per_time_periods ptp
220 WHERE ptp.time_period_id = l_strt_prd_id;
221
222 SELECT ptp.end_date
223 INTO g_end_period_date
224 FROM per_time_periods ptp
225 WHERE ptp.time_period_id = l_end_prd_id;
226
227 --Retrieve Organization and Payroll name
228 Begin
229 Select name
230 into l_org_name
231 From
232 hr_organization_units
233 Where
234 business_group_id +0 = l_business_grp_id
235 And business_group_id +0 = organization_id;
236
237 Select payroll_name
238 into l_payroll_name
239 From pay_payrolls_f
240 Where payroll_id = l_payroll_id
241 And g_end_period_date between effective_start_date and effective_end_date;
242
243 Exception
244 When no_data_found then
245 null;
246 End;
247
248 l_pay_period_display := to_char(g_start_period_date,'DD-MON-YYYY')||' to '||to_char(g_end_period_date,'DD-MON-YYYY');
249 Begin
250 Select
251 'Y' into g_split_year
252 From
253 dual
254 Where
255 1 < (Select
256 count(global_value)
257 From
258 ff_globals_f
259 Where
260 global_name = 'ZA_COIDA_LIMIT'
261 And legislation_code = 'ZA'
262 And (g_start_period_date between effective_start_date and effective_end_date
263 Or g_end_period_date between effective_start_date and effective_end_date));
264
265 Exception
266 When no_data_found then
267 null;
268 End;
269
270 Open csr_global(g_start_period_date,g_end_period_date);
271 Fetch csr_global Into g_coida_limit1, g_coida_start1, g_coida_end1; -- Fetch first row
272 If g_split_year = 'Y' Then
273 Fetch csr_global Into g_coida_limit2, g_coida_start2, g_coida_end2; -- Fetch second row
274 End If;
275 Close csr_global;
276
277 Begin
278 --Start Bug5973492: show the correct split heading
279 if g_split_year = 'Y' then
280 l_split1 := to_char(g_start_period_date,'DD-MON-YYYY')||'/ ' || to_char(g_coida_end1,'DD-MON-YYYY');
281 l_split2 := to_char(g_coida_start2,'DD-MON-YYYY')||'/ ' || to_char(g_end_period_date,'DD-MON-YYYY');
282 --End Bug5973492
283 l_coida_limit_display := g_coida_limit1||' / '||g_coida_limit2;
284 l_num_of_months1 := ceil(months_between(g_coida_end1+1,g_start_period_date)); -- Bug 12552359
285 l_num_of_months2 := ceil(months_between(g_end_period_date+1,g_coida_start2)); -- Bug 12552359
286 else
287 l_split1 := to_char(g_start_period_date,'DD-MON-YYYY')||'/ ' || to_char(g_end_period_date,'DD-MON-YYYY');
288 l_split2 := '';
289 l_coida_limit_display := g_coida_limit1;
290 l_num_of_months1 := ceil(months_between(g_end_period_date+1,g_start_period_date)); -- Bug 12552359
291 end if;
292 End;
293
294 -- Get the number of days in the tax year
295 begin
296 select max(end_date) - min(start_date) + 1
297 into g_num_of_days
298 from per_time_periods
299 where payroll_id = l_payroll_id
300 and prd_information1 = (select prd_information1
301 from per_time_periods
302 where payroll_id = l_payroll_id
303 and g_start_period_date between start_date and end_date);
304 end;
305
306 --Update the payroll_id column of pay_payroll_actions
307 update pay_payroll_actions
308 set payroll_id = l_payroll_id
309 where payroll_action_id = pactid;
310
311 sqlstr :=
312 'select distinct ass.person_id
313 from per_assignments_f ass,
314 pay_payrolls_f ppf,
315 pay_payroll_actions ppa
316 where ppa.payroll_action_id = :payroll_action_id
317 and ass.business_group_id = ppa.business_group_id
318 and ass.assignment_type = ''E''
319 and ppf.payroll_id = ass.payroll_id
320 and ppf.payroll_id = ppa.payroll_id
321 order by ass.person_id';
322
323 hr_utility.set_location('Populating ZA_COID_PAYROLL_INFO',60);
324 -- Archive 'ZA_COID_PAYROLL_INFO'
325 pay_action_information_api.create_action_information
326 (
327 p_action_information_id => l_action_info_id,
328 p_action_context_id => pactid,
329 p_action_context_type => 'PA',
330 p_object_version_number => l_ovn,
331 p_effective_date => g_arch_effective_date,
332 p_action_information_category => 'ZA_COID_PAYROLL_INFO',
333 p_action_information1 => l_payroll_name,
334 p_action_information2 => l_payroll_id,
335 p_action_information3 => l_org_name,
336 p_action_information4 => fnd_date.date_to_canonical(g_start_period_date),
337 p_action_information5 => fnd_date.date_to_canonical(g_end_period_date),
338 p_action_information6 => l_pay_period_display,
339 p_action_information7 => g_num_of_days,
340 p_action_information8 => g_coida_limit1,
341 p_action_information9 => g_coida_limit2,
342 p_action_information10 => l_coida_limit_display,
343 p_action_information11 => fnd_date.date_to_canonical(g_coida_start1),
344 p_action_information12 => fnd_date.date_to_canonical(g_coida_end1),
345 p_action_information13 => fnd_date.date_to_canonical(g_coida_start2),
346 p_action_information14 => fnd_date.date_to_canonical(g_coida_end2),
347 p_action_information15 => l_split1,
348 p_action_information16 => l_split2,
349 p_action_information17 => l_num_of_months1,
350 p_action_information18 => l_num_of_months2
351 );
352
353 hr_utility.set_location('Leaving '||l_proc,90);
354 end range_cursor;
355
356 procedure action_creation
357 (
358 pactid in number,
359 stperson in number,
360 endperson in number,
361 chunk in number
362 )
363 is
364 l_proc varchar2(200) :=g_package||'action_creation';
365
366 cursor csr_assignments(l_payroll_id number,l_end_period_id number,l_start_period_id number) IS
367 SELECT
368 asg.person_id
369 , asg.assignment_id
370 FROM
371 per_assignment_extra_info paei
372 , per_all_assignments_f asg
373 , per_person_type_usages_f pptu
374 , per_person_types ppt
375 WHERE
376 asg.payroll_id = l_payroll_id
377 AND asg.effective_end_date =
378 ( SELECT
379 MAX(asgm.effective_end_date)
380 FROM
381 per_assignments_f asgm
382 WHERE
383 asgm.assignment_id = asg.assignment_id
384 AND g_start_period_date <= asgm.effective_end_date
385 AND g_end_period_date >= asgm.effective_start_date
386 and asgm.payroll_id = l_payroll_id -- Bug 12572065
387 )
388 AND g_end_period_date BETWEEN pptu.effective_start_date and pptu.effective_end_date
389 AND asg.person_id between stperson and endperson
390 AND pptu.person_id = asg.person_id
391 AND pptu.person_type_id = ppt.person_type_id
392 AND ppt.system_person_type in ('EMP','EX_EMP')
393 AND asg.assignment_id = paei.assignment_id
394 AND paei.information_type = 'ZA_SPECIFIC_INFO'
395 AND exists (select 1
396 from pay_payroll_actions ppa,
397 pay_assignment_actions paa
398 where
399 ppa.payroll_id=asg.payroll_id
400 and paa.assignment_id=asg.assignment_id
401 and ppa.time_period_id <=l_end_period_id
402 and ppa.time_period_id >=l_start_period_id
403 and paa.payroll_action_id=ppa.payroll_action_id
404 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
405 and paa.action_status='C'
406 );
407
408
409 l_payroll_id number;
410 l_end_prd_id number;
411 l_strt_prd_id number;
412 prev_asg_id number:=0;
413 lockingactid number;
414 leg_param varchar2(2000);
415
416 begin
417 hr_utility.set_location('Entering '||l_proc, 10);
418 fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
419
420 -- Retrieve legislative parameters from the archiver payroll action
421 select legislative_parameters
422 into leg_param
423 from pay_payroll_actions
424 where payroll_action_id = pactid;
425
426 l_payroll_id := get_parameter('PAYROLL_ID', leg_param);
427 l_end_prd_id := get_parameter('END_PERIOD_ID', leg_param);
428 l_strt_prd_id := get_parameter('STRT_PERIOD_ID', leg_param);
429
430 if nvl(g_coida_limit1,0) = 0 then
431 set_globals(pactid);
432 end if;
433
434 for asgrec in csr_assignments(l_payroll_id,l_end_prd_id,l_strt_prd_id) loop
435 hr_utility.set_location('Assignment_id : ' || to_char(asgrec.assignment_id), 20);
436 -- Remove duplicate assignments
437 if prev_asg_id <> asgrec.assignment_id then -- revisit -- check if required?
438
439 prev_asg_id := asgrec.assignment_id;
440
441 hr_utility.set_location('Creating action for assignment_id '||asgrec.assignment_id, 50);
442 select pay_assignment_actions_s.nextval
443 into lockingactid
444 from dual;
445
446 -- Insert assignment into pay_assignment_actions
447 hr_nonrun_asact.insact
448 (
449 lockingactid,
450 asgrec.assignment_id,
451 pactid,
452 chunk,
453 null
454 );
455
456 end if;
457 end loop;
458
459 hr_utility.set_location('Leaving '||l_proc, 999);
460 end action_creation;
461
462 -----------------------------------------------------------------------
463 --procedure create_act_inf
464 -----------------------------------------------------------------------
465 procedure create_act_inf
466 ( p_action_context_id number
467 , p_action_context_type varchar2
468 , p_effective_date date
469 , p_action_information_category varchar2
470 , p_action_information1 varchar2 default null
471 , p_action_information2 varchar2 default null
472 , p_action_information3 varchar2 default null
473 , p_action_information4 varchar2 default null
474 , p_action_information5 varchar2 default null
475 , p_action_information6 varchar2 default null
476 , p_action_information7 varchar2 default null
477 , p_action_information8 varchar2 default null
478 , p_action_information9 varchar2 default null
479 , p_action_information10 varchar2 default null
480 , p_action_information11 varchar2 default null
481 , p_action_information12 varchar2 default null
482 , p_action_information13 varchar2 default null
483 , p_action_information14 varchar2 default null
484 , p_action_information15 varchar2 default null
485 , p_action_information16 varchar2 default null
486 , p_action_information17 varchar2 default null
487 , p_action_information18 varchar2 default null
488 , p_action_information29 varchar2 default null
489 , p_action_information30 varchar2 default null
490 , p_assignment_id number default null
491 )
492 is
493 l_proc varchar2(200):= g_package||'create_act_inf';
494 l_ovn number;
495 l_action_id number;
496 begin
497 hr_utility.set_location('Entering: '||l_proc,1);
498 pay_action_information_api.create_action_information
499 (
500 p_action_information_id => l_action_id,
501 p_action_context_id => p_action_context_id,
502 p_action_context_type => p_action_context_type,
503 p_object_version_number => l_ovn,
504 p_effective_date => p_effective_date,
505 p_action_information_category => p_action_information_category,
506 p_action_information1 => p_action_information1,
507 p_action_information2 => p_action_information2,
508 p_action_information3 => p_action_information3,
509 p_action_information4 => p_action_information4,
510 p_action_information5 => p_action_information5,
511 p_action_information6 => p_action_information6,
512 p_action_information7 => p_action_information7,
513 p_action_information8 => p_action_information8,
514 p_action_information9 => p_action_information9,
515 p_action_information10 => p_action_information10,
516 p_action_information11 => p_action_information11,
517 p_action_information12 => p_action_information12,
518 p_action_information13 => p_action_information13,
519 p_action_information14 => p_action_information14,
520 p_action_information15 => p_action_information15,
521 p_action_information16 => p_action_information16,
522 p_action_information17 => p_action_information17,
523 p_action_information18 => p_action_information18,
524 p_action_information29 => p_action_information29,
525 p_action_information30 => p_action_information30,
526 p_assignment_id => p_assignment_id
527 );
528
529 hr_utility.set_location('Leaving: '||l_proc,1);
530
531 end create_act_inf;
532
533
534
535
536 -----------------------------------------------------------------------
537 --Function retrieve_asg_type
538 --D for Directors
539 --N for Normal Employees
540 -----------------------------------------------------------------------
541 function retrieve_asg_type
542 ( p_assignment_id number
543 , p_nature varchar2
544 , p_forgn_nat varchar2
545 , p_exclude_coida varchar2 -- bug 12534150
546 )
547 return varchar2
548 IS
549 CURSOR csr_result_value
550 IS
551 SELECT
552 prrv.result_value
553 FROM
554 pay_element_types_f pet
555 , pay_input_values_f piv
556 , pay_run_results prr
557 , pay_run_result_values prrv
558 WHERE
559 pet.element_name = 'ZA_Tax'
560 AND pet.legislation_code = 'ZA'
561 AND pet.element_type_id = piv.element_type_id
562 AND piv.name = 'Tax Status'
563 AND piv.input_value_id = prrv.input_value_id
564 AND prr.element_type_id = pet.element_type_id
565 AND prr.run_result_id = prrv.run_result_id
566 AND prr.assignment_action_id =
567 (
568 SELECT
569 MAX(paa2.assignment_action_id)
570 FROM
571 pay_run_results prr2
572 , pay_assignment_actions paa2
573 , pay_payroll_actions ppa2
574 WHERE
575 prr2.element_type_id = pet.element_type_id
576 AND prr2.run_result_id = prr2.run_result_id
577 AND prr2.assignment_action_id = paa2.assignment_action_id
578 AND paa2.assignment_id = p_assignment_id
579 AND paa2.payroll_action_id = ppa2.payroll_action_id
580 AND ppa2.action_type IN ('R', 'Q', 'I', 'B', 'V')
581 AND ppa2.effective_date <= g_end_period_date
582 AND paa2.source_action_id IS not null
583 );
584 l_value varchar2(2);
585 l_proc varchar2(200) :=g_package||'retrieve_asg_type';
586 begin
587
588 hr_utility.set_location('Entering '||l_proc,10);
589 if p_forgn_nat = 'Y' then
590 open csr_result_value;
591 fetch csr_result_value into l_value;
592 close csr_result_value;
593 hr_utility.set_location('l_value: '||l_value,20);
594 end if;
595
596 if p_exclude_coida <> 'Y' then -- 12534150
597 if (p_nature in ('03','04','05','06','07', '08')
598 OR (p_forgn_nat = 'Y' AND l_value IN ('M','N','P','Q'))) then
599 --Director
600 return 'D';
601 else
602 return 'N';
603 end if;
604 else
605 return null;
606 end if ; -- end 12534150 to check if excluded from coida report
607
608 hr_utility.set_location('Leaving '||l_proc,10);
609
610 end retrieve_asg_type;
611
612 -------------------------------------------------------------------------
613 --- This function returns defined_balance_id for a balance and dimenesion
614 -------------------------------------------------------------------------
615 function get_def_bal_id (p_bal_name varchar2,
616 p_dim_name varchar2) return number is
617 cursor c_get_def_bal_id is
618 select pdb.defined_balance_id
619 from pay_balance_dimensions pbd
620 , pay_defined_balances pdb
621 , pay_balance_types pbt
622 where pbd.dimension_name = p_dim_name
623 and pbd.legislation_code = 'ZA'
624 and pdb.balance_type_id = pbt.balance_type_id
625 and pbt.balance_name = p_bal_name
626 and pbt.legislation_code = 'ZA'
627 and pdb.balance_dimension_id = pbd.balance_dimension_id;
628
629 l_def_bal_id number;
630 l_proc varchar2(200):=g_package||'get_def_bal_id';
631 begin
632 hr_utility.set_location('Entering '||l_proc,10);
633 open c_get_def_bal_id;
634 fetch c_get_def_bal_id into l_def_bal_id ;
635 close c_get_def_bal_id ;
636
637 return l_def_bal_id;
638 hr_utility.set_location('Leaving '||l_proc,90);
639
640 end get_def_bal_id;
641
642 -----------------------------------------------------------------------
643 --procedure process_balance
644 -----------------------------------------------------------------------
645 procedure process_balance
646 ( p_assact_id number
647 , p_asg_id number
648 , p_date date
649 , p_asg_type varchar2
650 , p_days_worked number
651 , p_effective_date date
652 , p_person_id number
653 , p_payroll_id number
654 )
655 is
656 l_tot_coid_def_id number;
657 l_meal_vouc_rfi_def_id number;
658 l_meal_vouc_nrfi_def_id number;
659 l_free_acc_rfi_def_id number;
660 l_free_acc_nrfi_def_id number;
661 l_tot_coid_income number;
662 l_meal_vouc_rfi number;
663 l_meal_vouc_nrfi number;
664 l_free_acc_rfi number;
665 l_free_acc_nrfi number;
666 l_cash_component number;
667 l_norm_income number;
668 l_dir_income number;
669 l_month varchar2(30);
670 l_days_worked number;
671 l_actual_earnings1 number;
672 l_actual_earnings2 number;
673 l_total_return number;
674 l_proc varchar2(200):=g_package||'process_balance';
675 l_run_action_seq number;
676 l_run_assact_id number;
677 l_start_py_adv_dt date;
678 l_pay_adv_start_date date;
679 l_person_type per_person_types.system_person_type%type;
680
681 -- Bug # 12329847 and 12334564
682
683 l_start_date date;
684 l_end_date date;
685 l_payroll_exists number;
686
687 -- Bug # 12329847 and 12334564
688
689 l_per_system_status varchar2(50); -- Bug#11938675
690
691 begin
692 hr_utility.set_location('Entering '||l_proc,10);
693 l_days_worked := p_days_worked;
694 l_tot_coid_def_id := get_def_bal_id('Total COIDAable Income','_ASG_TAX_MTD');
695 l_meal_vouc_rfi_def_id := get_def_bal_id('Meals Refreshments and Vouchers RFI' ,'_ASG_TAX_MTD');
696 l_meal_vouc_nrfi_def_id := get_def_bal_id('Meals Refreshments and Vouchers NRFI','_ASG_TAX_MTD');
697 l_free_acc_rfi_def_id := get_def_bal_id('Free or Cheap Accommodation RFI' ,'_ASG_TAX_MTD');
698 l_free_acc_nrfi_def_id := get_def_bal_id('Free or Cheap Accommodation NRFI','_ASG_TAX_MTD');
699 --Have not retrieved the value of COIDAable Normal Income as this is not used specifically anywhere
700 --Its value will add up to balance Total COIDAable Income
701 hr_utility.set_location('get_def_bal_id ',10);
702 l_start_py_adv_dt := to_date('01/'||to_char(p_date,'MM')||'/'||to_char(p_date,'YYYY'),'DD/MM/YYYY');
703 hr_utility.set_location('l_start_py_adv_dt ' || l_start_py_adv_dt,20);
704 l_run_assact_id :=0;
705 hr_utility.set_location('l_run_assact_id ' || l_run_assact_id,20);
706 select max(paa.action_sequence)
707 into l_run_action_seq
708 from pay_assignment_actions paa,
709 pay_payroll_actions ppa,
710 per_time_periods ptp
711 where paa.assignment_id = p_asg_id
712 and paa.action_status IN ('C','S') --10376999
713 and paa.payroll_action_id = ppa.payroll_action_id
714 and ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
715 and ppa.action_status = 'C'
716 and ppa.time_period_id = ptp.time_period_id
717 -- and ptp.prd_information1 = l_tax_year
718 and ptp.pay_advice_date <= p_date
719 and ptp.pay_advice_date >= l_start_py_adv_dt
720 and ptp.payroll_id = p_payroll_id; -- Bug # 12329847 and 12334564
721
722 hr_utility.set_location('l_run_action_seq ' || l_run_action_seq,20);
723
724 if l_run_action_seq is not null then
725
726 hr_utility.set_location('l_run_action_seq is not null ' ,30);
727
728 select assignment_action_id
729 into l_run_assact_id
730 from pay_assignment_actions
731 where assignment_id = p_asg_id
732 and action_sequence = l_run_action_seq;
733
734 hr_utility.set_location('l_run_assact_id ' || l_run_assact_id,30);
735 -- Bug # 12329847 and 12334564
736
737 else
738 /* get the period for which we should check the assigment has this payroll attached */
739
740 select min(ptp.start_date),
741 max(ptp.end_date)
742 into l_start_date,
743 l_end_date
744 from per_time_periods ptp
745 where ptp.payroll_id = P_PAYROLL_ID
746 and ptp.pay_advice_date <= P_DATE
747 and ptp.pay_advice_date >= L_START_PY_ADV_DT;
748
749 /* If the l_start_date or l_end_date is null invalid data return without doing anything */
750
751 hr_utility.set_location('l_start_date :'||l_start_date,15);
752 hr_utility.set_location('l_end_date :'||l_end_date,15);
753
754 if l_start_date is null or l_end_date is null then
755
756 return ;
757
758 else
759
760 /* check if at least there exists a record with the given payroll for the assignment */
761
762 l_payroll_exists :=0;
763
764 select count(1)
765 into l_payroll_exists
766 from per_all_assignments_f asg
767 where asg.assignment_id = p_asg_id
768 and asg.effective_start_date <= l_end_date
769 and asg.effective_end_date >= l_start_date
770 and asg.payroll_id = p_payroll_id; -- Bug#11938675
771
772 hr_utility.set_location('l_payroll_exists :'||l_payroll_exists,15);
773 if l_payroll_exists = 0 then
774
775 /* person does not have the provided payroll attached during the period */
776
777 return;
778
779 end if; -- l_payroll_exists
780 end if; -- l_start_date is null or l_end_date is null
781 end if; -- l_run_action_seq is not null
782
783 -- Bug # 12329847 and 12334564
784
785 -- Bug#11938675
786
787 begin
788 hr_utility.set_location('p_date :'||p_date,15);
789 hr_utility.set_location('L_START_PY_ADV_DT :'||L_START_PY_ADV_DT,15);
790
791 select max(past.per_system_status)
792 into l_per_system_status
793 from per_assignment_status_types past,
794 per_all_assignments_f asg
795 where past.assignment_status_type_id = asg.assignment_status_type_id
796 and asg.assignment_id = p_asg_id
797 and past.per_system_status = 'ACTIVE_ASSIGN'
798 and asg.effective_start_date <= p_date
799 and asg.effective_end_date >= L_START_PY_ADV_DT;
800
801 hr_utility.set_location('l_per_system_status from query :'||l_per_system_status,15);
802
803 if l_per_system_status is null then
804
805 select past.per_system_status
806 into l_per_system_status
807 from per_assignment_status_types past,
808 per_all_assignments_f asg
809 where past.assignment_status_type_id = asg.assignment_status_type_id
810 and asg.assignment_id = p_asg_id
811 and p_date between asg.effective_start_date and asg.effective_end_date;
812
813 hr_utility.set_location('l_per_system_status from if block :'||l_per_system_status,15);
814 end if;
815 exception
816 when others then
817 raise;
818 end;
819 hr_utility.set_location('l_per_system_status :'||l_per_system_status,15);
820 -- Bug#11938675
821
822
823 begin
824
825 /* check if the employee record exists. as say employee started in jun-2010. For the mar, Apr, and May 2010 it should not archive the balances */
826
827 /* for Ex employee the data should only be archived if it has any balances > 0 in the period */
828
829 Select ppt.system_person_type
830 into l_person_type
831 from
832 per_person_type_usages_f pptu
833 , per_person_types ppt
834 Where p_date BETWEEN pptu.effective_start_date and pptu.effective_end_date
835 AND pptu.person_id = p_person_id
836 AND pptu.person_type_id = ppt.person_type_id
837 AND ppt.system_person_type in ('EMP','EX_EMP');
838
839 hr_utility.set_location('l_person_type '|| l_person_type || ' :'||l_proc,20);
840
841 if nvl(l_run_assact_id ,0) > 0 then
842 -- l_tot_coid_income := pay_balance_pkg.get_value(l_tot_coid_def_id , p_asg_id , p_date);
843 l_tot_coid_income := pay_balance_pkg.get_value(p_defined_balance_id=>l_tot_coid_def_id , p_assignment_action_id => l_run_assact_id);
844 l_meal_vouc_rfi := pay_balance_pkg.get_value(p_defined_balance_id=>l_meal_vouc_rfi_def_id , p_assignment_action_id => l_run_assact_id);
845 l_meal_vouc_nrfi := pay_balance_pkg.get_value(p_defined_balance_id=>l_meal_vouc_nrfi_def_id , p_assignment_action_id => l_run_assact_id);
846 l_free_acc_rfi := pay_balance_pkg.get_value(p_defined_balance_id=>l_free_acc_rfi_def_id , p_assignment_action_id => l_run_assact_id);
847 l_free_acc_nrfi := pay_balance_pkg.get_value(p_defined_balance_id=>l_free_acc_nrfi_def_id , p_assignment_action_id => l_run_assact_id);
848
849 l_cash_component := l_meal_vouc_rfi + l_meal_vouc_nrfi + l_free_acc_rfi + l_free_acc_nrfi;
850 hr_utility.set_location('l_cash_component '|| l_cash_component || ' :',20);
851 /* Check if the p_days_worked and l_run_assact_id */
852 /* To cater for the terminated employee to be reported in the period where they have the earnings */
853 hr_utility.set_location('l_days_worked '|| l_days_worked || ' :',20);
854 if l_days_worked = 0 then
855 begin
856 hr_utility.set_location('getting max(PAY_ADVICE_DATE)+1 ',20);
857 select max(PAY_ADVICE_DATE)+1
858 into l_pay_adv_start_date
859 from per_time_periods
860 where payroll_id=p_payroll_id
861 and PAY_ADVICE_DATE < l_start_py_adv_dt;
862
863 hr_utility.set_location('l_pay_adv_start_date '|| l_pay_adv_start_date || ' :',30);
864
865 l_pay_adv_start_date:= nvl(l_pay_adv_start_date,l_start_py_adv_dt); -- Bug 12572065
866
867 exception
868 when no_data_found then
869 --No payroll calendar exist before this date. Hence default the start date to first of this month
870 -- l_pay_adv_start_date := trunc(rec_pay_dates.pay_advice_date,'MM');
871 hr_utility.set_location('no_data_found ',70);
872 hr_utility.set_location('l_start_py_adv_dt '|| l_start_py_adv_dt || ' :',70);
873 l_pay_adv_start_date := l_start_py_adv_dt;
874
875 end;
876 hr_utility.set_location('Before py_za_coida_pkg.get_working_days',75);
877 l_days_worked := py_za_coida_pkg.get_working_days
878 (
879 p_period_start => l_pay_adv_start_date
880 ,p_period_end => p_date
881 );
882 hr_utility.set_location('After py_za_coida_pkg.get_working_days',75);
883 end if;
884
885
886 /* End for terminated employee check*/
887
888
889 else
890 l_tot_coid_income := 0;
891 l_meal_vouc_rfi := 0;
892 l_meal_vouc_nrfi := 0;
893 l_free_acc_rfi := 0;
894 l_free_acc_nrfi :=0;
895
896 l_cash_component := 0;
897
898 end if;
899
900 --Normal Employee
901 if p_asg_type = 'N' then
902 l_norm_income := l_tot_coid_income - l_cash_component;
903 else
904 l_dir_income := l_tot_coid_income - l_cash_component;
905 end if;
906
907 l_total_return := l_cash_component + nvl(l_norm_income,0) + nvl(l_dir_income,0);
908
909 --Month for which the balance value is retrieved
910 l_month :=to_char(p_date,'MON'); --modified
911
912 --Calculate the actual earnings
913 hr_utility.set_location('Before get_act_earnings',75);
914 get_act_earnings
915 (
916 p_total_return => l_total_return
917 , p_days_worked => l_days_worked
918 , p_date => p_date
919 , p_act_earn1 => l_actual_earnings1
920 , p_act_earn2 => l_actual_earnings2
921 );
922 hr_utility.set_location('After get_act_earnings',75);
923 --'99999999990D99'
924
925 hr_utility.set_location('Before populating ZA_COID_EMP_INCOME_INFO',85);
926 hr_utility.set_location('l_month :'||l_month,85);
927 hr_utility.set_location('l_norm_income :'||l_norm_income,85);
928 hr_utility.set_location('l_dir_income :'||l_dir_income,85);
929 hr_utility.set_location('l_cash_component :'||l_cash_component,85);
930 hr_utility.set_location('l_total_return :'||l_total_return,85);
931 hr_utility.set_location('l_actual_earnings1 :'||l_actual_earnings1,85);
932 hr_utility.set_location('l_actual_earnings2 :'||l_actual_earnings2,85);
933 hr_utility.set_location('p_days_worked :'||p_days_worked,85);
934 hr_utility.set_location('l_days_worked :'||l_days_worked,85);
935 hr_utility.set_location('p_asg_type :'||p_asg_type,85);
936 hr_utility.set_location('p_person_id :'||p_person_id,85);
937 --Archive ZA_COID_EMP_INCOME_INFO
938
939 if ((l_person_type = 'EMP')
940 OR
941 (l_person_type = 'EX_EMP' and l_total_return > 0)) then
942 hr_utility.set_location('Inside if to archive ZA_COID_EMP_INCOME_INFO '||l_proc,85);
943
944 -- Bug # 12329847 and 12334564
945 /* condition to exclude the directors which have no coidable earnings */
946
947 if p_asg_type = 'D' and l_total_return =0 then
948 return;
949
950 else
951
952 -- Bug # 12329847 and 12334564
953
954 -- Bug#11938675
955 if ((l_per_system_status = 'ACTIVE_ASSIGN')
956 or
957 (l_per_system_status <>'ACTIVE_ASSIGN' and l_total_return > 0)) then
958
959 hr_utility.set_location('Inside if to archive ZA_COID_EMP_INCOME_INFO after assigment check'||l_proc,86);
960
961 -- Bug#11938675
962
963
964 create_act_inf
965 (
966 p_action_context_id => p_assact_id
967 , p_action_context_type => 'AAP'
968 , p_effective_date => p_effective_date
969 , p_action_information_category => 'ZA_COID_EMP_INCOME_INFO'
970 , p_action_information1 => l_month
971 , p_action_information2 => l_norm_income
972 , p_action_information3 => l_dir_income
973 , p_action_information4 => l_cash_component
974 , p_action_information5 => l_total_return
975 , p_action_information6 => l_actual_earnings1
976 , p_action_information7 => l_actual_earnings2
977 , p_action_information8 => p_days_worked
978 , p_action_information11 => l_person_type --Bug 14272272
979 , p_action_information9 => p_asg_type
980 , p_action_information10 => fnd_date.date_to_canonical(p_date)
981 , p_action_information30 => p_person_id
982 , p_assignment_id => p_asg_id
983 );
984 else -- Bug#11938675
985 hr_utility.set_location('Outside failing assigment check'||l_proc,86);
986 return; -- Bug#11938675
987 end if ; -- Bug#11938675
988 end if; -- Bug # 12329847 and 12334564
989
990 end if;
991 hr_utility.set_location('Leaving '||l_proc,90);
992
993 exception
994 when no_data_found then
995 --Assignment is not active in this period
996 --Hence dont archive any row of the assignment for the particular month
997 hr_utility.set_location('Assignment not active in the period',100);
998 return;
999 end;
1000
1001 end process_balance;
1002
1003 -----------------------------------------------------------------------
1004 --procedure archive_code
1005 -----------------------------------------------------------------------
1006 procedure archive_code
1007 (
1008 p_assactid in number,
1009 p_effective_date in date
1010 )
1011 is
1012
1013 cursor csr_pay_dates(p_payroll_id number)
1014 is
1015 select distinct pay_advice_date
1016 from per_time_periods
1017 where payroll_id=p_payroll_id
1018 and start_date >=g_start_period_date
1019 and end_date <= g_end_period_date
1020 order by pay_advice_date;
1021
1022 cursor csr_person_details
1023 is
1024 select per.last_name||' '||substr(per.first_name,1,1)||'.'||substr(nvl(per.middle_names,' '),1,1) last_name
1025 , per.person_id
1026 , per.employee_number emp_no
1027 , decode(ppt.system_person_type
1028 ,'EMP','Emp'
1029 ,'EX_EMP','Ex-Emp') type
1030 , asg.assignment_number asg_No
1031 , asg.assignment_id ass_id
1032 , asg.effective_start_date asg_start_date
1033 , per.effective_start_date hire_date
1034 , asg.effective_end_date asg_end_date
1035 , per.effective_end_date term_date
1036 , nvl(paei.aei_information4,'01') nature
1037 , nvl(paei.aei_information15,'N') foreign_national
1038 , nvl(paei.aei_information16,'N') exclude_coida -- 12534150
1039 from per_all_people_f per
1040 , per_all_assignments_f asg
1041 , pay_assignment_actions paa
1042 , per_assignment_extra_info paei
1043 , per_person_type_usages_f pptu
1044 , per_person_types ppt
1045 where paa.assignment_action_id = p_assactid
1046 and asg.assignment_id = paa.assignment_id
1047 and per.person_id = asg.person_id
1048 and paei.assignment_id = asg.assignment_id
1049 AND pptu.person_id = per.person_id
1050 AND pptu.person_type_id = ppt.person_type_id
1051 AND ppt.system_person_type in ('EMP','EX_EMP')
1052 AND paei.information_type = 'ZA_SPECIFIC_INFO'
1053 and p_effective_date between per.effective_start_date and per.effective_end_date
1054 and p_effective_date between pptu.effective_start_date and pptu.effective_end_date
1055 AND asg.effective_end_date =
1056 ( SELECT
1057 MAX(asgm.effective_end_date)
1058 FROM
1059 per_assignments_f asgm
1060 WHERE
1061 asgm.assignment_id = asg.assignment_id
1062 AND g_start_period_date <= asgm.effective_end_date
1063 AND g_end_period_date >= asgm.effective_start_date
1064 ) ;
1065
1066 l_proc varchar2(200):=g_package||'archive_code';
1067 rec_person_details csr_person_details%rowtype;
1068 l_payroll_id number;
1069 l_end_prd_id number;
1070 l_strt_prd_id number;
1071 l_asg_type varchar2(1);
1072 l_days_worked number;
1073 l_pay_adv_start_date date;
1074 leg_param varchar2(2000);
1075 pactid number;
1076 begin
1077 -- hr_utility.trace_on(null,'ZACOIDA');
1078 hr_utility.set_location('Entering '||l_proc, 10);
1079 fnd_file.put_line(fnd_file.log,'inside ' ||l_proc);
1080 hr_utility.set_location('p_assactid: '||p_assactid,10);
1081
1082 -- Retrieve legislative parameters from the archiver payroll action
1083 select ppa.legislative_parameters, ppa.payroll_action_id
1084 into leg_param , pactid
1085 from pay_payroll_actions ppa,
1086 pay_assignment_actions paa
1087 where paa.assignment_action_id = p_assactid
1088 and ppa.payroll_action_id = paa.payroll_action_id;
1089
1090
1091 l_payroll_id := get_parameter('PAYROLL_ID', leg_param);
1092 l_end_prd_id := get_parameter('END_PERIOD_ID', leg_param);
1093 l_strt_prd_id := get_parameter('STRT_PERIOD_ID', leg_param);
1094
1095 if nvl(g_coida_limit1,0) = 0 then
1096 set_globals(pactid);
1097 end if;
1098
1099 g_arch_effective_date :=p_effective_date;
1100 hr_utility.set_location('g_arch_effective_date :'||to_char(g_arch_effective_date,'dd-mm-yyyy'),15);
1101 hr_utility.set_location('l_payroll_id :'||l_payroll_id,15);
1102 hr_utility.set_location('l_end_prd_id :'||l_end_prd_id,15);
1103 hr_utility.set_location('l_strt_prd_id :'||l_strt_prd_id,15);
1104 hr_utility.set_location('g_coida_limit1 :'||g_coida_limit1,15);
1105 hr_utility.set_location('g_coida_limit2 :'||g_coida_limit2,15);
1106 hr_utility.set_location('g_coida_start1 :'||to_char(g_coida_start1,'dd-mm-yyyy'),15);
1107 hr_utility.set_location('g_coida_end1 :'||to_char(g_coida_end1,'dd-mm-yyyy'),15);
1108 hr_utility.set_location('g_coida_start2 :'||to_char(g_coida_start2,'dd-mm-yyyy'),15);
1109 hr_utility.set_location('g_coida_end2 :'||to_char(g_coida_end2,'dd-mm-yyyy'),15);
1110 hr_utility.set_location('g_start_period_date :'||to_char(g_start_period_date,'dd-mm-yyyy'),15);
1111 hr_utility.set_location('g_end_period_date :'||to_char(g_end_period_date,'dd-mm-yyyy'),15);
1112 hr_utility.set_location('g_num_of_days :'||g_num_of_days,15);
1113
1114
1115
1116 --Retrieve employee details
1117 open csr_person_details;
1118 fetch csr_person_details into rec_person_details;
1119 close csr_person_details;
1120
1121 l_asg_type :=retrieve_asg_type(rec_person_details.ass_id, rec_person_details.nature, rec_person_details.foreign_national, rec_person_details.exclude_coida); --12534150
1122 hr_utility.set_location('l_asg_type :'||l_asg_type,15);
1123
1124 --Archive ZA_COID_EMP_INFO
1125 create_act_inf
1126 (
1127 p_action_context_id => p_assactid
1128 , p_action_context_type => 'AAP'
1129 , p_effective_date => p_effective_date
1130 , p_action_information_category => 'ZA_COID_EMP_INFO'
1131 , p_action_information1 => rec_person_details.last_name
1132 , p_action_information2 => rec_person_details.emp_no
1133 , p_action_information3 => rec_person_details.type
1134 , p_action_information4 => fnd_date.date_to_canonical(rec_person_details.hire_date)
1135 , p_action_information5 => fnd_date.date_to_canonical(rec_person_details.term_date)
1136 , p_action_information6 => fnd_date.date_to_canonical(rec_person_details.asg_start_date)
1137 , p_action_information7 => fnd_date.date_to_canonical(rec_person_details.asg_end_date)
1138 , p_action_information9 => l_asg_type
1139 , p_action_information30 => rec_person_details.person_id
1140 , p_assignment_id => rec_person_details.ass_id
1141 );
1142 hr_utility.set_location('Populated ZA_COID_EMP_INFO',15);
1143
1144 --Now retrieve the monthly balances
1145 for rec_pay_dates in csr_pay_dates(l_payroll_id)
1146 loop
1147 begin
1148 select max(PAY_ADVICE_DATE)+1
1149 into l_pay_adv_start_date
1150 from per_time_periods
1151 where payroll_id=l_payroll_id
1152 and PAY_ADVICE_DATE < rec_pay_dates.pay_advice_date;
1153 hr_utility.set_location('l_pay_adv_start_date:'||to_char(l_pay_adv_start_date,'dd-mm-yyyy'),20);
1154 l_pay_adv_start_date := nvl(l_pay_adv_start_date,trunc(rec_pay_dates.pay_advice_date,'MM')); -- Bug 12572065
1155 hr_utility.set_location('l_pay_adv_start_date:'||to_char(l_pay_adv_start_date,'dd-mm-yyyy'),25);
1156
1157 exception
1158 when no_data_found then
1159 --No payroll calendar exist before this date. Hence default the start date to first of this month
1160 l_pay_adv_start_date := trunc(rec_pay_dates.pay_advice_date,'MM');
1161 end;
1162
1163 l_days_worked := py_za_coida_pkg.get_emp_days_worked
1164 (
1165 p_start_date => l_pay_adv_start_date,
1166 p_end_date => rec_pay_dates.pay_advice_date,
1167 p_payroll_id => l_payroll_id,
1168 p_person_id => rec_person_details.person_id
1169 );
1170
1171 hr_utility.set_location('l_pay_adv_start_date:'||to_char(l_pay_adv_start_date,'dd-mm-yyyy'),30);
1172 hr_utility.set_location('l_days_worked :'||l_days_worked,30);
1173 process_balance(p_assactid, rec_person_details.ass_id, rec_pay_dates.pay_advice_date, l_asg_type, l_days_worked, p_effective_date, rec_person_details.person_id,l_payroll_id);
1174
1175
1176 end loop;
1177 hr_utility.set_location('Leaving :'||l_proc,999);
1178 end archive_code ;
1179
1180 -----------------------------------------------------------------------
1181 --procedure archdinit
1182 -----------------------------------------------------------------------
1183
1184 procedure archdinit(pactid in number) as
1185
1186 --Cursor for fetching up person who have more than one assignment of same assignment type
1187 cursor csr_dup_person is
1188 select distinct
1189 pai.action_information30 person_id
1190 , pai.action_information9 asg_type
1191 from pay_payroll_actions ppa
1192 , pay_assignment_actions paa
1193 , pay_action_information pai
1194 , pay_assignment_actions paa2
1195 , pay_action_information pai2
1196 where ppa.payroll_action_id = pactid
1197 and ppa.action_status = 'C'
1198 and paa.payroll_action_id = ppa.payroll_action_id
1199 and paa.action_status = 'C'
1200 and pai.action_context_id = paa.assignment_action_id
1201 and pai.action_context_type = 'AAP'
1202 and pai.action_information_category = 'ZA_COID_EMP_INFO'
1203 and paa2.payroll_action_id = ppa.payroll_action_id
1204 and paa2.assignment_action_id = pai2.action_context_id
1205 and pai2.action_information30 = pai.action_information30 --Same person id
1206 and pai2.action_information9 = pai.action_information9 --Same assignment type
1207 and pai.action_information_id <> pai2.action_information_id
1208 and pai2.action_context_type = 'AAP'
1209 and pai2.action_information_category = 'ZA_COID_EMP_INFO';
1210
1211 --Retrieve the details of duplicate employee records
1212 cursor csr_dup_emp_action_info (l_person_id number, l_asg_type varchar2) is
1213 select pai.*
1214 from pay_action_information pai,
1215 pay_assignment_actions paa
1216 where pai.action_information30 = l_person_id
1217 and pai.action_information9 = l_asg_type
1218 and paa.payroll_action_id = pactid
1219 and paa.assignment_action_id = pai.action_context_id
1220 and pai.action_information_category='ZA_COID_EMP_INFO';
1221
1222 --Retrieve the details of duplicate employee income records
1223 cursor csr_dup_emp_inc_info (l_act_context number, l_month varchar2) is
1224 select pai.*
1225 from pay_action_information pai
1226 where pai.action_context_id = l_act_context
1227 and pai.action_information1 = nvl(l_month,pai.action_information1)
1228 and pai.action_information_category='ZA_COID_EMP_INCOME_INFO';
1229
1230
1231 --Retrieve the monthly cumulative details for a person
1232 -- fix for bug 14272272
1233 cursor csr_cumulative_details (l_person_id number, l_asg_type varchar2) is
1234 select nvl(sum(pai.action_information2),0) norm_income
1235 , nvl(sum(pai.action_information3),0) dir_income
1236 , nvl(sum(pai.action_information4),0) cash_comp
1237 , nvl(sum(pai.action_information5),0) tot_income
1238 , pai.action_information1 mon
1239 , fnd_date.canonical_to_date(pai.action_information10) pay_adv_date --revisit --need to populate this in archive code
1240 , pai.action_information8 num_days
1241 from pay_action_information pai
1242 , pay_assignment_actions paa
1243 , pay_action_information pai2
1244 where pai.action_information30 = l_person_id
1245 and pai.action_information9 = l_asg_type
1246 and paa.assignment_action_id = pai.action_context_id
1247 and paa.payroll_action_id = pactid
1248 and pai.action_context_id = pai2.action_context_id
1249 and pai.action_information_category ='ZA_COID_EMP_INCOME_INFO'
1250 and pai2.action_information_category = 'ZA_COID_EMP_INFO'
1251 and pai.action_information9 = pai2.action_information9
1252 and nvl(pai.action_information29,'I') <> 'E'
1253 and nvl(pai2.action_information29,'I') <> 'E'
1254 group by pai.action_information1
1255 , pai.action_information10
1256 , pai.action_information8;
1257 -- 14272272 ends
1258
1259 --Retrieve the monthly cumulative details for COIDA period
1260 cursor csr_month_cumulative_details is
1261 select count(pai.action_information_id) num_emp
1262 , nvl(sum(pai2.action_information2),0) norm_income
1263 , nvl(sum(pai2.action_information3),0) dir_income
1264 , nvl(sum(pai2.action_information4),0) cash_comp
1265 , nvl(sum(pai2.action_information5),0) tot_income
1266 , nvl(sum(pai2.action_information6),0) act_earn1
1267 , nvl(sum(pai2.action_information7),0) act_earn2
1268 , pai2.action_information1 mon
1269 , pai.action_information9 asg_type
1270 from pay_action_information pai --ZA_COID_EMP_INFO
1271 , pay_action_information pai2 --ZA_COID_EMP_INCOME_INFO
1272 , pay_assignment_actions paa
1273 where paa.payroll_action_id = pactid
1274 and paa.assignment_action_id = pai.action_context_id
1275 and pai.action_information_category = 'ZA_COID_EMP_INFO'
1276 and pai2.action_information_category = 'ZA_COID_EMP_INCOME_INFO'
1277 and pai.action_context_id = pai2.action_context_id
1278 and nvl(pai.action_information29,'I') <> 'E'
1279 and nvl(pai2.action_information29,'I') <> 'E'
1280 and pai.action_information9 = pai2.action_information9
1281 group by pai2.action_information1
1282 , pai.action_information9;
1283
1284 --Retrieve all months till period end date which donot have the ZA_COID_TOT_MONTH_INFO
1285 cursor csr_zero_totals (p_payroll_id number, p_strt_prd_id number, p_end_prd_id number, p_asg_type varchar2) is
1286 select to_char(PAY_ADVICE_DATE,'MON') mon -- Modified
1287 from per_time_periods
1288 where payroll_id = p_payroll_id
1289 and time_period_id >= p_strt_prd_id
1290 and time_period_id <= p_end_prd_id
1291 minus
1292 select action_information1 mon
1293 from pay_action_information
1294 where action_context_id = pactid
1295 and action_information_category = 'ZA_COID_TOT_MONTH_INFO'
1296 and action_information9 = p_asg_type;
1297
1298
1299 l_proc varchar2(200):=g_package||'archdinit';
1300 l_prev_act_id number :=0;
1301 l_act_id number;
1302 rec_emp_inc_info csr_dup_emp_inc_info%rowtype;
1303 l_prev_person_id number :=0;
1304 l_person_id number;
1305
1306 type cumulative_record is record
1307 ( norm_inc number
1308 , dir_inc number
1309 , cash_comp number
1310 , tot_inc number
1311 , act_earn1 number
1312 , act_earn2 number
1313 , pay_adv_dt date
1314 , num_days number
1315 );
1316
1317 type cumulative_tab is table of cumulative_record index by varchar2(30);
1318 cum_tab cumulative_tab;
1319 l_counter number:=0;
1320 l_payroll_id number;
1321 l_end_prd_id number;
1322 l_strt_prd_id number;
1323 leg_param varchar2(2000);
1324 l_month varchar2(30);
1325 l_tax_year varchar2(30);
1326
1327 begin
1328 hr_utility.set_location('Entering '||l_proc, 10);
1329 fnd_file.put_line(fnd_file.log,'Inside ' ||l_proc);
1330
1331 -- Retrieve legislative parameters from the archiver payroll action
1332 select legislative_parameters
1333 into leg_param
1334 from pay_payroll_actions
1335 where payroll_action_id = pactid;
1336
1337 --Bug 14272272
1338 if nvl(g_coida_limit1,0) = 0 then
1339 set_globals(pactid);
1340 end if;
1341
1342 l_payroll_id := get_parameter('PAYROLL_ID', leg_param);
1343 l_end_prd_id := get_parameter('END_PERIOD_ID', leg_param);
1344 l_strt_prd_id := get_parameter('STRT_PERIOD_ID', leg_param);
1345 l_tax_year := get_parameter('TAX_YEAR', leg_param); -- Added
1346
1347 hr_utility.set_location('l_payroll_id ' ||l_payroll_id,15);
1348 hr_utility.set_location('l_end_prd_id ' ||l_end_prd_id,15);
1349 hr_utility.set_location('l_strt_prd_id ' ||l_strt_prd_id,15);
1350 hr_utility.set_location('g_split_year ' ||g_split_year,15);
1351 hr_utility.set_location('l_tax_year ' ||l_tax_year,15); -- Added
1352 for rec_dup_person in csr_dup_person
1353 loop
1354 hr_utility.set_location('rec_dup_person.person_id ' ||rec_dup_person.person_id,15);
1355 hr_utility.set_location('rec_dup_person.asg_type ' ||rec_dup_person.asg_type,15);
1356 cum_tab.delete;
1357 for rec_cum_det in csr_cumulative_details (rec_dup_person.person_id, rec_dup_person.asg_type)
1358 loop
1359 hr_utility.set_location('Populating cum_tab table',20);
1360 hr_utility.set_location('rec_cum_det.mon: '||rec_cum_det.mon,20);
1361 cum_tab(rec_cum_det.mon).norm_inc := rec_cum_det.norm_income;
1362 cum_tab(rec_cum_det.mon).dir_inc := rec_cum_det.dir_income;
1363 cum_tab(rec_cum_det.mon).cash_comp := rec_cum_det.cash_comp;
1364 cum_tab(rec_cum_det.mon).tot_inc := rec_cum_det.tot_income;
1365 cum_tab(rec_cum_det.mon).pay_adv_dt:= rec_cum_det.pay_adv_date;
1366 cum_tab(rec_cum_det.mon).num_days := rec_cum_det.num_days;
1367 hr_utility.set_location('Populated cum_tab table for the month',20);
1368
1369 --Calculate actual earnings
1370 get_act_earnings
1371 (
1372 p_total_return => cum_tab(rec_cum_det.mon).tot_inc
1373 , p_days_worked => cum_tab(rec_cum_det.mon).num_days
1374 , p_date => cum_tab(rec_cum_det.mon).pay_adv_dt
1375 , p_act_earn1 => cum_tab(rec_cum_det.mon).act_earn1
1376 , p_act_earn2 => cum_tab(rec_cum_det.mon).act_earn2
1377 );
1378
1379 end loop; --csr_cumulative_details
1380 hr_utility.set_location('Populated Actual Earnings in cum_tab table',20);
1381
1382
1383 --Update the first record as cumilative record. Rest of the records will be marked as duplicate
1384 l_prev_person_id :=0;
1385 for rec_action_info in csr_dup_emp_action_info(rec_dup_person.person_id, rec_dup_person.asg_type)
1386 loop
1387 l_act_id := rec_action_info.action_context_id;
1388 if rec_dup_person.person_id <> l_prev_person_id then --First record mark as cumulative
1389 l_month :=cum_tab.first;
1390 for i in 1 .. cum_tab.count
1391 loop
1392 open csr_dup_emp_inc_info(l_act_id,l_month);
1393 fetch csr_dup_emp_inc_info into rec_emp_inc_info;
1394 if csr_dup_emp_inc_info%notfound then
1395 --create action info
1396 create_act_inf
1397 (
1398 p_action_context_id => l_act_id,
1399 p_action_context_type => 'AAP',
1400 p_effective_date => g_arch_effective_date,
1401 p_action_information_category => 'ZA_COID_EMP_INCOME_INFO',
1402 p_action_information1 => l_month,
1403 p_action_information2 => cum_tab(l_month).norm_inc,
1404 p_action_information3 => cum_tab(l_month).dir_inc,
1405 p_action_information4 => cum_tab(l_month).cash_comp,
1406 p_action_information5 => cum_tab(l_month).tot_inc,
1407 p_action_information6 => cum_tab(l_month).act_earn1,
1408 p_action_information7 => cum_tab(l_month).act_earn2,
1409 p_action_information8 => cum_tab(l_month).num_days,
1410 p_action_information9 => rec_dup_person.asg_type,
1411 p_action_information10 => fnd_date.date_to_canonical(cum_tab(l_month).pay_adv_dt),
1412 p_action_information30 => rec_dup_person.person_id
1413 );
1414
1415 else
1416 --Update action info
1417 pay_action_information_api.update_action_information(
1418 p_action_information_id => rec_emp_inc_info.action_information_id,
1419 p_object_version_number => rec_emp_inc_info.object_version_number,
1420 p_action_information1 => l_month,
1421 p_action_information2 => cum_tab(l_month).norm_inc,
1422 p_action_information3 => cum_tab(l_month).dir_inc,
1423 p_action_information4 => cum_tab(l_month).cash_comp,
1424 p_action_information5 => cum_tab(l_month).tot_inc,
1425 p_action_information6 => cum_tab(l_month).act_earn1,
1426 p_action_information7 => cum_tab(l_month).act_earn2
1427 );
1428 end if;
1429 close csr_dup_emp_inc_info;
1430
1431 l_month:=cum_tab.next(l_month);
1432 end loop;
1433 else --exclude other records
1434 --Update action info29 of 'ZA_COID_EMP_INFO' to 'E' i.e. Exclude
1435 pay_action_information_api.update_action_information(
1436 p_action_information_id => rec_action_info.action_information_id,
1437 p_object_version_number => rec_action_info.object_version_number,
1438 p_action_information29 => 'E'
1439 );
1440
1441 for rec_dup_inc_info in csr_dup_emp_inc_info(l_act_id,null)
1442 loop
1443 --Update action info29 of 'ZA_COID_EMP_INCOME_INFO' to 'E' i.e. Exclude
1444 pay_action_information_api.update_action_information(
1445 p_action_information_id => rec_dup_inc_info.action_information_id,
1446 p_object_version_number => rec_dup_inc_info.object_version_number,
1447 p_action_information29 => 'E'
1448 );
1449 end loop;
1450
1451 end if;
1452 l_prev_act_id := l_act_id;
1453 l_prev_person_id:=rec_dup_person.person_id;
1454 end loop;
1455 end loop;
1456
1457 hr_utility.set_location('Before populating ZA_COID_TOT_MONTH_INFO',50);
1458 --Populate 'ZA_COID_TOT_MONTH_INFO'
1459 for rec_month_cum_det in csr_month_cumulative_details
1460 loop
1461 hr_utility.set_location('rec_month_cum_det.mon:'||rec_month_cum_det.mon,50);
1462 create_act_inf
1463 (
1464 p_action_context_id => pactid,
1465 p_action_context_type => 'PA',
1466 p_effective_date => g_arch_effective_date,
1467 p_action_information_category => 'ZA_COID_TOT_MONTH_INFO',
1468 p_action_information1 => rec_month_cum_det.mon,
1469 p_action_information2 => rec_month_cum_det.norm_income,
1470 p_action_information3 => rec_month_cum_det.dir_income,
1471 p_action_information4 => rec_month_cum_det.cash_comp,
1472 p_action_information5 => rec_month_cum_det.tot_income,
1473 p_action_information6 => rec_month_cum_det.act_earn1,
1474 p_action_information7 => rec_month_cum_det.act_earn2,
1475 p_action_information8 => rec_month_cum_det.num_emp,
1476 p_action_information9 => rec_month_cum_det.asg_type,
1477 p_action_information10 => l_tax_year -- Added
1478 );
1479
1480 end loop;
1481 hr_utility.set_location('Populated ZA_COID_TOT_MONTH_INFO',50);
1482
1483 --Populate 'ZA_COID_TOT_MONTH_INFO' with zero value when no employees in that period
1484 for rec_zero_total in csr_zero_totals(l_payroll_id, l_strt_prd_id, l_end_prd_id, 'D')
1485 loop
1486 hr_utility.set_location('rec_zero_total.mon:'||rec_zero_total.mon,55);
1487 create_act_inf
1488 (
1489 p_action_context_id => pactid,
1490 p_action_context_type => 'PA',
1491 p_effective_date => g_arch_effective_date,
1492 p_action_information_category => 'ZA_COID_TOT_MONTH_INFO',
1493 p_action_information1 => rec_zero_total.mon,
1494 p_action_information2 => 0,
1495 p_action_information3 => 0,
1496 p_action_information4 => 0,
1497 p_action_information5 => 0,
1498 p_action_information6 => 0,
1499 p_action_information7 => 0,
1500 p_action_information8 => 0,
1501 p_action_information9 => 'D',
1502 p_action_information10 => l_tax_year -- Added
1503 );
1504 end loop;
1505 hr_utility.set_location('Populated ZA_COID_TOT_MONTH_INFO with zero figure for Directors',60);
1506
1507
1508 for rec_zero_total in csr_zero_totals(l_payroll_id, l_strt_prd_id, l_end_prd_id, 'N')
1509 loop
1510 hr_utility.set_location('rec_zero_total.mon:'||rec_zero_total.mon,65);
1511 create_act_inf
1512 (
1513 p_action_context_id => pactid,
1514 p_action_context_type => 'PA',
1515 p_effective_date => g_arch_effective_date,
1516 p_action_information_category => 'ZA_COID_TOT_MONTH_INFO',
1517 p_action_information1 => rec_zero_total.mon,
1518 p_action_information2 => 0,
1519 p_action_information3 => 0,
1520 p_action_information4 => 0,
1521 p_action_information5 => 0,
1522 p_action_information6 => 0,
1523 p_action_information7 => 0,
1524 p_action_information8 => 0,
1525 p_action_information9 => 'N',
1526 p_action_information10 => l_tax_year -- Added
1527 );
1528 end loop;
1529 hr_utility.set_location('Populated ZA_COID_TOT_MONTH_INFO with zero figure for Normal Emp',70);
1530
1531 end archdinit;
1532
1533 end PAY_ZA_COIDA_ARCHIVE;
1534