[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_LEGISLATIVE_ARCHIVE
Source
1 PACKAGE BODY pay_ie_legislative_archive AS
2 /* $Header: pyieparc.pkb 120.15.12010000.4 2008/08/22 11:11:18 rsahai ship $ */
3
4 TYPE balance_rec IS RECORD (
5 balance_type_id NUMBER,
6 balance_dimension_id NUMBER,
7 defined_balance_id NUMBER,
8 balance_narrative VARCHAR2(150),
9 balance_name VARCHAR2(150),
10 database_item_suffix VARCHAR2(30),
11 legislation_code VARCHAR2(20));
12
13 TYPE element_rec IS RECORD (
14 element_type_id NUMBER,
15 input_value_id NUMBER,
16 formula_id NUMBER,
17 element_narrative VARCHAR2(150),
18 -- Added for bug 5387406
19 uom VARCHAR2(30));
20
21
22
23 TYPE balance_table IS TABLE OF balance_rec INDEX BY BINARY_INTEGER;
24 TYPE element_table IS TABLE OF element_rec INDEX BY BINARY_INTEGER;
25
26 g_user_balance_table balance_table;
27 g_element_table element_table;
28 g_statutory_balance_table balance_table;
29
30 g_balance_archive_index NUMBER := 0;
31 g_element_archive_index NUMBER := 0;
32 g_max_element_index NUMBER := 0;
33 g_max_user_balance_index NUMBER := 0;
34 g_max_statutory_balance_index NUMBER := 0;
35
36 g_paye_details_element_id NUMBER;
37 g_paye_previous_pay_archived VARCHAR2(1);
38 g_paye_previous_pay_id NUMBER;
39 g_paye_previous_tax_archived VARCHAR2(1);
40 g_paye_previous_tax_id NUMBER;
41
42 g_tax_basis_id NUMBER;
43 g_prsi_cat_id NUMBER;
44 g_prsi_subcat_id NUMBER;
45 g_ins_weeks_id NUMBER;
46 g_tax_credit_id NUMBER;
47 g_std_cut_off_id NUMBER;
48 g_tax_unit_id NUMBER;
49 g_prsi_week_id NUMBER;
50
51 g_package CONSTANT VARCHAR2(30) := 'pay_ie_legislative_archive.';
52
53 g_balance_context CONSTANT VARCHAR2(30) := 'IE_BALANCES';
54 g_element_context CONSTANT VARCHAR2(30) := 'IE_ELEMENTS';
55
56 g_archive_pact NUMBER;
57 g_archive_effective_date DATE;
58
59 PROCEDURE get_parameters(p_payroll_action_id IN NUMBER,
60 p_token_name IN VARCHAR2,
61 p_token_value OUT NOCOPY VARCHAR2) IS
62
63 CURSOR csr_parameter_info(p_pact_id NUMBER,
64 p_token CHAR) IS
65 SELECT SUBSTR(legislative_parameters,
66 INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
67 INSTR(legislative_parameters,' ',
68 INSTR(legislative_parameters,p_token))
69 - (INSTR(legislative_parameters,p_token)+LENGTH(p_token))),
70 business_group_id
71 FROM pay_payroll_actions
72 WHERE payroll_action_id = p_pact_id;
73
74 l_business_group_id VARCHAR2(20);
75 l_token_value VARCHAR2(50);
76
77 l_proc VARCHAR2(50) := g_package || 'get_parameters';
78
79 BEGIN
80
81 hr_utility.set_location('Entering ' || l_proc,10);
82
83 hr_utility.set_location('Step ' || l_proc,20);
84 hr_utility.set_location('p_token_name = ' || p_token_name,20);
85
86 OPEN csr_parameter_info(p_payroll_action_id,
87 p_token_name);
88
89 FETCH csr_parameter_info INTO l_token_value,
90 l_business_group_id;
91
92 CLOSE csr_parameter_info;
93
94 IF p_token_name = 'BG_ID'
95
96 THEN
97
98 p_token_value := l_business_group_id;
99
100 ELSE
101
102 p_token_value := l_token_value;
103
104 END IF;
105
106 hr_utility.set_location('l_token_value = ' || l_token_value,20);
107 hr_utility.set_location('Leaving ' || l_proc,30);
108
109 END get_parameters;
110
111 PROCEDURE get_eit_definitions(p_pactid IN NUMBER,
112 p_business_group_id IN NUMBER,
113 p_payroll_pact IN NUMBER,
114 p_effective_date IN DATE,
115 p_eit_context IN VARCHAR2,
116 p_archive IN VARCHAR2) IS
117
118 CURSOR csr_eit_values(p_bg_id NUMBER,
119 p_context CHAR) IS
120 SELECT org.org_information1,
121 org.org_information2,
122 org.org_information3,
123 org.org_information4,
124 org.org_information5,
125 org.org_information6
126 FROM hr_organization_information_v org
127 WHERE org.org_information_context = p_context
128 AND org.organization_id = p_bg_id;
129
130 CURSOR csr_balance_name(p_balance_type_id NUMBER,
131 p_balance_dimension_id NUMBER) IS
132 SELECT pbt.balance_name,
133 pbd.database_item_suffix,
134 pbt.legislation_code,
135 pdb.defined_balance_id
136 FROM pay_balance_types pbt,
137 pay_balance_dimensions pbd,
138 pay_defined_balances pdb
139 WHERE pdb.balance_type_id = pbt.balance_type_id
140 AND pdb.balance_dimension_id = pbd.balance_dimension_id
141 AND pbt.balance_type_id = p_balance_type_id
142 AND pbd.balance_dimension_id = p_balance_dimension_id;
143
144
145 CURSOR csr_element_type(p_element_type_id NUMBER,
146 p_effective_date DATE) IS
147 SELECT pet.formula_id
148 FROM pay_element_types_f pet,
149 ff_formulas_f fff
150 WHERE pet.element_type_id = p_element_type_id
151 AND pet.formula_id = fff.formula_id
152 AND fff.formula_name = 'ONCE_EACH_PERIOD'
153 AND p_effective_date BETWEEN
154 fff.effective_start_date AND fff.effective_end_date
155 AND p_effective_date BETWEEN
156 pet.effective_start_date AND pet.effective_end_date;
157
158 CURSOR csr_input_value_uom(p_input_value_id NUMBER,
159 p_effective_date DATE) IS
160 SELECT piv.uom
161 FROM pay_input_values_f piv
162 WHERE piv.input_value_id = p_input_value_id
163 AND p_effective_date BETWEEN
164 piv.effective_start_date AND piv.effective_end_date;
165
166 l_action_info_id NUMBER(15);
167 l_formula_id NUMBER(9);
168 l_index NUMBER := 1;
169 l_ovn NUMBER(15);
170 l_uom VARCHAR(30);
171
172 l_proc VARCHAR2(50) := g_package || 'get_eit_definitions';
173
174 BEGIN
175
176 hr_utility.set_location('Entering ' || l_proc,10);
177
178 hr_utility.set_location('Step ' || l_proc,20);
179 hr_utility.set_location('p_eit_context = ' || p_eit_context,20);
180
181 FOR csr_eit_rec IN csr_eit_values(p_business_group_id,
182 p_eit_context)
183
184 LOOP
185
186 hr_utility.set_location('Step ' || l_proc,30);
187
188 hr_utility.set_location('org_information1 = ' || csr_eit_rec.org_information1,30);
189 hr_utility.set_location('org_information2 = ' || csr_eit_rec.org_information2,30);
190 hr_utility.set_location('org_information3 = ' || csr_eit_rec.org_information3,30);
191 hr_utility.set_location('org_information4 = ' || csr_eit_rec.org_information4,30);
192 hr_utility.set_location('org_information5 = ' || csr_eit_rec.org_information5,30);
193 hr_utility.set_location('org_information6 = ' || csr_eit_rec.org_information6,30);
194
195 IF p_eit_context = g_balance_context
196
197 THEN
198
199 g_user_balance_table(l_index).balance_type_id := csr_eit_rec.org_information2;
200
201 g_user_balance_table(l_index).balance_dimension_id := csr_eit_rec.org_information3;
202
203 g_user_balance_table(l_index).balance_narrative := csr_eit_rec.org_information4;
204
205 OPEN csr_balance_name(g_user_balance_table(l_index).balance_type_id,
206 g_user_balance_table(l_index).balance_dimension_id);
207
208 FETCH csr_balance_name
209 INTO g_user_balance_table(l_index).balance_name,
210 g_user_balance_table(l_index).database_item_suffix,
211 g_user_balance_table(l_index).legislation_code,
212 g_user_balance_table(l_index).defined_balance_id;
213
214 CLOSE csr_balance_name;
215
216 hr_utility.set_location('Arch EMEA BALANCE DEFINITION',99);
217
218 IF p_archive = 'Y'
219
220 THEN
221
222 pay_action_information_api.create_action_information (
223 p_action_information_id => l_action_info_id
224 , p_action_context_id => p_pactid
225 , p_action_context_type => 'PA'
226 , p_object_version_number => l_ovn
227 , p_effective_date => p_effective_date
228 , p_source_id => NULL
229 , p_source_text => NULL
230 , p_action_information_category => 'EMEA BALANCE DEFINITION'
231 , p_action_information1 => p_payroll_pact
232 , p_action_information2 => g_user_balance_table(l_index).defined_balance_id
233 , p_action_information3 => NULL
234 , p_action_information4 => csr_eit_rec.org_information4);
235
236 END IF;
237
238 g_max_user_balance_index := g_max_user_balance_index + 1;
239
240 END IF;
241
242 IF p_eit_context = g_element_context
243
244 THEN
245
246 g_element_table(l_index).element_type_id := csr_eit_rec.org_information1;
247
248 g_element_table(l_index).input_value_id := csr_eit_rec.org_information2;
249
250 g_element_table(l_index).element_narrative := csr_eit_rec.org_information3;
251
252 OPEN csr_input_value_uom(csr_eit_rec.org_information2,
253 p_effective_date);
254
255 FETCH csr_input_value_uom INTO l_uom;
256
257 CLOSE csr_input_value_uom;
258
259 -- added for bug 5387406
260 g_element_table(l_index).uom := l_uom;
261
262
263 IF p_archive = 'Y'
264
265 THEN
266
267 hr_utility.set_location('Arch EMEA ELEMENT DEFINITION',99);
268
269 pay_action_information_api.create_action_information (
270 p_action_information_id => l_action_info_id
271 , p_action_context_id => p_pactid
272 , p_action_context_type => 'PA'
273 , p_object_version_number => l_ovn
274 , p_effective_date => p_effective_date
275 , p_source_id => NULL
276 , p_source_text => NULL
277 , p_action_information_category => 'EMEA ELEMENT DEFINITION'
278 , p_action_information1 => p_payroll_pact
279 , p_action_information2 => csr_eit_rec.org_information1
280 , p_action_information3 => csr_eit_rec.org_information2
281 , p_action_information4 => csr_eit_rec.org_information3
282 , p_action_information5 => 'F'
283 , p_action_information6 => l_uom);
284
285 END IF;
286
287 END IF;
288
289 l_index := l_index + 1;
290
291 hr_utility.set_location('l_index = ' || l_index,99);
292
293 END LOOP;
294
295 g_max_element_index := l_index;
296
297 IF p_eit_context = g_balance_context
298
299 THEN
300
301 g_balance_archive_index := l_index - 1;
302
303 ELSE
304
305 g_element_archive_index := l_index - 1;
306
307 END IF;
308
309 hr_utility.set_location('g_balance_archive_index = ' || g_balance_archive_index,99);
310
311 hr_utility.set_location('Leaving ' || l_proc,30);
312
313 END get_eit_definitions;
314
315
316
317
318 PROCEDURE setup_element_definitions (p_pactid IN NUMBER,
319 p_payroll_pact IN NUMBER,
320 p_business_group_id IN NUMBER,
321 p_effective_date IN DATE)
322 IS
323
324 l_action_info_id NUMBER(15);
325 l_ovn NUMBER(15);
326 l_payment_type VARCHAR2(1);
327 l_payment_type_bik VARCHAR2(1);
328
329
330 -- Bug No: 2338289
331 -- Deduction Net Tax (and value) not to be shown, it should be
332 -- PAYE at Higher Rate and PAYE at Standard Rate
333 -- csr_element_name modified
334
335 CURSOR csr_element_name (p_business_group_id NUMBER,
336 p_effective_date DATE) IS
337 SELECT pet.element_type_id,
338 piv.input_value_id,
339 NVL(pet.reporting_name,pet.element_name) element_name,
340 pec.classification_name,
341 piv.uom
342 FROM pay_element_classifications pec,
343 pay_input_values_f piv,
344 pay_element_types_f pet
345 WHERE pec.classification_name IN
346 ('Court Orders',
347 'Voluntary Deductions',
348 'Pre-Tax Deductions',
349 'Pre PRSI Deduction', -- Bug 2672763
350 'Pre Tax and Pre PRSI Deduction', -- Bug 2672763
351 'PAYE',
352 'PRSI',
353 'Earnings',
354 'Direct Payments',
355 'IE Earnings Non PRSIable', -- Bug 2943335
356 'IE Earnings Non Taxable and Non PRSIable', -- Bug 2943335
357 'IE Social Benefits Clearup', -- Bug 2943335
358 'IE Benefit In Kind Arrearage',--Bug 2367175
359 'IE Benefit In Kind Arrearage Recovery',
360 'Advance Earnings') --Bug 3720315
361 AND pet.element_name <> 'IE PRSI'
362 and pet.element_name not in ('IE Reduced Std Rate Cut Off' , 'IE Reduced Tax Credit')
363 AND pec.business_group_id IS NULL
364 AND pec.legislation_code = 'IE'
365 AND pet.classification_id = pec.classification_id
366 AND NVL(pet.business_group_id,p_business_group_id) = p_business_group_id
367 AND piv.element_type_id = pet.element_type_id
368 AND (
369 (piv.name ='Pay Value' )
370 OR (pet.element_name in ('IE BIK Arrearage Details','IE BIK Arrearage Recovery Details') and piv.name in ('BIK Arrearage','BIK Arrearage Recovered'))
371 OR (pet.element_name in ('IE PAYE at higher rate','IE PAYE at standard rate') AND piv.name ='Value' ))
372 AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
373 AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
374 Union -- Added for bug Fix 2367175
375 SELECT pet.element_type_id,piv.input_value_id,
376 NVL(pet.reporting_name,pet.element_name) element_name,
377 'Information',
378 piv.uom
379 FROM pay_element_classifications pec,
380 pay_input_values_f piv,
381 pay_element_types_f pet
382 WHERE pec.classification_name IN ( 'Information')
383 AND pec.business_group_id IS NULL
384 AND pec.legislation_code = 'IE'
385 AND pet.classification_id = pec.classification_id
386 AND NVL(pet.business_group_id,p_business_group_id) = p_business_group_id
387 AND piv.element_type_id = pet.element_type_id
388 AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
389 AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
390 -- Changed to improve the performance 4771780
391 -- moving them to decode and avoiding OR condition removes merge cartesian join
392 AND piv.name = decode(pet.element_name,
393 'IE BIK Accommodation Details','Taxable Value for Run',
394 'IE BIK Asset Type Details','Taxable Value for Run',
395 'IE BIK Company Vehicle Details','Taxable Value for Run',
396 'IE BIK Preferential Loan Details','Taxable Value for Run',
397 'IE BIK Other Reportable Item Details','Taxable Value for Run',
398 'IE BIK Non Recurring Reportable Items','Benefit Value'
399 );
400
401 l_proc VARCHAR2(60) := g_package || 'setup_element_definitions';
402
403 BEGIN
404
405 hr_utility.set_location('Entering ' || l_proc,10);
406
407 hr_utility.set_location('p_payroll_pact = ' || p_payroll_pact,10);
408
409
410 FOR csr_element_rec IN csr_element_name(p_business_group_id,
411 p_effective_date)
412 LOOP
413
414 hr_utility.set_location('csr_element_rec.element_type_id = ' || csr_element_rec.element_type_id,20);
415 hr_utility.set_location('csr_element_rec.element_name = ' || csr_element_rec.element_name,20);
416
417 IF csr_element_rec.classification_name IN
418 ('Earnings', 'IE Earnings Non PRSIable', 'IE Earnings Non Taxable and Non PRSIable')
419 -- Bug 2943335 Added 'IE Earnings Non PRSIable' and'IE Earnings Non Taxable and Non PRSIable'
420 THEN
421
422 l_payment_type := 'E';
423 l_payment_type_bik :='P';
424
425 ELSIF csr_element_rec.classification_name = 'Direct Payments'
426
427 THEN
428
429 l_payment_type := 'P';
430 l_payment_type_bik :='P';
431
432 ELSIF csr_element_rec.classification_name = 'Information'
433
434 THEN
435
436 l_payment_type_bik :='C';
437 /*Bug No. 3720315*/
438 ELSIF csr_element_rec.classification_name = 'Advance Earnings'
439 THEN
440 l_payment_type := 'P';
441 l_payment_type_bik :='P';
442 /*End of Bug No. 3720315*/
443
444 ELSE
445
446 l_payment_type := 'D';
447 l_payment_type_bik :='P';
448
449 END IF;
450
451
452
453 hr_utility.set_location('Arch EMEA ELEMENT DEFINITION',99);
454
455 IF l_payment_type_bik='P'
456 THEN
457 pay_action_information_api.create_action_information (
458 p_action_information_id => l_action_info_id
459 , p_action_context_id => p_pactid
460 , p_action_context_type => 'PA'
461 , p_object_version_number => l_ovn
462 , p_effective_date => p_effective_date
463 , p_source_id => NULL
464 , p_source_text => NULL
465 , p_action_information_category => 'EMEA ELEMENT DEFINITION'
466 , p_action_information1 => p_payroll_pact
467 , p_action_information2 => csr_element_rec.element_type_id
468 , p_action_information3 => csr_element_rec.input_value_id
469 , p_action_information4 => csr_element_rec.element_name
470 , p_action_information5 => l_payment_type
471 , p_action_information6 => csr_element_rec.uom);
472
473 END IF;
474 --Added for bug fix 2367175
475 --To Display the taxable value for all benefits in kind, in both the payments and
476 --deduction section,passing a value of 'E' and 'D' to payment type
477
478 IF l_payment_type_bik='C'
479 THEN
480 pay_action_information_api.create_action_information (
481 p_action_information_id => l_action_info_id
482 , p_action_context_id => p_pactid
483 , p_action_context_type => 'PA'
484 , p_object_version_number => l_ovn
485 , p_effective_date => p_effective_date
486 , p_source_id => NULL
487 , p_source_text => NULL
488 , p_action_information_category => 'EMEA ELEMENT DEFINITION'
489 , p_action_information1 => p_payroll_pact
490 , p_action_information2 => csr_element_rec.element_type_id
491 , p_action_information3 => csr_element_rec.input_value_id
492 , p_action_information4 => csr_element_rec.element_name
493 , p_action_information5 => 'E'
494 , p_action_information6 => csr_element_rec.uom);
495
496 pay_action_information_api.create_action_information (
497 p_action_information_id => l_action_info_id
498 , p_action_context_id => p_pactid
499 , p_action_context_type => 'PA'
500 , p_object_version_number => l_ovn
501 , p_effective_date => p_effective_date
502 , p_source_id => NULL
503 , p_source_text => NULL
504 , p_action_information_category => 'EMEA ELEMENT DEFINITION'
505 , p_action_information1 => p_payroll_pact
506 , p_action_information2 => csr_element_rec.element_type_id
507 , p_action_information3 => csr_element_rec.input_value_id
508 , p_action_information4 => csr_element_rec.element_name
509 , p_action_information5 => 'D'
510 , p_action_information6 => csr_element_rec.uom);
511
512 END IF;
513
514 END LOOP;
515
516 hr_utility.set_location('Leaving ' || l_proc,30);
517
518 END setup_element_definitions;
519
520 PROCEDURE setup_standard_balance_table
521 IS
522
523 TYPE balance_name_rec IS RECORD (
524 balance_name VARCHAR2(30));
525
526 TYPE balance_id_rec IS RECORD (
527 defined_balance_id NUMBER,
528 balance_name VARCHAR2(30), -- 4879850
529 dimension_name VARCHAR2(100)); --6633719
530
531 TYPE balance_name_tab IS TABLE OF balance_name_rec INDEX BY BINARY_INTEGER;
532 TYPE balance_id_tab IS TABLE OF balance_id_rec INDEX BY BINARY_INTEGER;
533
534 l_statutory_balance balance_name_tab;
535 l_statutory_bal_id balance_id_tab;
536
537 -- Bug 3221451 : Added the condition to check the Legislation Code and
538 -- Business Group Id
539
540 CURSOR csr_balance_dimension(p_balance IN CHAR,
541 p_dimension IN CHAR) IS
542 SELECT pdb.defined_balance_id
543 FROM pay_balance_types pbt,
544 pay_balance_dimensions pbd,
545 pay_defined_balances pdb
546 WHERE pdb.balance_type_id = pbt.balance_type_id
547 AND pdb.balance_dimension_id = pbd.balance_dimension_id
548 AND pbt.balance_name = p_balance
549 AND pbd.database_item_suffix = p_dimension
550 AND pbd.legislation_code = 'IE'
551 AND pbd.business_group_id is NULL
552 AND pbt.legislation_code = 'IE'
553 AND pbt.business_group_id is NULL
554 AND pdb.legislation_code = 'IE'
555 AND pdb.business_group_id is NULL;
556
557 l_archive_index NUMBER := 0;
558 l_dimension VARCHAR2(16) := '_ASG_YTD';
559 l_dimension_1 VARCHAR2(16) := '_PRSI_ASG_YTD';
560 --Changed to stripe the balances by employer Level 4369280
561 l_dimension_2 VARCHAR2(20) := '_PER_PAYE_REF_YTD';
562 l_found VARCHAR2(1);
563 l_max_stat_balance NUMBER := 14;
564
565 l_proc VARCHAR2(120) := g_package || 'setup_standard_balance_table';
566 l_index_id NUMBER := 0;
567
568 l_dimension_3 VARCHAR2(50) := '_PER_PAYE_REF_PPSN_YTD'; --6633719
569
570 BEGIN
571
572
573
574
575
576 hr_utility.set_location('Entering ' || l_proc,10);
577
578 hr_utility.set_location('Step ' || l_proc,20);
579
580 l_statutory_balance(1).balance_name := 'IE Total Deductions';
581 l_statutory_balance(2).balance_name := 'IE Taxable Pay';
582 l_statutory_balance(3).balance_name := 'IE PRSIable Pay';
583 l_statutory_balance(4).balance_name := 'IE Net Tax';
584 l_statutory_balance(5).balance_name := 'IE PRSI Employer';
585 l_statutory_balance(6).balance_name := 'IE PRSI Employee';
586 l_statutory_balance(7).balance_name := 'IE PRSI Insurable Weeks';
587 -- Bug 3436737 : Added new balances which needs to be archived for
588 -- severance payment.
589 l_statutory_balance(8).balance_name := 'IE PRSI K Employee Lump Sum';
590 l_statutory_balance(9).balance_name := 'IE PRSI M Employee Lump Sum';
591 l_statutory_balance(10).balance_name := 'IE PRSI K Employer Lump Sum';
592 l_statutory_balance(11).balance_name := 'IE PRSI M Employer Lump Sum';
593 l_statutory_balance(12).balance_name := 'IE PRSI K Term Insurable Weeks';
594 l_statutory_balance(13).balance_name := 'IE PRSI M Term Insurable Weeks';
595 l_statutory_balance(14).balance_name := 'IE Term Health Levy';
596
597 hr_utility.set_location('Step = ' || l_proc,30);
598
599 FOR l_index IN 1 .. l_max_stat_balance
600
601 LOOP
602
603 l_dimension := '_ASG_YTD';
604 hr_utility.set_location('l_index = ' || l_index,30);
605 hr_utility.set_location('balance_name = ' || l_statutory_balance(l_index).balance_name,30);
606 hr_utility.set_location('l_dimension = ' || l_dimension,30);
607
608
609 IF l_statutory_balance(l_index).balance_name = 'IE PRSI Insurable Weeks' then
610 l_dimension := l_dimension_1;
611 END IF;
612
613
614 /* Commented to archive _ASG_YTD value instead of _PRSI_ASG_YTD for IE PRSI Employee
615 IF l_statutory_balance(l_index).balance_name = 'IE PRSI Employee' THEN
616 l_dimension := l_dimension_1;
617 END IF;
618 */
619 l_index_id := l_index_id +1;
620 OPEN csr_balance_dimension(l_statutory_balance(l_index).balance_name,
621 l_dimension);
622
623 FETCH csr_balance_dimension
624 INTO l_statutory_bal_id(l_index_id).defined_balance_id;
625 l_statutory_bal_id(l_index_id).balance_name := l_statutory_balance(l_index).balance_name; --4879850
626 l_statutory_bal_id(l_index_id).dimension_name := l_dimension; --6633719
627
628
629 IF csr_balance_dimension%NOTFOUND
630
631 THEN
632
633
634 l_statutory_bal_id(l_index_id).defined_balance_id := 0;
635
636 END IF;
637
638 CLOSE csr_balance_dimension;
639
640 -- Bug No 2569918 Added for P35/P60 Reporting
641 -- Create entries for PER_YTD defined balances in the l_statutory_bal_id pl/sql table
642 --
643 l_index_id := l_index_id + 1;
644 OPEN csr_balance_dimension(l_statutory_balance(l_index).balance_name,
645 l_dimension_2);
646
647 FETCH csr_balance_dimension
648 INTO l_statutory_bal_id(l_index_id).defined_balance_id;
649 l_statutory_bal_id(l_index_id).balance_name := l_statutory_balance(l_index).balance_name; -- 4879850
650 l_statutory_bal_id(l_index_id).dimension_name := l_dimension_2; --6633719
651
652 IF csr_balance_dimension%NOTFOUND
653
654 THEN
655
656
657 l_statutory_bal_id(l_index_id).defined_balance_id := 0;
658
659 END IF;
660
661 CLOSE csr_balance_dimension;
662
663 hr_utility.set_location('defined_balance_id = ' || l_statutory_bal_id(l_index_id).defined_balance_id,30);
664
665 --6633719
666 l_index_id := l_index_id + 1;
667 OPEN csr_balance_dimension(l_statutory_balance(l_index).balance_name,
668 l_dimension_3);
669
670 FETCH csr_balance_dimension
671 INTO l_statutory_bal_id(l_index_id).defined_balance_id;
672 l_statutory_bal_id(l_index_id).balance_name := l_statutory_balance(l_index).balance_name;
673 l_statutory_bal_id(l_index_id).dimension_name := l_dimension_3; --6633719
674
675 IF csr_balance_dimension%NOTFOUND
676
677 THEN
678
679 l_statutory_bal_id(l_index_id).defined_balance_id := 0;
680
681 END IF;
682
683 CLOSE csr_balance_dimension;
684 --6633719
685
686 END LOOP;
687
688 hr_utility.set_location('Step = ' || l_proc,40);
689
690 hr_utility.set_location('l_max_stat_balance = ' || l_max_stat_balance,40);
691 hr_utility.set_location('g_max_user_balance_index = ' || g_max_user_balance_index,40);
692
693 FOR l_index IN 1 .. l_index_id
694
695 LOOP
696
697 l_found := 'N';
698
699 FOR l_eit_index IN 1 .. g_max_user_balance_index
700
701 LOOP
702
703 hr_utility.set_location('l_index = ' || l_index,40);
704 hr_utility.set_location('l_eit_index = ' || l_eit_index,40);
705 hr_utility.set_location('defined_balance_id = ' || l_statutory_bal_id(l_index).defined_balance_id,40);
706 hr_utility.set_location('l_found = ' || l_found,40);
707
708 IF l_statutory_bal_id(l_index).defined_balance_id = g_user_balance_table(l_eit_index).defined_balance_id
709
710 THEN
711
712 l_found := 'Y';
713
714 END IF;
715
716 END LOOP;
717
718 IF l_found = 'N'
719
720 THEN
721
722 l_archive_index := l_archive_index + 1;
723
724 hr_utility.set_location('l_archive_index = ' || l_archive_index,40);
725
726 g_statutory_balance_table(l_archive_index).defined_balance_id := l_statutory_bal_id(l_index).defined_balance_id;
727 g_statutory_balance_table(l_archive_index).balance_name := l_statutory_bal_id(l_index).balance_name; --4879850
728 g_statutory_balance_table(l_archive_index).database_item_suffix := l_statutory_bal_id(l_index).dimension_name; --6633719
729
730
731 END IF;
732
733 END LOOP;
734
735 g_max_statutory_balance_index := l_archive_index;
736
737 hr_utility.set_location('Step ' || l_proc,50);
738 hr_utility.set_location('l_archive_index = ' || l_archive_index,50);
739
740 hr_utility.set_location('Leaving ' || l_proc,60);
741
742 END setup_standard_balance_table;
743
744
745
746
747 PROCEDURE archinit (p_payroll_action_id IN NUMBER)
748 IS
749
750 CURSOR csr_archive_effective_date(pactid NUMBER) IS
751 SELECT effective_date
752 FROM pay_payroll_actions
753 WHERE payroll_action_id = pactid;
754
755 CURSOR csr_input_value_id(p_element_name CHAR,
756 p_value_name CHAR) IS
757 SELECT pet.element_type_id,
758 piv.input_value_id
759 FROM pay_input_values_f piv,
760 pay_element_types_f pet
761 WHERE piv.element_type_id = pet.element_type_id
762 AND pet.legislation_code = 'IE'
763 AND pet.element_name = p_element_name
764 AND piv.name = p_value_name;
765
766 CURSOR csr_payroll_type(p_payroll_id NUMBER,
767 p_effective_date DATE) IS
768 SELECT period_type
769 FROM pay_all_payrolls_f
770 WHERE payroll_id = p_payroll_id
771 AND p_effective_date
772 BETWEEN effective_start_date AND effective_end_date;
773
774 CURSOR csr_get_prsi_week_id IS
775 SELECT pdb.defined_balance_id
776 FROM pay_defined_balances pdb,
777 pay_balance_types pbt,
778 pay_balance_dimensions pbd
779 WHERE pbd.dimension_name = '_ASG_YTD'
780 AND pbd.legislation_code = 'IE'
781 AND pbt.balance_name = 'IE PRSI Insurable Weeks'
782 AND pbt.legislation_code = 'IE'
783 AND pdb.balance_type_id = pbt.balance_type_id
784 AND pdb.balance_dimension_id = pbd.balance_dimension_id
785 AND pdb.legislation_code = 'IE';
786
787 -- 4369280
788 -- Cursor to fetch Employer id to stripe the balances.
789 CURSOR csr_get_tax_unit_id(p_business_group_id NUMBER,
790 p_consolidation_set NUMBER,
791 p_start_date DATE,
792 p_end_date DATE
793 )IS
794
795 SELECT org.organization_id
796 FROM
797 pay_all_payrolls_f ppf,
798 hr_soft_coding_keyflex flex,
799 hr_organization_information org
800 WHERE ppf.soft_coding_keyflex_id=flex.soft_coding_keyflex_id
801 AND ppf.business_group_id =p_business_group_id
802 AND org.org_information_context = 'IE_EMPLOYER_INFO'
803 AND org.organization_id=flex.segment4
804 AND ppf.consolidation_set_id =p_consolidation_set
805 -- AND ppf.payroll_id=p_payroll_id
806 AND ppf.effective_start_date <= p_end_date
807 AND ppf.effective_end_date >= p_start_date
808 AND rownum = 1;
809
810 l_proc VARCHAR2(50) := g_package || 'archinit';
811
812 l_assignment_set_id NUMBER;
813 l_bg_id NUMBER;
814 l_canonical_end_date DATE;
815 l_canonical_start_date DATE;
816 l_consolidation_set NUMBER;
817 l_end_date VARCHAR2(30);
818 l_payroll_id NUMBER;
819 l_start_date VARCHAR2(30);
820 l_tax_credit_value VARCHAR2(30);
821 l_std_cut_off_value VARCHAR2(30);
822 l_payroll_type VARCHAR2(30);
823
824 BEGIN
825
826 --
827 hr_utility.set_location('Entering ' || l_proc,10);
828
829 g_archive_pact := p_payroll_action_id;
830
831 OPEN csr_archive_effective_date(p_payroll_action_id);
832
833 FETCH csr_archive_effective_date
834 INTO g_archive_effective_date;
835
836 CLOSE csr_archive_effective_date;
837
838 pay_ie_legislative_archive.get_parameters (
839 p_payroll_action_id => p_payroll_action_id
840 , p_token_name => 'PAYROLL'
841 , p_token_value => l_payroll_id);
842
843 pay_ie_legislative_archive.get_parameters (
844 p_payroll_action_id => p_payroll_action_id
845 , p_token_name => 'CONSOLIDATION'
846 , p_token_value => l_consolidation_set);
847
848 pay_ie_legislative_archive.get_parameters (
849 p_payroll_action_id => p_payroll_action_id
850 , p_token_name => 'ASSIGNMENT_SET'
851 , p_token_value => l_assignment_set_id);
852
853 pay_ie_legislative_archive.get_parameters (
854 p_payroll_action_id => p_payroll_action_id
855 , p_token_name => 'START_DATE'
856 , p_token_value => l_start_date);
857
858 pay_ie_legislative_archive.get_parameters (
859 p_payroll_action_id => p_payroll_action_id
860 , p_token_name => 'END_DATE'
861 , p_token_value => l_end_date);
862
863 pay_ie_legislative_archive.get_parameters (
864 p_payroll_action_id => p_payroll_action_id
865 , p_token_name => 'BG_ID'
866 , p_token_value => l_bg_id);
867
868 hr_utility.set_location('Step ' || l_proc,20);
869 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
870 hr_utility.set_location('l_start_date = ' || l_start_date,20);
871 hr_utility.set_location('l_end_date = ' || l_end_date,20);
872
873 l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
874 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
875
876 -- Get Payroll Period Type
877
878 hr_utility.set_location('Getting Period Type of the Payroll', 30);
879
880 OPEN csr_payroll_type(l_payroll_id,
881 g_archive_effective_date);
882
883 FETCH csr_payroll_type INTO l_payroll_type;
884
885 CLOSE csr_payroll_type;
886
887 hr_utility.set_location('l_payroll_type = '|| l_payroll_type, 30);
888
889 IF l_payroll_type in ('Week', 'Bi-Week', 'Lunar Month') THEN
890 l_tax_credit_value := 'Weekly Tax Credit';
891 l_std_cut_off_value := 'Weekly Standard Rate Cutoff';
892
893 ELSE
894
895 l_tax_credit_value := 'Monthly Tax Credit';
896 l_std_cut_off_value := 'Monthly Standard Rate Cutoff';
897
898 END IF;
899
900 hr_utility.set_location('l_tax_credit_value = '|| l_tax_credit_value, 30);
901 hr_utility.set_location('l_std_cut_off_value = '|| l_std_cut_off_value, 30);
902
903 -- retrieve ids for tax elements
904
905 OPEN csr_input_value_id('IE PAYE details',l_tax_credit_value);
906
907 FETCH csr_input_value_id INTO g_paye_details_element_id,
908 g_tax_credit_id;
909
910 CLOSE csr_input_value_id;
911
912 hr_utility.set_location('g_tax_credit_id = '|| to_char(g_tax_credit_id), 30);
913
914 OPEN csr_input_value_id('IE PAYE details',l_std_cut_off_value);
915
916 FETCH csr_input_value_id INTO g_paye_details_element_id,
917 g_std_cut_off_id;
918
919 CLOSE csr_input_value_id;
920
921 hr_utility.set_location('g_std_cut_off_id = '|| to_char(g_std_cut_off_id), 30);
922
923 OPEN csr_input_value_id('IE PAYE details','Tax Basis');
924
925 FETCH csr_input_value_id INTO g_paye_details_element_id,
926 g_tax_basis_id;
927
928 CLOSE csr_input_value_id;
929
930
931 OPEN csr_input_value_id('IE PRSI Detail','Contribution Class');
932
933 FETCH csr_input_value_id INTO g_paye_details_element_id,
934 g_prsi_cat_id;
935
936 CLOSE csr_input_value_id;
937
938
939 OPEN csr_input_value_id('IE PRSI Detail','Subclass');
940
941 FETCH csr_input_value_id INTO g_paye_details_element_id,
942 g_prsi_subcat_id;
943
944 CLOSE csr_input_value_id;
945
946 OPEN csr_input_value_id('IE PRSI Detail','Insurable Weeks');
947
948 FETCH csr_input_value_id INTO g_paye_details_element_id,
949 g_ins_weeks_id;
950
951 CLOSE csr_input_value_id;
952
953 OPEN csr_get_prsi_week_id;
954
955 FETCH csr_get_prsi_week_id INTO g_prsi_week_id;
956
957 CLOSE csr_get_prsi_week_id;
958
959 OPEN csr_get_tax_unit_id(l_bg_id,l_consolidation_set,l_canonical_start_date,l_canonical_end_date);
960 FETCH csr_get_tax_unit_id INTO g_tax_unit_id;
961 CLOSE csr_get_tax_unit_id;
962
963 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
964 hr_utility.set_location('l_consolidation_set = ' || l_consolidation_set,20);
965 hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
966 hr_utility.set_location('l_canonical_end_date = ' || l_canonical_end_date,20);
967
968
969 -- retrieve and archive user defintions from EITs
970
971 g_max_user_balance_index := 0;
972
973 hr_utility.set_location('get_eit_definitions - balances',20);
974
975 pay_ie_legislative_archive.get_eit_definitions (
976 p_pactid => p_payroll_action_id
977 , p_business_group_id => l_bg_id
978 , p_payroll_pact => NULL
979 , p_effective_date => l_canonical_start_date
980 , p_eit_context => g_balance_context
981 , p_archive => 'N');
982
983 hr_utility.set_location('get_eit_definitions - elements',20);
984
985 pay_ie_legislative_archive.get_eit_definitions (
986 p_pactid => p_payroll_action_id
987 , p_business_group_id => l_bg_id
988 , p_payroll_pact => NULL
989 , p_effective_date => l_canonical_start_date
990 , p_eit_context => g_element_context
991 , p_archive => 'N');
992
993 pay_balance_pkg.set_context('PAYROLL_ACTION_ID'
994 , p_payroll_action_id);
995
996 -- setup statutory balances pl/sql table
997
998 pay_ie_legislative_archive.setup_standard_balance_table;
999
1000 hr_utility.set_location('Leaving ' || l_proc,20);
1001 --
1002
1003 END archinit;
1004
1005 PROCEDURE archive_employee_details (
1006 p_assactid IN NUMBER
1007 , p_assignment_id IN NUMBER
1008 , p_curr_pymt_ass_act_id IN NUMBER
1009 , p_date_earned IN DATE
1010 , p_effective_date IN DATE -- Bug Fix 4260031
1011 , p_curr_pymt_eff_date IN DATE
1012 , p_time_period_id IN NUMBER
1013 , p_record_count IN NUMBER) IS
1014
1015 l_action_info_id NUMBER;
1016 l_ovn NUMBER;
1017
1018 l_proc VARCHAR2(60) := g_package || 'archive_employee_details';
1019
1020 BEGIN
1021
1022 hr_utility.set_location('Entering ' || l_proc,10);
1023
1024 -- call generic procedure to retrieve and archive all data for
1025 -- EMPLOYEE DETAILS, ADDRESS DETAILS and EMPLOYEE NET PAY DISTRIBUTION
1026
1027 hr_utility.set_location('Calling pay_emp_action_arch',20);
1028
1029 pay_emp_action_arch.get_personal_information (
1030 p_payroll_action_id => g_archive_pact -- archive payroll_action_id
1031 , p_assactid => p_assactid -- archive assignment_action_id
1032 , p_assignment_id => p_assignment_id -- current assignment_id
1033 , p_curr_pymt_ass_act_id => p_curr_pymt_ass_act_id -- prepayment assignment_action_id
1034 -- , p_curr_eff_date => g_archive_effective_date -- archive effective_date
1035 , p_curr_eff_date => p_effective_date -- payroll effective_date -- Bug Fix 4260031
1036 , p_date_earned => p_date_earned -- payroll date_earned
1037 , p_curr_pymt_eff_date => p_curr_pymt_eff_date -- prepayment effective_date
1038 , p_tax_unit_id => NULL -- only required for US
1039 , p_time_period_id => p_time_period_id -- payroll time_period_id
1040 , p_ppp_source_action_id => NULL);
1041 --
1042 hr_utility.set_location('Leaving ' || l_proc,30);
1043 --
1044 END archive_employee_details;
1045
1046 PROCEDURE archive_ie_employee_details (
1047 p_assactid IN NUMBER
1048 , p_assignment_id IN NUMBER
1049 , p_curr_pymt_ass_act_id IN NUMBER
1050 , p_effective_date IN DATE
1051 , p_ppsn_override IN VARCHAR2) IS --6633719
1052
1053 l_action_info_id NUMBER;
1054 l_ovn NUMBER;
1055 l_tax_basis VARCHAR2(20);
1056 l_tax_basis_det VARCHAR2(20);
1057 l_ins_weeks NUMBER;
1058 l_run_action_id NUMBER;
1059 l_prsi_week NUMBER;
1060 l_prsi_cat VARCHAR2(10);
1061 l_prsi_subcat VARCHAR2(10);
1062 l_tax_credit NUMBER;
1063 l_std_cut_off NUMBER;
1064 --Bug 4025154 Changing l_firstname and l_lastname to varchar2(80) from varchar2(20)
1065 --as accented characters occupy more than 1 byte.
1066 --
1067 l_firstname VARCHAR2(80);
1068 l_surname VARCHAR2(80);
1069 l_dob VARCHAR2(20);
1070
1071 l_proc VARCHAR2(60) := g_package || 'archive_ie_employee_details';
1072
1073 -- Bug 2569918
1074 -- Added functions get_first_name,get_last_name and get_dob for P35/P60
1075 -- reporting
1076 -- Used per_all_assignments_f 4555600
1077 FUNCTION get_first_name(p_run_assignment_action_id NUMBER) RETURN VARCHAR2
1078 IS
1079 CURSOR csr_first_name IS
1080 SELECT substr(papf.first_name||' '||papf.middle_names,1,20)
1081 FROM per_people_f papf,
1082 per_assignments_f paf,
1083 pay_assignment_actions paa,
1084 pay_payroll_actions ppa
1085 WHERE paa.assignment_action_id = p_run_assignment_action_id
1086 AND paf.assignment_id = paa.assignment_id
1087 AND paf.person_id = papf.person_id
1088 AND paa.payroll_action_id = ppa.payroll_action_id
1089 AND ppa.effective_date between paf.effective_start_date
1090 and paf.effective_end_date
1091 AND ppa.effective_date between papf.effective_start_date
1092 and papf.effective_end_date;
1093 --Bug 4025154 Changing l_first_name to varchar2(80) from varchar2(20)
1094 --as accented characters occupy more than 1 byte.Although substr returns 20 chars
1095 --accented char. occupy greater than 20 bytes.They have been made as 80 for future requirement if any.
1096 l_first_name varchar2(80);
1097 --
1098 BEGIN
1099 --
1100 --
1101 OPEN csr_first_name;
1102 FETCH csr_first_name INTO l_first_name;
1103 CLOSE csr_first_name;
1104 --
1105 RETURN l_first_name;
1106 --
1107 EXCEPTION
1108 WHEN NO_DATA_FOUND THEN
1109 l_first_name := NULL;
1110 hr_utility.trace('First Name : NULL ');
1111 return l_first_name;
1112 --
1113 END get_first_name;
1114
1115 -- Used per_all_assignments_f 4555600
1116 FUNCTION get_last_name(p_run_assignment_action_id NUMBER) RETURN VARCHAR2
1117 IS
1118 CURSOR csr_last_name is
1119 SELECT substr(papf.last_name,1,20)
1120 FROM per_people_f papf,
1121 per_assignments_f paf,
1122 pay_assignment_actions paa,
1123 pay_payroll_actions ppa
1124 WHERE paa.assignment_action_id = p_run_assignment_action_id
1125 AND paf.assignment_id = paa.assignment_id
1126 AND paf.person_id = papf.person_id
1127 AND paa.payroll_action_id = ppa.payroll_action_id
1128 AND ppa.effective_date between paf.effective_start_date
1129 and paf.effective_end_date
1130 AND ppa.effective_date between papf.effective_start_date
1131 and papf.effective_end_date;
1132 --Bug 4025154 Changing l_last_name to varchar2(80) from varchar2(20)
1133 --as accented characters occupy more than 1 byte.
1134
1135 l_last_name varchar2(80);
1136 --
1137 BEGIN
1138 --
1139 --
1140 OPEN csr_last_name;
1141 FETCH csr_last_name INTO l_last_name;
1142 CLOSE csr_last_name;
1143 --
1144 RETURN l_last_name;
1145 --
1146 EXCEPTION
1147 WHEN NO_DATA_FOUND THEN
1148 l_last_name := NULL;
1149 hr_utility.trace('Last Name : NULL ');
1150 RETURN l_last_name;
1151 --
1152 END get_last_name;
1153
1154 FUNCTION get_dob(p_run_assignment_action_id NUMBER) RETURN VARCHAR2
1155 IS
1156
1157 -- Used per_all_assignments_f 4555600
1158 CURSOR csr_dob IS
1159 SELECT to_char(papf.date_of_birth,'dd-mon-yyyy')
1160 FROM per_people_f papf,
1161 per_assignments_f paf,
1162 pay_assignment_actions paa,
1163 pay_payroll_actions ppa
1164 WHERE paa.assignment_action_id = p_run_assignment_action_id
1165 AND paf.assignment_id = paa.assignment_id
1166 AND paf.person_id = papf.person_id
1167 AND paa.payroll_action_id = ppa.payroll_action_id
1168 AND ppa.effective_date between paf.effective_start_date
1169 and paf.effective_end_date
1170 AND ppa.effective_date between papf.effective_start_date
1171 and papf.effective_end_date;
1172 --
1173 --
1174 l_first_name varchar2(20);
1175 --
1176 BEGIN
1177 --
1178 --
1179 OPEN csr_dob;
1180 FETCH csr_dob INTO l_dob;
1181 CLOSE csr_dob;
1182 --
1183 RETURN l_dob;
1184 --
1185 EXCEPTION
1186 WHEN NO_DATA_FOUND THEN
1187 l_dob := NULL;
1188 hr_utility.trace('DOB : NULL ');
1189 return l_dob;
1190 --
1191 END get_dob;
1192
1193
1194
1195 BEGIN
1196
1197 hr_utility.set_location('Entering ' || l_proc,10);
1198
1199 -- Retrieve and Archive the IE specific employee details
1200
1201 l_tax_basis := pay_ie_archive_detail_pkg.get_tax_details (
1202 p_run_assignment_action_id => p_curr_pymt_ass_act_id
1203 ,p_input_value_id => g_tax_basis_id
1204 ,p_date_earned => to_char(p_effective_date, 'yyyy/mm/dd'));
1205
1206 hr_utility.set_location('l_tax_basis = ' || l_tax_basis,40);
1207
1208 l_prsi_cat := pay_ie_archive_detail_pkg.get_tax_details (
1209 p_run_assignment_action_id => p_curr_pymt_ass_act_id
1210 ,p_input_value_id => g_prsi_cat_id
1211 ,p_date_earned => to_char(p_effective_date, 'yyyy/mm/dd'));
1212
1213 hr_utility.set_location('l_prsi_cat = ' || l_prsi_cat,40);
1214
1215 l_prsi_subcat := pay_ie_archive_detail_pkg.get_tax_details (
1216 p_run_assignment_action_id => p_curr_pymt_ass_act_id
1217 ,p_input_value_id => g_prsi_subcat_id
1218 ,p_date_earned => to_char(p_effective_date, 'yyyy/mm/dd'));
1219
1220 hr_utility.set_location('l_prsi_subcat = ' || l_prsi_subcat,40);
1221
1222 l_ins_weeks := pay_ie_archive_detail_pkg.get_tax_details (
1223 p_run_assignment_action_id => p_curr_pymt_ass_act_id
1224 ,p_input_value_id => g_ins_weeks_id
1225 ,p_date_earned => to_char(p_effective_date, 'yyyy/mm/dd'));
1226
1227 hr_utility.set_location('l_ins_weeks = ' || l_ins_weeks,40);
1228
1229 l_tax_credit := pay_ie_archive_detail_pkg.get_tax_details (
1230 p_run_assignment_action_id => p_curr_pymt_ass_act_id
1231 ,p_input_value_id => g_tax_credit_id
1232 ,p_date_earned => to_char(p_effective_date, 'yyyy/mm/dd'));
1233
1234 hr_utility.set_location('l_tax_credit = ' || l_tax_credit,40);
1235
1236 l_std_cut_off := pay_ie_archive_detail_pkg.get_tax_details (
1237 p_run_assignment_action_id => p_curr_pymt_ass_act_id
1238 ,p_input_value_id => g_std_cut_off_id
1239 ,p_date_earned => to_char(p_effective_date, 'yyyy/mm/dd'));
1240
1241 hr_utility.set_location('l_std_cut_off = ' || l_std_cut_off,40);
1242
1243 hr_utility.set_location('g_prsi_week_id = ' || g_prsi_week_id,41);
1244 hr_utility.set_location('p_curr_pymt_ass_act_id = ' || p_curr_pymt_ass_act_id,42);
1245
1246 l_prsi_week := pay_balance_pkg.get_value (g_prsi_week_id,
1247 p_curr_pymt_ass_act_id,
1248 false);
1249
1250 hr_utility.set_location('l_prsi_week = ' || l_prsi_week,45);
1251
1252 l_firstname := get_first_name(p_curr_pymt_ass_act_id);
1253 l_surname := get_last_name(p_curr_pymt_ass_act_id);
1254 l_dob := get_dob(p_curr_pymt_ass_act_id);
1255
1256
1257 IF l_tax_basis = 'IE_CUMULATIVE'
1258
1259 THEN
1260
1261 l_tax_basis_det := 'Cumulative';
1262
1263 ELSIF l_tax_basis = 'IE_EMERGENCY'
1264
1265 THEN
1266
1267 l_tax_basis_det := 'Emergency';
1268
1269 ELSIF l_tax_basis = 'IE_WEEK1_MONTH1'
1270
1271 THEN
1272
1273 l_tax_basis_det := 'Week/Month 1';
1274
1275 ELSIF l_tax_basis = 'IE_EXEMPTION'
1276
1277 THEN
1278
1279 l_tax_basis_det := 'Exempt-Cumulative';
1280
1281 ELSIF l_tax_basis = 'IE_EXEMPT_WEEK_MONTH'
1282
1283 THEN
1284
1285 l_tax_basis_det := 'Exempt-Week1/Month1';
1286 -- 6266653
1287 ELSIF l_tax_basis = 'IE_EMERGENCY_NO_PPS'
1288 THEN
1289 l_tax_basis_det := 'Emergency No PPS';
1290
1291 ELSIF l_tax_basis = 'IE_EXCLUDE'
1292 THEN
1293 l_tax_basis_det := 'Exclusion';
1294 ELSE
1295
1296 l_tax_basis_det := l_tax_basis;
1297
1298 END IF;
1299
1300 hr_utility.set_location('Archiving IE EMPLOYEE DETAILS',50);
1301
1302 pay_action_information_api.create_action_information (
1303 p_action_information_id => l_action_info_id
1304 , p_action_context_id => p_assactid
1305 , p_action_context_type => 'AAP'
1306 , p_object_version_number => l_ovn
1307 , p_assignment_id => p_assignment_id
1308 , p_effective_date => g_archive_effective_date
1309 , p_source_id => NULL
1310 , p_source_text => NULL
1311 , p_action_information_category => 'IE EMPLOYEE DETAILS'
1312 , p_action_information1 => NULL
1313 , p_action_information2 => NULL
1314 , p_action_information3 => NULL
1315 , p_action_information20 => p_ppsn_override --6633719
1316 , p_action_information21 => l_tax_basis_det
1317 , p_action_information22 => l_prsi_cat
1318 , p_action_information23 => l_prsi_subcat
1319 , p_action_information24 => l_prsi_week --l_ins_weeks
1320 , p_action_information25 => l_dob
1321 , p_action_information26 => l_tax_credit
1322 , p_action_information27 => l_std_cut_off
1323 , p_action_information28 => l_firstname
1324 , p_action_information29 => l_surname);
1325
1326 --
1327 hr_utility.set_location('Leaving ' || l_proc,60);
1328 --
1329 END archive_ie_employee_details;
1330
1331 PROCEDURE process_balance (p_action_context_id IN NUMBER,
1332 p_assignment_id IN NUMBER,
1333 p_source_id IN NUMBER,
1334 p_effective_date IN DATE,
1335 p_balance IN VARCHAR2,
1336 p_dimension IN VARCHAR2,
1337 p_defined_bal_id IN NUMBER,
1338 p_record_count IN NUMBER,
1339 p_tax_unit_id IN NUMBER)
1340
1341 IS
1342 /*Bug No. 3738576*/
1343 CURSOR Cur_Act_Contexts IS
1344 SELECT pac.context_id, pac.context_value
1345 FROM pay_action_contexts pac, ff_contexts ffc
1346 WHERE pac.assignment_action_id = p_source_id
1347 AND ffc.context_name = 'SOURCE_TEXT'
1348 AND ffc.context_id = pac.context_id;
1349 /*
1350 SELECT Context_ID,Context_Value
1351 FROM PAY_ACTION_CONTEXTS
1352 WHERE Assignment_Action_ID = p_source_id;
1353 */
1354 v_Cur_Act_Contexts Cur_Act_Contexts%ROWTYPE;
1355
1356 -- Added for Bug 2545070 to handle PAYROLL REVERSALS
1357 CURSOR csr_get_reversal_action_id(
1358 c_assg_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
1359 SELECT max(paa_rev.assignment_action_id)
1360 FROM pay_assignment_actions paa_src
1361 ,pay_assignment_actions paa_rev
1362 ,pay_assignment_actions paa_cur
1363 ,pay_payroll_actions ppa_rev
1364 ,pay_action_interlocks pai_rev
1365 WHERE paa_cur.assignment_action_id = c_assg_action_id
1366 AND paa_src.source_action_id = paa_cur.source_action_id
1367 AND paa_src.assignment_id = paa_cur.assignment_id
1368 AND pai_rev.locked_action_id = paa_src.assignment_action_id
1369 AND ppa_rev.action_type = 'V'
1370 AND ppa_rev.payroll_action_id = paa_rev.payroll_action_id
1371 AND paa_rev.assignment_id = paa_src.assignment_id
1372 AND paa_rev.assignment_action_id = pai_rev.locking_action_id;
1373
1374 l_rev_asg_action_id NUMBER;
1375 l_source_id NUMBER;
1376
1377 l_action_info_id NUMBER;
1378 l_balance_value NUMBER :=0; -- 4879850
1379 l_ovn NUMBER;
1380 l_record_count VARCHAR2(10);
1381
1382 l_proc VARCHAR2(50) := g_package || 'process_balance';
1383
1384 BEGIN
1385
1386 hr_utility.set_location('Entering ' || l_proc,10);
1387
1388 hr_utility.set_location('Step ' || l_proc,20);
1389 hr_utility.set_location('p_source_id = ' || p_source_id,20);
1390 hr_utility.set_location('p_balance = ' || p_balance,20);
1391 hr_utility.set_location('p_dimension = ' || p_dimension,20);
1392 hr_utility.set_location('p_defined_bal_id = ' || p_defined_bal_id,20);
1393 hr_utility.set_location('p_tax_unit_id = ' || p_tax_unit_id,20);
1394 hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
1395
1396 -- Added for Bug 2545070 to handle PAYROLL REVERSALS
1397 OPEN csr_get_reversal_action_id(p_source_id);
1398 FETCH csr_get_reversal_action_id INTO l_rev_asg_action_id;
1399 CLOSE csr_get_reversal_action_id;
1400 l_source_id := NVL(l_rev_asg_action_id,p_source_id);
1401
1402 hr_utility.set_location('l_source_id = ' || l_source_id,20);
1403
1404 OPEN Cur_Act_Contexts;
1405 FETCH Cur_Act_Contexts INTO v_Cur_Act_Contexts;
1406 CLOSE Cur_Act_Contexts;
1407
1408 hr_utility.set_location('v_Cur_Act_Contexts.CONTEXT_ID= ' || v_Cur_Act_Contexts.CONTEXT_ID,20);
1409 hr_utility.set_location('v_Cur_Act_Contexts.CONTEXT_VALUE= ' || v_Cur_Act_Contexts.CONTEXT_VALUE,20);
1410
1411 -- Added if condition for 4879850
1412 -- Instead of checking Balance Name we now check the dimension for passing the Source Text Value to support SOE Balances
1413 -- Defined at BG Level which needs Source Text Context 5192325
1414 -- IF p_balance = 'IE PRSI Insurable Weeks' then
1415 -- Added IE PRSI Insurable weeks since p_dimension is not populated for Statutory Balances
1416 IF ((p_dimension IS NULL AND p_balance = 'IE PRSI Insurable Weeks') OR (p_dimension in ('_PRSI_ASG_YTD','_PRSI_ASG_PTD','_PER_PAYE_REF_PRSI_YTD','_PRSI_ASG_RUN','_ASG_PAYE_REF_PRSI_RUN'))) THEN
1417 IF v_Cur_Act_Contexts.CONTEXT_ID is not null and v_Cur_Act_Contexts.CONTEXT_VALUE is not null then
1418 l_balance_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1419 l_source_id,
1420 p_tax_unit_id,
1421 null,
1422 v_Cur_Act_Contexts.CONTEXT_ID,
1423 v_Cur_Act_Contexts.CONTEXT_VALUE,
1424 null,
1425 null);
1426 end if;
1427 else
1428 l_balance_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1429 l_source_id,
1430 p_tax_unit_id,
1431 null,
1432 null,
1433 null,
1434 null,
1435 null);
1436 end if;
1437
1438 hr_utility.set_location('l_balance_value = ' || l_balance_value,20);
1439 IF p_record_count = 0
1440
1441 THEN
1442
1443 l_record_count := NULL;
1444
1445 ELSE
1446
1447 l_record_count := p_record_count + 1;
1448 END IF;
1449 IF l_balance_value <> 0
1450
1451 THEN
1452
1453 hr_utility.set_location('Archiving EMEA BALANCES',20);
1454
1455 pay_action_information_api.create_action_information (
1456 p_action_information_id => l_action_info_id
1457 , p_action_context_id => p_action_context_id
1458 , p_action_context_type => 'AAP'
1459 , p_object_version_number => l_ovn
1460 , p_assignment_id => p_assignment_id
1461 , p_effective_date => p_effective_date
1462 , p_source_id => l_source_id
1463 , p_source_text => NULL
1464 , p_action_information_category => 'EMEA BALANCES'
1465 , p_action_information1 => p_defined_bal_id
1466 , p_action_information2 => NULL
1467 , p_action_information3 => NULL
1468 , p_action_information4 => fnd_number.number_to_canonical(l_balance_value) -- Changed by rmakhija for 3574741
1469 , p_action_information5 => l_record_count);
1470
1471 END IF;
1472
1473 hr_utility.set_location('Leaving ' || l_proc,30);
1474
1475 EXCEPTION
1476
1477 WHEN NO_DATA_FOUND
1478
1479 THEN
1480
1481 NULL;
1482
1483 END process_balance;
1484
1485 PROCEDURE get_element_info (p_action_context_id IN NUMBER,
1486 p_assignment_id IN NUMBER,
1487 p_child_assignment_action IN NUMBER,
1488 p_effective_date IN DATE,
1489 p_record_count IN NUMBER,
1490 p_run_method IN VARCHAR2)
1491 IS
1492
1493 CURSOR csr_element_values (p_assignment_action_id NUMBER,
1494 p_element_type_id NUMBER,
1495 p_input_value_id NUMBER) IS
1496 SELECT prv.result_value
1497 FROM pay_run_result_values prv,
1498 pay_run_results prr
1499 WHERE prr.status IN ('P','PA')
1500 AND prv.run_result_id = prr.run_result_id
1501 AND prr.assignment_action_id = p_assignment_action_id
1502 AND prr.element_type_id = p_element_type_id
1503 AND prv.input_value_id = p_input_value_id
1504 AND prv.result_value IS NOT NULL;
1505
1506 l_action_info_id NUMBER;
1507 l_column_sequence NUMBER;
1508 l_element_type_id NUMBER;
1509 l_main_sequence NUMBER;
1510 l_multi_sequence NUMBER;
1511 l_ovn NUMBER;
1512 l_record_count VARCHAR2(10);
1513
1514 -- Added for bug 5387406
1515 l_result_value pay_run_result_values.result_value%TYPE;
1516
1517
1518 BEGIN
1519
1520 hr_utility.set_location('Entering get_element_info',10);
1521
1522 l_column_sequence := 0;
1523 l_element_type_id := 0;
1524 l_main_sequence := 0;
1525 l_multi_sequence := NULL;
1526
1527 IF p_record_count = 0
1528
1529 THEN
1530
1531 l_record_count := NULL;
1532
1533 ELSE
1534
1535 l_record_count := p_record_count + 1;
1536
1537 END IF;
1538
1539 hr_utility.set_location('g_max_element_index = ' || g_max_element_index,10);
1540
1541 FOR l_index IN 1 .. g_max_element_index
1542
1543 LOOP
1544
1545 hr_utility.set_location('element_type_id = ' || g_element_table(l_index).element_type_id,10);
1546 hr_utility.set_location('input_value_id = ' || g_element_table(l_index).input_value_id,10);
1547 hr_utility.set_location('p_child_assignment_action = ' || p_child_assignment_action,10);
1548
1549 FOR rec_element_value IN csr_element_values (
1550 p_child_assignment_action
1551 , g_element_table(l_index).element_type_id
1552 , g_element_table(l_index).input_value_id)
1553
1554 LOOP
1555
1556 hr_utility.set_location('element_type_id = ' || g_element_table(l_index).element_type_id,10);
1557 hr_utility.set_location('input_value_id = ' || g_element_table(l_index).input_value_id,10);
1558 hr_utility.set_location('Archiving EMEA ELEMENT INFO',20);
1559
1560 IF l_element_type_id <> g_element_table(l_index).element_type_id
1561
1562 THEN
1563
1564 l_main_sequence := l_main_sequence + 1;
1565
1566 END IF;
1567
1568 hr_utility.set_location('l_main_sequence = ' || l_main_sequence,20);
1569
1570 l_column_sequence := l_column_sequence + 1;
1571
1572 -- If the run method is P, Process Separate, then only archive the data if
1573 -- a skip rule (formula_id) has been set. If there is no skip rule then the
1574 -- element info will be archived for the normal assignment action and doesn't
1575 -- need to be archived twice. If it is then duplicates will be displayed on
1576 -- the payslip.
1577
1578 IF p_run_method = 'P' AND g_element_table(l_index).formula_id IS NULL
1579
1580 THEN
1581
1582 NULL;
1583
1584 ELSE
1585 -- Added for bug 5387406. This checks for UOM, if it is money set the
1586 -- format mask to '999999999999999990.00'
1587 SELECT decode(g_element_table(l_index).uom, 'M',
1588 ltrim(rtrim(to_char(fnd_number.canonical_to_number(rec_element_value.result_value), '999999999999999990.00'))),
1589 rec_element_value.result_value)
1590 INTO l_result_value
1591 FROM dual;
1592
1593
1594 pay_action_information_api.create_action_information (
1595 p_action_information_id => l_action_info_id
1596 , p_action_context_id => p_action_context_id
1597 , p_action_context_type => 'AAP'
1598 , p_object_version_number => l_ovn
1599 , p_assignment_id => p_assignment_id
1600 , p_effective_date => p_effective_date
1601 , p_source_id => p_child_assignment_action
1602 , p_source_text => NULL
1603 , p_action_information_category => 'EMEA ELEMENT INFO'
1604 , p_action_information1 => g_element_table(l_index).element_type_id
1605 , p_action_information2 => g_element_table(l_index).input_value_id
1606 , p_action_information3 => NULL
1607 , p_action_information4 => l_result_value
1608 , p_action_information5 => l_main_sequence
1609 , p_action_information6 => l_multi_sequence
1610 , p_action_information7 => l_column_sequence
1611 , p_action_information8 => l_record_count);
1612
1613 END IF;
1614
1615 l_multi_sequence := NVL(l_multi_sequence,0) + 1;
1616 l_element_type_id := g_element_table(l_index).element_type_id;
1617
1618 END LOOP;
1619
1620 l_multi_sequence := NULL;
1621
1622 END LOOP;
1623
1624 EXCEPTION
1625
1626 WHEN NO_DATA_FOUND
1627
1628 THEN
1629
1630 NULL;
1631
1632 END get_element_info;
1633
1634 --For bug fix 3567562
1635 --Added a new procedure to get the PAYE reference attributed to payrolls within a consolidation set.
1636 PROCEDURE get_paye_reference(p_consolidation_set PAY_CONSOLIDATION_SETS.CONSOLIDATION_SET_ID%type
1637 ,g_paye_ref in out nocopy varchar2
1638 ,p_business_group_id varchar2
1639 ,p_start_date date
1640 ,p_end_date date
1641 ,l_error out nocopy varchar2)
1642
1643 is
1644 CURSOR get_payrolls is
1645 SELECT ppf.payroll_id
1646 FROM
1647 pay_all_payrolls_f ppf
1648 WHERE ppf.consolidation_set_id=p_consolidation_set
1649 AND ppf.business_group_id =p_business_group_id
1650 AND ppf.effective_start_date <= p_end_date
1651 AND ppf.effective_end_date >= p_start_date
1652 ORDER by payroll_id;
1653 --4369280
1654 --Changed to handle to architecture for Employer
1655 CURSOR get_paye_reference_details(p_payroll_id varchar2) is
1656 SELECT org.org_information2
1657 FROM
1658 pay_all_payrolls_f ppf,
1659 hr_soft_coding_keyflex flex,
1660 hr_organization_information org
1661 WHERE ppf.soft_coding_keyflex_id=flex.soft_coding_keyflex_id
1662 AND ppf.business_group_id =p_business_group_id
1663 AND org.org_information_context = 'IE_EMPLOYER_INFO'
1664 AND org.organization_id=flex.segment4
1665 AND ppf.consolidation_set_id =p_consolidation_set
1666 AND ppf.payroll_id=p_payroll_id
1667 AND ppf.effective_start_date <= p_end_date
1668 AND ppf.effective_end_date >= p_start_date;
1669
1670 l_paye_ref hr_organization_information.org_information2%type;
1671 l_paye_value hr_organization_information.org_information2%type;
1672 l_payroll_action_message varchar2(1000);
1673 c_error exception;
1674 error_message boolean;
1675 l_proc CONSTANT VARCHAR2(100):= g_package||'get_paye_reference_details';
1676
1677
1678 begin
1679
1680 hr_utility.set_location('Entering ' || l_proc,10);
1681
1682 for l_payroll_id in get_payrolls
1683 loop
1684 open get_paye_reference_details(l_payroll_id.payroll_id);
1685 loop
1686 fetch get_paye_reference_details into l_paye_ref;
1687 exit when get_paye_reference_details%notfound;
1688
1689 if l_paye_value <> l_paye_ref then
1690 raise c_error;
1691 else
1692 l_paye_value:=l_paye_ref;
1693 g_paye_ref:=l_paye_value;
1694 end if;
1695 end loop;
1696 close get_paye_reference_details;
1697 hr_utility.trace('paye ref='||l_paye_value);
1698 end loop;
1699
1700 hr_utility.set_location('Leaving ' || l_proc,40);
1701
1702 exception when c_error then
1703 l_error := 'Y';
1704 g_paye_ref:=null;
1705 fnd_message.set_name('PER','HR_IE_PAYE_EOY_ERROR');
1706 FND_FILE.PUT_LINE(fnd_file.log,fnd_message.get);
1707 error_message:=FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','HR_IE_PAYE_EOY_ERROR');
1708
1709 END get_paye_reference;
1710
1711 PROCEDURE range_cursor (pactid IN NUMBER,
1712 sqlstr OUT NOCOPY VARCHAR2)
1713 -- public procedure which archives the payroll information, then returns a
1714 -- varchar2 defining a SQL statement to select all the people that may be
1715 -- eligible for payslip reports.
1716 -- The archiver uses this cursor to split the people into chunks for parallel
1717 -- processing.
1718 IS
1719 --
1720 l_proc CONSTANT VARCHAR2(50):= g_package||'range_cursor';
1721 -- vars for constructing the sqlstr
1722 l_range_cursor VARCHAR2(4000) := NULL;
1723 l_parameter_match VARCHAR2(500) := NULL;
1724 l_ovn NUMBER(15);
1725 l_request_id NUMBER;
1726 l_action_info_id NUMBER(15);
1727 l_business_group_id NUMBER;
1728 g_paye_ref VARCHAR2(10);
1729
1730 /* CURSOR csr_input_value_id(p_element_name CHAR,
1731 p_value_name CHAR) IS
1732 SELECT pet.element_type_id,
1733 piv.input_value_id
1734 FROM pay_input_values_f piv,
1735 pay_element_types_f pet
1736 WHERE piv.element_type_id = pet.element_type_id
1737 AND pet.legislation_code = 'IE'
1738 AND pet.element_name = p_element_name
1739 AND piv.name = p_value_name;
1740
1741 CURSOR csr_payrolls (p_payroll_id NUMBER,
1742 p_consolidation_set_id NUMBER,
1743 p_effective_date DATE) IS
1744 SELECT ppf.payroll_id
1745 FROM pay_all_payrolls_f ppf
1746 WHERE ppf.consolidation_set_id = p_consolidation_set_id
1747 AND ppf.payroll_id = NVL(p_payroll_id,ppf.payroll_id)
1748 AND p_effective_date BETWEEN
1749 ppf.effective_start_date AND ppf.effective_end_date;
1750
1751
1752 CURSOR csr_payroll_info(p_payroll_id NUMBER,
1753 p_consolidation_id NUMBER,
1754 p_start_date DATE,
1755 p_end_date DATE,
1756 g_paye_ref VARCHAR2) IS
1757 SELECT pact.payroll_action_id payroll_action_id,
1758 pact.effective_date effective_date,
1759 pact.date_earned date_earned,
1760 pact.payroll_id,
1761 org.org_information1 tax_details_ref_no,
1762 org.org_information2 employer_paye_ref_no,
1763 ppf.payroll_name payroll_name,
1764 ppf.period_type period_type,
1765 pact.pay_advice_message payroll_message
1766 FROM pay_payrolls_f ppf,
1767 pay_payroll_actions pact,
1768 hr_soft_coding_keyflex flex,
1769 hr_organization_information org
1770 WHERE ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
1771 AND org.org_information_context = 'IE_ORG_INFORMATION'
1772 AND org.org_information1 = flex.segment1
1773 AND ppf.business_group_id = org.organization_id
1774 AND pact.payroll_id = ppf.payroll_id
1775 AND pact.effective_date BETWEEN
1776 ppf.effective_start_date AND ppf.effective_end_date
1777 AND pact.payroll_id = NVL(p_payroll_id,pact.payroll_id)
1778 AND ppf.consolidation_set_id = p_consolidation_id
1779 AND pact.effective_date BETWEEN
1780 p_start_date AND p_end_date
1781 AND (pact.action_type = 'P' OR
1782 pact.action_type = 'U')
1783 AND pact.action_status = 'C'
1784 AND NOT EXISTS (SELECT NULL
1785 FROM pay_action_information pai
1786 WHERE pai.action_context_id = pact.payroll_action_id
1787 AND pai.action_context_type = 'PA'
1788 AND pai.action_information_category = 'EMEA PAYROLL INFO'
1789 AND pai.action_information5 = g_paye_ref ) -- Bug fix 4001540
1790
1791 -- Added for bug fix 3567562 to restrict details fetched based on PAYE Reference.
1792 AND org.org_information2 = flex.segment3
1793 AND org.org_information2 = g_paye_ref;
1794
1795
1796 -- cursor csr_get_org_tax_address
1797 CURSOR csr_get_org_tax_address( c_consolidation_set PAY_CONSOLIDATION_SETS.CONSOLIDATION_SET_ID%type
1798 , g_paye_ref HR_ORGANIZATION_INFORMATION.ORG_INFORMATION2%type
1799 ) IS
1800 SELECT org_info.org_information3 employer_tax_addr1
1801 ,org_info.org_information4 employer_tax_addr2
1802 ,org_info.org_information5 employer_tax_addr3
1803 ,org_info.org_information6 employer_tax_contact
1804 ,org_info.org_information7 employer_tax_ref_phone
1805 --,org_all.name employer_tax_rep_name
1806 --Added for bug fix 3567562,mofified source of Employer statutory reporting name
1807 ,org_info.org_information8 employer_tax_rep_name
1808 ,pcs.business_group_id business_group_id
1809 --
1810 FROM hr_all_organization_units org_all
1811 ,hr_organization_information org_info
1812 ,pay_consolidation_sets pcs
1813 WHERE pcs.consolidation_set_id = c_consolidation_set
1814 AND org_all.organization_id = pcs.business_group_id
1815 AND org_info.organization_id = org_all.organization_id
1816 AND org_info.org_information_context = 'IE_ORG_INFORMATION'
1817 -- Added for bug fix 3567562 to restrict details fetched based on PAYE Reference.
1818 AND org_info.org_information2 = g_paye_ref ;
1819
1820
1821 --
1822 CURSOR csr_payroll_mesg (p_payroll_id NUMBER,
1823 p_start_date DATE,
1824 p_end_date DATE) IS
1825 SELECT pact.payroll_action_id payroll_action_id,
1826 pact.effective_date effective_date,
1827 pact.date_earned date_earned,
1828 pact.pay_advice_message payroll_message
1829 FROM pay_payrolls_f ppf,
1830 pay_payroll_actions pact
1831 WHERE pact.payroll_id = ppf.payroll_id
1832 AND pact.effective_date BETWEEN
1833 ppf.effective_start_date AND ppf.effective_end_date
1834 AND pact.payroll_id = p_payroll_id
1835 AND pact.effective_date BETWEEN
1836 p_start_date AND p_end_date
1837 AND (pact.action_type = 'R' OR
1838 pact.action_type = 'Q')
1839 AND pact.action_status = 'C'
1840 AND NOT EXISTS (SELECT NULL
1841 FROM pay_action_information pai
1842 WHERE pai.action_context_id = pact.payroll_action_id
1843 AND pai.action_context_type = 'PA'
1844 AND pai.action_information_category = 'EMPLOYEE OTHER INFORMATION'); */
1845 --
1846 l_assignment_set_id NUMBER;
1847 l_bg_id NUMBER;
1848 l_canonical_end_date DATE;
1849 l_canonical_start_date DATE;
1850 l_consolidation_set NUMBER;
1851 l_end_date VARCHAR2(30);
1852 l_legislation_code VARCHAR2(30) := 'IE';
1853 l_payroll_id NUMBER;
1854 l_start_date VARCHAR2(30);
1855 l_tax_period_no VARCHAR2(30);
1856 l_error varchar2(1) ;
1857
1858
1859 BEGIN
1860
1861 --hr_utility.trace_on(null,'IEPS');
1862 hr_utility.set_location('Entering ' || l_proc,10);
1863
1864 pay_ie_legislative_archive.get_parameters (
1865 p_payroll_action_id => pactid
1866 , p_token_name => 'PAYROLL'
1867 , p_token_value => l_payroll_id);
1868
1869 pay_ie_legislative_archive.get_parameters (
1870 p_payroll_action_id => pactid
1871 , p_token_name => 'CONSOLIDATION'
1872 , p_token_value => l_consolidation_set);
1873
1874 pay_ie_legislative_archive.get_parameters (
1875 p_payroll_action_id => pactid
1876 , p_token_name => 'ASSIGNMENT_SET'
1877 , p_token_value => l_assignment_set_id);
1878
1879 pay_ie_legislative_archive.get_parameters (
1880 p_payroll_action_id => pactid
1881 , p_token_name => 'START_DATE'
1882 , p_token_value => l_start_date);
1883
1884 pay_ie_legislative_archive.get_parameters (
1885 p_payroll_action_id => pactid
1886 , p_token_name => 'END_DATE'
1887 , p_token_value => l_end_date);
1888
1889 pay_ie_legislative_archive.get_parameters (
1890 p_payroll_action_id => pactid
1891 , p_token_name => 'BG_ID'
1892 , p_token_value => l_bg_id);
1893
1894 hr_utility.set_location('Step ' || l_proc,20);
1895 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
1896 hr_utility.set_location('l_start_date = ' || l_start_date,20);
1897 hr_utility.set_location('l_end_date = ' || l_end_date,20);
1898
1899 l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
1900 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
1901
1902 -- archive EMEA PAYROLL INFO for each prepayment run identified
1903
1904 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
1905 hr_utility.set_location('l_consolidation_set = ' || l_consolidation_set,20);
1906 hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
1907 hr_utility.set_location('l_canonical_end_date = ' || l_canonical_end_date,20);
1908
1909 --Added for bug fix 3567562, call to the procedure to get the PAYE reference value
1910 get_paye_reference (l_consolidation_set,g_paye_ref,l_bg_id,l_canonical_start_date,l_canonical_end_date,l_error);
1911
1912 if l_error ='Y' then
1913 sqlstr := 'SELECT 1 FROM dual WHERE to_char(:payroll_action_id) = dummy';
1914 else
1915
1916
1917 /*FOR tax_info_rec IN csr_get_org_tax_address (l_consolidation_set,g_paye_ref) LOOP
1918 --
1919 pay_action_information_api.create_action_information (
1920 p_action_information_id => l_action_info_id
1921 , p_action_context_id => pactid
1922 , p_action_context_type => 'PA'
1923 , p_object_version_number => l_ovn
1924 , p_action_information_category => 'ADDRESS DETAILS'
1925 , p_action_information1 => tax_info_rec.business_group_id
1926 , p_action_information5 => tax_info_rec.employer_tax_addr1
1927 , p_action_information6 => tax_info_rec.employer_tax_addr2
1928 , p_action_information7 => tax_info_rec.employer_tax_addr3
1929 , p_action_information14 => 'IE Employer Tax Address'
1930 , p_action_information26 => tax_info_rec.employer_tax_contact
1931 , p_action_information27 => tax_info_rec.employer_tax_ref_phone
1932 , p_action_information28 => tax_info_rec.employer_tax_rep_name);
1933 --
1934 END LOOP;
1935
1936
1937
1938 g_max_user_balance_index := 0;
1939
1940 pay_ie_legislative_archive.get_eit_definitions (
1941 p_pactid => pactid
1942 , p_business_group_id => l_bg_id
1943 , p_payroll_pact => NULL
1944 , p_effective_date => l_canonical_start_date
1945 , p_eit_context => g_balance_context
1946 , p_archive => 'Y');
1947
1948 pay_ie_legislative_archive.get_eit_definitions (
1949 p_pactid => pactid
1950 , p_business_group_id => l_bg_id
1951 , p_payroll_pact => NULL
1952 , p_effective_date => l_canonical_start_date
1953 , p_eit_context => g_element_context
1954 , p_archive => 'Y');
1955
1956 pay_ie_legislative_archive.setup_element_definitions (
1957 p_pactid => pactid
1958 , p_payroll_pact => NULL
1959 , p_business_group_id => l_bg_id
1960 , p_effective_date => l_canonical_start_date);
1961
1962 FOR rec_payrolls in csr_payrolls(l_payroll_id,
1963 l_consolidation_set,
1964 l_canonical_end_date)
1965 LOOP
1966
1967 hr_utility.set_location('Calling arch_pay_action_level_data',25);
1968 --
1969
1970 pay_emp_action_arch.arch_pay_action_level_data (
1971 p_payroll_action_id => pactid
1972 , p_payroll_id => rec_payrolls.payroll_id
1973 , p_effective_date => l_canonical_end_date);
1974
1975 --
1976 END LOOP;
1977
1978 FOR rec_payroll_info in csr_payroll_info(l_payroll_id,
1979 l_consolidation_set,
1980 l_canonical_start_date,
1981 l_canonical_end_date,
1982 g_paye_ref)
1983
1984 LOOP
1985 pay_balance_pkg.set_context('PAYROLL_ACTION_ID'
1986 , rec_payroll_info.payroll_action_id);
1987 hr_utility.set_location('rec_payroll_info.payroll_action_id = ' || rec_payroll_info.payroll_action_id,30);
1988 hr_utility.set_location('rec_payroll_info.tax_details_ref = ' || rec_payroll_info.tax_details_ref_no,30);
1989 hr_utility.set_location('rec_payroll_info.employers_paye_ref_no = ' || rec_payroll_info.employer_paye_ref_no,30);
1990
1991 hr_utility.set_location('Archiving EMEA PAYROLL INFO',30);
1992
1993 pay_action_information_api.create_action_information (
1994 p_action_information_id => l_action_info_id
1995 , p_action_context_id => pactid
1996 , p_action_context_type => 'PA'
1997 , p_object_version_number => l_ovn
1998 , p_effective_date => rec_payroll_info.effective_date
1999 , p_source_id => NULL
2000 , p_source_text => NULL
2001 , p_action_information_category => 'EMEA PAYROLL INFO'
2002 , p_action_information1 => rec_payroll_info.payroll_action_id
2003 , p_action_information2 => rec_payroll_info.payroll_id
2004 , p_action_information3 => l_consolidation_set
2005 , p_action_information4 => rec_payroll_info.tax_details_ref_no
2006 , p_action_information5 => rec_payroll_info.employer_paye_ref_no
2007 , p_action_information6 => NULL);
2008
2009 END LOOP;
2010
2011
2012 -- The Payroll level message is archived in the generic archive structure
2013 -- EMPLOYEE OTHER INFORMATION
2014
2015 FOR rec_payroll_msg in csr_payroll_mesg(l_payroll_id,
2016 l_canonical_start_date,
2017 l_canonical_end_date)
2018
2019 LOOP
2020
2021 IF rec_payroll_msg.payroll_message IS NOT NULL
2022 THEN
2023 --
2024 pay_action_information_api.create_action_information (
2025 p_action_information_id => l_action_info_id
2026 , p_action_context_id => pactid
2027 , p_action_context_type => 'PA'
2028 , p_object_version_number => l_ovn
2029 , p_effective_date => rec_payroll_msg.effective_date
2030 , p_source_id => NULL
2031 , p_source_text => NULL
2032 , p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
2033 , p_action_information1 => rec_payroll_msg.payroll_action_id
2034 , p_action_information2 => 'MESG'
2035 , p_action_information3 => NULL
2036 , p_action_information4 => NULL
2037 , p_action_information5 => NULL
2038 , p_action_information6 => rec_payroll_msg.payroll_message);
2039
2040 END IF;
2041
2042 END LOOP;
2043
2044 sqlstr := 'SELECT DISTINCT person_id
2045 FROM per_people_f ppf,
2046 pay_payroll_actions ppa
2047 WHERE ppa.payroll_action_id = :payroll_action_id
2048 AND ppa.business_group_id +0= ppf.business_group_id
2049 ORDER BY ppf.person_id'; */
2050
2051 if l_payroll_id is null then
2052
2053 -- Use full cursor not restricting by payroll
2054 --
2055 -- Used per_all_assignments_f 4555600
2056 hr_utility.trace('Range Cursor Not using Payroll Restriction');
2057 sqlstr := 'SELECT distinct asg.person_id
2058 FROM per_periods_of_service pos,
2059 per_assignments_f asg,
2060 pay_payroll_actions ppa
2061 WHERE ppa.payroll_action_id = :payroll_action_id
2062 AND pos.person_id = asg.person_id
2063 AND pos.period_of_service_id = asg.period_of_service_id
2064 AND pos.business_group_id = ppa.business_group_id
2065 AND asg.business_group_id = ppa.business_group_id
2066 ORDER BY asg.person_id';
2067 else
2068 --
2069 -- The Payroll ID was used as parameter, so restrict by this
2070 --
2071 hr_utility.trace('Range Cursor using Payroll Restriction');
2072 sqlstr := 'SELECT DISTINCT ppf.person_id
2073 FROM per_people_f ppf,
2074 pay_payroll_actions ppa,
2075 per_assignments_f paaf
2076 WHERE ppa.payroll_action_id = :payroll_action_id
2077 AND ppf.business_group_id +0 = ppa.business_group_id
2078 AND paaf.person_id = ppf.person_id
2079 AND paaf.payroll_id = '|| to_char(l_payroll_id) ||
2080 ' ORDER BY ppf.person_id';
2081 end if;
2082
2083 hr_utility.set_location('Leaving ' || l_proc,40);
2084 end if;
2085
2086 END range_cursor;
2087
2088 ---------------------------------------------------------------------------
2089 -- Function: range_person_on.
2090 -- Description: Returns true if the range_person performance enhancement is
2091 -- enabled for the system. Used by action_creation.
2092 ---------------------------------------------------------------------------
2093 FUNCTION range_person_on RETURN BOOLEAN IS
2094 --
2095 CURSOR csr_action_parameter is
2096 select parameter_value
2097 from pay_action_parameters
2098 where parameter_name = 'RANGE_PERSON_ID';
2099 --
2100 CURSOR csr_range_format_param is
2101 select par.parameter_value
2102 from pay_report_format_parameters par,
2103 pay_report_format_mappings_f map
2104 where map.report_format_mapping_id = par.report_format_mapping_id
2105 and map.report_type = 'IEPS'
2106 and map.report_format = 'IELDGEN'
2107 and map.report_qualifier = 'IE'
2108 and par.parameter_name = 'RANGE_PERSON_ID';
2109 --
2110 l_return boolean;
2111 l_action_param_val varchar2(30);
2112 l_report_param_val varchar2(30);
2113 --
2114 BEGIN
2115 hr_utility.set_location('range_person_on',10);
2116 --
2117 BEGIN
2118 open csr_action_parameter;
2119 fetch csr_action_parameter into l_action_param_val;
2120 close csr_action_parameter;
2121 --
2122 hr_utility.set_location('range_person_on',20);
2123 open csr_range_format_param;
2124 fetch csr_range_format_param into l_report_param_val;
2125 close csr_range_format_param;
2126 --
2127 hr_utility.set_location('range_person_on',30);
2128 EXCEPTION WHEN NO_DATA_FOUND THEN
2129 l_return := FALSE;
2130 END;
2131 --
2132 hr_utility.set_location('range_person_on',40);
2133 IF l_action_param_val = 'Y' AND l_report_param_val = 'Y' THEN
2134 l_return := TRUE;
2135 hr_utility.trace('Range Person = True');
2136 ELSE
2137 l_return := FALSE;
2138 END IF;
2139 --
2140 RETURN l_return;
2141 --
2142 END range_person_on;
2143
2144 PROCEDURE action_creation (pactid in number,
2145 stperson in number,
2146 endperson in number,
2147 chunk in number) is
2148 --
2149 CURSOR csr_prepaid_assignments(p_pact_id NUMBER,
2150 stperson NUMBER,
2151 endperson NUMBER,
2152 p_payroll_id NUMBER,
2153 p_consolidation_id NUMBER) IS
2154 SELECT act.assignment_id assignment_id,
2155 act.assignment_action_id run_action_id,
2156 act1.assignment_action_id prepaid_action_id,
2157 act.tax_unit_id tax_unit_id
2158 FROM pay_payroll_actions ppa,
2159 pay_payroll_actions appa,
2160 pay_payroll_actions appa2,
2161 pay_assignment_actions act,
2162 pay_assignment_actions act1,
2163 pay_action_interlocks pai,
2164 per_assignments_f as1
2165 WHERE ppa.payroll_action_id = p_pact_id
2166 AND appa.consolidation_set_id = p_consolidation_id
2167 AND appa.effective_date BETWEEN
2168 ppa.start_date AND ppa.effective_date
2169 AND as1.person_id BETWEEN
2170 stperson AND endperson
2171 AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
2172 AND act.payroll_action_id = appa.payroll_action_id
2173 AND act.source_action_id IS NULL
2174 AND as1.assignment_id = act.assignment_id
2175 -- AND ppa.effective_date BETWEEN
2176 AND appa.effective_date BETWEEN -- Bug Fix 4260031
2177 as1.effective_start_date AND as1.effective_end_date
2178 AND act.action_status = 'C'
2179 AND act.assignment_action_id = pai.locked_action_id
2180 AND act1.assignment_action_id = pai.locking_action_id
2181 AND act1.action_status = 'C'
2182 AND act1.payroll_action_id = appa2.payroll_action_id
2183 AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
2184 AND (as1.payroll_id = p_payroll_id OR p_payroll_id IS NULL)
2185 AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
2186 FROM pay_action_interlocks pai1,
2187 pay_assignment_actions act2,
2188 pay_payroll_actions appa3
2189 WHERE pai1.locked_action_id = act.assignment_action_id
2190 AND act2.assignment_action_id = pai1.locking_action_id
2191 AND act2.payroll_action_id = appa3.payroll_action_id
2192 AND appa3.action_type = 'X'
2193 AND appa3.report_type = 'IEPS')
2194 ORDER BY act.assignment_id, act.assignment_action_id
2195 FOR UPDATE OF as1.assignment_id;
2196
2197 -- csr_range_pre_assignments is a copy of csr_prepaid_assignments
2198 -- but with a join to pay_population_ranges for performance enhancement
2199 -- stperson and endperson are not needed, uses chunk.
2200 --
2201 CURSOR csr_range_pre_assignments(p_pact_id NUMBER,
2202 chunk NUMBER,
2203 p_payroll_id NUMBER,
2204 p_consolidation_id NUMBER) IS
2205 SELECT act.assignment_id assignment_id,
2206 act.assignment_action_id run_action_id,
2207 act1.assignment_action_id prepaid_action_id,
2208 act.tax_unit_id tax_unit_id
2209 FROM pay_payroll_actions ppa,
2210 pay_payroll_actions appa,
2211 pay_payroll_actions appa2,
2212 pay_assignment_actions act,
2213 pay_assignment_actions act1,
2214 pay_action_interlocks pai,
2215 per_assignments_f as1,
2216 pay_population_ranges ppr
2217 WHERE ppa.payroll_action_id = p_pact_id
2218 AND appa.consolidation_set_id = p_consolidation_id
2219 AND appa.effective_date BETWEEN
2220 ppa.start_date AND ppa.effective_date
2221 AND as1.person_id = ppr.person_id
2222 AND ppr.chunk_number = chunk
2223 AND ppr.payroll_action_id = p_pact_id
2224 AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
2225 AND act.payroll_action_id = appa.payroll_action_id
2226 AND act.source_action_id IS NULL
2227 AND as1.assignment_id = act.assignment_id
2228 -- AND ppa.effective_date BETWEEN
2229 AND appa.effective_date BETWEEN -- Bug Fix 4260031
2230 as1.effective_start_date AND as1.effective_end_date
2231 AND act.action_status = 'C'
2232 AND act.assignment_action_id = pai.locked_action_id
2233 AND act1.assignment_action_id = pai.locking_action_id
2234 AND act1.action_status = 'C'
2235 AND act1.payroll_action_id = appa2.payroll_action_id
2236 AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
2237 AND (as1.payroll_id = p_payroll_id OR p_payroll_id IS NULL)
2238 AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
2239 FROM pay_action_interlocks pai1,
2240 pay_assignment_actions act2,
2241 pay_payroll_actions appa3
2242 WHERE pai1.locked_action_id = act.assignment_action_id
2243 AND act2.assignment_action_id = pai1.locking_action_id
2244 AND act2.payroll_action_id = appa3.payroll_action_id
2245 AND appa3.action_type = 'X'
2246 AND appa3.report_type = 'IEPS')
2247 ORDER BY act.assignment_id, act.assignment_action_id
2248 FOR UPDATE OF as1.assignment_id;
2249
2250 l_actid NUMBER;
2251 l_canonical_end_date DATE;
2252 l_canonical_start_date DATE;
2253 l_consolidation_set VARCHAR2(30);
2254 l_end_date VARCHAR2(20);
2255 l_payroll_id NUMBER;
2256 l_prepay_action_id NUMBER;
2257 l_start_date VARCHAR2(20);
2258
2259 l_proc VARCHAR2(50) := g_package||'action_creation';
2260
2261 BEGIN
2262
2263 hr_utility.set_location('Entering ' || l_proc,10);
2264
2265 pay_ie_legislative_archive.get_parameters (
2266 p_payroll_action_id => pactid
2267 , p_token_name => 'PAYROLL'
2268 , p_token_value => l_payroll_id);
2269
2270 pay_ie_legislative_archive.get_parameters (
2271 p_payroll_action_id => pactid
2272 , p_token_name => 'CONSOLIDATION'
2273 , p_token_value => l_consolidation_set);
2274
2275 pay_ie_legislative_archive.get_parameters (
2276 p_payroll_action_id => pactid
2277 , p_token_name => 'START_DATE'
2278 , p_token_value => l_start_date);
2279
2280 pay_ie_legislative_archive.get_parameters (
2281 p_payroll_action_id => pactid
2282 , p_token_name => 'END_DATE'
2283 , p_token_value => l_end_date);
2284
2285 hr_utility.set_location('Step ' || l_proc,20);
2286 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
2287 hr_utility.set_location('l_start_date = ' || l_start_date,20);
2288 hr_utility.set_location('l_end_date = ' || l_end_date,20);
2289
2290 l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
2291 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
2292
2293 l_prepay_action_id := 0;
2294 -- Check that the Range Person settings are on, if so,
2295 -- use csr_range_pre_assignments. If not, use csr_prepaid_assignments.
2296 --
2297 IF range_person_on THEN
2298 FOR csr_rec IN csr_range_pre_assignments(pactid ,
2299 chunk,
2300 l_payroll_id,
2301 l_consolidation_set)
2302
2303 LOOP
2304
2305 IF l_prepay_action_id <> csr_rec.prepaid_action_id
2306
2307 THEN
2308
2309 SELECT pay_assignment_actions_s.NEXTVAL
2310 INTO l_actid
2311 FROM dual;
2312
2313 -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
2314
2315 hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,pactid,chunk,csr_rec.tax_unit_id);
2316
2317 -- CREATE THE ARCHIVE TO PAYROLL MASTER ASSIGNMENT ACTION INTERLOCK AND
2318 -- THE ARCHIVE TO PREPAYMENT ASSIGNMENT ACTION INTERLOCK
2319
2320 hr_utility.set_location('creating lock1 ' || l_actid || ' to ' || csr_rec.run_action_id,20);
2321 hr_utility.set_location('creating lock2 ' || l_actid || ' to ' || csr_rec.prepaid_action_id,20);
2322
2323 hr_nonrun_asact.insint(l_actid,csr_rec.prepaid_action_id);
2324
2325 END IF;
2326
2327 hr_nonrun_asact.insint(l_actid,csr_rec.run_action_id);
2328
2329 l_prepay_action_id := csr_rec.prepaid_action_id;
2330
2331 END LOOP;
2332
2333 ELSE
2334
2335 FOR csr_rec IN csr_prepaid_assignments(pactid,
2336 stperson,
2337 endperson,
2338 l_payroll_id,
2339 l_consolidation_set)
2340
2341 LOOP
2342
2343 IF l_prepay_action_id <> csr_rec.prepaid_action_id
2344
2345 THEN
2346
2347 SELECT pay_assignment_actions_s.NEXTVAL
2348 INTO l_actid
2349 FROM dual;
2350
2351 -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
2352
2353 hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,pactid,chunk,csr_rec.tax_unit_id);
2354
2355 -- CREATE THE ARCHIVE TO PAYROLL MASTER ASSIGNMENT ACTION INTERLOCK AND
2356 -- THE ARCHIVE TO PREPAYMENT ASSIGNMENT ACTION INTERLOCK
2357
2358 hr_utility.set_location('creating lock1 ' || l_actid || ' to ' || csr_rec.run_action_id,20);
2359 hr_utility.set_location('creating lock2 ' || l_actid || ' to ' || csr_rec.prepaid_action_id,20);
2360
2361 hr_nonrun_asact.insint(l_actid,csr_rec.prepaid_action_id);
2362
2363 END IF;
2364
2365 hr_nonrun_asact.insint(l_actid,csr_rec.run_action_id);
2366
2367 l_prepay_action_id := csr_rec.prepaid_action_id;
2368
2369 END LOOP;
2370 END IF;
2371
2372 hr_utility.set_location('Leaving ' || l_proc,20);
2373
2374 END action_creation;
2375
2376 PROCEDURE archive_code (p_assactid in number,
2377 p_effective_date in date) IS
2378
2379 --6633719
2380 cursor csr_ppsn_override(p_assignment_id NUMBER)
2381 is
2382 select 'Y' PPSN_OVERRIDE --aei_information1 PPSN_OVERRIDE --6633719
2383 from per_assignment_extra_info
2384 where assignment_id = p_assignment_id
2385 and aei_information_category = 'IE_ASG_OVERRIDE';
2386
2387 l_ppsn_override per_assignment_extra_info.aei_information1%type;
2388 --6633719
2389
2390 --Bug Fix 4317512
2391 /* Changed the cursor not to archive Period2 details,when one Prepayment is run for Period1 and
2392 Period2 togther and Archiver is run for Period1 */
2393
2394 CURSOR csr_assignment_actions(p_locking_action_id NUMBER) IS
2395 SELECT pay.locking_action_id pre_assignment_action_id, -- Bugfix 4567566
2396 pay.locked_action_id master_assignment_action_id,
2397 assact.assignment_id assignment_id,
2398 assact.payroll_action_id pay_payroll_action_id,
2399 paa.effective_date effective_date,
2400 ppaa.effective_date pre_effective_date,
2401 paa.date_earned date_earned,
2402 ptp.time_period_id time_period_id
2403 FROM pay_action_interlocks pre,
2404 pay_action_interlocks pay,
2405 pay_payroll_actions paa,
2406 pay_payroll_actions ppaa,
2407 pay_assignment_actions assact,
2408 pay_assignment_actions passact,
2409 per_time_periods ptp -- added to fetch correct time period id
2410 WHERE pre.locked_action_id = pay.locked_action_id
2411 AND pre.locking_action_id = p_locking_action_id
2412 AND pre.locked_action_id = assact.assignment_action_id
2413 AND assact.payroll_action_id = paa.payroll_action_id
2414 AND paa.action_type in ('R','Q')
2415 AND pay.locking_action_id = passact.assignment_action_id
2416 AND passact.payroll_action_id = ppaa.payroll_action_id
2417 AND ppaa.action_type IN ('P','U')
2418 AND assact.source_action_id IS NULL
2419 AND paa.payroll_id = ptp.payroll_id
2420 AND paa.date_earned between ptp.start_date and ptp.end_date
2421 ORDER BY pay.locked_action_id;
2422
2423 -- Bug Fix 3894307
2424 -- Changed the cursor to get latest child assignment action id
2425 /*CURSOR csr_child_actions(p_master_assignment_action NUMBER,
2426 p_payroll_action_id NUMBER,
2427 p_assignment_id NUMBER,
2428 p_effective_date DATE ) IS
2429 SELECT paa.assignment_action_id child_assignment_action_id,
2430 'S' run_type
2431 FROM pay_assignment_actions paa,
2432 pay_run_types_f prt
2433 WHERE paa.source_action_id = p_master_assignment_action
2434 AND paa.payroll_action_id = p_payroll_action_id
2435 AND paa.assignment_id = p_assignment_id
2436 AND paa.run_type_id = prt.run_type_id
2437 AND prt.run_method = 'S'
2438 AND p_effective_date BETWEEN
2439 prt.effective_start_date AND prt.effective_end_date
2440 UNION
2441 SELECT paa.assignment_action_id child_assignment_action_id,
2442 'NP' run_type
2443 FROM pay_assignment_actions paa
2444 WHERE paa.payroll_action_id = p_payroll_action_id
2445 AND paa.assignment_id = p_assignment_id
2446 AND paa.action_sequence = (SELECT MAX(paa1.action_sequence)
2447 FROM pay_assignment_actions paa1,
2448 pay_run_types_f prt1
2449 WHERE prt1.run_type_id = paa1.run_type_id
2450 AND prt1.run_method IN ('N','P')
2451 AND paa1.payroll_action_id = p_payroll_action_id
2452 AND paa1.assignment_id = p_assignment_id
2453 AND paa1.source_action_id = p_master_assignment_action
2454 AND p_effective_date BETWEEN
2455 prt1.effective_start_date AND prt1.effective_end_date);*/
2456 -- Bug Fix 3894307
2457 -- New Cursor
2458 CURSOR csr_child_actions(p_assignment_id NUMBER,
2459 p_effective_date DATE ) IS
2460 /*SELECT paa.assignment_action_id child_assignment_action_id,
2461 'S' run_type
2462 FROM pay_assignment_actions paa,
2463 pay_run_types_f prt
2464 WHERE paa.run_type_id = prt.run_type_id
2465 AND prt.run_method = 'S'
2466 AND p_effective_date BETWEEN prt.effective_start_date AND prt.effective_end_date
2467 AND paa.assignment_action_id = (SELECT /*+ USE_NL(paa, ppa)
2468 fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
2469 paa.assignment_action_id),16)) child_assignment_action_id
2470 FROM pay_assignment_actions paa,
2471 pay_payroll_actions ppa
2472 WHERE paa.assignment_id = p_assignment_id
2473 AND ppa.payroll_action_id = paa.payroll_action_id
2474 AND (paa.source_action_id is not null or ppa.action_type in ('I','V','B'))
2475 AND ppa.effective_date <= p_effective_date
2476 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
2477 AND paa.action_status = 'C')
2478 UNION */
2479 -- Bug Fix 4260031
2480 SELECT paa.assignment_action_id child_assignment_action_id,
2481 prt.run_method run_type
2482 FROM pay_assignment_actions paa,
2483 pay_run_types_f prt
2484 WHERE paa.run_type_id = prt.run_type_id
2485 AND prt.run_method IN ('N','P')
2486 AND p_effective_date BETWEEN prt.effective_start_date AND prt.effective_end_date
2487 AND paa.assignment_action_id = (SELECT /*+ USE_NL(paa, ppa) */
2488 fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
2489 paa.assignment_action_id),16)) child_assignment_action_id
2490 FROM pay_assignment_actions paa,
2491 pay_payroll_actions ppa
2492 WHERE paa.assignment_id = p_assignment_id
2493 AND ppa.payroll_action_id = paa.payroll_action_id
2494 AND (paa.source_action_id is not null or ppa.action_type in ('I','V'))
2495 AND ppa.effective_date between trunc(p_effective_date,'Y') and p_effective_date
2496 AND ppa.action_type in ('R', 'Q', 'I', 'V') -- Removed B as run type is not populated 4606580
2497 AND paa.action_status = 'C');
2498
2499 -- Bug Fix 3927328
2500 -- Bug Fix 4260031
2501 /*CURSOR csr_np_children (p_assignment_action_id NUMBER,
2502 p_payroll_action_id NUMBER,
2503 p_assignment_id NUMBER,
2504 p_effective_date DATE) IS
2505 SELECT paa.assignment_action_id np_assignment_action_id,
2506 prt.run_method
2507 FROM pay_assignment_actions paa,
2508 pay_run_types_f prt
2509 WHERE paa.source_action_id = p_assignment_action_id
2510 AND paa.payroll_action_id = p_payroll_action_id
2511 AND paa.assignment_id = p_assignment_id
2512 AND paa.run_type_id = prt.run_type_id
2513 AND prt.run_method IN ('N','P')
2514 AND p_effective_date BETWEEN
2515 prt.effective_start_date AND prt.effective_end_date;*/
2516
2517 -- Bug Fix 3927328 Changed Cursor
2518 /*CURSOR csr_np_children (p_assignment_id NUMBER,
2519 p_effective_date DATE) IS
2520 SELECT paa.assignment_action_id np_assignment_action_id,
2521 prt.run_method
2522 FROM pay_assignment_actions paa,
2523 pay_run_types_f prt
2524 WHERE paa.run_type_id = prt.run_type_id
2525 AND prt.run_method IN ('N','P')
2526 AND p_effective_date BETWEEN prt.effective_start_date AND prt.effective_end_date
2527 AND paa.assignment_action_id = (SELECT /*+ USE_NL(paa, ppa)
2528 fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
2529 paa.assignment_action_id),16))
2530 FROM pay_assignment_actions paa,
2531 pay_payroll_actions ppa
2532 WHERE paa.assignment_id = p_assignment_id
2533 AND ppa.payroll_action_id = paa.payroll_action_id
2534 AND (paa.source_action_id is not null or ppa.action_type in ('I','V','B'))
2535 AND ppa.effective_date between trunc(p_effective_date,'Y') and p_effective_date
2536 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
2537 AND paa.action_status = 'C'); */
2538
2539
2540 l_actid NUMBER;
2541 l_action_context_id NUMBER;
2542 l_action_info_id NUMBER(15);
2543 l_assignment_action_id NUMBER;
2544 l_business_group_id NUMBER;
2545 l_chunk_number NUMBER;
2546 l_date_earned DATE;
2547 l_ovn NUMBER;
2548 l_person_id NUMBER;
2549 l_record_count NUMBER;
2550 l_child_count NUMBER;
2551 l_salary VARCHAR2(10);
2552 l_sequence NUMBER;
2553
2554 l_proc VARCHAR2(50) := g_package || 'archive_code';
2555
2556 BEGIN
2557 -- hr_utility.trace_on(null,'test123');
2558 hr_utility.set_location('Entering '|| l_proc,10);
2559
2560 hr_utility.set_location('Step '|| l_proc,20);
2561 hr_utility.set_location('p_assactid = ' || p_assactid,20);
2562
2563 -- retrieve the chunk number for the current assignment action
2564
2565 SELECT paa.chunk_number
2566 INTO l_chunk_number
2567 FROM pay_assignment_actions paa
2568 WHERE paa.assignment_action_id = p_assactid;
2569
2570 l_action_context_id := p_assactid;
2571
2572 l_record_count := 0;
2573
2574 FOR csr_rec IN csr_assignment_actions(p_assactid)
2575
2576 LOOP
2577
2578 hr_utility.set_location('csr_rec.master_assignment_action_id = ' || csr_rec.master_assignment_action_id,20);
2579 hr_utility.set_location('csr_rec.pre_assignment_action_id = ' || csr_rec.pre_assignment_action_id,20);
2580 hr_utility.set_location('csr_rec.assignment_id = ' || csr_rec.assignment_id,20);
2581 hr_utility.set_location('csr_rec.date_earned = ' ||to_char( csr_rec.date_earned,'dd-mon-yyyy'),20);
2582 hr_utility.set_location('csr_rec.pre_effective_date = ' ||to_char( csr_rec.pre_effective_date,'dd-mon-yyyy'),20);
2583 hr_utility.set_location('csr_rec.time_period_id = ' || csr_rec.time_period_id,20);
2584
2585 IF l_record_count = 0
2586
2587 THEN
2588
2589 pay_ie_legislative_archive.archive_employee_details (
2590 p_assactid => p_assactid
2591 , p_assignment_id => csr_rec.assignment_id
2592 , p_curr_pymt_ass_act_id => csr_rec.pre_assignment_action_id -- prepayment assignment_action_id
2593 , p_date_earned => csr_rec.date_earned -- payroll date_earned
2594 , p_effective_date => csr_rec.effective_date -- payroll effective_date Added for Bug Fix 4260031
2595 , p_curr_pymt_eff_date => csr_rec.pre_effective_date -- prepayment effective_date
2596 , p_time_period_id => csr_rec.time_period_id -- payroll time_period_id
2597 , p_record_count => l_record_count );
2598
2599 END IF;
2600
2601 -- Bug Fix 3894307
2602 /*FOR csr_child_rec IN csr_child_actions(csr_rec.master_assignment_action_id,
2603 csr_rec.pay_payroll_action_id,
2604 csr_rec.assignment_id,
2605 csr_rec.effective_date)*/
2606
2607 FOR csr_child_rec IN csr_child_actions(csr_rec.assignment_id,
2608 csr_rec.effective_date)
2609
2610 LOOP
2611
2612 -- create additional archive assignment actions and interlocks
2613
2614 SELECT pay_assignment_actions_s.NEXTVAL
2615 INTO l_actid
2616 FROM dual;
2617
2618 hr_utility.set_location('csr_child_rec.run_type = ' || csr_child_rec.run_type,30);
2619 hr_utility.set_location('csr_rec.master_assignment_action_id = ' || csr_rec.master_assignment_action_id,30);
2620
2621 hr_nonrun_asact.insact(
2622 lockingactid => l_actid
2623 , assignid => csr_rec.assignment_id
2624 , pactid => g_archive_pact
2625 , chunk => l_chunk_number
2626 , greid => g_tax_unit_id
2627 , prepayid => NULL
2628 , status => 'C'
2629 , source_act => p_assactid);
2630
2631 -- Bug Fix 4260031
2632
2633 /*
2634 IF csr_child_rec.run_type = 'S'
2635
2636 THEN
2637
2638 hr_utility.set_location('creating lock3 ' || l_actid || ' to ' || csr_child_rec.child_assignment_action_id,30);
2639
2640 hr_nonrun_asact.insint(
2641 lockingactid => l_actid
2642 , lockedactid => csr_child_rec.child_assignment_action_id);
2643
2644 l_action_context_id := l_actid;
2645
2646 IF l_record_count = 0
2647
2648 THEN
2649
2650 pay_ie_legislative_archive.archive_employee_details(
2651 p_assactid => l_action_context_id
2652 , p_assignment_id => csr_rec.assignment_id
2653 , p_curr_pymt_ass_act_id => csr_rec.pre_assignment_action_id -- prepayment assignment_action_id
2654 , p_date_earned => csr_rec.date_earned -- payroll date_earned
2655 , p_effective_date => csr_rec.effective_date -- payroll effective_date Added for Bug Fix 4260031
2656 , p_curr_pymt_eff_date => csr_rec.pre_effective_date -- prepayment effective_date
2657 , p_time_period_id => csr_rec.time_period_id -- payroll time_period_id
2658 , p_record_count => l_record_count);
2659
2660 pay_ie_legislative_archive.archive_ie_employee_details (
2661 p_assactid => l_action_context_id
2662 , p_assignment_id => csr_rec.assignment_id
2663 , p_curr_pymt_ass_act_id => csr_child_rec.child_assignment_action_id
2664 , p_effective_date => csr_rec.effective_date);
2665
2666
2667 END IF;
2668
2669 pay_ie_legislative_archive.get_element_info (
2670 p_action_context_id => l_action_context_id
2671 , p_assignment_id => csr_rec.assignment_id
2672 , p_child_assignment_action => csr_child_rec.child_assignment_action_id
2673 , p_effective_date => csr_rec.effective_date
2674 , p_record_count => l_record_count
2675 , p_run_method => 'S');
2676
2677 END IF;
2678 */
2679
2680 --6633719
2681 l_ppsn_override := NULL;
2682 OPEN csr_ppsn_override(csr_rec.assignment_id);
2683 FETCH csr_ppsn_override INTO l_ppsn_override;
2684 CLOSE csr_ppsn_override;
2685
2686 hr_utility.set_location('l_ppsn_override = ' || l_ppsn_override,35);
2687 --6633719
2688
2689 IF csr_child_rec.run_type in ('N','P')
2690
2691 THEN
2692
2693 l_child_count := 0;
2694
2695
2696 -- Bug Fix 3927328
2697 /*FOR csr_np_rec IN csr_np_children(csr_rec.master_assignment_action_id,
2698 csr_rec.pay_payroll_action_id,
2699 csr_rec.assignment_id,
2700 csr_rec.effective_date)*/
2701
2702 /*FOR csr_np_rec IN csr_np_children(csr_rec.assignment_id,
2703 csr_rec.effective_date)
2704
2705 LOOP*/
2706
2707 hr_utility.set_location('creating lock4 ' || l_actid || ' to ' || csr_child_rec.child_assignment_action_id,30);
2708
2709 hr_nonrun_asact.insint(
2710 lockingactid => l_actid
2711 , lockedactid => csr_child_rec.child_assignment_action_id);
2712
2713 IF l_child_count = 0
2714
2715 THEN
2716
2717 pay_ie_legislative_archive.archive_ie_employee_details (
2718 p_assactid => l_action_context_id
2719 , p_assignment_id => csr_rec.assignment_id
2720 , p_curr_pymt_ass_act_id => csr_child_rec.child_assignment_action_id
2721 , p_effective_date => csr_rec.effective_date
2722 , p_ppsn_override => l_ppsn_override); --6633719
2723
2724 END IF;
2725
2726 pay_ie_legislative_archive.get_element_info (
2727 p_action_context_id => l_action_context_id
2728 , p_assignment_id => csr_rec.assignment_id
2729 , p_child_assignment_action => csr_child_rec.child_assignment_action_id
2730 , p_effective_date => csr_rec.effective_date
2731 , p_record_count => l_record_count
2732 , p_run_method => csr_child_rec.run_type);
2733
2734 l_child_count := l_child_count + 1;
2735
2736
2737 --END LOOP;
2738
2739 END IF;
2740
2741 -- Both User and Statutory Balances are archived for all Separate Payment assignment actions
2742 -- and the last (i.e. highest action_sequence) Process Separately assignment action
2743 -- (EMEA BALANCES)
2744
2745 -- archive user balances
2746
2747 hr_utility.set_location('Archive User Balances - Starting',60);
2748 hr_utility.set_location('g_max_user_balance_index = '|| g_max_user_balance_index,60);
2749
2750 FOR l_index IN 1 .. g_max_user_balance_index
2751
2752 LOOP
2753
2754 pay_ie_legislative_archive.process_balance (
2755 p_action_context_id => l_action_context_id
2756 , p_assignment_id => csr_rec.assignment_id
2757 , p_source_id => csr_child_rec.child_assignment_action_id
2758 , p_effective_date => csr_rec.effective_date
2759 , p_balance => g_user_balance_table(l_index).balance_name
2760 , p_dimension => g_user_balance_table(l_index).database_item_suffix
2761 , p_defined_bal_id => g_user_balance_table(l_index).defined_balance_id
2762 , p_record_count => l_record_count
2763 , p_tax_unit_id => g_tax_unit_id);
2764
2765 END LOOP;
2766
2767 hr_utility.set_location('Archive User Balances - Complete',60);
2768
2769 -- archive statutory balances
2770
2771 hr_utility.set_location('Archive Statutory Balances - Starting',70);
2772 hr_utility.set_location('g_max_statutory_balance_index = '|| g_max_statutory_balance_index,70);
2773
2774 FOR l_index IN 1 .. g_max_statutory_balance_index
2775
2776 LOOP
2777
2778 hr_utility.set_location('l_index = ' || l_index,70);
2779 --6633719
2780 IF g_statutory_balance_table(l_index).database_item_suffix <> '_PER_PAYE_REF_PPSN_YTD'
2781 OR (l_ppsn_override IS NOT NULL
2782 AND g_statutory_balance_table(l_index).database_item_suffix = '_PER_PAYE_REF_PPSN_YTD')
2783 THEN
2784 --6633719
2785 hr_utility.set_location('AssignmentID = ' || csr_rec.assignment_id,70);
2786 hr_utility.set_location('suffix = '||g_statutory_balance_table(l_index).database_item_suffix,70);
2787 hr_utility.set_location('Balance Name = '||g_statutory_balance_table(l_index).balance_name,70);
2788
2789 pay_ie_legislative_archive.process_balance (
2790 p_action_context_id => l_action_context_id
2791 , p_assignment_id => csr_rec.assignment_id
2792 , p_source_id => csr_child_rec.child_assignment_action_id
2793 , p_effective_date => csr_rec.effective_date
2794 , p_balance => g_statutory_balance_table(l_index).balance_name
2795 , p_dimension => g_statutory_balance_table(l_index).database_item_suffix
2796 , p_defined_bal_id => g_statutory_balance_table(l_index).defined_balance_id
2797 , p_record_count => l_record_count
2798 , p_tax_unit_id => g_tax_unit_id);
2799 END IF;
2800
2801 END LOOP;
2802
2803 hr_utility.set_location('Archive Statutory Balances - Complete',70);
2804
2805 END LOOP; -- child assignment actions
2806
2807 l_record_count := l_record_count + 1;
2808
2809
2810 END LOOP;
2811
2812 hr_utility.set_location('Leaving '|| l_proc,80);
2813
2814 END archive_code;
2815
2816 Procedure ARCHIVE_DEINIT(p_payroll_action_id IN NUMBER) IS
2817
2818
2819 l_proc CONSTANT VARCHAR2(50):= g_package||'archive_deinit';
2820
2821 l_archived NUMBER(1);
2822 l_ovn NUMBER(15);
2823 l_request_id NUMBER;
2824 l_action_info_id NUMBER(15);
2825 l_business_group_id NUMBER;
2826 g_paye_ref VARCHAR2(10);
2827
2828 CURSOR csr_check_archived(p_pact_id NUMBER) IS
2829 SELECT 1
2830 FROM DUAL
2831 WHERE EXISTS (SELECT NULL
2832 FROM pay_action_information pai
2833 WHERE pai.action_context_id = p_pact_id
2834 AND pai.action_context_type = 'PA'
2835 AND rownum = 1
2836 );
2837 CURSOR csr_input_value_id(p_element_name CHAR,
2838 p_value_name CHAR) IS
2839 SELECT pet.element_type_id,
2840 piv.input_value_id
2841 FROM pay_input_values_f piv,
2842 pay_element_types_f pet
2843 WHERE piv.element_type_id = pet.element_type_id
2844 AND pet.legislation_code = 'IE'
2845 AND pet.element_name = p_element_name
2846 AND piv.name = p_value_name;
2847
2848 CURSOR csr_payrolls (p_payroll_id NUMBER,
2849 p_consolidation_set_id NUMBER,
2850 p_effective_date DATE) IS
2851 SELECT ppf.payroll_id
2852 FROM pay_all_payrolls_f ppf
2853 WHERE ppf.consolidation_set_id = p_consolidation_set_id
2854 AND ppf.payroll_id = NVL(p_payroll_id,ppf.payroll_id)
2855 AND p_effective_date BETWEEN
2856 ppf.effective_start_date AND ppf.effective_end_date;
2857
2858 --4369280
2859 -- Changed to handle new employer architecture
2860 CURSOR csr_payroll_info(p_pact_id NUMBER,
2861 p_payroll_id NUMBER,
2862 p_consolidation_id NUMBER,
2863 p_start_date DATE,
2864 p_end_date DATE,
2865 g_paye_ref VARCHAR2) IS
2866 SELECT pact.payroll_action_id payroll_action_id,
2867 pact.effective_date effective_date,
2868 pact.date_earned date_earned,
2869 pact.payroll_id,
2870 org.org_information1 tax_details_ref_no,
2871 org.org_information2 employer_paye_ref_no,
2872 ppf.payroll_name payroll_name,
2873 ppf.period_type period_type,
2874 pact.pay_advice_message payroll_message
2875 FROM pay_payrolls_f ppf,
2876 pay_payroll_actions pact,
2877 hr_soft_coding_keyflex flex,
2878 hr_organization_information org
2879 WHERE ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
2880 AND org.org_information_context = 'IE_EMPLOYER_INFO'
2881 AND org.organization_id = flex.segment4
2882 -- AND ppf.business_group_id = org.organization_id
2883 AND pact.payroll_id = ppf.payroll_id
2884 AND pact.effective_date BETWEEN
2885 ppf.effective_start_date AND ppf.effective_end_date
2886 AND pact.payroll_id = NVL(p_payroll_id,pact.payroll_id)
2887 AND ppf.consolidation_set_id = p_consolidation_id
2888 AND (pact.action_type = 'P' OR
2889 pact.action_type = 'U')
2890 AND pact.action_status = 'C'
2891 AND exists (SELECT NULL
2892 FROM pay_assignment_actions paa,
2893 pay_action_interlocks pai,
2894 pay_assignment_actions paa_arc
2895 WHERE pai.locked_action_id = paa.assignment_action_id
2896 AND pai.locking_action_id = paa_arc.assignment_action_id
2897 AND paa_arc.payroll_action_id = p_pact_id
2898 AND paa.payroll_action_id = pact.payroll_action_id)
2899 AND NOT EXISTS (SELECT NULL
2900 FROM pay_action_information pai
2901 WHERE pai.action_context_id = pact.payroll_action_id
2902 AND pai.action_context_type = 'PA'
2903 AND pai.action_information_category = 'EMEA PAYROLL INFO'
2904 AND pai.action_information5 = g_paye_ref ) -- Bug fix 4001540
2905
2906 -- Added for bug fix 3567562 to restrict details fetched based on PAYE Reference.
2907 -- AND org.org_information2 = flex.segment3
2908 AND org.org_information2 = g_paye_ref;
2909
2910
2911 -- cursor csr_get_org_tax_address
2912 CURSOR csr_get_org_tax_address( c_consolidation_set PAY_CONSOLIDATION_SETS.CONSOLIDATION_SET_ID%type
2913 , g_paye_ref HR_ORGANIZATION_INFORMATION.ORG_INFORMATION2%type
2914 ) IS
2915 SELECT hrl.address_line_1 employer_tax_addr1
2916 ,hrl.address_line_2 employer_tax_addr2
2917 ,hrl.address_line_3 employer_tax_addr3
2918 ,org_info.org_information4 employer_tax_contact
2919 ,hrl.telephone_number_1 employer_tax_ref_phone
2920 --,org_all.name employer_tax_rep_name
2921 --Added for bug fix 3567562,mofified source of Employer statutory reporting name
2922 ,org_all.name employer_tax_rep_name
2923 ,pcs.business_group_id business_group_id
2924 --
2925 FROM hr_all_organization_units org_all
2926 ,hr_organization_information org_info
2927 ,pay_consolidation_sets pcs
2928 ,hr_locations_all hrl
2929 WHERE pcs.consolidation_set_id = c_consolidation_set
2930 AND org_all.business_group_id = pcs.business_group_id
2931 AND org_info.organization_id = org_all.organization_id
2932 --Changed to handle new Employer architecture(4369280)
2933 AND org_info.org_information_context = 'IE_EMPLOYER_INFO'
2934
2935 AND org_all.location_id = hrl.location_id (+)
2936 -- Added for bug fix 3567562 to restrict details fetched based on PAYE Reference.
2937 AND org_info.org_information2 = g_paye_ref ;
2938
2939
2940 --
2941 CURSOR csr_payroll_mesg (p_payroll_id NUMBER,
2942 p_start_date DATE,
2943 p_end_date DATE) IS
2944 SELECT pact.payroll_action_id payroll_action_id,
2945 pact.effective_date effective_date,
2946 pact.date_earned date_earned,
2947 pact.pay_advice_message payroll_message
2948 FROM pay_payrolls_f ppf,
2949 pay_payroll_actions pact
2950 WHERE pact.payroll_id = ppf.payroll_id
2951 AND pact.effective_date BETWEEN
2952 ppf.effective_start_date AND ppf.effective_end_date
2953 AND pact.payroll_id = p_payroll_id
2954 AND pact.effective_date BETWEEN
2955 p_start_date AND p_end_date
2956 AND (pact.action_type = 'R' OR
2957 pact.action_type = 'Q')
2958 AND pact.action_status = 'C'
2959 AND NOT EXISTS (SELECT NULL
2960 FROM pay_action_information pai
2961 WHERE pai.action_context_id = pact.payroll_action_id
2962 AND pai.action_context_type = 'PA'
2963 AND pai.action_information_category = 'EMPLOYEE OTHER INFORMATION');
2964 --
2965 l_assignment_set_id NUMBER;
2966 l_bg_id NUMBER;
2967 l_canonical_end_date DATE;
2968 l_canonical_start_date DATE;
2969 l_consolidation_set NUMBER;
2970 l_end_date VARCHAR2(30);
2971 l_legislation_code VARCHAR2(30) := 'IE';
2972 l_payroll_id NUMBER;
2973 l_start_date VARCHAR2(30);
2974 l_tax_period_no VARCHAR2(30);
2975 l_error varchar2(1) ;
2976
2977
2978 BEGIN
2979
2980 --hr_utility.trace_on(null,'IEPS');
2981 hr_utility.set_location('Entering ' || l_proc,10);
2982
2983 l_archived := 0;
2984
2985 OPEN csr_check_archived(p_payroll_action_id);
2986 FETCH csr_check_archived INTO l_archived;
2987 CLOSE csr_check_archived;
2988
2989 IF l_archived = 0 THEN
2990
2991 pay_ie_legislative_archive.get_parameters (
2992 p_payroll_action_id => p_payroll_action_id
2993 , p_token_name => 'PAYROLL'
2994 , p_token_value => l_payroll_id);
2995
2996 pay_ie_legislative_archive.get_parameters (
2997 p_payroll_action_id => p_payroll_action_id
2998 , p_token_name => 'CONSOLIDATION'
2999 , p_token_value => l_consolidation_set);
3000
3001 pay_ie_legislative_archive.get_parameters (
3002 p_payroll_action_id => p_payroll_action_id
3003 , p_token_name => 'ASSIGNMENT_SET'
3004 , p_token_value => l_assignment_set_id);
3005
3006 pay_ie_legislative_archive.get_parameters (
3007 p_payroll_action_id => p_payroll_action_id
3008 , p_token_name => 'START_DATE'
3009 , p_token_value => l_start_date);
3010
3011 pay_ie_legislative_archive.get_parameters (
3012 p_payroll_action_id => p_payroll_action_id
3013 , p_token_name => 'END_DATE'
3014 , p_token_value => l_end_date);
3015
3016 pay_ie_legislative_archive.get_parameters (
3017 p_payroll_action_id => p_payroll_action_id
3018 , p_token_name => 'BG_ID'
3019 , p_token_value => l_bg_id);
3020
3021 hr_utility.set_location('Step ' || l_proc,20);
3022 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
3023 hr_utility.set_location('l_start_date = ' || l_start_date,20);
3024 hr_utility.set_location('l_end_date = ' || l_end_date,20);
3025
3026 l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
3027 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
3028
3029 -- archive EMEA PAYROLL INFO for each prepayment run identified
3030
3031 hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
3032 hr_utility.set_location('l_consolidation_set = ' || l_consolidation_set,20);
3033 hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
3034 hr_utility.set_location('l_canonical_end_date = ' || l_canonical_end_date,20);
3035
3036 --Added for bug fix 3567562, call to the procedure to get the PAYE reference value
3037 get_paye_reference (l_consolidation_set,g_paye_ref,l_bg_id,l_canonical_start_date,l_canonical_end_date,l_error);
3038
3039 if l_error ='Y' then
3040 NULL;
3041 else
3042
3043
3044 FOR tax_info_rec IN csr_get_org_tax_address (l_consolidation_set,g_paye_ref) LOOP
3045 --
3046 pay_action_information_api.create_action_information (
3047 p_action_information_id => l_action_info_id
3048 , p_action_context_id => p_payroll_action_id
3049 , p_action_context_type => 'PA'
3050 , p_object_version_number => l_ovn
3051 , p_action_information_category => 'ADDRESS DETAILS'
3052 , p_action_information1 => tax_info_rec.business_group_id
3053 , p_action_information5 => tax_info_rec.employer_tax_addr1
3054 , p_action_information6 => tax_info_rec.employer_tax_addr2
3055 , p_action_information7 => tax_info_rec.employer_tax_addr3
3056 , p_action_information14 => 'IE Employer Tax Address'
3057 , p_action_information26 => tax_info_rec.employer_tax_contact
3058 , p_action_information27 => tax_info_rec.employer_tax_ref_phone
3059 , p_action_information28 => tax_info_rec.employer_tax_rep_name);
3060 --
3061 END LOOP;
3062
3063
3064
3065 g_max_user_balance_index := 0;
3066
3067 pay_ie_legislative_archive.get_eit_definitions (
3068 p_pactid => p_payroll_action_id
3069 , p_business_group_id => l_bg_id
3070 , p_payroll_pact => NULL
3071 , p_effective_date => l_canonical_start_date
3072 , p_eit_context => g_balance_context
3073 , p_archive => 'Y');
3074
3075 pay_ie_legislative_archive.get_eit_definitions (
3076 p_pactid => p_payroll_action_id
3077 , p_business_group_id => l_bg_id
3078 , p_payroll_pact => NULL
3079 , p_effective_date => l_canonical_start_date
3080 , p_eit_context => g_element_context
3081 , p_archive => 'Y');
3082
3083 pay_ie_legislative_archive.setup_element_definitions (
3084 p_pactid => p_payroll_action_id
3085 , p_payroll_pact => NULL
3086 , p_business_group_id => l_bg_id
3087 , p_effective_date => l_canonical_start_date);
3088
3089 FOR rec_payrolls in csr_payrolls(l_payroll_id,
3090 l_consolidation_set,
3091 l_canonical_end_date)
3092 LOOP
3093
3094 hr_utility.set_location('Calling arch_pay_action_level_data',25);
3095 --
3096
3097 pay_emp_action_arch.arch_pay_action_level_data (
3098 p_payroll_action_id => p_payroll_action_id
3099 , p_payroll_id => rec_payrolls.payroll_id
3100 , p_effective_date => l_canonical_end_date);
3101
3102 --
3103 END LOOP;
3104
3105 FOR rec_payroll_info in csr_payroll_info(p_payroll_action_id,
3106 l_payroll_id,
3107 l_consolidation_set,
3108 l_canonical_start_date,
3109 l_canonical_end_date,
3110 g_paye_ref)
3111
3112 LOOP
3113 pay_balance_pkg.set_context('PAYROLL_ACTION_ID'
3114 , rec_payroll_info.payroll_action_id);
3115 hr_utility.set_location('rec_payroll_info.payroll_action_id = ' || rec_payroll_info.payroll_action_id,30);
3116 hr_utility.set_location('rec_payroll_info.tax_details_ref = ' || rec_payroll_info.tax_details_ref_no,30);
3117 hr_utility.set_location('rec_payroll_info.employers_paye_ref_no = ' || rec_payroll_info.employer_paye_ref_no,30);
3118
3119 hr_utility.set_location('Archiving EMEA PAYROLL INFO',30);
3120
3121 pay_action_information_api.create_action_information (
3122 p_action_information_id => l_action_info_id
3123 , p_action_context_id => p_payroll_action_id
3124 , p_action_context_type => 'PA'
3125 , p_object_version_number => l_ovn
3126 , p_effective_date => rec_payroll_info.effective_date
3127 , p_source_id => NULL
3128 , p_source_text => NULL
3129 , p_action_information_category => 'EMEA PAYROLL INFO'
3130 , p_action_information1 => rec_payroll_info.payroll_action_id
3131 , p_action_information2 => rec_payroll_info.payroll_id
3132 , p_action_information3 => l_consolidation_set
3133 , p_action_information4 => rec_payroll_info.tax_details_ref_no
3134 , p_action_information5 => rec_payroll_info.employer_paye_ref_no
3135 , p_action_information6 => NULL);
3136
3137 END LOOP;
3138
3139
3140 -- The Payroll level message is archived in the generic archive structure
3141 -- EMPLOYEE OTHER INFORMATION
3142
3143 FOR rec_payroll_msg in csr_payroll_mesg(l_payroll_id,
3144 l_canonical_start_date,
3145 l_canonical_end_date)
3146
3147 LOOP
3148
3149 IF rec_payroll_msg.payroll_message IS NOT NULL
3150 THEN
3151 --
3152 pay_action_information_api.create_action_information (
3153 p_action_information_id => l_action_info_id
3154 , p_action_context_id => p_payroll_action_id
3155 , p_action_context_type => 'PA'
3156 , p_object_version_number => l_ovn
3157 , p_effective_date => rec_payroll_msg.effective_date
3158 , p_source_id => NULL
3159 , p_source_text => NULL
3160 , p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
3161 , p_action_information1 => rec_payroll_msg.payroll_action_id
3162 , p_action_information2 => 'MESG'
3163 , p_action_information3 => NULL
3164 , p_action_information4 => NULL
3165 , p_action_information5 => NULL
3166 , p_action_information6 => rec_payroll_msg.payroll_message);
3167
3168 END IF;
3169
3170 END LOOP;
3171
3172 hr_utility.set_location('Leaving ' || l_proc,40);
3173 end if;
3174 END IF;
3175 END ARCHIVE_DEINIT;
3176
3177 END;