[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select item_type, item_key
into p_item_type,p_item_key
from wf_items
where user_key=to_char(p_transaction_id)
and rownum<2;
select ias.notification_id notification_id
from wf_item_activity_statuses ias,
wf_notifications ntf
where ias.item_type = itemtype
and ias.item_key =itemkey
and ias.notification_id is not null
and ntf.notification_id = ias.notification_id
and ntf.status='OPEN'
union
select ias.notification_id notification_id
from wf_item_activity_statuses_h ias,
wf_notifications ntf
where ias.notification_id is not null
and ias.item_type = itemtype
and ias.item_key =itemkey
and ntf.notification_id = ias.notification_id
and ntf.status='OPEN' ;
select 1
into dummy
from sys.dual
where exists ( select null
from wf_notifications
where notification_id = nid
and status = 'OPEN'
);
lv_selected_func_prompt fnd_form_functions_vl.USER_FUNCTION_NAME%type;
select * into lr_hr_api_transaction_rec from hr_api_transactions
where transaction_id=ln_transaction_id;
select USER_FUNCTION_NAME into lv_selected_func_prompt
from fnd_form_functions_vl fffv
where fffv.function_id=lr_hr_api_transaction_rec.FUNCTION_ID;
lv_selected_func_prompt:= lv_selected_func_prompt||' - ' ||lr_hr_api_transaction_rec.section_display_name;
l_current_person_id := lr_hr_api_transaction_rec.SELECTED_PERSON_ID;
select decode(
fnd_profile.value('BEN_DISPLAY_EMPLOYEE_NAME')
,'FN',full_name,first_name||' '|| last_name||' '||suffix) FULL_NAME
into l_current_disp_name
from per_all_people_f
where person_id=l_current_person_id
and trunc(sysdate) between effective_start_date and effective_end_date;
SELECT varchar2_value into l_current_disp_name
FROM hr_api_transaction_values
WHERE transaction_step_id IN
(SELECT transaction_step_id
FROM hr_api_transaction_steps
WHERE transaction_id = ln_transaction_id
AND api_name = 'HR_PROCESS_PERSON_SS.PROCESS_API')
AND name = 'P_FULL_NAME'
AND 'NEW' =
(SELECT varchar2_value
FROM hr_api_transaction_values
WHERE transaction_step_id IN
(SELECT transaction_step_id
FROM hr_api_transaction_steps
WHERE transaction_id = ln_transaction_id
AND api_name = 'HR_PROCESS_PERSON_SS.PROCESS_API')
AND name = 'P_ACTION_TYPE');
fnd_message.set_token('SELECTED_PERSON_DISPLAY_NAME',l_current_disp_name,false);
select fffv.user_function_name,hat.section_display_name
into lv_subject,lv_section_display_name
from hr_api_transactions hat,fnd_form_functions_vl fffv
where hat.transaction_id=p_transaction_id
and hat.function_id=fffv.function_id;
select item_type,item_key
into p_itemType,p_itemKey
from wf_items
where user_key=to_char(p_transaction_id)
and item_type=nvl(p_itemType,'HRSFL')
and end_date is null
and rownum<2;
select hr_workflow_item_key_s.nextval
into p_itemKey
from sys.dual;
select * into lr_hr_api_transaction_rec from hr_api_transactions
where transaction_id=p_transaction_id;
'HR_SFL_DELETE_LINK_ATTR',
lv_relaunchSFLLink||'&'||'pAction=DELETE');
function getSFLStatusForUpdate(
p_currentTxnStatus in varchar2,
p_proposedTxnStatus in varchar2) RETURN VARCHAR2
is
--local variables
c_updateStatus hr_api_transactions.status%type;
c_updateStatus:= 'YS';
c_updateStatus:='S';
c_updateStatus:= 'YS';
c_updateStatus:='RIS';
c_updateStatus:='ROS';
c_updateStatus:=p_proposedTxnStatus;
return c_updateStatus;
end getSFLStatusForUpdate;
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 process_activity
into ln_activity_id
from
(select process_activity
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_type
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 item_type, item_key
into lv_sfl_item_type,lv_sfl_item_key
from wf_items
where user_key=to_char(p_transaction_id)
--and parent_item_type=nvl(p_approvalItemType,parent_item_type)
--and parent_item_key=nvl(p_approvalItemKey,parent_item_key)
and rownum<2;
select item_type, item_key
into lv_sfl_item_type,lv_sfl_item_key
from wf_items
where user_key=to_char(p_transaction_id)
and rownum<2;