[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
115.24 08-Jun-02 pabodla Do not select the contingent worker
assignment when assignment data is
fetched.
115.25 11-Oct-02 vsethi Rates Sequence no enhancements. Modified to cater
to new column ord_num on ben_acty_base_rt_f
115.26 30-Dec-2002 mmudigon NOCOPY
115.27 13-feb-2003 kmahendr Added a parameter to call -acty_base_rt.main
-----------------------------------------------------------------------------
*/
--
-- Globals.
--
g_package varchar2(50) := 'ben_efc_adjustments.';
select abr2.acty_base_rt_id,
abr2.entr_val_at_enrt_flag,
abr2.val
from ben_acty_base_rt_f abr,
ben_acty_base_rt_f abr2
where abr.acty_base_rt_id = c_abr_id
and abr2.acty_base_rt_id = abr.parnt_acty_base_rt_id
and abr2.parnt_chld_cd = 'PARNT'
and c_eff_date
between abr.effective_start_date
and abr.effective_end_date
and c_eff_date
between abr2.effective_start_date
and abr2.effective_end_date;
select epe.pgm_id,
epe.pl_id
from ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = c_epe_id;
select pln.pl_id
from ben_pl_f pln
where pln.pl_id = c_pln_id
and c_eff_date
between pln.effective_start_date
and pln.effective_end_date;
p_faterr_type := 'DELETEDINFO';
p_faterr_type := 'DELETEDINFO';
,p_last_update_date in date
,p_object_version_number in number
--
,p_who_counts in out nocopy g_who_counts
,p_faterr_code out nocopy varchar2
,p_faterr_type out nocopy varchar2
)
is
--
l_gap number;
<> nvl(p_last_update_date,hr_api.g_sot)
then
--
p_who_counts.mod_count := p_who_counts.mod_count+1;
l_gap := (p_last_update_date-p_creation_date)*(24*60);
select pil.per_in_ler_id
from ben_per_in_ler pil
where pil.person_id = c_person_id
order by pil.per_in_ler_id desc;
select pil.lf_evt_ocrd_dt,
pil.person_id,
pil.per_in_ler_id,
pil.business_group_id,
pil.ler_id,
pil.per_in_ler_stat_cd
from BEN_ELIG_PER_ELCTBL_CHC epe,
ben_per_in_ler pil,
per_all_people_f per
where pil.per_in_ler_id = epe.per_in_ler_id
and epe.ELIG_PER_ELCTBL_CHC_id = c_epe_id
and per.person_id = pil.person_id
and pil.lf_evt_ocrd_dt
between per.effective_start_date and per.effective_end_date;
select pil.lf_evt_ocrd_dt,
pil.person_id,
pil.per_in_ler_id,
pil.business_group_id,
pil.ler_id,
pil.per_in_ler_stat_cd,
enb.val
from ben_enrt_bnft enb,
BEN_ELIG_PER_ELCTBL_CHC epe,
ben_per_in_ler pil,
per_all_people_f per
where enb.ELIG_PER_ELCTBL_CHC_id = epe.ELIG_PER_ELCTBL_CHC_id
and pil.per_in_ler_id = epe.per_in_ler_id
and enb.enrt_bnft_id = c_enb_id
and per.person_id = pil.person_id
and pil.lf_evt_ocrd_dt
between per.effective_start_date and per.effective_end_date;
select pil.lf_evt_ocrd_dt,
pil.person_id,
pil.per_in_ler_id,
pil.business_group_id,
pil.ler_id,
pil.per_in_ler_stat_cd,
enb.val
from ben_enrt_bnft enb,
BEN_ELIG_PER_ELCTBL_CHC epe,
ben_per_in_ler pil
where enb.ELIG_PER_ELCTBL_CHC_id = epe.ELIG_PER_ELCTBL_CHC_id
and pil.per_in_ler_id = epe.per_in_ler_id
and enb.enrt_bnft_id = c_enb_id;
p_faterr_type := 'DELETEDINFO';
p_faterr_type := 'DELETEDINFO';
,p_last_update_date in date
--
,p_actl_prem_id in number default null
,p_acty_base_rt_id in number default null
,p_cvg_amt_calc_mthd_id in number default null
--
,p_vpfdets out nocopy gc_vpfdets%rowtype
,p_vpf_id out nocopy number
,p_faterr_code out nocopy varchar2
,p_faterr_type out nocopy varchar2
)
is
--
l_vpfdets gc_vpfdets%rowtype;
select avr.creation_date,
avr.last_update_date
from ben_acty_vrbl_rt_f avr
where avr.vrbl_rt_prfl_id = c_vpf_id
and avr.ACTY_BASE_RT_ID = c_abr_id
and c_eff_date
between avr.effective_start_date
and avr.effective_end_date;
if l_vpfdets.last_update_date > nvl(p_last_update_date,hr_api.g_eot)
then
--
p_faterr_code := 'VPFCORR';
if l_avrdets.last_update_date > p_last_update_date
then
--
p_faterr_code := 'VPFAVRCORR';
select fcu.derive_factor
from fnd_currencies fcu
where fcu.currency_code = c_curr_code;
procedure Insert_fndsession_row
(p_ses_date in date
)
is
--
/*
PRAGMA AUTONOMOUS_TRANSACTION;
end Insert_fndsession_row;
procedure insert_validation_exceptions
(p_val_set in g_failed_adj_values_tbl
,p_efc_action_id in number
,p_ent_scode in varchar2
,p_exception_type in varchar2
)
is
--
PRAGMA AUTONOMOUS_TRANSACTION;
insert into ben_efc_exclusions
(efc_action_id
,ent_scode
,exclusion_type
,pk_id
,effective_start_date
,effective_end_date
,exclusion_code
,old_val
,new_val
,object_version_number
,business_group_id
,creation_date
,last_update_date
,created_by
,last_updated_by
)
values
(p_efc_action_id
,p_ent_scode
,l_exception_type
,p_val_set(ele_num).id
,l_esd
,l_eed
,p_val_set(ele_num).faterr_code
,p_val_set(ele_num).old_val1
,p_val_set(ele_num).new_val1
,p_val_set(ele_num).ovn
,p_val_set(ele_num).bgp_id
,p_val_set(ele_num).credt
,p_val_set(ele_num).lud
,p_val_set(ele_num).cre_by
,p_val_set(ele_num).lu_by
);
end insert_validation_exceptions;
,last_update_date ben_elig_per_f.last_update_date%type
,object_version_number ben_elig_per_f.object_version_number%type
);
g_pep_success_adj_val_set.delete;
g_pep_rcoerr_val_set.delete;
g_pep_failed_adj_val_set.delete;
g_pep_fatal_error_val_set.delete;
l_sql_str := ' select pep.elig_per_id, '
||' pep.effective_start_date, '
||' pep.effective_end_date, '
||' pep.comp_ref_amt, '
||' pep.rt_comp_ref_amt, '
||' pep.comp_ref_uom, '
||' pep.rt_comp_ref_uom, '
||' pep.person_id, '
||' pep.pgm_id, '
||' pep.ptip_id, '
||' pep.plip_id, '
||' pep.pl_id, '
||' pep.business_group_id, '
||' pil.lf_evt_ocrd_dt, '
||' pil.per_in_ler_id, '
||' pep.creation_date, '
||' pep.last_update_date, '
||' pep.object_version_number '
||l_from_str
||l_where_str
||' order by pep.elig_per_id ';
hr_efc_info.insert_line('-- ');
hr_efc_info.insert_line('-- Adjusting elig pers ');
hr_efc_info.insert_line('-- ');
ben_manage_life_events.g_cache_proc_object.delete;
g_pep_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
g_pep_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
g_pep_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
,p_last_update_date => l_efc_row.last_update_date
,p_object_version_number => l_efc_row.object_version_number
--
,p_who_counts => l_who_counts
,p_faterr_code => l_faterr_code
,p_faterr_type => l_faterr_type
);
update ben_elig_per_f pep
set pep.comp_ref_amt = l_comp_rec.comp_ref_amt,
pep.rt_comp_ref_amt = l_comp_rec.rt_comp_ref_amt
where pep.elig_per_id = l_efc_row.elig_per_id
and pep.effective_start_date = l_efc_row.effective_start_date
and pep.effective_end_date = l_efc_row.effective_end_date;
g_pep_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
g_pep_fatal_error_val_set(l_faterrs_count).lud := l_efc_row.last_update_date;
g_pep_success_adj_val_set(l_calsucc_count).lud := l_efc_row.last_update_date;
,last_update_date ben_elig_per_opt_f.last_update_date%type
,object_version_number ben_elig_per_opt_f.object_version_number%type
,per_in_ler_id ben_elig_per_opt_f.per_in_ler_id%type
);
select cop.oipl_id
FROM ben_oipl_f cop
where cop.opt_id = c_opt_id
and cop.pl_id = c_pl_id
and c_eff_date
between cop.effective_start_date and cop.effective_end_date;
select opp.oiplip_id
FROM ben_oiplip_f opp
where opp.oipl_id = c_oipl_id
and opp.plip_id = c_plip_id
and c_eff_date
between opp.effective_start_date and opp.effective_end_date;
select pbb.per_bnfts_bal_id,
pbb.last_update_date,
pbb.object_version_number,
pbb.effective_end_date
FROM ben_per_bnfts_bal_f pbb
where pbb.person_id = c_person_id
order by pbb.per_bnfts_bal_id;
select epo.elig_per_opt_id,
count(*)
FROM ben_elig_per_opt_f epo
where epo.per_in_ler_id = c_per_in_ler_id
and epo.elig_per_opt_id = c_elig_per_opt_id
group by epo.elig_per_opt_id
having count(*) > 1;
g_epo_success_adj_val_set.delete;
g_epo_failed_adj_val_set.delete;
g_epo_rcoerr_val_set.delete;
g_epo_fatal_error_val_set.delete;
l_sql_str := ' select epo.elig_per_opt_id, '
||' epo.effective_start_date, '
||' epo.effective_end_date, '
||' epo.comp_ref_amt, '
||' epo.rt_comp_ref_amt, '
||' pep.person_id, '
||' pep.pgm_id, '
||' pep.pl_id, '
||' epo.opt_id, '
||' pep.business_group_id, '
||' pil.lf_evt_ocrd_dt, '
||' epo.creation_date, '
||' epo.last_update_date, '
||' epo.object_version_number, '
||' epo.per_in_ler_id '
||l_from_str
||l_where_str
||' order by epo.elig_per_opt_id ';
hr_efc_info.insert_line('-- ');
hr_efc_info.insert_line('-- Adjusting elig per options ');
hr_efc_info.insert_line('-- ');
,p_last_update_date => l_efc_row.last_update_date
,p_object_version_number => l_efc_row.object_version_number
,p_who_counts => l_who_counts
,p_faterr_code => l_faterr_code
,p_faterr_type => l_faterr_type
);
ben_manage_life_events.g_cache_proc_object.delete;
if row.last_update_date > l_efc_row.last_update_date then
--
l_fatal_error := TRUE;
g_epo_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
g_epo_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
update ben_elig_per_opt_f epo
set epo.comp_ref_amt = l_comp_rec.comp_ref_amt,
epo.rt_comp_ref_amt = l_comp_rec.rt_comp_ref_amt
where epo.elig_per_opt_id = l_efc_row.elig_per_opt_id
and epo.effective_start_date = l_efc_row.effective_start_date
and epo.effective_end_date = l_efc_row.effective_end_date;
g_epo_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
g_epo_fatal_error_val_set(l_faterrs_count).lud := l_efc_row.last_update_date;
g_epo_success_adj_val_set(l_calsucc_count).lud := l_efc_row.last_update_date;
,last_update_date ben_enrt_bnft.last_update_date%type
,creation_date ben_enrt_bnft.creation_date%type
,object_version_number ben_enrt_bnft.object_version_number%type
,cvg_mlt_cd ben_enrt_bnft.cvg_mlt_cd%type
);
select pbb.val,
bnb.name,
bnb.uom
from ben_per_bnfts_bal_f pbb,
ben_bnfts_bal_f bnb
where pbb.person_id = c_person_id
and pbb.bnfts_bal_id = bnb.bnfts_bal_id
and c_eff_date
between bnb.effective_start_date
and bnb.effective_end_date
and c_eff_date
between pbb.effective_start_date
and pbb.effective_end_date
and pbb.bnfts_bal_id = c_bnfts_bal_id;
select clf.comp_src_cd,
clf.bnfts_bal_id
from ben_comp_lvl_fctr clf
where clf.comp_lvl_fctr_id = c_clf_id;
select ccm.creation_date,
ccm.last_update_date,
ccm.object_version_number
from ben_cvg_amt_calc_mthd_f ccm
where ccm.cvg_amt_calc_mthd_id = c_ccm_id
and c_eff_date
between ccm.effective_start_date and ccm.effective_end_date;
select cop.creation_date,
cop.last_update_date,
cop.object_version_number
from ben_oipl_f cop
where cop.oipl_id = c_cop_id
and c_eff_date
between cop.effective_start_date and cop.effective_end_date;
select pln.creation_date,
pln.last_update_date,
pln.object_version_number
from ben_pl_f pln
where pln.pl_id = c_pln_id
and c_eff_date
between pln.effective_start_date and pln.effective_end_date;
select ppp.PAY_PROPOSAL_ID
from per_pay_proposals ppp,
per_assignments_f asg
where ppp.assignment_id = asg.assignment_id
and c_eff_date
between asg.effective_start_date and asg.effective_end_date
and asg.person_id = c_per_id;
select ppp.proposed_salary_n proposed_salary,
ppb.pay_basis,
ppb.pay_annualization_factor,
paf.period_type payroll,
asg.normal_hours,
asg.frequency
from per_pay_proposals ppp,
per_assignments_f asg,
per_pay_bases ppb,
pay_all_payrolls_f paf,
per_all_people_f per
where per.person_id = c_person_id
and asg.assignment_type <> 'C'
and asg.person_id = per.person_id
and asg.primary_flag = 'Y'
and ppb.pay_basis_id = asg.pay_basis_id
and asg.payroll_id = paf.payroll_id
and c_eff_date
between asg.effective_start_date
and asg.effective_end_date
and c_eff_date
between per.effective_start_date
and per.effective_end_date
and asg.assignment_id = ppp.assignment_id
and ppp.change_date <= c_eff_date
order by ppp.change_date desc;
g_enb_success_adj_val_set.delete;
g_enb_failed_adj_val_set.delete;
g_enb_rcoerr_val_set.delete;
g_enb_fatal_error_val_set.delete;
l_sql_str := ' select enb.enrt_bnft_id, '
||' enb.elig_per_elctbl_chc_id, '
||' enb.business_group_id, '
||' enb.mx_wout_ctfn_val, '
||' enb.mn_val, '
||' enb.mx_val, '
||' enb.incrmt_val, '
||' enb.dflt_val, '
||' enb.val, '
||' epe.pgm_id, '
||' epe.pl_id, '
||' epe.pl_typ_id, '
||' epe.ptip_id, '
||' epe.plip_id, '
||' epe.oipl_id, '
||' epe.prtt_enrt_rslt_id, '
||' epe.object_version_number epe_ovn, '
||' epe.elctbl_flag, '
||' pil.per_in_ler_id, '
||' pil.person_id, '
||' pil.lf_evt_ocrd_dt, '
||' pil.ler_id, '
||' enb.last_update_date, '
||' enb.creation_date, '
||' enb.object_version_number, '
||' enb.cvg_mlt_cd '
||l_from_str
||l_where_str
||' order by enb.enrt_bnft_id ';
hr_efc_info.insert_line('-- ');
hr_efc_info.insert_line('-- Adjusting coverages ');
hr_efc_info.insert_line('-- ');
l_faterr_type := 'DELETEDINFO';
Insert_fndsession_row
(p_ses_date => l_efc_row.lf_evt_ocrd_dt
);
,p_last_update_date => l_efc_row.last_update_date
--
,p_cvg_amt_calc_mthd_id => l_cvg_set(0).cvg_amt_calc_mthd_id
--
,p_vpfdets => l_vpfdets
,p_vpf_id => l_vpf_id
,p_faterr_code => l_faterr_code
,p_faterr_type => l_faterr_type
);
if nvl(l_ccm_dets.last_update_date,hr_api.g_sot)
> nvl(l_efc_row.creation_date,hr_api.g_sot)
then
--
l_faterr_code := 'CCMDTCORR';
,p_last_update_date => l_efc_row.last_update_date
,p_object_version_number => l_efc_row.object_version_number
,p_who_counts => l_who_counts
,p_faterr_code => l_faterr_code
,p_faterr_type => l_faterr_type
);
g_enb_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
update ben_enrt_bnft enb
set enb.val = l_enb_valrow.val,
enb.mn_val = l_enb_valrow.mn_val,
enb.mx_val = l_enb_valrow.mx_val,
enb.mx_wout_ctfn_val = l_enb_valrow.mx_wout_ctfn_val,
enb.incrmt_val = l_enb_valrow.incrmt_val,
enb.dflt_val = l_enb_valrow.dflt_val
where enb.enrt_bnft_id = l_efc_row.enrt_bnft_id;
g_enb_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
g_enb_fatal_error_val_set(l_faterrs_count).lud := l_efc_row.last_update_date;
g_enb_success_adj_val_set(l_calsucc_count).lud := l_efc_row.last_update_date;
,last_update_date ben_enrt_prem.last_update_date%type
,creation_date ben_enrt_prem.creation_date%type
,object_version_number ben_enrt_prem.object_version_number%type
);
select apr.actl_prem_id,
apr.mlt_cd,
apr.val,
apr.rndg_cd,
apr.rndg_rl,
apr.rt_typ_cd,
apr.bnft_rt_typ_cd,
apr.comp_lvl_fctr_id,
apr.prem_asnmt_cd,
apr.val_calc_rl,
apr.upr_lmt_val,
apr.upr_lmt_calc_rl,
apr.lwr_lmt_val,
apr.lwr_lmt_calc_rl,
apr.uom,
apr.last_update_date
from ben_actl_prem_f apr
where apr.actl_prem_id = c_apr_id
and apr.prem_asnmt_cd = 'ENRT'
and c_lf_evt_ocrd_dt
between apr.effective_start_date
and apr.effective_end_date;
select enb.val
from BEN_ELIG_PER_ELCTBL_CHC epe,
ben_enrt_bnft enb
where epe.ELIG_PER_ELCTBL_CHC_id = c_epe_id
and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id;
select enb.val
from ben_enrt_bnft enb
where enb.enrt_bnft_id = c_enb_id;
select apr.actl_prem_id,
max(apr.last_update_date) mxapr_lud,
max(vrp.last_update_date) mxvrp_lud
from ben_actl_prem_vrbl_rt_f apr,
ben_vrbl_rt_prfl_f vrp
where vrp.vrbl_rt_prfl_stat_cd = 'A'
and c_eff_date
between vrp.effective_start_date and vrp.effective_end_date
and apr.actl_prem_id = c_apr_id
and vrp.vrbl_rt_prfl_id = apr.vrbl_rt_prfl_id
and c_eff_date
between apr.effective_start_date
and apr.effective_end_date
group by apr.actl_prem_id;
g_epr_success_adj_val_set.delete;
g_epr_failed_adj_val_set.delete;
g_epr_rcoerr_val_set.delete;
g_epr_fatal_error_val_set.delete;
l_sql_str := ' select epr.enrt_prem_id, '
||' epr.elig_per_elctbl_chc_id, '
||' epr.enrt_bnft_id, '
||' epr.actl_prem_id, '
||' epr.business_group_id, '
||' epr.val, '
||' epr.last_update_date, '
||' epr.creation_date, '
||' epr.object_version_number '
||l_from_str
||l_where_str
||' order by epr.enrt_prem_id ';
hr_efc_info.insert_line('-- ');
hr_efc_info.insert_line('-- Adjusting enrolment premiums ');
hr_efc_info.insert_line('-- ');
l_faterr_type := 'DELETEDINFO';
,p_last_update_date => l_efc_row.last_update_date
--
,p_actl_prem_id => l_efc_row.actl_prem_id
--
,p_vpfdets => l_vpfdets
,p_vpf_id => l_vpf_id
,p_faterr_code => l_faterr_code
,p_faterr_type => l_faterr_type
);
if nvl(l_apr.last_update_date,hr_api.g_sot)
> nvl(l_efc_row.last_update_date,hr_api.g_eot)
and l_faterr_code is null
then
--
l_faterr_code := 'APREPRCORR';
,p_last_update_date => l_efc_row.last_update_date
,p_object_version_number => l_efc_row.object_version_number
--
,p_who_counts => l_who_counts
,p_faterr_code => l_faterr_code
,p_faterr_type => l_faterr_type
);
g_epr_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
g_epr_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
update ben_enrt_prem epr
set epr.val = l_val
where epr.enrt_prem_id = l_efc_row.enrt_prem_id;
g_epr_fatal_error_val_set(l_faterrs_count).lud := l_efc_row.last_update_date;
g_epr_success_adj_val_set(l_calsucc_count).lud := l_efc_row.last_update_date;
ben_efc_adjustments.insert_validation_exceptions
(p_val_set => g_epr_fatal_error_val_set
,p_efc_action_id => p_action_id
,p_ent_scode => 'EPR'
,p_exception_type => null
);
,last_update_date ben_enrt_rt.last_update_date%type
,creation_date ben_enrt_rt.creation_date%type
,object_version_number ben_enrt_rt.object_version_number%type
);
select apr.actl_prem_id,
apr.mlt_cd,
apr.val,
apr.rndg_cd,
apr.rndg_rl,
apr.rt_typ_cd,
apr.bnft_rt_typ_cd,
apr.comp_lvl_fctr_id,
apr.prem_asnmt_cd,
apr.val_calc_rl,
apr.upr_lmt_val,
apr.upr_lmt_calc_rl,
apr.lwr_lmt_val,
apr.lwr_lmt_calc_rl,
apr.uom
from ben_actl_prem_f apr
where apr.actl_prem_id = c_apr_id
and apr.prem_asnmt_cd = 'ENRT'
and c_lf_evt_ocrd_dt
between apr.effective_start_date
and apr.effective_end_date;
select enb.val
from BEN_ELIG_PER_ELCTBL_CHC epe,
ben_enrt_bnft enb
where epe.ELIG_PER_ELCTBL_CHC_id = c_epe_id
and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id;
select enb.val,
enb.last_update_date
from ben_enrt_bnft enb
where enb.enrt_bnft_id = c_enb_id;
select pgm.acty_ref_perd_cd,
pgm.object_version_number,
pgm.last_update_date
from ben_pgm_f pgm
where pgm.pgm_id = c_pgm_id
and c_eff_date
between pgm.effective_start_date
and pgm.effective_end_date;
select abr.rt_mlt_cd,
abr.actl_prem_id,
abr.last_update_date,
abr.rndg_cd,
abr.rndg_rl,
abr.use_calc_acty_bs_rt_flag,
abr.nnmntry_uom,
abr.val,
abr.entr_val_at_enrt_flag,
abr.entr_ann_val_flag,
abr.mn_elcn_val,
abr.mx_elcn_val,
abr.incrmt_elcn_val
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = c_abr_id
and c_eff_date
between abr.effective_start_date
and abr.effective_end_date;
select vrp.vrbl_rt_prfl_id,
vrp.mlt_cd
from ben_acty_vrbl_rt_f avr,
ben_vrbl_rt_prfl_f vrp
where vrp.vrbl_rt_prfl_stat_cd = 'A'
and c_eff_date
between vrp.effective_start_date
and vrp.effective_end_date
and avr.acty_base_rt_id = c_abr_id
and vrp.vrbl_rt_prfl_id = avr.vrbl_rt_prfl_id
and c_eff_date
between avr.effective_start_date
and avr.effective_end_date;
select epr.val,
pil.per_in_ler_stat_cd
from ben_enrt_prem epr,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe
where epr.actl_prem_id = c_actl_prem_id
and epr.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
and epe.elig_per_elctbl_chc_id = epr.elig_per_elctbl_chc_id
and pil.per_in_ler_id = epe.per_in_ler_id;
select ppp.change_date
from per_pay_proposals ppp
where ppp.assignment_id = c_asg_id
order by ppp.change_date desc;
select efc.CMCD_MN_ELCN_VAL,
efc.CMCD_MX_ELCN_VAL,
efc.CMCD_VAL,
efc.CMCD_DFLT_VAL,
efc.ANN_DFLT_VAL,
efc.DSPLY_MN_ELCN_VAL,
efc.DSPLY_MX_ELCN_VAL,
efc.DFLT_VAL,
efc.ANN_VAL,
efc.ANN_MN_ELCN_VAL,
efc.ANN_MX_ELCN_VAL,
efc.MX_ELCN_VAL,
efc.MN_ELCN_VAL,
efc.INCRMT_ELCN_VAL,
efc.VAL,
nvl(efc.pgm_uom,efc.nip_pl_uom) uom
from ben_enrt_rt_efc efc
where efc.efc_action_id = c_efc_action_id
and efc.enrt_rt_id = c_ecr_id;
g_ecr_success_adj_val_set.delete;
g_ecr_failed_adj_val_set.delete;
g_ecr_rcoerr_val_set.delete;
g_ecr_fatal_error_val_set.delete;
l_sql_str := ' select ecr.enrt_rt_id, '
||' ecr.elig_per_elctbl_chc_id, '
||' ecr.enrt_bnft_id, '
||' ecr.acty_base_rt_id, '
||' ecr.business_group_id, '
||' ecr.cmcd_mn_elcn_val, '
||' ecr.cmcd_mx_elcn_val, '
||' ecr.cmcd_val, '
||' ecr.cmcd_dflt_val, '
||' ecr.ann_dflt_val, '
||' ecr.dsply_mn_elcn_val, '
||' ecr.dsply_mx_elcn_val, '
||' ecr.dflt_val, '
||' ecr.ann_val, '
||' ecr.ann_mn_elcn_val, '
||' ecr.ann_mx_elcn_val, '
||' ecr.mx_elcn_val, '
||' ecr.mn_elcn_val, '
||' ecr.incrmt_elcn_val, '
||' ecr.val, '
||' ecr.last_update_date, '
||' ecr.creation_date, '
||' ecr.object_version_number '
||l_from_str
||l_where_str
||' order by ecr.enrt_rt_id ';
l_sql_str := l_sql_str||' FOR UPDATE OF ecr.enrt_rt_id ';
hr_efc_info.insert_line('-- ');
hr_efc_info.insert_line('-- Adjusting enrolment rates. Worker: '||p_worker_id
||' of '||p_total_workers
);
hr_efc_info.insert_line('-- ');
hr_efc_info.insert_line('-- ');
hr_efc_info.insert_line('-- Validating enrolment rate adjustments');
hr_efc_info.insert_line('-- ');
l_faterr_type := 'DELETEDINFO';
l_faterr_type := 'DELETEDINFO';
if (nvl(l_abrdets.last_update_date,hr_api.g_sot)
> nvl(l_efc_row.last_update_date,hr_api.g_eot))
and l_faterr_code is null
then
--
l_faterr_code := 'ABRCORR';
,p_last_update_date => l_efc_row.last_update_date
--
,p_acty_base_rt_id => l_efc_row.acty_base_rt_id
--
,p_vpfdets => l_vpfdets
,p_vpf_id => l_vpf_id
,p_faterr_code => l_faterr_code
,p_faterr_type => l_faterr_type
);
,p_last_update_date => l_efc_row.last_update_date
,p_object_version_number => l_efc_row.object_version_number
--
,p_who_counts => l_who_counts
,p_faterr_code => l_faterr_code
,p_faterr_type => l_faterr_type
);
g_ecr_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
update ben_enrt_rt ecr
set ecr.val = l_val,
ecr.ann_val = l_ann_val,
ecr.dflt_val = l_dflt_val,
ecr.ann_dflt_val = l_ann_dflt_val,
ecr.cmcd_val = l_cmcd_val
/*
--
-- Copied straight from a Vapro or ABR. No adjustment required.
--
ecr.cmcd_mn_elcn_val = l_cmcd_mn_elcn_val,
ecr.cmcd_mx_elcn_val = l_cmcd_mx_elcn_val,
ecr.dsply_mn_elcn_val = l_dsply_mn_elcn_val,
ecr.dsply_mx_elcn_val = l_dsply_mx_elcn_val,
ecr.ann_mn_elcn_val = l_ann_mn_elcn_val,
ecr.ann_mx_elcn_val = l_ann_mx_elcn_val,
ecr.mx_elcn_val = l_mx_elcn_val,
ecr.mn_elcn_val = l_mn_elcn_val,
ecr.incrmt_elcn_val = l_incrmt_elcn_val,
*/
where ecr.enrt_rt_id = l_efc_row.enrt_rt_id;
l_faterr_type := 'DELETEDINFO';
g_ecr_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
g_ecr_fatal_error_val_set(l_faterrs_count).lud := l_efc_row.last_update_date;
g_ecr_success_adj_val_set(l_calsucc_count).lud := l_efc_row.last_update_date;
ben_efc_adjustments.insert_validation_exceptions
(p_val_set => g_ecr_failed_adj_val_set
,p_efc_action_id => p_action_id
,p_ent_scode => 'ECR'
,p_exception_type => 'AF'
);
ben_efc_adjustments.insert_validation_exceptions
(p_val_set => g_ecr_fatal_error_val_set
,p_efc_action_id => p_action_id
,p_ent_scode => 'ECR'
,p_exception_type => null
);