[Home] [Help]
PACKAGE BODY: APPS.PAY_NZ_PAYSLIP_ARCHIVE
Source
1 PACKAGE BODY pay_nz_payslip_archive AS
2 /* $Header: pynzparc.pkb 120.4.12010000.3 2008/10/23 04:49:46 skshin ship $ */
3
4 ---------------------------------------------------------------------+
5 -- This is a global variable used to store Archive assignment action id
6 --------------------------------------------------------------------+
7
8
9 g_archive_pact NUMBER;
10
11
12 --------------------------------------------------------------------+
13 -- This procedure returns a sql string to SELECT a range
14 -- of assignments eligible for archival.
15 -- It calls pay_apac_payslip_archive.range_code that archives the EIT
16 -- definition and payroll level data (Messages, employer address details etc)
17 --------------------------------------------------------------------+
18
19 PROCEDURE range_code(p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE,
20 p_sql OUT NOCOPY VARCHAR2) IS
21
22
23 BEGIN
24
25 hr_utility.set_location('Start of range_code',1);
26
27
28 --------------------------------------------------------------------------------+
29 -- Call to range_code from common apac package 'pay_apac_payslip_archive'
30 -- to archive the payroll action level data and EIT defintions.
31 --------------------------------------------------------------------------------+
32
33 pay_apac_payslip_archive.range_code(p_payroll_action_id => p_payroll_action_id);
34
35
36 --
37 -- Bug 3580568
38 --
39 pay_core_payslip_utils.range_cursor(p_payroll_action_id,
40 p_sql);
41
42 hr_utility.set_location('End of range_code',2);
43
44 EXCEPTION
45 WHEN OTHERS THEN
46 hr_utility.set_location('Error in initialization_code',2);
47 RAISE;
48
49 END range_code;
50
51
52
53
54 --------------------------------------------------------------------+
55 -- This procedure is used to set global contexts .
56 -- The globals used are PL/SQL tables i.e.(g_user_balance_table and g_element_table)
57 -- It calls the procedure pay_apac_archive.initialization_code that
58 -- actually sets the global variables and populates the global tables.
59 --------------------------------------------------------------------+
60
61 PROCEDURE initialization_code (p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE) IS
62
63
64 BEGIN
65
66 hr_utility.set_location('Start of initialization_code',1);
67
68 g_archive_pact := p_payroll_action_id;
69
70 ------------------------------------------------------------------+
71 -- Call to common package procedure pay_apac_payslip_archive.
72 -- initialization_code to to set the global tables for EIT
73 -- that will be used by each thread in multi-threading.
74 ------------------------------------------------------------------+
75
76 pay_apac_payslip_archive.initialization_code(p_payroll_action_id => p_payroll_action_id);
77
78 hr_utility.set_location('End of initialization_code',2);
79
80 EXCEPTION
81 WHEN OTHERS THEN
82 hr_utility.set_location('Error in initialization_code',2);
83 RAISE;
84
85 END initialization_code;
86
87
88 --------------------------------------------------------------------+
89 -- This procedure further restricts the assignment_id's
90 -- returned by range_code
91 -- It filters the assignments selected by range_code procedure
92
93 -- Since the Payslip is given for each prepayment, the data should
94 -- be archived for each prepayment.
95 -- So, the successfully completed prepayments are selected and locked
96 -- by the archival action.
97 -- All the successfully completed runs under the prepayments are also
98 -- selected and locked by archival to make the core 'Choose Payslip'
99 -- work for NZ.
100 -- The archive will not pickup already archived prepayments.
101 --------------------------------------------------------------------+
102
103 PROCEDURE assignment_action_code (p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE,
104 p_start_person IN per_all_people_f.person_id%TYPE,
105 p_end_person IN per_all_people_f.person_id%TYPE,
106 p_chunk IN NUMBER) IS
107
108 BEGIN
109
110 hr_utility.trace('Start of assignment action code');
111
112 --
113 -- Bug 3580568
114 --
115 pay_core_payslip_utils.action_creation (
116 p_payroll_action_id,
117 p_start_person,
118 p_end_person,
119 p_chunk,
120 'NZ_PAYSLIP_ARCHIVE',
121 'NZ');
122
123
124
125 hr_utility.trace('End of Assignment action code');
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 hr_utility.trace('Error occured in Assignment action code');
130 RAISE;
131
132 END assignment_action_code;
133
134
135
136 --------------------------------------------------------------------+
137 -- This procedure archives the elements and run result values.
138 -- It uses NZ Pay Advice view 'pay_nz_soe_run_elements_v'
139 -- to get the elements and corresponding payments.
140 --------------------------------------------------------------------+
141
142 PROCEDURE archive_stat_elements(p_assignment_action_id IN NUMBER,
143 p_assignment_id IN NUMBER,
144 p_effective_date IN DATE,
145 p_assact_id IN NUMBER) IS
146
147 -- Cursor to get all the elements processed for the assignment in the
148 -- prepayment.
149
150 CURSOR csr_std_elements1(p_assignment_action_id NUMBER,
151 p_assignment_id NUMBER)
152 IS
153 SELECT element_reporting_name
154 ,classification_name
155 ,payment
156 FROM pay_nz_soe_run_elements_v1
157 WHERE assignment_action_id = p_assignment_action_id
158 AND assignment_id = p_assignment_id
159 AND classification_name IS NOT NULL
160 AND element_reporting_name NOT IN ('SSCWT Deduction','ESCT Deduction'); -- bug 7494658
161
162 CURSOR csr_std_elements2(p_assignment_action_id NUMBER,
163 p_assignment_id NUMBER)
164 IS
165 SELECT element_reporting_name
166 ,classification_name
167 ,payment
168 FROM pay_nz_soe_run_elements_v2
169 WHERE assignment_action_id = p_assignment_action_id
170 AND assignment_id = p_assignment_id
171 AND classification_name IS NOT NULL
172 AND element_reporting_name NOT IN ('SSCWT Deduction','ESCT Deduction'); -- bug 7494658
173
174 l_action_info_id NUMBER;
175 l_ovn NUMBER;
176 l_procedure_name CONSTANT VARCHAR2(80) := 'archive_stat_elements';
177
178 BEGIN
179
180 hr_utility.set_location('Entering procedure ' || l_procedure_name,10);
181
182 FOR csr_rec IN csr_std_elements1(p_assignment_action_id,p_assignment_id)
183
184 LOOP
185
186 hr_utility.set_location('Archiving Standard Element Details',20);
187
188 pay_action_information_api.create_action_information
189 ( p_action_information_id => l_action_info_id
190 , p_action_context_id => p_assact_id
191 , p_action_context_type => 'AAP'
192 , p_object_version_number => l_ovn
193 , p_effective_date => p_effective_date
194 , p_source_id => NULL
195 , p_source_text => NULL
196 , p_action_information_category => 'APAC ELEMENTS'
197 , p_action_information1 => csr_rec.element_reporting_name
198 , p_action_information2 => NULL
199 , p_action_information3 => NULL
200 , p_action_information4 => csr_rec.classification_name
201 , p_action_information5 => fnd_number.number_to_canonical (csr_rec.payment) -- Bug 3604103
202 );
203
204 END LOOP;
205
206 FOR csr_rec IN csr_std_elements2(p_assignment_action_id,p_assignment_id)
207
208 LOOP
209
210 hr_utility.set_location('Archiving Standard Element Details',20);
211
212 pay_action_information_api.create_action_information
213 ( p_action_information_id => l_action_info_id
214 , p_action_context_id => p_assact_id
215 , p_action_context_type => 'AAP'
216 , p_object_version_number => l_ovn
217 , p_effective_date => p_effective_date
218 , p_source_id => NULL
219 , p_source_text => NULL
220 , p_action_information_category => 'APAC ELEMENTS'
221 , p_action_information1 => csr_rec.element_reporting_name
222 , p_action_information2 => NULL
223 , p_action_information3 => NULL
224 , p_action_information4 => csr_rec.classification_name
225 , p_action_information5 => fnd_number.number_to_canonical (csr_rec.payment) -- Bug 3604103
226 );
227
228 END LOOP;
229
230 hr_utility.set_location('End of archive Standard Element',4);
231 hr_utility.set_location('Leaving procedure ' || l_procedure_name,10);
232
233 EXCEPTION
234 WHEN OTHERS THEN
235 hr_utility.set_location('Error raised in archiving Standard Elements ',5);
236 RAISE;
237
238 END archive_stat_elements;
239
240
241
242
243 --------------------------------------------------------------------+
244 -- Procedure to archive the Statutory balances
245 --------------------------------------------------------------------+
246
247 PROCEDURE archive_balances( p_effective_date IN DATE
248 ,p_assact_id IN NUMBER
249 ,p_narrative IN VARCHAR2
250 ,p_ytd IN NUMBER) IS
251
252 l_action_info_id NUMBER;
253 l_ovn NUMBER;
254 l_procedure_name CONSTANT VARCHAR2(80) := 'archive_balances';
255
256 BEGIN
257
258 hr_utility.set_location('Entering procedure ' || l_procedure_name,10);
259 hr_utility.set_location('archiving balances :',10);
260
261 -- Archive Statutory balances
262
263 pay_action_information_api.create_action_information
264 ( p_action_information_id => l_action_info_id
265 , p_action_context_id => p_assact_id
266 , p_action_context_type => 'AAP'
267 , p_object_version_number => l_ovn
268 , p_effective_date => p_effective_date
269 , p_source_id => NULL
270 , p_source_text => NULL
271 , p_action_information_category => 'APAC BALANCES'
272 , p_action_information1 => p_narrative
273 , p_action_information2 => NULL
274 , p_action_information3 => NULL
275 , p_action_information4 => fnd_number.number_to_canonical(p_ytd) -- Bug 3604103
276 );
277
278
279 EXCEPTION
280 WHEN OTHERS THEN
281 hr_utility.set_location('Error in archiving balance :',11);
282 RAISE;
283
284 END archive_balances;
285
286
287
288
289 --------------------------------------------------------------------+
290 -- Procedure to calculate the Statutory balances values
291 -- Calls procedure archive_balances to acutally archives
292 -- the Statutory balance values
293 --------------------------------------------------------------------+
294
295 PROCEDURE archive_stat_balances(p_assignment_action_id IN NUMBER
296 ,p_assignment_id IN NUMBER
297 ,p_date_earned IN DATE
298 ,p_effective_date IN DATE
299 ,p_assact_id IN NUMBER) IS
300
301 l_gross_this_pay NUMBER;
302 l_other_deductions_this_pay NUMBER;
303 l_tax_deductions_this_pay NUMBER;
304 l_gross_ytd NUMBER;
305 l_other_deductions_ytd NUMBER;
306 l_tax_deductions_ytd NUMBER;
307 l_non_tax_allow_this_pay NUMBER;
308 l_non_tax_allow_ytd NUMBER;
309 l_pre_tax_deductions_this_pay NUMBER;
310 l_pre_tax_deductions_ytd NUMBER;
311 l_net_payment_ytd NUMBER;
312 l_narrative VARCHAR2(150);
313 l_procedure_name CONSTANT VARCHAR2(80) := 'archive_stat_balances';
314
315 BEGIN
316
317 hr_utility.set_location('Entering procedure ' || l_procedure_name,10);
318 hr_utility.set_location('Calling balance_total from pay_nz_soe_pkg',20);
319
320 -- Get the totals of the statutory balances
321
322 pay_nz_soe_pkg.balance_totals(p_assignment_id,
323 p_assignment_action_id,
324 p_date_earned,
325 l_gross_this_pay,
326 l_other_deductions_this_pay,
327 l_tax_deductions_this_pay,
328 l_gross_ytd,
329 l_other_deductions_ytd,
330 l_tax_deductions_ytd,
331 l_non_tax_allow_this_pay,
332 l_non_tax_allow_ytd,
333 l_pre_tax_deductions_this_pay,
334 l_pre_tax_deductions_ytd);
335
336 l_gross_ytd := l_gross_ytd + l_pre_tax_deductions_ytd;
337
338 l_net_payment_ytd := l_gross_ytd
339 + l_non_tax_allow_ytd
340 - l_other_deductions_ytd
341 - l_tax_deductions_ytd;
342
343
344
345 l_narrative := 'Taxable Earnings';
346
347 hr_utility.set_location('Archiving value for ' || l_narrative,30);
348
349 archive_balances(p_effective_date =>p_effective_date
350 ,p_assact_id =>p_assact_id
351 ,p_narrative =>l_narrative
352 ,p_ytd =>l_gross_ytd);
353
354
355
356 l_narrative := 'Non Taxable Allowances';
357
358 hr_utility.set_location('Archiving value for ' || l_narrative,40);
359
360 archive_balances(p_effective_date =>p_effective_date
361 ,p_assact_id =>p_assact_id
362 ,p_narrative =>l_narrative
363 ,p_ytd =>l_non_tax_allow_ytd);
364
365
366
367 l_narrative := 'Tax Deductions';
368
369 hr_utility.set_location('Archiving value for ' || l_narrative,50);
370
371 archive_balances(p_effective_date =>p_effective_date
372 ,p_assact_id =>p_assact_id
373 ,p_narrative =>l_narrative
374 ,p_ytd =>l_tax_deductions_ytd);
375
376
377
378 l_narrative := 'Other Deductions ';
379
380 hr_utility.set_location('Archiving value for ' || l_narrative,60);
381
382 archive_balances(p_effective_date =>p_effective_date
383 ,p_assact_id =>p_assact_id
384 ,p_narrative =>l_narrative
385 ,p_ytd =>l_other_deductions_ytd);
386
387
388
389 l_narrative := 'Net Payment ';
390
391 hr_utility.set_location('Archiving value for ' || l_narrative,70);
392
393 archive_balances(p_effective_date =>p_effective_date
394 ,p_assact_id => p_assact_id
395 ,p_narrative => l_narrative
396 ,p_ytd =>l_net_payment_ytd);
397
398
399 hr_utility.set_location('End of Archiving Stat Balances ',80);
400
401 hr_utility.set_location('Leaving procedure ' || l_procedure_name,90);
402
403 EXCEPTION
404 WHEN OTHERS THEN
405 hr_utility.set_location('error in calling archive balance code :',11);
406 RAISE;
407
408 END archive_stat_balances;
409
410
411
412
413 --------------------------------------------------------------------------------------+
414 -- This procedure calls 'pay_emp_action_arch.get_personal_information' that actually
415 -- archives the employee details,employee address details, Employer Address Details
416 -- and Net Pay Distribution inforamation. Procedure 'get_personal_informatio' is
417 -- is passed tax_unit_id to make core provided 'Choose Payslip' work for us.
418 -- The action DF structures used are -
419 -- ADDRESS DETAILS
420 -- EMPLOYEE DETAILS
421 -- EMPLOYEE NET PAY DISTRIBUTION
422 -- EMPLOYEE OTHER INFORMATION
423 -- After core procedure completes the archival, the information stored for category
424 -- EMPLOYEE_NET_PAY_DISTRIBUTION is updated with bank name specific to New Zealand
425 -- using action_information5. Core procedure actually stores the bank branch number in
426 -- action_information5.
427 -- The NZ legsilative data (Tax Code) archival is also done in this procedure.
428 ---------------------------------------------------------------------------------------+
429
430 PROCEDURE archive_employee_details (p_payroll_action_id IN NUMBER
431 , p_pay_assignment_action_id IN NUMBER
432 , p_assactid IN NUMBER
433 , p_assignment_id IN NUMBER
434 , p_curr_pymt_ass_act_id IN NUMBER
435 , p_date_earned IN DATE
436 , p_curr_pymt_eff_date IN DATE
437 , p_run_effective_date IN DATE
438 , p_time_period_id IN NUMBER ) IS
439
440 -- Cursor to select the archived information for category 'EMPLOYEE NET PAY DISTRIBUTION'
441 -- by core package. Here actoin_information5 is the archived bank branch number
442
443 CURSOR csr_action_information_id(p_assact_id NUMBER)
444 IS
445 SELECT action_information_id
446 ,action_information5
447 FROM pay_action_information
448 WHERE action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
449 AND action_context_id = p_assact_id
450 AND action_context_type = 'AAP';
451
452
453
454 -- Cursor to select the tax_unit_id of the prepayment needed for archival
455
456 CURSOR csr_tax_unit_id(p_assignment_action_id NUMBER)
457 IS
458 SELECT tax_unit_id
459 FROM pay_assignment_actions
460 WHERE assignment_action_id = p_assignment_action_id;
461
462
463
464 -- Cursor to give the bank name for the bank code from the look up
465
466 CURSOR csr_bank_name(p_bank_code VARCHAR2)
467 IS
468 SELECT hr_general_utilities.Get_lookup_Meaning('NZ_BANK',p_bank_code)
469 FROM dual;
470
471
472 l_action_info_id NUMBER;
473 l_ovn NUMBER;
474 l_tax_code VARCHAR2(5);
475 l_tax_unit_id NUMBER;
476 l_procedure_name CONSTANT VARCHAR2(80) := 'archive_employee_details';
477 l_bank_name VARCHAR2(100);
478 l_bank_code VARCHAR2(2);
479
480 BEGIN
481
482 hr_utility.set_location('Entering procedure '|| l_procedure_name,10);
483
484 -- call generic procedure to retrieve and archive all data for
485 -- EMPLOYEE DETAILS, ADDRESS DETAILS and EMPLOYEE NET PAY DISTRIBUTION
486
487
488 OPEN csr_tax_unit_id(p_curr_pymt_ass_act_id);
489 FETCH csr_tax_unit_id INTO l_tax_unit_id;
490 CLOSE csr_tax_unit_id;
491
492 hr_utility.set_location('Calling pay_emp_action_arch.get_personal_information ',20);
493
494 pay_emp_action_arch.get_personal_information
495 (p_payroll_action_id => p_payroll_action_id -- archive payroll_action_id
496 , p_assactid => p_assactid -- archive assignment_action_id
497 , p_assignment_id => p_assignment_id -- current assignment_id
498 , p_curr_pymt_ass_act_id => p_curr_pymt_ass_act_id -- prepayment assignment_action_id
499 , p_curr_eff_date => p_run_effective_date -- run effective_date
500 , p_date_earned => p_date_earned -- payroll date_earned
501 , p_curr_pymt_eff_date => p_curr_pymt_eff_date -- prepayment effective_date
502 , p_tax_unit_id => l_tax_unit_id -- tax_unit_id needed for Choose Payslip region.
503 , p_time_period_id => p_time_period_id -- payroll time_period_id
504 , p_ppp_source_action_id => NULL
505 , p_run_action_id => p_pay_assignment_action_id
506 );
507
508 hr_utility.set_location('Returned from pay_emp_action_arch.csr_personal_information ',30);
509
510 -- Retrieve and Archive the NZ specific(tax code) employee details
511
512 l_tax_code := pay_nz_soe_pkg.get_tax_code(p_pay_assignment_action_id);
513
514 hr_utility.set_location('Archiving NZ EMPLOYEE DETAILS',60);
515
516 pay_action_information_api.create_action_information
517 ( p_action_information_id => l_action_info_id
518 , p_action_context_id => p_assactid
519 , p_action_context_type => 'AAP'
520 , p_object_version_number => l_ovn
521 , p_effective_date => p_curr_pymt_eff_date
522 , p_source_id => NULL
523 , p_source_text => NULL
524 , p_action_information_category => 'NZ EMPLOYEE DETAILS'
525 , p_action_information1 => NULL
526 , p_action_information2 => NULL
527 , p_action_information3 => NULL
528 , p_action_information21 => l_tax_code
529 );
530
531
532 hr_utility.set_location('Calling update Net Pay Distribution',80);
533
534 -- Update Net Pay Distribution record with Bank name
535 -- Since Core package puts the bank branch number in action_information5
536 -- the bank name is obtained using this information
537
538 FOR net_pay_rec in csr_action_information_id(p_assactid)
539
540 LOOP
541
542 l_bank_code := substr(net_pay_rec.action_information5,1,2);
543
544 OPEN csr_bank_name(l_bank_code);
545 FETCH csr_bank_name INTO l_bank_name;
546 CLOSE csr_bank_name;
547
548 l_ovn := 1;
549
550 pay_action_information_api.update_action_information
551 ( p_action_information_id => net_pay_rec.action_information_id
552 , p_object_version_number => l_ovn
553 , p_action_information9 => l_bank_name
554 );
555
556 END LOOP;
557
558 hr_utility.set_location('End of archive_employee_details',90);
559
560 EXCEPTION
561 WHEN OTHERS THEN
562 hr_utility.set_location('Error raised in archiving Employee details ',5);
563 RAISE;
564
565 END archive_employee_details;
566
567
568
569
570 --------------------------------------------------------------------+
571 -- Procedure to archive Accrual and Absence Details. It uses
572 -- NZ Pay Advice views as
573 -- pay_nz_asg_leave_taken_v - for leave details
574 --------------------------------------------------------------------+
575
576 PROCEDURE archive_accrual_details(p_payroll_action_id IN NUMBER
577 ,p_time_period_id IN NUMBER
578 ,p_assignment_id IN NUMBER
579 ,p_date_earned IN DATE
580 ,p_effective_date IN DATE
581 ,p_assact_id IN NUMBER
582 ,p_assignment_action_id IN NUMBER
583 ,p_period_end_date IN DATE) IS
584
585 -- Cursor to get the absence details based on NZ Pay Advice leaves view.
586
587 CURSOR csr_leave_taken1(p_time_period_id NUMBER
588 ,p_assignment_id NUMBER
589 ,p_date_earned DATE)
590 IS
591 SELECT element_reporting_name
592 ,start_date
593 ,end_date
594 ,absence_duration
595 FROM pay_nz_asg_leave_taken_v1
596 WHERE time_period_id = p_time_period_id
597 AND assignment_id = p_assignment_id
598 AND date_earned = p_date_earned;
599
600
601 CURSOR csr_leave_taken2(p_time_period_id NUMBER
602 ,p_assignment_id NUMBER
603 ,p_date_earned DATE)
604 IS
605 SELECT element_reporting_name
606 ,start_date
607 ,end_date
608 ,absence_duration
609 FROM pay_nz_asg_leave_taken_v2
610 WHERE time_period_id = p_time_period_id
611 AND assignment_id = p_assignment_id
612 AND date_earned = p_date_earned;
613
614 CURSOR csr_leave_taken3(p_time_period_id NUMBER
615 ,p_assignment_id NUMBER
616 ,p_date_earned DATE)
617 IS
618 SELECT element_reporting_name
619 ,start_date
620 ,end_date
621 ,absence_duration
622 FROM pay_nz_asg_leave_taken_v3
623 WHERE time_period_id = p_time_period_id
624 AND assignment_id = p_assignment_id
625 AND date_earned = p_date_earned;
626
627 CURSOR csr_leave_taken4(p_time_period_id NUMBER
628 ,p_assignment_id NUMBER
629 ,p_date_earned DATE)
630 IS
631 SELECT element_reporting_name
632 ,start_date
633 ,end_date
634 ,absence_duration
635 FROM pay_nz_asg_leave_taken_v4
636 WHERE time_period_id = p_time_period_id
637 AND assignment_id = p_assignment_id
638 AND date_earned = p_date_earned;
639
640 -- Cursor to get the accrual details
641
642 CURSOR csr_leave_balance(p_assignment_action_id NUMBER
643 ,p_assignment_id NUMBER)
644 IS
645 SELECT pap.accrual_plan_name
646 ,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
647 ,pap.accrual_units_of_measure
648 ,ppa.payroll_id
649 ,pap.business_group_id
650 ,pap.accrual_plan_id
651 FROM pay_accrual_plans pap,
652 pay_element_types_f pet,
653 pay_element_links_f pel,
654 pay_element_entries_f pee,
655 pay_assignment_actions paa,
656 pay_payroll_actions ppa
657 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
658 AND pel.element_type_id = pet.element_type_id
659 AND pee.element_link_id = pel.element_link_id
660 AND paa.assignment_id = pee.assignment_id
661 AND ppa.payroll_action_id = paa.payroll_action_id
662 AND pap.accrual_category = 'NZAL'
663 AND ppa.action_type IN('R','Q')
664 AND ppa.action_status = 'C'
665 AND ppa.date_earned BETWEEN pet.effective_start_date
666 AND pet.effective_end_date
667 AND ppa.date_earned BETWEEN pel.effective_start_date
668 AND pel.effective_end_date
669 AND ppa.date_earned BETWEEN pee.effective_start_date
670 AND pee.effective_end_date
671 AND paa.assignment_id = p_assignment_id
672 AND paa.assignment_action_id = p_assignment_action_id;
673
674 l_accrual_uom pay_accrual_plans.accrual_units_of_measure%TYPE;
675 l_action_info_id NUMBER;
676 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%TYPE;
677 l_accrual_category pay_accrual_plans.accrual_category%TYPE;
678 l_annual_leave_balance NUMBER;
679 l_ovn NUMBER;
680 l_payroll_id NUMBER;
681 l_bg_id NUMBER;
682 l_annual_leave_accrual_plan_id NUMBER;
683 l_start_date VARCHAR2(20);
684 l_end_date VARCHAR2(20);
685
686 BEGIN
687
688 hr_utility.set_location('Start of accrual archival code',1);
689
690 OPEN csr_leave_balance(p_assignment_action_id,p_assignment_id);
691 FETCH csr_leave_balance INTO
692 l_accrual_plan_name,
693 l_accrual_category,
694 l_accrual_uom,
695 l_payroll_id,
696 l_bg_id,
697 l_annual_leave_accrual_plan_id;
698 CLOSE csr_leave_balance;
699
700 -- Call to get annual leave balance
701
702 l_annual_leave_balance := hr_nz_holidays.get_net_accrual(p_assignment_id
703 ,l_payroll_id
704 ,l_bg_id
705 ,l_annual_leave_accrual_plan_id
706 ,p_period_end_date);
707
708
709 hr_utility.set_location('Archiving Annual leave information',2);
710
711 IF l_accrual_plan_name IS NOT NULL AND l_annual_leave_balance IS NULL THEN
712 l_annual_leave_balance := 0;
713 END IF;
714
715 IF l_accrual_plan_name IS NOT NULL THEN
716
717 pay_action_information_api.create_action_information
718 ( p_action_information_id => l_action_info_id
719 , p_action_context_id => p_assact_id
720 , p_action_context_type => 'AAP'
721 , p_object_version_number => l_ovn
722 , p_effective_date => p_effective_date
723 , p_source_id => NULL
724 , p_source_text => NULL
725 , p_action_information_category => 'APAC ACCRUALS'
726 , p_action_information1 => l_accrual_plan_name
727 , p_action_information2 => l_accrual_category
728 , p_action_information4 => fnd_number.number_to_canonical(round(l_annual_leave_balance,2)) -- Bug 3604103
729 , p_action_information5 => l_accrual_uom
730 );
731
732 END IF;
733
734 hr_utility.set_location('End of accrual archival ',1);
735
736 hr_utility.set_location('Start of leave archival code',1);
737
738 FOR csr_rec IN csr_leave_taken1(p_time_period_id,p_assignment_id,p_date_earned)
739
740 LOOP
741
742 l_start_date := fnd_date.date_to_canonical(csr_rec.start_date);
743 l_end_date := fnd_date.date_to_canonical(csr_rec.end_date);
744
745 pay_action_information_api.create_action_information
746 ( p_action_information_id => l_action_info_id
747 , p_action_context_id => p_assact_id
748 , p_action_context_type => 'AAP'
749 , p_object_version_number => l_ovn
750 , p_effective_date => p_effective_date
751 , p_source_id => NULL
752 , p_source_text => NULL
753 , p_action_information_category => 'APAC ABSENCES'
754 , p_action_information1 => NULL
755 , p_action_information2 => csr_rec.element_reporting_name
756 , p_action_information4 => l_start_date
757 , p_action_information5 => l_end_date
758 , p_action_information6 => fnd_number.number_to_canonical(csr_rec.absence_duration) -- Bug 3604103
759 , p_action_information7 => NULL
760 );
761
762 END LOOP;
763
764 FOR csr_rec IN csr_leave_taken2(p_time_period_id,p_assignment_id,p_date_earned)
765
766 LOOP
767
768 l_start_date := fnd_date.date_to_canonical(csr_rec.start_date);
769 l_end_date := fnd_date.date_to_canonical(csr_rec.end_date);
770
771 pay_action_information_api.create_action_information
772 ( p_action_information_id => l_action_info_id
773 , p_action_context_id => p_assact_id
774 , p_action_context_type => 'AAP'
775 , p_object_version_number => l_ovn
776 , p_effective_date => p_effective_date
777 , p_source_id => NULL
778 , p_source_text => NULL
779 , p_action_information_category => 'APAC ABSENCES'
780 , p_action_information1 => NULL
781 , p_action_information2 => csr_rec.element_reporting_name
782 , p_action_information4 => l_start_date
783 , p_action_information5 => l_end_date
784 , p_action_information6 => fnd_number.number_to_canonical(csr_rec.absence_duration) -- Bug 3604103
785 , p_action_information7 => NULL
786 );
787
788 END LOOP;
789
790 FOR csr_rec IN csr_leave_taken3(p_time_period_id,p_assignment_id,p_date_earned)
791
792 LOOP
793
794 l_start_date := fnd_date.date_to_canonical(csr_rec.start_date);
795 l_end_date := fnd_date.date_to_canonical(csr_rec.end_date);
796
797 pay_action_information_api.create_action_information
798 ( p_action_information_id => l_action_info_id
799 , p_action_context_id => p_assact_id
800 , p_action_context_type => 'AAP'
801 , p_object_version_number => l_ovn
802 , p_effective_date => p_effective_date
803 , p_source_id => NULL
804 , p_source_text => NULL
805 , p_action_information_category => 'APAC ABSENCES'
806 , p_action_information1 => NULL
807 , p_action_information2 => csr_rec.element_reporting_name
808 , p_action_information4 => l_start_date
809 , p_action_information5 => l_end_date
810 , p_action_information6 => fnd_number.number_to_canonical(csr_rec.absence_duration) -- Bug 3604103
811 , p_action_information7 => NULL
812 );
813
814 END LOOP;
815
816 FOR csr_rec IN csr_leave_taken4(p_time_period_id,p_assignment_id,p_date_earned)
817
818 LOOP
819
820 l_start_date := fnd_date.date_to_canonical(csr_rec.start_date);
821 l_end_date := fnd_date.date_to_canonical(csr_rec.end_date);
822
823 pay_action_information_api.create_action_information
824 ( p_action_information_id => l_action_info_id
825 , p_action_context_id => p_assact_id
826 , p_action_context_type => 'AAP'
827 , p_object_version_number => l_ovn
828 , p_effective_date => p_effective_date
829 , p_source_id => NULL
830 , p_source_text => NULL
831 , p_action_information_category => 'APAC ABSENCES'
832 , p_action_information1 => NULL
833 , p_action_information2 => csr_rec.element_reporting_name
834 , p_action_information4 => l_start_date
835 , p_action_information5 => l_end_date
836 , p_action_information6 => fnd_number.number_to_canonical(csr_rec.absence_duration) -- Bug 3604103
837 , p_action_information7 => NULL
838 );
839
840 END LOOP;
841
842 hr_utility.set_location('End of archive Leaves Taken',4);
843
844 EXCEPTION
845 WHEN OTHERS THEN
846 hr_utility.set_location('Error raised in archiving Accruals and Leave Taken ',5);
847 RAISE;
848
849 END archive_accrual_details;
850
851
852
853
854 --------------------------------------------------------------------+
855 -- Procedure to call the internal procedures to actually
856 -- the archive the data. The procedure called are -
857 -- archive_accrual_details
858 -- archive_employee_details
859 -- pay_apac_payslip_archive.archive_user_elements
860 -- archive_stat_balances
861 -- archive_stat_elements
862 -- pay_apac_payslip_archive.archive_user_balances
863 --------------------------------------------------------------------+
864
865 PROCEDURE archive_code (p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
866 p_effective_date IN DATE) IS
867
868
869 -- Cursor to select all the locked prepayment and payrolls by the archive
870 -- assignment action. The records are ordered descending as we only need
871 -- latest payroll run in the prepayment.
872
873 --
874 -- Bug 3580568
875 -- Changed cursor to csr_get_payslip_aa
876 --
877 cursor csr_get_payslip_aa(p_master_aa_id number)
878 is
879 SELECT paa_arch_chd.assignment_action_id chld_arc_assignment_action_id
880 ,paa_arch_chd.payroll_action_id arch_payroll_action_id
881 ,paa_pre.assignment_action_id pre_assignment_action_id
882 ,paa_run.assignment_action_id run_assignment_action_id
883 ,paa_run.payroll_action_id run_payroll_action_id
884 ,ppa_pre.effective_date pre_effective_date
885 ,paa_arch_chd.assignment_id
886 ,ppa_run.effective_date run_effective_date
887 ,ppa_run.date_earned run_date_earned
888 ,ptp.end_date period_end_date
889 ,ptp.time_period_id
890 ,ptp.start_date
891 ,ptp.regular_payment_date
892 FROM pay_assignment_actions paa_arch_chd
893 ,pay_assignment_actions paa_arch_mst
894 ,pay_assignment_actions paa_pre
895 ,pay_action_interlocks pai_pre
896 ,pay_assignment_actions paa_run
897 ,pay_action_interlocks pai_run
898 ,pay_payroll_actions ppa_pre
899 ,pay_payroll_actions ppa_run
900 ,per_time_periods ptp
901 ,per_business_groups pbg
902 WHERE paa_arch_mst.assignment_action_id = p_master_aa_id
903 AND paa_arch_chd.source_action_id = paa_arch_mst.assignment_action_id
904 AND paa_arch_chd.payroll_action_id = paa_arch_mst.payroll_action_id
905 AND ppa_pre.business_group_id = pbg.business_group_id
906 AND pbg.business_group_id = ppa_run.business_group_id
907 AND ppa_pre.payroll_id = ppa_run.payroll_id
908 AND paa_arch_chd.assignment_id = paa_arch_mst.assignment_id
909 AND pai_pre.locking_action_id = paa_arch_mst.assignment_action_id
910 AND pai_pre.locked_action_id = paa_pre.assignment_action_id
911 AND pai_run.locking_action_id = paa_arch_chd.assignment_action_id
912 AND pai_run.locked_action_id = paa_run.assignment_action_id
913 AND ppa_pre.payroll_action_id = paa_pre.payroll_action_id
914 AND ppa_pre.action_type IN ('P','U')
915 AND ppa_run.payroll_action_id = paa_run.payroll_action_id
916 AND ppa_run.action_type IN ('R','Q')
917 AND ptp.payroll_id = ppa_run.payroll_id
918 AND ppa_run.date_earned BETWEEN ptp.start_date
919 AND ptp.end_date
920 -- Get the highest in sequence for this payslip
921 AND paa_run.action_sequence =
922 (
923 SELECT MAX(paa_run2.action_sequence)
924 FROM pay_assignment_actions paa_run2
925 ,pay_action_interlocks pai_run2
926 WHERE pai_run2.locking_action_id = paa_arch_chd.assignment_action_id
927 AND pai_run2.locked_action_id = paa_run2.assignment_action_id
928 );
929
930 /* Bug No:5634580
931 This cursor returns actual termination date if it falls in the pay period */
932
933 CURSOR csr_payment_date(p_assignment_action_id NUMBER)
934 IS
935 SELECT pps.actual_termination_date
936 FROM pay_payroll_actions ppa,
937 pay_assignment_actions paa,
938 per_time_periods ptp,
939 per_all_assignments_f paf,
940 per_periods_of_service pps
941 WHERE paa.assignment_action_id = p_assignment_action_id
942 AND ppa.payroll_action_id = paa.payroll_action_id
943 AND ptp.payroll_id = ppa.payroll_id
944 AND paf.assignment_id = paa.assignment_id
945 AND pps.period_of_service_id = paf.period_of_service_id
946 AND ppa.date_earned between ptp.start_date AND ptp.end_date
947 AND pps.actual_termination_date between ptp.start_date AND ptp.end_date;
948
949
950 l_pre_pay_assact_id NUMBER;
951 l_period_end_date DATE;
952 l_time_period_id per_time_periods.time_period_id%type; /* Bug No : 2491444 */
953 l_payment_date DATE :=NULL;
954
955 BEGIN
956
957
958 hr_utility.set_location('Start of archive code',20);
959
960 --
961 -- Bug 3580568
962 --
963 pay_core_payslip_utils.generate_child_actions(p_assignment_action_id,
964 p_effective_date);
965
966 --
967 -- Bug 3580568
968 -- Changed cursor to csr_get_payslip_aa
969 --
970 FOR csr_rec IN csr_get_payslip_aa(p_assignment_action_id)
971
972 LOOP
973
974 hr_utility.set_location('csr_rec.master_assignment_action_id = ' || csr_rec.run_assignment_action_id,20);
975 hr_utility.set_location('csr_rec.pre_assignment_action_id = ' || csr_rec.pre_assignment_action_id,30);
976
977 /*Bug No:5634580
978 This cursor returns actual termination date if it falls in the pay period */
979
980 open csr_payment_date(csr_rec.run_assignment_action_id);
981 fetch csr_payment_date into l_payment_date;
982 if csr_payment_date%NOTFOUND then
983 l_payment_date := csr_rec.period_end_date;
984 end if;
985 close csr_payment_date;
986
987
988
989 -- Loop to be executed only once for a prepayment with latest payroll run details
990 -- in the prepayment
991
992 -- Call to procedure to archive User Configurable Balnaces
993 pay_apac_payslip_archive.archive_user_balances
994 ( p_arch_assignment_action_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
995 , p_run_assignment_action_id => csr_rec.run_assignment_action_id -- payroll assignment action id
996 , p_pre_effective_date => csr_rec.pre_effective_date -- prepayment effecive date
997 );
998
999
1000 -- Call to procedure to archive Statutory Elements
1001
1002 archive_stat_elements
1003 ( p_assignment_action_id => csr_rec.pre_assignment_action_id -- prepayment assignment action id
1004 , p_assignment_id => csr_rec.assignment_id -- assignment id
1005 , p_effective_date => csr_rec.pre_effective_date -- prepayment effective date
1006 , p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
1007 );
1008
1009 -- Call to procedure to archive Statutory balances
1010
1011 archive_stat_balances
1012 ( p_assignment_action_id => csr_rec.run_assignment_action_id -- payroll assignment action id
1013 , p_assignment_id => csr_rec.assignment_id -- assignment id
1014 , p_date_earned => csr_rec.run_date_earned -- payroll date earned
1015 , p_effective_date => csr_rec.pre_effective_date -- prepayment effective date
1016 , p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
1017 );
1018
1019 -- Call to procedure to archive User Configurable Elements
1020
1021 pay_apac_payslip_archive.archive_user_elements
1022 ( p_arch_assignment_action_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action
1023 , p_pre_assignment_action_id => csr_rec.pre_assignment_action_id -- prepayment assignment action id
1024 , p_latest_run_assact_id => csr_rec.run_assignment_action_id -- payroll assignment action id
1025 , p_pre_effective_date => csr_rec.pre_effective_date -- prepayment effective date
1026 );
1027
1028
1029
1030 /* Bug No : 2491444 -- Changed the value passed for time_period_id for all the procedures below.*/
1031
1032 -- Call to procedure to archive Employee Details
1033
1034 archive_employee_details
1035 ( p_payroll_action_id => csr_rec.arch_payroll_action_id -- archive payroll action id
1036 , p_assactid => csr_rec.chld_arc_assignment_action_id -- archive action id
1037 , p_pay_assignment_action_id => csr_rec.run_assignment_action_id -- payroll run action id
1038 , p_assignment_id => csr_rec.assignment_id -- assignment_id
1039 , p_curr_pymt_ass_act_id => csr_rec.pre_assignment_action_id -- prepayment assignment_action_id
1040 , p_date_earned => csr_rec.run_date_earned -- payroll date_earned
1041 , p_curr_pymt_eff_date => l_payment_date -- latest payment period end date
1042 , p_run_effective_date => csr_rec.run_effective_date -- run effective Date
1043 , p_time_period_id => csr_rec.time_period_id -- time_period_id of per_time_periods
1044 );
1045
1046
1047 -- Call to procedure to archive accrual and absennce details
1048
1049 archive_accrual_details
1050 ( p_payroll_action_id => csr_rec.run_payroll_action_id -- latest payroll action id
1051 , p_time_period_id => csr_rec.time_period_id -- latest period time period id
1052 , p_assignment_id => csr_rec.assignment_id -- assignment id
1053 , p_date_earned => csr_rec.run_date_earned -- latest payroll date earned
1054 , p_effective_date => csr_rec.pre_effective_date -- prepayment effective date
1055 , p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
1056 , p_assignment_action_id => csr_rec.run_assignment_action_id -- payroll run action id
1057 , p_period_end_date => csr_rec.period_end_date -- latest period end date
1058 );
1059
1060
1061 l_pre_pay_assact_id := csr_rec.pre_assignment_action_id;
1062
1063
1064 END LOOP;
1065
1066 hr_utility.set_location('End of archive code',37);
1067
1068 EXCEPTION
1069 WHEN OTHERS THEN
1070 hr_utility.set_location('Error in archive code :',11);
1071 RAISE;
1072
1073 END archive_code;
1074
1075 END pay_nz_payslip_archive;