[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
for each epe selected.
15-Nov-99 mhoyes 115.24 - Added trace messages for profiing.
18-Nov-99 pbodla 115.25 - Added elig_per_elctbl_chc_id as
parameter while evaluating val_calc_rl
and passed to limit_checks.
17-Jan-00 tguy 115.26 Added check for rounding when vrbl
rt trtmt cd = rplc do not round at
value at this level
02 Feb 00 lmcdonal 115.27 Break the computation of premium
into a separate procedure so that
it can be called independently.
Bug 1166174.
04-Apr-00 mmogel 115.28 Added tokes to messages to make
them more meaningful to the user
03-May-00 mhoyes 115.29 Removed request_id join from c_epe.
08-Aug-00 pbodla 115.30 - Bug 4948(WWW Bug 1259220)
When l_vr_trtmt_cd is null rounding
is not applied. So nvl applied
around l_vr_trtmt_cd.
07-Nov-00 mhoyes 115.31 - Added electable choice context
global.
- Bulk inserted ben_enrt_prem.
22-Nov-00 mhoyes 115.32 - Changed bulk bind composite data
structure to a varray. This avoids
random composite error on 8.1.6.2.
05-Jan-01 kmahendr 115.33 - Added per_in_ler_id parameter to perpil_cache
call
15 mar 01 tilak 115.34 g_computed_prem_val is added
This is used to store the value of the
computed premium, whic can be used for
calcualtion mode only to get the ammount
In this mode ele_chc_id,benefit is not inserted
so the global_variable to get the value
bug :bug :1676551
21-mar-2001 tilak 115.35 ultmt_upr_lmt,ultmt_lwr_lmt is validated
02-apr-2001 tilak 115.36 ultmt_upr_lmt_calc_rl,ultmt_lwr_lmt_calc_rl is validated
27-aug-2001 tilak 115.37 bug:1949361 jurisdiction code is
derived inside benutils.formula.
27-Sep-2001 kmahendr 115.38 Bug#1981673-Added parameter ann_mn_elcn_val and
ann_mx_elcn_val to ben_determine_variable_rates
call
08-Jun-2002 pabodla 115.39 Do not select the contingent worker
assignment when assignment data is
fetched.
14-Jun-2002 pabodla 115.41 Added dbdrv command
02-Aug-2004 tjesumic 115.44 fonm, determination of fonm_flag changed from global varaible to epe dt
08-Sep-2004 tjesumic 115.45 fonm, global fonm_cvg_strt_dt reintialized from epe dt
08-Sep-2004 tjesumic 115.46 fonm, global fonm_cvg_strt_dt reintialized from epe dt
08-Sep-2004 tjesumic 115.47 fonm, caching clearence
15-Nov-2004 kmahendr 115.48 Unrest. enh changes
30-dec-2004 nhunur 115.49 4031733 - No need to open cursor c_state.
9-Jun-2005 nhunur 115.50 4383988 - do fnd_number.canonical_to_number() to the
FF output before assigning to a number variable.
03-Oct-05 ssarkar 115.51 4644867 - Added order by clause to cursoe c_asg to Query 'E' assignment first
and then others .
10-Mar-06 swjain 115.52 In cursor c_asg, added condition to fetch active assignments only
10-Mar-06 swjain 115.53 Updated cursor c_asg
10-Aug-07 bmanyam 115.54 6330056 : Store all the premiums evaluated into
global pl/sql tbl
*/
--------------------------------------------------------------------------------
--
g_package varchar2(80) := 'ben_determine_actual_premium';
select region_2
from hr_locations_all loc,per_all_assignments_f asg
where loc.location_id = asg.location_id
and asg.person_id = p_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and p_date between
asg.effective_start_date and asg.effective_end_date;
select asg.assignment_id,asg.organization_id,loc.region_2
from per_all_assignments_f asg,hr_locations_all loc, per_assignment_status_types ast
where asg.person_id = p_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and asg.location_id = loc.location_id(+)
and asg.assignment_status_type_id = ast.assignment_status_type_id(+)
and ast.per_system_status(+) = 'ACTIVE_ASSIGN'
--and nvl(p_lf_evt_ocrd_dt,p_effective_date)
and p_date
between asg.effective_start_date
and asg.effective_end_date
order by assignment_type desc, effective_start_date desc; -- BUG 4644867
l_program_update_date_va benutils.g_date_table := benutils.g_date_table();
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.pl_id = p_pl_id
and apr.prem_asnmt_cd = 'ENRT' -- PROC are dealt with in benprplc.pkb
and p_date
between apr.effective_start_date
and apr.effective_end_date;
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.oipl_id = p_oipl_id
and apr.prem_asnmt_cd = 'ENRT' -- PROC are dealt with in benprplc.pkb
and p_date
between apr.effective_start_date
and apr.effective_end_date;
select oipl.opt_id
from ben_oipl_f oipl
where oipl.oipl_id = p_oipl_id
--and p_effective_date between
and p_date between
oipl.effective_start_date and oipl.effective_end_date;
g_computed_prem_tbl.delete;
l_program_update_date_va.extend(1);
l_program_update_date_va(l_epr_elenum) := sysdate;
ben_manage_unres_life_events.update_enrt_prem
( p_enrt_prem_id => l_enrt_prem_id
,p_val => l_val_va(i)
,p_uom => l_uom_va(i)
,p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id_va(i)
,p_enrt_bnft_id => l_enrt_bnft_id_va(i)
,p_actl_prem_id => l_actl_prem_id_va(i)
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_request_id => l_request_id_va(i)
,p_program_application_id => l_program_application_id_va(i)
,p_program_id => l_program_id_va(i)
,p_program_update_date => l_program_update_date_va(i)
);
INSERT INTO ben_enrt_prem
(enrt_prem_id,
val,
uom,
elig_per_elctbl_chc_id,
enrt_bnft_id,
actl_prem_id,
business_group_id,
epr_attribute_category,
epr_attribute1,
epr_attribute2,
epr_attribute3,
epr_attribute4,
epr_attribute5,
epr_attribute6,
epr_attribute7,
epr_attribute8,
epr_attribute9,
epr_attribute10,
epr_attribute11,
epr_attribute12,
epr_attribute13,
epr_attribute14,
epr_attribute15,
epr_attribute16,
epr_attribute17,
epr_attribute18,
epr_attribute19,
epr_attribute20,
epr_attribute21,
epr_attribute22,
epr_attribute23,
epr_attribute24,
epr_attribute25,
epr_attribute26,
epr_attribute27,
epr_attribute28,
epr_attribute29,
epr_attribute30,
object_version_number,
request_id,
program_application_id,
program_id,
program_update_date
)
VALUES
(ben_enrt_prem_s.nextval,
l_val_va(i),
l_uom_va(i),
l_elig_per_elctbl_chc_id_va(i),
l_enrt_bnft_id_va(i),
l_actl_prem_id_va(i),
l_business_group_id_va(i),
l_epr_attribute_category_va(i),
l_epr_attribute1_va(i),
l_epr_attribute2_va(i),
l_epr_attribute3_va(i),
l_epr_attribute4_va(i),
l_epr_attribute5_va(i),
l_epr_attribute6_va(i),
l_epr_attribute7_va(i),
l_epr_attribute8_va(i),
l_epr_attribute9_va(i),
l_epr_attribute10_va(i),
l_epr_attribute11_va(i),
l_epr_attribute12_va(i),
l_epr_attribute13_va(i),
l_epr_attribute14_va(i),
l_epr_attribute15_va(i),
l_epr_attribute16_va(i),
l_epr_attribute17_va(i),
l_epr_attribute18_va(i),
l_epr_attribute19_va(i),
l_epr_attribute20_va(i),
l_epr_attribute21_va(i),
l_epr_attribute22_va(i),
l_epr_attribute23_va(i),
l_epr_attribute24_va(i),
l_epr_attribute25_va(i),
l_epr_attribute26_va(i),
l_epr_attribute27_va(i),
l_epr_attribute28_va(i),
l_epr_attribute29_va(i),
l_epr_attribute30_va(i),
l_object_version_number_va(i),
l_request_id_va(i),
l_program_application_id_va(i),
l_program_id_va(i),
l_program_update_date_va(i)
);
INSERT INTO ben_enrt_prem
(enrt_prem_id,
val,
uom,
elig_per_elctbl_chc_id,
enrt_bnft_id,
actl_prem_id,
business_group_id,
epr_attribute_category,
epr_attribute1,
epr_attribute2,
epr_attribute3,
epr_attribute4,
epr_attribute5,
epr_attribute6,
epr_attribute7,
epr_attribute8,
epr_attribute9,
epr_attribute10,
epr_attribute11,
epr_attribute12,
epr_attribute13,
epr_attribute14,
epr_attribute15,
epr_attribute16,
epr_attribute17,
epr_attribute18,
epr_attribute19,
epr_attribute20,
epr_attribute21,
epr_attribute22,
epr_attribute23,
epr_attribute24,
epr_attribute25,
epr_attribute26,
epr_attribute27,
epr_attribute28,
epr_attribute29,
epr_attribute30,
object_version_number,
request_id,
program_application_id,
program_id,
program_update_date
)
VALUES
(ben_enrt_prem_s.nextval,
l_val_va(i),
l_uom_va(i),
l_elig_per_elctbl_chc_id_va(i),
l_enrt_bnft_id_va(i),
l_actl_prem_id_va(i),
l_business_group_id_va(i),
l_epr_attribute_category_va(i),
l_epr_attribute1_va(i),
l_epr_attribute2_va(i),
l_epr_attribute3_va(i),
l_epr_attribute4_va(i),
l_epr_attribute5_va(i),
l_epr_attribute6_va(i),
l_epr_attribute7_va(i),
l_epr_attribute8_va(i),
l_epr_attribute9_va(i),
l_epr_attribute10_va(i),
l_epr_attribute11_va(i),
l_epr_attribute12_va(i),
l_epr_attribute13_va(i),
l_epr_attribute14_va(i),
l_epr_attribute15_va(i),
l_epr_attribute16_va(i),
l_epr_attribute17_va(i),
l_epr_attribute18_va(i),
l_epr_attribute19_va(i),
l_epr_attribute20_va(i),
l_epr_attribute21_va(i),
l_epr_attribute22_va(i),
l_epr_attribute23_va(i),
l_epr_attribute24_va(i),
l_epr_attribute25_va(i),
l_epr_attribute26_va(i),
l_epr_attribute27_va(i),
l_epr_attribute28_va(i),
l_epr_attribute29_va(i),
l_epr_attribute30_va(i),
l_object_version_number_va(i),
l_request_id_va(i),
l_program_application_id_va(i),
l_program_id_va(i),
l_program_update_date_va(i)
);