[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update to be determined only by
acty-base-rt-id.
26-May-2000 shdas 115.60 round rates before variable
rates applied--5152.
29-May-2000 mhoyes 115.61 Added record structures to main.
29-May-2000 mhoyes 115.62 Re-applied fix 5152.
31-May-2000 mhoyes 115.63 Added enrt_perd_strt_dt to p_currepe_row.
12-JUN-2000 tilak 115.64 acty_base_rt >= enrt_perd_strt_dt condition
is removed from c_abr cursor for bug 5173
This conition return no row .when the enrollment for future
period the date betwen chek the future period and this condion
check the current period
26-JUN-2000 shdas 115.65 added codes for new mlt_cd 'SAREC'.
28-JUN-2000 mhoyes 115.66 - Fixed duplicate rate problem.
- Tuned c_prv.
- Bypassed c_current_elig when prtn_strt_dt
is passed in.
25-AUG-2000 pbodla 115.67 - Bug : 1386285 : When RT_OVRIDN_THRU_DT
is null assume it as EOT.
28-SEP-2000 stee 115.68 - Added p_cal_val for UK Select to
calculate a child rate if the parent
rate is calculate at enrollment.
27-AUG-2000 RCHASE 115.69 - wwbug#1207803.999 - correct perfprv
cursor to fetch rows when
updates have been made to the enrollment
result past the life event occured date.
This is a leapfrog version based on
115.67.
29-AUG-2000 jcarpent 115.70 - Merge version based on 115.68 and
115.69.
06-Nov-2000 tilak 115.71 bug-1480407 calculation of rate for enter at entrolment is added
06-nov-2000 tilak 115.72
29-nov-2000 gperry 115.73 Added person_id to epe record so that
rules work correctly in rate and
coverage call. WWBUG 1510623.
15-jan-2001 tilak 115.74 encremental chek is added to the rate
22-jan-2001 tilak 115.75 p_parent_val added to calculate the parent value
when the child recod called for calucaltion from
enrollment and parent entr_val_at_entr is on
07-mar-2001 tilak 115.76 bug : 1628762, when the premium based on coverage
and coverage is range then benefit id reauired
to get the premiuum value -c_enrt_prem cursor changed
06-mar-2001 ikasire 115.77 bug 1650517 not passing p_complete_year_flag
for SAREC condition for communicated amount
calculation
15-mar-2001 tilak 115.78 bun : 1676551 calling premium calcualtion
21-mar-2001 tilak 115.79 ultmt_upr_lmt,ultmt_lwr_lmt is validated
01-apr-2001 tilak 115.80 ultmt_upr_lmt_calc_rl ,ltmt_lwr_lmtcalc_rl is validated
01-may-2001 kmahendr 115.81 Bug#1749068 - changed p_cal_val value depending on parent rate's
enter value at enrollment flag
03-may-2001 kmahendr 115.83 As version 115.82 was based on leapfrog of version 115.77,
version 115.81 brought forward.
13-Jul-2001 ikasire 115.84 bug 1834655 fixed the code for default enrollment
13-Jul-2001 ikasire 115.85 removed the show errors
18-Jul-2001 ikasire 115.86 bug 1834655 added ann_rt_val to prv and perfprv
cursors
27-Aug-2001 mhoyes 115.87 - Replaced generic error messages,
- 91832 with 92748.
- 91833 with 92743, 92738, 92739, 92740
92741, 92742
- 91835 with 92744, 92745, 92746, 92747
29-aug-2001 tilak 115.88 bug:1949361 jurisdiction code is
derived inside benutils.formula.
27-Sep-2001 kmahendr 115.89 Bug#1981673-Added parameter ann_mn_elcn_val and
ann_mx_elcn_val to ben_determine_variable_rates
call and returned values assigned
05-dec-2001 tjesumic 115.90 Add,mutltifly,substract added for vapro values
bug:2112513
05-Feb-2002 kmahendr 115.91 Bug#2207947-Added a cursor c_perfPrv_2 to get the
prtt_rt_val_id if the coverage and rate starts in
future
05-Feb-2002 kmahendr 115.92 Added dbdrv : checkfile line
06-Feb-2002 pabodla 115.93 Calculating cmcd_mn/mx values.
07-Feb-2002 ikasire 115.94 Bug 2192102 Flex Credit fails in case of the
Flex Credit or VAPRO associated with the FC is
based on coverage calculation- fixed
15-Nov-2001 dschwart/ 115.95 Bug#1791203: altered estimate only functions to
gopal calculate correctly when frequency rules are
altered to 24 pay periods. bug : 1794303
26-Mar-2002 kmahendr 115.96 Bug#1833008 - Added parameter p_cal_for to the call
determine_compensation to handle multi-assignment.
18-Apr-2002 lmcdonal 115.97 l_coverage_val was not always being loaded
from p_bnft_amt. Needed for SS call to
this package.
30-Apr-2002 kmahendr 115.98 Added token to error message-91832.
01 May 2002 lmcdonal 115.99 Bug 2048257 Added main_w.
20-May-2002 pabodla 115.100 If this procedure is called from enrt
process then validate value with
enrt_rt table's min/max instead of
standard rate min max values.
23 May 2002 kmahendr 115.101 Added a parameter to main.
115.102 No changes
03 Jun 2002 pabodla 115.103 Bug 2400850 : Checking prtt_enrt_rslt_id
before assigning any values to p_dflt_val
05 Jun 2002 pabodla 115.104 Bug 2403243 : If vapro and abr both has
null values for min/max then assigning
null to p_mn/max_elcn_val variables
instead of zero
08-Jun-2002 pabodla 115.105 Do not select the contingent worker
assignment when assignment data is
fetched.
27-Jun-2002 Tilak 115.106 Bug 2438506: if vapro treatment code is
multiply by and abr have null value
then trat abr value as 1 else causes
null rate value calculation.
08-Jul-2002 ikasire 115.107 Bug 2445318 handling the p_dflt_val and
p_ann_dflt_val cases
12-Jul-2002 vsethi 115.108 Bug 1699585 added tokens for message BEN_91932_NOT_INCREMENT
15-Jul-2002 vsethi 115.109 Wrong variable referenced in cursor c_pln
04-sep-2002 kmahendr 115.111 Added new acty_ref_perd_cd - phr.
17-Sep-2002 hnarayan 115.112 Bug 2569884 - communicated min and max values should
be calculated irrespective of whether calculate for
enrollment flag is checked or not
11-Oct-2002 vsethi 115.113 Rates Sequence no enhancements. Modified to cater
to new column ord_num on ben_acty_base_rt_f
24-Oct-2002 shdas 115.114 Added ben_env_object.init to main_w
13-Nov-2002 vsethi 115.115 Bug 1210355, if variable rate is detected the mlt code
attached to the variable profile should be displayed
for the rate
09-DEC-2002 hnarayan 115.116 Bug 2691169 - added order by clause to cursor c_asg
so that it retrieves assignmnets of type 'E' first
and then of type 'B'
16 Dec 02 hnarayan 115.117 Added NOCOPY hint
24 Dec 02 kmullapu 115.118 Added new cursor c_pl_opt_name to display pl-opt name
in error messages. Bug 2385186
27 Dec 02 ikasire 115.119 Bug 2677804 changes for override thru date
23 Jan 03 ikasire 115.120 Bug 2149438 using to overloaded annual_to_period procedure
and rounding to 4 digits to improve the precision.
05-Feb-03 gjesumig 115.121 GSP enhancement, calculation added for new mlt_cd 'PRV'
12-Feb-03 tjesumic 115.122 2797031 , if premium value is null it is recaulcualted
nvl added to premium value
13-Feb-03 kmahendr 115.123 Added a parameter - p_iss_val and codes for auto_distr
22-May-03 kmahendr 115.124 Included ERL to evaluate formula
06-Jun-03 kmahendr 115.125 Rt_mlt_cd is populated from variable rate only if the
treatment code is Replace.Bug#2996378
21-oct-03 hmani 115.126 Bug 3177401 - Changed p_date_mandatory_flag to 'Y'
11-nov-03 hmani 115.127 Reversing back the previous fix
12-Nov-03 ikasire 115.128 Bug 3253180 Defaults not working when enter value at
enrollment annual flag is checked for the current
enrollment
13-Nov-03 kmahendr 115.129 Bug#3254240 - added codes in main_w to get the
communicated value
23-Feb-04 stee 115.130 Bug 3457483 - Check the assignment to use code
in activity base rate when selecting the
assignment.
14-Apr-04 mmudigon 115.132 FONM changes
29-May-04 mmudigon 115.133 FONM changes continued
05-Aug-04 tjesumic 115.134 FONM for ben_derive_factors fonm date paseed as param
03-Sep-04 hmani 115.135 fixed cmcd_dflt_val issue Bug 3274902
06-sep-04 hmani 115.136 Modified for annual value flag Bug 3274902
Also fixed few missed out FONM issues.
03-Dec-04 vvprabhu 115.137 Bug 3980063 - SSBEN Trace Enhancement
03-Dec-04 ikasire 115.138 Added defaults to main_w as per main
14-dec-04 nhunur 115.139 cwb now allows null values for min/max/incrmt val
so no need to validate
30-dec-2004 nhunur 115.140 4031733 - No need to open cursor c_state.
24-Jan-2004 swjain 115.141 3981982 - Min Max Enhancement. Added code to evaluate
min max rule.
27-Jan-2004 swjain 115.142 Updated the message number of the the message
BEN_XXXX_MN_MX_RL_OUT_ERR to
BEN_94130_MN_MX_RL_OUT_ERR
31-Jan-2004 swjain 115.143 3981982 - Added more input paramters in call to
benutils.formula
17-Feb-2004 vvprabhu 115.144 Bug 4143012 : Changes in procedure main_w
to avoid value of cost1 being displayed for
cost2 when cost1 is 'SAREC'
12-May-2005 ikasire 115.145 Moved the fnd_message binding into IF clause to avoid
misleading error message from SSBEN
23-May-2005 lakrish 115.146 4235088, do fnd_number.canonical_to_number() to the
FF output before assigning to a number variable
09-Jun-2005 nhunur 115.147 4383988, do fnd_number.canonical_to_number() to all
FF that return a number variable
05-Sep-05 swjain 115.148 Bug No 4538786 Per Pay Period with frequency rules
changes in procedure main
13-Sep-2005 rbingi 115.151 Bug-4604560 in procedure main, Added close for
cursor get_rt_and_element
28-Sep-2005 nhunur 115.152 Bug 4637525 : CWB- MULTIPLE OF COMPENSATION CHANGES.
02-Feb-2006 stee 115.153 Bug 4873847. cwb: Round the rec_val,
rec_mn_val, rec_mx_val.
24-Apr-2006 rgajula 115.154 Bug 5031047 Modified the order by clause to prv.rt_start_dt asecending
and added the clause c_effective_date < prv.rt_end_dt for the cursors
c_prv,c_perfprv,c_perfprv_2
18-May-2006 rgajula 115.155 Bug 5225815 do not prorate if there are no prior elections
or the l_rt_strt_dt is null
07-Jul-2006 bmanyam 115.156 5371364 : Do not Operate (i.e multiply/divide..)
the Increment Value.
21-Sep-2006 vborkar 115.157 5534498 : Passed p_person_id and p_start_date(rt_strt_dt)
parameters to annual_to_period for correct rate calculation
on 'Recalculate'.
25-Sep-2006 bmanyam 115.158 5557305 : Premium has to be recalculated everytime
for rates of type 'Multiple of Premium'
3-nov-2006 nhunur 115.159 c_perfprv,c_perfprv_2 to handle non recurrring
1-Feb-2007 bmanyam 115.160 5748126: Update BEN_ENRT_RT with the
latest PRV_ID, which occurs before the LE_OCRD_DT
23-Feb-2006 bmanyam 115.161 5898039: same as above. [ON or before LE_OCRD_DT]
22-Jan-2007 rtagarra 115.160 ICM Changes.
19-Dec-2007 krupani 115.162 Changes against Bug 6015724 incorporated from 115.162.
Forward port Bug is 6158436
26-Dec-2007 krupani 115.164 Changes against Bug 6314463 and 6330056 incorporated from 115.164
in R12 mainline
17-Jun-2008 sagnanas 115.165 Bug 7154229
*/
--------------------------------------------------------------------------------
--
g_package varchar2(80) := 'ben_determine_activity_base_rt';
select pln.name || ' '|| opt.name
from ben_elig_per_elctbl_chc epe,
ben_pl_f pln,
ben_oipl_f oipl,
ben_opt_f opt
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.pl_id = pln.pl_id
and epe.oipl_id = oipl.oipl_id(+)
and oipl.opt_id = opt.opt_id(+)
and p_effective_date between
pln.effective_start_date and pln.effective_end_date
and p_effective_date between
oipl.effective_start_date(+) and oipl.effective_end_date(+)
and p_effective_date between
opt.effective_start_date(+) and opt.effective_end_date(+);
select enrt.mn_elcn_val,
enrt.mx_elcn_val,
enrt.incrmt_elcn_val
from ben_enrt_rt enrt
where (enrt.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
or enrt.enrt_bnft_id = p_enrt_bnft_id) and
enrt.acty_base_rt_id = p_acty_base_rt_id;
select asg.assignment_id,
asg.organization_id,
asg.payroll_id,
loc.region_2
from per_all_assignments_f asg,hr_locations_all loc
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 c_effective_date
between asg.effective_start_date
and asg.effective_end_date
order by -- bug fix 2691169
asg.assignment_type desc;
select epe.pl_id,
epe.oipl_id,
epe.pgm_id,
epe.pl_typ_id,
epe.business_group_id,
epe.per_in_ler_id,
epe.plip_id,
epe.ptip_id,
epe.prtt_enrt_rslt_id,
epe.roll_crs_flag,
epe.elctbl_flag,
epe.enrt_cvg_strt_dt,
epe.enrt_cvg_strt_dt_cd,
epe.enrt_cvg_strt_dt_rl,
epe.yr_perd_id,
epe.pl_ordr_num,
epe.oipl_ordr_num,
pel.enrt_perd_id,
pel.lee_rsn_id,
pel.enrt_perd_strt_dt,
pel.acty_ref_perd_cd,
pil.person_id,
pil.ler_id
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil,
ben_pil_elctbl_chc_popl pel
where 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.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and epe.per_in_ler_id = pel.per_in_ler_id;
select opt_id
from ben_oipl_f oipl
where oipl.oipl_id = l_epe.oipl_id
and c_effective_date
between oipl.effective_start_date
and oipl.effective_end_date;
select abr.rt_mlt_cd,
abr.val,
abr.mn_elcn_val,
abr.mx_elcn_val,
abr.incrmt_elcn_val,
abr.dflt_val,
abr.rndg_cd,
abr.rndg_rl,
abr.pl_id,
abr.oipl_id,
abr.pgm_id,
abr.acty_typ_cd,
abr.rt_typ_cd,
abr.bnft_rt_typ_cd,
abr.ptd_comp_lvl_fctr_id,
abr.entr_val_at_enrt_flag,
abr.clm_comp_lvl_fctr_id,
abr.tx_typ_cd,
abr.val_calc_rl,
abr.nnmntry_uom,
abr.entr_ann_val_flag,
abr.comp_lvl_fctr_id,
abr.dsply_on_enrt_flag,
abr.use_to_calc_net_flx_cr_flag,
abr.rt_usg_cd,
abr.ann_mn_elcn_val,
abr.ann_mx_elcn_val,
abr.lwr_lmt_val,
abr.lwr_lmt_calc_rl,
abr.upr_lmt_val,
abr.upr_lmt_calc_rl,
abr.actl_prem_id,
abr.use_calc_acty_bs_rt_flag ,
abr.det_pl_ytd_cntrs_cd, -- Bug#1791203: added
abr.element_type_id,
abr.pay_rate_grade_rule_id,
abr.ordr_num,
abr.rate_periodization_rl, --BUG 3463457
abr.mn_mx_elcn_rl, -- Min Max Enhancement : 3981982
abr.input_va_calc_rl
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = p_acty_base_rt_id
and c_effective_date
between abr.effective_start_date
and abr.effective_end_date ;
select enb.val,
enb.dflt_val
from ben_enrt_bnft enb
where enb.enrt_bnft_id = p_enrt_bnft_id;
select
enb.enrt_bnft_id
from
ben_enrt_bnft enb,
ben_elig_per_elctbl_chc epe,
ben_elig_per_elctbl_chc epe_fc
where
enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and epe.pgm_id = epe_fc.pgm_id
and nvl(epe.oipl_id,-99999) = nvl(epe_fc.oipl_id,-99999)
and nvl(epe.pl_id,-99999) = nvl(epe_fc.pl_id,-99999)
and nvl(epe.plip_id,-99999) = nvl(epe_fc.plip_id,-99999)
and nvl(epe.ptip_id,-99999) = nvl(epe_fc.ptip_id,-99999)
and nvl(epe.pl_typ_id,-99999) = nvl(epe_fc.pl_typ_id,-99999)
and epe.per_in_ler_id = epe_fc.per_in_ler_id
and epe_fc.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id ;
select ccm.cvg_amt_calc_mthd_id
from ben_cvg_amt_calc_mthd_f ccm,
ben_pl_f pln
where pln.pl_id = l_epe.pl_id
and pln.pl_id = ccm.pl_id
and c_effective_date
between pln.effective_start_date
and pln.effective_end_date
and c_effective_date
between ccm.effective_start_date
and ccm.effective_end_date;
select ccm.cvg_amt_calc_mthd_id
from ben_cvg_amt_calc_mthd_f ccm,
ben_oipl_f cop
where cop.oipl_id = l_epe.oipl_id
and cop.oipl_id = ccm.oipl_id
and c_effective_date
between cop.effective_start_date
and cop.effective_end_date
and c_effective_date
between ccm.effective_start_date
and ccm.effective_end_date;
select abr2.acty_base_rt_id,abr2.entr_val_at_enrt_flag
from ben_acty_base_rt_f abr,
ben_acty_base_rt_f abr2
where abr.acty_base_rt_id = p_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 pln.nip_acty_ref_perd_cd
,pln.nip_enrt_info_rt_freq_cd
,name
from ben_pl_f pln
where pln.pl_id = c_pl_id
and c_effective_date
between pln.effective_start_date
and pln.effective_end_date;
select pgm.acty_ref_perd_cd,
pgm.enrt_info_rt_freq_cd
from ben_pgm_f pgm
where pgm.pgm_id = l_epe.pgm_id
and c_effective_date
between pgm.effective_start_date
and pgm.effective_end_date;
select abp.bnft_prvdr_pool_id
from ben_aplcn_to_bnft_pool_f abp
where abp.acty_base_rt_id = p_acty_base_rt_id
and c_effective_date
between abp.effective_start_date
and abp.effective_end_date
and abp.bnft_prvdr_pool_id in
(select epe.bnft_prvdr_pool_id
from ben_elig_per_elctbl_chc epe
where epe.pgm_id = l_epe.pgm_id
and epe.per_in_ler_id = l_epe.per_in_ler_id);
select prv.prtt_rt_val_id,
prv.rt_ovridn_flag,
prv.rt_ovridn_thru_dt,
prv.rt_val,
prv.ann_rt_val,
prv.cmcd_rt_val, -- Bug 6015724
prv.rt_strt_dt,
prv.rt_end_dt
from ben_prtt_rt_val prv,
ben_prtt_enrt_rslt_f pen,
ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
and epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and prv.prtt_rt_val_stat_cd is null
and prv.acty_base_rt_id = c_acty_base_rt_id
and c_effective_date
between pen.enrt_cvg_strt_dt
and pen.enrt_cvg_thru_dt
--and prv.acty_typ_cd = l_abr.acty_typ_cd bug 1295277 comment out.
--and prv.tx_typ_cd = l_abr.tx_typ_cd
and c_effective_date
between pen.effective_start_date
and pen.effective_end_date
/* get the latest prv for the enrollment result id */
and ((c_effective_date <= prv.rt_end_dt) or (prv.rt_strt_dt = prv.rt_end_dt))
order by prv.rt_strt_dt desc; -- 5748126 Changed ORDER-BY from ASC to DESC
select prv.prtt_rt_val_id,
prv.rt_ovridn_flag,
prv.rt_ovridn_thru_dt,
prv.rt_val,
prv.ann_rt_val,
prv.cmcd_rt_val, -- Bug 6015724
prv.rt_strt_dt,
prv.rt_end_dt
from ben_prtt_rt_val prv,
ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and prv.prtt_rt_val_stat_cd is null
and prv.acty_base_rt_id = c_acty_base_rt_id
and c_effective_date
between pen.enrt_cvg_strt_dt
and pen.enrt_cvg_thru_dt
--RCHASE wwbug#1207803.999 - correct perfprv cursor to fetch rows when
-- updates have been made to the enrollment
-- result past the life event occured date
--and c_effective_date
-- between pen.effective_start_date
-- and pen.effective_end_date
and pen.effective_end_date = hr_api.g_eot
and ((c_effective_date <= prv.rt_end_dt) or (prv.rt_strt_dt = prv.rt_end_dt))
--RCHASE end
order by prv.rt_strt_dt desc; -- 5748126 Changed ORDER-BY from ASC to DESC
select prv.prtt_rt_val_id,
prv.rt_ovridn_flag,
prv.rt_ovridn_thru_dt,
prv.rt_val,
prv.ann_rt_val,
prv.cmcd_rt_val, -- Bug 6015724
prv.rt_strt_dt,
prv.rt_end_dt
from ben_prtt_rt_val prv,
ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and prv.prtt_rt_val_stat_cd is null
and prv.acty_base_rt_id = c_acty_base_rt_id
and c_effective_date < pen.enrt_cvg_thru_dt
and pen.effective_end_date = hr_api.g_eot
and ((c_effective_date <= prv.rt_end_dt) or (prv.rt_strt_dt = prv.rt_end_dt))
order by prv.rt_strt_dt desc; -- 5748126 Changed ORDER-BY from ASC to DESC
select epo.prtn_strt_dt
from ben_elig_per_f ep,
ben_elig_per_opt_f epo,
ben_per_in_ler pil
where ep.person_id=c_person_id
and nvl(ep.pgm_id,-1)=c_pgm_id
and ep.pl_id=c_pl_id
and epo.opt_id=c_opt_id
and c_effective_date
between ep.effective_start_date and ep.effective_end_date
and ep.elig_per_id=epo.elig_per_id
and c_effective_date
between epo.effective_start_date and epo.effective_end_date
and pil.per_in_ler_id(+) = ep.per_in_ler_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
or
pil.per_in_ler_stat_cd is null)
union
select prtn_strt_dt
from ben_elig_per_f ep,
ben_per_in_ler pil
where ep.person_id=c_person_id
and nvl(ep.pgm_id,-1)=c_pgm_id
and ep.pl_id=c_pl_id
and c_effective_date
between ep.effective_start_date and ep.effective_end_date
and pil.per_in_ler_id(+) = ep.per_in_ler_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
or pil.per_in_ler_stat_cd is null)
and c_nvlopt_id=hr_api.g_number;
select ecr.val
from ben_enrt_prem ecr,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe
where ecr.actl_prem_id = p_actl_prem_id
and ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
and pil.per_in_ler_id = epe.per_in_ler_id
--- when the premium depend on coverage , coverage inturn multi rance
--- benefit is is required to find the correct row : bug 1628762
and (ecr.enrt_bnft_id is null or nvl(ecr.enrt_bnft_id ,0) = nvl(p_enrt_bnft_id ,0) )
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select region_2
from hr_locations_all loc,per_all_assignments_f asg
where loc.location_id = asg.location_id
and asg.assignment_type <> 'C'
and asg.person_id = p_person_id
and asg.primary_flag = 'Y'
and p_effective_date between
asg.effective_start_date and asg.effective_end_date;
select value
from pay_grade_rules_f pgr
where grade_rule_id = c_pay_rate_grade_rule_id
and c_effective_date between
pgr.effective_start_date and pgr.effective_end_date;
select auto_distr_flag
from ben_enrt_perd enp,
ben_pil_elctbl_chc_popl pel,
ben_elig_per_elctbl_chc epe
where enp.enrt_perd_id = pel.enrt_perd_id
and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
select abr.acty_base_rt_id, abr.element_type_id, epe.elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc epe, ben_acty_base_rt_f abr
where (epe.pl_id = abr.pl_id or epe.plip_id = abr.plip_id)
and epe.pgm_id = c_pgm_id and epe.per_in_ler_id = c_per_in_ler_id
and epe.comp_lvl_cd = 'PLANFC';
select 'Y'
from ben_actl_prem_f
where actl_prem_id = p_actl_prem_id
and (mlt_cd in ('CVG', 'NSVU') or
exists (select null from ben_actl_prem_vrbl_rt_f
where actl_prem_id = p_actl_prem_id));
select pl_id,oipl_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 p.element_link_id
from pay_element_links_f p
where element_type_id = p_element_type_id
and p_effective_date between p.effective_start_date
and p.effective_end_date;
select opt_typ_cd
from ben_pl_typ_f ptp,
ben_pl_f pln
where pln.pl_id = p_pl_id
and ptp.pl_typ_id = pln.pl_typ_id;
select p.input_value_id,p.default_value
from pay_link_input_values_f p
where element_link_id = p_element_link_id
and p_effective_date between
effective_start_date and effective_end_date
order by input_value_id ;
select eiv.extra_input_value_id,
eiv.input_value_id,
eiv.acty_base_rt_id,
eiv.input_text,
eiv.return_var_name,
eiv.upd_when_ele_ended_cd
from ben_extra_input_values eiv
where eiv.acty_base_rt_id = p_abr_id;
select input_value_id
from ben_extra_input_values
where extra_input_value_id = p_extra_input_value_id;
l_outputs.DELETE;
select ecr.rt_mlt_cd,
ecr.enrt_rt_id,
ecr.cmcd_acty_ref_perd_cd,
enb.elig_per_elctbl_chc_id,
ecr.acty_base_rt_id,
ecr.business_group_id
from ben_enrt_rt ecr,
ben_enrt_bnft enb
where enb.enrt_bnft_id = p_enrt_bnft_id
and ecr.enrt_bnft_id = enb.enrt_bnft_id
and ecr.acty_base_rt_id = p_acty_base_rt_id; --Bug 4143012 get the correct rate
select element_type_id,rate_periodization_rl
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = p_acty_base_rt_id
and l_effective_date between abr.effective_start_date
and abr.effective_end_date;
select enr.rt_strt_dt
from ben_enrt_rt enr
where enrt_rt_id = p_enrt_rt_id;