[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT info.full_name
FROM ben_cwb_person_info info
WHERE info.group_per_in_ler_id = v_group_per_in_ler_id;
SELECT nvl(dsgn.wthn_yr_start_dt,dsgn.yr_perd_start_dt)||' - '||
nvl(dsgn.wthn_yr_end_dt,dsgn.yr_perd_end_dt) forPeriod
FROM ben_per_in_ler pil,
ben_cwb_pl_dsgn dsgn
WHERE pil.per_in_ler_id = p_group_per_in_ler_id
AND pil.group_pl_id = dsgn.group_pl_id
AND pil.lf_evt_ocrd_dt = dsgn.lf_evt_ocrd_dt
AND dsgn.oipl_id = -1
AND dsgn.group_pl_id = dsgn.pl_id
AND dsgn.group_oipl_id = dsgn.oipl_id;
SELECT pei.person_id person_id, ppf.full_name person_name ,
usr.user_name user_name, usr.user_id user_id
FROM per_people_extra_info pei , per_all_people_f ppf ,
fnd_user usr , pqh_roles rls
WHERE information_type = 'PQH_ROLE_USERS' and
pei.person_id = ppf.person_id
AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date and ppf.effective_end_date
and usr.employee_id = ppf.person_id
and rls.role_id = to_number(pei.pei_information3)
and nvl(pei.pei_information5,'Y')='Y'
and rls.role_id = c_role_id;
select role_id
from pqh_roles
where role_type_cd ='CWB';
PROCEDURE updateEligibility
(
p_group_per_in_ler_id NUMBER
,p_pl_pl_id NUMBER
,p_pl_oipl_id NUMBER
,p_pl_elig_flag VARCHAR2
,p_pl_elig_change_status VARCHAR2
,p_pl_person_rate_id NUMBER
,p_o1_pl_id NUMBER
,p_o1_oipl_id NUMBER
,p_o1_elig_flag VARCHAR2
,p_o1_elig_change_status VARCHAR2
,p_o1_person_rate_id NUMBER default null
,p_o2_pl_id NUMBER
,p_o2_oipl_id NUMBER
,p_o2_elig_flag VARCHAR2
,p_o2_elig_change_status VARCHAR2
,p_o2_person_rate_id NUMBER
,p_o3_pl_id NUMBER
,p_o3_oipl_id NUMBER
,p_o3_elig_flag VARCHAR2
,p_o3_elig_change_status VARCHAR2
,p_o3_person_rate_id NUMBER
,p_o4_pl_id NUMBER
,p_o4_oipl_id NUMBER
,p_o4_elig_flag VARCHAR2
,p_o4_elig_change_status VARCHAR2
,p_o4_person_rate_id NUMBER
,p_elig_ovrid_person_id NUMBER
,p_elig_ovrid_dt DATE
)IS
l_inelig_rsn_cd VARCHAR2(4) := NULL;
l_proc VARCHAR2(80) := g_package||'.updateEligibility';
SELECT object_version_number
,ws_val
,elig_flag
,ws_rt_start_date
FROM ben_cwb_person_rates
WHERE person_rate_id = v_person_rate_id;
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_o1_pl_id
,p_oipl_id => p_o1_oipl_id
,p_elig_flag => p_o1_elig_flag
,p_ws_val => l_ws_val
,p_inelig_rsn_cd => l_inelig_rsn_cd
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
,p_ws_rt_start_date => l_ws_start_date
);
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_o1_pl_id
,p_oipl_id => p_o1_oipl_id
,p_elig_flag => p_o1_elig_flag
,p_ws_val => l_ws_val
,p_inelig_rsn_cd => l_inelig_rsn_cd
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
,p_ws_rt_start_date => l_ws_start_date
);
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_pl_pl_id
,p_oipl_id => p_pl_oipl_id
,p_elig_flag => 'Y'
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
);
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_o2_pl_id
,p_oipl_id => p_o2_oipl_id
,p_elig_flag => p_o2_elig_flag
,p_ws_val => l_ws_val
,p_inelig_rsn_cd => l_inelig_rsn_cd
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
,p_ws_rt_start_date => l_ws_start_date
);
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_o2_pl_id
,p_oipl_id => p_o2_oipl_id
,p_elig_flag => p_o2_elig_flag
,p_ws_val => l_ws_val
,p_inelig_rsn_cd => l_inelig_rsn_cd
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
,p_ws_rt_start_date => l_ws_start_date
);
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_pl_pl_id
,p_oipl_id => p_pl_oipl_id
,p_elig_flag => 'Y'
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
);
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_o3_pl_id
,p_oipl_id => p_o3_oipl_id
,p_elig_flag => p_o3_elig_flag
,p_ws_val => l_ws_val
,p_inelig_rsn_cd => l_inelig_rsn_cd
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
,p_ws_rt_start_date => l_ws_start_date
);
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_o3_pl_id
,p_oipl_id => p_o3_oipl_id
,p_elig_flag => p_o3_elig_flag
,p_ws_val => l_ws_val
,p_inelig_rsn_cd => l_inelig_rsn_cd
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
,p_ws_rt_start_date => l_ws_start_date
);
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_pl_pl_id
,p_oipl_id => p_pl_oipl_id
,p_elig_flag => 'Y'
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
);
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_o4_pl_id
,p_oipl_id => p_o4_oipl_id
,p_elig_flag => p_o4_elig_flag
,p_ws_val => l_ws_val
,p_inelig_rsn_cd => l_inelig_rsn_cd
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
,p_ws_rt_start_date => l_ws_start_date
);
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_o4_pl_id
,p_oipl_id => p_o4_oipl_id
,p_elig_flag => p_o4_elig_flag
,p_ws_val => l_ws_val
,p_inelig_rsn_cd => l_inelig_rsn_cd
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
,p_ws_rt_start_date => l_ws_start_date
);
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_pl_pl_id
,p_oipl_id => p_pl_oipl_id
,p_elig_flag => 'Y'
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
);
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_pl_pl_id
,p_oipl_id => p_pl_oipl_id
,p_elig_flag => p_pl_elig_flag
,p_ws_val => l_ws_val
,p_inelig_rsn_cd => l_inelig_rsn_cd
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
,p_ws_rt_start_date => l_ws_start_date
);
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_o1_pl_id
,p_oipl_id => p_o1_oipl_id
,p_elig_flag => 'N'
,p_ws_val => null
,p_inelig_rsn_cd => 'MGR'
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
,p_ws_rt_start_date => null
);
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_o2_pl_id
,p_oipl_id => p_o2_oipl_id
,p_elig_flag => 'N'
,p_ws_val => null
,p_inelig_rsn_cd => 'MGR'
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
,p_ws_rt_start_date => null
);
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_o3_pl_id
,p_oipl_id => p_o3_oipl_id
,p_elig_flag => 'N'
,p_ws_val => null
,p_inelig_rsn_cd => 'MGR'
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
,p_ws_rt_start_date => null
);
ben_cwb_person_rates_api.update_person_rate
(
p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_o4_pl_id
,p_oipl_id => p_o4_oipl_id
,p_elig_flag => 'N'
,p_ws_val => null
,p_inelig_rsn_cd => 'MGR'
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_object_version_number => l_object_version_number
,p_ws_rt_start_date => null
);
cursor c1 is select user_name from fnd_user
where employee_id = p_rcvr_person_id;
cursor c2 is select user_name from fnd_user
where employee_id = p_ws_person_id;
PROCEDURE select_next_approver
( itemtype IN VARCHAR2
, itemkey IN VARCHAR2
, actid IN NUMBER
, funcmode IN VARCHAR2
, result OUT NOCOPY VARCHAR2
)
IS
cursor approver_name(c_approver_id IN NUMBER) is select user_name from fnd_user
where employee_id = c_approver_id;
l_proc varchar2(61) := g_package||':'||'select_next_approver';
update ben_transaction
set attribute36 = l_approver_name,
attribute38 = to_char(sysdate,'yyyy/mm/dd'),
attribute35 = c_next_approver_out.person_id
where attribute1 = itemkey
and transaction_type = 'EMPELIGEMP';
, avalue => 'Select Next Approver Node');
, avalue => 'Unable to get the next approver or failed to update the transaction table');
cursor employeeId (c_approver_name IN VARCHAR2) is select employee_id from fnd_user
where user_name = l_approver_name;
ame_api.updateApprovalStatus2(applicationIdIn => 805,
transactionIdIn => itemkey,
approvalStatusIn => ame_util.approvedStatus,
approverPersonIdIn => l_approver_id,
transactionTypeIn => 'EMPELIG');
, avalue => 'Unable to update ame approver status');
l_errmsg VARCHAR2(500) := 'Unable to update the choice records after approval';
select tran_tbl.attribute2 group_per_in_ler_id
-- Plan Level transaction values
,tran_tbl.attribute18 pl_pl_id
,tran_tbl.attribute19 pl_oipl_id
,tran_tbl.attribute7 pl_status
,tran_tbl.attribute12 pl_change_status
,tran_tbl.attribute17 pl_person_rate_id
-- Option1 Level transaction values
,tran_tbl.attribute21 o1_pl_id
,tran_tbl.attribute22 o1_oipl_id
,tran_tbl.attribute8 o1_status
,tran_tbl.attribute13 o1_change_status
,tran_tbl.attribute20 o1_person_rate_id
-- Option2 Level transaction values
,tran_tbl.attribute24 o2_pl_id
,tran_tbl.attribute25 o2_oipl_id
,tran_tbl.attribute9 o2_status
,tran_tbl.attribute14 o2_change_status
,tran_tbl.attribute23 o2_person_rate_id
-- Option3 Level transaction values
,tran_tbl.attribute27 o3_pl_id
,tran_tbl.attribute28 o3_oipl_id
,tran_tbl.attribute10 o3_status
,tran_tbl.attribute15 o3_change_status
,tran_tbl.attribute26 o3_person_rate_id
-- Option4 Level transaction values
,tran_tbl.attribute30 o4_pl_id
,tran_tbl.attribute31 o4_oipl_id
,tran_tbl.attribute11 o4_status
,tran_tbl.attribute16 o4_change_status
,tran_tbl.attribute29 o4_person_rate_id
,tran_tbl.attribute37 ovrd_person_id
,pil.per_in_ler_stat_cd ler_stat
from ben_transaction tran_tbl,
ben_per_in_ler pil
where tran_tbl.attribute1 = itemkey
and tran_tbl.transaction_type = 'EMPELIGEMP'
and to_number(tran_tbl.attribute2) = pil.per_in_ler_id;
ben_cwb_summary_pkg.delete_pl_sql_tab;
updateEligibility
(
p_group_per_in_ler_id => to_number(emp_elig.group_per_in_ler_id)
-- Plan Level Parameters
,p_pl_pl_id => to_number(emp_elig.pl_pl_id)
,p_pl_oipl_id => to_number(emp_elig.pl_oipl_id)
,p_pl_elig_flag => emp_elig.pl_status
,p_pl_elig_change_status => emp_elig.pl_change_status
,p_pl_person_rate_id => to_number(emp_elig.pl_person_rate_id)
-- Option1 Level Parameters
,p_o1_pl_id => to_number(emp_elig.o1_pl_id)
,p_o1_oipl_id => to_number(emp_elig.o1_oipl_id)
,p_o1_elig_flag => emp_elig.o1_status
,p_o1_elig_change_status => emp_elig.o1_change_status
,p_o1_person_rate_id => to_number(emp_elig.o1_person_rate_id)
-- Option2 Level Parameters
,p_o2_pl_id => to_number(emp_elig.o2_pl_id)
,p_o2_oipl_id => to_number(emp_elig.o2_oipl_id)
,p_o2_elig_flag => emp_elig.o2_status
,p_o2_elig_change_status => emp_elig.o2_change_status
,p_o2_person_rate_id => to_number(emp_elig.o2_person_rate_id)
-- Option3 Level Parameters
,p_o3_pl_id => to_number(emp_elig.o3_pl_id)
,p_o3_oipl_id => to_number(emp_elig.o3_oipl_id)
,p_o3_elig_flag => emp_elig.o3_status
,p_o3_elig_change_status => emp_elig.o3_change_status
,p_o3_person_rate_id => to_number(emp_elig.o3_person_rate_id)
-- Option4 Level Parameters
,p_o4_pl_id => to_number(emp_elig.o4_pl_id)
,p_o4_oipl_id => to_number(emp_elig.o4_oipl_id)
,p_o4_elig_flag => emp_elig.o4_status
,p_o4_elig_change_status => emp_elig.o4_change_status
,p_o4_person_rate_id => to_number(emp_elig.o4_person_rate_id)
,p_elig_ovrid_person_id => to_number(emp_elig.ovrd_person_id)
,p_elig_ovrid_dt => sysdate
);
l_errmsg := 'Unable to update the choice record as it does not have a started life event';
update ben_transaction
set STATUS='PROCESSED'
where attribute1 = itemkey
and transaction_type='EMPELIGHDR';
update ben_transaction
set STATUS='PROCESSED'
where transaction_type = 'EMPELIGEMP'
and attribute1 = itemkey;