Skip Headers

Oracle Workflow Developer's Guide
Release 2.6.4
Part Number B15853-04
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

Defining Procedures and Functions for Oracle Workflow

This chapter describes the standard APIs to use for Oracle Workflow PL/SQL and Java procedures and functions.

This chapter covers the following topics:

Defining Procedures and Functions for Oracle Workflow

Oracle Workflow lets you integrate your own custom PL/SQL and Java procedures and functions at certain points in your workflow processes and in the Business Event System. To ensure that Oracle Workflow can properly execute your custom code, follow these standard APIs when developing your procedures and functions.

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.

Important: 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.

Oracle Workflow components that continue workflow processing asynchronously, such as background engines and the Notification System, do issue commits when appropriate on behalf of the calling application.

The example in this section is numbered with the notation (1) for easy referencing. The numbers 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 RUN executable statements> 
       resultout := 'COMPLETE:<result>';
       return;
     end if;

(4)  if ( funcmode = 'CANCEL' ) then
       <your CANCEL executable statements>  
       resultout := 'COMPLETE';
       return;
     end if;

(5)  if ( funcmode = 'SKIP' ) then
       <your SKIP executable statements>  
       resultout := 'COMPLETE:<result>';
       return;
     end if;

(6)  if ( funcmode = 'RETRY' ) then
       <your RETRY executable statements>  
       resultout := 'COMPLETE:<result>';
       return;
     end if;

(7)  if ( funcmode = 'VALIDATE' ) then
       <your VALIDATE executable statements>  
       resultout := 'COMPLETE';
       return;
     end if;

(8)  if ( funcmode = 'RESPOND' ) then
       <your RESPOND executable statements> 
       resultout := 'COMPLETE';
       return;
     end if;

(9)  if ( funcmode = 'FORWARD' ) then
       <your FORWARD executable statements>  
       resultout := 'COMPLETE';
       return;
     end if;

(10) if ( funcmode = 'TRANSFER' ) then
       <your TRANSFER executable statements> 
       resultout := 'COMPLETE';
       return;
     end if;

(11) if ( funcmode = 'QUESTION' ) then
       <your QUESTION executable statements>  
       resultout := 'COMPLETE';
       return;
     end if;

(12) if ( funcmode = 'ANSWER' ) then
       <your ANSWER executable statements>  
       resultout := 'COMPLETE';
       return;
     end if;

(13) if ( funcmode = 'TIMEOUT' ) then
       <your TIMEOUT executable statements>  
       if (<condition_ok_to_proceed>) then
         resultout := 'COMPLETE';
       else
         resultout := wf_engine.eng_timedout;
       end if;
       return;
     end if;

(14) if ( funcmode = '<other funcmode>' ) then
       resultout := ' ';
       return;
     end if;

(15) exception
       when others then
       WF_CORE.CONTEXT ('<package name>', '<procedure name>', 
         <itemtype>, <itemkey>, 
         to_char(<actid>), <funcmode>);
       raise;

(16) 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:

Variable Description
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 can be 'RUN', 'CANCEL', 'SKIP', or 'RETRY'. If the activity is a notification activity, with a post-notification function, then the mode can be 'RESPOND', 'FORWARD', 'TRANSFER', 'QUESTION', 'ANSWER', 'VALIDATE', 'TIMEOUT', CANCEL, 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 option set in the activity properties Detail page. When the Workflow Engine encounters an activity that has already run, it verifies the activity's Loop Reset option. If this option is set to Reset, 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 forward order and executes each activity in 'CANCEL' mode to clear all prior results for the activities so they can run again. See: Looping, Oracle Workflow API Reference and Loop Counter Activity.

(5) This section can contain executable statements that run if the value of funcmode is 'SKIP'. For example, you can validate the result supplied for the skipped activity or notify an administrator that the activity was skipped. An activity can have a funcmode of 'SKIP' if a user skips the activity from the Status Monitor, from an error notification, or using the WF_ENGINE.HandleError API.

Note: If you want to prevent users from skipping the activity, return a result of '#NOSKIP' or wf_engine.eng_noskip instead of 'COMPLETE:<result>'. When you set the result to specify that the activity cannot be skipped, Oracle Workflow raises an error instead of executing the skip operation. For example, set the result as follows:

resultout := wf_engine.eng_noskip;

(6) This section can contain executable statements that run if the value of funcmode is 'RETRY'. For example, you can verify that the retried activity is eligible to be reexecuted. An activity can have a funcmode of 'RETRY' if a user retries the activity from the Status Monitor, from an error notification, or using the WF_ENGINE.HandleError API.

