[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
2) Updates new enrollment action item(s) when an enrollment is changed.
3) Updates enrollment when an action item is completed in Designate Dpnt,
Desginate Beneficiary or Certification forms.
4) Create Certifications required for a person to complete action items.
5) Suspends an enrollment when an action item is created.
--------------------------------------------------------------------------------
History
-------
Version Date Author Comment
-------+----------+----------------+------------------------------------------
110.0 22 Apr 98 J Schneider Created.
110.1 06 Jun 98 S Tee Fixed invalid cursor when creating a BNF
action item and also BNFADDNL cursor,
c_cntngnt_types as it was not selecting
any rows.
110.2 06 Jun 98 J Schneider fixed DD if no dependents. changed
get_enrt_actn_id to return object_version_
number for updates.
110.3 08 Jun 98 J Schneider change to write multiple DDCTFN, BNFCTFN
added deenrollment proc
110.4 09 Jun 98 J Schneider added cmpltd_dt support get_enrt_actn_id
p_suspend_flag added
110.5 10 Jun 98 J Schneider uncommented exit
110.6 11 Jun 98 J Schneider moved suspend_actn stuff to API ben_prtt_
enrt_actn_f so all action items will
suspend enrt if new or cmpltd_dt is NULL
110.7 13 Jun 98 J Schneider l_g_suspend,l_suspend_flag rechecked for
problems
110.8 15 Jun 98 J Schneider ben_dsgn_rqmt_f now uses oipl_id
110.9 15 Jun 98 J Schneider redo oipl pl dsgn_rqmt
110.10 16 Jun 98 J Schneider dsgn_rqmt needs more work for DDADDNL
110.11 17 Jun 98 J Schneider recheck for DDADDNL if no dependents found
110.12 18 Jun 98 J Schneider return suspend flag at all times
110.13 18 Jun 98 J Schneider 110.13 typo
110.14 18 Jun 98 J Schneider DDADDNL redone
110.15 23 Jun 98 J Schneider fix DD and suspend mode
110.16 30 Jun 98 J Schneider datetrack_mode changes when doing updates,
get_enrt_actn_id a proc
110.17 07 Jul 98 J Mohapatra Added batch who cols in call to ben_cvrd_
dpnt_ctfn_prvdd_api.create...
110.18 15 Jul 98 J Schneider add exception handlers, fix dpnt ctfns.
110.19 16 Jul 98 J Schneider dependent certifications changes
110.20 17 Jul 98 J Schneider post rslt flag added for calls to suspend
enrollment
110.21 22 Jul 98 maagrawa rslt_object_version_number added to calls
to suspend enrollment.
115.10 12 Oct 98 bbulusu Modified a lot of procedures and functions
that go against action items to handle
dpnts and bnfs. Added out nocopy parameters to
determine_action_items to send msgs back
to forms. Made dpnt dsgn rqmts work at
plan and option level. Modified call to
delete_prtt_enrt_actn_. Wrote the get_due_
date function. Modified the check_bnf_ttee
func to check if a trustee is assigned.
115.11 26 Oct 98 bbulusu Modified get_due_dt function
115.12 06 Nov 98 bbulusu Fixed determine_bnf_action_items to insert
an opitional action item if the dsgn rqmt
is optional. Fixed the get_due_date func
to not error out nocopy if popl_actn_typ notfound
115.13 09 Dec 98 bbulusu Fixed problem with warning flags not being
returned.
115.14 10-Dec-98 lmcdonal re-write complete_dependent_designation.
115.15 22-Dec-98 bbulusu Fixed determine_bnf_actn_items to complete
a BNF actn item if one already exists and
bnfs are found Fixed determine_dpnt_actn_
items to complete a DD actn item if one
already exists and dpns are found Added
calls to the close unresolved actn items
batch process. Removed complete_all_actn_
items procedure (redundant). Fixed determ
ine_bnf_actn_items to delete all other bnf
actn item when a BNF actn item is written.
Minor fix to complete_dpnt_dsgn don't
need to check to see if an actn item was
found before calling process_action_item.
115.16 22-Dec-98 bbulusu uncommented exit statement.
115.17 28-Dec-98 stee Set the default for post_rslt_flag = 'Y'.
115.18 30-Dec-98 bbulusu Removed call to cls unresolved actn items.
115.19 24-Feb-99 bbulusu Modified check_dpnt_ctfn. Modified cursors
c_dpnt_ctfn_pl, c_dpnt_ctfn_pgm, c_dpnt_
ctfn_ptip to select dpnt ctfns based on
the contact type. Added function
check_ctfns_defined.
115.20 02-Mar-99 bbulusu Modified check_bnf_ctfn and determine_bnf
_actn_items for bug fixes.
115.21 04-Mar-99 bbulusu Modified get_due_dt and determine_bnf_miss
_actn_items.
115.22 08-Mar-99 tmathers Changed the not-equal operators to <>.
115.23 23-Mar-99 bbulusu Modified determine_addnl_bnf
115.24 29-Apr-99 bbulusu Fixed determine_addnl_bnf to pick up all
bnfs that are not spouses.
115.25 29-APR-99 shdas Added pl_typ_id,opt_id,ler_id,
business_group_id organization_id,
assignment_id to the parameter list of
genutils.formula
115.26 03-may-99 shdas Added jurisdiction_code.
115.28 08-may-99 jcarpent Check ('VOIDD', 'BCKDT') for pil stat cd
115.29 13-may-99 jcarpent More of same.
115.30 02-Jun-99 bbulusu Fix to not call check_bnf_ttee if dob is
not found for the bnf.
115.31 07-Jul-99 maagrawa Modified determine_dpnt_miss_actn_items
to work for plans not in programs.
115.32 09-Jul-99 bbulusu Added checks for backed out nocopy life evts.
115.33 14-Jul-99 maagrawa Corrected opening of cursor c_nsc_ctfn_pl.
Use of bnf_ctfn_rqd_flag and
dpnt_dsgn_no_ctfn_rqd_flag negated.
115.34 20-JUL-99 Gperry genutils -> benutils package rename.
115.35 24-Aug-99 maagrawa Changes related to breaking of dependents
table.
115.36 07-Sep-99 tguy fixed call to pay_mag_util
115.37 27-Sep-99 maagrawa Modified Check procedures for prtt, dpnt,
bnf certifications. Created new procedure
process_new_ctfn_action to create/update
a action item when new ctfn is created.
115.38 27-Sep-99 tguy Modified call to determine_date to
accomadate new codes.
115.39 12-Oct-99 stee Fixed enrollment certification for
benefit amount. Cursor c_ecc2 was
selecting comparing choice id to bnft_id.
Also set the enrt_ctfn_warning flag.
115.40 12-Nov-99 lmcdonal Better debugging messages.
115.41 09-Dec-99 maagrawa Call the date routine to calculate the
action item due date only if the date
code is not null.
115.42 10-Jan-00 lmcdonal If bnf dsgn is optional, do not make the
BNF% actions rqd.
115.43 14-Jan-00 lmcdonal Bug 1148447: When dpnt ctfn's are at PTIP lvl,
process was not writing actions nor ctfns
because pgm and ptip ids were not being passed
to check_ctfns_defined. Also, removed use of
lack...flag, using rqd_flag instead for dpnts.
115.44 17-Jan-00 TMathers Fixed syntax error in previous fix.
115.45 31-Mar-00 MMogel Added tokens to messages to make messages
more meaningful to the user
115.46 31-mar-00 shdas don't always create a rqd action item for addnl dpnt(3629).
115.47 03-apr-00 shdas bring c_chc_flags cursor into process_dpnt_action_items
from complete_dependent_desigantion and determine_dpnt_action_items.
115.48 12-May-00 lmcdonal Bug 1249901. before writing bnft ctfn's, check
if cvg entered at enrt, then entered amt greater
than max-wout-ctfn.
115.49 06-Jul-00 kmahendr Bug 5140- sub-query to get contact_type returns more than
one row as dependent may be a contact to more than one
participant. All the sub-query to get contact_type were
modified by adding prtt_person_id in the where clause
115.50 07-Jul-00 kmahendr Bug#1319520 is fixed on version 115.46 for aera
115.51 07-Jul-00 kmahendr Changes made in 115.50 applied to 115.49
115.52 18-Jul-00 bbulusu Fixed bug #5386. Added code to check for
ctfns defined in the ben_ler_chg_dpnt_
cvg_ctfn_f table for dpnt change of life
events.
115.53 08-Aug-00 bbulusu Bug 5432. Ctfn being recreated in
subsequenct life event.
115.54 15-Aug-00 IAli PCP Required for Participant and for Dependent process is added
115.55 16-Aug-00 IAli Fixed the infinite loop in determine_pcp_action procedure
115.56 19-Aug-00 Shdas added process_pcp_actn_items and
and process_pcp_dpnt_actn_items.
115.57 25-Aug-00 Shdas added warnings for process_pcp_actn_items
and process_pcp_dpnt_actn_items.
115.58 28-Aug-00 cdaniels OraBug# 4988. Suppressed raising of
error 91457 when alws_dpnt_desgn_flag
and dpnt_dsgn_cd not found for a
specified enrt_rslt_id and effective
date. Defaulted these values to 'N'
and 'O', respectively, for the case
when not found.
115.59 29-Aug-00 jcarpent Merge of 57 and 58 changes. 57 changes
were not ready for primetime so leaped
now in synch.
115.60 06-Sep-00 jcarpent 1269016. Handling of future change dt mode.
115.61 07-Sep-00 jcarpent Changes from 115.60 based on version 115.58.
Leapfrog version not containing pcp stuff.
115.62 07-Sep-00 jcarpent Same as version
115.63 26-Oct-00 pbodla - Enhancement : Pass contact person id as
input value for "certification required
when rule" if the rule is for contacts.
115.64 27-Oct-00 pbodla - param1(CON_PERSON_ID) passed to formula
115.65 15-Nov-00 jcarpent Bug 1488666. Was creating same ctfn
2 times because both cursors ecc1+2
were returning the same row.
115.66 22-Nov-00 jcarpent Bug 1488666. Was creating same ctfn
when one was a benefit ctfn and one
was an enrt ctfn. No need for both.
115.67 29-Dec-00 ikasire bug fix 1491912
115.68 08-mar-01 ikasire bug fix 1421978 modified the pcp cursors
for dependent and participant
115.69 20-apr-01 ikasire bug 1421978 Only one action item for pcp
is created for all the dependents of the
prtt. This is fixed to get the pcp
action item one for each dependent.
Added p_elig_cvrd_dpnt_id parameter to the
determine_pcp_dpnt_actn_items procedure.
Also passed p_elig_cvrd_dpnt_id parameter
for the call to get_prtt_enrt_actn_id and
process_action_item procedures from the
determine_pcp_dpnt_actn_items procedure.
115.70 25-Apr-01 maagrawa Performance changes.
115.71 17-May-01 maagrawa More Performance changes.
115.72 17-May-01 maagrawa Added exit statement.
115.73 02-Jul-01 kmahendr Bug#1842614- increased l_pln_name variable to 80
115.74 27-aug-01 tilak bug:1949361 jurisdiction code is
derived inside benutils.formula.
115.75 26-Dec-01 pabodla bug:1857685 - Do not create dependent and
beneficiary related action items for
waive plans and options.
115.76 08-FEB-02 aprabhak added an action item for future salary
increase
115.77 21-FEB-02 ikasire bug 2228123 getting the suspend_flag in
determine_action_items procedure
115.78 02-MAR-02 aprabhak modified the salary cursor to pickup
only salary increase plan
115.79 08-MAR-02 aprabhak Modified the future salary cursor.
Removed the rate join.
115.80 14-Mar-02 rpillay UTF8 Changes Bug 2254683
115.81 30-Apr-02 kmahendr Added token to message 91832.
115.82 23-May-02 ikasire Bug 2389261 Inconsistent results due to
not exclusion on epe with bnft_prvdr_pool_id
from the cursors
115.83 30-May-02 ikasire Bug 2386000 Added p_called_from to the
delete_cvrd_dpnt_ctfn_prvdd call
115.84 08-Jun-02 pabodla Do not select the contingent worker
assignment when assignment data is
fetched.
115.85 30-jul-02 hnarayan bug 1169240 - added the function
check_bnf_actn_item_dfnd to check for
any action items defined for beneficiaries
115.86 26-DEC-02 kichoudh NOCOPY changes
115.87 13-feb-03 hnarayan hr_utility.set_location - 'if g_debug' changes
115.88 21-Mar-03 mmudigon Bug 2858700. Added dt condition and order
by clause to cursor c_dpnt_bnf_adrs.
115.89 24-Mar-03 mmudigon Bug 2858700 continued. Added order by
clause to cursor c_prtt_adrs.
115.90 22-Jul-03 ikasire Bug 3042379 dont create action items for the
interim enrollment. defensive code.
115.91 19-Aug-03 ikasire Bug 3105160 added raise in the exception clause.
115.92 25-aug-03 kmahendr Bug#3108422 - if ERL is the cvg_mlt_cd certification action item
is called.
115.93 26-aug-03 kmahendr Condition modified for ERL
115.94 08-aug-03 pbodla Bug 3183266 : For flat range mx_wout_ctfn_val
will be null, so use nvl around it.
115.95 17-oct-03 hmani Modified the arg passed to
write_new_bnf_ctfn_item call - Bug 3196152
115.96 05-Nov-03 tjesumic contact_type are expected to have more than one row for epnt
sub query failing due to =, now changed to in # 3228530
115.97 13-Nov-03 bmanyam Bug.3248711. Allowing Dpnt Dsgn Action item to get created,
when (epe.alws_dpnt_dsgn_flag = 'Y' OR epe.dpnt_dsgn_cd = 'R'
115.98 17-Dec-03 vvprabhu Added the assignment for g_debug at the start
of each public procedure
115.99 21-Jan-2003 vvprabhu Added the assignment for g_debug at the start
of each public Function
115.100 20-Feb-2004 kmahendr Bug#3442729 - added codes to set_cmpltd_dt
115.101 19-Apr-2004 bmanyam Bug# 3510501 - In the date_track_verify method,
set the date-track-mode to UPDATE, in the ELSE..part.
115.102 01-Jun-2004 kmahendr Bug#3643597 - added a new private procedure
check_ctfn_prvdd.
115.103 02-Jun-2004 kmahendr Bug#3643597 - cursor c_prtt_enrt_rslt modified.
115.104 03-jun-2004 kmahendr Bug#3643597 - cursor c_prtt_enrt_rslt modified.
115.105 01-jul-2004 bmanyam Bug#3730053 - cursor c_emp_only modified.
OIPL level DSGN_RQMTS override OPT level
115.106 01-jul-2004 kmahendr bug#3590524 - Added codes to check_ctfn_prvdd
115.107 21-jul-2004 rpgupta bug#3771346 - If waive flag not checked at option level, check at the plan level
115.108 29-jul-2004 kmahendr bug#3748133 - Added codes to check_ctfn_prvdd
115.109 17-Aug-2004 hmani bug#3806262 - p_crntly_enrd_flag can be 'N' for new enrollment
115.110 19-Aug-2004 kmahendr Optional certification changes
115.111 23-Aug-2004 mmudigon CFW. Added p_act_item flag
2534391 :NEED TO LEAVE ACTION ITEMS
115.112 26-aug-2004 nhunur gscc compliance
115.113 26-Aug-2004 abparekh Bug# 3854556 - Modified cursor c_dsgn_bnf
115.114 31-Aug-2004 abparekh Bug# 3851427 Added p_susp_if_ctfn_not_prvd_flag to function
check_ctfns_defined. Consider "Suspend Enrollment" flag at
LER_CHG_DPNT level when certifications are considered at
LER_CHG_DPNT level. Modified p_rqd flag parameter in write_new_action_item
115.115 31-Aug-2004 pbodla CFW. Several cursors modified to join per_in_ler
115.116 09-sep-2004 mmudigon CFW. p_act_item flag no longer needed
115.117 03-nov-2004 kmahendr Bug3976575- cursor c_ctfn_defined modified to pl_id and
added codes in write_new_action_items for handling message
115.118 01-Dec-2004 abparekh Bug 4039404 : Fixed cursor c_pl_name in write_new_action_item
115.119 30-dec-2004 nhunur 4031733 - No need to open cursor c_state.
115.120 18-Mar-2005 swjain 4241743 - Added cursor c_prev_actn_ler in determine_other_actn_items
and modified if conditions to create action items also when ler id is different
115.121 22-Mar-2005 swjain Removed show errors
115.122 12-Apr-2005 swjain 4241743 - Removed cursor c_prev_actn_ler and added c_enrt_actn
115.123 13-Jun-2005 rbingi Bug:4396160 - Curosr name is changed from c_emp_only to c_tot_mn_mx_dpnts_req
cursor query changed to selects the total no. of Min and Max Dpnts req for the CompObj.
Changed Procedure: determine_dpnt_actn_items sothat,
For both Min and Max = 0, Action Item will NOT be created.
For Min = 0 and Max > 0, Action Item will be created IF Elig dpnts exists
For both Min and Max > 0, Action Item will be created irrespective of Elig dpnts.
115.124 13-Jun-2005 rbingi Corrected previuos change to create Action Item when no DD record is defined.
115.125 13-Jun-2005 rbingi Corrected compilation errors had in previuos version
115.126 29-Jun-2005 ikasire Bug 4422667 getting into loop issue
115.127 13-Jul-2005 ikasire Bug 4454990 fixed for the code
115.128 02-Aug-2005 rgajula Bug No 4525608
115.129 10-Aug-2005 A new parameter l_ctfn_actn_warning_o
was defined in determine_action_items which is passed on to
process_dpnt_actn_items and inturn passed to determine_other_actn_items
as out parameters.The signatures of the procedures have been changed accordingly
This flag whould capture the certification required action warning
if dependent details have been furnished and plan level certification have
not been furnished.
115.130 31-Aug-2005 ikasire BUG 4558512 completion date fix in set_cmpltd_dt procedure
115.131 01-Sep-2005 ikasire BUG 4558512 more changes
115.132 15-sep-2005 rgajula Bug 4610971 Passed the p_business_group_id to the procedure
ben_determine_date.main in procedure get_due_date so that
it ill be available to Action Type Due Date rule .
115.133 27-Apr-2006 swjain Bug 5156111 : Added additional logic for certications for organizations
in procedure determine_additional_bnf
115.134 28-Apr-2006 swjain Bug 5156111 : Updated cursor c_nsc_ctfn_pl in procedure determine_additional_bnf
115.135 02-May-2006 swjain Bug 5156111 : Updated cursor c_spouse to check for BNF_MAY_DSGT_ORG_FLAG flag
115.136 29-jun-2006 nhunur bug 5362890 : Added date clauses for pcr in c_spouse
114.137 11-Aug-2006 abparekh Bug 5461171 : Modified clauses in CURSOR c_nsc_ctfn_pl
115.138 9/20/2006 gsehgal bug 5513339 : added cursor c_curr_ovn_of_actn to pass right ovn
to delete_action_item
115.139 10-Jan-2007 bmanyam 5736589: No need for to check for Option Restrictions
when ctfn_determine_cd = 'ENRAT', as certifications are
determined at bendenrr (check_ctfn_prvdd)
115.141 23-Feb-07 swjain Bug 5887665: In procedure determine_other_actn_items, for coverage
certifications, added code to evaluate suspended codes
115.142 27-Feb-07 swjain Additional changes in procedure determine_other_actn_items and added new
parameter p_enrt_r_bnft_ctfn_cd in call to procedure check_ctfn_prvdd.
115.143 2-May-2007 rgajula Bug 5998009 : Corrected the code in the procedure determine_dpnt_miss_actn_items so as to make the system behaviour
ideal when Formula for 'Dependent Certification Required' type is used
115.144 04-May-2007 swjain Bug 6022327: Updated cursor c_ctfn_exists in procedure write_new_prtt_ctfn_prvdd_item
115.145 18-May-2007 swjain Bug 5965415: In procedure check_ctfn_prvdd, updated the cursor c_ctfn_prvdd and
c_ctfn_prvdd2 to check if any certification received in past for the same plan-option
(and not based on pen_id as it changes when coverage amount changes)
115.146 19-Jun-2007 gsehgal bug 6010780: checking future rows in update and correction mode also
115.147
115.148
115.149 23-Aug-2007 rgajula Bug 6353069 : Modified the procedure process_new_ctfn_action to check for dpnt certification suspend flag at various levels.
115.150 24-Sep-2007 rtagarra Bug 6434143 : before suspending enrollment check for SUSP_IF_CTFN_NOT_PRVD_FLAG flag.
115.151 22-Feb-2008 rtagarra 6840074
115.152 22-Sep-2008 sallumwa Bug 7417593 : Modified the cursor c_ctfn_prvdd to check if the certification is already received
in the past or not for the same plan and option.
115.153 24-sep-2008 sallumwa Bug 7417474 : Modified the cursor c_prtt_enrt_rslt,so that it doesn't fetch the record if two options
from the same plan are enrolled.
-------------------------------------------------------------------------------------------------------------------------------------------
*/
--
-- Package Variables
--
g_debug boolean := hr_utility.debug_enabled;
select ldc.susp_if_ctfn_not_prvd_flag
from ben_ler_chg_dpnt_cvg_ctfn_f lcc,
ben_ler_chg_dpnt_cvg_f ldc,
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
and pen.ler_id = ldc.ler_id
and p_effective_date between ldc.effective_start_date
and ldc.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null
and ((p_lvl_cd = 'PTIP' and ldc.ptip_id = p_ptip_id) OR
(p_lvl_cd = 'PL' and ldc.pl_id = p_pl_id) OR
(p_lvl_cd = 'PGM' and ldc.pgm_id = p_pgm_id))
and ldc.ler_chg_dpnt_cvg_id = lcc.ler_chg_dpnt_cvg_id
and p_effective_date between lcc.effective_start_date
and lcc.effective_end_date
and (lcc.rlshp_typ_cd is null
or
lcc.rlshp_typ_cd in (select contact_type
from per_contact_relationships
where contact_person_id = p_dpnt_person_id
and person_id = p_person_id
and business_group_id = p_business_group_id
and p_effective_date
between nvl(date_start, p_effective_date)
and nvl(date_end, hr_api.g_eot)));
select 'x'
from ben_pgm_dpnt_cvg_ctfn_f
where pgm_id = p_pgm_id
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date
and (rlshp_typ_cd is null
or
rlshp_typ_cd in (select contact_type
from per_contact_relationships
where contact_person_id = p_dpnt_person_id
and person_id = p_person_id
and business_group_id = p_business_group_id
and p_effective_date
between nvl(date_start, p_effective_date)
and nvl(date_end, hr_api.g_eot)));
select 'x'
from ben_ptip_dpnt_cvg_ctfn_f
where ptip_id = p_ptip_id
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date
and (rlshp_typ_cd is null
or
rlshp_typ_cd in (select contact_type
from per_contact_relationships
where contact_person_id = p_dpnt_person_id
and person_id = p_person_id
and business_group_id = p_business_group_id
and p_effective_date
between nvl(date_start, p_effective_date)
and nvl(date_end, hr_api.g_eot)));
select 'x'
from ben_pl_dpnt_cvg_ctfn_f
where pl_id = p_pl_id
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date
and (rlshp_typ_cd is null
or
rlshp_typ_cd in (select contact_type
from per_contact_relationships
where contact_person_id = p_dpnt_person_id
and person_id = p_person_id
and business_group_id = p_business_group_id
and p_effective_date
between nvl(date_start, p_effective_date)
and nvl(date_end, hr_api.g_eot)));
select pat.popl_actn_typ_id,
pat.effective_start_date,
pat.effective_end_date,
pat.actn_typ_due_dt_cd,
pat.actn_typ_due_dt_rl
from ben_popl_actn_typ_f pat
where pat.pl_id = v_pl_id
and pat.actn_typ_id = p_actn_typ_id
and pat.business_group_id = p_business_group_id
and p_effective_date between pat.effective_start_date
and pat.effective_end_date;
select pat.popl_actn_typ_id,
pat.effective_start_date,
pat.effective_end_date,
pat.actn_typ_due_dt_cd,
pat.actn_typ_due_dt_rl
from ben_popl_actn_typ_f pat
where pat.pgm_id = v_pgm_id
and pat.actn_typ_id = p_actn_typ_id
and pat.business_group_id = p_business_group_id
and p_effective_date between pat.effective_start_date
and pat.effective_end_date;
select epe.elig_per_elctbl_chc_id,
epe.pl_id,
epe.pgm_id
from ben_prtt_enrt_rslt_f pen,
ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
and pen.per_in_ler_id = epe.per_in_ler_id
and pil.per_in_ler_id = epe.per_in_ler_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
select bat.actn_typ_id
from ben_actn_typ bat
where bat.type_cd = p_type_cd
and bat.business_group_id = p_business_group_id;
select pea.prtt_enrt_actn_id,
pea.cmpltd_dt,
pea.object_version_number
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.actn_typ_id = p_actn_typ_id
and pea.pl_bnf_id is null
and pea.elig_cvrd_dpnt_id is null
and pea.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and pea.business_group_id = p_business_group_id
and p_effective_date between pea.effective_start_date
and pea.effective_end_date ;
select pea.prtt_enrt_actn_id,
pea.cmpltd_dt,
pea.object_version_number
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.actn_typ_id = p_actn_typ_id
and pea.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and pea.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and pea.business_group_id = p_business_group_id
and p_effective_date between pea.effective_start_date
and pea.effective_end_date;
select pea.prtt_enrt_actn_id,
pea.cmpltd_dt,
pea.object_version_number
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.actn_typ_id = p_actn_typ_id
and pea.pl_bnf_id = p_pl_bnf_id
and pea.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and pea.business_group_id = p_business_group_id
and p_effective_date between pea.effective_start_date
and pea.effective_end_date;
elsif l_datetrack_mode = DTMODE_DELETE then
-- delete mode converts to update
l_datetrack_mode := DTMODE_UPDATE;
l_datetrack_mode := DTMODE_UPDATE;
l_datetrack_mode := DTMODE_UPDATE;
if l_datetrack_mode = DTMODE_UPDATE and
p_eff_date IS NOT NULL and
p_start_date IS NOT NULL and
p_eff_date = p_start_date then
l_datetrack_mode := hr_api.g_correction;
select tl.name actn_typ_name
from ben_actn_typ typ,
ben_actn_typ_tl tl
where p_actn_typ_id = typ.actn_typ_id
and typ.actn_typ_id = tl.actn_typ_id
and tl.language = userenv('lang')
and typ.type_cd <> 'BNF'
and typ.type_cd like 'BNF%'
and typ.business_group_id = p_business_group_id;
select pln.name,
pen.person_id
from ben_pl_f pln,
ben_prtt_enrt_rslt_f pen
where pln.pl_id = pen.pl_id
and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id /* Bug 4039404 */
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between pln.effective_Start_date
and pln.effective_end_date
and p_effective_date between pen.effective_Start_date
and pen.effective_end_date;
,p_program_update_date => sysdate);
select null
from ben_prtt_enrt_ctfn_prvdd_f ecp
where ecp.enrt_ctfn_typ_cd=p_enrt_ctfn_typ_cd
and ecp.enrt_ctfn_rqd_flag=p_rqd_flag
and p_effective_date between
ecp.effective_start_date and ecp.effective_end_date
and ecp.business_group_id=p_business_group_id
and ecp.prtt_enrt_actn_id=p_prtt_enrt_actn_id
and exists -- Bug 6022327: Changed from not exists to exists
( select pea.prtt_enrt_actn_id
from ben_prtt_enrt_actn_f pea,
ben_per_in_ler pil
where pea.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and 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 pea.effective_start_date
from ben_prtt_enrt_actn_f pea
where pea.prtt_enrt_actn_id = p_prtt_enrt_actn_id
and pea.business_group_id = p_business_group_id
and p_effective_date between pea.effective_start_date
and pea.effective_end_date;
select object_version_number
from ben_prtt_enrt_actn_f pea
where pea.prtt_enrt_actn_id = p_prtt_enrt_actn_id
and pea.business_group_id = p_business_group_id
and p_effective_date < pea.effective_start_date
;
select max(pcs.enrt_ctfn_recd_dt) ctfn_recd_dt
from ben_prtt_enrt_ctfn_prvdd_f pcs
where pcs.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pcs.prtt_enrt_actn_id = p_prtt_enrt_actn_id
and p_effective_date between
pcs.effective_start_date and pcs.effective_end_date;
select max(ccp.dpnt_dsgn_ctfn_recd_dt) ctfn_recd_dt
from ben_cvrd_dpnt_ctfn_prvdd_f ccp,
ben_prtt_enrt_actn_f pea
where ccp.prtt_enrt_actn_id = p_prtt_enrt_actn_id
and pea.prtt_enrt_actn_id = p_prtt_enrt_actn_id
and ccp.elig_cvrd_dpnt_id = pea.elig_cvrd_dpnt_id
and p_effective_date between
pea.effective_start_date and pea.effective_end_date
and p_effective_date between
ccp.effective_start_date and ccp.effective_end_date;
select max(pbc.bnf_ctfn_recd_dt) ctfn_recd_dt
from ben_pl_bnf_ctfn_prvdd_f pbc,
ben_prtt_enrt_actn_f pea
where pbc.prtt_enrt_actn_id = p_prtt_enrt_actn_id
and pea.prtt_enrt_actn_id = p_prtt_enrt_actn_id
and pbc.pl_bnf_id = pea.pl_bnf_id
and p_effective_date between
pea.effective_start_date and pea.effective_end_date
and p_effective_date between
pbc.effective_start_date and pbc.effective_end_date;
select object_version_number
from ben_prtt_enrt_actn_f
where prtt_enrt_actn_id = c_prtt_enrt_actn_id
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date;
ben_prtt_enrt_actn_api.delete_PRTT_ENRT_ACTN
(
p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_object_version_number => p_object_version_number
,p_unsuspend_enrt_flag => 'N'
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => l_dummy_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
hr_utility.set_location(' Future change exists fo update or correction ', 121 );
ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
(p_prtt_enrt_actn_id => p_prtt_enrt_actn_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date,
p_datetrack_mode => l_datetrack_mode2,
p_object_version_number => p_object_version_number,
p_unsuspend_enrt_flag => 'N',
p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
p_rslt_object_version_number => l_dummy_number,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date
);
ben_prtt_enrt_actn_api.update_prtt_enrt_actn
(p_cmpltd_dt => l_cmpltd_dt
,p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => p_rslt_object_version_number
,p_actn_typ_id => p_actn_typ_id
,p_rqd_flag => p_rqd_flag
,p_effective_date => p_effective_date
,p_post_rslt_flag => p_post_rslt_flag
,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 => p_object_version_number 6010780
,p_object_version_number => curr_ovn
,p_datetrack_mode => l_datetrack_mode
);
select pea.prtt_enrt_rslt_id,
pea.actn_typ_id,
pea.object_version_number,
pea.business_group_id,
pea.effective_start_date,
pea.effective_end_date,
pen.object_version_number rslt_object_version_number
from ben_prtt_enrt_actn_f pea,
ben_prtt_enrt_rslt_f pen
where pea.prtt_enrt_actn_id = p_prtt_enrt_actn_id
and pea.cmpltd_dt IS NULL
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between pea.effective_start_date
and pea.effective_end_date
and pea.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
;
procedure delete_prtt_ctfn_prvdd
(p_prtt_enrt_ctfn_prvdd_id in number
,p_object_version_number in out nocopy number
,p_effective_date in date
,p_datetrack_mode in varchar2 default DTMODE_DELETE) is
--
-- for participant certifications
-- this procedure datetrack deletes the ben_prtt_enrt_ctfn_prvdd_f
--
l_proc varchar2(80);
l_proc := g_package||'.delete_prtt_ctfn_prvdd';
ben_prtt_enrt_ctfn_prvdd_api.delete_prtt_enrt_ctfn_prvdd
(p_prtt_enrt_ctfn_prvdd_id => p_prtt_enrt_ctfn_prvdd_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode);
end delete_prtt_ctfn_prvdd;
procedure delete_dpnt_ctfn_prvdd
(p_cvrd_dpnt_ctfn_prvdd_id in number
,p_object_version_number in out nocopy number
,p_effective_date in date
,p_business_group_id in number
,p_datetrack_mode in varchar2 default DTMODE_DELETE) is
--
-- for a dependent certification for a participant
-- datetrack_mode delete
--
l_proc varchar2(80);
l_proc := g_package||'.delete_dpnt_ctfn_prvdd';
ben_cvrd_dpnt_ctfn_prvdd_api.delete_cvrd_dpnt_ctfn_prvdd
(p_cvrd_dpnt_ctfn_prvdd_id => p_cvrd_dpnt_ctfn_prvdd_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
,p_datetrack_mode => p_datetrack_mode
,p_called_from => 'benactcm' );
end delete_dpnt_ctfn_prvdd;
,p_datetrack_mode in varchar2 default DTMODE_DELETE) is
--
-- this procedure removes participant certifications from
-- ben_prtt_enrt_ctfn_prvdd_f. This is datetrack_mode controlled
--
l_proc varchar2(80);
select pcs.prtt_enrt_ctfn_prvdd_id,
pcs.object_version_number
from ben_prtt_enrt_ctfn_prvdd_f pcs,
ben_prtt_enrt_actn_f pea,
ben_per_in_ler pil
where pcs.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pcs.enrt_ctfn_recd_dt is NULL
and pcs.business_group_id = p_business_group_id
and p_effective_date between pcs.effective_start_date
and pcs.effective_end_date
and pea.prtt_enrt_actn_id=pcs.prtt_enrt_actn_id
and pea.pl_bnf_id is null
and pea.elig_cvrd_dpnt_id is null
and pea.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and pea.business_group_id=p_business_group_id
and p_effective_date between pea.effective_start_date
and pea.effective_end_date
;
delete_prtt_ctfn_prvdd
(p_prtt_enrt_ctfn_prvdd_id => l_prtt_ctfn_prvdd.prtt_enrt_ctfn_prvdd_id
,p_object_version_number => l_prtt_ctfn_prvdd.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode);
,p_datetrack_mode in varchar2 default DTMODE_DELETE
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date) is
--
-- this procedure removes certifications for dependents of a participant
-- if cvrd_flag = Y and dpnt_dsgn_ctfn_recd_dt is NULL
-- this is datetrack_mode controlled
--
l_proc varchar2(80);
select prv.cvrd_dpnt_ctfn_prvdd_id,
prv.object_version_number
from ben_elig_cvrd_dpnt_f ecd, ben_cvrd_dpnt_ctfn_prvdd_f prv,
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 nvl(ecd.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
and ecd.elig_cvrd_dpnt_id = prv.elig_cvrd_dpnt_id
and prv.dpnt_dsgn_ctfn_recd_dt is NULL
and ecd.business_group_id = p_business_group_id
and p_effective_date between ecd.effective_start_date
and ecd.effective_end_date
and prv.business_group_id = p_business_group_id
and p_effective_date between prv.effective_start_date
and prv.effective_end_date
and pil.per_in_ler_id=ecd.per_in_ler_id
and pil.business_group_id=p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
delete_dpnt_ctfn_prvdd
(p_cvrd_dpnt_ctfn_prvdd_id => l_dpnt_ctfn_prvdd.cvrd_dpnt_ctfn_prvdd_id
,p_object_version_number => l_dpnt_ctfn_prvdd.object_version_number
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
,p_datetrack_mode => p_datetrack_mode);
procedure delete_action_item
(p_prtt_enrt_actn_id in number
,p_object_version_number in out nocopy number
,p_business_group_id in number
,p_effective_date in date
,p_datetrack_mode in varchar2 default DTMODE_DELETE
,p_prtt_enrt_rslt_id in number
,p_rslt_object_version_number in out nocopy number
,p_post_rslt_flag in varchar2) is
--
l_proc varchar2(80);
l_proc := g_package||'.delete_action_item';
ben_prtt_enrt_actn_api.delete_prtt_enrt_actn
(p_prtt_enrt_actn_id => p_prtt_enrt_actn_id
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_object_version_number => p_object_version_number
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => p_rslt_object_version_number
,p_post_rslt_flag => p_post_rslt_flag
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
end delete_action_item;
select sum(1) tot_ctfn,
sum(decode(pcs.enrt_ctfn_rqd_flag,'Y',1,0)) tot_rqd,
sum(decode(pcs.enrt_ctfn_recd_dt,null,1,0)) tot_open_ctfn,
sum(decode(pcs.enrt_ctfn_rqd_flag,'N',0,
decode(pcs.enrt_ctfn_recd_dt,null,1,0))) tot_open_rqd
from ben_prtt_enrt_ctfn_prvdd_f pcs
where pcs.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pcs.prtt_enrt_actn_id = p_prtt_enrt_actn_id
and p_effective_date between
pcs.effective_start_date and pcs.effective_end_date;
select sum(1) tot_ctfn,
sum(decode(ccp.dpnt_dsgn_ctfn_rqd_flag,'Y',1,0)) tot_rqd,
sum(decode(ccp.dpnt_dsgn_ctfn_recd_dt,null,1,0)) tot_open_ctfn,
sum(decode(ccp.dpnt_dsgn_ctfn_rqd_flag,'N',0,
decode(ccp.dpnt_dsgn_ctfn_recd_dt,null,1,0))) tot_open_rqd
from ben_cvrd_dpnt_ctfn_prvdd_f ccp
where ccp.prtt_enrt_actn_id = p_prtt_enrt_actn_id
and ccp.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and p_effective_date between
ccp.effective_start_date and ccp.effective_end_date;
select sum(1) tot_ctfn,
sum(decode(pbc.bnf_ctfn_rqd_flag,'Y',1,0)) tot_rqd,
sum(decode(pbc.bnf_ctfn_recd_dt,null,1,0)) tot_open_ctfn,
sum(decode(pbc.bnf_ctfn_rqd_flag,'N',0,
decode(pbc.bnf_ctfn_recd_dt,null,1,0))) tot_open_rqd
from ben_pl_bnf_ctfn_prvdd_f pbc
where pbc.prtt_enrt_actn_id = p_prtt_enrt_actn_id
and pbc.pl_bnf_id = p_pl_bnf_id
and p_effective_date between
pbc.effective_start_date and pbc.effective_end_date;
select pen.pgm_id,
pen.ptip_id,
pen.pl_id,
pen.pl_typ_id,
pen.oipl_id,
pen.ler_id,
pen.person_id,
pen.object_version_number
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
and p_effective_date between
pen.effective_start_date and pen.effective_end_date;
select pgm.dpnt_dsgn_lvl_cd
from ben_pgm_f pgm
where pgm.pgm_id = p_pgm_id
and pgm.business_group_id = p_business_group_id
and p_effective_date between
pgm.effective_start_date and pgm.effective_end_date;
select pgm.susp_if_ctfn_not_dpnt_flag
from ben_pgm_f pgm
where pgm.pgm_id = p_pgm_id
and pgm.business_group_id = p_business_group_id
and p_effective_date between
pgm.effective_start_date and pgm.effective_end_date;
select ptip.susp_if_ctfn_not_dpnt_flag
from ben_ptip_f ptip
where ptip.ptip_id = p_ptip_id
and ptip.business_group_id = p_business_group_id
and p_effective_date between
ptip.effective_start_date and ptip.effective_end_date;
select pl.susp_if_ctfn_not_dpnt_flag
from ben_pl_f pl
where pl.pl_id = p_pl_id
and pl.business_group_id = p_business_group_id
and p_effective_date between
pl.effective_start_date and pl.effective_end_date;
select ldc.susp_if_ctfn_not_prvd_flag
from BEN_LER_CHG_DPNT_CVG_f ldc,
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
and pen.ler_id = ldc.ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between ldc.effective_start_date
and ldc.effective_end_date
and ((p_lvl_cd = 'PTIP' and ldc.ptip_id = pen.ptip_id) OR
(p_lvl_cd = 'PL' and ldc.pl_id = pen.pl_id) OR
(p_lvl_cd = 'PGM' and ldc.pgm_id = pen.pgm_id));
p_datetrack_mode => hr_api.g_update,
p_rslt_object_version_number => l_rslt.object_version_number);
select per.date_of_birth
from per_all_people_f per
where per.person_id = p_person_id
and per.business_group_id = p_business_group_id
and p_effective_date between per.effective_start_date
and per.effective_end_date;
select pcr.rltd_per_rsds_w_dsgntr_flag
from per_contact_relationships pcr, ben_prtt_enrt_rslt_f perslt
where perslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and perslt.person_id = pcr.person_id
and pcr.contact_person_id = p_dpnt_bnf_person_id
and pcr.business_group_id = p_business_group_id
and perslt.business_group_id = p_business_group_id
and perslt.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
perslt.effective_start_date and perslt.effective_end_date;
select peradd.primary_flag,
peradd.address_line1,
peradd.postal_code
from per_addresses peradd,
ben_prtt_enrt_rslt_f perslt
where perslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and perslt.person_id = peradd.person_id
and peradd.business_group_id = p_business_group_id
and perslt.business_group_id = p_business_group_id
and perslt.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
perslt.effective_start_date and perslt.effective_end_date
order by decode(peradd.primary_flag,'Y',1,2);
select peradd.primary_flag,
peradd.address_line1,
peradd.postal_code
from per_addresses peradd
where peradd.person_id = p_dpnt_bnf_person_id
and peradd.business_group_id = p_business_group_id
and p_effective_date between peradd.date_from and
nvl(peradd.date_to,p_effective_date)
order by decode(peradd.primary_flag,'Y',1,2);
select peradd.primary_flag,
peradd.address_line1,
peradd.postal_code
from per_addresses peradd
where peradd.person_id = p_dpnt_bnf_person_id
and peradd.business_group_id = p_business_group_id; */
select per.national_identifier
from per_all_people_f per
where per.person_id = p_person_id
and per.business_group_id = p_business_group_id
and p_effective_date between
per.effective_start_date and per.effective_end_date;
select oipl.opt_id
from ben_oipl_f oipl
where oipl.oipl_id = p_oipl_id
and business_group_id = p_business_group_id
and p_effective_date between
oipl.effective_start_date and oipl.effective_end_date;
select rslt.pgm_id,
rslt.ptip_id,
rslt.pl_id,
rslt.pl_typ_id,
rslt.oipl_id,
rslt.ler_id,
rslt.person_id,
rslt.business_group_id
from ben_prtt_enrt_rslt_f rslt
where rslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and rslt.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
rslt.effective_start_date and rslt.effective_end_date;
select asg.assignment_id,asg.organization_id
from per_all_assignments_f asg
where asg.person_id = l_rslt.person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and p_effective_date between
asg.effective_start_date and asg.effective_end_date;
select region_2
from hr_locations_all loc,per_all_assignments_f asg
where loc.location_id = asg.location_id
and asg.person_id = l_rslt.person_id
and asg.assignment_type <> 'C'
and p_effective_date between
asg.effective_start_date and asg.effective_end_date
and asg.business_group_id=p_business_group_id;
select pgm.dpnt_dsgn_lvl_cd
from ben_pgm_f pgm
where pgm.pgm_id = p_pgm_id
and pgm.business_group_id = p_business_group_id
and p_effective_date between
pgm.effective_start_date and pgm.effective_end_date;
select pgm.susp_if_dpnt_ssn_nt_prv_cd,
pgm.susp_if_dpnt_dob_nt_prv_cd,
pgm.susp_if_dpnt_adr_nt_prv_cd,
pgm.susp_if_ctfn_not_dpnt_flag,
pgm.dpnt_ctfn_determine_cd,
pgm.dpnt_dsgn_no_ctfn_rqd_flag
from ben_pgm_f pgm
where pgm.pgm_id = p_pgm_id
and pgm.business_group_id = p_business_group_id
and p_effective_date between
pgm.effective_start_date and pgm.effective_end_date;
select ptip.susp_if_dpnt_ssn_nt_prv_cd,
ptip.susp_if_dpnt_dob_nt_prv_cd,
ptip.susp_if_dpnt_adr_nt_prv_cd,
ptip.susp_if_ctfn_not_dpnt_flag,
ptip.dpnt_ctfn_determine_cd,
ptip.dpnt_cvg_no_ctfn_rqd_flag
from ben_ptip_f ptip
where ptip.ptip_id = p_ptip_id
and ptip.business_group_id = p_business_group_id
and p_effective_date between
ptip.effective_start_date and ptip.effective_end_date;
select pl.susp_if_dpnt_ssn_nt_prv_cd,
pl.susp_if_dpnt_dob_nt_prv_cd,
pl.susp_if_dpnt_adr_nt_prv_cd,
pl.susp_if_ctfn_not_dpnt_flag,
pl.dpnt_ctfn_determine_cd,
pl.dpnt_no_ctfn_rqd_flag
from ben_pl_f pl
where pl.pl_id = p_pl_id
and pl.business_group_id = p_business_group_id
and p_effective_date between
pl.effective_start_date and pl.effective_end_date;
select lcc.dpnt_cvg_ctfn_typ_cd,
lcc.ctfn_rqd_when_rl,
lcc.rqd_flag,
lcc.rlshp_typ_cd,
ldc.susp_if_ctfn_not_prvd_flag,
ldc.ctfn_determine_cd
from ben_ler_chg_dpnt_cvg_ctfn_f lcc,
ben_ler_chg_dpnt_cvg_f ldc,
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
and pen.ler_id = ldc.ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between ldc.effective_start_date
and ldc.effective_end_date
and ((v_lvl_cd = 'PTIP' and ldc.ptip_id = v_ptip_id) OR
(v_lvl_cd = 'PL' and ldc.pl_id = v_pl_id) OR
(v_lvl_cd = 'PGM' and ldc.pgm_id = v_pgm_id))
and ldc.ler_chg_dpnt_cvg_id = lcc.ler_chg_dpnt_cvg_id
and p_effective_date between lcc.effective_start_date
and lcc.effective_end_date
and (lcc.rlshp_typ_cd is null
or
lcc.rlshp_typ_cd in (select contact_type
from per_contact_relationships
where contact_person_id = v_dpnt_person_id
and person_id = v_person_id
and business_group_id = p_business_group_id
and p_effective_date
between nvl(date_start, p_effective_date)
and nvl(date_end, hr_api.g_eot)));
select pl.dpnt_cvg_ctfn_typ_cd ctcvgcd,
pl.ctfn_rqd_when_rl ctrrl,
pl.rqd_flag,
pl.rlshp_typ_cd ctrlshcd,
pl.pl_id
from ben_pl_dpnt_cvg_ctfn_f pl
where pl.pl_id = v_pl_id
and pl.business_group_id = p_business_group_id
and p_effective_date between pl.effective_start_date
and pl.effective_end_date
and (pl.rlshp_typ_cd is null
or
pl.rlshp_typ_cd in (select contact_type
from per_contact_relationships
where contact_person_id = v_dpnt_person_id
and person_id = v_person_id
and business_group_id = p_business_group_id
and p_effective_date
between nvl(date_start, p_effective_date)
and nvl(date_end, hr_api.g_eot)));
select pgm.dpnt_cvg_ctfn_typ_cd ctcvgcd,
pgm.ctfn_rqd_when_rl ctrrl,
pgm.rqd_flag,
pgm.rlshp_typ_cd ctrlshcd,
pgm.pgm_id
from ben_pgm_dpnt_cvg_ctfn_f pgm
where pgm.pgm_id = v_pgm_id
and pgm.business_group_id = p_business_group_id
and p_effective_date between pgm.effective_start_date
and pgm.effective_end_date
and (pgm.rlshp_typ_cd is null
or
pgm.rlshp_typ_cd in (select contact_type
from per_contact_relationships
where contact_person_id = v_dpnt_person_id
and person_id = v_person_id
and business_group_id = p_business_group_id
and p_effective_date
between nvl(date_start, p_effective_date)
and nvl(date_end, hr_api.g_eot)));
select ptip.dpnt_cvg_ctfn_typ_cd ctcvgcd,
ptip.ctfn_rqd_when_rl ctrrl,
ptip.rqd_flag,
ptip.rlshp_typ_cd ctrlshcd,
ptip.ptip_id
from ben_ptip_dpnt_cvg_ctfn_f ptip
where ptip.ptip_id = v_ptip_id
and ptip.business_group_id = p_business_group_id
and p_effective_date between ptip.effective_start_date
and ptip.effective_end_date
and (ptip.rlshp_typ_cd is null
or
ptip.rlshp_typ_cd in (select contact_type
from per_contact_relationships
where contact_person_id = v_dpnt_person_id
and person_id = v_person_id
and business_group_id = p_business_group_id
and p_effective_date
between nvl(date_start, p_effective_date)
and nvl(date_end, hr_api.g_eot)));
select ecd.dpnt_person_id,
ecd.elig_cvrd_dpnt_id,
pen.pgm_id,
pen.ptip_id,
pen.pl_id
from ben_elig_cvrd_dpnt_f ecd,
ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_id = ecd.prtt_enrt_rslt_id
and ecd.cvg_strt_dt is not null
and nvl(ecd.cvg_thru_dt, hr_api.g_eot) = 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 between
pen.effective_start_date and pen.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null
and pil.per_in_ler_id=ecd.per_in_ler_id
and pil.business_group_id=p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select ccp.elig_cvrd_dpnt_id,
ccp.dpnt_dsgn_ctfn_typ_cd,
ccp.dpnt_dsgn_ctfn_rqd_flag,
ccp.dpnt_dsgn_ctfn_recd_dt
from ben_cvrd_dpnt_ctfn_prvdd_f ccp,
ben_elig_cvrd_dpnt_f ecd,
ben_per_in_ler pil
where ccp.elig_cvrd_dpnt_id = cl_dpnt_id
and ccp.dpnt_dsgn_ctfn_recd_dt IS NULL
and ccp.business_group_id = p_business_group_id
and p_effective_date between
ccp.effective_start_date and ccp.effective_end_date
and ecd.elig_cvrd_dpnt_id=ccp.elig_cvrd_dpnt_id
and ecd.business_group_id = p_business_group_id
and p_effective_date between
ecd.effective_start_date and ecd.effective_end_date
and pil.per_in_ler_id=ecd.per_in_ler_id
and pil.business_group_id=p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select object_version_number
from ben_prtt_enrt_actn_f
where prtt_enrt_actn_id = c_prtt_enrt_actn_id
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date;
if (l_prtt_enrt_actn_id IS NOT NULL and p_datetrack_mode = DTMODE_DELETE) then
--
delete_action_item
(p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
,p_object_version_number => l_object_version_number
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => p_rslt_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_post_rslt_flag => p_post_rslt_flag);
p_datetrack_mode = DTMODE_DELETE then
--
delete_action_item
(p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
,p_object_version_number => l_object_version_number
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => p_rslt_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_post_rslt_flag => p_post_rslt_flag);
if (l_prtt_enrt_actn_id IS NOT NULL and p_datetrack_mode = DTMODE_DELETE) then
--
delete_action_item
(p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
,p_object_version_number => l_object_version_number
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => p_rslt_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_post_rslt_flag => p_post_rslt_flag);
delete_action_item
(p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
,p_object_version_number => l_object_version_number
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => p_rslt_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_zap
,p_post_rslt_flag => p_post_rslt_flag);
if (l_prtt_enrt_actn_id IS NOT NULL and p_datetrack_mode = DTMODE_DELETE) then
--
delete_action_item
(p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
,p_object_version_number => l_object_version_number
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => p_rslt_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_post_rslt_flag => p_post_rslt_flag);
Select 'X'
from per_pay_proposals pay
,ben_prtt_enrt_rslt_f rslt
,ben_pl_typ_f tyP
Where rslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between rslt.effective_start_date and rslt.effective_end_date
and pay.approved = 'Y'
and rslt.assignment_id = pay.assignment_id
and pay.business_group_id = p_business_group_id
and pay.change_date >= p_effective_date
and typ.pl_typ_id = rslt.pl_typ_id
and rslt.prtt_enrt_rslt_stat_cd is null
and p_effective_date between typ.effective_start_date and typ.effective_end_date
and typ.comp_typ_cd = 'ICM7';
select ecd.dpnt_person_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 nvl(ecd.cvg_thru_dt, hr_api.g_eot) = 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 pil.per_in_ler_id=ecd.per_in_ler_id
and pil.business_group_id=p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select pea.prtt_enrt_actn_id,
pea.object_version_number
from ben_prtt_enrt_actn_f pea,
ben_actn_typ typ,
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 typ.type_cd <> 'DD'
and typ.type_cd like 'DD%'
and pea.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and typ.business_group_id = p_business_group_id
and pea.actn_typ_id = typ.actn_typ_id
and p_effective_date between
pea.effective_start_date and pea.effective_end_date
;
select sum(tot_mn_dpnts_rqd_num) tot_mn_dpnts_rqd_num,
sum(tot_mx_dpnts_alwd_num) tot_mx_dpnts_alwd_num
from (select sum(nvl(drq.mn_dpnts_rqd_num, 0 )) tot_mn_dpnts_rqd_num,
sum(nvl(drq.mx_dpnts_alwd_num,9999999999)) tot_mx_dpnts_alwd_num
from ben_dsgn_rqmt_f drq,
ben_oipl_f cop,
ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
-- and drq.oipl_id = pen.oipl_id -- 3730053: OPT level DSGN_RQMTS override OIPL level
and pen.oipl_id = cop.oipl_id
and ( (drq.oipl_id = pen.oipl_id
and not exists
(select null
from ben_dsgn_rqmt_f drq2
where drq2.opt_id = cop.opt_id
and p_effective_date between drq2.effective_start_date
and drq2.effective_end_date))
or cop.opt_id = drq.opt_id)
and drq.dsgn_typ_cd = 'DPNT'
--and drq.mn_dpnts_rqd_num > 0
--and drq.mx_dpnts_alwd_num = 0
and cop.business_group_id = p_business_group_id
and p_effective_date between cop.effective_start_date
and cop.effective_end_date
and pen.business_group_id = p_business_group_id
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
and drq.business_group_id = p_business_group_id
and p_effective_date between drq.effective_start_date
and drq.effective_end_date
UNION -- 3730053: Added this to check PL-level designation requirements are specified at PLAN-LEVEL.
select sum(nvl(drq.mn_dpnts_rqd_num, 0 )) tot_mn_dpnts_rqd_num,
sum(nvl(drq.mx_dpnts_alwd_num,9999999999)) tot_mx_dpnts_alwd_num
from ben_dsgn_rqmt_f drq,
ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and drq.pl_id = pen.pl_id
and drq.dsgn_typ_cd = 'DPNT'
--and drq.mn_dpnts_rqd_num > 0
--and drq.mx_dpnts_alwd_num = 0
and pen.business_group_id = p_business_group_id
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
and drq.business_group_id = p_business_group_id
and p_effective_date between drq.effective_start_date
and drq.effective_end_date
and (pen.oipl_id IS NULL
OR NOT EXISTS (select null
from ben_dsgn_rqmt_f drq3
where drq3.oipl_id = pen.oipl_id
and p_effective_date between drq3.effective_start_date and drq3.effective_end_date
UNION ALL
select null
from ben_dsgn_rqmt_f drq4,
ben_oipl_f cop
where cop.oipl_id = pen.oipl_id
and drq4.opt_id = cop.opt_id
and p_effective_date between cop.effective_start_date and cop.effective_end_date
and p_effective_date between drq4.effective_start_date and drq4.effective_end_date))
);
SELECT 'S'
from BEN_ELIG_DPNT egd,
BEN_ELIG_PER_ELCTBL_CHC epe,
BEN_PRTT_ENRT_RSLT_F pen
where pen.PRTT_ENRT_RSLT_ID = p_prtt_enrt_rslt_id
and pen.PRTT_ENRT_RSLT_ID = epe.PRTT_ENRT_RSLT_ID
and egd.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between pen.effective_start_date and pen.effective_end_date
and dpnt_inelig_flag = 'N' ;
delete_action_item
(p_prtt_enrt_actn_id => actn_item_rec.prtt_enrt_actn_id
,p_object_version_number => actn_item_rec.object_version_number
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => p_rslt_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_zap
,p_post_rslt_flag => p_post_rslt_flag);
select mn_dpnts_rqd_num
from ben_prtt_enrt_rslt_f perslt,
ben_dsgn_rqmt_f drq
where perslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and perslt.prtt_enrt_rslt_stat_cd is null
and drq.dsgn_typ_cd = 'DPNT'
and drq.grp_rlshp_cd is null
and (nvl(drq.pl_id,0) = perslt.pl_id
or
nvl(drq.oipl_id,0) = perslt.oipl_id
or
nvl(drq.opt_id,0) = (select o.opt_id
from ben_oipl_f o
where o.oipl_id = perslt.oipl_id
and p_effective_date
between o.effective_start_date
and o.effective_end_date)
)
and drq.business_group_id = p_business_group_id
and p_effective_date between drq.effective_start_date
and drq.effective_end_date
and perslt.business_group_id = p_business_group_id
and p_effective_date between perslt.effective_start_date
and perslt.effective_end_date;
select count(1) cnt
from ben_elig_cvrd_dpnt_f ecd,
ben_per_in_ler pil
where p_prtt_enrt_rslt_id = ecd.prtt_enrt_rslt_id
and ecd.cvg_strt_dt is not null
and nvl(ecd.cvg_thru_dt, hr_api.g_eot) = 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 pil.per_in_ler_id=ecd.per_in_ler_id
and pil.business_group_id=p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select distinct 'X' -- mn_dpnts_rqd_num
from ben_prtt_enrt_rslt_f perslt,
ben_dsgn_rqmt_f drq
where perslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and drq.dsgn_typ_cd = 'DPNT'
and drq.grp_rlshp_cd is not null
and perslt.prtt_enrt_rslt_stat_cd is null
and (nvl(drq.pl_id,0) = perslt.pl_id
or
nvl(drq.oipl_id,0) = perslt.oipl_id
or
nvl(drq.opt_id,0) = (select o.opt_id
from ben_oipl_f o
where o.oipl_id = perslt.oipl_id
and p_effective_date
between o.effective_start_date
and o.effective_end_date)
)
and drq.business_group_id = p_business_group_id
and p_effective_date between drq.effective_start_date
and drq.effective_end_date
and perslt.business_group_id = p_business_group_id
and p_effective_date between perslt.effective_start_date
and perslt.effective_end_date
and mn_dpnts_rqd_num >
(select count(1)
from ben_elig_cvrd_dpnt_f ecd,
per_contact_relationships pcr,
ben_per_in_ler pil
where ecd.prtt_enrt_rslt_id = perslt.prtt_enrt_rslt_id
and ecd.cvg_strt_dt is not null
and nvl(ecd.cvg_thru_dt, hr_api.g_eot) = 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 perslt.person_id = pcr.person_id
and ecd.dpnt_person_id = pcr.contact_person_id
and pcr.business_group_id = p_business_group_id
and pcr.contact_type in
(select drrt.rlshp_typ_cd
from ben_dsgn_rqmt_rlshp_typ drrt
where drrt.dsgn_rqmt_id = drq.dsgn_rqmt_id)
and pil.per_in_ler_id=ecd.per_in_ler_id
and pil.business_group_id=p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'));
select epe.alws_dpnt_dsgn_flag, epe.dpnt_dsgn_cd
from ben_prtt_enrt_rslt_f pen,
ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
-- Join by comp object not result_id
and nvl(pen.pgm_id,-1)=nvl(epe.pgm_id,-1)
and pen.pl_id=epe.pl_id
and epe.bnft_prvdr_pool_id is null -- Bug 2389261 exclude these records
and nvl(pen.oipl_id,-1)=nvl(epe.oipl_id,-1)
-- and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
and pen.per_in_ler_id = epe.per_in_ler_id
and epe.business_group_id = p_business_group_id
and pil.per_in_ler_id = epe.per_in_ler_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
select 'Y'
from ben_ler_rqrs_enrt_ctfn_f lre,
ben_ler_enrt_ctfn_f lec,
ben_per_in_ler pil
where lre.pl_id = p_pl_id
and pil.per_in_ler_id = p_per_in_ler_id
and lre.ler_id = pil.ler_id
and p_effective_date between lre.effective_start_date
and lre.effective_end_date
and p_effective_date between lec.effective_start_date
and lec.effective_end_date
and lec.ler_rqrs_enrt_ctfn_id = lre.ler_rqrs_enrt_ctfn_id
and lec.enrt_ctfn_typ_cd = p_enrt_ctfn_typ_cd
union
select 'Y'
from ben_enrt_ctfn_f ec
where ec.pl_id = p_pl_id
and p_effective_date between ec.effective_start_date
and ec.effective_end_date
and ec.enrt_ctfn_typ_cd = p_enrt_ctfn_typ_cd ;
delete enrollment call from election information.
So these are the following cases we need to keep in mind. If you see issues
please do add the example here so that the future developer won't intruduce another
regression.
Here is the status of the records when this procedure is being called
LE1 LE2
|------------------------------------|---------------------------------------------
Case 1: Continuing in the same enrollment
OldOipl OldOipl
OldPIL NewPIL
OldPEN OldPEN
CTD EOT
EED EOT
Case 2: Continuing in the same enrollment save and then change with new option again
OldOipl OldOipl
OldPIL NewPIL
OldPEN OldPEN
CTD EOT
EED EOT
[the data will change once the delete enrollment is called]
NewOipl
NewPIL
NewPEN
CTD EOT
EED EOT
Case 3: Replace the enrollment with a new Plan Option
OldOipl OldOipl
OldPIL OldPIL [Important]
OldPEN OldPEN
CTD EOT
EED EOT
[the data will change once the delete enrollment is called]
NewOipl
NewPIL
NewPEN
CTD EOT
EED EOT
Case 4: delete the current enrollment and enroll in a new one later
OldOipl OldOipl
OldPIL NewPIL
OldPEN OldPEN
CTD Date will be filled in
EED EOT
[the data will change once the delete enrollment is called]
NewOipl
NewPIL
NewPEN
CTD EOT
EED EOT
*/
cursor c_current_pl_enrollment (p_prtt_enrt_rslt_id number) is
select 'Y'
from ben_prtt_enrt_rslt_f pen,
ben_prtt_enrt_rslt_f pen2
where pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and nvl(pen2.rplcs_sspndd_rslt_id,-999) <> pen.prtt_enrt_rslt_id --NO Interim
and pen2.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
and pen2.pl_id = pen.pl_id
and pen2.person_id = pen.person_id
and pen.sspndd_flag = 'N'
and (
( pen2.per_in_ler_id = pen.per_in_ler_id and
( pen.enrt_cvg_thru_dt <> to_date('31-12-4712','dd-mm-yyyy') or
( (-- pen.prtt_enrt_rslt_id <> pen2.prtt_enrt_rslt_id and
exists (select 'x' from ben_elig_per_elctbl_chc epe,
ben_prtt_enrt_rslt_f pen3
where epe.per_in_ler_id = pen.per_in_ler_id and
epe.pl_id = pen.pl_id and
( epe.pgm_id = pen.pgm_id or pen.pgm_id is null) and
( epe.oipl_id = pen.oipl_id or pen.oipl_id is null) and
epe.crntly_enrd_flag = 'Y' and
epe.elctbl_flag = 'Y' and
epe.bnft_prvdr_pool_id is null and
pen3.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id and
pen3.prtt_enrt_rslt_stat_cd is null and
pen3.sspndd_flag = 'N' and
pen3.effective_end_date = pen.effective_start_date - 1
)
) and
pen.enrt_cvg_thru_dt = to_date('31-12-4712','dd-mm-yyyy')
)
) and
pen.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
)
or
( pen.enrt_cvg_thru_dt = to_date('31-12-4712','dd-mm-yyyy') and
pen.effective_end_date = to_date('31-12-4712','dd-mm-yyyy') and
pen.per_in_ler_id <> pen2.per_in_ler_id
)
)
and pen2.prtt_enrt_rslt_stat_cd is null
and pen.prtt_enrt_rslt_stat_cd is null ;
select 'Y'
from ben_prtt_enrt_rslt_f pen,
ben_prtt_enrt_rslt_f pen2
where pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and nvl(pen2.rplcs_sspndd_rslt_id,-999) <> pen.prtt_enrt_rslt_id --NO Interim
and pen2.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
and pen2.pl_id = pen.pl_id
and pen2.oipl_id = pen.oipl_id
and pen2.person_id = pen.person_id
and pen.sspndd_flag = 'N'
and (
( pen2.per_in_ler_id = pen.per_in_ler_id and
( pen.enrt_cvg_thru_dt <> to_date('31-12-4712','dd-mm-yyyy') or
( (-- pen.prtt_enrt_rslt_id <> pen2.prtt_enrt_rslt_id or
exists (select 'x' from ben_elig_per_elctbl_chc epe,
ben_prtt_enrt_rslt_f pen3
where epe.per_in_ler_id = pen2.per_in_ler_id and
epe.pl_id = pen2.pl_id and
( epe.pgm_id = pen2.pgm_id or pen2.pgm_id is null) and
( epe.oipl_id = pen2.oipl_id ) and
epe.crntly_enrd_flag = 'Y' and
epe.elctbl_flag = 'Y' and
epe.bnft_prvdr_pool_id is null and
pen3.prtt_enrt_rslt_id = pen2.prtt_enrt_rslt_id and
pen3.prtt_enrt_rslt_stat_cd is null and
pen3.sspndd_flag = 'N' and
pen3.effective_end_date = pen2.effective_start_date - 1
)
) and
pen.enrt_cvg_thru_dt = to_date('31-12-4712','dd-mm-yyyy')
)
) and
pen.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
)
or
( pen.enrt_cvg_thru_dt = to_date('31-12-4712','dd-mm-yyyy') and
pen.effective_end_date = to_date('31-12-4712','dd-mm-yyyy') and
pen.per_in_ler_id <> pen2.per_in_ler_id
)
)
and pen2.prtt_enrt_rslt_stat_cd is null
and pen.prtt_enrt_rslt_stat_cd is null ;
select null
from ben_prtt_enrt_ctfn_prvdd_f pcs
where pcs.prtt_enrt_rslt_id in
(select distinct(pen1.prtt_enrt_rslt_id) from ben_prtt_enrt_rslt_f pen1, ben_prtt_enrt_rslt_f pen2
where pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen1.person_id = pen2.person_id
and pen1.prtt_enrt_rslt_stat_cd is null
and pen2.prtt_enrt_rslt_stat_cd is null
and pen1.pl_id = pen2.pl_id
and pen1.per_in_ler_id <> pen2.per_in_ler_id
and nvl(pen2.oipl_id,0) = nvl(pen1.oipl_id,0)
and pen1.enrt_cvg_thru_dt >= pen1.effective_start_date
and pen1.enrt_cvg_strt_dt <= pen1.enrt_cvg_thru_dt
and pen2.enrt_cvg_thru_dt = to_date('4712/12/31','rrrr/mm/dd')
and p_effective_date between pen2.effective_start_date and
pen2.effective_end_date
and pen1.orgnl_enrt_dt = pen2.orgnl_enrt_dt)
and pcs.ENRT_CTFN_TYP_CD = p_enrt_ctfn_typ_cd
and pcs.ENRT_CTFN_RECD_DT is not null
and pcs.enrt_ctfn_rqd_flag = 'Y'
and pcs.ENRT_R_BNFT_CTFN_CD = nvl(p_enrt_r_bnft_ctfn_cd,'ENRT') -- Bug 5887665
---Bug 7417593
/* and p_effective_date between pcs.effective_start_date and
pcs.effective_end_date*/
and p_effective_date > pcs.enrt_ctfn_recd_dt
---Bug 7417593
;
select null
from ben_prtt_enrt_ctfn_prvdd_f pcs
where pcs.prtt_enrt_rslt_id in
(select distinct(pen1.prtt_enrt_rslt_id) from ben_prtt_enrt_rslt_f pen1, ben_prtt_enrt_rslt_f pen2
where pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen1.person_id = pen2.person_id
and pen1.prtt_enrt_rslt_stat_cd is null
and pen2.prtt_enrt_rslt_stat_cd is null
and pen1.pl_id = pen2.pl_id
and pen1.per_in_ler_id <> pen2.per_in_ler_id
and nvl(pen2.oipl_id,0) = nvl(pen1.oipl_id,0)
and pen1.enrt_cvg_thru_dt >= pen1.effective_start_date
and pen1.enrt_cvg_strt_dt <= pen1.enrt_cvg_thru_dt
and pen2.enrt_cvg_thru_dt = to_date('4712/12/31','rrrr/mm/dd')
and p_effective_date between pen2.effective_start_date and
pen2.effective_end_date
and pen1.orgnl_enrt_dt = pen2.orgnl_enrt_dt)
and pcs.ENRT_CTFN_RECD_DT is not null
and pcs.ENRT_R_BNFT_CTFN_CD = nvl(p_enrt_r_bnft_ctfn_cd,'ENRT') -- Bug 5887665
and pcs.enrt_ctfn_rqd_flag = 'N'
and exists (select null from ben_prtt_enrt_ctfn_prvdd_f pcs2
where pcs2.prtt_enrt_rslt_id = pcs.prtt_enrt_rslt_id
and pcs2.ENRT_CTFN_TYP_CD = p_enrt_ctfn_typ_cd
and p_effective_date between pcs2.effective_start_date and
pcs2.effective_end_date)
and p_effective_date between pcs.effective_start_date and
pcs.effective_end_date;
select pen.prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f pen,
ben_prtt_enrt_rslt_f pen2
where pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
-- and pen2.bnft_amt is null -- Bug 5887665 Need to call this code for benefit level certifications as well
and pen2.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
and pen2.pl_id = pen.pl_id
and pen2.person_id = pen.person_id
and ((pen2.per_in_ler_id = pen.per_in_ler_id and
pen2.oipl_id <> pen.oipl_id and -- Bug 5887665 Only in same LE we need to chk if option is changed
pen.enrt_cvg_thru_dt <> to_date('31-12-4712','dd-mm-yyyy') and
pen.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')) or
(pen.enrt_cvg_thru_dt = to_date('31-12-4712','dd-mm-yyyy') and
pen.effective_end_date = to_date('31-12-4712','dd-mm-yyyy') and -----Bug 7417474
pen2.oipl_id = pen.oipl_id)) ----Bug 7417474
and pen2.prtt_enrt_rslt_stat_cd is null
and pen.prtt_enrt_rslt_stat_cd is null;
select rstrn.cvg_incr_r_decr_only_cd
from ben_ler_bnft_rstrn_f rstrn,
ben_per_in_ler pil
where rstrn.ler_id = pil.ler_id
and rstrn.pl_id = p_pl_id
and pil.per_in_ler_id = p_per_in_ler_id
and p_effective_date
between rstrn.effective_start_date
and rstrn.effective_end_date;
select pln.cvg_incr_r_decr_only_cd,
pln.bnft_or_option_rstrctn_cd
from ben_pl_f pln
where pln.pl_id = p_pl_id
and p_effective_date
between pln.effective_start_date
and pln.effective_end_date;
select pen.bnft_amt
from ben_prtt_enrt_rslt_f pen,
ben_prtt_enrt_rslt_f pen2
where pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen2.bnft_amt <> pen.bnft_amt
and pen2.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
and pen2.pl_id = pen.pl_id
and pen2.person_id = pen.person_id
and ((pen2.per_in_ler_id = pen.per_in_ler_id and
pen.enrt_cvg_thru_dt <> to_date('31-12-4712','dd-mm-yyyy') and
pen.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')) or
(pen.enrt_cvg_thru_dt = to_date('31-12-4712','dd-mm-yyyy') and
pen.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')))
and pen2.prtt_enrt_rslt_stat_cd is null
and pen.prtt_enrt_rslt_stat_cd is null;
select 'Y'
from ben_elig_cvrd_dpnt_f egd
where egd.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and egd.per_in_ler_id = p_per_in_ler_id
and egd.cvg_thru_dt = hr_api.g_eot
and not exists (select null
from ben_elig_cvrd_dpnt_f egd2
where egd.elig_cvrd_dpnt_id = egd2.elig_cvrd_dpnt_id
and egd.dpnt_person_id = egd2.dpnt_person_id
and egd2.per_in_ler_id <> p_per_in_ler_id);
select epe.ctfn_rqd_flag,
epe.elig_per_elctbl_chc_id,
epe.crntly_enrd_flag,
epe.per_in_ler_id,
epe.pl_id,
nvl(pen.bnft_amt, 0) bnft_amt,
pen.oipl_id
from ben_prtt_enrt_rslt_f pen,
ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
-- join by comp object
and nvl(pen.pgm_id,-1)=nvl(epe.pgm_id,-1)
and pen.pl_id=epe.pl_id
and epe.bnft_prvdr_pool_id is null -- Bug 2389261 exclude these records
and nvl(pen.oipl_id,-1)=nvl(epe.oipl_id,-1)
-- and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
and pen.per_in_ler_id = epe.per_in_ler_id
and epe.business_group_id = p_business_group_id
and pil.per_in_ler_id = epe.per_in_ler_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
select ecc.rqd_flag,
ecc.enrt_ctfn_typ_cd,
ecc.SUSP_IF_CTFN_NOT_PRVD_FLAG,
ecc.ctfn_determine_cd
from ben_elctbl_chc_ctfn ecc
where ecc.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
-- jcarpent added line below for bug 1488666
and ecc.enrt_bnft_id is null
and ecc.business_group_id = p_business_group_id;
select enb.ctfn_rqd_flag,
nvl(enb.entr_val_at_enrt_flag, 'N') entr_val_at_enrt_flag,
mx_wout_ctfn_val,
cvg_mlt_cd
from ben_enrt_bnft enb
where enb.enrt_bnft_id = p_enrt_bnft_id
and enb.business_group_id = p_business_group_id;
select ecc.rqd_flag,
ecc.enrt_ctfn_typ_cd,
ecc.SUSP_IF_CTFN_NOT_PRVD_FLAG,
ecc.ctfn_determine_cd
from ben_elctbl_chc_ctfn ecc
where ecc.enrt_bnft_id = v_enrt_bnft_id
and ecc.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
and ecc.business_group_id = p_business_group_id;
select pec.prtt_enrt_ctfn_prvdd_id,
pec.enrt_ctfn_recd_dt,
pec.object_version_number
from ben_prtt_enrt_ctfn_prvdd_f pec
where pec.prtt_enrt_actn_id = v_prtt_enrt_actn_id
and pec.enrt_r_bnft_ctfn_cd = 'BNFT'
and pec.business_group_id = p_business_group_id
and p_effective_date between pec.effective_start_date
and pec.effective_end_date;
select pea.prtt_enrt_actn_id,
pea.per_in_ler_id
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.actn_typ_id = p_actn_typ_id
and pea.pl_bnf_id is null
and pea.elig_cvrd_dpnt_id is null
and pea.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and pea.business_group_id = p_business_group_id
and p_effective_date between pea.effective_start_date
and pea.effective_end_date ;
select rstrn.cvg_incr_r_decr_only_cd
from ben_ler_bnft_rstrn_f rstrn,
ben_per_in_ler pil
where rstrn.ler_id = pil.ler_id
and rstrn.pl_id = p_pl_id
and pil.per_in_ler_id = p_per_in_ler_id
and p_effective_date
between rstrn.effective_start_date
and rstrn.effective_end_date;
select pln.cvg_incr_r_decr_only_cd,
pln.bnft_or_option_rstrctn_cd
from ben_pl_f pln
where pln.pl_id = p_pl_id
and p_effective_date
between pln.effective_start_date
and pln.effective_end_date;
as part of calls from bepenapi delete_enrollment and this may fail.
Instead let us return without doing anything if this cursor doesnot
return any records */
-- fnd_message.set_name('BEN', 'BEN_91578_BENACPRM_EPE_NF');
Elsif p_datetrack_mode = hr_api.g_delete then
l_datetrack_mode := hr_api.g_delete;
Elsif p_datetrack_mode = hr_api.g_update then
l_datetrack_mode := hr_api.g_delete;
l_datetrack_mode := hr_api.g_delete;
delete_prtt_ctfn_prvdd
(p_prtt_enrt_ctfn_prvdd_id => l_enrt_ctfn.prtt_enrt_ctfn_prvdd_id
,p_object_version_number => l_enrt_ctfn.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode);
select 'x'
from ben_prmry_care_prvdr_f pf,
ben_elig_cvrd_dpnt_f ecd
where pf.elig_cvrd_dpnt_id = ecd.elig_cvrd_dpnt_id
-- and ecd.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id bug 1421978
and ecd.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and pf.business_group_id = p_business_group_id
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 between pf.effective_start_date
and pf.effective_end_date;
select 'x'
from ben_prmry_care_prvdr_f pf
where pf.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pf.business_group_id = p_business_group_id
and p_effective_date between pf.effective_start_date
and pf.effective_end_date;
select cop.pcp_dpnt_dsgn_cd,
ecd.elig_cvrd_dpnt_id
from ben_prtt_enrt_rslt_f pen,
ben_elig_cvrd_dpnt_f ecd,
ben_oipl_f cop
where cop.oipl_id = pen.oipl_id
and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_id = ecd.prtt_enrt_rslt_id
and ecd.cvg_strt_dt is not null
and nvl(ecd.cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
and pen.business_group_id = p_business_group_id
and cop.business_group_id = p_business_group_id
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 between cop.effective_start_date
and cop.effective_end_date
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
and cop.pcp_dpnt_dsgn_cd is not null ;
select pcp.pcp_dpnt_dsgn_cd ,
ecd.elig_cvrd_dpnt_id
from ben_prtt_enrt_rslt_f pen,
ben_elig_cvrd_dpnt_f ecd,
ben_pl_pcp pcp
where pcp.pl_id = pen.pl_id
and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_id = ecd.prtt_enrt_rslt_id
and ecd.cvg_strt_dt is not null
and nvl(ecd.cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
and pen.business_group_id = p_business_group_id
and pcp.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 between pen.effective_start_date
and pen.effective_end_date;
select cop.pcp_dsgn_cd
from ben_prtt_enrt_rslt_f perf,
ben_oipl_f cop
where cop.oipl_id = perf.oipl_id
and perf.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and perf.business_group_id = p_business_group_id
and cop.business_group_id = p_business_group_id
and p_effective_date between cop.effective_start_date
and cop.effective_end_date
and p_effective_date between perf.effective_start_date
and perf.effective_end_date
and cop.pcp_dsgn_cd is not null ;
select pcp.pcp_dsgn_cd
from ben_prtt_enrt_rslt_f perf,
ben_pl_pcp pcp
where pcp.pl_id = perf.pl_id
and perf.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and perf.business_group_id = p_business_group_id
and pcp.business_group_id = p_business_group_id
and p_effective_date between perf.effective_start_date
and perf.effective_end_date;
select round
(trunc
(months_between(p_effective_date, per.date_of_birth))/ 12
) bnf_age,
pb.ttee_person_id
from per_all_people_f per,
ben_pl_bnf_f pb
where pb.pl_bnf_id = p_pl_bnf_id
and per.person_id = pb.bnf_person_id
and per.business_group_id = p_business_group_id
and p_effective_date between per.effective_start_date
and per.effective_end_date;
select null
from ben_popl_actn_typ_f pat,
ben_actn_typ eat
where pat.pl_id = p_pl_id
and pat.actn_typ_id = eat.actn_typ_id
and eat.type_cd = p_actn_type_cd
and pat.business_group_id = p_business_group_id
and p_effective_date between
pat.effective_start_date and pat.effective_end_date ;
select pl.lack_ctfn_sspnd_enrt_flag ctflag,
pl.bnf_ctfn_typ_cd ctcvgcd,
pl.ctfn_rqd_when_rl ctrrl,
pl.rqd_flag rqd_flag,
pl.rlshp_typ_cd ctrlshcd,
pl.bnf_typ_cd, pl.pl_id
from ben_pl_bnf_ctfn_f pl
where pl.bnf_ctfn_typ_cd <> 'NSC'
and pl.pl_id = v_pl_id
and (pl.rlshp_typ_cd is null or
pl.rlshp_typ_cd in (select contact_type
from per_contact_relationships
where contact_person_id = v_bnf_person_id
and person_id = v_person_id
and business_group_id = p_business_group_id
and p_effective_date
between nvl(date_start, p_effective_date)
and nvl(date_end, hr_api.g_eot)))
and pl.business_group_id = p_business_group_id
and p_effective_date between
pl.effective_start_date and pl.effective_end_date;
select 's'
from ben_pl_bnf_ctfn_prvdd_f
where pl_bnf_id = v_pl_bnf_id
and p_effective_date between effective_start_date
and effective_end_date
and business_group_id = p_business_group_id;
select pbd.pl_bnf_id,
pbd.bnf_person_id
from ben_pl_bnf_f pbd,
ben_per_in_ler pil
where pbd.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pbd.dsgn_strt_dt is not null
and nvl(pbd.dsgn_thru_dt, hr_api.g_eot) = hr_api.g_eot
and pbd.business_group_id = p_business_group_id
and p_effective_date between pbd.effective_start_date
and pbd.effective_end_date
and pil.per_in_ler_id=pbd.per_in_ler_id
and pil.business_group_id=p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
-- Check fo DOB, SSN, ADDRESS only for person beneficiaries
-- and not for Organization Beneficiaries
and pbd.bnf_person_id is not null; -- Bug : 3854556
select 's'
from ben_pl_bnf_ctfn_f
where bnf_ctfn_typ_cd <> 'NSC'
and pl_id = p_pl_id
and (bnf_typ_cd is null or bnf_typ_cd = 'P')
and (rlshp_typ_cd is null
or
rlshp_typ_cd in (select contact_type
from per_contact_relationships
where contact_person_id = v_bnf_person_id
and person_id = v_person_id
and business_group_id = p_business_group_id
and p_effective_date
between nvl(date_start, p_effective_date)
and nvl(date_end, hr_api.g_eot)))
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date;
select person_id,
pl_id
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
SELECT pen.pgm_id, pen.pl_id, pen.pl_typ_id, pen.oipl_id, pen.ler_id,
pen.person_id, pen.business_group_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 asg.assignment_id, asg.organization_id
FROM per_all_assignments_f asg
WHERE asg.person_id = v_person_id
AND asg.assignment_type <> 'C'
AND asg.primary_flag = 'Y'
AND p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date;
SELECT opt_id
FROM ben_oipl_f oipl
WHERE oipl.oipl_id = v_oipl_id
AND oipl.business_group_id = p_business_group_id
AND p_effective_date BETWEEN oipl.effective_start_date
AND oipl.effective_end_date;
SELECT object_version_number
FROM ben_prtt_enrt_actn_f
WHERE prtt_enrt_actn_id = c_prtt_enrt_actn_id
AND business_group_id = p_business_group_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
select tl.name actn_typ_name
from ben_actn_typ typ,
ben_actn_typ_tl tl
where v_actn_typ_id = typ.actn_typ_id
and typ.actn_typ_id = tl.actn_typ_id
and tl.language = userenv('lang')
and typ.type_cd <> 'BNF'
and typ.type_cd like 'BNF%'
and typ.business_group_id = p_business_group_id;
SELECT pl.NAME
FROM ben_pl_f pl
WHERE pl.pl_id = v_plan_id
AND p_effective_date BETWEEN pl.effective_start_date
AND pl.effective_end_date;
if l_prtt_enrt_actn_id IS NOT NULL and p_datetrack_mode = DTMODE_DELETE then
--
delete_action_item
(p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
,p_object_version_number => l_object_version_number
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => p_rslt_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_post_rslt_flag => p_post_rslt_flag);
if l_prtt_enrt_actn_id IS NOT NULL and p_datetrack_mode = DTMODE_DELETE then
--
delete_action_item
(p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
,p_object_version_number => l_object_version_number
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => p_rslt_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_post_rslt_flag => p_post_rslt_flag);
if l_prtt_enrt_actn_id IS NOT NULL and p_datetrack_mode = DTMODE_DELETE then
--
delete_action_item
(p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
,p_object_version_number => l_object_version_number
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => p_rslt_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_post_rslt_flag => p_post_rslt_flag);
if l_prtt_enrt_actn_id IS NOT NULL and p_datetrack_mode = DTMODE_DELETE then
--
delete_action_item
(p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
,p_object_version_number => l_object_version_number
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => p_rslt_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_post_rslt_flag => p_post_rslt_flag);
delete_action_item
(p_prtt_enrt_actn_id => l_prtt_enrt_actn_id,
p_object_version_number => l_object_version_number,
p_business_group_id => p_business_group_id,
p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
p_rslt_object_version_number => p_rslt_object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_zap,
p_post_rslt_flag => p_post_rslt_flag
);
ben_warnings.delete_warnings
(p_application_short_name => 'BEN',
p_message_name => l_message_name,
p_parma => l_act_name,
p_parmb => plan_name_rec.NAME,
p_person_id => pen_info_rec.person_id
);
if l_prtt_enrt_actn_id IS NOT NULL and p_datetrack_mode = DTMODE_DELETE then
--
delete_action_item
(p_prtt_enrt_actn_id => l_prtt_enrt_actn_id
,p_object_version_number => l_object_version_number
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => p_rslt_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_mode
,p_post_rslt_flag => p_post_rslt_flag);
select pl_bnf_id
from ben_pl_bnf_f,
ben_per_in_ler pil
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between
effective_start_date and effective_end_date
and pil.per_in_ler_id=ben_pl_bnf_f.per_in_ler_id
and pil.business_group_id=p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select pea.prtt_enrt_actn_id,
pea.object_version_number
from ben_prtt_enrt_actn_f pea,
ben_actn_typ typ,
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 typ.type_cd <> 'BNF'
and typ.type_cd like 'BNF%'
and typ.business_group_id = p_business_group_id
and pea.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and pea.actn_typ_id = typ.actn_typ_id
and p_effective_date between pea.effective_start_date
and pea.effective_end_date
;
delete_action_item
(p_prtt_enrt_actn_id => actn_item_rec.prtt_enrt_actn_id
,p_object_version_number => actn_item_rec.object_version_number
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => p_rslt_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_zap
,p_post_rslt_flag => p_post_rslt_flag);
cursor c_rslt is select
rslt.pgm_id,
rslt.pl_id,
rslt.pl_typ_id,
rslt.oipl_id,
rslt.ler_id,
rslt.person_id,
rslt.business_group_id
from ben_prtt_enrt_rslt_f rslt
where rslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between
rslt.effective_start_date and rslt.effective_end_date;
select oipl.opt_id
from ben_oipl_f oipl
where oipl.oipl_id = l_rslt.oipl_id
and business_group_id = p_business_group_id
and p_effective_date between
oipl.effective_start_date and oipl.effective_end_date;
select asg.assignment_id,asg.organization_id
from per_all_assignments_f asg
where asg.person_id = l_rslt.person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and p_effective_date between
asg.effective_start_date and asg.effective_end_date;
select prmry_cntngnt_cd cntgcd,
sum(pct_dsgd_num) prcnt
from ben_pl_bnf_f ,
ben_per_in_ler pil
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and ben_pl_bnf_f.business_group_id = p_business_group_id
and p_effective_date between
ben_pl_bnf_f.effective_start_date and ben_pl_bnf_f.effective_end_date
and pil.per_in_ler_id=ben_pl_bnf_f.per_in_ler_id
and pil.business_group_id=p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
group by prmry_cntngnt_cd;
SELECT pcx.rqd_flag ctflag, pcx.bnf_ctfn_typ_cd ctcvgcd,
pcx.ctfn_rqd_when_rl ctrrl, pcx.rlshp_typ_cd ctrlshcd,
pcx.bnf_typ_cd, pcx.pl_id
FROM ben_pl_bnf_ctfn_f pcx
WHERE pcx.pl_id = v_pl_id
AND ( ( NVL (pcx.bnf_typ_cd, 'O') = 'O'
AND v_contact_type = 'O'
AND pcx.rlshp_typ_cd IS NULL
)
OR -- Bug 5156111
( NVL (pcx.bnf_typ_cd, 'P') = 'P'
AND NVL (pcx.rlshp_typ_cd, v_contact_type) = v_contact_type
AND v_contact_type <> 'O'
)
)
AND pcx.bnf_ctfn_typ_cd = 'NSC'
AND pcx.business_group_id = p_business_group_id
AND p_effective_date BETWEEN pcx.effective_start_date
AND pcx.effective_end_date;
select pcr.contact_type,
plb.bnf_person_id,
plb.pl_bnf_id,
pl.pl_id
from per_all_people_f per,
per_contact_relationships pcr,
ben_pl_f pl,
ben_pl_bnf_f plb,
ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.pl_id = pl.pl_id
and pl.bnf_qdro_rl_apls_flag = 'Y'
and pl.bnf_ctfn_rqd_flag = 'N' -- Flag is named incorrectly(opposite).
and plb.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and plb.prmry_cntngnt_cd = 'PRIMY'
and pcr.contact_person_id = plb.bnf_person_id
and pcr.person_id = pen.person_id
and per.person_id = pen.person_id
and per.marital_status = 'M'
and pcr.business_group_id = p_business_group_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 per.business_group_id = p_business_group_id
and p_effective_date between per.effective_start_date
and per.effective_end_date
and pl.business_group_id = p_business_group_id
and p_effective_date between pl.effective_start_date
and pl.effective_end_date
and plb.business_group_id = p_business_group_id
and p_effective_date between plb.effective_start_date
and plb.effective_end_date
and p_effective_date between nvl(date_start, p_effective_date) -- bug 5362890
and nvl(date_end, hr_api.g_eot)
and pil.per_in_ler_id=plb.per_in_ler_id
and pil.business_group_id=p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
UNION -- Bug 5156111 : Added union clause
select 'O',
plb.organization_id,
plb.pl_bnf_id,
pl.pl_id
from per_all_people_f per,
hr_all_organization_units o,
ben_pl_f pl,
ben_pl_bnf_f plb,
ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.pl_id = pl.pl_id
and pl.bnf_qdro_rl_apls_flag = 'Y'
and pl.bnf_may_dsgt_org_flag = 'Y'
and pl.bnf_ctfn_rqd_flag = 'N' -- Flag is named incorrectly(opposite).
and plb.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and plb.prmry_cntngnt_cd = 'PRIMY'
and o.organization_id = plb.organization_id
and per.person_id = pen.person_id
and per.marital_status = 'M'
and o.business_group_id = p_business_group_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 per.business_group_id = p_business_group_id
and p_effective_date between per.effective_start_date
and per.effective_end_date
and pl.business_group_id = p_business_group_id
and p_effective_date between pl.effective_start_date
and pl.effective_end_date
and plb.business_group_id = p_business_group_id
and p_effective_date between o.date_from
and nvl(o.date_to, p_effective_date)
and pil.per_in_ler_id=plb.per_in_ler_id
and pil.business_group_id=p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select 's'
from ben_pl_bnf_ctfn_prvdd_f
where pl_bnf_id = v_pl_bnf_id
and bnf_ctfn_recd_dt is not null
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date;
select pen.pl_id, pen.oipl_id,pen.sspndd_flag --Bug 2228123 added sspndd_flag
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 pln.invk_dcln_prtn_pl_flag
from ben_pl_f pln
where pln.pl_id = l_pl_id
and p_effective_date between
pln.effective_start_date and pln.effective_end_date;
select invk_wv_opt_flag
from ben_opt_f opt,
ben_oipl_f oipl
where opt.opt_id = oipl.opt_id
and oipl.oipl_id = l_oipl_id
and p_effective_date between
opt.effective_start_date and opt.effective_end_date
and p_effective_date between
oipl.effective_start_date and oipl.effective_end_date;
select pen.person_id,
pln.name
from ben_prtt_enrt_rslt_f pen
,ben_pl_f pln
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.pl_id= pln.pl_id
and pen.business_group_id = p_business_group_id
and pln.business_group_id = p_business_group_id
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
and p_effective_date between pln.effective_start_date
and pln.effective_end_date;
select 'x'
from ben_prtt_enrt_rslt_f pen
where pen.rplcs_sspndd_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null ;