[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_detail := 'Participant action closed(Enrollment deleted)';
l_detail := 'Participant action closed(No enrollment deleted)';
l_actn := 'Calling delete_prtt_enrt_actn(' || to_char(p_prtt_enrt_actn_id)
|| ')...' ;
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_unsuspend_enrt_flag => p_unsuspend_enrt_flag
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
);
select ecd.prtt_enrt_rslt_id,
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 = p_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');
select bnf.prtt_enrt_rslt_id, bnf.bnf_person_id
from ben_pl_bnf_f bnf,
ben_per_in_ler pil
where bnf.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and bnf.business_group_id= p_business_group_id
and p_effective_date between bnf.effective_start_date
and bnf.effective_end_date
and pil.per_in_ler_id=bnf.per_in_ler_id
and pil.business_group_id=bnf.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
Select a.prtt_enrt_rslt_id,
b.actn_typ_id,
a.enrt_ctfn_recd_dt
from ben_prtt_enrt_ctfn_prvdd_f a
,ben_prtt_enrt_actn_f b
,ben_per_in_ler pil
where a.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and a.business_group_id= p_business_group_Id
and p_effective_date between
a.effective_start_date and a.effective_start_date
and b.prtt_enrt_actn_id = a.prtt_enrt_actn_id
and p_effective_date between
b.effective_start_date and b.effective_start_date
and pil.per_in_ler_id=b.per_in_ler_id
and pil.business_group_id=b.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select pea.prtt_enrt_actn_id
,pea.cmpltd_dt
,pea.due_dt
,pea.rqd_flag
,pea.prtt_enrt_rslt_id
,pea.actn_typ_id
,'N' actn_cd
,pea.effective_start_date
,pea.effective_end_date
from ben_prtt_enrt_actn_f pea,
ben_per_in_ler pil
where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pea.business_group_id = p_business_group_id
/* Bug 10145083: Check for c_effective_date and not p_effective_date*/
and c_effective_date between pea.effective_start_date
and pea.effective_end_date
and pil.per_in_ler_id=pea.per_in_ler_id
and pil.business_group_id=pea.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
order by pea.prtt_enrt_rslt_id, pea.prtt_enrt_actn_id
;
/*Bug 9735870: When comparing the action items after the action items are updated,
action items will be end dated 1 day before the p_effective_date. Checking the
actions as of p_effective_date will not fetch any records. Changed the cursor
condition to check the action item as of p_effective_date -1 */
For rec2 in c1(p_effective_date-1) loop
l_fnd := FALSE;
select pen.prtt_enrt_rslt_id
,pen.effective_start_date
,pen.effective_end_date
,pen.business_group_id
,pen.person_id
,pen.rplcs_sspndd_rslt_id
,pen.sspndd_flag
,pen.object_version_number
,pen.per_in_ler_id
,'N' skip
from ben_prtt_enrt_rslt_f pen
where pen.person_id = p_person_id
and pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and exists (select null
from ben_prtt_enrt_actn_f pea
where pen.prtt_enrt_rslt_id = pea.prtt_enrt_rslt_id
and pea.cmpltd_dt is null
and p_effective_date between pea.effective_start_date
and pea.effective_end_date)
and nvl(pen.enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
and p_effective_date between pen.effective_start_date
and pen.effective_end_date -1
and ( pen.effective_end_date = hr_api.g_eot or --Bug 4398840
not exists (select 'x' from ben_prtt_enrt_rslt_f pen1 -- to exclude the ended result
where pen1.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen1.effective_end_date = hr_api.g_eot
and pen1.enrt_cvg_thru_dt <> hr_api.g_eot
)
)
and pen.effective_end_date >= pen.enrt_cvg_strt_dt;
select b.prtt_enrt_actn_id
,b.due_dt
,b.cmpltd_dt
,b.rqd_flag
,b.prtt_enrt_rslt_id
,b.actn_typ_id
,b.effective_start_date
,b.effective_end_date
,b.object_version_number
from ben_prtt_enrt_actn_f b,
ben_per_in_ler pil
where b.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and b.business_group_id = p_business_group_id
and b.cmpltd_dt is NULL
and p_effective_date between b.effective_start_date
and b.effective_end_date
and pil.per_in_ler_id=b.per_in_ler_id
and pil.business_group_id=b.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
AND EXISTS (SELECT NULL ------Bug 8620516
FROM ben_prtt_enrt_rslt_f pen
WHERE pen.prtt_enrt_rslt_id = b.prtt_enrt_rslt_id
AND pen.per_in_ler_id = pil.per_in_ler_id
AND pen.business_group_id = pil.business_group_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL)
;
select cvrd_dpnt_ctfn_prvdd_id id,
object_version_number,
effective_start_date,
effective_end_date,
hrl.MEANING,
'Dependent certification: ' type
from ben_cvrd_dpnt_ctfn_prvdd_f dpf,
hr_lookups hrl
where dpf.prtt_enrt_actn_id = c_prtt_enrt_actn_id
and dpf.business_group_id = p_business_group_id
and p_effective_date between dpf.effective_start_date
and dpf.effective_end_date
and hrl.LOOKUP_TYPE = 'BEN_DPNT_CVG_CTFN_TYP'
and hrl.LOOKUP_CODE = dpf.DPNT_DSGN_CTFN_TYP_CD
union
select prtt_enrt_ctfn_prvdd_id id,
object_version_number,
effective_start_date,
effective_end_date,
hrl.MEANING,
'Participant certification: ' type
from ben_prtt_enrt_ctfn_prvdd_f pcf,
hr_lookups hrl
where pcf.prtt_enrt_actn_id = c_prtt_enrt_actn_id
and pcf.business_group_id = p_business_group_id
and p_effective_date between pcf.effective_start_date
and pcf.effective_end_date
and hrl.LOOKUP_TYPE = 'BEN_ENRT_CTFN_TYP'
and hrl.LOOKUP_CODE = pcf.ENRT_CTFN_TYP_CD;
select pgm_id,
pl_id,
oipl_id
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date
and prtt_enrt_rslt_stat_cd is null;
select pgm.name||'('||to_char(pgm.pgm_id)||')'||':' name1
from ben_pgm_f pgm
where pgm.pgm_id = c_pgm_id
and p_effective_date between pgm.effective_start_date
and pgm.effective_end_date;
select pl.name||'('||to_char(pl.pl_id)||')' name1
from ben_pl_f pl
where pl.pl_id = c_pl_id
and p_effective_date between pl.effective_start_date
and pl.effective_end_date;
select ':'||opt.name||'('||to_char(opt.opt_id )||')' name1
from ben_oipl_f oipl,
ben_opt_f opt
where oipl.oipl_id = c_oipl_id
and p_effective_date between oipl.effective_start_date
and oipl.effective_end_date
and opt.opt_id = oipl.opt_id
and p_effective_date between opt.effective_start_date
and opt.effective_end_date;
select min(nvl(b.due_dt,hr_api.g_eot)) due_dt
from ben_prtt_enrt_actn_f b,
ben_per_in_ler pil
where b.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and b.business_group_id = p_business_group_id
and b.cmpltd_dt is NULL
and b.rqd_flag = 'Y'
and p_effective_date between b.effective_start_date
and b.effective_end_date
and pil.per_in_ler_id=b.per_in_ler_id
and pil.business_group_id=b.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
g_actn_tbl.delete;
g_cert_tbl.delete;
g_bnf_tbl.delete;
g_dpnt_tbl.delete;
l_actn := 'Calling ben_prtt_enrt_result_api.delete_enrollment...' ;
ben_prtt_enrt_result_api.delete_enrollment
(p_prtt_enrt_rslt_id => l_process_rec(i).prtt_enrt_rslt_id
,p_per_in_ler_id => l_process_rec(i).per_in_ler_id -- Bug 2386000
,p_business_group_id => p_business_group_id
,p_effective_start_date => l_process_rec(i).effective_start_date
,p_effective_end_date => l_process_rec(i).effective_end_date
,p_object_version_number => l_process_rec(i).object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_delete
,p_multi_row_validate => TRUE
,p_source => 'benuneai');
/*Bug 10145083: If the Suspended result is deleted, set the action code to 'D'*/
l_actn_cd := 'D';
,p_datetrack_mode => hr_api.g_delete
,p_object_version_number => l_reca.object_version_number
,p_prtt_enrt_rslt_id => l_dump_number
,p_rslt_object_version_number => l_dump_number
,p_unsuspend_enrt_flag => 'N'
,p_effective_start_date => l_recA.effective_start_date
,p_effective_end_date => l_recA.effective_end_date
,p_batch_flag => p_batch_flag);
,p_datetrack_mode => hr_api.g_delete
,p_object_version_number => l_reca.object_version_number
,p_prtt_enrt_rslt_id => l_dump_number
,p_rslt_object_version_number => l_dump_number
,p_unsuspend_enrt_flag => 'N'
,p_effective_start_date => l_recA.effective_start_date
,p_effective_end_date => l_recA.effective_end_date
,p_batch_flag => p_batch_flag
);
') deleted');
l_actn := 'Calling ben_person_actions_api.update_person_actions...';
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_object_version_number
,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_object_version_number
,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;
select 'Y'
from ben_benefit_actions bft
where bft.benefit_action_id = p_benefit_action_id
and bft.request_id = fnd_global.conc_request_id;
,p_person_selection_rule_id => l_parm.person_selection_rl
,p_location_id => l_parm.location_id
,p_audit_log => p_audit_log);
update ben_batch_ranges ran
set ran.range_status_cd = 'P'
where ran.range_id = l_range_id;
g_cache_per_proc.delete;
,p_person_selected => g_person_actn_cnt
,p_business_group_id => p_business_group_id);
,p_person_selected => g_person_actn_cnt
,p_business_group_id => p_business_group_id);
select to_char(process_date,'YYYY/MM/DD HH24:MI:SS') process_date
,business_group_id
,pgm_id
,pl_id
,location_id
,ler_id
,popl_enrt_typ_cycl_id
,person_id
,person_selection_rl
,validate_flag
,debug_messages_flag
,audit_log_flag
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'
)
is
--
l_effective_date date;
select distinct pen.person_id
from ben_prtt_enrt_rslt_f pen
,ben_per_in_ler pil
,ben_prtt_enrt_actn_f actn
where pen.business_group_id = p_business_group_id
and pen.business_group_id = actn.business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.prtt_enrt_rslt_id = actn.prtt_enrt_rslt_id
and nvl(pen.effective_end_date,hr_api.g_eot) = hr_api.g_eot
and nvl(actn.effective_end_date,hr_api.g_eot) = hr_api.g_eot
and actn.cmpltd_dt is null
and (p_person_id is null or
pen.person_id = p_person_id)
and (p_location_id is null or
exists ( select null
from per_assignments_f asg
where asg.person_id = pen.person_id
and asg.assignment_type <> 'C'
and asg.location_id = p_location_id
and asg.business_group_id = p_business_group_id
and l_effective_date between asg.effective_start_date
and asg.effective_end_date))
and (p_pgm_id is null or
pen.pgm_id = p_pgm_id)
and (p_pl_nip_id is null or
(pen.pl_id = p_pl_nip_id and pen.pgm_id is null))
and pil.per_in_ler_id=actn.per_in_ler_id
and pil.business_group_id=actn.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
,p_comp_selection_rl => NULL
,p_person_selection_rl => p_person_selection_rl
,p_ler_id => NULL
,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_debug_messages_flag => p_debug_messages
,p_audit_log_flag => p_audit_log
,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_rl is not null
then
--
l_person_ok := ben_batch_utils.person_selection_rule
(p_person_id => l_rec.person_id
,p_business_group_id => p_business_group_id
,p_person_selection_rule_id => p_person_selection_rl
,p_effective_date => l_effective_date);
hr_utility.set_location('No people selected', 10);
,p_person_selection_rule_id => p_person_selection_rl
,p_location_id => p_location_id
);
'No person got selected with above selection criteria.');