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