[Home] [Help]
174: and ptn.ptnl_ler_for_per_id <> l_winning_ptnl_rec.ptnl_ler_for_per_id;
175: --
176: cursor c_get_procd_pils_for_aba is
177: select pil.lf_evt_ocrd_dt
178: from ben_per_in_ler pil
179: where pil.person_id = p_person_id
180: and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
181: and pil.business_group_id = p_business_group_id
182: and pil.trgr_table_pk_id = l_potent.trgr_table_pk_id
183: order by pil.lf_evt_ocrd_dt asc;
184: --
185: cursor c_get_procd_pils_for_win is
186: select pil.lf_evt_ocrd_dt
187: from ben_per_in_ler pil
188: where pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
189: and pil.business_group_id = p_business_group_id
190: and pil.trgr_table_pk_id = l_winning_ptnl_rec.trgr_table_pk_id
191: and pil.ler_id = l_winning_ptnl_rec.ler_id
194: l_procd_lf_evt_ocrd_dt date;
195: --
196: cursor c_pils_to_backout(p_min_lf_evt_ocrd_dt date, p_ler_id number ) is
197: select pil.*
198: from ben_per_in_ler pil,
199: ben_ler_f ler
200: where pil.business_group_id = p_business_group_id
201: and pil.person_id = p_person_id
202: and pil.lf_evt_ocrd_dt >= p_min_lf_evt_ocrd_dt
235: -- bug
236: CURSOR c_pil_ovn (cv_per_in_ler_id IN NUMBER)
237: IS
238: SELECT object_version_number
239: FROM ben_per_in_ler
240: WHERE per_in_ler_id = cv_per_in_ler_id;
241:
242: pil_ovn_rec c_pil_ovn%ROWTYPE;
243: -- end bug
674: pil.voidd_dt,
675: pil.bckt_dt,
676: pil.clsd_dt,
677: pil.ntfn_dt
678: from ben_per_in_ler pil,
679: ben_ler_f ler
680: where pil.person_id = p_person_id
681: and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
682: and pil.lf_evt_ocrd_dt >= cv_lf_evt_ocrd_dt
699: pil.voidd_dt,
700: pil.bckt_dt,
701: pil.clsd_dt,
702: pil.ntfn_dt
703: from ben_per_in_ler pil,
704: ben_ler_f ler
705: where pil.person_id = p_person_id
706: and pil.per_in_ler_stat_cd in ('STRTD')
707: and pil.lf_evt_ocrd_dt < cv_lf_evt_ocrd_dt
983: -- Before we process GSP Rate Sync, ensure that one GSP Progression has been processed
984: -- for the person in the past
985: cursor c_gsp_prog_procd_exists is
986: select null
987: from ben_per_in_ler pil, ben_ler_f ler
988: where pil.person_id = p_person_id
989: and pil.ler_id = ler.ler_id
990: and p_effective_date between ler.effective_start_date and ler.effective_end_date
991: and ler.typ_cd = 'GSP';
991: and ler.typ_cd = 'GSP';
992: --
993: cursor get_gs_proc_strt_le(p_lf_evt_ocrd_dt date) is
994: select pil.*
995: from ben_per_in_ler pil,
996: ben_ler_f ler
997: where pil.person_id = p_person_id
998: and (pil.per_in_ler_stat_cd = 'STRTD'
999: or
1011: l_ptnl_rec ben_ptnl_ler_for_per%rowtype;
1012: l_potent get_all_potential%rowtype;
1013: l_min_ptnl get_all_potential%rowtype;
1014: l_recs_found boolean := false;
1015: l_pil_rec ben_per_in_ler%rowtype;
1016: l_curr_per_in_ler_id number;
1017: l_procd_dt date;
1018: l_strtd_dt date;
1019: l_voidd_dt date;
1315: order by pfl.lf_evt_ocrd_dt asc;
1316: --
1317: l_ptnl_rec ben_ptnl_ler_for_per%rowtype;
1318: --
1319: l_pil_rec ben_per_in_ler%rowtype;
1320: --
1321: -- Bug 3179 : pil processed or started and a ptnl is created on the
1322: -- same day. Processed or started pil is not gatting backed out.
1323: --
1322: -- same day. Processed or started pil is not gatting backed out.
1323: --
1324: cursor get_all_per_in_ler(p_lf_evt_ocrd_dt date, p_curr_per_in_ler_id number) is
1325: select pil.*
1326: from ben_per_in_ler pil,
1327: ben_ler_f ler
1328: where pil.person_id = p_person_id
1329: and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
1330: and pil.lf_evt_ocrd_dt >= p_lf_evt_ocrd_dt
1362: pil.voidd_dt,
1363: pil.bckt_dt,
1364: pil.clsd_dt,
1365: pil.ntfn_dt
1366: from ben_per_in_ler pil,
1367: ben_ler_f ler
1368: where pil.person_id = p_person_id
1369: and pil.per_in_ler_stat_cd = 'STRTD'
1370: and pil.ler_id <> ben_manage_life_events.g_ler_id
1386: pil.voidd_dt,
1387: pil.bckt_dt,
1388: pil.clsd_dt,
1389: pil.ntfn_dt
1390: from ben_per_in_ler pil,
1391: ben_ler_f ler
1392: where pil.person_id = p_person_id
1393: and pil.per_in_ler_stat_cd = 'PROCD'
1394: and pil.lf_evt_ocrd_dt >= cv_lf_evt_ocrd_dt
1422: pil.voidd_dt,
1423: pil.bckt_dt,
1424: pil.clsd_dt,
1425: pil.ntfn_dt
1426: from ben_per_in_ler pil,
1427: ben_ler_f ler
1428: where pil.person_id = p_person_id
1429: and pil.per_in_ler_stat_cd in ( 'PROCD', 'STRTD')
1430: and pil.ler_id <> ben_manage_life_events.g_ler_id
1483: --Start 6086392
1484: l_bckdt_pil_indx BINARY_INTEGER;
1485: l_bckdt_pil_count BINARY_INTEGER;
1486: l_date date;
1487: l_pil_object_version_number BEN_PER_IN_LER.OBJECT_VERSION_NUMBER%TYPE;
1488: --End 6086392
1489:
1490:
1491: -- bug
1491: -- bug
1492: CURSOR c_pil_ovn (cv_per_in_ler_id IN NUMBER)
1493: IS
1494: SELECT object_version_number
1495: FROM ben_per_in_ler
1496: WHERE per_in_ler_id = cv_per_in_ler_id;
1497:
1498: pil_ovn_rec c_pil_ovn%ROWTYPE;
1499: -- end bug
2772: -- CWB Changes : Cursor joined to ben_ler_f
2773: --
2774: cursor c_pils_to_backout is
2775: select pil.*
2776: from ben_per_in_ler pil,
2777: ben_ler_f ler
2778: where pil.business_group_id = p_business_group_id
2779: and pil.person_id = p_person_id
2780: and (pil.lf_evt_ocrd_dt > p_lf_evt_ocrd_dt -- 5727737/5677090: Need to backout all Future LEs
3589: pil.bckt_dt,
3590: pil.clsd_dt,
3591: pil.ntfn_dt,
3592: pil.ptnl_ler_for_per_id
3593: from ben_per_in_ler pil,
3594: ben_ler_f ler
3595: where pil.person_id = cv_person_id
3596: and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
3597: and pil.assignment_id = cv_assignment_id
3605: cursor c_ben_pil_elctbl_chc_popl (p_per_in_ler_id number) is
3606: select pel.pil_elctbl_chc_popl_id,
3607: pel.object_version_number
3608: from ben_pil_elctbl_chc_popl pel,
3609: ben_per_in_ler pil
3610: where pil.per_in_ler_id = p_per_in_ler_id
3611: and pil.business_group_id = p_business_group_id
3612: and pel.per_in_ler_id = pil.per_in_ler_id
3613: and pel.business_group_id = pil.business_group_id;