[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
dpnt_api.update_elig_cvrd_dpnt and
ben_plan_beneficiary_api.delete_plan_
beneficiary
110.14 Sep 25 98 bbulusu Added out nocopy parameters (warnings) to create
_enrollment and in the call to determine_
action_items.
115.11 Oct-02-98 Hugh Dang Remove Rt_strt_dt, Rt_end_dt,
enrld_cvrd_flag and Change BNFT_UOM
to UOM.
* Change Create/Delete enrollment logic.
115.11 Oct-13-98 Hugh Dang Make Enrt_bnft_id as default parameters.
115.12 Oct 13 1998 Hugh Dang Make enrt_cvg_thru_dt default to eot
instead of NULL
115.14 Oct 19 98 bbulusu added p_new_enrt_rslt_ovn in the call to
ben_mng_dpnt_bnf.recycle_dpnt_bnf
115.15 Oct 21 98 Hugh Dang Add business_group_id for all dpnt api.
115.16 Oct 21 98 Siok Tee Call Manage Primary care provider if plan
has not changed but oipl changes.
115.17 Oct 25 98 Jon Carpenter Added call to rate_and_coverage_dates
Removed get_rate_and_cvg_start_end_date
115.18 Oct 26 98 Hugh Dang Add p_per_in_ler_id, p_enrt_perd_id,
p_lee_rsn_id in delete enrollment and
make sure if electable choice is not passed
then above infor. need to passed.
115.19 Oct 29 98 Graham Perry Added in datetrack check for update_
enrollment, switches mode to correction
if needed.
115.20 Oct 29 98 lmcdonal Added new parms to call to delete prtt rt
val.
115.21 Oct 30 98 Hugh Dang Added new parms to call to update prtt
rt val.set post_rslt_flag to multi_row_
edit flag.
115.22 Oct 31 98 bbulusu Modified unhook_bnf procedure to zap all
bnf's when zapping enrt rslt. Siok fixed
the c_pen cursor in delete_enrollment.
115.23 Nov 01 98 Hdang Change logic on per_in_ler_id on delete
enrollment and Change C_pen cursor to
eliminate duplicate Electable choice.
115.24 Nov 02 98 Hdang Add business group id parms for update_prtt
rt_val.
115.26 Nov 04 98 Hdang add l_elig_per_elctbl_chc_id to make the
the per_in_ler for cursor is current.
115.27 Nov 04 98 Hdang Change C_prv cursor to eliminate others
outer join.
115.28 Nov 05 98 Hdang Change update enrollment's compare oipl
from equal to not equal
115.29 Nov 10 98 Hdang - Add call to delete ben_pl_bnf_ctfn api
to remove children before call prtt_actn
items.
- Add logic to handle Saving plan.
Zap enrollments elected today and delete
same day.
115.30 Dec 07 1998 Hdang Add parameter into multi-row edit to
handle last year enrollment but not
default current year.
115.31 Dec 10 1998 Hdang modify the c_prv to eliminate duplicate
row cause by multi-row ben_enrt_rt.
115.32 Dec 11 1998 lmcdonal When deleting rows in multi-row, join to
choice table.
115.33 Dec 15 1998 pxdas Added Code for logging change event
115.34 Dec 16 1998 pxdas Modified Change_exists_in_db procedure
115.35 Dec 16 1998 Hdang Add person_id and pgm_id to limit the fetch
and fix the c_prv cursor.
115.36 Dec 28 1998 Stee Fix cursor to zap beneficiaries in delete
enrollment.
115.37 Dec 28 1998 Hdang Remove per_in_ler check and pass per_in_ler
for all dependent procedure.
115.38 Dec 28 1998 Hdang Remove per_in_ler_id from update_prtt_rt_val.
115.39 Dec 29 1998 pxdas Modified Create_Change_Log procedure
115.40 Dec 29 1998 lmcdonal Because p_suspend_flag is an out, not an
in/out we have to always set it. It is
usually set when calling the action_item
proc, but when that proc is not called, we
must set the flag.
115.41 Jan 01 1999 pxdas Modified effective date when calling
Create_Change_Log procedure
115.42 Jan 04 1999 Hdang C_pil%notfound check.
115.43 Jan 12 1999 maagrawa p_acty_base_rt_id removed from call to
delete_prtt_rt_val.
115.44 Jan 21 1999 Hdang Avoid de-enrolled any enrollmente which has
the earliest de-enrollment date has not been
reached.
115.45 Jan 26 1999 Hdang * Remove mandatory check in delete enrollment.
* Change SVG_PL_FLAG to ENRT_PL_OPT_FLAG for
Create_enrollment to determine if
"Determine action item" need to be called.
115.46 Jan 30 1999 Yrathman Add no_lngr_elig_flag column
Modify delete_enrollment to assign cvg end date
1 day prior to new cvg start date, if found.
Modify multi_rows_edit to delete enrollments
no longer eligible for if elections were made
for program or plan type.
115.47 Feb 04 1999 jcarpent Added ended_per_in_ler_id to update_prv.
115.48 Feb 18 1999 Hdang Fixed cursor for remove old enrollments
which pick up all plan not in program.
115.49 Feb 24 1999 Yrathman Added bnft_ordr_num, prtt_enrt_rslt_stat_cd column
115.50 Mar 03 1999 jcarpent Removed dbms_output.put_lines
115.51 Mar 04 1999 jcarpent Populate p_prtt_enrt_interim_id out nocopy parm
115.52 Mar 10 1999 T Mathers
115.53 Mar 29 1999 T Hayden Added calls to ben_ext_chlg,
Added parameter p_source,
Removed old way of writing to chg log.
115.54 Apr 15 1999 mhoyes Un datetracked per in ler
115.55 Apr 16 1999 jcarpent Removed update to choice of crntly_enrd.
115.56 Apr 24 1999 lmcdonal prtt-rt-val has a status code now. Added
override condition to multi row c1 cursors.
prtt_enrt_rslt also has status code.
115.57 May 02 1999 maagrawa Added condition in multi row c1 cursors,
to exclude flex and imputed income plans.
115.58 May 03 1999 TGUY Added condition in multi to check for overall
min/max cvg across ptip.
115.59 May 10, 1999 lmcdonal Add void_enrollment procedure.
Changes to delete_enrollment:
1.Modify c_prv cursor so that it gets
rates even if tied to a bnft, not a chc.
2.Remove c_rt cursor and it's use.
3.Case 1 no longer zaps rows but instead
marks the rows as voided.
4.Since only case 3 and 4 delete, removed
the use of the delete_flag.
5.on insert, do not load choice fk to rslt
if rslt is void or backed out.
115.60 May 13, 1999 jcarpent Check ('VOIDD', 'BCKDT') for pil stat cd
115.61 May 19, 1999 lmcdonal Added parm to create_enrollment to trigger
which save point to set and rollback.
Also changed c_interim_enrt per Jon.
115.62 May 20, 1999 jcarpent Changed c_interim_enrt p_pen_id=>l_pen_id
115.63 May 25, 1999 maagrawa Fixed c_prv cursor to fetch enrt_rt
records for the current choice, if
it exists.
115.64 Jun 03, 1999 tmathers Leapfrog of 115.60 incorporating cursor
change from 115.63
115.65 Jun 03, 1999 tmathers Leapfrog of 115.63
115.66 Jun 04 1999 lmcdonal added comp_lvl_cd.
115.67 Jun 14 1999 tguy fixed cursor c_prtt_total_bnft_amt.
115.68 Jun 14 1999 tguy fixed cursor c_prtt_total_bnft_amt to
look at table and not a view.
115.69 Jul 1 1999 tguy added prtt_prem_f creating, update and
deleting. Also deals with deleting child
records.
115.70 Jul 2 1999 stee Add an edit for COBRA to not allow a
person to be covered in the same
plan as a dependent.
115.71 Jul 7 1999 jcarpent Add pil_id to most ben_prtt_prem_api calls
115.72 Jul 9 1999 jcarpent Added checks for backed out nocopy pil
115.73 Jul 14 1999 jcarpent close c_check_cvrd_in_plan moved to fix
invalid cursor error.
115.74 Jul 14 1999 stee Fix delete_enrollment to check for a
correctly check for a null per_in_ler id.
bug #1885.
115.75 Jul 23 1999 tguy fixed creating/update/delete prtt_prem_f
issues.
115.76 Jul 28 1999 jcarpent Commented out nocopy code in create_enrollment
which checked for update condition, will
no longer do an update, always a create.
115.77 Aug 06 1999 isen Added new and old values for extract change log
115.78 Aug 10, 1999 lmcdonal Leapfrog version 75 with fix listed in 79.
115.79 Aug 10, 1999 lmcdonal Modify delete_enrollment, case 2. It was trying
to call update_prtt_prem/by_mo in delete mode.
Since rslt is being updated for case2, the
prem process will pick up the change, no need to
do anything here. Bug 3061.
115.80 Aug 10, 1999 lmcdonal uncomment exit, remove ctrl-M's.
115.81 Aug 23, 1999 maagrawa add premium_warning(lmcdonal)
In Delete-result, remove call to delete
prtt-ctfns because they are deleted as part
of call to delete-prtt-actions.(lmcdonal)
Made Changes related to breaking of
dependents table. (maagrawa)
115.82 Aug 23, 1999 shdas added pl_ordr_num,plip_ordr_num,
ptip_ordr_num, oipl_ordr_num.
115.83 Sep 02, 1999 maagrawa Added HIPAA communications.
115.84 Sep 29, 1999 isen Modified calls to log_benefit_chg
115.85 Oct 02 ,1999 stee Change COBRA program type selection
to use 'COBRA%'instead of 'COBRA'
115.86 Oct 13 ,1999 lmcdonal Attempt to copy dpnts more freely - not
just if new rslt is written over old result.
Call recycle_dpnts in multi-row when non-
elected results are about to be deleted.
115.87 Oct 20, 1999 lmcdonal If the result being created is the
dummy flex or imputed income plan, do not
create ledgers (don't call accumulate pools).
115.88 Oct 29, 1999 lmcdonal Edit check the datetrack mode before updating
premiums in step 72.
115.89 Oct 29, 1999 maagrawa By-pass the erlst_deenrt_dt check in delete
enrollment, when calling from beninelg.
115.90 12-Nov-99 lmcdonal Better debugging messages.
115.91 30-Nov-99 jcarpent - replace datetrack strings with globals
update_override was spelled with 1 r. b3846
- Pass enrt_bnft_id to void enrollment.
115.92 01-Dec-99 lmcdonal When automatically de-enrolling prtt from
results that are choices but the prtt did
not choose them, only deenroll from choices
with elctbl_flag = 'Y'.
115.93 03-Dec-99 jcarpent - Changed c_prv cursors to eliminate dups
115.94 16-Dec-99 jcarpent - Create enrt - call action items more often
- Fix acrs_ptip to actually check ptip enrts
115.95 20-Dec-99 maagrawa Do not call manage_enrt_bnft when called
from election_information (benelinf).
115.96 30-Dec-99 jcarpent - Changed strtd to not in voidd bckdt n c_epe
Fixed 91902 message name so error shows.
115.97 11-Jan-00 jcarpent - Changed how erlst_deenrt_dt is checked.
115.98 13-Jan-00 maagrawa - Removed HIPAA Communications. Now called
from close enrollment.
115.99 18-Jan-00 shdas Added calls to remove_bnft_prvdd_ldgr in
first case of delete enrollment.
115.100 19-Jan-00 lmcdonal Bug 1132739. Cases where a date-track delete
was being done on a row where the eed of the
row = the eff dt of the dt-delete.
115.101 25-Jan-00 thayden - Changed existing calls to Change event log,
and added new calls to it, mostly in the
delete and void enrollment areas.
115.102 27-Jan-00 thayden - Pass end cvg thru date when creating chg log.
115.103 08-Feb-00 lmcdonal Bug 1166174. We need to re-calc certain
premiums after save of enrollment.
115.104 08-Feb-00 lmcdonal Bug 1169607 - chg to delete-enrollment
so that rows will be end-dated.
115.105 08-Feb-00 maagrawa Delete the interim coverage when the
suspended result is deleted.(1173011)
115.106 11-Feb-00 maagrawa Do not issue the savepoint delete_enrollment
in procedure delete_enrollment when calling
itself to delete interim coverage.
115.107 17-Feb-00 jcarpent - Fixed unhook dpnt bug 4715
- Fix result not found error in recalc_prem
115.108 17-Feb-00 thayden - Fix date passed into log_benefit_chg.
115.109 25-Feb-00 jcarpent - If delete_enrollment called from benuneai
don't delete interim.
115.110 26-Feb-00 maagrawa - Call action item routine from
update_enrollment. (1211553)
- Added parameters dpnt_actn_warning and
bnf_actn_warning to update_enrollment.
115.111 28-Feb-00 maagrawa - Delete interim enrollment only when called
from enrollment forms and from
election_information in delete_enrollment.
115.112 29-Feb-00 gperry Fixed caching problem when calling elig
dpnt api since business group id wasn't
being passed.
115.113 01-Mar-00 stee Added check for backed out nocopy result in
COBRA edit.
115.114 02-Mar-00 bbulusu Incorrect dt mode being
passed to update_prtt_prem
115.115 03-Mar-00 shdas modify determine_dpnt_cvg_dt_cd(4280)
115.116 04-Mar-00 pbodla - Bug 4822 :Check for min/max across ptip
is moved, called after delete enrollment.
115.117 05-Mar-00 jcarpent - Bug 4783 :changed c_prv2 cursor.
115.118 10-Mar-00 jcarpent - Bug 4839 :handle create of prtt_prem
115.119 31-Mar-00 maagrawa - Datetrack delete premium rows if they are
not end-dated in delete_enrollment. (4924)
115.120 05-Apr-00 lmcdonal Bug 1253007. Using globals for epe, pil,
pel, pen, enb, asg data for performance.
115.121 05-Apr-00 shdas Bug 4444--changed get_election_date to
consider per_in_ler_id while updating
enrollment result.
115.122 06-Apr-00 stee Remove per_in_ler_id from get_election_date
as enrollment results are voided when
a person is found newly ineligible.
115.123 07-Apr-00 lmcdonal Call to clear_pen.
115.124 11-Apr-00 shdas changed get_election_date to add 2 out nocopy parameters
to decide when to void an enrollment(4444).
115.125 14-Apr-00 jcarpent Refresh pen cache after action items calls
bug 5083
115.126 19-Apr-00 maagrawa Do not delete the interim coverage when it
is used by some other result also.(1274214)
115.127 24-Apr-00 gperry Fixed Internal Bug 4924.
115.128 25-Apr-00 maagrawa Fidelity P1(1278255): Cursor c_prv2 fixed.
Delete rates which are not end-dated
i.e. rate end date is end of time (eot).
115.129 26-Apr-00 jcarpent Fixed dt_mode for update of elig_cvrd_dpnt
in unhook_dpnts. (4938)
115.130 01-May-00 shdas added effective date adjustment in delete enrolment
when coverage has not started and source is benelinf(5129)
115.131 02-May-00 jcarpent Removed check for pel.elcns_made_dt or
pel.dflt_asnd_dt from no_lngr_elig_pnip_c.
From Clenr order of calls was
multi_rows_edit, post_results..., then
post enrt so dflt_asnd_dt was not yet set.
115.132 02-May-00 jcarpent bug # for above change is 5020.
115.133 12-May-00 lmcdonal Leapfrog of 128 with fix in 134 for
Fido P1 1299007.
115.134 12-May-00 lmcdonal Added actl_prem_id to c_ppe cursor.
115.135 13-May-00 lmcdonal Bug 1298802: re-open dpnt records for result
more often.
115.136 15-May-00 jcarpent Recycle dependents when ptip enrollment was
ended by benmngle. (4981/1259094)
115.137 14-jun-00 jcarpent p2 bug 1311768, handle 1 prior rt_end_dt.
based on 115.133.
115.138 14-jun-00 jcarpent Leapfrog: based on 115.136 with changes from 137.
115.139 28-jun-00 bbulusu Fixed cursor c_ptip_enrollment_info so that
it ignores the enrt_rslt row just created.
Fix for bug 4981.
115.140 14-jul-00 kmahendr Bug#5123 - Date Track end date of place holder plan for imputed income
when the prtt becomes ineligible after eligible need not be done.
Added one more elsif clause in delete_enrollment procedure to handle
this kind of situation
115.141 19-jul-00 gperry Fixed WWBUG 1349363.
Now pass in less of life event occurred
date-1 or effective date -1.
Changed remove_cert_action_items.
115.142 20-jul-00 kmahendr Effective date is to be passed if effective date -1 >
life event occurred - related to version -115.141
115.143 11-aug-00 gperry Fixed call to ben_determine_date.
Was passing cvg_end_dt_cd and
cvg_strt_dt_rl. Should have passed
cvg_end_dt_rl.
Fix for WWBUG 1375481.
115.144 06-Sep-00 jcarpent 1269016. Fixed handling of future dated
covered dependents when deleting an enrt.
115.145 14-Sep-00 jcarpent 1394044. Object version number issue.
also undo some of fix from prev version
so that don't delete rows for voided pils.
115.146 21-Sep-00 maagrawa 1406914. Do not decrease the effective_date
in remove_cert_action_items, if it is
less than the effective_start_date.
115.147 13-Oct-00 pbodla bug : 1418754 : added call to
chk_max_num_dpnt_for_pen after call to
update_elig_cvrd_dpnt.
115.148 25-Oct-00 bbulusu Bug fix 1471158. Don't zap elig_cvrd_dpnt
if cvg is already ended.
115.149 08-NOV-00 vputtiga Fixed bug 1485814. Added procedure
update_person_type_usages
115.150 19-nov-00 tilak mesage 91902 changed
pen cursor in delete enrollemt changed to get
plan and option name
ECVG_threu date is validated instead of effctived date
with erlst_deenrt_dt to deenroll
115.151 05-Jan-01 maagrawa Added parameters enrt_cvg_thru_dt and p_mode
to delete_enrollment to handle special
cases when the cvg_thru_dt is entered by
the user.
115.152 08-feb-01 tilak cursor for OPT/PTIP/PL created to validate
deentrolment allowed bug : 1620161
115.153 19-feb-01 tilak flex credit amout changes is not affecting
on next enrollment
cursor c_flex and c_bpl is creatd
to update the ledger
115.154 21-Feb-01 maagrawa In delete_enrollment, pass the coverage
end date to rate_and_coverage_dates
procedure when it is enterable by the user.
(1647095).
115.155 23-Feb-01 ikasire fix the '1 prior' date code in
delete_enrollment procedure by
adding 'WL' cases and changing the cursor
used for it (1633284,1558809,1584238)
115.156 15-Mar-01 kmahendr Bug#1684498 - added enrt_cvg_thru_dt condition
in the cursor c_prtt_total_bnft_amt in procedure
multi_rows_edit
115.157 23-Mar-01 maagrawa Call to ben_env_object.init added to
delete_enrollment.
115.158 26-Apr-01 maagrawa Performance changes.
Bypass dependent,beneficiary,flex credit,
premium logic based on plan design.
115.159 30-Apr-01 maagrawa Call ben_env_object.init to initialize
cache variables.
115.160 04-May-01 ikasire 1712890 Added the validation conditions for
attribute columns in UPDATE routine
115.161 08-May-01 jcarpent Bug 158783. Reset l_ppe.prtt_prem_id before
fetch since this is used instead of %found
115.162 17-May-01 maagrawa Changed the calls to ben_global_enrt.
115.164 21-May-01 kmahendr Merged version 163 and 162 as 163 was a
leapfrog version on 153
115.165 22-May-01 maagrawa Fixed bug introduced by performance changes
for premiums.
115.167 29-May-01 kmahendr Version 165 brought forward as version 166 was
a leapfrog version of 156 and fixes in 160
115.168 01-jun-01 tilak bug : 1186192 unhook_bnf is changed to end date
usage , unhhok_bnf is called from update
115.169 14-Jul-01 kmahendr Non-recurring element entries are not removed when
deenrolling from comp. objects - added cursor c_prv3
115.170 16-jul-01 kmahendr Change history for 115.169 entered
115.171 16-jul-01 shdas added p_enrt_cvg_end_dt to calc_dpnt_cvg_dt
115.172 17-Aug-01 kmahendr Bug#1939522 - for unhook_dpnt and unhook_bnf, per_in_ler
is passed null from delete_enrollment when delete_enro
is called from ben_newly_ineligible in selection mode
115.173 28-Aug-01 kmahendr cursor c_abr added to check non-recurring element entries
as unrestricted per_in_ler_id will always be same
115.174 17-Oct-01 kmahendr Bug#2038814 - new cursor c_prv4 added in the delete_enroll
ment procedure
115.175 02-Nov-01 kmahendr Changed paramater value from p_datetrack_mode to l_date
115.176 09-Nov-01 pbodla Bug 2093859 : zap the enrollment data if
coverage is not started yet. Case 3 and 4
( Mahendran and Manish have knowledge of the issue
before changing please consult one )
115.177 12-Nov-01 kmahendr Bug-2093859 - conc_request_id in self_service is given 0
- benmngle mode is checked with in clause for 0, -1
115.178 05-dec-01 dschwart/ - WWBUG#1646442:
bburns - added block of variable declarations in
create_enrolment
- replaced c_ppe in create_enrolment
- added c_pel and processing in create_enrolment
- added processing to get correct date/update mode
115.179 31-Jan-02 kmahendr -Bug#2209048-if delete_enrollment is called from beninelg
delete_mode is assigned 'DELETE'.
115.181 01-Apr-02 shdas reopen cvrd dpnt record only if he is still eligible.
changed c_egd cursor in update_enrollment to add
filtering on dpnt_inelig_flag.
115.182 23-May-02 ikasire Bug 2200139 Override Enhancements there may be
future enrollments needs to be deleted. To handle this
if the pen esd is >= p eff date, then we are passing
esd + 1 for p eff date from multi row edit.
115.183 29-May-02 ikasire 1.Bug 2386000 If the delete enrollment is called from
benuneai, pass this to p_called_from parameter of
unhook_dpnt.
2. When elig_per_elctbl_chc_id is null, we need to pass
lee_rsn_id and enrt_perd_id also along with pgm,pl,oipl
to ben_determine_date.rate_and_coverage_dates call.
115.184 25-Jun-02 tjesumic bug:2479616 for the 1 prior rate end date code, the date is
calcualted on coverage start date, that was changed to
calculate on rate start date
115.185 05-Jul-02 ikasire bug:2479616 fixed a typo in the 1 prior rate if condition
115.186 29-Aug-02 ikasire Bug 2537720 fix for the effective date in suspend
enrollment update case.
115.187 10-Sep-02 kmahendr Bug#2545915 - c_prv cursor in void_enrollment modified.
115.188 28-Sep-02 ikasire Bug 2600087 cursor C1 is not selecting the future
enrollements.
115.189 29-Sep-02 bmanyam Bug 2597005 - Changed the dt-mode to 'correction'
before calling 'update_elig_cvrd_dpnt'
115.190 30-sep-02 kmahendr Bug#2602124 - cursor c1 modified to not to pick the ones
with electable choice having result ids.
115.191 03-Oct -02 bmanyam Bug 2597005 - Reversed the changes made in 115.189 version
115.192 15-oct-02 tjesumic Bug 2546259 - Delete_enrolment Delete(ZAP) the result if
Coverage Started in the Future Date. If the current LE is
backedout , deleted result is required to restore the previous
LE. This fix Void the enrollment result instead of deleting
result of the future dated coverage
115.193 15-Oct-02 ikasire Bug 2627078 added the cursor to update the epe with
the current result id when the interim records are deleted
115.194 09-Nov-02 tjesumic Bug 2546259 - Suspend_flag control removed
115.195 14-Nov-02 kmahendr Bug#2625060 - in delete_enrollment procedure, activity base rt id is not
passed when calling update_prtt_rt_val api.
115.196 28-Nov-02 lakrish Bug 2519378, Set tokens for error message
BEN_91711_ENRT_RSLT_NOT_FND
115.197 07-Jan-03 ikasire Bug 2739965 delete enrollment for unrestricted.
added p_ler_typ_cd parameter to get_ben_pen_upd_dt_mode
procedure
115.199 15-Feb-03 pbodla DEBUG : hr_utility.set_location calls wrpped
around if statements.
115.200 14-Mar-03 kmahendr Bug#2785410 - code added in void_enrollment proc.
115.201 14-Mar-03 ikasire Bug2847110 and 2852703 1 Prior Code fixes
115.202 24-Mar-03 ikasire Bug 2739965 Added a warning message while deleting the future
rate changes.
115.203 10-Apr-03 kmahendr Bug#2893826 - cursor c_prem changed to look for per_in_ler_stat
cd strd
115.204 17-Jul-03 ikasire Bug3051674 fixes for the multi-row edits to happen
as of the lf_evt_ocrd_dt
115.205 28-Aug-03 tjesumic bug # 3086161 when the open LE reprocessed on the same day of
the previous LE process date. the previous LE result are
updated with open per in ler id. if the open LE is backedout
then the result of previous LE are lost. this is fixed
by copying the result of LE to backop table and copy
back to result when the open is backed out
bepenapi modified to copy to the backup table in update_prtt_reslt
and delete_enrollment to update the result from backup tabe
Related changes are in benbolfe.pkb
115.206 16-Sep-03 vsethi Bug 3123698 - Added tokens to messages BEN_92179_BELOW_MIN_AMOUNT and
BEN_92180_ABOVE_MAX_AMOUNT
115.207 26-Sep-03 kmahendr Added void_rate procedure and codes in delete_enroll - GHR fix
115.208 07-Oct-03 tjesumic if the dpnt enrolled in same type of plan the dpnt are recyled
even if alws_dpnt_dsgn_flag is 'N'
115.209 10-Oct-03 tjesumic even if the old result_id not passed the dpnt carry forward for
alws_dpnt_dsgn_flag is 'N' # 3175382
115.210 13-Nov-03 ikasire Bug 3256056 We want remove the penid from the enb record
while zaping or voiding the pen
115.211 14-Nov-03 mmudigon Bug 3222057. Step 155 Bypass call to
ben_cobj_cache.get_pgm_dets for plnip
115.212 15-Nov-03 kmahendr Bug#3260564 - assign rt_end_dt at the time delete_enrollment
for 1 prior codes based on new enrollment.
115.213 29-Dec-03 rpgupta Bug#3327224 - Make dpnt cvg end dt independent of prtt cvg end dt
115.214 08-Jan-04 vvprabhu Bug 1620171 - Changes to date format in message 91902
115.215 05-Feb-04 ikasire Bug 3412562 void_rate
115.216 11-Mar-04 bmanyam BUG: 3398353 - END-DATE/VOID Dependent Designation records
(BEN_ELIG_CVRD_DPNT_F) in void_enrollment procedure
for Unrestricted Enrollment.
Bug: 1620171 - For msg 91902, used fnd_date.date_to_chardate
instead of a fixed format-mask.
115.217 07=Apr-04 tjesumic FONM changes added
115.218 19-apr-04 bmanyam Bug# 3573173 : In c_prem cursor, added
condition NVL(ecr.enrt_bnft_id, -1) = NVL(p_enrt_bnft_id, -1) to
make sure the ben_enrt_prem record refers to the correct ben_enrt_bnft_f
record while creating ben_prtt_prem.
115.219 21-Apr-04 kmahendr Bug#3568529 - ben_env_object.init is called only if the
object was not initialised.
115.220 04-May-04 bmanyam Bug: 3574168 - In void_enrollment, delete_enrollment and
unhook_dpnt procedures, added code to delete the assigned
Primary-Care-Providers for the enrollment.
115.221 13-May-04 bmanyam Bug# 3574168: Removed delete_prmry_care_prvdr
from unhook_dpnt, as "update_elig_cvrd_dpnt"
procedure will do it.
115.222 17-May-04 ikasire Bug# 3631117 fixes for APP-PAY-07155 and APP-BEN-91711 errors
115.223 18-May-04 ikasire Bug# 3631117 change null to to_number(null)
115.224 18-May-04 nhunur Bug# 3626176
115.225 21-May-04 bmanyam Bug# 3631067 - changed cursors c_pcp and c_pcp_future
to fetch records based on cvg_thru_date and NOT p_effective_date
115.226 16-Jun-04 bmanyam Bug# 3657077 : In procedure determine_dpnt_cvg_dt_cd,
check if DPNT_DSGN_CD is specified at plan-level.
If it is specified then dependent designation level is PL.
This overrides PGM and PTIP levels
115.230 Leapfroged version of 115.228 by Amit Parekh
115.231 28-Jun-04 kmahendr Bug#3714789 - Effective date is derived from benmngle
parameter for Selection mode in delete_enrollment
115.232 30-Jun-04 abparekh Bug 3733213 : In delete_enrollment while setting new Coverage End Date
for CVG_END_DT_CD starting with W or LW, check for NULL value of
l_new_enrt_cvg_strt_dt and not whether cursor C_NEW_CVG_STRT_DT
fetched any row since the cursor uses aggregate function.
115.233 06-Jul-04 ikasire Bug 3695005 consider suspended enrollment also in the multi
row edits in evaluating coverage across plan types.
115.234 23-Jul-04 kmahendr Bug#3772143 - added parameter - p_include_erl
115.234 29-Jul-04 tjesumic Bug#3259447 - nvl added to recalc_premium for p_enrt_cvg_strt_dt
115.236 09-Aug-04 nhunur Bug - 3797391 - call unhook_dpnt / unhook_bnf .
115.237 23-Aug-04 mmudigon CFW. changes to delete_enrollment
2534391 :NEED TO LEAVE ACTION ITEMS
115.238 01-Sep-04 abparekh Bug# 3866580 : Call determine_action_items only for savings plan enrollment
at plan level (and not option in plan level). For rest of the plans
call for enrollment in plan as well as option in plan level
115.239 07-sep-04 mmudigon CFW. Continued
115.240 09-sep-04 mmudigon Bug fixes 3865108 and 3882130
115.241 30-sep-04 tmathers Overloaded multi_rows_edit as
not all libraries have the new parameter
p_include_erl.392661
115.242 07-oct-04 ssarkar BUG: 3904792: MAINLINE FIX FOR 3894240: WHEN UPDATING A SAVINGS
PLAN RATE SYSTEM IS END DATE
115.243 12-Oct-04 ikasire Bug 3939785 Need to handled the override thru date properly
115.244 19-Oct-04 abparekh Bug 3958064 Need to handled the override thru date properly only for Overriden records
115.245 12-Jan-05 lakrish 4114012, Added SS wrapper procedure delete_enrollment_w
115.246 18-Jan-05 ikasire Bug 4064635 CF SUSP and INTERIM 'W' and 'WL' cases
115.247 21-jan-05 nhunur Added c_prvdel cursor
115.248 07-Feb-05 tjesumic backout_future_coverage function removed from benelinf.pkb. future coverages are
copied into backup table in delete_enrollmet. multo_rows_edit take care of voiding
# 4118315
115.248 09-Mar-05 mmudigon Bug 4157759 . Added var l_prtt_rt_val_stat_cd in
proc delete_enrollment.
115.249 05-Apr-05 bmanyam Bug 4268494: Added '1 Prior' logic for
LDPPOEFD - 1 Prior or End of Pay Period On or After Effective Date
LDPPFEFD - 1 Prior or End of Pay Period after Effective Date
115.250 13-Apr-05 ikasire Added new parameter to manage_enrt_bnft call
115.251 15-Apr-05 kmahendr Added a condition for 1 prior codes in delete_enrollment
115.252 18-May-05 ikasire Bug 4262697 don't delete the results of previous life events if there
are no electable choices in the current life event of the comp
object.
115.253 23-may-05 ssarkar bug 3123698 : added cursor c_pl_typ_names to proc multi_row_edit.
115.255 26-May-05 vborkar Bug 4387247 : In wrapper method exception
handlers changes made to avoid null errors
on SS pages
115.256 01-Jun-05 vborkar Bug 4387247 : Modifications to take care of
application exceptions.
115.257 04-Jul-05 rgajula Bug 4431511 Added checks in get_ben_pen_upd_dt_modes to ensure update_override is passed as
effective datetrack mode if the desired date track mode is
delete next change in case of unrestricted enrollments
115.258 20-Jul-05 ikasire Bug 4463836 added new parameter p_called_from_ss
115.259 27-Jul-05 kmahendr Bug#4509665 - modified the cursor c_pl_typ_names
and increased the length of variable l_pl_typ_name
115.260 29-jul-05 tjesumic BUG 4510798 : fonm variable intialised on delete_enrollment
115.261 24-Aug-05 swjain Bug 4520785 : Updated cursor c_old_dpnt in procedure unhook_dpnt
115.262 24-Aug-05 ikasire Indented delete_enrollment procedure to improve productivity
in bug resolutions.
115.263 06-Oct-05 bmanyam Bug 4642299 : Added 'LODBEWM','ODBEWM' too for 1 Prior logic.
115.264 07-oct-05 ssarkar Bug 4616225 : Call to pl/oipl cache is set to nvl(life_event_dt/effective_date).
proc delete_enrollment modified for same .Also ben_cobj_cache.get_pl_dets/get_oipl_dets
are called after ben_global_enrt.get_pil .
115.265 17-Oct-05 vborkar BUG 4663971 : In delete_enrollment, called update_prtt_enrt_result
instead of delete_prtt_enrt_result for datetrack mode other than zap.
Also if eff date equals PEN start date then called update_prtt_enrt_result
in correction mode, otherwise in update mode.
115.266 18-Oct-05 abparekh Bug 4662362 : Unhook dependents / beneficiary in all case
115.267 31-Oct-05 ikasire Bug 4690334 : multi_rows_edit from overview page
115.268 31-Oct-05 ikasire Bug 4710937 cursor c_old_dpnt regression from 4520785
115.269 02-Nov-05 ikasire Bug 4709601 multi_rows_edit not required for CFW call
115.270 10-Nov-05 abparekh Bug 4723828 Correctly handle nullifying of benefit amount
115.271 18-Nov-05 bmanyam Bug 4739922: In delete_enrollment, Case 4:
Backout the Result/ (do not VOIDD).
115.272 18-Nov-05 bmanyam If enrollment api's are called from
concurrent manager for conversion or ivr kind of interfaces, call raises error.
benutils.get_batch_parameters
115.273 13-Dec-05 vborkar Bug 4695708 : Made changes to delete_enrollment
and delete_enrollment_w exception handlers
so that error messages are correctly shown
in SS.
115.275 24-Jan-06 vborkar 4962138 : In remove_cert_action_items, when p_source is null
changed datetrack mode from DELETE to ZAP,
in order to avoid display of action item(s) in PUI
for de-enrolled selections.
115.276 31-Jan-06 kmahendr Bug#4967063 - modified cursor c_new_cvg_strt in
delete_enrollment
115.277 02-Feb-06 bmanyam BUG 4919591 - Changed c_rslt_ptip, c_rslt_pl, c_rslt_oipl
to check Earliest De-enrollment date in case of Unrestricted.
115.278 17-Feb-06 kmahendr Bug#5032364 - modified cursor c_old_corr_pen in
delete_enrollment
115.279 28-Feb-06 rgajula Bug 4770367 : Added the NVL condition so as to allow the user to update
the attributes with NULL values if the user wishes to do so
115.280 03-Mar-06 rtagarra Bug 5018328 : Added condition in exception block for delete_enrollment so
that it will take care for this specific exception condition.
115.281 03-Mar-06 kmahendr Added check for automatic enrollment in
delete_enrollment - enh
115.282 13-Mar-06 kmahendr Bug#5082245 - savings plan is determined by flag
than opt type code
115.283 16_mar-06 rtagarra Bug 5018328 :Added a check for exception l_fnd_message_exception
in exception block for delete_enrollment procedure.
115.285 10-Apr-06 kmahendr Bug#5126242 - added a parameter to delete_prmry_care
115.286 19Apr-06 rtagarra Added cursor c_crntly_enrd_flag and c_crntly_enrd_flag_unres for Bug#5018328 which checks
whether the deenrollment should be allowed in subsequesnt LE depending on earliest deenrollment date
both for LE and Unrestricted.
115.286 02-May-06 ikasired Bug 5102337
115.291 13-jun-06 ssarkar bug 5287988 - leap -frog of 115.287 as ver 288 to 290 r reverted
115.292 28-jun-06 ssarkar bug 5347887 - need to populate rplcs_sspndd_rslt_id while end-dating sspndd pen.
115.293 29-jun-06 kmahendra/ bug 5363388 - marking pen as 'BCKDT' when cvg st dt > cvg end dt
ssarkar
115.294 10-Jul-06 rgajula Bug 5368060
Dont void the rate if the PEN record is Backed-Out
If the PEN record is end-dated in the subsequent life event dont void the rate
115.295 03-aug-06 ssarkar bug 5417132 - by passing chk of enrt method for flex and impute
115.297 04-Sep-06 rgajula Bug 5499809 - Void the enrollment and rate if the delete enrollment is being performed in the same life event and that life event is not unrestricted LE
115.298 27-Sep-06 stee Bug 5391554 - Adjust overlapping rates.
115.299 10/4/2006 gsehgal Bug 5550679 - void_rate void the rates for all per_in_ler_id whereas it should void the
rate only calling that per_in_ler_id
115.300 12-Oct-06 rtagarra Bug 5567840 - Added check so that error 94596 wont be raised for imputed income plans.
115.301 30-Oct-06 abparekh Bug 5626835 - For delete cases 3/4 : For Enter Value At Enrollment cases,
we need to void rate instead of deleting them.
115.302 01-Nov-06 swjain Bug 5637595 - Added more checks in procedure delete_enrollment so that for open
enrollment cases, if future dated coverages are there in previous
and subsequent LEs, then changing enrollments should not backout the
end dated coverage record.
115.303 22-Nov-06 vborkar Bug 5663280 - Do not 'VOID' carry forwarded enrollments which are end-dated in
current life event
115.304 24-nov-06 rtagarra Bug 5664907 - Application Exceptions for delete_enrollment are handled.
115.305 19-dec-06 ssarkar Bug 5717428
115.306 24-Jan-07 swjain Bug 5739530 - Updated procedure delete_enrollment, update future prvs only if
they haven't been updated previously.
115.307 30-Jan-07 rgajula Bug 5768795 - Moved the code for validation of "Coverage Across Plan Types" from multi_rows_edit
to a seperate procedure chk_coverage_across_plan_types.
115.309 21-feb-07 rgajula Bug 5859714 :Updated the field BEN_PER_IN_LER.BCKT_PER_IN_LER_ID of
the backed out L.E with the per_in_ler_id of the causing L.E
115.310 11-May-07 ikasired Bug 5985777 Backup the future completed action items.
115.311 14-May-06 rtagarra Bug 6000303 : Defer Deenrollment ENH.
115.312 04-Jul-07 swjain Bug 6165501 : Updated cursor c_intm_other_rslt in delete_enrollment procedure
115.313 04-Oct-07 rtagarra Bug 6471236 : void_rate should only be called for the PRV for which void should happen
instead of calling for all PRV's associated with the PEN for entr_val_at_enrt_flag.
115.314 26-Oct-07 swjain Bug 6528876 : Added order by clause in get_election_date
115.315 30-Nov-06 rtagarra Bug 6656136 : While calling multi_rows_edit pass l_lf_evt_ocrd_dt only for Open and for others pass p_effective_date.
115.316 01-Dec-07 sshetty Bug 6641853 : The enrollment is set to correction
when the changes are done within
enrollment period and does not
void.
115.317 14-Dec-07 sallumwa Bug 6683229 : Removed a condition,which is satisfied always because l_other_pen_rec
is not populated before it is used.
115.318 23-Dec-07 sshetty Bug 6641853 : Handled some more conditions for
same bug
115.318 06-Jan-08 sshetty Bug 6641853 : Fixed rate issue
115.319 14-Jan-08 rtagarra Bug 6747807 : Fixed cursor c_check_cvrd_in_plan
115.320 04-APR-08 sshetty Bug 6641853 : Added per_in_ler_id as cursor
param to make sure the correction
is not read as update
115.321 30-apr-08 sallumwa Bug 6925893 : A Rate Gap exists when Rate Start dates Code
is set to First of Next Pay period,
Rate end date is 1 Prior and enrollments
are saved on different dates
crossing pay periods.
115.322 19-may-08 bachakra Bug 6963660: Updated the effective start date of the
corrected result in the back up table to the more
recent date so, that the previous enrollment gets opened up
on backing out the current life event which has two
records attached to it for the same plan. Please refer bug
for test case.
115.325 24-jul-08 bachakra Bug 7206471: Adjusted coverage overlap.
115.326 10-sep-08 sallumwa Bug 7311284 : Modified the condition while setting the effective date in
delete_enrollment procedure.
115.327 12-Jun-08 sallumwa Bug 7133998: Modified the update_enrollment procedure to delete the future
premium records corresponding to the previous Le.
115.328 15-Oct-08 stee Bug 7197868: Remove fix for bug 6963660. Change cursor c_old_corr_pen
to check for effective_start_date.
115.329 07-Nov-08 sallumwa Bug 7458990: Reverted back the fix 5018328,so that the system throws the error
when trying to change the plan before the earliest de-enrol date.
==================================================================================================================
*/
--
-- Package Variables
--
g_package Varchar2(33) := ' ben_PRTT_ENRT_RESULT_api.';
select pea.prtt_enrt_actn_id
,pea.object_version_number
,pea.effective_start_date
,pea.effective_end_date
,pil.lf_evt_ocrd_dt
from ben_prtt_enrt_actn_f pea,
ben_per_in_ler pil
where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pea.business_group_id = p_business_group_id
and p_effective_date
between pea.effective_start_date and pea.effective_end_date
and pil.per_in_ler_id=pea.per_in_ler_id
and pil.business_group_id=pea.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') ;
select min(effective_start_date),
max(effective_end_date)
from ben_prtt_enrt_actn_f
where prtt_enrt_actn_id = p_prtt_enrt_actn_id;
select pea.object_version_number
from ben_prtt_enrt_actn_f pea
where pea.prtt_enrt_actn_id = p_prtt_enrt_actn_id
and p_effective_date between pea.effective_start_date
and pea.effective_end_date ;
select *
from ben_prtt_enrt_actn_f
where prtt_enrt_actn_id = p_prtt_enrt_actn_id
and effective_start_date > p_effective_date
order by effective_start_date desc ;
select *
from ben_prtt_enrt_ctfn_prvdd_f
where prtt_enrt_actn_id = p_prtt_enrt_actn_id
and effective_start_date > p_effective_date
order by effective_start_date desc ;
ben_prtt_enrt_ctfn_prvdd_api.delete_prtt_enrt_ctfn_prvdd
(p_validate => FALSE
,p_prtt_enrt_ctfn_prvdd_id=> l_prtt_ctfn_prvdd.prtt_enrt_ctfn_prvdd_id
,p_effective_start_date => l_prtt_ctfn_prvdd.effective_start_date
,p_effective_end_date => l_prtt_ctfn_prvdd.effective_end_date
,p_object_version_number => l_prtt_ctfn_prvdd.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
);
l_datetrack_mode := hr_api.g_delete;
insert into BEN_LE_CLSN_N_RSTR (
BKUP_TBL_TYP_CD,
BKUP_TBL_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
PRTT_IS_CVRD_FLAG, -- RQD_FLAG
ASSIGNMENT_ID, -- ACTN_TYP_ID
PRTT_ENRT_RSLT_ID,
PER_IN_LER_ID,
BUSINESS_GROUP_ID,
ENRT_CVG_THRU_DT, -- CMPLTD_DT
ENRT_OVRID_THRU_DT, -- DUE_DT
PGM_ID, -- PL_BNF_ID
PTIP_ID, -- ELIG_CVRD_DPNT_ID
LCR_ATTRIBUTE_CATEGORY,
LCR_ATTRIBUTE1,
LCR_ATTRIBUTE2,
LCR_ATTRIBUTE3,
LCR_ATTRIBUTE4,
LCR_ATTRIBUTE5,
LCR_ATTRIBUTE6,
LCR_ATTRIBUTE7,
LCR_ATTRIBUTE8,
LCR_ATTRIBUTE9,
LCR_ATTRIBUTE10,
LCR_ATTRIBUTE11,
LCR_ATTRIBUTE12,
LCR_ATTRIBUTE13,
LCR_ATTRIBUTE14,
LCR_ATTRIBUTE15 ,
LCR_ATTRIBUTE16,
LCR_ATTRIBUTE17,
LCR_ATTRIBUTE18,
LCR_ATTRIBUTE19,
LCR_ATTRIBUTE20,
LCR_ATTRIBUTE21,
LCR_ATTRIBUTE22,
LCR_ATTRIBUTE23,
LCR_ATTRIBUTE24,
LCR_ATTRIBUTE25,
LCR_ATTRIBUTE26,
LCR_ATTRIBUTE27,
LCR_ATTRIBUTE28,
LCR_ATTRIBUTE29,
LCR_ATTRIBUTE30,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
PER_IN_LER_ENDED_ID)
values (
'BEN_PRTT_ENRT_ACTN_F_UPD'
,l_pea.PRTT_ENRT_ACTN_ID
,l_pea.EFFECTIVE_START_DATE
,l_pea.EFFECTIVE_END_DATE
,l_pea.RQD_FLAG
,l_pea.ACTN_TYP_ID
,l_pea.PRTT_ENRT_RSLT_ID
,P_PER_IN_LER_ID
,l_pea.BUSINESS_GROUP_ID
,l_pea.CMPLTD_DT
,l_pea.DUE_DT
,l_pea.PL_BNF_ID
,l_pea.ELIG_CVRD_DPNT_ID
,l_pea.PEA_ATTRIBUTE_CATEGORY
,l_pea.PEA_ATTRIBUTE1
,l_pea.PEA_ATTRIBUTE2
,l_pea.PEA_ATTRIBUTE3
,l_pea.PEA_ATTRIBUTE4
,l_pea.PEA_ATTRIBUTE5
,l_pea.PEA_ATTRIBUTE6
,l_pea.PEA_ATTRIBUTE7
,l_pea.PEA_ATTRIBUTE8
,l_pea.PEA_ATTRIBUTE9
,l_pea.PEA_ATTRIBUTE10
,l_pea.PEA_ATTRIBUTE11
,l_pea.PEA_ATTRIBUTE12
,l_pea.PEA_ATTRIBUTE13
,l_pea.PEA_ATTRIBUTE14
,l_pea.PEA_ATTRIBUTE15
,l_pea.PEA_ATTRIBUTE16
,l_pea.PEA_ATTRIBUTE17
,l_pea.PEA_ATTRIBUTE18
,l_pea.PEA_ATTRIBUTE19
,l_pea.PEA_ATTRIBUTE20
,l_pea.PEA_ATTRIBUTE21
,l_pea.PEA_ATTRIBUTE22
,l_pea.PEA_ATTRIBUTE23
,l_pea.PEA_ATTRIBUTE24
,l_pea.PEA_ATTRIBUTE25
,l_pea.PEA_ATTRIBUTE26
,l_pea.PEA_ATTRIBUTE27
,l_pea.PEA_ATTRIBUTE28
,l_pea.PEA_ATTRIBUTE29
,l_pea.PEA_ATTRIBUTE30
,l_pea.LAST_UPDATE_DATE
,l_pea.LAST_UPDATED_BY
,l_pea.LAST_UPDATE_LOGIN
,l_pea.CREATED_BY
,l_pea.CREATION_DATE
,l_pea.REQUEST_ID
,l_pea.PROGRAM_APPLICATION_ID
,l_pea.PROGRAM_ID
,l_pea.PROGRAM_UPDATE_DATE
,l_pea.OBJECT_VERSION_NUMBER
,p_per_in_ler_ended_id
);
insert into BEN_LE_CLSN_N_RSTR (
BKUP_TBL_TYP_CD,
BKUP_TBL_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
PRTT_IS_CVRD_FLAG, -- ENRT_CTFN_RQD_FLAG
COMP_LVL_CD, -- ENRT_CTFN_TYP_CD
ENRT_CVG_THRU_DT, -- ENRT_CTFN_RECD_DT
PRTT_ENRT_RSLT_ID,
PGM_ID, -- PRTT_ENRT_ACTN_ID
ENRT_OVRID_THRU_DT, -- ENRT_CTFN_DND_DT
BNFT_TYP_CD, -- ENRT_R_BNFT_CTFN_CD
BUSINESS_GROUP_ID,
LCR_ATTRIBUTE_CATEGORY,
LCR_ATTRIBUTE1,
LCR_ATTRIBUTE2,
LCR_ATTRIBUTE3,
LCR_ATTRIBUTE4,
LCR_ATTRIBUTE5,
LCR_ATTRIBUTE6,
LCR_ATTRIBUTE7,
LCR_ATTRIBUTE8,
LCR_ATTRIBUTE9,
LCR_ATTRIBUTE10,
LCR_ATTRIBUTE11,
LCR_ATTRIBUTE12,
LCR_ATTRIBUTE13,
LCR_ATTRIBUTE14,
LCR_ATTRIBUTE15 ,
LCR_ATTRIBUTE16,
LCR_ATTRIBUTE17,
LCR_ATTRIBUTE18,
LCR_ATTRIBUTE19,
LCR_ATTRIBUTE20,
LCR_ATTRIBUTE21,
LCR_ATTRIBUTE22,
LCR_ATTRIBUTE23,
LCR_ATTRIBUTE24,
LCR_ATTRIBUTE25,
LCR_ATTRIBUTE26,
LCR_ATTRIBUTE27,
LCR_ATTRIBUTE28,
LCR_ATTRIBUTE29,
LCR_ATTRIBUTE30,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
PER_IN_LER_ID,
PER_IN_LER_ENDED_ID)
values (
'BEN_PRTT_ENRT_CTFN_PRVDD_F_UPD'
,l_lcs.PRTT_ENRT_CTFN_PRVDD_ID
,l_lcs.EFFECTIVE_START_DATE
,l_lcs.EFFECTIVE_END_DATE
,l_lcs.ENRT_CTFN_RQD_FLAG
,l_lcs.ENRT_CTFN_TYP_CD
,l_lcs.ENRT_CTFN_RECD_DT
,l_lcs.PRTT_ENRT_RSLT_ID
,l_lcs.PRTT_ENRT_ACTN_ID
,l_lcs.ENRT_CTFN_DND_DT
,l_lcs.ENRT_R_BNFT_CTFN_CD
,l_lcs.BUSINESS_GROUP_ID
,l_lcs.PCS_ATTRIBUTE_CATEGORY
,l_lcs.PCS_ATTRIBUTE1
,l_lcs.PCS_ATTRIBUTE2
,l_lcs.PCS_ATTRIBUTE3
,l_lcs.PCS_ATTRIBUTE4
,l_lcs.PCS_ATTRIBUTE5
,l_lcs.PCS_ATTRIBUTE6
,l_lcs.PCS_ATTRIBUTE7
,l_lcs.PCS_ATTRIBUTE8
,l_lcs.PCS_ATTRIBUTE9
,l_lcs.PCS_ATTRIBUTE10
,l_lcs.PCS_ATTRIBUTE11
,l_lcs.PCS_ATTRIBUTE12
,l_lcs.PCS_ATTRIBUTE13
,l_lcs.PCS_ATTRIBUTE14
,l_lcs.PCS_ATTRIBUTE15
,l_lcs.PCS_ATTRIBUTE16
,l_lcs.PCS_ATTRIBUTE17
,l_lcs.PCS_ATTRIBUTE18
,l_lcs.PCS_ATTRIBUTE19
,l_lcs.PCS_ATTRIBUTE20
,l_lcs.PCS_ATTRIBUTE21
,l_lcs.PCS_ATTRIBUTE22
,l_lcs.PCS_ATTRIBUTE23
,l_lcs.PCS_ATTRIBUTE24
,l_lcs.PCS_ATTRIBUTE25
,l_lcs.PCS_ATTRIBUTE26
,l_lcs.PCS_ATTRIBUTE27
,l_lcs.PCS_ATTRIBUTE28
,l_lcs.PCS_ATTRIBUTE29
,l_lcs.PCS_ATTRIBUTE30
,l_lcs.LAST_UPDATE_DATE
,l_lcs.LAST_UPDATED_BY
,l_lcs.LAST_UPDATE_LOGIN
,l_lcs.CREATED_BY
,l_lcs.CREATION_DATE
,l_lcs.REQUEST_ID
,l_lcs.PROGRAM_APPLICATION_ID
,l_lcs.PROGRAM_ID
,l_lcs.PROGRAM_UPDATE_DATE
,l_lcs.OBJECT_VERSION_NUMBER
,p_per_in_ler_id
,p_per_in_ler_ended_id
);
ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
(p_validate => FALSE
,p_prtt_enrt_actn_id => l_bnf_types.prtt_enrt_actn_id
,p_effective_start_date => l_bnf_types.effective_start_date
,p_effective_end_date => l_bnf_types.effective_end_date
,p_object_version_number => l_bnf_types.object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_unsuspend_enrt_flag => 'N'
,p_rslt_object_version_number => l_tmp
,p_business_group_id => p_business_group_id);
p_datetrack_mode = hr_api.g_delete and
nvl(p_source,'benelinf') <> 'benelinf' then
l_datetrack_mode := p_datetrack_mode;
p_datetrack_mode = hr_api.g_delete and
p_source is null then
l_datetrack_mode := hr_api.g_zap;
if (p_datetrack_mode = hr_api.g_delete and
l_bnf_types.effective_end_date > l_effective_date) or
p_datetrack_mode <> hr_api.g_delete then
ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
(p_validate => FALSE
,p_prtt_enrt_actn_id => l_bnf_types.prtt_enrt_actn_id
,p_effective_start_date => l_bnf_types.effective_start_date
,p_effective_end_date => l_bnf_types.effective_end_date
,p_object_version_number => l_bnf_types.object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => l_datetrack_mode -- 4962138
,P_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,P_unsuspend_enrt_flag => 'N'
,P_rslt_object_version_number => l_tmp
,P_business_group_id => p_business_group_id);
l_dt_update boolean;
l_dt_update_override boolean;
l_dt_update_change_insert boolean;
l_dt_delete_next_change boolean;
l_dt_delete boolean;
,p_update => l_dt_update
,p_update_override => l_dt_update_override
,p_update_change_insert => l_dt_update_change_insert
);
and p_desired_datetrack_mode = hr_api.g_delete_next_change) then
ben_pen_shd.find_dt_del_modes
(p_effective_date => p_effective_date
,p_base_key_value => p_base_key_value
,p_delete_next_change => l_dt_delete_next_change
,p_zap => l_dt_zap
,p_delete => l_dt_delete
,p_future_change => l_dt_future_change
);
and p_desired_datetrack_mode = hr_api.g_delete) then
ben_pen_shd.find_dt_del_modes
(p_effective_date => p_effective_date
,p_base_key_value => p_base_key_value
,p_delete_next_change => l_dt_delete_next_change
,p_zap => l_dt_zap
,p_delete => l_dt_delete
,p_future_change => l_dt_future_change
);
if l_dt_update then
if g_debug then
hr_utility.set_location( 'l_dt_update',1234);
if l_dt_update_override then
if g_debug then
hr_utility.set_location( 'l_dt_update_override',1234);
if l_dt_update_change_insert then
if g_debug then
hr_utility.set_location( 'l_dt_update_change_insert',1234);
if l_dt_delete_next_change then
if g_debug then
hr_utility.set_location( 'l_dt_delete_next_change',1234);
if l_dt_delete then
if g_debug then
hr_utility.set_location( 'l_dt_delete',1234);
if (p_desired_datetrack_mode = hr_api.g_update and l_dt_update) then
p_datetrack_allow := hr_api.g_update;
elsif (l_dt_update) then
p_datetrack_allow := hr_api.g_update;
elsif (l_dt_update_override and -- p_ler_typ_cd = 'SCHEDDU' and --Bug 5102337 this applied to LE mode also
p_desired_datetrack_mode = hr_api.g_update) then
p_datetrack_allow := hr_api.g_update_override;
elsif (l_dt_delete_next_change and p_ler_typ_cd = 'SCHEDDU' and
p_desired_datetrack_mode = hr_api.g_delete_next_change) then
p_datetrack_allow := hr_api.g_update_override;
elsif (l_dt_delete and p_ler_typ_cd = 'SCHEDDU' and
p_desired_datetrack_mode = hr_api.g_delete) then
p_datetrack_allow := hr_api.g_update_override;
SELECT null
FROM ben_ext_chg_evt_log a
WHERE a.person_id = p_person_id
AND a.chg_evt_cd = p_chg_evt_cd
AND a.prmtr_01 = to_char(p_pl_id)
AND a.prmtr_02 = to_char(p_oipl_id)
AND trunc(a.chg_eff_dt) = trunc(p_chg_eff_dt);
,p_program_update_date in date default null
,p_object_version_number out nocopy number
,p_per_in_ler_id in number default null
,p_bnft_typ_cd in varchar2 default null
,p_bnft_ordr_num in number default null
,p_prtt_enrt_rslt_stat_cd in varchar2 default null
,p_bnft_nnmntry_uom in varchar2 default null
,p_comp_lvl_cd in varchar2 default null
,p_effective_date in date
,p_multi_row_validate in boolean default TRUE
,p_elig_per_elctbl_chc_id in number
,p_prtt_enrt_rslt_id_o in number
,p_suspend_flag out nocopy varchar2
,p_prtt_enrt_interim_id out nocopy number
,p_datetrack_mode in varchar2
,p_dpnt_actn_warning out nocopy boolean
,p_bnf_actn_warning out nocopy boolean
,p_ctfn_actn_warning out nocopy boolean
,p_enrt_bnft_id in number default NULL
,p_source in varchar2 default null
) is
--
-- Declare cursors and local variables
--
l_chg_evt_cd hr_lookups.lookup_code%type;
l_update varchar2(1) := 'N';
lb_update boolean;
lb_update_override boolean;
lb_update_change_insert boolean;
select pen.prtt_enrt_rslt_id,
pen.pl_id,
pen.oipl_id,
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.prtt_enrt_rslt_stat_cd is null and
pen.sspndd_flag='N' and
pen.prtt_enrt_rslt_id <> l_prtt_enrt_rslt_id and
pen.effective_end_date = hr_api.g_eot and
p_enrt_cvg_strt_dt-1 <=
pen.enrt_cvg_thru_dt and
pen.enrt_cvg_strt_dt < pen.effective_end_date and
p_ptip_id=pen.ptip_id;
select prtt_enrt_rslt_id
,effective_start_date
,enrt_cvg_strt_dt
,enrt_cvg_thru_dt
,pgm_id
,ptip_id
,pl_typ_id
,pl_id
,oipl_id
,object_version_number
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
and p_effective_date between
effective_start_date and effective_end_date;
select ecr.val,
ecr.uom,
ecr.actl_prem_id,
pil.lf_evt_ocrd_dt,
pil.ler_id
from ben_enrt_prem ecr,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe
where ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
and pil.per_in_ler_id = epe.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and NVL(ecr.enrt_bnft_id, -1) = NVL(p_enrt_bnft_id, -1); -- 3573173. Added this condition.
select ppe.prtt_prem_id,
ppe.std_prem_uom,
ppe.std_prem_val,
ppe.actl_prem_id,
ppe.object_version_number
from ben_prtt_prem_f ppe
-- ben_per_in_ler pil
where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and ppe.actl_prem_id = p_actl_prem_id
and p_effective_date between
ppe.effective_start_date and ppe.effective_end_date;
select ppe.prtt_prem_id,
ppe.std_prem_uom,
ppe.std_prem_val,
ppe.actl_prem_id,
ppe.object_version_number
from ben_prtt_prem_f ppe
where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and ppe.actl_prem_id = p_actl_prem_id
and /*p_effective_date*/ p_ppe_dt_to_use between
ppe.effective_start_date and ppe.effective_end_date;
select null
from ben_elig_cvrd_dpnt_f pdp
,ben_prtt_enrt_rslt_f pen
,ben_pgm_f pgm
where pdp.dpnt_person_id = p_person_id
and pdp.business_group_id = p_business_group_id
and p_effective_date
between pdp.effective_start_date and pdp.effective_end_date
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
--and pen.prtt_enrt_rslt_stat_cd not in ('BCKDT', 'VOIDD')
and pen.prtt_enrt_rslt_stat_cd is null
and pdp.cvg_thru_dt = hr_api.g_eot
and pen.pgm_id = p_pgm_id
and pen.pgm_id = pgm.pgm_id
and pen.prtt_enrt_rslt_stat_cd is null
and pgm.pgm_typ_cd like 'COBRA%'
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and pen.pl_id = p_pl_id
and pen.business_group_id = pdp.business_group_id
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and pgm.business_group_id = pen.business_group_id
and p_effective_date between
pgm.effective_start_date and pgm.effective_end_date;
select pel.enrt_perd_id,pel.lee_rsn_id
from ben_pil_elctbl_chc_popl pel
,ben_elig_per_elctbl_chc epe
where pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
and epe.elig_per_elctbl_chc_id = p_elig_pe_elctbl_chc_id;
select ptp.opt_typ_cd
from ben_pl_typ_f ptp
where ptp.pl_typ_id = p_pl_typ_id
and p_effective_date between ptp.effective_start_date
and ptp.effective_end_date;
,p_program_update_date => sysdate
,p_object_version_number => l_object_version_number
,p_per_in_ler_id => p_per_in_ler_id
,p_bnft_typ_cd => p_bnft_typ_cd
,p_bnft_ordr_num => p_bnft_ordr_num
,p_prtt_enrt_rslt_stat_cd => p_prtt_enrt_rslt_stat_cd
,p_bnft_nnmntry_uom => p_bnft_nnmntry_uom
,p_comp_lvl_cd => p_comp_lvl_cd
,p_effective_date => l_effective_date
,p_multi_row_validate => p_multi_row_validate
);
ben_global_enrt.reload_pen -- result globals loaded after insert.
(p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
,p_effective_date => p_effective_date
,p_global_pen_rec => l_global_pen_rec);
ben_ELIG_PER_ELC_CHC_api.update_ELIG_PER_ELC_CHC
(p_validate => FALSE
,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
,p_object_version_number =>
l_global_epe_rec.object_version_number
,p_effective_date => p_effective_date
,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
);
ben_global_enrt.reload_epe -- chc globals re-loaded after update.
(p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
,p_global_epe_rec => l_global_epe_rec);
if (l_update = 'Y' or l_create = 'Y') then
--
for l_prem in c_prem loop
--
l_step := 120;
if l_ppe.prtt_prem_id is not null and l_update = 'Y' then
-- Because the benefit amount could have changed, and the premiums
-- can be based on the benefit amount, re-calc it. It does a recalc
-- if the benefit amount is entered at enrollment.
-- PPE is from prtt-prem. prem is from enrt-prem.
ben_PRTT_PREM_api.recalc_PRTT_PREM
(p_prtt_prem_id => l_ppe.prtt_prem_id
,p_std_prem_uom => l_prem.uom
,p_std_prem_val => l_prem.val -- in/out
,p_actl_prem_id => l_prem.actl_prem_id
,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_ler_id => l_prem.ler_id
,p_lf_evt_ocrd_dt => l_prem.lf_evt_ocrd_dt
,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
,p_enrt_bnft_id => p_enrt_bnft_id
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
-- bof FONM
,p_enrt_cvg_strt_dt => nvl(l_enrt_cvg_strt_dt ,l_ppe_dt_to_use)
,p_rt_strt_dt => l_rt_strt_dt
-- eof FONM
);
p_update => lb_update,
p_update_override => lb_update_override,
p_update_change_insert => lb_update_change_insert);
if lb_update_override then
--
l_ppe_datetrack_mode := hr_api.g_update_override;
elsif lb_update then
--
l_ppe_datetrack_mode := hr_api.g_update;
ben_prtt_prem_api.update_prtt_prem
( p_validate => FALSE
,p_prtt_prem_id => l_ppe.prtt_prem_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_std_prem_uom => l_prem.uom
,p_std_prem_val => l_prem.val
,p_actl_prem_id => l_prem.actl_prem_id
,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_ppe.object_version_number
,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
/* CODE PRIOR TO WWBUG: 1646442
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
*/
/* Start of Changes for WWBUG: 1646442 */
,p_effective_date => l_ppe_dt_to_use
,p_datetrack_mode => l_ppe_datetrack_mode
/* End of Changes for WWBUG: 1646442 */
);
,p_program_update_date => sysdate
/*
CODE PRIOR TO WWBUG: 1646442
,p_effective_date => p_effective_date
*/
/* Start of Changes for WWBUG: 1646442 */
,p_effective_date => l_ppe_dt_to_use
/* End of Changes for WWBUG: 1646442 */
);
,p_program_update_date in date default null
,p_object_version_number out nocopy number
,p_per_in_ler_id in number default null
,p_bnft_typ_cd in varchar2 default null
,p_bnft_ordr_num in number default null
,p_prtt_enrt_rslt_stat_cd in varchar2 default null
,p_bnft_nnmntry_uom in varchar2 default null
,p_comp_lvl_cd in varchar2 default null
,p_effective_date in date
,p_multi_row_validate in boolean default TRUE
) is
--
-- Declare cursors and local variables
--
l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
,p_program_update_date => p_program_update_date
,p_per_in_ler_id => p_per_in_ler_id
,p_bnft_typ_cd => p_bnft_typ_cd
,p_bnft_ordr_num => p_bnft_ordr_num
,p_prtt_enrt_rslt_stat_cd => p_prtt_enrt_rslt_stat_cd
,p_bnft_nnmntry_uom => p_bnft_nnmntry_uom
,p_comp_lvl_cd => p_comp_lvl_cd
,p_effective_date => trunc(p_effective_date)
);
,p_program_update_date => p_program_update_date
,p_object_version_number => l_object_version_number
,p_per_in_ler_id => p_per_in_ler_id
,p_bnft_typ_cd => p_bnft_typ_cd
,p_bnft_ordr_num => p_bnft_ordr_num
,p_prtt_enrt_rslt_stat_cd => p_prtt_enrt_rslt_stat_cd
,p_bnft_nnmntry_uom => p_bnft_nnmntry_uom
,p_comp_lvl_cd => p_comp_lvl_cd
,p_effective_date => trunc(p_effective_date)
);
,p_program_update_date => p_program_update_date
,p_object_version_number => l_object_version_number
,p_per_in_ler_id => p_per_in_ler_id
,p_bnft_typ_cd => p_bnft_typ_cd
,p_bnft_ordr_num => p_bnft_ordr_num
,p_prtt_enrt_rslt_stat_cd => p_prtt_enrt_rslt_stat_cd
,p_bnft_nnmntry_uom => p_bnft_nnmntry_uom
,p_comp_lvl_cd => p_comp_lvl_cd
,p_effective_date => trunc(p_effective_date)
);
procedure update_ENROLLMENT
(p_validate in boolean default false
,p_prtt_enrt_rslt_id in number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_business_group_id in number default hr_api.g_number
,p_oipl_id in number default hr_api.g_number
,p_person_id in number default hr_api.g_number
,p_assignment_id in number default hr_api.g_number
,p_pgm_id in number default hr_api.g_number
,p_pl_id in number default hr_api.g_number
,p_rplcs_sspndd_rslt_id in number default hr_api.g_number
,p_ptip_id in number default hr_api.g_number
,p_pl_typ_id in number default hr_api.g_number
,p_ler_id in number default hr_api.g_number
,p_sspndd_flag in varchar2 default hr_api.g_varchar2
,p_prtt_is_cvrd_flag in varchar2 default hr_api.g_varchar2
,p_enrt_bnft_id in number default NULL
,p_bnft_amt in number default hr_api.g_number
,p_uom in varchar2 default hr_api.g_varchar2
,p_orgnl_enrt_dt in date default hr_api.g_date
,p_enrt_mthd_cd in varchar2 default hr_api.g_varchar2
,p_enrt_ovridn_flag in varchar2 default hr_api.g_varchar2
,p_enrt_ovrid_rsn_cd in varchar2 default hr_api.g_varchar2
,p_erlst_deenrt_dt in date default hr_api.g_date
,p_enrt_cvg_strt_dt in date default hr_api.g_date
,p_enrt_cvg_thru_dt in date default hr_api.g_date
,p_enrt_ovrid_thru_dt in date default hr_api.g_date
,p_pl_ordr_num in number default hr_api.g_number
,p_plip_ordr_num in number default hr_api.g_number
,p_ptip_ordr_num in number default hr_api.g_number
,p_oipl_ordr_num in number default hr_api.g_number
,p_pen_attribute_category in varchar2 default hr_api.g_varchar2
,p_pen_attribute1 in varchar2 default hr_api.g_varchar2
,p_pen_attribute2 in varchar2 default hr_api.g_varchar2
,p_pen_attribute3 in varchar2 default hr_api.g_varchar2
,p_pen_attribute4 in varchar2 default hr_api.g_varchar2
,p_pen_attribute5 in varchar2 default hr_api.g_varchar2
,p_pen_attribute6 in varchar2 default hr_api.g_varchar2
,p_pen_attribute7 in varchar2 default hr_api.g_varchar2
,p_pen_attribute8 in varchar2 default hr_api.g_varchar2
,p_pen_attribute9 in varchar2 default hr_api.g_varchar2
,p_pen_attribute10 in varchar2 default hr_api.g_varchar2
,p_pen_attribute11 in varchar2 default hr_api.g_varchar2
,p_pen_attribute12 in varchar2 default hr_api.g_varchar2
,p_pen_attribute13 in varchar2 default hr_api.g_varchar2
,p_pen_attribute14 in varchar2 default hr_api.g_varchar2
,p_pen_attribute15 in varchar2 default hr_api.g_varchar2
,p_pen_attribute16 in varchar2 default hr_api.g_varchar2
,p_pen_attribute17 in varchar2 default hr_api.g_varchar2
,p_pen_attribute18 in varchar2 default hr_api.g_varchar2
,p_pen_attribute19 in varchar2 default hr_api.g_varchar2
,p_pen_attribute20 in varchar2 default hr_api.g_varchar2
,p_pen_attribute21 in varchar2 default hr_api.g_varchar2
,p_pen_attribute22 in varchar2 default hr_api.g_varchar2
,p_pen_attribute23 in varchar2 default hr_api.g_varchar2
,p_pen_attribute24 in varchar2 default hr_api.g_varchar2
,p_pen_attribute25 in varchar2 default hr_api.g_varchar2
,p_pen_attribute26 in varchar2 default hr_api.g_varchar2
,p_pen_attribute27 in varchar2 default hr_api.g_varchar2
,p_pen_attribute28 in varchar2 default hr_api.g_varchar2
,p_pen_attribute29 in varchar2 default hr_api.g_varchar2
,p_pen_attribute30 in varchar2 default hr_api.g_varchar2
,p_request_id in number default hr_api.g_number
,p_program_application_id in number default hr_api.g_number
,p_program_id in number default hr_api.g_number
,p_program_update_date in date default hr_api.g_date
,p_object_version_number in out nocopy number
,p_per_in_ler_id in number default hr_api.g_number
,p_bnft_typ_cd in varchar2 default hr_api.g_varchar2
,p_bnft_ordr_num in number default hr_api.g_number
,p_prtt_enrt_rslt_stat_cd in varchar2 default hr_api.g_varchar2
,p_bnft_nnmntry_uom in varchar2 default hr_api.g_varchar2
,p_comp_lvl_cd in varchar2 default hr_api.g_varchar2
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_multi_row_validate in boolean default TRUE
,p_suspend_flag out nocopy varchar2
,p_prtt_enrt_interim_id out nocopy number
,p_dpnt_actn_warning out nocopy boolean
,p_bnf_actn_warning out nocopy boolean
,p_ctfn_actn_warning out nocopy boolean
) is
--
-- Declare cursors and local variables
--
l_pl_rec ben_cobj_cache.g_pl_inst_row;
select pen.rplcs_sspndd_rslt_id
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id and
pen.business_group_id = p_business_group_id and
p_effective_date between
pen.effective_start_date and pen.effective_end_date;
select ecr.val,
ecr.uom,
ecr.actl_prem_id,
pil.lf_evt_ocrd_dt,
pil.ler_id,
epe.elig_per_elctbl_chc_id
from ben_enrt_prem ecr,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe
where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
and pil.per_in_ler_id = epe.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD';
select ppe.prtt_prem_id,
ppe.std_prem_uom,
ppe.std_prem_val,
ppe.actl_prem_id,
ppe.object_version_number,
ppe.effective_start_date
from ben_prtt_prem_f ppe,
ben_per_in_ler pil
where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and ppe.actl_prem_id = p_actl_prem_id
/*
CODE PRIOR TO WWBUG: 1646442
and p_effective_date between
*/
/* Start of Changes for WWBUG: 1646442 */
and p_ppe_dt_to_use between
/* End of Changes for WWBUG: 1646442 */
ppe.effective_start_date and ppe.effective_end_date
and pil.per_in_ler_id=ppe.per_in_ler_id
and pil.business_group_id=ppe.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select ppe.*
from ben_prtt_prem_f ppe,
ben_per_in_ler pil
where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and ppe.actl_prem_id = p_actl_prem_id
and p_ppe_dt_to_use <= ppe.effective_start_date
and pil.per_in_ler_id=ppe.per_in_ler_id
and pil.business_group_id=ppe.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select egd.elig_cvrd_dpnt_id,
egd.per_in_ler_id,
pdp.effective_start_date,
pdp.object_version_number
from ben_elig_per_elctbl_chc epe,
ben_elig_dpnt egd,
ben_elig_cvrd_dpnt_f pdp,
ben_per_in_ler egd_pil,
ben_per_in_ler pdp_pil
where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and epe.per_in_ler_id = p_per_in_ler_id
and epe.elig_per_elctbl_chc_id = egd.elig_per_elctbl_chc_id
and egd.elig_cvrd_dpnt_id = pdp.elig_cvrd_dpnt_id
and pdp.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
-- and egd.per_in_ler_id <> pdp.per_in_ler_id
and egd.per_in_ler_id = egd_pil.per_in_ler_id
and egd_pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pdp.per_in_ler_id = pdp_pil.per_in_ler_id
and pdp_pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
and p_effective_date between
pdp.effective_start_date and pdp.effective_end_date
and p_effective_date >= pdp.cvg_strt_dt
and egd.dpnt_inelig_flag = 'N'
-- and p_effective_date between
-- pdp.cvg_strt_dt and pdp.cvg_thru_dt
;
select epe.bnft_prvdr_pool_id,
epe.elig_per_elctbl_chc_id,
epe.prtt_enrt_rslt_id,
epe.business_group_id,
epe.per_in_ler_id,
ecr.enrt_rt_id,
ecr.acty_base_rt_id,
nvl(ecr.dflt_val, ecr.val) val
from ben_elig_per_elctbl_chc epe1,
ben_elig_per_elctbl_chc epe,
ben_enrt_rt ecr
where epe1.prtt_enrt_rslt_id =p_prtt_enrt_rslt_id and
epe1.per_in_ler_id = p_per_in_ler_id and
epe1.business_group_id=p_business_group_id and
epe1.pgm_id = epe.pgm_id and
epe1.per_in_ler_id = epe.per_in_ler_id and
epe.bnft_prvdr_pool_id is not null and
epe.business_group_id=p_business_group_id and
ecr.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id and
ecr.rt_usg_cd = 'FLXCR' and
ecr.business_group_id = p_business_group_id;
select prvdd_val
from ben_bnft_prvdd_ldgr_f bpl,
ben_per_in_ler pil
where bpl.acty_base_rt_id = c_acty_base_rt_id
and bpl.bnft_prvdr_pool_id = c_bnft_prvdr_pool_id
and bpl.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and pil.per_in_ler_id = bpl.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and bpl.business_group_id = p_business_group_id
and p_effective_date between
bpl.effective_start_date and bpl.effective_end_date ;
select prtt_enrt_rslt_id
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.comp_lvl_cd = 'PLANFC'
and epe.business_group_id = p_business_group_id;
select elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and per_in_ler_id = p_per_in_ler_id;
select pel.enrt_perd_id,pel.lee_rsn_id
from ben_pil_elctbl_chc_popl pel
,ben_elig_per_elctbl_chc epe
where pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
and epe.elig_per_elctbl_chc_id = p_elig_pe_elctbl_chc_id;
select ptp.opt_typ_cd
from ben_pl_typ_f ptp
where ptp.pl_typ_id = p_pl_typ_id
and p_effective_date between ptp.effective_start_date
and ptp.effective_end_date;
l_proc varchar2(72); -- := g_package||'update_ENROLLMENT';
l_update boolean;
l_update_override boolean;
l_update_change_insert boolean;
l_proc := g_package||'update_ENROLLMENT';
savepoint update_ENROLLMENT;
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update_override then
--
l_datetrack_mode := hr_api.g_update_override;
elsif l_update then
--
l_datetrack_mode := hr_api.g_update;
ben_PRTT_ENRT_RESULT_API.update_PRTT_ENRT_RESULT
(p_validate => FALSE
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_business_group_id => p_business_group_id
,p_oipl_id => p_oipl_id
,p_person_id => p_person_id
,p_assignment_id => p_assignment_id
,p_pgm_id => p_pgm_id
,p_pl_id => p_pl_id
,p_rplcs_sspndd_rslt_id => p_rplcs_sspndd_rslt_id
,p_ptip_id => p_ptip_id
,p_pl_typ_id => p_pl_typ_id
,p_ler_id => p_ler_id
,p_sspndd_flag => p_sspndd_flag
,p_prtt_is_cvrd_flag => p_prtt_is_cvrd_flag
,p_bnft_amt => p_bnft_amt
,p_uom => p_uom
,p_orgnl_enrt_dt => p_orgnl_enrt_dt
,p_enrt_mthd_cd => p_enrt_mthd_cd
,p_enrt_ovridn_flag => p_enrt_ovridn_flag
,p_enrt_ovrid_rsn_cd => p_enrt_ovrid_rsn_cd
,p_erlst_deenrt_dt => p_erlst_deenrt_dt
,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt
,p_enrt_ovrid_thru_dt => p_enrt_ovrid_thru_dt
,p_pl_ordr_num => p_pl_ordr_num
,p_plip_ordr_num => p_plip_ordr_num
,p_ptip_ordr_num => p_ptip_ordr_num
,p_oipl_ordr_num => p_oipl_ordr_num
,p_pen_attribute_category => p_pen_attribute_category
,p_pen_attribute1 => p_pen_attribute1
,p_pen_attribute2 => p_pen_attribute2
,p_pen_attribute3 => p_pen_attribute3
,p_pen_attribute4 => p_pen_attribute4
,p_pen_attribute5 => p_pen_attribute5
,p_pen_attribute6 => p_pen_attribute6
,p_pen_attribute7 => p_pen_attribute7
,p_pen_attribute8 => p_pen_attribute8
,p_pen_attribute9 => p_pen_attribute9
,p_pen_attribute10 => p_pen_attribute10
,p_pen_attribute11 => p_pen_attribute11
,p_pen_attribute12 => p_pen_attribute12
,p_pen_attribute13 => p_pen_attribute13
,p_pen_attribute14 => p_pen_attribute14
,p_pen_attribute15 => p_pen_attribute15
,p_pen_attribute16 => p_pen_attribute16
,p_pen_attribute17 => p_pen_attribute17
,p_pen_attribute18 => p_pen_attribute18
,p_pen_attribute19 => p_pen_attribute19
,p_pen_attribute20 => p_pen_attribute20
,p_pen_attribute21 => p_pen_attribute21
,p_pen_attribute22 => p_pen_attribute22
,p_pen_attribute23 => p_pen_attribute23
,p_pen_attribute24 => p_pen_attribute24
,p_pen_attribute25 => p_pen_attribute25
,p_pen_attribute26 => p_pen_attribute26
,p_pen_attribute27 => p_pen_attribute27
,p_pen_attribute28 => p_pen_attribute28
,p_pen_attribute29 => p_pen_attribute29
,p_pen_attribute30 => p_pen_attribute30
,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_per_in_ler_id => p_per_in_ler_id
,p_bnft_typ_cd => p_bnft_typ_cd
,p_bnft_ordr_num => p_bnft_ordr_num
,p_prtt_enrt_rslt_stat_cd => p_prtt_enrt_rslt_stat_cd
,p_bnft_nnmntry_uom => p_bnft_nnmntry_uom
,p_comp_lvl_cd => p_comp_lvl_cd
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => l_datetrack_mode
,p_multi_row_validate => p_multi_row_validate
);
ben_global_enrt.reload_pen -- result globals re-loaded after update.
(p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_effective_date => p_effective_date
,p_global_pen_rec => l_global_pen_rec);
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update_override then
--
l_ppe_datetrack_mode := hr_api.g_update_override;
elsif l_update then
--
l_ppe_datetrack_mode := hr_api.g_update;
insert into BEN_LE_CLSN_N_RSTR (
BKUP_TBL_TYP_CD,
LCR_ATTRIBUTE6,
LCR_ATTRIBUTE7,
LCR_ATTRIBUTE8,
LCR_ATTRIBUTE9,
LCR_ATTRIBUTE10,
LCR_ATTRIBUTE11,
LCR_ATTRIBUTE12,
LCR_ATTRIBUTE13,
LCR_ATTRIBUTE14,
LCR_ATTRIBUTE15,
LCR_ATTRIBUTE16,
LCR_ATTRIBUTE17,
LCR_ATTRIBUTE18,
LCR_ATTRIBUTE19,
LCR_ATTRIBUTE20,
LCR_ATTRIBUTE21,
LCR_ATTRIBUTE22,
LCR_ATTRIBUTE23,
LCR_ATTRIBUTE24,
LCR_ATTRIBUTE25,
LCR_ATTRIBUTE26,
LCR_ATTRIBUTE27,
LCR_ATTRIBUTE28,
LCR_ATTRIBUTE29,
LCR_ATTRIBUTE30,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
OBJECT_VERSION_NUMBER,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
PER_IN_LER_ID,
PER_IN_LER_ENDED_ID,
BKUP_TBL_ID, -- PRTT_PREM_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
STD_PREM_UOM,
STD_PREM_VAL,
ACTL_PREM_ID,
PRTT_ENRT_RSLT_ID,
BUSINESS_GROUP_ID,
LCR_ATTRIBUTE_CATEGORY,
LCR_ATTRIBUTE1,
LCR_ATTRIBUTE2,
LCR_ATTRIBUTE3,
LCR_ATTRIBUTE4,
LCR_ATTRIBUTE5
)
values (
'BEN_PRTT_PREM_F_CORR',
l_ppe1.PPE_ATTRIBUTE6,
l_ppe1.PPE_ATTRIBUTE7,
l_ppe1.PPE_ATTRIBUTE8,
l_ppe1.PPE_ATTRIBUTE9,
l_ppe1.PPE_ATTRIBUTE10,
l_ppe1.PPE_ATTRIBUTE11,
l_ppe1.PPE_ATTRIBUTE12,
l_ppe1.PPE_ATTRIBUTE13,
l_ppe1.PPE_ATTRIBUTE14,
l_ppe1.PPE_ATTRIBUTE15,
l_ppe1.PPE_ATTRIBUTE16,
l_ppe1.PPE_ATTRIBUTE17,
l_ppe1.PPE_ATTRIBUTE18,
l_ppe1.PPE_ATTRIBUTE19,
l_ppe1.PPE_ATTRIBUTE20,
l_ppe1.PPE_ATTRIBUTE21,
l_ppe1.PPE_ATTRIBUTE22,
l_ppe1.PPE_ATTRIBUTE23,
l_ppe1.PPE_ATTRIBUTE24,
l_ppe1.PPE_ATTRIBUTE25,
l_ppe1.PPE_ATTRIBUTE26,
l_ppe1.PPE_ATTRIBUTE27,
l_ppe1.PPE_ATTRIBUTE28,
l_ppe1.PPE_ATTRIBUTE29,
l_ppe1.PPE_ATTRIBUTE30,
l_ppe1.LAST_UPDATE_DATE,
l_ppe1.LAST_UPDATED_BY,
l_ppe1.LAST_UPDATE_LOGIN,
l_ppe1.CREATED_BY,
l_ppe1.CREATION_DATE,
l_ppe1.OBJECT_VERSION_NUMBER,
l_ppe1.REQUEST_ID,
l_ppe1.PROGRAM_APPLICATION_ID,
l_ppe1.PROGRAM_ID,
l_ppe1.PROGRAM_UPDATE_DATE,
l_ppe1.PER_IN_LER_ID,
p_per_in_ler_id,
l_ppe1.PRTT_PREM_ID,
l_ppe1.EFFECTIVE_START_DATE,
l_ppe1.EFFECTIVE_END_DATE,
l_ppe1.STD_PREM_UOM,
l_ppe1.STD_PREM_VAL,
l_ppe1.ACTL_PREM_ID,
l_ppe1.PRTT_ENRT_RSLT_ID,
l_ppe1.BUSINESS_GROUP_ID,
l_ppe1.PPE_ATTRIBUTE_CATEGORY,
l_ppe1.PPE_ATTRIBUTE1,
l_ppe1.PPE_ATTRIBUTE2,
l_ppe1.PPE_ATTRIBUTE3,
l_ppe1.PPE_ATTRIBUTE4,
l_ppe1.PPE_ATTRIBUTE5
);
ben_prtt_prem_api.update_prtt_prem
( p_validate => FALSE
,p_prtt_prem_id => l_ppe1.prtt_prem_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_per_in_ler_id => p_per_in_ler_id
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => l_ppe1.prtt_enrt_rslt_id
,p_object_version_number => l_ppe1.object_version_number
,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_effective_date => l_ppe1.effective_start_date
,p_datetrack_mode => 'CORRECTION'
);
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update_override then
--
l_ppe_datetrack_mode := hr_api.g_update_override;
elsif l_update then
--
l_ppe_datetrack_mode := hr_api.g_update;
ben_prtt_prem_api.update_prtt_prem
( p_validate => FALSE
,p_prtt_prem_id => l_ppe.prtt_prem_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_std_prem_uom => l_prem.uom
,p_std_prem_val => l_prem.val
,p_actl_prem_id => l_prem.actl_prem_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_ppe.object_version_number
,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
/*
CODE PRIOR TO WWBUG: 1646442
,p_effective_date => p_effective_date
*/
/* Start of Changes for WWBUG: 1646442 */
,p_effective_date => l_ppe_dt_to_use
/* End of Changes for WWBUG: 1646442 */
,p_datetrack_mode => l_ppe_datetrack_mode
);
,p_program_update_date => sysdate
/*
CODE PRIOR TO WWBUG: 1646442
,p_effective_date => p_effective_date
*/
/* Start of Changes for WWBUG: 1646442 */
,p_effective_date => l_ppe_dt_to_use
/* End of Changes for WWBUG: 1646442 */
);
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update_override then
l_egd_datetrack_mode := hr_api.g_update_override;
elsif l_update then
l_egd_datetrack_mode := hr_api.g_update;
ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
p_elig_cvrd_dpnt_id => l_egd.elig_cvrd_dpnt_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_business_group_id => p_business_group_id,
p_per_in_ler_id => p_per_in_ler_id,
p_cvg_thru_dt => hr_api.g_eot,
p_object_version_number => l_egd.object_version_number,
p_datetrack_mode => l_egd_datetrack_mode,
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_effective_date => p_effective_date,
p_multi_row_actn => FALSE);
(p_action => 'UPDATE'
,p_pl_id => p_pl_id
,p_old_pl_id => l_global_pen_rec.pl_id
,p_oipl_id => p_oipl_id
,p_old_oipl_id => l_global_pen_rec.oipl_id
,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
,p_old_enrt_cvg_strt_dt => l_global_pen_rec.enrt_cvg_strt_dt
,p_bnft_amt => p_bnft_amt
,p_old_bnft_amt => l_global_pen_rec.bnft_amt
,p_pen_attribute1 => p_pen_attribute1
,p_old_pen_attribute1 => l_global_pen_rec.pen_attribute1
,p_pen_attribute2 => p_pen_attribute2
,p_old_pen_attribute2 => l_global_pen_rec.pen_attribute2
,p_pen_attribute3 => p_pen_attribute3
,p_old_pen_attribute3 => l_global_pen_rec.pen_attribute3
,p_pen_attribute4 => p_pen_attribute4
,p_old_pen_attribute4 => l_global_pen_rec.pen_attribute4
,p_pen_attribute5 => p_pen_attribute5
,p_old_pen_attribute5 => l_global_pen_rec.pen_attribute5
,p_pen_attribute6 => p_pen_attribute6
,p_old_pen_attribute6 => l_global_pen_rec.pen_attribute6
,p_pen_attribute7 => p_pen_attribute7
,p_old_pen_attribute7 => l_global_pen_rec.pen_attribute7
,p_pen_attribute8 => p_pen_attribute8
,p_old_pen_attribute8 => l_global_pen_rec.pen_attribute8
,p_pen_attribute9 => p_pen_attribute9
,p_old_pen_attribute9 => l_global_pen_rec.pen_attribute9
,p_pen_attribute10 => p_pen_attribute10
,p_old_pen_attribute10 => l_global_pen_rec.pen_attribute10
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_per_in_ler_id => l_global_pen_rec.per_in_ler_id
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
);
ROLLBACK TO update_ENROLLMENT;
ROLLBACK TO update_ENROLLMENT;
end update_ENROLLMENT;
procedure update_PRTT_ENRT_RESULT
(p_validate in boolean default false
,p_prtt_enrt_rslt_id in number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_business_group_id in number default hr_api.g_number
,p_oipl_id in number default hr_api.g_number
,p_person_id in number default hr_api.g_number
,p_assignment_id in number default hr_api.g_number
,p_pgm_id in number default hr_api.g_number
,p_pl_id in number default hr_api.g_number
,p_rplcs_sspndd_rslt_id in number default hr_api.g_number
,p_ptip_id in number default hr_api.g_number
,p_pl_typ_id in number default hr_api.g_number
,p_ler_id in number default hr_api.g_number
,p_sspndd_flag in varchar2 default hr_api.g_varchar2
,p_prtt_is_cvrd_flag in varchar2 default hr_api.g_varchar2
,p_bnft_amt in number default hr_api.g_number
,p_uom in varchar2 default hr_api.g_varchar2
,p_orgnl_enrt_dt in date default hr_api.g_date
,p_enrt_mthd_cd in varchar2 default hr_api.g_varchar2
,p_no_lngr_elig_flag in varchar2 default hr_api.g_varchar2
,p_enrt_ovridn_flag in varchar2 default hr_api.g_varchar2
,p_enrt_ovrid_rsn_cd in varchar2 default hr_api.g_varchar2
,p_erlst_deenrt_dt in date default hr_api.g_date
,p_enrt_cvg_strt_dt in date default hr_api.g_date
,p_enrt_cvg_thru_dt in date default hr_api.g_date
,p_enrt_ovrid_thru_dt in date default hr_api.g_date
,p_pl_ordr_num in number default hr_api.g_number
,p_plip_ordr_num in number default hr_api.g_number
,p_ptip_ordr_num in number default hr_api.g_number
,p_oipl_ordr_num in number default hr_api.g_number
,p_pen_attribute_category in varchar2 default hr_api.g_varchar2
,p_pen_attribute1 in varchar2 default hr_api.g_varchar2
,p_pen_attribute2 in varchar2 default hr_api.g_varchar2
,p_pen_attribute3 in varchar2 default hr_api.g_varchar2
,p_pen_attribute4 in varchar2 default hr_api.g_varchar2
,p_pen_attribute5 in varchar2 default hr_api.g_varchar2
,p_pen_attribute6 in varchar2 default hr_api.g_varchar2
,p_pen_attribute7 in varchar2 default hr_api.g_varchar2
,p_pen_attribute8 in varchar2 default hr_api.g_varchar2
,p_pen_attribute9 in varchar2 default hr_api.g_varchar2
,p_pen_attribute10 in varchar2 default hr_api.g_varchar2
,p_pen_attribute11 in varchar2 default hr_api.g_varchar2
,p_pen_attribute12 in varchar2 default hr_api.g_varchar2
,p_pen_attribute13 in varchar2 default hr_api.g_varchar2
,p_pen_attribute14 in varchar2 default hr_api.g_varchar2
,p_pen_attribute15 in varchar2 default hr_api.g_varchar2
,p_pen_attribute16 in varchar2 default hr_api.g_varchar2
,p_pen_attribute17 in varchar2 default hr_api.g_varchar2
,p_pen_attribute18 in varchar2 default hr_api.g_varchar2
,p_pen_attribute19 in varchar2 default hr_api.g_varchar2
,p_pen_attribute20 in varchar2 default hr_api.g_varchar2
,p_pen_attribute21 in varchar2 default hr_api.g_varchar2
,p_pen_attribute22 in varchar2 default hr_api.g_varchar2
,p_pen_attribute23 in varchar2 default hr_api.g_varchar2
,p_pen_attribute24 in varchar2 default hr_api.g_varchar2
,p_pen_attribute25 in varchar2 default hr_api.g_varchar2
,p_pen_attribute26 in varchar2 default hr_api.g_varchar2
,p_pen_attribute27 in varchar2 default hr_api.g_varchar2
,p_pen_attribute28 in varchar2 default hr_api.g_varchar2
,p_pen_attribute29 in varchar2 default hr_api.g_varchar2
,p_pen_attribute30 in varchar2 default hr_api.g_varchar2
,p_request_id in number default hr_api.g_number
,p_program_application_id in number default hr_api.g_number
,p_program_id in number default hr_api.g_number
,p_program_update_date in date default hr_api.g_date
,p_object_version_number in out nocopy number
,p_per_in_ler_id in number default hr_api.g_number
,p_bnft_typ_cd in varchar2 default hr_api.g_varchar2
,p_bnft_ordr_num in number default hr_api.g_number
,p_prtt_enrt_rslt_stat_cd in varchar2 default hr_api.g_varchar2
,p_bnft_nnmntry_uom in varchar2 default hr_api.g_varchar2
,p_comp_lvl_cd in varchar2 default hr_api.g_varchar2
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_multi_row_validate in boolean default TRUE
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) ; -- := g_package||'update_PRTT_ENRT_RESULT';
select *
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prtt_enrt_rslt_stat_cd is null
and p_effective_date between effective_start_date and effective_end_date ;
select 'x'
from BEN_LE_CLSN_N_RSTR
where bkup_tbl_id = c_pen_id
and ((per_in_ler_id = c_pil_id
and per_in_ler_ended_id = c_pil_end_id)
or per_in_ler_ended_id = c_pil_id) -- 7197868
and BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR'
union
select 'x'
from BEN_LE_CLSN_N_RSTR
where bkup_tbl_id = c_pen_id
and per_in_ler_id = c_pil_id
and BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F'
and c_eff_strt_dt = EFFECTIVE_START_DATE
and c_eff_end_dt = EFFECTIVE_END_DATE
and c_cvg_strt_dt = ENRT_CVG_STRT_DT
and c_cvg_thru_dt = ENRT_CVG_THRU_DT
and nvl(c_pen_stat_cd,'-1') = nvl(PRTT_ENRT_RSLT_STAT_CD,'-1')
;
l_proc := g_package||'update_PRTT_ENRT_RESULT';
savepoint update_PRTT_ENRT_RESULT;
hr_utility.set_location(' insertin the row of ' || p_prtt_enrt_rslt_id , 99 );
insert into BEN_LE_CLSN_N_RSTR (
BKUP_TBL_TYP_CD,
COMP_LVL_CD,
LCR_ATTRIBUTE16,
LCR_ATTRIBUTE17,
LCR_ATTRIBUTE18,
LCR_ATTRIBUTE19,
LCR_ATTRIBUTE20,
LCR_ATTRIBUTE21,
LCR_ATTRIBUTE22,
LCR_ATTRIBUTE23,
LCR_ATTRIBUTE24,
LCR_ATTRIBUTE25,
LCR_ATTRIBUTE26,
LCR_ATTRIBUTE27,
LCR_ATTRIBUTE28,
LCR_ATTRIBUTE29,
LCR_ATTRIBUTE30,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
BKUP_TBL_ID, -- PRTT_ENRT_RSLT_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
ENRT_CVG_STRT_DT,
ENRT_CVG_THRU_DT,
SSPNDD_FLAG,
PRTT_IS_CVRD_FLAG,
BNFT_AMT,
BNFT_NNMNTRY_UOM,
BNFT_TYP_CD,
UOM,
ORGNL_ENRT_DT,
ENRT_MTHD_CD,
ENRT_OVRIDN_FLAG,
ENRT_OVRID_RSN_CD,
ERLST_DEENRT_DT,
ENRT_OVRID_THRU_DT,
NO_LNGR_ELIG_FLAG,
BNFT_ORDR_NUM,
PERSON_ID,
ASSIGNMENT_ID,
PGM_ID,
PRTT_ENRT_RSLT_STAT_CD,
PL_ID,
OIPL_ID,
PTIP_ID,
PL_TYP_ID,
LER_ID,
PER_IN_LER_ID,
RPLCS_SSPNDD_RSLT_ID,
BUSINESS_GROUP_ID,
LCR_ATTRIBUTE_CATEGORY,
LCR_ATTRIBUTE1,
LCR_ATTRIBUTE2,
LCR_ATTRIBUTE3,
LCR_ATTRIBUTE4,
LCR_ATTRIBUTE5,
LCR_ATTRIBUTE6,
LCR_ATTRIBUTE7,
LCR_ATTRIBUTE8,
LCR_ATTRIBUTE9,
LCR_ATTRIBUTE10,
LCR_ATTRIBUTE11,
LCR_ATTRIBUTE12,
LCR_ATTRIBUTE13,
LCR_ATTRIBUTE14,
LCR_ATTRIBUTE15 ,
PER_IN_LER_ENDED_ID,
PL_ORDR_NUM,
PLIP_ORDR_NUM,
PTIP_ORDR_NUM,
OIPL_ORDR_NUM)
values (
'BEN_PRTT_ENRT_RSLT_F_CORR',
l_old_rslt.COMP_LVL_CD,
l_old_rslt.PEN_ATTRIBUTE16,
l_old_rslt.PEN_ATTRIBUTE17,
l_old_rslt.PEN_ATTRIBUTE18,
l_old_rslt.PEN_ATTRIBUTE19,
l_old_rslt.PEN_ATTRIBUTE20,
l_old_rslt.PEN_ATTRIBUTE21,
l_old_rslt.PEN_ATTRIBUTE22,
l_old_rslt.PEN_ATTRIBUTE23,
l_old_rslt.PEN_ATTRIBUTE24,
l_old_rslt.PEN_ATTRIBUTE25,
l_old_rslt.PEN_ATTRIBUTE26,
l_old_rslt.PEN_ATTRIBUTE27,
l_old_rslt.PEN_ATTRIBUTE28,
l_old_rslt.PEN_ATTRIBUTE29,
l_old_rslt.PEN_ATTRIBUTE30,
l_old_rslt.LAST_UPDATE_DATE,
l_old_rslt.LAST_UPDATED_BY,
l_old_rslt.LAST_UPDATE_LOGIN,
l_old_rslt.CREATED_BY,
l_old_rslt.CREATION_DATE,
l_old_rslt.REQUEST_ID,
l_old_rslt.PROGRAM_APPLICATION_ID,
l_old_rslt.PROGRAM_ID,
l_old_rslt.PROGRAM_UPDATE_DATE,
l_old_rslt.OBJECT_VERSION_NUMBER,
l_old_rslt.PRTT_ENRT_RSLT_ID,
l_old_rslt.EFFECTIVE_START_DATE,
l_old_rslt.EFFECTIVE_END_DATE,
l_old_rslt.ENRT_CVG_STRT_DT,
l_old_rslt.ENRT_CVG_THRU_DT,
l_old_rslt.SSPNDD_FLAG,
l_old_rslt.PRTT_IS_CVRD_FLAG,
l_old_rslt.BNFT_AMT,
l_old_rslt.BNFT_NNMNTRY_UOM,
l_old_rslt.BNFT_TYP_CD,
l_old_rslt.UOM,
l_old_rslt.ORGNL_ENRT_DT,
l_old_rslt.ENRT_MTHD_CD,
l_old_rslt.ENRT_OVRIDN_FLAG,
l_old_rslt.ENRT_OVRID_RSN_CD,
l_old_rslt.ERLST_DEENRT_DT,
l_old_rslt.ENRT_OVRID_THRU_DT,
l_old_rslt.NO_LNGR_ELIG_FLAG,
l_old_rslt.BNFT_ORDR_NUM,
l_old_rslt.PERSON_ID,
l_old_rslt.ASSIGNMENT_ID,
l_old_rslt.PGM_ID,
l_old_rslt.PRTT_ENRT_RSLT_STAT_CD,
l_old_rslt.PL_ID,
l_old_rslt.OIPL_ID,
l_old_rslt.PTIP_ID,
l_old_rslt.PL_TYP_ID,
l_old_rslt.LER_ID,
l_old_rslt.PER_IN_LER_ID,
l_old_rslt.RPLCS_SSPNDD_RSLT_ID,
l_old_rslt.BUSINESS_GROUP_ID,
l_old_rslt.PEN_ATTRIBUTE_CATEGORY,
l_old_rslt.PEN_ATTRIBUTE1,
l_old_rslt.PEN_ATTRIBUTE2,
l_old_rslt.PEN_ATTRIBUTE3,
l_old_rslt.PEN_ATTRIBUTE4,
l_old_rslt.PEN_ATTRIBUTE5,
l_old_rslt.PEN_ATTRIBUTE6,
l_old_rslt.PEN_ATTRIBUTE7,
l_old_rslt.PEN_ATTRIBUTE8,
l_old_rslt.PEN_ATTRIBUTE9,
l_old_rslt.PEN_ATTRIBUTE10,
l_old_rslt.PEN_ATTRIBUTE11,
l_old_rslt.PEN_ATTRIBUTE12,
l_old_rslt.PEN_ATTRIBUTE13,
l_old_rslt.PEN_ATTRIBUTE14,
l_old_rslt.PEN_ATTRIBUTE15,
p_per_in_ler_id ,
l_old_rslt.PL_ORDR_NUM,
l_old_rslt.PLIP_ORDR_NUM,
l_old_rslt.PTIP_ORDR_NUM,
l_old_rslt.OIPL_ORDR_NUM
);
ben_PRTT_ENRT_RESULT_bk2.update_PRTT_ENRT_RESULT_b
(
p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_business_group_id => p_business_group_id
,p_oipl_id => p_oipl_id
,p_person_id => p_person_id
,p_assignment_id => p_assignment_id
,p_pgm_id => p_pgm_id
,p_pl_id => p_pl_id
,p_rplcs_sspndd_rslt_id => p_rplcs_sspndd_rslt_id
,p_ptip_id => p_ptip_id
,p_pl_typ_id => p_pl_typ_id
,p_ler_id => p_ler_id
,p_sspndd_flag => p_sspndd_flag
,p_prtt_is_cvrd_flag => p_prtt_is_cvrd_flag
,p_bnft_amt => p_bnft_amt
,p_uom => p_uom
,p_orgnl_enrt_dt => p_orgnl_enrt_dt
,p_enrt_mthd_cd => p_enrt_mthd_cd
,p_no_lngr_elig_flag => p_no_lngr_elig_flag
,p_enrt_ovridn_flag => p_enrt_ovridn_flag
,p_enrt_ovrid_rsn_cd => p_enrt_ovrid_rsn_cd
,p_erlst_deenrt_dt => p_erlst_deenrt_dt
,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt
,p_enrt_ovrid_thru_dt => p_enrt_ovrid_thru_dt
,p_pl_ordr_num => p_pl_ordr_num
,p_plip_ordr_num => p_plip_ordr_num
,p_ptip_ordr_num => p_ptip_ordr_num
,p_oipl_ordr_num => p_oipl_ordr_num
,p_pen_attribute_category => p_pen_attribute_category
,p_pen_attribute1 => p_pen_attribute1
,p_pen_attribute2 => p_pen_attribute2
,p_pen_attribute3 => p_pen_attribute3
,p_pen_attribute4 => p_pen_attribute4
,p_pen_attribute5 => p_pen_attribute5
,p_pen_attribute6 => p_pen_attribute6
,p_pen_attribute7 => p_pen_attribute7
,p_pen_attribute8 => p_pen_attribute8
,p_pen_attribute9 => p_pen_attribute9
,p_pen_attribute10 => p_pen_attribute10
,p_pen_attribute11 => p_pen_attribute11
,p_pen_attribute12 => p_pen_attribute12
,p_pen_attribute13 => p_pen_attribute13
,p_pen_attribute14 => p_pen_attribute14
,p_pen_attribute15 => p_pen_attribute15
,p_pen_attribute16 => p_pen_attribute16
,p_pen_attribute17 => p_pen_attribute17
,p_pen_attribute18 => p_pen_attribute18
,p_pen_attribute19 => p_pen_attribute19
,p_pen_attribute20 => p_pen_attribute20
,p_pen_attribute21 => p_pen_attribute21
,p_pen_attribute22 => p_pen_attribute22
,p_pen_attribute23 => p_pen_attribute23
,p_pen_attribute24 => p_pen_attribute24
,p_pen_attribute25 => p_pen_attribute25
,p_pen_attribute26 => p_pen_attribute26
,p_pen_attribute27 => p_pen_attribute27
,p_pen_attribute28 => p_pen_attribute28
,p_pen_attribute29 => p_pen_attribute29
,p_pen_attribute30 => p_pen_attribute30
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => p_program_update_date
,p_object_version_number => p_object_version_number
,p_per_in_ler_id => p_per_in_ler_id
,p_bnft_typ_cd => p_bnft_typ_cd
,p_bnft_ordr_num => p_bnft_ordr_num
,p_prtt_enrt_rslt_stat_cd => p_prtt_enrt_rslt_stat_cd
,p_bnft_nnmntry_uom => p_bnft_nnmntry_uom
,p_comp_lvl_cd => p_comp_lvl_cd
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'UPDATE_PRTT_ENRT_RESULT'
,p_hook_type => 'BP'
);
,p_program_update_date => p_program_update_date
,p_object_version_number => l_object_version_number
,p_per_in_ler_id => p_per_in_ler_id
,p_bnft_typ_cd => p_bnft_typ_cd
,p_bnft_ordr_num => p_bnft_ordr_num
,p_prtt_enrt_rslt_stat_cd => p_prtt_enrt_rslt_stat_cd
,p_bnft_nnmntry_uom => p_bnft_nnmntry_uom
,p_comp_lvl_cd => p_comp_lvl_cd
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
ben_PRTT_ENRT_RESULT_bk2.update_PRTT_ENRT_RESULT_a
(
p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_business_group_id => p_business_group_id
,p_oipl_id => p_oipl_id
,p_person_id => p_person_id
,p_assignment_id => p_assignment_id
,p_pgm_id => p_pgm_id
,p_pl_id => p_pl_id
,p_rplcs_sspndd_rslt_id => p_rplcs_sspndd_rslt_id
,p_ptip_id => p_ptip_id
,p_pl_typ_id => p_pl_typ_id
,p_ler_id => p_ler_id
,p_sspndd_flag => p_sspndd_flag
,p_prtt_is_cvrd_flag => p_prtt_is_cvrd_flag
,p_bnft_amt => p_bnft_amt
,p_uom => p_uom
,p_orgnl_enrt_dt => p_orgnl_enrt_dt
,p_enrt_mthd_cd => p_enrt_mthd_cd
,p_no_lngr_elig_flag => p_no_lngr_elig_flag
,p_enrt_ovridn_flag => p_enrt_ovridn_flag
,p_enrt_ovrid_rsn_cd => p_enrt_ovrid_rsn_cd
,p_erlst_deenrt_dt => p_erlst_deenrt_dt
,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt
,p_enrt_ovrid_thru_dt => p_enrt_ovrid_thru_dt
,p_pl_ordr_num => p_pl_ordr_num
,p_plip_ordr_num => p_plip_ordr_num
,p_ptip_ordr_num => p_ptip_ordr_num
,p_oipl_ordr_num => p_oipl_ordr_num
,p_pen_attribute_category => p_pen_attribute_category
,p_pen_attribute1 => p_pen_attribute1
,p_pen_attribute2 => p_pen_attribute2
,p_pen_attribute3 => p_pen_attribute3
,p_pen_attribute4 => p_pen_attribute4
,p_pen_attribute5 => p_pen_attribute5
,p_pen_attribute6 => p_pen_attribute6
,p_pen_attribute7 => p_pen_attribute7
,p_pen_attribute8 => p_pen_attribute8
,p_pen_attribute9 => p_pen_attribute9
,p_pen_attribute10 => p_pen_attribute10
,p_pen_attribute11 => p_pen_attribute11
,p_pen_attribute12 => p_pen_attribute12
,p_pen_attribute13 => p_pen_attribute13
,p_pen_attribute14 => p_pen_attribute14
,p_pen_attribute15 => p_pen_attribute15
,p_pen_attribute16 => p_pen_attribute16
,p_pen_attribute17 => p_pen_attribute17
,p_pen_attribute18 => p_pen_attribute18
,p_pen_attribute19 => p_pen_attribute19
,p_pen_attribute20 => p_pen_attribute20
,p_pen_attribute21 => p_pen_attribute21
,p_pen_attribute22 => p_pen_attribute22
,p_pen_attribute23 => p_pen_attribute23
,p_pen_attribute24 => p_pen_attribute24
,p_pen_attribute25 => p_pen_attribute25
,p_pen_attribute26 => p_pen_attribute26
,p_pen_attribute27 => p_pen_attribute27
,p_pen_attribute28 => p_pen_attribute28
,p_pen_attribute29 => p_pen_attribute29
,p_pen_attribute30 => p_pen_attribute30
,p_request_id => p_request_id
,p_program_application_id => p_program_application_id
,p_program_id => p_program_id
,p_program_update_date => p_program_update_date
,p_object_version_number => l_object_version_number
,p_per_in_ler_id => p_per_in_ler_id
,p_bnft_typ_cd => p_bnft_typ_cd
,p_bnft_ordr_num => p_bnft_ordr_num
,p_prtt_enrt_rslt_stat_cd => p_prtt_enrt_rslt_stat_cd
,p_bnft_nnmntry_uom => p_bnft_nnmntry_uom
,p_comp_lvl_cd => p_comp_lvl_cd
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'UPDATE_PRTT_ENRT_RESULT'
,p_hook_type => 'AP'
);
ROLLBACK TO update_PRTT_ENRT_RESULT;
ROLLBACK TO update_PRTT_ENRT_RESULT;
end update_PRTT_ENRT_RESULT;
procedure delete_PRTT_ENRT_RESULT
(p_validate in boolean default false
,p_prtt_enrt_rslt_id in number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_object_version_number in out nocopy number
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_multi_row_validate in boolean default TRUE
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) ; -- := g_package||'delete_PRTT_ENRT_RESULT';
l_proc := g_package||'delete_PRTT_ENRT_RESULT';
savepoint delete_PRTT_ENRT_RESULT;
ben_PRTT_ENRT_RESULT_bk3.delete_PRTT_ENRT_RESULT_b
(
p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'DELETE_PRTT_ENRT_RESULT'
,p_hook_type => 'BP'
);
ben_PRTT_ENRT_RESULT_bk3.delete_PRTT_ENRT_RESULT_a
(
p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'DELETE_PRTT_ENRT_RESULT'
,p_hook_type => 'AP'
);
ROLLBACK TO delete_PRTT_ENRT_RESULT;
ROLLBACK TO delete_PRTT_ENRT_RESULT;
end delete_PRTT_ENRT_RESULT;
select epe.elig_per_elctbl_chc_id
,epe.pgm_id
,epe.ptip_id
,epe.pl_id
,pil.ler_id
from ben_elig_per_elctbl_chc epe
,ben_per_in_ler pil
where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.business_group_id = p_business_group_id
and epe.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
;
select chg.cvg_eff_strt_cd
,chg.cvg_eff_end_cd
,chg.cvg_eff_strt_rl
,chg.cvg_eff_end_rl
,chg.ler_chg_dpnt_cvg_cd
,chg.ler_chg_dpnt_cvg_rl
from ben_ler_chg_dpnt_cvg_f chg
where chg.ler_id = l_epe.ler_id
and chg.business_group_id = p_business_group_id
and decode(p_level
,'PL',l_epe.pl_id
,'PTIP', l_epe.ptip_id
,'PGM', l_epe.pgm_id) = decode(p_level
,'PL',chg.pl_id
,'PTIP', chg.ptip_id
,'PGM', chg.pgm_id)
and p_effective_date between
chg.effective_start_date and chg.effective_end_date
;
Select min(effective_start_date),pen.per_in_ler_id
From ben_prtt_enrt_rslt_f pen
Where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
And pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
Group by pen.per_in_ler_id
order by pen.per_in_ler_id asc; -- Bug 6528876
select pen.ler_id
,pen.person_id
,pen.ENRT_CVG_STRT_DT
,pen.ENRT_CVG_THRU_DT
,pen.effective_start_date
,pen.effective_end_date
,pen.pl_id
,pen.oipl_id
,pen.object_version_number
,epe.elig_per_elctbl_chc_id
,epe.per_in_ler_id
,epe.object_version_number epe_ovn
,enb.enrt_bnft_id
from ben_prtt_enrt_rslt_f pen
,ben_elig_per_elctbl_chc epe
,ben_per_in_ler pil
,ben_enrt_bnft enb
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id (+)
and nvl(epe.elig_per_elctbl_chc_id, -1) = enb.elig_per_elctbl_chc_id (+)
and pen.business_group_id = p_business_group_id
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
and pil.per_in_ler_id(+)=epe.per_in_ler_id
and pil.business_group_id(+)=epe.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 pen.ler_id
,pen.person_id
,pen.ENRT_CVG_STRT_DT
,pen.ENRT_CVG_THRU_DT
,pen.effective_start_date
,pen.effective_end_date
,pen.pl_id
,pen.oipl_id
,pen.object_version_number
,to_number(null) elig_per_elctbl_chc_id
,to_number(null) per_in_ler_id
,to_number(null) epe_ovn
,to_number(null) enrt_bnft_id
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
;
select epe.elig_per_elctbl_chc_id
,epe.per_in_ler_id
,epe.object_version_number epe_ovn
,enb.enrt_bnft_id
from ben_elig_per_elctbl_chc epe
,ben_per_in_ler pil
,ben_enrt_bnft enb
where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and nvl(epe.elig_per_elctbl_chc_id, -1) = enb.elig_per_elctbl_chc_id (+)
and pil.per_in_ler_id=epe.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select prv.prtt_rt_val_id
--,max(ecr.enrt_rt_id) enrt_rt_id
,ecr.enrt_rt_id
,prv.object_version_number
,ecr.acty_base_rt_id
,abr.element_type_id
,abr.input_value_id
,prv.rt_strt_dt
from ben_prtt_rt_val prv
,ben_enrt_rt ecr
,ben_acty_base_rt_f abr
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.prtt_rt_val_stat_cd is null
and prv.prtt_rt_val_id = ecr.prtt_rt_val_id (+) */
/* joining ecr with choice id or benefit id is removed as ecr will be
having prtt_rt_val_id only for that particular benefit record in the case of
flat range - bug#2545915 - this one matches with the cursor in delete_enrollment
*/
/*
and (l_pen.elig_per_elctbl_chc_id is null or
l_pen.enrt_bnft_id is not null or
l_pen.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id)
and (l_pen.enrt_bnft_id is null or
l_pen.enrt_bnft_id = ecr.enrt_bnft_id)
and prv.business_group_id = p_business_group_id
and nvl(prv.acty_base_rt_id,-1) = abr.acty_base_rt_id (+)
and p_effective_date between abr.effective_start_date (+)
and abr.effective_end_date (+)
order by prv.rt_strt_dt desc; */
select distinct prv.prtt_rt_val_id
,prv.object_version_number
,abr.element_type_id
,abr.input_value_id
,prv.rt_strt_dt
from ben_prtt_rt_val prv
,ben_acty_base_rt_f abr
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.prtt_rt_val_stat_cd is null
and prv.business_group_id = p_business_group_id
and prv.acty_base_rt_id = abr.acty_base_rt_id
and p_effective_date between abr.effective_start_date
and abr.effective_end_date
order by prv.rt_strt_dt desc;
select prv.prtt_rt_val_id
,prv.object_version_number
,ecr.enrt_rt_id
,ecr.acty_base_rt_id
from ben_prtt_rt_val prv,
ben_enrt_rt ecr
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.prtt_rt_val_id = ecr.prtt_rt_val_id
and prv.rt_end_dt <> hr_api.g_eot
and prv.business_group_id = p_business_group_id
and prv.per_in_ler_id = p_per_in_ler_id
and prv.prtt_rt_val_stat_cd is null;
select py.processing_type,
abr.rcrrg_cd
from ben_acty_base_rt_f abr,
pay_element_types_f py
where abr.element_type_id = py.element_type_id(+)
and abr.acty_base_rt_id = p_acty_base_rt_id
and p_effective_date between abr.effective_start_date
and abr.effective_end_date
and p_effective_date between py.effective_start_date(+)
and py.effective_end_date(+);
select null
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil,
ben_ler_f ler
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.effective_start_date > p_effective_start_date
and pen.per_in_ler_id = pil.per_in_ler_id
and pil.ler_id = ler.ler_id
and ler.typ_cd = 'SCHEDDU'
and p_effective_start_date between ler.effective_start_date
and ler.effective_end_date
and pen.business_group_id = p_business_group_id;
select pcp.PRMRY_CARE_PRVDR_ID
,pcp.EFFECTIVE_START_DATE
,pcp.EFFECTIVE_END_DATE
,pcp.PRTT_ENRT_RSLT_ID
,pcp.BUSINESS_GROUP_ID
,pcp.OBJECT_VERSION_NUMBER
from ben_prmry_care_prvdr_f pcp
where pcp.business_group_id = p_business_group_id
and pcp.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between pcp.effective_start_date
and pcp.effective_end_date
;
select pcp.PRMRY_CARE_PRVDR_ID
,pcp.EFFECTIVE_START_DATE
,pcp.EFFECTIVE_END_DATE
,pcp.PRTT_ENRT_RSLT_ID
,pcp.BUSINESS_GROUP_ID
,pcp.OBJECT_VERSION_NUMBER
from ben_prmry_care_prvdr_f pcp
where pcp.business_group_id = p_business_group_id
and pcp.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date < pcp.effective_start_date
and NVL(pcp.effective_end_date, hr_api.g_eot) = hr_api.g_eot
;
hr_utility.set_location('delete prtt',3459);
update ben_enrt_rt
set prtt_rt_val_id = null
where enrt_rt_id = l_prv.enrt_rt_id;
ben_prtt_rt_val_api.delete_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_prv.prtt_rt_val_id
,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
,P_EFFECTIVE_DATE => l_eff_dt
,p_person_id => l_pen.person_id
,p_business_group_id => p_business_group_id
);
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_prv.prtt_rt_val_id
,P_RT_END_DT => (l_prv.rt_strt_dt - 1)
,p_person_id => l_pen.person_id
-- ,p_acty_base_rt_id => l_prv.acty_base_rt_id
,p_input_value_id => l_prv.input_value_id
,p_element_type_id => l_prv.element_type_id
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_prtt_rt_val_stat_cd => 'VOIDD'
,p_business_group_id => p_business_group_id
,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
hr_utility.set_location('Delete prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id,15);
ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
(P_VALIDATE => FALSE
,P_PRMRY_CARE_PRVDR_ID => l_pcp.prmry_care_prvdr_id
,P_EFFECTIVE_START_DATE => l_pcp.effective_start_date
,P_EFFECTIVE_END_DATE => l_pcp.effective_end_date
,P_OBJECT_VERSION_NUMBER => l_pcp.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
,P_DATETRACK_MODE => hr_api.g_zap
,p_called_from => 'delete_enrollment'
);
ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
(P_VALIDATE => FALSE
,P_PRMRY_CARE_PRVDR_ID => l_pcp_future.prmry_care_prvdr_id
,P_EFFECTIVE_START_DATE => l_pcp_future.effective_start_date
,P_EFFECTIVE_END_DATE => l_pcp_future.effective_end_date
,P_OBJECT_VERSION_NUMBER => l_pcp_future.object_version_number
,P_EFFECTIVE_DATE => l_pcp_effective_date
,P_DATETRACK_MODE => hr_api.g_zap
,p_called_from => 'delete_enrollment'
);
ben_prtt_enrt_result_api.delete_prtt_enrt_result
(p_validate => false,
p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_pen.object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_future_change,
p_multi_row_validate => FALSE);
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_validate => FALSE
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_per_in_ler_id => l_pen.per_in_ler_id
,p_enrt_cvg_thru_dt => (l_pen.enrt_cvg_strt_dt - 1)
,p_prtt_enrt_rslt_stat_cd => 'VOIDD'
,p_object_version_number => l_pen.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_multi_row_validate => p_multi_row_validate
,p_business_group_id => p_business_group_id );
(p_action => 'DELETE'
,p_old_pl_id => l_pen.pl_id
,p_old_oipl_id => l_pen.oipl_id
,p_old_enrt_cvg_strt_dt => l_pen.enrt_cvg_strt_dt
,p_old_enrt_cvg_end_dt => l_pen.enrt_cvg_thru_dt
,p_pl_id => l_pen.pl_id
,p_oipl_id => l_pen.oipl_id
,p_enrt_cvg_strt_dt => l_pen.enrt_cvg_strt_dt
,p_enrt_cvg_end_dt => (l_pen.enrt_cvg_strt_dt - 1)
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_per_in_ler_id => l_pen.per_in_ler_id
,p_person_id => l_pen.person_id
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
);
procedure delete_enrollment
(p_validate in boolean default false
,p_per_in_ler_id in number default NULL
,p_lee_rsn_id in number default NULL
,p_enrt_perd_id in number default NULL
,p_prtt_enrt_rslt_id in number
,p_business_group_id in number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_object_version_number in out nocopy number
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_multi_row_validate in boolean default TRUE
,p_source in varchar2 default null
,p_enrt_cvg_thru_dt in date default null
,p_mode in varchar2 default null)
is
--
l_fnd_message_exception exception;
select pen.pl_id
,pen.pgm_id
,pen.ptip_id
,pen.pl_typ_id
,pen.oipl_id
,pen.ler_id
,pen.person_id
,pen.business_group_id
,pen.ENRT_CVG_STRT_DT
,pen.ENRT_CVG_THRU_DT
,pen.SSPNDD_FLAG
,pen.ERLST_DEENRT_DT
,pen.effective_start_date
,pen.effective_end_date
,pen.object_version_number pen_ovn
,pen.rplcs_sspndd_rslt_id
,pen.per_in_ler_id pen_per_in_ler_id
,epe.elig_per_elctbl_chc_id
,epe.per_in_ler_id
,epe.object_version_number epe_ovn
,epe.MNDTRY_FLAG
,epe.fonm_cvg_strt_dt
,epe.ELCTBL_FLAG
,epe.AUTO_ENRT_FLAG
,enb.enrt_bnft_id
,pil.per_in_ler_stat_cd
,oipl.opt_id
,pl.name pl_name
,opt.name opt_name
,pl.imptd_incm_calc_cd
from ben_prtt_enrt_rslt_f pen
,ben_elig_per_elctbl_chc epe
,ben_per_in_ler pil
,ben_enrt_bnft enb
,ben_oipl_f oipl
,ben_pl_f pl
,ben_opt_f opt
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id (+)
and nvl(epe.elig_per_elctbl_chc_id, -1) = enb.elig_per_elctbl_chc_id (+)
and pen.business_group_id = epe.business_group_id (+)
and nvl(p_per_in_ler_id, pen.per_in_Ler_id) = epe.per_in_ler_id (+)
and pen.business_group_id = p_business_group_id
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
and pil.per_in_ler_id(+)=epe.per_in_ler_id
and pil.business_group_id(+)=epe.business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null -- outer join condition
)
and pl.business_group_id=p_business_group_id
and pl.pl_id =pen.pl_id
and p_effective_date between
pl.effective_start_date and pl.effective_end_date
and oipl.business_group_id(+)=p_business_group_id
and oipl.oipl_id(+)=pen.oipl_id
and p_effective_date between
oipl.effective_start_date(+) and oipl.effective_end_date(+)
and opt.business_group_id(+)=p_business_group_id
and opt.opt_id(+)=oipl.opt_id
and p_effective_date between
opt.effective_start_date(+) and opt.effective_end_date(+)
;
select ppe.prtt_prem_id,
ppe.object_version_number,
ppe.effective_start_date,
ppe.effective_end_date,
ppe.actl_prem_id
from ben_prtt_prem_f ppe,
ben_per_in_ler pil
where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pil.per_in_ler_id=ppe.per_in_ler_id
and pil.business_group_id=ppe.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select ppe.prtt_prem_id,
ppe.object_version_number,
ppe.effective_start_date,
ppe.effective_end_date,
ppe.actl_prem_id
from ben_prtt_prem_f ppe,
ben_per_in_ler pil
where ppe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pil.per_in_ler_id=ppe.per_in_ler_id
and pil.business_group_id=ppe.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
-- jcarpent added additional logic below to fix primary key error.
-- case 1 is where you want the dt effective row
-- case 2 is where you want the future dated rows to zap.
and p_ppe_dt_to_use between ppe.effective_start_date and ppe.effective_end_date
UNION
select ppe1.prtt_prem_id,
ppe1.object_version_number,
ppe1.effective_start_date,
ppe1.effective_end_date,
ppe1.actl_prem_id
from ben_prtt_prem_f ppe1,
ben_per_in_ler pil
where ppe1.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pil.per_in_ler_id=ppe1.per_in_ler_id
and pil.business_group_id=ppe1.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and ppe1.effective_start_date > p_ppe_dt_to_use
and not exists
(select 1
from ben_prtt_prem_f ppe2,
ben_per_in_ler pil
where ppe2.prtt_prem_id = ppe1.prtt_prem_id
and ppe2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pil.per_in_ler_id=ppe2.per_in_ler_id
and pil.business_group_id=ppe2.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and p_ppe_dt_to_use between
ppe2.effective_start_date and ppe2.effective_end_date);
select prm.prtt_prem_by_mo_id,
prm.object_version_number,
prm.effective_start_date,
prm.effective_end_date
from ben_prtt_prem_by_mo_f prm
where prm.prtt_prem_id = l_prtt_prem_id;
select prv.prtt_rt_val_id
,prv.rt_strt_dt
,prv.per_in_ler_id
,prv.object_version_number
,prv.acty_base_rt_id
,abr.element_type_id
,abr.input_value_id
from ben_prtt_rt_val prv
,ben_acty_base_rt_f abr
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.rt_end_dt = hr_api.g_eot
and prv.business_group_id = p_business_group_id
and prv.acty_base_rt_id = abr.acty_base_rt_id
and p_effective_date between abr.effective_start_date
and abr.effective_end_date
;
select prv.prtt_rt_val_id
,prv.rt_strt_dt
,prv.per_in_ler_id
,prv.object_version_number
,prv.acty_base_rt_id
,abr.element_type_id
,abr.input_value_id
from ben_prtt_rt_val prv
,ben_acty_base_rt_f abr
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.per_in_ler_id <> p_per_in_ler_id -- basu
and prv.rt_end_dt > p_rt_end_dt
and prv.rt_end_dt <> hr_api.g_eot
and prv.business_group_id = p_business_group_id
and prv.acty_base_rt_id = abr.acty_base_rt_id
and p_effective_date between abr.effective_start_date
and abr.effective_end_date
;
select 'Y'
from ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id = p_per_in_ler_id
and pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.prtt_enrt_rslt_stat_cd is null;
select pen.prtt_enrt_rslt_id
,pen.enrt_cvg_strt_dt
,pen.per_in_ler_id
,pen.object_version_number
from ben_prtt_enrt_rslt_f pen
,ben_ptip_f ptip
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id -- 245535
and pen.per_in_ler_id <> p_per_in_ler_id -- basu
and pen.enrt_cvg_thru_dt > p_cvg_end_dt -- '20-jan-2008'
and pen.enrt_cvg_thru_dt <> hr_api.g_eot -- '31-dec-4712'
and pen.business_group_id = p_business_group_id --81545
and pen.ptip_id = ptip.ptip_id
and p_effective_date between ptip.effective_start_date
and ptip.effective_end_date;
select pgi_information1
from ben_pgm_extra_info
where information_type = 'ADJ_RATE_PREV_LF_EVT'
and pgm_id = p_pgm_id;
select pgi_information1
from ben_pgm_extra_info
where information_type = 'ADJ_CVG_PREV_LF_EVT'
and pgm_id = p_pgm_id;
select prv.prtt_rt_val_id
,max(ecr.enrt_rt_id) enrt_rt_id
,prv.object_version_number
,ecr.acty_base_rt_id
,abr.element_type_id
,abr.input_value_id
,abr.entr_val_at_enrt_flag
,prv.rt_strt_dt
from ben_prtt_rt_val prv
,ben_enrt_rt ecr
,ben_acty_base_rt_f abr
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.prtt_rt_val_id = ecr.prtt_rt_val_id (+)
and prv.business_group_id = p_business_group_id
and nvl(prv.acty_base_rt_id,-1) = abr.acty_base_rt_id (+)
and p_effective_date between abr.effective_start_date (+)
and abr.effective_end_date (+)
group by
prv.prtt_rt_val_id
,prv.object_version_number
,ecr.acty_base_rt_id
,abr.element_type_id
,abr.input_value_id
,abr.entr_val_at_enrt_flag
,prv.rt_strt_dt;
select prv.prtt_rt_val_id
,prv.object_version_number
,ecr.enrt_rt_id
,ecr.acty_base_rt_id
from ben_prtt_rt_val prv,
ben_enrt_rt ecr
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.prtt_rt_val_id = ecr.prtt_rt_val_id
and prv.rt_end_dt <> hr_api.g_eot
and prv.business_group_id = p_business_group_id
and prv.per_in_ler_id = p_per_in_ler_id
and prv.prtt_rt_val_stat_cd is null;
select prv.prtt_rt_val_id
,ecr.enrt_rt_id enrt_rt_id
,prv.object_version_number
,ecr.acty_base_rt_id
,abr.element_type_id
,abr.input_value_id
,prv.rt_end_dt --This is needed to determine the future started rate
from ben_prtt_rt_val prv
,ben_enrt_rt ecr
,ben_acty_base_rt_f abr
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.rt_end_dt > p_rt_end_dt
and prv.prtt_rt_val_id = ecr.prtt_rt_val_id (+)
and prv.business_group_id = p_business_group_id
and prv.prtt_rt_val_stat_cd is null
and nvl(prv.acty_base_rt_id,-1) = abr.acty_base_rt_id (+)
and p_effective_date between abr.effective_start_date (+)
and abr.effective_end_date (+);
select py.processing_type,
abr.rcrrg_cd
from ben_acty_base_rt_f abr,
pay_element_types_f py
where abr.element_type_id = py.element_type_id(+)
and abr.acty_base_rt_id = p_acty_base_rt_id
and p_effective_date between abr.effective_start_date
and abr.effective_end_date
and p_effective_date between py.effective_start_date(+)
and py.effective_end_date(+);
select pcp.PRMRY_CARE_PRVDR_ID
,pcp.EFFECTIVE_START_DATE
,pcp.EFFECTIVE_END_DATE
,pcp.PRTT_ENRT_RSLT_ID
,pcp.BUSINESS_GROUP_ID
,pcp.OBJECT_VERSION_NUMBER
from ben_prmry_care_prvdr_f pcp
where business_group_id = p_business_group_id
and prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and c_pcp_effective_date between effective_start_date --3631067: Changed p_effective_date to c_pcp_effective_date
and effective_end_date
;
select pcp.PRMRY_CARE_PRVDR_ID
,pcp.EFFECTIVE_START_DATE
,pcp.EFFECTIVE_END_DATE
,pcp.PRTT_ENRT_RSLT_ID
,pcp.BUSINESS_GROUP_ID
,pcp.OBJECT_VERSION_NUMBER
from ben_prmry_care_prvdr_f pcp
where business_group_id = p_business_group_id
and prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and c_pcp_effective_date < effective_start_date ----3631067: Changed p_effective_date to c_pcp_effective_date
and NVL(effective_end_date, hr_api.g_eot) = hr_api.g_eot
;
select max(enrt_cvg_strt_dt)
from ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id = nvl(p_per_in_ler_id, -1) -- is this OK?
and ((pen.pgm_id is null and pen.pl_typ_id is not null
and pen.pl_typ_id = nvl(l_pl_typ_id, -1))
or (pen.ptip_id is not null and pen.ptip_id = nvl(l_ptip_id, -1))
)
and pen.no_lngr_elig_flag = 'N'
and ((l_pl_id =-999 and pen.enrt_cvg_strt_dt >= p_enrt_cvg_strt_dt)
or pen.pl_id = l_pl_id ) -- Bug 2847110
and pen.prtt_enrt_rslt_stat_cd is null
-- and pen.enrt_cvg_strt_dt >= p_effective_date
and pen.ENRT_CVG_THRU_DT = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot
-- and pen.business_group_id = p_business_group_id
--and p_effective_date between pen.effective_start_date
-- and pen.effective_end_date
and pen.prtt_enrt_rslt_id <> p_prtt_enrt_rslt_id
;
select 'Y'
from ben_prtt_enrt_rslt_f pen
where pen.rplcs_sspndd_rslt_id = l_pen.rplcs_sspndd_rslt_id
and pen.business_group_id = p_business_group_id
and pen.sspndd_flag = 'Y'
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
-- Bug 6165501 : Added union clause if its a Correction case, and interim has already
-- been end-dated by the new pil, then no need to call delete_enrollment for the interim
UNION
select 'Y'
from ben_prtt_enrt_rslt_f pen, ben_le_clsn_n_rstr cls
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt <> hr_api.g_eot
and pen.per_in_ler_id = p_per_in_ler_id
and cls.bkup_tbl_id = pen.prtt_enrt_rslt_id
and cls.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F_CORR'
and cls.per_in_ler_ended_id = p_per_in_ler_id;
select pen.prtt_enrt_rslt_id,
pen.object_version_number,
pen.effective_start_date,
pen.effective_end_date
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = l_pen.rplcs_sspndd_rslt_id
and pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pen.effective_start_date and pen.effective_end_date;
select 'x'
from ben_prtt_enrt_rslt_f pen
where pen.person_id = p_person_id
and nvl(p_pgm_id,-1) = nvl(pen.pgm_id,-1)
and nvl(p_pl_id ,-1) = nvl(pen.pl_id,-1)
and nvl(p_oipl_id,-1) = nvl(pen.oipl_id, -1)
and (p_per_in_ler_id <> pen.per_in_ler_id
or l_global_pil_rec.typ_cd = 'SCHEDDU') -- 4919591
and pen.prtt_enrt_rslt_stat_cd is null
and pen.sspndd_flag = 'N'
and p_effective_date > pen.effective_start_date
and pen.enrt_cvg_thru_dt > p_effective_date -- 4919591
and pen.effective_end_date = hr_api.g_eot; --4919591
select 'x'
from ben_prtt_enrt_rslt_f pen
where pen.person_id = p_person_id
and nvl(p_pgm_id,-1) = nvl(pen.pgm_id,-1)
and p_pl_id = pen.pl_id
and (p_per_in_ler_id <> pen.per_in_ler_id
or l_global_pil_rec.typ_cd = 'SCHEDDU') -- 4919591
and pen.prtt_enrt_rslt_stat_cd is null
and pen.sspndd_flag = 'N'
and p_effective_date > pen.effective_start_date
and pen.enrt_cvg_thru_dt > p_effective_date; -- 4919591
select 'x'
from ben_prtt_enrt_rslt_f pen
where pen.person_id = p_person_id
and p_pgm_id = pen.pgm_id
and p_ptip_id = pen.ptip_id
and (p_per_in_ler_id <> pen.per_in_ler_id
or l_global_pil_rec.typ_cd = 'SCHEDDU')
and pen.prtt_enrt_rslt_stat_cd is null
and pen.sspndd_flag = 'N'
and p_effective_date > pen.effective_start_date
and pen.enrt_cvg_thru_dt > p_effective_date
and pen.effective_end_date = hr_api.g_eot;
select *
from ben_per_in_ler
where per_in_ler_id = p_per_in_ler_id
and business_group_id = p_business_group_id;
select pen.object_version_number,pen.effective_start_date --4663971
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between pen.effective_start_date and pen.effective_end_date ;
select pel.enrt_perd_id,pel.lee_rsn_id
from ben_pil_elctbl_chc_popl pel
,ben_elig_per_elctbl_chc epe
where pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
and epe.elig_per_elctbl_chc_id = p_elig_pe_elctbl_chc_id;
SELECT leer.lee_rsn_id
FROM ben_lee_rsn_f leer,
ben_popl_enrt_typ_cycl_f petc
WHERE leer.ler_id = c_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;
SELECT leer.lee_rsn_id
FROM ben_lee_rsn_f leer,
ben_popl_enrt_typ_cycl_f petc
WHERE leer.ler_id = c_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'
AND petc.business_group_id = p_business_group_id
AND c_effective_date BETWEEN petc.effective_start_date
AND petc.effective_end_date;
select prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f pen
where pen.person_id = p_person_id
and nvl(p_pgm_id,-1) = nvl(pen.pgm_id,-1)
and nvl(p_pl_id ,-1) = nvl(pen.pl_id,-1)
and nvl(p_oipl_id,-1) = nvl(pen.oipl_id, -1)
and p_per_in_ler_id = pen.per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt <> hr_api.g_eot
;
select typ_cd
from ben_ler_f ler,
ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.ler_id = ler.ler_id ;
select per_in_ler_id
from BEN_LE_CLSN_N_RSTR
where bkup_tbl_id = c_pen_id
and per_in_ler_ended_id = c_pil_id
and per_in_ler_id <> c_pil_id
and effective_start_date = c_pen_effective_start_date -- bug 7197868
and enrt_cvg_thru_dt = hr_api.g_eot;
select object_version_number
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = c_pen_id
and pen.per_in_ler_id = c_pil_id
and p_effective_date between
pen.effective_start_date and pen.effective_end_date ;
select epe.crntly_enrd_flag,ler.typ_cd
from ben_elig_per_elctbl_chc epe,
ben_ler_f ler,
ben_prtt_enrt_rslt_f pen
where epe.per_in_ler_id=p_per_in_ler_id
and epe.elig_per_elctbl_chc_id=p_elig_pe_elctbl_chc_id
and epe.prtt_enrt_rslt_id=pen.prtt_enrt_rslt_id
and pen.per_in_ler_id=p_per_in_ler_id
and pen.ler_id = ler.ler_id
and nvl(epe.erlst_deenrt_dt,hr_api.g_sot) > p_effective_date;
select ler.typ_cd
from ben_prtt_enrt_rslt_f pen,
ben_elig_per_elctbl_chc epe,
ben_ler_f ler
where epe.elig_per_elctbl_chc_id=p_elig_pe_elctbl_chc_id
and epe.per_in_ler_id=p_per_in_ler_id
and pen.ler_id = ler.ler_id
and epe.prtt_enrt_rslt_id=pen.prtt_enrt_rslt_id
and pen.per_in_ler_id=p_per_in_ler_id
and nvl(epe.erlst_deenrt_dt,hr_api.g_sot) > p_effective_date;
select 'Y'
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt = to_date('4712/12/31','rrrr/mm/dd')
and pen.per_in_ler_id <> p_per_in_ler_id
and pen.enrt_cvg_strt_dt = p_enrt_cvg_strt_dt
and pen.effective_end_date < p_effective_start_date;
SELECT oldprv.prtt_rt_val_id,
oldprv.prtt_rt_val_stat_cd ,
oldprv.object_version_number,
abr.input_value_id,
abr.element_type_id
FROM ben_prtt_rt_val oldprv,
ben_prtt_rt_val curprv,
ben_acty_base_rt_f abr
WHERE curprv.business_group_id=oldprv.business_group_id
AND curprv.per_in_ler_id = oldprv.ended_per_in_ler_id
AND oldprv.rt_end_dt <> hr_api.g_eot
AND oldprv.acty_base_rt_id = curprv.acty_base_rt_id
AND oldprv.prtt_rt_val_stat_cd IS NULL
and curprv.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
AND abr.acty_base_rt_id = oldprv.acty_base_rt_id
AND p_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
and oldprv.ended_per_in_ler_id= p_per_in_ler_id
AND oldprv.business_group_id= p_business_group_id;
select lf_evt_ocrd_dt from
ben_per_in_ler where
per_in_ler_id = p_per_in_ler_id and
per_in_ler_stat_cd not in ('VOIDD','BCKDT');
SELECT 'X' place_holder
FROM ben_le_clsn_n_rstr bcr
WHERE bcr.bkup_tbl_id=cp_prtt_enrt_rslt_id
AND bcr.effective_start_date=cp_effective_date
UNION
SELECT 'X' place_holder
FROM ben_le_clsn_n_rstr bcr
WHERE bcr.bkup_tbl_id=cp_prtt_enrt_rslt_id
AND bcr.per_in_ler_id <> cp_per_in_ler_id
AND bcr.bkup_tbl_typ_cd LIKE '%CORR' ;
l_proc varchar2(72); -- := g_package||'delete_enrollment';
select prv.prtt_rt_val_id
,prv.object_version_number
,prv.acty_base_rt_id
,abr.element_type_id
,abr.input_value_id
,prv.rt_strt_dt
from ben_prtt_rt_val prv
,ben_acty_base_rt_f abr
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
-- bug: 5550679
and prv.per_in_ler_id = p_per_in_ler_id
--
and prv.prtt_rt_val_stat_cd is null
and prv.business_group_id = p_business_group_id
and prv.acty_base_rt_id = abr.acty_base_rt_id
and p_effective_date between abr.effective_start_date
and abr.effective_end_date
order by prv.rt_strt_dt desc;
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_prv.prtt_rt_val_id
,P_RT_END_DT => (l_prv.rt_strt_dt - 1)
,p_person_id => p_person_id
,p_input_value_id => l_prv.input_value_id
,p_element_type_id => l_prv.element_type_id
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_prtt_rt_val_stat_cd => 'VOIDD'
,p_business_group_id => p_business_group_id
,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
l_proc := g_package||'delete_enrollment';
p_source <> 'delete_enrollment' then
--
-- Do not issue the savepoint if this procedure called itself.
--
savepoint delete_enrollment;
fnd_message.set_name('BEN','BEN_91902_ENRT_NOT_ALWD_DELETE');
fnd_message.set_name('BEN','BEN_91902_ENRT_NOT_ALWD_DELETE');
fnd_message.set_name('BEN','BEN_91902_ENRT_NOT_ALWD_DELETE');
fnd_message.set_name('BEN','BEN_91902_ENRT_NOT_ALWD_DELETE');
,p_datetrack_mode => hr_api.g_delete
);
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_get_old_prv.prtt_rt_val_id
,P_RT_END_DT => l_rt_end_dt
,p_person_id => l_pen.person_id
,p_input_value_id => l_get_old_prv.input_value_id
,p_element_type_id => l_get_old_prv.element_type_id
,p_prtt_rt_val_stat_cd => null
,p_business_group_id => p_business_group_id
,P_OBJECT_VERSION_NUMBER => l_get_old_prv.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
hr_utility.set_location('delete prtt',3459);
update ben_enrt_rt
set prtt_rt_val_id = null
where enrt_rt_id = l_prv.enrt_rt_id;
ben_prtt_rt_val_api.delete_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_prv.prtt_rt_val_id
,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
,P_EFFECTIVE_DATE => l_eff_dt
,p_person_id => l_pen.person_id
,p_business_group_id => p_business_group_id
);
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_prv.prtt_rt_val_id
,P_RT_END_DT => l_rt_end_dt
,p_person_id => l_pen.person_id
--Bug#2625060 - for converted data, null is passed for activity base rt id
-- ,p_acty_base_rt_id => l_prv.acty_base_rt_id
,p_input_value_id => l_prv.input_value_id
,p_prtt_rt_val_stat_cd => l_prtt_rt_val_stat_cd
,p_element_type_id => l_prv.element_type_id
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_business_group_id => p_business_group_id
,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_prv5.prtt_rt_val_id
,P_RT_END_DT => l_rt_end_dt
,p_person_id => l_pen.person_id
,p_input_value_id => l_prv5.input_value_id
,p_element_type_id => l_prv5.element_type_id
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_business_group_id => p_business_group_id
,P_OBJECT_VERSION_NUMBER => l_prv5.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_validate => FALSE,
p_prtt_enrt_rslt_id => l_pen5.prtt_enrt_rslt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_business_group_id => p_business_group_id,
p_object_version_number => l_pen5.object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_correction,
p_multi_row_validate => FALSE,
p_enrt_cvg_thru_dt => l_enrt_cvg_end_dt,
p_person_id => l_pen.person_id
);
/* Bug 5739530 : Update prv only if it has not been updated previously */
if l_prv4.prtt_rt_val_id <> prev_prtt_rt_val_id then
--
prev_prtt_rt_val_id := l_prv4.prtt_rt_val_id;
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_prv4.prtt_rt_val_id
,P_RT_END_DT => l_rt_end_dt
,p_person_id => l_pen.person_id
--Bug#2625060 - for converted data, null is passed for activity base rt id
-- ,p_acty_base_rt_id => l_prv4.acty_base_rt_id
,p_input_value_id => l_prv4.input_value_id
,p_element_type_id => l_prv4.element_type_id
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_business_group_id => p_business_group_id
,P_OBJECT_VERSION_NUMBER => l_prv4.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
hr_utility.set_location('DELETE prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id, 15);
hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_date, 15);
hr_utility.set_location('DATETRACK_MODE '|| hr_api.g_delete, 15);
ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
(P_VALIDATE => FALSE
,P_PRMRY_CARE_PRVDR_ID => l_pcp.prmry_care_prvdr_id
,P_EFFECTIVE_START_DATE => l_pcp.effective_start_date
,P_EFFECTIVE_END_DATE => l_pcp.effective_end_date
,P_OBJECT_VERSION_NUMBER => l_pcp.object_version_number
,P_EFFECTIVE_DATE => l_pcp_effective_date
,P_DATETRACK_MODE => hr_api.g_delete
,p_called_from => 'delete_enrollment'
);
hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_start_date, 15);
ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
(P_VALIDATE => FALSE
,P_PRMRY_CARE_PRVDR_ID => l_pcp_future.prmry_care_prvdr_id
,P_EFFECTIVE_START_DATE => l_pcp_future.effective_start_date
,P_EFFECTIVE_END_DATE => l_pcp_future.effective_end_date
,P_OBJECT_VERSION_NUMBER => l_pcp_future.object_version_number
,P_EFFECTIVE_DATE => l_pcp_effective_start_date
,P_DATETRACK_MODE => hr_api.g_zap
,p_called_from => 'delete_enrollment'
);
,p_datetrack_mode => hr_api.g_delete
,p_source => p_source
,p_per_in_ler_id => l_pen.pen_per_in_ler_id
,p_per_in_ler_ended_id=> l_pen.per_in_ler_id
);
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_validate => FALSE
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_per_in_ler_id => l_pen.per_in_ler_id
,p_ler_id => l_pen.ler_id
,p_enrt_cvg_thru_dt => l_enrt_cvg_end_dt
--,p_rplcs_sspndd_rslt_id => l_rplcs_sspndd_rslt_id /* 5347887*/
,p_object_version_number => l_pen.pen_ovn
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_multi_row_validate => p_multi_row_validate
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_stat_cd => l_prtt_enrt_rslt_Stat_cd
);
(p_action => 'DELETE'
,p_old_pl_id => l_pen.pl_id
,p_old_oipl_id => l_pen.oipl_id
,p_old_enrt_cvg_strt_dt => l_pen.enrt_cvg_strt_dt
,p_old_enrt_cvg_end_dt => l_pen.enrt_cvg_thru_dt
,p_pl_id => l_pen.pl_id
,p_oipl_id => l_pen.oipl_id
,p_enrt_cvg_strt_dt => l_pen.enrt_cvg_strt_dt
,p_enrt_cvg_end_dt => l_enrt_cvg_end_dt
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_per_in_ler_id => l_pen.per_in_ler_id
,p_person_id => l_pen.person_id
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
);
,p_datetrack_mode => hr_api.g_delete
);
if p_datetrack_mode=hr_api.g_update
and l_pen.effective_start_date < p_effective_date
and p_per_in_ler_id <> l_pil_id then -----Added for the Bug 7311284
--
l_eff_dt:=p_effective_date-1;
hr_utility.set_location('delete prtt',3459);
update ben_enrt_rt
set prtt_rt_val_id = null
where enrt_rt_id = l_prv.enrt_rt_id;
ben_prtt_rt_val_api.delete_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_prv.prtt_rt_val_id
,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
,P_EFFECTIVE_DATE => l_eff_dt
,p_person_id => l_pen.person_id
,p_business_group_id => p_business_group_id
);
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_prv.prtt_rt_val_id
,P_RT_END_DT => l_rt_end_dt
,p_person_id => l_pen.person_id
--Bug#2625060 - for converted data, null is passed for activity base rt id
-- ,p_acty_base_rt_id => l_prv.acty_base_rt_id
,p_input_value_id => l_prv.input_value_id
,p_element_type_id => l_prv.element_type_id
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_business_group_id => p_business_group_id
,p_prtt_rt_val_stat_cd => 'BCKDT'
,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_prv5.prtt_rt_val_id
,P_RT_END_DT => l_rt_end_dt
,p_person_id => l_pen.person_id
,p_input_value_id => l_prv5.input_value_id
,p_element_type_id => l_prv5.element_type_id
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_business_group_id => p_business_group_id
,P_OBJECT_VERSION_NUMBER => l_prv5.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_validate => FALSE,
p_prtt_enrt_rslt_id => l_pen5.prtt_enrt_rslt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_business_group_id => p_business_group_id,
p_object_version_number => l_pen5.object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_correction,
p_multi_row_validate => FALSE,
p_enrt_cvg_thru_dt => l_enrt_cvg_end_dt,
p_person_id => l_pen.person_id
);
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_prv4.prtt_rt_val_id
,P_RT_END_DT => l_rt_end_dt
,p_person_id => l_pen.person_id
--Bug#2625060 - for converted data, null is passed for activity base rt id
-- ,p_acty_base_rt_id => l_prv4.acty_base_rt_id
,p_input_value_id => l_prv4.input_value_id
,p_element_type_id => l_prv4.element_type_id
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_business_group_id => p_business_group_id
,p_prtt_rt_val_stat_cd => 'BCKDT' -- Bug 4739922: Backout the Result/ (do not VOIDD).
,P_OBJECT_VERSION_NUMBER => l_prv4.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_validate => FALSE
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_per_in_ler_id => l_pen.per_in_ler_id
,p_ler_id => l_pen.ler_id
,p_enrt_cvg_thru_dt => l_enrt_cvg_end_dt
,p_object_version_number => l_pen.pen_ovn
,p_prtt_enrt_rslt_stat_cd => 'BCKDT' -- Bug 4739922: Backout the Result/ (do not VOIDD).
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_multi_row_validate => p_multi_row_validate
,p_business_group_id => p_business_group_id
);
,p_datetrack_mode => hr_api.g_delete
);
l_datetrack_mode := hr_api.g_delete;
,p_rslt_delete_flag => TRUE
);
,p_rslt_delete_flag => TRUE
,p_called_from => p_source );
if p_source = 'delete_enrollment' then
--
open c_curr_rslt (l_pen.person_id, l_pen.pgm_id,
l_pen.pl_id, l_pen.oipl_id, p_per_in_ler_id ) ;
ben_ELIG_PER_ELC_CHC_api.update_ELIG_PER_ELC_CHC
(p_validate => FALSE
,p_elig_per_elctbl_chc_id => l_pen.elig_per_elctbl_chc_id
,p_prtt_enrt_rslt_id => l_current_result_id -- NULL
,p_object_version_number => l_pen.epe_ovn
,p_effective_date => l_eff_dt
);
-- and hence the previously selected RT_VAL
-- Note : Probably we need this fix for all cases, but I made change for Enter Value At
-- Enrollment case only. Right now for other cases, while reinstating we create records
-- from BEPENAPI.MULTI_ROWS_EDIT >> BEN_DET_ENRT_RATES using ECR
--
IF l_prv.entr_val_at_enrt_flag = 'Y'
THEN
--
-- Bug 6471236
/* void_rate
(p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
p_business_group_id => p_business_group_id,
p_person_id => l_pen.person_id,
p_per_in_ler_id => p_per_in_ler_id,
p_effective_date => p_effective_date
); */
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_prv.prtt_rt_val_id
,P_RT_END_DT => l_temp_date
,p_person_id => l_pen.person_id
,p_input_value_id => l_prv.input_value_id
,p_element_type_id => l_prv.element_type_id
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_prtt_rt_val_stat_cd => l_temp_stat_cd
,p_business_group_id => p_business_group_id
,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_prv.prtt_rt_val_id
,P_RT_END_DT => l_enrt_cvg_end_dt
,p_person_id => l_pen.person_id
,p_input_value_id => l_prv.input_value_id
,p_element_type_id => l_prv.element_type_id
,p_ended_per_in_ler_id => p_per_in_ler_id
-- ,p_prtt_rt_val_stat_cd => l_temp_stat_cd
,p_business_group_id => p_business_group_id
,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
ben_prtt_rt_val_api.delete_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_prv.prtt_rt_val_id
,P_ENRT_RT_ID => l_prv.enrt_rt_id
,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
,P_EFFECTIVE_DATE => l_eff_dt
,p_person_id => l_pen.person_id
,p_business_group_id => p_business_group_id
);
ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
(P_VALIDATE => FALSE
,P_PRMRY_CARE_PRVDR_ID => l_pcp.prmry_care_prvdr_id
,P_EFFECTIVE_START_DATE => l_pcp.effective_start_date
,P_EFFECTIVE_END_DATE => l_pcp.effective_end_date
,P_OBJECT_VERSION_NUMBER => l_pcp.object_version_number
,P_EFFECTIVE_DATE => l_eff_dt
,P_DATETRACK_MODE => l_datetrack_mode
,p_called_from => 'delete_enrollment'
);
This whole section has been deleted
** BBurns 15aug2001
** A 'invalid primary key' error occurred when a date track delete
** was attempted with a p_effective_date prior to the start of
** the ben_prtt_prem_by_mo_f row. The ben_prtt_prem_by_mo_f rows have
** already been reported to the carrier and should not be modified
** by this procedure.
** A review of how prtt_prem_by_mo_f is defined and the functionality
** of this procedure is in order.
**
--
-- delete child ben_prtt_prem_by_mo_f records
--
for l_prm in c_prm loop
--
if (l_datetrack_mode = hr_api.g_delete and
l_prm.effective_end_date > l_eff_dt) or
l_datetrack_mode <> hr_api.g_delete then
--
ben_prtt_prem_by_mo_api.delete_prtt_prem_by_mo
(p_validate => false,
p_prtt_prem_by_mo_id => l_prm.prtt_prem_by_mo_id,
p_object_version_number => l_prm.object_version_number,
p_effective_date => l_eff_dt,
p_effective_start_date => l_prm.effective_end_date,
p_effective_end_date => l_prm.effective_start_date,
p_datetrack_mode => l_datetrack_mode
);
if (l_datetrack_mode = hr_api.g_delete and
l_ppe.effective_end_date > l_eff_dt) or
l_datetrack_mode <> hr_api.g_delete then
--
/* Start of Changes for WWBUG: 1646442: added */
l_ppe_dt_to_use := least(l_enrt_cvg_end_dt,l_rt_end_dt);
ben_prtt_prem_api.delete_prtt_prem
(p_validate => false,
p_prtt_prem_id => l_ppe.prtt_prem_id,
p_object_version_number => l_ppe.object_version_number,
p_effective_date => l_eff_dt,
p_effective_start_date => l_ppe.effective_end_date,
p_effective_end_date => l_ppe.effective_start_date,
p_datetrack_mode => l_datetrack_mode
);
ben_prtt_prem_api.delete_prtt_prem
(p_validate => false,
p_prtt_prem_id => l_ppe.prtt_prem_id,
p_object_version_number => l_ppe.object_version_number,
p_effective_date => l_ppe_dt_to_use, /*l_eff_dt*/
p_effective_start_date => l_ppe.effective_end_date,
p_effective_end_date => l_ppe.effective_start_date,
p_datetrack_mode => l_ppe_datetrack_mode /*l_datetrack_mode*/
);
if (l_datetrack_mode = hr_api.g_delete and
l_pen.effective_end_date > l_eff_dt) or
l_datetrack_mode <> hr_api.g_delete then
--
-- 4663971
open c_pen_ovn( p_prtt_enrt_rslt_id,l_eff_dt ) ;
l_datetrack_mode := hr_api.g_update ;
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_validate => FALSE
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_per_in_ler_id => l_pen.per_in_ler_id
,p_ler_id => l_pen.ler_id
,p_enrt_cvg_thru_dt => l_temp_date
,p_rplcs_sspndd_rslt_id => null
,p_object_version_number => l_pen_ovn.object_version_number
,p_effective_date => l_eff_dt
,p_datetrack_mode => l_datetrack_mode --4663971
,p_multi_row_validate => p_multi_row_validate
,p_business_group_id => p_business_group_id
);
ben_prtt_enrt_result_api.delete_prtt_enrt_result
(p_validate => FALSE
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_object_version_number => l_pen_ovn.object_version_number -- 2386000
,p_effective_date => l_eff_dt
,p_datetrack_mode => l_datetrack_mode
,p_multi_row_validate => p_multi_row_validate);
(p_action => 'DELETE'
,p_old_pl_id => l_pen.pl_id
,p_old_oipl_id => l_pen.oipl_id
,p_old_enrt_cvg_strt_dt => l_pen.enrt_cvg_strt_dt
,p_old_enrt_cvg_end_dt => l_pen.enrt_cvg_thru_dt
,p_pl_id => l_pen.pl_id
,p_oipl_id => l_pen.oipl_id
,p_enrt_cvg_strt_dt => l_pen.enrt_cvg_strt_dt
,p_enrt_cvg_end_dt => l_eff_dt
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_per_in_ler_id => l_pen.per_in_ler_id
,p_person_id => l_pen.person_id
,p_business_group_id => p_business_group_id
,p_effective_date => l_eff_dt
);
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_validate => FALSE
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_per_in_ler_id => l_corr_pil_id
,p_ler_id => l_pen.ler_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_correction
,p_multi_row_validate => FALSE
,p_business_group_id => p_business_group_id
);
update ben_le_clsn_n_rstr
set effective_start_date = p_effective_start_date
where per_in_ler_id = l_corr_pil_id
and bkup_tbl_id = p_prtt_enrt_rslt_id
and bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F_CORR';
delete_enrollment(
p_validate => false,
p_per_in_ler_id => p_per_in_ler_id,
p_prtt_enrt_rslt_id => l_interim.prtt_enrt_rslt_id,
p_effective_start_date => l_interim.effective_start_date,
p_effective_end_date => l_interim.effective_end_date,
p_object_version_number => l_interim.object_version_number,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date,
p_datetrack_mode => p_datetrack_mode,
p_source => 'delete_enrollment',
p_lee_rsn_id => p_lee_rsn_id,
p_enrt_perd_id => p_enrt_perd_id,
p_multi_row_validate => false);
ROLLBACK TO delete_enrollment;
ROLLBACK TO delete_enrollment;
ROLLBACK TO delete_enrollment;
ROLLBACK TO delete_enrollment;
end delete_enrollment;
,p_rslt_delete_flag in boolean default FALSE
) is
cursor c_old_bnf
is
select pbf.pl_bnf_id
,pbf.effective_start_date
,pbf.effective_end_date
,pbf.object_version_number
,pbf.dsgn_strt_dt
,pbf.dsgn_thru_dt
,pbf.per_in_ler_id
from ben_pl_bnf_f pbf,
ben_per_in_ler pil
where pbf.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pbf.business_group_id = p_business_group_id
and p_effective_date between pbf.effective_start_date
and pbf.effective_end_date
and pil.per_in_ler_id=pbf.per_in_ler_id
and pil.business_group_id=pbf.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select pbf.pl_bnf_id
,pbf.object_version_number
from ben_pl_bnf_f pbf,
ben_per_in_ler pil
where pbf.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date
between pbf.effective_start_date
and pbf.effective_end_date
and pil.per_in_ler_id=pbf.per_in_ler_id
and pil.business_group_id=pbf.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
l_update boolean;
l_update_override boolean;
l_update_change_insert boolean;
l_datetrack_mode := hr_api.g_update;
and not p_rslt_delete_flag
and l_datetrack_mode<>hr_api.g_zap
) then
--
-- If Coverage started, then end the coverage by set the coverage
-- thru date.
--
dt_api.find_dt_upd_modes
(p_effective_date => p_effective_date,
p_base_table_name => 'BEN_PL_BNF_F',
p_base_key_column => 'PL_BNF_ID',
p_base_key_value => orec.PL_BNF_ID,
p_correction => l_correction,
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update_override then
--
l_datetrack_mode := hr_api.g_update_override;
elsif l_update then
--
l_datetrack_mode := hr_api.g_update;
hr_utility.set_location('calling update mode' || l_datetrack_mode , 192 );
ben_PLAN_BENEFICIARY_api.update_PLAN_BENEFICIARY
(p_validate => FALSE
,p_business_group_id => p_business_group_id
,p_pl_bnf_id => orec.pl_bnf_id
,p_effective_start_date => orec.effective_start_date
,p_effective_end_date => orec.effective_end_date
,p_dsgn_thru_dt => p_dsgn_thru_dt
,p_per_in_ler_id => l_per_in_ler_id
,p_object_version_number => orec.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_multi_row_actn => FALSE);
l_datetrack_mode := hr_api.g_delete;
if l_datetrack_mode = hr_api.g_delete and
p_effective_date = orec.effective_end_date then
--
-- Already end dated
--
null;
hr_utility.set_location('calling delete ' || l_datetrack_mode , 192 );
ben_plan_beneficiary_api.delete_plan_beneficiary
(p_validate => p_validate
,p_pl_bnf_id => orec.pl_bnf_id
,p_effective_start_date => orec.effective_start_date
,p_effective_end_date => orec.effective_end_date
,p_object_version_number => orec.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => nvl(l_datetrack_mode,p_datetrack_mode)
,p_multi_row_actn => FALSE
,p_business_group_id => p_business_group_id);
,p_rslt_delete_flag in boolean default FALSE
,p_called_from in varchar2 default 'bepenapi'
) is
cursor c_old_dpnt is
select ecd.elig_cvrd_dpnt_id
,ecd.effective_start_date
,ecd.effective_end_date
,ecd.dpnt_person_id
,ecd.cvg_strt_dt
,ecd.CVG_PNDG_FLAG
,ecd.cvg_thru_dt
,ecd.object_version_number
,ecd.per_in_ler_id
from ben_elig_cvrd_dpnt_f ecd,
ben_per_in_ler pil
where ecd.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
and ecd.cvg_strt_dt is not null
-- and ecd.cvg_thru_dt = hr_api.g_eot
and ecd.business_group_id=p_business_group_id
-- and p_effective_date between
-- ecd.effective_start_date and ecd.effective_end_date
and p_effective_date <= ecd.effective_end_date
and pil.per_in_ler_id(+)=ecd.per_in_ler_id
and pil.business_group_id(+)=ecd.business_group_id
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') or
-- outer join condition below
-- above is case where found pil,
-- below is case where pil not found and want row
pil.per_in_ler_stat_cd is null)
/* Bug 4520785 Pick only those cvrd dependent records where either cvg thru date hasn't
been end dated yet or cvg thru date is more than effective date */
and ecd.elig_cvrd_dpnt_id not in (select ecd1.elig_cvrd_dpnt_id
from ben_elig_cvrd_dpnt_f ecd1
where ecd1.elig_cvrd_dpnt_id = ecd.elig_cvrd_dpnt_id
and ecd1.cvg_thru_dt <= p_effective_date
and p_effective_date <= ecd1.effective_end_date ) -- Bug 4710937
/* End Bug 4520785 */
order by ecd.elig_cvrd_dpnt_id,
ecd.effective_start_date;
select pcp.PRMRY_CARE_PRVDR_ID
,pcp.EFFECTIVE_START_DATE
,pcp.EFFECTIVE_END_DATE
,pcp.PRTT_ENRT_RSLT_ID
,pcp.BUSINESS_GROUP_ID
,pcp.OBJECT_VERSION_NUMBER
from ben_prmry_care_prvdr_f pcp
where business_group_id = p_business_group_id
and elig_cvrd_dpnt_id = c_elig_cvrd_dpnt_id
and p_effective_date between effective_start_date
and effective_end_date
;
select pcp.PRMRY_CARE_PRVDR_ID
,pcp.EFFECTIVE_START_DATE
,pcp.EFFECTIVE_END_DATE
,pcp.PRTT_ENRT_RSLT_ID
,pcp.BUSINESS_GROUP_ID
,pcp.OBJECT_VERSION_NUMBER
from ben_prmry_care_prvdr_f pcp
where pcp.business_group_id = p_business_group_id
and pcp.elig_cvrd_dpnt_id = c_elig_cvrd_dpnt_id
and p_effective_date < pcp.effective_start_date
and NVL(pcp.effective_end_date, hr_api.g_eot) = hr_api.g_eot
;
l_update boolean;
l_update_override boolean;
l_update_change_insert boolean;
l_datetrack_mode := hr_api.g_update;
and not p_rslt_delete_flag
and l_datetrack_mode<>hr_api.g_zap
) then
--
-- If Coverage started, then end the coverage by set the coverage
-- thru date.
--
dt_api.find_dt_upd_modes
(p_effective_date => p_effective_date,
p_base_table_name => 'BEN_ELIG_CVRD_DPNT_F',
p_base_key_column => 'ELIG_CVRD_DPNT_ID',
p_base_key_value => orec.elig_cvrd_dpnt_id,
p_correction => l_correction,
p_update => l_update,
p_update_override => l_update_override,
p_update_change_insert => l_update_change_insert);
if l_update_override then
--
l_datetrack_mode := hr_api.g_update_override;
elsif l_update then
--
l_datetrack_mode := hr_api.g_update;
/* Bug 2597005: Reverse changes (Update the coverages of Dependents in correction mode )*/
-- l_datetrack_mode := hr_api.g_correction;
/* Bug 2597005: Reverse changes (Update the coverages of Dependents in correction mode) */
--
else
--
l_datetrack_mode := hr_api.g_correction;
ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt
(p_validate => FALSE
,p_business_group_id => p_business_group_id
,p_elig_cvrd_dpnt_id => orec.elig_cvrd_dpnt_id
,p_effective_start_date => orec.effective_start_date
,p_effective_end_date => orec.effective_end_date
,p_cvg_thru_dt => p_cvg_thru_dt
,p_per_in_ler_id => l_per_in_ler_id
,p_object_version_number => orec.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_multi_row_actn => FALSE);
l_datetrack_mode := hr_api.g_delete;
if l_datetrack_mode = hr_api.g_delete and
p_effective_date = orec.effective_end_date then
--
-- Already end dated
--
null;
hr_utility.set_location('Delete prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id, 15);
ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
(P_VALIDATE => FALSE
,P_PRMRY_CARE_PRVDR_ID => l_pcp.prmry_care_prvdr_id
,P_EFFECTIVE_START_DATE => l_pcp.effective_start_date
,P_EFFECTIVE_END_DATE => l_pcp.effective_end_date
,P_OBJECT_VERSION_NUMBER => l_pcp.object_version_number
,P_EFFECTIVE_DATE => l_effective_date
,P_DATETRACK_MODE => l_datetrack_mode
,p_called_from => 'delete_enrollment'
);
ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
(P_VALIDATE => FALSE
,P_PRMRY_CARE_PRVDR_ID => l_pcp_future.prmry_care_prvdr_id
,P_EFFECTIVE_START_DATE => l_pcp_future.effective_start_date
,P_EFFECTIVE_END_DATE => l_pcp_future.effective_end_date
,P_OBJECT_VERSION_NUMBER => l_pcp_future.object_version_number
,P_EFFECTIVE_DATE => l_pcp_effective_date
,P_DATETRACK_MODE => hr_api.g_zap
,p_called_from => 'delete_enrollment'
);
ben_elig_cvrd_dpnt_api.delete_elig_cvrd_dpnt
(p_validate => FALSE
,p_elig_cvrd_dpnt_id => orec.elig_cvrd_dpnt_id
,p_effective_start_date => orec.effective_start_date
,p_business_group_id => p_business_group_id
,p_effective_end_date => orec.effective_end_date
,p_object_version_number => orec.object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_multi_row_actn => FALSE
,p_called_from => p_called_from
);
select acrs_ptip_cvg_id,
mx_cvg_alwd_amt,
mn_cvg_alwd_amt
from ben_acrs_ptip_cvg_f
where pgm_id = p_pgm_id
and --p_effective_date between BUG 3051674
p_lf_evt_ocrd_dt between
effective_start_date and effective_end_date
and business_group_id = p_business_group_id;
select sum(prt.bnft_amt)
from ben_prtt_enrt_rslt_f prt,
ben_ptip_f ptp
where prt.pgm_id = p_pgm_id
and prt.person_id = p_person_id
and prt.sspndd_flag = 'N' -- # 3626176
and prt.prtt_enrt_rslt_stat_cd is null
and prt.effective_end_date = hr_api.g_eot
and nvl(prt.enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
and p_effective_date between
prt.effective_start_date and prt.effective_end_date
and prt.business_group_id = p_business_group_id
and p_lf_evt_ocrd_dt between -- BUG 3051674 p_effective_date between
ptp.effective_start_date and ptp.effective_end_date
and ptp.acrs_ptip_cvg_id=p_acrs_ptip_cvg_id
and ptp.ptip_id=prt.ptip_id
and ptp.business_group_id=prt.business_group_id
;
select sum(prt.bnft_amt)
from ben_prtt_enrt_rslt_f prt,
ben_ptip_f ptp
where prt.pgm_id = p_pgm_id
and prt.person_id = p_person_id
and ( prt.sspndd_flag = 'Y' or (
prt.sspndd_flag = 'N' and not exists (
select 'x'
from ben_prtt_enrt_rslt_f prt1
where prt1.person_id = p_person_id
and prt1.pgm_id = p_pgm_id
and PRT1.rplcs_sspndd_rslt_id = prt.prtt_enrt_rslt_id
and prt1.per_in_ler_id = prt.per_in_ler_id
and prt1.sspndd_flag = 'Y'
and p_lf_evt_ocrd_dt between
prt1.effective_start_date and prt1.effective_end_date
and prt1.effective_end_date = hr_api.g_eot
and prt1.enrt_cvg_thru_dt = hr_api.g_eot
and prt1.prtt_enrt_rslt_stat_cd is null)))
and prt.prtt_enrt_rslt_stat_cd is null
and prt.effective_end_date = hr_api.g_eot
and prt.enrt_cvg_thru_dt = hr_api.g_eot
and p_effective_date between
prt.effective_start_date and prt.effective_end_date
and prt.business_group_id = p_business_group_id
and p_lf_evt_ocrd_dt between -- BUG 3051674 p_effective_date between
ptp.effective_start_date and ptp.effective_end_date
and ptp.acrs_ptip_cvg_id=p_acrs_ptip_cvg_id
and ptp.ptip_id=prt.ptip_id
and ptp.business_group_id=prt.business_group_id
;
select name
from ben_pl_typ_f
where pl_typ_id in
( select pl_typ_id
from ben_ptip_f
where pgm_id = p_pgm_id
and acrs_ptip_cvg_id = p_acrs_ptip_cvg_id
and business_group_id = p_business_group_id)
and p_lf_evt_ocrd_dt between
effective_start_date and effective_end_date;
select
lf_evt_ocrd_dt
from
ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id ;
Select r.prtt_enrt_rslt_id
,r.effective_start_date
,r.effective_end_date
,r.object_version_number
,r.pl_id
,r.pl_typ_id
,r.oipl_id
,r.pgm_id
,r.erlst_deenrt_dt
,r.person_id
,oipl.opt_id
,r.ptip_id
,r.enrt_mthd_cd --Bug 2200139
From ben_prtt_enrt_rslt_f r,
ben_oipl_f oipl,
ben_elig_per_elctbl_chc c
Where c.per_in_ler_id = p_per_in_ler_id
and r.prtt_enrt_rslt_id = c.prtt_enrt_rslt_id
and r.per_in_ler_id <> c.per_in_ler_id
and r.person_id = p_person_id
and nvl(r.pgm_id,-1) = p_pgm_id
and r.effective_end_date = hr_api.g_eot
and nvl(enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
and r.prtt_enrt_rslt_stat_cd is null
and c.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and c.elctbl_flag = 'Y'
/* BUG 3939785
and (r.ENRT_OVRID_THRU_DT is null or
r.ENRT_OVRID_THRU_DT < p_effective_date)
*/
and ( r.enrt_ovridn_flag = 'N' /* Bug 3958064 */
or
( r.enrt_ovridn_flag = 'Y' and nvl(r.ENRT_OVRID_THRU_DT,hr_api.g_eot) <= p_effective_date )
)
and oipl.oipl_id(+)=r.oipl_id
and oipl.business_group_id(+)=r.business_group_id
and p_effective_date between
oipl.effective_start_date(+) and oipl.effective_end_date(+)
--
-- Bug 2600087 Added for finding the results which are not found
-- when linked with epe records
union
Select r.prtt_enrt_rslt_id
,r.effective_start_date
,r.effective_end_date
,r.object_version_number
,r.pl_id
,r.pl_typ_id
,r.oipl_id
,r.pgm_id
,r.erlst_deenrt_dt
,r.person_id
,oipl.opt_id
,r.ptip_id
,r.enrt_mthd_cd
From ben_prtt_enrt_rslt_f r,
ben_oipl_f oipl
Where r.per_in_ler_id <> p_per_in_ler_id
and r.person_id = p_person_id
and nvl(r.pgm_id,-1) = p_pgm_id
and r.effective_end_date = hr_api.g_eot
and nvl(enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
and r.prtt_enrt_rslt_stat_cd is null
and r.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
/* BUG 3939785
and (r.ENRT_OVRID_THRU_DT is null or
r.ENRT_OVRID_THRU_DT < p_effective_date)
*/
and ( r.enrt_ovridn_flag = 'N' /* Bug 3958064 */
or
( r.enrt_ovridn_flag = 'Y' and nvl(r.ENRT_OVRID_THRU_DT,hr_api.g_eot) <= p_effective_date )
)
and oipl.oipl_id(+)=r.oipl_id
and oipl.business_group_id(+)=r.business_group_id
and p_effective_date between
oipl.effective_start_date(+) and oipl.effective_end_date(+)
--bug#2602124
and not exists
(
select k.prtt_enrt_rslt_id
from ben_elig_per_elctbl_chc k
Where k.per_in_ler_id = p_per_in_ler_id
and k.prtt_enrt_rslt_id = r.prtt_enrt_rslt_id
and k.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
)
--bug 4262697 We need to delete only if there are electable choices exists in this life event
-- otherwise we don't need to delete.
and exists
(
select k.elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc k
Where k.per_in_ler_id = p_per_in_ler_id
and k.pl_id = r.pl_id
and k.pgm_id= p_pgm_id
and ((k.oipl_id is null and r.oipl_id is null) or
(k.oipl_id = r.oipl_id )
)
)
;
Select r.prtt_enrt_rslt_id
,r.effective_start_date
,r.effective_end_date
,r.object_version_number
,r.pl_id
,r.pl_typ_id
,r.oipl_id
,r.pgm_id
,r.erlst_deenrt_dt
,r.person_id
,oipl.opt_id
,r.ptip_id
,r.enrt_mthd_cd
From ben_prtt_enrt_rslt_f r
,ben_elig_per_elctbl_chc c
,ben_oipl_f oipl
,ben_pil_elctbl_chc_popl pel
Where c.per_in_ler_id = p_per_in_ler_id
and r.prtt_enrt_rslt_id = c.prtt_enrt_rslt_id
and r.per_in_ler_id <> c.per_in_ler_id
and r.person_id = p_person_id
and r.prtt_enrt_rslt_stat_cd is null
and r.pgm_id is NULL
and pel.pgm_id is NULL
and (pel.elcns_made_dt is not null or pel.dflt_asnd_dt is not NULL)
and pel.pl_id = r.pl_id
and c.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id
and r.effective_end_date = hr_api.g_eot
and nvl(enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
and c.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and c.elctbl_flag = 'Y'
/*
and (r.ENRT_OVRID_THRU_DT is null or
r.ENRT_OVRID_THRU_DT < p_effective_date)
*/
and ( r.enrt_ovridn_flag = 'N' /* Bug 3958064 */
or
( r.enrt_ovridn_flag = 'Y' and nvl(r.ENRT_OVRID_THRU_DT,hr_api.g_eot) <= p_effective_date )
)
and oipl.oipl_id(+)=r.oipl_id
and oipl.business_group_id(+)=r.business_group_id
and p_effective_date between
oipl.effective_start_date(+) and oipl.effective_end_date(+)
-- BUG: 3904792: MAINLINE FIX FOR 3894240: WHEN UPDATING A SAVINGS PLAN RATE SYSTEM IS END DATE
AND NOT EXISTS
(SELECT NULL
FROM ben_ler_f ler,
ben_pl_f pln
WHERE ler.ler_id = r.ler_id
AND ler.typ_cd = 'SCHEDDU'
AND pln.SVGS_PL_FLAG = 'Y'
and pln.pl_id = r.pl_id
AND p_effective_date between pln.effective_start_date
AND pln.effective_end_date
AND p_effective_date between ler.effective_start_date
AND ler.effective_end_date
)
-- BUG: 3904792: ENDS --
;
select pen.prtt_enrt_rslt_id,
pen.object_version_number,
pen.effective_start_date
from ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id = p_per_in_ler_id
and pen.pgm_id = p_pgm_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.no_lngr_elig_flag = 'Y'
and pen.effective_end_date = hr_api.g_eot
and nvl(enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
and pen.business_group_id = p_business_group_id;
select distinct pln.pl_typ_id
from ben_pil_elctbl_chc_popl pel,
ben_pl_f pln
where pel.per_in_ler_id = p_per_in_ler_id
and pel.pgm_id is null
and (pel.elcns_made_dt is not null or pel.dflt_asnd_dt is not NULL)
and pln.pl_id = nvl(pel.pl_id, -1)
and p_effective_date between
pln.effective_start_date and pln.effective_end_date
and pel.business_group_id = p_business_group_id
and pln.business_group_id = p_business_group_id;
select pen.prtt_enrt_rslt_id,
pen.object_version_number,
pen.effective_start_date
from ben_prtt_enrt_rslt_f pen
where pen.pgm_id is null
and pl_typ_id = l_pl_typ_id
and pen.per_in_ler_id = p_per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.no_lngr_elig_flag = 'Y'
and pen.effective_end_date = hr_api.g_eot
and nvl(enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
and pen.business_group_id = p_business_group_id;
select acrs_ptip_cvg_id,
mx_cvg_alwd_amt,
mn_cvg_alwd_amt
from ben_acrs_ptip_cvg_f
where pgm_id = p_pgm_id
and --p_effective_date between BUG 3051674
l_lf_evt_ocrd_dt between
effective_start_date and effective_end_date
and business_group_id = p_business_group_id;
select name
from ben_pl_typ_f
where pl_typ_id in
( select pl_typ_id
from ben_ptip_f
where pgm_id = p_pgm_id
and acrs_ptip_cvg_id = p_acrs_ptip_cvg_id
and business_group_id = p_business_group_id)
and l_lf_evt_ocrd_dt between
effective_start_date and effective_end_date;
select sum(prt.bnft_amt)
from ben_prtt_enrt_rslt_f prt,
ben_ptip_f ptp
where prt.pgm_id = p_pgm_id
and prt.person_id = p_person_id
and prt.sspndd_flag = 'N' -- # 3626176
and prt.prtt_enrt_rslt_stat_cd is null
and prt.effective_end_date = hr_api.g_eot
and nvl(prt.enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
and p_effective_date between
prt.effective_start_date and prt.effective_end_date
and prt.business_group_id = p_business_group_id
and l_lf_evt_ocrd_dt between -- BUG 3051674 p_effective_date between
ptp.effective_start_date and ptp.effective_end_date
and ptp.acrs_ptip_cvg_id=p_acrs_ptip_cvg_id
and ptp.ptip_id=prt.ptip_id
and ptp.business_group_id=prt.business_group_id
;
select sum(prt.bnft_amt)
from ben_prtt_enrt_rslt_f prt,
ben_ptip_f ptp
where prt.pgm_id = p_pgm_id
and prt.person_id = p_person_id
and ( prt.sspndd_flag = 'Y' or (
prt.sspndd_flag = 'N' and not exists (
select 'x'
from ben_prtt_enrt_rslt_f prt1
where prt1.person_id = p_person_id
and prt1.pgm_id = p_pgm_id
and PRT1.rplcs_sspndd_rslt_id = prt.prtt_enrt_rslt_id
and prt1.per_in_ler_id = prt.per_in_ler_id
and prt1.sspndd_flag = 'Y'
and l_lf_evt_ocrd_dt between
prt1.effective_start_date and prt1.effective_end_date
and prt1.effective_end_date = hr_api.g_eot
and prt1.enrt_cvg_thru_dt = hr_api.g_eot
and prt1.prtt_enrt_rslt_stat_cd is null)))
and prt.prtt_enrt_rslt_stat_cd is null
and prt.effective_end_date = hr_api.g_eot
and prt.enrt_cvg_thru_dt = hr_api.g_eot
and p_effective_date between
prt.effective_start_date and prt.effective_end_date
and prt.business_group_id = p_business_group_id
and l_lf_evt_ocrd_dt between -- BUG 3051674 p_effective_date between
ptp.effective_start_date and ptp.effective_end_date
and ptp.acrs_ptip_cvg_id=p_acrs_ptip_cvg_id
and ptp.ptip_id=prt.ptip_id
and ptp.business_group_id=prt.business_group_id
;
Select distinct r.prtt_enrt_rslt_id
,r.effective_start_date
,r.effective_end_date
,r.object_version_number
,r.pl_id
,r.pl_typ_id
,r.oipl_id
,r.pgm_id
,r.enrt_cvg_strt_dt
,c.elig_per_elctbl_chc_id
,c.dpnt_cvg_strt_dt_cd
,c.dpnt_cvg_strt_dt_rl
,pil.ler_id
,c.alws_dpnt_dsgn_flag
,pl.bnf_dsgn_cd
From ben_prtt_enrt_rslt_f r,
ben_elig_per_elctbl_chc c,
ben_per_in_ler pil,
ben_pl_f pl
Where r.pl_typ_id = p_pl_typ_id
and r.per_in_ler_id = p_per_in_ler_id
and r.pgm_id = p_pgm_id
and r.person_id = p_person_id
and r.effective_end_date = hr_api.g_eot
and r.enrt_cvg_thru_dt = hr_api.g_eot
and r.prtt_enrt_rslt_stat_cd is null
and r.prtt_enrt_rslt_id = c.prtt_enrt_rslt_id
and r.per_in_ler_id = c.per_in_ler_id
and c.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and pil.per_in_ler_id = c.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and pl.pl_id = r.pl_id
and p_effective_date between
pl.effective_start_date and pl.effective_end_date
and ((c.alws_dpnt_dsgn_flag = 'Y'
and not exists (select 'x' from ben_elig_cvrd_dpnt_f pdp
where pdp.prtt_enrt_rslt_id = r.prtt_enrt_rslt_id
and p_effective_date between
pdp.effective_start_date and pdp.effective_end_date))
OR
(pl.bnf_dsgn_cd is not null
and not exists (select 'x' from ben_pl_bnf_f pbn
where pbn.prtt_enrt_rslt_id = r.prtt_enrt_rslt_id
and p_effective_date between
pbn.effective_start_date and pbn.effective_end_date)));
select ler.typ_cd
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id = p_per_in_ler_id
and pil.ler_id = ler.ler_id
and pil.business_group_id = p_business_group_id
and pil.business_group_id = ler.business_group_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date;
,p_datetrack_mode => hr_api.g_update -- note below
,p_process_dpnt => l_process_dpnt
,p_process_bnf => l_process_bnf);
delete_enrollment
(p_validate => FALSE
,p_per_in_ler_id => p_per_in_ler_id
,p_prtt_enrt_rslt_id => l_rec(i).prtt_enrt_rslt_id
,p_business_group_id => p_business_group_id
,p_effective_start_date => l_rec(i).effective_start_date
,p_effective_end_date => l_rec(i).effective_end_date
,p_object_version_number => l_rec(i).object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => hr_api.g_delete
,p_multi_row_validate => FALSE
,p_source => 'bepenapi'
);
delete_enrollment
(p_validate => FALSE
,p_per_in_ler_id => p_per_in_ler_id
,p_prtt_enrt_rslt_id => rslt.prtt_enrt_rslt_id
,p_business_group_id => p_business_group_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => rslt.object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => hr_api.g_delete
,p_multi_row_validate => FALSE
,p_source => 'bepenapi'
);
delete_enrollment
(p_validate => FALSE
,p_per_in_ler_id => p_per_in_ler_id
,p_prtt_enrt_rslt_id => rslt.prtt_enrt_rslt_id
,p_business_group_id => p_business_group_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => rslt.object_version_number
,p_effective_date => l_effective_date
,p_datetrack_mode => hr_api.g_delete
,p_multi_row_validate => FALSE
,p_source => 'bepenapi'
);
ben_newly_ineligible.defer_delete_enrollment -- Defer ENH
(p_per_in_ler_id => p_per_in_ler_id,
p_person_id => p_person_id,
p_business_group_id => p_business_group_id,
p_effective_date => l_lf_evt_ocrd_dt
);
Procedure update_person_type_usages
(p_person_id in number,
p_business_group_id in number,
p_effective_date in date
) is
--
l_proc varchar2(72); -- := g_package||'update_person_type_usages';
l_proc := g_package||'update_person_type_usages';
End update_person_type_usages;
procedure delete_enrollment_w
(p_validate in boolean default false
,p_per_in_ler_id in number default NULL
,p_lee_rsn_id in number default NULL
,p_enrt_perd_id in number default NULL
,p_prtt_enrt_rslt_id in number
,p_business_group_id in number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_object_version_number in out nocopy number
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_multi_row_validate in boolean default TRUE
,p_source in varchar2 default null
,p_enrt_cvg_thru_dt in date default null
,p_mode in varchar2 default null)
is
l_proc varchar2(72) := g_package||'delete_enrollment_w';
delete_enrollment
(p_validate => p_validate
,p_per_in_ler_id => p_per_in_ler_id
,p_lee_rsn_id => p_lee_rsn_id
,p_enrt_perd_id => p_enrt_perd_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_business_group_id => p_business_group_id
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_multi_row_validate => p_multi_row_validate
,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt
,p_mode => p_mode);
hr_utility.set_location ('Application Error in delete_enrollment_w.', 88);
hr_utility.set_location ('Other Error in delete_enrollment_w : '|| sqlerrm , 89);
end delete_enrollment_w;