[Home] [Help]
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
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.
230: -- Globals needed to copy copy_
231: -- table.
232: --
233: type g_cache_copy_person_bdgt_rt is record
234: (person_id ben_cwb_person_rates.person_id%type,
235: group_pl_id ben_pl_f.pl_id%type,
236: group_oipl_id ben_cwb_person_rates.group_oipl_id%type,
237: group_lf_evt_ocrd_dt date,
238: copy_ws_bdgt_val ben_cwb_person_rates.copy_ws_bdgt_val%type,
232: --
233: type g_cache_copy_person_bdgt_rt is record
234: (person_id ben_cwb_person_rates.person_id%type,
235: group_pl_id ben_pl_f.pl_id%type,
236: group_oipl_id ben_cwb_person_rates.group_oipl_id%type,
237: group_lf_evt_ocrd_dt date,
238: copy_ws_bdgt_val ben_cwb_person_rates.copy_ws_bdgt_val%type,
239: copy_dist_bdgt_val ben_cwb_person_rates.copy_dist_bdgt_val%type,
240: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,
234: (person_id ben_cwb_person_rates.person_id%type,
235: group_pl_id ben_pl_f.pl_id%type,
236: group_oipl_id ben_cwb_person_rates.group_oipl_id%type,
237: group_lf_evt_ocrd_dt date,
238: copy_ws_bdgt_val ben_cwb_person_rates.copy_ws_bdgt_val%type,
239: copy_dist_bdgt_val ben_cwb_person_rates.copy_dist_bdgt_val%type,
240: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,
241: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
242: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
235: group_pl_id ben_pl_f.pl_id%type,
236: group_oipl_id ben_cwb_person_rates.group_oipl_id%type,
237: group_lf_evt_ocrd_dt date,
238: copy_ws_bdgt_val ben_cwb_person_rates.copy_ws_bdgt_val%type,
239: copy_dist_bdgt_val ben_cwb_person_rates.copy_dist_bdgt_val%type,
240: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,
241: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
242: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
243: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
236: group_oipl_id ben_cwb_person_rates.group_oipl_id%type,
237: group_lf_evt_ocrd_dt date,
238: copy_ws_bdgt_val ben_cwb_person_rates.copy_ws_bdgt_val%type,
239: copy_dist_bdgt_val ben_cwb_person_rates.copy_dist_bdgt_val%type,
240: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,
241: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
242: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
243: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
244: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
237: group_lf_evt_ocrd_dt date,
238: copy_ws_bdgt_val ben_cwb_person_rates.copy_ws_bdgt_val%type,
239: copy_dist_bdgt_val ben_cwb_person_rates.copy_dist_bdgt_val%type,
240: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,
241: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
242: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
243: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
244: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
245: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
238: copy_ws_bdgt_val ben_cwb_person_rates.copy_ws_bdgt_val%type,
239: copy_dist_bdgt_val ben_cwb_person_rates.copy_dist_bdgt_val%type,
240: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,
241: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
242: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
243: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
244: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
245: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
246: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
239: copy_dist_bdgt_val ben_cwb_person_rates.copy_dist_bdgt_val%type,
240: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,
241: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
242: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
243: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
244: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
245: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
246: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
247: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
240: copy_rsrv_val ben_cwb_person_rates.copy_rsrv_val%type,
241: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
242: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
243: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
244: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
245: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
246: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
247: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
248: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
241: copy_dist_bdgt_mn_val ben_cwb_person_rates.copy_dist_bdgt_mn_val%type,
242: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
243: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
244: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
245: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
246: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
247: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
248: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
249: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
242: copy_dist_bdgt_mx_val ben_cwb_person_rates.copy_dist_bdgt_mx_val%type,
243: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
244: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
245: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
246: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
247: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
248: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
249: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
250: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,
243: copy_dist_bdgt_incr_val ben_cwb_person_rates.copy_dist_bdgt_incr_val%type,
244: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
245: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
246: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
247: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
248: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
249: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
250: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,
251: copy_ws_bdgt_iss_val ben_cwb_person_rates.copy_ws_bdgt_iss_val%type,
244: copy_ws_bdgt_mn_val ben_cwb_person_rates.copy_ws_bdgt_mn_val%type,
245: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
246: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
247: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
248: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
249: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
250: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,
251: copy_ws_bdgt_iss_val ben_cwb_person_rates.copy_ws_bdgt_iss_val%type,
252: copy_dist_bdgt_iss_date ben_cwb_person_rates.copy_dist_bdgt_iss_date%type,
245: copy_ws_bdgt_mx_val ben_cwb_person_rates.copy_ws_bdgt_mx_val%type,
246: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
247: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
248: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
249: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
250: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,
251: copy_ws_bdgt_iss_val ben_cwb_person_rates.copy_ws_bdgt_iss_val%type,
252: copy_dist_bdgt_iss_date ben_cwb_person_rates.copy_dist_bdgt_iss_date%type,
253: copy_ws_bdgt_iss_date ben_cwb_person_rates.copy_ws_bdgt_iss_date%type
246: copy_ws_bdgt_incr_val ben_cwb_person_rates.copy_ws_bdgt_incr_val%type,
247: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
248: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
249: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
250: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,
251: copy_ws_bdgt_iss_val ben_cwb_person_rates.copy_ws_bdgt_iss_val%type,
252: copy_dist_bdgt_iss_date ben_cwb_person_rates.copy_dist_bdgt_iss_date%type,
253: copy_ws_bdgt_iss_date ben_cwb_person_rates.copy_ws_bdgt_iss_date%type
254: );
247: copy_rsrv_mn_val ben_cwb_person_rates.copy_rsrv_mn_val%type,
248: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
249: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
250: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,
251: copy_ws_bdgt_iss_val ben_cwb_person_rates.copy_ws_bdgt_iss_val%type,
252: copy_dist_bdgt_iss_date ben_cwb_person_rates.copy_dist_bdgt_iss_date%type,
253: copy_ws_bdgt_iss_date ben_cwb_person_rates.copy_ws_bdgt_iss_date%type
254: );
255: type g_cache_copy_person_bdgt_typ is table of g_cache_copy_person_bdgt_rt index
248: copy_rsrv_mx_val ben_cwb_person_rates.copy_rsrv_mx_val%type,
249: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
250: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,
251: copy_ws_bdgt_iss_val ben_cwb_person_rates.copy_ws_bdgt_iss_val%type,
252: copy_dist_bdgt_iss_date ben_cwb_person_rates.copy_dist_bdgt_iss_date%type,
253: copy_ws_bdgt_iss_date ben_cwb_person_rates.copy_ws_bdgt_iss_date%type
254: );
255: type g_cache_copy_person_bdgt_typ is table of g_cache_copy_person_bdgt_rt index
256: by binary_integer;
249: copy_rsrv_incr_val ben_cwb_person_rates.copy_rsrv_incr_val%type,
250: copy_dist_bdgt_iss_val ben_cwb_person_rates.copy_dist_bdgt_iss_val%type,
251: copy_ws_bdgt_iss_val ben_cwb_person_rates.copy_ws_bdgt_iss_val%type,
252: copy_dist_bdgt_iss_date ben_cwb_person_rates.copy_dist_bdgt_iss_date%type,
253: copy_ws_bdgt_iss_date ben_cwb_person_rates.copy_ws_bdgt_iss_date%type
254: );
255: type g_cache_copy_person_bdgt_typ is table of g_cache_copy_person_bdgt_rt index
256: by binary_integer;
257: g_cache_copy_person_bdgt_tbl g_cache_copy_person_bdgt_typ;
1811: where hrh.mgr_per_in_ler_id = pil.per_in_ler_id
1812: and hrh.lvl_num > 0)
1813: and not exists
1814: (select 'Y'
1815: from ben_cwb_person_rates
1816: where group_pl_id = cv_group_pl_id
1817: and person_id = pil.person_id
1818: and lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
1819: and elig_flag = 'Y');
1825: /* If track inelig flag is N for group plan then
1826: *
1827: * For given GROUP_PL_ID, GROUP_OIPL_ID, LF_EVT_OCRD_DT
1828: *
1829: * if there are no rows in ben_cwb_person_rates with
1830: * ELIG_FLAG = Y and only consider uncloned data
1831: * then
1832: *
1833: * Delete data from ben_cwb_person_rates, ben_cwb_person_groups,
1829: * if there are no rows in ben_cwb_person_rates with
1830: * ELIG_FLAG = Y and only consider uncloned data
1831: * then
1832: *
1833: * Delete data from ben_cwb_person_rates, ben_cwb_person_groups,
1834: * ben_per_in_ler, ben_ptnl_ler_for_per, ben_pil_elctbl_chc_popl,
1835: * ben_elig_per_elctbl_chc, ben_group_heirarchy,
1836: * ben_cwb_person_tasks.
1837: */
1967: ,c_pl_id IN NUMBER
1968: )
1969: is
1970: select cpr.rowid, cpr.*
1971: from ben_cwb_person_rates cpr
1972: where cpr.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
1973: and cpr.group_pl_id = c_group_pl_id
1974: and cpr.pl_id = c_pl_id
1975: and cpr.person_id =
1973: and cpr.group_pl_id = c_group_pl_id
1974: and cpr.pl_id = c_pl_id
1975: and cpr.person_id =
1976: (select cpr1.person_id
1977: from ben_cwb_person_rates cpr1
1978: where cpr1.group_pl_id = c_group_pl_id
1979: and cpr1.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
1980: and cpr1.pl_id = c_pl_id
1981: and rownum = 1);
2130: ,p_procd_dt => l_procd_dt
2131: ,p_strtd_dt => l_strtd_dt
2132: ,p_voidd_dt => l_voidd_dt);
2133: --
2134: -- Now clone the ben_cwb_person_rates, ben_cwb_group_rates.
2135: --
2136: if p_pl_id = ben_manage_cwb_life_events.g_cache_group_plan_rec.group_pl_id then
2137: --
2138: -- Per in ler created is a group per in ler so populate other
2259: -- For each of the person rates rows for a sample person
2260: -- copy data to current person.
2261: --
2262: -- Populate
2263: -- BEN_CWB_PERSON_RATES
2264: -- Primary Key: PERSON_RATE_ID
2265: --
2266: hr_utility.set_location ('p_pl_id ' || p_pl_id,60);
2267: hr_utility.set_location ('g_cache_group_plan_rec.group_pl_id '||
2292: close c_asg;
2293: --
2294: end if;
2295: --
2296: insert into ben_cwb_person_rates
2297: (person_rate_id ,
2298: group_per_in_ler_id ,
2299: pl_id ,
2300: oipl_id ,
2351: last_update_login ,
2352: created_by ,
2353: creation_date */
2354: ) values
2355: (ben_cwb_person_rates_s.nextval,
2356: nvl(g_cache_group_plan_rec.group_per_in_ler_id, -1),
2357: l_cpr_rec.pl_id,
2358: nvl(l_cpr_rec.oipl_id, -1),
2359: l_cpr_rec.group_pl_id,
3276: fnd_message.raise_error;
3277: --
3278: end global_process;
3279: --
3280: -- Procedure to populate ben_cwb_person_rates, ben_cwb_person_groups.
3281: --
3282: procedure populate_cwb_rates(
3283: --
3284: -- Columns needed for ben_cwb_person_rates
3280: -- Procedure to populate ben_cwb_person_rates, ben_cwb_person_groups.
3281: --
3282: procedure populate_cwb_rates(
3283: --
3284: -- Columns needed for ben_cwb_person_rates
3285: --
3286: p_person_id in number
3287: ,p_assignment_id in number default null
3288: ,p_organization_id in number default null
3485:
3486: end if;
3487: --
3488: -- Populate
3489: -- BEN_CWB_PERSON_RATES
3490: -- Primary Key: PERSON_RATE_ID
3491: --
3492: if ((p_pl_id = g_cache_group_plan_rec.group_pl_id and
3493: g_cache_group_plan_rec.plans_wthn_group_pl = 1
3538: hr_utility.set_location ('Person rate GROUP_PL_ID ' || g_cache_group_plan_rec.group_pl_id,15);
3539: hr_utility.set_location ('Person rate LF_EVT_OCRD_DT ' || g_cache_group_plan_rec.group_lf_evt_ocrd_dt,15);
3540: end if;
3541:
3542: insert into ben_cwb_person_rates
3543: (person_rate_id ,
3544: group_per_in_ler_id ,
3545: pl_id ,
3546: oipl_id ,
3597: last_update_login ,
3598: created_by ,
3599: creation_date
3600: ) values
3601: (ben_cwb_person_rates_s.nextval ,
3602: nvl(g_cache_group_plan_rec.group_per_in_ler_id, -1) ,
3603: p_pl_id ,
3604: nvl(p_oipl_id, -1),
3605: g_cache_group_plan_rec.group_pl_id ,
3911: p_effective_date in date default null) is
3912: --
3913: l_proc varchar2(72) := g_package||'.popu_missing_person_pil';
3914: --
3915: -- 9999 check whether person id is populated in ben_cwb_person_rates.
3916: --
3917: CURSOR c_person_rates
3918: (c_person_id IN NUMBER
3919: ,c_group_lf_evt_ocrd_dt IN DATE
3919: ,c_group_lf_evt_ocrd_dt IN DATE
3920: ,c_group_pl_id IN NUMBER
3921: )
3922: is
3923: select /*+ INDEX (CPR BEN_CWB_PERSON_RATES_FK3) */ cpr.rowid, cpr.*
3924: from ben_cwb_person_rates cpr
3925: where cpr.person_id = c_person_id
3926: and cpr.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
3927: and cpr.group_pl_id = c_group_pl_id
3920: ,c_group_pl_id IN NUMBER
3921: )
3922: is
3923: select /*+ INDEX (CPR BEN_CWB_PERSON_RATES_FK3) */ cpr.rowid, cpr.*
3924: from ben_cwb_person_rates cpr
3925: where cpr.person_id = c_person_id
3926: and cpr.lf_evt_ocrd_dt = c_group_lf_evt_ocrd_dt
3927: and cpr.group_pl_id = c_group_pl_id
3928: -- Bug 3517726 : Do not consider any data which is not deleted
4134: -- Initialise the copy budget structures.
4135: --
4136: g_cache_copy_person_bdgt_tbl := g_cache_copy_person_bdgt_tbl1;
4137: --
4138: -- Now update the group per in ler id on ben_cwb_person_rates
4139: --
4140: l_copy_person_bdgt_count := 0;
4141: for l_per_rt_rec in c_person_rates(p_person_id,
4142: p_group_lf_evt_ocrd_dt,
4142: p_group_lf_evt_ocrd_dt,
4143: p_group_pl_id
4144: ) loop
4145: --
4146: update ben_cwb_person_rates cpr
4147: set cpr.group_per_in_ler_id = l_curr_per_in_ler_id
4148: where cpr.rowid = l_per_rt_rec.rowid;
4149: --
4150: -- Write copy budget values into table structures
4438: l_group_per_in_ler_id number null;
4439: cursor c_null_gpil_cpr(cv_group_pl_id in number,
4440: cv_lf_evt_ocrd_dt in date) is
4441: select cpr.*, cpr.rowid
4442: from ben_cwb_person_rates cpr
4443: where GROUP_PER_IN_LER_ID = -1
4444: and GROUP_PL_ID = cv_group_pl_id
4445: and LF_EVT_OCRD_DT = cv_lf_evt_ocrd_dt;
4446: --
4497:
4498: Create potential, per in ler for the group_ler_id for the person.
4499: Insert row into ben_cwb_group_hrchy if row already not exists.
4500:
4501: Update the group_per_in_ler_id on ben_cwb_person_rates
4502: where group_per_in_ler_id is null and
4503: group_pl_id = p_group_pl_id and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt and
4504: assignment_id = p_assignment_id;
4505:
4564: end if;
4565: --
4566: end loop;
4567: --
4568: -- Now populate the group per in ler id on ben_cwb_person_rates table
4569: -- if it's missing.
4570: --
4571: g_error_log_rec.calling_proc := 'ben_cwb_person_rates';
4572: g_error_log_rec.step_number := 22;
4567: --
4568: -- Now populate the group per in ler id on ben_cwb_person_rates table
4569: -- if it's missing.
4570: --
4571: g_error_log_rec.calling_proc := 'ben_cwb_person_rates';
4572: g_error_log_rec.step_number := 22;
4573: --
4574: l_group_per_in_ler_id := null;
4575: for l_null_gpil_rec in c_null_gpil_cpr(g_cache_group_plan_rec.group_pl_id,
4581: g_cache_group_plan_rec.group_ler_id);
4582: fetch get_gpil_id into l_group_per_in_ler_id;
4583: close get_gpil_id;
4584: --
4585: update ben_cwb_person_rates
4586: set group_per_in_ler_id = l_group_per_in_ler_id
4587: where rowid = l_null_gpil_rec.rowid;
4588: --
4589: end loop;
4731: cv_pl_id in number,
4732: cv_lf_evt_ocrd_dt in date,
4733: cv_person_id in number) is
4734: select *
4735: from ben_cwb_person_rates
4736: where group_pl_id = cv_group_pl_id
4737: and pl_id = cv_pl_id
4738: and nvl(oipl_id, -1) = -1
4739: and person_id = cv_person_id
4743: cv_pl_id in number,
4744: cv_lf_evt_ocrd_dt in date,
4745: cv_person_id in number) is
4746: select sum(ws_val) ws_val, sum(nvl(ws_val, 0)) ws_val_0_if_null
4747: from ben_cwb_person_rates
4748: where group_pl_id = cv_group_pl_id
4749: and pl_id = cv_pl_id
4750: and nvl(oipl_id, -1) <> -1
4751: and person_id = cv_person_id
4761: if g_debug then
4762: hr_utility.set_location ('Entering :sum_oipl_rates_and_upd_pl_rate ' ,10);
4763: end if;
4764: --
4765: -- Fetch all ben_cwb_person_rates for the person
4766: -- for the plan, for lf_evt_ocrd_dt if there exists oipl for this plan
4767: --
4768: if g_options_exists is null then
4769: --
4794: then
4795: --
4796: -- Update plan level cpr.
4797: --
4798: update ben_cwb_person_rates
4799: set ws_val = l_oipl_ws_sum
4800: where person_rate_id = l_cpr_rec.person_rate_id;
4801: --
4802: end if;
4829: where cpg.group_pl_id = c_group_pl_id
4830: and cpg.lf_evt_ocrd_dt = c_lf_evt_ocrd_dt
4831: and exists
4832: (select null
4833: from ben_cwb_person_rates cpr,
4834: ben_cwb_group_hrchy cgh
4835: where cgh.emp_per_in_ler_id = cpr.GROUP_PER_IN_LER_ID
4836: and cpr.ELIG_FLAG = 'Y'
4837: and cgh.LVL_NUM > 1
4846: where cpg.group_pl_id = c_group_pl_id
4847: and cpg.lf_evt_ocrd_dt = c_lf_evt_ocrd_dt
4848: and not exists
4849: (select null
4850: from ben_cwb_person_rates cpr1,
4851: ben_cwb_group_hrchy cgh1
4852: where cgh1.emp_per_in_ler_id = cpr1.GROUP_PER_IN_LER_ID
4853: and cpr1.ELIG_FLAG = 'Y'
4854: and cgh1.LVL_NUM > 1
4854: and cgh1.LVL_NUM > 1
4855: and cgh1.mgr_per_in_ler_id = cpg.GROUP_PER_IN_LER_ID)
4856: and exists
4857: (select null
4858: from ben_cwb_person_rates cpr,
4859: ben_cwb_group_hrchy cgh
4860: where cgh.emp_per_in_ler_id = cpr.GROUP_PER_IN_LER_ID
4861: and cpr.ELIG_FLAG = 'Y'
4862: and cgh.LVL_NUM = 1