[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
dt_cd/rl) in the call to update
api on elec_chc table. Make
changes in the hirerchy of
picking of cvg strt date/rl.
11 Sep 98 maagrawa 115.4 Eligibility to be checked for
non-electable choices also.
30-OCT-98 G PERRY 115.5 Removed Control m
10-DEC-98 lmcdonal 115.6 Do not load dpnt cvg_strt_dt
when creating dpnt records that are
tied only to a choice and not to a
result. Bug 1536.
15-DEC-98 stee 115.7 Remove dsgn rqmt check as it
is checked in ben_evaluate_dpnt
-elg_profiles.
20-DEC-98 G PERRY 115.8 Added in info to cache about the
covered dependent.
22-DEC-98 stee 115.9 If participant has a qmcso, set
electable_flag to 'N' for choices
that do not allow dependents.
28-DEC-98 stee 115.10 Fix c_contact cursor to select
the contacts within a date range.
02-JAN-99 stee 115.11 Added p_multi_row_actn of false
to update_elig_cvrd_dpnt api.
18 Jan 99 G Perry 115.12 LED V ED
05 Apr 99 mhoyes 115.13 - Un-datetrack of per_in_ler_f changes.
- Removed DT restriction from
- main/c_per_in_ler
30 Apr 99 shdas 115.15 Added contexts to rule calls(genutils.formula)
Added a rule call for enrt_cvg_strt_dt_rl.
08 May 99 jcarpent 115.16 Check ('VOIDD', 'BCKDT') for pil stat cd
06 Jun 99 stee 115.17 Check for datetrack mode before
updating dependent.
28 Jul 99 tmathers 115.18 genutils to benutils.
06 Aug 99 mhoyes 115.19 - Added new trace messages.
10 Aug 99 gperry 115.20 Removed global references to
g_cache_person.
24 Aug 99 maagrawa 115.21 Changes related to breaking of
dependents table.
26 Aug 99 gperry 115.22 Added benefits assignment call for
when employee assignment is null.
30 Aug 99 maagrawa 115.23 Added p_dpnt_inelig_rsn_cd to
Dependent eligibility process to
return ineligible reason.
02 Sep 99 maagrawa 115.24 Added HIPAA communication.
07 Sep 99 jcarpent 115.25 Removed qmcso check.
11 Oct 99 jcarpent 115.26 Added bg_id to update_elig_cvrd_dpn
19 Nov 99 pbodla 115.27 Passed p_elig_per_elctbl_chc_id
to formula
30 Nov 99 lmcdonal 115.28 add per_all_people_f to c_contact
cursor to avoid error in bendpelg.
03 Jan 00 maagrawa 115.29 Added pl_typ_id to comm. process.
07 Jan 00 pbodla 115.30 oabrules.xls says the ler_chg_dpnt_cvg_rl
should return Y/N.
18 Jan 00 maagrawa 115.31 Removed HIPAA comm. Now called
from benmngle.
12 Feb 00 maagrawa 115.32 Use the elig_change_dt to
calculate cvg_end_dt (1193065).
17 Feb 00 jcarpent 115.33 change c_contact cursor to also
include covered even if ended.
22 Feb 00 jcarpent 115.34 Added hierarchy for ben_dsgn_rqmt
10 Mar 00 jcarpent 115.35 Pass cvg_end_dt to update_elig_dpnt
31 mar 00 maagrawa 115.36 Pass the cvg_strt_dt to dpnt.
eligibilty process (4929).
01 Apr 00 stee 115.36 Set a global variable when
dependent is found ineligible
so a benefit assignment can
be written.
05 Apr 00 mmogel 115.38 Added tokens to message calls to
make the messages more meaningful
11 Apr 00 jcarpent 115.39 Handle the case when bendsgel has
already end dated the elig covered
dependent in the future (5076)
25 Apr 00 mhoyes 115.40 - Added trace messages for profiling.
01 May 00 pbodla 115.41 - Task 131 : Elig dependent rows are
created before creating the electable
choice rows. Added procedures main() -
created the elig dependent rows,
p_upd_egd_with_epe_id()- updates elig
dependent rows with electable choice
rows.
13 May 00 mhoyes 115.42 - Added trace messages for profiling.
14 May 00 lmcdonal 115.43 Bug 1193065. Use life event occurred
date for elig_strt_dt.
15 May 00 mhoyes 115.44 - Called performance API.
22 May 00 jcarpent 115.45 - Create elig_dpnts when mnanrd so
that they can be carried over by
the system. (4981).
23 May 00 stee 115.46 - Do not update coverage end date
if the dependent was previously
ineligible.
24 May 00 jcarpent 115.47 - Fix for bug introduced in 115.45
29 May 00 mhoyes 115.48 - Called update performance covers
for EPEs and EGDs.
15 Jun 00 pbodla 115.49 - Removed old main(). as Martin looked
at it for performance reasons.
- Task 133 : Interim coverage cannot
be assigned when in insufficient number
of dependents have been designated
to a participant enrollment. In this case,
the current product will end the
participant's current coverage.
To have above functionality, if no
dependent designation is required if
l_mn_dpnts_rqd_num is 0 or
l_no_mn_num_dfnd_flag is Y.
28 Jun 00 mhoyes 115.50 - Called update performance covers
for EPE.
- Stored sysdate to local to reduce
sysdate references.
09 Aug 00 maagrawa 115.51 - If plan/option has decline flag ON,
do not create elig_dpnt records.
14 Aug 00 gperry 115.52 Fixed WWBUG 1375474.
Passed in more contexts to
ben_determine_date call.
30 Aug 00 tilak 115.53 bug:1390107 region_2 is validate before calling pay_mag_util.
lokup_jurisdiction_code
07 Nov 00 mhoyes 115.54 - Phased out c_elctbl_chc. Referenced
comp object loop electable choice context
global.
21 NOV 00 rchase 115.55 - Bug 1500945. Update elig_dpnt row
when coverage is lost
due to ineligibility. Leapfrog
based on ver 115.53.
21 NOV 00 jcarpent 115.56 - Merged version of 115.54 and 115.55.
12 DEC 00 jcarpent 115.57 - Bug 1524099, When elig_dpnt row was
not found was assuming eligible.
Added nvl(..dpnt_inelig_flag,'N').
26 DEC 00 Ikasire 115.58 Bug 1531647 added input values for contact_person_id
in the call to ben_determine_date.main procedure
05 Jan 01 kmahendr 115.59 Added per_in_ler_id column in the cursor to fetch active
life event
27 Aug 01 tilak 115.61 bug:1949361 jurisdiction code is
derived inside benutils.formula.
18 nov 01 tjesumic 115.62 cwb changes
20 dec 01 ikasire 115.63 added dbdrv lines
22 jan 02 mhoyes 115.64 - Added p_per_in_ler_id and p_opt_id to
call to get_elig_dpnt_rec.
23 Jan 02 ikasire 115.65 Bug 2189561 dependent eligility profile
not recognised when attached at plan level
24 jan 02 tjesumic 115.66 2147682 - fixed by jc
11 mar 02 mhoyes 115.67 - Tuned get_elig_dpnt_rec.
- Modified c_per_in_ler changed alias from
pil. to ler.
12 jun 02 mhoyes 115.68 - Performance tuning. Bypassed call to
get_elig_per_id.
31 jul 02 hnarayan 115.69 - bug 1192368 - modified designation level determination
in procedure main to first check if it is defined at
plan level, then check at program level.
03 Oct 02 tjesumic 115.70 # 2508745 To validate the PTIP level the plan type id
is validated instead of PTIP id , this allow to validate
across the porgram
25 Nov-02 tjesumic 115.71 fix of the # 2508745 reverted
10 Feb-03 pbodla 115.72 Filter GRADE/STEP life events.
18-Mar-04 kmahendr 115.73 bug#3495592 - cursor c_dpnt_exist added to
check already created elig_dpnt record
27-jul-04 nhunur 115.74 3685120-create elig_dpnt records for unrestricted.
03-Aug-04 tjesumic 115.75 3784375 - cursor c_pdp changed to validate the eot
03-Aug-04 tjesumic 115.76 fonm changes
16-Aug-04 kmahendr 115.77 Bug#3238951 - assign O to dpnt_dsgn_cd if the
plan is waive and reversed the fix made for
bug#1192368
17-Sep-04 pbodla 115.78 iREC : Avoid fetching the iRecruitement
life events.
15-Nov-04 kmahendr 115.79 Unrest. enh changes
14-dec-04 nhunur 115.80 4051409 - Reset variable l_elig_dpnt_id to allow creation
of elig_dpnt rows for multiple contacts.
07-Apr-05 abparekh 115. 81 Bug 4287999 : Fixed cursor c_contact not to select
deceased persons
12-May-05 nhunur 115. 82 Bug 4366892 : Fixed cursor c_contact not to select
one contact more than once if he has more than 1
personal relationship.
03-Jun-05 kmahendr 115.84 Added additional conditions to cursor c_contact
to avoid regression
03-Oct-05 kmahendr 115.85 Added mode_cd R to create elig_dpnt rows
24-Oct-05 kmahendr 115.86 Bug#4658173 - effective_date is arrived
based on pdp row
26-Oct-05 bmanyam 115.87 Bug 4692782 - changed c_contact cursor.
16-Dec-05 bmanyam 115.88 4697057 - Relationship End-date should
be end-date of dependent coverage.
04-Feb-06 mhoyes 115.89 - bug4966769 - hr_utility tuning.
04-Apr-06 vborkar 115.90 Bug 5127698 : Changes in cursors c_pdp,
c_plan_enrolment_info and c_oipl_enrolment_info.
17-Apr-06 bmanyam 115.91 5152062 - Changed c_dsgn, such that
all desgn.requirement are summed up
at a particular level (PL/OIPL/OPT).
26-May-06 bmanyam 115.92 5100008 - EGD elig_thru_dt is the
date eligibility is lost. Previously the elig_thru_dt
was updated with PDP cvg_thru_dt.
28-Jun-06 swjain 115.94 5331889 - Added person_id as param in call to
benutils.formula for ler_chg_dpnt_cvg_rl in procedure main
15-Sep-06 rgajula 115.95 Bug 5529902: In the cursor c_dsgn
if mx_dpnts_alwd_num is NULL, consider unlimited designees can be assigned.
02-Apr-07 swjain 115.96 Bug 5936849 : Set L_ENV.MODE_CD using benutils
if not available through benenvir
01-Oct-09 krupani 115.97 Bug 7481099: Corrected the fix done against bug 4287999
08-Mar-10 sagnanas 115.98 Bug 9443647 - Corrected the fix done against bug 4287999
*/
--------------------------------------------------------------------------------
g_rec benutils.g_batch_dpnt_rec;
l_update boolean;
l_update_override boolean;
l_update_change_insert boolean;
select opt_id from ben_oipl_f oipl
where oipl.oipl_id = p_oipl_id
and oipl.business_group_id = p_business_group_id
and l_lf_evt_ocrd_dt
between oipl.effective_start_date
and oipl.effective_end_date;
select pl_typ_id
from ben_ptip_f ptip
where p_ptip_id = ptip.ptip_id
and l_lf_evt_ocrd_dt
between ptip.effective_start_date
and ptip.effective_end_date ;
select pl.dpnt_dsgn_cd,
pl.dpnt_cvg_strt_dt_cd,
pl.dpnt_cvg_strt_dt_rl,
pl.dpnt_cvg_end_dt_cd,
pl.dpnt_cvg_end_dt_rl
from ben_pl_f pl
where pl.pl_id = p_pl_id
and pl.business_group_id = p_business_group_id
and l_lf_evt_ocrd_dt
between pl.effective_start_date
and pl.effective_end_date;
select pgm.dpnt_dsgn_lvl_cd,
pgm.dpnt_dsgn_cd,
pgm.dpnt_cvg_strt_dt_cd,
pgm.dpnt_cvg_strt_dt_rl,
pgm.dpnt_cvg_end_dt_cd,
pgm.dpnt_cvg_end_dt_rl
from ben_pgm_f pgm
where pgm.pgm_id = p_pgm_id
and pgm.business_group_id = p_business_group_id
and l_lf_evt_ocrd_dt
between pgm.effective_start_date
and pgm.effective_end_date;
select ptip.dpnt_dsgn_cd,
ptip.dpnt_cvg_strt_dt_cd,
ptip.dpnt_cvg_strt_dt_rl,
ptip.dpnt_cvg_end_dt_cd,
ptip.dpnt_cvg_end_dt_rl
from ben_ptip_f ptip
where ptip.ptip_id = p_ptip_id
and ptip.business_group_id = p_business_group_id
and l_lf_evt_ocrd_dt
between ptip.effective_start_date
and ptip.effective_end_date;
select pil.ler_id,
pil.person_id,
pil.lf_evt_ocrd_dt,
pil.per_in_ler_id
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = p_person_id
--and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd = 'STRTD'
---cwb changes
and ler.ler_id = pil.ler_id
and ler.typ_cd not in ('COMP', 'GSP', 'IREC')
and l_lf_evt_ocrd_dt
between ler.effective_start_date
and ler.effective_end_date
-- unrestricted enhancement
and pil.per_in_ler_id = nvl(p_per_in_ler_id,pil.per_in_ler_id);
select chg.cvg_eff_strt_cd,
chg.cvg_eff_end_cd,
chg.cvg_eff_strt_rl,
chg.cvg_eff_end_rl,
chg.ler_chg_dpnt_cvg_cd,
chg.ler_chg_dpnt_cvg_rl
from ben_ler_chg_dpnt_cvg_f chg
where chg.ler_id = l_per_in_ler.ler_id
and chg.business_group_id = p_business_group_id
and decode(p_level,
'PL',p_pl_id,
'PTIP',p_ptip_id,
'PGM', p_pgm_id) =
decode(p_level,
'PL',chg.pl_id,
'PTIP',chg.ptip_id,
'PGM', chg.pgm_id)
and l_lf_evt_ocrd_dt
between chg.effective_start_date
and chg.effective_end_date;
select chg.cvg_eff_strt_cd,
chg.cvg_eff_end_cd,
chg.cvg_eff_strt_rl,
chg.cvg_eff_end_rl,
chg.ler_chg_dpnt_cvg_cd,
chg.ler_chg_dpnt_cvg_rl
from ben_ler_chg_dpnt_cvg_f chg,
ben_ptip_f ptip
where chg.ler_id = l_per_in_ler.ler_id
and chg.business_group_id = p_business_group_id
and chg.Ptip_id = ptip.ptip_id (+)
and l_lf_evt_ocrd_dt
between ptip.effective_start_date (+)
and ptip.effective_end_date (+)
and decode(p_level,
'PL',p_pl_id,
'PTIP',l_pl_typ_id, -- 2508745 ptip validateated agains pl type id
'PGM', p_pgm_id) =
decode(p_level,
'PL',chg.pl_id,
'PTIP',ptip.pl_typ_id,
'PGM', chg.pgm_id)
and l_lf_evt_ocrd_dt
between chg.effective_start_date
and chg.effective_end_date;
select prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f pen
where pen.person_id=p_person_id and
pen.business_group_id =p_business_group_id and
--pen.sspndd_flag='N' and --5127698
pen.prtt_enrt_rslt_stat_cd is null and
pen.effective_end_date = hr_api.g_eot and
nvl(l_lf_evt_ocrd_dt,p_effective_date)-1 <=
pen.enrt_cvg_thru_dt and
pen.enrt_cvg_strt_dt < pen.effective_end_date
and pen.pl_id = p_pl_id
and nvl(pen.pgm_id, -1) = nvl(p_pgm_id, -1);
select prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f pen
where pen.person_id=p_person_id and
pen.business_group_id =p_business_group_id and
--pen.sspndd_flag='N' and --5127698
pen.prtt_enrt_rslt_stat_cd is null and
pen.effective_end_date = hr_api.g_eot and
nvl(l_lf_evt_ocrd_dt,p_effective_date)-1 <=
pen.enrt_cvg_thru_dt and
pen.enrt_cvg_strt_dt < pen.effective_end_date and
pen.oipl_id=p_oipl_id
and nvl(pen.pgm_id, -1) = nvl(p_pgm_id, -1);
select ctr.contact_person_id,
ctr.contact_relationship_id,
ctr.contact_type,
ctr.date_end, -- 4697057 Added this
'Y' contact_active_flag,
per.date_of_death /* Bug 7481099 */
from per_contact_relationships ctr,
per_all_people_f per
where per.person_id = ctr.contact_person_id
-- and l_lf_evt_ocrd_dt <= nvl(per.DATE_OF_DEATH, l_lf_evt_ocrd_dt) /* Bug 4287999 */
and l_lf_evt_ocrd_dt between per.effective_start_date and per.effective_end_date
and ctr.personal_flag = 'Y'
and ctr.contact_relationship_id =
( select min(contact_relationship_id)
from per_contact_relationships ctr2
where ctr2.contact_person_id = ctr.contact_person_id
and ctr2.person_id = p_person_id /* Bug 4692782 */
and ctr2.personal_flag = 'Y'
and l_lf_evt_ocrd_dt between
nvl(ctr2.date_start,nvl(l_lf_evt_ocrd_dt,p_effective_date))
and nvl(ctr2.date_end,nvl(l_lf_evt_ocrd_dt,p_effective_date))
)
and ctr.business_group_id = p_business_group_id
and ctr.person_id = p_person_id
and l_lf_evt_ocrd_dt between
nvl(ctr.date_start,nvl(l_lf_evt_ocrd_dt,p_effective_date))
and nvl(ctr.date_end,nvl(l_lf_evt_ocrd_dt,p_effective_date))
union
--
-- this union is to provide rows for ended contacts.
-- these ended contacts will have contact_active_flag='N'.
-- these contacts elig_cvrd_dpnt rows should be ended.
--
select ctr.contact_person_id,
ctr.contact_relationship_id,
ctr.contact_type,
ctr.date_end, -- 4697057 Added this
'N' contact_active_flag,
per.date_of_death /* Bug 7481099 */
from per_contact_relationships ctr,
per_all_people_f per,
ben_elig_cvrd_dpnt_f pdp,
ben_per_in_ler pil
where per.person_id = ctr.contact_person_id
-- and l_lf_evt_ocrd_dt <= nvl(per.DATE_OF_DEATH, l_lf_evt_ocrd_dt) /* Bug 4287999 */
and l_lf_evt_ocrd_dt between per.effective_start_date and per.effective_end_date
and ctr.personal_flag = 'Y'
and ctr.business_group_id = p_business_group_id
and ctr.person_id = p_person_id
and l_lf_evt_ocrd_dt >= nvl(ctr.date_end,nvl(l_lf_evt_ocrd_dt,p_effective_date))
and pdp.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
and pdp.business_group_id = p_business_group_id
and pdp.dpnt_person_id = ctr.contact_person_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
pdp.effective_start_date and pdp.effective_end_date
and l_lf_evt_ocrd_dt between pdp.cvg_strt_dt and nvl(pdp.cvg_thru_dt,hr_api.g_eot)
and pil.per_in_ler_id=pdp.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and not exists (
select null
from per_contact_relationships ctr1,
per_all_people_f per1
where ctr1.contact_person_id=ctr.contact_person_id
and per1.person_id = ctr1.contact_person_id
and l_lf_evt_ocrd_dt between per1.effective_start_date and per1.effective_end_date
and ctr1.personal_flag = 'Y'
and ctr1.business_group_id = p_business_group_id
and ctr1.person_id = p_person_id
and nvl(l_lf_evt_ocrd_dt,p_effective_date) between
nvl(ctr1.date_start, nvl(l_lf_evt_ocrd_dt,p_effective_date))
and nvl(ctr1.date_end, nvl(l_lf_evt_ocrd_dt,p_effective_date)))
;
select a.lvl, -- 5152062 : Added the GROUPING..
SUM(NVL(a.mn_dpnts_rqd_num,0)) mn_dpnts_rqd_num,
SUM(NVL(a.mx_dpnts_alwd_num,9999999)) mx_dpnts_alwd_num, --Bug 5529902: if mx_dpnts_alwd_num is NULL consider unlimited designees can be assigned
MAX(NVL(a.no_mn_num_dfnd_flag,'N')) no_mn_num_dfnd_flag
from (
select decode(nvl(ddr.oipl_id,-1),
-1,
decode(nvl(ddr.opt_id,-1),
-1,
3,--pl level
2),--opt level
1) lvl, -- oipl level,
mn_dpnts_rqd_num,
mx_dpnts_alwd_num,
no_mn_num_dfnd_flag
from ben_dsgn_rqmt_f ddr
where (ddr.oipl_id = p_oipl_id
or ddr.pl_id = p_pl_id
or ddr.opt_id = (select oipl.opt_id
from ben_oipl_f oipl
where oipl.oipl_id = p_oipl_id
and oipl.business_group_id =
p_business_group_id
and l_lf_evt_ocrd_dt
between oipl.effective_start_date
and oipl.effective_end_date))
and ddr.dsgn_typ_cd = 'DPNT'
/*
Task 133 : when mn_dpnts_rqd_num is 0 or no_mn_num_dfnd_flag is Y
then make dpnt_dsgn_cd optional, otherwise no interim will be
assigned. These commented conditions are checked when the rows are
fetched.
and nvl(ddr.mn_dpnts_rqd_num,-1) = 0
and nvl(ddr.mx_dpnts_alwd_num,-1) = 0
*/
and ddr.business_group_id = p_business_group_id
and l_lf_evt_ocrd_dt
between ddr.effective_start_date
and ddr.effective_end_date
) a
group by a.lvl
order by 1
;
select pdp.object_version_number,
pdp.elig_cvrd_dpnt_id,
pdp.effective_start_date,
pdp.cvg_strt_dt,
pdp.effective_end_date
from ben_elig_cvrd_dpnt_f pdp,
ben_per_in_ler pil
where pdp.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
and pdp.business_group_id = p_business_group_id
and pdp.dpnt_person_id = l_contact.contact_person_id
-- bug 3784375
--and nvl(p_lf_evt_ocrd_dt,p_effective_date)
-- between pdp.effective_start_date and pdp.effective_end_date
and pdp.effective_end_date = hr_api.g_eot
--
-- Bug 5127698 : Due to following date check, dependents do not get carry forwarded
-- when PDP.cvg_strt_dt is later than l_lf_evt_ocrd_dt.
-- Above date check(EOT) should be sufficient.
--and l_lf_evt_ocrd_dt
-- between pdp.cvg_strt_dt and nvl(pdp.cvg_thru_dt,hr_api.g_eot)
-- Instead of above check, added following check which will make sure that
-- end dated dependents will not get carry forwarded
and nvl(pdp.cvg_thru_dt,hr_api.g_eot) >= l_lf_evt_ocrd_dt
and nvl(pdp.cvg_thru_dt,hr_api.g_eot) >= pdp.cvg_strt_dt
and pil.per_in_ler_id=pdp.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select decode(pln.invk_dcln_prtn_pl_flag,
'N', nvl(opt.invk_wv_opt_flag,'N'),
'Y') dcln_pl_opt_flag
from ben_pl_f pln,
ben_oipl_f oipl,
ben_opt_f opt
where pln.pl_id = p_pl_id
and oipl.oipl_id (+) = p_oipl_id
and oipl.pl_id (+) = pln.pl_id
and oipl.opt_id = opt.opt_id (+)
and l_lf_evt_ocrd_dt
between pln.effective_start_date and pln.effective_end_date
and l_lf_evt_ocrd_dt
between oipl.effective_start_date(+) and oipl.effective_end_date(+)
and l_lf_evt_ocrd_dt
between opt.effective_start_date(+) and opt.effective_end_date(+);
select null
from ben_elig_dpnt egd
where egd.dpnt_person_id = p_dpnt_person_id
and egd.per_in_ler_id = p_per_in_ler_id
and egd.elig_per_id = p_elig_per_id
and (p_elig_per_opt_id is null or
egd.elig_per_opt_id = p_elig_per_opt_id);
g_egd_table.delete;
ben_manage_unres_life_events.update_elig_dpnt
(p_elig_dpnt_id => l_elig_dpnt_id
,p_create_dt => l_lf_evt_ocrd_dt
,p_business_group_id => p_business_group_id
,p_elig_per_elctbl_chc_id => null
,p_dpnt_person_id => l_contact.contact_person_id
,p_per_in_ler_id => l_per_in_ler.per_in_ler_id
,p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
,p_elig_strt_dt => nvl(l_egd_rec.elig_strt_dt,
nvl(p_lf_evt_ocrd_dt,
p_effective_date))
,p_elig_thru_dt => hr_api.g_eot
,p_elig_per_id => l_elig_per_id
,p_elig_per_opt_id => l_elig_per_opt_id,
p_ovrdn_flag => nvl(l_egd_rec.ovrdn_flag,'N')
,p_ovrdn_thru_dt => l_egd_rec.ovrdn_thru_dt
,p_object_version_number => l_egd_object_version_number
,p_effective_date => p_effective_date
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => l_sysdate
);
,p_program_update_date => l_sysdate
);
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update_override then
--
l_datetrack_mode := hr_api.g_update_override;
elsif l_update then
--
l_datetrack_mode := hr_api.g_update;
-- Update the eligible dependent record
--
hr_utility.set_location (' Elig N BECD_UECD '||l_proc,90);
ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt
(p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id
,p_effective_start_date => l_pdp_effective_start_date
,p_effective_end_date => l_pdp_effective_end_date
,p_per_in_ler_id => l_per_in_ler.per_in_ler_id
,p_cvg_thru_dt => l_cvg_end_dt
,p_object_version_number => l_pdp.object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => l_sysdate
,p_business_group_id => p_business_group_id
,p_multi_row_actn => FALSE
);
ben_elig_dpnt_api.update_elig_dpnt(
p_elig_dpnt_id => l_egd_rec.elig_dpnt_id
,p_object_version_number => l_egd_rec.object_version_number
,p_effective_date => nvl(l_lf_evt_ocrd_dt,p_effective_date)
,p_elig_thru_dt => (l_contact_date_end-1) --l_cvg_end_dt 5100008
,p_dpnt_inelig_flag => 'Y'
,p_inelg_rsn_cd => l_inelig_rsn_cd
);
ben_elig_dpnt_api.update_perf_elig_dpnt
(p_elig_dpnt_id => l_egd_rec.elig_dpnt_id
,p_per_in_ler_id => l_per_in_ler.per_in_ler_id
,p_elig_thru_dt => (l_contact_date_end-1)--nvl(l_cvg_end_dt,p_effective_date) 5100008
,p_dpnt_inelig_flag => 'Y'
,p_inelg_rsn_cd => l_inelig_rsn_cd
,p_object_version_number => l_egd_rec.object_version_number
,p_effective_date => nvl(l_lf_evt_ocrd_dt,p_effective_date)
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => l_sysdate
);
select chc.elig_per_elctbl_chc_id,
chc.object_version_number,
chc.business_group_id,
chc.per_in_ler_id,
chc.pgm_id,
chc.pl_id,
chc.pl_typ_id,
chc.ptip_id,
chc.oipl_id,
chc.prtt_enrt_rslt_id,
chc.elctbl_flag,
pel.enrt_perd_id
from ben_elig_per_elctbl_chc chc,
ben_pil_elctbl_chc_popl pel
where chc.elig_per_elctbl_chc_id = l_chc_id
and chc.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id;
select pil.ler_id,
pil.person_id,
pil.lf_evt_ocrd_dt
from ben_per_in_ler pil
where pil.per_in_ler_id = l_elctbl_chc.per_in_ler_id
and pil.business_group_id = l_elctbl_chc.business_group_id
and pil.per_in_ler_stat_cd = 'STRTD';
ben_elig_per_elc_chc_api.update_perf_elig_per_elc_chc
(p_elig_per_elctbl_chc_id => l_elctbl_chc.elig_per_elctbl_chc_id,
p_alws_dpnt_dsgn_flag => 'N',
p_object_version_number => l_elctbl_chc.object_version_number,
p_effective_date => p_effective_date,
p_dpnt_dsgn_cd => g_upd_epe_egd_rec.g_code,
p_ler_chg_dpnt_cvg_cd => g_upd_epe_egd_rec.g_ler_chg_dpnt_cvg_cd,
p_program_application_id => fnd_global.prog_appl_id,
p_program_id => fnd_global.conc_program_id,
p_request_id => fnd_global.conc_request_id,
p_program_update_date => l_sysdate,
p_dpnt_cvg_strt_dt_cd => g_upd_epe_egd_rec.g_cvg_strt_cd ,
p_dpnt_cvg_strt_dt_rl => g_upd_epe_egd_rec.g_cvg_strt_rl );
ben_elig_per_elc_chc_api.update_perf_elig_per_elc_chc
(p_elig_per_elctbl_chc_id => l_elctbl_chc.elig_per_elctbl_chc_id,
p_alws_dpnt_dsgn_flag => 'Y',
p_object_version_number => l_elctbl_chc.object_version_number,
p_effective_date => p_effective_date,
p_dpnt_dsgn_cd => g_upd_epe_egd_rec.g_code,
p_ler_chg_dpnt_cvg_cd => g_upd_epe_egd_rec.g_ler_chg_dpnt_cvg_cd,
p_program_application_id => fnd_global.prog_appl_id,
p_program_id => fnd_global.conc_program_id,
p_request_id => fnd_global.conc_request_id,
p_program_update_date => l_sysdate,
p_dpnt_cvg_strt_dt_cd => g_upd_epe_egd_rec.g_cvg_strt_cd ,
p_dpnt_cvg_strt_dt_rl => g_upd_epe_egd_rec.g_cvg_strt_rl );
ben_elig_dpnt_api.update_perf_elig_dpnt
(p_elig_dpnt_id => g_egd_table(l_curr_count).elig_dpnt_id,
p_per_in_ler_id => l_elctbl_chc.per_in_ler_id,
p_elig_per_elctbl_chc_id => l_elctbl_chc.elig_per_elctbl_chc_id,
p_object_version_number =>
g_egd_table(l_curr_count).object_version_number,
p_effective_date => p_effective_date,
p_program_update_date => l_sysdate);