[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select a.pil_elctbl_chc_popl_id
,a.cls_enrt_dt_to_use_cd
,a.dflt_asnd_dt
,a.dflt_enrt_dt
,a.auto_asnd_dt
,a.elcns_made_dt
,a.enrt_perd_end_dt
,a.enrt_perd_strt_dt
,a.enrt_typ_cycl_cd
,a.pgm_id
,a.pl_id
,a.pil_elctbl_popl_stat_cd
,a.procg_end_dt
,'N' set_flag
,a.object_version_number
,a.defer_deenrol_flag
from ben_pil_elctbl_chc_popl a
where a.per_in_ler_id = c_per_in_ler_id
and a.business_group_id = c_business_group_id
and a.pil_elctbl_popl_stat_cd = 'STRTD';
select 'Y'
from ben_elig_per_elctbl_chc
where (elctbl_flag = 'Y' -------Bug 8531750
or crntly_enrd_flag = 'Y') -------Bug 8531750
and auto_enrt_flag = 'N'
and pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id;
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 effective_start_date
and 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');
procedure update_per_in_ler
(p_per_in_ler_id in number
,p_ler_id in number
,p_person_id in number
,p_effective_date in date
,p_business_group_id in number
,p_per_in_ler_stat_cd in varchar2
,p_object_version_number in number
,p_datetrack_mode in varchar2
)
is
--
l_effective_start_date date;
l_proc varchar2(80):=g_package||'.update_per_in_ler';
ben_person_life_event_api.update_person_life_event
(p_validate => FALSE
,p_per_in_ler_id => p_per_in_ler_id
,p_per_in_ler_stat_cd => p_per_in_ler_stat_cd
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
,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
,p_procd_dt => l_procd_dt
,p_strtd_dt => l_strtd_dt
,p_voidd_dt => l_voidd_dt);
benutils.update_life_event_cache(p_open_and_closed => 'N');
end update_per_in_ler;
select pil.object_version_number
,pil.person_id
,pil.ler_id
,pil.lf_evt_ocrd_dt
,ppf.business_group_id
,pil.per_in_ler_stat_cd
from ben_per_in_ler pil,
per_all_people_f ppf,
ben_ler_f ler
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd = 'STRTD'
and ler.ler_id = pil.ler_id
and ppf.person_id = pil.person_id
and p_effective_date
between ppf.effective_start_date
and ppf.effective_end_date
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and ler.typ_cd <> 'SCHEDDU';
select prtt_enrt_rslt_id,
person_id,
pgm_id,
pl_id,
oipl_id,
object_version_number,
ler_id
from ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id = p_per_in_ler_id
and pen.no_lngr_elig_flag = 'Y'
and nvl(pen.enrt_cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
and pen.business_group_id = p_business_group_id;
select 'Y'
from wf_item_activity_statuses process ,
wf_process_activities activity ,
hr_api_transactions txn,
hr_api_transaction_steps step ,
hr_api_transaction_values vlv,
wf_item_attribute_values submit_attribute
where activity.process_name = 'ROOT'
and activity.process_item_type = activity.activity_item_type
and activity.instance_id = process.process_activity
and process.activity_status = 'ACTIVE'
and txn.item_type = process.item_type
and txn.item_key = process.item_key
and txn.selected_person_id = l_person_id
and txn.transaction_id = step.transaction_id
and step.api_name = 'BEN_PROCESS_COMPENSATION_W.PROCESS_API'
and vlv.number_value is not null
and submit_attribute.text_value = 'Y'
and txn.item_type = submit_attribute.item_type
and txn.item_key = submit_attribute.item_key
and submit_attribute.name = 'TRAN_SUBMIT'
and step.transaction_step_id = vlv.transaction_step_id
and vlv.name = 'P_PER_IN_LER_ID'
and vlv.number_value = p_per_in_ler_id;
select null
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and ler.ler_id = pil.ler_id
and ler.business_group_id = p_business_group_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and ler.typ_cd = 'SCHEDDU';
select ELIG_PER_ELCTBL_CHC_ID,
ELCTBL_FLAG
from ben_elig_per_elctbl_chc
where per_in_ler_id =p_per_in_ler_id
and business_group_id = p_business_group_id;
SELECT leer.lee_rsn_id
FROM ben_lee_rsn_f leer,
ben_popl_enrt_typ_cycl_f petc
WHERE leer.ler_id = c_ler_id
AND leer.business_group_id = p_business_group_id
AND p_effective_date BETWEEN leer.effective_start_date
AND leer.effective_end_date
AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
AND petc.pl_id = c_pl_id
AND petc.enrt_typ_cycl_cd = 'L' -- life event
AND petc.business_group_id = p_business_group_id
AND p_effective_date BETWEEN petc.effective_start_date
AND petc.effective_end_date;
SELECT leer.lee_rsn_id
FROM ben_lee_rsn_f leer,
ben_popl_enrt_typ_cycl_f petc
WHERE leer.ler_id = c_ler_id
AND leer.business_group_id = p_business_group_id
AND p_effective_date BETWEEN leer.effective_start_date
AND leer.effective_end_date
AND leer.popl_enrt_typ_cycl_id = petc.popl_enrt_typ_cycl_id
AND petc.pgm_id = c_pgm_id
AND petc.enrt_typ_cycl_cd = 'L'
AND petc.business_group_id = p_business_group_id
AND p_effective_date BETWEEN petc.effective_start_date
AND petc.effective_end_date;
select
max(a.enrt_perd_end_dt),
min(a.enrt_perd_strt_dt)
from ben_pil_elctbl_chc_popl a
where a.per_in_ler_id = c_per_in_ler_id
and a.business_group_id = c_business_group_id
and a.pil_elctbl_popl_stat_cd = 'STRTD';
SELECT elctbl_flag
FROM ben_elig_per_elctbl_chc epe1
WHERE epe1.business_group_id = p_business_group_id
AND epe1.pil_elctbl_chc_popl_id = p_popl_id
AND epe1.elctbl_flag = 'Y';
SELECT elctbl_flag
FROM ben_elig_per_elctbl_chc epe
WHERE epe.business_group_id = p_business_group_id
AND epe.pil_elctbl_chc_popl_id = p_popl_id
AND epe.crntly_enrd_flag = 'Y'
AND epe.auto_enrt_flag = 'N';
ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl
(p_validate => p_validate
,p_pil_elctbl_chc_popl_id => l_rec.pil_elctbl_chc_popl_id
,p_pil_elctbl_popl_stat_cd => 'PROCD'
,p_business_group_id => p_business_group_id
,p_object_version_number => l_rec.object_version_number
,p_effective_date => p_effective_date
,p_defer_deenrol_flag => l_rec.defer_deenrol_flag);
ben_prtt_enrt_result_api.delete_enrollment
(p_validate => p_validate
,p_per_in_ler_id => p_per_in_ler_id
,p_prtt_enrt_rslt_id => rslt.prtt_enrt_rslt_id
,p_business_group_id => p_business_group_id
,p_effective_start_date => l_rslt_eff_start_date
,p_effective_end_date => l_rslt_eff_end_date
,p_object_version_number => rslt.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => 'DELETE'
,p_multi_row_validate => TRUE
,p_source => 'benclenr');
ben_newly_ineligible.defer_delete_enrollment
(p_per_in_ler_id => p_per_in_ler_id,
p_person_id => l_person_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date
);
update_per_in_ler
(p_per_in_ler_id => p_per_in_ler_id
,p_ler_id => l_ler_id
,p_person_id => l_person_id
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
,p_per_in_ler_stat_cd => 'PROCD'
,p_object_version_number => l_object_version_number
,p_datetrack_mode => hr_api.g_correction);
benutils.update_life_event_cache(p_open_and_closed => 'Y');
ben_person_actions_api.update_person_actions
(p_person_action_id => p_person_action_id
,p_action_status_cd => 'P'
,p_object_version_number => l_pers_ovn
,p_effective_date => p_effective_date);
ben_person_actions_api.update_person_actions
(p_person_action_id => p_person_action_id
,p_action_status_cd => 'E'
,p_object_version_number => l_pers_ovn
,p_effective_date => p_effective_date);
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_location_id => l_parm.location_id
,p_ler_id => l_parm.ler_id
,p_mode => l_parm.mode_cd -- 1674123
,p_audit_log => p_audit_log);
update ben_batch_ranges ran
set ran.range_status_cd = 'P'
where ran.range_id = l_range_id;
hr_utility.set_location('Updated range ' || to_char(l_range_id) ||
' status code to P', 10);
g_cache_per_proc.delete;
update ben_person_actions
set action_status_cd = 'T'
where person_action_id = l_person_action_id;
update ben_person_actions
set action_status_cd = 'E'
where person_action_id = g_cache_per_proc(l_cnt).person_action_id;
select -- to_char(process_date, 'YYYY/MM/DD HH24:MI:SS') process_date
fnd_date.date_to_canonical(process_date) process_date
,business_group_id
,pgm_id
,pl_id
,location_id
,ler_id
-- PB : 5422 :
,lf_evt_ocrd_dt
-- ,popl_enrt_typ_cycl_id
,person_id
,person_selection_rl
,validate_flag
,debug_messages_flag
,audit_log_flag
,close_uneai_flag
,uneai_effective_date
,mode_cd -- 1674123
From ben_benefit_actions ben
Where ben.benefit_action_id = p_benefit_action_id;
,p_Person_selection_rl in number default NULL
,p_validate in varchar2 default 'N'
,p_debug_messages in varchar2 default 'N'
,p_audit_log in varchar2 default 'N'
,p_uneai_effective_date in varchar2 default null
,p_close_uneai_flag in varchar2 default 'Y'
,p_close_cd in varchar2 default 'NORCLOSE' -- 1674123
)
is
--
-- Local variable declaration.
--
l_uneai_effective_date date;
select distinct pil.person_id
,pil.per_in_ler_id
from ben_per_in_ler pil,
per_all_people_f per,
ben_ler_f ler
where pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd = 'STRTD'
and pil.person_id = per.person_id
and pil.ler_id = ler.ler_id
--GSP changes
--and ler.typ_cd not in ('GSP','COMP','SCHEDDU','ABS', 'IREC') /* Bug 3981328 : Added Code IREC */
and l_effective_date between ler.effective_start_date and
ler.effective_end_date
and l_effective_date between per.effective_start_date and
per.effective_end_date
and ((p_ler_id is null and ler.typ_cd not in ('GSP','COMP','SCHEDDU','ABS', 'IREC')) or
(pil.ler_id = p_ler_id and ler.typ_cd not in ('GSP','SCHEDDU','ABS', 'IREC')))
--Bug 4193968: Added the following check to prevent closing of the enrollments if the
-- Effective date is less than the life event occured date, in case the life event type is not 'Open'
and ((ler.typ_cd in('SCHEDDO')) or (l_effective_date >= pil.lf_evt_ocrd_dt ))
-- End Bug 4193968
and (p_person_id is null or pil.person_id = p_person_id)
and (p_lf_evt_ocrd_dt is null
or exists (select null
from ben_pil_elctbl_chc_popl pel,
ben_enrt_perd enp
where pel.per_in_ler_id = pil.per_in_ler_id
and pel.enrt_perd_id = enp.enrt_perd_id
and enp.asnd_lf_evt_dt = l_lf_evt_ocrd_dt
and pel.pil_elctbl_popl_stat_cd = 'STRTD' ))
/* PB : 5422 :
and (p_popl_enrt_typ_cycl_id is null
or exists (select null
from ben_pil_elctbl_chc_popl pel
where pel.per_in_ler_id = pil.per_in_ler_id
and pel.enrt_perd_id = p_popl_enrt_typ_cycl_id
and pel.pil_elctbl_popl_stat_cd = 'STRTD' ))
*/
and (p_location_id is null
or exists(select null
from per_all_assignments_f paf
where paf.person_id = pil.person_id
and paf.assignment_type <> 'C'
and paf.location_id = p_location_id
and paf.primary_flag = 'Y'
and l_effective_date between
paf.effective_start_date and paf.effective_end_date))
and (p_pgm_id is null
or exists(select null
from ben_pil_elctbl_chc_popl pel
where pel.pgm_id = p_pgm_id
and pel.per_in_ler_id = pil.per_in_ler_id
and pel.pil_elctbl_popl_stat_cd = 'STRTD' ))
and (p_pl_nip_id is null
or exists(select null
from ben_pil_elctbl_chc_popl pel
where pl_id = p_pl_nip_id
and pel.per_in_ler_id = pil.per_in_ler_id
and pel.pgm_id is null
and pel.pil_elctbl_popl_stat_cd = 'STRTD' ));
,p_comp_selection_rl => NULL
,p_person_selection_rl => p_person_selection_rl
,p_ler_id => p_ler_id
,p_organization_id => NULL
,p_benfts_grp_id => NULL
,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 => NULL
,p_payroll_id => NULL
,p_audit_log_flag => p_audit_log
,p_debug_messages_flag => 'N'
,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
,p_uneai_effective_date => l_uneai_effective_date
,p_close_uneai_flag => p_close_uneai_flag
--
-- Bug No 4034201
--
,p_ptnl_ler_for_per_stat_cd => p_close_cd
);
delete from ben_batch_ranges
where benefit_action_id = l_benefit_action_id;
if p_person_selection_rl is not null then
--
l_person_ok := ben_batch_utils.person_selection_rule
(p_person_id => l_perid_va(i)
,p_business_group_id => p_business_group_id
,p_person_selection_rule_id => p_person_selection_rl
,p_effective_date => l_effective_date);
,p_person_selection_rule_id => p_person_selection_rl
,p_location_id => p_location_id
,p_ler_id => p_ler_id
,p_mode => p_close_cd -- 1674123
,p_audit_log => p_audit_log);
'No person got selected with above selection criteria.');
,p_person_selected => l_person_actn_cnt
,p_business_group_id => p_business_group_id);
,p_person_selected => l_person_actn_cnt
,p_business_group_id => p_business_group_id);
select null
from ben_per_in_ler pil,
ben_ler_f ler
where pil.lf_evt_ocrd_dt > p_lf_evt_ocrd_dt
and pil.person_id = p_person_id
and ler.ler_id = pil.ler_id
and ler.typ_cd not in ( 'COMP','SCHEDDU', 'ABS', 'GSP', 'IREC') /* Bug 3981328 : Added Codes GSP, IREC, ABS */
and pil.lf_evt_ocrd_dt between
ler.effective_start_date and
ler.effective_end_date
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
select pel.*
from ben_pil_elctbl_chc_popl pel
where pel.per_in_ler_id = p_per_in_ler_id
and pel.business_group_id = p_business_group_id;
select crp.*
from ben_cbr_per_in_ler crp
where crp.per_in_ler_id = p_per_in_ler_id
and crp.business_group_id = p_business_group_id
and crp.init_evt_flag = 'N';
select cqb.*
from ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
where crp.cbr_per_in_ler_id = p_cbr_per_in_ler_id
and crp.cbr_quald_bnf_id = cqb.cbr_quald_bnf_id
and cqb.business_group_id = p_business_group_id
and cqb.business_group_id = crp.business_group_id;
select pil.*
from ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id;
select ELIG_PER_ELCTBL_CHC_ID,
ELCTBL_FLAG
from ben_elig_per_elctbl_chc
where per_in_ler_id =p_per_in_ler_id
and elctbl_flag = 'Y'
and business_group_id = p_business_group_id;
select enb.enrt_bnft_id,
enb.object_version_number,
enb.business_group_id,
epe.prtt_enrt_rslt_id
from ben_elig_per_elctbl_chc epe,
ben_enrt_bnft enb
where epe.per_in_ler_id = cv_per_in_ler_id
and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
and epe.prtt_enrt_rslt_id IS NOT NULL
and enb.prtt_enrt_rslt_id IS NULL
and exists ( select 'x' from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
and pen.bnft_ordr_num = enb.ordr_num
and pen.prtt_enrt_rslt_stat_cd is NULL
and pen.per_in_ler_id = epe.per_in_ler_id ) ;
ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl
(p_validate => p_validate
,p_pil_elctbl_chc_popl_id => l_pel_rec.pil_elctbl_chc_popl_id
,p_pil_elctbl_popl_stat_cd => 'STRTD'
,p_business_group_id => p_business_group_id
,p_object_version_number => l_pel_rec.object_version_number
,p_effective_date => p_effective_date);
ben_cbr_quald_bnf_api.update_cbr_quald_bnf
(p_validate => false
,p_cbr_quald_bnf_id => l_cqb_rec.cbr_quald_bnf_id
,p_cbr_elig_perd_end_dt => l_crp_rec.prvs_elig_perd_end_dt
,p_cbr_inelg_rsn_cd => null
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
);
ben_cbr_per_in_ler_api.delete_cbr_per_in_ler
(p_validate => false
,p_cbr_per_in_ler_id => l_crp_rec.cbr_per_in_ler_id
,p_object_version_number => l_crp_rec.object_version_number
,p_effective_date => p_effective_date
);
electability and no enrollments results then previous LE status will not be updated to 'STRTD' status.*/
if ( ben_back_out_life_event.g_no_reopen_flag = 'N') then
ben_person_life_event_api.update_person_life_event
(p_validate => p_validate
,p_per_in_ler_id => p_per_in_ler_id
,p_per_in_ler_stat_cd => 'STRTD'
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => l_pil_rec.strtd_dt
,p_procd_dt => l_procd_dt
,p_strtd_dt => l_strtd_dt
,p_voidd_dt => l_voidd_dt
);