[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Newly elig does update, not insert.
First Elig records have start date.
Newly Elig recs start date
Unless there is an 'overlapping' past rec:
Newly inelig recs update (corr) old rec
with computed end date, then update
record with start date = end date +1.
Newly elig recs update (corr) old rec
with computed (strt date-1), then update
record with computed start date.
First inelig have end date +1 in
start date
24 Jan 98 lmcdonal 110.8 Add opt_id back into c_elig_per_opt.
Add order by to c_opt_next_popl_yr.
24 Jan 98 lmcdonal 110.9 Add set_name before all raise
g_record_error.
Remove strt, end dt RL processing.
03 Feb 98 lmcdonal 110.10 Remove 'when other's in exception.
06 Mar 98 gperry 110.11 Removed certain cursors so code
now uses global structures. Other
general tidying up of code.
08 Apr 98 gperry 110.12 Added output string info for
improvements to the log.
26 May 98 thayden 110.13 Chg AFDPPSY to AFDFPPSY.
27 May 98 gperry 110.14 Added calls to formula cover
routine.
06 Jun 98 jmohapat 110.15 Added call to deenroll for newly ineligible
07 Jun 98 jmohapatra 110.16 procedure name changed to
ben_newly_ineligible.main
10 Jun 98 lmcdonal 110.17 made header line after create line.
12 Jun 98 gperry 110.18 Fixed update modes so we can
handle update_overrides.
14 Jun 98 gperry 110.19 Fixed log messages.
07 Jul 98 jmohapat 110.20 Added batch who cols to call of
apis like(ben_elig_person_option_api.
create_elig_person_option and update,
ben_eligibile_person_api.create_eligible
_person and update)
23 Jul 98 thayden 110.21 Added Inelg rsn cd.
23 Oct 98 gperry 115.11 Added in extra call to
ben_newly_ineligible to handle
plans as well as programs.
26 Oct 98 gperry 115.12 Corrected ben_elig_per_f so
pgm_id is populated if pl_id has
a program parent.
23 Nov 98 gperry 115.13 Supports new columns for
ben_elig_per_f and
ben_elig_per_opt_f
23 Nov 98 gperry 115.14 Added inelg_rsn_cd.
02 Jan 99 gperry 115.15 Made trk inelig per flag work.
18 Jan 99 G Perry 115.16 LED V ED
25 Jan 99 G Perry 115.17 Fixed first time inelig case
for options.
17 Feb 99 G Perry 115.18 Added once_r_cntug_cd to insert
and update of elig_per and
elig_per_opt.
25 Feb 99 G Perry 115.19 Converted get_start_end_dates
to use generic call to
ben_determine_date.main.
09 Mar 99 G Perry 115.20 IS to AS.
03 May 99 S Das 115.23 Added parameters to genutils.formula.
04 May 99 S Das 115.24 Added jurisdiction code.
05 May 99 G Perry 115.25 Changed code to use
ben_comp_object routine.
ben_elig_object routine.
Both routines use hashed indexes.
06 May 99 G Perry 115.26 Added call to ben_comp_object
that was missed in last arcs in.
06 May 99 jcarpent 115.27 Fork of 115.19 to change cache
06 May 99 jcarpent 115.28 Same as 115.26
14 May 99 bbulusu 115.29 Added waiting period logic in check_prev_el.
Added function get_prtn_st_dt_aftr_wtg.
14 May 99 G Perry 115.30 Added support for PLIP and PTIP.
14 Jun 99 T Mathers 115.31 Changed to_dates to be NLS compliant
17 Jun 99 bbulusu 115.32 Added wait_perd_cmpltn_dt to the Elig per
and elig per opt apis. Added hr_api.g_sot.
23 Jun 99 G Perry 115.33 performance fixes and fixed balas spurious
use of dates.
28 Jun 99 jcarpent 115.34 Added per_in_ler_id to elig_per api call.
and added p_per_in_ler_id to check_prev_elg
08 Jul 99 maagrawa 115.35 Modified c_ptnl_le cursor to look for
non-voided potentials (Bug 2698).
11 Jul 99 mhoyes 115.36 - Added new trace messages.
- Removed + 0s from all cursors.
12 Jul 99 jcarpent 115.37 - Added checks for backed out nocopy pil.
13 Jul 99 mhoyes 115.38 - Added trace messages.
20-JUL-99 Gperry 115.39 genutils -> benutils package rename.
28-JUL-99 mhoyes 115.40 - Added trace messages.
- Removed product join from cursor
c_prev_elig_check.
03-AUG-99 jcarpent 115.41 - changed prev_elig_check cursor to
weed out nocopy backed out nocopy pils.
10-AUG-99 Gperry 115.42 Removed references to g_cache_person.
Fix all date formula calls to use
fnd_date.canonical_to_date.
19-AUG-99 Shdas 115.43 Added pl_ordr_num,plip_ordr_num,ptip_ordr_num
in create_eligible_person and oipl_ordr_num
in create_elig_person_option.
GPerry Added call to get benefits assignment if no
employee assignment is found.
17-SEP-99 pbodla 115.44 Bug 2551 fixes. Code reshuffled see comments
01-OCT-99 gperry 115.45 Backport of 115.42 with Eligibility fixed.
Now still elig records
get created even though eligibility hasn't
changed.
04-OCT-99 gperry 115.46 Leapfrog of 115.44 with Eligibility fixed.
03-NOV-99 mhoyes 115.47 - Added eligibility transition state.
OUT NOCOPY parameters to check_prev_elig.
- Added new logic to flag when a non tracked
first time in-eligible occurs. This is only
stored and passed as an OUT NOCOPY parameter. No
elig per is written.
- Passed p_override_validation to EPO and PEP
APIs to bypass insert_validate validation.
This doubles the execution speed of these
two APIs.
12-NOV-99 mhoyes 115.48 - Added new trace messages.
12-NOV-99 stee 115.49 - Added business_group_id parameter to some
of the calls to ben_determine_date
procedure.
10-JAN-00 stee 115.50 - Get per_in_ler info in get_start_end_dates
procedure for date calculations.
WWBUG:1096812
11-Feb-00 jcarpent 115.51 - Use db mode globals.
- pass pgm_id to beninelg
21-FEB-00 tguy 115.52 Fixed heirarchy in get_start_end_dates
procedure to walk down levels of comp obj
wwbugs: 1161304,1161305,1183241,1167918
23-Feb-00 lmcdonal 115.53 Bug 1167918: Only inherit plip, ptip, pgm
if plan/oipl is in a program. And if plan
is in pgm, look for plip before pl.
Get ptip inheritance to work.
24-Feb-00 jcarpent 115.54 - same as 115.51
24-Feb-00 jcarpent 115.55 - Don't call beninelg unless have oipl, pl,
or pgm. - patch of 115.51
24-Feb-00 jcarpent 115.56 - Leapfrog. same fix as above to 115.53.
26-Feb-00 mhoyes 115.57 - Added p_comp_obj_tree_row to
check_prev_elig and passed into
derive_rates_and_factors.
03-Mar-00 mhoyes 115.58 - Passed p_comp_obj_tree_row into
ben_det_wait_perd_cmpltn.main.
03-Mar-00 mhoyes 115.59 - Phased out nocopy ben_env_object.
07-Mar-00 gperry 115.60 Fixed trk_inelig_per_flag for PTIP and PLIP.
31-Mar-00 gperry 115.61 Added oiplip support hangs off plip.
03-Apr-00 mmogel 115.62 - Added tokens to message calls to make
messages more meaningful to the user
07-Apr-00 gperry 115.63 If person fails waiting period make them
ineligible for the object.
WWBUG 1230645, previously it errored rec.
15-Mar-00 mhoyes 115.64 - Modified calls to create_Eligible_Person to
create_perf_Eligible_Person.
- Re-referenced and cleared junk from get
start and end dates.
- Modified calls to
create_Elig_Person_Option to
create_perf_Elig_Person_Option.
22-May-00 mhoyes 115.65 - Replaced benutils function call to get the
per in ler with a cache call.
22-May-00 mhoyes 115.66 - Reduced cache calls in get start and end
dates by using passed in records.
23-May-00 shdas 115.67 - bug5224 call ben_newly_inelig.main if inelig for
plip and ptip.
24-May-00 mhoyes 115.68 - Tuned check_prev_elig cursors.
02-Jun-00 mhoyes 115.70 - Re-instated 115.68.
28-Jun-00 mhoyes 115.71 - Passed current row information into waiting
periods.
- Cached eligibility cursors.
30-Jun-00 mhoyes 115.72 - Passed in context parameters into bendrpar.
13-Jul-00 mhoyes 115.73 - Removed context parameters.
04-Aug-00 jcarpent 115.74 - 5412. Don't reset override fields
when doing an update.
14-Sep-00 mhoyes 115.75 - Removed highly executed hr_utility.set_location
statements.
02-Oct-00 pbodla 115.76 - Bugs : 1412882, part of bug 1412951
When elig per or opt rows are created they
should be created as of least of p_effective_date
and p_lf_evt_ocrd_dt
06-Oct-00 pbodla 115.77 - Above fix caused problem for selection and
temporal mode of benmngle run : due to null
passed to effective_date while pep/epo
api's are called. So put a nvl around
p_lf_evt_ocrd_dt.
29-Nov-00 Tmathers 115.78 - Fixed Invalid cursor error, wwbug
- 1517275, caused by having a Temporal
- LE created by BENDRPAR then re-closing
- already closed cursor.
15-Dec-00 rchase 115.79 - Bug 1531030. p_comp_rec and p_oiplip_rec
were not being refreshed with updated info.
01-May-01 mhoyes 115.80 - Pointed c_prev_oiplip_elig_check to the
EPO cache.
18-Jul-01 kmahendr 115.81 - Bug#1871579-Effective date is passed as least of
p_effective_date or lf_evt_ocrd_dt to cursors in
check_prv_eligibility
01-Aug-01 kmahendr 115.82 - Plan not in programs not getting deenrolled on selection
mode-bug#1871579. nvl is used to return effective date
for least function in 3 places where it was not there before
27-aug-01 tilak 115.83 bug:1949361 jurisdiction code is
derived inside benutils.formula.
26-sep-01 tjesumic 115.84 wait_perd_Strt_dt added
19-dec-01 pbodla 115.85 CWB Changes - Look for non comp potential
life events when waiting periods are defined.
20-Dec-01 ikasire 115.86 added dbdrv lines
07-Jan-02 Rpillay 115.87 Added Set Verify Off.
11-Mar-02 mhoyes 115.88 Dependent eligibility tuning.
12-Mar-02 pbodla 115.89 Bug 2284417 : Do not update the per in ler id
with new per in ler id as this peice of
row should belong to previous per in ler.
Next update will update the per in ler id as
well as new prtn strt dt.
12-Jul-02 mhoyes 115.90 - Added calls to get_curroiplippep_dets and
get_currplnpep_dets.
20-Aug-02 mhoyes 115.91 - Populated comp object list row eligible values.
12-Oct-02 ikasire 115.92 - Bug 2551834 fixed the codes ALDCPPSY and ALDCPPY
in get_start_and_end_dates procedure
29-Jan-03 kmahendr 115.93 - In P mode participation start date and end date are
defaulted to Event and 1 day before event
10-feb-03 hnarayan 115.94 - Added NOCOPY Changes
11-Mar-03 rpillay 115.95 - Bug 2806554 - Changed default for
Participation End Date Code from AEOT to
ODBED
17-Mar-03 vsethi 115.96 - Bug 2650247 - populating the value of inelg_rsn_cd
in p_comp_obj_tree_row record.
07-Apr-03 mmudigon 115.97 - Bug#2841136-Effective date is passed as
least of p_effective_date or lf_evt_ocrd_dt
to calls to ben_pep_cache.
Corrected typo in determining DT Track mode
for updating OIPLIP.
24-Jun-03 hnarayan 115.98 - Bug 3001411 - changed cursor c_ptnl_le to
ignore LEs of type SCHEDDU, ABS, COMP and GSP
becos the presence of these LE shud not make a
person ineligible in case of waiting period setup.
02-Sep-03 rpgupta 115.99 - Bug 3111613 - if cache does'nt return any
elig_per_id, raise an error
18-Sep-03 mhoyes 115.100 - Phased in calls to ben_pep_cache1 rather than
ben_pep_cache.
19 Sep 03 mhoyes 115.101 - 3150329 - Update eligibility APIs.
26 Sep 03 mhoyes 115.102 - More update eligibility APIs.
21 Jan 04 ikasire 115.103 BUG 3327841 - fixed the wrong assignment of epo
data to pep. Also calling clear_down_cache
to get the right data.
02 Feb 04 ikasire 115.104 BUG 3327841 Using the ben_pep_cache1.get_currplnpep_dets
to get the current pep info instread of depending on
ben_pep_cache.get_pilepo_dets
30 Mar 04 ikasire 115.105 fonm changes.
15 Apr 04 ikasire 115.106 Bug 3550789 added a new procedure save_to_restore
to save the PEP EPO data in certain cases when
we loose the data and can't restore as part of
reprocess
1 June 04 kmahendr 115.108 removed assignment of prtn_strt_dt in fonm
mode per prasad
4 June 04 rpgupta 115.109 3597303 - Make person eligible even if a ptnl
LE exists before the waiting period end
28-Sep-04 abparekh 115.110 iRec : Modified procedure check_prev_elig
Modified cursor c_prev_elig_check and
c_prev_opt_elig_check in check_prev_elig
28-Mar-05 ikasire 115.111 Bug 4241413 fix for the data type of
wait_perd_cmpltn_dt
26-Apr-05 mmudigon 115.112 Score and Weight
17-Jun-05 abparekh 115.113 Bug 4438430 : Pass PER_IN_LER_ID while creating
ESW record
12-jan-05 ssarkar 115.114 Bug 4947426 : passed l_effective_dt to update_perf_Eligible_Person
23-Jan-06 kmahendr 115.115 Bug#4960082 - newly_ineligible process is
added to still_ineligible condition
27-Jan-06 mhoyes 115.116 Bug#4968123 - hr_utility debug and locally
defined plsql tuning.
30-Jan-06 mhoyes 115.117 Bug#4968123 - moved out locall defined procs
to ben_determine_eligibility3.
30-Jan-06 mhoyes 115.118 Bug#4968123 - moved out cursors
to ben_determine_eligibility4.
28-Jun-06 swjain 115.119 Bug 5331889 Added person_id param in call to
benutils.formula in procedure get_start_end_dates
24-Jul-06 kmahendr 115.120 Bug#5404392-added ben_newly_ineligible call
in the case of first time ineligible.
25-aug-06 ssarkar 115.121 bug# 5478994 - passed ler_id to update_perf_eligible_person
11-Dec-06 rgajula 115.122 Bug 5682845 - passed the l_envplipid to the call to ben_pep_Cache.get_pilepo_dets.
07-Feb-07 kmahendr 115.124 Fidelity Enh to update inelig rows and
reversed fix made for 5682845
08-Feb-07 kmahendr 115.125 Reversed the condition to update inelig rows
16-Feb-07 rtagarra 115.126 ICM Changes.
12-jun-07 rtagarra 115.127 ICM: Bug 6038232. Also incorporated changes of Bug 6000303 : Defer Deenrollment ENH
on 04-Dec-07 from branchline.
23-Jan-2008 sallumwa 115.128 Bug 6601884 : For still ineligible records, when ineligibility is to be tracked,
the records should be updated with correct datetrack mode for both ben_elig_per_f
and ben_elig_per_opt_f.
12-Jan-2009 krupani 120.17.12010000.1 Forward ported fix of 11i bug 8542643
31-Oct-09 velvanop 120.17.12010000.2 Bug 9081414: Fwd port of Bug 9020962 : If future eligibility record exists for the previous per_in_ler_id, then insert the future eligiblity record
in backup table ben_le_clsn_n_rstr
16-Aug-10 krupani 120.17.12010000.4 Bug 10029253 - 12.1 forward port of 9789915 : In still-ineligible case,
p_still_inelig was not getting set. Fixed the same.
30-Aug-10 velvanop 120.17.12010000.5 Bug 10030441; If a Life Event is processed after the waiting period completion date
14-dec-11 usaraswa 120.17.12010000.7 Bug 12732878: If future eligibility record exists for the previous per_in_ler_id for oipl and oiplip comp object, then insert
the future eligiblity record in to backup table ben_le_clsn_n_rstr
--------------------------------------------------------------------------------
*/
--
-- -----------------------------------------------------------------------------
-- |----------------------< get_start_end_dates >------------------------------|
-- -----------------------------------------------------------------------------
--
procedure get_start_end_dates
(p_comp_obj_tree_row in ben_manage_life_events.g_cache_proc_objects_rec
,p_pil_row in ben_per_in_ler%rowtype
,p_effective_date in date
,p_business_group_id in number
,p_person_id in number
,p_pl_id in number
,p_pgm_id in number
,p_oipl_id in number
,p_plip_id in number
,p_ptip_id in number
,p_prev_prtn_strt_dt in date
,p_prev_prtn_end_dt in date
,p_start_or_end in varchar2
,p_prtn_eff_strt_dt out nocopy date
,p_prtn_eff_strt_dt_cd out nocopy varchar2
,p_prtn_eff_strt_dt_rl out nocopy number
,p_prtn_eff_end_dt out nocopy date
)
is
--
/* 4968123
l_proc varchar2(100):=g_package||'get_start_end_dates';
whether a new row will be inserted or an existing row will be
updated.
4. If a person is still eligible or ineligible, then nothing is done.
5. If a person is newly eligible, then a new row will be inserted.
6. If a person is newly ineligible, then an existing row will be
updated.
7. If a person is first-time eligible, then a new row will be inserted.
8. If a person is first-time ineligible, and the track ineligible persons
flag is 'Y' on the program, plan or option we are processing then a
new row will be inserted with elig_flag of 'N'.
9. If a person is found "eligible again"--meaning the history of the
person shows that they were once eligible, then found ineligible
and now are eligible again, then that person will be treated as
though they are newly eligible, i.e. a row will be inserted.
*/
--
l_epo_row ben_derive_part_and_rate_facts.g_cache_structure;
l_update boolean;
l_update_override boolean;
l_update_change_insert boolean;
select pep.elig_per_id,
pep.elig_flag,
pep.prtn_strt_dt,
pep.prtn_end_dt,
pep.per_in_ler_id,
pep.object_version_number
from ben_elig_per_f pep,
ben_per_in_ler pil
where pep.person_id = c_person_id
and nvl(pep.pgm_id,-1) = c_pgm_id
and nvl(pep.pl_id,-1) = c_pl_id
and pep.plip_id is null
and nvl(pep.ptip_id,-1) = c_ptip_id
and c_effective_date
between pep.effective_start_date
and pep.effective_end_date
and pil.per_in_ler_id(+)=pep.per_in_ler_id
and pil.business_group_id(+)=pep.business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null -- outer join condition
)
and nvl(pil.assignment_id, -9999) = decode ( l_benmngle_parm_rec.mode_cd,
'I',
ben_manage_life_events.g_irec_ass_rec.assignment_id,
nvl(pil.assignment_id, -9999) ) -- iRec : Match assignment_id for iRec
;
select epo.elig_per_opt_id,
epo.elig_flag,
epo.prtn_strt_dt,
epo.prtn_end_dt,
epo.object_version_number,
pep.elig_per_id,
epo.per_in_ler_id,
pep.prtn_strt_dt,
pep.prtn_end_dt
from ben_elig_per_opt_f epo,
ben_per_in_ler pil,
ben_elig_per_f pep
where pep.person_id = c_person_id
and pep.pl_id = c_pl_id
and epo.opt_id = c_opt_id
and pep.elig_per_id = epo.elig_per_id
and pep.pgm_id is null
and c_effective_date
between pep.effective_start_date
and pep.effective_end_date
and c_effective_date
between epo.effective_start_date
and epo.effective_end_date
and pil.per_in_ler_id(+)=epo.per_in_ler_id
and pil.business_group_id(+)=epo.business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null)
and nvl(pil.assignment_id,-9999) = decode ( l_benmngle_parm_rec.mode_cd,
'I',
ben_manage_life_events.g_irec_ass_rec.assignment_id,
nvl(pil.assignment_id, -9999) ); -- iRec : Match assignment_id for iRec
select epo.elig_per_opt_id,
epo.elig_per_id,
epo.effective_start_date,
epo.object_version_number
from ben_elig_per_opt_f epo,
ben_per_in_ler pil,
ben_elig_per_f pep
where pep.person_id = c_person_id
and pep.pgm_id = c_pgm_id
and pep.plip_id = c_plip_id
and epo.opt_id = c_opt_id
and pep.elig_per_id = epo.elig_per_id
and c_effective_date
between pep.effective_start_date
and pep.effective_end_date
and c_effective_date
between epo.effective_start_date
and epo.effective_end_date
and pil.per_in_ler_id(+)=epo.per_in_ler_id
and pil.business_group_id(+)=epo.business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null) ;
select pep.prtn_strt_dt
from ben_elig_per_f pep,
ben_per_in_ler pil
where pep.person_id = p_person_id
and nvl(pep.pgm_id,-1) =
nvl(p_pgm_id,nvl(l_envpgm_id,-1))
and nvl(pep.pl_id,-1) = nvl(p_pl_id,-1)
and nvl(pep.plip_id,-1) = nvl(p_plip_id,-1)
and nvl(pep.ptip_id,-1) = nvl(p_ptip_id,-1)
and c_prtn_eff_strt_dt between pep.prtn_strt_dt
and (pep.prtn_end_dt + c_add_one)
and pep.elig_flag = c_elig_flag
and pil.per_in_ler_id(+)=pep.per_in_ler_id
and pil.business_group_id(+)=pep.business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null -- outer join condition
);
cursor c_get_pl_id(c_effective_date date) is select pl_id
from ben_plip_f cpp
where cpp.plip_id = p_plip_id
and c_effective_date
between cpp.effective_start_date
and cpp.effective_end_date;
select pl.pl_id
from ben_ptip_f ctp,ben_pl_f pl
where ctp.ptip_id = p_ptip_id
and pl.pl_typ_id = ctp.pl_typ_id
and c_effective_date
between ctp.effective_start_date
and ctp.effective_end_date
and c_effective_date
between pl.effective_start_date
and pl.effective_end_date;
select epo.prtn_strt_dt
from ben_elig_per_opt_f epo,
ben_elig_per_f pep,
ben_per_in_ler pil
where pep.person_id = p_person_id
and pep.pl_id = l_envpl_id
and nvl(pep.pgm_id,-1) = nvl(l_envpgm_id,-1)
and pep.elig_per_id = epo.elig_per_id
and c_prtn_eff_strt_dt
between epo.prtn_strt_dt
and (epo.prtn_end_dt + c_add_one)
and epo.opt_id = l_oipl_rec.opt_id
and epo.elig_flag = c_elig_flag
and pil.per_in_ler_id(+)=epo.per_in_ler_id
and pil.business_group_id(+)=epo.business_group_id+0
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null);
select 's'
from ben_ptnl_ler_for_per ptl,
ben_ler_f ler
where ptl.person_id = p_person_id
and ptl.ler_id <> p_ler_id
and ler.ler_id = ptl.ler_id
and ler.typ_cd not in ('COMP', 'ABS', 'SCHEDDU', 'GSP', 'IREC') -- iRec
and ptl.ptnl_ler_for_per_stat_cd <> 'VOIDD'
and ptl.lf_evt_ocrd_dt
between p_effective_date
and v_wait_end_dt
and ptl.business_group_id = p_business_group_id;
select plip.ordr_num from ben_plip_f plip
where plip.pgm_id = nvl(p_pgm_id,l_envpgm_id)
and pl_id = p_pl_id
and c_effective_date
between plip.effective_start_date
and plip.effective_end_date
and plip.business_group_id = p_business_group_id;
select pil.per_in_ler_id
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id not in (c_per_in_ler_id)
and pil.person_id = p_person_id
and pil.ler_id = ler.ler_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date
and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
order by pil.lf_evt_ocrd_dt desc;
select * from ben_elig_per_f
where elig_per_id = c_elig_per_id
and per_in_ler_id = c_per_in_ler_id;
select * from ben_elig_per_f pep
where nvl(pep.pgm_id,-1) = nvl(c_pgm_id,-1)
and nvl(pep.pl_id,-1) = nvl(c_pl_id,-1)
and nvl(pep.plip_id,-1) = nvl(c_plip_id ,-1)
and nvl(pep.ptip_id,-1) = nvl(c_ptip_id,-1)
and pep.per_in_ler_id = c_per_in_ler_id
and c_effective_date < pep.effective_start_date;
select * from ben_elig_per_opt_f
where elig_per_opt_id = c_elig_per_opt_id
and per_in_ler_id = c_per_in_ler_id;
select * from ben_elig_per_opt_f epo
where epo.elig_per_opt_id = c_elig_per_opt_id
and epo.per_in_ler_id = c_per_in_ler_id
and c_effective_date < epo.effective_start_date;
hr_utility.set_location ('New update for elig per opt',100);
-- Get the datetrack update mode. If the record already exists and the effective date is
-- same as start date, then update should be done in correction mode. In that case, the old
-- record should be inserted in the backup table
dt_api.find_dt_upd_modes
(p_effective_date => l_effective_dt,
p_base_table_name => 'BEN_ELIG_PER_OPT_F',
p_base_key_column => 'elig_per_opt_id',
p_base_key_value => l_elig_per_opt_id,
p_correction => l_correction,
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;
-- insert into backup table if updating in correction mode
IF l_datetrack_mode = hr_api.g_correction AND
l_per_in_ler_id <> NVL(l_prev_per_in_ler_id,-1) THEN
--
ben_determine_eligibility3.save_to_restore
(p_current_per_in_ler_id => l_per_in_ler_id
,p_per_in_ler_id => l_prev_per_in_ler_id
,p_elig_per_id => NULL
,p_elig_per_opt_id => l_elig_per_opt_id
,p_effective_date => l_effective_dt
);
ben_Eligible_Person_perf_api.update_perf_Elig_Person_Option
(p_validate => FALSE,
p_elig_per_opt_id => l_elig_per_opt_id,
p_elig_per_id => l_elig_per_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_per_in_ler_id => l_per_in_ler_id,
p_elig_flag => 'N',
p_prtn_strt_dt => l_prtn_strt_dt,
p_prtn_end_dt => null,
p_rt_comp_ref_amt => p_comp_rec.rt_comp_ref_amt,
p_rt_cmbn_age_n_los_val => p_comp_rec.rt_cmbn_age_n_los_val,
p_rt_comp_ref_uom => p_comp_rec.rt_comp_ref_uom,
p_rt_age_val => p_comp_rec.rt_age_val,
p_rt_los_val => p_comp_rec.rt_los_val,
p_rt_hrs_wkd_val => p_comp_rec.rt_hrs_wkd_val,
p_rt_hrs_wkd_bndry_perd_cd => p_comp_rec.rt_hrs_wkd_bndry_perd_cd,
p_rt_age_uom => p_comp_rec.rt_age_uom,
p_rt_los_uom => p_comp_rec.rt_los_uom,
p_rt_pct_fl_tm_val => p_comp_rec.rt_pct_fl_tm_val,
p_rt_frz_los_flag => 'N',
p_rt_frz_age_flag => 'N',
p_rt_frz_cmp_lvl_flag => 'N',
p_rt_frz_pct_fl_tm_flag => 'N',
p_rt_frz_hrs_wkd_flag => 'N',
p_rt_frz_comb_age_and_los_flag => 'N',
p_once_r_cntug_cd => p_comp_rec.once_r_cntug_cd,
p_comp_ref_amt => p_comp_rec.comp_ref_amt,
p_cmbn_age_n_los_val => p_comp_rec.cmbn_age_n_los_val,
p_comp_ref_uom => p_comp_rec.comp_ref_uom,
p_age_val => p_comp_rec.age_val,
p_los_val => p_comp_rec.los_val,
p_hrs_wkd_val => p_comp_rec.hrs_wkd_val,
p_hrs_wkd_bndry_perd_cd => p_comp_rec.hrs_wkd_bndry_perd_cd,
p_age_uom => p_comp_rec.age_uom,
p_los_uom => p_comp_rec.los_uom,
p_pct_fl_tm_val => p_comp_rec.pct_fl_tm_val,
p_frz_los_flag => 'N',
p_frz_age_flag => 'N',
p_frz_cmp_lvl_flag => 'N',
p_frz_pct_fl_tm_flag => 'N',
p_frz_hrs_wkd_flag => 'N',
p_frz_comb_age_and_los_flag => 'N',
p_wait_perd_strt_dt => l_wait_perd_strt_dt,
p_wait_perd_cmpltn_date => l_wait_perd_cmpltn_dt,
p_effective_date => l_effective_dt,
p_object_version_number => l_object_version_number_opt,
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 => sysdate);
ben_icm_life_events.g_cache_epo_object(l_count_icm1).program_update_date :=sysdate;
ben_icm_life_events.g_cache_epo_object(l_count_icm1).p_datetrack_mode:= 'UPDATE';
p_program_update_date => sysdate,
--
-- Bugs : 1412882, part of bug 1412951
--
p_effective_date => l_effective_dt,
-- p_effective_date => p_effective_date
--
-- Bypass insert validate validation for performance
--
p_override_validation => TRUE
);
ben_icm_life_events.g_cache_epo_object(l_count_icm1).program_update_date :=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;
IF (l_datetrack_mode = hr_api.g_update_override) -- AND
-- l_per_in_ler_id <> NVL(l_prev_per_in_ler_id,-1) THEN
then --
open c_prev_pil(p_pil_row.per_in_ler_id);
ben_Eligible_Person_perf_api.update_perf_Elig_Person_Option
(p_validate => FALSE,
p_elig_per_opt_id => l_elig_per_opt_id,
p_elig_per_id => l_elig_per_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_per_in_ler_id => l_per_in_ler_id,
p_elig_flag => l_elig_flag,
p_prtn_strt_dt => l_prtn_strt_dt,
p_prtn_end_dt => null,
p_rt_comp_ref_amt => p_comp_rec.rt_comp_ref_amt,
p_rt_cmbn_age_n_los_val => p_comp_rec.rt_cmbn_age_n_los_val,
p_rt_comp_ref_uom => p_comp_rec.rt_comp_ref_uom,
p_rt_age_val => p_comp_rec.rt_age_val,
p_rt_los_val => p_comp_rec.rt_los_val,
p_rt_hrs_wkd_val => p_comp_rec.rt_hrs_wkd_val,
p_rt_hrs_wkd_bndry_perd_cd => p_comp_rec.rt_hrs_wkd_bndry_perd_cd,
p_rt_age_uom => p_comp_rec.rt_age_uom,
p_rt_los_uom => p_comp_rec.rt_los_uom,
p_rt_pct_fl_tm_val => p_comp_rec.rt_pct_fl_tm_val,
p_rt_frz_los_flag => 'N',
p_rt_frz_age_flag => 'N',
p_rt_frz_cmp_lvl_flag => 'N',
p_rt_frz_pct_fl_tm_flag => 'N',
p_rt_frz_hrs_wkd_flag => 'N',
p_rt_frz_comb_age_and_los_flag => 'N',
p_once_r_cntug_cd => p_comp_rec.once_r_cntug_cd,
p_comp_ref_amt => p_comp_rec.comp_ref_amt,
p_cmbn_age_n_los_val => p_comp_rec.cmbn_age_n_los_val,
p_comp_ref_uom => p_comp_rec.comp_ref_uom,
p_age_val => p_comp_rec.age_val,
p_los_val => p_comp_rec.los_val,
p_hrs_wkd_val => p_comp_rec.hrs_wkd_val,
p_hrs_wkd_bndry_perd_cd => p_comp_rec.hrs_wkd_bndry_perd_cd,
p_age_uom => p_comp_rec.age_uom,
p_los_uom => p_comp_rec.los_uom,
p_pct_fl_tm_val => p_comp_rec.pct_fl_tm_val,
p_frz_los_flag => 'N',
p_frz_age_flag => 'N',
p_frz_cmp_lvl_flag => 'N',
p_frz_pct_fl_tm_flag => 'N',
p_frz_hrs_wkd_flag => 'N',
p_frz_comb_age_and_los_flag => 'N',
-- p_wait_perd_cmpltn_dt => l_wait_perd_cmpltn_dt,
p_wait_perd_strt_dt => l_wait_perd_strt_dt,
p_wait_perd_cmpltn_date => l_wait_perd_cmpltn_dt,
--
-- Bugs : 1412882, part of bug 1412951
--
p_effective_date => l_effective_dt,
-- p_effective_date => p_effective_date,
p_object_version_number => l_object_version_number_opt,
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 => sysdate);
ben_icm_life_events.g_cache_epo_object(l_count_icm1).program_update_date :=sysdate;
ben_Eligible_Person_perf_api.update_perf_Elig_Person_Option
(p_validate => FALSE,
p_elig_per_opt_id => l_elig_per_opt_id,
p_elig_per_id => l_elig_per_id,
--
-- Bug 2284417 : Do not update the per in ler id with new
-- per in ler id as this row peice of row should belong to
-- previous per in ler. Next update will update the
-- per in ler id as well as new prtn strt dt.
--
-- p_per_in_ler_id => l_per_in_ler_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_prtn_end_dt => l_prtn_eff_strt_dt-1,
-- p_effective_date => p_effective_date,
p_effective_date => l_effective_dt,
p_object_version_number => l_object_version_number_opt,
p_datetrack_mode => hr_api.g_correction,
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 => 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;
IF (l_datetrack_mode = hr_api.g_update_override) -- AND
-- l_per_in_ler_id <> NVL(l_prev_per_in_ler_id,-1) THEN
then --
open c_prev_pil(p_pil_row.per_in_ler_id);
ben_Eligible_Person_perf_api.update_perf_Elig_Person_Option
(p_validate => FALSE,
p_elig_per_opt_id => l_elig_per_opt_id,
p_elig_per_id => l_elig_per_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_per_in_ler_id => l_per_in_ler_id,
p_elig_flag => l_elig_flag,
p_prtn_strt_dt => l_prtn_strt_dt,
p_prtn_end_dt => null,
p_rt_comp_ref_amt => p_comp_rec.rt_comp_ref_amt,
p_rt_cmbn_age_n_los_val => p_comp_rec.rt_cmbn_age_n_los_val,
p_rt_comp_ref_uom => p_comp_rec.rt_comp_ref_uom,
p_rt_age_val => p_comp_rec.rt_age_val,
p_rt_los_val => p_comp_rec.rt_los_val,
p_rt_hrs_wkd_val => p_comp_rec.rt_hrs_wkd_val,
p_rt_hrs_wkd_bndry_perd_cd => p_comp_rec.rt_hrs_wkd_bndry_perd_cd,
p_rt_age_uom => p_comp_rec.rt_age_uom,
p_rt_los_uom => p_comp_rec.rt_los_uom,
p_rt_pct_fl_tm_val => p_comp_rec.rt_pct_fl_tm_val,
p_rt_frz_los_flag => 'N',
p_rt_frz_age_flag => 'N',
p_rt_frz_cmp_lvl_flag => 'N',
p_rt_frz_pct_fl_tm_flag => 'N',
p_rt_frz_hrs_wkd_flag => 'N',
p_rt_frz_comb_age_and_los_flag => 'N',
p_once_r_cntug_cd => p_comp_rec.once_r_cntug_cd,
p_comp_ref_amt => p_comp_rec.comp_ref_amt,
p_cmbn_age_n_los_val => p_comp_rec.cmbn_age_n_los_val,
p_comp_ref_uom => p_comp_rec.comp_ref_uom,
p_age_val => p_comp_rec.age_val,
p_los_val => p_comp_rec.los_val,
p_hrs_wkd_val => p_comp_rec.hrs_wkd_val,
p_hrs_wkd_bndry_perd_cd => p_comp_rec.hrs_wkd_bndry_perd_cd,
p_age_uom => p_comp_rec.age_uom,
p_los_uom => p_comp_rec.los_uom,
p_pct_fl_tm_val => p_comp_rec.pct_fl_tm_val,
p_frz_los_flag => 'N',
p_frz_age_flag => 'N',
p_frz_cmp_lvl_flag => 'N',
p_frz_pct_fl_tm_flag => 'N',
p_frz_hrs_wkd_flag => 'N',
p_frz_comb_age_and_los_flag => 'N',
-- p_wait_perd_cmpltn_dt => l_wait_perd_cmpltn_dt,
p_wait_perd_cmpltn_date => l_wait_perd_cmpltn_dt,
p_wait_perd_strt_dt => l_wait_perd_strt_dt,
--
-- Bugs : 1412882, part of bug 1412951
--
p_effective_date => l_effective_dt,
-- p_effective_date => p_effective_date,
p_object_version_number => l_object_version_number_opt,
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 => sysdate);
ben_icm_life_events.g_cache_epo_object(l_count_icm1).program_update_date :=sysdate;
ben_Eligible_Person_perf_api.update_perf_Elig_Person_Option
(p_validate => FALSE,
p_elig_per_opt_id => l_elig_per_opt_id,
p_elig_per_id => l_elig_per_id,
--
-- Bug 2284417 : Do not update the per in ler id with new
-- per in ler id as this row peice of row should belong to
-- previous per in ler. Next update will update the
-- per in ler id as well as new prtn strt dt.
--
-- p_per_in_ler_id => l_per_in_ler_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_prtn_end_dt => l_prtn_eff_end_dt,
-- p_effective_date => p_effective_date,
p_effective_date => l_effective_dt,
p_object_version_number => l_object_version_number_opt,
p_datetrack_mode => hr_api.g_correction,
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 => 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;
IF (l_datetrack_mode = hr_api.g_update_override) -- AND
-- l_per_in_ler_id <> NVL(l_prev_per_in_ler_id,-1) THEN
then --
open c_prev_pil(p_pil_row.per_in_ler_id);
ben_Eligible_Person_perf_api.update_perf_Elig_Person_Option
(p_validate => FALSE,
p_elig_per_opt_id => l_elig_per_opt_id,
p_elig_per_id => l_elig_per_id,
p_per_in_ler_id => l_per_in_ler_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_elig_flag => l_elig_flag,
p_prtn_strt_dt => l_prtn_strt_dt,
p_prtn_end_dt => null,
p_rt_comp_ref_amt => p_comp_rec.rt_comp_ref_amt,
p_rt_cmbn_age_n_los_val => p_comp_rec.rt_cmbn_age_n_los_val,
p_rt_comp_ref_uom => p_comp_rec.rt_comp_ref_uom,
p_rt_age_val => p_comp_rec.rt_age_val,
p_rt_los_val => p_comp_rec.rt_los_val,
p_rt_hrs_wkd_val => p_comp_rec.rt_hrs_wkd_val,
p_rt_hrs_wkd_bndry_perd_cd => p_comp_rec.rt_hrs_wkd_bndry_perd_cd,
p_rt_age_uom => p_comp_rec.rt_age_uom,
p_rt_los_uom => p_comp_rec.rt_los_uom,
p_rt_pct_fl_tm_val => p_comp_rec.rt_pct_fl_tm_val,
p_rt_frz_los_flag => 'N',
p_rt_frz_age_flag => 'N',
p_rt_frz_cmp_lvl_flag => 'N',
p_rt_frz_pct_fl_tm_flag => 'N',
p_rt_frz_hrs_wkd_flag => 'N',
p_rt_frz_comb_age_and_los_flag => 'N',
p_once_r_cntug_cd => p_comp_rec.once_r_cntug_cd,
p_comp_ref_amt => p_comp_rec.comp_ref_amt,
p_cmbn_age_n_los_val => p_comp_rec.cmbn_age_n_los_val,
p_comp_ref_uom => p_comp_rec.comp_ref_uom,
p_age_val => p_comp_rec.age_val,
p_los_val => p_comp_rec.los_val,
p_hrs_wkd_val => p_comp_rec.hrs_wkd_val,
p_hrs_wkd_bndry_perd_cd => p_comp_rec.hrs_wkd_bndry_perd_cd,
p_age_uom => p_comp_rec.age_uom,
p_los_uom => p_comp_rec.los_uom,
p_pct_fl_tm_val => p_comp_rec.pct_fl_tm_val,
p_frz_los_flag => 'N',
p_frz_age_flag => 'N',
p_frz_cmp_lvl_flag => 'N',
p_frz_pct_fl_tm_flag => 'N',
p_frz_hrs_wkd_flag => 'N',
p_frz_comb_age_and_los_flag => 'N',
--
-- Bugs : 1412882, part of bug 1412951
--
p_effective_date => l_effective_dt,
-- p_effective_date => p_effective_date,
p_object_version_number => l_object_version_number_opt,
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 => sysdate,
p_inelg_rsn_cd => p_inelg_rsn_cd);
ben_icm_life_events.g_cache_epo_object(l_count_icm1).program_update_date :=sysdate;
p_program_update_date => sysdate,
p_inelg_rsn_cd => p_inelg_rsn_cd
--
-- Bypass insert validate validation for performance
--
,p_override_validation => TRUE);
ben_icm_life_events.g_cache_epo_object(l_count_icm1).program_update_date :=sysdate;
p_program_update_date => sysdate,
--
-- Bugs : 1412882, part of bug 1412951
--
p_effective_date => l_effective_dt,
-- p_effective_date => p_effective_date
p_override_validation => TRUE);
ben_icm_life_events.g_cache_epo_object(l_count_icm1).program_update_date :=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;
IF (l_datetrack_mode = hr_api.g_update_override) -- AND
-- l_per_in_ler_id <> NVL(l_prev_per_in_ler_id,-1) THEN
then --
open c_prev_pil(p_pil_row.per_in_ler_id);
ben_Eligible_Person_perf_api.update_perf_Elig_Person_Option
(p_validate => FALSE,
p_elig_per_opt_id => l_prev_oiplip_elig_check.elig_per_opt_id,
p_elig_per_id => l_prev_oiplip_elig_check.elig_per_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_per_in_ler_id => l_per_in_ler_id,
p_elig_flag => 'Y',
p_prtn_strt_dt => null,
p_prtn_end_dt => null,
p_rt_comp_ref_amt => p_oiplip_rec.rt_comp_ref_amt,
p_rt_cmbn_age_n_los_val => p_oiplip_rec.rt_cmbn_age_n_los_val,
p_rt_comp_ref_uom => p_oiplip_rec.rt_comp_ref_uom,
p_rt_age_val => p_oiplip_rec.rt_age_val,
p_rt_los_val => p_oiplip_rec.rt_los_val,
p_rt_hrs_wkd_val => p_oiplip_rec.rt_hrs_wkd_val,
p_rt_hrs_wkd_bndry_perd_cd => p_oiplip_rec.rt_hrs_wkd_bndry_perd_cd,
p_rt_age_uom => p_oiplip_rec.rt_age_uom,
p_rt_los_uom => p_oiplip_rec.rt_los_uom,
p_rt_pct_fl_tm_val => p_oiplip_rec.rt_pct_fl_tm_val,
p_rt_frz_los_flag => 'N',
p_rt_frz_age_flag => 'N',
p_rt_frz_cmp_lvl_flag => 'N',
p_rt_frz_pct_fl_tm_flag => 'N',
p_rt_frz_hrs_wkd_flag => 'N',
p_rt_frz_comb_age_and_los_flag => 'N',
p_once_r_cntug_cd => p_oiplip_rec.once_r_cntug_cd,
p_comp_ref_amt => p_oiplip_rec.comp_ref_amt,
p_cmbn_age_n_los_val => p_oiplip_rec.cmbn_age_n_los_val,
p_comp_ref_uom => p_oiplip_rec.comp_ref_uom,
p_age_val => p_oiplip_rec.age_val,
p_los_val => p_oiplip_rec.los_val,
p_hrs_wkd_val => p_oiplip_rec.hrs_wkd_val,
p_hrs_wkd_bndry_perd_cd => p_oiplip_rec.hrs_wkd_bndry_perd_cd,
p_age_uom => p_oiplip_rec.age_uom,
p_los_uom => p_oiplip_rec.los_uom,
p_pct_fl_tm_val => p_oiplip_rec.pct_fl_tm_val,
p_frz_los_flag => 'N',
p_frz_age_flag => 'N',
p_frz_cmp_lvl_flag => 'N',
p_frz_pct_fl_tm_flag => 'N',
p_frz_hrs_wkd_flag => 'N',
p_frz_comb_age_and_los_flag => 'N',
-- p_wait_perd_cmpltn_dt => null,
p_wait_perd_cmpltn_date => null,
p_wait_perd_strt_dt => null,
--
-- Bugs : 1412882, part of bug 1412951
--
p_effective_date => l_effective_dt,
-- p_effective_date => p_effective_date,
p_object_version_number => l_prev_oiplip_elig_check.object_version_number,
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 => sysdate);
ben_icm_life_events.g_cache_epo_object(l_count_icm1).program_update_date :=sysdate;
hr_utility.set_location ('New update for elig per'||l_elig_per_id,101);
hr_utility.set_location ('Update mode'||l_datetrack_mode,102);
-- Bug 8542643: Update the still inelig records irrespective of whether
-- old value is greater or new value is greater for age or los
if p_comp_rec.age_val is not null or
p_comp_rec.los_val is not null
then
--
l_p_object_version_number := l_object_version_number;
-- same as start date, then update should be done in correction mode. In that case, the old
-- record should be inserted in the backup table
if g_debug then
hr_utility.set_location('ben_determine_eligibility2.check_prev_elig DTAPI_FDUM ', 889);
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;
/* Bug 9020962: If future eligibility record exists, then insert the record
in backup table ben_le_clsn_n_rstr */
IF (l_datetrack_mode = hr_api.g_update_override) AND
l_per_in_ler_id <> NVL(l_prev_per_in_ler_id,-1) THEN
--
open c_prev_pil(p_pil_row.per_in_ler_id);
ben_Eligible_Person_perf_api.update_perf_Eligible_Person
(p_validate => FALSE,
p_elig_per_id => l_elig_per_id,
p_per_in_ler_id => l_per_in_ler_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_elig_flag => 'N', --l_elig_flag,
p_prtn_strt_dt => l_prtn_strt_dt,
p_prtn_end_dt => null,
p_ler_id => p_ler_id, -- bug 5478994
p_rt_comp_ref_amt => p_comp_rec.rt_comp_ref_amt,
p_rt_cmbn_age_n_los_val => p_comp_rec.rt_cmbn_age_n_los_val,
p_rt_comp_ref_uom => p_comp_rec.rt_comp_ref_uom,
p_rt_age_val => p_comp_rec.rt_age_val,
p_rt_los_val => p_comp_rec.rt_los_val,
p_rt_hrs_wkd_val => p_comp_rec.rt_hrs_wkd_val,
p_rt_hrs_wkd_bndry_perd_cd => p_comp_rec.rt_hrs_wkd_bndry_perd_cd,
p_rt_age_uom => p_comp_rec.rt_age_uom,
p_rt_los_uom => p_comp_rec.rt_los_uom,
p_rt_pct_fl_tm_val => p_comp_rec.rt_pct_fl_tm_val,
p_rt_frz_los_flag => 'N',
p_rt_frz_age_flag => 'N',
p_rt_frz_cmp_lvl_flag => 'N',
p_rt_frz_pct_fl_tm_flag => 'N',
p_rt_frz_hrs_wkd_flag => 'N',
p_rt_frz_comb_age_and_los_flag => 'N',
p_once_r_cntug_cd => p_comp_rec.once_r_cntug_cd,
p_comp_ref_amt => p_comp_rec.comp_ref_amt,
p_cmbn_age_n_los_val => p_comp_rec.cmbn_age_n_los_val,
p_comp_ref_uom => p_comp_rec.comp_ref_uom,
p_age_val => p_comp_rec.age_val,
p_los_val => p_comp_rec.los_val,
p_hrs_wkd_val => p_comp_rec.hrs_wkd_val,
p_hrs_wkd_bndry_perd_cd => p_comp_rec.hrs_wkd_bndry_perd_cd,
p_age_uom => p_comp_rec.age_uom,
p_los_uom => p_comp_rec.los_uom,
p_pct_fl_tm_val => p_comp_rec.pct_fl_tm_val,
p_frz_los_flag => 'N',
p_frz_age_flag => 'N',
p_frz_cmp_lvl_flag => 'N',
p_frz_pct_fl_tm_flag => 'N',
p_frz_hrs_wkd_flag => 'N',
p_frz_comb_age_and_los_flag => 'N',
p_wait_perd_cmpltn_dt => l_wait_perd_cmpltn_dt,
p_wait_perd_strt_dt => l_wait_perd_strt_dt,
p_object_version_number => l_object_version_number,
p_effective_date => l_effective_dt,
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 => sysdate);
ben_icm_life_events.g_cache_pep_object(l_count_icm).program_update_date :=sysdate;
ben_icm_life_events.g_cache_pep_object(l_count_icm).p_datetrack_mode:= 'UPDATE';
p_program_update_date => sysdate
--
-- Bypass insert validate validation for performance
--
,p_override_validation => TRUE);
ben_icm_life_events.g_cache_pep_object(l_count_icm).program_update_date :=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;
/* Bug 9020962: If future eligibility record exists, then insert the record
in backup table ben_le_clsn_n_rstr */
IF (l_datetrack_mode = hr_api.g_update_override) AND
l_per_in_ler_id <> NVL(l_prev_per_in_ler_id,-1) THEN
--
open c_prev_pil(p_pil_row.per_in_ler_id);
ben_Eligible_Person_perf_api.update_perf_Eligible_Person
(p_validate => FALSE,
p_elig_per_id => l_elig_per_id,
p_per_in_ler_id => l_per_in_ler_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_elig_flag => l_elig_flag,
p_prtn_strt_dt => l_prtn_strt_dt,
p_prtn_end_dt => null,
p_ler_id => p_ler_id, -- bug 5478994
p_rt_comp_ref_amt => p_comp_rec.rt_comp_ref_amt,
p_rt_cmbn_age_n_los_val => p_comp_rec.rt_cmbn_age_n_los_val,
p_rt_comp_ref_uom => p_comp_rec.rt_comp_ref_uom,
p_rt_age_val => p_comp_rec.rt_age_val,
p_rt_los_val => p_comp_rec.rt_los_val,
p_rt_hrs_wkd_val => p_comp_rec.rt_hrs_wkd_val,
p_rt_hrs_wkd_bndry_perd_cd => p_comp_rec.rt_hrs_wkd_bndry_perd_cd,
p_rt_age_uom => p_comp_rec.rt_age_uom,
p_rt_los_uom => p_comp_rec.rt_los_uom,
p_rt_pct_fl_tm_val => p_comp_rec.rt_pct_fl_tm_val,
p_rt_frz_los_flag => 'N',
p_rt_frz_age_flag => 'N',
p_rt_frz_cmp_lvl_flag => 'N',
p_rt_frz_pct_fl_tm_flag => 'N',
p_rt_frz_hrs_wkd_flag => 'N',
p_rt_frz_comb_age_and_los_flag => 'N',
p_once_r_cntug_cd => p_comp_rec.once_r_cntug_cd,
p_comp_ref_amt => p_comp_rec.comp_ref_amt,
p_cmbn_age_n_los_val => p_comp_rec.cmbn_age_n_los_val,
p_comp_ref_uom => p_comp_rec.comp_ref_uom,
p_age_val => p_comp_rec.age_val,
p_los_val => p_comp_rec.los_val,
p_hrs_wkd_val => p_comp_rec.hrs_wkd_val,
p_hrs_wkd_bndry_perd_cd => p_comp_rec.hrs_wkd_bndry_perd_cd,
p_age_uom => p_comp_rec.age_uom,
p_los_uom => p_comp_rec.los_uom,
p_pct_fl_tm_val => p_comp_rec.pct_fl_tm_val,
p_frz_los_flag => 'N',
p_frz_age_flag => 'N',
p_frz_cmp_lvl_flag => 'N',
p_frz_pct_fl_tm_flag => 'N',
p_frz_hrs_wkd_flag => 'N',
p_frz_comb_age_and_los_flag => 'N',
p_wait_perd_cmpltn_dt => l_wait_perd_cmpltn_dt,
p_wait_perd_strt_dt => l_wait_perd_strt_dt,
p_object_version_number => l_object_version_number,
--
-- Bugs : 1412882, part of bug 1412951
--
p_effective_date => l_effective_dt,
-- p_effective_date => p_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 => sysdate);
ben_icm_life_events.g_cache_pep_object(l_count_icm).program_update_date :=sysdate;
ben_Eligible_Person_perf_api.update_perf_Eligible_Person
(p_validate => FALSE,
p_elig_per_id => l_elig_per_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_prtn_end_dt => (l_prtn_eff_strt_dt -1),
p_object_version_number => l_object_version_number,
p_effective_date => l_effective_dt, -- 4947426
p_datetrack_mode => hr_api.g_correction,
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 => 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;
/* Bug 9020962: If future eligibility record exists, then insert the record
in backup table ben_le_clsn_n_rstr */
IF (l_datetrack_mode = hr_api.g_update_override) AND
l_per_in_ler_id <> NVL(l_prev_per_in_ler_id,-1) THEN
--
open c_prev_pil(p_pil_row.per_in_ler_id);
ben_Eligible_Person_perf_api.update_perf_Eligible_Person
(p_validate => FALSE,
p_elig_per_id => l_elig_per_id,
p_per_in_ler_id => l_per_in_ler_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_elig_flag => l_elig_flag,
p_prtn_strt_dt => l_prtn_strt_dt,
p_prtn_end_dt => null,
p_ler_id => p_ler_id, -- bug 5478994
p_rt_comp_ref_amt => p_comp_rec.rt_comp_ref_amt,
p_rt_cmbn_age_n_los_val => p_comp_rec.rt_cmbn_age_n_los_val,
p_rt_comp_ref_uom => p_comp_rec.rt_comp_ref_uom,
p_rt_age_val => p_comp_rec.rt_age_val,
p_rt_los_val => p_comp_rec.rt_los_val,
p_rt_hrs_wkd_val => p_comp_rec.rt_hrs_wkd_val,
p_rt_hrs_wkd_bndry_perd_cd => p_comp_rec.rt_hrs_wkd_bndry_perd_cd,
p_rt_age_uom => p_comp_rec.rt_age_uom,
p_rt_los_uom => p_comp_rec.rt_los_uom,
p_rt_pct_fl_tm_val => p_comp_rec.rt_pct_fl_tm_val,
p_rt_frz_los_flag => 'N',
p_rt_frz_age_flag => 'N',
p_rt_frz_cmp_lvl_flag => 'N',
p_rt_frz_pct_fl_tm_flag => 'N',
p_rt_frz_hrs_wkd_flag => 'N',
p_rt_frz_comb_age_and_los_flag => 'N',
p_once_r_cntug_cd => p_comp_rec.once_r_cntug_cd,
p_comp_ref_amt => p_comp_rec.comp_ref_amt,
p_cmbn_age_n_los_val => p_comp_rec.cmbn_age_n_los_val,
p_comp_ref_uom => p_comp_rec.comp_ref_uom,
p_age_val => p_comp_rec.age_val,
p_los_val => p_comp_rec.los_val,
p_hrs_wkd_val => p_comp_rec.hrs_wkd_val,
p_hrs_wkd_bndry_perd_cd => p_comp_rec.hrs_wkd_bndry_perd_cd,
p_age_uom => p_comp_rec.age_uom,
p_los_uom => p_comp_rec.los_uom,
p_pct_fl_tm_val => p_comp_rec.pct_fl_tm_val,
p_frz_los_flag => 'N',
p_frz_age_flag => 'N',
p_frz_cmp_lvl_flag => 'N',
p_frz_pct_fl_tm_flag => 'N',
p_frz_hrs_wkd_flag => 'N',
p_frz_comb_age_and_los_flag => 'N',
p_wait_perd_cmpltn_dt => l_wait_perd_cmpltn_dt,
p_wait_perd_strt_dt => l_wait_perd_strt_dt,
p_object_version_number => l_object_version_number,
--
-- Bugs : 1412882, part of bug 1412951
--
p_effective_date => l_effective_dt,
-- p_effective_date => p_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 => sysdate);
ben_icm_life_events.g_cache_pep_object(l_count_icm).program_update_date :=sysdate;
ben_Eligible_Person_perf_api.update_perf_Eligible_Person
(p_validate => FALSE,
p_elig_per_id => l_elig_per_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_prtn_end_dt => l_prtn_eff_end_dt,
p_object_version_number => l_object_version_number,
-- p_effective_date => p_effective_date,
p_effective_date => l_effective_dt,
p_datetrack_mode => hr_api.g_correction,
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 => 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;
/* Bug 9020962: If future eligibility record exists, then insert the record
in backup table ben_le_clsn_n_rstr */
IF (l_datetrack_mode = hr_api.g_update_override) AND
l_per_in_ler_id <> NVL(l_prev_per_in_ler_id,-1) THEN
--
open c_prev_pil(p_pil_row.per_in_ler_id);
ben_Eligible_Person_perf_api.update_perf_Eligible_Person
(p_validate => FALSE,
p_elig_per_id => l_elig_per_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_per_in_ler_id => l_per_in_ler_id,
p_elig_flag => l_elig_flag,
p_prtn_strt_dt => l_prtn_strt_dt,
p_prtn_end_dt => null,
p_ler_id => p_ler_id, -- bug 5478994
p_rt_comp_ref_amt => p_comp_rec.rt_comp_ref_amt,
p_rt_cmbn_age_n_los_val => p_comp_rec.rt_cmbn_age_n_los_val,
p_rt_comp_ref_uom => p_comp_rec.rt_comp_ref_uom,
p_rt_age_val => p_comp_rec.rt_age_val,
p_rt_los_val => p_comp_rec.rt_los_val,
p_rt_hrs_wkd_val => p_comp_rec.rt_hrs_wkd_val,
p_rt_hrs_wkd_bndry_perd_cd => p_comp_rec.rt_hrs_wkd_bndry_perd_cd,
p_rt_age_uom => p_comp_rec.rt_age_uom,
p_rt_los_uom => p_comp_rec.rt_los_uom,
p_rt_pct_fl_tm_val => p_comp_rec.rt_pct_fl_tm_val,
p_rt_frz_los_flag => 'N',
p_rt_frz_age_flag => 'N',
p_rt_frz_cmp_lvl_flag => 'N',
p_rt_frz_pct_fl_tm_flag => 'N',
p_rt_frz_hrs_wkd_flag => 'N',
p_rt_frz_comb_age_and_los_flag => 'N',
p_once_r_cntug_cd => p_comp_rec.once_r_cntug_cd,
p_comp_ref_amt => p_comp_rec.comp_ref_amt,
p_cmbn_age_n_los_val => p_comp_rec.cmbn_age_n_los_val,
p_comp_ref_uom => p_comp_rec.comp_ref_uom,
p_age_val => p_comp_rec.age_val,
p_los_val => p_comp_rec.los_val,
p_hrs_wkd_val => p_comp_rec.hrs_wkd_val,
p_hrs_wkd_bndry_perd_cd => p_comp_rec.hrs_wkd_bndry_perd_cd,
p_age_uom => p_comp_rec.age_uom,
p_los_uom => p_comp_rec.los_uom,
p_pct_fl_tm_val => p_comp_rec.pct_fl_tm_val,
p_frz_los_flag => 'N',
p_frz_age_flag => 'N',
p_frz_cmp_lvl_flag => 'N',
p_frz_pct_fl_tm_flag => 'N',
p_frz_hrs_wkd_flag => 'N',
p_frz_comb_age_and_los_flag => 'N',
p_object_version_number => l_object_version_number,
--
-- Bugs : 1412882, part of bug 1412951
--
p_effective_date => l_effective_dt,
-- p_effective_date => p_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 => sysdate,
p_inelg_rsn_cd => p_inelg_rsn_cd);
ben_icm_life_events.g_cache_pep_object(l_count_icm).program_update_date :=sysdate;
p_program_update_date => sysdate,
p_inelg_rsn_cd => p_inelg_rsn_cd
--
-- Bypass insert validate validation for performance
--
,p_override_validation => TRUE
);
ben_icm_life_events.g_cache_pep_object(l_count_icm).program_update_date :=sysdate;