[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_PAYSLIP_ARCHIVE
Source
1 PACKAGE BODY pay_gb_payslip_archive AS
2 /* $Header: pygbparc.pkb 120.5 2006/09/27 05:53:26 npershad noship $ */
3
4 TYPE balance_rec IS RECORD (
5 balance_type_id NUMBER,
6 balance_dimension_id NUMBER,
7 defined_balance_id NUMBER,
8 --Bug Number 3526619
9 balance_narrative VARCHAR2(80),
10 balance_name VARCHAR2(80),
11 database_item_suffix VARCHAR2(30),
12 legislation_code VARCHAR2(20),
13 ni_type_ind VARCHAR2(1));
14
15 TYPE element_rec IS RECORD (
16 element_type_id NUMBER,
17 input_value_id NUMBER,
18 formula_id NUMBER,
19 --Bug Number 3526619
20 element_narrative VARCHAR2(80),
21 uom VARCHAR2(30));
22
23 TYPE ni_total_rec IS RECORD (
24 balance_name VARCHAR2(30),
25 category VARCHAR2(1));
26
27 TYPE balance_table IS TABLE OF balance_rec INDEX BY BINARY_INTEGER;
28 TYPE element_table IS TABLE OF element_rec INDEX BY BINARY_INTEGER;
29 TYPE ni_total_table IS TABLE OF ni_total_rec INDEX BY BINARY_INTEGER;
30
31 g_user_balance_table balance_table;
32 g_element_table element_table;
33 g_statutory_balance_table balance_table;
34 g_ni_totals_table ni_total_table;
35
36 g_balance_archive_index NUMBER := 0;
37 g_element_archive_index NUMBER := 0;
38 g_max_element_index NUMBER := 0;
39 g_max_user_balance_index NUMBER := 0;
40 g_max_statutory_balance_index NUMBER := 0;
41
42 g_ni_element_id NUMBER;
43 g_paye_details_element_id NUMBER;
44 g_paye_element_id NUMBER;
45 g_paye_previous_pay_archived VARCHAR2(1);
46 g_paye_previous_pay_id NUMBER;
47 g_paye_previous_tax_archived VARCHAR2(1);
48 g_paye_previous_tax_id NUMBER;
49
50 g_ni_cat_id NUMBER;
51 g_paye_tax_basis_id NUMBER;
52 g_paye_tax_code_id NUMBER;
53 g_tax_basis_id NUMBER;
54 g_tax_code_id NUMBER;
55
56 g_package CONSTANT VARCHAR2(30) := 'pay_gb_payslip_archive.';
57
58 g_balance_context CONSTANT VARCHAR2(30) := 'GB_PAYSLIP_BALANCES';
59 g_element_context CONSTANT VARCHAR2(30) := 'GB_PAYSLIP_ELEMENTS';
60
61 g_archive_pact NUMBER;
62 g_archive_effective_date DATE;
63
64 PROCEDURE get_parameters(p_payroll_action_id IN NUMBER,
65 p_token_name IN VARCHAR2,
66 p_token_value OUT NOCOPY VARCHAR2) IS
67
68 CURSOR csr_parameter_info(p_pact_id NUMBER,
69 p_token CHAR) IS
70 SELECT SUBSTR(legislative_parameters,
71 INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
72 INSTR(legislative_parameters,' ',
73 INSTR(legislative_parameters,p_token))
74 - (INSTR(legislative_parameters,p_token)+LENGTH(p_token))),
75 business_group_id
76 FROM pay_payroll_actions
77 WHERE payroll_action_id = p_pact_id;
78
79 l_business_group_id VARCHAR2(20);
80 l_token_value VARCHAR2(50);
81
82 l_proc VARCHAR2(50) := g_package || 'get_parameters';
83
84 BEGIN
85
86 hr_utility.set_location('Entering ' || l_proc,10);
87
88 hr_utility.set_location('Step ' || l_proc,20);
89 hr_utility.set_location('p_token_name = ' || p_token_name,20);
90
91 OPEN csr_parameter_info(p_payroll_action_id,
92 p_token_name);
93
94 FETCH csr_parameter_info INTO l_token_value,
95 l_business_group_id;
96
97 CLOSE csr_parameter_info;
98
99 IF p_token_name = 'BG_ID'
100
101 THEN
102
103 p_token_value := l_business_group_id;
104
105 ELSE
106
107 p_token_value := l_token_value;
108
109 END IF;
110
111 hr_utility.set_location('l_token_value = ' || l_token_value,20);
112 hr_utility.set_location('Leaving ' || l_proc,30);
113
114 END get_parameters;
115
116 PROCEDURE get_eit_definitions(p_pactid IN NUMBER,
117 p_business_group_id IN NUMBER,
118 p_payroll_pact IN NUMBER,
119 p_effective_date IN DATE,
120 p_eit_context IN VARCHAR2,
121 p_archive IN VARCHAR2) IS
122
123 CURSOR csr_eit_values(p_bg_id NUMBER,
124 p_context CHAR) IS
125 SELECT org.org_information1,
126 org.org_information2,
127 org.org_information3,
128 org.org_information4,
129 org.org_information5,
130 org.org_information6
131 FROM hr_organization_information_v org
132 WHERE org.org_information_context = p_context
133 AND org.organization_id = p_bg_id;
134
135 CURSOR csr_balance_name(p_balance_type_id NUMBER,
136 p_balance_dimension_id NUMBER) IS
137 SELECT pbt.balance_name,
138 pbd.database_item_suffix,
139 pbt.legislation_code,
140 pdb.defined_balance_id
141 FROM pay_balance_types pbt,
142 pay_balance_dimensions pbd,
143 pay_defined_balances pdb
144 WHERE pdb.balance_type_id = pbt.balance_type_id
145 AND pdb.balance_dimension_id = pbd.balance_dimension_id
146 AND pbt.balance_type_id = p_balance_type_id
147 AND pbd.balance_dimension_id = p_balance_dimension_id;
148
149 CURSOR csr_element_type(p_element_type_id NUMBER,
150 p_effective_date DATE) IS
151 SELECT pet.formula_id
152 FROM pay_element_types_f pet,
153 ff_formulas_f fff
154 WHERE pet.element_type_id = p_element_type_id
155 AND pet.formula_id = fff.formula_id
156 AND fff.formula_name = 'ONCE_EACH_PERIOD'
157 AND p_effective_date BETWEEN
158 fff.effective_start_date AND fff.effective_end_date
159 AND p_effective_date BETWEEN
160 pet.effective_start_date AND pet.effective_end_date;
161
162 CURSOR csr_input_value_uom(p_input_value_id NUMBER,
163 p_effective_date DATE) IS
164 SELECT piv.uom
165 FROM pay_input_values_f piv
166 WHERE piv.input_value_id = p_input_value_id
167 AND p_effective_date BETWEEN
168 piv.effective_start_date AND piv.effective_end_date;
169
170 l_action_info_id NUMBER(15);
171 l_formula_id NUMBER(9);
172 l_index NUMBER := 1;
173 l_ovn NUMBER(15);
174 l_uom VARCHAR(30);
175
176 l_proc VARCHAR2(50) := g_package || 'get_eit_definitions';
177
178 BEGIN
179
180 hr_utility.set_location('Entering ' || l_proc,10);
181
182 hr_utility.set_location('Step ' || l_proc,20);
183 hr_utility.set_location('p_eit_context = ' || p_eit_context,20);
184
185 FOR csr_eit_rec IN csr_eit_values(p_business_group_id,
186 p_eit_context)
187
188 LOOP
189
190 hr_utility.set_location('Step ' || l_proc,30);
191
192 hr_utility.set_location('org_information1 = ' || csr_eit_rec.org_information1,30);
193 hr_utility.set_location('org_information2 = ' || csr_eit_rec.org_information2,30);
194 hr_utility.set_location('org_information3 = ' || csr_eit_rec.org_information3,30);
195 hr_utility.set_location('org_information4 = ' || csr_eit_rec.org_information4,30);
196 hr_utility.set_location('org_information5 = ' || csr_eit_rec.org_information5,30);
197 hr_utility.set_location('org_information6 = ' || csr_eit_rec.org_information6,30);
198
199 IF p_eit_context = g_balance_context
200
201 THEN
202
203 g_user_balance_table(l_index).balance_type_id := SUBSTR(csr_eit_rec.org_information1,2);
204
205 g_user_balance_table(l_index).balance_dimension_id := csr_eit_rec.org_information2;
206
207 g_user_balance_table(l_index).balance_narrative := csr_eit_rec.org_information3;
208
209
210 OPEN csr_balance_name(g_user_balance_table(l_index).balance_type_id,
211 g_user_balance_table(l_index).balance_dimension_id);
212
213 FETCH csr_balance_name
214 INTO g_user_balance_table(l_index).balance_name,
215 g_user_balance_table(l_index).database_item_suffix,
216 g_user_balance_table(l_index).legislation_code,
217 g_user_balance_table(l_index).defined_balance_id;
218
219 CLOSE csr_balance_name;
220
221 -- If the balance name is NI Employer it is processed
222 -- differently. This type of balance is identified here and
223 -- given an n_type_ind of E.
224
225 -- If the balance name is NI Employee or if the balance name
226 -- starts with NI and doesnt have a space as the 5th character
227 -- then it is a total of all categories for that particular balance.
228 -- This type of balance is identified here and given an
229 -- n_type_ind of T.
230
231 hr_utility.set_location('g_user_balance_table(l_index).balance_name = ' ||
232 g_user_balance_table(l_index).balance_name,50);
233
234 IF g_user_balance_table(l_index).balance_name = 'NI Employer'
235
236 THEN
237
238 g_user_balance_table(l_index).ni_type_ind := 'E';
239
240 ELSIF (g_user_balance_table(l_index).balance_name = 'NI Employee' OR
241 SUBSTR(csr_eit_rec.org_information1,1,1) = 2)
242
243 THEN
244
245 g_user_balance_table(l_index).ni_type_ind := 'T';
246
247 ELSE
248
249 g_user_balance_table(l_index).ni_type_ind := ' ';
250
251 END IF;
252
253 hr_utility.set_location('Arch EMEA BALANCE DEFINITION',99);
254
255 IF p_archive = 'Y'
256
257 THEN
258
259 pay_action_information_api.create_action_information (
260 p_action_information_id => l_action_info_id
261 , p_action_context_id => p_pactid
262 , p_action_context_type => 'PA'
263 , p_object_version_number => l_ovn
264 , p_effective_date => p_effective_date
265 , p_source_id => NULL
266 , p_source_text => NULL
267 , p_action_information_category => 'EMEA BALANCE DEFINITION'
268 , p_action_information1 => p_payroll_pact
269 , p_action_information2 => g_user_balance_table(l_index).defined_balance_id
270 , p_action_information3 => NULL
271 , p_action_information4 => csr_eit_rec.org_information3
272 , p_action_information5 => g_user_balance_table(l_index).ni_type_ind);
273
274 END IF;
275
276 g_max_user_balance_index := g_max_user_balance_index + 1;
277
278 END IF;
279
280 IF p_eit_context = g_element_context
281
282 THEN
283
284 g_element_table(l_index).element_type_id := csr_eit_rec.org_information1;
285
286 g_element_table(l_index).input_value_id := csr_eit_rec.org_information2;
287
288 g_element_table(l_index).element_narrative := csr_eit_rec.org_information3;
289
290 l_formula_id := NULL;
291
292 OPEN csr_element_type(csr_eit_rec.org_information1,
293 p_effective_date);
294
295 FETCH csr_element_type INTO l_formula_id;
296
297 CLOSE csr_element_type;
298
299 g_element_table(l_index).formula_id := l_formula_id;
300
301 l_uom := NULL;
302
303 OPEN csr_input_value_uom(csr_eit_rec.org_information2,
304 p_effective_date);
305
306 FETCH csr_input_value_uom INTO l_uom;
307
308 CLOSE csr_input_value_uom;
309
310 g_element_table(l_index).uom := l_uom;
311
312 IF p_archive = 'Y'
313
314 THEN
315
316 hr_utility.set_location('Arch EMEA ELEMENT DEFINITION',99);
317
318 pay_action_information_api.create_action_information (
319 p_action_information_id => l_action_info_id
320 , p_action_context_id => p_pactid
321 , p_action_context_type => 'PA'
322 , p_object_version_number => l_ovn
323 , p_effective_date => p_effective_date
324 , p_source_id => NULL
325 , p_source_text => NULL
326 , p_action_information_category => 'EMEA ELEMENT DEFINITION'
327 , p_action_information1 => p_payroll_pact
328 , p_action_information2 => csr_eit_rec.org_information1
329 , p_action_information3 => csr_eit_rec.org_information2
330 , p_action_information4 => csr_eit_rec.org_information3
331 , p_action_information5 => 'F'
332 , p_action_information6 => l_uom);
333
334 END IF;
335
336 END IF;
337
338 l_index := l_index + 1;
339
340 hr_utility.set_location('l_index = ' || l_index,99);
341
342 END LOOP;
343
344 g_max_element_index := l_index;
345
346 IF p_eit_context = g_balance_context
347
348 THEN
349
350 g_balance_archive_index := l_index - 1;
351
352 ELSE
353
354 g_element_archive_index := l_index - 1;
355
356 END IF;
357
358 hr_utility.set_location('g_balance_archive_index = ' || g_balance_archive_index,99);
359
360 hr_utility.set_location('Leaving ' || l_proc,30);
361
362 END get_eit_definitions;
363
364 PROCEDURE setup_element_definitions (p_pactid IN NUMBER,
365 p_payroll_pact IN NUMBER,
366 p_business_group_id IN NUMBER,
367 p_effective_date IN DATE)
368 IS
369
370 l_action_info_id NUMBER(15);
371 l_ovn NUMBER(15);
372 l_payment_type VARCHAR2(1);
373
374 CURSOR csr_element_name (p_business_group_id NUMBER,
375 p_effective_date DATE) IS
376 SELECT pet.element_type_id,
377 piv.input_value_id,
378 NVL(pet.reporting_name,pet.element_name) element_name,
379 pec.classification_name,
380 piv.uom
381 FROM pay_element_classifications pec,
382 pay_input_values_f piv,
383 pay_element_types_f pet
384 WHERE pec.classification_name IN ('Court Orders','Voluntary Deductions','Pre Tax Deductions',
385 'PAYE','NI','Earnings','Direct Payment','Pre NI Deductions','Pre Tax and NI Deductions')
386 AND pec.business_group_id IS NULL
387 AND pec.legislation_code = 'GB'
388 AND pet.classification_id = pec.classification_id
389 AND NVL(pet.business_group_id,p_business_group_id) = p_business_group_id
390 AND piv.element_type_id = pet.element_type_id
391 AND piv.name = 'Pay Value'
392 AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
393 AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
394
395 l_proc VARCHAR2(60) := g_package || 'setup_element_definitions';
396
397 BEGIN
398
399 hr_utility.set_location('Entering ' || l_proc,10);
400
401 hr_utility.set_location('p_payroll_pact = ' || p_payroll_pact,10);
402
403 FOR csr_element_rec IN csr_element_name(p_business_group_id,
404 p_effective_date)
405
406 LOOP
407
408 hr_utility.set_location('csr_element_rec.element_type_id = ' || csr_element_rec.element_type_id,20);
409 hr_utility.set_location('csr_element_rec.element_name = ' || csr_element_rec.element_name,20);
410
411 IF csr_element_rec.classification_name = 'Earnings'
412
413 THEN
414
415 l_payment_type := 'E';
416
417 ELSIF csr_element_rec.classification_name = 'Direct Payment'
418
419 THEN
420
421 l_payment_type := 'P';
422
423 ELSE
424
425 l_payment_type := 'D';
426
427 END IF;
428
429 hr_utility.set_location('Arch EMEA ELEMENT DEFINITION',99);
430
431 pay_action_information_api.create_action_information (
432 p_action_information_id => l_action_info_id
433 , p_action_context_id => p_pactid
434 , p_action_context_type => 'PA'
435 , p_object_version_number => l_ovn
436 , p_effective_date => p_effective_date
437 , p_source_id => NULL
438 , p_source_text => NULL
439 , p_action_information_category => 'EMEA ELEMENT DEFINITION'
440 , p_action_information1 => p_payroll_pact
441 , p_action_information2 => csr_element_rec.element_type_id
442 , p_action_information3 => csr_element_rec.input_value_id
443 , p_action_information4 => csr_element_rec.element_name
444 , p_action_information5 => l_payment_type
445 , p_action_information6 => csr_element_rec.uom);
446
447 END LOOP;
448
449 hr_utility.set_location('Leaving ' || l_proc,30);
450
451 END setup_element_definitions;
452
453 PROCEDURE setup_standard_balance_table
454 IS
455
456 TYPE balance_name_rec IS RECORD (
457 balance_name VARCHAR2(30));
458
459 TYPE balance_id_rec IS RECORD (
460 defined_balance_id NUMBER);
461
462 TYPE balance_name_tab IS TABLE OF balance_name_rec INDEX BY BINARY_INTEGER;
463 TYPE balance_id_tab IS TABLE OF balance_id_rec INDEX BY BINARY_INTEGER;
464
465 l_statutory_balance balance_name_tab;
466 l_statutory_bal_id balance_id_tab;
467
468 CURSOR csr_balance_dimension(p_balance IN CHAR,
469 p_dimension IN CHAR) IS
470 SELECT pdb.defined_balance_id
471 FROM pay_balance_types pbt,
472 pay_balance_dimensions pbd,
473 pay_defined_balances pdb
474 WHERE pdb.balance_type_id = pbt.balance_type_id
475 AND pdb.balance_dimension_id = pbd.balance_dimension_id
476 AND pbt.balance_name = p_balance
477 AND pbd.database_item_suffix = p_dimension;
478
479 l_archive_index NUMBER := 0;
480 l_dimension VARCHAR2(12) := '_ASG_TD_YTD';
481 l_found VARCHAR2(1);
482 l_max_stat_balance NUMBER := 13;
483
484 l_proc VARCHAR2(100) := g_package || 'setup_standard_balance_table';
485
486
487 BEGIN
488
489 hr_utility.set_location('Entering ' || l_proc,10);
490
491 hr_utility.set_location('Step ' || l_proc,20);
492
493 l_statutory_balance(1).balance_name := 'Gross Pay';
494 l_statutory_balance(2).balance_name := 'Notional Pay';
495 l_statutory_balance(3).balance_name := 'Taxable Pay';
496 l_statutory_balance(4).balance_name := 'NIable Pay';
497 l_statutory_balance(5).balance_name := 'SSP Total';
498 l_statutory_balance(6).balance_name := 'SMP Total';
499 l_statutory_balance(7).balance_name := 'Tax Credit';
500 l_statutory_balance(8).balance_name := 'PAYE';
501 l_statutory_balance(9).balance_name := 'NI Employer';
502 l_statutory_balance(10).balance_name := 'NI Ees Rebate';
503 l_statutory_balance(11).balance_name := 'NI Ers Rebate';
504 l_statutory_balance(12).balance_name := 'Student Loan';
505 l_statutory_balance(13).balance_name := 'Superannuation Total';
506
507 hr_utility.set_location('Step = ' || l_proc,30);
508
509 FOR l_index IN 1 .. l_max_stat_balance
510
511 LOOP
512
513 hr_utility.set_location('l_index = ' || l_index,30);
514 hr_utility.set_location('balance_name = ' || l_statutory_balance(l_index).balance_name,30);
515 hr_utility.set_location('l_dimension = ' || l_dimension,30);
516
517 OPEN csr_balance_dimension(l_statutory_balance(l_index).balance_name,
518 l_dimension);
519
520 FETCH csr_balance_dimension
521 INTO l_statutory_bal_id(l_index).defined_balance_id;
522
523 IF csr_balance_dimension%NOTFOUND
524
525 THEN
526
527 l_statutory_bal_id(l_index).defined_balance_id := 0;
528
529 END IF;
530
531 CLOSE csr_balance_dimension;
532
533 hr_utility.set_location('defined_balance_id = ' || l_statutory_bal_id(l_index).defined_balance_id,30);
534
535 END LOOP;
536
537 hr_utility.set_location('Step = ' || l_proc,40);
538
539 hr_utility.set_location('l_max_stat_balance = ' || l_max_stat_balance,40);
540 hr_utility.set_location('g_max_user_balance_index = ' || g_max_user_balance_index,40);
541
542 FOR l_index IN 1 .. l_max_stat_balance
543
544 LOOP
545
546 l_found := 'N';
547
548 FOR l_eit_index IN 1 .. g_max_user_balance_index
549
550 LOOP
551
552 hr_utility.set_location('l_index = ' || l_index,40);
553 hr_utility.set_location('l_eit_index = ' || l_eit_index,40);
554 hr_utility.set_location('defined_balance_id = ' || l_statutory_bal_id(l_index).defined_balance_id,40);
555 hr_utility.set_location('l_found = ' || l_found,40);
556
557 IF l_statutory_bal_id(l_index).defined_balance_id = g_user_balance_table(l_eit_index).defined_balance_id
558
559 THEN
560
561 l_found := 'Y';
562
563 END IF;
564
565 END LOOP;
566
567 IF l_found = 'N'
568
569 THEN
570
571 hr_utility.set_location('l_archive_index = ' || l_archive_index,40);
572
573 l_archive_index := l_archive_index + 1;
574
575 g_statutory_balance_table(l_archive_index).defined_balance_id := l_statutory_bal_id(l_index).defined_balance_id;
576
577 g_statutory_balance_table(l_archive_index).ni_type_ind := ' ';
578
579 END IF;
580
581 END LOOP;
582
583 g_max_statutory_balance_index := l_archive_index;
584
585 hr_utility.set_location('Step ' || l_proc,50);
586 hr_utility.set_location('l_archive_index = ' || l_archive_index,50);
587
588 hr_utility.set_location('Leaving ' || l_proc,60);
589
590 END setup_standard_balance_table;
591
592 PROCEDURE archinit (p_payroll_action_id IN NUMBER)
593 IS
594
595 CURSOR csr_archive_effective_date(pactid NUMBER) IS
596 SELECT effective_date
597 FROM pay_payroll_actions
598 WHERE payroll_action_id = pactid;
599
600 CURSOR csr_input_value_id(p_element_name CHAR,
601 p_value_name CHAR) IS
602 SELECT pet.element_type_id,
603 piv.input_value_id
604 FROM pay_input_values_f piv,
605 pay_element_types_f pet
606 WHERE piv.element_type_id = pet.element_type_id
607 AND pet.legislation_code = 'GB'
608 AND pet.element_name = p_element_name
609 AND piv.name = p_value_name;
610
611 l_proc VARCHAR2(50) := g_package || 'archinit';
612
613 l_assignment_set_id NUMBER;
614 l_bg_id NUMBER;
615 l_canonical_end_date DATE;
616 l_canonical_start_date DATE;
617 l_consolidation_set NUMBER;
618 l_end_date VARCHAR2(30);
619 l_payroll_id NUMBER;
620 l_start_date VARCHAR2(30);
621
622 BEGIN
623
624 -- hr_utility.trace_on(NULL,'UKPS0');
625
626 hr_utility.set_location('Entering ' || l_proc,10);
627
628 g_archive_pact := p_payroll_action_id;
629
630 OPEN csr_archive_effective_date(p_payroll_action_id);
631
632 FETCH csr_archive_effective_date
633 INTO g_archive_effective_date;
634
635 CLOSE csr_archive_effective_date;
636
637 pay_gb_payslip_archive.get_parameters (
638 p_payroll_action_id => p_payroll_action_id
639 , p_token_name => 'PAYROLL'
640 , p_token_value => l_payroll_id);
641
642 pay_gb_payslip_archive.get_parameters (
643 p_payroll_action_id => p_payroll_action_id
644 , p_token_name => 'CONSOLIDATION'
645 , p_token_value => l_consolidation_set);
646
647 pay_gb_payslip_archive.get_parameters (
648 p_payroll_action_id => p_payroll_action_id
649 , p_token_name => 'ASSIGNMENT_SET'
650 , p_token_value => l_assignment_set_id);
651
652 pay_gb_payslip_archive.get_parameters (
653 p_payroll_action_id => p_payroll_action_id
654 , p_token_name => 'START_DATE'
655 , p_token_value => l_start_date);
656
657 pay_gb_payslip_archive.get_parameters (
658 p_payroll_action_id => p_payroll_action_id
659 , p_token_name => 'END_DATE'
660 , p_token_value => l_end_date);
661
662 pay_gb_payslip_archive.get_parameters (
663 p_payroll_action_id => p_payroll_action_id
664 , p_token_name => 'BG_ID'
665 , p_token_value => l_bg_id);
666
667 hr_utility.set_location('Step ' || l_proc,20);
668 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
669 hr_utility.set_location('l_start_date = ' || l_start_date,20);
670 hr_utility.set_location('l_end_date = ' || l_end_date,20);
671
672 l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
673 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
674
675 -- retrieve ids for NI and tax elements
676
677 OPEN csr_input_value_id('NI','Category');
678
679 FETCH csr_input_value_id INTO g_ni_element_id,
680 g_ni_cat_id;
681
682 CLOSE csr_input_value_id;
683
684 OPEN csr_input_value_id('PAYE Details','Tax Code');
685
686 FETCH csr_input_value_id INTO g_paye_details_element_id,
687 g_tax_code_id;
688
689 CLOSE csr_input_value_id;
690
691
692 OPEN csr_input_value_id('PAYE Details','Tax Basis');
693
694 FETCH csr_input_value_id INTO g_paye_details_element_id,
695 g_tax_basis_id;
696
697 CLOSE csr_input_value_id;
698
699 OPEN csr_input_value_id('PAYE','Tax Code');
700
701 FETCH csr_input_value_id INTO g_paye_element_id,
702 g_paye_tax_code_id;
703
704 CLOSE csr_input_value_id;
705
706
707 OPEN csr_input_value_id('PAYE','Tax Basis');
708
709 FETCH csr_input_value_id INTO g_paye_element_id,
710 g_paye_tax_basis_id;
711
712 CLOSE csr_input_value_id;
713
714 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
715 hr_utility.set_location('l_consolidation_set = ' || l_consolidation_set,20);
716 hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
717 hr_utility.set_location('l_canonical_end_date = ' || l_canonical_end_date,20);
718
719 -- retrieve and archive user defintions from EITs
720
721 g_max_user_balance_index := 0;
722
723 hr_utility.set_location('get_eit_definitions - balances',20);
724
725 pay_gb_payslip_archive.get_eit_definitions (
726 p_pactid => p_payroll_action_id
727 , p_business_group_id => l_bg_id
728 , p_payroll_pact => NULL
729 , p_effective_date => l_canonical_start_date
730 , p_eit_context => g_balance_context
731 , p_archive => 'N');
732
733 hr_utility.set_location('get_eit_definitions - elements',20);
734
735 pay_gb_payslip_archive.get_eit_definitions (
736 p_pactid => p_payroll_action_id
737 , p_business_group_id => l_bg_id
738 , p_payroll_pact => NULL
739 , p_effective_date => l_canonical_start_date
740 , p_eit_context => g_element_context
741 , p_archive => 'N');
742
743 pay_balance_pkg.set_context('PAYROLL_ACTION_ID'
744 , p_payroll_action_id);
745
746 -- setup statutory balances pl/sql table
747
748 pay_gb_payslip_archive.setup_standard_balance_table;
749
750 hr_utility.set_location('Leaving ' || l_proc,20);
751
752 END archinit;
753
754 PROCEDURE archive_employee_details (
755 p_assactid IN NUMBER
756 , p_assignment_id IN NUMBER
757 , p_curr_pymt_ass_act_id IN NUMBER
758 , p_effective_date IN DATE
759 , p_date_earned IN DATE
760 , p_curr_pymt_eff_date IN DATE
761 , p_time_period_id IN NUMBER
762 , p_record_count IN NUMBER) IS
763
764 l_action_info_id NUMBER;
765 l_ovn NUMBER;
766
767 l_proc VARCHAR2(50) := g_package || 'archive_employee_details';
768
769 BEGIN
770
771 hr_utility.set_location('Entering ' || l_proc,10);
772
773 -- call generic procedure to retrieve and archive all data for
774 -- EMPLOYEE DETAILS, ADDRESS DETAILS and EMPLOYEE NET PAY DISTRIBUTION
775
776 hr_utility.set_location('Calling pay_emp_action_arch',20);
777
778 pay_emp_action_arch.get_personal_information (
779 p_payroll_action_id => g_archive_pact -- archive payroll_action_id
780 , p_assactid => p_assactid -- archive assignment_action_id
781 , p_assignment_id => p_assignment_id -- current assignment_id
782 , p_curr_pymt_ass_act_id => p_curr_pymt_ass_act_id -- prepayment assignment_action_id
783 , p_curr_eff_date => g_archive_effective_date -- archive effective_date
784 , p_date_earned => p_date_earned -- payroll date_earned
785 , p_curr_pymt_eff_date => p_curr_pymt_eff_date -- prepayment effective_date
786 , p_tax_unit_id => NULL -- only required for US
787 , p_time_period_id => p_time_period_id -- payroll time_period_id
788 , p_ppp_source_action_id => NULL);
789
790 hr_utility.set_location('Returned from pay_emp_action_arch',30);
791
792 END archive_employee_details;
793
794 PROCEDURE archive_gb_employee_details (
795 p_assactid IN NUMBER
796 , p_assignment_id IN NUMBER
797 , p_curr_pymt_ass_act_id IN NUMBER
798 , p_effective_date IN DATE) IS
799
800 l_action_info_id NUMBER;
801 l_ni_cat VARCHAR2(10);
802 l_ovn NUMBER;
803 l_tax_basis VARCHAR2(10);
804 l_tax_basis_det VARCHAR2(20);
805 l_tax_code VARCHAR2(10);
806
807 l_proc VARCHAR2(60) := g_package || 'archive_gb_employee_details';
808
809 BEGIN
810
811 -- Retrieve and Archive the GB specific employee details
812
813 l_ni_cat := pay_gb_payroll_actions_pkg.get_tax_details (
814 p_run_assignment_action_id => p_curr_pymt_ass_act_id
815 , p_input_value_id => g_ni_cat_id
816 , p_paye_input_value_id => g_ni_cat_id
817 , p_date_earned => to_char(p_effective_date,'yyyy/mm/dd'));
818
819 l_tax_code := pay_gb_payroll_actions_pkg.get_tax_details (
820 p_run_assignment_action_id => p_curr_pymt_ass_act_id
821 , p_input_value_id => g_tax_code_id
822 , p_paye_input_value_id => g_paye_tax_code_id
823 , p_date_earned => to_char(p_effective_date,'yyyy/mm/dd'));
824
825 hr_utility.set_location('l_tax_code = ' || l_tax_code,40);
826
827 l_tax_basis := pay_gb_payroll_actions_pkg.get_tax_details (
828 p_run_assignment_action_id => p_curr_pymt_ass_act_id
829 , p_input_value_id => g_tax_basis_id
830 , p_paye_input_value_id => g_paye_tax_basis_id
831 , p_date_earned => to_char(p_effective_date,'yyyy/mm/dd'));
832
833 hr_utility.set_location('l_tax_basis = ' || l_tax_basis,40);
834
835 IF l_tax_basis = 'C'
836
837 THEN
838
839 l_tax_basis_det := 'Cumulative';
840
841 ELSIF l_tax_basis = 'N'
842
843 THEN
844
845 l_tax_basis_det := 'Non Cumulative';
846
847 ELSE
848
849 l_tax_basis_det := l_tax_basis;
850
851 END IF;
852
853 hr_utility.set_location('Archiving GB EMPLOYEE DETAILS',50);
854
855 pay_action_information_api.create_action_information (
856 p_action_information_id => l_action_info_id
857 , p_action_context_id => p_assactid
858 , p_action_context_type => 'AAP'
859 , p_object_version_number => l_ovn
860 , p_assignment_id => p_assignment_id
861 , p_effective_date => g_archive_effective_date
862 , p_source_id => NULL
863 , p_source_text => NULL
864 , p_action_information_category => 'GB EMPLOYEE DETAILS'
865 , p_action_information1 => NULL
866 , p_action_information2 => NULL
867 , p_action_information3 => NULL
868 , p_action_information21 => l_tax_code
869 , p_action_information22 => l_tax_basis_det
870 , p_action_information23 => l_ni_cat);
871
872 END archive_gb_employee_details;
873
874 FUNCTION process_employer_balance (
875 p_assignment_action_id IN NUMBER,
876 p_balance_dimension IN VARCHAR2)
877 RETURN NUMBER
878
879 -- This function calculates the NI Employer YTD balance, which is not
880 -- forced to be the latest balance in the NI formula. The following
881 -- formula is used instead :
882 -- NI_x_EMPLOYER = NI_x_TOTAL - NI_x_EMPLOYEE + NI_C_EMPLOYER + NI_S_EMPLOYER
883 --
884 -- The function pay_gb_payroll_actions_pkg.report_employer_balance does the
885 -- same thing, but uses globals which are not calculated if the function is
886 -- called directly so it cannot be called from this package.
887
888 IS
889
890 l_tax_district_ytd VARCHAR2(11) := '_ASG_TD_YTD';
891 l_temp NUMBER;
892 l_total NUMBER;
893
894 BEGIN
895
896 g_ni_totals_table(1).balance_name := 'NI A Total';
897 g_ni_totals_table(2).balance_name := 'NI B Total';
898 g_ni_totals_table(3).balance_name := 'NI D Total';
899 g_ni_totals_table(4).balance_name := 'NI E Total';
900 g_ni_totals_table(5).balance_name := 'NI F Total';
901 g_ni_totals_table(6).balance_name := 'NI G Total';
902
903 g_ni_totals_table(1).category := 'A';
904 g_ni_totals_table(2).category := 'B';
905 g_ni_totals_table(3).category := 'D';
906 g_ni_totals_table(4).category := 'E';
907 g_ni_totals_table(5).category := 'F';
908 g_ni_totals_table(6).category := 'G';
909
910 l_temp := 0;
911 l_total := 0;
912
913 FOR l_index IN 1..6
914
915 LOOP
916
917 IF hr_gbbal.ni_category_exists_in_year (p_assignment_action_id,
918 g_ni_totals_table(l_index).category) = 1
919
920 THEN
921
922 l_temp := pay_gb_payroll_actions_pkg.report_balance_items (
923 p_balance_name => g_ni_totals_table(l_index).balance_name
924 , p_dimension => p_balance_dimension
925 , p_assignment_action_id => p_assignment_action_id);
926
927 l_total := l_total + l_temp;
928
929 END IF;
930
931 END LOOP;
932
933 l_temp := pay_gb_payroll_actions_pkg.report_all_ni_balance (
934 p_balance_name => 'NI Employee'
935 , p_dimension => p_balance_dimension
936 , p_assignment_action_id => p_assignment_action_id);
937
938 l_total := l_total - l_temp;
939
940 l_temp := pay_gb_payroll_actions_pkg.report_balance_items (
941 p_balance_name => 'NI C Employer'
942 , p_dimension => p_balance_dimension
943 , p_assignment_action_id => p_assignment_action_id);
944
945 l_total := l_total + l_temp;
946
947 l_temp := pay_gb_payroll_actions_pkg.report_balance_items (
948 p_balance_name => 'NI S Employer'
949 , p_dimension => p_balance_dimension
950 , p_assignment_action_id => p_assignment_action_id);
951
952 l_total := l_total + l_temp;
953
954 return l_total;
955
956 END process_employer_balance;
957
958 PROCEDURE process_balance (p_action_context_id IN NUMBER,
959 p_assignment_id IN NUMBER,
960 p_source_id IN NUMBER,
961 p_effective_date IN DATE,
962 p_balance IN VARCHAR2,
963 p_dimension IN VARCHAR2,
964 p_defined_bal_id IN NUMBER,
965 p_ni_type IN VARCHAR2,
966 p_record_count IN NUMBER)
967
968 IS
969
970 --Bug 5172062
971 CURSOR csr_context_values(p_assig_action_id NUMBER, p_context_name varchar2
972 ) IS
973 SELECT pac.context_id context_id
974 ,pac.context_value context_value
975 ,ff.context_name context_name
976 FROM ff_contexts ff
977 ,pay_action_contexts pac
978 WHERE ff.context_name = p_context_name
979 AND pac.context_id = ff.context_id
980 AND pac.assignment_Action_id = p_assig_action_id;
981 --
982
983 CURSOR csr_get_reference(p_element_entry_id NUMBER
984 ,p_effective_date DATE
985 ,p_assig_action_id NUMBER)
986 IS
987 SELECT prrv.result_value reference
988 FROM pay_element_entries_f peef
989 ,pay_run_results prr
990 ,pay_run_result_values prrv
991 ,pay_input_values_f piv
992 WHERE peef.element_entry_id = p_element_entry_id
993 and piv.name ='Reference'
994 and piv.legislation_code='GB'
995 and peef.element_type_id = piv.element_type_id
996 and peef.element_type_id = prr.element_type_id
997 and peef.element_entry_id = prr.element_entry_id
998 and prr.assignment_action_id =p_assig_action_id
999 and prr.run_result_id = prrv.run_result_id
1000 and prrv.input_value_id = piv.input_value_id
1001 and p_effective_date between
1002 peef.effective_start_date and peef.effective_end_date
1003 and p_effective_date between
1004 piv.effective_start_date and piv.effective_end_date;
1005
1006
1007 CURSOR csr_get_agg_info(p_assignment_id IN NUMBER)
1008 IS
1009 SELECT per_information10
1010 FROM per_all_people_f ppf,
1011 per_all_assignments_f paf
1012 WHERE paf.assignment_id = p_assignment_id
1013 and ppf.person_id = paf.person_id
1014 and p_effective_date between
1015 paf.effective_start_date and paf.effective_end_date
1016 and p_effective_date between
1017 ppf.effective_start_date and ppf.effective_end_date;
1018
1019
1020
1021 l_action_info_id NUMBER;
1022 l_balance_value NUMBER;
1023 l_ni_balance VARCHAR2(80);
1024 l_ovn NUMBER;
1025 l_record_count VARCHAR2(10);
1026 l_context VARCHAR2(100);
1027 l_agg_flag VARCHAR2(1);
1028
1029
1030 l_proc VARCHAR2(50) := g_package || 'process_balance';
1031
1032 v_context_rec csr_context_values%ROWTYPE;
1033 v_csr_reference csr_get_reference%ROWTYPE;
1034
1035 BEGIN
1036
1037 --hr_utility.trace_on(null, 'PARC');
1038
1039 hr_utility.set_location('Entering ' || l_proc,10);
1040
1041 OPEN csr_get_agg_info(p_assignment_id);
1042 FETCH csr_get_agg_info into l_agg_flag;
1043 CLOSE csr_get_agg_info;
1044
1045 IF (p_dimension LIKE '%ELEMENT_ITD%' or p_dimension LIKE '%ELEMENT_PTD%') or
1046 (p_dimension LIKE '%PER_CO_TD_REF_ITD%' or p_dimension LIKE '%PER_CO_TD_REF_PTD%') then
1047
1048 IF p_record_count = 0 THEN
1049
1050 l_record_count := NULL;
1051
1052 ELSE
1053 l_record_count := p_record_count + 1;
1054
1055 END IF;
1056
1057
1058 IF p_dimension LIKE '%ELEMENT_ITD%' or p_dimension LIKE '%ELEMENT_PTD%' then
1059
1060 OPEN csr_context_values(p_source_id,'ORIGINAL_ENTRY_ID');
1061 LOOP
1062 FETCH csr_context_values into v_context_rec;
1063 exit when csr_context_values%notfound;
1064
1065 l_balance_value := 0;
1066
1067 IF v_context_rec.context_name IS NOT NULL AND v_context_rec.context_value IS NOT NULL THEN
1068
1069 pay_balance_pkg.set_context(v_context_rec.context_name, v_context_rec.context_value);
1070
1071 l_balance_value := pay_balance_pkg.get_value(p_defined_bal_id, p_source_id);
1072
1073
1074 OPEN csr_get_reference(v_context_rec.context_value,p_effective_date,p_source_id);
1075 FETCH csr_get_reference into v_csr_reference;
1076
1077 if v_csr_reference.reference ='Unknown' then
1078 v_csr_reference.reference := null;
1079 end if;
1080
1081
1082 IF l_balance_value <> 0 and nvl(l_agg_flag,'N')='N' then
1083
1084 pay_action_information_api.create_action_information (
1085 p_action_information_id => l_action_info_id
1086 ,p_action_context_id => p_action_context_id
1087 ,p_action_context_type => 'AAP'
1088 ,p_object_version_number => l_ovn
1089 ,p_assignment_id => p_assignment_id
1090 ,p_effective_date => p_effective_date
1091 ,p_source_id => p_source_id
1092 ,p_source_text => NULL
1093 ,p_action_information_category => 'EMEA BALANCES'
1094 ,p_action_information1 => p_defined_bal_id
1095 ,p_action_information2 => v_csr_reference.reference -- Context value
1096 ,p_action_information3 => NULL
1097 ,p_action_information4 => fnd_number.number_to_canonical(l_balance_value)
1098 ,p_action_information5 => l_record_count
1099 ,p_action_information6 => v_context_rec.context_value);
1100 END IF;
1101
1102 close csr_get_reference;
1103 END IF;
1104 END LOOP;
1105 CLOSE csr_context_values;
1106 END IF;
1107
1108 IF p_dimension LIKE '%PER_CO_TD_REF_ITD%' or p_dimension LIKE '%PER_CO_TD_REF_PTD%' THEN
1109
1110
1111 OPEN csr_context_values(p_source_id,'SOURCE_TEXT');
1112 LOOP
1113 FETCH csr_context_values into v_context_rec;
1114 exit when csr_context_values%notfound;
1115
1116 l_balance_value := 0;
1117 IF v_context_rec.context_name IS NOT NULL AND v_context_rec.context_value IS NOT NULL THEN
1118
1119 pay_balance_pkg.set_context(v_context_rec.context_name, v_context_rec.context_value);
1120 l_balance_value := pay_balance_pkg.get_value(p_defined_bal_id, p_source_id);
1121
1122 if v_context_rec.context_value ='Unknown' then
1123 v_context_rec.context_value := null;
1124 end if;
1125
1126 IF l_balance_value <> 0 and nvl(l_agg_flag,'N') = 'Y' then
1127
1128 pay_action_information_api.create_action_information (
1129 p_action_information_id => l_action_info_id
1130 ,p_action_context_id => p_action_context_id
1131 ,p_action_context_type => 'AAP'
1132 ,p_object_version_number => l_ovn
1133 ,p_assignment_id => p_assignment_id
1134 ,p_effective_date => p_effective_date
1135 ,p_source_id => p_source_id
1136 ,p_source_text => NULL
1137 ,p_action_information_category => 'EMEA BALANCES'
1138 ,p_action_information1 => p_defined_bal_id
1139 ,p_action_information2 => v_context_rec.context_value -- Context value
1140 ,p_action_information3 => NULL
1141 ,p_action_information4 => fnd_number.number_to_canonical(l_balance_value)
1142 ,p_action_information5 => l_record_count );
1143 END IF;
1144 END IF;
1145 END LOOP;
1146 CLOSE csr_context_values;
1147 END IF;
1148 -- end of court order context sensitive balances
1149
1150 ELSE
1151 hr_utility.set_location('Step ' || l_proc,20);
1152 hr_utility.set_location('p_source_id = ' || p_source_id,20);
1153 hr_utility.set_location('p_balance = ' || p_balance,20);
1154 hr_utility.set_location('p_dimension = ' || p_dimension,20);
1155 hr_utility.set_location('p_defined_bal_id = ' || p_defined_bal_id,20);
1156 hr_utility.set_location('ni_type = ' || nvl(p_ni_type,'NULL'),20);
1157
1158 IF p_ni_type = ' '
1159
1160 THEN
1161
1162 l_balance_value := pay_balance_pkg.get_value (
1163 p_defined_balance_id => p_defined_bal_id
1164 , p_assignment_action_id => p_source_id);
1165
1166 ELSIF p_ni_type = 'T'
1167
1168 THEN
1169
1170 l_ni_balance := SUBSTR(p_balance,1,3) || SUBSTR(p_balance,6);
1171
1172 hr_utility.set_location('l_ni_balance = ' || l_ni_balance,20);
1173
1174 l_balance_value := pay_gb_payroll_actions_pkg.report_all_ni_balance (
1175 p_balance_name => l_ni_balance
1176 , p_assignment_action_id => p_source_id
1177 , p_dimension => p_dimension);
1178
1179 ELSE
1180
1181 l_balance_value := pay_gb_payslip_archive.process_employer_balance (
1182 p_assignment_action_id => p_source_id
1183 ,p_balance_dimension => p_dimension);
1184
1185 END IF;
1186
1187 hr_utility.set_location('l_balance_value = ' || l_balance_value,20);
1188
1189 IF p_record_count = 0
1190
1191 THEN
1192
1193 l_record_count := NULL;
1194
1195 ELSE
1196
1197 l_record_count := p_record_count + 1;
1198 -- l_record_count := ' (' || l_record_count || ')';
1199
1200 END IF;
1201
1202 IF l_balance_value <> 0
1203
1204 THEN
1205
1206 hr_utility.set_location('Archiving EMEA BALANCES',20);
1207
1208 pay_action_information_api.create_action_information (
1209 p_action_information_id => l_action_info_id
1210 , p_action_context_id => p_action_context_id
1211 , p_action_context_type => 'AAP'
1212 , p_object_version_number => l_ovn
1213 , p_assignment_id => p_assignment_id
1214 , p_effective_date => p_effective_date
1215 , p_source_id => p_source_id
1216 , p_source_text => NULL
1217 , p_action_information_category => 'EMEA BALANCES'
1218 , p_action_information1 => p_defined_bal_id
1219 , p_action_information2 => NULL
1220 , p_action_information3 => NULL
1221 , p_action_information4 => fnd_number.number_to_canonical(l_balance_value)
1222 , p_action_information5 => l_record_count);
1223
1224 END IF;
1225 END IF;
1226
1227 hr_utility.set_location('Leaving ' || l_proc,30);
1228
1229 EXCEPTION
1230
1231 WHEN NO_DATA_FOUND
1232
1233 THEN
1234
1235 NULL;
1236
1237 END process_balance;
1238
1239 PROCEDURE get_element_info (p_action_context_id IN NUMBER,
1240 p_assignment_id IN NUMBER,
1241 p_child_assignment_action IN NUMBER,
1242 p_effective_date IN DATE,
1243 p_record_count IN NUMBER,
1244 p_run_method IN VARCHAR2)
1245 IS
1246
1247 CURSOR csr_element_values (p_assignment_action_id NUMBER,
1248 p_element_type_id NUMBER,
1249 p_input_value_id NUMBER) IS
1250 SELECT prv.result_value
1251 FROM pay_run_result_values prv,
1252 pay_run_results prr
1253 WHERE prr.status IN ('P','PA')
1254 AND prv.run_result_id = prr.run_result_id
1255 AND prr.assignment_action_id = p_assignment_action_id
1256 AND prr.element_type_id = p_element_type_id
1257 AND prv.input_value_id = p_input_value_id
1258 AND prv.result_value IS NOT NULL;
1259
1260 l_action_info_id NUMBER;
1261 l_column_sequence NUMBER;
1262 l_element_type_id NUMBER;
1263 l_main_sequence NUMBER;
1264 l_multi_sequence NUMBER;
1265 l_ovn NUMBER;
1266 l_record_count VARCHAR2(10);
1267 l_result_value pay_run_result_values.result_value%TYPE;
1268
1269 BEGIN
1270
1271 hr_utility.set_location('Entering get_element_info',10);
1272
1273 l_column_sequence := 0;
1274 l_element_type_id := 0;
1275 l_main_sequence := 0;
1276 l_multi_sequence := NULL;
1277
1278 IF p_record_count = 0
1279
1280 THEN
1281
1282 l_record_count := NULL;
1283
1284 ELSE
1285
1286 l_record_count := p_record_count + 1;
1287
1288 END IF;
1289
1290 hr_utility.set_location('g_max_element_index = ' || g_max_element_index,10);
1291
1292 FOR l_index IN 1 .. g_max_element_index
1293
1294 LOOP
1295
1296 hr_utility.set_location('element_type_id = ' || g_element_table(l_index).element_type_id,10);
1297 hr_utility.set_location('input_value_id = ' || g_element_table(l_index).input_value_id,10);
1298 hr_utility.set_location('p_child_assignment_action = ' || p_child_assignment_action,10);
1299
1300 FOR rec_element_value IN csr_element_values (
1301 p_child_assignment_action
1302 , g_element_table(l_index).element_type_id
1303 , g_element_table(l_index).input_value_id)
1304
1305 LOOP
1306
1307 hr_utility.set_location('element_type_id = ' || g_element_table(l_index).element_type_id,10);
1308 hr_utility.set_location('input_value_id = ' || g_element_table(l_index).input_value_id,10);
1309 hr_utility.set_location('Archiving EMEA ELEMENT INFO',20);
1310
1311 hr_utility.set_location('l_element_type_id = ' || l_element_type_id,20);
1312 hr_utility.set_location('g_element_table.element_type_id = ' || g_element_table(l_index).element_type_id,20);
1313
1314
1315 IF l_element_type_id <> g_element_table(l_index).element_type_id
1316
1317 THEN
1318
1319 l_main_sequence := l_main_sequence + 1;
1320
1321 END IF;
1322
1323 hr_utility.set_location('l_main_sequence = ' || l_main_sequence,20);
1324
1325 l_column_sequence := l_column_sequence + 1;
1326
1327 -- If the run method is P, Process Separate, then only archive the data if
1328 -- a skip rule (formula_id) has been set. If there is no skip rule then the
1329 -- element info will be archived for the normal assignment action and doesn't
1330 -- need to be archived twice. If it is then duplicates will be displayed on
1331 -- the payslip.
1332
1333 IF p_run_method = 'P' AND g_element_table(l_index).formula_id IS NULL
1334
1335 THEN
1336
1337 NULL;
1338
1339 ELSE
1340 SELECT decode(g_element_table(l_index).uom, 'M',
1341 ltrim(rtrim(to_char(fnd_number.canonical_to_number(rec_element_value.result_value), '999999999999999990.00'))),
1342 rec_element_value.result_value)
1343 INTO l_result_value
1344 FROM dual;
1345 --
1346 pay_action_information_api.create_action_information (
1347 p_action_information_id => l_action_info_id
1348 , p_action_context_id => p_action_context_id
1349 , p_action_context_type => 'AAP'
1350 , p_object_version_number => l_ovn
1351 , p_assignment_id => p_assignment_id
1352 , p_effective_date => p_effective_date
1353 , p_source_id => p_child_assignment_action
1354 , p_source_text => NULL
1355 , p_action_information_category => 'EMEA ELEMENT INFO'
1356 , p_action_information1 => g_element_table(l_index).element_type_id
1357 , p_action_information2 => g_element_table(l_index).input_value_id
1358 , p_action_information3 => NULL
1359 , p_action_information4 => l_result_value
1360 , p_action_information5 => l_main_sequence
1361 , p_action_information6 => l_multi_sequence
1362 , p_action_information7 => l_column_sequence
1363 , p_action_information8 => l_record_count);
1364
1365 END IF;
1366
1367 l_multi_sequence := NVL(l_multi_sequence,0) + 1;
1368 l_element_type_id := g_element_table(l_index).element_type_id;
1369
1370 END LOOP;
1371
1372 l_multi_sequence := NULL;
1373
1374 END LOOP;
1375
1376 EXCEPTION
1377
1378 WHEN NO_DATA_FOUND
1379
1380 THEN
1381
1382 NULL;
1383
1384 END get_element_info;
1385
1386 PROCEDURE range_cursor (pactid IN NUMBER,
1387 sqlstr OUT NOCOPY VARCHAR2)
1388 -- public procedure which archives the payroll information, then returns a
1389 -- varchar2 defining a SQL statement to select all the people that may be
1390 -- eligible for payslip reports.
1391 -- The archiver uses this cursor to split the people into chunks for parallel
1392 -- processing.
1393 IS
1394 --
1395 l_proc CONSTANT VARCHAR2(50):= g_package||'range_cursor';
1396 -- vars for constructing the sqlstr
1397 l_range_cursor VARCHAR2(4000) := NULL;
1398 l_parameter_match VARCHAR2(500) := NULL;
1399 l_ovn NUMBER(15);
1400 l_request_id NUMBER;
1401 l_action_info_id NUMBER(15);
1402 l_business_group_id NUMBER;
1403
1404 CURSOR csr_input_value_id(p_element_name CHAR,
1405 p_value_name CHAR) IS
1406 SELECT pet.element_type_id,
1407 piv.input_value_id
1408 FROM pay_input_values_f piv,
1409 pay_element_types_f pet
1410 WHERE piv.element_type_id = pet.element_type_id
1411 AND pet.legislation_code = 'GB'
1412 AND pet.element_name = p_element_name
1413 AND piv.name = p_value_name;
1414
1415 /* 4071160 - This cursor to get payrolls based on a given consolidation set is
1416 is not consistent with other processes like prePayments and Cheque Writer etc.
1417 CURSOR csr_payrolls (p_payroll_id NUMBER,
1418 p_consolidation_set_id NUMBER,
1419 p_effective_date DATE) IS
1420 SELECT ppf.payroll_id
1421 FROM pay_all_payrolls_f ppf
1422 WHERE ppf.consolidation_set_id = p_consolidation_set_id
1423 AND ppf.payroll_id = NVL(p_payroll_id,ppf.payroll_id)
1424 AND p_effective_date BETWEEN
1425 ppf.effective_start_date AND ppf.effective_end_date;
1426 4071160 */
1427
1428
1429 --Commented for Bug fix 5209228
1430 /* CURSOR csr_payroll_info(p_payroll_id NUMBER,
1431 p_consolidation_id NUMBER,
1432 p_start_date DATE,
1433 p_end_date DATE) IS
1434 SELECT pact.payroll_action_id payroll_action_id,
1435 pact.effective_date effective_date,
1436 pact.date_earned date_earned,
1437 pact.payroll_id,
1438 org.org_information1 employers_ref_no,
1439 org.org_information2 tax_office_name,
1440 org.org_information3 employer_name,
1441 org.org_information4 employer_address,
1442 org.org_information8 tax_office_phone_no,
1443 ppf.payroll_name payroll_name,
1444 ppf.period_type period_type,
1445 pact.pay_advice_message payroll_message
1446 FROM pay_payrolls_f ppf,
1447 pay_payroll_actions pact,
1448 hr_soft_coding_keyflex flex,
1449 hr_organization_information org
1450 WHERE ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
1451 AND org.org_information_context = 'Tax Details References'
1452 AND org.org_information1 = flex.segment1
1453 AND ppf.business_group_id = org.organization_id
1454 AND pact.payroll_id = ppf.payroll_id
1455 AND pact.effective_date BETWEEN
1456 ppf.effective_start_date AND ppf.effective_end_date
1457 AND pact.payroll_id = NVL(p_payroll_id,pact.payroll_id)
1458 AND pact.consolidation_set_id = p_consolidation_id -- 4071160
1459 AND pact.effective_date BETWEEN
1460 p_start_date AND p_end_date
1461 AND (pact.action_type = 'P' OR
1462 pact.action_type = 'U')
1463 AND pact.action_status = 'C'
1464 AND NOT EXISTS (SELECT NULL
1465 FROM pay_action_information pai
1466 WHERE pai.action_context_id = pact.payroll_action_id
1467 AND pai.action_context_type = 'PA'
1468 AND pai.action_information_category = 'EMEA PAYROLL INFO');
1469
1470 CURSOR csr_payroll_mesg (p_payroll_id NUMBER,
1471 p_start_date DATE,
1472 p_end_date DATE) IS
1473 SELECT pact.payroll_action_id payroll_action_id,
1474 pact.effective_date effective_date,
1475 pact.date_earned date_earned,
1476 pact.pay_advice_message payroll_message
1477 FROM pay_payrolls_f ppf,
1478 pay_payroll_actions pact
1479 WHERE pact.payroll_id = ppf.payroll_id
1480 AND pact.effective_date BETWEEN
1481 ppf.effective_start_date AND ppf.effective_end_date
1482 AND pact.payroll_id = p_payroll_id
1483 AND pact.effective_date BETWEEN
1484 p_start_date AND p_end_date
1485 AND (pact.action_type = 'R' OR
1486 pact.action_type = 'Q')
1487 AND pact.action_status = 'C'
1488 AND NOT EXISTS (SELECT NULL
1489 FROM pay_action_information pai
1490 WHERE pai.action_context_id = pact.payroll_action_id
1491 AND pai.action_context_type = 'PA'
1492 AND pai.action_information_category = 'EMPLOYEE OTHER INFORMATION');
1493 */
1494
1495 l_assignment_set_id NUMBER;
1496 l_bg_id NUMBER;
1497 l_canonical_end_date DATE;
1498 l_canonical_start_date DATE;
1499 l_consolidation_set NUMBER;
1500 l_end_date VARCHAR2(30);
1501 l_legislation_code VARCHAR2(30) := 'GB';
1502 l_payroll_id NUMBER;
1503 l_start_date VARCHAR2(30);
1504 l_tax_period_no VARCHAR2(30);
1505
1506 BEGIN
1507
1508 -- hr_utility.trace_on(NULL,'UKPS1');
1509
1510 hr_utility.set_location('Entering ' || l_proc,10);
1511
1512 pay_gb_payslip_archive.get_parameters (
1513 p_payroll_action_id => pactid
1514 , p_token_name => 'PAYROLL'
1515 , p_token_value => l_payroll_id);
1516
1517 pay_gb_payslip_archive.get_parameters (
1518 p_payroll_action_id => pactid
1519 , p_token_name => 'CONSOLIDATION'
1520 , p_token_value => l_consolidation_set);
1521
1522 pay_gb_payslip_archive.get_parameters (
1523 p_payroll_action_id => pactid
1524 , p_token_name => 'ASSIGNMENT_SET'
1525 , p_token_value => l_assignment_set_id);
1526
1527 pay_gb_payslip_archive.get_parameters (
1528 p_payroll_action_id => pactid
1529 , p_token_name => 'START_DATE'
1530 , p_token_value => l_start_date);
1531
1532 pay_gb_payslip_archive.get_parameters (
1533 p_payroll_action_id => pactid
1534 , p_token_name => 'END_DATE'
1535 , p_token_value => l_end_date);
1536
1537 pay_gb_payslip_archive.get_parameters (
1538 p_payroll_action_id => pactid
1539 , p_token_name => 'BG_ID'
1540 , p_token_value => l_bg_id);
1541
1542 hr_utility.set_location('Step ' || l_proc,20);
1543 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
1544 hr_utility.set_location('l_start_date = ' || l_start_date,20);
1545 hr_utility.set_location('l_end_date = ' || l_end_date,20);
1546
1547 l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
1548 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
1549
1550 -- archive EMEA PAYROLL INFO for each prepayment run identified
1551
1552 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
1553 hr_utility.set_location('l_consolidation_set = ' || l_consolidation_set,20);
1554 hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
1555 hr_utility.set_location('l_canonical_end_date = ' || l_canonical_end_date,20);
1556
1557 g_max_user_balance_index := 0;
1558
1559 pay_gb_payslip_archive.get_eit_definitions (
1560 p_pactid => pactid
1561 , p_business_group_id => l_bg_id
1562 , p_payroll_pact => NULL
1563 , p_effective_date => l_canonical_start_date
1564 , p_eit_context => g_balance_context
1565 , p_archive => 'Y');
1566
1567 pay_gb_payslip_archive.get_eit_definitions (
1568 p_pactid => pactid
1569 , p_business_group_id => l_bg_id
1570 , p_payroll_pact => NULL
1571 , p_effective_date => l_canonical_start_date
1572 , p_eit_context => g_element_context
1573 , p_archive => 'Y');
1574
1575 pay_gb_payslip_archive.setup_element_definitions (
1576 p_pactid => pactid
1577 , p_payroll_pact => NULL
1578 , p_business_group_id => l_bg_id
1579 , p_effective_date => l_canonical_start_date);
1580
1581
1582 /* 4071160 - This cursor to get payrolls based on a given consolidation set is
1583 is not consistent with other processes like prePayments and Cheque Writer etc.
1584 FOR rec_payrolls in csr_payrolls(l_payroll_id,
1585 l_consolidation_set,
1586 l_canonical_end_date)
1587 LOOP
1588
1589 hr_utility.set_location('Calling arch_pay_action_level_data',25);
1590
1591 pay_emp_action_arch.arch_pay_action_level_data (
1592 p_payroll_action_id => pactid
1593 , p_payroll_id => rec_payrolls.payroll_id
1594 , p_effective_date => l_canonical_end_date);
1595
1596 END LOOP;
1597 4071160 */
1598
1599
1600
1601 --Commented for Bug fix 5209228
1602 /* FOR rec_payroll_info in csr_payroll_info(l_payroll_id,
1603 l_consolidation_set,
1604 l_canonical_start_date,
1605 l_canonical_end_date)
1606
1607 LOOP
1608
1609 pay_balance_pkg.set_context('PAYROLL_ACTION_ID'
1610 , rec_payroll_info.payroll_action_id);
1611
1612 hr_utility.set_location('rec_payroll_info.payroll_action_id = ' || rec_payroll_info.payroll_action_id,30);
1613 hr_utility.set_location('rec_payroll_info.tax_office_name = ' || rec_payroll_info.tax_office_name,30);
1614 hr_utility.set_location('rec_payroll_info.tax_office_phone_no = ' || rec_payroll_info.tax_office_phone_no,30);
1615 hr_utility.set_location('rec_payroll_info.employers_ref_no = ' || rec_payroll_info.employers_ref_no,30);
1616
1617 hr_utility.set_location('Archiving EMEA PAYROLL INFO',30);
1618
1619 -- Added for 4071160
1620 pay_emp_action_arch.arch_pay_action_level_data (
1621 p_payroll_action_id => pactid
1622 , p_payroll_id => rec_payroll_info.payroll_id
1623 , p_effective_date => l_canonical_end_date);
1624 -- End 4071160
1625
1626 pay_action_information_api.create_action_information (
1627 p_action_information_id => l_action_info_id
1628 , p_action_context_id => pactid
1629 , p_action_context_type => 'PA'
1630 , p_object_version_number => l_ovn
1631 , p_effective_date => rec_payroll_info.effective_date
1632 , p_source_id => NULL
1633 , p_source_text => NULL
1634 , p_action_information_category => 'EMEA PAYROLL INFO'
1635 , p_action_information1 => rec_payroll_info.payroll_action_id
1636 , p_action_information2 => rec_payroll_info.payroll_id
1637 , p_action_information3 => NULL
1638 , p_action_information4 => rec_payroll_info.tax_office_name
1639 , p_action_information5 => rec_payroll_info.tax_office_phone_no
1640 , p_action_information6 => rec_payroll_info.employers_ref_no);
1641
1642 END LOOP;
1643
1644 -- The Payroll level message is archived in the generic archive structure
1645 -- EMPLOYEE OTHER INFORMATION
1646
1647 FOR rec_payroll_msg in csr_payroll_mesg(l_payroll_id,
1648 l_canonical_start_date,
1649 l_canonical_end_date)
1650
1651 LOOP
1652
1653 IF rec_payroll_msg.payroll_message IS NOT NULL
1654
1655 THEN
1656
1657 pay_action_information_api.create_action_information (
1658 p_action_information_id => l_action_info_id
1659 , p_action_context_id => pactid
1660 , p_action_context_type => 'PA'
1661 , p_object_version_number => l_ovn
1662 , p_effective_date => rec_payroll_msg.effective_date
1663 , p_source_id => NULL
1664 , p_source_text => NULL
1665 , p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
1666 , p_action_information1 => rec_payroll_msg.payroll_action_id
1667 , p_action_information2 => 'MESG'
1668 , p_action_information3 => NULL
1669 , p_action_information4 => NULL
1670 , p_action_information5 => NULL
1671 , p_action_information6 => rec_payroll_msg.payroll_message);
1672
1673 END IF;
1674
1675 END LOOP;*/
1676 --
1677 -- Performance enhancement, the range code can now restrict
1678 -- by payroll_id, if the payroll_id is not null, ie has
1679 -- been defined in the conc process call. The l_payroll_id
1680 -- was selected by the get_parameters call above.
1681 --
1682 if l_payroll_id is null then
1683 --
1684 -- Use full cursor not restricting by payroll
1685 --
1686 hr_utility.trace('Range Cursor Not using Payroll Restriction');
1687 sqlstr := 'SELECT DISTINCT person_id
1688 FROM per_people_f ppf,
1689 pay_payroll_actions ppa
1690 WHERE ppa.payroll_action_id = :payroll_action_id
1691 AND ppa.business_group_id +0= ppf.business_group_id
1692 ORDER BY ppf.person_id';
1693 else
1694 --
1695 -- The Payroll ID was used as parameter, so restrict by this
1696 --
1697 hr_utility.trace('Range Cursor using Payroll Restriction');
1698 sqlstr := 'SELECT DISTINCT ppf.person_id
1699 FROM per_all_people_f ppf,
1700 pay_payroll_actions ppa,
1701 per_all_assignments_f paaf
1702 WHERE ppa.payroll_action_id = :payroll_action_id
1703 AND ppf.business_group_id +0 = ppa.business_group_id
1704 AND paaf.person_id = ppf.person_id
1705 AND paaf.payroll_id = '|| to_char(l_payroll_id) ||
1706 ' ORDER BY ppf.person_id';
1707 end if;
1708 --
1709 hr_utility.set_location('Leaving ' || l_proc,40);
1710
1711 END range_cursor;
1712 ---------------------------------------------------------------------------
1713 -- Function: range_person_on.
1714 -- Description: Returns true if the range_person performance enhancement is
1715 -- enabled for the system. Used by action_creation.
1716 ---------------------------------------------------------------------------
1717 FUNCTION range_person_on RETURN BOOLEAN IS
1718 --
1719 CURSOR csr_action_parameter is
1720 select parameter_value
1721 from pay_action_parameters
1722 where parameter_name = 'RANGE_PERSON_ID';
1723 --
1724 CURSOR csr_range_format_param is
1725 select par.parameter_value
1726 from pay_report_format_parameters par,
1727 pay_report_format_mappings_f map
1728 where map.report_format_mapping_id = par.report_format_mapping_id
1729 and map.report_type = 'UKPS'
1730 and map.report_format = 'UKPSGEN'
1731 and map.report_qualifier = 'GB'
1732 and par.parameter_name = 'RANGE_PERSON_ID'; -- Bug fix 5567246
1733 --
1734 l_return boolean;
1735 l_action_param_val varchar2(30);
1736 l_report_param_val varchar2(30);
1737 --
1738 BEGIN
1739 hr_utility.set_location('range_person_on',10);
1740 --
1741 BEGIN
1742 open csr_action_parameter;
1743 fetch csr_action_parameter into l_action_param_val;
1744 close csr_action_parameter;
1745 --
1746 hr_utility.set_location('range_person_on',20);
1747 open csr_range_format_param;
1748 fetch csr_range_format_param into l_report_param_val;
1749 close csr_range_format_param;
1750 --
1751 hr_utility.set_location('range_person_on',30);
1752 EXCEPTION WHEN NO_DATA_FOUND THEN
1753 l_return := FALSE;
1754 END;
1755 --
1756 hr_utility.set_location('range_person_on',40);
1757 IF l_action_param_val = 'Y' AND l_report_param_val = 'Y' THEN
1758 l_return := TRUE;
1759 hr_utility.trace('Range Person = True');
1760 ELSE
1761 l_return := FALSE;
1762 END IF;
1763 --
1764 RETURN l_return;
1765 --
1766 END range_person_on;
1767 ---------------------------------------------------------------------------
1768 PROCEDURE action_creation (pactid in number,
1769 stperson in number,
1770 endperson in number,
1771 chunk in number) is
1772 --
1773 CURSOR csr_prepaid_assignments(p_pact_id NUMBER,
1774 stperson NUMBER,
1775 endperson NUMBER,
1776 p_payroll_id NUMBER,
1777 p_consolidation_id NUMBER) IS
1778 SELECT act.assignment_id assignment_id,
1779 act.assignment_action_id run_action_id,
1780 act1.assignment_action_id prepaid_action_id
1781 FROM pay_payroll_actions ppa,
1782 pay_payroll_actions appa,
1783 pay_payroll_actions appa2,
1784 pay_assignment_actions act,
1785 pay_assignment_actions act1,
1786 pay_action_interlocks pai,
1787 per_all_assignments_f as1
1788 WHERE ppa.payroll_action_id = p_pact_id
1789 AND appa.consolidation_set_id = p_consolidation_id
1790 AND appa.effective_date BETWEEN
1791 ppa.start_date AND ppa.effective_date
1792 AND as1.person_id BETWEEN
1793 stperson AND endperson
1794 AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
1795 AND act.payroll_action_id = appa.payroll_action_id
1796 AND act.source_action_id IS NULL
1797 AND as1.assignment_id = act.assignment_id
1798 AND ppa.effective_date BETWEEN
1799 as1.effective_start_date AND as1.effective_end_date
1800 AND act.action_status = 'C'
1801 AND act.assignment_action_id = pai.locked_action_id
1802 AND act1.assignment_action_id = pai.locking_action_id
1803 AND act1.action_status = 'C'
1804 AND act1.payroll_action_id = appa2.payroll_action_id
1805 AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
1806 AND (as1.payroll_id = p_payroll_id OR p_payroll_id IS NULL)
1807 AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
1808 FROM pay_action_interlocks pai1,
1809 pay_assignment_actions act2,
1810 pay_payroll_actions appa3
1811 WHERE pai1.locked_action_id = act.assignment_action_id
1812 AND act2.assignment_action_id = pai1.locking_action_id
1813 AND act2.payroll_action_id = appa3.payroll_action_id
1814 AND appa3.action_type = 'X'
1815 AND appa3.report_type = 'UKPS')
1816 ORDER BY act.assignment_id
1817 FOR UPDATE OF as1.assignment_id;
1818 --
1819 -- csr_range_pre_assignments is a copy of csr_prepaid_assignments
1820 -- but with a join to pay_population_ranges for performance enhancement
1821 -- stperson and endperson are not needed, uses chunk.
1822 --
1823 CURSOR csr_range_pre_assignments(p_pact_id NUMBER,
1824 p_payroll_id NUMBER,
1825 p_consolidation_id NUMBER) IS
1826 SELECT act.assignment_id assignment_id,
1827 act.assignment_action_id run_action_id,
1828 act1.assignment_action_id prepaid_action_id
1829 FROM pay_payroll_actions ppa,
1830 pay_payroll_actions appa,
1831 pay_payroll_actions appa2,
1832 pay_assignment_actions act,
1833 pay_assignment_actions act1,
1834 pay_action_interlocks pai,
1835 per_all_assignments_f as1,
1836 pay_population_ranges ppr
1837 WHERE ppa.payroll_action_id = p_pact_id
1838 AND appa.consolidation_set_id = p_consolidation_id
1839 AND appa.effective_date BETWEEN
1840 ppa.start_date AND ppa.effective_date
1841 AND as1.person_id = ppr.person_id
1842 AND ppr.chunk_number = chunk
1843 AND ppr.payroll_action_id = p_pact_id
1844 AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
1845 AND act.payroll_action_id = appa.payroll_action_id
1846 AND act.source_action_id IS NULL
1847 AND as1.assignment_id = act.assignment_id
1848 AND ppa.effective_date BETWEEN
1849 as1.effective_start_date AND as1.effective_end_date
1850 AND act.action_status = 'C'
1851 AND act.assignment_action_id = pai.locked_action_id
1852 AND act1.assignment_action_id = pai.locking_action_id
1853 AND act1.action_status = 'C'
1854 AND act1.payroll_action_id = appa2.payroll_action_id
1855 AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
1856 AND (as1.payroll_id = p_payroll_id OR p_payroll_id IS NULL)
1857 AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
1858 FROM pay_action_interlocks pai1,
1859 pay_assignment_actions act2,
1860 pay_payroll_actions appa3
1861 WHERE pai1.locked_action_id = act.assignment_action_id
1862 AND act2.assignment_action_id = pai1.locking_action_id
1863 AND act2.payroll_action_id = appa3.payroll_action_id
1864 AND appa3.action_type = 'X'
1865 AND appa3.report_type = 'UKPS')
1866 ORDER BY act.assignment_id
1867 FOR UPDATE OF as1.assignment_id;
1868 --
1869 l_actid NUMBER;
1870 l_canonical_end_date DATE;
1871 l_canonical_start_date DATE;
1872 l_consolidation_set VARCHAR2(30);
1873 l_end_date VARCHAR2(20);
1874 l_payroll_id NUMBER;
1875 l_prepay_action_id NUMBER;
1876 l_start_date VARCHAR2(20);
1877
1878 l_proc VARCHAR2(50) := g_package||'action_creation';
1879
1880 BEGIN
1881 -- hr_utility.trace_on(null,'UKPS3');
1882 hr_utility.set_location('Entering ' || l_proc,10);
1883
1884 pay_gb_payslip_archive.get_parameters (
1885 p_payroll_action_id => pactid
1886 , p_token_name => 'PAYROLL'
1887 , p_token_value => l_payroll_id);
1888
1889 pay_gb_payslip_archive.get_parameters (
1890 p_payroll_action_id => pactid
1891 , p_token_name => 'CONSOLIDATION'
1892 , p_token_value => l_consolidation_set);
1893
1894 pay_gb_payslip_archive.get_parameters (
1895 p_payroll_action_id => pactid
1896 , p_token_name => 'START_DATE'
1897 , p_token_value => l_start_date);
1898
1899 pay_gb_payslip_archive.get_parameters (
1900 p_payroll_action_id => pactid
1901 , p_token_name => 'END_DATE'
1902 , p_token_value => l_end_date);
1903
1904 hr_utility.set_location('Step ' || l_proc,20);
1905 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
1906 hr_utility.set_location('l_start_date = ' || l_start_date,20);
1907 hr_utility.set_location('l_end_date = ' || l_end_date,20);
1908
1909 l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
1910 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
1911
1912 l_prepay_action_id := 0;
1913 --
1914 -- Check that the Range Person settings are on, if so,
1915 -- use csr_range_pre_assignments. If not, use csr_prepaid_assignments.
1916 --
1917 IF range_person_on THEN
1918
1919 FOR csr_rec IN csr_range_pre_assignments(pactid, l_payroll_id, l_consolidation_set)
1920 LOOP
1921 IF l_prepay_action_id <> csr_rec.prepaid_action_id THEN
1922 --
1923 SELECT pay_assignment_actions_s.NEXTVAL
1924 INTO l_actid
1925 FROM dual;
1926 --
1927 -- Create the archive assignment action for master action
1928 --
1929 hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,pactid,chunk,NULL);
1930 --
1931 -- Create Archive to master action interlock and
1932 -- the archive to prepayment asg action interlock
1933 --
1934 hr_utility.trace('creating lock1 ' || l_actid || ' to ' || csr_rec.run_action_id);
1935 hr_utility.trace('creating lock2 ' || l_actid || ' to ' || csr_rec.prepaid_action_id);
1936 --
1937 hr_nonrun_asact.insint(l_actid,csr_rec.prepaid_action_id);
1938 END IF;
1939 --
1940 hr_nonrun_asact.insint(l_actid,csr_rec.run_action_id);
1941 l_prepay_action_id := csr_rec.prepaid_action_id;
1942 END LOOP;
1943 --
1944 ELSE
1945 --
1946 -- Use the original code for non performance-enhanced cursor
1947 --
1948 FOR csr_rec IN csr_prepaid_assignments(pactid,
1949 stperson,
1950 endperson,
1951 l_payroll_id,
1952 l_consolidation_set)
1953
1954 LOOP
1955
1956 IF l_prepay_action_id <> csr_rec.prepaid_action_id
1957
1958 THEN
1959
1960 SELECT pay_assignment_actions_s.NEXTVAL
1961 INTO l_actid
1962 FROM dual;
1963
1964 -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
1965
1966 hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,pactid,chunk,NULL);
1967
1968 -- CREATE THE ARCHIVE TO PAYROLL MASTER ASSIGNMENT ACTION INTERLOCK AND
1969 -- THE ARCHIVE TO PREPAYMENT ASSIGNMENT ACTION INTERLOCK
1970
1971 hr_utility.set_location('creating lock1 ' || l_actid || ' to ' || csr_rec.run_action_id,20);
1972 hr_utility.set_location('creating lock2 ' || l_actid || ' to ' || csr_rec.prepaid_action_id,20);
1973
1974 hr_nonrun_asact.insint(l_actid,csr_rec.prepaid_action_id);
1975
1976 END IF;
1977
1978 hr_nonrun_asact.insint(l_actid,csr_rec.run_action_id);
1979
1980 l_prepay_action_id := csr_rec.prepaid_action_id;
1981
1982 END LOOP;
1983 --
1984 END IF; -- Range Person Code check.
1985
1986 hr_utility.set_location('Leaving ' || l_proc,20);
1987
1988 END action_creation;
1989
1990 PROCEDURE archive_code (p_assactid in number,
1991 p_effective_date in date) IS
1992
1993 CURSOR csr_assignment_actions(p_locking_action_id NUMBER) IS
1994 SELECT pre.locked_action_id pre_assignment_action_id,
1995 pay.locked_action_id master_assignment_action_id,
1996 assact.assignment_id assignment_id,
1997 assact.payroll_action_id pay_payroll_action_id,
1998 paa.effective_date effective_date,
1999 ppaa.effective_date pre_effective_date,
2000 paa.date_earned date_earned,
2001 paa.time_period_id time_period_id
2002 FROM pay_action_interlocks pre,
2003 pay_action_interlocks pay,
2004 pay_payroll_actions paa,
2005 pay_payroll_actions ppaa,
2006 pay_assignment_actions assact,
2007 pay_assignment_actions passact
2008 WHERE pre.locked_action_id = pay.locking_action_id
2009 AND pre.locking_action_id = p_locking_action_id
2010 AND pre.locked_action_id = passact.assignment_action_id
2011 AND passact.payroll_action_id = ppaa.payroll_action_id
2012 AND ppaa.action_type IN ('P','U')
2013 AND pay.locked_action_id = assact.assignment_action_id
2014 AND assact.payroll_action_id = paa.payroll_action_id
2015 AND assact.source_action_id IS NULL
2016 ORDER BY pay.locked_action_id;
2017
2018 CURSOR csr_child_actions(p_master_assignment_action NUMBER,
2019 p_payroll_action_id NUMBER,
2020 p_assignment_id NUMBER,
2021 p_effective_date DATE ) IS
2022 SELECT paa.assignment_action_id child_assignment_action_id,
2023 'S' run_type
2024 FROM pay_assignment_actions paa,
2025 pay_run_types_f prt
2026 WHERE paa.source_action_id = p_master_assignment_action
2027 AND paa.payroll_action_id = p_payroll_action_id
2028 AND paa.assignment_id = p_assignment_id
2029 AND paa.run_type_id = prt.run_type_id
2030 AND prt.run_method = 'S'
2031 AND p_effective_date BETWEEN
2032 prt.effective_start_date AND prt.effective_end_date
2033 UNION
2034 SELECT paa.assignment_action_id child_assignment_action_id,
2035 'NP' run_type
2036 FROM pay_assignment_actions paa
2037 WHERE paa.payroll_action_id = p_payroll_action_id
2038 AND paa.assignment_id = p_assignment_id
2039 AND paa.action_sequence = (SELECT MAX(paa1.action_sequence)
2040 FROM pay_assignment_actions paa1,
2041 pay_run_types_f prt1
2042 WHERE prt1.run_type_id = paa1.run_type_id
2043 AND prt1.run_method IN ('N','P')
2044 AND paa1.payroll_action_id = p_payroll_action_id
2045 AND paa1.assignment_id = p_assignment_id
2046 AND paa1.source_action_id = p_master_assignment_action
2047 AND p_effective_date BETWEEN
2048 prt1.effective_start_date AND prt1.effective_end_date);
2049
2050 CURSOR csr_np_children (p_assignment_action_id NUMBER,
2051 p_payroll_action_id NUMBER,
2052 p_assignment_id NUMBER,
2053 p_effective_date DATE) IS
2054 SELECT paa.assignment_action_id np_assignment_action_id,
2055 prt.run_method
2056 FROM pay_assignment_actions paa,
2057 pay_run_types_f prt
2058 WHERE paa.source_action_id = p_assignment_action_id
2059 AND paa.payroll_action_id = p_payroll_action_id
2060 AND paa.assignment_id = p_assignment_id
2061 AND paa.run_type_id = prt.run_type_id
2062 AND prt.run_method IN ('N','P')
2063 AND p_effective_date BETWEEN
2064 prt.effective_start_date AND prt.effective_end_date;
2065
2066 l_actid NUMBER;
2067 l_action_context_id NUMBER;
2068 l_action_info_id NUMBER(15);
2069 l_assignment_action_id NUMBER;
2070 l_business_group_id NUMBER;
2071 l_child_count NUMBER;
2072 l_chunk_number NUMBER;
2073 l_date_earned DATE;
2074 l_ovn NUMBER;
2075 l_person_id NUMBER;
2076 l_record_count NUMBER;
2077 l_salary VARCHAR2(10);
2078 l_sequence NUMBER;
2079 csr_rec csr_assignment_actions%rowtype;
2080
2081 l_proc VARCHAR2(50) := g_package || 'archive_code';
2082
2083 BEGIN
2084
2085 -- hr_utility.trace_on(NULL,'UKPS2');
2086
2087 hr_utility.set_location('Entering '|| l_proc,10);
2088
2089 hr_utility.set_location('Step '|| l_proc,20);
2090 hr_utility.set_location('p_assactid = ' || p_assactid,20);
2091
2092 -- retrieve the chunk number for the current assignment action
2093 SELECT paa.chunk_number
2094 INTO l_chunk_number
2095 FROM pay_assignment_actions paa
2096 WHERE paa.assignment_action_id = p_assactid;
2097
2098 l_action_context_id := p_assactid;
2099
2100 l_record_count := 0;
2101
2102 /*****************************************************************
2103 ** Cursor to return all the Runs for a Pre Payment Process which
2104 ** is being archived.
2105 *****************************************************************/
2106 OPEN csr_assignment_actions(p_assactid);
2107 LOOP
2108 fetch csr_assignment_actions into csr_rec;
2109 hr_utility.set_location('csr_rec.master_assignment_action_id = ' ||
2110 csr_rec.master_assignment_action_id,20);
2111 hr_utility.set_location('csr_rec.pre_assignment_action_id = ' ||
2112 csr_rec.pre_assignment_action_id,20);
2113
2114 /*****************************************************************
2115 ** Archive the employee details for the last row returned by
2116 ** the cursor csr_assignment_actions.
2117 ** This will ensure that the the correct time period is passed
2118 ** to the global package if there are multiple runs in a single
2119 ** pre payment. Also, the global package can not be called
2120 ** multiple times if there are multiple runs in a single pre
2121 ** payment as it will archive the Net Distribution every time.
2122 **
2123 ** Call the global package only if the cursor fetches a record.
2124 *****************************************************************/
2125 if csr_assignment_actions%notfound then
2126 if csr_assignment_actions%rowcount > 0 then
2127 /* pay_gb_payslip_archive.archive_employee_details
2128 p_assactid => p_assactid
2129 ,p_assignment_id => assignment_id
2130 ,p_curr_pymt_ass_act_id => prepayment assignment_action_id
2131 ,p_effective_date => payroll effective_date
2132 ,p_date_earned => payroll date_earned
2133 ,p_curr_pymt_eff_date => prepayment effective_date
2134 ,p_time_period_id => payroll time_period_id
2135 ,p_record_count => l_record_count);
2136 */
2137 pay_gb_payslip_archive.archive_employee_details (
2138 p_assactid => p_assactid
2139 ,p_assignment_id => csr_rec.assignment_id
2140 ,p_curr_pymt_ass_act_id => csr_rec.pre_assignment_action_id
2141 ,p_effective_date => csr_rec.effective_date
2142 ,p_date_earned => csr_rec.date_earned
2143 ,p_curr_pymt_eff_date => csr_rec.pre_effective_date
2144 ,p_time_period_id => csr_rec.time_period_id
2145 ,p_record_count => l_record_count);
2146 end if;
2147 exit;
2148 end if;
2149
2150
2151 /*****************************************************************
2152 ** This returns all the Child Actions for a given master
2153 ** assignment action. There will not be any issue in this case if
2154 ** there are multiple runs for a pre payment as we calling it
2155 ** for the master run action.
2156 *****************************************************************/
2157 FOR csr_child_rec IN csr_child_actions(
2158 csr_rec.master_assignment_action_id,
2159 csr_rec.pay_payroll_action_id,
2160 csr_rec.assignment_id,
2161 csr_rec.effective_date)
2162
2163 LOOP
2164 -- create additional archive assignment actions and interlocks
2165 SELECT pay_assignment_actions_s.NEXTVAL
2166 INTO l_actid
2167 FROM dual;
2168
2169 hr_utility.set_location('csr_child_rec.run_type = ' ||
2170 csr_child_rec.run_type,30);
2171 hr_utility.set_location('csr_rec.master_assignment_action_id = ' ||
2172 csr_rec.master_assignment_action_id,30);
2173
2174 hr_nonrun_asact.insact(
2175 lockingactid => l_actid
2176 ,assignid => csr_rec.assignment_id
2177 ,pactid => g_archive_pact
2178 ,chunk => l_chunk_number
2179 ,greid => NULL
2180 ,prepayid => NULL
2181 ,status => 'C'
2182 ,source_act => p_assactid);
2183
2184 IF csr_child_rec.run_type = 'S' THEN
2185 hr_utility.set_location('creating lock3 ' || l_actid || ' to ' ||
2186 csr_child_rec.child_assignment_action_id,30);
2187
2188 hr_nonrun_asact.insint(
2189 lockingactid => l_actid
2190 ,lockedactid => csr_child_rec.child_assignment_action_id);
2191
2192 l_action_context_id := l_actid;
2193
2194 IF l_record_count = 0 THEN
2195 /* pay_gb_payslip_archive.archive_employee_details
2196 p_assactid => p_assactid
2197 ,p_assignment_id => assignment_id
2198 ,p_curr_pymt_ass_act_id => prepayment assignment_action_id
2199 ,p_effective_date => payroll effective_date
2200 ,p_date_earned => payroll date_earned
2201 ,p_curr_pymt_eff_date => prepayment effective_date
2202 ,p_time_period_id => payroll time_period_id
2203 ,p_record_count => l_record_count);
2204 */
2205 pay_gb_payslip_archive.archive_employee_details (
2206 p_assactid => l_action_context_id
2207 ,p_assignment_id => csr_rec.assignment_id
2208 ,p_curr_pymt_ass_act_id => csr_rec.pre_assignment_action_id
2209 ,p_effective_date => csr_rec.effective_date
2210 ,p_date_earned => csr_rec.date_earned
2211 ,p_curr_pymt_eff_date => csr_rec.pre_effective_date
2212 ,p_time_period_id => csr_rec.time_period_id
2213 ,p_record_count => l_record_count);
2214
2215 pay_gb_payslip_archive.archive_gb_employee_details (
2216 p_assactid => l_action_context_id
2217 ,p_assignment_id => csr_rec.assignment_id
2218 ,p_curr_pymt_ass_act_id => csr_child_rec.child_assignment_action_id
2219 ,p_effective_date => csr_rec.effective_date);
2220
2221 END IF;
2222
2223 pay_gb_payslip_archive.get_element_info (
2224 p_action_context_id => l_action_context_id
2225 , p_assignment_id => csr_rec.assignment_id
2226 , p_child_assignment_action => csr_child_rec.child_assignment_action_id
2227 , p_effective_date => csr_rec.effective_date
2228 , p_record_count => l_record_count
2229 , p_run_method => 'S');
2230
2231 END IF;
2232
2233 IF csr_child_rec.run_type = 'NP' THEN
2234 l_child_count := 0;
2235 FOR csr_np_rec IN csr_np_children(
2236 csr_rec.master_assignment_action_id,
2237 csr_rec.pay_payroll_action_id,
2238 csr_rec.assignment_id,
2239 csr_rec.effective_date)
2240 LOOP
2241 hr_utility.set_location('creating lock4 ' || l_actid || ' to ' ||
2242 csr_np_rec.np_assignment_action_id,30);
2243
2244 hr_nonrun_asact.insint(
2245 lockingactid => l_actid
2246 ,lockedactid => csr_np_rec.np_assignment_action_id);
2247
2248 IF l_child_count = 0 AND l_record_count = 0 THEN
2249 pay_gb_payslip_archive.archive_gb_employee_details (
2250 p_assactid => l_action_context_id
2251 ,p_assignment_id => csr_rec.assignment_id
2252 ,p_curr_pymt_ass_act_id => csr_np_rec.np_assignment_action_id
2253 ,p_effective_date => csr_rec.effective_date);
2254 END IF;
2255
2256 pay_gb_payslip_archive.get_element_info (
2257 p_action_context_id => l_action_context_id
2258 ,p_assignment_id => csr_rec.assignment_id
2259 ,p_child_assignment_action => csr_np_rec.np_assignment_action_id
2260 ,p_effective_date => csr_rec.effective_date
2261 ,p_record_count => l_record_count
2262 ,p_run_method => csr_np_rec.run_method);
2263
2264 l_child_count := l_child_count + 1;
2265
2266 END LOOP;
2267 END IF;
2268
2269 -- Both User and Statutory Balances are archived for all Separate
2270 -- Payment assignment actions and the last (i.e. highest action_sequence)
2271 -- Process Separately assignment action (EMEA BALANCES) archive
2272 -- user balances
2273 hr_utility.set_location('Archive User Balances - Starting',60);
2274 hr_utility.set_location('g_max_user_balance_index = '||
2275 g_max_user_balance_index,60);
2276
2277 FOR l_index IN 1 .. g_max_user_balance_index
2278 LOOP
2279 pay_gb_payslip_archive.process_balance (
2280 p_action_context_id => l_action_context_id
2281 , p_assignment_id => csr_rec.assignment_id
2282 , p_source_id => csr_child_rec.child_assignment_action_id
2283 , p_effective_date => csr_rec.effective_date
2284 , p_balance => g_user_balance_table(l_index).balance_name
2285 , p_dimension => g_user_balance_table(l_index).database_item_suffix
2286 , p_defined_bal_id => g_user_balance_table(l_index).defined_balance_id
2287 , p_ni_type => g_user_balance_table(l_index).ni_type_ind
2288 , p_record_count => l_record_count);
2289
2290 END LOOP;
2291
2292 hr_utility.set_location('Archive User Balances - Complete',60);
2293
2294 -- archive statutory balances
2295 hr_utility.set_location('Archive Statutory Balances - Starting',70);
2296 hr_utility.set_location('g_max_statutory_balance_index = '||
2297 g_max_statutory_balance_index,70);
2298
2299 FOR l_index IN 1 .. g_max_statutory_balance_index
2300 LOOP
2301 hr_utility.set_location('l_index = ' || l_index,70);
2302 pay_gb_payslip_archive.process_balance (
2303 p_action_context_id => l_action_context_id
2304 , p_assignment_id => csr_rec.assignment_id
2305 , p_source_id => csr_child_rec.child_assignment_action_id
2306 , p_effective_date => csr_rec.effective_date
2307 , p_balance => g_statutory_balance_table(l_index).balance_name
2308 , p_dimension => g_statutory_balance_table(l_index).database_item_suffix
2309 , p_defined_bal_id => g_statutory_balance_table(l_index).defined_balance_id
2310 , p_ni_type => g_statutory_balance_table(l_index).ni_type_ind
2311 , p_record_count => l_record_count);
2312 END LOOP;
2313
2314 hr_utility.set_location('Archive Statutory Balances - Complete',70);
2315
2316 END LOOP; -- child assignment actions
2317
2318 l_record_count := l_record_count + 1;
2319
2320 END LOOP;
2321 close csr_assignment_actions;
2322
2323 hr_utility.set_location('Leaving '|| l_proc,80);
2324
2325 END archive_code;
2326
2327 --Added for bug fix 5209228
2328 PROCEDURE ARCHIVE_DEINIT(p_payroll_action_id IN NUMBER) IS
2329
2330
2331 l_proc CONSTANT VARCHAR2(50):= g_package||'archive_deinit';
2332
2333 l_archived NUMBER(1);
2334 l_ovn NUMBER(15);
2335 l_request_id NUMBER;
2336 l_action_info_id NUMBER(15);
2337 l_business_group_id NUMBER;
2338
2339
2340 CURSOR csr_payroll_info(p_payroll_id NUMBER,
2341 p_consolidation_id NUMBER,
2342 p_start_date DATE,
2343 p_end_date DATE) IS
2344 SELECT pact.payroll_action_id payroll_action_id,
2345 pact.effective_date effective_date,
2346 pact.date_earned date_earned,
2347 pact.payroll_id,
2348 org.org_information1 employers_ref_no,
2349 org.org_information2 tax_office_name,
2350 org.org_information3 employer_name,
2351 org.org_information4 employer_address,
2352 org.org_information8 tax_office_phone_no,
2353 ppf.payroll_name payroll_name,
2354 ppf.period_type period_type,
2355 pact.pay_advice_message payroll_message
2356 FROM pay_payrolls_f ppf,
2357 pay_payroll_actions pact,
2358 hr_soft_coding_keyflex flex,
2359 hr_organization_information org
2360 WHERE ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
2361 AND org.org_information_context = 'Tax Details References'
2362 AND org.org_information1 = flex.segment1
2363 AND ppf.business_group_id = org.organization_id
2364 AND pact.payroll_id = ppf.payroll_id
2365 AND pact.effective_date BETWEEN
2366 ppf.effective_start_date AND ppf.effective_end_date
2367 AND pact.payroll_id = NVL(p_payroll_id,pact.payroll_id)
2368 AND pact.consolidation_set_id = p_consolidation_id -- 4071160
2369 AND pact.effective_date BETWEEN
2370 p_start_date AND p_end_date
2371 AND (pact.action_type = 'P' OR
2372 pact.action_type = 'U')
2373 AND pact.action_status = 'C'
2374 AND NOT EXISTS (SELECT NULL
2375 FROM pay_action_information pai
2376 WHERE pai.action_context_id = pact.payroll_action_id
2377 AND pai.action_context_type = 'PA'
2378 AND pai.action_information_category = 'EMEA PAYROLL INFO');
2379
2380 CURSOR csr_payroll_mesg (p_payroll_id NUMBER,
2381 p_start_date DATE,
2382 p_end_date DATE) IS
2383 SELECT pact.payroll_action_id payroll_action_id,
2384 pact.effective_date effective_date,
2385 pact.date_earned date_earned,
2386 pact.pay_advice_message payroll_message
2387 FROM pay_payrolls_f ppf,
2388 pay_payroll_actions pact
2389 WHERE pact.payroll_id = ppf.payroll_id
2390 AND pact.effective_date BETWEEN
2391 ppf.effective_start_date AND ppf.effective_end_date
2392 AND pact.payroll_id = p_payroll_id
2393 AND pact.effective_date BETWEEN
2394 p_start_date AND p_end_date
2395 AND (pact.action_type = 'R' OR
2396 pact.action_type = 'Q')
2397 AND pact.action_status = 'C'
2398 AND NOT EXISTS (SELECT NULL
2399 FROM pay_action_information pai
2400 WHERE pai.action_context_id = pact.payroll_action_id
2401 AND pai.action_context_type = 'PA'
2402 AND pai.action_information_category = 'EMPLOYEE OTHER INFORMATION');
2403
2404 --
2405 l_assignment_set_id NUMBER;
2406 l_bg_id NUMBER;
2407 l_canonical_end_date DATE;
2408 l_canonical_start_date DATE;
2409 l_consolidation_set NUMBER;
2410 l_end_date VARCHAR2(30);
2411 l_legislation_code VARCHAR2(30) := 'GB';
2412 l_payroll_id NUMBER;
2413 l_start_date VARCHAR2(30);
2414 l_tax_period_no VARCHAR2(30);
2415 l_error varchar2(1) ;
2416
2417
2418 BEGIN
2419
2420 hr_utility.set_location('Entering ' || l_proc,10);
2421
2422
2423 -- To avoid re-archiving while Retry
2424 delete from pay_action_information pai
2425 where pai.action_context_id = p_payroll_action_id
2426 and pai.action_context_type = 'PA'
2427 and pai.action_information_category in ('EMPLOYEE OTHER INFORMATION')
2428 and pai.action_information2 = 'MESG';
2429
2430 delete from pay_action_information pai
2431 where pai.action_context_id = p_payroll_action_id
2432 and pai.action_context_type = 'PA'
2433 and pai.action_information_category in ('EMEA PAYROLL INFO');
2434
2435
2436 pay_gb_payslip_archive.get_parameters (
2437 p_payroll_action_id => p_payroll_action_id
2438 , p_token_name => 'PAYROLL'
2439 , p_token_value => l_payroll_id);
2440
2441 pay_gb_payslip_archive.get_parameters (
2442 p_payroll_action_id => p_payroll_action_id
2443 , p_token_name => 'CONSOLIDATION'
2444 , p_token_value => l_consolidation_set);
2445
2446 pay_gb_payslip_archive.get_parameters (
2447 p_payroll_action_id => p_payroll_action_id
2448 , p_token_name => 'ASSIGNMENT_SET'
2449 , p_token_value => l_assignment_set_id);
2450
2451 pay_gb_payslip_archive.get_parameters (
2452 p_payroll_action_id => p_payroll_action_id
2453 , p_token_name => 'START_DATE'
2454 , p_token_value => l_start_date);
2455
2456 pay_gb_payslip_archive.get_parameters (
2457 p_payroll_action_id => p_payroll_action_id
2458 , p_token_name => 'END_DATE'
2459 , p_token_value => l_end_date);
2460
2461 pay_gb_payslip_archive.get_parameters (
2462 p_payroll_action_id => p_payroll_action_id
2463 , p_token_name => 'BG_ID'
2464 , p_token_value => l_bg_id);
2465
2466
2467 hr_utility.set_location('Step ' || l_proc,20);
2468 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
2469 hr_utility.set_location('l_start_date = ' || l_start_date,20);
2470 hr_utility.set_location('l_end_date = ' || l_end_date,20);
2471
2472 l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
2473 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
2474
2475 -- archive EMEA PAYROLL INFO for each prepayment run identified
2476
2477 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
2478 hr_utility.set_location('l_consolidation_set = ' || l_consolidation_set,20);
2479 hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
2480 hr_utility.set_location('l_canonical_end_date = ' || l_canonical_end_date,20);
2481
2482
2483 --Archiving contexts EMPLOYEE OTHER INFORMATION for MESG and
2484 --ADDRESS DETAILS for Employer Address
2485
2486 pay_emp_action_arch.arch_pay_action_level_data (
2487 p_payroll_action_id => p_payroll_action_id
2488 , p_effective_date => l_canonical_end_date);
2489
2490
2491 --Archiving context EMEA PAYROLL INFO
2492 FOR rec_payroll_info in csr_payroll_info(l_payroll_id,
2493 l_consolidation_set,
2494 l_canonical_start_date,
2495 l_canonical_end_date)
2496
2497 LOOP
2498
2499 pay_balance_pkg.set_context('PAYROLL_ACTION_ID'
2500 , rec_payroll_info.payroll_action_id);
2501
2502
2503 hr_utility.set_location('rec_payroll_info.payroll_action_id = ' || rec_payroll_info.payroll_action_id,30);
2504 hr_utility.set_location('rec_payroll_info.tax_office_name = ' || rec_payroll_info.tax_office_name,30);
2505 hr_utility.set_location('rec_payroll_info.tax_office_phone_no = ' || rec_payroll_info.tax_office_phone_no,30);
2506 hr_utility.set_location('rec_payroll_info.employers_ref_no = ' || rec_payroll_info.employers_ref_no,30);
2507
2508 hr_utility.set_location('Archiving EMEA PAYROLL INFO',30);
2509
2510 pay_action_information_api.create_action_information (
2511 p_action_information_id => l_action_info_id
2512 , p_action_context_id => p_payroll_action_id
2513 , p_action_context_type => 'PA'
2514 , p_object_version_number => l_ovn
2515 , p_effective_date => rec_payroll_info.effective_date
2516 , p_source_id => NULL
2517 , p_source_text => NULL
2518 , p_action_information_category => 'EMEA PAYROLL INFO'
2519 , p_action_information1 => rec_payroll_info.payroll_action_id
2520 , p_action_information2 => rec_payroll_info.payroll_id
2521 , p_action_information3 => NULL
2522 , p_action_information4 => rec_payroll_info.tax_office_name
2523 , p_action_information5 => rec_payroll_info.tax_office_phone_no
2524 , p_action_information6 => rec_payroll_info.employers_ref_no);
2525
2526 END LOOP;
2527
2528 -- The Payroll level message is archived in the generic archive structure
2529 -- EMPLOYEE OTHER INFORMATION
2530
2531 --Archiving context EMPLOYEE OTHER INFORMATION
2532 FOR rec_payroll_msg in csr_payroll_mesg(l_payroll_id,
2533 l_canonical_start_date,
2534 l_canonical_end_date)
2535
2536 LOOP
2537
2538 IF rec_payroll_msg.payroll_message IS NOT NULL
2539
2540 THEN
2541
2542 pay_action_information_api.create_action_information (
2543 p_action_information_id => l_action_info_id
2544 , p_action_context_id => p_payroll_action_id
2545 , p_action_context_type => 'PA'
2546 , p_object_version_number => l_ovn
2547 , p_effective_date => rec_payroll_msg.effective_date
2548 , p_source_id => NULL
2549 , p_source_text => NULL
2550 , p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
2551 , p_action_information1 => rec_payroll_msg.payroll_action_id
2552 , p_action_information2 => 'MESG'
2553 , p_action_information3 => NULL
2554 , p_action_information4 => NULL
2555 , p_action_information5 => NULL
2556 , p_action_information6 => rec_payroll_msg.payroll_message);
2557
2558 END IF;
2559
2560 END LOOP;
2561
2562 hr_utility.set_location('Leaving ' || l_proc,40);
2563
2564
2565 END ARCHIVE_DEINIT;
2566
2567
2568 END;