[Home] [Help]
PACKAGE BODY: APPS.GHR_FEHB_PLAN_DESIGN
Source
1 PACKAGE BODY ghr_fehb_plan_design AS
2 /* $Header: ghfehbpd.pkb 120.2 2005/06/28 14:07:36 bgarg noship $ */
3
4 --
5 -- Package Variables
6 --
7 g_package varchar2(100) := 'ghr_fehb_plan_design.';
8
9 procedure create_sub_life_events(p_target_business_group_id in number)
10 is
11 l_ler_id ben_ler_f.ler_id%type;
12 l_effective_start_date ben_ler_f.effective_start_date%type;
13 l_effective_end_date ben_ler_f.effective_end_date%type;
14 l_object_version_number ben_ler_f.object_version_number%type;
15 l_ler_name ben_ler_f.name%type;
16 l_ler_short_code ben_ler_f.short_code%type;
17 l_target_business_group_id number := 1503;
18 l_exists Varchar2(1);
19
20 Cursor check_if_ler_exists is
21 select 'Y'
22 from ben_ler_f
23 where name = l_ler_name
24 and business_group_id = p_target_business_group_id;
25 Begin
26 for i in 1..3 Loop
27 if i = 1 then
28 l_ler_name := 'Change in Dependents';
29 Elsif i = 2 then
30 l_ler_name := 'Change in Marital Status';
31 Elsif i = 3 then
32 l_ler_name := 'Change in Dependents - End Date';
33 End If;
34
35 l_exists := 'N';
36 for i in check_if_ler_exists Loop
37 l_exists := 'Y';
38 End Loop;
39 If l_exists = 'N' Then
40 ben_life_event_reason_api.create_life_event_reason(
41 p_ler_id => l_ler_id
42 ,p_effective_start_date => l_effective_start_date
43 ,p_effective_end_date => l_effective_end_date
44 ,p_name => l_ler_name
45 ,p_business_group_id => p_target_business_group_id
46 ,p_object_version_number => l_object_version_number
47 ,p_ovridg_le_flag => 'N'
48 ,p_qualg_evt_flag => 'N'
49 ,p_ck_rltd_per_elig_flag => 'N'
50 ,p_cm_aply_flag => 'N'
51 ,p_typ_cd => 'PRSNL'
52 --,p_short_code => l_ler_short_code
53 ,p_effective_date => to_date('1951/01/01','yyyy/mm/dd')
54 );
55 End If;
56 End Loop;
57 End Create_sub_life_events;
58
59 Procedure create_person_type_usages (p_target_business_group_id in Number) is
60
61 l_ff_id ff_formulas_f.formula_id%type;
62 l_formula_name ff_formulas_f.formula_name%type;
63 l_pt_name BEN_PER_INFO_CHG_CS_LER_F.name%type;
64 l_source_table BEN_PER_INFO_CHG_CS_LER_F.source_table%type;
65 l_source_column BEN_PER_INFO_CHG_CS_LER_F.source_column%type;
66 l_le_name BEN_LER_F.name%type;
67
68
69 cursor c_get_ler_id is
70 select ler_id,effective_start_date from ben_ler_f
71 where name = l_le_name
72 and business_group_id = p_target_business_group_id;
73
74 l_proc varchar2(100) := 'create_person_type_usages';
75 l_chg_cs_ler_id number;
76 l_per_info_cs_id number;
77 l_clpse_lf_evt_id ben_clpse_lf_evt_f.clpse_lf_evt_id%type;
78 l_esd date := to_date('1951/01/01','yyyy/mm/dd');
79 l_eed date := to_date('4712/12/31','yyyy/mm/dd');
80 l_ovn number;
81 l_ler_id ben_ler_f.ler_id%type;
82 l_ler_id1 ben_ler_f.ler_id%type;
83 l_ler_id2 ben_ler_f.ler_id%type;
84 l_ler_id3 ben_ler_f.ler_id%type;
85 l_effective_date date;
86
87
88 cursor c_get_ff_id is
89 select formula_id
90 from ff_formulas_f
91 where formula_name = l_formula_name
92 and l_effective_date between
93 effective_start_date and effective_end_date;
94
95 cursor c_chk_per_info_chg is
96 select name,per_info_chg_cs_ler_id from BEN_PER_INFO_CHG_CS_LER_F
97 where source_table = l_source_table
98 and source_column = l_source_column
99 and business_group_id = p_target_business_group_id;
100
101 cursor c_chk_lpl(p_per_info_chg_cs_ler_id in number,
102 p_ler_id in number
103 ) is
104 SELECT 'X'
105 FROM ben_ler_per_info_cs_ler_f
106 WHERE per_info_chg_cs_ler_id = p_per_info_chg_cs_ler_id
107 AND ler_id = p_ler_id
108 AND business_group_id = p_target_business_group_id;
109
110
111
112 l_exists boolean default FALSE;
113 Begin
114
115 -- Change in Marital Status
116 l_exists := FALSE;
117 l_le_name := 'Change in Marital Status';
118 l_pt_name := 'Change in Marital Status';
119 l_source_table := 'PER_ALL_PEOPLE_F';
120 l_source_column := 'MARITAL_STATUS';
121 For i in c_get_ler_id loop
122 l_ler_id1 := i.ler_id;
123 l_effective_date := i.effective_start_date;
124 End Loop;
125 For chk_per_info_chg in c_chk_per_info_chg
126 loop
127 l_chg_cs_ler_id := chk_per_info_chg.per_info_chg_cs_ler_id;
128 l_exists := TRUE;
129 exit;
130 end loop;
131
132 IF l_ler_id1 is NOT NULL THEN
133 IF NOT l_exists THEN
134 l_formula_name := 'GHR_MARI_STATUS_LER_TRIGGER';
135
136 FOR ff_rec IN c_get_ff_id
137 LOOP
138 l_ff_id := ff_rec.formula_id;
139 exit;
140 END LOOP;
141
142 -- To create the Person Type Change
143 ben_Person_Change_Cs_Ler_api.create_person_change_cs_ler
144 (p_per_info_chg_cs_ler_id => l_chg_cs_ler_id
145 ,p_effective_start_date => l_esd
146 ,p_effective_end_date => l_eed
147 ,p_name => l_le_name
148 , p_old_val => 'OABANY'
149 ,p_new_val => 'OABANY'
150 ,p_source_column => l_source_column
151 ,p_source_table => l_source_table
152 ,p_per_info_chg_cs_ler_rl => l_ff_id
153 ,p_business_group_id => p_target_business_group_id
154 ,p_object_version_number => l_ovn
155 ,p_effective_date => l_effective_date
156 );
157 END IF;
158 l_exists := FALSE;
159 For chk_lpl in c_chk_lpl(l_chg_cs_ler_id,l_ler_id1)
160 loop
161 l_exists := TRUE;
162 exit;
163 end loop;
164 IF NOT l_exists THEN
165 hr_utility.set_location('Calling create_ler_per_info_cs:'|| l_proc, 10);
166
167 -- To associate the Person Type Change with the LER
168 ben_Ler_Per_Info_Cs_Ler_api.create_Ler_Per_Info_Cs_Ler
169 (p_ler_per_info_cs_ler_id => l_per_info_cs_id
170 ,p_effective_start_date => l_esd
171 ,p_effective_End_date => l_eed
172 ,p_per_info_chg_cs_ler_id => l_chg_cs_ler_id
173 ,p_ler_id => l_ler_id1
174 ,p_business_Group_id => p_target_business_group_id
175 ,p_object_version_number => l_ovn
176 ,p_effective_date => l_effective_date
177 );
178 END IF;
179 END IF;
180
181 -- Change in Dependents
182 l_exists := FALSE;
183 l_le_name := 'Change in Dependents';
184 l_pt_name := 'Change in Dependents';
185 l_source_column := 'CONTACT_TYPE';
186 l_source_table := 'PER_CONTACT_RELATIONSHIPS';
187 For i in c_get_ler_id loop
188 l_ler_id2 := i.ler_id;
189 l_effective_date := i.effective_start_date;
190 End Loop;
191 For chk_per_info_chg in c_chk_per_info_chg
192 loop
193 l_chg_cs_ler_id := chk_per_info_chg.per_info_chg_cs_ler_id;
194 l_exists := TRUE;
195 exit;
196 end loop;
197
198 IF l_ler_id2 is not null THEN
199 IF NOT l_exists THEN
200 -- To create the Person Type Change
201 l_formula_name := 'GHR_CONT_LER_TRIGGER';
202
203 FOR ff_rec IN c_get_ff_id
204 LOOP
205 l_ff_id := ff_rec.formula_id;
206 exit;
207 END LOOP;
208
209 ben_Person_Change_Cs_Ler_api.create_person_change_cs_ler
210 (p_per_info_chg_cs_ler_id => l_chg_cs_ler_id
211 ,p_effective_start_date => l_esd
212 ,p_effective_end_date => l_eed
213 ,p_name => l_le_name
214 , p_old_val => 'OABANY'
215 ,p_new_val => 'OABANY'
216 ,p_source_column => l_source_column
217 ,p_source_table => l_source_table
218 ,p_per_info_chg_cs_ler_rl => l_ff_id
219 ,p_business_group_id => p_target_business_group_id
220 ,p_object_version_number => l_ovn
221 ,p_effective_date => l_effective_date
222 );
223 END IF;
224 l_exists := FALSE;
225 For chk_lpl in c_chk_lpl(l_chg_cs_ler_id,l_ler_id2)
226 loop
227 l_exists := TRUE;
228 exit;
229 end loop;
230 IF NOT l_exists THEN
231
232 hr_utility.set_location('Calling create_ler_per_info_cs:'|| l_proc, 10);
233
234 -- To associate the Person Type Change with the LER
235 ben_Ler_Per_Info_Cs_Ler_api.create_Ler_Per_Info_Cs_Ler
236 (p_ler_per_info_cs_ler_id => l_per_info_cs_id
237 ,p_effective_start_date => l_esd
238 ,p_effective_End_date => l_eed
239 ,p_per_info_chg_cs_ler_id => l_chg_cs_ler_id
240 ,p_ler_id => l_ler_id2
241 ,p_business_Group_id => p_target_business_group_id
242 ,p_object_version_number => l_ovn
243 ,p_effective_date => l_effective_date
244 );
245 END IF;
246 END IF;
247 -- Change in Dependents -- End Date
248 l_exists := FALSE;
249 l_le_name := 'Change in Dependents - End Date';
250 l_pt_name := 'Change in Dependents - End Date';
251 l_source_column := 'DATE_END';
252 l_source_table := 'PER_CONTACT_RELATIONSHIPS';
253 For i in c_get_ler_id loop
254 l_ler_id2 := i.ler_id;
255 l_effective_date := i.effective_start_date;
256 End Loop;
257 For chk_per_info_chg in c_chk_per_info_chg
258 loop
259 l_chg_cs_ler_id := chk_per_info_chg.per_info_chg_cs_ler_id;
260 l_exists := TRUE;
261 exit;
262 end loop;
263
264 IF l_ler_id2 is not null THEN
265 IF NOT l_exists THEN
266 -- To create the Person Type Change
267 l_formula_name := 'GHR_CONT_LER_TRIGGER';
268
269 FOR ff_rec IN c_get_ff_id
270 LOOP
271 l_ff_id := ff_rec.formula_id;
272 exit;
273 END LOOP;
274
275 ben_Person_Change_Cs_Ler_api.create_person_change_cs_ler
276 (p_per_info_chg_cs_ler_id => l_chg_cs_ler_id
277 ,p_effective_start_date => l_esd
278 ,p_effective_end_date => l_eed
279 ,p_name => l_le_name
280 , p_old_val => 'OABANY'
281 ,p_new_val => 'OABANY'
282 ,p_source_column => l_source_column
283 ,p_source_table => l_source_table
284 ,p_per_info_chg_cs_ler_rl => l_ff_id
285 ,p_business_group_id => p_target_business_group_id
286 ,p_object_version_number => l_ovn
287 ,p_effective_date => l_effective_date
288 );
289 END IF;
290 l_exists := FALSE;
291 For chk_lpl in c_chk_lpl(l_chg_cs_ler_id,l_ler_id2)
292 loop
293 l_exists := TRUE;
294 exit;
295 end loop;
296 IF NOT l_exists THEN
297
298 hr_utility.set_location('Calling create_ler_per_info_cs:'|| l_proc, 10);
299
300 -- To associate the Person Type Change with the LER
301 ben_Ler_Per_Info_Cs_Ler_api.create_Ler_Per_Info_Cs_Ler
302 (p_ler_per_info_cs_ler_id => l_per_info_cs_id
303 ,p_effective_start_date => l_esd
304 ,p_effective_End_date => l_eed
305 ,p_per_info_chg_cs_ler_id => l_chg_cs_ler_id
306 ,p_ler_id => l_ler_id2
307 ,p_business_Group_id => p_target_business_group_id
308 ,p_object_version_number => l_ovn
309 ,p_effective_date => l_effective_date
310 );
311 END IF;
312 END IF;
313
314 -- Modified the processing to attach New life event with the address chnage.
315 -- Change in Primary Address
316 l_exists := FALSE;
317 l_le_name := 'Employee/Family member loses coverage under FEHB or another group plan';
318 l_pt_name := 'Employee/Family member loses coverage under FEHB or another group plan';
319 l_source_table := 'PER_ADDRESSES';
320 l_source_column := 'REGION_2';
321 For i in c_get_ler_id loop
322 l_ler_id3 := i.ler_id;
323 l_effective_date := i.effective_start_date;
324 End Loop;
325 For chk_per_info_chg in c_chk_per_info_chg
326 loop
327 l_chg_cs_ler_id := chk_per_info_chg.per_info_chg_cs_ler_id;
328 l_exists := TRUE;
329 exit;
330 end loop;
331 IF l_ler_id3 is not null THEN
332 IF NOT l_exists THEN
333 -- Create the Person Type Change
334 l_formula_name := 'GHR_ADDRESS_LER_TRIGGER';
335
336 FOR ff_rec IN c_get_ff_id
337 LOOP
338 l_ff_id := ff_rec.formula_id;
339 exit;
340 END LOOP;
341
342 ben_Person_Change_Cs_Ler_api.create_person_change_cs_ler
343 (p_per_info_chg_cs_ler_id => l_chg_cs_ler_id
344 ,p_effective_start_date => l_esd
345 ,p_effective_end_date => l_eed
346 ,p_name => l_le_name
347 ,p_old_val => 'OABANY'
348 ,p_new_val => 'OABANY'
349 ,p_source_column => l_source_column
350 ,p_source_table => l_source_table
351 ,p_per_info_chg_cs_ler_rl => l_ff_id
352 ,p_business_group_id => p_target_business_group_id
353 ,p_object_version_number => l_ovn
354 ,p_effective_date => l_effective_date
355 );
356 END IF;
357 l_exists := FALSE;
358 For chk_lpl in c_chk_lpl(l_chg_cs_ler_id,l_ler_id3)
359 loop
360 l_exists := TRUE;
361 exit;
362 end loop;
363 IF NOT l_exists THEN
364
365 hr_utility.set_location('Calling create_ler_per_info_cs:'|| l_proc, 10);
366
367 -- To associate the Person Type Change with the LER
368 ben_Ler_Per_Info_Cs_Ler_api.create_Ler_Per_Info_Cs_Ler
369 (p_ler_per_info_cs_ler_id => l_per_info_cs_id
370 ,p_effective_start_date => l_esd
371 ,p_effective_End_date => l_eed
372 ,p_per_info_chg_cs_ler_id => l_chg_cs_ler_id
373 ,p_ler_id => l_ler_id3
374 ,p_business_Group_id => p_target_business_group_id
375 ,p_object_version_number => l_ovn
376 ,p_effective_date => l_effective_date
377 );
378 END IF;
379 END IF;
380
381 hr_utility.set_location('Leaving: '|| g_package||l_proc, 50);
382 End create_person_type_usages;
383
384 Procedure create_collapse_rule (p_target_business_group_id in Number) is
385 l_clpse_lf_evt_id ben_clpse_lf_evt_f.clpse_lf_evt_id%type;
386 l_esd date := to_date('1951/01/01','yyyy/mm/dd');
387 l_eed date := to_date('4712/12/31','yyyy/mm/dd');
388 l_ovn number;
389 l_ler_id ben_ler_f.ler_id%type;
390 l_ler_id1 ben_ler_f.ler_id%type;
391 l_ler_id2 ben_ler_f.ler_id%type;
392 l_ler_id3 ben_ler_f.ler_id%type;
393 l_effective_date date;
394 l_le_name BEN_LER_F.name%type;
395 l_ctr number;
396 l_proc varchar2(100) := 'create_collapse_rule';
397 l_seq ben_clpse_lf_evt_f.seq%type;
398 -- Check whether an existing Collapsing rule is there between
399 -- the main life event and sub life events
400 cursor c_chk_clp is
401 select clpse_lf_evt_id
402 from ben_clpse_lf_evt_f clp
403 where clp.business_group_id = p_target_business_group_id
404 and eval_ler_id = l_ler_id
405 and ler1_id = l_ler_id1
406 and ler2_id = l_ler_id2
407 and ler3_id = l_ler_id3;
408 -- Get the maximum sequence number for the given business group id
409 cursor c_get_clp_max_seq is
410 select nvl(max(seq),0) seqnum
411 from ben_clpse_lf_evt_f clp
412 where clp.business_group_id = p_target_business_group_id;
413
414 cursor c_chk_ler is
415 select ler_id,effective_start_date from ben_ler_f
416 where name = l_le_name
417 and business_group_id = p_target_business_group_id;
418 begin
419 hr_utility.set_location('Entering: '|| g_package||l_proc, 5);
420 -- Create the Sub Life Events
421 create_sub_life_events(p_target_business_group_id);
422 -- Create person type changes and Attach person type changes
423 create_person_type_usages(p_target_business_group_id);
424 -- Create Collpasing Rule
425 -- Linking three sub life events to main life event 'Change in Family Status'
426 l_clpse_lf_evt_id := NULL;
427 for i in 1..4 Loop
428 l_ctr := i;
429 if i = 1 then
430 l_le_name := 'Change in Family Status';
431 elsif i = 2 then
432 l_le_name := 'Change in Dependents';
433 elsif i = 3 then
434 l_le_name := 'Change in Marital Status';
435 elsif i = 4 then
436 l_le_name := 'Change in Dependents - End Date';
437 end If;
438
439 for chk_ler in c_chk_ler loop
440 IF l_ctr = 1 then
441 l_ler_id := chk_ler.ler_id;
442 l_effective_date := chk_ler.effective_start_date;
443 ELSIF l_ctr = 2 then
444 l_ler_id1 := chk_ler.ler_id;
445 ELSIF l_ctr = 3 then
446 l_ler_id2 := chk_ler.ler_id;
447 ELSIF l_ctr = 4 then
448 l_ler_id3 := chk_ler.ler_id;
449 END IF;
450 End Loop;
451 End Loop;
452 For chk_clp in c_chk_clp loop
453 l_clpse_lf_evt_id := chk_clp.clpse_lf_evt_id;
454 exit;
455 End Loop;
456 IF l_clpse_lf_evt_id is NULL then
457 IF l_ler_id is not null and
458 l_ler_id1 is not null and
459 l_ler_id2 is not null and
460 l_ler_id3 is not null THEN
461 For max_seq in c_get_clp_max_seq loop
462 l_seq := max_seq.seqnum + 1;
463 exit;
464 End Loop;
465
466 ben_clpse_lf_evt_api.create_clpse_lf_evt
467 (p_validate => FALSE
468 ,p_clpse_lf_evt_id => l_clpse_lf_evt_id
469 ,p_effective_start_date => l_esd
470 ,p_effective_end_date => l_eed
471 ,p_business_group_id => p_target_business_group_id
472 ,p_eval_ler_id => l_ler_id
473 ,p_seq => l_seq
474 ,p_ler1_id => l_ler_id1
475 ,p_bool1_cd => 'OR'
476 ,p_ler2_id => l_ler_id2
477 ,p_bool2_cd => 'OR'
478 ,p_ler3_id => l_ler_id3
479 ,p_eval_cd => 'V'
480 ,p_eval_ler_det_cd => 'ELED'
481 ,p_object_version_number => l_ovn
482 ,p_effective_date => l_effective_date
483 );
484 END IF;
485 END IF;
486 end create_collapse_rule;
487
488
489 Procedure create_program_and_plans (p_target_business_group_id in Number) is
490 --
491 l_proc Varchar2(100):= g_package||'create_program_and_plans';
492 p_validate Number := 0;
493 p_copy_entity_txn_id Number;
494 p_effective_date Date;
495 p_prefix_suffix_cd Varchar2(2);
496 p_prefix_suffix_text Varchar2(2);
497 p_reuse_object_flag Varchar2(1);
498 p_transaction_category_id Number(15);
499 l_effective_start_date Date;
500 l_effective_end_date Date;
501 Nothing_To_Do Exception;
502
503 --
504 Cursor get_txn_category_id is
505 select transaction_category_id
506 from pqh_transaction_categories
507 where short_name = 'BEN_PDCPWZ';
508 Cursor get_copy_txn_id is
509 select copy_entity_txn_id
510 from pqh_copy_entity_txns
511 where transaction_category_id = p_transaction_category_id
512 and context_business_group_id = 0
513 and display_name = 'GHR_FEHB_SEED_PROGRAM_DESIGN';
514 Cursor update_program_status is
515 select * from ben_pgm_f
516 where name = 'Federal Employees Health Benefits'
517 and business_group_id = p_target_business_group_id;
518 --
519 Begin
520
521 hr_utility.set_location('Entering:'|| g_package||l_proc, 5);
522
523 Open get_txn_category_id;
524 Fetch get_txn_category_id into p_transaction_category_id;
525 hr_utility.trace('Transaction Category Id :'|| p_transaction_category_id);
526 hr_utility.set_location('Opening cursor get_copy_txn_id '||l_proc, 10);
527 --dbms_output.put_line('txn category id '||p_transaction_category_id);
528
529 Open get_copy_txn_id;
530 fetch get_copy_txn_id into p_copy_entity_txn_id;
531 If get_copy_txn_id%notfound then
532 Raise Nothing_to_do;
533 End If;
534 hr_utility.trace('Copy entity Txn. Id :'|| p_copy_entity_txn_id);
535 --dbms_output.put_line('copy_entity_txn_id :'||p_copy_entity_txn_id );
536
537 ----------------------------
538 /* This update is introduced to open the lookup type delivererd by Benefits team */
539 /* which is being used only by GHr customers for now. It would be open for all */
540 /* customers at a later date, at which point this update can be removed form here */
541
542 update FND_LOOKUP_VALUES
543 set end_date_active = null,
544 description = null,
545 last_updated_by = 1
546 where lookup_code = 'FDPPELD'
547 and lookup_type = 'BEN_ENRT_CVG_STRT'
548 and language = 'US' ;
549
550 -----------------------
551 -- Set the variables
552 p_effective_date := to_date('12/31/2020','MM/DD//YYYY');
553 p_prefix_suffix_cd := null;
554 p_prefix_suffix_text := null;
555 p_reuse_object_flag := 'Y';
556
557 BEN_PD_COPY_TO_BEN_five.g_ghr_mode := 'TRUE';
558
559 --dbms_output.put_line('now callinmg..........');
560 BEN_PD_COPY_TO_BEN_TWO.create_stg_to_ben_rows(p_validate,
561 p_copy_entity_txn_id,
562 p_effective_date,
563 p_prefix_suffix_text,
564 p_reuse_object_flag,
565 p_target_business_group_id,
566 p_prefix_suffix_cd);
567 --dbms_output.put_line('BACK');
568 For i in update_program_status Loop
569 ben_Program_api.update_program(
570 p_pgm_id => i.pgm_id
571 ,p_effective_start_date => l_effective_start_date
572 ,p_effective_end_date => l_effective_end_date
573 ,p_pgm_stat_cd => 'A'
574 ,p_object_version_number => i.object_version_number
575 ,p_effective_date => i.effective_start_date
576 ,p_datetrack_mode => 'CORRECTION'
577 );
578 End Loop;
579
580 If get_txn_category_id%ISOPEN then
581 CLOSE get_txn_category_id;
582 End If;
583 If get_copy_txn_id%ISOPEN then
584 CLOSE get_copy_txn_id;
585 End If;
586 hr_utility.set_location('Leaving :'|| g_package||l_proc, 50);
587
588 Exception
589 When Nothing_to_do Then
590 If get_txn_category_id%ISOPEN then
591 CLOSE get_txn_category_id;
592 End If;
593 If get_copy_txn_id%ISOPEN then
594 CLOSE get_copy_txn_id;
595 End If;
596 null;
597 When others then
598 If get_txn_category_id%ISOPEN then
599 CLOSE get_txn_category_id;
600 End If;
601 If get_copy_txn_id%ISOPEN then
602 CLOSE get_copy_txn_id;
603 End If;
604 hr_utility.set_location('Leaving :'|| g_package||l_proc, 70);
605 Raise;
606 End create_program_and_plans;
607
608
609
610 Procedure populate_fehb_plan_design (p_errbuf OUT NOCOPY VARCHAR2,
611 p_retcode OUT NOCOPY NUMBER,
612 p_target_business_group_id in Number) is
613
614 Cursor check_pgm_exists is
615 select 'Y' from ben_pgm_f
616 where name = 'Federal Employees Health Benefits'
617 and business_group_id = p_target_business_group_id;
618
619 l_proc varchar2(100):= 'Populate_fehb_plan_design.';
620 p_exists Varchar2(1):= 'N';
621 l_err_msg Varchar2(2000);
622 Nothing_To_Do Exception;
623
624
625 Begin
626 hr_utility.set_location('entering :'|| g_package||l_proc, 10);
627 hr_utility.trace('Business Group Id ' ||p_target_business_group_id);
628 Open check_pgm_exists;
629 Fetch check_pgm_exists into p_exists;
630 If check_pgm_exists%NOTFOUND Then
631 p_exists := 'N';
632 End If;
633 If p_exists = 'Y' then
634 Raise nothing_to_do;
635 End If;
636
637 Savepoint create_plan_design;
638 --dbms_output.put_line('now starting plan design ' ||p_target_business_group_id);
639 create_program_and_plans(p_target_business_group_id);
640 hr_utility.trace('After create_program_and_plans....');
641 create_collapse_rule(p_target_business_group_id);
642 --commit;
643 If check_pgm_exists%ISOPEN then
644 CLOSE check_pgm_exists;
645 End If;
646 hr_utility.set_location('Leaving :'|| g_package||l_proc, 50);
647 Exception
648 When Nothing_to_do Then
649 If check_pgm_exists%ISOPEN then
650 CLOSE check_pgm_exists;
651 End If;
652 null;
653 When others then
654 If check_pgm_exists%ISOPEN then
655 CLOSE check_pgm_exists;
656 End If;
657 hr_utility.set_location('Leaving :'|| g_package||l_proc, 60);
658 hr_utility.trace('Error ' ||sqlerrm(sqlcode));
659 l_err_msg := substr(p_target_business_group_id||':'||nvl(fnd_message.get,sqlerrm),1,1999) ;
660 rollback to create_plan_design;
661 ghr_wgi_pkg.create_ghr_errorlog
662 (p_program_name => l_proc,
663 p_log_text => l_err_msg,
664 p_message_name => null,
665 p_log_date => sysdate
666 );
667 commit;
668 End populate_fehb_plan_design;
669
670 /*
671
672 Procedure populate_fehb_pd_all_bgs is
673
674 Cursor c_get_business_group_id is
675 select business_group_id from per_business_groups;
676 --where business_group_id = 2670;
677
678 l_proc varchar2(100):= 'Populate_fehb_pd_all_bgs.';
679 p_errbuf varchar(2000);
680 p_retcode number;
681
682 Begin
683 hr_utility.set_location('entering :'|| g_package||l_proc, 10);
684 For i in c_get_business_group_id Loop
685 Begin
686 hr_utility.trace('Business Group Id ' ||i.business_group_id);
687 ghr_fehb_plan_design.populate_fehb_plan_design(
688 p_errbuf , p_retcode ,
689 i.business_group_id);
690 End;
691 End Loop;
692 hr_utility.set_location('Leaving :'|| l_proc, 50);
693 End populate_fehb_pd_all_bgs;
694 */
695
696
697 Procedure get_recs_for_fehb_migration(p_errbuf OUT NOCOPY Varchar2
698 ,p_retcode OUT NOCOPY Number
699 ,p_business_group_id in Number) is
700
701
702 l_option_code ben_opt_f.short_code%type;
703 l_pt_flag Varchar2(1);
704
705 Cursor c_fehb_migration is
706 SELECT eef.effective_start_date start_date,
707 eef.assignment_id,
708 elt.element_name,
709 eef.element_entry_id,
710 eef.object_version_number,
711 ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id,
712 'Enrollment',
713 eef.effective_start_date) enrollment,
714 ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id,
715 'Health Plan',
716 eef.effective_start_date) health_plan,
717 ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id,
718 'Pre tax Waiver',
719 eef.effective_start_date) pt_flag,
720 ghr_ss_views_pkg.get_ele_entry_value_ason_date (eef.element_entry_id,
721 'Temps Total Cost',
722 eef.effective_start_date) Temps_cost,
723 asg.person_id
724
725
726 FROM pay_element_entries_f eef,
727 pay_element_types_f elt,
728 per_all_assignments_f asg
729 WHERE eef.assignment_id = asg.assignment_id
730 and elt.element_type_id = eef.element_type_id
731 AND eef.effective_start_date BETWEEN elt.effective_start_date AND elt.effective_end_date
732 AND eef.effective_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
733 and eef.effective_end_date = hr_api.g_eot
734 and asg.business_group_id = p_business_group_id
735 AND upper(pqp_fedhr_uspay_int_utils.return_old_element_name(elt.element_name,
736 p_business_group_id,
737 eef.effective_start_date))
738 IN ('HEALTH BENEFITS'
739 -- ,'HEALTH BENEFITS PRE TAX'
740 )
741 order by eef.assignment_id,element_name;
742 begin
743 -- set program name
744 ghr_mto_int.set_log_program_name('GHR_FEHB_MIGRATION');
745 for fehb_migration in c_fehb_migration loop
746 l_option_code := null;
747 l_pt_flag := nvl(fehb_migration.pt_flag,'N');
748 If fehb_migration.enrollment = 'Y' Then
749 l_option_code := null;
750 ElsIf l_pt_flag = 'Y' and fehb_migration.enrollment in ('1','2','4','5') then
751 l_option_code := fehb_migration.enrollment||'A';
752 Elsif l_pt_flag = 'N' and fehb_migration.enrollment in ('1','2','4','5') then
753 l_option_code := fehb_migration.enrollment||'P';
754 Else
755 l_option_code := fehb_migration.enrollment;
756 ENd If;
757 --dbms_output.put_line('2.Option ' ||l_option_code);
758 --dbms_output.put_line('person_id ' ||fehb_migration.person_id);
759 --dbms_output.put_line('temps_total_cost ' ||fehb_migration.temps_cost);
760 ghr_general.ghr_fehb_migrate(fehb_migration.assignment_id,
761 p_business_group_id,
762 fehb_migration.person_id,
763 fehb_migration.start_date,
764 fehb_migration.health_plan,
765 l_option_code,
766 fehb_migration.element_entry_id,
767 fehb_migration.object_version_number,
768 fehb_migration.temps_cost);
769 commit;
770 End Loop;
771 end get_recs_for_fehb_migration;
772
773 end ghr_fehb_plan_design;