eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BEN_MANAGE_CWB_LIFE_EVENTS dependencies on BEN_CWB_GROUP_HRCHY

Line 863: from ben_cwb_group_hrchy hrh_0,

859: cursor c_no_0_hrchy(p_pl_id number,
860: p_lf_evt_ocrd_dt date) is
861: select /*+ leading(MGR_INFO_0) use_nl(HRH_0)*/ unique hrh_0.mgr_per_in_ler_id
862: -- Bug 12717230
863: from ben_cwb_group_hrchy hrh_0,
864: ben_cwb_person_info mgr_info_0
865: where mgr_info_0.group_per_in_ler_id = hrh_0.emp_per_in_ler_id
866: and mgr_info_0.group_pl_id = p_pl_id
867: and mgr_info_0.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt

Line 871: from ben_cwb_group_hrchy hrh

867: and mgr_info_0.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
868: and hrh_0.lvl_num = 1
869: and not exists
870: ( select /*+ nl_aj */ 'Y' -- Bug 12717230
871: from ben_cwb_group_hrchy hrh
872: where hrh.mgr_per_in_ler_id = hrh_0.mgr_per_in_ler_id
873: and hrh.emp_per_in_ler_id = hrh_0.mgr_per_in_ler_id
874: and hrh.lvl_num = 0
875: );

Line 887: ben_cwb_group_hrchy cwb,

883: cwb.emp_per_in_ler_id,
884: pil.ws_mgr_id,
885: pil.person_id
886: from
887: ben_cwb_group_hrchy cwb,
888: ben_per_in_ler pil
889: where
890: cwb.mgr_per_in_ler_id = -1
891: and pil.per_in_ler_id = cwb.emp_per_in_ler_id

Line 967: insert into ben_cwb_group_hrchy (

963: hr_utility.set_location('insert_mgr_hrchy p_mgr_per_in_ler_id '
964: ||p_mgr_per_in_ler_id || ' lvl = '
965: || p_lvl_num, 20);
966: end if;
967: insert into ben_cwb_group_hrchy (
968: emp_per_in_ler_id,
969: mgr_per_in_ler_id,
970: lvl_num )
971: values (

Line 988: from ben_cwb_group_hrchy cwh

984: -- CWB bug : 2712602
985: --
986: cursor c_cwh is
987: select rowid
988: from ben_cwb_group_hrchy cwh
989: where cwh.lvl_num = -1 and
990: cwh.mgr_per_in_ler_id = -1
991: --
992: -- Bug 2541072 : Do not consider all per in ler's.

Line 1011: from ben_cwb_group_hrchy cwh

1007: -- And also the last subordinate is now reporting to another manager
1008: -- we need to delete the pil,0,0 row of the employee.
1009: --
1010: delete
1011: from ben_cwb_group_hrchy cwh
1012: where (( cwh.lvl_num = -1
1013: and cwh.mgr_per_in_ler_id = -1) OR
1014: ( cwh.lvl_num = 0 and
1015: cwh.mgr_per_in_ler_id = cwh.emp_per_in_ler_id ) )

Line 1018: from ben_cwb_group_hrchy cwh1

1014: ( cwh.lvl_num = 0 and
1015: cwh.mgr_per_in_ler_id = cwh.emp_per_in_ler_id ) )
1016: and not exists
1017: (select null
1018: from ben_cwb_group_hrchy cwh1
1019: where cwh1.mgr_per_in_ler_id = cwh.emp_per_in_ler_id
1020: and cwh1.lvl_num <> 0
1021: )
1022: --

Line 1040: from ben_per_in_ler pil, ben_cwb_group_hrchy cwh

1036: -- delete, but waiting for GSI to validate it scales better.
1037: -- For bug 4587770
1038: --
1039: select cwh.rowid
1040: from ben_per_in_ler pil, ben_cwb_group_hrchy cwh
1041: where pil.per_in_ler_id = cwh.emp_per_in_ler_id
1042: and pil.per_in_ler_stat_cd = 'STRTD'
1043: and pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
1044: and pil.group_pl_id = cv_pl_id

Line 1051: from ben_cwb_group_hrchy cwh1

1047: ( cwh.lvl_num = 0 and
1048: cwh.mgr_per_in_ler_id = cwh.emp_per_in_ler_id ) )
1049: and not exists
1050: (select null
1051: from ben_cwb_group_hrchy cwh1
1052: where cwh1.mgr_per_in_ler_id = cwh.emp_per_in_ler_id
1053: and cwh1.lvl_num <> 0
1054: );
1055: */

