[Home] [Help]
2057: pep.prtn_end_dt,
2058: pep.per_in_ler_id,
2059: pep.object_version_number
2060: from ben_elig_per_f pep,
2061: ben_per_in_ler pil
2062: where pep.person_id = c_person_id
2063: and nvl(pep.pgm_id,-1) = c_pgm_id
2064: and nvl(pep.pl_id,-1) = c_pl_id
2065: and pep.plip_id is null
2090: epo.per_in_ler_id,
2091: pep.prtn_strt_dt strt_dt,
2092: pep.prtn_end_dt end_dt
2093: from ben_elig_per_opt_f epo,
2094: ben_per_in_ler pil,
2095: ben_elig_per_f pep
2096: where pep.person_id = c_person_id
2097: and pep.pl_id = c_pl_id
2098: and epo.opt_id = c_opt_id
2443: --bug#2718215
2444: cursor c_result_pgm (p_pgm_id number) is
2445: select effective_start_date
2446: from ben_prtt_enrt_rslt_f pen,
2447: ben_per_in_ler pil
2448: where pen.pgm_id = p_pgm_id
2449: and pen.person_id = pil.person_id
2450: and pil.per_in_ler_id = p_per_in_ler_id
2451: and pen.prtt_enrt_rslt_stat_cd is null
2453: --
2454: cursor c_result_pln (p_pl_id number) is
2455: select effective_start_date
2456: from ben_prtt_enrt_rslt_f pen,
2457: ben_per_in_ler pil
2458: where pen.pl_id = p_pl_id
2459: and pen.person_id = pil.person_id
2460: and pil.per_in_ler_id = p_per_in_ler_id
2461: and pen.prtt_enrt_rslt_stat_cd is null
2983: p_business_group_id number) is
2984: select unique pel_0.mgr_pil_elctbl_chc_popl_id
2985: from ben_cwb_hrchy pel_0,
2986: ben_pil_elctbl_chc_popl mgr_pel_0,
2987: ben_per_in_ler pil_0
2988: where
2989: mgr_pel_0.pil_elctbl_chc_popl_id = pel_0.mgr_pil_elctbl_chc_popl_id
2990: and mgr_pel_0.per_in_ler_id = pil_0.per_in_ler_id
2991: and mgr_pel_0.pl_id = p_pl_id
2997: and pel_0.mgr_pil_elctbl_chc_popl_id not in
2998: ( select mgr_pel.pil_elctbl_chc_popl_id
2999: from ben_cwb_hrchy hrh,
3000: ben_pil_elctbl_chc_popl mgr_pel,
3001: ben_per_in_ler pil
3002: where hrh.mgr_pil_elctbl_chc_popl_id =
3003: hrh.emp_pil_elctbl_chc_popl_id
3004: and mgr_pel.pil_elctbl_chc_popl_id = hrh.mgr_pil_elctbl_chc_popl_id
3005: and hrh.lvl_num = 0
3021: pel.ws_mgr_id
3022: from
3023: ben_cwb_hrchy cwb,
3024: ben_pil_elctbl_chc_popl pel ,
3025: ben_per_in_ler pil
3026: where
3027: cwb.mgr_pil_elctbl_chc_popl_id = -1
3028: and pel.pil_elctbl_chc_popl_id = cwb.emp_pil_elctbl_chc_popl_id
3029: --
3047: pil.ler_id,
3048: pil.business_group_id
3049: from
3050: ben_pil_elctbl_chc_popl pel,
3051: ben_per_in_ler pil
3052: where
3053: pel.per_in_ler_id = pil.per_in_ler_id
3054: and pel.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
3055: and pil.per_in_ler_stat_cd = 'STRTD';
3071: p_business_group_id number) is
3072: select pel.ws_mgr_id,
3073: pel.pil_elctbl_chc_popl_id
3074: from ben_pil_elctbl_chc_popl pel,
3075: ben_per_in_ler pil
3076: where pel.per_in_ler_id = pil.per_in_ler_id
3077: and pel.pl_id = p_pl_id
3078: and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
3079: and pil.ler_id = p_ler_id
3147: --
3148: and exists
3149: (select null
3150: from ben_pil_elctbl_chc_popl pel ,
3151: ben_per_in_ler pil
3152: where pel.pil_elctbl_chc_popl_id = cwh.emp_pil_elctbl_chc_popl_id
3153: and pel.per_in_ler_id = pil.per_in_ler_id
3154: and pil.per_in_ler_stat_cd = 'STRTD'
3155: and ((cv_pl_id = -1 and cv_lf_evt_ocrd_dt = hr_api.g_eot)
3182: --
3183: and exists
3184: (select null
3185: from ben_pil_elctbl_chc_popl pel ,
3186: ben_per_in_ler pil
3187: where pel.pil_elctbl_chc_popl_id = cwh.emp_pil_elctbl_chc_popl_id
3188: and pel.per_in_ler_id = pil.per_in_ler_id
3189: and pil.per_in_ler_stat_cd = 'STRTD'
3190: and ((cv_pl_id = -1 and cv_lf_evt_ocrd_dt = hr_api.g_eot)
3354: delete from ben_cwb_hrchy
3355: where emp_pil_elctbl_chc_popl_id in (
3356: select pel.pil_elctbl_chc_popl_id
3357: from ben_pil_elctbl_chc_popl pel,
3358: ben_per_in_ler pil
3359: where pel.pl_id = lv_pl_id
3360: and pil.lf_evt_ocrd_dt = lv_lf_evt_ocrd_dt
3361: and pil.per_in_ler_id = pel.per_in_ler_id
3362: and pil.per_in_ler_stat_cd = 'BCKDT');
3364: delete from ben_cwb_hrchy
3365: where mgr_pil_elctbl_chc_popl_id in (
3366: select pel.pil_elctbl_chc_popl_id
3367: from ben_pil_elctbl_chc_popl pel,
3368: ben_per_in_ler pil
3369: where pel.pl_id = lv_pl_id
3370: and pil.lf_evt_ocrd_dt = lv_lf_evt_ocrd_dt
3371: and pil.per_in_ler_id = pel.per_in_ler_id
3372: and pil.per_in_ler_stat_cd = 'BCKDT');
3431: p_business_group_id number) is
3432: select unique epe_0.mgr_elig_per_elctbl_chc_id
3433: from ben_cwb_mgr_hrchy epe_0,
3434: ben_elig_per_elctbl_chc mgr_epe_0,
3435: ben_per_in_ler pil_0
3436: where
3437: mgr_epe_0.elig_per_elctbl_chc_id = epe_0.mgr_elig_per_elctbl_chc_id
3438: and mgr_epe_0.per_in_ler_id = pil_0.per_in_ler_id
3439: and mgr_epe_0.pl_id = p_pl_id
3445: and epe_0.mgr_elig_per_elctbl_chc_id not in
3446: ( select mgr_epe.elig_per_elctbl_chc_id
3447: from ben_cwb_mgr_hrchy hrh,
3448: ben_elig_per_elctbl_chc mgr_epe,
3449: ben_per_in_ler pil
3450: where hrh.mgr_elig_per_elctbl_chc_id =
3451: hrh.emp_elig_per_elctbl_chc_id
3452: and mgr_epe.elig_per_elctbl_chc_id = hrh.mgr_elig_per_elctbl_chc_id
3453: and hrh.lvl_num = 0
3469: epe.ws_mgr_id
3470: from
3471: ben_cwb_mgr_hrchy cwb,
3472: ben_elig_per_elctbl_chc epe ,
3473: ben_per_in_ler pil
3474: where
3475: cwb.mgr_elig_per_elctbl_chc_id = -1
3476: and epe.elig_per_elctbl_chc_id = cwb.emp_elig_per_elctbl_chc_id
3477: --
3495: pil.ler_id,
3496: pil.business_group_id
3497: from
3498: ben_elig_per_elctbl_chc epe,
3499: ben_per_in_ler pil
3500: where
3501: epe.per_in_ler_id = pil.per_in_ler_id
3502: and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
3503: and pil.per_in_ler_stat_cd = 'STRTD';
3519: p_business_group_id number) is
3520: select epe.ws_mgr_id,
3521: epe.elig_per_elctbl_chc_id
3522: from ben_elig_per_elctbl_chc epe,
3523: ben_per_in_ler pil
3524: where epe.per_in_ler_id = pil.per_in_ler_id
3525: and epe.pl_id = p_pl_id
3526: and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
3527: and pil.ler_id = p_ler_id
3604: --
3605: and exists
3606: (select null
3607: from ben_elig_per_elctbl_chc epe ,
3608: ben_per_in_ler pil
3609: where epe.elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id
3610: and epe.per_in_ler_id = pil.per_in_ler_id
3611: and pil.per_in_ler_stat_cd = 'STRTD'
3612: and ((cv_pl_id = -1 and cv_lf_evt_ocrd_dt = hr_api.g_eot)
3625: --
3626: and exists
3627: (select null
3628: from ben_elig_per_elctbl_chc epe ,
3629: ben_per_in_ler pil
3630: where epe.elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id
3631: and epe.per_in_ler_id = pil.per_in_ler_id
3632: and pil.per_in_ler_stat_cd = 'STRTD'
3633: and ((cv_pl_id = -1 and cv_lf_evt_ocrd_dt = hr_api.g_eot)
4994: select pil.per_in_ler_id,
4995: ptnl.ptnl_ler_for_per_id,
4996: ptnl.object_version_number ptnl_ovn,
4997: pil.object_version_number pil_ovn
4998: from ben_per_in_ler pil,
4999: ben_ptnl_ler_for_per ptnl
5000: where pil.ler_id = p_ler_id
5001: and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
5002: and pil.person_id = p_person_id
5132: order by pfl.lf_evt_ocrd_dt asc, ler.lf_evt_oper_cd desc;
5133:
5134: cursor c_pil (p_person_id number) is
5135: select 'x'
5136: from ben_per_in_ler pil,
5137: ben_ler_f ler
5138: where pil.per_in_ler_stat_cd = 'STRTD'
5139: and pil.person_id = p_person_id
5140: and pil.ler_id = ler.ler_id
6997: -- Bug 2574791
6998: cursor c_pel is
6999: select pel.pl_id, pil.lf_evt_ocrd_dt, pel.business_group_id
7000: from ben_pil_elctbl_chc_popl pel
7001: ,ben_per_in_ler pil
7002: where pel.per_in_ler_id = pil.per_in_ler_id
7003: and pel.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id;
7004: --
7005: begin
8926: p_lf_evt_oper_cd in varchar2 default null) is /* GSP Rate Sync */
8927: --
8928: l_package varchar2(80);
8929: l_created_ler_id ben_ler_f.ler_id%type := null;
8930: l_per_in_ler_id ben_per_in_ler.per_in_ler_id%type;
8931: l_ptnl_ler benutils.g_ptnl_ler;
8932: l_effective_start_date date;
8933: l_effective_end_date date;
8934: l_rec benutils.g_active_life_event;
9759: cursor c_ptnl is
9760: select a.ptnl_ler_for_per_id ptnl_ler_for_per_id,
9761: a.object_version_number object_version_number
9762: from ben_ptnl_ler_for_per a,
9763: ben_per_in_ler b
9764: where b.per_in_ler_id = l_rec.per_in_ler_id
9765: and b.ptnl_ler_for_per_id = a.ptnl_ler_for_per_id;
9766: --
9767: cursor c_ptip_pl_id(p_gsp_pgm_id number,
9894: l_parpl_row ben_pl_f%rowtype;
9895: l_paropt_row ben_cobj_cache.g_opt_inst_row;
9896: l_paroipl_row ben_cobj_cache.g_oipl_inst_row;
9897: l_per_row per_all_people_F%rowtype;
9898: l_pil_row ben_per_in_ler%rowtype;
9899: l_empasg_row per_all_assignments_f%rowtype;
9900: l_benasg_row per_all_assignments_f%rowtype;
9901: l_appasg_row ben_person_object.g_cache_ass_table;
9902: l_empasgast_row per_assignment_status_types%rowtype;