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