[Home] [Help]
PACKAGE BODY: APPS.PAY_ZA_PAYSLIP_ARCHIVE
Source
1 package body pay_za_payslip_archive as
2 /* $Header: pyzaparc.pkb 120.10.12010000.2 2008/08/06 08:45:06 ubhat ship $ */
3
4 type balance_rec is record
5 (
6 balance_type_id number,
7 balance_dimension_id number,
8 defined_balance_id number,
9 balance_narrative varchar2(50),
10 balance_name varchar2(80),
11 database_item_suffix varchar2(80),
12 legislation_code varchar2(20)
13 );
14
15 type element_rec is record
16 (
17 element_type_id number,
18 input_value_id number,
19 formula_id number,
20 element_narrative varchar2(50),
21 once_each_period_flag varchar2(1)
22 );
23
24 type balance_table is table of balance_rec index by binary_integer;
25 type element_table is table of element_rec index by binary_integer;
26
27 g_user_balance_table balance_table;
28 g_element_table element_table;
29 g_statutory_balance_table balance_table;
30
31 g_max_element_index number := 0;
32 g_max_user_balance_index number := 0;
33 g_balance_archive_index number := 0;
34 g_element_archive_index number := 0;
35 g_max_statutory_balance_index number := 0;
36
37 g_tax_element_id number;
38 g_tax_status varchar2(60) := null;
39 g_tax_status_meaning varchar2(80) := null;
40
41 g_balance_context constant varchar2(30) := 'ZA_PAYSLIP_BALANCES';
42 g_element_context constant varchar2(30) := 'ZA_PAYSLIP_ELEMENTS';
43
44 g_archive_pact number;
45 g_archive_effective_date date;
46
47 g_package constant varchar2(30) := 'pay_za_payslip_archive.';
48
49 -- This procedure retrieves legislative parameters from the payroll action
50 procedure get_parameters
51 (
52 p_payroll_action_id in number,
53 p_token_name in varchar2,
54 p_token_value out nocopy varchar2
55 ) is
56
57 cursor csr_parameter_info
58 (
59 p_pact_id number,
60 p_token char
61 ) is
62 select substr
63 (
64 legislative_parameters,
65 instr
66 (
67 legislative_parameters,
68 p_token
69 ) + (length(p_token) + 1),
70 instr
71 (
72 legislative_parameters,
73 ' ',
74 instr
75 (
76 legislative_parameters,
77 p_token
78 )
79 )
80 -
81 (
82 instr
83 (
84 legislative_parameters,
85 p_token
86 ) + length(p_token)
87 )
88 ),
89 business_group_id
90 from pay_payroll_actions
91 where payroll_action_id = p_pact_id;
92
93 l_business_group_id varchar2(20);
94 l_token_value varchar2(50);
95
96 l_proc varchar2(50);
97
98 begin
99 l_proc := g_package || 'get_parameters';
100 -- Removed default assignment to remove GSCC warning
101
102 hr_utility.set_location('Entering ' || l_proc, 10);
103
104 hr_utility.set_location('Step ' || l_proc, 20);
105 hr_utility.set_location('p_token_name = ' || p_token_name, 20);
106
107 open csr_parameter_info
108 (
109 p_payroll_action_id,
110 p_token_name
111 );
112 fetch csr_parameter_info into l_token_value, l_business_group_id;
113 close csr_parameter_info;
114
115 if p_token_name = 'BG_ID' then
116
117 p_token_value := l_business_group_id;
118
119 else
120
121 p_token_value := l_token_value;
122
123 end if;
124
125 hr_utility.set_location('l_token_value = ' || p_token_value, 20);
126 hr_utility.set_location('Leaving ' || l_proc, 30);
127
128 exception
129 when others then
130 p_token_value := null;
131
132 end get_parameters;
133
134 procedure get_eit_definitions
135 (
136 p_pactid in number, -- Payroll Action of the Archiver
137 p_business_group_id in number,
138 p_payroll_pact in number, -- Payroll Action of the Prepayments
139 p_effective_date in date, -- Effective Date of the Prepayments
140 p_eit_context in varchar2, -- ZA_PAYSLIP_BALANCES, ZA_PAYSLIP_ELEMENTS
141 p_archive in varchar2 -- Y, N
142 ) is
143
144 -- Return all the Org Developer Flex values for the specified context
145 cursor csr_eit_values
146 (
147 p_bg_id number,
148 p_context char
149 ) is
150 select org.org_information1,
151 org.org_information2,
152 org.org_information3,
153 org.org_information4,
154 org.org_information5,
155 org.org_information6
156 from hr_organization_information org
157 where org.org_information_context = p_context
158 and org.organization_id = p_bg_id;
159
160 -- Returns the details of a specific balance
161 cursor csr_balance_name
162 (
163 p_balance_type_id number,
164 p_balance_dimension_id number
165 ) is
166 select pbt.balance_name,
167 pbd.database_item_suffix,
168 pbt.legislation_code,
169 pdb.defined_balance_id
170 from pay_balance_types pbt,
171 pay_balance_dimensions pbd,
172 pay_defined_balances pdb
173 where pbt.balance_type_id = p_balance_type_id
174 and pbd.balance_dimension_id = p_balance_dimension_id
175 and pdb.balance_type_id = pbt.balance_type_id
176 and pdb.balance_dimension_id = pbd.balance_dimension_id;
177
178 cursor csr_element_type
179 (
180 p_element_type_id number,
181 p_effective_date date
182 ) is
183 select pet.formula_id
184 from pay_element_types_f pet,
185 ff_formulas_f fff
186 where pet.element_type_id = p_element_type_id
187 and pet.formula_id = fff.formula_id
188 and fff.formula_name = 'ONCE_EACH_PERIOD'
189 and p_effective_date between fff.effective_start_date and fff.effective_end_date
190 and p_effective_date between pet.effective_start_date and pet.effective_end_date;
191
192 --added for Bug 4488264
193 cursor csr_element_type1
194 (p_element_type_id number,
195 p_effective_date date
196 ) is
197 select once_each_period_flag
198 from pay_element_types_f pet
199 where pet.element_type_id = p_element_type_id
200 and p_effective_date between pet.effective_start_date and pet.effective_end_date;
201
202 cursor csr_input_value_uom
203 (
204 p_input_value_id number,
205 p_effective_date date
206 ) is
207 select piv.uom
208 from pay_input_values_f piv
209 where piv.input_value_id = p_input_value_id
210 and p_effective_date between piv.effective_start_date and piv.effective_end_date;
211
212 l_index number := 1;
213 l_action_info_id number(15);
214 l_formula_id number(15);
215 l_ovn number(15);
216 l_uom varchar(30);
217 l_once_each_period_flag varchar2(1);
218
219 l_proc varchar2(50);
220
221 begin
222 l_proc := g_package || 'get_eit_definitions';
223 -- Removed default assignment to remove GSCC warning
224
225 hr_utility.set_location('Entering ' || l_proc, 10);
226
227 hr_utility.set_location('Step ' || l_proc, 20);
228 hr_utility.set_location('p_eit_context = ' || p_eit_context, 20);
229
230 -- Loop through all the Org Developer Flex values for the specified context
231 for csr_eit_rec in csr_eit_values
232 (
233 p_business_group_id,
234 p_eit_context
235 )
236 loop
237
238 hr_utility.set_location('Step ' || l_proc, 30);
239
240 hr_utility.set_location('org_information1 = ' || csr_eit_rec.org_information1, 30);
241 hr_utility.set_location('org_information2 = ' || csr_eit_rec.org_information2, 30);
242 hr_utility.set_location('org_information3 = ' || csr_eit_rec.org_information3, 30);
243 hr_utility.set_location('org_information4 = ' || csr_eit_rec.org_information4, 30);
244 hr_utility.set_location('org_information5 = ' || csr_eit_rec.org_information5, 30);
245 hr_utility.set_location('org_information6 = ' || csr_eit_rec.org_information6, 30);
246
247 -- Business Group Level Balances
248 if p_eit_context = g_balance_context then
249
250 -- Populate Balance PL/SQL table
251 g_user_balance_table(l_index).balance_type_id := csr_eit_rec.org_information1;
252 g_user_balance_table(l_index).balance_dimension_id := csr_eit_rec.org_information2;
253 g_user_balance_table(l_index).balance_narrative := csr_eit_rec.org_information3;
254
255 -- Retrieve the balance details into PL/SQL table
256 open csr_balance_name
257 (
258 g_user_balance_table(l_index).balance_type_id,
259 g_user_balance_table(l_index).balance_dimension_id
260 );
261 fetch csr_balance_name
262 into g_user_balance_table(l_index).balance_name,
263 g_user_balance_table(l_index).database_item_suffix,
264 g_user_balance_table(l_index).legislation_code,
265 g_user_balance_table(l_index).defined_balance_id;
266
267 close csr_balance_name;
268
269 hr_utility.set_location('g_user_balance_table(l_index).balance_name = ' ||
270 g_user_balance_table(l_index).balance_name, 50);
271
272 hr_utility.set_location('Arch EMEA BALANCE DEFINITION', 99);
273
274 -- Archive the balance definition in 'EMEA BALANCE DEFINITION' if archive is Y
275 if p_archive = 'Y' then
276
277 pay_action_information_api.create_action_information
278 (
279 p_action_information_id => l_action_info_id,
280 p_action_context_id => p_pactid, -- Payroll Action of the Archiver
281 p_action_context_type => 'PA',
282 p_object_version_number => l_ovn,
283 p_effective_date => p_effective_date, -- Effective Date of the Prepayments
284 p_source_id => null,
285 p_source_text => null,
286 p_action_information_category => 'EMEA BALANCE DEFINITION',
287 p_action_information1 => p_payroll_pact, -- Payroll Action of the Prepayments
288 p_action_information2 => g_user_balance_table(l_index).defined_balance_id,
289 p_action_information3 => null,
290 p_action_information4 => csr_eit_rec.org_information3 -- Balance Narrative
291 );
292
293 end if;
294
295 g_max_user_balance_index := g_max_user_balance_index + 1;
296
297 end if;
298
299 -- Business Group Level Elements
300 if p_eit_context = g_element_context then
301
302 -- Populate Element PL/SQL table
303 g_element_table(l_index).element_type_id := csr_eit_rec.org_information1;
304 g_element_table(l_index).input_value_id := csr_eit_rec.org_information2;
305 g_element_table(l_index).element_narrative := csr_eit_rec.org_information3;
306
307 l_formula_id := null;
308
309 open csr_element_type(csr_eit_rec.org_information1, p_effective_date);
310 fetch csr_element_type into l_formula_id;
311 close csr_element_type;
312
313 g_element_table(l_index).formula_id := l_formula_id;
314
315 -- Added for Bug 4488264
316 l_once_each_period_flag := null;
317 open csr_element_type1(csr_eit_rec.org_information1, p_effective_date);
318 fetch csr_element_type1 into l_once_each_period_flag;
319 close csr_element_type1;
320 g_element_table(l_index).once_each_period_flag := l_once_each_period_flag;
321
322 -- Retrieve the input value details
323 open csr_input_value_uom(csr_eit_rec.org_information2, p_effective_date);
324 fetch csr_input_value_uom into l_uom;
325 close csr_input_value_uom;
326
327 -- Archive the element definition in 'EMEA ELEMENT DEFINITION' if archive is Y
328 -- Note: These are the elements on the Organization Developer Flexfield only
329 if p_archive = 'Y' then
330
331 hr_utility.set_location('Arch EMEA ELEMENT DEFINITION', 99);
332
333 pay_action_information_api.create_action_information
334 (
335 p_action_information_id => l_action_info_id,
336 p_action_context_id => p_pactid, -- Payroll Action of the Archiver
337 p_action_context_type => 'PA',
338 p_object_version_number => l_ovn,
339 p_effective_date => p_effective_date, -- Effective Date of the Prepayments
340 p_source_id => null,
341 p_source_text => null,
342 p_action_information_category => 'EMEA ELEMENT DEFINITION',
343 p_action_information1 => p_payroll_pact, -- Payroll Action of the Prepayments
344 p_action_information2 => csr_eit_rec.org_information1, -- Element Type ID
345 p_action_information3 => csr_eit_rec.org_information2, -- Input Value ID
346 p_action_information4 => csr_eit_rec.org_information3, -- Element Narrative
347 p_action_information5 => 'F', -- To indicate their from Flexfield, not Earning etc.
348 p_action_information6 => l_uom
352
349 );
350
351 end if;
353 end if;
354
355 l_index := l_index + 1;
356
357 hr_utility.set_location('l_index = ' || l_index, 99);
358
359 end loop;
360
361 g_max_element_index := l_index;
362
363 if p_eit_context = g_balance_context then
364
365 g_balance_archive_index := l_index - 1;
366
367 else
368
369 g_element_archive_index := l_index - 1;
370
371 end if;
372
373 hr_utility.set_location('g_balance_archive_index = ' || g_balance_archive_index, 99);
374
375 hr_utility.set_location('Leaving ' || l_proc, 30);
376
377 end get_eit_definitions;
378
379 procedure setup_element_definitions
380 (
381 p_pactid in number, -- Payroll Action of Archiver
382 p_payroll_pact in number, -- Payroll Action of Prepayments
383 p_effective_date in date -- Effective Date of Prepayments
384 ) is
385
386 -- Returns all elements for the specified Prepayment and Classifications
387 -- FIX which classifications should i use
388 cursor csr_element_name(p_payroll_action_id number) is -- Payroll Action of Prepayments
389 select distinct pet.element_type_id element_type_id,
390 piv.input_value_id,
391 nvl(pet.reporting_name, pet.element_name) element_name,
392 pec.classification_name,
393 piv.uom
394 from pay_element_types_f pet,
395 pay_input_values_f piv,
396 pay_run_results prr,
397 pay_element_classifications pec,
398 pay_assignment_actions paa, -- Assignment Action of Prepayments
399 pay_assignment_actions rpaa, -- Assignment Action of Run
400 pay_action_interlocks pai,
401 pay_payroll_actions ppa -- Payroll Action of Prepayments
402 where pet.element_type_id = prr.element_type_id
403 and piv.element_type_id = pet.element_type_id
404 and piv.name = 'Pay Value'
405 and pet.classification_id = pec.classification_id
406 and pec.classification_name in
407 (
408 'Statutory Information',
409 'Normal Income',
410 'Statutory Deductions',
411 'Lump Sum Amounts',
412 'Allowances',
413 'Deductions',
414 'Information',
415 'Involuntary Deductions',
416 'Employer Contributions',
417 'Voluntary Deductions',
418 'Direct Payments',
419 'Fringe Benefits'
420 )
421 and pet.element_name not in ('ZA_Tax_Output', 'ZA_Tax_Output_2', 'ZA_Tax', 'ZA_Tax_2',
422 'ZA_Tax_3', 'ZA_Tax_4', 'ZA_Tax_5', 'ZA_Tax_D1', 'ZA_Tax_D2',
423 'ZA_Tax_D3', 'ZA_Tax_M', 'ZA_Tax_6')
424 and pec.legislation_code = 'ZA'
425 and prr.assignment_action_id = rpaa.assignment_action_id
426 and paa.payroll_action_id = ppa.payroll_action_id
427 and pai.locking_action_id = paa.assignment_action_id
428 and rpaa.assignment_action_id = pai.locked_action_id
429 and ppa.effective_date between pet.effective_start_date and pet.effective_end_date
430 and ppa.effective_date between piv.effective_start_date and piv.effective_end_date
431 and ppa.payroll_action_id = p_payroll_action_id;
432
433 -- Added the ZA_Tax_6 IN the NOT IN list
434 -- for the bug no 3663022
435
436 l_payment_type varchar2(1);
437 l_action_info_id number(15);
438 l_ovn number(15);
439
440 l_proc varchar2(60);
441
442 begin
443 l_proc := g_package || 'setup_element_definitions';
444 -- Removed default assignment to remove GSCC warning
445
446 hr_utility.set_location('Entering ' || l_proc, 10);
447 hr_utility.set_location('p_payroll_pact = ' || p_payroll_pact, 10);
448
449 -- Loop through all elements for the specified Prepayment and Classifications
450 for csr_element_rec in csr_element_name(p_payroll_pact) loop -- Payroll Action of Prepayments
451
452 hr_utility.set_location('csr_element_rec.element_type_id = ' || csr_element_rec.element_type_id, 20);
453 hr_utility.set_location('csr_element_rec.element_name = ' || csr_element_rec.element_name, 20);
454
455 -- Classify the Element according to Classification
456 -- FIX which classifications should i use?
457 if csr_element_rec.classification_name in ('Normal Income', 'Allowances', 'Direct Payments', 'Lump Sum Amounts') then
458
459 l_payment_type := 'E'; -- Earning
460
461 elsif csr_element_rec.classification_name in ('Fringe Benefits', 'Information', 'Employer Contributions', 'Statutory Information') then
462
463 l_payment_type := 'B'; -- Benefits
464
465 else
466
467 l_payment_type := 'D'; -- Deduction
468
469 end if;
470
471 hr_utility.set_location('Arch EMEA ELEMENT DEFINITION', 99);
472
473 -- Archive the element definition in 'EMEA ELEMENT DEFINITION'
474 -- Note: These are the Elements from the above Classifications and not the ones on the Org Flex
475 pay_action_information_api.create_action_information
476 (
477 p_action_information_id => l_action_info_id,
478 p_action_context_id => p_pactid,
482 p_source_id => null,
479 p_action_context_type => 'PA',
480 p_object_version_number => l_ovn,
481 p_effective_date => p_effective_date,
483 p_source_text => null,
484 p_action_information_category => 'EMEA ELEMENT DEFINITION',
485 p_action_information1 => p_payroll_pact,
486 p_action_information2 => csr_element_rec.element_type_id,
487 p_action_information3 => csr_element_rec.input_value_id,
488 p_action_information4 => csr_element_rec.element_name,
489 p_action_information5 => l_payment_type,
490 p_action_information6 => csr_element_rec.uom
491 );
492
493 end loop;
494
495 hr_utility.set_location('Leaving ' || l_proc, 30);
496
497 end setup_element_definitions;
498
499 procedure setup_standard_balance_table is
500
501 type balance_name_rec is record (balance_name varchar2(80));
502
503 type balance_id_rec is record (defined_balance_id number);
504
505 type balance_name_tab is table of balance_name_rec index by binary_integer;
506 type balance_id_tab is table of balance_id_rec index by binary_integer;
507
508 l_statutory_balance balance_name_tab;
509 l_statutory_bal_id balance_id_tab;
510 -- 3221746 included subquery
511 cursor csr_balance_dimension
512 (
513 p_balance in varchar2,
514 p_dimension in varchar2
515 ) is
516 select pdb.defined_balance_id
517 from pay_balance_types pbt,
518 pay_defined_balances pdb
519 where pdb.balance_type_id = pbt.balance_type_id
520 and pbt.balance_name = p_balance
521 and pdb.balance_dimension_id = (select balance_dimension_id
522 from pay_balance_dimensions
523 where dimension_name = p_dimension);
524
525
526 l_archive_index number := 0;
527 l_dimension varchar2(12) ;
528 l_found varchar2(1);
529
530 l_max_stat_balance number := 197;
531
532 l_proc varchar2(100);
533
534 begin
535 l_dimension := '_ASG_TAX_YTD';
536 l_proc := g_package || 'setup_standard_balance_table';
537 -- Removed default assignment to remove GSCC warning
538
539 hr_utility.set_location('Entering ' || l_proc, 10);
540 hr_utility.set_location('Step ' || l_proc, 20);
541
542 l_statutory_balance(1 ).balance_name := 'Taxable Income RFI';
543 l_statutory_balance(2 ).balance_name := 'Taxable Income PKG';
544 l_statutory_balance(3 ).balance_name := 'Taxable Income NRFI';
545 l_statutory_balance(4 ).balance_name := 'Non Taxable Income';
546 l_statutory_balance(5 ).balance_name := 'Taxable Pension RFI';
547 l_statutory_balance(6 ).balance_name := 'Taxable Pension PKG';
548 l_statutory_balance(7 ).balance_name := 'Taxable Pension NRFI';
549 l_statutory_balance(8 ).balance_name := 'Non Taxable Pension';
550 l_statutory_balance(9 ).balance_name := 'Taxable Annual Payment RFI';
551 l_statutory_balance(10 ).balance_name := 'Taxable Annual Payment PKG';
552 l_statutory_balance(11 ).balance_name := 'Taxable Annual Payment NRFI';
553 l_statutory_balance(12 ).balance_name := 'Annual Bonus RFI';
554 l_statutory_balance(13 ).balance_name := 'Annual Bonus PKG';
555 l_statutory_balance(14 ).balance_name := 'Annual Bonus NRFI';
556 l_statutory_balance(15 ).balance_name := 'Commission RFI';
557 l_statutory_balance(16 ).balance_name := 'Commission PKG';
558 l_statutory_balance(17 ).balance_name := 'Commission NRFI';
559 l_statutory_balance(18 ).balance_name := 'Overtime RFI';
560 l_statutory_balance(19 ).balance_name := 'Overtime PKG';
561 l_statutory_balance(20 ).balance_name := 'Overtime NRFI';
562 l_statutory_balance(21 ).balance_name := 'Taxable Arbitration Award RFI';
563 l_statutory_balance(22 ).balance_name := 'Taxable Arbitration Award NRFI';
564 l_statutory_balance(23 ).balance_name := 'Non Taxable Arbitration Award';
565 l_statutory_balance(24 ).balance_name := 'Annuity from Retirement Fund RFI';
566 l_statutory_balance(25 ).balance_name := 'Annuity from Retirement Fund PKG';
567 l_statutory_balance(26 ).balance_name := 'Annuity from Retirement Fund NRFI';
568 l_statutory_balance(27 ).balance_name := 'Purchased Annuity Taxable RFI';
569 l_statutory_balance(28 ).balance_name := 'Purchased Annuity Taxable PKG';
570 l_statutory_balance(29 ).balance_name := 'Purchased Annuity Taxable NRFI';
571 l_statutory_balance(30 ).balance_name := 'Purchased Annuity Non Taxable';
572 l_statutory_balance(31 ).balance_name := 'Travel Allowance RFI';
573 l_statutory_balance(32 ).balance_name := 'Travel Allowance PKG';
574 l_statutory_balance(33 ).balance_name := 'Travel Allowance NRFI';
575 l_statutory_balance(34 ).balance_name := 'Taxable Reimbursive Travel RFI';
576 l_statutory_balance(35 ).balance_name := 'Taxable Reimbursive Travel PKG';
577 l_statutory_balance(36 ).balance_name := 'Taxable Reimbursive Travel NRFI';
578 l_statutory_balance(37 ).balance_name := 'Non Taxable Reimbursive Travel';
579 l_statutory_balance(38 ).balance_name := 'Taxable Subsistence RFI';
580 l_statutory_balance(39 ).balance_name := 'Taxable Subsistence PKG';
581 l_statutory_balance(40 ).balance_name := 'Taxable Subsistence NRFI';
582 l_statutory_balance(41 ).balance_name := 'Non Taxable Subsistence';
583 l_statutory_balance(42 ).balance_name := 'Entertainment Allowance RFI';
587 l_statutory_balance(46 ).balance_name := 'Share Options Exercised NRFI';
584 l_statutory_balance(43 ).balance_name := 'Entertainment Allowance PKG';
585 l_statutory_balance(44 ).balance_name := 'Entertainment Allowance NRFI';
586 l_statutory_balance(45 ).balance_name := 'Share Options Exercised RFI';
588 l_statutory_balance(47 ).balance_name := 'Public Office Allowance RFI';
589 l_statutory_balance(48 ).balance_name := 'Public Office Allowance PKG';
590 l_statutory_balance(49 ).balance_name := 'Public Office Allowance NRFI';
591 l_statutory_balance(50 ).balance_name := 'Uniform Allowance';
592 l_statutory_balance(51 ).balance_name := 'Tool Allowance RFI';
593 l_statutory_balance(52 ).balance_name := 'Tool Allowance PKG';
594 l_statutory_balance(53 ).balance_name := 'Tool Allowance NRFI';
595 l_statutory_balance(54 ).balance_name := 'Computer Allowance RFI';
596 l_statutory_balance(55 ).balance_name := 'Computer Allowance PKG';
597 l_statutory_balance(56 ).balance_name := 'Computer Allowance NRFI';
598 l_statutory_balance(57 ).balance_name := 'Telephone Allowance RFI';
599 l_statutory_balance(58 ).balance_name := 'Telephone Allowance PKG';
600 l_statutory_balance(59 ).balance_name := 'Telephone Allowance NRFI';
601 l_statutory_balance(60 ).balance_name := 'Other Taxable Allowance RFI';
602 l_statutory_balance(61 ).balance_name := 'Other Taxable Allowance PKG';
603 l_statutory_balance(62 ).balance_name := 'Other Taxable Allowance NRFI';
604 l_statutory_balance(63 ).balance_name := 'Other Non Taxable Allowance';
605 l_statutory_balance(64 ).balance_name := 'Asset Purchased at Reduced Value RFI';
606 l_statutory_balance(65 ).balance_name := 'Asset Purchased at Reduced Value PKG';
607 l_statutory_balance(66 ).balance_name := 'Asset Purchased at Reduced Value NRFI';
608 l_statutory_balance(67 ).balance_name := 'Use of Motor Vehicle RFI';
609 l_statutory_balance(68 ).balance_name := 'Use of Motor Vehicle PKG';
610 l_statutory_balance(69 ).balance_name := 'Use of Motor Vehicle NRFI';
611 l_statutory_balance(70 ).balance_name := 'Right of Use of Asset RFI';
612 l_statutory_balance(71 ).balance_name := 'Right of Use of Asset PKG';
613 l_statutory_balance(72 ).balance_name := 'Right of Use of Asset NRFI';
614 l_statutory_balance(73 ).balance_name := 'Meals Refreshments and Vouchers RFI';
615 l_statutory_balance(74 ).balance_name := 'Meals Refreshments and Vouchers PKG';
616 l_statutory_balance(75 ).balance_name := 'Meals Refreshments and Vouchers NRFI';
617 l_statutory_balance(76 ).balance_name := 'Free or Cheap Accommodation RFI';
618 l_statutory_balance(77 ).balance_name := 'Free or Cheap Accommodation PKG';
619 l_statutory_balance(78 ).balance_name := 'Free or Cheap Accommodation NRFI';
620 l_statutory_balance(79 ).balance_name := 'Free or Cheap Services RFI';
621 l_statutory_balance(80 ).balance_name := 'Free or Cheap Services PKG';
622 l_statutory_balance(81 ).balance_name := 'Free or Cheap Services NRFI';
623 l_statutory_balance(82 ).balance_name := 'Low or Interest Free Loans RFI';
624 l_statutory_balance(83 ).balance_name := 'Low or Interest Free Loans PKG';
625 l_statutory_balance(84 ).balance_name := 'Low or Interest Free Loans NRFI';
626 l_statutory_balance(85 ).balance_name := 'Payment of Employee Debt RFI';
627 l_statutory_balance(86 ).balance_name := 'Payment of Employee Debt PKG';
628 l_statutory_balance(87 ).balance_name := 'Payment of Employee Debt NRFI';
629 l_statutory_balance(88 ).balance_name := 'Bursaries and Scholarships RFI';
630 l_statutory_balance(89 ).balance_name := 'Bursaries and Scholarships PKG';
631 l_statutory_balance(90 ).balance_name := 'Bursaries and Scholarships NRFI';
632 l_statutory_balance(91 ).balance_name := 'Medical Aid Paid on Behalf of Employee RFI';
633 l_statutory_balance(92 ).balance_name := 'Medical Aid Paid on Behalf of Employee PKG';
634 l_statutory_balance(93 ).balance_name := 'Medical Aid Paid on Behalf of Employee NRFI';
635 l_statutory_balance(94 ).balance_name := 'Retirement or Retrenchment Gratuities';
636 l_statutory_balance(95 ).balance_name := 'Resignation Pension and RAF Lump Sums';
637 l_statutory_balance(96 ).balance_name := 'Retirement Pension and RAF Lump Sums';
638 l_statutory_balance(97 ).balance_name := 'Resignation Provident Lump Sums';
639 l_statutory_balance(98 ).balance_name := 'Retirement Provident Lump Sums';
640 l_statutory_balance(99 ).balance_name := 'Special Remuneration';
641 l_statutory_balance(100).balance_name := 'Other Lump Sums';
642 l_statutory_balance(101).balance_name := 'Current Pension Fund';
643 l_statutory_balance(102).balance_name := 'Arrear Pension Fund';
644 l_statutory_balance(103).balance_name := 'Current Provident Fund';
645 l_statutory_balance(104).balance_name := 'Arrear Provident Fund';
646 l_statutory_balance(105).balance_name := 'Medical Aid Contribution';
647 l_statutory_balance(106).balance_name := 'Current Retirement Annuity';
648 l_statutory_balance(107).balance_name := 'Arrear Retirement Annuity';
649 l_statutory_balance(108).balance_name := 'Tax on Lump Sums';
650 l_statutory_balance(109).balance_name := 'Tax';
651 l_statutory_balance(110).balance_name := 'UIF Employee Contribution';
652 l_statutory_balance(111).balance_name := 'Voluntary Tax';
653 l_statutory_balance(112).balance_name := 'Bonus Provision';
654 l_statutory_balance(113).balance_name := 'SITE';
655 l_statutory_balance(114).balance_name := 'PAYE';
656 l_statutory_balance(115).balance_name := 'Annual Pension Fund';
657 l_statutory_balance(116).balance_name := 'Annual Commission RFI';
658 l_statutory_balance(117).balance_name := 'Annual Commission PKG';
662 l_statutory_balance(121).balance_name := 'Restraint of Trade PKG';
659 l_statutory_balance(118).balance_name := 'Annual Commission NRFI';
660 l_statutory_balance(119).balance_name := 'Annual Provident Fund';
661 l_statutory_balance(120).balance_name := 'Restraint of Trade RFI';
663 l_statutory_balance(122).balance_name := 'Restraint of Trade NRFI';
664 l_statutory_balance(123).balance_name := 'Annual Restraint of Trade RFI';
665 l_statutory_balance(124).balance_name := 'Annual Restraint of Trade PKG';
666 l_statutory_balance(125).balance_name := 'Annual Restraint of Trade NRFI';
667 l_statutory_balance(126).balance_name := 'Annual Asset Purchased at Reduced Value RFI';
668 l_statutory_balance(127).balance_name := 'Annual Asset Purchased at Reduced Value PKG';
669 l_statutory_balance(128).balance_name := 'Annual Asset Purchased at Reduced Value NRFI';
670 l_statutory_balance(129).balance_name := 'Annual Retirement Annuity';
671 l_statutory_balance(130).balance_name := 'Annual Arrear Pension Fund';
672 l_statutory_balance(131).balance_name := 'Annual Arrear Retirement Annuity';
673 l_statutory_balance(132).balance_name := 'Annual Bursaries and Scholarships NRFI';
674 l_statutory_balance(133).balance_name := 'Annual Bursaries and Scholarships RFI';
675 l_statutory_balance(134).balance_name := 'Annual Bursaries and Scholarships PKG';
676 l_statutory_balance(135).balance_name := 'Annual EE Income Protection Policy Contributions';
677 l_statutory_balance(136).balance_name := 'Annual Independent Contractor Payments NRFI';
678 l_statutory_balance(137).balance_name := 'Annual Independent Contractor Payments RFI';
679 l_statutory_balance(138).balance_name := 'Annual Independent Contractor Payments PKG';
680 l_statutory_balance(139).balance_name := 'Annual Labour Broker Payments NRFI';
681 l_statutory_balance(140).balance_name := 'Annual Labour Broker Payments RFI';
682 l_statutory_balance(141).balance_name := 'Annual Labour Broker Payments PKG';
683 l_statutory_balance(142).balance_name := 'Annual NRFIable Total Package';
684 l_statutory_balance(143).balance_name := 'Annual Payment of Employee Debt NRFI';
685 l_statutory_balance(144).balance_name := 'Annual Payment of Employee Debt RFI';
686 l_statutory_balance(145).balance_name := 'Annual Payment of Employee Debt PKG';
687 l_statutory_balance(146).balance_name := 'Annual RFIable Total Package';
688 l_statutory_balance(147).balance_name := 'Directors Deemed Remuneration';
689 l_statutory_balance(148).balance_name := 'EE Income Protection Policy Contributions';
690 l_statutory_balance(149).balance_name := 'Executive Equity Shares NRFI';
691 l_statutory_balance(150).balance_name := 'Executive Equity Shares RFI';
692 l_statutory_balance(151).balance_name := 'Independent Contractor Payments NRFI';
693 l_statutory_balance(152).balance_name := 'Independent Contractor Payments RFI';
694 l_statutory_balance(153).balance_name := 'Independent Contractor Payments PKG';
695 l_statutory_balance(154).balance_name := 'Labour Broker Payments NRFI';
696 l_statutory_balance(155).balance_name := 'Labour Broker Payments RFI';
697 l_statutory_balance(156).balance_name := 'Labour Broker Payments PKG';
698 l_statutory_balance(157).balance_name := 'NRFIable Total Package';
699 l_statutory_balance(158).balance_name := 'Non Taxable Subsistence Allowance Foreign Travel';
700 l_statutory_balance(159).balance_name := 'Other Retirement Lump Sums';
701 l_statutory_balance(160).balance_name := 'RFIable Total Package';
702 l_statutory_balance(161).balance_name := 'Taxable Subsistence Allowance Foreign Travel NRFI';
703 l_statutory_balance(162).balance_name := 'Taxable Subsistence Allowance Foreign Travel RFI';
704 l_statutory_balance(163).balance_name := 'Taxable Subsistence Allowance Foreign Travel PKG';
705 l_statutory_balance(164).balance_name := 'EE Broadbased Share Plan NRFI';
706 l_statutory_balance(165).balance_name := 'EE Broadbased Share Plan RFI';
707 l_statutory_balance(166).balance_name := 'EE Broadbased Share Plan PKG';
708 l_statutory_balance(167).balance_name := 'Other Lump Sum Taxed as Annual Payment RFI';
709 l_statutory_balance(168).balance_name := 'Other Lump Sum Taxed as Annual Payment NRFI';
710 l_statutory_balance(169).balance_name := 'Other Lump Sum Taxed as Annual Payment PKG';
711 -- End bug 4276047
712
713 -- Begin: TYS 06-07 Changes
714 l_statutory_balance(170).balance_name := 'Med Costs Pd by ER IRO EE_Family RFI';
715 l_statutory_balance(171).balance_name := 'Med Costs Pd by ER IRO EE_Family NRFI';
716 l_statutory_balance(172).balance_name := 'Med Costs Pd by ER IRO EE_Family PKG';
717 l_statutory_balance(173).balance_name := 'Annual Med Costs Pd by ER IRO EE_Family RFI';
718 l_statutory_balance(174).balance_name := 'Annual Med Costs Pd by ER IRO EE_Family NRFI';
719 l_statutory_balance(175).balance_name := 'Annual Med Costs Pd by ER IRO EE_Family PKG';
720 l_statutory_balance(176).balance_name := 'Annual Med Costs Pd by ER IRO Other RFI';
721 l_statutory_balance(177).balance_name := 'Annual Med Costs Pd by ER IRO Other NRFI';
722 l_statutory_balance(178).balance_name := 'Annual Med Costs Pd by ER IRO Other PKG';
723 l_statutory_balance(179).balance_name := 'Med Costs Pd by ER IRO Other RFI';
724 l_statutory_balance(180).balance_name := 'Med Costs Pd by ER IRO Other NRFI';
725 l_statutory_balance(181).balance_name := 'Med Costs Pd by ER IRO Other PKG';
726 l_statutory_balance(182).balance_name := 'Medical Contributions Abatement';
727 l_statutory_balance(183).balance_name := 'Annual Medical Contributions Abatement';
731 l_statutory_balance(187).balance_name := 'Med Costs Dmd Pd by EE EE_Family PKG';
728 l_statutory_balance(184).balance_name := 'Medical Fund Capping Amount';
729 l_statutory_balance(185).balance_name := 'Med Costs Dmd Pd by EE EE_Family RFI';
730 l_statutory_balance(186).balance_name := 'Med Costs Dmd Pd by EE EE_Family NRFI';
732 l_statutory_balance(188).balance_name := 'Annual Med Costs Dmd Pd by EE EE_Family RFI';
733 l_statutory_balance(189).balance_name := 'Annual Med Costs Dmd Pd by EE EE_Family NRFI';
734 l_statutory_balance(190).balance_name := 'Annual Med Costs Dmd Pd by EE EE_Family PKG';
735 l_statutory_balance(191).balance_name := 'Med Costs Dmd Pd by EE Other RFI';
736 l_statutory_balance(192).balance_name := 'Med Costs Dmd Pd by EE Other NRFI';
737 l_statutory_balance(193).balance_name := 'Med Costs Dmd Pd by EE Other PKG';
738 l_statutory_balance(194).balance_name := 'Annual Med Costs Dmd Pd by EE Other RFI';
739 l_statutory_balance(195).balance_name := 'Annual Med Costs Dmd Pd by EE Other NRFI';
740 l_statutory_balance(196).balance_name := 'Annual Med Costs Dmd Pd by EE Other PKG';
741 l_statutory_balance(197).balance_name := 'Non Taxable Med Costs Pd by ER';
742 -- End: TYS 06-07 Changes
743
744 -- Begin: TYE 08 Changes
745 l_statutory_balance(198).balance_name := 'Employers Retirement Annuity Fund Contributions';
746 l_statutory_balance(199).balance_name := 'Employers Premium paid on Loss of Income Policies';
747 l_statutory_balance(200).balance_name := 'Medical Contr Pd by ER for Retired EE';
748 l_statutory_balance(201).balance_name := 'Surplus Apportionment';
749 l_statutory_balance(202).balance_name := 'Unclaimed Benefits';
750 l_statutory_balance(203).balance_name := 'Retire Pen RAF Prov Fund Ben on Ret or Death RFI';
751 l_statutory_balance(204).balance_name := 'Retire Pen RAF Prov Fund Ben on Ret or Death NRFI';
752 l_statutory_balance(205).balance_name := 'Tax on Retirement Fund Lump Sums';
753 -- End: TYE 08 Changes
754
755 hr_utility.set_location('Step = ' || l_proc, 30);
756
757 for l_index in 1..l_max_stat_balance loop
758
759 hr_utility.set_location('l_index = ' || l_index, 30);
760 hr_utility.set_location('balance_name = ' || l_statutory_balance(l_index).balance_name, 30);
761 hr_utility.set_location('l_dimension = ' || l_dimension, 30);
762
763 open csr_balance_dimension(l_statutory_balance(l_index).balance_name, l_dimension);
764 fetch csr_balance_dimension
765 into l_statutory_bal_id(l_index).defined_balance_id;
766
767 if csr_balance_dimension%notfound then
768
769 l_statutory_bal_id(l_index).defined_balance_id := 0;
770
771 end if;
772
773 close csr_balance_dimension;
774
775 hr_utility.set_location('defined_balance_id = ' || l_statutory_bal_id(l_index).defined_balance_id, 30);
776
777 end loop;
778
779 hr_utility.set_location('Step = ' || l_proc, 40);
780
781 hr_utility.set_location('l_max_stat_balance = ' || l_max_stat_balance, 40);
782 hr_utility.set_location('g_max_user_balance_index = ' || g_max_user_balance_index, 40);
783
784 for l_index in 1..l_max_stat_balance loop
785
786 l_found := 'N';
787
788 for l_eit_index in 1..g_max_user_balance_index loop
789
790 hr_utility.set_location('l_index = ' || l_index, 40);
791 hr_utility.set_location('l_eit_index = ' || l_eit_index, 40);
792 hr_utility.set_location('defined_balance_id = ' || l_statutory_bal_id(l_index).defined_balance_id, 40);
793 hr_utility.set_location('l_found = ' || l_found, 40);
794
795 if l_statutory_bal_id(l_index).defined_balance_id
796 = g_user_balance_table(l_eit_index).defined_balance_id then
797
798 l_found := 'Y';
799
800 end if;
801
802 end loop;
803
804 if l_found = 'N' then
805
806 hr_utility.set_location('l_archive_index = ' || l_archive_index, 40);
807
808 l_archive_index := l_archive_index + 1;
809 g_statutory_balance_table(l_archive_index).defined_balance_id := l_statutory_bal_id(l_index).defined_balance_id;
810
811 end if;
812
813 end loop;
814
815 g_max_statutory_balance_index := l_archive_index;
816
817 hr_utility.set_location('Step ' || l_proc, 50);
818 hr_utility.set_location('l_archive_index = ' || l_archive_index, 50);
819
820 hr_utility.set_location('Leaving ' || l_proc, 60);
821
822 end setup_standard_balance_table;
823
824 procedure arch_za_pay_action_level_data(
825 p_payroll_action_id in number
826 ,p_payroll_id in number
827 ,p_effective_date in date) is
828
829 cursor csr_get_organization(p_payroll_id number
830 ,p_effective_date date) is
831 select distinct paei.aei_information7 legal_entity_id
832 from per_all_assignments_f paa,
833 per_assignment_extra_info paei
834 where paa.payroll_id = p_payroll_id
835 and p_effective_date between paa.effective_start_date
836 and paa.effective_end_date
837 and paa.assignment_id = paei.assignment_id
838 and paei.information_type = 'ZA_SPECIFIC_INFO'
839 and not exists (select 1
840 from per_all_assignments_f paa1
844 and paa1.effective_end_date);
841 where paa1.payroll_id = p_payroll_id
842 and paa1.organization_id = paei.aei_information7
843 And p_effective_date between paa1.effective_start_date
845 -- added effective date condition in "not exists" clasuse on 02-Jul-06
846
847
848 cursor csr_legal_entity_details(p_legal_entity_id number) is
849 select hou.name legal_entity_name,
850 hl.address_line_1,
851 hl.address_line_2,
852 hl.address_line_3,
853 hl.town_or_city,
854 hl.region_1,
855 hl.region_2,
856 hl.region_3,
857 hl.postal_code,
858 hl.country,
859 hl.telephone_number_1
860 from hr_locations hl,
861 hr_organization_units hou
862 where hou.organization_id = p_legal_entity_id
863 and hou.location_id = hl.location_id;
864
865 l_action_information_id number(15);
866 l_ovn number;
867 l_name varchar2(240);
868 l_address_line_1 varchar2(240);
869 l_address_line_2 varchar2(240);
870 l_address_line_3 varchar2(240);
871 l_town_or_city varchar2(30);
872 l_region_1 varchar2(120);
873 l_region_2 varchar2(120);
874 l_region_3 varchar2(120);
875 l_postal_code varchar2(30);
876 l_country varchar2(60);
877 l_telephone_number_1 varchar2(60);
878
879 l_proc varchar2(60) ;
880
881 begin
882 l_proc := g_package || 'arch_za_pay_action_level_data';
883 -- Removed default assignment to remove GSCC warning
884
885 hr_utility.set_location('Entering ' || l_proc, 10);
886
887 hr_utility.set_location('Step ' || l_proc, 20);
888
889 for rec_get_organization in csr_get_organization(p_payroll_id,p_effective_date)
890
891 loop
892
893 open csr_legal_entity_details(rec_get_organization.legal_entity_id);
894
895 fetch csr_legal_entity_details into l_name,
896 l_address_line_1,
897 l_address_line_2,
898 l_address_line_3,
899 l_town_or_city,
900 l_region_1,
901 l_region_2,
902 l_region_3,
903 l_postal_code,
904 l_country,
905 l_telephone_number_1;
906
907 close csr_legal_entity_details;
908
909 pay_action_information_api.create_action_information(
910 p_action_information_id => l_action_information_id,
911 p_action_context_id => p_payroll_action_id, -- Payroll Action of the Archiver
912 p_action_context_type => 'PA',
913 p_object_version_number => l_ovn,
914 p_effective_date => p_effective_date, -- Effective Date of the Prepayments
915 p_source_id => null,
916 p_source_text => null,
917 p_action_information_category => 'ADDRESS DETAILS',
918 p_action_information1 => rec_get_organization.legal_entity_id,
919 p_action_information2 => null,
920 p_action_information3 => null,
921 p_action_information4 => null,
922 p_action_information5 => l_address_line_1,
923 p_action_information6 => l_address_line_2,
924 p_action_information7 => l_address_line_3,
925 p_action_information8 => l_town_or_city,
926 p_action_information9 => l_region_1,
927 p_action_information10 => l_region_2,
928 p_action_information11 => l_region_3,
929 p_action_information12 => l_postal_code,
930 p_action_information13 => l_country,
931 p_action_information14 => 'Employer Address'
932 );
933
934 end loop;
935
936 hr_utility.set_location('Leaving ' || l_proc, 30);
937
938 end arch_za_pay_action_level_data;
939
940 procedure update_employee_information(
941 p_action_context_id in number
942 ,p_assignment_id in number) is
943
944 cursor csr_get_archive_info(p_action_context_id number
945 ,p_assignment_id number) is
946 select action_information_id,
947 effective_date,
948 object_version_number
949 from pay_action_information
950 where action_context_id = p_action_context_id
951 and action_context_type = 'AAP'
952 and assignment_id = p_assignment_id
953 and action_information_category = 'EMPLOYEE DETAILS';
954
955 cursor csr_emp_legal_entity(p_assignment_id number) is
956 select paei.aei_information7 legal_entity_id,
957 hou.name legal_entity_name,
958 hl.telephone_number_1
959 from per_assignment_extra_info paei,
960 hr_organization_units hou,
961 hr_locations hl
962 where paei.assignment_id = p_assignment_id
966
963 and paei.information_type = 'ZA_SPECIFIC_INFO'
964 and paei.aei_information7 = hou.organization_id
965 and hou.location_id = hl.location_id;
967 l_action_information_id number(15);
968 l_effective_date date;
969 l_ovn number;
970 l_legal_entity_id varchar2(150);
971 l_legal_entity_name varchar2(240);
972 l_telephone_number_1 varchar2(60);
973
974 l_proc varchar2(60);
975
976 begin
977 l_proc := g_package || 'update_employee_information';
978 -- Removed default assignment to remove GSCC warning
979 hr_utility.set_location('Entering ' || l_proc, 10);
980
981 hr_utility.set_location('Step ' || l_proc, 20);
982
983 open csr_get_archive_info(p_action_context_id,p_assignment_id);
984
985 loop
986
987 fetch csr_get_archive_info into l_action_information_id,
988 l_effective_date,
989 l_ovn;
990
991 if csr_get_archive_info%notfound then
992 exit;
993 end if;
994
995 open csr_emp_legal_entity(p_assignment_id);
996
997 fetch csr_emp_legal_entity into l_legal_entity_id,
998 l_legal_entity_name,
999 l_telephone_number_1;
1000
1001 close csr_emp_legal_entity;
1002
1003 -- telephone number should be archived in action_information25
1004 -- but for ZA, address_line4 is stored in hl.telephone_number_1
1005 -- so action_information25 is set to null until this is resolved
1006
1007 pay_action_information_api.update_action_information(
1008 p_action_information_id => l_action_information_id,
1009 p_object_version_number => l_ovn,
1010 p_action_information18 => l_legal_entity_name,
1011 p_action_information25 => NULL
1012 );
1013
1014 update pay_action_information
1015 set tax_unit_id = l_legal_entity_id
1016 where action_information_id = l_action_information_id;
1017
1018 end loop;
1019 close csr_get_archive_info;
1020
1021 hr_utility.set_location('Leaving ' || l_proc, 30);
1022
1023 end update_employee_information;
1024
1025 -- This procedure gets called third to do initialization.
1026 -- The procedure gets called once for each concurrent sub process?
1027 procedure archinit(p_payroll_action_id in number) is -- Payroll Action of the Archiver
1028
1029 cursor csr_archive_effective_date(pactid number) is
1030 select effective_date
1031 from pay_payroll_actions
1032 where payroll_action_id = pactid;
1033
1034 cursor csr_input_value_id
1035 (
1036 p_element_name varchar2,
1037 p_value_name varchar2
1038 ) is
1039 select piv.input_value_id
1040 from pay_input_values_f piv,
1041 pay_element_types_f pet
1042 where piv.element_type_id = pet.element_type_id
1043 and pet.legislation_code = 'ZA'
1044 and pet.element_name = p_element_name
1045 and piv.name = p_value_name;
1046
1047 -- Returns all prepayments for the specified parameters
1048 cursor csr_payroll_info
1049 (
1050 p_payroll_id number,
1051 p_consolidation_id number,
1052 p_start_date date,
1053 p_end_date date
1054 ) is
1055 select pact.payroll_action_id payroll_action_id,
1056 pact.effective_date effective_date
1057 from pay_payrolls_f ppf,
1058 pay_payroll_actions pact -- Payroll Action of Prepayments
1059 where pact.payroll_id = ppf.payroll_id
1060 and pact.effective_date between ppf.effective_start_date and ppf.effective_end_date
1061 and pact.payroll_id = nvl(p_payroll_id, pact.payroll_id)
1062 and pact.consolidation_set_id = p_consolidation_id
1063 and pact.effective_date between p_start_date and p_end_date
1064 and
1065 (
1066 pact.action_type = 'P'
1067 or
1068 pact.action_type = 'U'
1069 )
1070 and pact.action_status = 'C';
1071
1072 l_payroll_id number;
1073 l_consolidation_set number;
1074 l_assignment_set_id number;
1075 l_start_date varchar2(30);
1076 l_end_date varchar2(30);
1077 l_bg_id number;
1078 l_canonical_end_date date;
1079 l_canonical_start_date date;
1080
1081 l_proc varchar2(50);
1082
1083 begin
1084 l_proc := g_package || 'archinit';
1085 -- Removed default assignment to remove GSCC warning
1086
1087 -- hr_utility.trace_on(null,'ZA_SOE');
1088 hr_utility.set_location('Entering ' || l_proc, 10);
1089
1090 g_archive_pact := p_payroll_action_id; -- Payroll Action of the Archiver
1091
1092 -- Get the effective date of the payroll action
1093 open csr_archive_effective_date(p_payroll_action_id); -- Payroll Action of the Archiver
1094 fetch csr_archive_effective_date into g_archive_effective_date;
1095 close csr_archive_effective_date;
1096
1097 -- Retrieve the legislative parameters from the payroll action
1098 pay_za_payslip_archive.get_parameters
1099 (
1100 p_payroll_action_id => p_payroll_action_id, -- Payroll Action of the Archiver
1101 p_token_name => 'PAYROLL',
1105 pay_za_payslip_archive.get_parameters
1102 p_token_value => l_payroll_id
1103 );
1104
1106 (
1107 p_payroll_action_id => p_payroll_action_id,
1108 p_token_name => 'CONSOLIDATION',
1109 p_token_value => l_consolidation_set
1110 );
1111
1112 pay_za_payslip_archive.get_parameters
1113 (
1114 p_payroll_action_id => p_payroll_action_id,
1115 p_token_name => 'ASSIGNMENT_SET',
1116 p_token_value => l_assignment_set_id
1117 );
1118
1119 pay_za_payslip_archive.get_parameters
1120 (
1121 p_payroll_action_id => p_payroll_action_id,
1122 p_token_name => 'START_DATE',
1123 p_token_value => l_start_date
1124 );
1125
1126 pay_za_payslip_archive.get_parameters
1127 (
1128 p_payroll_action_id => p_payroll_action_id,
1129 p_token_name => 'END_DATE',
1130 p_token_value => l_end_date
1131 );
1132
1133 pay_za_payslip_archive.get_parameters
1134 (
1135 p_payroll_action_id => p_payroll_action_id,
1136 p_token_name => 'BG_ID',
1137 p_token_value => l_bg_id
1138 );
1139
1140 hr_utility.set_location('Step ' || l_proc, 20);
1141 hr_utility.set_location('l_payroll_id = ' || l_payroll_id, 20);
1142 hr_utility.set_location('l_start_date = ' || l_start_date, 20);
1143 hr_utility.set_location('l_end_date = ' || l_end_date, 20);
1144
1145 l_canonical_start_date := to_date(l_start_date,'yyyy/mm/dd');
1146 l_canonical_end_date := to_date(l_end_date,'yyyy/mm/dd');
1147
1148 -- Retrieve id for tax element
1149 open csr_input_value_id('ZA_Tax','Tax Status');
1150 fetch csr_input_value_id into g_tax_element_id;
1151 close csr_input_value_id;
1152
1153 hr_utility.set_location('l_payroll_id = ' || l_payroll_id, 20);
1154 hr_utility.set_location('l_consolidation_set = ' || l_consolidation_set, 20);
1155 hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date, 20);
1156 hr_utility.set_location('l_canonical_end_date = ' || l_canonical_end_date, 20);
1157
1158 -- Loop through all the prepayments for the specified parameters
1159 for rec_payroll_info in csr_payroll_info
1160 (
1161 l_payroll_id,
1162 l_consolidation_set,
1163 l_canonical_start_date,
1164 l_canonical_end_date
1165 )
1166 loop
1167
1168 -- Retrieve and archive user definitions from EITs
1169 -- The definitions are archived again for each prepayment
1170 g_max_user_balance_index := 0;
1171
1172 hr_utility.set_location('get_eit_definitions - balances', 20);
1173
1174 pay_za_payslip_archive.get_eit_definitions
1175 (
1176 p_pactid => p_payroll_action_id, -- Payroll Action of the Archiver
1177 p_business_group_id => l_bg_id,
1178 p_payroll_pact => rec_payroll_info.payroll_action_id, -- Payroll Action of the Prepayment
1179 p_effective_date => rec_payroll_info.effective_date, -- Effective Date of the Prepayment
1180 p_eit_context => g_balance_context,
1181 p_archive => 'N'
1182 );
1183
1184 hr_utility.set_location('get_eit_definitions - elements', 20);
1185
1186 pay_za_payslip_archive.get_eit_definitions
1187 (
1188 p_pactid => p_payroll_action_id, -- Payroll Action of the Archiver
1189 p_business_group_id => l_bg_id,
1190 p_payroll_pact => rec_payroll_info.payroll_action_id, -- Payroll Action of the Prepayment
1191 p_effective_date => rec_payroll_info.effective_date, -- Effective Date of the Prepayment
1192 p_eit_context => g_element_context,
1193 p_archive => 'N'
1194 );
1195
1196 -- Set the Payroll Action ID context to the Payroll Action of the current Prepayment
1197 -- Note: It takes the last value it gets set to
1198 -- FIX must be an error
1199 pay_balance_pkg.set_context
1200 (
1201 'PAYROLL_ACTION_ID',
1202 rec_payroll_info.payroll_action_id
1203 );
1204
1205 end loop;
1206
1207 -- Setup statutory balances pl/sql table
1208 pay_za_payslip_archive.setup_standard_balance_table;
1209
1210 hr_utility.set_location('Leaving ' || l_proc, 20);
1211
1212 end archinit;
1213
1214 procedure archive_employee_details
1215 (
1216 p_assactid in number,
1217 p_assignment_id in number,
1218 p_curr_pymt_ass_act_id in number,
1219 p_date_earned in date,
1220 p_curr_pymt_eff_date in date,
1221 p_time_period_id in number
1222 ) is
1223
1224 l_action_info_id number;
1225 l_person_id number;
1226 l_ovn number;
1227 l_tax_status varchar2(60);
1228 l_tax_status_meaning varchar2(80);
1229 l_termination_date date;
1230 l_tax_ref_number varchar2(150);
1231 l_tax_period varchar(20);
1232 l_pay_date date;
1233
1234 l_proc varchar2(50);
1235
1236 begin
1240
1237 l_proc := g_package || 'archive_employee_details';
1238 -- Removed default assignment to remove GSCC warning
1239 hr_utility.set_location('Entering ' || l_proc, 10);
1241 -- Call generic procedure to retrieve and archive all data for
1242 -- EMPLOYEE DETAILS, ADDRESS DETAILS and EMPLOYEE NET PAY DISTRIBUTION
1243 hr_utility.set_location('Calling pay_emp_action_arch', 20);
1244
1245 -- Archive Employee Details through core package
1246 pay_emp_action_arch.get_personal_information
1247 (
1248 p_payroll_action_id => g_archive_pact, -- archive payroll_action_id
1249 p_assactid => p_assactid, -- archive assignment_action_id
1250 p_assignment_id => p_assignment_id, -- current assignment_id
1251 p_curr_pymt_ass_act_id => p_curr_pymt_ass_act_id, -- prepayment assignment_action_id
1252 p_curr_eff_date => g_archive_effective_date, -- archive effective_date
1253 p_date_earned => p_date_earned, -- payroll date_earned
1254 p_curr_pymt_eff_date => p_curr_pymt_eff_date, -- prepayment effective_date
1255 p_tax_unit_id => null, -- only required for US
1256 p_time_period_id => p_time_period_id, -- payroll time_period_id
1257 p_ppp_source_action_id => null
1258 );
1259
1260 hr_utility.set_location('Returned from pay_emp_action_arch', 30);
1261
1262 -- Call procedure to archive ZA specific data in generic EMPLOYEE DETAILS
1263
1264 hr_utility.set_location('Calling update_employee_information', 40);
1265
1266 update_employee_information
1267 (
1268 p_action_context_id => p_assactid,
1269 p_assignment_id => p_assignment_id
1270 );
1271
1272 hr_utility.set_location('Returned from update_employee_information', 50);
1273
1274 -- Retrieve the ZA specific employee details
1275 -- Person Id
1276 select max(person_id)
1277 into l_person_id
1278 from per_all_assignments_f
1279 where assignment_id = p_assignment_id;
1280
1281 -- Tax Reference Number (Income Tax Number)
1282 begin
1283
1284 select max(per_information1)
1285 into l_tax_ref_number
1286 from per_all_people_f papf
1287 where papf.person_id = l_person_id
1288 and papf.current_employee_flag = 'Y'
1289 and per_information_category = 'ZA'
1290 and g_archive_effective_date between effective_start_date and effective_end_date; -- Bug 4204930
1291
1292 exception
1293 when no_data_found then
1294 l_tax_ref_number := null;
1295
1296 end;
1297
1298 -- Tax Status
1299 begin
1300
1301 select peevf.screen_entry_value
1302 into l_tax_status
1303 from pay_element_entries_f peef,
1304 pay_element_entry_values_f peevf
1305 where peef.assignment_id = p_assignment_id
1306 and peevf.input_value_id = g_tax_element_id
1307 and peef.element_entry_id = peevf.element_entry_id
1308 and peef.effective_start_date <= g_archive_effective_date -- Bug 3513520
1309 and peef.effective_end_date >= g_archive_effective_date -- Bug 3513520
1310 and peevf.effective_start_date = peef.effective_start_date; -- Bug 3513520
1311
1312 if g_tax_status is null or g_tax_status <> l_tax_status then
1313
1314 select meaning
1315 into g_tax_status_meaning
1316 from hr_lookups
1317 where lookup_type = 'ZA_TAX_STATUS'
1318 and application_id = 800
1319 and lookup_code = l_tax_status;
1320
1321 g_tax_status := l_tax_status;
1322 l_tax_status_meaning := g_tax_status_meaning;
1323
1324 else
1325
1326 l_tax_status_meaning := g_tax_status_meaning;
1327
1328 end if;
1329
1330 exception
1331 when no_data_found then
1332 l_tax_status_meaning := null;
1333
1334 end;
1335
1336 -- Tax Period
1337 begin
1338
1339 select period_num, cut_off_date
1340 into l_tax_period, l_pay_date
1341 from per_time_periods
1342 where time_period_id = p_time_period_id;
1343
1344 exception
1345 when no_data_found then
1346 l_tax_period := null;
1347
1348 end;
1349
1350 -- Termination Date
1351 begin
1352
1353 select decode(to_char(max(papf.effective_end_date), 'dd/mm/yyyy'), '31/12/4712', null, max(papf.effective_end_date))
1354 into l_termination_date
1355 from per_all_people_f papf
1356 where papf.person_id = l_person_id
1357 and papf.current_employee_flag = 'Y';
1358
1359 exception
1360 when no_data_found then
1361 l_termination_date := null;
1362
1363 end;
1364
1365 hr_utility.set_location('Archiving ZA EMPLOYEE DETAILS', 50);
1366
1367 -- Archive the ZA specific employee details
1368 pay_action_information_api.create_action_information
1369 (
1370 p_action_information_id => l_action_info_id,
1371 p_action_context_id => p_assactid,
1372 p_action_context_type => 'AAP',
1373 p_object_version_number => l_ovn,
1374 p_assignment_id => p_assignment_id,
1378 p_action_information_category => 'ZA EMPLOYEE DETAILS',
1375 p_effective_date => g_archive_effective_date,
1376 p_source_id => null,
1377 p_source_text => null,
1379 p_action_information1 => null,
1380 p_action_information2 => null,
1381 p_action_information3 => null,
1382 p_action_information21 => l_tax_ref_number,
1383 p_action_information22 => l_tax_status_meaning,
1384 p_action_information23 => l_tax_period,
1385 p_action_information24 => fnd_date.date_to_displaydate(l_termination_date), -- Bug 3513520
1386 p_action_information25 => fnd_date.date_to_displaydate(l_pay_date) -- Bug 3513520
1387 );
1388
1389 end archive_employee_details;
1390
1391 procedure process_balance
1392 (
1393 p_action_context_id in number,
1394 p_assignment_id in number,
1395 p_source_id in number,
1396 p_effective_date in date,
1397 p_balance in varchar2,
1398 p_dimension in varchar2,
1399 p_defined_bal_id in number,
1400 p_record_count in number
1401 ) is
1402
1403 l_action_info_id number;
1404 l_balance_value number;
1405 l_ovn number;
1406 l_record_count varchar2(10);
1407
1408 l_proc varchar2(50);
1409
1410 begin
1411 l_proc := g_package || 'process_balance';
1412 -- Removed default assignment to remove GSCC warning
1413 hr_utility.set_location('Entering ' || l_proc, 10);
1414
1415 hr_utility.set_location('Step ' || l_proc, 20);
1416 hr_utility.set_location('p_source_id = ' || p_source_id, 20);
1417 hr_utility.set_location('p_balance = ' || p_balance, 20);
1418 hr_utility.set_location('p_dimension = ' || p_dimension, 20);
1419 hr_utility.set_location('p_defined_bal_id = ' || p_defined_bal_id, 20);
1420
1421 l_balance_value := pay_balance_pkg.get_value
1422 (
1423 p_defined_balance_id => p_defined_bal_id,
1424 p_assignment_action_id => p_source_id
1425 );
1426
1427 hr_utility.set_location('l_balance_value = ' || l_balance_value, 20);
1428
1429 if p_record_count = 0 then
1430
1431 l_record_count := null;
1432
1433 else
1434
1435 l_record_count := p_record_count + 1;
1436
1437 end if;
1438
1439 if l_balance_value <> 0 then
1440
1441 hr_utility.set_location('Archiving EMEA BALANCES', 20);
1442
1443 pay_action_information_api.create_action_information
1444 (
1445 p_action_information_id => l_action_info_id,
1446 p_action_context_id => p_action_context_id,
1447 p_action_context_type => 'AAP',
1448 p_object_version_number => l_ovn,
1449 p_assignment_id => p_assignment_id,
1450 p_effective_date => p_effective_date,
1451 p_source_id => p_source_id,
1452 p_source_text => null,
1453 p_action_information_category => 'EMEA BALANCES',
1454 p_action_information1 => p_defined_bal_id,
1455 p_action_information2 => null,
1456 p_action_information3 => null,
1457 p_action_information4 => fnd_number.number_to_canonical(l_balance_value),
1458 p_action_information5 => l_record_count
1459 );
1460
1461 end if;
1462
1463 hr_utility.set_location('Leaving ' || l_proc, 30);
1464
1465 exception
1466 when no_data_found then
1467 null;
1468
1469 end process_balance;
1470
1471 procedure get_element_info
1472 (
1473 p_action_context_id in number,
1474 p_assignment_id in number,
1475 p_child_assignment_action in number,
1476 p_effective_date in date,
1477 p_record_count in number,
1478 p_run_method in varchar2
1479 ) is
1480
1481 cursor csr_element_values
1482 (
1483 p_assignment_action_id number,
1484 p_element_type_id number,
1485 p_input_value_id number
1486 ) is
1487 select prv.result_value
1488 from pay_run_result_values prv,
1489 pay_run_results prr
1490 where prr.status in ('P', 'PA')
1491 and prv.run_result_id = prr.run_result_id
1492 and prr.assignment_action_id = p_assignment_action_id
1493 and prr.element_type_id = p_element_type_id
1494 and prv.input_value_id = p_input_value_id
1495 and prv.result_value is not null;
1496
1497 l_column_sequence number;
1498 l_element_type_id number;
1499 l_main_sequence number;
1500 l_multi_sequence number;
1501 l_action_info_id number;
1502 l_ovn number;
1503 l_record_count varchar2(10);
1504
1505 l_proc varchar2(50) ;
1506
1507 begin
1508
1509 l_proc := g_package || 'get_element_info';
1510 -- Removed default assignment to remove GSCC warning
1511
1512 hr_utility.set_location('Entering ' || l_proc, 10);
1513
1514 l_column_sequence := 0;
1515 l_element_type_id := 0;
1519 if p_record_count = 0 then
1516 l_main_sequence := 0;
1517 l_multi_sequence := null;
1518
1520
1521 l_record_count := null;
1522
1523 else
1524
1525 l_record_count := p_record_count + 1;
1526
1527 end if;
1528
1529 hr_utility.set_location('g_max_element_index = ' || g_max_element_index, 10);
1530
1531 for l_index in 1 .. g_max_element_index loop
1532
1533 hr_utility.set_location('element_type_id = ' || g_element_table(l_index).element_type_id, 10);
1534 hr_utility.set_location('input_value_id = ' || g_element_table(l_index).input_value_id, 10);
1535 hr_utility.set_location('p_child_assignment_action = ' || p_child_assignment_action, 10);
1536
1537 for rec_element_value in csr_element_values
1538 (
1539 p_child_assignment_action,
1540 g_element_table(l_index).element_type_id,
1541 g_element_table(l_index).input_value_id
1542 )
1543
1544 loop
1545
1546 hr_utility.set_location('element_type_id = ' || g_element_table(l_index).element_type_id, 10);
1547 hr_utility.set_location('input_value_id = ' || g_element_table(l_index).input_value_id, 10);
1548 hr_utility.set_location('Archiving EMEA ELEMENT INFO', 20);
1549
1550 if l_element_type_id <> g_element_table(l_index).element_type_id then
1551
1552 l_main_sequence := l_main_sequence + 1;
1553
1554 end if;
1555
1556 hr_utility.set_location('l_main_sequence = ' || l_main_sequence, 20);
1557
1558 l_column_sequence := l_column_sequence + 1;
1559
1560 -- If the run method is P, Process Separate, then only archive the data if
1561 -- a skip rule (formula_id) OR Once Each Period Flag has been set.
1562 -- If there is no skip rule then the element info will be archived for
1563 -- the normal assignment action and doesn't need to be archived twice.
1564 -- If it is then duplicates will be displayed on the payslip.
1565
1566 /*if p_run_method = 'P' and g_element_table(l_index).formula_id is null then*/
1567 --Added for Bug 4488264
1568 if ((p_run_method = 'P' and g_element_table(l_index).formula_id is null)
1569 OR
1570 (p_run_method = 'P' and nvl(g_element_table(l_index).once_each_period_flag,'N') <> 'Y')) then
1571
1572 null;
1573
1574 else
1575
1576 pay_action_information_api.create_action_information
1577 (
1578 p_action_information_id => l_action_info_id,
1579 p_action_context_id => p_action_context_id,
1580 p_action_context_type => 'AAP',
1581 p_object_version_number => l_ovn,
1582 p_assignment_id => p_assignment_id,
1583 p_effective_date => p_effective_date,
1584 p_source_id => p_child_assignment_action,
1585 p_source_text => null,
1586 p_action_information_category => 'EMEA ELEMENT INFO',
1587 p_action_information1 => g_element_table(l_index).element_type_id,
1588 p_action_information2 => g_element_table(l_index).input_value_id,
1589 p_action_information3 => null,
1590 p_action_information4 => rec_element_value.result_value,
1591 p_action_information5 => l_main_sequence,
1592 p_action_information6 => l_multi_sequence,
1593 p_action_information7 => l_column_sequence
1594 );
1595
1596 end if;
1597
1598 l_multi_sequence := nvl(l_multi_sequence, 0) + 1;
1599 l_element_type_id := g_element_table(l_index).element_type_id;
1600
1601 end loop;
1602
1603 l_multi_sequence := null;
1604
1605 end loop;
1606
1607 exception
1608 when no_data_found then
1609 null;
1610
1611 end get_element_info;
1612
1613 -- Public procedure which archives the payroll information, then returns a
1614 -- varchar2 defining a SQL statement to select all the people that may be
1615 -- eligible for payslip reports.
1616 -- The archiver uses this cursor to split the people into chunks for parallel
1617 -- processing.
1618 -- This procedure gets called first to determine which person_id's to process
1619 procedure range_cursor
1620 (
1621 pactid in number, -- Payroll Action of the Archiver
1622 sqlstr out nocopy varchar2
1623 ) is
1624
1625 -- Variables for constructing the sqlstr
1626 l_range_cursor VARCHAR2(4000) := NULL;
1627 l_parameter_match VARCHAR2(500) := NULL;
1628
1629 l_request_id NUMBER;
1630 l_business_group_id NUMBER;
1631
1632 CURSOR csr_input_value_id(p_element_name CHAR,
1633 p_value_name CHAR) IS
1634 SELECT pet.element_type_id,
1635 piv.input_value_id
1636 FROM pay_input_values_f piv,
1637 pay_element_types_f pet
1638 WHERE piv.element_type_id = pet.element_type_id
1639 AND pet.legislation_code = 'ZA'
1640 AND pet.element_name = p_element_name
1644 /* This cursor to get payrolls based on a given consolidation set is
1641 AND piv.name = p_value_name;
1642 --
1643 -- Start of comment for Bug 4153551
1645 is not consistent with other processes like prePayments and Cheque Writer etc.
1646 --
1647 -- New cursor for processing archive information by payroll
1648 CURSOR csr_payrolls (p_payroll_id NUMBER,
1649 p_consolidation_set_id NUMBER,
1650 p_effective_date DATE) IS
1651 SELECT ppf.payroll_id
1652 FROM pay_all_payrolls_f ppf
1653 WHERE ppf.consolidation_set_id = p_consolidation_set_id
1654 AND ppf.payroll_id = NVL(p_payroll_id,ppf.payroll_id)
1655 AND p_effective_date BETWEEN
1656 ppf.effective_start_date AND ppf.effective_end_date;
1657 --
1658 -- Emd of comment for Bug 4153551 */
1659 --
1660 -- Returns all prepayments for the specified parameters that has not been archived yet
1661 cursor csr_payroll_info
1662 (p_payroll_id number
1663 ,p_consolidation_id number
1664 ,p_start_date date
1665 ,p_end_date date
1666 ) is
1667 select pact.payroll_action_id payroll_action_id, -- Payroll Action of Prepayments
1668 pact.effective_date effective_date, -- Effective Date of Prepayments
1669 pact.date_earned date_earned,
1670 pact.payroll_id,
1671 ppf.payroll_name payroll_name,
1672 ppf.period_type period_type,
1673 pact.pay_advice_message payroll_message
1674 from pay_payrolls_f ppf,
1675 pay_payroll_actions pact -- Payroll Action of Prepayments
1676 where pact.payroll_id = ppf.payroll_id
1677 and pact.effective_date between ppf.effective_start_date and ppf.effective_end_date
1678 and pact.payroll_id = nvl(p_payroll_id, pact.payroll_id)
1679 and pact.consolidation_set_id = p_consolidation_id
1680 and pact.effective_date between p_start_date and p_end_date
1681 and (pact.action_type = 'P' or pact.action_type = 'U')
1682 and pact.action_status = 'C'
1683 and not exists
1684 (
1685 select null
1686 from pay_action_information pai
1687 where pai.action_context_id = pact.payroll_action_id -- Payroll Action of Prepayments
1688 and pai.action_context_type = 'PA'
1689 and pai.action_information_category = 'EMEA PAYROLL INFO'
1690 );
1691
1692 -- Returns all the Pay Advice messages that have not been archived for the specificied
1693 -- payroll and date range
1694 -- FIX will not work for multiple payrolls (multiple payrolls possible?)
1695 cursor csr_payroll_mesg
1696 (
1697 p_payroll_id number,
1698 p_start_date date,
1699 p_end_date date
1700 ) is
1701 select pact.payroll_action_id payroll_action_id,
1702 pact.effective_date effective_date,
1703 pact.date_earned date_earned,
1704 pact.pay_advice_message payroll_message
1705 from pay_payrolls_f ppf,
1706 pay_payroll_actions pact -- Payroll Action of Run
1707 where pact.payroll_id = ppf.payroll_id
1708 and pact.effective_date between ppf.effective_start_date and ppf.effective_end_date
1709 and pact.payroll_id = p_payroll_id
1710 and pact.effective_date between p_start_date and p_end_date
1711 and (pact.action_type = 'R' or pact.action_type = 'Q')
1712 and pact.action_status = 'C'
1713 and not exists
1714 (
1715 select null
1716 from pay_action_information pai
1717 where pai.action_context_id = pact.payroll_action_id -- FIX can't user payroll action id of Run
1718 and pai.action_context_type = 'PA' -- should be PA of archiver
1719 and pai.action_information_category = 'EMPLOYEE OTHER INFORMATION'
1720 );
1721
1722 l_action_info_id number(15);
1723 l_ovn number(15);
1724
1725 l_payroll_id number;
1726 l_consolidation_set number;
1727 l_assignment_set_id number;
1728 l_start_date varchar2(30);
1729 l_end_date varchar2(30);
1730 l_bg_id number;
1731 l_canonical_start_date date;
1732 l_canonical_end_date date;
1733
1734 l_legislation_code VARCHAR2(30) ;
1735 l_tax_period_no VARCHAR2(30);
1736
1737
1738 l_proc constant varchar2(50) := g_package || 'range_cursor';
1739
1740 begin
1741 l_legislation_code := 'ZA';
1742 -- Removed default assignment to remove GSCC warning
1743 --
1744 --hr_utility.trace_on(null, 'ZA_SOE');
1745 hr_utility.set_location('Entering ' || l_proc, 10);
1746
1747 -- Retrieve the legislative parameters from the payroll action
1748 pay_za_payslip_archive.get_parameters
1749 (
1750 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
1751 p_token_name => 'PAYROLL',
1752 p_token_value => l_payroll_id
1753 );
1754
1755 pay_za_payslip_archive.get_parameters
1756 (
1757 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
1758 p_token_name => 'CONSOLIDATION',
1759 p_token_value => l_consolidation_set
1760 );
1761
1762 pay_za_payslip_archive.get_parameters
1763 (
1764 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
1765 p_token_name => 'ASSIGNMENT_SET',
1769 pay_za_payslip_archive.get_parameters
1766 p_token_value => l_assignment_set_id
1767 );
1768
1770 (
1771 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
1772 p_token_name => 'START_DATE',
1773 p_token_value => l_start_date
1774 );
1775
1776 pay_za_payslip_archive.get_parameters
1777 (
1778 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
1779 p_token_name => 'END_DATE',
1780 p_token_value => l_end_date
1781 );
1782
1783 pay_za_payslip_archive.get_parameters
1784 (
1785 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
1786 p_token_name => 'BG_ID',
1787 p_token_value => l_bg_id
1788 );
1789
1790 hr_utility.set_location('Step ' || l_proc, 20);
1791 hr_utility.set_location('l_payroll_id = ' || l_payroll_id, 20);
1792 hr_utility.set_location('l_start_date = ' || l_start_date, 20);
1793 hr_utility.set_location('l_end_date = ' || l_end_date, 20);
1794
1795 l_canonical_start_date := to_date(l_start_date, 'yyyy/mm/dd');
1796 l_canonical_end_date := to_date(l_end_date, 'yyyy/mm/dd');
1797
1798 -- Archive EMEA PAYROLL INFO for each prepayment run identified
1799 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
1800 hr_utility.set_location('l_consolidation_set = ' || l_consolidation_set,20);
1801 hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
1802 hr_utility.set_location('l_canonical_end_date = ' || l_canonical_end_date,20);
1803 --
1804 -- Loop through all the prepayments for the specified parameters that has not been archived yet
1805 for rec_payroll_info in csr_payroll_info
1806 (l_payroll_id
1807 ,l_consolidation_set
1808 ,l_canonical_start_date
1809 ,l_canonical_end_date)
1810 loop
1811 --
1812 g_max_user_balance_index := 0;
1813 --
1814 -- Retrieve and archive user balance definitions from EITs
1815 -- The definitions are archived again for each prepayment
1816 pay_za_payslip_archive.get_eit_definitions
1817 (
1818 p_pactid => pactid, -- Payroll Action of Archiver
1819 p_business_group_id => l_bg_id,
1820 p_payroll_pact => rec_payroll_info.payroll_action_id, -- Payroll Action of Prepayments
1821 p_effective_date => rec_payroll_info.effective_date, -- Effective Date of Prepayments
1822 p_eit_context => g_balance_context,
1823 p_archive => 'Y'
1824 );
1825 --
1826 -- This archives the element definitions on the Org Developer Flexfield
1827 pay_za_payslip_archive.get_eit_definitions
1828 (
1829 p_pactid => pactid, -- Payroll Action of Archiver
1830 p_business_group_id => l_bg_id,
1831 p_payroll_pact => rec_payroll_info.payroll_action_id, -- Payroll Action of Prepayments
1832 p_effective_date => rec_payroll_info.effective_date, -- Effective Date of Prepayments
1833 p_eit_context => g_element_context,
1834 p_archive => 'Y'
1835 );
1836 --
1837 -- This archives the element definitions for each Pay Advice Classification
1838 pay_za_payslip_archive.setup_element_definitions
1839 (
1840 p_pactid => pactid, -- Payroll Action of Archiver
1841 p_payroll_pact => rec_payroll_info.payroll_action_id, -- Payroll Action of Prepayments
1842 p_effective_date => rec_payroll_info.effective_date -- Effective Date of Prepayments
1843 );
1844 end loop; -- End loop rec_payroll_info
1845 --
1846 --
1847 -- Start of comment for Bug 4153551
1848 /* This cursor to get payrolls based on a given consolidation set is
1849 is not consistent with other processes like prePayments and Cheque Writer etc.
1850
1851 FOR rec_payrolls in csr_payrolls(l_payroll_id
1852 ,l_consolidation_set
1853 ,l_canonical_end_date)
1854 LOOP
1855 -- Start Bug No 3436989 passing rec_payrolls.payroll_id parameter instead of l_payroll_id
1856 hr_utility.set_location('Calling arch_pay_action_level_data', 25);
1857 pay_emp_action_arch.arch_pay_action_level_data
1858 (p_payroll_action_id => pactid
1859 ,p_payroll_id => rec_payrolls.payroll_id -- l_payroll_id
1860 ,p_effective_date => l_canonical_end_date
1861 );
1862
1863 hr_utility.set_location('Calling arch_za_pay_action_level_data', 27);
1864 arch_za_pay_action_level_data
1865 (p_payroll_action_id => pactid
1866 ,p_payroll_id => rec_payrolls.payroll_id -- l_payroll_id
1867 ,p_effective_date => l_canonical_end_date
1868 );
1869 -- End of 3436989
1870 END LOOP; -- End loop rec_payrolls
1871 --
1872 -- End of comment for Bug 4153551 */
1873 --
1874 --
1875 for rec_payroll_info in csr_payroll_info
1876 (l_payroll_id
1877 ,l_consolidation_set
1878 ,l_canonical_start_date
1879 ,l_canonical_end_date)
1880 loop
1881 --
1882 --
1883 -- Set the Payroll Action ID context to the Payroll Action of the current Prepayment
1884 -- Note: It takes the last value it gets set to
1888 'PAYROLL_ACTION_ID',
1885 -- FIX must be an error
1886 pay_balance_pkg.set_context
1887 (
1889 rec_payroll_info.payroll_action_id
1890 );
1891 --
1892 --
1893 -- Added for Bug 4153551
1894 hr_utility.set_location('Calling arch_pay_action_level_data', 25);
1895 pay_emp_action_arch.arch_pay_action_level_data(
1896 p_payroll_action_id => pactid
1897 ,p_payroll_id => rec_payroll_info.payroll_id -- l_payroll_id (for bug 3436989)
1898 ,p_effective_date => l_canonical_end_date
1899 );
1900
1901 hr_utility.set_location('Calling arch_za_pay_action_level_data', 27);
1902 arch_za_pay_action_level_data(
1903 p_payroll_action_id => pactid
1904 ,p_payroll_id => rec_payroll_info.payroll_id -- l_payroll_id (for bug 3436989)
1905 ,p_effective_date => l_canonical_end_date
1906 );
1907 -- End Bug 4153551
1908 --
1909 --
1910 hr_utility.set_location('rec_payroll_info.payroll_action_id = ' || rec_payroll_info.payroll_action_id, 30);
1911 --
1912 hr_utility.set_location('Archiving EMEA PAYROLL INFO', 30);
1913 --
1914 -- Archive the Payroll Info in 'EMEA PAYROLL INFO'
1915 -- Note: Actually information on Org DF Tax Details References
1916 pay_action_information_api.create_action_information
1917 ( p_action_information_id => l_action_info_id
1918 ,p_action_context_id => pactid
1919 ,p_action_context_type => 'PA'
1920 ,p_object_version_number => l_ovn
1921 ,p_effective_date => rec_payroll_info.effective_date
1922 ,p_source_id => null
1923 ,p_source_text => null
1924 ,p_action_information_category => 'EMEA PAYROLL INFO'
1925 ,p_action_information1 => rec_payroll_info.payroll_action_id
1926 ,p_action_information2 => null
1927 ,p_action_information3 => null
1928 --,p_action_information4 => rec_payroll_info.tax_office_name
1929 --,p_action_information5 => rec_payroll_info.tax_office_phone_no
1930 --,p_action_information6 => rec_payroll_info.employers_ref_no
1931 );
1932
1933 -- Loop through all the Pay Advice messages that have not been archived for the specificied
1934 -- payroll and date range
1935 -- EMPLOYEE OTHER INFORMATION
1936 --
1937 for rec_payroll_msg in csr_payroll_mesg
1938 (
1939 rec_payroll_info.payroll_id,
1940 l_canonical_start_date,
1941 l_canonical_end_date
1942 )
1943 loop
1944 --Archive the Payroll message in 'EMPLOYEE OTHER INFORMATION'
1945 pay_action_information_api.create_action_information
1946 (
1947 p_action_information_id => l_action_info_id,
1948 p_action_context_id => pactid, -- Payroll Action ID of archiver
1949 p_action_context_type => 'PA',
1950 p_object_version_number => l_ovn,
1951 p_effective_date => rec_payroll_msg.effective_date,
1952 p_source_id => null,
1953 p_source_text => null,
1954 p_action_information_category => 'EMPLOYEE OTHER INFORMATION',
1955 p_action_information1 => rec_payroll_msg.payroll_action_id,
1956 p_action_information2 => 'MESG',
1957 p_action_information3 => null,
1958 p_action_information4 => null,
1959 p_action_information5 => null,
1960 p_action_information6 => rec_payroll_msg.payroll_message
1961 );
1962
1963 end loop;
1964 end loop;
1965 -- Populate the sqlstr to specify person_id's to process
1966 -- 3221746 modified business_group_id join condition
1967 sqlstr := 'select distinct person_id
1968 from per_people_f ppf,
1969 pay_payroll_actions ppa
1970 where ppa.payroll_action_id = :payroll_action_id
1971 and ppf.business_group_id = ppa.business_group_id
1972 order by ppf.person_id';
1973
1974 hr_utility.set_location('Leaving ' || l_proc, 40);
1975 -- hr_utility.trace_off;
1976
1977 exception
1978 when others then
1979 sqlstr := null;
1980
1981 end range_cursor;
1982
1983 -- This procedure creates the assignment actions for a specific chunk.
1984 -- This procedure gets called second to create the assignment actions.
1985 -- The procedure gets called once for each person_id returned by the range_cursor.
1986 procedure action_creation
1987 (
1988 pactid in number, -- Payroll Action of Archiver
1989 stperson in number,
1990 endperson in number,
1991 chunk in number
1992 ) is
1993
1994 -- Returns all runs and prepayments that have not already been locked by
1995 -- an Payslip Archive process
1996 -- 3221746 added ORDERED hint
1997 cursor csr_prepaid_assignments
1998 (
1999 p_pact_id number, -- Payroll Action of Archiver
2000 stperson number,
2001 endperson number,
2002 p_payroll_id number,
2003 p_consolidation_id number
2004 ) is
2005 select paa_run.assignment_id assignment_id,
2006 paa_run.assignment_action_id run_action_id,
2007 paa_pre.assignment_action_id prepaid_action_id
2011 per_all_assignments_f paaf,
2008 from pay_payroll_actions ppa_pre, -- Payroll Action of Prepayment
2009 pay_assignment_actions paa_pre, -- Assignment Action of Prepayment
2010 pay_action_interlocks pai,
2012 pay_assignment_actions paa_run, -- Assignment Action of Run
2013 pay_payroll_actions ppa_run, -- Payroll Action of Run
2014 pay_payroll_actions ppa_arch -- Payroll Action of Archiver
2015 where ppa_arch.payroll_action_id = p_pact_id
2016 and ppa_run.action_type in ('R', 'Q') -- Payroll Run or Quickpay Run
2017 and (ppa_run.payroll_id = p_payroll_id or p_payroll_id is null)
2018 and ppa_run.effective_date between ppa_arch.start_date and ppa_arch.effective_date
2019 and ppa_run.business_group_id = ppa_arch.business_group_id
2020 and paa_run.payroll_action_id = ppa_run.payroll_action_id
2021 and paa_run.source_action_id is null
2022 and paa_run.action_status = 'C'
2023 and paaf.assignment_id = paa_run.assignment_id
2024 and ppa_arch.effective_date between paaf.effective_start_date and paaf.effective_end_date
2025 and paaf.person_id between stperson and endperson
2026 and (paaf.payroll_id = p_payroll_id or p_payroll_id is null)
2027 and pai.locked_action_id = paa_run.assignment_action_id
2028 and paa_pre.assignment_action_id = pai.locking_action_id
2029 and paa_pre.action_status = 'C'
2030 and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
2031 and ppa_pre.action_type in ('P', 'U') -- Prepayments or Quickpay Prepayments
2032 and ppa_pre.consolidation_set_id = p_consolidation_id
2033 and not exists -- You can comment this to make the Archive rerunable
2034 (
2035 select /*+ ORDERED */ NULL
2036 from pay_action_interlocks pai2,
2037 pay_assignment_actions paa_arch2, -- Assignment Action of Archiver
2038 pay_payroll_actions ppa_arch2 -- Payroll Action of Archiver
2039 where pai2.locked_action_id = paa_run.assignment_action_id
2040 and paa_arch2.assignment_action_id = pai2.locking_action_id
2041 and paa_arch2.payroll_action_id = ppa_arch2.payroll_action_id
2042 and ppa_arch2.action_type = 'X'
2043 and ppa_arch2.report_type = 'ZA_SOE'
2044 )
2045 order by paa_run.assignment_id
2046 for update of paaf.assignment_id;
2047
2048 cursor csr_get_tax_unit_id (p_assignment_id number) is
2049 select paei.aei_information7
2050 from per_assignment_extra_info paei
2051 where paei.assignment_id = p_assignment_id
2052 and paei.information_type = 'ZA_SPECIFIC_INFO';
2053
2054 l_payroll_id number;
2055 l_consolidation_set varchar2(30);
2056 l_start_date varchar2(20);
2057 l_end_date varchar2(20);
2058 l_canonical_start_date date;
2059 l_canonical_end_date date;
2060 l_actid number;
2061 l_prepay_action_id number;
2062 l_tax_unit_id number;
2063
2064 l_proc varchar2(50);
2065
2066 begin
2067
2068 l_proc := g_package || 'action_creation';
2069 -- Removed default assignment to remove GSCC warning
2070 --hr_utility.trace_on(null, 'ZA_SOE');
2071 hr_utility.set_location('Entering ' || l_proc, 10);
2072
2073 -- Retrieve the legislative parameters from the payroll action
2074 pay_za_payslip_archive.get_parameters
2075 (
2076 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
2077 p_token_name => 'PAYROLL',
2078 p_token_value => l_payroll_id
2079 );
2080
2081 pay_za_payslip_archive.get_parameters
2082 (
2083 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
2084 p_token_name => 'CONSOLIDATION',
2085 p_token_value => l_consolidation_set
2086 );
2087
2088 pay_za_payslip_archive.get_parameters
2089 (
2090 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
2091 p_token_name => 'START_DATE',
2092 p_token_value => l_start_date
2093 );
2094
2095 pay_za_payslip_archive.get_parameters
2096 (
2097 p_payroll_action_id => pactid, -- Payroll Action of the Archiver
2098 p_token_name => 'END_DATE',
2099 p_token_value => l_end_date
2100 );
2101
2102 hr_utility.set_location('Step ' || l_proc, 20);
2103 hr_utility.set_location('l_payroll_id = ' || l_payroll_id, 20);
2104 hr_utility.set_location('l_start_date = ' || l_start_date, 20);
2105 hr_utility.set_location('l_end_date = ' || l_end_date, 20);
2106 hr_utility.set_location('pactid = ' || pactid, 20);
2107
2108 l_canonical_start_date := to_date(l_start_date, 'yyyy/mm/dd');
2109 l_canonical_end_date := to_date(l_end_date, 'yyyy/mm/dd');
2110
2111 l_prepay_action_id := 0;
2112
2113 -- Loop through all runs and prepayments that have not already been locked by
2114 -- an Payslip Archive process
2115 hr_utility.set_location('csr_prepaid_assignments info', 99);
2116 hr_utility.set_location('pactid = '||pactid, 99);
2117 hr_utility.set_location('stperson = '||stperson, 99);
2118 hr_utility.set_location('endperson = '||endperson, 99);
2119 hr_utility.set_location('l_payroll_id = '||l_payroll_id, 99);
2120 hr_utility.set_location('l_consolidation_set = '||l_consolidation_set, 99);
2124 stperson,
2121 for csr_rec in csr_prepaid_assignments
2122 (
2123 pactid,
2125 endperson,
2126 l_payroll_id,
2127 l_consolidation_set
2128 )
2129 loop
2130
2131 if l_prepay_action_id <> csr_rec.prepaid_action_id then
2132
2133 -- Select the next Assignment Action ID
2134 select pay_assignment_actions_s.nextval
2135 into l_actid
2136 from dual;
2137
2138 -- retrieve the tax_unit_id
2139 l_tax_unit_id := null;
2140
2141 open csr_get_tax_unit_id(csr_rec.assignment_id);
2142
2143 fetch csr_get_tax_unit_id into l_tax_unit_id;
2144
2145 close csr_get_tax_unit_id;
2146
2147 -- Create the archive assignment action for the master assignment action
2148 hr_nonrun_asact.insact(l_actid, csr_rec.assignment_id, pactid, chunk, l_tax_unit_id);
2149
2150 -- Create the archive to payroll master assignment action interlock and
2151 -- the archive to prepayment assignment action interlock
2152 hr_utility.set_location('creating lock for assignment_id ' || csr_rec.assignment_id, 20);
2153 hr_utility.set_location('creating lock1 ' || l_actid || ' to ' || csr_rec.run_action_id, 20);
2154 hr_utility.set_location('creating lock2 ' || l_actid || ' to ' || csr_rec.prepaid_action_id, 20);
2155
2156 hr_nonrun_asact.insint(l_actid, csr_rec.prepaid_action_id);
2157
2158 end if;
2159
2160 hr_nonrun_asact.insint(l_actid, csr_rec.run_action_id);
2161 l_prepay_action_id := csr_rec.prepaid_action_id;
2162
2163 end loop;
2164
2165 hr_utility.set_location('Leaving ' || l_proc, 20);
2166
2167 end action_creation;
2168
2169 -- This procedure gets called fourth to archive each assignment action.
2170 -- The procedure gets called once for each assignment action.
2171 procedure archive_code
2172 (
2173 p_assactid in number, -- Assignment Action of Archiver
2174 p_effective_date in date
2175 ) is
2176
2177 -- Returns all the Prepayments and Runs for the current Archiver Assignment Action
2178 -- This only returns master assignment actions, since the source_action_id is null
2179 cursor csr_assignment_actions(p_locking_action_id number) is
2180 select pre.locked_action_id pre_assignment_action_id, -- Assignment Action of Prepayments
2181 pay.locked_action_id master_assignment_action_id, -- Assignment Action of Run
2182 assact.assignment_id assignment_id,
2183 assact.payroll_action_id pay_payroll_action_id, -- Payroll Action of Run
2184 paa.effective_date effective_date, -- Effective Date of Run
2185 ppaa.effective_date pre_effective_date, -- Effective Date of Archive
2186 paa.date_earned date_earned, -- Date Earned of Run
2187 paa.time_period_id time_period_id -- Time Period Id of Run
2188 from pay_action_interlocks pre, -- Lock of Archiver on Prepayment
2189 pay_action_interlocks pay, -- Lock of Prepayment on Run
2190 pay_payroll_actions paa, -- Payroll Action of Run
2191 pay_payroll_actions ppaa, -- Payroll Action of Archiver
2192 pay_assignment_actions assact, -- Assignment Action of Run
2193 pay_assignment_actions passact -- Assignment Action of Archiver
2194 where pre.locked_action_id = pay.locking_action_id
2195 and pre.locking_action_id = p_locking_action_id -- Assignment Action of Archiver
2196 and pre.locked_action_id = passact.assignment_action_id
2197 and passact.payroll_action_id = ppaa.payroll_action_id
2198 and ppaa.action_type in ('P', 'U')
2199 and pay.locked_action_id = assact.assignment_action_id
2200 and assact.payroll_action_id = paa.payroll_action_id
2201 and assact.source_action_id is NULL;
2202
2203
2204 -- Returns the assignment_action_id of any child runs
2205 -- The assignment_action_id with the maximum action_sequence is returned for N and P run types
2206 -- FIX Could use Edwin trick
2207 cursor csr_child_actions
2208 (
2209 p_master_assignment_action number, -- Assignment Action of Master Run
2210 p_payroll_action_id number, -- Payroll Action of Master Run
2211 p_assignment_id number,
2212 p_effective_date date
2213 ) is
2214 select paa.assignment_action_id child_assignment_action_id,
2215 'S' run_type -- Separate Payment Run
2216 from pay_assignment_actions paa, -- Assignment Action of Child Run
2217 pay_run_types_f prt
2218 where paa.source_action_id = p_master_assignment_action -- Assignment Action of Master Run
2219 and paa.payroll_action_id = p_payroll_action_id -- Payroll Action of Master Run
2220 and paa.assignment_id = p_assignment_id
2221 and paa.run_type_id = prt.run_type_id
2222 and prt.run_method = 'S'
2223 and p_effective_date between prt.effective_start_date and prt.effective_end_date
2224 union
2225 select paa.assignment_action_id child_assignment_action_id,
2226 'NP' run_type -- Standard Run, Process Separate Run
2227 from pay_assignment_actions paa
2228 where paa.payroll_action_id = p_payroll_action_id
2229 and paa.assignment_id = p_assignment_id
2230 and paa.action_sequence =
2231 (
2235 where prt1.run_type_id = paa1.run_type_id
2232 select max(paa1.action_sequence)
2233 from pay_assignment_actions paa1,
2234 pay_run_types_f prt1
2236 and prt1.run_method in ('N', 'P')
2237 and paa1.payroll_action_id = p_payroll_action_id -- Payroll Action of Master Run
2238 and paa1.assignment_id = p_assignment_id
2239 and paa1.source_action_id = p_master_assignment_action -- Assignment Action of Master Run
2240 and p_effective_date between prt1.effective_start_date and prt1.effective_end_date
2241 );
2242
2243 cursor csr_np_children
2244 (
2245 p_assignment_action_id number,
2246 p_payroll_action_id number,
2247 p_assignment_id number,
2248 p_effective_date date
2249 ) is
2250 select paa.assignment_action_id np_assignment_action_id,
2251 prt.run_method
2252 from pay_assignment_actions paa,
2253 pay_run_types_f prt
2254 where paa.source_action_id = p_assignment_action_id
2255 and paa.payroll_action_id = p_payroll_action_id
2256 and paa.assignment_id = p_assignment_id
2257 and paa.run_type_id = prt.run_type_id
2258 and prt.run_method in ('N','P')
2259 and p_effective_date between prt.effective_start_date and prt.effective_end_date;
2260
2261 l_actid number;
2262 l_action_context_id number;
2263 l_action_info_id number(15);
2264 l_assignment_action_id number;
2265 l_business_group_id number;
2266 l_chunk_number number;
2267 l_date_earned date;
2268 l_ovn number;
2269 l_person_id number;
2270 l_record_count number;
2271 l_salary varchar2(10);
2272 l_sequence number;
2273
2274 l_proc varchar2(50) ;
2275
2276 l_pre_assignment_action_id number;
2277
2278 -- 3693941 added the l_pre_assignment_action_id variable.
2279 begin
2280
2281 l_proc := g_package || 'archive_code';
2282
2283
2284
2285 -- Removed default assignment to remove GSCC warning
2286 -- hr_utility.trace_on(null, 'ZA_SOE');
2287 hr_utility.set_location('Entering '|| l_proc, 10);
2288
2289 hr_utility.set_location('Step '|| l_proc, 20);
2290 hr_utility.set_location('p_assactid = ' || p_assactid, 20);
2291
2292 -- Retrieve the chunk number for the current assignment action
2293 select paa.chunk_number
2294 into l_chunk_number
2295 from pay_assignment_actions paa
2296 where paa.assignment_action_id = p_assactid;
2297
2298 l_action_context_id := p_assactid; --Assignment Action of Archiver
2299
2300 l_record_count := 0;
2301
2302 l_pre_assignment_action_id := 0;
2303 -- 3693941 initialise the l_pre_assignment_action_id variable.
2304 -- Loop through all the Prepayments and Runs for the current Archiver Assignment Action
2305 for csr_rec in csr_assignment_actions(p_assactid) loop
2306
2307 hr_utility.set_location('csr_rec.master_assignment_action_id = ' || csr_rec.master_assignment_action_id, 20);
2308 hr_utility.set_location('csr_rec.pre_assignment_action_id = ' || csr_rec.pre_assignment_action_id, 20);
2309 hr_utility.set_location('csr_rec.assignment_id = ' || csr_rec.assignment_id,20);
2310 hr_utility.set_location('csr_rec.date_earned = ' ||to_char( csr_rec.date_earned,'dd-mon-yyyy'),20);
2311 hr_utility.set_location('csr_rec.pre_effective_date = ' ||to_char( csr_rec.pre_effective_date,'dd-mon-yyyy'),20);
2312 hr_utility.set_location('csr_rec.time_period_id = ' || csr_rec.time_period_id,20);
2313
2314 if l_record_count = 0 then
2315
2316 hr_utility.set_location(' record_count = 0 , starting archive_employee_details' , 23);
2317
2318 -- Archive the Employee Details in 'EMPLOYEE DETAILS', 'ADDRESS DETAILS',
2319 -- 'EMPLOYEE NET PAY DISTRIBUTION' and 'ZA EMPLOYEE DETAILS'
2320 pay_za_payslip_archive.archive_employee_details
2321 (
2322 p_assactid => p_assactid,
2323 p_assignment_id => csr_rec.assignment_id,
2324 p_curr_pymt_ass_act_id => csr_rec.pre_assignment_action_id, -- prepayment assignment_action_id
2325 p_date_earned => csr_rec.date_earned, -- payroll date_earned
2326 p_curr_pymt_eff_date => csr_rec.pre_effective_date, -- prepayment effective_date
2327 p_time_period_id => csr_rec.time_period_id -- payroll time_period_id
2328 );
2329
2330 hr_utility.set_location(' out of archive_employee_details ' , 25);
2331
2332 end if;
2333
2334 pay_za_payslip_archive.get_element_info
2335 (
2336 p_action_context_id => l_action_context_id,
2337 p_assignment_id => csr_rec.assignment_id,
2338 p_child_assignment_action => csr_rec.master_assignment_action_id,
2339 p_effective_date => csr_rec.pre_effective_date,
2340 p_record_count => l_record_count,
2341 p_run_method => 'N'
2342 );
2343
2344 -- Added if condition for the bug 3693941
2345 if l_pre_assignment_action_id <> csr_rec.pre_assignment_action_id then
2346 -- Both User and Statutory Balances are archived for all Separate Payment assignment actions
2350 -- Archive user balances
2347 -- and the last (i.e. highest action_sequence) Process Separately assignment action
2348 -- (EMEA BALANCES)
2349
2351 hr_utility.set_location('l_pre_assignment_action_id = '|| l_pre_assignment_action_id, 60);
2352
2353 l_pre_assignment_action_id := csr_rec.pre_assignment_action_id;
2354
2355 hr_utility.set_location('Archive User Balances - Starting', 60);
2356 hr_utility.set_location('g_max_user_balance_index = '|| g_max_user_balance_index, 60);
2357 hr_utility.set_location('l_pre_assignment_action_id = '|| l_pre_assignment_action_id, 60);
2358
2359 for l_index in 1..g_max_user_balance_index loop
2360 -- Bug 5507715
2361 pay_za_payslip_archive.process_balance
2362 (
2363 p_action_context_id => l_action_context_id,
2364 p_assignment_id => csr_rec.assignment_id,
2365 p_source_id => csr_rec.master_assignment_action_id, --csr_rec.pre_assignment_action_id, --
2366 p_effective_date => csr_rec.pre_effective_date,
2367 p_balance => g_user_balance_table(l_index).balance_name,
2368 p_dimension => g_user_balance_table(l_index).database_item_suffix,
2369 p_defined_bal_id => g_user_balance_table(l_index).defined_balance_id,
2370 p_record_count => l_record_count
2371 );/*
2372 IF g_user_balance_table(l_index).database_item_suffix = '_ASG_RUN' or
2373 g_user_balance_table(l_index).database_item_suffix = '_ASG_TAX_PTD' then
2374 pay_za_payslip_archive.process_balance
2375 (
2376 p_action_context_id => l_action_context_id,
2377 p_assignment_id => csr_rec.assignment_id,
2378 p_source_id => csr_rec.master_assignment_action_id,
2379 p_effective_date => csr_rec.pre_effective_date,
2380 p_balance => g_user_balance_table(l_index).balance_name,
2381 p_dimension => g_user_balance_table(l_index).database_item_suffix,
2382 p_defined_bal_id => g_user_balance_table(l_index).defined_balance_id,
2383 p_record_count => l_record_count
2384 );
2385 else
2386 pay_za_payslip_archive.process_balance
2387 (
2388 p_action_context_id => l_action_context_id,
2389 p_assignment_id => csr_rec.assignment_id,
2390 p_source_id => csr_rec.pre_assignment_action_id, --csr_rec.master_assignment_action_id,
2391 p_effective_date => csr_rec.pre_effective_date,
2392 p_balance => g_user_balance_table(l_index).balance_name,
2393 p_dimension => g_user_balance_table(l_index).database_item_suffix,
2394 p_defined_bal_id => g_user_balance_table(l_index).defined_balance_id,
2395 p_record_count => l_record_count
2396 );
2397 END if;*/
2398
2399 end loop;
2400
2401 hr_utility.set_location('Archive User Balances - Complete', 60);
2402
2403 -- Archive statutory balances
2404 hr_utility.set_location('Archive Statutory Balances - Starting', 70);
2405 hr_utility.set_location('g_max_statutory_balance_index = '|| g_max_statutory_balance_index, 70);
2406
2407 for l_index in 1..g_max_statutory_balance_index loop
2408
2409 hr_utility.set_location('l_index = ' || l_index, 70);
2410 --5507715
2411 pay_za_payslip_archive.process_balance
2412 (
2413 p_action_context_id => l_action_context_id,
2414 p_assignment_id => csr_rec.assignment_id,
2415 p_source_id => csr_rec.master_assignment_action_id,--csr_rec.pre_assignment_action_id, --
2416 p_effective_date => csr_rec.pre_effective_date,
2417 p_balance => g_statutory_balance_table(l_index).balance_name,
2418 p_dimension => g_statutory_balance_table(l_index).database_item_suffix,
2419 p_defined_bal_id => g_statutory_balance_table(l_index).defined_balance_id,
2420 p_record_count => l_record_count
2421 ); /*
2422 IF g_statutory_balance_table(l_index).database_item_suffix = '_ASG_RUN' or
2423 g_statutory_balance_table(l_index).database_item_suffix = '_ASG_TAX_PTD' then
2424
2425 pay_za_payslip_archive.process_balance
2426 (
2427 p_action_context_id => l_action_context_id,
2428 p_assignment_id => csr_rec.assignment_id,
2429 p_source_id => csr_rec.master_assignment_action_id,
2430 p_effective_date => csr_rec.pre_effective_date,
2431 p_balance => g_statutory_balance_table(l_index).balance_name,
2432 p_dimension => g_statutory_balance_table(l_index).database_item_suffix,
2433 p_defined_bal_id => g_statutory_balance_table(l_index).defined_balance_id,
2434 p_record_count => l_record_count
2435 );
2436 else
2437 pay_za_payslip_archive.process_balance
2438 (
2439 p_action_context_id => l_action_context_id,
2440 p_assignment_id => csr_rec.assignment_id,
2441 p_source_id => csr_rec.pre_assignment_action_id, --csr_rec.master_assignment_action_id,
2442 p_effective_date => csr_rec.pre_effective_date,
2443 p_balance => g_statutory_balance_table(l_index).balance_name,
2444 p_dimension => g_statutory_balance_table(l_index).database_item_suffix,
2445 p_defined_bal_id => g_statutory_balance_table(l_index).defined_balance_id,
2446 p_record_count => l_record_count
2447 );
2448 END if;*/
2449 end loop;
2450 end if;
2451 -- and passed the csr_rec.pre_assignment_action_id instead of master_assignment_action_id
2452 -- End of if condition for the bug 3693941
2453 hr_utility.set_location('Archive Statutory Balances - Complete', 70);
2454
2455 l_record_count := l_record_count + 1;
2456
2457 end loop;
2458
2459 hr_utility.set_location('Leaving '|| l_proc, 80);
2460
2461 end archive_code;
2462 end;