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_PERSON_RATES

Line 39: ben_cwb_person_rates if group pil

35: 21 Jan 03 pbodla 115.8 Integrated with CWB procedures.
36: 21 Jan 03 pbodla 115.9 Fixed popu_missing_person_pil to
37: populate data properly.
38: 28 Jan 03 pbodla 115.12 Added code populate group pil id on
39: ben_cwb_person_rates if group pil
40: is null
41: 02 Feb 04 pbodla 115.13 Added c_benfts_grp cursor to fetch
42: the benefit group based on name.
43: Plans business group id have to

Line 179: 21-Feb-06 pbodla 115.60 currency col populated in ben_cwb_person_rates.

175: get_abr_ele_inp
176: determine_curr_code
177: 17-Feb-06 pbodla/stee115.59 CWB Multi currency support. Added
178: determine_curr_code
179: 21-Feb-06 pbodla 115.60 currency col populated in ben_cwb_person_rates.
180: 28-Feb-06 pbodla 115.62 Fix currency_det_cd for salary basis and standard
181: rates.
182: 04-Mar-06 maagrawa 115.63 Include call for exchange rate creation.
183: 24-Mar-06 maagrawa 115.64 GSCC nocopy error.

Line 241: (person_id ben_cwb_person_rates.person_id%type,

237: -- Globals needed to copy copy_ columns from rates table to group
238: -- table.
239: --
240: type g_cache_copy_person_bdgt_rt is record
241: (person_id ben_cwb_person_rates.person_id%type,
242: group_pl_id ben_pl_f.pl_id%type,
243: group_oipl_id ben_cwb_person_rates.group_oipl_id%type,
244: group_lf_evt_ocrd_dt date,
245: copy_ws_bdgt_val ben_cwb_person_rates.copy_ws_bdgt_val%type,

Line 243: group_oipl_id ben_cwb_person_rates.group_oipl_id%type,

239: --
240: type g_cache_copy_person_bdgt_rt is record
241: (person_id ben_cwb_person_rates.person_id%type,
242: group_pl_id ben_pl_f.pl_id%type,
243: group_oipl_id ben_cwb_person_rates.group_oipl_id%type,
244: group_lf_evt_ocrd_dt date,
245: copy_ws_bdgt_val ben_cwb_person_rates.copy_ws_bdgt_val%type,
246: copy_dist_bdgt_val ben_cwb_person_rates.copy_dist_bdgt_val%type,
247: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,

Line 245: copy_ws_bdgt_val ben_cwb_person_rates.copy_ws_bdgt_val%type,

241: (person_id ben_cwb_person_rates.person_id%type,
242: group_pl_id ben_pl_f.pl_id%type,
243: group_oipl_id ben_cwb_person_rates.group_oipl_id%type,
244: group_lf_evt_ocrd_dt date,
245: copy_ws_bdgt_val ben_cwb_person_rates.copy_ws_bdgt_val%type,
246: copy_dist_bdgt_val ben_cwb_person_rates.copy_dist_bdgt_val%type,
247: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,
248: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
249: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,

Line 246: copy_dist_bdgt_val ben_cwb_person_rates.copy_dist_bdgt_val%type,

242: group_pl_id ben_pl_f.pl_id%type,
243: group_oipl_id ben_cwb_person_rates.group_oipl_id%type,
244: group_lf_evt_ocrd_dt date,
245: copy_ws_bdgt_val ben_cwb_person_rates.copy_ws_bdgt_val%type,
246: copy_dist_bdgt_val ben_cwb_person_rates.copy_dist_bdgt_val%type,
247: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,
248: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
249: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
250: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,

Line 247: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,

243: group_oipl_id ben_cwb_person_rates.group_oipl_id%type,
244: group_lf_evt_ocrd_dt date,
245: copy_ws_bdgt_val ben_cwb_person_rates.copy_ws_bdgt_val%type,
246: copy_dist_bdgt_val ben_cwb_person_rates.copy_dist_bdgt_val%type,
247: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,
248: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
249: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
250: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
251: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,

Line 248: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,

244: group_lf_evt_ocrd_dt date,
245: copy_ws_bdgt_val ben_cwb_person_rates.copy_ws_bdgt_val%type,
246: copy_dist_bdgt_val ben_cwb_person_rates.copy_dist_bdgt_val%type,
247: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,
248: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
249: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
250: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
251: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
252: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,

Line 249: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,

245: copy_ws_bdgt_val ben_cwb_person_rates.copy_ws_bdgt_val%type,
246: copy_dist_bdgt_val ben_cwb_person_rates.copy_dist_bdgt_val%type,
247: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,
248: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
249: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
250: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
251: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
252: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
253: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,

Line 250: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,

246: copy_dist_bdgt_val ben_cwb_person_rates.copy_dist_bdgt_val%type,
247: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,
248: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
249: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
250: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
251: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
252: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
253: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
254: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,

Line 251: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,

247: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,
248: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
249: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
250: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
251: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
252: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
253: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
254: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
255: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,

Line 252: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,

248: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
249: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
250: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
251: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
252: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
253: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
254: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
255: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
256: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,

Line 253: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,

249: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
250: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
251: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
252: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
253: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
254: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
255: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
256: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
257: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,

Line 254: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,

250: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
251: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
252: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
253: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
254: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
255: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
256: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
257: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,
258: copy_ws_bdgt_iss_val ben_cwb_person_rates.copy_ws_bdgt_iss_val%type,

Line 255: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,

251: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
252: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
253: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
254: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
255: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
256: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
257: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,
258: copy_ws_bdgt_iss_val ben_cwb_person_rates.copy_ws_bdgt_iss_val%type,
259: copy_dist_bdgt_iss_date ben_cwb_person_rates.copy_dist_bdgt_iss_date%type,

Line 256: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,

252: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
253: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
254: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
255: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
256: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
257: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,
258: copy_ws_bdgt_iss_val ben_cwb_person_rates.copy_ws_bdgt_iss_val%type,
259: copy_dist_bdgt_iss_date ben_cwb_person_rates.copy_dist_bdgt_iss_date%type,
260: copy_ws_bdgt_iss_date ben_cwb_person_rates.copy_ws_bdgt_iss_date%type

Line 257: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,

253: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
254: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
255: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
256: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
257: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,
258: copy_ws_bdgt_iss_val ben_cwb_person_rates.copy_ws_bdgt_iss_val%type,
259: copy_dist_bdgt_iss_date ben_cwb_person_rates.copy_dist_bdgt_iss_date%type,
260: copy_ws_bdgt_iss_date ben_cwb_person_rates.copy_ws_bdgt_iss_date%type
261: );

Line 258: copy_ws_bdgt_iss_val ben_cwb_person_rates.copy_ws_bdgt_iss_val%type,

254: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
255: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
256: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
257: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,
258: copy_ws_bdgt_iss_val ben_cwb_person_rates.copy_ws_bdgt_iss_val%type,
259: copy_dist_bdgt_iss_date ben_cwb_person_rates.copy_dist_bdgt_iss_date%type,
260: copy_ws_bdgt_iss_date ben_cwb_person_rates.copy_ws_bdgt_iss_date%type
261: );
262: type g_cache_copy_person_bdgt_typ is table of g_cache_copy_person_bdgt_rt index

Line 259: copy_dist_bdgt_iss_date ben_cwb_person_rates.copy_dist_bdgt_iss_date%type,

255: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
256: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
257: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,
258: copy_ws_bdgt_iss_val ben_cwb_person_rates.copy_ws_bdgt_iss_val%type,
259: copy_dist_bdgt_iss_date ben_cwb_person_rates.copy_dist_bdgt_iss_date%type,
260: copy_ws_bdgt_iss_date ben_cwb_person_rates.copy_ws_bdgt_iss_date%type
261: );
262: type g_cache_copy_person_bdgt_typ is table of g_cache_copy_person_bdgt_rt index
263: by binary_integer;

Line 260: copy_ws_bdgt_iss_date ben_cwb_person_rates.copy_ws_bdgt_iss_date%type

256: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
257: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,
258: copy_ws_bdgt_iss_val ben_cwb_person_rates.copy_ws_bdgt_iss_val%type,
259: copy_dist_bdgt_iss_date ben_cwb_person_rates.copy_dist_bdgt_iss_date%type,
260: copy_ws_bdgt_iss_date ben_cwb_person_rates.copy_ws_bdgt_iss_date%type
261: );
262: type g_cache_copy_person_bdgt_typ is table of g_cache_copy_person_bdgt_rt index
263: by binary_integer;
264: g_cache_copy_person_bdgt_tbl g_cache_copy_person_bdgt_typ;

