[Home] [Help]
PACKAGE BODY: APPS.PAY_ZA_UIF_ARCHIVE_PKG
Source
1 package body pay_za_uif_archive_pkg as
2 /* $Header: pyzauifa.pkb 120.2.12010000.2 2008/08/06 08:48:44 ubhat ship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 2001 Oracle Corporation Redwood Shores, California, USA|
6 | All rights reserved. |
7 +======================================================================+
8 SQL Script File name : pyzauifa.pkb
9 Description : This sql script seeds the Package Body that
10 creates the UIF Archive code
11
12 Change List:
13 ------------
14
15 Name Date Version Bug Text
16 -------------- ----------- ------- ------ -----------------------------
17 L.Kloppers 21-Apr-2002 115.0 2266156 Initial Version
18 L.Kloppers 02-May-2002 115.1 2266156 Modified Employee Cursor
19 L.Kloppers 06-May-2002 115.2 2266156 Replaced global variables with
20 local procedure variables
21 to allow for multi-threading
22 L.Kloppers 06-May-2002 115.3 2266156 Replaced Nature of Person
23 Lookup Meaning with Codes in
24 action_creation procedure
25 L.Kloppers 06-May-2002 115.4 2266156 Replaced global variables with
26 local procedure variables
27 L.Kloppers 08-May-2002 115.5 2266156 Modified to allow for multiple
28 archives per UIF Month
29 L.Kloppers 09-May-2002 115.6 2266156 Added range_cursor_mag for UIF File
30 J.N. Louw 13-Jun-2002 115.7 2411444 ID_FLEX_STRUCTURE_CODE reference added
31 J.N. Louw 17-Jun-2002 115.8 2420012 Id Flex select corrected
32 Nirupa S 10-Dec-2002 115.10 2686708 Added NOCOPY
33 Nageswara Rao 24-Jan-2003 115.11 2654703 Added new function
34 get_uif_total_remu_sub_uif
35 Nageswara Rao 13-Feb-2003 115.12 2798916 Changed query in Action Creation
36 procedure
37 Nageswara Rao 14-Feb-2003 115.13 Changes to query in Action Creation
38 procedure to select all Employees
39 in a payroll run
40 Nageswara Rao 10-Apr-2003 115.15 2874102 changes to obsolete reason_non_contrib
41 code '07'
42 2863938 when first_name is null, archive 'XXX'
43 Kaladhaur P 05-Oct-2004 115.16 3869426 Modified query in Action Creation to
44 include future terminated employees
45 to Electronic UIF File
46 A. Mahanty 23-DEC-2004 115.18 4072410 An extra condition was added for an Employee
47 having a non-contribution reason of 01 and with
48 a value in the UIF Employee Contribution balance.
49 No non-contribution reason must be written to the
50 UIF File for such cases.
51 A. Mahanty 14-FEB-2004 115.19 4134166 The Monthly UIFable Limit calculation was changed.
52 Two cursors csr_pay_periods_per_year and
53 csr_pay_periods_per_month were added.The cursor
54 csr_uif_limit was modified.
55 4140343 An additional condition was added to set to zero the
56 balance values for an employee who has not been processed
57 even once in a month.All eligible employees are included
58 in the UIF File, even if they are not processed.
59 Kaladhaur P 22-Apr-2005 115.20 4306265 Modified the cursor csr_employee_data in archive_data.
60 Modified the parameter value passed to csr_employee_data
61 inorder to fetch date effective data.
62 Kaladhaur P 15-Sep-2005 115.20 4612798 R12 Performance Bug Fix. Tuned the query in the cursor
63 csr_latest_asg_action.
64 A. Mahanty 19-Dec-2005 115.22 4768622 R12 Performance Bug Fix. Modified the query in the
65 procedure archive_data.
66 P.Arusia 16-Jul-2008 115.23 7255839 If reason for UIF non-contribution is 007, then report
67 it as 07
68 ========================================================================
69 */
70 sql_range varchar2(4000);
71 prev_asg_id number;
72
73 g_total_gross_tax_rem number;
74 g_total_uif_contribution number;
75
76 /* changes as per2654703 */
77 g_total_remu_sub_uif number;
78
79 g_total_no_emps number;
80
81 g_archive_pact number;
82 g_archive_effective_date date;
83 g_package constant varchar2(30) := 'pay_za_uif_archive_pkg.';
84 g_canonical_end_date date;
85 g_canonical_start_date date;
86 g_business_group_id number;
87
88 g_asg_set_id number;
89 g_person_id number;
90
91
92 /*--------------------------------------------------------------------------
93 Name : get_parameters
94 Purpose : This retrieves legislative parameters from the payroll action.
95 Arguments :
96 --------------------------------------------------------------------------*/
97 procedure get_parameters
98 (
99 p_payroll_action_id in number,
100 p_token_name in varchar2,
101 p_token_value out nocopy varchar2
102 ) is
103
104 cursor csr_parameter_info
105 (
106 p_pact_id number,
107 p_token char
108 ) is
109 select substr
110 (
111 legislative_parameters,
112 instr
113 (
114 legislative_parameters,
115 p_token
116 ) + (length(p_token) + 1),
117 instr
118 (
119 legislative_parameters,
120 ' ',
121 instr
122 (
123 legislative_parameters,
124 p_token
125 )
126 )
127 -
128 (
129 instr
130 (
131 legislative_parameters,
132 p_token
133 ) + length(p_token)
134 )
135 ),
136 business_group_id
137 from pay_payroll_actions
138 where payroll_action_id = p_pact_id;
139
140 l_business_group_id varchar2(20);
141 l_token_value varchar2(50);
142
143 l_proc varchar2(50) := g_package || 'get_parameters';
144
145 begin
146
147 hr_utility.set_location('Entering ' || l_proc, 10);
148
149 hr_utility.set_location('Step ' || l_proc, 20);
150 hr_utility.set_location('p_token_name = ' || p_token_name, 20);
151
152 open csr_parameter_info
153 (
154 p_payroll_action_id,
155 p_token_name
156 );
157 fetch csr_parameter_info into l_token_value, l_business_group_id;
158 close csr_parameter_info;
159
160 if p_token_name = 'BG_ID' then
161
162 p_token_value := l_business_group_id;
163
164 else
165
166 p_token_value := l_token_value;
167
168 end if;
169
170 hr_utility.set_location('l_token_value = ' || p_token_value, 20);
171 hr_utility.set_location('Leaving ' || l_proc, 30);
172 --
173 exception
174 when others then
175 p_token_value := null;
176 --
177 end get_parameters;
178
179
180 /*--------------------------------------------------------------------------
181 Name : get_balance_value
182 Purpose : This returns the Balance Value for a 'ZA' Balance
183 Arguments : Assignment Action, Balance Name and Balance Dimension
184 Notes :
185 --------------------------------------------------------------------------*/
186 function get_balance_value
187 (
188 p_assignment_id in per_all_assignments_f.assignment_id%type,
189 p_balance_name in pay_balance_types.balance_name%type,
190 p_dimension in pay_balance_dimensions.dimension_name%type,
191 p_effective_date in date
192 )
193 return number is
194
195 cursor csr_latest_asg_action is
196 select paa.assignment_action_id
197 from pay_assignment_actions paa
198 where paa.assignment_id = p_assignment_id
199 and paa.rowid =
200 (
201 select substr(
202 max(lpad(paa2.action_sequence, 15, 0) || paa2.rowid)
203 , -length(paa2.rowid)
204 )
205 from pay_payroll_actions ppa2
206 , pay_assignment_actions paa2
207 where paa2.assignment_id = p_assignment_id
208 and ppa2.payroll_action_id = paa2.payroll_action_id
209 and ppa2.action_type in ('R', 'Q', 'I', 'B', 'V')
210 and ppa2.effective_date >= (add_months(p_effective_date, -1)+1) /*g_canonical_start_date*/
211 and ppa2.effective_date <= p_effective_date /*g_canonical_end_date*/
212 group by length(paa2.rowid)
213 );
214
215 l_latest_asg_action pay_assignment_actions.assignment_action_id%type;
216 l_defined_bal_id pay_defined_balances.defined_balance_id%type;
217 l_balance_value number;
218
219
220 l_proc varchar2(50) := g_package || 'get_balance';
221
222 begin
223
224 hr_utility.set_location('Entering '|| l_proc,10);
225
226 open csr_latest_asg_action;
227 fetch csr_latest_asg_action into l_latest_asg_action;
228 close csr_latest_asg_action;
229
230 hr_utility.set_location(g_package||'get_balance',20);
231
232 -- Set the Payroll Action ID context to the last Payroll Action
233 -- that the assignment were processed in
234 pay_balance_pkg.set_context
235 (
236 'PAYROLL_ACTION_ID',
237 l_latest_asg_action
238 );
239
240 select def.defined_balance_id
241 into l_defined_bal_id
242 from pay_defined_balances def,
243 pay_balance_types pbt,
244 pay_balance_dimensions dim,
245 pay_balance_types_tl pbt_tl
246 where pbt_tl.balance_name = p_balance_name
247 and pbt_tl.language = 'US'
248 and pbt_tl.balance_type_id = pbt.balance_type_id
249 and pbt.legislation_code = 'ZA'
250 and dim.dimension_name = p_dimension
251 and dim.legislation_code = 'ZA'
252 and pbt.balance_type_id = def.balance_type_id
253 and dim.balance_dimension_id = def.balance_dimension_id
254 and def.legislation_code = 'ZA';
255
256
257 hr_utility.set_location('Step ' || l_proc, 30);
258 hr_utility.set_location('l_latest_asg_action = ' || l_latest_asg_action, 30);
259 hr_utility.set_location('l_balance = ' || p_balance_name, 30);
260 hr_utility.set_location('l_dimension = ' || p_dimension, 30);
261 hr_utility.set_location('l_defined_bal_id = ' || l_defined_bal_id, 30);
262
263 --Bug 4140343
264 --If an active or suspended assignment is not processed in a particular month then
265 --the balances values (dimension = _ASG_TAX_MTD ) are set to 0 and the employee is shown on the
266 --reported on the UIF File
267 if l_latest_asg_action is NOT NULL then
268
269 l_balance_value := pay_balance_pkg.get_value
270 (
271 p_defined_balance_id => l_defined_bal_id,
272 p_assignment_action_id => l_latest_asg_action
273 );
274
275 hr_utility.set_location('l_balance_value = ' || l_balance_value, 40);
276
277 else
278 l_balance_value := 0;
279
280 end if;
281
282 return l_balance_value;
283
284 exception
285 when others then
286 hr_utility.set_location(l_proc,50);
287 hr_utility.set_message(801,'Sql Err Code: '||to_char(sqlcode));
288 hr_utility.raise_error;
289
290 end get_balance_value;
291
292
293 /*--------------------------------------------------------------------------
294 Name : range_cursor
295 Purpose : This returns the select statement that is used to created the
296 range rows.
297 Arguments :
298 Notes : The range cursor determines which people should be processed.
299 The normal practice is to include everyone, and then limit
300 the list during the assignment action creation.
301 --------------------------------------------------------------------------*/
302 procedure range_cursor
303 (
304 pactid in number,
305 sqlstr out nocopy varchar2
306 ) is
307
308 -- Returns Creator Information for the specified UIF Month that has not been archived yet
309 cursor csr_creator_info is
310 select hoi.org_information1,
311 hoi.org_information2,
312 hoi.org_information3,
313 hoi.org_information4
314 from hr_organization_information hoi
315 , hr_all_organization_units org
316 where hoi.org_information_context = 'ZA_UIF_CREATOR_INFO'
317 and hoi.organization_id = org.organization_id
318 and org.organization_id = g_business_group_id;
319 /*Commented out the following to allow re-archiving*/
320 /*
321 and not exists
322 (
323 select null
324 from pay_action_information pai
325 , pay_payroll_actions ppa
326 where pai.action_context_type = 'PA'
327 and pai.action_information_category = 'ZA UIF CREATOR DETAILS'
328 and pai.action_information1 = g_business_group_id
329 and pai.action_information2 = to_char(g_canonical_end_date, 'YYYYMM')
330 and ppa.payroll_action_id = pai.action_context_id
331 )
332 */
333
334 cursor csr_archive_effective_date(pactid number) is
335 select effective_date
336 from pay_payroll_actions
337 where payroll_action_id = pactid;
338
339 l_payroll_id number;
340 l_start_date varchar2(30);
341 l_end_date varchar2(30);
342 l_bg_id number;
343 l_canonical_end_date date;
344 l_canonical_start_date date;
345
346 l_creator_uif_reference hr_organization_information.org_information1%type;
347 l_contact_person hr_organization_information.org_information2%type;
348 l_contact_number hr_organization_information.org_information3%type;
349 l_contact_email_address hr_organization_information.org_information4%type;
350
351 l_action_info_id number;
352 l_ovn number;
353
354 l_proc varchar2(50) := g_package || 'range_cursor';
355
356 begin
357
358 --hr_utility.trace_on(null, 'UIF');
359
360 hr_utility.set_location('Entering ' || l_proc, 10);
361 hr_utility.trace('Entering ' || l_proc);
362
363 g_archive_pact := pactid; -- Payroll Action of the Archiver
364
365 hr_utility.trace('g_archive_pact: ' || to_char(g_archive_pact));
366
367
368 -- Get the effective date of the payroll action
369 open csr_archive_effective_date(pactid); -- Payroll Action of the Archiver
370 fetch csr_archive_effective_date
371 into g_archive_effective_date;
372 close csr_archive_effective_date;
373
374 -- Retrieve the legislative parameters from the payroll action
375 get_parameters
376 (
377 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
378 p_token_name => 'PAYROLL_ID',
379 p_token_value => l_payroll_id
380 );
381
382 -- Update the payroll_id column on the Payroll_Action record.
383 update pay_payroll_actions
384 set payroll_id = l_payroll_id
385 where payroll_action_id = pactid;
386
387
388 select get_parameter('START_DATE', legislative_parameters)
389 into l_start_date
390 from pay_payroll_actions
391 where payroll_action_id = pactid;
392
393 select get_parameter('END_DATE', legislative_parameters)
394 into l_end_date
395 from pay_payroll_actions
396 where payroll_action_id = pactid;
397
398 select get_parameter('BG_ID', legislative_parameters)
399 into l_bg_id
400 from pay_payroll_actions
401 where payroll_action_id = pactid;
402
403 select get_parameter('ASG_SET_ID', legislative_parameters)
404 into g_asg_set_id
405 from pay_payroll_actions
406 where payroll_action_id = pactid;
407
408 select get_parameter('PERSON_ID', legislative_parameters)
409 into g_person_id
410 from pay_payroll_actions
411 where payroll_action_id = pactid;
412
413 g_canonical_start_date := to_date(l_start_date,'yyyy/mm/dd');
414 g_canonical_end_date := to_date(l_end_date,'yyyy/mm/dd');
415
416 g_business_group_id := l_bg_id;
417
418 hr_utility.set_location('Step ' || l_proc, 20);
419 hr_utility.set_location('l_payroll_id = ' || l_payroll_id, 20);
420 hr_utility.set_location('l_start_date = ' || l_start_date, 20);
421 hr_utility.set_location('l_end_date = ' || l_end_date, 20);
422 hr_utility.set_location('g_business_group_id = ' || g_business_group_id, 20);
423 hr_utility.set_location('g_asg_set_id = ' || g_asg_set_id, 20);
424 hr_utility.set_location('g_person_id = ' || g_person_id, 20);
425
426 hr_utility.set_location('g_canonical_start_date = ' || g_canonical_start_date, 20);
427 hr_utility.set_location('g_canonical_end_date = ' || g_canonical_end_date, 20);
428
429
430 -- Archive Creator Information that have not been archived yet for the specified UIF Month
431 open csr_creator_info;
432 fetch csr_creator_info
433 into l_creator_uif_reference,
434 l_contact_person,
435 l_contact_number,
436 l_contact_email_address;
437
438 if csr_creator_info%notfound
439 then
440
441 hr_utility.set_location('ZA UIF CREATOR DETAILS does not exist', 21);
442
443 else
444
445 hr_utility.set_location('Calling arch_pay_action_level_data', 25);
446
447 hr_utility.set_location('Archiving ZA UIF CREATOR DETAILS', 30);
448
449 hr_utility.set_location('l_creator_uif_reference '||l_creator_uif_reference, 30);
450 hr_utility.set_location('l_contact_person '||l_contact_person, 30);
451 hr_utility.set_location('l_contact_number '||l_contact_number, 30);
452 hr_utility.set_location('l_contact_email_address '||l_contact_email_address, 30);
453
454 --Process UIF Ref. No.
455 l_creator_uif_reference := process_uif_ref_no (l_creator_uif_reference);
456
457 hr_utility.set_location('l_creator_uif_reference_processed '||l_creator_uif_reference, 30);
458
459 -- Archive 'ZA UIF CREATOR DETAILS'
460 pay_action_information_api.create_action_information
461 (
462 p_action_information_id => l_action_info_id,
463 p_action_context_id => pactid,
464 p_action_context_type => 'PA',
465 p_object_version_number => l_ovn,
466 p_effective_date => g_archive_effective_date,
467 p_action_information_category => 'ZA UIF CREATOR DETAILS',
468 p_action_information1 => g_business_group_id,
469 p_action_information2 => to_char(g_canonical_end_date, 'YYYYMM'),
470 p_action_information3 => l_payroll_id,
471 p_action_information4 => l_creator_uif_reference /*Creator UIF Reference Number*/,
472 p_action_information5 => l_contact_person /*Contact Person*/,
473 p_action_information6 => l_contact_number /*Contact Number*/,
474 p_action_information7 => l_contact_email_address /*Contact E-mail Address*/
475 );
476
477 end if;
478
479 close csr_creator_info;
480
481
482 sql_range :=
483 'select distinct asg.person_id
484 from per_assignments_f asg,
485 pay_payrolls_f ppf,
486 pay_payroll_actions ppa
487 where ppa.payroll_action_id = :payroll_action_id
488 and asg.business_group_id = ppa.business_group_id
489 and asg.assignment_type = ''E''
490 and ppf.payroll_id = asg.payroll_id
491 and ppf.payroll_id = ppa.payroll_id
492 order by asg.person_id';
493
494 sqlstr := sql_range;
495
496 hr_utility.set_location('Leaving ' || l_proc, 10);
497
498 --hr_utility.trace_off;
499 --
500 exception
501 when others then
502 sqlstr := null;
503 --
504 end range_cursor;
505
506
507 /*--------------------------------------------------------------------------
508 Name : range_cursor_mag
509 Purpose : This returns the select statement that is used to created the
510 range rows for the UIF File.
511 Arguments :
512 Notes : The range cursor determines which people should be processed.
513 The normal practice is to include everyone, and then limit
514 the list during the assignment action creation.
515 --------------------------------------------------------------------------*/
516 procedure range_cursor_mag
517 (
518 pactid in number,
519 sqlstr out nocopy varchar2
520 ) is
521
522 sql_range varchar2(4000);
523
524 l_proc varchar2(50) := g_package || 'range_cursor_mag';
525
526 begin
527
528 --hr_utility.trace_on(null, 'UIF');
529
530 hr_utility.set_location('Entering ' || l_proc, 10);
531
532 sql_range :=
533 'select distinct asg.person_id
534 from per_assignments_f asg,
535 pay_payrolls_f ppf,
536 pay_payroll_actions ppa
537 where ppa.payroll_action_id = :payroll_action_id
538 and asg.business_group_id = ppa.business_group_id
539 and asg.assignment_type = ''E''
540 and ppf.payroll_id = asg.payroll_id
541 and ppf.payroll_id = ppa.payroll_id
542 order by asg.person_id';
543
544 sqlstr := sql_range;
545
546 hr_utility.set_location('Leaving ' || l_proc, 10);
547
548 --hr_utility.trace_off;
549 --
550 exception
551 when others then
552 sqlstr := null;
553 --
554 end range_cursor_mag;
555
556
557 /*--------------------------------------------------------------------------
558 Name : action_creation
559 Purpose : This creates the assignment actions for a specific chunk.
560 Arguments :
561 Notes :
562 --------------------------------------------------------------------------*/
563 procedure action_creation
564 (
565 pactid in number,
566 stperson in number,
567 endperson in number,
568 chunk in number
569 ) is
570
571 -- This cursor returns all Assignments that are active during,
572 -- or that ends in this UIF Month (not before). It excludes Independent
573 -- Contractors and specific Nature of Person assignments.
574 -- Note: There is no outer join to per_assignment_extra_info, as it is
575 -- mandatory that an Assignment must have a Legal Entity, which
576 -- must be entered on per_assignment_extra_info
577
578 /* Changes Bug 2798916 - Commenting existing query and rewriting the query */
579
580 /* cursor csr_get_asg (p_pactid number
581 , p_stperson number
582 , p_endperson number
583 , p_canonical_start_date date
584 , p_canonical_end_date date) is
585 select asg.person_id person_id,
586 asg.assignment_id assignment_id
587 from per_assignments_f asg,
588 per_assignments_f asg3,
589 --pay_payrolls_f ppf, -- Bug 2608190
590 pay_payroll_actions ppa_arch,
591 per_assignment_extra_info paei,
592 per_periods_of_service pds -- Bug 2654703
593 where asg.business_group_id = ppa_arch.business_group_id
594 and asg.period_of_service_id = pds.period_of_service_id -- Bug 2608190
595 and asg3.period_of_service_id = pds.period_of_service_id -- Bug 2608190
596 and asg.person_id between p_stperson and p_endperson
597 and paei.assignment_id = asg.assignment_id
598 and paei.aei_information_category = 'ZA_SPECIFIC_INFO'
599 -- Not an Independent Contractor
600 and nvl(paei.aei_information6, 'N') = 'N'
601 -- Nature of Person not in the following ZA_PER_NATURES Lookup Values
602 and paei.aei_information4 not in ('04', '05', '06', '07', '08', '09')
603 and ppa_arch.payroll_action_id = p_pactid
604 --and ppf.payroll_id = ppa_arch.payroll_id -- Bug 2608190
605 --and asg.payroll_id = ppf.payroll_id -- Bug 2608190
606 and ppa_arch.payroll_id = asg.payroll_id -- Bug 2608190
607 --and ppa_arch.effective_date between ppf.effective_start_date -- Bug 2608190
608 -- and ppf.effective_end_date -- Bug 2608190
609 -- Get the Assignment End Date
610 and asg.effective_end_date =
611 (
612 select max(asg2.effective_end_date)
613 from per_assignments_f asg2
614 , per_assignment_status_types sta
615 where asg2.assignment_id = asg.assignment_id
616 and asg2.assignment_status_type_id = sta.assignment_status_type_id
617 and sta.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
618 )
619 -- Check that Assignment ends after UIF Month Start Date
620 and ( asg.effective_end_date >= p_canonical_start_date
621 or pds.final_process_date >= p_canonical_start_date ) -- Bug 2608190
622 -- Get the Assignment Start Date
623 and asg3.assignment_id = asg.assignment_id
624 and asg3.effective_start_date =
625 (
626 select min(asg4.effective_start_date)
627 from per_assignments_f asg4
628 , per_assignment_status_types sta
629 where asg4.assignment_id = asg3.assignment_id
630 and asg4.assignment_status_type_id = sta.assignment_status_type_id
631 and sta.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
632 )
633 -- Check that Assignment starts before UIF Month End Date
634 and asg3.effective_start_date <= p_canonical_end_date
635 order by 2
636 for update of asg.assignment_id;
637 */
638
639 /* Changes Bug 2798916 - New query
640 This Query will not take the records which falls after last standard process
641 and before final process date and having UIF contributions zero
642 */
643
644 cursor csr_get_asg (p_pactid number
645 , p_stperson number
646 , p_endperson number
647 , p_canonical_start_date date
648 , p_canonical_end_date date) is
649 select ppf.person_id
650 ,paa.assignment_id
651 from per_all_people_f ppf
652 ,per_all_assignments_f paa
653 ,per_assignment_extra_info paei
654 ,pay_payroll_actions ppa_arch
655 ,per_periods_of_service pps
656 where paa.business_group_id = ppa_arch.business_group_id
657 and paa.person_id = ppf.person_id
658 and ppf.person_id between p_stperson and p_endperson /* to select all Employees in a payroll run */
659 and paa.period_of_service_id = pps.period_of_service_id
660 and paei.assignment_id = paa.assignment_id
661 and ppa_arch.payroll_id = paa.payroll_id
662 and ppa_arch.payroll_action_id = p_pactid
663 and paei.aei_information_category = 'ZA_SPECIFIC_INFO'
664 /* Not an Independent Contractor */
665 and nvl(paei.aei_information6, 'N') = 'N'
666 /* Nature of Person not in the following ZA_PER_NATURES Lookup Values */
667 and paei.aei_information4 not in ('04', '05', '06', '07', '08', '09')
668 and ppf.effective_start_date = ( select max(effective_start_date)
669 from per_all_people_f ppf1
670 where ppf1.person_id = ppf.person_id
671 and ppf1.effective_start_date <= p_canonical_end_date
672 and ppf1.effective_end_date >= '01-MAR-' || to_number(to_char(p_canonical_end_date ,'YYYY') - 1))
673 and paa.effective_start_date = ( select max(paa1.effective_start_date)
674 from per_all_assignments_f paa1 where paa1.assignment_id = paa.assignment_id
675 and paa1.effective_start_date <= p_canonical_end_date
676 and paa1.effective_end_date >= '01-MAR-' || to_number(to_char(p_canonical_end_date ,'YYYY') - 1))
677 and
678 (
679 (
680 pps.actual_termination_date is not null
681 and
682 (
683 (
684 pps.actual_termination_date between '01-MAR-' || to_number(to_char(p_canonical_end_date ,'YYYY') - 1) and p_canonical_end_date
685 and
686 (
687 pps.actual_termination_date between p_canonical_start_date and p_canonical_end_date
688 or
689 (
690 pps.actual_termination_date < p_canonical_start_date
691 and nvl(pps.final_process_date,to_date('31-12-4712','DD-MM-YYYY')) >= p_canonical_start_date
692 and pay_za_uif_archive_pkg.get_balance_value(paa.assignment_id,'Total UIFable Income','_ASG_TAX_MTD',p_canonical_end_date) <> 0
693 )
694 )
695 )
696 or pps.actual_termination_date > p_canonical_end_date /* New Condition for Bug 3869426 */
697 )
698 )
699 or pps.actual_termination_date is null
700 )
701 order by 2
702 for update of paa.assignment_id;
703
704
705 l_payroll_id number;
706 l_consolidation_set number;
707 l_assignment_set_id number;
708
709 leg_param pay_payroll_actions.legislative_parameters%type;
710 asg_include boolean;
711 lockingactid number;
712 v_incl_sw hr_assignment_set_amendments.include_or_exclude%type;
713
714 l_proc varchar2(50) := g_package || 'action_creation';
715
716 l_start_date varchar2(30);
717 l_end_date varchar2(30);
718 l_bg_id number;
719 l_asg_set_id number;
720 l_person_id number;
721 l_canonical_end_date date;
722 l_canonical_start_date date;
723
724 l_persid number;
725 l_asgid number;
726
727 begin
728
729 --hr_utility.trace_on(null, 'UIF');
730
731 hr_utility.set_location('Entering ' || l_proc, 10);
732
733 select get_parameter('START_DATE', legislative_parameters)
734 into l_start_date
735 from pay_payroll_actions
736 where payroll_action_id = pactid;
737
738 select get_parameter('END_DATE', legislative_parameters)
739 into l_end_date
740 from pay_payroll_actions
741 where payroll_action_id = pactid;
742
743 select get_parameter('BG_ID', legislative_parameters)
744 into l_bg_id
745 from pay_payroll_actions
746 where payroll_action_id = pactid;
747
748 select get_parameter('ASG_SET_ID', legislative_parameters)
749 into l_asg_set_id
750 from pay_payroll_actions
751 where payroll_action_id = pactid;
752
753 select get_parameter('PERSON_ID', legislative_parameters)
754 into l_person_id
755 from pay_payroll_actions
756 where payroll_action_id = pactid;
757
758 l_canonical_start_date := to_date(l_start_date,'yyyy/mm/dd');
759 l_canonical_end_date := to_date(l_end_date,'yyyy/mm/dd');
760
761 hr_utility.set_location('pactid '||to_char(pactid), 10);
762 hr_utility.set_location('l_person_id '||to_char(l_person_id), 10);
763 hr_utility.set_location('l_asg_set_id '||to_char(l_asg_set_id), 10);
764 hr_utility.set_location('stperson '||to_char(stperson), 10);
765 hr_utility.set_location('endperson '||to_char(endperson), 10);
766 hr_utility.set_location('l_canonical_start_date '||to_char(l_canonical_start_date, 'dd/mm/yyyy'), 10);
767 hr_utility.set_location('l_canonical_end_date '||to_char(l_canonical_end_date, 'dd/mm/yyyy'), 10);
768
769 if l_asg_set_id is not null then
770 -- need to find out if assignments in assignment-set are set to Include or Exclude.
771 begin
772 select distinct include_or_exclude
773 into v_incl_sw
774 from hr_assignment_set_amendments
775 where assignment_set_id = l_asg_set_id;
776 exception
777 when no_data_found then
778 -- default to Include, should not go here though.
779 v_incl_sw := 'I';
780 end;
781 end if;
782
783 hr_utility.set_location('Before csr_get_asg', 20);
784
785 for asgrec in csr_get_asg (pactid, stperson, endperson, l_canonical_start_date, l_canonical_end_date) loop
786
787 hr_utility.set_location('ASG: ' || to_char(asgrec.assignment_id), 30);
788
789 asg_include := TRUE;
790
791 -- Remove duplicate assignments
792 if prev_asg_id <> asgrec.assignment_id then
793
794 prev_asg_id := asgrec.assignment_id;
795
796 if l_asg_set_id is not null then
797
798 declare
799 inc_flag varchar2(5);
800 begin
801 select include_or_exclude
802 into inc_flag
803 from hr_assignment_set_amendments
804 where assignment_set_id = l_asg_set_id
805 and assignment_id = asgrec.assignment_id;
806
807 if inc_flag = 'E' then
808 asg_include := FALSE;
809 end if;
810
811 exception
812 -- goes through this exception, for each assignment in the payroll
813 -- but not in the relevant assignment_set.
814 when no_data_found then
815 if v_incl_sw = 'I' then
816 asg_include := FALSE;
817 else
818 asg_include := TRUE;
819 end if;
820 end ;
821
822 end if;
823
824 if l_person_id is not null then
825
826 if l_person_id <> asgrec.person_id then
827 asg_include := FALSE;
828 end if;
829
830 end if;
831
832 if asg_include = TRUE then
833 select pay_assignment_actions_s.nextval
834 into lockingactid
835 from dual;
836
837
838 -- Insert assignment into pay_assignment_actions
839 hr_nonrun_asact.insact
840 (
841 lockingactid,
842 asgrec.assignment_id,
843 pactid,
844 chunk,
845 null
846 );
847
848 end if;
849
850 end if;
851
852 end loop;
853
854 hr_utility.set_location('Leaving ' || l_proc, 30);
855
856 --hr_utility.trace_off;
857
858 end action_creation;
859
860
861 /*--------------------------------------------------------------------------
862 Name : archinit
863 Purpose : This procedure can be used to perform an initialisation
864 section
865 Arguments :
866 Notes :
867 --------------------------------------------------------------------------*/
868 procedure archinit
869 (
870 p_payroll_action_id in number
871 ) is
872
873 l_proc varchar2(50) := g_package || 'archinit';
874
875
876 begin
877
878 null;
879
880 end archinit;
881
882
883 /*--------------------------------------------------------------------------
884 Name : archive_data
885 Purpose : Archive data by calling
886 pay_action_information_api.create_action_information
887 Arguments :
888 Notes :
889 --------------------------------------------------------------------------*/
890 procedure archive_data
891 (
892 p_assactid in number,
893 p_effective_date in date
894 ) is
895
896 -- Employee Data:
897 l_id_number per_people_f.national_identifier%type;
898 l_other_number per_people_f.per_information2%type;
899 l_employee_number per_people_f.employee_number%type;
900 l_last_name per_people_f.last_name%type;
901 l_first_names varchar2(600);
902 l_date_of_birth per_people_f.date_of_birth%type;
903 l_date_employed_from per_periods_of_service.date_start%type;
904 l_date_employed_to per_periods_of_service.actual_termination_date%type;
905 l_leaving_reason per_periods_of_service.leaving_reason%type;
906
907 cursor csr_employee_data (p_person_id number, p_eff_date date) is
908 select per.national_identifier ID_Number,
909 nvl(per.per_information2, per.per_information3) Other_Number,
910 per.employee_number,
911 per.last_name,
912 names(per.first_name||', '||per.middle_names) First_Names,
913 per.date_of_birth,
914 pos.date_start Date_Employed_From,
915 pos.actual_termination_date Date_Employed_To,
916 pos.leaving_reason
917 from per_people_f per,
918 per_periods_of_service pos
919 where per.person_id = p_person_id
920 -- Bug 4306265: and per.effective_end_date between per.effective_start_date and p_eff_date -- Modified the condition for Bug : 3869426
921 and p_eff_date between per.effective_start_date and per.effective_end_date -- Bug 4306265: re-enabled /* Old Condition Before Bug : 3869426 */
922 and per.per_information_category = 'ZA'
923 and pos.person_id(+) = per.person_id
924 and nvl(pos.actual_termination_date(+), per.effective_end_date) = per.effective_end_date;
925
926 l_leaving_reason_meaning fnd_lookup_values.meaning%type;
927
928 cursor csr_leaving_reason_meaning (p_leaving_reason varchar2) is
929 select flv.meaning
930 from fnd_lookup_types flt,
931 fnd_lookup_values flv
932 where flt.lookup_type = 'LEAV_REAS'
933 and flt.lookup_type = flv.lookup_type
934 and flv.language = 'US'
935 and flv.lookup_code = p_leaving_reason
936 and flv.enabled_flag = 'Y';
937
938
939 --Assignment Start Date
940 l_asg_start per_assignments_f.effective_start_date%type;
941
942 cursor csr_asg_start (p_asg_id number) is
943 select min(asg2.effective_start_date)
944 from per_assignments_f asg2
945 , per_assignment_status_types sta
946 where asg2.assignment_id = p_asg_id
947 and asg2.assignment_status_type_id = sta.assignment_status_type_id
948 and sta.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN', 'TERM_ASSIGN'); -- Added 'TERM_ASSIGN' for bug 3869426
949
950 --Assignment UIF Data:
951 l_empl_status per_assignment_extra_info.aei_information2%type;
952 l_reason_non_contrib per_assignment_extra_info.aei_information1%type;
953
954 cursor csr_asg_uif_data (p_asg_id number) is
955 select paei.aei_information2 Empl_Status,
956 paei.aei_information1 Reason_Non_Contrib
957 from per_assignment_extra_info paei
958 where paei.assignment_id = p_asg_id
959 and paei.aei_information_category = 'ZA_UIF_INFO';
960
961
962 --Employment Status Code:
963 cursor csr_empl_status (p_empl_stat_value varchar2) is
964 select flv.lookup_code
965 from fnd_lookup_types flt,
966 fnd_lookup_values flv
967 where flt.lookup_type = 'ZA_UIF_TERMINATION_CATEGORIES'
968 and flt.lookup_type = flv.lookup_type
969 and flv.language = 'US'
970 and flv.meaning = p_empl_stat_value
971 and flv.enabled_flag = 'Y';
972
973
974 --Assignment Legal Entity Data:
975 l_legal_entity per_assignment_extra_info.aei_information7%type;
976
977 cursor csr_asg_leg_ent_data (p_asg_id number) is
978 select paei.aei_information7 Legal_Entity
979 from per_assignment_extra_info paei
980 where paei.assignment_id = p_asg_id
981 and paei.aei_information_category = 'ZA_SPECIFIC_INFO';
982
983
984 --Legal Entity Organization Data:
985 l_employer_uif_ref_no hr_organization_information.org_information6%type;
986
987 /* Changes as per Bug2654703 */
988 l_employer_email_address hr_organization_information.org_information10%type;
989
990 l_employer_paye_number hr_organization_information.org_information3%type;
991
992 -- call with l_legal_entity from Assignment UIF Data cursor
993 cursor csr_leg_entity_data (p_org_id number) is
994 select hoi.org_information6 Employer_UIF_Ref_No,
995 /* Changes as per Bug2654703 */
996 hoi.org_information10 Employer_email_Address,
997
998 hoi.org_information3 Employer_PAYE_Number
999 from hr_organization_information hoi,
1000 hr_all_organization_units org
1001 where org.organization_id = p_org_id
1002 and org.organization_id = hoi.organization_id
1003 and hoi.org_information_context = 'ZA_LEGAL_ENTITY';
1004
1005
1006 --Bank Data:
1007 l_bank_branch_code pay_external_accounts.segment1%type;
1008 l_bank_account_number pay_external_accounts.segment3%type;
1009 l_bank_account_type pay_external_accounts.segment2%type;
1010
1011 cursor csr_bank_data (p_asg_id number, p_eff_date date) is
1012 select pea.segment1 seg1,
1013 pea.segment3 seg3,
1014 pea.segment2 seg2
1015 from pay_personal_payment_methods_f pppm,
1016 pay_external_accounts pea,
1017 pay_org_payment_methods_f porg,
1018 pay_legislation_rules plr
1019 where pppm.assignment_id = p_asg_id
1020 and pea.external_account_id = pppm.external_account_id
1021 and pea.id_flex_num = plr.rule_mode
1022 and plr.LEGISLATION_CODE = 'ZA'
1023 and plr.rule_type = 'E'
1024 and pea.territory_code = 'ZA'
1025 and pppm.priority =
1026 ( select min(pppm2.priority)
1027 from pay_personal_payment_methods_f pppm2,
1028 pay_external_accounts pea2,
1029 pay_org_payment_methods_f porg2,
1030 pay_legislation_rules plr2
1031 where pppm2.assignment_id = pppm.assignment_id
1032 and pea2.external_account_id = pppm2.external_account_id
1033 and pea2.id_flex_num = plr2.rule_mode
1034 and plr2.LEGISLATION_CODE = 'ZA'
1035 and plr2.rule_type = 'E'
1036 and pea2.territory_code = 'ZA'
1037 and p_eff_date between pppm2.effective_start_date
1038 and pppm2.effective_end_date
1039 and pppm2.org_payment_method_id = porg2.org_payment_method_id
1040 /* Exclude 3rd Party Payment Methods*/
1041 and porg2.defined_balance_id is not null
1042 and p_eff_date between porg2.effective_start_date
1043 and porg2.effective_end_date
1044 )
1045 and p_eff_date between pppm.effective_start_date
1046 and pppm.effective_end_date
1047 and pppm.org_payment_method_id = porg.org_payment_method_id
1048 /* Exclude 3rd Party Payment Methods*/
1049 and porg.defined_balance_id is not null
1050 and p_eff_date between porg.effective_start_date
1051 and porg.effective_end_date;
1052
1053
1054 -- Added for Bug 4134166
1055 l_pay_periods_per_year number :=0;
1056 cursor csr_pay_periods_per_year(p_eff_date date, p_payroll_id number) is
1057 select count(ptp.end_date)
1058 from per_time_periods ptp
1059 where ptp.payroll_id = p_payroll_id
1060 and ptp.end_date >= '01-MAR-'||to_char(p_eff_date,'YYYY')
1061 and ptp.end_date < '01-MAR-'||to_number(to_char(p_eff_date,'YYYY')+1);
1062
1063 --Added for Bug 4134166
1064 l_pay_periods_per_month number :=0;
1065 cursor csr_pay_periods_per_month(p_eff_date date, p_payroll_id number)is
1066 select count(ptp.end_date)
1067 from per_time_periods ptp
1068 where ptp.payroll_id = p_payroll_id
1069 and to_char(ptp.end_date,'MMYYYY')= to_char(p_eff_date, 'MMYYYY');
1070
1071 --UIF Limit
1072 l_uif_limit number := 0;
1073 cursor csr_uif_limit (p_eff_date date) is
1074 select round((to_number(ffg.global_value)*l_pay_periods_per_month/l_pay_periods_per_year),2) --Bug 4134166
1075 --select (to_number(ffg.global_value)/12)
1076 from ff_globals_f ffg
1077 where ffg.global_name = 'ZA_UIF_ANN_LIM'
1078 and ffg.legislation_code = 'ZA'
1079 and p_eff_date between ffg.effective_start_date and ffg.effective_end_date;
1080
1081
1082 l_asgid pay_assignment_actions.assignment_id%type;
1083
1084 l_person_id per_assignments_f.person_id%type;
1085
1086 l_empl_stat_value varchar2(200);
1087
1088 l_action_info_id number;
1089 l_ovn number;
1090
1091 l_assignment_action_id number;
1092
1093 l_dimension pay_balance_dimensions.dimension_name%type:= '_ASG_TAX_MTD';
1094 l_balance_name pay_balance_types_tl.balance_name%type;
1095
1096 l_temp_gt_bal number := 0;
1097 l_gross_taxable_remuneration number := 0;
1098 l_gross_uif_remuneration number := 0;
1099 l_temp_uc_bal number := 0;
1100 l_uif_contribution number := 0;
1101
1102 l_archive_effective_date date;
1103 l_business_group_id number;
1104
1105 l_asg_eff_end_date date;
1106
1107 l_proc varchar2(50) := g_package || 'archive_data';
1108 l_pactid number:=0; --Bug 4134166
1109 l_payroll_id number:=0; --Bug 4134166
1110
1111 begin
1112
1113 --hr_utility.trace_on(null, 'UIF');
1114
1115 hr_utility.set_location('Entering ' || l_proc, 10);
1116
1117 --get the Archive Effective Date
1118 select ppa.effective_date,
1119 ppa.payroll_action_id
1120 into l_archive_effective_date,
1121 l_pactid --Bug 4134166
1122 from pay_payroll_actions ppa,
1123 pay_assignment_actions paa
1124 where paa.payroll_action_id = ppa.payroll_action_id
1125 and paa.assignment_action_id = p_assactid;
1126
1127
1128 --get the assignment_id from the assignment_action_id
1129 select paa.assignment_id
1130 into l_asgid
1131 from pay_assignment_actions paa
1132 where paa.assignment_action_id = p_assactid;
1133
1134 --get the person_id, Business Group and the
1135 --assignment's effective end date from the assignment_id
1136 select asg.person_id
1137 , business_group_id
1138 , asg.effective_end_date
1139 into l_person_id
1140 , l_business_group_id
1141 , l_asg_eff_end_date
1142 from per_assignments_f asg
1143 where asg.assignment_id = l_asgid
1144 and asg.effective_end_date =
1145 (
1146 select max(asg2.effective_end_date)
1147 from per_assignments_f asg2
1148 , per_assignment_status_types sta
1149 where asg2.assignment_id = l_asgid --Bug 4768622
1150 and asg2.assignment_status_type_id = sta.assignment_status_type_id
1151 and sta.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN') -- Bug 4306265: Removed 'TERM_ASSIGN') -- Added 'TERM_ASSIGN' for bug 3869426
1152 );
1153
1154
1155 -- Employee Data:
1156 -- Strat: Bug 4306265
1157 if l_asg_eff_end_date > p_effective_date then
1158 l_asg_eff_end_date := p_effective_date;
1159 end if;
1160 -- End: Bug 4306265
1161
1162 open csr_employee_data (l_person_id, l_asg_eff_end_date);
1163
1164 fetch csr_employee_data
1165 into l_id_number,
1166 l_other_number,
1167 l_employee_number,
1168 l_last_name,
1169 l_first_names,
1170 l_date_of_birth,
1171 l_date_employed_from,
1172 l_date_employed_to,
1173 l_leaving_reason;
1174
1175 close csr_employee_data;
1176
1177 -- When first_name is null, replace with 'XXX'
1178 -- Bug 2863938
1179 if l_first_names is null then
1180
1181 l_first_names := 'XXX';
1182
1183 end if;
1184
1185 --Assignment Start Date
1186 open csr_asg_start (l_asgid);
1187
1188 fetch csr_asg_start
1189 into l_asg_start;
1190
1191 close csr_asg_start;
1192
1193
1194 --If l_date_employed_from IS NULL then set it to the l_asg_start
1195 --as it is Mandatory for the UIF File
1196 if l_date_employed_from is null then
1197
1198 l_date_employed_from := l_asg_start;
1199
1200 end if;
1201
1202
1203 --Assignment UIF Data:
1204 open csr_asg_uif_data (l_asgid);
1205
1206 fetch csr_asg_uif_data
1207 into l_empl_status,
1208 l_reason_non_contrib;
1209
1210 close csr_asg_uif_data;
1211
1212
1213 --IF l_date_employed_to IS NOT NULL then get the value for l_empl_status from
1214 --the ZA_TERMINATION_CATEGORIES User Table, column UIF Employment Status,
1215 --by using the mapping for LEAV_REAS (from per_periods_of_service.leaving_reason)
1216 if (l_date_employed_to is not null and l_date_employed_to <> to_date('31/12/4712', 'DD/MM/YYYY'))
1217 then
1218
1219 --Get the Lookup Meaning for the LEAV_REAS Lookup Code in l_leaving_reason
1220 open csr_leaving_reason_meaning (l_leaving_reason);
1221
1222 fetch csr_leaving_reason_meaning
1223 into l_leaving_reason_meaning;
1224
1225 close csr_leaving_reason_meaning;
1226
1227 if l_leaving_reason_meaning is not null
1228 then
1229
1230 --Get the mapped Legislative UIF Employment Status value
1231 l_empl_stat_value := per_za_utility_pkg.get_table_value (
1232 p_table_name => 'ZA_TERMINATION_CATEGORIES'
1233 , p_col_name => 'UIF Employment Status'
1234 , p_row_value => l_leaving_reason_meaning
1235 , p_effective_date => p_effective_date
1236 , p_business_group_id => l_business_group_id
1237 );
1238
1239 --Set the l_empl_status code for each Legislative UIF Employment Status Meaning
1240 if l_empl_stat_value is not null then
1241
1242 open csr_empl_status (l_empl_stat_value);
1243
1244 fetch csr_empl_status
1245 into l_empl_status;
1246
1247 close csr_empl_status;
1248
1249 else
1250
1251 l_empl_status := '06';
1252
1253 end if;
1254
1255 else
1256 /* default Employment Status to 06 Resigned - should not happen though, as
1257 there should always be a Leaving Reason if Person is Terminated */
1258 l_empl_status := '06';
1259
1260 end if;
1261
1262 end if;
1263
1264 -- IF l_date_employed_to is greater than the end of the next UIF month then it,
1265 -- and the Employment Status, must not be displayed in the file
1266 if (l_date_employed_to is not null and l_date_employed_to > add_months(l_archive_effective_date, 1))
1267 then
1268
1269 l_date_employed_to := null;
1270 l_empl_status := '';
1271
1272 end if;
1273
1274
1275 --Legal Entity
1276 open csr_asg_leg_ent_data (l_asgid);
1277
1278 fetch csr_asg_leg_ent_data
1279 into l_legal_entity;
1280
1281 close csr_asg_leg_ent_data;
1282
1283
1284 --Legal Entity Organization Data:
1285 --call with l_legal_entity from Assignment UIF Data cursor
1286 open csr_leg_entity_data (l_legal_entity);
1287
1288 fetch csr_leg_entity_data
1289 into l_employer_uif_ref_no
1290 , l_employer_email_address /* Bug 2654703 */
1291 , l_employer_paye_number;
1292
1293 close csr_leg_entity_data;
1294
1295
1296 --Process UIF Ref No
1297 l_employer_uif_ref_no := process_uif_ref_no (l_employer_uif_ref_no);
1298
1299
1300 --Bank Data:
1301 open csr_bank_data (l_asgid, p_effective_date);
1302
1303 fetch csr_bank_data
1304 into l_bank_branch_code
1305 , l_bank_account_number
1306 , l_bank_account_type;
1307
1308 close csr_bank_data;
1309
1310
1311 --Balance Values
1312
1313
1314 --Gross Taxable Remuneration
1315
1316 l_balance_name := 'Total NRFIable Income';
1317 l_temp_gt_bal := get_balance_value(l_asgid, l_balance_name, l_dimension, p_effective_date);
1318 l_gross_taxable_remuneration := l_gross_taxable_remuneration + l_temp_gt_bal;
1319 l_balance_name := 'Total RFIable Income';
1320 l_temp_gt_bal := get_balance_value(l_asgid, l_balance_name, l_dimension, p_effective_date);
1321 l_gross_taxable_remuneration := l_gross_taxable_remuneration + l_temp_gt_bal;
1322 l_balance_name := 'Total NRFIable Annual Income';
1323 l_temp_gt_bal := get_balance_value(l_asgid, l_balance_name, l_dimension, p_effective_date);
1324 l_gross_taxable_remuneration := l_gross_taxable_remuneration + l_temp_gt_bal;
1325 l_balance_name := 'Total RFIable Annual Income';
1326 l_temp_gt_bal := get_balance_value(l_asgid, l_balance_name, l_dimension, p_effective_date);
1327 l_gross_taxable_remuneration := l_gross_taxable_remuneration + l_temp_gt_bal;
1328 l_balance_name := 'Retirement or Retrenchment Gratuities';
1329 l_temp_gt_bal := get_balance_value(l_asgid, l_balance_name, l_dimension, p_effective_date);
1330 l_gross_taxable_remuneration := l_gross_taxable_remuneration + l_temp_gt_bal;
1331 l_balance_name := 'Resignation Pension and RAF Lump Sums';
1332 l_temp_gt_bal := get_balance_value(l_asgid, l_balance_name, l_dimension, p_effective_date);
1333 l_gross_taxable_remuneration := l_gross_taxable_remuneration + l_temp_gt_bal;
1334 l_balance_name := 'Retirement Pension and RAF Lump Sums';
1335 l_temp_gt_bal := get_balance_value(l_asgid, l_balance_name, l_dimension, p_effective_date);
1336 l_gross_taxable_remuneration := l_gross_taxable_remuneration + l_temp_gt_bal;
1337 l_balance_name := 'Resignation Provident Lump Sums';
1338 l_temp_gt_bal := get_balance_value(l_asgid, l_balance_name, l_dimension, p_effective_date);
1339 l_gross_taxable_remuneration := l_gross_taxable_remuneration + l_temp_gt_bal;
1340 l_balance_name := 'Retirement Provident Lump Sums';
1341 l_temp_gt_bal := get_balance_value(l_asgid, l_balance_name, l_dimension, p_effective_date);
1342 l_gross_taxable_remuneration := l_gross_taxable_remuneration + l_temp_gt_bal;
1343 l_balance_name := 'Special Remuneration';
1344 l_temp_gt_bal := get_balance_value(l_asgid, l_balance_name, l_dimension, p_effective_date);
1345 l_gross_taxable_remuneration := l_gross_taxable_remuneration + l_temp_gt_bal;
1346 l_balance_name := 'Other Lump Sums';
1347 l_temp_gt_bal := get_balance_value(l_asgid, l_balance_name, l_dimension, p_effective_date);
1348 l_gross_taxable_remuneration := l_gross_taxable_remuneration + l_temp_gt_bal;
1349
1350
1351 --Gross UIF Remuneration Package
1352
1353 l_balance_name := 'Total UIFable Income';
1354 l_gross_uif_remuneration := get_balance_value(l_asgid, l_balance_name, l_dimension, p_effective_date);
1355
1356 --If Total UIFable Income greater than Monthly UIF Limit,
1357 --then the limit must be printed instead
1358
1359 --Added for Bug 4134166
1360 get_parameters
1361 (
1362 p_payroll_action_id => l_pactid, -- Payroll Action of the Archiver
1363 p_token_name => 'PAYROLL_ID',
1364 p_token_value => l_payroll_id
1365 );
1366
1367 hr_utility.set_location('l_payroll_id => '|| l_payroll_id, 100);
1368 hr_utility.set_location('l_pactid => '|| l_pactid, 100);
1369
1370 --Added for Bug 4134166
1371 open csr_pay_periods_per_year(p_effective_date,l_payroll_id);
1372 fetch csr_pay_periods_per_year
1373 into l_pay_periods_per_year;
1374
1375 close csr_pay_periods_per_year;
1376
1377 --Added for Bug 4134166
1378 open csr_pay_periods_per_month(p_effective_date,l_payroll_id);
1379
1380 fetch csr_pay_periods_per_month
1381 into l_pay_periods_per_month;
1382
1383 close csr_pay_periods_per_month;
1384
1385 hr_utility.set_location('l_pay_periods_per_year => '|| to_char(l_pay_periods_per_year), 100);
1386 hr_utility.set_location('l_pay_periods_per_month => '|| to_char(l_pay_periods_per_month), 100);
1387
1388 open csr_uif_limit (p_effective_date);
1389
1390 fetch csr_uif_limit
1391 into l_uif_limit;
1392
1393 close csr_uif_limit;
1394
1395
1396 if l_gross_uif_remuneration > l_uif_limit then
1397
1398 l_gross_uif_remuneration := l_uif_limit;
1399
1400 end if;
1401
1402
1403 --UIF Employee Contribution = UIF Employee Contribution + UIF Employer Contribution
1404
1405 l_balance_name := 'UIF Employee Contribution';
1406 l_temp_uc_bal := get_balance_value(l_asgid, l_balance_name, l_dimension, p_effective_date);
1407 l_uif_contribution := l_uif_contribution + l_temp_uc_bal;
1408
1409 l_balance_name := 'UIF Employer Contribution';
1410 l_temp_uc_bal := get_balance_value(l_asgid, l_balance_name, l_dimension, p_effective_date);
1411 l_uif_contribution := l_uif_contribution + l_temp_uc_bal;
1412
1413
1414 --If UIF contribution (8320) is zero, check Person, Assignment,
1415 --Assignment Extra Information Flexfield for a value in Reason for Non-contribution
1416 --field. If this field has no value print 06 (no income this period)
1417 if l_uif_contribution <= 0 then
1418
1419 if l_reason_non_contrib is null then
1420
1421 l_reason_non_contrib := '06';
1422
1423 end if;
1424 else
1425 --Bug 4072410
1426 --If an employee has a value in the UIF Employee Contribution balance
1427 --AND has a non-contribution reason of 01, then no non-contribution
1428 --reason must be written to the UIF File.
1429 if l_reason_non_contrib = '01' then
1430 l_reason_non_contrib := NULL;
1431 end if;
1432 end if;
1433
1434 -- Changes as per Enhancement Bug 2874102
1435 -- As reason_non_contrib code '07' is obsoleted after 01-04-2003, if the code
1436 -- is there for employee, should not be archived.
1437
1438 if ( l_reason_non_contrib = '07'
1439 and p_effective_date >= to_date('01-04-2003','dd-mm-yyyy')
1440 ) then
1441 l_reason_non_contrib := NULL;
1442 end if;
1443
1444 -- Changes as per Bug 7255839
1445 -- the code 007 (Employees who receive Old Age Pension from the State)
1446 -- should actually be reported as code '07'
1447 -- We could not use '07' in the lookup, as it contained the end-date
1448 -- value for Seasonal Worker. Hence converting '007' to '07' here.
1449 if ( l_reason_non_contrib = '007') then
1450 l_reason_non_contrib := '07';
1451 end if;
1452
1453
1454 hr_utility.set_location('Archiving ZA UIF EMPLOYEE DETAILS', 50);
1455
1456 -- Only archive record if the Assignment's Legal Entity and the latter's
1457 -- Employer UIF Ref No exist
1458 if (l_employer_uif_ref_no is not null and l_legal_entity is not null) then
1459
1460 hr_utility.set_location('p_action_context_id => '|| to_char(p_assactid), 60);
1461 hr_utility.set_location('p_action_context_type => '|| 'AAP', 60);
1462 hr_utility.set_location('p_assignment_id => '|| to_char(l_asgid), 60);
1463 hr_utility.set_location('p_effective_date => '|| to_char(l_archive_effective_date, 'dd/mm/yyyy'), 60);
1464 hr_utility.set_location('p_action_information_category => '|| 'ZA UIF EMPLOYEE DETAILS', 60);
1465 hr_utility.set_location('p_action_information1 => '|| to_char(l_asgid), 60);
1466 hr_utility.set_location('p_action_information2 => '|| to_char(l_archive_effective_date, 'YYYYMM'), 60);
1467 hr_utility.set_location('p_action_information3 => '|| l_legal_entity, 60);
1468 hr_utility.set_location('p_action_information4 => '|| l_employer_uif_ref_no, 60);
1469 hr_utility.set_location('p_action_information5 => '|| l_employer_paye_number, 60);
1470 hr_utility.set_location('p_action_information6 => '|| l_id_number, 60);
1471 hr_utility.set_location('p_action_information7 => '|| l_other_number, 60);
1472 hr_utility.set_location('p_action_information8 => '|| l_employee_number, 60);
1473 hr_utility.set_location('p_action_information9 => '|| l_last_name, 60);
1474 hr_utility.set_location('p_action_information10 => '|| l_first_names, 60);
1475 hr_utility.set_location('p_action_information11 => '|| to_char(l_date_of_birth, 'YYYYMMDD'), 60);
1476 hr_utility.set_location('p_action_information12 => '|| to_char(l_date_employed_from, 'YYYYMMDD'), 60);
1477 hr_utility.set_location('p_action_information13 => '|| to_char(l_date_employed_to, 'YYYYMMDD'), 60);
1478 hr_utility.set_location('p_action_information14 => '|| l_empl_status, 60);
1479 hr_utility.set_location('p_action_information15 => '|| l_reason_non_contrib, 60);
1480 hr_utility.set_location('p_action_information16 => '|| l_gross_taxable_remuneration, 60);
1481 hr_utility.set_location('p_action_information17 => '|| l_gross_uif_remuneration, 60);
1482 hr_utility.set_location('p_action_information18 => '|| l_uif_contribution, 60);
1483 hr_utility.set_location('p_action_information19 => '|| l_bank_branch_code, 60);
1484 hr_utility.set_location('p_action_information20 => '|| l_bank_account_number, 60);
1485 hr_utility.set_location('p_action_information21 => '|| l_bank_account_type, 60);
1486 hr_utility.set_location('p_action_information22 => '|| l_employer_email_address, 50); /* Bug 2654703 */
1487
1488
1489 -- Archive the ZA UIF EMPLOYEE DETAILS
1490 pay_action_information_api.create_action_information
1491 (
1492 p_action_information_id => l_action_info_id,
1493 p_action_context_id => p_assactid, -- Assignment Action of the Archiver
1494 p_action_context_type => 'AAP',
1495 p_object_version_number => l_ovn,
1496 p_assignment_id => l_asgid,
1497 p_effective_date => l_archive_effective_date,
1498 p_source_id => null,
1499 p_source_text => null,
1500 p_action_information_category => 'ZA UIF EMPLOYEE DETAILS',
1501 p_action_information1 => l_asgid,
1502 p_action_information2 => to_char(l_archive_effective_date, 'YYYYMM'),
1503 p_action_information3 => l_legal_entity,
1504 p_action_information4 => l_employer_uif_ref_no,
1505 p_action_information5 => l_employer_paye_number,
1506 p_action_information6 => l_id_number,
1507 p_action_information7 => l_other_number,
1508 p_action_information8 => l_employee_number,
1509 p_action_information9 => l_last_name,
1510 p_action_information10 => l_first_names,
1511 p_action_information11 => to_char(l_date_of_birth, 'YYYYMMDD'),
1512 p_action_information12 => to_char(l_date_employed_from, 'YYYYMMDD'),
1513 p_action_information13 => to_char(l_date_employed_to, 'YYYYMMDD'),
1514 p_action_information14 => l_empl_status,
1515 p_action_information15 => l_reason_non_contrib,
1516 p_action_information16 => l_gross_taxable_remuneration,
1517 p_action_information17 => l_gross_uif_remuneration,
1518 p_action_information18 => l_uif_contribution,
1519 p_action_information19 => l_bank_branch_code,
1520 p_action_information20 => l_bank_account_number,
1521 p_action_information21 => l_bank_account_type,
1522 p_action_information22 => l_employer_email_address /* Bug 2654703 */
1523 );
1524
1525 end if;
1526
1527 hr_utility.set_location('Leaving ' || l_proc, 60);
1528
1529 --hr_utility.trace_off;
1530
1531 end archive_data;
1532
1533
1534 /*--------------------------------------------------------------------------
1535 Name : process_uif_ref_no
1536 Purpose : Process UIF Ref No
1537 Arguments : p_employer_uif_ref_no
1538 Notes : Should be zero filled to fit the field size A9. Slash should be
1539 left out if included in the number. e.g. 123456/8 should be
1540 sent as 001234568, e.g. of UIF Ref. No. 062441/0 or U120721099
1541 --------------------------------------------------------------------------*/
1542 function process_uif_ref_no
1543 (
1544 p_employer_uif_ref_no in varchar2
1545 ) return varchar2 is
1546
1547 l_length number;
1548 l_employer_uif_ref_no varchar2(11);
1549 l_temp_no varchar2(11) := '';
1550
1551 begin
1552
1553 hr_utility.set_location('Entering process_uif_ref_no', 10);
1554
1555 l_employer_uif_ref_no := p_employer_uif_ref_no;
1556
1557 hr_utility.set_location('l_employer_uif_ref_no = '|| l_employer_uif_ref_no, 20);
1558
1559 if l_employer_uif_ref_no is not null then
1560
1561 l_employer_uif_ref_no := ltrim(rtrim(l_employer_uif_ref_no));
1562
1563 hr_utility.set_location('l_employer_uif_ref_no = '|| l_employer_uif_ref_no, 25);
1564
1565 l_length := to_number(length(l_employer_uif_ref_no));
1566
1567 hr_utility.set_location('length_l_employer_uif_ref_no = '|| to_char(l_length), 30);
1568
1569 for i in 1 .. l_length
1570
1571 loop
1572
1573 if substr(l_employer_uif_ref_no, i, 1)
1574 in ('0','1','2','3','4','5','6','7','8','9')
1575 then
1576
1577 l_temp_no := l_temp_no || substr(l_employer_uif_ref_no, i, 1);
1578
1579 hr_utility.set_location('l_temp_no = '|| l_temp_no, 35);
1580
1581 end if;
1582
1583 end loop;
1584
1585 end if;
1586
1587 l_employer_uif_ref_no := ltrim(rtrim(l_temp_no));
1588
1589 hr_utility.set_location('l_employer_uif_ref_no_PROCESSED= '|| l_employer_uif_ref_no, 40);
1590
1591 l_length := to_number(length(l_employer_uif_ref_no));
1592
1593 if l_length > 9 then
1594
1595 l_employer_uif_ref_no := substr(l_employer_uif_ref_no, -9);
1596
1597 end if;
1598
1599 l_employer_uif_ref_no := lpad(l_employer_uif_ref_no, 9, '0');
1600
1601 hr_utility.set_location('l_employer_uif_ref_no_PROCESSED_zero_padded= '|| l_employer_uif_ref_no, 50);
1602
1603 return l_employer_uif_ref_no;
1604
1605 end process_uif_ref_no;
1606
1607
1608 /*--------------------------------------------------------------------------
1609 Name : get_parameter
1610 Purpose : Returns a legislative parameter
1611 Arguments :
1612 Notes : The legislative parameter field must be of the form:
1613 PARAMETER_NAME=PARAMETER_VALUE. No spaces is allowed in either
1614 the PARAMETER_NAME or the PARAMETER_VALUE.
1615 --------------------------------------------------------------------------*/
1616 function get_parameter
1617 (
1618 name in varchar2,
1619 parameter_list varchar2
1620 ) return varchar2 is
1621
1622 start_ptr number;
1623 end_ptr number;
1624 token_val pay_payroll_actions.legislative_parameters%type;
1625 par_value pay_payroll_actions.legislative_parameters%type;
1626
1627 begin
1628
1629 token_val := name || '=';
1630
1631 start_ptr := instr(parameter_list, token_val) + length(token_val);
1632 end_ptr := instr(parameter_list, ' ', start_ptr);
1633
1634 /* if there is no spaces, then use the length of the string */
1635 if end_ptr = 0 then
1636 end_ptr := length(parameter_list) + 1;
1637 end if;
1638
1639 /* Did we find the token */
1640 if instr(parameter_list, token_val) = 0 then
1641 par_value := NULL;
1642 else
1643 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
1644 end if;
1645
1646 return par_value;
1647
1648 end get_parameter;
1649
1650
1651
1652 function names(name varchar2) return varchar2 is
1653
1654 l_pos number;
1655 l_pos2 number;
1656 l_name varchar2(255);
1657 l_answer varchar2(255);
1658
1659 begin
1660
1661 -- Remove any unnecessary spaces
1662 l_name := ltrim(rtrim(name));
1663
1664 -- Get the first name
1665 l_pos := instr(l_name, ',', 1, 1);
1666 l_answer := rtrim(substr(l_name, 1, l_pos - 1));
1667
1668 -- Append the second name
1669 l_pos2 := instr(l_name, ',', l_pos + 1, 1);
1670 if l_pos2 = 0 then
1671
1672 -- Concatenate the rest of the string
1673 l_answer := l_answer || ' ' || ltrim(rtrim( substr(l_name, l_pos + 1) ));
1674
1675 else
1676
1677 -- Concatenate the name up to the comma
1678 l_answer := l_answer || ' ' || ltrim(rtrim( substr(l_name, l_pos + 1, l_pos2 - l_pos - 1) ));
1679
1680 end if;
1681
1682 l_answer := ltrim(rtrim(l_answer));
1683
1684 return l_answer;
1685
1686 end names;
1687
1688 function clean(name varchar2) return varchar2 is
1689
1690 l_invalid varchar2(255) := '&`''';
1691 l_answer varchar2(255);
1692 l_pos number;
1693 l_count number;
1694
1695 begin
1696
1697 l_answer := name;
1698
1699 if l_answer = '&&&,&&&' then
1700
1701 return '&&&';
1702
1703 else
1704
1705 -- Loop through the invalid characters
1706 for l_count in 1..length(l_invalid) loop
1707
1708 l_pos := instr(l_answer, substr(l_invalid, l_count, 1), 1, 1);
1709 while l_pos <> 0 loop
1710
1711 -- Replace the invalid character with a space
1712 l_answer := substr(l_answer, 1, l_pos - 1) || ' ' || substr(l_answer, l_pos + 1);
1713 l_pos := instr(l_answer, substr(l_invalid, l_count, 1), 1, 1);
1714
1715 end loop;
1716
1717 end loop;
1718
1719 return l_answer;
1720
1721 end if;
1722
1723 end;
1724
1725 function get_uif_employer_count return number is
1726 begin
1727
1728 return g_total_no_emps;
1729
1730 end;
1731
1732 function get_uif_total_gross_tax_rem return number is
1733 begin
1734
1735 return g_total_gross_tax_rem;
1736
1737 end;
1738
1739 /* Bug 2654703 */
1740 function get_uif_total_remu_sub_uif return number is
1741 begin
1742
1743 return g_total_remu_sub_uif;
1744
1745 end;
1746
1747 function get_uif_total_uif_contrib return number is
1748 begin
1749
1750 return g_total_uif_contribution;
1751
1752 end;
1753
1754
1755 function set_size
1756 (
1757 p_code in varchar2,
1758 p_type in varchar2,
1759 p_value in varchar2
1760 ) return varchar2 is
1761
1762 l_text varchar2(256);
1763 l_code varchar2(256);
1764 l_value varchar2(256);
1765
1766 begin
1767
1768 --hr_utility.trace_on(null, 'UIF');
1769
1770 l_code := p_code;
1771
1772 -- Remove any spaces
1773 l_value := rtrim(ltrim(p_value));
1774
1775 --Initialize globals
1776 if p_code = '0000' then
1777
1778 g_total_gross_tax_rem := 0;
1779 g_total_uif_contribution := 0;
1780
1781 /* Changes as per Bug 2654703 */
1782 g_total_remu_sub_uif := 0;
1783
1784 g_total_no_emps := 0;
1785
1786 end if;
1787
1788
1789 -- Check whether the Employer amounts and Count totals should be incremented
1790 if to_number(l_code) = 8300 then
1791
1792 g_total_gross_tax_rem := g_total_gross_tax_rem + to_number(l_value);
1793
1794 g_total_no_emps := g_total_no_emps + 1;
1795
1796 /* Changes as per Bug 2654703 */
1797 elsif to_number(l_code) = 8310 then
1798 g_total_remu_sub_uif := g_total_remu_sub_uif + to_number(l_value);
1799
1800 elsif to_number(l_code) = 8320 then
1801
1802 g_total_uif_contribution := g_total_uif_contribution + to_number(l_value);
1803
1804 end if;
1805
1806
1807 -- Check whether the Employer (Legal Entity) counts should be reset
1808 if l_code = '8130' then /*Employer Total Gross Taxable Remuneration*/
1809
1810 l_value := to_char(g_total_gross_tax_rem);
1811
1812 g_total_gross_tax_rem := 0;
1813
1814 /* Changes as per Bug 2654703 */
1815 elsif l_code = '8135' then /* Employer Total remuneration subject to UIF */
1816 l_value := to_char(g_total_remu_sub_uif);
1817 g_total_remu_sub_uif := 0;
1818
1819 elsif l_code = '8140' then /*Employer Total Contribution*/
1820
1821 l_value := to_char(g_total_uif_contribution);
1822
1823 g_total_uif_contribution := 0;
1824
1825 elsif l_code = '8150' then /*Employer Total Number of Employee Records*/
1826
1827 l_value := to_char(g_total_no_emps);
1828
1829 g_total_no_emps := 0;
1830
1831 end if;
1832
1833
1834 --Process field:
1835
1836 -- Check for empty fields
1837 if (l_value = '&&&') then
1838
1839 -- The field should be left out completely
1840 l_text := '';
1841
1842 -- Check for a record terminator field
1843 elsif (l_value = '@@@') then
1844
1845 l_text := fnd_global.local_chr(13) || fnd_global.local_chr(10);
1846
1847 -- A value field was provided
1848 else
1849
1850 -- Check for the start of a record
1851 if p_code in ('8000','8001','8002') then /* Bug 2654703 */
1852 l_text := p_code;
1853 else
1854 l_text := ',' || p_code;
1855 end if;
1856
1857 -- Append the value
1858 if p_type = 'N' then
1859
1860 --first take off decimal .00 if it exists
1861 if mod(to_number(l_value), trunc(to_number(l_value))) = 0 then
1862
1863 l_value := to_char(to_number(l_value));
1864
1865 end if;
1866
1867 l_text := l_text || ',' || l_value;
1868 else
1869 -- Add quotes if it is a character field
1870 l_text := l_text || ',"' || l_value || '"';
1871 end if;
1872
1873 end if;
1874
1875
1876 hr_utility.trace('DO(' || l_code || ',' || l_value || ',' || l_text || ')');
1877 --hr_utility.trace_off;
1878 return l_text;
1879
1880 end;
1881
1882 function za_power
1883 (
1884 p_number in number,
1885 p_power in number
1886 ) return number is
1887
1888 begin
1889
1890 return power(p_number, p_power);
1891
1892 end;
1893
1894 function za_to_char
1895 (
1896 p_number in number,
1897 p_format in varchar2
1898 ) return varchar2 is
1899
1900 begin
1901
1902 -- Check whether the Format parameter was defaulted
1903 if p_format = '&&&' then
1904
1905 return to_char(p_number);
1906
1907 else
1908
1909 return ltrim(to_char(p_number, p_format));
1910
1911 end if;
1912
1913 end;
1914
1915
1916 begin
1917
1918 prev_asg_id := 0;
1919 --g_size := 0;
1920
1921 end pay_za_uif_archive_pkg;