(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 'VALIDATE'. This mode is executed before 'RESPOND' mode, if you define this section in your post-notification function. For example, include execution statements that validate the response values before accepting and recording the response.

(8) 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 to 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, Oracle Workflow API Reference.

(9) 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.

(10) 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.

(11) This section is needed only for post-notification functions. Use this section to include execution statements that run if the value of funcmode is 'QUESTION', that is, when a user requests more information about a notification from another user. For example, include execution statements that validate the role to which the request for more information is being sent.

(12) This section is needed only for post-notification functions. Use this section to include execution statements that run if the value of funcmode is 'ANSWER', that is, when a user answers a request. For example, include execution statements that validate the answering information.

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

(13) 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.

(14) This section handles execution modes other than those already specified. 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.

(15) 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, Oracle Workflow API Reference.

Standard API for Java Procedures Called by Function Activities

In standalone Oracle Workflow, you can create custom Java classes to be called by external Java function activities in an Oracle Workflow process. Java procedures that are called by function activities are implemented as classes that extend the WFFunctionAPI class. The custom Java classes should follow a standard API format so that they can be properly executed by the Oracle Workflow Java Function Activity Agent.

Note: This functionality is currently only available for the standalone version of Oracle Workflow. It is not available in Oracle Applications.

Important: 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, just as with PL/SQL procedures, you should never commit within the Java procedure of a function activity. The Workflow Engine never issues a commit as it is the responsibility of the calling application to commit.

Oracle Workflow components that continue workflow processing asynchronously, such as background engines and the Notification System, do issue commits when appropriate on behalf of the calling application.

Many Workflow Engine and Notification APIs have corresponding Java methods that your Java program can call to communicate with Oracle Workflow. The WFFunctionAPI and WFAttribute classes also contain methods that your Java program can call to access item type and activity attributes. See: Oracle Workflow Java Interface, Oracle Workflow API Reference, Workflow Function APIs, Oracle Workflow API Reference, and Workflow Attribute APIs, Oracle Workflow API Reference.

To invoke a custom Java class from within a workflow process, create an external Java function activity that calls the class. See: To Create a Function Activity.

Java function activities are implemented as external procedures. When the Workflow Engine reaches an external Java function activity, the Workflow Engine places a message on the Workflow 'Outbound' queue. The Java Function Activity Agent monitors this queue and calls the class indicated in the Function Name property for the function activity. When the Java procedure is complete, the Java Function Activity Agent enqueues the results onto the 'Inbound' queue. See: Setting Up the Java Function Activity Agent, Oracle Workflow Administrator's Guide.

Note: These 'Outbound' and 'Inbound' queues are separate from the queues used for the Business Event System. See: Workflow Queue APIs, Oracle Workflow API Reference.

After a Java procedure completes, you must run a background engine to process the 'Inbound' queue and complete the function activity. Otherwise, the activity will remain in the DEFERRED status. See: Setting Up Background Engines, Oracle Workflow Administrator's Guide.

You must include the JAR files containing your custom classes in your CLASSPATH to make the classes accessible to the Java Function Activity Agent. The custom class files should reside on the same platform where the Java Function Activity Agent is run. The Java Function Activity Agent does not need to reside on the same tier as the database, however.

The example in this section is numbered with the notation (1) for easy referencing. The numbers themselves are not part of the procedure.

(1)  package oracle.apps.fnd.wf;

(2)  import java.io.*; 
     import java.sql.*; 
     import java.math.BigDecimal; 
     import oracle.sql.*; 
     import oracle.jdbc.*;

     import oracle.apps.fnd.common.*; 
     import oracle.apps.fnd.wf.engine.*; 
     import oracle.apps.fnd.wf.*; 

(3)  public class className extends WFFunctionAPI {

(4)    public boolean execute(WFContext pWCtx){

(5)      ErrorStack es = pWCtx.getWFErrorStack(); 
         try
         {

(6)        WFAttribute lAAttr = new WFAttribute(); 
           WFAttribute lIAttr = new WFAttribute(); 

(7)        loadActivityAttributes(pWCtx, itemType, 
             itemKey, actID); 
           loadItemAttributes(pWCtx); 

(8)        lAAttr = getActivityAttr("AATTR"); 
           lIAttr = getItemAttr("IATTR"); 

(9)        <your executable statements> 

(10)       lIAttr.value((Object)"NEWVALUE"); 
           setItemAttrValue(pWCtx, lIAttr); 

(11)     }
         catch (Exception e) 
         { 
           es.addMessage("WF","WF_FN_ERROR"); 
           es.addToken("MODULE",this.getClass().getName()); 
           es.addToken("ITEMTYPE",itemType); 
           es.addToken("ITEMKEY",itemKey); 
           es.addToken("ACTID",actID.toString()); 
           es.addToken("FUNCMODE",funcMode); 
           es.addToken("ERRMESSAGE",e.getMessage()); 
           return false; 
         }

(12)     return true; 
       }
     }

(1) By default, Java classes supplied by Oracle Workflow will be in the oracle.apps.fnd.wf package. This section is optional.

(2) For correct operation, you must include the listed packages.

(3) The custom Java class must extend the WFFunctionAPI class. This class provides class variables and methods that are essential to the operation of your function activity.

The parameters that are normally supplied to a PL/SQL function activity are available to the custom class as class variables. They are initialized prior to the call of the boolean execute() method. The resultOut and the errorStack are then passed back to the Oracle Workflow Engine.

The status of the completed activity will be set to COMPLETE unless a value is present in the errorStack variable. If there is a value in this variable, then the activity status will be set to ERROR. The contents of the errorStack variable can be set by using the ErrorStack class within the WFContext class. Refer also to sections 5 and 11 of this API for catching exceptions.

The predefined class variables include:

Variable Description
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. Currently the only supported mode for external Java function activities is the 'RUN' mode.
resultOut If a result type is specified in the activity properties page for the activity in the Oracle Workflow Builder, this parameter represents the expected result that is returned when the procedure completes.

Note: Unlike the resultout for a PL/SQL procedure called by a function activity, the resultOut for a Java procedure does not include a status code. In the Java API, only the result type value is required. The status of the activity will be set automatically by the Workflow Engine depending on whether there is a value in the errorStack variable.

(4) The custom Java class must implement the boolean execute() method. This will be the main entry point for your Java class. On successful completion, this method should return true.

(5) It is important to catch exceptions with your custom Java class and pass them back to the engine via the ErrorStack class. Refer also to section 11 of this API for catching exceptions.

(6) To access item and activity attributes, a WFAttribute class is provided.

(7) The values of the item attributes are not automatically available to the Java class. They are loaded on demand. The values can be loaded explicitly with the void loadItemAttributes(WFContext) or void loadActivityAttributes(WFContext) methods. The values are also loaded implicitly when you call the WFAttribute getItemAttr(String) or WFAttribute getActivityAttr(String) methods. This section is optional.

(8) The actual values of the item and activity attributes are accessed via the WFAttribute getItemAttr(String) and WFAttribute getActivityAttr(String) methods. If you have not explicitly loaded the values of the attributes, they will be automatically loaded at this point.

(9) This section contains your own executable statements. Usually, you add these executable statements after retrieving the required item and activity attribute details (section 8) and before setting item attribute values (section 10).

(10) Setting the value of an item attribute with the void setItemAttrValue(WFContext, WFAttribute) method writes the value of your local WFAttribute to the database. You need to set the values of the WFAttribute class with the WFAttribute.value(Object) method.

(11) It is important to catch exceptions within your custom Java class and pass them back to the engine via the ErrorStack class.

An unsuccessful execution of the external Java function activity will return false.

Note that any message in the WFContext.ErrorStack class variable will be passed back to the Workflow Engine and will cause the activity to be assigned a completion status of ERROR.

(12) A successfully executed external Java function activity will return true.

Standard API for an Item Type Selector or Callback Function

For any given item type, you can define a single function that operates as both a selector and a callback function. A selector function is a PL/SQL procedure that automatically identifies the specific process definition to execute when a workflow is initiated for a particular item type but no process name is provided. Oracle Workflow also supports using a callback function to reset or test item type context information. You can define one PL/SQL procedure that includes both selector and callback functionality by following a standard API.

Oracle Workflow can call the selector/callback function with the following commands:

The standard API for the selector/callback function is as follows. The example in this section is numbered with the notation (1) for easy referencing. The numbers themselves are not part of the procedure.

(1)  procedure <procedure name> 
       (item_type in varchar2,
        item_key in varchar2,
        activity_id in number,
        command in varchar2,
        resultout in out varchar2) is

(2)  <local declarations> 

(3)  begin

     if ( command = 'RUN' ) then
       <your RUN executable statements> 
       resultout := '<Name of process to run>';
       return;
     end if;

(4)  if ( command = 'SET_CTX' ) then
       <your executable statements for establishing 
         context information>  
       return;
     end if;

(5)  if ( command = 'TEST_CTX' ) then
       <your executable statements for testing 
         the validity of the current context information> 
       resultout := '<TRUE or FALSE or NOTSET> ';
       return;
     end if;

(6)  if ( command = '<other command>' ) then
       resultout := ' ';
       return;
     end if;

(7)  exception
       when others then
       WF_CORE.CONTEXT ('<package name>', '<procedure name>', 
         <itemtype>,<itemkey>, 
         to_char(<actid>), <command>);
       raise;

(8)  end <procedure name>;

(1) When the Workflow Engine calls the selector/callback function, it passes four parameters to the procedure and may expect a result when the procedure completes. The parameters are defined here:

Variable Description
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 that this procedure is called from. Note that this parameter is always null if the procedure is called with the 'RUN' command to execute the selector functionality.
command The command that determines how to execute the selector/callback function. Either 'RUN', 'SET_CTX', or 'TEST_CTX'. Other commands may be added in the future.
resultout A result may be returned depending on the command that is used to call the selector/callback function.
If the function is called with 'RUN', the name of the process to run must be returned through the resultout parameter. If the function is called with 'SET_CTX', then no return value is expected. If the function is called with 'TEST_CTX', then the code must return 'TRUE' if the context is correct, 'FALSE' if the context is incorrect, or 'NOTSET' if the context has not been initialized yet. If any other value is returned, Oracle Workflow assumes that this command is not implemented by the callback.

(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 make up your selector function. It executes if the value of command is 'RUN'. One of the executable statements should return a result for the procedure that reflects the process to run. For example, a result can be 'REQUISITION_APPROVAL', which is the name of an example process activity.

(4) This section contains one or more executable statements that set item type context information if the value of command is 'SET_CTX'. The Workflow Engine calls the selector/callback function with this command each time it encounters a new item type and item key combination, before executing any function activities for that combination. This command is useful when you need to set item type context information in a database session before the activities in that session can execute as intended. For example, you might need to set up the responsibility and organization context for function activities that are sensitive to multi-organization data.

(5) This section contains one or more executable statements that validate item type context information if the value of command is 'TEST_CTX'. The Workflow Engine calls the selector/callback function with this command to validate that the current database session context is acceptable before the Workflow Engine executes an activity, such as during background processing. The code in this section should return 'TRUE' if the context is correct, 'FALSE' if the context is incorrect, or 'NOTSET' if the context has not been initialized yet.

(6) This section handles execution modes other than 'RUN', 'SET_CTX' or 'TEST_CTX' as others may be added in the future. Since your function does not need to implement any of these other possible commands, it should simply return null.

(7) 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, Oracle Workflow API Reference.

Standard APIs for "PL/SQL" Documents

You can integrate a document into a workflow process by defining an attribute of type document for an item type, message, or activity. Oracle Workflow supports document types called "PL/SQL" documents, "PL/SQL CLOB" documents, and "PL/SQL BLOB" documents. A PL/SQL document represents data as a character string, a PL/SQL CLOB document represents data as a character large object (CLOB), and a PL/SQL BLOB document represents data as a binary large object (BLOB).

The document-type attribute that you create tells Oracle Workflow how to construct a dynamic call to a PL/SQL procedure that generates the document. You can embed a PL/SQL or PL/SQL CLOB document-type message attribute in a message body to display the document in a notification. You can also attach a PL/SQL, PL/SQL CLOB, or PL/SQL BLOB document-type message attribute to a message to include the document as an attachment to the notification.

The PL/SQL procedures that generate PL/SQL, PL/SQL CLOB, and PL/SQL BLOB documents must follow standard API formats.

Note: If you create a PL/SQL document or a PL/SQL CLOB document that contains HTML, you should take security precautions to ensure that only the HTML code you intend to include is executed. If you retrieve any data from the database at runtime for inclusion in the document, use the WF_CORE.SubstituteSpecialChars() API to substitute entity references for any HTML tag characters in that data, so that those characters will not be interpreted as HTML code and executed. See: SubstituteSpecialChars, Oracle Workflow API Reference.

Note that you should not substitute entity references for HTML tags that you include in the document yourself. Otherwise, the document will not be displayed with your intended HTML formatting. You only need to perform this substitution for data that is retrieved from the database at runtime, which may be entered from an external source.

Related Topics

"PL/SQL" Documents

"PL/SQL CLOB" Documents

"PL/SQL BLOB" Documents

"PL/SQL" Documents

The PL/SQL procedure that generates a PL/SQL document must have the following standard API:

procedure <procedure name> 
  (document_id in varchar2,
   display_type in varchar2,
   document in out nocopy varchar2,
   document_type in out nocopy varchar2)

The arguments for the procedure are as follows:

Variable Description
document_id A string that uniquely identifies a document. This is the same string as the value that you specify in the default value field of the Attribute property page for a "PL/SQL" document (plsql:<procedure>/<document_identifier>). <procedure> should be replaced with the PL/SQL package and procedure name in the form of package.procedure. The phrase <document_identifier> should be replaced with the PL/SQL argument string that you want to pass directly to the procedure. The argument string should identify the document. For example: plsql:po_wf.show_req/2034. If you wish to generate the PL/SQL argument string value dynamically, create another item attribute, and reference that item attribute as "&ITEM_ATTRIBUTE" in place of the PL/SQL argument string. Then before any activity that references this other item attribute gets executed, call the WF_ENGINE.SetItemAttribute API to dynamically set the PL/SQL argument string value. For example: plsql:po_wf.show_req/&POREQ_NUMBER.
display_type One of three values that represents the content type used for the notification presentation, also referred to as the requested type:
  • text/plain - the document is embedded inside a plain text representation of the notification as viewed from an e-mail message. The entire e-mail message must be less than or equal to 32K, so depending on how large your e-mail template is, some of the plain text document that the procedure generates may get truncated. See: Modifying Your Message Templates, Oracle Workflow Administrator's Guide.

  • text/html - the document is embedded inside an HTML representation of the notification as viewed from the Notification Web page, or the HTML attachment to an e-mail message. The procedure must generate an HTML representation of the document of up to 32K, but should not include top level HTML tags like <HTML> or <BODY> since the HTML page that the document is being inserted into already contains these tags. If you include top level HTML tags accidentally, Oracle Workflow removes the tags for you when the document attribute is referenced in a message body. Note that the procedure can alternatively generate a plain text document, as the notification system can automatically surround plain text with the appropriate HTML tags to preserve formatting.

  • '<type>/<subtype>' - the document is presented as a separate attachment to the notification. Any content type may be returned.

document The outbound text buffer where up to 32K of document text is returned.
document_type The outbound text buffer where the document content type is returned. Also referred to as the returned type. If no type is supplied, then 'text/plain' is assumed.

Related Topics

To Define a Document Attribute

"PL/SQL CLOB" Documents

The PL/SQL procedure that generates a PL/SQL CLOB document must have the following standard API:

procedure <procedure name> 
  (document_id in varchar2,
   display_type in varchar2,
   document in out nocopy clob,
   document_type in out nocopy varchar2)

A PL/SQL CLOB document that you include as an attachment to a notification can contain a PDF or RTF document. You should first store the document in the database as a binary large object (BLOB) and then convert the document into a CLOB as part of the PL/SQL procedure that generates the CLOB. You can use the UTL_RAW.Cast_To_VARCHAR2 function to convert the data from the BLOB into VARCHAR2 data that you write to a CLOB. See: UTL_RAW, Oracle Supplied PL/SQL Packages and Types Reference.

If your database version is Oracle9i Database or higher, a PL/SQL CLOB document that you include as an attachment to a notification can also contain any other binary data that is encoded to base64. With this database version, you can optionally use the WF_MAIL_UTIL.EncodeBLOB procedure to encode the PDF, RTF, or other binary data to base64. See: EncodeBLOB, Oracle Workflow API Reference.

Note: You can call WF_NOTIFICATION.WriteToClob() to help build a CLOB by appending a string of character data to it. See: WriteToClob, Oracle Workflow API Reference.

Note: Oracle8i Database does not support base64 encoding, so if you are using Oracle8i Database, the WF_MAIL_UTIL.EncodeBLOB procedure is not available, and you cannot store binary data other than PDF or RTF documents in a PL/SQL CLOB document. This feature is available only if you are using Oracle9i Database or higher.

However, both Oracle8i Database and Oracle9i Database and higher support the UTL_RAW.Cast_To_VARCHAR2 function, so you can store PDF and RTF documents in an attached PL/SQL CLOB document on any of these database versions.

The arguments for the procedure are as follows:

Variable Description
document_id A string that uniquely identifies a document. This is the same string as the value that you specify in the default value field of the Attribute property page for a "PL/SQL CLOB" document (plsqlclob:<procedure>/<document_identifier>). <procedure> should be replaced with the PL/SQL package and procedure name in the form of package.procedure. The phrase <document_identifier> should be replaced with the PL/SQL argument string that you want to pass directly to the procedure. The argument string should identify the document. For example: plsqlclob:po_wf.show_req_clob/2036. If you wish to generate the PL/SQL argument string value dynamically, create another item attribute, and reference that item attribute as "&ITEM_ATTRIBUTE" in place of the PL/SQL argument string. Then before any activity that references this other item attribute gets executed, call the WF_ENGINE.SetItemAttribute API to dynamically set the PL/SQL argument string value. For example: plsqlclob:po_wf.show_req_clob/&POREQ_NUMBER.
display_type One of three values that represents the content type used for the notification presentation, also referred to as the requested type:
  • text/plain - the document is embedded inside a plain text representation of the notification.

  • text/html - the document is embedded inside an HTML representation of the notification as viewed from the Notification Web page. The procedure must generate an HTML representation of the document, but should not include top level HTML tags like <HTML> or <BODY> since the HTML page that the document is being inserted into already contains these tags. If you include top level HTML tags accidentally, Oracle Workflow removes the tags for you when the document attribute is referenced in a message body. Note that the procedure can alternatively generate a plain text document, as the notification system can automatically surround plain text with the appropriate HTML tags to preserve formatting.

  • '<type>/<subtype>' - the document is presented as a separate attachment to the notification. Any content type may be returned.

document The outbound LOB locator pointing to where the document text is stored. This locator is a temporary LOB locator, so you must write your document text to this locator rather than replacing its value. If this value is overwritten, the temporary LOB is not implicitly freed. For more information about LOB locators and storing CLOB data, see DBMS_LOB - Operational Notes, Oracle Database PL/SQL Packages and Types Reference and Temporary LOBs, Oracle Application Developer's Guide - Large Objects (LOBs).
document_type The outbound text buffer where the document content type is returned. Also referred to as the returned type. If no type is supplied, then 'text/plain' is assumed. For a PDF or RTF document, this argument should specify an appropriate Multi-purpose Internet Mail Extension (MIME) type, such as 'application/pdf' or 'application/rtf'. You can also optionally specify a file name for the attachment as part of this argument. Use a semicolon (';') to separate the file name from the preceding value in the argument, and specify the file name in the format 'name=<filename>' with no spaces before or after the equal sign ('='). For example, you can set a value for the document type with the following command:
document_type := 'application/pdf; name=filename.pdf';

Note: If your database version is Oracle9i Database or higher, and you are using the WF_MAIL_UTIL.EncodeBLOB API to encode binary data to base64 in order to store the data in this PL/SQL CLOB document, then the document_type parameter should specify an appropriate MIME type with a primary type of either application or image, such as 'application/doc', 'application/pdf', 'image/jpg', or 'image/gif'. The MIME type must be followed by a semicolon (';') and then by the encoding specification 'encoding=base64' with no spaces before or after the equal sign. You can also optionally specify a file name for the attachment as part of this argument. Use a semicolon (';') to separate the file name from the preceding value in the argument, and specify the file name in the format 'name=<filename>' with no spaces before or after the equal sign ('='). For example, you can set a value for the document type with the following command:

document_type := 'image/jpg; encoding=base64; name=filename.jpg';

Example

The following example shows a sample procedure to produce a PL/SQL CLOB document that contains a PDF or RTF document, using the UTL_RAW.Cast_To_VARCHAR2 function to convert a BLOB to a CLOB.

procedure cdoc 
  (document_id in varchar2,
   display_type in varchar2,
   document in out nocopy clob,
   document_type in out nocopy varchar2)

is

  bdoc blob;
  cdoc clob;
  content_type varchar2(100);

  lob_id number;
  amount number;

  bdoc_size number;
  block number := 10000;
  blockCount number;
  rawBuff RAW(32000);
  pos number;
  charBuff varchar2(32000);
  charBuff_size number;

  filename varchar2(200);
  location varchar2(200);
  data_type varchar2(100);

begin

  dbms_lob.createTemporary(cdoc, FALSE, dbms_lob.call);

  -- Determine the document to display from the 
  -- Document ID
  lob_id := to_number(document_id);

  -- Obtain the BLOB version of the document
  select filename, location, content_type, data_type, bdata 
  into filename, location, content_type, data_type, bdoc
  from sjm_lobs 
  where id = lob_id;

  -- recast the BLOB to a CLOB   
  bdoc_size := dbms_lob.getLength(bdoc);
  if block < bdoc_size then
    blockCount := round((bdoc_size/block)+0.5);
  else 
    blockCount := 1;
  end if;
  pos := 1;
  for i in 1..blockCount loop
    dbms_lob.read(bdoc, block, pos, rawBuff);
    charBuff := utl_raw.cast_to_varchar2(rawBuff);
    charbuff_size := length(charBuff);
    dbms_lob.writeAppend(cdoc, charbuff_size, charBuff);
    pos := pos + block;
  end loop;

  -- Now copy the content to the document
  amount := dbms_lob.getLength(cdoc);
  dbms_lob.copy(document, cdoc, amount, 1, 1);

  -- Set the MIME type as a part of the document_type.
  document_type := content_type||'; name='||filename;

  exception
    when others then 
      wf_core.context('LOBDOC_PKG', 'cdoc', 
        document_id, display_type);
      raise;

end cdoc;

Related Topics

To Define a Document Attribute

"PL/SQL BLOB" Documents

The PL/SQL procedure that generates a PL/SQL BLOB document must have the following standard API:

procedure <procedure name> 
  (document_id in varchar2,
   display_type in varchar2,
   document in out nocopy blob,
   document_type in out nocopy varchar2)

The arguments for the procedure are as follows:

Variable Description
document_id A string that uniquely identifies a document. This is the same string as the value that you specify in the default value field of the Attribute property page for a "PL/SQL BLOB" document (plsqlblob:<procedure>/<document_identifier>). <procedure> should be replaced with the PL/SQL package and procedure name in the form of package.procedure. The phrase <document_identifier> should be replaced with the PL/SQL argument string that you want to pass directly to the procedure. The argument string should identify the document. For example: plsqlblob:po_wf.show_req_blob/2038. If you wish to generate the PL/SQL argument string value dynamically, create another item attribute, and reference that item attribute as "&ITEM_ATTRIBUTE" in place of the PL/SQL argument string. Then before any activity that references this other item attribute gets executed, call the WF_ENGINE.SetItemAttribute API to dynamically set the PL/SQL argument string value. For example: plsqlblob:po_wf.show_req_blob/&POREQ_NUMBER.
display_type For a PL/SQL BLOB document, this value should be ' ' to represent the content type used for the notification presentation, also referred to as the requested type:
'<type>/<subtype>' - the document is presented as a separate attachment to the notification. Any content type may be returned.
document The outbound LOB locator pointing to where the document data is stored. This locator is a temporary LOB locator, so you must write your document data to this locator rather than replacing its value. If this value is overwritten, the temporary LOB is not implicitly freed. For more information about LOB locators and storing BLOB data, see DBMS_LOB - Operational Notes, Oracle Database PL/SQL Packages and Types Reference and Temporary LOBs, Oracle Application Developer's Guide - Large Objects (LOBs).
document_type The outbound text buffer where the document content type is returned. Also referred to as the returned type. If no type is supplied, then 'text/plain' is assumed. This argument should specify an appropriate MIME type with a primary type of either application or image, such as 'application/doc', 'application/pdf', 'image/jpg', or 'image/gif'. You can also optionally specify a file name for the attachment as part of this argument. Use a semicolon (';') to separate the file name from the preceding value in the argument, and specify the file name in the format 'name=<filename>' with no spaces before or after the equal sign ('='). For example, you can set a value for the document_type with the following command:
document_type := 'image/jpg; name=filename.jpg';

Example

The following example shows a sample procedure to produce a PL/SQL BLOB document that contains a binary file such as an image or a PDF document.

Note: This example shows a simple API used for testing purposes. In a production instance, the API that produces a PL/SQL BLOB document should further validate the syntax of the document_type parameter when appending the base64 encoding specification.

/* API to produce a PL/SQL BLOB document for use as a  
   notification attachment based on the specified 
   document ID. */

procedure getBlobDoc
  (document_id in varchar2,
   content_type in varchar2,
   document in out nocopy blob,
   document_type in out nocopy varchar2)

is

  l_docid  pls_integer;
  l_filename varchar2(100);
  l_errmsg varchar2(100) := 
    'The document is not found in the database';
  l_bdoc blob;
  l_data_type varchar2(100);

begin

  -- Determine the document to display from the 
  -- Document ID
  l_docid := to_number(document_id);

  -- Obtain the BLOB version of the document
  select filename, content_type, bdata
  into l_filename, document_type, l_bdoc
  from wftst_lobs
  where id = l_docid;

  -- Set the encoding as a part of the document_type
  document_type := document_type || '; encoding=base64;';

  -- Now copy the content to the document
  dbms_lob.Copy(document, l_bdoc, dbms_lob.getLength(l_bdoc));

exception
  when others then
    dbms_lob.WriteAppend(document, length(l_errmsg), l_errmsg);
    wf_core.context('WFMLR_TEST','getBLOBDoc',document_id);
    raise;

end getBlobDoc;

Related Topics

To Define a Document Attribute

Standard API for an Event Data Generate Function

When you define an event in the Business Event System, you can assign the event a generate function that can produce the complete event data from the event name, event key, and an optional parameter list. The event data gives additional details to describe what occurred and can be structured as an XML document. You should specify a generate function if the application that raises the event will not produce the event data itself.

When an event is raised locally, the Event Manager checks each subscription before executing it to determine whether the subscription requires the event data. If the event data is required but is not already provided, the Event Manager calls the generate function for the event to produce the event data. The generate function returns the event data in character large object (CLOB) format.

Note: If the event data is required but no generate function is defined for the event, Oracle Workflow creates a default set of event data using the event name and event key.

Note: If the generate function is costly, and you want to return control to the calling application more quickly after raising the event, you can defer all the subscriptions that require the complete event data. Then the Event Manager will not run the generate function until those subscriptions are executed at a later time. See: Deferred Subscription Processing.

Oracle Workflow provides a standard generate function that you can use for demonstration or testing purposes. See: Default_Generate, Oracle Workflow API Reference.

Related Topics

To Define an Event

To Define an Event Subscription

Parameter List Structure, Oracle Workflow API Reference

Standard API for a PL/SQL Generate Function

A PL/SQL generate function must have the following standard API:

function <function_name> 
  (p_event_name in varchar2,
   p_event_key in varchar2
   p_parameter_list in wf_parameter_list_t default null) 
  return clob;

The arguments for the function are as follows:

Variable Description
p_event_name The internal name of the event.
p_event_key A string generated when the event occurs within a program or application. The event key uniquely identifies a specific instance of the event.
p_parameter_list An optional list of additional parameter name and value pairs for the event.

Standard API for a Java Generate Function

In Oracle Applications only, you can assign an event a Java generate function to be executed in the middle tier, instead of a PL/SQL generate function. A Java generate function must be a Java class using the following Java interface:

public interface GenerateInterface
{
  public String generate(BusinessEvent event, 
      WorkflowContext context)
    throws BusinessEventException;
}

The arguments for the API are as follows:

Variable Description
event The BusinessEvent object. The payload passed to the BusinessEvent class must be serializable.
context Workflow context information, including the Log object which can be used for logging.

For more information, see the Oracle Workflow Javadoc.

Standard APIs for a Queue Handler

When you define an agent in the Business Event System, you must assign the agent a queue handler. The queue handler is a package that translates between the standard Workflow event message format defined by the WF_EVENT_T datatype and the message format required by the queue associated with the agent. In Oracle Applications, a Java queue handler translates between the BusinessEvent object format and the queue's message format.

Oracle Workflow provides two standard queue handlers for queues that use the WF_EVENT_T format, WF_EVENT_QH for normal processing and WF_ERROR_QH for error queues. Oracle Workflow also provides a standard queue handler named WF_EVENT_OJMSTEXT_QH for queues that use JMS Text messages as their payload format.

You can also create your own custom queue handlers for queues that use other formats. If you create a custom queue handler, you must provide standard enqueue and dequeue APIs in your package. Java queue handlers must also include some other standard APIs.

Related Topics

Event Message Structure, Oracle Workflow API Reference

Agents (for standalone Oracle Workflow)

Agents (for Oracle Applications)

Mapping Between WF_EVENT_T and SYS.AQ$_JMS_TEXT_MESSAGE, Oracle Workflow API Reference

Standard APIs for a PL/SQL Queue Handler

Enqueue

The Enqueue procedure in a queue handler package must enqueue an event message onto a queue associated with an outbound agent. You can optionally specify an override agent where you want to enqueue the event message. Otherwise, the event message is enqueued on the From Agent specified within the message. The Enqueue procedure transforms the event message's header information if necessary to enqueue the message in the format required by the queue.

When an event message is being sent, the generic WF_EVENT.Enqueue procedure determines which queue handler is associated with the specified outbound agent and calls the Enqueue procedure in that queue handler to enqueue the message.

The PL/SQL Enqueue procedure must have the following standard API:

procedure enqueue 
  (p_event in WF_EVENT_T,
   p_out_agent_override in WF_AGENT_T);

The arguments for the procedure are as follows:

Variable Description
p_event The event message.
p_out_agent_override The outbound agent on whose queue the event message should be enqueued.

Dequeue

The Dequeue procedure in a queue handler package must dequeue an event message from the queue associated with the specified inbound agent, selecting the message to dequeue by the message priority. The procedure transforms the event message's header information if necessary and returns the event message in the standard WF_EVENT_T structure. Additionally, the Dequeue procedure can set the date and time when the message is dequeued into the RECEIVE_DATE attribute of the event message.

When an event message is being received, the WF_EVENT.Listen procedure determines which queue handler to use with the specified inbound agent and calls the Dequeue procedure in that queue handler to dequeue the message.

The PL/SQL Dequeue procedure must have the following standard API:

procedure dequeue 
  (p_agent_guid in raw,
   p_event out WF_EVENT_T);

The arguments for the procedure are as follows:

Variable Description
p_agent_guid The globally unique identifier of the inbound agent from whose queue the event message should be dequeued.
p_event The event message.

Standard APIs for a Java Queue Handler

In Oracle Applications only, you can provide a Java queue handler to be executed during Java event message processing in the middle tier, instead of a PL/SQL queue handler. A Java queue handler must be a Java class using the following Java interface:

public interface QueueHandlerInterface
{
  public void init(Connection conn,
                   AgentEO agent,
                   Log log,  
                   String uniqueLogId,
                   Properties props)
    throws QueueHandlerException;

  public String enqueue(BusinessEvent event) 
    throws QueueHandlerException;

  public BusinessEvent dequeue()
    throws QueueHandlerException;

  public void destroy(); 

  public String getMsgId()
    throws QueueHandlerException;

  public CLOB getEventData();
}

In addition to the enqueue and dequeue APIs, a Java queue handler must also contain methods to initialize the connection to the agent, destroy objects created during queue processing after the processing is complete, retrieve a message ID from a message on the queue, and retrieve the CLOB reference to the event data of the last business event dequeued by the queue handler.

The arguments for the init method are as follows:

Variable Description
conn The JDBC connection used to establish the connection with the queue.
agent The AgentEO object that contains the information for this agent and its queue.
log The Log object which can be used for logging.
uniqueLogId The log ID for recording debug messages.
props The property mapping for enqueue and dequeue operations.

The argument for the enqueue method is as follows:

Variable Description
event The BusinessEvent object to be enqueued.

For more information, see the Oracle Workflow Javadoc.

Standard API for an Event Subscription Rule Function

When you define an event subscription, you choose a function called a rule function to run on the event message. Oracle Workflow provides a standard default rule function named WF_RULE.Default_Rule to perform basic subscription processing. The default rule function includes the following actions:

See: Default_Rule, Oracle Workflow API Reference.

Oracle Workflow also provides additional standard rule functions that you can use for advanced processing, testing, debugging, or other purposes. Commonly used rule functions including the following:

See: Event Subscription Rule APIs, Oracle Workflow API Reference.

You can extend your subscription processing by creating custom rule functions. Custom rule functions must be defined according to a standard API.

A rule function may read from or write to the event message or perform any other database action. However, you should never commit within a rule function. The Event Manager never issues a commit as it is the responsibility of the calling application to commit. Additionally, the rule function must not change the connection context in any way, including security and NLS settings.

Note: If your rule function writes to the event message, any subsequent subscriptions executed on the event will access the changed message.

If the subscription processing that you want to perform for an event includes several successive steps, you may find it advantageous to define multiple subscriptions to the event with simple rule functions that you can reuse, rather than creating complex specialized rule functions that cannot be reused. You can use the phase numbers for the subscriptions to specify the order in which they should be executed.

By default, the Event Manager uses the event key as the correlation ID for the event message when no other correlation ID is specified. If you want to specify a different correlation ID, you can use WF_EVENT_FUNCTIONS_PKG.AddCorrelation to add a correlation ID to the event message, either by calling this function within your custom rule function or by defining another subscription that uses WF_EVENT_FUNCTIONS_PKG.AddCorrelation as its rule function. See: AddCorrelation, Oracle Workflow API Reference.

If you want to send the event message to a workflow process or to an agent after running custom code on the message, you must either include the send processing in your rule function, or define a separate subscription that uses the default rule function to perform the send processing.

Note: When you define a subscription in the Event Manager, you can define the workflow item type, workflow process name, out agent, to agent, priority, and parameters for your send processing, as well as defining the rule function. Any rule function can access these send attributes, but if you do not use the default rule function, you must explicitly include the send processing in your custom rule function if you want to send the event from the same subscription.

Related Topics

To Define an Event Subscription

Event Message Structure, Oracle Workflow API Reference

Workflow Core APIs, Oracle Workflow API Reference

Event Subscription Rule APIs, Oracle Workflow API Reference

Event(), Oracle Workflow API Reference

Send(), Oracle Workflow API Reference

Default_Rule, Oracle Workflow API Reference

SetErrorInfo(), Oracle Workflow API Reference

Standard API for a PL/SQL Subscription Rule Function

The standard API for a PL/SQL rule function is as follows. This section is numbered with the notation (1) for easy referencing. The numbers themselves are not part of the procedure.

(1)  function <function_name> 
       (p_subscription_guid in raw,
        p_event in out WF_EVENT_T) 
       return varchar2 is

(2)  <local declarations> 

(3)  begin
       <your executable statements> 

(4)  <optional code for WARNING> 
       WF_CORE.CONTEXT('<package name>', 
                       '<function name>', 
                       p_event.getEventName( ), 
                       p_subscription_guid);
       WF_EVENT.setErrorInfo(p_event, 'WARNING');
       return 'WARNING';

(5)  return 'SUCCESS';

(6)  exception
       when others then
         WF_CORE.CONTEXT('<package name>', 
                         '<function name>', 
                         p_event.getEventName( ), 
                         p_subscription_guid);
         WF_EVENT.setErrorInfo(p_event, 'ERROR');
         return 'ERROR';

(7)  end;

(1) When the Event Manager calls the rule function, it passes two parameters to the function and expects a return code when the function completes. The parameters are defined here:

Variable Description
p_subscription_guid The globally unique identifier for the subscription.
p_event The event message.

The function must return one of the following status codes:

(2) This section declares any local arguments that are used within the function.

(3) The procedure body begins in this section with one or more executable statements that make up your rule function.

(4) This optional section calls WF_CORE.CONTEXT() if a warning condition occurs, so that you can include context information in the error stack to help you locate the source of an error. It also sets the warning information into the event message and returns the status code 'WARNING'. See: CONTEXT, Oracle Workflow API Reference.

(5) This section returns the status code 'SUCCESS' when the rule function's normal processing completes successfully.

(6) 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. It also sets the error information into the event message and returns the status code 'ERROR'. See: CONTEXT, Oracle Workflow API Reference.

Note: In standalone Oracle Workflow, if you raise an unhandled exception in the rule function, the Event Manager rolls back all subscription processing for the event and raises the exception to the calling application. In this case the event message is not placed on the WF_ERROR queue.

In Oracle Applications, the Event Manager's treatment of an unhandled exception in a rule function depends on the type of error handling specified for the subscription.

Standard API for a Java Subscription Rule Function

In Oracle Applications only, you can provide a Java subscription rule function to be executed in the middle tier, instead of a PL/SQL rule function. A Java rule function must be a Java class using the following Java interface:

public interface SubscriptionInterface
{
  void onBusinessEvent(Subscription eo, BusinessEvent event,
                       WorkflowContext ctx)
    throws BusinessEventException;
}

The arguments for the API are as follows:

Variable Description
eo The Subscription object, which provides information about the subscription such as the phase number and any parameters.
event The BusinessEvent object, which provides information about the business event that occurred, including the event name, event key, event data, and the optional payload object.
ctx Workflow context information, including the database connection and the Log object which can be used for logging.

If the execution of the rule function does not succeed, the subscription can pass the error details to the Event Manager by encapsulating the details in a BusinessEventException. Depending on the error handling specified for the subscription, the Event Manager can either halt subscription processing for this event and roll back any subscriptions already executed for the event, or roll back only the errored subscription and continue processing the next subscription for the event.

Ensure that your rule function Java class is part of the JAVA_TOP that is included in the AF_CLASSPATH on the concurrent processing tier for your Oracle Applications installation.

Note: When raising an event from Java, you can also set a serializable object as a payload for the event. The payload is available to all Java subscriptions to the event.

For more information, see the Oracle Workflow Javadoc.