Line 1860: from ben_cwb_person_rates

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'
1860: from ben_cwb_person_rates
1861: where group_pl_id = cv_group_pl_id
1862: and person_id = pil.person_id
1863: and lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
1864: and elig_flag = 'Y');

Line 1874: * if there are no rows in ben_cwb_person_rates with

1870: /* If track inelig flag is N for group plan then
1871: *
1872: * For given GROUP_PL_ID, GROUP_OIPL_ID, LF_EVT_OCRD_DT
1873: *
1874: * if there are no rows in ben_cwb_person_rates with
1875: * ELIG_FLAG = Y and only consider uncloned data
1876: * then
1877: *
1878: * Delete data from ben_cwb_person_rates, ben_cwb_person_groups,

Line 1878: * Delete data from ben_cwb_person_rates, ben_cwb_person_groups,

1874: * if there are no rows in ben_cwb_person_rates with
1875: * ELIG_FLAG = Y and only consider uncloned data
1876: * then
1877: *
1878: * Delete data from ben_cwb_person_rates, ben_cwb_person_groups,
1879: * ben_per_in_ler, ben_ptnl_ler_for_per, ben_pil_elctbl_chc_popl,
1880: * ben_elig_per_elctbl_chc, ben_group_heirarchy,
1881: * ben_cwb_person_tasks.
1882: */

