[Home] [Help]
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);
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:
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:
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: