[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 users.user_name , ppf.first_name , ppf.last_name
from fnd_user users
,per_All_people_f ppf
where users.employee_id=ppf.person_id
and users.employee_id=c_approver_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date;
update ben_transaction set attribute12= l_approver_name , attribute13= l_approver_last_name
where attribute2= itemkey
and attribute1= 'EMP'
and transaction_type='CWBEMPRSGN';
UPDATE ben_transaction
SET attribute21 =
( select notification_id
from wf_item_activity_statuses
where
item_key = itemkey
and item_type = itemtype
and assigned_user= l_approver_user
)
, attribute22 = l_approver_user
WHERE attribute3=itemkey
and transaction_type='CWBEMPRSGN'
and attribute1='APPR'
and attribute21 is null;
ame_api.updateApprovalStatus2(applicationIdIn =>805,
transactionIdIn => itemkey,
approvalStatusIn => ame_util.approvedStatus,
approverPersonIdIn=> l_approver_id,
transactionTypeIn=> 'PROPEMPRSGN' );
UPDATE ben_transaction
SET attribute40 = l_error_message
WHERE attribute2=itemkey
and transaction_type='CWBEMPRSGN'
and attribute1='EMP';
UPDATE ben_transaction
SET attribute21 =
( select notification_id
from wf_item_activity_statuses
where
item_key = itemkey
and item_type = itemtype
and assigned_user= l_approver_user
)
, attribute22 = l_approver_user
WHERE attribute3=itemkey
and transaction_type='CWBEMPRSGN'
and attribute1='APPR'
and attribute21 is null;
select attribute3 , attribute10 , attribute8 ,attribute14 , attribute16 , attribute5 || ' ' || attribute18 , attribute7 || ' ' || attribute19 , attribute21 , attribute6
from ben_transaction
where attribute2=itemkey
and attribute1='EMP'
and transaction_type = 'CWBEMPRSGN';
select object_version_number
from ben_per_in_ler
where per_in_ler_id = c_per_in_ler_id;
select approval_cd
from ben_cwb_person_groups
where group_per_in_ler_id =c_per_in_ler_id
and group_pl_id=c_pl_id
and group_oipl_id=-1;
ben_Person_Life_Event_api.update_Person_Life_Event
(
p_validate => false
,p_per_in_ler_id => l_per_in_ler_id
,p_mgr_ovrid_dt => sysdate
,p_mgr_ovrid_person_id => l_ovr_id
,p_ws_mgr_id => l_ws_mgr_id
,p_object_version_number => l_obj_ver_num
,p_effective_date => sysdate
,p_group_pl_id => l_pl_id
,p_procd_dt => l_procd_dt
,p_strtd_dt => l_strtd_dt
,p_voidd_dt => l_voidd_dt
);
ben_cwb_summary_pkg.delete_pl_sql_tab;
BEN_CWB_SUMMARY_PKG.update_summary_on_reassignment
(p_old_mgr_per_in_ler_id => l_ws_mgr_per_in_ler_id
,p_new_mgr_per_in_ler_id => l_prop_mgr_per_in_ler_id
,p_emp_per_in_ler_id => l_per_in_ler_id
);
ben_cwb_audit_api.update_per_record
(p_per_in_ler_id => l_per_in_ler_id
,p_old_val => l_ws_mgr_id_old
,p_audit_type_cd => 'MG'
);
select users.user_name , ppf.first_name , ppf.last_name
from fnd_user users
,per_all_people_f ppf
where
users.employee_id=ppf.person_id
and employee_id=c_approver_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date;
select pil.person_id , pil.per_in_ler_id
from ben_cwb_group_hrchy hrchy,
ben_per_in_ler pil
where hrchy.emp_per_in_ler_id = c_per_in_ler_id
--and hrchy.mgr_per_in_ler_id <> hrchy.emp_per_in_ler_id
and hrchy.lvl_num <> 0
and hrchy.mgr_per_in_ler_id = pil.per_in_ler_id;
select pil.person_id
from ben_cwb_group_hrchy hrchy,
ben_per_in_ler pil
where
hrchy.emp_per_in_ler_id =c_per_in_ler_id
and hrchy.mgr_per_in_ler_id <>hrchy.emp_per_in_ler_id
and hrchy.mgr_per_in_ler_id = pil.per_in_ler_id
and LVL_NUM =1;
select distinct pil.person_id
from ben_cwb_group_hrchy hrchy,
ben_per_in_ler pil
where
pil.group_pl_id = c_plan_id
and pil.per_in_ler_id= hrchy.mgr_per_in_ler_id
and emp_per_in_ler_id=p_prop_ws_mgr_per_in_ler_id
and hrchy.lvl_num = (select max(h1.lvl_num)
from ben_cwb_group_hrchy h1
where h1.emp_per_in_ler_id = p_prop_ws_mgr_per_in_ler_id
);
--select BEN_CWB_WF_NTF_S.NEXTVAL into l_itemkey from dual;
-- Insert Approval information record , to fetch approver list in AME
open get_top_person_id(p_plan_id);
select BEN_TRANSACTION_S.NEXTVAL into l_transaction_id from dual;
insert into ben_transaction(transaction_id,
transaction_type,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute40,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16)
values (l_transaction_id,
'CWBEMPRSGN',
'APPR',
p_prop_ws_manager_id,
l_itemkey,
p_plan_name,
p_reccount,
l_curr_ws_manager_name,
l_prop_ws_manager_name,
l_requestor_name,
p_request_date,
p_prop_ws_mgr_per_in_ler_id,
p_message,
l_top_person_id,
p_prop_ws_manager_id,
l_requestor_last_name,
l_curr_ws_manager_last_name,
l_prop_ws_manager_last_name
);
update ben_transaction set attribute2=l_requestor_person_id
where attribute3=l_itemkey
and transaction_type='CWBEMPRSGN'
and attribute1='APPR';
update ben_transaction set attribute2=l_requestor_person_id
where attribute3=l_itemkey
and transaction_type='CWBEMPRSGN'
and attribute1='APPR';
select users.user_name , ppf.first_name , ppf.last_name
from fnd_user users
,per_all_people_f ppf
where
users.employee_id=ppf.person_id
and users.employee_id=c_approver_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date;
/*select BEN_CWB_WF_NTF_S.NEXTVAL into l_itemkey from dual;*/
select BEN_TRANSACTION_S.NEXTVAL into l_transaction_id from dual;
insert into ben_transaction(transaction_id,
transaction_type,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute40,
attribute14,
attribute15,
attribute16)
values (l_transaction_id,
'CWBEMPRSGN',
'APPR',
p_prop_ws_manager_id,
l_itemkey,
p_plan_name,
p_reccount,
l_curr_ws_manager_name,
l_prop_ws_manager_name,
l_requestor_name,
fnd_date.canonical_to_date(sysdate), --p_request_date,
p_message,
l_requestor_last_name,
l_curr_ws_manager_last_name,
l_prop_ws_manager_last_name);
select ppf.first_name , ppf.last_name
from fnd_user users
,per_all_people_f ppf
where
users.employee_id=ppf.person_id
and users.employee_id=c_requestor_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date;
select BEN_TRANSACTION_S.NEXTVAL into l_transaction_id from dual;
insert into ben_transaction(transaction_id,
transaction_type,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22
)
values (l_transaction_id,
'CWBEMPRSGN',
'EMP',
p_transaction_id,
p_per_in_ler_id,
p_emp_num,
l_curr_mgr_first_name,
p_curr_ws_mgr_id,
l_prop_mgr_first_name,
p_prop_ws_mgr_id,
l_requestor_first_name,
p_requestor_id,
fnd_date.canonical_to_date(sysdate) , --p_request_date,
p_prop_ws_mgr_per_in_ler_id,
l_emp_first_name,
p_curr_ws_mgr_per_in_ler_id,
l_emp_last_name,
l_curr_mgr_last_name,
l_prop_mgr_last_name,
l_requestor_last_name,
p_group_pl_id,
p_business_group
);
update ben_transaction set status='DEL'
where attribute1= 'EMP'
and transaction_type='CWBEMPRSGN'
and attribute2=to_number(itemkey);
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;
cursor c2 is select role_id,role_name
from pqh_roles
where role_type_cd ='CWB';