Faced a problem in a custom workflow in time out node.
Have created a workflow with timeout node.
The workflow initiated from a custom form which registered in a custom application. Even if the time out node has been added in wf, still time out has not been worked. Process stuck at the node where timeout has been added. The reason I have found when workflow background process runs, the time out called from WF_ENGINE.ProcessTimeOut(itemtype in varchar2) package. And the package has only considered APPS schema or more precisely it checks the the owner of the workflow is in apps schema.
As the owner of the wf is custom application so each time wf process stuck at the timeout node and its wait for the response.
procedure ProcessTimeOut(itemtype in varchar2)
is
resource_busy exception;
pragma exception_init(resource_busy, -00054);
l_itemtype varchar2(8);
l_itemkey varchar2(240);
l_actid pls_integer;
pntfstatus varchar2(8);
pntfresult varchar2(30);
-- Select one timeout activity that matches itemtype
-- NOTE: Two separate cursors are used for itemtype and no-itemtype
-- cases to get better execution plans.
-- select everything but completed and error.
-- avoid "not in" which disables index in RBO
cursor curs_itype is
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;
cursor curs_noitype is
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');
idarr RowidArrayTyp;
arrsize pls_integer;
eligible boolean;
schema varchar2(30);
begin
-- Fetch eligible rows into array
arrsize := 0;
if (itemtype is not null) then
-- Fetch by itemtype
for id in curs_itype loop
arrsize := arrsize + 1;
idarr(arrsize) := id.row_id;
end loop;
else
-- Fetch all itemtypes
for id in curs_noitype loop
arrsize := arrsize + 1;
idarr(arrsize) := id.row_id;
end loop;
end if;
-- Process all eligible rows found
for i in 1 .. arrsize loop
-- Lock row, and check if still eligible for execution
-- To check eligibility, do original select only add rowid condition.
-- Note ok to use no-itemtype variant since itemtype can't change
-- and was already filtered for in original select.
-- select everything but completed and error. avoid "not in" which
-- disables index in RBO.
begin
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;
-- check if schema matched
schema := Wf_Engine.GetItemAttrText(l_itemtype,l_itemkey,
wf_engine.eng_schema, ignore_notfound=>TRUE);
---*** XX Added
--- For time out feature wf only consider APPS schema.
--- We have registered our custom packages in the XX schema
--- So delibaretly we are bypassing schema value as APPS
If Schema = 'XX'
then
schema :='APPS';
End if;
---*** XX End
if (schema is null or
schema = Wf_Engine.Current_Schema) then
eligible := TRUE;
else
eligible := FALSE;
end if;
exception
when resource_busy or no_data_found then
-- If row already locked, or no longer eligible to run,
-- continue on to next item in list.
eligible := FALSE;
end;
if (eligible) then
-- Set the status to COMPLETE:#TIMEOUT.
Wf_Item_Activity_Status.Create_Status(l_itemtype, l_itemkey, l_actid,
wf_engine.eng_completed, wf_engine.eng_timedout);
begin
begin
begin
savepoint wf_savepoint;
-- If there is a function attached, call it in timeout mode to
-- give the function one last chance to complete and override
-- the timeout.
Wf_Engine_Util.Execute_Post_NTF_Function(l_itemtype, l_itemkey,
l_actid, wf_engine.eng_timeout, pntfstatus, pntfresult);
if (pntfstatus = wf_engine.eng_completed) then
-- Post-notification function found and returned a completed
-- status.
-- Complete activity with result of post-notification function.
Wf_Engine_Util.Complete_Activity(l_itemtype, l_itemkey, l_actid,
pntfresult, FALSE);
else
-- Either had no post-notification function, or result was still
-- not complete.
-- In either case, complete activity with #TIMEOUT.
Wf_Engine_Util.Complete_Activity(l_itemtype, l_itemkey, l_actid,
wf_engine.eng_timedout);
end if;
exception
when others then
-- If anything in this process raises an exception:
-- 1. rollback any work in this process thread
-- Raise an exception for the next exception handler to finish
-- remaining steps.
rollback to wf_savepoint;
raise;
end;
exception
when NO_SAVEPOINT then
-- Catch any savepoint error in case of a commit happened.
Wf_Core.Token('ACTIVITY', Wf_Engine.GetActivityLabel(l_actid));
Wf_Core.Raise('WFENG_COMMIT_IN_COMPLETE');
end;
exception
when OTHERS then
-- Remaining steps for completing activity raises an exception:
-- 2. set this activity to error status
-- 3. execute the error process (if any)
-- 4. clear the error to continue with next activity
Wf_Core.Context('Wf_Engine', 'ProcessTimeout', l_itemkey, l_itemtype,
to_char(l_actid));
Wf_Item_Activity_Status.Set_Error(l_itemtype, l_itemkey, l_actid,
wf_engine.eng_exception, FALSE);
Wf_Engine_Util.Execute_Error_Process(l_itemtype, l_itemkey,
l_actid, wf_engine.eng_exception);
Wf_Core.Clear;
end;
end if;
-- bug 7828862 - Resynch apps context from cached values if it changed
wfa_sec.Restore_Ctx();
-- For eligible row: Commit work to insure this activity
-- thread doesn't interfere with others.
-- For non-eligible row: Commit to release the lock.
commit;
Fnd_Concurrent.Set_Preferred_RBS;
end loop;
exception
when others then
Wf_Core.Context('Wf_Engine', 'ProcessTimeout', l_itemkey, l_itemtype,
to_char(l_actid));
raise;
end ProcessTimeOut;
