[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select max(enp.data_freeze_date) data_freeze_date
from ben_popl_enrt_typ_cycl_f petc
,ben_enrt_perd enp
,ben_yr_perd yr
,ben_wthn_yr_perd wyr
where petc.pl_id = v_pl_id
and v_search_date between petc.effective_start_date and petc.effective_end_date
and petc.popl_enrt_typ_cycl_id = enp.popl_enrt_typ_cycl_id
and yr.yr_perd_id = enp.yr_perd_id
and enp.wthn_yr_perd_id = wyr.wthn_yr_perd_id (+);
, p_program_update_date => SYSDATE
, p_bft_attribute30 => 'N'
);
insert into ben_transaction( transaction_id
,transaction_type
,status
,attribute1
,attribute2
--,attribute3
)
values( BEN_TRANSACTION_S.NEXTVAL
,'EMPENROLL'||l_benefit_action_id
,'E'
--,FND_MSG_PUB.GET_DETAIL(p_msg_index => l_index)
,replace(l_err,chr(0),' ')
,p_person_id
--l_loc
);
select attribute1
from ben_benefit_actions actn, ben_transaction, fnd_concurrent_requests req
where req.request_id = v_request_id
and req.request_id = actn.request_id
and transaction_type = 'EMPENROLL'||benefit_action_id
and attribute2 = req.ARGUMENT3;
select ppt.system_person_type
from per_person_type_usages_f ptu
,per_person_types ppt
where ptu.person_id = p_person_id
and p_effective_date between ptu.effective_start_date
and ptu.effective_end_date
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('EMP','CWK');
select date_start
,actual_termination_date
,final_process_date
,projected_termination_date
from per_all_assignments_f asg
,per_periods_of_service pds
where asg.person_id = p_person_id
and p_effective_date between
asg.effective_start_date and asg.effective_end_date
and asg.period_of_service_id = pds.period_of_service_id;
select date_start
,actual_termination_date
,final_process_date
,projected_termination_date
from per_periods_of_placement
where person_id = p_person_id
and p_effective_date between date_start
and nvl(actual_termination_date,to_date('4712/12/31','yyyy/mm/dd'));
select pil.per_in_ler_id
,pil.per_in_ler_stat_cd
,per.post_process_stat_cd
,rates.group_per_in_ler_id rates_id
from ben_per_in_ler pil
,ben_cwb_person_info per
,ben_cwb_person_rates rates
where pil.person_id = p_person_id
and pil.group_pl_id = p_group_pl_id
and pil.lf_evt_ocrd_dt = p_lf_evt_dt
and pil.per_in_ler_stat_cd <> 'BCKDT'
and pil.per_in_ler_id = per.group_per_in_ler_id
and pil.per_in_ler_id = rates.group_per_in_ler_id (+)
and rates.oipl_id (+) = -1;
select decode(asg.payroll_id,null,'Y','N') no_payroll
,decode(ppp.pay_proposal_id,null,'Y','N') no_salary
,decode(asg.supervisor_id, null, 'Y', 'N') no_supervisor
,decode(asg.position_id, null, 'Y', 'N') no_position
,decode(asg.pay_basis_id, null, 'Y', 'N') no_paybasis
,decode(curr_abs.absence_attendance_id,null,'N','Y') curr_abs
,decode(future_abs.absence_attendance_id,null,'N','Y') future_abs
from per_all_assignments_f asg
,per_pay_proposals ppp
,per_absence_attendances curr_abs
,per_absence_attendances future_abs
where asg.person_id = p_person_id
and asg.primary_flag = 'Y'
and asg.assignment_type in ('E','C')
and p_eff_date between asg.effective_start_date and asg.effective_end_date
and asg.assignment_id = ppp.assignment_id (+)
and ppp.proposed_salary_n (+) > 0
and ppp.approved (+) = 'Y'
and asg.person_id = curr_abs.person_id (+)
and p_eff_date between curr_abs.date_start(+) and curr_abs.date_end (+)
and asg.person_id = future_abs.person_id (+)
and p_eff_date < future_abs.date_start (+);
select HRCHY_TO_USE_CD
from
BEN_ENRT_PERD enrt
, ben_cwb_pl_dsgn dsgn
where dsgn.pl_id = p_group_pl_id
and enrt.enrt_perd_id = dsgn.enrt_perd_id
and dsgn.lf_evt_ocrd_dt= p_lf_evt_dt;
select meaning into p_method_display
from hr_lookups
where lookup_type = 'BEN_CWB_ADMIN_PP_TYPE'
and lookup_code = p_method;
select meaning into p_term_date
from hr_lookups
where lookup_type = 'BEN_CWB_MISC_TEXT'
and lookup_code = 'ONGOING';
select pil.per_in_ler_id
,grppln.name group_pl_name
,decode(rates.group_per_in_ler_id, null, '-1',pln.name) plan_name
,pln.prsrv_bdgt_cd prsrv_bdgt_cd
,decode(grppln.wthn_yr_start_dt,null,null,grppln.wthn_yr_start_dt
||' - '||grppln.wthn_yr_end_dt) period
,elig_stat.meaning eligible_status
,event_stat.meaning event_status
,per.post_process_stat_cd pp_stat_cd
,pln.pl_id
into l_group_per_in_ler_id
,l_group_pl_name
,l_plan_name
,l_prsrv_bdgt_cd
,l_period
,l_elig_status
,l_event_status
,l_pp_stat_cd
,l_pl_id
from ben_per_in_ler pil
,ben_cwb_pl_dsgn grppln
,ben_cwb_pl_dsgn pln
,ben_cwb_person_rates rates
,ben_cwb_person_info per
,hr_lookups elig_stat
,hr_lookups event_stat
where pil.person_id = p_person_id
and pil.group_pl_id = p_group_pl_id
and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and pil.per_in_ler_stat_cd ='STRTD'
and pil.per_in_ler_id = per.group_per_in_ler_id
and event_stat.lookup_type (+) = 'BEN_PER_IN_LER_STAT'
and event_stat.lookup_code (+) = pil.per_in_ler_stat_cd
and event_stat.enabled_flag (+) = 'Y'
and pil.group_pl_id = grppln.pl_id
and grppln.oipl_id = -1
and pil.lf_evt_ocrd_dt = grppln.lf_evt_ocrd_dt
and pil.group_pl_id = pln.group_pl_id
and pil.lf_evt_ocrd_dt = pln.lf_evt_ocrd_dt
and decode(rates.group_per_in_ler_id, null, 'Y', pln.actual_flag)= 'Y'
and pln.oipl_id = -1
and pil.per_in_ler_id = rates.group_per_in_ler_id (+)
and decode(rates.group_per_in_ler_id,null,pil.group_pl_id,rates.pl_id)
= pln.pl_id
and rates.oipl_id (+)= -1
and elig_stat.lookup_type (+) = 'BEN_CWB_ELIG_CRITERIA'
and elig_stat.lookup_code (+) = rates.elig_flag
and elig_stat.enabled_flag (+) = 'Y'
and rownum = 1;