Line 2016: from ben_cwb_person_rates cpr

2012: ,c_pl_id IN NUMBER
2013: )
2014: is
2015: select cpr.rowid, cpr.*
2016: from ben_cwb_person_rates cpr
2017: where cpr.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
2018: and cpr.group_pl_id = c_group_pl_id
2019: and cpr.pl_id = c_pl_id
2020: and cpr.person_id =

Line 2022: from ben_cwb_person_rates cpr1

2018: and cpr.group_pl_id = c_group_pl_id
2019: and cpr.pl_id = c_pl_id
2020: and cpr.person_id =
2021: (select cpr1.person_id
2022: from ben_cwb_person_rates cpr1
2023: where cpr1.group_pl_id = c_group_pl_id
2024: and cpr1.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
2025: and cpr1.pl_id = c_pl_id
2026: and rownum = 1);

Line 2179: -- Now clone the ben_cwb_person_rates, ben_cwb_group_rates.

2175: ,p_procd_dt => l_procd_dt
2176: ,p_strtd_dt => l_strtd_dt
2177: ,p_voidd_dt => l_voidd_dt);
2178: --
2179: -- Now clone the ben_cwb_person_rates, ben_cwb_group_rates.
2180: --
2181: if p_pl_id = ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id then
2182: --
2183: -- Per in ler created is a group per in ler so populate other

Line 2308: -- BEN_CWB_PERSON_RATES

