[Home] [Help]
PACKAGE BODY: APPS.GHR_TSP_PLAN_DESIGN
Source
1 PACKAGE BODY ghr_tsp_plan_design AS
2 /* $Header: ghtsppd.pkb 120.2 2006/10/24 18:25:08 bgarg noship $ */
3
4 --
5 -- Package Variables
6 --
7 g_package varchar2(100) := 'ghr_tsp_plan_design.';
8
9 Procedure create_tsp_program_and_plans (p_target_business_group_id in Number) is
10 --
11 l_proc Varchar2(100);
12 p_validate Number := 0;
13 p_copy_entity_txn_id Number;
14 p_effective_date Date;
15 p_prefix_suffix_cd Varchar2(2);
16 p_prefix_suffix_text Varchar2(2);
17 p_reuse_object_flag Varchar2(1);
18 p_transaction_category_id Number(15);
19 l_effective_start_date Date;
20 l_effective_end_date Date;
21 Nothing_To_Do Exception;
22
23 --
24 Cursor get_txn_category_id is
25 select transaction_category_id
26 from pqh_transaction_categories
27 where short_name = 'BEN_PDCPWZ';
28 Cursor get_copy_txn_id is
29 select copy_entity_txn_id
30 from pqh_copy_entity_txns
31 where transaction_category_id = p_transaction_category_id
32 and context_business_group_id = 0
33 and display_name = 'GHR_TSP_SEED_PROGRAM_DESIGN';
34 Cursor update_program_status is
35 select * from ben_pgm_f
36 where name = 'Federal Thrift Savings Plan (TSP)'
37 and business_group_id = p_target_business_group_id;
38 --
39 Begin
40 l_proc := g_package||'create_tsp_program_and_plans';
41
42 hr_utility.set_location('Entering:'|| g_package||l_proc, 5);
43
44 Open get_txn_category_id;
45 Fetch get_txn_category_id into p_transaction_category_id;
46 hr_utility.trace('Transaction Category Id :'|| p_transaction_category_id);
47 hr_utility.set_location('Opening cursor get_copy_txn_id '||l_proc, 10);
48 --dbms_output.put_line('txn category id '||p_transaction_category_id);
49
50 Open get_copy_txn_id;
51 fetch get_copy_txn_id into p_copy_entity_txn_id;
52 If get_copy_txn_id%notfound then
53 Raise Nothing_to_do;
54 End If;
55 hr_utility.trace('Copy entity Txn. Id :'|| p_copy_entity_txn_id);
56 --dbms_output.put_line('copy_entity_txn_id :'||p_copy_entity_txn_id );
57
58
59 -- Set the variables
60 p_effective_date := to_date('12/31/2005','MM/DD//YYYY');
61 p_prefix_suffix_cd := null;
62 p_prefix_suffix_text := null;
63 p_reuse_object_flag := 'Y';
64
65 BEN_PD_COPY_TO_BEN_five.g_ghr_mode := 'TRUE';
66
67 --dbms_output.put_line('now calling..........');
68 BEN_PD_COPY_TO_BEN_TWO.create_stg_to_ben_rows(p_validate,
69 p_copy_entity_txn_id,
70 p_effective_date,
71 p_prefix_suffix_text,
72 p_reuse_object_flag,
73 p_target_business_group_id,
74 p_prefix_suffix_cd);
75 For i in update_program_status Loop
76 ben_Program_api.update_program(
77 p_pgm_id => i.pgm_id
78 ,p_effective_start_date => l_effective_start_date
79 ,p_effective_end_date => l_effective_end_date
80 ,p_pgm_stat_cd => 'A'
81 ,p_object_version_number => i.object_version_number
82 ,p_effective_date => i.effective_start_date
83 ,p_datetrack_mode => 'CORRECTION'
84 );
85 End Loop;
86
87 If get_txn_category_id%ISOPEN then
88 CLOSE get_txn_category_id;
89 End If;
90 If get_copy_txn_id%ISOPEN then
91 CLOSE get_copy_txn_id;
92 End If;
93 hr_utility.set_location('Leaving :'|| g_package||l_proc, 50);
94
95 Exception
96 When Nothing_to_do Then
97 If get_txn_category_id%ISOPEN then
98 CLOSE get_txn_category_id;
99 End If;
100 If get_copy_txn_id%ISOPEN then
101 CLOSE get_copy_txn_id;
102 End If;
103 null;
104 When others then
105 If get_txn_category_id%ISOPEN then
106 CLOSE get_txn_category_id;
107 End If;
108 If get_copy_txn_id%ISOPEN then
109 CLOSE get_copy_txn_id;
110 End If;
111 hr_utility.set_location('Leaving :'|| g_package||l_proc, 70);
112 Raise;
113 End create_tsp_program_and_plans;
114
115
116
117 Procedure populate_tsp_plan_design (p_errbuf OUT NOCOPY VARCHAR2,
118 p_retcode OUT NOCOPY NUMBER,
119 p_target_business_group_id in Number) is
120
121 Cursor check_pgm_exists is
122 select 'Y' from ben_pgm_f
123 where name = 'Federal Thrift Savings Plan (TSP)'
124 and business_group_id = p_target_business_group_id;
125
126 l_proc varchar2(100);
127 p_exists Varchar2(1);
128 l_err_msg Varchar2(2000);
129 Nothing_To_Do Exception;
130
131
132 Begin
133 l_proc := 'Populate_tsp_plan_design.';
134 p_exists:= 'N';
135 hr_utility.set_location('entering :'|| g_package||l_proc, 10);
136 hr_utility.trace('Business Group Id ' ||p_target_business_group_id);
137 Open check_pgm_exists;
138 Fetch check_pgm_exists into p_exists;
139 If check_pgm_exists%NOTFOUND Then
140 p_exists := 'N';
141 End If;
142 If p_exists = 'Y' then
143 Raise nothing_to_do;
144 End If;
145
146 savepoint create_tsp_plan_design;
147 --dbms_output.put_line('now starting plan design ' ||p_target_business_group_id);
148 create_tsp_program_and_plans(p_target_business_group_id);
149 hr_utility.trace('After create_program_and_plans....');
150 commit;
151 If check_pgm_exists%ISOPEN then
152 CLOSE check_pgm_exists;
153 End If;
154 hr_utility.set_location('Leaving :'|| g_package||l_proc, 50);
155 Exception
156 When Nothing_to_do Then
157 If check_pgm_exists%ISOPEN then
158 CLOSE check_pgm_exists;
159 End If;
160 null;
161 When others then
162 If check_pgm_exists%ISOPEN then
163 CLOSE check_pgm_exists;
164 End If;
165 hr_utility.set_location('Leaving :'|| g_package||l_proc, 60);
166 hr_utility.trace('Error ' ||sqlerrm(sqlcode));
167 l_err_msg := substr(p_target_business_group_id||':'||nvl(fnd_message.get,sqlerrm),1,1999) ;
168 rollback to create_tsp_plan_design;
169 ghr_wgi_pkg.create_ghr_errorlog
170 (p_program_name => l_proc,
171 p_log_text => l_err_msg,
172 p_message_name => null,
173 p_log_date => sysdate
174 );
175 commit;
176 End populate_tsp_plan_design;
177
178
179 procedure tsp_continue_coverage (
180 p_person_id in per_all_people_f.person_id%type,
181 p_business_group_id in per_business_groups.business_group_id%type,
182 p_ler_id in ben_ler_f.ler_id%type,
183 p_pgm_id in ben_pgm_f.pgm_id%type,
184 p_effective_date in Date ) is
185
186 Nothing_To_Do Exception;
187 l_err_msg varchar2(2000);
188 l_per_in_ler_id number;
189 l_exists boolean;
190 l_elig_per_elctbl_chc_id number;
191 l_term_ler_id ben_ler_f.ler_id%type;
192 l_rt_val number;
193 l_enrt_rt_id number;
194 l_asg_id number;
195 l_proc varchar2(100);
196
197 /* output Vars needed for procedure election_information */
198 l_esd date;
199 l_eed date;
200 l_prtt_enrt_interim_id number;
201 L_boolean boolean;
202
203 l_suspend_flag varchar2(30);
204 l_ovn number;
205 l_prtt_rt_val_id1 number;
206 l_prtt_rt_val_id2 number;
207 l_prtt_rt_val_id3 number;
208 l_prtt_rt_val_id4 number;
209 l_prtt_rt_val_id5 number;
210 l_prtt_rt_val_id6 number;
211 l_prtt_rt_val_id7 number;
212 l_prtt_rt_val_id8 number;
213 l_prtt_rt_val_id9 number;
214 l_prtt_rt_val_id10 number;
215 l_prtt_enrt_rslt_id number;
216
217 l_enrt_bnft_id number;
218 l_prtt_rt_val_id number;
219
220 /* output Vars needed for procedure p_evt_lf_evts_from benauthe */
221 l_prog_count number;
222 l_plan_count number;
223 l_oipl_count number;
224 l_person_count number;
225 l_plan_nip_count number;
226 l_oipl_nip_count number;
227 l_ler_id number;
228 l_retcode number;
229 l_errbuf varchar2(2000);
230
231 /* output Vars needed for procedure p_proc_lf_evts_from benauthe */
232 l_benefit_action_id number;
233
234 -- to get old Employee contribution eligibility date
235 l_element_name Varchar2(50);
236 l_input_value_name Varchar2(50);
237
238 --Out vars.
239 l_effective_start_date Date;
240 l_effective_end_date Date;
241 l_elig_dt Date;
242 l_element_entry_id Number;
243 l_object_version_number Number;
244 l_process_warning Boolean;
245 --
246 l_val Varchar2(50);
247 l_multi_error_flag Boolean;
248 l_old_asg_id Number;
249 l_new_asg_id Number;
250
251
252
253
254 -- Cursor to get Pgm_id for the given Business Group
255 Cursor c_get_pgm_id is
256 select pgm.pgm_id
257 from ben_pgm_f pgm
258 where pgm.name = 'Federal Thrift Savings Plan (TSP)'
259 and pgm.business_group_id = p_business_group_id;
260
261 -- Cursor to get ler id for "Termination of Appointment"
262 Cursor c_get_ler_id is
263 select ler_id
264 from ben_ler_f
265 where name = 'Termination of Appointment'
266 and business_group_id = p_business_group_id
267 and trunc(p_effective_date) between effective_start_date and effective_end_date;
268
269
270 -- Cursor to check if Employee has any open Benefits record for TSP
271 Cursor c_emp_in_tsp is
272 select 1
273 from ben_prtt_enrt_rslt_f
274 where person_id = p_person_id
275 and business_group_id = p_business_group_id
276 and pgm_id = p_pgm_id
277 and enrt_cvg_thru_dt = hr_api.g_eot
278 and trunc(p_effective_date) between effective_start_date and effective_end_date
279 and prtt_enrt_rslt_stat_cd is null;
280
281 -- Cursor to get last TSP benefit record for the person
282 Cursor c_get_ee_last_tsp_enrt_rec is
283 select pl_id,pl_typ_id,ptip_id,prtt_enrt_rslt_id,oipl_id
284 from ben_prtt_enrt_rslt_f
285 where person_id = p_person_id
286 and business_group_id = p_business_group_id
287 and pgm_id = p_pgm_id
288 and ler_id = l_term_ler_id
289 order by prtt_enrt_rslt_id desc;
290
291 get_ee_last_tsp_enrt_rec c_get_ee_last_tsp_enrt_rec%rowtype;
292
293 Cursor c_get_prtt_rt_val is
294 select rt_val
295 from ben_prtt_rt_val
296 where prtt_enrt_rslt_id = get_ee_last_tsp_enrt_rec.prtt_enrt_rslt_id
297 order by prtt_rt_val_id desc;
298
299 Cursor c_get_elig_chc_id is
300 select elig_per_elctbl_chc_id,
301 pil.per_in_ler_id
302 from ben_elig_per_ELCTBL_chc chc ,
303 ben_per_in_ler pil
304 where chc.pgm_id = p_pgm_id
305 and chc.pl_typ_id = get_ee_last_tsp_enrt_rec.pl_typ_id
306 and chc.pl_id = get_ee_last_tsp_enrt_rec.pl_id
307 and chc.oipl_id = get_ee_last_tsp_enrt_rec.oipl_id
308 --and chc.plip_id = l_plip_id
309 and chc.ptip_id = get_ee_last_tsp_enrt_rec.ptip_id
310 and pil.per_in_ler_id = chc.per_in_ler_id
311 and pil.ler_id = p_ler_id
312 and pil.person_id = p_person_id;
313
314 Cursor c_get_enrt_rt_id is
315 select enrt_rt_id
316 from ben_enrt_rt
317 where elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id;
318
319 Cursor c_get_prev_asg_id is
320 select assignment_id
321 from per_all_assignments_f
322 where person_id = p_person_id
323 and trunc (p_effective_date - 1) between effective_start_date and effective_end_date
324 and assignment_type = 'E';
325
326 BEGIN
327 --dbms_output.put_line(' start ' ||p_effective_date);
328 l_proc := 'TSP Cont. Coverage';
329 l_element_name := 'TSP';
330 l_input_value_name := 'Emp Contrib Elig Date';
331 hr_utility.set_location('entering :'|| g_package||l_proc, 10);
332 hr_utility.trace('Person Id ' ||p_person_id);
333 l_err_msg := null;
334
335 --Check if any current open record exists in the Ben table for this person and program
336 hr_utility.set_location(l_proc, 20);
337 l_exists := FALSE;
338 for emp_in_tsp in c_emp_in_tsp loop
339 --dbms_output.put_line('Data exists in Ben Table' );
340 l_exists := TRUE;
341 exit;
342 end loop;
343 If l_exists then
344 --dbms_output.put_line('No Action' );
345 l_err_msg := 'Person Id ' ||p_person_id ||' has open Benefits Enrollment record';
346 Raise Nothing_to_do;
347 End If;
348
349 -- Get Ler Id for 'Termination of Appointment' life event
350 hr_utility.set_location(l_proc, 30);
351 For get_ler_id in c_get_ler_id loop
352 l_term_ler_id := get_ler_id.ler_id;
353 Exit;
354 End loop;
355
356 hr_utility.set_location(l_proc, 40);
357 open c_get_ee_last_tsp_enrt_rec;
358 fetch c_get_ee_last_tsp_enrt_rec into get_ee_last_tsp_enrt_rec;
359 --dbms_output.put_line('Plan id :' ||get_ee_last_tsp_enrt_rec.pl_id||'...pl_typ_id: '||get_ee_last_tsp_enrt_rec.pl_typ_id);
360 If get_ee_last_tsp_enrt_rec.pl_id is null Then
361 --dbms_output.put_line(' Person was not enrolled in TSP before');
362 l_err_msg := 'Person Id ' ||p_person_id ||' : No previous TSP record to Copy';
363 Raise Nothing_to_do;
364 End If;
365
366 If c_get_ee_last_tsp_enrt_rec%ISOPEN then Close c_get_ee_last_tsp_enrt_rec; End If;
367
368 For get_prtt_rt_val in c_get_prtt_rt_val Loop
369 l_rt_val := get_prtt_rt_val.rt_val;
370 exit;
371 End Loop;
372
373 hr_utility.set_location(l_proc, 50);
374 --dbms_output.put_line('Rate Value :' ||l_rt_val);
375
376 -- Run Process Life Events for the EE
377 --dbms_output.put_line('Before calling p_proc_lf_evts_from_benauthe 1');
378 ben_on_line_lf_evt.p_evt_lf_evts_from_benauthe(
379 p_person_id => p_person_id
380 ,p_effective_date => p_effective_date
381 ,p_business_group_id => p_business_group_id
382 ,p_pgm_id => p_pgm_id
383 ,p_pl_id => null
384 ,p_mode => 'L'
385 ,p_popl_enrt_typ_cycl_id => null
386 ,p_lf_evt_ocrd_dt => p_effective_date
387 ,p_prog_count => l_prog_count
388 ,p_plan_count => l_plan_count
389 ,p_oipl_count => l_oipl_count
390 ,p_person_count => l_person_count
391 ,p_plan_nip_count => l_plan_nip_count
392 ,p_oipl_nip_count => l_oipl_nip_count
393 ,p_ler_id => l_ler_id
394 ,p_errbuf => l_errbuf
395 ,p_retcode => l_retcode);
396 --
397 --dbms_output.put_line('Before calling p_proc_lf_evts_from_benauthe 2');
398 hr_utility.set_location(l_proc, 60);
399 ben_on_line_lf_evt.p_proc_lf_evts_from_benauthe(
400 p_person_id => p_person_id
401 ,p_effective_date => p_effective_date
402 ,p_business_group_id => p_business_group_id
403 ,p_mode => 'L'
404 ,p_ler_id => l_ler_id
405 ,p_person_count => l_person_count
406 ,p_benefit_action_id => l_benefit_action_id
407 ,p_errbuf => l_errbuf
408 ,p_retcode => l_retcode);
409 --
410 --dbms_output.put_line('After calling p_proc_lf_evts_from_benauthe 2');
411 hr_utility.set_location(l_proc, 70);
412 open c_get_elig_chc_id;
413 fetch c_get_elig_chc_id into l_elig_per_elctbl_chc_id,l_per_in_ler_id;
414 If c_get_elig_chc_id%NOTFOUND then
415 l_err_msg := 'Person_ID '|| p_person_id||':No Electable Choice found for this person' ;
416 --dbms_output.put_line('NO Electable choice id ');
417 Raise Nothing_to_do;
418 End If;
419 --dbms_output.put_line('Electable choice id ' || l_elig_per_elctbl_chc_id);
420 --dbms_output.put_line('PER in LER ID ' || l_per_in_ler_id);
421
422
423 hr_utility.set_location(l_proc, 80);
424 open c_get_enrt_rt_id;
425 fetch c_get_enrt_rt_id into l_enrt_rt_id;
426 If c_get_enrt_rt_id%NOTFOUND then
427 l_err_msg := 'Person_ID '|| p_person_id||':No Electable Rate found for this person' ;
428 --dbms_output.put_line('NO Electable enrollment Rate id ');
429 Raise Nothing_to_do;
430 End If;
431 --dbms_output.put_line('Electable rate id ' || l_enrt_rt_id||':'||p_effective_date);
432
433 hr_utility.set_location(l_proc, 90);
434 ben_election_information.election_information
435 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
436 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
437 ,p_effective_date => p_effective_date
438 ,p_enrt_mthd_cd => 'E'
439 ,p_enrt_bnft_id => l_enrt_bnft_id
440 ,p_enrt_rt_id1 => l_enrt_rt_id
441 ,p_rt_val1 => l_rt_val
442 ,p_prtt_rt_val_id1 => l_prtt_rt_val_id1
443 ,p_prtt_rt_val_id2 => l_prtt_rt_val_id2
444 ,p_prtt_rt_val_id3 => l_prtt_rt_val_id3
445 ,p_prtt_rt_val_id4 => l_prtt_rt_val_id4
446 ,p_prtt_rt_val_id5 => l_prtt_rt_val_id5
447 ,p_prtt_rt_val_id6 => l_prtt_rt_val_id6
448 ,p_prtt_rt_val_id7 => l_prtt_rt_val_id7
449 ,p_prtt_rt_val_id8 => l_prtt_rt_val_id8
450 ,p_prtt_rt_val_id9 => l_prtt_rt_val_id9
451 ,p_prtt_rt_val_id10 => l_prtt_rt_val_id10
452 ,p_enrt_cvg_strt_dt => p_effective_date
453 ,p_enrt_cvg_thru_dt => hr_api.g_eot
454 ,p_datetrack_mode => 'INSERT'
455 ,p_suspend_flag => l_suspend_flag
456 ,p_effective_start_date => l_esd
457 ,p_effective_end_date => l_eed
458 ,p_object_version_number => l_ovn
459 ,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
460 ,p_business_group_id => p_business_group_id
461 ,p_dpnt_actn_warning => l_Boolean
462 ,p_bnf_actn_warning => l_Boolean
463 ,p_ctfn_actn_warning => l_Boolean
464 );
465 --dbms_output.put_line('NOw calling ben-proc_common_enrt_rslt.post_enrt');
466 hr_utility.set_location(l_proc, 100);
467 ben_proc_common_enrt_rslt.process_post_enrt_calls_w
468 (p_validate => 'N'
469 ,p_person_id => p_person_id
470 ,p_per_in_ler_id => l_per_in_ler_id
471 ,p_pgm_id => p_pgm_id
472 ,p_pl_id => get_ee_last_tsp_enrt_rec.pl_id
473 ,p_flx_cr_flag => 'N'
474 ,p_enrt_mthd_cd => 'E'
475 ,p_proc_cd => null
476 ,p_cls_enrt_flag => 'Y'
477 ,p_business_group_id => p_business_group_id
478 ,p_effective_date => to_char(p_effective_date,'YYYY/MM/DD'));
479 --
480 If c_get_elig_chc_id%ISOPEN Then
481 close c_get_elig_chc_id;
482 End If;
483 --dbms_output.put_line('Enrollment Result id ' || l_prtt_enrt_rslt_id);
484 --End if;
485 /*
486 -- Read value for input value "Emp Contrib Elig Date" for the previous assignment
487 -- and update new element with the date (if it not null)
488
489 for get_prev_asg_id in c_get_prev_asg_id loop
490 l_old_asg_id := get_prev_asg_id.assignment_id;
491 exit;
492 End Loop;
493 ghr_api.retrieve_element_entry_value
494 (p_element_name => l_element_name
495 ,p_input_value_name => l_input_value_name
496 ,p_assignment_id => l_old_asg_id
497 ,p_effective_date => p_effective_date
498 ,p_value => l_val
499 ,p_multiple_error_flag => l_multi_error_flag);
500 If l_val is not null Then
501 l_elig_dt := to_date(substr(l_val,1,10),'yyyy/mm/dd');
502 ghr_element_api.process_sf52_element
503 (p_assignment_id => l_new_asg_id
504 ,p_element_name => l_element_name
505 ,p_input_value_name1 => l_input_value_name
506 ,p_value1 => l_elig_dt
507 ,p_effective_date => p_effective_date
508 ,p_process_warning => l_process_warning
509 );
510
511 End If;
512 */
513 hr_utility.set_location(l_proc, 200);
514 hr_utility.set_location('leaving :'|| g_package||l_proc, 210);
515 Exception
516 When Nothing_to_do Then
517 hr_utility.set_location('Exception' ||l_proc, 300);
518 If c_get_elig_chc_id%ISOPEN Then
519 close c_get_elig_chc_id;
520 End If;
521 rollback;
522 ghr_wgi_pkg.create_ghr_errorlog
523 (p_program_name => l_proc||'-'||to_char(sysdate,'DD-MON-YYYY'),
524 p_log_text => l_err_msg,
525 p_message_name => null,
526 p_log_date => sysdate
527 );
528 --dbms_output.put_line('Data Issue... Nothing_to_do! ');
529 When others then
530 hr_utility.set_location('Exception' ||l_proc, 310);
531 --dbms_output.put_line('Script Failed. Contact Your System Administrator.! ');
532 If c_get_elig_chc_id%ISOPEN then
533 close c_get_elig_chc_id;
534 End If;
535 rollback;
536 ghr_wgi_pkg.create_ghr_errorlog
537 (p_program_name => l_proc||'-'||to_char(sysdate,'DD-MON-YYYY'),
538 p_log_text => 'PERSON ID:'||p_person_id || ' ' || sqlerrm(sqlcode),
539 p_message_name => null,
540 p_log_date => sysdate
541 );
542 End tsp_continue_coverage;
543
544
545 Procedure tsp_continue_coverage_cp(p_errbuf OUT NOCOPY VARCHAR2,
546 p_retcode OUT NOCOPY NUMBER) is
547
548 l_pgm_id ben_pgm_f.pgm_id%type;
549 l_err_msg varchar2(2000);
550 l_business_group_id number;
551 l_ler_id ben_ler_f.ler_id%type;
552 l_person_id Number;
553 l_effective_date Date;
554 l_proc Varchar2(100) ;
555
556 Nothing_to_do Exception;
557
558
559 -- Cursor to get Pgm_id for the given Business Group
560 Cursor c_get_pgm_id is
561 select pgm.pgm_id
562 from ben_pgm_f pgm
563 where pgm.name = 'Federal Thrift Savings Plan (TSP)'
564 and pgm.business_group_id = l_business_group_id;
565
566 -- Cursor to get potential records
567 Cursor c_get_ptnl_emps is
568 select person_id,business_group_id,ler_id,lf_evt_ocrd_dt
569 from ben_ptnl_ler_for_per
570 where ler_id in (select ler_id from ben_ler_f where name = 'TSP Continuation of Coverage'
571 and sysdate between effective_start_date and effective_end_date)
572 and ptnl_ler_for_per_stat_cd = 'UNPROCD'
573 and lf_evt_ocrd_dt <= sysdate;
574
575 BEGIN
576 --dbms_output.put_line(' start ' );
577 l_proc := 'TSP_Cont_Cvg_CP';
578 hr_utility.set_location('Entering' ||l_proc, 100);
579 l_err_msg := null;
580
581 -- get all potential employees
582 for get_ptnl_emps in c_get_ptnl_emps Loop
583 Begin
584 hr_utility.set_location(l_proc, 110);
585 l_person_id := get_ptnl_emps.person_id;
586 l_business_group_id := get_ptnl_emps.business_group_id;
587 l_ler_id := get_ptnl_emps.ler_id;
588 l_effective_date := get_ptnl_emps.lf_evt_ocrd_dt;
589 hr_utility.trace('Person id ' ||l_person_id||' BG ID : '||l_business_group_id);
590 hr_utility.trace('ler id ' ||l_ler_id||' EFf DT : '||l_effective_date);
591
592 -- Get Program Id
593 for pgm_rec in c_get_pgm_id loop
594 l_pgm_id := pgm_rec.pgm_id;
595 --dbms_output.put_line('pgm id'|| ' ' ||l_pgm_id );
596 end loop;
597 If l_pgm_id is null Then
598 --dbms_output.put_line ('NO program found ');
599 l_err_msg := ':No Program defined in this BG ' ||l_business_group_id;
600 Raise Nothing_to_do;
601 End If;
602
603 --call tsp_contine_coverage procedure
604 tsp_continue_coverage(l_person_id,
605 l_business_group_id,
606 l_ler_id,
607 l_pgm_id,
608 l_effective_date);
609
610 exception
611 When Nothing_to_do Then
612 --rollback;
613 ghr_wgi_pkg.create_ghr_errorlog
614 (p_program_name => l_proc||'-'||to_char(sysdate,'DD-MON-YYYY'),
615 p_log_text => l_err_msg,
616 p_message_name => null,
617 p_log_date => sysdate
618 );
619 when others then
620 null;
621 end;
622 End Loop;
623
624 End tsp_continue_coverage_cp;
625
626
627 Procedure get_recs_for_tsp_migration(p_errbuf OUT NOCOPY Varchar2
628 ,p_retcode OUT NOCOPY Number
629 ,p_business_group_id in Number) is
630
631
632 Cursor c_emp_tsp(c_business_group_id in number, c_element_name in pay_element_types_f.element_name%type) is
633 select
634 e.assignment_id assignment_id,
635 decode(name,'Rate','Percentage',
636 'Status','Terminate Contributions',
637 name) Name,
638 decode (screen_entry_value,'T',0,'S',0,screen_entry_value) screen_entry_value,
639 e.effective_start_date,
640 g.person_id
641 from pay_element_types_f a,
642 pay_input_values_f b,
643 pay_element_links_f c,
644 pay_link_input_values_f d,
645 pay_element_entries_f e,
646 pay_element_entry_values_f f,
647 per_all_assignments_f g
648 where a.element_type_id = b.element_type_id
649 and a.element_type_id = c.element_type_id
650 and c.element_link_id = d.element_link_id
651 and b.input_value_id = d.input_value_id
652 and e.element_link_id = c.element_link_id
653 and f.element_entry_id = e.element_entry_id
654 and f.input_value_id = b.input_value_id
655 and g.business_group_id = c_business_group_id
656 and e.effective_end_date = hr_api.g_eot
657 --and trunc(sysdate) between e.effective_start_date and e.effective_End_date
658 and trunc(e.effective_start_date) between f.effective_start_date and f.effective_End_date
659 and g.assignment_id = e.assignment_id
660 and trunc(e.effective_start_date) between g.effective_start_date and g.effective_end_date
661 --and a.element_name = 'TSP'
662 and a.element_name = c_element_name
663 and ( (b.name in ('Rate','Amount') and (ghr_general.return_number(screen_entry_value) > 0))
664 or (b.name = 'Status' and screen_entry_value in ('T','S')))
665 order by 1, 2 desc ;
666
667 l_element_name pay_element_types_f.element_name%type;
668
669 BEGIN
670 -- 11.5.10 Performance Changes
671 l_element_name := NVL(UPPER(pqp_fedhr_uspay_int_utils.return_new_element_name ('TSP',p_business_group_id,sysdate,NULL)),'$Sys_Def$');
672
673 -- set program name
674 ghr_mto_int.set_log_program_name('GHR_TSP_MIGRATION');
675 for emp_rec in c_emp_tsp(p_business_group_id,l_element_name) loop
676 ghr_general.ghr_tsp_migrate(emp_rec.assignment_id,
677 emp_rec.name,
678 emp_rec.screen_entry_value,
679 emp_rec.effective_start_date,
680 p_business_group_id,
681 emp_rec.person_id);
682 commit;
683 end loop;
684 End get_recs_for_tsp_migration;
685 end ghr_tsp_plan_design;