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