[Home] [Help]
1172: ,p_effective_date in date)
1173: is
1174: cursor c_prev_pil(c_per_in_ler_id number ) is
1175: select pil.per_in_ler_id
1176: from ben_per_in_ler pil,
1177: ben_ler_f ler
1178: where pil.per_in_ler_id not in (c_per_in_ler_id)
1179: and pil.person_id = p_person_id
1180: and pil.ler_id = ler.ler_id
2449: pep.prtn_end_dt,
2450: pep.per_in_ler_id,
2451: pep.object_version_number
2452: from ben_elig_per_f pep,
2453: ben_per_in_ler pil
2454: where pep.person_id = c_person_id
2455: and nvl(pep.pgm_id,-1) = c_pgm_id
2456: and nvl(pep.pl_id,-1) = c_pl_id
2457: and pep.plip_id is null
2482: epo.per_in_ler_id,
2483: pep.prtn_strt_dt strt_dt,
2484: pep.prtn_end_dt end_dt
2485: from ben_elig_per_opt_f epo,
2486: ben_per_in_ler pil,
2487: ben_elig_per_f pep
2488: where pep.person_id = c_person_id
2489: and pep.pl_id = c_pl_id
2490: and epo.opt_id = c_opt_id
2835: --bug#2718215
2836: cursor c_result_pgm (p_pgm_id number) is
2837: select effective_start_date
2838: from ben_prtt_enrt_rslt_f pen,
2839: ben_per_in_ler pil
2840: where pen.pgm_id = p_pgm_id
2841: and pen.person_id = pil.person_id
2842: and pil.per_in_ler_id = p_per_in_ler_id
2843: and pen.prtt_enrt_rslt_stat_cd is null
2845: --
2846: cursor c_result_pln (p_pl_id number) is
2847: select effective_start_date
2848: from ben_prtt_enrt_rslt_f pen,
2849: ben_per_in_ler pil
2850: where pen.pl_id = p_pl_id
2851: and pen.person_id = pil.person_id
2852: and pil.per_in_ler_id = p_per_in_ler_id
2853: and pen.prtt_enrt_rslt_stat_cd is null
3375: p_business_group_id number) is
3376: select unique pel_0.mgr_pil_elctbl_chc_popl_id
3377: from ben_cwb_hrchy pel_0,
3378: ben_pil_elctbl_chc_popl mgr_pel_0,
3379: ben_per_in_ler pil_0
3380: where
3381: mgr_pel_0.pil_elctbl_chc_popl_id = pel_0.mgr_pil_elctbl_chc_popl_id
3382: and mgr_pel_0.per_in_ler_id = pil_0.per_in_ler_id
3383: and mgr_pel_0.pl_id = p_pl_id
3389: and pel_0.mgr_pil_elctbl_chc_popl_id not in
3390: ( select mgr_pel.pil_elctbl_chc_popl_id
3391: from ben_cwb_hrchy hrh,
3392: ben_pil_elctbl_chc_popl mgr_pel,
3393: ben_per_in_ler pil
3394: where hrh.mgr_pil_elctbl_chc_popl_id =
3395: hrh.emp_pil_elctbl_chc_popl_id
3396: and mgr_pel.pil_elctbl_chc_popl_id = hrh.mgr_pil_elctbl_chc_popl_id
3397: and hrh.lvl_num = 0
3413: pel.ws_mgr_id
3414: from
3415: ben_cwb_hrchy cwb,
3416: ben_pil_elctbl_chc_popl pel ,
3417: ben_per_in_ler pil
3418: where
3419: cwb.mgr_pil_elctbl_chc_popl_id = -1
3420: and pel.pil_elctbl_chc_popl_id = cwb.emp_pil_elctbl_chc_popl_id
3421: --
3439: pil.ler_id,
3440: pil.business_group_id
3441: from
3442: ben_pil_elctbl_chc_popl pel,
3443: ben_per_in_ler pil
3444: where
3445: pel.per_in_ler_id = pil.per_in_ler_id
3446: and pel.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
3447: and pil.per_in_ler_stat_cd = 'STRTD';
3463: p_business_group_id number) is
3464: select pel.ws_mgr_id,
3465: pel.pil_elctbl_chc_popl_id
3466: from ben_pil_elctbl_chc_popl pel,
3467: ben_per_in_ler pil
3468: where pel.per_in_ler_id = pil.per_in_ler_id
3469: and pel.pl_id = p_pl_id
3470: and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
3471: and pil.ler_id = p_ler_id
3539: --
3540: and exists
3541: (select null
3542: from ben_pil_elctbl_chc_popl pel ,
3543: ben_per_in_ler pil
3544: where pel.pil_elctbl_chc_popl_id = cwh.emp_pil_elctbl_chc_popl_id
3545: and pel.per_in_ler_id = pil.per_in_ler_id
3546: and pil.per_in_ler_stat_cd = 'STRTD'
3547: and ((cv_pl_id = -1 and cv_lf_evt_ocrd_dt = hr_api.g_eot)
3574: --
3575: and exists
3576: (select null
3577: from ben_pil_elctbl_chc_popl pel ,
3578: ben_per_in_ler pil
3579: where pel.pil_elctbl_chc_popl_id = cwh.emp_pil_elctbl_chc_popl_id
3580: and pel.per_in_ler_id = pil.per_in_ler_id
3581: and pil.per_in_ler_stat_cd = 'STRTD'
3582: and ((cv_pl_id = -1 and cv_lf_evt_ocrd_dt = hr_api.g_eot)
3746: delete from ben_cwb_hrchy
3747: where emp_pil_elctbl_chc_popl_id in (
3748: select pel.pil_elctbl_chc_popl_id
3749: from ben_pil_elctbl_chc_popl pel,
3750: ben_per_in_ler pil
3751: where pel.pl_id = lv_pl_id
3752: and pil.lf_evt_ocrd_dt = lv_lf_evt_ocrd_dt
3753: and pil.per_in_ler_id = pel.per_in_ler_id
3754: and pil.per_in_ler_stat_cd = 'BCKDT');
3756: delete from ben_cwb_hrchy
3757: where mgr_pil_elctbl_chc_popl_id in (
3758: select pel.pil_elctbl_chc_popl_id
3759: from ben_pil_elctbl_chc_popl pel,
3760: ben_per_in_ler pil
3761: where pel.pl_id = lv_pl_id
3762: and pil.lf_evt_ocrd_dt = lv_lf_evt_ocrd_dt
3763: and pil.per_in_ler_id = pel.per_in_ler_id
3764: and pil.per_in_ler_stat_cd = 'BCKDT');
3823: p_business_group_id number) is
3824: select unique epe_0.mgr_elig_per_elctbl_chc_id
3825: from ben_cwb_mgr_hrchy epe_0,
3826: ben_elig_per_elctbl_chc mgr_epe_0,
3827: ben_per_in_ler pil_0
3828: where
3829: mgr_epe_0.elig_per_elctbl_chc_id = epe_0.mgr_elig_per_elctbl_chc_id
3830: and mgr_epe_0.per_in_ler_id = pil_0.per_in_ler_id
3831: and mgr_epe_0.pl_id = p_pl_id
3837: and epe_0.mgr_elig_per_elctbl_chc_id not in
3838: ( select mgr_epe.elig_per_elctbl_chc_id
3839: from ben_cwb_mgr_hrchy hrh,
3840: ben_elig_per_elctbl_chc mgr_epe,
3841: ben_per_in_ler pil
3842: where hrh.mgr_elig_per_elctbl_chc_id =
3843: hrh.emp_elig_per_elctbl_chc_id
3844: and mgr_epe.elig_per_elctbl_chc_id = hrh.mgr_elig_per_elctbl_chc_id
3845: and hrh.lvl_num = 0
3861: epe.ws_mgr_id
3862: from
3863: ben_cwb_mgr_hrchy cwb,
3864: ben_elig_per_elctbl_chc epe ,
3865: ben_per_in_ler pil
3866: where
3867: cwb.mgr_elig_per_elctbl_chc_id = -1
3868: and epe.elig_per_elctbl_chc_id = cwb.emp_elig_per_elctbl_chc_id
3869: --
3887: pil.ler_id,
3888: pil.business_group_id
3889: from
3890: ben_elig_per_elctbl_chc epe,
3891: ben_per_in_ler pil
3892: where
3893: epe.per_in_ler_id = pil.per_in_ler_id
3894: and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
3895: and pil.per_in_ler_stat_cd = 'STRTD';
3911: p_business_group_id number) is
3912: select epe.ws_mgr_id,
3913: epe.elig_per_elctbl_chc_id
3914: from ben_elig_per_elctbl_chc epe,
3915: ben_per_in_ler pil
3916: where epe.per_in_ler_id = pil.per_in_ler_id
3917: and epe.pl_id = p_pl_id
3918: and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
3919: and pil.ler_id = p_ler_id
3996: --
3997: and exists
3998: (select null
3999: from ben_elig_per_elctbl_chc epe ,
4000: ben_per_in_ler pil
4001: where epe.elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id
4002: and epe.per_in_ler_id = pil.per_in_ler_id
4003: and pil.per_in_ler_stat_cd = 'STRTD'
4004: and ((cv_pl_id = -1 and cv_lf_evt_ocrd_dt = hr_api.g_eot)
4017: --
4018: and exists
4019: (select null
4020: from ben_elig_per_elctbl_chc epe ,
4021: ben_per_in_ler pil
4022: where epe.elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id
4023: and epe.per_in_ler_id = pil.per_in_ler_id
4024: and pil.per_in_ler_stat_cd = 'STRTD'
4025: and ((cv_pl_id = -1 and cv_lf_evt_ocrd_dt = hr_api.g_eot)
5386: select pil.per_in_ler_id,
5387: ptnl.ptnl_ler_for_per_id,
5388: ptnl.object_version_number ptnl_ovn,
5389: pil.object_version_number pil_ovn
5390: from ben_per_in_ler pil,
5391: ben_ptnl_ler_for_per ptnl
5392: where pil.ler_id = p_ler_id
5393: and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
5394: and pil.person_id = p_person_id
5524: order by pfl.lf_evt_ocrd_dt asc, ler.lf_evt_oper_cd desc;
5525:
5526: cursor c_pil (p_person_id number) is
5527: select 'x'
5528: from ben_per_in_ler pil,
5529: ben_ler_f ler
5530: where pil.per_in_ler_stat_cd = 'STRTD'
5531: and pil.person_id = p_person_id
5532: and pil.ler_id = ler.ler_id
7403: -- Bug 2574791
7404: cursor c_pel is
7405: select pel.pl_id, pil.lf_evt_ocrd_dt, pel.business_group_id
7406: from ben_pil_elctbl_chc_popl pel
7407: ,ben_per_in_ler pil
7408: where pel.per_in_ler_id = pil.per_in_ler_id
7409: and pel.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id;
7410: --
7411: begin
9334: p_lf_evt_oper_cd in varchar2 default null) is /* GSP Rate Sync */
9335: --
9336: l_package varchar2(80);
9337: l_created_ler_id ben_ler_f.ler_id%type := null;
9338: l_per_in_ler_id ben_per_in_ler.per_in_ler_id%type;
9339: l_ptnl_ler benutils.g_ptnl_ler;
9340: l_effective_start_date date;
9341: l_effective_end_date date;
9342: l_rec benutils.g_active_life_event;
10167: cursor c_ptnl is
10168: select a.ptnl_ler_for_per_id ptnl_ler_for_per_id,
10169: a.object_version_number object_version_number
10170: from ben_ptnl_ler_for_per a,
10171: ben_per_in_ler b
10172: where b.per_in_ler_id = l_rec.per_in_ler_id
10173: and b.ptnl_ler_for_per_id = a.ptnl_ler_for_per_id;
10174: --
10175: cursor c_ptip_pl_id(p_gsp_pgm_id number,
10350: l_parpl_row ben_pl_f%rowtype;
10351: l_paropt_row ben_cobj_cache.g_opt_inst_row;
10352: l_paroipl_row ben_cobj_cache.g_oipl_inst_row;
10353: l_per_row per_all_people_F%rowtype;
10354: l_pil_row ben_per_in_ler%rowtype;
10355: l_empasg_row per_all_assignments_f%rowtype;
10356: l_benasg_row per_all_assignments_f%rowtype;
10357: l_appasg_row ben_person_object.g_cache_ass_table;
10358: l_empasgast_row per_assignment_status_types%rowtype;
10543: -- cursor to fetch per_in_ler_id in mode S (MPE conc program)
10544:
10545: cursor c_get_unres_per_in_ler is
10546: select pil.per_in_ler_id
10547: from ben_per_in_ler pil,
10548: ben_ler_f ler
10549: where pil.person_id = p_person_id
10550: and pil.business_group_id = p_business_group_id
10551: and ler.ler_id = pil.ler_id
12737: ler.ptnl_ler_trtmt_cd,
12738: pil.object_version_number,
12739: pil.ptnl_ler_for_per_id,
12740: ler.qualg_evt_flag
12741: from ben_per_in_ler pil,
12742: ben_ler_f ler
12743: where pil.person_id = p_person_id
12744: and ler.ler_id = pil.ler_id
12745: and p_effective_date