[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
115.5 12-JAN-2000 stee - Fix update_cobra_info to write a qualified
beneficiary row when a dependent is
designated.
115.6 17-JAN-2000 stee - When checking for max cvg thru dt, limit it
to the COBRA program.
WWBUG# 1166171
115.7 30-JAN-2000 stee - If a person is does not need the
extension in a disability event as he/she
already has 36 months, update the
eligibility end date on pil_elctbl_chc_popl
in case they are allowed to make elections.
WWBUG# 11772229.
115.7 06-FEB-2000 stee - Check for backed out nocopy events.WWBUG#1178633.
115.8 01-MAR-2000 stee - COBRA by plan type.
115.10 06-MAR-2000 stee - Fix disability at time of life event dates.
115.11 30-MAR-2000 stee - Fix disability within 60 days of the life
event. Change update_dpnt_cobra_info to
not error out nocopy if the elig dates are null
as the dates are null for an open enrollment
event. WWBUG#: 1249902, 1147607, 1252082.
115.12 31-MAR-2000 stee - Fix to extend cobra eligibility end date
if it is greater than the current
eligibility end date.
Write cbr_per_in_ler for all qualified
beneficiary eligible for the disability
extension.
115.13 04-APR-2000 mmogel - Added tokens to messages to make them
more meaningful to the user
115.14 06-APR-2000 stee - When checking for min dates use null
instead of %notfound. Bug# 4956
115.15 28-APR-2000 stee - Update cobra ineligibility status when
prtt is no longer eligible or has waived
coverage. Also fix chk_enrld_or_cvrd
cursor to outer join to oipl table
if plan has no option.
115.16 17-May-2000 stee - Add pl_typ_id to the where clause when
selecting enrollment coverage end date
in determine_cobra_elig_dates for cobra
by plan type.
115.17 11-OCT-2000 rchase - added parameter pl typ id for
calls to formula as contexts.
115.18 20-OCT-2000 stee - When checking if a person is a cobra
qualified beneficiary, use elig_end_dt >
lf_evt_ocrd_dt as cobra start date can
be after the lf evt ocrd dt. WWBUG#1469388.
115.19 26-Oct-2000 rchase - fix wwbug 1480395 fetch pgm_id, if
necessary for formula context. Ensure the
proper rule is passed to the formula call
when determining mx poe date.
115.20 21-Mar-2001 ikasire bug 1566944 added ptip parameter and
edited c_get_max_poe cursor to see for
pgm_id or ptip_id
115.21 20-Aug-2001 stee Bug 1348235: Fix duplicate qual bnf row
when a dependent is added after the initial
qualifying event.
115.22 29-Aug-2001 pbodla bug:1949361 jurisdiction code is
derived inside benutils.formula
115.23 30-NOV-2001 stee Back out nocopy changes made in version 115.20.
The c_get_max_poe cursor may retrieve
the wrong period of enrollment if COBRA
by plan type is implemented. Also, for
subsequent events, it is not finding the
current qualified beneficiary row so a
duplicate one is created.
115.24 22-JAN-2002 stee If a person is no longer disabled, reduce
the max period of enrollment if applicable.
Bug# 2068332.
115.25 01-FEB-2002 stee Added dbdrv lines.
115.26 21-MAY-2002 stee Fix the cobra eligibility end date.
Bug# 2355218.
115.27 08-Jun-2002 pabodla - Do not select the contingent worker
assignment when assignment data is
fetched.
115.28 12-Aug-2002 stee Check if electable choices exist before
ending COBRA eligibility. Bug 1794808.
115.29 11-Sep-2002 stee Close c_get_ler_type cursor.
115.30 04-Nov-2002 stee For COBRA by plan type, check for enrollment
in the COBRA program instead of plan type
to determine eligibility. Bug 2626516.
115.31 23-DEC-2002 lakrish NOCOPY changes.
115.32 14-MAR-2003 stee When determining the enrollment coverage
start date for cobra eligibility start date,
use electable choices instead of enrollment
results. Bug 2821672 and 2815797.
115.33 13-Oct-2003 rpillay Bug 3097501 - Added procedures
allocate_payment, do_rounding and
get_amount_due
115.34 15-Oct-2003 rpillay Bug 3097501 - Added date check in
c_rates cursor (allocate_payment)
115.35 15-Oct-2003 rpillay Bug 3097501 - Changes to c_prev_pymts_latest
cursor (allocate_payment)
115.36 20-Oct-2003 rpillay Bug 3097501 - Changes to allocate_payments to
adjust payments against past and future rates
115.37 22-Oct-2003 rpillay Bug 3097501 - Changes to handle FSA balance
calculations when no change in amount
115.38 24-Oct-2003 rpillay Bug 3097501 - Changes to get_amount_due -
Using Rates in place of element balance
for FSA
115.39 18-Nov-2003 rpillay Bug 3097501 - Changes for not doing automatic
adjustments for plan year and element
changes
115.40 24-Nov-2003 rpillay Added nocopy to p_excess_amount
115.41 19-Dec-2003 rpillay Changes to ignore excess payments in
allocate_payments
115.42 22-Dec-2003 rpillay Changes to c_pen in allocate_payments to
check for coverage start and thru dates
115.43 05-Jan-2004 rpillay Bug 3338978 - added check for month_strt_dt
< rt_strt_dt in allocate_payments
115.44 13-Jan-2004 stee Remove ptip_id from the where clause in
c_get_enrt_cvg_thru_dt and
c_get_dpnt_cvg_thru_dt in the
get_max_cvg_thru_dt function. The eligibility
end date is the coverage end date of the
program. Bug 3368053.
115.45 15-Jan-2004 rpillay Moved code to fetch costing data to
get_costing_details procedure.
Pass cost_allocation_keyflex_id as NULL
while making payment adjustments to ensure
that costed values get assigned using the
Costing Hierarchy.
115.46 27-Sep-2004 tjesumic new param p_cvrd_today added in chk_enrld_or_cvrd # 3843549
coerage validation changes as per the param
115.47 04-jan-2005 ssarkar Bug# 3630753 : commented fnd_message.set_token('PROC',l_proc).
115.48 08-Sep-2005 stee If eligibility period end date is 01/01/0001, then
set the quald_bnf_flag = 'N' and leave the eligibility
period end date as is. Bug 4486609.
115.49 28-dec-2005 stee Only terminate cobra eligibility in chk_cobra_eligibility
if the person was previously enrolled in COBRA
benefits. Bug 4338471.
115.50 15-Feb-2005 bmanyam 4881917 PERF Fix: XBuild1 Drop
115.51 30-Jun-2006 swjain 5331889 Added person_id param to benutils.formula call in
115.52 08-Nov-2006 stee When creating quald_bnf for a dependent, get
the cvrd_emp_person_id with a person type
usage of 'PRTN'.
115.53 22-Feb-2008 rtagarra Bug 6840074
115.55 30-May-2008 velvanop Bug 7116537- Commented the p_effective_date condition of cursor c_get_enrt_cvg_thru_dt in
get_max_cvg_thru_dt function.
115.56 20-Feb-2009 velvanop Bug 8211414- Modified cursor c_get_quald_bnf. To determine QB, flag 'quald_bnf_flag' should be 'Y' .
Even though QB records exists, new QB records will be created for a LE only if the flag is set to 'N'.
115.57 28-Apr-2009 stee ARRA COBRA changes.
115.58 10-May-2009 stee ARRA COBRA changes - Create quald bnf
for dependent.
115.59 10-Feb-2009 stee Bug 9294528 - Check that a dependent does
not have a initial qualifying event
before adjusting their cobra end date.
115.60 09-Jun-2010 stee Bug 9690964 - If the covered employee id
is not found, get it from the prior
quald benefiaciary row.
115.61 02-May-2011 stee Bug 12386329 - Terminate COBRA eligibility for
covered dependents if the participant loses
eligibility.
115.62 28-Aug-2012 stee Bug 14332963 - Do not terminate the COBRA
eligibility for a covered dependent if the
there is a potential event for the dependent.
*/
--------------------------------------------------------------------------------
g_package varchar2(80):='ben_cobra_requirements.';
select pil.lf_evt_ocrd_dt
from ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id;
select null
from ben_cbr_per_in_ler crp
where crp.per_in_ler_id = p_per_in_ler_id
and crp.business_group_id = p_business_group_id
and crp.init_evt_flag = 'Y';
select per.registered_disabled_flag
from per_all_people_f per
where per.person_id = p_person_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
per.effective_start_date and per.effective_end_date
and per.business_group_id = p_business_group_id;
select psl.new_val
from ben_per_info_chg_cs_ler_f psl
where psl.source_table = 'PER_ALL_PEOPLE_F'
and psl.source_column = 'REGISTERED_DISABLED_FLAG'
and nvl(p_lf_evt_ocrd_dt, p_effective_date)
between psl.effective_start_date and psl.effective_end_date
and psl.business_group_id = p_business_group_id;
select ler.ler_id
from ben_ler_f ler
where ler.typ_cd = 'DSBLTY'
and ler.business_group_id = p_business_group_id
and ler.qualg_evt_flag = 'Y'
and p_lf_evt_ocrd_dt
between ler.effective_start_date
and ler.effective_end_date;
select peo.*
from ben_elig_to_prte_rsn_f peo
where peo.ler_id = l_dsblty_ler_id
and peo.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt, p_effective_date)
between peo.effective_start_date and peo.effective_end_date
and nvl(peo.pgm_id,-1) = nvl(p_pgm_id,-1)
and nvl(peo.ptip_id,-1) = nvl(p_ptip_id,-1)
and (peo.mx_poe_val is not null or
peo.mx_poe_rl is not null);
,p_update out nocopy boolean
) is
--
-- Declare cursors and local variables
--
l_det_end_date boolean := false;
select peo.*
,ler.typ_cd
,pil.lf_evt_ocrd_dt
from ben_elig_to_prte_rsn_f peo
,ben_per_in_ler pil
,ben_ler_f ler
where pil.ler_id = peo.ler_id
and pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and peo.business_group_id = pil.business_group_id
and nvl(p_lf_evt_ocrd_dt, p_effective_date)
between peo.effective_start_date and peo.effective_end_date
and nvl(peo.pgm_id,-1) = nvl(p_pgm_id,-1)
and nvl(peo.ptip_id,-1) = nvl(p_ptip_id,-1)
and (peo.mx_poe_val is not null or
peo.mx_poe_rl is not null)
and pil.ler_id = ler.ler_id
and nvl(p_lf_evt_ocrd_dt, p_effective_date)
between ler.effective_start_date and ler.effective_end_date
and ler.business_group_id = pil.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
select cqb.*
from ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where cqb.quald_bnf_person_id = p_person_id
and cqb.business_group_id = p_business_group_id
and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
and crp.init_evt_flag = 'Y'
and cqb.quald_bnf_flag = 'Y' -- Bug 8211414
and cqb.pgm_id = nvl(p_pgm_id, cqb.pgm_id)
and nvl(cqb.ptip_id,-1) = nvl(p_ptip_id, -1)
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.business_group_id = cqb.business_group_id
and crp.business_group_id = pil.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select min(epe.enrt_cvg_strt_dt)
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.ptip_id = nvl(p_ptip_id, epe.ptip_id)
and epe.pgm_id = nvl(p_pgm_id, epe.pgm_id)
and epe.enrt_cvg_strt_dt is not null
and epe.elctbl_flag = 'Y'
and epe.business_group_id = p_business_group_id;
select reg.regn_id
from ben_regn_f reg
where p_effective_date between
reg.effective_start_date and reg.effective_end_date
and reg.business_group_id = p_business_group_id
and reg.sttry_citn_name = 'COBRA';
select loc.region_2,asg.assignment_id,asg.organization_id
from hr_locations_all loc,per_all_assignments_f asg
where loc.location_id(+) = asg.location_id
and asg.person_id = p_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and p_effective_date
between asg.effective_start_date and asg.effective_end_date
and asg.business_group_id=p_business_group_id;
select pgm_id
from ben_ptip_f
where ptip_id = p_ptip_id
and p_effective_date between effective_start_date
and effective_end_date;
select null
from ben_ler_f ler
,ben_per_in_ler pil
where ler.ler_id = pil.ler_id
and pil.per_in_ler_id = p_per_in_ler_id
and ler.typ_cd = 'ENDDSBLTY'
and p_effective_date between
ler.effective_start_date and ler.effective_end_date;
select crp.prvs_elig_perd_end_dt
from ben_ler_f ler
,ben_per_in_ler pil
,ben_cbr_per_in_ler crp
where ler.ler_id = pil.ler_id
and pil.per_in_ler_id = crp.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pil.business_group_id = p_business_group_id
and pil.business_group_id = ler.business_group_id
and crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
and crp.business_group_id = ler.business_group_id
and ler.typ_cd = 'DSBLTY'
and crp.cnt_num = (select max(crp2.cnt_num)
from ben_cbr_per_in_ler crp2
,ben_per_in_ler pil2
,ben_ler_f ler2
where crp2.cbr_quald_bnf_id = p_cbr_quald_bnf_id
and crp2.business_group_id = pil2.business_group_id
and crp2.business_group_id = ler2.business_group_id
and crp2.business_group_id = p_business_group_id
and crp2.per_in_ler_id = pil2.per_in_ler_id
and pil2.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pil2.ler_id = ler2.ler_id
and ler2.qualg_evt_flag = 'Y')
and p_effective_date between
ler.effective_start_date and ler.effective_end_date;
select peo.*
,pil.lf_evt_ocrd_dt
from ben_ler_f ler
,ben_per_in_ler pil
,ben_cbr_per_in_ler crp
,ben_elig_to_prte_rsn_f peo
where ler.ler_id = pil.ler_id
and pil.per_in_ler_id = crp.per_in_ler_id
and crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
and pil.business_group_id = p_business_group_id
and crp.business_group_id = pil.business_group_id
and ler.business_group_id = pil.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and crp.init_evt_flag = 'Y'
and pil.lf_evt_ocrd_dt
between peo.effective_start_date and peo.effective_end_date
and nvl(peo.pgm_id,-1) = nvl(p_pgm_id,-1)
and nvl(peo.ptip_id,-1) = nvl(p_ptip_id,-1)
and (peo.mx_poe_val is not null or
peo.mx_poe_rl is not null)
and pil.lf_evt_ocrd_dt
between ler.effective_start_date and ler.effective_end_date;
p_update := true;
p_update := false;
p_update := false;
select loc.region_2,asg.assignment_id,asg.organization_id
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
and asg.business_group_id=p_business_group_id;
select reg.regn_id
from ben_regn_f reg
where p_effective_date between
reg.effective_start_date and reg.effective_end_date
and reg.business_group_id = p_business_group_id
and reg.sttry_citn_name = 'COBRA';
l_update boolean := false;
select null
from ben_pgm_f pgm
where pgm.pgm_id = p_pgm_id
and pgm.pgm_typ_cd like 'COBRA%'
and p_effective_date
between pgm.effective_start_date and pgm.effective_end_date
and pgm.business_group_id=p_business_group_id;
l_update := true;
return l_update;
select null
from ben_prtt_enrt_rslt_f pen
,ben_pl_f pln
,ben_oipl_f cop
,ben_opt_f opt
where pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.pgm_id = nvl(p_pgm_id, pen.pgm_id)
-- and pen.ptip_id = nvl(p_ptip_id, pen.ptip_id)
and pen.sspndd_flag = 'N'
---
and
( (
nvl(p_cvrd_today,'N') = 'N'
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
) OR
(
nvl(p_cvrd_today,'N') = 'Y'
AND pen.effective_end_date = hr_api.g_eot
AND p_effective_date BETWEEN pen.enrt_cvg_strt_dt
AND pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
)
)
and pen.business_group_id = p_business_group_id
and pen.pl_id = pln.pl_id
and pln.invk_dcln_prtn_pl_flag = 'N'
and p_effective_date between pln.effective_start_date
and pln.effective_end_date
and pln.business_group_id = pen.business_group_id
and pen.oipl_id = cop.oipl_id (+)
and pen.business_group_id = cop.business_group_id (+)
and p_effective_date between
cop.effective_start_date (+)
and cop.effective_end_date (+)
and cop.opt_id = opt.opt_id (+)
and nvl(opt.invk_wv_opt_flag,'N') = 'N'
and cop.business_group_id = opt.business_group_id (+)
and p_effective_date between
opt.effective_start_date (+)
and opt.effective_end_date (+);
select null
from ben_prtt_enrt_rslt_f pen
,ben_elig_cvrd_dpnt_f pdp
where pdp.dpnt_person_id = p_person_id
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.pgm_id = nvl(p_pgm_id, pen.pgm_id)
-- and pen.ptip_id = nvl(p_ptip_id, pen.ptip_id)
and pen.sspndd_flag = 'N'
---
and
( (
nvl(p_cvrd_today,'N') = 'N'
and pdp.cvg_thru_dt = hr_api.g_eot
and pdp.cvg_thru_dt <= pdp.effective_end_date
and p_effective_date between pdp.effective_start_date
and pdp.effective_end_date
) OR
(
nvl(p_cvrd_today,'N') = 'Y'
AND pdp.effective_end_date = hr_api.g_eot
AND p_effective_date BETWEEN pdp.cvg_strt_dt
AND pdp.cvg_thru_dt
AND pdp.cvg_strt_dt < pdp.effective_end_date
)
)
--and pen.effective_end_date = hr_api.g_eot
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
and pen.business_group_id = p_business_group_id
and pdp.business_group_id = pen.business_group_id;
select max(pen.enrt_cvg_thru_dt)
from ben_prtt_enrt_rslt_f pen
where pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.sspndd_flag = 'N'
and pen.pgm_id = nvl(p_pgm_id,pen.pgm_id)
--
-- Bug 3368053: Remove ptip_id
-- and pen.ptip_id = nvl(p_ptip_id,pen.ptip_id)
and pen.prtt_enrt_rslt_stat_cd is null
and nvl(pen.per_in_ler_id,-1) = nvl(p_per_in_ler_id, -1)
and pen.enrt_cvg_thru_dt <> hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot
-- Bug 7116537, Commented the condition p_effective_date between effective_start_date and effective_end_date
/*and p_effective_date between
pen.effective_start_date and pen.effective_end_date*/
and pen.business_group_id = p_business_group_id;
select max(pdp.cvg_thru_dt)
from ben_elig_cvrd_dpnt_f pdp
,ben_prtt_enrt_rslt_f pen
where pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and pdp.dpnt_person_id = p_person_id
and pen.pgm_id = p_pgm_id
--
-- Bug 3368053: Remove ptip_id
-- and pen.ptip_id = nvl(p_ptip_id,pen.ptip_id)
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
and pen.business_group_id = p_business_group_id
and pdp.cvg_thru_dt <> hr_api.g_eot
and p_lf_evt_ocrd_dt >= pdp.cvg_strt_dt
and pdp.effective_end_date = hr_api.g_eot
and p_effective_date between pdp.effective_start_date
and pdp.effective_end_date
and pdp.business_group_id = pen.business_group_id
group by pdp.dpnt_person_id;
procedure update_cobra_elig_info
(p_person_id in number
,p_per_in_ler_id in number
,p_lf_evt_ocrd_dt in date
,p_effective_date in date
,p_business_group_id in number
,p_validate in boolean default false) is
--
l_proc varchar2(80) := g_package||
'.update_cobra_elig_info';
l_update boolean;
select epe.*, pgm.poe_lvl_cd
from ben_elig_per_elctbl_chc epe
,ben_pgm_f pgm
where epe.per_in_ler_id = p_per_in_ler_id
and epe.business_group_id = p_business_group_id
and epe.pgm_id = pgm.pgm_id
and nvl(p_lf_evt_ocrd_dt, p_effective_date)
between pgm.effective_start_date and pgm.effective_end_date
and pgm.business_group_id = p_business_group_id
and pgm.pgm_typ_cd like 'COBRA%'
order by epe.pgm_id, epe.ptip_id;
select ler.*
from ben_ler_f ler
,ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.ler_id = ler.ler_id
and ler.name = 'Assistance Eligible Individual'
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date;
select cqb.*
from ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where cqb.quald_bnf_person_id = p_person_id
and cqb.business_group_id = p_business_group_id
and p_lf_evt_ocrd_dt
between cqb.cbr_elig_perd_strt_dt
and cqb.cbr_elig_perd_end_dt
and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
and crp.init_evt_flag = 'Y'
and cqb.quald_bnf_flag = 'N'
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.business_group_id = cqb.business_group_id
and crp.business_group_id = pil.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
ben_cbr_quald_bnf_api.update_cbr_quald_bnf
(p_cbr_quald_bnf_id=> l_cqb_rec.cbr_quald_bnf_id
,p_quald_bnf_flag => 'Y'
,p_business_group_id => p_business_group_id
,p_object_version_number => l_cqb_object_version_number
,p_effective_date => p_effective_date);
,p_update => l_update
);
l_update)
then
update_cobra_info
(p_per_in_ler_id => p_per_in_ler_id
,p_person_id => p_person_id
,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
,p_cqb_object_version_number => l_cqb_object_version_number
,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
,p_old_cbr_elig_perd_end_dt => l_old_cbr_elig_perd_end_dt
,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
,p_dsbld_apls => l_dsbld_apls
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
,p_cvrd_emp_person_id => l_cvrd_emp_person_id
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
,p_pgm_id => l_epe_rec.pgm_id
,p_validate => p_validate
);
,p_update => l_update
);
l_update)
then
--
update_cobra_info
(p_per_in_ler_id => p_per_in_ler_id
,p_person_id => p_person_id
,p_cbr_quald_bnf_id => l_cbr_quald_bnf_id
,p_cqb_object_version_number => l_cqb_object_version_number
,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
,p_old_cbr_elig_perd_end_dt => l_old_cbr_elig_perd_end_dt
,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
,p_dsbld_apls => l_dsbld_apls
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
,p_cvrd_emp_person_id => l_cvrd_emp_person_id
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
,p_pgm_id => l_epe_rec.pgm_id
,p_pl_typ_id => l_epe_rec.pl_typ_id
,p_ptip_id => l_epe_rec.ptip_id
,p_validate => p_validate
);
end update_cobra_elig_info;
select null
from per_person_type_usages_f ptu
,per_person_types pet
where ptu.person_type_id = pet.person_type_id
and ptu.person_id = p_person_id
and l_effective_date between
ptu.effective_start_date and ptu.effective_end_date
and pet.system_person_type = 'PRTN';
select null
from per_contact_relationships ctr
where ctr.person_id = p_person_id
and ctr.contact_type = 'S'
and p_lf_evt_ocrd_dt
between nvl(ctr.date_start,hr_api.g_sot) and
nvl(ctr.date_end,hr_api.g_eot)
and ctr.business_group_id = p_business_group_id;
procedure update_dpnt_cobra_info
(p_per_in_ler_id in number
,p_person_id in number
,p_business_group_id in number
,p_effective_date in date
,p_prtt_enrt_rslt_id in number
,p_validate in boolean default false) is
--
l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
l_update boolean;
l_proc varchar2(80) := g_package||'.update_dpnt_cobra_info';
select cqb.*
from ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where cqb.quald_bnf_person_id = p_person_id
and nvl(cqb.cbr_elig_perd_end_dt,l_lf_evt_ocrd_dt) >= l_lf_evt_ocrd_dt
and cqb.pgm_id = l_pgm_id
and nvl(cqb.ptip_id,l_ptip_id) = l_ptip_id
and crp.cbr_quald_bnf_id = cqb.cbr_quald_bnf_id
and cqb.business_group_id = p_business_group_id
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.business_group_id = cqb.business_group_id
and pil.business_group_id = crp.business_group_id
and crp.init_evt_flag = 'Y'
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select pen.*
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_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;
select cqb.*, crp.per_in_ler_id
from ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where cqb.quald_bnf_person_id = l_enrld_person_id
and cqb.cbr_elig_perd_end_dt > l_lf_evt_ocrd_dt
and crp.cbr_quald_bnf_id = cqb.cbr_quald_bnf_id
and cqb.business_group_id = p_business_group_id
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.business_group_id = cqb.business_group_id
and pil.business_group_id = crp.business_group_id
and crp.init_evt_flag = 'Y'
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select pgm.poe_lvl_cd
from ben_pgm_f pgm
where pgm.pgm_id = l_pgm_id
and p_effective_date
between pgm.effective_start_date and pgm.effective_end_date
and pgm.business_group_id = p_business_group_id;
select null
from ben_ler_f ler
,ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.ler_id = ler.ler_id
and ler.name = 'Assistance Eligible Individual'
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date;
,p_update => l_update
);
,p_update => l_update
);
update_cobra_info
(p_per_in_ler_id => p_per_in_ler_id
,p_person_id => p_person_id
,p_cbr_quald_bnf_id => l_cqb_rec.cbr_quald_bnf_id
,p_cqb_object_version_number => l_cqb_rec.object_version_number
,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
,p_old_cbr_elig_perd_end_dt => l_cqb_rec.cbr_elig_perd_end_dt
,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
,p_dsbld_apls => l_dsbld_apls
,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
,p_quald_bnf_flag => l_quald_bnf_flag
,p_cvrd_emp_person_id => l_cvrd_emp_rec.cvrd_emp_person_id
,p_business_group_id => p_business_group_id
,p_pgm_id => l_pgm_id
,p_ptip_id => l_cqb_ptip_id
,p_pl_typ_id => l_pl_typ_id
,p_effective_date => p_effective_date
,p_validate => p_validate
);
,p_update => l_update
);
,p_update => l_update
);
update_cobra_info
(p_per_in_ler_id => p_per_in_ler_id
,p_person_id => p_person_id
,p_cbr_quald_bnf_id => l_cqb_rec.cbr_quald_bnf_id
,p_cqb_object_version_number => l_cqb_object_version_number
,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
,p_old_cbr_elig_perd_end_dt => l_cqb_rec.cbr_elig_perd_end_dt
,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
,p_dsbld_apls => l_dsbld_apls
,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
,p_cvrd_emp_person_id => l_cqb_rec.cvrd_emp_person_id
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
,p_validate => p_validate
);
,p_update => l_update
);
,p_update => l_update
);
update_cobra_info
(p_per_in_ler_id => p_per_in_ler_id
,p_person_id => p_person_id
,p_cbr_quald_bnf_id => null
,p_cqb_object_version_number => null
,p_cbr_elig_perd_strt_dt => l_cbr_elig_perd_strt_dt
,p_old_cbr_elig_perd_end_dt => l_cqb_rec.cbr_elig_perd_end_dt
,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
,p_dsbld_apls => l_dsbld_apls
,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
,p_quald_bnf_flag => 'Y'
,p_cvrd_emp_person_id => l_cvrd_emp_rec.cvrd_emp_person_id
,p_business_group_id => p_business_group_id
,p_pgm_id => l_pgm_id
,p_ptip_id => l_cqb_ptip_id
,p_pl_typ_id => l_pl_typ_id
,p_effective_date => p_effective_date
,p_validate => p_validate
);
end update_dpnt_cobra_info;
l_update boolean := false;
select cqb.*
from ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where cqb.quald_bnf_person_id = p_quald_bnf_person_id
and cqb.quald_bnf_flag = 'Y'
and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
and cqb.business_group_id = p_business_group_id
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.business_group_id = cqb.business_group_id
and pil.business_group_id = crp.business_group_id
and crp.init_evt_flag = 'Y'
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select distinct pdp.dpnt_person_id
from ben_elig_cvrd_dpnt_f pdp
where pdp.per_in_ler_id = p_per_in_ler_id
and p_lf_evt_ocrd_dt
between pdp.effective_start_date and pdp.effective_end_date
and pdp.cvg_thru_dt <> hr_api.g_eot
and pdp.business_group_id = p_business_group_id;
select crp.*
from ben_cbr_per_in_ler crp
where crp.per_in_ler_id = p_per_in_ler_id
and crp.init_evt_flag = 'Y'
and crp.business_group_id = p_business_group_id;
select null
from ben_ptnl_ler_for_per ppl
where ppl.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and ppl.person_id = p_person_id
and ppl.ptnl_ler_for_per_stat_cd in ('DTCTD', 'PROCD', 'UNPROCD')
and ppl.business_group_id = p_business_group_id;
update_cobra_info
(p_per_in_ler_id => p_per_in_ler_id
,p_person_id => l_cqb_rec.quald_bnf_person_id
,p_cbr_quald_bnf_id => l_cqb_rec.cbr_quald_bnf_id
,p_cqb_object_version_number => l_cqb_rec.object_version_number
,p_cbr_elig_perd_strt_dt => l_cqb_rec.cbr_elig_perd_strt_dt
,p_old_cbr_elig_perd_end_dt => l_cqb_rec.cbr_elig_perd_end_dt
,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
,p_cvrd_emp_person_id => l_cqb_rec.cvrd_emp_person_id
,p_cbr_inelg_rsn_cd => 'VEC' -- Voluntary end of coverage.
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
,p_validate => p_validate
);
update_cobra_info
(p_per_in_ler_id => p_per_in_ler_id
,p_person_id => l_cqb_rec.quald_bnf_person_id
,p_cbr_quald_bnf_id => l_cqb_rec.cbr_quald_bnf_id
,p_cqb_object_version_number => l_cqb_rec.object_version_number
,p_cbr_elig_perd_strt_dt => l_cqb_rec.cbr_elig_perd_strt_dt
,p_old_cbr_elig_perd_end_dt => l_cqb_rec.cbr_elig_perd_end_dt
,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
,p_cvrd_emp_person_id => l_cqb_rec.cvrd_emp_person_id
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
,p_validate => p_validate
);
procedure update_cobra_info
(p_per_in_ler_id in number
,p_person_id in number
,p_cbr_quald_bnf_id in number default null
,p_cqb_object_version_number in number default null
,p_cbr_elig_perd_strt_dt in date default null
,p_old_cbr_elig_perd_end_dt in date default null
,p_cbr_elig_perd_end_dt in date
,p_dsbld_apls in boolean default false
,p_lf_evt_ocrd_dt in date
,p_quald_bnf_flag in varchar2 default 'Y'
,p_cvrd_emp_person_id in number default null
,p_cbr_inelg_rsn_cd in varchar2 default hr_api.g_varchar2
,p_business_group_id in number
,p_effective_date in date
,p_pgm_id in number default null
,p_ptip_id in number default null
,p_pl_typ_id in number default null
,p_validate in boolean default false) is
--
l_effective_date ben_per_in_ler.lf_evt_ocrd_dt%type;
l_proc varchar2(80) := g_package||'.update_cobra_info';
l_update boolean;
select null
from per_person_type_usages_f ptu
,per_person_types pet
where ptu.person_type_id = pet.person_type_id
and ptu.person_id = p_person_id
and l_effective_date between
ptu.effective_start_date and ptu.effective_end_date
and pet.system_person_type = 'PRTN';
select ctr.person_id
from per_contact_relationships ctr
,per_person_type_usages_f ptu
,per_person_types pet
where ctr.contact_person_id = p_person_id
and p_lf_evt_ocrd_dt
between nvl(ctr.date_start,hr_api.g_sot) and
nvl(ctr.date_end,hr_api.g_eot)
and ctr.business_group_id = p_business_group_id
and ctr.person_id = ptu.person_id
and ptu.person_type_id = pet.person_type_id
and l_effective_date between
ptu.effective_start_date and ptu.effective_end_date
and pet.system_person_type = 'PRTN';
select null
from ben_ler_f ler
,ben_per_in_ler pil
where ler.ler_id = pil.ler_id
and pil.per_in_ler_id = p_per_in_ler_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date
and pil.business_group_id = p_business_group_id
and pil.business_group_id = ler.business_group_id
and ler.qualg_evt_flag = 'Y';
select crp.*
from ben_cbr_per_in_ler crp
where (crp.per_in_ler_id = p_per_in_ler_id
or (crp.per_in_ler_id
in (select distinct crp2.per_in_ler_id
from ben_cbr_per_in_ler crp2
,ben_cbr_quald_bnf cqb
,ben_per_in_ler pil
where cqb.cvrd_emp_person_id = p_cvrd_emp_person_id
and cqb.cbr_quald_bnf_id = crp2.cbr_quald_bnf_id
and crp2.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and cqb.business_group_id = p_business_group_id
and crp2.business_group_id = cqb.business_group_id
and pil.business_group_id = crp2.business_group_id)))
and crp.business_group_id = p_business_group_id
and crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id;
select max(crp.cnt_num)
from ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
and crp.business_group_id = p_business_group_id
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.business_group_id = pil.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select cqb.*
from ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where cqb.cvrd_emp_person_id = l_cvrd_emp_person_id
and cqb.quald_bnf_person_id <> p_person_id
and cqb.quald_bnf_flag = 'Y'
and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
and cqb.business_group_id = p_business_group_id
and crp.business_group_id = cqb.business_group_id
and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
and crp.per_in_ler_id = pil.per_in_ler_id
and cqb.pgm_id = nvl(p_pgm_id, cqb.pgm_id)
and nvl(cqb.ptip_id,-1) = nvl(p_ptip_id, -1)
and crp.business_group_id = pil.business_group_id
and crp.init_evt_flag = 'Y'
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select cbr_elig_perd_end_dt
from ben_cbr_quald_bnf cqb
where cqb.cvrd_emp_person_id = l_cvrd_emp_person_id
and cqb.quald_bnf_person_id = cqb.cvrd_emp_person_id
and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
and cqb.business_group_id = p_business_group_id
and cqb.pgm_id = nvl(p_pgm_id, cqb.pgm_id)
and nvl(cqb.ptip_id,-1) = nvl(p_ptip_id, -1);
select crp.*
from ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where cqb.cvrd_emp_person_id = p_cvrd_emp_person_id
and cqb.quald_bnf_person_id = cqb.cvrd_emp_person_id
and cqb.pgm_id = nvl(p_pgm_id, cqb.pgm_id)
and nvl(cqb.ptip_id,-1) = nvl(p_ptip_id, -1)
and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
and cqb.business_group_id = p_business_group_id
and crp.init_evt_flag = 'Y'
and crp.business_group_id = cqb.business_group_id
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.business_group_id = pil.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select null
from ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where cqb.quald_bnf_person_id = p_person_id
and cqb.quald_bnf_flag = 'Y'
and cqb.cbr_elig_perd_end_dt >= p_lf_evt_ocrd_dt
and cqb.business_group_id = p_business_group_id
and crp.business_group_id = cqb.business_group_id
and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.per_in_ler_id <> p_per_in_ler_id
and cqb.pgm_id = nvl(p_pgm_id, cqb.pgm_id)
and nvl(cqb.ptip_id,-1) = nvl(p_ptip_id, -1)
and crp.business_group_id = pil.business_group_id
and crp.init_evt_flag = 'Y'
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select cqb.*
from ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where cqb.quald_bnf_person_id = p_person_id
and cqb.business_group_id = p_business_group_id
/* and p_lf_evt_ocrd_dt
between cqb.cbr_elig_perd_strt_dt
and cqb.cbr_elig_perd_end_dt */
and cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
and crp.init_evt_flag = 'Y'
-- and cqb.quald_bnf_flag = 'N'
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.business_group_id = cqb.business_group_id
and crp.business_group_id = pil.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
order by cqb.cbr_elig_perd_strt_dt desc
;
select ctr.person_id
from per_contact_relationships ctr
where ctr.contact_person_id = p_person_id
and p_lf_evt_ocrd_dt
between nvl(ctr.date_start,hr_api.g_sot) and
nvl(ctr.date_end,hr_api.g_eot)
and ctr.business_group_id = p_business_group_id;
hr_utility.set_location('Inserting quald bnf: ' || l_proc, 10);
ben_cbr_quald_bnf_api.update_cbr_quald_bnf
(p_validate => p_validate
,p_cbr_quald_bnf_id => l_dpnt_rec.cbr_quald_bnf_id
,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
);
hr_utility.set_location('l_cbr_elig_perd_end_dt update: '||l_cbr_elig_perd_end_dt, 10);
ben_cbr_quald_bnf_api.update_cbr_quald_bnf
(p_validate => p_validate
,p_cbr_quald_bnf_id => p_cbr_quald_bnf_id
,p_quald_bnf_flag => l_quald_bnf_flag
,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
,p_cbr_inelg_rsn_cd => p_cbr_inelg_rsn_cd
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
);
ben_cbr_quald_bnf_api.update_cbr_quald_bnf
(p_validate => p_validate
,p_cbr_quald_bnf_id => l_dpnt_rec.cbr_quald_bnf_id
,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
);
end update_cobra_info;
l_update boolean := false;
select cqb.*
from ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where cqb.quald_bnf_person_id = p_person_id
and cqb.quald_bnf_flag = 'Y'
and cqb.cbr_elig_perd_end_dt >= l_effective_date
and crp.cbr_quald_bnf_id = cqb.cbr_quald_bnf_id
and cqb.business_group_id = p_business_group_id
and crp.init_evt_flag = 'Y'
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.business_group_id = cqb.business_group_id
and pil.business_group_id = crp.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select cqb.*
from ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where cqb.quald_bnf_person_id = p_person_id
and cqb.quald_bnf_flag = 'Y'
and cqb.cbr_elig_perd_end_dt >= l_effective_date
and crp.cbr_quald_bnf_id = cqb.cbr_quald_bnf_id
and cqb.business_group_id = p_business_group_id
and crp.init_evt_flag = 'Y'
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.business_group_id = cqb.business_group_id
and pil.business_group_id = crp.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select max(crp.cnt_num)
from ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where crp.cbr_quald_bnf_id = l_cbr_quald_bnf_id
and crp.business_group_id = p_business_group_id
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.business_group_id = pil.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select ler.typ_cd
from ben_ler_f ler
,ben_per_in_ler pil
where ler.ler_id = pil.ler_id
and pil.per_in_ler_id = p_per_in_ler_id
and ler.business_group_id = p_business_group_id
and ler.business_group_id = pil.business_group_id
and p_effective_date
between ler.effective_start_date and
ler.effective_end_date;
select null
from ben_elig_per_elctbl_chc chc
where chc.pgm_id = p_pgm_id
and chc.ptip_id = nvl(p_ptip_id, chc.ptip_id)
and chc.elctbl_flag = 'Y'
and chc.per_in_ler_id = p_per_in_ler_id
and chc.business_group_id = p_business_group_id;
select distinct pdp.dpnt_person_id
from ben_elig_cvrd_dpnt_f pdp
where pdp.per_in_ler_id = p_per_in_ler_id
and l_lf_evt_ocrd_dt
between pdp.effective_start_date and pdp.effective_end_date
and pdp.cvg_thru_dt <> hr_api.g_eot
and pdp.business_group_id = p_business_group_id;
update_cobra_info
(p_per_in_ler_id => p_per_in_ler_id
,p_person_id => l_dpnt_cqb_rec.quald_bnf_person_id
,p_cbr_quald_bnf_id => l_dpnt_cqb_rec.cbr_quald_bnf_id
,p_cqb_object_version_number => l_dpnt_cqb_rec.object_version_number
,p_cbr_elig_perd_strt_dt => l_dpnt_cqb_rec.cbr_elig_perd_strt_dt
,p_old_cbr_elig_perd_end_dt => l_dpnt_cqb_rec.cbr_elig_perd_end_dt
,p_cbr_elig_perd_end_dt => l_cbr_elig_perd_end_dt
,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
,p_cvrd_emp_person_id => l_dpnt_cqb_rec.cvrd_emp_person_id
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
,p_validate => p_validate
);
select max(crp.cnt_num)
from ben_cbr_per_in_ler crp
,ben_per_in_ler pil
where crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
and crp.business_group_id = p_business_group_id
and crp.per_in_ler_id = pil.per_in_ler_id
and crp.business_group_id = pil.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select null
from ben_cbr_per_in_ler crp
where crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
and crp.per_in_ler_id = p_per_in_ler_id;
ben_cbr_quald_bnf_api.update_cbr_quald_bnf
(p_validate => p_validate
,p_cbr_quald_bnf_id => p_cbr_quald_bnf_id
,p_quald_bnf_flag => p_quald_bnf_flag
,p_cbr_elig_perd_end_dt => p_cbr_elig_perd_end_dt
,p_cbr_inelg_rsn_cd => p_cbr_inelg_rsn_cd
,p_business_group_id => p_business_group_id
,p_object_version_number => l_cqb_object_version_number
,p_effective_date => p_effective_date
);
select pen.prtt_enrt_rslt_id,
pen.pl_id,
opt.opt_id,
pen.pgm_id,
pen.ler_id,
pen.pl_typ_id,
pen.person_id,
pen.effective_start_date,
pen.effective_end_date
from ben_prtt_enrt_rslt_f pen,
ben_oipl_f opt
where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and opt.oipl_id(+)=pen.oipl_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_strt_dt between opt.effective_start_date(+)
and opt.effective_end_date(+)
order by pen.effective_start_date desc;
select abr.prtl_mo_det_mthd_cd,
abr.prtl_mo_det_mthd_rl,
abr.wsh_rl_dy_mo_num,
abr.prtl_mo_eff_dt_det_cd,
abr.prtl_mo_eff_dt_det_rl,
abr.rndg_cd,
abr.rndg_rl,
abr.ele_rqd_flag,
abr.one_ann_pymt_cd,
abr.entr_ann_val_flag,
abr.use_calc_acty_bs_rt_flag,
abr.acty_typ_cd,
abr.input_va_calc_rl,
abr.rt_typ_cd,
abr.element_type_id,
abr.input_value_id,
abr.ele_entry_val_cd,
abr.rt_mlt_cd,
abr.parnt_chld_cd,
abr.rcrrg_cd,
abr.name
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id=c_acty_base_rt_id
and c_effective_date between abr.effective_start_date
and abr.effective_end_date;
select abr2.rt_mlt_cd,
abr2.entr_ann_val_flag
from ben_acty_base_rt_f abr,
ben_acty_base_rt_f abr2
where abr.acty_base_rt_id = c_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 distinct
yp.start_date,yp.end_date
from ben_prtt_enrt_rslt_f pen,
ben_popl_yr_perd pyp,
ben_yr_perd yp
where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and c_effective_date <= pen.effective_end_date
and pyp.pl_id=pen.pl_id
and yp.yr_perd_id=pyp.yr_perd_id
and pen.prtt_enrt_rslt_stat_cd is null
and c_rate_start_or_end_dt
between yp.start_date and yp.end_date;
SELECT prv.rt_strt_dt
,prv.rt_end_dt
,prv.ann_rt_val
FROM ben_prtt_enrt_rslt_f pen
,ben_prtt_rt_val prv
WHERE pen.person_id = c_person_id
AND pen.pgm_id = c_pgm_id
AND pen.business_group_id = c_business_group_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.sspndd_flag = 'N'
AND pen.effective_end_date = hr_api.g_eot
AND pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
AND pen.business_group_id = prv.business_group_id
AND prv.prtt_rt_val_stat_cd IS NULL
AND prv.acty_base_rt_id = c_acty_base_rt_id
AND prv.rt_strt_dt < c_cur_rt_strt_dt
AND prv.rt_end_dt >= c_plan_year_strt_dt
ORDER BY prv.rt_strt_dt;
SELECT NVL(sum(a.result_value),0) result_value
FROM pay_run_result_values a
,pay_element_types_f b
,pay_assignment_actions d
,pay_payroll_actions e
,pay_run_results h
,ben_acty_base_rt_f i
,pay_input_values_f j
WHERE d.assignment_id = c_assignment_id
AND d.payroll_action_id = e.payroll_action_id
AND i.input_value_id = j.input_value_id
AND i.element_type_id = b.element_type_id
AND i.acty_base_rt_id = c_acty_base_rt_id
AND c_effective_date BETWEEN i.effective_start_date
AND i.effective_end_date
AND i.business_group_id = c_business_group_id
AND b.element_type_id = h.element_type_id
AND d.assignment_action_id = h.assignment_action_id
AND e.date_earned BETWEEN c_strt_dt AND c_end_dt
AND a.input_value_id = j.input_value_id
AND a.run_result_id = h.run_result_id
AND j.element_type_id = b.element_type_id
AND c_effective_date BETWEEN b.effective_start_date
AND b.effective_end_date
AND c_effective_date BETWEEN j.effective_start_date
AND j.effective_end_date;
select pln.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 opt.name
from ben_oipl_f cop,
ben_opt_f opt
where cop.oipl_id = c_oipl_id
and cop.opt_id = opt.opt_id
and c_effective_date between cop.effective_start_date
and cop.effective_end_date
and c_effective_date between opt.effective_start_date
and opt.effective_end_date;
SELECT pen.pl_id
,prv.element_entry_value_id
,prv.acty_base_rt_id
,prv.rt_strt_dt
,prv.rt_end_dt
,prv.ann_rt_val
,pen.prtt_enrt_rslt_id
,prv.mlt_cd
FROM ben_prtt_enrt_rslt_f pen
,ben_prtt_rt_val prv
,ben_acty_base_rt_f abr
WHERE pen.person_id = c_person_id
AND pen.pgm_id = c_pgm_id
AND pen.pl_typ_id = c_pl_typ_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.sspndd_flag = 'N'
AND pen.business_group_id = c_business_group_id
AND pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
AND prv.business_group_id = pen.business_group_id
AND prv.prtt_rt_val_stat_cd IS NULL
AND prv.acty_typ_cd LIKE 'PBC%'
AND pen.effective_end_date = hr_api.g_eot
AND c_effective_date BETWEEN prv.rt_strt_dt and
prv.rt_end_dt
AND prv.acty_base_rt_id = abr.acty_base_rt_id
AND abr.element_type_id +0 = c_element_type_id -- PERF FIX. Added +0
AND abr.input_value_id +0= c_input_value_id -- PERF FIX. Added +0
AND c_effective_date BETWEEN abr.effective_start_date
and abr.effective_end_date;
SELECT pen.pl_id
,pen.oipl_id
,prv.element_entry_value_id
,prv.acty_base_rt_id
,prv.rt_strt_dt
,prv.rt_end_dt
,prv.ann_rt_val
,pen.prtt_enrt_rslt_id
,prv.mlt_cd
,abr.name
FROM ben_prtt_enrt_rslt_f pen
,ben_prtt_rt_val prv
,ben_acty_base_rt_f abr
WHERE pen.person_id = c_person_id
AND pen.pgm_id = c_pgm_id
AND pen.pl_typ_id = c_pl_typ_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.sspndd_flag = 'N'
AND pen.business_group_id = c_business_group_id
AND pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
AND prv.business_group_id = pen.business_group_id
AND prv.prtt_rt_val_stat_cd IS NULL
AND prv.acty_typ_cd LIKE 'PBC%'
AND pen.effective_end_date = hr_api.g_eot
AND c_effective_date BETWEEN prv.rt_strt_dt and
prv.rt_end_dt
AND prv.acty_base_rt_id = abr.acty_base_rt_id
AND ((abr.element_type_id <> c_element_type_id) OR (abr.input_value_id <> c_input_value_id))
AND c_effective_date BETWEEN abr.effective_start_date
and abr.effective_end_date
ORDER by pen.pl_id,pen.oipl_id;
select pr.consolidation_set_id
from pay_all_payrolls_f pr
where pr.payroll_id = c_payroll_id
and c_effective_date between pr.effective_start_date
and pr.effective_end_date;
select entries.balance_adj_cost_flag
from ben_acty_base_rt_f abr,
pay_element_entry_values_f ee_values,
pay_element_entries_f entries
where abr.acty_base_rt_id = c_acty_base_rt_id
and c_effective_date between abr.effective_start_date
and abr.effective_end_date
and abr.business_group_id = c_business_group_id
and abr.input_value_id = ee_values.input_value_id
and c_effective_date between ee_values.effective_start_date
and ee_values.effective_end_date
and ee_values.element_entry_id = entries.element_entry_id
and c_effective_date between entries.effective_start_date
and entries.effective_end_date;
select elk.costable_type,
link_inp_val.element_link_id,
abr.input_value_id
from ben_acty_base_rt_f abr,
pay_input_values_f inp_val,
pay_element_links_f elk,
pay_link_input_values_f link_inp_val
where acty_base_rt_id = c_acty_base_rt_id
and abr.input_value_id = inp_val.input_value_id
and c_effective_date between abr.effective_start_date
and abr.effective_end_date
and abr.business_group_id = c_business_group_id
and c_effective_date between inp_val.effective_start_date
and inp_val.effective_end_date
and inp_val.business_group_id = c_business_group_id
and link_inp_val.input_value_id = inp_val.input_value_id
and elk.element_link_id = link_inp_val.element_link_id
and c_effective_date between elk.effective_start_date
and elk.effective_end_date
and c_effective_date between link_inp_val.effective_start_date
and link_inp_val.effective_end_date;
SELECT pen.pgm_id
,pen.pl_typ_id
,abr.element_type_id
,abr.input_value_id
FROM ben_prtt_enrt_rslt_f pen
,ben_prtt_rt_val prv
,ben_acty_base_rt_f abr
WHERE pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
AND c_effective_date BETWEEN pen.enrt_cvg_strt_dt
AND pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_thru_dt <= pen.effective_end_date
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.sspndd_flag = 'N'
AND pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
AND prv.acty_base_rt_id = c_acty_base_rt_id
AND c_effective_date BETWEEN prv.rt_strt_dt and
prv.rt_end_dt
AND prv.acty_base_rt_id = abr.acty_base_rt_id
AND c_effective_date BETWEEN abr.effective_start_date
and abr.effective_end_date;
SELECT NVL(pln.cobra_pymt_due_dy_num,1)
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 NVL(sum(a.result_value),0) result_value
FROM pay_run_result_values a
,pay_element_types_f b
,pay_assignment_actions d
,pay_payroll_actions e
,pay_run_results h
,ben_acty_base_rt_f i
,pay_input_values_f j
WHERE d.assignment_id = c_assignment_id
AND d.payroll_action_id = e.payroll_action_id
AND i.input_value_id = j.input_value_id
AND i.element_type_id = b.element_type_id
AND i.acty_base_rt_id = c_acty_base_rt_id
AND c_effective_date BETWEEN i.effective_start_date
AND i.effective_end_date
AND i.business_group_id = c_business_group_id
AND b.element_type_id = h.element_type_id
AND d.assignment_action_id = h.assignment_action_id
AND e.date_earned BETWEEN c_strt_dt AND c_end_dt
AND a.input_value_id = j.input_value_id
AND a.run_result_id = h.run_result_id
AND j.element_type_id = b.element_type_id
AND c_effective_date BETWEEN b.effective_start_date
AND b.effective_end_date
AND c_effective_date BETWEEN j.effective_start_date
AND j.effective_end_date;
SELECT NVL(sum(a.result_value),0) result_value
,LAST_DAY(e.date_earned) month_end_dt
FROM pay_run_result_values a
,pay_element_types_f b
,pay_assignment_actions d
,pay_payroll_actions e
,pay_run_results h
,ben_acty_base_rt_f i
,pay_input_values_f j
WHERE d.assignment_id = c_assignment_id
AND d.payroll_action_id = e.payroll_action_id
AND i.input_value_id = j.input_value_id
AND i.element_type_id = b.element_type_id
AND i.acty_base_rt_id = c_acty_base_rt_id
AND c_effective_date BETWEEN i.effective_start_date
AND i.effective_end_date
AND i.business_group_id = c_business_group_id
AND b.element_type_id = h.element_type_id
AND d.assignment_action_id = h.assignment_action_id
AND e.date_earned BETWEEN c_strt_dt AND c_end_dt
AND a.input_value_id = j.input_value_id
AND a.run_result_id = h.run_result_id
AND j.element_type_id = b.element_type_id
AND c_effective_date BETWEEN b.effective_start_date
AND b.effective_end_date
AND c_effective_date BETWEEN j.effective_start_date
AND j.effective_end_date
group by LAST_DAY(e.date_earned)
order by LAST_DAY(e.date_earned) desc;
SELECT NVL(sum(a.result_value),0) result_value
,LAST_DAY(e.date_earned) month_end_dt
FROM pay_run_result_values a
,pay_element_types_f b
,pay_assignment_actions d
,pay_payroll_actions e
,pay_run_results h
,ben_acty_base_rt_f i
,pay_input_values_f j
WHERE d.assignment_id = c_assignment_id
AND d.payroll_action_id = e.payroll_action_id
AND i.input_value_id = j.input_value_id
AND i.element_type_id = b.element_type_id
AND i.acty_base_rt_id = c_acty_base_rt_id
AND c_effective_date BETWEEN i.effective_start_date
AND i.effective_end_date
AND i.business_group_id = c_business_group_id
AND b.element_type_id = h.element_type_id
AND d.assignment_action_id = h.assignment_action_id
AND a.input_value_id = j.input_value_id
AND a.run_result_id = h.run_result_id
AND j.element_type_id = b.element_type_id
AND c_effective_date BETWEEN b.effective_start_date
AND b.effective_end_date
AND c_effective_date BETWEEN j.effective_start_date
AND j.effective_end_date
AND e.date_earned between c_rt_strt_dt and c_rt_end_dt
group by LAST_DAY(e.date_earned)
having NVL(sum(a.result_value),0) = c_per_month_amt
order by LAST_DAY(e.date_earned) desc;
SELECT pen.pl_id
,prv.element_entry_value_id
,prv.acty_base_rt_id
,prv.rt_strt_dt
,prv.rt_end_dt
,prv.ann_rt_val
,pen.prtt_enrt_rslt_id
,prv.mlt_cd
FROM ben_prtt_enrt_rslt_f pen
,ben_prtt_rt_val prv
,ben_acty_base_rt_f abr
WHERE pen.person_id = c_person_id
AND pen.pgm_id = c_pgm_id
AND pen.pl_typ_id = c_pl_typ_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.sspndd_flag = 'N'
AND pen.business_group_id = c_business_group_id
AND pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
AND prv.business_group_id = pen.business_group_id
AND prv.prtt_rt_val_stat_cd IS NULL
AND prv.acty_typ_cd LIKE 'PBC%'
AND pen.effective_end_date = hr_api.g_eot
AND prv.rt_strt_dt >= c_effective_date
AND prv.acty_base_rt_id = abr.acty_base_rt_id
AND abr.element_type_id +0= c_element_type_id -- PERF FIX. Added +0
AND abr.input_value_id +0= c_input_value_id -- PERF FIX. Added +0
AND c_effective_date BETWEEN abr.effective_start_date
and abr.effective_end_date
ORDER BY prv.rt_strt_dt;
SELECT pen.pl_id
,prv.element_entry_value_id
,prv.acty_base_rt_id
,prv.rt_strt_dt
,prv.rt_end_dt
,prv.ann_rt_val
,pen.prtt_enrt_rslt_id
,prv.mlt_cd
FROM ben_prtt_enrt_rslt_f pen
,ben_prtt_rt_val prv
,ben_acty_base_rt_f abr
WHERE pen.person_id = c_person_id
AND pen.pgm_id = c_pgm_id
AND pen.pl_typ_id = c_pl_typ_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.sspndd_flag = 'N'
AND pen.business_group_id = c_business_group_id
AND pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
AND prv.business_group_id = pen.business_group_id
AND prv.prtt_rt_val_stat_cd IS NULL
AND prv.acty_typ_cd LIKE 'PBC%'
AND pen.effective_end_date = hr_api.g_eot
AND prv.acty_base_rt_id = abr.acty_base_rt_id
AND abr.element_type_id = c_element_type_id
AND abr.input_value_id = c_input_value_id
AND prv.rt_strt_dt BETWEEN abr.effective_start_date
and abr.effective_end_date
ORDER BY prv.rt_strt_dt desc;
select distinct
yp.start_date,yp.end_date
from ben_prtt_enrt_rslt_f pen,
ben_popl_yr_perd pyp,
ben_yr_perd yp
where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and c_effective_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
and pyp.pl_id=pen.pl_id
and yp.yr_perd_id=pyp.yr_perd_id
and c_effective_date between yp.start_date and yp.end_date;