[Home] [Help]
PACKAGE BODY: APPS.PER_HU_EMP_CERT_ARCHIVE
Source
1 PACKAGE BODY per_hu_emp_cert_archive as
2 /* $Header: pehuecar.pkb 120.1 2005/06/27 22:56:38 alikhar noship $ */
3
4 --------------------------------------------------------------------------------
5 -- GET_PARAMETER
6 --------------------------------------------------------------------------------
7 FUNCTION get_parameter(
8 p_parameter_string IN VARCHAR2
9 ,p_token IN VARCHAR2
10 ) RETURN VARCHAR2 IS
11 --
12 l_parameter pay_payroll_actions.legislative_parameters%TYPE;
13 l_start_pos NUMBER;
14 l_delimiter VARCHAR2(1);
15 --
16 BEGIN
17 l_parameter := NULL;
18 l_delimiter := ' ';
19 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
20 IF l_start_pos = 0 THEN
21 l_delimiter := '|';
22 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
23 END IF;
24 IF l_start_pos <> 0 THEN
25 l_start_pos := l_start_pos + length(p_token||'=');
26 l_parameter := substr(p_parameter_string,
27 l_start_pos,
28 instr(p_parameter_string||' ',
29 l_delimiter,l_start_pos)
30 - l_start_pos);
31
32 END IF;
33 RETURN l_parameter;
34 END get_parameter;
35 --------------------------------------------------------------------------------
36 -- GET_ALL_PARAMETERS
37 --------------------------------------------------------------------------------
38 PROCEDURE get_all_parameters(p_payroll_action_id IN NUMBER
39 ,p_business_group_id OUT NOCOPY NUMBER
40 ,p_start_date OUT NOCOPY DATE
41 ,p_end_date OUT NOCOPY DATE
42 ,p_payroll_id OUT NOCOPY NUMBER
43 ,p_issue_date OUT NOCOPY DATE
44 ) IS
45 --
46 CURSOR csr_parameter_info (c_payroll_action_id NUMBER) IS
47 SELECT get_parameter(legislative_parameters, 'PAYROLL_ID')
48 ,fnd_date.canonical_to_date(get_parameter(legislative_parameters, 'DATE'))
49 ,start_date
50 ,effective_date
51 ,business_group_id
52 FROM pay_payroll_actions
53 WHERE payroll_action_id = c_payroll_action_id;
54 --
55 BEGIN
56 --
57 OPEN csr_parameter_info (p_payroll_action_id);
58 FETCH csr_parameter_info INTO p_payroll_id
59 ,p_issue_date
60 ,p_start_date
61 ,p_end_date
62 ,p_business_group_id;
63 CLOSE csr_parameter_info;
64 --
65 END;
66 --
67 --------------------------------------------------------------------------------
68 -- RANGE_CODE
69 --------------------------------------------------------------------------------
70 PROCEDURE range_code(p_actid IN NUMBER
71 ,sqlstr OUT NOCOPY VARCHAR2)
72 IS
73 --
74 CURSOR csr_comp_address(c_business_group_id NUMBER) IS
75 SELECT hoi.organization_id organization_id
76 ,addr.postal_code c_postal_code
77 ,addr.town_or_city c_town
78 ,nvl(hr_general.decode_lookup('HU_COUNTY', addr.region_2),addr.region_2) c_county
79 ,addr.address_line_1 c_location_name
80 ,nvl(hr_general.decode_lookup('HU_LOCATION_TYPES', addr.address_line_2),addr.address_line_2) c_location_type
81 ,addr.address_line_3 c_street_number
82 ,addr.loc_information14 c_building
83 ,addr.loc_information15 c_stairway
84 ,addr.loc_information16 c_floor
85 ,addr.loc_information17 c_door
86 FROM hr_organization_information hoi
87 ,hr_all_organization_units hou
88 ,hr_locations_all addr
89 ,hr_organization_information hoi1
90 WHERE hou.organization_id = c_business_group_id
91 AND hoi.organization_id = hou.organization_id
92 AND hoi.org_information_context = 'HU_COMPANY_INFORMATION_DETAILS'
93 AND hoi1.organization_id = hou.organization_id
94 AND hoi1.org_information_context = 'CLASS'
95 AND hoi1.org_information1 = 'HU_COMPANY_INFORMATION'
96 AND hoi1.org_information2 = 'Y'
97 AND hou.location_id = addr.location_id (+)
98 ORDER BY hoi.organization_id ;
99
100 -- Variables for storing company's address
101 l_business_group_id hr_organization_units.business_group_id%type;
102 l_ovn NUMBER;
103 l_action_info_id NUMBER;
104 l_start_date DATE;
105 l_end_date DATE;
106 l_payroll_id NUMBER;
107 l_issue_date DATE;
108 --
109 BEGIN
110 --
111
112 get_all_parameters (p_actid
113 ,l_business_group_id
114 ,l_start_date
115 ,l_end_date
116 ,l_payroll_id
117 ,l_issue_date
118 );
119 --
120 sqlstr := 'select distinct person_id '||
121 'from per_people_f ppf, '||
122 'pay_payroll_actions ppa '||
123 'where ppa.payroll_action_id = :payroll_action_id '||
124 'and ppa.business_group_id = ppf.business_group_id '||
125 'order by ppf.person_id';
126 --
127
128 --
129 FOR c_rec IN csr_comp_address (l_business_group_id) LOOP
130
131 --Archiving Employer Address
132 pay_action_information_api.create_action_information (
133 p_action_information_id => l_action_info_id
134 , p_action_context_id => p_actid
135 , p_action_context_type => 'PA'
136 , p_object_version_number => l_ovn
137 , p_effective_date => l_issue_date
138 , p_action_information_category => 'ADDRESS DETAILS'
139 , p_action_information1 => c_rec.organization_id
140 , p_action_information5 => c_rec.c_location_name
141 , p_action_information6 => c_rec.c_location_type
142 , p_action_information7 => c_rec.c_street_number
143 , p_action_information8 => c_rec.c_town
144 , p_action_information10 => c_rec.c_county
145 , p_action_information12 => c_rec.c_postal_code
146 , p_action_information26 => c_rec.c_building
147 , p_action_information27 => c_rec.c_stairway
148 , p_action_information28 => c_rec.c_floor
149 , p_action_information29 => c_rec.c_door
150 , p_action_information14 => 'EMPLOYER');
151 --
152 END LOOP;
153 --
154 EXCEPTION
155 WHEN OTHERS THEN
156 -- Return cursor that selects no rows
157 sqlstr := 'select 1 '||
158 '/* ERROR - Employer Details Fetch failed with: '||
159 sqlerrm(sqlcode)||' */ '||
160 'from dual where to_char(:payroll_action_id) = dummy';
161 END range_code;
162
163 --------------------------------------------------------------------------------
164 -- ACTION_CREATION_CODE
165 --------------------------------------------------------------------------------
166 PROCEDURE action_creation_code (p_actid IN NUMBER
167 ,stperson IN NUMBER
168 ,endperson IN NUMBER
169 ,chunk IN NUMBER) IS
170
171 --
172 CURSOR csr_terminated_assignments(c_pact_id NUMBER
173 ,c_stperson NUMBER
174 ,c_endperson NUMBER
175 ,c_payroll_id NUMBER
176 ) IS
177 SELECT assignment_id
178 FROM per_all_assignments_f asl
179 ,per_periods_of_service ppos
180 ,pay_payroll_actions ppa
181 WHERE ppa.payroll_action_id = c_pact_id
182 AND asl.person_id BETWEEN c_stperson AND c_endperson
183 AND asl.primary_flag = 'Y'
184 AND ppos.period_of_service_id = asl.period_of_service_id
185 AND ppos.actual_termination_date BETWEEN asl.effective_end_date
186 AND asl.effective_end_date
187 AND asl.business_group_id = ppa.business_group_id
188 AND nvl(asl.payroll_id,0) = nvl(c_payroll_id,nvl(asl.payroll_id,0))
189 AND ppos.actual_termination_date BETWEEN ppa.start_date
190 AND ppa.effective_date
191 AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
192 FROM pay_payroll_actions appa
193 ,pay_assignment_actions act -- Bug Fix 4369797 Changed table order
194 ,pay_action_information pai
195 WHERE appa.action_status = 'C'
196 AND appa.report_type = 'HU_EMP_CERT'
197 AND appa.report_category = 'ARCHIVE'
198 AND appa.report_qualifier = 'HU' -- Bug Fix 4369797
199 AND appa.action_type = 'X' -- Added
200 AND act.action_status = 'C' -- some
201 AND pai.action_context_type = 'AAP' -- new conditions
202 AND pai.action_information_category = 'HU_EMP_CERTIFICATION'
203 AND act.assignment_id = asl.assignment_id
204 AND act.payroll_action_id = appa.payroll_action_id
205 AND pai.action_context_id = act.assignment_action_id
206 )
207 ORDER BY assignment_id;
208
209 l_actid NUMBER;
210 l_prepay_action_id NUMBER;
211 l_Payroll_id NUMBER;
212 l_start_date DATE;
213 l_end_date DATE;
214 l_business_group_id NUMBER;
215 l_issue_date DATE;
216
217 BEGIN
218 --
219
220 --
221 get_all_parameters (p_actid
222 ,l_business_group_id
223 ,l_start_date
224 ,l_end_date
225 ,l_payroll_id
226 ,l_issue_date);
227 --
228 FOR csr_rec IN csr_terminated_assignments(p_actid
229 ,stperson
230 ,endperson
231 ,l_payroll_id) LOOP
232 --
233 SELECT pay_assignment_actions_s.NEXTVAL
234 INTO l_actid
235 FROM dual;
236 -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
237 hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,p_actid,chunk,NULL);
238 END LOOP;
239
240 END action_creation_code;
241 --------------------------------------------------------------------------------
242 -- ARCHIVE_CODE
243 --------------------------------------------------------------------------------
244 PROCEDURE archive_code (p_assactid in number,
245 p_effective_date in date) IS
246
247
248 l_person_id per_all_people_f.person_id%TYPE;
249 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
250 l_end_date DATE;
251
252 BEGIN
253
254 -- get Employee data
255 get_employee_data(p_assactid
256 ,l_assignment_id
257 ,p_effective_date
258 ,l_person_id
259 ,l_end_date
260 );
261
262 get_person_address(l_person_id
263 ,p_assactid
264 ,l_assignment_id
265 ,l_end_date
266 ,p_effective_date
267 );
268
269 END archive_code;
270
271 --------------------------------------------------------------------------------
272 -- GET_PERSON_ADDRESS
273 --------------------------------------------------------------------------------
274 PROCEDURE get_person_address(p_person_id IN NUMBER
275 ,p_assactid IN NUMBER
276 ,p_assignment_id IN NUMBER
277 ,p_termination_date IN DATE
278 ,p_effective_date IN DATE
279 )IS
280
281 CURSOR csr_person_addr IS
282 SELECT addr.postal_code
283 ,addr.town_or_city town
284 ,addr.address_line1 location_name
285 ,nvl(hr_general.decode_lookup('HU_LOCATION_TYPES', addr.address_line2),addr.address_line2) location_type
286 ,addr.address_line3 street_number
287 ,addr.add_information14 building
288 ,addr.add_information15 stairway
289 ,addr.add_information16 floor
290 ,addr.add_information17 door
291 FROM per_addresses addr
292 WHERE addr.person_id = p_person_id
293 AND addr.primary_flag = 'Y'
294 AND p_termination_date between addr.date_from and
295 nvl(addr.date_to,fnd_date.canonical_to_date('4712/12/31'));
296
297 l_addr csr_person_addr%ROWTYPE;
298 l_found BOOLEAN;
299 l_action_info_id NUMBER;
300 l_ovn NUMBER;
301 BEGIN
302
303 OPEN csr_person_addr;
304 FETCH csr_person_addr INTO l_addr;
305 l_found := csr_person_addr%FOUND;
306 CLOSE csr_person_addr;
307
308 IF l_found THEN
309
310 -- Archiving Employee Address Information
311 pay_action_information_api.create_action_information (
312 p_action_information_id => l_action_info_id
313 , p_action_context_id => p_assactid
314 , p_action_context_type => 'AAP'
315 , p_object_version_number => l_ovn
316 , p_assignment_id => p_assignment_id
317 , p_effective_date => p_effective_date
318 , p_action_information_category => 'ADDRESS DETAILS'
319 , p_action_information1 => p_person_id
320 , p_action_information5 => l_addr.location_name
321 , p_action_information6 => l_addr.location_type
322 , p_action_information7 => l_addr.street_number
323 , p_action_information8 => l_addr.town
324 , p_action_information12 => l_addr.postal_code
325 , p_action_information26 => l_addr.building
326 , p_action_information27 => l_addr.stairway
327 , p_action_information28 => l_addr.floor
328 , p_action_information29 => l_addr.door
329 , p_action_information14 => 'EMPLOYEE'
330 );
331 END IF;
332 END get_person_address;
333
334 --------------------------------------------------------------------------------
335 -- GET_EMPLOYEE_DATA
336 --------------------------------------------------------------------------------
337 PROCEDURE get_employee_data(p_assactid IN NUMBER
338 ,p_assignment_id IN OUT NOCOPY NUMBER
339 ,p_effective_date IN DATE
340 ,p_person_id IN OUT NOCOPY NUMBER
341 ,p_end_date IN OUT NOCOPY DATE
342 ) IS
343
344 CURSOR csr_employee_data IS
345 SELECT pap.person_id
346 ,pap.full_name
347 ,pap.previous_last_name Maiden_name
348 ,pap.national_identifier social_security_code
349 ,pap.town_of_birth place_of_birth
350 ,fnd_date.date_to_chardate(pap.date_of_birth) date_of_birth
351 ,pap.per_information1 mother_maiden_name
352 ,fnd_date.date_to_chardate(ppf.date_start) hire_date
353 ,fnd_date.date_to_chardate(ppf.actual_termination_date) termination_date
354 ,ppf.actual_termination_date actual_termination_date
355 ,paa.assignment_id
356 ,decode(hr.segment2,'Y','Yes','No') railway_benefit
357 ,hoi.organization_id organization_id
358 ,hoi.org_information1 company_name
359 ,paas.payroll_action_id
360 FROM per_all_people_F pap
361 ,per_periods_of_service ppf
362 ,per_all_assignments_f paa
363 ,pay_assignment_actions paas
364 ,hr_soft_coding_keyflex hr
365 ,hr_organization_information hoi
366 WHERE paas.assignment_action_id = p_assactid
367 AND paas.assignment_id = paa.assignment_id
368 AND pap.person_id = ppf.person_id
369 AND ppf.business_group_id = paa.business_group_id
370 AND pap.person_id = paa.person_id
371 AND paa.period_of_service_id = ppf.period_of_service_id
372 AND hoi.organization_id = paa.business_group_id
373 AND hoi.org_information_context = 'HU_COMPANY_INFORMATION_DETAILS'
374 AND paa.soft_coding_keyflex_id = hr.soft_coding_keyflex_id (+)
375 AND ppf.actual_termination_date BETWEEN pap.effective_start_date
376 AND pap.effective_end_date
377 AND ppf.actual_termination_date BETWEEN paa.effective_start_date
378 AND paa.effective_end_date;
379
380 CURSOR csr_pension_data(c_assignment_id number, c_effective_date date, c_type varchar2) IS
381 SELECT eev.screen_entry_value start_date
382 ,pee.element_entry_id ppf_element_entry_id
383 ,pei.eei_information1 pf_scheme_name
384 ,hou.name provider_name
385 ,hou.organization_id provider_code
386 ,decode(addr.town_or_city,NULL,NULL,addr.town_or_city||',')
387 ||decode(addr.postal_code,NULL,NULL,' '||addr.postal_code)
388 ||decode(addr.address_line_1, NULL, NULL, ' '||addr.address_line_1)
389 ||decode(addr.address_line_2, NULL, NULL
390 , ' '||nvl(hr_general.decode_lookup('HU_LOCATION_TYPES', addr.address_line_2),addr.address_line_2))
391 ||decode(addr.address_line_3, NULL, NULL, ' '||addr.address_line_3||'. ')
392 ||decode(addr.loc_information14, NULL, NULL, addr.loc_information14||'.')
393 ||decode(addr.loc_information15, NULL, NULL, addr.loc_information15||'.')
394 ||decode(addr.loc_information16, NULL, NULL, addr.loc_information16||'.')
395 ||decode(addr.loc_information17, NULL, NULL, addr.loc_information17||'.') address
396 ,pee.personal_payment_method_id payment_method_id
397 FROM pay_element_entries_f pee
398 ,pay_element_entry_values_f eev
399 ,pay_input_values_f piv
400 ,pay_element_types_f pet
401 ,pay_element_type_extra_info pei
402 ,hr_organization_units hou
403 ,hr_locations_all addr
404 WHERE pee.element_entry_id = eev.element_entry_id
405 AND eev.input_value_id + 0 = piv.input_value_id
406 AND piv.element_type_id = pet.element_type_id
407 AND pee.assignment_id = c_assignment_id
408 AND piv.name = 'Override Start Date'
409 AND pet.element_type_id = pei.element_type_id
410 AND pei.eei_information_category = 'HU_PENSION_SCHEME_INFO'
411 AND pei.eei_information4 = c_type
412 AND pei.eei_information2 = hou.organization_id
413 AND hou.location_id = addr.location_id (+)
414 AND c_effective_date BETWEEN eev.effective_start_date
415 AND eev.effective_end_date
416 AND c_effective_date BETWEEN piv.effective_start_date
417 AND piv.effective_end_date
418 AND c_effective_date BETWEEN pet.effective_start_date
419 AND pet.effective_end_date
420 AND c_effective_date BETWEEN pee.effective_start_date
421 AND pee.effective_end_date;
422 --
423 CURSOR csr_account_details(c_effective_date date,c_payment_method_id number)IS
424 SELECT pea.segment2 bank_ac_no
425 FROM pay_personal_payment_methods_f ppp
426 ,pay_external_accounts pea
427 WHERE ppp.personal_payment_method_id = c_payment_method_id
428 AND ppp.external_account_id = pea.external_account_id
429 AND c_effective_date BETWEEN ppp.effective_start_date
430 AND ppp.effective_end_date;
431
432 --
433 CURSOR csr_element_start_date(p_element_entry_id NUMBER) IS
434 SELECT min(pee.effective_start_date)
435 FROM pay_element_entries_f pee
436 WHERE pee.element_entry_id = p_element_entry_id;
437 --
438 CURSOR csr_absence_days(c_person_id NUMBER
439 ,c_termination_year VARCHAR2) IS
440 SELECT SUM(paat.absence_days)
441 FROM per_absence_attendance_types pat
442 ,per_absence_attendances paat
443 WHERE pat.absence_attendance_type_id = paat.absence_attendance_type_id
444 AND pat.absence_category = 'S'
445 AND paat.person_id = c_person_id
446 AND to_char(paat.date_end,'YYYY') = c_termination_year;
447
448 --
449 CURSOR csr_sickness_holiday_taken(c_person_id number
450 ,c_termination_year varchar2
451 ,c_termination_date date
452 ) IS
453 SELECT sum(decode(sign(c_termination_date - paat.date_end),-1, c_termination_date, paat.date_end)
454 - decode(to_char(paat.date_start,'yyyy'),c_termination_year
455 ,paat.date_start, to_date('01-01-'||c_termination_year,'dd-mm-YYYY'))
456 + 1
457 ) sickness_leave_taken
458 FROM per_absence_attendance_types pat
459 ,per_absence_attendances paat
460 WHERE pat.ABSENCE_ATTENDANCE_TYPE_ID = paat.ABSENCE_ATTENDANCE_TYPE_ID
461 AND pat.ABSENCE_CATEGORY = 'S'
462 AND paat.person_id = c_person_id
463 AND to_char(paat.date_end,'YYYY') = c_termination_year;
464
465 --
466 CURSOR csr_pre_emp_sickness_holiday(c_person_id NUMBER
467 ,c_termination_year VARCHAR2
468 ,c_business_group_id NUMBER) IS
469 SELECT pem_information1
470 FROM per_previous_employers
471 WHERE business_group_id = c_business_group_id
472 AND person_id = c_person_id
473 AND to_char(end_date,'YYYY') = c_termination_year
474 ORDER BY end_date DESC;
475
476 --
477 CURSOR csr_pension_provider_code(c_meaning VARCHAR)IS
478 SELECT hrl.lookup_code
479 FROM hr_lookups hrl
480 WHERE hrl.lookup_type = 'HU_PENSION_PROVIDERS'
481 AND hrl.meaning = c_meaning
482 AND hrl.enabled_flag = 'Y' ;
483 --
484 l_found BOOLEAN;
485 l_employee_data csr_employee_data%rowtype;
486 l_action_info_id number;
487 l_ovn number;
488
489 l_ppension_data csr_pension_data%rowtype;
490 l_vpension_data csr_pension_data%rowtype;
491
492 l_Payroll_id NUMBER;
493 l_start_date DATE;
494 l_end_date DATE;
495 l_business_group_id NUMBER;
496 l_issue_date DATE;
497 l_pension_provider_code hr_lookups.lookup_code%TYPE;
498 l_ele_entry_start_date pay_element_entries_f.effective_start_date%TYPE;
499 l_pre_emp_sickness_holiday per_previous_employers.pem_information1%TYPE;
500 l_total_sickness_holiday NUMBER;
501 l_account_no pay_external_accounts.segment2%TYPE;
502 l_absence_days NUMBER;
503
504 TYPE t_vpp_name IS TABLE OF hr_organization_units.name%type INDEX BY BINARY_INTEGER;
505 l_vpp_name t_vpp_name;
506 lctr number;
507 --
508 BEGIN
509 --
510
511 OPEN csr_employee_data;
512 FETCH csr_employee_data INTO l_employee_data;
513 l_found := csr_employee_data%found;
514 CLOSE csr_employee_data;
515
516 IF l_found THEN
517 p_assignment_id := l_employee_data.assignment_id;
518 p_person_id := l_employee_data.person_id;
519 p_end_date := l_employee_data.actual_termination_date ;
520
521
522 get_all_parameters (l_employee_data.payroll_action_id
523 ,l_business_group_id
524 ,l_start_date
525 ,l_end_date
526 ,l_payroll_id
527 ,l_issue_date);
528
529 OPEN csr_pension_data (l_employee_data.assignment_id, l_employee_data.termination_date, 'PPF');
530 FETCH csr_pension_data INTO l_ppension_data;
531 CLOSE csr_pension_data;
532 --
533
534 OPEN csr_account_details(l_employee_data.termination_date,l_ppension_data.payment_method_id);
535 FETCH csr_account_details INTO l_account_no;
536 CLOSE csr_account_details;
537 --
538
539 OPEN csr_element_start_date(l_ppension_data.ppf_element_entry_id);
540 FETCH csr_element_start_date INTO l_ele_entry_start_date;
541 CLOSE csr_element_start_date;
542 --
543
544 OPEN csr_pension_provider_code(l_ppension_data.provider_name);
545 FETCH csr_pension_provider_code INTO l_pension_provider_code;
546 CLOSE csr_pension_provider_code;
547 --
548 lctr := 1;
549 FOR v_pension_data IN csr_pension_data (l_employee_data.assignment_id, l_employee_data.termination_date, 'VPF') loop
550 l_vpp_name(lctr) := v_pension_data.provider_name;
551 lctr := lctr + 1;
552 END LOOP;
553
554 LOOP
555 EXIT WHEN lctr > 5;
556 l_vpp_name(lctr) := ' ';
557 lctr := lctr + 1;
558 END LOOP;
559 --
560 l_absence_days := NULL;
561
562 OPEN csr_absence_days (l_employee_data.person_id, to_char(l_employee_data.actual_termination_date,'YYYY'));
563 FETCH csr_absence_days INTO l_absence_days;
564 CLOSE csr_absence_days;
565
566 IF l_absence_days IS NULL THEN
567 OPEN csr_sickness_holiday_taken (l_employee_data.person_id, to_char(l_employee_data.actual_termination_date,'YYYY')
568 ,l_employee_data.actual_termination_date);
569 FETCH csr_sickness_holiday_taken INTO l_absence_days;
570 CLOSE csr_sickness_holiday_taken;
571 END IF;
572 --
573 OPEN csr_pre_emp_sickness_holiday(l_employee_data.person_id
574 ,to_char(l_employee_data.actual_termination_date,'YYYY')
575 ,l_business_group_id);
576 FETCH csr_pre_emp_sickness_holiday INTO l_pre_emp_sickness_holiday;
577 CLOSE csr_pre_emp_sickness_holiday;
578 --
579
580 l_total_sickness_holiday := NVL(l_absence_days,0) + to_number(NVL(l_pre_emp_sickness_holiday,'0')) ;
581
582 -- Archiving Employee Data
583 pay_action_information_api.create_action_information (
584 p_action_information_id => l_action_info_id
585 , p_action_context_id => p_assactid
586 , p_action_context_type => 'AAP'
587 , p_object_version_number => l_ovn
588 , p_assignment_id => l_employee_data.assignment_id
589 , p_effective_date => p_effective_date
590 , p_action_information_category => 'HU_EMP_CERTIFICATION'
591 , p_action_information1 => l_employee_data.person_id
592 , p_action_information2 => l_employee_data.organization_id
593 , p_action_information5 => l_employee_data.company_name
594 , p_action_information6 => l_employee_data.full_name
595 , p_action_information7 => l_employee_data.Maiden_name
596 , p_action_information8 => l_employee_data.social_security_code
597 , p_action_information9 => l_employee_data.place_of_birth
598 , p_action_information10 => l_employee_data.date_of_birth
599 , p_action_information11 => l_employee_data.mother_maiden_name
600 , p_action_information12 => l_employee_data.hire_date
601 , p_action_information13 => l_employee_data.Termination_date
602 , p_action_information14 => l_total_sickness_holiday
603 , p_action_information15 => l_employee_data.railway_benefit
604 , p_action_information16 => fnd_date.date_to_displaydate(nvl(fnd_date.canonical_to_date(l_ppension_data.start_date),l_ele_entry_start_date))
605 , p_action_information17 => l_pension_provider_code
606 , p_action_information18 => l_ppension_data.provider_name
607 , p_action_information19 => l_ppension_data.address
608 , p_action_information20 => l_account_no
609 , p_action_information21 => l_vpp_name(1)
610 , p_action_information22 => l_vpp_name(2)
611 , p_action_information23 => l_vpp_name(3)
612 , p_action_information24 => l_vpp_name(4)
613 , p_action_information25 => l_vpp_name(5)
614 , p_action_information26 => fnd_date.date_to_displaydate(l_issue_date)
615 );
616 END IF;
617 END get_employee_data;
618
619 END per_hu_emp_cert_archive;