2304: -- For each of the person rates rows for a sample person
2305: -- copy data to current person.
2306: --
2307: -- Populate
2308: -- BEN_CWB_PERSON_RATES
2309: -- Primary Key: PERSON_RATE_ID
2310: --
2311: hr_utility.set_location ('p_pl_id ' || p_pl_id,60);
2312: hr_utility.set_location ('g_cache_group_plan_rec.group_pl_id '||

Line 2341: insert into ben_cwb_person_rates

2337: close c_asg;
2338: --
2339: end if;
2340: --
2341: insert into ben_cwb_person_rates
2342: (person_rate_id ,
2343: group_per_in_ler_id ,
2344: pl_id ,
2345: oipl_id ,

Line 2400: (ben_cwb_person_rates_s.nextval,

2396: last_update_login ,
2397: created_by ,
2398: creation_date */
2399: ) values
2400: (ben_cwb_person_rates_s.nextval,
2401: nvl(g_cache_group_plan_rec.group_per_in_ler_id, -1),
2402: l_cpr_rec.pl_id,
2403: nvl(l_cpr_rec.oipl_id, -1),
2404: l_cpr_rec.group_pl_id,

Line 3085: FND_STATS.GATHER_TABLE_STATS('BEN','BEN_CWB_PERSON_RATES');

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,
3089: -- buff => 'Completed : Populating heirarchy data, ' || to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'));

Line 3332: -- Procedure to populate ben_cwb_person_rates, ben_cwb_person_groups.

3328: fnd_message.raise_error;
3329: --
3330: end global_process;
3331: --
3332: -- Procedure to populate ben_cwb_person_rates, ben_cwb_person_groups.
3333: --
3334: procedure populate_cwb_rates(
3335: --
3336: -- Columns needed for ben_cwb_person_rates

Line 3336: -- Columns needed for ben_cwb_person_rates

3332: -- Procedure to populate ben_cwb_person_rates, ben_cwb_person_groups.
3333: --
3334: procedure populate_cwb_rates(
3335: --
3336: -- Columns needed for ben_cwb_person_rates
3337: --
3338: p_person_id in number
3339: ,p_assignment_id in number default null
3340: ,p_organization_id in number default null

Line 3541: -- BEN_CWB_PERSON_RATES

3537:
3538: end if;
3539: --
3540: -- Populate
3541: -- BEN_CWB_PERSON_RATES
3542: -- Primary Key: PERSON_RATE_ID
3543: --
3544: if ((p_pl_id = g_cache_group_plan_rec.group_pl_id and
3545: g_cache_group_plan_rec.plans_wthn_group_pl = 1

Line 3594: insert into ben_cwb_person_rates

3590: hr_utility.set_location ('Person rate GROUP_PL_ID ' || g_cache_group_plan_rec.group_pl_id,15);
3591: hr_utility.set_location ('Person rate LF_EVT_OCRD_DT ' || g_cache_group_plan_rec.group_lf_evt_ocrd_dt,15);
3592: end if;
3593:
3594: insert into ben_cwb_person_rates
3595: (person_rate_id ,
3596: group_per_in_ler_id ,
3597: pl_id ,
3598: oipl_id ,

Line 3653: (ben_cwb_person_rates_s.nextval ,

3649: last_update_login ,
3650: created_by ,
3651: creation_date
3652: ) values
3653: (ben_cwb_person_rates_s.nextval ,
3654: nvl(g_cache_group_plan_rec.group_per_in_ler_id, -1) ,
3655: p_pl_id ,
3656: nvl(p_oipl_id, -1),
3657: g_cache_group_plan_rec.group_pl_id ,

Line 3967: -- 9999 check whether person id is populated in ben_cwb_person_rates.

3963: p_effective_date in date default null) is
3964: --
3965: l_proc varchar2(72) := g_package||'.popu_missing_person_pil';
3966: --
3967: -- 9999 check whether person id is populated in ben_cwb_person_rates.
3968: --
3969: CURSOR c_person_rates
3970: (c_person_id IN NUMBER
3971: ,c_group_lf_evt_ocrd_dt IN DATE

Line 3975: select /+ INDEX (CPR BEN_CWB_PERSON_RATES_FK3) / cpr.rowid, cpr.*

3971: ,c_group_lf_evt_ocrd_dt IN DATE
3972: ,c_group_pl_id IN NUMBER
3973: )
3974: is
3975: select /*+ INDEX (CPR BEN_CWB_PERSON_RATES_FK3) */ cpr.rowid, cpr.*
3976: from ben_cwb_person_rates cpr
3977: where cpr.person_id = c_person_id
3978: and cpr.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
3979: and cpr.group_pl_id = c_group_pl_id

Line 3976: from ben_cwb_person_rates cpr

3972: ,c_group_pl_id IN NUMBER
3973: )
3974: is
3975: select /*+ INDEX (CPR BEN_CWB_PERSON_RATES_FK3) */ cpr.rowid, cpr.*
3976: from ben_cwb_person_rates cpr
3977: where cpr.person_id = c_person_id
3978: and cpr.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
3979: and cpr.group_pl_id = c_group_pl_id
3980: -- Bug 3517726 : Do not consider any data which is not deleted

Line 4190: -- Now update the group per in ler id on ben_cwb_person_rates

4186: -- Initialise the copy budget structures.
4187: --
4188: g_cache_copy_person_bdgt_tbl := g_cache_copy_person_bdgt_tbl1;
4189: --
4190: -- Now update the group per in ler id on ben_cwb_person_rates
4191: --
4192: l_copy_person_bdgt_count := 0;
4193: for l_per_rt_rec in c_person_rates(p_person_id,
4194: p_group_lf_evt_ocrd_dt,

Line 4198: update ben_cwb_person_rates cpr

4194: p_group_lf_evt_ocrd_dt,
4195: p_group_pl_id
4196: ) loop
4197: --
4198: update ben_cwb_person_rates cpr
4199: set cpr.group_per_in_ler_id = l_curr_per_in_ler_id
4200: where cpr.rowid = l_per_rt_rec.rowid;
4201: --
4202: -- Write copy budget values into table structures

Line 4494: from ben_cwb_person_rates cpr

4490: l_group_per_in_ler_id number null;
4491: cursor c_null_gpil_cpr(cv_group_pl_id in number,
4492: cv_lf_evt_ocrd_dt in date) is
4493: select cpr.*, cpr.rowid
4494: from ben_cwb_person_rates cpr
4495: where GROUP_PER_IN_LER_ID = -1
4496: and GROUP_PL_ID = cv_group_pl_id
4497: and LF_EVT_OCRD_DT = cv_lf_evt_ocrd_dt;
4498: --

Line 4553: Update the group_per_in_ler_id on ben_cwb_person_rates

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
4556: assignment_id = p_assignment_id;
4557:

Line 4620: -- Now populate the group per in ler id on ben_cwb_person_rates table

4616: end if;
4617: --
4618: end loop;
4619: --
4620: -- Now populate the group per in ler id on ben_cwb_person_rates table
4621: -- if it's missing.
4622: --
4623: g_error_log_rec.calling_proc := 'ben_cwb_person_rates';
4624: g_error_log_rec.step_number := 22;

Line 4623: g_error_log_rec.calling_proc := 'ben_cwb_person_rates';

4619: --
4620: -- Now populate the group per in ler id on ben_cwb_person_rates table
4621: -- if it's missing.
4622: --
4623: g_error_log_rec.calling_proc := 'ben_cwb_person_rates';
4624: g_error_log_rec.step_number := 22;
4625: --
4626: l_group_per_in_ler_id := null;
4627: for l_null_gpil_rec in c_null_gpil_cpr(g_cache_group_plan_rec.group_pl_id,

Line 4637: update ben_cwb_person_rates

4633: g_cache_group_plan_rec.group_ler_id);
4634: fetch get_gpil_id into l_group_per_in_ler_id;
4635: close get_gpil_id;
4636: --
4637: update ben_cwb_person_rates
4638: set group_per_in_ler_id = l_group_per_in_ler_id
4639: where rowid = l_null_gpil_rec.rowid;
4640: --
4641: end loop;

Line 4787: from ben_cwb_person_rates

4783: cv_pl_id in number,
4784: cv_lf_evt_ocrd_dt in date,
4785: cv_person_id in number) is
4786: select *
4787: from ben_cwb_person_rates
4788: where group_pl_id = cv_group_pl_id
4789: and pl_id = cv_pl_id
4790: and nvl(oipl_id, -1) = -1
4791: and person_id = cv_person_id

Line 4799: from ben_cwb_person_rates

4795: cv_pl_id in number,
4796: cv_lf_evt_ocrd_dt in date,
4797: cv_person_id in number) is
4798: select sum(ws_val) ws_val, sum(nvl(ws_val, 0)) ws_val_0_if_null
4799: from ben_cwb_person_rates
4800: where group_pl_id = cv_group_pl_id
4801: and pl_id = cv_pl_id
4802: and nvl(oipl_id, -1) <> -1
4803: and person_id = cv_person_id

Line 4817: -- Fetch all ben_cwb_person_rates for the person

4813: if g_debug then
4814: hr_utility.set_location ('Entering :sum_oipl_rates_and_upd_pl_rate ' ,10);
4815: end if;
4816: --
4817: -- Fetch all ben_cwb_person_rates for the person
4818: -- for the plan, for lf_evt_ocrd_dt if there exists oipl for this plan
4819: --
4820: if g_options_exists is null then
4821: --

Line 4850: update ben_cwb_person_rates

4846: then
4847: --
4848: -- Update plan level cpr.
4849: --
4850: update ben_cwb_person_rates
4851: set ws_val = l_oipl_ws_sum
4852: where person_rate_id = l_cpr_rec.person_rate_id;
4853: --
4854: end if;

Line 4889: from BEN_CWB_PERSON_RATES CPR

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
4890: where CGH.EMP_PER_IN_LER_ID = CPR.GROUP_PER_IN_LER_ID
4891: AND CPR.ELIG_FLAG = 'Y' ))
4892:
4893: and cpg.dist_bdgt_val_last_upd_date is null

Line 4904: from ben_cwb_person_rates cpr1,

4900: where cpg.group_pl_id = c_group_pl_id
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

Line 4912: from ben_cwb_person_rates cpr,

4908: and cgh1.LVL_NUM > 1
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