[Home] [Help]
137:
138: CURSOR c_rate_ovn (v_group_per_in_ler_id IN NUMBER, v_pl_id IN NUMBER, v_oipl_id IN NUMBER)
139: IS
140: SELECT object_version_number
141: FROM ben_cwb_person_rates rt
142: WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
143: AND rt.pl_id = v_pl_id
144: AND rt.oipl_id = v_oipl_id;
145:
249: , ben_per_in_ler mgr_pil
250: , ben_cwb_group_hrchy hrchy
251: , per_business_groups_perf bg
252: , ben_cwb_person_info per
253: , ben_cwb_person_rates rates
254: , ben_cwb_pl_dsgn dsgn
255: WHERE pil.per_in_ler_stat_cd = 'PROCD'
256: AND pil.group_pl_id = v_pl_id
257: AND pil.lf_evt_ocrd_dt = v_lf_evt_orcd_date
325: AND hrchy.lvl_num > 0)
326: )
327: and not exists(
328: select null from
329: ben_cwb_person_rates
330: where group_per_in_ler_id = pil.per_in_ler_id
331: )
332: and (v_business_group_id is null or
333: per.business_group_id = v_business_group_id)
357: --, ben_cwb_group_hrchy mgr_hrchy
358: , per_business_groups_perf bg
359: , ben_cwb_person_info per
360: , ben_cwb_person_info mgr_per
361: --, ben_cwb_person_rates rates
362: --, ben_cwb_pl_dsgn dsgn
363: WHERE pil.per_in_ler_stat_cd = 'PROCD'
364: AND pil.group_pl_id = v_pl_id
365: AND pil.lf_evt_ocrd_dt = v_lf_evt_orcd_date
395:
396: CURSOR c_check_eligibility (v_group_per_in_ler_id IN NUMBER)
397: IS
398: select null
399: from ben_cwb_person_rates
400: where group_per_in_ler_id = v_group_per_in_ler_id
401: and oipl_id = -1
402: and elig_flag = 'Y';
403:
477:
478: CURSOR c_task_type (v_group_per_in_ler_id IN NUMBER)
479: IS
480: SELECT dsgn.ws_sub_acty_typ_cd
481: FROM ben_cwb_person_rates rt
482: , ben_cwb_pl_dsgn dsgn
483: WHERE rt.group_per_in_ler_id = v_group_per_in_ler_id
484: AND rt.pl_id = dsgn.pl_id
485: AND rt.oipl_id = dsgn.oipl_id
682: FROM ben_transaction trans,
683: ben_cwb_pl_dsgn dsgn
684: WHERE trans.transaction_id IN
685: (SELECT DISTINCT pl_id
686: FROM ben_cwb_person_rates rates
687: WHERE group_per_in_ler_id = v_group_per_in_ler_id)
688: AND trans.transaction_type = 'CWBPPOVDT' || to_char(v_lf_evt_ocrd_dt,'yyyy/mm/dd');
689:
690:
713: , xchg.xchg_rate
714: , rt.elig_flag
715: , rt.currency
716: , rt.comp_posting_date
717: FROM ben_cwb_person_rates rt
718: , ben_oipl_f oipl
719: , ben_cwb_pl_dsgn dsgn
720: , ben_opt_f opt
721: , ben_cwb_person_info info
775: , rt.elig_flag
776: , info.fte_factor
777: , rt.currency
778: , rt.comp_posting_date
779: FROM ben_cwb_person_rates rt
780: , ben_cwb_pl_dsgn dsgn
781: , ben_cwb_person_info info
782: , ben_per_in_ler pil
783: , ben_oipl_f oipl
2218: -- Ignore proration etc., YES
2219: -- Element entry is shared by mutliple cwb entries ? NO
2220: --
2221: -- Assumptions.
2222: -- ben_cwb_person_rates.ELEMENT_ENTRY_VALUE_ID will be passed this routine.
2223: -- Element type from ben_cwb_plan_design overrides from abr.
2224: -- Abr information have to be fetched similar to cwb post process.
2225: --
2226: --
2386: ) is
2387: -- CWBGLOBAL
2388: cursor c_cwb_person_rates is
2389: select distinct pl_id, oipl_id, pay_proposal_id
2390: from ben_cwb_person_rates
2391: where group_per_in_ler_id = p_per_in_ler_id
2392: order by pay_proposal_id;
2393: --
2394: l_pl_id number;
2404: rates.object_version_number,
2405: rates.ws_val,
2406: per_saladmin_utility.get_pay_annualization_factor(info.assignment_id,rates.comp_posting_date,null,'PERIOD') pay_annual_ftr,
2407: dsgn.pl_annulization_factor plan_pay_annual_ftr --12714771
2408: from ben_cwb_person_rates rates,
2409: ben_cwb_person_info info,
2410: ben_cwb_pl_dsgn dsgn
2411: where rates.group_per_in_ler_id = p_per_in_ler_id
2412: and rates.pl_id = l_pl_id
2446: select
2447: nvl(plRt.ws_val,0) ws_val
2448: from
2449: ben_cwb_pl_dsgn pl
2450: ,ben_cwb_person_rates plRt
2451: ,ben_cwb_person_info per
2452: where plRt.group_per_in_ler_id = p_per_in_ler_id
2453: and plRt.group_per_in_ler_id = per.group_per_in_ler_id
2454: and plRt.pl_id = pl.pl_id
2463: (per.grd_min_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_min,
2464: (per.grd_max_val*per.grade_annulization_factor/pl.pl_annulization_factor) grd_max
2465: from
2466: ben_cwb_pl_dsgn pl
2467: ,ben_cwb_person_rates plRt
2468: ,ben_cwb_person_info per
2469: where plRt.group_per_in_ler_id = p_per_in_ler_id
2470: and plRt.group_per_in_ler_id = per.group_per_in_ler_id
2471: and plRt.pl_id = pl.pl_id
2477: select
2478: nvl(optRt.ws_val,0) ws_val
2479: from
2480: ben_cwb_pl_dsgn pl
2481: ,ben_cwb_person_rates optRt
2482: ,ben_cwb_person_info per
2483: where optRt.group_per_in_ler_id = p_per_in_ler_id
2484: and optRt.group_per_in_ler_id = per.group_per_in_ler_id
2485: and optRt.pl_id = pl.pl_id
2696: );
2697:
2698: end if;
2699: --
2700: -- Bug 5130397 : When CWB plan has options attached, then all corresponding rows in BEN_CWB_PERSON_RATES
2701: -- has pay_proposal_id populated and this being same ID, we should not call delete API
2702: -- more than once. Hence added following check : l_pay_proposal_id <> l_pay_proposal_id_prev
2703: --
2704: if l_pay_proposal_id is not null AND
2962:
2963: --12581640
2964: CURSOR c_posting_date(v_group_per_in_ler_id number) is
2965: select min(comp_posting_date)
2966: from ben_cwb_person_rates
2967: where group_per_in_ler_id = v_group_per_in_ler_id
2968: and comp_posting_date is not null ;
2969: l_posting_date date;
2970: