[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pln.nip_acty_ref_perd_cd
,pln.pl_id
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.pgm_id,
pen.per_in_ler_id,
pen.prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f pen
where pen.person_id = p_person_id
and pen.pl_id = p_pl_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.business_group_id = p_business_group_id
and p_effective_date between
pen.effective_start_date and pen.effective_end_date;
select abr.acty_base_rt_id,
abr.rt_typ_cd,
abr.tx_typ_cd,
abr.acty_typ_cd,
abr.rt_mlt_cd,
abr.bnft_rt_typ_cd,
abr.dsply_on_enrt_flag,
abr.comp_lvl_fctr_id,
abr.actl_prem_id,
abr.input_value_id,
abr.element_type_id
from ben_acty_base_rt_f abr
where abr.pl_id = p_pl_id
and abr.acty_typ_cd = 'PRFRFS'
and abr.acty_base_rt_stat_cd = 'A'
and p_effective_date between
abr.effective_start_date and
abr.effective_end_date;
select pgm.acty_ref_perd_cd
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 prv.prtt_rt_val_id,
prv.object_version_number,
ecr.enrt_rt_id
from ben_acty_base_rt_f abr,
ben_prtt_rt_val prv,
ben_enrt_rt ecr
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.prtt_rt_val_stat_cd is null
and prv.acty_base_rt_id = p_acty_base_rt_id
and ecr.prtt_rt_val_id = prv.prtt_rt_val_id
and prv.rt_strt_dt between p_start_date
and p_end_date
and prv.acty_base_rt_id = abr.acty_base_rt_id
and abr.acty_typ_cd = 'PRFRFS'
and p_start_date between
abr.effective_start_date and
abr.effective_end_date;
ben_prtt_rt_val_api.delete_prtt_rt_val
(p_validate => false
,p_prtt_rt_val_id => l_prv_rec.prtt_rt_val_id
,p_enrt_rt_id => l_prv_rec.enrt_rt_id
,p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_object_version_number => l_prv_rec.object_version_number
,p_effective_date => p_end_date
);
,p_person_selection_rule_id in number default null) is
--
l_package varchar2(80) := g_package||'.process_forfeitures';
select pln.name, pln.FRFS_DISTR_MTHD_CD,
pln.FRFS_DISTR_MTHD_RL,
pln.FRFS_CNTR_DET_CD,
pln.FRFS_DISTR_DET_CD,
pln.COST_ALLOC_KEYFLEX_1_ID,
pln.COST_ALLOC_KEYFLEX_2_ID,
pln.POST_TO_GL_FLAG,
pln.FRFS_VAL_DET_CD,
pln.FRFS_MX_CRYFWD_VAL,
pln.FRFS_PORTION_DET_CD,
pln.BNDRY_PERD_CD,
pyp.Acpt_clm_rqsts_thru_dt,
yp.start_date,
yp.end_date
from ben_pl_f pln,
ben_popl_yr_perd pyp,
ben_yr_perd yp
where pln.pl_id = p_pl_id
and pln.pl_id = pyp.pl_id
and pln.pl_stat_cd = 'A'
and pyp.yr_perd_id = yp.yr_perd_id
and p_effective_date BETWEEN yp.start_date AND yp.end_date
and pyp.business_group_id = p_business_group_id
and yp.business_group_id = p_business_group_id
and p_effective_date between
pln.effective_start_date and pln.effective_end_date;
select /* abr.*,*/ clf.* -- 9999 Add the only colums required.
from ben_acty_base_rt_f abr,
ben_comp_lvl_fctr clf
where abr.acty_typ_cd like p_acty_typ_cd || '%'
-- p_acty_typ_cd is like PRD/PRC
and abr.pl_id = p_pl_id
and abr.acty_base_rt_stat_cd = 'A'
and abr.ttl_comp_lvl_fctr_id = clf.comp_lvl_fctr_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select /* abr.*,*/ clf.* -- 9999 Add the only colums required.
from ben_acty_base_rt_f abr,
ben_comp_lvl_fctr clf
where abr.acty_typ_cd not like 'PRD%'
and abr.pl_id = p_pl_id
and abr.acty_base_rt_stat_cd = 'A'
and abr.ttl_comp_lvl_fctr_id = clf.comp_lvl_fctr_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select unique pen.person_id
/* pen.prtt_enrt_rslt_id, pen.person_id, pen.pl_id, pen.oipl_id,
pen.pgm_id, pen.pl_typ_id
*/
from ben_prtt_enrt_rslt_f pen,
per_all_people_f per
where per.person_id = pen.person_id
and per.business_group_id = pen.business_group_id
and p_effective_date between per.effective_start_date
and per.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null
and pen.sspndd_flag = 'N'
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and ( (pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_strt_dt < p_end_date
)
or(pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date between p_start_date and p_end_date
and pen.enrt_cvg_strt_dt < p_end_date
)
or(pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date between p_start_date and p_end_date
and pen.enrt_cvg_strt_dt between p_start_date and p_end_date
)
)
and pen.pl_id = p_pl_id
and pen.business_group_id = p_business_group_id
and (pen.person_id = p_person_id or p_person_id is null)
and (per.person_type_id = p_person_type_id or p_person_type_id is null) ;
select sum(nvl(pry.aprvd_fr_pymt_amt,0))
from ben_prtt_reimbmt_rqst_f prc,
ben_prtt_rmt_aprvd_fr_pymt_f pry
where prc.pl_id = p_pl_id
and prc.prtt_reimbmt_rqst_stat_cd in ( 'APPRVD','PDINFL','PRTLYPD')
and nvl(pry.pymt_stat_cd,' ') <> ('RMBPNDNG')
and p_effective_date between prc.effective_start_date
and prc.effective_end_date
and prc.submitter_person_id = p_person_id
and prc.business_group_id = p_business_group_id
and prc.incrd_from_dt between p_start_date and p_end_date
-- is it clms thru dt
and prc.incrd_to_dt between p_start_date and p_end_date
-- 9999 what is p_end_date
and prc.prtt_reimbmt_rqst_id = pry.prtt_reimbmt_rqst_id
and p_effective_date between pry.effective_start_date
and pry.effective_end_date;
select bnb.val
from ben_per_bnfts_bal_f bnb
where bnb.bnfts_bal_id = p_bnfts_bal_id
and bnb.person_id = p_person_id
and bnb.business_group_id = p_business_group_id
and p_effective_date
between bnb.effective_start_date
and bnb.effective_end_date;
select paf.assignment_id
from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.business_group_id = p_business_group_id
and paf.primary_flag = 'Y'
and paf.assignment_type = p_assignment_type
and p_effective_date
between paf.effective_start_date
and paf.effective_end_date;
delete from ben_pl_frfs_val_f
where pl_id = p_pl_id
and business_group_id = p_business_group_id
and start_date = l_start_date
and end_date = l_end_date
;
insert into BEN_PL_FRFS_VAL_F
(PL_FRFS_VAL_ID
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,BUSINESS_GROUP_ID
,PL_ID
,start_date
,end_date
,ttl_cntr_val
,ttl_distr_val
,ttl_frfd_val
,COST_ALLOC_KEYFLEX_1_ID
,COST_ALLOC_KEYFLEX_2_ID
,OBJECT_VERSION_NUMBER
) values
(
ben_pl_frfs_val_f_s.nextval,
p_effective_date,
hr_api.g_eot,
p_business_group_id,
p_pl_id,
l_start_date,
l_end_date,
l_tot_pl_cntr_val,
l_tot_pl_distr_val,
l_total_frfd_val,
l_pl_subj_frfs.COST_ALLOC_KEYFLEX_1_ID,
l_pl_subj_frfs.COST_ALLOC_KEYFLEX_2_ID,
1
);
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
;
ben_batch_utils.ini; -- deletes g_cache_person, g_cache_comp, g_pgm_tbl etc.,
,p_person_selection_rule_id => null -- 9999 what to send l_parm.person_selection_rule_id
,p_comp_selection_rule_id => l_parm.comp_selection_rl
,p_pgm_id => l_parm.pgm_id
,p_pl_typ_id => l_parm.pl_typ_id
,p_pl_id => l_parm.pl_id
,p_organization_id => l_parm.organization_id -- 9999 l_parm needs to be changed.
-- If it comes from l_parm then delete it as parameter.
,p_ler_id => null
,p_benfts_grp_id => null
,p_location_id => null
,p_legal_entity_id => null
,p_payroll_id => null
);
update ben_batch_ranges ran set ran.range_status_cd = 'P'
where ran.range_id = l_range_id;
g_cache_person_process.delete;
,p_person_selection_rule_id => null); -- l_parm.person_selection_rule_id);
update ben_person_actions
set action_status_cd = 'P'
where person_id = g_cache_person_process(l_cnt).person_id
and benefit_action_id = p_benefit_action_id;
update ben_person_actions
set action_status_cd = 'E'
where person_id = g_cache_person_process(l_cnt).person_id
and benefit_action_id = p_benefit_action_id;
,p_comp_selection_rule_id in number default null
,p_person_selection_rule_id in number default null -- For Future Enhancement.
,p_debug_messages in varchar2 default 'N'
,p_audit_log_flag in varchar2 default 'N'
,p_commit_data_flag in varchar2 default 'Y'
,p_threads in number
,p_chunk_size in number
,p_max_errors in number
,p_restart in boolean default FALSE ) is
--
-- Cursors declaration.
--
-- Plans subjected to forfeiture to be processed:
cursor c_pl_subj_frfs (p_effective_date date) is
select pln.*
from ben_pl_f pln
where pln.business_group_id = p_business_group_id
and pln.frfs_aply_flag = 'Y'
and pln.pl_stat_cd = 'A'
and pln.pl_id = NVL(p_pl_id, pln.pl_id)
and pln.pl_typ_id = NVL(p_pl_typ_id, pln.pl_typ_id)
and p_effective_date between
pln.effective_start_date and pln.effective_end_date
and (p_pgm_id is null
OR EXISTS
(SELECT NULL
FROM ben_plip_f cpp
WHERE cpp.pl_id = pln.pl_id
-- AND cpp.pgm_id = NVL(p_pgm_id, cpp.pgm_id)
AND cpp.pgm_id = p_pgm_id
AND cpp.business_group_id = pln.business_group_id
AND cpp.plip_stat_cd = 'A'
AND p_effective_date BETWEEN cpp.effective_start_date
AND cpp.effective_end_date));
l_actn := 'Calling ben_batch_utils.comp_obj_selection_rule...';
or p_comp_selection_rule_id is not null then
rl_ret := 'Y';
elsif rl_ret = 'Y' and p_comp_selection_rule_id is not null then
l_actn := 'found a comp object rule...';
(p_formula_id => p_comp_selection_rule_id
,p_effective_date => l_effective_date
,p_pgm_id => l_pgm_id
,p_pl_id => l_pl_id
,p_pl_typ_id => l_pl_typ_id
,p_opt_id => l_opt_id
,p_ler_id => null
,p_business_group_id => p_business_group_id);
hr_utility.set_location('not skip...Inserting Ben_person_actions',28);
l_actn := 'Inserting Ben_person_actions...';
select ben_person_actions_s.nextval
into l_person_action_id
from sys.dual;
insert into ben_person_actions
(person_action_id,
person_id,
ler_id,
benefit_action_id,
action_status_cd,
object_version_number,
chunk_number,
non_person_cd)
values
(l_person_action_id,
l_pl_subj_frfs.pl_id,
0,
p_benefit_action_id,
'U',
1,
l_chunk_num,
'FRFS');
l_actn := 'Inserting Ben_batch_ranges.......';
hr_utility.set_location('Inserting Ben_batch_ranges',32);
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,
p_benefit_action_id,
'U',
l_start_person_action_id,
l_end_person_action_id,
1);
l_actn := 'Inserting Final Ben_batch_ranges...';
hr_utility.set_location('Inserting Final Ben_batch_ranges',38);
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,
p_benefit_action_id,
'U',
l_start_person_action_id,
l_end_person_action_id,
1);
,p_comp_selection_rule_id => p_comp_selection_rule_id
,p_pgm_id => p_pgm_id
,p_pl_typ_id => p_pl_typ_id
,p_pl_id => p_pl_id
,p_person_id => p_person_id
,p_person_selection_rule_id => p_person_selection_rule_id
,p_person_type_id => p_person_type_id
,p_ler_id => null
,p_organization_id => p_organization_id
,p_benfts_grp_id => null
,p_location_id => null
,p_legal_entity_id => null
,p_payroll_id => null
);
'<< No Plans For Forfeiture were selected with above selection criteria >>' );
,p_person_selected => l_person_cnt
,p_business_group_id => p_business_group_id
,p_non_person_cd => 'FRFS'); -- used in benrptut
,p_person_selected => l_person_cnt
,p_business_group_id => p_business_group_id
) ;
,p_comp_selection_rule_id in number default null
,p_person_selection_rule_id in number default null -- For Future Enhancement.
,p_debug_messages in varchar2 default 'N'
,p_audit_log_flag in varchar2 default 'N'
,p_commit_data_flag in varchar2 default 'Y'
) is
--
-- local variable declaration.
--
l_request_id number;
,p_comp_selection_rl => p_comp_selection_rule_id
,p_person_selection_rl => p_person_selection_rule_id
,p_ler_id => null
,p_organization_id => p_organization_id
,p_benfts_grp_id => null
,p_location_id => null
,p_pstl_zip_rng_id => NULL
,p_rptg_grp_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_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
);
l_actn := 'Delete rows from ben_batch_ranges..';
hr_utility.set_location('Delete rows from ben_batch_ranges',16);
Delete from ben_batch_ranges
Where benefit_action_id = l_benefit_action_id;
if p_person_id is null and p_person_selection_rule_id is null then
--
-- Only process Plans subject to forfeiture if no person criteria was
-- selected
--
ben_forfeiture_concurrent.process_by_plan(
errbuf => l_errbuf
,retcode => l_retcode
,p_benefit_action_id => l_benefit_action_id
,p_effective_date => p_effective_date -- l_effective_date_char
,p_validate => p_validate
,p_business_group_id => p_business_group_id
,p_organization_id => p_organization_id
,p_frfs_perd_det_cd => p_frfs_perd_det_cd
,p_person_id => p_person_id
,p_person_type_id => p_person_type_id
,p_pgm_id => p_pgm_id
,p_pl_typ_id => p_pl_typ_id
,p_pl_id => p_pl_id
,p_comp_selection_rule_id => p_comp_selection_rule_id
,p_person_selection_rule_id => p_person_selection_rule_id
,p_debug_messages => p_debug_messages
,p_audit_log_flag => p_audit_log_flag
,p_commit_data_flag => p_commit_data_flag
,p_threads => l_threads
,p_chunk_size => l_chunk_size
,p_max_errors => g_max_errors_allowed
,p_restart => l_restart);
,p_person_selected => l_person_cnt
,p_business_group_id => p_business_group_id);
,p_person_selected => l_person_cnt
,p_business_group_id => p_business_group_id
) ;