[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_PERSON
Source
1 Package Body ben_ext_person as
2 /* $Header: benxpers.pkb 120.47 2011/11/03 19:46:07 pvelvano 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 ,
1492 'AT_GLB',a.region_1,'AU_GLB',a.region_1 ,'CH_GBL',a.region_1,'DE',a.region_1 ,
1493 'DE_GBL',a.region_1,'ES',a.region_1 ,'ES_GBL',a.region_1,'FR',a.region_1 ,
1494 'FR_GBL',a.region_1,'IT_GLB',a.region_1 ,'MX',a.region_1,'MX_GLB',a.region_1 ,
1495 'MY_GLB',a.region_1,'NL_GLB',a.region_1 ,'PL',a.region_1,'ZA',a.region_1 ,
1496 'ZA_GLB ',a.region_1,'BZ_GLB',a.region_2,'NL',a.region_2 ,'US',a.region_2,'US_GLB',a.region_2 ,
1497 'US_GLB_FED',a.region_2,'NL',a.region_2 ,'AE',a.ADDRESS_LINE3,
1498 'IN',ADD_INFORMATION15,
1499 NULL) state_ansi
1500 , a.postal_code
1501 , a.country
1502 , a.region_1
1503 , a.region_3
1504 , a.date_from
1505 , a.last_update_date
1506 , a.last_updated_by
1507 , a.last_update_login
1508 , a.created_by
1509 , a.creation_date
1510 from per_addresses a
1511 where
1512 a.person_id = p_person_id
1513 and p_effective_date between nvl(a.date_from, p_effective_date)
1514 and nvl(a.date_to, p_effective_date)
1515 and a.primary_flag = 'Y'
1516 ;
1517
1518 -- related persons primary address
1519 cursor c_rltd_prmy_address is
1520 select
1521 a.address_line1
1522 , a.address_line2
1523 , a.address_line3
1524 , a.town_or_city
1525 , a.region_2 -- 9710319
1526 , decode(a.style ,'CA_GLB',a.region_1,'CA',a.region_1 ,
1527 'AT_GLB',a.region_1,'AU_GLB',a.region_1 ,'CH_GBL',a.region_1,'DE',a.region_1 ,
1528 'DE_GBL',a.region_1,'ES',a.region_1 ,'ES_GBL',a.region_1,'FR',a.region_1 ,
1529 'FR_GBL',a.region_1,'IT_GLB',a.region_1 ,'MX',a.region_1,'MX_GLB',a.region_1 ,
1530 'MY_GLB',a.region_1,'NL_GLB',a.region_1 ,'PL',a.region_1,'ZA',a.region_1 ,
1531 'ZA_GLB ',a.region_1,'BZ_GLB',a.region_2,'NL',a.region_2 ,'US',a.region_2,'US_GLB',a.region_2 ,
1532 'US_GLB_FED',a.region_2,'NL',a.region_2 ,'AE',a.ADDRESS_LINE3,
1533 'IN',ADD_INFORMATION15,
1534 NULL) state_ansi
1535 , a.postal_code
1536 , a.country
1537 , a.region_1
1538 , a.region_3
1539 , a.date_from
1540 from per_addresses a,
1541 per_contact_relationships c,
1542 per_all_people_f p
1543 where
1544 c.contact_person_id = p_person_id
1545 and c.person_id = p.person_id
1546 and a.person_id = p.person_id
1547 and a.primary_flag = 'Y'
1548 and c.rltd_per_rsds_w_dsgntr_flag = 'Y'
1549 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
1550 and nvl(p.effective_end_date, p_effective_date)
1551 and p_effective_date between nvl(a.date_from, p_effective_date)
1552 and nvl(a.date_to, p_effective_date);
1553
1554 --
1555 Begin
1556 --
1557 if g_debug then
1558 l_proc := g_package||'get_primary_address_info';
1559 hr_utility.set_location('Entering'||l_proc, 5);
1560 end if;
1561 --
1562 open c_prmy_address;
1563 fetch c_prmy_address into
1564 g_prim_address_line_1,
1565 g_prim_address_line_2,
1566 g_prim_address_line_3,
1567 g_prim_city,
1568 g_prim_state,
1569 g_prim_state_ansi,
1570 g_prim_postal_code,
1571 g_prim_country,
1572 g_prim_county,
1573 g_prim_region_3,
1574 g_prim_address_date,
1575 g_addr_last_update_date,
1576 g_addr_last_updated_by,
1577 g_addr_last_update_login,
1578 g_addr_created_by,
1579 g_addr_creation_date
1580 ;
1581 --
1582 if c_prmy_address%notfound then
1583 --
1584 -- when address is not found grab one on the related person that resides
1585 -- with them. This will get addresses for contacts.
1586 --
1587 open c_rltd_prmy_address;
1588 fetch c_rltd_prmy_address into
1589 g_prim_address_line_1,
1590 g_prim_address_line_2,
1591 g_prim_address_line_3,
1592 g_prim_city,
1593 g_prim_state,
1594 g_prim_state_ansi,
1595 g_prim_postal_code,
1596 g_prim_country,
1597 g_prim_county,
1598 g_prim_region_3,
1599 g_prim_address_date
1600 ;
1601 --
1602 close c_rltd_prmy_address;
1603 end if;
1604 close c_prmy_address;
1605 --
1606 --
1607 if g_debug then
1608 hr_utility.set_location('Exiting'||l_proc, 15);
1609 end if;
1610 --
1611 end get_primary_address_info;
1612 --
1613 -- ----------------------------------------------------------------------------
1614 -- |------< get_mailing_address_info >------------------------------------------|
1615 -- ----------------------------------------------------------------------------
1616 --
1617 Procedure get_mailing_address_info(p_person_id in number,
1618 p_effective_date in date) is
1619 --
1620 l_proc varchar2(72);
1621 --
1622 cursor c_mail_address is
1623 select
1624 a.address_line1
1625 , a.address_line2
1626 , a.address_line3
1627 , a.town_or_city
1628 , a.region_2
1629 ,decode(a.style ,'CA_GLB',a.region_1,'CA',a.region_1 ,
1630 'AT_GLB',a.region_1,'AU_GLB',a.region_1 ,'CH_GBL',a.region_1,'DE',a.region_1 ,
1631 'DE_GBL',a.region_1,'ES',a.region_1 ,'ES_GBL',a.region_1,'FR',a.region_1 ,
1632 'FR_GBL',a.region_1,'IT_GLB',a.region_1 ,'MX',a.region_1,'MX_GLB',a.region_1 ,
1633 'MY_GLB',a.region_1,'NL_GLB',a.region_1 ,'PL',a.region_1,'ZA',a.region_1 ,
1634 'ZA_GLB ',a.region_1,'BZ_GLB',a.region_2,'NL',a.region_2 ,'US',a.region_2,'US_GLB',a.region_2 ,
1635 'US_GLB_FED',a.region_2,'NL',a.region_2 ,'AE',a.ADDRESS_LINE3,
1636 'IN',ADD_INFORMATION15,
1637 NULL) state_ansi
1638 , a.postal_code
1639 , a.country
1640 , a.region_1
1641 , a.region_3
1642 , a.date_from
1643 from per_addresses a
1644 where
1645 a.person_id = p_person_id
1646 and p_effective_date between nvl(a.date_from, p_effective_date)
1647 and nvl(a.date_to, p_effective_date)
1648 and a.primary_flag = 'N'
1649 and a.address_type = 'M'
1650 ;
1651 --
1652 Begin
1653 --
1654 if g_debug then
1655 l_proc := g_package||'get_mailing_address_info';
1656 hr_utility.set_location('Entering'||l_proc, 5);
1657 end if;
1658 --
1659 open c_mail_address;
1660 fetch c_mail_address into
1661 g_mail_address_line_1,
1662 g_mail_address_line_2,
1663 g_mail_address_line_3,
1664 g_mail_city,
1665 g_mail_state,
1666 g_mail_state_ansi,
1667 g_mail_postal_code,
1668 g_mail_country,
1669 g_mail_county,
1670 g_mail_region_3,
1671 g_mail_address_date
1672 ;
1673 --
1674 close c_mail_address;
1675 --
1676 if g_debug then
1677 hr_utility.set_location('Exiting'||l_proc, 15);
1678 end if;
1679 --
1680 end get_mailing_address_info;
1681 --
1682 -- ----------------------------------------------------------------------------
1683 -- |------< get_comm_address_info >------------------------------------------|
1684 -- ----------------------------------------------------------------------------
1685 --
1686 -- NOTE: See misc/oab/extract/Address hierarchy logic.doc for more info.
1687 --
1688 Procedure get_comm_address_info(p_person_id in number,
1689 p_address_id in number,
1690 p_effective_date in date) is
1691 --
1692 l_proc varchar2(72);
1693 --
1694 cursor c_comm_address is
1695 select
1696 a.address_line1
1697 , a.address_line2
1698 , a.address_line3
1699 , a.town_or_city
1700 , a.region_2
1701 , a.postal_code
1702 , a.country
1703 , a.region_1
1704 , a.region_3
1705 , a.date_from
1706 from per_addresses a
1707 where
1708 a.address_id = p_address_id;
1709 --
1710 cursor c_prim_rltd_address is
1711 select
1712 a.address_line1
1713 , a.address_line2
1714 , a.address_line3
1715 , a.town_or_city
1716 , a.region_2
1717 , a.postal_code
1718 , a.country
1719 , a.region_1
1720 , a.region_3
1721 , a.date_from
1722 from per_addresses a,
1723 per_contact_relationships r
1724 where
1725 r.contact_person_id = p_person_id
1726 and r.person_id = a.person_id
1727 and a.town_or_city is not null
1728 and p_effective_date between nvl(a.date_from, p_effective_date)
1729 and nvl(a.date_to, p_effective_date)
1730 and a.primary_flag = 'Y'
1731 and r.rltd_per_rsds_w_dsgntr_flag = 'Y'
1732 ;
1733 --
1734 Begin
1735 --
1736 if g_debug then
1737 l_proc := g_package||'get_comm_address_info';
1738 hr_utility.set_location('Entering'||l_proc, 5);
1739 end if;
1740 --
1741 --
1742 IF p_address_id is not null then
1743 open c_comm_address;
1744 fetch c_comm_address into
1745 g_cm_addr_line1,
1746 g_cm_addr_line2,
1747 g_cm_addr_line3,
1748 g_cm_city,
1749 g_cm_state,
1750 g_cm_postal_code,
1751 g_cm_country,
1752 g_cm_county,
1753 g_cm_region_3,
1754 g_cm_address_date
1755 ;
1756 --
1757 close c_comm_address;
1758
1759 --
1760 END IF;
1761 --
1762 -- If communication address was not found use mailing address.
1763 --
1764 IF g_cm_city is null and g_mail_city is not null then
1765 --
1766 g_cm_addr_line1 := g_mail_address_line_1;
1767 g_cm_addr_line2 := g_mail_address_line_2;
1768 g_cm_addr_line3 := g_mail_address_line_3;
1769 g_cm_city := g_mail_city;
1770 g_cm_state := g_mail_state;
1771 g_cm_postal_code := g_mail_postal_code;
1772 g_cm_country := g_mail_country;
1773 g_cm_county := g_mail_county;
1774 g_cm_region_3 := g_mail_region_3;
1775 g_cm_address_date := g_mail_address_date;
1776 --
1777 END IF; --g_cm_city is null and g_mail_city is not null then
1778 --
1779 -- If communication address is still blank use primary address.
1780 --
1781 IF g_cm_city is null and ( g_prim_city is not null or g_prim_state is not null ) then
1782 --
1783 g_cm_addr_line1 := g_prim_address_line_1;
1784 g_cm_addr_line2 := g_prim_address_line_2;
1785 g_cm_addr_line3 := g_prim_address_line_3;
1786 g_cm_city := g_prim_city;
1787 g_cm_state := g_prim_state;
1788 g_cm_postal_code := g_prim_postal_code;
1789 g_cm_country := g_prim_country;
1790 g_cm_county := g_prim_county;
1791 g_cm_region_3 := g_prim_region_3;
1792 g_cm_address_date := g_prim_address_date;
1793 --
1794 END IF; --g_cm_city is null and g_prim_city is not null then
1795 --
1796 If (g_cm_city is null and g_cm_state is null) then
1797 open c_prim_rltd_address;
1798 fetch c_prim_rltd_address into
1799 g_cm_addr_line1,
1800 g_cm_addr_line2,
1801 g_cm_addr_line3,
1802 g_cm_city,
1803 g_cm_state,
1804 g_cm_postal_code,
1805 g_cm_country,
1806 g_cm_county,
1807 g_cm_region_3,
1808 g_cm_address_date
1809 ;
1810 close c_prim_rltd_address;
1811 --
1812 End if;
1813 if g_debug then
1814 hr_utility.set_location('Exiting'||l_proc, 15);
1815 end if;
1816 --
1817 end get_comm_address_info;
1818 --
1819 -- ----------------------------------------------------------------------------
1820 -- |------< get_phone_info >----------------------------------------------|
1821 -- ----------------------------------------------------------------------------
1822 --
1823 Procedure get_phone_info(p_person_id in number,
1824 p_effective_date in date) is
1825 --
1826 l_proc varchar2(72);
1827 --
1828 cursor c_phone is
1829 select
1830 h.phone_number phone_home
1831 , w.phone_number phone_work
1832 , f.phone_number phone_fax
1833 , m.phone_number phone_mobile
1834 , b.phone_number phone_pager
1835 from per_all_people_f p
1836 , per_phones h
1837 , per_phones w
1838 , per_phones f
1839 , per_phones m
1840 , per_phones b
1841 where p.person_id = p_person_id
1842 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
1843 and nvl(p.effective_end_date, p_effective_date)
1844 and h.parent_id (+) = p.person_id
1845 and w.parent_id (+) = p.person_id
1846 and f.parent_id (+) = p.person_id
1847 and m.parent_id (+) = p.person_id
1848 and b.parent_id (+) = p.person_id
1849 and h.parent_table (+) = 'PER_ALL_PEOPLE_F'
1850 and w.parent_table (+) = 'PER_ALL_PEOPLE_F'
1851 and f.parent_table (+) = 'PER_ALL_PEOPLE_F'
1852 and m.parent_table (+) = 'PER_ALL_PEOPLE_F'
1853 and b.parent_table (+) = 'PER_ALL_PEOPLE_F'
1854 and h.phone_type (+) = 'H1'
1855 and w.phone_type (+) = 'W1'
1856 and f.phone_type (+) = 'WF'
1857 and m.phone_type (+) = 'M'
1858 and b.phone_type (+) = 'P'
1859 and p_effective_date between nvl(h.date_from(+), p_effective_date)
1860 and nvl(h.date_to(+), p_effective_date)
1861 and p_effective_date between nvl(w.date_from(+), p_effective_date)
1862 and nvl(w.date_to(+), p_effective_date)
1863 and p_effective_date between nvl(f.date_from(+), p_effective_date)
1864 and nvl(f.date_to(+), p_effective_date)
1865 and p_effective_date between nvl(m.date_from(+), p_effective_date)
1866 and nvl(m.date_to(+), p_effective_date)
1867 and p_effective_date between nvl(b.date_from(+), p_effective_date)
1868 and nvl(b.date_to(+), p_effective_date)
1869 ;
1870
1871
1872
1873 cursor c_rltd_phone is
1874 select
1875 h.phone_number phone_home
1876 , w.phone_number phone_work
1877 , f.phone_number phone_fax
1878 , m.phone_number phone_mobile
1879 from per_all_people_f p
1880 , per_phones h
1881 , per_phones w
1882 , per_phones f
1883 , per_phones m
1884 ,per_contact_relationships r
1885 where r.contact_person_id = p_person_id
1886 and r.rltd_per_rsds_w_dsgntr_flag = 'Y'
1887 and p.person_id = r.person_id
1888 and p_effective_date between nvl(p.effective_start_date, p_effective_date)
1889 and nvl(p.effective_end_date, p_effective_date)
1890 and h.parent_id (+) = p.person_id
1891 and w.parent_id (+) = p.person_id
1892 and f.parent_id (+) = p.person_id
1893 and m.parent_id (+) = p.person_id
1894 and h.parent_table (+) = 'PER_ALL_PEOPLE_F'
1895 and w.parent_table (+) = 'PER_ALL_PEOPLE_F'
1896 and f.parent_table (+) = 'PER_ALL_PEOPLE_F'
1897 and m.parent_table (+) = 'PER_ALL_PEOPLE_F'
1898 and h.phone_type (+) = 'H1'
1899 and w.phone_type (+) = 'W1'
1900 and f.phone_type (+) = 'WF'
1901 and m.phone_type (+) = 'M'
1902 and p_effective_date between nvl(h.date_from(+), p_effective_date)
1903 and nvl(h.date_to(+), p_effective_date)
1904 and p_effective_date between nvl(w.date_from(+), p_effective_date)
1905 and nvl(w.date_to(+), p_effective_date)
1906 and p_effective_date between nvl(f.date_from(+), p_effective_date)
1907 and nvl(f.date_to(+), p_effective_date)
1908 and p_effective_date between nvl(m.date_from(+), p_effective_date)
1909 and nvl(m.date_to(+), p_effective_date)
1910 ;
1911 --
1912 Begin
1913 --
1914 if g_debug then
1915 l_proc := g_package||'get_phone_info';
1916 hr_utility.set_location('Entering'||l_proc, 5);
1917 end if;
1918 --
1919 open c_phone;
1920 fetch c_phone into
1921 g_phone_home,
1922 g_phone_work,
1923 g_phone_fax,
1924 g_phone_mobile,
1925 g_phone_pager
1926 ;
1927 hr_utility.set_location(' looking phone ' || g_phone_home , 99 );
1928 if c_phone%notfound or
1929 (g_phone_home is null and g_phone_work is null and g_phone_fax is null and g_phone_mobile is null) then
1930 hr_utility.set_location(' looking for related phone ' || p_person_id , 99 );
1931 -- get related person information
1932 open c_rltd_phone;
1933 fetch c_rltd_phone into
1934 g_phone_home,
1935 g_phone_work,
1936 g_phone_fax,
1937 g_phone_mobile
1938 ;
1939 close c_rltd_phone;
1940 hr_utility.set_location(' home related phone ' || g_phone_home , 99 );
1941 end if ;
1942 --
1943 close c_phone;
1944 --
1945 if g_debug then
1946 hr_utility.set_location('Exiting'||l_proc, 15);
1947 end if;
1948 --
1949 end get_phone_info;
1950 --
1951 -- ----------------------------------------------------------------------------
1952 -- |------< get_period_of_svc_info >-------------------------------------------|
1953 -- ----------------------------------------------------------------------------
1954 --
1955 Procedure get_period_of_svc_info(p_person_id in number,
1956 p_effective_date in date) is
1957 --
1958 l_proc varchar2(72);
1959 --
1960 cursor c_period_of_svc is
1961 select date_start
1962 , actual_termination_date
1963 , adjusted_svc_date
1964 , leaving_reason
1965 , last_update_date
1966 , last_updated_by
1967 , last_update_login
1968 , created_by
1969 , creation_date
1970 , attribute1
1971 , attribute2
1972 , attribute3
1973 , attribute4
1974 , attribute5
1975 , attribute6
1976 , attribute7
1977 , attribute8
1978 , attribute9
1979 , attribute10
1980 from per_periods_of_service pps
1981 where pps.person_id = p_person_id
1982 and pps.date_start = (select max(pps1.date_start) -- this gets most recent
1983 from per_periods_of_service pps1
1984 where pps1.person_id = p_person_id
1985 and pps1.date_start <= p_effective_date);
1986
1987 --
1988 Begin
1989 --
1990 if g_debug then
1991 l_proc := g_package||'get_period_of_svc_info';
1992 hr_utility.set_location('Entering'||l_proc, 5);
1993 end if;
1994 --
1995 open c_period_of_svc;
1996 fetch c_period_of_svc into
1997 g_last_hire_date,
1998 g_actual_term_date,
1999 g_adjusted_svc_date,
2000 g_term_reason,
2001 g_pos_last_update_date,
2002 g_pos_last_updated_by,
2003 g_pos_last_update_login,
2004 g_pos_created_by,
2005 g_pos_creation_date,
2006 g_prs_flex_01,
2007 g_prs_flex_02,
2008 g_prs_flex_03,
2009 g_prs_flex_04,
2010 g_prs_flex_05,
2011 g_prs_flex_06,
2012 g_prs_flex_07,
2013 g_prs_flex_08,
2014 g_prs_flex_09,
2015 g_prs_flex_10
2016 ;
2017 close c_period_of_svc;
2018 --
2019 if g_debug then
2020 hr_utility.set_location('Exiting'||l_proc, 15);
2021 end if;
2022 --
2023 end get_period_of_svc_info;
2024 --
2025 -- ----------------------------------------------------------------------------
2026 -- |------< get_svc_area_info >----------------------------------------------|
2027 -- ----------------------------------------------------------------------------
2028 --
2029 Procedure get_svc_area_info(p_postal_code in varchar2,
2030 p_effective_date in date) is
2031 --
2032 l_proc varchar2(72);
2033 --
2034 cursor c_prmy_svc_area is
2035 select svc.svc_area_id,
2036 svc.name
2037 from ben_svc_area_f svc
2038 , ben_svc_area_pstl_zip_rng_f svps
2039 , ben_pstl_zip_rng_f pszip
2040 where p_postal_code between nvl(pszip.from_value, p_postal_code)
2041 and nvl(pszip.to_value, p_postal_code)
2042 and pszip.pstl_zip_rng_id = svps.pstl_zip_rng_id
2043 and svps.svc_area_id = svc.svc_area_id
2044 and p_effective_date between nvl(svps.effective_start_date, p_effective_date)
2045 and nvl(svps.effective_end_date, p_effective_date)
2046 and p_effective_date between nvl(svc.effective_start_date, p_effective_date)
2047 and nvl(svc.effective_end_date, p_effective_date)
2048 and p_effective_date between nvl(pszip.effective_start_date, p_effective_date)
2049 and nvl(pszip.effective_end_date, p_effective_date);
2050 --
2051 Begin
2052 --
2053 if g_debug then
2054 l_proc := g_package||'get_svc_area_info';
2055 hr_utility.set_location('Entering'||l_proc, 5);
2056 end if;
2057 --
2058 open c_prmy_svc_area;
2059 fetch c_prmy_svc_area into ben_ext_person.g_prim_addr_sva_id,
2060 ben_ext_person.g_prim_addr_service_area;
2061 close c_prmy_svc_area;
2062 --
2063 if g_debug then
2064 hr_utility.set_location('Exiting'||l_proc, 15);
2065 end if;
2066 --
2067 end get_svc_area_info;
2068 --
2069 -- ----------------------------------------------------------------------------
2070 -- |------< get_started_ler_info >----------------------------------------------|
2071 -- ----------------------------------------------------------------------------
2072 --
2073 Procedure get_started_ler_info(p_person_id in number,
2074 p_effective_date in date) is
2075 --
2076 l_proc varchar2(72);
2077 --
2078 cursor c_started_ler is
2079 select /*+ leading(PLER) */
2080 pler.per_in_ler_id per_in_ler_id
2081 , pler.lf_evt_ocrd_dt lf_evt_ocrd_dt
2082 , pler.ntfn_dt lf_evt_note_dt
2083 , ler.ler_id ler_id
2084 , ler.name ler_name
2085 , ler.ler_attribute1
2086 , ler.ler_attribute2
2087 , ler.ler_attribute3
2088 , ler.ler_attribute4
2089 , ler.ler_attribute5
2090 , ler.ler_attribute6
2091 , ler.ler_attribute7
2092 , ler.ler_attribute8
2093 , ler.ler_attribute9
2094 , ler.ler_attribute10
2095 from
2096 ben_per_in_ler pler,
2097 ben_ler_f ler
2098 where
2099 pler.person_id = p_person_id
2100 and pler.ler_id = ler.ler_id
2101 and pler.per_in_ler_stat_cd = 'STRTD'
2102 and p_effective_date between ler.effective_start_date and ler.effective_end_date
2103 ;
2104 --
2105 Begin
2106 --
2107 if g_debug then
2108 l_proc := g_package||'get_started_ler_info';
2109 hr_utility.set_location('Entering'||l_proc, 5);
2110 end if;
2111 --
2112 open c_started_ler;
2113 fetch c_started_ler into
2114 g_per_in_ler_id,
2115 g_lf_evt_ocrd_dt,
2116 g_lf_evt_note_dt,
2117 g_ler_id,
2118 g_ler_name,
2119 g_ler_attr_1,
2120 g_ler_attr_2,
2121 g_ler_attr_3,
2122 g_ler_attr_4,
2123 g_ler_attr_5,
2124 g_ler_attr_6,
2125 g_ler_attr_7,
2126 g_ler_attr_8,
2127 g_ler_attr_9,
2128 g_ler_attr_10;
2129 close c_started_ler;
2130 --
2131 if g_debug then
2132 hr_utility.set_location('Exiting'||l_proc, 15);
2133 end if;
2134 --
2135 end get_started_ler_info;
2136 --
2137 -- ----------------------------------------------------------------------------
2138 -- |------< get_bnfts_group_info >----------------------------------------------|
2139 -- ----------------------------------------------------------------------------
2140 --
2141 Procedure get_bnfts_group_info(p_benfts_grp_id in number) is
2142 --
2143 l_proc varchar2(72);
2144 --
2145 cursor c_bnfts_group is
2146 select bgr.name
2147 , bgr.bng_attribute1
2148 , bgr.bng_attribute2
2149 , bgr.bng_attribute3
2150 , bgr.bng_attribute4
2151 , bgr.bng_attribute5
2152 , bgr.bng_attribute6
2153 , bgr.bng_attribute7
2154 , bgr.bng_attribute8
2155 , bgr.bng_attribute9
2156 , bgr.bng_attribute10
2157 from ben_benfts_grp bgr
2158 where bgr.benfts_grp_id = p_benfts_grp_id;
2159 --
2160 Begin
2161 --
2162 if g_debug then
2163 l_proc := g_package||'get_bnfts_group_info';
2164 hr_utility.set_location('Entering'||l_proc, 5);
2165 end if;
2166 --
2167 open c_bnfts_group;
2168 fetch c_bnfts_group into
2169 g_benefit_group,
2170 g_bng_flex_01,
2171 g_bng_flex_02,
2172 g_bng_flex_03,
2173 g_bng_flex_04,
2174 g_bng_flex_05,
2175 g_bng_flex_06,
2176 g_bng_flex_07,
2177 g_bng_flex_08,
2178 g_bng_flex_09,
2179 g_bng_flex_10;
2180 close c_bnfts_group;
2181 --
2182 if g_debug then
2183 hr_utility.set_location('Exiting'||l_proc, 15);
2184 end if;
2185 --
2186 end get_bnfts_group_info;
2187 --
2188 -- ----------------------------------------------------------------------------
2189 -- |------< get_absence_info >----------------------------------------------|
2190 -- ----------------------------------------------------------------------------
2191 --
2192 Procedure get_absence_info(p_person_id in number,
2193 p_effective_date in date) is
2194 --
2195 l_proc varchar2(72);
2196 --
2197 cursor c_absence is
2198 select abs.abs_attendance_reason_id
2199 , abs.absence_attendance_type_id
2200 , abs.date_start
2201 , abs.date_end
2202 , abs.absence_days
2203 , abs.last_update_date
2204 , abs.last_updated_by
2205 , abs.last_update_login
2206 , abs.created_by
2207 , abs.creation_date
2208 , abs.attribute1
2209 , abs.attribute2
2210 , abs.attribute3
2211 , abs.attribute4
2212 , abs.attribute5
2213 , abs.attribute6
2214 , abs.attribute7
2215 , abs.attribute8
2216 , abs.attribute9
2217 , abs.attribute10
2218 from per_absence_attendances abs
2219 where abs.person_id = p_person_id
2220 and p_effective_date between nvl(abs.date_start,p_effective_date)
2221 and nvl(abs.date_end, p_effective_date);
2222
2223 CURSOR abs_cat(p_absence_attendance_type_id NUMBER) IS
2224 SELECT abt.absence_category
2225 , abt.name abs_type
2226 , luk.meaning abs_category
2227 FROM per_absence_attendance_types abt
2228 , hr_lookups luk
2229 WHERE abt.absence_attendance_type_id = p_absence_attendance_type_id
2230 AND abt.absence_category = luk.lookup_code
2231 AND luk.lookup_type = 'ABSENCE_CATEGORY';
2232
2233 --
2234
2235 CURSOR abs_reason(p_abs_attendance_reason_id NUMBER) IS
2236 SELECT lkp.meaning abs_reason ,
2237 abr.name -- Bug 2841958, getting the reason code
2238 FROM per_abs_attendance_reasons abr
2239 , hr_lookups lkp
2240 WHERE abr.abs_attendance_reason_id = p_abs_attendance_reason_id
2241 AND abr.name = lkp.lookup_code
2242 AND lkp.lookup_type = 'ABSENCE_REASON';
2243
2244 --
2245 Begin
2246 --
2247 if g_debug then
2248 l_proc := g_package||'get_absence_info';
2249 hr_utility.set_location('Entering'||l_proc, 5);
2250 hr_utility.set_location('bug 4208'||p_person_id , 4208);
2251 end if;
2252 --
2253
2254 open c_absence;
2255 fetch c_absence into
2256 g_abs_reason
2257 , g_abs_type
2258 , g_abs_start_dt
2259 , g_abs_end_dt
2260 , g_abs_duration
2261 , g_abs_last_update_date
2262 , g_abs_last_updated_by
2263 , g_abs_last_update_login
2264 , g_abs_created_by
2265 , g_abs_creation_date
2266 , g_abs_flex_01
2267 , g_abs_flex_02
2268 , g_abs_flex_03
2269 , g_abs_flex_04
2270 , g_abs_flex_05
2271 , g_abs_flex_06
2272 , g_abs_flex_07
2273 , g_abs_flex_08
2274 , g_abs_flex_09
2275 , g_abs_flex_10;
2276 close c_absence;
2277 --
2278 open abs_cat(g_abs_type);
2279 fetch abs_cat into g_abs_category,g_abs_type_name,g_abs_category_name;
2280 close abs_cat;
2281 --
2282 open abs_reason(g_abs_reason);
2283 fetch abs_reason into g_abs_reason_name ,
2284 g_abs_reason_cd; -- Bug 2841958, extra column in cursor
2285 close abs_reason;
2286 --
2287 if g_debug then
2288 hr_utility.set_location('Exiting'||l_proc, 15);
2289 end if;
2290 --
2291 end get_absence_info;
2292 --
2293 -- ----------------------------------------------------------------------------
2294 -- |------< get_cobra_info >----------------------------------------------|
2295 -- ----------------------------------------------------------------------------
2296 --
2297 Procedure get_cobra_info(p_person_id in number,
2298 p_effective_date in date) is
2299 --
2300 l_proc varchar2(72);
2301 --
2302 cursor cbra_info_c is
2303 select ler.ler_id event_id,
2304 ler.name event_name,
2305 cqb.cbr_elig_perd_strt_dt strt_dt,
2306 cqb.cbr_elig_perd_end_dt end_dt
2307 from ben_cbr_quald_bnf cqb,
2308 ben_cbr_per_in_ler cpl,
2309 ben_ler_f ler,
2310 ben_per_in_ler pil
2311 where
2312 cqb.quald_bnf_person_id = p_person_id
2313 and quald_bnf_flag = 'Y'
2314 and p_effective_date between nvl(cqb.cbr_elig_perd_strt_dt,p_effective_date)
2315 and nvl(cqb.cbr_elig_perd_end_dt,p_effective_date)
2316 and cqb.cbr_quald_bnf_id = cpl.cbr_quald_bnf_id
2317 and cpl.per_in_ler_id = pil.per_in_ler_id
2318 and pil.ler_id = ler.ler_id
2319 and p_effective_date between nvl(ler.effective_start_date,p_effective_date)
2320 and nvl(ler.effective_end_date ,p_effective_date)
2321 and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
2322 ;
2323
2324 cursor c_person_type is
2325 SELECT 'x'
2326 FROM per_person_type_usages_f ptu ,
2327 per_person_types ppt
2328 WHERE ptu.person_id = p_person_id
2329 and ptu.person_type_id = ppt.person_type_id
2330 and ppt.system_person_type in ('SRVNG_FMLY_MMBR','SRVNG_SPS')
2331 AND p_effective_date between ptu.effective_start_date and
2332 ptu.effective_end_date;
2333
2334 l_dummy varchar2(1) ;
2335 --
2336 Begin
2337 --
2338 if g_debug then
2339 l_proc := g_package||'get_cobra_info';
2340 hr_utility.set_location('Entering'||l_proc, 5);
2341 end if;
2342 --
2343 -- get cobra information
2344 --
2345 open cbra_info_c;
2346 fetch cbra_info_c into
2347 g_cbra_ler_id,
2348 g_cbra_ler_name,
2349 g_cbra_strt_dt,
2350 g_cbra_end_dt;
2351 if cbra_info_c%found then
2352 g_bnft_stat_cd := 'C';
2353 elsif cbra_info_c%notfound then
2354
2355 g_bnft_stat_cd := 'A';
2356 ---- check whether the person is surviver of prtt
2357 open c_person_type ;
2358 fetch c_person_type into l_dummy ;
2359 if c_person_type%found then
2360 g_bnft_stat_cd := 'S';
2361 end if ;
2362 close c_person_type ;
2363
2364 end if;
2365 close cbra_info_c;
2366 --
2367 if g_debug then
2368 hr_utility.set_location('Exiting'||l_proc, 15);
2369 end if;
2370 --
2371 end get_cobra_info;
2372 --
2373 -- ----------------------------------------------------------------------------
2374 -- |------< get_bnfts_bal_info >----------------------------------------------|
2375 -- ----------------------------------------------------------------------------
2376 --
2377 Procedure get_bnfts_bal_info(p_person_id in number,
2378 p_effective_date in date) is
2379 --
2380 l_proc varchar2(72);
2381 --
2382 cursor c_bnfts_bal (p_bnfts_bal_usg_cd varchar2) is
2383 select sum(val)
2384 from ben_per_bnfts_bal_f a,
2385 ben_bnfts_bal_f b
2386 where a.person_id = p_person_id
2387 and a.bnfts_bal_id = b.bnfts_bal_id
2388 and b.bnfts_bal_usg_cd = p_bnfts_bal_usg_cd
2389 and p_effective_date between nvl(a.effective_start_date,p_effective_date)
2390 and nvl(a.effective_end_date,p_effective_date)
2391 and p_effective_date between nvl(b.effective_start_date,p_effective_date)
2392 and nvl(b.effective_end_date,p_effective_date );
2393 --
2394 Begin
2395 --
2396 if g_debug then
2397 l_proc := g_package||'get_bnfts_bal_info';
2398 hr_utility.set_location('Entering'||l_proc, 5);
2399 end if;
2400 --
2401 -- Vacation
2402 --
2403 IF ben_extract.g_bb4_csr = 'Y' THEN
2404 open c_bnfts_bal('VAC');
2405 fetch c_bnfts_bal into
2406 g_benefit_bal_vacation;
2407 close c_bnfts_bal;
2408 END IF;
2409 --
2410 -- Sick Leave
2411 --
2412 IF ben_extract.g_bb3_csr = 'Y' THEN
2413 open c_bnfts_bal('SCK');
2414 fetch c_bnfts_bal into
2415 g_benefit_bal_sickleave;
2416 close c_bnfts_bal;
2417 END IF;
2418 --
2419 -- Pension
2420 --
2421 IF ben_extract.g_bb2_csr = 'Y' THEN
2422 open c_bnfts_bal('PENBEN');
2423 fetch c_bnfts_bal into
2424 g_benefit_bal_pension;
2425 close c_bnfts_bal;
2426 END IF;
2427 --
2428 -- Defined Contribution
2429 --
2430 IF ben_extract.g_bb1_csr = 'Y' THEN
2431 open c_bnfts_bal('DCBEN');
2432 fetch c_bnfts_bal into
2433 g_benefit_bal_dfncntrbn;
2434 close c_bnfts_bal;
2435 END IF;
2436 --
2437 -- Wellness
2438 --
2439 IF ben_extract.g_bb5_csr = 'Y' THEN
2440 open c_bnfts_bal('WLNS');
2441 fetch c_bnfts_bal into
2442 g_benefit_bal_wellness;
2443 close c_bnfts_bal;
2444 END IF;
2445 --
2446 --
2447 if g_debug then
2448 hr_utility.set_location('Exiting'||l_proc, 15);
2449 end if;
2450 --
2451 end get_bnfts_bal_info;
2452
2453 --- this procedure to avoid the duplication for each
2454 --- extract type
2455
2456 Procedure Extract_person_info(p_person_id in number,
2457 p_effective_date in date, -- passed in from conc mgr
2458 p_business_group_id in number,
2459 p_ext_rslt_id in number
2460 ) IS
2461
2462 l_proc varchar2(72);
2463
2464 begin
2465
2466 g_debug := hr_utility.debug_enabled;
2467 if g_debug then
2468 l_proc := g_package||' Extract_person_info';
2469 hr_utility.set_location('Entering'||l_proc, 5);
2470 end if;
2471
2472
2473 get_person_info (p_person_id => p_person_id,
2474 p_effective_date => g_person_ext_dt);
2475 --
2476 if g_debug then
2477 hr_utility.set_location('asg level ' || ben_extract.g_asg_csr,99);
2478 end if;
2479 if ben_extract.g_asg_csr = 'Y' then
2480 get_assignment_info (p_person_id => p_person_id,
2481 p_assignment_id => g_assignment_id,
2482 p_effective_date => g_person_ext_dt,
2483 p_ext_rslt_id => p_ext_rslt_id );
2484 end if;
2485 --
2486 -- get the primary address for communication too
2487 -- priamry address is used if the cmmunication not avaialable
2488
2489 if ben_extract.g_addr_csr = 'Y' or ben_extract.g_asa_csr = 'Y' or ben_extract.g_cma_csr = 'Y' then
2490 get_primary_address_info (p_person_id => p_person_id,
2491 p_effective_date => g_person_ext_dt);
2492 end if;
2493 --
2494 if ben_extract.g_ma_csr = 'Y' then
2495 get_mailing_address_info (p_person_id => p_person_id,
2496 p_effective_date => g_person_ext_dt);
2497 end if;
2498 ---
2499 if ben_extract.g_cma_csr = 'Y' then
2500 get_comm_address_info(p_person_id => p_person_id,
2501 p_address_id => g_cm_address_id,
2502 p_effective_date => g_person_ext_dt);
2503 end if;
2504
2505 --
2506 if ben_extract.g_phn_csr = 'Y' then
2507 get_phone_info (p_person_id => p_person_id,
2508 p_effective_date => g_person_ext_dt);
2509 end if;
2510 --
2511 if ben_extract.g_pos_csr = 'Y' then
2512 get_period_of_svc_info (p_person_id => p_person_id,
2513 p_effective_date => g_person_ext_dt);
2514 end if;
2515 --
2516 if ben_extract.g_asa_csr = 'Y' then
2517 get_svc_area_info (p_postal_code => g_prim_postal_code,
2518 p_effective_date => g_person_ext_dt);
2519 end if;
2520 --
2521 if ben_extract.g_ler_csr = 'Y' then
2522 get_started_ler_info (p_person_id => p_person_id,
2523 p_effective_date => g_person_ext_dt);
2524 end if;
2525 --
2526 if ben_extract.g_bgr_csr = 'Y' then
2527 get_bnfts_group_info (p_benfts_grp_id => g_benefit_group_id);
2528 end if;
2529 --
2530 if ben_extract.g_abs_csr = 'Y' then
2531 get_absence_info (p_person_id => p_person_id,
2532 p_effective_date => g_person_ext_dt);
2533 end if;
2534 --
2535 if ben_extract.g_cbra_csr = 'Y' then
2536 get_cobra_info (p_person_id => p_person_id,
2537 p_effective_date => g_person_ext_dt);
2538 end if;
2539 --
2540 if ben_extract.g_bb1_csr = 'Y' or ben_extract.g_bb2_csr = 'Y' or ben_extract.g_bb3_csr = 'Y'
2541 or ben_extract.g_bb4_csr = 'Y' or ben_extract.g_bb5_csr = 'Y' then
2542 get_bnfts_bal_info (p_person_id => p_person_id,
2543 p_effective_date => g_person_ext_dt);
2544 end if;
2545 ---
2546
2547 if ben_extract.g_sup_csr = 'Y' then
2548 -- supervisor infor expect supervisor id so if it not intialised
2549 -- intialise again
2550 if nvl(ben_extract.g_asg_csr,'N') <> 'Y' then
2551 get_assignment_info (p_person_id => p_person_id,
2552 p_assignment_id => g_assignment_id,
2553 p_effective_date => g_person_ext_dt,
2554 p_ext_rslt_id => p_ext_rslt_id );
2555 end if;
2556
2557 get_supervisor_info (p_supervisor_id => g_asg_supervisor_id ,
2558 p_effective_date => g_person_ext_dt);
2559 end if;
2560 -- basic salary
2561 if ben_extract.g_bsl_csr = 'Y' then
2562 get_base_annual_salary_info(p_person_id => p_person_id,
2563 p_effective_date => g_person_ext_dt);
2564 end if;
2565 if ben_extract.g_shl_csr = 'Y' then
2566 get_School_info(p_person_id => p_person_id,
2567 p_effective_date => g_person_ext_dt);
2568 end if;
2569
2570 --person level flex provided and used
2571 if ben_extract.g_flxcr_csr = 'Y' then
2572 get_person_flex_credit(p_person_id => p_person_id,
2573 p_effective_date => g_person_ext_dt);
2574 end if ;
2575
2576
2577 --
2578 if g_debug then
2579 hr_utility.set_location('Exiting'||l_proc, 15);
2580 end if;
2581 --
2582
2583 End Extract_person_info ;
2584
2585
2586
2587 --
2588 -- ----------------------------------------------------------------------------
2589 -- |------< process_ext_person >----------------------------------------------|
2590 -- ----------------------------------------------------------------------------
2591 -- This procedure will determine the processing route based on the extract
2592 -- definition for a given person. It will call process_ext_levels to complete
2593 -- all detail records for a given person. It is an open issue whether or not it
2594 -- needs to evaluate inclusion criteria here for Full Profile (Yes for now).
2595 --
2596 Procedure process_ext_person(
2597 p_person_id in number,
2598 p_ext_dfn_id in number,
2599 p_ext_rslt_id in number,
2600 p_ext_file_id in number,
2601 p_ext_crit_prfl_id in number,
2602 p_data_typ_cd in varchar2,
2603 p_ext_typ_cd in varchar2,
2604 p_effective_date in date, -- passed in from conc mgr
2605 p_business_group_id in number,
2606 p_penserv_mode in varchar2 --vkodedal changes for penserver - 30-apr-2008
2607 ) IS
2608 --
2609 l_proc varchar2(72);
2610 --
2611 l_include varchar2(1);
2612 l_dummy_start_date date;
2613 l_dummy_end_date date;
2614 l_chg_actl_strt_dt date;
2615 l_chg_actl_end_dt date;
2616 l_chg_eff_strt_dt date;
2617 l_chg_eff_end_dt date;
2618 l_to_be_sent_strt_dt date;
2619 l_to_be_sent_end_dt date;
2620 l_person_ext_dt date;
2621 l_benefits_ext_dt date;
2622 --
2623 cursor c_changes_only_extract
2624 (p_chg_actl_strt_dt in date,
2625 p_chg_actl_end_dt in date,
2626 p_chg_eff_strt_dt in date,
2627 p_chg_eff_end_dt in date)
2628 is
2629 select a.ext_chg_evt_log_id
2630 , a.chg_evt_cd
2631 , a.chg_eff_dt
2632 , trunc(a.chg_actl_dt)
2633 , a.last_update_login
2634 , a.prmtr_01
2635 , a.prmtr_02
2636 , a.prmtr_03
2637 , a.prmtr_04
2638 , a.prmtr_05
2639 , a.prmtr_06
2640 , a.old_val1
2641 , a.old_val2
2642 , a.old_val3
2643 , a.old_val4
2644 , a.old_val5
2645 , a.old_val6
2646 , a.new_val1
2647 , a.new_val2
2648 , a.new_val3
2649 , a.new_val4
2650 , a.new_val5
2651 , a.new_val6
2652 , 'BEN' chg_evt_source
2653 from ben_ext_chg_evt_log a
2654 where
2655 a.person_id = p_person_id
2656 and trunc(a.chg_actl_dt) between nvl(p_chg_actl_strt_dt, hr_api.g_sot)
2657 and nvl(p_chg_actl_end_dt, hr_api.g_eot)
2658 and a.chg_eff_dt between nvl(p_chg_eff_strt_dt, hr_api.g_sot)
2659 and nvl(p_chg_eff_end_dt, hr_api.g_eot)
2660 order by a.chg_eff_dt;
2661
2662
2663
2664 cursor c_chg_pay_evt is
2665 select xcv.val_1 event_group_id
2666 from ben_ext_crit_typ xct
2667 ,ben_ext_crit_val xcv
2668 where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
2669 and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
2670 and xct.CRIT_TYP_CD = 'CPE'
2671 ;
2672
2673
2674 l_pay_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
2675 l_pay_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
2676 l_pay_detail_tab pay_interpreter_pkg.t_detailed_output_table_type;
2677 l_pay_pro_type_tab pay_interpreter_pkg.t_proration_type_table_type;
2678 l_dated_table_id pay_event_updates.dated_table_id%type ;
2679 l_pay_Assignment_id number ;
2680
2681 l_pay_detail_tot_tab t_detailed_output_table;
2682 l_pay_tot_Srno number ;
2683 l_pay_evt_srno number ;
2684 l_g_c_found varchar2(1) ;
2685 cursor c_pay_chg_tbl ( p_dated_table_id number) is
2686 select table_name
2687 from pay_dated_tables
2688 where dated_table_id = p_dated_table_id
2689 ;
2690
2691 --
2692 /*
2693 cursor c_communication_extract
2694 (p_to_be_sent_strt_dt in date,
2695 p_to_be_sent_end_dt in date)
2696 is
2697 select e.name
2698 , e.cm_typ_id
2699 , e.shrt_name
2700 , e.pc_kit_cd
2701 , a.per_cm_id
2702 , a.per_in_ler_id
2703 , a.prtt_enrt_actn_id
2704 , nvl(b.effective_start_date,a.effective_start_date) effective_start_date
2705 , d.proc_cd
2706 , b.to_be_sent_dt
2707 , b.sent_dt
2708 , a.last_update_date
2709 , b.last_update_date
2710 , b.dlvry_instn_txt
2711 , b.inspn_rqd_flag
2712 , b.address_id
2713 , b.per_cm_prvdd_id
2714 , b.object_version_number
2715 , b.effective_start_date
2716 , c.effective_start_date
2717 , l.ler_id
2718 , l.name
2719 , p.per_in_ler_stat_cd
2720 , nvl(p.lf_evt_ocrd_dt,a.effective_start_date) lf_evt_ocrd_dt
2721 , nvl(p.ntfn_dt,a.effective_start_date) ntfn_dt
2722 from ben_per_cm_f a,
2723 ben_per_cm_prvdd_f b,
2724 ben_per_cm_trgr_f c,
2725 ben_cm_trgr d,
2726 ben_cm_typ_f e,
2727 ben_per_in_ler p,
2728 ben_ler_f l
2729 where
2730 a.person_id = p_person_id
2731 and a.per_cm_id = b.per_cm_id
2732 and a.cm_typ_id = e.cm_typ_id
2733 and a.per_cm_id = c.per_cm_id(+)
2734 and c.cm_trgr_id = d.cm_trgr_id(+)
2735 and a.per_in_ler_id = p.per_in_ler_id(+)
2736 and p.ler_id = l.ler_id(+)
2737 and b.per_cm_prvdd_stat_cd = 'ACTIVE' -- this should be inclusion criteria.
2738 -- the following line of code was put here for performance.
2739 and nvl(b.to_be_sent_dt,hr_api.g_sot) between nvl(p_to_be_sent_strt_dt, hr_api.g_sot)
2740 and nvl(p_to_be_sent_end_dt, hr_api.g_eot)
2741 and p_effective_date between b.effective_start_date
2742 and b.effective_end_date
2743 and b.effective_start_date between a.effective_start_date
2744 and a.effective_end_date
2745 and b.effective_start_date
2746 between nvl(c.effective_start_date,b.effective_start_date)
2747 and nvl(c.effective_end_date,b.effective_start_date)
2748 and b.effective_start_date between e.effective_start_date
2749 and e.effective_end_date
2750 and b.effective_start_date
2751 between nvl(l.effective_start_date,b.effective_start_date)
2752 and nvl(l.effective_end_date,b.effective_start_date)
2753 order by b.to_be_sent_dt , b.per_cm_prvdd_id;
2754 */
2755
2756
2757 l_per_cm_id_va t_number ;
2758 l_per_in_ler_id_va t_number ;
2759 l_prtt_enrt_actn_id_va t_number ;
2760 l_effective_start_date_va t_date ;
2761 l_per_cm_eff_start_date_va t_date ;
2762 l_to_be_sent_dt_va t_date ;
2763 l_sent_dt_va t_date ;
2764 l_per_cm_last_update_date_va t_date ;
2765 l_last_update_date_va t_date ;
2766 l_dlvry_instn_txt_va t_varchar2_600 ;
2767 l_inspn_rqd_flag_va t_varchar2_30 ;
2768 l_address_id_va t_number ;
2769 l_per_cm_prvdd_id_va t_number ;
2770 l_object_version_number_va t_number ;
2771 l_cm_typ_id_va t_number ;
2772
2773
2774 cursor c_communication_extract
2775 (p_to_be_sent_strt_dt in date,
2776 p_to_be_sent_end_dt in date)
2777 is
2778 select a.per_cm_id
2779 , a.per_in_ler_id
2780 , a.prtt_enrt_actn_id
2781 , b.effective_start_date
2782 , a.effective_start_date per_cm_eff_start_date
2783 , b.to_be_sent_dt
2784 , b.sent_dt
2785 , a.last_update_date per_cm_last_update_date
2786 , b.last_update_date
2787 , b.dlvry_instn_txt
2788 , b.inspn_rqd_flag
2789 , b.address_id
2790 , b.per_cm_prvdd_id
2791 , b.object_version_number
2792 , a.cm_typ_id
2793 from ben_per_cm_f a,
2794 ben_per_cm_prvdd_f b
2795 where
2796 a.person_id = p_person_id
2797 and a.per_cm_id = b.per_cm_id
2798 and b.per_cm_prvdd_stat_cd = 'ACTIVE' -- this should be inclusion criteria.
2799 -- the following line of code was put here for performance.
2800 and nvl(b.to_be_sent_dt,hr_api.g_sot) between nvl(p_to_be_sent_strt_dt, hr_api.g_sot)
2801 and nvl(p_to_be_sent_end_dt, hr_api.g_eot)
2802 and p_effective_date between b.effective_start_date
2803 and b.effective_end_date
2804 and b.effective_start_date between a.effective_start_date
2805 and a.effective_end_date
2806 order by b.to_be_sent_dt , b.per_cm_prvdd_id;
2807
2808
2809 cursor c_per_comm_trigger
2810 (p_per_cm_id in number,
2811 p_effective_date in date
2812 ) is
2813 select c.effective_start_date ,
2814 c.cm_trgr_id
2815 from ben_per_cm_trgr_f c
2816 where p_per_cm_id = c.per_cm_id
2817 and p_effective_date
2818 between c.effective_start_date and c.effective_end_date
2819 ;
2820
2821
2822
2823 cursor c_comm_trgr (
2824 p_cm_trgr_id in number
2825 ) is
2826 select d.proc_cd
2827 from ben_cm_trgr d
2828 where p_cm_trgr_id = d.cm_trgr_id ;
2829
2830
2831 cursor c_comm_typ (
2832 p_cm_typ_id in number ,
2833 p_effective_date in date
2834 ) is
2835 select e.name
2836 , e.shrt_name
2837 , e.pc_kit_cd
2838 from ben_cm_typ_f e
2839 where p_cm_typ_id = e.cm_typ_id
2840 and p_effective_date between e.effective_start_date
2841 and e.effective_end_date ;
2842
2843
2844 cursor c_pil ( p_per_in_ler_id number ,
2845 p_effective_date in date
2846 ) is
2847 select l.ler_id
2848 ,l.name
2849 ,p.per_in_ler_stat_cd
2850 ,p.lf_evt_ocrd_dt
2851 ,p.ntfn_dt
2852 from ben_per_in_ler p,
2853 ben_ler_f l
2854 where p_per_in_ler_id = p.per_in_ler_id
2855 and p.ler_id = l.ler_id
2856 and p_effective_date
2857 between l.effective_start_date and l.effective_end_date
2858 ;
2859
2860 l_cm_trgr_id ben_per_cm_trgr_f.cm_trgr_id%type ;
2861 l_last_per_cm_prvdd_id number:=null;
2862 l_err_message fnd_new_messages.message_text%type ;
2863 --
2864 /* Start of Changes for WWBUG: 2008949: added cursor */
2865 cursor c_chg_penid(p_element_entry_id number,
2866 p_effective_date date) is
2867 select ee.creator_id
2868 from pay_element_entries_f ee
2869 where ee.element_entry_id = p_element_entry_id
2870 and p_effective_date between ee.effective_start_date and ee.effective_end_date;
2871 /* End of Changes for WWBUG: 2008949: added cursor */
2872 -- CWB
2873
2874 cursor c_cwb_extract is
2875 select cpi.GROUP_PER_IN_LER_ID
2876 ,cpi.ASSIGNMENT_ID
2877 ,cpi.PERSON_ID
2878 ,cpi.SUPERVISOR_ID
2879 ,cpi.EFFECTIVE_DATE
2880 ,cpi.FULL_NAME
2881 ,cpi.BRIEF_NAME
2882 ,cpi.CUSTOM_NAME
2883 ,cpi.SUPERVISOR_FULL_NAME
2884 ,cpi.SUPERVISOR_BRIEF_NAME
2885 ,cpi.SUPERVISOR_CUSTOM_NAME
2886 ,cpi.LEGISLATION_CODE
2887 ,cpi.YEARS_EMPLOYED
2888 ,cpi.YEARS_IN_JOB
2889 ,cpi.YEARS_IN_POSITION
2890 ,cpi.YEARS_IN_GRADE
2891 ,cpi.EMPLOYEE_NUMBER
2892 ,cpi.START_DATE
2893 ,cpi.ORIGINAL_START_DATE
2894 ,cpi.ADJUSTED_SVC_DATE
2895 ,cpi.BASE_SALARY
2896 ,cpi.BASE_SALARY_CHANGE_DATE
2897 ,cpi.PAYROLL_NAME
2898 ,cpi.PERFORMANCE_RATING
2899 ,cpi.PERFORMANCE_RATING_TYPE
2900 ,cpi.PERFORMANCE_RATING_DATE
2901 ,cpi.BUSINESS_GROUP_ID
2902 ,cpi.ORGANIZATION_ID
2903 ,cpi.JOB_ID
2904 ,cpi.GRADE_ID
2905 ,cpi.POSITION_ID
2906 ,cpi.PEOPLE_GROUP_ID
2907 ,cpi.SOFT_CODING_KEYFLEX_ID
2908 ,cpi.LOCATION_ID
2909 ,cpi.PAY_RATE_ID
2910 ,cpi.ASSIGNMENT_STATUS_TYPE_ID
2911 ,cpi.FREQUENCY
2912 ,cpi.GRADE_ANNULIZATION_FACTOR
2913 ,cpi.PAY_ANNULIZATION_FACTOR
2914 ,cpi.GRD_MIN_VAL
2915 ,cpi.GRD_MAX_VAL
2916 ,cpi.GRD_MID_POINT
2917 ,cpi.GRD_QUARTILE
2918 ,cpi.GRD_COMPARATIO
2919 ,cpi.EMP_CATEGORY
2920 ,cpi.CHANGE_REASON
2921 ,cpi.NORMAL_HOURS
2922 ,cpi.EMAIL_ADDRESS
2923 ,cpi.BASE_SALARY_FREQUENCY
2924 ,cpi.NEW_ASSGN_OVN
2925 ,cpi.NEW_PERF_EVENT_ID
2926 ,cpi.NEW_PERF_REVIEW_ID
2927 ,cpi.POST_PROCESS_STAT_CD
2928 ,cpi.FEEDBACK_RATING
2929 ,cpi.OBJECT_VERSION_NUMBER
2930 ,cpi.CUSTOM_SEGMENT1
2931 ,cpi.CUSTOM_SEGMENT2
2932 ,cpi.CUSTOM_SEGMENT3
2933 ,cpi.CUSTOM_SEGMENT4
2934 ,cpi.CUSTOM_SEGMENT5
2935 ,cpi.CUSTOM_SEGMENT6
2936 ,cpi.CUSTOM_SEGMENT7
2937 ,cpi.CUSTOM_SEGMENT8
2938 ,cpi.CUSTOM_SEGMENT9
2939 ,cpi.CUSTOM_SEGMENT10
2940 ,cpi.CUSTOM_SEGMENT11
2941 ,cpi.CUSTOM_SEGMENT12
2942 ,cpi.CUSTOM_SEGMENT13
2943 ,cpi.CUSTOM_SEGMENT14
2944 ,cpi.CUSTOM_SEGMENT15
2945 ,cpi.PEOPLE_GROUP_NAME
2946 ,cpi.PEOPLE_GROUP_SEGMENT1
2947 ,cpi.PEOPLE_GROUP_SEGMENT2
2948 ,cpi.PEOPLE_GROUP_SEGMENT3
2949 ,cpi.PEOPLE_GROUP_SEGMENT4
2950 ,cpi.PEOPLE_GROUP_SEGMENT5
2951 ,cpi.PEOPLE_GROUP_SEGMENT6
2952 ,cpi.PEOPLE_GROUP_SEGMENT7
2953 ,cpi.PEOPLE_GROUP_SEGMENT8
2954 ,cpi.PEOPLE_GROUP_SEGMENT9
2955 ,cpi.PEOPLE_GROUP_SEGMENT10
2956 ,cpi.PEOPLE_GROUP_SEGMENT11
2957 ,cpi.ASS_ATTRIBUTE_CATEGORY
2958 ,cpi.ASS_ATTRIBUTE1
2959 ,cpi.ASS_ATTRIBUTE2
2960 ,cpi.ASS_ATTRIBUTE3
2961 ,cpi.ASS_ATTRIBUTE4
2962 ,cpi.ASS_ATTRIBUTE5
2963 ,cpi.ASS_ATTRIBUTE6
2964 ,cpi.ASS_ATTRIBUTE7
2965 ,cpi.ASS_ATTRIBUTE8
2966 ,cpi.ASS_ATTRIBUTE9
2967 ,cpi.ASS_ATTRIBUTE10
2968 ,cpi.ASS_ATTRIBUTE11
2969 ,cpi.ASS_ATTRIBUTE12
2970 ,cpi.ASS_ATTRIBUTE13
2971 ,cpi.ASS_ATTRIBUTE14
2972 ,cpi.ASS_ATTRIBUTE15
2973 ,cpi.ASS_ATTRIBUTE16
2974 ,cpi.ASS_ATTRIBUTE17
2975 ,cpi.ASS_ATTRIBUTE18
2976 ,cpi.ASS_ATTRIBUTE19
2977 ,cpi.ASS_ATTRIBUTE20
2978 ,cpi.ASS_ATTRIBUTE21
2979 ,cpi.ASS_ATTRIBUTE22
2980 ,cpi.ASS_ATTRIBUTE23
2981 ,cpi.ASS_ATTRIBUTE24
2982 ,cpi.ASS_ATTRIBUTE25
2983 ,cpi.ASS_ATTRIBUTE26
2984 ,cpi.ASS_ATTRIBUTE27
2985 ,cpi.ASS_ATTRIBUTE28
2986 ,cpi.ASS_ATTRIBUTE29
2987 ,cpi.ASS_ATTRIBUTE30
2988 ,cpi.CPI_ATTRIBUTE_CATEGORY
2989 ,cpi.CPI_ATTRIBUTE1
2990 ,cpi.CPI_ATTRIBUTE2
2991 ,cpi.CPI_ATTRIBUTE3
2992 ,cpi.CPI_ATTRIBUTE4
2993 ,cpi.CPI_ATTRIBUTE5
2994 ,cpi.CPI_ATTRIBUTE6
2995 ,cpi.CPI_ATTRIBUTE7
2996 ,cpi.CPI_ATTRIBUTE8
2997 ,cpi.CPI_ATTRIBUTE9
2998 ,cpi.CPI_ATTRIBUTE10
2999 ,cpi.CPI_ATTRIBUTE11
3000 ,cpi.CPI_ATTRIBUTE12
3001 ,cpi.CPI_ATTRIBUTE13
3002 ,cpi.CPI_ATTRIBUTE14
3003 ,cpi.CPI_ATTRIBUTE15
3004 ,cpi.CPI_ATTRIBUTE16
3005 ,cpi.CPI_ATTRIBUTE17
3006 ,cpi.CPI_ATTRIBUTE18
3007 ,cpi.CPI_ATTRIBUTE19
3008 ,cpi.CPI_ATTRIBUTE20
3009 ,cpi.CPI_ATTRIBUTE21
3010 ,cpi.CPI_ATTRIBUTE22
3011 ,cpi.CPI_ATTRIBUTE23
3012 ,cpi.CPI_ATTRIBUTE24
3013 ,cpi.CPI_ATTRIBUTE25
3014 ,cpi.CPI_ATTRIBUTE26
3015 ,cpi.CPI_ATTRIBUTE27
3016 ,cpi.CPI_ATTRIBUTE28
3017 ,cpi.CPI_ATTRIBUTE29
3018 ,cpi.CPI_ATTRIBUTE30
3019 ,cpi.LAST_UPDATE_DATE
3020 ,cpi.LAST_UPDATED_BY
3021 ,cpi.LAST_UPDATE_LOGIN
3022 ,cpi.CREATED_BY
3023 ,cpi.CREATION_DATE
3024 ,cpi.FEEDBACK_DATE
3025 ,pil.lf_evt_ocrd_dt
3026 ,pil.group_pl_id
3027 ,pil.PER_IN_LER_STAT_CD
3028 ,ler.name LER_NAME
3029 ,pl.name group_pl_name
3030 ,pl.PERF_REVW_STRT_DT
3031 ,pl.EMP_INTERVIEW_TYP_CD
3032 ,pl.ASG_UPDT_EFF_DATE
3033 from ben_cwb_person_info cpi ,
3034 ben_per_in_ler pil ,
3035 ben_ler_f ler ,
3036 ben_cwb_pl_dsgn pl
3037 where cpi.person_id = p_person_id
3038 and cpi.group_per_in_ler_id = pil.per_in_ler_id
3039 and pil.ler_id = ler.ler_id
3040 and pil.group_pl_id = pl.pl_id
3041 and pl.oipl_id = -1
3042 and pil.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
3043 and cpi.effective_date
3044 between ler.effective_start_date and ler.effective_end_date
3045 ;
3046
3047
3048 cursor c_bg_name(p_business_group_id number) is
3049 select name
3050 from per_business_groups_perf bg
3051 where business_group_id = p_business_group_id ;
3052
3053 cursor c_org_name(p_org_id number) is
3054 select name
3055 from hr_all_organization_units_vl org
3056 where org.organization_id = p_org_id ;
3057
3058 cursor c_pos (p_pos_id number) is
3059 select name
3060 from per_positions
3061 where position_id = p_pos_id
3062 ;
3063
3064 cursor c_job(p_job_id number) is
3065 select name
3066 from per_jobs_vl
3067 where job_id = p_job_id
3068 ;
3069
3070 cursor c_grade(p_grade_id number) is
3071 select name
3072 from per_grades_vl
3073 where grade_id = p_grade_id
3074 ;
3075
3076 cursor c_loc(p_loc_id number) is
3077 select location_code
3078 from hr_locations_all
3079 where location_id = p_loc_id
3080 ;
3081
3082 cursor c_payr(p_rate_id number) is
3083 select name
3084 from pay_rates
3085 where rate_id = p_rate_id
3086 ;
3087
3088 cursor c_pln(p_pl_id number , p_dt date ) is
3089 select name
3090 from ben_cwb_pl_dsgn pl
3091 where p_pl_id = pl.PL_ID
3092 and pl.oipl_id = -1
3093 and p_dt = pl.lf_evt_ocrd_dt
3094 ;
3095
3096
3097 cursor c_groups (p_grp_id number) is
3098 select group_name
3099 from pay_people_groups
3100 where PEOPLE_GROUP_ID = p_grp_id
3101 ;
3102
3103 cursor c_asg_status (p_asg_stat_id number) is
3104 select user_status
3105 from PER_ASSIGNMENT_STATUS_TYPES
3106 where ASSIGNMENT_STATUS_TYPE_ID = p_asg_stat_id
3107 ;
3108
3109
3110
3111 cursor c_hr_lkup(p_lkup_type varchar2,
3112 p_lkup_code varchar2) is
3113 select meaning
3114 from hr_lookups
3115 where lookup_type = p_lkup_type
3116 and lookup_code = p_lkup_code
3117 ;
3118
3119 cursor c_tran (p_trn_id number ,
3120 p_trn_type varchar2) is
3121 select ATTRIBUTE3,
3122 ATTRIBUTE5,
3123 ATTRIBUTE6,
3124 ATTRIBUTE7,
3125 ATTRIBUTE8
3126 from ben_transaction
3127 where TRANSACTION_ID = p_trn_id
3128 and TRANSACTION_TYPE = p_trn_type
3129 ;
3130
3131 --- to get all the primary assignment within the period
3132
3133 cursor c_pay_p_asg (p_person_id number ,
3134 p_from_date date ,
3135 p_to_date date ) is
3136 select distinct assignment_id
3137 from per_all_assignments_f
3138 where person_id = p_person_id
3139 and ( primary_flag = 'Y' OR (ASSIGNMENT_TYPE ='A' and p_penserv_mode = 'N') ) -- vkodedal fix for 6798915, 9181637
3140 and effective_start_date <= p_to_date and
3141 effective_end_date >= p_from_date
3142 ;
3143
3144 -- to get th last date of the assignment to
3145 -- validate the assgnmnet against type
3146 cursor c_pay_asg_date (p_Assignment_id number ) is
3147 select effective_start_date
3148 from per_all_assignments_f
3149 where Assignment_id = p_Assignment_id
3150 and ( primary_flag = 'Y' OR (ASSIGNMENT_TYPE ='A' and p_penserv_mode = 'N') ) -- vkodedal fix for 6798915,9181637
3151 order by effective_start_date desc ;
3152 l_pay_asg_eff_date date ;
3153 l_tran c_tran%rowtype ;
3154 l_eff_event_scount number ;
3155 l_eff_event_ecount number ;
3156
3157
3158
3159 Begin
3160 --
3161 g_debug := hr_utility.debug_enabled;
3162 if g_debug then
3163 l_proc := g_package||'process_ext_person';
3164 hr_utility.set_location('Entering'||l_proc, 5);
3165 end if;
3166
3167 --
3168 -- Get general extract info
3169 --
3170 g_business_group_id := p_business_group_id;
3171 g_effective_date := p_effective_date;
3172 --
3173 g_person_id := p_person_id;
3174
3175 --
3176 SAVEPOINT cur_transaction;
3177 -- --------------------------------------------------
3178 -- Full Profile Extract
3179 -- --------------------------------------------------
3180 IF p_data_typ_cd = 'F' THEN
3181 --
3182 init_detail_globals;
3183 --
3184 ben_ext_util.get_ext_dates
3185 (p_ext_dfn_id => p_ext_dfn_id,
3186 p_data_typ_cd => p_data_typ_cd,
3187 p_effective_date => p_effective_date,
3188 p_person_ext_dt => l_person_ext_dt, --out
3189 p_benefits_ext_dt => l_benefits_ext_dt); -- out
3190 --
3191 g_person_ext_dt := l_person_ext_dt;
3192 g_benefits_ext_dt := l_benefits_ext_dt;
3193 --
3194 g_rcd_seq := 1;
3195 --
3196 g_trans_num := 1;
3197
3198
3199 Extract_person_info(p_person_id => p_person_id,
3200 p_effective_date => p_effective_date, -- passed in from conc mgr
3201 p_business_group_id => p_business_group_id,
3202 p_ext_rslt_id => p_ext_rslt_id
3203 ) ;
3204 --
3205 l_include := 'Y';
3206 --
3207 if p_ext_crit_prfl_id is not null THEN
3208 --
3209 ben_ext_evaluate_inclusion.Evaluate_Person_Incl
3210 (p_person_id => p_person_id,
3211 p_postal_code => g_prim_postal_code,
3212 p_org_id => g_employee_organization_id,
3213 p_loc_id => g_location_id,
3214 p_gre => null, -- this will be fetched in called program.
3215 p_state => g_prim_state,
3216 p_bnft_grp => g_benefit_group_id,
3217 p_ee_status => g_employee_status_id,
3218 p_chg_evt_cd => null,
3219 p_effective_date => g_person_ext_dt,
3220 p_actl_date => null,
3221 p_include => l_include);
3222 --
3223 end if;
3224 --
3225 if l_include = 'Y' then
3226 --
3227 if nvl(ben_extract.g_spcl_hndl_flag,'X') <> 'Y' then -- normal processing
3228
3229 process_ext_levels(
3230 p_person_id => p_person_id,
3231 p_ext_rslt_id => p_ext_rslt_id,
3232 p_ext_file_id => p_ext_file_id,
3233 p_data_typ_cd => p_data_typ_cd,
3234 p_ext_typ_cd => p_ext_typ_cd,
3235 p_business_group_id => p_business_group_id,
3236 p_effective_date => g_effective_date
3237 );
3238 else -- special handling flag tells us that it is an ansi 834 extract.
3239 --
3240 ben_ext_ansi.main(
3241 p_person_id => p_person_id,
3242 p_ext_rslt_id => p_ext_rslt_id,
3243 p_ext_file_id => p_ext_file_id,
3244 p_data_typ_cd => p_data_typ_cd,
3245 p_ext_typ_cd => p_ext_typ_cd,
3246 p_ext_crit_prfl_id => p_ext_crit_prfl_id,
3247 p_business_group_id => p_business_group_id,
3248 p_effective_date => g_benefits_ext_dt
3249 );
3250 end if;
3251 --
3252 end if; -- l_include = 'Y'
3253 --
3254 -- ==========================================
3255 -- Changes Only Extract
3256 -- ==========================================
3257 ELSIF p_data_typ_cd = 'C' THEN
3258 --
3259 g_trans_num := 1;
3260 --
3261 ben_ext_util.get_chg_dates
3262 (p_ext_dfn_id => ben_extract.g_ext_dfn_id, --in
3263 p_effective_date => g_effective_date, --in
3264 p_chg_actl_strt_dt => l_chg_actl_strt_dt, --out
3265 p_chg_actl_end_dt => l_chg_actl_end_dt, --out
3266 p_chg_eff_strt_dt => l_chg_eff_strt_dt, --out
3267 p_chg_eff_end_dt => l_chg_eff_end_dt); --out
3268
3269 -- if the parameter passed from extract , then overide the criteria dates
3270 if ben_ext_thread.g_effective_start_date is not null then
3271 l_chg_eff_strt_dt := ben_ext_thread.g_effective_start_date ;
3272 l_chg_eff_end_dt := ben_ext_thread.g_effective_end_date ;
3273 end if ;
3274
3275 if ben_ext_thread.g_actual_start_date is not null then
3276 l_chg_actl_strt_dt := ben_ext_thread.g_actual_start_date ;
3277 l_chg_actl_end_dt := ben_ext_thread.g_actual_end_date ;
3278 end if ;
3279
3280 hr_utility.set_location( 'chg actl date ' || l_chg_actl_strt_dt || ' / ' || l_chg_actl_end_dt, 99 );
3281 hr_utility.set_location( 'chg eff date ' || l_chg_eff_strt_dt || ' / ' || l_chg_eff_end_dt , 99 );
3282 --
3283 if ben_ext_thread.g_chg_ext_from_ben = 'Y' then
3284 hr_utility.set_location( ' extract chg evt log included ' , 99 );
3285 open c_changes_only_extract (l_chg_actl_strt_dt,
3286 l_chg_actl_end_dt,
3287 l_chg_eff_strt_dt,
3288 l_chg_eff_end_dt);
3289 LOOP
3290 --
3291 init_detail_globals;
3292 --
3293 FETCH c_changes_only_extract into
3294 --
3295 g_ext_chg_evt_log_id,
3296 g_chg_evt_cd,
3297 g_chg_eff_dt,
3298 g_chg_actl_dt,
3299 g_chg_last_update_login,
3300 g_chg_prmtr_01,
3301 g_chg_prmtr_02,
3302 g_chg_prmtr_03,
3303 g_chg_prmtr_04,
3304 g_chg_prmtr_05,
3305 g_chg_prmtr_06,
3306 g_chg_old_val1,
3307 g_chg_old_val2,
3308 g_chg_old_val3,
3309 g_chg_old_val4,
3310 g_chg_old_val5,
3311 g_chg_old_val6,
3312 g_chg_new_val1,
3313 g_chg_new_val2,
3314 g_chg_new_val3,
3315 g_chg_new_val4,
3316 g_chg_new_val5,
3317 g_chg_new_val6,
3318 g_chg_evt_source
3319 ;
3320 --
3321 EXIT WHEN c_changes_only_extract%NOTFOUND;
3322 --
3323 --g_extract_date := g_chg_eff_dt;
3324 --
3325 ben_ext_util.get_ext_dates
3326 (p_ext_dfn_id => p_ext_dfn_id,
3327 p_data_typ_cd => p_data_typ_cd,
3328 p_effective_date => g_effective_date,
3329 p_person_ext_dt => l_person_ext_dt, --out
3330 p_benefits_ext_dt => l_benefits_ext_dt); -- out
3331 --
3332 g_person_ext_dt := l_person_ext_dt;
3333 g_benefits_ext_dt := l_benefits_ext_dt;
3334 --
3335 l_include := 'Y';
3336 --
3337 if p_ext_crit_prfl_id is not null THEN
3338 --
3339 ben_ext_evaluate_inclusion.evaluate_change_log_incl
3340 (p_chg_evt_cd => g_chg_evt_cd,
3341 p_chg_evt_source => g_chg_evt_source,
3342 p_chg_eff_dt => g_chg_eff_dt,
3343 p_chg_actl_dt => g_chg_actl_dt,
3344 p_last_update_login => g_chg_last_update_login,
3345 p_effective_date => g_effective_date,
3346 p_include => l_include);
3347 --
3348 end if; -- p_ext_crit_prfl_id is not null
3349 --
3350 if l_include = 'Y' then
3351 --
3352
3353 Extract_person_info(p_person_id => p_person_id,
3354 p_effective_date => p_effective_date, -- passed in from conc mgr
3355 p_business_group_id => p_business_group_id,
3356 p_ext_rslt_id => p_ext_rslt_id
3357 ) ;
3358 --
3359 if p_ext_crit_prfl_id is not null THEN
3360 --
3361 ben_ext_evaluate_inclusion.Evaluate_Person_Incl
3362 (p_person_id => p_person_id,
3363 p_postal_code => g_prim_postal_code,
3364 p_org_id => g_employee_organization_id,
3365 p_loc_id => g_location_id,
3366 p_gre => null, -- this will be fetched in called program.
3367 p_state => g_prim_state,
3368 p_bnft_grp => g_benefit_group_id,
3369 p_ee_status => g_employee_status_id,
3370 p_chg_evt_cd => g_chg_evt_cd,
3371 p_chg_evt_source => g_chg_evt_source,
3372 p_effective_date => g_person_ext_dt,
3373 --RCHASE
3374 p_eff_date => g_chg_eff_dt,
3375 --End RCHASE
3376 p_actl_date => g_chg_actl_dt,
3377 p_include => l_include);
3378 --
3379 end if; -- p_ext_crit_prfl_id is not null
3380 --
3381 end if; -- l_include = 'Y'
3382 --
3383 IF l_include = 'Y' THEN
3384 --
3385 -- Not really sure what this hard coding is all about, should be investigated. th.
3386 --
3387 if g_debug then
3388 hr_utility.set_location(' Change Event Code ' || g_chg_evt_cd , 99 );
3389 end if;
3390 --BBurns Bug 1745274. Set context for AD and DD also on line below.
3391 /*
3392 CODE PRIOR TO WWBUG: 2008949
3393 if g_chg_evt_cd in ('AB', 'AD', 'DD', 'RB', 'TBAC', 'TBBC', 'UOBO', 'CCSD', 'CCED') then
3394 */
3395 /* Start of Changes for WWBUG: 2008949 added COECA */
3396 if g_chg_evt_cd in ('AB', 'AD', 'DD', 'RB', 'TBAC',
3397 'TBBC', 'UOBO', 'CCSD', 'CCED', 'COECA') then
3398 /* End of Changes for WWBUG: 2008949 added COECA */
3399 --
3400 g_chg_pl_id := g_chg_prmtr_01;
3401 g_chg_enrt_rslt_id := g_chg_prmtr_03;
3402 --
3403 elsif g_chg_evt_cd in ('DEE', 'AEE', 'UEE') then
3404 --
3405 g_chg_input_value_id := to_number(g_chg_prmtr_02);
3406 --
3407 /* Start of Changes for WWBUG: 2008949: addition */
3408 --
3409 g_chg_enrt_rslt_id := to_number(g_chg_prmtr_03);
3410
3411 if g_chg_enrt_rslt_id is null
3412 then
3413 --
3414 --Fetch the prtt_enrt_rslt_id. This will be the only enrollment link
3415 --between the chg_evt_log and ben_prtt_enrt_rslt_id
3416 --
3417 open c_chg_penid(p_element_entry_id => to_number(g_chg_prmtr_01),
3418 p_effective_date => g_chg_eff_dt);
3419 fetch c_chg_penid into g_chg_enrt_rslt_id;
3420 if c_chg_penid%notfound
3421 then
3422 --we do not have a link between the chg_evt and an
3423 --enrollment.
3424 g_chg_enrt_rslt_id := null;
3425 end if;
3426 close c_chg_penid;
3427 end if;
3428 /* End of Changes for WWBUG: 2008949: addition */
3429 end if;
3430 --
3431 -- get change log information
3432 --
3433 IF g_chg_evt_cd in ( 'CON', 'COUN') THEN
3434 --
3435 if g_chg_old_val5 is not null then
3436 g_previous_last_name := g_chg_old_val5; -- needs fixing.
3437 g_previous_first_name := g_chg_old_val3;
3438 g_previous_middle_name := g_chg_old_val4;
3439 g_previous_suffix := g_chg_old_val6;
3440 end if ;
3441
3442 if g_debug then
3443 hr_utility.set_location(' l name ' || g_previous_last_name , 99 );
3444 hr_utility.set_location(' f name ' || g_previous_first_name , 99 );
3445 hr_utility.set_location(' m name ' || g_previous_middle_name , 99 );
3446 end if;
3447
3448 ELSIF g_chg_evt_cd = 'CONS' THEN
3449 g_previous_prefix := g_chg_old_val1 ;
3450 --
3451 ELSIF g_chg_evt_cd = 'COSS' THEN
3452 --
3453 g_previous_ssn := g_chg_old_val1 ;
3454 ELSIF g_chg_evt_cd = 'COG' then
3455 g_previous_sex := g_chg_old_val1 ;
3456 --
3457 ELSIF g_chg_evt_cd = 'CODB' THEN
3458 --
3459 g_previous_dob := to_date(g_chg_old_val1 ,'MM/DD/YYYY');
3460 --
3461 END IF;
3462 --
3463 g_rcd_seq := 1; -- what's this do? th.
3464 --
3465 if nvl(ben_extract.g_spcl_hndl_flag,'X') <> 'Y' then -- normal processing
3466 --
3467 process_ext_levels(
3468 p_person_id => p_person_id,
3469 p_ext_rslt_id => p_ext_rslt_id,
3470 p_ext_file_id => p_ext_file_id,
3471 p_data_typ_cd => p_data_typ_cd,
3472 p_ext_typ_cd => p_ext_typ_cd,
3473 p_business_group_id => p_business_group_id,
3474 p_effective_date => g_effective_date
3475 );
3476 else -- special handling flag tells us that it is an ansi 834 extract.
3477 --
3478 ben_ext_ansi.main(
3479 p_person_id => p_person_id,
3480 p_ext_rslt_id => p_ext_rslt_id,
3481 p_ext_file_id => p_ext_file_id,
3482 p_data_typ_cd => p_data_typ_cd,
3483 p_ext_typ_cd => p_ext_typ_cd,
3484 p_ext_crit_prfl_id => p_ext_crit_prfl_id,
3485 p_business_group_id => p_business_group_id,
3486 p_effective_date => g_benefits_ext_dt
3487 );
3488 end if;
3489 --
3490 g_trans_num := g_trans_num + 1;
3491 --
3492 END IF; -- l_include = 'Y'
3493
3494 --
3495 END LOOP; --changes
3496
3497 --
3498 close c_changes_only_extract;
3499 --
3500 end if ; --- for extract chg logs
3501
3502
3503 if ben_ext_thread.g_chg_ext_from_pay = 'Y' then
3504 hr_utility.set_location( ' PAY event log included ' , 99 );
3505 -- Loop thorough all the assignment id for a person
3506 -- within the extract period
3507 --- get the primary assg as of effective date
3508 init_assignment_id(p_person_id => p_person_id ,
3509 p_effective_date => p_effective_date) ;
3510
3511 l_pay_tot_Srno := 1 ;
3512 l_pay_evt_srno := 1 ;
3513
3514 --- determine the adv dates only one for a process
3515
3516 If ben_ext_evaluate_inclusion.g_chg_actl_dt_incl_rqd = 'N' and
3517 ben_ext_evaluate_inclusion.g_chg_eff_dt_incl_rqd = 'N' and
3518 ben_ext_evaluate_inclusion.g_cmbn_incl_rqd = 'Y' then
3519
3520 hr_utility.set_location('pay adv condition mode ' ||g_pay_adv_date_mode , 66 );
3521 if g_pay_adv_date_mode is null then
3522 hr_utility.set_location('pay adv condition exisit withoutot other criteria' , 66 );
3523 get_pay_adv_crit_dates(
3524 p_ext_crit_prfl_id => p_ext_crit_prfl_id,
3525 p_ext_dfn_id => p_ext_dfn_id,
3526 p_business_group_id => p_business_group_id,
3527 p_effective_date => p_effective_date,
3528 p_eff_from_dt => g_pay_adv_eff_from_dt,
3529 p_eff_to_dt => g_pay_adv_eff_to_dt,
3530 p_act_from_dt => g_pay_adv_act_from_dt ,
3531 p_act_to_dt => g_pay_adv_act_to_dt,
3532 p_date_mode => g_pay_adv_date_mode
3533 ) ;
3534 end if ;
3535
3536 end if ;
3537
3538
3539 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)),
3540 nvl(l_chg_eff_end_dt,nvl(l_chg_actl_end_dt,p_effective_date))
3541 )
3542 Loop
3543 hr_utility.set_location(' pay assg id ' ||pasg.Assignment_id , 66 ) ;
3544
3545 open c_pay_asg_date (pasg.Assignment_id) ;
3546 fetch c_pay_asg_date into l_pay_asg_eff_date ;
3547 close c_pay_asg_date ;
3548 hr_utility.set_location(' pay assg date ' ||l_pay_asg_eff_date , 66 ) ;
3549 hr_utility.set_location(' pay actual start date ' ||l_chg_actl_strt_dt , 66 ) ;
3550
3551 -- determine the assignment before call the interpreter
3552 init_assignment_id(p_person_id => p_person_id ,
3553 p_effective_date => l_pay_asg_eff_date ,
3554 p_Assignment_id => pasg.Assignment_id ) ;
3555
3556
3557 l_pay_Assignment_id := g_assignment_id ;
3558 if l_pay_Assignment_id is not null then
3559
3560
3561 -- this is a pqp idea to collect the unique column and group id
3562 -- pls dont change the logic unless agreed with pqp
3563 -- this loop collect all the change event result from PEI and colect in a table
3564 -- and also collect the unique table/column/event intto global table
3565 -- pqp need the global table, only used in formula
3566 for i in c_chg_pay_evt
3567 Loop
3568 l_pay_detail_tab.delete ;
3569 l_pay_proration_dates.delete ;
3570 l_pay_proration_changes.delete ;
3571 l_pay_pro_type_tab.delete ;
3572
3573
3574 If ben_ext_evaluate_inclusion.g_chg_actl_dt_incl_rqd = 'N' and
3575 ben_ext_evaluate_inclusion.g_chg_eff_dt_incl_rqd = 'N' and
3576 ben_ext_evaluate_inclusion.g_cmbn_incl_rqd = 'Y' then
3577
3578
3579
3580
3581 Begin
3582
3583
3584 if g_pay_adv_date_mode = 'B' or g_pay_adv_date_mode = 'E' then
3585 hr_utility.set_location('adv effective date mode '||g_pay_adv_eff_from_dt||'/'||
3586 g_pay_adv_eff_to_dt,99) ;
3587
3588 l_eff_event_ecount := 0 ;
3589 l_eff_event_scount := 0 ;
3590 ben_ext_util.entries_affected
3591 (p_assignment_id => l_pay_Assignment_id
3592 ,p_event_group_id => i.event_group_id
3593 ,p_mode => NULL -- 'DATE_PROCESSED' -- 'DATE_EARNED' --
3594 ,p_start_date => (g_pay_adv_eff_from_dt-1)
3595 -- since the PDI use the exclisive of the start and end
3596 ,p_end_date => (g_pay_adv_eff_to_dt)
3597 ,p_business_group_id => p_business_group_id
3598 ,p_detailed_output => l_pay_detail_tab
3599 ,p_process_mode => 'ENTRY_EFFECTIVE_DATE'
3600 ,p_penserv_mode => p_penserv_mode -- vkodedal - changes for penserver -30-apr-2008
3601 );
3602
3603 hr_utility.set_location( 'number of result ' ||l_pay_detail_tab.count, 99 ) ;
3604
3605 -- get the starting count of total colection for comparison
3606 l_eff_event_scount := l_pay_tot_Srno ;
3607
3608 if l_pay_detail_tab.count > 0 then
3609 -- collect all the information onto a table for process for a person
3610 FOR l_pay IN l_pay_detail_tab.FIRST..l_pay_detail_tab.LAST
3611 LOOP
3612
3613 hr_utility.set_location(' insertining tot '|| l_pay_tot_Srno|| ' / '
3614 ||l_pay_detail_tab(l_pay).column_name,99) ;
3615
3616 l_pay_detail_tot_tab(l_pay_tot_Srno).dated_table_id
3617 := l_pay_detail_tab(l_pay).dated_table_id ;
3618 l_pay_detail_tot_tab(l_pay_tot_Srno).datetracked_event
3619 := l_pay_detail_tab(l_pay).datetracked_event ;
3620 l_pay_detail_tot_tab(l_pay_tot_Srno).update_type
3621 := l_pay_detail_tab(l_pay).update_type ;
3622 l_pay_detail_tot_tab(l_pay_tot_Srno).surrogate_key
3623 := l_pay_detail_tab(l_pay).surrogate_key ;
3624 l_pay_detail_tot_tab(l_pay_tot_Srno).column_name
3625 := l_pay_detail_tab(l_pay).column_name ;
3626 l_pay_detail_tot_tab(l_pay_tot_Srno).effective_date
3627 := l_pay_detail_tab(l_pay).effective_date ;
3628 l_pay_detail_tot_tab(l_pay_tot_Srno).old_value
3629 := l_pay_detail_tab(l_pay).old_value ;
3630 l_pay_detail_tot_tab(l_pay_tot_Srno).new_value
3631 := l_pay_detail_tab(l_pay).new_value ;
3632 l_pay_detail_tot_tab(l_pay_tot_Srno).change_values
3633 := l_pay_detail_tab(l_pay).change_values ;
3634 l_pay_detail_tot_tab(l_pay_tot_Srno).proration_type
3635 := l_pay_detail_tab(l_pay).proration_type ;
3636 l_pay_detail_tot_tab(l_pay_tot_Srno).change_mode
3637 := l_pay_detail_tab(l_pay).change_mode ;
3638 l_pay_detail_tot_tab(l_pay_tot_Srno).event_group_id := i.event_group_id ;
3639 l_pay_detail_tot_tab(l_pay_tot_Srno).actual_date
3640 := l_pay_detail_tab(l_pay).creation_date ;
3641 l_pay_tot_Srno := l_pay_tot_Srno + 1 ;
3642
3643 End loop ;
3644 end if ;
3645
3646 End if ;
3647
3648
3649 if g_pay_adv_date_mode = 'B' or g_pay_adv_date_mode = 'C' then
3650 -- get the total count of srno for efficient comaprison
3651 l_eff_event_ecount := l_pay_detail_tot_tab.count ;
3652 l_pay_detail_tab.delete ;
3653 hr_utility.set_location('adv actual date mode '||g_pay_adv_act_from_dt||' / ' ||
3654 g_pay_adv_act_to_dt,99) ;
3655 ben_ext_util.entries_affected
3656 (p_assignment_id => l_pay_Assignment_id
3657 ,p_event_group_id => i.event_group_id
3658 ,p_mode => NULL -- 'DATE_PROCESSED' -- 'DATE_EARNED' --
3659 ,p_start_date => trunc(g_pay_adv_act_from_dt)
3660 -- since the PDI use the exclisive of the start and end
3661 ,p_end_date => (trunc(g_pay_adv_act_to_dt)+0.99999)
3662 ,p_business_group_id => p_business_group_id
3663 ,p_detailed_output => l_pay_detail_tab
3664 ,p_process_mode => 'ENTRY_CREATION_DATE'
3665 ,p_penserv_mode => p_penserv_mode --vkodedal changes for penserver - 30-apr-2008
3666 );
3667
3668
3669
3670 hr_utility.set_location( 'number of result ' ||l_pay_detail_tab.count, 99 ) ;
3671
3672 if l_pay_detail_tab.count > 0 then
3673
3674 -- collect all the information onto a table for process for a person
3675 FOR l_pay IN l_pay_detail_tab.FIRST..l_pay_detail_tab.LAST
3676 LOOP
3677
3678
3679 l_g_c_found := 'N' ;
3680 -- Look for the duplication from actaul and effective
3681 if g_pay_adv_date_mode = 'B' and (l_eff_event_ecount-l_eff_event_scount) >= 0 then
3682 --for l_g_c IN 1 .. l_pay_detail_tot_tab.count
3683 for l_g_c IN l_eff_event_scount .. l_eff_event_ecount
3684 Loop
3685 if l_pay_detail_tot_tab(l_g_c).dated_table_id=l_pay_detail_tab(l_pay).dated_table_id
3686 and l_pay_detail_tot_tab(l_g_c).event_group_id = i.event_group_id
3687 and l_pay_detail_tot_tab(l_g_c).surrogate_key
3688 = l_pay_detail_tab(l_pay).surrogate_key
3689 and l_pay_detail_tot_tab(l_g_c).update_type
3690 = l_pay_detail_tab(l_pay).update_type
3691 and l_pay_detail_tot_tab(l_g_c).effective_date
3692 = l_pay_detail_tab(l_pay).effective_date
3693 and l_pay_detail_tot_tab(l_g_c).actual_Date
3694 = l_pay_detail_tab(l_pay).creation_date
3695 and nvl(l_pay_detail_tot_tab(l_g_c).column_name,'-1')
3696 = nvl(l_pay_detail_tab(l_pay).column_name,'-1')
3697 and nvl(l_pay_detail_tot_tab(l_g_c).datetracked_event,'-1')
3698 = nvl(l_pay_detail_tab(l_pay).datetracked_event,'-1')
3699 and nvl(l_pay_detail_tot_tab(l_g_c).proration_type,'-1')
3700 = nvl(l_pay_detail_tab(l_pay).proration_type,'-1')
3701 and nvl(l_pay_detail_tot_tab(l_g_c).change_mode,'-1')
3702 = nvl(l_pay_detail_tab(l_pay).change_mode,'-1')
3703 and nvl(l_pay_detail_tot_tab(l_g_c).change_values,'-1')
3704 = nvl(l_pay_detail_tab(l_pay).change_values,'-1')
3705 and nvl(l_pay_detail_tot_tab(l_g_c).old_value,'-1')
3706 = nvl(l_pay_detail_tab(l_pay).old_value,'-1')
3707 and nvl(l_pay_detail_tot_tab(l_g_c).new_value,'-1')
3708 = nvl(l_pay_detail_tab(l_pay).new_value,'-1')
3709 then
3710 l_g_c_found := 'Y' ;
3711 exit ;
3712 end if ;
3713 End loop ;
3714 End if ;
3715
3716
3717 --- if the entry is unique then create
3718 if l_g_c_found = 'N' then
3719
3720 hr_utility.set_location(' insertining tot '|| l_pay_tot_Srno|| ' / '
3721 ||l_pay_detail_tab(l_pay).column_name,99) ;
3722
3723 l_pay_detail_tot_tab(l_pay_tot_Srno).dated_table_id
3724 := l_pay_detail_tab(l_pay).dated_table_id ;
3725 l_pay_detail_tot_tab(l_pay_tot_Srno).datetracked_event
3726 := l_pay_detail_tab(l_pay).datetracked_event ;
3727 l_pay_detail_tot_tab(l_pay_tot_Srno).update_type
3728 := l_pay_detail_tab(l_pay).update_type ;
3729 l_pay_detail_tot_tab(l_pay_tot_Srno).surrogate_key
3730 := l_pay_detail_tab(l_pay).surrogate_key ;
3731 l_pay_detail_tot_tab(l_pay_tot_Srno).column_name
3732 := l_pay_detail_tab(l_pay).column_name ;
3733 l_pay_detail_tot_tab(l_pay_tot_Srno).effective_date
3734 := l_pay_detail_tab(l_pay).effective_date ;
3735 l_pay_detail_tot_tab(l_pay_tot_Srno).old_value
3736 := l_pay_detail_tab(l_pay).old_value ;
3737 l_pay_detail_tot_tab(l_pay_tot_Srno).new_value
3738 := l_pay_detail_tab(l_pay).new_value ;
3739 l_pay_detail_tot_tab(l_pay_tot_Srno).change_values
3740 := l_pay_detail_tab(l_pay).change_values ;
3741 l_pay_detail_tot_tab(l_pay_tot_Srno).proration_type
3742 := l_pay_detail_tab(l_pay).proration_type ;
3743 l_pay_detail_tot_tab(l_pay_tot_Srno).change_mode
3744 := l_pay_detail_tab(l_pay).change_mode ;
3745 l_pay_detail_tot_tab(l_pay_tot_Srno).event_group_id := i.event_group_id ;
3746 l_pay_detail_tot_tab(l_pay_tot_Srno).actual_date
3747 := l_pay_detail_tab(l_pay).creation_date ;
3748 l_pay_tot_Srno := l_pay_tot_Srno + 1 ;
3749 end if ; -- unique entry
3750 end loop ;
3751
3752 End if ;
3753
3754 end if;
3755 Exception
3756 WHEN hr_application_error THEN
3757 -- the exception handled only when thge pqp raise the error with the msg
3758 IF hr_utility.get_message = 'BEN_94629_NO_ASG_ACTION_ID' THEN
3759 hr_utility.set_location( 'Current assignment has no Assignment Action id. ' ,-9999);
3760 g_err_num := 94629 ;
3761 g_err_name := 'BEN_94629_NO_ASG_ACTION_ID' ;
3762 g_elmt_name:= null ;
3763 raise detail_restart_error ;
3764 else
3765 hr_utility.set_location( 'unknow exception raised in pqp.',-9999);
3766 raise; -- to re-raise the exception
3767 end if ;
3768
3769 End ;
3770
3771 Else
3772
3773
3774 Begin
3775
3776 if l_chg_actl_strt_dt is not null and ben_ext_evaluate_inclusion.g_chg_actl_dt_incl_rqd = 'Y' then
3777 -- call the interpreter in actual date mode
3778 -- as per my understanding from PQP - ram
3779 -- since the actual date has the time stamp , the time stamp play the role in extracting info
3780 -- so the from date is truncated and to date is extended to the last second of the day
3781
3782 hr_utility.set_location('pay actual dt mode '||trunc(l_chg_actl_strt_dt)||' / '||
3783 (trunc(l_chg_actl_end_dt)+0.99999) , 66 );
3784
3785 ben_ext_util.entries_affected
3786 (p_assignment_id => l_pay_Assignment_id
3787 ,p_event_group_id => i.event_group_id
3788 ,p_mode => NULL -- 'DATE_PROCESSED' -- 'DATE_EARNED' --
3789 ,p_start_date => trunc(l_chg_actl_strt_dt)
3790 -- since the PDI use the exclisive of the start and end
3791 ,p_end_date => (trunc(l_chg_actl_end_dt)+0.99999)
3792 ,p_business_group_id => p_business_group_id
3793 ,p_detailed_output => l_pay_detail_tab
3794 ,p_process_mode => 'ENTRY_CREATION_DATE'
3795 ,p_penserv_mode => p_penserv_mode --vkodedal changes for penserver - 30-apr-2008
3796 );
3797
3798 else
3799 -- call in payroll interpreter in effctive date mode
3800 -- payroll exclude the from date data for proration purpose ,
3801 --the interpreter developerd for proration
3802 -- then used for reporting so the functionality remains the same
3803 -- we are passing -1 to make sure the from date data is included
3804 hr_utility.set_location(' pay effectivedt mode ' ||(l_chg_eff_strt_dt-1) || ' / ' ||
3805 l_chg_eff_end_dt , 66 ) ;
3806 ben_ext_util.entries_affected
3807 (p_assignment_id => l_pay_Assignment_id
3808 ,p_event_group_id => i.event_group_id
3809 ,p_mode => NULL -- 'DATE_PROCESSED' -- 'DATE_EARNED' --
3810 ,p_start_date => (l_chg_eff_strt_dt-1)
3811 -- since the PDI use the exclisive of the start and end
3812 ,p_end_date => (l_chg_eff_end_dt)
3813 ,p_business_group_id => p_business_group_id
3814 ,p_detailed_output => l_pay_detail_tab
3815 ,p_process_mode => 'ENTRY_EFFECTIVE_DATE'
3816 ,p_penserv_mode => p_penserv_mode --vkodedal changes for penserver - 30-apr-2008
3817 );
3818 end if ;
3819 Exception
3820 WHEN hr_application_error THEN
3821 -- the exception handled only when thge pqp raise the error with the msg
3822 IF hr_utility.get_message = 'BEN_94629_NO_ASG_ACTION_ID' THEN
3823 hr_utility.set_location( 'Current assignment has no Assignment Action id. ' ,-9999);
3824 g_err_num := 94629 ;
3825 g_err_name := 'BEN_94629_NO_ASG_ACTION_ID' ;
3826 g_elmt_name:= null ;
3827 raise detail_restart_error ;
3828 else
3829 hr_utility.set_location( 'unknow exception raised in ben_ext_util.entries_affected.',-9999);
3830 raise; -- to re-raise the exception
3831 end if ;
3832 End ;
3833
3834 hr_utility.set_location( 'number of result ' ||l_pay_detail_tab.count, 99 ) ;
3835 if l_pay_detail_tab.count > 0 then
3836
3837 -- collect all the information onto a table for process for a person
3838 FOR l_pay IN l_pay_detail_tab.FIRST..l_pay_detail_tab.LAST
3839 LOOP
3840
3841 hr_utility.set_location(' insertining tot '|| l_pay_tot_Srno|| ' / ' ||
3842 l_pay_detail_tab(l_pay).column_name,99) ;
3843
3844 l_pay_detail_tot_tab(l_pay_tot_Srno).dated_table_id := l_pay_detail_tab(l_pay).dated_table_id ;
3845 l_pay_detail_tot_tab(l_pay_tot_Srno).datetracked_event
3846 :=l_pay_detail_tab(l_pay).datetracked_event ;
3847 l_pay_detail_tot_tab(l_pay_tot_Srno).update_type := l_pay_detail_tab(l_pay).update_type ;
3848 l_pay_detail_tot_tab(l_pay_tot_Srno).surrogate_key := l_pay_detail_tab(l_pay).surrogate_key ;
3849 l_pay_detail_tot_tab(l_pay_tot_Srno).column_name := l_pay_detail_tab(l_pay).column_name ;
3850 l_pay_detail_tot_tab(l_pay_tot_Srno).effective_date := l_pay_detail_tab(l_pay).effective_date ;
3851 l_pay_detail_tot_tab(l_pay_tot_Srno).old_value := l_pay_detail_tab(l_pay).old_value ;
3852 l_pay_detail_tot_tab(l_pay_tot_Srno).new_value := l_pay_detail_tab(l_pay).new_value ;
3853 l_pay_detail_tot_tab(l_pay_tot_Srno).change_values := l_pay_detail_tab(l_pay).change_values ;
3854 l_pay_detail_tot_tab(l_pay_tot_Srno).proration_type := l_pay_detail_tab(l_pay).proration_type ;
3855 l_pay_detail_tot_tab(l_pay_tot_Srno).change_mode := l_pay_detail_tab(l_pay).change_mode ;
3856 l_pay_detail_tot_tab(l_pay_tot_Srno).event_group_id := i.event_group_id ;
3857 l_pay_detail_tot_tab(l_pay_tot_Srno).actual_date := l_pay_detail_tab(l_pay).creation_date;
3858 l_pay_tot_Srno := l_pay_tot_Srno + 1 ;
3859
3860 --- find the unique column for global colection for a person
3861 l_g_c_found := 'N' ;
3862 for l_g_c IN 1 .. g_pay_evt_group_tab.count
3863 Loop
3864 if g_pay_evt_group_tab(l_g_c).dated_table_id = l_pay_detail_tab(l_pay).dated_table_id and
3865 g_pay_evt_group_tab(l_g_c).column_name = l_pay_detail_tab(l_pay).column_name and
3866 g_pay_evt_group_tab(l_g_c).event_group_id = i.event_group_id then
3867 l_g_c_found := 'Y' ;
3868 exit ;
3869 end if ;
3870 End loop ;
3871 -- if the value not already exist
3872 if l_g_c_found = 'N' then
3873 hr_utility.set_location('insertining GL '||l_pay_evt_srno||' / '||
3874 l_pay_detail_tab(l_pay).column_name,99) ;
3875 g_pay_evt_group_tab(l_pay_evt_srno).dated_table_id:=l_pay_detail_tab(l_pay).dated_table_id ;
3876 g_pay_evt_group_tab(l_pay_evt_srno).column_name := l_pay_detail_tab(l_pay).column_name ;
3877 g_pay_evt_group_tab(l_pay_evt_srno).event_group_id := i.event_group_id ;
3878 l_pay_evt_srno := l_pay_evt_srno + 1 ;
3879 end if ;
3880 End Loop ;
3881 End if ;
3882 End If; --- adv criteria
3883
3884 End Loop ;
3885 End if ; -- asg id is not null
3886 end loop ; -- multiple asg id
3887 --- sor the table value
3888
3889 -- reintialise the global
3890 init_assignment_id(p_person_id => p_person_id ,
3891 p_effective_date => p_effective_date) ;
3892
3893
3894 ben_ext_payroll_balance.sort_payroll_events
3895 (p_pay_events_tab => l_pay_detail_tot_tab ) ;
3896
3897 -- process the collected nformation for a person
3898 hr_utility.set_location( 'number of sorted result ' ||g_pay_proc_evt_tab.count, 99 ) ;
3899 if g_pay_proc_evt_tab.count > 0 then
3900 FOR l_pay IN 1 .. g_pay_proc_evt_tab.count
3901 LOOP
3902 init_detail_globals;
3903
3904 hr_utility.set_location( ' column name ' ||g_pay_proc_evt_tab(l_pay).column_name
3905 ||' / '||g_pay_proc_evt_tab(l_pay).dated_table_id , 99 );
3906
3907 l_dated_table_id := g_pay_proc_evt_tab(l_pay).dated_table_id ;
3908 g_chg_pay_column := g_pay_proc_evt_tab(l_pay).column_name ;
3909 g_chg_eff_dt := g_pay_proc_evt_tab(l_pay).effective_date ;
3910 g_chg_old_val1 := g_pay_proc_evt_tab(l_pay).old_value ;
3911 g_chg_new_val1 := g_pay_proc_evt_tab(l_pay).new_value ;
3912 g_chg_evt_cd := g_pay_proc_evt_tab(l_pay).event_group_id ;
3913 g_chg_pay_mode := g_pay_proc_evt_tab(l_pay).change_mode ;
3914 g_chg_update_type := g_pay_proc_evt_tab(l_pay).update_type ;
3915 g_chg_surrogate_key := g_pay_proc_evt_tab(l_pay).surrogate_key ;
3916 g_chg_next_event_date := g_pay_proc_evt_tab(l_pay).next_evt_start_date ;
3917 g_chg_actl_dt := g_pay_proc_evt_tab(l_pay).actual_date ;
3918 g_chg_pay_evt_index := l_pay ;
3919
3920 hr_utility.set_location(' pay chg index '||g_chg_pay_evt_index,99) ;
3921 hr_utility.set_location('date and end date '||g_person_id||'-'||g_chg_eff_dt||'/'||
3922 g_chg_next_event_date,99) ;
3923 g_chg_evt_source := 'PAY' ;
3924 ben_ext_util.get_ext_dates
3925 (p_ext_dfn_id => p_ext_dfn_id,
3926 p_data_typ_cd => p_data_typ_cd,
3927 p_effective_date => g_effective_date,
3928 p_person_ext_dt => l_person_ext_dt, --out
3929 p_benefits_ext_dt => l_benefits_ext_dt); -- out
3930 --
3931 g_person_ext_dt := l_person_ext_dt;
3932 g_benefits_ext_dt := l_benefits_ext_dt;
3933
3934 --determine the table name from the id
3935 if l_dated_table_id is not null then
3936 open c_pay_chg_tbl(l_dated_table_id) ;
3937 fetch c_pay_chg_tbl into g_chg_pay_table ;
3938 close c_pay_chg_tbl ;
3939 end if ;
3940
3941 l_include := 'Y';
3942 --
3943 if p_ext_crit_prfl_id is not null THEN
3944 --
3945 ben_ext_evaluate_inclusion.evaluate_change_log_incl
3946 (p_chg_evt_cd => g_chg_evt_cd,
3947 p_chg_evt_source => g_chg_evt_source,
3948 p_chg_eff_dt => trunc(g_chg_eff_dt),
3949 p_chg_actl_dt => trunc(g_chg_actl_dt) ,
3950 p_last_update_login => null ,
3951 p_effective_date => g_effective_date,
3952 p_include => l_include);
3953 --
3954 end if; -- p_ext_crit_prfl_id is not null
3955 --
3956 hr_utility.set_location( ' Inclusion flag ' || l_include , 99 ) ;
3957 hr_utility.set_location( ' actual ' || g_chg_actl_dt , 99 ) ;
3958 hr_utility.set_location( ' efective ' || g_chg_eff_dt , 99 ) ;
3959
3960 if l_include = 'Y' then
3961 --
3962
3963 Extract_person_info(p_person_id => p_person_id,
3964 p_effective_date => p_effective_date, -- passed in from conc mgr
3965 p_business_group_id => p_business_group_id,
3966 p_ext_rslt_id => p_ext_rslt_id
3967 ) ;
3968 --
3969 if p_ext_crit_prfl_id is not null THEN
3970 --
3971 ben_ext_evaluate_inclusion.Evaluate_Person_Incl
3972 (p_person_id => p_person_id,
3973 p_postal_code => g_prim_postal_code,
3974 p_org_id => g_employee_organization_id,
3975 p_loc_id => g_location_id,
3976 p_gre => null, -- this will be fetched in called program.
3977 p_state => g_prim_state,
3978 p_bnft_grp => g_benefit_group_id,
3979 p_ee_status => g_employee_status_id,
3980 p_chg_evt_cd => g_chg_evt_cd,
3981 p_chg_evt_source => g_chg_evt_source,
3982 p_effective_date => g_person_ext_dt,
3983 --RCHASE
3984 p_eff_date => trunc(g_chg_eff_dt),
3985 --End RCHASE
3986 p_actl_date => trunc(g_chg_actl_dt),
3987 p_include => l_include);
3988 --
3989 end if; -- p_ext_crit_prfl_id is not null
3990 --
3991 end if; -- l_include = 'Y'
3992 --
3993 if l_include = 'Y' THEN
3994 if g_debug then
3995 hr_utility.set_location(' Change Event Code ' || g_chg_evt_cd , 99 );
3996 end if;
3997 g_rcd_seq := 1;
3998 --
3999 if nvl(ben_extract.g_spcl_hndl_flag,'X') <> 'Y' then -- normal processing
4000 --
4001 process_ext_levels(
4002 p_person_id => p_person_id,
4003 p_ext_rslt_id => p_ext_rslt_id,
4004 p_ext_file_id => p_ext_file_id,
4005 p_data_typ_cd => p_data_typ_cd,
4006 p_ext_typ_cd => p_ext_typ_cd,
4007 p_business_group_id => p_business_group_id,
4008 p_effective_date => g_effective_date
4009 );
4010 else -- special handling flag tells us that it is an ansi 834 extract.
4011 --
4012 ben_ext_ansi.main(
4013 p_person_id => p_person_id,
4014 p_ext_rslt_id => p_ext_rslt_id,
4015 p_ext_file_id => p_ext_file_id,
4016 p_data_typ_cd => p_data_typ_cd,
4017 p_ext_typ_cd => p_ext_typ_cd,
4018 p_ext_crit_prfl_id => p_ext_crit_prfl_id,
4019 p_business_group_id => p_business_group_id,
4020 p_effective_date => g_benefits_ext_dt
4021 );
4022 end if;
4023 --
4024 g_trans_num := g_trans_num + 1;
4025 --
4026 END IF; -- l_include = 'Y'
4027
4028 END LOOP; -- collection loop
4029 End if ; -- count total collection return
4030 -- clear the table for next person
4031 l_pay_detail_tot_tab.delete ;
4032 g_pay_evt_group_tab.delete ;
4033
4034 end if ; --- for pay eventi process
4035
4036 -- ==========================================
4037 -- Communication Extract
4038 -- ==========================================
4039 --
4040 ELSIF p_data_typ_cd = 'CM' THEN
4041 --
4042 g_cm_flag := 'Y';
4043 --
4044 g_trans_num := 1;
4045 --
4046 ben_ext_util.get_cm_dates
4047 (p_ext_dfn_id => ben_extract.g_ext_dfn_id, --in
4048 p_effective_date => g_effective_date, --in
4049 p_to_be_sent_strt_dt => l_to_be_sent_strt_dt, --out
4050 p_to_be_sent_end_dt => l_to_be_sent_end_dt); --out
4051
4052 --- Communication cursor changed to three cursors and a bulk collect
4053 --- there is a remote possibility this may fetch lesser row due to
4054 --- changes in external joints , 1 communication can have more trigger if it is manual
4055 --- since we generate 1 communication on extract row, we do not need to worry
4056
4057 --
4058 open c_communication_extract (l_to_be_sent_strt_dt,
4059 l_to_be_sent_end_dt);
4060 fetch c_communication_extract bulk collect into
4061 l_per_cm_id_va ,
4062 l_per_in_ler_id_va ,
4063 l_prtt_enrt_actn_id_va ,
4064 l_effective_start_date_va ,
4065 l_per_cm_eff_start_date_va ,
4066 l_to_be_sent_dt_va ,
4067 l_sent_dt_va ,
4068 l_per_cm_last_update_date_va ,
4069 l_last_update_date_va ,
4070 l_dlvry_instn_txt_va ,
4071 l_inspn_rqd_flag_va ,
4072 l_address_id_va ,
4073 l_per_cm_prvdd_id_va ,
4074 l_object_version_number_va ,
4075 l_cm_typ_id_va
4076 ;
4077
4078 close c_communication_extract ;
4079
4080 for i IN 1 .. l_per_cm_id_va.count
4081 --
4082 LOOP
4083 --
4084 init_detail_globals;
4085
4086 g_per_cm_id := l_per_cm_id_va(i) ;
4087 g_cm_per_in_ler_id := l_per_in_ler_id_va(i) ;
4088 g_cm_prtt_enrt_actn_id := l_prtt_enrt_actn_id_va(i) ;
4089 g_cm_eff_dt := nvl(l_effective_start_date_va(i),l_per_cm_eff_start_date_va(i) ) ;
4090 g_cm_to_be_sent_dt := l_to_be_sent_dt_va(i) ;
4091 g_cm_sent_dt := l_sent_dt_va(i) ;
4092 g_cm_last_update_date := l_per_cm_last_update_date_va(i) ;
4093 g_cm_pvdd_last_update_date := l_last_update_date_va(i) ;
4094 g_cm_dlvry_instn_txt := l_dlvry_instn_txt_va(i) ;
4095 g_cm_inspn_rqd_flag := l_inspn_rqd_flag_va(i) ;
4096 g_cm_address_id := l_address_id_va(i) ;
4097 g_per_cm_prvdd_id := l_per_cm_prvdd_id_va(i) ;
4098 g_per_cm_object_version_number := l_object_version_number_va(i) ;
4099 g_cm_prvdd_eff_dt := l_effective_start_date_va(i) ;
4100 g_cm_type_id := l_cm_typ_id_va (i) ;
4101
4102
4103
4104 --- get the trigger date from person commu trigger
4105 l_cm_trgr_id := null ;
4106 open c_per_comm_trigger (g_per_cm_id , p_effective_date) ;
4107 fetch c_per_comm_trigger into g_cm_trgr_proc_dt, l_cm_trgr_id ;
4108 close c_per_comm_trigger ;
4109
4110 --- communication trigger setup information
4111
4112 if l_cm_trgr_id is not null then
4113 open c_comm_trgr (l_cm_trgr_id) ;
4114 fetch c_comm_trgr into g_cm_trgr_proc_name ;
4115 close c_comm_trgr ;
4116 end if ;
4117
4118 --- communication type information
4119 open c_comm_typ (l_cm_typ_id_va(i) , g_cm_eff_dt) ;
4120 fetch c_comm_typ into
4121 g_cm_type ,
4122 g_cm_short_name ,
4123 g_cm_kit
4124 ;
4125 close c_comm_typ ;
4126
4127 --- life event information
4128
4129 if l_per_in_ler_id_va(i) is not null then
4130 open c_pil (l_per_in_ler_id_va(i) , g_cm_eff_dt ) ;
4131 fetch c_pil into g_cm_lf_evt_id
4132 ,g_cm_lf_evt
4133 ,g_cm_lf_evt_stat
4134 ,g_cm_lf_evt_ocrd_dt
4135 ,g_cm_lf_evt_ntfn_dt
4136 ;
4137 close c_pil ;
4138
4139 end if ;
4140 --- vkodedal 9744958 If the comm is not tied to any LE, set the lf_evt and ntfd_dt
4141 if g_cm_lf_evt_ocrd_dt is null and l_per_cm_eff_start_date_va(i) is not null then
4142 g_cm_lf_evt_ocrd_dt := l_per_cm_eff_start_date_va(i) ;
4143 end if ;
4144
4145 if g_cm_lf_evt_ntfn_dt is null and l_per_cm_eff_start_date_va(i) is not null then
4146 g_cm_lf_evt_ntfn_dt := l_per_cm_eff_start_date_va(i) ;
4147 end if ;
4148
4149
4150 g_detail_extracted:=false;
4151 --
4152 --g_extract_date := g_cm_eff_dt;
4153 --
4154 ben_ext_util.get_ext_dates
4155 (p_ext_dfn_id => p_ext_dfn_id,
4156 p_data_typ_cd => p_data_typ_cd,
4157 p_effective_date => g_effective_date,
4158 p_person_ext_dt => l_person_ext_dt, --out
4159 p_benefits_ext_dt => l_benefits_ext_dt); -- out
4160 --
4161 g_person_ext_dt := l_person_ext_dt;
4162 g_benefits_ext_dt := l_benefits_ext_dt;
4163 --
4164 l_include := 'Y';
4165 --
4166 if p_ext_crit_prfl_id is not null THEN
4167 --
4168 ben_ext_evaluate_inclusion.evaluate_comm_incl
4169 (p_cm_typ_id => g_cm_type_id,
4170 p_last_update_date => g_cm_last_update_date,
4171 p_pvdd_last_update_date => g_cm_pvdd_last_update_date,
4172 p_sent_dt => g_cm_sent_dt,
4173 p_to_be_sent_dt => g_cm_to_be_sent_dt,
4174 p_effective_date => g_effective_date,
4175 p_include => l_include);
4176 --
4177 end if; -- p_ext_crit_prfl_id is not null
4178 --
4179 if l_include = 'Y' then
4180 --
4181 Extract_person_info(p_person_id => p_person_id,
4182 p_effective_date => p_effective_date, -- passed in from conc mgr
4183 p_business_group_id => p_business_group_id ,
4184 p_ext_rslt_id => p_ext_rslt_id
4185 ) ;
4186 --
4187 --
4188 if p_ext_crit_prfl_id is not null THEN
4189 --
4190 ben_ext_evaluate_inclusion.Evaluate_Person_Incl
4191 (p_person_id => p_person_id,
4192 p_postal_code => g_prim_postal_code,
4193 p_org_id => g_employee_organization_id,
4194 p_loc_id => g_location_id,
4195 p_gre => null, -- this will be fetched in called program.
4196 p_state => g_prim_state,
4197 p_bnft_grp => g_benefit_group_id,
4198 p_ee_status => g_employee_status_id,
4199 p_chg_evt_cd => null,
4200 p_effective_date => g_person_ext_dt,
4201 p_actl_date => null,
4202 p_include => l_include);
4203 --
4204 end if; -- p_ext_crit_prfl_id is not null
4205 --
4206 end if; -- l_include = 'Y'
4207 --
4208 IF l_include = 'Y' THEN
4209 --
4210 g_rcd_seq := 1;
4211 --
4212 if nvl(ben_extract.g_spcl_hndl_flag,'X') <> 'Y' then -- normal processing
4213 --
4214 process_ext_levels(
4215 p_person_id => p_person_id,
4216 p_ext_rslt_id => p_ext_rslt_id,
4217 p_ext_file_id => p_ext_file_id,
4218 p_data_typ_cd => p_data_typ_cd,
4219 p_ext_typ_cd => p_ext_typ_cd,
4220 p_business_group_id => p_business_group_id,
4221 p_effective_date => g_effective_date
4222 );
4223 else -- special handling flag tells us that it is an ansi 834 extract.
4224 --
4225 ben_ext_ansi.main(
4226 p_person_id => p_person_id,
4227 p_ext_rslt_id => p_ext_rslt_id,
4228 p_ext_file_id => p_ext_file_id,
4229 p_data_typ_cd => p_data_typ_cd,
4230 p_ext_typ_cd => p_ext_typ_cd,
4231 p_ext_crit_prfl_id => p_ext_crit_prfl_id,
4232 p_business_group_id => p_business_group_id,
4233 p_effective_date => g_benefits_ext_dt
4234 );
4235 end if;
4236 --
4237 g_trans_num := g_trans_num + 1;
4238 --
4239 END IF; -- l_include = 'Y'
4240 --
4241 -- updating ben_per_cm_prvdd_f.sent_dt
4242 --
4243 if (ben_ext_person.g_cm_flag = 'Y' and
4244 ben_ext_person.g_upd_cm_sent_dt_flag = 'Y' and
4245 ben_ext_person.g_per_cm_prvdd_id is not null and
4246 g_detail_extracted) then
4247 if nvl(l_last_per_cm_prvdd_id,-1) <> ben_ext_person.g_per_cm_prvdd_id then
4248 ben_PER_CM_PRVDD_api.update_PER_CM_PRVDD
4249 (p_validate => null,
4250 p_per_cm_prvdd_id => ben_ext_person.g_per_cm_prvdd_id,
4251 p_effective_start_date=> l_dummy_start_date,
4252 p_effective_end_date => l_dummy_end_date,
4253 p_sent_dt => trunc(sysdate),
4254 p_object_version_number=>ben_ext_person.g_per_cm_object_version_number,
4255 p_effective_date => ben_ext_person.g_cm_prvdd_eff_dt,
4256 p_datetrack_mode => 'CORRECTION');
4257 l_last_per_cm_prvdd_id:=ben_ext_person.g_per_cm_prvdd_id;
4258 end if;
4259 end if;
4260
4261 END LOOP;
4262
4263 --fixed bug 7323551--invalid cursor
4264 -- close c_communication_extract;
4265 -- ==================================
4266 -- Comp work bench CWB
4267 -- ================================
4268 ELSIF p_data_typ_cd = 'CW' THEN
4269
4270 g_trans_num := 1;
4271 --
4272 init_detail_globals;
4273 --
4274 for l_cwb in c_cwb_extract
4275 Loop
4276 g_CWB_EFFECTIVE_DATE := l_cwb.effective_date ;
4277 g_CWB_LE_DT := l_cwb.LF_EVT_OCRD_DT ;
4278 hr_utility.set_location('cwb person ' || l_cwb.person_id , 99 ) ;
4279 ben_ext_util.get_ext_dates
4280 (p_ext_dfn_id => p_ext_dfn_id,
4281 p_data_typ_cd => p_data_typ_cd,
4282 p_effective_date => g_effective_date,
4283 p_person_ext_dt => l_person_ext_dt, --out
4284 p_benefits_ext_dt => l_benefits_ext_dt); -- out
4285 --
4286 g_person_ext_dt := l_person_ext_dt;
4287 g_benefits_ext_dt := l_benefits_ext_dt;
4288 --
4289 l_include := 'Y';
4290 --
4291 if p_ext_crit_prfl_id is not null THEN
4292 --
4293 ben_ext_evaluate_inclusion.evaluate_cwb_incl
4294 (p_group_pl_id => l_cwb.group_pl_id ,
4295 p_lf_evt_ocrd_dt => g_CWB_LE_DT ,
4296 p_include => l_include ,
4297 p_effective_date => p_effective_date )
4298 ;
4299 --
4300 end if; -- p_ext_crit_prfl_id is not null
4301 --
4302 if l_include = 'Y' then
4303 -- change the busines group of person
4304 g_business_group_id := l_cwb.business_group_id ;
4305
4306 Extract_person_info(p_person_id => p_person_id,
4307 p_effective_date => p_effective_date, -- passed in from conc mgr
4308 p_business_group_id => l_cwb.business_group_id,
4309 p_ext_rslt_id => p_ext_rslt_id
4310 ) ;
4311 --
4312 --
4313 --
4314 if p_ext_crit_prfl_id is not null THEN
4315 --
4316 ben_ext_evaluate_inclusion.Evaluate_Person_Incl
4317 (p_person_id => p_person_id,
4318 p_postal_code => g_prim_postal_code,
4319 p_org_id => g_employee_organization_id,
4320 p_loc_id => g_location_id,
4321 p_gre => null, -- this will be fetched in called program.
4322 p_state => g_prim_state,
4323 p_bnft_grp => g_benefit_group_id,
4324 p_ee_status => g_employee_status_id,
4325 p_chg_evt_cd => null,
4326 p_effective_date => g_person_ext_dt,
4327 p_actl_date => null,
4328 p_include => l_include);
4329 --
4330 end if; -- p_ext_crit_prfl_id is not null
4331 end if ;
4332
4333 if l_include = 'Y' then
4334
4335 ---- Assign CWB Variables
4336 g_cwb_per_group_per_in_ler_id := l_cwb.group_per_in_ler_id ;
4337 g_cwb_per_group_pl_id := l_cwb.group_pl_id ;
4338 g_CWB_Person_FULL_NAME := l_cwb.FULL_NAME ;
4339 g_CWB_Person_Custom_Name := l_cwb.Custom_Name;
4340 g_CWB_Person_Brief_Name := l_cwb.Brief_Name;
4341 g_CWB_Life_Event_Name := l_cwb.Ler_name;
4342 g_CWB_Life_Event_Occurred_Date := l_cwb.LF_EVT_OCRD_DT;
4343 g_CWB_Person_EMAIL_DDRESS := l_cwb.EMAIL_ADDRESS;
4344 g_CWB_Person_EMPLOYEE_NUMBER := l_cwb.EMPLOYEE_NUMBER;
4345 g_CWB_Person_BASE_SALARY := l_cwb.BASE_SALARY;
4346 g_CWB_Person_CHANGE_REASON := l_cwb.CHANGE_REASON;
4347 g_CWB_PEOPLE_GROUP_NAME := l_cwb.PEOPLE_GROUP_name;
4348 g_CWB_PEOPLE_GROUP_SEGMENT1 := l_cwb.PEOPLE_GROUP_SEGMENT1;
4349 g_CWB_PEOPLE_GROUP_SEGMENT10 := l_cwb.PEOPLE_GROUP_SEGMENT10;
4350 g_CWB_PEOPLE_GROUP_SEGMENT11 := l_cwb.PEOPLE_GROUP_SEGMENT11;
4351 g_CWB_PEOPLE_GROUP_SEGMENT2 := l_cwb.PEOPLE_GROUP_SEGMENT2;
4352 g_CWB_PEOPLE_GROUP_SEGMENT3 := l_cwb.PEOPLE_GROUP_SEGMENT3;
4353 g_CWB_PEOPLE_GROUP_SEGMENT4 := l_cwb.PEOPLE_GROUP_SEGMENT4;
4354 g_CWB_PEOPLE_GROUP_SEGMENT5 := l_cwb.PEOPLE_GROUP_SEGMENT5;
4355 g_CWB_PEOPLE_GROUP_SEGMENT6 := l_cwb.PEOPLE_GROUP_SEGMENT6;
4356 g_CWB_PEOPLE_GROUP_SEGMENT7 := l_cwb.PEOPLE_GROUP_SEGMENT7;
4357 g_CWB_PEOPLE_GROUP_SEGMENT8 := l_cwb.PEOPLE_GROUP_SEGMENT8;
4358 g_CWB_PEOPLE_GROUP_SEGMENT9 := l_cwb.PEOPLE_GROUP_SEGMENT9;
4359 g_CWB_Person_BASE_SALARY_FREQ := l_cwb.BASE_SALARY_FREQUENCY;
4360 g_CWB_Person_POST_PROCESS_Stat := l_cwb.POST_PROCESS_Stat_cd;
4361 g_CWB_Person_START_DATE := l_cwb.START_DATE;
4362 g_CWB_Person_ADJUSTED_SVC_DATE := l_cwb.ADJUSTED_SVC_DATE;
4363 g_CWB_Person_Assg_ATTRIBUTE1 := l_cwb.Ass_ATTRIBUTE1;
4364 g_CWB_Person_Assg_ATTRIBUTE2 := l_cwb.Ass_ATTRIBUTE2;
4365 g_CWB_Person_Assg_ATTRIBUTE3 := l_cwb.Ass_ATTRIBUTE3;
4366 g_CWB_Person_Assg_ATTRIBUTE4 := l_cwb.Ass_ATTRIBUTE4;
4367 g_CWB_Person_Assg_ATTRIBUTE5 := l_cwb.Ass_ATTRIBUTE5;
4368 g_CWB_Person_Assg_ATTRIBUTE6 := l_cwb.Ass_ATTRIBUTE6;
4369 g_CWB_Person_Assg_ATTRIBUTE7 := l_cwb.Ass_ATTRIBUTE7;
4370 g_CWB_Person_Assg_ATTRIBUTE8 := l_cwb.Ass_ATTRIBUTE8;
4371 g_CWB_Person_Assg_ATTRIBUTE9 := l_cwb.Ass_ATTRIBUTE9;
4372 g_CWB_Person_Assg_ATTRIBUTE10 := l_cwb.Ass_ATTRIBUTE10;
4373 g_CWB_Person_Assg_ATTRIBUTE11 := l_cwb.Ass_ATTRIBUTE11;
4374 g_CWB_Person_Assg_ATTRIBUTE12 := l_cwb.Ass_ATTRIBUTE12;
4375 g_CWB_Person_Assg_ATTRIBUTE13 := l_cwb.Ass_ATTRIBUTE13;
4376 g_CWB_Person_Assg_ATTRIBUTE14 := l_cwb.Ass_ATTRIBUTE14;
4377 g_CWB_Person_Assg_ATTRIBUTE15 := l_cwb.Ass_ATTRIBUTE15;
4378 g_CWB_Person_Assg_ATTRIBUTE16 := l_cwb.Ass_ATTRIBUTE16;
4379 g_CWB_Person_Assg_ATTRIBUTE17 := l_cwb.Ass_ATTRIBUTE17;
4380 g_CWB_Person_Assg_ATTRIBUTE18 := l_cwb.Ass_ATTRIBUTE18;
4381 g_CWB_Person_Assg_ATTRIBUTE19 := l_cwb.Ass_ATTRIBUTE19;
4382 g_CWB_Person_Assg_ATTRIBUTE20 := l_cwb.Ass_ATTRIBUTE20;
4383 g_CWB_Person_Assg_ATTRIBUTE21 := l_cwb.Ass_ATTRIBUTE21;
4384 g_CWB_Person_Assg_ATTRIBUTE22 := l_cwb.Ass_ATTRIBUTE22;
4385 g_CWB_Person_Assg_ATTRIBUTE23 := l_cwb.Ass_ATTRIBUTE23;
4386 g_CWB_Person_Assg_ATTRIBUTE24 := l_cwb.Ass_ATTRIBUTE24;
4387 g_CWB_Person_Assg_ATTRIBUTE25 := l_cwb.Ass_ATTRIBUTE25;
4388 g_CWB_Person_Assg_ATTRIBUTE26 := l_cwb.Ass_ATTRIBUTE26;
4389 g_CWB_Person_Assg_ATTRIBUTE27 := l_cwb.Ass_ATTRIBUTE27;
4390 g_CWB_Person_Assg_ATTRIBUTE28 := l_cwb.Ass_ATTRIBUTE28;
4391 g_CWB_Person_Assg_ATTRIBUTE29 := l_cwb.Ass_ATTRIBUTE29;
4392 g_CWB_Person_Assg_ATTRIBUTE30 := l_cwb.Ass_ATTRIBUTE30;
4393 g_CWB_Person_Info_ATTRIBUTE1 := l_cwb.CPI_ATTRIBUTE1;
4394 g_CWB_Person_Info_ATTRIBUTE2 := l_cwb.CPI_ATTRIBUTE2;
4395 g_CWB_Person_Info_ATTRIBUTE3 := l_cwb.CPI_ATTRIBUTE3;
4396 g_CWB_Person_Info_ATTRIBUTE4 := l_cwb.CPI_ATTRIBUTE4;
4397 g_CWB_Person_Info_ATTRIBUTE5 := l_cwb.CPI_ATTRIBUTE5;
4398 g_CWB_Person_Info_ATTRIBUTE6 := l_cwb.CPI_ATTRIBUTE6;
4399 g_CWB_Person_Info_ATTRIBUTE7 := l_cwb.CPI_ATTRIBUTE7;
4400 g_CWB_Person_Info_ATTRIBUTE8 := l_cwb.CPI_ATTRIBUTE8;
4401 g_CWB_Person_Info_ATTRIBUTE9 := l_cwb.CPI_ATTRIBUTE9;
4402 g_CWB_Person_Info_ATTRIBUTE10 := l_cwb.CPI_ATTRIBUTE10;
4403 g_CWB_Person_Info_ATTRIBUTE11 := l_cwb.CPI_ATTRIBUTE11;
4404 g_CWB_Person_Info_ATTRIBUTE12 := l_cwb.CPI_ATTRIBUTE12;
4405 g_CWB_Person_Info_ATTRIBUTE13 := l_cwb.CPI_ATTRIBUTE13;
4406 g_CWB_Person_Info_ATTRIBUTE14 := l_cwb.CPI_ATTRIBUTE14;
4407 g_CWB_Person_Info_ATTRIBUTE15 := l_cwb.CPI_ATTRIBUTE15;
4408 g_CWB_Person_Info_ATTRIBUTE16 := l_cwb.CPI_ATTRIBUTE16;
4409 g_CWB_Person_Info_ATTRIBUTE17 := l_cwb.CPI_ATTRIBUTE17;
4410 g_CWB_Person_Info_ATTRIBUTE18 := l_cwb.CPI_ATTRIBUTE18;
4411 g_CWB_Person_Info_ATTRIBUTE19 := l_cwb.CPI_ATTRIBUTE19;
4412 g_CWB_Person_Info_ATTRIBUTE20 := l_cwb.CPI_ATTRIBUTE20;
4413 g_CWB_Person_Info_ATTRIBUTE21 := l_cwb.CPI_ATTRIBUTE21;
4414 g_CWB_Person_Info_ATTRIBUTE22 := l_cwb.CPI_ATTRIBUTE22;
4415 g_CWB_Person_Info_ATTRIBUTE23 := l_cwb.CPI_ATTRIBUTE23;
4416 g_CWB_Person_Info_ATTRIBUTE24 := l_cwb.CPI_ATTRIBUTE24;
4417 g_CWB_Person_Info_ATTRIBUTE25 := l_cwb.CPI_ATTRIBUTE25;
4418 g_CWB_Person_Info_ATTRIBUTE26 := l_cwb.CPI_ATTRIBUTE26;
4419 g_CWB_Person_Info_ATTRIBUTE27 := l_cwb.CPI_ATTRIBUTE27;
4420 g_CWB_Person_Info_ATTRIBUTE28 := l_cwb.CPI_ATTRIBUTE28;
4421 g_CWB_Person_Info_ATTRIBUTE29 := l_cwb.CPI_ATTRIBUTE29;
4422 g_CWB_Person_Info_ATTRIBUTE30 := l_cwb.CPI_ATTRIBUTE30;
4423 g_CWB_Person_CUSTOM_SEGMENT1 := l_cwb.CUSTOM_SEGMENT1;
4424 g_CWB_Person_CUSTOM_SEGMENT2 := l_cwb.CUSTOM_SEGMENT2;
4425 g_CWB_Person_CUSTOM_SEGMENT3 := l_cwb.CUSTOM_SEGMENT3;
4426 g_CWB_Person_CUSTOM_SEGMENT4 := l_cwb.CUSTOM_SEGMENT4;
4427 g_CWB_Person_CUSTOM_SEGMENT5 := l_cwb.CUSTOM_SEGMENT5;
4428 g_CWB_Person_CUSTOM_SEGMENT6 := l_cwb.CUSTOM_SEGMENT6;
4429 g_CWB_Person_CUSTOM_SEGMENT7 := l_cwb.CUSTOM_SEGMENT7;
4430 g_CWB_Person_CUSTOM_SEGMENT8 := l_cwb.CUSTOM_SEGMENT8;
4431 g_CWB_Person_CUSTOM_SEGMENT9 := l_cwb.CUSTOM_SEGMENT9;
4432 g_CWB_Person_CUSTOM_SEGMENT10 := l_cwb.CUSTOM_SEGMENT10;
4433 g_CWB_Person_CUSTOM_SEGMENT11 := l_cwb.CUSTOM_SEGMENT11;
4434 g_CWB_Person_CUSTOM_SEGMENT13 := l_cwb.CUSTOM_SEGMENT12;
4435 g_CWB_Person_CUSTOM_SEGMENT14 := l_cwb.CUSTOM_SEGMENT13;
4436 g_CWB_Person_CUSTOM_SEGMENT12 := l_cwb.CUSTOM_SEGMENT14;
4437 g_CWB_Person_CUSTOM_SEGMENT15 := l_cwb.CUSTOM_SEGMENT15;
4438 g_CWB_Person_FEEDBACK_RATING := l_cwb.FEEDBACK_RATING;
4439 g_CWB_Person_FREQUENCY := l_cwb.FREQUENCY;
4440 g_CWB_Person_Grade_MAX_VAL := l_cwb.GRD_MAX_VAL;
4441 g_CWB_Person_Grade_MID_POINT := l_cwb.GRD_MID_POINT;
4442 g_CWB_Person_Grade_MIN_VAL := l_cwb.GRD_MIN_VAL;
4443 g_CWB_Person_GRADE_ANN_FACTOR := l_cwb.GRADE_ANNULIZATION_FACTOR;
4444 g_CWB_Person_Grade_COMPARATIO := l_cwb.Grd_COMPARATIO;
4445 g_CWB_Person_LEGISLATION := l_cwb.LEGISLATION_CODE;
4446 g_CWB_Person_NORMAL_HOURS := l_cwb.NORMAL_HOURS;
4447 g_CWB_Person_ORIG_START_DATE := l_cwb.ORIGINAL_START_DATE;
4448 g_CWB_Person_PAY_ANNUL_FACTOR := l_cwb.PAY_ANNULIZATION_FACTOR;
4449 g_CWB_Person_SUP_BRIEF_NAME := l_cwb.SUPERVISOR_BRIEF_NAME;
4450 g_CWB_Person_SUP_CUSTOM_NAME := l_cwb.SUPERVISOR_CUSTOM_NAME;
4451 g_CWB_Person_SUP_FULL_NAME := l_cwb.SUPERVISOR_FULL_NAME;
4452 g_CWB_Person_YEARS_EMPLOYED := l_cwb.YEARS_EMPLOYED;
4453 g_CWB_Person_YEARS_IN_GRADE := l_cwb.YEARS_IN_GRADE;
4454 g_CWB_Person_YEARS_IN_POS := l_cwb.YEARS_IN_POSITION;
4455 g_CWB_Person_YEARS_IN_JOB := l_cwb.YEARS_IN_JOB;
4456 g_CWB_Person_PAYROLL_NAME := l_cwb.payroll_name ;
4457
4458 --- business group name
4459 open c_bg_name(l_cwb.business_group_id ) ;
4460 fetch c_bg_name into g_CWB_Person_BG_Name ;
4461 close c_bg_name ;
4462
4463 open c_org_name(l_cwb.organization_id) ;
4464 fetch c_org_name into g_CWB_Person_ORG_name ;
4465 close c_org_name ;
4466
4467 open c_job(l_Cwb.job_id) ;
4468 fetch c_job into g_CWB_Person_JOB_name ;
4469 close c_job ;
4470
4471 open c_loc(l_Cwb.location_id) ;
4472 fetch c_loc into g_CWB_Person_location ;
4473 close c_loc ;
4474
4475 open c_pos(l_Cwb.position_id) ;
4476 fetch c_pos into g_CWB_Person_POSITION ;
4477 close c_pos ;
4478
4479 open c_grade(l_Cwb.grade_id) ;
4480 fetch c_grade into g_CWB_Person_GRADE_name ;
4481 close c_grade ;
4482
4483 open c_payr(l_Cwb.pay_rate_id) ;
4484 fetch c_payr into g_CWB_Person_PAY_RATE ;
4485 close c_payr ;
4486
4487 open c_asg_status(l_cwb.ASSIGNMENT_STATUS_TYPE_ID) ;
4488 fetch c_asg_status into g_CWB_Person_STATUS_TYPE ;
4489 close c_asg_status ;
4490
4491 open c_hr_lkup('EMP_CAT', l_cwb.EMP_CATEGORY) ;
4492 fetch c_hr_lkup into g_CWB_Person_EMPloyee_CATEGORY ;
4493 close c_hr_lkup ;
4494
4495 open c_hr_lkup('BEN_CWB_QUAR_IN_GRD', l_cwb.GRD_QUARTILE) ;
4496 fetch c_hr_lkup into g_CWB_Person_Grade_QUARTILE ;
4497 close c_hr_lkup ;
4498
4499 open c_hr_lkup('BEN_PER_IN_LER_STAT', l_cwb.PER_IN_LER_STAT_CD) ;
4500 fetch c_hr_lkup into g_CWB_Life_Event_status ;
4501 close c_hr_lkup ;
4502
4503 open c_pln (g_cwb_per_group_pl_id , g_CWB_Life_Event_Occurred_Date );
4504 fetch c_pln into g_cwb_group_plan_name ;
4505 close c_pln ;
4506
4507 --- from transaction table
4508 -- performance rating
4509 open c_tran( l_cwb.ASSIGNMENT_ID,
4510 'CWBPERF'||to_char(l_cwb.PERF_REVW_STRT_DT ,'RRRR/MM/DD')||nvl(l_cwb.EMP_INTERVIEW_TYP_CD,'')
4511 ) ;
4512 fetch c_tran into l_tran ;
4513 close c_tran ;
4514 if l_tran.ATTRIBUTE3 is not null then
4515 open c_hr_lkup('PERFORMANCE_RATING', l_tran.ATTRIBUTE3) ;
4516 fetch c_hr_lkup into g_CWB_new_Perf_rating ;
4517 close c_hr_lkup ;
4518 end if ;
4519 g_CWB_Person_PERF_RATING_DATE := l_cwb.PERF_REVW_STRT_DT ;
4520 if l_cwb.EMP_INTERVIEW_TYP_CD is not null then
4521 open c_hr_lkup('EMP_INTERVIEW_TYPE', l_cwb.EMP_INTERVIEW_TYP_CD) ;
4522 fetch c_hr_lkup into g_CWB_Persom_PERF_RATING_TYPE ;
4523 close c_hr_lkup ;
4524 end if ;
4525
4526 l_tran := null ;
4527 open c_tran( l_cwb.ASSIGNMENT_ID,
4528 'CWBASG'||to_char(l_cwb.ASG_UPDT_EFF_DATE ,'RRRR/MM/DD'))
4529 ;
4530 fetch c_tran into l_tran ;
4531 close c_tran ;
4532
4533 if l_tran.ATTRIBUTE3 is not null then
4534 open c_hr_lkup('EMP_ASSIGN_REASON', l_tran.ATTRIBUTE3) ;
4535 fetch c_hr_lkup into g_cwb_nw_chg_reason ;
4536 close c_hr_lkup ;
4537 end if ;
4538
4539 if l_tran.ATTRIBUTE5 is not null then
4540 open c_job(l_tran.ATTRIBUTE5) ;
4541 fetch c_job into g_CWB_new_Job_name ;
4542 close c_job ;
4543 end if ;
4544
4545 if l_tran.ATTRIBUTE6 is not null then
4546 open c_pos(l_tran.ATTRIBUTE6) ;
4547 fetch c_pos into g_CWB_new_Postion_name ;
4548 close c_pos ;
4549 end if ;
4550
4551 if l_tran.ATTRIBUTE7 is not null then
4552 open c_grade(l_tran.ATTRIBUTE7) ;
4553 fetch c_grade into g_CWB_new_Grade_name ;
4554 close c_grade ;
4555 end if ;
4556
4557 if l_tran.ATTRIBUTE8 is not null then
4558 open c_groups(l_tran.ATTRIBUTE8) ;
4559 fetch c_groups into g_CWB_new_Group_name ;
4560 close c_groups ;
4561 end if ;
4562
4563
4564
4565 /*
4566 g_CWB_new_Group_name := null ;
4567 */
4568
4569 ----
4570 g_rcd_seq := 1;
4571 --
4572 if nvl(ben_extract.g_spcl_hndl_flag,'X') <> 'Y' then -- normal processing
4573 --
4574 process_ext_levels(
4575 p_person_id => p_person_id,
4576 p_ext_rslt_id => p_ext_rslt_id,
4577 p_ext_file_id => p_ext_file_id,
4578 p_data_typ_cd => p_data_typ_cd,
4579 p_ext_typ_cd => p_ext_typ_cd,
4580 p_business_group_id => p_business_group_id,
4581 p_effective_date => g_effective_date
4582 );
4583 else -- special handling flag tells us that it is an ansi 834 extract.
4584 --
4585 ben_ext_ansi.main(
4586 p_person_id => p_person_id,
4587 p_ext_rslt_id => p_ext_rslt_id,
4588 p_ext_file_id => p_ext_file_id,
4589 p_data_typ_cd => p_data_typ_cd,
4590 p_ext_typ_cd => p_ext_typ_cd,
4591 p_ext_crit_prfl_id => p_ext_crit_prfl_id,
4592 p_business_group_id => p_business_group_id,
4593 p_effective_date => g_benefits_ext_dt
4594 );
4595 end if;
4596 --
4597 g_trans_num := g_trans_num + 1;
4598 --
4599 END IF; -- l_include = 'Y'
4600
4601 end loop ;
4602
4603
4604
4605 END IF; -- extract type
4606
4607 if g_debug then
4608 hr_utility.set_location('Exiting'||l_proc, 15);
4609 end if;
4610 --
4611 EXCEPTION
4612 --
4613 WHEN detail_error THEN
4614 --
4615 ROLLBACK TO cur_transaction;
4616 l_err_message := ben_ext_fmt.get_error_msg(g_err_num,g_err_name,g_elmt_name ) ;
4617 if g_debug then
4618 hr_utility.set_location('err msg ' || l_err_message, 99.98 );
4619 end if;
4620 write_error(
4621 p_err_num => g_err_num,
4622 p_err_name => l_err_message,
4623 p_typ_cd => 'E',
4624 p_request_id => ben_extract.g_request_id,
4625 p_ext_rslt_id => p_ext_rslt_id
4626 );
4627
4628 When detail_restart_error then
4629
4630 ROLLBACK TO cur_transaction;
4631 l_err_message := ben_ext_fmt.get_error_msg(g_err_num,g_err_name,g_elmt_name ) ;
4632 if g_debug then
4633 hr_utility.set_location('err msg ' || l_err_message, 99.98 );
4634 end if;
4635 write_error(
4636 p_err_num => g_err_num,
4637 p_err_name => l_err_message,
4638 p_typ_cd => 'E',
4639 p_request_id => ben_extract.g_request_id,
4640 p_ext_rslt_id => p_ext_rslt_id
4641 );
4642 Raise ;
4643
4644 WHEN required_error THEN
4645 --
4646 ROLLBACK TO cur_transaction;
4647
4648 WHEN Others THEN --- any unexpted error
4649
4650 ROLLBACK TO cur_transaction;
4651 -- just error the person and go ahead with other person
4652 -- the log will be created in extract pkg , for only no data found log
4653 -- error
4654 if g_err_num = 94102 then
4655 l_err_message := ben_ext_fmt.get_error_msg(g_err_num,g_err_name) ;
4656 write_error(
4657 p_err_num => g_err_num,
4658 p_err_name => l_err_message,
4659 p_typ_cd => 'E',
4660 p_request_id => ben_extract.g_request_id,
4661 p_ext_rslt_id => p_ext_rslt_id
4662 );
4663 end if ;
4664
4665 Raise ; -- raise the exception to benxcrit
4666
4667
4668 --
4669 End process_ext_person;
4670 --
4671 -- ----------------------------------------------------------------------------
4672 -- |------< process_ext_levels >----------------------------------------------|
4673 -- ----------------------------------------------------------------------------
4674 -- This procedure will process extract levels and call ben_ext_fmt.process_ext_recs
4675 -- for each record level according to the extract definition.
4676 --
4677 -- For simplicity and due to the time constraint it is assummed that a given person
4678 -- can only be a participant or a dependent (not both) as well as the fact that a
4679 -- person can not be a dependent of more that one particiant for a particular plan.
4680 -- This will mater only when dependendents are processed as people.
4681 -- This restriction will be addressed in the future release.
4682 --
4683 Procedure process_ext_levels(
4684 p_person_id in number,
4685 p_ext_rslt_id in number,
4686 p_ext_file_id in number,
4687 p_data_typ_cd in varchar2,
4688 p_ext_typ_cd in varchar2,
4689 p_business_group_id in number,
4690 p_effective_date in date
4691 ) IS
4692 --
4693 l_proc varchar2(72);
4694 --
4695 l_dummy varchar2(30);
4696 l_rec_lvl_cd varchar2(30);
4697 l_cursor_cd varchar2(30);
4698 l_comp_incl varchar2(1) := 'Y';
4699 l_rollback boolean;
4700 --
4701 --
4702 cursor purged_rslt_c (l_pl_id number) is
4703 select
4704 pl.name pl_name,
4705 -- opt.opt_id opt_id,
4706 -- opt.name opt_name,
4707 -- enrt.enrt_cvg_strt_dt cvg_strt_dt,
4708 -- enrt.enrt_cvg_thru_dt cvg_thru_dt,
4709 -- enrt.bnft_amt bnft_amt,
4710 -- enrt.pgm_id pgm_id,
4711 -- pgm.name pgm_name,
4712 pl.pl_typ_id pl_typ_id,
4713 ptp.name pl_typ_name
4714 from ben_pl_f pl,
4715 -- ben_oipl_f oipl,
4716 -- ben_opt_f opt,
4717 -- ben_pgm_f pgm,
4718 ben_pl_typ_f ptp
4719 where
4720 pl.pl_id = l_pl_id
4721 and g_effective_date between pl.effective_start_date
4722 and pl.effective_end_date
4723 --
4724 and pl.pl_typ_id = ptp.pl_typ_id
4725 and g_effective_date between nvl(ptp.effective_start_date, g_effective_date)
4726 and nvl(ptp.effective_end_date, g_effective_date)
4727 ;
4728
4729 --
4730 begin
4731 --
4732 g_debug := hr_utility.debug_enabled;
4733 if g_debug then
4734 l_proc := g_package||'process_ext_levels';
4735 hr_utility.set_location('Entering'||l_proc, 5);
4736 end if;
4737 --
4738 -- Initialize rollback flag.
4739 --
4740 l_rollback:=FALSE;
4741
4742 if g_debug then
4743 hr_utility.set_location('ben_extract.g_per_lvl ' || ben_extract.g_per_lvl ,99 );
4744 end if;
4745 --
4746 IF ben_extract.g_per_lvl = 'Y' THEN
4747 --
4748 -- Process Personal Level Detail Records
4749 --
4750 --
4751 if g_debug then
4752 hr_utility.set_location(' ben_ext_fmt.process_ext_recs',99 );
4753 end if;
4754 ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
4755 p_ext_file_id => p_ext_file_id,
4756 p_data_typ_cd => p_data_typ_cd,
4757 p_ext_typ_cd => p_ext_typ_cd,
4758 p_rcd_typ_cd => 'D',
4759 p_low_lvl_cd => 'P',
4760 p_person_id => p_person_id,
4761 p_chg_evt_cd => g_chg_evt_cd,
4762 p_business_group_id => p_business_group_id,
4763 p_effective_date => g_effective_date
4764 );
4765
4766 --
4767 --
4768 END IF;
4769 --
4770 -- create enrollment, dependent and beneficiary level rows
4771 -- =======================================================
4772 --RCHASE
4773 --IF nvl(g_chg_evt_cd, '*') <> 'TBBC' then
4774 --
4775 -- extract enrollment levels
4776 --
4777 IF (ben_extract.g_enrt_lvl = 'Y' OR ben_extract.g_dpnt_lvl = 'Y' OR ben_extract.g_bnf_lvl = 'Y' OR
4778 ben_extract.g_actn_lvl = 'Y' or ben_extract.g_prem_lvl = 'Y' ) THEN
4779 --
4780 if g_debug then
4781 hr_utility.set_location(' ben_ext_enrt.main',99 );
4782 end if;
4783 ben_ext_enrt.main(
4784 p_person_id => p_person_id,
4785 p_ext_rslt_id => p_ext_rslt_id,
4786 p_ext_file_id => p_ext_file_id,
4787 p_data_typ_cd => p_data_typ_cd,
4788 p_ext_typ_cd => p_ext_typ_cd,
4789 p_chg_evt_cd => g_chg_evt_cd,
4790 p_business_group_id => p_business_group_id,
4791 p_effective_date => g_benefits_ext_dt);
4792 END IF;
4793 --
4794 --
4795 --RCHASE
4796 --ELSIF nvl(g_chg_evt_cd, '*') = 'TBBC' and ben_extract.g_enrt_lvl = 'Y' then
4797 --
4798 -- open purged_rslt_c(g_chg_pl_id);
4799 --
4800 -- fetch purged_rslt_c into
4801 -- g_enrt_pl_name,
4802 -- g_enrt_pl_typ_id,
4803 -- g_enrt_pl_typ_name;
4804 --
4805 -- ben_ext_fmt.process_ext_recs(p_ext_rslt_id => p_ext_rslt_id,
4806 -- p_ext_file_id => p_ext_file_id,
4807 -- p_data_typ_cd => p_data_typ_cd,
4808 -- p_ext_typ_cd => p_ext_typ_cd,
4809 -- p_rcd_typ_cd => 'D',
4810 -- p_low_lvl_cd => 'E',
4811 -- p_person_id => p_person_id,
4812 -- p_chg_evt_cd => g_chg_evt_cd,
4813 -- p_business_group_id => p_business_group_id,
4814 -- p_effective_date => g_effective_date
4815 -- );
4816 --
4817 --END IF; -- part type
4818 --
4819 -- create eligibility extract rows
4820 -- =========================================
4821 if ben_extract.g_elig_lvl = 'Y' or ben_extract.g_eligdpnt_lvl = 'Y' then
4822 --
4823 ben_ext_elig.main(
4824 p_person_id => p_person_id,
4825 p_ext_rslt_id => p_ext_rslt_id,
4826 p_ext_file_id => p_ext_file_id,
4827 p_data_typ_cd => p_data_typ_cd,
4828 p_ext_typ_cd => p_ext_typ_cd,
4829 p_chg_evt_cd => g_chg_evt_cd,
4830 p_business_group_id => p_business_group_id,
4831 p_effective_date => g_benefits_ext_dt
4832 );
4833 --
4834 --
4835 end if;
4836 --
4837 -- create flex credit extract rows
4838 -- =========================================
4839 if ben_extract.g_flex_lvl = 'Y' then
4840 --
4841 ben_ext_flcr.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_benefits_ext_dt
4850 );
4851 --
4852 --
4853 end if;
4854 --
4855 -- create payroll extract rows
4856 -- ================================
4857 if ben_extract.g_payroll_lvl = 'Y' then
4858 --
4859 ben_ext_payroll.main(
4860 p_person_id => p_person_id,
4861 p_ext_rslt_id => p_ext_rslt_id,
4862 p_ext_file_id => p_ext_file_id,
4863 p_data_typ_cd => p_data_typ_cd,
4864 p_ext_typ_cd => p_ext_typ_cd,
4865 p_chg_evt_cd => g_chg_evt_cd,
4866 p_business_group_id => p_business_group_id,
4867 p_effective_date => g_person_ext_dt
4868 );
4869 --
4870 end if;
4871 --
4872 -- create run result extract rows
4873 -- ================================
4874 if ben_extract.g_runrslt_lvl = 'Y' then
4875 --
4876 ben_ext_runrslt.main(
4877 p_person_id => p_person_id,
4878 p_ext_rslt_id => p_ext_rslt_id,
4879 p_ext_file_id => p_ext_file_id,
4880 p_data_typ_cd => p_data_typ_cd,
4881 p_ext_typ_cd => p_ext_typ_cd,
4882 p_chg_evt_cd => g_chg_evt_cd,
4883 p_business_group_id => p_business_group_id,
4884 p_effective_date => g_person_ext_dt
4885 );
4886 --
4887 end if;
4888 --
4889 -- create contact extract rows
4890 -- ================================
4891 if ben_extract.g_contact_lvl = 'Y' then
4892 --
4893 ben_ext_contact.main(
4894 p_person_id => p_person_id,
4895 p_ext_rslt_id => p_ext_rslt_id,
4896 p_ext_file_id => p_ext_file_id,
4897 p_data_typ_cd => p_data_typ_cd,
4898 p_ext_typ_cd => p_ext_typ_cd,
4899 p_chg_evt_cd => g_chg_evt_cd,
4900 p_business_group_id => p_business_group_id,
4901 p_effective_date => g_person_ext_dt
4902 );
4903 --
4904 end if;
4905
4906 --- cwb
4907 if p_data_typ_cd = 'CW' THEN
4908
4909 hr_utility.set_location( ' bdgt lvl ' || ben_extract.g_cwb_bdgt_lvl , 99 );
4910
4911 if ben_extract.g_cwb_bdgt_lvl = 'Y' then
4912 ben_ext_cwb.extract_person_groups
4913 ( p_person_id => p_person_id,
4914 p_per_in_ler_id => g_cwb_per_group_per_in_ler_id,
4915 p_ext_rslt_id => p_ext_rslt_id,
4916 p_ext_file_id => p_ext_file_id,
4917 p_data_typ_cd => p_data_typ_cd,
4918 p_ext_typ_cd => p_ext_typ_cd,
4919 p_business_group_id => p_business_group_id,
4920 p_effective_date => g_person_ext_dt) ;
4921 end if ;
4922
4923 if ben_extract.g_cwb_awrd_lvl = 'Y' then
4924 ben_ext_cwb.extract_person_rates
4925 ( p_person_id => p_person_id,
4926 p_per_in_ler_id => g_cwb_per_group_per_in_ler_id,
4927 p_ext_rslt_id => p_ext_rslt_id,
4928 p_ext_file_id => p_ext_file_id,
4929 p_data_typ_cd => p_data_typ_cd,
4930 p_ext_typ_cd => p_ext_typ_cd,
4931 p_business_group_id => p_business_group_id,
4932 p_effective_date => g_person_ext_dt) ;
4933 end if ;
4934 end if ;
4935
4936
4937 --
4938
4939
4940 if ben_extract.g_otl_summ_lvl = 'Y' then
4941
4942 hxc_ext_timecard.process_summary (
4943 p_person_id => p_person_id,
4944 p_ext_rslt_id => p_ext_rslt_id,
4945 p_ext_file_id => p_ext_file_id,
4946 p_ext_crit_prfl_id => NULL,
4947 p_data_typ_cd => p_data_typ_cd,
4948 p_ext_typ_cd => p_ext_typ_cd,
4949 p_effective_date => p_effective_date );
4950
4951 end if;
4952
4953 --
4954 /* this validation is done on low level , this is changed to do in record level
4955 this validation moved to benxfrmt.pkb
4956 FOR i in ben_extract.gtt_rcd_rqd_vals.first .. ben_extract.gtt_rcd_rqd_vals.last LOOP
4957 --
4958 IF NOT ben_extract.gtt_rcd_rqd_vals(i).rcd_found
4959 THEN
4960 l_rollback := TRUE; -- raise required_error;
4961 ELSIF ben_extract.gtt_rcd_rqd_vals(1).low_lvl_cd <> 'NOREQDRCD'
4962 THEN
4963 ben_extract.gtt_rcd_rqd_vals(i).rcd_found := FALSE; -- reset the value
4964 END IF;
4965 --
4966 END LOOP;
4967 */
4968
4969
4970 -- validate the mandatory for low level in sequenc
4971 FOR i in ben_extract.gtt_rcd_rqd_vals_seq.first .. ben_extract.gtt_rcd_rqd_vals_seq.last LOOP
4972 --
4973 If NOT ben_extract.gtt_rcd_rqd_vals_seq(i).rcd_found THEN
4974 hr_utility.set_location('Mandatory failed '||ben_extract.gtt_rcd_rqd_vals_seq(i).low_lvl_cd || ' '||
4975 ben_extract.gtt_rcd_rqd_vals_seq(i).seq_num , 15);
4976 l_rollback := TRUE; -- raise required_error;
4977 end if ;
4978 if ben_extract.gtt_rcd_rqd_vals_seq(1).low_lvl_cd <> 'NOREQDRCD' then
4979 ben_extract.gtt_rcd_rqd_vals_seq(i).rcd_found := FALSE; -- reset the value
4980 end if ;
4981 END LOOP;
4982 --
4983 IF l_rollback
4984 THEN
4985 RAISE required_error;
4986 END IF;
4987 --
4988 if g_debug then
4989 hr_utility.set_location('Exiting'||l_proc, 15);
4990 end if;
4991 --
4992 --
4993 End process_ext_levels;
4994 --
4995 --
4996 -- ----------------------------------------------------------------------------
4997 -- |------< init_detail_globals >---------------------------------------------|
4998 -- ----------------------------------------------------------------------------
4999 --
5000 Procedure init_detail_globals IS
5001 --
5002 l_proc varchar2(72);
5003 --
5004 --
5005 --
5006 begin
5007 --
5008 g_debug := hr_utility.debug_enabled;
5009 if g_debug then
5010 l_proc := g_package||'init_detail_globals';
5011 hr_utility.set_location('Entering'||l_proc, 5);
5012 end if;
5013 --
5014 --
5015 -- personal (25)
5016 --
5017 g_chg_evt_cd := null;
5018 g_chg_evt_source := null;
5019 g_chg_actl_dt := null;
5020 g_chg_eff_dt := null;
5021 g_chg_pl_id := null;
5022 g_chg_input_value_id := null;
5023 g_chg_old_val1 := null;
5024 g_chg_old_val2 := null;
5025 g_chg_old_val3 := null;
5026 g_chg_old_val4 := null;
5027 g_chg_old_val5 := null;
5028 g_chg_old_val6 := null;
5029 g_chg_new_val1 := null;
5030 g_chg_new_val2 := null;
5031 g_chg_new_val3 := null;
5032 g_chg_new_val4 := null;
5033 g_chg_new_val5 := null;
5034 g_chg_new_val6 := null;
5035 g_chg_enrt_rslt_id := null;
5036 g_chg_pl_id := null;
5037 g_chg_pay_table := null;
5038 g_chg_pay_column := null;
5039 g_chg_pay_mode := null;
5040 g_chg_update_type := null;
5041 g_chg_surrogate_key := null;
5042 g_chg_next_event_date := null;
5043 g_chg_pay_evt_index := null;
5044 --
5045 g_previous_last_name := null;
5046 g_previous_first_name := null;
5047 g_previous_middle_name := null;
5048 g_previous_suffix := null;
5049 g_previous_prefix := null;
5050 g_previous_ssn := null;
5051 g_previous_dob := null;
5052 g_previous_sex := null;
5053 --
5054 g_part_type := null;
5055 g_per_rlshp_type := null;
5056 g_part_ssn := null;
5057 --
5058 g_national_identifier := null;
5059 g_last_name := null;
5060 g_first_name := null;
5061 g_middle_names := null;
5062 g_full_name := null;
5063 g_suffix := null;
5064 g_prefix := null;
5065 g_title := null;
5066 g_sex := null;
5067 g_date_of_birth := null;
5068 g_data_verification_dt := null;
5069 g_marital_status := null;
5070 g_employee_category := null;
5071 g_registered_disabled_flag := null;
5072 g_student_status := null;
5073 g_date_of_death := null;
5074 g_employee_number := null;
5075 g_benefit_group_id := null;
5076 g_benefit_group := null;
5077 g_bng_flex_01 := null;
5078 g_bng_flex_02 := null;
5079 g_bng_flex_03 := null;
5080 g_bng_flex_04 := null;
5081 g_bng_flex_05 := null;
5082 g_bng_flex_06 := null;
5083 g_bng_flex_07 := null;
5084 g_bng_flex_08 := null;
5085 g_bng_flex_09 := null;
5086 g_bng_flex_10 := null;
5087 g_benefit_bal_vacation := null;
5088 g_benefit_bal_sickleave := null;
5089 g_benefit_bal_pension := null;
5090 g_benefit_bal_dfncntrbn := null;
5091 g_benefit_bal_wellness := null;
5092 g_per_attr_1 := null;
5093 g_per_attr_2 := null;
5094 g_per_attr_3 := null;
5095 g_per_attr_4 := null;
5096 g_per_attr_5 := null;
5097 g_per_attr_6 := null;
5098 g_per_attr_7 := null;
5099 g_per_attr_8 := null;
5100 g_per_attr_9 := null;
5101 g_per_attr_10 := null;
5102 --
5103 g_applicant_number := null;
5104 g_correspondence_language := null;
5105 g_email_address := null;
5106 g_known_as := null;
5107 g_mailstop := null;
5108 g_nationality := null;
5109 g_pre_name_adjunct := null;
5110 g_original_date_of_hire := null;
5111 g_uses_tobacco_flag := null;
5112 g_office_number := null;
5113 --
5114 g_prim_address_line_1 := null;
5115 g_prim_address_line_2 := null;
5116 g_prim_address_line_3 := null;
5117 g_prim_city := null;
5118 g_prim_state := null;
5119 g_prim_state_ansi := null;
5120 g_prim_postal_code := null;
5121 g_prim_country := null;
5122 g_prim_county := null;
5123 g_prim_region_3 := null;
5124 g_prim_address_date := null;
5125 g_prim_addr_service_area := null;
5126 --
5127 g_mail_address_line_1 := null;
5128 g_mail_address_line_2 := null;
5129 g_mail_address_line_3 := null;
5130 g_mail_city := null;
5131 g_mail_state := null;
5132 g_mail_state_ansi := null;
5133 g_mail_postal_code := null;
5134 g_mail_country := null;
5135 g_mail_county := null;
5136 g_mail_region_3 := null;
5137 g_mail_address_date := null;
5138 --
5139 g_phone_home := null;
5140 g_phone_work := null;
5141 g_phone_fax := null;
5142 g_phone_mobile := null;
5143 g_phone_pager := null;
5144 --
5145 g_last_hire_date := null;
5146 g_actual_term_date := null;
5147 g_adjusted_svc_date := null;
5148 g_term_reason := null;
5149 --
5150 g_employee_status := null;
5151 g_employee_grade := null;
5152 g_grd_flex_01 := null;
5153 g_grd_flex_02 := null;
5154 g_grd_flex_03 := null;
5155 g_grd_flex_04 := null;
5156 g_grd_flex_05 := null;
5157 g_grd_flex_06 := null;
5158 g_grd_flex_07 := null;
5159 g_grd_flex_08 := null;
5160 g_grd_flex_09 := null;
5161 g_grd_flex_10 := null;
5162 g_employee_barg_unit := null;
5163 g_employee_organization := null;
5164 g_employee_grade_id := null;
5165 g_employee_organization_id := null;
5166 g_employee_status_id := null;
5167 g_location_id := null;
5168 g_location_code := null;
5169 g_location_addr1 := null;
5170 g_location_addr2 := null;
5171 g_location_addr3 := null;
5172 g_location_city := null;
5173 g_location_country := null;
5174 g_location_zip := null;
5175 g_location_region1 := null;
5176 g_location_region2 := null;
5177 g_location_region3 := null;
5178 -- org address
5179 g_org_location_addr1 := null ;
5180 g_org_location_addr2 := null ;
5181 g_org_location_addr3 := null ;
5182 g_org_location_city := null ;
5183 g_org_location_country := null ;
5184 g_org_location_zip := null ;
5185 g_org_location_region1 := null ;
5186 g_org_location_region2 := null ;
5187 g_org_location_region3 := null ;
5188 --
5189 g_alc_flex_01 := null;
5190 g_alc_flex_02 := null;
5191 g_alc_flex_03 := null;
5192 g_alc_flex_04 := null;
5193 g_alc_flex_05 := null;
5194 g_alc_flex_06 := null;
5195 g_alc_flex_07 := null;
5196 g_alc_flex_08 := null;
5197 g_alc_flex_09 := null;
5198 g_alc_flex_10 := null;
5199 g_asg_title := null;
5200 g_position_id := null;
5201 g_job_id := null;
5202 g_payroll_id := null;
5203 g_people_group_id := null;
5204 g_pay_basis_id := null;
5205 g_hourly_salaried_code := null;
5206 g_labour_union_member_flag := null;
5207 g_manager_flag := null;
5208 g_position := null;
5209 g_pos_flex_01 := null;
5210 g_pos_flex_02 := null;
5211 g_pos_flex_03 := null;
5212 g_pos_flex_04 := null;
5213 g_pos_flex_05 := null;
5214 g_pos_flex_06 := null;
5215 g_pos_flex_07 := null;
5216 g_pos_flex_08 := null;
5217 g_pos_flex_09 := null;
5218 g_pos_flex_10 := null;
5219 g_job := null;
5220 g_job_flex_01 := null;
5221 g_job_flex_02 := null;
5222 g_job_flex_03 := null;
5223 g_job_flex_04 := null;
5224 g_job_flex_05 := null;
5225 g_job_flex_06 := null;
5226 g_job_flex_07 := null;
5227 g_job_flex_08 := null;
5228 g_job_flex_09 := null;
5229 g_job_flex_10 := null;
5230 g_payroll := null;
5231 g_prl_flex_01 := null;
5232 g_prl_flex_02 := null;
5233 g_prl_flex_03 := null;
5234 g_prl_flex_04 := null;
5235 g_prl_flex_05 := null;
5236 g_prl_flex_06 := null;
5237 g_prl_flex_07 := null;
5238 g_prl_flex_08 := null;
5239 g_prl_flex_09 := null;
5240 g_prl_flex_10 := null;
5241 g_people_group := null;
5242 g_pay_basis := null;
5243 g_pbs_flex_01 := null;
5244 g_pbs_flex_02 := null;
5245 g_pbs_flex_03 := null;
5246 g_pbs_flex_04 := null;
5247 g_pbs_flex_05 := null;
5248 g_pbs_flex_06 := null;
5249 g_pbs_flex_07 := null;
5250 g_pbs_flex_08 := null;
5251 g_pbs_flex_09 := null;
5252 g_pbs_flex_10 := null;
5253 g_payroll_period_type := null;
5254 g_payroll_period_number := null;
5255 g_payroll_period_strtdt := null;
5256 g_payroll_period_enddt := null;
5257 g_payroll_costing := null;
5258 g_payroll_costing_id := null;
5259 g_payroll_consolidation_set := null;
5260 g_payroll_consolidation_set_id := null;
5261 g_asg_attr_1 := null;
5262 g_asg_attr_2 := null;
5263 g_asg_attr_3 := null;
5264 g_asg_attr_4 := null;
5265 g_asg_attr_5 := null;
5266 g_asg_attr_6 := null;
5267 g_asg_attr_7 := null;
5268 g_asg_attr_8 := null;
5269 g_asg_attr_9 := null;
5270 g_asg_attr_10 := null;
5271 --
5272 g_sup_full_name := null ;
5273 g_sup_employee_number := null ;
5274 g_asg_normal_hours := null ;
5275 g_asg_frequency := null ;
5276 g_asg_time_normal_start := null ;
5277 g_asg_time_normal_finish := null ;
5278 g_asg_supervisor_id := null ;
5279 g_base_salary := null ;
5280 g_asg_type := null ;
5281 --
5282 g_abs_reason_name := null;
5283 g_abs_category_name := null;
5284 g_abs_type_name := null;
5285 g_abs_reason := null;
5286 g_abs_category := null;
5287 g_abs_type := null;
5288 g_abs_start_dt := null;
5289 g_abs_end_dt := null;
5290 g_abs_duration := null;
5291 g_abs_last_update_date := null;
5292 g_abs_last_updated_by := null;
5293 g_abs_last_update_login := null;
5294 g_abs_created_by := null;
5295 g_abs_creation_date := null;
5296 g_abs_reason_cd := null; -- Bug 2841958
5297
5298 g_abs_flex_01 := null;
5299 g_abs_flex_02 := null;
5300 g_abs_flex_03 := null;
5301 g_abs_flex_04 := null;
5302 g_abs_flex_05 := null;
5303 g_abs_flex_06 := null;
5304 g_abs_flex_07 := null;
5305 g_abs_flex_08 := null;
5306 g_abs_flex_09 := null;
5307 g_abs_flex_10 := null;
5308 --
5309 g_prs_flex_01 := null;
5310 g_prs_flex_02 := null;
5311 g_prs_flex_03 := null;
5312 g_prs_flex_04 := null;
5313 g_prs_flex_05 := null;
5314 g_prs_flex_06 := null;
5315 g_prs_flex_07 := null;
5316 g_prs_flex_08 := null;
5317 g_prs_flex_09 := null;
5318 g_prs_flex_10 := null;
5319 --
5320 -- g_correspondence_language := null;
5321 -- g_work_telephone := null;
5322 -- g_nationality := null;
5323 -- g_email_address := null;
5324 --
5325 -- these globals are assigned value in this package, so initialized here
5326 g_enrt_pl_name := null;
5327 g_enrt_pl_typ_id := null;
5328 g_enrt_pl_typ_name := null;
5329 /* Start of Changes for WWBUG: 1828349 added */
5330 g_enrt_prtt_enrt_rslt_id := null;
5331 /* End of Changes for WWBUG: 1828349 added */
5332 --
5333 g_ee_pre_tax_cost := null;
5334 g_ee_after_tax_cost := null;
5335 g_ee_ttl_cost := null;
5336 g_er_ttl_cost := null;
5337 --
5338 g_per_in_ler_id := null;
5339 g_ler_id := null;
5340 g_ler_name := null;
5341 g_lf_evt_ocrd_dt := null;
5342 g_lf_evt_note_dt := null;
5343 --
5344 g_cm_type := null;
5345 g_cm_type_id := null;
5346 g_cm_lf_evt_ocrd_dt := null;
5347 g_cm_lf_evt := null;
5348 g_cm_lf_evt_id := null;
5349 g_cm_lf_evt_stat := null;
5350 g_cm_lf_evt_ntfn_dt := null;
5351 g_cm_trgr_proc_name := null;
5352 g_cm_trgr_proc_dt := null;
5353 g_cm_addr_line1 := null;
5354 g_cm_addr_line2 := null;
5355 g_cm_addr_line3 := null;
5356 g_cm_city := null;
5357 g_cm_state := null;
5358 g_cm_postal_code := null;
5359 g_cm_country := null;
5360 g_cm_county := null;
5361 g_cm_region_3 := null;
5362 g_cm_dlvry_instn_txt := null;
5363 g_cm_inspn_rqd_flag := null;
5364 g_cm_to_be_sent_dt := null;
5365 --
5366 g_per_cm_prvdd_id := null;
5367 g_per_cm_object_version_number := null;
5368 --
5369 g_cbra_ler_id := null;
5370 g_cbra_ler_name := null;
5371 g_cbra_strt_dt := null;
5372 g_cbra_end_dt := null;
5373 --
5374 g_flex_credit_provided := null;
5375 g_flex_credit_forfited := null;
5376 g_flex_credit_used := null;
5377 g_flex_credit_excess := null;
5378 --intializing other id
5379 g_assignment_id := null ;
5380 g_dpnt_cvrd_dpnt_id := null ;
5381 g_elig_dpnt_id := null ;
5382
5383 --- intialize cwb globals
5384 g_cwb_per_group_per_in_ler_id := null ;
5385 g_cwb_per_group_pl_id := null ;
5386 g_CWB_Person_FULL_NAME := null ;
5387 g_CWB_Person_Custom_Name := null ;
5388 g_CWB_Life_Event_Name := null ;
5389 g_CWB_Life_Event_Occurred_Date := null ;
5390 g_CWB_Person_EMAIL_DDRESS := null ;
5391 g_CWB_Person_EMPLOYEE_NUMBER := null ;
5392 g_CWB_Person_BASE_SALARY := null ;
5393 g_CWB_Person_Brief_Name := null ;
5394 g_CWB_Person_BG_Name := null ;
5395 g_CWB_Person_CHANGE_REASON := null ;
5396 g_CWB_PEOPLE_GROUP_NAME := null ;
5397 g_CWB_PEOPLE_GROUP_SEGMENT1 := null ;
5398 g_CWB_PEOPLE_GROUP_SEGMENT10 := null ;
5399 g_CWB_PEOPLE_GROUP_SEGMENT11 := null ;
5400 g_CWB_PEOPLE_GROUP_SEGMENT2 := null ;
5401 g_CWB_PEOPLE_GROUP_SEGMENT3 := null ;
5402 g_CWB_PEOPLE_GROUP_SEGMENT4 := null ;
5403 g_CWB_PEOPLE_GROUP_SEGMENT5 := null ;
5404 g_CWB_PEOPLE_GROUP_SEGMENT6 := null ;
5405 g_CWB_PEOPLE_GROUP_SEGMENT7 := null ;
5406 g_CWB_PEOPLE_GROUP_SEGMENT8 := null ;
5407 g_CWB_PEOPLE_GROUP_SEGMENT9 := null ;
5408 g_CWB_Persom_PERF_RATING_TYPE := null ;
5409 g_CWB_Person_PERF_RATING := null ;
5410 g_CWB_Person_BASE_SALARY_FREQ := null ;
5411 g_CWB_Person_EMPloyee_CATEGORY := null ;
5412 g_CWB_Person_Grade_COMPARATIO := null ;
5413 g_CWB_Person_POST_PROCESS_Stat := null ;
5414 g_CWB_Person_START_DATE := null ;
5415 g_CWB_Person_ADJUSTED_SVC_DATE := null ;
5416 g_CWB_Person_Assg_ATTRIBUTE1 := null ;
5417 g_CWB_Person_Assg_ATTRIBUTE10 := null ;
5418 g_CWB_Person_Assg_ATTRIBUTE11 := null ;
5419 g_CWB_Person_Assg_ATTRIBUTE12 := null ;
5420 g_CWB_Person_Assg_ATTRIBUTE13 := null ;
5421 g_CWB_Person_Assg_ATTRIBUTE14 := null ;
5422 g_CWB_Person_Assg_ATTRIBUTE15 := null ;
5423 g_CWB_Person_Assg_ATTRIBUTE16 := null ;
5424 g_CWB_Person_Assg_ATTRIBUTE17 := null ;
5425 g_CWB_Person_Assg_ATTRIBUTE18 := null ;
5426 g_CWB_Person_Assg_ATTRIBUTE19 := null ;
5427 g_CWB_Person_Assg_ATTRIBUTE2 := null ;
5428 g_CWB_Person_Assg_ATTRIBUTE20 := null ;
5429 g_CWB_Person_Assg_ATTRIBUTE21 := null ;
5430 g_CWB_Person_Assg_ATTRIBUTE22 := null ;
5431 g_CWB_Person_Assg_ATTRIBUTE23 := null ;
5432 g_CWB_Person_Assg_ATTRIBUTE24 := null ;
5433 g_CWB_Person_Assg_ATTRIBUTE25 := null ;
5434 g_CWB_Person_Assg_ATTRIBUTE26 := null ;
5435 g_CWB_Person_Assg_ATTRIBUTE28 := null ;
5436 g_CWB_Person_Assg_ATTRIBUTE29 := null ;
5437 g_CWB_Person_Assg_ATTRIBUTE3 := null ;
5438 g_CWB_Person_Assg_ATTRIBUTE30 := null ;
5439 g_CWB_Person_Assg_ATTRIBUTE4 := null ;
5440 g_CWB_Person_Assg_ATTRIBUTE5 := null ;
5441 g_CWB_Person_Assg_ATTRIBUTE6 := null ;
5442 g_CWB_Person_Assg_ATTRIBUTE7 := null ;
5443 g_CWB_Person_Assg_ATTRIBUTE8 := null ;
5444 g_CWB_Person_Assg_ATTRIBUTE9 := null ;
5445 g_CWB_Person_Assg_ATTRIBUTE27 := null ;
5446 g_CWB_Person_Info_ATTRIBUTE1 := null ;
5447 g_CWB_Person_Info_ATTRIBUTE10 := null ;
5448 g_CWB_Person_Info_ATTRIBUTE2 := null ;
5449 g_CWB_Person_Info_ATTRIBUTE3 := null ;
5450 g_CWB_Person_Info_ATTRIBUTE4 := null ;
5451 g_CWB_Person_Info_ATTRIBUTE5 := null ;
5452 g_CWB_Person_Info_ATTRIBUTE6 := null ;
5453 g_CWB_Person_Info_ATTRIBUTE7 := null ;
5454 g_CWB_Person_Info_ATTRIBUTE11 := null ;
5455 g_CWB_Person_Info_ATTRIBUTE12 := null ;
5456 g_CWB_Person_Info_ATTRIBUTE13 := null ;
5457 g_CWB_Person_Info_ATTRIBUTE14 := null ;
5458 g_CWB_Person_Info_ATTRIBUTE15 := null ;
5459 g_CWB_Person_Info_ATTRIBUTE16 := null ;
5460 g_CWB_Person_Info_ATTRIBUTE17 := null ;
5461 g_CWB_Person_Info_ATTRIBUTE18 := null ;
5462 g_CWB_Person_Info_ATTRIBUTE19 := null ;
5463 g_CWB_Person_Info_ATTRIBUTE20 := null ;
5464 g_CWB_Person_Info_ATTRIBUTE21 := null ;
5465 g_CWB_Person_Info_ATTRIBUTE22 := null ;
5466 g_CWB_Person_Info_ATTRIBUTE23 := null ;
5467 g_CWB_Person_Info_ATTRIBUTE24 := null ;
5468 g_CWB_Person_Info_ATTRIBUTE25 := null ;
5469 g_CWB_Person_Info_ATTRIBUTE26 := null ;
5470 g_CWB_Person_Info_ATTRIBUTE27 := null ;
5471 g_CWB_Person_Info_ATTRIBUTE28 := null ;
5472 g_CWB_Person_Info_ATTRIBUTE29 := null ;
5473 g_CWB_Person_Info_ATTRIBUTE30 := null ;
5474 g_CWB_Person_Info_ATTRIBUTE8 := null ;
5475 g_CWB_Person_Info_ATTRIBUTE9 := null ;
5476 g_CWB_Person_CUSTOM_SEGMENT1 := null ;
5477 g_CWB_Person_CUSTOM_SEGMENT10 := null ;
5478 g_CWB_Person_CUSTOM_SEGMENT11 := null ;
5479 g_CWB_Person_CUSTOM_SEGMENT13 := null ;
5480 g_CWB_Person_CUSTOM_SEGMENT14 := null ;
5481 g_CWB_Person_CUSTOM_SEGMENT2 := null ;
5482 g_CWB_Person_CUSTOM_SEGMENT4 := null ;
5483 g_CWB_Person_CUSTOM_SEGMENT5 := null ;
5484 g_CWB_Person_CUSTOM_SEGMENT6 := null ;
5485 g_CWB_Person_CUSTOM_SEGMENT7 := null ;
5486 g_CWB_Person_CUSTOM_SEGMENT9 := null ;
5487 g_CWB_Person_CUSTOM_SEGMENT12 := null ;
5488 g_CWB_Person_CUSTOM_SEGMENT15 := null ;
5489 g_CWB_Person_CUSTOM_SEGMENT8 := null ;
5490 g_CWB_Person_CUSTOM_SEGMENT3 := null ;
5491 g_CWB_Person_FEEDBACK_RATING := null ;
5492 g_CWB_Person_FREQUENCY := null ;
5493 g_CWB_Person_Grade_MAX_VAL := null ;
5494 g_CWB_Person_Grade_MID_POINT := null ;
5495 g_CWB_Person_Grade_MIN_VAL := null ;
5496 g_CWB_Person_GRADE_name := null ;
5497 g_CWB_Person_Grade_QUARTILE := null ;
5498 g_CWB_Person_GRADE_ANN_FACTOR := null ;
5499 g_CWB_Person_JOB_name := null ;
5500 g_CWB_Person_LEGISLATION := null ;
5501 g_CWB_Person_LOCATION := null ;
5502 g_CWB_Person_NORMAL_HOURS := null ;
5503 g_CWB_Person_ORG_name := null ;
5504 g_CWB_Person_ORIG_START_DATE := null ;
5505 g_CWB_Person_PAY_RATE := null ;
5506 g_CWB_Person_PAY_ANNUL_FACTOR := null ;
5507 g_CWB_Person_PAYROLL_NAME := null ;
5508 g_CWB_Person_PERF_RATING_DATE := null ;
5509 g_CWB_Person_POSITION := null ;
5510 g_CWB_Person_STATUS_TYPE := null ;
5511 g_CWB_Person_SUP_BRIEF_NAME := null ;
5512 g_CWB_Person_SUP_CUSTOM_NAME := null ;
5513 g_CWB_Person_SUP_FULL_NAME := null ;
5514 g_CWB_Person_YEARS_EMPLOYED := null ;
5515 g_CWB_Person_YEARS_IN_GRADE := null ;
5516 g_CWB_Person_YEARS_IN_POS := null ;
5517 g_CWB_Person_YEARS_IN_JOB := null ;
5518 g_cwb_nw_chg_reason := null ;
5519 g_CWB_new_Job_name := null ;
5520 g_CWB_new_Grade_name := null ;
5521 g_CWB_new_Group_name := null ;
5522 g_CWB_new_Postion_name := null ;
5523 g_CWB_new_Perf_rating := null ;
5524 g_CWB_LE_Dt := null ;
5525 g_CWB_effective_date := null ;
5526 g_CWB_Life_Event_status := null ;
5527 g_cwb_group_plan_name := null ;
5528 -- subheader
5529 g_group_elmt_value1 := null ;
5530 g_group_elmt_value2 := null ;
5531 if g_debug then
5532 hr_utility.set_location('Exiting'||l_proc, 15);
5533 end if;
5534 --
5535 End init_detail_globals;
5536 --
5537 -- ----------------------------------------------------------------------------
5538 -- |------< write_error >---------------------------------------------|
5539 -- ----------------------------------------------------------------------------
5540 --
5541 Procedure write_error(p_err_num in number,
5542 p_err_name in varchar2,
5543 p_typ_cd in varchar2,
5544 p_request_id in number,
5545 p_ext_rslt_id in number) IS
5546 --
5547 l_proc varchar2(72);
5548 l_err_num number(15);
5549 --
5550 cursor err_cnt_c is
5551 select count(*) from ben_ext_rslt_err
5552 where ext_rslt_id = p_ext_rslt_id --request_id = p_request_id
5553 and typ_cd <> 'W';
5554 --
5555 --
5556 begin
5557 --
5558 g_debug := hr_utility.debug_enabled;
5559 if g_debug then
5560 l_proc := g_package||'write_error';
5561 hr_utility.set_location('Entering'||l_proc, 5);
5562 hr_utility.set_location('error message ' || p_err_name,99.97);
5563 end if;
5564 --
5565 open err_cnt_c;
5566 fetch err_cnt_c into l_err_num;
5567 close err_cnt_c;
5568 --
5569
5570 if l_err_num >= ben_ext_thread.g_max_errors_allowed then
5571 --
5572 ben_ext_thread.g_err_num := 91947;
5573 ben_ext_thread.g_err_name := 'BEN_91947_EXT_MX_ERR_NUM';
5574 raise ben_ext_thread.g_job_failure_error;
5575 --
5576 end if;
5577 --
5578 if g_business_group_id is not null then
5579 --
5580 ben_ext_util.write_err
5581 (p_err_num => p_err_num,
5582 p_err_name => p_err_name, --error form will take care of it,
5583 p_typ_cd => p_typ_cd,
5584 p_person_id => g_person_id,
5585 p_request_id => p_request_id,
5586 p_ext_rslt_id => p_ext_rslt_id,
5587 p_business_group_id => g_business_group_id
5588 );
5589 --
5590 commit;
5591 --
5592 end if;
5593 --
5594 if g_debug then
5595 hr_utility.set_location('Exiting'||l_proc, 15);
5596 end if;
5597 --
5598 end write_error;
5599 --
5600 END ben_ext_person;