[Home] [Help]
PACKAGE BODY: APPS.PAY_ZA_EMP201
Source
1 PACKAGE BODY PAY_ZA_EMP201 AS
2 /* $Header: pyzae201.pkb 120.4.12020000.5 2013/02/05 10:38:54 abdash ship $ */
3 /* Copyright (c) Oracle Corporation 2005. All rights reserved. */
4 /*
5 PRODUCT
6 Oracle Payroll - ZA Localisation EMP201 Package
7
8 NAME
9 pay_za_emp201.pkb
10
11 DESCRIPTION
12 This is the ZA EMP201 package. It contains
13 functions and procedures used by EMP201 Report.
14
15 MODIFICATION HISTORY
16 Person Date Version Bug Comments
17 --------- ---------- ----------- ------- --------------------------------
18 P Arusia Initial Version
19 R Babla 10/06/2009 120.0 8512751 Updated Initial Version
20 R Babla 26/06/2009 120.1 8512751 Removed GSCC Errors
21 R Babla 26/06/2009 120.0.12010000.3 8512751 Modified the message in warning section.
22 Changed the archive_code to set l_cnt_paye_perm
23 as N if its seasonal worker with no remuneration
24 with EMP201 Status as Y
25 R Babla 01/09/2009 120.0.12010000.3 8859207 1.Changes done in cursor csr_asg_details to include
26 space between name
27 2. Changes done in the parameters passed to cursor
28 csr_check_asg_termination
29 BKeshary 07/12/2010 120.0.12010000.4 10376999 Changes to avoid impact of enabling skip
30 terminated asg leg rule.
31 A Dash 03/08/2012 120.0.12010000.6 14081001 Reverse run in a month should be reported in that month even
32 if it is a reversal of a payrun of a previous month.
33 asnell 10/12/2012 120.0.12010000.7 14543232 Time period based on actions effective date even if its a reversal
34 ABDASH 28/01/2013 120.0.12010000.8 16174886 Adding of legal entity parameter to ZA EMP201 Report.
35 ABDASH 05/02/2013 120.0.12010000.9 16174886 Adding of legal entity parameter to ZA EMP201 Report.
36 */
37
38 g_package constant varchar2(30) := 'pay_za_emp201.';
39 g_archive_effective_date date ;
40
41 -- -----------------------------------------------------------------------------
42 -- formatted_canonical
43 -- -----------------------------------------------------------------------------
44 -- This function converts varchar2 in decimal format
45 -- eg 0 is converted to 0.00
46 function formatted_canonical(
47 canonical varchar2)
48 return varchar2 is
49 decimal_char varchar2(1);
50 dummy varchar2(20);
51 begin
52 hr_utility.set_location('Entered canonical_to_number',20);
53 decimal_char := substr(ltrim(to_char(.3,'0D0')),2,1);
54 hr_utility.set_location('Done with decimal_char',20);
55 hr_utility.set_location('decimal_char:'||decimal_char,20);
56 return rtrim(ltrim(to_char(translate(canonical, '.', decimal_char),'999999999999999990D99')));
57 --return canonical;
58 end formatted_canonical;
59
60
61 -- -----------------------------------------------------------------------------
62 -- Get Parameters
63 -- -----------------------------------------------------------------------------
64 procedure get_parameters
65 (
66 p_payroll_action_id in number,
67 p_token_name in varchar2,
68 p_token_value out nocopy varchar2
69 ) is
70
71 cursor csr_parameter_info
72 (
73 p_pact_id number,
74 p_token char
75 ) is
76 select substr
77 (
78 legislative_parameters,
79 instr
80 (
81 legislative_parameters,
82 p_token
83 ) + (length(p_token) + 1),
84 instr
85 (
86 legislative_parameters,
87 ' ',
88 instr
89 (
90 legislative_parameters,
91 p_token
92 )
93 )
94 -
95 (
96 instr
97 (
98 legislative_parameters,
99 p_token
100 ) + length(p_token)
101 )
102 ),
103 business_group_id
104 from pay_payroll_actions
105 where payroll_action_id = p_pact_id;
106
107 l_business_group_id number;
108 l_proc varchar2(50) := g_package || 'get_parameters';
109
110 begin
111
112 hr_utility.set_location('Entering ' || l_proc, 10);
113
114 hr_utility.set_location('Step ' || l_proc, 20);
115 hr_utility.set_location('p_token_name = ' || p_token_name, 20);
116
117 open csr_parameter_info
118 (
119 p_payroll_action_id,
120 p_token_name
121 );
122 fetch csr_parameter_info into p_token_value, l_business_group_id;
123 close csr_parameter_info;
124
125 if p_token_name = 'BG_ID' then
126 p_token_value := l_business_group_id;
127 end if ;
128
129 hr_utility.set_location('p_token_value = ' || p_token_value, 20);
130 hr_utility.set_location('Leaving ' || l_proc, 30);
131 --
132 exception
133 when others then
134 p_token_value := null;
135 --
136 end get_parameters;
137
138 function get_parameter
139 (
140 name in varchar2,
141 parameter_list varchar2
142 ) return varchar2 is
143
144 start_ptr number;
145 end_ptr number;
146 token_val pay_payroll_actions.legislative_parameters%type;
147 par_value pay_payroll_actions.legislative_parameters%type;
148
149 begin
150
151 token_val := name || '=';
152
153 start_ptr := instr(parameter_list, token_val) + length(token_val);
154 end_ptr := instr(parameter_list, ' ', start_ptr);
155
156 /* if there is no spaces, then use the length of the string */
157 if end_ptr = 0 then
158 end_ptr := length(parameter_list) + 1;
159 end if;
160
161 /* Did we find the token */
162 if instr(parameter_list, token_val) = 0 then
163 par_value := NULL;
164 else
165 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
166 end if;
167
168 return par_value;
169
170 end get_parameter;
171
172 --
173 --
174 --
175 -- -----------------------------------------------------------------------------
176 -- Range Cursor
177 -- -----------------------------------------------------------------------------
178 --
179
180 procedure range_cursor
181 (
182 pactid in number,
183 sqlstr out nocopy varchar2
184 ) is
185 -- l_payroll_id number ;
186 l_legal_entity_id number;
187 l_proc varchar2(50) := g_package || 'range_cursor';
188
189 begin
190 hr_utility.set_location('Entering ' || l_proc, 10);
191
192 -- Bug 16174886
193 -- Leg Entity param added to CP
194
195 -- Payroll ID is not required now.
196 -- Retrieve the Payroll_ID from legislative parameters from the payroll action
197 /*
198 get_parameters
199 (
200 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
201 p_token_name => 'PAYROLL_ID',
202 p_token_value => l_payroll_id
203 );
204
205 -- Update the payroll_id column on the Payroll_Action record.
206 update pay_payroll_actions
207 set payroll_id = l_payroll_id
208 where payroll_action_id = pactid;
209 */
210 -- Retrieve the LEGAL_ENTITY_ID from legislative parameters from the payroll action
211 get_parameters
212 (
213 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
214 p_token_name => 'LEGAL_ENTITY',
215 p_token_value => l_legal_entity_id
216 );
217
218 -- Commented out as the range cursor will use Leg Entity instead
219
220 /*
221 sqlstr :=
222 'select distinct ass.person_id
223 from per_assignments_f ass,
224 pay_payrolls_f ppf,
225 pay_payroll_actions ppa
226 where ppa.payroll_action_id = :payroll_action_id
227 and ass.business_group_id = ppa.business_group_id
228 and ass.assignment_type = ''E''
229 and ppf.payroll_id = ass.payroll_id
230 and ppf.payroll_id = ppa.payroll_id
231 order by ass.person_id';
232 */
233 sqlstr :=
234 'select distinct paf.person_id
235 from per_assignments_f paf,
236 pay_payrolls_f ppf,
237 pay_payroll_actions ppa ,
238 per_assignment_extra_info aei
239 where ppa.payroll_action_id = :payroll_action_id
240 and paf.business_group_id = ppa.business_group_id
241 and paf.assignment_type = ''E''
242 and ppf.payroll_id = paf.payroll_id
243 and aei.assignment_id = paf.assignment_id
244 and aei.aei_information_category = ''ZA_SPECIFIC_INFO''
245 and aei.aei_information7 = to_char('||l_legal_entity_id||')
246 order by paf.person_id';
247
248
249 -- Bug 16174886
250 hr_utility.set_location('Leaving ' || l_proc, 10);
251
252 --hr_utility.trace_off;
253 --
254 exception
255 when others then
256 sqlstr := null;
257 --
258 end range_cursor;
259
260
261 --
262 --
263 -- -----------------------------------------------------------------------------
264 -- Archinit code
265 -- -----------------------------------------------------------------------------
266 --
267 -- Archive legal entity level information here
268 procedure archinit
269 (
270 p_payroll_action_id in number
271 ) is
272
273 cursor csr_archive_effective_date(pactid number) is
274 select effective_date
275 from pay_payroll_actions
276 where payroll_action_id = pactid;
277
278 -- Bug 16174886
279 -- payroll name no longer required
280 /*
281 CURSOR get_payroll_name (p_payroll_id number, l_effective_date date)IS
282 select payroll_name
283 from pay_all_payrolls_f
284 where payroll_id = p_payroll_id
285 and l_effective_date between effective_start_date and effective_end_date;
286 */
287 CURSOR get_legal_entity_name(p_organization_id number)IS
288 select haou.name
289 from hr_all_organization_units haou,
290 hr_organization_information hoi,
291 hr_organization_information hoi2
292 where haou.organization_id = p_organization_id
293 and hoi.organization_id = haou.organization_id
294 and hoi.org_information_context = 'ZA_LEGAL_ENTITY'
295 and hoi2.organization_id = haou.organization_id
296 and hoi2.org_information_context = 'CLASS'
297 and hoi2.org_information1 = 'HR_LEGAL'
298 and hoi2.org_information2 = 'Y' ;
299 -- Bug 16174886
300
301 l_action_info_id number;
302 l_ovn number;
303 -- l_payroll_id number;
304 l_effective_date date;
305 -- l_payroll_name varchar2(80);
306 l_calendar_month varchar2(30);
307 l_payroll_prd varchar2(30);
308 l_legal_entity_id number;
309 l_legal_entity_name varchar2(240);
310 l_proc varchar2(50) := g_package || 'arch_init';
311
312 begin
313 hr_utility.set_location('Entering ' || l_proc, 10);
314
315 -- Get the effective date of the payroll action
316 open csr_archive_effective_date(p_payroll_action_id); -- Payroll Action of the Archiver
317 fetch csr_archive_effective_date
318 into g_archive_effective_date;
319 close csr_archive_effective_date;
320
321 -- Bug 16174886
322 -- payroll_id and payroll_name no longer required.
323 /*
324 -- Retrieve the Payroll_ID from legislative parameters from the payroll action
325 get_parameters
326 (
327 p_payroll_action_id => p_payroll_action_id, -- Payroll Action of the Archiver
328 p_token_name => 'PAYROLL_ID',
329 p_token_value => l_payroll_id
330 );
331 */
332 -- Bug 16174886
333
334 -- Retrieve the Calendar_Month from legislative parameters from the payroll action
335 get_parameters
336 (
337 p_payroll_action_id => p_payroll_action_id, -- Payroll Action of the Archiver
338 p_token_name => 'CALENDAR_MONTH',
339 p_token_value => l_calendar_month
340 );
341
342 l_effective_date := fnd_date.canonical_to_date(l_calendar_month);
343
344
345 -- Bug 16174886
346 -- Retrieve the Legal_Entity from legislative parameters from the payroll action
347 get_parameters
348 (
349 p_payroll_action_id => p_payroll_action_id, -- Payroll Action of the Archiver
350 p_token_name => 'LEGAL_ENTITY',
351 p_token_value => l_legal_entity_id
352 );
353
354 open get_legal_entity_name(l_legal_entity_id);
355 fetch get_legal_entity_name into l_legal_entity_name ;
356 close get_legal_entity_name ;
357
358 -- Payroll Name no longer required.
359 /*
360 open get_payroll_name(l_payroll_id, l_effective_date);
361 fetch get_payroll_name into l_payroll_name ;
362 close get_payroll_name ;
363 */
364 -- Bug 16174886
365 -- Convert calendar month to 'Mon YYYY' format, for eg 'Feb 2008'
366 l_payroll_prd := to_char(to_date(l_calendar_month,'RRRR/MM/DD HH24:MI:SS'),'Mon YYYY');
367
368 -- Archive 'ZA EMP201 LEGAL ENTITY DETAILS'
369 pay_action_information_api.create_action_information
370 (
371 p_action_information_id => l_action_info_id,
372 p_action_context_id => p_payroll_action_id,
373 p_action_context_type => 'PA',
374 p_object_version_number => l_ovn,
375 p_effective_date => g_archive_effective_date,
376 p_action_information_category => 'ZA EMP201 LEGAL ENTITY DETAILS',
377 p_action_information1 => l_legal_entity_id,
378 p_action_information2 => l_legal_entity_name,
379 p_action_information3 => l_payroll_prd
380 );
381 hr_utility.set_location('Leaving ' || l_proc, 10);
382 end archinit;
383
384
385 --
386 --
387 -- -----------------------------------------------------------------------------
388 -- Assignment Action Creation
389 -- -----------------------------------------------------------------------------
390 --
391 procedure action_creation
392 (
393 pactid in number,
394 stperson in number,
395 endperson in number,
396 chunk in number
397 ) is
398
399 -- pick up all assignments within this person range,
400 -- belonging to this payroll and business group
401 -- (as per the last person/assignment record before this month end )
402 -- which are
403 -- a) not terminated (or)
404 -- b) terminated but
405 -- i) termination starts after this month end (or)
406 -- ii) termination happened within this month
407 -- iii) action_termination_date was before month start
408 -- but final_process_date is still left
409
410 cursor csr_get_asg (p_pactid number
411 , p_stperson number
412 , p_endperson number
413 , p_canonical_start_date date
414 , p_canonical_end_date date
415 , p_legal_entity_id number
416 , p_payroll_id number) is
417 select ppf.person_id
418 , paa.assignment_id
419 from per_all_people_f ppf
420 , per_all_assignments_f paa
421 , pay_payroll_actions ppa
422 , per_periods_of_service pps
423 , per_assignment_extra_info aei
424 where ppf.person_id between p_stperson and p_endperson
425 and paa.person_id = ppf.person_id
426 and paa.business_group_id = ppa.business_group_id
427 and ppa.payroll_action_id = p_pactid
428 and aei.assignment_id = paa.assignment_id
429 and aei.aei_information_category = 'ZA_SPECIFIC_INFO'
430 and aei.aei_information7 = to_char(p_legal_entity_id)
431 and paa.payroll_id = nvl(p_payroll_id, paa.payroll_id)
432 and paa.period_of_service_id = pps.period_of_service_id
433 -- last person record before this month end
434 and ppf.effective_start_date = ( select max(effective_start_date)
435 from per_all_people_f ppf1
436 where ppf1.person_id = ppf.person_id
437 and ppf1.effective_start_date <= p_canonical_end_date
438 )
439 -- last assignment record before this month end
440 and paa.effective_start_date = ( select max(paa1.effective_start_date)
441 from per_all_assignments_f paa1 where paa1.assignment_id = paa.assignment_id
442 and paa1.effective_start_date <= p_canonical_end_date
443 )
444 and
445 (
446 pps.actual_termination_date is null -- employee is not terminated
447 or -- (or)
448 (
449 pps.actual_termination_date is not null -- employee is terminated but
450 and
451 (
452 pps.actual_termination_date > p_canonical_end_date -- 1) termination is after this month end (or)
453 or
454 pps.actual_termination_date between p_canonical_start_date and p_canonical_end_date -- 2) termination is within this month (or)
455 or
456 (
457 pps.actual_termination_date < p_canonical_start_date -- 3) termination happened before month start (but) final_process_date is after month start
458 and nvl(pps.final_process_date,to_date('31-12-4712','DD-MM-YYYY')) >= p_canonical_start_date
459 )
460
461 )
462 )
463 )
464 order by 2
465 for update of paa.assignment_id;
466
467 l_calendar_month varchar(30);
468 l_month_start date ;
469 l_month_end date ;
470 l_asg_set_id number;
471 v_incl_sw hr_assignment_set_amendments.include_or_exclude%type;
472 asg_include boolean;
473 prev_asg_id number := 0 ;
474 lockingactid number;
475 l_legal_entity_id number;
476 l_payroll_id number := null;
477
478 l_proc varchar2(50) := g_package || 'action_creation';
479 begin
480 -- Retrieve the Calendar Month from legislative parameters from the payroll action
481 hr_utility.set_location('pactid '||to_char(pactid), 10);
482
483 hr_utility.set_location('stperson '||to_char(stperson), 10);
484 hr_utility.set_location('endperson '||to_char(endperson), 10);
485
486 get_parameters
487 (
488 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
489 p_token_name => 'CALENDAR_MONTH',
490 p_token_value => l_calendar_month
491 );
492
493 hr_utility.set_location('l_calendar_month '||l_calendar_month, 10);
494
495 l_month_start := add_months(fnd_date.canonical_to_date(l_calendar_month), -1)+1;
496 l_month_end := fnd_date.canonical_to_date(l_calendar_month) ;
497
498 hr_utility.set_location('l_month_start '||to_char(l_month_start, 'dd/mm/yyyy'), 10);
499 hr_utility.set_location('l_month_end '||to_char(l_month_end, 'dd/mm/yyyy'), 10);
500
501 -- Retrieve the Calendar Month from legislative parameters from the payroll action
502 get_parameters
503 (
504 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
505 p_token_name => 'ASG_SET_ID',
506 p_token_value => l_asg_set_id
507 );
508
509 hr_utility.set_location('l_asg_set_id '||to_char(l_asg_set_id), 10);
510
511 -- Bug 16174886
512
513 -- Retrieve the Payroll_ID from legislative parameters from the payroll action
514 get_parameters
515 (
516 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
517 p_token_name => 'PAYROLL_ID',
518 p_token_value => l_payroll_id
519 );
520
521 -- Retrieve the LEGAL_ENTITY_ID from legislative parameters from the payroll action
522 get_parameters
523 (
524 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
525 p_token_name => 'LEGAL_ENTITY',
526 p_token_value => l_legal_entity_id
527 );
528
529 -- Bug 16174886
530
531 hr_utility.set_location('l_legal_entity_id '||to_char(l_legal_entity_id), 10);
532 hr_utility.set_location('l_payroll_id '||to_char(l_payroll_id), 10);
533
534 if l_asg_set_id is not null then
535 -- find out if assignments in assignment-set are set to Include or Exclude.
536 begin
537 select distinct include_or_exclude
538 into v_incl_sw
539 from hr_assignment_set_amendments
540 where assignment_set_id = l_asg_set_id;
541 exception
542 when no_data_found then
543 -- default to Include, should not go here though.
544 v_incl_sw := 'I';
545 end;
546 end if;
547
548 hr_utility.set_location('Before csr_get_asg', 20);
549
550 for asgrec in csr_get_asg ( pactid, stperson, endperson, l_month_start, l_month_end, l_legal_entity_id, l_payroll_id)
551 loop
552 hr_utility.set_location('ASS_ID: ' || to_char(asgrec.assignment_id), 30);
553 asg_include := TRUE;
554
555 -- Remove duplicate assignments
556 if prev_asg_id <> asgrec.assignment_id then
557 prev_asg_id := asgrec.assignment_id;
558 if l_asg_set_id is not null then
559 declare
560 inc_flag varchar2(5);
561 begin
562 select include_or_exclude
563 into inc_flag
564 from hr_assignment_set_amendments
565 where assignment_set_id = l_asg_set_id
566 and assignment_id = asgrec.assignment_id;
567
568 if inc_flag = 'E' then
569 asg_include := FALSE;
570 end if;
571 exception
572 -- goes through this exception, for each assignment in the payroll
573 -- but not in the relevant assignment_set.
574 when no_data_found then
575 if v_incl_sw = 'I' then
576 asg_include := FALSE;
577 else
578 asg_include := TRUE;
579 end if;
580 end ;
581 end if; -- end of l_asg_set_id is not null
582
583 if asg_include = TRUE then
584 select pay_assignment_actions_s.nextval
585 into lockingactid
586 from dual;
587
588 -- Insert assignment into pay_assignment_actions
589 hr_nonrun_asact.insact
590 (
591 lockingactid,
592 asgrec.assignment_id,
593 pactid,
594 chunk,
595 null
596 );
597 end if; --end of if asg_include = TRUE
598 end if; -- end of ( if not duplicate assignment )
599 end loop ;
600
601 hr_utility.set_location('Leaving ' || l_proc, 30);
602
603 --hr_utility.trace_off;
604
605 end action_creation;
606
607 --
608 -- -----------------------------------------------------------------------------
609 -- Archive Data
610 -- -----------------------------------------------------------------------------
611 --
612
613 procedure archive_data
614 (
615 p_assactid in number,
616 p_archive_effective_date in date
617 ) is
618
619 -- Retrieve the employee details
620 cursor csr_asg_details (l_eff_date date)is
621 select p.employee_number
622 ,p.title ||' '|| p.first_name ||' '|| p.last_name emp_name
623 ,a.assignment_id
624 ,a.period_of_service_id
625 ,a.assignment_number
626 ,a.payroll_id
627 from per_all_people_f p
628 , per_all_assignments_f a
629 , pay_assignment_actions paa
630 where paa.assignment_action_id = p_assactid
631 and a.assignment_id = paa.assignment_id
632 and p.person_id = a.person_id
633 and l_eff_date between p.effective_start_date and p.effective_end_date
634 and l_eff_date between a.effective_start_date and a.effective_end_date ;
635
636 -- EMP201 Status logic -
637 -- a) If EMP201_Status is provided (aei_information12), use it
638 -- b) else, if Employment Equity Status is provided (aei_information11), use it
639 -- c) else, use the Employment Equity Status defaulting logic
640 -- (per_za_employment_equity_pkg.get_ee_employment_type_name),
641 -- which says that employee has to work for a continuous period of
642 -- 3 months in order to be seen as permanent
643
644 cursor csr_asg_specific_info_dtls (p_assignment_id number
645 , p_effective_date date
646 , p_period_of_service_id number) is
647 select hr_general.decode_lookup('ZA_PER_NATURES',aei.aei_information4) nature,
648 nvl(aei.aei_information6,'N') independent_contractor,
649 nvl(aei.aei_information10,'N') labour_broker,
650 decode(aei.aei_information12,
651 'P','Permanent',
652 'N','Non-Permanent',
653 nvl(decode(aei.aei_information11,
654 'P','Permanent',
655 'N','Non-Permanent'),
656 per_za_employment_equity_pkg.get_ee_employment_type_name(p_effective_date
657 , p_period_of_service_id))) EMP201_status
658 from per_assignment_extra_info aei
659 where aei.assignment_id = p_assignment_id
660 and aei.information_type = 'ZA_SPECIFIC_INFO' ;
661
662 --Retrieve the employee's UIF Information
663 cursor csr_asg_uif_info_dtls (p_assignment_id number) is
664 select aei.aei_information1 reason_for_non_contrib
665 from per_assignment_extra_info aei
666 where aei.assignment_id = p_assignment_id
667 and aei.information_type = 'ZA_UIF_INFO' ;
668
669 -- select the payroll_action_id of the last payroll run
670 -- whose pay_advice_date falls in the calendar month
671 cursor csr_payroll_action (p_assignment_id number,
672 l_effective_date date) is
673 select ppa.payroll_action_id
674 , ppa.payroll_id
675 , ptp.end_date
676 from pay_assignment_actions paa,
677 pay_payroll_actions ppa,
678 per_time_periods ptp
679 where paa.assignment_id = p_assignment_id
680 and paa.payroll_action_id = ppa.payroll_action_id
681 and ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
682 and paa.action_status IN ('C','S') -- 10376999
683 and ptp.payroll_id = ppa.payroll_id
684 and ppa.date_earned between ptp.start_date and ptp.end_date
685 and ptp.pay_advice_date between trunc(l_effective_date,'Month') and l_effective_date
686 and paa.action_sequence = (select max(paa1.action_sequence)
687 from pay_assignment_actions paa1,
688 pay_payroll_actions ppa1,
689 per_time_periods ptp1
690 where paa1.assignment_id = p_assignment_id
691 and paa1.payroll_action_id = ppa1.payroll_action_id
692 and ppa1.action_type IN ('R', 'Q', 'V', 'B', 'I')
693 and paa1.action_status IN ('C','S') -- 10376999
694 and ptp1.payroll_id = ppa1.payroll_id
695 and ppa1.date_earned between ptp1.start_date and ptp1.end_date
696 and ptp1.pay_advice_date between trunc(l_effective_date,'Month') and l_effective_date
697 ) ;
698
699 --Check whether the employee is SDL Exempt or Non-Exempt
700 --If the SDL is set to Exempt at assignment extra information, then the employee is exempt
701 --else its considered the one set at organisation level
702 cursor get_sdl_exemption (p_assignment_id number
703 , p_effective_date date) is
704 select decode(hoi.org_information1,'Exempt','E',nvl(aei.aei_information9,'N')) "Exemption"
705 from per_all_assignments_f ass
706 , hr_organization_information hoi
707 , per_assignment_extra_info aei
708 where ass.assignment_id = p_assignment_id
709 and p_effective_date between ass.effective_start_date and ass.effective_end_date
710 and ass.organization_id = hoi.organization_id
711 and hoi.org_information_context = 'ZA_NQF_SETA_INFO'
712 and aei.assignment_id = ass.assignment_id
713 and aei.information_type = 'ZA_SPECIFIC_INFO' ;
714
715 --Used in UIF Calculation to retrieve the period limit
716 --Its same query used for route ZA_PAY_MONTH_PERIOD_NUMBER
717 cursor csr_za_pay_mnth_prd_num (p_payroll_action_id number
718 , p_payroll_id number) is
719 select count(ptp.end_date)
720 from per_time_periods ptp
721 where ptp.pay_advice_date =
722 (select tperiod.pay_advice_date
723 from per_time_periods tperiod,
724 pay_payroll_actions paction
725 where paction.payroll_action_id = p_payroll_action_id
726 and tperiod.time_period_id = paction.time_period_id
727 )
728 and ptp.end_date <=
729 (select tperiod.end_date
730 from per_time_periods tperiod,
731 pay_payroll_actions paction
732 where paction.payroll_action_id = p_payroll_action_id
733 and tperiod.time_period_id = paction.time_period_id
734 )
735 and ptp.payroll_id = p_payroll_id;
736
737 --Used in UIF Calculation to retrieve the period limit
738 --Its same query used for route ZA_PAY_PERIODS_PER_YEAR
739 cursor csr_pay_prd_per_yr (p_payroll_action_id number
740 , p_payroll_id number) is
741 select count(ptp.end_date)
742 from per_time_periods PTP
743 where ptp.prd_information1 =
744 (select tperiod.prd_information1
745 from per_time_periods tperiod,
746 pay_payroll_actions paction
747 where paction.payroll_action_id = p_payroll_action_id
748 and tperiod.time_period_id = paction.time_period_id)
749 and ptp.payroll_id = p_payroll_id;
750
751 --Retrieve the value of the global
752 cursor csr_global_value (p_global_name VARCHAR2
753 , p_effective_date date) is
754 select global_value
755 from ff_globals_f
756 where global_name = p_global_name
757 and p_effective_date between effective_start_date
758 and effective_end_date
759 and legislation_code = 'ZA';
760
761
762 --Retrive the Tax Status from Run results
763 cursor csr_tax_status (p_payroll_action_id number ) is
764 select prrv.result_value
765 from pay_payroll_actions ppa
766 , pay_assignment_actions paa
767 , pay_element_types_f pet
768 , pay_input_values_f piv
769 , pay_run_results prr
770 , pay_run_result_values prrv
771 where ppa.payroll_action_id = p_payroll_action_id
772 and paa.payroll_action_id = ppa.payroll_action_id
773 and pet.element_name = 'ZA_Tax'
774 and piv.element_type_id = pet.element_type_id
775 and piv.name = 'Tax Status'
776 and prr.element_type_id = pet.element_type_id
777 and prr.assignment_action_id = paa.assignment_action_id
778 and prrv.run_result_id = prr.run_result_id
779 and prrv.input_value_id = piv.input_value_id
780 and ppa.effective_date between pet.effective_start_date and pet.effective_end_date
781 and ppa.effective_date between piv.effective_start_date and piv.effective_end_date ;
782
783 --Retrieve the Tax Status from element entry
784 cursor csr_tax_status_from_ele_entry (p_assignment_id number
785 , p_effective_date date) is
786 select peev.screen_entry_value
787 from pay_element_entry_values_f peev
788 , pay_element_entries_f peef
789 , pay_element_types_f pet
790 , pay_input_values_f piv
791 where
792 pet.legislation_code = 'ZA'
793 and pet.element_name = 'ZA_Tax'
794 and piv.element_type_id = pet.element_type_id
795 and piv.name = 'Tax Status'
796 and peef.assignment_id = p_assignment_id
797 and peef.element_type_id = pet.element_type_id
798 and peev.element_entry_id = peef.element_entry_id
799 and peev.input_value_id = piv.input_value_id
800 and p_effective_date between pet.effective_start_date and pet.effective_end_date
801 and p_effective_date between piv.effective_start_date and piv.effective_end_date
802 and p_effective_date between peef.effective_start_date and peef.effective_end_date
803 and p_effective_date between peev.effective_start_date and peev.effective_end_date ;
804
805 --Returns Y is assignment is terminated, else N
806 cursor csr_check_asg_termination (p_assignment_id number
807 , p_effective_date date) is
808 select decode (past.PER_SYSTEM_STATUS, 'TERM_ASSIGN','Y','N') asg_terminated
809 from per_all_assignments_f paa,
810 per_assignment_status_types past
811 where paa.assignment_id = p_assignment_id
812 and paa.assignment_status_type_id = past.assignment_status_type_id
813 and p_effective_date between paa.effective_start_date and paa.effective_end_date ;
814
815 -- Bug 14081001
816 cursor csr_get_rr_aa_id (p_assignment_id number,
817 p_month_end date,
818 p_payroll_id number)
819 is
820 select paa.assignment_action_id assignment_action_id
821 from pay_assignment_actions paa,
822 pay_payroll_actions ppa,
823 per_time_periods ptp
824 where paa.assignment_id = p_assignment_id
825 and paa.action_status IN ('C','S')
826 and paa.payroll_action_id = ppa.payroll_action_id
827 and ppa.action_type = 'V'
828 and ppa.action_status = 'C'
829 and ppa.payroll_id = p_payroll_id
830 -- bug 14543232 use time period spanning effective_date
831 -- and ppa.time_period_id = ptp.time_period_id
832 and ptp.payroll_id = p_payroll_id
833 and ppa.effective_date between ptp.start_date and ptp.end_date
834 and ptp.end_date <= p_month_end
835 and ptp.end_date >= trunc(p_month_end, 'mm') -- start of the month
836 and ppa.effective_date <> ppa.date_earned;
837
838 cursor csr_get_balance_data
839 is
840 select balance_type_id
841 ,balance_name
842 from pay_balance_types
843 where legislation_code = 'ZA'
844 and balance_name in (
845 'Tax'
846 ,'Net PAYE Taxable Income'
847 ,'Net Taxable Income'
848 ,'Skills Levy'
849 ,'UIF Employee Contribution'
850 ,'UIF Employer Contribution'
851 ,'Total UIFable Income'
852 ,'Gross Remuneration'
853 );
854 -- Bug 14081001
855
856 l_pactid_archive number ;
857 l_calendar_month varchar2(30);
858 l_month_end date ;
859 l_effective_date date ;
860 l_asg_end_date date :=null;
861 l_last_run_payroll_action_id number ;
862 l_payroll_id number ;
863
864 l_ass_payroll_id number;
865
866 l_proc varchar2(50) := g_package || 'archive_data';
867 l_employee_number varchar2(30);
868 l_assignment_no varchar2(30);
869 l_emp_name varchar2(350);
870 l_assignment_id number ;
871 l_period_of_service_id number ;
872 l_nature_of_person varchar2(1);
873 l_independent_contractor varchar2(1);
874 l_labour_broker varchar2(1);
875 l_EMP201_status varchar2 (20);
876 l_reason_for_uif_non_contrib varchar2(3);
877
878 l_tax_dim_mtd number;
879 l_paye_remuneration number;
880 l_paye_rem_dim_mtd number;
881 l_tax number;
882 l_sdl_exemption varchar2(1);
883 l_net_taxable_inc_dim_mtd number ;
884 l_skills_levy_dim_mtd number ;
885 l_leviable_amt number;
886 l_sdl_amt number ;
887 l_temp_emp_hours_dim_mtd number;
888 l_temp_emp_hours number;
889 l_za_pay_mnth_prd_num number ;
890 l_pay_prd_per_yr number ;
891 l_uif_ee_dim_mtd number ;
892 l_uif_er_dim_mtd number ;
893 l_uifable_income_dim_mtd number ;
894 l_uif_ee_contr number;
895 l_uif_er_contr number;
896 l_uif_amt number ;
897 l_uif_remuneration number ;
898 l_UIF_Annual_Limit number ;
899 l_UIF_period_limit number ;
900
901 l_tax_status varchar2(2);
902 l_gross_remun number ;
903 l_gross_remun_dim_mtd number;
904 l_seasonal_not_paid_flag boolean := false ;
905 l_cnt_paye_perm varchar2(1) ;
906 l_cnt_paye_non_perm varchar2(1);
907
908 l_asg_terminated varchar2(1);
909 l_site_dim_ytd number;
910 l_paye_dim_ytd number ;
911 l_tax_dim_ytd number;
912 l_tax_ytd number;
913 l_site number ;
914 l_paye number;
915 l_raise_warning varchar2(1) ;
916
917 l_action_info_id number ;
918 l_ovn number ;
919
920 l_pay_effective_date date ;
921
922 -- Bug 14081001
923 l_loop_count NUMBER;
924 l_context_lst pay_balance_pkg.t_context_tab; -- used for batch balance retrieval
925 l_output_table pay_balance_pkg.t_detailed_bal_out_tab; -- output of batch balance retrieval
926 l_defined_balance_lst pay_balance_pkg.t_balance_value_tab; -- used for batch balance retrieval
927
928 TYPE t_asg_act_id_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
929
930 TYPE t_net_bal_rec is record
931 ( defined_balance_id number
932 , bal_value number
933 );
934
935 TYPE t_net_bal_table IS TABLE OF t_net_bal_rec INDEX BY VARCHAR2(200);
936
937 TYPE t_bal_name_table is table of VARCHAR2(200) index by BINARY_INTEGER;
938
939 l_asg_act_id_table t_asg_act_id_table;
940 l_net_bal_table t_net_bal_table;
941 l_bal_name_table t_bal_name_table;
942 -- Bug 14081001
943
944 begin
945 -- hr_utility.trace_on(null, 'ZA_EMP201');
946 -- Bug 14081001
947 l_bal_name_table.delete;
948 l_net_bal_table.delete;
949 l_asg_act_id_table.delete;
950 l_context_lst.delete;
951 l_output_table.delete;
952 l_defined_balance_lst.delete;
953 -- Bug 14081001
954 hr_utility.set_location('Entering ' || l_proc, 10);
955
956 --get the Archive Effective Date
957 select ppa.payroll_action_id
958 into l_pactid_archive
959 from pay_payroll_actions ppa,
960 pay_assignment_actions paa
961 where paa.payroll_action_id = ppa.payroll_action_id
962 and paa.assignment_action_id = p_assactid;
963
964 -- Retrieve the Calendar Month from legislative parameters from the payroll action
965 get_parameters
966 (
967 p_payroll_action_id => l_pactid_archive, -- Payroll Action of the Archiver
968 p_token_name => 'CALENDAR_MONTH',
969 p_token_value => l_calendar_month
970 );
971
972 l_month_end := fnd_date.canonical_to_date(l_calendar_month) ;
973
974 l_effective_date := l_month_end ;
975
976 -- Retrieve the employee details
977 open csr_asg_details (l_effective_date);
978 fetch csr_asg_details into l_employee_number
979 , l_emp_name
980 , l_assignment_id
981 , l_period_of_service_id
982 , l_assignment_no
983 , l_ass_payroll_id;
984 if csr_asg_details%NOTFOUND then
985 hr_utility.set_location('csr_asg_details not found',10);
986 select max(effective_end_date)
987 into l_asg_end_date
988 from per_all_assignments_f paf,
989 pay_assignment_actions paa
990 where effective_end_date <= l_month_end
991 and paa.assignment_id = paf.assignment_id
992 and paa.assignment_action_id = p_assactid;
993
994 -- hr_utility.set_location('l_asg_end_date: '||to_char('l_asg_end_date','dd-mon-yyyy'),10);
995 hr_utility.set_location('Found csr_asg_details',10);
996
997 close csr_asg_details ;
998
999 open csr_asg_details (l_asg_end_date);
1000 fetch csr_asg_details into l_employee_number
1001 , l_emp_name
1002 , l_assignment_id
1003 , l_period_of_service_id
1004 , l_assignment_no
1005 , l_ass_payroll_id;
1006 end if ;
1007 close csr_asg_details ;
1008
1009 hr_utility.set_location(l_proc, 20);
1010
1011 --Retrieve the ZA_SPECIFIC_INFO details
1012 open csr_asg_specific_info_dtls (l_assignment_id, nvl(l_asg_end_date,l_effective_date), l_period_of_service_id);
1013 fetch csr_asg_specific_info_dtls into l_nature_of_person
1014 , l_independent_contractor
1015 , l_labour_broker
1016 , l_EMP201_status ;
1017 if csr_asg_specific_info_dtls%NOTFOUND then
1018 hr_utility.set_location(l_proc, 30);
1019 l_nature_of_person := 'A' ;
1020 l_independent_contractor := 'N' ;
1021 l_labour_broker := 'N';
1022 l_EMP201_status := per_za_employment_equity_pkg.get_ee_employment_type_name(l_effective_date, l_period_of_service_id);
1023 end if ;
1024 close csr_asg_specific_info_dtls ;
1025
1026 hr_utility.set_location(l_proc, 40);
1027
1028 --Retrieve the UIF details
1029 open csr_asg_uif_info_dtls(l_assignment_id) ;
1030 fetch csr_asg_uif_info_dtls into l_reason_for_uif_non_contrib ;
1031 close csr_asg_uif_info_dtls ;
1032
1033 hr_utility.set_location(l_proc, 50);
1034
1035 --Retrieve the last payroll run for this employee in the current Month (Month for which archival is run)
1036 open csr_payroll_action (l_assignment_id, l_effective_date);
1037 fetch csr_payroll_action into l_last_run_payroll_action_id
1038 , l_payroll_id
1039 , l_pay_effective_date;
1040 if csr_payroll_action%NOTFOUND then
1041 hr_utility.trace('Payroll Not Run this month');
1042 hr_utility.set_location(l_proc, 60);
1043 l_last_run_payroll_action_id := null ;
1044 end if ;
1045 close csr_payroll_action ;
1046
1047 hr_utility.set_location(l_proc, 70);
1048
1049 hr_utility.trace('Assignment_id : '||l_assignment_id);
1050 hr_utility.trace('l_nature_of_person : '||l_nature_of_person);
1051 hr_utility.trace('l_independent_contractor : '||l_independent_contractor);
1052 hr_utility.trace('l_labour_broker : '||l_labour_broker);
1053 hr_utility.trace('l_EMP201_status : '||l_EMP201_status);
1054 hr_utility.trace('l_reason_for_non_contrib : '||l_reason_for_uif_non_contrib);
1055 hr_utility.trace('l_last_run_payroll_action_id : '||l_last_run_payroll_action_id);
1056 hr_utility.trace('l_payroll_id : '||l_payroll_id);
1057 hr_utility.trace('l_month_end : '||l_month_end);
1058
1059 -- Bug 14081001
1060 l_loop_count := 0;
1061 for ass_act_id in csr_get_rr_aa_id (p_assignment_id=> l_assignment_id,
1062 p_month_end => l_month_end,
1063 p_payroll_id=> l_payroll_id)
1064 loop
1065 hr_utility.set_location(l_proc, 71);
1066 l_loop_count := l_loop_count + 1;
1067 hr_utility.trace('assignment_action_id : '||ass_act_id.assignment_action_id);
1068 l_asg_act_id_table(l_loop_count) := ass_act_id.assignment_action_id;
1069 end loop;
1070 l_loop_count := 0;
1071 if l_asg_act_id_table.COUNT > 0 then
1072 hr_utility.set_location(l_proc, 72);
1073 -- to get data for batch balance retrieval
1074 for balance in csr_get_balance_data
1075 loop
1076 hr_utility.set_location(l_proc, 73);
1077 l_loop_count := l_loop_count + 1;
1078 hr_utility.trace('l_loop_count : '||l_loop_count);
1079
1080 l_defined_balance_lst(l_loop_count).defined_balance_id := pay_za_payroll_action_pkg.defined_balance_id(balance.balance_name, '_ASG_RUN');
1081 hr_utility.trace(' balance.balance_name : '|| balance.balance_name);
1082 hr_utility.trace(' l_defined_balance_lst(l_loop_count).defined_balance_id : '|| l_defined_balance_lst(l_loop_count).defined_balance_id);
1083
1084 -- Initializing the l_net_bal_table to Zero
1085 l_bal_name_table(l_defined_balance_lst(l_loop_count).defined_balance_id) := balance.balance_name;
1086 hr_utility.trace(' l_bal_name_table(l_defined_balance_lst(l_loop_count).defined_balance_id) : '|| l_bal_name_table(l_defined_balance_lst(l_loop_count).defined_balance_id));
1087
1088 l_net_bal_table(balance.balance_name).bal_value := 0;
1089 l_net_bal_table(balance.balance_name).defined_balance_id := l_defined_balance_lst(l_loop_count).defined_balance_id;
1090
1091 end loop;
1092
1093 hr_utility.set_location(l_proc, 74);
1094 -- to get data for batch balance retrieval
1095
1096 l_context_lst(1).tax_unit_id :=null;
1097 l_context_lst(1).jurisdiction_code :=null;
1098 l_context_lst(1).source_id :=null;
1099 l_context_lst(1).source_text :=null;
1100 l_context_lst(1).source_number :=null;
1101 l_context_lst(1).source_text2 :=null;
1102 l_context_lst(1).time_def_id :=null;
1103 l_context_lst(1).balance_date :=null;
1104 l_context_lst(1).local_unit_id :=null;
1105 l_context_lst(1).source_number2 :=null;
1106 l_context_lst(1).organization_id :=null;
1107
1108 for i in l_asg_act_id_table.first .. l_asg_act_id_table.last
1109 loop
1110 hr_utility.set_location(l_proc, 75);
1111 hr_utility.trace(' l_asg_act_id_table(i) : '|| l_asg_act_id_table(i));
1112 pay_balance_pkg.get_value(p_assignment_action_id => l_asg_act_id_table(i)
1113 , p_defined_balance_lst => l_defined_balance_lst
1114 , p_context_lst => l_context_lst
1115 , p_output_table => l_output_table
1116 );
1117
1118 hr_utility.set_location(l_proc, 76);
1119 hr_utility.trace(' l_output_table.COUNT : '|| l_output_table.COUNT);
1120 for j in l_output_table.first .. l_output_table.last
1121 loop
1122 hr_utility.set_location(l_proc, 77);
1123 l_net_bal_table(l_bal_name_table(l_output_table(j).defined_balance_id)).bal_value := l_net_bal_table(l_bal_name_table(l_output_table(j).defined_balance_id)).bal_value
1124 + nvl(l_output_table(j).balance_value,0);
1125 end loop;
1126
1127 end loop;
1128 end if;
1129 -- Bug 14081001
1130
1131 -- Get PAYE Data
1132 l_tax_dim_mtd := pay_za_payroll_action_pkg.defined_balance_id('Tax', '_ASG_TAX_MTD');
1133 l_tax := nvl(pay_balance_pkg.get_value(l_tax_dim_mtd, l_assignment_id,nvl(l_asg_end_date,l_pay_effective_date)),0);
1134
1135 -- Get PAYE Remuneration
1136 l_paye_rem_dim_mtd := pay_za_payroll_action_pkg.defined_balance_id('Net PAYE Taxable Income', '_ASG_TAX_MTD');
1137 l_paye_remuneration := nvl(pay_balance_pkg.get_value(l_paye_rem_dim_mtd, l_assignment_id,nvl(l_asg_end_date,l_pay_effective_date)),0);
1138
1139 -- Bug 14081001
1140 if l_asg_act_id_table.COUNT > 0 then
1141 l_tax := l_tax + l_net_bal_table('Tax').bal_value ;
1142 l_paye_remuneration := l_paye_remuneration + l_net_bal_table('Net PAYE Taxable Income').bal_value ;
1143 end if;
1144 -- Bug 14081001
1145
1146 hr_utility.set_location(l_proc, 80);
1147 -- Get SDL Data
1148 open get_sdl_exemption (l_assignment_id, nvl(l_asg_end_date,l_effective_date));
1149 fetch get_sdl_exemption into l_sdl_exemption ;
1150 close get_sdl_exemption ;
1151
1152
1153 -- exclude Exempt employees
1154 if l_sdl_exemption = 'N' then
1155
1156 hr_utility.set_location(l_proc, 90);
1157
1158 l_net_taxable_inc_dim_mtd := pay_za_payroll_action_pkg.defined_balance_id('Net Taxable Income', '_ASG_TAX_MTD');
1159 l_skills_levy_dim_mtd := pay_za_payroll_action_pkg.defined_balance_id('Skills Levy', '_ASG_TAX_MTD');
1160
1161 l_leviable_amt := nvl(pay_balance_pkg.get_value(l_net_taxable_inc_dim_mtd, l_assignment_id,nvl(l_asg_end_date,l_pay_effective_date)),0);
1162 l_sdl_amt := nvl(pay_balance_pkg.get_value(l_skills_levy_dim_mtd, l_assignment_id,nvl(l_asg_end_date,l_pay_effective_date)),0);
1163
1164 -- Bug 14081001
1165 if l_asg_act_id_table.COUNT > 0 then
1166 l_leviable_amt := l_leviable_amt + l_net_bal_table('Net Taxable Income').bal_value ;
1167 l_sdl_amt := l_sdl_amt + l_net_bal_table('Skills Levy').bal_value ;
1168 end if;
1169 -- Bug 14081001
1170
1171 if l_leviable_amt < 0 then
1172 l_leviable_amt := 0 ;
1173 end if ;
1174 else
1175 hr_utility.set_location(l_proc, 100);
1176 l_leviable_amt := 0 ;
1177 l_sdl_amt := 0 ;
1178 end if ;
1179
1180 -- Get UIF Data
1181
1182 -- Remuneration for employee's with a UIF reason for Non-Contribution must be excluded as must
1183 -- Remuneration of Independent Contractors and any non-natural persons (Nature of Person = D, E, F, G, H or K).
1184
1185 l_temp_emp_hours_dim_mtd := pay_za_payroll_action_pkg.defined_balance_id('ZA_TEMPORARY_EMPLOYEE_HOURS', '_ASG_TAX_MTD');
1186 l_temp_emp_hours := nvl(pay_balance_pkg.get_value(l_temp_emp_hours_dim_mtd, l_assignment_id,nvl(l_asg_end_date,l_pay_effective_date)),0);
1187
1188 if l_last_run_payroll_action_id is null then -- No payroll run for this calendar month
1189 hr_utility.set_location(l_proc, 110);
1190 l_uif_amt := 0 ;
1191 l_uif_remuneration := 0 ;
1192 elsif (l_nature_of_person in ('D','E','F','G','H','K') or
1193 l_independent_contractor = 'Y' or
1194 l_reason_for_uif_non_contrib in ('02','03','04','05','06','08','007') or
1195 ( l_reason_for_uif_non_contrib= '01'
1196 and
1197 l_temp_emp_hours < 24.0
1198 )
1199 ) then
1200 hr_utility.set_location(l_proc, 120);
1201 l_uif_amt := 0 ;
1202 l_uif_remuneration := 0 ;
1203 else
1204 hr_utility.set_location(l_proc, 130);
1205 -- get UIF amount and Remuneration
1206 l_uif_ee_dim_mtd := pay_za_payroll_action_pkg.defined_balance_id('UIF Employee Contribution', '_ASG_TAX_MTD');
1207 l_uif_er_dim_mtd := pay_za_payroll_action_pkg.defined_balance_id('UIF Employer Contribution', '_ASG_TAX_MTD');
1208 l_uifable_income_dim_mtd := pay_za_payroll_action_pkg.defined_balance_id('Total UIFable Income', '_ASG_TAX_MTD');
1209
1210 l_uif_ee_contr := nvl(pay_balance_pkg.get_value(l_uif_ee_dim_mtd, l_assignment_id,nvl(l_asg_end_date,l_pay_effective_date)),0);
1211 l_uif_er_contr := nvl(pay_balance_pkg.get_value(l_uif_er_dim_mtd, l_assignment_id,nvl(l_asg_end_date,l_pay_effective_date)),0);
1212
1213 -- Bug 14081001
1214 if l_asg_act_id_table.COUNT > 0 then
1215 l_uif_ee_contr := l_uif_ee_contr + l_net_bal_table('UIF Employee Contribution').bal_value ;
1216 l_uif_er_contr := l_uif_er_contr + l_net_bal_table('UIF Employer Contribution').bal_value ;
1217 end if;
1218 -- Bug 14081001
1219
1220 l_uif_amt := l_uif_ee_contr+ l_uif_er_contr ;
1221
1222 l_uif_remuneration := nvl(pay_balance_pkg.get_value(l_uifable_income_dim_mtd, l_assignment_id,nvl(l_asg_end_date,l_pay_effective_date)),0);
1223
1224 -- Bug 14081001
1225 if l_asg_act_id_table.COUNT > 0 then
1226 l_uif_remuneration := l_uif_remuneration + l_net_bal_table('Total UIFable Income').bal_value ;
1227 end if;
1228 -- Bug 14081001
1229
1230 -- Each person's UIF Remuneration must be limited to the monthly UIF maximum
1231 -- calculate periodic UIF limit
1232
1233 open csr_za_pay_mnth_prd_num(l_last_run_payroll_action_id, l_payroll_id);
1234 fetch csr_za_pay_mnth_prd_num into l_za_pay_mnth_prd_num ;
1235 close csr_za_pay_mnth_prd_num ;
1236
1237 open csr_pay_prd_per_yr(l_last_run_payroll_action_id, l_payroll_id);
1238 fetch csr_pay_prd_per_yr into l_pay_prd_per_yr ;
1239 close csr_pay_prd_per_yr ;
1240
1241 open csr_global_value( 'ZA_UIF_ANN_LIM', l_effective_date);
1242 fetch csr_global_value into l_UIF_Annual_Limit ;
1243 close csr_global_value ;
1244
1245 l_UIF_period_limit := round(l_za_pay_mnth_prd_num * l_UIF_Annual_Limit / l_pay_prd_per_yr ,2) ;
1246
1247 -- if UIF Remuneration > period_limit, then truncate it to period_limit
1248 if l_uif_remuneration > l_UIF_period_limit then
1249 hr_utility.set_location(l_proc, 140);
1250 l_uif_remuneration := l_UIF_period_limit ;
1251 end if ;
1252 end if ;
1253
1254 hr_utility.set_location(l_proc, 150);
1255
1256 -- whether to count the employee in Permanent or Non-Permanent or None
1257 if (l_nature_of_person not in ('D','E','F','G','H','K') and
1258 l_independent_contractor <> 'Y' and
1259 l_labour_broker <> 'Y') then
1260
1261 hr_utility.set_location(l_proc, 160);
1262
1263 -- get tax_status from run results
1264 if l_last_run_payroll_action_id is not null then
1265 hr_utility.set_location(l_proc, 170);
1266 open csr_tax_status(l_last_run_payroll_action_id);
1267 fetch csr_tax_status into l_tax_status ;
1268 close csr_tax_status ;
1269 else
1270 -- get tax status from element entry screen values
1271 hr_utility.set_location(l_proc, 180);
1272 -- tax status effective as on month_end_date / assignment_end_date
1273 open csr_tax_status_from_ele_entry (l_assignment_id,nvl(l_asg_end_date,l_pay_effective_date)) ;
1274 fetch csr_tax_status_from_ele_entry into l_tax_status ;
1275 if csr_tax_status_from_ele_entry%NOTFOUND then
1276 hr_utility.set_location(l_proc, 190);
1277 -- if tax status not specified, assume to be non-seasonal worker
1278 l_tax_status := 'A' ;
1279 end if ;
1280 close csr_tax_status_from_ele_entry;
1281 end if ;
1282
1283 hr_utility.trace('tax_status :'||l_tax_status);
1284
1285 l_gross_remun_dim_mtd := pay_za_payroll_action_pkg.defined_balance_id('Gross Remuneration', '_ASG_CAL_MTD');
1286 l_gross_remun := nvl(pay_balance_pkg.get_value(l_gross_remun_dim_mtd, l_assignment_id,nvl(l_asg_end_date,l_pay_effective_date)),0) ;
1287
1288 -- Bug 14081001
1289 if l_asg_act_id_table.COUNT > 0 then
1290 l_gross_remun := l_gross_remun + l_net_bal_table('Gross Remuneration').bal_value ;
1291 end if;
1292 -- Bug 14081001
1293
1294 if l_tax_status = 'G' and l_gross_remun = 0 then
1295 -- Seasonal worker and no remuneration in this month
1296 -- Hence don't count this employee
1297 hr_utility.set_location(l_proc, 200);
1298 l_seasonal_not_paid_flag := true ;
1299 end if ;
1300
1301 -- Do not count if employee is a seasonal worker
1302 -- and has not been paid any remuneration this month
1303 if l_seasonal_not_paid_flag = false then
1304 hr_utility.set_location(l_proc, 210);
1305 if l_EMP201_status = 'Permanent' then
1306 hr_utility.set_location(l_proc, 220);
1307 l_cnt_paye_perm := 'Y' ;
1308 else
1309 hr_utility.set_location(l_proc, 230);
1310 if l_gross_remun > 0 then
1311 hr_utility.set_location(l_proc, 240);
1312 -- Count non-permanent employees only if
1313 -- remuneration was paid / accrued to them during
1314 -- the relevant month
1315 l_cnt_paye_non_perm := 'Y';
1316 end if ;
1317 end if ;
1318 -- If seasonal worker with no remuneration and EMP201 status as 'Permanent', then dont count in permanent
1319 elsif (l_seasonal_not_paid_flag and l_EMP201_status = 'Permanent' ) then
1320 l_cnt_paye_perm := 'N' ;
1321 end if ;
1322 end if ;
1323
1324 hr_utility.set_location(l_proc, 220);
1325
1326 -------WARNING PAGE ------------------------------
1327
1328 -- Check each employee processed to see if they are terminated or not.
1329 -- If they are terminated they must have a value in SITE and/or PAYE.
1330 -- If this is not the case then a Warning Page must be printed at the end
1331 -- of the report.
1332 -- For summary format of the report option, print only count of
1333 -- defaulting assignments.
1334
1335 -- check if assignment is terminated
1336 open csr_check_asg_termination(l_assignment_id, l_effective_date);
1337 fetch csr_check_asg_termination into l_asg_terminated ;
1338 if csr_check_asg_termination%NOTFOUND then
1339 hr_utility.set_location(l_proc, 245);
1340 l_asg_terminated := 'Y' ;
1341 end if ;
1342 close csr_check_asg_termination ;
1343
1344 if l_asg_terminated = 'Y' then -- assignment is terminated
1345 hr_utility.set_location(l_proc, 250);
1346 l_tax_dim_ytd := pay_za_payroll_action_pkg.defined_balance_id('Tax', '_ASG_TAX_YTD');
1347 l_tax_ytd := nvl(pay_balance_pkg.get_value(l_tax_dim_ytd, l_assignment_id,nvl(l_asg_end_date,l_pay_effective_date)),0);
1348
1349 l_site_dim_ytd := pay_za_payroll_action_pkg.defined_balance_id('SITE', '_ASG_TAX_YTD');
1350 l_site := nvl(pay_balance_pkg.get_value(l_site_dim_ytd, l_assignment_id,nvl(l_asg_end_date,l_pay_effective_date)),0);
1351
1352 l_paye_dim_ytd := pay_za_payroll_action_pkg.defined_balance_id('PAYE', '_ASG_TAX_YTD');
1353 l_paye := nvl(pay_balance_pkg.get_value(l_paye_dim_ytd, l_assignment_id,nvl(l_asg_end_date,l_pay_effective_date)),0);
1354
1355 if l_tax_ytd <> 0 and l_site = 0 and l_paye = 0 then -- SITE/PAYE split not calculated
1356 hr_utility.set_location(l_proc, 255);
1357 l_raise_warning := 'Y' ;
1358 end if ; -- end of SITE/PAYE split not calculated for the assignment
1359 end if ; -- end of Assignment Terminated
1360
1361 hr_utility.set_location(l_proc, 260);
1362
1363 hr_utility.set_location('p_action_context_id => '|| to_char(p_assactid), 60);
1364 hr_utility.set_location('p_action_context_type => '|| 'AAP', 60);
1365 hr_utility.set_location('p_assignment_id => '|| l_assignment_id, 60);
1366 hr_utility.set_location('p_effective_date => '|| to_char(l_effective_date, 'dd/mm/yyyy'), 60);
1367 hr_utility.set_location('p_action_information_category => '|| 'ZA EMP201 ASSIGNMENT DATA', 60);
1368 hr_utility.set_location('p_action_information1 => '|| l_assignment_id, 60);
1369 hr_utility.set_location('p_action_information2 => '|| to_char(l_effective_date, 'YYYYMM'), 60);
1370 hr_utility.set_location('p_action_information3 => '|| l_employee_number, 60);
1371 hr_utility.set_location('p_action_information4 => '|| l_emp_name, 60);
1372 hr_utility.set_location('p_action_information5 => '|| l_paye_remuneration, 60);
1373 hr_utility.set_location('p_action_information6 => '|| l_tax, 60);
1374 hr_utility.set_location('p_action_information7 => '|| l_leviable_amt, 60);
1375 hr_utility.set_location('p_action_information8 => '|| l_sdl_amt, 60);
1376 hr_utility.set_location('p_action_information9 => '|| l_uif_remuneration, 60);
1377 hr_utility.set_location('p_action_information10 => '|| l_uif_amt, 60);
1378 hr_utility.set_location('p_action_information11 => '|| l_EMP201_status, 60);
1379 hr_utility.set_location('p_action_information12 => '|| l_cnt_paye_perm, 60);
1380 hr_utility.set_location('p_action_information13 => '|| l_cnt_paye_non_perm, 60);
1381 hr_utility.set_location('p_action_information14 => '|| l_raise_warning, 60);
1382 hr_utility.set_location('p_action_information15 => '|| l_asg_terminated, 60);
1383 hr_utility.set_location('p_action_information16 => '|| l_assignment_no, 60);
1384 hr_utility.set_location('p_action_information17 => '|| l_pactid_archive, 60);
1385 hr_utility.set_location('p_action_information18 => '|| l_ass_payroll_id, 60);
1386
1387
1388 -- Archive the ZA EMP201 ASSIGNMENT DETAILS
1389 pay_action_information_api.create_action_information
1390 (
1391 p_action_information_id => l_action_info_id,
1392 p_action_context_id => p_assactid, -- Assignment Action of the Archiver
1393 p_action_context_type => 'AAP',
1394 p_object_version_number => l_ovn,
1395 p_assignment_id => l_assignment_id,
1396 p_effective_date => p_archive_effective_date,
1397 p_source_id => null,
1398 p_source_text => null,
1399 p_action_information_category => 'ZA EMP201 ASSIGNMENT DETAILS',
1400 p_action_information1 => l_assignment_id,
1401 p_action_information2 => to_char(l_effective_date, 'YYYYMM'),
1402 p_action_information3 => l_employee_number,
1403 p_action_information4 => l_emp_name,
1404 p_action_information5 => fnd_number.number_to_canonical(l_paye_remuneration),
1405 p_action_information6 => fnd_number.number_to_canonical(l_tax),
1406 p_action_information7 => fnd_number.number_to_canonical(l_leviable_amt),
1407 p_action_information8 => fnd_number.number_to_canonical(l_sdl_amt),
1408 p_action_information9 => fnd_number.number_to_canonical(l_uif_remuneration),
1409 p_action_information10 => fnd_number.number_to_canonical(l_uif_amt),
1410 p_action_information11 => l_EMP201_status,
1411 p_action_information12 => l_cnt_paye_perm,
1412 p_action_information13 => l_cnt_paye_non_perm,
1413 p_action_information14 => l_raise_warning,
1414 p_action_information15 => l_asg_terminated,
1415 p_action_information16 => l_assignment_no,
1416 p_action_information17 => l_pactid_archive,
1417 p_action_information18 => l_ass_payroll_id
1418 );
1419
1420 hr_utility.set_location('Leaving ' || l_proc, 270);
1421 --hr_utility.trace_off ;
1422 end archive_data;
1423
1424 -- Bug 16174886
1425 --
1426 --
1427 -- -----------------------------------------------------------------------------
1428 -- Archdinit code
1429 -- -----------------------------------------------------------------------------
1430 --
1431 -- Archive payroll level information here
1432
1433 procedure archdinit(pactid in number) as
1434
1435 cursor csr_distinct_payroll_names_rec is
1436 select distinct pai.action_information18 payroll_id,
1437 ppf.payroll_name payroll_name,
1438 pai.effective_date effective_date
1439 from pay_payroll_actions ppa
1440 , pay_assignment_actions paa
1441 , pay_action_information pai
1442 , pay_all_payrolls_f ppf
1443 where ppa.payroll_action_id = pactid
1444 and ppa.action_status = 'C'
1445 and paa.payroll_action_id = ppa.payroll_action_id
1446 and paa.action_status = 'C'
1447 and pai.action_context_id = paa.assignment_action_id
1448 and pai.action_context_type = 'AAP'
1449 and pai.action_information_category = 'ZA EMP201 ASSIGNMENT DETAILS'
1450 and ppf.payroll_id = pai.action_information18
1451 and pai.effective_date between effective_start_date and effective_end_date
1452 order by payroll_id;
1453
1454
1455 l_action_info_id number;
1456 l_ovn number;
1457 l_proc varchar2(50) := g_package || 'arch_dinit';
1458
1459 begin
1460
1461 hr_utility.set_location('Entering ' || l_proc, 10);
1462
1463 for ass_pay_id in csr_distinct_payroll_names_rec
1464 loop
1465 hr_utility.set_location(l_proc, 20);
1466
1467 -- Archive 'ZA EMP201 PAYROLL DETAILS'
1468 pay_action_information_api.create_action_information
1469 (
1470 p_action_information_id => l_action_info_id,
1471 p_action_context_id => pactid,
1472 p_action_context_type => 'PA',
1473 p_object_version_number => l_ovn,
1474 p_effective_date => ass_pay_id.effective_date,
1475 p_action_information_category => 'ZA EMP201 PAYROLL DETAILS',
1476 p_action_information1 => ass_pay_id.payroll_id,
1477 p_action_information2 => ass_pay_id.payroll_name
1478 );
1479
1480 hr_utility.set_location(l_proc, 30);
1481 end loop;
1482 hr_utility.set_location('Leaving ' || l_proc, 40);
1483
1484 end archdinit;
1485
1486 -- Bug 16174886
1487
1488 /*--------------------------------------------------------------------------
1489 Name : get_parameters
1490 Purpose : This retrieves legislative parameters from the payroll action.
1491 Arguments :
1492 --------------------------------------------------------------------------*/
1493
1494 --
1495 -- -----------------------------------------------------------------------------
1496 -- Get the correct characterset for XML generation
1497 -- -----------------------------------------------------------------------------
1498 --
1499 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
1500 CURSOR csr_get_iana_charset IS
1501 SELECT tag
1502 FROM fnd_lookup_values
1503 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
1504 AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
1505 INSTR(USERENV('LANGUAGE'), '.') + 1)
1506 AND language = 'US';
1507 --
1508 lv_iana_charset fnd_lookup_values.tag%type;
1509 BEGIN
1510 OPEN csr_get_iana_charset;
1511 FETCH csr_get_iana_charset INTO lv_iana_charset;
1512 CLOSE csr_get_iana_charset;
1513 RETURN (lv_iana_charset);
1514 END get_IANA_charset;
1515
1516 --
1517 --
1518 -- -----------------------------------------------------------------------------
1519 -- Takes XML element from a table and puts them into a CLOB.
1520 -- -----------------------------------------------------------------------------
1521 --
1522
1523 PROCEDURE write_to_clob (p_clob OUT NOCOPY CLOB) IS
1524
1525 -- l_xml_element_template0 VARCHAR2(20) := '<TAG>VALUE</TAG>';
1526 -- l_xml_element_template1 VARCHAR2(30) := '<TAG><![CDATA[VALUE]]></TAG>';
1527 -- l_xml_element_template2 VARCHAR2(10) := '<TAG>';
1528 -- l_xml_element_template3 VARCHAR2(10) := '</TAG>';
1529 l_str1 VARCHAR2(80) ;
1530 l_str2 VARCHAR2(20) := '</EOY> </ROOT>';
1531 l_xml_element VARCHAR2(800);
1532 l_clob CLOB;
1533 --
1534 BEGIN
1535
1536 l_str1 := '<?xml version="1.0" encoding="' || get_IANA_charset || '"?>' ;
1537
1538 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
1539 dbms_lob.open(l_clob, DBMS_LOB.LOB_READWRITE);
1540 --
1541 dbms_lob.writeappend(l_clob, LENGTH(l_str1), l_str1);
1542 --
1543 IF g_xml_element_table.COUNT > 0 THEN
1544 --
1545 FOR table_counter IN g_xml_element_table.FIRST .. g_xml_element_table.LAST LOOP
1546 --
1547 IF g_xml_element_table(table_counter).tagvalue = '_START_' THEN
1548 l_xml_element := '<' || g_xml_element_table(table_counter).tagname || '>';
1549 ELSIF g_xml_element_table(table_counter).tagvalue = '_END_' THEN
1550 l_xml_element := '</' || g_xml_element_table(table_counter).tagname || '>';
1551 ELSIF g_xml_element_table(table_counter).tagvalue = '_COMMENT_' THEN
1552 l_xml_element := '<!-- ' || g_xml_element_table(table_counter).tagname || ' -->';
1553 ELSE
1554 l_xml_element := '<' || g_xml_element_table(table_counter).tagname ||
1555 '><![CDATA[' || g_xml_element_table(table_counter).tagvalue ||
1556 ']]></' || g_xml_element_table(table_counter).tagname || '>';
1557 END IF;
1558 --
1559 dbms_lob.writeappend(l_clob, LENGTH(l_xml_element), l_xml_element);
1560 --
1561 END LOOP;
1562 --
1563 END IF;
1564
1565 p_clob := l_clob;
1566 --
1567 EXCEPTION
1568 WHEN OTHERS THEN
1569 --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
1570 hr_utility.set_location(sqlerrm(sqlcode),110);
1571 --
1572 END write_to_clob;
1573
1574 --
1575 --
1576 -- -----------------------------------------------------------------------------
1577 -- Main procedure to be called to generate XML file for EMP201 report
1578 -- -----------------------------------------------------------------------------
1579 --
1580
1581 PROCEDURE get_emp201_xml
1582 (business_group_id number
1583 ,calendar_month varchar2
1584 ,calendar_month_hidden varchar2
1585 ,EMP201_FILE_PREPROCESS varchar2
1586 ,p_detail_flag varchar2
1587 ,p_template_name IN VARCHAR2
1588 ,p_xml OUT NOCOPY CLOB) AS
1589
1590
1591 cursor csr_get_emp201_le_data(p_archive_pact number) is
1592 select action_information1 l_legal_entity_id
1593 , action_information2 l_legal_entity_name
1594 , action_information3 l_payroll_prd
1595 from pay_action_information
1596 where action_context_id = p_archive_pact
1597 and action_context_type = 'PA'
1598 and action_information_category = 'ZA EMP201 LEGAL ENTITY DETAILS' ;
1599
1600 cursor csr_get_emp201_payroll_data(p_archive_pact number) is
1601 select action_information1 payroll_id
1602 , action_information2 payroll_name
1603 from pay_action_information
1604 where action_context_id = p_archive_pact
1605 and action_context_type = 'PA'
1606 and action_information_category = 'ZA EMP201 PAYROLL DETAILS'
1607 order by payroll_id ;
1608
1609 cursor csr_get_emp201_asg_data(p_archive_pact number, p_payroll_id number) is
1610 select action_information1 assignment_id,
1611 action_information2 l_effective_date,
1612 action_information3 employee_number,
1613 action_information4 emp_name,
1614 formatted_canonical(action_information5) paye_remuneration,
1615 formatted_canonical(action_information6) tax,
1616 formatted_canonical(action_information7) leviable_amt,
1617 formatted_canonical(action_information8) sdl_amt,
1618 formatted_canonical(action_information9) uif_remuneration,
1619 formatted_canonical(action_information10) uif_amt,
1620 action_information11 EMP201_status,
1621 action_information12 cnt_paye_perm,
1622 action_information13 cnt_paye_non_perm,
1623 action_information14 raise_warning,
1624 action_information15 asg_terminated,
1625 action_information16 assignment_no
1626 from pay_action_information pai
1627 , pay_assignment_actions paa
1628 where paa.payroll_action_id = p_archive_pact
1629 and pai.action_context_id = paa.assignment_action_id
1630 and pai.action_context_type = 'AAP'
1631 and pai.action_information_category = 'ZA EMP201 ASSIGNMENT DETAILS'
1632 and pai.action_information18 = p_payroll_id
1633 order by employee_number,emp_name;
1634
1635
1636 type emp is record (
1637 employee_number varchar2(30),
1638 asg_no varchar2(30),
1639 emp_name varchar2(350),
1640 payroll_id number,
1641 payroll_name varchar2(80)
1642 ) ;
1643
1644 type defaulting_emp_tab is TABLE of emp index by binary_integer ;
1645
1646 defaulting_emp_rec defaulting_emp_tab ;
1647
1648 defaulting_asg_count number := 0 ;
1649
1650 l_clob CLOB;
1651 l_str1 varchar2(200);
1652 l_archive_pact number ;
1653 l_proc_name constant varchar2(200) := g_package || 'get_emp201_xml' ;
1654
1655 -- l_payroll_id number ;
1656 -- l_payroll_name varchar2(80) ;
1657 l_payroll_prd varchar2(30) ;
1658 l_legal_entity_id number;
1659 l_legal_entity_name varchar2(240);
1660
1661
1662 l_first_asgn_rec boolean := true ;
1663
1664 l_emp_rec_printed boolean := false ;
1665
1666 l_tot_paye_rem number := 0 ;
1667 l_tot_paye number := 0 ;
1668 l_tot_leviable_amt number := 0 ;
1669 l_tot_sdl_amt number := 0;
1670 l_tot_uif_rem number := 0;
1671 l_tot_uif_amt number := 0 ;
1672
1673 l_cnt_paye_perm number := 0 ;
1674 l_cnt_paye_non_perm number := 0;
1675 l_cnt_sdl number := 0 ;
1676 l_cnt_uif number := 0 ;
1677 l_control_tot number := 0 ;
1678
1679 l_xml_element_count number := 0 ;
1680
1681 l_payroll_id number;
1682 l_payroll_open number;
1683
1684 BEGIN
1685 -- hr_utility.trace_on(null,'ZAEMP201');
1686 hr_utility.set_location('Entering ' || l_proc_name, 10);
1687 g_xml_element_table.DELETE;
1688 ---
1689 -- Start XML
1690 ---
1691 g_xml_element_table(l_xml_element_count).tagname := 'EMP201';
1692 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1693 l_xml_element_count := l_xml_element_count + 1;
1694
1695
1696 ---
1697 -- Legal Entity Data
1698 ---
1699 l_archive_pact := substr(EMP201_FILE_PREPROCESS,instr(EMP201_FILE_PREPROCESS,'=')+1);
1700
1701 open csr_get_emp201_le_data(l_archive_pact) ;
1702 fetch csr_get_emp201_le_data into l_legal_entity_id
1703 , l_legal_entity_name
1704 , l_payroll_prd;
1705 close csr_get_emp201_le_data ;
1706
1707 hr_utility.set_location(l_proc_name, 20);
1708
1709 g_xml_element_table(l_xml_element_count).tagname := 'Legal Entity Information';
1710 g_xml_element_table(l_xml_element_count).tagvalue := '_COMMENT_';
1711 l_xml_element_count := l_xml_element_count + 1;
1712
1713 -- Bug 16174886
1714 g_xml_element_table(l_xml_element_count).tagname := 'LEGAL_ENTITY';
1715 g_xml_element_table(l_xml_element_count).tagvalue := l_legal_entity_name;
1716 l_xml_element_count := l_xml_element_count + 1;
1717 /*
1718 g_xml_element_table(l_xml_element_count).tagname := 'PAYROLL_NAME';
1719 g_xml_element_table(l_xml_element_count).tagvalue := l_payroll_name;
1720 l_xml_element_count := l_xml_element_count + 1;
1721 */
1722 -- Bug 16174886
1723 g_xml_element_table(l_xml_element_count).tagname := 'PAYROLL_PRD';
1724 g_xml_element_table(l_xml_element_count).tagvalue := l_payroll_prd;
1725 l_xml_element_count := l_xml_element_count + 1;
1726
1727 g_xml_element_table(l_xml_element_count).tagname := 'DETAIL_FLAG';
1728 g_xml_element_table(l_xml_element_count).tagvalue := p_detail_flag;
1729 l_xml_element_count := l_xml_element_count + 1;
1730
1731 hr_utility.set_location(l_proc_name, 30);
1732
1733
1734 ---
1735 -- Payroll Data
1736 ---
1737 for pay in csr_get_emp201_payroll_data(l_archive_pact)
1738 loop
1739 hr_utility.trace('Payroll_id : '||pay.payroll_id);
1740 hr_utility.trace('Payroll_name : '||pay.payroll_name);
1741
1742 g_xml_element_table(l_xml_element_count).tagname := 'PAYROLL';
1743 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1744 l_xml_element_count := l_xml_element_count + 1;
1745
1746 g_xml_element_table(l_xml_element_count).tagname := 'Payroll Information';
1747 g_xml_element_table(l_xml_element_count).tagvalue := '_COMMENT_';
1748 l_xml_element_count := l_xml_element_count + 1;
1749
1750
1751 g_xml_element_table(l_xml_element_count).tagname := 'PAYROLL_NAME';
1752 g_xml_element_table(l_xml_element_count).tagvalue := pay.payroll_name;
1753 l_xml_element_count := l_xml_element_count + 1;
1754
1755
1756 l_tot_paye_rem := 0 ;
1757 l_tot_paye := 0 ;
1758 l_tot_leviable_amt := 0 ;
1759 l_tot_sdl_amt := 0;
1760 l_tot_uif_rem := 0;
1761 l_tot_uif_amt := 0 ;
1762
1763 l_cnt_paye_perm := 0 ;
1764 l_cnt_paye_non_perm := 0;
1765 l_cnt_sdl := 0 ;
1766 l_cnt_uif := 0 ;
1767 l_control_tot := 0 ;
1768
1769 hr_utility.trace('Payroll_name : '||pay.payroll_name);
1770 hr_utility.trace('l_tot_paye_rem : '||l_tot_paye_rem);
1771 hr_utility.trace('l_tot_paye : '||l_tot_paye);
1772 hr_utility.trace('l_tot_leviable_amt: '||l_tot_leviable_amt);
1773 hr_utility.trace('l_tot_sdl_amt : '||l_tot_sdl_amt);
1774 hr_utility.trace('l_tot_uif_rem : '||l_tot_uif_rem);
1775 hr_utility.trace('l_tot_uif_amt : '||l_tot_uif_amt);
1776 hr_utility.trace('l_cnt_paye_perm: '||l_cnt_paye_perm);
1777 hr_utility.trace('l_cnt_paye_non_perm: '||l_cnt_paye_non_perm);
1778 hr_utility.trace('l_cnt_sdl : '||l_cnt_sdl);
1779 hr_utility.trace('l_cnt_uif : '||l_cnt_uif);
1780 hr_utility.trace('l_control_tot : '||l_control_tot);
1781
1782 ---
1783 -- Employees Data
1784 ---
1785
1786 for ass in csr_get_emp201_asg_data (l_archive_pact,pay.payroll_id )
1787 loop
1788 hr_utility.trace('Assignment_id : '||ass.assignment_id);
1789
1790 /* 1. Dont display employee records if the employee is terminated and has not earned any
1791 late payment, and hence dont count them in permanent/non-permanent.
1792 2. In case of non-permanent employees with no remuneration, dont display in the report
1793 and hence dont count them in permanent/non-permanent. */
1794 if (((ass.cnt_paye_non_perm is null and ass.cnt_paye_perm is null) OR ass.asg_terminated='Y')
1795 AND (ass.paye_remuneration = 0 and ass.tax = 0 and ass.leviable_amt = 0 and ass.sdl_amt = 0
1796 and ass.uif_remuneration = 0 and ass.uif_amt = 0 ))
1797 then
1798 null;
1799 else
1800 if p_detail_flag = 'Y' then
1801 hr_utility.set_location(l_proc_name, 100);
1802 if l_first_asgn_rec then
1803 -- Add <ALL_EMP> start tag before the first assignment
1804 g_xml_element_table(l_xml_element_count).tagname := 'Employees Information';
1805 g_xml_element_table(l_xml_element_count).tagvalue := '_COMMENT_';
1806 l_xml_element_count := l_xml_element_count + 1;
1807
1808 g_xml_element_table(l_xml_element_count).tagname := 'ALL_EMP';
1809 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1810 l_xml_element_count := l_xml_element_count + 1;
1811
1812 l_first_asgn_rec := false;
1813 l_emp_rec_printed := true ;
1814 end if ;
1815
1816 hr_utility.set_location(l_proc_name, 110);
1817
1818 -- Employees Data will come here
1819 hr_utility.set_location(l_proc_name, 130);
1820 g_xml_element_table(l_xml_element_count).tagname := 'EMP';
1821 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1822 l_xml_element_count := l_xml_element_count + 1;
1823
1824 g_xml_element_table(l_xml_element_count).tagname := 'EMP_NO';
1825 g_xml_element_table(l_xml_element_count).tagvalue := ass.employee_number;
1826 l_xml_element_count := l_xml_element_count + 1;
1827
1828 g_xml_element_table(l_xml_element_count).tagname := 'EMP_NAME';
1829 g_xml_element_table(l_xml_element_count).tagvalue := ass.emp_name;
1830 l_xml_element_count := l_xml_element_count + 1;
1831
1832 g_xml_element_table(l_xml_element_count).tagname := 'PAYE_REMUNERATION';
1833 g_xml_element_table(l_xml_element_count).tagvalue := ass.paye_remuneration;
1834 l_xml_element_count := l_xml_element_count + 1;
1835
1836 g_xml_element_table(l_xml_element_count).tagname := 'PAYE';
1837 g_xml_element_table(l_xml_element_count).tagvalue := ass.tax;
1838 l_xml_element_count := l_xml_element_count + 1;
1839
1840 g_xml_element_table(l_xml_element_count).tagname := 'LEVIABLE_AMT';
1841 g_xml_element_table(l_xml_element_count).tagvalue := ass.leviable_amt;
1842 l_xml_element_count := l_xml_element_count + 1;
1843
1844 g_xml_element_table(l_xml_element_count).tagname := 'SDL_AMT';
1845 g_xml_element_table(l_xml_element_count).tagvalue := ass.sdl_amt;
1846 l_xml_element_count := l_xml_element_count + 1;
1847
1848 g_xml_element_table(l_xml_element_count).tagname := 'UIF_REMUNERATION';
1849 g_xml_element_table(l_xml_element_count).tagvalue := ass.uif_remuneration;
1850 l_xml_element_count := l_xml_element_count + 1;
1851
1852 g_xml_element_table(l_xml_element_count).tagname := 'UIF_AMT';
1853 g_xml_element_table(l_xml_element_count).tagvalue := ass.uif_amt;
1854 l_xml_element_count := l_xml_element_count + 1;
1855
1856 g_xml_element_table(l_xml_element_count).tagname := 'CATEGORY';
1857 g_xml_element_table(l_xml_element_count).tagvalue := ass.EMP201_status;
1858 l_xml_element_count := l_xml_element_count + 1;
1859
1860 g_xml_element_table(l_xml_element_count).tagname := 'EMP';
1861 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
1862 l_xml_element_count := l_xml_element_count + 1;
1863 end if ; -- End of p_detail_flag = 'Y'
1864
1865 l_tot_paye_rem := l_tot_paye_rem + ass.paye_remuneration ;
1866 l_tot_paye := l_tot_paye + ass.tax ;
1867 l_tot_leviable_amt := l_tot_leviable_amt + ass.leviable_amt ;
1868 l_tot_sdl_amt := l_tot_sdl_amt + ass.sdl_amt ;
1869 l_tot_uif_rem := l_tot_uif_rem + ass.uif_remuneration ;
1870 l_tot_uif_amt := l_tot_uif_amt + ass.uif_amt ;
1871
1872 hr_utility.set_location(l_proc_name, 140);
1873 if ass.cnt_paye_perm = 'Y' then
1874 l_cnt_paye_perm := l_cnt_paye_perm + 1 ;
1875 end if ;
1876
1877 if ass.cnt_paye_non_perm = 'Y' then
1878 l_cnt_paye_non_perm := l_cnt_paye_non_perm + 1 ;
1879 end if ;
1880
1881 if fnd_number.canonical_to_number(ass.sdl_amt) > 0 then
1882 l_cnt_sdl := l_cnt_sdl + 1 ;
1883 end if ;
1884
1885 if fnd_number.canonical_to_number(ass.uif_amt) > 0 then
1886 l_cnt_uif := l_cnt_uif + 1 ;
1887 end if ;
1888
1889 end if ; -- end of rem and amt <>0 */
1890
1891 hr_utility.set_location(l_proc_name, 150);
1892 if ass.raise_warning = 'Y' then
1893 defaulting_asg_count := defaulting_asg_count + 1;
1894 defaulting_emp_rec(defaulting_asg_count).employee_number := ass.employee_number ;
1895 defaulting_emp_rec(defaulting_asg_count).asg_no := ass.assignment_no;
1896 defaulting_emp_rec(defaulting_asg_count).emp_name := ass.emp_name ;
1897 defaulting_emp_rec(defaulting_asg_count).payroll_id := pay.payroll_id;
1898 defaulting_emp_rec(defaulting_asg_count).payroll_name := pay.payroll_name;
1899 hr_utility.set_location(l_proc_name, 160);
1900 end if ;
1901
1902 end loop ;
1903
1904 if l_emp_rec_printed = true then
1905 -- if <ALL_EMP> tag was printed, then close it now ( after all employees
1906 -- data has been printed
1907 g_xml_element_table(l_xml_element_count).tagname := 'ALL_EMP';
1908 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
1909 l_xml_element_count := l_xml_element_count + 1;
1910 hr_utility.set_location(l_proc_name, 170);
1911 end if;
1912
1913 l_control_tot := l_tot_paye + l_tot_sdl_amt + l_tot_uif_amt ;
1914
1915 ---
1916 -- Print totals
1917 --
1918 g_xml_element_table(l_xml_element_count).tagname := 'Totals';
1919 g_xml_element_table(l_xml_element_count).tagvalue := '_COMMENT_';
1920 l_xml_element_count := l_xml_element_count + 1;
1921
1922 g_xml_element_table(l_xml_element_count).tagname := 'TOT_PAYE_REM';
1923 g_xml_element_table(l_xml_element_count).tagvalue := formatted_canonical(l_tot_paye_rem);
1924 l_xml_element_count := l_xml_element_count + 1;
1925
1926 g_xml_element_table(l_xml_element_count).tagname := 'TOT_PAYE';
1927 g_xml_element_table(l_xml_element_count).tagvalue := formatted_canonical(l_tot_paye);
1928 l_xml_element_count := l_xml_element_count + 1;
1929
1930 g_xml_element_table(l_xml_element_count).tagname := 'TOT_LEVIABLE_AMT';
1931 g_xml_element_table(l_xml_element_count).tagvalue := formatted_canonical(l_tot_leviable_amt);
1932 l_xml_element_count := l_xml_element_count + 1;
1933
1934 g_xml_element_table(l_xml_element_count).tagname := 'TOT_SDL_AMT';
1935 g_xml_element_table(l_xml_element_count).tagvalue := formatted_canonical(l_tot_sdl_amt);
1936 l_xml_element_count := l_xml_element_count + 1;
1937
1938 g_xml_element_table(l_xml_element_count).tagname := 'TOT_UIF_REM';
1939 g_xml_element_table(l_xml_element_count).tagvalue := formatted_canonical(l_tot_uif_rem);
1940 l_xml_element_count := l_xml_element_count + 1;
1941
1942 g_xml_element_table(l_xml_element_count).tagname := 'TOT_UIF_AMT';
1943 g_xml_element_table(l_xml_element_count).tagvalue := formatted_canonical(l_tot_uif_amt);
1944 l_xml_element_count := l_xml_element_count + 1;
1945
1946 g_xml_element_table(l_xml_element_count).tagname := 'CONTROL_TOTAL';
1947 g_xml_element_table(l_xml_element_count).tagvalue := formatted_canonical(l_control_tot);
1948 l_xml_element_count := l_xml_element_count + 1;
1949
1950 g_xml_element_table(l_xml_element_count).tagname := 'Employee Counts';
1951 g_xml_element_table(l_xml_element_count).tagvalue := '_COMMENT_';
1952 l_xml_element_count := l_xml_element_count + 1;
1953
1954 g_xml_element_table(l_xml_element_count).tagname := 'CNT_PAYE_PERM';
1955 g_xml_element_table(l_xml_element_count).tagvalue := l_cnt_paye_perm;
1956 l_xml_element_count := l_xml_element_count + 1;
1957
1958 g_xml_element_table(l_xml_element_count).tagname := 'CNT_PAYE_NON_PERM';
1959 g_xml_element_table(l_xml_element_count).tagvalue := l_cnt_paye_non_perm;
1960 l_xml_element_count := l_xml_element_count + 1;
1961
1962 g_xml_element_table(l_xml_element_count).tagname := 'CNT_SDL';
1963 g_xml_element_table(l_xml_element_count).tagvalue := l_cnt_sdl;
1964 l_xml_element_count := l_xml_element_count + 1;
1965
1966 g_xml_element_table(l_xml_element_count).tagname := 'CNT_UIF';
1967 g_xml_element_table(l_xml_element_count).tagvalue := l_cnt_uif;
1968 l_xml_element_count := l_xml_element_count + 1;
1969
1970 l_first_asgn_rec := true ;
1971
1972 g_xml_element_table(l_xml_element_count).tagname := 'PAYROLL';
1973 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
1974 l_xml_element_count := l_xml_element_count + 1;
1975
1976 end loop; -- for pay loop
1977
1978 hr_utility.set_location(l_proc_name, 180);
1979
1980 -------WARNING PAGE ------------------------------
1981 if defaulting_asg_count > 0 then
1982 hr_utility.set_location(l_proc_name, 190);
1983 g_xml_element_table(l_xml_element_count).tagname := 'Warning Page';
1984 g_xml_element_table(l_xml_element_count).tagvalue := '_COMMENT_';
1985 l_xml_element_count := l_xml_element_count + 1;
1986
1987 g_xml_element_table(l_xml_element_count).tagname := 'PG_BRK';
1988 g_xml_element_table(l_xml_element_count).tagvalue := 'Y';
1989 l_xml_element_count := l_xml_element_count + 1;
1990
1991 g_xml_element_table(l_xml_element_count).tagname := 'WARN';
1992 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1993 l_xml_element_count := l_xml_element_count + 1;
1994
1995 g_xml_element_table(l_xml_element_count).tagname := 'HEADER_LINE';
1996 g_xml_element_table(l_xml_element_count).tagvalue := 'The following Employees do not ' ||
1997 'have a SITE / PAYE split. Please process a final payroll run, a tax override or ' ||
1998 'a tax balance adjustment for these employees to ensure correct Tax Year End reporting:';
1999 l_xml_element_count := l_xml_element_count + 1;
2000 end if ;
2001
2002 hr_utility.set_location(l_proc_name, 200);
2003
2004 l_payroll_id := 0;
2005 l_payroll_open := 0;
2006
2007 for i in 1 .. defaulting_emp_rec.count
2008 loop
2009 if l_payroll_id <> defaulting_emp_rec(i).payroll_id then
2010 l_payroll_id := defaulting_emp_rec(i).payroll_id;
2011
2012 if l_payroll_open=1 then
2013 g_xml_element_table(l_xml_element_count).tagname := 'PAYROLL';
2014 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2015 l_xml_element_count := l_xml_element_count + 1;
2016 l_payroll_open := 0;
2017 end if;
2018
2019 g_xml_element_table(l_xml_element_count).tagname := 'PAYROLL';
2020 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2021 l_xml_element_count := l_xml_element_count + 1;
2022 l_payroll_open := 1;
2023
2024 g_xml_element_table(l_xml_element_count).tagname := 'PAYROLL_NAME';
2025 g_xml_element_table(l_xml_element_count).tagvalue := 'defaulting_emp_rec(i).payroll_name';
2026 l_xml_element_count := l_xml_element_count + 1;
2027
2028 end if;
2029
2030 if l_payroll_id=defaulting_emp_rec(i).payroll_id then
2031 g_xml_element_table(l_xml_element_count).tagname := 'WARN_EMP';
2032 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2033 l_xml_element_count := l_xml_element_count + 1;
2034
2035 g_xml_element_table(l_xml_element_count).tagname := 'EMP_NUM';
2036 g_xml_element_table(l_xml_element_count).tagvalue := defaulting_emp_rec(i).employee_number;
2037 l_xml_element_count := l_xml_element_count + 1;
2038
2039 g_xml_element_table(l_xml_element_count).tagname := 'ASG_NUM';
2040 g_xml_element_table(l_xml_element_count).tagvalue := defaulting_emp_rec(i).asg_no;
2041 l_xml_element_count := l_xml_element_count + 1;
2042
2043 g_xml_element_table(l_xml_element_count).tagname := 'EMP_NAME';
2044 g_xml_element_table(l_xml_element_count).tagvalue := defaulting_emp_rec(i).emp_name ;
2045 l_xml_element_count := l_xml_element_count + 1;
2046
2047 g_xml_element_table(l_xml_element_count).tagname := 'WARN_EMP';
2048 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2049 l_xml_element_count := l_xml_element_count + 1;
2050 end if;
2051 end loop ;
2052
2053 IF l_payroll_open=1 then
2054 g_xml_element_table(l_xml_element_count).tagname := 'PAYROLL';
2055 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2056 l_xml_element_count := l_xml_element_count + 1;
2057 l_payroll_open := 0;
2058 end if;
2059
2060 hr_utility.set_location(l_proc_name,220);
2061
2062 if defaulting_asg_count > 0 then
2063 g_xml_element_table(l_xml_element_count).tagname := 'WARN';
2064 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2065 l_xml_element_count := l_xml_element_count + 1;
2066 end if ;
2067
2068 ---
2069 -- End XML
2070 ---
2071 g_xml_element_table(l_xml_element_count).tagname := 'EMP201';
2072 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2073 l_xml_element_count := l_xml_element_count + 1;
2074 --
2075 write_to_clob(p_xml);
2076 --
2077 hr_utility.set_location(l_proc_name, 999);
2078 hr_utility.set_location('Leaving ' || l_proc_name, 1000);
2079
2080 END get_emp201_xml ;
2081
2082 END PAY_ZA_EMP201 ;