[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
31-Aug-99 jcarpent 115.68 - Update to look at ptip enrollment cols.
Use new ler_chg_pgm_enrt_f table.
Added dflt_enrt_cd/rl hierarchy.
07-Sep-99 tguy 115.69 fixed call to pay_mag_util
09-Sep-99 jcarpent 115.70 - Turn off auto flag for plan choices
created due to auto oipl.
09-Sep-99 stee 115.71 - Add new cvg_incr_r_decr_only codes
MNAVLO and MXAVLO.
21-Sep-99 jcarpent 115.72 - Added dflt_enrt_cds nncsedr, ndcsedr
22-Sep-99 jcarpent 115.73 - Changed calls to auto_enrt_(mthd)_rl
Combined calls for ler_chg and non-ler
chg to call determine_enrolment once.
23-Sep-99 jcarpent 115.74 - Renamed l_enrt_rl to l_ler_enrt_rl.
24-Sep-99 jcarpent 115.75 - Changed auto_enrt_cd/flag processing.
Consolidated *electable_flag variables
and parameters to be more consistent.
25-Sep-99 stee 115.76 - Added pgm_typ_cd to elctbl_chc_api.
01-Oct-99 tguy 115.77 - Fixed level jumping issues
01-Oct-99 jcarpent 115.78 - Added oipl.enrt_cd/rl
03-Oct-99 tguy 115.79 - New fixes to level jumping.
06-Oct-99 tguy 115.80 - added new checks for level jumping.
14-Oct-99 jcarpent 115.81 - Allow enrt_cd to control elctbl_flag
for automatic enrollments.
19-Oct-99 pbodla 115.82 - At Level : Period for program found
in call to ben_determine_date.main
l_pgme_enrt_perd_end_dt_rl is modified
to l_pgme_enrt_perd_strt_dt_rl
26-Oct-99 maagrawa 115.83 - Level jumping fixed for persons, who
are not previously enrolled. i.e. when
enrd_ordr_num is null.
02-Nov-99 maagrawa 115.84 - Level jumping restrictions removed at
plip level.
12-Nov-99 mhoyes 115.85 - Fixed bug 3630. Invalid number problem
in cursor c_ler_chg_enrt_info.
16-Nov-99 jcarpent 115.86 - Changed c_ler_chg_enrt_info again
problem was with decode for dflt_enrt_rl
- Also Added determine_dflt_enrt_cd
- Also added new global
- Fixed auto enrt rule execution.
was never running auto enrt rule.
03-Dec-99 lmcdonal 115.87 stl_elig...flag was being passed
incorrectly to determine_enrollment.
08-Dec-99 jcarpent 115.88 - Change hierarchy for enrt_mthd_cd/rl.
- Fix oipl enrt_cd bug. Rl overwrote cd.
01-Jan-00 pbodla 115.89 - Automatic enrollment rule is executed,
even if the enrt_mthd_cd is null.
05-Jan-00 jcarpent 115.90 - Added procs find_rqd_perd_enrt and
find_enrt_at_same_level
13-Jan-00 shdas 115.91 - added code so that choices for flx and
imptd inc plans are created irrespective
of whether they are configured to be
electable for each life event or not.
21-Jan-00 pbodla 115.92 - p_elig-per_id is added to
execute_enrt_rule, determine_enrolment
and passes to benutils.formula
05-Jan-00 maagrawa 115.93 - Get the auto enrollment flag from oipl's
ler record, if availabe (Bug 4290).
- If the choice is automatic, it cannot
be default. (Bug 1175262).
07-Jan-00 jcarpent 115.94 - Subtract 1 day from date when checking
prev enrollment for tco-chg logic.
(bug 1120686/4145)
15-Feb-00 jcarpent 115.95 - Subtract 1 day from all enrollment chks
- Added plip level jumping restrictsions
back in. (bug 1146785/4153)
07-Mar-00 jcarpent 115.96 - De-enroll if "Lose only". bug 1217196
16-Mar-00 stee 115.97 - Remove program restriction if
cvg_incr_r_decr_only_cd is specified at
the plip level.
23-Mar-00 jcarpent 115.98 - Fix enrt_mthd_rule use. Removed
l_auto_enrt_rl (use l_ler_auto_enrt_rl)
Bug 1246785/4922.
29-Mar-00 lmcdonal 115.66 Bug 1247115 - do not subtract 1 from
life event ocrd dt when looking for
esd/eed. Only when looking for cvg
strt/end dates. Did this to all
cursors that had the -1 except those
looking at elig_per tables.
06-Apr-00 mmogel 115.100 - Added tokens to message calls to make
the messages more meaningful to the user
11-Apr-00 maagrawa 115.101 - Start the enrollment period on the
effective date if there is an conflicting
life event between the enrollment period
start date and the effective date.
Bug 4988.
18-Apr-00 maagrawa 115.102 Backport to 115.100
18-Apr-00 lmcdonal 115.103 Fido Bug 6176: When looking for current
enrollments, no longer look for
nvl(lf_evt_orcd_dt, eff dt) between esd and eed
instead look for eed = eot.
20-Apr-00 mhoyes 115.104 - Added profiling messages.
03-May-00 pbodla 115.105 - Task 131 : execute_enrt_rule modified
- If elig dependent rows are already created
then pass the elig dependent id as input
values to rule.
12-May-00 mhoyes 115.106 - Added profiling messages.
13-May-00 jcarpent 115.107 - Modified enrt_perd_strt_dt logic for
reinstated le's (4988,1269016)
15-May-00 mhoyes 115.108 - Called create EPE performance cover.
16-May-00 mhoyes 115.109 - Added profiling messages.
18-May-00 jcarpent 115.110 - Modified c_get_latest_procd_dt to
ignore backed out nocopy pils (4988,1269016)
23-May-00 mhoyes 115.113 - General tuning of SQL by dharris.
- Added p_comp_obj_tree_row to
enrolment_requirements.
24-May-00 mhoyes 115.114 - Added/removed trace messages.
- Enabled cache mode for rates_and_coverage
dates. This caches the per in ler.
25-May-00 jcarpent 115.115 - Added logic to have crnt_enrt_prclds_chg
work for entire ptip. (5216,1308627)
29-May-00 mhoyes 115.116 - Commented out nocopy hr_utility calls for
performance in highly executed logic.
- Tuning by dharris.
31-May-00 mhoyes 115.117 - Passed through current comp object
row parameters to avoid cache calls.
- Passed comp object rows to
rate_and_coverage_dates to avoid cursor
executions.
09-Jun-00 pbodla 115.118 - Bug 5272 : A fix to cursors
c_sched_enrol_period_for_plan,
c_sched_enrol_period_for_pgm in version
115.113 caused this bug. These cursors
are fixed.
23-Jun-00 pbodla 115.119 - Fix to check enrollment results as
of the life event occurred date instead
of life event occurred -1 when
determining the default flag. Bug 5241.
28-Jun-00 shdas 115.120 - Added procedure execute_auto_dflt_enrt_rule.
28-Jun-00 jcarpent 115.121 - Bug 4936: Use new cvg_strt_dt if sched
enrt and plan is for 125 or 129
regulation.
29-Jun-00 mhoyes 115.122 - Reworked cursors c_nenrt and c_pl_bnft_rstrn
as PLSQL.
30-Jun-00 mhoyes 115.123 - Fired cursor c_nenrt when context parameters
are not set.
05-Jul-00 mhoyes 115.124 - Added context parameters.
13-Jul-00 mhoyes 115.125 - Removed context parameters.
19-Jul-00 jcarpent 115.126 - 5241,1343362. Added update_defaults
03-Aug-00 jcarpent 115.127 - 5429. Fixed reinstate dependent logic.
04-Aug-00 pbodla 115.128 - 4871(1255493) : After mndtry_rl executed
it's out nocopy put is not used.
31-Aug-00 jcarpent 115.129 - Comp object changes were causing
non-date effective error. Added
requery logic. WWbug 1394507.
05-sep-00 pbodla 115.130 - Bug 5422 : Allow different enrollment periods
for programs for a scheduled enrollment.
p_popl_enrt_typ_cycl_id is removed.
14-Sep-00 jcarpent 115.131 - Leapfrog version based on 115.129.
Bug 1401098. Added logic to update_
defaults to change elctbl_flag also.
Needed for rules which use enrol/elig
in other plans for electablility.
14-Sep-00 jcarpent 115.132 - Merged version of 115.131 with 130.
25-Sep-00 mhoyes 115.133 - Removed hr_utility.set_locations.
07-Nov-00 mhoyes 115.134 - Added hr_utility.set_locations for
profiling.
- Modified update_defaults to use the
electable choice performance API.
15-dec-00 tilak 115.135 bug: 1527086 early denenrollment date carried forward
for future enrolment ,oipl_id condtion addded in cursor wich
take the information ofexisting enrolment for OPT level
04-jan-01 jcarpent 115.136 - Bug 1568555. Removed +1 from enrt
period logic.
01-feb-01 kmahendr 115.137 - Bug 1621593 - If plan is savings and has options attached
then previous enrollment is checked and prtt_enrt_rslt_id
is assigned
08-feb-01 tilak 115.138 find_enrt_at_same_level changed to return the correct row
bug : 1620161
26-feb-01 kmahendr 115.139 - unrestricted process changes - if future enrollments exists
then treat it as not currently enrolled
27-feb-01 thayden 115.140 - change unrestricted enrolment period end date to end of time.
08-jun-01 kmahendr 115.141 - Bug#1811636 - For level jumping logical order number is used
in place of database ordr_num
08-jun-01 kmahendr 115.142 - Effective date condition added to cursor c_opt_level
26-jul-01 ikasire 115.143 bug1895874 added new nip_dflt_flag to ben_pl_f table.
In the internal procedure determine_dflt_enrt_cd
added to parameters p_pgm_rec,p_ptip_rec,p_plip_rec,
p_oipl_rec,p_pl_rec to get the data from the already
cached records. Also removed the calls to ben_cobj_cache
from internal determine_dflt_enrt_cd procedure as this
can not be used here.
27-aug-01 tilak 115.144 bug:1949361 jurisdiction code is
derived inside benutils.formula.
13-Nov-01 kmahendr 115.145 bug#2080856 - in determine_dflt_flag procedure, coverage is
checked at ptip level if plan level fails.
15-Nov-01 kmahendr 115.146 Bug#2080856 - if dflt_enrt_cd is null then default flag
is set to 'N' - changed the assignment in determine_dflt_flag
07-Dec-01 mhoyes 115.146 - Added p_per_in_ler_id to enrolment_requirements.
11-Dec-01 mhoyes 115.147 - Added p_per_in_ler_id to update_defaults.
18-Dec-01 kmahendr 115.148 - Comp. work bench changes
19-Dec-01 kmahendr 115.149 - put the end if before elig_flag for p_run_mode = 'W'
29-Dec-01 pbodla 115.152 - CWB Changes : Initialised package
globals g_ple_hrchy_to_use_cd,
g_ple_pos_structure_version_id
to use later in benmngle.
29-Dec-01 pbodla 115.153 - CWB Changes : hierarchy to use code
lookup codes changed to match ones in
seed115, extended C mode to W.
03-Jan-02 rpillay 115.154 - Applied nvl function to all flag-type parameters
passed in the call to create_perf_elig_per_elc_chc
from enrolment_requirements Bug# 2141756
04-jan-02 pbodla 115.155 - CWB Changes : hierarchy table is
populated after epe row is created.
11-Jan-02 ikasire 115.156 CWB Changes Bug 2172036 addition of new column
assignment_id to epe table
29-Jan-02 kmahendr 115.157 Bug#2108168 - Added a cursor c_ptip_waive_enrolment_info
in the procedure - determine_enrollment to check for
current enrollment in a waive plan
12-feb-02 pbodla 115.158 - CWB Changes : 2213828 - No need to
determine the enrt_perd_strt_dt,
enrt_perd_end_dt if the mode is W.
12-feb-02 pbodla 115.159 - CWB Changes : 2230922 : If ws manager id
not found then default to supervisor if
position heirarchy is used.
27-Feb-02 rpillay 115.160 - CWB Bug 2241010 Made changes to set elctbl_flag
and elig_flag to 'N' when person is ineligible
01-Apr-02 ikasire 115.161 Bug 229032 if the enrollment starts in a future
date, for unrestricted, user cannot get the enrollment
record for correction causing problems.
17-Apr-02 kmahendr 115.162 Bug#2328029 - previous eligibility must be arrived based
on life event occurred date or effective date-changed
l_effective_date_1 in enrollment_requirements and update
default procedures.
02-May-02 hnarayan 115.163 bug 2008871 - commented the return and included it
inside the requery cursor if block
since it affects positive cases of electability
14-May-02 ikasire 115.164 Bug 2374403 electable choices for the imputed
income type of spouse is not getting created
properly. IMPTD_INCM_CALC_CD of SPSL is used
instead of using SPS
19-May-02 ikasire 115.165 Bug 2200139 Override Enrollment changes
08-Jun-02 pabodla 115.164 Do not select the contingent worker
assignment when assignment data is
fetched.
20-Jun-02 ikasire 115.167 Bug 2404008 fixed by passing lf_evt_ocrd_dt rather
than p_effective_date to determine_dflt_enrt_cd call
14-Jul-02 pbodla 115.168 ABSENCES - Added absences mode M.
This mode is similar to mode L.
05-Aug-02 mmudigon 115.169 ABSENCES - do not look at backed out nocopy and
processed lers for determining enrollment
period
08-Aug-02 tjesumic 115.170 # 2500805 certain situation,where 2 option in same plan
became defaulted if the coverage start date is not controlled
Pls See the bug for the test case.
cursors c_plan_enrolment_info,c_oipl_enrolment_info in
procedure determine_dflt_flag validatd for cvg_strt_dt
21-Aug-02 pbodla 115.171 Bug 2503570 - CWB electable flag always set Y
29-Aug-02 kmahendr 115.172 Bug#2207956 - new enrollment code added and references
to sec129 or 125 is removed.
13-Sep-02 tjesumic 115.173 # 2534744 to fix the issue of defaulting past enrolled
elected value new retutn values introduced in formula
AUTO_DFLT_ELCN_VAL in execute_auto_dflt_enrt_rule
formula type auto enrollment
Which return elected value which is used by bencvrge to
populated the default benefit
28-Sep-02 pbodla 115.174 Bug 2600087 added order by clause to
c_oipl_enrolment_info and c_plan_enrolment_info
20-Oct-02 mhoyes 115.175 - Phased in call to ben_pep_cache.get_currpepepo_dets.
- Phased out nocopy previous eligibility cursors and
pointed to the previous eligibility cache.
01-Nov-02 mmudigon 115.176 - CWB: Bug 2526595 Bypass creation of epe
if trk_inelig is set to No and the person
is not eligible
01-Nov-02 tjesumic 115.177 - # 2542162 enrt_perd_strt_dt send as param to the rule which
Calculate the enrt_perd_end_dt
01-Dec-02 pabodla 115.178 - Arcsing the file with CWB itemization code as commented.
04-Dec-02 rpillay 115.179 - CWB: Bug 2684227 - If a position is vacant climb the
position hierarchy till a person is found
09-Dec-02 mmudigon 115.180 - CWB itemization code uncommented.
16 Dec 02 hnarayan 115.181 Added NOCOPY hint
27 Dec 02 ikasire 115.182 Bug 2677804 changes.for Override thru date
03 Jan 03 tjesumic 115.183 bug # 2685018 if the formula return the result id then
the result id will be catcated wiht dpnt carry forawrd to store
07 Dec 03 tjesumic 115.184 New return value added in default enrollment formula 'PREV_PRTT_ENRT_RSLT_ID'
This value added to the value of CARRY_FORWARD_ELIG_DPNT for storage
10 jan 03 pbodla 115.18? GRADE/STEP : Added code to support
grade step progression process.
24-Feb-03 mmudigon 115.185 - CWB itemization : implement trk_inelig
flag at oipl level
12-Mar-03 ikasire 115.186 Bug 2827121 Contingent worker issue for
cwb
17-Mar-03 vsethi 115.187 - Bug 2650247 -passing the value for inelg_rsn_cd in call to
elig per elctbl chc api
10-Apr-03 rpgupta 115.188 - Bug 2746865
Enrollment period window enhancement
Changed the logic for arriving at the enrollment period start
and end dates
20-Jun-03 kmahendr 115.189 - Bug#2980560 - date passed to get_pilepo was changed to
l_effective_date_1
14-Jul-03 ikasire 115.190 - Bug 3044311 to get valid manager
for cwb position hierarchy.
11-Aug-03 ikasire 115.191 Bug 3058189 use l_lf_evt_ocrd_dt for the unrestricted cursors
while getting the pen info.
11-Aug-03 tjesumic 115.192 # 3086161 whne the LE reprocedd ,the Enrt Dt determined as of the
Previous LE Processed Date. if the nerollment made on the same date
the Exisiting results are updated in correction mode(same date).
Whne the cirrent LE backedout the previous LE results are lost
because the per_in_ler id updated in correction mode updated
This is fixed : if the max enrollment date is higer than the
max processed date then max enrollment dt + 1 used for enrt_prd_strt_dt
12-Aug-03 vsethi 115.193 Bug 3063867, Changed enrt_perd_strt_dt When a prior life event has been
VOIDD, the enrollment period start date should not be set as the last
backed out date instead retain the original enrt window.
28-Aug-03 tjesumic 115.194 115.192 fix is reversed for # 3086161
25-Sep-03 rpillay 115.195 GRADE/STEP: Changes to enrolment_requirements
to not throw error for G mode when year periods
are not set up
07-oct-03 hmani 115.196 Bug 3137519 - Modified the c_get_latest_enrt_dt cursor
21-oct-03 hmani 115.197 Bug 3177401 - Changed p_date_mandatory_flag to 'Y'
28-Oct-03 tjesumic 115.198 # 2982606 when the coverage start date of the prev LE is in future
and current LE is current or earlier than previous one
create new result and set currently enrolled flag to 'N'
related changes in benbolfe , benelinf ,benleclr
11-oct-03 hmani 115.199 reversing the change done in 115.197. Changing the flag again.
18-Nov-03 tjesumic 115.200 # 3248770 Voided per in ler is not considered to find the enrollment period date
14-Jan-04 pbodla 115.201 GLOBALCWB : moved the heirarchy data
population to benptnle (on to ben_per_in_ler)
15-Mar-04 pbodla 115.202 GLOBALCWB : Bug 3502094 : For FP-F trk
inelig flag do not have any significance.
For july FP : final functionality will be
decided later.
15-Mar-04 pbodla 115.203 GLOBALCWB : Bug 3502094 : Commented end if
properly
18-Mar-04 rpgupta 115.204 3510229 - Allow system to pick up the
default enrollment code from higher
levels if not found in the lowest level
13-Apr-04 kmahendr 115.205 FONM changes.
29-Apr-04 mmudigon 115.206 Bug 3595902. Changes to cursors
c_plan_enrolment_info_unrst and
c_plan_enrolment_info in proc
enrolment_requirements
15-Jun-04 mmudigon 115.207 Bug 3685228. Merged cursors
c_plan_enrolment_info_unrst and
c_plan_enrolment_info in proc
enrolment_requirements. Treating 'M'
mode similar to 'U' mode for these
cursors.
29-Jun-04 kmahendr 115.208 Bug#3726552 - added call for updating dflt flag on
enrt bnft in update_defaults
13-Jul-04 kmahendr 115.209 Bug#3697378 - modified cursor-c_get_latest_enrt_dt
23-Aug-04 mmudigon 115.210 CFW : 2534391 :NEED TO LEAVE ACTION ITEMS
CERTIFICATIONS on subsequent events
27-Sep-04 pbodla 115.211 iRec : Avoid iRec life events similar to
gsp events.
30-Sep-04 abparekh 115.212 iRec : While picking assignments for iRec do not compare
primary flag. Extend / Exclude processing for iRec like GSP.
21-Oct-04 tjesumic 115.213 # 3936695 Enrollment_cd fixed by cheking the level of the setup
26-Oct-04 tjesumic 115.214 # 3936695
29-Oct-04 ikasire 115.215 Bugs 3972973 and 3978745 fixes
15-nov-04 kmahendr 115.216 Unrest. enh changes
17-Nov-04 tjesumic 115.217 # 4008380 fixed. fixed default flag validations
22-Nov-04 abparekh 115.218 Bug 4023880 : Fixed code to create elctbl chc at PLAN level if
they dont exist while creating elctbl chc at OIPL level.
01-Dec-04 kmahendr 115.219 Unrest. enh changes
30-dec-04 nhunur 115.49 4031733 - No need to open cursor c_state.
04-Jan-04 kmahendr 115.221 Bug#4096382 - condition added to return if comp.
objects are in pending or suspended in U or R mode
05-Jan-05 ikasire 115.222 Bug 4106760 fix
11-Jan-05 ikasire 115.223 BUG 4064635 CF Suspended Interim Changes
26-Jan-05 ikasire 115.224 BUG 4064635 CF Suspended Interim Changes
02-Feb-05 ikasire 115.225 BUG 4064635 CF Suspended Interim Changes
18-Apr-05 tjesumic 115.226 GHR enhancement to add number of days in enrt perd codeds
29-Apr-05 kmahendr 115.227 Added a parameter - update_def_elct_flag to
determine_enrolment - bug#4338685
24-May-05 bmanyam 115.228 Bug 4388226 : Changed c_get_latest_enrt_dt to
pick up valid enrollments only.
08-Jul-05 rbingi 115.229 Bug 4447114 : assigning Global value of
ben_evaluate_elig_profiles.g_inelg_rsn_cd
to l_inelg_rsn_cd in case of CWB run mode
12-Jul-05 pbodla 115.230 Populating data into ben_cwb_hrchy is
completely removed.
21-Jul-05 kmahendr 115.231 Bug#4478186 - added codes to procedure
enrt_perd_strt_dt
10-Nov-05 tjesumic 115.233 fix 115.232 (4571771) is reversed as the benauten electable flag
validation is reverted
18-Jan-06 rbingi 115.234 Bug-4717052: Calling update_elig_per_elctbl when epe
existing for PLAN record for update of pen_id.
15-Feb-06 rbingi 115.235 Bug-5035423: contd from prev fix, calling Update_epe
only when epe exists for PLAN
03-Mar-06 kmahendr 115.236 Added new enrollment codes
14-Mar-06 ssarkar 115.237 5092244 - populating g_egd_table in update_defaults
04-Apr-06 rbingi 115.238 5029028: Added UNIONs to select the enrolment codes
from respective tables if ler_chg records not defined
26-Jun-06 swjain 115.239 5331889 - Added person_id param in calls to benutils.formula
30-Jun-06 swjain 115.240 Commented out show errors
01-sep-06 ssarkar 115.241 Bug 5491475 - reverted 5029028 fix
26-Sep-06 abparekh 115.242 Bug 5555402 - While determining DFLT_ENRT_CD, consider code
at PL_NIP level for enrollment at PLIP level
28-Sep-06 abparekh 115.243 Bug 5569758 - Get OIPL details correctly in procedure
DETERMINE_DFLT_ENRT_CD (1)
27-Sep-06 stee 115.244 Bug 5650482 - For scheduled mode, change the processing
end date to not slide if the event is backed out and
reprocessed at a later date.
05-Dec-06 stee 115.245 Bug 5650482 - If ENRT_PERD_DET_OVRLP_BCKDT_CD
= L_EPSD_PEPD then slide the processing date.
06-Jan-06 bmanyam 115.246 5736589: Increase/Decrease Requires certification
for Option Restrictions
08-Jan-06 bmanyam 115.247 5736589: Increase/Decrease Requires certification
for Option Restrictions
12-Jan-07 gsehgal 115.248 Bug 5644451 - Defaults are not getting created properly.
See bug details for test case. Also local procedure
determine_dflt_enrt_cd moved at top of the package as this
is now used in ben_enrolment_requirements.enrolment_requirements
10-Feb-07 stee 115.249 Added a check for g_debug for trace statements.
22-Jan-07 rtagarra 115.249 -- ICM Changes for 'D' Mode.
28-May-07 rgajula 115.250 Bug 6061856 -- Modified the procedure determine_dflt_enrt_cd such that
Default Enrollment Codes at LER level Override Default Enrollment Codes at OIPL level.
20-Dec-07 sagnanas Included fix 6281735 and 6519487 for 12.1
*/
---------------------------------------------------------------------------------------------------
g_package VARCHAR2(80) := 'ben_enrolment_requirements';
SELECT leo.dflt_enrt_cd,
leo.dflt_enrt_rl,
leo.dflt_flag
FROM ben_ler_chg_oipl_enrt_f leo
WHERE p_oipl_id = leo.oipl_id
AND p_ler_id = leo.ler_id
AND p_effective_date BETWEEN leo.effective_start_date
AND leo.effective_end_date;
SELECT len.dflt_enrt_cd,
len.dflt_enrt_rl,
len.dflt_flag
FROM ben_ler_chg_pl_nip_enrt_f len
WHERE p_pl_id = len.pl_id
AND p_ler_id = len.ler_id
AND p_effective_date BETWEEN len.effective_start_date
AND len.effective_end_date;
SELECT lep.dflt_enrt_cd,
lep.dflt_enrt_rl,
lep.dflt_flag
FROM ben_ler_chg_plip_enrt_f lep
WHERE p_plip_id = lep.plip_id
AND p_ler_id = lep.ler_id
AND p_effective_date BETWEEN lep.effective_start_date
AND lep.effective_end_date;
SELECT lep.dflt_enrt_cd,
lep.dflt_enrt_rl
FROM ben_ler_chg_ptip_enrt_f lep
WHERE p_ptip_id = lep.ptip_id
AND p_ler_id = lep.ler_id
AND p_effective_date BETWEEN lep.effective_start_date
AND lep.effective_end_date;
SELECT lep.dflt_enrt_cd,
lep.dflt_enrt_rl
FROM ben_ler_chg_pgm_enrt_f lep
WHERE p_pgm_id = lep.pgm_id
AND p_ler_id = lep.ler_id
AND p_effective_date BETWEEN lep.effective_start_date
AND lep.effective_end_date;
SELECT pln.nip_dflt_enrt_cd,
pln.nip_dflt_enrt_det_rl,
pln.nip_dflt_flag
FROM ben_pl_f pln
WHERE p_pl_id = pln.pl_id
AND p_effective_date BETWEEN pln.effective_start_date
AND pln.effective_end_date;
SELECT plp.dflt_enrt_cd,
plp.dflt_enrt_det_rl
FROM ben_plip_f plp
WHERE p_plip_id = plp.plip_id
AND p_effective_date BETWEEN plp.effective_start_date
AND plp.effective_end_date;
SELECT ptp.dflt_enrt_cd,
ptp.dflt_enrt_det_rl
FROM ben_ptip_f ptp
WHERE p_ptip_id = ptp.ptip_id
AND p_effective_date BETWEEN ptp.effective_start_date
AND ptp.effective_end_date;
SELECT oipl.auto_enrt_flag,
oipl.auto_enrt_mthd_rl,
oipl.crnt_enrt_prclds_chg_flag,
oipl.dflt_flag,
oipl.enrt_cd,
oipl.enrt_rl,
oipl.ler_chg_oipl_enrt_id,
oipl.stl_elig_cant_chg_flag
FROM ben_ler_chg_oipl_enrt_f oipl
WHERE oipl.oipl_id = p_oipl_id
AND oipl.ler_id = p_ler_id
AND p_lf_evt_ocrd_dt BETWEEN oipl.effective_start_date
AND oipl.effective_end_date;
SELECT pgm.auto_enrt_mthd_rl,
pgm.crnt_enrt_prclds_chg_flag,
pgm.enrt_cd,
pgm.enrt_mthd_cd,
pgm.enrt_rl,
pgm.ler_chg_pgm_enrt_id,
pgm.stl_elig_cant_chg_flag
FROM ben_ler_chg_pgm_enrt_f pgm
WHERE pgm.pgm_id = p_pgm_id
AND pgm.ler_id = p_ler_id
AND p_lf_evt_ocrd_dt BETWEEN pgm.effective_start_date
AND pgm.effective_end_date;
SELECT ptip.crnt_enrt_prclds_chg_flag,
ptip.enrt_cd,
ptip.enrt_mthd_cd,
ptip.enrt_rl,
ptip.ler_chg_ptip_enrt_id,
ptip.stl_elig_cant_chg_flag,
ptip.tco_chg_enrt_cd
FROM ben_ler_chg_ptip_enrt_f ptip
WHERE ptip.ptip_id = p_ptip_id
AND ptip.ler_id = p_ler_id
AND p_lf_evt_ocrd_dt BETWEEN ptip.effective_start_date
AND ptip.effective_end_date;
SELECT plip.auto_enrt_mthd_rl,
plip.crnt_enrt_prclds_chg_flag,
plip.dflt_flag,
plip.enrt_cd,
plip.enrt_mthd_cd,
plip.enrt_rl,
plip.ler_chg_plip_enrt_id,
plip.stl_elig_cant_chg_flag,
plip.tco_chg_enrt_cd
FROM ben_ler_chg_plip_enrt_f plip
WHERE plip.plip_id = p_plip_id
AND plip.ler_id = p_ler_id
AND p_lf_evt_ocrd_dt BETWEEN plip.effective_start_date
AND plip.effective_end_date;
SELECT pl_nip.auto_enrt_mthd_rl,
pl_nip.crnt_enrt_prclds_chg_flag,
pl_nip.dflt_flag,
pl_nip.enrt_cd,
pl_nip.enrt_mthd_cd,
pl_nip.enrt_rl,
pl_nip.ler_chg_pl_nip_enrt_id,
pl_nip.stl_elig_cant_chg_flag,
pl_nip.tco_chg_enrt_cd
FROM ben_ler_chg_pl_nip_enrt_f pl_nip
WHERE pl_nip.pl_id = p_pl_id
AND pl_nip.ler_id = p_ler_id
AND p_lf_evt_ocrd_dt BETWEEN pl_nip.effective_start_date
AND pl_nip.effective_end_date;
p_update_def_elct_flag varchar2) IS
--
l_proc VARCHAR2(80)
:= g_package ||
'.determine_enrolment';
SELECT pen.enrt_cvg_strt_dt enrt_cvg_strt_dt
FROM ben_prtt_enrt_rslt_f pen, ben_pl_f pl, ben_oipl_f oipl
WHERE pen.person_id = p_person_id
AND pen.business_group_id = p_business_group_id
AND pen.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt_1 BETWEEN pen.enrt_cvg_strt_dt
AND pen.enrt_cvg_thru_dt
AND pl.pl_id = pen.pl_id
AND pl.business_group_id = p_business_group_id
AND pl.pl_typ_id = p_pl_typ_id
AND oipl.oipl_id = pen.oipl_id
AND oipl.opt_id = p_opt_id
AND l_lf_evt_ocrd_dt BETWEEN pl.effective_start_date
AND pl.effective_end_date
AND l_lf_evt_ocrd_dt BETWEEN oipl.effective_start_date
AND oipl.effective_end_date
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND pen.sspndd_flag = 'N'
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND oipl.business_group_id = p_business_group_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NULL));
SELECT pen.enrt_cvg_strt_dt enrt_cvg_strt_dt
FROM ben_prtt_enrt_rslt_f pen,
ben_pl_f pl,
ben_oipl_f oipl,
ben_elig_cvrd_dpnt_f pdp
WHERE pdp.dpnt_person_id = p_person_id
AND l_lf_evt_ocrd_dt_1 BETWEEN pdp.cvg_strt_dt AND pdp.cvg_thru_dt
AND pen.effective_end_date = hr_api.g_eot
AND pdp.effective_end_date = hr_api.g_eot
AND pdp.business_group_id = p_business_group_id
AND pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
AND pen.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt_1 BETWEEN pen.enrt_cvg_strt_dt
AND pen.enrt_cvg_thru_dt
AND pl.pl_id = pen.pl_id
AND pl.business_group_id = p_business_group_id
AND pl.pl_typ_id = p_pl_typ_id
AND oipl.oipl_id = pen.oipl_id
AND oipl.opt_id = p_opt_id
AND l_lf_evt_ocrd_dt BETWEEN pl.effective_start_date
AND pl.effective_end_date
AND l_lf_evt_ocrd_dt BETWEEN oipl.effective_start_date
AND oipl.effective_end_date
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND pen.sspndd_flag = 'N'
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND oipl.business_group_id = p_business_group_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NULL));
SELECT loc.region_2
FROM hr_locations_all loc, per_all_assignments_f asg
WHERE loc.location_id = asg.location_id
AND asg.person_id = p_person_id
and asg.assignment_type <> 'C'
AND asg.primary_flag = 'Y'
AND l_lf_evt_ocrd_dt BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.business_group_id = p_business_group_id;
SELECT asg.assignment_id,
asg.organization_id
FROM per_all_assignments_f asg
WHERE person_id = p_person_id
and asg.assignment_type <> 'C'
AND asg.primary_flag = decode(p_run_mode, 'I',asg.primary_flag, 'Y') -- iRec
AND l_lf_evt_ocrd_dt BETWEEN asg.effective_start_date
AND asg.effective_end_date;
SELECT pen.enrt_cvg_strt_dt enrt_cvg_strt_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 pen.prtt_enrt_rslt_stat_cd IS NULL
-- AND pen.sspndd_flag = 'N' --CFW
AND (pen.sspndd_flag = 'N' --CFW
OR (pen.sspndd_flag = 'Y' and
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
)
AND pen.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt_1 <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND p_pl_id = pen.pl_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NULL));
SELECT pen.enrt_cvg_strt_dt enrt_cvg_strt_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 pen.prtt_enrt_rslt_stat_cd IS NULL
-- AND pen.sspndd_flag = 'N' --CFW
AND (pen.sspndd_flag = 'N' --CFW
OR (pen.sspndd_flag = 'Y' and
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
)
AND pen.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt_1 <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND p_pl_id = pen.pl_id
AND pen.pgm_id = p_pgm_id ;
SELECT pen.enrt_cvg_strt_dt enrt_cvg_strt_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 pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt_1 <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND g_ptip_id = pen.ptip_id
AND pen.pgm_id = p_pgm_id ;
SELECT pen.enrt_cvg_strt_dt enrt_cvg_strt_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 pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt_1 <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND pen.pgm_id = p_pgm_id ;
SELECT pen.enrt_cvg_strt_dt
FROM ben_prtt_enrt_rslt_f pen, ben_pl_f pln
WHERE pen.person_id = p_person_id
AND pen.business_group_id = p_business_group_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.sspndd_flag = 'N'
AND pen.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt_1 <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND g_ptip_id = pen.ptip_id
AND pln.pl_id = pen.pl_id
AND pln.invk_dcln_prtn_pl_flag = 'Y'
AND l_lf_evt_ocrd_dt BETWEEN pln.effective_start_date
AND pln.effective_end_date;
SELECT pen.enrt_cvg_strt_dt enrt_cvg_strt_dt
FROM ben_prtt_enrt_rslt_f pen, ben_elig_cvrd_dpnt_f pdp
WHERE pdp.dpnt_person_id = p_person_id
AND pen.effective_end_date = hr_api.g_eot
AND pdp.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt_1 BETWEEN pdp.cvg_strt_dt AND pdp.cvg_thru_dt
AND pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
AND pen.business_group_id = p_business_group_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
-- AND pen.sspndd_flag = 'N'
AND (pen.sspndd_flag = 'N' --CFW
OR (pen.sspndd_flag = 'Y' and
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
)
AND l_lf_evt_ocrd_dt_1 <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_thru_dt < pen.effective_end_date
AND p_pl_id = pen.pl_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NULL));
SELECT pen.pl_id,
pen.oipl_id,
plip.plip_id
FROM ben_prtt_enrt_rslt_f pen, ben_plip_f plip
WHERE pen.person_id = p_person_id
AND pen.business_group_id = p_business_group_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
--AND pen.sspndd_flag = 'N'
AND (pen.sspndd_flag = 'N' --CFW
OR (pen.sspndd_flag = 'Y' and
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
)
AND pen.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt_1 <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND g_ptip_id = pen.ptip_id
AND plip.pgm_id = pen.pgm_id
AND plip.pl_id = pen.pl_id
AND l_lf_evt_ocrd_dt BETWEEN plip.effective_start_date
AND plip.effective_end_date;
SELECT bpf.plip_id
FROM ben_plip_f bpf
WHERE bpf.pl_id = p_pl_id
AND bpf.pgm_id = p_pgm_id
AND bpf.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN bpf.effective_start_date
AND bpf.effective_end_date;
select
nvl(oipl.crnt_enrt_prclds_chg_flag,
nvl(pl_nip.crnt_enrt_prclds_chg_flag,
nvl(plip.crnt_enrt_prclds_chg_flag,
nvl(ptip.crnt_enrt_prclds_chg_flag,
pgm.crnt_enrt_prclds_chg_flag)))) crnt_enrt_prclds_chg_flag
from dual,
ben_ler_chg_pl_nip_enrt_f pl_nip,
ben_ler_chg_pgm_enrt_f pgm,
ben_ler_chg_ptip_enrt_f ptip,
ben_ler_chg_plip_enrt_f plip,
ben_ler_chg_oipl_enrt_f oipl
where
pl_nip.pl_id(+)=l_enrolled_ptip.pl_id and
pl_nip.business_group_id(+)=p_business_group_id and
pl_nip.ler_id(+)=decode(dual.dummy,dual.dummy,p_ler_id,dual.dummy) and
l_lf_evt_ocrd_dt between
pl_nip.effective_start_date(+) and pl_nip.effective_end_date(+) and
ptip.ptip_id(+)=g_ptip_id and
ptip.business_group_id(+)=p_business_group_id and
ptip.ler_id(+)=decode(dual.dummy,dual.dummy,p_ler_id,dual.dummy) and
l_lf_evt_ocrd_dt between
ptip.effective_start_date(+) and ptip.effective_end_date(+) and
pgm.pgm_id(+)=p_pgm_id and
pgm.business_group_id(+)=p_business_group_id and
pgm.ler_id(+)=decode(dual.dummy,dual.dummy,p_ler_id,dual.dummy) and
l_lf_evt_ocrd_dt between
pgm.effective_start_date(+) and pgm.effective_end_date(+) and
plip.plip_id(+)=l_enrolled_ptip.plip_id and
plip.business_group_id(+)=p_business_group_id and
plip.ler_id(+)=decode(dual.dummy,dual.dummy,p_ler_id,dual.dummy) and
l_lf_evt_ocrd_dt between
plip.effective_start_date(+) and plip.effective_end_date(+) and
oipl.oipl_id(+) = l_enrolled_ptip.oipl_id and
oipl.business_group_id(+) = p_business_group_id and
oipl.ler_id(+)=decode(dual.dummy,dual.dummy,p_ler_id,dual.dummy) and
l_lf_evt_ocrd_dt between
oipl.effective_start_date(+) and oipl.effective_end_date(+);
/* if p_update_def_elct_flag is not null then -- don't reevaluate the rule again
--
p_electable_flag := p_update_def_elct_flag;
select assignment_id,
position_id,
supervisor_id
from per_all_assignments_f
where person_id = p_person_id
and primary_flag = 'Y'
and assignment_type in ( 'E','C' ) -- Bug 2827121
and p_effective_date
between effective_start_date and effective_end_date;
select parent_position_id
from per_pos_structure_elements
where subordinate_position_id = p_position_id
and pos_structure_version_id = p_pos_structure_version_id;
select person_id
from per_all_assignments_f ass,
per_assignment_status_types ast
where ass.position_id = p_position_id
and ass.primary_flag = 'Y'
and ass.assignment_type in ( 'E' , 'C' ) -- Bug 2827121
and p_effective_date
between ass.effective_start_date and ass.effective_end_date
--Bug 3044311 -- Need to verify what other system types should be considered.
and ass.assignment_status_type_id = ast.assignment_status_type_id
-- and ast.active_flag = 'Y'
and ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN') ;
SELECT MAX(pil.procd_dt)
FROM ben_per_in_ler pil
-- CWB changes
,ben_ler_f ler
WHERE pil.person_id = p_person_id
AND pil.ler_id = ler.ler_id
and ler.typ_cd not in ('COMP','ABS', 'GSP', 'IREC','SCHEDDU')
and l_lf_evt_ocrd_dt between
ler.effective_start_date and ler.effective_end_date
AND pil.business_group_id = p_business_group_id
AND pil.per_in_ler_stat_cd NOT IN ('BCKDT', 'VOIDD')
AND pil.procd_dt IS NOT NULL;
SELECT MAX(pil.bckt_dt)
FROM ben_per_in_ler pil
-- CWB changes
,ben_ler_f ler
,ben_ptnl_ler_for_per plr
WHERE pil.person_id = p_person_id
AND pil.ler_id = ler.ler_id
and ler.typ_cd not in ('COMP','ABS', 'GSP', 'IREC','SCHEDDU')
and l_lf_evt_ocrd_dt between
ler.effective_start_date and ler.effective_end_date
AND pil.business_group_id = p_business_group_id
AND pil.ler_id = p_ler_id
AND pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
AND pil.bckt_dt IS NOT NULL
and pil.per_in_ler_stat_cd = 'BCKDT' -- 3063867
and pil.ptnl_ler_for_per_id = plr.ptnl_ler_for_per_id --3248770
and plr.ptnl_ler_for_per_stat_cd <> 'VOIDD' ;
select max(rslt.effective_start_date)
from ben_prtt_enrt_rslt_f rslt,ben_ler_f ler
where rslt.person_id = p_person_id
and ler.ler_id=rslt.ler_id
-- and rslt.prtt_enrt_rslt_stat_cd NOT IN ('BCKDT', 'VOIDD')
and rslt.prtt_enrt_rslt_stat_cd is null
and ler.typ_cd not in ('COMP','ABS', 'GSP', 'IREC','SCHEDDU' )
and rslt.business_group_id = p_business_group_id
and rslt.enrt_cvg_thru_dt = hr_api.g_eot; -- Bug 4388226 - End-dated suspended enrl shudn't be picked up.
SELECT bpt.effective_start_date,
bpt.effective_end_date
FROM ben_pl_typ_f bpt
WHERE bpt.pl_typ_id = l_pl_typ_id
AND l_lf_evt_ocrd_dt BETWEEN bpt.effective_start_date
AND bpt.effective_end_date
AND bpt.business_group_id = p_business_group_id;
SELECT loc.region_2
FROM hr_locations_all loc, per_all_assignments_f asg
WHERE loc.location_id = asg.location_id
AND asg.person_id = p_person_id
and asg.assignment_type <> 'C'
AND asg.primary_flag = 'Y'
AND l_lf_evt_ocrd_dt BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.business_group_id = p_business_group_id;
SELECT asg.assignment_id,
asg.organization_id
FROM per_all_assignments_f asg
WHERE asg.person_id = p_person_id
and asg.assignment_type <> 'C'
AND asg.primary_flag = decode (p_run_mode, 'I', asg.primary_flag, 'Y')-- iREC
AND l_lf_evt_ocrd_dt BETWEEN asg.effective_start_date
AND asg.effective_end_date;
SELECT pyp.yr_perd_id,
pyp.popl_yr_perd_id,
yp.start_date,
yp.end_date,
pyp.ordr_num
FROM ben_popl_yr_perd pyp, ben_yr_perd yp
WHERE pyp.pl_id = l_pl_id
AND pyp.yr_perd_id = yp.yr_perd_id
AND pyp.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN yp.start_date AND yp.end_date
AND yp.business_group_id = p_business_group_id;
SELECT NULL
FROM ben_elig_per_elctbl_chc epe
WHERE epe.oipl_id = p_oipl_id
-- added 9/25/98 to handle plans in mult progs
AND (
( epe.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( epe.pgm_id IS NULL
AND p_pgm_id IS NULL))
AND epe.per_in_ler_id = l_per_in_ler_id
AND epe.business_group_id = p_business_group_id;
SELECT elig_per_elctbl_chc_id
FROM ben_elig_per_elctbl_chc epe
WHERE epe.pl_id = pp_pl_id
AND epe.oipl_id IS NULL
-- added 9/25/98 to handle plans in mult progs
AND (
( epe.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( epe.pgm_id IS NULL
AND p_pgm_id IS NULL))
AND epe.per_in_ler_id = l_per_in_ler_id
AND epe.business_group_id = p_business_group_id;
SELECT pen.enrt_cvg_strt_dt,
pen.erlst_deenrt_dt,
pen.prtt_enrt_rslt_id,
pen.enrt_ovridn_flag,
pen.enrt_ovrid_thru_dt,
pen.orgnl_enrt_dt,
pen.enrt_cvg_thru_dt,
pen.pl_typ_id
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.person_id = p_person_id
AND pen.business_group_id = p_business_group_id
AND (pen.sspndd_flag = 'N' --CFW
OR (pen.sspndd_flag = 'Y' and
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
)
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.effective_end_date = hr_api.g_eot
AND p_cvg_dt <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND pen.oipl_id IS NULL
AND l_pl_id = pen.pl_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NULL))
order by pen.enrt_cvg_strt_dt,decode(pen.sspndd_flag,'Y',1,2) ;
SELECT pen.enrt_cvg_strt_dt,
pen.erlst_deenrt_dt,
pen.prtt_enrt_rslt_id,
pen.enrt_ovridn_flag,
pen.enrt_ovrid_thru_dt,
pen.enrt_cvg_thru_dt,
pen.pl_typ_id
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.person_id = p_person_id
AND pen.business_group_id = p_business_group_id
AND (pen.sspndd_flag = 'N' --CFW
OR (pen.sspndd_flag = 'Y' and
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
)
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.effective_end_date = hr_api.g_eot
AND p_cvg_dt <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND p_oipl_id = pen.oipl_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NULL))
-- Bug 2600087
order by pen.enrt_cvg_strt_dt,decode(pen.sspndd_flag,'Y',1,2);
SELECT 'Y'
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.person_id = p_person_id
AND pen.effective_end_date = hr_api.g_eot
AND pen.enrt_cvg_strt_dt > p_enrt_cvg_thru_dt
ANd pen.pl_typ_id = p_pl_typ_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NULL))
AND pen.prtt_enrt_rslt_stat_cd is null;
SELECT 'Y'
FROM ben_prtt_enrt_rslt_f pen, ben_elig_cvrd_dpnt_f pdp
WHERE pdp.dpnt_person_id = p_person_id
AND pdp.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt_1 BETWEEN pdp.cvg_strt_dt AND pdp.cvg_thru_dt
AND pdp.business_group_id = p_business_group_id
AND pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
AND pen.business_group_id = p_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 l_lf_evt_ocrd_dt_1 <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND l_pl_id = pen.pl_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NULL));
SELECT 'Y'
FROM ben_prtt_enrt_rslt_f pen, ben_elig_cvrd_dpnt_f pdp
WHERE pdp.dpnt_person_id = p_person_id
AND pdp.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt_1 BETWEEN pdp.cvg_strt_dt AND pdp.cvg_thru_dt
AND pdp.business_group_id = p_business_group_id
AND pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
AND pen.business_group_id = p_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 l_lf_evt_ocrd_dt_1 <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND p_oipl_id = pen.oipl_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NULL));
SELECT ep.elig_per_id,
epo.elig_flag,
ep.must_enrl_anthr_pl_id,
epo.prtn_strt_dt
FROM ben_elig_per_f ep, ben_elig_per_opt_f epo, ben_per_in_ler pil
WHERE ep.person_id = p_person_id
AND ep.pl_id = l_pl_id
AND (
( ep.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( ep.pgm_id IS NULL
AND p_pgm_id IS NULL))
AND ep.business_group_id = p_business_group_id
AND p_effective_date BETWEEN ep.effective_start_date
AND ep.effective_end_date
AND ep.elig_per_id = epo.elig_per_id
AND epo.opt_id = l_opt_id
AND epo.business_group_id = p_business_group_id
AND p_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') -- found row condition
OR pil.per_in_ler_stat_cd IS NULL); -- outer join condition
SELECT epo.elig_flag
FROM ben_elig_per_f ep, ben_elig_per_opt_f epo, ben_per_in_ler pil
WHERE ep.person_id = p_person_id
AND ep.pl_id = l_pl_id
AND (
( ep.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( ep.pgm_id IS NULL
AND p_pgm_id IS NULL))
AND ep.business_group_id = p_business_group_id
AND p_effective_date - 1 BETWEEN ep.effective_start_date
AND ep.effective_end_date
AND ep.elig_per_id = epo.elig_per_id
AND epo.opt_id = l_opt_id
AND epo.business_group_id = p_business_group_id
AND l_effective_date_1 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') -- found row condition
OR pil.per_in_ler_stat_cd IS NULL); -- outer join condition
SELECT pep.elig_per_id,
pep.elig_flag,
pep.must_enrl_anthr_pl_id,
pep.prtn_strt_dt,
pep.inelg_rsn_cd -- 2650247
FROM ben_elig_per_f pep, ben_per_in_ler pil
WHERE pep.person_id = p_person_id
AND pep.pl_id = l_pl_id
AND (
( pep.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pep.pgm_id IS NULL
AND p_pgm_id IS NULL))
AND pep.business_group_id = p_business_group_id
AND p_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 pep.elig_flag
FROM ben_elig_per_f pep, ben_per_in_ler pil
WHERE pep.person_id = p_person_id
AND pep.pl_id = l_pl_id
AND (
( pep.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pep.pgm_id IS NULL
AND p_pgm_id IS NULL))
AND pep.business_group_id = p_business_group_id
AND l_effective_date_1 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 pil.per_in_ler_id,
ler.typ_cd,
ler.name,
pil.lf_evt_ocrd_dt,
ler.effective_start_date,
ler.effective_end_date
FROM ben_per_in_ler pil, ben_ler_f ler
WHERE pil.person_id = p_person_id
AND pil.business_group_id = p_business_group_id
AND pil.ler_id = p_ler_id
AND pil.per_in_ler_stat_cd = 'STRTD'
AND ler.business_group_id = p_business_group_id
AND pil.ler_id = ler.ler_id
AND l_lf_evt_ocrd_dt BETWEEN ler.effective_start_date
AND ler.effective_end_date
and nvl(pil.assignment_id, -9999) = decode (p_run_mode,
'I',
ben_manage_life_events.g_irec_ass_rec.assignment_id,
nvl(pil.assignment_id, -9999) ); -- iRec
SELECT pil.per_in_ler_id,
ler.typ_cd,
ler.name,
pil.lf_evt_ocrd_dt,
ler.effective_start_date,
ler.effective_end_date
FROM ben_per_in_ler pil, ben_ler_f ler
WHERE pil.per_in_ler_id = p_per_in_ler_id
AND pil.business_group_id = p_business_group_id
-- AND pil.ler_id = p_ler_id
-- AND pil.per_in_ler_stat_cd = 'STRTD'
AND ler.business_group_id = p_business_group_id
AND pil.ler_id = ler.ler_id
AND l_lf_evt_ocrd_dt BETWEEN ler.effective_start_date
AND ler.effective_end_date;
SELECT enrtp.enrt_perd_id,
enrtp.strt_dt,
enrtp.end_dt,
enrtp.procg_end_dt,
enrtp.dflt_enrt_dt,
petc.enrt_typ_cycl_cd,
enrtp.cls_enrt_dt_to_use_cd,
enrtp.hrchy_to_use_cd,
enrtp.pos_structure_version_id,
/* bug 2746865 */
enrtp.enrt_perd_det_ovrlp_bckdt_cd
FROM ben_popl_enrt_typ_cycl_f petc,
ben_enrt_perd enrtp,
ben_ler_f ler
WHERE petc.pl_id = l_pl_id
AND petc.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt 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 = p_lf_evt_ocrd_dt
/* PB :5422 AND enrtp.strt_dt=enrtp1.strt_dt
AND enrtp1.popl_enrt_typ_cycl_id = p_popl_enrt_typ_cycl_id
AND enrtp1.business_group_id = p_business_group_id */
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 l_lf_evt_ocrd_dt between ler.effective_start_date (+)
and ler.effective_end_date (+);
SELECT enrtp.enrt_perd_id,
enrtp.strt_dt,
enrtp.end_dt,
enrtp.procg_end_dt,
enrtp.dflt_enrt_dt,
petc.enrt_typ_cycl_cd,
enrtp.cls_enrt_dt_to_use_cd,
enrtp.hrchy_to_use_cd,
enrtp.pos_structure_version_id,
/* bug 2746865*/
enrtp.enrt_perd_det_ovrlp_bckdt_cd
FROM ben_popl_enrt_typ_cycl_f petc,
ben_enrt_perd enrtp,
ben_ler_f ler
WHERE petc.pgm_id = p_pgm_id
AND petc.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt 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 = p_lf_evt_ocrd_dt
/* PB :5422 AND enrtp1.business_group_id = p_business_group_id
AND enrtp.strt_dt= enrtp1.strt_dt
AND enrtp1.enrt_perd_id = p_popl_enrt_typ_cycl_id */
AND enrtp.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
-- comp work bench
and ler.ler_id (+) = enrtp.ler_id
and ler.ler_id (+) = p_ler_id
and l_lf_evt_ocrd_dt between ler.effective_start_date (+)
and ler.effective_end_date (+);
SELECT leer.dys_aftr_end_to_dflt_num,
leer.enrt_perd_end_dt_rl,
leer.enrt_perd_strt_dt_rl,
leer.enrt_perd_end_dt_cd,
leer.enrt_perd_strt_dt_cd,
leer.addl_procg_dys_num,
petc.enrt_typ_cycl_cd,
leer.lee_rsn_id,
leer.cls_enrt_dt_to_use_cd,
leer.effective_start_date,
leer.effective_end_date,
leer.enrt_perd_strt_days,
leer.enrt_perd_end_days,
/* bug 2746865*/
leer.enrt_perd_det_ovrlp_bckdt_cd
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 l_lf_evt_ocrd_dt 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 = l_pl_id
AND petc.enrt_typ_cycl_cd = 'L' -- life event
AND petc.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN petc.effective_start_date
AND petc.effective_end_date;
SELECT leer.dys_aftr_end_to_dflt_num,
leer.enrt_perd_end_dt_rl,
leer.enrt_perd_strt_dt_rl,
leer.enrt_perd_end_dt_cd,
leer.enrt_perd_strt_dt_cd,
leer.addl_procg_dys_num,
petc.enrt_typ_cycl_cd,
leer.lee_rsn_id,
leer.cls_enrt_dt_to_use_cd,
leer.effective_start_date,
leer.effective_end_date ,
leer.enrt_perd_strt_days,
leer.enrt_perd_end_days,
/* bug 2746865*/
leer.enrt_perd_det_ovrlp_bckdt_cd
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 l_lf_evt_ocrd_dt 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 = p_pgm_id
AND petc.enrt_typ_cycl_cd = 'L'
AND petc.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN petc.effective_start_date
AND petc.effective_end_date;
SELECT bpf.plip_id,
bpf.dflt_flag,
bpf.enrt_cd,
bpf.enrt_rl,
bpf.enrt_mthd_cd,
bpf.auto_enrt_mthd_rl,
bpf.alws_unrstrctd_enrt_flag,
bpf.effective_start_date,
bpf.effective_end_date,
bpf.ordr_num
FROM ben_plip_f bpf
WHERE bpf.pl_id = l_pl_id
AND bpf.pgm_id = p_pgm_id
AND bpf.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN bpf.effective_start_date
AND bpf.effective_end_date;
SELECT ptip.ptip_id,
ptip.enrt_cd,
ptip.enrt_rl,
ptip.enrt_mthd_cd,
ptip.auto_enrt_mthd_rl,
ptip.effective_start_date,
ptip.effective_end_date,
ptip.ordr_num
FROM ben_ptip_f ptip
WHERE ptip.pl_typ_id = l_pl_typ_id
AND ptip.pgm_id = p_pgm_id
AND ptip.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN ptip.effective_start_date
AND ptip.effective_end_date;
SELECT NULL
FROM ben_oipl_f cop
WHERE cop.pl_id = l_pl_id
AND business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN cop.effective_start_date
AND cop.effective_end_date;
SELECT '4',
plip.pgm_id,
oipl.ordr_num,
plip.bnft_or_option_rstrctn_cd,
lbr.cvg_incr_r_decr_only_cd,
lbr.mx_cvg_mlt_incr_num,
lbr.mx_cvg_mlt_incr_wcf_num
FROM ben_oipl_f oipl, ben_ler_bnft_rstrn_f lbr, ben_plip_f plip
WHERE plip.plip_id = l_plip_id
AND plip.business_group_id = p_business_group_id
AND plip.bnft_or_option_rstrctn_cd = 'OPT'
AND l_lf_evt_ocrd_dt BETWEEN plip.effective_start_date
AND plip.effective_end_date
AND oipl.oipl_id = p_oipl_id
AND oipl.pl_id = plip.pl_id
AND oipl.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN oipl.effective_start_date
AND oipl.effective_end_date
AND lbr.plip_id = plip.plip_id
AND lbr.ler_id = p_ler_id
AND lbr.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN lbr.effective_start_date
AND lbr.effective_end_date
UNION ALL
SELECT '3',
TO_NUMBER(NULL),
oipl.ordr_num,
pl.bnft_or_option_rstrctn_cd,
lbr.cvg_incr_r_decr_only_cd,
lbr.mx_cvg_mlt_incr_num,
lbr.mx_cvg_mlt_incr_wcf_num
FROM ben_oipl_f oipl, ben_ler_bnft_rstrn_f lbr, ben_pl_f pl
WHERE pl.pl_id = l_pl_id
AND pl.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN pl.effective_start_date
AND pl.effective_end_date
AND oipl.oipl_id = p_oipl_id
AND oipl.pl_id = pl.pl_id
AND oipl.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN oipl.effective_start_date
AND oipl.effective_end_date
AND lbr.pl_id = pl.pl_id
AND lbr.plip_id IS NULL
AND lbr.ler_id = p_ler_id
AND pl.bnft_or_option_rstrctn_cd = 'OPT'
AND lbr.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN lbr.effective_start_date
AND lbr.effective_end_date;
SELECT '2',
plip.pgm_id,
oipl.ordr_num,
plip.bnft_or_option_rstrctn_cd,
plip.cvg_incr_r_decr_only_cd,
plip.mx_cvg_mlt_incr_num,
plip.mx_cvg_mlt_incr_wcf_num
FROM ben_oipl_f oipl, ben_plip_f plip
WHERE plip.plip_id = l_plip_id
AND plip.bnft_or_option_rstrctn_cd = 'OPT'
AND plip.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN plip.effective_start_date
AND plip.effective_end_date
AND oipl.oipl_id = p_oipl_id
AND oipl.pl_id = plip.pl_id
AND oipl.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN oipl.effective_start_date
AND oipl.effective_end_date
UNION ALL
SELECT '1',
TO_NUMBER(NULL),
oipl.ordr_num,
pl.bnft_or_option_rstrctn_cd,
pl.cvg_incr_r_decr_only_cd,
pl.mx_cvg_mlt_incr_num,
pl.mx_cvg_mlt_incr_wcf_num
FROM ben_oipl_f oipl, ben_pl_f pl
WHERE pl.pl_id = l_pl_id
AND pl.bnft_or_option_rstrctn_cd = 'OPT'
AND pl.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN pl.effective_start_date
AND pl.effective_end_date
AND oipl.oipl_id = p_oipl_id
AND oipl.pl_id = pl.pl_id
AND oipl.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN oipl.effective_start_date
AND oipl.effective_end_date;
SELECT enrd_oipl.ordr_num
FROM ben_prtt_enrt_rslt_f pen, ben_oipl_f enrd_oipl
WHERE
-- get result for plan if exists
pen.person_id = p_person_id
AND pen.business_group_id = p_business_group_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.sspndd_flag = 'N'
AND pen.effective_end_date = hr_api.g_eot
AND pen.enrt_cvg_thru_dt >= l_lf_evt_ocrd_dt_1
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND pen.pl_id = l_pl_id
-- get enrolled oipl
AND enrd_oipl.oipl_id = pen.oipl_id
AND enrd_oipl.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN enrd_oipl.effective_start_date
AND enrd_oipl.effective_end_date;
SELECT enrd_oipl.ordr_num
FROM ben_prtt_enrt_rslt_f pen,
ben_elig_cvrd_dpnt_f pdp,
ben_oipl_f enrd_oipl
WHERE pdp.dpnt_person_id = p_person_id
AND pdp.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt_1 BETWEEN pdp.cvg_strt_dt AND pdp.cvg_thru_dt
AND pdp.business_group_id = p_business_group_id
AND pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
-- get result for plan if exists
AND pen.business_group_id = p_business_group_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.sspndd_flag = 'N'
AND pen.effective_end_date = hr_api.g_eot
AND pen.enrt_cvg_thru_dt >= l_lf_evt_ocrd_dt_1
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND pen.pl_id = l_pl_id
-- get enrolled oipl
AND enrd_oipl.oipl_id = pen.oipl_id
AND enrd_oipl.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN enrd_oipl.effective_start_date
AND enrd_oipl.effective_end_date;
SELECT MIN(cop.ordr_num),
MAX(cop.ordr_num)
FROM ben_oipl_f cop
WHERE cop.pl_id = l_pl_id
AND l_lf_evt_ocrd_dt BETWEEN cop.effective_start_date
AND cop.effective_end_date
AND cop.business_group_id = p_business_group_id;
select 'Y'
from ben_pl_regn_f prg,
ben_regn_f regn
where prg.pl_id=l_pl_id
and p_effective_date between
prg.effective_start_date and prg.effective_end_date
and prg.business_group_id=p_business_group_id
and regn.regn_id=prg.regn_id
and regn.name in ('IRC Section 125','IRC Section 129')
and p_effective_date between
regn.effective_start_date and regn.effective_end_date
and regn.business_group_id=p_business_group_id
;
select 'Y'
from ben_pgm_f pgm
where pgm_id=p_id and
business_group_id=p_business_group_id and
p_ed between effective_start_date and effective_end_date
;
select 'Y'
from ben_plip_f plip
where plip_id=p_id and
business_group_id=p_business_group_id and
p_ed between effective_start_date and effective_end_date
;
select 'Y'
from ben_ptip_f ptip
where ptip_id=p_id and
business_group_id=p_business_group_id and
p_ed between effective_start_date and effective_end_date
;
select 'Y'
from ben_oipl_f oipl
where oipl_id=p_id and
business_group_id=p_business_group_id and
p_ed between effective_start_date and effective_end_date
;
select 'Y'
from ben_opt_f opt
where opt_id=p_id and
business_group_id=p_business_group_id and
p_ed between effective_start_date and effective_end_date
;
select 'Y'
from ben_pl_f plan
where pl_id=p_id and
business_group_id=p_business_group_id and
p_ed between effective_start_date and effective_end_date
;
select 'Y'
from ben_pl_typ_f pl_typ
where pl_typ_id=p_id and
business_group_id=p_business_group_id and
p_ed between effective_start_date and effective_end_date
;
select 'Y'
from ben_lee_rsn_f lee_rsn
where lee_rsn_id=p_id and
business_group_id=p_business_group_id and
p_ed between effective_start_date and effective_end_date
;
select 'Y'
from ben_ler_f ler
where ler_id=p_id and
business_group_id=p_business_group_id and
p_ed between effective_start_date and effective_end_date
;
select min(effective_start_date)
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = l_prtt_enrt_rslt_id;
select oipl2.ordr_num
from ben_oipl_f oipl,
ben_oipl_f oipl2
where oipl.oipl_id = p_oipl_id
and oipl.pl_id = oipl2.pl_id
and oipl2.oipl_stat_cd = 'A'
and oipl.business_group_id = p_business_group_id
and l_lf_evt_ocrd_dt BETWEEN oipl.effective_start_date
AND oipl.effective_end_date
and oipl2.business_group_id = p_business_group_id
and l_lf_evt_ocrd_dt BETWEEN oipl2.effective_start_date
AND oipl2.effective_end_date
order by 1;
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,
pel.pil_elctbl_chc_popl_id
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_cwb_hrchy hrc
where pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
and epe.elig_per_elctbl_chc_id = cv_emp_epe_id
and hrc.emp_pil_elctbl_chc_popl_id(+) = pel.pil_elctbl_chc_popl_id;
ben_manage_unres_life_events.update_elig_per_elctbl_choice
(
p_elig_per_elctbl_chc_id => l_epe_exists,
p_business_group_id => p_business_group_id,
p_auto_enrt_flag => NVL(l_rec_auto_enrt_flag,'N'),
p_per_in_ler_id => l_per_in_ler_id,
p_yr_perd_id => l_yr_perd_id,
p_pl_id => l_pl_id,
p_pl_typ_id => l_pl_typ_id,
p_oipl_id => p_oipl_id,
p_pgm_id => p_pgm_id,
p_pgm_typ_cd => l_pgm_rec.pgm_typ_cd,
p_must_enrl_anthr_pl_id => l_must_enrl_anthr_pl_id,
p_plip_id => l_plip_id,
p_ptip_id => l_ptip_id,
p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id,
p_comp_lvl_cd => l_comp_lvl_cd,
p_enrt_cvg_strt_dt_cd => l_rec_enrt_cvg_strt_dt_cd,
p_enrt_perd_end_dt => l_rec_enrt_perd_end_dt,
p_enrt_perd_strt_dt => l_rec_enrt_perd_strt_dt,
p_enrt_cvg_strt_dt_rl => l_rec_enrt_cvg_strt_dt_rl,
p_roll_crs_flag => 'N',
p_ctfn_rqd_flag => NVL(l_ctfn_rqd_flag,'N'),
p_crntly_enrd_flag => NVL(l_rec_crntly_enrd_flag,'N'),
p_dflt_flag => NVL(l_dflt_flag,'N'),
p_elctbl_flag => NVL(l_rec_elctbl_flag,'N'),
p_mndtry_flag => NVL(l_rec_mndtry_flag,'N'),
p_dflt_enrt_dt => l_rec_dflt_asnmt_dt,
p_dpnt_cvg_strt_dt_cd => NULL,
p_dpnt_cvg_strt_dt_rl => NULL,
p_enrt_cvg_strt_dt => l_rec_enrt_cvg_strt_dt,
p_alws_dpnt_dsgn_flag => 'N',
p_erlst_deenrt_dt => l_rec_erlst_deenrt_dt,
p_procg_end_dt => l_rec_procg_end_dt,
p_pl_ordr_num => l_plan_rec.ordr_num,
p_plip_ordr_num => l_plip_ordr_num,
p_ptip_ordr_num => l_ptip_ordr_num,
p_oipl_ordr_num => l_oipl_rec.ordr_num,
--p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date,
p_enrt_perd_id => l_rec_enrt_perd_id,
p_lee_rsn_id => l_rec_lee_rsn_id,
p_cls_enrt_dt_to_use_cd => l_rec_cls_enrt_dt_to_use_cd,
p_uom => l_rec_uom,
p_acty_ref_perd_cd => l_rec_acty_ref_perd_cd,
p_cryfwd_elig_dpnt_cd => l_reinstt_cd,
p_ws_mgr_id => l_ws_mgr_id,
p_elig_flag => NVL(l_elig_flag,'Y'),
p_assignment_id => l_assignment_id ,
p_fonm_cvg_strt_dt => l_fonm_cvg_strt_dt,
p_inelig_rsn_cd => l_inelg_rsn_cd);
p_program_update_date => SYSDATE,
p_enrt_perd_id => l_rec_enrt_perd_id,
p_lee_rsn_id => l_rec_lee_rsn_id,
p_cls_enrt_dt_to_use_cd => l_rec_cls_enrt_dt_to_use_cd,
p_uom => l_rec_uom,
p_acty_ref_perd_cd => l_rec_acty_ref_perd_cd,
p_cryfwd_elig_dpnt_cd => l_reinstt_cd,
-- added for cwb
p_ws_mgr_id => l_ws_mgr_id,
p_elig_flag => NVL(l_elig_flag,'Y'),
p_assignment_id => l_assignment_id ,
p_fonm_cvg_strt_dt => l_fonm_cvg_strt_dt,
p_inelig_rsn_cd => l_inelg_rsn_cd); -- 2650247
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);
ben_manage_unres_life_events.update_elig_per_elctbl_choice
(p_elig_per_elctbl_chc_id => nvl(l_epe_exists,l_choice_exists_flag),
p_business_group_id => p_business_group_id,
p_auto_enrt_flag => 'N',
p_per_in_ler_id => l_per_in_ler_id,
p_yr_perd_id => l_yr_perd_id,
p_pl_id => l_pl_id,
p_pl_typ_id => l_pl_typ_id,
p_oipl_id => NULL, -- this is the kicker
p_pgm_id => p_pgm_id,
p_pgm_typ_cd => l_pgm_rec.pgm_typ_cd,
p_must_enrl_anthr_pl_id => NULL,
p_plip_id => l_plip_id,
p_ptip_id => l_ptip_id,
p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id_2, -- NULL was before
p_comp_lvl_cd => 'PLAN',
p_enrt_cvg_strt_dt_cd => l_rec_enrt_cvg_strt_dt_cd,
p_enrt_perd_end_dt => l_rec_enrt_perd_end_dt,
p_enrt_perd_strt_dt => l_rec_enrt_perd_strt_dt,
p_enrt_cvg_strt_dt_rl => l_rec_enrt_cvg_strt_dt_rl,
p_roll_crs_flag => 'N',
p_ctfn_rqd_flag => NVL(l_ctfn_rqd_flag,'N'),
p_crntly_enrd_flag => 'N',
p_dflt_flag => 'N',
p_elctbl_flag => 'N',
p_mndtry_flag => NVL(l_rec_mndtry_flag,'N'),
p_dflt_enrt_dt => NULL,
p_dpnt_cvg_strt_dt_cd => NULL,
p_dpnt_cvg_strt_dt_rl => NULL,
p_enrt_cvg_strt_dt => l_rec_enrt_cvg_strt_dt,
p_alws_dpnt_dsgn_flag => 'N',
p_erlst_deenrt_dt => l_rec_erlst_deenrt_dt,
p_procg_end_dt => l_rec_procg_end_dt,
p_pl_ordr_num => l_plan_rec.ordr_num,
p_plip_ordr_num => l_plip_ordr_num,
p_ptip_ordr_num => l_ptip_ordr_num,
p_oipl_ordr_num => l_oipl_rec.ordr_num,
p_effective_date => p_effective_date,
p_enrt_perd_id => l_rec_enrt_perd_id,
p_lee_rsn_id => l_rec_lee_rsn_id,
p_cls_enrt_dt_to_use_cd => l_rec_cls_enrt_dt_to_use_cd,
p_uom => l_rec_uom,
p_acty_ref_perd_cd => l_rec_acty_ref_perd_cd,
p_cryfwd_elig_dpnt_cd => l_reinstt_cd,
p_fonm_cvg_strt_dt => l_fonm_cvg_strt_dt,
p_inelig_rsn_cd => l_inelg_rsn_cd);
p_program_update_date => SYSDATE,
p_enrt_perd_id => l_rec_enrt_perd_id,
p_lee_rsn_id => l_rec_lee_rsn_id,
p_cls_enrt_dt_to_use_cd => l_rec_cls_enrt_dt_to_use_cd,
p_uom => l_rec_uom,
p_acty_ref_perd_cd => l_rec_acty_ref_perd_cd,
p_cryfwd_elig_dpnt_cd => l_reinstt_cd,
p_fonm_cvg_strt_dt => l_fonm_cvg_strt_dt,
p_inelig_rsn_cd => l_inelg_rsn_cd); -- 2650247
SELECT 'Y'
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.person_id = p_person_id
AND pen.business_group_id = p_business_group_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
-- AND pen.sspndd_flag = 'N' --CFW
AND (pen.sspndd_flag = 'N' --CFW
OR (pen.sspndd_flag = 'Y' and
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
)
AND pen.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt <= pen.enrt_cvg_thru_dt
AND l_lf_evt_ocrd_dt >= pen.enrt_cvg_strt_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND p_pl_id = pen.pl_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NULL));
SELECT 'Y'
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.person_id = p_person_id
AND pen.business_group_id = p_business_group_id
-- AND pen.sspndd_flag = 'N' --CFW
AND (pen.sspndd_flag = 'N' --CFW
OR (pen.sspndd_flag = 'Y' and
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
)
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt <= pen.enrt_cvg_thru_dt
AND l_lf_evt_ocrd_dt >= pen.enrt_cvg_strt_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND p_oipl_id = pen.oipl_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NULL));
SELECT 'Y'
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.person_id = p_person_id
AND pen.business_group_id = p_business_group_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
-- AND pen.sspndd_flag = 'N'
AND (pen.sspndd_flag = 'N' --CFW
OR (pen.sspndd_flag = 'Y' and
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
)
AND pen.effective_end_date = hr_api.g_eot
AND
-- nvl(p_lf_evt_ocrd_dt,p_effective_date) between
-- pen.effective_start_date and pen.effective_end_date and
l_lf_evt_ocrd_dt <= pen.enrt_cvg_thru_dt
AND l_lf_evt_ocrd_dt >= pen.enrt_cvg_strt_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND p_ptip_id = pen.ptip_id;
SELECT loc.region_2
FROM hr_locations_all loc, per_all_assignments_f asg
WHERE loc.location_id = asg.location_id
AND asg.person_id = p_person_id
and asg.assignment_type <> 'C'
AND asg.primary_flag = 'Y'
AND l_lf_evt_ocrd_dt BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.business_group_id = p_business_group_id;
SELECT asg.assignment_id,
asg.organization_id
FROM per_all_assignments_f asg
WHERE asg.person_id = p_person_id
and asg.assignment_type <> 'C'
AND asg.primary_flag = decode(p_run_mode, 'I',asg.primary_flag, 'Y') -- iRec
AND l_lf_evt_ocrd_dt BETWEEN asg.effective_start_date
AND asg.effective_end_date;
SELECT pyp.yr_perd_id,
pyp.popl_yr_perd_id,
yp.start_date,
yp.end_date
FROM ben_popl_yr_perd pyp, ben_yr_perd yp
WHERE pyp.pl_id = p_pl_id
AND pyp.business_group_id = p_business_group_id
AND pyp.ordr_num = p_popl_yr_perd_ordr_num
AND yp.business_group_id = p_business_group_id
AND pyp.yr_perd_id = yp.yr_perd_id;
SELECT '1',
ldc.ler_chg_dpnt_cvg_cd,
ldc.ler_chg_dpnt_cvg_rl
FROM ben_ler_chg_dpnt_cvg_f ldc
WHERE ldc.ler_id = p_ler_id
AND ldc.business_group_id = p_business_group_id
AND ldc.pl_id = p_pl_id
AND l_lf_evt_ocrd_dt BETWEEN ldc.effective_start_date
AND ldc.effective_end_date
AND ldc.ler_chg_dpnt_cvg_cd IS NOT NULL
UNION ALL
SELECT '2',
ldc.ler_chg_dpnt_cvg_cd,
ldc.ler_chg_dpnt_cvg_rl
FROM ben_ler_chg_dpnt_cvg_f ldc
WHERE ldc.ler_id = p_ler_id
AND ldc.business_group_id = p_business_group_id
AND ldc.ptip_id = p_ptip_id
AND l_lf_evt_ocrd_dt BETWEEN ldc.effective_start_date
AND ldc.effective_end_date
AND ldc.ler_chg_dpnt_cvg_cd IS NOT NULL
UNION ALL
SELECT '3',
ldc.ler_chg_dpnt_cvg_cd,
ldc.ler_chg_dpnt_cvg_rl
FROM ben_ler_chg_dpnt_cvg_f ldc
WHERE ldc.ler_id = p_ler_id
AND ldc.business_group_id = p_business_group_id
AND ldc.pgm_id = p_pgm_id
AND l_lf_evt_ocrd_dt BETWEEN ldc.effective_start_date
AND ldc.effective_end_date
AND ldc.ler_chg_dpnt_cvg_cd IS NOT NULL;
SELECT leo.dflt_enrt_cd,
leo.dflt_enrt_rl
FROM ben_ler_chg_oipl_enrt_f leo
WHERE p_oipl_id = leo.oipl_id
AND p_ler_id = leo.ler_id
AND p_effective_date BETWEEN leo.effective_start_date
AND leo.effective_end_date;
SELECT len.dflt_enrt_cd,
len.dflt_enrt_rl
FROM ben_ler_chg_pl_nip_enrt_f len
WHERE p_pl_id = len.pl_id
AND p_ler_id = len.ler_id
AND p_effective_date BETWEEN len.effective_start_date
AND len.effective_end_date;
SELECT lep.dflt_enrt_cd,
lep.dflt_enrt_rl
FROM ben_ler_chg_plip_enrt_f lep
WHERE p_plip_id = lep.plip_id
AND p_ler_id = lep.ler_id
AND p_effective_date BETWEEN lep.effective_start_date
AND lep.effective_end_date;
SELECT lep.dflt_enrt_cd,
lep.dflt_enrt_rl
FROM ben_ler_chg_ptip_enrt_f lep
WHERE p_ptip_id = lep.ptip_id
AND p_ler_id = lep.ler_id
AND p_effective_date BETWEEN lep.effective_start_date
AND lep.effective_end_date;
SELECT lep.dflt_enrt_cd,
lep.dflt_enrt_rl
FROM ben_ler_chg_pgm_enrt_f lep
WHERE p_pgm_id = lep.pgm_id
AND p_ler_id = lep.ler_id
AND p_effective_date BETWEEN lep.effective_start_date
AND lep.effective_end_date;
SELECT pln.nip_dflt_enrt_cd,
pln.nip_dflt_enrt_det_rl
FROM ben_pl_f pln
WHERE p_pl_id = pln.pl_id
AND p_effective_date BETWEEN pln.effective_start_date
AND pln.effective_end_date;
SELECT plp.dflt_enrt_cd,
plp.dflt_enrt_det_rl
FROM ben_plip_f plp
WHERE p_plip_id = plp.plip_id
AND p_effective_date BETWEEN plp.effective_start_date
AND plp.effective_end_date;
SELECT ptp.dflt_enrt_cd,
ptp.dflt_enrt_det_rl
FROM ben_ptip_f ptp
WHERE p_ptip_id = ptp.ptip_id
AND p_effective_date BETWEEN ptp.effective_start_date
AND ptp.effective_end_date;
SELECT leo.dflt_enrt_cd,
leo.dflt_enrt_rl,
leo.dflt_flag
FROM ben_ler_chg_oipl_enrt_f leo
WHERE p_oipl_id = leo.oipl_id
AND p_ler_id = leo.ler_id
AND p_effective_date BETWEEN leo.effective_start_date
AND leo.effective_end_date;
SELECT len.dflt_enrt_cd,
len.dflt_enrt_rl,
len.dflt_flag
FROM ben_ler_chg_pl_nip_enrt_f len
WHERE p_pl_id = len.pl_id
AND p_ler_id = len.ler_id
AND p_effective_date BETWEEN len.effective_start_date
AND len.effective_end_date;
SELECT lep.dflt_enrt_cd,
lep.dflt_enrt_rl,
lep.dflt_flag
FROM ben_ler_chg_plip_enrt_f lep
WHERE p_plip_id = lep.plip_id
AND p_ler_id = lep.ler_id
AND p_effective_date BETWEEN lep.effective_start_date
AND lep.effective_end_date;
SELECT lep.dflt_enrt_cd,
lep.dflt_enrt_rl
FROM ben_ler_chg_ptip_enrt_f lep
WHERE p_ptip_id = lep.ptip_id
AND p_ler_id = lep.ler_id
AND p_effective_date BETWEEN lep.effective_start_date
AND lep.effective_end_date;
SELECT lep.dflt_enrt_cd,
lep.dflt_enrt_rl
FROM ben_ler_chg_pgm_enrt_f lep
WHERE p_pgm_id = lep.pgm_id
AND p_ler_id = lep.ler_id
AND p_effective_date BETWEEN lep.effective_start_date
AND lep.effective_end_date;
SELECT pln.nip_dflt_enrt_cd,
pln.nip_dflt_enrt_det_rl,
pln.nip_dflt_flag
FROM ben_pl_f pln
WHERE p_pl_id = pln.pl_id
AND p_effective_date BETWEEN pln.effective_start_date
AND pln.effective_end_date;
SELECT plp.dflt_enrt_cd,
plp.dflt_enrt_det_rl
FROM ben_plip_f plp
WHERE p_plip_id = plp.plip_id
AND p_effective_date BETWEEN plp.effective_start_date
AND plp.effective_end_date;
SELECT ptp.dflt_enrt_cd,
ptp.dflt_enrt_det_rl
FROM ben_ptip_f ptp
WHERE p_ptip_id = ptp.ptip_id
AND p_effective_date BETWEEN ptp.effective_start_date
AND ptp.effective_end_date;
SELECT '1',
bo.rqd_perd_enrt_nenrt_uom,
bo.rqd_perd_enrt_nenrt_val,
bo.rqd_perd_enrt_nenrt_rl
FROM ben_oipl_f bo
WHERE bo.oipl_id = p_oipl_id
AND p_effective_date BETWEEN bo.effective_start_date
AND bo.effective_end_date
AND bo.business_group_id = p_business_group_id
AND (
(
bo.rqd_perd_enrt_nenrt_uom IS NOT NULL
AND bo.rqd_perd_enrt_nenrt_val IS NOT NULL)
OR bo.rqd_perd_enrt_nenrt_rl IS NOT NULL)
UNION ALL
SELECT '2',
bo.rqd_perd_enrt_nenrt_uom,
bo.rqd_perd_enrt_nenrt_val,
bo.rqd_perd_enrt_nenrt_rl
FROM ben_opt_f bo
WHERE bo.opt_id = p_opt_id
AND p_effective_date BETWEEN bo.effective_start_date
AND bo.effective_end_date
AND bo.business_group_id = p_business_group_id
AND (
(
bo.rqd_perd_enrt_nenrt_uom IS NOT NULL
AND bo.rqd_perd_enrt_nenrt_val IS NOT NULL)
OR bo.rqd_perd_enrt_nenrt_rl IS NOT NULL)
UNION ALL
SELECT '3',
bp.rqd_perd_enrt_nenrt_uom,
bp.rqd_perd_enrt_nenrt_val,
bp.rqd_perd_enrt_nenrt_rl
FROM ben_pl_f bp
WHERE bp.pl_id = p_pl_id
AND p_effective_date BETWEEN bp.effective_start_date
AND bp.effective_end_date
AND bp.business_group_id = p_business_group_id
AND (
(
bp.rqd_perd_enrt_nenrt_uom IS NOT NULL
AND bp.rqd_perd_enrt_nenrt_val IS NOT NULL)
OR bp.rqd_perd_enrt_nenrt_rl IS NOT NULL)
UNION ALL
SELECT '4',
bp.rqd_perd_enrt_nenrt_tm_uom,
bp.rqd_perd_enrt_nenrt_val,
bp.rqd_perd_enrt_nenrt_rl
FROM ben_ptip_f bp
WHERE bp.ptip_id = p_ptip_id
AND p_effective_date BETWEEN bp.effective_start_date
AND bp.effective_end_date
AND bp.business_group_id = p_business_group_id
AND (
(
bp.rqd_perd_enrt_nenrt_tm_uom IS NOT NULL
AND bp.rqd_perd_enrt_nenrt_val IS NOT NULL)
OR bp.rqd_perd_enrt_nenrt_rl IS NOT NULL);
SELECT *
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.person_id = p_person_id
AND pen.business_group_id = p_business_group_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.sspndd_flag = 'N'
AND pen.effective_end_date = hr_api.g_eot
AND l_effective_date_1 < pen.enrt_cvg_thru_dt
AND pen.prtt_enrt_rslt_id <> p_prtt_enrt_rslt_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NOT NULL))
AND pen.comp_lvl_cd NOT IN ('PLANFC', 'PLANIMP')
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND pen.ptip_id = p_ptip_id;
SELECT *
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.person_id = p_person_id
AND pen.business_group_id = p_business_group_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
-- AND pen.sspndd_flag = 'N' --CFW
AND pen.effective_end_date = hr_api.g_eot
AND l_effective_date_1 < pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND pen.prtt_enrt_rslt_id <> p_prtt_enrt_rslt_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NOT NULL))
AND pen.comp_lvl_cd NOT IN ('PLANFC', 'PLANIMP')
AND pen.oipl_id = p_oipl_id;
SELECT *
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.person_id = p_person_id
AND pen.business_group_id = p_business_group_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.sspndd_flag = 'N'
AND pen.effective_end_date = hr_api.g_eot
AND l_effective_date_1 < pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND pen.prtt_enrt_rslt_id <> p_prtt_enrt_rslt_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NOT NULL))
AND pen.comp_lvl_cd NOT IN ('PLANFC', 'PLANIMP')
AND pen.pl_id = p_pl_id;
SELECT pen.*
FROM ben_prtt_enrt_rslt_f pen, ben_pl_f pl, ben_oipl_f oipl
WHERE pen.person_id = p_person_id
AND pen.business_group_id = p_business_group_id
AND pen.effective_end_date = hr_api.g_eot
AND l_effective_date_1 BETWEEN pen.enrt_cvg_strt_dt
AND pen.enrt_cvg_thru_dt
AND pl.pl_id = pen.pl_id
and pen.oipl_id = p_oipl_id /* bug 1527086 */
AND pl.business_group_id = p_business_group_id
AND pl.pl_typ_id = p_pl_typ_id
AND oipl.pl_id = pl.pl_id
AND oipl.opt_id = p_opt_id
AND p_effective_date BETWEEN pl.effective_start_date
AND pl.effective_end_date
AND p_effective_date BETWEEN oipl.effective_start_date
AND oipl.effective_end_date
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND pen.sspndd_flag = 'N'
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND oipl.business_group_id = p_business_group_id
AND pen.prtt_enrt_rslt_id <> p_prtt_enrt_rslt_id
AND (
( pen.pgm_id = p_pgm_id
AND p_pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND p_pgm_id IS NOT NULL))
AND pen.comp_lvl_cd NOT IN ('PLANFC', 'PLANIMP');
procedure update_defaults
(p_run_mode in varchar2
,p_business_group_id in number
,p_effective_date in date
,p_lf_evt_ocrd_dt in date default null
,p_ler_id in number
,p_person_id in number
,p_per_in_ler_id in number
)
IS
--
l_oipl_id_va benutils.g_number_table := benutils.g_number_table();
l_proc VARCHAR2(80) := g_package || '.update_defaults';
select epe.oipl_id,
epe.pl_id,
epe.pgm_id,
epe.ptip_id,
epe.plip_id,
epe.dflt_flag,
epe.ELCTBL_FLAG,
epe.ELIG_PER_ELCTBL_CHC_ID,
epe.OBJECT_VERSION_NUMBER
from ben_elig_per_elctbl_chc epe,
/*
ben_per_in_ler pil,
*/
ben_pl_f pln
where epe.auto_enrt_flag = 'N'
and epe.per_in_ler_id = c_per_in_ler_id
/*
and pil.person_id = p_person_id
and pil.per_in_ler_id = epe.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
*/
and pln.pl_id=epe.pl_id
and c_lf_evt_ocrd_dt
between pln.effective_start_date and pln.effective_end_date
and nvl(pln.imptd_incm_calc_cd,'x') NOT IN ('PRTT', 'DPNT', 'SPS')
and invk_flx_cr_pl_flag='N'
and (epe.oipl_id is not null or
not exists
(select null
from ben_oipl_f oipl
where c_lf_evt_ocrd_dt
between oipl.effective_start_date and oipl.effective_end_date
and oipl.pl_id=epe.pl_id
)
);
SELECT bpf.dflt_flag
FROM ben_plip_f bpf
WHERE bpf.pl_id = p_pl_id
AND bpf.pgm_id = p_pgm_id
AND bpf.business_group_id = p_business_group_id
AND l_lf_evt_ocrd_dt BETWEEN bpf.effective_start_date
AND bpf.effective_end_date;
SELECT asg.assignment_id,
asg.organization_id
FROM per_all_assignments_f asg
WHERE asg.person_id = p_person_id
and asg.assignment_type <> 'C'
AND asg.primary_flag = decode(p_run_mode, 'I',asg.primary_flag,'Y') -- iRec
AND l_lf_evt_ocrd_dt BETWEEN asg.effective_start_date
AND asg.effective_end_date
;
SELECT pen.enrt_cvg_strt_dt,
pen.erlst_deenrt_dt,
pen.prtt_enrt_rslt_id,
pen.enrt_ovridn_flag,
pen.enrt_ovrid_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 pen.sspndd_flag = 'N' --CFW
AND (pen.sspndd_flag = 'N' --CFW
OR (pen.sspndd_flag = 'Y' and
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
)
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt_1 <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND l_plan_rec.pl_id = pen.pl_id
AND (
( pen.pgm_id = l_pgm_rec.pgm_id
AND l_pgm_rec.pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND l_pgm_rec.pgm_id IS NULL));
SELECT pen.enrt_cvg_strt_dt,
pen.erlst_deenrt_dt,
pen.prtt_enrt_rslt_id,
pen.enrt_ovridn_flag,
pen.enrt_ovrid_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 pen.sspndd_flag = 'N' --CFW
AND (pen.sspndd_flag = 'N' --CFW
OR (pen.sspndd_flag = 'Y' and
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
)
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt_1 <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND l_oipl_rec.oipl_id = pen.oipl_id
AND (
( pen.pgm_id = l_pgm_rec.pgm_id
AND l_pgm_rec.pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND l_pgm_rec.pgm_id IS NULL));
SELECT 'Y'
FROM ben_prtt_enrt_rslt_f pen, ben_elig_cvrd_dpnt_f pdp
WHERE pdp.dpnt_person_id = p_person_id
AND pdp.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt_1 BETWEEN pdp.cvg_strt_dt AND pdp.cvg_thru_dt
AND pdp.business_group_id = p_business_group_id
AND pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
AND pen.business_group_id = p_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 l_lf_evt_ocrd_dt_1 <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND l_plan_rec.pl_id = pen.pl_id
AND (
( pen.pgm_id = l_pgm_rec.pgm_id
AND l_pgm_rec.pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND l_pgm_rec.pgm_id IS NULL));
SELECT 'Y'
FROM ben_prtt_enrt_rslt_f pen, ben_elig_cvrd_dpnt_f pdp
WHERE pdp.dpnt_person_id = p_person_id
AND pdp.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt_1 BETWEEN pdp.cvg_strt_dt AND pdp.cvg_thru_dt
AND pdp.business_group_id = p_business_group_id
AND pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
AND pen.business_group_id = p_business_group_id
-- AND pen.sspndd_flag = 'N'
AND (pen.sspndd_flag = 'N' --CFW
OR (pen.sspndd_flag = 'Y' and
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
)
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.effective_end_date = hr_api.g_eot
AND l_lf_evt_ocrd_dt_1 <= pen.enrt_cvg_thru_dt
AND pen.enrt_cvg_strt_dt < pen.effective_end_date
AND l_oipl_rec.oipl_id = pen.oipl_id
AND (
( pen.pgm_id = l_pgm_rec.pgm_id
AND l_pgm_rec.pgm_id IS NOT NULL)
OR ( pen.pgm_id IS NULL
AND l_pgm_rec.pgm_id IS NULL));
SELECT epo.elig_flag
FROM ben_elig_per_f ep, ben_elig_per_opt_f epo, ben_per_in_ler pil
WHERE ep.person_id = p_person_id
AND ep.pl_id = l_plan_rec.pl_id
AND (
( ep.pgm_id = l_pgm_rec.pgm_id
AND l_pgm_rec.pgm_id IS NOT NULL)
OR ( ep.pgm_id IS NULL
AND l_pgm_rec.pgm_id IS NULL))
AND ep.business_group_id = p_business_group_id
AND p_effective_date - 1 BETWEEN ep.effective_start_date
AND ep.effective_end_date
AND ep.elig_per_id = epo.elig_per_id
AND epo.opt_id = l_oipl_rec.opt_id
AND epo.business_group_id = p_business_group_id
AND l_effective_date_1 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') -- found row condition
OR pil.per_in_ler_stat_cd IS NULL); -- outer join condition
SELECT pep.elig_flag
FROM ben_elig_per_f pep, ben_per_in_ler pil
WHERE pep.person_id = p_person_id
AND pep.pl_id = l_plan_rec.pl_id
AND (
( pep.pgm_id = l_pgm_rec.pgm_id
AND l_pgm_rec.pgm_id IS NOT NULL)
OR ( pep.pgm_id IS NULL
AND l_pgm_rec.pgm_id IS NULL))
AND pep.business_group_id = p_business_group_id
AND l_effective_date_1 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 enb.enrt_bnft_id,
enb.object_version_number
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = p_elctbl_chc_id
and enb.dflt_flag = 'N'
and (exists (select null from
ben_enrt_bnft enb2
where enb2.enrt_bnft_id = enb.enrt_bnft_id
and enb2.dflt_val = enb2.val)
or enb.cvg_mlt_cd not in ('FLRNG', 'CLRNG','FLPCLRNG','CLPFLRNG'));
SELECT egd.elig_dpnt_id, egd.object_version_number
FROM ben_elig_dpnt egd, ben_per_in_ler pil
WHERE elig_per_elctbl_chc_id = v_epe_id
AND pil.per_in_ler_id = egd.per_in_ler_id
AND pil.per_in_ler_stat_cd = 'STRTD';
ben_determine_dpnt_eligibility.g_egd_table.DELETE;
p_update_def_elct_flag => l_ELCTBL_FLAG -- 5092244 : this parameter is no longer needed.
);
ben_elig_per_elc_chc_api.update_perf_elig_per_elc_chc
(p_elig_per_elctbl_chc_id => l_epe_id,
p_dflt_flag => l_dflt_flag,
p_elctbl_flag => l_new_elctbl_flag,
p_object_version_number => l_epe_ovn,
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
);
ben_enrt_bnft_api.update_enrt_bnft
(p_enrt_bnft_id => l_enrt_bnft.enrt_bnft_id
,p_dflt_flag => 'Y'
,p_object_version_number => l_enrt_bnft.object_version_number
,p_effective_date => p_effective_date);
END update_defaults;