[Home] [Help]
560: prv.rt_ovridn_flag,
561: prv.rt_ovridn_thru_dt,
562: prv.ann_rt_val,
563: prv.cmcd_rt_val
564: from ben_prtt_rt_val prv
565: where prv.prtt_enrt_rslt_id = l_pen.prtt_enrt_rslt_id
566: and prv.acty_base_rt_id = v_acty_base_rt_id
567: /* Bug 13539277: If there is no electability for the LE and if there exists future dated
568: rate record, then consider the future rate record to check whether there is rate change*/
569: and ((v_rt_strt_dt is not null and (
570: (
571: (v_rt_strt_dt between prv.rt_strt_dt and prv.rt_end_dt)
572: and not exists
573: (select '1' from ben_prtt_rt_val prv1
574: where prv1.prtt_enrt_rslt_id = l_pen.prtt_enrt_rslt_id
575: and prv1.acty_base_rt_id = v_acty_base_rt_id
576: and prv1.prtt_rt_val_stat_cd is null
577: and prv1.rt_strt_dt > v_rt_strt_dt)
578: ) or
579: (prv.rt_end_dt = hr_api.g_eot
580: and prv.rt_strt_dt > v_rt_strt_dt
581: and exists
582: (select '1' from ben_prtt_rt_val prv1
583: where prv1.prtt_enrt_rslt_id = l_pen.prtt_enrt_rslt_id
584: and prv1.acty_base_rt_id = v_acty_base_rt_id
585: and prv1.prtt_rt_val_stat_cd is null
586: and prv1.rt_strt_dt > v_rt_strt_dt))
594: --
595: cursor c_prv_min_dt(p_pen_id number,
596: p_abr_id number) is
597: select min(prv.rt_strt_dt)
598: from ben_prtt_rt_val prv
599: where prv.prtt_enrt_rslt_id = p_pen_id
600: and prv.acty_base_rt_id = p_abr_id
601: and prv.prtt_rt_val_stat_cd is null;
602:
1224: --
1225: -- Bug 7566569 : Check whether the element / rate is a non-recurring one. If it is non-recurring,
1226: -- we should not re-create it while running Recalculate Participant Values program
1227: --
1228: BEN_PRTT_RT_VAL_API.get_non_recurring_end_dt
1229: (p_rt_strt_dt => l_rt_strt_dt
1230: ,p_acty_base_rt_id => l_ecr.acty_base_rt_id
1231: ,p_business_group_id => p_business_group_id
1232: ,p_rt_end_dt => l_rt_end_dt_non_rec
2373: select
2374: prv.rt_val,
2375: prv.rt_strt_dt,
2376: prv.object_version_number
2377: from ben_prtt_rt_val prv
2378: where prv.prtt_rt_val_id = p_prtt_rt_val_id and
2379: prv.business_group_id = p_business_group_id and
2380: prv.prtt_rt_val_stat_cd is null
2381: ;
2384: --
2385: cursor c_prv_future is
2386: select
2387: prv.*
2388: from ben_prtt_rt_val prv
2389: where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id and
2390: prv.business_group_id = p_business_group_id and
2391: prv.acty_base_rt_id = l_acty_base_rt_id and
2392: prv.prtt_rt_val_stat_cd is null
2395: --
2396: cursor c_prv_past is
2397: select
2398: prv.*
2399: from ben_prtt_rt_val prv
2400: where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id and
2401: prv.rt_end_dt = (l_prv.rt_strt_dt -1) and
2402: prv.business_group_id = p_business_group_id and
2403: prv.acty_base_rt_id = l_acty_base_rt_id and
2446: l_acty_base_rt_id := l_ecr.acty_base_rt_id;
2447: --
2448:
2449: if l_prv.rt_strt_dt < p_rt_strt_dt and p_mode = 'UPD' then
2450: ben_prtt_rt_val_api.delete_prtt_rt_val
2451: (p_prtt_rt_val_id => p_prtt_rt_val_id
2452: ,p_person_id => p_person_id
2453: ,p_business_group_id => p_business_group_id
2454: ,p_object_version_number => l_prv.object_version_number
2478: end if;
2479: --
2480: if l_prv_future.rt_strt_dt > p_rt_strt_dt then
2481: --
2482: ben_prtt_rt_val_api.delete_prtt_rt_val
2483: (p_prtt_rt_val_id => l_prv_future.prtt_rt_val_id
2484: ,p_person_id => p_person_id
2485: ,p_business_group_id => p_business_group_id
2486: ,p_object_version_number => l_prv_future.object_version_number
2501: -- do nothing for non recurring rate
2502: null;
2503: else
2504:
2505: ben_prtt_rt_val_api.update_prtt_rt_val(
2506: p_validate => FALSE
2507: ,p_prtt_rt_val_id => l_prv_future.prtt_rt_val_id
2508: ,p_person_id => p_person_id
2509: ,p_input_value_id => l_abr.input_value_id