[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
person_selection_rule_id and
comp_selection_rule_id in to
called PL/SQL process.
14 Jan 98 G Perry 110.6 Added caching of comp object
types and messages.
14 Jan 98 T Mathers 110.7 Added calls to Pate04r5.
15 Jan 98 G Perry 110.8 Changed cache objects
variables to match real
lookup codes.
15 Jan 98 G Perry 110.9 Changed p_validate from a
boolean to a character due
to SRS not supporting boolean.
15 Jan 98 lmcdonal 110.10 Added call to
determine_eligibility.
16 Jan 98 G Perry 110.11 Added retcode and errbuf
which are needed by SRS.
18 Jan 98 G Perry 110.12 Added in real messages now
seeded in seed11.
19 Jan 98 lmcdonal 110.13 Created globals for person,
assignment and life event name.
Add get_ler_name.
21 Jan 98 G Perry 110.14 Created globals
g_last_person_failed
g_last_plan_failed
g_last_prog_failed
to handle propergation of comp
object failures.
22 Jan 98 lmcdonal 110.15 Uncomment determine elig.
24 Jan 98 G Perry 110.16 Fixed call to determine elig
so runs in every mode bar
derivable factor mode.
Added id function which
returns a bracketed number.
25 Jan 98 G Perry 110.17 Added banner for lers.
25 Jan 98 G Perry 110.18 Added app_exception exception
handler for row handler errors.
25 Jan 98 lmcdonal 110.19 Add setting of g_last...failed
to exception routine.
Change 6 locals from number(38)
to number.
26 Jan 98 lmcdonal 110.20 Call determine elig and evaluate
lers only if person has changed.
27 Jan 98 G Perry 110.21 Set g_person_changed so that it
takes into account when we get
several identical person_ids
and ler_ids.
27 Jan 98 G Perry 110.22 Changed commit_all_data proc.
02 Feb 98 G Perry 110.23 Moved Commit into C code and
passed p_validate to C code.
02 Feb 98 G Perry 110.24 Changed exception app_exception
call so it just raises an error.
03 Feb 98 G Perry 110.25 Completely removed
app_exception call and capture
via when others call.
06 Mar 98 G Perry 110.26 Removed commit_all_data procedure
Added log_benmngle_statistics
Restart, removed lots of logging
messages. Chng looping mechanism
for comp objects so they are done
in one call rather than lots of
seperate calls. Added lots of
caching routines.
16 Mar 98 G Perry 110.27 Added prtn_eff_strt_dt_rl and
prtn_eff_end_dt_rl into object
cache structure for pl,pgm,oipl.
08 Apr 98 G Perry 110.28 Added all multithread logic into
package. Added Conc defintion
to allow restart and multithread.
Cached objects only once now.
09 Apr 98 G Perry 110.29 Backport for BD2.
09 Apr 98 G Perry 110.30 remove commit in logging part.
13 Apr 98 G Perry 110.31 Added logging to PLSQL.
20 Apr 98 G Perry 110.32 Fixed cache errors.
Added error handling to trap
where error occured.
Added exception g_life_event
_after Clears cache each time.
21 Apr 98 G Perry 110.33 Fixed problem with restart.
18 May 98 G Perry 110.34 dbsynch up.
21 May 98 jcarpent 110.35 Added determine enrolment rqmts
27 May 98 G Perry 110.36 Added in fast formula cover calls.
27 May 98 jcarpent 110.38 Added elig_per_elctbl_chc_id arg.
04 Jun 98 G Perry 110.39 Added in reference to fte_value
and total_fte_value. This way we
can cache full time equivalents
this can be used by BENDRPAR.
04 Jun 98 jcarpent 110.40 Restricted call to bendenrr.
04 Jun 98 G Perry 110.41 Fixed compilation error and
moved output string to correct
position following change 110.40
05 Jun 98 G Perry 110.42 Add last place variable to
call before deenrollment.
07 Jun 98 G Perry 110.43 Changed name of package/proc for
dependent eligibility Added
global for last_pgm_id.
Fixed elig_to_prte_rsn_f bug,
this is only derived in life
event mode.
07 Jun 98 Ty Hayden 110.44 Added call to ben_determine_coverage
07 Jun 98 G Perry 110.46 Added in scheduled enrollment
logic.
08 Jun 98 G Perry 110.47 Removed exit statement.
08 Jun 98 G Perry 110.48 Fixed skipping of comp objects
when eligibility fails for a plan
or program.
11 Jun 98 G Perry 110.49 Including logic for temporal
life event processing.
12 Jun 98 Ty Hayden 110.50 Added call to ben_determine_rates
12 Jun 98 G Perry 110.51 Fixed error when defaulting
person types for contacts.
13 Jun 98 G Perry 110.52 Added Message names
Added parameter output.
14 Jun 98 G Perry 110.53 Added procedure
ben_determine_elct_chc_flx_imp.
14 Jun 98 G Perry 110.54 Improved life event cursor.
18 Jun 98 G Perry 110.55 Fixed when ineligible for
program ineligible for plan.
Fixed so that scheduled life
event is not commited.
08 Jul 98 jcarpent 110.56 Added p_popl_enrt_typ_cycl_id arg
21 Jul 98 G Perry 110.57 Fixed c_person_life cursor so
subselect only picks up detected
or unprocessed life events.
30 Jul 98 G Perry 110.58 Fixed cursor for percent full
time stuff from budget values.
Removed hr_lookups from
assignment cursor, this is not
needed.
03 Aug 98 F Martin 110.59 Reordered queries for greater
efficiency. These now utilise
more efficient indexes.
06 Aug 98 G Perry 110.60 Added check_business_rules
procedure to easily collate all
business rules.
Fixed bugs 490,491,492,493,494
10 Aug 98 G Perry 110.61 Added in concurrent request
columns to create_benefit
_actions_api call.
11 Aug 98 G Perry 110.62 Validate mode now multi-threads.
Moved person_actions code to
new module benbatpe.pkb.
24 Aug 98 G Perry 110.63 Added new globals to structure
for use in bendenrr.
26 Aug 98 G Perry 115.33 Moved person_selection_rule
into benbatpe.pkb.
Fixed wwbug 1129.
27 Aug 98 G Perry 115.34 Fixed bug 608.
problem was outer-join to
per_assignment_budget_values.
28 Aug 98 G Perry 115.35 Added check to test for whether
electable choices were written
for a life event that was being
processed.
28 Aug 98 G Perry 115.36 Removed logic where we store all
old potential life event info.
23 Sep 98 G Perry 115.37 Split up code so Prasad can do
his whatif functionality in
stages.
24 Sep 98 G Perry 115.38 Added in code to copy action id
to global structure for Prasad.
07 Oct 98 G Perry 115.39 Corrections following schema
issues. Added slave logic.
15 Oct 98 G Perry 115.40 Added new cache info for oipl.
20 Oct 98 J Carpenter 115.41 Added new cache info for pl+pgm.
20 Oct 98 J Carpenter 115.42 Added new columns to pgm cache
24 Oct 98 T Guy 115.43 Moved call to ben_determine_rate
inside people loop. Added column
person_id to call.
25 Oct 98 G Perry 115.45 Added ben_generate_communications
26 Oct 98 G Perry 115.46 Added hourly_salaried_code to
assignment select to go in cache
30 Oct 98 G Perry 115.47 Added in logic for unrestricted
enrollment and multiple scheduled
enrollment.
31 Oct 98 G Perry 115.49 Fixed error messages.
02 Nov 98 G Perry 115.50 Used new call to get parameters.
10 Nov 98 G Perry 115.51 Validate mode now regularly
commits to the logfile. All
modes now use true
multithreading. Made master
process drive from a cursor
rather than a global. Used
new mathod of writing to cache
structure rather than
ben_reporting table.
23 Nov 98 G Perry 115.52 Added support for rates and
factors which means that call
to determine_elig_prfls and
derive_rates_and_factors have
new parameters.
07 Dec 98 G Perry 115.53 Added support for object
selection rule.
17 Dec 98 G Perry 115.54 Added caching logic for logging
info.
21 Dec 98 G Perry 115.55 Fixed cussor to just bring back
unrestricted for unrestricted
mode.
22 Dec 98 T Guy 115.56 Added ben_determine_rate_chg
28 Dec 98 J Lamoureux 115.57 Commented out nocopy parameters dflt_
enrt_dt and elcn_made_dt in call
to person_life_event.
31 Dec 98 G Perry 115.58 Added people group to cache
and commented out nocopy rate change
call. tanie to fix.
01 Jan 99 G Perry 115.59 Global g_number_proc_objects was
not being reset to 0 in multi-
thread process.
02 Jan 99 G Perry 115.60 Added call to ben_cel_cache proc.
02 Jan 99 G Perry 115.61 Fixed cache_person_ler_information.
18 Jan 99 G Perry 115.62 LED V ED
18 Jan 99 T Guy 115.63 added lf_evt_ocrd_dt to rate change
call.
25 Jan 99 G Perry 115.64 Added p_mode variable so that
we only call certain stuff under
certain conditions. Makes the
run faster.
08 Feb 99 G Perry 115.65 Added in parameter
ptnl_ler_trtmt_cd in call to
derive rates and factors.
11 Feb 99 G Perry 115.66 Fixed error trace message prior
to evaluate life events call.
17 Feb 99 G Perry 115.67 Added once_r_cntug_cd and elig
flag param to determine_elig_
prfls and derive_part_and_rate_
facts.
18 Feb 99 G Perry 115.68 Support for canonical dates.
24 Feb 99 G Perry 115.69 Support for restart process.
26 Feb 99 T Guy 115.70 Added ben_determine_chc_ctfn
27 Feb 99 G Perry 115.71 Added Concurrent request id to
log. Added business_group_id
parameter to check_all_slaves
_finished procedure.
03 Mar 99 G Perry 115.72 Fixed unrestricted mode so it
creates batch_ler_info record.
09 Mar 99 G Perry 115.73 Changed c_pln cursor so it
picks up all plans if none are
specified for an enrollment
period. Bug 1924.
15 Mar 99 G Perry 115.75 Changed parameter order of
process procedure per bug 1529.
22 Mar 99 Tmathers 115.76 Changed -MON- to /MM/
05 Apr 99 mhoyes 115.78 - Un-datetrack of per_in_ler_f changes
- Removed DT restriction from
process_rows/c_ler_exists.
- Modified calls to per_in_ler and
ptnl_ler_for_per APIs
16-mar-99 pbodla 115.79 - If the supplied mode and mode of
winning life event differ, raise
error. p_enrt_perd_id parameter
added to create_ptnl_ler_for_per
in C mode.
21-APR-99 mhoyes 115.80 - Added p_popl_enrt_typ_cycl_id to
all calls to create_ptnl_ler_for_per
22-APR-99 GPerry 115.81 Added call set_potential_ler_id.
for temporal mode.
22-APR-99 mhoyes 115.82 - Modified call to
create_ptnl_ler_for_per and
update_ptnl_ler_for_per
23-APR-99 GPerry 115.83 Added p_mode to evaluate life
event call.
22-APR-99 mhoyes 115.84 Modified per in ler calls.
29-APR-99 shdas 115.85 Added to parameter list of
benutils.formula.
06-May-99 TGuy 115.86 uncommented rate change call
06-MAY-99 shdas 115.87 Added jurisdiction_code.
06-May-99 bbulusu 115.89 Added original_hire_date to the
person cache.
06-May-99 GPerry 115.90 Backport for Fidelity.
06-May-99 GPerry 115.91 Leapfrog from 115.89
10-May-99 GPerry 115.92 Backport Fix.
10-May-99 GPerry 115.93 Leapfrog from 115.91
14-May-99 GPerry 115.94 Support for PLIP and PTIP.
15-May-99 GPerry 115.95 Fix for Bug 2107. Ensure only
active comp objects are included.
18-May-99 GPerry 115.96 Added calls to environment routine.
Now we capture the message correctly
through the app_exception and
g_record_error exceptions.
lmcdonal 115.97 Added rudimentary p_comp_object_name
for ptip and plip. Also increase
length of comp object name in log.
20-May-99 jcarpent 115.98 Change enrt_perd_for_pl query to
handle no record condition and
allow no rows for flex and impt inc
27-May-99 G Perry 115.99 Changed c_pgm cursor so we pick
up cobra programs last.
02-May-99 bbulusu 115.100 Added 3 columns to c_person cursor
to fetch into the g_cache_person.
06-Jun-99 stee 115.101 Added 3 new cursors,c_pgm2,c_pln2,
c_oipl2 to build_comp_object
to process the COBRA program last.
09-Jun-99 stee 115.102 Only process COBRA last if in
life event or unrestricted mode.
16-Jun-99 bbulusu 115.103 Added call to ben_person_object to
cache info for person being procssed
18-Jun-99 G Perry 115.104 Removed FTE code and derived factor
life event stuff. This is all
cache on demand in the
ben_person_object and
ben_seeddata_object packages.
21-Jun-99 mhoyes 115.105 Added new trace messages.
23-Jun-99 G Perry 115.106 Added resetting calls to setenv
so that flags are reset correctly.
25-Jun-99 G Perry 115.107 Added call to ben_life_object.
Removed ben_person_object routine
as cache is cache on demand.
25-Jun-99 G Perry 115.108 Added p_rec to ben_comp_object.
set_object call.
28-Jun-99 mhoyes 115.109 Added new trace messages.
28-Jun-99 tguy 115.110 Added new call to
ben_determine_actual_premium.main
01-JUL-99 pbodla 115.111 Changes related with << Life Event Collision >>
added call to get_ori_bckdt_pil()
added call to p_lf_evt_clps_restore()
05-JUL-99 mhoyes 115.112 - Externalised c_pln_nip to
ben_pln_cache.nipplnpln_getdets.
- Added new trace messages.
07-JUL-99 mhoyes 115.113 - Backed out nocopy c_pln_nip cache.
07-JUL-99 shdas 115.114 Added setenv for business_group in evaluate_life_events.
08-JUL-99 mhoyes 115.115 - Added new trace messages.
09-JUL-99 mhoyes 115.116 - Added new trace messages.
- Removed + 0s from all cursors.
19-JUL-99 stee 115.117 - Fixed c_pln cursor to limit
check for enrd_perd_for_pl by
business_group_id. Also, do
not call actual premium if
no choices are created.
20-JUL-99 GPerry 115.118 genutils -> benutils package rename
26-JUL-99 GPerry 115.119 Removed ben_timing stuff.
26-JUL-99 GPerry 115.120 Added calls to dt_fndate.
Added call to new
create_perf_benefit_actions to
reduce bottleneck.
Added call to clear_down_cache in
benutils package.
26-JUL-99 GPerry 115.121 Removed setenv for business_group
in evaluate_life_events.
27-JUL-99 mhoyes 115.122 - Fixed genutil problems.
29-JUL-99 mhoyes 115.123 - Assigned globals to locals in
process_comp_objects.
- Added new trace messages.
- Modified references to benutils.
to bentype.
10-AUG-99 Gperry 115.124 Removed global references to
g_cache_person and the like.
Rewrote person_header.
24-AUG-99 Gperry 115.125 Fixed p_no_plans flag so it
only applies to plans not in
programs.
26-AUG-99 Gperry 115.126 Added call to benefits assignment
if employee assignment is null.
31-AUG-99 Gperry 115.127 Changed call to ptnl_ler_for_per.
31-AUG-99 mhoyes 115.128 - Added new trace message.
01-SEP-99 Gperry 115.129 Changed call to determine
eligibility.
14-SEP-99 Gperry 115.130 Fixed bug 2907. Life event occurred
date is now passed to automatic
enrollment routine.
15-SEP-99 Gperry 115.131 Added audit log flag to main
process procedure.
28-SEP-99 Stee 115.132 Changed where clause to select
COBRA pgms with a pgm_typ like
'COBRA%' instead of COBRA.
28-SEP-99 tguy 115.133 Moved closing of per_in_ler to
after rate change check. This
allows us to avoid duplication
and throughing off counts for
reporting purposes
04-OCT-99 mhoyes 115.134 - Tuned build_comp_object_list.
Replaced c_pln and c_oipl with
bgpcpp_getdets and bgpcop_getdets.
04-OCT-99 stee 115.135 - Added ptip_id to
g_cache_person_prtn.
04-OCT-99 mhoyes 115.136 - Added calls to flush_global_structures
to clear build_comp_object_list
plip and oipl caches.
08-OCT-99 jcarpent 115.137 - Moved auto_enrt to after rates
are created. Fixed some calling_
proc tokens
13-OCT-99 gperry 115.138 Fix bug 2703.
13-OCT-99 gperry 115.139 Fix bug 2960.
25-OCT-99 gperry 115.140 Fix bug 3103.
03-NOV-99 mhoyes 115.141 - Added comp object filtering
to improve performance. After
BENDETEL when the comp object
for the person is still in-eligible
or first time in-eligible then all
other processing in the loop is
skipped.
13-NOV-99 mhoyes 115.142 - Added trace messages.
14-NOV-99 gperry 115.143 Added mode to ben_batch_reporting
package call.
19-NOV-99 GPERRY 115.144 Added new flags.
22-NOV-99 PBODLA 115.145 Bug : 3511 : When a potential already
sitting and benmngle is ran in scheduled
mode with a effective date other than
life event occured date a duplicate
potential is being created and benmngle
is stopping. Use lf_evt_ocrd_dt instead
of effective_date in the where clause
of c_ler_exists.
24-NOV-99 mhoyes 115.146 - Bug : 3511 : Modified c_ler_exists
to restict by not in VOIDD and PROCD
rather than only DTCTD or UNPROCD
- Removed obsolete c_ler_exists cursor
from c_ler_exists.
24-NOV-99 jcarpent 115.147 - Added more bendenrr globals so pil can
be closed when no auto/electable epes
are created.
12-DEC-99 pbodla 115.148 - Modified c_ler_exists to look for
DTCTD or UNPROCD ( chaged not in to in
part of where clause)
22-DEC-99 gperry 115.149 Modified create_life_person_actions
call which occurred due to fix in
1096742.
30-DEC-99 maagrawa 115.150 Added parameter business_group_id.
to ben_determine_rate_chg.main
03-FEB-00 mhoyes 115.156 - Added HPAPRTTDE and HPADPNTLC to
call to generate communications.
- Modified process_comp_object to
process local l_comp_obj_tree.
- Fixed problem with app_exception. The
error from process_life_events is
now suppressed rather than raised. The
error is raised when max errors is
reached.
- Rolled back to pre-filtering.
07-FEB-00 mhoyes 115.157 - Modified/Added trace messages for
profiling.
- Fixed bug 1178659. Error was being
raised from the g_record_error
exception because life event occured
date had not been derived. Set the
life event occured date to the
effective date.
10-FEB-00 mhoyes 115.158 - Fixed bug 1169238. The eligibility
profile cache was not being cleared
down from build_comp_object_list.
Called ben_elp_cache.clear_down_cache
when comp object list is re-built.
- Added clear_init_benmngle_caches to
clear caches and globals at the
benmngle level. This should also be
called from on-line benmngle.
- Added call to
ben_person_object.clear_down_cache
from flush_global_structures. This
means that person cache information
is cleared between multiple benmngle
runs.
21-Feb-00 lmcdonal 115.159 load ptip id thru calls to setenv.
Bug 1179550.
25-Feb-00 mhoyes 115.160 - Added trace messages.
- Bug 1179550. Nullified ptip id and
plip_id values to avoid multiple
program issues.
- Revamped elig flags to uses a local
record structure rather than globals.
- Passed p_comp_obj_tree_row into
determine_eligibility. Currently
dual supporting ben_env_object
comp object globals in parallel
with p_comp_obj_tree_row. Bendrpar
totally uses p_comp_obj_tree_row but
bendetel, bendete2 and benwtprc still
need to be moved over.
27-Feb-00 stee 115.161 - Added new parameter,
p_cbr_tmprl_evt_flag.
28-Feb-00 stee 115.162 - Pass p_cbr_tmprl_evt_flag to
update benefits action.
28-Feb-00 stee 115.163 - Pass p_cbr_tmprl_evt_flag to
all relevant procedures.
01-MAR-00 pbodla 115.164 - Bug 4186 : Added csd_by_ptnl_ler_for_per
to create_related_person_ler call.
01-MAR-00 pbodla 115.165 - Do not run restore if electable choices
are not created
01-MAR-00 pbodla 115.166 - Bug : 4293/1172230 : Message to indicate
in what mode benmngle to run in case the
supplied mode is different.
02-MAR-00 stee 115.167 - Update COBRA qualified beneficiary
information if COBRA electable
choices exist.
03-MAR-00 mhoyes 115.168 - Phased out nocopy ben_env_object for comp
objects.
04-MAR-00 mhoyes 115.169 - Added comp object cache parameters to
load_cache.
- Stored parent comp object ID values
on the comp object cache.
- Added support for elig_tran_state.
Phased out nocopy first_inelig and
still_inelig flags.
04-MAR-00 mhoyes 115.170 - Modified build_comp_object_list to
process one comp object per cache
row.
- Revamped comp object list to reflect
the new cache struture.
04-MAR-00 mhoyes 115.171 - Fixed problem with parent elig flag
at ptip and plip levels.
05-MAR-00 mhoyes 115.172 - Fixed problem with cobra oipls
in build comp object list. The
parent comp object information
was not being populated.
05-MAR-00 stee 115.173 - Update cobra eligibility
only in life event or
unrestricted mode.
06-MAR-00 gperry 115.174 Changed process_life_events to use
nocopy so that we can use locals
to trap the number of errors.
Also made sure that when max errors
is hit the process stops.
07-MAR-00 mhoyes 115.175 - Moved build comp object list out
to ben_comp_object_list package
file bebmbcol.pkh.
08-MAR-00 mhoyes 115.176 - Added trace messages for profiling.
13-MAR-00 mhoyes 115.177 - Phase 5 performance. Implemented
PTIP level comp object filtering
in process_comp_objects.
14-MAR-00 mhoyes 115.178 - Phase 7 performance. Implemented
PLIP level comp object filtering
in process_comp_objects.
- Initialised comp object cache
global from
clear_init_benmngle_caches.
- Phase 8 performance. Implemented
plan and oipl level comp object
filtering in process_comp_objects.
- created set_up_cobj_part_elig and
moved set_up_part_elig from
build comp object list to
process comp objects.
15-MAR-00 mhoyes 115.179 - Modified get_comp_object_name so
that comp object ids are not
truncated from the audit log.
17-MAR-00 mhoyes 115.190 - Fixed eligible parent flag problems.
18-MAR-00 mhoyes 115.181 - Fixed filtering problems at PTIP
and PLIP levels.
21-MAR-00 pbodla 115.182 - Bug : 4919 : c_ler_exists modified
; do not create potential even if a
10-Apr-00 stee 115.195 Only update cobra eligibility
dates if event is a cobra qualifying
event.
12-Apr-00 mhoyes 115.196 - Fixed 5075. Initialised eligible flag
on the duplicate PTIP global in
check_dupproc_ptip so that
it defaults to false. This prevents
parent eligibility flags being set
incorrectly when the PTIP is filtered.
Previously un-initialised the value
defaulted to true causing the PTIP
to be filtered and the plip below to be
processed.
14-Apr-00 pbodla 115.197 - Fixed 5093 : When unrestircted ptnl's
created notification date is populated.
14-Apr-00 stee 115.198 Update cobra info if mode is
Unrestricted( for core customers).
21-Apr-00 gperry 115.199 Fixed bug 5062 by testing for
whether a PTIP has been found when
filtering happened at the PTIP
level.
01 May 00 pbodla 115.200 - Task 131 : Elig dependent rows are
created before creating the electable
choice rows. Called procedures
ben_determine_dpnt_eligibility.main()after
calling ben_determine_eligibility.determine_elig_prfls.
p_upd_egd_with_epe_id called after
electable choice is created to set
electable choice id on dependent rows.
12 May 00 mhoyes 115.201 - Pulled filtering out nocopy of benmngle into
ben_comp_obj_filter.
12 May 00 stee 115.202 - Fix c_get_inelig_dpnt_info cursor.
15 May 00 mhoyes 115.203 - Called get_comp_object_name
procedure only when audit log flag
is Y.
22 May 00 mhoyes 115.204 - Revamped set_up_cobj_part_elig so that
cached current and parent rows are
passed down through bendetel and
bendete2.
23 May 00 mhoyes 115.205 - Passed p_comp_obj_tree_row into
enrolment_requirements.
24 May 00 mhoyes 115.206 - Called ben_pil_object.clear_down_cache
from flush_global_structures.
26 May 00 pbodla 115.207 - Bug 5123 : Added p_run_mode, p_enrt_perd_id
parameters to determine_elig_prfl proc.
31-May-00 gperry 115.208 BP of 115.198 with thread fix.
31 May 00 mhoyes 115.209 - Passed through cuurent comp object rows
into bendenrr.
05 Jun 00 stee 115.210 - Move electable chc certification
process into the comp object loop.
wwbug #1308629.
22 Jun 00 kmahendr 115.211 - Added a cursor in process_comp_objects procedure
to check whether pil_elctbl_popl_stat is to be
updated to PROCD - wwbug #1277369
26 Jun 00 stee 115.212 - Change p_derivable_factors to a
code.
27 Jun 00 mhoyes 115.213 - Fixed temporal mode audit log problem.
- Moved person cache calls out nocopy of bendetel
and into process_comp_objects.
28 Jun 00 jcarpent 115.214 - Bug 5176, 1329041. Pass more args
to bebmfilt.pkb filter_comp_objects.
29 Jun 00 mhoyes 115.215 - Passed mode_cd to create_normal_person_actions.
05 Jul 00 mhoyes 115.216 - Passed context parameters through to bendrpar.
10 Jul 00 mhoyes 115.217 - Bypassed filtering in temporal mode.
13 Jul 00 mhoyes 115.218 - Removed context parameters.
19 Jul 00 jcarpent 115.218 - 5241,1343362. Added update_defaults
26 Jul 00 gperry 115.219 Fixed BUG 5422/1365397.
SCheduled enrollment now voided
when no changes made to elig,
elect, rates, etc.
02 Aug 00 mhoyes 115.220 - Moved call to set_temporal_ler_id
outside of process_comp_object loop.
24 Aug 00 mhoyes 115.221 - Memory leaking fixes for bug 1387371.
05 Sep 00 pbodla 115.222 - Bug 5422 : Allow different enrollment periods
for programs for a scheduled enrollment.
p_popl_enrt_typ_cycl_id is removed.
06 Sep 00 jcarpent 115.223 - Leapfrog version based on 115.221
Fixes bug 1398444. Participation
cache was not cleared between people
06 Sep 00 jcarpent 115.224 - Merged version of 115.222 with 115.223.
18-Sep-00 pbodla 115.225 - Healthnet changes : PB : Added parameter
p_lmt_prpnip_by_org_typ_id to
Comp objects are now selected based on person's
organization id if p_lmt_prpnip_by_org_typ_id is
Y.
22-Sep-00 gperry 115.226 Added back param
p_popl_enrt_typ_cycl_id as otherwise
do_multithread bombs.
WWBUG 1412825.
25-Sep-00 gperry 115.227 Added call to reset life event
occurred date when BENMNGLE errors
in eveluate_life_events procedure.
WWBUG 1412614.
26-Sep-00 gperry 115.228 Fixed WWBUG 1412808.
Clear pil_cache for cases where a
backout may have occurred that
called communications.
27 Sep 00 pbodla 115.229 - Code added due to BUG 5422/1365397,
in version 115.219 is removed.
- Pil no longer required to be voided
if there is no impact of it on
eligibility, rates etc.,. It should
become processed.
05 Oct 00 gperry 115.230 Fixed call to ben_determine_eligibility
so that it passes the life event
occurred date as p_effective_date
when run in selection mode. This
is due to how least work in bendete2
for Prasads fixes.
06 Oct 00 pbodla 115.231 -- Above fix is removed as, the
fix should be in bendete2.pkb
13 Oct 00 rchase 115.232 ensure caches are cleared properly
when processing comp objects.
19 OCT 00 rchase 115.233 wwBug1427383 - return correct date for
creating dependent benefit assignment
07 Nov 00 mhoyes 115.234 - Added comp object loop electable choice
context global.
- Added clear down cache call for the
electable choice list.
08 Nov 00 vputtiga 115.235 - Fixed Bug 1485814.
g_enrollment_change global set to FALSE.
added a call at end of process_comp_objects to
ben_prtt_enrt_result.update_person_type_usages
Leapfrog based on 115.233.
15 Nov 00 jcarpent 115.236 Merged version of 115.235 and 115.234
22 Nov 00 tmathers 115.238 Merged version of 115.236 and 115.237
05 Jan 01 kmahendr 115.239 Unrestricted life event process changes
16 Jan 01 mhoyes 115.240 - Raised the oracle error code for the
generic 91665 error.
18 Jan 01 mhoyes 115.242 - Leapfrog of 115.240.
23 Jan 01 mhoyes 115.242 - Modified the call to coverage for EFC.
22 Mar 01 mhoyes 115.244 - Fixed multi-threading problem with
g_ler_id which was only being set in
the master and not the slaves.
28 Mar 01 gvenkata 115.245 - Bug 1696526. Was using lf_evt_ocrd_dt
from previous person for caching assignment.
- Bug 1636071. In call to benmgle from
benauthe g_modified_mode was not being
used so open events were not working.
09 Apr 01 kmahendr 115.246 - Bug 1543462 - If life event is opened and closed
in the same run, benmngle is to use least of sysdate or
effective_date to update processed date for active
life event
23 May 01 mhoyes 115.247 - Added refresh cache call for benelmen cache.
01 Jul 01 kmahendr 115.248 - Unrestricted process changes
18 Jul 01 ikasire 115.249 Commented the call to default enrollment
see details in bug 1874263 or in this
package at the place of call to
ben_manage_default_enrt.Process_default_enrt
19 Jul 01 kmahendr 115.250 - Bug#1871579- Effective date is greater
than unrestricted life event started date
benmngle errors out. made changes to
call ben_person_object.get_object and
ben_determine_derive_factors
30 Jul 01 pbodla 115.251 - Bug : 1894718 Also treat the electable
choices associated with suspended
enrollments as in pending work flow.
In other words do not delete the electable
choices data and other data if the
enrollment is in suspended state.
02-Aug-01 ikasire 115.251 Bug 1895846 added two new procedures
delete_in_pndg_elig_dpnt and
reset_elctbl_chc_inpng_flag
see bug for more details
28-Aug-01 kmahendr 115.252 updating of ben_enrt_rt in U mode
removed - Bug#1936976
11-Aug-01 kmahendr 115.253 Bug#1900657-Added codes in procedure
process_comp_objects
25-Sep-01 kmahendr 115.254 Made changes for R mode - private
procedure update_elig_row added
26-Sep-01 kmahendr 115.255 In R mode report for error not called
26-Sep-01 kmahendr 115.256 Only Activity Summary not called
05-Oct-01 kmahendr 115.257 Bug#2032672-added l_mode = C before
checking of electbl choice created
call
08-Oct-01 kmahendr 115.259 115.257 brought forward as version
115.258 was a leap frog version of
115.251 and fix of 115.257
26-Oct-01 maagrawa 115.260 Backported to 115.252
Do not create choices for "Save for
Later" ICD entries.
26-Oct-01 maagrawa 115.261 115.259 + 115.260.
09-Nov-01 mhoyes 115.262 - Fixed 2105125. Added hint to always
use index in cursor c_range_thread.
This avoids a full table lock on
ben_batch_ranges when running
multi-threaded.
30-Nov-01 mhoyes 115.263 - Made p_benefit_action_id in/out on
process.
03-Dec-01 mhoyes 115.264 - Re-enabled mode validation.
03-Dec-01 mhoyes 115.265 - dbdrv line.
04-Dec-01 kmahendr 115.266 - Bug#2097833 - parameter -p_per_in_ler_id
is added to ben_determine_dpnt_eligibility.
main call
06-Dec-01 mhoyes 115.267 - Fixed concurrent manager problem
with new CAGR OUT NOCOPY parameter on process.
Added new routine inner_process.
07-Dec-01 mhoyes 115.268 - Passed p_per_in_ler_id to
enrolment_requirements.
11-Dec-01 mhoyes 115.269 - Passed p_per_in_ler_id to
update_defaults.
19-Dec-01 pbodla 115.270 - CWB Changes : New mode W added for
processing comp work bench events.
27-Dec-01 pbodla 115.271 - CWB Changes : Added
popu_cross_gb_epe_data - to create
pil, epe, pel, ecr data for managers
who are in different business group
ikasire 115.271 - CWB Changes : Added popu_epe_heir
to populate performace table.
04-Jan-02 pbodla 115.272 - CWB Changes : Call procedure
popu_cross_gb_epe_data only in W
mode
07-Jan-02 rpillay 115.273 - Added Set Verify Off.
07-Jan-02 pbodla 115.274 - CWB Changes : Also delete the rows
for employees who do not have
subordinates and with level -1
08-Jan-02 ikasire 115.275 Bug 2172031 changes the order of
parameters in cwb_process
11-Jan-02 ikasire 115.276 Added a new public procedure
rebuild_heirarchy for rebuilding
the CWB hierarchy.
11-Jan-02 ikasire 115.277 More changes to popu_epe_heir and
rebuild_heirarchy
11-Jan-02 ikasire 115.278 Bug 2172036 changed a call to
after making it from function to
procedure with extra assignment_id
column.
18-Jan-02 mhoyes 115.279 - Fixed restart null benefit action
problem. Introduced by CAGR.
01-Feb-02 stee 115.280 - Update cobra information for
a life event type of 'ENDDSBLTY'.
Bug#2068332.
12-Feb-02 mhoyes 115.281 - Added write_bft_statistics and
init_bft_statistics.
- Moved delete_elctbl_choice and
update_in_pend_flag to
ben_manage_unres_life_events.
12-Feb-02 mhoyes 115.282 - Removed dbms_output.
15-Feb-02 rpillay 115.283 - Bug# 2214961 removed check for
Ineligibility (l_continue_loop)
for CWB (l_mode = 'W')
19-Feb-02 ikasire 115.284 Bug 2172036 and 2231371 fixes
popu_cross_gb_epe_data was not
passing assignment_id in the
creation of epe
27-Feb-02 pabodla 115.285 Bug 2237993 CWB - moved
popu_cross_gb_epe_data call form
process_rows to internal_process
to avoid creating duplicate records
for the cross business group.
11-Mar-02 mhoyes 115.286 Dependent eligibility tuning.
20 Mar 02 tjesumic 115.287 PTIP caching data is refreshed when the
current row ptip_id is not matching with
cached ptip_id bug 2228464
25-Mar-02 rpillay 115.289 Fixed CWB Bugs 2270672 and 2275257
13-Jun-02 mhoyes 115.290 - Test harness changes for family Pack C.
Called populate_benmngle_rbvs.
15-Jul-02 mhoyes 115.291 - Moved out nocopy start_slaves, grab_next_batch_range
and check_all_slaves_finished to
ben_maintain_benefit_actions.
26-Jul-02 pbodla 115.292 - ABSENCES - Added procedure abse_process
to process absences. All the changes for
absence processing are identified with
ABSENCES tag.
17-Jul-02 kmahendr 115.293 - ABSENCES - Added looping in process_rows.
24-Jul-02 mmudigon 115.294 - ABSENCES - Added logic for exiting out nocopy of loop
in case of error
14-Aug-02 stee 115.295 COBRA: If person is no longer
enrolled in a COBRA program and
has no enrollment opportunity,
terminate COBRA eligibility.
Bug: 1794808.
30-Aug-02 ikasire 115.296 CWB Hierarcy changes Bugs 2541072 and 2541065
05-Sep-02 lakrish 115.297 Exception g_record_error handled in do_multithread
- raised by build_comp_object_list
13-Sep-02 pbodla 115.298 2288042 Create 0 level heirarchy data if
manager is processed first and
employee is processed later benmngle run.
18-Sep-02 pbodla 115.299 2288042 Create 0 level heirarchy :
tested in hrcwbdvl.
25-Sep-02 pbodla 115.300 Bug 2574791 : modified popu_hrchy and
rebuild_hrchy to make reassign employee
work.
28-Oct-02 kmahendr 115.301 Bug#2638681 - effective date is passed instead
of lf_evt_ocrd_dt for creating person actions
29-Oct-02 mmudigon 115.302 CWB: Bug 2526595 Added proc
del_cwb_pil()
18-Nov-02 pbodla 115.303 ABSE: 2673323 For every absence life event
recache the person data.
In absence mode several life events
may be processed in single run, so
lf_evt_ocrd_dt may change, so recache
the person data as of current life
event otherwise leads to inconsistent
eligibility.
01-Dec-02 pabodla 115.304 Arcsing the file with CWB itemization
code as commented.
09-Dec-02 mmudigon 115.305 CWB itemization code uncommented.
18-Dec-02 kmahendr 115.306 Bug#2718215 - logic for updating elcns_made_dt
in update_enrt_rt procedure changed
31-Dec-02 pbodla 115.307 Bug#2712602 - CWB : When a comp
per in ler is backed out nocopy and rerun
again rebuild the heirarchy.
23-Jan-03 mmudigon 115.308 CWB itemization: Added rt edits.
25-Jan-03 pbodla 115.309 GRADE/STEP : added code to support
grade/step processing.
25-Jan-03 pbodla 115.310 Modified cursor c3 to remove errors.
29-Jan-03 kmahendr 115.311 Added a wrapper for Personnel Action Mode
30-Jan-03 pbodla 115.312 Added a wrapper for Grade/step
progression participation process.
06-feb-2003 nhunur 115.313 Commented cursor c2 and an validation in
check_business_rules for bug - 2784150
10-feb-2003 pbodla 115.314 GRADE/STEP : Added code to support
grade/step processing.
14-Feb-2003 mmudigon 115.315 CWB itemization: bug fix 2793785
modified cursor c5 in check_business
14-Feb-2003 rpillay 115.316 HRMS Debug Performance changes to
hr_utility.set_location calls
24-Feb-2003 mmudigon 115.317 CWB itemization: Bug fix 2801671
Make ineligible for plan if not elig
for any options
07-Mar-2003 nhunur 115.318 Modified code to handle error messages
properly. Bug - 2836770.
14-Mar-2003 lakrish 115.319 Bug 2840078 check that cwb plans/options
do not have coverages attached
10-Apr-2003 mhoyes 115.320 - Bug 2900255 - enabled baseline mode
for the test harness.
11-Apr-2003 tjesumic 115.321 - # 2899702 if the setup is auto enrollment Person Type usages
are not create.created in close enrollement
because the multi edit is not called , Person Type usages
created in multiedit
autoenrollment calls update_person_type_usages
with g_enrollment_change true
27-Apr-03 mmudigon 115.322 - Absences July FP enhancements.
Additional param p_abs_historical_mode
01-Aug-03 rpgupta 115.323 - 2940151 Grade/ step
1. added some parameters to grade_step_process
2. Added some checks to procedure
check_business_rules
19-Aug-03 ikasire 115.324 2940151 GSP Added New Procedure gsp_proc_dflt_auten
and call after determine_rates.
Need to include pqgspdef.pkh and pqhgsppp.pkh files
along with this version.
20-Aug-03 rpgupta 115.325 - 2940151 Grade/ step
Fixed issues in check_business_rule
21-Aug-03 mmudigon 115.326 - 2940151 Grade/ step. Loop through
all GSP potential LEs
01-Sep-03 hmani 115.327 - 3087889 Passed lf_evt_ocrd_dt to
set_up_cobj_part_elig proc instead of effective_date
16-Sep-03 pbodla 115.328 - GSP : mode specific get_active_life_
event procedure is called.
22-Sep-03 rpgupta 115.329 - GSP: Passed GSP parameters from procedure
grade_step_process to procedure
process
26-Sep-03 stee 115.330 - 2894200: If derivable factors
parameter is 'NONE' and there are
derived factors attached to a
compensation object, set it to the
default parameter.
11-Nov-03 ikasire 115.331 setting g_no_ptnl_ler_id to evaluate in drpar
not to trigger potential life events for
U,M,W,I,P,A Modes - BUG 3243960
26-Nov-03 pbodla 115.332 3216667 : iss_val, mx_elcn_val,
mn_elcn_val are passed to cloned rows.
22-Dec-03 Indrasen 115.333 CWBGLOBAL New Procedure
10-Jan-04 tjesumic 115.334 new cursor c9 added in check_business_rule to validate
the cwb task for the budget flag
20-Jan-04 ikasire 115.335/338 ben_pep_cache.clear_down_cache called after call to
ben_enrolment_requirements.update_defaults as it uses
a different effective date
21-Jan-04 ikasire 115.339 Added p_trace_plans_flag to CWBGLOBAL procedure
02-Feb-04 pbodla 115.340 GLOBALCWB : Error
BEN_91769_NOONE_TO_PROCESS is not
relevant for CWB.
Cursor C5 modified such that
worksheet rates are not relevant for
group plan.
16-Feb-04 kmahendr 115.341 Bug#3420298 - dpnt_eligibility calls made in R
mode
17-Feb-04 kmahendr 115.343 Leapfrog version of 115.341
18-Feb-04 tjesumic 115.344 cwb edit , cursor c10,c11 created , c5 edited
18-Feb-04 tjesumic 115.345 cwb edit
23-Feb-04 mmudigon 115.346 GSP: Selective Eligibility evaluation
Changes in process_comp_objects proc
27-Feb-04 pbodla 115.347 GLOBALCWB: procedure update_cwb_epe
modified not to delete the epe,
calls to del_cwb_pil etc are commented.
02-Mar-04 tjesumic 115.348 cwb cursor c6 validation removed
08-Mar-04 abparekh 115.349 Modified Cursor c2 in check_business_rules to check
that more than one similar type of CWB Rates (except
CWBAHE, CWBGP, CWBRA) are not attached to the same
Plan : Bug 3482033
07-Apr-04 pbodla 115.350 FONM : Added fonm functionality.
Tilak
12-Apr-2004 nhunur 115.351 Bypass generate_communication for GSP mode.
22-Apr-2004 pbodla 115.352 FONM : clear the caches if previous
coverage date is different from current.
New GLOBALCWB messages added.
Some Commented code is deleted.
28-May-04 mmudigon 115.353 OSP fixes to process multiple LEs
31-May-04 abparekh 115.354 Bug : 3658807 Set the message name in evaluate_life_events
before raising exception g_record_error.
16-Jun-04 mmudigon 115.355 GSP fixes to process multiple LEs
18 Aug 04 tjesumic 115.356 ptip_id parameter added for csd_rsd determination
fonm validated for every comp object
23 Aug 04 mmudigon 115.357 CFW. Call to ben_carry_forward
2534391 :NEED TO LEAVE ACTION ITEMS
03-Sep-04 abparekh 115.358 3870204 : Don't error out when criteria does not select
any person. Give message in audit log and complete with Normal status.
28-Sep-04 hmani 115.359 IREC - Front Port of 115.343.15102.4
30-Sep-04 abparekh 115.360 IREC - Set G_LER_ID in do_multithread for subsequent use.
15-Oct-04 abparekh 115.361 GSP Rate Sync changes
18-Oct-04 abparekh 115.362 GSP Rate Sync changes
18-Oct-04 abparekh 115.363 Bug 3964719 : GSP : Get employee step only when progression style
is not Grade Progression.
03-Nov-04 abparekh 115.364 Bug 3975857 Changed definition and usage of cursor C2 in
check_business_rules procedure so that validation works correctly
03-Nov-04 kmahendr 115.365 Bug#3903126 - global variable g_derivable_factor
assigned value inside do_multithread procedure
03-Nov-04 pbodla 115.366 GLOBALCWB :Bug#3968065-Added call to
sum_oipl_rates_and_upd_pl_rate
15-Nov-04 kmahendr 115.367 Unrest. enh changes
03-Dec-04 ikasire 115.368 BUG 4046914
14-Dec-04 pbodla 115.369 bug 4040013 - when sum_oipl_rates_an...
procedure is called, plan id is going as null
so l_cwb_pl_id is passed.
06-Jan-05 ikasire 115.370 Bug 4064635 a call to new Procedure got added
Look for dependent package changes
31-Jan-05 abparekh 115.371 Bug 4149182 Commented the check to prevent rates at plan level
when options attached to the plan.
14-Jan-05 bmanyam 115.372/373 Bug: 4128034. CWB Mode fnd_session needs to be set.
22-feb-05 nhunur 115.374 Bug : 4199099 - Task for group plan need not be 'A'
24-feb-05 tjesumic 115.375 Bug : 4204020 - cache_person_information called after determing LE date
25-Feb-05 abparekh 115.376 Added procedure person_header_new and called it
from process_life_events to separate caching from peson header
29-Feb-05 pbodla 115.377 4214845 : To not skip the comp object
processing if parent object is ineligible.
08-mar-05 nhunur 115.378 GSI Netherlands issue. Added condition to get GRE info
only for US leg code.
10-Mar-05 mmudigon 115.380 Bug 4194337. Added calls to
set_parent_elig_flags in proc
update_elig_per_rows
24-Mar-05 pbodla 115.381 Bug 4258498 - Moved the carry forward results call
after the reinstate call, so that reinstate first
attempt to get back the enrollments first.
25-Mar-05 abparekh 115.382 Bug 4245975 : Rollback in exception g_life_event_after
only for G, M modes
07-Apr-05 mmudigon 115.383 Bug 4234501. Removed Rollback in
exception g_life_event_after
14-Apr-05 kmahendr 115.384 Bug#4291122 - added newly_ineligible condition
to call enrolment_requirement
22-apr-05 nhunur 115.385 All threads should have fnd_sessions populated for FF.
05-May-05 tjesumic 115.386 Fonm Clearing cache for fonm date date is fixed
23-May-05 mmudigon 115.387 Rank and Score Call added
01-jun-05 pbodla 115.388 Bug 4258200 : Budget and reserve rates
can be attached to local plans so that the
data is copied over in the event of
cloning of cwb data.
28-jun-05 nhunur 115.389 for GSP chk elig at PGM level for all prog styles.
26-jul-05 ssarkar 115.390 Bug 4496944 : Handled exception thrown by person_header_new .
03-Aug-05 rbingi 115.391 Bug 4394545: Erroring if cwb_process(BENCOMOD) is submitted through SRS
(Not by other Conc Request)
12-Sep-05 ikasire 115.392 Bug 4463267 added new procedure call update_susp_if_ctfn_flag
22-sep-05 ssarkar 115.393 Bug 4621751 irec2 -- offer assignment
06-Oct-05 rbingi 115.394 Bug 4640014 Added call to ben_manage_unres_life_events.clear_epe_cache
10-nov-05 ssarkar 115.395 IREC2: Called post_irec_process_update.
17-nov-05 nhunur 115.396 bug - 4743143 - gsp changes in check_business_rules, grade_step_process.
03-Jan-06 nhunur 115.397 cwb - changes for person type param.
02-Feb-06 swjain 115.398 CWB Thread Num Enhancement
08-Feb-06 abparekh 115.399 Bug 4875181 - Added p_run_rollup_only to cwb_global_process
28-Feb-06 kmahendr 115.400 Added ben_reopen_ended_result call for
GM issue - Fidelity
14-mar-06 nhunur 115.401 bug 5090149 for cwb
16-Mar-06 kmahendr 115.402 Bug#5089721-added codes to update_elig_per
proc
23-Mar-06 kmahendr 115.403 Bug#5100083 - added parameters to call
ben_reopen_ended_result
04-APR-06 ssarkar 115.404 Bug 5055119 - added end_date_elig_per_rows
22-May-06 pbodla 115.405 Bug 5232223 - Added code to handle the trk inelig flag
If trk inelig flag is set to N at group plan level
then do not create cwb per in ler and all associated data.
29-jul-06 nhunur 115.406 CAGR fix for person selection
20-Sep-06 abparekh 115.407 Bug 5550359 : Passed p_validate correctly to benptnle
Added p_validate to PROCESS_LIFE_EVENTS
and EVALUATE_LIFE_EVENTS
21-sep-06 nhunur 115.408 bug 5534550 - call reopen only for L or C modes
17-oct-06 stee 115.409 bug 5364920 - pass per_in_ler_id to
ben_generate_communication.
30-oct-06 gsehgal 115.410 bug 5618436 - changed in print parameters
16-Nov-06 maagrawa 115.411 5666180. Remove the cwb check (c7)
which prevented plan with only one
salary component.
22-Jan-07 rtagarra 115.412 ICM Changes.
06-Aug-07 rtagarra 115.415 Bug 6321565 : For 'R' mode corrected the if condition to call benutils.get_ler.
04-Dec-07 krupani 115.419 Incorporated changes of defer deenrollment, Bug 6519622, Bug 6373951,
Bug 6373951 and Bug 6390880 from branchline to mainline
10-Mar-08 krupani 115.421 Bug 6404338. Min Max enhancement incorporated in R12 mainline
12-Aug-08 pvelugul 120.28.12010000.2 Bug 6872010: Cleared global variables
g_fonm_cvg_strt_dt and g_fonm_rt_strt_dt
in clear_init_benmngle_caches
12-Aug-08 pvelugul 120.28.12010000.3 Bug 6806014: While processing open, if the winner life event is
a normal life event(mode 'L'), then change the mode on the fly
to 'L' from 'C', instead of raising an exception
*/
--------------------------------------------------------------------------------
--
g_package varchar2(80) := 'ben_manage_life_events';
p_person_selection_rule_id in number,
p_person_type_id in number,
p_pgm_id in number,
p_pl_id in number,
p_ler_id in number,
p_pl_typ_id in number,
p_opt_id in number,
p_lf_evt_ocrd_dt in date,
p_org_heirarchy_id in number default null,
p_org_starting_node_id in number default null,
p_asg_events_to_all_sel_dt in date default null,
p_per_sel_dt_cd in varchar2 default null,
p_per_sel_dt_from in date default null,
p_per_sel_dt_to in date default null,
p_year_from in number default null,
p_year_to in number default null,
p_qual_type in number default null,
p_qual_status in varchar2 default null,
p_lf_evt_oper_cd in varchar2 default null
) is
-- PB : 5422 :
-- p_popl_enrt_typ_cycl_id in number) is
--
l_package varchar2(80) := g_package||'.check_business_rules';
select null
from per_person_type_usages_f ppu
where ppu.person_id = p_person_id
and ppu.person_type_id = p_person_type_id
and p_effective_date
between ppu.effective_start_date
and ppu.effective_end_date;
select null
from ben_acty_base_rt_f abr
where abr.pl_id = p_pl_id
and abr.acty_typ_cd like 'CWB%'
and abr.acty_typ_cd not in ('CWBAHE') -- Bug 3975857 - Removed codes CWBRA,CWBGP
and abr.acty_base_rt_stat_cd = 'A'
and abr.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between abr.effective_start_date
and abr.effective_end_date
group by abr.acty_typ_cd
having count(*) > 1;
select enp.uses_bdgt_flag,
enp.prsvr_bdgt_cd,
DECODE(pln.pl_id,pln.group_pl_id,'Y','N') Group_plan
from ben_enrt_perd enp,
ben_popl_enrt_typ_cycl_f pet,
ben_pl_f pln
where pet.pl_id = p_pl_id
and pet.business_group_id = p_business_group_id
and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id
and enp.asnd_lf_evt_dt = p_lf_evt_ocrd_dt
and pln.pl_id = p_pl_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
pet.effective_start_date and pet.effective_end_date
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
pln.effective_start_date and pln.effective_end_date
;
select count(decode(abr.acty_typ_cd,'CWBDB',1,0)),
count(decode(abr.acty_typ_cd,'CWBWB',1,0)),
count(decode(abr.acty_typ_cd,'CWBR',1,0))
from ben_acty_base_rt_f abr
where abr.acty_typ_cd in ('CWBDB','CWBWB','CWBR')
and abr.acty_base_rt_stat_cd = 'A'
and abr.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
abr.effective_start_date and abr.effective_end_date
and (abr.pl_id = p_pl_id or
abr.oipl_id in
(select oipl.oipl_id
from ben_oipl_f oipl
where oipl.pl_id = p_pl_id
and oipl.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
oipl.effective_start_date and oipl.effective_end_date
)
);
select 'x'
from ben_acty_base_rt_f abr1
where
( abr1.acty_typ_cd in ('CWBDB')
and abr1.acty_base_rt_stat_cd = 'A'
and abr1.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
abr1.effective_start_date and abr1.effective_end_date
and (abr1.pl_id = p_pl_id or
abr1.oipl_id in
(select oipl.oipl_id
from ben_oipl_f oipl
where oipl.pl_id = p_pl_id
and oipl.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
oipl.effective_start_date and oipl.effective_end_date
)
)
and not exists
(select 'x'
from ben_acty_base_rt_f abr2
where abr2.acty_typ_cd = 'CWBWB'
and abr2.acty_base_rt_stat_cd = 'A'
and abr2.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
abr2.effective_start_date and abr2.effective_end_date
and nvl(abr2.pl_id,-1) = nvl(abr1.pl_id,-1)
and nvl(abr2.oipl_id,-1) = nvl(abr1.oipl_id,-1)
)
) OR
--if plan or option has CWBWB and it should have CWBDB
( abr1.acty_typ_cd in ('CWBWB')
and abr1.acty_base_rt_stat_cd = 'A'
and abr1.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
abr1.effective_start_date and abr1.effective_end_date
and (abr1.pl_id = p_pl_id or
abr1.oipl_id in
(select oipl.oipl_id
from ben_oipl_f oipl
where oipl.pl_id = p_pl_id
and oipl.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
oipl.effective_start_date and oipl.effective_end_date
)
)
and not exists
(select 'x'
from ben_acty_base_rt_f abr2
where abr2.acty_typ_cd = 'CWBDB'
and abr2.acty_base_rt_stat_cd = 'A'
and abr2.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
abr2.effective_start_date and abr2.effective_end_date
and nvl(abr2.pl_id,-1) = nvl(abr1.pl_id,-1)
and nvl(abr2.oipl_id,-1) = nvl(abr1.oipl_id,-1)
)
) ;
select 'x'
from ben_acty_base_rt_f abr1
where abr1.acty_typ_cd in ('CWBDB')
and abr1.acty_base_rt_stat_cd = 'A'
and abr1.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
abr1.effective_start_date and abr1.effective_end_date
and (abr1.pl_id = p_pl_id or
abr1.oipl_id in
(select oipl.oipl_id
from ben_oipl_f oipl
where oipl.pl_id = p_pl_id
and oipl.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
oipl.effective_start_date and oipl.effective_end_date
)
)
and not exists
(select 'x'
from ben_acty_base_rt_f abr2
where abr2.acty_typ_cd in ('CWBES')
and abr2.acty_base_rt_stat_cd = 'A'
and abr2.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
abr2.effective_start_date and abr2.effective_end_date
and nvl(abr2.pl_id,-1) = nvl(abr1.pl_id,-1)
and nvl(abr2.oipl_id,-1) = nvl(abr1.oipl_id,-1)
);
select count(opt.opt_id)
from ben_opt_f opt
where opt.component_reason is not null
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
opt.effective_start_date and opt.effective_end_date
and opt.opt_id in
(select oipl.opt_id
from ben_oipl_f oipl
where oipl.pl_id = p_pl_id
and oipl.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
oipl.effective_start_date and oipl.effective_end_date);
select 'x' found
from ben_cvg_amt_calc_mthd_f ccm,
ben_pl_typ_f pt,
ben_pl_f pl
where pl.pl_id = p_pl_id
and pt.pl_typ_id = pl.pl_typ_id
and pt.opt_typ_cd = 'CWB'
and (ccm.pl_id = pl.pl_id or
ccm.oipl_id in
(select oipl.oipl_id
from ben_oipl_f oipl
where oipl.pl_id = pl.pl_id
and oipl.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
oipl.effective_start_date and oipl.effective_end_date
)
)
and ccm.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between ccm.effective_start_date and ccm.effective_end_date
and pl.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between pl.effective_start_date and pl.effective_end_date
and pt.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between pt.effective_start_date and pt.effective_end_date;
select 'x'
from ben_cwb_wksht_grp cwg
where cwg.pl_id = p_pl_id ;
select 'x' found
from ben_oipl_f oipl
where oipl.pl_id = p_pl_id
and oipl.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
oipl.effective_start_date and oipl.effective_end_date ;
select 'x' found
from ben_acty_base_rt_f abr
where abr.acty_typ_cd in ('CWBDB','CWBWB','CWBR')
and abr.acty_base_rt_stat_cd = 'A'
and abr.business_group_id = p_business_group_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date) between
abr.effective_start_date and abr.effective_end_date
and abr.pl_id = p_pl_id
;
select null
from ben_rptg_grp bnr
where bnr.business_group_id = p_business_group_id
and bnr.rptg_prps_cd = 'IREC';
select null
from ben_ler_f ler
where ler.typ_cd = 'GSP'
and ler.lf_evt_oper_cd = 'SYNC'
and ler.business_group_id = p_business_group_id
and p_effective_date between ler.effective_start_date
and ler.effective_end_date;
p_person_selection_rule_id is not null then
--
fnd_message.set_name('BEN','BEN_91745_RULE_AND_PERSON');
procedure update_elig_per_rows
(p_comp_obj_tree_row in ben_manage_life_events.g_cache_proc_objects_rec
,p_comp_rec in ben_derive_part_and_rate_facts.g_cache_structure
,p_person_id in number
,p_par_elig_state in out nocopy ben_comp_obj_filter.g_par_elig_state_rec
,p_treeele_num in number
,p_business_group_id in number
,p_effective_date in date
,p_lf_evt_ocrd_dt in date
,p_per_in_ler_id in number
,p_continue_loop out nocopy boolean) is
--
l_envpgm_id number := p_comp_obj_tree_row.par_pgm_id;
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
)
;
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 strt_dt,
pep.prtn_end_dt 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);
select null
from ben_elig_per_f
where person_id = p_person_id
and p_effective_date between effective_start_date and
effective_end_date
and per_in_ler_id is null
and pgm_id = p_pgm_id;
select null
from ben_elig_per_f
where person_id = p_person_id
and p_effective_date between effective_start_date and
effective_end_date
and per_in_ler_id is null
and pgm_id is null
and pl_id = p_pl_id;
select null
from ben_elig_per_opt_f epo
where epo.per_in_ler_id is null
and p_effective_date between epo.effective_start_date
and epo.effective_end_date
and epo.elig_per_id in
(select elig_per_id
from ben_elig_per_f
where person_id = p_person_id
and p_effective_date between effective_start_date and
effective_end_date
and per_in_ler_id = p_per_in_ler_id
and pgm_id = p_pgm_id);
select null
from ben_elig_per_opt_f epo
where epo.per_in_ler_id is null
and p_effective_date between epo.effective_start_date
and epo.effective_end_date
and epo.elig_per_id in
(select elig_per_id
from ben_elig_per_f
where person_id = p_person_id
and p_effective_date between effective_start_date and
effective_end_date
and per_in_ler_id = p_per_in_ler_id
and pgm_id is null
and pl_id = p_pl_id);
l_proc := g_package ||'.update_elig_per_rows';
update ben_elig_per_f pep set per_in_ler_id = p_per_in_ler_id
where pep.person_id = p_person_id
and pep.per_in_ler_id is null
and pep.pgm_id = l_envpgm_id
and p_effective_date between pep.effective_start_date
and pep.effective_end_date;
update ben_elig_per_opt_f epo
set per_in_ler_id = p_per_in_ler_id
where epo.per_in_ler_id is null
and epo.elig_per_id in
(select elig_per_id from ben_elig_per_f pep
where pep.pgm_id = l_envpgm_id
and pep.per_in_ler_id = p_per_in_ler_id
and p_effective_date between pep.effective_start_date
and pep.effective_end_date)
and p_effective_date between epo.effective_start_date and
epo.effective_end_date
and epo.opt_id = l_oipl_rec.opt_id;
update ben_elig_per_f pep set per_in_ler_id = p_per_in_ler_id
where pep.person_id = p_person_id
and pep.per_in_ler_id is null
and pep.pgm_id is null
and pep.pl_id = l_envpl_id
and p_effective_date between pep.effective_start_date
and pep.effective_end_date;
update ben_elig_per_opt_f epo
set per_in_ler_id = p_per_in_ler_id
where epo.per_in_ler_id is null
and epo.elig_per_id in
(select elig_per_id from ben_elig_per_f pep
where pep.pgm_id is null
and pep.pl_id = l_envpl_id
and pep.per_in_ler_id = p_per_in_ler_id
and p_effective_date between pep.effective_start_date
and pep.effective_end_date)
and p_effective_date between epo.effective_start_date and
epo.effective_end_date
and epo.opt_id = l_oipl_rec.opt_id;
hr_utility.set_location('OIPL ID-update'||l_oipl_id,111);
procedure update_enrt_rt (p_per_in_ler_id number) is
--
cursor c_enrt_rt is
select *
from ben_enrt_rt
where elig_per_elctbl_chc_id in
(select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
where per_in_ler_id = p_per_in_ler_id)
union
select *
from ben_enrt_rt
where enrt_bnft_id in
(select enrt_bnft_id from ben_enrt_bnft
where elig_per_elctbl_chc_id in
(select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc
where per_in_ler_id = p_per_in_ler_id));
select *
from ben_pil_elctbl_chc_popl
where per_in_ler_id = p_per_in_ler_id;
select effective_start_date
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pen.pgm_id = p_pgm_id
and pen.person_id = pil.person_id
and pil.per_in_ler_id = p_per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.effective_end_date = hr_api.g_eot;
select effective_start_date
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pen.pl_id = p_pl_id
and pen.person_id = pil.person_id
and pil.per_in_ler_id = p_per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.effective_end_date = hr_api.g_eot;
update ben_enrt_rt
set prtt_rt_val_id = ben_manage_life_events.g_enrt_rt_tbl(j).prtt_rt_val_id
where enrt_rt_id = i.enrt_rt_id;
hr_utility.set_location('Entering popl update',10);
update ben_pil_elctbl_chc_popl
set elcns_made_dt = ben_manage_life_events.g_pil_popl_tbl(j).elcns_made_dt
where pil_elctbl_chc_popl_id = i.pil_elctbl_chc_popl_id;
update ben_pil_elctbl_chc_popl
set elcns_made_dt = l_effective_start_date
where pil_elctbl_chc_popl_id = i.pil_elctbl_chc_popl_id;
update ben_pil_elctbl_chc_popl
set elcns_made_dt = l_effective_start_date
where pil_elctbl_chc_popl_id = i.pil_elctbl_chc_popl_id;
hr_utility.set_location('Leaving update enrt',10);
select elig_per_elctbl_chc_id,
object_version_number
from ben_elig_per_elctbl_chc epe
where elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id
and per_in_ler_id = p_per_in_ler_id ;
ben_elig_per_elc_chc_api.update_perf_ELIG_PER_ELC_CHC
(p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id
,p_object_version_number => l_epe.object_version_number
,p_dflt_flag => l_dflt_flag
,p_auto_enrt_flag => l_auto_enrt_flag
,p_effective_date => p_effective_date
);
select
elig_per_elctbl_chc_id
from
ben_elig_per_elctbl_chc epe
where
per_in_ler_id = p_per_in_ler_id and
in_pndg_wkflow_flag = 'S';
update ben_elig_per_elctbl_chc
set in_pndg_wkflow_flag = 'N'
where in_pndg_wkflow_flag = 'S'
and per_in_ler_id = p_per_in_ler_id;
ben_elig_per_elc_chc_api.update_perf_ELIG_PER_ELC_CHC
(p_elig_per_elctbl_chc_id => l_epe.elig_per_elctbl_chc_id
,p_object_version_number => l_object_version_number
,p_in_pndg_wkflow_flag => 'N'
,p_effective_date => p_effective_date
);
procedure delete_in_pndg_elig_dpnt( p_per_in_ler_id in number,
p_effective_date in date) is
l_object_version_number number ;
select
elig_dpnt_id
from
ben_elig_dpnt egd
where
egd.per_in_ler_id = p_per_in_ler_id and
egd.elig_per_elctbl_chc_id is null ;
l_package := 'delete_in_pndg_elig_dpnt' ;
ben_elig_dpnt_api.delete_elig_dpnt
(p_elig_dpnt_id => l_in_pndg_edg.elig_dpnt_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
) ;
g_cache_person_process.delete;
benutils.g_batch_param_table_object.delete;
SELECT fcp.concurrent_program_name
FROM ben_benefit_actions bft, fnd_concurrent_programs fcp
WHERE bft.program_id = fcp.concurrent_program_id
AND bft.benefit_action_id = p_benefit_action_id;
buff => 'Person Selection Rule :'||
benutils.iftrue
(p_expression => l_rec.person_selection_rl is null,
p_true => 'None',
p_false => l_rec.person_selection_rl));
buff => 'Comp Object Selection Rule :'||
benutils.iftrue
(p_expression => l_rec.comp_selection_rl is null,
p_true => 'None',
p_false => l_rec.comp_selection_rl));
select unique pel_0.mgr_pil_elctbl_chc_popl_id
from ben_cwb_hrchy pel_0,
ben_pil_elctbl_chc_popl mgr_pel_0,
ben_per_in_ler pil_0
where
mgr_pel_0.pil_elctbl_chc_popl_id = pel_0.mgr_pil_elctbl_chc_popl_id
and mgr_pel_0.per_in_ler_id = pil_0.per_in_ler_id
and mgr_pel_0.pl_id = p_pl_id
and pil_0.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
-- and pil_0.ler_id = p_ler_id
and pil_0.business_group_id = p_business_group_id
and pil_0.per_in_ler_stat_cd = 'STRTD'
and pel_0.lvl_num > 0
and pel_0.mgr_pil_elctbl_chc_popl_id not in
( select mgr_pel.pil_elctbl_chc_popl_id
from ben_cwb_hrchy hrh,
ben_pil_elctbl_chc_popl mgr_pel,
ben_per_in_ler pil
where hrh.mgr_pil_elctbl_chc_popl_id =
hrh.emp_pil_elctbl_chc_popl_id
and mgr_pel.pil_elctbl_chc_popl_id = hrh.mgr_pil_elctbl_chc_popl_id
and hrh.lvl_num = 0
and mgr_pel.per_in_ler_id = pil.per_in_ler_id
and mgr_pel.pl_id = p_pl_id
and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
-- and pil.ler_id = p_ler_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd = 'STRTD'
);
select
cwb.emp_pil_elctbl_chc_popl_id,
pel.ws_mgr_id
from
ben_cwb_hrchy cwb,
ben_pil_elctbl_chc_popl pel ,
ben_per_in_ler pil
where
cwb.mgr_pil_elctbl_chc_popl_id = -1
and pel.pil_elctbl_chc_popl_id = cwb.emp_pil_elctbl_chc_popl_id
--
-- Bug 2541072 : Do not consider all per in ler's.
--
and pel.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and ((cv_pl_id = -1 and cv_lf_evt_ocrd_dt = hr_api.g_eot)
or
(pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt and pel.pl_id = cv_pl_id)
)
and cwb.lvl_num = -1;
select
pel.pl_id,
pil.lf_evt_ocrd_dt,
pil.ler_id,
pil.business_group_id
from
ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
where
pel.per_in_ler_id = pil.per_in_ler_id
and pel.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
and pil.per_in_ler_stat_cd = 'STRTD';
select pel.ws_mgr_id,
pel.pil_elctbl_chc_popl_id
from ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
where pel.per_in_ler_id = pil.per_in_ler_id
and pel.pl_id = p_pl_id
and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and pil.ler_id = p_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd = 'STRTD';
procedure insert_mgr_hrchy ( p_emp_pil_elctbl_chc_popl_id number,
p_mgr_pil_elctbl_chc_popl_id number,
p_lvl_num number ) is
begin
--
if g_debug then
hr_utility.set_location('insert_mgr_hrchy p_emp_pil_elctbl_chc_popl_id '
||p_emp_pil_elctbl_chc_popl_id,10);
hr_utility.set_location('insert_mgr_hrchy p_mgr_pil_elctbl_chc_popl_id '
||p_mgr_pil_elctbl_chc_popl_id || ' lvl = ' || p_lvl_num, 20);
insert into ben_cwb_hrchy (
emp_pil_elctbl_chc_popl_id,
mgr_pil_elctbl_chc_popl_id,
lvl_num )
values (
p_emp_pil_elctbl_chc_popl_id,
p_mgr_pil_elctbl_chc_popl_id,
p_lvl_num );
end insert_mgr_hrchy;
procedure update_init_pel(cv_pl_id number, cv_lf_evt_ocrd_dt date) is
--
-- CWB bug : 2712602
--
cursor c_cwh is
select rowid
from ben_cwb_hrchy cwh
where cwh.lvl_num = -1 and
cwh.mgr_pil_elctbl_chc_popl_id = -1
--
-- Bug 2541072 : Do not consider all per in ler's.
--
and exists
(select null
from ben_pil_elctbl_chc_popl pel ,
ben_per_in_ler pil
where pel.pil_elctbl_chc_popl_id = cwh.emp_pil_elctbl_chc_popl_id
and pel.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and ((cv_pl_id = -1 and cv_lf_evt_ocrd_dt = hr_api.g_eot)
or
(pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt and pel.pl_id = cv_pl_id)
)
) ;
delete
from ben_cwb_hrchy cwh
where (( cwh.lvl_num = -1
and cwh.mgr_pil_elctbl_chc_popl_id = -1) OR
( cwh.lvl_num = 0 and
cwh.mgr_pil_elctbl_chc_popl_id = cwh.emp_pil_elctbl_chc_popl_id ) )
and not exists
(select null
from ben_cwb_hrchy cwh1
where cwh1.mgr_pil_elctbl_chc_popl_id = cwh.emp_pil_elctbl_chc_popl_id
and cwh1.lvl_num <> 0
)
--
-- Bug 2541072 : Do not consider all per in ler's.
--
and exists
(select null
from ben_pil_elctbl_chc_popl pel ,
ben_per_in_ler pil
where pel.pil_elctbl_chc_popl_id = cwh.emp_pil_elctbl_chc_popl_id
and pel.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and ((cv_pl_id = -1 and cv_lf_evt_ocrd_dt = hr_api.g_eot)
or
(pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt and pel.pl_id = cv_pl_id)
)
) ;
update ben_cwb_hrchy cwh
set cwh.mgr_pil_elctbl_chc_popl_id = cwh.emp_pil_elctbl_chc_popl_id,
cwh.lvl_num = 0
where cwh.lvl_num = -1
and cwh.mgr_pil_elctbl_chc_popl_id = -1
and cwh.rowid = l_cwh.rowid;
delete from ben_cwb_hrchy where rowid = l_cwh.rowid;null;
insert_mgr_hrchy(l_emp_pel,l_mgr_pel,l_level);
hr_utility.set_location('Before call to delete_init_pel',10);
update_init_pel(lv_pl_id, lv_lf_evt_ocrd_dt) ;
hr_utility.set_location('After call to delete_init_pel',10);
delete from ben_cwb_hrchy
where emp_pil_elctbl_chc_popl_id in (
select pel.pil_elctbl_chc_popl_id
from ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
where pel.pl_id = lv_pl_id
and pil.lf_evt_ocrd_dt = lv_lf_evt_ocrd_dt
and pil.per_in_ler_id = pel.per_in_ler_id
and pil.per_in_ler_stat_cd = 'BCKDT');
delete from ben_cwb_hrchy
where mgr_pil_elctbl_chc_popl_id in (
select pel.pil_elctbl_chc_popl_id
from ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
where pel.pl_id = lv_pl_id
and pil.lf_evt_ocrd_dt = lv_lf_evt_ocrd_dt
and pil.per_in_ler_id = pel.per_in_ler_id
and pil.per_in_ler_stat_cd = 'BCKDT');
insert into ben_cwb_hrchy (
emp_pil_elctbl_chc_popl_id,
mgr_pil_elctbl_chc_popl_id,
lvl_num )
values (
l_no_0_hrchy.mgr_pil_elctbl_chc_popl_id,
l_no_0_hrchy.mgr_pil_elctbl_chc_popl_id,
0 );
select unique epe_0.mgr_elig_per_elctbl_chc_id
from ben_cwb_mgr_hrchy epe_0,
ben_elig_per_elctbl_chc mgr_epe_0,
ben_per_in_ler pil_0
where
mgr_epe_0.elig_per_elctbl_chc_id = epe_0.mgr_elig_per_elctbl_chc_id
and mgr_epe_0.per_in_ler_id = pil_0.per_in_ler_id
and mgr_epe_0.pl_id = p_pl_id
and pil_0.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
-- and pil_0.ler_id = p_ler_id
and pil_0.business_group_id = p_business_group_id
and pil_0.per_in_ler_stat_cd = 'STRTD'
and epe_0.lvl_num > 0
and epe_0.mgr_elig_per_elctbl_chc_id not in
( select mgr_epe.elig_per_elctbl_chc_id
from ben_cwb_mgr_hrchy hrh,
ben_elig_per_elctbl_chc mgr_epe,
ben_per_in_ler pil
where hrh.mgr_elig_per_elctbl_chc_id =
hrh.emp_elig_per_elctbl_chc_id
and mgr_epe.elig_per_elctbl_chc_id = hrh.mgr_elig_per_elctbl_chc_id
and hrh.lvl_num = 0
and mgr_epe.per_in_ler_id = pil.per_in_ler_id
and mgr_epe.pl_id = p_pl_id
and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
-- and pil.ler_id = p_ler_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd = 'STRTD'
);
select
cwb.emp_elig_per_elctbl_chc_id,
epe.ws_mgr_id
from
ben_cwb_mgr_hrchy cwb,
ben_elig_per_elctbl_chc epe ,
ben_per_in_ler pil
where
cwb.mgr_elig_per_elctbl_chc_id = -1
and epe.elig_per_elctbl_chc_id = cwb.emp_elig_per_elctbl_chc_id
--
-- Bug 2541072 : Do not consider all per in ler's.
--
and epe.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and ((cv_pl_id = -1 and cv_lf_evt_ocrd_dt = hr_api.g_eot)
or
(pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt and epe.pl_id = cv_pl_id)
)
and cwb.lvl_num = -1;
select
epe.pl_id,
pil.lf_evt_ocrd_dt,
pil.ler_id,
pil.business_group_id
from
ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where
epe.per_in_ler_id = pil.per_in_ler_id
and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and pil.per_in_ler_stat_cd = 'STRTD';
select epe.ws_mgr_id,
epe.elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where epe.per_in_ler_id = pil.per_in_ler_id
and epe.pl_id = p_pl_id
and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and pil.ler_id = p_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd = 'STRTD';
procedure insert_mgr_hrchy ( p_emp_elig_per_elctbl_chc_id number,
p_mgr_elig_per_elctbl_chc_id number,
p_lvl_num number ) is
begin
--
if g_debug then
hr_utility.set_location('insert_mgr_hrchy p_emp_elig_per_elctbl_chc_id '
||p_emp_elig_per_elctbl_chc_id,10);
hr_utility.set_location('insert_mgr_hrchy p_mgr_elig_per_elctbl_chc_id '
||p_mgr_elig_per_elctbl_chc_id || ' lvl = ' || p_lvl_num, 20);
insert into ben_cwb_mgr_hrchy (
emp_elig_per_elctbl_chc_id,
mgr_elig_per_elctbl_chc_id,
lvl_num )
values (
p_emp_elig_per_elctbl_chc_id,
p_mgr_elig_per_elctbl_chc_id,
p_lvl_num );
end insert_mgr_hrchy;
procedure update_init_epe(cv_pl_id number, cv_lf_evt_ocrd_dt date) is
begin
--
-- Also delete the rows for employees who do not have
-- subordinates and with level -1 .
-- And also the last subordinate is now reporting to another manager
-- we need to delete the epe,0,0 row of the employee.
--
delete
from ben_cwb_mgr_hrchy cwh
where (( cwh.lvl_num = -1
and cwh.mgr_elig_per_elctbl_chc_id = -1) OR
( cwh.lvl_num = 0 and
cwh.mgr_elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id ) )
and not exists
(select null
from ben_cwb_mgr_hrchy cwh1
where cwh1.mgr_elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id
and cwh1.lvl_num <> 0
)
--
-- Bug 2541072 : Do not consider all per in ler's.
--
and exists
(select null
from ben_elig_per_elctbl_chc epe ,
ben_per_in_ler pil
where epe.elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id
and epe.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and ((cv_pl_id = -1 and cv_lf_evt_ocrd_dt = hr_api.g_eot)
or
(pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt and epe.pl_id = cv_pl_id)
)
) ;
update ben_cwb_mgr_hrchy cwh
set cwh.mgr_elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id,
cwh.lvl_num = 0
where cwh.lvl_num = -1 and
cwh.mgr_elig_per_elctbl_chc_id = -1
--
-- Bug 2541072 : Do not consider all per in ler's.
--
and exists
(select null
from ben_elig_per_elctbl_chc epe ,
ben_per_in_ler pil
where epe.elig_per_elctbl_chc_id = cwh.emp_elig_per_elctbl_chc_id
and epe.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and ((cv_pl_id = -1 and cv_lf_evt_ocrd_dt = hr_api.g_eot)
or
(pil.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt and epe.pl_id = cv_pl_id)
)
) ;
insert_mgr_hrchy(l_emp_epe,l_mgr_epe,l_level);
hr_utility.set_location('Before call to delete_init_epe',10);
update_init_epe(lv_pl_id, lv_lf_evt_ocrd_dt) ;
hr_utility.set_location('After call to delete_init_epe',10);
insert into ben_cwb_mgr_hrchy (
emp_elig_per_elctbl_chc_id,
mgr_elig_per_elctbl_chc_id,
lvl_num )
values (
l_no_0_hrchy.mgr_elig_per_elctbl_chc_id,
l_no_0_hrchy.mgr_elig_per_elctbl_chc_id,
0 );
l_last_update_date ben_elig_per_elctbl_chc.last_update_date%TYPE;
l_last_updated_by ben_elig_per_elctbl_chc.last_updated_by%TYPE;
l_last_update_login ben_elig_per_elctbl_chc.last_update_login%TYPE;
select pel.dflt_enrt_dt,
pel.cls_enrt_dt_to_use_cd,
pel.enrt_typ_cycl_cd,
pel.enrt_perd_strt_dt,
pel.enrt_perd_end_dt,
pel.lee_rsn_id,
pel.enrt_perd_id,
pel.uom,
pel.acty_ref_perd_cd,
pel.business_group_id per_business_group_id,
per.business_group_id mgr_business_group_id,
pel.ws_mgr_id
from ben_pil_elctbl_chc_popl pel,
per_all_people_f per
where pel.per_in_ler_id = cv_per_in_ler_id
and pel.ws_mgr_id = per.person_id (+)
and l_effective_date between effective_start_date
and effective_end_date;
select epe.* ,
pel.dflt_enrt_dt,
pel.cls_enrt_dt_to_use_cd,
pel.enrt_typ_cycl_cd,
pel.enrt_perd_strt_dt,
pel.enrt_perd_end_dt,
pel.lee_rsn_id,
pel.enrt_perd_id,
pel.uom,
pel.acty_ref_perd_cd,
epe.business_group_id per_business_group_id,
per.business_group_id mgr_business_group_id
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
per_all_people_f per
where epe.per_in_ler_id = cv_per_in_ler_id
and epe.per_in_ler_id = pel.per_in_ler_id
and epe.pl_id = pel.pl_id
and epe.ws_mgr_id = per.person_id (+)
and l_effective_date between effective_start_date
and effective_end_date;
select ecr.*
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = cv_elig_per_elctbl_chc_id;
SELECT pen.prtt_enrt_rslt_id
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.person_id = cv_person_id
AND pen.business_group_id = cv_business_group_id
AND pen.sspndd_flag = 'N'
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.effective_end_date = hr_api.g_eot
AND cv_lf_evt_ocrd_dt <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND cv_pl_id = pen.pl_id
AND (
( pen.pgm_id = cv_pgm_id
AND cv_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND cv_pgm_id IS NULL));
select emp_elig_per_elctbl_chc_id
from ben_cwb_mgr_hrchy
where emp_elig_per_elctbl_chc_id = cv_emp_epe_id;
select hrc.emp_pil_elctbl_chc_popl_id,
epe.pil_elctbl_chc_popl_id
from ben_elig_per_elctbl_chc epe,
ben_cwb_hrchy hrc
where epe.elig_per_elctbl_chc_id = cv_emp_epe_id
and hrc.emp_pil_elctbl_chc_popl_id(+) = epe.pil_elctbl_chc_popl_id;
p_program_update_date => l_effective_date);
,p_program_update_date => trunc(sysdate)
,p_procd_dt => l_procd_dt
,p_strtd_dt => l_strtd_dt
,p_voidd_dt => l_voidd_dt);
p_program_update_date => trunc(SYSDATE),
p_enrt_perd_id => l_curr_epe.enrt_perd_id,
p_lee_rsn_id => l_curr_epe.lee_rsn_id,
p_cls_enrt_dt_to_use_cd => l_curr_epe.cls_enrt_dt_to_use_cd,
p_uom => l_curr_epe.uom,
p_acty_ref_perd_cd => l_curr_epe.acty_ref_perd_cd,
p_cryfwd_elig_dpnt_cd => l_curr_epe.cryfwd_elig_dpnt_cd,
-- added for cwb
p_mode => p_mode,
p_ws_mgr_id => l_mgr_person_id_temp,
p_elig_flag => 'N',
p_assignment_id => l_assignment_id ); -- l_curr_epe.elig_flag);
insert into ben_cwb_hrchy (
emp_pil_elctbl_chc_popl_id,
mgr_pil_elctbl_chc_popl_id,
lvl_num )
values(
l_pel_id,
-1,
-1);
INSERT INTO ben_enrt_rt
(
enrt_rt_id,
acty_typ_cd,
tx_typ_cd,
ctfn_rqd_flag,
dflt_flag,
dflt_pndg_ctfn_flag,
dsply_on_enrt_flag,
use_to_calc_net_flx_cr_flag,
entr_val_at_enrt_flag,
asn_on_enrt_flag,
rl_crs_only_flag,
dflt_val,
ann_val,
ann_mn_elcn_val,
ann_mx_elcn_val,
val,
ISS_VAL,
nnmntry_uom,
mx_elcn_val,
mn_elcn_val,
incrmt_elcn_val,
cmcd_acty_ref_perd_cd,
cmcd_mn_elcn_val,
cmcd_mx_elcn_val,
cmcd_val,
cmcd_dflt_val,
rt_usg_cd,
ann_dflt_val,
bnft_rt_typ_cd,
rt_mlt_cd,
dsply_mn_elcn_val,
dsply_mx_elcn_val,
entr_ann_val_flag,
rt_strt_dt,
rt_strt_dt_cd,
rt_strt_dt_rl,
rt_typ_cd,
elig_per_elctbl_chc_id,
acty_base_rt_id,
spcl_rt_enrt_rt_id,
enrt_bnft_id,
prtt_rt_val_id,
decr_bnft_prvdr_pool_id,
cvg_amt_calc_mthd_id,
actl_prem_id,
comp_lvl_fctr_id,
ptd_comp_lvl_fctr_id,
clm_comp_lvl_fctr_id,
business_group_id,
ecr_attribute_category,
ecr_attribute1,
ecr_attribute2,
ecr_attribute3,
ecr_attribute4,
ecr_attribute5,
ecr_attribute6,
ecr_attribute7,
ecr_attribute8,
ecr_attribute9,
ecr_attribute10,
ecr_attribute11,
ecr_attribute12,
ecr_attribute13,
ecr_attribute14,
ecr_attribute15,
ecr_attribute16,
ecr_attribute17,
ecr_attribute18,
ecr_attribute19,
ecr_attribute20,
ecr_attribute21,
ecr_attribute22,
ecr_attribute23,
ecr_attribute24,
ecr_attribute25,
ecr_attribute26,
ecr_attribute27,
ecr_attribute28,
ecr_attribute29,
ecr_attribute30,
last_update_login,
created_by,
creation_date,
last_updated_by,
last_update_date,
request_id,
program_application_id,
program_id,
program_update_date,
object_version_number)
VALUES(
ben_enrt_rt_s.nextval,
l_ecr_rec.acty_typ_cd,
l_ecr_rec.tx_typ_cd,
l_ecr_rec.ctfn_rqd_flag,
l_ecr_rec.dflt_flag,
l_ecr_rec.dflt_pndg_ctfn_flag,
l_ecr_rec.dsply_on_enrt_flag,
l_ecr_rec.use_to_calc_net_flx_cr_flag,
l_ecr_rec.entr_val_at_enrt_flag,
l_ecr_rec.asn_on_enrt_flag,
l_ecr_rec.rl_crs_only_flag,
l_ecr_rec.dflt_val,
l_ecr_rec.ann_val,
l_ecr_rec.ann_mn_elcn_val,
l_ecr_rec.ann_mx_elcn_val,
l_ecr_rec.val,
-- 3216667 : iss_val, mx_elcn_val, mn_elcn_val are passed to
-- cloned rows.
l_ecr_rec.ISS_VAL,
l_ecr_rec.nnmntry_uom,
-- Initially they have to go null.
l_ecr_rec.mx_elcn_val,
l_ecr_rec.mn_elcn_val,
l_ecr_rec.incrmt_elcn_val,
l_ecr_rec.cmcd_acty_ref_perd_cd,
-- Initially they have to go null.
null, --l_ecr_rec.cmcd_mn_elcn_val,
null, --l_ecr_rec.cmcd_mx_elcn_val,
l_ecr_rec.cmcd_val,
null,
l_ecr_rec.rt_usg_cd,
l_ecr_rec.ann_dflt_val,
l_ecr_rec.bnft_rt_typ_cd,
l_ecr_rec.rt_mlt_cd,
-- Initially they have to go null.
null, -- l_ecr_rec.dsply_mn_elcn_val,
null, -- l_ecr_rec.dsply_mx_elcn_val,
l_ecr_rec.entr_ann_val_flag,
l_ecr_rec.rt_strt_dt,
l_ecr_rec.rt_strt_dt_cd,
l_ecr_rec.rt_strt_dt_rl,
l_ecr_rec.rt_typ_cd,
l_elig_per_elctbl_chc_id,
l_ecr_rec.acty_base_rt_id,
null,
null, -- enrt_bnft_id : Should be null
null, -- prtt_rt_val_id : should be null
l_ecr_rec.decr_bnft_prvdr_pool_id,
l_ecr_rec.cvg_amt_calc_mthd_id,
l_ecr_rec.actl_prem_id,
l_ecr_rec.comp_lvl_fctr_id,
l_ecr_rec.ptd_comp_lvl_fctr_id,
l_ecr_rec.clm_comp_lvl_fctr_id,
l_curr_epe.per_business_group_id,
l_ecr_rec.ecr_attribute_category,
l_ecr_rec.ecr_attribute1,
l_ecr_rec.ecr_attribute2,
l_ecr_rec.ecr_attribute3,
l_ecr_rec.ecr_attribute4,
l_ecr_rec.ecr_attribute5,
l_ecr_rec.ecr_attribute6,
l_ecr_rec.ecr_attribute7,
l_ecr_rec.ecr_attribute8,
l_ecr_rec.ecr_attribute9,
l_ecr_rec.ecr_attribute10,
l_ecr_rec.ecr_attribute11,
l_ecr_rec.ecr_attribute12,
l_ecr_rec.ecr_attribute13,
l_ecr_rec.ecr_attribute14,
l_ecr_rec.ecr_attribute15,
l_ecr_rec.ecr_attribute16,
l_ecr_rec.ecr_attribute17,
l_ecr_rec.ecr_attribute18,
l_ecr_rec.ecr_attribute19,
l_ecr_rec.ecr_attribute20,
l_ecr_rec.ecr_attribute21,
l_ecr_rec.ecr_attribute22,
l_ecr_rec.ecr_attribute23,
l_ecr_rec.ecr_attribute24,
l_ecr_rec.ecr_attribute25,
l_ecr_rec.ecr_attribute26,
l_ecr_rec.ecr_attribute27,
l_ecr_rec.ecr_attribute28,
l_ecr_rec.ecr_attribute29,
l_ecr_rec.ecr_attribute30,
l_last_update_login,
l_created_by,
l_creation_date,
l_last_updated_by,
trunc(SYSDATE),
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
trunc(SYSDATE),
l_object_version_number);
l_last_update_date ben_elig_per_elctbl_chc.last_update_date%TYPE;
l_last_updated_by ben_elig_per_elctbl_chc.last_updated_by%TYPE;
l_last_update_login ben_elig_per_elctbl_chc.last_update_login%TYPE;
select epe.* ,
pel.dflt_enrt_dt,
pel.cls_enrt_dt_to_use_cd,
pel.enrt_typ_cycl_cd,
pel.enrt_perd_strt_dt,
pel.enrt_perd_end_dt,
pel.lee_rsn_id,
pel.enrt_perd_id,
pel.uom,
pel.acty_ref_perd_cd,
epe.business_group_id per_business_group_id,
per.business_group_id mgr_business_group_id
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
per_all_people_f per
where epe.per_in_ler_id = cv_per_in_ler_id
and epe.per_in_ler_id = pel.per_in_ler_id
and epe.pl_id = pel.pl_id
and epe.ws_mgr_id = per.person_id (+)
and l_effective_date between effective_start_date
and effective_end_date;
select ecr.*
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = cv_elig_per_elctbl_chc_id;
SELECT pen.prtt_enrt_rslt_id
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.person_id = cv_person_id
AND pen.business_group_id = cv_business_group_id
AND pen.sspndd_flag = 'N'
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.effective_end_date = hr_api.g_eot
AND cv_lf_evt_ocrd_dt <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND cv_pl_id = pen.pl_id
AND (
( pen.pgm_id = cv_pgm_id
AND cv_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND cv_pgm_id IS NULL));
select emp_elig_per_elctbl_chc_id
from ben_cwb_mgr_hrchy
where emp_elig_per_elctbl_chc_id = cv_emp_epe_id;
p_program_update_date => l_effective_date);
,p_program_update_date => trunc(sysdate)
,p_procd_dt => l_procd_dt
,p_strtd_dt => l_strtd_dt
,p_voidd_dt => l_voidd_dt);
p_program_update_date => trunc(SYSDATE),
p_enrt_perd_id => l_curr_epe.enrt_perd_id,
p_lee_rsn_id => l_curr_epe.lee_rsn_id,
p_cls_enrt_dt_to_use_cd => l_curr_epe.cls_enrt_dt_to_use_cd,
p_uom => l_curr_epe.uom,
p_acty_ref_perd_cd => l_curr_epe.acty_ref_perd_cd,
p_cryfwd_elig_dpnt_cd => l_curr_epe.cryfwd_elig_dpnt_cd,
-- added for cwb
p_mode => p_mode,
p_ws_mgr_id => l_mgr_person_id_temp,
p_elig_flag => 'N',
p_assignment_id => l_assignment_id ); -- l_curr_epe.elig_flag);
insert into ben_cwb_mgr_hrchy (
emp_elig_per_elctbl_chc_id,
mgr_elig_per_elctbl_chc_id,
lvl_num )
values(
l_elig_per_elctbl_chc_id,
-1,
-1);
INSERT INTO ben_enrt_rt
(
enrt_rt_id,
acty_typ_cd,
tx_typ_cd,
ctfn_rqd_flag,
dflt_flag,
dflt_pndg_ctfn_flag,
dsply_on_enrt_flag,
use_to_calc_net_flx_cr_flag,
entr_val_at_enrt_flag,
asn_on_enrt_flag,
rl_crs_only_flag,
dflt_val,
ann_val,
ann_mn_elcn_val,
ann_mx_elcn_val,
val,
nnmntry_uom,
mx_elcn_val,
mn_elcn_val,
incrmt_elcn_val,
cmcd_acty_ref_perd_cd,
cmcd_mn_elcn_val,
cmcd_mx_elcn_val,
cmcd_val,
cmcd_dflt_val,
rt_usg_cd,
ann_dflt_val,
bnft_rt_typ_cd,
rt_mlt_cd,
dsply_mn_elcn_val,
dsply_mx_elcn_val,
entr_ann_val_flag,
rt_strt_dt,
rt_strt_dt_cd,
rt_strt_dt_rl,
rt_typ_cd,
elig_per_elctbl_chc_id,
acty_base_rt_id,
spcl_rt_enrt_rt_id,
enrt_bnft_id,
prtt_rt_val_id,
decr_bnft_prvdr_pool_id,
cvg_amt_calc_mthd_id,
actl_prem_id,
comp_lvl_fctr_id,
ptd_comp_lvl_fctr_id,
clm_comp_lvl_fctr_id,
business_group_id,
ecr_attribute_category,
ecr_attribute1,
ecr_attribute2,
ecr_attribute3,
ecr_attribute4,
ecr_attribute5,
ecr_attribute6,
ecr_attribute7,
ecr_attribute8,
ecr_attribute9,
ecr_attribute10,
ecr_attribute11,
ecr_attribute12,
ecr_attribute13,
ecr_attribute14,
ecr_attribute15,
ecr_attribute16,
ecr_attribute17,
ecr_attribute18,
ecr_attribute19,
ecr_attribute20,
ecr_attribute21,
ecr_attribute22,
ecr_attribute23,
ecr_attribute24,
ecr_attribute25,
ecr_attribute26,
ecr_attribute27,
ecr_attribute28,
ecr_attribute29,
ecr_attribute30,
last_update_login,
created_by,
creation_date,
last_updated_by,
last_update_date,
request_id,
program_application_id,
program_id,
program_update_date,
object_version_number)
VALUES(
ben_enrt_rt_s.nextval,
l_ecr_rec.acty_typ_cd,
l_ecr_rec.tx_typ_cd,
l_ecr_rec.ctfn_rqd_flag,
l_ecr_rec.dflt_flag,
l_ecr_rec.dflt_pndg_ctfn_flag,
l_ecr_rec.dsply_on_enrt_flag,
l_ecr_rec.use_to_calc_net_flx_cr_flag,
l_ecr_rec.entr_val_at_enrt_flag,
l_ecr_rec.asn_on_enrt_flag,
l_ecr_rec.rl_crs_only_flag,
l_ecr_rec.dflt_val,
l_ecr_rec.ann_val,
l_ecr_rec.ann_mn_elcn_val,
l_ecr_rec.ann_mx_elcn_val,
l_ecr_rec.val,
l_ecr_rec.nnmntry_uom,
-- Initially they have to go null.
null, -- l_ecr_rec.mx_elcn_val,
null, -- l_ecr_rec.mn_elcn_val,
l_ecr_rec.incrmt_elcn_val,
l_ecr_rec.cmcd_acty_ref_perd_cd,
-- Initially they have to go null.
null, --l_ecr_rec.cmcd_mn_elcn_val,
null, --l_ecr_rec.cmcd_mx_elcn_val,
l_ecr_rec.cmcd_val,
null,
l_ecr_rec.rt_usg_cd,
l_ecr_rec.ann_dflt_val,
l_ecr_rec.bnft_rt_typ_cd,
l_ecr_rec.rt_mlt_cd,
-- Initially they have to go null.
null, -- l_ecr_rec.dsply_mn_elcn_val,
null, -- l_ecr_rec.dsply_mx_elcn_val,
l_ecr_rec.entr_ann_val_flag,
l_ecr_rec.rt_strt_dt,
l_ecr_rec.rt_strt_dt_cd,
l_ecr_rec.rt_strt_dt_rl,
l_ecr_rec.rt_typ_cd,
l_elig_per_elctbl_chc_id,
l_ecr_rec.acty_base_rt_id,
null,
null, -- enrt_bnft_id : Should be null
null, -- prtt_rt_val_id : should be null
l_ecr_rec.decr_bnft_prvdr_pool_id,
l_ecr_rec.cvg_amt_calc_mthd_id,
l_ecr_rec.actl_prem_id,
l_ecr_rec.comp_lvl_fctr_id,
l_ecr_rec.ptd_comp_lvl_fctr_id,
l_ecr_rec.clm_comp_lvl_fctr_id,
l_curr_epe.per_business_group_id,
l_ecr_rec.ecr_attribute_category,
l_ecr_rec.ecr_attribute1,
l_ecr_rec.ecr_attribute2,
l_ecr_rec.ecr_attribute3,
l_ecr_rec.ecr_attribute4,
l_ecr_rec.ecr_attribute5,
l_ecr_rec.ecr_attribute6,
l_ecr_rec.ecr_attribute7,
l_ecr_rec.ecr_attribute8,
l_ecr_rec.ecr_attribute9,
l_ecr_rec.ecr_attribute10,
l_ecr_rec.ecr_attribute11,
l_ecr_rec.ecr_attribute12,
l_ecr_rec.ecr_attribute13,
l_ecr_rec.ecr_attribute14,
l_ecr_rec.ecr_attribute15,
l_ecr_rec.ecr_attribute16,
l_ecr_rec.ecr_attribute17,
l_ecr_rec.ecr_attribute18,
l_ecr_rec.ecr_attribute19,
l_ecr_rec.ecr_attribute20,
l_ecr_rec.ecr_attribute21,
l_ecr_rec.ecr_attribute22,
l_ecr_rec.ecr_attribute23,
l_ecr_rec.ecr_attribute24,
l_ecr_rec.ecr_attribute25,
l_ecr_rec.ecr_attribute26,
l_ecr_rec.ecr_attribute27,
l_ecr_rec.ecr_attribute28,
l_ecr_rec.ecr_attribute29,
l_ecr_rec.ecr_attribute30,
l_last_update_login,
l_created_by,
l_creation_date,
l_last_updated_by,
trunc(SYSDATE),
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
trunc(SYSDATE),
l_object_version_number);
procedure update_cwb_epe
(p_per_in_ler_id number,
p_effective_date date) is
l_elig_per_elctbl_chc_id number;
l_package varchar2(80) := g_package||'.update_cwb_epe';
select pl.trk_inelig_per_flag
from ben_pl_f pl
where pl.pl_id = l_pl_id
and p_effective_date between pl.effective_start_date and
pl.effective_end_date;
select 'x'
from ben_oipl_f oipl
where oipl.pl_id = l_pl_id
and p_effective_date between oipl.effective_start_date and
oipl.effective_end_date;
select epe1.elig_per_elctbl_chc_id,
epe1.object_version_number,
epe1.pl_id
from ben_elig_per_elctbl_chc epe1
where epe1.per_in_ler_id = p_per_in_ler_id
and epe1.pl_id is not null
and epe1.oipl_id is null;
select 'x'
from ben_elig_per_elctbl_chc epe2
where epe2.per_in_ler_id = p_per_in_ler_id
and epe2.oipl_id is not null
and epe2.pl_id = l_pl_id
and epe2.elig_flag ='Y';
/* GLOBALCWB : If this data is deleted then no cwb rates are written
co do not delete
ben_elig_per_elc_chc_api.delete_ELIG_PER_ELC_CHC
(p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date);
ben_elig_per_elc_chc_api.update_perf_ELIG_PER_ELC_CHC
(p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
,p_elig_flag => 'N'
,p_inelig_rsn_cd => 'OTH'
,p_object_version_number => l_ovn
,p_effective_date => p_effective_date);
select pil.per_in_ler_id,
ptnl.ptnl_ler_for_per_id,
ptnl.object_version_number ptnl_ovn,
pil.object_version_number pil_ovn
from ben_per_in_ler pil,
ben_ptnl_ler_for_per ptnl
where pil.ler_id = p_ler_id
and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and pil.person_id = p_person_id
and ptnl.ptnl_ler_for_per_id = pil.ptnl_ler_for_per_id
and not exists
(select 'x'
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = pil.per_in_ler_id);
select pil_elctbl_chc_popl_id,
object_version_number
from ben_pil_elctbl_chc_popl
where per_in_ler_id = l_pil_rec.per_in_ler_id;
ben_Pil_Elctbl_chc_Popl_api.delete_Pil_Elctbl_chc_Popl
(p_pil_elctbl_chc_popl_id => l_popl_rec.pil_elctbl_chc_popl_id,
p_object_version_number => l_popl_rec.object_version_number,
p_effective_date => p_effective_date);
ben_Person_Life_Event_api.delete_Person_Life_Event
(p_per_in_ler_id => l_pil_rec.per_in_ler_id,
p_object_version_number => l_pil_rec.pil_ovn,
p_effective_date => p_effective_date);
ben_ptnl_ler_for_per_api.delete_ptnl_ler_for_per
(p_ptnl_ler_for_per_id => l_pil_rec.ptnl_ler_for_per_id,
p_object_version_number => l_pil_rec.ptnl_ovn,
p_effective_date => p_effective_date);
p_person_selection_rule_id in number,
p_comp_selection_rule_id in number,
-- PB : 5422 :
-- p_popl_enrt_typ_cycl_id in number,
p_derivable_factors in varchar2,
p_cbr_tmprl_evt_flag in varchar2,
p_person_count in out nocopy number,
p_error_person_count in out nocopy number,
p_thread_id in number,
p_validate in varchar2,
p_effective_date in date,
p_lf_evt_ocrd_dt in date,
p_gsp_eval_elig_flag in varchar2 default null, /* GSP Rate Sync */
p_lf_evt_oper_cd in varchar2 default null /* GSP Rate Sync */
) is
--
l_ler_id ben_person_actions.ler_id%type;
select ben.person_id,
ben.person_action_id,
ben.object_version_number,
ben.ler_id
from ben_person_actions ben
where ben.benefit_action_id = p_benefit_action_id
and ben.action_status_cd <> 'P'
and ben.person_action_id
between p_start_person_action_id
and p_end_person_action_id
order by ben.person_action_id;
select ptnl_ler_for_per_id
from ben_ptnl_ler_for_per pfl,
ben_ler_f ler
where pfl.ptnl_ler_for_per_stat_cd not in ('VOIDD','PROCD')
and pfl.person_id = p_person_id
and pfl.ler_id = ler.ler_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and pfl.lf_evt_ocrd_dt <= p_effective_date
and ler.typ_cd = decode(p_mode,'M','ABS','G','GSP',null)
order by pfl.lf_evt_ocrd_dt asc, ler.lf_evt_oper_cd desc;
select 'x'
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_stat_cd = 'STRTD'
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 = decode(p_mode,'M','ABS','G','GSP',null);
p_person_selection_rule_id => p_person_selection_rule_id,
p_comp_selection_rule_id => p_comp_selection_rule_id,
p_derivable_factors => p_derivable_factors,
p_cbr_tmprl_evt_flag => p_cbr_tmprl_evt_flag,
p_person_count => p_person_count,
p_error_person_count => p_error_person_count,
p_effective_date => p_effective_date,
p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
p_validate => p_validate, /* Bug 5550359 */
p_gsp_eval_elig_flag => p_gsp_eval_elig_flag, /* GSP Rate Sync */
p_lf_evt_oper_cd => p_lf_evt_oper_cd /* GSP Rate Sync */
);
p_person_selection_rule_id in number,
p_comp_selection_rule_id in number,
p_derivable_factors in varchar2,
p_thread_id in number,
p_lf_evt_ocrd_dt in varchar2,
p_cbr_tmprl_evt_flag in varchar2,
p_lmt_prpnip_by_org_flag in varchar2,
p_gsp_eval_elig_flag in varchar2 default null, /* GSP Rate Sync */
p_lf_evt_oper_cd in varchar2 default null /* GSP Rate Sync */
) is
--
l_package varchar2(80);
,p_comp_selection_rule_id => p_comp_selection_rule_id
,p_effective_date => l_effective_date
,p_pgm_id => p_pgm_id
,p_business_group_id => p_business_group_id
,p_pl_id => p_pl_id
-- PB : 5422 :
-- Pass on the asnd_lf_evt_dt
--
,p_asnd_lf_evt_dt => null
-- ,p_popl_enrt_typ_cycl_id => p_popl_enrt_typ_cycl_id
,p_no_programs => p_no_programs
,p_no_plans => p_no_plans
,p_rptg_grp_id => p_rptg_grp_id
,p_pl_typ_id => p_pl_typ_id
,p_opt_id => p_opt_id
,p_eligy_prfl_id => p_eligy_prfl_id
,p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
,p_thread_id => p_thread_id
,p_mode => p_mode
);
p_person_selection_rule_id => p_person_selection_rule_id,
p_comp_selection_rule_id => p_comp_selection_rule_id,
--
-- PB : 5422 :
-- Following parameter is no longer needed,
--
-- p_popl_enrt_typ_cycl_id => p_popl_enrt_typ_cycl_id,
p_derivable_factors => p_derivable_factors,
p_cbr_tmprl_evt_flag => p_cbr_tmprl_evt_flag,
p_person_count => l_person_count,
p_error_person_count => l_error_person_count,
p_thread_id => p_thread_id,
p_validate => p_validate,
p_effective_date => l_effective_date,
p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
p_gsp_eval_elig_flag => p_gsp_eval_elig_flag, /* GSP Rate Sync */
p_lf_evt_oper_cd => p_lf_evt_oper_cd /* GSP Rate Sync */
);
select process_date,
mode_cd,
derivable_factors_flag,
validate_flag,
person_id,
person_type_id,
pgm_id,
business_group_id,
pl_id,
--
-- PB : 5422 : No longer needed.
-- popl_enrt_typ_cycl_id,
--
no_programs_flag,
no_plans_flag,
comp_selection_rl,
person_selection_rl,
ler_id,
organization_id,
benfts_grp_id,
location_id,
pstl_zip_rng_id,
rptg_grp_id,
pl_typ_id,
opt_id,
eligy_prfl_id,
vrbl_rt_prfl_id,
legal_entity_id,
payroll_id,
audit_log_flag,
debug_messages_flag,
-- PB : 5422 :
-- Add lf_evt_ocrd_dt
lf_evt_ocrd_dt,
lmt_prpnip_by_org_flag,
inelg_action_cd
from ben_benefit_actions ben
where ben.benefit_action_id = p_benefit_action_id;
p_comp_selection_rule_id => l_parameters.comp_selection_rl,
p_person_selection_rule_id => l_parameters.person_selection_rl,
p_ler_id => l_parameters.ler_id,
p_organization_id => l_parameters.organization_id,
p_benfts_grp_id => l_parameters.benfts_grp_id,
p_location_id => l_parameters.location_id,
p_pstl_zip_rng_id => l_parameters.pstl_zip_rng_id,
p_rptg_grp_id => l_parameters.rptg_grp_id,
p_pl_typ_id => l_parameters.pl_typ_id,
p_opt_id => l_parameters.opt_id,
p_eligy_prfl_id => l_parameters.eligy_prfl_id,
p_vrbl_rt_prfl_id => l_parameters.vrbl_rt_prfl_id,
p_legal_entity_id => l_parameters.legal_entity_id,
p_payroll_id => l_parameters.payroll_id,
p_commit_data => 'Y',
p_audit_log_flag => l_parameters.audit_log_flag,
p_cbr_tmprl_evt_flag => l_parameters.debug_messages_flag,--cobra
p_abs_historical_mode => l_parameters.inelg_action_cd,
p_lmt_prpnip_by_org_flag => l_parameters.lmt_prpnip_by_org_flag,
-- PB : 5422 :
-- Now pass on the lf_evt_ocrd_dt
p_lf_evt_ocrd_dt => fnd_date.date_to_canonical(l_parameters.lf_evt_ocrd_dt));
p_comp_selection_rule_id in number default null,
p_person_selection_rule_id in number default null,
p_ler_id in number default null,
p_organization_id in number default null,
p_benfts_grp_id in number default null,
p_location_id in number default null,
p_pstl_zip_rng_id in number default null,
p_rptg_grp_id in number default null,
p_pl_typ_id in number default null,
p_opt_id in number default null,
p_eligy_prfl_id in number default null,
p_vrbl_rt_prfl_id in number default null,
p_legal_entity_id in number default null,
p_payroll_id in number default null,
p_commit_data in varchar2 default 'Y',
p_audit_log_flag in varchar2 default 'N',
p_lmt_prpnip_by_org_flag in varchar2 default 'N',
p_cbr_tmprl_evt_flag in varchar2 default 'N',
p_cwb_person_type in varchar2 default null) is
--
l_retcode number;
select parent_request_id
from fnd_concurrent_requests
where request_id = fnd_global.conc_request_id;
p_comp_selection_rule_id =>p_comp_selection_rule_id,
p_person_selection_rule_id =>p_person_selection_rule_id,
p_ler_id =>p_ler_id,
p_organization_id =>p_organization_id,
p_benfts_grp_id =>p_benfts_grp_id,
p_location_id =>p_location_id,
p_pstl_zip_rng_id =>p_pstl_zip_rng_id,
p_rptg_grp_id =>p_rptg_grp_id,
p_pl_typ_id =>p_pl_typ_id,
p_opt_id =>p_opt_id,
p_eligy_prfl_id =>p_eligy_prfl_id,
p_vrbl_rt_prfl_id =>p_vrbl_rt_prfl_id,
p_legal_entity_id =>p_legal_entity_id,
p_payroll_id =>p_payroll_id,
p_commit_data =>p_commit_data,
p_audit_log_flag =>p_audit_log_flag,
p_lmt_prpnip_by_org_flag =>p_lmt_prpnip_by_org_flag,
p_cbr_tmprl_evt_flag =>p_cbr_tmprl_evt_flag ,
p_cwb_person_type => p_cwb_person_type);
p_comp_selection_rule_id in number default null,
p_person_selection_rule_id in number default null,
p_ler_id in number default null,
p_organization_id in number default null,
p_benfts_grp_id in number default null,
p_location_id in number default null,
p_pstl_zip_rng_id in number default null,
p_rptg_grp_id in number default null,
p_pl_typ_id in number default null,
p_opt_id in number default null,
p_eligy_prfl_id in number default null,
p_vrbl_rt_prfl_id in number default null,
p_legal_entity_id in number default null,
p_payroll_id in number default null,
p_commit_data in varchar2 default 'Y',
p_audit_log_flag in varchar2 default 'N',
p_lmt_prpnip_by_org_flag in varchar2 default 'N',
p_cbr_tmprl_evt_flag in varchar2 default 'N',
p_trace_plans_flag in varchar2 default 'N',
p_cwb_person_type in varchar2 default null,
p_run_rollup_only in varchar2 default 'N' /* Bug 4875181 */
) is
--
l_retcode number;
p_comp_selection_rule_id =>p_comp_selection_rule_id,
p_person_selection_rule_id =>p_person_selection_rule_id,
p_ler_id =>p_ler_id,
p_organization_id =>p_organization_id,
p_benfts_grp_id =>p_benfts_grp_id,
p_location_id =>p_location_id,
p_pstl_zip_rng_id =>p_pstl_zip_rng_id,
p_rptg_grp_id =>p_rptg_grp_id,
p_pl_typ_id =>p_pl_typ_id,
p_opt_id =>p_opt_id,
p_eligy_prfl_id =>p_eligy_prfl_id,
p_vrbl_rt_prfl_id =>p_vrbl_rt_prfl_id,
p_legal_entity_id =>p_legal_entity_id,
p_payroll_id =>p_payroll_id,
p_commit_data =>p_commit_data,
p_audit_log_flag =>p_audit_log_flag,
p_lmt_prpnip_by_org_flag =>p_lmt_prpnip_by_org_flag,
p_cbr_tmprl_evt_flag =>p_cbr_tmprl_evt_flag,
p_trace_plans_flag =>p_trace_plans_flag,
p_cwb_person_type => p_cwb_person_type,
p_run_rollup_only =>p_run_rollup_only /* Bug 4875181 */
);
,p_comp_selection_rule_id in number default null
,p_person_selection_rule_id in number default null
,p_ler_id in number default null
,p_organization_id in number default null
,p_benfts_grp_id in number default null
,p_location_id in number default null
,p_pstl_zip_rng_id in number default null
,p_rptg_grp_id in number default null
,p_pl_typ_id in number default null
,p_opt_id in number default null
,p_eligy_prfl_id in number default null
,p_vrbl_rt_prfl_id in number default null
,p_legal_entity_id in number default null
,p_payroll_id in number default null
-- GRADE/STEP : Added for grade/step benmngle
,p_org_heirarchy_id in number default null
,p_org_starting_node_id in number default null
,p_grade_ladder_id in number default null
,p_asg_events_to_all_sel_dt in varchar2 default null
,p_rate_id in number default null -- pay scale
,p_per_sel_dt_cd in varchar2 default null -- business rule
,p_per_sel_dt_from in varchar2 default null -- business rule date from
,p_per_sel_dt_to in varchar2 default null -- business rule date to
,p_per_sel_freq_cd in varchar2 default null -- 2940151
,p_year_from in number default null -- business rule year from
,p_year_to in number default null -- business rule year to
,p_cagr_id in number default null -- Coll agreement id
,p_qual_type in number default null
,p_qual_status in varchar2 default null
-- 2940151
,p_id_flex_num in number default null
,p_concat_segs in varchar2 default null
-- end 2940151
-- GRADE/STEP : End
,p_commit_data in varchar2 default 'Y'
,p_audit_log_flag in varchar2 default 'N'
,p_lmt_prpnip_by_org_flag in varchar2 default 'N'
,p_cbr_tmprl_evt_flag in varchar2 default 'N'
)is
--
l_bin binary_integer ;
p_comp_selection_rule_id =>p_comp_selection_rule_id,
p_person_selection_rule_id =>p_person_selection_rule_id,
p_ler_id =>p_ler_id,
p_organization_id =>p_organization_id,
p_benfts_grp_id =>p_benfts_grp_id,
p_location_id =>p_location_id,
p_pstl_zip_rng_id =>p_pstl_zip_rng_id,
p_rptg_grp_id =>p_rptg_grp_id,
p_pl_typ_id =>p_pl_typ_id,
p_opt_id =>p_opt_id,
p_eligy_prfl_id =>p_eligy_prfl_id,
p_vrbl_rt_prfl_id =>p_vrbl_rt_prfl_id,
p_legal_entity_id =>p_legal_entity_id,
p_payroll_id =>p_payroll_id,
p_commit_data =>p_commit_data,
p_audit_log_flag =>p_audit_log_flag,
p_lmt_prpnip_by_org_flag =>p_lmt_prpnip_by_org_flag,
p_cbr_tmprl_evt_flag =>p_cbr_tmprl_evt_flag ,
-- passed these gsp parameters
-- version 115.329
p_org_heirarchy_id =>p_org_heirarchy_id,
p_org_starting_node_id =>p_org_starting_node_id,
p_grade_ladder_id =>p_grade_ladder_id,
p_asg_events_to_all_sel_dt =>p_asg_events_to_all_sel_dt,
p_rate_id =>p_rate_id,
p_per_sel_dt_cd =>p_per_sel_dt_cd,
p_per_sel_dt_from => fnd_date.canonical_to_date(p_per_sel_dt_from),
p_per_sel_dt_to => fnd_date.canonical_to_date(p_per_sel_dt_to),
p_year_from =>p_year_from,
p_year_to =>p_year_to,
p_cagr_id =>p_cagr_id,
p_qual_type =>p_qual_type,
p_qual_status =>p_qual_status,
-- 2940151
p_per_sel_freq_cd =>p_per_sel_freq_cd,
p_concat_segs =>p_concat_segs,
p_gsp_eval_elig_flag => 'Y', /* GSP Rate Sync */
p_lf_evt_oper_cd => 'PROG' /* GSP Rate Sync */
-- end 2940151
);
,p_comp_selection_rule_id in number default null
,p_person_selection_rule_id in number default null
,p_organization_id in number default null
,p_benfts_grp_id in number default null
,p_location_id in number default null
,p_pstl_zip_rng_id in number default null
,p_rptg_grp_id in number default null
,p_legal_entity_id in number default null
,p_payroll_id in number default null
,p_org_heirarchy_id in number default null
,p_org_starting_node_id in number default null
,p_grade_ladder_id in number default null
,p_rate_id in number default null -- pay scale
,p_per_sel_dt_cd in varchar2 default null -- business rule
,p_per_sel_dt_from in varchar2 default null -- business rule date from
,p_per_sel_dt_to in varchar2 default null -- business rule date to
,p_per_sel_freq_cd in varchar2 default null -- 2940151
,p_year_from in number default null -- business rule year from
,p_year_to in number default null -- business rule year to
,p_cagr_id in number default null -- Coll agreement id
,p_qual_type in number default null
,p_qual_status in varchar2 default null
,p_id_flex_num in number default null
,p_concat_segs in varchar2 default null
,p_commit_data in varchar2 default 'Y'
,p_gsp_eval_elig_flag in varchar2 default 'N' -- Evaluate Eligibility
,p_audit_log_flag in varchar2 default 'N'
,p_lmt_prpnip_by_org_flag in varchar2 default 'N'
)is
--
l_bin binary_integer ;
p_comp_selection_rule_id => p_comp_selection_rule_id,
p_person_selection_rule_id => p_person_selection_rule_id,
p_organization_id => p_organization_id,
p_benfts_grp_id => p_benfts_grp_id,
p_location_id => p_location_id,
p_pstl_zip_rng_id => p_pstl_zip_rng_id,
p_rptg_grp_id => p_rptg_grp_id,
p_legal_entity_id => p_legal_entity_id,
p_payroll_id => p_payroll_id,
p_org_heirarchy_id => p_org_heirarchy_id,
p_org_starting_node_id => p_org_starting_node_id,
p_grade_ladder_id => p_grade_ladder_id,
p_rate_id => p_rate_id,
p_per_sel_dt_cd => p_per_sel_dt_cd,
p_per_sel_dt_from => p_per_sel_dt_from,
p_per_sel_dt_to => p_per_sel_dt_to,
p_per_sel_freq_cd => p_per_sel_freq_cd,
p_year_from => p_year_from,
p_year_to => p_year_to,
p_cagr_id => p_cagr_id,
p_qual_type => p_qual_type,
p_qual_status => p_qual_status,
p_concat_segs => p_concat_segs,
p_commit_data => p_commit_data,
p_audit_log_flag => p_audit_log_flag,
p_lmt_prpnip_by_org_flag => p_lmt_prpnip_by_org_flag,
p_asg_events_to_all_sel_dt => p_effective_date, -- Pass Effective Date as PTNL Life Event Occurred Date
p_gsp_eval_elig_flag => p_gsp_eval_elig_flag,
p_lf_evt_oper_cd => 'SYNC' -- Life Event Operation code for Rate Synchronization
);
p_comp_selection_rule_id in number default null,
p_person_selection_rule_id in number default null,
p_ler_id in number default null,
p_organization_id in number default null,
p_benfts_grp_id in number default null,
p_location_id in number default null,
p_pstl_zip_rng_id in number default null,
p_rptg_grp_id in number default null,
p_pl_typ_id in number default null,
p_opt_id in number default null,
p_eligy_prfl_id in number default null,
p_vrbl_rt_prfl_id in number default null,
p_legal_entity_id in number default null,
p_payroll_id in number default null,
p_commit_data in varchar2 default 'Y',
p_audit_log_flag in varchar2 default 'N',
p_lmt_prpnip_by_org_flag in varchar2 default 'N',
p_abs_historical_mode in varchar2 default 'N',
p_cbr_tmprl_evt_flag in varchar2 default 'N') is
--
l_retcode number;
p_comp_selection_rule_id =>p_comp_selection_rule_id,
p_person_selection_rule_id =>p_person_selection_rule_id,
p_ler_id =>p_ler_id,
p_organization_id =>p_organization_id,
p_benfts_grp_id =>p_benfts_grp_id,
p_location_id =>p_location_id,
p_pstl_zip_rng_id =>p_pstl_zip_rng_id,
p_rptg_grp_id =>p_rptg_grp_id,
p_pl_typ_id =>p_pl_typ_id,
p_opt_id =>p_opt_id,
p_eligy_prfl_id =>p_eligy_prfl_id,
p_vrbl_rt_prfl_id =>p_vrbl_rt_prfl_id,
p_legal_entity_id =>p_legal_entity_id,
p_payroll_id =>p_payroll_id,
p_commit_data =>p_commit_data,
p_audit_log_flag =>p_audit_log_flag,
p_lmt_prpnip_by_org_flag =>p_lmt_prpnip_by_org_flag,
p_cbr_tmprl_evt_flag =>p_cbr_tmprl_evt_flag,
p_abs_historical_mode =>p_abs_historical_mode);
p_comp_selection_rule_id in number default null,
p_person_selection_rule_id in number default null,
p_ler_id in number default null,
p_organization_id in number default null,
p_benfts_grp_id in number default null,
p_location_id in number default null,
p_pstl_zip_rng_id in number default null,
p_rptg_grp_id in number default null,
p_pl_typ_id in number default null,
p_opt_id in number default null,
p_eligy_prfl_id in number default null,
p_vrbl_rt_prfl_id in number default null,
p_legal_entity_id in number default null,
p_payroll_id in number default null,
p_commit_data in varchar2 default 'Y',
p_audit_log_flag in varchar2 default 'N',
p_lmt_prpnip_by_org_flag in varchar2 default 'N',
p_abs_historical_mode in varchar2 default 'N',
p_cbr_tmprl_evt_flag in varchar2 default 'N',
p_assignment_id in number default null,
p_offer_assignment_rec in per_all_assignments_f%rowtype) ----bug 4621751 irec2
is
--
l_retcode number;
select ass.*
from per_all_assignments_F ass
where assignment_id = p_assignment_id
and to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS') between ass.effective_start_date
and ass.effective_end_date;
select ass.*
from per_all_assignments_F ass
where assignment_id = p_assignment_id
and to_date(p_effective_date,'YYYY/MM/DD HH24:MI:SS') between ass.effective_start_date
and ass.effective_end_date;
update per_all_assignments_F
set
BUSINESS_GROUP_ID = p_offer_assignment_rec.BUSINESS_GROUP_ID
,RECRUITER_ID = p_offer_assignment_rec.RECRUITER_ID
,GRADE_ID = p_offer_assignment_rec.GRADE_ID
,POSITION_ID = p_offer_assignment_rec.POSITION_ID
,JOB_ID = p_offer_assignment_rec.JOB_ID
,ASSIGNMENT_STATUS_TYPE_ID = p_offer_assignment_rec.ASSIGNMENT_STATUS_TYPE_ID
,PAYROLL_ID = p_offer_assignment_rec.PAYROLL_ID
,LOCATION_ID = p_offer_assignment_rec.LOCATION_ID
,PERSON_REFERRED_BY_ID = p_offer_assignment_rec.PERSON_REFERRED_BY_ID
,SUPERVISOR_ID = p_offer_assignment_rec.SUPERVISOR_ID
,SPECIAL_CEILING_STEP_ID = p_offer_assignment_rec.SPECIAL_CEILING_STEP_ID
,PERSON_ID = p_offer_assignment_rec.PERSON_ID
,RECRUITMENT_ACTIVITY_ID = p_offer_assignment_rec.RECRUITMENT_ACTIVITY_ID
,SOURCE_ORGANIZATION_ID = p_offer_assignment_rec.SOURCE_ORGANIZATION_ID
,ORGANIZATION_ID = p_offer_assignment_rec.ORGANIZATION_ID
,PEOPLE_GROUP_ID = p_offer_assignment_rec.PEOPLE_GROUP_ID
,SOFT_CODING_KEYFLEX_ID = p_offer_assignment_rec.SOFT_CODING_KEYFLEX_ID
,VACANCY_ID = p_offer_assignment_rec.VACANCY_ID
,PAY_BASIS_ID = p_offer_assignment_rec.PAY_BASIS_ID
,ASSIGNMENT_SEQUENCE = p_offer_assignment_rec.ASSIGNMENT_SEQUENCE
,APPLICATION_ID = p_offer_assignment_rec.APPLICATION_ID
,ASSIGNMENT_NUMBER = p_offer_assignment_rec.ASSIGNMENT_NUMBER
,CHANGE_REASON = p_offer_assignment_rec.CHANGE_REASON
,COMMENT_ID = p_offer_assignment_rec.COMMENT_ID
,DATE_PROBATION_END = p_offer_assignment_rec.DATE_PROBATION_END
,DEFAULT_CODE_COMB_ID = p_offer_assignment_rec.DEFAULT_CODE_COMB_ID
,EMPLOYMENT_CATEGORY = p_offer_assignment_rec.EMPLOYMENT_CATEGORY
,FREQUENCY = p_offer_assignment_rec.FREQUENCY
,INTERNAL_ADDRESS_LINE = p_offer_assignment_rec.INTERNAL_ADDRESS_LINE
,MANAGER_FLAG = p_offer_assignment_rec.MANAGER_FLAG
,NORMAL_HOURS = p_offer_assignment_rec.NORMAL_HOURS
,PERF_REVIEW_PERIOD = p_offer_assignment_rec.PERF_REVIEW_PERIOD
,PERF_REVIEW_PERIOD_FREQUENCY = p_offer_assignment_rec.PERF_REVIEW_PERIOD_FREQUENCY
,PERIOD_OF_SERVICE_ID = p_offer_assignment_rec.PERIOD_OF_SERVICE_ID
,PROBATION_PERIOD = p_offer_assignment_rec.PROBATION_PERIOD
,PROBATION_UNIT = p_offer_assignment_rec.PROBATION_UNIT
,SAL_REVIEW_PERIOD = p_offer_assignment_rec.SAL_REVIEW_PERIOD
,SAL_REVIEW_PERIOD_FREQUENCY = p_offer_assignment_rec.SAL_REVIEW_PERIOD_FREQUENCY
,SET_OF_BOOKS_ID = p_offer_assignment_rec.SET_OF_BOOKS_ID
,SOURCE_TYPE = p_offer_assignment_rec.SOURCE_TYPE
,TIME_NORMAL_FINISH = p_offer_assignment_rec.TIME_NORMAL_FINISH
,TIME_NORMAL_START = p_offer_assignment_rec.TIME_NORMAL_START
,REQUEST_ID = p_offer_assignment_rec.REQUEST_ID
,PROGRAM_APPLICATION_ID = p_offer_assignment_rec.PROGRAM_APPLICATION_ID
,PROGRAM_ID = p_offer_assignment_rec.PROGRAM_ID
,PROGRAM_UPDATE_DATE = p_offer_assignment_rec.PROGRAM_UPDATE_DATE
,ASS_ATTRIBUTE_CATEGORY = p_offer_assignment_rec.ASS_ATTRIBUTE_CATEGORY
,ASS_ATTRIBUTE1 = p_offer_assignment_rec.ASS_ATTRIBUTE1
,ASS_ATTRIBUTE2 = p_offer_assignment_rec.ASS_ATTRIBUTE2
,ASS_ATTRIBUTE3 = p_offer_assignment_rec.ASS_ATTRIBUTE3
,ASS_ATTRIBUTE4 = p_offer_assignment_rec.ASS_ATTRIBUTE4
,ASS_ATTRIBUTE5 = p_offer_assignment_rec.ASS_ATTRIBUTE5
,ASS_ATTRIBUTE6 = p_offer_assignment_rec.ASS_ATTRIBUTE6
,ASS_ATTRIBUTE7 = p_offer_assignment_rec.ASS_ATTRIBUTE7
,ASS_ATTRIBUTE8 = p_offer_assignment_rec.ASS_ATTRIBUTE8
,ASS_ATTRIBUTE9 = p_offer_assignment_rec.ASS_ATTRIBUTE9
,ASS_ATTRIBUTE10 = p_offer_assignment_rec.ASS_ATTRIBUTE10
,ASS_ATTRIBUTE11 = p_offer_assignment_rec.ASS_ATTRIBUTE11
,ASS_ATTRIBUTE12 = p_offer_assignment_rec.ASS_ATTRIBUTE12
,ASS_ATTRIBUTE13 = p_offer_assignment_rec.ASS_ATTRIBUTE13
,ASS_ATTRIBUTE14 = p_offer_assignment_rec.ASS_ATTRIBUTE14
,ASS_ATTRIBUTE15 = p_offer_assignment_rec.ASS_ATTRIBUTE15
,ASS_ATTRIBUTE16 = p_offer_assignment_rec.ASS_ATTRIBUTE16
,ASS_ATTRIBUTE17 = p_offer_assignment_rec.ASS_ATTRIBUTE17
,ASS_ATTRIBUTE18 = p_offer_assignment_rec.ASS_ATTRIBUTE18
,ASS_ATTRIBUTE19 = p_offer_assignment_rec.ASS_ATTRIBUTE19
,ASS_ATTRIBUTE20 = p_offer_assignment_rec.ASS_ATTRIBUTE20
,ASS_ATTRIBUTE21 = p_offer_assignment_rec.ASS_ATTRIBUTE21
,ASS_ATTRIBUTE22 = p_offer_assignment_rec.ASS_ATTRIBUTE22
,ASS_ATTRIBUTE23 = p_offer_assignment_rec.ASS_ATTRIBUTE23
,ASS_ATTRIBUTE24 = p_offer_assignment_rec.ASS_ATTRIBUTE24
,ASS_ATTRIBUTE25 = p_offer_assignment_rec.ASS_ATTRIBUTE25
,ASS_ATTRIBUTE26 = p_offer_assignment_rec.ASS_ATTRIBUTE26
,ASS_ATTRIBUTE27 = p_offer_assignment_rec.ASS_ATTRIBUTE27
,ASS_ATTRIBUTE28 = p_offer_assignment_rec.ASS_ATTRIBUTE28
,ASS_ATTRIBUTE29 = p_offer_assignment_rec.ASS_ATTRIBUTE29
,ASS_ATTRIBUTE30 = p_offer_assignment_rec.ASS_ATTRIBUTE30
,TITLE = p_offer_assignment_rec.TITLE
,OBJECT_VERSION_NUMBER = p_offer_assignment_rec.OBJECT_VERSION_NUMBER
,BARGAINING_UNIT_CODE = p_offer_assignment_rec.BARGAINING_UNIT_CODE
,LABOUR_UNION_MEMBER_FLAG = p_offer_assignment_rec.LABOUR_UNION_MEMBER_FLAG
,HOURLY_SALARIED_CODE = p_offer_assignment_rec.HOURLY_SALARIED_CODE
,CONTRACT_ID = p_offer_assignment_rec.CONTRACT_ID
,COLLECTIVE_AGREEMENT_ID = p_offer_assignment_rec.COLLECTIVE_AGREEMENT_ID
,CAGR_ID_FLEX_NUM = p_offer_assignment_rec.CAGR_ID_FLEX_NUM
,CAGR_GRADE_DEF_ID = p_offer_assignment_rec.CAGR_GRADE_DEF_ID
,ESTABLISHMENT_ID = p_offer_assignment_rec.ESTABLISHMENT_ID
,NOTICE_PERIOD = p_offer_assignment_rec.NOTICE_PERIOD
,NOTICE_PERIOD_UOM = p_offer_assignment_rec.NOTICE_PERIOD_UOM
,EMPLOYEE_CATEGORY = p_offer_assignment_rec.EMPLOYEE_CATEGORY
,WORK_AT_HOME = p_offer_assignment_rec.WORK_AT_HOME
,JOB_POST_SOURCE_NAME = p_offer_assignment_rec.JOB_POST_SOURCE_NAME
,POSTING_CONTENT_ID = p_offer_assignment_rec.POSTING_CONTENT_ID
,PERIOD_OF_PLACEMENT_DATE_START = p_offer_assignment_rec.PERIOD_OF_PLACEMENT_DATE_START
,VENDOR_ID = p_offer_assignment_rec.VENDOR_ID
,VENDOR_EMPLOYEE_NUMBER = p_offer_assignment_rec.VENDOR_EMPLOYEE_NUMBER
,VENDOR_ASSIGNMENT_NUMBER = p_offer_assignment_rec.VENDOR_ASSIGNMENT_NUMBER
,ASSIGNMENT_CATEGORY = p_offer_assignment_rec.ASSIGNMENT_CATEGORY
,PROJECT_TITLE = p_offer_assignment_rec.PROJECT_TITLE
,APPLICANT_RANK = p_offer_assignment_rec.APPLICANT_RANK
,GRADE_LADDER_PGM_ID = p_offer_assignment_rec.GRADE_LADDER_PGM_ID
,SUPERVISOR_ASSIGNMENT_ID = p_offer_assignment_rec.SUPERVISOR_ASSIGNMENT_ID
,VENDOR_SITE_ID = p_offer_assignment_rec.VENDOR_SITE_ID
,PO_HEADER_ID = p_offer_assignment_rec.PO_HEADER_ID
,PO_LINE_ID = p_offer_assignment_rec.PO_LINE_ID
,PROJECTED_ASSIGNMENT_END = p_offer_assignment_rec.PROJECTED_ASSIGNMENT_END
where assignment_id = p_assignment_id ;
p_comp_selection_rule_id =>p_comp_selection_rule_id,
p_person_selection_rule_id =>p_person_selection_rule_id,
p_ler_id =>p_ler_id,
p_organization_id =>p_organization_id,
p_benfts_grp_id =>p_benfts_grp_id,
p_location_id =>p_location_id,
p_pstl_zip_rng_id =>p_pstl_zip_rng_id,
p_rptg_grp_id =>p_rptg_grp_id,
p_pl_typ_id =>p_pl_typ_id,
p_opt_id =>p_opt_id,
p_eligy_prfl_id =>p_eligy_prfl_id,
p_vrbl_rt_prfl_id =>p_vrbl_rt_prfl_id,
p_legal_entity_id =>p_legal_entity_id,
p_payroll_id =>p_payroll_id,
p_commit_data =>p_commit_data,
p_audit_log_flag =>p_audit_log_flag,
p_lmt_prpnip_by_org_flag =>p_lmt_prpnip_by_org_flag,
p_cbr_tmprl_evt_flag =>p_cbr_tmprl_evt_flag,
p_abs_historical_mode =>p_abs_historical_mode
);
select pel.pl_id, pil.lf_evt_ocrd_dt, pel.business_group_id
from ben_pil_elctbl_chc_popl pel
,ben_per_in_ler pil
where pel.per_in_ler_id = pil.per_in_ler_id
and pel.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id;
insert into ben_cwb_hrchy (
emp_pil_elctbl_chc_popl_id,
mgr_pil_elctbl_chc_popl_id,
lvl_num )
values (
p_pil_elctbl_chc_popl_id,
-1,
-1 );
select
emp_pil_elctbl_chc_popl_id
from ben_cwb_hrchy
where mgr_pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
and lvl_num > 0;
insert into ben_cwb_hrchy (
emp_pil_elctbl_chc_popl_id,
mgr_pil_elctbl_chc_popl_id,
lvl_num ) values (r_emp_repo.emp_pil_elctbl_chc_popl_id, -1, -1);
delete from ben_cwb_hrchy
where emp_pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
and lvl_num >= 0;
delete from ben_cwb_hrchy
where emp_pil_elctbl_chc_popl_id in (
select emp_pil_elctbl_chc_popl_id
from ben_cwb_hrchy
where mgr_pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id )
and lvl_num >= 0;
insert into ben_cwb_hrchy(
emp_pil_elctbl_chc_popl_id,
mgr_pil_elctbl_chc_popl_id,
lvl_num )
select
distinct emp_pil_elctbl_chc_popl_id,
emp_pil_elctbl_chc_popl_id,
0
from ben_cwb_hrchy cwb1
where emp_pil_elctbl_chc_popl_id =
( select mgr_pil_elctbl_chc_popl_id from ben_cwb_hrchy
where emp_pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id
and lvl_num = 1 )
and not exists ( select null from ben_cwb_hrchy cwb2
where cwb1.emp_pil_elctbl_chc_popl_id = cwb2.emp_pil_elctbl_chc_popl_id
and lvl_num = 0 ) ;
p_comp_selection_rule_id in number default null,
p_person_selection_rule_id in number default null,
p_ler_id in number default null,
p_organization_id in number default null,
p_benfts_grp_id in number default null,
p_location_id in number default null,
p_pstl_zip_rng_id in number default null,
p_rptg_grp_id in number default null,
p_pl_typ_id in number default null,
p_opt_id in number default null,
p_eligy_prfl_id in number default null,
p_vrbl_rt_prfl_id in number default null,
p_legal_entity_id in number default null,
p_payroll_id in number default null,
p_commit_data in varchar2 default 'Y',
p_audit_log_flag in varchar2 default 'N',
p_lmt_prpnip_by_org_flag in varchar2 default 'N',
p_cbr_tmprl_evt_flag in varchar2 default 'N') is
--
l_retcode number;
p_comp_selection_rule_id =>p_comp_selection_rule_id,
p_person_selection_rule_id =>p_person_selection_rule_id,
p_ler_id =>p_ler_id,
p_organization_id =>p_organization_id,
p_benfts_grp_id =>p_benfts_grp_id,
p_location_id =>p_location_id,
p_pstl_zip_rng_id =>p_pstl_zip_rng_id,
p_rptg_grp_id =>p_rptg_grp_id,
p_pl_typ_id =>p_pl_typ_id,
p_opt_id =>p_opt_id,
p_eligy_prfl_id =>p_eligy_prfl_id,
p_vrbl_rt_prfl_id =>p_vrbl_rt_prfl_id,
p_legal_entity_id =>p_legal_entity_id,
p_payroll_id =>p_payroll_id,
p_commit_data =>p_commit_data,
p_audit_log_flag =>p_audit_log_flag,
p_lmt_prpnip_by_org_flag =>p_lmt_prpnip_by_org_flag,
p_cbr_tmprl_evt_flag =>p_cbr_tmprl_evt_flag );
p_comp_selection_rule_id in number default null,
p_person_selection_rule_id in number default null,
p_ler_id in number default null,
p_organization_id in number default null,
p_benfts_grp_id in number default null,
p_location_id in number default null,
p_pstl_zip_rng_id in number default null,
p_rptg_grp_id in number default null,
p_pl_typ_id in number default null,
p_opt_id in number default null,
p_eligy_prfl_id in number default null,
p_vrbl_rt_prfl_id in number default null,
p_legal_entity_id in number default null,
p_payroll_id in number default null,
p_commit_data in varchar2 default 'Y',
p_audit_log_flag in varchar2 default 'N',
p_lmt_prpnip_by_org_flag in varchar2 default 'N',
p_cbr_tmprl_evt_flag in varchar2 default 'N') is
--
l_retcode number;
p_comp_selection_rule_id =>p_comp_selection_rule_id,
p_person_selection_rule_id =>p_person_selection_rule_id,
p_ler_id =>p_ler_id,
p_organization_id =>p_organization_id,
p_benfts_grp_id =>p_benfts_grp_id,
p_location_id =>p_location_id,
p_pstl_zip_rng_id =>p_pstl_zip_rng_id,
p_rptg_grp_id =>p_rptg_grp_id,
p_pl_typ_id =>p_pl_typ_id,
p_opt_id =>p_opt_id,
p_eligy_prfl_id =>p_eligy_prfl_id,
p_vrbl_rt_prfl_id =>p_vrbl_rt_prfl_id,
p_legal_entity_id =>p_legal_entity_id,
p_payroll_id =>p_payroll_id,
p_commit_data =>p_commit_data,
p_audit_log_flag =>p_audit_log_flag,
p_lmt_prpnip_by_org_flag =>p_lmt_prpnip_by_org_flag,
p_cbr_tmprl_evt_flag =>p_cbr_tmprl_evt_flag );
,p_comp_selection_rule_id in number default null
,p_person_selection_rule_id in number default null
,p_ler_id in number default null
,p_organization_id in number default null
,p_benfts_grp_id in number default null
,p_location_id in number default null
,p_pstl_zip_rng_id in number default null
,p_rptg_grp_id in number default null
,p_pl_typ_id in number default null
,p_opt_id in number default null
,p_eligy_prfl_id in number default null
,p_vrbl_rt_prfl_id in number default null
,p_legal_entity_id in number default null
,p_payroll_id in number default null
,p_commit_data in varchar2 default 'Y'
,p_audit_log_flag in varchar2 default 'N'
,p_lmt_prpnip_by_org_flag in varchar2 default 'N'
,p_cbr_tmprl_evt_flag in varchar2 default 'N'
-- GRADE/STEP : Added for grade/step benmngle
,p_org_heirarchy_id in number default null
,p_org_starting_node_id in number default null
,p_grade_ladder_id in number default null
,p_asg_events_to_all_sel_dt in varchar2 default null
,p_rate_id in number default null -- pay scale
,p_per_sel_dt_cd in varchar2 default null -- business rule
,p_per_sel_dt_from in date default null -- business rule date from
,p_per_sel_dt_to in date default null -- business rule date to
,p_year_from in number default null -- business rule year from
,p_year_to in number default null -- business rule year to
,p_cagr_id in number default null -- Coll agreement id
,p_qual_type in number default null
,p_qual_status in varchar2 default null
-- 2940151
,p_per_sel_freq_cd in varchar2 default null
,p_concat_segs in varchar2 default null
-- end 2940151
,p_abs_historical_mode in varchar2 default 'N'
,p_gsp_eval_elig_flag in varchar2 default null -- GSP Rate Sync : Evaluate Eligibility
,p_lf_evt_oper_cd in varchar2 default null -- GSP Rate Sync : Life Event Operation code
,p_cwb_person_type in varchar2 default null
)
is
--
l_package varchar2(80);
select person_id
from ben_person_actions
where benefit_action_id = p_benefit_action_id
and action_status_cd = 'P';
select pl.trk_inelig_per_flag
from ben_pl_f pl
where pl.pl_id = p_pl_id
and l_effective_date between pl.effective_start_date and
pl.effective_end_date;
select ler.ler_id, pet.popl_enrt_typ_cycl_id
from ben_popl_enrt_typ_cycl_f pet,
ben_enrt_perd enp,
ben_ler_f ler
where enp.business_group_id = p_business_group_id
-- PB : 5422 :
and enp.asnd_lf_evt_dt = l_lf_evt_ocrd_dt
-- and enp.enrt_perd_id = p_popl_enrt_typ_cycl_id
and enp.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id
and pet.business_group_id = enp.business_group_id
and l_effective_date
between pet.effective_start_date
and pet.effective_end_date
-- CWB Changes
and ((ler.typ_cd = 'SCHEDD'||pet.enrt_typ_cycl_cd and p_mode = 'C') or
(ler.typ_cd = 'COMP' and p_mode = 'W')
)
-- CWB Changes end
and ler.business_group_id = pet.business_group_id
and l_effective_date
between ler.effective_start_date
and ler.effective_end_date
-- CWB Change
and ((p_mode = 'W' and ler.ler_id = enp.ler_id
and pet.pl_id = p_pl_id) or p_mode <> 'W');
select people_group_structure
from per_business_groups
where business_group_id = p_business_group_id;
p_person_selection_rule_id => p_person_selection_rule_id,
p_person_type_id => p_person_type_id,
p_pgm_id => p_pgm_id,
p_pl_id => p_pl_id,
p_ler_id => p_ler_id,
p_pl_typ_id => p_pl_typ_id,
p_opt_id => p_opt_id,
p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
-- Grade/Step progression
p_org_heirarchy_id => p_org_heirarchy_id,
p_org_starting_node_id => p_org_starting_node_id,
p_asg_events_to_all_sel_dt => l_asg_events_to_all_sel_dt,
p_per_sel_dt_cd => p_per_sel_dt_cd,
p_per_sel_dt_from => l_per_sel_dt_from,
p_per_sel_dt_to => l_per_sel_dt_to,
p_year_from => p_year_from,
p_year_to => p_year_to,
p_qual_type => p_qual_type,
p_qual_status => p_qual_status,
p_lf_evt_oper_cd => p_lf_evt_oper_cd /* GSP Rate Sync */
);
p_comp_selection_rl => p_comp_selection_rule_id,
p_person_selection_rl => p_person_selection_rule_id,
p_ler_id => p_ler_id,
p_organization_id => p_organization_id,
p_benfts_grp_id => p_benfts_grp_id,
p_location_id => p_location_id,
p_pstl_zip_rng_id => p_pstl_zip_rng_id,
p_rptg_grp_id => p_rptg_grp_id,
p_pl_typ_id => p_pl_typ_id,
p_opt_id => p_opt_id,
p_eligy_prfl_id => p_eligy_prfl_id,
p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id,
p_legal_entity_id => p_legal_entity_id,
p_payroll_id => p_payroll_id,
p_debug_messages_flag => p_cbr_tmprl_evt_flag,
p_audit_log_flag => p_audit_log_flag,
--
-- PB : Healthnet change : Limit comp object selection
-- based on the org id.
--
p_lmt_prpnip_by_org_flag => p_lmt_prpnip_by_org_flag,
--
-- GRADE/STEP : Reuse date_from to store l_asg_events_to_all_sel_dt
-- Later add this column to bft table use proper column
-- We may need to add other grade/step paramters to this table
-- for now just reuse.
--
p_date_from => l_asg_events_to_all_sel_dt,
p_request_id => fnd_global.conc_request_id,
p_program_application_id => fnd_global.prog_appl_id,
p_program_id => fnd_global.conc_program_id,
p_program_update_date => sysdate,
p_object_version_number => l_object_version_number,
p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt,
p_inelg_action_cd => p_abs_historical_mode,
p_effective_date => l_effective_date,
-- 2940151
p_org_hierarchy_id => p_org_heirarchy_id, -- note the spelling for hierarchy
p_org_starting_node_id => p_org_starting_node_id,
p_grade_ladder_id => p_grade_ladder_id,
p_asg_events_to_all_sel_dt => p_asg_events_to_all_sel_dt,
p_rate_id => p_rate_id,
p_per_sel_dt_cd => p_per_sel_dt_cd,
p_per_sel_dt_from => p_per_sel_dt_from,
p_per_sel_dt_to => p_per_sel_dt_to,
p_year_from => p_year_from,
p_year_to => p_year_to,
p_cagr_id => p_cagr_id,
p_qual_type => p_qual_type,
p_qual_status => p_qual_status,
p_per_sel_freq_cd => p_per_sel_freq_cd ,
p_concat_segs => p_concat_segs
-- end 2940151
);
p_person_selection_rule_id => p_person_selection_rule_id,
-- GRADE/STEP
p_org_heirarchy_id => p_org_heirarchy_id,
p_org_starting_node_id => p_org_starting_node_id,
p_grade_ladder_id => p_grade_ladder_id,
p_asg_events_to_all_sel_dt => l_asg_events_to_all_sel_dt,
p_rate_id => p_rate_id,
p_per_sel_dt_cd => p_per_sel_dt_cd,
p_per_sel_dt_from => l_per_sel_dt_from,
p_per_sel_dt_to => l_per_sel_dt_to,
p_year_from => p_year_from,
p_year_to => p_year_to,
p_cagr_id => p_cagr_id,
p_qual_type => p_qual_type,
p_qual_status => p_qual_status,
-- 2940151
p_per_sel_freq_cd =>p_per_sel_freq_cd,
p_id_flex_num =>l_id_flex_num,
p_concat_segs =>p_concat_segs,
-- end 2940151
-- End GRADE/STEP
p_effective_date => l_effective_date,
p_chunk_size => l_chunk_size,
p_threads => l_threads,
p_num_ranges => l_num_ranges,
p_num_persons => l_num_persons,
--
-- PB : Healthnet change : Limit comp object selection
-- based on the org id.
--
p_lmt_prpnip_by_org_flag => p_lmt_prpnip_by_org_flag,
p_commit_data => p_commit_data,
-- ABSENCES : p_mode added
p_mode => p_mode,
p_lf_evt_oper_cd => p_lf_evt_oper_cd /* GSP Rate Sync */
);
,p_person_selection_rule_id => p_person_selection_rule_id
--
-- PB : Healthnet Change
-- Pass the l_lf_evt_ocrd_dt for scheduled mode
-- Bug#2638681 - Healthnet change reversed
--,p_effective_date => nvl(l_lf_evt_ocrd_dt, l_effective_date)
,p_effective_date => l_effective_date
,p_lmt_prpnip_by_org_flag => p_lmt_prpnip_by_org_flag
,p_mode => p_mode
,p_chunk_size => l_chunk_size
,p_threads => l_threads
,p_num_ranges => l_num_ranges
,p_num_persons => l_num_persons
,p_commit_data => p_commit_data
,p_popl_enrt_typ_cycl_id => l_popl_enrt_typ_cycl_id
,p_cwb_person_type => p_cwb_person_type
,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt
);
,p_person_selection_rule_id => p_person_selection_rule_id
,p_comp_selection_rule_id => p_comp_selection_rule_id
,p_derivable_factors => p_derivable_factors
,p_cbr_tmprl_evt_flag => p_cbr_tmprl_evt_flag
,p_lmt_prpnip_by_org_flag => p_lmt_prpnip_by_org_flag
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
,p_gsp_eval_elig_flag => l_gsp_eval_elig_flag /* GSP Rate Sync */
,p_lf_evt_oper_cd => p_lf_evt_oper_cd /* GSP Rate Sync */
);
p_person_selection_rule_id => p_person_selection_rule_id,
p_comp_selection_rule_id => p_comp_selection_rule_id,
p_derivable_factors => p_derivable_factors,
p_thread_id => l_threads+1,
p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
p_lmt_prpnip_by_org_flag => p_lmt_prpnip_by_org_flag,
p_cbr_tmprl_evt_flag => p_cbr_tmprl_evt_flag,
p_gsp_eval_elig_flag => l_gsp_eval_elig_flag, /* GSP Rate Sync */
p_lf_evt_oper_cd => p_lf_evt_oper_cd /* GSP Rate Sync */
);
,p_comp_selection_rule_id in number default null
,p_person_selection_rule_id in number default null
,p_ler_id in number default null
,p_organization_id in number default null
,p_benfts_grp_id in number default null
,p_location_id in number default null
,p_pstl_zip_rng_id in number default null
,p_rptg_grp_id in number default null
,p_pl_typ_id in number default null
,p_opt_id in number default null
,p_eligy_prfl_id in number default null
,p_vrbl_rt_prfl_id in number default null
,p_legal_entity_id in number default null
,p_payroll_id in number default null
,p_commit_data in varchar2 default 'Y'
,p_audit_log_flag in varchar2 default 'N'
,p_lmt_prpnip_by_org_flag in varchar2 default 'N'
,p_cbr_tmprl_evt_flag in varchar2 default 'N'
-- GRADE/STEP : Added for grade/step benmngle
,p_org_heirarchy_id in number default null
,p_org_starting_node_id in number default null
,p_grade_ladder_id in number default null
,p_asg_events_to_all_sel_dt in varchar2 default null
,p_rate_id in number default null -- pay scale
,p_per_sel_dt_cd in varchar2 default null -- business rule
,p_per_sel_dt_from in date default null -- business rule date from
,p_per_sel_dt_to in date default null -- business rule date to
,p_year_from in number default null -- business rule year from
,p_year_to in number default null -- business rule year to
,p_cagr_id in number default null -- Coll agreement id
,p_qual_type in number default null
,p_qual_status in varchar2 default null
-- 2940151
,p_per_sel_freq_cd in varchar2 default null
,p_concat_segs in varchar2 default null
-- end 2940151
,p_abs_historical_mode in varchar2 default 'N'
,p_gsp_eval_elig_flag in varchar2 default null -- GSP Rate Sync : Evaluate Eligibility
,p_lf_evt_oper_cd in varchar2 default null -- GSP Rate Sync : Life Event Operation code
,p_cwb_person_type in varchar2 default null)
is
--
l_package varchar2(80);
,p_comp_selection_rule_id => p_comp_selection_rule_id
,p_person_selection_rule_id => p_person_selection_rule_id
,p_ler_id => p_ler_id
,p_organization_id => p_organization_id
,p_benfts_grp_id => p_benfts_grp_id
,p_location_id => p_location_id
,p_pstl_zip_rng_id => p_pstl_zip_rng_id
,p_rptg_grp_id => p_rptg_grp_id
,p_pl_typ_id => p_pl_typ_id
,p_opt_id => p_opt_id
,p_eligy_prfl_id => p_eligy_prfl_id
,p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
,p_legal_entity_id => p_legal_entity_id
,p_payroll_id => p_payroll_id
,p_commit_data => p_commit_data
,p_audit_log_flag => p_audit_log_flag
,p_lmt_prpnip_by_org_flag => p_lmt_prpnip_by_org_flag
,p_cbr_tmprl_evt_flag => p_cbr_tmprl_evt_flag
-- GRADE/STEP : Added for grade/step benmngle
,p_org_heirarchy_id => p_org_heirarchy_id
,p_org_starting_node_id => p_org_starting_node_id
,p_grade_ladder_id => p_grade_ladder_id
,p_asg_events_to_all_sel_dt => p_asg_events_to_all_sel_dt
,p_rate_id => p_rate_id
,p_per_sel_dt_cd => p_per_sel_dt_cd
,p_per_sel_dt_from => p_per_sel_dt_from
,p_per_sel_dt_to => p_per_sel_dt_to
,p_year_from => p_year_from
,p_year_to => p_year_to
,p_cagr_id => p_cagr_id
,p_qual_type => p_qual_type
,p_qual_status => p_qual_status
,p_abs_historical_mode => p_abs_historical_mode
-- 2940151
,p_per_sel_freq_cd => p_per_sel_freq_cd
,p_concat_segs => p_concat_segs
-- end 2940151
,p_gsp_eval_elig_flag => p_gsp_eval_elig_flag -- GSP Rate Sync : Evaluate Eligibility
,p_lf_evt_oper_cd => p_lf_evt_oper_cd -- GSP Rate Sync : Life Event Operation code
,p_cwb_person_type => p_cwb_person_type);
select pen.pl_id,
pen.oipl_id,
pen.pgm_id,
pen.ptip_id,
pen.enrt_cvg_strt_dt,
pen.enrt_cvg_thru_dt
from ben_prtt_enrt_rslt_f pen
where pen.person_id = p_person_id
and pen.business_group_id = p_business_group_id
and p_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';
g_cache_person_prtn.delete;
select ptn.ptnl_ler_for_per_id
from ben_ptnl_ler_for_per ptn
where ptn.ler_id = cv_ler_id
and ptn.person_id = cv_person_id
and ptn.business_group_id = cv_business_group_id
and ptn.lf_evt_ocrd_dt = cv_lf_evt_ocrd_dt
and ptn.ptnl_ler_for_per_stat_cd in ('DTCTD','UNPROCD', 'PROCD');
,p_comp_selection_rule_id => l_param_rec.comp_selection_rl
,p_effective_date => l_rec.lf_evt_ocrd_dt
,p_pgm_id => l_param_rec.pgm_id
,p_business_group_id => l_param_rec.business_group_id
,p_pl_id => l_param_rec.pl_id
-- PB : 5422 :
-- ,p_popl_enrt_typ_cycl_id => l_param_rec.popl_enrt_typ_cycl_id
,p_asnd_lf_evt_dt => l_asnd_lf_evt_dt
,p_no_programs => l_param_rec.no_programs_flag
,p_no_plans => l_param_rec.no_plans_flag
,p_rptg_grp_id => l_param_rec.rptg_grp_id
,p_pl_typ_id => l_param_rec.pl_typ_id
,p_opt_id => l_param_rec.opt_id
,p_eligy_prfl_id => l_param_rec.eligy_prfl_id
,p_vrbl_rt_prfl_id => l_param_rec.vrbl_rt_prfl_id
,p_thread_id => benutils.g_thread_id
-- PB : 5422 :
,p_mode => nvl(ben_manage_life_events.g_modified_mode,l_param_rec.mode_cd)
-- ,p_mode => l_param_rec.mode_cd
,p_lmt_prpnip_by_org_flag => l_param_rec.lmt_prpnip_by_org_flag
,p_person_id => p_person_id
);
ben_manage_unres_life_events.delete_elctbl_choice
(p_person_id => p_person_id
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
,p_rec => l_rec
);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per
(p_validate => false
,p_object_version_number => l_object_version_number
,p_ptnl_ler_for_per_id => l_rec.ptnl_ler_for_per_id
,p_lf_evt_ocrd_dt => p_effective_date
,p_ptnl_ler_for_per_stat_cd => 'PROCD'
,p_ler_id => p_ler_id
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,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 => sysdate
,p_ntfn_dt => trunc(sysdate)
,p_procd_dt => p_effective_date);
p_program_update_date => sysdate
,p_procd_dt => l_procd_dt
,p_strtd_dt => l_strtd_dt
,p_voidd_dt => l_voidd_dt
);
ben_person_life_event_api.update_person_life_event
(p_per_in_ler_id => l_rec.per_in_ler_id,
p_per_in_ler_stat_cd => 'STRTD',
p_object_version_number => l_rec.object_version_number,
p_lf_evt_ocrd_dt => p_effective_date,
p_effective_date => p_effective_date,
p_procd_dt => l_procd_dt,
p_strtd_dt => l_strtd_dt,
p_voidd_dt => l_voidd_dt);
,p_comp_selection_rule_id => l_param_rec.comp_selection_rl
,p_effective_date => p_effective_date
,p_pgm_id => l_param_rec.pgm_id
,p_business_group_id => l_param_rec.business_group_id
,p_pl_id => l_param_rec.pl_id
,p_asnd_lf_evt_dt => l_asnd_lf_evt_dt
,p_no_programs => l_param_rec.no_programs_flag
,p_no_plans => l_param_rec.no_plans_flag
,p_rptg_grp_id => l_param_rec.rptg_grp_id
,p_pl_typ_id => l_param_rec.pl_typ_id
,p_opt_id => l_param_rec.opt_id
,p_eligy_prfl_id => l_param_rec.eligy_prfl_id
,p_vrbl_rt_prfl_id => l_param_rec.vrbl_rt_prfl_id
,p_thread_id => benutils.g_thread_id
,p_mode => 'U'
,p_lmt_prpnip_by_org_flag => l_param_rec.lmt_prpnip_by_org_flag
,p_person_id => p_person_id
);
,p_comp_selection_rule_id => l_param_rec.comp_selection_rl
,p_effective_date => p_effective_date
,p_pgm_id => l_param_rec.pgm_id
,p_business_group_id => l_param_rec.business_group_id
,p_pl_id => l_param_rec.pl_id
-- PB : 5422 :
-- ,p_popl_enrt_typ_cycl_id => l_param_rec.popl_enrt_typ_cycl_id
,p_asnd_lf_evt_dt => null
,p_no_programs => l_param_rec.no_programs_flag
,p_no_plans => l_param_rec.no_plans_flag
,p_rptg_grp_id => l_param_rec.rptg_grp_id
,p_pl_typ_id => l_param_rec.pl_typ_id
,p_opt_id => l_param_rec.opt_id
,p_eligy_prfl_id => l_param_rec.eligy_prfl_id
,p_vrbl_rt_prfl_id => l_param_rec.vrbl_rt_prfl_id
,p_thread_id => benutils.g_thread_id
-- PB : 5422 :
,p_mode => p_mode
-- ,p_mode => l_param_rec.mode_cd
,p_lmt_prpnip_by_org_flag => l_param_rec.lmt_prpnip_by_org_flag
,p_person_id => p_person_id
);
select strt_dt
from ben_enrt_perd
where enrt_perd_id = p_popl_enrt_typ_cycl_id;
select pdp.dpnt_person_id, min(egd.elig_thru_dt) cvg_thru_dt--min(pdp.cvg_thru_dt) cvg_thru_dt
from ben_elig_cvrd_dpnt_f pdp
,ben_elig_dpnt egd
,ben_prtt_enrt_rslt_f pen
where pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen.person_id = p_person_id
and pen.effective_end_date = hr_api.g_eot
and pen.business_group_id = p_business_group_id
and nvl(pdp.cvg_thru_dt,hr_api.g_eot) <> hr_api.g_eot
and pdp.business_group_id = pen.business_group_id
and pdp.elig_cvrd_dpnt_id = egd.elig_cvrd_dpnt_id
and egd.dpnt_inelig_flag = 'Y'
and egd.per_in_ler_id = p_per_in_ler_id
and egd.business_group_id = pdp.business_group_id
group by pdp.dpnt_person_id;
select popl.pil_elctbl_chc_popl_id,
popl.object_version_number
from ben_pil_elctbl_chc_popl popl
where per_in_ler_id = p_per_in_ler_id
and not exists
(select null
from ben_elig_per_elctbl_chc epe
where epe.pil_elctbl_chc_popl_id = popl.pil_elctbl_chc_popl_id
and elctbl_flag = 'Y');
select 'Y'
from ben_pil_elctbl_chc_popl popl
where per_in_ler_id = p_per_in_ler_id
and (popl.pgm_id = p_pgm_id or
popl.pl_id = p_pl_id);
select a.ptnl_ler_for_per_id ptnl_ler_for_per_id,
a.object_version_number object_version_number
from ben_ptnl_ler_for_per a,
ben_per_in_ler b
where b.per_in_ler_id = l_rec.per_in_ler_id
and b.ptnl_ler_for_per_id = a.ptnl_ler_for_per_id;
select ptip.ptip_id,
pln.pl_id
from ben_ptip_f ptip,
ben_pl_f pln,
ben_plip_f plip
where plip.plip_id = p_gsp_plip_id
and plip.pgm_id = p_gsp_pgm_id
and p_effective_date between plip.effective_start_date
and plip.effective_end_date
and pln.pl_id = plip.pl_id
and plip.effective_start_date between pln.effective_start_date
and pln.effective_end_date
and ptip.pl_typ_id = pln.pl_typ_id
and ptip.pgm_id = p_gsp_pgm_id
and p_effective_date between ptip.effective_start_date
and ptip.effective_end_date;
select pen.*
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.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
and pen.business_group_id = p_business_group_id
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and pen.per_in_ler_id = p_per_in_ler_id
;
select count(*)
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.ptip_id = p_ptip_id
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and ( pen.enrt_cvg_thru_dt = hr_api.g_eot
or ( pen.enrt_cvg_thru_dt >= p_effective_date
and nvl(pen.enrt_ovridn_flag,'N') = 'Y'
)
)
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
;
select ptip.ptip_id,
ptip.pgm_id,
ptip.pl_typ_id,
ptip.Mx_ENRD_ALWD_OVRID_NUM,
ptip.no_mx_pl_typ_ovrid_flag,
ptip.MN_ENRD_RQD_OVRID_NUM,
ptip.no_mn_pl_typ_overid_flag,
plt.name,
Plt.MN_ENRL_RQD_NUM,
plt.MX_ENRL_ALWD_NUM
from ben_ptip_f ptip
, ben_pl_typ_f plt
where ptip.ptip_id = p_ptip_id
and ptip.pl_typ_id = plt.pl_typ_id
and ptip.business_group_id = p_business_group_id
and p_effective_date between
ptip.effective_start_date and ptip.effective_end_date
and p_effective_date between
plt.effective_start_date and plt.effective_end_date
;
select pep.elig_flag
from ben_elig_per_f pep
where pep.per_in_ler_id = p_per_in_ler_id
and pep.ptip_id = p_ptip_id
and pep.business_group_id = p_business_group_id
and p_effective_date between pep.effective_start_date
and pep.effective_end_date
;
select 1 ordr_num ,
leer.lee_rsn_id,
NVL(leer.defer_deenrol_flag,'N')
FROM ben_lee_rsn_f leer, ben_popl_enrt_typ_cycl_f petc
WHERE leer.ler_id = p_ler_id
AND leer.business_group_id = p_business_group_id
AND c_effective_date BETWEEN leer.effective_start_date
AND leer.effective_end_date
AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
AND petc.pl_id = c_pl_id
AND petc.enrt_typ_cycl_cd = 'L' -- life event
AND petc.business_group_id = p_business_group_id
AND c_effective_date BETWEEN petc.effective_start_date
AND petc.effective_end_date
UNION
select 2 ordr_num ,
leer.lee_rsn_id,
NVL(leer.defer_deenrol_flag,'N')
FROM ben_lee_rsn_f leer, ben_popl_enrt_typ_cycl_f petc
WHERE leer.ler_id = p_ler_id
AND leer.business_group_id = p_business_group_id
AND c_effective_date BETWEEN leer.effective_start_date
AND leer.effective_end_date
AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
AND petc.pgm_id = c_pgm_id
AND petc.enrt_typ_cycl_cd = 'L' -- life event
AND petc.business_group_id = p_business_group_id
AND c_effective_date BETWEEN petc.effective_start_date
AND petc.effective_end_date
order by 1 ;
select 1 ordr_num ,
enrtp.enrt_perd_id,
NVL(enrtp.defer_deenrol_flag,'N')
FROM ben_popl_enrt_typ_cycl_f petc,
ben_enrt_perd enrtp,
ben_ler_f ler
WHERE petc.pl_id = c_pl_id
AND petc.business_group_id = p_business_group_id
AND c_effective_date BETWEEN petc.effective_start_date
AND petc.effective_end_date
AND petc.enrt_typ_cycl_cd <> 'L'
AND enrtp.business_group_id = p_business_group_id
AND enrtp.asnd_lf_evt_dt = c_effective_date
AND enrtp.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
-- comp work bench changes
and ler.ler_id (+) = enrtp.ler_id
and ler.ler_id (+) = p_ler_id
and c_effective_date between ler.effective_start_date (+)
and ler.effective_end_date (+)
UNION
select 2 ordr_num ,
enrtp.enrt_perd_id,
NVL(enrtp.defer_deenrol_flag,'N')
FROM ben_popl_enrt_typ_cycl_f petc,
ben_enrt_perd enrtp,
ben_ler_f ler
WHERE petc.pgm_id = c_pgm_id
AND petc.business_group_id = p_business_group_id
AND c_effective_date BETWEEN petc.effective_start_date
AND petc.effective_end_date
AND petc.enrt_typ_cycl_cd <> 'L'
AND enrtp.business_group_id = p_business_group_id
AND enrtp.asnd_lf_evt_dt = c_effective_date
AND enrtp.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
-- comp work bench changes
and ler.ler_id (+) = enrtp.ler_id
and ler.ler_id (+) = p_ler_id
and c_effective_date between ler.effective_start_date (+)
and ler.effective_end_date (+)
order by 1 ;
select opp.oiplip_id, opt_id
from ben_oiplip_f opp, ben_oipl_f cop
where opp.oipl_id = cv_oipl_id
and opp.plip_id = cv_plip_id
and p_effective_date between opp.effective_start_date and opp.effective_end_date
and cop.oipl_id = opp.oipl_id
and p_effective_date between cop.effective_start_date and cop.effective_end_date;
select pel.*
from ben_pil_elctbl_chc_popl pel
where pel.pgm_id = p_pgm_id
and pel.pl_id is null
and pel.pil_elctbl_popl_stat_cd not in ('VOIDD','BCKDT')
and pel.per_in_ler_id = p_per_in_ler_id;
select pel.*
from ben_pil_elctbl_chc_popl pel
where pel.pl_id = p_pl_id
and pel.pgm_id is null
and pel.pil_elctbl_popl_stat_cd not in ('VOIDD','BCKDT')
and pel.per_in_ler_id = p_per_in_ler_id;
l_defer_deenrl_tbl.delete;
ben_person_object.g_cache_pil_rec.delete;
pqh_gsp_post_process.update_rate_sync_salary
(p_per_in_ler_id => l_pil_row.per_in_ler_id
,p_effective_date => p_effective_date
);
ben_determine_dpnt_eligibility.g_egd_table.delete;
update_elig_per_rows
(p_comp_obj_tree_row => l_comp_obj_tree_row
,p_comp_rec => l_comp_rec
,p_person_id => p_person_id
,p_treeele_num => l_treeele_num
,p_par_elig_state => l_par_elig_state
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
,p_lf_evt_ocrd_dt => l_rec.lf_evt_ocrd_dt
,p_per_in_ler_id => l_pil_row.per_in_ler_id
,p_continue_loop => l_continue_loop);
ben_determine_dpnt_eligibility.g_egd_table.delete;
hr_utility.set_location ('DEFER UPDATED ' || l_defer_popl_id,9653);
ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl
(p_validate => FALSE,
p_pil_elctbl_chc_popl_id => l_defer_popl_id,
p_object_version_number => l_defer_popl_ovn,
p_effective_date => p_effective_date,
p_defer_deenrol_flag => 'Y'
);
update_cwb_epe
(p_per_in_ler_id => l_rec.per_in_ler_id
,p_effective_date => p_effective_date);
ben_enrolment_requirements.update_defaults
(p_run_mode => l_mode
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
,p_lf_evt_ocrd_dt => l_rec.lf_evt_ocrd_dt
,p_ler_id => p_ler_id
,p_person_id => p_person_id
,p_per_in_ler_id => l_pil_row.per_in_ler_id
);
BEN_DETERMINE_CHC_CTFN.update_susp_if_ctfn_flag
(p_effective_date => p_effective_date,
p_lf_evt_ocrd_dt => l_rec.lf_evt_ocrd_dt,
p_person_id => p_person_id,
p_per_in_ler_id => l_pil_row.per_in_ler_id
);
fnd_message.set_token('PROC','delete_in_pndg_elig_dpnt');
delete_in_pndg_elig_dpnt( l_rec.per_in_ler_id,p_effective_date ) ;
pqh_gsp_post_process.update_rate_sync_salary
(p_per_in_ler_id => l_pil_row.per_in_ler_id
,p_effective_date => p_effective_date
);
update_enrt_rt (p_per_in_ler_id => l_rec.per_in_ler_id);
ben_cobra_requirements.update_cobra_elig_info
(p_person_id => p_person_id
,p_per_in_ler_id => l_rec.per_in_ler_id
,p_lf_evt_ocrd_dt => l_rec.lf_evt_ocrd_dt
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
);
ben_person_life_event_api.update_person_life_event
(p_per_in_ler_id => l_rec.per_in_ler_id,
p_per_in_ler_stat_cd => 'PROCD',
p_object_version_number => l_rec.object_version_number,
-- p_effective_date => p_effective_date,
-- Bug#1543462
p_effective_date => least(p_effective_date,trunc(sysdate)),
p_procd_dt => l_procd_dt,
p_strtd_dt => l_strtd_dt,
p_voidd_dt => l_voidd_dt);
benutils.update_life_event_cache(p_open_and_closed => 'Y');
ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl
(p_pil_elctbl_chc_popl_id => l_pil_elctbl_chc_popl.pil_elctbl_chc_popl_id
,p_pil_elctbl_popl_stat_cd => 'PROCD'
,p_business_group_id => p_business_group_id
,p_object_version_number => l_pil_elctbl_chc_popl.object_version_number
,p_effective_date => p_effective_date);
ben_prtt_enrt_result_api.update_person_type_usages
(p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
);
ben_irc_util.post_irec_process_update
(p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_assignment_id => G_IREC_OLD_ASS_REC.assignment_id,
p_effective_date => p_effective_date);
update per_all_assignments_F
set
BUSINESS_GROUP_ID = G_IREC_OLD_ASS_REC.BUSINESS_GROUP_ID
,RECRUITER_ID = G_IREC_OLD_ASS_REC.RECRUITER_ID
,GRADE_ID = G_IREC_OLD_ASS_REC.GRADE_ID
,POSITION_ID = G_IREC_OLD_ASS_REC.POSITION_ID
,JOB_ID = G_IREC_OLD_ASS_REC.JOB_ID
,ASSIGNMENT_STATUS_TYPE_ID = G_IREC_OLD_ASS_REC.ASSIGNMENT_STATUS_TYPE_ID
,PAYROLL_ID = G_IREC_OLD_ASS_REC.PAYROLL_ID
,LOCATION_ID = G_IREC_OLD_ASS_REC.LOCATION_ID
,PERSON_REFERRED_BY_ID = G_IREC_OLD_ASS_REC.PERSON_REFERRED_BY_ID
,SUPERVISOR_ID = G_IREC_OLD_ASS_REC.SUPERVISOR_ID
,SPECIAL_CEILING_STEP_ID = G_IREC_OLD_ASS_REC.SPECIAL_CEILING_STEP_ID
,PERSON_ID = G_IREC_OLD_ASS_REC.PERSON_ID
,RECRUITMENT_ACTIVITY_ID = G_IREC_OLD_ASS_REC.RECRUITMENT_ACTIVITY_ID
,SOURCE_ORGANIZATION_ID = G_IREC_OLD_ASS_REC.SOURCE_ORGANIZATION_ID
,ORGANIZATION_ID = G_IREC_OLD_ASS_REC.ORGANIZATION_ID
,PEOPLE_GROUP_ID = G_IREC_OLD_ASS_REC.PEOPLE_GROUP_ID
,SOFT_CODING_KEYFLEX_ID = G_IREC_OLD_ASS_REC.SOFT_CODING_KEYFLEX_ID
,VACANCY_ID = G_IREC_OLD_ASS_REC.VACANCY_ID
,PAY_BASIS_ID = G_IREC_OLD_ASS_REC.PAY_BASIS_ID
,ASSIGNMENT_SEQUENCE = G_IREC_OLD_ASS_REC.ASSIGNMENT_SEQUENCE
,APPLICATION_ID = G_IREC_OLD_ASS_REC.APPLICATION_ID
,ASSIGNMENT_NUMBER = G_IREC_OLD_ASS_REC.ASSIGNMENT_NUMBER
,CHANGE_REASON = G_IREC_OLD_ASS_REC.CHANGE_REASON
,COMMENT_ID = G_IREC_OLD_ASS_REC.COMMENT_ID
,DATE_PROBATION_END = G_IREC_OLD_ASS_REC.DATE_PROBATION_END
,DEFAULT_CODE_COMB_ID = G_IREC_OLD_ASS_REC.DEFAULT_CODE_COMB_ID
,EMPLOYMENT_CATEGORY = G_IREC_OLD_ASS_REC.EMPLOYMENT_CATEGORY
,FREQUENCY = G_IREC_OLD_ASS_REC.FREQUENCY
,INTERNAL_ADDRESS_LINE = G_IREC_OLD_ASS_REC.INTERNAL_ADDRESS_LINE
,MANAGER_FLAG = G_IREC_OLD_ASS_REC.MANAGER_FLAG
,NORMAL_HOURS = G_IREC_OLD_ASS_REC.NORMAL_HOURS
,PERF_REVIEW_PERIOD = G_IREC_OLD_ASS_REC.PERF_REVIEW_PERIOD
,PERF_REVIEW_PERIOD_FREQUENCY = G_IREC_OLD_ASS_REC.PERF_REVIEW_PERIOD_FREQUENCY
,PERIOD_OF_SERVICE_ID = G_IREC_OLD_ASS_REC.PERIOD_OF_SERVICE_ID
,PROBATION_PERIOD = G_IREC_OLD_ASS_REC.PROBATION_PERIOD
,PROBATION_UNIT = G_IREC_OLD_ASS_REC.PROBATION_UNIT
,SAL_REVIEW_PERIOD = G_IREC_OLD_ASS_REC.SAL_REVIEW_PERIOD
,SAL_REVIEW_PERIOD_FREQUENCY = G_IREC_OLD_ASS_REC.SAL_REVIEW_PERIOD_FREQUENCY
,SET_OF_BOOKS_ID = G_IREC_OLD_ASS_REC.SET_OF_BOOKS_ID
,SOURCE_TYPE = G_IREC_OLD_ASS_REC.SOURCE_TYPE
,TIME_NORMAL_FINISH = G_IREC_OLD_ASS_REC.TIME_NORMAL_FINISH
,TIME_NORMAL_START = G_IREC_OLD_ASS_REC.TIME_NORMAL_START
,REQUEST_ID = G_IREC_OLD_ASS_REC.REQUEST_ID
,PROGRAM_APPLICATION_ID = G_IREC_OLD_ASS_REC.PROGRAM_APPLICATION_ID
,PROGRAM_ID = G_IREC_OLD_ASS_REC.PROGRAM_ID
,PROGRAM_UPDATE_DATE = G_IREC_OLD_ASS_REC.PROGRAM_UPDATE_DATE
,ASS_ATTRIBUTE_CATEGORY = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE_CATEGORY
,ASS_ATTRIBUTE1 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE1
,ASS_ATTRIBUTE2 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE2
,ASS_ATTRIBUTE3 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE3
,ASS_ATTRIBUTE4 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE4
,ASS_ATTRIBUTE5 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE5
,ASS_ATTRIBUTE6 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE6
,ASS_ATTRIBUTE7 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE7
,ASS_ATTRIBUTE8 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE8
,ASS_ATTRIBUTE9 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE9
,ASS_ATTRIBUTE10 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE10
,ASS_ATTRIBUTE11 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE11
,ASS_ATTRIBUTE12 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE12
,ASS_ATTRIBUTE13 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE13
,ASS_ATTRIBUTE14 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE14
,ASS_ATTRIBUTE15 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE15
,ASS_ATTRIBUTE16 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE16
,ASS_ATTRIBUTE17 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE17
,ASS_ATTRIBUTE18 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE18
,ASS_ATTRIBUTE19 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE19
,ASS_ATTRIBUTE20 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE20
,ASS_ATTRIBUTE21 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE21
,ASS_ATTRIBUTE22 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE22
,ASS_ATTRIBUTE23 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE23
,ASS_ATTRIBUTE24 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE24
,ASS_ATTRIBUTE25 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE25
,ASS_ATTRIBUTE26 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE26
,ASS_ATTRIBUTE27 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE27
,ASS_ATTRIBUTE28 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE28
,ASS_ATTRIBUTE29 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE29
,ASS_ATTRIBUTE30 = G_IREC_OLD_ASS_REC.ASS_ATTRIBUTE30
,TITLE = G_IREC_OLD_ASS_REC.TITLE
,OBJECT_VERSION_NUMBER = G_IREC_OLD_ASS_REC.OBJECT_VERSION_NUMBER
,BARGAINING_UNIT_CODE = G_IREC_OLD_ASS_REC.BARGAINING_UNIT_CODE
,LABOUR_UNION_MEMBER_FLAG = G_IREC_OLD_ASS_REC.LABOUR_UNION_MEMBER_FLAG
,HOURLY_SALARIED_CODE = G_IREC_OLD_ASS_REC.HOURLY_SALARIED_CODE
,CONTRACT_ID = G_IREC_OLD_ASS_REC.CONTRACT_ID
,COLLECTIVE_AGREEMENT_ID = G_IREC_OLD_ASS_REC.COLLECTIVE_AGREEMENT_ID
,CAGR_ID_FLEX_NUM = G_IREC_OLD_ASS_REC.CAGR_ID_FLEX_NUM
,CAGR_GRADE_DEF_ID = G_IREC_OLD_ASS_REC.CAGR_GRADE_DEF_ID
,ESTABLISHMENT_ID = G_IREC_OLD_ASS_REC.ESTABLISHMENT_ID
,NOTICE_PERIOD = G_IREC_OLD_ASS_REC.NOTICE_PERIOD
,NOTICE_PERIOD_UOM = G_IREC_OLD_ASS_REC.NOTICE_PERIOD_UOM
,EMPLOYEE_CATEGORY = G_IREC_OLD_ASS_REC.EMPLOYEE_CATEGORY
,WORK_AT_HOME = G_IREC_OLD_ASS_REC.WORK_AT_HOME
,JOB_POST_SOURCE_NAME = G_IREC_OLD_ASS_REC.JOB_POST_SOURCE_NAME
,POSTING_CONTENT_ID = G_IREC_OLD_ASS_REC.POSTING_CONTENT_ID
,PERIOD_OF_PLACEMENT_DATE_START = G_IREC_OLD_ASS_REC.PERIOD_OF_PLACEMENT_DATE_START
,VENDOR_ID = G_IREC_OLD_ASS_REC.VENDOR_ID
,VENDOR_EMPLOYEE_NUMBER = G_IREC_OLD_ASS_REC.VENDOR_EMPLOYEE_NUMBER
,VENDOR_ASSIGNMENT_NUMBER = G_IREC_OLD_ASS_REC.VENDOR_ASSIGNMENT_NUMBER
,ASSIGNMENT_CATEGORY = G_IREC_OLD_ASS_REC.ASSIGNMENT_CATEGORY
,PROJECT_TITLE = G_IREC_OLD_ASS_REC.PROJECT_TITLE
,APPLICANT_RANK = G_IREC_OLD_ASS_REC.APPLICANT_RANK
,GRADE_LADDER_PGM_ID = G_IREC_OLD_ASS_REC.GRADE_LADDER_PGM_ID
,SUPERVISOR_ASSIGNMENT_ID = G_IREC_OLD_ASS_REC.SUPERVISOR_ASSIGNMENT_ID
,VENDOR_SITE_ID = G_IREC_OLD_ASS_REC.VENDOR_SITE_ID
,PO_HEADER_ID = G_IREC_OLD_ASS_REC.PO_HEADER_ID
,PO_LINE_ID = G_IREC_OLD_ASS_REC.PO_LINE_ID
,PROJECTED_ASSIGNMENT_END = G_IREC_OLD_ASS_REC.PROJECTED_ASSIGNMENT_END
where assignment_id = G_IREC_OLD_ASS_REC.assignment_id ;
p_person_selection_rule_id in number default null,
p_comp_selection_rule_id in number default null,
-- PB : 5422 :
-- p_popl_enrt_typ_cycl_id in number default null,
p_derivable_factors in varchar2 default 'ASC',
p_cbr_tmprl_evt_flag in varchar2 default 'N',
p_person_count in out nocopy number,
p_error_person_count in out nocopy number,
p_lf_evt_ocrd_dt in date,
p_effective_date in date,
p_validate in varchar2 default 'N', /* Bug 5550359 */
p_gsp_eval_elig_flag in varchar2 default null, /* GSP Rate Sync */
p_lf_evt_oper_cd in varchar2 default null /* GSP Rate Sync */
) is
--
l_package varchar2(80);
select count(*)
from ben_person_actions pac
where pac.benefit_action_id = p_benefit_action_id
and pac.action_status_cd = nvl(p_status_cd,pac.action_status_cd);