[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
581: ,p_oipl_rec in ben_oipl_f%rowtype
582: ,p_plip_id in number
583: ,p_plip_rec in ben_plip_f%rowtype
584: ,p_pl_id in number
585: ,p_pl_rec in ben_pl_f%rowtype
586: ,p_ptip_id in number
587: ,p_ptip_rec in ben_ptip_f%rowtype
588: ,p_pgm_id in number
589: ,p_pgm_rec in ben_pgm_f%rowtype
658: CURSOR c_pl_nip_dflt_cd IS
659: SELECT pln.nip_dflt_enrt_cd,
660: pln.nip_dflt_enrt_det_rl,
661: pln.nip_dflt_flag
662: FROM ben_pl_f pln
663: WHERE p_pl_id = pln.pl_id
664: AND p_effective_date BETWEEN pln.effective_start_date
665: AND pln.effective_end_date;
666: --
681: AND ptp.effective_end_date;
682: --
683: l_dflt_enrt_cd VARCHAR2(30);
684: l_dflt_enrt_rl NUMBER;
685: l_plan_rec ben_pl_f%ROWTYPE;
686: l_oipl_rec ben_cobj_cache.g_oipl_inst_row;
687: l_ler_dflt_flag varchar2(30); -- 3510229
688: BEGIN
689: --
705: -- 5 if pgm ben_ler_chg_pgm_enrt_f
706: --
707: -- 6 if oipl ben_oipl_f (from cache)
708:
709: -- 7 if no pgm ben_pl_f
710: -- 8 if pgm ben_plip_f
711: -- 9 if pgm ben_ptip_f
712: --
713: --End Bug 6061856
1469: --
1470: -- --
1471: CURSOR c_enrolled_opts_in_pl_typ IS
1472: SELECT pen.enrt_cvg_strt_dt enrt_cvg_strt_dt
1473: FROM ben_prtt_enrt_rslt_f pen, ben_pl_f pl, ben_oipl_f oipl
1474: WHERE pen.person_id = p_person_id
1475: AND pen.business_group_id = p_business_group_id
1476: AND pen.effective_end_date = hr_api.g_eot
1477: AND l_lf_evt_ocrd_dt_1 BETWEEN pen.enrt_cvg_strt_dt
1500: --
1501: CURSOR c_cvrd_opts_in_pl_typ IS
1502: SELECT pen.enrt_cvg_strt_dt enrt_cvg_strt_dt
1503: FROM ben_prtt_enrt_rslt_f pen,
1504: ben_pl_f pl,
1505: ben_oipl_f oipl,
1506: ben_elig_cvrd_dpnt_f pdp
1507: WHERE pdp.dpnt_person_id = p_person_id
1508: AND l_lf_evt_ocrd_dt_1 BETWEEN pdp.cvg_strt_dt AND pdp.cvg_thru_dt
1633:
1634: -- Bug#2108168 - Check for current enrollment in a waive plan
1635: CURSOR c_ptip_waive_enrolment_info IS
1636: SELECT pen.enrt_cvg_strt_dt
1637: FROM ben_prtt_enrt_rslt_f pen, ben_pl_f pln
1638: WHERE pen.person_id = p_person_id
1639: AND pen.business_group_id = p_business_group_id
1640: AND pen.prtt_enrt_rslt_stat_cd IS NULL
1641: AND pen.sspndd_flag = 'N'
2562: --l_overide_enrt_cvg_strt_dt_rl number;
2563: l_pl_id NUMBER;
2564: l_prereq_electable_flag VARCHAR2(30);
2565: l_oipl_name ben_opt_f.name%TYPE;
2566: l_pl_name ben_pl_f.name%TYPE;
2567: l_ler_name ben_ler_f.name%TYPE;
2568: l_must_enrl_anthr_pl_id NUMBER;
2569: l_auto_enrt_flag VARCHAR2(30) := 'N';
2570: l_rec_auto_enrt_flag VARCHAR2(30) := 'N';
2707: l_mn_ordr_num ben_oipl_f.ordr_num%TYPE;
2708: l_mx_ordr_num ben_oipl_f.ordr_num%TYPE;
2709: l_enrd_ordr_num NUMBER;
2710: l_level VARCHAR2(30);
2711: l_plan_rec ben_pl_f%ROWTYPE;
2712: l_pgm_rec ben_pgm_f%ROWTYPE;
2713: l_oipl_rec ben_cobj_cache.g_oipl_inst_row;
2714: l_pen_rec ben_prtt_enrt_rslt_f%ROWTYPE;
2715: l_pl_typ_esd DATE;
3385: pl.bnft_or_option_rstrctn_cd,
3386: lbr.cvg_incr_r_decr_only_cd,
3387: lbr.mx_cvg_mlt_incr_num,
3388: lbr.mx_cvg_mlt_incr_wcf_num
3389: FROM ben_oipl_f oipl, ben_ler_bnft_rstrn_f lbr, ben_pl_f pl
3390: WHERE pl.pl_id = l_pl_id
3391: AND pl.business_group_id = p_business_group_id
3392: AND l_lf_evt_ocrd_dt BETWEEN pl.effective_start_date
3393: AND pl.effective_end_date
3433: pl.bnft_or_option_rstrctn_cd,
3434: pl.cvg_incr_r_decr_only_cd,
3435: pl.mx_cvg_mlt_incr_num,
3436: pl.mx_cvg_mlt_incr_wcf_num
3437: FROM ben_oipl_f oipl, ben_pl_f pl
3438: WHERE pl.pl_id = l_pl_id
3439: AND pl.bnft_or_option_rstrctn_cd = 'OPT'
3440: AND pl.business_group_id = p_business_group_id
3441: AND l_lf_evt_ocrd_dt BETWEEN pl.effective_start_date
3572: ;
3573: --
3574: cursor c_plan_requery(p_id number,p_ed date) is
3575: select 'Y'
3576: from ben_pl_f plan
3577: where pl_id=p_id and
3578: business_group_id=p_business_group_id and
3579: p_ed between effective_start_date and effective_end_date
3580: ;
8781: --
8782: CURSOR c_pl_nip_dflt_cd IS
8783: SELECT pln.nip_dflt_enrt_cd,
8784: pln.nip_dflt_enrt_det_rl
8785: FROM ben_pl_f pln
8786: WHERE p_pl_id = pln.pl_id
8787: AND p_effective_date BETWEEN pln.effective_start_date
8788: AND pln.effective_end_date;
8789: --
8804: AND ptp.effective_end_date;
8805: --
8806: l_dflt_enrt_cd VARCHAR2(30);
8807: l_dflt_enrt_rl NUMBER;
8808: l_plan_rec ben_pl_f%ROWTYPE;
8809: l_oipl_rec ben_cobj_cache.g_oipl_inst_row;
8810: l_pl_rec ben_cobj_cache.g_pl_inst_row;
8811: BEGIN
8812: g_debug := hr_utility.debug_enabled;
8826: -- 4.5 if pgm ben_ler_chg_pl_nip_enrt_f
8827: -- 5 if pgm ben_ler_chg_ptip_enrt_f
8828: -- 6 if pgm ben_ler_chg_pgm_enrt_f
8829: --
8830: -- 7 if no pgm ben_pl_f
8831: -- 8 if pgm ben_plip_f
8832: -- 9 if pgm ben_ptip_f
8833: --
8834: IF p_oipl_id IS NOT NULL THEN
8970: ,p_oipl_rec in ben_oipl_f%rowtype
8971: ,p_plip_id in number
8972: ,p_plip_rec in ben_plip_f%rowtype
8973: ,p_pl_id in number
8974: ,p_pl_rec in ben_pl_f%rowtype
8975: ,p_ptip_id in number
8976: ,p_ptip_rec in ben_ptip_f%rowtype
8977: ,p_pgm_id in number
8978: ,p_pgm_rec in ben_pgm_f%rowtype
9047: CURSOR c_pl_nip_dflt_cd IS
9048: SELECT pln.nip_dflt_enrt_cd,
9049: pln.nip_dflt_enrt_det_rl,
9050: pln.nip_dflt_flag
9051: FROM ben_pl_f pln
9052: WHERE p_pl_id = pln.pl_id
9053: AND p_effective_date BETWEEN pln.effective_start_date
9054: AND pln.effective_end_date;
9055: --
9070: AND ptp.effective_end_date;
9071: --
9072: l_dflt_enrt_cd VARCHAR2(30);
9073: l_dflt_enrt_rl NUMBER;
9074: l_plan_rec ben_pl_f%ROWTYPE;
9075: l_oipl_rec ben_cobj_cache.g_oipl_inst_row;
9076: l_ler_dflt_flag varchar2(30); -- 3510229
9077: BEGIN
9078: hr_utility.set_location('Entering: ' || l_proc, 10);
9088: -- 4 if pgm ben_ler_chg_plip_enrt_f
9089: -- 5 if pgm ben_ler_chg_ptip_enrt_f
9090: -- 6 if pgm ben_ler_chg_pgm_enrt_f
9091: --
9092: -- 7 if no pgm ben_pl_f
9093: -- 8 if pgm ben_plip_f
9094: -- 9 if pgm ben_ptip_f
9095: --
9096: IF p_oipl_id IS NOT NULL THEN
9391: SELECT '3',
9392: bp.rqd_perd_enrt_nenrt_uom,
9393: bp.rqd_perd_enrt_nenrt_val,
9394: bp.rqd_perd_enrt_nenrt_rl
9395: FROM ben_pl_f bp
9396: WHERE bp.pl_id = p_pl_id
9397: AND p_effective_date BETWEEN bp.effective_start_date
9398: AND bp.effective_end_date
9399: AND bp.business_group_id = p_business_group_id
9604: -- Gets the enrolment information for the opt
9605: --
9606: CURSOR c_opt_enrolment_info IS
9607: SELECT pen.*
9608: FROM ben_prtt_enrt_rslt_f pen, ben_pl_f pl, ben_oipl_f oipl
9609: WHERE pen.person_id = p_person_id
9610: AND pen.business_group_id = p_business_group_id
9611: AND pen.effective_end_date = hr_api.g_eot
9612: AND l_effective_date_1 BETWEEN pen.enrt_cvg_strt_dt
9691: l_dflt_flag varchar2(30);
9692: l_use_dflt_flag varchar2(30);
9693: l_oipl_rec ben_oipl_f%rowtype;
9694: l_empty_oipl ben_oipl_f%rowtype;
9695: l_plan_rec ben_pl_f%rowtype;
9696: l_pgm_rec ben_pgm_f%rowtype;
9697: l_empty_pgm ben_pgm_f%rowtype;
9698: l_plip_rec ben_plip_f%rowtype;
9699: l_empty_plip ben_plip_f%rowtype;
9729: from ben_elig_per_elctbl_chc epe,
9730: /*
9731: ben_per_in_ler pil,
9732: */
9733: ben_pl_f pln
9734: where epe.auto_enrt_flag = 'N'
9735: and epe.per_in_ler_id = c_per_in_ler_id
9736: /*
9737: and pil.person_id = p_person_id