[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';
,p_comp_selection_rl in number default null
,p_person_selection_rl in number default null
,p_ler_id in number default null
,p_organization_id in number default null
,p_benfts_grp_id in number default null
,p_location_id in number default null
,p_pstl_zip_rng_id in number default null
,p_rptg_grp_id in number default null
,p_pl_typ_id in number default null
,p_opt_id in number default null
,p_eligy_prfl_id in number default null
,p_vrbl_rt_prfl_id in number default null
,p_legal_entity_id in number default null
,p_payroll_id in number default null
,p_debug_messages_flag in varchar2 default 'N'
,p_cm_trgr_typ_cd in varchar2 default null
,p_cm_typ_id in number default null
,p_age_fctr_id in number default null
,p_min_age in number default null
,p_max_age in number default null
,p_los_fctr_id in number default null
,p_min_los in number default null
,p_max_los in number default null
,p_cmbn_age_los_fctr_id in number default null
,p_min_cmbn in number default null
,p_max_cmbn in number default null
,p_date_from in date default null
,p_elig_enrol_cd in varchar2 default null
,p_actn_typ_id in number default null
,p_use_fctr_to_sel_flag in varchar2 default 'N'
,p_los_det_to_use_cd in varchar2 default null
,p_audit_log_flag in varchar2 default 'N'
,p_lmt_prpnip_by_org_flag in varchar2 default 'N'
,p_lf_evt_ocrd_dt in date default null
,p_ptnl_ler_for_per_stat_cd in varchar2 default null
,p_bft_attribute_category in varchar2 default null
,p_bft_attribute1 in varchar2 default null
,p_bft_attribute3 in varchar2 default null
,p_bft_attribute4 in varchar2 default null
,p_bft_attribute5 in varchar2 default null
,p_bft_attribute6 in varchar2 default null
,p_bft_attribute7 in varchar2 default null
,p_bft_attribute8 in varchar2 default null
,p_bft_attribute9 in varchar2 default null
,p_bft_attribute10 in varchar2 default null
,p_bft_attribute11 in varchar2 default null
,p_bft_attribute12 in varchar2 default null
,p_bft_attribute13 in varchar2 default null
,p_bft_attribute14 in varchar2 default null
,p_bft_attribute15 in varchar2 default null
,p_bft_attribute16 in varchar2 default null
,p_bft_attribute17 in varchar2 default null
,p_bft_attribute18 in varchar2 default null
,p_bft_attribute19 in varchar2 default null
,p_bft_attribute20 in varchar2 default null
,p_bft_attribute21 in varchar2 default null
,p_bft_attribute22 in varchar2 default null
,p_bft_attribute23 in varchar2 default null
,p_bft_attribute24 in varchar2 default null
,p_bft_attribute25 in varchar2 default null
,p_bft_attribute26 in varchar2 default null
,p_bft_attribute27 in varchar2 default null
,p_bft_attribute28 in varchar2 default null
,p_bft_attribute29 in varchar2 default null
,p_bft_attribute30 in varchar2 default null
,p_request_id in number default null
,p_program_application_id in number default null
,p_program_id in number default null
,p_program_update_date in date default null
,p_object_version_number out nocopy number
,p_effective_date in date default null
,p_enrt_perd_id in number default null
,p_inelg_action_cd in varchar2 default null
,p_org_hierarchy_id in number default null
,p_org_starting_node_id in number default null
,p_grade_ladder_id in number default null
,p_asg_events_to_all_sel_dt in varchar2 default null
,p_rate_id in number default null
,p_per_sel_dt_cd in varchar2 default null
,p_per_sel_freq_cd in varchar2 default null
,p_per_sel_dt_from in date default null
,p_per_sel_dt_to in date default null
,p_year_from in number default null
,p_year_to in number default null
,p_cagr_id in number default null
,p_qual_type in number default null
,p_qual_status in varchar2 default null
,p_concat_segs in varchar2 default null
,p_grant_price_val in number default null
)
is
--
-- Declare cursors and local variables
--
l_benefit_action_id ben_benefit_actions.benefit_action_id%TYPE;
select ben_benefit_actions_s.nextval
into l_benefit_action_id
from sys.dual;
insert into ben_benefit_actions
(benefit_action_id
,process_date
,uneai_effective_date
,mode_cd
,derivable_factors_flag
,close_uneai_flag
,validate_flag
,person_id
,person_type_id
,pgm_id
,business_group_id
,pl_id
,popl_enrt_typ_cycl_id
,no_programs_flag
,no_plans_flag
,comp_selection_rl
,person_selection_rl
,ler_id
,organization_id
,benfts_grp_id
,location_id
,pstl_zip_rng_id
,rptg_grp_id
,pl_typ_id
,opt_id
,eligy_prfl_id
,vrbl_rt_prfl_id
,legal_entity_id
,payroll_id
,debug_messages_flag
,cm_trgr_typ_cd
,cm_typ_id
,age_fctr_id
,min_age
,max_age
,los_fctr_id
,min_los
,max_los
,cmbn_age_los_fctr_id
,min_cmbn
,max_cmbn
,date_from
,elig_enrol_cd
,actn_typ_id
,use_fctr_to_sel_flag
,los_det_to_use_cd
,audit_log_flag
,lmt_prpnip_by_org_flag
,lf_evt_ocrd_dt
,ptnl_ler_for_per_stat_cd
,bft_attribute_category
,bft_attribute1
,bft_attribute3
,bft_attribute4
,bft_attribute5
,bft_attribute6
,bft_attribute7
,bft_attribute8
,bft_attribute9
,bft_attribute10
,bft_attribute11
,bft_attribute12
,bft_attribute13
,bft_attribute14
,bft_attribute15
,bft_attribute16
,bft_attribute17
,bft_attribute18
,bft_attribute19
,bft_attribute20
,bft_attribute21
,bft_attribute22
,bft_attribute23
,bft_attribute24
,bft_attribute25
,bft_attribute26
,bft_attribute27
,bft_attribute28
,bft_attribute29
,bft_attribute30
,request_id
,program_application_id
,program_id
,program_update_date
,object_version_number
,enrt_perd_id
,inelg_action_cd
,org_hierarchy_id
,org_starting_node_id
,grade_ladder_id
,asg_events_to_all_sel_dt
,rate_id
,per_sel_dt_cd
,per_sel_freq_cd
,per_sel_dt_from
,per_sel_dt_to
,year_from
,year_to
,cagr_id
,qual_type
,qual_status
,concat_segs
,grant_price_val)
values
(l_benefit_action_id
,p_process_date
,p_uneai_effective_date
,p_mode_cd
,p_derivable_factors_flag
,p_close_uneai_flag
,p_validate_flag
,p_person_id
,p_person_type_id
,p_pgm_id
,p_business_group_id
,p_pl_id
,p_popl_enrt_typ_cycl_id
,p_no_programs_flag
,p_no_plans_flag
,p_comp_selection_rl
,p_person_selection_rl
,p_ler_id
,p_organization_id
,p_benfts_grp_id
,p_location_id
,p_pstl_zip_rng_id
,p_rptg_grp_id
,p_pl_typ_id
,p_opt_id
,p_eligy_prfl_id
,p_vrbl_rt_prfl_id
,p_legal_entity_id
,p_payroll_id
,p_debug_messages_flag
,p_cm_trgr_typ_cd
,p_cm_typ_id
,p_age_fctr_id
,p_min_age
,p_max_age
,p_los_fctr_id
,p_min_los
,p_max_los
,p_cmbn_age_los_fctr_id
,p_min_cmbn
,p_max_cmbn
,p_date_from
,p_elig_enrol_cd
,p_actn_typ_id
,p_use_fctr_to_sel_flag
,p_los_det_to_use_cd
,p_audit_log_flag
,p_lmt_prpnip_by_org_flag
,to_char(p_lf_evt_ocrd_dt,'DD-MON-YYYY')
,p_ptnl_ler_for_per_stat_cd
,p_bft_attribute_category
,p_bft_attribute1
,p_bft_attribute3
,p_bft_attribute4
,p_bft_attribute5
,p_bft_attribute6
,p_bft_attribute7
,p_bft_attribute8
,p_bft_attribute9
,p_bft_attribute10
,p_bft_attribute11
,p_bft_attribute12
,p_bft_attribute13
,p_bft_attribute14
,p_bft_attribute15
,p_bft_attribute16
,p_bft_attribute17
,p_bft_attribute18
,p_bft_attribute19
,p_bft_attribute20
,p_bft_attribute21
,p_bft_attribute22
,p_bft_attribute23
,p_bft_attribute24
,p_bft_attribute25
,p_bft_attribute26
,p_bft_attribute27
,p_bft_attribute28
,p_bft_attribute29
,p_bft_attribute30
,p_request_id
,p_program_application_id
,p_program_id
,p_program_update_date
,l_object_version_number
,p_enrt_perd_id
,p_inelg_action_cd
,p_org_hierarchy_id
,p_org_starting_node_id
,p_grade_ladder_id
,p_asg_events_to_all_sel_dt
,p_rate_id
,p_per_sel_dt_cd
,p_per_sel_freq_cd
,p_per_sel_dt_from
,p_per_sel_dt_to
,p_year_from
,p_year_to
,p_cagr_id
,p_qual_type
,p_qual_status
,p_concat_segs
,p_grant_price_val);
PROCEDURE insert_person_actions (
p_per_actn_id_array IN HR_MISC_SS_NUMBER_TABLE
, p_per_id IN HR_MISC_SS_NUMBER_TABLE
, p_group_per_in_ler_id IN HR_MISC_SS_NUMBER_TABLE
, p_benefit_action_id IN NUMBER
,p_is_placeholder IN HR_MISC_SS_NUMBER_TABLE
)
IS
l_num_rows NUMBER := p_per_actn_id_array.COUNT;
INSERT INTO ben_person_actions
(person_action_id
, person_id
, ler_id
, benefit_action_id
, action_status_cd
, object_version_number
, NON_PERSON_CD
)
VALUES (p_per_actn_id_array (l_count)
, p_per_id (l_count)
, p_group_per_in_ler_id (l_count)
, p_benefit_action_id
, 'U'
, 1
, decode(p_is_placeholder (l_count),1,'Y','N')
);
END insert_person_actions;
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 = v_name
AND options.start_date_active <= SYSDATE
AND nvl (options.end_date_active
,SYSDATE) >= SYSDATE
AND options.application_id = valu.application_id
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 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;