[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_ARCHIVE_TETA
Source
1 PACKAGE BODY PAY_SE_ARCHIVE_TETA AS
2 /* $Header: pyseteta.pkb 120.0.12000000.1 2007/07/11 12:30:00 dbehera noship $ */
3
4 TYPE lock_rec IS RECORD (
5 archive_assact_id NUMBER);
6
7 TYPE lock_table IS TABLE OF lock_rec INDEX BY BINARY_INTEGER;
8
9 g_debug boolean := hr_utility.debug_enabled;
10 g_lock_table lock_table;
11 g_index NUMBER := -1;
12 g_index_assact NUMBER := -1;
13 g_index_bal NUMBER := -1;
14 g_package VARCHAR2(33) := ' PAY_SE_ARCHIVE_TETA.';
15 g_archive VARCHAR2(1);
16 g_effective_date DATE;
17 g_date_report DATE;
18 g_person_id NUMBER ;
19 g_business_group_id NUMBER ;
20 g_payroll_action_id NUMBER ;
21
22
23
24 /* GET PARAMETER */
25 FUNCTION GET_PARAMETER(
26 p_parameter_string IN VARCHAR2
27 ,p_token IN VARCHAR2
28 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
29 IS
30 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
31 l_start_pos NUMBER;
32 l_delimiter VARCHAR2(1):=' ';
33 l_proc VARCHAR2(240):= g_package||' get parameter ';
34 BEGIN
35 --
36 --fnd_file.put_line(fnd_file.log,'Range Code 23'||p_token);
37 IF g_debug THEN
38 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
39 END IF;
40 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
41 --
42 IF l_start_pos = 0 THEN
43 l_delimiter := '|';
44 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
45 END IF;
46
47 IF l_start_pos <> 0 THEN
48 l_start_pos := l_start_pos + length(p_token||'=');
49 l_parameter := substr(p_parameter_string,
50 l_start_pos,
51 instr(p_parameter_string||' ',
52 l_delimiter,l_start_pos)
53 - l_start_pos);
54 IF p_segment_number IS NOT NULL THEN
55 l_parameter := ':'||l_parameter||':';
56 l_parameter := substr(l_parameter,
57 instr(l_parameter,':',1,p_segment_number)+1,
58 instr(l_parameter,':',1,p_segment_number+1) -1
59 - instr(l_parameter,':',1,p_segment_number));
60 END IF;
61 END IF;
62 --
63 IF g_debug THEN
64 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
65 END IF;
66
67 --fnd_file.put_line(fnd_file.log,'Range Code 24');
68 RETURN l_parameter;
69
70 END;
71
72 /* GET ALL PARAMETERS */
73 PROCEDURE GET_ALL_PARAMETERS
74 (
75 p_payroll_action_id IN NUMBER
76 ,p_business_group_id OUT NOCOPY NUMBER
77 ,p_person_id OUT NOCOPY NUMBER
78 ,p_date_report OUT NOCOPY DATE
79 ,p_effective_date OUT NOCOPY DATE
80 ,p_archive OUT NOCOPY VARCHAR2
81 ) IS
82
83 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
84 SELECT PAY_SE_ARCHIVE_TETA.GET_PARAMETER(legislative_parameters,'PERSON_ID')
85 ,fnd_date.canonical_to_date(PAY_SE_ARCHIVE_TETA.GET_PARAMETER(legislative_parameters,'DATE_REPORT'))
86 ,PAY_SE_ARCHIVE_TETA.GET_PARAMETER(legislative_parameters,'ARCHIVE')
87 ,effective_date
88 ,business_group_id
89 FROM pay_payroll_actions
90 WHERE payroll_action_id = p_payroll_action_id;
91 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
92 --
93 BEGIN
94
95 --fnd_file.put_line(fnd_file.log,'Range Code 21'||TO_CHAR(p_payroll_action_id));
96
97 OPEN csr_parameter_info (p_payroll_action_id);
98
99 FETCH csr_parameter_info
100 INTO p_person_id
101 ,p_date_report
102 ,p_archive
103 ,p_effective_date
104 ,p_business_group_id;
105 CLOSE csr_parameter_info;
106
107 --fnd_file.put_line(fnd_file.log,'Range Code 22');
108 --
109 IF g_debug THEN
110 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
111 END IF;
112 --fnd_file.put_line(fnd_file.log,'Range Code 22222');
113 END GET_ALL_PARAMETERS;
114
115 /* RANGE CODE */
116 PROCEDURE RANGE_CODE (p_payroll_action_id IN NUMBER
117 ,p_sql OUT NOCOPY VARCHAR2)
118 IS
119
120
121 l_action_info_id NUMBER;
122 l_ovn NUMBER;
123 l_count NUMBER;
124
125 /* Cursors */
126
127 CURSOR csr_person_details
128 ( csr_v_person_id NUMBER
129 , csr_v_effective_date DATE )
130 IS
131 SELECT *
132 FROM per_all_people_f ppf
133 WHERE ppf.person_id = csr_v_person_id
134 AND csr_v_effective_date BETWEEN ppf.effective_start_date
135 AND ppf.effective_end_date;
136
137
138 /* End of Cursors */
139
140 BEGIN
141
142 IF g_debug THEN
143 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
144 END IF;
145
146 p_sql := 'SELECT DISTINCT person_id
147 FROM per_people_f ppf
148 ,pay_payroll_actions ppa
149 WHERE ppa.payroll_action_id = :payroll_action_id
150 AND ppa.business_group_id = ppf.business_group_id
151 AND ROWNUM < 2
152 ORDER BY ppf.person_id';
153
154 g_archive := NULL;
155 g_date_report := NULL ;
156 g_effective_date := NULL ;
157 g_person_id := NULL ;
158 g_payroll_action_id := p_payroll_action_id ;
159
160 --fnd_file.put_line(fnd_file.log,'InsideRange Code');
161
162 PAY_SE_ARCHIVE_TETA.GET_ALL_PARAMETERS(
163 p_payroll_action_id
164 ,g_business_group_id
165 ,g_person_id
166 ,g_date_report
167 ,g_effective_date
168 ,g_archive ) ;
169
170 IF g_archive = 'Y' THEN
171
172 --fnd_file.put_line(fnd_file.log,'Inside Range Code Archive');
173 SELECT count(*)
174 INTO l_count
175 FROM pay_action_information
176 WHERE action_information_category = 'EMEA REPORT DETAILS'
177 AND action_information1 = 'PYSETETA'
178 AND action_context_id = p_payroll_action_id;
179
180 IF l_count < 1 then
181
182 --fnd_file.put_line(fnd_file.log,'Range Code Count');
183 FOR person_rec IN csr_person_details( g_person_id , g_date_report )
184 LOOP
185
186 --fnd_file.put_line(fnd_file.log,'Range Code LOOP');
187 pay_action_information_api.create_action_information (
188 p_action_information_id => l_action_info_id
189 ,p_action_context_id => p_payroll_action_id
190 ,p_action_context_type => 'PA'
191 ,p_object_version_number => l_ovn
192 ,p_effective_date => g_effective_date
193 ,p_source_id => NULL
194 ,p_source_text => NULL
195 ,p_action_information_category => 'EMEA REPORT DETAILS'
196 ,p_action_information1 => 'PYSETETA'
197 ,p_action_information2 => g_person_id
198 ,p_action_information3 => person_rec.full_name
199 ,p_action_information4 => person_rec.national_identifier
200 ,p_action_information5 => person_rec.employee_number
201 ,p_action_information6 => g_date_report
202 ,p_action_information7 => null
203 ,p_action_information8 => null
204 ,p_action_information9 => null
205 ,p_action_information10 => null
206 ,p_action_information11 => null
207 ,p_action_information12 => null
208 ,p_action_information13 => null
209 ,p_action_information14 => null
210 ,p_action_information15 => null
211 ,p_action_information16 => null
212 ,p_action_information17 => null
213 ,p_action_information18 => null
214 ,p_action_information19 => null
215 ,p_action_information20 => null
216 ,p_action_information21 => null
217 ,p_action_information22 => null
218 ,p_action_information23 => null
219 ,p_action_information24 => null
220 ,p_action_information25 => null
221 ,p_action_information26 => null
222 ,p_action_information27 => null
223 ,p_action_information28 => null
224 ,p_action_information29 => null
225 ,p_action_information30 => null );
226
227 END LOOP;
228
229 END IF;
230
231 END IF;
232
233 IF g_debug THEN
234 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
235 END IF;
236 EXCEPTION
237 WHEN others THEN
238 IF g_debug THEN
239 hr_utility.set_location('error raised assignment_action_code ',5);
240 END if;
241 RAISE;
242 END RANGE_CODE;
243
244 /* ASSIGNMENT ACTION CODE */
245 PROCEDURE ASSIGNMENT_ACTION_CODE
246 (p_payroll_action_id IN NUMBER
247 ,p_start_person IN NUMBER
248 ,p_end_person IN NUMBER
249 ,p_chunk IN NUMBER)
250 IS
251
252 CURSOR csr_person_assignments
253 ( csr_v_person_id NUMBER
254 ,csr_v_business_group_id NUMBER
255 , csr_v_effective_date DATE )
256 IS
257 SELECT assign.assignment_id
258 FROM
259 per_all_assignments_f assign
260 ,hr_soft_coding_keyflex scl
261 ,hr_organization_units o1
262 ,hr_organization_information hoi1
263 ,hr_organization_information hoi2
264 ,hr_organization_information hoi3
265 WHERE assign.person_id= csr_v_person_id
266 AND assign.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
267 AND o1.business_group_id = csr_v_business_group_id
268 AND hoi1.organization_id = o1.organization_id
269 AND hoi1.organization_id = TO_CHAR(scl.segment2)
270 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
271 AND hoi1.org_information_context = 'CLASS'
272 AND o1.organization_id = hoi2.org_information1
273 AND hoi2.org_information_context='SE_LOCAL_UNITS'
274 AND hoi2.organization_id = hoi3.organization_id
275 AND hoi3.org_information_context='CLASS'
276 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
277 AND primary_flag ='Y'
278 AND assign.effective_start_date < =csr_v_effective_date
279 GROUP BY assign.assignment_id;
280
281 l_prepay_action_id NUMBER;
282 l_actid NUMBER;
283 l_assignment_id NUMBER;
284 l_action_sequence NUMBER;
285 l_assact_id NUMBER;
286 l_pact_id NUMBER;
287 l_flag NUMBER := 0;
288 l_action_info_id NUMBER;
289 l_ovn NUMBER;
290 BEGIN
291 IF g_debug THEN
292 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
293 END IF;
294
295 --fnd_file.put_line(fnd_file.log,'Assignment Action Code');
296
297 PAY_SE_ARCHIVE_TETA.GET_ALL_PARAMETERS(
298 p_payroll_action_id
299 ,g_business_group_id
300 ,g_person_id
301 ,g_date_report
302 ,g_effective_date
303 ,g_archive ) ;
304
305 g_payroll_action_id :=p_payroll_action_id;
306
307 FOR person_assignments_rec IN csr_person_assignments( g_person_id, g_business_group_id, g_date_report)
308 LOOP
309
310 --fnd_file.put_line(fnd_file.log,'Assignment Action Code Loop');
311
312 SELECT pay_assignment_actions_s.NEXTVAL
313 INTO l_actid
314 FROM dual;
315 --
316 g_index_assact := g_index_assact + 1;
317 g_lock_table(g_index_assact).archive_assact_id := l_actid; /* For Element archival */
318 -- Create the archive assignment action
319 hr_nonrun_asact.insact(l_actid
320 ,person_assignments_rec.assignment_id
321 ,p_payroll_action_id
322 ,p_chunk
323 ,NULL);
324 -- Create archive to prepayment assignment action interlock
325 --
326 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
327
328 END LOOP;
329
330 IF g_debug THEN
331 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
332 END IF;
333 EXCEPTION
334 WHEN others THEN
335 IF g_debug THEN
336 hr_utility.set_location('error raised assignment_action_code ',5);
337 END if;
338 RAISE;
339
340 END ASSIGNMENT_ACTION_CODE;
341 /* INITIALIZATION CODE */
342 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
343 IS
344
345 BEGIN
346 IF g_debug THEN
347 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
348 END IF;
349
350 IF g_debug THEN
351 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
352 END IF;
353
354 EXCEPTION
355 WHEN others THEN
356 IF g_debug THEN
357 hr_utility.set_location('error raised initialization code ',5);
358 END if;
359 RAISE;
360 END INITIALIZATION_CODE;
361
362 /* ARCHIVE CODE */
363 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
364 ,p_effective_date IN DATE)
365 IS
366
367
368 /* Cursor to retrieve Person Current Employment Time Details */
369 CURSOR csr_person_teta
370 ( csr_v_assign_id NUMBER
371 ,csr_v_person_id NUMBER
372 ,csr_v_business_group_id NUMBER
373 , csr_v_effective_date DATE )
374 IS
375 SELECT assign.assignment_id
376 ,hoi3.organization_id, assign.employment_category
377 , trunc(assign.effective_start_date) effective_start_date
378 ,trunc(decode(assign.effective_end_date,to_date('31/12/4712','dd/mm/yyyy'), g_date_report ,assign.effective_end_date) ) effective_end_date
379 FROM
380 per_all_assignments_f assign
381 ,hr_soft_coding_keyflex scl
382 ,hr_organization_units o1
383 ,hr_organization_information hoi1
384 ,hr_organization_information hoi2
385 ,hr_organization_information hoi3
386 WHERE assign.assignment_id = csr_v_assign_id
387 AND assign.person_id = csr_v_person_id
388 AND assign.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
389 AND o1.business_group_id = csr_v_business_group_id
390 AND hoi1.organization_id = o1.organization_id
391 AND hoi1.organization_id = TO_CHAR(scl.segment2)
392 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
393 AND hoi1.org_information_context = 'CLASS'
394 AND o1.organization_id = hoi2.org_information1
395 AND hoi2.org_information_context='SE_LOCAL_UNITS'
396 AND hoi2.organization_id = hoi3.organization_id
397 AND hoi3.org_information_context='CLASS'
398 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
399 AND primary_flag ='Y'
400 AND assign.effective_start_date < =csr_v_effective_date
401 ORDER BY assign.effective_start_date DESC;
402
403
404 /* Cursor to retrieve Person Current Employment Time Summary Details */
405 CURSOR csr_person_sum_teta
406 ( csr_v_assign_id NUMBER
407 , csr_v_person_id NUMBER
408 , csr_v_business_group_id NUMBER
409 , csr_v_effective_date DATE )
410 IS
411 SELECT assign.assignment_id
412 ,hoi3.organization_id, assign.employment_category
413 ,sum(decode(assign.effective_end_date,to_date('31/12/4712','dd/mm/yyyy'), g_date_report ,assign.effective_end_date) - assign.effective_start_date ) days_worked
414 FROM
415 per_all_assignments_f assign
416 ,hr_soft_coding_keyflex scl
417 ,hr_organization_units o1
418 ,hr_organization_information hoi1
419 ,hr_organization_information hoi2
420 ,hr_organization_information hoi3
421 WHERE assign.assignment_id = csr_v_assign_id
422 AND assign.person_id = csr_v_person_id
423 AND assign.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
424 AND o1.business_group_id = csr_v_business_group_id
425 AND hoi1.organization_id = o1.organization_id
426 AND hoi1.organization_id = TO_CHAR(scl.segment2)
427 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
428 AND hoi1.org_information_context = 'CLASS'
429 AND o1.organization_id = hoi2.org_information1
430 AND hoi2.org_information_context='SE_LOCAL_UNITS'
431 AND hoi2.organization_id = hoi3.organization_id
432 AND hoi3.org_information_context='CLASS'
433 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
434 AND primary_flag ='Y'
435 AND assign.effective_start_date < =csr_v_effective_date
436 GROUP BY assign.assignment_id ,hoi3.organization_id, assign.employment_category;
437
438
439
440
441 /*Cursor for Employee Typeto Legal Employer*/
442 CURSOR csr_emp_name
443 (
444 csr_v_employer_id NUMBER
445 )
446 IS
447 SELECT hou.NAME
448 FROM hr_organization_units hou
449 WHERE hou.organization_id=csr_v_employer_id;
450
451 rec_emp_name csr_emp_name%ROWTYPE;
452
453
454 /* Cursor to retrieve Assignment Category */
455 CURSOR csr_emp_catg
456 ( csr_v_lookup_code VARCHAR2
457 , csr_v_effective_date DATE )
458 IS
459 SELECT l.meaning employment_category
460 FROM hr_leg_lookups l
461 WHERE l.lookup_type = 'EMP_CAT'
462 AND l.enabled_flag = 'Y'
463 AND l.lookup_code = csr_v_lookup_code
464 AND csr_v_effective_date
465 BETWEEN nvl(start_date_active,csr_v_effective_date)
466 AND nvl(end_date_active,csr_v_effective_date) ;
467
468 rec_emp_catg csr_emp_catg%ROWTYPE;
469
470 /*Cursor for fetching assignment_id*/
471 CURSOR csr_assign_id
472 IS
473 SELECT paa.assignment_id
474 FROM pay_assignment_actions paa
475 WHERE paa.assignment_action_id = p_assignment_action_id;
476
477 l_action_context_id NUMBER;
478 l_flag NUMBER := 0;
479 l_action_info_id NUMBER;
480 l_ovn NUMBER;
481 l_assign_id NUMBER;
482
483
484 BEGIN
485 IF g_debug THEN
486 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
487 END IF;
488
489 --fnd_file.put_line(fnd_file.log,'Archive Code');
490 IF g_archive='Y' THEN
491
492
493
494 BEGIN
495 SELECT 1
496 INTO l_flag
497 FROM pay_action_information
498 WHERE action_information_category = 'EMEA REPORT INFORMATION'
499 AND action_information1 = 'PYSETETA'
500 AND action_information2 = 'PER'
501 AND action_context_id = p_assignment_action_id;
502 EXCEPTION
503 WHEN NO_DATA_FOUND THEN
504 OPEN csr_assign_id;
505 FETCH csr_assign_id INTO l_assign_id;
506 CLOSE csr_assign_id;
507
508 --fnd_file.put_line(fnd_file.log,'Before Loop Archive Code');
509
510 FOR person_teta_rec IN csr_person_teta( l_assign_id,g_person_id, g_business_group_id, g_date_report)
511 LOOP
512
513
514 --fnd_file.put_line(fnd_file.log,'Inside Loop Archive Code');
515
516 OPEN csr_emp_catg( person_teta_rec.employment_category, g_date_report);
517 FETCH csr_emp_catg INTO rec_emp_catg;
518 CLOSE csr_emp_catg;
519
520 OPEN csr_emp_name(person_teta_rec.organization_id) ;
521 FETCH csr_emp_name INTO rec_emp_name;
522 CLOSE csr_emp_name;
523
524
525 pay_action_information_api.create_action_information (
526 p_action_information_id=> l_action_info_id,
527 p_action_context_id=> p_assignment_action_id,
528 p_action_context_type=> 'AAP',
529 p_object_version_number=> l_ovn,
530 p_effective_date=> g_effective_date,
531 p_source_id=> NULL,
532 p_source_text=> NULL,
533 p_action_information_category=> 'EMEA REPORT INFORMATION',
534 p_action_information1=> 'PYSETETA',
535 p_action_information2=> g_payroll_action_id,
536 p_action_information3=> g_person_id ,
537 p_action_information4=> person_teta_rec.organization_id,
538 p_action_information5=> rec_emp_name.name,
539 p_action_information6=> person_teta_rec.employment_category ,
540 p_action_information7=> rec_emp_catg.employment_category ,
541 p_action_information8=> person_teta_rec.effective_start_date,
542 p_action_information9=> person_teta_rec.effective_end_date,
543 p_action_information10=> person_teta_rec.effective_end_date - person_teta_rec.effective_start_date ,
544 p_action_information11=> 'CH',
545 p_action_information12=> NULL ,
546 p_action_information13=> NULL,
547 p_action_information14=> NULL,
548 p_action_information15=> NULL,
549 p_action_information16=> NULL,
550 p_action_information17=> NULL,
551 p_action_information18=> NULL,
552 p_action_information19=> NULL,
553 p_action_information20=> NULL,
554 p_action_information21=> NULL,
555 p_action_information22=> NULL,
556 p_action_information23=> NULL,
557 p_action_information24=> NULL,
558 p_action_information25=> NULL,
559 p_action_information26=> NULL,
560 p_action_information27=> NULL,
561 p_action_information28=> NULL,
562 p_action_information29=> NULL,
563 p_action_information30=> l_assign_id
564 );
565
566 END LOOP;
567
568 FOR person_sum_teta_rec IN csr_person_sum_teta( l_assign_id,g_person_id, g_business_group_id, g_date_report)
569 LOOP
570
571
572 --fnd_file.put_line(fnd_file.log,'Inside Loop Archive Code');
573
574 OPEN csr_emp_catg( person_sum_teta_rec.employment_category, g_date_report);
575 FETCH csr_emp_catg INTO rec_emp_catg;
576 CLOSE csr_emp_catg;
577
578 OPEN csr_emp_name(person_sum_teta_rec.organization_id) ;
579 FETCH csr_emp_name INTO rec_emp_name;
580 CLOSE csr_emp_name;
581
582
583 pay_action_information_api.create_action_information (
584 p_action_information_id=> l_action_info_id,
585 p_action_context_id=> p_assignment_action_id,
586 p_action_context_type=> 'AAP',
587 p_object_version_number=> l_ovn,
588 p_effective_date=> g_effective_date,
589 p_source_id=> NULL,
590 p_source_text=> NULL,
591 p_action_information_category=> 'EMEA REPORT INFORMATION',
592 p_action_information1=> 'PYSETETA',
593 p_action_information2=> g_payroll_action_id,
594 p_action_information3=> g_person_id ,
595 p_action_information4=> person_sum_teta_rec.organization_id,
596 p_action_information5=> rec_emp_name.name,
597 p_action_information6=> person_sum_teta_rec.employment_category ,
598 p_action_information7=> rec_emp_catg.employment_category ,
599 p_action_information8=> person_sum_teta_rec.days_worked ,
600 p_action_information9=> NULL,
601 p_action_information10=> NULL,
602 p_action_information11=> 'CS',
603 p_action_information12=> NULL ,
604 p_action_information13=> NULL,
605 p_action_information14=> NULL,
606 p_action_information15=> NULL,
607 p_action_information16=> NULL,
608 p_action_information17=> NULL,
609 p_action_information18=> NULL,
610 p_action_information19=> NULL,
611 p_action_information20=> NULL,
612 p_action_information21=> NULL,
613 p_action_information22=> NULL,
614 p_action_information23=> NULL,
615 p_action_information24=> NULL,
616 p_action_information25=> NULL,
617 p_action_information26=> NULL,
618 p_action_information27=> NULL,
619 p_action_information28=> NULL,
620 p_action_information29=> NULL,
621 p_action_information30=> l_assign_id
622 );
623
624 END LOOP;
625
626 WHEN OTHERS THEN
627 NULL;
628 END;
629 END IF;---ARCHIVE=YES
630
631 IF g_debug THEN
632 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
633 END IF;
634
635 EXCEPTION
636 WHEN others THEN
637 IF g_debug THEN
638 hr_utility.set_location('error raised in archive code ',5);
639 END if;
640 RAISE;
641 END ARCHIVE_CODE;
642
643
644 PROCEDURE DEINITIALIZATION_CODE
645 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
646
647
648 l_years_worked NUMBER;
649 l_days_worked NUMBER;
650 l_action_info_id NUMBER;
651 l_ovn NUMBER;
652
653 /* Cursor to retrieve Person Previous Employment Time Details */
654 CURSOR csr_person_prev_teta
655 ( csr_v_person_id NUMBER
656 ,csr_v_business_group_id NUMBER
657 , csr_v_effective_date DATE )
658 IS
659 SELECT *
660 FROM per_previous_employers_v ppev
661 WHERE ppev.person_id= csr_v_person_id
662 AND ppev.business_group_id = csr_v_business_group_id
663 AND ppev.start_date <= csr_v_effective_date
664 ORDER BY ppev.start_date DESC;
665
666
667 /* Cursor to retrieve summary information about the Employee) */
668 CURSOR csr_sum_time IS
669 SELECT sum(action_information10) total_days_worked
670 FROM pay_action_information
671 WHERE action_information_category = 'EMEA REPORT INFORMATION'
672 AND action_information1 = 'PYSETETA'
673 AND action_information2 = to_char(p_payroll_action_id)
674 AND action_information11 IN ('PH','CH');
675
676 BEGIN
677 IF g_debug THEN
678 hr_utility.set_location(' Entering Procedure DEINITIALIZATION_CODE',380);
679 END IF;
680
681 PAY_SE_ARCHIVE_TETA.GET_ALL_PARAMETERS(
682 p_payroll_action_id
683 ,g_business_group_id
684 ,g_person_id
685 ,g_date_report
686 ,g_effective_date
687 ,g_archive ) ;
688
689 g_payroll_action_id :=p_payroll_action_id;
690
691 --fnd_file.put_line(fnd_file.log,'Inside Deinitialization Code');
692
693 FOR person_prev_teta_rec IN csr_person_prev_teta( g_person_id, g_business_group_id, g_date_report)
694 LOOP
695
696
697 --fnd_file.put_line(fnd_file.log,'Inside Loop Archive Code');
698
699 pay_action_information_api.create_action_information (
700 p_action_information_id=> l_action_info_id,
701 p_action_context_id=> p_payroll_action_id,
702 p_action_context_type=> 'PA',
703 p_object_version_number=> l_ovn,
704 p_effective_date=> g_effective_date,
705 p_source_id=> NULL,
706 p_source_text=> NULL,
707 p_action_information_category=> 'EMEA REPORT INFORMATION',
708 p_action_information1=> 'PYSETETA',
709 p_action_information2=> g_payroll_action_id,
710 p_action_information3=> g_person_id ,
711 p_action_information4=> person_prev_teta_rec.previous_employer_id ,
712 p_action_information5=> person_prev_teta_rec.employer_name ,
713 p_action_information6=> NULL,
714 p_action_information7=> NULL,
715 p_action_information8=> person_prev_teta_rec.start_date,
716 p_action_information9=> person_prev_teta_rec.end_date ,
717 p_action_information10=> person_prev_teta_rec.end_date - person_prev_teta_rec.start_date ,
718 p_action_information11=> 'PH',
719 p_action_information12=> NULL ,
720 p_action_information13=> NULL,
721 p_action_information14=> NULL,
722 p_action_information15=> NULL,
723 p_action_information16=> NULL,
724 p_action_information17=> NULL,
725 p_action_information18=> NULL,
726 p_action_information19=> NULL,
727 p_action_information20=> NULL,
728 p_action_information21=> NULL,
729 p_action_information22=> NULL,
730 p_action_information23=> NULL,
731 p_action_information24=> NULL,
732 p_action_information25=> NULL,
733 p_action_information26=> NULL,
734 p_action_information27=> NULL,
735 p_action_information28=> NULL,
736 p_action_information29=> NULL,
737 p_action_information30=> NULL
738 );
739
740 END LOOP;
741
742
743 FOR sum_time_rec IN csr_sum_time
744 LOOP
745
746 fnd_file.put_line(fnd_file.log,'Inside Loop Deinitialization Code');
747 IF sum_time_rec.total_days_worked >= 365 THEN
748
749 l_years_worked := trunc(sum_time_rec.total_days_worked/365);
750 l_days_worked := mod(sum_time_rec.total_days_worked,365);
751
752 ELSE
753 l_years_worked := 0;
754 l_days_worked := sum_time_rec.total_days_worked;
755 END IF;
756
757 pay_action_information_api.create_action_information (
758 p_action_information_id=> l_action_info_id,
759 p_action_context_id=> p_payroll_action_id,
760 p_action_context_type=> 'PA',
761 p_object_version_number=> l_ovn,
762 p_effective_date=> g_effective_date,
763 p_source_id=> NULL,
764 p_source_text=> NULL,
765 p_action_information_category => 'EMEA REPORT INFORMATION',
766 p_action_information1=> 'PYSETETA',
767 p_action_information2=> 'S',
768 p_action_information3=> l_years_worked,
769 p_action_information4=> l_days_worked ,
770 p_action_information5=> NULL,
771 p_action_information6=> NULL,
772 p_action_information7=> NULL,
773 p_action_information8=> NULL,
774 p_action_information9=> NULL,
775 p_action_information10=> NULL,
776 p_action_information11=> NULL,
777 p_action_information12=> NULL,
778 p_action_information13=> NULL,
779 p_action_information14=> NULL,
780 p_action_information15=> NULL,
781 p_action_information16=> NULL,
782 p_action_information17=> NULL,
783 p_action_information18=> NULL,
784 p_action_information19=> NULL,
785 p_action_information20=> NULL,
786 p_action_information21=> NULL,
787 p_action_information22=> NULL,
788 p_action_information23=> NULL,
789 p_action_information24=> NULL,
790 p_action_information25=> NULL,
791 p_action_information26=> NULL,
792 p_action_information27=> NULL,
793 p_action_information28=> NULL,
794 p_action_information29=> NULL,
795 p_action_information30=> NULL );
796
797 END LOOP;
798
799 IF g_debug THEN
800 hr_utility.set_location(' Leaving Procedure DEINITIALIZATION_CODE',390);
801 END IF;
802
803 EXCEPTION
804 WHEN others THEN
805 IF g_debug THEN
806 hr_utility.set_location('error raised in DEINITIALIZATION_CODE ',5);
807 END if;
808 RAISE;
809 END;
810
811 BEGIN
812
813 g_archive := NULL;
814 g_date_report := NULL ;
815 g_effective_date := NULL ;
816 g_person_id := NULL ;
817 g_payroll_action_id := NULL ;
818
819 END PAY_SE_ARCHIVE_TETA;