[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
08-Jun-02 pabodla 115.38 Do not select the contingent worker
assignment when assignment data is
fetched.
04-Sep-02 kmahendr 115.39 added codes in get_periods_between for new acty_ref_perd_cd.
15-Oct-02 kmahendr 115.40 Added overloaded function - get_periods_between and parameter
to annual_to_period - Bug#2556948
07-jan-03 vsethi 115.41 No copy changes
09-jan-03 kmahendr 115.42 Bug#2734491-Child rate is treated as parent- codes added in
annual to period
16-an-03 kmullapu 115.43 Bug 2745691. Added convert_pcr_rates_w
23-Jan-03 ikasire 115.45 Bug 2149438 Added overloaded funcrtions for
period_to_annual and annual_to_period to handle the
rounding externally.
13-feb-02 vsethi 115.31 Enclosed all hr_utility debug calls inside if
17-Mar-03 kmullapu 115.47 Bug 2745691:modified convert_pcr_rates_w to set
p_use_balance_flag:='Y' only when parent rate is SAREC or SAAEAR
26-Jun-03 lakrish 115.48 Bug 2992321, made ann_rt_val parameters
as IN OUT in convert_pcr_rates_w
12-Sep-03 rpillay 115.49 GRADE/STEP : Changes to set_default_dates to not throw error
for G mode when year periods are not set up.
26-Sep-03 rpillay 115.50 GRADE/STEP : Check for Grade Step program instead of
looking for 'G' mode
21-Oct-03 ikasire 115.51 BUG 3191928 fixes if the year period used it not right.
22-Oct-03 ikasire 115.52 BUG 3191928 fixed the typo order of select clause list
28-Oct-03 ikasire 115.53 BUG 3159774 c_count_periods_chq modified.see comments in the code
31-Oct-03 kmahendr 115.54 Bug#3231548 - added additional parameter to get_periods
between
31-oct-03 kmahendr 115.55 Bug#3231548 - the condition added to another tot-periods
28-jan-03 ikasire 115.56 Bug#3394862 - The estimate_balance procedure is calling
get_periods_between with the new payroll_id for determining
old periods in a different payroll. We need to use the one
on element entries with a nvl getting from the p_payroll_id
parameter.
10-Feb-03 ikasire 115.57 Bug 3430334. Search with tag 3430334 for more details.
12-Feb-03 ikasire 115.59 reverted the changes made in 115.58 until futher review by PM
not to get into some other patch before we complete the
review and testing.
26-Apr-04 kmahendr 115.60 Bug#3510633 - Added parameter person_id to function
annual_to_period
21-Jun-04 bmanyam 115.62 Bug# 3704632 - Added NVL() to p_end_date parameter
in the cursot to find number_of_periods in function get_periods_between().
22-Jun-04 bmanyam 115.63 Bug# 3704632 - Removed the above change. Added
select clause to fetch end-date of pay-year from per_time_periods
for 'Calender Month' and 'Lunar Month'
29-Nov-04 kmahendr 115.64 Codes added for new Rate Start date
03-Dec-04 vvprabhu 115.65 Bug 3980063 SSBEN Trace Enhancement
21-Dec-04 kmahendr 115.66 Bug#4037102 - nvl used to pass start date
27-Apr-05 swjain 115.67 Bug#4290565 Modified procedure prorate_min_max
25-Jul-05 kmahendr 115.68 Bug#4504449 - changed IYYY to YYYY in add_months
27-Jul-05 kmahendr 115.69 Bug#4504449 - l_periods defaulted to 1 in the
case of element with frequency rule in
get_periods_between
21-Mar-06 vborkar 115.70 5104247 Added p_child_rt_flag parameters to
convert_pcr_rates_w procedure.
27-Mar-06 kmahendr 115.71 Bug#5077258 - nvl added to return 0 for
balance if null in get_balance func
16-Aug-06 vborkar 115.72 5460638 For enterable rate, adjusted the defined rate(upto .01)
when it falls outside min-max window due to rounding error.
15-Nov-05 bmanyam 115.74 5642552 For Annual Rates, annual_to_period, the pay_period_amt
should be evaluated from pay_period_start NOT yr_start_dt.
20-Feb-07 rtagarra 115.75 ICM Changes
04-Dec-07 krupani 115.76 Incorporated changes of secure views and Bug 6455096 from branchline to mainline
12-Aug-08 ubhat 120.11 Forward port bug fix Bug 6830210:
14-Aug-08 veparame 120.12 For FwdPort of Bug 6913654: Modified get_periods_function to return correct number of periods.
Modified period_to_annual function to calculate correct annual rates for
'Estimate Only' rates
-- ==========================================================================================================
*/
--
--
g_package varchar2(80) := 'ben_distribute_rates';
select element_type_id
from ben_enrt_rt ert,
ben_acty_base_rt_f abr
where ert.enrt_rt_id=p_enrt_rt_id and
ert.business_group_id=p_business_group_id and
abr.acty_base_rt_id=ert.acty_base_rt_id and
p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select 'Y'
from pay_ele_payroll_freq_rules epf
where epf.element_type_id = l_element_type_id
and epf.payroll_id = p_payroll_id
and epf.business_group_id = p_business_group_id;
select ptp.start_date,
ptp.end_date
from per_time_periods ptp
where ptp.payroll_id = v_payroll_id
and ptp.end_date between
v_start_date and v_end_date;
select period_type
from pay_all_payrolls_f
where payroll_id = p_payroll_id
and p_date between effective_start_date
and effective_end_date;
select sum(power(2,(FRP.period_no_in_reset_period) - 1))
,decode(epf.reset_period_type,'Year','YYYY','MM')
from pay_ele_payroll_freq_rules EPF
, pay_freq_rule_periods FRP
where FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
and EPF.payroll_id = p_payroll_id
and EPF.element_type_id = p_element_type_id
group by epf.ele_payroll_freq_rule_id ,epf.reset_period_type;
select sum(
ben_distribute_rates.decde_bits(
bitand(power(2,count(end_date )) -1,p_frq_bitmap_no)
))
from per_time_periods
where payroll_id = p_payroll_id
and --end_date -- Bug 6830210
regular_payment_date -- Bug 6830210
between p_rt_start
and p_eoy
group by to_char(end_date,p_reset)
;
select count(1)
from per_time_periods ptp
where ptp.payroll_id = v_payroll_id
-- and nvl(ptp.regular_payment_date,ptp.end_date) between
and ptp.end_date between
v_start_date and v_end_date;
select MAX(end_date)
into l_max_end_date
from per_time_periods
where payroll_id = p_payroll_id
and TO_CHAR(end_date,'YYYY') =
(SELECT TO_CHAR(end_date,'YYYY')
from per_time_periods
where payroll_id = p_payroll_id
and p_start_date between start_date and end_date
);
select count(*)
into l_periods
from per_time_periods
where payroll_id = p_payroll_id
and end_date between p_start_date and NVL(p_end_date,l_max_end_date); -- 3704632 : Added NVL() here.
SELECT TPT.number_per_fiscal_year
INTO l_periods
FROM per_time_period_types TPT,
pay_payrolls_f PRL
WHERE TPT.period_type = PRL.period_type
AND PRL.business_group_id = p_business_group_id
AND PRL.payroll_id = p_payroll_id;
select element_type_id
from ben_enrt_rt ert,
ben_acty_base_rt_f abr
where ert.enrt_rt_id=p_enrt_rt_id and
ert.business_group_id=p_business_group_id and
abr.acty_base_rt_id=ert.acty_base_rt_id and
p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select 'Y'
from pay_ele_payroll_freq_rules epf
where epf.element_type_id = l_element_type_id
and epf.payroll_id = p_payroll_id
and epf.business_group_id = p_business_group_id;
select ptp.start_date,
ptp.end_date
from per_time_periods ptp
where ptp.payroll_id = v_payroll_id
and ptp.end_date between
v_start_date and v_end_date;
select period_type
,pay_date_offset
from pay_all_payrolls_f
where payroll_id = p_payroll_id
and p_date between effective_start_date
and effective_end_date;
select sum(power(2,(FRP.period_no_in_reset_period) - 1))
,decode(epf.reset_period_type,'Year','YYYY','MM')
from pay_ele_payroll_freq_rules EPF
, pay_freq_rule_periods FRP
where FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
and EPF.payroll_id = p_payroll_id
and EPF.element_type_id = p_element_type_id
group by epf.ele_payroll_freq_rule_id ,epf.reset_period_type;
select sum(
ben_distribute_rates.decde_bits(
bitand(power(2,count(end_date )) -1,p_frq_bitmap_no)
))
from per_time_periods
where payroll_id = p_payroll_id
and --end_date -- Bug 6830210
regular_payment_date -- Bug 6830210
between p_rt_start
and p_eoy
group by to_char(end_date,p_reset)
;
select count(1)
from per_time_periods ptp
where ptp.payroll_id = v_payroll_id
-- and nvl(ptp.regular_payment_date,ptp.end_date) between
and ptp.end_date between
v_start_date and v_end_date;
select count(1)
from per_time_periods ptp
where ptp.payroll_id = v_payroll_id
-- and ptp.end_date between
-- v_start_date and v_end_date
-- Bug 6455096
-- and ptp.end_date >= v_start_date
and ptp.regular_payment_date >= v_start_date
-- Bug 6455096
and nvl(ptp.regular_payment_date,ptp.end_date) between
v_start_date and v_end_date;
select count(*)
into l_periods
from per_time_periods
where payroll_id = p_payroll_id
and end_date
between p_start_date
and p_end_date;
SELECT TPT.number_per_fiscal_year
INTO l_periods
FROM per_time_period_types TPT,
pay_payrolls_f PRL
WHERE TPT.period_type = PRL.period_type
AND PRL.business_group_id = p_business_group_id
AND PRL.payroll_id = p_payroll_id;
select to_number(evl.screen_entry_value) entry_value,
evl.effective_start_date,
evl.effective_end_date,
ety.processing_type,
asg.payroll_id,
eln.element_type_id
from ben_prtt_rt_val prv,
ben_per_in_ler pil,
pay_element_entry_values_f evl,
per_all_assignments_f asg,
pay_element_entries_f een,
pay_element_links_f eln,
pay_element_types_f ety
where prv.acty_base_rt_id = p_acty_base_rt_id
and prv.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and prv.element_entry_value_id = evl.element_entry_value_id
and evl.element_entry_id = een.element_entry_id
and een.assignment_id = asg.assignment_id
and asg.person_id = p_person_id
and een.element_link_id = eln.element_link_id
and eln.element_type_id = ety.element_type_id
and prv.prtt_rt_val_stat_cd is null
and prv.business_group_id = p_business_group_id
and evl.effective_start_date <= p_date_to
and evl.effective_end_date >= p_date_from
and evl.effective_start_date between
prv.rt_strt_dt and prv.rt_end_dt
and evl.effective_start_date between
asg.effective_start_date and asg.effective_end_date
and evl.effective_start_date between
een.effective_start_date and een.effective_end_date
and evl.effective_start_date between
eln.effective_start_date and eln.effective_end_date
and evl.effective_start_date between
ety.effective_start_date and ety.effective_end_date;
select ecr.ptd_comp_lvl_fctr_id,
ecr.elig_per_elctbl_chc_id,
ecr.enrt_bnft_id,
ecr.acty_base_rt_id,
abr.det_pl_ytd_cntrs_cd,
abr.parnt_chld_cd
from ben_enrt_rt ecr,
ben_acty_base_rt_f abr
where ecr.enrt_rt_id = p_enrt_rt_id
and ecr.business_group_id = p_business_group_id
and ecr.acty_base_rt_id = abr.acty_base_rt_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select epe.per_in_ler_id,
epe.pgm_id,
epe.pl_id,
epe.oipl_id,
pel.lee_rsn_id,
pel.enrt_perd_id,
pil.person_id,
pil.lf_evt_ocrd_dt
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
where epe.elig_per_elctbl_chc_id = l_ecr.elig_per_elctbl_chc_id
and epe.per_in_ler_id = pil.per_in_ler_id
and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and pel.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
UNION
select epe.per_in_ler_id,
epe.pgm_id,
epe.pl_id,
epe.oipl_id,
pel.lee_rsn_id,
pel.enrt_perd_id,
pil.person_id,
pil.lf_evt_ocrd_dt
from ben_enrt_bnft enb,
ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
where enb.enrt_bnft_id = l_ecr.enrt_bnft_id
and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and pel.per_in_ler_id = pil.per_in_ler_id
and epe.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
select clf.comp_src_cd,
clf.defined_balance_id
from ben_comp_lvl_fctr clf
where clf.comp_lvl_fctr_id = l_ecr.ptd_comp_lvl_fctr_id
and clf.business_group_id = p_business_group_id;
select max(pac.effective_date) + 1
from pay_person_latest_balances plb,
per_all_assignments_f asg,
pay_assignment_actions paa,
pay_payroll_actions pac
where plb.person_id = l_epe.person_id
and asg.assignment_type <> 'C'
and plb.defined_balance_id = l_clf.defined_balance_id
and asg.person_id = l_epe.person_id
and asg.primary_flag = 'Y'
and asg.assignment_id = paa.assignment_id
and paa.payroll_action_id = pac.payroll_action_id
and asg.business_group_id = p_business_group_id
and p_effective_date between
asg.effective_start_date and asg.effective_end_date
and pac.effective_date between
v_start_date and v_end_date;
select abr2.det_pl_ytd_cntrs_cd
from ben_acty_base_rt_f abr,
ben_acty_base_rt_f abr2
where abr.acty_base_rt_id = c_acty_base_rt_id
and abr2.acty_base_rt_id = abr.parnt_acty_base_rt_id
and abr2.parnt_chld_cd = 'PARNT'
and c_effective_date
between abr.effective_start_date
and abr.effective_end_date
and c_effective_date
between abr2.effective_start_date
and abr2.effective_end_date;
select epe.enrt_cvg_strt_dt,
epe.enrt_cvg_strt_dt_cd,
epe.enrt_cvg_strt_dt_rl,
pel.enrt_perd_strt_dt,
epe.yr_perd_id,
pel.acty_ref_perd_cd,
--BUG 3191928
epe.per_in_ler_id,
epe.pgm_id,
epe.pl_id,
epe.oipl_id,
pel.lee_rsn_id,
pel.enrt_perd_id,
pil.person_id,
pil.lf_evt_ocrd_dt,
--END BUG 3191928
epe.pl_typ_id -- ICM
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.business_group_id = p_business_group_id
and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and pel.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
select ecr.rt_strt_dt,
ecr.rt_strt_dt_cd,
ecr.rt_strt_dt_rl,
pel.enrt_perd_strt_dt,
epe.elig_per_elctbl_chc_id,
epe.yr_perd_id,
pel.acty_ref_perd_cd,
ecr.acty_base_rt_id,
--BUG 3191928
epe.per_in_ler_id,
epe.pgm_id,
epe.pl_id,
epe.oipl_id,
pel.lee_rsn_id,
pel.enrt_perd_id,
pil.person_id,
pil.lf_evt_ocrd_dt
--END BUG 3191928
from ben_enrt_rt ecr,
ben_enrt_bnft enb,
ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
where ecr.enrt_rt_id = p_enrt_rt_id
and ecr.business_group_id = p_business_group_id
and decode(ecr.enrt_bnft_id, null, ecr.elig_per_elctbl_chc_id,
enb.elig_per_elctbl_chc_id) =
epe.elig_per_elctbl_chc_id
and enb.enrt_bnft_id (+) = ecr.enrt_bnft_id
and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and pel.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
select yrp.end_date end_date,
yrp.start_date start_date
from ben_yr_perd yrp
where yrp.yr_perd_id = l_yr_perd_id
and yrp.business_group_id = p_business_group_id;
SELECT yp.end_date end_date,
yp.start_date start_date
FROM ben_popl_yr_perd pyp,
ben_yr_perd yp
WHERE pyp.pl_id = l_pl_id
AND pyp.yr_perd_id = yp.yr_perd_id
AND pyp.business_group_id = p_business_group_id
AND l_start_date between yp.start_date AND yp.end_date
AND yp.business_group_id = p_business_group_id ;
select min(ptp.start_date)
from per_time_periods ptp
where ptp.payroll_id = v_payroll_id
and ptp.start_date between
v_start_date and v_end_date;
select pgm.pgm_typ_cd
from ben_pgm_f pgm,
ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
and pgm.pgm_id = epe.pgm_id
and p_effective_date between pgm.effective_start_date
and pgm.effective_end_date;
select ptp.opt_typ_cd
from ben_pl_typ_f ptp
where ptp.pl_typ_id = p_pl_typ_id
and p_effective_date between ptp.effective_start_date
and ptp.effective_end_date;
select
abr.det_pl_ytd_cntrs_cd
from ben_enrt_rt ecr,
ben_acty_base_rt_f abr
where ecr.enrt_rt_id = p_enrt_rt_id
and ecr.business_group_id = p_business_group_id
and ecr.acty_base_rt_id = abr.acty_base_rt_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select cmcd_val
from ben_enrt_rt ecr
where ecr.enrt_rt_id = p_enrt_rt_id
and ecr.business_group_id = p_business_group_id;
select
abr.det_pl_ytd_cntrs_cd
from ben_enrt_rt ecr,
ben_acty_base_rt_f abr
where ecr.enrt_rt_id = p_enrt_rt_id
and ecr.business_group_id = p_business_group_id
and ecr.acty_base_rt_id = abr.acty_base_rt_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select cmcd_val
from ben_enrt_rt ecr
where ecr.enrt_rt_id = p_enrt_rt_id
and ecr.business_group_id = p_business_group_id;
select
abr.det_pl_ytd_cntrs_cd,
abr.entr_ann_val_flag,
abr.rt_mlt_cd
from ben_enrt_rt ecr,
ben_acty_base_rt_f abr
where ecr.enrt_rt_id = p_enrt_rt_id
and ecr.business_group_id = p_business_group_id
and ecr.acty_base_rt_id = abr.acty_base_rt_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select pay_date_offset
from pay_all_payrolls_f prl
where prl.payroll_id = p_payroll_id
and p_effective_date between prl.effective_start_date
and prl.effective_end_date;
select payroll_id
from per_all_assignments_f ass
where ass.person_id = p_person_id
and ass.primary_flag = 'Y'
and ass.assignment_type <> 'C'
and p_effective_date between ass.effective_start_date
and ass.effective_end_date
order by decode(ass.assignment_type, 'E',1,'B',2,3);
select
abr.det_pl_ytd_cntrs_cd,
abr.entr_ann_val_flag,
abr.rt_mlt_cd
from ben_enrt_rt ecr,
ben_acty_base_rt_f abr
where ecr.enrt_rt_id = p_enrt_rt_id
and ecr.business_group_id = p_business_group_id
and ecr.acty_base_rt_id = abr.acty_base_rt_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select pay_date_offset
from pay_all_payrolls_f prl
where prl.payroll_id = p_payroll_id
and p_effective_date between prl.effective_start_date
and prl.effective_end_date;
select payroll_id
from per_all_assignments_f ass
where ass.person_id = p_person_id
and ass.primary_flag = 'Y'
and ass.assignment_type <> 'C'
and p_effective_date between ass.effective_start_date
and ass.effective_end_date
order by decode(ass.assignment_type, 'E',1,'B',2,3);
select
abr.det_pl_ytd_cntrs_cd,
abr.entr_ann_val_flag,
abr.rt_mlt_cd
from ben_enrt_rt ecr,
ben_acty_base_rt_f abr
where ecr.enrt_rt_id = p_enrt_rt_id
and ecr.business_group_id = p_business_group_id
and ecr.acty_base_rt_id = abr.acty_base_rt_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select pay_date_offset
from pay_all_payrolls_f prl
where prl.payroll_id = p_payroll_id
and p_effective_date between prl.effective_start_date
and prl.effective_end_date;
select pel.enrt_perd_id,
pel.lee_rsn_id,
epe.yr_perd_id
from ben_pil_elctbl_chc_popl pel,
ben_elig_per_elctbl_chc epe
where pel.per_in_ler_id = p_per_in_ler_id
and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
and pel.per_in_ler_id = epe.per_in_ler_id
and ((p_pgm_id is not null and
pel.pgm_id = p_pgm_id)
OR
(p_pgm_id is null and
pel.pl_id = p_pl_id));
select pil.lf_evt_ocrd_dt,
epe.pgm_id,
epe.pl_id,
epe.oipl_id,
epe.per_in_ler_id,
epe.yr_perd_id,
pel.enrt_perd_id,
pel.lee_rsn_id,
pil.business_group_id
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and pel.per_in_ler_id = pil.per_in_ler_id;
select yrp.start_date
from ben_yr_perd yrp
where yrp.yr_perd_id = l_get_epe.yr_perd_id
and yrp.business_group_id = l_get_epe.business_group_id;
select abr.ptd_comp_lvl_fctr_id,
abr.clm_comp_lvl_fctr_id,
abr.det_pl_ytd_cntrs_cd
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = p_acty_base_rt_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select abr.prort_mn_ann_elcn_val_cd
,abr.prort_mx_ann_elcn_val_cd
,abr.prort_mn_ann_elcn_val_rl
,abr.prort_mx_ann_elcn_val_rl
,abr.element_type_id
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = p_acty_base_rt_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select yrp.start_date, yrp.end_date, epe.pgm_id, epe.pl_id,
epe.pl_typ_id, epe.business_group_id, pil.ler_id,
oipl.opt_id
from ben_yr_perd yrp
,ben_elig_per_elctbl_chc epe
,ben_per_in_ler pil
,ben_oipl_f oipl
where epe.yr_perd_id = yrp.yr_perd_id
and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.per_in_ler_id = pil.per_in_ler_id
and epe.oipl_id = oipl.oipl_id(+)
and p_effective_date between
nvl(oipl.effective_start_date, p_effective_date)
and nvl(oipl.effective_end_date, p_effective_date);
select pgm.acty_ref_perd_cd
from ben_pgm_f pgm
where pgm.pgm_id = p_pgm_id
and p_effective_date between
pgm.effective_start_date and pgm.effective_end_date;
select pl.nip_acty_ref_perd_cd
from ben_pl_f pl
where pl.pl_id = p_pl_id
and p_effective_date between
pl.effective_start_date and pl.effective_end_date;
select asg.assignment_id, asg.organization_id, loc.region_2 state, asg.location_id,
asg.payroll_id
from hr_locations_all loc, per_all_assignments_f asg
where asg.person_id = p_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and loc.location_id(+) = asg.location_id
and p_effective_date between
asg.effective_start_date and asg.effective_end_date
order by 1;
g_period_to_annual_cache.delete;
g_annual_to_period_cache.delete;
g_period_to_annual_cache.delete;
g_annual_to_period_cache.delete;
select payroll_id from
per_all_assignments_f
where person_id = p_person_id
and assignment_type <> 'C'
and p_effective_date between effective_start_date and effective_end_date
and primary_flag = 'Y';
is select abr.rate_periodization_rl, ecr.acty_base_rt_id
from ben_enrt_rt ecr,
ben_acty_base_rt_f abr
where ecr.enrt_rt_id = cv_enrt_rt_id
and abr.acty_base_rt_id = ecr.acty_base_rt_id
and cv_effective_date between abr.effective_start_date
and abr.effective_end_date ;
select pbg.legislation_code
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id;
select mn_elcn_val, mx_elcn_val
from ben_enrt_rt ecr
where ecr.enrt_rt_id = p_enrt_rt_id;
Select 'N'
From ben_acty_base_rt_f
Where acty_base_rt_id=p_prnt_acty_base_rt_id
and p_effective_date between effective_start_date and effective_end_date
and (nvl(rt_mlt_cd,'XX')='SAREC' or entr_ann_val_flag='Y');
select
abr.val
,abr.RT_TYP_CD
,ecr.cmcd_acty_ref_perd_cd
from ben_acty_base_rt_f abr,
ben_enrt_rt ecr
where
abr.PARNT_ACTY_BASE_RT_ID= p_prnt_acty_base_rt_id
and abr.rt_mlt_cd='PRNT'
and abr.ACTY_BASE_RT_ID = ecr.ACTY_BASE_RT_ID
and ecr.enrt_rt_id=c_enrt_rate_id
and p_effective_date between effective_start_date and effective_end_date;