Standard API for PL/SQL Procedures in Oracle WorkFlow

Standard API for PL/SQL Procedures Called by Function Activities

All PL/SQL stored procedures that are called by function or notification activities in an Oracle Workflow process should follow this standard API format so that the Workflow Engine can properly execute the activity.

Attention: The Workflow Engine traps errors produced by function activities by setting a savepoint before each function activity. If an activity produces an unhandled exception, the engine performs a rollback to the savepoint, and sets the activity to the ERROR status. For this reason, you should never commit within the PL/SQL procedure of a function activity. The Workflow Engine never issues a commit as it is the responsibility of the calling application to commit.

For environments such as database triggers or distributed transactions that do not allow savepoints, the Workflow Engine automatically traps "Savepoint not allowed" errors and defers the execution of the activity to the background engine.

The example in this section is numbered with the notation 1--> for easy referencing. The numbers and arrows themselves are not part of the procedure.

1--> procedure <procedure name=""> (itemtype in varchar2,

                                  itemkey in varchar2,

                                  actid in number,

                                  funcmode in varchar2,

                                  resultout out varchar2) is

2--> <local declarations="">

3--> begin

      if ( funcmode = 'RUN' ) then

           <your executable="" run="" statements="">

           resultout := 'COMPLETE:<result>';

           return;

      end if;

4--> if ( funcmode = 'CANCEL' ) then

           <your cancel="" executable="" statements="">

           resultout := 'COMPLETE';

           return;

      end if;

5--> if ( funcmode = 'RESPOND' ) then

           <your executable="" respond="" statements="">

           resultout := 'COMPLETE';

           return;

      end if;

6--> if ( funcmode = 'FORWARD' ) then

           <your executable="" forward="" statements="">

           resultout := 'COMPLETE';

           return;

      end if;

7--> if ( funcmode = 'TRANSFER' ) then

           <your executable="" statements="" transfer="">

           resultout := 'COMPLETE';

           return;

      end if;

8--> if ( funcmode = 'TIMEOUT' ) then

           <your executable="" statements="" timeout="">

           if (<condition_ok_to_proceed>) then

              resultout := 'COMPLETE';

           else

              resultout := wf_engine.eng_timedout;

           end if;

           return;

      end if;

9--> if ( funcmode = '<other funcmode="">' ) then

           resultout := ' ';

           return;

      end if;

10--> exception

           when others then

            WF_CORE.CONTEXT ('<package name="">', '<procedure name="">', <itemtype>,

                            <itemkey>, to_char(<actid>), <funcmode>);

           raise;

11--> end <procedure name="">;

1--> When the Workflow Engine calls a stored procedure for a function activity, it passes four parameters to the procedure and may expect a result when the procedure completes. The parameters are defined here:

itemtype The internal name for the item type. Item types are defined in the Oracle Workflow Builder.

itemkey A string that represents a primary key generated by the workflow-enabled application for the item type. The string uniquely identifies the item within an item type.

actid The ID number of the activity from which this procedure is called.

funcmode The execution mode of the activity. If the activity is a function activity, the mode is either 'RUN' or 'CANCEL'. If the activity is a notification activity, with a post-notification function, then the mode can be 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT', or 'RUN'. Other execution modes may be added in the future.

resultout If a result type is specified in the Activities properties page for the activity in the Oracle Workflow Builder, this parameter represents the expected result that is returned when the procedure completes. The possible results are:

COMPLETE:<result_code>--activity completes with the indicated result code. The result code must match one of the result codes specified in the result type of the function activity.

WAITING--activity is pending, waiting on another activity to complete before it completes. An example is the Standard 'AND' activity.

DEFERRED:<date>--activity is deferred to a background engine for execution until a given date. <date> must be of the format: to_char(<date_string>, wf_engine.date_format)

NOTIFIED:<notification_id>:<assigned_user>--an external entity is notified that an action must be performed. A notification ID and an assigned user can optionally be returned with this result. Note that the external entity must call CompleteActivity( ) to inform the Workflow Engine when the action completes.

ERROR:<error_code>--activity encounters an error and returns the indicated error code.

2--> This section declares any local arguments that are used within the procedure.

3--> The procedure body begins in this section with an IF statement. This section contains one or more executable statements that run if the value of funcmode is 'RUN'. One of the executable statements can return a result for the procedure. For example, a result can be 'COMPLETE:APPROVED'.

Note: The Workflow Engine automatically runs a post-notification function in RUN mode after the Notification System completes execution of the post-notification function in RESPOND mode. The RUN mode executable statements can perform processing such as vote tallying and determine what result to return for the notification activity.

4--> This section clears the activity and can contain executable statements that run if the value of funcmode is 'CANCEL'. Often, this section contains no executable statements to simply return a null value, but this section also provides you with the chance to 'undo' something if necessary. An activity can have a funcmode of 'CANCEL' in the special case where the activity is part of a loop that is being revisited.

The first activity in a loop must always have the Loop Reset flag checked in the Activities properties Detail page. When the Workflow Engine encounters an activity that has already run, it verifies whether the activity's Loop Reset flag is set. If the flag is set, the engine then identifies the activities that belong in that loop and sets funcmode to 'CANCEL' for those activities. Next, the engine transitions through the loop in reverse order and executes each activity in 'CANCEL' mode to clear all prior results for the activities so they can run again. See: Looping and Loop Counter Activity.

5--> This section is needed only for post-notification functions. Use this section to include execution statements that run if the value of funcmode is 'RESPOND', that is, when a RESPOND operation is performed. For example, include execution statements that validate the response of the notification. After the Notification System completes execution of the post-notification function in RESPOND mode, the Workflow Engine then runs the post-notification function again in RUN mode. See: Post-notification functions.

6--> This section is needed only for post-notification functions. Use this section to include execution statements that run if the value of funcmode is 'FORWARD', that is, when a notification's state changes to 'FORWARD'. For example, include execution statements that validate the role to which the notification is being forwarded.

7--> This section is needed only for post-notification functions. Use this section to include execution statements that run if the value of funcmode is 'TRANSFER', that is, when a notification's state changes to 'TRANSFER'. For example, include execution statements that validate the role to which the notification is being transferred.

Note: For 'RESPOND', 'FORWARD', and 'TRANSFER' funcmodes, the resultout parameter is ignored, except if the returned value looks something like 'ERROR%'. Therefore, if you do not want the Respond, Forward or Transfer operation to occur after having executed your post-notification function, you can do one of two things:

Return 'ERROR:<errcode>' in the resultout parameter to convert it to a generic exception with the errcode mentioned in the message.

Raise an exception directly in your procedure with a more informative error message. See: Post-notification Functions and Notification Model:

8--> This section is needed only for post-notification functions. Use this section to include execution statements that run if a notification activity times out. You can include logic to test whether the workflow can proceed normally, and if so, to complete the activity so that the workflow can continue to the next activity. For example, if a Voting activity times out before all recipients respond, you can include logic that determines how to interpret the responses based on the current response pool and completes the activity with the appropriate result.

You should also include logic to return a result of wf_engine.eng_timedout if the workflow cannot proceed normally. Model any subsequent behavior in your process diagram using a <timeout> transition to another activity. The Workflow Engine will follow the <timeout> transition when the result wf_engine.eng_timedout is returned.

9--> This section handles execution modes other than 'RUN', 'CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', or 'TIMEOUT'. Other execution modes may be added in the future. Since your activity does not need to implement any of these other possible modes, it should simply return null.

10--> This section calls WF_CORE.CONTEXT( ) if an exception occurs, so that you can include context information in the error stack to help you locate the source of an error. See: CONTEXT.