[Home] [Help]
305: 27-feb-01 thayden 115.140 - change unrestricted enrolment period end date to end of time.
306: 08-jun-01 kmahendr 115.141 - Bug#1811636 - For level jumping logical order number is used
307: in place of database ordr_num
308: 08-jun-01 kmahendr 115.142 - Effective date condition added to cursor c_opt_level
309: 26-jul-01 ikasire 115.143 bug1895874 added new nip_dflt_flag to ben_pl_f table.
310: In the internal procedure determine_dflt_enrt_cd
311: added to parameters p_pgm_rec,p_ptip_rec,p_plip_rec,
312: p_oipl_rec,p_pl_rec to get the data from the already
313: cached records. Also removed the calls to ben_cobj_cache
554: ,p_oipl_rec in ben_oipl_f%rowtype
555: ,p_plip_id in number
556: ,p_plip_rec in ben_plip_f%rowtype
557: ,p_pl_id in number
558: ,p_pl_rec in ben_pl_f%rowtype
559: ,p_ptip_id in number
560: ,p_ptip_rec in ben_ptip_f%rowtype
561: ,p_pgm_id in number
562: ,p_pgm_rec in ben_pgm_f%rowtype
631: CURSOR c_pl_nip_dflt_cd IS
632: SELECT pln.nip_dflt_enrt_cd,
633: pln.nip_dflt_enrt_det_rl,
634: pln.nip_dflt_flag
635: FROM ben_pl_f pln
636: WHERE p_pl_id = pln.pl_id
637: AND p_effective_date BETWEEN pln.effective_start_date
638: AND pln.effective_end_date;
639: --
654: AND ptp.effective_end_date;
655: --
656: l_dflt_enrt_cd VARCHAR2(30);
657: l_dflt_enrt_rl NUMBER;
658: l_plan_rec ben_pl_f%ROWTYPE;
659: l_oipl_rec ben_cobj_cache.g_oipl_inst_row;
660: l_ler_dflt_flag varchar2(30); -- 3510229
661: BEGIN
662: --
678: -- 5 if pgm ben_ler_chg_pgm_enrt_f
679: --
680: -- 6 if oipl ben_oipl_f (from cache)
681:
682: -- 7 if no pgm ben_pl_f
683: -- 8 if pgm ben_plip_f
684: -- 9 if pgm ben_ptip_f
685: --
686: --End Bug 6061856
1442: --
1443: -- --
1444: CURSOR c_enrolled_opts_in_pl_typ IS
1445: SELECT pen.enrt_cvg_strt_dt enrt_cvg_strt_dt
1446: FROM ben_prtt_enrt_rslt_f pen, ben_pl_f pl, ben_oipl_f oipl
1447: WHERE pen.person_id = p_person_id
1448: AND pen.business_group_id = p_business_group_id
1449: AND pen.effective_end_date = hr_api.g_eot
1450: AND l_lf_evt_ocrd_dt_1 BETWEEN pen.enrt_cvg_strt_dt
1473: --
1474: CURSOR c_cvrd_opts_in_pl_typ IS
1475: SELECT pen.enrt_cvg_strt_dt enrt_cvg_strt_dt
1476: FROM ben_prtt_enrt_rslt_f pen,
1477: ben_pl_f pl,
1478: ben_oipl_f oipl,
1479: ben_elig_cvrd_dpnt_f pdp
1480: WHERE pdp.dpnt_person_id = p_person_id
1481: AND l_lf_evt_ocrd_dt_1 BETWEEN pdp.cvg_strt_dt AND pdp.cvg_thru_dt
1606:
1607: -- Bug#2108168 - Check for current enrollment in a waive plan
1608: CURSOR c_ptip_waive_enrolment_info IS
1609: SELECT pen.enrt_cvg_strt_dt
1610: FROM ben_prtt_enrt_rslt_f pen, ben_pl_f pln
1611: WHERE pen.person_id = p_person_id
1612: AND pen.business_group_id = p_business_group_id
1613: AND pen.prtt_enrt_rslt_stat_cd IS NULL
1614: AND pen.sspndd_flag = 'N'
2535: --l_overide_enrt_cvg_strt_dt_rl number;
2536: l_pl_id NUMBER;
2537: l_prereq_electable_flag VARCHAR2(30);
2538: l_oipl_name ben_opt_f.name%TYPE;
2539: l_pl_name ben_pl_f.name%TYPE;
2540: l_ler_name ben_ler_f.name%TYPE;
2541: l_must_enrl_anthr_pl_id NUMBER;
2542: l_auto_enrt_flag VARCHAR2(30) := 'N';
2543: l_rec_auto_enrt_flag VARCHAR2(30) := 'N';
2680: l_mn_ordr_num ben_oipl_f.ordr_num%TYPE;
2681: l_mx_ordr_num ben_oipl_f.ordr_num%TYPE;
2682: l_enrd_ordr_num NUMBER;
2683: l_level VARCHAR2(30);
2684: l_plan_rec ben_pl_f%ROWTYPE;
2685: l_pgm_rec ben_pgm_f%ROWTYPE;
2686: l_oipl_rec ben_cobj_cache.g_oipl_inst_row;
2687: l_pen_rec ben_prtt_enrt_rslt_f%ROWTYPE;
2688: l_pl_typ_esd DATE;
3339: pl.bnft_or_option_rstrctn_cd,
3340: lbr.cvg_incr_r_decr_only_cd,
3341: lbr.mx_cvg_mlt_incr_num,
3342: lbr.mx_cvg_mlt_incr_wcf_num
3343: FROM ben_oipl_f oipl, ben_ler_bnft_rstrn_f lbr, ben_pl_f pl
3344: WHERE pl.pl_id = l_pl_id
3345: AND pl.business_group_id = p_business_group_id
3346: AND l_lf_evt_ocrd_dt BETWEEN pl.effective_start_date
3347: AND pl.effective_end_date
3387: pl.bnft_or_option_rstrctn_cd,
3388: pl.cvg_incr_r_decr_only_cd,
3389: pl.mx_cvg_mlt_incr_num,
3390: pl.mx_cvg_mlt_incr_wcf_num
3391: FROM ben_oipl_f oipl, ben_pl_f pl
3392: WHERE pl.pl_id = l_pl_id
3393: AND pl.bnft_or_option_rstrctn_cd = 'OPT'
3394: AND pl.business_group_id = p_business_group_id
3395: AND l_lf_evt_ocrd_dt BETWEEN pl.effective_start_date
3526: ;
3527: --
3528: cursor c_plan_requery(p_id number,p_ed date) is
3529: select 'Y'
3530: from ben_pl_f plan
3531: where pl_id=p_id and
3532: business_group_id=p_business_group_id and
3533: p_ed between effective_start_date and effective_end_date
3534: ;
8642: --
8643: CURSOR c_pl_nip_dflt_cd IS
8644: SELECT pln.nip_dflt_enrt_cd,
8645: pln.nip_dflt_enrt_det_rl
8646: FROM ben_pl_f pln
8647: WHERE p_pl_id = pln.pl_id
8648: AND p_effective_date BETWEEN pln.effective_start_date
8649: AND pln.effective_end_date;
8650: --
8665: AND ptp.effective_end_date;
8666: --
8667: l_dflt_enrt_cd VARCHAR2(30);
8668: l_dflt_enrt_rl NUMBER;
8669: l_plan_rec ben_pl_f%ROWTYPE;
8670: l_oipl_rec ben_cobj_cache.g_oipl_inst_row;
8671: l_pl_rec ben_cobj_cache.g_pl_inst_row;
8672: BEGIN
8673: g_debug := hr_utility.debug_enabled;
8687: -- 4.5 if pgm ben_ler_chg_pl_nip_enrt_f
8688: -- 5 if pgm ben_ler_chg_ptip_enrt_f
8689: -- 6 if pgm ben_ler_chg_pgm_enrt_f
8690: --
8691: -- 7 if no pgm ben_pl_f
8692: -- 8 if pgm ben_plip_f
8693: -- 9 if pgm ben_ptip_f
8694: --
8695: IF p_oipl_id IS NOT NULL THEN
8831: ,p_oipl_rec in ben_oipl_f%rowtype
8832: ,p_plip_id in number
8833: ,p_plip_rec in ben_plip_f%rowtype
8834: ,p_pl_id in number
8835: ,p_pl_rec in ben_pl_f%rowtype
8836: ,p_ptip_id in number
8837: ,p_ptip_rec in ben_ptip_f%rowtype
8838: ,p_pgm_id in number
8839: ,p_pgm_rec in ben_pgm_f%rowtype
8908: CURSOR c_pl_nip_dflt_cd IS
8909: SELECT pln.nip_dflt_enrt_cd,
8910: pln.nip_dflt_enrt_det_rl,
8911: pln.nip_dflt_flag
8912: FROM ben_pl_f pln
8913: WHERE p_pl_id = pln.pl_id
8914: AND p_effective_date BETWEEN pln.effective_start_date
8915: AND pln.effective_end_date;
8916: --
8931: AND ptp.effective_end_date;
8932: --
8933: l_dflt_enrt_cd VARCHAR2(30);
8934: l_dflt_enrt_rl NUMBER;
8935: l_plan_rec ben_pl_f%ROWTYPE;
8936: l_oipl_rec ben_cobj_cache.g_oipl_inst_row;
8937: l_ler_dflt_flag varchar2(30); -- 3510229
8938: BEGIN
8939: hr_utility.set_location('Entering: ' || l_proc, 10);
8949: -- 4 if pgm ben_ler_chg_plip_enrt_f
8950: -- 5 if pgm ben_ler_chg_ptip_enrt_f
8951: -- 6 if pgm ben_ler_chg_pgm_enrt_f
8952: --
8953: -- 7 if no pgm ben_pl_f
8954: -- 8 if pgm ben_plip_f
8955: -- 9 if pgm ben_ptip_f
8956: --
8957: IF p_oipl_id IS NOT NULL THEN
9252: SELECT '3',
9253: bp.rqd_perd_enrt_nenrt_uom,
9254: bp.rqd_perd_enrt_nenrt_val,
9255: bp.rqd_perd_enrt_nenrt_rl
9256: FROM ben_pl_f bp
9257: WHERE bp.pl_id = p_pl_id
9258: AND p_effective_date BETWEEN bp.effective_start_date
9259: AND bp.effective_end_date
9260: AND bp.business_group_id = p_business_group_id
9462: -- Gets the enrolment information for the opt
9463: --
9464: CURSOR c_opt_enrolment_info IS
9465: SELECT pen.*
9466: FROM ben_prtt_enrt_rslt_f pen, ben_pl_f pl, ben_oipl_f oipl
9467: WHERE pen.person_id = p_person_id
9468: AND pen.business_group_id = p_business_group_id
9469: AND pen.effective_end_date = hr_api.g_eot
9470: AND l_effective_date_1 BETWEEN pen.enrt_cvg_strt_dt
9549: l_dflt_flag varchar2(30);
9550: l_use_dflt_flag varchar2(30);
9551: l_oipl_rec ben_oipl_f%rowtype;
9552: l_empty_oipl ben_oipl_f%rowtype;
9553: l_plan_rec ben_pl_f%rowtype;
9554: l_pgm_rec ben_pgm_f%rowtype;
9555: l_empty_pgm ben_pgm_f%rowtype;
9556: l_plip_rec ben_plip_f%rowtype;
9557: l_empty_plip ben_plip_f%rowtype;
9587: from ben_elig_per_elctbl_chc epe,
9588: /*
9589: ben_per_in_ler pil,
9590: */
9591: ben_pl_f pln
9592: where epe.auto_enrt_flag = 'N'
9593: and epe.per_in_ler_id = c_per_in_ler_id
9594: /*
9595: and pil.person_id = p_person_id