[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_ss_update_start_dt IN DATE,
p_ss_update_end_dt IN DATE,
p_effective_dt IN DATE
)
RETURN VARCHAR2
IS
l_access_cd VARCHAR2 (2);
ELSIF p_effective_dt NOT BETWEEN p_ss_update_start_dt AND p_ss_update_end_dt
THEN
l_access_cd := 'RO';
Select bcpi.full_name,
bcpi.brief_name,
bcpi.custom_name
From ben_cwb_person_info bcpi,
ben_cwb_group_hrchy bcgh
where bcgh.emp_per_in_ler_id = p_emp_per_in_ler_id
and bcgh.lvl_num = (select max(lvl_num) - p_level + 1
from ben_cwb_group_hrchy
where emp_per_in_ler_id = p_emp_per_in_ler_id)
and bcgh.lvl_num > 0
and bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id;
SELECT valu.profile_option_value
FROM fnd_profile_options options
,fnd_profile_option_values valu
WHERE options.profile_option_name = upper(v_name)
AND options.start_date_active <= sysdate
AND nvl(options.end_date_active, sysdate) >= sysdate
AND options.profile_option_id = valu.profile_option_id
AND valu.level_id = 10001;
SELECT valu.profile_option_value
FROM fnd_profile_options options
,fnd_profile_option_values valu
WHERE options.profile_option_name = upper(v_name)
AND options.start_date_active <= sysdate
AND nvl(options.end_date_active, sysdate) >= sysdate
AND options.profile_option_id = valu.profile_option_id
AND valu.level_value_application_id = 800
AND valu.level_id = 10003
AND valu.level_value = v_resp_id;
SELECT valu.profile_option_value
FROM fnd_profile_options options
,fnd_profile_option_values valu
WHERE options.profile_option_name = upper(v_name)
AND options.start_date_active <= sysdate
AND nvl(options.end_date_active, sysdate) >= sysdate
AND options.profile_option_id = valu.profile_option_id
AND valu.application_id = 805
AND valu.level_id = 10004
AND valu.level_value = v_user_id;
SELECT 'x'
FROM per_people_f ppf,
per_person_types ppt
WHERE ppf.person_id = p_person_id
AND ppt.person_type_id = ppf.person_type_id
AND ppt.system_person_type <> 'EX_EMP_APL'
AND p_effective_date between ppf.effective_start_date and ppf.effective_end_date;
Select name
From ben_cwb_pl_dsgn
Where pl_id = p_group_plan_id
and group_pl_id = p_group_plan_id
And lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
--And oipl_id <> -1
--And opt_count = 1;
Select bcpr.*
From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
And bcpd.group_pl_id = p_group_plan_id
-- AND bcpd.pl_id = p_group_plan_id
And bcpd.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
--And bcpd.oipl_id <> -1
--And bcpd.opt_count = 1
and oipl_ordr_num = 1
And bcpr.pl_id = bcpd.pl_id
And bcpr.group_pl_id = bcpd.group_pl_id
And bcpr.oipl_id = bcpd.oipl_id
And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
and bcpr.elig_flag = 'Y';
Select decode(ws_nnmntry_uom,null,currency, hr_general.decode_lookup('BEN_NNMNTRY_UOM',ws_nnmntry_uom) )
From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
And bcpd.group_pl_id = p_group_plan_id
And bcpd.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
And bcpr.pl_id = bcpd.pl_id
And bcpr.group_pl_id = bcpd.group_pl_id
And bcpr.oipl_id = bcpd.oipl_id
And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
and bcpr.elig_flag = 'Y'
and oipl_ordr_num = 1;
Select name
From ben_cwb_pl_dsgn
Where pl_id = p_group_plan_id
And group_pl_id = p_group_plan_id
And lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
-- And oipl_id <> -1
-- And opt_count = 2;
Select bcpr.*
From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
And bcpd.group_pl_id = p_group_plan_id
--AND bcpd.pl_id = p_group_plan_id
And bcpd.lf_evt_ocrd_dt= p_lf_evt_ocrd_dt
--And bcpd.oipl_id <> -1
-- And bcpd.opt_count = 2
and oipl_ordr_num = 2
And bcpd.pl_id = bcpr.pl_id
And bcpd.group_pl_id = bcpr.group_pl_id
And bcpd.oipl_id = bcpr.oipl_id
And bcpd.lf_evt_ocrd_dt = bcpr.lf_evt_ocrd_dt
and bcpr.elig_flag = 'Y';
Select decode(ws_nnmntry_uom,null,currency,hr_general.decode_lookup('BEN_NNMNTRY_UOM',ws_nnmntry_uom))
From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
And bcpd.group_pl_id = p_group_plan_id
And bcpd.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
And bcpr.pl_id = bcpd.pl_id
And bcpr.group_pl_id = bcpd.group_pl_id
And bcpr.oipl_id = bcpd.oipl_id
And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
and bcpr.elig_flag = 'Y'
and oipl_ordr_num = 2;
Select name
From ben_cwb_pl_dsgn
Where pl_id = p_group_plan_id
And group_pl_id = p_group_plan_id
And lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
-- And oipl_id <> -1
-- And opt_count = 3;
Select bcpr.*
From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
And bcpd.group_pl_id = p_group_plan_id
-- AND bcpd.pl_id = p_group_plan_id
And bcpd.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
--And bcpd.oipl_id <> -1
--And bcpd.opt_count = 3
and oipl_ordr_num = 3
And bcpd.pl_id = bcpr.pl_id
And bcpd.group_pl_id = bcpr.group_pl_id
And bcpd.oipl_id = bcpr.oipl_id
And bcpd.lf_evt_ocrd_dt = bcpr.lf_evt_ocrd_dt
and bcpr.elig_flag = 'Y';
Select decode(ws_nnmntry_uom,null,currency,hr_general.decode_lookup('BEN_NNMNTRY_UOM',ws_nnmntry_uom))
From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
And bcpd.group_pl_id = p_group_plan_id
And bcpd.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
And bcpr.pl_id = bcpd.pl_id
And bcpr.group_pl_id = bcpd.group_pl_id
And bcpr.oipl_id = bcpd.oipl_id
And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
and bcpr.elig_flag = 'Y'
and oipl_ordr_num = 3;
Select name
From ben_cwb_pl_dsgn
Where pl_id = p_group_plan_id
And group_pl_id = p_group_plan_id
And lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
-- And oipl_id <> -1
-- And opt_count = 4;
Select bcpr.*
From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
And bcpd.group_pl_id = p_group_plan_id
-- AND bcpd.pl_id = p_group_plan_id
And bcpd.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
--And bcpd.oipl_id <> -1
-- And bcpd.opt_count = 4
and oipl_ordr_num = 4
And bcpd.pl_id = bcpr.pl_id
And bcpd.group_pl_id = bcpr.group_pl_id
And bcpd.oipl_id = bcpr.oipl_id
And bcpd.lf_evt_ocrd_dt = bcpr.lf_evt_ocrd_dt
and bcpr.elig_flag = 'Y';
Select decode(ws_nnmntry_uom,null,currency,hr_general.decode_lookup('BEN_NNMNTRY_UOM',ws_nnmntry_uom))
From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
And bcpd.group_pl_id = p_group_plan_id
And bcpd.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
And bcpr.pl_id = bcpd.pl_id
And bcpr.group_pl_id = bcpd.group_pl_id
And bcpr.oipl_id = bcpd.oipl_id
And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
and bcpr.elig_flag = 'Y'
and oipl_ordr_num = 4;
select max(lf_evt_ocrd_dt)
from ben_cwb_person_rates
where group_per_in_ler_id = p_group_per_in_ler_id
and group_pl_id = p_group_plan_id
--and pl_id = p_pl_id
and lf_evt_ocrd_dt < p_lf_evt_ocrd_dt
and elig_flag = 'Y';
select * from ben_cwb_person_rates
where group_per_in_ler_id = p_group_per_in_ler_id
and group_pl_id = p_group_plan_id
--and pl_id = p_pl_id
and oipl_id = p_oipl_id
and lf_evt_ocrd_dt = c_lf_evt_ocrd_dt
and elig_flag = 'Y';
select max(nvl(proposed_salary_n,0))
from per_pay_proposals ppp ,ben_cwb_person_rates rts
where rts.pay_proposal_id = ppp.pay_proposal_id
and rts.group_per_in_ler_id = p_group_per_in_ler_id
and rts.group_pl_id = p_group_plan_id
and rts.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
and rts.ws_val is not null
and rts.elig_flag = 'Y';
select nvl(base_salary,0)
from ben_cwb_person_info
where group_per_in_ler_id = p_group_per_in_ler_id;
select max(change_date)
from per_pay_proposals ppp
,ben_cwb_person_rates rts
where rts.pay_proposal_id = ppp.pay_proposal_id
and rts.group_per_in_ler_id = p_group_per_in_ler_id
and rts.group_pl_id = p_group_plan_id
and rts.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
and rts.ws_val is not null
and rts.elig_flag = 'Y';
select BASE_SALARY_CHANGE_DATE
from ben_cwb_person_info
where group_per_in_ler_id = p_group_per_in_ler_id;
select initcap(pay_basis)
from per_pay_bases ppb,
per_all_assignments_f paaf
where ppb.pay_basis_id = paaf.pay_basis_id
and sysdate between paaf.effective_start_date and paaf.effective_end_date
and paaf.primary_flag = 'Y'
and paaf.assignment_id = g_person_rates_rec.assignment_id;
select hr_general.decode_lookup('PERFORMANCE_RATING',attribute3)
From ben_transaction
where transaction_id = p_assignment_id
and transaction_type = 'CWBPERF'||to_char(p_perf_revw_strt_dt,'rrrr/mm/dd')
||p_emp_interview_typ_cd;
select pqh_document_short_name
from ben_cwb_pl_dsgn
where pl_id = p_plan_id
and oipl_id = -1
and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
select info.full_name, info.brief_name, info.custom_name
from ben_cwb_person_info info,
ben_cwb_group_hrchy hrchy
where hrchy.emp_per_in_ler_id = p_group_per_in_ler_id
and hrchy.mgr_per_in_ler_id = info.group_per_in_ler_id
and hrchy.lvl_num = 1;
Select currency
From ben_cwb_person_rates bcpr,ben_cwb_pl_dsgn bcpd
Where bcpr.group_per_in_ler_id = p_group_per_in_ler_id
And bcpd.group_pl_id = p_group_plan_id
And bcpd.lf_evt_ocrd_dt = p_lf_evnt_ocrd_dt
And bcpr.pl_id = bcpd.pl_id
And bcpr.group_pl_id = bcpd.group_pl_id
And bcpr.oipl_id = bcpd.oipl_id
And bcpr.lf_evt_ocrd_dt = bcpd.lf_evt_ocrd_dt
And bcpr.elig_flag = 'Y'
And oipl_ordr_num = p_oipl_ordr_num;