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