Line 1063: update ben_cwb_group_hrchy cwh

1059: for l_cwh in c_cwh loop
1060: --
1061: begin
1062: --
1063: update ben_cwb_group_hrchy cwh
1064: set cwh.mgr_per_in_ler_id = cwh.emp_per_in_ler_id,
1065: cwh.lvl_num = 0
1066: where cwh.lvl_num = -1
1067: and cwh.mgr_per_in_ler_id = -1

Line 1071: delete from ben_cwb_group_hrchy where rowid = l_cwh.rowid;null;

1067: and cwh.mgr_per_in_ler_id = -1
1068: and cwh.rowid = l_cwh.rowid;
1069: exception
1070: when others then
1071: delete from ben_cwb_group_hrchy where rowid = l_cwh.rowid;null;
1072: end;
1073: --
1074: end loop;
1075: --

Line 1245: delete from ben_cwb_group_hrchy

1241: if fnd_global.conc_request_id not in ( 0,-1) then
1242: --
1243: -- CWB 2712602 : Delete all the hrchy data linked to backed out per in ler.
1244: --
1245: delete from ben_cwb_group_hrchy
1246: where emp_per_in_ler_id in (
1247: select pil.per_in_ler_id
1248: from ben_per_in_ler pil
1249: where pil.group_pl_id = lv_pl_id

Line 1253: delete from ben_cwb_group_hrchy

1249: where pil.group_pl_id = lv_pl_id
1250: and pil.lf_evt_ocrd_dt = lv_lf_evt_ocrd_dt
1251: and pil.per_in_ler_stat_cd = 'BCKDT');
1252: --
1253: delete from ben_cwb_group_hrchy
1254: where mgr_per_in_ler_id in (
1255: select pil.per_in_ler_id
1256: from ben_per_in_ler pil
1257: where pil.group_pl_id = lv_pl_id

Line 1272: insert into ben_cwb_group_hrchy (

1268: c_no_0_hrchy(lv_pl_id, lv_lf_evt_ocrd_dt) loop
1269: --
1270: begin
1271: --
1272: insert into ben_cwb_group_hrchy (
1273: emp_per_in_ler_id,
1274: mgr_per_in_ler_id,
1275: lvl_num )
1276: values (

Line 1478: from ben_cwb_group_hrchy

1474: l_uses_custom_intg varchar2(5);
1475: --
1476: cursor c_cwb_hrchy(cv_emp_pil_id in number) is
1477: select emp_per_in_ler_id
1478: from ben_cwb_group_hrchy
1479: where emp_per_in_ler_id = cv_emp_pil_id;
1480: --
1481: cursor c_cwb_tasks(cv_pl_id in number) is
1482: select *

Line 1503: -- Populate ben_cwb_group_hrchy

1499: end if;
1500: --
1501: g_cache_group_plan_rec.group_per_in_ler_id := p_group_per_in_ler_id;
1502: --
1503: -- Populate ben_cwb_group_hrchy
1504: --
1505: open c_cwb_hrchy(p_group_per_in_ler_id);
1506: fetch c_cwb_hrchy into l_emp_pil_id;
1507: --

Line 1511: insert into ben_cwb_group_hrchy (

1507: --
1508: hr_utility.set_location('p_group_per_in_ler_id: '||p_group_per_in_ler_id,111);
1509: if c_cwb_hrchy%notfound then
1510: --
1511: insert into ben_cwb_group_hrchy (
1512: emp_per_in_ler_id,
1513: mgr_per_in_ler_id,
1514: lvl_num,
1515: OBJECT_VERSION_NUMBER )

Line 1855: from ben_cwb_group_hrchy hrh

1851: and ptnl.ptnl_ler_for_per_id = pil.ptnl_ler_for_per_id
1852: and ptnl.ptnl_ler_for_per_stat_cd = 'PROCD'
1853: and not exists
1854: (select 'Y'
1855: from ben_cwb_group_hrchy hrh
1856: where hrh.mgr_per_in_ler_id = pil.per_in_ler_id
1857: and hrh.lvl_num > 0)
1858: and not exists
1859: (select 'Y'

Line 3084: FND_STATS.GATHER_TABLE_STATS('BEN','BEN_CWB_GROUP_HRCHY');

3080: --
3081: --
3082: -- BEGIN: Gather stats for the CWB tables that are updated with group_per_in_ler_id's BUG#13436491
3083: --
3084: FND_STATS.GATHER_TABLE_STATS('BEN','BEN_CWB_GROUP_HRCHY');
3085: FND_STATS.GATHER_TABLE_STATS('BEN','BEN_CWB_PERSON_RATES');
3086: FND_STATS.GATHER_TABLE_STATS('BEN','BEN_CWB_PERSON_GROUPS');
3087: -- END changes BUG#13436491
3088: fnd_file.put_line(which => fnd_file.log,

Line 3769: insert into ben_cwb_group_hrchy (

3765: --
3766: -- Step 1
3767: begin
3768: --
3769: insert into ben_cwb_group_hrchy (
3770: emp_per_in_ler_id,
3771: mgr_per_in_ler_id,
3772: lvl_num )
3773: values (

Line 3802: from ben_cwb_group_hrchy

3798: declare
3799:
3800: cursor c_emp_repo is
3801: select emp_per_in_ler_id
3802: from ben_cwb_group_hrchy
3803: where mgr_per_in_ler_id = p_group_per_in_ler_id
3804: and lvl_num > 0 ;
3805: --
3806: begin

Line 3812: insert into ben_cwb_group_hrchy (

3808: for r_emp_repo in c_emp_repo loop
3809: --
3810: begin
3811:
3812: insert into ben_cwb_group_hrchy (
3813: emp_per_in_ler_id,
3814: mgr_per_in_ler_id,
3815: lvl_num ) values (r_emp_repo.emp_per_in_ler_id, -1, -1);
3816: --

Line 3842: delete from ben_cwb_group_hrchy

3838: begin
3839: --
3840: --First Delete the Manager's own Hierarchy
3841: --
3842: delete from ben_cwb_group_hrchy
3843: where emp_per_in_ler_id = p_group_per_in_ler_id
3844: and lvl_num >= 0;
3845: --
3846: -- Now delete the Employees reporting to this manager(if he is a manager).

Line 3848: delete from ben_cwb_group_hrchy

3844: and lvl_num >= 0;
3845: --
3846: -- Now delete the Employees reporting to this manager(if he is a manager).
3847: --
3848: delete from ben_cwb_group_hrchy
3849: where emp_per_in_ler_id in (
3850: select emp_per_in_ler_id
3851: from ben_cwb_group_hrchy
3852: where mgr_per_in_ler_id = p_group_per_in_ler_id )

Line 3851: from ben_cwb_group_hrchy

3847: --
3848: delete from ben_cwb_group_hrchy
3849: where emp_per_in_ler_id in (
3850: select emp_per_in_ler_id
3851: from ben_cwb_group_hrchy
3852: where mgr_per_in_ler_id = p_group_per_in_ler_id )
3853: and lvl_num >= 0;
3854: --
3855: exception when others then

Line 3887: insert into ben_cwb_group_hrchy(

3883: end if;
3884: --
3885: begin
3886: --
3887: insert into ben_cwb_group_hrchy(
3888: emp_per_in_ler_id,
3889: mgr_per_in_ler_id,
3890: lvl_num )
3891: select

Line 3895: from ben_cwb_group_hrchy cwb1

3891: select
3892: distinct emp_per_in_ler_id,
3893: emp_per_in_ler_id,
3894: 0
3895: from ben_cwb_group_hrchy cwb1
3896: where emp_per_in_ler_id =
3897: ( select mgr_per_in_ler_id from ben_cwb_group_hrchy
3898: where emp_per_in_ler_id = p_group_per_in_ler_id
3899: and lvl_num = 1 )

Line 3897: ( select mgr_per_in_ler_id from ben_cwb_group_hrchy

3893: emp_per_in_ler_id,
3894: 0
3895: from ben_cwb_group_hrchy cwb1
3896: where emp_per_in_ler_id =
3897: ( select mgr_per_in_ler_id from ben_cwb_group_hrchy
3898: where emp_per_in_ler_id = p_group_per_in_ler_id
3899: and lvl_num = 1 )
3900: and not exists ( select null from ben_cwb_group_hrchy cwb2
3901: where cwb1.emp_per_in_ler_id = cwb2.emp_per_in_ler_id

Line 3900: and not exists ( select null from ben_cwb_group_hrchy cwb2

3896: where emp_per_in_ler_id =
3897: ( select mgr_per_in_ler_id from ben_cwb_group_hrchy
3898: where emp_per_in_ler_id = p_group_per_in_ler_id
3899: and lvl_num = 1 )
3900: and not exists ( select null from ben_cwb_group_hrchy cwb2
3901: where cwb1.emp_per_in_ler_id = cwb2.emp_per_in_ler_id
3902: and lvl_num = 0 ) ;
3903: --
3904: exception when no_data_found then

Line 3919: insert into ben_cwb_group_hrchy(

3915: -- move the sub query into main query.
3916: -- Waiting for GSI to validate. Once validated at gsi db, following code
3917: -- can replace above insert.
3918: --
3919: insert into ben_cwb_group_hrchy(
3920: emp_per_in_ler_id,
3921: mgr_per_in_ler_id,
3922: lvl_num )
3923: select

Line 3927: from ben_cwb_group_hrchy h

3923: select
3924: h.mgr_per_in_ler_id
3925: ,h.mgr_per_in_ler_id
3926: ,0
3927: from ben_cwb_group_hrchy h
3928: where h.emp_per_in_ler_id = p_group_per_in_ler_id
3929: and h.lvl_num = 1;
3930: --
3931: exception when others then

Line 4551: Insert row into ben_cwb_group_hrchy if row already not exists.

4547: If cached data for one group plan is not available,
4548: cache group plan per in ler data.
4549:
4550: Create potential, per in ler for the group_ler_id for the person.
4551: Insert row into ben_cwb_group_hrchy if row already not exists.
4552:
4553: Update the group_per_in_ler_id on ben_cwb_person_rates
4554: where group_per_in_ler_id is null and
4555: group_pl_id = p_group_pl_id and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt and

Line 4885: FROM BEN_CWB_GROUP_HRCHY CGH

4881: where cpg.group_pl_id = c_group_pl_id
4882: and cpg.lf_evt_ocrd_dt = c_lf_evt_ocrd_dt
4883: and exists
4884: ( SELECT /*+ no_unnest */ NULL
4885: FROM BEN_CWB_GROUP_HRCHY CGH
4886: where CGH.MGR_PER_IN_LER_ID = CPG.GROUP_PER_IN_LER_ID
4887: AND CGH.LVL_NUM > 1
4888: And exists (select /*+ no_unnest */ null
4889: from BEN_CWB_PERSON_RATES CPR

Line 4905: ben_cwb_group_hrchy cgh1

4901: and cpg.lf_evt_ocrd_dt = c_lf_evt_ocrd_dt
4902: and not exists
4903: (select /*+ no_unnest */ null
4904: from ben_cwb_person_rates cpr1,
4905: ben_cwb_group_hrchy cgh1
4906: where cgh1.emp_per_in_ler_id = cpr1.GROUP_PER_IN_LER_ID
4907: and cpr1.ELIG_FLAG = 'Y'
4908: and cgh1.LVL_NUM > 1
4909: and cgh1.mgr_per_in_ler_id = cpg.GROUP_PER_IN_LER_ID)

Line 4913: ben_cwb_group_hrchy cgh

4909: and cgh1.mgr_per_in_ler_id = cpg.GROUP_PER_IN_LER_ID)
4910: and exists
4911: (select /*+ no_unnest */ null
4912: from ben_cwb_person_rates cpr,
4913: ben_cwb_group_hrchy cgh
4914: where cgh.emp_per_in_ler_id = cpr.GROUP_PER_IN_LER_ID
4915: and cpr.ELIG_FLAG = 'Y'
4916: and cgh.LVL_NUM = 1
4917: and cgh.mgr_per_in_ler_id = cpg.GROUP_PER_IN_LER_ID)