[Home] [Help]
697: 115.329 07-Nov-08 sallumwa Bug 7458990: Reverted back the fix 5018328,so that the system throws the error
698: when trying to change the plan before the earliest de-enrol date.
699: 115.330 29-Jan-09 sallumwa Bug 7711723 :Whenever future certification prvdd records are found,move them to
700: back-up table and delete the same from ben_prtt_enrt_actn_f and
701: ben_prtt_enrt_ctfn_prvdd_f tables.
702: 115.332 16-Feb-09 velvanop Bug 7561395: If Coverage Amount is changed multiple times for the same Life Event, then Required
703: Certication alert should not popup. When the Benefit Amount is changed, New penid will
704: be generated for the enrollment. This causes action item and certification records
705: to be deleted of the old penid. When Benefit Amount is changed for the third time, since there are no
857:
858: --
859: cursor c_future_pcs(p_prtt_enrt_actn_id number,p_effective_date date ) is
860: select *
861: from ben_prtt_enrt_ctfn_prvdd_f
862: where prtt_enrt_actn_id = p_prtt_enrt_actn_id
863: and effective_start_date > p_effective_date
864: order by effective_start_date desc ;
865: --
1044: );
1045:
1046: end loop;
1047: --
1048: --ben_prtt_enrt_ctfn_prvdd_f
1049: --
1050: for l_lcs in c_future_pcs(l_bnf_types.prtt_enrt_actn_id,l_effective_date) loop
1051: --
1052: insert into BEN_LE_CLSN_N_RSTR (
1105: OBJECT_VERSION_NUMBER,
1106: PER_IN_LER_ID,
1107: PER_IN_LER_ENDED_ID)
1108: values (
1109: 'BEN_PRTT_ENRT_CTFN_PRVDD_F_UPD'
1110: ,l_lcs.PRTT_ENRT_CTFN_PRVDD_ID
1111: ,l_lcs.EFFECTIVE_START_DATE
1112: ,l_lcs.EFFECTIVE_END_DATE
1113: ,l_lcs.ENRT_CTFN_RQD_FLAG
7188: INDEX BY BINARY_INTEGER;
7189: l_act_items l_act_items_table;
7190:
7191: cursor c_get_ctfn is
7192: select ctfn.* from ben_prtt_enrt_ctfn_prvdd_f ctfn,ben_prtt_enrt_actn_f actn,
7193: (select max(effective_start_date) max_effective_date,prtt_enrt_actn_id,prtt_enrt_ctfn_prvdd_id
7194: from ben_prtt_enrt_ctfn_prvdd_f
7195: where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
7196: group by prtt_enrt_actn_id,prtt_enrt_ctfn_prvdd_id) max_ctfn_rec
7190:
7191: cursor c_get_ctfn is
7192: select ctfn.* from ben_prtt_enrt_ctfn_prvdd_f ctfn,ben_prtt_enrt_actn_f actn,
7193: (select max(effective_start_date) max_effective_date,prtt_enrt_actn_id,prtt_enrt_ctfn_prvdd_id
7194: from ben_prtt_enrt_ctfn_prvdd_f
7195: where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
7196: group by prtt_enrt_actn_id,prtt_enrt_ctfn_prvdd_id) max_ctfn_rec
7197: where ctfn.prtt_enrt_ctfn_prvdd_id=max_ctfn_rec.prtt_enrt_ctfn_prvdd_id
7198: and ctfn.prtt_enrt_actn_id=max_ctfn_rec.prtt_enrt_actn_id
7205: where prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
7206: and prtt_enrt_rslt_stat_cd is NULL;
7207:
7208: l_ctfn_rec c_get_ctfn%rowtype;
7209: TYPE l_ctfn_table IS TABLE OF ben_prtt_enrt_ctfn_prvdd_f%rowtype
7210: INDEX BY BINARY_INTEGER;
7211: l_ctfn l_ctfn_table;
7212:
7213: cursor c_chk_act_item_exits(c_prtt_enrt_rslt_id number) is