[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select WAAV.PROCESS_ACTIVITY_ID, WAAV.NAME, WAAV.VALUE_TYPE,
WAAV.TEXT_VALUE, WAAV.NUMBER_VALUE, WAAV.DATE_VALUE
from WF_ACTIVITY_ATTR_VALUES WAAV
where WAAV.PROCESS_ACTIVITY_ID = c_actid
and WAAV.NAME = c_aname;
select sys_context('USERENV','CURRENT_SCHEMA')
into wf_engine.schema
from sys.dual;
insert into WF_ITEM_ATTRIBUTE_VALUES (
ITEM_TYPE,
ITEM_KEY,
NAME,
TEXT_VALUE,
NUMBER_VALUE,
DATE_VALUE
) values (
itemtype,
itemkey,
aname,
AddItemAttr.text_value,
AddItemAttr.number_value,
AddItemAttr.date_value
);
insert into WF_ITEM_ATTRIBUTE_VALUES (
ITEM_TYPE,
ITEM_KEY,
NAME,
TEXT_VALUE
) values (
itemtype,
itemkey,
aname(arrayIndex),
avalue(arrayIndex)
);
insert into WF_ITEM_ATTRIBUTE_VALUES (
ITEM_TYPE,
ITEM_KEY,
NAME,
NUMBER_VALUE
) values (
itemtype,
itemkey,
aname(arrayIndex),
avalue(arrayIndex)
);
insert into WF_ITEM_ATTRIBUTE_VALUES (
ITEM_TYPE,
ITEM_KEY,
NAME,
DATE_VALUE
) values (
itemtype,
itemkey,
aname(arrayIndex),
avalue(arrayIndex)
);
select WIA.ITEM_TYPE, WIA.NAME, WIA.TYPE, WIA.SUBTYPE, WIA.FORMAT,
WIA.TEXT_DEFAULT, WIA.NUMBER_DEFAULT, WIA.DATE_DEFAULT
into WF_CACHE.ItemAttributes(wiaIND)
from WF_ITEM_ATTRIBUTES WIA
where WIA.ITEM_TYPE = itemtype
and WIA.NAME = aname;
SELECT name
INTO tvalue
FROM wf_role_lov_vl
WHERE upper(display_name) = upper(avalue)
AND rownum = 1;
update WF_ITEM_ATTRIBUTE_VALUES set
TEXT_VALUE = tvalue
where ITEM_TYPE = itemtype
and ITEM_KEY = itemkey
and NAME = aname;
insert into WF_ITEM_ATTRIBUTE_VALUES (ITEM_TYPE, ITEM_KEY, NAME, TEXT_VALUE)
select
SetItemAttrText.itemtype,
SetItemAttrText.itemkey,
SetItemAttrText.aname,
SetItemAttrText.avalue
from WF_ITEM_ATTRIBUTES WIA
where WIA.ITEM_TYPE = SetItemAttrText.itemtype
and WIA.NAME = SetItemAttrText.aname;
update WF_ITEM_ATTRIBUTE_VALUES set
TEXT_VALUE = p_avalue
where ITEM_TYPE = p_itemtype
and ITEM_KEY = p_itemkey
and NAME = p_aname;
insert into WF_ITEM_ATTRIBUTE_VALUES (ITEM_TYPE, ITEM_KEY, NAME, TEXT_VALUE)
select
SetItemAttrText2.p_itemtype,
SetItemAttrText2.p_itemkey,
SetItemAttrText2.p_aname,
SetItemAttrText2.p_avalue
from WF_ITEM_ATTRIBUTES WIA
where WIA.ITEM_TYPE = SetItemAttrText2.p_itemtype
and WIA.NAME = SetItemAttrText2.p_aname;
select WIA.ITEM_TYPE, WIA.NAME, WIA.TYPE, WIA.SUBTYPE, WIA.FORMAT,
WIA.TEXT_DEFAULT, WIA.NUMBER_DEFAULT, WIA.DATE_DEFAULT
into WF_CACHE.ItemAttributes(wiaIND)
from WF_ITEM_ATTRIBUTES WIA
where WIA.ITEM_TYPE = itemtype
and WIA.NAME = aname;
update WF_ITEM_ATTRIBUTE_VALUES set
NUMBER_VALUE = avalue
where ITEM_TYPE = itemtype
and ITEM_KEY = itemkey
and NAME = aname;
insert into WF_ITEM_ATTRIBUTE_VALUES (ITEM_TYPE, ITEM_KEY, NAME, NUMBER_VALUE)
select
SetItemAttrNumber.itemtype,
SetItemAttrNumber.itemkey,
SetItemAttrNumber.aname,
SetItemAttrNumber.avalue
from WF_ITEM_ATTRIBUTES WIA
where WIA.ITEM_TYPE = SetItemAttrNumber.itemtype
and WIA.NAME = SetItemAttrNumber.aname;
update WF_ITEM_ATTRIBUTE_VALUES set
DATE_VALUE = avalue
where ITEM_TYPE = itemtype
and ITEM_KEY = itemkey
and NAME = aname;
insert into WF_ITEM_ATTRIBUTE_VALUES (ITEM_TYPE, ITEM_KEY, NAME, DATE_VALUE)
select
SetItemAttrDate.itemtype,
SetItemAttrDate.itemkey,
SetItemAttrDate.aname,
SetItemAttrDate.avalue
from WF_ITEM_ATTRIBUTES WIA
where WIA.ITEM_TYPE = SetItemAttrDate.itemtype
and WIA.NAME = SetItemAttrDate.aname;
update WF_ITEM_ATTRIBUTE_VALUES set
EVENT_VALUE = SetItemAttrEvent.event
where ITEM_TYPE = SetItemAttrEvent.itemtype
and ITEM_KEY = SetItemAttrEvent.itemkey
and NAME = SetItemAttrEvent.name;
insert into WF_ITEM_ATTRIBUTE_VALUES (ITEM_TYPE, ITEM_KEY, NAME, EVENT_VALUE)
select
SetItemAttrEvent.itemtype,
SetItemAttrEvent.itemkey,
SetItemAttrEvent.name,
SetItemAttrEvent.event
from WF_ITEM_ATTRIBUTES WIA
where WIA.ITEM_TYPE = SetItemAttrEvent.itemtype
and WIA.NAME = SetItemAttrEvent.name;
update WF_ITEM_ATTRIBUTE_VALUES set
TEXT_VALUE = avalue(arrayIndex)
where ITEM_TYPE = itemtype
and ITEM_KEY = itemkey
and NAME = aname(arrayIndex);
insert into WF_ITEM_ATTRIBUTE_VALUES
(ITEM_TYPE, ITEM_KEY, NAME, TEXT_VALUE)
select itemtype, itemkey, aname(arrayIndex), avalue(arrayIndex)
from WF_ITEM_ATTRIBUTES WIA
where
WIA.ITEM_TYPE = itemtype
and
WIA.NAME = aname(arrayIndex)
and not exists (select 1 from WF_ITEM_ATTRIBUTE_VALUES WIAV
where WIAV.item_type=itemtype
and WIAV.item_key=itemkey
and WIAV.NAME=aname(arrayIndex));
update WF_ITEM_ATTRIBUTE_VALUES set
NUMBER_VALUE = avalue(arrayIndex)
where ITEM_TYPE = itemtype
and ITEM_KEY = itemkey
and NAME = aname(arrayIndex);
insert into WF_ITEM_ATTRIBUTE_VALUES
(ITEM_TYPE, ITEM_KEY, NAME, NUMBER_VALUE)
select itemtype, itemkey, aname(arrayIndex), avalue(arrayIndex)
from WF_ITEM_ATTRIBUTES WIA
where
WIA.ITEM_TYPE = itemtype
and
WIA.NAME = aname(arrayIndex)
and not exists (select 1 from WF_ITEM_ATTRIBUTE_VALUES WIAV
where WIAV.item_type=itemtype
and WIAV.item_key=itemkey
and WIAV.NAME=aname(arrayIndex));
update WF_ITEM_ATTRIBUTE_VALUES set
DATE_VALUE = avalue(arrayIndex)
where ITEM_TYPE = itemtype
and ITEM_KEY = itemkey
and NAME = aname(arrayIndex);
insert into WF_ITEM_ATTRIBUTE_VALUES
(ITEM_TYPE, ITEM_KEY, NAME, DATE_VALUE)
select itemtype, itemkey, aname(arrayIndex), avalue(arrayIndex)
from WF_ITEM_ATTRIBUTES WIA
where
WIA.ITEM_TYPE = itemtype
and
WIA.NAME = aname(arrayIndex)
and not exists (select 1 from WF_ITEM_ATTRIBUTE_VALUES WIAV
where WIAV.item_type=itemtype
and WIAV.item_key=itemkey
and WIAV.NAME=aname(arrayIndex));
select WIA.ITEM_TYPE, WIA.NAME, WIA.TYPE, WIA.SUBTYPE, WIA.FORMAT,
WIA.TEXT_DEFAULT, WIA.NUMBER_DEFAULT, WIA.DATE_DEFAULT
into WF_CACHE.ItemAttributes(wiaIND)
from WF_ITEM_ATTRIBUTES WIA
where WIA.ITEM_TYPE = itemtype
and WIA.NAME = aname;
select WIA.ITEM_TYPE, WIA.NAME, WIA.TYPE, WIA.SUBTYPE, WIA.FORMAT,
WIA.TEXT_DEFAULT, WIA.NUMBER_DEFAULT, WIA.DATE_DEFAULT
into WF_CACHE.ItemAttributes(wiaIND)
from WF_ITEM_ATTRIBUTES WIA
where WIA.ITEM_TYPE = itemtype
and WIA.NAME = aname;
select TEXT_VALUE
into lvalue
from WF_ITEM_ATTRIBUTE_VALUES
where ITEM_TYPE = itemtype
and ITEM_KEY = itemkey
and NAME = aname;
select TEXT_DEFAULT
into lvalue
from WF_ITEM_ATTRIBUTES
where ITEM_TYPE = itemtype
and NAME = aname;
select NUMBER_VALUE
into lvalue
from WF_ITEM_ATTRIBUTE_VALUES
where ITEM_TYPE = itemtype
and ITEM_KEY = itemkey
and NAME = aname;
select NUMBER_DEFAULT
into lvalue
from WF_ITEM_ATTRIBUTES
where ITEM_TYPE = itemtype
and NAME = aname;
select DATE_VALUE
into lvalue
from WF_ITEM_ATTRIBUTE_VALUES
where ITEM_TYPE = itemtype
and ITEM_KEY = itemkey
and NAME = aname;
select DATE_DEFAULT
into lvalue
from WF_ITEM_ATTRIBUTES
where ITEM_TYPE = itemtype
and NAME = aname;
select WIA.ITEM_TYPE, WIA.NAME, WIA.TYPE, WIA.SUBTYPE, WIA.FORMAT,
WIA.TEXT_DEFAULT, WIA.NUMBER_DEFAULT, WIA.DATE_DEFAULT
into WF_CACHE.ItemAttributes(wiaIND)
from WF_ITEM_ATTRIBUTES WIA
where WIA.ITEM_TYPE = GetItemAttrClob.itemtype
and WIA.NAME = GetItemAttrClob.aname;
select EVENT_VALUE
into lvalue
from WF_ITEM_ATTRIBUTE_VALUES
where ITEM_TYPE = GetItemAttrEvent.itemtype
and ITEM_KEY = GetItemAttrEvent.itemkey
and NAME = GetItemAttrEvent.name;
select null into l_value
from WF_ITEM_ATTRIBUTES WIA
where WIA.ITEM_TYPE = GetItemAttrEvent.itemtype
and WIA.NAME = GetItemAttrEvent.name;
select WA.ITEM_TYPE, WA.NAME, WA.VERSION, WA.TYPE, WA.RERUN,
WA.EXPAND_ROLE, WA.COST, WA.ERROR_ITEM_TYPE, WA.ERROR_PROCESS,
WA.FUNCTION, WA.FUNCTION_TYPE,
WA.EVENT_NAME, WA.MESSAGE, WA.BEGIN_DATE,
WA.END_DATE, WA.DIRECTION, WAA.ACTIVITY_ITEM_TYPE,
WAA.ACTIVITY_NAME, WAA.ACTIVITY_VERSION, WAA.NAME, WAA.TYPE,
WAA.SUBTYPE, WAA.FORMAT, WPA.PROCESS_ITEM_TYPE, WPA.PROCESS_NAME,
WPA.PROCESS_VERSION, WPA.ACTIVITY_ITEM_TYPE, WPA.ACTIVITY_NAME,
WPA.INSTANCE_ID, WPA.INSTANCE_LABEL, WPA.PERFORM_ROLE,
WPA.PERFORM_ROLE_TYPE, WPA.START_END, WPA.DEFAULT_RESULT
into WF_CACHE.Activities(waIND).ITEM_TYPE,
WF_CACHE.Activities(waIND).NAME,
WF_CACHE.Activities(waIND).VERSION,
WF_CACHE.Activities(waIND).TYPE,
WF_CACHE.Activities(waIND).RERUN,
WF_CACHE.Activities(waIND).EXPAND_ROLE,
WF_CACHE.Activities(waIND).COST,
WF_CACHE.Activities(waIND).ERROR_ITEM_TYPE,
WF_CACHE.Activities(waIND).ERROR_PROCESS,
WF_CACHE.Activities(waIND).FUNCTION,
WF_CACHE.Activities(waIND).FUNCTION_TYPE,
WF_CACHE.Activities(waIND).EVENT_NAME,
WF_CACHE.Activities(waIND).MESSAGE,
WF_CACHE.Activities(waIND).BEGIN_DATE,
WF_CACHE.Activities(waIND).END_DATE,
WF_CACHE.Activities(waIND).DIRECTION,
WF_CACHE.ActivityAttributes(waaIND).ACTIVITY_ITEM_TYPE,
WF_CACHE.ActivityAttributes(waaIND).ACTIVITY_NAME,
WF_CACHE.ActivityAttributes(waaIND).ACTIVITY_VERSION,
WF_CACHE.ActivityAttributes(waaIND).NAME,
WF_CACHE.ActivityAttributes(waaIND).TYPE,
WF_CACHE.ActivityAttributes(waaIND).SUBTYPE,
WF_CACHE.ActivityAttributes(waaIND).FORMAT,
WF_CACHE.ProcessActivities(actid).PROCESS_ITEM_TYPE,
WF_CACHE.ProcessActivities(actid).PROCESS_NAME,
WF_CACHE.ProcessActivities(actid).PROCESS_VERSION,
WF_CACHE.ProcessActivities(actid).ACTIVITY_ITEM_TYPE,
WF_CACHE.ProcessActivities(actid).ACTIVITY_NAME,
WF_CACHE.ProcessActivities(actid).INSTANCE_ID,
WF_CACHE.ProcessActivities(actid).INSTANCE_LABEL,
WF_CACHE.ProcessActivities(actid).PERFORM_ROLE,
WF_CACHE.ProcessActivities(actid).PERFORM_ROLE_TYPE,
WF_CACHE.ProcessActivities(actid).START_END,
WF_CACHE.ProcessActivities(actid).DEFAULT_RESULT
from WF_ACTIVITY_ATTRIBUTES WAA, WF_PROCESS_ACTIVITIES WPA,
WF_ACTIVITIES WA
where WPA.INSTANCE_ID = actid
and WA.ITEM_TYPE = WPA.ACTIVITY_ITEM_TYPE
and WA.NAME = WPA.ACTIVITY_NAME
and actdate >= WA.BEGIN_DATE
and actdate < NVL(WA.END_DATE, actdate+1)
and WAA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
and WAA.ACTIVITY_NAME = WA.NAME
and WAA.ACTIVITY_VERSION = WA.VERSION
and WAA.NAME = aname;
select WA.ITEM_TYPE, WA.NAME, WA.VERSION, WA.TYPE, WA.RERUN,
WA.EXPAND_ROLE, WA.COST, WA.ERROR_ITEM_TYPE, WA.ERROR_PROCESS,
WA.FUNCTION, WA.FUNCTION_TYPE,
WA.EVENT_NAME, WA.MESSAGE, WA.BEGIN_DATE,
WA.END_DATE, WA.DIRECTION, WAA.ACTIVITY_ITEM_TYPE,
WAA.ACTIVITY_NAME, WAA.ACTIVITY_VERSION, WAA.NAME, WAA.TYPE,
WAA.SUBTYPE, WAA.FORMAT, WPA.PROCESS_ITEM_TYPE, WPA.PROCESS_NAME,
WPA.PROCESS_VERSION, WPA.ACTIVITY_ITEM_TYPE, WPA.ACTIVITY_NAME,
WPA.INSTANCE_ID, WPA.INSTANCE_LABEL, WPA.PERFORM_ROLE,
WPA.PERFORM_ROLE_TYPE, WPA.START_END, WPA.DEFAULT_RESULT
into WF_CACHE.Activities(waIND).ITEM_TYPE,
WF_CACHE.Activities(waIND).NAME,
WF_CACHE.Activities(waIND).VERSION,
WF_CACHE.Activities(waIND).TYPE,
WF_CACHE.Activities(waIND).RERUN,
WF_CACHE.Activities(waIND).EXPAND_ROLE,
WF_CACHE.Activities(waIND).COST,
WF_CACHE.Activities(waIND).ERROR_ITEM_TYPE,
WF_CACHE.Activities(waIND).ERROR_PROCESS,
WF_CACHE.Activities(waIND).FUNCTION,
WF_CACHE.Activities(waIND).FUNCTION_TYPE,
WF_CACHE.Activities(waIND).EVENT_NAME,
WF_CACHE.Activities(waIND).MESSAGE,
WF_CACHE.Activities(waIND).BEGIN_DATE,
WF_CACHE.Activities(waIND).END_DATE,
WF_CACHE.Activities(waIND).DIRECTION,
WF_CACHE.ActivityAttributes(waaIND).ACTIVITY_ITEM_TYPE,
WF_CACHE.ActivityAttributes(waaIND).ACTIVITY_NAME,
WF_CACHE.ActivityAttributes(waaIND).ACTIVITY_VERSION,
WF_CACHE.ActivityAttributes(waaIND).NAME,
WF_CACHE.ActivityAttributes(waaIND).TYPE,
WF_CACHE.ActivityAttributes(waaIND).SUBTYPE,
WF_CACHE.ActivityAttributes(waaIND).FORMAT,
WF_CACHE.ProcessActivities(actid).PROCESS_ITEM_TYPE,
WF_CACHE.ProcessActivities(actid).PROCESS_NAME,
WF_CACHE.ProcessActivities(actid).PROCESS_VERSION,
WF_CACHE.ProcessActivities(actid).ACTIVITY_ITEM_TYPE,
WF_CACHE.ProcessActivities(actid).ACTIVITY_NAME,
WF_CACHE.ProcessActivities(actid).INSTANCE_ID,
WF_CACHE.ProcessActivities(actid).INSTANCE_LABEL,
WF_CACHE.ProcessActivities(actid).PERFORM_ROLE,
WF_CACHE.ProcessActivities(actid).PERFORM_ROLE_TYPE,
WF_CACHE.ProcessActivities(actid).START_END,
WF_CACHE.ProcessActivities(actid).DEFAULT_RESULT
from WF_ACTIVITY_ATTRIBUTES WAA, WF_PROCESS_ACTIVITIES WPA,
WF_ACTIVITIES WA
where WPA.INSTANCE_ID = actid
and WA.ITEM_TYPE = WPA.ACTIVITY_ITEM_TYPE
and WA.NAME = WPA.ACTIVITY_NAME
and actdate >= WA.BEGIN_DATE
and actdate < NVL(WA.END_DATE, actdate+1)
and WAA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
and WAA.ACTIVITY_NAME = WA.NAME
and WAA.ACTIVITY_VERSION = WA.VERSION
and WAA.NAME = aname;
select WA.ITEM_TYPE, WA.NAME, WA.VERSION, WA.TYPE, WA.RERUN,
WA.EXPAND_ROLE, WA.COST, WA.ERROR_ITEM_TYPE, WA.ERROR_PROCESS,
WA.FUNCTION, WA.FUNCTION_TYPE, WA.MESSAGE, WA.BEGIN_DATE,
WA.END_DATE, WA.DIRECTION, WPA.PROCESS_ITEM_TYPE,
WPA.PROCESS_NAME, WPA.PROCESS_VERSION, WPA.ACTIVITY_ITEM_TYPE,
WPA.ACTIVITY_NAME, WPA.INSTANCE_ID, WPA.INSTANCE_LABEL,
WPA.PERFORM_ROLE, WPA.PERFORM_ROLE_TYPE, WPA.START_END,
WPA.DEFAULT_RESULT
into WF_CACHE.Activities(waIND).ITEM_TYPE,
WF_CACHE.Activities(waIND).NAME,
WF_CACHE.Activities(waIND).VERSION,
WF_CACHE.Activities(waIND).TYPE,
WF_CACHE.Activities(waIND).RERUN,
WF_CACHE.Activities(waIND).EXPAND_ROLE,
WF_CACHE.Activities(waIND).COST,
WF_CACHE.Activities(waIND).ERROR_ITEM_TYPE,
WF_CACHE.Activities(waIND).ERROR_PROCESS,
WF_CACHE.Activities(waIND).FUNCTION,
WF_CACHE.Activities(waIND).FUNCTION_TYPE,
WF_CACHE.Activities(waIND).MESSAGE,
WF_CACHE.Activities(waIND).BEGIN_DATE,
WF_CACHE.Activities(waIND).END_DATE,
WF_CACHE.Activities(waIND).DIRECTION,
WF_CACHE.ProcessActivities(actid).PROCESS_ITEM_TYPE,
WF_CACHE.ProcessActivities(actid).PROCESS_NAME,
WF_CACHE.ProcessActivities(actid).PROCESS_VERSION,
WF_CACHE.ProcessActivities(actid).ACTIVITY_ITEM_TYPE,
WF_CACHE.ProcessActivities(actid).ACTIVITY_NAME,
WF_CACHE.ProcessActivities(actid).INSTANCE_ID,
WF_CACHE.ProcessActivities(actid).INSTANCE_LABEL,
WF_CACHE.ProcessActivities(actid).PERFORM_ROLE,
WF_CACHE.ProcessActivities(actid).PERFORM_ROLE_TYPE,
WF_CACHE.ProcessActivities(actid).START_END,
WF_CACHE.ProcessActivities(actid).DEFAULT_RESULT
from WF_PROCESS_ACTIVITIES WPA, WF_ACTIVITIES WA
where WPA.INSTANCE_ID = actid
and WA.ITEM_TYPE = WPA.ACTIVITY_ITEM_TYPE
and WA.NAME = WPA.ACTIVITY_NAME
and actdate >= WA.BEGIN_DATE
and actdate < NVL(WA.END_DATE, actdate+1);
select WPA.PROCESS_ITEM_TYPE, WPA.PROCESS_NAME, WPA.PROCESS_VERSION,
WPA.ACTIVITY_ITEM_TYPE, WPA.ACTIVITY_NAME, WPA.INSTANCE_ID,
WPA.INSTANCE_LABEL, WPA.PERFORM_ROLE, WPA.PERFORM_ROLE_TYPE,
WPA.START_END, WPA.DEFAULT_RESULT
into WF_CACHE.ProcessActivities(actid)
from WF_PROCESS_ACTIVITIES WPA
where WPA.INSTANCE_ID = GetActivityLabel.actid;
result := Wf_Engine_Util.Execute_Selector_Function(itemtype,
itemkey, wf_engine.eng_testctx);
result := Wf_Engine_Util.Execute_Selector_Function(itemtype,
itemkey, wf_engine.eng_setctx);
Wf_Item_Activity_Status.Update_Notification(itemtype, itemkey, actid,
number_value, text_value);
select
S.ROWID ROW_ID
from WF_ITEM_ACTIVITY_STATUSES S
where S.DUE_DATE < SYSDATE
and S.ACTIVITY_STATUS in ('ACTIVE','WAITING','NOTIFIED',
'SUSPEND','DEFERRED')
and S.ITEM_TYPE = itemtype;
select
S.ROWID ROW_ID
from WF_ITEM_ACTIVITY_STATUSES S
where S.DUE_DATE < SYSDATE
and S.ACTIVITY_STATUS in ('ACTIVE','WAITING','NOTIFIED',
'SUSPEND','DEFERRED');
select
S.ITEM_TYPE, S.ITEM_KEY, S.PROCESS_ACTIVITY
into l_itemtype, l_itemkey, l_actid
from WF_ITEM_ACTIVITY_STATUSES S , WF_ITEMS WI
where S.DUE_DATE < SYSDATE
and S.ACTIVITY_STATUS in ('WAITING','NOTIFIED','SUSPEND',
'DEFERRED','ACTIVE')
and S.ROWID = idarr(i)
and WI.item_type = S.ITEM_TYPE
and WI.item_key = S.ITEM_KEY
for update of S.ACTIVITY_STATUS, WI.item_type , wi.item_key NOWAIT;
select /*+ ORDERED USE_NL (WIASP WI WPAP WAP)
INDEX (WIASP WF_ITEM_ACTIVITY_STATUSES_N1) */
WIASP.ROWID ROW_ID
from WF_ITEM_ACTIVITY_STATUSES WIASP,
WF_ITEMS WI,
WF_PROCESS_ACTIVITIES WPAP,
WF_ACTIVITIES WAP
where WIASP.ITEM_TYPE = itemtype
and WIASP.PROCESS_ACTIVITY = WPAP.INSTANCE_ID
and WPAP.ACTIVITY_ITEM_TYPE = WAP.ITEM_TYPE
and WPAP.ACTIVITY_NAME = WAP.NAME
and WIASP.ITEM_TYPE = WI.ITEM_TYPE
and WIASP.ITEM_KEY = WI.ITEM_KEY
and WI.BEGIN_DATE >= WAP.BEGIN_DATE
and WI.BEGIN_DATE < nvl(WAP.END_DATE, WI.BEGIN_DATE+1)
and WAP.TYPE = wf_engine.eng_process
and WIASP.ACTIVITY_STATUS = 'ACTIVE' --use literal to force index
and not exists
(select null
from WF_ITEM_ACTIVITY_STATUSES WIASC,
WF_PROCESS_ACTIVITIES WPAC
where WAP.ITEM_TYPE = WPAC.PROCESS_ITEM_TYPE
and WAP.NAME = WPAC.PROCESS_NAME
and WAP.VERSION = WPAC.PROCESS_VERSION
and WPAC.INSTANCE_ID = WIASC.PROCESS_ACTIVITY
and WIASC.ITEM_TYPE = WI.ITEM_TYPE
and WIASC.ITEM_KEY = WI.ITEM_KEY
and WIASC.ACTIVITY_STATUS in ('ACTIVE','NOTIFIED','SUSPEND',
'DEFERRED','ERROR'));
select /*+ ORDERED USE_NL (WIASP WI WPAP WAP)
INDEX (WIASP WF_ITEM_ACTIVITY_STATUSES_N1) */
WIASP.ROWID ROW_ID
from WF_ITEM_ACTIVITY_STATUSES WIASP,
WF_ITEMS WI,
WF_PROCESS_ACTIVITIES WPAP,
WF_ACTIVITIES WAP
where WIASP.PROCESS_ACTIVITY = WPAP.INSTANCE_ID
and WPAP.ACTIVITY_ITEM_TYPE = WAP.ITEM_TYPE
and WPAP.ACTIVITY_NAME = WAP.NAME
and WIASP.ITEM_TYPE = WI.ITEM_TYPE
and WIASP.ITEM_KEY = WI.ITEM_KEY
and WI.BEGIN_DATE >= WAP.BEGIN_DATE
and WI.BEGIN_DATE < nvl(WAP.END_DATE, WI.BEGIN_DATE+1)
and WAP.TYPE = 'PROCESS'
and WIASP.ACTIVITY_STATUS = 'ACTIVE' --use literal to force index
and not exists
(select null
from WF_ITEM_ACTIVITY_STATUSES WIASC,
WF_PROCESS_ACTIVITIES WPAC
where WAP.ITEM_TYPE = WPAC.PROCESS_ITEM_TYPE
and WAP.NAME = WPAC.PROCESS_NAME
and WAP.VERSION = WPAC.PROCESS_VERSION
and WPAC.INSTANCE_ID = WIASC.PROCESS_ACTIVITY
and WIASC.ITEM_TYPE = decode(wap.direction,
wap.direction, WI.ITEM_TYPE,
wi.item_type)
and WIASC.ITEM_KEY = WI.ITEM_KEY
and WIASC.ACTIVITY_STATUS in ('ACTIVE', 'NOTIFIED', 'SUSPEND',
'DEFERRED', 'ERROR'));
select
WIASP.ITEM_TYPE, WIASP.ITEM_KEY, WIASP.PROCESS_ACTIVITY
into l_itemtype, l_itemkey, l_actid
from WF_ITEM_ACTIVITY_STATUSES WIASP,
WF_PROCESS_ACTIVITIES WPAP,
WF_ACTIVITIES WAP,
WF_ITEMS WI
where WIASP.PROCESS_ACTIVITY = WPAP.INSTANCE_ID
and WPAP.ACTIVITY_ITEM_TYPE = WAP.ITEM_TYPE
and WPAP.ACTIVITY_NAME = WAP.NAME
and WIASP.ITEM_TYPE = WI.ITEM_TYPE
and WIASP.ITEM_KEY = WI.ITEM_KEY
and WI.BEGIN_DATE >= WAP.BEGIN_DATE
and WI.BEGIN_DATE < nvl(WAP.END_DATE, WI.BEGIN_DATE+1)
and WAP.TYPE = wf_engine.eng_process
and WIASP.ACTIVITY_STATUS = 'ACTIVE' --use literal to force index
and not exists
(select null
from WF_ITEM_ACTIVITY_STATUSES WIASC,
WF_PROCESS_ACTIVITIES WPAC
where WAP.ITEM_TYPE = WPAC.PROCESS_ITEM_TYPE
and WAP.NAME = WPAC.PROCESS_NAME
and WAP.VERSION = WPAC.PROCESS_VERSION
and WPAC.INSTANCE_ID = WIASC.PROCESS_ACTIVITY
and WIASC.ITEM_TYPE = WI.ITEM_TYPE
and WIASC.ITEM_KEY = WI.ITEM_KEY
and WIASC.ACTIVITY_STATUS in ('ACTIVE','NOTIFIED','SUSPEND',
'DEFERRED','ERROR'))
and WIASP.ROWID = idarr(i)
for update of WIASP.ACTIVITY_STATUS, WI.ITEM_TYPE ,WI.ITEM_KEY NOWAIT;
select WIA.NAME
from WF_ITEM_ATTRIBUTES WIA
where WIA.ITEM_TYPE = CreateProcess.itemtype
and WIA.TYPE = 'EVENT';*/
Wf_Core.Raise('WFENG_ITEM_ROOT_SELECTOR');
select PROCESS_ACTIVITY, BEGIN_DATE
from WF_ITEM_ACTIVITY_STATUSES
where ITEM_TYPE = itemtype
and ITEM_KEY = itemkey
and ACTIVITY_STATUS = wf_engine.eng_deferred;
SELECT wn.notification_id
FROM wf_notifications wn, WF_ITEM_ACTIVITY_STATUSES ias
WHERE ias.item_type = itemtype
AND ias.item_key = itemkey
AND ias.notification_id is not null
AND ias.notification_id = wn.group_id
AND wn.status = 'OPEN'
UNION
SELECT wn.notification_id
FROM wf_notifications wn, WF_ITEM_ACTIVITY_STATUSES_H iash
WHERE iash.item_type = itemtype
AND iash.item_key = itemkey
AND iash.notification_id is not null
AND iash.notification_id = wn.notification_id
AND wn.status = 'OPEN'; --
select
PROCESS_ACTIVITY, BEGIN_DATE
from WF_ITEM_ACTIVITY_STATUSES
where ITEM_TYPE = itemtype
and ITEM_KEY = itemkey
and ACTIVITY_STATUS = wf_engine.eng_deferred;
SELECT item_key
FROM wf_items
WHERE item_type = p_itemType
AND end_date is NULL;
SELECT item_type, item_key
FROM wf_items
WHERE end_date is NULL;
SELECT distinct wias.item_key
FROM wf_item_activity_statuses wias
WHERE wias.item_type = p_itemType
AND wias.activity_status = wf_engine.eng_suspended;
SELECT distinct wias.item_type, wias.item_key
FROM wf_item_activity_statuses wias
WHERE wias.activity_status = wf_engine.eng_suspended;
select parent_item_type, parent_item_key, parent_context
into l_parent_itemType, l_parent_itemKey, l_parent_context
from wf_items
where item_type = copy_itemtype
and item_key = copy_itemkey
for update of item_type;
insert into wf_items(
ITEM_TYPE, ITEM_KEY,
ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION,
OWNER_ROLE, USER_KEY,
PARENT_ITEM_TYPE, PARENT_ITEM_KEY, PARENT_CONTEXT,
BEGIN_DATE, END_DATE)
select
ITEM_TYPE, NEW_ITEMKEY,
ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION,
OWNER_ROLE, USER_KEY,
PARENT_ITEM_TYPE, PARENT_ITEM_KEY, PARENT_CONTEXT,
BEGIN_DATE, null
from wf_items
where item_type = copy_itemtype
and item_key = copy_itemkey;
delete from wf_item_attribute_values
where item_type = copy_itemtype
and item_key = new_itemkey;
insert into wf_item_attribute_values
(ITEM_TYPE, ITEM_KEY, NAME,
TEXT_VALUE, NUMBER_VALUE, DATE_VALUE)
select ITEM_TYPE, NEW_ITEMKEY, NAME,
TEXT_VALUE, NUMBER_VALUE, DATE_VALUE
from wf_item_attribute_values
where item_type = copy_itemtype
and item_key = copy_itemkey
and name not like '#LBL_'
and name not like '#CNT_'
union all
select ITEM_TYPE, new_itemkey, NAME,
TEXT_DEFAULT, NUMBER_DEFAULT, DATE_DEFAULT
from WF_ITEM_ATTRIBUTES
where ITEM_TYPE = copy_itemtype
and NAME not in
(select name
from wf_item_attribute_values
where item_type = copy_itemtype
and item_key = copy_itemkey
and name not like '#LBL_'
and name not like '#CNT_');
update WF_ITEM_ATTRIBUTE_VALUES
set NUMBER_VALUE = NUMBER_VALUE + 1
where NAME like '#CNT_%'
and NUMBER_VALUE is not null
and ITEM_TYPE = l_parent_itemType
and ITEM_KEY = l_parent_itemKey;
select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY,
ACTIVITY_STATUS, ACTIVITY_RESULT_CODE,
ASSIGNED_USER, NOTIFICATION_ID,
BEGIN_DATE, END_DATE, EXECUTION_TIME,
ERROR_NAME, ERROR_MESSAGE, ERROR_STACK,
OUTBOUND_QUEUE_ID, DUE_DATE
from wf_item_activity_statuses
where item_type = itemtype
and item_key = copy_itemkey;
select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY,
ACTIVITY_STATUS, ACTIVITY_RESULT_CODE,
ASSIGNED_USER, NOTIFICATION_ID,
BEGIN_DATE, END_DATE, EXECUTION_TIME,
ERROR_NAME, ERROR_MESSAGE, ERROR_STACK,
OUTBOUND_QUEUE_ID, DUE_DATE
from wf_item_activity_statuses_h
where item_type = itemtype
and item_key = copy_itemkey;
select ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY,
ACTIVITY_STATUS, ACTIVITY_RESULT_CODE,
ASSIGNED_USER, NOTIFICATION_ID,
BEGIN_DATE, END_DATE, EXECUTION_TIME,
ERROR_NAME, ERROR_MESSAGE, ERROR_STACK,
OUTBOUND_QUEUE_ID, DUE_DATE
from wf_item_activity_statuses
where item_type = itemtype
and item_key = copy_itemkey
and notification_id is not null
and activity_status = 'NOTIFIED'
order by notification_id;
insert into wf_item_activity_statuses
(ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY,
ACTIVITY_STATUS, ACTIVITY_RESULT_CODE,
ASSIGNED_USER, NOTIFICATION_ID,
BEGIN_DATE, END_DATE, EXECUTION_TIME,
ERROR_NAME, ERROR_MESSAGE, ERROR_STACK,
OUTBOUND_QUEUE_ID, DUE_DATE)
values(act.item_type, itemkey, act.process_activity,
act.activity_status, act.activity_result_code,
act.assigned_user, nid,
act.begin_date, act.end_date, act.execution_time,
act.error_name, act.error_message, act.error_stack,
msg_id, act.due_date);
insert into wf_item_activity_statuses_h
(ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY,
ACTIVITY_STATUS, ACTIVITY_RESULT_CODE,
ASSIGNED_USER, NOTIFICATION_ID,
BEGIN_DATE, END_DATE, EXECUTION_TIME,
ERROR_NAME, ERROR_MESSAGE, ERROR_STACK,
OUTBOUND_QUEUE_ID, DUE_DATE)
values(hist.item_type, itemkey, hist.process_activity,
hist.activity_status, hist.activity_result_code,
hist.assigned_user, nid,
hist.begin_date, hist.end_date, hist.execution_time,
hist.error_name, hist.error_message, hist.error_stack,
null, hist.due_date);
update wf_item_activity_statuses ias
set activity_status = wf_engine.eng_notified
where item_type = itemtype
and item_key = itemkey
and activity_status = 'ACTIVE'
and activity_status = wf_engine.eng_active
and exists (select 'its a function, not subprocess'
from wf_process_activities pa,
wf_activities ac
where pa.activity_name = ac.name
and pa.activity_item_type = ac.item_type
and pa.activity_item_type = ias.item_type
and pa.instance_id = ias.process_activity
and type='FUNCTION');
Wf_Core.Raise('WFENG_ITEM_ROOT_SELECTOR');
Wf_Core.Raise('WFENG_ITEM_ROOT_SELECTOR');
update WF_NOTIFICATIONS WN set
status = 'CLOSED',
end_date = sysdate
where WN.GROUP_ID = CompleteActivity.notid
and WN.STATUS = 'OPEN';
select WPA.PROCESS_NAME, WPA.INSTANCE_LABEL
into process, label
from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_PROCESS_ACTIVITIES WPA
where WIAS.ITEM_TYPE = itemtype
and WIAS.ITEM_KEY = itemkey
and WIAS.ACTIVITY_STATUS = wf_engine.eng_notified
and WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
and WPA.ACTIVITY_NAME = actname
and WPA.PROCESS_NAME = nvl(process, WPA.PROCESS_NAME);
Wf_Item_Activity_Status.Update_Notification(itemtype, itemkey, actid,
'', performer);
Wf_Item_Activity_Status.Update_Notification(itemtype, itemkey, actid,
'', performer);
Wf_Item_Activity_Status.Update_Notification(itemtype, itemkey, actid,
notid, performer);
select ias.ITEM_KEY,
pa.INSTANCE_LABEL activity,
pa.INSTANCE_ID actid
from WF_ITEM_ACTIVITY_STATUSES ias,
WF_PROCESS_ACTIVITIES pa
where ias.ITEM_TYPE = x_itemtype
and (x_itemkey is null or ias.ITEM_KEY = x_itemkey)
and (x_activity is null or pa.INSTANCE_LABEL = x_activity)
and ias.PROCESS_ACTIVITY = pa.INSTANCE_ID
and ias.ACTIVITY_STATUS = 'ERROR';
Lets do a single select for rownum < 1 this
should suffice
cursor act_curs (p_itemtype varchar2, p_itemkey varchar2) is
select pa.instance_label,pa.instance_id
ias.activity_status,
ias.activity_result_code ,
ias.assigned_user,
ias.notification_id NID,
ntf.status,
ias.performed_by
from wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities ac,
wf_activities ap,
wf_items i,
wf_notifications ntf
where ias.item_type = p_itemtype
and ias.item_key = p_itemkey
and ias.activity_status = wf_engine.eng_completed
and ias.process_activity = pa.instance_id
and pa.activity_name = ac.name
and pa.activity_item_type = ac.item_type
and pa.process_name = ap.name
and pa.process_item_type = ap.item_type
and pa.process_version = ap.version
and i.item_type = '&item_type'
and i.item_key = ias.item_key
and i.begin_date >= ac.begin_date
and i.begin_date < nvl(ac.end_date, i.begin_date+1)
and ntf.notification_id(+) = ias.notification_id
order by decode(ias.activity_status,'ERROR',1,'NOTIFIED',2,'DEFERRED',3,'SUSPEND',4,'WAITING',5,'ACTIVE',6,'COMPLETE',7) asc , ias.execution_time desc
*/
begin
--Get the item status
--Use the API above for the same
wf_engine.ItemStatus(itemtype ,itemkey ,l_status,l_result);
select process_activity,
activity_status,
activity_result_code
into l_instance_id,
l_status,
l_result
from
(
select process_activity,
activity_status,
activity_result_code
from wf_item_activity_statuses
where item_type = itemtype
and item_key = itemkey
and activity_status <> wf_engine.eng_completed
order by decode(activity_status, 'ERROR',1, 'NOTIFIED',2, 'DEFERRED',3,
'SUSPEND',4, 'WAITING',5, 'ACTIVE',6, 7) asc,
begin_date desc, execution_time desc
)
where rownum < 2;
select ROOT_ACTIVITY, BEGIN_DATE
into rootactivity, active_date
from WF_ITEMS
where ITEM_TYPE = p_item_type
and ITEM_KEY = p_item_key;
select WPA.ACTIVITY_ITEM_TYPE, WPA.ACTIVITY_NAME
from WF_PROCESS_ACTIVITIES WPA,
WF_ACTIVITIES WA
where WPA.PROCESS_ITEM_TYPE = p_process_item_type
and WPA.PROCESS_NAME = p_process_name
and WPA.PROCESS_VERSION = ver
and WPA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
and WPA.ACTIVITY_NAME = WA.NAME
and WA.TYPE = 'PROCESS'
and active_date >= WA.BEGIN_DATE
and active_date < nvl(WA.END_DATE, active_date+1);
select VERSION into m_version
from WF_ACTIVITIES
where ITEM_TYPE = p_process_item_type
and NAME = p_process_name
and active_date >= BEGIN_DATE
and active_date < nvl(END_DATE, active_date + 1);
select count(1) into n
from WF_PROCESS_ACTIVITIES
where PROCESS_ITEM_TYPE = p_process_item_type
and PROCESS_NAME = p_process_name
and PROCESS_VERSION = m_version
and ACTIVITY_ITEM_TYPE = nvl(p_activity_item_type, p_process_item_type)
and ACTIVITY_NAME = p_activity_name;
SELECT WIAS.PROCESS_ACTIVITY actid
FROM WF_ITEM_ACTIVITY_STATUSES WIAS, WF_PROCESS_ACTIVITIES WPA,
WF_ACTIVITIES WA
WHERE WIAS.ITEM_TYPE = event.itemtype
AND WIAS.ITEM_KEY = event.itemkey
AND WIAS.ACTIVITY_STATUS = 'NOTIFIED'
AND WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
AND WPA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
AND WPA.ACTIVITY_NAME = WA.NAME
AND actdate >= WA.BEGIN_DATE
AND actdate < NVL(WA.END_DATE, actdate+1)
AND WA.TYPE = 'EVENT'
AND WA.DIRECTION = 'RECEIVE'
AND (WA.EVENT_NAME is null
OR WA.EVENT_NAME in
(SELECT WE.NAME -- Single events
FROM WF_EVENTS WE
WHERE WE.TYPE = 'EVENT'
AND WE.NAME = event.event_name
UNION ALL
SELECT GRP.NAME -- Groups containing event
FROM WF_EVENTS GRP, WF_EVENT_GROUPS WEG, WF_EVENTS MBR
WHERE GRP.TYPE = 'GROUP'
AND GRP.GUID = WEG.GROUP_GUID
AND WEG.MEMBER_GUID = MBR.GUID
AND MBR.NAME = event.event_name));
SELECT parent_item_type, parent_item_key
INTO parent_itemtype, parent_itemkey
FROM wf_items
WHERE item_type = itemtype
AND item_key = itemkey;
SELECT /*+ LEADING(WA) */ WIAS.ITEM_TYPE, WIAS.ITEM_KEY, WIAS.PROCESS_ACTIVITY ACTID
FROM WF_ITEM_ACTIVITY_STATUSES WIAS,
WF_PROCESS_ACTIVITIES WPA,
(
SELECT /*+ NO_MERGE */ WA.*
FROM WF_ACTIVITIES WA
where WA.TYPE = 'EVENT'
AND WA.DIRECTION = 'RECEIVE'
AND ( WA.EVENT_NAME IS NULL OR
WA.EVENT_NAME = event2.event_name OR
EXISTS
(
SELECT null -- Groups containing event
FROM WF_EVENTS GRP, WF_EVENT_GROUPS WEG, WF_EVENTS MBR
WHERE GRP.TYPE = 'GROUP'
AND GRP.GUID = WEG.GROUP_GUID
AND WEG.MEMBER_GUID = MBR.GUID
AND MBR.NAME = event2.event_name
AND GRP.NAME = WA.EVENT_NAME
)
)
) WA,
WF_ITEMS WI
WHERE WIAS.ACTIVITY_STATUS = 'NOTIFIED'
AND WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
AND WIAS.ITEM_TYPE = WPA.PROCESS_ITEM_TYPE
AND WPA.ACTIVITY_ITEM_TYPE = WA.ITEM_TYPE
AND WPA.ACTIVITY_NAME = WA.NAME
AND EXISTS
( SELECT 1 FROM WF_ACTIVITY_ATTR_VALUES WAAV,
WF_ITEM_ATTRIBUTE_VALUES WIAV
WHERE WAAV.PROCESS_ACTIVITY_ID = WIAS.PROCESS_ACTIVITY
AND WAAV.NAME = '#BUSINESS_KEY'
AND WAAV.VALUE_TYPE = 'ITEMATTR'
AND WIAV.ITEM_TYPE = WIAS.ITEM_TYPE
AND WIAV.ITEM_KEY = WIAS.ITEM_KEY
AND WAAV.TEXT_VALUE = WIAV.NAME
AND WIAV.TEXT_VALUE = event2.businesskey)
AND WI.ITEM_TYPE = WIAS.ITEM_TYPE
AND WI.ITEM_KEY = WIAS.ITEM_KEY
FOR UPDATE OF WI.ITEM_TYPE,WI.item_key NOWAIT;
update WF_ITEM_ATTRIBUTE_VALUES wiav
set wiav.NUMBER_VALUE = (wiav.NUMBER_VALUE+p_addend)
where wiav.ITEM_TYPE = p_itemtype
and wiav.ITEM_KEY = p_itemkey
and wiav.NAME = p_aname
returning wiav.NUMBER_VALUE into l_avalue;
update WF_ITEM_ATTRIBUTE_VALUES wiav
set wiav.NUMBER_VALUE = 0
where wiav.ITEM_TYPE = p_itemtype
and wiav.ITEM_KEY = p_itemkey
and wiav.NAME = p_aname
returning wiav.NUMBER_VALUE into l_avalue;
SELECT wias.item_key,
wpa.process_name,
wpa.instance_label activity
FROM wf_item_activity_statuses wias,
wf_process_activities wpa
WHERE wias.item_type = x_item_type
AND (x_item_key IS NULL OR wias.item_key = x_item_key)
AND (x_process IS NULL OR wpa.process_name = x_process)
AND (x_activity IS NULL OR wpa.instance_label = x_activity)
AND (x_start_date IS NULL OR wias.begin_date >= x_start_date)
AND (x_end_date IS NULL OR wias.begin_date <= x_end_date)
AND wias.process_activity = wpa.instance_id
AND wias.activity_status = 'ERROR'
AND x_max_retry >=
(SELECT count(1)
FROM wf_item_activity_statuses_h wiash
WHERE wiash.item_type = wias.item_type
AND wiash.item_key = wias.item_key
AND wiash.process_activity = wias.process_activity
AND wiash.action = 'RETRY');