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