[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
from wf_item_attribute_values wiav
where wiav.item_type = p_item_type
and wiav.item_key = p_item_key
and wiav.name = p_name;
SELECT process_activity
into ln_activity_id
from
(select process_activity
FROM WF_ITEM_ACTIVITY_STATUSES IAS
WHERE ias.item_type = p_item_type
and ias.item_key = p_item_key
and ias.activity_status = 'NOTIFIED'
and ias.process_activity in (
select wpa.instance_id
FROM WF_PROCESS_ACTIVITIES WPA,
WF_ACTIVITY_ATTRIBUTES WAA,
WF_ACTIVITIES WA,
WF_ITEMS WI
WHERE wpa.process_item_type = ias.item_type
and wa.item_type = wpa.process_item_type
and wa.name = wpa.activity_name
and wi.item_type = ias.item_type
and wi.item_key = ias.item_key
and wi.begin_date >= wa.begin_date
and wi.begin_date < nvl(wa.end_date,wi.begin_date+1)
and waa.activity_item_type = wa.item_type
and waa.activity_name = wa.name
and waa.activity_version = wa.version
and waa.type = 'FORM'
)
order by begin_date desc)
where rownum<=1;
select text_value
into lv_result_code
from wf_notification_attributes
where notification_id=wf_engine.context_nid
and name='RESULT';
select text_value
into lv_comments
from wf_notification_attributes
where notification_id=wf_engine.context_nid
and name='WF_NOTE';
DEL :Delete
REJECTED :Reject
RESUBMIT :Resubmit
RETURNEDFORCORRECTION:Return for Correction
SFL :Saved For Later
START :Start Over
TIMEOUT :Timeout/No response
*/
-- block update the approval action history
begin
if(lv_result_code ='APPROVED') then
if g_debug then
hr_utility.set_location('calling hr_trans_history_api.archive_approve',4);
hr_utility.set_location('calling hr_trans_history_api.archive_delete',7);
hr_trans_history_api.archive_delete(c_transaction_id,
wf_engine.context_nid,
wf_engine.context_user,
lv_comments);
select RECIPIENT_ROLE
into lv_recipient
from wf_notifications
where notification_id=wf_engine.context_nid;
select display_name into l_current_forward_to_disp_name from wf_roles where name = l_current_forward_to_username;
IF lv_exists <>'N' AND lv_isvalid <>'DELETED' THEN
l_forward_to_person_id :=
wf_engine.GetItemAttrNumber
(itemtype => p_item_type,
itemkey => p_item_key,
aname => lv_item_name
);
aname=>'HR_APR_API_INSERTION_ATTR',
text_value=>p_approverRec.api_insertion,
number_value=>null,
date_value=>null);
aname=>'HR_APR_API_INSERTION_ATTR',
text_value=>p_approverRec.api_insertion,
number_value=>null,
date_value=>null);
SELECT 'Y'
FROM wf_local_roles
WHERE name = p_role_name;
select user_name
from wf_user_roles
where role_name = p_role_name;
procedure updateApprovalHistory( p_item_type in varchar2,
p_item_key in varchar2,
p_act_id in number,
funmode in varchar2,
result out nocopy varchar2 )
is
-- local variables
c_proc constant varchar2(30) := 'updateApprovalHistory';
end updateApprovalHistory;
procedure updateApproveStatus( p_item_type in varchar2,
p_item_key in varchar2,
p_act_id in number,
funmode in varchar2,
result out nocopy varchar2 )
is
-- local variables
c_proc constant varchar2(30) := 'updateApproveStatus';
select *
from wf_user_roles
where role_name = p_role_name;
select ORIGINAL_RECIPIENT into original_forward_to_user from wf_notifications where notification_id = notification_rec.notification_id;
hr_utility.set_location('calling ame_api2.updateApprovalStatus2', 3);
hr_utility.set_location('before update AME: ' || l_current_forward_to_username,111);
ame_api6.updateApprovalStatus2(applicationIdIn=>c_application_id,
transactionTypeIn =>c_transaction_type,
transactionIdIn=>c_transaction_id,
approvalStatusIn =>ame_util.approvedStatus,
approverNameIn =>l_current_forward_to_username,
itemClassIn => null,
itemIdIn =>null,
actionTypeIdIn=> null,
groupOrChainIdIn =>null,
occurrenceIn =>null,
notificationIn => notification_rec,
forwardeeIn =>ame_util.emptyApproverRecord2,
updateItemIn =>false);
hr_utility.set_location('Error in updateApproveStatus SQLERRM' ||' '||to_char(SQLCODE),10);
hr_utility.set_location('returned from calling ame_api2.updateApprovalStatus2', 8);
updateApprovalHistory( p_item_type=>p_item_type,
p_item_key=>p_item_key,
p_act_id=>p_act_id,
funmode=>funmode,
result=>result);*/
hr_utility.set_location('Error in updateApproveStatus SQLERRM' ||' '||to_char(SQLCODE),30);
end updateApproveStatus;
ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
transactionTypeIn =>c_transaction_type,
transactionIdIn=>c_transaction_id,
approvalStatusIn =>ame_util.notifiedStatus,
approverNameIn =>c_next_approvers(l_index).name,
itemClassIn => wf_engine.GetItemAttrText(itemtype => p_item_type ,
itemkey => p_item_key,
aname => 'HR_APR_ITEM_CLASS_ATTR'),
-- HR_APR_ITEM_ID_ATTR
itemIdIn =>wf_engine.GetItemAttrText(itemtype => p_item_type ,
itemkey => p_item_key,
aname => 'HR_APR_ITEM_ID_ATTR'),
-- HR_APR_ACTION_TYPE_ID_ATTR
actionTypeIdIn=> wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
itemkey => p_item_key,
aname => 'HR_APR_ACTION_TYPE_ID_ATTR'),
-- HR_APR_GRPORCHN_ID_ATTR
groupOrChainIdIn =>wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
itemkey => p_item_key,
aname => 'HR_APR_GRPORCHN_ID_ATTR'),
occurrenceIn =>null,
forwardeeIn =>ame_util.emptyApproverRecord2,
updateItemIn =>false);
hr_utility.set_location('calling ame_api2.updateApprovalStatus2', 2);
ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
transactionTypeIn =>c_transaction_type,
transactionIdIn=>c_transaction_id,
approvalStatusIn =>ame_util.notifiedStatus,
approverNameIn =>l_current_forward_to_username,
itemClassIn => wf_engine.GetItemAttrText(itemtype => p_item_type ,
itemkey => p_item_key,
aname => 'HR_APR_ITEM_CLASS_ATTR'),
-- HR_APR_ITEM_ID_ATTR
itemIdIn =>wf_engine.GetItemAttrText(itemtype => p_item_type ,
itemkey => p_item_key,
aname => 'HR_APR_ITEM_ID_ATTR'),
-- HR_APR_ACTION_TYPE_ID_ATTR
actionTypeIdIn=> wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
itemkey => p_item_key,
aname => 'HR_APR_ACTION_TYPE_ID_ATTR'),
-- HR_APR_GRPORCHN_ID_ATTR
groupOrChainIdIn =>wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
itemkey => p_item_key,
aname => 'HR_APR_GRPORCHN_ID_ATTR'),
occurrenceIn =>null,
forwardeeIn =>ame_util.emptyApproverRecord2,
updateItemIn =>false);
procedure updateNoResponseStatus( p_item_type in varchar2,
p_item_key in varchar2,
p_act_id in number,
funmode in varchar2,
result out nocopy varchar2 )
is
-- local variables
c_proc constant varchar2(30) := 'updateNoResponseStatus';
select *
from wf_user_roles
where role_name = p_role_name;
ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
transactionTypeIn =>c_transaction_type,
transactionIdIn=>c_transaction_id,
approvalStatusIn =>ame_util.noResponseStatus,
approverNameIn =>c.user_name,
itemClassIn => null,
itemIdIn =>null,
actionTypeIdIn=> null,
groupOrChainIdIn =>null,
occurrenceIn =>null,
forwardeeIn =>ame_util.emptyApproverRecord2,
updateItemIn =>false);
ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
transactionTypeIn =>c_transaction_type,
transactionIdIn=>c_transaction_id,
approvalStatusIn =>ame_util.noResponseStatus,
approverNameIn =>c_next_approvers(l_index).name,
itemClassIn => null,
itemIdIn =>null,
actionTypeIdIn=> null,
groupOrChainIdIn =>null,
occurrenceIn =>null,
forwardeeIn =>ame_util.emptyApproverRecord2,
updateItemIn =>false);
hr_utility.set_location('calling ame_api2.updateApprovalStatus2', 2);
ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
transactionTypeIn =>c_transaction_type,
transactionIdIn=>c_transaction_id,
approvalStatusIn =>ame_util.noResponseStatus,
approverNameIn =>l_current_forward_to_username,
itemClassIn => null,
itemIdIn =>null,
actionTypeIdIn=> null,
groupOrChainIdIn =>null,
occurrenceIn =>null,
forwardeeIn =>ame_util.emptyApproverRecord2,
updateItemIn =>false);
end updateNoResponseStatus;
SELECT transaction_ref_table, transaction_ref_id
FROM hr_api_transactions
WHERE transaction_id = p_txn_id;
select text_value
into lv_result_code
from wf_notification_attributes
where notification_id=wf_engine.context_nid
and name='RESULT';
select orig_system,orig_system_id,display_name from wf_roles where name = wf_engine.context_new_role;
select last_name,first_name from per_all_people_f where
person_id = l_new_fwd_person_id;*/
hr_utility.set_location('calling ame_api2.updateApprovalStatus2', 3);
ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
transactionTypeIn =>c_transaction_type,
transactionIdIn=>c_transaction_id,
approvalStatusIn =>ame_util.forwardStatus,
approverNameIn =>l_current_forward_to_username,
itemClassIn => null,
itemIdIn =>null,
actionTypeIdIn=> null,
groupOrChainIdIn =>null,
occurrenceIn =>null,
forwardeeIn =>rec_forwardee,
updateItemIn =>false);
hr_utility.set_location('Error in updateApproveStatus SQLERRM' ||' '||to_char(SQLCODE),10);
hr_utility.set_location('returned from calling ame_api2.updateApprovalStatus2', 8);
hr_utility.set_location('calling ame_api2.updateApprovalStatus2', 2);
ame_api6.updateApprovalStatus2(applicationIdIn=>c_application_id,
transactionTypeIn =>c_transaction_type,
transactionIdIn=>c_transaction_id,
approvalStatusIn =>null,
approverNameIn =>c_all_approvers(i).name,
itemClassIn => c_all_approvers(i).ITEM_CLASS,
itemIdIn =>c_all_approvers(i).ITEM_ID,
actionTypeIdIn=> c_all_approvers(i).ACTION_TYPE_ID,
groupOrChainIdIn =>c_all_approvers(i).GROUP_OR_CHAIN_ID,
occurrenceIn =>c_all_approvers(i).OCCURRENCE,
notificationIn => notification_rec,
forwardeeIn =>ame_util.emptyApproverRecord2,
updateItemIn =>false);
ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
transactionTypeIn =>c_transaction_type,
transactionIdIn=>c_transaction_id,
approvalStatusIn =>null,
approverNameIn =>c_all_approvers(i).name,
itemClassIn => c_all_approvers(i).ITEM_CLASS,
itemIdIn =>c_all_approvers(i).ITEM_ID,
actionTypeIdIn=> c_all_approvers(i).ACTION_TYPE_ID,
groupOrChainIdIn =>c_all_approvers(i).GROUP_OR_CHAIN_ID,
occurrenceIn =>c_all_approvers(i).OCCURRENCE,
forwardeeIn =>ame_util.emptyApproverRecord2,
updateItemIn =>false);
select creator_person_id
into lv_creator_person_id
from hr_api_transactions
where transaction_id=p_transaction_id;
select business_group_id
into lv_businessGroupId
from per_all_people_f
where person_id=p_orig_system_id
and sysdate between effective_start_date and effective_end_date;
select original_recipient
into lv_user_name
from wf_notifications
where notification_id =p_notification_id;
select original_recipient
into lv_user_name
from wf_notifications
where notification_id =p_notification_id;
SELECT 'X'
FROM wf_item_attribute_values
WHERE item_type = p_item_Type
AND item_key = p_item_Key
AND name like 'ADDITIONAL_APPROVER_%'
AND number_value = p_rfcUserOrigSystemId;
SELECT pth.employee_id
FROM pqh_ss_approval_history pah,
fnd_user pth
WHERE pah.user_name = pth.user_name
AND pah.transaction_history_id = p_transaction_id
AND approval_history_id = (
SELECT MAX(approval_history_id)
FROM pqh_ss_approval_history pah1,
fnd_user pth1
WHERE pah1.user_name = pth1.user_name
AND pah1.transaction_history_id = pah.transaction_history_id
AND pth1.employee_id IN (
SELECT pth2.employee_id --, pth2.user_name, approval_history_id
FROM pqh_ss_approval_history pah2,
fnd_user pth2
WHERE pah2.user_name = pth2.user_name
AND pah2.transaction_history_id = pah.transaction_history_id
AND approval_history_id < (
SELECT MIN(approval_history_id)
FROM pqh_ss_approval_history
WHERE transaction_history_id = pah.transaction_history_id
AND user_name = lv_role_name
AND approval_history_id > 0
)
and approval_history_id > 0
MINUS
SELECT number_value
FROM wf_item_attribute_values
WHERE item_type = p_item_Type
AND item_key = p_item_Key
AND name like 'ADDITIONAL_APPROVER_%'
)
);
SELECT ias.notification_id
into ln_rfc_notification_id
FROM WF_ITEM_ACTIVITY_STATUSES IAS
WHERE ias.item_type = p_item_Type
and ias.item_key = p_item_Key
and IAS.ACTIVITY_STATUS = 'NOTIFIED'
and notification_id is not null
and rownum < 2;
select employee_id into lv_role_orig_sys_id from (
SELECT pth.employee_id
FROM pqh_ss_approval_history pah,
fnd_user pth
WHERE pah.user_name = pth.user_name
AND pah.transaction_history_id = p_transaction_id
and approval_history_id > 0
and approval_history_id = (select min(approval_history_id) from pqh_ss_approval_history where USER_NAME = p_rfcRoleName and transaction_history_id = p_transaction_id)
and pah.last_update_date <(select min(last_update_date) from pqh_ss_approval_history where USER_NAME = p_rfcRoleName and transaction_history_id = p_transaction_id
and approval_history_id = (select min(approval_history_id) from pqh_ss_approval_history where USER_NAME = p_rfcRoleName and transaction_history_id = p_transaction_id))
order by pah.last_update_date desc
)
where rownum = 1;
select MAIN_APPRAISER_ID,APPRAISER_PERSON_ID,APPRAISEE_PERSON_ID
into l_main_appraiser_id, l_appraiser_person_id,l_appraisee_person_id
from per_appraisals
where APPRAISAL_ID=l_appraisal_id;
select substr(context,1,instr(context,':',1)-1) itemtype
,substr(context,instr(context,':')+1, (
instr(context,':',instr(context,':')+1 ) - instr(context,':')-1) ) itemkey
into lv_item_type,lv_item_key
from wf_notifications
where notification_id = p_notification_id;
select item_type,item_key
into lv_item_type,lv_item_key
from hr_api_transactions
where transaction_id=p_transaction_id;
select wrpv.display_name
from wf_runnable_processes_v wrpv
where wrpv.item_type = p_item_type
and wrpv.process_name = p_process_name;
is select full_name
from per_all_people_f papf
where papf.person_id = p_person_id
and trunc(sysdate) between effective_start_date
and effective_end_date;
select *
into lr_transaction_rec
from hr_api_transactions
where transaction_id=p_transaction_id;
select hr_workflow_item_key_s.nextval
into p_item_key
from sys.dual;
,p_service_orig_sys_id =>lr_transaction_rec.selected_person_id
,p_service_orig_sys =>'PER' -- need to revisit for role based support
);
select * into lr_hr_api_transaction_rec
from hr_api_transactions
where transaction_id=p_transaction_id;
select * into lr_hr_api_transaction_rec
from hr_api_transactions
where transaction_id=p_transaction_id;
select notification_id
into ln_notification_id
FROM WF_ITEM_ACTIVITY_STATUSES IAS
WHERE ias.item_type = lr_hr_api_transaction_rec.item_type
and ias.item_key = lr_hr_api_transaction_rec.item_key
and ias.activity_status = 'NOTIFIED'
and ias.notification_id is not null
and rownum<=1;
select * into lr_hr_api_transaction_rec
from hr_api_transactions
where transaction_id=p_transaction_id;
hr_transaction_api.update_transaction(
p_transaction_id => p_transaction_id,
p_status => 'Y',
p_transaction_state => null);
procedure update_comments(
p_ntf_id in number,
p_txn_status in varchar2,
p_approval_comments in varchar2)
is
cursor csr_wf_note_exists is
select 1
from wf_notification_attributes wna
where notification_id = p_ntf_id
and NAME = 'WF_NOTE';
end update_comments;
select * into lr_hr_api_transaction_rec
from hr_api_transactions
where transaction_id=p_transaction_id;
hr_transaction_api.update_transaction(
p_transaction_id => p_transaction_id,
p_status => 'Y',
p_transaction_state => null);
update_comments(ln_notification_id,lr_hr_api_transaction_rec.status,p_approval_comments);
hr_transaction_api.update_transaction(
p_transaction_id => p_transaction_id,
p_status => 'Y');
select item_type,item_key
into lv_item_type,lv_item_key
from hr_api_transactions
where transaction_id=p_transaction_id;
select * into lr_hr_api_transaction_rec
from hr_api_transactions
where transaction_id=p_transaction_id;
select status,transaction_state
into lv_status,lv_state
from hr_api_transactions
where transaction_id=p_transaction_id;
procedure updateRejectStatus( p_item_type in varchar2,
p_item_key in varchar2,
p_act_id in number,
funmode in varchar2,
result out nocopy varchar2)
is
-- local variables
c_proc constant varchar2(30) := 'updateRejectStatus';
select *
from wf_user_roles
where role_name = p_role_name;
select transaction_ref_table into l_trans_ref_table
from hr_api_transactions
where item_type = p_item_type and item_key = p_item_key;
select ORIGINAL_RECIPIENT into original_forward_to_user from wf_notifications where notification_id = notification_rec.notification_id;
hr_utility.set_location('calling ame_api6.updateApproveStatus2 with Reject status', 3);
ame_api6.updateApprovalStatus2(applicationIdIn=>c_application_id,
transactionTypeIn =>c_transaction_type,
transactionIdIn=>c_transaction_id,
approvalStatusIn =>ame_util.rejectStatus,
approverNameIn =>l_current_forward_to_username,
itemClassIn => null,
itemIdIn => null,
actionTypeIdIn=> null,
groupOrChainIdIn => null,
occurrenceIn => null,
notificationIn => notification_rec,
forwardeeIn =>ame_util.emptyApproverRecord2,
updateItemIn =>false);
hr_utility.set_location('Error in updateRejectStatus SQLERRM' ||' '||to_char(SQLCODE),10);
hr_utility.set_location('returned from calling ame_api2.updateApprovalStatus2', 8);
updateApprovalHistory( p_item_type=>p_item_type,
p_item_key=>p_item_key,
p_act_id=>p_act_id,
funmode=>funmode,
result=>result);*/
hr_utility.set_location('Error in updateRejectStatus SQLERRM' ||' '||to_char(SQLCODE),30);
end updateRejectStatus;
select lower(meaning) into toString from hr_lookups where LOOKUP_TYPE = 'BEN_TO_FROM' and lookup_code = 'TO';