[Home] [Help]
PACKAGE BODY: APPS.PER_ES_COMP_CERT_ARCHIVE_PKG
Source
1 PACKAGE BODY per_es_comp_cert_archive_pkg as
2 /* $Header: peesccar.pkb 120.7 2006/05/12 00:52:18 grchandr noship $ */
3 --------------------------------------------------------------------------------
4 -- GET_ALL_PARAMETERS
5 --------------------------------------------------------------------------------
6 PROCEDURE get_all_parameters(p_payroll_action_id IN NUMBER
7 ,p_business_group_id OUT NOCOPY NUMBER
8 ,p_start_date OUT NOCOPY DATE
9 ,p_end_date OUT NOCOPY DATE
10 ,p_legal_employer OUT NOCOPY NUMBER) IS
11 --
12 CURSOR csr_parameter_info (c_payroll_action_id NUMBER) IS
13 SELECT get_parameters(c_payroll_action_id, 'Legal_Employer')
14 ,start_date
15 ,effective_date
16 ,business_group_id
17 FROM pay_payroll_actions
18 WHERE payroll_action_id = c_payroll_action_id;
19 --
20 BEGIN
21 --
22 OPEN csr_parameter_info (p_payroll_action_id);
23 FETCH csr_parameter_info INTO p_legal_employer
24 ,p_start_date
25 ,p_end_date
26 ,p_business_group_id;
27 CLOSE csr_parameter_info;
28 --
29 END;
30 --------------------------------------------------------------------------------
31 -- GET_PARAMETERS
32 --------------------------------------------------------------------------------
33 FUNCTION get_parameters(p_payroll_action_id IN NUMBER,
34 p_token_name IN VARCHAR2) RETURN VARCHAR2 IS
35
36 CURSOR csr_parameter_info IS
37 SELECT SUBSTR(legislative_parameters,
38 INSTR(legislative_parameters,p_token_name)+(LENGTH(p_token_name)+1),
39 INSTR(legislative_parameters,' ',
40 INSTR(legislative_parameters,p_token_name)))
41 FROM pay_payroll_actions
42 WHERE payroll_action_id = p_payroll_action_id;
43 --
44 l_token_value VARCHAR2(50);
45 --
46 BEGIN
47 --
48 OPEN csr_parameter_info;
49 FETCH csr_parameter_info INTO l_token_value;
50 CLOSE csr_parameter_info;
51 return(l_token_value);
52 END get_parameters;
53 --------------------------------------------------------------------------------
54 -- RANGE_CODE
55 --------------------------------------------------------------------------------
56 PROCEDURE range_code(p_actid IN NUMBER
57 ,sqlstr OUT NOCOPY VARCHAR2)
58 IS
59
60 --
61 CURSOR csr_legal_employer(c_business_group_id NUMBER, c_legal_employer NUMBER)IS
62 SELECT hoi1.organization_id organization_id
63 ,hoi2.org_information1 company_name
64 ,hoi2.org_information3 representative_title
65 ,hoi2.org_information8 cac
66 ,hoi2.org_information2 person_id
67 FROM hr_organization_information hoi1
68 ,hr_All_organization_units hou
69 ,hr_organization_information hoi2
70 WHERE hou.business_group_id = c_business_group_id
71 AND hoi1.organization_id = hou.organization_id
72 AND hoi2.organization_id = hou.organization_id
73 AND hou.organization_id = NVL(c_legal_employer,hou.organization_id)
74 AND hoi1.org_information_context = 'CLASS'
75 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
76 AND hoi2.org_information_context = 'ES_STATUTORY_INFO'
77 ORDER BY hoi1.organization_id ;
78 --
79 CURSOR csr_legal_representative_info(c_person_id NUMBER, c_effective_date DATE) IS
80 SELECT pap.full_name representative_name
81 ,decode(pap.per_information2, 'DNI', pap.per_information2, 'PASSPORT',pap.per_information3,NULL) dni_passport
82 FROM per_all_people_f pap
83 WHERE pap.person_id = c_person_id
84 AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
85 --
86
87 l_ovn NUMBER;
88 l_action_info_id NUMBER;
89 l_business_group_id hr_organization_units.business_group_id%type;
90 l_start_date DATE;
91 l_end_date DATE;
92 l_legal_employer number;
93 l_legal_representative_info csr_legal_representative_info%rowtype;
94 BEGIN
95 --
96 -- Return Range Cursor
97 -- Note: There must be one and only one entry of :payroll_action_id in
98 -- the string, and the statement must be ordered by person_id
99 --
100 get_all_parameters (p_actid
101 ,l_business_group_id
102 ,l_start_date
103 ,l_end_date
104 ,l_legal_employer);
105 --
106
107 --
108 sqlstr := 'select distinct person_id '||
109 'from per_people_f ppf, '||
110 'pay_payroll_actions ppa '||
111 'where ppa.payroll_action_id = :payroll_action_id '||
112 'and ppa.business_group_id = ppf.business_group_id '||
113 'order by ppf.person_id';
114 --
115
116 --
117 FOR c_rec IN csr_legal_employer(l_business_group_id, l_legal_employer) LOOP
118
119 OPEN csr_legal_representative_info(to_number(c_rec.person_id),l_end_date);
120 FETCH csr_legal_representative_info INTO l_legal_representative_info;
121 CLOSE csr_legal_representative_info;
122 --Archiving Employee Data
123 pay_action_information_api.create_action_information (
124 p_action_information_id => l_action_info_id
125 , p_action_context_id => p_actid
126 , p_action_context_type => 'PA'
127 , p_object_version_number => l_ovn
128 , p_assignment_id => NULL
129 , p_effective_date => l_end_date
130 , p_source_id => NULL
131 , p_source_text => NULL
132 , p_action_information_category => 'ES_CC_REP_EMPLOYER'
133 , p_action_information1 => c_rec.organization_id
134 , p_action_information4 => c_rec.company_name
135 , p_action_information5 => c_rec.cac
136 , p_action_information6 => l_legal_representative_info.representative_name
137 , p_action_information7 => l_legal_representative_info.dni_passport
138 , p_action_information8 => c_rec.representative_title);
139
140 --
141 get_employer_address(c_rec.organization_id
142 ,p_actid
143 ,l_end_date
144 );
145 END LOOP;
146 --
147 EXCEPTION
148 WHEN OTHERS THEN
149 -- Return cursor that selects no rows
150 sqlstr := 'select 1 '||
151 '/* ERROR - Employer Details Fetch failed with: '||
152 sqlerrm(sqlcode)||' */ '||
153 'from dual where to_char(:payroll_action_id) = dummy';
154 hr_utility.set_location(' Leaving: range code',110);
155 END range_code;
156 --------------------------------------------------------------------------------
157 -- ACTION_CREATION_CODE
158 --------------------------------------------------------------------------------
159 PROCEDURE action_creation_code (p_actid IN NUMBER
160 ,stperson IN NUMBER
161 ,endperson IN NUMBER
162 ,chunk IN NUMBER) IS
163
164 --
165 CURSOR csr_terminated_assignments(stperson NUMBER
166 ,endperson NUMBER
167 ,c_legal_employer NUMBER
168 ,c_start_date DATE
169 ,c_end_date DATE
170 ,c_business_group_id NUMBER) IS
171 SELECT assignment_id
172 FROM per_all_assignments_f asl
173 ,per_periods_of_service ppos
174 ,hr_soft_coding_keyflex hr
175 ,hr_organization_information hoi
176 WHERE asl.person_id BETWEEN stperson AND endperson
177 AND asl.primary_flag = 'Y'
178 AND ppos.period_of_service_id = asl.period_of_service_id
179 AND ppos.actual_termination_date BETWEEN c_start_date AND c_end_date
180 AND asl.effective_end_date = ppos.actual_termination_date
181 AND asl.business_group_id = c_business_group_id
182 AND hr.soft_coding_keyflex_id = asl.soft_coding_keyflex_id
183 AND hr.segment2 = hoi.org_information1
184 AND hoi.org_information_context = 'ES_WORK_CENTER_REF'
185 AND hoi.organization_id = decode(c_legal_employer,NULL,hoi.organization_id,c_legal_employer)
186 AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
187 FROM pay_payroll_actions appa
188 ,pay_assignment_actions act
189 ,pay_action_information pai
190 WHERE act.assignment_id = asl.assignment_id
191 AND act.payroll_action_id = appa.payroll_action_id
192 AND appa.report_category = 'ARCHIVE'
193 AND appa.action_status = 'C'
194 AND appa.report_qualifier = 'ES'
195 AND appa.report_type = 'ES_COMP_CERT'
196 AND pai.action_context_id = act.assignment_action_id
197 AND pai.action_information_category = 'ES_CC_REP_EMPLOYEE'
198 AND pai.action_information21 = 'T');
199 --
200 CURSOR csr_td_assignments(c_p_actid NUMBER
201 ,stperson NUMBER
202 ,endperson NUMBER
203 ,c_legal_employer NUMBER
204 ,c_start_date DATE
205 ,c_end_date DATE
206 ,c_business_group_id NUMBER) IS
207 SELECT assignment_id
208 FROM per_all_assignments_f asl
209 ,hr_soft_coding_keyflex hr
210 ,hr_organization_information hoi
211 ,per_absence_attendance_types pat
212 ,per_absence_attendances paa
213 ,pay_payroll_actions ppa
214 where ppa.payroll_action_id = c_p_actid
215 AND asl.person_id BETWEEN stperson AND endperson
216 AND asl.primary_flag = 'Y'
217 AND asl.business_group_id = ppa.business_group_id
218 AND paa.person_id = asl.person_id
219 AND pat.absence_attendance_type_id = paa.absence_attendance_type_id
220 AND pat.absence_category = 'TD'
221 AND pat.business_group_id = ppa.business_group_id
222 AND paa.business_group_id = ppa.business_group_id
223 AND paa.date_start between c_start_date AND c_end_date
224 AND hr.soft_coding_keyflex_id = asl.soft_coding_keyflex_id
225 AND hr.segment2 = hoi.org_information1
226 AND hoi.org_information_context = 'ES_WORK_CENTER_REF'
227 AND hoi.organization_id =NVL(c_legal_employer,hoi.organization_id)
228 AND c_end_date between asl.effective_start_date and asl.effective_end_date
229 AND NOT EXISTS (SELECT NULL
230 FROM pay_payroll_actions appa
231 ,pay_assignment_actions act
232 ,pay_action_information pai
233 WHERE act.assignment_id = asl.assignment_id
234 AND act.payroll_action_id = appa.payroll_action_id
235 AND appa.report_category = 'ARCHIVE'
236 AND appa.action_status = 'C'
237 AND appa.report_type = 'ES_COMP_CERT'
238 AND appa.report_qualifier = 'ES'
239 AND pai.action_context_id = act.assignment_action_id
240 AND pai.action_information_category = 'ES_CC_REP_EMPLOYEE'
241 AND pai.action_information21 = 'S'
242 AND pai.action_information22 = to_char(paa.absence_attendance_id))
243 AND NOT EXISTS (SELECT NULL
244 FROM pay_payroll_actions appa
245 ,pay_assignment_actions act
246 WHERE appa.payroll_action_id = c_p_actid
247 AND act.payroll_action_id = appa.payroll_action_id
248 AND act.assignment_id = asl.assignment_id);
249 --
250 l_actid NUMBER;
251 l_legal_employer NUMBER;
252 l_start_date DATE;
253 l_end_date DATE;
254 l_business_group_id NUMBER;
255 --
256 BEGIN
257 --
258 get_all_parameters (p_actid
259 ,l_business_group_id
260 ,l_start_date
261 ,l_end_date
262 ,l_legal_employer);
263 --
264 FOR csr_rec IN csr_terminated_assignments(stperson
265 ,endperson
266 ,l_legal_employer
267 ,l_start_date
268 ,l_end_date
269 ,l_business_group_id) LOOP
270 --
271 SELECT pay_assignment_actions_s.NEXTVAL
272 INTO l_actid
273 FROM dual;
274 -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
275 hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,p_actid,chunk,NULL);
276 END LOOP;
277 --
278 FOR csr_rec IN csr_td_assignments(p_actid
279 ,stperson
280 ,endperson
281 ,l_legal_employer
282 ,l_start_date
283 ,l_end_date
284 ,l_business_group_id) LOOP
285 --
286 SELECT pay_assignment_actions_s.NEXTVAL
287 INTO l_actid
288 FROM dual;
289 -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
290 hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,p_actid,chunk,NULL);
291 END LOOP;
292 --
293 END action_creation_code;
294 --------------------------------------------------------------------------------
295 -- ARCHIVE_CODE
296 --------------------------------------------------------------------------------
297 PROCEDURE archive_code (p_assactid in number,
298 p_effective_date in date) IS
299
300
301 l_person_id per_all_people_f.person_id%type;
302 l_assignment_id per_all_assignments_f.assignment_id%type;
303 l_type VARCHAR2(1);
304 l_end_date DATE;
305
306 BEGIN
307 -- get Employee data
308 get_employee_data(p_assactid
309 ,l_assignment_id
310 ,p_effective_date
311 ,l_person_id
312 ,l_end_date
313 ,l_type
314 );
315 get_person_address(l_person_id
316 ,p_assactid
317 ,l_assignment_id
318 ,l_end_date
319 ,p_effective_date
320 );
321 get_element_entries(p_assactid
322 ,l_assignment_id
323 ,l_end_date
324 ,l_type
325 );
326 END archive_code;
327
328 --------------------------------------------------------------------------------
329 -- GET_PERSON_ADDRESS
330 --------------------------------------------------------------------------------
331 PROCEDURE get_person_address(p_person_id IN NUMBER
332 ,p_assactid IN NUMBER
333 ,p_assignment_id IN NUMBER
334 ,p_termination_date IN DATE
335 ,p_effective_date IN DATE
336 )IS
337
338 CURSOR csr_person_addr IS
339 SELECT addr.address_line1 address_line1
340 ,addr.address_line2 address_line2
341 ,addr.address_line3 address_line3
342 ,addr.town_or_city town_or_city
343 ,hr_general.decode_lookup('ES_PROVINCE_CODES',addr.region_2) prov
344 ,addr.postal_code postal_code
345 FROM per_addresses addr
346 WHERE addr.person_id = p_person_id
347 AND addr.primary_flag = 'Y'
348 AND p_termination_date between addr.date_from and
349 nvl(addr.date_to,fnd_date.canonical_to_date('4712/12/31'));
350 --
351 l_addr csr_person_addr%rowtype;
352 l_found boolean;
353 l_province per_addresses.region_2%type ;
354 l_action_info_id NUMBER;
355 l_ovn NUMBER;
356 --
357 BEGIN
358 --
359 OPEN csr_person_addr;
360 FETCH csr_person_addr INTO l_addr;
361 l_found := csr_person_addr%found;
362 CLOSE csr_person_addr;
363 IF l_found THEN
364 -- Archiving Employee Address Information
365 pay_action_information_api.create_action_information (
366 p_action_information_id => l_action_info_id
367 , p_action_context_id => p_assactid
368 , p_action_context_type => 'AAP'
369 , p_object_version_number => l_ovn
370 , p_assignment_id => p_assignment_id
371 , p_effective_date => p_effective_date
372 , p_source_id => NULL
373 , p_source_text => NULL
374 , p_action_information_category => 'ADDRESS DETAILS'
375 , p_action_information1 => p_person_id
376 , p_action_information5 => l_addr.address_line1
377 , p_action_information6 => l_addr.address_line2
378 , p_action_information7 => l_addr.address_line3
379 , p_action_information8 => l_addr.town_or_city
380 , p_action_information10 => l_addr.prov
381 , p_action_information12 => l_addr.postal_code);
382
383 END IF;
384 END get_person_address;
385 --------------------------------------------------------------------------------
386 -- GET_EMPLOYER_ADDRESS
387 --------------------------------------------------------------------------------
388 PROCEDURE get_employer_address(p_organization_id IN NUMBER
389 ,p_actid IN NUMBER
390 ,p_effective_date IN DATE
391 ) IS
392 --
393 CURSOR csr_employer_addr(c_organization_id NUMBER) IS
394 SELECT addr.address_line_1||' - '||hr_general.decode_lookup('HR_ES_LOCATION_TYPES',addr.address_line_1) address_line1
395 ,addr.address_line_3 address_line3
396 ,addr.town_or_city town_or_city
397 ,hr_general.decode_lookup('ES_PROVINCE_CODES',addr.region_2) prov
398 ,addr.postal_code postal_code
399 ,addr.telephone_number_1 telephone_number
400 FROM hr_organization_units hou,
401 hr_locations_all addr
402 WHERE hou.organization_id = c_organization_id
403 AND hou.location_id = addr.location_id;
404 --
405 l_found BOOLEAN;
406 l_employer_addr csr_employer_addr%rowtype;
407 l_province per_addresses.region_2%type ;
408 l_action_info_id NUMBER;
409 l_ovn number;
410 --
411 BEGIN
412 OPEN csr_employer_addr(p_organization_id);
413 FETCH csr_employer_addr INTO l_employer_addr;
414 l_found := csr_employer_addr%found;
415 CLOSE csr_employer_addr;
416
417 IF l_found THEN
418 -- Archiving Legal Employer Address Information
419 pay_action_information_api.create_action_information (
420 p_action_information_id => l_action_info_id
421 , p_action_context_id => p_actid
422 , p_action_context_type => 'PA'
423 , p_object_version_number => l_ovn
424 , p_effective_date => p_effective_date
425 , p_action_information_category => 'ADDRESS DETAILS'
426 , p_action_information1 => p_organization_id
427 , p_action_information5 => l_employer_addr.address_line1
428 , p_action_information7 => l_employer_addr.address_line3
429 , p_action_information8 => l_employer_addr.town_or_city
430 , p_action_information10 => l_employer_addr.prov
431 , p_action_information12 => l_employer_addr.postal_code
432 , p_action_information26 => l_employer_addr.telephone_number);
433 END IF;
434 END get_employer_address;
435 --------------------------------------------------------------------------------
436 -- GET_EMPLOYEE_DATA
437 --------------------------------------------------------------------------------
438 PROCEDURE get_employee_data(p_assactid IN NUMBER
439 ,p_assignment_id IN OUT NOCOPY NUMBER
440 ,p_effective_date IN DATE
441 ,p_person_id IN OUT NOCOPY NUMBER
442 ,p_end_date IN OUT NOCOPY DATE
443 ,p_type IN OUT NOCOPY VARCHAR2
444 ) IS
445
446 CURSOR csr_employee_data IS
447 SELECT pap.person_id person_id
448 ,paa.assignment_id assignment_id
449 ,paa.business_group_id organization_id
450 ,pap.full_name emp_name
451 ,decode(pap.per_information2, 'NIE', NULL,pap.per_information3) dni_passport
452 ,paa.job_id job_id
453 ,pps.date_start start_date
454 ,pps.actual_termination_date end_date
455 ,pps.leaving_reason leaving_reason
456 ,hoi.organization_id legal_employer
457 ,hr.segment2 work_center_id
458 ,hr.segment5 cont_group
459 ,hr_general.decode_lookup('ES_PROFESSIONAL_CAT'
460 ,paa.employee_category) prof_catg
461 ,paa.soft_coding_keyflex_id sc_key_id
462 ,'T' type
463 , 0 abs_attn_id
464 ,to_date('01-01-0001','dd-mm-yyyy') sickness_start_date
465 ,to_date('31-12-4712','dd-mm-yyyy') sickness_end_date
466 ,pps.pds_information5 accrued_vacation
467 ,pps.pds_information6 vacation_accrued
468 ,pps.pds_information7 vacation_taken
469 ,pps.pds_information8 vacation_reamining
470 ,fnd_date.canonical_to_date(pps.pds_information9) vacation_from
471 ,fnd_date.canonical_to_date(pps.pds_information10) vacation_to
472 FROM per_all_people_f pap
473 ,per_all_assignments_f paa
474 ,per_periods_of_service pps
475 ,pay_assignment_actions paas
476 ,pay_payroll_actions ppa
477 ,hr_soft_coding_keyflex hr
478 ,hr_organization_information hoi
479 WHERE paas.assignment_action_id = p_assactid
480 AND paas.payroll_action_id = ppa.payroll_action_id
481 AND paa.assignment_id = paas.assignment_id
482 AND pap.person_id = paa.person_id
483 AND pap.person_id = pps.person_id
484 AND pps.period_of_service_id = paa.period_of_service_id
485 AND hr.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
486 AND hr.segment2 = hoi.org_information1
487 AND hoi.org_information_context = 'ES_WORK_CENTER_REF'
488 AND pps.actual_termination_date BETWEEN ppa.start_date
489 AND ppa.effective_date
490 AND pps.actual_termination_date BETWEEN pap.effective_start_date
491 AND pap.effective_end_date
492 AND pps.actual_termination_date BETWEEN paa.effective_start_date
493 AND paa.effective_end_date
494 UNION
495 SELECT pap.person_id person_id
496 ,paa.assignment_id assignment_id
497 ,paa.business_group_id organization_id
498 ,pap.full_name emp_name
499 ,decode(pap.per_information2, 'NIE', NULL,pap.per_information3) dni_passport
500 ,paa.job_id job_id
501 ,pps.date_start start_date
502 ,pps.actual_termination_date end_date
503 ,pps.leaving_reason leaving_reason
504 ,hoi.organization_id legal_employer
505 ,hr.segment2 work_center_id
506 ,hr.segment5 cont_group
507 ,hr_general.decode_lookup('ES_PROFESSIONAL_CAT'
508 ,paa.employee_category) prof_catg
509 ,paa.soft_coding_keyflex_id sc_key_id
510 ,'S' Type
511 ,paat.absence_attendance_id abs_attn_id
512 ,paat.date_start sickness_start_date
513 ,paat.date_end sickness_end_date
514 ,pps.pds_information5 accrued_vacation
515 ,pps.pds_information6 vacation_accrued
516 ,pps.pds_information7 vacation_taken
517 ,pps.pds_information8 vacation_reamining
518 ,fnd_date.canonical_to_date(pps.pds_information9) vacation_from
519 ,fnd_date.canonical_to_date(pps.pds_information10) vacation_to
520 FROM per_all_people_f pap
521 ,per_all_assignments_f paa
522 ,pay_assignment_actions paas
523 ,pay_payroll_actions ppa
524 ,per_periods_of_service pps
525 ,hr_soft_coding_keyflex hr
526 ,hr_organization_information hoi
527 ,per_absence_attendance_types pat
528 ,per_absence_attendances paat
529 WHERE paas.assignment_action_id = p_assactid
530 AND paas.payroll_action_id = ppa.payroll_action_id
531 AND paa.assignment_id = paas.assignment_id
532 AND pps.period_of_service_id (+)= paa.period_of_service_id
533 AND pap.person_id = paa.person_id
534 AND pap.person_id = pps.person_id
535 AND pap.effective_start_date = (select max(papf.effective_start_date)
536 from per_all_people_f papf
537 where papf.person_id = pap.person_id
538 AND papf.effective_start_date <= ppa.effective_date)
539 AND paa.effective_start_date = (select max(paaf.effective_start_date)
540 from per_all_assignments_f paaf
541 where paaf.assignment_id = paa.Assignment_id
542 AND paaf.effective_start_date <= ppa.effective_date)
543 AND hr.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
544 AND hr.segment2 = hoi.org_information1
545 AND hoi.org_information_context = 'ES_WORK_CENTER_REF'
546 AND pat.ABSENCE_ATTENDANCE_TYPE_ID = paat.ABSENCE_ATTENDANCE_TYPE_ID
547 AND pat.ABSENCE_CATEGORY = 'TD'
548 AND paat.person_id = pap.person_id
549 AND paat.date_start between ppa.start_date AND ppa.effective_date
550 order by Type desc;
551
552
553 CURSOR csr_contract_data(c_person_id number, c_effective_date date) IS
554 SELECT pcf.contract_id contract_id
555 ,hr_general.decode_lookup('CONTRACT_TYPE',pcf.type) contract_type
556 ,hr_contract_api.get_active_end_date (pcf.contract_id
557 ,p_effective_date,pcf.status) contract_end_date
558 FROM per_contracts_f pcf
559 where pcf.person_id = c_person_id
560 AND c_effective_date BETWEEN pcf.effective_start_date
561 AND pcf.effective_end_date;
562
563
564 CURSOR csr_get_ss_id(c_assignment_id number, c_effective_date date) IS
565 SELECT screen_entry_value ss_id
566 FROM pay_element_entries_f peef
567 ,pay_element_entry_values_f peev
568 ,pay_input_values_f piv
569 ,pay_element_types_f pet
570 WHERE pet.element_name = 'Social Security Details'
571 AND piv.element_type_id = pet.element_type_id
572 AND pet.legislation_code = 'ES'
573 AND piv.name ='Social Security Identifier'
574 AND peef.element_type_id = pet.element_type_id
575 AND peef.assignment_id = c_assignment_id
576 AND peev.element_entry_id = peef.element_entry_id
577 AND peev.input_value_id = piv.input_value_id
578 AND c_effective_date BETWEEN pet.effective_start_date
579 AND pet.effective_end_date
580 AND c_effective_date BETWEEN peef.effective_start_date
581 AND peef.effective_end_date
582 AND c_effective_date BETWEEN peev.effective_start_date
583 AND peev.effective_end_date
584 AND c_effective_date BETWEEN piv.effective_start_date
585 AND piv.effective_end_date;
586
587
588 l_found BOOLEAN;
589 l_employee_data csr_employee_data%rowtype;
590 l_contract_data csr_contract_data%rowtype;
591 l_emp_ss_id csr_get_ss_id%rowtype;
592 l_leaving_reason per_shared_types.information1%type;
593 l_action_info_id number;
594 l_emp_occupation per_jobs_tl.name%type;
595 l_ovn number;
596 l_vac_days number;
597 l_sickness_start_date date;
598
599
600 CURSOR csr_stat_leav_reas_bgspec(c_business_group_id NUMBER) IS
601 SELECT hr_general.decode_lookup('STAT_TERM_REASONS',information1) prov
602 FROM per_shared_types
603 WHERE lookup_type ='LEAV_REAS'
604 AND system_type_cd = l_employee_data.leaving_reason
605 AND business_group_id = c_business_group_id;
606
607 CURSOR csr_stat_leav_reas IS
608 SELECT hr_general.decode_lookup('STAT_TERM_REASONS',information1) prov
609 FROM per_shared_types
610 WHERE lookup_type ='LEAV_REAS'
611 AND system_type_cd = l_employee_data.leaving_reason
612 AND business_group_id IS NULL;
613
614 CURSOR csr_job_name(c_job_id NUMBER) IS
615 SELECT jbt.name
616 FROM per_jobs_tl jbt
617 WHERE jbt.language = userenv('LANG')
618 AND jbt.job_id = c_job_id;
619
620 CURSOR csr_get_wc_prov(c_wc_id NUMBER) IS
621 SELECT hr_general.decode_lookup('ES_PROVINCE_CODES',addr.region_2) prov
622 FROM hr_organization_units hou,
623 hr_locations_all addr
624 WHERE hou.organization_id = c_wc_id
625 AND hou.location_id = addr.location_id;
626
627 l_wc_prov hr_lookups.meaning%type;
628
629 BEGIN
630 OPEN csr_employee_data;
631 FETCH csr_employee_data INTO l_employee_data;
632 l_found := csr_employee_data%found;
633 CLOSE csr_employee_data;
634
635 IF l_found THEN
636
637 p_assignment_id := l_employee_data.assignment_id;
638 p_person_id := l_employee_data.person_id;
639 p_end_date := l_employee_data.end_date;
640 p_type := l_employee_data.type;
641
642 l_vac_days := nvl(l_employee_data.vacation_accrued,0) - nvl(l_employee_data.vacation_taken,0);
643 IF l_employee_data.vacation_accrued IS NULL
644 AND l_employee_data.vacation_taken IS NULL THEN
645 l_vac_days := NULL;
646 END IF;
647
648 IF p_type = 'S' THEN
649 p_end_date := l_employee_data.sickness_start_date;
650 END IF;
651
652 l_sickness_start_date := l_employee_data.sickness_start_date;
653
654 IF l_employee_data.sickness_start_date = to_date('01-01-0001','dd-mm-yyyy')
655 AND l_employee_data.sickness_end_date = to_date('31-12-4712','dd-mm-yyyy') then
656 l_sickness_start_date := null;
657 END IF;
658
659 OPEN csr_job_name(l_employee_data.job_id);
660 FETCH csr_job_name INTO l_emp_occupation;
661 CLOSE csr_job_name;
662
663 OPEN csr_get_wc_prov(l_employee_data.work_center_id);
664 FETCH csr_get_wc_prov INTO l_wc_prov;
665 CLOSE csr_get_wc_prov;
666
667 OPEN csr_stat_leav_reas_bgspec(l_employee_data.organization_id);
668 FETCH csr_stat_leav_reas_bgspec INTO l_leaving_reason;
669 IF csr_stat_leav_reas_bgspec%NOTFOUND THEN
670 OPEN csr_stat_leav_reas;
671 FETCH csr_stat_leav_reas INTO l_leaving_reason;
672 IF csr_stat_leav_reas%NOTFOUND THEN
673 l_leaving_reason := NULL;
674 END IF;
675 CLOSE csr_stat_leav_reas;
676 END IF;
677 CLOSE csr_stat_leav_reas_bgspec;
678
679 OPEN csr_contract_data(l_employee_data.person_id, p_end_date);
680 FETCH csr_contract_data INTO l_contract_data;
681 l_found := csr_contract_data%found;
682 CLOSE csr_contract_data;
683
684 OPEN csr_get_ss_id(l_employee_data.assignment_id, p_end_date);
685 FETCH csr_get_ss_id INTO l_emp_ss_id;
686 l_found := csr_get_ss_id%found;
687 CLOSE csr_get_ss_id;
688
689 -- Archiving Employee Data
690 pay_action_information_api.create_action_information (
691 p_action_information_id => l_action_info_id
692 , p_action_context_id => p_assactid
693 , p_action_context_type => 'AAP'
694 , p_object_version_number => l_ovn
695 , p_assignment_id => l_employee_data.assignment_id
696 , p_effective_date => p_effective_date
697 , p_action_information_category => 'ES_CC_REP_EMPLOYEE'
698 , p_action_information1 => l_employee_data.person_id
699 , p_action_information2 => l_employee_data.legal_employer
700 , p_action_information3 => l_employee_data.emp_name
701 , p_action_information4 => l_employee_data.dni_passport
702 , p_action_information5 => l_emp_ss_id.ss_id
703 , p_action_information6 => l_employee_data.cont_group
704 , p_action_information7 => l_employee_data.prof_catg
705 , p_action_information8 => l_emp_occupation
706 , p_action_information9 => fnd_date.date_to_displaydate(l_employee_data.start_date)
707 , p_action_information10 => fnd_date.date_to_displaydate(l_employee_data.end_date)
708 , p_action_information11 => fnd_date.date_to_displaydate(l_contract_data.contract_end_date)
709 , p_action_information12 => l_leaving_reason
710 , p_action_information13 => l_employee_data.accrued_vacation
711 , p_action_information14 => l_vac_days
712 , p_action_information15 => fnd_date.date_to_displaydate(l_employee_data.vacation_from)
713 , p_action_information16 => fnd_date.date_to_displaydate(l_employee_data.vacation_to)
714 , p_action_information17 => fnd_date.date_to_displaydate(l_sickness_start_date)
715 , p_action_information20 => l_contract_data.contract_type
716 , p_action_information21 => l_employee_data.type
717 , p_action_information22 => l_employee_data.abs_attn_id
718 , p_action_information23 => l_wc_prov);
719
720 END IF;
721 END get_employee_data;
722
723 --------------------------------------------------------------------------------
724 -- GET_ELEMENT_ENTRIES
725 --------------------------------------------------------------------------------
726 PROCEDURE get_element_entries(p_assactid IN NUMBER
727 ,p_assignment_id IN NUMBER
728 ,p_effective_date IN DATE
729 ,p_type IN VARCHAR2
730 ) IS
731
732 CURSOR csr_Contribution_base(c_assignment_id number, c_effective_date date) IS
733 SELECT pee.rowid row_id
734 ,pee.element_entry_id
735 ,min(decode(piv.name, 'Year', eev.screen_entry_value, null)) year
736 ,min(decode(piv.name, 'Month', hr_general.decode_lookup('ES_MONTH_NAMES',eev.screen_entry_value), null)) month
737 ,min(decode(piv.name, 'Contribution Days', eev.screen_entry_value, null)) contribution_days
738 ,min(decode(piv.name, 'Regular Situation Base', eev.screen_entry_value, null)) rs_cont_base
739 ,min(decode(piv.name, 'IA ID Contribution', eev.screen_entry_value, null)) ia_id_contribution
740 ,min(decode(piv.name, 'Note', eev.screen_entry_value, null)) note
741 ,min(decode(p_type,'S',decode(piv.name, 'Last TD Report Paid', eev.screen_entry_value, null),null)) last_TD_date
742 FROM pay_element_entries_f pee
743 ,pay_element_entry_values_f eev
744 ,pay_input_values_f piv
745 ,pay_element_types_f pet
746 WHERE pee.element_entry_id = eev.element_entry_id
747 AND c_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
748 AND eev.input_value_id + 0 = piv.input_value_id
749 AND c_effective_date BETWEEN eev.effective_start_date AND eev.effective_end_date
750 AND piv.element_type_id = pet.element_type_id
751 AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
752 AND pee.assignment_id = c_assignment_id
753 AND pet.element_name = decode(p_type,'T','Employee Termination Contribution Bases','Employee Temporary Disability Contribution Bases')
754 AND pet.legislation_code = 'ES'
755 AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
756 GROUP BY pee.rowid
757 ,pee.element_entry_id;
758
759 l_action_info_id number;
760 l_ovn number;
761
762 BEGIN
763 --
764 FOR l_cont_base IN csr_Contribution_base(p_assignment_id, p_effective_date)LOOP
765
766 -- Archiving Element Data
767
768 pay_action_information_api.create_action_information (
769 p_action_information_id => l_action_info_id
770 , p_action_context_id => p_assactid
771 , p_action_context_type => 'AAP'
772 , p_object_version_number => l_ovn
773 , p_assignment_id => p_assignment_id
774 , p_effective_date => p_effective_date
775 , p_action_information_category => 'ES_CC_REP_ELEMENT_INFO'
776 , p_action_information5 => p_type
777 , p_action_information6 => l_cont_base.year
778 , p_action_information7 => l_cont_base.month
779 , p_action_information8 => l_cont_base.contribution_days
780 , p_action_information9 => l_cont_base.RS_cont_base
781 , p_action_information10 => l_cont_base.IA_ID_Contribution
782 , p_action_information11 => l_cont_base.note
783 , p_action_information12 => fnd_date.date_to_displaydate(fnd_date.canonical_to_date(l_cont_base.last_td_date))
784 );
785
786 END LOOP;
787
788 END get_element_entries;
789
790 -------------------------------------------------------------------------------
791 -- WRITETOCLOB
792 --------------------------------------------------------------------------------
793 /*PROCEDURE WritetoCLOB (
794 p_xfdf_blob out nocopy blob)
795 IS
796 l_xfdf_string clob;
797 l_str1 varchar2(1000);
798 l_str2 varchar2(20);
799 l_str3 varchar2(20);
800 l_str4 varchar2(20);
801 l_str5 varchar2(20);
802 l_str6 varchar2(30);
803 l_str7 varchar2(1000);
804 l_str8 varchar2(240);
805 l_str9 varchar2(240);
806
807 BEGIN
808
809 l_str1 := '<?xml version="1.0" encoding="UTF-8"?>
810 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
811 <fields> ' ;
812 l_str2 := '<field name="';
813 l_str3 := '">';
814 l_str4 := '<value>' ;
815 l_str5 := '</value> </field>' ;
816 l_str6 := '</fields> </xfdf>';
817 l_str7 := '<?xml version="1.0" encoding="UTF-8"?>
818 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
819 <fields>
820 </fields> </xfdf>';
821 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
822 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
823
824 IF vXMLTable.count > 0 THEN
825 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
826 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
827 l_str8 := vXMLTable(ctr_table).TagName;
828 l_str9 := vXMLTable(ctr_table).TagValue;
829 --
830 IF (l_str9 is not null) THEN
831 dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2 );
832 dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
833 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );
834 dbms_lob.writeAppend( l_xfdf_string, length(l_str4), l_str4 );
835 dbms_lob.writeAppend( l_xfdf_string, length(l_str9), l_str9);
836 dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5 );
837 ELSE
838 null;
839 END IF;
840 END LOOP;
841 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6 );
842 ELSE
843 dbms_lob.writeAppend( l_xfdf_string, length(l_str7), l_str7 );
844 END IF;
845
846 DBMS_LOB.CREATETEMPORARY(p_xfdf_blob,TRUE);
847 clob_to_blob(l_xfdf_string,p_xfdf_blob);
848 --return p_xfdf_blob;
849 EXCEPTION
850 WHEN OTHERS then
851 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
852 HR_UTILITY.RAISE_ERROR;
853 END WritetoCLOB;*/
854 PROCEDURE WritetoCLOB (p_xfdf_blob out nocopy blob
855 ,p_xfdf_string out nocopy clob)
856 IS
857 l_str1 varchar2(1000);
858 l_str2 varchar2(20);
859 l_str3 varchar2(20);
860 l_str4 varchar2(20);
861 l_str5 varchar2(20);
862 l_str6 varchar2(30);
863 l_str7 varchar2(1000);
864 l_str8 varchar2(240);
865 l_str9 varchar2(240);
866 BEGIN
867 l_str1 := '<?xml version="1.0" encoding="UTF-8"?>
868 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
869 <fields> ' ;
870 l_str2 := '<field name="';
871 l_str3 := '">';
872 l_str4 := '<value><![CDATA[' ;
873 l_str5 := ']]></value> </field>' ;
874 l_str6 := '</fields> </xfdf>';
875 l_str7 := '<?xml version="1.0" encoding="UTF-8"?>
876 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
877 <fields>
878 </fields> </xfdf>';
879 dbms_lob.createtemporary(p_xfdf_string,FALSE,DBMS_LOB.CALL);
880 dbms_lob.open(p_xfdf_string,dbms_lob.lob_readwrite);
881 IF vXMLTable.count > 0 THEN
882 dbms_lob.writeAppend( p_xfdf_string, length(l_str1), l_str1 );
883 FOR ctr_table IN vXMLTable.FIRST .. vXMLTable.LAST LOOP
884 l_str8 := vXMLTable(ctr_table).TagName;
885 l_str9 := vXMLTable(ctr_table).TagValue;
886 --
887 IF (l_str9 is not null) THEN
888 dbms_lob.writeAppend( p_xfdf_string, length(l_str2), l_str2 );
889 dbms_lob.writeAppend( p_xfdf_string, length(l_str8),l_str8);
890 dbms_lob.writeAppend( p_xfdf_string, length(l_str3), l_str3 );
891 dbms_lob.writeAppend( p_xfdf_string, length(l_str4), l_str4 );
892 dbms_lob.writeAppend( p_xfdf_string, length(l_str9), l_str9);
893 dbms_lob.writeAppend( p_xfdf_string, length(l_str5), l_str5 );
894 ELSE
895 null;
896 END IF;
897 END LOOP;
898 dbms_lob.writeAppend( p_xfdf_string, length(l_str6), l_str6 );
899 ELSE
900 dbms_lob.writeAppend( p_xfdf_string, length(l_str7), l_str7 );
901 END IF;
902 DBMS_LOB.CREATETEMPORARY(p_xfdf_blob,TRUE);
903 clob_to_blob(p_xfdf_string,p_xfdf_blob);
904 --return p_xfdf_blob;
905 EXCEPTION
906 WHEN OTHERS then
907 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
908 HR_UTILITY.RAISE_ERROR;
909 END WritetoCLOB;
910 --------------------------------------------------------------------------------
911 -- CLOB_TO_BLOB
912 --------------------------------------------------------------------------------
913 PROCEDURE clob_to_blob(p_clob CLOB
914 ,p_blob IN OUT NOCOPY BLOB) IS
915 --
916 l_length_clob NUMBER;
917 l_offset pls_integer;
918 l_varchar_buffer VARCHAR2(32767);
919 l_raw_buffer RAW(32767);
920 l_buffer_len NUMBER;
921 l_chunk_len NUMBER;
922 l_blob blob;
923 g_nls_db_char VARCHAR2(60);
924 --
925 l_raw_buffer_len pls_integer;
926 l_blob_offset pls_integer := 1;
927 --
928 BEGIN
929 --
930 hr_utility.set_location('Entered Procedure clob to blob',120);
931 --
932 SELECT userenv('LANGUAGE') INTO g_nls_db_char FROM dual;
933 --
934 l_buffer_len := 20000;
935 l_length_clob := dbms_lob.getlength(p_clob);
936 l_offset := 1;
937 --
938 while l_length_clob > 0 loop
939 --
940 IF l_length_clob < l_buffer_len THEN
941 l_chunk_len := l_length_clob;
942 ELSE
943 l_chunk_len := l_buffer_len;
944 END IF;
945 --
946 DBMS_LOB.READ(p_clob,l_chunk_len,l_offset,l_varchar_buffer);
947 --
948 l_raw_buffer := utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.AL32UTF8',g_nls_db_char);
949 l_raw_buffer_len := utl_raw.length(utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.AL32UTF8',g_nls_db_char));
950 dbms_lob.write(p_blob,l_raw_buffer_len, l_blob_offset, l_raw_buffer);
951 --
952 l_blob_offset := l_blob_offset + l_raw_buffer_len;
953 l_offset := l_offset + l_chunk_len;
954 l_length_clob := l_length_clob - l_chunk_len;
955 --
956 END LOOP;
957 hr_utility.set_location('Finished Procedure clob to blob ',130);
958 END;
959 --------------------------------------------------------------------------------
960 -- FETCH_PDF_BLOB
961 --------------------------------------------------------------------------------
962 PROCEDURE fetch_pdf_blob
963 (p_pdf_blob OUT NOCOPY blob)
964
965 IS
966
967 BEGIN
968
969 SELECT file_data INTO p_pdf_blob
970 FROM fnd_lobs
971 WHERE file_id = (SELECT MAX(file_id) FROM per_gb_xdo_templates
972 WHERE file_name like '%ES_company_cert.pdf%');
973 EXCEPTION
974 WHEN no_data_found THEN
975 NULL;
976 END fetch_pdf_blob;
977 --
978 --------------------------------------------------------------------------------
979 -- POPULATE_COMP_CERT
980 --------------------------------------------------------------------------------
981 PROCEDURE populate_comp_cert
982 (p_request_id IN NUMBER
983 ,p_payroll_action_id NUMBER
984 ,p_legal_employer NUMBER
985 ,p_person_id NUMBER
986 ,p_xfdf_blob OUT NOCOPY BLOB
987 )IS
988 p_xfdf_string clob;
989 BEGIN
990 populate_plsql_table( p_request_id
991 ,p_payroll_action_id
992 ,p_legal_employer
993 ,p_person_id);
994 WritetoCLOB (p_xfdf_blob,p_xfdf_string);
995 END populate_comp_cert;
996
997 --------------------------------------------------------------------------------
998 -- POPULATE_PLSQL_TABLE
999 --------------------------------------------------------------------------------
1000 PROCEDURE populate_plsql_table
1001 (p_request_id IN NUMBER
1002 ,p_payroll_action_id NUMBER
1003 ,p_legal_employer NUMBER
1004 ,p_person_id NUMBER
1005 )IS
1006
1007 CURSOR csr_get_data IS
1008 SELECT substr(pai1.action_information4,1,40) company_name
1009 ,substr(pai1.action_information5,1,15) CAC
1010 ,substr(pai1.action_information6,1,40) representative_name
1011 ,pai1.action_information7 representative_DNI
1012 ,substr(pai1.action_information8,1,45) representative_Position
1013 ,substr(pai2.action_information5,1,40) cloc_type
1014 ,substr(pai2.action_information7,1,10) cloc_no
1015 ,substr(pai2.action_information8,1,16) ccity
1016 ,substr(pai2.action_information10,1,24) cprov_name
1017 ,substr(pai2.action_information12,1,7) cpostal_code
1018 ,substr(pai2.action_information26,1,16) ctel_no
1019 ,substr(pai3.action_information3,1,34) emp_name
1020 ,substr(pai3.action_information4,1,15) dni_passport
1021 ,pai3.action_information5 social_security_identifier
1022 ,pai3.action_information6 cont_grp
1023 ,substr(pai3.action_information7,1,15) prof_catg
1024 ,substr(pai3.action_information8,1,25) emp_occupation
1025 ,pai3.action_information9 start_date
1026 ,pai3.action_information10 end_date
1027 ,pai3.action_information11 contract_end_date
1028 ,substr(pai3.action_information12,1,30) leaving_reason
1029 ,pai3.action_information13 accured_vac
1030 ,pai3.action_information14 no_vac_days
1031 ,pai3.action_information15 vac_from
1032 ,pai3.action_information16 vac_till
1033 ,pai3.action_information17 sick_leave_start_date
1034 ,pai3.action_information18 number1
1035 ,pai3.action_information19 date1
1036 ,substr(pai3.action_information20,1,14) contract_type
1037 ,substr(pai3.action_information23,1,10) wc_prov
1038 ,substr(pai4.action_information5,1,15) eloc_type
1039 ,substr(pai4.action_information6,1,10) eloc_name
1040 ,substr(pai4.action_information7,1,8) eloc_no
1041 ,substr(pai4.action_information8,1,15) ecity
1042 ,substr(pai4.action_information10,1,13) eprov_name
1043 ,substr(pai4.action_information12,1,6) epostal_code
1044 ,paa.assignment_action_id
1045 FROM pay_payroll_actions ppa
1046 ,pay_assignment_actions paa
1047 ,pay_action_information pai1 --Employer rec
1048 ,pay_action_information pai2 --Employer Address
1049 ,pay_action_information pai3 --Employee rec
1050 ,pay_action_information pai4 --Employee address
1051 WHERE ppa.payroll_action_id = p_payroll_action_id
1052 AND ppa.payroll_action_id = paa.payroll_action_id
1053 AND pai1.action_context_id = ppa.payroll_action_id
1054 AND pai2.action_context_id (+)= pai1.action_context_id
1055 AND pai1.action_context_type = 'PA'
1056 AND pai2.action_context_type (+)= 'PA'
1057 AND pai1.action_information_category = 'ES_CC_REP_EMPLOYER'
1058 AND pai2.action_information_category(+)= 'ADDRESS DETAILS'
1059 AND pai1.action_information1 = pai2.action_information1(+)
1060 AND pai3.action_context_type = 'AAP'
1061 AND pai3.action_context_id = paa.assignment_action_id
1062 AND pai4.action_context_id (+)= pai3.action_context_id
1063 AND pai4.action_context_type (+)= 'AAP'
1064 AND pai3.action_information_category = 'ES_CC_REP_EMPLOYEE'
1065 AND pai4.action_information_category(+)= 'ADDRESS DETAILS'
1066 AND pai3.action_information1 = pai4.action_information1(+)
1067 AND pai3.action_information2 = pai1.action_information1
1068 AND pai1.action_information1 = NVL(p_legal_employer,pai1.action_information1)
1069 AND pai3.action_information1 = NVL(p_person_id,pai3.action_information1);
1070
1071
1072
1073 CURSOR get_element_details(c_assignment_action_id number) IS
1074 SELECT pai1.action_information5 Type
1075 ,pai1.action_information6 Year
1076 ,substr(pai1.action_information7,1,12) Month
1077 ,pai1.action_information8 contribution_days
1078 ,pai1.action_information9 contribution_base
1079 ,pai1.action_information10 ia_id_cont
1080 ,substr(pai1.action_information11,1,20) note
1081 FROM pay_action_information pai1
1082 WHERE pai1.action_context_id = c_assignment_action_id
1083 AND pai1.action_context_type = 'AAP'
1084 AND pai1.action_information_category = 'ES_CC_REP_ELEMENT_INFO'
1085 AND pai1.action_information5 = 'T';
1086
1087
1088 CURSOR get_emp_sickness_details(c_assignment_action_id number) IS
1089 SELECT pai1.action_information5 Type
1090 ,pai1.action_information6 Year
1091 ,substr(pai1.action_information7,1,12) Month
1092 ,pai1.action_information8 contribution_days
1093 ,pai1.action_information9 contribution_base
1094 ,pai1.action_information10 ia_id_cont
1095 ,substr(pai1.action_information11,1,20) note
1096 ,pai1.action_information12 last_td_date
1097 FROM pay_action_information pai1
1098 WHERE pai1.action_context_id = c_assignment_action_id
1099 AND pai1.action_context_type = 'AAP'
1100 AND pai1.action_information_category = 'ES_CC_REP_ELEMENT_INFO'
1101 AND pai1.action_information5 = 'S';
1102
1103
1104 lctr NUMBER;
1105 l_last_td_date VARCHAR2(30);
1106 l_sum_cont_days NUMBER;
1107 l_sum_cont_base NUMBER;
1108 l_sum_ia_id_cont NUMBER;
1109 BEGIN
1110
1111 vXMLTable.DELETE;
1112 vCtr := 1;
1113
1114 FOR c_rec in csr_get_data LOOP
1115 l_sum_cont_days := NULL;
1116 l_sum_cont_base := NULL;
1117 l_sum_ia_id_cont := NULL;
1118 l_last_td_date := null;
1119 vXMLTable(vCtr).TagName := 'CC_WC_PROV';
1120 vXMLTable(vCtr).TagValue := upper(c_rec.wc_prov);
1121 vCtr := vCtr + 1;
1122 vXMLTable(vCtr).TagName := 'CC_COMP_REPRESENTATIVE_NAME';
1123 vXMLTable(vCtr).TagValue := (c_rec.representative_name);
1124 vCtr := vCtr + 1;
1125 vXMLTable(vCtr).TagName := 'CC_ REPRESENTATIVE_DNI';
1126 vXMLTable(vCtr).TagValue := (c_rec.representative_DNI);
1127 vCtr := vCtr + 1;
1128 vXMLTable(vCtr).TagName := 'CC_ REPRESENTATIVE_POS';
1129 vXMLTable(vCtr).TagValue := (c_rec.representative_Position);
1130 vCtr := vCtr + 1;
1131 vXMLTable(vCtr).TagName := 'CC_COMP_NAME';
1132 vXMLTable(vCtr).TagValue := (c_rec.company_name);
1133 vCtr := vCtr + 1;
1134 vXMLTable(vCtr).TagName := 'CC_CAC';
1135 vXMLTable(vCtr).TagValue := (c_rec.CAC);
1136 vCtr := vCtr + 1;
1137 vXMLTable(vCtr).TagName := 'CC-CLOC_TYPE';
1138 vXMLTable(vCtr).TagValue := (c_rec.cloc_type);
1139 vCtr := vCtr + 1;
1140 vXMLTable(vCtr).TagName := 'CC_CLOC_NO';
1141 vXMLTable(vCtr).TagValue := (c_rec.cloc_no);
1142 vCtr := vCtr + 1;
1143 vXMLTable(vCtr).TagName := 'CC_CPOSTAL_CODE';
1144 vXMLTable(vCtr).TagValue := (c_rec.cpostal_code);
1145 vCtr := vCtr + 1;
1146 vXMLTable(vCtr).TagName := 'CC_CCITY';
1147 vXMLTable(vCtr).TagValue := (c_rec.ccity);
1148 vCtr := vCtr + 1;
1149 vXMLTable(vCtr).TagName := 'CC_CPROV_NAME';
1150 vXMLTable(vCtr).TagValue := (c_rec.cprov_name);
1151 vCtr := vCtr + 1;
1152 vXMLTable(vCtr).TagName := 'CC_C_TEL_NO';
1153 vXMLTable(vCtr).TagValue := (c_rec.ctel_no);
1154 vCtr := vCtr + 1;
1155 vXMLTable(vCtr).TagName := 'CC_EMP_NAME';
1156 vXMLTable(vCtr).TagValue := (c_rec.emp_name);
1157 vCtr := vCtr + 1;
1158 vXMLTable(vCtr).TagName := 'CC_EMP_DNI';
1159 vXMLTable(vCtr).TagValue := (c_rec.dni_passport);
1160 vCtr := vCtr + 1;
1161 vXMLTable(vCtr).TagName := 'CC_ELOC_TYPE';
1162 vXMLTable(vCtr).TagValue := (c_rec.eloc_type);
1163 vCtr := vCtr + 1;
1164 vXMLTable(vCtr).TagName := 'CC_ELOC_NAME';
1165 vXMLTable(vCtr).TagValue := (c_rec.eloc_name);
1166 vCtr := vCtr + 1;
1167 vXMLTable(vCtr).TagName := 'CC_ELOC_NO';
1168 vXMLTable(vCtr).TagValue := (c_rec.eloc_no);
1169 vCtr := vCtr + 1;
1170 vXMLTable(vCtr).TagName := 'CC_ECITY';
1171 vXMLTable(vCtr).TagValue := (c_rec.ecity);
1172 vCtr := vCtr + 1;
1173 vXMLTable(vCtr).TagName := 'CC_EPROV_NAME';
1174 vXMLTable(vCtr).TagValue := (c_rec.eprov_name);
1175 vCtr := vCtr + 1;
1176 vXMLTable(vCtr).TagName := 'CC_EPOSTAL_CODE';
1177 vXMLTable(vCtr).TagValue := (c_rec.epostal_code);
1178 vCtr := vCtr + 1;
1179 vXMLTable(vCtr).TagName := 'CC_SSN';
1180 vXMLTable(vCtr).TagValue := (c_rec.social_security_identifier);
1181 vCtr := vCtr + 1;
1182 vXMLTable(vCtr).TagName := 'CC_CONT_GRP';
1183 vXMLTable(vCtr).TagValue := (c_rec.cont_grp);
1184 vCtr := vCtr + 1;
1185 vXMLTable(vCtr).TagName := 'CC_PROF_CATG';
1186 vXMLTable(vCtr).TagValue := (c_rec.prof_catg);
1187 vCtr := vCtr + 1;
1188 vXMLTable(vCtr).TagName := 'CC_OCCUPATION';
1189 vXMLTable(vCtr).TagValue := (c_rec.emp_occupation);
1190 vCtr := vCtr + 1;
1191 vXMLTable(vCtr).TagName := 'CC_START_DATE';
1192 vXMLTable(vCtr).TagValue := (c_rec.start_date);
1193 vCtr := vCtr + 1;
1194 vXMLTable(vCtr).TagName := 'CC_END_DATE';
1195 vXMLTable(vCtr).TagValue := (c_rec.end_date);
1196 vCtr := vCtr + 1;
1197 vXMLTable(vCtr).TagName := 'CC_CONTRACT_END_DATE';
1198 vXMLTable(vCtr).TagValue := (c_rec.contract_end_date);
1199 vCtr := vCtr + 1;
1200 vXMLTable(vCtr).TagName := 'CC_CONTRACT_TYPE';
1201 vXMLTable(vCtr).TagValue := (c_rec.contract_type);
1202 vCtr := vCtr + 1;
1203 vXMLTable(vCtr).TagName := 'CC_LEAVING_REASON';
1204 vXMLTable(vCtr).TagValue := (c_rec.leaving_reason);
1205 vCtr := vCtr + 1;
1206 vXMLTable(vCtr).TagName := 'CC_ACCURED_VACATION';
1207 vXMLTable(vCtr).TagValue := (c_rec.accured_vac);
1208 vCtr := vCtr + 1;
1209 vXMLTable(vCtr).TagName := 'CC_NO_ACC_VAC';
1210 vXMLTable(vCtr).TagValue := (c_rec.no_vac_days);
1211 vCtr := vCtr + 1;
1212 vXMLTable(vCtr).TagName := 'CC_VAC_FROM';
1213 vXMLTable(vCtr).TagValue := (c_rec.vac_from);
1214 vCtr := vCtr + 1;
1215 vXMLTable(vCtr).TagName := 'CC_VAC_TO';
1216 vXMLTable(vCtr).TagValue := (c_rec.vac_till);
1217 vCtr := vCtr + 1;
1218 vXMLTable(vCtr).TagName := 'CC_TD_START_DATE';
1219 vXMLTable(vCtr).TagValue := (c_rec.sick_leave_start_date);
1220 vCtr := vCtr + 1;
1221 vXMLTable(vCtr).TagName := 'CC_NUMBER';
1222 vXMLTable(vCtr).TagValue := (null);
1223 vCtr := vCtr + 1;
1224 vXMLTable(vCtr).TagName := 'CC_DATE';
1225 vXMLTable(vCtr).TagValue := (null);
1226 vCtr := vCtr + 1;
1227 lctr := 1;
1228 FOR c_element_details IN get_element_Details(c_rec.assignment_action_id) LOOP
1229 vXMLTable(vCtr).TagName := 'CC_YEAR'||to_char(lctr);
1230 vXMLTable(vCtr).TagValue := (c_element_details.Year);
1231 vCtr := vCtr + 1;
1232 vXMLTable(vCtr).TagName := 'CC_MONTH'||to_char(lctr);
1233 vXMLTable(vCtr).TagValue := (c_element_details.Month);
1234 vCtr := vCtr + 1;
1235 vXMLTable(vCtr).TagName := 'CC_CONT_DAYS'||to_char(lctr);
1236 vXMLTable(vCtr).TagValue := (fnd_number.canonical_to_number(c_element_details.contribution_days));
1237 vCtr := vCtr + 1;
1238 vXMLTable(vCtr).TagName := 'CC_IA_ID_CONT'||to_char(lctr);
1239 vXMLTable(vCtr).TagValue := (fnd_number.canonical_to_number(c_element_details.ia_id_cont));
1240 vCtr := vCtr + 1;
1241 vXMLTable(vCtr).TagName := 'CC_NOTE'||to_char(lctr);
1242 vXMLTable(vCtr).TagValue := (c_element_details.note);
1243 vCtr := vCtr + 1;
1244 vXMLTable(vCtr).TagName := 'CC_CONT_BASE'||to_char(lctr);
1245 vXMLTable(vCtr).TagValue := (fnd_number.canonical_to_number(c_element_details.contribution_base));
1246 vCtr := vCtr + 1;
1247 lctr := lctr + 1;
1248 END LOOP;
1249 LOOP
1250 EXIT WHEN lctr > 3;
1251 vXMLTable(vCtr).TagName := 'CC_YEAR'||to_char(lctr);
1252 vXMLTable(vCtr).TagValue := ' ';
1253 vCtr := vCtr + 1;
1254 vXMLTable(vCtr).TagName := 'CC_MONTH'||to_char(lctr);
1255 vXMLTable(vCtr).TagValue := ' ';
1256 vCtr := vCtr + 1;
1257 vXMLTable(vCtr).TagName := 'CC_CONT_DAYS'||to_char(lctr);
1258 vXMLTable(vCtr).TagValue := ' ';
1259 vCtr := vCtr + 1;
1260 vXMLTable(vCtr).TagName := 'CC_IA_ID_CONT'||to_char(lctr);
1261 vXMLTable(vCtr).TagValue := ' ';
1262 vCtr := vCtr + 1;
1263 vXMLTable(vCtr).TagName := 'CC_NOTE'||to_char(lctr);
1264 vXMLTable(vCtr).TagValue := ' ';
1265 vCtr := vCtr + 1;
1266 vXMLTable(vCtr).TagName := 'CC_CONT_BASE'||to_char(lctr);
1267 vXMLTable(vCtr).TagValue := ' ';
1268 vCtr := vCtr + 1;
1269 lctr := lctr + 1;
1270 END LOOP;
1271 FOR c_element_details IN get_emp_sickness_Details(c_rec.assignment_action_id) LOOP
1272 IF lctr = 4 THEN
1273 l_sum_cont_days := 0;
1274 l_sum_cont_base := 0;
1275 l_sum_ia_id_cont := 0;
1276 END IF;
1277 vXMLTable(vCtr).TagName := 'CC_YEAR'||to_char(lctr);
1278 vXMLTable(vCtr).TagValue := (c_element_details.Year);
1279 vCtr := vCtr + 1;
1280 vXMLTable(vCtr).TagName := 'CC_MONTH'||to_char(lctr);
1281 vXMLTable(vCtr).TagValue := (c_element_details.Month);
1282 vCtr := vCtr + 1;
1283 vXMLTable(vCtr).TagName := 'CC_CONT_DAYS'||to_char(lctr);
1284 vXMLTable(vCtr).TagValue := (fnd_number.canonical_to_number(c_element_details.contribution_days));
1285 vCtr := vCtr + 1;
1286 vXMLTable(vCtr).TagName := 'CC_IA_ID_CONT'||to_char(lctr);
1287 vXMLTable(vCtr).TagValue := (fnd_number.canonical_to_number(c_element_details.ia_id_cont));
1288 vCtr := vCtr + 1;
1289 vXMLTable(vCtr).TagName := 'CC_NOTE'||to_char(lctr);
1290 vXMLTable(vCtr).TagValue := (c_element_details.note);
1291 vCtr := vCtr + 1;
1292 vXMLTable(vCtr).TagName := 'CC_CONT_BASE'||to_char(lctr);
1293 vXMLTable(vCtr).TagValue := (fnd_number.canonical_to_number(c_element_details.contribution_base));
1294 vCtr := vCtr + 1;
1295 lctr := lctr + 1;
1296 IF c_element_details.last_td_date IS NOT NULL THEN
1297 l_last_td_date := c_element_details.last_td_date;
1298 END IF;
1299 l_sum_cont_days := l_sum_cont_days + nvl(fnd_number.canonical_to_number(c_element_details.contribution_days),0);
1300 l_sum_cont_base := l_sum_cont_base + nvl(fnd_number.canonical_to_number(c_element_details.contribution_base),0);
1301 l_sum_ia_id_cont := l_sum_ia_id_cont + nvl(fnd_number.canonical_to_number(c_element_details.ia_id_cont),0);
1302 END LOOP;
1303
1304 LOOP
1305 EXIT WHEN lctr > 11;
1306 vXMLTable(vCtr).TagName := 'CC_YEAR'||to_char(lctr);
1307 vXMLTable(vCtr).TagValue := ' ';
1308 vCtr := vCtr + 1;
1309 vXMLTable(vCtr).TagName := 'CC_MONTH'||to_char(lctr);
1310 vXMLTable(vCtr).TagValue := ' ';
1311 vCtr := vCtr + 1;
1312 vXMLTable(vCtr).TagName := 'CC_CONT_DAYS'||to_char(lctr);
1313 vXMLTable(vCtr).TagValue := ' ';
1314 vCtr := vCtr + 1;
1315 vXMLTable(vCtr).TagName := 'CC_IA_ID_CONT'||to_char(lctr);
1316 vXMLTable(vCtr).TagValue := ' ';
1317 vCtr := vCtr + 1;
1318 vXMLTable(vCtr).TagName := 'CC_NOTE'||to_char(lctr);
1319 vXMLTable(vCtr).TagValue := ' ';
1320 vCtr := vCtr + 1;
1321 vXMLTable(vCtr).TagName := 'CC_CONT_BASE'||to_char(lctr);
1322 vXMLTable(vCtr).TagValue := ' ';
1323 vCtr := vCtr + 1;
1324 lctr := lctr + 1;
1325 END LOOP;
1326
1327 vXMLTable(vCtr).TagName := 'CC_YEAR'||to_char(lctr);
1328 vXMLTable(vCtr).TagValue := ' ';
1329 vCtr := vCtr + 1;
1330 vXMLTable(vCtr).TagName := 'CC_MONTH'||to_char(lctr);
1331 vXMLTable(vCtr).TagValue := ' ';
1332 vCtr := vCtr + 1;
1333 vXMLTable(vCtr).TagName := 'CC_CONT_DAYS'||to_char(lctr);
1334 vXMLTable(vCtr).TagValue := l_sum_cont_days;
1335 vCtr := vCtr + 1;
1336 vXMLTable(vCtr).TagName := 'CC_IA_ID_CONT'||to_char(lctr);
1337 vXMLTable(vCtr).TagValue := l_sum_ia_id_cont;
1338 vCtr := vCtr + 1;
1339 vXMLTable(vCtr).TagName := 'CC_NOTE'||to_char(lctr);
1340 vXMLTable(vCtr).TagValue := ' ';
1341 vCtr := vCtr + 1;
1342 vXMLTable(vCtr).TagName := 'CC_CONT_BASE'||to_char(lctr);
1343 vXMLTable(vCtr).TagValue := l_sum_cont_base;
1344
1345 vXMLTable(vCtr).TagName := 'CC_LAST_TD_REPORT';
1346 vXMLTable(vCtr).TagValue := nvl(l_last_td_date,' ');
1347 vCtr := vCtr + 1;
1348
1349 END LOOP;
1350 END populate_plsql_table;
1351 --
1352 END per_es_comp_cert_archive_pkg;