APPS.BEN_MANAGE_LIFE_EVENTS dependencies on BEN_CWB_MGR_HRCHY

Line 3825: from ben_cwb_mgr_hrchy epe_0,

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

Line 3839: from ben_cwb_mgr_hrchy hrh,

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

Line 3863: ben_cwb_mgr_hrchy cwb,

3859: select
3860: cwb.emp_elig_per_elctbl_chc_id,
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

Line 3959: insert into ben_cwb_mgr_hrchy (

3955: ||p_emp_elig_per_elctbl_chc_id,10);
3956: hr_utility.set_location('insert_mgr_hrchy p_mgr_elig_per_elctbl_chc_id '
3957: ||p_mgr_elig_per_elctbl_chc_id || ' lvl = ' || p_lvl_num, 20);
3958: end if;
3959: insert into ben_cwb_mgr_hrchy (
3960: emp_elig_per_elctbl_chc_id,
3961: mgr_elig_per_elctbl_chc_id,
3962: lvl_num )
3963: values (

Line 3983: from ben_cwb_mgr_hrchy cwh

3979: -- And also the last subordinate is now reporting to another manager
3980: -- we need to delete the epe,0,0 row of the employee.
3981: --
3982: delete
3983: from ben_cwb_mgr_hrchy cwh
3984: where (( cwh.lvl_num = -1
3985: and cwh.mgr_elig_per_elctbl_chc_id = -1) OR
3986: ( cwh.lvl_num = 0 and
3987: cwh.mgr_elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id ) )

Line 3990: from ben_cwb_mgr_hrchy cwh1

3986: ( cwh.lvl_num = 0 and
3987: cwh.mgr_elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id ) )
3988: and not exists
3989: (select null
3990: from ben_cwb_mgr_hrchy cwh1
3991: where cwh1.mgr_elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id
3992: and cwh1.lvl_num <> 0
3993: )
3994: --

Line 4010: update ben_cwb_mgr_hrchy cwh

4006: (pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt and epe.pl_id = cv_pl_id)
4007: )
4008: ) ;
4009: --
4010: update ben_cwb_mgr_hrchy cwh
4011: set cwh.mgr_elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id,
4012: cwh.lvl_num = 0
4013: where cwh.lvl_num = -1 and
4014: cwh.mgr_elig_per_elctbl_chc_id = -1

Line 4177: insert into ben_cwb_mgr_hrchy (

4173: for l_no_0_hrchy in c_no_0_hrchy(lv_pl_id, lv_lf_evt_ocrd_dt, lv_business_group_id) loop
4174: --
4175: begin
4176: --
4177: insert into ben_cwb_mgr_hrchy (
4178: emp_elig_per_elctbl_chc_id,
4179: mgr_elig_per_elctbl_chc_id,
4180: lvl_num )
4181: values (

Line 4319: from ben_cwb_mgr_hrchy

4315: l_emp_epe_id number;
4316: --
4317: cursor c_cwb_hrchy(cv_emp_epe_id in number) is
4318: select emp_elig_per_elctbl_chc_id
4319: from ben_cwb_mgr_hrchy
4320: where emp_elig_per_elctbl_chc_id = cv_emp_epe_id;
4321: --
4322: cursor c_hrchy(cv_emp_epe_id in number) is
4323: select hrc.emp_pil_elctbl_chc_popl_id,

Line 4834: from ben_cwb_mgr_hrchy

4830: l_emp_epe_id number;
4831: --
4832: cursor c_cwb_hrchy(cv_emp_epe_id in number) is
4833: select emp_elig_per_elctbl_chc_id
4834: from ben_cwb_mgr_hrchy
4835: where emp_elig_per_elctbl_chc_id = cv_emp_epe_id;
4836: --
4837: begin
4838: --

Line 5037: insert into ben_cwb_mgr_hrchy (

5033: fetch c_cwb_hrchy into l_emp_epe_id;
5034: --
5035: if c_cwb_hrchy%notfound then
5036: --
5037: insert into ben_cwb_mgr_hrchy (
5038: emp_elig_per_elctbl_chc_id,
5039: mgr_elig_per_elctbl_chc_id,
5040: lvl_num )
5041: values(