[Home] [Help]
PACKAGE BODY: APPS.PAY_CN_PAYSLIP_ARCHIVE
Source
1 PACKAGE BODY pay_cn_payslip_archive AS
2 /* $Header: pycnparc.pkb 120.16.12020000.2 2012/08/06 13:41:09 mdubasi ship $ */
3
4 ----------------------------------------------------------------------+
5 -- This is a global variable used to store Archive assignment action id
6 ----------------------------------------------------------------------+
7
8 g_archive_pact NUMBER;
9 g_package CONSTANT VARCHAR2(100) := 'pay_cn_payslip_archive';
10
11 --------------------------------------------------------------------------
12 -- --
13 -- Name : RANGE_CODE --
14 -- Type : PROCEDURE --
15 -- Access : Public --
16 -- Description : This procedure returns a sql string to select a --
17 -- range of assignments eligible for archival. --
18 -- It calls pay_apac_payslip_archive.range_code that --
19 -- archives the EIT definition and payroll level data --
20 -- (Messages, employer address details etc) --
21 -- --
22 -- Parameters : --
23 -- IN : p_payroll_action_id NUMBER --
24 -- OUT : p_sql VARCHAR2 --
25 -- --
26 -- Change History : --
27 --------------------------------------------------------------------------
28 -- Rev# Date Userid Description --
29 --------------------------------------------------------------------------
30 -- 115.0 30-JUN-2003 bramajey Initial Version --
31 -- 115.1 03-JUL-2003 bramajey Removed 'distinct' from SQL statement--
32 -- returned. --
33 -- Added csr_leave_balance%FOUND --
34 -- condition --
35 --------------------------------------------------------------------------
36 --
37
38 PROCEDURE range_code(
39 p_payroll_action_id IN NUMBER
40 ,p_sql OUT NOCOPY VARCHAR2
41 )
42 IS
43 --
44 l_procedure VARCHAR2(100);
45 --
46 BEGIN
47 --
48 l_procedure := g_package || '.range_code';
49 hr_utility.set_location('Entering ' || l_procedure,10);
50
51 --------------------------------------------------------------------------------+
52 -- Call to range_code from common apac package 'pay_apac_payslip_archive'
53 -- to archive the payroll action level data and EIT defintions.
54 --------------------------------------------------------------------------------+
55
56 pay_apac_payslip_archive.range_code
57 (
58 p_payroll_action_id => p_payroll_action_id
59 );
60
61 --
62 -- sql string to SELECT a range of assignments eligible for archival.
63 --
64
65 -- Bug 3580609
66 -- Call core package to return SQL statement
67 pay_core_payslip_utils.range_cursor(p_payroll_action_id
68 ,p_sql);
69
70 hr_utility.set_location('Leaving ' || l_procedure,20);
71 --
72 EXCEPTION
73 WHEN OTHERS THEN
74 hr_utility.set_location('Error in ' || l_procedure,30);
75 RAISE;
76 --
77 END range_code;
78
79
80 /*--------------------------------------------------------------------------
81 -- Name : GET_PARAMETER --
82 -- Type : FUNCTION --
83 -- Access : Public --
84 -- Description : This function returns the payroll_id for the --
85 -- payroll_action --
86 -- Parameters : --
87 -- IN : p_name VARCHAR2 --
88 p_leg_parameters VARCHAR2 --
89 -- Returns: VARCHAR2 --
90 -------------------------------------------------------------------------- */
91
92
93 FUNCTION get_parameter
94 (
95 p_name IN VARCHAR2,
96 p_leg_parameters IN VARCHAR2
97 ) RETURN VARCHAR2 IS
98
99 start_ptr NUMBER;
100 end_ptr NUMBER;
101 token_val pay_payroll_actions.legislative_parameters%TYPE;
102 par_value pay_payroll_actions.legislative_parameters%TYPE;
103
104 BEGIN
105
106 token_val := p_name || '=';
107
108 start_ptr := instr(p_leg_parameters, token_val) + length(token_val);
109 end_ptr := instr(p_leg_parameters, ' ', start_ptr);
110
111 /* if there is no spaces, then use the length of the string */
112 IF end_ptr = 0 THEN
113 end_ptr := length(p_leg_parameters) + 1;
114 END IF;
115
116 IF instr(p_leg_parameters, token_val) = 0 THEN
117 par_value := NULL;
118 ELSE
119 par_value := substr(p_leg_parameters, start_ptr, end_ptr - start_ptr);
120 END IF;
121
122 RETURN par_value;
123
124 END get_parameter;
125
126 --------------------------------------------------------------------------
127 -- --
128 -- Name : INITIALIZATION_CODE --
129 -- Type : PROCEDURE --
130 -- Access : Public --
131 -- Description : This procedure is used to set global contexts. --
132 -- HThe globals used are PL/SQL tables --
133 -- i.e.(g_user_balance_table and g_element_table) --
134 -- It calls the procedure --
135 -- pay_apac_archive.initialization_code that actially --
136 -- sets the global variables and populates the global --
137 -- tables. --
138 -- --
139 -- Parameters : --
140 -- IN : p_payroll_action_id NUMBER --
141 -- OUT : N/A --
142 -- --
143 -- Change History : --
144 --------------------------------------------------------------------------
145 -- Rev# Date Userid Description --
146 --------------------------------------------------------------------------
147 -- 115.0 30-JUN-2003 bramajey Initial Version --
148 -- 115.1 03-JUL-2003 bramajey Replaced %TYPE with actual data type --
149 -- in parameter list. --
150 -- 115.2 14-Nov-2008 mdubasi Added code to update payroll_id in --
151 -- pay_payroll_actions table.
152 --------------------------------------------------------------------------
153 --
154
155
156 PROCEDURE initialization_code (
157 p_payroll_action_id IN NUMBER
158 )
159 IS
160 --
161 l_procedure VARCHAR2(100) ;
162 --
163 l_payroll_id NUMBER;
164 leg_param pay_payroll_actions.legislative_parameters%TYPE;
165 l_ppa_payroll_id pay_payroll_actions.payroll_id%TYPE;
166 l_pactid pay_payroll_actions.payroll_action_id%TYPE;
167 --
168 BEGIN
169 --
170 l_procedure := g_package || '.initialization_code';
171 hr_utility.set_location('Entering ' || l_procedure,10);
172
173
174 --------------------------------------------------------------------------
175 -- Code to update the payroll_id in the pay_payroll_actions tabel.
176 --------------------------------------------------------------------------
177
178 SELECT legislative_parameters,payroll_id
179 INTO leg_param,l_ppa_payroll_id
180 FROM pay_payroll_actions
181 WHERE payroll_action_id = p_payroll_action_id ;
182
183 l_payroll_id := get_parameter('PAYROLL', leg_param);
184
185 -- Update the Payroll Action with the Payroll ID
186
187 IF l_ppa_payroll_id IS NULL THEN
188
189 UPDATE pay_payroll_actions
190 SET payroll_id = l_payroll_id
191 WHERE payroll_action_id = p_payroll_action_id;
192
193 END IF;
194
195
196 g_archive_pact := p_payroll_action_id;
197
198 ------------------------------------------------------------------+
199 -- Call to common package procedure pay_apac_payslip_archive.
200 -- initialization_code to to set the global tables for EIT
201 -- that will be used by each thread in multi-threading.
202 ------------------------------------------------------------------+
203
204 pay_apac_payslip_archive.initialization_code(
205 p_payroll_action_id => p_payroll_action_id
206 );
207
208 hr_utility.set_location('Leaving ' || l_procedure,20);
209 --
210 EXCEPTION
211 WHEN OTHERS THEN
212 hr_utility.set_location('Error in ' || l_procedure,10);
213 RAISE;
214 --
215 END initialization_code;
216
217 --------------------------------------------------------------------------
218 -- --
219 -- Name : ASSIGNMENT_ACTION_CODE --
220 -- Type : PROCEDURE --
221 -- Access : Public --
222 -- Description : This procedure further restricts the assignment_id's--
223 -- returned by range_code. --
224 -- It filters the assignments selected by range_code --
225 -- procedure. --
226 -- Since the Payslip is given for each prepayment,the --
227 -- data should be archived for each prepayment. --
228 -- So,the successfully completed prepayments are --
229 -- selected and locked by the archival action --
230 -- All the successfully completed prepayments are --
231 -- selected and locked by archival to make the core --
232 -- 'Choose Payslip' work for CN. --
233 -- The archive will not pickup already archived --
234 -- prepayments --
235 -- --
236 -- Parameters : --
237 -- IN : p_payroll_action_id NUMBER --
238 -- p_start_person NUMBER --
239 -- p_end_person NUMBER --
240 -- p_chunk NUMBER --
241 -- OUT : N/A --
242 -- --
243 -- Change History : --
244 --------------------------------------------------------------------------
245 -- Rev# Date Userid Description --
246 --------------------------------------------------------------------------
247 -- 115.0 30-JUN-2003 bramajey Initial Version --
248 -- 115.1 03-JUL-2003 bramajey Replaced %TYPE with actual data type --
249 -- in parameter list. --
250 -- 115.10 07-Nov-2003 statkar Removed one date-effective check from--
251 -- the cursor for ppa1.effective_date --
252 --------------------------------------------------------------------------
253 --
254
255 PROCEDURE assignment_action_code (
256 p_payroll_action_id IN NUMBER
257 ,p_start_person IN NUMBER
258 ,p_end_person IN NUMBER
259 ,p_chunk IN NUMBER
260 )
261 IS
262 --
263 -- Bug 3580609
264 -- Removed cursors and local variable declarations
265
266 l_procedure VARCHAR2(100);
267 --
268 BEGIN
269 --
270 l_procedure := g_package || '.assignment_action_code';
271 hr_utility.set_location('Entering ' || l_procedure,10);
272
273 -- Bug 3580609
274 -- Call core package to create assignment actions
275 pay_core_payslip_utils.action_creation (
276 p_payroll_action_id
277 ,p_start_person
278 ,p_end_person
279 ,p_chunk
280 ,'CN_PAYSLIP_ARCHIVE'
281 ,'CN');
282
283 --
284 EXCEPTION
285 --
286 WHEN OTHERS THEN
287 hr_utility.set_location('Error in ' || l_procedure,10);
288 RAISE;
289 --
290 END assignment_action_code;
291
292 --------------------------------------------------------------------------
293 -- --
294 -- Name : ARCHIVE_ACCRUAL_DETAILS --
295 -- Type : PROCEDURE --
296 -- Access : Private --
297 -- Description : This procedure is used to archive accrual details --
298 -- for a given assignment_action_id. --
299 -- It calls per_accrual_calc_functions.get_net_accrual --
300 -- to get the net_accrual for the given assignment_id --
301 -- --
302 -- Parameters : --
303 -- IN : p_payroll_action_id NUMBER --
304 -- p_time_period_id NUMBER --
305 -- p_assignment_id NUMBER --
306 -- p_date_earned DATE --
307 -- p_effective_date DATE --
308 -- p_assact_id NUMBER --
309 -- p_assignment_action_id NUMBER --
310 -- p_period_end_date DATE --
311 -- p_period_start_date DATE --
312 -- --
313 -- OUT : N/A --
314 -- --
315 -- Change History : --
316 --------------------------------------------------------------------------
317 -- Rev# Date Userid Description --
318 --------------------------------------------------------------------------
319 -- 115.0 30-JUN-2003 bramajey Initial Version --
320 -- 115.1 03-JUL-2003 bramajey Changed parameter list in cursor. --
321 -- Included csr_leave_balance%FOUND --
322 -- condition --
323 --------------------------------------------------------------------------
324 --
325
326 PROCEDURE archive_accrual_details (
327 p_payroll_action_id IN NUMBER
328 ,p_time_period_id IN NUMBER
329 ,p_assignment_id IN NUMBER
330 ,p_date_earned IN DATE
331 ,p_effective_date IN DATE
332 ,p_assact_id IN NUMBER
333 ,p_assignment_action_id IN NUMBER
334 ,p_period_end_date IN DATE
335 ,p_period_start_date IN DATE
336 )
337 IS
338 --
339
340 -- Cursor to get the Leave Balance Details .
341
342 CURSOR csr_leave_balance
343 IS
344 --
345 SELECT pap.accrual_plan_name
346 ,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
347 ,pap.accrual_units_of_measure
348 ,ppa.payroll_id
349 ,pap.business_group_id
350 ,pap.accrual_plan_id
351 FROM pay_accrual_plans pap
352 ,pay_element_types_f pet
353 ,pay_element_links_f pel
354 ,pay_element_entries_f pee
355 ,pay_assignment_actions paa
356 ,pay_payroll_actions ppa
357 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
358 AND pel.element_type_id = pet.element_type_id
359 AND pee.element_link_id = pel.element_link_id
360 AND paa.assignment_id = pee.assignment_id
361 AND ppa.payroll_action_id = paa.payroll_action_id
362 AND ppa.action_type IN ('R','Q')
363 AND ppa.action_status = 'C'
364 AND ppa.date_earned BETWEEN pet.effective_start_date
365 AND pet.effective_end_date
366 AND ppa.date_earned BETWEEN pel.effective_start_date
367 AND pel.effective_end_date
368 AND ppa.date_earned BETWEEN pee.effective_start_date
369 AND pee.effective_end_date
370 AND paa.assignment_id = p_assignment_id
371 AND paa.assignment_action_id = p_assignment_action_id;
372 --
373
374 l_action_info_id NUMBER;
375 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%type;
376 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%type;
377 l_accrual_category pay_accrual_plans.accrual_category%type;
378 l_accrual_uom pay_accrual_plans.accrual_units_of_measure%type;
379 l_payroll_id pay_all_payrolls_f.payroll_id%type;
380 l_business_group_id NUMBER;
381 l_effective_date DATE;
382 l_annual_leave_balance NUMBER;
383 l_ovn NUMBER;
384 l_leave_taken NUMBER;
385 l_start_date DATE;
386 l_end_date DATE;
387 l_accrual_end_date DATE;
388 l_accrual NUMBER;
389 l_total_leave_taken NUMBER;
390 l_procedure VARCHAR2(100);
391 l_product_release VARCHAR2(50);
392 --
393 BEGIN
394 --
395 l_procedure := g_package || '.archive_accrual_details';
396 hr_utility.set_location('Entering ' || l_procedure,10);
397
398 hr_utility.set_location('Opening Cursor csr_leave_balance',20);
399
400 OPEN csr_leave_balance;
401 FETCH csr_leave_balance INTO
402 l_accrual_plan_name
403 ,l_accrual_category
404 ,l_accrual_uom
405 ,l_payroll_id
406 ,l_business_group_id
407 ,l_accrual_plan_id;
408
409 /*Bug#14374752 Starts*/
410 SELECT substr(p.product_version,1,2) INTO l_product_release
411 FROM fnd_application a, fnd_application_tl t, fnd_product_installations p
412 WHERE a.application_id = p.application_id
413 AND a.application_id = t.application_id
414 AND t.language = Userenv ('LANG')
415 AND Substr (a.application_short_name, 1, 5) = 'PAY';
416
417 IF TO_NUMBER(l_product_release) = 12 THEN
418 select accrual_plan_name into l_accrual_plan_name from pay_accrual_plans_tl
419 where accrual_plan_id = l_accrual_plan_id
420 and LANGUAGE = USERENV('LANG');
421 END IF;
422 /*Bug#14374752 Ends*/
423 IF csr_leave_balance%FOUND THEN
424 --
425 -- Call to get annual leave balance
426
427 hr_utility.set_location('Archiving Annual leave Balance information',30);
428
429 per_accrual_calc_functions.get_net_accrual
430 (
431 p_assignment_id => p_assignment_id -- number in
432 ,p_plan_id => l_accrual_plan_id -- number in
433 ,p_payroll_id => l_payroll_id -- number in
434 ,p_business_group_id => l_business_group_id -- number in
435 ,p_calculation_date => p_date_earned -- date in
436 ,p_start_date => l_start_date -- date out
437 ,p_end_date => l_end_date -- date out
438 ,p_accrual_end_date => l_accrual_end_date -- date out
439 ,p_accrual => l_accrual -- number out
440 ,p_net_entitlement => l_annual_leave_balance -- number out
441 );
442
443
444 IF l_annual_leave_balance IS NULL THEN
445 --
446 l_annual_leave_balance := 0;
447 --
448 END IF;
449
450
451 hr_utility.set_location('Archiving Leave Taken information',40);
452
453 l_leave_taken := per_accrual_calc_functions.get_absence
454 (
455 p_assignment_id
456 ,l_accrual_plan_id
457 ,p_period_end_date
458 ,p_period_start_date
459 );
460 l_ovn :=1;
461
462 IF l_accrual_plan_name IS NOT NULL THEN
463 --
464 pay_action_information_api.create_action_information
465 (
466 p_action_information_id => l_action_info_id
467 ,p_action_context_id => p_assact_id
468 ,p_action_context_type => 'AAP'
469 ,p_object_version_number => l_ovn
470 ,p_effective_date => p_effective_date
471 ,p_source_id => NULL
472 ,p_source_text => NULL
473 ,p_action_information_category => 'APAC ACCRUALS'
474 ,p_action_information1 => l_accrual_plan_name
475 ,p_action_information2 => l_accrual_category
476 ,p_action_information4 => fnd_number.number_to_canonical(l_annual_leave_balance) -- Bug 3604206
477 ,p_action_information5 => l_accrual_uom
478 );
479 --
480 END IF;
481 --
482 --
483 END IF;
484 --
485 CLOSE csr_leave_balance;
486 hr_utility.set_location('Closing Cursor csr_leave_balance',50);
487
488 hr_utility.set_location('Leaving ' || l_procedure,60);
489
490 --
491 EXCEPTION
492 WHEN OTHERS THEN
493 IF csr_leave_balance%ISOPEN THEN
494 --
495 CLOSE csr_leave_balance;
496 --
497 END IF;
498 --
499 hr_utility.set_location('Error in ' || l_procedure,70);
500 RAISE;
501 --
502 END archive_accrual_details;
503
504 --------------------------------------------------------------------------
505 -- --
506 -- Name : ARCHIVE_ABSENCES --
507 -- Type : PROCEDURE --
508 -- Access : Private --
509 -- Description : This procedure archives Absences for the employee --
510 -- based on Payroll Assignment_action_id --
511 -- --
512 -- Parameters : --
513 -- IN : p_arch_action_id NUMBER --
514 -- p_assg_act_id NUMBER --
515 -- p_pre_effective_date DATE --
516 -- --
517 -- OUT : N/A --
518 -- --
519 -- Change History : --
520 --------------------------------------------------------------------------
521 -- Rev# Date Userid Description --
522 --------------------------------------------------------------------------
523 -- 115.0 30-JUN-2003 bramajey Initial Version --
524 -- 115.1 03-JUL-2003 bramajey Changed parameter list in cursor. --
525 -- 115.2 27-Sep-2005 snekkala Removed use of pay_element_entry --
526 -- values_f in the cursor csr_asg_absences --
527 -- 115.3 15-Apr-2010 dduvvuri Added condition on pay_run_Results to
528 -- pick up only Processed and Adjusted run results.
529 --------------------------------------------------------------------------
530
531
532 PROCEDURE archive_absences (
533 p_arch_act_id IN NUMBER
534 ,p_assg_act_id IN NUMBER
535 ,p_pre_effective_date IN DATE
536 )
537 --
538 IS
539 --
540 -- Cursor to fetch absence details for the Assignment
541 --
542 CURSOR csr_asg_absences
543 IS
544 --
545 SELECT pat.name absence_type
546 ,pet.reporting_name reporting_name
547 ,decode(pet.processing_type,'R',greatest(pab.date_start,PTP.START_DATE),pab.date_start) start_date
548 ,decode(pet.processing_type,'R',least(pab.date_end,PTP.END_DATE),pab.date_end) end_date
549 ,decode(pet.processing_type,'R',to_number(prrv.result_value),nvl(pab.absence_days,pab.absence_hours)) absence_days
550 FROM pay_assignment_actions paa
551 ,pay_payroll_actions ppa
552 ,pay_run_results prr
553 ,pay_run_result_values prrv
554 ,per_time_periods ptp
555 ,pay_element_types_f pet
556 ,pay_input_values_f piv
557 ,pay_element_entries_f pee
558 ,per_absence_attendance_types pat
559 ,per_absence_attendances pab
560 WHERE paa.assignment_action_id = p_assg_act_id
561 AND ppa.payroll_action_id = paa.payroll_action_id
562 AND ppa.action_type IN ('Q','R')
563 AND ptp.time_period_id = ppa.time_period_id
564 AND paa.assignment_action_id = prr.assignment_action_id
565 AND pet.element_type_id = prr.element_type_id
566 AND pet.element_type_id = piv.element_type_id
567 AND piv.input_value_id = pat.input_value_id
568 AND pat.absence_attendance_type_id = pab.absence_attendance_type_id
569 AND pab.absence_attendance_id = pee.creator_id
570 AND pee.creator_type = 'A'
571 AND pee.assignment_id = paa.assignment_id
572 AND pee.element_entry_id = prr.source_id
573 AND piv.input_value_id = prrv.input_value_id
574 AND prr.run_result_id = prrv.run_result_id
575 AND prr.status in ('P','PA')
576 AND ppa.effective_date BETWEEN pet.effective_start_date
577 AND pet.effective_end_date
578 AND ppa.effective_date BETWEEN pee.effective_start_date
579 AND pee.effective_end_date
580 AND ppa.effective_date BETWEEN piv.effective_start_date
581 AND piv.effective_end_date;
582
583 l_procedure varchar2(200);
584 l_start_date VARCHAR2(20);
585 l_end_date VARCHAR2(20);
586 l_ovn NUMBER;
587 l_action_info_id NUMBER;
588 --
589 --
590 BEGIN
591 --
592 l_procedure := g_package || '.archive_absences';
593 hr_utility.set_location('Entering Procedure ' || l_procedure,10);
594 --
595 FOR csr_rec in csr_asg_absences
596 LOOP
597 --
598 hr_utility.set_location('csr_rec.name..................= ' ||csr_rec.absence_type,50);
599 hr_utility.set_location('csr_rec.element_reporting_name.= '||csr_rec.reporting_name,50);
600 hr_utility.set_location('csr_rec.start_date.............= '||to_char(csr_rec.start_date,'DD-MON-YYYY'),50);
601 hr_utility.set_location('csr_rec.end_date...............= '||to_char(csr_rec.end_date,'DD-MON-YYYY'),50);
602 hr_utility.set_location('csr_rec.absence_days.......... = '||csr_rec.absence_days,50);
603
604 l_start_date := fnd_date.date_to_canonical(csr_rec.start_date);
605 l_end_date := fnd_date.date_to_canonical(csr_rec.end_date);
606
607 l_ovn := 1;
608
609 pay_action_information_api.create_action_information
610 (
611 p_action_information_id => l_action_info_id
612 ,p_action_context_id => p_arch_act_id
613 ,p_action_context_type => 'AAP'
614 ,p_object_version_number => l_ovn
615 ,p_effective_date => p_pre_effective_date
616 ,p_source_id => NULL
617 ,p_source_text => NULL
618 ,p_action_information_category => 'APAC ABSENCES'
619 ,p_action_information1 => csr_rec.absence_type
620 ,p_action_information2 => csr_rec.reporting_name
621 ,p_action_information3 => NULL
622 ,p_action_information4 => l_start_date
623 ,p_action_information5 => l_end_date
624 ,p_action_information6 => fnd_number.number_to_canonical(csr_rec.absence_days) -- Bug 3604206
625 ,p_action_information7 => NULL
626 );
627 --
628 END LOOP;
629 --
630 hr_utility.set_location('Leaving Procedure ' || l_procedure,20);
631 --
632 EXCEPTION
633 --
634 WHEN others THEN
635 IF csr_asg_absences%ISOPEN THEN
636 close csr_asg_absences;
637 END IF;
638 hr_utility.set_location('Error in ' || l_procedure,30);
639 RAISE;
640 --
641 END archive_absences;
642 --
643
644
645 --------------------------------------------------------------------------
646 -- --
647 -- Name : ARCHIVE_STAT_ELEMENTS --
648 -- Type : PROCEDURE --
649 -- Access : Private --
650 -- Description : This procedure archives the elements and --
651 -- run result values. It uses view --
652 -- PAY_CN_ASG_ELEMENTS_V to get the elements and --
653 -- correspoding payments. --
654 -- --
655 -- Parameters : --
656 -- IN : p_assignment_action_id NUMBER --
657 -- p_effective_date DATE --
658 -- p_assact_id NUMBER --
659 -- --
660 -- OUT : N/A --
661 -- --
662 -- Change History : --
663 --------------------------------------------------------------------------
664 -- Rev# Date Userid Description --
665 --------------------------------------------------------------------------
666 -- 115.0 30-JUN-2003 bramajey Initial Version --
667 -- 115.1 03-JUL-2003 bramajey Changed parameter list in cursor. --
668 -- 115.2 20-Dec-2005 snekkala Removed cursor csr_std_elements --
669 -- Added csr_std_elements1, --
670 -- csr_std_elements2 --
671 -- and csr_locking_exists --
672 -- 115.3 24-Feb-2006 lnagaraj Used csr_stat_elements in place --
673 -- of changes in previous version --
674 -- 115.4 27-May-2008 dduvvuri 7121458- Added fnd_number.canonical_to_number in the
675 -- cursor csr_stat_elements ro remove
676 -- invalid number issues
677 -- 115.5 01-Dec-2008 rsaharay Added code for Pre Tax Non Statutory --
678 -- Deductions --
679 -- 115.6 12-Apr-2010 dduvvuri Added join prr.status in ('P','PA') to pick up
680 -- processed and adjusted run results only
681 --------------------------------------------------------------------------
682 --
683
684 PROCEDURE archive_stat_elements(
685 p_assignment_action_id IN NUMBER
686 ,p_effective_date IN DATE
687 ,p_assact_id IN NUMBER
688 )
689 IS
690 --
691 -- Cursor to get all the elements processed for the assignment in the
692 -- prepayment.
693
694 CURSOR csr_stat_elements
695 IS
696 SELECT nvl(petl.reporting_name,petl.element_name) element_reporting_name
697 , decode(pec.classification_name ,'Special Payments','Taxable Earnings'
698 ,'Annual Bonus','Taxable Earnings'
699 ,'Retro Taxable Earnings','Taxable Earnings'
700 ,'Retro Special Payments','Taxable Earnings'
701 ,'Retro Annual Bonus','Taxable Earnings'
702 ,'Voluntary Deductions','Voluntary Dedn'
703 ,'Severance Payments','Taxable Earnings'
704 ,'Direct Payments','Non Taxable Earnings'
705 ,'Retro Statutory Deductions','Statutory Deductions'
706 ,'Retro Variable Yearly Earnings','Taxable Earnings'
707 ,'Variable Yearly Earnings','Taxable Earnings'
708 ,'Retro Pre Tax Non Statutory Deductions' , 'Pre Tax Non Statutory Deductions'
709 ,pec.classification_name
710 ) classification_name
711 , sum(decode(substr(piv.uom,1,1), 'M', fnd_number.canonical_to_number(prrv.result_value), null)) amount
712 , decode(pet.input_currency_code, 'CNY',NULL
713 , pet.input_currency_code) foreign_currency_code
714 , pay_cn_payslip.get_exchange_rate(pet.input_currency_code
715 ,pet.output_currency_code
716 ,ppa.effective_date
717 ,ppa.business_group_id
718 ) exchange_rate
719 FROM pay_payroll_actions ppa
720 , pay_assignment_actions paa
721 , pay_run_results prr
722 , pay_run_result_values prrv
723 , pay_input_values_f piv
724 , pay_element_types_f pet
725 , pay_element_types_f_tl petl
726 , pay_element_classifications pec
727 ,pay_action_interlocks pai
728 WHERE ppa.action_type in ('R','Q')
729 AND ppa.action_status = 'C'
730 AND ppa.payroll_action_id = paa.payroll_action_id
731 AND paa.assignment_action_id = prr.assignment_action_id
732 AND pai.locking_action_id = p_assignment_action_id
733 AND pec.classification_name IN ('Taxable Earnings'
734 ,'Voluntary Deductions'
735 ,'Non Taxable Earnings'
736 ,'Statutory Deductions'
737 ,'Special Payments'
738 ,'Annual Bonus'
739 ,'Severance Payments'
740 ,'Direct Payments'
741 ,'Retro Taxable Earnings'
742 ,'Retro Statutory Deductions'
743 ,'Retro Special Payments'
744 ,'Retro Annual Bonus'
745 ,'Variable Yearly Earnings'
746 ,'Retro Variable Yearly Earnings'
747 ,'Pre Tax Non Statutory Deductions'
748 ,'Retro Pre Tax Non Statutory Deductions'
749 )
750 AND pec.legislation_code = 'CN'
751 AND pec.classification_id = pet.classification_id
752 AND pet.element_name <> 'Special Payments Normal'
753 AND pet.element_type_id = petl.element_type_id
754 AND petl.language = USERENV('LANG')
755 AND pet.element_type_id = piv.element_type_id
756 AND piv.name = decode(pec.classification_name,'Special Payments','Payment Amount'
757 ,'Pay Value')
758 AND pet.element_type_id = prr.element_type_id
759 AND prr.run_result_id = prrv.run_result_id
760 AND prr.status in ('P','PA')
761 AND piv.input_value_id = prrv.input_value_id
762 AND ppa.effective_date BETWEEN pet.effective_start_date
763 AND pet.effective_end_date
764 AND ppa.effective_date BETWEEN piv.effective_start_date
765 AND piv.effective_end_date
766 AND pai.locked_action_id = paa.assignment_action_id
767 GROUP BY pet.rowid
768 , decode(pec.classification_name ,'Special Payments','Taxable Earnings'
769 ,'Annual Bonus','Taxable Earnings'
770 ,'Retro Taxable Earnings','Taxable Earnings'
771 ,'Retro Special Payments','Taxable Earnings'
772 ,'Retro Annual Bonus','Taxable Earnings'
773 ,'Voluntary Deductions','Voluntary Dedn'
774 ,'Severance Payments','Taxable Earnings'
775 ,'Direct Payments','Non Taxable Earnings'
776 ,'Retro Statutory Deductions','Statutory Deductions'
777 ,'Retro Variable Yearly Earnings','Taxable Earnings'
778 ,'Variable Yearly Earnings','Taxable Earnings'
779 ,'Retro Pre Tax Non Statutory Deductions' , 'Pre Tax Non Statutory Deductions'
780 ,pec.classification_name
781 )
782 , nvl(petl.reporting_name,petl.element_name)
783 , pet.processing_priority
784 , pet.input_currency_code
785 , pay_cn_payslip.get_exchange_rate(pet.input_currency_code
786 ,pet.output_currency_code
787 ,ppa.effective_date
788 , ppa.business_group_id
789 );
790
791
792 l_action_info_id NUMBER;
793 l_ovn NUMBER;
794 l_foreign_currency_amount NUMBER;
795 l_rate NUMBER;
796 l_procedure VARCHAR2(100);
797 --
798 --
799 BEGIN
800 --
801
802 l_procedure := g_package ||'.archive_stat_elements';
803 hr_utility.set_location('Entering ' || l_procedure,10);
804
805 hr_utility.set_location('Opening Cursor csr_std_elements',20);
806
807
808 FOR csr_rec IN csr_stat_elements
809 LOOP
810 --
811 hr_utility.set_location('Archiving Details of Element ' ||csr_rec.element_reporting_name ,30);
812
813 IF nvl(csr_rec.exchange_rate,0) <> 0 THEN
814 l_foreign_currency_amount := csr_rec.amount / csr_rec.exchange_rate;
815 ELSE
816 l_foreign_currency_amount := NULL;
817 END IF;
818
819 IF ( csr_rec.amount IS NOT NULL) THEN
820 --
821 pay_action_information_api.create_action_information
822 (
823 p_action_information_id => l_action_info_id
824 ,p_action_context_id => p_assact_id
825 ,p_action_context_type => 'AAP'
826 ,p_object_version_number => l_ovn
827 ,p_effective_date => p_effective_date
828 ,p_source_id => NULL
829 ,p_source_text => NULL
830 ,p_action_information_category => 'APAC ELEMENTS'
831 ,p_action_information1 => csr_rec.element_reporting_name
832 ,p_action_information2 => NULL
833 ,p_action_information3 => NULL
834 ,p_action_information4 => csr_rec.classification_name
835 ,p_action_information5 => fnd_number.number_to_canonical(csr_rec.amount) -- Bug 3604206
836 ,p_action_information10 => fnd_number.number_to_canonical(csr_rec.exchange_rate) -- Bug 3604206
837 ,p_action_information11 => fnd_number.number_to_canonical(l_foreign_currency_amount) -- Bug 3604206
838 ,p_action_information12 => csr_rec.foreign_currency_code
839 );
840 --
841 END IF;
842 --
843 --
844 END LOOP;
845 --
846
847
848 hr_utility.set_location('Closing Cursor csr_std_elements',40);
849 hr_utility.set_location('End of archive Standard Element',50);
850 hr_utility.set_location('Leaving ' || l_procedure,80);
851
852 --
853 EXCEPTION
854 --
855 WHEN OTHERS THEN
856 --
857 IF csr_stat_elements%ISOPEN THEN
858 --
859 CLOSE csr_stat_elements;
860 --
861 END IF;
862 --
863 hr_utility.set_location('Error in ' || l_procedure,70);
864 RAISE;
865 --
866 END archive_stat_elements;
867
868 -- Bug 3116630 starts
869 --
870 --------------------------------------------------------------------------
871 -- --
872 -- Name : ARCHIVE_INFO_ELEMENTS --
873 -- Type : PROCEDURE --
874 -- Access : Private --
875 -- Description : This procedure archives given 'Information' --
876 -- elements --
877 -- Parameters : --
878 -- IN : p_assignment_action_id NUMBER --
879 -- p_assact_id NUMBER --
880 -- p_effective_date DATE --
881 -- p_element_name VARCHAR2 --
882 -- p_input_value_name VARCHAR2 --
883 -- --
884 -- OUT : N/A --
885 -- --
886 -- Change History : --
887 --------------------------------------------------------------------------
888 -- Rev# Date Userid Description --
889 --------------------------------------------------------------------------
890 -- 115.0 03-SEP-2003 bramajey Initial Version --
891 -- 115.1 18-Dec-2006 rpalli Element name to be archived as per --
892 -- session language --
893 --------------------------------------------------------------------------
894 --
895
896 PROCEDURE archive_info_element (
897 p_assignment_action_id IN NUMBER
898 ,p_assact_id IN NUMBER
899 ,p_effective_date IN DATE
900 ,p_element_name IN VARCHAR2
901 ,p_input_value_name IN VARCHAR2
902 )
903 IS
904 CURSOR csr_elem_name IS
905 SELECT petl.element_name
906 FROM pay_element_types_f pet,
907 pay_element_types_f_tl petl
908 WHERE pet.element_name = p_element_name
909 AND pet.legislation_code = 'CN'
910 AND pet.element_type_id = petl.element_type_id
911 AND petl.language = userenv('LANG');
912 --
913 l_action_info_id NUMBER;
914 l_ovn NUMBER;
915 l_value NUMBER;
916 l_procedure VARCHAR2(80);
917 l_element_name VARCHAR2(255);
918 --
919
920 BEGIN
921 --
922 l_procedure := g_package || '.archive_special_elements';
923 hr_utility.set_location('Entering ' || l_procedure,10);
924
925 pay_cn_payslip.get_run_result_value (
926 p_assignment_action_id => p_assignment_action_id
927 ,p_element_name => p_element_name
928 ,p_input_value_name => p_input_value_name
929 ,p_value => l_value
930 );
931
932 hr_utility.set_location('Archiving '|| p_element_name || p_input_value_name,20);
933
934 OPEN csr_elem_name;
935 FETCH csr_elem_name
936 INTO l_element_name;
937 CLOSE csr_elem_name;
938
939 pay_action_information_api.create_action_information
940 (
941 p_action_information_id => l_action_info_id
942 ,p_action_context_id => p_assact_id
943 ,p_action_context_type => 'AAP'
944 ,p_object_version_number => l_ovn
945 ,p_effective_date => p_effective_date
946 ,p_source_id => NULL
947 ,p_source_text => NULL
948 ,p_action_information_category => 'APAC ELEMENTS'
949 ,p_action_information1 => l_element_name
950 ,p_action_information2 => NULL
951 ,p_action_information3 => NULL
952 ,p_action_information4 => 'Information'
953 ,p_action_information5 => fnd_number.number_to_canonical(l_value) -- Bug 3604206
954 ,p_action_information7 => p_input_value_name
955 ,p_action_information10 => null
956 ,p_action_information11 => null
957 ,p_action_information12 => null
958 );
959 hr_utility.set_location('Leaving ' || l_procedure,30);
960 --
961 END archive_info_element;
962 --
963
964
965 --------------------------------------------------------------------------
966 -- --
967 -- Name : ARCHIVE_SPECIAL_ELEMENTS --
968 -- Type : PROCEDURE --
969 -- Access : Private --
970 -- Description : This procedure archives elements required --
971 -- for Tax Reporting purposes --
972 -- --
973 -- Parameters : --
974 -- IN : p_assignment_action_id NUMBER --
975 -- p_effective_date DATE --
976 -- p_assact_id NUMBER --
977 -- --
978 -- OUT : N/A --
979 -- --
980 -- Change History : --
981 --------------------------------------------------------------------------
982 -- Rev# Date Userid Description --
983 --------------------------------------------------------------------------
984 -- 115.0 03-SEP-2003 bramajey Initial Version --
985 -- 115.1 20-Jul-2005 rpalli Bug4303538: Added code to archive --
986 -- "Bonus Taxable Income" --
987 -- 115.2 03-Mar-2006 rpalli 4994788 Modified code to archive --
988 -- elements useful in annual bonus tax --
989 -- reporting --
990 -- 115.3 26-Apr-2006 rpalli 5160582 Modified code to archive --
991 -- some more input values for elements --
992 -- useful in annual bonus tax reporting --
993 -- and removed code for above fix for --
994 -- bug 4994788 --
995 --------------------------------------------------------------------------
996 --
997
998 PROCEDURE archive_special_elements(
999 p_assignment_action_id IN NUMBER
1000 ,p_effective_date IN DATE
1001 ,p_assact_id IN NUMBER
1002 )
1003 IS
1004 --
1005 l_procedure VARCHAR2(80);
1006 --
1007 BEGIN
1008 --
1009 l_procedure := g_package || '.archive_special_elements';
1010 hr_utility.set_location('Entering ' || l_procedure,10);
1011
1012 archive_info_element (
1013 p_assignment_action_id => p_assignment_action_id
1014 ,p_assact_id => p_assact_id
1015 ,p_effective_date => p_effective_date
1016 ,p_element_name => 'Tax Report Information'
1017 ,p_input_value_name => 'QD Amount'
1018 );
1019
1020 archive_info_element (
1021 p_assignment_action_id => p_assignment_action_id
1022 ,p_assact_id => p_assact_id
1023 ,p_effective_date => p_effective_date
1024 ,p_element_name => 'Tax Report Information'
1025 ,p_input_value_name => 'Separate QD Amount'
1026 );
1027
1028 archive_info_element (
1029 p_assignment_action_id => p_assignment_action_id
1030 ,p_assact_id => p_assact_id
1031 ,p_effective_date => p_effective_date
1032 ,p_element_name => 'Tax Report Information'
1033 ,p_input_value_name => 'Separate Tax Rate'
1034 );
1035
1036 archive_info_element (
1037 p_assignment_action_id => p_assignment_action_id
1038 ,p_assact_id => p_assact_id
1039 ,p_effective_date => p_effective_date
1040 ,p_element_name => 'Tax Report Information'
1041 ,p_input_value_name => 'Severance QD Amount'
1042 );
1043
1044 archive_info_element (
1045 p_assignment_action_id => p_assignment_action_id
1046 ,p_assact_id => p_assact_id
1047 ,p_effective_date => p_effective_date
1048 ,p_element_name => 'Tax Report Information'
1049 ,p_input_value_name => 'Severance Tax Rate'
1050 );
1051
1052 archive_info_element (
1053 p_assignment_action_id => p_assignment_action_id
1054 ,p_assact_id => p_assact_id
1055 ,p_effective_date => p_effective_date
1056 ,p_element_name => 'Tax Report Information'
1057 ,p_input_value_name => 'Severance Taxable Income'
1058 );
1059
1060 archive_info_element (
1061 p_assignment_action_id => p_assignment_action_id
1062 ,p_assact_id => p_assact_id
1063 ,p_effective_date => p_effective_date
1064 ,p_element_name => 'Tax Report Information'
1065 ,p_input_value_name => 'Tax Exempt Amount'
1066 );
1067
1068 archive_info_element (
1069 p_assignment_action_id => p_assignment_action_id
1070 ,p_assact_id => p_assact_id
1071 ,p_effective_date => p_effective_date
1072 ,p_element_name => 'Tax Report Information'
1073 ,p_input_value_name => 'Tax Rate'
1074 );
1075
1076 archive_info_element (
1077 p_assignment_action_id => p_assignment_action_id
1078 ,p_assact_id => p_assact_id
1079 ,p_effective_date => p_effective_date
1080 ,p_element_name => 'Tax Report Information'
1081 ,p_input_value_name => 'Taxable Income'
1082 );
1083
1084 /*Bug 4303538 starts */
1085 archive_info_element (
1086 p_assignment_action_id => p_assignment_action_id
1087 ,p_assact_id => p_assact_id
1088 ,p_effective_date => p_effective_date
1089 ,p_element_name => 'Tax on Annual Bonus'
1090 ,p_input_value_name => 'Bonus Taxable Income'
1091 );
1092
1093 archive_info_element (
1094 p_assignment_action_id => p_assignment_action_id
1095 ,p_assact_id => p_assact_id
1096 ,p_effective_date => p_effective_date
1097 ,p_element_name => 'Retro Tax on Annual Bonus'
1098 ,p_input_value_name => 'Bonus Taxable Income'
1099 );
1100 /*Bug 4303538 ends */
1101
1102
1103 /*Bug 5160582 starts */
1104 archive_info_element (
1105 p_assignment_action_id => p_assignment_action_id
1106 ,p_assact_id => p_assact_id
1107 ,p_effective_date => p_effective_date
1108 ,p_element_name => 'Tax on Annual Bonus'
1109 ,p_input_value_name => 'Bonus Tax Rate'
1110 );
1111
1112 archive_info_element (
1113 p_assignment_action_id => p_assignment_action_id
1114 ,p_assact_id => p_assact_id
1115 ,p_effective_date => p_effective_date
1116 ,p_element_name => 'Tax on Annual Bonus'
1117 ,p_input_value_name => 'Bonus QD Amount'
1118 );
1119
1120 archive_info_element (
1121 p_assignment_action_id => p_assignment_action_id
1122 ,p_assact_id => p_assact_id
1123 ,p_effective_date => p_effective_date
1124 ,p_element_name => 'Retro Tax on Annual Bonus'
1125 ,p_input_value_name => 'Bonus Tax Rate'
1126 );
1127
1128 archive_info_element (
1129 p_assignment_action_id => p_assignment_action_id
1130 ,p_assact_id => p_assact_id
1131 ,p_effective_date => p_effective_date
1132 ,p_element_name => 'Retro Tax on Annual Bonus'
1133 ,p_input_value_name => 'Bonus QD Amount'
1134 );
1135
1136 /*Bug 5160582 ends */
1137
1138 archive_info_element (
1139 p_assignment_action_id => p_assignment_action_id
1140 ,p_assact_id => p_assact_id
1141 ,p_effective_date => p_effective_date
1142 ,p_element_name => 'Special Payments Separate'
1143 ,p_input_value_name => 'Process Separate Amount'
1144 );
1145
1146 hr_utility.set_location('Leaving ' || l_procedure,20);
1147
1148 --
1149 END archive_special_elements;
1150 --
1151 -- Bug 3116630 ends
1152
1153 --------------------------------------------------------------------------
1154 -- --
1155 -- Name : ARCHIVE_BALANCES --
1156 -- Type : PROCEDURE --
1157 -- Access : Private --
1158 -- Description : This procedure archives the given balance,its --
1159 -- current and YTD value. --
1160 -- --
1161 -- Parameters : --
1162 -- IN : p_effective_date DATE --
1163 -- p_assact_id NUMBER --
1164 -- p_narraive VARCHAR2 --
1165 -- p_value_curr NUMBER --
1166 -- p_value_ytd NUMBER --
1167 -- --
1168 -- OUT : N/A --
1169 -- --
1170 -- Change History : --
1171 --------------------------------------------------------------------------
1172 -- Rev# Date Userid Description --
1173 --------------------------------------------------------------------------
1174 -- 115.0 30-JUN-2003 bramajey Initial Version --
1175 -- 115.1 09-JUL-2004 sshankar Bug 3746275. Archived balance name --
1176 -- under source text coulmn and balance --
1177 -- reporting name under information1 --
1178 --------------------------------------------------------------------------
1179 --
1180
1181 PROCEDURE archive_balances(
1182 p_effective_date IN DATE
1183 ,p_assact_id IN NUMBER
1184 ,p_narrative IN VARCHAR2
1185 ,p_value_curr IN NUMBER
1186 ,p_value_ytd IN NUMBER
1187 ,p_bal_rpt_name IN VARCHAR2
1188 )
1189 IS
1190 --
1191 l_action_info_id NUMBER;
1192 l_ovn NUMBER;
1193 l_procedure VARCHAR2(80);
1194 --
1195 BEGIN
1196 --
1197 l_procedure := g_package || '.archive_balances';
1198 hr_utility.set_location('Entering ' || l_procedure,10);
1199 hr_utility.set_location('Archiving balance : ' || p_narrative,20);
1200 hr_utility.set_location('Balance reporting name : ' || p_bal_rpt_name,25);
1201
1202 -- Archive Statutory balances
1203
1204 pay_action_information_api.create_action_information
1205 (
1206 p_action_information_id => l_action_info_id
1207 ,p_action_context_id => p_assact_id
1208 ,p_action_context_type => 'AAP'
1209 ,p_object_version_number => l_ovn
1210 ,p_effective_date => p_effective_date
1211 ,p_source_id => NULL
1212 ,p_source_text => p_narrative
1213 ,p_action_information_category => 'APAC BALANCES'
1214 ,p_action_information1 => p_bal_rpt_name
1215 ,p_action_information2 => NULL
1216 ,p_action_information3 => NULL
1217 ,p_action_information4 => fnd_number.number_to_canonical(p_value_ytd) -- Bug 3604206
1218 ,p_action_information5 => fnd_number.number_to_canonical(p_value_curr) -- Bug 3604206
1219 );
1220
1221 hr_utility.set_location('Leaving ' || l_procedure,30);
1222 --
1223 EXCEPTION
1224 WHEN OTHERS THEN
1225 hr_utility.set_location('Error in ' || l_procedure,40);
1226 RAISE;
1227 --
1228 END archive_balances;
1229 --
1230
1231 --------------------------------------------------------------------------
1232 -- --
1233 -- Name : ARCHIVE_STAT_BALANCES --
1234 -- Type : PROCEDURE --
1235 -- Access : Private --
1236 -- Description : This procedure calls pay_cn_payslip.balance_totals --
1237 -- to get the current and YTD values of the following --
1238 -- balances --
1239 -- 1. Taxable Earnings --
1240 -- 2. Non Taxable Earnings --
1241 -- 3. Statutory Deductions --
1242 -- 4. Voluntary Deductions --
1243 -- It then calls ARCHIVE_BALANCES to archive --
1244 -- individual balances --
1245 -- --
1246 -- Parameters : --
1247 -- IN : p_assignment_action_id NUMBER --
1248 -- p_assignment_id NUMBER --
1249 -- p_date_earned DATE --
1250 -- p_effective_date DATE --
1251 -- p_assact_id NUMBER --
1252 -- --
1253 -- OUT : N/A --
1254 -- --
1255 -- Change History : --
1256 --------------------------------------------------------------------------
1257 -- Rev# Date Userid Description --
1258 --------------------------------------------------------------------------
1259 -- 115.0 30-JUN-2003 bramajey Initial Version --
1260 -- 115.1 09-JUL-2004 sshankar Added code to archive balances --
1261 -- reporting name from translated table.--
1262 -- Bug 3746275. --
1263 -- 115.2 03-Mar-2006 rpalli 4994788 Modified code to archive --
1264 -- balances useful in annual bonus tax --
1265 -- reporting --
1266 -- 115.3 18-Dec-2006 rpalli 5717755 Modified code to archive --
1267 -- balance names for annnual bonus --
1268 -- based on session language --
1269 -- 115.4 19-Dec-2006 rpalli 5724500 Modified code to archive --
1270 -- balance report names for annnual bonus--
1271 -- 115.5 01-Dec-2008 rsaharay Added code for Pre Tax Non Statutory --
1272 -- Deductions --
1273 --------------------------------------------------------------------------
1274 --
1275
1276 PROCEDURE archive_stat_balances(
1277 p_assignment_action_id IN NUMBER
1278 ,p_assignment_id IN NUMBER
1279 ,p_date_earned IN DATE
1280 ,p_effective_date IN DATE
1281 ,p_assact_id IN NUMBER
1282 )
1283 IS
1284 --
1285 l_taxable_earnings_current NUMBER;
1286 l_non_taxable_earnings_current NUMBER;
1287 l_voluntary_deductions_current NUMBER;
1288 l_statutory_deductions_current NUMBER;
1289 l_pre_tax_deductions_current NUMBER;
1290 l_taxable_earnings_ytd NUMBER;
1291 l_non_taxable_earnings_ytd NUMBER;
1292 l_statutory_deductions_ytd NUMBER;
1293 l_voluntary_deductions_ytd NUMBER;
1294 l_pre_tax_deductions_ytd NUMBER;
1295 l_narrative VARCHAR2(150);
1296 l_procedure VARCHAR2(80);
1297 --
1298 -- Bug 3746275. Start
1299 --
1300 l_balance_rpt_name VARCHAR2(200);
1301
1302 l_annual_bonus_current NUMBER;
1303 l_annual_bonus_ytd NUMBER;
1304 l_retro_ann_bonus_current NUMBER;
1305 l_retro_ann_bonus_ytd NUMBER;
1306 l_tax_ann_bonus_current NUMBER;
1307 l_tax_ann_bonus_ytd NUMBER;
1308
1309 CURSOR csr_balance_rpt_name(c_balance_name VARCHAR2)
1310 IS
1311 SELECT nvl(bal_tl.reporting_name, bal_tl.balance_name)
1312 FROM pay_balance_types bal
1313 ,pay_balance_types_tl bal_tl
1314 WHERE bal.balance_name = c_balance_name
1315 AND bal.legislation_code = 'CN'
1316 AND bal.balance_type_id = bal_tl.balance_type_id
1317 AND bal_tl.language = USERENV('LANG');
1318 --
1319 -- Bug 3746275. End
1320 --
1321 --
1322 BEGIN
1323 --
1324 l_procedure := g_package || '.archive_stat_balances';
1325 hr_utility.set_location('Entering ' || l_procedure,10);
1326 hr_utility.set_location('Calling balance_total from pay_cn_payslip',20);
1327
1328 -- Get the totals of all the balances
1329
1330 pay_cn_payslip.balance_totals(
1331 p_prepaid_tag => 'Y'
1332 ,p_assignment_action_id => p_assignment_action_id
1333 ,p_taxable_earnings_current => l_taxable_earnings_current
1334 ,p_non_taxable_earnings_current => l_non_taxable_earnings_current
1335 ,p_voluntary_deductions_current => l_voluntary_deductions_current
1336 ,p_statutory_deductions_current => l_statutory_deductions_current
1337 ,p_pre_tax_deductions_current => l_pre_tax_deductions_current
1338 ,p_taxable_earnings_ytd => l_taxable_earnings_ytd
1339 ,p_non_taxable_earnings_ytd => l_non_taxable_earnings_ytd
1340 ,p_voluntary_deductions_ytd => l_voluntary_deductions_ytd
1341 ,p_statutory_deductions_ytd => l_statutory_deductions_ytd
1342 ,p_pre_tax_deductions_ytd => l_pre_tax_deductions_ytd
1343 );
1344
1345 l_narrative := 'Taxable Earnings';
1346 --
1347 -- Bug 3746275. Start
1348 -- Fetch Balance's reporting name from translated table for Userenv language
1349 --
1350 OPEN csr_balance_rpt_name(l_narrative);
1351 FETCH csr_balance_rpt_name INTO l_balance_rpt_name;
1352 CLOSE csr_balance_rpt_name;
1353 hr_utility.set_location('Archiving value for ' || l_balance_rpt_name,30);
1354
1355 archive_balances(
1356 p_effective_date => p_effective_date
1357 ,p_assact_id => p_assact_id
1358 ,p_narrative => l_narrative
1359 ,p_value_curr => l_taxable_earnings_current
1360 ,p_value_ytd => l_taxable_earnings_ytd
1361 ,p_bal_rpt_name => l_balance_rpt_name
1362 );
1363
1364 --
1365 -- Bug 3746275. End
1366 --
1367 l_narrative := 'Non Taxable Earnings';
1368 --
1369 -- Bug 3746275. Start
1370 -- Fetch Balance's reporting name from translated table for Userenv language
1371 --
1372 OPEN csr_balance_rpt_name(l_narrative);
1373 FETCH csr_balance_rpt_name INTO l_balance_rpt_name;
1374 CLOSE csr_balance_rpt_name;
1375 hr_utility.set_location('Archiving value for ' || l_balance_rpt_name,30);
1376
1377 archive_balances(
1378 p_effective_date => p_effective_date
1379 ,p_assact_id => p_assact_id
1380 ,p_narrative => l_narrative
1381 ,p_value_curr => l_non_taxable_earnings_current
1382 ,p_value_ytd => l_non_taxable_earnings_ytd
1383 ,p_bal_rpt_name => l_balance_rpt_name
1384 );
1385
1386 --
1387 -- Bug 3746275. End
1388 --
1389 l_narrative := 'Voluntary Deductions';
1390 --
1391 -- Bug 3746275. Start
1392 -- Fetch Balance's reporting name from translated table for Userenv language
1393 --
1394 OPEN csr_balance_rpt_name(l_narrative);
1395 FETCH csr_balance_rpt_name INTO l_balance_rpt_name;
1396 CLOSE csr_balance_rpt_name;
1397 hr_utility.set_location('Archiving value for ' || l_balance_rpt_name,30);
1398
1399 archive_balances(
1400 p_effective_date => p_effective_date
1401 ,p_assact_id => p_assact_id
1402 ,p_narrative => l_narrative
1403 ,p_value_curr => l_voluntary_deductions_current
1404 ,p_value_ytd => l_voluntary_deductions_ytd
1405 ,p_bal_rpt_name => l_balance_rpt_name
1406 );
1407 --
1408 -- Bug 3746275. End
1409 --
1410
1411 l_narrative := 'Statutory Deductions';
1412 --
1413 -- Bug 3746275. Start
1414 -- Fetch Balance's reporting name from translated table for Userenv language
1415 --
1416 OPEN csr_balance_rpt_name(l_narrative);
1417 FETCH csr_balance_rpt_name INTO l_balance_rpt_name;
1418 CLOSE csr_balance_rpt_name;
1419
1420 hr_utility.set_location('Archiving value for ' || l_balance_rpt_name,30);
1421
1422 archive_balances(
1423 p_effective_date => p_effective_date
1424 ,p_assact_id => p_assact_id
1425 ,p_narrative => l_narrative
1426 ,p_value_curr => l_statutory_deductions_current
1427 ,p_value_ytd => l_statutory_deductions_ytd
1428 ,p_bal_rpt_name => l_balance_rpt_name
1429 );
1430 --
1431 -- Bug 3746275. End
1432 --
1433
1434 l_narrative := 'Pre Tax Non Statutory Deductions';
1435 --
1436 -- Bug 3746275. Start
1437 -- Fetch Balance's reporting name from translated table for Userenv language
1438 --
1439 OPEN csr_balance_rpt_name(l_narrative);
1440 FETCH csr_balance_rpt_name INTO l_balance_rpt_name;
1441 CLOSE csr_balance_rpt_name;
1442 hr_utility.set_location('Archiving value for ' || l_balance_rpt_name,35);
1443
1444 archive_balances(
1445 p_effective_date => p_effective_date
1446 ,p_assact_id => p_assact_id
1447 ,p_narrative => l_narrative
1448 ,p_value_curr => l_pre_tax_deductions_current
1449 ,p_value_ytd => l_pre_tax_deductions_ytd
1450 ,p_bal_rpt_name => l_balance_rpt_name
1451 );
1452
1453
1454 l_narrative := 'Annual Bonus';
1455 --
1456 --
1457 OPEN csr_balance_rpt_name(l_narrative);
1458 FETCH csr_balance_rpt_name INTO l_balance_rpt_name;
1459 CLOSE csr_balance_rpt_name;
1460 hr_utility.set_location('Archiving value for ' || l_balance_rpt_name,40);
1461
1462 pay_cn_payslip.current_and_ytd_balances (
1463 p_prepaid_tag => 'Y'
1464 ,p_assignment_action_id => p_assact_id
1465 ,p_balance_name => l_narrative
1466 ,p_current_balance => l_annual_bonus_current
1467 ,p_ytd_balance => l_annual_bonus_ytd
1468 );
1469
1470 archive_balances(
1471 p_effective_date => p_effective_date
1472 ,p_assact_id => p_assact_id
1473 ,p_narrative => l_narrative
1474 ,p_value_curr => l_annual_bonus_current
1475 ,p_value_ytd => l_annual_bonus_ytd
1476 ,p_bal_rpt_name => l_balance_rpt_name
1477 );
1478
1479
1480 l_narrative := 'Retro Annual Bonus';
1481 --
1482 --
1483 OPEN csr_balance_rpt_name(l_narrative);
1484 FETCH csr_balance_rpt_name INTO l_balance_rpt_name;
1485 CLOSE csr_balance_rpt_name;
1486 hr_utility.set_location('Archiving value for ' || l_balance_rpt_name,45);
1487
1488 pay_cn_payslip.current_and_ytd_balances (
1489 p_prepaid_tag => 'Y'
1490 ,p_assignment_action_id => p_assact_id
1491 ,p_balance_name => l_narrative
1492 ,p_current_balance => l_retro_ann_bonus_current
1493 ,p_ytd_balance => l_retro_ann_bonus_ytd
1494 );
1495
1496 archive_balances(
1497 p_effective_date => p_effective_date
1498 ,p_assact_id => p_assact_id
1499 ,p_narrative => l_narrative
1500 ,p_value_curr => l_retro_ann_bonus_current
1501 ,p_value_ytd => l_retro_ann_bonus_ytd
1502 ,p_bal_rpt_name => l_balance_rpt_name
1503 );
1504
1505 l_narrative := 'Tax on Annual Bonus';
1506 --
1507 --
1508 OPEN csr_balance_rpt_name(l_narrative);
1509 FETCH csr_balance_rpt_name INTO l_balance_rpt_name;
1510 CLOSE csr_balance_rpt_name;
1511 hr_utility.set_location('Archiving value for ' || l_balance_rpt_name,45);
1512
1513 pay_cn_payslip.current_and_ytd_balances (
1514 p_prepaid_tag => 'Y'
1515 ,p_assignment_action_id => p_assact_id
1516 ,p_balance_name => l_narrative
1517 ,p_current_balance => l_tax_ann_bonus_current
1518 ,p_ytd_balance => l_tax_ann_bonus_ytd
1519 );
1520
1521 archive_balances(
1522 p_effective_date => p_effective_date
1523 ,p_assact_id => p_assact_id
1524 ,p_narrative => l_narrative
1525 ,p_value_curr => l_tax_ann_bonus_current
1526 ,p_value_ytd => l_tax_ann_bonus_ytd
1527 ,p_bal_rpt_name => l_balance_rpt_name
1528 );
1529
1530 hr_utility.set_location('End of Archiving Stat Balances ',100);
1531
1532 hr_utility.set_location('Leaving ' || l_procedure,110);
1533 --
1534 EXCEPTION
1535 WHEN OTHERS THEN
1536 hr_utility.set_location('Error in ' || l_procedure,120);
1537 RAISE;
1538 --
1539 END archive_stat_balances;
1540
1541 --------------------------------------------------------------------------
1542 -- --
1543 -- Name : ARCHIVE_LEGAL_EMPLOYER_DETAILS --
1544 -- Type : PROCEDURE --
1545 -- Access : Private --
1546 -- Description : This procedure archives the legal employer address --
1547 -- and withholding file number of the Employer --
1548 -- The action DF structures used are --
1549 -- ADDRESS DETAILS --
1550 -- Parameters : --
1551 -- IN : p_payroll_action_id NUMBER --
1552 -- p_tax_unit_id NUMBER --
1553 -- p_effective_date DATE --
1554 -- --
1555 -- OUT : N/A --
1556 -- --
1557 -- Change History : --
1558 --------------------------------------------------------------------------
1559 -- Rev# Date Userid Description --
1560 --------------------------------------------------------------------------
1561 -- 115.0 17-SEP-2003 bramajey Initial Version --
1562 -- 115.1 18-SEP-2003 bramajey Added code to archive telephone --
1563 -- number --
1564 --------------------------------------------------------------------------
1565 PROCEDURE archive_legal_employer_details
1566 (
1567 p_payroll_action_id IN NUMBER
1568 ,p_employer_id IN NUMBER
1569 ,p_effective_date IN DATE
1570 )
1571 IS
1572 CURSOR csr_arch_address IS
1573 SELECT 'exists'
1574 FROM pay_action_information
1575 WHERE action_context_id = p_payroll_action_id
1576 AND action_context_type = 'PA'
1577 AND action_information_category = 'ADDRESS DETAILS'
1578 AND action_information1 = p_employer_id
1579 AND action_information14 = 'Legal Employer Address'
1580 AND effective_date = p_effective_date;
1581
1582 CURSOR csr_le_address IS
1583 SELECT hl.address_line_1
1584 ,hl.address_line_2
1585 ,hr_general.decode_lookup('CN_PROVINCE',hl.town_or_city) province
1586 ,ft.territory_short_name country
1587 ,hl.postal_code
1588 ,hl.telephone_number_1
1589 FROM hr_all_organization_units hou
1590 ,hr_locations hl
1591 ,fnd_territories_tl ft
1592 WHERE hou.organization_id = p_employer_id
1593 AND hou.location_id = hl.location_id
1594 AND hl.country = ft.territory_code
1595 AND ft.language = userenv ('LANG');
1596
1597 --
1598 CURSOR csr_file_number
1599 IS
1600 SELECT hoi.org_information8
1601 FROM hr_organization_information hoi
1602 WHERE hoi.organization_id = p_employer_id
1603 AND hoi.org_information_context like 'PER_EMPLOYER_INFO_CN' ;
1604 --
1605 l_dummy VARCHAR2(10);
1606 l_address_line_1 hr_locations.address_line_1%TYPE;
1607 l_address_line_2 hr_locations.address_line_2%TYPE;
1608 l_province hr_lookups.meaning%TYPE;
1609 l_country fnd_territories_tl.territory_short_name%TYPE;
1610 l_postal_code hr_locations.postal_code%TYPE;
1611 l_telephone_no hr_locations.telephone_number_1%TYPE;
1612 l_file_number hr_organization_information.org_information8%TYPE;
1613 l_procedure VARCHAR2(80);
1614 l_ovn NUMBER;
1615 l_action_info_id NUMBER;
1616
1617 BEGIN
1618 l_procedure := g_package || '.archive_employee_details';
1619 l_ovn := TO_NUMBER(NULL);
1620 l_action_info_id := TO_NUMBER(NULL);
1621
1622 hr_utility.set_location('Entering ' || l_procedure,10);
1623
1624 hr_utility.set_location('Opening cursor csr_arch_address',20);
1625 OPEN csr_arch_address;
1626 FETCH csr_arch_address
1627 INTO l_dummy;
1628 IF csr_arch_address%NOTFOUND THEN
1629 --
1630
1631 hr_utility.set_location('Opening cursor csr_le_address',30);
1632
1633 OPEN csr_le_address;
1634 FETCH csr_le_address
1635 INTO l_address_line_1, l_address_line_2, l_province, l_country, l_postal_code,l_telephone_no;
1636 CLOSE csr_le_address;
1637
1638 hr_utility.set_location('Closing cursor csr_le_address',40);
1639
1640 hr_utility.set_location('Opening cursor csr_file_number',50);
1641
1642 OPEN csr_file_number;
1643 FETCH csr_file_number
1644 INTO l_file_number;
1645 CLOSE csr_file_number;
1646
1647 hr_utility.set_location('Closing cursor csr_file_number',60);
1648 --
1649 -- Archiving the Employer Address only if it doesnot exists
1650 --
1651 hr_utility.set_location('Archiving Legal Employer Address',70);
1652
1653 pay_action_information_api.create_action_information
1654 (
1655 p_action_information_id => l_action_info_id,
1656 p_object_version_number => l_ovn,
1657 p_action_information_category => 'ADDRESS DETAILS',
1658 p_action_context_id => p_payroll_action_id,
1659 p_action_context_type => 'PA',
1660 p_effective_date => p_effective_date,
1661 p_action_information1 => p_employer_id,
1662 p_action_information5 => l_address_line_1,
1663 p_action_information6 => l_address_line_2,
1664 p_action_information8 => l_province,
1665 p_action_information9 => l_telephone_no,
1666 p_action_information12 => l_postal_code,
1667 p_action_information13 => l_country,
1668 p_action_information14 => 'Legal Employer Address',
1669 p_action_information26 => l_file_number
1670 );
1671
1672 hr_utility.set_location('After archival of Legal Employer Address',80);
1673 --
1674 END IF;
1675
1676 CLOSE csr_arch_address;
1677
1678 hr_utility.set_location('Leaving ' || l_procedure,200);
1679 --
1680 EXCEPTION
1681 --
1682 WHEN OTHERS THEN
1683 IF csr_arch_address%ISOPEN THEN
1684 CLOSE csr_arch_address;
1685 END IF;
1686 IF csr_le_address%ISOPEN THEN
1687 CLOSE csr_le_address;
1688 END IF;
1689 IF csr_file_number%ISOPEN THEN
1690 CLOSE csr_file_number;
1691 END IF;
1692
1693 END archive_legal_employer_details;
1694
1695
1696
1697 --------------------------------------------------------------------------
1698 -- --
1699 -- Name : ARCHIVE_EMPLOYEE_DETAILS --
1700 -- Type : PROCEDURE --
1701 -- Access : Private --
1702 -- Description : This procedure calls --
1703 -- 'pay_emp_action_arch.get_personal_information' that --
1704 -- actually archives the employee details,employee --
1705 -- ddress details, Employer Address Details --
1706 -- and Net Pay Distribution information. Procedure --
1707 -- 'get_personal_information' is passed tax_unit_id --
1708 -- to make core provided 'Choose Payslip' work for CN. --
1709 -- The action DF structures used are --
1710 -- ADDRESS DETAILS --
1711 -- EMPLOYEE DETAILS --
1712 -- EMPLOYEE NET PAY DISTRIBUTION --
1713 -- EMPLOYEE OTHER INFORMATION --
1714 -- After core procedure completes the archival, the --
1715 -- information stored for category --
1716 -- EMPLOYEE_NET_PAY_DISTRIBUTION is updated with --
1717 -- Bank_name,Bank Branch,Account Number,percentage --
1718 -- and currency code. --
1719 -- Then EMPLOYEE DETAILS is updated with the --
1720 -- payroll_location available in SOFT_CODING_KEY_FLEX --
1721 -- --
1722 -- Parameters : --
1723 -- IN : p_payroll_action_id NUMBER --
1724 -- p_pay_assignment_action_id NUMBER --
1725 -- p_assact_id NUMBER --
1726 -- p_assignment_id NUMBER --
1727 -- p_curr_pymt_ass_act_id NUMBER --
1728 -- p_date_earned DATE --
1729 -- p_latest_period_payment_date DATE --
1730 -- p_run_effective_date DATE --
1731 -- p_time_period_id NUMBER --
1732 -- p_pre_effective_date DATE --
1733 -- --
1734 -- OUT : N/A --
1735 -- --
1736 -- Change History : --
1737 --------------------------------------------------------------------------
1738 -- Rev# Date Userid Description --
1739 --------------------------------------------------------------------------
1740 -- 115.0 30-JUN-2003 bramajey Initial Version --
1741 -- 115.1 03-JUL-2003 bramajey Changed Parameter list in cursors --
1742 -- 115.2 03-SEP-2003 bramajey Changed code to archive Tax area and --
1743 -- meaning of Payout Location --
1744 -- 115.3 15-Sep-2003 bramajey Modified csr_bank_details to archive --
1745 -- currency_code for Cheque/Cash PPMs --
1746 -- Changed code to archive Expatriate & --
1747 -- Passport. --
1748 -- 115.4 18-Sep-2003 bramajey Made changes in effective date check --
1749 -- csr_bank_details --
1750 -- 115.5 30-Sep-2003 vinaraya Included the decode in the select --
1751 -- clause in csr_bank_details --
1752 --------------------------------------------------------------------------
1753 --
1754
1755 PROCEDURE archive_employee_details (
1756 p_payroll_action_id IN NUMBER
1757 ,p_pay_assignment_action_id IN NUMBER
1758 ,p_assactid IN NUMBER
1759 ,p_assignment_id IN NUMBER
1760 ,p_curr_pymt_ass_act_id IN NUMBER
1761 ,p_date_earned IN DATE
1762 ,p_latest_period_payment_date IN DATE
1763 ,p_run_effective_date IN DATE
1764 ,p_time_period_id IN NUMBER
1765 ,p_pre_effective_date IN DATE
1766 )
1767 IS
1768 --
1769 -- Cursor to select the archived information for category 'EMPLOYEE NET PAY DISTRIBUTION'
1770 -- by core package.
1771
1772 CURSOR csr_net_pay_action_info_id
1773 IS
1774 SELECT action_information_id
1775 ,action_information1
1776 ,action_information2
1777 FROM pay_action_information
1778 WHERE action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
1779 AND action_context_id = p_assactid
1780 AND action_context_type = 'AAP';
1781 --
1782
1783 -- Cursor to select the archived information for category 'EMPLOYEE NET PAY DISTRIBUTION'
1784 -- by core package.
1785
1786 CURSOR csr_emp_det_action_info_id
1787 IS
1788 SELECT action_information_id
1789 FROM pay_action_information
1790 WHERE action_information_category = 'EMPLOYEE DETAILS'
1791 AND action_context_id = p_assactid
1792 AND action_context_type = 'AAP';
1793
1794 -- Cursor to select the tax_unit_id of the prepayment needed for archival
1795
1796 CURSOR csr_tax_unit_id
1797 IS
1798 SELECT tax_unit_id
1799 FROM pay_assignment_actions
1800 WHERE assignment_action_id = p_curr_pymt_ass_act_id;
1801 --
1802
1803
1804 -- Cursor to get the bank name,percentage and currency code
1805
1806 /*********** Bug 3166092 ***************************************/
1807 /* Modified the query to fetch bank details only when the payment
1808 type is Direct Deposit */
1809
1810 CURSOR csr_bank_details(
1811 p_personal_payment_method_id NUMBER
1812 ,p_org_payment_method_id NUMBER
1813 )
1814 IS
1815 SELECT pea.segment1 bank_name
1816 ,pea.segment2 bank_branch
1817 ,pea.segment3 account_number
1818 ,ppm.percentage percentage
1819 ,pop.currency_code
1820 FROM pay_external_accounts pea
1821 ,pay_pre_payments ppp
1822 ,pay_org_payment_methods_f pop
1823 ,pay_personal_payment_methods_f ppm
1824 WHERE ppp.assignment_action_id = p_curr_pymt_ass_act_id
1825 AND nvl(ppp.personal_payment_method_id,0) = nvl(p_personal_payment_method_id,0)
1826 AND ppp.org_payment_method_id = p_org_payment_method_id
1827 AND ppp.personal_payment_method_id = ppm.personal_payment_method_id (+)
1828 AND ppp.org_payment_method_id = pop.org_payment_method_id
1829 AND ppm.external_account_id = pea.external_account_id (+)
1830 AND p_pre_effective_date BETWEEN pop.effective_start_date
1831 AND pop.effective_end_date
1832 AND p_pre_effective_date BETWEEN nvl(ppm.effective_start_date,p_pre_effective_date)
1833 AND nvl(ppm.effective_end_date,p_pre_effective_date);
1834
1835
1836 --
1837 CURSOR csr_soft_key
1838 IS
1839 SELECT hsck.segment20 -- Tax Area
1840 ,hr_general.decode_lookup('CN_PAYOUT_LOCATION',hsck.segment22) -- Payout Location
1841 FROM hr_soft_coding_keyflex hsck
1842 ,per_all_assignments_f paaf
1843 ,pay_assignment_actions paa
1844 ,pay_payroll_actions ppa
1845 WHERE paa.assignment_action_id = p_assactid
1846 AND paa.payroll_action_id = ppa.payroll_action_id
1847 AND paa.assignment_id = paaf.assignment_id
1848 AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
1849 AND ppa.effective_date BETWEEN paaf.effective_start_date
1850 AND paaf.effective_end_date;
1851 --
1852 -- Bug 3139966 starts
1853 --
1854 CURSOR csr_person_id
1855 IS
1856 SELECT paa.person_id
1857 FROM per_all_assignments_f paa
1858 WHERE paa.assignment_id = p_assignment_id
1859 AND p_date_earned BETWEEN paa.effective_start_date
1860 AND paa.effective_end_date;
1861 --
1862 CURSOR csr_expatriate (p_person_id IN NUMBER)
1863 IS
1864 SELECT pap.per_information8 expatriate
1865 FROM per_all_people_f pap
1866 WHERE pap.person_id = p_person_id
1867 AND p_date_earned BETWEEN pap.effective_start_date
1868 AND pap.effective_end_date;
1869 --
1870 CURSOR csr_passport (p_person_id IN NUMBER)
1871 IS
1872 SELECT pei.pei_information2
1873 FROM per_people_extra_info pei
1874 WHERE pei.person_id = p_person_id
1875 AND pei.pei_information_category = 'PER_PASSPORT_INFO_CN' ;
1876 --
1877 l_passport per_people_extra_info.pei_information2%TYPE;
1878 l_person_id per_all_assignments_f.person_id%TYPE;
1879 l_expatriate per_all_people_f.per_information8%TYPE;
1880
1881 -- Bug 3139966 ends
1882
1883 l_action_info_id NUMBER;
1884 l_ovn NUMBER;
1885 l_tax_code VARCHAR2(5);
1886 l_tax_unit_id NUMBER;
1887 l_procedure VARCHAR2(80);
1888 l_bank_name VARCHAR2(100);
1889 l_bank_branch VARCHAR2(100);
1890 l_account_number VARCHAR2(100);
1891 l_percentage NUMBER;
1892 l_currency_code VARCHAR2(15);
1893 l_emp_det_act_info_id NUMBER;
1894 l_tax_area VARCHAR2(10);
1895 l_payroll_location VARCHAR2(100);
1896
1897 --
1898 BEGIN
1899 --
1900 l_procedure := g_package || '.archive_employee_details';
1901 hr_utility.set_location('Entering ' || l_procedure,10);
1902
1903 -- call generic procedure to retrieve and archive all data for
1904 -- EMPLOYEE DETAILS, ADDRESS DETAILS and EMPLOYEE NET PAY DISTRIBUTION
1905
1906 hr_utility.set_location('Opening Cursor csr_tax_unit_id',20);
1907
1908 OPEN csr_tax_unit_id;
1909 FETCH csr_tax_unit_id INTO l_tax_unit_id;
1910 CLOSE csr_tax_unit_id;
1911
1912 hr_utility.set_location('Closing Cursor csr_tax_unit_id',30);
1913
1914 hr_utility.set_location('Calling pay_emp_action_arch.get_personal_information ',40);
1915
1916 pay_emp_action_arch.get_personal_information
1917 (
1918 p_payroll_action_id => p_payroll_action_id -- archive payroll_action_id
1919 ,p_assactid => p_assactid -- archive assignment_action_id
1920 ,p_assignment_id => p_assignment_id -- current assignment_id
1921 ,p_curr_pymt_ass_act_id => p_curr_pymt_ass_act_id -- prepayment assignment_action_id
1922 ,p_curr_eff_date => p_run_effective_date -- run effective_date
1923 ,p_date_earned => p_date_earned -- payroll date_earned
1924 ,p_curr_pymt_eff_date => p_latest_period_payment_date -- latest payment date
1925 ,p_tax_unit_id => l_tax_unit_id -- tax_unit_id needed for Choose Payslip region.
1926 ,p_time_period_id => p_time_period_id -- time_period_id from per_time_periods
1927 ,p_ppp_source_action_id => NULL
1928 ,p_run_action_id => p_pay_assignment_action_id
1929 );
1930
1931 hr_utility.set_location('Returned from pay_emp_action_arch.csr_personal_information ',50);
1932
1933 hr_utility.set_location('Calling update Net Pay Distribution',60);
1934
1935 hr_utility.set_location('Opening Cursor csr_net_pay_action_info_id',70);
1936
1937 FOR net_pay_rec in csr_net_pay_action_info_id
1938
1939 LOOP
1940 --
1941 hr_utility.set_location('Opening Cursor csr_bank_details',80);
1942 OPEN csr_bank_details(
1943 net_pay_rec.action_information2
1944 ,net_pay_rec.action_information1
1945 );
1946
1947 FETCH csr_bank_details INTO l_bank_name
1948 ,l_bank_branch
1949 ,l_account_number
1950 ,l_percentage
1951 ,l_currency_code;
1952 CLOSE csr_bank_details;
1953 hr_utility.set_location('Closing Cursor csr_bank_details',90);
1954
1955 l_ovn := 1;
1956
1957 hr_utility.set_location('Archiving Bank Details',95);
1958
1959 pay_action_information_api.update_action_information
1960 (
1961 p_action_information_id => net_pay_rec.action_information_id
1962 ,p_object_version_number => l_ovn
1963 ,p_action_information5 => l_bank_name
1964 ,p_action_information6 => l_bank_branch
1965 ,p_action_information7 => l_account_number
1966 ,p_action_information12 => l_percentage
1967 ,p_action_information13 => l_currency_code
1968 );
1969 --
1970 END LOOP;
1971
1972 hr_utility.set_location('Closing Cursor csr_net_pay_action_info_id',100);
1973
1974 --
1975 -- Payroll Location available in soft coding key flexfield needs to be archived
1976 -- as this is not archived by the Core Package
1977 --
1978
1979 --
1980 -- Fetch the action_information_id of action information category EMPLOYEE DETAILS
1981 --
1982
1983 hr_utility.set_location('Opening Cursor csr_emp_det_action_info_id',110);
1984
1985
1986 OPEN csr_emp_det_action_info_id;
1987 FETCH csr_emp_det_action_info_id INTO l_emp_det_act_info_id;
1988 CLOSE csr_emp_det_action_info_id;
1989
1990
1991 hr_utility.set_location('Closing Cursor csr_emp_det_action_info_id',120);
1992
1993 --
1994 -- Fetch Payroll Location
1995 --
1996
1997 hr_utility.set_location('Opening Cursor csr_soft_key',130);
1998
1999 -- Bug 3116630 starts
2000 -- Added code to archive Tax Area
2001 --
2002 OPEN csr_soft_key;
2003 FETCH csr_soft_key
2004 INTO l_tax_area,l_payroll_location;
2005 CLOSE csr_soft_key;
2006
2007 hr_utility.set_location('Closing Cursor csr_soft_key',140);
2008 -- Bug 3139966 starts
2009 -- Added code to archive Expatriate Indicator, Passport
2010 OPEN csr_person_id;
2011 FETCH csr_person_id
2012 INTO l_person_id;
2013 CLOSE csr_person_id;
2014
2015 hr_utility.set_location('Opening Cursor csr_expatriate', 150);
2016
2017 OPEN csr_expatriate (l_person_id);
2018 FETCH csr_expatriate
2019 INTO l_expatriate;
2020 CLOSE csr_expatriate;
2021
2022 hr_utility.set_location('Closing Cursor csr_expatriate', 160);
2023
2024 hr_utility.set_location('Opening Cursor csr_passport', 170);
2025
2026 OPEN csr_passport (l_person_id);
2027 FETCH csr_passport
2028 INTO l_passport;
2029 CLOSE csr_passport;
2030 hr_utility.set_location('Closing Cursor csr_passport', 180);
2031
2032 --
2033 -- Update Payroll Location,Tax Area, Passport and Expatriate Indicator
2034 --
2035
2036 hr_utility.set_location('Updating Tax area, Payroll location, Passport and Expat',190);
2037 l_ovn := 1;
2038 pay_action_information_api.update_action_information
2039 (
2040 p_action_information_id => l_emp_det_act_info_id
2041 ,p_object_version_number => l_ovn
2042 ,p_action_information23 => l_tax_area
2043 ,p_action_information24 => l_payroll_location
2044 ,p_action_information25 => l_expatriate
2045 ,p_action_information26 => l_passport
2046 );
2047
2048 -- Bug 3116630 ends
2049
2050 hr_utility.set_location('Archiving the Legal Employer Details',200);
2051 archive_legal_employer_details
2052 (
2053 p_payroll_action_id => p_payroll_action_id
2054 ,p_employer_id => l_tax_unit_id
2055 ,p_effective_date => p_pre_effective_date
2056 );
2057
2058 -- Bug 3139966 ends
2059 hr_utility.set_location('Leaving ' || l_procedure,200);
2060 --
2061 EXCEPTION
2062 --
2063 WHEN OTHERS THEN
2064 IF csr_bank_details%ISOPEN THEN
2065 CLOSE csr_bank_details;
2066 END IF;
2067 IF csr_tax_unit_id%ISOPEN THEN
2068 CLOSE csr_tax_unit_id;
2069 END IF;
2070 IF csr_net_pay_action_info_id%ISOPEN THEN
2071 CLOSE csr_net_pay_action_info_id;
2072 END IF;
2073 IF csr_emp_det_action_info_id%ISOPEN THEN
2074 CLOSE csr_emp_det_action_info_id;
2075 END IF;
2076 IF csr_soft_key%ISOPEN THEN
2077 CLOSE csr_soft_key;
2078 END IF;
2079 IF csr_person_id%ISOPEN THEN
2080 CLOSE csr_person_id;
2081 END IF;
2082 IF csr_passport%ISOPEN THEN
2083 CLOSE csr_passport;
2084 END IF;
2085 IF csr_expatriate%ISOPEN THEN
2086 CLOSE csr_expatriate;
2087 END IF;
2088
2089 hr_utility.set_location('Error in ' || l_procedure,10);
2090 RAISE;
2091 --
2092 END archive_employee_details;
2093
2094
2095 --------------------------------------------------------------------------
2096 -- --
2097 -- Name : ARCHIVE_CODE --
2098 -- Type : PROCEDURE --
2099 -- Access : Public --
2100 -- Description : Procedure to call the internal procedures to --
2101 -- actually the archive the data. The procedure --
2102 -- called are --
2103 -- pay_apac_payslip_archive.archive_user_balances --
2104 -- pay_apac_payslip_archive.archive_user_elements --
2105 -- archive_stat_balances --
2106 -- archive_stat_elements --
2107 -- archive_employee_details --
2108 -- archive_accrual_details --
2109 -- archive_absences --
2110 -- --
2111 -- Parameters : --
2112 -- IN : p_assignment_action_id NUMBER --
2113 -- p_effective_date DATE --
2114 -- --
2115 -- OUT : N/A --
2116 -- --
2117 -- Change History : --
2118 --------------------------------------------------------------------------
2119 -- Rev# Date Userid Description --
2120 --------------------------------------------------------------------------
2121 -- 115.0 30-JUN-2003 bramajey Initial Version --
2122 -- 115.1 11-AUG-2003 bramajey Changed condition to --
2123 -- pre.locked_action_id = --
2124 -- passact.assignment_action_id --
2125 -- in csr_assignment_actions --
2126 -- 115.2 03-SEP-2003 bramajey Added code to Archive Special --
2127 -- elements --
2128 -- 115.3 20-Sep-2005 snekkala Modified cursor get_payslip_aa --
2129 -- for performance
2130 --------------------------------------------------------------------------
2131 --
2132
2133 PROCEDURE archive_code (
2134 p_assignment_action_id IN NUMBER
2135 ,p_effective_date IN DATE
2136 )
2137 IS
2138 --
2139 -- Cursor to select all the locked prepayment and payrolls by the archive
2140 -- assignment action. The records are ordered descending as we only need
2141 -- latest payroll run in the prepayment.
2142
2143 -- Bug 3580609
2144 -- Changed cursor as suggested by core
2145
2146 CURSOR get_payslip_aa(p_master_aa_id NUMBER)
2147 IS
2148 SELECT paa_arch_chd.assignment_action_id chld_arc_assignment_action_id
2149 ,paa_arch_chd.payroll_action_id arc_payroll_action_id
2150 ,paa_pre.assignment_action_id pre_assignment_action_id
2151 ,paa_run.assignment_action_id run_assignment_action_id
2152 ,paa_run.payroll_action_id run_payroll_action_id
2153 ,ppa_pre.effective_date pre_effective_date
2154 ,paa_arch_chd.assignment_id
2155 ,ppa_run.effective_date run_effective_date
2156 ,ppa_run.date_earned run_date_earned
2157 ,ptp.end_date period_end_date
2158 ,ptp.time_period_id
2159 ,ptp.start_date period_start_date
2160 ,ptp.regular_payment_date
2161 FROM pay_assignment_actions paa_arch_chd
2162 ,pay_assignment_actions paa_arch_mst
2163 ,pay_assignment_actions paa_pre
2164 ,pay_action_interlocks pai_pre
2165 ,pay_assignment_actions paa_run
2166 ,pay_action_interlocks pai_run
2167 ,pay_payroll_actions ppa_pre
2168 ,pay_payroll_actions ppa_run
2169 ,per_time_periods ptp
2170 ,per_business_groups pbg
2171 WHERE paa_arch_mst.assignment_action_id = p_master_aa_id
2172 AND paa_arch_chd.source_action_id = paa_arch_mst.assignment_action_id
2173 AND paa_arch_chd.payroll_action_id = paa_arch_mst.payroll_action_id
2174 AND ppa_pre.business_group_id = pbg.business_group_id
2175 AND pbg.business_group_id = ppa_run.business_group_id
2176 AND ppa_pre.payroll_id = ppa_run.payroll_id
2177 AND paa_arch_chd.assignment_id = paa_arch_mst.assignment_id
2178 AND pai_pre.locking_action_id = paa_arch_mst.assignment_action_id
2179 AND pai_pre.locked_action_id = paa_pre.assignment_action_id
2180 AND pai_run.locking_action_id = paa_arch_chd.assignment_action_id
2181 AND pai_run.locked_action_id = paa_run.assignment_action_id
2182 AND ppa_pre.payroll_action_id = paa_pre.payroll_action_id
2183 AND ppa_pre.action_type IN ('P','U')
2184 AND ppa_run.payroll_action_id = paa_run.payroll_action_id
2185 AND ppa_run.action_type IN ('R','Q')
2186 AND ptp.payroll_id = ppa_run.payroll_id
2187 AND ppa_run.date_earned BETWEEN ptp.start_date
2188 AND ptp.end_date
2189 -- Get the highest in sequence for this payslip
2190 AND paa_run.action_sequence =
2191 (
2192 SELECT MAX(paa_run2.action_sequence)
2193 FROM pay_assignment_actions paa_run2
2194 ,pay_action_interlocks pai_run2
2195 WHERE pai_run2.locking_action_id = paa_arch_chd.assignment_action_id
2196 AND pai_run2.locked_action_id = paa_run2.assignment_action_id
2197 );
2198
2199 /* Bug No:5634390
2200 This cursor returns actual termination date if it falls in the pay period */
2201
2202 CURSOR csr_payment_date(p_assignment_action_id NUMBER)
2203 IS
2204 SELECT pps.actual_termination_date
2205 FROM pay_payroll_actions ppa,
2206 pay_assignment_actions paa,
2207 per_time_periods ptp,
2208 per_all_assignments_f paf,
2209 per_periods_of_service pps
2210 WHERE paa.assignment_action_id = p_assignment_action_id
2211 AND ppa.payroll_action_id = paa.payroll_action_id
2212 AND ptp.payroll_id = ppa.payroll_id
2213 AND paf.assignment_id = paa.assignment_id
2214 AND pps.period_of_service_id = paf.period_of_service_id
2215 AND ppa.date_earned between ptp.start_date AND ptp.end_date
2216 AND pps.actual_termination_date between ptp.start_date AND ptp.end_date;
2217
2218
2219 --
2220 l_procedure VARCHAR2(100);
2221 l_payment_date DATE :=NULL;
2222 --
2223 BEGIN
2224 --
2225 l_procedure := g_package || '.archive_code';
2226 hr_utility.set_location('Entering ' || l_procedure,10);
2227
2228 -- Bug 3580609
2229 -- Create Child Assignment Actions
2230 pay_core_payslip_utils.generate_child_actions(p_assignment_action_id
2231 ,p_effective_date);
2232
2233 hr_utility.set_location('Opening Cursor get_payslip_aa',15);
2234
2235 -- Bug 3580609
2236 -- use cursor suggested by core
2237 FOR csr_rec IN get_payslip_aa(p_assignment_action_id)
2238 LOOP
2239 --
2240
2241 hr_utility.set_location('csr_rec.master_assignment_action_id = ' || csr_rec.run_assignment_action_id,20);
2242 hr_utility.set_location('csr_rec.pre_assignment_action_id = ' || csr_rec.pre_assignment_action_id,30);
2243
2244 -- Added for bug 5634390
2245 open csr_payment_date(csr_rec.run_assignment_action_id);
2246 fetch csr_payment_date into l_payment_date;
2247 if csr_payment_date%NOTFOUND then
2248 l_payment_date := csr_rec.regular_payment_date;
2249 end if;
2250 close csr_payment_date;
2251
2252 --
2253 -- Call to procedure to archive User Configurable Balances
2254 --
2255
2256 pay_apac_payslip_archive.archive_user_balances
2257 (
2258 p_arch_assignment_action_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
2259 ,p_run_assignment_action_id => csr_rec.run_assignment_action_id -- payroll assignment action id
2260 ,p_pre_effective_date => csr_rec.pre_effective_date -- prepayment effecive date
2261 );
2262
2263
2264 --
2265 -- Call to procedure to archive User Configurable Elements
2266 --
2267
2268 pay_apac_payslip_archive.archive_user_elements
2269 (
2270 p_arch_assignment_action_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action
2271 ,p_pre_assignment_action_id => csr_rec.pre_assignment_action_id -- prepayment assignment action id
2272 ,p_latest_run_assact_id => csr_rec.run_assignment_action_id -- payroll assignment action id
2273 ,p_pre_effective_date => csr_rec.pre_effective_date -- prepayment effective date
2274 );
2275
2276
2277 --
2278 -- Call to procedure to archive Statutory Elements
2279 --
2280
2281 archive_stat_elements
2282 (
2283 p_assignment_action_id => csr_rec.pre_assignment_action_id -- prepayment assignment action id
2284 ,p_effective_date => csr_rec.pre_effective_date -- prepayment effective date
2285 ,p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
2286 );
2287
2288
2289 -- Bug 3116630 starts
2290 -- Call to procedure to archive Special Elements
2291 --
2292
2293 archive_special_elements
2294 (
2295 p_assignment_action_id => csr_rec.run_assignment_action_id -- payroll assignment action id
2296 ,p_effective_date => csr_rec.pre_effective_date -- prepayment effective date
2297 ,p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
2298 );
2299
2300 -- Bug 3116630 ends
2301
2302
2303 --
2304 -- Call to procedure to archive Statutory Balances
2305 --
2306
2307 archive_stat_balances
2308 (
2309 p_assignment_action_id => csr_rec.run_assignment_action_id -- payroll assignment action
2310 ,p_assignment_id => csr_rec.assignment_id -- assignment id
2311 ,p_date_earned => csr_rec.run_date_earned -- payroll date earned
2312 ,p_effective_date => csr_rec.pre_effective_date -- prepayments effective date
2313 ,p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
2314 );
2315
2316
2317 --
2318 -- Call to procedure to archive Employee Details
2319 --
2320
2321 archive_employee_details
2322 (
2323 p_payroll_action_id => csr_rec.arc_payroll_action_id -- archive payroll action id
2324 ,p_assactid => csr_rec.chld_arc_assignment_action_id -- archive action id
2325 ,p_pay_assignment_action_id => csr_rec.run_assignment_action_id -- payroll run action id
2326 ,p_assignment_id => csr_rec.assignment_id -- assignment_id
2327 ,p_curr_pymt_ass_act_id => csr_rec.pre_assignment_action_id -- prepayment assignment_action_id
2328 ,p_date_earned => csr_rec.run_date_earned -- payroll date_earned
2329 ,p_latest_period_payment_date => l_payment_date -- latest payment date
2330 ,p_run_effective_date => csr_rec.run_effective_date -- run effective Date
2331 ,p_time_period_id => csr_rec.time_period_id -- time_period_id from per_time_periods
2332 ,p_pre_effective_date => csr_rec.pre_effective_date -- prepayment effective date
2333 );
2334
2335
2336 --
2337 -- Call to procedure to archive accrual details
2338 --
2339
2340 archive_accrual_details
2341 (
2342 p_payroll_action_id => csr_rec.run_payroll_action_id -- latest payroll action id
2343 ,p_time_period_id => csr_rec.time_period_id -- latest period time period id
2344 ,p_assignment_id => csr_rec.assignment_id -- assignment id
2345 ,p_date_earned => csr_rec.run_date_earned -- latest payroll date earned
2346 ,p_effective_date => csr_rec.pre_effective_date -- prepayment effective date
2347 ,p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
2348 ,p_assignment_action_id => csr_rec.run_assignment_action_id -- payroll run action id
2349 ,p_period_end_date => csr_rec.period_end_date -- latest period end date
2350 ,p_period_start_date => csr_rec.period_start_date -- latest period start date
2351 );
2352
2353
2354 --
2355 -- Call to procedure to archive absences
2356 --
2357
2358 archive_absences
2359 (
2360 p_arch_act_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
2361 ,p_assg_act_id => csr_rec.run_assignment_action_id -- payroll run action id
2362 ,p_pre_effective_date => csr_rec.pre_effective_date -- prepayment effective date
2363 );
2364
2365 --
2366 END LOOP;
2367
2368 hr_utility.set_location('Closing Cursor csr_assignment_actions',40);
2369 hr_utility.set_location('Leaving ' || l_procedure,50);
2370 --
2371 EXCEPTION
2372 WHEN OTHERS THEN
2373 IF get_payslip_aa%ISOPEN THEN
2374 close get_payslip_aa;
2375 END IF;
2376 hr_utility.set_location('Error in ' || l_procedure,50);
2377 RAISE;
2378 --
2379 END archive_code;
2380 --
2381 END pay_cn_payslip_archive;