[Home] [Help]
PACKAGE BODY: APPS.PAY_SG_PAYSLIP_ARCHIVE
Source
1 PACKAGE BODY pay_sg_payslip_archive AS
2 /* $Header: pysgparc.pkb 120.5 2011/02/01 12:23:09 dduvvuri ship $ */
3
4 ---------------------------------------------------------------------+
5 -- This is a global variable used to store Archive assignment action id
6 --------------------------------------------------------------------+
7
8 g_archive_pact NUMBER;
9
10 --------------------------------------------------------------------+
11 -- This procedure returns a sql string to SELECT a range
12 -- of assignments eligible for archival.
13 -- It calls pay_apac_payslip_archive.range_code that archives the EIT
14 -- definition and payroll level data (Messages, employer address details etc)
15 -- Major changes were made to the procedure as part of
16 -- fix for bug 3580587
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 -- Call to range_code from common apac package 'pay_apac_payslip_archive'
28 -- to archive the payroll action level data and EIT defintions.
29 --------------------------------------------------------------------------------+
30 pay_apac_payslip_archive.range_code( p_payroll_action_id => p_payroll_action_id );
31 --
32 pay_core_payslip_utils.range_cursor( p_payroll_action_id,
33 p_sql
34 );
35 --
36 hr_utility.set_location('End of range_code',2);
37 EXCEPTION
38 WHEN OTHERS THEN
39 hr_utility.set_location('Error in range code',2);
40 RAISE;
41
42 END range_code;
43
44
45 --------------------------------------------------------------------+
46 -- This procedure is used to set global contexts .
47 -- The globals used are PL/SQL tables i.e.(g_user_balance_table and g_element_table)
48 -- It calls the procedure pay_apac_archive.initialization_code that
49 -- actually sets the global variables and populates the global tables.
50 --------------------------------------------------------------------+
51
52 PROCEDURE initialization_code (p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE) IS
53
54
55 BEGIN
56 hr_utility.set_location('Start of initialization_code',1);
57
58 g_archive_pact := p_payroll_action_id;
59
60 ------------------------------------------------------------------+
61 -- Call to common package procedure pay_apac_payslip_archive.
62 -- initialization_code to to set the global tables for EIT
63 -- that will be used by each thread in multi-threading.
64 ------------------------------------------------------------------+
65
66 pay_apac_payslip_archive.initialization_code(p_payroll_action_id => p_payroll_action_id);
67
68 hr_utility.set_location('End of initialization_code',2);
69
70 EXCEPTION
71 WHEN OTHERS THEN
72 hr_utility.set_location('Error in initialization_code',2);
73 RAISE;
74
75 END initialization_code;
76
77
78 --------------------------------------------------------------------+
79 -- This procedure further restricts the assignment_id's
80 -- returned by range_code
81 -- It filters the assignments selected by range_code procedure
82
83 -- Since the Payslip is given for each prepayment, the data should
84 -- be archived for each prepayment.
85 -- So, the successfully completed prepayments are selected and locked
86 -- by the archival action.
87 -- All the successfully completed runs under the prepayments are also
88 -- selected and locked by archival to make the core 'Choose Payslip'
89 -- work for SG.
90 -- The archive will not pickup already archived prepayments.
91 -- Major changes were made to the procedure as part of
92 -- fix for bug 3580587
93 --------------------------------------------------------------------+
94
95 PROCEDURE assignment_action_code (p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE,
96 p_start_person IN per_all_people_f.person_id%TYPE,
97 p_end_person IN per_all_people_f.person_id%TYPE,
98 p_chunk IN NUMBER) IS
99 BEGIN
100 hr_utility.set_location('Start of Assignment_Action_Code',1);
101 pay_core_payslip_utils.action_creation ( p_payroll_action_id,
102 p_start_person,
103 p_end_person,
104 p_chunk,
105 'SG_PAYSLIP_ARCHIVE',
106 'SG');
107 hr_utility.set_location('End of Assignment_Action_Code',2);
108 EXCEPTION
109 WHEN OTHERS THEN
110 hr_utility.set_location('Error in Assignment_Action_Code',2);
111 RAISE;
112 END assignment_action_code;
113
114
115 --------------------------------------------------------------------+
116 -- This procedure archives the elements and run result values.
117 -- It uses SG Pay Advice view 'pay_sg_asg_elements_v'
118 -- to get the elements and corresponding payments.
119 --------------------------------------------------------------------+
120
121 PROCEDURE archive_stat_elements(p_assignment_action_id IN NUMBER,
122 p_assignment_id IN NUMBER,
123 p_effective_date IN DATE,
124 p_assact_id IN NUMBER) IS
125
126 -- Cursor to get all the elements processed for the assignment in the
127 -- prepayment.
128
129 CURSOR csr_std_elements(p_assignment_action_id NUMBER,
130 p_assignment_id NUMBER)
131 IS
132 SELECT element_reporting_name
133 , classification_group
134 , amount
135 , foreign_currency_code
136 , hours
137 , exchange_rate
138 FROM pay_sg_asg_elements_v
139 WHERE run_assignment_action_id = p_assignment_action_id
140 AND classification_group IS NOT NULL;
141
142
143 l_action_info_id NUMBER;
144 l_ovn NUMBER;
145 l_foreign_currency_amount NUMBER;
146 l_rate NUMBER;
147 l_procedure_name VARCHAR2(80) ;
148
149 BEGIN
150 l_procedure_name := 'archive_stat_elements';
151 hr_utility.set_location('Entering Procedure pay_sg_payslip_archive.' || l_procedure_name,10);
152
153 FOR csr_rec IN csr_std_elements(p_assignment_action_id,p_assignment_id)
154
155 LOOP
156
157 hr_utility.set_location('Archiving Standard Element Details',20);
158
159 /* Start of Bug No : 2643038 */
160 IF nvl(csr_rec.exchange_rate,0) <> 0 THEN
161 l_foreign_currency_amount := csr_rec.amount / csr_rec.exchange_rate;
162 ELSE
163 l_foreign_currency_amount := NULL; /* Bug No : 2648763 */
164 END IF;
165 /* End of Bug No : 2643038*/
166
167 /* Start of bug fix 11692029 */
168 IF nvl(csr_rec.hours,0) <> 0 THEN
169 l_rate := csr_rec.amount / csr_rec.hours;
170 ELSE
171 l_rate := 0;
172 END IF;
173 /* End of bug fix 11692029 */
174
175 pay_action_information_api.create_action_information
176 ( p_action_information_id => l_action_info_id
177 , p_action_context_id => p_assact_id
178 , p_action_context_type => 'AAP'
179 , p_object_version_number => l_ovn
180 , p_effective_date => p_effective_date
181 , p_source_id => NULL
182 , p_source_text => NULL
183 , p_action_information_category => 'APAC ELEMENTS'
184 , p_action_information1 => csr_rec.element_reporting_name
185 , p_action_information2 => NULL
186 , p_action_information3 => NULL
187 , p_action_information4 => csr_rec.classification_group
188 , p_action_information5 => fnd_number.number_to_canonical(csr_rec.amount) -- Bug 3604110
189 , p_action_information7 => csr_rec.hours
190 , p_action_information9 => fnd_number.number_to_canonical(l_rate) -- Bug 3604110
191 , p_action_information10 => fnd_number.number_to_canonical(csr_rec.exchange_rate) -- Bug 3604110
192 , p_action_information11 => fnd_number.number_to_canonical(l_foreign_currency_amount) -- Bug 3604110
193 , p_action_information12 => csr_rec.foreign_currency_code);
194
195
196 END LOOP;
197 hr_utility.trace('Closing Cursor csr_std_elements');
198 hr_utility.set_location('End of archive Standard Element',4);
199 hr_utility.set_location('Leaving Procedure pay_sg_payslip_archive.' || l_procedure_name,10);
200
201 EXCEPTION
202 WHEN OTHERS THEN
203 hr_utility.set_location('Error in archiving Standard Elements ',5);
204 RAISE;
205
206 END archive_stat_elements;
207
208
209
210
211 --------------------------------------------------------------------+
212 -- Procedure to archive the Statutory balances
213 --------------------------------------------------------------------+
214
215 PROCEDURE archive_balances( p_effective_date IN DATE
216 ,p_assact_id IN NUMBER
217 ,p_narrative IN VARCHAR2
218 ,p_ytd IN NUMBER) IS
219
220 l_action_info_id NUMBER;
221 l_ovn NUMBER;
222 l_procedure_name VARCHAR2(80);
223
224 BEGIN
225 l_procedure_name := 'archive_balances';
226 hr_utility.set_location('Entering Procedure pay_sg_payslip_archive.' || l_procedure_name,10);
227 hr_utility.set_location('archiving balances :',10);
228
229 -- Archive Statutory balances
230
231 pay_action_information_api.create_action_information
232 ( p_action_information_id => l_action_info_id
233 , p_action_context_id => p_assact_id
234 , p_action_context_type => 'AAP'
235 , p_object_version_number => l_ovn
236 , p_effective_date => p_effective_date
237 , p_source_id => NULL
238 , p_source_text => NULL
239 , p_action_information_category => 'APAC BALANCES'
240 , p_action_information1 => p_narrative
241 , p_action_information2 => NULL
242 , p_action_information3 => NULL
243 , p_action_information4 => fnd_number.number_to_canonical(p_ytd) -- Bug 3604110
244 );
245
246 hr_utility.set_location('Leaving Procedure pay_sg_payslip_archive.' || l_procedure_name,10);
247
248
249 EXCEPTION
250 WHEN OTHERS THEN
251 hr_utility.set_location('Error in archiving balance :',11);
252 RAISE;
253
254 END archive_balances;
255
256
257 --------------------------------------------------------------------+
258 -- Procedure to archive the CPF balances
259 --------------------------------------------------------------------+
260
261 PROCEDURE archive_cpf_balances( p_effective_date IN DATE
262 , p_assact_id IN NUMBER
263 , p_narrative IN VARCHAR2
264 , p_curr IN NUMBER
265 , p_ytd IN NUMBER) IS
266
267 l_action_info_id NUMBER;
268 l_ovn NUMBER;
269 l_procedure_name VARCHAR2(80);
270
271 BEGIN
272 l_procedure_name := 'archive_cpf_balances';
273 hr_utility.set_location('Entering Procedure pay_sg_payslip_archive.' || l_procedure_name,10);
274 hr_utility.set_location('archiving cpf balances :',10);
275
276 -- Archive CPF balances
277
278 pay_action_information_api.create_action_information
279 ( p_action_information_id => l_action_info_id
280 , p_action_context_id => p_assact_id
281 , p_action_context_type => 'AAP'
282 , p_object_version_number => l_ovn
283 , p_effective_date => p_effective_date
284 , p_source_id => NULL
285 , p_source_text => NULL
286 , p_action_information_category => 'APAC BALANCES 2'
287 , p_action_information1 => p_narrative
288 , p_action_information2 => NULL
289 , p_action_information3 => NULL
290 , p_action_information4 => fnd_number.number_to_canonical(p_curr) -- Bug 3604110
291 , p_action_information5 => fnd_number.number_to_canonical(p_ytd) -- Bug 3604110
292 );
293
294 hr_utility.set_location('Leaving Procedure pay_sg_payslip_archive.' || l_procedure_name,10);
295
296
297 EXCEPTION
298 WHEN OTHERS THEN
299 hr_utility.set_location('Error in archiving CPF_balances :',11);
300 RAISE;
301
302 END archive_cpf_balances;
303
304 --------------------------------------------------------------------+
305 -- Procedure to calculate the balances values
306 -- Calls procedure archive_balances and archive_cpf_balances to
307 -- actually archives all the balance values
308 --------------------------------------------------------------------+
309
310 PROCEDURE archive_stat_balances(p_assignment_action_id IN NUMBER
311 ,p_assignment_id IN NUMBER
312 ,p_date_earned IN DATE
313 ,p_effective_date IN DATE
314 ,p_assact_id IN NUMBER) IS
315
316 l_gross_pay_current NUMBER;
317 l_statutory_deductions_current NUMBER;
318 l_other_deductions_current NUMBER;
319 l_net_pay_current NUMBER;
320 l_non_payroll_current NUMBER;
321 l_gross_pay_ytd NUMBER;
322 l_statutory_deductions_ytd NUMBER;
323 l_other_deductions_ytd NUMBER;
324 l_net_pay_ytd NUMBER;
325 l_non_payroll_ytd NUMBER;
326 l_employee_cpf_current NUMBER;
327 l_employer_cpf_current NUMBER;
328 l_cpf_total_current NUMBER;
329 l_employee_cpf_ytd NUMBER;
330 l_employer_cpf_ytd NUMBER;
331 l_cpf_total_ytd NUMBER;
332 l_person_id NUMBER;
333 l_narrative VARCHAR2(150);
334 l_procedure_name VARCHAR2(80);
335
336 /* Bug 2824397 */
337 /* Bug:2824397. Removed distinct and added rownum join */
338 cursor c_person_id(c_assignment_id per_all_assignments.assignment_id%type) is
339 select person_id
340 from per_assignments_f /* Bug# 2920732 */
341 where assignment_id = c_assignment_id
342 and rownum = 1;
343 /* Bug 2824397 */
344
345 BEGIN
346 l_procedure_name := 'archive_stat_balances';
347 hr_utility.set_location('Entering Procedure pay_sg_payslip_archive.' || l_procedure_name,10);
348 hr_utility.set_location('Calling balance_total from pay_sg_soe_pkg',20);
349
350 /* Bug 2824397 */
351 open c_person_id(p_assignment_id);
352 fetch c_person_id into l_person_id;
353 close c_person_id;
354
355 /* Bug 2824397 */
356 -- Get the totals of all the balances
357
358 pay_sg_soe.balance_totals( 'Y',
359 p_assignment_action_id,
360 l_person_id,
361 l_gross_pay_current,
362 l_statutory_deductions_current,
363 l_other_deductions_current,
364 l_net_pay_current,
365 l_non_payroll_current,
366 l_gross_pay_ytd,
367 l_statutory_deductions_ytd,
368 l_other_deductions_ytd,
369 l_net_pay_ytd,
370 l_non_payroll_ytd,
371 l_employee_cpf_current,
372 l_employer_cpf_current,
373 l_cpf_total_current,
374 l_employee_cpf_ytd,
375 l_employer_cpf_ytd,
376 l_cpf_total_ytd
377 );
378
379 l_narrative := 'Gross Income';
380
381 hr_utility.set_location('Archiving value for ' || l_narrative,30);
382
383 archive_balances(p_effective_date =>p_effective_date
384 ,p_assact_id =>p_assact_id
385 ,p_narrative =>l_narrative
386 ,p_ytd =>l_gross_pay_ytd);
387
388
389 l_narrative := 'Statutory Deductions';
390
391 hr_utility.set_location('Archiving value for ' || l_narrative,40);
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_statutory_deductions_ytd);
397
398
399 l_narrative := 'Other Deductions';
400
401 hr_utility.set_location('Archiving value for ' || l_narrative,50);
402
403 archive_balances(p_effective_date => p_effective_date
404 ,p_assact_id => p_assact_id
405 ,p_narrative => l_narrative
406 ,p_ytd => l_other_deductions_ytd);
407
408
409 l_narrative := 'Net Payment';
410
411 hr_utility.set_location('Archiving value for ' || l_narrative,60);
412
413 archive_balances(p_effective_date => p_effective_date
414 ,p_assact_id => p_assact_id
415 ,p_narrative => l_narrative
416 ,p_ytd => l_net_pay_ytd);
417
418
419 l_narrative := 'Non Payroll Payments';
420
421 hr_utility.set_location('Archiving value for ' || l_narrative,70);
422
423 archive_balances(p_effective_date => p_effective_date
424 ,p_assact_id => p_assact_id
425 ,p_narrative => l_narrative
426 ,p_ytd => l_non_payroll_ytd);
427
428
429 l_narrative := 'Employee';
430
431 hr_utility.set_location('Archiving value for ' || l_narrative,70);
432
433 archive_cpf_balances(p_effective_date => p_effective_date
434 ,p_assact_id => p_assact_id
435 ,p_narrative => l_narrative
436 ,p_curr => l_employee_cpf_current
437 ,p_ytd => l_employee_cpf_ytd);
438
439
440
441 l_narrative := 'Employer';
442
443 hr_utility.set_location('Archiving value for ' || l_narrative,70);
444
445 archive_cpf_balances(p_effective_date => p_effective_date
446 ,p_assact_id => p_assact_id
447 ,p_narrative => l_narrative
448 ,p_curr => l_employer_cpf_current
449 ,p_ytd => l_employer_cpf_ytd);
450
451
452 hr_utility.set_location('End of Archiving Stat Balances ',100);
453
454 hr_utility.set_location('Leaving Procedure pay_sg_payslip_archive.' || l_procedure_name,110);
455
456 EXCEPTION
457 WHEN OTHERS THEN
458 hr_utility.set_location('error in calling archive balance code :',11);
459 RAISE;
460
461 END archive_stat_balances;
462
463
464
465
466 --------------------------------------------------------------------------------------+
467 -- This procedure calls 'pay_emp_action_arch.get_personal_information' that actually
468 -- archives the employee details,employee address details, Employer Address Details
469 -- and Net Pay Distribution information. Procedure 'get_personal_information' is
470 -- is passed tax_unit_id to make core provided 'Choose Payslip' work for us.
471 -- The action DF structures used are -
472 -- ADDRESS DETAILS
473 -- EMPLOYEE DETAILS
474 -- EMPLOYEE NET PAY DISTRIBUTION
475 -- EMPLOYEE OTHER INFORMATION
476 -- After core procedure completes the archival, the information stored for category
477 -- EMPLOYEE_NET_PAY_DISTRIBUTION is updated with bank name,percentage,currency code
478 -- specific to Singapore using action_information9,action_information12 and
479 -- action_information13 respectively.
480 ---------------------------------------------------------------------------------------+
481
482 PROCEDURE archive_employee_details (p_payroll_action_id IN NUMBER
483 , p_pay_assignment_action_id IN NUMBER
484 , p_assactid IN NUMBER
485 , p_assignment_id IN NUMBER
486 , p_curr_pymt_ass_act_id IN NUMBER
487 , p_date_earned IN DATE
488 , p_latest_period_payment_date IN DATE /* Bug No : 2470554 */
489 , p_run_effective_date IN DATE
490 , p_time_period_id IN NUMBER
491 , p_pre_effective_date IN DATE /* Bug 5730336 */ ) IS
492
493 -- Cursor to select the archived information for category 'EMPLOYEE NET PAY DISTRIBUTION'
494 -- by core package.
495
496 CURSOR csr_action_information_id(p_assact_id NUMBER)
497 IS
498 SELECT action_information_id
499 , action_information1 /* Bug No : 2672510 */
500 , action_information2 /* Bug No : 2538781 */
501 FROM pay_action_information
502 WHERE action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
503 AND action_context_id = p_assact_id
504 AND action_context_type = 'AAP';
505
506 -- Cursor to select the tax_unit_id of the prepayment needed for archival
507
508 CURSOR csr_tax_unit_id(p_assignment_action_id NUMBER)
509 IS
510 SELECT tax_unit_id
511 FROM pay_assignment_actions
512 WHERE assignment_action_id = p_assignment_action_id;
513
514
515 -- Cursor to get the bank name,percentage and currency code using the view
516 -- pay_sg_asg_net_payments_v
517
518
519 /* Start of Bug No : 2538781 */
520
521 CURSOR csr_bank_details(p_curr_pymt_ass_act_id NUMBER, l_personal_payment_method_id NUMBER, l_org_payment_method_id NUMBER) /* Bug No : 2672510 */
522 IS
523 SELECT hl.meaning account_type /* Bug 5435029 */
524 , pea.segment5 bank_name
525 , ppm.percentage
526 , pop.currency_code
527 FROM pay_external_accounts pea
528 , pay_personal_payment_methods_f ppm
529 , pay_org_payment_methods_f pop
530 , pay_pre_payments ppp
531 , hr_lookups hl
532 WHERE ppm.personal_payment_method_id(+) = ppp.personal_payment_method_id
533 AND pop.org_payment_method_id = ppp.org_payment_method_id
534 AND pea.segment3 = hl.lookup_code (+)
535 AND hl.lookup_type(+) = 'SG_ACCOUNT_TYPE'
536 AND pea.external_account_id(+) = ppm.external_account_id
537 AND ppp.assignment_action_id = p_curr_pymt_ass_act_id
538 AND ((ppp.personal_payment_method_id = l_personal_payment_method_id) or
539 (ppp.org_payment_method_id = l_org_payment_method_id and ppp.personal_payment_method_id is null)) /* Bug No : 2672510 */
540 AND p_pre_effective_date BETWEEN pop.effective_start_date
541 AND pop.effective_end_date
542 AND p_pre_effective_date BETWEEN nvl(ppm.effective_start_date, p_pre_effective_date)
543 AND nvl(ppm.effective_end_date, p_pre_effective_date); /* Bug 5730336 */
544
545 /* End of Bug No : 2538781 */
546
547 l_action_info_id NUMBER;
548 l_ovn NUMBER;
549 l_tax_code VARCHAR2(5);
550 l_tax_unit_id NUMBER;
551 l_procedure_name VARCHAR2(80);
552 l_account_type VARCHAR2(100);
553 l_bank_name VARCHAR2(100);
554 l_percentage NUMBER;
555 l_currency_code VARCHAR2(15);
556
557 BEGIN
558 l_procedure_name := 'archive_employee_details';
559 hr_utility.set_location('Entering Procedure pay_sg_payslip_archive.'|| l_procedure_name,10);
560
561
562 -- call generic procedure to retrieve and archive all data for
563 -- EMPLOYEE DETAILS, ADDRESS DETAILS and EMPLOYEE NET PAY DISTRIBUTION
564
565 hr_utility.trace('Opening Cursor csr_tax_unit_id');
566 OPEN csr_tax_unit_id(p_curr_pymt_ass_act_id);
567 FETCH csr_tax_unit_id INTO l_tax_unit_id;
568 CLOSE csr_tax_unit_id;
569 hr_utility.trace('Closing Cursor csr_tax_unit_id');
570 hr_utility.set_location('Calling pay_emp_action_arch.get_personal_information ',20);
571
572 pay_emp_action_arch.get_personal_information
573 (p_payroll_action_id => p_payroll_action_id -- archive payroll_action_id
574 , p_assactid => p_assactid -- archive assignment_action_id
575 , p_assignment_id => p_assignment_id -- current assignment_id
576 , p_curr_pymt_ass_act_id => p_curr_pymt_ass_act_id -- prepayment assignment_action_id
577 , p_curr_eff_date => p_run_effective_date -- run effective_date
578 , p_date_earned => p_date_earned -- payroll date_earned
579 , p_curr_pymt_eff_date => p_latest_period_payment_date -- latest payment date /* Bug No : 2470554 */
580 , p_tax_unit_id => l_tax_unit_id -- tax_unit_id needed for Choose Payslip region.
581 , p_time_period_id => p_time_period_id -- time_period_id from per_time_periods /* Bug No:2496783 */
582 , p_ppp_source_action_id => NULL
583 , p_run_action_id => p_pay_assignment_action_id
584 );
585
586 hr_utility.set_location('Returned from pay_emp_action_arch.csr_personal_information ',30);
587
588 hr_utility.set_location('Calling update Net Pay Distribution',80);
589
590 hr_utility.trace('Opening Cursor csr_action_information_id');
591
592 FOR net_pay_rec in csr_action_information_id(p_assactid)
593
594 LOOP
595
596 hr_utility.trace('Opening Cursor csr_bank_details');
597 OPEN csr_bank_details(p_curr_pymt_ass_act_id , net_pay_rec.action_information2, net_pay_rec.action_information1); /* Bug No : 2538781,2672510 */
598 FETCH csr_bank_details INTO l_account_type /* Bug 5435029 */
599 , l_bank_name
600 , l_percentage
601 , l_currency_code;
602 CLOSE csr_bank_details;
603 hr_utility.trace('Closing Cursor csr_bank_details');
604
605 l_ovn := 1;
606
607 pay_action_information_api.update_action_information
608 ( p_action_information_id => net_pay_rec.action_information_id
609 , p_object_version_number => l_ovn
610 , p_action_information6 => l_account_type /* Bug 5435029 */
611 , p_action_information9 => l_bank_name
612 , p_action_information12 => l_percentage
613 , p_action_information13 => l_currency_code
614 );
615
616 END LOOP;
617
618 hr_utility.trace('Closing Cursor csr_action_information_id');
619
620 hr_utility.set_location('Leaving Procedure pay_sg_payslip_archive.' || l_procedure_name,10);
621
622 EXCEPTION
623 WHEN OTHERS THEN
624 hr_utility.set_location('Error in archiving Employee details ',5);
625 RAISE;
626
627 END archive_employee_details;
628
629
630 --------------------------------------------------------------------+
631 -- Procedure to archive Accrual and Absence Details.
632 --------------------------------------------------------------------+
633
634 PROCEDURE archive_accrual_details ( p_payroll_action_id IN NUMBER
635 , p_time_period_id IN NUMBER
636 , p_assignment_id IN NUMBER
637 , p_date_earned IN DATE
638 , p_effective_date IN DATE
639 , p_assact_id IN NUMBER
640 , p_assignment_action_id IN NUMBER
641 , p_period_end_date IN DATE
642 , p_period_start_date IN DATE ) IS
643
644
645 -- Cursor to get the Leave Balance Details .
646
647 CURSOR csr_leave_balance( p_assignment_action_id NUMBER
648 , p_assignment_id NUMBER)
649 IS
650 SELECT pap.accrual_plan_name
651 , hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
652 , pap.accrual_units_of_measure
653 , ppa.payroll_id
654 , pap.business_group_id
655 , pap.accrual_plan_id
656 FROM pay_accrual_plans pap,
657 pay_element_types_f pet,
658 pay_element_links_f pel,
659 pay_element_entries_f pee,
660 pay_assignment_actions paa,
661 pay_payroll_actions ppa
662 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
663 AND pel.element_type_id = pet.element_type_id
664 AND pee.element_link_id = pel.element_link_id
665 AND paa.assignment_id = pee.assignment_id
666 AND ppa.payroll_action_id = paa.payroll_action_id
667 AND pap.accrual_category = 'SGAL'
668 AND ppa.action_type IN('R','Q')
669 AND ppa.action_status = 'C'
670 AND ppa.date_earned BETWEEN pet.effective_start_date
671 AND pet.effective_end_date
672 AND ppa.date_earned BETWEEN pel.effective_start_date
673 AND pel.effective_end_date
674 AND ppa.date_earned BETWEEN pee.effective_start_date
675 AND pee.effective_end_date
676 AND paa.assignment_id = p_assignment_id
677 AND paa.assignment_action_id = p_assignment_action_id;
678
679
680 l_action_info_id NUMBER;
681 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%type;
682 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%type;
683 l_accrual_category pay_accrual_plans.accrual_category%type;
684 l_accrual_uom pay_accrual_plans.accrual_units_of_measure%type;
685 l_payroll_id pay_all_payrolls_f.payroll_id%type;
686 l_procedure_name VARCHAR2(80);
687 l_business_group_id NUMBER;
688 l_effective_date DATE;
689 l_annual_leave_balance NUMBER;
690 l_ovn NUMBER;
691 l_leave_taken NUMBER;
692
693 BEGIN
694 l_procedure_name := 'archive_employee_details';
695 hr_utility.set_location('Start of accrual archival code',1);
696
697 hr_utility.set_location('Entering Procedure pay_sg_payslip_archive.' || l_procedure_name,10);
698
699 OPEN csr_leave_balance(p_assignment_action_id,p_assignment_id);
700 FETCH csr_leave_balance INTO
701 l_accrual_plan_name,
702 l_accrual_category,
703 l_accrual_uom,
704 l_payroll_id,
705 l_business_group_id,
706 l_accrual_plan_id;
707
708 CLOSE csr_leave_balance;
709
710 -- Call to get annual leave balance
711
712 hr_utility.set_location('Archiving Annual leave Balance information',2);
713
714 l_annual_leave_balance := pay_sg_soe.net_accrual( p_assignment_id
715 , l_accrual_plan_id
716 , l_payroll_id
717 , l_business_group_id
718 , p_date_earned); /* Bug No : 2538781 */
719
720
721 IF l_annual_leave_balance IS NULL THEN
722 l_annual_leave_balance := 0;
723 END IF;
724
725 hr_utility.set_location('Archiving Leave Taken information',2);
726
727 l_leave_taken := per_accrual_calc_functions.get_absence
728 (p_assignment_id,
729 l_accrual_plan_id,
730 p_period_end_date,
731 p_period_start_date);
732
733
734 IF l_leave_taken IS NULL THEN
735 l_leave_taken := 0;
736 END IF;
737
738 IF l_accrual_plan_name IS NOT NULL THEN
739
740 pay_action_information_api.create_action_information
741 ( p_action_information_id => l_action_info_id
742 , p_action_context_id => p_assact_id
743 , p_action_context_type => 'AAP'
744 , p_object_version_number => l_ovn
745 , p_effective_date => p_effective_date
746 , p_source_id => NULL
747 , p_source_text => NULL
748 , p_action_information_category => 'APAC ABSENCES'
749 , p_action_information1 => l_accrual_plan_name
750 , p_action_information2 => l_accrual_category
751 , p_action_information6 => fnd_number.number_to_canonical(l_leave_taken) -- Bug 3604110
752 , p_action_information7 => l_accrual_uom
753 , p_action_information8 => fnd_number.number_to_canonical(l_annual_leave_balance) -- Bug 3604110
754 );
755
756 END IF;
757
758 hr_utility.set_location('Leaving Procedure pay_sg_payslip_archive.' || l_procedure_name,10);
759
760
761 EXCEPTION
762 WHEN OTHERS THEN
763 hr_utility.set_location('Error raised in archiving Accruals and Leave Taken ',5);
764 RAISE;
765
766 END archive_accrual_details;
767
768
769
770
771 --------------------------------------------------------------------+
772 -- Procedure to call the internal procedures to actually
773 -- the archive the data. The procedure called are -
774 -- archive_accrual_details
775 -- archive_employee_details
776 -- pay_apac_payslip_archive.archive_user_elements
777 -- archive_stat_balances
778 -- archive_cpf_balances
779 -- archive_stat_elements
780 -- pay_apac_payslip_archive.archive_user_balances
781 -- Major changes were made to the procedure as part of
782 -- fix for bug 3580587
783 --------------------------------------------------------------------+
784
785 PROCEDURE archive_code (p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
786 p_effective_date IN DATE) IS
787
788
789 -- Cursor to select all the locked prepayment and payrolls by the archive
790 -- assignment action. The records are ordered descending as we only need
791 -- latest payroll run in the prepayment.
792 cursor get_payslip_aa(p_master_aa_id number)
793 is
794 select paa_arch_chd.assignment_action_id chld_arc_assignment_action_id,
795 paa_pre.assignment_action_id pre_assignment_action_id,
796 paa_run.assignment_action_id run_assignment_action_id,
797 ppa_pre.effective_date pre_effective_date,
798 paa_arch_chd.assignment_id,
799 ppa_run.payroll_action_id,
800 ppa_run.effective_date run_effective_date,
801 ppa_run.date_earned run_date_earned,
802 ptp.start_date period_start_date,
803 ptp.end_date period_end_date,
804 ptp.regular_payment_date,
805 ptp.time_period_id
806 from pay_assignment_actions paa_arch_chd,
807 pay_assignment_actions paa_arch_mst,
808 pay_assignment_actions paa_pre,
809 pay_action_interlocks pai_pre,
810 pay_assignment_actions paa_run,
811 pay_action_interlocks pai_run,
812 pay_payroll_actions ppa_pre,
813 pay_payroll_actions ppa_run,
814 per_time_periods ptp
815 where paa_arch_mst.assignment_action_id = p_master_aa_id
816 and paa_arch_chd.source_action_id = paa_arch_mst.assignment_action_id
817 and paa_arch_chd.payroll_action_id = paa_arch_mst.payroll_action_id
818 and paa_arch_chd.assignment_id = paa_arch_mst.assignment_id
819 and pai_pre.locking_action_id = paa_arch_mst.assignment_action_id
820 and pai_pre.locked_action_id = paa_pre.assignment_action_id
821 and pai_run.locking_action_id = paa_arch_chd.assignment_action_id
822 and pai_run.locked_action_id = paa_run.assignment_action_id
823 and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
824 and ppa_pre.action_type in ('P','U')
825 and ppa_run.payroll_action_id = paa_run.payroll_action_id
826 and ppa_run.action_type in ('R','Q')
827 and ptp.payroll_id = ppa_run.payroll_id
828 and ppa_run.date_earned between ptp.start_date
829 and ptp.end_date
830 -- Get the highest in sequence for this payslip
831 and paa_run.action_sequence = (select max(paa_run2.action_sequence)
832 from pay_assignment_actions paa_run2,
833 pay_action_interlocks pai_run2
834 where pai_run2.locking_action_id = paa_arch_chd.assignment_action_id
835 and pai_run2.locked_action_id = paa_run2.assignment_action_id
836 );
837
838 /* Added for the bug#5495382
839 This cursor returns actual termination date if it falls in the pay period */
840
841 CURSOR csr_payment_date(p_assignment_action_id NUMBER)
842 IS
843 SELECT pps.actual_termination_date
844 FROM pay_payroll_actions ppa,
845 pay_assignment_actions paa,
846 per_time_periods ptp,
847 per_all_assignments_f paf,
848 per_periods_of_service pps
849 WHERE paa.assignment_action_id = p_assignment_action_id
850 AND ppa.payroll_action_id = paa.payroll_action_id
851 AND ptp.payroll_id = ppa.payroll_id
852 AND paf.assignment_id = paa.assignment_id
853 AND pps.period_of_service_id = paf.period_of_service_id
854 AND ppa.date_earned between ptp.start_date AND ptp.end_date
855 AND pps.actual_termination_date between ptp.start_date AND ptp.end_date;
856
857 l_pre_pay_assact_id NUMBER :=0;
858 l_payment_date DATE :=NULL;
859
860 BEGIN
861 hr_utility.set_location('Start of archive code',20);
862 --
863 pay_core_payslip_utils.generate_child_actions( p_assignment_action_id,
864 p_effective_date
865 );
866 --
867 FOR csr_rec IN get_payslip_aa( p_assignment_action_id )
868 LOOP
869 /* Added for the bug#5495382 */
870 open csr_payment_date(csr_rec.run_assignment_action_id);
871 fetch csr_payment_date into l_payment_date;
872 if csr_payment_date%NOTFOUND then
873 l_payment_date := csr_rec.regular_payment_date;
874 end if;
875 close csr_payment_date;
876
877 -- Loop to be executed only once for a prepayment with latest payroll run details
878 -- in the prepayment
879 IF l_pre_pay_assact_id <> csr_rec.pre_assignment_action_id THEN
880 -- Call to procedure to archive User Configurable Balnaces
881 pay_apac_payslip_archive.archive_user_balances(
882 p_arch_assignment_action_id => csr_rec.chld_arc_assignment_action_id, -- archive assignment action id
883 p_run_assignment_action_id => csr_rec.run_assignment_action_id, -- payroll assignment action id
884 p_pre_effective_date => csr_rec.pre_effective_date -- prepayment effecive date
885 );
886 -- Call to procedure to archive Statutory Elements
887 archive_stat_elements (
888 p_assignment_action_id => csr_rec.pre_assignment_action_id, -- prepayment assignment action id
889 p_assignment_id => csr_rec.assignment_id, -- assignment id
890 p_effective_date => csr_rec.pre_effective_date, -- prepayment effective date
891 p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
892 );
893 -- Call to procedure to archive Statutory balances
894 archive_stat_balances(
895 p_assignment_action_id => csr_rec.run_assignment_action_id, -- payroll assignment action id
896 p_assignment_id => csr_rec.assignment_id, -- assignment id
897 p_date_earned => csr_rec.run_date_earned, -- payroll date earned
898 p_effective_date => csr_rec.pre_effective_date, -- prepayment effective date
899 p_assact_id => csr_rec.chld_arc_assignment_action_id -- archive assignment action id
900 );
901 -- Call to procedure to archive User Configurable Elements
902 pay_apac_payslip_archive.archive_user_elements (
903 p_arch_assignment_action_id => csr_rec.chld_arc_assignment_action_id, -- archive assignment action
904 p_pre_assignment_action_id => csr_rec.pre_assignment_action_id, -- prepayment assignment action id
905 p_latest_run_assact_id => csr_rec.run_assignment_action_id, -- payroll assignment action id
906 p_pre_effective_date => csr_rec.pre_effective_date -- prepayment effective date
907 );
908 -- Call to procedure to archive Employee Details
909 -- Bug No : 2496783 Correct time_period_id is passed to the following procedure calls
910 archive_employee_details (
911 p_payroll_action_id => g_archive_pact, -- archive payroll action id
912 p_assactid => csr_rec.chld_arc_assignment_action_id, -- archive action id
913 p_pay_assignment_action_id => csr_rec.run_assignment_action_id, -- payroll run action id
914 p_assignment_id => csr_rec.assignment_id, -- assignment_id
915 p_curr_pymt_ass_act_id => csr_rec.pre_assignment_action_id, -- prepayment assignment_action_id
916 p_date_earned => csr_rec.run_date_earned, -- payroll date_earned
917 p_latest_period_payment_date => l_payment_date, -- latest payment date /*BugNo:5495382*/
918 p_run_effective_date => csr_rec.run_effective_date, -- run effective Date
919 p_time_period_id => csr_rec.time_period_id, -- time_period_id from per_time_periods
920 p_pre_effective_date => csr_rec.pre_effective_date
921 -- prepayment effective date, bug 5730336
922 );
923 -- Call to procedure to archive accrual and absennce details
924 archive_accrual_details (
925 p_payroll_action_id => csr_rec.payroll_action_id, -- latest payroll action id
926 p_time_period_id => csr_rec.time_period_id, -- latest period time period id
927 p_assignment_id => csr_rec.assignment_id, -- assignment id
928 p_date_earned => csr_rec.run_date_earned, -- latest payroll date earned
929 p_effective_date => csr_rec.pre_effective_date, -- prepayment effective date
930 p_assact_id => csr_rec.chld_arc_assignment_action_id, -- archive assignment action id
931 p_assignment_action_id => csr_rec.run_assignment_action_id, -- payroll run action id
932 p_period_end_date => csr_rec.period_end_date, -- latest period end date
933 p_period_start_date => csr_rec.period_start_date -- latest period start date
934 );
935 END IF;
936 l_pre_pay_assact_id := csr_rec.pre_assignment_action_id;
937 END LOOP;
938 --
939 hr_utility.set_location('End of archive code',37);
940 EXCEPTION
941 WHEN OTHERS THEN
942 hr_utility.set_location('Error in archive code :',11);
943 RAISE;
944 END archive_code;
945
946
947 END pay_sg_payslip_archive;