1 PACKAGE BODY pay_kr_payslip_archive AS
2 /* $Header: pykrparc.pkb 120.2 2006/12/08 05:05:03 pdesu noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 /********************************************************************************
7 -- This procedure returns a sql string to select a range
8 -- of assignments eligible for archival.
9 -- This range_code calls the APAC common procedure
10 -- 'pay_apac_payslip_archive.range_code' to archive the EIT's balance and
11 -- elements.And also this common procedure archives the payroll action
12 -- level data(e.g. Messages etc),because as this KR legislative range_code is
13 -- not multi-threaded procedure.
14 -- This common 'pay_apac_payslip_archive.range_code' procedure takes
15 -- care of the actual archival of EIT's data
16 ********************************************************************************/
17
18 PROCEDURE range_code(p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE
19 ,sqlstr OUT NOCOPY VARCHAR2)
20 IS
21 BEGIN
22
23 IF g_debug then
24 hr_utility.trace(' Start of range_code');
25 END IF;
26
27 -- call to APAC common archive procedure,which actually archives the EIT
28 -- balances and elements values and also the payroll action level data(like messages..etc.)
29 pay_apac_payslip_archive.range_code(p_payroll_action_id => p_payroll_action_id);
30
31 -- Bug No: 3580598
32 pay_core_payslip_utils.range_cursor(p_payroll_action_id,
33 sqlstr);
34
35 IF g_debug THEN
36 hr_utility.trace('End of range_code');
37 END IF;
38
39 EXCEPTION
40 WHEN OTHERS THEN
41 hr_utility.trace('Error occured in range code');
42 RAISE;
43 END range_code;
44
45 /********************************************************************************
46 -- This procedure is used to set global contexts
47 -- This initialization_code calls the APAC common procedure
48 -- 'pay_apac_payslip_archive.initialization_code' to populate the EIT's
49 -- balance and elements values into global tables, because as it is multi-threaded
50 -- procedure(actual archival of EIT's data is not done from this procedure)
51 ********************************************************************************/
52
53 PROCEDURE initialization_code(p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE)
54 IS
55
56 -- cursor to get the archival effective_date
57
58 CURSOR csr_archive_effective_date(p_payroll_action_id NUMBER)
59 IS
60 SELECT ppa.effective_date
61 FROM pay_payroll_actions ppa
62 WHERE ppa.payroll_action_id = p_payroll_action_id;
63
64 BEGIN
65
66 if g_debug then
67 hr_utility.trace(' Start of Initialization Code');
68 end if;
69
70 OPEN csr_archive_effective_date(p_payroll_action_id);
71 FETCH csr_archive_effective_date INTO g_archive_effective_date;
72 CLOSE csr_archive_effective_date;
73
74 g_archive_payroll_action_id :=p_payroll_action_id;
75
76 if g_debug then
77 hr_utility.trace(' g_archive_effective_date......:'||g_archive_effective_date);
78 hr_utility.trace(' g_archive_payroll_action_id...:'||g_archive_payroll_action_id);
79 end if;
80
81 -- call to APAC common procedure "initilization_code" to populate the EIT's
82 -- balances and elements values into global tables
83
84 pay_apac_payslip_archive.initialization_code(
85 p_payroll_action_id => p_payroll_action_id );
86
87 if g_debug then
88 hr_utility.trace('Exiting from initliazation Code');
89 end if;
90
91 EXCEPTION
92 WHEN OTHERS THEN
93 hr_utility.trace('Error occured in initialization code');
94 RAISE;
95 END initialization_code;
96
97
98 /********************************************************************************
99 -- This procedure further restricts the assignment_id's returned by
100 -- range_code.
101 ********************************************************************************/
102 PROCEDURE assignment_action_code(p_payroll_action_id IN NUMBER
103 ,p_start_person IN NUMBER
104 ,p_end_person IN NUMBER
105 ,p_chunk IN NUMBER)
106 IS
107
108 BEGIN
109 IF g_debug then
110 hr_utility.trace(' Start of assignment action code');
111 END IF;
112 -- Bug No: 3580598
113 pay_core_payslip_utils.action_creation (
114 p_payroll_action_id,
115 p_start_person,
116 p_end_person,
117 p_chunk,
118 'KR_PAYSLIP_ARCHIVE',
119 'KR');
120 IF g_debug then
121 hr_utility.trace('End of Assignment action code');
122 END IF;
123
124 EXCEPTION
125 WHEN OTHERS THEN
126 hr_utility.trace('Error occured in Assignment action code');
127 RAISE;
128 END assignment_action_code;
129
130 /********************************************************************************
131 -- This procedure archives all the standard earnings and deductions
132 -- elements into pay_action_information with action_information_category
133 -- as 'APAC ELEMENTS'.
134 ********************************************************************************/
135
136 PROCEDURE archive_kr_standard_elements
137 (p_pre_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
138 ,p_pre_effective_date IN DATE
139 ,p_arch_assignment_action_id IN NUMBER)
140 IS
141
142 -- cursor to fetch the run result values of earnings and deduction
143 -- elements.
144 CURSOR csr_std_elements(p_pre_assignment_action_id NUMBER)
145 IS
146 SELECT element_reporting_name
147 ,classification_name
148 ,amount
149 ,round(hours,2) hours
150 FROM pay_kr_asg_elements_v
151 WHERE assignment_action_id = p_pre_assignment_action_id
152 ORDER BY element_reporting_name;
153
154 l_action_info_id NUMBER;
155 l_ovn NUMBER;
156
157 BEGIN
158
159 if g_debug then
160 hr_utility.trace('Entering the archive_kr_standard_elements procedure');
161 end if;
162
163 FOR csr_record IN csr_std_elements(p_pre_assignment_action_id)
164 LOOP
165
166 pay_action_information_api.create_action_information
167 (p_action_information_id => l_action_info_id
168 ,p_action_context_id => p_arch_assignment_action_id
169 ,p_action_context_type => 'AAP'
170 ,p_object_version_number => l_ovn
171 ,p_effective_date => p_pre_effective_date
172 ,p_source_id => NULL
173 ,p_source_text => NULL
174 ,p_action_information_category=> 'APAC ELEMENTS'
175 ,p_action_information1 => csr_record.element_reporting_name
176 ,p_action_information2 => NULL
177 ,p_action_information3 => NULL
178 ,p_action_information4 => csr_record.classification_name
179 ,p_action_information5 => fnd_number.number_to_canonical(csr_record.amount) --3604142
180 ,p_action_information7 => csr_record.hours
181 );
182
183 END LOOP;
184
185 if g_debug then
186 hr_utility.trace('Exiting the archive_kr_standard_elements procedure');
187 end if;
188
189 EXCEPTION
190 WHEN OTHERS THEN
191 hr_utility.trace('Error raised in Archiving KR Standard Elements');
192 RAISE;
193
194 END archive_kr_standard_elements;
195
196 /********************************************************************************
197 -- This procedure archives Annual Leave information of an employee into
198 -- pay_action_information with action_information_category as
199 -- 'APAC ABSENCES'.
200 ********************************************************************************/
201
202 PROCEDURE archive_leave_balances
203 (p_arch_assignment_action_id IN NUMBER
204 ,p_run_assignment_action_id IN NUMBER
205 ,p_run_payroll_action_id IN NUMBER
206 ,p_assignment_id IN NUMBER
207 ,p_period_end_date IN DATE
208 ,p_pre_effective_date IN DATE)
209 IS
210 -- cursor to fetch the accrual plan id and the accruals UOM
211 -- and latest runs effective date info
212
213 CURSOR csr_leave_bal(
214 p_run_assignment_action_id NUMBER
215 ,p_assignment_id NUMBER )
216 IS
217 SELECT pap.accrual_plan_id accrual_plan_id
218 ,pap.business_group_id business_group_id
219 ,pap.accrual_plan_element_type_id accrual_plan_element_type_id
220 ,pap.accrual_plan_name accrual_plan_name
221 ,pap.accrual_category accrual_category
222 ,pap.accrual_start accrual_start_date
223 ,ppa.payroll_id payroll_id
224 ,pap.accrual_units_of_measure accrual_units_of_measure
225 ,hoi.org_information13 leave_taken_dim
226 ,pac.assignment_id
227 FROM pay_accrual_plans pap
228 ,pay_assignment_actions pac
229 ,pay_payroll_actions ppa
230 ,hr_organization_information hoi
231 ,pay_element_links_f pel
232 ,pay_element_entries_f pee
233 ,pay_element_types_f pet
234 WHERE pac.assignment_action_id = p_run_assignment_action_id
235 AND pac.assignment_id = p_assignment_id
236 AND pac.payroll_action_id = ppa.payroll_action_id
237 /* AND pel.payroll_id = ppa.payroll_id */ -- Bug 2891590
238 AND ppa.action_type IN ('R','Q')
239 AND ppa.action_status = 'C'
240 AND pel.element_type_id = pet.element_type_id
241 AND pee.element_link_id = pel.element_link_id
242 AND pee.assignment_id = pac.assignment_id
243 AND pet.element_type_id = pap. accrual_plan_element_type_id
244 AND pac.tax_unit_id = hoi.organization_id
245 AND hoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
246 AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
247 AND ppa.effective_date BETWEEN pel.effective_start_date AND pel.effective_end_date
248 AND ppa.effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
249
250 l_from_date DATE;
251 l_leave_taken NUMBER;
252 l_leave_balance NUMBER;
253 l_start_date DATE;
254 l_end_date DATE;
255 l_current_start_date VARCHAR2(20);
256 l_current_end_date VARCHAR2(20);
257 l_accrual_end_date DATE;
258 l_accrual NUMBER;
259 l_net_entilement NUMBER;
260 l_action_info_id NUMBER;
261 l_ovn NUMBER;
262
263 BEGIN
264
265 if g_debug then
266 hr_utility.trace(' Start of Archive Leave Balances');
267 end if;
268
269 FOR leave_rec IN csr_leave_bal(p_run_assignment_action_id
270 ,p_assignment_id)
271 LOOP
272 if g_debug then
273 hr_utility.trace('..l_taken_dimension..:'||leave_rec.leave_taken_dim);
274 end if;
275
276 IF leave_rec.leave_taken_dim= 'KRCTD' THEN
277
278 l_from_date := to_date('01-01-'||to_char(p_period_end_date,'RRRR'),'DD-MM-YYYY');
279
280 ELSIF leave_rec.leave_taken_dim = 'KRFTD' THEN
281
282 l_from_date := to_date('01-04-'||to_char(p_period_end_date,'RRRR'),'DD-MM-YYYY');
283
284 END IF;
285
286 if g_debug then
287 hr_utility.trace('....l_from_date........:'||l_from_date);
288 end if;
289
290 l_leave_taken:=per_accrual_calc_functions.get_absence(
291 p_assignment_id => p_assignment_id
292 ,p_plan_id => leave_rec.accrual_plan_id
293 ,p_calculation_date => p_period_end_date
294 ,p_start_date => l_from_date );
295
296 if g_debug then
297 hr_utility.trace(' .......Leaves Taken...:'||l_leave_taken);
298 end if;
299
300 l_current_start_date:=fnd_date.date_to_canonical(l_from_date);
301 l_current_end_date :=fnd_date.date_to_canonical(p_period_end_date);
302
303 if g_debug then
304 hr_utility.trace('....l_current_start_date....:'|| l_current_start_date);
305 hr_utility.trace('....l_current_end_date......:'|| l_current_end_date);
306 end if;
307
308 per_accrual_calc_functions.get_net_accrual(
309 p_assignment_id => p_assignment_id
310 ,p_plan_id => leave_rec.accrual_plan_id
311 ,p_payroll_id => leave_rec.payroll_id
312 ,p_business_group_id => leave_rec.business_group_id
313 ,p_assignment_action_id => p_run_assignment_action_id
314 ,p_calculation_date => p_period_end_date
315 ,p_accrual_start_date => l_from_date
316 ,p_start_date => l_start_date
317 ,p_end_date => l_end_date
318 ,p_accrual_end_date => l_accrual_end_date
319 ,p_accrual => l_accrual
320 ,p_net_entitlement => l_net_entilement
321 );
322
323 l_leave_balance := l_accrual - l_leave_taken;
324
325 if g_debug then
326 hr_utility.trace('..Leave Balances...........:'|| l_leave_balance);
327 hr_utility.trace('..archiving the ABSENCES Info');
328 end if;
329
330 pay_action_information_api.create_action_information (
331 p_action_information_id => l_action_info_id
332 ,p_action_context_id => p_arch_assignment_action_id
333 ,p_action_context_type => 'AAP'
334 ,p_object_version_number => l_ovn
335 ,p_effective_date => p_pre_effective_date
336 ,p_source_id => NULL
337 ,p_source_text => NULL
338 ,p_action_information_category=> 'APAC ABSENCES'
339 ,p_action_information1 => NULL
340 ,p_action_information2 => NULL
341 ,p_action_information4 => l_current_start_date
342 ,p_action_information5 => l_current_end_date
343 ,p_action_information6 => fnd_number.number_to_canonical(l_leave_balance) --3604142
344 ,p_action_information7 => leave_rec.accrual_units_of_measure
345 ,p_action_information8 => fnd_number.number_to_canonical(l_leave_taken) --3604142
346 );
347
348 END LOOP;
349
350 EXCEPTION
351 WHEN OTHERS THEN
352 hr_utility.trace('Error occured in archive_leave_balances');
353 RAISE;
354 END archive_leave_balances;
355
356 /********************************************************************************
357 -- This procedure populates the message saying
358 -- 'Multiple Run types exists for this Pay Advice' into
359 -- pay_message_lines table for the assignment which have more than one
360 -- Run type for the current payment.
361 -- This is the seeded message.
362 ********************************************************************************/
363
364 PROCEDURE populate_multiple_runtypes_msg(
365 p_line_sequence IN NUMBER
366 ,p_payroll_id IN NUMBER
367 ,p_message_level IN CHAR
368 ,p_arch_assignment_action_id IN NUMBER
369 ,p_source_type IN CHAR)
370
371 IS
372
373 l_message_text fnd_new_messages.message_text%TYPE;
374
375 BEGIN
376
377 if g_debug then
378 hr_utility.trace(' Start Of populate_multiple_runtypes_msg');
379 end if;
380
381 fnd_message.set_name('PAY','PAY_KR_RUN_TYPE_WARNING_MESG');
382
383 l_message_text := fnd_message.get;
384
385 if g_debug then
386 hr_utility.trace(' l_message_text..:'||l_message_text);
387 hr_utility.trace(' Inserting the multiple run types message into PAY_MESSAGE_LINES table');
388 end if;
389
390 INSERT INTO pay_message_lines(line_sequence,
391 payroll_id,
392 message_level,
393 source_id,
394 source_type,
395 line_text)
396 VALUES(
397 p_line_sequence
398 ,p_payroll_id
399 ,p_message_level
400 ,p_arch_assignment_action_id
401 ,p_source_type
402 ,l_message_text
403 );
404
405
406 if g_debug then
407 hr_utility.trace(' End Of populate_multiple_runtypes_msg');
408 end if;
409
410 EXCEPTION
411 WHEN OTHERS THEN
412 hr_utility.trace('Error occured in archive_leave_balances');
413 RAISE;
414
415 END populate_multiple_runtypes_msg;
416
417 /********************************************************************************
418 -- This procedure archives the Employee Other information like
419 -- Second Grade, Grade Point , Job Title, Seniority and Run Type Name
420 -- into pay_action_information with action_information_category as
421 -- 'KR EMPLOYEE DETAILS'.
422 ********************************************************************************/
423
424 PROCEDURE archive_employee_other_info(
425 p_arch_assignment_action_id IN NUMBER
426 ,p_assignment_id IN NUMBER
427 ,p_run_effective_date IN DATE
428 ,p_pre_effective_date IN DATE
429 ,p_run_type_id IN NUMBER )
430 IS
431
432
433 -- cursor to fetch the KR Additional Information like
434 -- KR Job title, KR Seniority ,Second Grade , and Grade Point
435
436 CURSOR csr_additional_info(p_assignment_id NUMBER
437 ,p_run_effective_date DATE)
438 IS
439 SELECT paa.assignment_id
440 ,hr_general.decode_lookup('KR_JOB_TITLE',hsck.segment2) kr_job_title
441 ,hr_general.decode_lookup('KR_SENIORITY',hsck.segment3) kr_seniority
442 ,pkg.grade_name second_grade
443 ,pgp.grade_point_name grade_point
444 FROM hr_soft_coding_keyflex hsck
445 ,per_assignments_f paa
446 ,per_kr_grades pkg
447 ,per_kr_g_points pgp
448 WHERE hsck.soft_coding_keyflex_id(+) = paa.soft_coding_keyflex_id
449 AND paa.assignment_id = p_assignment_id
450 AND pkg.grade_id (+) = hsck.segment4 -- This segement4 stores the grade_id
451 AND pgp.grade_point_id (+) = hsck.segment5 -- This segement5 stores the grade point id
452 AND p_run_effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date;
453
454 -- cursor to fetch the run type name
455
456 CURSOR csr_run_type_name(p_run_type_id NUMBER)
457 IS
458 SELECT prtl.run_type_name
459 FROM pay_run_types_f_vl prtl
460 WHERE prtl.run_type_id = p_run_type_id;
461
462 l_kr_job_title VARCHAR2(150):=null;
463 l_kr_seniority VARCHAR2(150):=null;
464 l_second_grade per_kr_grades.grade_name%TYPE;
465 l_grade_point per_kr_g_points.grade_point_name%TYPE;
466 l_kr_run_type_name pay_run_types_f_vl.run_type_name%TYPE;
467 l_action_info_id NUMBER;
468 l_ovn NUMBER;
469
470 BEGIN
471
472 if g_debug then
473 hr_utility.trace(' Start Of archive_kr_employee_details');
474 end if;
475
476 FOR csr_record IN csr_additional_info(p_assignment_id,p_run_effective_date)
477 LOOP
478
479 l_kr_job_title := csr_record.kr_job_title;
480 l_kr_seniority := csr_record.kr_seniority;
481 l_second_grade := csr_record.second_grade;
482 l_grade_point := csr_record.grade_point;
483
484 END LOOP;
485
486 if g_debug then
487 hr_utility.trace(' l_kr_job_title ...:'||l_kr_job_title);
488 hr_utility.trace(' l_kr_seniority....:'||l_kr_seniority);
489 hr_utility.trace(' l_second_grade....:'||l_second_grade);
490 hr_utility.trace(' l_grade_point.....:'||l_grade_point);
491 end if;
492
493 OPEN csr_run_type_name(p_run_type_id);
494 FETCH csr_run_type_name INTO l_kr_run_type_name;
495 CLOSE csr_run_type_name;
496
497 if g_debug then
498 hr_utility.trace('l_kr_run_type_name...:'|| l_kr_run_type_name);
499 end if;
500
501 pay_action_information_api.create_action_information (
502 p_action_information_id => l_action_info_id
503 ,p_action_context_id => p_arch_assignment_action_id
504 ,p_action_context_type => 'AAP'
505 ,p_object_version_number => l_ovn
506 ,p_effective_date => p_pre_effective_date
507 ,p_source_id => NULL
508 ,p_source_text => NULL
509 ,p_action_information_category=> 'KR EMPLOYEE DETAILS'
510 ,p_action_information21 => l_second_grade
511 ,p_action_information22 => l_kr_run_type_name
512 ,p_action_information23 => l_kr_job_title
513 ,p_action_information24 => l_kr_seniority
514 ,p_action_information25 => l_grade_point
515 );
516
517 if g_debug then
518 hr_utility.trace(' End Of archive_kr_employee_details');
519 end if;
520
521 EXCEPTION
522 WHEN OTHERS THEN
523 hr_utility.trace('Error occured in archive_kr_employee_details');
524 RAISE;
525 END archive_employee_other_info;
526
527 /********************************************************************************
528 -- This procedure calls 'pay_emp_action_arch.get_personal_information' that actually
529 -- archives the employee details,employee address details, Employer Address Details
530 -- and Net Pay Distribution inforamation. Procedure 'get_personal_informatio' is
531 -- is passed tax_unit_id to make core provided 'Choose Payslip' work for us.
532 -- The action DF structures used are -
533 -- ADDRESS DETAILS
534 -- EMPLOYEE DETAILS
535 -- EMPLOYEE NET PAY DISTRIBUTION
536 -- EMPLOYEE OTHER INFORMATION
537 -- After core procedure completes the archival, the information stored for category
538 -- EMPLOYEE_NET_PAY_DISTRIBUTION is updated with bank name specific to Korea
539 -- using action_information5. Core procedure actually stores the bank branch number in
540 -- action_information5
541 -- And also updates action_information18 with the 'KR Business Place' for the
542 -- EMPLOYEE DETAILS action_information_category.Core procedure actually stores the
543 -- Oragnization Name.
544 ********************************************************************************/
545
546 PROCEDURE archive_employee_details (
547 p_assignment_action_id IN NUMBER
548 ,p_assignment_id IN NUMBER
549 ,p_current_pymt_ass_act_id IN NUMBER
550 ,p_date_earned IN DATE
551 ,p_current_pymt_eff_date IN DATE
552 ,p_time_period_id IN NUMBER
553 ,p_tax_unit_id IN NUMBER
554 ,p_run_action_id IN NUMBER
555 ,p_run_effective_date IN DATE)
556 IS
557 -- cursor for getting the KR Bank Name
558
559 CURSOR csr_net_pay(p_assignment_action_id IN NUMBER)
560 IS
561 SELECT pai.action_information_id
562 ,pai.action_information5
563 ,nvl(hr_general_utilities.get_lookup_Meaning('KR_BANK',substr(pai.action_information5,1,2)),' ') bank_name
564 FROM pay_action_information pai
565 WHERE action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
566 AND action_context_id = p_assignment_action_id
567 AND action_context_type = 'AAP';
568
569 -- cursor for getting the Employer Address archived by core package
570
571 CURSOR csr_emp_details(p_assignment_action_id IN NUMBER)
572 IS
573 SELECT pai.action_information_id
574 ,pai.action_information18
575 FROM pay_action_information pai
576 WHERE action_information_category = 'EMPLOYEE DETAILS'
577 AND action_context_id = p_assignment_action_id
578 AND action_context_type = 'AAP';
579
580 -- cursor for retrieving the Business Place for Korea
581
582 CURSOR csr_business_place(p_tax_unit_id NUMBER)
583 IS
584 SELECT hoi.org_information1 business_place
585 FROM hr_organization_information hoi
586 WHERE hoi.organization_id = p_tax_unit_id
587 AND hoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION';
588
589
590 l_ovn NUMBER;
591 l_kr_business_place hr_organization_information.org_information1%TYPE;
592
593 BEGIN
594
595 if g_debug then
596 hr_utility.trace(' Start Of archive_employee_details');
597 end if;
598
599 pay_emp_action_arch.get_personal_information(
600 p_payroll_action_id => g_archive_payroll_action_id -- arch payroll action id
601 ,p_assactid => p_assignment_action_id -- arch assignment action id
602 ,p_assignment_id => p_assignment_id -- current assignment id
603 ,p_curr_pymt_ass_act_id => p_current_pymt_ass_act_id -- prepay ass_act_id
604 ,p_curr_eff_date => p_run_effective_date -- Latest payroll runs effective_date
605 ,p_date_earned => p_date_earned -- payroll date earned
606 ,p_curr_pymt_eff_date => p_current_pymt_eff_date -- payment_date
607 ,p_tax_unit_id => p_tax_unit_id -- Business place id
608 ,p_time_period_id => p_time_period_id -- payroll time_period_id
609 ,p_ppp_source_action_id => NULL
610 ,p_run_action_id => p_run_action_id -- Latest run asst_act_id
611 );
612
613 if g_debug then
614 hr_utility.trace(' Updating the Bank Details..');
615 end if;
616
617 FOR net_pay_rec IN csr_net_pay(p_assignment_action_id)
618 LOOP
619 l_ovn :=1;
620
621 if g_debug then
622 hr_utility.trace(' Bank Name....:'||net_pay_rec.bank_name);
623 end if;
624
625 pay_action_information_api.update_action_information (
626 p_action_information_id => net_pay_rec.action_information_id
627 ,p_object_version_number => l_ovn
628 ,p_action_information9 => net_pay_rec.bank_name );
629 END LOOP;
630
631 if g_debug then
632 hr_utility.trace(' Updating the Employer Name with Business Place');
633 end if;
634
635 OPEN csr_business_place(p_tax_unit_id);
636 FETCH csr_business_place INTO l_kr_business_place;
637 CLOSE csr_business_place;
638
639 if g_debug then
640 hr_utility.trace(' Business Place ..:'|| l_kr_business_place);
641 end if;
642
643 FOR emp_rec IN csr_emp_details(p_assignment_action_id)
644 LOOP
645
646 l_ovn :=1;
647
648 if g_debug then
649 hr_utility.trace(' Updating the Employer Name..:'||emp_rec.action_information18);
650 hr_utility.trace(' with Business Place ..:'||l_kr_business_place);
651 end if;
652
653 pay_action_information_api.update_action_information (
654 p_action_information_id => emp_rec.action_information_id
655 ,p_object_version_number => l_ovn
656 ,p_action_information18 => l_kr_business_place);
657
658 END LOOP;
659
660 if g_debug then
661 hr_utility.trace(' End Of archive_employee_details');
662 end if;
663
664 EXCEPTION
665 WHEN OTHERS THEN
666 hr_utility.trace('Error occured in archive_employee_details');
667 RAISE;
668 END archive_employee_details;
669
670 /********************************************************************************
671 -- archive_code code calls the following procedures to archive the
672 -- information required for the Korea Self service Online-Payslip
673
674 -- archive_employee_details : Which archives the employee details
675 -- archive_kr_standard_elements : archives the standards elements that
676 are processed by payroll run.
677 -- archive_leave_balances : archives the anuual leave information
678 and also the accrual plan information
679 -- archive_employee_other_info : archive other info like KR job title,
680 KR Grade and KR Seniority.
681 -- populate_multiple_runtypes_msg: Inserts the message into pay_message_lines
682 if there are more then one run type exists
683 for the current payment.
684 -- pay_apac_payslip_archive.
685 archive_user_elements : This is the APAC common package is used to
686 archive the user Configurable elments
687 information.
688 -- pay_apac_payslip_archive.
689 archive_user_balances : This is the APAC common package is used to
690 archive the user Configurable Balances
691 information.
692 ********************************************************************************/
693
694 PROCEDURE archive_code (p_assignment_action_id IN NUMBER
695 ,p_effective_date IN DATE)
696 IS
697 CURSOR get_payslip_aa(p_master_aa_id number) -- Bug No: 3580598
698 IS
699 SELECT paa_arch_chd.assignment_action_id chld_arc_assignment_action_id,
700 paa_pre.assignment_action_id pre_assignment_action_id,
701 paa_run.assignment_action_id run_assignment_action_id,
702 ppa_pre.effective_date prepayment_effective_date,
703 paa_arch_chd.assignment_id assignment_id,
704 ppa_run.effective_date run_effective_date,
705 ppa_run.date_earned date_earned,
706 ptp.end_date period_end_date,
707 ptp.time_period_id time_period_id,
708 paa_run.payroll_action_id run_payroll_action_id,
709 ppa_run.payroll_id payroll_id,
710 ppa_run.run_type_id run_type_id,
711 paa_run.tax_unit_id tax_unit_id
712 FROM pay_assignment_actions paa_arch_chd,
713 pay_assignment_actions paa_arch_mst,
714 pay_assignment_actions paa_pre,
715 pay_action_interlocks pai_pre,
716 pay_assignment_actions paa_run,
717 pay_action_interlocks pai_run,
718 pay_payroll_actions ppa_pre,
719 pay_payroll_actions ppa_run,
720 per_time_periods ptp
721 WHERE paa_arch_mst.assignment_action_id = p_master_aa_id
722 AND paa_arch_chd.source_action_id = paa_arch_mst.assignment_action_id
723 AND paa_arch_chd.payroll_action_id = paa_arch_mst.payroll_action_id
724 AND paa_arch_chd.assignment_id = paa_arch_mst.assignment_id
725 AND pai_pre.locking_action_id = paa_arch_mst.assignment_action_id
726 AND pai_pre.locked_action_id = paa_pre.assignment_action_id
727 AND pai_run.locking_action_id = paa_arch_chd.assignment_action_id
728 AND pai_run.locked_action_id = paa_run.assignment_action_id
729 AND ppa_pre.payroll_action_id = paa_pre.payroll_action_id
730 AND ppa_pre.action_type in ('P','U')
731 AND ppa_run.payroll_action_id = paa_run.payroll_action_id
732 AND ppa_run.action_type in ('R','Q')
733 AND ptp.payroll_id = ppa_run.payroll_id
734 AND ppa_run.date_earned between ptp.start_date
735 AND ptp.end_date
736 -- Get the highest in sequence for this payslip
737 AND paa_run.action_sequence = (SELECT max(paa_run2.action_sequence)
738 FROM pay_assignment_actions paa_run2,
739 pay_action_interlocks pai_run2
740 WHERE pai_run2.locking_action_id =
741 paa_arch_chd.assignment_action_id
742 AND pai_run2.locked_action_id =
743 paa_run2.assignment_action_id
744 );
745 -- cursor to fetch the period end date ,regular_payment_date
746 CURSOR csr_period_end_date(p_assignment_action_id NUMBER)
747 IS
748 SELECT ptp.end_date end_date,
749 ptp.regular_payment_date regular_payment_date,
750 ptp.time_period_id time_period_id
751 FROM per_time_periods ptp
752 ,pay_payroll_actions ppa
753 WHERE ptp.payroll_id = ppa.payroll_id
754 AND ppa.payroll_action_id = ( SELECT payroll_action_id
755 FROM pay_assignment_actions
756 WHERE assignment_action_id =p_assignment_action_id)
757 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
758
759 -- Bug 5600114
760 CURSOR csr_get_termination_date(p_assignment_action_id NUMBER)
761 IS
762 SELECT pps.actual_termination_date
763 FROM per_all_assignments_f paf,
764 per_periods_of_service pps,
765 pay_assignment_actions paa,
766 per_time_periods ptp,
767 pay_payroll_actions ppa
768 WHERE paa.assignment_action_id = p_assignment_action_id
769 AND ptp.payroll_id = ppa.payroll_id
770 AND paf.assignment_id = paa.assignment_id
771 AND ppa.payroll_action_id = paa.payroll_action_id
772 AND pps.period_of_service_id = paf.period_of_service_id
773 AND pps.actual_termination_date between paf.effective_start_date and paf.effective_end_date
774 AND pps.actual_termination_date between ptp.start_date AND ptp.end_date;
775
776 -- cursor to fetch the sequence_number for populating the message
777 -- for multiple run types
778 CURSOR csr_msg_sequence
779 IS
780 SELECT pay_message_lines_s.nextval
781 FROM dual;
782
783 l_run_type_id NUMBER:=0;
784 l_run_type_count NUMBER:=0;
785 l_pre_assignment_action_id NUMBER:=0;
786 l_latest_run_type_id NUMBER;
787 l_payroll_id NUMBER;
788 l_line_sequence NUMBER;
789 l_period_end_date DATE;
790 l_payment_date DATE;
791 l_regular_payment_date DATE;
792 l_time_period_id per_time_periods.time_period_id%TYPE;
793
794 BEGIN
795
796 if g_debug then
797 hr_utility.trace('Entering the archive_code');
798 end if;
799 -- Bug No: 3580598
800 pay_core_payslip_utils.generate_child_actions(p_assignment_action_id,
801 p_effective_date);
802 l_pre_assignment_action_id:=0;
803
804 FOR csr_master_record IN get_payslip_aa(p_assignment_action_id) LOOP
805 IF g_debug then
806 hr_utility.trace(' ..for master assignment action id..:'||csr_master_record.run_assignment_action_id);
807 hr_utility.trace(' ..for assignment_id..:'||csr_master_record.assignment_id);
808 END IF;
809
810 -- This condition is for to archive the data once for each prepayment
811 -- So for the case of two payroll runs under the single prepayment we have to archive
812 -- employee information based on the latest payroll runs action id
813 --
814 IF l_pre_assignment_action_id <> csr_master_record.pre_assignment_action_id THEN
815
816 OPEN csr_period_end_date(csr_master_record.run_assignment_action_id);
817 FETCH csr_period_end_date INTO l_period_end_date , l_regular_payment_date,l_time_period_id;
818 CLOSE csr_period_end_date;
819
820 -- Bug 5600114
821 OPEN csr_get_termination_date(csr_master_record.run_assignment_action_id);
822 FETCH csr_get_termination_date into l_payment_date;
823 IF csr_get_termination_date%NOTFOUND then
824 l_payment_date := l_regular_payment_date;
825 END IF;
826 CLOSE csr_get_termination_date;
827
828 if g_debug then
829 hr_utility.trace(' ..l_period_end_date...:'||l_period_end_date);
830 hr_utility.trace(' ..l_payment_date ...:'||l_payment_date);
831 end if;
832
833 archive_employee_details (
834 -- 3580598
835 p_assignment_action_id => csr_master_record.chld_arc_assignment_action_id -- arch ass_action_id
836 ,p_assignment_id => csr_master_record.assignment_id -- assignment_id
837 ,p_current_pymt_ass_act_id => csr_master_record.pre_assignment_action_id -- prepay ass_action_id
838 ,p_date_earned => csr_master_record.date_earned -- payroll date_earned
839 ,p_current_pymt_eff_date => l_payment_date -- payment date
840 ,p_time_period_id => l_time_period_id
841 ,p_tax_unit_id => csr_master_record.tax_unit_id -- business place id
842 ,p_run_action_id => csr_master_record.run_assignment_action_id
843 ,p_run_effective_date => csr_master_record.run_effective_date);
844
845 archive_kr_standard_elements(
846 p_pre_assignment_action_id => csr_master_record.pre_assignment_action_id
847 ,p_pre_effective_date => csr_master_record.prepayment_effective_date
848 -- 3580598
849 ,p_arch_assignment_action_id => csr_master_record.chld_arc_assignment_action_id --archival ass_action_id
850 );
851
852 pay_apac_payslip_archive.archive_user_elements(
853 -- 3580598
854 p_arch_assignment_action_id => csr_master_record.chld_arc_assignment_action_id
855 ,p_pre_assignment_action_id => csr_master_record.pre_assignment_action_id
856 ,p_latest_run_assact_id => csr_master_record.run_assignment_action_id
857 ,p_pre_effective_date => csr_master_record.prepayment_effective_date
858 );
859
860 pay_apac_payslip_archive.archive_user_balances(
861 -- 3580598
862 p_arch_assignment_action_id => csr_master_record.chld_arc_assignment_action_id
863 ,p_run_assignment_action_id => csr_master_record.run_assignment_action_id
864 ,p_pre_effective_date => csr_master_record.prepayment_effective_date
865 );
866
867 archive_leave_balances(
868 -- 3580598
869 p_arch_assignment_action_id => csr_master_record.chld_arc_assignment_action_id
870 ,p_run_assignment_action_id => csr_master_record.run_assignment_action_id
871 ,p_run_payroll_action_id => csr_master_record.run_payroll_action_id
872 ,p_assignment_id => csr_master_record.assignment_id
873 ,p_period_end_date => l_period_end_date
874 ,p_pre_effective_date => csr_master_record.prepayment_effective_date
875 );
876
877 archive_employee_other_info(
878 -- 3580598
879 p_arch_assignment_action_id => csr_master_record.chld_arc_assignment_action_id
880 ,p_assignment_id => csr_master_record.assignment_id
881 ,p_run_effective_date => csr_master_record.run_effective_date
882 ,p_pre_effective_date => csr_master_record.prepayment_effective_date
883 ,p_run_type_id => csr_master_record.run_type_id
884 );
885
886 l_latest_run_type_id := csr_master_record.run_type_id;
887 l_payroll_id := csr_master_record.payroll_id;
888
889 END IF;
890
891 l_pre_assignment_action_id := csr_master_record.pre_assignment_action_id;
892
893 IF l_run_type_id <> csr_master_record.run_type_id THEN
894 l_run_type_count :=l_run_type_count + 1;
895 END IF;
896 l_run_type_id :=csr_master_record.run_type_id;
897
898 END LOOP; /* End of the csr_run_assignment_actions(p_assignment_action_id) Loop */
899 if g_debug then
900 hr_utility.trace(' l_run_type_count ....:'||l_run_type_count);
901 end if;
902 IF l_run_type_count > 1 THEN
903 OPEN csr_msg_sequence;
904 FETCH csr_msg_sequence INTO l_line_sequence;
905 CLOSE csr_msg_sequence;
906
907 populate_multiple_runtypes_msg(
908 p_line_sequence => l_line_sequence
909 ,p_payroll_id => l_payroll_id
910 ,p_message_level => 'W'
911 ,p_arch_assignment_action_id => p_assignment_action_id
912 ,p_source_type => 'A'
913 );
914 END IF;
915 if g_debug then
916 hr_utility.trace('Exiting from the archive_code');
917 end if;
918 EXCEPTION
919 WHEN OTHERS THEN
920 hr_utility.trace('Error raised in archive_code');
921 RAISE;
922 END archive_code;
923 END pay_kr_payslip_archive; /* End Of the Package Body */
[Home] [Help]