APPS.BEN_MANAGE_LIFE_EVENTS dependencies on BEN_CWB_MGR_HRCHY

Line 3433: from ben_cwb_mgr_hrchy epe_0,

3429: cursor c_no_0_hrchy(p_pl_id number,
3430: p_lf_evt_ocrd_dt date,
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

Line 3447: from ben_cwb_mgr_hrchy hrh,

3443: and pil_0.per_in_ler_stat_cd = 'STRTD'
3444: and epe_0.lvl_num > 0
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

Line 3471: ben_cwb_mgr_hrchy cwb,

3467: select
3468: cwb.emp_elig_per_elctbl_chc_id,
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

Line 3567: insert into ben_cwb_mgr_hrchy (

3563: ||p_emp_elig_per_elctbl_chc_id,10);
3564: hr_utility.set_location('insert_mgr_hrchy p_mgr_elig_per_elctbl_chc_id '
3565: ||p_mgr_elig_per_elctbl_chc_id || ' lvl = ' || p_lvl_num, 20);
3566: end if;
3567: insert into ben_cwb_mgr_hrchy (
3568: emp_elig_per_elctbl_chc_id,
3569: mgr_elig_per_elctbl_chc_id,
3570: lvl_num )
3571: values (

Line 3591: from ben_cwb_mgr_hrchy cwh

3587: -- And also the last subordinate is now reporting to another manager
3588: -- we need to delete the epe,0,0 row of the employee.
3589: --
3590: delete
3591: from ben_cwb_mgr_hrchy cwh
3592: where (( cwh.lvl_num = -1
3593: and cwh.mgr_elig_per_elctbl_chc_id = -1) OR
3594: ( cwh.lvl_num = 0 and
3595: cwh.mgr_elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id ) )

Line 3598: from ben_cwb_mgr_hrchy cwh1

3594: ( cwh.lvl_num = 0 and
3595: cwh.mgr_elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id ) )
3596: and not exists
3597: (select null
3598: from ben_cwb_mgr_hrchy cwh1
3599: where cwh1.mgr_elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id
3600: and cwh1.lvl_num <> 0
3601: )
3602: --

Line 3618: update ben_cwb_mgr_hrchy cwh

3614: (pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt and epe.pl_id = cv_pl_id)
3615: )
3616: ) ;
3617: --
3618: update ben_cwb_mgr_hrchy cwh
3619: set cwh.mgr_elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id,
3620: cwh.lvl_num = 0
3621: where cwh.lvl_num = -1 and
3622: cwh.mgr_elig_per_elctbl_chc_id = -1

Line 3785: insert into ben_cwb_mgr_hrchy (

3781: for l_no_0_hrchy in c_no_0_hrchy(lv_pl_id, lv_lf_evt_ocrd_dt, lv_business_group_id) loop
3782: --
3783: begin
3784: --
3785: insert into ben_cwb_mgr_hrchy (
3786: emp_elig_per_elctbl_chc_id,
3787: mgr_elig_per_elctbl_chc_id,
3788: lvl_num )
3789: values (

Line 3927: from ben_cwb_mgr_hrchy

3923: l_emp_epe_id number;
3924: --
3925: cursor c_cwb_hrchy(cv_emp_epe_id in number) is
3926: select emp_elig_per_elctbl_chc_id
3927: from ben_cwb_mgr_hrchy
3928: where emp_elig_per_elctbl_chc_id = cv_emp_epe_id;
3929: --
3930: cursor c_hrchy(cv_emp_epe_id in number) is
3931: select hrc.emp_pil_elctbl_chc_popl_id,

Line 4442: from ben_cwb_mgr_hrchy

4438: l_emp_epe_id number;
4439: --
4440: cursor c_cwb_hrchy(cv_emp_epe_id in number) is
4441: select emp_elig_per_elctbl_chc_id
4442: from ben_cwb_mgr_hrchy
4443: where emp_elig_per_elctbl_chc_id = cv_emp_epe_id;
4444: --
4445: begin
4446: --

Line 4645: insert into ben_cwb_mgr_hrchy (

4641: fetch c_cwb_hrchy into l_emp_epe_id;
4642: --
4643: if c_cwb_hrchy%notfound then
4644: --
4645: insert into ben_cwb_mgr_hrchy (
4646: emp_elig_per_elctbl_chc_id,
4647: mgr_elig_per_elctbl_chc_id,
4648: lvl_num )
4649: values(