[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
115.14 15-Nov-06 rtagarra Bug 5049253:Commented the insert into exception when there is no
PRV corresponding to the PIL for prv_adjustments.
-----------------------------------------------------------------------------
*/
--
-- Globals.
--
g_package varchar2(50) := 'ben_efc_adjustments1.';
,last_update_date ben_per_in_ler.last_update_date%type
,object_version_number ben_per_in_ler.object_version_number%type
/*
,enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type
*/
);
select prv.prtt_rt_val_id,
prv.rt_val,
prv.ann_rt_val,
prv.cmcd_rt_val,
pen.enrt_mthd_cd,
prv.prtt_enrt_rslt_id,
prv.acty_base_rt_id,
prv.creation_date,
prv.last_update_date,
prv.object_version_number,
prv.MLT_CD,
pil.person_id,
pil.per_in_ler_id
from ben_prtt_rt_val prv,
ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where prv.prtt_rt_val_id = c_prv_id
and pil.lf_evt_ocrd_dt
between pen.effective_start_date and pen.effective_end_date
and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and prv.per_in_ler_id = pil.per_in_ler_id;
select enb.prtt_enrt_rslt_id
from ben_enrt_bnft enb
where enb.enrt_bnft_id = c_enb_id;
select prv.acty_base_rt_id,
prv.prtt_enrt_rslt_id,
prv.creation_date,
prv.last_update_date,
prv.created_by,
prv.last_updated_by,
prv.last_update_login,
prv.object_version_number,
prv.business_group_id,
prv.prtt_rt_val_id,
prv.per_in_ler_id,
prv.rt_val,
prv.ann_rt_val,
prv.mlt_cd,
pil.person_id,
prv.RT_END_DT
from ben_prtt_rt_val prv,
ben_per_in_ler pil
where prv.per_in_ler_id = c_pil_id
and prv.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select ecr.enrt_rt_id,
ecr.rt_mlt_cd,
ecr.enrt_bnft_id,
ecr.elig_per_elctbl_chc_id,
ecr.asn_on_enrt_flag,
ecr.entr_val_at_enrt_flag,
ecr.rt_strt_dt_cd
from ben_enrt_rt ecr
where ecr.prtt_rt_val_id = c_prv_id;
select abr.last_update_date,
abr.rt_mlt_cd,
abr.nnmntry_uom,
abr.entr_val_at_enrt_flag,
abr.rt_typ_cd,
abr.val,
abr.pgm_id,
abr.ptip_id,
abr.pl_id,
abr.plip_id,
abr.oipl_id,
abr.oiplip_id,
abr.actl_prem_id
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 pen.effective_end_date,
pen.enrt_cvg_strt_dt,
pen.ENRT_CVG_THRU_DT,
pen.pgm_id,
pen.pl_id,
pen.oipl_id,
pen.prtt_enrt_rslt_stat_cd,
pen.enrt_ovridn_flag,
pen.sspndd_flag,
pen.enrt_mthd_cd
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = c_pen_id
and c_eff_date
between pen.effective_start_date and pen.effective_end_date;
select pen.effective_end_date,
pen.prtt_enrt_rslt_stat_cd
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = c_pen_id;
select ecr.prtt_rt_val_id,
ecr.enrt_rt_id,
ecr.val,
ecr.ann_val,
ecr.rt_mlt_cd,
ecr.SPCL_RT_ENRT_RT_ID,
ecr.business_group_id,
ecr.enrt_bnft_id,
ecr.elig_per_elctbl_chc_id,
ecr.acty_base_rt_id,
ecr.entr_val_at_enrt_flag
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
and ecr.SPCL_RT_ENRT_RT_ID is null
/*
and ecr.entr_val_at_enrt_flag = 'N'
*/
/*
and ecr.asn_on_enrt_flag = 'Y'
*/
UNION
select ecr.prtt_rt_val_id,
ecr.enrt_rt_id,
ecr.val,
ecr.ann_val,
ecr.rt_mlt_cd,
ecr.SPCL_RT_ENRT_RT_ID,
ecr.business_group_id,
ecr.enrt_bnft_id,
ecr.elig_per_elctbl_chc_id,
ecr.acty_base_rt_id,
ecr.entr_val_at_enrt_flag
from ben_enrt_bnft enb,
ben_enrt_rt ecr
where enb.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
and enb.ENRT_BNFT_ID = ecr.ENRT_BNFT_ID
and enb.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and ecr.SPCL_RT_ENRT_RT_ID is null
/*
and ecr.entr_val_at_enrt_flag = 'N'
*/
/*
and ecr.asn_on_enrt_flag = 'Y'
*/
;
select epe.pgm_id,
epe.pl_id,
epe.oipl_id,
epe.elig_per_elctbl_chc_id,
epe.spcl_rt_pl_id,
epe.spcl_rt_oipl_id,
pel.acty_ref_perd_cd
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil,
ben_pil_elctbl_chc_popl pel,
ben_prtt_enrt_rslt_f pen
where epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and pil.per_in_ler_id = epe.per_in_ler_id
and pil.per_in_ler_id = c_pil_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pen.prtt_enrt_rslt_id = c_pen_id
and nvl(pen.pgm_id,-1) = nvl(epe.pgm_id,-1)
and pen.pl_id = epe.pl_id
and nvl(pen.oipl_id,-1) = nvl(epe.oipl_id,-1)
and c_eff_date
between pen.effective_start_date and pen.effective_end_date;
select ncu.currency_code
from hr_ncu_currencies ncu
where ncu.currency_code = c_uom;
select prvefc.prtt_rt_val_id
from ben_prtt_rt_val_efc prvefc
where prvefc.efc_action_id = c_efc_action_id
and prvefc.prtt_rt_val_id > c_pk1
and mod(prvefc.prtt_rt_val_id, c_total_workers) = c_worker_id;
select efc.rt_val,
efc.ann_rt_val,
efc.cmcd_rt_val,
efc.pgm_uom,
efc.nip_pl_uom
from ben_prtt_rt_val_efc efc
where efc.efc_action_id = c_efc_action_id
and efc.prtt_rt_val_id = c_prv_id;
select apr.uom
from ben_actl_prem_f apr
where apr.actl_prem_id = c_apr_id
and c_eff_date
between apr.effective_start_date and apr.effective_end_date;
SELECT abr.pgm_id,
abr.ptip_id,
abr.pl_id,
abr.plip_id,
abr.oipl_id,
abr.oiplip_id,
abr.nnmntry_uom
FROM ben_vrbl_rt_prfl_f vpf
, ben_acty_vrbl_rt_f avr
, ben_acty_base_rt_f abr
WHERE avr.vrbl_rt_prfl_id = c_vpf_id
AND vpf.VRBL_USG_CD = 'RT'
AND vpf.vrbl_rt_prfl_id = avr.vrbl_rt_prfl_id
AND avr.acty_base_rt_id = abr.acty_base_rt_id
AND c_eff_date
BETWEEN avr.effective_start_date AND avr.effective_end_date
AND c_eff_date
BETWEEN abr.effective_start_date AND abr.effective_end_date
AND avr.acty_base_rt_id =
(select min(avr1.acty_base_rt_id)
from ben_acty_vrbl_rt_f avr1,
ben_acty_base_rt_f abr1
where avr1.vrbl_rt_prfl_id = avr.vrbl_rt_prfl_id
AND vpf.effective_start_date
BETWEEN avr1.effective_start_date AND avr1.effective_end_date
AND avr1.acty_base_rt_id = abr1.acty_base_rt_id
AND vpf.effective_start_date
BETWEEN abr1.effective_start_date AND abr1.effective_end_date
and abr1.NNMNTRY_UOM is null
and avr1.ordr_num =
(select min(avr2.ordr_num)
from ben_acty_vrbl_rt_f avr2,
ben_acty_base_rt_f abr2
where avr2.vrbl_rt_prfl_id = avr.vrbl_rt_prfl_id
AND vpf.effective_start_date
BETWEEN avr2.effective_start_date AND avr2.effective_end_date
AND avr2.acty_base_rt_id = abr1.acty_base_rt_id
AND vpf.effective_start_date
BETWEEN abr2.effective_start_date AND abr2.effective_end_date
and abr2.NNMNTRY_UOM is null
)
)
ORDER BY avr.ORDR_NUM;
l_faterr_type := 'DELETEDINFO';
l_faterr_type := 'DELETEDINFO';
,p_last_update_date => null
--
,p_acty_base_rt_id => p_prvabr_id
--
,p_vpfdets => l_vpfdets
,p_vpf_id => l_vpf_id
,p_faterr_code => l_faterr_code
,p_faterr_type => l_faterr_type
);
l_faterr_type := 'DELETEDINFO';
procedure update_prv
(p_prtt_rt_val_id in number
,p_rt_val in number
,p_ann_rt_val in number
,p_chunk in number
,p_efc_worker_id in number
,p_chunkrow_count in out nocopy number
)
is
begin
--
update ben_prtt_rt_val prv
set prv.rt_val = p_rt_val,
prv.ann_rt_val = p_ann_rt_val
where prv.prtt_rt_val_id = p_prtt_rt_val_id;
ben_efc_adjustments.g_prv_success_adj_val_set.delete;
ben_efc_adjustments.g_prv_failed_adj_val_set.delete;
ben_efc_adjustments.g_prv_rcoerr_val_set.delete;
ben_efc_adjustments.g_prv_fatal_error_val_set.delete;
||' (select min(per1.effective_start_date) '
||' from per_all_people_f per1 '
||' where per.person_id = per1.person_id '
||' ) '
||' and pil.lf_evt_ocrd_dt '
||' between per.effective_start_date and per.effective_end_date '
/* Exclude out nocopy voided and backed out nocopy life events */
||' and pil.per_in_ler_stat_cd not in ('
||''''||'VOIDD'||''''||','||''''||'BCKDT'||''''||') '
||' and (prv.rt_val is not null '
||' or prv.ann_rt_val is not null '
||' or prv.cmcd_rt_val is not null) ';
||' pil.last_update_date, '
||' pil.object_version_number ';
l_sql_str := ' select '||l_groupby_str
||l_from_str
||l_where_str
||' group by '||l_groupby_str
||' order by pil.per_in_ler_id ';
hr_efc_info.insert_line('-- ');
hr_efc_info.insert_line('-- Adjusting participant rate values ');
hr_efc_info.insert_line('-- ');
,p_last_update_date => l_prvdets.last_update_date
,p_object_version_number => l_prvdets.object_version_number
--
,p_who_counts => l_who_counts
,p_faterr_code => l_faterr_code
,p_faterr_type => l_faterr_type
);
ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).lud := l_prvdets.last_update_date;
ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).lud := l_prvdets.last_update_date;
ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).lud := l_prvdets.last_update_date;
update_prv
(p_prtt_rt_val_id => l_prv_rtval_set(prvele_num).prtt_rt_val_id
,p_rt_val => l_prv_rtval_set(prvele_num).rt_val
,p_ann_rt_val => l_prv_rtval_set(prvele_num).ann_rt_val
,p_chunk => p_chunk
,p_efc_worker_id => p_efc_worker_id
,p_chunkrow_count => l_chunkrow_count
);
l_faterr_type := 'DELETEDINFO';
l_faterr_type := 'DELETEDINFO';
ben_efc_adjustments.g_prv_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
if pilprv_row.creation_date <> pilprv_row.last_update_date
and l_faterr_code is null
and l_adjfailed
then
--
if pilprv_row.RT_END_DT <> hr_api.g_eot
then
--
l_faterr_code := 'PRVENDDATED';
,p_last_update_date => pilprv_row.last_update_date
,p_object_version_number => pilprv_row.object_version_number
--
,p_who_counts => l_who_counts
,p_faterr_code => l_faterr_code
,p_faterr_type => l_faterr_type
);
ben_efc_adjustments.g_prv_fatal_error_val_set(l_faterrs_count).lud := pilprv_row.last_update_date;
ben_efc_adjustments.g_prv_success_adj_val_set(l_calsucc_count).lud := pilprv_row.last_update_date;
update_prv
(p_prtt_rt_val_id => pilprv_row.prtt_rt_val_id
,p_rt_val => l_prv_rt_val
,p_ann_rt_val => l_prv_ann_rt_val
,p_chunk => p_chunk
,p_efc_worker_id => p_efc_worker_id
,p_chunkrow_count => l_chunkrow_count
);
ben_efc_adjustments.g_prv_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
ben_efc_adjustments.g_prv_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
ben_efc_adjustments.insert_validation_exceptions
(p_val_set => ben_efc_adjustments.g_prv_failed_adj_val_set
,p_efc_action_id => p_action_id
,p_ent_scode => 'PRV'
,p_exception_type => 'AF'
);
ben_efc_adjustments.insert_validation_exceptions
(p_val_set => ben_efc_adjustments.g_prv_fatal_error_val_set
,p_efc_action_id => p_action_id
,p_ent_scode => 'PRV'
,p_exception_type => null
);
,prv_last_update_date ben_prtt_rt_val.last_update_date%type
,prv_last_update_login ben_prtt_rt_val.last_update_login%type
,prv_created_by ben_prtt_rt_val.created_by%type
,screen_entry_value pay_element_entry_values_f.screen_entry_value%type
,enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type
,business_group_id ben_prtt_rt_val.business_group_id%type
,prtt_rt_val_id ben_prtt_rt_val.prtt_rt_val_id%type
,acty_ref_perd_cd ben_prtt_rt_val.acty_ref_perd_cd%type
,acty_base_rt_id ben_prtt_rt_val.acty_base_rt_id%type
,prtt_enrt_rslt_id ben_prtt_rt_val.prtt_enrt_rslt_id%type
,rt_strt_dt ben_prtt_rt_val.rt_strt_dt%type
,rt_val ben_prtt_rt_val.rt_val%type
,prv_ovn ben_prtt_rt_val.object_version_number%type
,input_value_id ben_acty_base_rt_f.input_value_id%type
,element_type_id ben_acty_base_rt_f.element_type_id%type
,lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type
,person_id ben_per_in_ler.person_id%type
,per_in_ler_id ben_per_in_ler.per_in_ler_id%type
,ELEMENT_ENTRY_ID pay_element_entry_values_f.ELEMENT_ENTRY_ID%type
);
select count(*)
from ben_prtt_rt_val
where element_entry_value_id = c_eev_id;
select abr.last_update_date,
abr.ele_rqd_flag
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 ele.creation_date,
ele.last_update_date,
ele.object_version_number,
ele.created_by,
ele.last_updated_by,
ele.last_update_login
from pay_element_entries_f ele
where ele.ELEMENT_ENTRY_ID = c_ele_id
and c_eff_date
between ele.effective_start_date and ele.effective_end_date;
select ele.effective_start_date,
ele.effective_end_date
from pay_element_entries_f ele
where ele.ELEMENT_ENTRY_ID = c_ele_id;
select efc.SCREEN_ENTRY_VALUE,
efc.input_currency_code
from pay_element_entry_values_f_efc efc
where efc.efc_action_id = c_efc_action_id
and efc.element_entry_value_id = c_eev_id
and efc.effective_start_date = c_eev_esd;
select exc.efc_action_id
from ben_efc_exclusions exc
where exc.efc_action_id = c_efc_action_id
and exc.ent_scode = c_ent_scode
and exc.pk_id = c_prv_id
and exc.exclusion_type = 'VALIDEXCLUSION';
select efc.efc_action_id
from ben_prtt_rt_val_efc efc
where efc.efc_action_id = c_efc_action_id
and efc.prtt_rt_val_id = c_prv_id;
ben_efc_adjustments.g_eev_failed_adj_val_set.delete;
ben_efc_adjustments.g_eev_rcoerr_val_set.delete;
ben_efc_adjustments.g_eev_fatal_error_val_set.delete;
ben_efc_adjustments.g_eev_success_adj_val_set.delete;
||' (select min(prv1.prtt_rt_val_id) '
||' from ben_prtt_rt_val prv1 '
||' where prv.element_entry_value_id = prv1.element_entry_value_id '
||' ) '
||' and prv.rt_strt_dt '
||' between pen.effective_start_date and pen.effective_end_date '
||' and pen.effective_start_date = '
||' (select min(pen1.effective_start_date) '
||' from ben_prtt_enrt_rslt_f pen1 '
||' where pen.prtt_enrt_rslt_id = pen1.prtt_enrt_rslt_id '
||' ) '
||' and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id '
||' and prv.acty_base_rt_id = abr.acty_base_rt_id '
||' and prv.rt_strt_dt '
||' between abr.effective_start_date and abr.effective_end_date '
||' and abr.effective_start_date = '
||' (select min(abr1.effective_start_date) '
||' from ben_acty_base_rt_f abr1 '
||' where abr.acty_base_rt_id = abr1.acty_base_rt_id '
||' ) '
||' and prv.per_in_ler_id = pil.per_in_ler_id '
||' and per.person_id = pil.person_id '
||' and prv.rt_strt_dt '
||' between per.effective_start_date and per.effective_end_date '
||' and per.effective_start_date = '
||' (select min(per1.effective_start_date) '
||' from per_all_people_f per1 '
||' where per.person_id = per1.person_id '
||' ) '
||' and eev.screen_entry_value is not null '
/* Exclude out nocopy voided and backed out nocopy life events */
||' and pil.per_in_ler_stat_cd not in ('
||''''||'VOIDD'||''''||','||''''||'BCKDT'||''''||') '
;
l_sql_str := ' select eev.element_entry_value_id, '
||' eev.effective_start_date, '
||' eev.effective_end_date, '
||' prv.creation_date, '
||' prv.last_update_date, '
||' prv.last_update_login, '
||' prv.created_by, '
||' eev.screen_entry_value, '
||' pen.enrt_mthd_cd, '
||' prv.business_group_id, '
||' prv.prtt_rt_val_id, '
||' prv.acty_ref_perd_cd, '
||' prv.acty_base_rt_id, '
||' prv.prtt_enrt_rslt_id, '
||' prv.rt_strt_dt, '
||' prv.rt_val, '
||' prv.object_version_number, '
||' abr.input_value_id, '
||' abr.element_type_id, '
||' pil.lf_evt_ocrd_dt, '
||' pil.person_id, '
||' pil.per_in_ler_id, '
||' eev.ELEMENT_ENTRY_ID '
||l_from_str
||l_where_str
||' order by eev.element_entry_value_id, '
||' eev.effective_start_date ';
l_sql_str := l_sql_str||' for update of eev.element_entry_value_id ';
hr_efc_info.insert_line('-- ');
hr_efc_info.insert_line('-- Adjusting element entry values. Worker: '||p_worker_id
||' of '||p_total_workers
);
hr_efc_info.insert_line('-- ');
hr_efc_info.insert_line('-- ');
hr_efc_info.insert_line('-- Validating element entry value adjustments');
hr_efc_info.insert_line('-- ');
if l_tmpdets.last_update_login <> l_efc_row.prv_last_update_login
and l_tmpdets.created_by = l_efc_row.prv_created_by
and l_faterr_code is null
then
--
if l_efc_row.prv_last_update_login = -1 then
--
l_faterr_code := 'SQLPLUSELEPRVCORR';
,p_last_update_date => l_eledets.last_update_date
,p_object_version_number => l_eledets.object_version_number
--
,p_who_counts => l_who_counts
,p_faterr_code => l_faterr_code
,p_faterr_type => l_faterr_type
);
if l_abrdets.last_update_date > l_eledets.creation_date
and l_faterr_code is null
then
--
l_faterr_code := 'ABRCORR';
ben_efc_adjustments.g_eev_failed_adj_val_set(l_calfail_count).lud := l_eledets.last_update_date;
update PAY_ELEMENT_ENTRY_VALUES_F eev
set eev.screen_entry_value = l_eev_screen_entry_value
where eev.ELEMENT_ENTRY_VALUE_id = l_efc_row.ELEMENT_ENTRY_VALUE_id
and eev.effective_start_date = l_efc_row.effective_start_date;
ben_efc_adjustments.g_eev_rcoerr_val_set(l_rcoerr_count).lud := l_eledets.last_update_date;
ben_efc_adjustments.g_eev_fatal_error_val_set(l_faterrs_count).lud := l_eledets.last_update_date;
ben_efc_adjustments.g_eev_fatal_error_val_set(l_faterrs_count).lu_by := l_eledets.last_updated_by;
ben_efc_adjustments.g_eev_success_adj_val_set(l_calsucc_count).lud := l_eledets.last_update_date;
ben_efc_adjustments.insert_validation_exceptions
(p_val_set => ben_efc_adjustments.g_eev_failed_adj_val_set
,p_efc_action_id => p_action_id
,p_ent_scode => 'EEV'
,p_exception_type => 'AF'
);
ben_efc_adjustments.insert_validation_exceptions
(p_val_set => ben_efc_adjustments.g_eev_fatal_error_val_set
,p_efc_action_id => p_action_id
,p_ent_scode => 'EEV'
,p_exception_type => null
);
l_sql_str := 'select count(*) '
||' from pay_element_entry_values_f eev, '
||' ben_prtt_rt_val prv '
||' where prv.element_entry_value_id = eev.element_entry_value_id '
||' and eev.screen_entry_value is not null ';
,p_efctable_sql => 'select count(*) from pay_element_entry_values_f_efc '
||' where efc_action_id = '||p_action_id
--
,p_bgp_id => p_business_group_id
,p_action_id => p_action_id
--
,p_conv_count => l_conv_count
,p_unconv_count => l_unconv_count
,p_tabrow_count => l_tabrow_count
);
,last_update_date ben_bnft_prvdd_ldgr_f.last_update_date%type
,object_version_number ben_bnft_prvdd_ldgr_f.object_version_number%type
,created_by ben_bnft_prvdd_ldgr_f.created_by%type
,last_updated_by ben_bnft_prvdd_ldgr_f.last_updated_by%type
,used_val ben_bnft_prvdd_ldgr_f.used_val%type
,FRFTD_VAL ben_bnft_prvdd_ldgr_f.FRFTD_VAL%type
,PRVDD_VAL ben_bnft_prvdd_ldgr_f.PRVDD_VAL%type
,RLD_UP_VAL ben_bnft_prvdd_ldgr_f.RLD_UP_VAL%type
,CASH_RECD_VAL ben_bnft_prvdd_ldgr_f.CASH_RECD_VAL%type
,business_group_id ben_bnft_prvdd_ldgr_f.business_group_id%type
,bnft_prvdr_pool_id ben_bnft_prvdd_ldgr_f.bnft_prvdr_pool_id%type
,acty_base_rt_id ben_bnft_prvdd_ldgr_f.acty_base_rt_id%type
,prtt_enrt_rslt_id ben_bnft_prvdd_ldgr_f.prtt_enrt_rslt_id%type
,person_id ben_per_in_ler.person_id%type
,per_in_ler_id ben_per_in_ler.per_in_ler_id%type
,lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%type
);
select ecr.prtt_rt_val_id,
ecr.ELIG_PER_ELCTBL_CHC_ID
from ben_enrt_bnft enb,
ben_enrt_rt ecr,
ben_bnft_prvdd_ldgr_f bpl,
ben_elig_per_elctbl_chc epe
where ecr.DECR_BNFT_PRVDR_POOL_ID = bpl.BNFT_PRVDR_POOL_ID
and ecr.acty_base_rt_id = bpl.acty_base_rt_id
and ecr.enrt_bnft_id = enb.enrt_bnft_id
and bpl.per_in_ler_id = epe.per_in_ler_id
and enb.ELIG_PER_ELCTBL_CHC_ID = epe.ELIG_PER_ELCTBL_CHC_ID
and bpl.acty_base_rt_id = c_abr_id
and bpl.prtt_enrt_rslt_id = c_pen_id
union
select ecr.prtt_rt_val_id,
ecr.ELIG_PER_ELCTBL_CHC_ID
from ben_enrt_rt ecr,
ben_bnft_prvdd_ldgr_f bpl,
ben_elig_per_elctbl_chc epe
where ecr.DECR_BNFT_PRVDR_POOL_ID = bpl.BNFT_PRVDR_POOL_ID
and ecr.acty_base_rt_id = bpl.acty_base_rt_id
and ecr.ELIG_PER_ELCTBL_CHC_ID = epe.ELIG_PER_ELCTBL_CHC_ID
and bpl.per_in_ler_id = epe.per_in_ler_id
and bpl.acty_base_rt_id = c_abr_id
and bpl.prtt_enrt_rslt_id = c_pen_id
;
select bpl.bnft_prvdd_ldgr_id,
bpl.effective_start_date,
bpl.effective_end_date,
bpl.used_val,
bpl.FRFTD_VAL,
bpl.PRVDD_VAL,
bpl.RLD_UP_VAL,
bpl.CASH_RECD_VAL,
bpl.creation_date,
bpl.last_update_date,
bpl.created_by,
bpl.last_updated_by,
bpl.object_version_number
from ben_bnft_prvdd_ldgr_f bpl
where bpl.bnft_prvdd_ldgr_id = c_bpl_id
and c_eff_date
between bpl.effective_start_date and bpl.effective_end_date;
select ecr.enrt_rt_id,
ecr.enrt_bnft_ID,
ecr.ELIG_PER_ELCTBL_CHC_ID,
ecr.decr_bnft_prvdr_pool_id,
ecr.prtt_rt_val_id,
ecr.val,
epe.prtt_enrt_rslt_id
from ben_enrt_rt ecr,
ben_elig_per_elctbl_chc epe
where ecr.acty_base_rt_id = c_abr_id
/*
and ecr.decr_bnft_prvdr_pool_id = c_bpp_id
*/
and ecr.ELIG_PER_ELCTBL_CHC_ID = epe.ELIG_PER_ELCTBL_CHC_ID
and epe.per_in_ler_id = c_pil_id
/*
and epe.prtt_enrt_rslt_id = c_pen_id
*/
union
select ecr.enrt_rt_id,
ecr.enrt_bnft_ID,
ecr.ELIG_PER_ELCTBL_CHC_ID,
ecr.decr_bnft_prvdr_pool_id,
ecr.prtt_rt_val_id,
ecr.val,
epe.prtt_enrt_rslt_id
from ben_enrt_rt ecr,
ben_enrt_bnft enb,
ben_elig_per_elctbl_chc epe
where
/*
ecr.DECR_BNFT_PRVDR_POOL_ID = c_bpp_id
and
*/
ecr.acty_base_rt_id = c_abr_id
and ecr.enrt_bnft_id = enb.enrt_bnft_id
and enb.ELIG_PER_ELCTBL_CHC_ID = epe.ELIG_PER_ELCTBL_CHC_ID
and epe.per_in_ler_id = c_pil_id;
select bpp.dflt_excs_trtmt_cd,
bpp.auto_alct_excs_flag
from ben_bnft_prvdr_pool_f bpp
where bpp.bnft_prvdr_pool_id = c_bpp_id
and c_eff_date
between bpp.effective_start_date and bpp.effective_end_date;
ben_efc_adjustments.g_bpl_success_adj_val_set.delete;
ben_efc_adjustments.g_bpl_failed_adj_val_set.delete;
ben_efc_adjustments.g_bpl_rcoerr_val_set.delete;
ben_efc_adjustments.g_bpl_fatal_error_val_set.delete;
l_sql_str := ' select bpl.bnft_prvdd_ldgr_id, '
||' bpl.effective_start_date, '
||' bpl.effective_end_date, '
||' bpl.creation_date, '
||' bpl.last_update_date, '
||' bpl.object_version_number, '
||' bpl.created_by, '
||' bpl.last_updated_by, '
||' bpl.used_val, '
||' bpl.FRFTD_VAL, '
||' bpl.PRVDD_VAL, '
||' bpl.RLD_UP_VAL, '
||' bpl.CASH_RECD_VAL, '
||' bpl.business_group_id, '
||' bpl.bnft_prvdr_pool_id, '
||' bpl.acty_base_rt_id, '
||' bpl.prtt_enrt_rslt_id, '
||' pil.person_id, '
||' pil.per_in_ler_id, '
||' pil.lf_evt_ocrd_dt '
||l_from_str
||l_where_str
||' order by pil.per_in_ler_id, bpl.prtt_enrt_rslt_id ';
hr_efc_info.insert_line('-- ');
hr_efc_info.insert_line('-- Adjusting benefit provider ledgers ');
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_efc_adjustments.g_bpl_failed_adj_val_set(l_calfail_count).lud := l_efc_row.last_update_date;
update ben_bnft_prvdd_ldgr_f bpl
set bpl.used_val = l_bpl_used_val,
bpl.prvdd_val = l_bpl_prvdd_val,
bpl.cash_recd_val = l_bpl_cash_recd_val,
bpl.frftd_val = l_bpl_frftd_val,
bpl.rld_up_val = l_bpl_rld_up_val
where bpl.bnft_prvdd_ldgr_id = l_efc_row.bnft_prvdd_ldgr_id
and bpl.effective_start_date = l_efc_row.effective_start_date
and bpl.effective_end_date = l_efc_row.effective_end_date;
ben_efc_adjustments.g_bpl_rcoerr_val_set(l_rcoerr_count).lud := l_efc_row.last_update_date;
ben_efc_adjustments.g_bpl_fatal_error_val_set(l_faterrs_count).lud := l_efc_row.last_update_date;
ben_efc_adjustments.g_bpl_fatal_error_val_set(l_faterrs_count).lu_by := l_efc_row.last_updated_by;
ben_efc_adjustments.g_bpl_success_adj_val_set(l_calsucc_count).lud := l_efc_row.last_update_date;