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