[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select to_char(WF_ERROR_PROCESSES_S.NEXTVAL)
from SYS.DUAL;
procedure Selector(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out nocopy varchar2) is
--
l_package varchar2(80) := g_package||'.Selector';
wf_core.context('ben_on_line_lf_evt', 'Selector',
itemtype, itemkey, to_char(actid), funcmode);
end Selector;
select count(*)
from ben_ptnl_ler_for_per ptn
where ptn.person_id = to_number(l_person_id)
and ptn.business_group_id+0 = to_number(l_business_group_id)
/* and ptn.lf_evt_ocrd_dt = p_effective_date */
and ptn.ptnl_ler_for_per_stat_cd in ('DTCTD','UNPROCD');
select count(*)
from ben_per_in_ler pil
where pil.person_id = to_number(l_person_id)
and pil.business_group_id = to_number(l_business_group_id)
and pil.per_in_ler_stat_cd = 'STRTD';
select ler_id
from ben_person_actions
where BENEFIT_ACTION_ID = benutils.g_benefit_action_id;
select count(*)
from ben_ptnl_ler_for_per ptn
where ptn.person_id = l_person_id
and ptn.business_group_id+0 = l_business_group_id
/* and ptn.lf_evt_ocrd_dt = p_effective_date */
and ptn.ptnl_ler_for_per_stat_cd in ('DTCTD','UNPROCD');
select ler.name,
ler.ler_id,
pil.lf_evt_ocrd_dt,
pil.per_in_ler_stat_cd,
pel.dflt_enrt_dt
from ben_per_in_ler pil,
ben_ler_f ler,
ben_pil_elctbl_chc_popl pel
where pil.ler_id = ler.ler_id
and pil.per_in_ler_id = pel.per_in_ler_id
and pil.person_id = l_person_id
and pil.business_group_id = l_business_group_id
and ler.business_group_id = l_business_group_id
and pel.business_group_id = l_business_group_id
and pil.per_in_ler_stat_cd = 'STRTD'
/*and l_effective_date
between pil.effective_start_date
and pil.effective_end_date */
and l_effective_date
between ler.effective_start_date
and ler.effective_end_date;
select csr.contact_person_id,
ppf.full_name,
ler.name,
ler.ler_id,
pil.lf_evt_ocrd_dt,
pil.per_in_ler_stat_cd,
pel.dflt_enrt_dt
from per_contact_relationships csr, --?? will be _f
per_people_f ppf,
ben_per_in_ler pil,
ben_ler_f ler,
ben_pil_elctbl_chc_popl pel
where csr.person_id = l_person_id
and ppf.person_id = csr.contact_person_id
and pil.per_in_ler_id = pel.per_in_ler_id
/* and l_effective_date
between ppf.effective_start_date
and ppf.effective_end_date */
and csr.personal_flag = 'Y'
and l_effective_date between nvl(csr.date_start, l_effective_date)
and nvl(csr.date_end, l_effective_date)
and pil.ler_id = ler.ler_id
and pil.person_id = csr.contact_person_id
and pil.business_group_id = l_business_group_id
and ler.business_group_id = l_business_group_id
and pel.business_group_id = l_business_group_id
and pil.per_in_ler_stat_cd = 'STRTD'
/* and l_effective_date
between pil.effective_start_date
and pil.effective_end_date */
and l_effective_date
between ler.effective_start_date
and ler.effective_end_date
order by 1;
p_comp_selection_rule_id => null,
p_person_selection_rule_id => null,
p_ler_id => null,
p_organization_id => null,
p_benfts_grp_id => null,
p_location_id => null,
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_commit_data => 'N',
p_lmt_prpnip_by_org_flag => nvl(fnd_profile.value('BEN_LMT_PRPNIP_BY_ORG_FLAG'), 'N'),
p_lf_evt_ocrd_dt => l_effective_date );
p_comp_selection_rule_id => null,
p_effective_date => l_effective_date,
p_pgm_id => null,
p_business_group_id => l_business_group_id,
p_pl_id => null,
p_oipl_id => null,
p_asnd_lf_evt_dt => null,
-- p_popl_enrt_typ_cycl_id => null,
p_no_programs => 'N',
p_no_plans => 'N',
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_thread_id => 1,
p_mode => 'L');
select count(*)
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pcp,
ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = to_number(l_person_id)
and pil.per_in_ler_id = pcp.per_in_ler_id
and pcp.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
and ler.ler_id = pil.ler_id
and ler.business_group_id+0 = to_number(l_business_group_id)
and to_date(l_effective_date, 'dd/MM/yyyy')
between ler.effective_start_date
and ler.effective_end_date
and epe.business_group_id+0 = to_number(l_business_group_id)
and pil.business_group_id+0 = to_number(l_business_group_id)
and pcp.business_group_id+0 = to_number(l_business_group_id)
and pil.per_in_ler_stat_cd = 'STRTD'
/* and to_date(l_effective_date, 'dd/MM/yyyy')
between pil.effective_start_date
and pil.effective_end_date */
and ( to_date(l_effective_date, 'dd/MM/yyyy')
between pcp.enrt_perd_strt_dt
and pcp.enrt_perd_end_dt or ler.TYP_CD = 'SCHDDU');
select count(*)
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where pil.person_id = to_number(l_person_id)
and epe.business_group_id+0 = to_number(l_business_group_id)
and epe.PER_IN_LER_ID = pil.PER_IN_LER_ID
and pil.business_group_id+0 = to_number(l_business_group_id)
and pil.per_in_ler_stat_cd = 'PROCD';
/* 9999 delete below line */
resultout := 'COMPLETE:Y';
select ler_id
from ben_person_actions
where BENEFIT_ACTION_ID = benutils.g_benefit_action_id;
p_comp_selection_rule_id => null,
p_person_selection_rule_id => null,
p_ler_id => null,
p_organization_id => null,
p_benfts_grp_id => null,
p_location_id => null,
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_commit_data => 'N' ); */
p_comp_selection_rule_id => null,
p_effective_date => p_effective_date,
p_pgm_id => null,
p_business_group_id => p_business_group_id,
p_pl_id => null,
p_oipl_id => null,
p_popl_enrt_typ_cycl_id => null,
p_no_programs => 'N',
p_no_plans => 'N',
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_thread_id => 1,
p_mode => 'L', -- is it a parameter????
-- p_cache_single_object => 'N', -- 99999 Graham why removed
p_prog_count => p_prog_count,
p_plan_count => p_plan_count,
p_oipl_count => p_oipl_count,
p_plan_nip_count => p_plan_nip_count,
p_oipl_nip_count => p_oipl_nip_count); */
select ler_id
from ben_person_actions
where BENEFIT_ACTION_ID = benutils.g_benefit_action_id;
select pgm_id
from BEN_PIL_ELCTBL_CHC_POPL
where business_group_id = p_business_group_id
and PER_IN_LER_ID = l_rec.per_in_ler_id
and pgm_id is not null;
select pcr.contact_person_id ,
pcr.contact_relationship_id
from per_contact_relationships pcr,
per_all_people_f per
where pcr.person_id = p_person_id
and pcr.contact_person_id = per.person_id
and pcr.personal_flag = 'Y'
and p_effective_date between per.effective_start_date and per.effective_end_date
and p_effective_date between
nvl(pcr.date_start,p_effective_date) and
nvl(pcr.date_end,p_effective_date)
and p_effective_date > l_rec.lf_evt_ocrd_dt
and nvl(pcr.date_start,p_effective_date) > l_rec.lf_evt_ocrd_dt ;
p_comp_selection_rule_id => null,
p_person_selection_rule_id => null,
p_ler_id => null,
p_organization_id => null,
p_benfts_grp_id => null,
p_location_id => null,
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_commit_data => 'N',
p_lmt_prpnip_by_org_flag => nvl(fnd_profile.value('BEN_LMT_PRPNIP_BY_ORG_FLAG'), 'N'),
p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt ); -- GLOBAL CWB l_effective_date );
p_comp_selection_rule_id => null,
p_effective_date => p_effective_date,
p_pgm_id => p_pgm_id,
p_business_group_id => p_business_group_id,
p_pl_id => p_pl_id,
p_oipl_id => null,
-- p_popl_enrt_typ_cycl_id => null,
p_asnd_lf_evt_dt => null,
p_no_programs => 'N',
p_no_plans => 'N',
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_thread_id => 1,
p_mode => p_mode);
select ler_id
from ben_person_actions
where BENEFIT_ACTION_ID = benutils.g_benefit_action_id;
p_comp_selection_rule_id => null,
p_person_selection_rule_id => null,
p_ler_id => null,
p_organization_id => null,
p_benfts_grp_id => null,
p_location_id => null,
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_commit_data => 'N',
p_lmt_prpnip_by_org_flag => nvl(fnd_profile.value('BEN_LMT_PRPNIP_BY_ORG_FLAG'), 'N'),
p_lf_evt_ocrd_dt => l_lf_evt_ocrd_dt );
select ben.object_version_number, ben.person_action_id
from ben_person_actions ben
where ben.benefit_action_id = g_benefit_action_id
and ben.person_id = p_person_id
and ben.action_status_cd <> 'P';
select ler_id
from ben_person_actions
where BENEFIT_ACTION_ID = benutils.g_benefit_action_id;
p_comp_selection_rule_id => null,
p_person_selection_rule_id => null,
p_ler_id => null,
p_organization_id => null,
p_benfts_grp_id => null,
p_location_id => null,
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_commit_data => 'N',
p_lmt_prpnip_by_org_flag => nvl(fnd_profile.value('BEN_LMT_PRPNIP_BY_ORG_FLAG'), 'N'),
p_lf_evt_ocrd_dt => l_effective_date );
select *
from ben_pop_up_messages pop
where pop.function_name = p_function_name
and pop.event_name = p_event_name
and nvl(pop.block_name, '***') = nvl(p_block_name, '***')
and nvl(pop.field_name, '***') = nvl(p_field_name, '***')
and pop.business_group_id+0 = p_business_group_id
and p_effective_date
between nvl(pop.start_date, p_effective_date)
and nvl(pop.end_date, p_effective_date);
select assignment_id,organization_id
from per_assignments_f paf
where paf.person_id = p_person_id
and paf.assignment_type <> 'C'
and paf.primary_flag = 'Y'
and paf.business_group_id + 0= p_business_group_id
and p_effective_date between
paf.effective_start_date and paf.effective_end_date;
select region_2
from hr_locations_all loc,per_all_assignments_f asg
where loc.location_id = asg.location_id
and asg.person_id = p_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and p_effective_date between
asg.effective_start_date and asg.effective_end_date
and asg.business_group_id+0=p_business_group_id;
l_mess_type varchar2(10); -- 9999 delete it
select ler.typ_cd
from ben_ptnl_ler_for_per pel,
ben_ler_f ler
where p_effective_date between ler.effective_start_date
and ler.effective_end_date
and ler.typ_cd in ( 'SCHEDDO','SCHEDDA')
and pel.ptnl_ler_for_per_stat_cd = 'UNPROCD'
and pel.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and p_person_id = pel.person_id
and pel.ler_id =ler.ler_id ;
select null
from ben_pil_elctbl_chc_popl pel
where per_in_ler_id = p_per_in_ler_id
for update nowait;