[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* per_in_ler_id to delete_enrollment.
* 05 Nov 1998 Hugh Dang 115.9 Change call to get_parameters.
* 01 Dec 1998 Hugh Dang 115.10 Remove p_mode_cd check.
* 02 Dec 1998 Hugh Dang 115.11 Change logic how to handle report/log,
* and common procedures/Function into
* ben_batch_utils package.
* 11 Dec 1998 S Tee 115.12 Added the per_in_ler_id to process
* common enrollment result procedure.
* 20-Dec-1998 Hdang 115.13 Added audit_log to turn audit log report
* on and off.
* 22-Dec-1998 Hdang 115.14 Turn Audit log report on.
*
* 28-Dec-1998 Jlamoureux 115.15 Removed dflt_enrt_dt, enrt_typ_cycl_cd,
* enrt_perd_strt_dt, and enrt_perd_end_dt
* from c_dflt_mn_epe cursor. These columns
* have moved to pil_elctbl_chc_popl.
* 28-Dec-1998 Hdang 115.16 Add Prtt_enrt_rslt_id in batch_rate tbl.
* 29-Dec-1998 Hdang 115.17 Call ini_proc_info from process.
* 01-Jan-1999 Stee 115.18 Changed post enrollment to pass a process
* code.
* 12-Jan-1999 Hdang 115.19 Added commit to force report info got
* commit.
* 03-Feb-1999 Hdang 115.20 Add logic to default comp obj procedure to
* handle mandatory flag.
* 22-Feb-1999 Hdang 115.21 Chagne p_effective_date data type from date
* to varchar2
*
* 22-Feb-1999 Hdang 115.22 Change multitrhead p_eefective_date.
* 03-MAr-1999 Stee 115.23 Removed dbms_output.put_line.
* 22-MAR-1999 TMathers 115.27 CHanged -MON- to /MM/
* 05-Apr-1999 mhoyes 115.28 - Un-datetrack of per_in_ler_f changes.
* 10-May-1999 jcarpent 115.29 Check ('VOIDD','BCKDT') for pil stt cd
* 20-JUL-1999 Gperry 115.30 genutils -> benutils package rename.
* 12-Aug-1999 lmcdonal 115.31 Start support for enterable cvg amounts.
* To complete, need task 280 to be done
* (changes to benelinf).
* 25-Aug-1999 Gperry 115.32 Added ben_env_object call to multithread.
* 25-AUG-1999 Gperry 115.33 Leapfrog of 115.30 with ben_env_object fix.
* 25-AUG-1999 Gperry 115.34 Leapfrog of 115.32 with ben_env_object fix.
* 14-SEP-1999 shdas 115.35 added bnft_val to election_information
* 06-Oct-1999 tguy 115.36 added call to dt_fndate
* 19-Oct-1999 maagrawa 115.37 Call to write_table_and_file changed to
* log the messages in the log file.
* 10-Nov-1999 jcarpent 115.38 Switched order of post_enrollment and
* post_results calls so that pil is not
* closed too soon.
* 14-Dec-1999 jcarpent 115.39 Moved close enrollment to end of process.
* 28-Dec-1999 stee 115.40 Added per_in_ler_id to multi_rows_edit
* call and removed delete enrollment as it
* will be deleted in the multi_rows_edit
* logic and dependents will be recycled
* properly.
* 01-Feb-2000 gperry 115.41 Fixed WWBUG 1176104. Multithreading not
* working.
* 10-Feb-2000 jcarpent 115.42 Bleeding benefits fixed. bnft vars set
* null within default loop.
* 04-Apr-2000 gperry 115.43 Fixed WWBUG 1217194.
* 11-Apr-2000 mmogel 115.45 Added tokens to messages to make them
* more meaningful to the user
* 18-May-2000 gperry 115.46 No persons selected errors silently now.
* WWBUG 1097159
* 18-May-2000 gperry 115.47 Initial performance fixes.
* 19-May-2000 gperry 115.48 Removed ben_timing stuff.
* 30-May-2000 gperry 115.49 More performance tuning.
* 29-Jun-2000 shdas 115.50 Added call to reinstate_dpnt
* 23-jan-2001 jcarpent 115.51 Bug 1609055. Set ler info when c_pel
* returns no rows.
* 01-Jul-2001 kmahendr 115.52 Unrestricted changes
* 13-Jul-2001 ikasire 115.53 Bug 1834566 changed the cursor c_rt
* where clause to remove
* ecr.prtt_rt_val_id is null condition.
* 18-dec-2001 tjesumic 115.54 cwb changes
* 20-dec-2001 ikasire 115.55 added dbdrv lines
* 30-Apr-2002 kmahendr 115.56 Added write calls to capture error messages.
*
* 08-Jun-02 pabodla 115.57 Do not select the contingent worker
* assignment when assignment data is
* fetched.
* 14-Nov-02 vsethi 115.58 Bug 2370264 In Default_Comp_obj changed exception
* handling for forms (p_batch_flag is false)
* 19-dec-02 pabodla 115.59 NOCOPY Changes
03-Jan-03 tjesumic 115.60 after the enhncemnt # 2685018 cryfwd_elig_dpnt_cd value is
concated with result id from where the dpnt carry forwarded ,
this fix will seprate the code from result id
* 05-Mar-03 hnarayan 115.62 Bug 2828045 - In c_person cursor of Process,
* uncommented BG id check and added check
* to exclude PILs of type COMP and ABS. Also fixed
* p_ler_id parameter check condition in the query.
* 07-Mar-03 tjesumic 115.63 2944657 whne the enrollment is closed from LE form
* min max restriction is nat validated. the enrollment can be
* made without enrolling in a plan, Min reqment of the plan is 1 though
* this is fixed by calling ben_proc_common_enrt_rslt.set_elcn_made_or_asnd_dt
* before multi_rows_edit
* 15-MAY-03 glingapp 115.64 2961251 Passed the pl_id parameter in
* ben_proc_common_enrt_rslt.set_elcn_made_or_asnd_dt.
* 16-Sep-03 kmahendr 115.65 GSP changes
* 22-Sep-03 mmudigon 115.66 Bug 3121395. Changes to c_rt cursor
* 6-oct-03 nhunur 115.67 made changes to exception handling in default_comp_obj procedure.
* 16-Oct-03 tjesumic 115.68 l_prev_enrt_rslt_id resintialized
* 30-Oct-03 ikasire 115.69 Bug 3192923 OVERRIDE thru date needs to be handled
* 13-sep-04 vvprabhu 115.70 Bug 3876613 Procedure Default_Comp_obj_w added
* 05-nov-04 vvprabhu 115.71 Bug 3978573 parameter p_called_from_ss added to
* Default_Comp_obj to pypass multi_row_edit when called from SSBEN.
* 03-Dec-04 ikasire 115.72 Bug 4046914
* 10-Jan-05 lakrish 115.73 3989075, call dt_fndate.change_ses_date to insert a row into
* fnd_sessions while running default enrt process from SS
* 26-Jan-05 ikasire 115.74 BUG 4064635 CF Suspended Interim Changes
* 26-May-05 vborkar 115.75 Bug 4387247 : In wrapper method exception
* handlers changes made to avoid null errors
* on SS pages
* 01-Jun-05 vborkar 115.76 Bug 4387247 : Modifications to take care of
* application exceptions.
* 22-Jun-05 vvprabhu 115.77 Bug 4421813 ben_env_obj.init added to default_comp_obj
* in cases where it is called from self-service.
* 20-Jul-05 ikasire 115.78 Bug 4463836 passing the p_called_from_ss to multi_row_edit call
* 26-Oct-05 bmanyam 115.79 4684991 - Use lf_evt_ocrd_dt instead of p_effective_date
* when checking for overide-thru-date
* 13-Dec-05 vborkar 115.80 Bug 4695708 : Made changes to Default_Comp_obj
* and Default_Comp_obj_w exception handlers
* so that error messages are correctly shown
* in SS.
* 11-Apr-06 swjain 115.82 Bug 4951065 - Updated cursor c_dflt_mn_epe_mndtry
* in procedure Default_Comp_obj
* 03-May-06 abparekh 115.83 Bug 5158204 - use minimum (enrt perd start date, defaults assnd date )
* for default enrollment date
* 14-Sep-06 abparekh 115.84 Bug 5407755 - Modified fix of version 115.83
* 115.85 use NVL(defaults assnd date, enrt perd end date)
* for close enrollment date
* 16-Nov-06 vvprabhu 115.86 Bug 5664300 - added p_called_frm_ss parameter to
* 115.87 process_post_results
* 30-Nov-06 rtagarra 115.88 Bug 5662220 - Added check so that when there is no condition for a person
* in person_selection_rule then skip the person.
* 22-Jan-07 kmahendr 115.89 Bug#5768880 - changed cursor c_pel to filter
out unrestricted pels
* 27-apr-07 nhunur 115.90 changed incorrect join condition introduced above
* 24-May-07 sjilla 115.91 Bug 6027345 - Additional more specific Exception hadler used.
* 06-aug-07 swjain 115.92 Bug 6319484 - Updated cursor c_dflt_mn_epe
* 09-Aug-07 vvprabhu 115.93 Bug 5857493 - added g_audit_flag to
* control person selection rule error logging
* 12-jun-08 bachakra 115.97 Bug 7166971 - added clause in c_pel to apply defaults
for those programs whose default assigned date is less
than effective date. Also removed the fix for 6992857
as that is not the expected functioanlity.
* 28-Jul-09 velvanop 115.99 Fidelity Enhancement Bug No: 8716679
* The enhancement request is to reinstate elections from an intervening event
* with a life event that is backed out and reprocessed. The objective is to allow
* customers to have the ability to determine whether elections made for
* intervening events should be brought forward for a backed out life events.
* 18-Jan-11 stee 115.98 Bug 10377891 - added enrt_mthd_cd check to
* c_dflt_mn_epe.
* 11-Jul-11 velvanop 115.99 Bug 12688164 - Multi row edit validation in default enrollment process should happen after
* the enrollments of backed out LE are reinstated. If no enrollments are
* reinstated multi row edit should be called after applying the defaults
* 30-Apr-12 amnaraya 120.17.12010000.10 Bug: Default elections has to be made on the Enrollment period start
* date and not on the effective date. Reference bug 7623319.
* 28-Jan-13 amnaraya 120.17.12010000.11 Enh 16043887: When the Beneficiaries are carry forwared and the
* Exclude Participant flag is set, show a note.
* -----------------------------------------------------------------------------------
*/
--
-- Global cursor and variables declaration
--
g_package varchar2(80) := 'Ben_manage_default_Enrt';
Select ecd.dpnt_person_id, ecd.cvg_strt_dt, ecd.cvg_thru_dt
From ben_elig_cvrd_dpnt_f ecd,
ben_per_in_ler pil
Where ecd.prtt_enrt_rslt_id is not NULL
and ecd.prtt_enrt_rslt_id = c_prtt_enrt_rslt_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=ecd.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') ;
insert into ben_batch_rate_info
(batch_rt_id,
benefit_action_id,
person_id,
pgm_id,
pl_id,
oipl_id,
dflt_val,
val,
actn_cd,
dflt_flag,
business_group_id,
object_version_number)
values
(ben_batch_rate_info_s.nextval,
benutils.g_benefit_action_id,
p_person_id,
l_cache(i).pgm_id,
l_cache(i).pl_id,
l_cache(i).oipl_id,
l_cache(i).bnft_amt,
l_cache(i).prtt_enrt_rslt_id,
l_actn_cd,
'Y',
p_business_group_id,
1);
insert into ben_batch_dpnt_info
(batch_dpnt_id,
person_id,
benefit_action_id,
business_group_id,
pgm_id,
pl_id,
oipl_id,
enrt_cvg_strt_dt,
enrt_cvg_thru_dt,
actn_cd,
object_version_number,
dpnt_person_id)
values
(ben_batch_dpnt_info_s.nextval,
p_person_id,
benutils.g_benefit_action_id,
p_business_group_id,
l_cache(i).pgm_id,
l_cache(i).pl_id,
l_cache(i).oipl_id,
l_rec.cvg_strt_dt,
l_rec.cvg_thru_dt,
l_actn_cd,
1,
l_rec.dpnt_person_id);
Select ran.range_id
,ran.starting_person_action_id
,ran.ending_person_action_id
From ben_batch_ranges ran
Where ran.range_status_cd = 'U'
And ran.BENEFIT_ACTION_ID = P_BENEFIT_ACTION_ID
And rownum < 2
For update of ran.range_status_cd
;
Select ben.person_id
,ben.person_action_id
,ben.object_version_number
,ben.ler_id
From ben_person_actions ben
Where ben.benefit_action_id = p_benefit_action_id
And ben.action_status_cd <> 'P'
And ben.person_action_id between
l_start_person_action_id and l_end_person_action_id
Order by ben.person_action_id
;
Select *
From ben_benefit_actions ben
Where ben.benefit_action_id = p_benefit_action_id
;
,p_person_selection_rule_id => l_parm.person_selection_rl
,p_person_type_id => l_parm.person_type_id
,p_ler_id => l_parm.ler_id
,p_organization_id => l_parm.organization_id
,p_benfts_grp_id => l_parm.benfts_grp_id
,p_location_id => l_parm.location_id
,p_legal_entity_id => l_parm.legal_entity_id
,p_payroll_id => l_parm.payroll_id
,p_audit_log => l_parm.audit_log_flag
);
update ben_batch_ranges ran set ran.range_status_cd = 'P'
where ran.range_id = l_range_id;
g_cache_person_process.delete;
Select -- to_char(process_date,'YYYY/MM/DD HH24:MI:SS') process_date
fnd_date.date_to_canonical(process_date) process_date
,validate_flag
,person_id
,person_type_id
,pgm_id
,business_group_id
,pl_id
,popl_enrt_typ_cycl_id
,person_selection_rl
,ler_id
,organization_id
,benfts_grp_id
,location_id
,legal_entity_id
,payroll_id
,debug_messages_flag
,audit_log_flag
From ben_benefit_actions ben
Where ben.benefit_action_id = p_benefit_action_id;
,p_person_selection_rule_id in number default null
,p_ler_id in number default null
,p_organization_id in number default null
,p_benfts_grp_id in number default null
,p_location_id in number default null
,p_legal_entity_id in number default null
,p_payroll_id in number default null
,p_debug_messages in varchar2 default 'N'
,p_audit_log in varchar2 default 'N') is
--
-- local variable declaration.
--
l_effective_date date;
select pil.person_id
from ben_per_in_ler pil
where
pil.business_group_id = p_business_group_id -- 2828045
and pil.per_in_ler_id in
(select pel.per_in_ler_id
from ben_pil_elctbl_chc_popl pel
where pel.business_group_id = pil.business_group_id
and pel.per_in_ler_id = pil.per_in_ler_id
and pel.dflt_enrt_dt <= l_effective_date
and pel.dflt_asnd_dt is NULL
and pel.ELCNS_MADE_DT is NULL)
and pil.per_in_ler_stat_cd = 'STRTD'
and (p_person_id is NULL or pil.person_id = p_person_id)
-- and (p_ler_id is NULL or pil.per_in_ler_id = p_ler_id) -- 2828045
and (p_ler_id is NULL or pil.ler_id = p_ler_id)
and exists (select null -- 2828045
from ben_ler_f ler
where ler.ler_id = pil.ler_id
and ler.typ_cd not in ('COMP','ABS','GSP')
and l_effective_date
between ler.effective_start_date
and ler.effective_end_date
)
and (p_person_type_id is null
or exists (select null
from per_person_type_usages ptu
where ptu.person_id = pil.person_id
and ptu.person_type_id = p_person_type_id))
-- Bug : 2170794 Commented the code below and added the new clauses .
/* and ((p_location_id is null
and p_organization_id is null
and p_legal_entity_id is null
and p_payroll_id is null)
or exists (select null
from per_all_assignments_f asg
where nvl(asg.location_id,hr_api.g_number) =
nvl(p_location_id,hr_api.g_number)
and nvl(asg.organization_id,hr_api.g_number) =
nvl(p_organization_id,hr_api.g_number)
and nvl(asg.soft_coding_keyflex_id,hr_api.g_number) =
nvl(p_legal_entity_id,hr_api.g_number)
and nvl(asg.payroll_id, hr_api.g_number)=
nvl(p_payroll_id,hr_api.g_number)
and asg.person_id = pil.person_id
and asg.primary_flag = 'Y'
and asg.business_group_id = pil.business_group_id
and l_effective_date
between asg.effective_start_date
and asg.effective_end_date))
*/
and ((p_location_id is null )
or exists (select null
from per_all_assignments_f asg
where asg.location_id = p_location_id
and asg.person_id = pil.person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and asg.business_group_id = pil.business_group_id
and l_effective_date
between asg.effective_start_date and asg.effective_end_date))
and (( p_organization_id is null )
or exists (select null
from hr_organization_units org,
per_all_assignments_f asg
where asg.organization_id = org.organization_id
and org.organization_id = p_organization_id
and l_effective_date
between org.date_from and nvl(org.date_to,l_effective_date )
and asg.person_id = pil.person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and asg.business_group_id = pil.business_group_id
and l_effective_date
between asg.effective_start_date and asg.effective_end_date))
and (( p_legal_entity_id is null )
or exists (select null
from hr_soft_coding_keyflex hsc,
per_all_assignments_f asg
where asg.person_id = pil.person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and asg.business_group_id = pil.business_group_id
and l_effective_date
between asg.effective_start_date and asg.effective_end_date
and asg.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = to_char(p_legal_entity_id)))
and (( p_payroll_id is null)
or exists (select null
from pay_payrolls_f pay,
per_all_assignments_f asg
where asg.person_id = pil.person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and asg.business_group_id = pil.business_group_id
and l_effective_date
between asg.effective_start_date and asg.effective_end_date
and pay.payroll_id = p_payroll_id
and pay.payroll_id = asg.payroll_id
and l_effective_date
between pay.effective_start_date and pay.effective_end_date ))
-- Bug : 2170794
and (p_benfts_grp_id is null
or exists (select null
from ben_benfts_grp bng,
per_all_people_f ppf
where bng.benfts_grp_id = p_benfts_grp_id
And bng.business_group_id = pil.business_group_id
And ppf.person_id = pil.person_id
And ppf.benefit_group_id = bng.benfts_grp_id
And l_effective_date
between ppf.effective_start_date
and ppf.effective_end_date));
,p_comp_selection_rl => NULL
,p_person_selection_rl => p_person_selection_rule_id
,p_ler_id => p_ler_id
,p_organization_id => p_organization_id
,p_benfts_grp_id => p_benfts_grp_id
,p_location_id => p_location_id
,p_pstl_zip_rng_id => NULL
,p_rptg_grp_id => NULL
,p_pl_typ_id => NULL
,p_opt_id => NULL
,p_eligy_prfl_id => NULL
,p_vrbl_rt_prfl_id => NULL
,p_legal_entity_id => p_legal_entity_id
,p_payroll_id => p_payroll_id
,p_audit_log_flag => p_audit_log
,p_debug_messages_flag => p_debug_messages
,p_object_version_number => l_object_version_number
,p_effective_date => l_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);
if p_person_selection_rule_id is not null then
--
l_actn := 'Calling Ben_batch_utils.person_selection_rule...';
ben_batch_utils.person_selection_rule
(p_person_id => l_person_id_out
,p_business_group_id => p_business_group_id
,p_person_selection_rule_id=> p_person_selection_rule_id
,p_effective_date => l_effective_date);
select ben_person_actions_s.nextval
into l_person_action_id(l_num_rows)
from sys.dual;
insert into ben_person_actions
(person_action_id,
person_id,
ler_id,
benefit_action_id,
action_status_cd,
object_version_number)
values
(l_person_action_id(l_count),
l_person_id(l_count),
0,
l_benefit_action_id,
'U',
1);
select ben_batch_ranges_s.nextval
into l_range_id
from sys.dual;
insert into ben_batch_ranges
(range_id,
benefit_action_id,
range_status_cd,
starting_person_action_id,
ending_person_action_id,
object_version_number)
values
(l_range_id,
l_benefit_action_id,
'U',
l_start_person_action_id,
l_end_person_action_id,
1);
l_person_action_id.delete;
l_person_id.delete;
insert into ben_person_actions
(person_action_id,
person_id,
ler_id,
benefit_action_id,
action_status_cd,
object_version_number)
values
(l_person_action_id(l_count),
l_person_id(l_count),
0,
l_benefit_action_id,
'U',
1);
select ben_batch_ranges_s.nextval
into l_range_id
from sys.dual;
insert into ben_batch_ranges
(range_id,
benefit_action_id,
range_status_cd,
starting_person_action_id,
ending_person_action_id,
object_version_number)
values
(l_range_id,
l_benefit_action_id,
'U',
l_start_person_action_id,
l_end_person_action_id,
1);
l_person_action_id.delete;
l_person_id.delete;
,p_person_selection_rule_id => p_person_selection_rule_id
,p_person_type_id => p_person_type_id
,p_ler_id => p_ler_id
,p_organization_id => p_organization_id
,p_benfts_grp_id => p_benfts_grp_id
,p_location_id => p_location_id
,p_legal_entity_id => p_legal_entity_id
,p_payroll_id => p_payroll_id
,p_audit_log => p_audit_log);
'<< No Person got selected with above selection criteria >>' );
,p_person_selected => l_num_persons
,p_business_group_id => p_business_group_id);
,p_person_selected => l_num_persons
,p_business_group_id => p_business_group_id);
,p_person_selected => l_num_persons
,p_business_group_id => p_business_group_id);
Select epe.ELIG_PER_ELCTBL_CHC_ID
,epe.PGM_ID
,epe.PL_ID
,epe.OIPL_ID
,epe.DPNT_CVG_STRT_DT_CD
,epe.DPNT_CVG_STRT_DT_RL
,epe.ENRT_CVG_STRT_DT
,epe.CRYFWD_ELIG_DPNT_CD
,epe.CRNTLY_ENRD_FLAG
,epe.DFLT_FLAG
,epe.ELCTBL_FLAG
,epe.MNDTRY_FLAG
,epe.AUTO_ENRT_FLAG
,epe.PRTT_ENRT_RSLT_ID
,epe.BUSINESS_GROUP_ID
,'DEF' actn_cd
,'N' Suspended
,epe.in_pndg_wkflow_flag
From ben_elig_per_elctbl_chc epe
Where epe.Auto_enrt_flag = 'N'
and epe.per_in_ler_id = p_per_in_ler_id
and epe.Business_group_id = p_business_group_id
and (epe.elctbl_flag = 'Y' or epe.mndtry_flag = 'Y')
and (p_pgm_id is NULL or epe.pgm_id = p_pgm_id)
and (p_pl_nip_id is null
or (p_pl_nip_id = epe.pl_id and epe.pgm_id is NULL) )
/* Modified the condition for Enhancement Bug :8716679. Defaulting the explicit elections
will only be called if p_reinstate_dflts_flag = 'Y' or else normal defaulting logic will work.*/
and ( (p_reinstate_dflts_flag = 'N' and (epe.dflt_flag = 'Y' or epe.crntly_enrd_flag = 'Y') ) or
(p_reinstate_dflts_flag = 'Y' and epe.crntly_enrd_flag = 'Y' and prtt_enrt_rslt_id is not null
and 'Y' = ( select 'Y' from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
and pen.per_in_ler_id = p_prev_per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_mthd_cd = 'E'
and pen.sspndd_flag = 'N'
and not exists
(select 'Y' from ben_prtt_enrt_rslt_f pen2
where pen.prtt_enrt_rslt_id = pen2.rplcs_sspndd_rslt_id
and pen2.prtt_enrt_rslt_stat_cd is null
and pen2.per_in_ler_id = p_prev_per_in_ler_id) )
) )
/* End of change for Enhancement Bug :8716679 */
and not exists (select null from ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id = epe.per_in_ler_id
-- Bug 6319484 Instead of checking for same pen_id,
-- check if not already enrolled in same plan
and nvl(pen.pgm_id,hr_api.g_number) = nvl(epe.pgm_id,hr_api.g_number)
--
-- bug 10377891. If there are multiple options for a plan and one is
-- a rollover that was automatically enrolled.
--
and (pen.pl_id = epe.pl_id
and pen.enrt_mthd_cd <> 'A') -- bug 10377891
-- and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd IS NULL
and pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt = hr_api.g_eot )
order by epe.pgm_id, epe.pl_id;
select pen.prtt_enrt_rslt_id||'Y' from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = c_pen_id
and pen.per_in_ler_id = l_prev_pil_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_mthd_cd = 'E'
and pen.sspndd_flag = 'N'
and not exists
(select 'Y' from ben_prtt_enrt_rslt_f pen2
where pen.prtt_enrt_rslt_id = pen2.rplcs_sspndd_rslt_id
and pen2.prtt_enrt_rslt_stat_cd is null
and pen2.per_in_ler_id = l_prev_pil_id);
Select epe.ELIG_PER_ELCTBL_CHC_ID
,epe.PGM_ID
,epe.PL_ID
,epe.OIPL_ID
,epe.DPNT_CVG_STRT_DT_CD
,epe.DPNT_CVG_STRT_DT_RL
,epe.ENRT_CVG_STRT_DT
,epe.CRYFWD_ELIG_DPNT_CD
,epe.CRNTLY_ENRD_FLAG
,epe.DFLT_FLAG
,epe.ELCTBL_FLAG
,epe.MNDTRY_FLAG
,epe.AUTO_ENRT_FLAG
,epe.PRTT_ENRT_RSLT_ID
,epe.BUSINESS_GROUP_ID
,'DEF' actn_cd
,'N' Suspended
,epe.in_pndg_wkflow_flag
From ben_elig_per_elctbl_chc epe
Where epe.dflt_flag = 'N'
and epe.crntly_enrd_flag = 'N'
and epe.mndtry_flag = 'Y'
and epe.Auto_enrt_flag = 'N'
and epe.per_in_ler_id = p_per_in_ler_id
and epe.Business_group_id = p_business_group_id
and nvl(epe.pgm_id,hr_api.g_number) = nvl(c_pgm_id, hr_api.g_number)
and epe.pl_id = c_pl_id
and comp_lvl_cd = 'OIPL'; -- Bug 4951065
Select prtt_enrt_rslt_id
,effective_start_date
,effective_end_date
,object_version_number
,bnft_amt
,uom
,enrt_mthd_cd
,business_group_id
,enrt_cvg_strt_dt
,enrt_cvg_thru_dt
,ERLST_DEENRT_DT
,enrt_ovrid_thru_dt
,enrt_ovridn_flag
From ben_prtt_enrt_rslt_f
Where prtt_enrt_rslt_id = lc_prtt_enrt_rslt_id
and p_effective_date between
effective_start_date and effective_end_date
and prtt_enrt_rslt_stat_cd IS NULL
and business_group_id = p_business_group_id
;
Select ecr.enrt_rt_id
,nvl(ecr.val,ecr.dflt_val) default_val
,ecr.ANN_DFLT_VAL
From ben_enrt_rt ecr
Where ecr.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
and ecr.business_group_id = p_business_group_id
and ecr.entr_val_at_enrt_flag = 'Y'
-- and ecr.prtt_rt_val_id is null -- ikasire bug 1834655
Union
Select ecr.enrt_rt_id
,nvl(ecr.val,ecr.dflt_val) default_val
,ecr.ANN_DFLT_VAL
From ben_enrt_rt ecr
,ben_enrt_bnft enb
Where enb.enrt_bnft_id = ecr.enrt_bnft_id
and ecr.business_group_id = p_business_group_id
and enb.business_group_id = p_business_group_id
and enb.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
and ecr.entr_val_at_enrt_flag = 'Y'
-- and ecr.prtt_rt_val_id is null -- ikasire bug 1834655
;
Select enrt_bnft_id, val, dflt_val, entr_val_at_enrt_flag,cvg_mlt_cd
From ben_enrt_bnft
Where elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id
and dflt_flag = 'Y'
;
SELECT pel.enrt_perd_strt_dt
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 = v_elig_per_elctbl_chc_id;
SELECT pl.bnf_exl_participant_flag
,pl.name
FROM ben_pl_f pl
WHERE pl.pl_id = l_pl_id
AND p_effective_date
BETWEEN pl.effective_start_date
AND pl.effective_end_date;
SELECT 1
FROM ben_pl_bnf_f
WHERE prtt_enrt_rslt_id = l_pen_id
AND bnf_person_id = p_person_id
AND p_effective_date
BETWEEN effective_start_date
AND effective_end_date;
l_datetrack_mode := hr_api.g_update;
l_datetrack_mode := hr_api.g_insert;
if l_datetrack_mode = hr_api.g_insert and l_cryfwd_elig_dpnt_cd = 'CFRRWP' then
ben_automatic_enrollments.reinstate_dpnt
(p_pgm_id => l_epe(i).pgm_id,
p_pl_id => l_epe(i).pl_id,
p_oipl_id => l_epe(i).oipl_id,
p_business_group_id => p_business_group_id,
p_person_id => p_person_id,
p_per_in_ler_id => p_per_in_ler_id,
p_elig_per_elctbl_chc_id => l_epe(i).elig_per_elctbl_chc_id,
p_dpnt_cvg_strt_dt_cd => l_epe(i).dpnt_cvg_strt_dt_cd,
p_dpnt_cvg_strt_dt_rl => l_epe(i).dpnt_cvg_strt_dt_rl,
p_enrt_cvg_strt_dt => l_epe(i).enrt_cvg_strt_dt,
p_effective_date => p_effective_date,
p_prev_prtt_enrt_rslt_id => l_prev_prtt_enrt_rslt_id
);
Select a.PER_IN_LER_ID
,a.PGM_ID
,a.PL_ID
,b.lf_evt_ocrd_dt
,b.ler_id
,a.dflt_enrt_dt
,a.enrt_perd_strt_dt
,a.enrt_perd_end_dt
From Ben_pil_elctbl_chc_popl a
,ben_per_in_ler b
,ben_ler_f ler
Where a.PIL_ELCTBL_POPL_STAT_CD = 'STRTD'
--and a.business_group_id = b.business_group_id
and a.business_group_id = p_business_group_id
and a.per_in_ler_id = b.per_in_ler_id
and a.dflt_enrt_dt <= p_effective_date -- 7166971
and b.per_in_ler_stat_cd = 'STRTD'
and a.ELCNS_MADE_DT is NULL
and ler.ler_id = b.ler_id
and ler.typ_cd not in ('SCHEDDU') -- bug5768880
and p_effective_date between ler.effective_start_date
and ler.effective_End_date
and b.person_id = p_person_id ;
Select b.PER_IN_LER_ID
,b.lf_evt_ocrd_dt
,b.ler_id
From ben_per_in_ler b,
ben_ler_f ler
Where b.per_in_ler_stat_cd = 'STRTD'
and b.person_id = p_person_id
and b.ler_id = ler.ler_id
and ler.typ_cd not in ('COMP','GSP')
and p_effective_date between
ler.effective_start_date and
ler.effective_end_date
;
l_actn := 'Calling ben_person_actions_api.update_person_actions...';
update ben_person_actions
set action_status_cd = 'P'
where person_action_id = p_person_action_id;
ben_person_actions_api.update_person_actions
(p_person_action_id => p_person_action_id
,p_action_status_cd => 'E'
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date
);