1 PACKAGE BODY pay_apac_payslip_archive AS
2 /* $Header: pyapacps.pkb 120.4 2007/10/15 06:19:29 jalin noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_apac_payslip_archive
21
22 Description :This is a common package to archive the payroll
23 action level data for APAC countries SS payslip.
24 Different procedures defined are called by the
25 APAC countries legislative Payslip Data Archiver.
26
27
28
29
30 Change List
31 -----------
32 Date Name Vers Bug No Description
33 ----------- ---------- ------ ------- --------------------------
34 22-Apr-2002 kaverma 115.0 Created.
35 22-Apr-2002 kaverma 115.1 2306309 Changes After code review comments
36 23-Apr-2002 kaverma 115.2 2306309 Changes After code review comment
37 24-Apr-2002 kaverma 115.4 Added p_archive parameter to get_eit_definitions
38 02-May-2002 kaverma 115.5 Added procedure range_code
39 27-Aug-2002 srrajago 115.6 2518997 Call to set the context for 'TAX UNIT ID' included in archive_user_balances.
40 09-Sep-2002 apunekar 115.7 Call to set the context for 'SOURCE ID' included in archive_user_balances.
41 22-Oct-2002 srrajago 115.8 2613475 'EMEA ELEMENT DEFINITION' and 'EMEA BALANCE DEFINITON' are now archived
42 only once per archive run to improve performance. (In proc process_eit,
43 cursor csr_payroll_info removed and values passed to parameters
44 p_pre_payroll_action_id and p_pre_effective_date changed).
45 03-Nov-2002 Ragovind 115.9 2689226 Added NOCOPY for function get_legislative_parameters.
46 04-Nov-2003 Puchil 115.10 3228928 Added a new cursor csr_element_num_val to sum all the element values for
47 numeric values and archive individual values for other types of input.
48 >Changed hr_utility.trace to execute conditionally.
49 19-Apr-2004 bramajey 115.5 3578040 Renamed procedure range_code to
50 archive_payroll_level_data
51 04-May-2004 bramajey 115.6 3604206 Added code to convert numeric data to
52 canonical using fnd_number.number_to_canonical while archiving.
53 Reverted back changes done for 3578040
54 29-Jun-2004 punmehta 115.7 3731940 Added source_action_id check
55 02-Jul-2004 punmehta 115.8 3731940 Modified source_action_id check
56 02-Jul-2004 punmehta 115.9 3731940 Modified for GSCC warnings
57 12-Dec-2006 aaagarwa 115.10 5048802 Added deinitialization_code
58 05-Oct-2007 jalin 115.11 6471802 Reset l_element_archived to N in the loop in archive_user_elements procedure
59 11-Oct-2007 jalin 115.12 6486660 Added NOT null check for l_sum_value in archive_user_elements
60 15-Oct-2007 jalin 115.13 6486660 Changed calling function pay_in_utils to hr_utility
61
62 *******************************************************************/
63
64 /*Global variable to enable trace conditionally*/
65 g_debug boolean;
66
67
68 /*********************************************************************
69 Name : get_eit_definitions
70 Purpose : Archives the EIT definition details for user configurable
71 balances as well as user configurable elements.
72 p_archive is flag for archival to happen.
73 *********************************************************************/
74
75 PROCEDURE get_eit_definitions(p_payroll_action_id IN NUMBER,
76 p_business_group_id IN NUMBER,
77 p_pre_payroll_action_id IN NUMBER,
78 p_pre_effective_date IN DATE,
79 p_archive IN VARCHAR2)
80
81 IS
82
83
84 -- Cursor to get the declared EIT definitions
85
86 CURSOR csr_eit_values(p_business_group_id NUMBER)
87 IS
88 SELECT org.org_information1,
89 org.org_information2,
90 org.org_information3,
91 org.org_information4,
92 org.org_information5,
93 org.org_information6,
94 org.org_information7
95 FROM hr_organization_information_v org
96 WHERE org.org_information_context = pay_apac_payslip_archive.g_bg_context
97 AND org.organization_id = p_business_group_id;
98
99
100 -- Cursor to fetch the balance name delcared at EIT and definded balance id
101
102 CURSOR csr_balance_name(p_balance_type_id NUMBER,
103 p_balance_dimension_id NUMBER)
104 IS
105 SELECT nvl(pbttl.reporting_name,pbttl.balance_name),
106 pbd.database_item_suffix,
107 pdb.legislation_code,
108 pdb.defined_balance_id
109 FROM pay_balance_types_tl pbttl,
110 pay_balance_dimensions pbd,
111 pay_defined_balances pdb
112 WHERE pdb.balance_type_id = pbttl.balance_type_id
113 AND pdb.balance_dimension_id = pbd.balance_dimension_id
114 AND pbttl.balance_type_id = p_balance_type_id
115 AND pbd.balance_dimension_id = p_balance_dimension_id
116 AND pbttl.language = userenv('LANG');
117
118
119
120 -- Cursor to get the element name declared at EIT
121
122 CURSOR csr_eit_element_name(p_element_type_id NUMBER,
123 p_effective_date DATE)
124 IS
125 SELECT nvl(pettl.reporting_name,pettl.element_name)
126 FROM pay_element_types_f_tl pettl,
127 pay_element_types_f pet
128 WHERE pet.element_type_id = p_element_type_id
129 AND pettl.element_type_id = pet.element_type_id
130 AND pettl.language = userenv('LANG')
131 AND p_effective_date BETWEEN pet.effective_start_date
132 AND pet.effective_end_date;
133
134
135 -- Cursor to get the Input Value Name and Unit of Measure
136
137 CURSOR csr_input_value_uom(p_input_value_id NUMBER,
138 p_effective_date DATE)
139 IS
140 SELECT piv.uom,
141 pivtl.name
142 FROM pay_input_values_f piv,
143 pay_input_values_f_tl pivtl
144 WHERE piv.input_value_id = p_input_value_id
145 AND pivtl.input_value_id = piv.input_value_id
146 AND pivtl.language = userenv('LANG')
147 AND p_effective_date BETWEEN piv.effective_start_date
148 AND piv.effective_end_date;
149
150
151 l_action_info_id NUMBER;
152 l_index NUMBER := 0;
153 l_ovn NUMBER;
154 l_uom pay_input_values_f.uom%TYPE;
155 l_context VARCHAR2(30);
156 l_element_index PLS_INTEGER :=0;
157 l_balance_index PLS_INTEGER :=0;
158 l_element_type_id NUMBER;
159 l_element_name pay_element_types_f.reporting_name%TYPE;
160 l_input_value_id NUMBER;
161 l_element_narrative VARCHAR2(150);
162 l_input_value_name pay_input_values_f.name%TYPE;
163
164
165 BEGIN
166 g_debug := hr_utility.debug_enabled;
167 IF g_debug THEN
168 hr_utility.trace('Entering procedure get_eit_definitions');
169 END IF;
170
171 FOR csr_eit_rec IN csr_eit_values(p_business_group_id)
172
173 LOOP
174
175 l_context := csr_eit_rec.org_information1;
176
177 IF g_debug THEN
178 hr_utility.trace(' For context : l_context ........:'||l_context);
179 END IF;
180
181 IF (l_context = pay_apac_payslip_archive.g_balance_context) THEN
182
183 l_balance_index := l_balance_index+1;
184
185 g_user_balance_table(l_balance_index).balance_type_id := csr_eit_rec.org_information4;
186 g_user_balance_table(l_balance_index).balance_dimension_id := csr_eit_rec.org_information5;
187 g_user_balance_table(l_balance_index).balance_narrative := csr_eit_rec.org_information7;
188
189 OPEN csr_balance_name(g_user_balance_table(l_balance_index).balance_type_id
190 ,g_user_balance_table(l_balance_index).balance_dimension_id);
191 FETCH csr_balance_name
192 INTO g_user_balance_table(l_balance_index).balance_name,
193 g_user_balance_table(l_balance_index).database_item_suffix,
194 g_user_balance_table(l_balance_index).legislation_code,
195 g_user_balance_table(l_balance_index).defined_balance_id;
196
197 CLOSE csr_balance_name;
198
199
200 -- If user lefts the display name blank for balances then display name
201 -- will be 'balance reporting name || 'dimension name'
202
203 IF csr_eit_rec.org_information7 IS NULL THEN
204 g_user_balance_table(l_balance_index).balance_narrative:= g_user_balance_table(l_balance_index).balance_name ||' '|| g_user_balance_table(l_balance_index).database_item_suffix;
205 END IF;
206
207 IF g_debug THEN
208 hr_utility.trace('Archiving the user configured balances.......');
209 END IF;
210
211 IF p_archive = 'Y' THEN
212
213 pay_action_information_api.create_action_information
214 ( p_action_information_id => l_action_info_id
215 , p_action_context_id => p_payroll_action_id
216 , p_action_context_type => 'PA'
217 , p_object_version_number => l_ovn
218 , p_effective_date => p_pre_effective_date
219 , p_source_id => NULL
220 , p_source_text => NULL
221 , p_action_information_category => 'EMEA BALANCE DEFINITION'
222 , p_action_information1 => p_pre_payroll_action_id
223 , p_action_information2 => g_user_balance_table(l_balance_index).defined_balance_id
224 , p_action_information4 => g_user_balance_table(l_balance_index).balance_narrative
225 );
226 END IF;
227
228 END IF; -- l_context = pay_apac_payslip_archive.g_balance_context
229
230 IF ( l_context = pay_apac_payslip_archive.g_element_context ) THEN
231
232 l_element_type_id := csr_eit_rec.org_information2;
233 l_input_value_id := csr_eit_rec.org_information3;
234 l_element_narrative := csr_eit_rec.org_information7;
235
236 OPEN csr_eit_element_name(csr_eit_rec.org_information2 , p_pre_effective_date);
237 FETCH csr_eit_element_name INTO l_element_name;
238 CLOSE csr_eit_element_name;
239
240
241 OPEN csr_input_value_uom(l_input_value_id , p_pre_effective_date);
242 FETCH csr_input_value_uom INTO l_uom,l_input_value_name;
243 CLOSE csr_input_value_uom;
244
245 l_element_index := l_element_index + 1;
246
247 IF g_debug THEN
248 hr_utility.trace(' ......ELEMENT :'||l_element_index);
249 END IF;
250 g_element_table(l_element_index).element_type_id := l_element_type_id;
251 g_element_table(l_element_index).input_value_id := l_input_value_id;
252 g_element_table(l_element_index).element_narrative := l_element_narrative;
253
254 -- If user lefts the display name blank for elements then display name
255 -- will be 'element reporting name' || 'input value name'
256
257 IF csr_eit_rec.org_information7 IS NULL THEN
258 g_element_table(l_element_index).element_narrative := l_element_name || ' ' || l_input_value_name;
259 END IF;
260
261 IF p_archive = 'Y' THEN
262
263 pay_action_information_api.create_action_information
264 ( p_action_information_id => l_action_info_id
265 , p_action_context_id => p_payroll_action_id
266 , p_action_context_type => 'PA'
267 , p_object_version_number => l_ovn
268 , p_effective_date => p_pre_effective_date
269 , p_source_id => NULL
270 , p_source_text => NULL
271 , p_action_information_category => 'EMEA ELEMENT DEFINITION'
272 , p_action_information1 => p_pre_payroll_action_id
273 , p_action_information2 => g_element_table(l_element_index).element_type_id
274 , p_action_information3 => g_element_table(l_element_index).input_value_id
275 , p_action_information4 => g_element_table(l_element_index).element_narrative
276 , p_action_information5 => 'F'
277 , p_action_information6 => l_uom
278 );
279
280 END IF;
281
282 END IF; -- l_context = l_context = pay_apac_payslip_archive.g_element_context
283 END LOOP;
284
285 g_max_user_balance_index := l_balance_index;
286 g_max_user_element_index := l_element_index;
287
288 IF g_debug THEN
289 hr_utility.trace('Leaving procedure get_eit_definition ');
290 END IF;
291
292 EXCEPTION
293 WHEN OTHERS THEN
294 IF g_debug THEN
295 hr_utility.trace('Error occured in get_eit_definitions');
296 END IF;
297 RAISE;
298
299 END get_eit_definitions;
300
301
302
303 /*********************************************************************
304 Name : archive_user_balances
305 Purpose : Archives the EIT values for the defined balances dimension
306 *********************************************************************/
307
308 PROCEDURE archive_user_balances(p_arch_assignment_action_id IN NUMBER,
309 p_run_assignment_action_id IN NUMBER,
310 p_pre_effective_date IN DATE)
311
312 IS
313
314 l_action_info_id NUMBER;
315 l_balance_value NUMBER;
316 l_ovn NUMBER;
317 l_tax_unit_id pay_assignment_actions.tax_unit_id%type; /* Bug No : 2518997 */
318 l_source pay_run_result_values.result_value%type;
319
320 /* Start of Bug No : 2518997 */
321
322 CURSOR csr_tax_unit_id(p_run_assignment_action_id pay_assignment_actions.tax_unit_id%type)
323 IS
324 SELECT tax_unit_id
325 FROM pay_assignment_actions
326 WHERE assignment_action_id = p_run_assignment_action_id;
327
328 /* End of Bug No : 2518997 */
329
330 /*Set context for source id*/
331
332 CURSOR csr_set_source_id(p_run_assignment_action_id pay_assignment_actions.tax_unit_id%type)
333 IS
334 select distinct CONTEXT.result_value
335 from pay_run_result_values CONTEXT
336 ,pay_input_values_f PIVF
337 ,pay_run_results RR
338 ,pay_payroll_actions PACT,
339 pay_assignment_actions paa
340 where CONTEXT.run_result_id = RR.run_result_id
341 and CONTEXT.input_value_id = PIVF.input_value_id
342 and PIVF.name = 'Source'
343 and paa.assignment_action_id= p_run_assignment_action_id
344 and paa.assignment_action_id=RR.assignment_action_id
345 and paa.payroll_action_id=pact.payroll_action_id
346 and PACT.effective_date between PIVF.effective_start_date and PIVF.effective_end_date;
347
348
349 BEGIN
350 g_debug := hr_utility.debug_enabled;
351 IF g_debug THEN
352 hr_utility.trace('Start of archive user balances');
353 END IF;
354
355 FOR l_index IN 1 .. pay_apac_payslip_archive.g_max_user_balance_index LOOP
356
357 /* Start of Bug No : 2518997 */
358
359 OPEN csr_tax_unit_id(p_run_assignment_action_id);
360 FETCH csr_tax_unit_id INTO l_tax_unit_id;
361 CLOSE csr_tax_unit_id;
362
363 IF l_tax_unit_id IS NOT NULL THEN
364 pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
365 END IF;
366
367 /* End of Bug No : 2518997 */
368
369 pay_balance_pkg.set_context('SOURCE_ID',99);
370 OPEN csr_set_source_id(p_run_assignment_action_id);
371 FETCH csr_set_source_id into l_source;
372 CLOSE csr_set_source_id;
373
374 /*Set context source id if exists*/
375 if l_source is not null then
376 pay_balance_pkg.set_context('SOURCE_ID',l_source);
377 end if;
378
379 l_balance_value := pay_balance_pkg.get_value (
380 p_defined_balance_id => pay_apac_payslip_archive.g_user_balance_table(l_index).defined_balance_id
381 ,p_assignment_action_id => p_run_assignment_action_id
382 );
383 -- Archive balance if non-zero
384
385 IF l_balance_value <> 0 THEN
386
387 pay_action_information_api.create_action_information
388 ( p_action_information_id => l_action_info_id
389 , p_action_context_id => p_arch_assignment_action_id
390 , p_action_context_type => 'AAP'
391 , p_object_version_number => l_ovn
392 , p_effective_date => p_pre_effective_date
393 , p_source_id => p_run_assignment_action_id
394 , p_source_text => NULL
395 , p_action_information_category => 'EMEA BALANCES'
396 , p_action_information1 => pay_apac_payslip_archive.g_user_balance_table(l_index).defined_balance_id
397 , p_action_information4 => fnd_number.number_to_canonical(l_balance_value) -- Bug 3604206
398 );
399
400 END IF;
401
402 END LOOP;
403 IF g_debug THEN
404 hr_utility.trace(' End of archive user balances');
405 END IF;
406
407 EXCEPTION
408 WHEN OTHERS THEN
409 IF g_debug THEN
410 hr_utility.trace('Error occured in archive user balances');
411 END IF;
412 RAISE;
413 END archive_user_balances;
414
415
416
417
418 /*********************************************************************
419 Name : archive_user_elements
420 Purpose : Archives the EIT values for input values defined in the
421 EIT definition
422 *********************************************************************/
423
424 PROCEDURE archive_user_elements(p_arch_assignment_action_id IN NUMBER,
425 p_pre_assignment_action_id IN NUMBER,
426 p_latest_run_assact_id IN NUMBER,
427 p_pre_effective_date IN DATE)
428 IS
429
430
431 -- Cursor to select all payroll runs under the prepayment
432
433 CURSOR csr_all_runs_under_prepay(p_pre_assignment_action_id NUMBER)
434 IS
435 SELECT pac.assignment_action_id
436 ,pac.source_action_id
437 FROM pay_action_interlocks pai
438 ,pay_assignment_actions pac
439 ,pay_payroll_Actions ppa
440 WHERE pai.locking_action_id = p_pre_assignment_action_id
441 AND pai.locked_action_id = pac.assignment_action_id
442 AND ppa.payroll_action_id = pac.payroll_action_id
443 AND (ppa.run_type_id IS NULL
444 OR
445 (ppa.run_type_id IS not NULL
446 and pac.source_action_id IS NOT NULL)) --Bug:3731940 Because run results are not queried for master record
447 ORDER BY pac.assignment_action_id DESC;
448
449
450
451 -- Cursor to select all archived elements for category 'APAC ELEMENTS'
452
453 CURSOR csr_archived_elements(p_arch_assignment_action_id NUMBER)
454 IS
455 SELECT action_information1
456 FROM pay_action_information
457 WHERE action_context_id = p_arch_assignment_action_id
458 AND action_information_category = 'APAC ELEMENTS'
459 AND action_context_type = 'AAP';
460
461
462
463 -- Cursor to get element name declared at EIT
464
465 CURSOR csr_eit_element_name(p_element_type_id NUMBER,
466 p_effective_date DATE)
467 IS
468 SELECT nvl(reporting_name,element_name)
469 FROM pay_element_types_f
470 WHERE element_type_id = p_element_type_id
471 AND p_effective_date BETWEEN effective_start_date
472 AND effective_end_date;
473
474
475
476 -- Cursor to get Input Value name and unit of measure
477
478 CURSOR csr_input_name(p_input_value_id NUMBER,
479 p_effective_date DATE)
480 IS
481 SELECT substr(uom,1,1) uom,
482 name
483 FROM pay_input_values_f
484 WHERE input_value_id = p_input_value_id
485 AND p_effective_date BETWEEN effective_start_date
486 AND effective_end_date;
487
488
489 -- Cursor to get element run result values
490
491 CURSOR csr_element_values(p_assignment_action_id NUMBER,
492 p_element_type_id NUMBER,
493 p_input_value_id NUMBER)
494 IS
495 SELECT prv.result_value value
496 FROM pay_run_result_values prv,
497 pay_run_results prr
498 WHERE prr.status IN ('P','PA')
499 AND prv.run_result_id = prr.run_result_id
500 AND prr.assignment_action_id = p_assignment_action_id
501 AND prr.element_type_id = p_element_type_id
502 AND prv.input_value_id = p_input_value_id
503 AND prv.result_value IS NOT NULL;
504
505 -- Cursor to get element run result values for numeric results
506
507 CURSOR csr_element_num_val(p_assignment_action_id NUMBER,
508 p_element_type_id NUMBER,
509 p_input_value_id NUMBER)
510 IS
511 SELECT sum(fnd_number.canonical_to_number(prv.result_value)) value/*Bug 3228928*/
512 FROM pay_run_result_values prv,
513 pay_run_results prr
514 WHERE prr.status IN ('P','PA')
515 AND prv.run_result_id = prr.run_result_id
516 AND prr.assignment_action_id = p_assignment_action_id
517 AND prr.element_type_id = p_element_type_id
518 AND prv.input_value_id = p_input_value_id
519 AND prv.result_value IS NOT NULL;
520
521 l_latest_assignment_action_id NUMBER;
522 l_run_value NUMBER:=0;/*Bug 3228928*/
523 l_sum_value NUMBER:=0;
524 l_uom pay_input_values_f.uom%TYPE;
525 l_action_info_id NUMBER;
526 l_ovn NUMBER;
527 l_element_name pay_element_types_f.reporting_name%TYPE;
528 l_input_name pay_input_values_f.name%TYPE;
529 l_element_archived VARCHAR2(1);
530
531 BEGIN
532 g_debug := hr_utility.debug_enabled;
533 l_element_archived := 'N';
534 IF g_debug THEN
535 hr_utility.trace(' Start of archive user elements');
536 END IF;
537
538 FOR l_index IN 1 .. g_max_user_element_index LOOP
539
540 OPEN csr_eit_element_name(g_element_table(l_index).element_type_id,p_pre_effective_date);
541 FETCH csr_eit_element_name INTO l_element_name;
542 CLOSE csr_eit_element_name;
543
544 OPEN csr_input_name(g_element_table(l_index).input_value_id,p_pre_effective_date);
545 FETCH csr_input_name INTO l_uom,l_input_name;
546 CLOSE csr_input_name;
547
548
549 -- Check if the element is already archived in 'APAC ELEMENTS'
550
551 FOR csr_rec in csr_archived_elements(p_arch_assignment_action_id)
552
553 LOOP
554
555 IF csr_rec.action_information1 = l_element_name AND l_input_name = 'Pay Value' THEN
556 l_element_archived := 'Y';
557 EXIT;
558
559 END IF;
560
561 END LOOP;
562
563 -- No archival if Element is already archived in 'APAC ELEMENTS'
564
565 IF l_element_archived = 'N' THEN
566
567 IF g_debug THEN
568 hr_utility.trace(' ...Unit Of Measure is ...:'||l_uom);
569 END IF;
570
571 IF (l_uom ='M' OR l_uom='H' OR l_uom='I') THEN
572
573 l_sum_value:=0;
574
575 -- Sum all the run result values in case of multiple payrolls in prepayment
576
577 FOR rec_all_actions IN csr_all_runs_under_prepay(p_pre_assignment_action_id)
578 LOOP
579
580 l_run_value := 0;
581
582 OPEN csr_element_num_val(rec_all_actions.assignment_action_id
583 ,g_element_table(l_index).element_type_id
584 ,g_element_table(l_index).input_value_id);
585
586 Fetch csr_element_num_val INTO l_run_value;
587
588 CLOSE csr_element_num_val;
589
590 if l_run_value is not null or l_run_value <> 0 then /* Bug 6486660 */
591 l_sum_value:=l_sum_value + l_run_value;
592 end if;
593
594 END LOOP;
595 /*Bug 3228928 - Archive the sum for numeric values */
596 IF l_sum_value <> 0 THEN
597
598 pay_action_information_api.create_action_information
599 ( p_action_information_id => l_action_info_id
600 , p_action_context_id => p_arch_assignment_action_id
601 , p_action_context_type => 'AAP'
602 , p_object_version_number => l_ovn
603 , p_effective_date => p_pre_effective_date
604 , p_source_id => p_pre_assignment_action_id
605 , p_source_text => NULL
606 , p_action_information_category => 'EMEA ELEMENT INFO'
607 , p_action_information1 => g_element_table(l_index).element_type_id
608 , p_action_information2 => g_element_table(l_index).input_value_id
609 , p_action_information4 => fnd_number.number_to_canonical(l_sum_value) -- Bug 3604206
610 );
611
612 END IF;
613
614 ELSE
615 /*Bug 3228928 - Archive all the input value for non numeric values */
616 FOR rec_element_value in csr_element_values
617 (p_latest_run_assact_id
618 ,g_element_table(l_index).element_type_id
619 ,g_element_table(l_index).input_value_id)
620 LOOP
621
622 pay_action_information_api.create_action_information
623 ( p_action_information_id => l_action_info_id
624 , p_action_context_id => p_arch_assignment_action_id
625 , p_action_context_type => 'AAP'
626 , p_object_version_number => l_ovn
627 , p_effective_date => p_pre_effective_date
628 , p_source_id => p_pre_assignment_action_id
629 , p_source_text => NULL
630 , p_action_information_category => 'EMEA ELEMENT INFO'
631 , p_action_information1 => g_element_table(l_index).element_type_id
632 , p_action_information2 => g_element_table(l_index).input_value_id
633 , p_action_information4 => rec_element_value.value
634 );
635
636 END LOOP;
637
638 END IF;
639
640 END IF; -- If l_element_archived = 'N'
641
642 l_element_archived := 'N'; /* Bug 6471802 */
643
644 END LOOP; -- End of 1.. max_user_elements Loop
645
646 IF g_debug THEN
647 hr_utility.trace(' End of archive user elements');
648 END IF;
649
650 EXCEPTION
651 WHEN OTHERS THEN
652 IF g_debug THEN
653 hr_utility.trace('Error occured in archive user elements');
654 END IF;
655 RAISE;
656
657 END archive_user_elements;
658
659
660
661
662 /*******************************************************************************
663 Name : process_eit
664 Purpose : This procedure is called from both initialization_code and archive_payroll_level_data
665 with different archive flag variable.
666 From initiliazation_code it is called with p_archive='N' to populate the
667 EIT's balances and elements into global table.
668 From archive_payroll_level_data it is called with p_archive='Y' to actually archive
669 the EIT's balances and elements.
670 This procedure internally calls the common
671 pay_apac_payslip_archive.get_eit_definitions to archive the EIT balances
672 and elements.
673 ********************************************************************************/
674
675
676 PROCEDURE process_eit(p_payroll_action_id IN NUMBER
677 ,p_archive IN VARCHAR2)
678 IS
679
680 l_payroll_id NUMBER;
681 l_consolidation_set_id VARCHAR2(30);
682 l_business_group_id NUMBER;
683 l_start_date VARCHAR2(20);
684 l_end_date VARCHAR2(20);
685 l_canonical_start_date DATE;
686 l_canonical_end_date DATE;
687
688 BEGIN
689
690 IF g_debug THEN
691 hr_utility.trace('Start of process_eit');
692 END IF;
693
694 -- Get the legislative parameters of the archive request.
695
696 pay_apac_payslip_archive.get_legislative_parameters
697 (p_payroll_action_id,
698 l_payroll_id,
699 l_consolidation_set_id,
700 l_business_group_id,
701 l_start_date,
702 l_end_date
703 );
704
705 l_canonical_start_date := TO_DATE(l_start_date,fnd_date.canonical_mask);
706 l_canonical_end_date := TO_DATE(l_end_date,fnd_date.canonical_mask);
707
708 l_business_group_id := to_number(l_business_group_id);
709
710 pay_apac_payslip_archive.get_eit_definitions
711 ( p_payroll_action_id => p_payroll_action_id -- archival payroll_action_id
712 , p_business_group_id => l_business_group_id -- business group legislative parameter
713 , p_pre_payroll_action_id => NULL /* Bug No : 2613475 */
714 , p_pre_effective_date => l_canonical_start_date /* Bug No : 2613475 */
715 , p_archive => p_archive
716 );
717
718 IF g_debug THEN
719 hr_utility.trace('End of process_eit');
720 END IF;
721
722 EXCEPTION
723 WHEN OTHERS THEN
724 IF g_debug THEN
725 hr_utility.trace('Error occured in process_eit');
726 END IF;
727 RAISE;
728 END process_eit;
729
730
731
732 -- Bug 3604206
733
734 /*********************************************************************
735 Name : range_code
736 Purpose : Calls the process_eit to archive the EIT details and
737 also archives the payroll level data -
738 Messages and Employer address details.
739 *********************************************************************/
740
741 PROCEDURE range_code(p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE)
742 IS
743
744 -- Cursor to get the pay advice message
745
746 CURSOR csr_payroll_msg(p_payroll_id NUMBER,
747 p_start_date DATE,
748 p_end_date DATE)
749 IS
750 SELECT ppa.payroll_action_id payroll_action_id
751 ,ppa.effective_date run_effective_date
752 ,ppa.date_earned date_earned
753 ,ppa.pay_advice_message payroll_message
754 FROM pay_payrolls_f pp,
755 pay_payroll_actions ppa
756 WHERE ppa.payroll_id = p_payroll_id
757 AND ppa.effective_date BETWEEN p_start_date
758 AND p_end_date
759 AND (ppa.action_type = 'R' OR
760 ppa.action_type = 'Q' )
761 AND ppa.action_status = 'C'
762 AND ppa.payroll_id = pp.payroll_id
763 AND NOT EXISTS (SELECT NULL
764 FROM pay_action_information pai
765 WHERE pai.action_context_id = ppa.payroll_action_id
766 AND pai.action_context_type = 'PA'
767 AND pai.action_information_category = 'EMPLOYEE OTHER INFORMATION');
768
769 l_payroll_id NUMBER;
770 l_consolidation_set_id VARCHAR2(30);
771 l_business_group_id NUMBER;
772 l_start_date VARCHAR2(20);
773 l_end_date VARCHAR2(20);
774 l_canonical_start_date DATE;
775 l_canonical_end_date DATE;
776 l_action_info_id NUMBER;
777 l_ovn NUMBER;
778 l_archive VARCHAR2(1) ;
779
780
781 BEGIN
782 g_debug := hr_utility.debug_enabled;
783 l_archive := 'Y';
784 IF g_debug THEN
785 hr_utility.trace(' Start of APAC archive Range Code');
786 END IF;
787
788 -----------------------------------------------------------------------+
789 -- Call to process_eit with p_archive parameter as 'Y' as this will
790 -- archive the EIT details. Range_cursor is a non multi-threaded process
791 -- so archival of EIT and payroll level data is done in this procedure.
792 -----------------------------------------------------------------------+
793
794 process_eit(p_payroll_action_id,l_archive);
795
796
797 -- Get the legislative parameters of the archive request.
798
799 get_legislative_parameters
800 (p_payroll_action_id,
801 l_payroll_id,
802 l_consolidation_set_id,
803 l_business_group_id,
804 l_start_date,
805 l_end_date
806 );
807
808 l_canonical_start_date := TO_DATE(l_start_date,fnd_date.canonical_mask);
809 l_canonical_end_date := TO_DATE(l_end_date,fnd_date.canonical_mask);
810
811 l_business_group_id := to_number(l_business_group_id);
812
813 -- Call to Core package to archive Employer Address Details
814 -- Needed for Person Information region to work on payslip.
815
816 pay_emp_action_arch.arch_pay_action_level_data
817 (p_payroll_action_id,
818 l_payroll_id,
819 l_canonical_end_date);
820
821 IF g_debug THEN
822 hr_utility.trace('Archiving the Payroll Messages.');
823 END IF;
824
825 FOR csr_msg_rec IN csr_payroll_msg(l_payroll_id
826 ,l_canonical_start_date
827 ,l_canonical_end_date)
828 LOOP
829
830 IF csr_msg_rec.payroll_message IS NOT NULL THEN
831
832 pay_action_information_api.create_action_information
833 ( p_action_information_id => l_action_info_id
834 , p_action_context_id => p_payroll_action_id
835 , p_action_context_type => 'PA'
836 , p_object_version_number => l_ovn
837 , p_effective_date => csr_msg_rec.run_effective_date
838 , p_source_id => NULL
839 , p_source_text => NULL
840 , p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
841 , p_action_information1 => l_business_group_id
842 , p_action_information2 => 'MESG'
843 , p_action_information6 => csr_msg_rec.payroll_message);
844 END IF;
845
846 END LOOP;
847
848
849 IF g_debug THEN
850 hr_utility.trace('End of APAC archive Range Code');
851 END IF;
852
853 EXCEPTION
854 WHEN OTHERS THEN
855 IF g_debug THEN
856 hr_utility.trace('Error occured in APAC archive Range Code');
857 END IF;
858 RAISE;
859
860 END range_code;
861
862
863
864
865
866 /*********************************************************************
867 Name : initialization_code
868 Purpose : Calls process_eit to set the globals.
869 *********************************************************************/
870
871
872 PROCEDURE initialization_code(p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE)
873 IS
874
875 l_archive VARCHAR2(1);
876
877 BEGIN
878 g_debug := hr_utility.debug_enabled;
879 l_archive := 'N';
880 IF g_debug THEN
881 hr_utility.trace(' Start of APAC archive Initialization Code');
882 END IF;
883
884 -----------------------------------------------------------------------+
885 -- Call to process_eit with p_archive parameter as 'N' as this will
886 -- populate the EIT values in global tables. initialization_code is a
887 -- multi-threaded process. It is used to set the global contexts and variables.
888 -----------------------------------------------------------------------+
889
890 process_eit(p_payroll_action_id,l_archive);
891
892 IF g_debug THEN
893 hr_utility.trace('End of APAC Initliazation Code');
894 END IF;
895
896 EXCEPTION
897 WHEN OTHERS THEN
898 IF g_debug THEN
899 hr_utility.trace('Error occured in APAC archive Initialization Code');
900 END IF;
901 RAISE;
902
903 END initialization_code;
904
905
906
907 /*********************************************************************
908 Name : get_legislative_parameters
909 Purpose : csrs the value of legislative parameters from the
910 payroll run. For this to call, the legislative strings
911 in the concurrent request parameters should be defined as below .
912
913 PAYROLL ---- Payroll Id
914 CONSOLIDATION ---- Consolidation Id
915 BG_ID ---- Business Group Id
916 START_DATE ---- Start Date
917 END_DATE ---- End Date
918
919 *********************************************************************/
920
921 PROCEDURE get_legislative_parameters(p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE,
922 p_payroll_id OUT NOCOPY NUMBER,
923 p_consolidation OUT NOCOPY NUMBER,
924 p_business_group_id OUT NOCOPY NUMBER,
925 p_start_date OUT NOCOPY VARCHAR2,
926 p_end_date OUT NOCOPY VARCHAR2)
927 IS
928
929
930 -- Cursor to get legislative parameters from the archive request
931
932 CURSOR csr_params(p_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
933 IS
934 SELECT pay_core_utils.get_parameter('PAYROLL',legislative_parameters) payroll_id,
935 pay_core_utils.get_parameter('CONSOLIDATION',legislative_parameters) consolidation_set_id,
936 pay_core_utils.get_parameter('BG_ID',legislative_parameters) business_group_id,
937 pay_core_utils.get_parameter('START_DATE',legislative_parameters) start_date,
938 pay_core_utils.get_parameter('END_DATE',legislative_parameters) end_date
939 FROM pay_payroll_actions ppa
940 WHERE ppa.payroll_action_id = p_payroll_action_id;
941
942 BEGIN
943 g_debug := hr_utility.debug_enabled;
944 IF g_debug THEN
945 hr_utility.trace('Start of get_legislative_parameters Procedure');
946 END IF;
947
948 OPEN csr_params(p_payroll_action_id);
949 FETCH csr_params INTO p_payroll_id
950 ,p_consolidation
951 ,p_business_group_id
952 ,p_start_date
953 ,p_end_date;
954 CLOSE csr_params;
955
956 IF g_debug THEN
957 hr_utility.trace('End of get_legislative_parameters Procedure');
958 END IF;
959
960 EXCEPTION
961 WHEN OTHERS THEN
962 IF g_debug THEN
963 hr_utility.trace('Error occured in get_legislative_parameters');
964 END IF;
965 RAISE;
966
967 END get_legislative_parameters; /* End of get_legislative_parameters */
968
969 --------------------------------------------------------------------------
970 -- --
971 -- Name : DEINITIALIZATION_CODE --
972 -- Type : PROCEDURE --
973 -- Access : Public --
974 -- Description : Procedure to archive the PA level data if quick --
975 -- archive has been run. --
976 -- called are --
977 -- Parameters : --
978 -- IN : p_payroll_action_id NUMBER --
979 -- OUT : N/A --
980 -- Change History : --
981 --------------------------------------------------------------------------
982 -- Rev# Date Userid Description --
983 --------------------------------------------------------------------------
984 -- 115.0 06-Dec-2006 aaagarwa Initial Version --
985 --------------------------------------------------------------------------
986 PROCEDURE deinitialization_code (p_payroll_action_id IN NUMBER)
987 IS
988 CURSOR check_pa_data_existence
989 IS
990 SELECT 1
991 FROM pay_action_information
992 WHERE action_context_id = p_payroll_action_id
993 AND action_context_type = 'PA'
994 AND action_information_category IN('EMEA BALANCE DEFINITION'
995 ,'EMEA ELEMENT DEFINITION'
996 ,'EMPLOYEE OTHER INFORMATION'
997 ,'ADDRESS DETAILS'
998 );
999
1000 l_procedure VARCHAR2(100);
1001 l_count NUMBER;
1002
1003 BEGIN
1004 l_procedure := 'pay_apac_payslip_archive.deinitialization_code';
1005
1006 IF g_debug THEN
1007 hr_utility.set_location(l_procedure, 10);
1008 END IF;
1009 l_count := -1;
1010
1011 OPEN check_pa_data_existence;
1012 FETCH check_pa_data_existence INTO l_count;
1013 CLOSE check_pa_data_existence;
1014
1015 IF (g_debug)
1016 THEN
1017 hr_utility.trace('p_payroll_action_id:'||p_payroll_action_id);
1018 hr_utility.trace('l_count:'||l_count);
1019 END IF;
1020
1021 IF (l_count = -1)
1022 THEN
1023 pay_apac_payslip_archive.range_code(p_payroll_action_id => p_payroll_action_id);
1024 IF g_debug THEN
1025 hr_utility.set_location(l_procedure, 20);
1026 END IF;
1027 END IF;
1028
1029 IF g_debug THEN
1030 hr_utility.set_location(l_procedure, 30);
1031 END IF;
1032
1033 END deinitialization_code;
1034
1035 BEGIN
1036
1037 g_max_user_element_index := 0;
1038 g_max_user_balance_index := 0;
1039
1040 g_balance_context := 'BALANCE';
1041 g_element_context := 'ELEMENT';
1042 g_bg_context := 'Business Group:Payslip Info';
1043
1044 END pay_apac_payslip_archive; /* End Of the Package Body */
[Home] [Help]