[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 distinct activity_name,activity_display_name
FROM
(
SELECT a.name activity_name,
a.display_name activity_display_name
FROM wf_activities_vl a
,wf_item_activity_statuses ias
,wf_process_activities pa
WHERE ias.item_type = p_item_type
AND ias.item_key = p_item_key
AND ias.process_activity = p_actid
AND ias.process_activity = pa.instance_id
AND pa.activity_name = a.name
AND pa.activity_item_type = a.item_type
UNION ALL
SELECT a.name activity_name,
a.display_name activity_display_name
FROM wf_activities_vl a
,wf_item_activity_statuses_h iash
,wf_process_activities pa
WHERE iash.item_type = p_item_type
AND iash.item_key = p_item_key
AND iash.process_activity = p_actid
AND iash.process_activity = pa.instance_id
AND pa.activity_name = a.name
AND pa.activity_item_type = a.item_type
);
select wiasv.activity_result_code
from wf_item_activity_statuses wiasv
where wiasv.process_activity = p_actid
and wiasv.item_type = p_item_type
and wiasv.item_key = p_item_key
order by wiasv.end_date desc;
/*select wiasv.activity_result_code
from wf_item_activity_statuses_v wiasv
where wiasv.activity_id = p_actid
and wiasv.item_type = p_item_type
and wiasv.item_key = p_item_key
order by wiasv.activity_end_date desc;*/
select wrpv.display_name
from wf_runnable_processes_v wrpv
where wrpv.item_type = p_item_type
and wrpv.process_name = p_process_name;
select hr_workflow_item_key_s.nextval
into l_item_key
from sys.dual;
select 1
from wf_item_activity_statuses wias
,wf_process_activities wpa1
where wpa1.process_item_type = p_item_type
and wpa1.process_name = g_wf_root_process
and wpa1.process_version =
(select max(wpa2.process_version)
from wf_process_activities wpa2
where wpa2.process_item_type = p_item_type
and wpa2.process_name = g_wf_root_process)
and wias.process_activity = wpa1.instance_id
and wias.item_type = p_item_type
and wias.item_key = p_item_key
and wias.activity_status = g_wf_root_process_active;
/*select wiasv.activity_id
from wf_item_activity_statuses_v wiasv
where wiasv.item_type = p_item_type
and wiasv.item_key = p_item_key
and wiasv.activity_type_code = g_wf_function
and wiasv.activity_status_code = g_wf_activity_notified
and exists
(select 1
from wf_activity_attr_values waav
where waav.process_activity_id = wiasv.activity_id
and waav.name in (g_hr_activity_type, g_hr_activity_type_value));*/
SELECT process_activity activity_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 = g_wf_activity_notified
and exists
(select 1
from wf_activity_attr_values waav
where waav.process_activity_id = ias.process_activity
and waav.name in (g_hr_activity_type, g_hr_activity_type_value));
select waav.name
,waav.text_value
from wf_activity_attr_values waav
where waav.process_activity_id = c_process_activity_id
and waav.name in (g_hr_activity_type, g_hr_activity_type_value);
SELECT /*+ ordered */
process.item_key
FROM wf_process_activities activity,
wf_item_activity_statuses process,
wf_item_activity_statuses result,
wf_item_attribute_values attribute
WHERE activity.activity_name = p_process_name
AND activity.activity_item_type = p_item_type
AND activity.process_item_type = p_item_type
AND activity.instance_id = process.process_activity
AND process.activity_status = 'ACTIVE'
AND process.item_type = p_item_type
AND process.item_key = attribute.item_key
AND attribute.item_type = p_item_type
AND attribute.name = 'CURRENT_PERSON_ID'
AND attribute.number_value = p_current_person_id
and result.item_type = p_item_type
and result.item_key = process.item_key
and result.activity_result_code = p_activity_result_code;
select process.item_key
from wf_item_activity_statuses process
,wf_item_attribute_values attribute
,wf_process_activities activity
where activity.activity_name = p_process_name
and activity.process_item_type = p_item_type
and activity.activity_item_type||'' = p_item_type
and activity.instance_id = process.process_activity
and process.activity_status||'' = 'ACTIVE'
and process.item_type = p_item_type
and process.item_key = attribute.item_key
and attribute.item_type = p_item_type
and attribute.name = 'CURRENT_PERSON_ID'
and attribute.number_value = p_current_person_id;
select process.item_key
from wf_item_activity_statuses process
,wf_item_attribute_values attribute
,wf_process_activities activity
,wf_item_activity_statuses result
where activity.activity_name = p_process_name
and activity.process_item_type = p_item_type
and activity.activity_item_type = p_item_type
and activity.instance_id = process.process_activity
and process.activity_status = 'ACTIVE'
and process.item_type = p_item_type
and process.item_key = attribute.item_key
and attribute.item_type = process.item_type
and attribute.name = 'CURRENT_PERSON_ID'
and attribute.number_value = p_current_person_id
and result.item_type = p_item_type
and result.item_key = process.item_key
and result.activity_result_code = p_activity_result_code;
/*select process.item_key
from wf_item_activity_statuses_v process
where process.activity_name = p_process_name
and process.activity_status_code = 'ACTIVE'
and process.item_type = p_item_type
and process.item_key in (select attribute.item_key
from wf_item_attribute_values attribute
where attribute.item_type = p_item_type
and attribute.name = 'CURRENT_PERSON_ID'
and attribute.number_value = p_current_person_id);*/
select 1
from hr_api_transaction_steps
where item_type = p_item_type
and item_key = l_active_item_key;
select activity.instance_id activity_id
from wf_process_activities activity,
wf_item_activity_statuses process
where activity.activity_name = p_activity_name
and activity.process_item_type = p_item_type
and activity.activity_item_type = p_item_type
and activity.instance_id = process.process_activity
and process.item_type = p_item_type
and process.item_key = l_active_item_key
and process.activity_status = 'COMPLETE';
/* select distinct process.activity_id
from wf_item_activity_statuses_v process
where process.item_type = p_item_type
and process.item_key = l_active_item_key
and process.activity_name = p_activity_name; */
SELECT /*+ ordered */
process.item_key
FROM wf_process_activities activity,
wf_item_activity_statuses process,
wf_item_attribute_values attribute
WHERE activity.activity_name = p_process_name
AND activity.activity_item_type = p_item_type
AND activity.instance_id = process.process_activity
AND process.activity_status = 'ACTIVE'
AND process.item_type = p_item_type
AND process.item_key = attribute.item_key
AND attribute.item_type = p_item_type
AND attribute.name = 'CURRENT_PERSON_ID'
AND attribute.number_value = p_current_person_id;
select process.item_key
from wf_item_attribute_values attribute,
wf_process_activities activity,
wf_item_activity_statuses process
where activity.activity_name = p_process_name
-- and activity.process_item_type = p_item_type
-- and activity.activity_item_type||'' = p_item_type
and activity.instance_id = process.process_activity
and process.activity_status = 'ACTIVE'
and process.item_type = p_item_type
and process.item_key = attribute.item_key
and attribute.item_type = p_item_type
and attribute.name = 'CURRENT_PERSON_ID'
and attribute.number_value = p_current_person_id;
select process.item_key
from wf_item_activity_statuses process
,wf_item_attribute_values attribute
,wf_process_activities activity
where activity.activity_name = p_process_name
and activity.process_item_type = p_item_type
and activity.activity_item_type = p_item_type
and activity.instance_id = process.process_activity
and process.activity_status = 'ACTIVE'
and process.item_type = p_item_type
and process.item_key = attribute.item_key
and attribute.item_type = p_item_type
and attribute.name = 'CURRENT_PERSON_ID'
and attribute.number_value = p_current_person_id;
/*select process.item_key
from wf_item_activity_statuses_v process
where process.activity_name = p_process_name
and process.activity_status_code = 'ACTIVE'
and process.item_type = p_item_type
and process.item_key in (select attribute.item_key
from wf_item_attribute_values attribute
where attribute.item_type = p_item_type
and attribute.name = 'CURRENT_PERSON_ID'
and attribute.number_value = p_current_person_id);*/
select 1
from hr_api_transaction_steps
where item_type = p_item_type
and item_key = l_active_item_key;
select activity.instance_id activity_id
from wf_process_activities activity,
wf_item_activity_statuses process
where activity.activity_name = p_activity_name
and activity.process_item_type = p_item_type
and activity.activity_item_type = p_item_type
and activity.instance_id = process.process_activity
and process.item_type = p_item_type
and process.item_key = l_active_item_key
and process.activity_status = 'COMPLETE';
/*select distinct process.activity_id
from wf_item_activity_statuses_v process
where process.item_type = p_item_type
and process.item_key = l_active_item_key
and process.activity_name = p_activity_name; */
select process.item_key
from wf_item_activity_statuses process
,wf_item_attribute_values attribute
,wf_process_activities activity
,wf_item_activity_statuses result
where activity.activity_name = p_process_name
and activity.process_item_type = p_item_type
and activity.activity_item_type = p_item_type
and activity.instance_id = process.process_activity
and process.activity_status = 'ACTIVE'
and process.item_type = p_item_type
and process.item_key = attribute.item_key
and attribute.item_type = process.item_type
and attribute.name = 'CURRENT_PERSON_ID'
and attribute.number_value = p_current_person_id
and result.item_type = process.item_type
and result.item_key = process.item_key
and result.activity_result_code = p_result_code;
select process.item_key
from wf_item_activity_statuses process
,wf_item_attribute_values attribute
,wf_process_activities activity
where activity.activity_name = p_process_name
and activity.process_item_type = p_item_type
and activity.activity_item_type = p_item_type
and activity.instance_id = process.process_activity
and process.activity_status = 'ACTIVE'
and process.item_type = p_item_type
and process.item_key = attribute.item_key
and attribute.item_type = process.item_type
and attribute.name = 'CURRENT_PERSON_ID'
and attribute.number_value = p_current_person_id;
select transaction_step_id, activity_id
from hr_api_transaction_steps
where item_type = p_item_type
and item_key = csr_p_item_key
and upper(api_name) = csr_p_api_name;
select step.transaction_step_id, step.activity_id
from hr_api_transaction_steps step
,hr_api_transaction_values value
where item_type = p_item_type
and item_key = csr_p_item_key
and upper(api_name) = csr_p_api_name
and step.transaction_step_id = value.transaction_step_id
and value.name = 'P_PRIMARY_FLAG'
and value.varchar2_value = csr_p_primary_flag;
l_transaction_status_to_delete varchar2(10);
l_delete_transaction boolean default false;
select wias.item_key
from wf_item_activity_statuses wias,
wf_activity_attr_values waav,
wf_process_activities wpa,
wf_item_attribute_values wiav
where wias.item_type = itemtype
and wias.activity_status = 'NOTIFIED'
and wpa.instance_id = wias.process_activity
and wpa.instance_id = waav.process_activity_id
and waav.name = 'HR_ACTIVITY_TYPE'
and wiav.item_key = wias.item_key
and wiav.item_type = wias.item_type
and wiav.name = 'SESSION_ID'
and not exists
(select 1
from icx_sessions s
where s.session_id = wiav.number_value
and s.disabled_flag = 'N');
select * from (select a.transaction_id transaction_id,
s.text_value status,
a.item_type item_type,
a.item_key item_key
from (select transaction_id,
status,
nvl(item_type,hr_workflow_service.getItemType(t.transaction_id)) item_type,
nvl(item_key,hr_workflow_service.getItemKey(t.transaction_id)) item_key
from hr_api_transactions t
where t.last_update_date <= sysdate - c_transaction_age
and t.status not in ('Y', 'YS','W')
and t.transaction_ref_table <> 'PER_APPRAISALS'
) a, -- bug 3635925 , bug 5357274, bug 5990955
wf_item_attribute_values s
where a.item_type = itemtype
and a.item_type = s.item_type
and a.item_key = s.item_key
and s.name = 'TRAN_SUBMIT')
where status = nvl(c_status,status) ;
SELECT t.item_type, t.item_key, t.transaction_id
FROM hr_api_transactions t, per_appraisals a
WHERE t.transaction_ref_table = 'PER_APPRAISALS'
AND t.last_update_date <= sysdate - c_transaction_age
AND t.transaction_ref_id = a.appraisal_id
AND a.appraisal_system_status IN ('DELETED','COMPLETED');
select hats.transaction_id
from hr_api_transaction_steps hats
where hats.item_type = itemtype
and hats.item_key = l_item_key;
/* SELECT distinct hats.transaction_id
FROM hr_api_transaction_steps hats
WHERE NOT EXISTS (SELECT 'Y'
FROM wf_items wi
WHERE wi.item_type = hats.item_type
AND wi.item_key = hats.item_key);
SELECT hat.transaction_id
FROM hr_api_transactions hat
WHERE NOT EXISTS (SELECT 'Y'
FROM wf_items wi
WHERE wi.item_type = nvl(hat.item_type,hr_workflow_service.getItemType(hat.transaction_id))
AND wi.item_key = nvl(hat.item_key,hr_workflow_service.getItemKey(hat.transaction_id))
)
AND Not Exists( select 'Y' from wf_items w --fix for bug 6121860
where w.item_type = 'HRSFL'
and w.user_key = hat.transaction_id)
and hat.item_key is not null;
select wi.item_key
from wf_items wi , wf_item_attribute_values av, icx_sessions s
where wi.item_type= itemtype
and trunc(wi.begin_date) <= trunc(sysdate) --fix for bug 6642996
and wi.end_date is null
and av.item_type = wi.item_type
and av.item_key = wi.item_key
and av.name = 'SESSION_ID'
and av.number_value = s.session_id(+)
and s.disabled_flag(+) = 'Y'
and (
(wi.item_key) not in (
select t1.item_key
from hr_api_transactions t1
where wi.item_type = t1.item_type
and wi.item_key = t1.item_key
and t1.item_type = itemtype
)
and (wi.item_key) not in (
select ts.item_key
from hr_api_transaction_steps ts
where ts.item_type = wi.item_type
and ts.item_key = wi.item_key
and ts.item_type = itemtype
)
);
select wi.item_key
from wf_items wi
where wi.item_type = itemtype
and wi.begin_date <= trunc(sysdate)
and wi.end_date is null
and not exists (select 'e' from hr_api_transactions t
where
t.item_type is not null
and t.item_key is not null
and wi.item_type = t.item_type
and wi.item_key = t.item_key
)
and not exists (select 'e' from hr_api_transactions t, hr_api_transaction_steps ts
where
t.item_type is null
and t.item_key is null
and t.transaction_id = ts.transaction_id
and wi.item_type = ts.item_type
and wi.item_key = ts.item_key
and ts.item_type is not null
and ts.item_key is not null
)
and exists (select 'e' from wf_item_attribute_values av, icx_sessions s
where av.item_type = wi.item_type
and av.item_key = wi.item_key
and av.name = 'SESSION_ID'
and av.number_value = s.session_id(+)
and s.disabled_flag(+) = 'Y');
select hat.transaction_ref_id,
hat.assignment_id,
iof.offer_status
from hr_api_transactions hat,
irc_offers iof
where hat.transaction_ref_table='IRC_OFFERS' and
hat.transaction_ref_id = iof.offer_id and
hat.transaction_id = c_transaction_id and
iof.offer_status in ('PENDING','CORRECTION');
select wi.item_key
from wf_items wi
where wi.item_type = 'HRSFL'
and not exists (select transaction_id from hr_api_transactions
where transaction_id = wi.user_key);
l_transaction_status_to_delete := wf_engine.getitemattrText
(itemtype => itemtype
,itemkey => itemkey
,aname => 'HR_TRAN_STAT_FOR_DEL_ATTR');
if(l_transaction_status_to_delete='ALL') then
c_status := null;
c_status := l_transaction_status_to_delete;
select TEXT_VALUE
into l_transaction_status
from WF_ITEM_ATTRIBUTE_VALUES
where ITEM_TYPE = itemtype
and ITEM_KEY = rec.item_key
and NAME = 'TRAN_SUBMIT';
l_delete_transaction:= false;
l_delete_transaction := false;
l_delete_transaction := true;
if(l_delete_transaction) then -- delete transaction which explicitly
-- identified for delete.
-- First abort the WF process for this transaction record.
BEGIN -- Block to 'abort' the WF process
wf_engine.abortprocess(itemtype => itemtype
,itemkey => rec.item_key
,result => 'eng_force');
l_delete_transaction:= false;
l_delete_transaction := false;
elsif(l_transaction_status_to_delete='ALL') then
l_delete_transaction := true;
elsif (l_transaction_status_to_delete=l_transaction_status) then
l_delete_transaction := true;
if(l_delete_transaction) then -- delete transaction which explicitly
-- identified for delete.
-- First abort the WF process for this transaction record.
BEGIN -- Block to 'abort' the WF process
wf_engine.abortprocess(itemtype => itemtype
,itemkey => rec.item_key
,result => 'eng_force');
select hr_workflow_item_key_s.nextval
into l_item_key
from sys.dual;
select ts.item_type
into getItemType.l_item_type
from hr_api_transaction_steps ts
where ts.transaction_id=getItemType.p_transaction_id
and ts.item_type is not null and rownum <=1;
select ts.item_key
into getItemkey.l_item_key
from hr_api_transaction_steps ts
where getItemkey.p_transaction_id = ts.transaction_id
and ts.item_key is not null and rownum <=1;