[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Update cash entry not just ins
06 jul 98 jcarpent 115.5 When update result pass ovn
27 jul 98 jcarpent 115.6 Fix messages take out nocopy message.get
30 jul 98 jcarpent 115.7 removed use of the global g_credit_pool
for most stuff.
22 SEP 98 GPERRY 115.8 Corrected error messages
13 OCT 98 jcarpent 115.9 Added excess credit processing
16 OCT 98 jcarpent 115.10 Changed total_pools to allow non-flex
calls to pass through with no error.
02 NOV 98 jcarpent 115.11 Fixed accum pools for chc not providing
credits
10 NOV 98 jcarpent 115.12 Added bg_id to delete_bpl call
04 DEC 98 jcarpent 115.13 Check max elctn val on enrt_rt
Added code comments
Use pool_rlovr_rqmt_f.prtt_elig_rlovr_rl
Use pool.auto_alct_excs_flag
23 DEC 98 jcarpent 115.14 Subtract forfeitures from
total flex credits.
29 JAN 99 shdas 115.15 added codes for comp objs.
17 MAR 99 shdas 115.17 modified create_rollover_enrollment.
16 APR 99 shdas 115.18 changed ben_per_in_ler_f to ben_per_in_ler.
29 APR 99 shdas 115.19 added parameters to genutils.formula.
29 Apr 99 lmcdonal 115.20 prtt_enrt_rslt now has stat cd.
04 May 99 shdas 115.21 added jurisdiction code.
27 May 99 maagrawa 115.22 Added new procedures to re-calculate
flex credits when choice may not be present.
25-Jun-99 jcarpent 115.23 Added per_in_ler_id to bpl.
09-Jul-99 jcarpent 115.24 Added checks for backed out nocopy pil
20-JUL-99 Gperry 115.25 genutils -> benutils package
rename.
12-AUG-99 maagrawa 115.26 Corrected cursor c_cash_abr
to fetch values into correct
variables.
07-Sep-99 shdas 115.27 Added codes for cmbn_ptip_opt.
fixed call to pay_mag_util (TGUY)
added bnft_val to election_information
21-Sep-99 lmcdonal 115.28 Added rlovr_val_rl and
dflt_excs_trtmt_rl calls.
23-Sep-99 cparmar 115.29 added item pil_flex.ler_id in total_pools
for the group by clause
21-Oct-99 lmcdonal 115.30 ledger did not always have
per-in-ler-id filled in
12-Nov-99 lmcdonal 115.31 Calls to create_Benefit_Prvdd_Ledger
must pass in person id and enrt_mthd_cd.
Added enrt_mthd_cd to create_debit_ledger_entry,
recompute_flex_credits
17-Nov-99 pbodla 115.32 added acty_base_rt_id parameter to formula call
when prtt_elig_rlovr_rl is evaluated.
Also added to run_rule procedure.
18-Nov-99 pbodla 115.33 - added elig_per_elctbl_chc_id parameter
to run_rule, formula calls.
- elig_per_elctbl_chc_id selected in cursor
c_cash_abr
- Added cursor c_epe
18-Jan-00 shdas 115.34 - changed c-ledger cursor in
cleanup_invalid_ledger_entries.
25-Jan-00 maagrawa 115.35 -Added parameter p_per_in_ler_id to procedures
create_credit_ledger_entry,
create_debit_ledger_entry,
cleanup_invalid_ledger_entries,
create_flex_credit_enrolment, total_pools,
create_rollover_enrollment.
-Fixed procedures to not look at 'STRTD'
per_in_ler. (Bug 1148445)
29-Jan-00 shdas 115.36 modified remove_bnft_prvdd_ldgr(bug 4493)
28-Feb-00 maagrawa 115.37 Pass p_source to delete_enrollment.
28-Feb-00 shdas 115.38 Added get_dt_upd_mode(4785).
03-Mar-00 maagrawa 115.39 Get the acty_base_rt_id for the pool in
procedure distribute_credits (c_cash_abr) using
the ledger row.
09-Mar-00 lmcdonal 115.40 Support for oiplip flex credit rates.
05-Apr-00 mmogel 115.41 Added tokens to messages to make them more
meaningful to the user
15-Aug-00 maagrawa 115.42 Removed the unions from the c_choice cursor in
accumulate_pools procedure.
28-Sep-00 stee 115.43 UK Select changes. Net credits are processed
through payroll.
09-Oct-00 stee 115.44 UK Select changes. When a person is found
ineligible for a comp object, the net credits
are re-calculated.
12-Oct-00 maagrawa 115.45 Added p_old_rlovr_amt to
create_rollover_enrollment.
Pass the bnft_id and bnft_val, if
benefit record exists for rollover plan.
17-Oct-00 stee 115.46 Change c_prv2 to get acty_typ_cd from
ben_prtt_rt_val. Fix total_credits in
recompute_flex_credits to check for null
forfeited amount.
18-Oct-00 stee 115.47 Added total pool restriction edit for rollover
amount at the plan level.
16-Jan-01 mhoyes 115.48 - Added calculate only mode parameter to
create_debit_ledger_entry for EFC.
13-Feb-00 pbodla 115.50 - Put the version 115.48 with changes in 115.49
30-Mar-01 kmahendr 115.51 - Bug#1708166 - when comp.object is replaced in the
subsequent life event, the flex credit entry for
the deenrolled comp.object is not being deleted
from ben_bnft_prvdd_ldgr_f with the result the
used value is wrongly shown - added codes to
cleanup_invalid_ledger_entries
09-Apr-01 pbodla 115.52 - Bug 1711831 - While getting the activity base rate
for the provided credits choice row for the pool
we are working with cursor c_cash_abr is not
getting the row if the person was deenrolled from
comp object in the benmngle run. But the ledger
row is still sitting. To avoid activity base rate
row not found error use the cursor c_get_pool_abr.
12-Apr-1 ikasire 115.53 bug 1285336 changed the cursor
c_cmbn_ptip_opt_enrollment
01-May-01 kmahendr 115.54 Bug#1750825-Added edit to cleanup credit ledger entries
if the comp. object is deenrolled in the subsequent life
event.
Added Get_DT_Upd_Mode calls before update_ledger_api to
fix error 07211-future dated rows exist.
02-May-01 kmahendr 115.55 Leap frog version of 115.52 and changes in version
115.54 included
02-May-01 kmahendr 115.56 Version 115.54 brought in as version 115.55 was leap frog
17-May-01 maagrawa 115.57 Modified call to ben_global_enrt procedures.
10-Jul-01 mhoyes 115.58 - Converted compute_excess, create_credit_ledger_entry and
create_debit_ledger_entry for EFC.
27-aug-01 tilak 115.59 bug:1949361 jurisdiction code is
derived inside benutils.formula.
23-Oct-01 kmahendr 115.60 bug#2065445-cursor c_bnft_prvdd_ldgr and c_bnft_prvdd_ldgr_2
modified to select row based on p_effective_date
26-Oct-01 kmahendr 115.61 bug#2077743-in cursor c_prv order by rt_strt_dt desc added to
fetch the last rate row.
30-Oct-01 pbodla 115.62 bug#1931485-modified cusor c_rollovers to outerjoin
to ben_oipl_f table.
02-Nov-01 kmahendr 115.63 cursors in person_enrolled_in_choice modified to look for
effective_end_date = eot
09-Jan-02 kmahendr 115.64 Added a cursor c_enrt_rt to get the acty_base_rt_id for
getting the ledger entries thro cursor c_bnft_prvdd_ldgr_2
in cleanup_invalid ledger entries - Bug#2171014
25-Jan-02 pbodla 115.65 Bug 2185478 Added procedure to validate the
rollover value entered by the user on flex
enrollment form and show proper message immediately
25-Jan-02 stee 115.66 Check if a person is enrolled in the comp
object prior to deleting the ledger for a
roll over enrollment. Bug 2119974.
28-Jan-02 pbodla 115.67 Added dbdrv lines for GSCC compliance.
07-Feb-02 ikasire 115.68 2199238 rollover records not appearing in the
flex enrollment form fixed.
11-Feb-02 kmahendr 115.69 Bug#2210322-Ledger rows for used_val need not be updated
in the same life event. Changed cursor in create_debit_ledger
procedure.
15-Feb-02 iaksire 115.70 Bug 2185478 added the union to the cursor in default_rollover
to handle the cases of enrt_bnft records
06-Mar-02 kmahendr 115.71 Bug#2254326 - Modified cursor c_flx_credit_plan to pick up
the one not deenrolled.
11-mar-02 tjesumic 115.72 bug#2251057 auto_alct_exces_flag control moved just for
Rollover
13 MAR 02 tjesumic 115.73 bug#2251057 automatic rollover is executed for default
enrollment cursor c_get_mthd added
15 May 02 ikasire 115.74 Bug 2200139 create_credit_ledger_entry always recomputes the
provided value from the enrt_rt record if the rate is changed
on bpp record. Now this will not happen if the enrollment
method code is 'O' (for Override Enrollment)
23 May 02 kmahendr 115.75 Added a parameter to ben_determine_acty_base_rt
39 May-02 ikasire 115.76 Bug 2386000 Added cursors to pass lee_rsn_id to rate_and
_coverage_dates call
08-Jun-02 pabodla 115.31 - Do not select the contingent worker
assignment when assignment data is
fetched.
08 Aug 02 kmahendr 115.78 -Bug#2382651 - added additional parameter to total_pools
and create_flex_credit_enrollment and made changes to
work for Multiple flex programs.
14 Aug 02 kmahendr 115.79 - Bug#2441871 - In Update_rate procedure the ler_id is
fetched from per_in_ler and create_prtt_rt api is called
only if enrt rt id is not null
11-Oct-02 vsethi 115.80 Rates Sequence no enhancements. Modified to cater
to new column ord_num on ben_acty_base_rt_f
06-Nov-02 ikasire 115.81 Bug 2645993 Fixed the case where Used amount is more than
the provided amount.
11-Nov-02 lakrish 115.82 Bug 2645624 Changed create_flex_credit_enrolment to allow
non-flex calls to pass through with no error.
02-dec-02 hnarayan 115.83 Bug fix 2689926 - fixed c_choice cursor in accumulate_pools
procedure to pick up the correct benefit pool row from EPE
30-Dec-02 mmudigon 115.84 NOCOPY
13-Feb-03 kmahendr 115.85 Added a parameter to call -acty_base_rt.main
01-May-03 pbodla 115.86 Removed where clause of c_choice as part of
2917128 as provided ledger entries are not
getting created properly.
13-May-03 rpgupta 115.87 Bug 2988218 - Even When the flex shell plan is invalid,
processing completes fine. If p_rt_val is null, we need
the error to be displayed. So changed the expression.
Also do this check only if it is called form a flex pgm,
so that a call from non flex does'nt error.
13-May-03 rpgupta 115.88 Added COBRAFLX to chk the program type
21-Aug-03 kmahendr 115.89 Bug#2736036 - for net credit method,deduction rate is updated to
zero if contribution is positive and vice versa.
19-Mar-04 ikasire 115.90 Added formula rate_periodization_rl
16-Apr-04 kmahendr 115.91 Added codes in total_pools and cleanup_invalid_ledger to
handle situations when benefit pool already enrolled
becomes inactive
02-Jun-04 nhunur 115.92 changed c_prv to prevent MJC
31-Aug-04 ikasire 115.93 Bug 3864152 fixed the nvl condition
22-Mar-05 abparekh 115.94 Bug 4251187 : Pass proper per_in_ler_id to update of BPL
in procedure distribute_credits
29-Apr-05 kmahendr 115.95 Bug#4340736 - ledger entry for forfeiture is written wrongly
Code added in total_pools
03-May-05 mmudigon 115.96 Bug 4320660. Order by clause for cursors
c_prv2 and c_prv_child
07-Jul-05 kmahendr 115.97 Bug#4473573 - added net_Credits_method condition
before resetting distributed value to 0 in
total_credits.
09-Aug-05 swjain 115.98 Bug#4538041 - Modified c_pgm_enrollment cursor in
procedure person_enrolled_in_choice to exclude enrollments in
Flex Credit Plans
26-Sep-05 swjain 115.100 Bug 4613270 - Modified cursor c_cash_abr in procedure
distribute_credits to fetch the acty_base_rt_id corresponding
to the provided(FLXCR) row only
04-Nov-05 swjain 115.101 Bug No 4714939 - Updated the cursor c_cash_rcvd to fetch any
cash row for the pool instead of a particular acty_base_rt_id
25-Dec-05 rbingi 115.102 Bug No 4964766 - Modified cursor c_rlovr_chc to
rollover to the plan within the flex program
Modified c_oipl_enrolment to check pen's pgm_id also
06-Feb-05 rbingi 115.103 Contd: 4964766 - pgm_id condition added in
cursor c_plan_enrolment also.
13-Mar-05 rbingi 115.104 Contd: 4964766 - passing pen_id null to elinf when
created in prev LE and coverage starting in future.
06-Apr-06 kmahendr 115.105 Bug#5136668 - modified cursor c_prv in compute_rollover
27-Apr-06 rbingi 115.106 Bug 5185351: deleting ledger entries for suspended
enrollements in cleanup_invalid_ledger_entries
06-Jun-06 rbingi 115.107 Bug 5257226: Updating rate even when created in past pil
in case of updated ledger.
22-Aug-06 abparekh 115.108 Bug 5447507 : Fixed distribute_credits to query FLXCR ABR
from EPE, if not found through BPL
04-Sep-06 abparekh 115.109 Bug 5344961 : Fixed cursor C_BPL in procedure DELETE_ALL_LEDGERS
06-Sep-06 abparekh 115.110 Bug 5500864 : In procedure cleanup_invalid_ledger_entries delete BPL
in DELETE mode instead of ZAP mode to retain history
of ledger entries
02-NOV-06 ssarkar 115.111 bug 5608160 : calculation of l_new_prtt_rt_val is
modified based entr_val_enrt_flag
07-Nov-06 nhunur 115.112 cleanup_invalid_ledger_entries changed to pass correct dates for
delete BPL
22-Feb-08 rtagarra 115.113 Bug 6834215 : Closed the cursor c_choice.
02-Jul-08 sallumwa 115.114 Bug 7118730 : Re-calculate the ledger if used amt is more than
provided amount.
*/
--------------------------------------------------------------------------------
g_package varchar2(80):='ben_provider_pools';
select
bpp.pgm_id,
ptip.pl_typ_id,
nvl(plip.pl_id, plip2.pl_id) pl_id,
oipl.opt_id
from ben_bnft_prvdr_pool_f bpp, ben_ptip_f ptip, ben_plip_f plip,
ben_oipl_f oipl, ben_oiplip_f oiplip, ben_plip_f plip2
where p_effective_date between
bpp.effective_start_date and
bpp.effective_end_date and
bpp.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
bpp.business_group_id=p_business_group_id
and bpp.ptip_id = ptip.ptip_id(+)
and bpp.plip_id = plip.plip_id(+)
and bpp.oiplip_id = oiplip.oiplip_id(+)
and oiplip.oipl_id = oipl.oipl_id(+)
and oiplip.plip_id = plip2.plip_id(+)
and p_effective_date between
ptip.effective_start_date and
ptip.effective_end_date
and p_effective_date between
plip.effective_start_date and
plip.effective_end_date
and p_effective_date between
oiplip.effective_start_date and
oiplip.effective_end_date
and p_effective_date between
oipl.effective_start_date and
oipl.effective_end_date
and p_effective_date between
plip2.effective_start_date and
plip2.effective_end_date
;
select asg.assignment_id,
asg.organization_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
p_effective_date between
asg.effective_start_date and asg.effective_end_date
;
l_update boolean := FALSE;
l_update_override boolean := FALSE;
l_update_change_insert boolean := FALSE;
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update_override or l_update_change_insert then
p_mode := 'UPDATE_OVERRIDE';
elsif l_update then
p_mode := 'UPDATE';
select epe.bnft_prvdr_pool_id,
epe.elig_per_elctbl_chc_id,
epe.prtt_enrt_rslt_id,
epe.pgm_id,
epe.ptip_id,
epe.plip_id,
epe.pl_id,
epe.oipl_id,
epe.oiplip_id,
epe.cmbn_plip_id,
epe.cmbn_ptip_id,
epe.cmbn_ptip_opt_id,
epe.business_group_id,
epe.per_in_ler_id
from ben_elig_per_elctbl_chc epe1,
ben_elig_per_elctbl_chc epe
where epe1.elig_per_elctbl_chc_id=p_elig_per_elctbl_chc_id and
epe1.business_group_id=p_business_group_id and
epe1.pgm_id = epe.pgm_id and -- start fix 2689926
/* Removed where clause as part of 2917128
*/
epe1.per_in_ler_id = epe.per_in_ler_id and
epe.bnft_prvdr_pool_id is not null and
epe.business_group_id=p_business_group_id
order by
epe.pgm_id,
epe.ptip_id,
epe.plip_id,
epe.pl_id,
epe.oipl_id,
epe.oiplip_id,
epe.cmbn_plip_id,
epe.cmbn_ptip_id,
epe.cmbn_ptip_opt_id ;
select 'x'
from ben_prtt_enrt_rslt_f per
where per.person_id=p_person_id and
per.business_group_id=p_epe_rec.business_group_id and
p_effective_date between
per.effective_start_date and per.effective_end_date and
(enrt_cvg_thru_dt is null or
enrt_cvg_thru_dt=hr_api.g_eot) and
per.effective_end_date = hr_api.g_eot and
per.pgm_id=p_epe_rec.pgm_id and
per.prtt_enrt_rslt_id<>p_old_result_id and
per.prtt_enrt_rslt_stat_cd is null and
-- Bug 4538041
per.COMP_LVL_CD <> 'PLANFC' and
-- End Bug 4538041
per.sspndd_flag='N';
select 'x'
from ben_prtt_enrt_rslt_f per,
ben_pl_f pl,
ben_ptip_f pt
where per.person_id=p_person_id and
per.business_group_id=p_epe_rec.business_group_id and
p_effective_date between
per.effective_start_date and per.effective_end_date and
(enrt_cvg_thru_dt is null or
enrt_cvg_thru_dt=hr_api.g_eot) and
per.effective_end_date = hr_api.g_eot and
per.pl_id=pl.pl_id and
pl.business_group_id=p_epe_rec.business_group_id and
per.prtt_enrt_rslt_id<>p_old_result_id and
per.sspndd_flag='N' and
per.prtt_enrt_rslt_stat_cd is null and
pl.pl_typ_id=pt.pl_typ_id and
pt.business_group_id=p_epe_rec.business_group_id and
pt.pgm_id=p_epe_rec.pgm_id and
p_epe_rec.ptip_id=pt.ptip_id;
select 'x'
from ben_prtt_enrt_rslt_f per,
ben_pl_f pl,
ben_ptip_f pt
where per.person_id=p_person_id and
per.business_group_id=p_epe_rec.business_group_id and
p_effective_date between
per.effective_start_date and per.effective_end_date and
(enrt_cvg_thru_dt is null or
enrt_cvg_thru_dt=hr_api.g_eot) and
per.effective_end_date = hr_api.g_eot and
per.pl_id=pl.pl_id and
pl.business_group_id=p_epe_rec.business_group_id and
per.prtt_enrt_rslt_id<>p_old_result_id and
per.sspndd_flag='N' and
per.prtt_enrt_rslt_stat_cd is null and
pl.pl_typ_id=pt.pl_typ_id and
pt.business_group_id=p_epe_rec.business_group_id and
pt.pgm_id=p_epe_rec.pgm_id and
p_epe_rec.cmbn_ptip_id=pt.cmbn_ptip_id;
select 'x'
from ben_prtt_enrt_rslt_f per,
ben_pl_f pl,
ben_plip_f cpp
where per.person_id=p_person_id and
per.business_group_id=p_epe_rec.business_group_id and
p_effective_date between
per.effective_start_date and per.effective_end_date and
(enrt_cvg_thru_dt is null or
enrt_cvg_thru_dt=hr_api.g_eot) and
per.effective_end_date = hr_api.g_eot and
per.pl_id=pl.pl_id and
pl.business_group_id=p_epe_rec.business_group_id and
per.prtt_enrt_rslt_id<>p_old_result_id and
per.sspndd_flag='N' and
per.prtt_enrt_rslt_stat_cd is null and
cpp.pl_id=pl.pl_id and
cpp.business_group_id=p_epe_rec.business_group_id and
cpp.pgm_id=p_epe_rec.pgm_id and
p_epe_rec.cmbn_plip_id=cpp.cmbn_plip_id;
select 'x'
from ben_prtt_enrt_rslt_f per,
ben_pl_f pl,
ben_ptip_f pt,
ben_oipl_f oipl,
ben_opt_f opt
where per.person_id=p_person_id and
per.business_group_id=p_epe_rec.business_group_id and
p_effective_date between
per.effective_start_date and per.effective_end_date and
(enrt_cvg_thru_dt is null or
enrt_cvg_thru_dt=hr_api.g_eot) and
per.pl_id=pl.pl_id and
pl.business_group_id=p_epe_rec.business_group_id and
per.prtt_enrt_rslt_id<>p_old_result_id and
per.sspndd_flag='N' and
per.prtt_enrt_rslt_stat_cd is null and
pl.pl_typ_id=pt.pl_typ_id and
pt.business_group_id=p_epe_rec.business_group_id and
pt.pgm_id=p_epe_rec.pgm_id and
p_epe_rec.cmbn_ptip_opt_id=opt.cmbn_ptip_opt_id and
p_effective_date between
opt.effective_start_date and opt.effective_end_date and
opt.business_group_id=p_epe_rec.business_group_id and
oipl.opt_id=opt.opt_id and
oipl.oipl_id=per.oipl_id and
p_effective_date between
oipl.effective_start_date and oipl.effective_end_date and
oipl.business_group_id=p_epe_rec.business_group_id
;
select 'x'
from ben_prtt_enrt_rslt_f per,
ben_pl_f pl,
ben_optip_f otp,
ben_oipl_f oipl
where per.person_id=p_person_id and
per.business_group_id=p_epe_rec.business_group_id and
p_effective_date between
per.effective_start_date and per.effective_end_date and
(enrt_cvg_thru_dt is null or
enrt_cvg_thru_dt=hr_api.g_eot) and
per.effective_end_date = hr_api.g_eot and
per.pl_id=pl.pl_id and
pl.business_group_id=p_epe_rec.business_group_id and
per.prtt_enrt_rslt_id<>p_old_result_id and
per.sspndd_flag='N' and
per.prtt_enrt_rslt_stat_cd is null and
pl.pl_typ_id=otp.pl_typ_id and
otp.business_group_id=p_epe_rec.business_group_id and
otp.pgm_id=p_epe_rec.pgm_id and
p_epe_rec.cmbn_ptip_opt_id=otp.cmbn_ptip_opt_id and
p_effective_date between
otp.effective_start_date and otp.effective_end_date and
oipl.opt_id=otp.opt_id and
oipl.oipl_id=per.oipl_id and
p_effective_date between
oipl.effective_start_date and oipl.effective_end_date and
oipl.business_group_id=p_epe_rec.business_group_id
;
select 'x'
from ben_prtt_enrt_rslt_f per
where per.person_id=p_person_id and
per.business_group_id=p_epe_rec.business_group_id and
per.prtt_enrt_rslt_id<>p_old_result_id and
per.sspndd_flag='N' and
per.prtt_enrt_rslt_stat_cd is null and
p_effective_date between
per.effective_start_date and per.effective_end_date and
(enrt_cvg_thru_dt is null or
enrt_cvg_thru_dt=hr_api.g_eot) and
per.effective_end_date = hr_api.g_eot and
p_epe_rec.pl_id=per.pl_id and
p_epe_rec.pgm_id = per.pgm_id; -- Added : 4964766
select 'x'
from ben_prtt_enrt_rslt_f per
where per.person_id=p_person_id and
per.business_group_id=p_epe_rec.business_group_id and
per.prtt_enrt_rslt_id<>p_old_result_id and
per.sspndd_flag='N' and
per.prtt_enrt_rslt_stat_cd is null and
p_effective_date between
per.effective_start_date and per.effective_end_date and
(enrt_cvg_thru_dt is null or
enrt_cvg_thru_dt=hr_api.g_eot) and
per.effective_end_date = hr_api.g_eot and
p_epe_rec.oipl_id=per.oipl_id and
p_epe_rec.pgm_id = per.pgm_id; -- Added : 4964766
select 'x'
from ben_prtt_enrt_rslt_f per,ben_oiplip_f oiplip,ben_plip_f cpp
where per.person_id=p_person_id and
per.business_group_id=p_epe_rec.business_group_id and
per.prtt_enrt_rslt_id<>p_old_result_id and
per.sspndd_flag='N' and
per.prtt_enrt_rslt_stat_cd is null and
p_effective_date between
per.effective_start_date and per.effective_end_date and
(enrt_cvg_thru_dt is null or
enrt_cvg_thru_dt=hr_api.g_eot) and
per.effective_end_date = hr_api.g_eot and
cpp.pgm_id = p_epe_rec.pgm_id and
per.pl_id = cpp.pl_id and
cpp.plip_id = oiplip.plip_id and
per.oipl_id = oiplip.oipl_id and
p_epe_rec.oiplip_id=oiplip.oiplip_id and
p_effective_date between
cpp.effective_start_date and cpp.effective_end_date and
cpp.business_group_id=p_epe_rec.business_group_id and
p_effective_date between
oiplip.effective_start_date and oiplip.effective_end_date and
oiplip.business_group_id=p_epe_rec.business_group_id;
select bpp.pgm_id
from ben_bnft_prvdr_pool_f bpp
where bpp.bnft_prvdr_pool_id = p_bnft_prvdr_pool_id
and p_effective_date between bpp.effective_start_date
and bpp.effective_end_date;
select
decode(enb.enrt_bnft_id,null,
ecr2.enrt_rt_id,ecr1.enrt_rt_id) enrt_rt_id,
decode(enb.enrt_bnft_id,null,
ecr2.acty_base_rt_id,ecr1.acty_base_rt_id) acty_base_rt_id,
decode(enb.enrt_bnft_id,null,
nvl(ecr2.dflt_val,ecr2.val),nvl(ecr1.dflt_val,ecr1.val)) val
from ben_enrt_rt ecr1,
ben_enrt_rt ecr2,
ben_enrt_bnft enb
where ((ecr1.elig_per_elctbl_chc_id=p_epe_rec.elig_per_elctbl_chc_id
and (ecr1.enrt_bnft_id = enb.enrt_bnft_id
or ecr1.enrt_bnft_id is null)
and ecr2.enrt_rt_id = ecr1.enrt_rt_id)
or
(ecr2.enrt_bnft_id = enb.enrt_bnft_id and
ecr2.enrt_rt_id = ecr1.enrt_rt_id and
enb.elig_per_elctbl_chc_id = p_epe_rec.elig_per_elctbl_chc_id))
and
(ecr1.business_group_id=p_epe_rec.business_group_id or
ecr2.business_group_id=p_epe_rec.business_group_id)
and
--(ecr1.decr_bnft_prvdr_pool_id is null or
--ecr2.decr_bnft_prvdr_pool_id is null)
(ecr1.rt_usg_cd = 'FLXCR' or
ecr2.rt_usg_cd = 'FLXCR')
;
select ecr.enrt_rt_id,
ecr.acty_base_rt_id,
nvl(ecr.dflt_val, ecr.val) val
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = c_epe_id
and ecr.rt_usg_cd = 'FLXCR';
select bpl.bnft_prvdd_ldgr_id,
bpl.prvdd_val,
bpl.object_version_number,
bpl.effective_start_date
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.bnft_prvdr_pool_id=p_epe_rec.bnft_prvdr_pool_id
and bpl.business_group_id=p_epe_rec.business_group_id
and bpl.acty_base_rt_id = l_acty_base_rt_id
and bpl.prtt_enrt_rslt_id=g_credit_pool_result_id
and bpl.prvdd_val is not null
and p_effective_date between
bpl.effective_start_date and bpl.effective_end_date
and pil.per_in_ler_id=bpl.per_in_ler_id
and pil.business_group_id=bpl.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
l_datetrack_mode:=hr_api.g_update;
ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_prtt_ro_of_unusd_amt_flag => 'N'
,p_frftd_val => null
,p_prvdd_val => l_val
,p_used_val => null
,p_bnft_prvdr_pool_id => p_epe_rec.bnft_prvdr_pool_id
,p_acty_base_rt_id => l_acty_base_rt_id
,p_per_in_ler_id => p_epe_rec.per_in_ler_id
,p_prtt_enrt_rslt_id => g_credit_pool_result_id
,p_business_group_id => p_epe_rec.business_group_id
,p_object_version_number => l_object_version_number
,p_cash_recd_val => null
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
);
hr_utility.set_location('UPDATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),80);
select pgm_id
from ben_elig_per_elctbl_chc epe
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
select bpl.bnft_prvdd_ldgr_id,
bpl.per_in_ler_id,
bpl.used_val,
bpl.object_version_number,
bpl.effective_start_date
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.bnft_prvdr_pool_id=p_enrt_rt_rec.decr_bnft_prvdr_pool_id
and bpl.business_group_id=p_epe_rec.business_group_id
and bpl.acty_base_rt_id=p_enrt_rt_rec.acty_base_rt_id
and bpl.prtt_enrt_rslt_id=g_credit_pool_result_id
and bpl.used_val is not null
and p_effective_date between
bpl.effective_start_date and bpl.effective_end_date
and pil.per_in_ler_id=bpl.per_in_ler_id
and pil.business_group_id=bpl.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') ;
select rt_val
from ben_prtt_rt_val
where prtt_rt_val_id=p_enrt_rt_rec.prtt_rt_val_id;
l_datetrack_mode:=hr_api.g_update;
ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
p_bnft_prvdd_ldgr_id => p_bnft_prvdd_ldgr_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
--
-- Bug 2199238 rollover plan not displayed because of this
-- ,p_prtt_ro_of_unusd_amt_flag => 'N'
,p_frftd_val => null
,p_prvdd_val => null
,p_used_val => l_val
,p_bnft_prvdr_pool_id => p_enrt_rt_rec.decr_bnft_prvdr_pool_id
,p_acty_base_rt_id => p_enrt_rt_rec.acty_base_rt_id
,p_per_in_ler_id => p_epe_rec.per_in_ler_id
,p_prtt_enrt_rslt_id => g_credit_pool_result_id
,p_business_group_id => p_epe_rec.business_group_id
,p_object_version_number => l_object_version_number
,p_cash_recd_val => null
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
);
hr_utility.set_location('UPDATED LEDGER ID='||to_char(p_bnft_prvdd_ldgr_id),55);
l_delete_bpl boolean default true;
select distinct
epe.bnft_prvdr_pool_id,
epe.elig_per_elctbl_chc_id,
epe.prtt_enrt_rslt_id,
epe.pgm_id,
epe.ptip_id,
epe.plip_id,
epe.pl_id,
epe.oipl_id,
epe.cmbn_ptip_id,
epe.cmbn_plip_id,
epe.cmbn_ptip_opt_id,
epe.business_group_id,
pil.per_in_ler_id,
bpl.bnft_prvdd_ldgr_id,
bpl.object_version_number,
decode(enb.enrt_bnft_id, null,
ecr2.rt_usg_cd,ecr1.rt_usg_cd) rt_usg_cd
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil,
ben_enrt_rt ecr1,
ben_enrt_rt ecr2,
ben_enrt_bnft enb,
ben_bnft_prvdd_ldgr_f bpl,
ben_elig_per_elctbl_chc epe_flex,
ben_per_in_ler pil_flex
where pil.person_id=p_person_id and
pil.business_group_id=p_business_group_id and
pil.per_in_ler_stat_cd='STRTD' and
pil.per_in_ler_id=epe.per_in_ler_id and
bpl.per_in_ler_id = pil.per_in_ler_id and
epe.business_group_id=p_business_group_id and
--epe.bnft_prvdr_pool_id is not null and
epe.elig_per_elctbl_chc_id=ecr2.elig_per_elctbl_chc_id(+) and
(ecr1.acty_base_rt_id=bpl.acty_base_rt_id
or ecr2.acty_base_rt_id=bpl.acty_base_rt_id) and
epe.elig_per_elctbl_chc_id=enb.elig_per_elctbl_chc_id(+) and
enb.enrt_bnft_id = ecr1.enrt_bnft_id(+) and
bpl.business_group_id=p_business_group_id and
p_effective_date between
bpl.effective_start_date and bpl.effective_end_date
--and bpl.prvdd_val is null and
and bpl.prtt_enrt_rslt_id=epe_flex.prtt_enrt_rslt_id and
epe_flex.business_group_id=p_business_group_id and
epe_flex.per_in_ler_id=pil_flex.per_in_ler_id and
pil_flex.business_group_id=p_business_group_id and
pil_flex.person_id=p_person_id --and
;
select pen.prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id <> p_prtt_enrt_rslt_id
and pen.comp_lvl_cd <> 'PLANFC'
and pen.person_id = p_person_id
and p_effective_date between
pen.effective_start_date and pen.effective_end_date;
select 'x'
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.bnft_prvdd_ldgr_id = l_ldgr_id
and bpl.per_in_ler_id = l_per_in_ler_id
-- UK change : Bug 1634870
and bpl.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and p_effective_date between
bpl.effective_start_date and bpl.effective_end_date;
select enrt_cvg_strt_dt,
enrt_cvg_thru_dt,
prtt_enrt_rslt_stat_cd,
sspndd_flag
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between effective_start_date
and effective_end_date;
select distinct acty_base_rt_id
from ben_prtt_rt_val
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
select prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f
where person_id = p_person_id
and comp_lvl_cd = 'PLANFC'
and prtt_enrt_rslt_stat_cd is null
-- added effective_end_date line Bug# 2254326
and effective_end_date = hr_api.g_eot
and p_effective_date between enrt_cvg_strt_dt
and enrt_cvg_thru_dt;
select bpl.bnft_prvdd_ldgr_id,
bpl.prtt_ro_of_unusd_amt_flag,
bpl.object_version_number,
bpl.effective_start_date
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.acty_base_rt_id = l_acty_base_rt_id
and bpl.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
-- and bpl.effective_end_date = hr_api.g_eot
and p_effective_date between bpl.effective_start_date
and bpl.effective_end_date
and bpl.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select epe.bnft_prvdr_pool_id,
epe.elig_per_elctbl_chc_id,
epe.prtt_enrt_rslt_id,
epe.pgm_id,
epe.ptip_id,
epe.plip_id,
epe.pl_id,
epe.oipl_id,
epe.oiplip_id,
epe.cmbn_plip_id,
epe.cmbn_ptip_id,
epe.cmbn_ptip_opt_id,
epe.business_group_id,
epe.per_in_ler_id
from ben_elig_per_elctbl_chc epe1,
ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where epe1.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id and
epe1.business_group_id=p_business_group_id and
epe1.pgm_id = epe.pgm_id and
epe1.per_in_ler_id = epe.per_in_ler_id and
epe.bnft_prvdr_pool_id is not null and
epe.business_group_id=p_business_group_id and
epe1.per_in_ler_id = pil.per_in_ler_id and
pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select bpl.bnft_prvdd_ldgr_id,
bpl.object_version_number,
effective_start_date
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.bnft_prvdr_pool_id = l_epe.bnft_prvdr_pool_id
and bpl.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
and bpl.acty_base_rt_id = p_acty_base_rt_id
--and bpl.effective_end_date = hr_api.g_eot
and p_effective_date between bpl.effective_start_date
and bpl.effective_end_date
and bpl.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.acty_base_rt_id,
nvl(ecr.dflt_val, ecr.val) val
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = c_epe_id
and ecr.rt_usg_cd = 'FLXCR';
select null
from ben_elig_per_elctbl_chc epe
,ben_prtt_enrt_rslt_f pen
,ben_enrt_rt ecr
,ben_enrt_bnft enb
where epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and epe.business_group_id = pen.business_group_id
and pen.sspndd_flag = 'N'
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot
and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
and enb.enrt_bnft_id = ecr.enrt_bnft_id
and ecr.acty_base_rt_id = l_acty_base_rt_id;
ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_zap,
p_business_group_id => p_business_group_id
);
l_delete_bpl := false;
l_delete_bpl := true;
if l_delete_bpl then
--
-- Bug 5500864
-- We dont want to purge the BPL entries. This prevents the reinstatement in case
-- the life event that deletes these entries is voided subsequently
-- Following call is delete of USED_VAL BPL entries.
--
if p_effective_date = l_bpl_esd
then
l_datetrack_mode := hr_api.g_zap;
l_datetrack_mode := hr_api.g_delete;
ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_object_version_number,
p_effective_date => l_effective_date,
p_datetrack_mode => l_datetrack_mode,
p_business_group_id => p_business_group_id
);
l_datetrack_mode := hr_api.g_delete;
ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_object_version_number,
p_effective_date => l_effective_date,
p_datetrack_mode => l_datetrack_mode,
p_business_group_id => p_business_group_id
);
select distinct
epe.bnft_prvdr_pool_id,
epe.elig_per_elctbl_chc_id,
epe.prtt_enrt_rslt_id,
epe.pgm_id,
epe.ptip_id,
epe.plip_id,
epe.pl_id,
epe.oipl_id,
epe.cmbn_ptip_id,
epe.cmbn_ptip_opt_id,
epe.business_group_id,
bpl.bnft_prvdd_ldgr_id,
bpl.object_version_number,
decode(enb.enrt_bnft_id,null,
ecr2.rt_usg_cd,ecr1.rt_usg_cd) rt_usg_cd
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil,
ben_enrt_rt ecr1,
ben_enrt_rt ecr2,
ben_enrt_bnft enb,
ben_bnft_prvdd_ldgr_f bpl,
ben_elig_per_elctbl_chc epe_flex,
ben_per_in_ler pil_flex,
ben_per_in_ler pil_flex1
where pil.per_in_ler_id = p_per_in_ler_id and
pil.business_group_id=p_business_group_id and
epe.bnft_prvdr_pool_id is not null and
pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
pil.per_in_ler_id=epe.per_in_ler_id and
epe.business_group_id=p_business_group_id and
epe.elig_per_elctbl_chc_id=ecr2.elig_per_elctbl_chc_id(+) and
(ecr1.acty_base_rt_id=bpl.acty_base_rt_id
or ecr2.acty_base_rt_id=bpl.acty_base_rt_id) and
epe.elig_per_elctbl_chc_id=enb.elig_per_elctbl_chc_id(+) and
enb.enrt_bnft_id = ecr1.enrt_bnft_id(+) and
bpl.business_group_id=p_business_group_id and
p_effective_date between
bpl.effective_start_date and bpl.effective_end_date and
-- bpl.cash_recd_val is null and
bpl.prtt_enrt_rslt_id=epe_flex.prtt_enrt_rslt_id and
epe_flex.business_group_id=p_business_group_id and
epe_flex.per_in_ler_id=pil_flex.per_in_ler_id and
pil_flex.business_group_id=p_business_group_id and
-- Bug 1634870
pil_flex1.per_in_ler_id=bpl.per_in_ler_id and
pil_flex1.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
pil_flex1.business_group_id=p_business_group_id and
pil_flex.person_id=p_person_id
;
ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_zap,
p_business_group_id => p_business_group_id
);
select elig_per_elctbl_chc_id,
epe.prtt_enrt_rslt_id,
per.object_version_number,
pil.per_in_ler_id,
epe.pgm_id,
epe.pl_id,
per.enrt_cvg_strt_dt,
per.per_in_ler_id chc_pen_id
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil,
ben_prtt_enrt_rslt_f per
where epe.comp_lvl_cd = 'PLANFC' and
epe.per_in_ler_id = p_per_in_ler_id and
per.prtt_enrt_rslt_stat_cd is null and
epe.business_group_id=p_business_group_id and
epe.per_in_ler_id=pil.per_in_ler_id and
pil.business_group_id=p_business_group_id and
pil.person_id=p_person_id and
pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
per.prtt_enrt_rslt_id(+)=epe.prtt_enrt_rslt_id and
(p_pgm_id is null or p_pgm_id = epe.pgm_id) and
p_effective_date between per.effective_start_date(+) and
per.effective_end_date(+) ;
select ecr.prtt_rt_val_id,
ecr.enrt_rt_id
from ben_enrt_rt ecr,
ben_acty_base_rt_f abr
where ecr.elig_per_elctbl_chc_id=
l_flex_credit_choice.elig_per_elctbl_chc_id
and ecr.business_group_id=p_business_group_id
and ecr.acty_typ_cd not in ('NCRDSTR','NCRUDED')
and abr.parnt_acty_base_rt_id is null
and ecr.acty_base_rt_id = abr.acty_base_rt_id
and ecr.business_group_id = abr.business_group_id
and p_effective_date between abr.effective_start_date
and abr.effective_end_date;
select ecr.prtt_rt_val_id
,ecr.enrt_rt_id
,ecr.acty_base_rt_id
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id=
l_flex_credit_choice.elig_per_elctbl_chc_id
and ecr.acty_typ_cd = p_acty_typ_cd
and ecr.business_group_id=p_business_group_id;
select ecr.prtt_rt_val_id
,ecr.enrt_rt_id
,ecr.acty_base_rt_id
from ben_enrt_rt ecr
,ben_acty_base_rt_f abr
where ecr.elig_per_elctbl_chc_id=
l_flex_credit_choice.elig_per_elctbl_chc_id
and ecr.acty_base_rt_id = abr.acty_base_rt_id
and abr.parnt_acty_base_rt_id = p_acty_base_rt_id
and ecr.business_group_id=p_business_group_id
and abr.business_group_id = ecr.business_group_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select pgm_typ_cd
from ben_pgm_f
where pgm_id = p_pgm_id
and business_group_id = p_business_group_id
and p_effective_date between
effective_start_date and effective_end_date;
p_datetrack_mode => hr_api.g_insert,
p_suspend_flag => l_suspend_flag,
p_object_version_number => l_flex_credit_choice.object_version_number,
p_prtt_enrt_interim_id => l_prtt_enrt_interim_id,
p_rt_val1 => p_rt_val,
p_enrt_rt_id1 => l_enrt_rt_id,
p_prtt_rt_val_id1 => l_good_prtt_rt_val_id,
p_prtt_rt_val_id2 => l_prtt_rt_val_id,
p_prtt_rt_val_id3 => l_prtt_rt_val_id,
p_prtt_rt_val_id4 => l_prtt_rt_val_id,
p_prtt_rt_val_id5 => l_prtt_rt_val_id,
p_prtt_rt_val_id6 => l_prtt_rt_val_id,
p_prtt_rt_val_id7 => l_prtt_rt_val_id,
p_prtt_rt_val_id8 => l_prtt_rt_val_id,
p_prtt_rt_val_id9 => l_prtt_rt_val_id,
p_prtt_rt_val_id10 => l_prtt_rt_val_id,
p_business_group_id => p_business_group_id,
p_dpnt_actn_warning => l_dpnt_actn_warning,
p_bnf_actn_warning => l_bnf_actn_warning,
p_ctfn_actn_warning => l_ctfn_actn_warning
);
select person_id
from ben_prtt_enrt_rslt_f res
where res.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id and
--res.prtt_enrt_rslt_stat_cd is null and
p_effective_date between
res.effective_start_date and res.effective_end_date and
p_business_group_id=res.business_group_id ;
select bpl.bnft_prvdd_ldgr_id,
bpl.frftd_val,
bpl.object_version_number,
bpl.effective_start_date
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id
and bpl.business_group_id=p_business_group_id
and bpl.acty_base_rt_id = p_acty_base_rt_id
and bpl.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
and bpl.frftd_val is not null
and p_effective_date between
bpl.effective_start_date and bpl.effective_end_date
and pil.per_in_ler_id=bpl.per_in_ler_id
and pil.business_group_id=bpl.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select bpl.bnft_prvdd_ldgr_id,
bpl.cash_recd_val,
bpl.object_version_number,
bpl.effective_start_date
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id
and bpl.business_group_id=p_business_group_id
-- and bpl.acty_base_rt_id = p_acty_base_rt_id /* Bug No 4714939 */
and bpl.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
and bpl.cash_recd_val is not null
and p_effective_date between
bpl.effective_start_date and bpl.effective_end_date
and pil.per_in_ler_id=bpl.per_in_ler_id
and pil.business_group_id=bpl.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') ;
l_datetrack_mode:=hr_api.g_update;
ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_prtt_ro_of_unusd_amt_flag => 'N'
,p_frftd_val => l_balance
,p_prvdd_val => null
,p_used_val => null
,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
,p_acty_base_rt_id => p_acty_base_rt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_cash_recd_val => null
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
);
hr_utility.set_location('UPDATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),30);
ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_prtt_ro_of_unusd_amt_flag => 'N'
,p_frftd_val => null -- l_balance
,p_prvdd_val => null
,p_used_val => null
,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
,p_acty_base_rt_id => p_acty_base_rt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_cash_recd_val => l_balance_for_cr
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
);
select bpl.acty_base_rt_id
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil,
ben_acty_base_rt_f abr -- Bug 4613270
where bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id
and bpl.business_group_id=p_business_group_id
and bpl.prtt_enrt_rslt_id=g_credit_pool_result_id
and p_effective_date between
bpl.effective_start_date and bpl.effective_end_date
and pil.per_in_ler_id=bpl.per_in_ler_id
and pil.business_group_id=bpl.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
/* Bug 4613270 */
and bpl.acty_base_rt_id = abr.acty_base_rt_id
and abr.rt_usg_cd = 'FLXCR' and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
SELECT ecr.acty_base_rt_id
FROM ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe,
ben_enrt_rt ecr
WHERE pil.person_id = p_person_id
AND pil.business_group_id = p_business_group_id
AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
AND epe.per_in_ler_id = pil.per_in_ler_id
AND epe.business_group_id = p_business_group_id
AND epe.bnft_prvdr_pool_id = p_bnft_prvdr_pool_id
AND ecr.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
AND ecr.rt_usg_cd = 'FLXCR'
AND ecr.business_group_id = p_business_group_id
ORDER BY pil.per_in_ler_stat_Cd desc;
select bpl.bnft_prvdd_ldgr_id,
bpl.cash_recd_val,
bpl.object_version_number,
bpl.effective_start_date
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.bnft_prvdr_pool_id = c_bnft_prvdr_pool_id
and bpl.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and bpl.cash_recd_val is not null
and c_effective_date
between bpl.effective_start_date and bpl.effective_end_date
and pil.per_in_ler_id = bpl.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select bpp.pgm_id
from ben_bnft_prvdr_pool_f bpp
where bpp.bnft_prvdr_pool_id = p_bnft_prvdr_pool_id
and p_effective_date between bpp.effective_start_date
and bpp.effective_end_date;
hr_utility.set_location('insert cash row', 290);
l_datetrack_mode:=hr_api.g_update;
ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_prtt_ro_of_unusd_amt_flag => 'N'
,p_frftd_val => null
,p_prvdd_val => null
,p_used_val => null
,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
,p_acty_base_rt_id => l_acty_base_rt_id
,p_per_in_ler_id => nvl(p_per_in_ler_id, hr_api.g_number) /* Bug 4251187 */
,p_prtt_enrt_rslt_id => g_credit_pool_result_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_cash_recd_val => l_cash_val
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
);
hr_utility.set_location('UPDATED LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),350);
select
prr.mn_rlovr_pct_num,
prr.mn_rlovr_val,
prr.mx_rchd_dflt_ordr_num,
prr.mx_rlovr_pct_num,
prr.mx_rlovr_val,
prr.pct_rlovr_incrmt_num,
prr.pct_rndg_cd,
prr.pct_rndg_rl,
prr.rlovr_val_incrmt_num,
prr.rlovr_val_rl,
prr.val_rndg_cd,
prr.val_rndg_rl,
prr.acty_base_rt_id,
epe.elig_per_elctbl_chc_id,
ecr.enrt_rt_id,
ecr.mn_elcn_val,
ecr.mx_elcn_val,
prv.rt_val,
prv.prtt_rt_val_id,
rslt.prtt_enrt_rslt_id,
prr.prtt_elig_rlovr_rl,
asg.assignment_id,
asg.organization_id,
loc.region_2,
oipl.opt_id,
epe.pl_id,
epe.pgm_id,
pil.ler_id,
epe.pl_typ_id,
epe.per_in_ler_id
from
ben_bnft_pool_rlovr_rqmt_f prr,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe,
ben_enrt_rt ecr,
ben_prtt_rt_val prv,
ben_prtt_enrt_rslt_f rslt,
per_all_assignments_f asg,
hr_locations_all loc,
ben_oipl_f oipl
where
prr.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
prr.business_group_id=p_business_group_id and
p_effective_date between
prr.effective_start_date and prr.effective_end_date and
pil.per_in_ler_id=p_per_in_ler_id and
pil.business_group_id=p_business_group_id and
pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
epe.per_in_ler_id=pil.per_in_ler_id and
epe.elctbl_flag='Y' and
epe.business_group_id=p_business_group_id and
ecr.elig_per_elctbl_chc_id=epe.elig_per_elctbl_chc_id and
-- Added for bug 2185478
(p_acty_base_rt_id is null or
prr.acty_base_rt_id = p_acty_base_rt_id) and
ecr.acty_base_rt_id=prr.acty_base_rt_id and
ecr.business_group_id=p_business_group_id and
prv.prtt_rt_val_id(+)=ecr.prtt_rt_val_id and
prv.business_group_id(+)=p_business_group_id and
rslt.prtt_enrt_rslt_id(+)=epe.prtt_enrt_rslt_id and
p_effective_date between
rslt.effective_start_date(+) and rslt.effective_end_date(+) and
rslt.business_group_id(+)=p_business_group_id and
asg.person_id=pil.person_id and
asg.assignment_type <> 'C'and
asg.primary_flag='Y' and
asg.location_id = loc.location_id(+) and
p_effective_date between
asg.effective_start_date and asg.effective_end_date and
oipl.oipl_id(+)=epe.oipl_id and
p_effective_date between
oipl.effective_start_date(+) and oipl.effective_end_date(+) and
oipl.business_group_id(+)=p_business_group_id
--Bug 2185478 added the union to handle the cases of enrt_bnft
union
select
prr.mn_rlovr_pct_num,
prr.mn_rlovr_val,
prr.mx_rchd_dflt_ordr_num,
prr.mx_rlovr_pct_num,
prr.mx_rlovr_val,
prr.pct_rlovr_incrmt_num,
prr.pct_rndg_cd,
prr.pct_rndg_rl,
prr.rlovr_val_incrmt_num,
prr.rlovr_val_rl,
prr.val_rndg_cd,
prr.val_rndg_rl,
prr.acty_base_rt_id,
epe.elig_per_elctbl_chc_id,
ecr.enrt_rt_id,
ecr.mn_elcn_val,
ecr.mx_elcn_val,
prv.rt_val,
prv.prtt_rt_val_id,
rslt.prtt_enrt_rslt_id,
prr.prtt_elig_rlovr_rl,
asg.assignment_id,
asg.organization_id,
loc.region_2,
oipl.opt_id,
epe.pl_id,
epe.pgm_id,
pil.ler_id,
epe.pl_typ_id,
epe.per_in_ler_id
from
ben_bnft_pool_rlovr_rqmt_f prr,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe,
ben_enrt_bnft enb,
ben_enrt_rt ecr,
ben_prtt_rt_val prv,
ben_prtt_enrt_rslt_f rslt,
per_all_assignments_f asg,
hr_locations_all loc,
ben_oipl_f oipl
where
prr.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
prr.business_group_id=p_business_group_id and
p_effective_date between
prr.effective_start_date and prr.effective_end_date and
pil.per_in_ler_id=p_per_in_ler_id and
pil.business_group_id=p_business_group_id and
pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
epe.per_in_ler_id=pil.per_in_ler_id and
epe.elctbl_flag='Y' and
epe.business_group_id=p_business_group_id and
enb.elig_per_elctbl_chc_id=epe.elig_per_elctbl_chc_id and
enb.enrt_bnft_id = ecr.enrt_bnft_id and
-- Added for bug 2185478
(p_acty_base_rt_id is null or
prr.acty_base_rt_id = p_acty_base_rt_id) and
ecr.acty_base_rt_id=prr.acty_base_rt_id and
ecr.business_group_id=p_business_group_id and
prv.prtt_rt_val_id(+)=ecr.prtt_rt_val_id and
prv.business_group_id(+)=p_business_group_id and
rslt.prtt_enrt_rslt_id(+)=epe.prtt_enrt_rslt_id and
p_effective_date between
rslt.effective_start_date(+) and rslt.effective_end_date(+) and
rslt.business_group_id(+)=p_business_group_id and
asg.person_id=pil.person_id and
asg.assignment_type <> 'C'and
asg.primary_flag='Y' and
asg.location_id = loc.location_id(+) and
p_effective_date between
asg.effective_start_date and asg.effective_end_date and
oipl.oipl_id(+)=epe.oipl_id and
p_effective_date between
oipl.effective_start_date(+) and oipl.effective_end_date(+) and
oipl.business_group_id(+)=p_business_group_id
order by mx_rchd_dflt_ordr_num
;
select
nvl(sum(prvdd_val),0),
nvl(sum(decode(prtt_ro_of_unusd_amt_flag,'N',used_val,0)),0),-- non rollovers
nvl(sum(cash_recd_val),0),
nvl(sum(decode(prtt_ro_of_unusd_amt_flag,'Y',used_val,0)),0) -- rollovers
from ben_bnft_prvdd_ldgr_f bpl,
ben_elig_per_elctbl_chc epe_flex,
ben_per_in_ler pil_flex,
ben_per_in_ler pil_flex1
where p_effective_date between
bpl.effective_start_date and bpl.effective_end_date and
bpl.business_group_id=p_business_group_id and
bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
bpl.prtt_enrt_rslt_id=epe_flex.prtt_enrt_rslt_id and
-- exclude the rollover for this abr
(bpl.acty_base_rt_id<>l_acty_base_rt_id or
-- but include the used amounts for this abr
prtt_ro_of_unusd_amt_flag='N') and
epe_flex.business_group_id=p_business_group_id and
epe_flex.per_in_ler_id=pil_flex.per_in_ler_id and
pil_flex.business_group_id=p_business_group_id and
pil_flex.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
pil_flex.per_in_ler_id=p_per_in_ler_id and
-- Bug 1634870
pil_flex1.per_in_ler_id=bpl.per_in_ler_id and
pil_flex1.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
pil_flex1.business_group_id=p_business_group_id
group by bpl.bnft_prvdr_pool_id
;
select
nvl(sum(prvdd_val),0),
nvl(sum(decode(prtt_ro_of_unusd_amt_flag,'N',used_val,0)),0),-- non rollovers
nvl(sum(cash_recd_val),0),
nvl(sum(decode(prtt_ro_of_unusd_amt_flag,'Y',used_val,0)),0) -- rollovers
from ben_bnft_prvdd_ldgr_f bpl,
ben_elig_per_elctbl_chc epe_flex,
ben_per_in_ler pil_flex,
ben_per_in_ler pil_flex1
where p_effective_date between
bpl.effective_start_date and bpl.effective_end_date and
bpl.business_group_id=p_business_group_id and
bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
bpl.prtt_enrt_rslt_id=epe_flex.prtt_enrt_rslt_id and
bpl.acty_base_rt_id=l_acty_base_rt_id and -- this is the difference
-- bpl.prtt_ro_of_unusd_amt_flag='Y' and -- and this is the rollover
epe_flex.business_group_id=p_business_group_id and
epe_flex.per_in_ler_id=pil_flex.per_in_ler_id and
pil_flex.business_group_id=p_business_group_id and
pil_flex.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
pil_flex.per_in_ler_id=p_per_in_ler_id and
-- Bug 1634870
pil_flex1.per_in_ler_id=bpl.per_in_ler_id and
pil_flex1.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
pil_flex1.business_group_id=p_business_group_id
group by bpl.bnft_prvdr_pool_id
;
select
bnft_prvdd_ldgr_id,
bpl.used_val,
bpl.object_version_number,
bpl.effective_start_date
from ben_bnft_prvdd_ldgr_f bpl,
ben_elig_per_elctbl_chc epe_flex,
ben_per_in_ler pil_flex,
ben_per_in_ler pil_flex1
where p_effective_date between
bpl.effective_start_date and bpl.effective_end_date and
bpl.business_group_id=p_business_group_id and
bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
bpl.prtt_enrt_rslt_id=epe_flex.prtt_enrt_rslt_id and
--bpl.used_val<>0 and -----For the Bug 7118730
bpl.prtt_ro_of_unusd_amt_flag='Y' and
epe_flex.business_group_id=p_business_group_id and
epe_flex.per_in_ler_id=pil_flex.per_in_ler_id and
pil_flex.business_group_id=p_business_group_id and
pil_flex.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
-- Bug 1634870
pil_flex1.per_in_ler_id=bpl.per_in_ler_id and
pil_flex1.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
pil_flex1.business_group_id=p_business_group_id and
pil_flex.per_in_ler_id=p_per_in_ler_id ;
ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_prtt_ro_of_unusd_amt_flag => 'Y'
,p_frftd_val => null
,p_prvdd_val => null
,p_used_val => l_balance
,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
,p_acty_base_rt_id => l_acty_base_rt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_prtt_enrt_rslt_id => g_credit_pool_result_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_cash_recd_val => 0
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
);
l_datetrack_mode:=hr_api.g_update;
ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_prtt_ro_of_unusd_amt_flag => 'Y'
,p_frftd_val => null
,p_prvdd_val => null
,p_used_val => l_balance
,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
,p_acty_base_rt_id => l_acty_base_rt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_prtt_enrt_rslt_id => g_credit_pool_result_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_cash_recd_val => 0
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
);
hr_utility.set_location('UPDATED LEDGER (ID='||to_char(l_bnft_prvdd_ldgr_id),130);
select bpp.*
from ben_bnft_prvdr_pool_f bpp
where p_effective_date between
bpp.effective_start_date and
bpp.effective_end_date and
bpp.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
bpp.business_group_id=p_business_group_id
;
select
prv.rt_val,
prv.prtt_rt_val_id
from ben_prtt_rt_val prv,
ben_enrt_rt ecr
where ecr.enrt_rt_id = v_enrt_rt_id and
prv.prtt_rt_val_id=ecr.prtt_rt_val_id and
prv.prtt_rt_val_stat_cd is null and
prv.per_in_ler_id = p_per_in_ler_id and
ecr.business_group_id=p_business_group_id;
select decode(enb.enrt_bnft_id,
null, ecr2.enrt_rt_id,
ecr1.enrt_rt_id) enrt_rt_id,
decode(enb.enrt_bnft_id,
null, ecr2.rt_mlt_cd,
ecr1.rt_mlt_cd) rt_mlt_cd,
decode(enb.enrt_bnft_id,
null, ecr2.entr_val_at_enrt_flag,
ecr1.entr_val_at_enrt_flag) entr_val_at_enrt_flag, --bug 5608160
enb.enrt_bnft_id,
nvl(enb.val, enb.dflt_val) bnft_val,
epe.elig_per_elctbl_chc_id,
pel.acty_ref_perd_cd,
pen.prtt_enrt_rslt_id,
pen.bnft_amt,
pen.object_version_number
from ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_enrt_rt ecr1,
ben_enrt_rt ecr2,
ben_enrt_bnft enb,
ben_prtt_enrt_rslt_f pen,
ben_bnft_prvdr_pool_f bpp -- join to get only current pgm_id - rgajula
where pil.per_in_ler_id=p_per_in_ler_id and
pil.business_group_id=p_business_group_id and
pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
pil.per_in_ler_id=epe.per_in_ler_id and
pil.per_in_ler_id = pel.per_in_ler_id and
pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id and
epe.business_group_id=p_business_group_id and
epe.elig_per_elctbl_chc_id=ecr2.elig_per_elctbl_chc_id(+) and
bpp.bnft_prvdr_pool_id = p_bnft_prvdr_pool_id and --
bpp.business_group_id = p_business_group_id and --
p_effective_date between bpp.effective_start_date and bpp.effective_end_date and --
bpp.pgm_id = epe.pgm_id and --
(p_acty_base_rt_id is null or
ecr1.acty_base_rt_id = p_acty_base_rt_id or
ecr2.acty_base_rt_id = p_acty_base_rt_id) and
pen.prtt_enrt_rslt_id(+)=epe.prtt_enrt_rslt_id and
epe.elig_per_elctbl_chc_id=enb.elig_per_elctbl_chc_id(+) and
enb.enrt_bnft_id = ecr1.enrt_bnft_id(+) and
pen.prtt_enrt_rslt_stat_cd is null and
p_effective_date between
pen.effective_start_date(+) and pen.effective_end_date(+) and
pen.business_group_id(+)=p_business_group_id ;
is select rate_periodization_rl
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = cv_acty_base_rt_id
and p_effective_date between abr.effective_start_date
and abr.effective_end_date ;
l_datetrack_mode:=hr_api.g_insert;
select bpl.bnft_prvdr_pool_id,
nvl(sum(prvdd_val),0) prvdd_total,
nvl(sum(used_val),0) used_total, -- include rollovers
nvl(sum(frftd_val),0) frftd_total,
nvl(sum(cash_recd_val),0) cash_recd_total,
pil_flex.ler_id
from ben_bnft_prvdd_ldgr_f bpl,
ben_elig_per_elctbl_chc epe_flex,
ben_per_in_ler pil_flex,
ben_per_in_ler pil_flex1
where p_effective_date between
bpl.effective_start_date and bpl.effective_end_date and
bpl.business_group_id=p_business_group_id and
bpl.bnft_prvdr_pool_id is not null and
bpl.prtt_enrt_rslt_id=epe_flex.prtt_enrt_rslt_id and
epe_flex.business_group_id=p_business_group_id and
epe_flex.per_in_ler_id=pil_flex.per_in_ler_id and
pil_flex.business_group_id=p_business_group_id and
pil_flex.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
pil_flex.per_in_ler_id=p_per_in_ler_id and
-- Bug 1634870
pil_flex1.per_in_ler_id=bpl.per_in_ler_id and
pil_flex1.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
pil_flex1.business_group_id=p_business_group_id
and epe_flex.pgm_id = p_pgm_id
group by bpl.bnft_prvdr_pool_id, pil_flex.ler_id
;
select bpp.*
from ben_bnft_prvdr_pool_f bpp
where p_effective_date between
bpp.effective_start_date and
bpp.effective_end_date and
bpp.bnft_prvdr_pool_id=l_ledger_totals.bnft_prvdr_pool_id and
bpp.business_group_id=p_business_group_id
;
select ecr.acty_base_rt_id,
epe.elig_per_elctbl_chc_id
from ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe,
ben_enrt_rt ecr
where pil.per_in_ler_id=p_per_in_ler_id and
pil.business_group_id=p_business_group_id and
pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
epe.per_in_ler_id=pil.per_in_ler_id and
epe.business_group_id=p_business_group_id and
epe.bnft_prvdr_pool_id=l_ledger_totals.bnft_prvdr_pool_id and
ecr.elig_per_elctbl_chc_id=epe.elig_per_elctbl_chc_id and
ecr.rt_usg_cd='FLXCR' and
ecr.business_group_id=p_business_group_id;
select abr.acty_base_rt_id
from ben_bnft_prvdd_ldgr_f bpl,
ben_acty_base_rt_f abr
where bpl.bnft_prvdr_pool_id = l_ledger_totals.bnft_prvdr_pool_id and
abr.rt_usg_cd = 'FLXCR' and
bpl.business_group_id = abr.business_group_id and
bpl.business_group_id = p_business_group_id and
bpl.acty_base_rt_id = abr.acty_base_rt_id and
p_effective_date between abr.effective_start_date and
abr.effective_end_date ;
select prv.acty_ref_perd_cd,
ecr.acty_base_rt_id,
prv.rt_strt_dt,
prv.rt_val,
abr.element_type_id
from ben_prtt_rt_val prv,
ben_enrt_rt ecr,
ben_acty_base_rt_f abr,
ben_prtt_enrt_rslt_f res
where prv.prtt_rt_val_id=p_prtt_rt_val_id and
prv.business_group_id=p_business_group_id and
ecr.prtt_rt_val_id=prv.prtt_rt_val_id and
ecr.business_group_id=p_business_group_id and
abr.acty_base_rt_id=ecr.acty_base_rt_id and
abr.business_group_id=p_business_group_id and
p_effective_date between
abr.effective_start_date and abr.effective_end_date and
res.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id and
res.business_group_id=p_business_group_id and
p_effective_date between
res.effective_start_date and res.effective_end_date
;
select bpl.bnft_prvdd_ldgr_id,
bpl.cash_recd_val,
bpl.object_version_number,
bpl.effective_start_date
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.bnft_prvdr_pool_id=l_ledger_totals.bnft_prvdr_pool_id
and bpl.business_group_id=p_business_group_id
and bpl.acty_base_rt_id = l_acty_base_rt_id
and bpl.prtt_enrt_rslt_id=g_credit_pool_result_id
and bpl.cash_recd_val is not null
and p_effective_date between
bpl.effective_start_date and bpl.effective_end_date
and pil.per_in_ler_id=bpl.per_in_ler_id
and pil.business_group_id=bpl.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') ;
select bpr.mx_pct_ttl_crs_cn_roll_num
,bpr.acty_base_rt_id
from ben_bnft_pool_rlovr_rqmt_f bpr
where p_effective_date between
bpr.effective_start_date and
bpr.effective_end_date
and bpr.bnft_prvdr_pool_id=l_ledger_totals.bnft_prvdr_pool_id
and bpr.business_group_id=p_business_group_id
and bpr.mx_pct_ttl_crs_cn_roll_num is not null
;
select bpl.used_val
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where p_effective_date between
bpl.effective_start_date and
bpl.effective_end_date
and bpl.acty_base_rt_id = p_acty_base_rt_id
-- UK change : Bug 1634870
and bpl.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and bpl.bnft_prvdr_pool_id=l_ledger_totals.bnft_prvdr_pool_id
and bpl.business_group_id=p_business_group_id
and bpl.prtt_ro_of_unusd_amt_flag = 'Y';
select 'x' from
ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id = p_per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_mthd_cd = 'D'
and p_effective_date between
pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.effective_end_date = hr_api.g_eot ;
procedure update_rate(p_prtt_rt_val_id in out nocopy number,
p_val in number,
p_prtt_enrt_rslt_id in number,
p_ended_per_in_ler_id in number,
p_effective_date in date,
p_business_group_id in number) is
--
cursor c_prv is
select prv.*
from ben_prtt_rt_val prv
where prv.prtt_rt_val_id = p_prtt_rt_val_id
and prv.prtt_rt_val_stat_cd is null;
select pen.pgm_id,
pen.pl_id,
pen.oipl_id,
pen.person_id,
pen.ler_id
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pen.effective_start_date and pen.effective_end_date;
select abr.input_value_id,
abr.element_type_id
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = l_prv_rec.acty_base_rt_id
and abr.business_group_id = p_business_group_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select nvl(ecr1.enrt_rt_id, ecr2.enrt_rt_id) enrt_rt_id
from ben_enrt_rt ecr1,
ben_enrt_rt ecr2,
ben_elig_per_elctbl_chc epe,
ben_enrt_bnft enb
where epe.per_in_ler_id = p_ended_per_in_ler_id
and ecr1.prtt_rt_val_id(+) = p_prtt_rt_val_id
and ecr2.prtt_rt_val_id(+) = p_prtt_rt_val_id
and epe.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id (+)
and enb.enrt_bnft_id = ecr2.enrt_bnft_id(+);
SELECT leer.lee_rsn_id
FROM ben_lee_rsn_f leer,
ben_popl_enrt_typ_cycl_f petc
WHERE leer.ler_id = c_ler_id
AND leer.business_group_id = p_business_group_id
AND p_effective_date BETWEEN leer.effective_start_date
AND leer.effective_end_date
AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
AND petc.pl_id = c_pl_id
AND petc.enrt_typ_cycl_cd = 'L' -- life event
AND petc.business_group_id = p_business_group_id
AND p_effective_date BETWEEN petc.effective_start_date
AND petc.effective_end_date;
SELECT leer.lee_rsn_id
FROM ben_lee_rsn_f leer,
ben_popl_enrt_typ_cycl_f petc
WHERE leer.ler_id = c_ler_id
AND leer.business_group_id = p_business_group_id
AND p_effective_date BETWEEN leer.effective_start_date
AND leer.effective_end_date
AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
AND petc.pgm_id = c_pgm_id
AND petc.enrt_typ_cycl_cd = 'L'
AND petc.business_group_id = p_business_group_id
AND p_effective_date BETWEEN petc.effective_start_date
AND petc.effective_end_date;
select ler_id
from ben_per_in_ler pil
where pil.per_in_ler_id = p_ended_per_in_ler_id;
l_proc varchar2(80) := g_package||'.update_rate';
ben_prtt_rt_val_api.delete_prtt_rt_val(
p_prtt_rt_val_id => p_prtt_rt_val_id
,p_enrt_rt_id => l_enrt_rt_id
,p_person_id => l_pen_rec.person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_prv_rec.object_version_number
,p_effective_date => p_effective_date
);
ben_prtt_rt_val_api.update_prtt_rt_val(
p_prtt_rt_val_id => p_prtt_rt_val_id
,p_rt_end_dt => l_rt_end_dt
,p_ended_per_in_ler_id => p_ended_per_in_ler_id
,p_acty_base_rt_id => l_prv_rec.acty_base_rt_id
,p_input_value_id => l_abr_rec.input_value_id
,p_element_type_id => l_abr_rec.element_type_id
,p_person_id => l_pen_rec.person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_prv_rec.object_version_number
,p_effective_date => p_effective_date
);
end update_rate;
procedure delete_all_ledgers(p_bnft_prvdr_pool_id in number,
p_flex_rslt_id in number,
p_person_id in number,
p_per_in_ler_id in number,
p_effective_date in date,
p_business_group_id in number) is
--
-- All ledgers for the pool.
--
cursor c_bpl is
select bpl.bnft_prvdd_ldgr_id,
bpl.object_version_number,
bpl.effective_start_date,
bpl.acty_base_rt_id,
bpl.prtt_ro_of_unusd_amt_flag,
bpl.used_val
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.bnft_prvdr_pool_id = p_bnft_prvdr_pool_id
and bpl.prtt_enrt_rslt_id = p_flex_rslt_id
and bpl.business_group_id = p_business_group_id
and p_effective_date between
bpl.effective_start_date and bpl.effective_end_date
and pil.per_in_ler_id=bpl.per_in_ler_id
and pil.business_group_id=bpl.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
order by prtt_ro_of_unusd_amt_flag
/* Bug 5344961 - Added the above order by clause. This is because if a ROLLOVER (prtt_ro_of_unusd_amt_flag=Y)
BPL row is picked up earlier, we will delete the corresponding enrollment. This in a typical scenario (see bug)
will delete all provided BPL rows in that program. So if cursor C_BPL then tries to delete such BPL row
we will get an error. This typical scenario is when the rollover plan is same as the plan that has
used flex credits */
;
select pen.prtt_enrt_rslt_id,
pen.object_version_number,
pen.effective_start_date,
abr.entr_val_at_enrt_flag,
prv.prtt_rt_val_id,
prv.rt_val
from ben_prtt_enrt_rslt_f pen,
ben_prtt_rt_val prv,
ben_acty_base_rt_f abr
where prv.acty_base_rt_id = v_acty_base_rt_id
and prv.business_group_id = p_business_group_id
and prv.prtt_rt_val_stat_cd is null
and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and prv.acty_base_rt_id = abr.acty_base_rt_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
l_proc varchar2(80) := g_package||'.delete_all_ledgers';
l_datetrack_mode := hr_api.g_delete;
ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
p_validate => false,
p_bnft_prvdd_ldgr_id => l_bpl_rec.bnft_prvdd_ldgr_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_bpl_rec.object_version_number,
p_effective_date => l_effective_date,
p_datetrack_mode => l_datetrack_mode,
p_business_group_id => p_business_group_id,
p_process_enrt_flag => 'N');
update_rate(p_prtt_rt_val_id => l_prtt_rt_val_id,
p_val => (l_rt_val-l_bpl_rec.used_val),
p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id,
p_ended_per_in_ler_id => p_per_in_ler_id,
p_effective_date => p_effective_date,
p_business_group_id => p_business_group_id);
l_datetrack_mode := hr_api.g_delete;
ben_prtt_enrt_result_api.delete_enrollment(
p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id,
p_per_in_ler_id => p_per_in_ler_id,
p_object_version_number => l_pen_object_version_number,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_effective_date => p_effective_date,
p_business_group_id => p_business_group_id,
p_datetrack_mode => l_datetrack_mode,
p_source => 'benpstcr');
end delete_all_ledgers;
select
prr.mn_rlovr_pct_num,
prr.mn_rlovr_val,
prr.mx_rchd_dflt_ordr_num,
prr.mx_rlovr_pct_num,
prr.mx_rlovr_val,
prr.pct_rlovr_incrmt_num,
prr.pct_rndg_cd,
prr.pct_rndg_rl,
prr.rlovr_val_incrmt_num,
prr.rlovr_val_rl,
prr.val_rndg_cd,
prr.val_rndg_rl,
prr.acty_base_rt_id,
rslt.prtt_enrt_rslt_id,
prv.rt_val,
prv.prtt_rt_val_id,
prr.prtt_elig_rlovr_rl,
asg.assignment_id,
asg.organization_id,
loc.region_2,
oipl.opt_id,
rslt.pl_id,
rslt.pgm_id,
rslt.ler_id,
rslt.pl_typ_id
from
ben_bnft_pool_rlovr_rqmt_f prr,
ben_prtt_rt_val prv,
ben_prtt_enrt_rslt_f rslt,
per_all_assignments_f asg,
hr_locations_all loc,
ben_oipl_f oipl
where
prr.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
prr.business_group_id=p_business_group_id and
p_effective_date between
prr.effective_start_date and prr.effective_end_date and
prv.acty_base_rt_id = prr.acty_base_rt_id and
prv.prtt_enrt_rslt_id = rslt.prtt_enrt_rslt_id and
rslt.person_id=p_person_id and
rslt.business_group_id=p_business_group_id and
rslt.prtt_enrt_rslt_stat_cd is null and
rslt.enrt_cvg_thru_dt = hr_api.g_eot and
prv.business_group_id =p_business_group_id and
prv.prtt_rt_val_stat_cd is null and
p_effective_date between
rslt.effective_start_date and rslt.effective_end_date and
asg.person_id=rslt.person_id and
asg.assignment_type <> 'C'and
asg.primary_flag='Y' and
asg.location_id = loc.location_id(+) and
p_effective_date between
asg.effective_start_date and asg.effective_end_date and
oipl.oipl_id(+)=rslt.oipl_id and
p_effective_date between
oipl.effective_start_date(+) and oipl.effective_end_date(+) and
oipl.business_group_id(+)=p_business_group_id
order by prr.mx_rchd_dflt_ordr_num
;
select
nvl(sum(bpl.prvdd_val),0),
nvl(sum(decode(bpl.prtt_ro_of_unusd_amt_flag,
'N',bpl.used_val,
0)),0),-- non rollovers
nvl(sum(bpl.cash_recd_val),0),
nvl(sum(decode(bpl.prtt_ro_of_unusd_amt_flag,
'Y',bpl.used_val,
0)),0) -- rollovers
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where p_effective_date between
bpl.effective_start_date and bpl.effective_end_date and
bpl.business_group_id=p_business_group_id and
bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
bpl.prtt_enrt_rslt_id = p_flex_rslt_id and
-- exclude the rollover for this abr
(bpl.acty_base_rt_id<>l_acty_base_rt_id or
-- but include the used amounts for this abr
bpl.prtt_ro_of_unusd_amt_flag='N')
and pil.per_in_ler_id=bpl.per_in_ler_id
and pil.business_group_id=bpl.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
group by bpl.bnft_prvdr_pool_id;
select
bnft_prvdd_ldgr_id,
bpl.used_val,
bpl.object_version_number,
bpl.effective_start_date
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where p_effective_date between
bpl.effective_start_date and bpl.effective_end_date and
bpl.business_group_id=p_business_group_id and
bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
bpl.prtt_enrt_rslt_id = p_flex_rslt_id and
bpl.acty_base_rt_id = l_acty_base_rt_id and
bpl.prtt_ro_of_unusd_amt_flag = 'Y'
and pil.per_in_ler_id=bpl.per_in_ler_id
and pil.business_group_id=bpl.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select prv.prtt_rt_val_id,
prv.rt_val,
prv.rt_strt_dt
from ben_prtt_rt_val prv -- ,ben_prtt_enrt_rslt_f pen
where prv.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
and prv.acty_base_rt_id = l_acty_base_rt_id
and prv.prtt_rt_val_stat_cd is null
and prv.business_group_id = p_business_group_id
and (prv.per_in_ler_id = c_per_in_ler_id or c_per_in_ler_id = -1 ) -- bug#5136668 -only for that lf event
order by prv.rt_strt_dt desc;
select elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc
where prtt_enrt_rslt_id = l_prtt_enrt_rslt_id;
update_rate(p_prtt_rt_val_id => l_prtt_rt_val_id,
p_val => (l_rt_val + l_balance),
p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id,
p_ended_per_in_ler_id => p_per_in_ler_id,
p_effective_date => p_effective_date,
p_business_group_id => p_business_group_id);
l_datetrack_mode:=hr_api.g_update;
ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
p_bnft_prvdd_ldgr_id => l_bnft_prvdd_ldgr_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_prtt_ro_of_unusd_amt_flag => 'Y'
,p_frftd_val => null
,p_prvdd_val => null
,p_used_val => l_balance
,p_bnft_prvdr_pool_id => p_bnft_prvdr_pool_id
,p_acty_base_rt_id => l_acty_base_rt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_prtt_enrt_rslt_id => p_flex_rslt_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_cash_recd_val => 0
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_process_enrt_flag => 'N'
);
hr_utility.set_location('UPDATED LEDGER ID='||To_char(l_bnft_prvdd_ldgr_id),130);
update_rate(p_prtt_rt_val_id => l_prtt_rt_val_id,
p_val => (l_rt_val-l_old_rlovr_val+l_balance),
p_ended_per_in_ler_id => p_per_in_ler_id,
p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
select nvl(sum(bpl.prvdd_val),0) tot_prvdd,
nvl(sum(bpl.used_val),0) tot_used,
nvl(sum(frftd_val),0) tot_frftd,
nvl(sum(cash_recd_val),0) tot_cash,
pil.ler_id
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.bnft_prvdr_pool_id = c_bnft_prvdr_pool_id
and bpl.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and c_effective_date
between bpl.effective_start_date and bpl.effective_end_date
and pil.per_in_ler_id=bpl.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
group by pil.ler_id;
select bpl.acty_base_rt_id
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.bnft_prvdr_pool_id = p_bnft_prvdr_pool_id
and bpl.prtt_enrt_rslt_id = p_flex_rslt_id
and bpl.prvdd_val is not null
and bpl.business_group_id = p_business_group_id
and p_effective_date between
bpl.effective_start_date and bpl.effective_end_date
and pil.per_in_ler_id=bpl.per_in_ler_id
and pil.business_group_id=bpl.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select bpp.pct_rndg_cd,
bpp.pct_rndg_rl,
bpp.dflt_excs_trtmt_cd,
bpp.dflt_excs_trtmt_rl,
bpp.mn_dstrbl_pct_num,
bpp.mn_dstrbl_val,
bpp.mx_dstrbl_pct_num,
bpp.mx_dstrbl_val,
bpp.rlovr_rstrcn_cd,
bpp.val_rndg_cd,
bpp.val_rndg_rl,
bpp.auto_alct_excs_flag,
bpp.uses_net_crs_mthd_flag
from ben_bnft_prvdr_pool_f bpp
where bpp.bnft_prvdr_pool_id = p_bnft_prvdr_pool_id
and bpp.business_group_id = p_business_group_id
and p_effective_date between
bpp.effective_start_date and bpp.effective_end_date;
procedure update_net_credit_rate
(p_prtt_rt_val_id in number
,p_flex_rslt_id in number
,p_acty_base_rt_id in number
,p_per_in_ler_id in number
,p_def_exc_amount in number
,p_effective_date in date
,p_business_group_id in number
) is
--
cursor c_prv_child is
select prv.prtt_rt_val_id,
prv.rt_val,
prv.rt_strt_dt,
prv.acty_base_rt_id
from ben_prtt_rt_val prv
,ben_acty_base_rt_f abr
where prv.prtt_enrt_rslt_id = p_flex_rslt_id
and prv.business_group_id = p_business_group_id
and prv.prtt_rt_val_stat_cd is null
and abr.acty_base_rt_id = prv.acty_base_rt_id
and p_effective_date
between abr.effective_start_date and
abr.effective_end_date
and abr.business_group_id = prv.business_group_id
and abr.parnt_acty_base_rt_id = p_acty_base_rt_id
order by prv.rt_strt_dt desc;
select pen.*
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_flex_rslt_id
and pen.business_group_id = p_business_group_id
and p_effective_date
between pen.effective_start_date and
pen.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null;
select abr.*
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = p_acty_base_rt_id
and abr.business_group_id = p_business_group_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select abr.*
from ben_acty_base_rt_f abr
where abr.parnt_acty_base_rt_id = p_acty_base_rt_id
and abr.business_group_id = p_business_group_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select acty_ref_perd_cd
,cmcd_ref_perd_cd
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_flex_rslt_id
and prv.prtt_rt_val_stat_cd is null
and prv.business_group_id = p_business_group_id;
SELECT leer.lee_rsn_id
FROM ben_lee_rsn_f leer,
ben_popl_enrt_typ_cycl_f petc
WHERE leer.ler_id = c_ler_id
AND leer.business_group_id = p_business_group_id
AND p_effective_date BETWEEN leer.effective_start_date
AND leer.effective_end_date
AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
AND petc.pl_id = c_pl_id
AND petc.enrt_typ_cycl_cd = 'L' -- life event
AND petc.business_group_id = p_business_group_id
AND p_effective_date BETWEEN petc.effective_start_date
AND petc.effective_end_date;
SELECT leer.lee_rsn_id
FROM ben_lee_rsn_f leer,
ben_popl_enrt_typ_cycl_f petc
WHERE leer.ler_id = c_ler_id
AND leer.business_group_id = p_business_group_id
AND p_effective_date BETWEEN leer.effective_start_date
AND leer.effective_end_date
AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
AND petc.pgm_id = c_pgm_id
AND petc.enrt_typ_cycl_cd = 'L'
AND petc.business_group_id = p_business_group_id
AND p_effective_date BETWEEN petc.effective_start_date
AND petc.effective_end_date;
l_proc varchar2(80) := g_package||'.update_net_credit_rate';
hr_utility.set_location('update_net_credit amt '||p_def_exc_amount,5);
update_rate
(p_prtt_rt_val_id => l_prtt_rt_val_id
,p_val => p_def_exc_amount
,p_prtt_enrt_rslt_id => p_flex_rslt_id
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
);
update_rate
(p_prtt_rt_val_id => l_prv_child_rec.prtt_rt_val_id
,p_val => l_child_rt_val
,p_prtt_enrt_rslt_id => p_flex_rslt_id
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
);
end update_net_credit_rate;
l_debit_ledger_deleted boolean := false;
l_credit_ledger_deleted boolean := false;
select 'Y'
from ben_pgm_f pgm
where pgm.pgm_id = p_pgm_id
and pgm.business_group_id = p_business_group_id
and pgm.pgm_typ_cd in ('FLEX','FPC')
and p_effective_date between
pgm.effective_start_date and pgm.effective_end_date;
select bpl.bnft_prvdd_ldgr_id,
bpl.object_version_number,
bpl.effective_start_date,
bpl.bnft_prvdr_pool_id,
bpl.prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f enrt_pen,
ben_prtt_rt_val enrt_prv,
ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil,
ben_prtt_enrt_rslt_f flex_pen
where enrt_pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and enrt_pen.prtt_enrt_rslt_stat_cd is null
and enrt_pen.enrt_cvg_thru_dt = hr_api.g_eot
and p_effective_date between
enrt_pen.effective_start_date and enrt_pen.effective_end_date
and enrt_pen.prtt_enrt_rslt_id = enrt_prv.prtt_enrt_rslt_id
and enrt_prv.prtt_rt_val_stat_cd is null
and enrt_prv.acty_base_rt_id = bpl.acty_base_rt_id
and bpl.used_val is not null
and p_effective_date between
bpl.effective_start_date and bpl.effective_end_date
and bpl.prtt_enrt_rslt_id = flex_pen.prtt_enrt_rslt_id
and flex_pen.person_id = p_person_id
and flex_pen.prtt_enrt_rslt_stat_cd is null
and flex_pen.enrt_cvg_thru_dt = hr_api.g_eot
and p_effective_date between
flex_pen.effective_start_date and flex_pen.effective_end_date
and pil.per_in_ler_id=bpl.per_in_ler_id
and pil.business_group_id=bpl.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
order by bpl.bnft_prvdd_ldgr_id, bpl.effective_start_date -- rajkiran
;
select bpp.pgm_id,
bpp.plip_id,
bpp.ptip_id,
bpp.cmbn_ptip_id,
bpp.cmbn_plip_id,
bpp.cmbn_ptip_opt_id,
bpp.business_group_id,
bpp.bnft_prvdr_pool_id,
bpl.prvdd_val,
bpl.prtt_enrt_rslt_id,
bpp.uses_net_crs_mthd_flag
from ben_prtt_enrt_rslt_f flex_pen,
ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil,
ben_bnft_prvdr_pool_f bpp
where flex_pen.person_id = p_person_id
and flex_pen.pgm_id = p_pgm_id
and flex_pen.prtt_enrt_rslt_stat_cd is null
and flex_pen.enrt_cvg_thru_dt = hr_api.g_eot
and p_effective_date between
flex_pen.effective_start_date and flex_pen.effective_end_date
and flex_pen.prtt_enrt_rslt_id = bpl.prtt_enrt_rslt_id
and bpl.prvdd_val is not null
and p_effective_date between
bpl.effective_start_date and bpl.effective_end_date
and bpl.bnft_prvdr_pool_id = bpp.bnft_prvdr_pool_id
and p_effective_date between
bpp.effective_start_date and bpp.effective_end_date
and pil.per_in_ler_id=bpl.per_in_ler_id
and pil.business_group_id=bpl.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select bpl.frftd_val
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.bnft_prvdr_pool_id = v_pool_id
and bpl.prtt_enrt_rslt_id = l_flex_rslt_id
and bpl.business_group_id = p_business_group_id
and bpl.frftd_val is not null
and p_effective_date between
bpl.effective_start_date and bpl.effective_end_date
and pil.per_in_ler_id=bpl.per_in_ler_id
and pil.business_group_id=bpl.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select prv.prtt_rt_val_id,
prv.rt_val,
prv.rt_strt_dt
from ben_prtt_rt_val prv
,ben_acty_base_rt_f abr
where prv.prtt_enrt_rslt_id = l_flex_rslt_id
and prv.business_group_id = p_business_group_id
and prv.prtt_rt_val_stat_cd is null
and abr.acty_base_rt_id = prv.acty_base_rt_id
and p_effective_date
between abr.effective_start_date and
abr.effective_end_date
and abr.business_group_id = prv.business_group_id
and abr.acty_typ_cd not in ('NCRDSTR','NCRUDED')
and abr.parnt_acty_base_rt_id is null
order by 3 desc;
select prv.prtt_rt_val_id,
prv.rt_val,
prv.rt_strt_dt,
prv.acty_typ_cd,
prv.acty_base_rt_id
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = l_flex_rslt_id
and prv.business_group_id = p_business_group_id
and prv.prtt_rt_val_stat_cd is null
and prv.acty_typ_cd in ('NCRDSTR','NCRUDED')
order by 3 desc;
select abr.acty_base_rt_id
from ben_acty_base_rt_f abr
,ben_prtt_enrt_rslt_f pen
where abr.pl_id = pen.pl_id
and pen.prtt_enrt_rslt_id = l_flex_rslt_id
and abr.business_group_id = p_business_group_id
and pen.business_group_id = abr.business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date
between pen.effective_start_date and
pen.effective_end_date
and abr.acty_typ_cd = p_acty_typ_cd;
select bnft_prvdd_ldgr_id,
effective_start_date,
effective_end_date,
object_version_number
from ben_bnft_prvdd_ldgr_f bpl
where bnft_prvdd_ldgr_id = p_bpl_id
--and effective_start_date < p_eff_dt
order by effective_start_date;
l_debit_ledger_deleted := true;
l_datetrack_mode := hr_api.g_delete;
l_datetrack_mode := hr_api.g_delete;
ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
p_validate => false,
p_bnft_prvdd_ldgr_id => l_dbt_bpl_rec.bnft_prvdd_ldgr_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_ovn, --l_dbt_bpl_rec.object_version_number,
p_effective_date => l_effective_date,
p_datetrack_mode => l_datetrack_mode,
p_business_group_id => p_business_group_id,
p_process_enrt_flag => 'N');
l_credit_ledger_deleted := true;
delete_all_ledgers(
p_bnft_prvdr_pool_id => l_crdt_bpp_rec.bnft_prvdr_pool_id,
p_person_id => p_person_id,
p_per_in_ler_id => p_per_in_ler_id,
p_flex_rslt_id => l_flex_rslt_id,
p_effective_date => p_effective_date,
p_business_group_id => p_business_group_id);
if not(l_credit_ledger_deleted) and not(l_debit_ledger_deleted) then
--
-- No ledger deletion took place, it means everything is in order.
--
hr_utility.set_location(l_proc,45);
update_net_credit_rate
(p_prtt_rt_val_id => l_prv_rec.prtt_rt_val_id
,p_flex_rslt_id => l_flex_rslt_id
,p_acty_base_rt_id => l_prv_rec.acty_base_rt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_def_exc_amount => l_def_exc_amount
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
);
update_net_credit_rate
(p_prtt_rt_val_id => l_prv_rec.prtt_rt_val_id
,p_flex_rslt_id => l_flex_rslt_id
,p_acty_base_rt_id => l_prv_rec.acty_base_rt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_def_exc_amount => 0
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
);
update_net_credit_rate
(p_prtt_rt_val_id => l_prv_rec.prtt_rt_val_id
,p_flex_rslt_id => l_flex_rslt_id
,p_acty_base_rt_id => l_prv_rec.acty_base_rt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_def_exc_amount => abs(l_def_exc_amount)
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
);
update_net_credit_rate
(p_prtt_rt_val_id => l_prv_rec.prtt_rt_val_id
,p_flex_rslt_id => l_flex_rslt_id
,p_acty_base_rt_id => l_prv_rec.acty_base_rt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_def_exc_amount => 0
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
);
update_net_credit_rate
(p_prtt_rt_val_id => null
,p_flex_rslt_id => l_flex_rslt_id
,p_acty_base_rt_id => l_acty_base_rt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_def_exc_amount => abs(l_def_exc_amount)
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
);
update_net_credit_rate
(p_prtt_rt_val_id => null
,p_flex_rslt_id => l_flex_rslt_id
,p_acty_base_rt_id => l_acty_base_rt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_def_exc_amount => l_def_exc_amount
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
);
update_rate(p_prtt_rt_val_id => l_prtt_rt_val_id,
p_val => l_total_credits,
p_prtt_enrt_rslt_id => l_flex_rslt_id,
p_ended_per_in_ler_id => p_per_in_ler_id,
p_effective_date => p_effective_date,
p_business_group_id => p_business_group_id);