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