[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
STEE Update Pil elctbl chc popl with elcn_made_dt
,auto_asnd_dt and dflt_asnd_dt instead of
per_in_ler_f. Add call to generate
communications.
115.4 10-DEC-1998 bbulusu added per_in_ler_id to the process_post_res
ults procedure.
115.5 11-DEC-1998 stee Fix to select per_in_ler from table instead
of view.
115.6 01-JAN-1999 stee Added new parameters, pgm_id, pl_id and
ler_id to ben_generate_communications.
115.7 13-JAN-1999 stee Added new parameter to call to
ben_det_imputed_income.
115.8 10-FEB-1999 yrathman Added set_elcn_made_or_asnd_dt procedure,
moved coded from process_post_enrollment
115.9 22-FEB-1999 stee Move generate communications to run
before close enrollment bug#1918.
115.10 09-APR-1999 mhoyes Un-datetrack of per_in_ler_f changes.
115.11 21-jun-1999 jcarpent Added uneai to close single enrollment.
115.12 02-JUL-1999 stee Update COBRA information.
115.13 02-OCT-1999 stee Changed update_cobra_info to a new
procedure ben_cobra_requirements.
update_prtt_cobra_info.
115.14 01-MAR-2000 stee Remove update_prtt_cobra_info. Cobra
qualified beneficiary are now written
during benmngle. COBRA eligibility is
evaluated during close enrollment.
115.15 11-APR-2000 gperry Added extra param for fido ctrl m call.
115.16 31-Jul-2000 pzclark Added a wrapper to procedure
process_post_enrollment to allow self
service java code to call using varchar2
'TRUE' or 'FALSE' instead of booleans.
115.17 15-Aug-2000 maagrawa Added procedure process_post_enrt_calls_w
(wrapper for self-service).
115.18 05-sep-2000 pbodla Bug 5422 : Allow different enrollment periods
for programs for a scheduled enrollment.
p_popl_enrt_typ_cycl_id is removed.
115.19 06-Sep-2000 maagrawa Backported to 115.17. Added exception
handling for self-service wrapper.
115.20 06-Sep-2000 maagrawa Leapfrog. 115.18 +115.19 changes.
115.21 14-Sep-2000 pzclark Backported to 115.19. Added when others
exception to process_post_enrollment_w
procedure.
115.22 14-Sep-2000 pzclark Leapfrog 115.21. Added when others
exception to process_post_enrollment_w
procedure to version 115.20
115.23 14-Sep-2000 pzclark Backported to 115.21, added EXIT to end of
Package
115.24 14-Sep-2000 pzclark Leapfrog 115.23. Back to version 115.22
115.25 12-Mar-2001 ikasire bug 1644520 fixed the problem in election
made date
115.26 13-Mar-2001 ikasire bug 1644520 fixed the problem in election
made date. taking max of pen.effective_start_date
for the pil,pgm and plan - added a cursor
115.28 13-Mar-2001 ikasire to correct the version numbers read 25 as 26
26 as 27
115.29 17-May-2001 maagrawa Added parameter self_service_flag to
process_post_results for performance.
115.30 18-dec-2001 tjesumic cwb changes
115.31 19-dec-2001 tjesumic cwb changes
115.32 20-dec-2001 ikasire added dbdrv lines
115.33 02-jan-2002 tjesumic 2170324 paramter for proc_cd2 to proc_cd5 added
115.34 06-nov-2002 shdas bug fix 2656718 -- added fnd_msg_pub.initialize
enrollment selection page was showing some cached
message from fnd table
115.35 13-aug-2003 kmahendr Added codes for coverage calculation for ERL
115.36 22-aug-2003 kmahendr Multi_row edit proc called after election_info.
115.37 02-sep-2003 kmahendr Bug#3120675 - warnings handled
115.38 02-sep-2003 kmahendr Message nos changed
115.39 23-jul-2004 kmahendr Bug#3772143 - added parameter - p_include_erl to multi_rows_edit
115.40 28-jul-2004 kmahendr Bug#3772143 - Multi_rows_edit called outside the loop
115.41 11-oct-2004 kmahendr Bug#3944970 - added parameter to process_post_results
and imputed_income call is not made for ICD enrollments
115.42 03-Dec-2004 ikasire Bug 3988565 Changed effective_date data type to Date
115.43 15-Mar-2005 ikasire overloaded process_post_enrt_calls_w with a new
parameter p_self_service_flag to avoid conversion issues
115.44 17-may-05 ssarkar Bug: 4362939 : modified process_post_results -- 'l_call_multi_rows' set outside the coverage amount chk.
115.45 26-May-05 vborkar Bug 4387247 : In wrapper method exception
handlers changes made to avoid null errors
on SS pages
115.46 01-Jun-05 vborkar Bug 4387247 : Modifications to take care of
application exceptions.
115.47 06-Jun-05 ikasire Bug 4414127
115.48 17-Jun-05 vborkar Bug 4436578 : In SS wrapper app exception handler
added generic(default) exception handler code.
115.49 02-Nov-05 ikasire BUG 4709601 we don't need to multi_rows_edit for
CFW process
115.50 11-Nov-05 ikasire BUG 4718599 fixed ERL cursor to return the correct
number of records.
115.51 26-Oct-06 bmanyam 5621577 When CVG is ERL and Rates are
Enter Value At Enrollment, fetch rates again
during re-enrollment in process_post_results
115.53 09-Nov-06 rtagarra Added condition while calling close_single_enrollment so that fix 5529696 wont be
overriden by 5527233.
115.54 16-Nov-06 vvprabhu Bug 5664300 - parameter p_called_from_ss added
115.55 to process_post_results
155.56 31-Aug-07 rtagarra Bug 5997904 : Life Event will be closed when user makes election when
the code is 'ELCNSMADE'
115.57 19-Oct-08 sagnanas 7447088 - Added enrt_mthd_cd 'D' in cursor c_enrt_rslt
115.58 28-Oct-08 sagnanas 7510533 - Commented pen.per_in_ler_id in cursor c_enrt_rslt
115.59 29-Sep-09 stee 8930024 - Fix c_enrt_rslt to exclude enrt_mthd_cd = 'D'
if called from automatic enrollment.
115.60 27-Oct-09 stee 9026755 - Change fix for bug 7510533. Check the
electable flag instead.
115.61 02-Sep-11 velvanop Bug 12938378 - Do not call multi_rows_edit from 'process_post_results' when Automatic enrollments are applied.
multi-row edit is called and the enrollment results are terminated since the per_in_ler_id
has not been updated with the current per_in_ler_id.
115.62 23-Nov-11 velvanop Bug 13367099: Do not consider Imputed Income enrollment to determine Elections Made date
*/
------------------------------------------------------------------------------
g_debug boolean := hr_utility.debug_enabled;
select pel.pil_elctbl_chc_popl_id
,pel.object_version_number
,pel.elcns_made_dt
from ben_pil_elctbl_chc_popl pel
where pel.per_in_ler_id = p_per_in_ler_id
and pel.pgm_id = p_pgm_id;
select pel.pil_elctbl_chc_popl_id
,pel.object_version_number
,pel.elcns_made_dt
from ben_pil_elctbl_chc_popl pel
where pel.per_in_ler_id = p_per_in_ler_id
and pel.pl_id = p_pl_id;
select max(pen.EFFECTIVE_START_DATE) elcns_made_dt
from ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id = p_per_in_ler_id
and pen.pgm_id = p_pgm_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.comp_lvl_cd <> 'PLANIMP'; -- Bug 13367099
select max(pen.EFFECTIVE_START_DATE) elcns_made_dt
from ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id = p_per_in_ler_id
and pen.pl_id = p_pl_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt = hr_api.g_eot ;
ben_Pil_Elctbl_chc_Popl_api.update_Pil_Elctbl_chc_Popl
(p_validate => p_validate
,p_pil_elctbl_chc_popl_id => l_pil_elctbl_chc_popl_id
,p_elcns_made_dt => nvl(l_new_elcns_made_dt ,p_effective_date)
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
);
ben_Pil_Elctbl_chc_Popl_api.update_Pil_Elctbl_chc_Popl
(p_validate => p_validate
,p_pil_elctbl_chc_popl_id => l_pil_elctbl_chc_popl_id
,p_dflt_asnd_dt => p_effective_date
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
);
ben_Pil_Elctbl_chc_Popl_api.update_Pil_Elctbl_chc_Popl
(p_validate => p_validate
,p_pil_elctbl_chc_popl_id => l_pil_elctbl_chc_popl_id
,p_auto_asnd_dt => p_effective_date
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
);
select per_in_ler_id
from ben_per_in_ler
where person_id = p_person_id
and per_in_ler_stat_cd = 'STRTD'
and business_group_id = p_business_group_id;
select lf_evt_ocrd_dt
from ben_per_in_ler pil
where pil.per_in_ler_id = l_per_in_ler_id;
select pen.*,
enb.elig_per_elctbl_chc_id,
enb.enrt_bnft_id
from ben_prtt_enrt_rslt_f pen,
ben_enrt_bnft enb,
ben_elig_per_elctbl_chc epe
where pen.person_id = c_person_id
and ( pen.enrt_mthd_cd = c_enrt_mthd_cd
or pen.enrt_mthd_cd = 'O'
or ( (pen.enrt_mthd_cd = 'D' or pen.enrt_mthd_cd = 'E') --7447088 --Bug 12938378: Check for Explicit enrollments
and c_enrt_mthd_cd <> 'A')) -- 8930024
and pen.prtt_enrt_rslt_stat_cd is null
/* 9026755 */
and ((pen.per_in_ler_id = c_per_in_ler_id
and epe.elctbl_flag = 'Y')
or (pen.per_in_ler_id <> c_per_in_ler_id
and epe.elctbl_flag = 'N'))
/* end 9026755 */
and enrt_cvg_thru_dt = hr_api.g_eot
and pen.comp_lvl_cd <> 'PLANIMP'
and pen.prtt_enrt_rslt_id = enb.prtt_enrt_rslt_id
and enb.cvg_mlt_cd = 'ERL'
and c_effective_date
between pen.effective_start_date and pen.effective_end_date
and pen.effective_end_date = hr_api.g_eot
and enb.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and epe.per_in_ler_id = c_per_in_ler_id
;
select ecr.enrt_rt_id,
prv.rt_val,
prv.ann_rt_val,
prv.prtt_rt_val_id
from ben_prtt_rt_val prv,
ben_elig_per_elctbl_chc epe,
ben_enrt_bnft enb,
ben_enrt_rt ecr
where prv.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and prv.prtt_rt_val_stat_cd is null
and ecr.entr_val_at_enrt_flag = 'Y'
and ecr.prtt_rt_val_id = prv.prtt_rt_val_id
and epe.per_in_ler_id = c_per_in_ler_id
and epe.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
and ecr.enrt_bnft_id(+) = enb.enrt_bnft_id
and NVL(ecr.elig_per_elctbl_chc_id, epe.elig_per_elctbl_chc_id) = epe.elig_per_elctbl_chc_id
;
SELECT 'Y' interim_flag,
prtt_enrt_rslt_id suspended_pen_id,
object_version_number suspended_ovn
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.rplcs_sspndd_rslt_id = c_interim_pen
AND pen.person_id = c_person_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.per_in_ler_id = c_per_in_ler_id
AND pen.enrt_cvg_thru_dt = hr_api.g_eot
AND c_effective_date BETWEEN pen.effective_start_date
AND pen.effective_end_date
AND pen.effective_end_date = hr_api.g_eot;
select name,
SUBJ_TO_IMPTD_INCM_TYP_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;
p_datetrack_mode => hr_api.g_update,
p_suspend_flag => l_dummy_char,
p_effective_start_date => l_dummy_date,
p_effective_end_date => l_dummy_date,
p_object_version_number => l_enrt_rslt.object_version_number,
p_prtt_enrt_interim_id => l_dummy_number,
p_business_group_id => p_business_group_id,
p_dpnt_actn_warning => l_DPNT_ACTN_WARNING,
p_bnf_actn_warning => l_BNF_ACTN_WARNING,
p_ctfn_actn_warning => l_ctfn_actn_warning);
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_validate => FALSE,
p_prtt_enrt_rslt_id => l_suspended_pen_id,
p_effective_start_date => l_dummy_date,
p_effective_end_date => l_dummy_date,
p_business_group_id => p_business_group_id,
p_RPLCS_SSPNDD_RSLT_ID => l_enrt_rslt.prtt_enrt_rslt_id,
p_object_version_number => l_suspended_ovn,
p_effective_date => p_effective_date,
p_datetrack_mode => l_datetrack_mode,
p_multi_row_validate => FALSE,
p_program_application_id => fnd_global.prog_appl_id,
p_program_id => fnd_global.conc_program_id,
p_request_id => fnd_global.conc_request_id,
p_program_update_date => sysdate);
has not been updated with the current per_in_ler_id. Call multi_rows_edit when p_enrt_mthd_cd <> 'A' */
if p_enrt_mthd_cd <> 'A' and l_call_multi_rows and nvl(ben_sspndd_enrollment.g_cfw_flag,'Y') = 'N' then
ben_prtt_enrt_result_api.multi_rows_edit(
p_person_id => p_person_id
,p_effective_date =>p_effective_date
,p_business_group_id =>p_business_group_id
,p_pgm_id => l_enrt_rslt.pgm_id
,p_per_in_ler_id =>p_per_in_ler_id
,p_include_erl => 'Y'
,p_called_frm_ss => p_called_frm_ss
);
select enp.asnd_lf_evt_dt
from ben_pil_elctbl_chc_popl pel,
ben_enrt_perd enp
where pel.per_in_ler_id = p_per_in_ler_id
and nvl(pel.pgm_id,-1) = nvl(p_pgm_id,-1)
and nvl(pel.pl_id,-1) = nvl(p_pl_id,-1)
and pel.enrt_perd_id = enp.enrt_perd_id;