[Home] [Help]
696: to check for effective_start_date.
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
823: ,pea.object_version_number
824: ,pea.effective_start_date
825: ,pea.effective_end_date
826: ,pil.lf_evt_ocrd_dt
827: from ben_prtt_enrt_actn_f pea,
828: ben_per_in_ler pil
829: where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
830: and pea.business_group_id = p_business_group_id
831: and p_effective_date
836:
837: cursor c_min_max_dt(p_prtt_enrt_actn_id number) is
838: select min(effective_start_date),
839: max(effective_end_date)
840: from ben_prtt_enrt_actn_f
841: where prtt_enrt_actn_id = p_prtt_enrt_actn_id;
842: --
843: cursor c_pea_ovn(p_prtt_enrt_actn_id number,
844: p_effective_date date) is
842: --
843: cursor c_pea_ovn(p_prtt_enrt_actn_id number,
844: p_effective_date date) is
845: select pea.object_version_number
846: from ben_prtt_enrt_actn_f pea
847: where pea.prtt_enrt_actn_id = p_prtt_enrt_actn_id
848: and p_effective_date between pea.effective_start_date
849: and pea.effective_end_date ;
850: --
849: and pea.effective_end_date ;
850: --
851: cursor c_future_pea(p_prtt_enrt_actn_id number,p_effective_date date ) is
852: select *
853: from ben_prtt_enrt_actn_f
854: where prtt_enrt_actn_id = p_prtt_enrt_actn_id
855: and effective_start_date > p_effective_date
856: order by effective_start_date desc ;
857:
924: --
925: --Find if there are any future action items. If exists
926: --backup those rows.
927: --
928: --ben_prtt_enrt_actn_f
929: --
930: for l_pea in c_future_pea(l_bnf_types.prtt_enrt_actn_id,l_effective_date) loop
931: --
932: insert into BEN_LE_CLSN_N_RSTR (
985: PROGRAM_UPDATE_DATE,
986: OBJECT_VERSION_NUMBER,
987: PER_IN_LER_ENDED_ID)
988: values (
989: 'BEN_PRTT_ENRT_ACTN_F_UPD'
990: ,l_pea.PRTT_ENRT_ACTN_ID
991: ,l_pea.EFFECTIVE_START_DATE
992: ,l_pea.EFFECTIVE_END_DATE
993: ,l_pea.RQD_FLAG
7172: select object_version_number from ben_prtt_enrt_rslt_f
7173: where prtt_enrt_rslt_id = g_new_prtt_enrt_rslt_id;
7174:
7175: cursor c_get_actn_items is
7176: select actn.* from ben_prtt_enrt_actn_f actn,
7177: (select max(effective_start_date) max_effective_date,prtt_enrt_actn_id from ben_prtt_enrt_actn_f
7178: where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
7179: and per_in_ler_id = p_per_in_ler_id
7180: and cmpltd_dt is not null --Bug 8669907: Get only the completed action items for creation
7173: where prtt_enrt_rslt_id = g_new_prtt_enrt_rslt_id;
7174:
7175: cursor c_get_actn_items is
7176: select actn.* from ben_prtt_enrt_actn_f actn,
7177: (select max(effective_start_date) max_effective_date,prtt_enrt_actn_id from ben_prtt_enrt_actn_f
7178: where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
7179: and per_in_ler_id = p_per_in_ler_id
7180: and cmpltd_dt is not null --Bug 8669907: Get only the completed action items for creation
7181: group by prtt_enrt_actn_id) new_actn
7183: and new_actn.prtt_enrt_actn_id=actn.prtt_enrt_actn_id
7184: and actn.cmpltd_dt is not null;
7185:
7186: l_act_items_rec c_get_actn_items%rowtype;
7187: TYPE l_act_items_table IS TABLE OF ben_prtt_enrt_actn_f%rowtype
7188: INDEX BY BINARY_INTEGER;
7189: l_act_items l_act_items_table;
7190:
7191: cursor c_get_ctfn is
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
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
7214: select '1' from ben_prtt_enrt_actn_f actn
7215: where actn.prtt_enrt_rslt_id=c_prtt_enrt_rslt_id;
7216:
7217: /* Added for Bug 8669907 */
7218: cursor c_chk_same_comp_obj(c_new_pen_id number,c_old_pen_id number,c_per_in_ler_id number) is