[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_PERSON
Source
1 Package Body ben_ext_person as
2 /* $Header: benxpers.pkb 120.38.12010000.3 2008/08/28 04:02:40 vkodedal ship $ */
3 -- ----------------------------------------------------------------------------
4 -- | Private Global Definitions |
5 -- ----------------------------------------------------------------------------
6 --
7 --
8 g_package varchar2(33) := ' ben_ext_person.'; -- Global package name
9 g_debug boolean := hr_utility.debug_enabled;
10
11 TYPE t_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
12 TYPE t_varchar2_30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
13 TYPE t_varchar2_600 IS TABLE OF VARCHAR2(600) INDEX BY BINARY_INTEGER;
14 TYPE t_date IS TABLE OF date INDEX BY BINARY_INTEGER;
15
16
17
18 Procedure get_pay_adv_crit_dates(p_ext_crit_prfl_id in number default null,
19 p_ext_dfn_id in number,
20 p_business_group_id in number,
21 p_effective_date in date,
22 p_eff_from_dt out nocopy date,
23 p_eff_to_dt out nocopy date,
24 p_act_from_dt out nocopy date,
25 p_act_to_dt out nocopy date,
26 p_date_mode out nocopy varchar2
27 ) is
28 --
29 l_proc varchar2(72);
30 l_eff_from_dt date;
31 l_eff_to_dt date;
32 l_act_from_dt date;
33 l_act_to_dt date;
34
35 cursor c1 is
36 select ecc.crit_typ_cd,
37 ecc.oper_cd,
38 ecc.val_1,
39 ecc.val_2
40 from ben_ext_crit_typ ect,
41 ben_ext_crit_val ecv,
42 ben_ext_crit_cmbn ecc
43 where ect.crit_typ_cd = 'ADV'
44 and ect.ext_crit_typ_id = ecv.ext_crit_typ_id
45 and ect.ext_crit_prfl_id = p_ext_crit_prfl_id
46 and ecv.ext_crit_val_id = ecc.ext_crit_val_id
47 and ecc.crit_typ_cd in ('CAD','CED')
48 order by 1
49 ;
50
51 l_cad_exist varchar2(1) ;
52 l_ced_exist varchar2(1) ;
53 l_from_date date ;
54 l_to_date date ;
55 l_date_mode varchar2(1) ;
56
57
58 --
59 Begin
60 if g_debug then
61 l_proc := g_package||'get_pay_adv_crit_dates';
62 hr_utility.set_location('Entering'||l_proc, 5);
63 end if;
64
65 l_cad_exist := 'N' ;
66 l_ced_exist := 'N' ;
67
68 for i in c1
69 Loop
70
71 hr_utility.set_location('oper cd '||i.oper_cd, 5);
72 hr_utility.set_location('crit_typ_cd cd '||i.crit_typ_cd, 5);
73
74 l_from_date := ben_ext_util.calc_ext_date
75 (p_ext_date_cd => i.val_1,
76 p_abs_date => p_effective_date,
77 p_ext_dfn_id => p_ext_dfn_id);
78 if i.oper_cd = 'EQ' then
79 l_to_date := l_from_date ;
80 else
81
82 l_to_date := ben_ext_util.calc_ext_date
83 (p_ext_date_cd => i.val_2,
84 p_abs_date => p_effective_date,
85 p_ext_dfn_id => p_ext_dfn_id);
86
87 End if ;
88
89 if i.crit_typ_cd = 'CAD' then
90 l_cad_exist := 'Y' ;
91 -- calc the cad from and to date
92 -- get the lowest from date and highetst to date excluding eof and bof
93 if l_from_date is not null and l_from_date <> hr_api.g_sot then
94 if nvl(l_act_from_dt,hr_api.g_eot) > l_from_date then
95 l_act_from_dt := l_from_date ;
96 end if ;
97
98 end if ;
99
100
101 if l_to_date is not null and l_to_date <> hr_api.g_eot then
102 if nvl(l_act_to_dt,hr_api.g_sot) < l_to_date then
103 l_act_to_dt := l_to_date ;
104 end if ;
105 end if ;
106
107
108
109
110 else
111 l_ced_exist := 'Y' ;
112 -- calc the cad from and to date
113 -- get the lowest from date and highetst to date excluding eof and bof
114 if l_from_date is not null and l_from_date <> hr_api.g_sot then
115 if nvl(l_eff_from_dt,hr_api.g_eot) > l_from_date then
116 l_eff_from_dt := l_from_date ;
117 end if ;
118
119 end if ;
120
121
122 if l_to_date is not null and l_to_date <> hr_api.g_eot then
123 if nvl(l_eff_to_dt,hr_api.g_sot) < l_to_date then
124 l_eff_to_dt := l_to_date ;
125 end if ;
126 end if ;
127
128 End if;
129
130 End Loop ;
131
132 --- if there is not date fix them as bot and eot
133 --- if the dates are bot and eot return there is not point in
134 --- executing the interpreter twice when one more is bot and eot
135 if l_cad_exist = 'Y' then
136 if l_act_from_dt is null then
137 l_act_from_dt := hr_api.g_sot ;
138 end if ;
139
140 if l_act_to_dt is null then
141 l_act_to_dt := hr_api.g_eot ;
142 end if ;
143
144 if l_act_from_dt = hr_api.g_sot and l_act_to_dt = hr_api.g_eot then
145 p_act_from_dt := l_act_from_dt;
146 p_act_to_dt := l_act_to_dt ;
147 p_date_mode := 'C' ;
148
149 hr_utility.set_location('eff_from_dt '|| p_eff_from_dt , 15);
150 hr_utility.set_location('eff_to_dt '|| p_eff_to_dt , 15);
151 hr_utility.set_location('Exiting for C eot bot '||l_proc, 15);
152
153 Return ;
154 end if ;
155
156 -- when no effective date exit
157 if l_ced_exist = 'N' then
158
159 p_act_from_dt := l_act_from_dt;
160 p_act_to_dt := l_act_to_dt ;
161 p_date_mode := 'C' ;
162
163 hr_utility.set_location('eff_from_dt '|| p_eff_from_dt , 15);
164 hr_utility.set_location('eff_to_dt '|| p_eff_to_dt , 15);
165 hr_utility.set_location('Exiting for C no ced '||l_proc, 15);
166
167 Return ;
168
169 end if ;
170 end if ;
171
172 if l_ced_exist = 'Y' then
173
174 if l_eff_from_dt is null then
175 l_eff_from_dt := hr_api.g_sot ;
176 end if ;
177
178 if l_eff_to_dt is null then
179 l_eff_to_dt := hr_api.g_eot ;
180 end if ;
181
182 if l_eff_from_dt = hr_api.g_sot and l_eff_to_dt = hr_api.g_eot then
183 p_eff_from_dt := l_eff_from_dt;
184 p_eff_to_dt := l_eff_to_dt ;
185 p_date_mode := 'E' ;
186
187 hr_utility.set_location('eff_from_dt '|| p_eff_from_dt , 15);
188 hr_utility.set_location('eff_to_dt '|| p_eff_to_dt , 15);
189 hr_utility.set_location('Exiting for E eot bot '||l_proc, 15);
190
191 Return ;
192 end if ;
193
194 -- when no actual date exit
195 if l_cad_exist = 'N' then
196
197 p_eff_from_dt := l_eff_from_dt;
198 p_eff_to_dt := l_eff_to_dt ;
199 p_date_mode := 'E' ;
200
201 hr_utility.set_location('eff_from_dt '|| p_eff_from_dt , 15);
202 hr_utility.set_location('eff_to_dt '|| p_eff_to_dt , 15);
203 hr_utility.set_location('Exiting for no cad '||l_proc, 15);
204
205 Return ;
206
207 end if ;
208
209 end if ;
210
211
212 if l_cad_exist = 'Y' and l_ced_exist = 'Y' then
213
214 p_act_from_dt := l_act_from_dt;
215 p_act_to_dt := l_act_to_dt ;
216 p_eff_from_dt := l_eff_from_dt;
217 p_eff_to_dt := l_eff_to_dt ;
218 p_date_mode := 'B' ;
219
220 End if ;
221
222
223 hr_utility.set_location('act_from_dt '|| p_act_from_dt , 15);
224 hr_utility.set_location('act_to_dt '|| p_act_to_dt , 15);
225 hr_utility.set_location('eff_from_dt '|| p_eff_from_dt , 15);
226 hr_utility.set_location('eff_to_dt '|| p_eff_to_dt , 15);
227
228 hr_utility.set_location('node '|| p_date_mode , 15);
229 if g_debug then
230 hr_utility.set_location('Exiting'||l_proc, 15);
231 end if;
232
233 End get_pay_adv_crit_dates ;
234
235 -- ----------------------------------------------------------------------------
236 -- |------< Check_assg_info >----------------------------------------------|
237 -- ----------------------------------------------------------------------------
238 --
239 Procedure Check_assg_info (p_person_id in number,
240 p_effective_date in date ,
241 p_assignment_type in varchar2 ,
242 p_assignment_id in out nocopy number ) is
243 --
244 l_proc varchar2(72);
245 --
246 cursor c_asg is
247 select assignment_id
248 from per_all_assignments_f
249 where person_id = p_person_id
250 and p_effective_date between effective_start_date
251 and effective_end_date
252 and primary_flag = 'Y'
253 and (p_assignment_id is null or p_assignment_id = assignment_id )
254 and assignment_type = nvl(p_assignment_type,assignment_type) -- for any null will be sent
255 order by effective_start_date desc ; -- for any take the latest
256
257
258
259 cursor c_appl_asg is
260 select assignment_id
261 from per_all_assignments_f
262 where person_id = p_person_id
263 and p_effective_date between effective_start_date
264 and effective_end_date
265 and (p_assignment_id is null or p_assignment_id = assignment_id )
266 and assignment_type = nvl(p_assignment_type,assignment_type) -- for any null will be sent
267 order by effective_start_date desc ; -- for any take the latest
268
269
270
271 begin
272 if g_debug then
273 l_proc := g_package||'Check_assg_info';
274 hr_utility.set_location('Entering'||l_proc, 5);
275 end if;
276
277 open c_asg ;
278 fetch c_Asg into p_assignment_id ;
279 if c_asg%notfound then
280 p_assignment_id := null ;
281 end if ;
282 close c_Asg ;
283
284 --- if the type is applicant assignement then dont validate the primary key
285 ---
286 if p_assignment_id is null and p_assignment_type = 'A' then
287
288 open c_appl_asg ;
289 fetch c_appl_Asg into p_assignment_id ;
290 if c_appl_asg%notfound then
291 p_assignment_id := null ;
292 end if ;
293 close c_appl_Asg ;
294
295
296 end if ;
297
298 if g_debug then
299 hr_utility.set_location('assignment_id : ' || p_assignment_id , 99 );
300 hr_utility.set_location('Exiting'||l_proc, 15);
301 end if;
302 end Check_assg_info;
303
304
305 -- ----------------------------------------------------------------------------
306 -- |------< init_assignment_id >----------------------------------------------|
307 -- intialising the ass_id is taken from assignment_info , whether the assignment_info
308 -- called or not assignment_id is initalised
309 -- ----------------------------------------------------------------------------
310 --
311
312
313 Procedure init_assignment_id(p_person_id in number,
314 p_effective_date in date ,
315 p_assignment_id in number default null )is
316
317 --
318 l_proc varchar2(72);
319 l_asg_to_use_cd varchar2(10) ;
320 l_assignment_id number ;
321
322 --
323 Begin
324 if g_debug then
325 l_proc := g_package||'init_assignment_id';
326 hr_utility.set_location('Entering'||l_proc, 5);
327 end if;
328 -- p_asg id param added to validate a particular id
329 if p_assignment_id is not null then
330 l_assignment_id := p_assignment_id ;
331 end if ;
332
333 --if the assignment to use code is not defined then use
334 -- empl, benefit,applicant order
335
336 if g_debug then
337 hr_utility.set_location('rqd '|| ben_ext_evaluate_inclusion.g_asg_to_use_rqd, 99 );
338 end if;
339 if ben_ext_evaluate_inclusion.g_asg_to_use_rqd = 'Y' then
340 l_asg_to_use_cd := ben_ext_evaluate_inclusion.g_asg_to_use_list(1) ;
341 if g_debug then
342 hr_utility.set_location('order by user '|| l_asg_to_use_cd, 99 );
343 end if;
344 end if ;
345
346 if l_asg_to_use_cd is null then
347 l_asg_to_use_cd := 'EBAC' ; -- hardcoded default
348 -- Emp/BEN/Appl/Cont
349 end if ;
350 if g_debug then
351 hr_utility.set_location(' ass cd ' || l_asg_to_use_cd, 99 );
352 end if;
353
354 ----determine the kind of assignment
355 if l_asg_to_use_cd = 'EAO' then
356
357 -- Employee assignment only
358 Check_assg_info(p_person_id => p_person_id,
359 p_effective_date => p_effective_date ,
360 p_assignment_type => 'E' ,
361 p_assignment_id => l_assignment_id ) ;
362 elsif l_asg_to_use_cd = 'BAO' then
363 -- Employee assignment only
364 Check_assg_info(p_person_id => p_person_id,
365 p_effective_date => p_effective_date ,
366 p_assignment_type => 'B' ,
367 p_assignment_id => l_assignment_id ) ;
368 elsif l_asg_to_use_cd = 'ANY' then
369 Check_assg_info(p_person_id => p_person_id,
370 p_effective_date => p_effective_date ,
371 p_assignment_type => null ,
372 p_assignment_id => l_assignment_id ) ;
373
374 elsif l_asg_to_use_cd = 'AAO' then
375 -- Applicant assignment only
376 Check_assg_info(p_person_id => p_person_id,
377 p_effective_date => p_effective_date ,
378 p_assignment_type => 'A' ,
379 p_assignment_id => l_assignment_id ) ;
380 elsif l_asg_to_use_cd = 'CAO' then
381 -- Contngent assignment only
382 Check_assg_info(p_person_id => p_person_id,
383 p_effective_date => p_effective_date ,
384 p_assignment_type => 'C' ,
385 p_assignment_id => l_assignment_id ) ;
386 elsif l_asg_to_use_cd = 'ETB' then
387 -- Employee then Benefits assignment only
388 Check_assg_info(p_person_id => p_person_id,
389 p_effective_date => p_effective_date ,
390 p_assignment_type => 'E' ,
391 p_assignment_id => l_assignment_id ) ;
392 if l_assignment_id is null then
393 Check_assg_info(p_person_id => p_person_id,
394 p_effective_date => p_effective_date ,
395 p_assignment_type => 'B' ,
396 p_assignment_id => l_assignment_id ) ;
397 end if ;
398 elsif l_asg_to_use_cd = 'BTE' then
399 -- Benefits then Employee assignment only
400 Check_assg_info(p_person_id => p_person_id,
401
402 p_effective_date => p_effective_date ,
403 p_assignment_type => 'B' ,
404 p_assignment_id => l_assignment_id ) ;
405 if l_assignment_id is null then
406 Check_assg_info(p_person_id => p_person_id,
407 p_effective_date => p_effective_date ,
408 p_assignment_type => 'E' ,
409 p_assignment_id => l_assignment_id ) ;
410 end if ;
411
412 elsif l_asg_to_use_cd = 'EBA' then
413 -- Employee then Benefits then Applicant assignment only
414 Check_assg_info(p_person_id => p_person_id,
415 p_effective_date => p_effective_date ,
416 p_assignment_type => 'E' ,
417 p_assignment_id => l_assignment_id ) ;
418 if l_assignment_id is null then
419 Check_assg_info(p_person_id => p_person_id,
420 p_effective_date => p_effective_date ,
421 p_assignment_type => 'B' ,
422 p_assignment_id => l_assignment_id ) ;
423 if l_assignment_id is null then
424 Check_assg_info(p_person_id => p_person_id,
425 p_effective_date => p_effective_date ,
426 p_assignment_type => 'A' ,
427 p_assignment_id => l_assignment_id ) ;
428 end if ;
429
430 end if ;
431
432 elsif l_asg_to_use_cd = 'EBAC' then
433 -- Employee then Benefits then Applicant assignment only
434 Check_assg_info(p_person_id => p_person_id,
435 p_effective_date => p_effective_date ,
436 p_assignment_type => 'E' ,
437 p_assignment_id => l_assignment_id ) ;
438 if l_assignment_id is null then
439 Check_assg_info(p_person_id => p_person_id,
440 p_effective_date => p_effective_date ,
441 p_assignment_type => 'B' ,
442 p_assignment_id => l_assignment_id ) ;
443 if l_assignment_id is null then
444 Check_assg_info(p_person_id => p_person_id,
445 p_effective_date => p_effective_date ,
446 p_assignment_type => 'A' ,
447 p_assignment_id => l_assignment_id ) ;
448 if l_assignment_id is null then
449 Check_assg_info(p_person_id => p_person_id,
450 p_effective_date => p_effective_date ,
451 p_assignment_type => 'C' ,
452 p_assignment_id => l_assignment_id ) ;
453 end if ;
454 end if ;
455
456
457 end if ;
458
459
460 end if ;
461 ---intialise the global assignment_id
462 g_assignment_id := l_assignment_id ;
463
464 if g_debug then
465 hr_utility.set_location('assignment_id : ' || g_assignment_id , 99 );
466 hr_utility.set_location('Exiting'||l_proc, 15);
467 end if;
468
469 End init_assignment_id ;
470 --
471 --
472
473 --
474 -- ----------------------------------------------------------------------------
475 -- |------< get_person_info >----------------------------------------------|
476 -- ----------------------------------------------------------------------------
477 --
478 Procedure get_person_info(p_person_id in number,
479 p_effective_date in date) is
480 --
481 l_proc varchar2(72);
482 --
483 cursor c_person_info is
484 select
485 p.last_name
486 , p.date_of_birth
487 , p.employee_number
488 , p.first_name
489 , p.full_name
490 , p.marital_status
491 , p.middle_names
492 , p.national_identifier
493 , p.registered_disabled_flag
494 , p.sex
495 , p.student_status
496 , p.suffix
497 , p.pre_name_adjunct
498 , p.title
499 , p.date_of_death
500 , p.benefit_group_id
501 , p.applicant_number
502 , p.correspondence_language
503 , p.email_address
504 , p.known_as
505 , p.mailstop
506 , p.nationality
507 , p.pre_name_adjunct
508 , p.previous_last_name
509 , p.original_date_of_hire
510 , p.uses_tobacco_flag
511 , p.office_number
512 , p.date_employee_data_verified
513 , p.last_update_date
514 , p.last_updated_by
515 , p.last_update_login
516 , p.created_by
517 , p.creation_date
518 , p.attribute1
519 , p.attribute2
520 , p.attribute3
521 , p.attribute4
522 , p.attribute5
523 , p.attribute6
524 , p.attribute7
525 , p.attribute8
526 , p.attribute9
527 , p.attribute10
528 , p.person_type_id
529 ,ppt.user_person_type
530 ,p.per_information1
531 ,p.per_information2
532 ,p.per_information3
533 ,p.per_information4
534 ,p.per_information5
535 ,p.per_information6
536 ,p.per_information7
537 ,p.per_information8
538 ,p.per_information9
539 ,p.per_information10
540 ,p.per_information11
541 ,p.per_information12
542 ,p.per_information13
543 ,p.per_information14
544 ,p.per_information15
545 ,p.per_information16
546 ,p.per_information17
547 ,p.per_information18
548 ,p.per_information19
549 ,p.per_information20
550 ,p.per_information21
551 ,p.per_information22
552 ,p.per_information23
553 ,p.per_information24
554 ,p.per_information25
555 ,p.per_information26
556 ,p.per_information27
557 ,p.per_information28
558 ,p.per_information29
559 ,p.per_information30
560 ,p.business_group_id
561 from per_all_people_f p,
562 per_person_types ppt
563 where
564 p.person_id = p_person_id
565 and p_effective_date between p.effective_start_date
566 and p.effective_end_date
567 and p.business_group_id = ppt.business_group_id
568 and p.person_type_id = ppt.person_type_id
569 ;
570 --
571 cursor bus_c(p_id number)
572 is
573 select name
574 from per_business_groups_perf
575 where business_group_id = p_id
576 ;
577
578 l_business_group_id per_business_groups.business_group_id%type ;
579 l_business_group_name per_business_groups.name%type ;
580
581 Begin
582 --
583 if g_debug then
584 l_proc := g_package||'get_person_info';
585 hr_utility.set_location('Entering'||l_proc, 5);
586 end if;
587 --
588 open c_person_info;
589 fetch c_person_info into
590 g_last_name,
591 g_date_of_birth,
592 g_employee_number,
593 g_first_name,
594 g_full_name,
595 g_marital_status,
596 g_middle_names,
597 g_national_identifier,
598 g_registered_disabled_flag,
599 g_sex,
600 g_student_status,
601 g_suffix,
602 g_prefix,
603 g_title,
604 g_date_of_death,
605 g_benefit_group_id,
606 g_applicant_number,
607 g_correspondence_language,
608 g_email_address,
609 g_known_as,
610 g_mailstop,
611 g_nationality,
612 g_pre_name_adjunct,
613 g_previous_last_name,
614 g_original_date_of_hire,
615 g_uses_tobacco_flag,
616 g_office_number,
617 g_data_verification_dt,
618 g_last_update_date,
619 g_last_updated_by,
620 g_last_update_login,
621 g_created_by,
622 g_creation_date,
623 g_per_attr_1,
624 g_per_attr_2,
625 g_per_attr_3,
626 g_per_attr_4,
627 g_per_attr_5,
628 g_per_attr_6,
629 g_per_attr_7,
630 g_per_attr_8,
631 g_per_attr_9,
632 g_per_attr_10,
633 g_person_type_id,
634 g_person_types,
635 g_per_information1,
636 g_per_information2,
637 g_per_information3,
638 g_per_information4,
639 g_per_information5,
640 g_per_information6,
641 g_per_information7,
642 g_per_information8,
643 g_per_information9,
644 g_per_information10,
645 g_per_information11,
646 g_per_information12,
647 g_per_information13,
648 g_per_information14,
649 g_per_information15,
650 g_per_information16,
651 g_per_information17,
652 g_per_information18,
653 g_per_information19,
654 g_per_information20,
655 g_per_information21,
656 g_per_information22,
657 g_per_information23,
658 g_per_information24,
659 g_per_information25,
660 g_per_information26,
661 g_per_information27,
662 g_per_information28,
663 g_per_information29,
664 g_per_information30,
665 l_business_group_id
666 ;
667 --
668 if c_person_info%NOTFOUND THEN
669 --
670 -- invalid person id !!!
671 -- should close cursor and raise error here
672 --
673 null;
674 --
675 end if;
676 --
677 close c_person_info;
678
679
680 if ben_extract.g_bg_csr = 'Y' then
681 open bus_c(l_business_group_id);
682 fetch bus_c into l_business_group_name;
683 close bus_c;
684 end if ;
685
686
687 if g_ext_global_flag = 'Y' then
688 ben_ext_person.g_business_group_id := l_business_group_id ;
689 ben_extract.g_business_group_name := l_business_group_name ;
690 end if ;
691 hr_utility.set_location('Global BG ' || ben_ext_person.g_business_group_id|| ' / ' ||ben_extract.g_proc_business_group_id,99) ;
692
693 ---initalize the assignment_id as soon the person information avaialble
694 init_assignment_id(p_person_id =>p_person_id ,
695 p_effective_date =>p_effective_date );
696
697 if g_debug then
698 hr_utility.set_location('Tobacco Usage '||g_uses_tobacco_flag, 5);
699 hr_utility.set_location('Exiting'||l_proc, 15);
700 end if;
701 --
702 --
703 end get_person_info;
704
705
706 procedure get_pos_info (p_position_id in number,
707 p_effective_date in date ) is
708
709 --
710 l_proc varchar2(72) := g_package||'get_pos_info';
711 --
712 begin
713 if g_debug then
714 hr_utility.set_location('Entering'||l_proc, 5);
715 end if;
716
717 select
718 pos.name,
719 pos.attribute1,
720 pos.attribute2,
721 pos.attribute3,
722 pos.attribute4,
723 pos.attribute5,
724 pos.attribute6,
725 pos.attribute7,
726 pos.attribute8,
727 pos.attribute9,
728 pos.attribute10
729 into
730 g_position,
731 g_pos_flex_01,
732 g_pos_flex_02,
733 g_pos_flex_03,
734 g_pos_flex_04,
735 g_pos_flex_05,
736 g_pos_flex_06,
737 g_pos_flex_07,
738 g_pos_flex_08,
739 g_pos_flex_09,
740 g_pos_flex_10
741 from HR_ALL_POSITIONS_F pos
742 where pos.position_id = p_position_id
743 and p_effective_date between pos.EFFECTIVE_START_DATE and pos.EFFECTIVE_END_DATE ;
744
745 if g_debug then
746 hr_utility.set_location('Exiting'||l_proc, 15);
747 end if;
748
749 end get_pos_info ;
750
751
752 procedure get_job_info (p_job_id in number,
753 p_effective_date in date ) is
754
755 --
756 l_proc varchar2(72) := g_package||'get_job_info';
757 --
758 begin
759 if g_debug then
760 hr_utility.set_location('Entering'||l_proc, 5);
761 end if;
762 select
763 j.name,
764 j.attribute1,
765 j.attribute2,
766 j.attribute3,
767 j.attribute4,
768 j.attribute5,
769 j.attribute6,
770 j.attribute7,
771 j.attribute8,
772 j.attribute9,
773 j.attribute10
774 into
775 g_job,
776 g_job_flex_01,
777 g_job_flex_02,
778 g_job_flex_03,
779 g_job_flex_04,
780 g_job_flex_05,
781 g_job_flex_06,
782 g_job_flex_07,
783 g_job_flex_08,
784 g_job_flex_09,
785 g_job_flex_10
786 from per_jobs_vl j
787 where j.job_id = p_job_id;
788
789 if g_debug then
790 hr_utility.set_location('Exiting'||l_proc, 15);
791 end if;
792
793 end get_job_info ;
794
795
796
797 procedure get_payroll_info (p_payroll_id in number,
798 p_effective_date in date ) is
799
800 --
801 l_proc varchar2(72) := g_package||'get_payroll_info';
802 --
803 begin
804 if g_debug then
805 hr_utility.set_location('Entering'||l_proc, 5);
806 end if;
807
808 select
809 pay.payroll_name,
810 pay.period_type,
811 pay.attribute1,
812 pay.attribute2,
813 pay.attribute3,
814 pay.attribute4,
815 pay.attribute5,
816 pay.attribute6,
817 pay.attribute7,
818 pay.attribute8,
819 pay.attribute9,
820 pay.attribute10,
821 tmpr.period_num,
822 tmpr.start_date,
823 tmpr.end_date,
824 k.concatenated_segments,
825 k.cost_allocation_keyflex_id,
826 c.consolidation_set_name,
827 c.consolidation_set_id
828 into
829 g_payroll,
830 g_payroll_period_type,
831 g_prl_flex_01,
832 g_prl_flex_02,
833 g_prl_flex_03,
834 g_prl_flex_04,
835 g_prl_flex_05,
836 g_prl_flex_06,
837 g_prl_flex_07,
838 g_prl_flex_08,
839 g_prl_flex_09,
840 g_prl_flex_10,
841 g_payroll_period_number,
842 g_payroll_period_strtdt,
843 g_payroll_period_enddt,
844 g_payroll_costing,
845 g_payroll_costing_id,
846 g_payroll_consolidation_set,
847 g_payroll_consolidation_set_id
848 from pay_payrolls_f pay,
849 per_time_periods tmpr,
850 pay_cost_allocation_keyflex k,
851 pay_consolidation_sets c
852 where pay.payroll_id = p_payroll_id
853 and p_effective_date between
854 nvl(pay.effective_start_date, p_effective_date)
855 and nvl(pay.effective_end_date, p_effective_date)
856 and pay.payroll_id = tmpr.payroll_id
857 and pay.period_type = tmpr.period_type
858 and p_effective_date between nvl(tmpr.start_date, p_effective_date)
859 and nvl(tmpr.end_date, p_effective_date)
860 and pay.cost_allocation_keyflex_id = k.cost_allocation_keyflex_id (+)
861 and pay.consolidation_set_id = c.consolidation_set_id;
862
863
864
865 if g_debug then
866 hr_utility.set_location('Exiting'||l_proc, 15);
867 end if;
868
869 end get_payroll_info ;
870
871
872
873 procedure get_grade_info (p_grade_id in number,
874 p_effective_date in date ) is
875
876 --
877 l_proc varchar2(72) := g_package||'get_grade_info';
878 --
879 begin
880 if g_debug then
881 hr_utility.set_location('Entering'||l_proc, 5);
882 end if;
883
884
885 select
886 g.name,
887 g.attribute1,
888 g.attribute2,
889 g.attribute3,
890 g.attribute4,
891 g.attribute5,
892 g.attribute6,
893 g.attribute7,
894 g.attribute8,
895 g.attribute9,
896 g.attribute10
897 into
898 g_employee_grade,
899 g_grd_flex_01,
900 g_grd_flex_02,
901 g_grd_flex_03,
902 g_grd_flex_04,
903 g_grd_flex_05,
904 g_grd_flex_06,
905 g_grd_flex_07,
906 g_grd_flex_08,
907 g_grd_flex_09,
908 g_grd_flex_10
909 from per_grades_vl g
910 where g.grade_id = p_grade_id;
911
912 if g_debug then
913 hr_utility.set_location('Exiting'||l_proc, 15);
914 end if;
915
916 end get_grade_info ;
917
918 procedure get_org_loc_info (p_org_id in number,
919 p_effective_date in date ) is
920
921 --
922 l_proc varchar2(72) := g_package||'get_org_loc_info';
923
924
925 cursor c_org is
926 select location_id
927 from hr_all_organization_units
928 where organization_id = p_org_id ;
929
930 l_location_id Hr_locations_all.location_id%Type ;
931
932 cursor c_loc_info (p_location_id number) is
933 select l.address_line_1,
934 l.address_line_2,
935 l.address_line_3,
936 l.town_or_city,
937 l.country,
938 l.postal_code,
939 l.region_1,
940 l.region_2,
941 l.region_3,
942 l.Telephone_number_1
943 from hr_locations_all l
944 where l.location_id = p_location_id;
945
946 --
947 begin
948 if g_debug then
949 hr_utility.set_location('Entering'||l_proc, 5);
950 end if;
951
952 open c_org ;
953 fetch c_org into l_location_id ;
954 close c_org ;
955 if l_location_id is not null then
956
957 open c_loc_info(l_location_id) ;
958 fetch c_loc_info into
959 g_org_location_addr1,
960 g_org_location_addr2,
961 g_org_location_addr3,
962 g_org_location_city ,
963 g_org_location_country,
964 g_org_location_zip,
965 g_org_location_region1 ,
966 g_org_location_region2,
967 g_org_location_region3 ,
968 g_org_location_phone;
969
970 close c_loc_info ;
971 end if ;
972 if g_debug then
973 hr_utility.set_location('Exiting'||l_proc, 15);
974 end if;
975
976 end get_org_loc_info ;
977
978
979
980
981
982
983
984 procedure get_loc_info (p_location_id in number,
985 p_effective_date in date ) is
986
987 --
988 l_proc varchar2(72) := g_package||'get_loc_info';
989 --
990 begin
991 if g_debug then
992 hr_utility.set_location('Entering'||l_proc, 5);
993 end if;
994
995 select
996 l.location_code,
997 l.address_line_1,
998 l.address_line_2,
999 l.address_line_3,
1000 l.town_or_city,
1001 l.country,
1002 l.postal_code,
1003 l.region_1,
1004 l.region_2,
1005 l.region_3,
1006 l.attribute1,
1007 l.attribute2,
1008 l.attribute3,
1009 l.attribute4,
1010 l.attribute5,
1011 l.attribute6,
1012 l.attribute7,
1013 l.attribute8,
1014 l.attribute9,
1015 l.attribute10
1016 into
1017 g_location_code,
1018 g_location_addr1,
1019 g_location_addr2,
1020 g_location_addr3,
1021 g_location_city ,
1022 g_location_country,
1023 g_location_zip,
1024 g_location_region1 ,
1025 g_location_region2,
1026 g_location_region3,
1027 g_alc_flex_01,
1028 g_alc_flex_02,
1029 g_alc_flex_03,
1030 g_alc_flex_04,
1031 g_alc_flex_05,
1032 g_alc_flex_06,
1033 g_alc_flex_07,
1034 g_alc_flex_08,
1035 g_alc_flex_09,
1036 g_alc_flex_10
1037 from hr_locations_all l
1038 where l.location_id = p_location_id;
1039
1040
1041 if g_debug then
1042 hr_utility.set_location('Exiting'||l_proc, 15);
1043 end if;
1044
1045 end get_loc_info ;
1046
1047
1048 --
1049 --
1050 -- ----------------------------------------------------------------------------
1051 -- |------< get_assignment_info >----------------------------------------------|
1052 -- ----------------------------------------------------------------------------
1053 --
1054 Procedure get_assignment_info(p_person_id in number,
1055 p_assignment_id in number,
1056 p_effective_date in date ,
1057 p_ext_rslt_id in number )is
1058
1059 --
1060 l_proc varchar2(72) := g_package||'get_assignment_info';
1061 --
1062 cursor c_asg_info (p_assignment_id number ) is
1063 select
1064 a.bargaining_unit_code,
1065 a.grade_id,
1066 a.organization_id,
1067 a.location_id,
1068 a.assignment_status_type_id,
1069 a.title,
1070 a.position_id,
1071 a.job_id,
1072 a.payroll_id,
1073 a.people_group_id,
1074 a.pay_basis_id,
1075 a.hourly_salaried_code,
1076 a.labour_union_member_flag,
1077 a.manager_flag,
1078 a.employment_category,
1079 a.last_update_date ,
1080 a.last_updated_by ,
1081 a.last_update_login,
1082 a.created_by ,
1083 a.creation_date ,
1084 o.name,
1085 s.user_status,
1086 grp.group_name,
1087 b.name,
1088 b.attribute1,
1089 b.attribute2,
1090 b.attribute3,
1091 b.attribute4,
1092 b.attribute5,
1093 b.attribute6,
1094 b.attribute7,
1095 b.attribute8,
1096 b.attribute9,
1097 b.attribute10,
1098 a.ass_attribute1,
1099 a.ass_attribute2,
1100 a.ass_attribute3,
1101 a.ass_attribute4,
1102 a.ass_attribute5,
1103 a.ass_attribute6,
1104 a.ass_attribute7,
1105 a.ass_attribute8,
1106 a.ass_attribute9,
1107 a.ass_attribute10,
1108 a.normal_hours,
1109 a.frequency,
1110 a.time_normal_start,
1111 a.time_normal_finish,
1112 a.supervisor_id ,
1113 a.assignment_type,
1114 b.pay_basis
1115 from per_all_assignments_f a,
1116 hr_all_organization_units_vl o,
1117 per_assignment_status_types s,
1118 pay_people_groups grp,
1119 per_pay_bases b
1120 where
1121 a.person_id = p_person_id
1122 and p_effective_date between a.effective_start_date
1123 and a.effective_end_date
1124 and a.assignment_id = p_assignment_id
1125 and a.organization_id = o.organization_id
1126 and a.assignment_status_type_id = s.assignment_status_type_id
1127 and a.people_group_id = grp.people_group_id (+)
1128 and a.pay_basis_id = b.pay_basis_id (+)
1129 ;
1130
1131 l_asg_to_use_cd varchar2(10) ;
1132 l_assignment_id number ;
1133 Begin
1134 --
1135 if g_debug then
1136 hr_utility.set_location('Entering'||l_proc, 5);
1137 end if;
1138 --
1139 open c_asg_info (p_assignment_id);
1140 fetch c_asg_info into
1141 g_employee_barg_unit,
1142 g_employee_grade_id,
1143 g_employee_organization_id,
1144 g_location_id,
1145 g_employee_status_id,
1146 g_asg_title,
1147 g_position_id,
1148 g_job_id,
1149 g_payroll_id,
1150 g_people_group_id,
1151 g_pay_basis_id,
1152 g_hourly_salaried_code,
1153 g_labour_union_member_flag,
1154 g_manager_flag,
1155 g_employee_category,
1156 g_asg_last_update_date,
1157 g_asg_last_updated_by,
1158 g_asg_last_update_login,
1159 g_asg_created_by,
1160 g_asg_creation_date,
1161 g_employee_organization,
1162 g_employee_status,
1163 g_people_group,
1164 g_pay_basis,
1165 g_pbs_flex_01,
1166 g_pbs_flex_02,
1167 g_pbs_flex_03,
1168 g_pbs_flex_04,
1169 g_pbs_flex_05,
1170 g_pbs_flex_06,
1171 g_pbs_flex_07,
1172 g_pbs_flex_08,
1173 g_pbs_flex_09,
1174 g_pbs_flex_10,
1175 g_asg_attr_1,
1176 g_asg_attr_2,
1177 g_asg_attr_3,
1178 g_asg_attr_4,
1179 g_asg_attr_5,
1180 g_asg_attr_6,
1181 g_asg_attr_7,
1182 g_asg_attr_8,
1183 g_asg_attr_9,
1184 g_asg_attr_10,
1185 g_asg_normal_hours,
1186 g_asg_frequency,
1187 g_asg_time_normal_start,
1188 g_asg_time_normal_finish,
1189 g_asg_supervisor_id,
1190 g_asg_type,
1191 g_pay_basis_type
1192 ;
1193
1194 close c_asg_info;
1195
1196 if g_debug then
1197 hr_utility.set_location('Payroll id '||g_payroll_id, 5);
1198 end if;
1199
1200 begin
1201 if g_employee_grade_id is not null
1202 then
1203 if g_debug then
1204 hr_utility.set_location('asg Grade'||g_employee_grade_id, 5);
1205 end if;
1206
1207 get_grade_info (p_grade_id => g_employee_grade_id,
1208 p_effective_date => p_effective_date );
1209
1210 end if;
1211
1212 if g_location_id is not null then
1213 if g_debug then
1214 hr_utility.set_location('asg Location'||g_location_id , 5);
1215 end if;
1216 get_loc_info (p_location_id => g_location_id,
1217 p_effective_date => p_effective_date );
1218
1219 end if;
1220
1221 if g_position_id is not null then
1222 if g_debug then
1223 hr_utility.set_location('Asg Position'||g_position_id, 5);
1224 end if;
1225 get_pos_info (p_position_id => g_position_id,
1226 p_effective_date => p_effective_date ) ;
1227 end if;
1228
1229 if g_job_id is not null then
1230 if g_debug then
1231 hr_utility.set_location('Asg Job'||g_job_id, 5);
1232 end if;
1233 get_job_info (p_job_id => g_job_id,
1234 p_effective_date => p_effective_date );
1235 end if;
1236
1237 if g_payroll_id is not null then
1238 if g_debug then
1239 hr_utility.set_location('asg pay'||g_payroll_id, 5);
1240 end if;
1241
1242 get_payroll_info (p_payroll_id => g_payroll_id,
1243 p_effective_date => p_effective_date );
1244 end if;
1245
1246 if g_employee_organization_id is not null then
1247 if g_debug then
1248 hr_utility.set_location('Emp org '||g_employee_organization_id, 5);
1249 end if;
1250
1251 get_org_loc_info (p_org_id => g_employee_organization_id,
1252 p_effective_date => p_effective_date );
1253 end if;
1254
1255
1256 Exception
1257 When NO_DATA_FOUND then
1258 if g_debug then
1259 hr_utility.set_location('NO_DATA_FOUND IN ASG CHILD ', 5) ;
1260 end if;
1261 g_err_num := 94102 ;
1262 g_err_name := 'BEN_94102_EXT_ERROR_ON_ASG' ;
1263
1264
1265 Raise ;
1266
1267 end;
1268 if g_debug then
1269 hr_utility.set_location('asg type '|| g_asg_type, 99 );
1270 hr_utility.set_location('Exiting'||l_proc, 15);
1271 end if;
1272 --
1273 --
1274 end get_assignment_info;
1275
1276
1277 --
1278 -- ----------------------------------------------------------------------------
1279 -- |------< get_School_info >------------------------------------------|
1280 -- This procedure extract only the current school
1281 -- ----------------------------------------------------------------------------
1282 --
1283 procedure get_School_info(p_person_id in number ,
1284 p_effective_date in date ) is
1285
1286 --
1287 l_proc varchar2(72);
1288 --
1289 Cursor c_school
1290 is select
1291 est.name
1292 from PER_ESTABLISHMENTS EST ,
1293 PER_ESTABLISHMENT_ATTENDANCES esa
1294 where esa.person_id = p_person_id
1295 and est.ESTABLISHMENT_id = esa.ESTABLISHMENT_id
1296 and p_effective_date between attended_start_date and nvl(attended_end_date,p_effective_date);
1297
1298
1299
1300 Begin
1301 if g_debug then
1302 l_proc := g_package||'get_School_info';
1303 hr_utility.set_location('Entering'||l_proc, 15);
1304 end if;
1305 open c_school ;
1306 fetch c_school into g_ESTABLISHMENT_name ;
1307 if c_school%notfound then
1308 g_ESTABLISHMENT_name := null ;
1309 end if ;
1310 close c_school ;
1311
1312 if g_debug then
1313 hr_utility.set_location('Exiting'||l_proc, 15);
1314 end if;
1315 end get_School_info;
1316
1317
1318 --
1319 -- ----------------------------------------------------------------------------
1320 -- |------< get_base_annual_salary_info_info >------------------------------------------|
1321 -- ----------------------------------------------------------------------------
1322 --
1323 procedure get_base_annual_salary_info(p_person_id in number ,
1324 p_effective_date in date ) is
1325
1326 --
1327 l_proc varchar2(72);
1328 --
1329 cursor c (l_person_id number ) is select
1330 a.pay_annualization_factor,b.proposed_salary_n
1331 from per_pay_bases a,
1332 per_pay_proposals b,
1333 per_all_assignments_f c
1334 where
1335 c.person_id = l_person_id and
1336 p_effective_date between c.effective_start_date and c.effective_end_date and
1337 c.assignment_id= g_assignment_id and
1338 c.assignment_id = b.assignment_id and
1339 c.pay_basis_id = a.pay_basis_id
1340 and b.change_date =
1341 (select max(d.change_date)
1342 from per_pay_proposals d
1343 where d.assignment_id = c.assignment_id
1344 and d.change_date <= p_effective_date
1345 and approved = 'Y' )
1346 ;
1347
1348 lc c%rowtype ;
1349
1350 begin
1351 if g_debug then
1352 l_proc := g_package||'get_base_annual_salary_info_info';
1353 hr_utility.set_location('Entering'||l_proc, 15);
1354 end if;
1355
1356 open c (p_person_id ) ;
1357 fetch c into lc ;
1358 close c ;
1359 g_base_salary := lc.pay_annualization_factor * lc.proposed_salary_n ;
1360 if g_debug then
1361 hr_utility.set_location(' salary ' || g_base_salary , 936);
1362 hr_utility.set_location('Exiting'||l_proc, 15);
1363 end if;
1364 end get_base_annual_salary_info;
1365
1366
1367
1368
1369 --
1370 -- ----------------------------------------------------------------------------
1371 -- ------< get_person_flex_credit>-----------------------------------------
1372 -- ----------------------------------------------------------------------------
1373 --
1374 Procedure get_person_flex_credit(p_person_id in number,
1375 p_effective_date in date) is
1376 --
1377 l_proc varchar2(72);
1378 --
1379 --
1380 cursor flex_cred_info_c is
1381 select
1382 sum(bpl.prvdd_val) credit_provided
1383 , sum(bpl.frftd_val) credit_forfited
1384 , sum(bpl.used_val) credit_used
1385 from ben_prtt_enrt_rslt_f pen
1386 ,ben_per_in_ler pil
1387 ,ben_bnft_prvdd_ldgr_f bpl
1388 ,ben_pl_f pl
1389 where
1390 pen.person_id = p_person_id
1391 and pen.prtt_enrt_rslt_id = bpl.prtt_enrt_rslt_id
1392 and p_effective_date between nvl(pen.effective_start_date, p_effective_date)
1393 and nvl(pen.effective_end_date, p_effective_date)
1394 and p_effective_date between nvl(bpl.effective_start_date, p_effective_date)
1395 and nvl(bpl.effective_end_date, p_effective_date)
1396 and pil.per_in_ler_id=bpl.per_in_ler_id
1397 and pil.business_group_id+0=bpl.business_group_id+0
1398 and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
1399 and pen.pl_id = pl.pl_id
1400 and pl.invk_flx_cr_pl_flag = 'Y'
1401 and pl.imptd_incm_calc_cd is null
1402 and p_effective_date between nvl(pl.effective_start_date, p_effective_date)
1403 and nvl(pl.effective_end_date, p_effective_date)
1404 ;
1405
1406 begin
1407 if g_debug then
1408 l_proc := g_package||'get_person_flex_credit';
1409 hr_utility.set_location('Entering'||l_proc, 15);
1410 end if;
1411
1412 -- the fLex cedit calcualted in person level
1413 if g_debug then
1414 hr_utility.set_location('entering to open flex credit ' ,160);
1415 end if;
1416 open flex_cred_info_c;
1417 fetch flex_cred_info_c into ben_ext_person.g_flex_credit_provided
1418 ,ben_ext_person.g_flex_credit_forfited
1419 ,ben_ext_person.g_flex_credit_used;
1420 ben_ext_person.g_flex_credit_excess :=
1421 nvl(ben_ext_person.g_flex_credit_provided,0) -
1422 nvl(ben_ext_person.g_flex_credit_forfited,0) -
1423 nvl(ben_ext_person.g_flex_credit_used,0);
1424 close flex_cred_info_c;
1425 if g_debug then
1426 hr_utility.set_location('provided amount '|| ben_ext_person.g_flex_credit_provided ,160);
1427 hr_utility.set_location('used amount '|| ben_ext_person.g_flex_credit_used ,160);
1428 hr_utility.set_location('Exiting'||l_proc, 15);
1429 end if;
1430
1431 end get_person_flex_credit;
1432
1433
1434
1435
1436
1437 --
1438 -- ----------------------------------------------------------------------------
1439 -- |------< get_supervisor_info >------------------------------------------|
1440 -- ----------------------------------------------------------------------------
1441 --
1442 Procedure get_supervisor_info(p_supervisor_id in number,
1443 p_effective_date in date) is
1444 --
1445 l_proc varchar2(72);
1446 --
1447 cursor c_sup_info is
1448 select
1449 full_name,
1450 employee_number
1451 from per_all_people_f
1452 where person_id = p_supervisor_id
1453 and p_effective_date between effective_start_date
1454 and effective_end_date ;
1455 begin
1456 if g_debug then
1457 l_proc := g_package||'get_supervisor_info';
1458 hr_utility.set_location('Entering'||l_proc, 15);
1459 end if;
1460
1461 open c_sup_info ;
1462 fetch c_sup_info into
1463 g_sup_full_name ,
1464 g_sup_employee_number ;
1465 close c_sup_info ;
1466
1467 if g_debug then
1468 hr_utility.set_location('Exiting'||l_proc, 15);
1469 end if;
1470 end get_supervisor_info;
1471
1472
1473 --
1474 -- ----------------------------------------------------------------------------
1475 -- |------< get_primary_address_info >------------------------------------------|
1476 -- ----------------------------------------------------------------------------
1477 --
1478 Procedure get_primary_address_info(p_person_id in number,
1479 p_effective_date in date) is
1480 --
1481 l_proc varchar2(72);
1482 --
1483 cursor c_prmy_address is
1484 select
1485 a.address_line1
1486 , a.address_line2
1487 , a.address_line3
1488 , a.town_or_city
1489 , a.region_2
1490 -- if the address style is CA or CA GLB then get the state from region_1
1491 , decode(a.style ,'CA_GLB',a.region_1,'CA',a.region_1 , a.region_2) state_ansi
1492 , a.postal_code
1493 , a.country
1494 , a.region_1
1495 , a.region_3
1496 , a.date_from
1497 , a.last_update_date
1498 , a.last_updated_by
1499 , a.last_update_login
1500 , a.created_by
1501 , a.creation_date
1502 from per_addresses a
1503 where
1504 a.person_id = p_person_id
1505 and p_effective_date between nvl(a.date_from, p_effective_date)
1506 and nvl(a.date_to, p_effective_date)
1507 and a.primary_flag = 'Y'
1508 ;
1509
1510 -- related persons primary address
1511 cursor c_rltd_prmy_address is
1512 select
1513 a.address_line1
1514 , a.address_line2
1515 , a.address_line3
1516 , a.town_or_city
1517 , decode(a.style ,'CA_GLB',a.region_1,'CA',a.region_1 , a.region_2) state_ansi
1518 , a.region_2
1519 , a.postal_code
1520 , a.country
1521 , a.region_1
1522 , a.region_3
1523 , a.date_from
1524 from per_addresses a,
1525 per_contact_relationships c,
1526 per_all_people_f p
1527 where
1528 c.contact_person_id = p_person_id
1529 and c.person_id = p.person_id
1530 and a.person_id = p.person_id
1531 and a.primary_flag = 'Y'
1532 and c.rltd_per_rsds_w_dsgntr_flag = 'Y'
1533 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
1534 and nvl(p.effective_end_date, p_effective_date)
1535 and p_effective_date between nvl(a.date_from, p_effective_date)
1536 and nvl(a.date_to, p_effective_date);
1537
1538 --
1539 Begin
1540 --
1541 if g_debug then
1542 l_proc := g_package||'get_primary_address_info';
1543 hr_utility.set_location('Entering'||l_proc, 5);
1544 end if;
1545 --
1546 open c_prmy_address;
1547 fetch c_prmy_address into
1548 g_prim_address_line_1,
1549 g_prim_address_line_2,
1550 g_prim_address_line_3,
1551 g_prim_city,
1552 g_prim_state,
1553 g_prim_state_ansi,
1554 g_prim_postal_code,
1555 g_prim_country,
1556 g_prim_county,
1557 g_prim_region_3,
1558 g_prim_address_date,
1559 g_addr_last_update_date,
1560 g_addr_last_updated_by,
1561 g_addr_last_update_login,
1562 g_addr_created_by,
1563 g_addr_creation_date
1564 ;
1565 --
1566 if c_prmy_address%notfound then
1567 --
1568 -- when address is not found grab one on the related person that resides
1569 -- with them. This will get addresses for contacts.
1570 --
1571 open c_rltd_prmy_address;
1572 fetch c_rltd_prmy_address into
1573 g_prim_address_line_1,
1574 g_prim_address_line_2,
1575 g_prim_address_line_3,
1576 g_prim_city,
1577 g_prim_state,
1578 g_prim_state_ansi,
1579 g_prim_postal_code,
1580 g_prim_country,
1581 g_prim_county,
1582 g_prim_region_3,
1583 g_prim_address_date
1584 ;
1585 --
1586 close c_rltd_prmy_address;
1587 end if;
1588 close c_prmy_address;
1589 --
1590 --
1591 if g_debug then
1592 hr_utility.set_location('Exiting'||l_proc, 15);
1593 end if;
1594 --
1595 end get_primary_address_info;
1596 --
1597 -- ----------------------------------------------------------------------------
1598 -- |------< get_mailing_address_info >------------------------------------------|
1599 -- ----------------------------------------------------------------------------
1600 --
1601 Procedure get_mailing_address_info(p_person_id in number,
1602 p_effective_date in date) is
1603 --
1604 l_proc varchar2(72);
1605 --
1606 cursor c_mail_address is
1607 select
1608 a.address_line1
1609 , a.address_line2
1610 , a.address_line3
1611 , a.town_or_city
1612 , a.region_2
1613 , a.postal_code
1614 , a.country
1615 , a.region_1
1616 , a.region_3
1617 , a.date_from
1618 from per_addresses a
1619 where
1620 a.person_id = p_person_id
1621 and p_effective_date between nvl(a.date_from, p_effective_date)
1622 and nvl(a.date_to, p_effective_date)
1623 and a.primary_flag = 'N'
1624 and a.address_type = 'M'
1625 ;
1626 --
1627 Begin
1628 --
1629 if g_debug then
1630 l_proc := g_package||'get_mailing_address_info';
1631 hr_utility.set_location('Entering'||l_proc, 5);
1632 end if;
1633 --
1634 open c_mail_address;
1635 fetch c_mail_address into
1636 g_mail_address_line_1,
1637 g_mail_address_line_2,
1638 g_mail_address_line_3,
1639 g_mail_city,
1640 g_mail_state,
1641 g_mail_postal_code,
1642 g_mail_country,
1643 g_mail_county,
1644 g_mail_region_3,
1645 g_mail_address_date
1646 ;
1647 --
1648 close c_mail_address;
1649 --
1650 if g_debug then
1651 hr_utility.set_location('Exiting'||l_proc, 15);
1652 end if;
1653 --
1654 end get_mailing_address_info;
1655 --
1656 -- ----------------------------------------------------------------------------
1657 -- |------< get_comm_address_info >------------------------------------------|
1658 -- ----------------------------------------------------------------------------
1659 --
1660 -- NOTE: See misc/oab/extract/Address hierarchy logic.doc for more info.
1661 --
1662 Procedure get_comm_address_info(p_person_id in number,
1663 p_address_id in number,
1664 p_effective_date in date) is
1665 --
1666 l_proc varchar2(72);
1667 --
1668 cursor c_comm_address is
1669 select
1670 a.address_line1
1671 , a.address_line2
1672 , a.address_line3
1673 , a.town_or_city
1674 , a.region_2
1675 , a.postal_code
1676 , a.country
1677 , a.region_1
1678 , a.region_3
1679 , a.date_from
1680 from per_addresses a
1681 where
1682 a.address_id = p_address_id;
1683 --
1684 cursor c_prim_rltd_address is
1685 select
1686 a.address_line1
1687 , a.address_line2
1688 , a.address_line3
1689 , a.town_or_city
1690 , a.region_2
1691 , a.postal_code
1692 , a.country
1693 , a.region_1
1694 , a.region_3
1695 , a.date_from
1696 from per_addresses a,
1697 per_contact_relationships r
1698 where
1699 r.contact_person_id = p_person_id
1700 and r.person_id = a.person_id
1701 and a.town_or_city is not null
1702 and p_effective_date between nvl(a.date_from, p_effective_date)
1703 and nvl(a.date_to, p_effective_date)
1704 and a.primary_flag = 'Y'
1705 and r.rltd_per_rsds_w_dsgntr_flag = 'Y'
1706 ;
1707 --
1708 Begin
1709 --
1710 if g_debug then
1711 l_proc := g_package||'get_comm_address_info';
1712 hr_utility.set_location('Entering'||l_proc, 5);
1713 end if;
1714 --
1715 --
1716 IF p_address_id is not null then
1717 open c_comm_address;
1718 fetch c_comm_address into
1719 g_cm_addr_line1,
1720 g_cm_addr_line2,
1721 g_cm_addr_line3,
1722 g_cm_city,
1723 g_cm_state,
1724 g_cm_postal_code,
1725 g_cm_country,
1726 g_cm_county,
1727 g_cm_region_3,
1728 g_cm_address_date
1729 ;
1730 --
1731 close c_comm_address;
1732
1733 --
1734 END IF;
1735 --
1736 -- If communication address was not found use mailing address.
1737 --
1738 IF g_cm_city is null and g_mail_city is not null then
1739 --
1740 g_cm_addr_line1 := g_mail_address_line_1;
1741 g_cm_addr_line2 := g_mail_address_line_2;
1742 g_cm_addr_line3 := g_mail_address_line_3;
1743 g_cm_city := g_mail_city;
1744 g_cm_state := g_mail_state;
1745 g_cm_postal_code := g_mail_postal_code;
1746 g_cm_country := g_mail_country;
1747 g_cm_county := g_mail_county;
1748 g_cm_region_3 := g_mail_region_3;
1749 g_cm_address_date := g_mail_address_date;
1750 --
1751 END IF; --g_cm_city is null and g_mail_city is not null then
1752 --
1753 -- If communication address is still blank use primary address.
1754 --
1755 IF g_cm_city is null and ( g_prim_city is not null or g_prim_state is not null ) then
1756 --
1757 g_cm_addr_line1 := g_prim_address_line_1;
1758 g_cm_addr_line2 := g_prim_address_line_2;
1759 g_cm_addr_line3 := g_prim_address_line_3;
1760 g_cm_city := g_prim_city;
1761 g_cm_state := g_prim_state;
1762 g_cm_postal_code := g_prim_postal_code;
1763 g_cm_country := g_prim_country;
1764 g_cm_county := g_prim_county;
1765 g_cm_region_3 := g_prim_region_3;
1766 g_cm_address_date := g_prim_address_date;
1767 --
1768 END IF; --g_cm_city is null and g_prim_city is not null then
1769 --
1770 If (g_cm_city is null and g_cm_state is null) then
1771 open c_prim_rltd_address;
1772 fetch c_prim_rltd_address into
1773 g_cm_addr_line1,
1774 g_cm_addr_line2,
1775 g_cm_addr_line3,
1776 g_cm_city,
1777 g_cm_state,
1778 g_cm_postal_code,
1779 g_cm_country,
1780 g_cm_county,
1781 g_cm_region_3,
1782 g_cm_address_date
1783 ;
1784 close c_prim_rltd_address;
1785 --
1786 End if;
1787 if g_debug then
1788 hr_utility.set_location('Exiting'||l_proc, 15);
1789 end if;
1790 --
1791 end get_comm_address_info;
1792 --
1793 -- ----------------------------------------------------------------------------
1794 -- |------< get_phone_info >----------------------------------------------|
1795 -- ----------------------------------------------------------------------------
1796 --
1797 Procedure get_phone_info(p_person_id in number,
1798 p_effective_date in date) is
1799 --
1800 l_proc varchar2(72);
1801 --
1802 cursor c_phone is
1803 select
1804 h.phone_number phone_home
1805 , w.phone_number phone_work
1806 , f.phone_number phone_fax
1807 , m.phone_number phone_mobile
1808 from per_all_people_f p
1809 , per_phones h
1810 , per_phones w
1811 , per_phones f
1812 , per_phones m
1813 where p.person_id = p_person_id
1814 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
1815 and nvl(p.effective_end_date, p_effective_date)
1816 and h.parent_id (+) = p.person_id
1817 and w.parent_id (+) = p.person_id
1818 and f.parent_id (+) = p.person_id
1819 and m.parent_id (+) = p.person_id
1820 and h.parent_table (+) = 'PER_ALL_PEOPLE_F'
1821 and w.parent_table (+) = 'PER_ALL_PEOPLE_F'
1822 and f.parent_table (+) = 'PER_ALL_PEOPLE_F'
1823 and m.parent_table (+) = 'PER_ALL_PEOPLE_F'
1824 and h.phone_type (+) = 'H1'
1825 and w.phone_type (+) = 'W1'
1826 and f.phone_type (+) = 'WF'
1827 and m.phone_type (+) = 'M'
1828 and p_effective_date between nvl(h.date_from, p_effective_date)
1829 and nvl(h.date_to, p_effective_date)
1830 and p_effective_date between nvl(w.date_from, p_effective_date)
1831 and nvl(w.date_to, p_effective_date)
1832 and p_effective_date between nvl(f.date_from, p_effective_date)
1833 and nvl(f.date_to, p_effective_date)
1834 and p_effective_date between nvl(m.date_from, p_effective_date)
1835 and nvl(m.date_to, p_effective_date)
1836 ;
1837
1838
1839
1840 cursor c_rltd_phone is
1841 select
1842 h.phone_number phone_home
1843 , w.phone_number phone_work
1844 , f.phone_number phone_fax
1845 , m.phone_number phone_mobile
1846 from per_all_people_f p
1847 , per_phones h
1848 , per_phones w
1849 , per_phones f
1850 , per_phones m
1851 ,per_contact_relationships r
1852 where r.contact_person_id = p_person_id
1853 and r.rltd_per_rsds_w_dsgntr_flag = 'Y'
1854 and p.person_id = r.person_id
1855 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
1856 and nvl(p.effective_end_date, p_effective_date)
1857 and h.parent_id (+) = p.person_id
1858 and w.parent_id (+) = p.person_id
1859 and f.parent_id (+) = p.person_id
1860 and m.parent_id (+) = p.person_id
1861 and h.parent_table (+) = 'PER_ALL_PEOPLE_F'
1862 and w.parent_table (+) = 'PER_ALL_PEOPLE_F'
1863 and f.parent_table (+) = 'PER_ALL_PEOPLE_F'
1864 and m.parent_table (+) = 'PER_ALL_PEOPLE_F'
1865 and h.phone_type (+) = 'H1'
1866 and w.phone_type (+) = 'W1'
1867 and f.phone_type (+) = 'WF'
1868 and m.phone_type (+) = 'M'
1869 and p_effective_date between nvl(h.date_from, p_effective_date)
1870 and nvl(h.date_to, p_effective_date)
1871 and p_effective_date between nvl(w.date_from, p_effective_date)
1872 and nvl(w.date_to, p_effective_date)
1873 and p_effective_date between nvl(f.date_from, p_effective_date)
1874 and nvl(f.date_to, p_effective_date)
1875 and p_effective_date between nvl(m.date_from, p_effective_date)
1876 and nvl(m.date_to, p_effective_date)
1877 ;
1878 --
1879 Begin
1880 --
1881 if g_debug then
1882 l_proc := g_package||'get_phone_info';
1883 hr_utility.set_location('Entering'||l_proc, 5);
1884 end if;
1885 --
1886 open c_phone;
1887 fetch c_phone into
1888 g_phone_home,
1889 g_phone_work,
1890 g_phone_fax,
1891 g_phone_mobile
1892 ;
1893 hr_utility.set_location(' looking phone ' || g_phone_home , 99 );
1894 if c_phone%notfound or
1895 (g_phone_home is null and g_phone_work is null and g_phone_fax is null and g_phone_mobile is null) then
1896 hr_utility.set_location(' looking for related phone ' || p_person_id , 99 );
1897 -- get related person information
1898 open c_rltd_phone;
1899 fetch c_rltd_phone into
1900 g_phone_home,
1901 g_phone_work,
1902 g_phone_fax,
1903 g_phone_mobile
1904 ;
1905 close c_rltd_phone;
1906 hr_utility.set_location(' home related phone ' || g_phone_home , 99 );
1907 end if ;
1908 --
1909 close c_phone;
1910 --
1911 if g_debug then
1912 hr_utility.set_location('Exiting'||l_proc, 15);
1913 end if;
1914 --
1915 end get_phone_info;
1916 --
1917 -- ----------------------------------------------------------------------------
1918 -- |------< get_period_of_svc_info >-------------------------------------------|
1919 -- ----------------------------------------------------------------------------
1920 --
1921 Procedure get_period_of_svc_info(p_person_id in number,
1922 p_effective_date in date) is
1923 --
1924 l_proc varchar2(72);
1925 --
1926 cursor c_period_of_svc is
1927 select date_start
1928 , actual_termination_date
1929 , adjusted_svc_date
1930 , leaving_reason
1931 , last_update_date
1932 , last_updated_by
1933 , last_update_login
1934 , created_by
1935 , creation_date
1936 , attribute1
1937 , attribute2
1938 , attribute3
1939 , attribute4
1940 , attribute5
1941 , attribute6
1942 , attribute7
1943 , attribute8
1944 , attribute9
1945 , attribute10
1946 from per_periods_of_service pps
1947 where pps.person_id = p_person_id
1948 and pps.date_start = (select max(pps1.date_start) -- this gets most recent
1949 from per_periods_of_service pps1
1950 where pps1.person_id = p_person_id
1951 and pps1.date_start <= p_effective_date);
1952
1953 --
1954 Begin
1955 --
1956 if g_debug then
1957 l_proc := g_package||'get_period_of_svc_info';
1958 hr_utility.set_location('Entering'||l_proc, 5);
1959 end if;
1960 --
1961 open c_period_of_svc;
1962 fetch c_period_of_svc into
1963 g_last_hire_date,
1964 g_actual_term_date,
1965 g_adjusted_svc_date,
1966 g_term_reason,
1967 g_pos_last_update_date,
1968 g_pos_last_updated_by,
1969 g_pos_last_update_login,
1970 g_pos_created_by,
1971 g_pos_creation_date,
1972 g_prs_flex_01,
1973 g_prs_flex_02,
1974 g_prs_flex_03,
1975 g_prs_flex_04,
1976 g_prs_flex_05,
1977 g_prs_flex_06,
1978 g_prs_flex_07,
1979 g_prs_flex_08,
1980 g_prs_flex_09,
1981 g_prs_flex_10
1982 ;
1983 close c_period_of_svc;
1984 --
1985 if g_debug then
1986 hr_utility.set_location('Exiting'||l_proc, 15);
1987 end if;
1988 --
1989 end get_period_of_svc_info;
1990 --
1991 -- ----------------------------------------------------------------------------
1992 -- |------< get_svc_area_info >----------------------------------------------|
1993 -- ----------------------------------------------------------------------------
1994 --
1995 Procedure get_svc_area_info(p_postal_code in varchar2,
1996 p_effective_date in date) is
1997 --
1998 l_proc varchar2(72);
1999 --
2000 cursor c_prmy_svc_area is
2001 select svc.svc_area_id,
2002 svc.name
2003 from ben_svc_area_f svc
2004 , ben_svc_area_pstl_zip_rng_f svps
2005 , ben_pstl_zip_rng_f pszip
2006 where p_postal_code between nvl(pszip.from_value, p_postal_code)
2007 and nvl(pszip.to_value, p_postal_code)
2008 and pszip.pstl_zip_rng_id = svps.pstl_zip_rng_id
2009 and svps.svc_area_id = svc.svc_area_id
2010 and p_effective_date between nvl(svps.effective_start_date, p_effective_date)
2011 and nvl(svps.effective_end_date, p_effective_date)
2012 and p_effective_date between nvl(svc.effective_start_date, p_effective_date)
2013 and nvl(svc.effective_end_date, p_effective_date)
2014 and p_effective_date between nvl(pszip.effective_start_date, p_effective_date)
2015 and nvl(pszip.effective_end_date, p_effective_date);
2016 --
2017 Begin
2018 --
2019 if g_debug then
2020 l_proc := g_package||'get_svc_area_info';
2021 hr_utility.set_location('Entering'||l_proc, 5);
2022 end if;
2023 --
2024 open c_prmy_svc_area;
2025 fetch c_prmy_svc_area into ben_ext_person.g_prim_addr_sva_id,
2026 ben_ext_person.g_prim_addr_service_area;
2027 close c_prmy_svc_area;
2028 --
2029 if g_debug then
2030 hr_utility.set_location('Exiting'||l_proc, 15);
2031 end if;
2032 --
2033 end get_svc_area_info;
2034 --
2035 -- ----------------------------------------------------------------------------
2036 -- |------< get_started_ler_info >----------------------------------------------|
2037 -- ----------------------------------------------------------------------------
2038 --
2039 Procedure get_started_ler_info(p_person_id in number,
2040 p_effective_date in date) is
2041 --
2042 l_proc varchar2(72);
2043 --
2044 cursor c_started_ler is
2045 select /*+ leading(PLER) */
2046 pler.per_in_ler_id per_in_ler_id
2047 , pler.lf_evt_ocrd_dt lf_evt_ocrd_dt
2048 , pler.ntfn_dt lf_evt_note_dt
2049 , ler.ler_id ler_id
2050 , ler.name ler_name
2051 , ler.ler_attribute1
2052 , ler.ler_attribute2
2053 , ler.ler_attribute3
2054 , ler.ler_attribute4
2055 , ler.ler_attribute5
2056 , ler.ler_attribute6
2057 , ler.ler_attribute7
2058 , ler.ler_attribute8
2059 , ler.ler_attribute9
2060 , ler.ler_attribute10
2061 from
2062 ben_per_in_ler pler,
2063 ben_ler_f ler
2064 where
2065 pler.person_id = p_person_id
2066 and pler.ler_id = ler.ler_id
2067 and pler.per_in_ler_stat_cd = 'STRTD'
2068 and p_effective_date between ler.effective_start_date and ler.effective_end_date
2069 ;
2070 --
2071 Begin
2072 --
2073 if g_debug then
2074 l_proc := g_package||'get_started_ler_info';
2075 hr_utility.set_location('Entering'||l_proc, 5);
2076 end if;
2077 --
2078 open c_started_ler;
2079 fetch c_started_ler into
2080 g_per_in_ler_id,
2081 g_lf_evt_ocrd_dt,
2082 g_lf_evt_note_dt,
2083 g_ler_id,
2084 g_ler_name,
2085 g_ler_attr_1,
2086 g_ler_attr_2,
2087 g_ler_attr_3,
2088 g_ler_attr_4,
2089 g_ler_attr_5,
2090 g_ler_attr_6,
2091 g_ler_attr_7,
2092 g_ler_attr_8,
2093 g_ler_attr_9,
2094 g_ler_attr_10;
2095 close c_started_ler;
2096 --
2097 if g_debug then
2098 hr_utility.set_location('Exiting'||l_proc, 15);
2099 end if;
2100 --
2101 end get_started_ler_info;
2102 --
2103 -- ----------------------------------------------------------------------------
2104 -- |------< get_bnfts_group_info >----------------------------------------------|
2105 -- ----------------------------------------------------------------------------
2106 --
2107 Procedure get_bnfts_group_info(p_benfts_grp_id in number) is
2108 --
2109 l_proc varchar2(72);
2110 --
2111 cursor c_bnfts_group is
2112 select bgr.name
2113 , bgr.bng_attribute1
2114 , bgr.bng_attribute2
2115 , bgr.bng_attribute3
2116 , bgr.bng_attribute4
2117 , bgr.bng_attribute5
2118 , bgr.bng_attribute6
2119 , bgr.bng_attribute7
2120 , bgr.bng_attribute8
2121 , bgr.bng_attribute9
2122 , bgr.bng_attribute10
2123 from ben_benfts_grp bgr
2124 where bgr.benfts_grp_id = p_benfts_grp_id;
2125 --
2126 Begin
2127 --
2128 if g_debug then
2129 l_proc := g_package||'get_bnfts_group_info';
2130 hr_utility.set_location('Entering'||l_proc, 5);
2131 end if;
2132 --
2133 open c_bnfts_group;
2134 fetch c_bnfts_group into
2135 g_benefit_group,
2136 g_bng_flex_01,
2137 g_bng_flex_02,
2138 g_bng_flex_03,
2139 g_bng_flex_04,
2140 g_bng_flex_05,
2141 g_bng_flex_06,
2142 g_bng_flex_07,
2143 g_bng_flex_08,
2144 g_bng_flex_09,
2145 g_bng_flex_10;
2146 close c_bnfts_group;
2147 --
2148 if g_debug then
2149 hr_utility.set_location('Exiting'||l_proc, 15);
2150 end if;
2151 --
2152 end get_bnfts_group_info;
2153 --
2154 -- ----------------------------------------------------------------------------
2155 -- |------< get_absence_info >----------------------------------------------|
2156 -- ----------------------------------------------------------------------------
2157 --
2158 Procedure get_absence_info(p_person_id in number,
2159 p_effective_date in date) is
2160 --
2161 l_proc varchar2(72);
2162 --
2163 cursor c_absence is
2164 select abs.abs_attendance_reason_id
2165 , abs.absence_attendance_type_id
2166 , abs.date_start
2167 , abs.date_end
2168 , abs.absence_days
2169 , abs.last_update_date
2170 , abs.last_updated_by
2171 , abs.last_update_login
2172 , abs.created_by
2173 , abs.creation_date
2174 , abs.attribute1
2175 , abs.attribute2
2176 , abs.attribute3
2177 , abs.attribute4
2178 , abs.attribute5
2179 , abs.attribute6
2180 , abs.attribute7
2181 , abs.attribute8
2182 , abs.attribute9
2183 , abs.attribute10
2184 from per_absence_attendances abs
2185 where abs.person_id = p_person_id
2186 and p_effective_date between nvl(abs.date_start,p_effective_date)
2187 and nvl(abs.date_end, p_effective_date);
2188
2189 CURSOR abs_cat(p_absence_attendance_type_id NUMBER) IS
2190 SELECT abt.absence_category
2191 , abt.name abs_type
2192 , luk.meaning abs_category
2193 FROM per_absence_attendance_types abt
2194 , hr_lookups luk
2195 WHERE abt.absence_attendance_type_id = p_absence_attendance_type_id
2196 AND abt.absence_category = luk.lookup_code
2197 AND luk.lookup_type = 'ABSENCE_CATEGORY';
2198
2199 --
2200
2201 CURSOR abs_reason(p_abs_attendance_reason_id NUMBER) IS
2202 SELECT lkp.meaning abs_reason ,
2203 abr.name -- Bug 2841958, getting the reason code
2204 FROM per_abs_attendance_reasons abr
2205 , hr_lookups lkp
2206 WHERE abr.abs_attendance_reason_id = p_abs_attendance_reason_id
2207 AND abr.name = lkp.lookup_code
2208 AND lkp.lookup_type = 'ABSENCE_REASON';
2209
2210 --
2211 Begin
2212 --
2213 if g_debug then
2214 l_proc := g_package||'get_absence_info';
2215 hr_utility.set_location('Entering'||l_proc, 5);
2216 hr_utility.set_location('bug 4208'||p_person_id , 4208);
2217 end if;
2218 --
2219
2220 open c_absence;
2221 fetch c_absence into
2222 g_abs_reason
2223 , g_abs_type
2224 , g_abs_start_dt
2225 , g_abs_end_dt
2226 , g_abs_duration
2227 , g_abs_last_update_date
2228 , g_abs_last_updated_by
2229 , g_abs_last_update_login
2230 , g_abs_created_by
2231 , g_abs_creation_date
2232 , g_abs_flex_01
2233 , g_abs_flex_02
2234 , g_abs_flex_03
2235 , g_abs_flex_04
2236 , g_abs_flex_05
2237 , g_abs_flex_06
2238 , g_abs_flex_07
2239 , g_abs_flex_08
2240 , g_abs_flex_09
2241 , g_abs_flex_10;
2242 close c_absence;
2243 --
2244 open abs_cat(g_abs_type);
2245 fetch abs_cat into g_abs_category,g_abs_type_name,g_abs_category_name;
2246 close abs_cat;
2247 --
2248 open abs_reason(g_abs_reason);
2249 fetch abs_reason into g_abs_reason_name ,
2250 g_abs_reason_cd; -- Bug 2841958, extra column in cursor
2251 close abs_reason;
2252 --
2253 if g_debug then
2254 hr_utility.set_location('Exiting'||l_proc, 15);
2255 end if;
2256 --
2257 end get_absence_info;
2258 --
2259 -- ----------------------------------------------------------------------------
2260 -- |------< get_cobra_info >----------------------------------------------|
2261 -- ----------------------------------------------------------------------------
2262 --
2263 Procedure get_cobra_info(p_person_id in number,
2264 p_effective_date in date) is
2265 --
2266 l_proc varchar2(72);
2267 --
2268 cursor cbra_info_c is
2269 select ler.ler_id event_id,
2270 ler.name event_name,
2271 cqb.cbr_elig_perd_strt_dt strt_dt,
2272 cqb.cbr_elig_perd_end_dt end_dt
2273 from ben_cbr_quald_bnf cqb,
2274 ben_cbr_per_in_ler cpl,
2275 ben_ler_f ler,
2276 ben_per_in_ler pil
2277 where
2278 cqb.quald_bnf_person_id = p_person_id
2279 and quald_bnf_flag = 'Y'
2280 and p_effective_date between nvl(cqb.cbr_elig_perd_strt_dt,p_effective_date)
2281 and nvl(cqb.cbr_elig_perd_end_dt,p_effective_date)
2282 and cqb.cbr_quald_bnf_id = cpl.cbr_quald_bnf_id
2283 and cpl.per_in_ler_id = pil.per_in_ler_id
2284 and pil.ler_id = ler.ler_id
2285 and p_effective_date between nvl(ler.effective_start_date,p_effective_date)
2286 and nvl(ler.effective_end_date ,p_effective_date)
2287 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
2288 ;
2289
2290 cursor c_person_type is
2291 SELECT 'x'
2292 FROM per_person_type_usages_f ptu ,
2293 per_person_types ppt
2294 WHERE ptu.person_id = p_person_id
2295 and ptu.person_type_id = ppt.person_type_id
2296 and ppt.system_person_type in ('SRVNG_FMLY_MMBR','SRVNG_SPS')
2297 AND p_effective_date between ptu.effective_start_date and
2298 ptu.effective_end_date;
2299
2300 l_dummy varchar2(1) ;
2301 --
2302 Begin
2303 --
2304 if g_debug then
2305 l_proc := g_package||'get_cobra_info';
2306 hr_utility.set_location('Entering'||l_proc, 5);
2307 end if;
2308 --
2309 -- get cobra information
2310 --
2311 open cbra_info_c;
2312 fetch cbra_info_c into
2313 g_cbra_ler_id,
2314 g_cbra_ler_name,
2315 g_cbra_strt_dt,
2316 g_cbra_end_dt;
2317 if cbra_info_c%found then
2318 g_bnft_stat_cd := 'C';
2319 elsif cbra_info_c%notfound then
2320
2321 g_bnft_stat_cd := 'A';
2322 ---- check whether the person is surviver of prtt
2323 open c_person_type ;
2324 fetch c_person_type into l_dummy ;
2325 if c_person_type%found then
2326 g_bnft_stat_cd := 'S';
2327 end if ;
2328 close c_person_type ;
2329
2330 end if;
2331 close cbra_info_c;
2332 --
2333 if g_debug then
2334 hr_utility.set_location('Exiting'||l_proc, 15);
2335 end if;
2336 --
2337 end get_cobra_info;
2338 --
2339 -- ----------------------------------------------------------------------------
2340 -- |------< get_bnfts_bal_info >----------------------------------------------|
2341 -- ----------------------------------------------------------------------------
2342 --
2343 Procedure get_bnfts_bal_info(p_person_id in number,
2344 p_effective_date in date) is
2345 --
2346 l_proc varchar2(72);
2347 --
2348 cursor c_bnfts_bal (p_bnfts_bal_usg_cd varchar2) is
2349 select sum(val)
2350 from ben_per_bnfts_bal_f a,
2351 ben_bnfts_bal_f b
2352 where a.person_id = p_person_id
2353 and a.bnfts_bal_id = b.bnfts_bal_id
2354 and b.bnfts_bal_usg_cd = p_bnfts_bal_usg_cd
2355 and p_effective_date between nvl(a.effective_start_date,p_effective_date)
2356 and nvl(a.effective_end_date,p_effective_date)
2357 and p_effective_date between nvl(b.effective_start_date,p_effective_date)
2358 and nvl(b.effective_end_date,p_effective_date );
2359 --
2360 Begin
2361 --
2362 if g_debug then
2363 l_proc := g_package||'get_bnfts_bal_info';
2364 hr_utility.set_location('Entering'||l_proc, 5);
2365 end if;
2366 --
2367 -- Vacation
2368 --
2369 IF ben_extract.g_bb4_csr = 'Y' THEN
2370 open c_bnfts_bal('VAC');
2371 fetch c_bnfts_bal into
2372 g_benefit_bal_vacation;
2373 close c_bnfts_bal;
2374 END IF;
2375 --
2376 -- Sick Leave
2377 --
2378 IF ben_extract.g_bb3_csr = 'Y' THEN
2379 open c_bnfts_bal('SCK');
2380 fetch c_bnfts_bal into
2381 g_benefit_bal_sickleave;
2382 close c_bnfts_bal;
2383 END IF;
2384 --
2385 -- Pension
2386 --
2387 IF ben_extract.g_bb2_csr = 'Y' THEN
2388 open c_bnfts_bal('PENBEN');
2389 fetch c_bnfts_bal into
2390 g_benefit_bal_pension;
2391 close c_bnfts_bal;
2392 END IF;
2393 --
2394 -- Defined Contribution
2395 --
2396 IF ben_extract.g_bb1_csr = 'Y' THEN
2397 open c_bnfts_bal('DCBEN');
2398 fetch c_bnfts_bal into
2399 g_benefit_bal_dfncntrbn;
2400 close c_bnfts_bal;
2401 END IF;
2402 --
2403 -- Wellness
2404 --
2405 IF ben_extract.g_bb5_csr = 'Y' THEN
2406 open c_bnfts_bal('WLNS');
2407 fetch c_bnfts_bal into
2408 g_benefit_bal_wellness;
2409 close c_bnfts_bal;
2410 END IF;
2411 --
2412 --
2413 if g_debug then
2414 hr_utility.set_location('Exiting'||l_proc, 15);
2415 end if;
2416 --
2417 end get_bnfts_bal_info;
2418
2419 --- this procedure to avoid the duplication for each
2420 --- extract type
2421
2422 Procedure Extract_person_info(p_person_id in number,
2423 p_effective_date in date, -- passed in from conc mgr
2424 p_business_group_id in number,
2425 p_ext_rslt_id in number
2426 ) IS
2427
2428 l_proc varchar2(72);
2429
2430 begin
2431
2432 g_debug := hr_utility.debug_enabled;
2433 if g_debug then
2434 l_proc := g_package||' Extract_person_info';
2435 hr_utility.set_location('Entering'||l_proc, 5);
2436 end if;
2437
2438
2439 get_person_info (p_person_id => p_person_id,
2440 p_effective_date => g_person_ext_dt);
2441 --
2442 if g_debug then
2443 hr_utility.set_location('asg level ' || ben_extract.g_asg_csr,99);
2444 end if;
2445 if ben_extract.g_asg_csr = 'Y' then
2446 get_assignment_info (p_person_id => p_person_id,
2447 p_assignment_id => g_assignment_id,
2448 p_effective_date => g_person_ext_dt,
2449 p_ext_rslt_id => p_ext_rslt_id );
2450 end if;
2451 --
2452 -- get the primary address for communication too
2453 -- priamry address is used if the cmmunication not avaialable
2454
2455 if ben_extract.g_addr_csr = 'Y' or ben_extract.g_asa_csr = 'Y' or ben_extract.g_cma_csr = 'Y' then
2456 get_primary_address_info (p_person_id => p_person_id,
2457 p_effective_date => g_person_ext_dt);
2458 end if;
2459 --
2460 if ben_extract.g_ma_csr = 'Y' then
2461 get_mailing_address_info (p_person_id => p_person_id,
2462 p_effective_date => g_person_ext_dt);
2463 end if;
2464 ---
2465 if ben_extract.g_cma_csr = 'Y' then
2466 get_comm_address_info(p_person_id => p_person_id,
2467 p_address_id => g_cm_address_id,
2468 p_effective_date => g_person_ext_dt);
2469 end if;
2470
2471 --
2472 if ben_extract.g_phn_csr = 'Y' then
2473 get_phone_info (p_person_id => p_person_id,
2474 p_effective_date => g_person_ext_dt);
2475 end if;
2476 --
2477 if ben_extract.g_pos_csr = 'Y' then
2478 get_period_of_svc_info (p_person_id => p_person_id,
2479 p_effective_date => g_person_ext_dt);
2480 end if;
2481 --
2482 if ben_extract.g_asa_csr = 'Y' then
2483 get_svc_area_info (p_postal_code => g_prim_postal_code,
2484 p_effective_date => g_person_ext_dt);
2485 end if;
2486 --
2487 if ben_extract.g_ler_csr = 'Y' then
2488 get_started_ler_info (p_person_id => p_person_id,
2489 p_effective_date => g_person_ext_dt);
2490 end if;
2491 --
2492 if ben_extract.g_bgr_csr = 'Y' then
2493 get_bnfts_group_info (p_benfts_grp_id => g_benefit_group_id);
2494 end if;
2495 --
2496 if ben_extract.g_abs_csr = 'Y' then
2497 get_absence_info (p_person_id => p_person_id,
2498 p_effective_date => g_person_ext_dt);
2499 end if;
2500 --
2501 if ben_extract.g_cbra_csr = 'Y' then
2502 get_cobra_info (p_person_id => p_person_id,
2503 p_effective_date => g_person_ext_dt);
2504 end if;
2505 --
2506 if ben_extract.g_bb1_csr = 'Y' or ben_extract.g_bb2_csr = 'Y' or ben_extract.g_bb3_csr = 'Y'
2507 or ben_extract.g_bb4_csr = 'Y' or ben_extract.g_bb5_csr = 'Y' then
2508 get_bnfts_bal_info (p_person_id => p_person_id,
2509 p_effective_date => g_person_ext_dt);
2510 end if;
2511 ---
2512
2513 if ben_extract.g_sup_csr = 'Y' then
2514 -- supervisor infor expect supervisor id so if it not intialised
2515 -- intialise again
2516 if nvl(ben_extract.g_asg_csr,'N') <> 'Y' then
2517 get_assignment_info (p_person_id => p_person_id,
2518 p_assignment_id => g_assignment_id,
2519 p_effective_date => g_person_ext_dt,
2520 p_ext_rslt_id => p_ext_rslt_id );
2521 end if;
2522
2523 get_supervisor_info (p_supervisor_id => g_asg_supervisor_id ,
2524 p_effective_date => g_person_ext_dt);
2525 end if;
2526 -- basic salary
2527 if ben_extract.g_bsl_csr = 'Y' then
2528 get_base_annual_salary_info(p_person_id => p_person_id,
2529 p_effective_date => g_person_ext_dt);
2530 end if;
2531 if ben_extract.g_shl_csr = 'Y' then
2532 get_School_info(p_person_id => p_person_id,
2533 p_effective_date => g_person_ext_dt);
2534 end if;
2535
2536 --person level flex provided and used
2537 if ben_extract.g_flxcr_csr = 'Y' then
2538 get_person_flex_credit(p_person_id => p_person_id,
2539 p_effective_date => g_person_ext_dt);
2540 end if ;
2541
2542
2543 --
2544 if g_debug then
2545 hr_utility.set_location('Exiting'||l_proc, 15);
2546 end if;
2547 --
2548
2549 End Extract_person_info ;
2550
2551
2552
2553 --
2554 -- ----------------------------------------------------------------------------
2555 -- |------< process_ext_person >----------------------------------------------|
2556 -- ----------------------------------------------------------------------------
2557 -- This procedure will determine the processing route based on the extract
2558 -- definition for a given person. It will call process_ext_levels to complete
2559 -- all detail records for a given person. It is an open issue whether or not it
2560 -- needs to evaluate inclusion criteria here for Full Profile (Yes for now).
2561 --
2562 Procedure process_ext_person(
2563 p_person_id in number,
2564 p_ext_dfn_id in number,
2565 p_ext_rslt_id in number,
2566 p_ext_file_id in number,
2567 p_ext_crit_prfl_id in number,
2568 p_data_typ_cd in varchar2,
2569 p_ext_typ_cd in varchar2,
2570 p_effective_date in date, -- passed in from conc mgr
2571 p_business_group_id in number,
2572 p_penserv_mode in varchar2 --vkodedal changes for penserver - 30-apr-2008
2573 ) IS
2574 --
2575 l_proc varchar2(72);
2576 --
2577 l_include varchar2(1);
2578 l_dummy_start_date date;
2579 l_dummy_end_date date;
2580 l_chg_actl_strt_dt date;
2581 l_chg_actl_end_dt date;
2582 l_chg_eff_strt_dt date;
2583 l_chg_eff_end_dt date;
2584 l_to_be_sent_strt_dt date;
2585 l_to_be_sent_end_dt date;
2586 l_person_ext_dt date;
2587 l_benefits_ext_dt date;
2588 --
2589 cursor c_changes_only_extract
2590 (p_chg_actl_strt_dt in date,
2591 p_chg_actl_end_dt in date,
2592 p_chg_eff_strt_dt in date,
2593 p_chg_eff_end_dt in date)
2594 is
2595 select a.ext_chg_evt_log_id
2596 , a.chg_evt_cd
2597 , a.chg_eff_dt
2598 , trunc(a.chg_actl_dt)
2599 , a.last_update_login
2600 , a.prmtr_01
2601 , a.prmtr_02
2602 , a.prmtr_03
2603 , a.prmtr_04
2604 , a.prmtr_05
2605 , a.prmtr_06
2606 , a.old_val1
2607 , a.old_val2
2608 , a.old_val3
2609 , a.old_val4
2610 , a.old_val5
2611 , a.old_val6
2612 , a.new_val1
2613 , a.new_val2
2614 , a.new_val3
2615 , a.new_val4
2616 , a.new_val5
2617 , a.new_val6
2618 , 'BEN' chg_evt_source
2619 from ben_ext_chg_evt_log a
2620 where
2621 a.person_id = p_person_id
2622 and trunc(a.chg_actl_dt) between nvl(p_chg_actl_strt_dt, hr_api.g_sot)
2623 and nvl(p_chg_actl_end_dt, hr_api.g_eot)
2624 and a.chg_eff_dt between nvl(p_chg_eff_strt_dt, hr_api.g_sot)
2625 and nvl(p_chg_eff_end_dt, hr_api.g_eot)
2626 order by a.chg_eff_dt;
2627
2628
2629
2630 cursor c_chg_pay_evt is
2631 select xcv.val_1 event_group_id
2632 from ben_ext_crit_typ xct
2633 ,ben_ext_crit_val xcv
2634 where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
2635 and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
2636 and xct.CRIT_TYP_CD = 'CPE'
2637 ;
2638
2639
2640 l_pay_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
2641 l_pay_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
2642 l_pay_detail_tab pay_interpreter_pkg.t_detailed_output_table_type;
2643 l_pay_pro_type_tab pay_interpreter_pkg.t_proration_type_table_type;
2644 l_dated_table_id pay_event_updates.dated_table_id%type ;
2645 l_pay_Assignment_id number ;
2646
2647 l_pay_detail_tot_tab t_detailed_output_table;
2648 l_pay_tot_Srno number ;
2649 l_pay_evt_srno number ;
2650 l_g_c_found varchar2(1) ;
2651 cursor c_pay_chg_tbl ( p_dated_table_id number) is
2652 select table_name
2653 from pay_dated_tables
2654 where dated_table_id = p_dated_table_id
2655 ;
2656
2657 --
2658 /*
2659 cursor c_communication_extract
2660 (p_to_be_sent_strt_dt in date,
2661 p_to_be_sent_end_dt in date)
2662 is
2663 select e.name
2664 , e.cm_typ_id
2665 , e.shrt_name
2666 , e.pc_kit_cd
2667 , a.per_cm_id
2668 , a.per_in_ler_id
2669 , a.prtt_enrt_actn_id
2670 , nvl(b.effective_start_date,a.effective_start_date) effective_start_date
2671 , d.proc_cd
2672 , b.to_be_sent_dt
2673 , b.sent_dt
2674 , a.last_update_date
2675 , b.last_update_date
2676 , b.dlvry_instn_txt
2677 , b.inspn_rqd_flag
2678 , b.address_id
2679 , b.per_cm_prvdd_id
2680 , b.object_version_number
2681 , b.effective_start_date
2682 , c.effective_start_date
2683 , l.ler_id
2684 , l.name
2685 , p.per_in_ler_stat_cd
2686 , nvl(p.lf_evt_ocrd_dt,a.effective_start_date) lf_evt_ocrd_dt
2687 , nvl(p.ntfn_dt,a.effective_start_date) ntfn_dt
2688 from ben_per_cm_f a,
2689 ben_per_cm_prvdd_f b,
2690 ben_per_cm_trgr_f c,
2691 ben_cm_trgr d,
2692 ben_cm_typ_f e,
2693 ben_per_in_ler p,
2694 ben_ler_f l
2695 where
2696 a.person_id = p_person_id
2697 and a.per_cm_id = b.per_cm_id
2698 and a.cm_typ_id = e.cm_typ_id
2699 and a.per_cm_id = c.per_cm_id(+)
2700 and c.cm_trgr_id = d.cm_trgr_id(+)
2701 and a.per_in_ler_id = p.per_in_ler_id(+)
2702 and p.ler_id = l.ler_id(+)
2703 and b.per_cm_prvdd_stat_cd = 'ACTIVE' -- this should be inclusion criteria.
2704 -- the following line of code was put here for performance.
2705 and nvl(b.to_be_sent_dt,hr_api.g_sot) between nvl(p_to_be_sent_strt_dt, hr_api.g_sot)
2706 and nvl(p_to_be_sent_end_dt, hr_api.g_eot)
2707 and p_effective_date between b.effective_start_date
2708 and b.effective_end_date
2709 and b.effective_start_date between a.effective_start_date
2710 and a.effective_end_date
2711 and b.effective_start_date
2712 between nvl(c.effective_start_date,b.effective_start_date)
2713 and nvl(c.effective_end_date,b.effective_start_date)
2714 and b.effective_start_date between e.effective_start_date
2715 and e.effective_end_date
2716 and b.effective_start_date
2717 between nvl(l.effective_start_date,b.effective_start_date)
2718 and nvl(l.effective_end_date,b.effective_start_date)
2719 order by b.to_be_sent_dt , b.per_cm_prvdd_id;
2720 */
2721
2722
2723 l_per_cm_id_va t_number ;
2724 l_per_in_ler_id_va t_number ;
2725 l_prtt_enrt_actn_id_va t_number ;
2726 l_effective_start_date_va t_date ;
2727 l_per_cm_eff_start_date_va t_date ;
2728 l_to_be_sent_dt_va t_date ;
2729 l_sent_dt_va t_date ;
2730 l_per_cm_last_update_date_va t_date ;
2731 l_last_update_date_va t_date ;
2732 l_dlvry_instn_txt_va t_varchar2_600 ;
2733 l_inspn_rqd_flag_va t_varchar2_30 ;
2734 l_address_id_va t_number ;
2735 l_per_cm_prvdd_id_va t_number ;
2736 l_object_version_number_va t_number ;
2737 l_cm_typ_id_va t_number ;
2738
2739
2740 cursor c_communication_extract
2741 (p_to_be_sent_strt_dt in date,
2742 p_to_be_sent_end_dt in date)
2743 is
2744 select a.per_cm_id
2745 , a.per_in_ler_id
2746 , a.prtt_enrt_actn_id
2747 , b.effective_start_date
2748 , a.effective_start_date per_cm_eff_start_date
2749 , b.to_be_sent_dt
2750 , b.sent_dt
2751 , a.last_update_date per_cm_last_update_date
2752 , b.last_update_date
2753 , b.dlvry_instn_txt
2754 , b.inspn_rqd_flag
2755 , b.address_id
2756 , b.per_cm_prvdd_id
2757 , b.object_version_number
2758 , a.cm_typ_id
2759 from ben_per_cm_f a,
2760 ben_per_cm_prvdd_f b
2761 where
2762 a.person_id = p_person_id
2763 and a.per_cm_id = b.per_cm_id
2764 and b.per_cm_prvdd_stat_cd = 'ACTIVE' -- this should be inclusion criteria.
2765 -- the following line of code was put here for performance.
2766 and nvl(b.to_be_sent_dt,hr_api.g_sot) between nvl(p_to_be_sent_strt_dt, hr_api.g_sot)
2767 and nvl(p_to_be_sent_end_dt, hr_api.g_eot)
2768 and p_effective_date between b.effective_start_date
2769 and b.effective_end_date
2770 and b.effective_start_date between a.effective_start_date
2771 and a.effective_end_date
2772 order by b.to_be_sent_dt , b.per_cm_prvdd_id;
2773
2774
2775 cursor c_per_comm_trigger
2776 (p_per_cm_id in number,
2777 p_effective_date in date
2778 ) is
2779 select c.effective_start_date ,
2780 c.cm_trgr_id
2781 from ben_per_cm_trgr_f c
2782 where p_per_cm_id = c.per_cm_id
2783 and p_effective_date
2784 between c.effective_start_date and c.effective_end_date
2785 ;
2786
2787
2788
2789 cursor c_comm_trgr (
2790 p_cm_trgr_id in number
2791 ) is
2792 select d.proc_cd
2793 from ben_cm_trgr d
2794 where p_cm_trgr_id = d.cm_trgr_id ;
2795
2796
2797 cursor c_comm_typ (
2798 p_cm_typ_id in number ,
2799 p_effective_date in date
2800 ) is
2801 select e.name
2802 , e.shrt_name
2803 , e.pc_kit_cd
2804 from ben_cm_typ_f e
2805 where p_cm_typ_id = e.cm_typ_id
2806 and p_effective_date between e.effective_start_date
2807 and e.effective_end_date ;
2808
2809
2810 cursor c_pil ( p_per_in_ler_id number ,
2811 p_effective_date in date
2812 ) is
2813 select l.ler_id
2814 ,l.name
2815 ,p.per_in_ler_stat_cd
2816 ,p.lf_evt_ocrd_dt
2817 ,p.ntfn_dt
2818 from ben_per_in_ler p,
2819 ben_ler_f l
2820 where p_per_in_ler_id = p.per_in_ler_id
2821 and p.ler_id = l.ler_id
2822 and p_effective_date
2823 between l.effective_start_date and l.effective_end_date
2824 ;
2825
2826 l_cm_trgr_id ben_per_cm_trgr_f.cm_trgr_id%type ;
2827 l_last_per_cm_prvdd_id number:=null;
2828 l_err_message fnd_new_messages.message_text%type ;
2829 --
2830 /* Start of Changes for WWBUG: 2008949: added cursor */
2831 cursor c_chg_penid(p_element_entry_id number,
2832 p_effective_date date) is
2833 select ee.creator_id
2834 from pay_element_entries_f ee
2835 where ee.element_entry_id = p_element_entry_id
2836 and p_effective_date between ee.effective_start_date and ee.effective_end_date;
2837 /* End of Changes for WWBUG: 2008949: added cursor */
2838 -- CWB
2839
2840 cursor c_cwb_extract is
2841 select cpi.GROUP_PER_IN_LER_ID
2842 ,cpi.ASSIGNMENT_ID
2843 ,cpi.PERSON_ID
2844 ,cpi.SUPERVISOR_ID
2845 ,cpi.EFFECTIVE_DATE
2846 ,cpi.FULL_NAME
2847 ,cpi.BRIEF_NAME
2848 ,cpi.CUSTOM_NAME
2849 ,cpi.SUPERVISOR_FULL_NAME
2850 ,cpi.SUPERVISOR_BRIEF_NAME
2851 ,cpi.SUPERVISOR_CUSTOM_NAME
2852 ,cpi.LEGISLATION_CODE
2853 ,cpi.YEARS_EMPLOYED
2854 ,cpi.YEARS_IN_JOB
2855 ,cpi.YEARS_IN_POSITION
2856 ,cpi.YEARS_IN_GRADE
2857 ,cpi.EMPLOYEE_NUMBER
2858 ,cpi.START_DATE
2859 ,cpi.ORIGINAL_START_DATE
2860 ,cpi.ADJUSTED_SVC_DATE
2861 ,cpi.BASE_SALARY
2862 ,cpi.BASE_SALARY_CHANGE_DATE
2863 ,cpi.PAYROLL_NAME
2864 ,cpi.PERFORMANCE_RATING
2865 ,cpi.PERFORMANCE_RATING_TYPE
2866 ,cpi.PERFORMANCE_RATING_DATE
2867 ,cpi.BUSINESS_GROUP_ID
2868 ,cpi.ORGANIZATION_ID
2869 ,cpi.JOB_ID
2870 ,cpi.GRADE_ID
2871 ,cpi.POSITION_ID
2872 ,cpi.PEOPLE_GROUP_ID
2873 ,cpi.SOFT_CODING_KEYFLEX_ID
2874 ,cpi.LOCATION_ID
2875 ,cpi.PAY_RATE_ID
2876 ,cpi.ASSIGNMENT_STATUS_TYPE_ID
2877 ,cpi.FREQUENCY
2878 ,cpi.GRADE_ANNULIZATION_FACTOR
2879 ,cpi.PAY_ANNULIZATION_FACTOR
2880 ,cpi.GRD_MIN_VAL
2881 ,cpi.GRD_MAX_VAL
2882 ,cpi.GRD_MID_POINT
2883 ,cpi.GRD_QUARTILE
2884 ,cpi.GRD_COMPARATIO
2885 ,cpi.EMP_CATEGORY
2886 ,cpi.CHANGE_REASON
2887 ,cpi.NORMAL_HOURS
2888 ,cpi.EMAIL_ADDRESS
2889 ,cpi.BASE_SALARY_FREQUENCY
2890 ,cpi.NEW_ASSGN_OVN
2891 ,cpi.NEW_PERF_EVENT_ID
2892 ,cpi.NEW_PERF_REVIEW_ID
2893 ,cpi.POST_PROCESS_STAT_CD
2894 ,cpi.FEEDBACK_RATING
2895 ,cpi.OBJECT_VERSION_NUMBER
2896 ,cpi.CUSTOM_SEGMENT1
2897 ,cpi.CUSTOM_SEGMENT2
2898 ,cpi.CUSTOM_SEGMENT3
2899 ,cpi.CUSTOM_SEGMENT4
2900 ,cpi.CUSTOM_SEGMENT5
2901 ,cpi.CUSTOM_SEGMENT6
2902 ,cpi.CUSTOM_SEGMENT7
2903 ,cpi.CUSTOM_SEGMENT8
2904 ,cpi.CUSTOM_SEGMENT9
2905 ,cpi.CUSTOM_SEGMENT10
2906 ,cpi.CUSTOM_SEGMENT11
2907 ,cpi.CUSTOM_SEGMENT12
2908 ,cpi.CUSTOM_SEGMENT13
2909 ,cpi.CUSTOM_SEGMENT14
2910 ,cpi.CUSTOM_SEGMENT15
2911 ,cpi.PEOPLE_GROUP_NAME
2912 ,cpi.PEOPLE_GROUP_SEGMENT1
2913 ,cpi.PEOPLE_GROUP_SEGMENT2
2914 ,cpi.PEOPLE_GROUP_SEGMENT3
2915 ,cpi.PEOPLE_GROUP_SEGMENT4
2916 ,cpi.PEOPLE_GROUP_SEGMENT5
2917 ,cpi.PEOPLE_GROUP_SEGMENT6
2918 ,cpi.PEOPLE_GROUP_SEGMENT7
2919 ,cpi.PEOPLE_GROUP_SEGMENT8
2920 ,cpi.PEOPLE_GROUP_SEGMENT9
2921 ,cpi.PEOPLE_GROUP_SEGMENT10
2922 ,cpi.PEOPLE_GROUP_SEGMENT11
2923 ,cpi.ASS_ATTRIBUTE_CATEGORY
2924 ,cpi.ASS_ATTRIBUTE1
2925 ,cpi.ASS_ATTRIBUTE2
2926 ,cpi.ASS_ATTRIBUTE3
2927 ,cpi.ASS_ATTRIBUTE4
2928 ,cpi.ASS_ATTRIBUTE5
2929 ,cpi.ASS_ATTRIBUTE6
2930 ,cpi.ASS_ATTRIBUTE7
2931 ,cpi.ASS_ATTRIBUTE8
2932 ,cpi.ASS_ATTRIBUTE9
2933 ,cpi.ASS_ATTRIBUTE10
2934 ,cpi.ASS_ATTRIBUTE11
2935 ,cpi.ASS_ATTRIBUTE12
2936 ,cpi.ASS_ATTRIBUTE13
2937 ,cpi.ASS_ATTRIBUTE14
2938 ,cpi.ASS_ATTRIBUTE15
2939 ,cpi.ASS_ATTRIBUTE16
2940 ,cpi.ASS_ATTRIBUTE17
2941 ,cpi.ASS_ATTRIBUTE18
2942 ,cpi.ASS_ATTRIBUTE19
2943 ,cpi.ASS_ATTRIBUTE20
2944 ,cpi.ASS_ATTRIBUTE21
2945 ,cpi.ASS_ATTRIBUTE22
2946 ,cpi.ASS_ATTRIBUTE23
2947 ,cpi.ASS_ATTRIBUTE24
2948 ,cpi.ASS_ATTRIBUTE25
2949 ,cpi.ASS_ATTRIBUTE26
2950 ,cpi.ASS_ATTRIBUTE27
2951 ,cpi.ASS_ATTRIBUTE28
2952 ,cpi.ASS_ATTRIBUTE29
2953 ,cpi.ASS_ATTRIBUTE30
2954 ,cpi.CPI_ATTRIBUTE_CATEGORY
2955 ,cpi.CPI_ATTRIBUTE1
2956 ,cpi.CPI_ATTRIBUTE2
2957 ,cpi.CPI_ATTRIBUTE3
2958 ,cpi.CPI_ATTRIBUTE4
2959 ,cpi.CPI_ATTRIBUTE5
2960 ,cpi.CPI_ATTRIBUTE6
2961 ,cpi.CPI_ATTRIBUTE7
2962 ,cpi.CPI_ATTRIBUTE8
2963 ,cpi.CPI_ATTRIBUTE9
2964 ,cpi.CPI_ATTRIBUTE10
2965 ,cpi.CPI_ATTRIBUTE11
2966 ,cpi.CPI_ATTRIBUTE12
2967 ,cpi.CPI_ATTRIBUTE13
2968 ,cpi.CPI_ATTRIBUTE14
2969 ,cpi.CPI_ATTRIBUTE15
2970 ,cpi.CPI_ATTRIBUTE16
2971 ,cpi.CPI_ATTRIBUTE17
2972 ,cpi.CPI_ATTRIBUTE18
2973 ,cpi.CPI_ATTRIBUTE19
2974 ,cpi.CPI_ATTRIBUTE20
2975 ,cpi.CPI_ATTRIBUTE21
2976 ,cpi.CPI_ATTRIBUTE22
2977 ,cpi.CPI_ATTRIBUTE23
2978 ,cpi.CPI_ATTRIBUTE24
2979 ,cpi.CPI_ATTRIBUTE25
2980 ,cpi.CPI_ATTRIBUTE26
2981 ,cpi.CPI_ATTRIBUTE27
2982 ,cpi.CPI_ATTRIBUTE28
2983 ,cpi.CPI_ATTRIBUTE29
2984 ,cpi.CPI_ATTRIBUTE30
2985 ,cpi.LAST_UPDATE_DATE
2986 ,cpi.LAST_UPDATED_BY
2987 ,cpi.LAST_UPDATE_LOGIN
2988 ,cpi.CREATED_BY
2989 ,cpi.CREATION_DATE
2990 ,cpi.FEEDBACK_DATE
2991 ,pil.lf_evt_ocrd_dt
2992 ,pil.group_pl_id
2993 ,pil.PER_IN_LER_STAT_CD
2994 ,ler.name LER_NAME
2995 ,pl.name group_pl_name
2996 ,pl.PERF_REVW_STRT_DT
2997 ,pl.EMP_INTERVIEW_TYP_CD
2998 ,pl.ASG_UPDT_EFF_DATE
2999 from ben_cwb_person_info cpi ,
3000 ben_per_in_ler pil ,
3001 ben_ler_f ler ,
3002 ben_cwb_pl_dsgn pl
3003 where cpi.person_id = p_person_id
3004 and cpi.group_per_in_ler_id = pil.per_in_ler_id
3005 and pil.ler_id = ler.ler_id
3006 and pil.group_pl_id = pl.pl_id
3007 and pl.oipl_id = -1
3008 and pil.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
3009 and cpi.effective_date
3010 between ler.effective_start_date and ler.effective_end_date
3011 ;
3012
3013
3014 cursor c_bg_name(p_business_group_id number) is
3015 select name
3016 from per_business_groups_perf bg
3017 where business_group_id = p_business_group_id ;
3018
3019 cursor c_org_name(p_org_id number) is
3020 select name
3021 from hr_all_organization_units_vl org
3022 where org.organization_id = p_org_id ;
3023
3024 cursor c_pos (p_pos_id number) is
3025 select name
3026 from per_positions
3027 where position_id = p_pos_id
3028 ;
3029
3030 cursor c_job(p_job_id number) is
3031 select name
3032 from per_jobs_vl
3033 where job_id = p_job_id
3034 ;
3035
3036 cursor c_grade(p_grade_id number) is
3037 select name
3038 from per_grades_vl
3039 where grade_id = p_grade_id
3040 ;
3041
3042 cursor c_loc(p_loc_id number) is
3043 select location_code
3044 from hr_locations_all
3045 where location_id = p_loc_id
3046 ;
3047
3048 cursor c_payr(p_rate_id number) is
3049 select name
3050 from pay_rates
3051 where rate_id = p_rate_id
3052 ;
3053
3054 cursor c_pln(p_pl_id number , p_dt date ) is
3055 select name
3056 from ben_cwb_pl_dsgn pl
3057 where p_pl_id = pl.PL_ID
3058 and pl.oipl_id = -1
3059 and p_dt = pl.lf_evt_ocrd_dt
3060 ;
3061
3062
3063 cursor c_groups (p_grp_id number) is
3064 select group_name
3065 from pay_people_groups
3066 where PEOPLE_GROUP_ID = p_grp_id
3067 ;
3068
3069 cursor c_asg_status (p_asg_stat_id number) is
3070 select user_status
3071 from PER_ASSIGNMENT_STATUS_TYPES
3072 where ASSIGNMENT_STATUS_TYPE_ID = p_asg_stat_id
3073 ;
3074
3075
3076
3077 cursor c_hr_lkup(p_lkup_type varchar2,
3078 p_lkup_code varchar2) is
3079 select meaning
3080 from hr_lookups
3081 where lookup_type = p_lkup_type
3082 and lookup_code = p_lkup_code
3083 ;
3084
3085 cursor c_tran (p_trn_id number ,
3086 p_trn_type varchar2) is
3087 select ATTRIBUTE3,
3088 ATTRIBUTE5,
3089 ATTRIBUTE6,
3090 ATTRIBUTE7,
3091 ATTRIBUTE8
3092 from ben_transaction
3093 where TRANSACTION_ID = p_trn_id
3094 and TRANSACTION_TYPE = p_trn_type
3095 ;
3096
3097 --- to get all the primary assignment within the period
3098
3099 cursor c_pay_p_asg (p_person_id number ,
3100 p_from_date date ,
3101 p_to_date date ) is
3102 select distinct assignment_id
3103 from per_all_assignments_f
3104 where person_id = p_person_id
3105 and ( primary_flag = 'Y' OR ASSIGNMENT_TYPE ='A' ) -- vkodedal fix for 6798915
3106 and effective_start_date <= p_to_date and
3107 effective_end_date >= p_from_date
3108 ;
3109
3110 -- to get th last date of the assignment to
3111 -- validate the assgnmnet against type
3112 cursor c_pay_asg_date (p_Assignment_id number ) is
3113 select effective_start_date
3114 from per_all_assignments_f
3115 where Assignment_id = p_Assignment_id
3116 and ( primary_flag = 'Y' OR ASSIGNMENT_TYPE ='A' ) -- vkodedal fix for 6798915
3117 order by effective_start_date desc ;
3118 l_pay_asg_eff_date date ;
3119 l_tran c_tran%rowtype ;
3120 l_eff_event_scount number ;
3121 l_eff_event_ecount number ;
3122
3123
3124
3125 Begin
3126 --
3127 g_debug := hr_utility.debug_enabled;
3128 if g_debug then
3129 l_proc := g_package||'process_ext_person';
3130 hr_utility.set_location('Entering'||l_proc, 5);
3131 end if;
3132
3133 --
3134 -- Get general extract info
3135 --
3136 g_business_group_id := p_business_group_id;
3137 g_effective_date := p_effective_date;
3138 --
3139 g_person_id := p_person_id;
3140
3141 --
3142 SAVEPOINT cur_transaction;
3143 -- --------------------------------------------------
3144 -- Full Profile Extract
3145 -- --------------------------------------------------
3146 IF p_data_typ_cd = 'F' THEN
3147 --
3148 init_detail_globals;
3149 --
3150 ben_ext_util.get_ext_dates
3151 (p_ext_dfn_id => p_ext_dfn_id,
3152 p_data_typ_cd => p_data_typ_cd,
3153 p_effective_date => p_effective_date,
3154 p_person_ext_dt => l_person_ext_dt, --out
3155 p_benefits_ext_dt => l_benefits_ext_dt); -- out
3156 --
3157 g_person_ext_dt := l_person_ext_dt;
3158 g_benefits_ext_dt := l_benefits_ext_dt;
3159 --
3160 g_rcd_seq := 1;
3161 --
3162 g_trans_num := 1;
3163
3164
3165 Extract_person_info(p_person_id => p_person_id,
3166 p_effective_date => p_effective_date, -- passed in from conc mgr
3167 p_business_group_id => p_business_group_id,
3168 p_ext_rslt_id => p_ext_rslt_id
3169 ) ;
3170 --
3171 l_include := 'Y';
3172 --
3173 if p_ext_crit_prfl_id is not null THEN
3174 --
3175 ben_ext_evaluate_inclusion.Evaluate_Person_Incl
3176 (p_person_id => p_person_id,
3177 p_postal_code => g_prim_postal_code,
3178 p_org_id => g_employee_organization_id,
3179 p_loc_id => g_location_id,
3180 p_gre => null, -- this will be fetched in called program.
3181 p_state => g_prim_state,
3182 p_bnft_grp => g_benefit_group_id,
3183 p_ee_status => g_employee_status_id,
3184 p_chg_evt_cd => null,
3185 p_effective_date => g_person_ext_dt,
3186 p_actl_date => null,
3187 p_include => l_include);
3188 --
3189 end if;
3190 --
3191 if l_include = 'Y' then
3192 --
3193 if nvl(ben_extract.g_spcl_hndl_flag,'X') <> 'Y' then -- normal processing
3194
3195 process_ext_levels(
3196 p_person_id => p_person_id,
3197 p_ext_rslt_id => p_ext_rslt_id,
3198 p_ext_file_id => p_ext_file_id,
3199 p_data_typ_cd => p_data_typ_cd,
3200 p_ext_typ_cd => p_ext_typ_cd,
3201 p_business_group_id => p_business_group_id,
3202 p_effective_date => g_effective_date
3203 );
3204 else -- special handling flag tells us that it is an ansi 834 extract.
3205 --
3206 ben_ext_ansi.main(
3207 p_person_id => p_person_id,
3208 p_ext_rslt_id => p_ext_rslt_id,
3209 p_ext_file_id => p_ext_file_id,
3210 p_data_typ_cd => p_data_typ_cd,
3211 p_ext_typ_cd => p_ext_typ_cd,
3212 p_ext_crit_prfl_id => p_ext_crit_prfl_id,
3213 p_business_group_id => p_business_group_id,
3214 p_effective_date => g_benefits_ext_dt
3215 );
3216 end if;
3217 --
3218 end if; -- l_include = 'Y'
3219 --
3220 -- ==========================================
3221 -- Changes Only Extract
3222 -- ==========================================
3223 ELSIF p_data_typ_cd = 'C' THEN
3224 --
3225 g_trans_num := 1;
3226 --
3227 ben_ext_util.get_chg_dates
3228 (p_ext_dfn_id => ben_extract.g_ext_dfn_id, --in
3229 p_effective_date => g_effective_date, --in
3230 p_chg_actl_strt_dt => l_chg_actl_strt_dt, --out
3231 p_chg_actl_end_dt => l_chg_actl_end_dt, --out
3232 p_chg_eff_strt_dt => l_chg_eff_strt_dt, --out
3233 p_chg_eff_end_dt => l_chg_eff_end_dt); --out
3234
3235 -- if the parameter passed from extract , then overide the criteria dates
3236 if ben_ext_thread.g_effective_start_date is not null then
3237 l_chg_eff_strt_dt := ben_ext_thread.g_effective_start_date ;
3238 l_chg_eff_end_dt := ben_ext_thread.g_effective_end_date ;
3239 end if ;
3240
3241 if ben_ext_thread.g_actual_start_date is not null then
3242 l_chg_actl_strt_dt := ben_ext_thread.g_actual_start_date ;
3243 l_chg_actl_end_dt := ben_ext_thread.g_actual_end_date ;
3244 end if ;
3245
3246 hr_utility.set_location( 'chg actl date ' || l_chg_actl_strt_dt || ' / ' || l_chg_actl_end_dt, 99 );
3247 hr_utility.set_location( 'chg eff date ' || l_chg_eff_strt_dt || ' / ' || l_chg_eff_end_dt , 99 );
3248 --
3249 if ben_ext_thread.g_chg_ext_from_ben = 'Y' then
3250 hr_utility.set_location( ' extract chg evt log included ' , 99 );
3251 open c_changes_only_extract (l_chg_actl_strt_dt,
3252 l_chg_actl_end_dt,
3253 l_chg_eff_strt_dt,
3254 l_chg_eff_end_dt);
3255 LOOP
3256 --
3257 init_detail_globals;
3258 --
3259 FETCH c_changes_only_extract into
3260 --
3261 g_ext_chg_evt_log_id,
3262 g_chg_evt_cd,
3263 g_chg_eff_dt,
3264 g_chg_actl_dt,
3265 g_chg_last_update_login,
3266 g_chg_prmtr_01,
3267 g_chg_prmtr_02,
3268 g_chg_prmtr_03,
3269 g_chg_prmtr_04,
3270 g_chg_prmtr_05,
3271 g_chg_prmtr_06,
3272 g_chg_old_val1,
3273 g_chg_old_val2,
3274 g_chg_old_val3,
3275 g_chg_old_val4,
3276 g_chg_old_val5,
3277 g_chg_old_val6,
3278 g_chg_new_val1,
3279 g_chg_new_val2,
3280 g_chg_new_val3,
3281 g_chg_new_val4,
3282 g_chg_new_val5,
3283 g_chg_new_val6,
3284 g_chg_evt_source
3285 ;
3286 --
3287 EXIT WHEN c_changes_only_extract%NOTFOUND;
3288 --
3289 --g_extract_date := g_chg_eff_dt;
3290 --
3291 ben_ext_util.get_ext_dates
3292 (p_ext_dfn_id => p_ext_dfn_id,
3293 p_data_typ_cd => p_data_typ_cd,
3294 p_effective_date => g_effective_date,
3295 p_person_ext_dt => l_person_ext_dt, --out
3296 p_benefits_ext_dt => l_benefits_ext_dt); -- out
3297 --
3298 g_person_ext_dt := l_person_ext_dt;
3299 g_benefits_ext_dt := l_benefits_ext_dt;
3300 --
3301 l_include := 'Y';
3302 --
3303 if p_ext_crit_prfl_id is not null THEN
3304 --
3305 ben_ext_evaluate_inclusion.evaluate_change_log_incl
3306 (p_chg_evt_cd => g_chg_evt_cd,
3307 p_chg_evt_source => g_chg_evt_source,
3308 p_chg_eff_dt => g_chg_eff_dt,
3309 p_chg_actl_dt => g_chg_actl_dt,
3310 p_last_update_login => g_chg_last_update_login,
3311 p_effective_date => g_effective_date,
3312 p_include => l_include);
3313 --
3314 end if; -- p_ext_crit_prfl_id is not null
3315 --
3316 if l_include = 'Y' then
3317 --
3318
3319 Extract_person_info(p_person_id => p_person_id,
3320 p_effective_date => p_effective_date, -- passed in from conc mgr
3321 p_business_group_id => p_business_group_id,
3322 p_ext_rslt_id => p_ext_rslt_id
3323 ) ;
3324 --
3325 if p_ext_crit_prfl_id is not null THEN
3326 --
3327 ben_ext_evaluate_inclusion.Evaluate_Person_Incl
3328 (p_person_id => p_person_id,
3329 p_postal_code => g_prim_postal_code,
3330 p_org_id => g_employee_organization_id,
3331 p_loc_id => g_location_id,
3332 p_gre => null, -- this will be fetched in called program.
3333 p_state => g_prim_state,
3334 p_bnft_grp => g_benefit_group_id,
3335 p_ee_status => g_employee_status_id,
3336 p_chg_evt_cd => g_chg_evt_cd,
3337 p_chg_evt_source => g_chg_evt_source,
3338 p_effective_date => g_person_ext_dt,
3339 --RCHASE
3340 p_eff_date => g_chg_eff_dt,
3341 --End RCHASE
3342 p_actl_date => g_chg_actl_dt,
3343 p_include => l_include);
3344 --
3345 end if; -- p_ext_crit_prfl_id is not null
3346 --
3347 end if; -- l_include = 'Y'
3348 --
3349 IF l_include = 'Y' THEN
3350 --
3351 -- Not really sure what this hard coding is all about, should be investigated. th.
3352 --
3353 if g_debug then
3354 hr_utility.set_location(' Change Event Code ' || g_chg_evt_cd , 99 );
3355 end if;
3356 --BBurns Bug 1745274. Set context for AD and DD also on line below.
3357 /*
3358 CODE PRIOR TO WWBUG: 2008949
3359 if g_chg_evt_cd in ('AB', 'AD', 'DD', 'RB', 'TBAC', 'TBBC', 'UOBO', 'CCSD', 'CCED') then
3360 */
3361 /* Start of Changes for WWBUG: 2008949 added COECA */
3362 if g_chg_evt_cd in ('AB', 'AD', 'DD', 'RB', 'TBAC',
3363 'TBBC', 'UOBO', 'CCSD', 'CCED', 'COECA') then
3364 /* End of Changes for WWBUG: 2008949 added COECA */
3365 --
3366 g_chg_pl_id := g_chg_prmtr_01;
3367 g_chg_enrt_rslt_id := g_chg_prmtr_03;
3368 --
3369 elsif g_chg_evt_cd in ('DEE', 'AEE', 'UEE') then
3370 --
3371 g_chg_input_value_id := to_number(g_chg_prmtr_02);
3372 --
3373 /* Start of Changes for WWBUG: 2008949: addition */
3374 --
3375 g_chg_enrt_rslt_id := to_number(g_chg_prmtr_03);
3376
3377 if g_chg_enrt_rslt_id is null
3378 then
3379 --
3380 --Fetch the prtt_enrt_rslt_id. This will be the only enrollment link
3381 --between the chg_evt_log and ben_prtt_enrt_rslt_id
3382 --
3383 open c_chg_penid(p_element_entry_id => to_number(g_chg_prmtr_01),
3384 p_effective_date => g_chg_eff_dt);
3385 fetch c_chg_penid into g_chg_enrt_rslt_id;
3386 if c_chg_penid%notfound
3387 then
3388 --we do not have a link between the chg_evt and an
3389 --enrollment.
3390 g_chg_enrt_rslt_id := null;
3391 end if;
3392 close c_chg_penid;
3393 end if;
3394 /* End of Changes for WWBUG: 2008949: addition */
3395 end if;
3396 --
3397 -- get change log information
3398 --
3399 IF g_chg_evt_cd in ( 'CON', 'COUN') THEN
3400 --
3401 if g_chg_old_val5 is not null then
3402 g_previous_last_name := g_chg_old_val5; -- needs fixing.
3403 g_previous_first_name := g_chg_old_val3;
3404 g_previous_middle_name := g_chg_old_val4;
3405 g_previous_suffix := g_chg_old_val6;
3406 end if ;
3407
3408 if g_debug then
3409 hr_utility.set_location(' l name ' || g_previous_last_name , 99 );
3410 hr_utility.set_location(' f name ' || g_previous_first_name , 99 );
3411 hr_utility.set_location(' m name ' || g_previous_middle_name , 99 );
3412 end if;
3413
3414 ELSIF g_chg_evt_cd = 'CONS' THEN
3415 g_previous_prefix := g_chg_old_val1 ;
3416 --
3417 ELSIF g_chg_evt_cd = 'COSS' THEN
3418 --
3419 g_previous_ssn := g_chg_old_val1 ;
3420 ELSIF g_chg_evt_cd = 'COG' then
3421 g_previous_sex := g_chg_old_val1 ;
3422 --
3423 ELSIF g_chg_evt_cd = 'CODB' THEN
3424 --
3425 g_previous_dob := to_date(g_chg_old_val1 ,'MM/DD/YYYY');
3426 --
3427 END IF;
3428 --
3429 g_rcd_seq := 1; -- what's this do? th.
3430 --
3431 if nvl(ben_extract.g_spcl_hndl_flag,'X') <> 'Y' then -- normal processing
3432 --
3433 process_ext_levels(
3434 p_person_id => p_person_id,
3435 p_ext_rslt_id => p_ext_rslt_id,
3436 p_ext_file_id => p_ext_file_id,
3437 p_data_typ_cd => p_data_typ_cd,
3438 p_ext_typ_cd => p_ext_typ_cd,
3439 p_business_group_id => p_business_group_id,
3440 p_effective_date => g_effective_date
3441 );
3442 else -- special handling flag tells us that it is an ansi 834 extract.
3443 --
3444 ben_ext_ansi.main(
3445 p_person_id => p_person_id,
3446 p_ext_rslt_id => p_ext_rslt_id,
3447 p_ext_file_id => p_ext_file_id,
3448 p_data_typ_cd => p_data_typ_cd,
3449 p_ext_typ_cd => p_ext_typ_cd,
3450 p_ext_crit_prfl_id => p_ext_crit_prfl_id,
3451 p_business_group_id => p_business_group_id,
3452 p_effective_date => g_benefits_ext_dt
3453 );
3454 end if;
3455 --
3456 g_trans_num := g_trans_num + 1;
3457 --
3458 END IF; -- l_include = 'Y'
3459
3460 --
3461 END LOOP; --changes
3462
3463 --
3464 close c_changes_only_extract;
3465 --
3466 end if ; --- for extract chg logs
3467
3468
3469 if ben_ext_thread.g_chg_ext_from_pay = 'Y' then
3470 hr_utility.set_location( ' PAY event log included ' , 99 );
3471 -- Loop thorough all the assignment id for a person
3472 -- within the extract period
3473 --- get the primary assg as of effective date
3474 init_assignment_id(p_person_id => p_person_id ,
3475 p_effective_date => p_effective_date) ;
3476
3477 l_pay_tot_Srno := 1 ;
3478 l_pay_evt_srno := 1 ;
3479
3480 --- determine the adv dates only one for a process
3481
3482 If ben_ext_evaluate_inclusion.g_chg_actl_dt_incl_rqd = 'N' and
3483 ben_ext_evaluate_inclusion.g_chg_eff_dt_incl_rqd = 'N' and
3484 ben_ext_evaluate_inclusion.g_cmbn_incl_rqd = 'Y' then
3485
3486 hr_utility.set_location('pay adv condition mode ' ||g_pay_adv_date_mode , 66 );
3487 if g_pay_adv_date_mode is null then
3488 hr_utility.set_location('pay adv condition exisit withoutot other criteria' , 66 );
3489 get_pay_adv_crit_dates(
3490 p_ext_crit_prfl_id => p_ext_crit_prfl_id,
3491 p_ext_dfn_id => p_ext_dfn_id,
3492 p_business_group_id => p_business_group_id,
3493 p_effective_date => p_effective_date,
3494 p_eff_from_dt => g_pay_adv_eff_from_dt,
3495 p_eff_to_dt => g_pay_adv_eff_to_dt,
3496 p_act_from_dt => g_pay_adv_act_from_dt ,
3497 p_act_to_dt => g_pay_adv_act_to_dt,
3498 p_date_mode => g_pay_adv_date_mode
3499 ) ;
3500 end if ;
3501
3502 end if ;
3503
3504
3505 for pasg in c_pay_p_asg(p_person_id , nvl(l_chg_eff_strt_dt,nvl(l_chg_actl_strt_dt,p_effective_date)),
3506 nvl(l_chg_eff_end_dt,nvl(l_chg_actl_end_dt,p_effective_date))
3507 )
3508 Loop
3509 hr_utility.set_location(' pay assg id ' ||pasg.Assignment_id , 66 ) ;
3510
3511 open c_pay_asg_date (pasg.Assignment_id) ;
3512 fetch c_pay_asg_date into l_pay_asg_eff_date ;
3513 close c_pay_asg_date ;
3514 hr_utility.set_location(' pay assg date ' ||l_pay_asg_eff_date , 66 ) ;
3515 hr_utility.set_location(' pay actual start date ' ||l_chg_actl_strt_dt , 66 ) ;
3516
3517 -- determine the assignment before call the interpreter
3518 init_assignment_id(p_person_id => p_person_id ,
3519 p_effective_date => l_pay_asg_eff_date ,
3520 p_Assignment_id => pasg.Assignment_id ) ;
3521
3522
3523 l_pay_Assignment_id := g_assignment_id ;
3524 if l_pay_Assignment_id is not null then
3525
3526
3527 -- this is a pqp idea to collect the unique column and group id
3528 -- pls dont change the logic unless agreed with pqp
3529 -- this loop collect all the change event result from PEI and colect in a table
3530 -- and also collect the unique table/column/event intto global table
3531 -- pqp need the global table, only used in formula
3532 for i in c_chg_pay_evt
3533 Loop
3534 l_pay_detail_tab.delete ;
3535 l_pay_proration_dates.delete ;
3536 l_pay_proration_changes.delete ;
3537 l_pay_pro_type_tab.delete ;
3538
3539
3540 If ben_ext_evaluate_inclusion.g_chg_actl_dt_incl_rqd = 'N' and
3541 ben_ext_evaluate_inclusion.g_chg_eff_dt_incl_rqd = 'N' and
3542 ben_ext_evaluate_inclusion.g_cmbn_incl_rqd = 'Y' then
3543
3544
3545
3546
3547 Begin
3548
3549
3550 if g_pay_adv_date_mode = 'B' or g_pay_adv_date_mode = 'E' then
3551 hr_utility.set_location('adv effective date mode '||g_pay_adv_eff_from_dt||'/'||
3552 g_pay_adv_eff_to_dt,99) ;
3553
3554 l_eff_event_ecount := 0 ;
3555 l_eff_event_scount := 0 ;
3556 ben_ext_util.entries_affected
3557 (p_assignment_id => l_pay_Assignment_id
3558 ,p_event_group_id => i.event_group_id
3559 ,p_mode => NULL -- 'DATE_PROCESSED' -- 'DATE_EARNED' --
3560 ,p_start_date => (g_pay_adv_eff_from_dt-1)
3561 -- since the PDI use the exclisive of the start and end
3562 ,p_end_date => (g_pay_adv_eff_to_dt)
3563 ,p_business_group_id => p_business_group_id
3564 ,p_detailed_output => l_pay_detail_tab
3565 ,p_process_mode => 'ENTRY_EFFECTIVE_DATE'
3566 ,p_penserv_mode => p_penserv_mode -- vkodedal - changes for penserver -30-apr-2008
3567 );
3568
3569 hr_utility.set_location( 'number of result ' ||l_pay_detail_tab.count, 99 ) ;
3570
3571 -- get the starting count of total colection for comparison
3572 l_eff_event_scount := l_pay_tot_Srno ;
3573
3574 if l_pay_detail_tab.count > 0 then
3575 -- collect all the information onto a table for process for a person
3576 FOR l_pay IN l_pay_detail_tab.FIRST..l_pay_detail_tab.LAST
3577 LOOP
3578
3579 hr_utility.set_location(' insertining tot '|| l_pay_tot_Srno|| ' / '
3580 ||l_pay_detail_tab(l_pay).column_name,99) ;
3581
3582 l_pay_detail_tot_tab(l_pay_tot_Srno).dated_table_id
3583 := l_pay_detail_tab(l_pay).dated_table_id ;
3584 l_pay_detail_tot_tab(l_pay_tot_Srno).datetracked_event
3585 := l_pay_detail_tab(l_pay).datetracked_event ;
3586 l_pay_detail_tot_tab(l_pay_tot_Srno).update_type
3587 := l_pay_detail_tab(l_pay).update_type ;
3588 l_pay_detail_tot_tab(l_pay_tot_Srno).surrogate_key
3589 := l_pay_detail_tab(l_pay).surrogate_key ;
3590 l_pay_detail_tot_tab(l_pay_tot_Srno).column_name
3591 := l_pay_detail_tab(l_pay).column_name ;
3592 l_pay_detail_tot_tab(l_pay_tot_Srno).effective_date
3593 := l_pay_detail_tab(l_pay).effective_date ;
3594 l_pay_detail_tot_tab(l_pay_tot_Srno).old_value
3595 := l_pay_detail_tab(l_pay).old_value ;
3596 l_pay_detail_tot_tab(l_pay_tot_Srno).new_value
3597 := l_pay_detail_tab(l_pay).new_value ;
3598 l_pay_detail_tot_tab(l_pay_tot_Srno).change_values
3599 := l_pay_detail_tab(l_pay).change_values ;
3600 l_pay_detail_tot_tab(l_pay_tot_Srno).proration_type
3601 := l_pay_detail_tab(l_pay).proration_type ;
3602 l_pay_detail_tot_tab(l_pay_tot_Srno).change_mode
3603 := l_pay_detail_tab(l_pay).change_mode ;
3604 l_pay_detail_tot_tab(l_pay_tot_Srno).event_group_id := i.event_group_id ;
3605 l_pay_detail_tot_tab(l_pay_tot_Srno).actual_date
3606 := l_pay_detail_tab(l_pay).creation_date ;
3607 l_pay_tot_Srno := l_pay_tot_Srno + 1 ;
3608
3609 End loop ;
3610 end if ;
3611
3612 End if ;
3613
3614
3615 if g_pay_adv_date_mode = 'B' or g_pay_adv_date_mode = 'C' then
3616 -- get the total count of srno for efficient comaprison
3617 l_eff_event_ecount := l_pay_detail_tot_tab.count ;
3618 l_pay_detail_tab.delete ;
3619 hr_utility.set_location('adv actual date mode '||g_pay_adv_act_from_dt||' / ' ||
3620 g_pay_adv_act_to_dt,99) ;
3621 ben_ext_util.entries_affected
3622 (p_assignment_id => l_pay_Assignment_id
3623 ,p_event_group_id => i.event_group_id
3624 ,p_mode => NULL -- 'DATE_PROCESSED' -- 'DATE_EARNED' --
3625 ,p_start_date => trunc(g_pay_adv_act_from_dt)
3626 -- since the PDI use the exclisive of the start and end
3627 ,p_end_date => (trunc(g_pay_adv_act_to_dt)+0.99999)
3628 ,p_business_group_id => p_business_group_id
3629 ,p_detailed_output => l_pay_detail_tab
3630 ,p_process_mode => 'ENTRY_CREATION_DATE'
3631 ,p_penserv_mode => p_penserv_mode --vkodedal changes for penserver - 30-apr-2008
3632 );
3633
3634
3635
3636 hr_utility.set_location( 'number of result ' ||l_pay_detail_tab.count, 99 ) ;
3637
3638 if l_pay_detail_tab.count > 0 then
3639
3640 -- collect all the information onto a table for process for a person
3641 FOR l_pay IN l_pay_detail_tab.FIRST..l_pay_detail_tab.LAST
3642 LOOP
3643
3644
3645 l_g_c_found := 'N' ;
3646 -- Look for the duplication from actaul and effective
3647 if g_pay_adv_date_mode = 'B' and (l_eff_event_ecount-l_eff_event_scount) >= 0 then
3648 --for l_g_c IN 1 .. l_pay_detail_tot_tab.count
3649 for l_g_c IN l_eff_event_scount .. l_eff_event_ecount
3650 Loop
3651 if l_pay_detail_tot_tab(l_g_c).dated_table_id=l_pay_detail_tab(l_pay).dated_table_id
3652 and l_pay_detail_tot_tab(l_g_c).event_group_id = i.event_group_id
3653 and l_pay_detail_tot_tab(l_g_c).surrogate_key
3654 = l_pay_detail_tab(l_pay).surrogate_key
3655 and l_pay_detail_tot_tab(l_g_c).update_type
3656 = l_pay_detail_tab(l_pay).update_type
3657 and l_pay_detail_tot_tab(l_g_c).effective_date
3658 = l_pay_detail_tab(l_pay).effective_date
3659 and l_pay_detail_tot_tab(l_g_c).actual_Date
3660 = l_pay_detail_tab(l_pay).creation_date
3661 and nvl(l_pay_detail_tot_tab(l_g_c).column_name,'-1')
3662 = nvl(l_pay_detail_tab(l_pay).column_name,'-1')
3663 and nvl(l_pay_detail_tot_tab(l_g_c).datetracked_event,'-1')
3664 = nvl(l_pay_detail_tab(l_pay).datetracked_event,'-1')
3665 and nvl(l_pay_detail_tot_tab(l_g_c).proration_type,'-1')
3666 = nvl(l_pay_detail_tab(l_pay).proration_type,'-1')
3667 and nvl(l_pay_detail_tot_tab(l_g_c).change_mode,'-1')
3668 = nvl(l_pay_detail_tab(l_pay).change_mode,'-1')
3669 and nvl(l_pay_detail_tot_tab(l_g_c).change_values,'-1')
3670 = nvl(l_pay_detail_tab(l_pay).change_values,'-1')
3671 and nvl(l_pay_detail_tot_tab(l_g_c).old_value,'-1')
3672 = nvl(l_pay_detail_tab(l_pay).old_value,'-1')
3673 and nvl(l_pay_detail_tot_tab(l_g_c).new_value,'-1')
3674 = nvl(l_pay_detail_tab(l_pay).new_value,'-1')
3675 then
3676 l_g_c_found := 'Y' ;
3677 exit ;
3678 end if ;
3679 End loop ;
3680 End if ;
3681
3682
3683 --- if the entry is unique then create
3684 if l_g_c_found = 'N' then
3685
3686 hr_utility.set_location(' insertining tot '|| l_pay_tot_Srno|| ' / '
3687 ||l_pay_detail_tab(l_pay).column_name,99) ;
3688
3689 l_pay_detail_tot_tab(l_pay_tot_Srno).dated_table_id
3690 := l_pay_detail_tab(l_pay).dated_table_id ;
3691 l_pay_detail_tot_tab(l_pay_tot_Srno).datetracked_event
3692 := l_pay_detail_tab(l_pay).datetracked_event ;
3693 l_pay_detail_tot_tab(l_pay_tot_Srno).update_type
3694 := l_pay_detail_tab(l_pay).update_type ;
3695 l_pay_detail_tot_tab(l_pay_tot_Srno).surrogate_key
3696 := l_pay_detail_tab(l_pay).surrogate_key ;
3697 l_pay_detail_tot_tab(l_pay_tot_Srno).column_name
3698 := l_pay_detail_tab(l_pay).column_name ;
3699 l_pay_detail_tot_tab(l_pay_tot_Srno).effective_date
3700 := l_pay_detail_tab(l_pay).effective_date ;
3701 l_pay_detail_tot_tab(l_pay_tot_Srno).old_value
3702 := l_pay_detail_tab(l_pay).old_value ;
3703 l_pay_detail_tot_tab(l_pay_tot_Srno).new_value
3704 := l_pay_detail_tab(l_pay).new_value ;
3705 l_pay_detail_tot_tab(l_pay_tot_Srno).change_values
3706 := l_pay_detail_tab(l_pay).change_values ;
3707 l_pay_detail_tot_tab(l_pay_tot_Srno).proration_type
3708 := l_pay_detail_tab(l_pay).proration_type ;
3709 l_pay_detail_tot_tab(l_pay_tot_Srno).change_mode
3710 := l_pay_detail_tab(l_pay).change_mode ;
3711 l_pay_detail_tot_tab(l_pay_tot_Srno).event_group_id := i.event_group_id ;
3712 l_pay_detail_tot_tab(l_pay_tot_Srno).actual_date
3713 := l_pay_detail_tab(l_pay).creation_date ;
3714 l_pay_tot_Srno := l_pay_tot_Srno + 1 ;
3715 end if ; -- unique entry
3716 end loop ;
3717
3718 End if ;
3719
3720 end if;
3721 Exception
3722 WHEN hr_application_error THEN
3723 -- the exception handled only when thge pqp raise the error with the msg
3724 IF hr_utility.get_message = 'BEN_94629_NO_ASG_ACTION_ID' THEN
3725 hr_utility.set_location( 'Current assignment has no Assignment Action id. ' ,-9999);
3726 g_err_num := 94629 ;
3727 g_err_name := 'BEN_94629_NO_ASG_ACTION_ID' ;
3728 g_elmt_name:= null ;
3729 raise detail_restart_error ;
3730 else
3731 hr_utility.set_location( 'unknow exception raised in pqp.',-9999);
3732 raise; -- to re-raise the exception
3733 end if ;
3734
3735 End ;
3736
3737 Else
3738
3739
3740 Begin
3741
3742 if l_chg_actl_strt_dt is not null and ben_ext_evaluate_inclusion.g_chg_actl_dt_incl_rqd = 'Y' then
3743 -- call the interpreter in actual date mode
3744 -- as per my understanding from PQP - ram
3745 -- since the actual date has the time stamp , the time stamp play the role in extracting info
3746 -- so the from date is truncated and to date is extended to the last second of the day
3747
3748 hr_utility.set_location('pay actual dt mode '||trunc(l_chg_actl_strt_dt)||' / '||
3749 (trunc(l_chg_actl_end_dt)+0.99999) , 66 );
3750
3751 ben_ext_util.entries_affected
3752 (p_assignment_id => l_pay_Assignment_id
3753 ,p_event_group_id => i.event_group_id
3754 ,p_mode => NULL -- 'DATE_PROCESSED' -- 'DATE_EARNED' --
3755 ,p_start_date => trunc(l_chg_actl_strt_dt)
3756 -- since the PDI use the exclisive of the start and end
3757 ,p_end_date => (trunc(l_chg_actl_end_dt)+0.99999)
3758 ,p_business_group_id => p_business_group_id
3759 ,p_detailed_output => l_pay_detail_tab
3760 ,p_process_mode => 'ENTRY_CREATION_DATE'
3761 ,p_penserv_mode => p_penserv_mode --vkodedal changes for penserver - 30-apr-2008
3762 );
3763
3764 else
3765 -- call in payroll interpreter in effctive date mode
3766 -- payroll exclude the from date data for proration purpose ,
3767 --the interpreter developerd for proration
3768 -- then used for reporting so the functionality remains the same
3769 -- we are passing -1 to make sure the from date data is included
3770 hr_utility.set_location(' pay effectivedt mode ' ||(l_chg_eff_strt_dt-1) || ' / ' ||
3771 l_chg_eff_end_dt , 66 ) ;
3772 ben_ext_util.entries_affected
3773 (p_assignment_id => l_pay_Assignment_id
3774 ,p_event_group_id => i.event_group_id
3775 ,p_mode => NULL -- 'DATE_PROCESSED' -- 'DATE_EARNED' --
3776 ,p_start_date => (l_chg_eff_strt_dt-1)
3777 -- since the PDI use the exclisive of the start and end
3778 ,p_end_date => (l_chg_eff_end_dt)
3779 ,p_business_group_id => p_business_group_id
3780 ,p_detailed_output => l_pay_detail_tab
3781 ,p_process_mode => 'ENTRY_EFFECTIVE_DATE'
3782 ,p_penserv_mode => p_penserv_mode --vkodedal changes for penserver - 30-apr-2008
3783 );
3784 end if ;
3785 Exception
3786 WHEN hr_application_error THEN
3787 -- the exception handled only when thge pqp raise the error with the msg
3788 IF hr_utility.get_message = 'BEN_94629_NO_ASG_ACTION_ID' THEN
3789 hr_utility.set_location( 'Current assignment has no Assignment Action id. ' ,-9999);
3790 g_err_num := 94629 ;
3791 g_err_name := 'BEN_94629_NO_ASG_ACTION_ID' ;
3792 g_elmt_name:= null ;
3793 raise detail_restart_error ;
3794 else
3795 hr_utility.set_location( 'unknow exception raised in ben_ext_util.entries_affected.',-9999);
3796 raise; -- to re-raise the exception
3797 end if ;
3798 End ;
3799
3800 hr_utility.set_location( 'number of result ' ||l_pay_detail_tab.count, 99 ) ;
3801 if l_pay_detail_tab.count > 0 then
3802
3803 -- collect all the information onto a table for process for a person
3804 FOR l_pay IN l_pay_detail_tab.FIRST..l_pay_detail_tab.LAST
3805 LOOP
3806
3807 hr_utility.set_location(' insertining tot '|| l_pay_tot_Srno|| ' / ' ||
3808 l_pay_detail_tab(l_pay).column_name,99) ;
3809
3810 l_pay_detail_tot_tab(l_pay_tot_Srno).dated_table_id := l_pay_detail_tab(l_pay).dated_table_id ;
3811 l_pay_detail_tot_tab(l_pay_tot_Srno).datetracked_event
3812 :=l_pay_detail_tab(l_pay).datetracked_event ;
3813 l_pay_detail_tot_tab(l_pay_tot_Srno).update_type := l_pay_detail_tab(l_pay).update_type ;
3814 l_pay_detail_tot_tab(l_pay_tot_Srno).surrogate_key := l_pay_detail_tab(l_pay).surrogate_key ;
3815 l_pay_detail_tot_tab(l_pay_tot_Srno).column_name := l_pay_detail_tab(l_pay).column_name ;
3816 l_pay_detail_tot_tab(l_pay_tot_Srno).effective_date := l_pay_detail_tab(l_pay).effective_date ;
3817 l_pay_detail_tot_tab(l_pay_tot_Srno).old_value := l_pay_detail_tab(l_pay).old_value ;
3818 l_pay_detail_tot_tab(l_pay_tot_Srno).new_value := l_pay_detail_tab(l_pay).new_value ;
3819 l_pay_detail_tot_tab(l_pay_tot_Srno).change_values := l_pay_detail_tab(l_pay).change_values ;
3820 l_pay_detail_tot_tab(l_pay_tot_Srno).proration_type := l_pay_detail_tab(l_pay).proration_type ;
3821 l_pay_detail_tot_tab(l_pay_tot_Srno).change_mode := l_pay_detail_tab(l_pay).change_mode ;
3822 l_pay_detail_tot_tab(l_pay_tot_Srno).event_group_id := i.event_group_id ;
3823 l_pay_detail_tot_tab(l_pay_tot_Srno).actual_date := l_pay_detail_tab(l_pay).creation_date;
3824 l_pay_tot_Srno := l_pay_tot_Srno + 1 ;
3825
3826 --- find the unique column for global colection for a person
3827 l_g_c_found := 'N' ;
3828 for l_g_c IN 1 .. g_pay_evt_group_tab.count
3829 Loop
3830 if g_pay_evt_group_tab(l_g_c).dated_table_id = l_pay_detail_tab(l_pay).dated_table_id and
3831 g_pay_evt_group_tab(l_g_c).column_name = l_pay_detail_tab(l_pay).column_name and
3832 g_pay_evt_group_tab(l_g_c).event_group_id = i.event_group_id then
3833 l_g_c_found := 'Y' ;
3834 exit ;
3835 end if ;
3836 End loop ;
3837 -- if the value not already exist
3838 if l_g_c_found = 'N' then
3839 hr_utility.set_location('insertining GL '||l_pay_evt_srno||' / '||
3840 l_pay_detail_tab(l_pay).column_name,99) ;
3841 g_pay_evt_group_tab(l_pay_evt_srno).dated_table_id:=l_pay_detail_tab(l_pay).dated_table_id ;
3842 g_pay_evt_group_tab(l_pay_evt_srno).column_name := l_pay_detail_tab(l_pay).column_name ;
3843 g_pay_evt_group_tab(l_pay_evt_srno).event_group_id := i.event_group_id ;
3844 l_pay_evt_srno := l_pay_evt_srno + 1 ;
3845 end if ;
3846 End Loop ;
3847 End if ;
3848 End If; --- adv criteria
3849
3850 End Loop ;
3851 End if ; -- asg id is not null
3852 end loop ; -- multiple asg id
3853 --- sor the table value
3854
3855 -- reintialise the global
3856 init_assignment_id(p_person_id => p_person_id ,
3857 p_effective_date => p_effective_date) ;
3858
3859
3860 ben_ext_payroll_balance.sort_payroll_events
3861 (p_pay_events_tab => l_pay_detail_tot_tab ) ;
3862
3863 -- process the collected nformation for a person
3864 hr_utility.set_location( 'number of sorted result ' ||g_pay_proc_evt_tab.count, 99 ) ;
3865 if g_pay_proc_evt_tab.count > 0 then
3866 FOR l_pay IN 1 .. g_pay_proc_evt_tab.count
3867 LOOP
3868 init_detail_globals;
3869
3870 hr_utility.set_location( ' column name ' ||g_pay_proc_evt_tab(l_pay).column_name
3871 ||' / '||g_pay_proc_evt_tab(l_pay).dated_table_id , 99 );
3872
3873 l_dated_table_id := g_pay_proc_evt_tab(l_pay).dated_table_id ;
3874 g_chg_pay_column := g_pay_proc_evt_tab(l_pay).column_name ;
3875 g_chg_eff_dt := g_pay_proc_evt_tab(l_pay).effective_date ;
3876 g_chg_old_val1 := g_pay_proc_evt_tab(l_pay).old_value ;
3877 g_chg_new_val1 := g_pay_proc_evt_tab(l_pay).new_value ;
3878 g_chg_evt_cd := g_pay_proc_evt_tab(l_pay).event_group_id ;
3879 g_chg_pay_mode := g_pay_proc_evt_tab(l_pay).change_mode ;
3880 g_chg_update_type := g_pay_proc_evt_tab(l_pay).update_type ;
3881 g_chg_surrogate_key := g_pay_proc_evt_tab(l_pay).surrogate_key ;
3882 g_chg_next_event_date := g_pay_proc_evt_tab(l_pay).next_evt_start_date ;
3883 g_chg_actl_dt := g_pay_proc_evt_tab(l_pay).actual_date ;
3884 g_chg_pay_evt_index := l_pay ;
3885
3886 hr_utility.set_location(' pay chg index '||g_chg_pay_evt_index,99) ;
3887 hr_utility.set_location('date and end date '||g_person_id||'-'||g_chg_eff_dt||'/'||
3888 g_chg_next_event_date,99) ;
3889 g_chg_evt_source := 'PAY' ;
3890 ben_ext_util.get_ext_dates
3891 (p_ext_dfn_id => p_ext_dfn_id,
3892 p_data_typ_cd => p_data_typ_cd,
3893 p_effective_date => g_effective_date,
3894 p_person_ext_dt => l_person_ext_dt, --out
3895 p_benefits_ext_dt => l_benefits_ext_dt); -- out
3896 --
3897 g_person_ext_dt := l_person_ext_dt;
3898 g_benefits_ext_dt := l_benefits_ext_dt;
3899
3900 --determine the table name from the id
3901 if l_dated_table_id is not null then
3902 open c_pay_chg_tbl(l_dated_table_id) ;
3903 fetch c_pay_chg_tbl into g_chg_pay_table ;
3904 close c_pay_chg_tbl ;
3905 end if ;
3906
3907 l_include := 'Y';
3908 --
3909 if p_ext_crit_prfl_id is not null THEN
3910 --
3911 ben_ext_evaluate_inclusion.evaluate_change_log_incl
3912 (p_chg_evt_cd => g_chg_evt_cd,
3913 p_chg_evt_source => g_chg_evt_source,
3914 p_chg_eff_dt => trunc(g_chg_eff_dt),
3915 p_chg_actl_dt => trunc(g_chg_actl_dt) ,
3916 p_last_update_login => null ,
3917 p_effective_date => g_effective_date,
3918 p_include => l_include);
3919 --
3920 end if; -- p_ext_crit_prfl_id is not null
3921 --
3922 hr_utility.set_location( ' Inclusion flag ' || l_include , 99 ) ;
3923 hr_utility.set_location( ' actual ' || g_chg_actl_dt , 99 ) ;
3924 hr_utility.set_location( ' efective ' || g_chg_eff_dt , 99 ) ;
3925
3926 if l_include = 'Y' then
3927 --
3928
3929 Extract_person_info(p_person_id => p_person_id,
3930 p_effective_date => p_effective_date, -- passed in from conc mgr
3931 p_business_group_id => p_business_group_id,
3932 p_ext_rslt_id => p_ext_rslt_id
3933 ) ;
3934 --
3935 if p_ext_crit_prfl_id is not null THEN
3936 --
3937 ben_ext_evaluate_inclusion.Evaluate_Person_Incl
3938 (p_person_id => p_person_id,
3939 p_postal_code => g_prim_postal_code,
3940 p_org_id => g_employee_organization_id,
3941 p_loc_id => g_location_id,
3942 p_gre => null, -- this will be fetched in called program.
3943 p_state => g_prim_state,
3944 p_bnft_grp => g_benefit_group_id,
3945 p_ee_status => g_employee_status_id,
3946 p_chg_evt_cd => g_chg_evt_cd,
3947 p_chg_evt_source => g_chg_evt_source,
3948 p_effective_date => g_person_ext_dt,
3949 --RCHASE
3950 p_eff_date => trunc(g_chg_eff_dt),
3951 --End RCHASE
3952 p_actl_date => trunc(g_chg_actl_dt),
3953 p_include => l_include);
3954 --
3955 end if; -- p_ext_crit_prfl_id is not null
3956 --
3957 end if; -- l_include = 'Y'
3958 --
3959 if l_include = 'Y' THEN
3960 if g_debug then
3961 hr_utility.set_location(' Change Event Code ' || g_chg_evt_cd , 99 );
3962 end if;
3963 g_rcd_seq := 1;
3964 --
3965 if nvl(ben_extract.g_spcl_hndl_flag,'X') <> 'Y' then -- normal processing
3966 --
3967 process_ext_levels(
3968 p_person_id => p_person_id,
3969 p_ext_rslt_id => p_ext_rslt_id,
3970 p_ext_file_id => p_ext_file_id,
3971 p_data_typ_cd => p_data_typ_cd,
3972 p_ext_typ_cd => p_ext_typ_cd,
3973 p_business_group_id => p_business_group_id,
3974 p_effective_date => g_effective_date
3975 );
3976 else -- special handling flag tells us that it is an ansi 834 extract.
3977 --
3978 ben_ext_ansi.main(
3979 p_person_id => p_person_id,
3980 p_ext_rslt_id => p_ext_rslt_id,
3981 p_ext_file_id => p_ext_file_id,
3982 p_data_typ_cd => p_data_typ_cd,
3983 p_ext_typ_cd => p_ext_typ_cd,
3984 p_ext_crit_prfl_id => p_ext_crit_prfl_id,
3985 p_business_group_id => p_business_group_id,
3986 p_effective_date => g_benefits_ext_dt
3987 );
3988 end if;
3989 --
3990 g_trans_num := g_trans_num + 1;
3991 --
3992 END IF; -- l_include = 'Y'
3993
3994 END LOOP; -- collection loop
3995 End if ; -- count total collection return
3996 -- clear the table for next person
3997 l_pay_detail_tot_tab.delete ;
3998 g_pay_evt_group_tab.delete ;
3999
4000 end if ; --- for pay eventi process
4001
4002 -- ==========================================
4003 -- Communication Extract
4004 -- ==========================================
4005 --
4006 ELSIF p_data_typ_cd = 'CM' THEN
4007 --
4008 g_cm_flag := 'Y';
4009 --
4010 g_trans_num := 1;
4011 --
4012 ben_ext_util.get_cm_dates
4013 (p_ext_dfn_id => ben_extract.g_ext_dfn_id, --in
4014 p_effective_date => g_effective_date, --in
4015 p_to_be_sent_strt_dt => l_to_be_sent_strt_dt, --out
4016 p_to_be_sent_end_dt => l_to_be_sent_end_dt); --out
4017
4018 --- Communication cursor changed to three cursors and a bulk collect
4019 --- there is a remote possibility this may fetch lesser row due to
4020 --- changes in external joints , 1 communication can have more trigger if it is manual
4021 --- since we generate 1 communication on extract row, we do not need to worry
4022
4023 --
4024 open c_communication_extract (l_to_be_sent_strt_dt,
4025 l_to_be_sent_end_dt);
4026 fetch c_communication_extract bulk collect into
4027 l_per_cm_id_va ,
4028 l_per_in_ler_id_va ,
4029 l_prtt_enrt_actn_id_va ,
4030 l_effective_start_date_va ,
4031 l_per_cm_eff_start_date_va ,
4032 l_to_be_sent_dt_va ,
4033 l_sent_dt_va ,
4034 l_per_cm_last_update_date_va ,
4035 l_last_update_date_va ,
4036 l_dlvry_instn_txt_va ,
4037 l_inspn_rqd_flag_va ,
4038 l_address_id_va ,
4039 l_per_cm_prvdd_id_va ,
4040 l_object_version_number_va ,
4041 l_cm_typ_id_va
4042 ;
4043
4044 close c_communication_extract ;
4045
4046 for i IN 1 .. l_per_cm_id_va.count
4047 --
4048 LOOP
4049 --
4050 init_detail_globals;
4051
4052 g_per_cm_id := l_per_cm_id_va(i) ;
4053 g_cm_per_in_ler_id := l_per_in_ler_id_va(i) ;
4054 g_cm_prtt_enrt_actn_id := l_prtt_enrt_actn_id_va(i) ;
4055 g_cm_eff_dt := nvl(l_effective_start_date_va(i),l_per_cm_eff_start_date_va(i) ) ;
4056 g_cm_to_be_sent_dt := l_to_be_sent_dt_va(i) ;
4057 g_cm_sent_dt := l_sent_dt_va(i) ;
4058 g_cm_last_update_date := l_per_cm_last_update_date_va(i) ;
4059 g_cm_pvdd_last_update_date := l_last_update_date_va(i) ;
4060 g_cm_dlvry_instn_txt := l_dlvry_instn_txt_va(i) ;
4061 g_cm_inspn_rqd_flag := l_inspn_rqd_flag_va(i) ;
4062 g_cm_address_id := l_address_id_va(i) ;
4063 g_per_cm_prvdd_id := l_per_cm_prvdd_id_va(i) ;
4064 g_per_cm_object_version_number := l_object_version_number_va(i) ;
4065 g_cm_prvdd_eff_dt := l_effective_start_date_va(i) ;
4066 g_cm_type_id := l_cm_typ_id_va (i) ;
4067
4068
4069
4070 --- get the trigger date from person commu trigger
4071 l_cm_trgr_id := null ;
4072 open c_per_comm_trigger (g_per_cm_id , p_effective_date) ;
4073 fetch c_per_comm_trigger into g_cm_trgr_proc_dt, l_cm_trgr_id ;
4074 close c_per_comm_trigger ;
4075
4076 --- communication trigger setup information
4077
4078 if l_cm_trgr_id is not null then
4079 open c_comm_trgr (l_cm_trgr_id) ;
4080 fetch c_comm_trgr into g_cm_trgr_proc_name ;
4081 close c_comm_trgr ;
4082 end if ;
4083
4084 --- communication type information
4085 open c_comm_typ (l_cm_typ_id_va(i) , g_cm_eff_dt) ;
4086 fetch c_comm_typ into
4087 g_cm_type ,
4088 g_cm_short_name ,
4089 g_cm_kit
4090 ;
4091 close c_comm_typ ;
4092
4093 --- life event information
4094
4095 if l_per_in_ler_id_va(i) is not null then
4096 open c_pil (l_per_in_ler_id_va(i) , g_cm_eff_dt ) ;
4097 fetch c_pil into g_cm_lf_evt_id
4098 ,g_cm_lf_evt
4099 ,g_cm_lf_evt_stat
4100 ,g_cm_lf_evt_ocrd_dt
4101 ,g_cm_lf_evt_ntfn_dt
4102 ;
4103 close c_pil ;
4104
4105 if g_cm_lf_evt_ocrd_dt is null and l_per_cm_eff_start_date_va(i) is not null then
4106 g_cm_lf_evt_ocrd_dt := l_per_cm_eff_start_date_va(i) ;
4107 end if ;
4108
4109 if g_cm_lf_evt_ntfn_dt is null and l_per_cm_eff_start_date_va(i) is not null then
4110 g_cm_lf_evt_ntfn_dt := l_per_cm_eff_start_date_va(i) ;
4111 end if ;
4112
4113 end if ;
4114
4115 g_detail_extracted:=false;
4116 --
4117 --g_extract_date := g_cm_eff_dt;
4118 --
4119 ben_ext_util.get_ext_dates
4120 (p_ext_dfn_id => p_ext_dfn_id,
4121 p_data_typ_cd => p_data_typ_cd,
4122 p_effective_date => g_effective_date,
4123 p_person_ext_dt => l_person_ext_dt, --out
4124 p_benefits_ext_dt => l_benefits_ext_dt); -- out
4125 --
4126 g_person_ext_dt := l_person_ext_dt;
4127 g_benefits_ext_dt := l_benefits_ext_dt;
4128 --
4129 l_include := 'Y';
4130 --
4131 if p_ext_crit_prfl_id is not null THEN
4132 --
4133 ben_ext_evaluate_inclusion.evaluate_comm_incl
4134 (p_cm_typ_id => g_cm_type_id,
4135 p_last_update_date => g_cm_last_update_date,
4136 p_pvdd_last_update_date => g_cm_pvdd_last_update_date,
4137 p_sent_dt => g_cm_sent_dt,
4138 p_to_be_sent_dt => g_cm_to_be_sent_dt,
4139 p_effective_date => g_effective_date,
4140 p_include => l_include);
4141 --
4142 end if; -- p_ext_crit_prfl_id is not null
4143 --
4144 if l_include = 'Y' then
4145 --
4146 Extract_person_info(p_person_id => p_person_id,
4147 p_effective_date => p_effective_date, -- passed in from conc mgr
4148 p_business_group_id => p_business_group_id ,
4149 p_ext_rslt_id => p_ext_rslt_id
4150 ) ;
4151 --
4152 --
4153 if p_ext_crit_prfl_id is not null THEN
4154 --
4155 ben_ext_evaluate_inclusion.Evaluate_Person_Incl
4156 (p_person_id => p_person_id,
4157 p_postal_code => g_prim_postal_code,
4158 p_org_id => g_employee_organization_id,
4159 p_loc_id => g_location_id,
4160 p_gre => null, -- this will be fetched in called program.
4161 p_state => g_prim_state,
4162 p_bnft_grp => g_benefit_group_id,
4163 p_ee_status => g_employee_status_id,
4164 p_chg_evt_cd => null,
4165 p_effective_date => g_person_ext_dt,
4166 p_actl_date => null,
4167 p_include => l_include);
4168 --
4169 end if; -- p_ext_crit_prfl_id is not null
4170 --
4171 end if; -- l_include = 'Y'
4172 --
4173 IF l_include = 'Y' THEN
4174 --
4175 g_rcd_seq := 1;
4176 --
4177 if nvl(ben_extract.g_spcl_hndl_flag,'X') <> 'Y' then -- normal processing
4178 --
4179 process_ext_levels(
4180 p_person_id => p_person_id,
4181 p_ext_rslt_id => p_ext_rslt_id,
4182 p_ext_file_id => p_ext_file_id,
4183 p_data_typ_cd => p_data_typ_cd,
4184 p_ext_typ_cd => p_ext_typ_cd,
4185 p_business_group_id => p_business_group_id,
4186 p_effective_date => g_effective_date
4187 );
4188 else -- special handling flag tells us that it is an ansi 834 extract.
4189 --
4190 ben_ext_ansi.main(
4191 p_person_id => p_person_id,
4192 p_ext_rslt_id => p_ext_rslt_id,
4193 p_ext_file_id => p_ext_file_id,
4194 p_data_typ_cd => p_data_typ_cd,
4195 p_ext_typ_cd => p_ext_typ_cd,
4196 p_ext_crit_prfl_id => p_ext_crit_prfl_id,
4197 p_business_group_id => p_business_group_id,
4198 p_effective_date => g_benefits_ext_dt
4199 );
4200 end if;
4201 --
4202 g_trans_num := g_trans_num + 1;
4203 --
4204 END IF; -- l_include = 'Y'
4205 --
4206 -- updating ben_per_cm_prvdd_f.sent_dt
4207 --
4208 if (ben_ext_person.g_cm_flag = 'Y' and
4209 ben_ext_person.g_upd_cm_sent_dt_flag = 'Y' and
4210 ben_ext_person.g_per_cm_prvdd_id is not null and
4211 g_detail_extracted) then
4212 if nvl(l_last_per_cm_prvdd_id,-1) <> ben_ext_person.g_per_cm_prvdd_id then
4213 ben_PER_CM_PRVDD_api.update_PER_CM_PRVDD
4214 (p_validate => null,
4215 p_per_cm_prvdd_id => ben_ext_person.g_per_cm_prvdd_id,
4216 p_effective_start_date=> l_dummy_start_date,
4217 p_effective_end_date => l_dummy_end_date,
4218 p_sent_dt => trunc(sysdate),
4219 p_object_version_number=>ben_ext_person.g_per_cm_object_version_number,
4220 p_effective_date => ben_ext_person.g_cm_prvdd_eff_dt,
4221 p_datetrack_mode => 'CORRECTION');
4222 l_last_per_cm_prvdd_id:=ben_ext_person.g_per_cm_prvdd_id;
4223 end if;
4224 end if;
4225
4226 END LOOP;
4227
4228 --fixed bug 7323551--invalid cursor
4229 -- close c_communication_extract;
4230 -- ==================================
4231 -- Comp work bench CWB
4232 -- ================================
4233 ELSIF p_data_typ_cd = 'CW' THEN
4234
4235 g_trans_num := 1;
4236 --
4237 init_detail_globals;
4238 --
4239 for l_cwb in c_cwb_extract
4240 Loop
4241 g_CWB_EFFECTIVE_DATE := l_cwb.effective_date ;
4242 g_CWB_LE_DT := l_cwb.LF_EVT_OCRD_DT ;
4243 hr_utility.set_location('cwb person ' || l_cwb.person_id , 99 ) ;
4244 ben_ext_util.get_ext_dates
4245 (p_ext_dfn_id => p_ext_dfn_id,
4246 p_data_typ_cd => p_data_typ_cd,
4247 p_effective_date => g_effective_date,
4248 p_person_ext_dt => l_person_ext_dt, --out
4249 p_benefits_ext_dt => l_benefits_ext_dt); -- out
4250 --
4251 g_person_ext_dt := l_person_ext_dt;
4252 g_benefits_ext_dt := l_benefits_ext_dt;
4253 --
4254 l_include := 'Y';
4255 --
4256 if p_ext_crit_prfl_id is not null THEN
4257 --
4258 ben_ext_evaluate_inclusion.evaluate_cwb_incl
4259 (p_group_pl_id => l_cwb.group_pl_id ,
4260 p_lf_evt_ocrd_dt => g_CWB_LE_DT ,
4261 p_include => l_include ,
4262 p_effective_date => p_effective_date )
4263 ;
4264 --
4265 end if; -- p_ext_crit_prfl_id is not null
4266 --
4267 if l_include = 'Y' then
4268 -- change the busines group of person
4269 g_business_group_id := l_cwb.business_group_id ;
4270
4271 Extract_person_info(p_person_id => p_person_id,
4272 p_effective_date => p_effective_date, -- passed in from conc mgr
4273 p_business_group_id => l_cwb.business_group_id,
4274 p_ext_rslt_id => p_ext_rslt_id
4275 ) ;
4276 --
4277 --
4278 --
4279 if p_ext_crit_prfl_id is not null THEN
4280 --
4281 ben_ext_evaluate_inclusion.Evaluate_Person_Incl
4282 (p_person_id => p_person_id,
4283 p_postal_code => g_prim_postal_code,
4284 p_org_id => g_employee_organization_id,
4285 p_loc_id => g_location_id,
4286 p_gre => null, -- this will be fetched in called program.
4287 p_state => g_prim_state,
4288 p_bnft_grp => g_benefit_group_id,
4289 p_ee_status => g_employee_status_id,
4290 p_chg_evt_cd => null,
4291 p_effective_date => g_person_ext_dt,
4292 p_actl_date => null,
4293 p_include => l_include);
4294 --
4295 end if; -- p_ext_crit_prfl_id is not null
4296 end if ;
4297
4298 if l_include = 'Y' then
4299
4300 ---- Assign CWB Variables
4301 g_cwb_per_group_per_in_ler_id := l_cwb.group_per_in_ler_id ;
4302 g_cwb_per_group_pl_id := l_cwb.group_pl_id ;
4303 g_CWB_Person_FULL_NAME := l_cwb.FULL_NAME ;
4304 g_CWB_Person_Custom_Name := l_cwb.Custom_Name;
4305 g_CWB_Person_Brief_Name := l_cwb.Brief_Name;
4306 g_CWB_Life_Event_Name := l_cwb.Ler_name;
4307 g_CWB_Life_Event_Occurred_Date := l_cwb.LF_EVT_OCRD_DT;
4308 g_CWB_Person_EMAIL_DDRESS := l_cwb.EMAIL_ADDRESS;
4309 g_CWB_Person_EMPLOYEE_NUMBER := l_cwb.EMPLOYEE_NUMBER;
4310 g_CWB_Person_BASE_SALARY := l_cwb.BASE_SALARY;
4311 g_CWB_Person_CHANGE_REASON := l_cwb.CHANGE_REASON;
4312 g_CWB_PEOPLE_GROUP_NAME := l_cwb.PEOPLE_GROUP_name;
4313 g_CWB_PEOPLE_GROUP_SEGMENT1 := l_cwb.PEOPLE_GROUP_SEGMENT1;
4314 g_CWB_PEOPLE_GROUP_SEGMENT10 := l_cwb.PEOPLE_GROUP_SEGMENT10;
4315 g_CWB_PEOPLE_GROUP_SEGMENT11 := l_cwb.PEOPLE_GROUP_SEGMENT11;
4316 g_CWB_PEOPLE_GROUP_SEGMENT2 := l_cwb.PEOPLE_GROUP_SEGMENT2;
4317 g_CWB_PEOPLE_GROUP_SEGMENT3 := l_cwb.PEOPLE_GROUP_SEGMENT3;
4318 g_CWB_PEOPLE_GROUP_SEGMENT4 := l_cwb.PEOPLE_GROUP_SEGMENT4;
4319 g_CWB_PEOPLE_GROUP_SEGMENT5 := l_cwb.PEOPLE_GROUP_SEGMENT5;
4320 g_CWB_PEOPLE_GROUP_SEGMENT6 := l_cwb.PEOPLE_GROUP_SEGMENT6;
4321 g_CWB_PEOPLE_GROUP_SEGMENT7 := l_cwb.PEOPLE_GROUP_SEGMENT7;
4322 g_CWB_PEOPLE_GROUP_SEGMENT8 := l_cwb.PEOPLE_GROUP_SEGMENT8;
4323 g_CWB_PEOPLE_GROUP_SEGMENT9 := l_cwb.PEOPLE_GROUP_SEGMENT9;
4324 g_CWB_Person_BASE_SALARY_FREQ := l_cwb.BASE_SALARY_FREQUENCY;
4325 g_CWB_Person_POST_PROCESS_Stat := l_cwb.POST_PROCESS_Stat_cd;
4326 g_CWB_Person_START_DATE := l_cwb.START_DATE;
4327 g_CWB_Person_ADJUSTED_SVC_DATE := l_cwb.ADJUSTED_SVC_DATE;
4328 g_CWB_Person_Assg_ATTRIBUTE1 := l_cwb.Ass_ATTRIBUTE1;
4329 g_CWB_Person_Assg_ATTRIBUTE2 := l_cwb.Ass_ATTRIBUTE2;
4330 g_CWB_Person_Assg_ATTRIBUTE3 := l_cwb.Ass_ATTRIBUTE3;
4331 g_CWB_Person_Assg_ATTRIBUTE4 := l_cwb.Ass_ATTRIBUTE4;
4332 g_CWB_Person_Assg_ATTRIBUTE5 := l_cwb.Ass_ATTRIBUTE5;
4333 g_CWB_Person_Assg_ATTRIBUTE6 := l_cwb.Ass_ATTRIBUTE6;
4334 g_CWB_Person_Assg_ATTRIBUTE7 := l_cwb.Ass_ATTRIBUTE7;
4335 g_CWB_Person_Assg_ATTRIBUTE8 := l_cwb.Ass_ATTRIBUTE8;
4336 g_CWB_Person_Assg_ATTRIBUTE9 := l_cwb.Ass_ATTRIBUTE9;
4337 g_CWB_Person_Assg_ATTRIBUTE10 := l_cwb.Ass_ATTRIBUTE10;
4338 g_CWB_Person_Assg_ATTRIBUTE11 := l_cwb.Ass_ATTRIBUTE11;
4339 g_CWB_Person_Assg_ATTRIBUTE12 := l_cwb.Ass_ATTRIBUTE12;
4340 g_CWB_Person_Assg_ATTRIBUTE13 := l_cwb.Ass_ATTRIBUTE13;
4341 g_CWB_Person_Assg_ATTRIBUTE14 := l_cwb.Ass_ATTRIBUTE14;
4342 g_CWB_Person_Assg_ATTRIBUTE15 := l_cwb.Ass_ATTRIBUTE15;
4343 g_CWB_Person_Assg_ATTRIBUTE16 := l_cwb.Ass_ATTRIBUTE16;
4344 g_CWB_Person_Assg_ATTRIBUTE17 := l_cwb.Ass_ATTRIBUTE17;
4345 g_CWB_Person_Assg_ATTRIBUTE18 := l_cwb.Ass_ATTRIBUTE18;
4346 g_CWB_Person_Assg_ATTRIBUTE19 := l_cwb.Ass_ATTRIBUTE19;
4347 g_CWB_Person_Assg_ATTRIBUTE20 := l_cwb.Ass_ATTRIBUTE20;
4348 g_CWB_Person_Assg_ATTRIBUTE21 := l_cwb.Ass_ATTRIBUTE21;
4349 g_CWB_Person_Assg_ATTRIBUTE22 := l_cwb.Ass_ATTRIBUTE22;
4350 g_CWB_Person_Assg_ATTRIBUTE23 := l_cwb.Ass_ATTRIBUTE23;
4351 g_CWB_Person_Assg_ATTRIBUTE24 := l_cwb.Ass_ATTRIBUTE24;
4352 g_CWB_Person_Assg_ATTRIBUTE25 := l_cwb.Ass_ATTRIBUTE25;
4353 g_CWB_Person_Assg_ATTRIBUTE26 := l_cwb.Ass_ATTRIBUTE26;
4354 g_CWB_Person_Assg_ATTRIBUTE27 := l_cwb.Ass_ATTRIBUTE27;
4355 g_CWB_Person_Assg_ATTRIBUTE28 := l_cwb.Ass_ATTRIBUTE28;
4356 g_CWB_Person_Assg_ATTRIBUTE29 := l_cwb.Ass_ATTRIBUTE29;
4357 g_CWB_Person_Assg_ATTRIBUTE30 := l_cwb.Ass_ATTRIBUTE30;
4358 g_CWB_Person_Info_ATTRIBUTE1 := l_cwb.CPI_ATTRIBUTE1;
4359 g_CWB_Person_Info_ATTRIBUTE2 := l_cwb.CPI_ATTRIBUTE2;
4360 g_CWB_Person_Info_ATTRIBUTE3 := l_cwb.CPI_ATTRIBUTE3;
4361 g_CWB_Person_Info_ATTRIBUTE4 := l_cwb.CPI_ATTRIBUTE4;
4362 g_CWB_Person_Info_ATTRIBUTE5 := l_cwb.CPI_ATTRIBUTE5;
4363 g_CWB_Person_Info_ATTRIBUTE6 := l_cwb.CPI_ATTRIBUTE6;
4364 g_CWB_Person_Info_ATTRIBUTE7 := l_cwb.CPI_ATTRIBUTE7;
4365 g_CWB_Person_Info_ATTRIBUTE8 := l_cwb.CPI_ATTRIBUTE8;
4366 g_CWB_Person_Info_ATTRIBUTE9 := l_cwb.CPI_ATTRIBUTE9;
4367 g_CWB_Person_Info_ATTRIBUTE10 := l_cwb.CPI_ATTRIBUTE10;
4368 g_CWB_Person_Info_ATTRIBUTE11 := l_cwb.CPI_ATTRIBUTE11;
4369 g_CWB_Person_Info_ATTRIBUTE12 := l_cwb.CPI_ATTRIBUTE12;
4370 g_CWB_Person_Info_ATTRIBUTE13 := l_cwb.CPI_ATTRIBUTE13;
4371 g_CWB_Person_Info_ATTRIBUTE14 := l_cwb.CPI_ATTRIBUTE14;
4372 g_CWB_Person_Info_ATTRIBUTE15 := l_cwb.CPI_ATTRIBUTE15;
4373 g_CWB_Person_Info_ATTRIBUTE16 := l_cwb.CPI_ATTRIBUTE16;
4374 g_CWB_Person_Info_ATTRIBUTE17 := l_cwb.CPI_ATTRIBUTE17;
4375 g_CWB_Person_Info_ATTRIBUTE18 := l_cwb.CPI_ATTRIBUTE18;
4376 g_CWB_Person_Info_ATTRIBUTE19 := l_cwb.CPI_ATTRIBUTE19;
4377 g_CWB_Person_Info_ATTRIBUTE20 := l_cwb.CPI_ATTRIBUTE20;
4378 g_CWB_Person_Info_ATTRIBUTE21 := l_cwb.CPI_ATTRIBUTE21;
4379 g_CWB_Person_Info_ATTRIBUTE22 := l_cwb.CPI_ATTRIBUTE22;
4380 g_CWB_Person_Info_ATTRIBUTE23 := l_cwb.CPI_ATTRIBUTE23;
4381 g_CWB_Person_Info_ATTRIBUTE24 := l_cwb.CPI_ATTRIBUTE24;
4382 g_CWB_Person_Info_ATTRIBUTE25 := l_cwb.CPI_ATTRIBUTE25;
4383 g_CWB_Person_Info_ATTRIBUTE26 := l_cwb.CPI_ATTRIBUTE26;
4384 g_CWB_Person_Info_ATTRIBUTE27 := l_cwb.CPI_ATTRIBUTE27;
4385 g_CWB_Person_Info_ATTRIBUTE28 := l_cwb.CPI_ATTRIBUTE28;
4386 g_CWB_Person_Info_ATTRIBUTE29 := l_cwb.CPI_ATTRIBUTE29;
4387 g_CWB_Person_Info_ATTRIBUTE30 := l_cwb.CPI_ATTRIBUTE30;
4388 g_CWB_Person_CUSTOM_SEGMENT1 := l_cwb.CUSTOM_SEGMENT1;
4389 g_CWB_Person_CUSTOM_SEGMENT2 := l_cwb.CUSTOM_SEGMENT2;
4390 g_CWB_Person_CUSTOM_SEGMENT3 := l_cwb.CUSTOM_SEGMENT3;
4391 g_CWB_Person_CUSTOM_SEGMENT4 := l_cwb.CUSTOM_SEGMENT4;
4392 g_CWB_Person_CUSTOM_SEGMENT5 := l_cwb.CUSTOM_SEGMENT5;
4393 g_CWB_Person_CUSTOM_SEGMENT6 := l_cwb.CUSTOM_SEGMENT6;
4394 g_CWB_Person_CUSTOM_SEGMENT7 := l_cwb.CUSTOM_SEGMENT7;
4395 g_CWB_Person_CUSTOM_SEGMENT8 := l_cwb.CUSTOM_SEGMENT8;
4396 g_CWB_Person_CUSTOM_SEGMENT9 := l_cwb.CUSTOM_SEGMENT9;
4397 g_CWB_Person_CUSTOM_SEGMENT10 := l_cwb.CUSTOM_SEGMENT10;
4398 g_CWB_Person_CUSTOM_SEGMENT11 := l_cwb.CUSTOM_SEGMENT11;
4399 g_CWB_Person_CUSTOM_SEGMENT13 := l_cwb.CUSTOM_SEGMENT12;
4400 g_CWB_Person_CUSTOM_SEGMENT14 := l_cwb.CUSTOM_SEGMENT13;
4401 g_CWB_Person_CUSTOM_SEGMENT12 := l_cwb.CUSTOM_SEGMENT14;
4402 g_CWB_Person_CUSTOM_SEGMENT15 := l_cwb.CUSTOM_SEGMENT15;
4403 g_CWB_Person_FEEDBACK_RATING := l_cwb.FEEDBACK_RATING;
4404 g_CWB_Person_FREQUENCY := l_cwb.FREQUENCY;
4405 g_CWB_Person_Grade_MAX_VAL := l_cwb.GRD_MAX_VAL;
4406 g_CWB_Person_Grade_MID_POINT := l_cwb.GRD_MID_POINT;
4407 g_CWB_Person_Grade_MIN_VAL := l_cwb.GRD_MIN_VAL;
4408 g_CWB_Person_GRADE_ANN_FACTOR := l_cwb.GRADE_ANNULIZATION_FACTOR;
4409 g_CWB_Person_Grade_COMPARATIO := l_cwb.Grd_COMPARATIO;
4410 g_CWB_Person_LEGISLATION := l_cwb.LEGISLATION_CODE;
4411 g_CWB_Person_NORMAL_HOURS := l_cwb.NORMAL_HOURS;
4412 g_CWB_Person_ORIG_START_DATE := l_cwb.ORIGINAL_START_DATE;
4413 g_CWB_Person_PAY_ANNUL_FACTOR := l_cwb.PAY_ANNULIZATION_FACTOR;
4414 g_CWB_Person_SUP_BRIEF_NAME := l_cwb.SUPERVISOR_BRIEF_NAME;
4415 g_CWB_Person_SUP_CUSTOM_NAME := l_cwb.SUPERVISOR_CUSTOM_NAME;
4416 g_CWB_Person_SUP_FULL_NAME := l_cwb.SUPERVISOR_FULL_NAME;
4417 g_CWB_Person_YEARS_EMPLOYED := l_cwb.YEARS_EMPLOYED;
4418 g_CWB_Person_YEARS_IN_GRADE := l_cwb.YEARS_IN_GRADE;
4419 g_CWB_Person_YEARS_IN_POS := l_cwb.YEARS_IN_POSITION;
4420 g_CWB_Person_YEARS_IN_JOB := l_cwb.YEARS_IN_JOB;
4421 g_CWB_Person_PAYROLL_NAME := l_cwb.payroll_name ;
4422
4423 --- business group name
4424 open c_bg_name(l_cwb.business_group_id ) ;
4425 fetch c_bg_name into g_CWB_Person_BG_Name ;
4426 close c_bg_name ;
4427
4428 open c_org_name(l_cwb.organization_id) ;
4429 fetch c_org_name into g_CWB_Person_ORG_name ;
4430 close c_org_name ;
4431
4432 open c_job(l_Cwb.job_id) ;
4433 fetch c_job into g_CWB_Person_JOB_name ;
4434 close c_job ;
4435
4436 open c_loc(l_Cwb.location_id) ;
4437 fetch c_loc into g_CWB_Person_location ;
4438 close c_loc ;
4439
4440 open c_pos(l_Cwb.position_id) ;
4441 fetch c_pos into g_CWB_Person_POSITION ;
4442 close c_pos ;
4443
4444 open c_grade(l_Cwb.grade_id) ;
4445 fetch c_grade into g_CWB_Person_GRADE_name ;
4446 close c_grade ;
4447
4448 open c_payr(l_Cwb.pay_rate_id) ;
4449 fetch c_payr into g_CWB_Person_PAY_RATE ;
4450 close c_payr ;
4451
4452 open c_asg_status(l_cwb.ASSIGNMENT_STATUS_TYPE_ID) ;
4453 fetch c_asg_status into g_CWB_Person_STATUS_TYPE ;
4454 close c_asg_status ;
4455
4456 open c_hr_lkup('EMP_CAT', l_cwb.EMP_CATEGORY) ;
4457 fetch c_hr_lkup into g_CWB_Person_EMPloyee_CATEGORY ;
4458 close c_hr_lkup ;
4459
4460 open c_hr_lkup('BEN_CWB_QUAR_IN_GRD', l_cwb.GRD_QUARTILE) ;
4461 fetch c_hr_lkup into g_CWB_Person_Grade_QUARTILE ;
4462 close c_hr_lkup ;
4463
4464 open c_hr_lkup('BEN_PER_IN_LER_STAT', l_cwb.PER_IN_LER_STAT_CD) ;
4465 fetch c_hr_lkup into g_CWB_Life_Event_status ;
4466 close c_hr_lkup ;
4467
4468 open c_pln (g_cwb_per_group_pl_id , g_CWB_Life_Event_Occurred_Date );
4469 fetch c_pln into g_cwb_group_plan_name ;
4470 close c_pln ;
4471
4472 --- from transaction table
4473 -- performance rating
4474 open c_tran( l_cwb.ASSIGNMENT_ID,
4475 'CWBPERF'||to_char(l_cwb.PERF_REVW_STRT_DT ,'RRRR/MM/DD')||nvl(l_cwb.EMP_INTERVIEW_TYP_CD,'')
4476 ) ;
4477 fetch c_tran into l_tran ;
4478 close c_tran ;
4479 if l_tran.ATTRIBUTE3 is not null then
4480 open c_hr_lkup('PERFORMANCE_RATING', l_tran.ATTRIBUTE3) ;
4481 fetch c_hr_lkup into g_CWB_new_Perf_rating ;
4482 close c_hr_lkup ;
4483 end if ;
4484 g_CWB_Person_PERF_RATING_DATE := l_cwb.PERF_REVW_STRT_DT ;
4485 if l_cwb.EMP_INTERVIEW_TYP_CD is not null then
4486 open c_hr_lkup('EMP_INTERVIEW_TYPE', l_cwb.EMP_INTERVIEW_TYP_CD) ;
4487 fetch c_hr_lkup into g_CWB_Persom_PERF_RATING_TYPE ;
4488 close c_hr_lkup ;
4489 end if ;
4490
4491 l_tran := null ;
4492 open c_tran( l_cwb.ASSIGNMENT_ID,
4493 'CWBASG'||to_char(l_cwb.ASG_UPDT_EFF_DATE ,'RRRR/MM/DD'))
4494 ;
4495 fetch c_tran into l_tran ;
4496 close c_tran ;
4497
4498 if l_tran.ATTRIBUTE3 is not null then
4499 open c_hr_lkup('EMP_ASSIGN_REASON', l_tran.ATTRIBUTE3) ;
4500 fetch c_hr_lkup into g_cwb_nw_chg_reason ;
4501 close c_hr_lkup ;
4502 end if ;
4503
4504 if l_tran.ATTRIBUTE5 is not null then
4505 open c_job(l_tran.ATTRIBUTE5) ;
4506 fetch c_job into g_CWB_new_Job_name ;
4507 close c_job ;
4508 end if ;
4509
4510 if l_tran.ATTRIBUTE6 is not null then
4511 open c_pos(l_tran.ATTRIBUTE6) ;
4512 fetch c_pos into g_CWB_new_Postion_name ;
4513 close c_pos ;
4514 end if ;
4515
4516 if l_tran.ATTRIBUTE7 is not null then
4517 open c_grade(l_tran.ATTRIBUTE7) ;
4518 fetch c_grade into g_CWB_new_Grade_name ;
4519 close c_grade ;
4520 end if ;
4521
4522 if l_tran.ATTRIBUTE8 is not null then
4523 open c_groups(l_tran.ATTRIBUTE8) ;
4524 fetch c_groups into g_CWB_new_Group_name ;
4525 close c_groups ;
4526 end if ;
4527
4528
4529
4530 /*
4531 g_CWB_new_Group_name := null ;
4532 */
4533
4534 ----
4535 g_rcd_seq := 1;
4536 --
4537 if nvl(ben_extract.g_spcl_hndl_flag,'X') <> 'Y' then -- normal processing
4538 --
4539 process_ext_levels(
4540 p_person_id => p_person_id,
4541 p_ext_rslt_id => p_ext_rslt_id,
4542 p_ext_file_id => p_ext_file_id,
4543 p_data_typ_cd => p_data_typ_cd,
4544 p_ext_typ_cd => p_ext_typ_cd,
4545 p_business_group_id => p_business_group_id,
4546 p_effective_date => g_effective_date
4547 );
4548 else -- special handling flag tells us that it is an ansi 834 extract.
4549 --
4550 ben_ext_ansi.main(
4551 p_person_id => p_person_id,
4552 p_ext_rslt_id => p_ext_rslt_id,
4553 p_ext_file_id => p_ext_file_id,
4554 p_data_typ_cd => p_data_typ_cd,
4555 p_ext_typ_cd => p_ext_typ_cd,
4556 p_ext_crit_prfl_id => p_ext_crit_prfl_id,
4557 p_business_group_id => p_business_group_id,
4558 p_effective_date => g_benefits_ext_dt
4559 );
4560 end if;
4561 --
4562 g_trans_num := g_trans_num + 1;
4563 --
4564 END IF; -- l_include = 'Y'
4565
4566 end loop ;
4567
4568
4569
4570 END IF; -- extract type
4571
4572 if g_debug then
4573 hr_utility.set_location('Exiting'||l_proc, 15);
4574 end if;
4575 --
4576 EXCEPTION
4577 --
4578 WHEN detail_error THEN
4579 --
4580 ROLLBACK TO cur_transaction;
4581 l_err_message := ben_ext_fmt.get_error_msg(g_err_num,g_err_name,g_elmt_name ) ;
4582 if g_debug then
4583 hr_utility.set_location('err msg ' || l_err_message, 99.98 );
4584 end if;
4585 write_error(
4586 p_err_num => g_err_num,
4587 p_err_name => l_err_message,
4588 p_typ_cd => 'E',
4589 p_request_id => ben_extract.g_request_id,
4590 p_ext_rslt_id => p_ext_rslt_id
4591 );
4592
4593 When detail_restart_error then
4594
4595 ROLLBACK TO cur_transaction;
4596 l_err_message := ben_ext_fmt.get_error_msg(g_err_num,g_err_name,g_elmt_name ) ;
4597 if g_debug then
4598 hr_utility.set_location('err msg ' || l_err_message, 99.98 );
4599 end if;
4600 write_error(
4601 p_err_num => g_err_num,
4602 p_err_name => l_err_message,
4603 p_typ_cd => 'E',
4604 p_request_id => ben_extract.g_request_id,
4605 p_ext_rslt_id => p_ext_rslt_id
4606 );
4607 Raise ;
4608
4609 WHEN required_error THEN
4610 --
4611 ROLLBACK TO cur_transaction;
4612
4613 WHEN Others THEN --- any unexpted error
4614
4615 ROLLBACK TO cur_transaction;
4616 -- just error the person and go ahead with other person
4617 -- the log will be created in extract pkg , for only no data found log
4618 -- error
4619 if g_err_num = 94102 then
4620 l_err_message := ben_ext_fmt.get_error_msg(g_err_num,g_err_name) ;
4621 write_error(
4622 p_err_num => g_err_num,
4623 p_err_name => l_err_message,
4624 p_typ_cd => 'E',
4625 p_request_id => ben_extract.g_request_id,
4626 p_ext_rslt_id => p_ext_rslt_id
4627 );
4628 end if ;
4629
4630 Raise ; -- raise the exception to benxcrit
4631
4632
4633 --
4634 End process_ext_person;
4635 --
4636 -- ----------------------------------------------------------------------------
4637 -- |------< process_ext_levels >----------------------------------------------|
4638 -- ----------------------------------------------------------------------------
4639 -- This procedure will process extract levels and call ben_ext_fmt.process_ext_recs
4640 -- for each record level according to the extract definition.
4641 --
4642 -- For simplicity and due to the time constraint it is assummed that a given person
4643 -- can only be a participant or a dependent (not both) as well as the fact that a
4644 -- person can not be a dependent of more that one particiant for a particular plan.
4645 -- This will mater only when dependendents are processed as people.
4646 -- This restriction will be addressed in the future release.
4647 --
4648 Procedure process_ext_levels(
4649 p_person_id in number,
4650 p_ext_rslt_id in number,
4651 p_ext_file_id in number,
4652 p_data_typ_cd in varchar2,
4653 p_ext_typ_cd in varchar2,
4654 p_business_group_id in number,
4655 p_effective_date in date
4656 ) IS
4657 --
4658 l_proc varchar2(72);
4659 --
4660 l_dummy varchar2(30);
4661 l_rec_lvl_cd varchar2(30);
4662 l_cursor_cd varchar2(30);
4663 l_comp_incl varchar2(1) := 'Y';
4664 l_rollback boolean;
4665 --
4666 --
4667 cursor purged_rslt_c (l_pl_id number) is
4668 select
4669 pl.name pl_name,
4670 -- opt.opt_id opt_id,
4671 -- opt.name opt_name,
4672 -- enrt.enrt_cvg_strt_dt cvg_strt_dt,
4673 -- enrt.enrt_cvg_thru_dt cvg_thru_dt,
4674 -- enrt.bnft_amt bnft_amt,
4675 -- enrt.pgm_id pgm_id,
4676 -- pgm.name pgm_name,
4677 pl.pl_typ_id pl_typ_id,
4678 ptp.name pl_typ_name
4679 from ben_pl_f pl,
4680 -- ben_oipl_f oipl,
4681 -- ben_opt_f opt,
4682 -- ben_pgm_f pgm,
4683 ben_pl_typ_f ptp
4684 where
4685 pl.pl_id = l_pl_id
4686 and g_effective_date between pl.effective_start_date
4687 and pl.effective_end_date
4688 --
4689 and pl.pl_typ_id = ptp.pl_typ_id
4690 and g_effective_date between nvl(ptp.effective_start_date, g_effective_date)
4691 and nvl(ptp.effective_end_date, g_effective_date)
4692 ;
4693
4694 --
4695 begin
4696 --
4697 g_debug := hr_utility.debug_enabled;
4698 if g_debug then
4699 l_proc := g_package||'process_ext_levels';
4700 hr_utility.set_location('Entering'||l_proc, 5);
4701 end if;
4702 --
4703 -- Initialize rollback flag.
4704 --
4705 l_rollback:=FALSE;
4706
4707 if g_debug then
4708 hr_utility.set_location('ben_extract.g_per_lvl ' || ben_extract.g_per_lvl ,99 );
4709 end if;
4710 --
4711 IF ben_extract.g_per_lvl = 'Y' THEN
4712 --
4713 -- Process Personal Level Detail Records
4714 --
4715 --
4716 if g_debug then
4717 hr_utility.set_location(' ben_ext_fmt.process_ext_recs',99 );
4718 end if;
4719 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
4720 p_ext_file_id => p_ext_file_id,
4721 p_data_typ_cd => p_data_typ_cd,
4722 p_ext_typ_cd => p_ext_typ_cd,
4723 p_rcd_typ_cd => 'D',
4724 p_low_lvl_cd => 'P',
4725 p_person_id => p_person_id,
4726 p_chg_evt_cd => g_chg_evt_cd,
4727 p_business_group_id => p_business_group_id,
4728 p_effective_date => g_effective_date
4729 );
4730
4731 --
4732 --
4733 END IF;
4734 --
4735 -- create enrollment, dependent and beneficiary level rows
4736 -- =======================================================
4737 --RCHASE
4738 --IF nvl(g_chg_evt_cd, '*') <> 'TBBC' then
4739 --
4740 -- extract enrollment levels
4741 --
4742 IF (ben_extract.g_enrt_lvl = 'Y' OR ben_extract.g_dpnt_lvl = 'Y' OR ben_extract.g_bnf_lvl = 'Y' OR
4743 ben_extract.g_actn_lvl = 'Y' or ben_extract.g_prem_lvl = 'Y' ) THEN
4744 --
4745 if g_debug then
4746 hr_utility.set_location(' ben_ext_enrt.main',99 );
4747 end if;
4748 ben_ext_enrt.main(
4749 p_person_id => p_person_id,
4750 p_ext_rslt_id => p_ext_rslt_id,
4751 p_ext_file_id => p_ext_file_id,
4752 p_data_typ_cd => p_data_typ_cd,
4753 p_ext_typ_cd => p_ext_typ_cd,
4754 p_chg_evt_cd => g_chg_evt_cd,
4755 p_business_group_id => p_business_group_id,
4756 p_effective_date => g_benefits_ext_dt);
4757 END IF;
4758 --
4759 --
4760 --RCHASE
4761 --ELSIF nvl(g_chg_evt_cd, '*') = 'TBBC' and ben_extract.g_enrt_lvl = 'Y' then
4762 --
4763 -- open purged_rslt_c(g_chg_pl_id);
4764 --
4765 -- fetch purged_rslt_c into
4766 -- g_enrt_pl_name,
4767 -- g_enrt_pl_typ_id,
4768 -- g_enrt_pl_typ_name;
4769 --
4770 -- ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
4771 -- p_ext_file_id => p_ext_file_id,
4772 -- p_data_typ_cd => p_data_typ_cd,
4773 -- p_ext_typ_cd => p_ext_typ_cd,
4774 -- p_rcd_typ_cd => 'D',
4775 -- p_low_lvl_cd => 'E',
4776 -- p_person_id => p_person_id,
4777 -- p_chg_evt_cd => g_chg_evt_cd,
4778 -- p_business_group_id => p_business_group_id,
4779 -- p_effective_date => g_effective_date
4780 -- );
4781 --
4782 --END IF; -- part type
4783 --
4784 -- create eligibility extract rows
4785 -- =========================================
4786 if ben_extract.g_elig_lvl = 'Y' or ben_extract.g_eligdpnt_lvl = 'Y' then
4787 --
4788 ben_ext_elig.main(
4789 p_person_id => p_person_id,
4790 p_ext_rslt_id => p_ext_rslt_id,
4791 p_ext_file_id => p_ext_file_id,
4792 p_data_typ_cd => p_data_typ_cd,
4793 p_ext_typ_cd => p_ext_typ_cd,
4794 p_chg_evt_cd => g_chg_evt_cd,
4795 p_business_group_id => p_business_group_id,
4796 p_effective_date => g_benefits_ext_dt
4797 );
4798 --
4799 --
4800 end if;
4801 --
4802 -- create flex credit extract rows
4803 -- =========================================
4804 if ben_extract.g_flex_lvl = 'Y' then
4805 --
4806 ben_ext_flcr.main(
4807 p_person_id => p_person_id,
4808 p_ext_rslt_id => p_ext_rslt_id,
4809 p_ext_file_id => p_ext_file_id,
4810 p_data_typ_cd => p_data_typ_cd,
4811 p_ext_typ_cd => p_ext_typ_cd,
4812 p_chg_evt_cd => g_chg_evt_cd,
4813 p_business_group_id => p_business_group_id,
4814 p_effective_date => g_benefits_ext_dt
4815 );
4816 --
4817 --
4818 end if;
4819 --
4820 -- create payroll extract rows
4821 -- ================================
4822 if ben_extract.g_payroll_lvl = 'Y' then
4823 --
4824 ben_ext_payroll.main(
4825 p_person_id => p_person_id,
4826 p_ext_rslt_id => p_ext_rslt_id,
4827 p_ext_file_id => p_ext_file_id,
4828 p_data_typ_cd => p_data_typ_cd,
4829 p_ext_typ_cd => p_ext_typ_cd,
4830 p_chg_evt_cd => g_chg_evt_cd,
4831 p_business_group_id => p_business_group_id,
4832 p_effective_date => g_person_ext_dt
4833 );
4834 --
4835 end if;
4836 --
4837 -- create run result extract rows
4838 -- ================================
4839 if ben_extract.g_runrslt_lvl = 'Y' then
4840 --
4841 ben_ext_runrslt.main(
4842 p_person_id => p_person_id,
4843 p_ext_rslt_id => p_ext_rslt_id,
4844 p_ext_file_id => p_ext_file_id,
4845 p_data_typ_cd => p_data_typ_cd,
4846 p_ext_typ_cd => p_ext_typ_cd,
4847 p_chg_evt_cd => g_chg_evt_cd,
4848 p_business_group_id => p_business_group_id,
4849 p_effective_date => g_person_ext_dt
4850 );
4851 --
4852 end if;
4853 --
4854 -- create contact extract rows
4855 -- ================================
4856 if ben_extract.g_contact_lvl = 'Y' then
4857 --
4858 ben_ext_contact.main(
4859 p_person_id => p_person_id,
4860 p_ext_rslt_id => p_ext_rslt_id,
4861 p_ext_file_id => p_ext_file_id,
4862 p_data_typ_cd => p_data_typ_cd,
4863 p_ext_typ_cd => p_ext_typ_cd,
4864 p_chg_evt_cd => g_chg_evt_cd,
4865 p_business_group_id => p_business_group_id,
4866 p_effective_date => g_person_ext_dt
4867 );
4868 --
4869 end if;
4870
4871 --- cwb
4872 if p_data_typ_cd = 'CW' THEN
4873
4874 hr_utility.set_location( ' bdgt lvl ' || ben_extract.g_cwb_bdgt_lvl , 99 );
4875
4876 if ben_extract.g_cwb_bdgt_lvl = 'Y' then
4877 ben_ext_cwb.extract_person_groups
4878 ( p_person_id => p_person_id,
4879 p_per_in_ler_id => g_cwb_per_group_per_in_ler_id,
4880 p_ext_rslt_id => p_ext_rslt_id,
4881 p_ext_file_id => p_ext_file_id,
4882 p_data_typ_cd => p_data_typ_cd,
4883 p_ext_typ_cd => p_ext_typ_cd,
4884 p_business_group_id => p_business_group_id,
4885 p_effective_date => g_person_ext_dt) ;
4886 end if ;
4887
4888 if ben_extract.g_cwb_awrd_lvl = 'Y' then
4889 ben_ext_cwb.extract_person_rates
4890 ( p_person_id => p_person_id,
4891 p_per_in_ler_id => g_cwb_per_group_per_in_ler_id,
4892 p_ext_rslt_id => p_ext_rslt_id,
4893 p_ext_file_id => p_ext_file_id,
4894 p_data_typ_cd => p_data_typ_cd,
4895 p_ext_typ_cd => p_ext_typ_cd,
4896 p_business_group_id => p_business_group_id,
4897 p_effective_date => g_person_ext_dt) ;
4898 end if ;
4899 end if ;
4900
4901
4902 --
4903
4904
4905 if ben_extract.g_otl_summ_lvl = 'Y' then
4906
4907 hxc_ext_timecard.process_summary (
4908 p_person_id => p_person_id,
4909 p_ext_rslt_id => p_ext_rslt_id,
4910 p_ext_file_id => p_ext_file_id,
4911 p_ext_crit_prfl_id => NULL,
4912 p_data_typ_cd => p_data_typ_cd,
4913 p_ext_typ_cd => p_ext_typ_cd,
4914 p_effective_date => p_effective_date );
4915
4916 end if;
4917
4918 --
4919 /* this validation is done on low level , this is changed to do in record level
4920 this validation moved to benxfrmt.pkb
4921 FOR i in ben_extract.gtt_rcd_rqd_vals.first .. ben_extract.gtt_rcd_rqd_vals.last LOOP
4922 --
4923 IF NOT ben_extract.gtt_rcd_rqd_vals(i).rcd_found
4924 THEN
4925 l_rollback := TRUE; -- raise required_error;
4926 ELSIF ben_extract.gtt_rcd_rqd_vals(1).low_lvl_cd <> 'NOREQDRCD'
4927 THEN
4928 ben_extract.gtt_rcd_rqd_vals(i).rcd_found := FALSE; -- reset the value
4929 END IF;
4930 --
4931 END LOOP;
4932 */
4933
4934
4935 -- validate the mandatory for low level in sequenc
4936 FOR i in ben_extract.gtt_rcd_rqd_vals_seq.first .. ben_extract.gtt_rcd_rqd_vals_seq.last LOOP
4937 --
4938 If NOT ben_extract.gtt_rcd_rqd_vals_seq(i).rcd_found THEN
4939 hr_utility.set_location('Mandatory failed '||ben_extract.gtt_rcd_rqd_vals_seq(i).low_lvl_cd || ' '||
4940 ben_extract.gtt_rcd_rqd_vals_seq(i).seq_num , 15);
4941 l_rollback := TRUE; -- raise required_error;
4942 end if ;
4943 if ben_extract.gtt_rcd_rqd_vals_seq(1).low_lvl_cd <> 'NOREQDRCD' then
4944 ben_extract.gtt_rcd_rqd_vals_seq(i).rcd_found := FALSE; -- reset the value
4945 end if ;
4946 END LOOP;
4947 --
4948 IF l_rollback
4949 THEN
4950 RAISE required_error;
4951 END IF;
4952 --
4953 if g_debug then
4954 hr_utility.set_location('Exiting'||l_proc, 15);
4955 end if;
4956 --
4957 --
4958 End process_ext_levels;
4959 --
4960 --
4961 -- ----------------------------------------------------------------------------
4962 -- |------< init_detail_globals >---------------------------------------------|
4963 -- ----------------------------------------------------------------------------
4964 --
4965 Procedure init_detail_globals IS
4966 --
4967 l_proc varchar2(72);
4968 --
4969 --
4970 --
4971 begin
4972 --
4973 g_debug := hr_utility.debug_enabled;
4974 if g_debug then
4975 l_proc := g_package||'init_detail_globals';
4976 hr_utility.set_location('Entering'||l_proc, 5);
4977 end if;
4978 --
4979 --
4980 -- personal (25)
4981 --
4982 g_chg_evt_cd := null;
4983 g_chg_evt_source := null;
4984 g_chg_actl_dt := null;
4985 g_chg_eff_dt := null;
4986 g_chg_pl_id := null;
4987 g_chg_input_value_id := null;
4988 g_chg_old_val1 := null;
4989 g_chg_old_val2 := null;
4990 g_chg_old_val3 := null;
4991 g_chg_old_val4 := null;
4992 g_chg_old_val5 := null;
4993 g_chg_old_val6 := null;
4994 g_chg_new_val1 := null;
4995 g_chg_new_val2 := null;
4996 g_chg_new_val3 := null;
4997 g_chg_new_val4 := null;
4998 g_chg_new_val5 := null;
4999 g_chg_new_val6 := null;
5000 g_chg_enrt_rslt_id := null;
5001 g_chg_pl_id := null;
5002 g_chg_pay_table := null;
5003 g_chg_pay_column := null;
5004 g_chg_pay_mode := null;
5005 g_chg_update_type := null;
5006 g_chg_surrogate_key := null;
5007 g_chg_next_event_date := null;
5008 g_chg_pay_evt_index := null;
5009 --
5010 g_previous_last_name := null;
5011 g_previous_first_name := null;
5012 g_previous_middle_name := null;
5013 g_previous_suffix := null;
5014 g_previous_prefix := null;
5015 g_previous_ssn := null;
5016 g_previous_dob := null;
5017 g_previous_sex := null;
5018 --
5019 g_part_type := null;
5020 g_per_rlshp_type := null;
5021 g_part_ssn := null;
5022 --
5023 g_national_identifier := null;
5024 g_last_name := null;
5025 g_first_name := null;
5026 g_middle_names := null;
5027 g_full_name := null;
5028 g_suffix := null;
5029 g_prefix := null;
5030 g_title := null;
5031 g_sex := null;
5032 g_date_of_birth := null;
5033 g_data_verification_dt := null;
5034 g_marital_status := null;
5035 g_employee_category := null;
5036 g_registered_disabled_flag := null;
5037 g_student_status := null;
5038 g_date_of_death := null;
5039 g_employee_number := null;
5040 g_benefit_group_id := null;
5041 g_benefit_group := null;
5042 g_bng_flex_01 := null;
5043 g_bng_flex_02 := null;
5044 g_bng_flex_03 := null;
5045 g_bng_flex_04 := null;
5046 g_bng_flex_05 := null;
5047 g_bng_flex_06 := null;
5048 g_bng_flex_07 := null;
5049 g_bng_flex_08 := null;
5050 g_bng_flex_09 := null;
5051 g_bng_flex_10 := null;
5052 g_benefit_bal_vacation := null;
5053 g_benefit_bal_sickleave := null;
5054 g_benefit_bal_pension := null;
5055 g_benefit_bal_dfncntrbn := null;
5056 g_benefit_bal_wellness := null;
5057 g_per_attr_1 := null;
5058 g_per_attr_2 := null;
5059 g_per_attr_3 := null;
5060 g_per_attr_4 := null;
5061 g_per_attr_5 := null;
5062 g_per_attr_6 := null;
5063 g_per_attr_7 := null;
5064 g_per_attr_8 := null;
5065 g_per_attr_9 := null;
5066 g_per_attr_10 := null;
5067 --
5068 g_applicant_number := null;
5069 g_correspondence_language := null;
5070 g_email_address := null;
5071 g_known_as := null;
5072 g_mailstop := null;
5073 g_nationality := null;
5074 g_pre_name_adjunct := null;
5075 g_original_date_of_hire := null;
5076 g_uses_tobacco_flag := null;
5077 g_office_number := null;
5078 --
5079 g_prim_address_line_1 := null;
5080 g_prim_address_line_2 := null;
5081 g_prim_address_line_3 := null;
5082 g_prim_city := null;
5083 g_prim_state := null;
5084 g_prim_state_ansi := null;
5085 g_prim_postal_code := null;
5086 g_prim_country := null;
5087 g_prim_county := null;
5088 g_prim_region_3 := null;
5089 g_prim_address_date := null;
5090 g_prim_addr_service_area := null;
5091 --
5092 g_mail_address_line_1 := null;
5093 g_mail_address_line_2 := null;
5094 g_mail_address_line_3 := null;
5095 g_mail_city := null;
5096 g_mail_state := null;
5097 g_mail_postal_code := null;
5098 g_mail_country := null;
5099 g_mail_county := null;
5100 g_mail_region_3 := null;
5101 g_mail_address_date := null;
5102 --
5103 g_phone_home := null;
5104 g_phone_work := null;
5105 g_phone_fax := null;
5106 g_phone_mobile := null;
5107 --
5108 g_last_hire_date := null;
5109 g_actual_term_date := null;
5110 g_adjusted_svc_date := null;
5111 g_term_reason := null;
5112 --
5113 g_employee_status := null;
5114 g_employee_grade := null;
5115 g_grd_flex_01 := null;
5116 g_grd_flex_02 := null;
5117 g_grd_flex_03 := null;
5118 g_grd_flex_04 := null;
5119 g_grd_flex_05 := null;
5120 g_grd_flex_06 := null;
5121 g_grd_flex_07 := null;
5122 g_grd_flex_08 := null;
5123 g_grd_flex_09 := null;
5124 g_grd_flex_10 := null;
5125 g_employee_barg_unit := null;
5126 g_employee_organization := null;
5127 g_employee_grade_id := null;
5128 g_employee_organization_id := null;
5129 g_employee_status_id := null;
5130 g_location_id := null;
5131 g_location_code := null;
5132 g_location_addr1 := null;
5133 g_location_addr2 := null;
5134 g_location_addr3 := null;
5135 g_location_city := null;
5136 g_location_country := null;
5137 g_location_zip := null;
5138 g_location_region1 := null;
5139 g_location_region2 := null;
5140 g_location_region3 := null;
5141 -- org address
5142 g_org_location_addr1 := null ;
5143 g_org_location_addr2 := null ;
5144 g_org_location_addr3 := null ;
5145 g_org_location_city := null ;
5146 g_org_location_country := null ;
5147 g_org_location_zip := null ;
5148 g_org_location_region1 := null ;
5149 g_org_location_region2 := null ;
5150 g_org_location_region3 := null ;
5151 --
5152 g_alc_flex_01 := null;
5153 g_alc_flex_02 := null;
5154 g_alc_flex_03 := null;
5155 g_alc_flex_04 := null;
5156 g_alc_flex_05 := null;
5157 g_alc_flex_06 := null;
5158 g_alc_flex_07 := null;
5159 g_alc_flex_08 := null;
5160 g_alc_flex_09 := null;
5161 g_alc_flex_10 := null;
5162 g_asg_title := null;
5163 g_position_id := null;
5164 g_job_id := null;
5165 g_payroll_id := null;
5166 g_people_group_id := null;
5167 g_pay_basis_id := null;
5168 g_hourly_salaried_code := null;
5169 g_labour_union_member_flag := null;
5170 g_manager_flag := null;
5171 g_position := null;
5172 g_pos_flex_01 := null;
5173 g_pos_flex_02 := null;
5174 g_pos_flex_03 := null;
5175 g_pos_flex_04 := null;
5176 g_pos_flex_05 := null;
5177 g_pos_flex_06 := null;
5178 g_pos_flex_07 := null;
5179 g_pos_flex_08 := null;
5180 g_pos_flex_09 := null;
5181 g_pos_flex_10 := null;
5182 g_job := null;
5183 g_job_flex_01 := null;
5184 g_job_flex_02 := null;
5185 g_job_flex_03 := null;
5186 g_job_flex_04 := null;
5187 g_job_flex_05 := null;
5188 g_job_flex_06 := null;
5189 g_job_flex_07 := null;
5190 g_job_flex_08 := null;
5191 g_job_flex_09 := null;
5192 g_job_flex_10 := null;
5193 g_payroll := null;
5194 g_prl_flex_01 := null;
5195 g_prl_flex_02 := null;
5196 g_prl_flex_03 := null;
5197 g_prl_flex_04 := null;
5198 g_prl_flex_05 := null;
5199 g_prl_flex_06 := null;
5200 g_prl_flex_07 := null;
5201 g_prl_flex_08 := null;
5202 g_prl_flex_09 := null;
5203 g_prl_flex_10 := null;
5204 g_people_group := null;
5205 g_pay_basis := null;
5206 g_pbs_flex_01 := null;
5207 g_pbs_flex_02 := null;
5208 g_pbs_flex_03 := null;
5209 g_pbs_flex_04 := null;
5210 g_pbs_flex_05 := null;
5211 g_pbs_flex_06 := null;
5212 g_pbs_flex_07 := null;
5213 g_pbs_flex_08 := null;
5214 g_pbs_flex_09 := null;
5215 g_pbs_flex_10 := null;
5216 g_payroll_period_type := null;
5217 g_payroll_period_number := null;
5218 g_payroll_period_strtdt := null;
5219 g_payroll_period_enddt := null;
5220 g_payroll_costing := null;
5221 g_payroll_costing_id := null;
5222 g_payroll_consolidation_set := null;
5223 g_payroll_consolidation_set_id := null;
5224 g_asg_attr_1 := null;
5225 g_asg_attr_2 := null;
5226 g_asg_attr_3 := null;
5227 g_asg_attr_4 := null;
5228 g_asg_attr_5 := null;
5229 g_asg_attr_6 := null;
5230 g_asg_attr_7 := null;
5231 g_asg_attr_8 := null;
5232 g_asg_attr_9 := null;
5233 g_asg_attr_10 := null;
5234 --
5235 g_sup_full_name := null ;
5236 g_sup_employee_number := null ;
5237 g_asg_normal_hours := null ;
5238 g_asg_frequency := null ;
5239 g_asg_time_normal_start := null ;
5240 g_asg_time_normal_finish := null ;
5241 g_asg_supervisor_id := null ;
5242 g_base_salary := null ;
5243 g_asg_type := null ;
5244 --
5245 g_abs_reason_name := null;
5246 g_abs_category_name := null;
5247 g_abs_type_name := null;
5248 g_abs_reason := null;
5249 g_abs_category := null;
5250 g_abs_type := null;
5251 g_abs_start_dt := null;
5252 g_abs_end_dt := null;
5253 g_abs_duration := null;
5254 g_abs_last_update_date := null;
5255 g_abs_last_updated_by := null;
5256 g_abs_last_update_login := null;
5257 g_abs_created_by := null;
5258 g_abs_creation_date := null;
5259 g_abs_reason_cd := null; -- Bug 2841958
5260
5261 g_abs_flex_01 := null;
5262 g_abs_flex_02 := null;
5263 g_abs_flex_03 := null;
5264 g_abs_flex_04 := null;
5265 g_abs_flex_05 := null;
5266 g_abs_flex_06 := null;
5267 g_abs_flex_07 := null;
5268 g_abs_flex_08 := null;
5269 g_abs_flex_09 := null;
5270 g_abs_flex_10 := null;
5271 --
5272 g_prs_flex_01 := null;
5273 g_prs_flex_02 := null;
5274 g_prs_flex_03 := null;
5275 g_prs_flex_04 := null;
5276 g_prs_flex_05 := null;
5277 g_prs_flex_06 := null;
5278 g_prs_flex_07 := null;
5279 g_prs_flex_08 := null;
5280 g_prs_flex_09 := null;
5281 g_prs_flex_10 := null;
5282 --
5283 -- g_correspondence_language := null;
5284 -- g_work_telephone := null;
5285 -- g_nationality := null;
5286 -- g_email_address := null;
5287 --
5288 -- these globals are assigned value in this package, so initialized here
5289 g_enrt_pl_name := null;
5290 g_enrt_pl_typ_id := null;
5291 g_enrt_pl_typ_name := null;
5292 /* Start of Changes for WWBUG: 1828349 added */
5293 g_enrt_prtt_enrt_rslt_id := null;
5294 /* End of Changes for WWBUG: 1828349 added */
5295 --
5296 g_ee_pre_tax_cost := null;
5297 g_ee_after_tax_cost := null;
5298 g_ee_ttl_cost := null;
5299 g_er_ttl_cost := null;
5300 --
5301 g_per_in_ler_id := null;
5302 g_ler_id := null;
5303 g_ler_name := null;
5304 g_lf_evt_ocrd_dt := null;
5305 g_lf_evt_note_dt := null;
5306 --
5307 g_cm_type := null;
5308 g_cm_type_id := null;
5309 g_cm_lf_evt_ocrd_dt := null;
5310 g_cm_lf_evt := null;
5311 g_cm_lf_evt_id := null;
5312 g_cm_lf_evt_stat := null;
5313 g_cm_lf_evt_ntfn_dt := null;
5314 g_cm_trgr_proc_name := null;
5315 g_cm_trgr_proc_dt := null;
5316 g_cm_addr_line1 := null;
5317 g_cm_addr_line2 := null;
5318 g_cm_addr_line3 := null;
5319 g_cm_city := null;
5320 g_cm_state := null;
5321 g_cm_postal_code := null;
5322 g_cm_country := null;
5323 g_cm_county := null;
5324 g_cm_region_3 := null;
5325 g_cm_dlvry_instn_txt := null;
5326 g_cm_inspn_rqd_flag := null;
5327 g_cm_to_be_sent_dt := null;
5328 --
5329 g_per_cm_prvdd_id := null;
5330 g_per_cm_object_version_number := null;
5331 --
5332 g_cbra_ler_id := null;
5333 g_cbra_ler_name := null;
5334 g_cbra_strt_dt := null;
5335 g_cbra_end_dt := null;
5336 --
5337 g_flex_credit_provided := null;
5338 g_flex_credit_forfited := null;
5339 g_flex_credit_used := null;
5340 g_flex_credit_excess := null;
5341 --intializing other id
5342 g_assignment_id := null ;
5343 g_dpnt_cvrd_dpnt_id := null ;
5344 g_elig_dpnt_id := null ;
5345
5346 --- intialize cwb globals
5347 g_cwb_per_group_per_in_ler_id := null ;
5348 g_cwb_per_group_pl_id := null ;
5349 g_CWB_Person_FULL_NAME := null ;
5350 g_CWB_Person_Custom_Name := null ;
5351 g_CWB_Life_Event_Name := null ;
5352 g_CWB_Life_Event_Occurred_Date := null ;
5353 g_CWB_Person_EMAIL_DDRESS := null ;
5354 g_CWB_Person_EMPLOYEE_NUMBER := null ;
5355 g_CWB_Person_BASE_SALARY := null ;
5356 g_CWB_Person_Brief_Name := null ;
5357 g_CWB_Person_BG_Name := null ;
5358 g_CWB_Person_CHANGE_REASON := null ;
5359 g_CWB_PEOPLE_GROUP_NAME := null ;
5360 g_CWB_PEOPLE_GROUP_SEGMENT1 := null ;
5361 g_CWB_PEOPLE_GROUP_SEGMENT10 := null ;
5362 g_CWB_PEOPLE_GROUP_SEGMENT11 := null ;
5363 g_CWB_PEOPLE_GROUP_SEGMENT2 := null ;
5364 g_CWB_PEOPLE_GROUP_SEGMENT3 := null ;
5365 g_CWB_PEOPLE_GROUP_SEGMENT4 := null ;
5366 g_CWB_PEOPLE_GROUP_SEGMENT5 := null ;
5367 g_CWB_PEOPLE_GROUP_SEGMENT6 := null ;
5368 g_CWB_PEOPLE_GROUP_SEGMENT7 := null ;
5369 g_CWB_PEOPLE_GROUP_SEGMENT8 := null ;
5370 g_CWB_PEOPLE_GROUP_SEGMENT9 := null ;
5371 g_CWB_Persom_PERF_RATING_TYPE := null ;
5372 g_CWB_Person_PERF_RATING := null ;
5373 g_CWB_Person_BASE_SALARY_FREQ := null ;
5374 g_CWB_Person_EMPloyee_CATEGORY := null ;
5375 g_CWB_Person_Grade_COMPARATIO := null ;
5376 g_CWB_Person_POST_PROCESS_Stat := null ;
5377 g_CWB_Person_START_DATE := null ;
5378 g_CWB_Person_ADJUSTED_SVC_DATE := null ;
5379 g_CWB_Person_Assg_ATTRIBUTE1 := null ;
5380 g_CWB_Person_Assg_ATTRIBUTE10 := null ;
5381 g_CWB_Person_Assg_ATTRIBUTE11 := null ;
5382 g_CWB_Person_Assg_ATTRIBUTE12 := null ;
5383 g_CWB_Person_Assg_ATTRIBUTE13 := null ;
5384 g_CWB_Person_Assg_ATTRIBUTE14 := null ;
5385 g_CWB_Person_Assg_ATTRIBUTE15 := null ;
5386 g_CWB_Person_Assg_ATTRIBUTE16 := null ;
5387 g_CWB_Person_Assg_ATTRIBUTE17 := null ;
5388 g_CWB_Person_Assg_ATTRIBUTE18 := null ;
5389 g_CWB_Person_Assg_ATTRIBUTE19 := null ;
5390 g_CWB_Person_Assg_ATTRIBUTE2 := null ;
5391 g_CWB_Person_Assg_ATTRIBUTE20 := null ;
5392 g_CWB_Person_Assg_ATTRIBUTE21 := null ;
5393 g_CWB_Person_Assg_ATTRIBUTE22 := null ;
5394 g_CWB_Person_Assg_ATTRIBUTE23 := null ;
5395 g_CWB_Person_Assg_ATTRIBUTE24 := null ;
5396 g_CWB_Person_Assg_ATTRIBUTE25 := null ;
5397 g_CWB_Person_Assg_ATTRIBUTE26 := null ;
5398 g_CWB_Person_Assg_ATTRIBUTE28 := null ;
5399 g_CWB_Person_Assg_ATTRIBUTE29 := null ;
5400 g_CWB_Person_Assg_ATTRIBUTE3 := null ;
5401 g_CWB_Person_Assg_ATTRIBUTE30 := null ;
5402 g_CWB_Person_Assg_ATTRIBUTE4 := null ;
5403 g_CWB_Person_Assg_ATTRIBUTE5 := null ;
5404 g_CWB_Person_Assg_ATTRIBUTE6 := null ;
5405 g_CWB_Person_Assg_ATTRIBUTE7 := null ;
5406 g_CWB_Person_Assg_ATTRIBUTE8 := null ;
5407 g_CWB_Person_Assg_ATTRIBUTE9 := null ;
5408 g_CWB_Person_Assg_ATTRIBUTE27 := null ;
5409 g_CWB_Person_Info_ATTRIBUTE1 := null ;
5410 g_CWB_Person_Info_ATTRIBUTE10 := null ;
5411 g_CWB_Person_Info_ATTRIBUTE2 := null ;
5412 g_CWB_Person_Info_ATTRIBUTE3 := null ;
5413 g_CWB_Person_Info_ATTRIBUTE4 := null ;
5414 g_CWB_Person_Info_ATTRIBUTE5 := null ;
5415 g_CWB_Person_Info_ATTRIBUTE6 := null ;
5416 g_CWB_Person_Info_ATTRIBUTE7 := null ;
5417 g_CWB_Person_Info_ATTRIBUTE11 := null ;
5418 g_CWB_Person_Info_ATTRIBUTE12 := null ;
5419 g_CWB_Person_Info_ATTRIBUTE13 := null ;
5420 g_CWB_Person_Info_ATTRIBUTE14 := null ;
5421 g_CWB_Person_Info_ATTRIBUTE15 := null ;
5422 g_CWB_Person_Info_ATTRIBUTE16 := null ;
5423 g_CWB_Person_Info_ATTRIBUTE17 := null ;
5424 g_CWB_Person_Info_ATTRIBUTE18 := null ;
5425 g_CWB_Person_Info_ATTRIBUTE19 := null ;
5426 g_CWB_Person_Info_ATTRIBUTE20 := null ;
5427 g_CWB_Person_Info_ATTRIBUTE21 := null ;
5428 g_CWB_Person_Info_ATTRIBUTE22 := null ;
5429 g_CWB_Person_Info_ATTRIBUTE23 := null ;
5430 g_CWB_Person_Info_ATTRIBUTE24 := null ;
5431 g_CWB_Person_Info_ATTRIBUTE25 := null ;
5432 g_CWB_Person_Info_ATTRIBUTE26 := null ;
5433 g_CWB_Person_Info_ATTRIBUTE27 := null ;
5434 g_CWB_Person_Info_ATTRIBUTE28 := null ;
5435 g_CWB_Person_Info_ATTRIBUTE29 := null ;
5436 g_CWB_Person_Info_ATTRIBUTE30 := null ;
5437 g_CWB_Person_Info_ATTRIBUTE8 := null ;
5438 g_CWB_Person_Info_ATTRIBUTE9 := null ;
5439 g_CWB_Person_CUSTOM_SEGMENT1 := null ;
5440 g_CWB_Person_CUSTOM_SEGMENT10 := null ;
5441 g_CWB_Person_CUSTOM_SEGMENT11 := null ;
5442 g_CWB_Person_CUSTOM_SEGMENT13 := null ;
5443 g_CWB_Person_CUSTOM_SEGMENT14 := null ;
5444 g_CWB_Person_CUSTOM_SEGMENT2 := null ;
5445 g_CWB_Person_CUSTOM_SEGMENT4 := null ;
5446 g_CWB_Person_CUSTOM_SEGMENT5 := null ;
5447 g_CWB_Person_CUSTOM_SEGMENT6 := null ;
5448 g_CWB_Person_CUSTOM_SEGMENT7 := null ;
5449 g_CWB_Person_CUSTOM_SEGMENT9 := null ;
5450 g_CWB_Person_CUSTOM_SEGMENT12 := null ;
5451 g_CWB_Person_CUSTOM_SEGMENT15 := null ;
5452 g_CWB_Person_CUSTOM_SEGMENT8 := null ;
5453 g_CWB_Person_CUSTOM_SEGMENT3 := null ;
5454 g_CWB_Person_FEEDBACK_RATING := null ;
5455 g_CWB_Person_FREQUENCY := null ;
5456 g_CWB_Person_Grade_MAX_VAL := null ;
5457 g_CWB_Person_Grade_MID_POINT := null ;
5458 g_CWB_Person_Grade_MIN_VAL := null ;
5459 g_CWB_Person_GRADE_name := null ;
5460 g_CWB_Person_Grade_QUARTILE := null ;
5461 g_CWB_Person_GRADE_ANN_FACTOR := null ;
5462 g_CWB_Person_JOB_name := null ;
5463 g_CWB_Person_LEGISLATION := null ;
5464 g_CWB_Person_LOCATION := null ;
5465 g_CWB_Person_NORMAL_HOURS := null ;
5466 g_CWB_Person_ORG_name := null ;
5467 g_CWB_Person_ORIG_START_DATE := null ;
5468 g_CWB_Person_PAY_RATE := null ;
5469 g_CWB_Person_PAY_ANNUL_FACTOR := null ;
5470 g_CWB_Person_PAYROLL_NAME := null ;
5471 g_CWB_Person_PERF_RATING_DATE := null ;
5472 g_CWB_Person_POSITION := null ;
5473 g_CWB_Person_STATUS_TYPE := null ;
5474 g_CWB_Person_SUP_BRIEF_NAME := null ;
5475 g_CWB_Person_SUP_CUSTOM_NAME := null ;
5476 g_CWB_Person_SUP_FULL_NAME := null ;
5477 g_CWB_Person_YEARS_EMPLOYED := null ;
5478 g_CWB_Person_YEARS_IN_GRADE := null ;
5479 g_CWB_Person_YEARS_IN_POS := null ;
5480 g_CWB_Person_YEARS_IN_JOB := null ;
5481 g_cwb_nw_chg_reason := null ;
5482 g_CWB_new_Job_name := null ;
5483 g_CWB_new_Grade_name := null ;
5484 g_CWB_new_Group_name := null ;
5485 g_CWB_new_Postion_name := null ;
5486 g_CWB_new_Perf_rating := null ;
5487 g_CWB_LE_Dt := null ;
5488 g_CWB_effective_date := null ;
5489 g_CWB_Life_Event_status := null ;
5490 g_cwb_group_plan_name := null ;
5491 -- subheader
5492 g_group_elmt_value1 := null ;
5493 g_group_elmt_value2 := null ;
5494 if g_debug then
5495 hr_utility.set_location('Exiting'||l_proc, 15);
5496 end if;
5497 --
5498 End init_detail_globals;
5499 --
5500 -- ----------------------------------------------------------------------------
5501 -- |------< write_error >---------------------------------------------|
5502 -- ----------------------------------------------------------------------------
5503 --
5504 Procedure write_error(p_err_num in number,
5505 p_err_name in varchar2,
5506 p_typ_cd in varchar2,
5507 p_request_id in number,
5508 p_ext_rslt_id in number) IS
5509 --
5510 l_proc varchar2(72);
5511 l_err_num number(15);
5512 --
5513 cursor err_cnt_c is
5514 select count(*) from ben_ext_rslt_err
5515 where ext_rslt_id = p_ext_rslt_id --request_id = p_request_id
5516 and typ_cd <> 'W';
5517 --
5518 --
5519 begin
5520 --
5521 g_debug := hr_utility.debug_enabled;
5522 if g_debug then
5523 l_proc := g_package||'write_error';
5524 hr_utility.set_location('Entering'||l_proc, 5);
5525 hr_utility.set_location('error message ' || p_err_name,99.97);
5526 end if;
5527 --
5528 open err_cnt_c;
5529 fetch err_cnt_c into l_err_num;
5530 close err_cnt_c;
5531 --
5532
5533 if l_err_num >= ben_ext_thread.g_max_errors_allowed then
5534 --
5535 ben_ext_thread.g_err_num := 91947;
5536 ben_ext_thread.g_err_name := 'BEN_91947_EXT_MX_ERR_NUM';
5537 raise ben_ext_thread.g_job_failure_error;
5538 --
5539 end if;
5540 --
5541 if g_business_group_id is not null then
5542 --
5543 ben_ext_util.write_err
5544 (p_err_num => p_err_num,
5545 p_err_name => p_err_name, --error form will take care of it,
5546 p_typ_cd => p_typ_cd,
5547 p_person_id => g_person_id,
5548 p_request_id => p_request_id,
5549 p_ext_rslt_id => p_ext_rslt_id,
5550 p_business_group_id => g_business_group_id
5551 );
5552 --
5553 commit;
5554 --
5555 end if;
5556 --
5557 if g_debug then
5558 hr_utility.set_location('Exiting'||l_proc, 15);
5559 end if;
5560 --
5561 end write_error;
5562 --
5563 END ben_ext_person;