The Item import Interface(IOI) reads data from following tables for importing items and item details. The MTL_SYSTEMS_ITEM_INTERFACE table is used for new item numbers
and all item attributes. This is the main item interface table, and can be
the only table used to import items. MTL_ITEM_REVISIONS_INTERFACE is used if Item revisions history is also loaded with items. Item categories can be imported using MTL_ITEM_CATEGORIES_INTERFACE.
The import error can be tracked using MTL_INTERFACE_ERRORS table. The transaction_id and request_id populated by the import program can be used to link interface table and error table.

Required columns in MTL_SYSTEM_ITEMS_INTERFACE
PROCESS_FLAG = 1 (The column is used to identify status of record)
TRANSACTION_TYPE = ‘CREATE’ or ‘UPDATE’
SET_PROCESS_ID = any numeric value (This is not a required column but for performance it is advised to use this column and then run import program for the value entered here)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org.
DESCRIPTION = ‘Description of the item’
ITEM_NUMBER and/or SEGMENT(n) = If using item_number then each segment value should be entered concatenated by segment seperator. If Item revisions history is also being loaded then Item_number should be populated.
LIST_PRICE_PER_UNIT = If material cost is to be populated for an item along with item import .

Required columns in MTL_ITEM_REVISIONS_INTERFACE table. The table is only used if Item revision is to be loaded in the same run with IOI. If this table is not used then items are created with the default revision setup for an organization.
PROCESS_FLAG = 1
TRANSACTION_TYPE = ‘CREATE’
SET_PROCESS_ID = any numeric value(Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org.
REVISION
EFFECTIVITY_DATE
IMPLEMENTATION_DATE
ITEM_NUMBER = Same as item_number in mtl_system_items_interface table.
Each row in the mtl_item_revisions_interface table must have the REVISION and
EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.

Required columns for MTL_ITEM_CATEGORIES_INTERFACE table.
TRANSACTION_TYPE = ‘CREATE’
SET_PROCESS_ID = any numeric value(Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org
ITEM_NUMBER/INVENTORY_ITEM_ID or both
CATEGORY_SET_NAME or CATEGORY_SET_NAME or both
CATEGORY_ID or CATEGORY_NAME or both

For performance purpose, it is advised to batch set of records using set_process_id column and then run import program for that set_process_id. The item import(IOI) program can be run in parallel if seperate set_process_ids are passed while submitting. The IOI automatically separates Master records from Child, and
processes Master records first. However, as one IOI process is not aware of
other IOI processes running in parallel, do not split a given item’s separate
Organization records into two different SET_PROCESS_IDs that are being run in
parallel.

Item import program can be run in 2 modes INSERT & UPDATE.
The method to update Item attribute columns to NULL is to use the following values:
· for Numeric fields: insert -999999
· for Character fields: insert ‘!’

Parameters for a PL/SQL :

When the Workflow Engine calls a stored procedure for a unction activity, it passes four parameters to the procedure and may expect a result when the procedure completes.

Itemtype (IN) : Internal name of the item
Itemkey (IN) : A string to uniquely identify the item type instance
Actid (IN) : ID of the activity from where this procedure is called.
Funcmode (IN) : Execution mode of the activity
Resultout (OUT) : Result Type of the activity

FUNCMODE : For functions, it can be RUN or CANCEL
For notifications, it can be RESPOND, FORWARD, TRANSFER or TIMEOUT

RESULTOUT :
COMPLETE:: Activity successful.
WAITING : Pending for some other activity
DEFERRED: : Activity deferred till
NOTIFIED::: Activity notified to with a . Externally, must be completed using WF_ENGINE.CompleteActivity.
ERROR: :Activity encounters an error

PL/SQL Coding Standards

Procedure
( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out varchar2 )
is
;
Begin
if ( funcmode = ’RUN’ ) then
resultout := ’COMPLETE:’;
return;
end if;
if ( funcmode = ’CANCEL’ ) then
resultout := ’COMPLETE’;
return;
end if;
if ( funcmode = ’RESPOND’ ) then
resultout := ’COMPLETE’;
return;
end if;
if ( funcmode = ’FORWARD’ ) then
resultout := ’COMPLETE’;
return;
end if;
if ( funcmode = ’TIMEOUT’ ) then
resultout := ’COMPLETE’;
return;
end if;
if ( funcmode = ’TRANSFER’ ) then
resultout := ’COMPLETE’;
return;
end if;
Exception
When others then
WF_CORE.CONTEXT (’’, ’’, , , to_char(),
);
raise;
End ;

==========================================================================

Example for Custom Requisition WF :

PROCEDURE Req_App_Process(Requisitionid IN VARCHAR2,
RequisitionDesc IN VARCHAR2,
RequisitionAmount IN NUMBER,
RequestorUsername IN VARCHAR2,
ProcessOwner IN VARCHAR2,
Workflowprocess IN VARCHAR2 DEFAULT NULL,
item_type IN VARCHAR2 DEFAULT NULL ) IS

ItemType VARCHAR2(30) := NVL(item_type,’WFDEMO’);
ItemKey VARCHAR2(30) := RequisitionNumber;
ItemUserKey VARCHAR2(80) := RequisitionDesc;

BEGIN

— Start Process :
— If workflowprocess is passed, it will be run.
— If workflowprocess is NOT passed, the selector function
— defined in the item type will determine which process to run.

Wf_Engine.CreateProcess( ItemType => ItemType,
ItemKey => ItemKey,
process => WorkflowProcess );

Wf_Engine.SetItemUserKey (ItemType => ItemType,
ItemKey => ItemKey,
UserKey => ItemUserKey);

— Initialize workflow item attributes

Wf_Engine.SetItemAttrText (itemtype => itemtype,
itemkey => itemkey,
aname => ‘REQUISITION_NUMBER’,
avalue => RequisitionNumber);

Wf_Engine.SetItemAttrText (itemtype => itemtype,
itemkey => itemkey,
aname => ‘REQUISITION_DESCRIPTION’,
avalue => ItemUserKey);
Wf_Engine.SetItemAttrNumber( itemtype => itemtype,
itemkey => itemkey,
aname => ‘REQUISITION_AMOUNT’,
avalue => RequisitionAmount );

Wf_Engine.SetItemAttrText (itemtype => itemtype,
itemkey => itemkey,
aname => ‘REQUESTOR_USERNAME’,
avalue => RequestorUsername);
Wf_Engine.SetItemOwner ( itemtype => itemtype,
itemkey => itemkey,
owner => ProcessOwner );

Wf_Engine.StartProcess( itemtype => itemtype,
itemkey => itemkey );
EXCEPTION
WHEN OTHERS THEN

Wf_Core.context (‘WF_REQDEMO’,’StartProcess’,RequisitionNumber,RequisitionAmount,
RequestorUsername,ProcessOwner,Workflowprocess);
RAISE;

END Req_App_Process;
========================================================================

Allow Modification to Customized Objects

“Allow modifications of customized objects” check box in the “About Oracle Workflow Builder”

When Checked :
The range of editable access levels can appear as a combination of solid green and crosshatch grey areas.
The levels depicted by grey crosshatches represent levels that usually cannot modify customized objects, but can now do so because Oracle Workflow Builder is operating in ’upload’ mode.
Upload mode means that Oracle Workflow Builder can save your edits, overwriting any protected objects that you have access to modify as well as any previously customized objects.

When Unchecked :
The range of editable access levels appears as a solid green area.
This indicates that when you save your work, Oracle Workflow Builder is operating in ’upgrade’ mode, only saving edits to protected objects that you have access to change and leaving objects that have been previously customized untouched.
Note: An object appears with a small red lock over its icon in the navigator tree to indicate that it is a read–only if you are operating at an access level that does not have permission to edit an object, that is, your access level is in a white area of the Range of Editable Access Levels’ indicator bar.

Default Protection Levels :
The following range of levels are presumed by Oracle Workflow:
0-9 Oracle Workflow
10-19 Oracle Application Object Library
20-99 Oracle Applications development
100-999 Customer organization.
You can determine how you want this range to be interpreted. For example, 100 can represent headquarters, while 101 can represent a regional office, and so on.
1000 Public

WorkFlow Commands and Instructions:
————
The workflow definitions are stored into the database. The standard workflow files (wft) files are in
the $PA_TOP/patch/115/import/US/ and $PO_TOP/patch/115/import/US. The custom files are located in
$PA_TOP/patch/115/import and $PO_TOP/patch/115/import respectively.

When ever any patch is applied that impacts the workflow customizations, follow the below steps to
reapply the customizations.

1) Log on to the environment on which the customizations need to be applied.
2) Set the environment.
Ex: ./oracle/devl/app/prodappl/APPSDEVL_mmpsodevl.env
3) Fire the workflow definition download command to download the workflow definition from the database

How to Download Workflow from Oracle Applications?

DOWNLOAD WORKFLOW:

WFLOAD apps/apps 0 Y DOWNLOAD $HOME/PAAPINVW.wft PAAPINVW

WFLOAD apps/apps 0 Y DOWNLOAD $HOME/poxwfpag.wft POWFPOAG

The above commands creates the workflow definition files (wft) in the local directory $HOME/

4) Verify whether the workflow changes are intact.

5) If the workflow definitions are overwritten then apply the custom workflow definitions from the
mentioned directory.

How to Upload Workflow from Oracle Applications?

UPLOAD WORKFLOW:

WFLOAD apps/apps 0 Y UPLOAD $PA_TOP/patch/115/import/PAAPINVW.wft

WFLOAD apps/apps 0 Y UPLOAD $PO_TOP/patch/115/import/poxwfpag.wft

verify the log and output file for any errors.

Note: If the customizations needed to be appended to the standard workflow then, modify the standard
workflow and add the customizations on top of it.

Oracle Workflow APIs:

1 WF_ENGINE
2 WF_CORE
3 WF_PURGE
4 WF_DIRECTORY
5 WF_PREF
6 WF_MONITOR
7 Oracle Workflow Views
8 WF_QUEUE
9 FND_DOCUMENT_MANAGEMENT
10 WF_NOTIFICATIONS

WF_ENGINE APIs:

The Workflow Engine APIs can be called by an application program or a workflow function in the runtime phase to communicate with the engine and to change the status of each of the activities. These APIs are
defined in a PL/SQL package called WF_ENGINE.

CreateProcess (itemtype in varchar2,itemkey in varchar2,process in varchar2 default );
Creates a new runtime process for an application item. For example, a Requisition item type may have a Requisition Approval Process as a top level process. When a particular requisition is created, an application calls CreateProcess to set up the information needed to start the defined process.

SetItemUserKey (itemtype in varchar2,itemkey in varchar2, userkey in varchar2);
Lets you set a user–friendly identifier for an item in a process, which is initially identified by an item type and item key. The user key is intended to be a user–friendly identifier to locate items in the Workflow
Monitor and other user interface components of Oracle Workflow.

GetItemUserKey (itemtype in varchar2,itemkey in varchar2) return varchar2;
Returns the user–friendly key assigned to an item in a process, identified by an item type and item key. The user key is a user–friendly identifier to locate items in the Workflow Monitor and other user interface components of Oracle Workflow.

GetActivityLabel (actid in number) return varchar2;
Returns the instance label of an activity, given the internal activity instance ID. The label returned has the following format, which is suitable for passing to other Workflow Engine APIs, such as CompleteActivity and HandleError, that accept activity labels as arguments: :

SetItemOwner (itemtype in varchar2,itemkey in varchar2,owner in varchar2);
A procedure to set the owner of existing items. The owner must be a valid role. Typically, the role that initiates a transaction is assigned as the process owner, so that any participant in that role can find and view
the status of that process instance in the Workflow Monitor.

StartProcess (itemtype in varchar2,itemkey in varchar2);
Begins execution of the specified process. The engine locates the activity marked as START and then executes it. CreateProcess( ) must first be called to define the itemtype and itemkey before calling
StartProcess( ).

LaunchProcess (itemtype in varchar2,itemkey in varchar2,process in varchar2 default ”,userkey in varchar2 default ”,owner in varchar2 default ”);
Launches a specified process by creating the new runtime process and beginning its execution. This is a wrapper that combines CreateProcess and StartProcess.

SuspendProcess (itemtype in varchar2,itemkey in varchar2,process in varchar2 default ”);
Suspends process execution so that no new transitions occur. Outstanding notifications can complete by calling CompleteActivity( ), but the workflow does not transition to the next activity. Restart suspended processes by calling ResumeProcess( ). ResumeProcess(itemtype in varchar2,itemkey in varchar2,process in varchar2 default ”); Returns a suspended process to normal execution status. Any activities that were transitioned to while the process was suspended are now executed.

AbortProcess (itemtype in varchar2,itemkey in varchar2,process in varchar2 default ”,result in varchar2 default eng_force);
Aborts process execution and cancels outstanding notifications. The process status is considered COMPLETE, with a result specified by the result argument. Also, any outstanding notifications or subprocesses are set to a status of COMPLETE with a result of force, regardless of the result argument.

CreateForkProcess (copy_itemtype in varchar2,copy_itemkey in varchar2,new_itemkey in varchar2,same_version in boolean default TRUE);
Forks a runtime process by creating a new process that is a copy of the original. After calling CreateForkProcess(), you can call APIs such as SetItemOwner(), SetItemUserKey(), or the SetItemAttribute APIs to reset any item properties or modify any item attributes that you want for the new process. Then you must call StartForkProcess() to start the new process. Use CreateForkProcess() when you need to change item specific attributes during the course of a process. For example, if an order cannot be met due to insufficient inventory stock, you can use CreateForkProcess() to fork a new transaction for the backorder quantity. Note that any approval notification will be copied. The result is as if two items were created for this transaction.

StartForkProcess(itemtype in varchar2,itemkey in varchar2);
Begins execution of the new forked process that you specify. Before you call StartForkProcess( ), you must first call CreateForkProcess( ) to create the new process. You can modify the item attributes of the new
process before calling StartForkProcess().
If the new process uses the same version as the original, StartForkProcess() copies the status and history of each activity in the forked process, activity by activity. If the new process uses the latest version, then StartForkProcess() executes StartProcess().
If you call StartForkProcess() from within a process, any function activity in the process that had a status of ‘Active’ is updated to have a status of ‘Notified.’ You must call CompleteActivity() afterwards to continue the process.
StartForkProcess() automatically refreshes any notification attributes that are based on item attributes. Any open notifications in the original process are copied and sent again in the new process. Closed notifications are copied but not resent; their status remains remains ‘Complete.’
Any Wait activities in the new process are activated at the same time as the original activities. For example, if a 24 hour Wait activity in the original process is due to be eligible in two hours, the new Wait activity
is also eligible in two hours.

Background (itemtype in varchar2,minthreshold in number default null,
maxthreshold in number default null,process_deferred in boolean default TRUE,process_timeout in boolean default TRUE);
Runs a background engine for processing deferred and/or timed out activities using the parameters specified. The background engine executes all activities that satisfy the given arguments at the time that
the background engine is invoked. This procedure does not remain running long term, so you must restart this procedure periodically. Any activities that are newly deferred or timed out after the current background engine starts are processed by the next background engine that is invoked. You may run a script called wfbkgchk.sql to get a list of the activities waiting to be processed by the next background engine run. If you are using the standalone version of Oracle Workflow, you can use one of the sample background engine looping scripts described below or create your own script to make the background engine procedure loop indefinitely. If you are using the version of Oracle Workflow embedded in Oracle Applications, you can use the concurrent program version of this procedure and take advantage of the concurrent manager to schedule the background engine to run periodically.

AddItemAttr (itemtype in varchar2,itemkey in varchar2,aname in varchar2);
Adds an empty item type attribute variable to the process. Although most item type attributes are defined at design time, developers can create new attributes at runtime for a specific process.

SetItemAttrText(itemtype in varchar2,itemkey in varchar2,aname in varchar2,avalue in varchar2);
SetItemAttrNumber(itemtype in varchar2,itemkey in varchar2,aname in varchar2,avalue in number);
SetItemAttrDate (itemtype in varchar2,itemkey in varchar2,aname in varchar2,avalue in date);
Sets the value of an item type attribute in a process. Use the correct procedure for your attribute type. All attribute types except number and date use SetItemAttrText.

SetItemAttrDocument(itemtype in varchar2,itemkey in varchar2,aname in varchar2,documentid in varchar2);
Sets the value of an item attribute of type document, to a document identifier.

GetItemAttrText(itemtype in varchar2,itemkey in varchar2,aname in varchar2) return varchar2;
GetItemAttrNumber(itemtype in varchar2,itemkey in varchar2,aname in varchar2) return number;
GetItemAttrDate(itemtype in varchar2,itemkey in varchar2,aname in varchar2) return date;
Returns the value of an item type attribute in a process. Use the correct function for your attribute type. All attribute types except number and date use GetItemAttrText.

GetItemAttrDocument(itemtype in varchar2,itemkey in varchar2,aname in varchar2) return varchar2;
Returns the document identifier for a document–type item attribute.The document identifier is a concate- nated string of the following values:
DM::: is the node ID assigned to the document management system node as defined in the Document Management Nodes web page. is the document ID of the document, as assigned by the document management system where the document resides. is the version of the document. If a version is not specified, the latest version is assumed.

GetItemAttrInfo(itemtype in varchar2,aname in varchar2,atype out varchar2,subtype out varchar2,format out varchar2);
Returns information about an item type attribute, such as its type and format, if any is specified. Currently, subtype information is not available for item type attributes

GetActivityAttrInfo(itemtype in varchar2,itemkey in varchar2,actid in number,aname in varchar2,atype out varchar2,subtype out varchar2,format out varchar2);
Returns information about an activity attribute, such as its type and format, if any is specified. This procedure currently does not return any subtype information for activity attributes.

GetActivityAttrText(itemtype in varchar2,itemkey in varchar2,actid in number,aname in varchar2) return varchar2;
GetActivityAttrNumber (itemtype in varchar2,itemkey in varchar2,actid in number,aname in varchar2) return number;
GetActivityAttrDate (itemtype in varchar2,itemkey in varchar2,actid in number,aname in varchar2) return date;
Returns the value of an activity attribute in a process. Use the correct function for your attribute type. If the attribute is a Number or Date type, then the appropriate function translates the number/date value to a text string representation using the attribute format.
Note: Use GetActivityAttrText for Form, URLs, lookups and document attribute types.

BeginActivity (itemtype in varchar2,itemkey in varchar2,activity in varchar2);
Determines if the specified activity can currently be performed on the process item and raises an exception if it cannot. The CompleteActivity() procedure automatically performs this function as part of its validation. However, you can use BeginActivity to verify that the activity you intend to perform is currently allowed before actually calling it.

CompleteActivity(itemtype in varchar2,itemkey in varchar2,activity in varchar2,result_code in varchar2);
Notifies the workflow engine that the specified activity has been completed for a particular item. This procedure can be called for the following situations:

To indicate a completed activity with an optional result—This signals the Workflow Engine that an asynchronous activity has been completed. This procedure requires that the activity currently has a status of ‘Notified’. An optional activity completion result can also be passed. The result can determine what transition the process takes next.

To start and create a new item—Call CompleteActivity for a START activity to create a new item. START activities are designated as the beginning of a process in the Workflow Builder. The item type and key specified in this call must be passed to all subsequent calls that operate on this item.
Note: You can call CompleteActivity instead of StartActivity when you want to start a process with an activity node that is mid–stream in a process thread and not at the beginning of a process thread. The activity node you specify as the beginning of the process must be set to Start in the Node tab of its
property page or else an error will be raised.

AssignActivity(itemtype in varchar2,itemkey in varchar2,activity in varchar2,performer in varchar2);
Assigns or reassigns an activity to another performer. This procedure may be called before the activity is transitioned to. For example, a function activity earlier in the process may determine the performer of
a later activity. If a new user is assigned to a notification activity that already has an
outstanding notification, the outstanding notification is canceled and a new notification is generated for the new user by calling WF_Notification.Transfer.

HandleError (itemtype in varchar2,itemkey in varchar2,activity in varchar2,command in varchar2,result in varchar2);
This procedure is generally called from an activity in an ERROR process to handle any process activity that has encountered an error.

SetItemParent(itemtype in varchar2,itemkey in varchar2,parent_itemtype in varchar2,parent_itemkey in varchar2, parent_context in varchar2);
Defines the parent/child relationship for a master process and a detail process. This API must be called by any detail process spawned from a master process to define the parent/child relationship between the two
processes. You make a call to this API after you call the CreateProcess API, but before you call the StartProcess API for the detail process.

ItemStatus(itemtype in varchar2,itemkey in varchar2,status out varchar2,result out varchar2);
Returns the status and result for the root process of the specified item instance. Possible values returned for the status are: ACTIVE, COMPLETE, ERROR, or SUSPENDED. If the root process does not exist, then the item key does not exist and will thus cause the procedure to raise an exception.

Workflow core APIs:

PL/SQL procedures called by function activities can use a set of core Oracle Workflow APIs to raise and catch errors.

When a PL/SQL procedure called by a function activity either raises an unhandled exception, or returns a result beginning with ‘ERROR:’, the Workflow Engine sets the function activity’s status to ERROR and sets
the columns ERROR_NAME, ERROR_MESSAGE, and ERROR_STACK in the table WF_ITEM_ACTIVITY_STATUSES to reflect the error.

The columns ERROR_NAME and ERROR_MESSAGE get set to either the values returned by a call to WF_CORE.RAISE( ), or to the SQL error name and message if no call to RAISE( ) is found. The column
ERROR_STACK gets set to the contents set by a call to WF_CORE.CONTEXT( ), regardless of the error source.

CLEAR Clears the error buffers.

GET_ERROR(err_name out varchar2,err_message out varchar2 err_stack out varchar2);
Returns the name of a current error message and the token substituted error message. Also clears the error stack. Returns null if there is no current error.

declare
errname varchar2(30);
errmsg varchar2(2000);
errstack varchar2(32000);
begin

Wf_Engine.CompleteActivity(itemtype, itemkey, activity,
result_code);

exception
when others then
wf_core.get_error(err_name, err_msg, err_stack);
if (err_name is not null) then
wf_core.clear;
— Wf error occurred. Signal error as appropriate.
else
— not a wf error. Handle otherwise.
end if;
end;

TOKEN (token_name in varchar2,token_value in varchar2);
Defines an error token and substitutes it with a value. Calls to TOKEN( ) and RAISE( ) raise predefined errors for Oracle Workflow that are stored in the WF_RESOURCES table. The error messages contain tokens that need to be replaced with relevant values when the error message is raised. This is an alternative to raising PL/SQL standard exceptions or custom–defined exceptions.

RAISE (name in varchar2);
Raises an exception to the caller by supplying a correct error number and token substituted message for the name of the error message provided.

Calls to TOKEN( ) and RAISE( ) raise predefined errors for Oracle Workflow that are stored in the WF_RESOURCES table. The error messages contain tokens that need to be replaced with relevant values
when the error message is raised. This is an alternative to raising PL/SQL standard exceptions or custom defined exceptions.

CONTEXT (pkg_name IN VARCHAR2,proc_name IN VARCHAR2,arg1 IN VARCHAR2 DEFAULT ‘*none*’,arg2 IN VARCHAR2 DEFAULT ‘*none*’,arg3 IN VARCHAR2 DEFAULT ‘*none*’,arg4 IN VARCHAR2 DEFAULT ‘*none*’,arg5 IN VARCHAR2 DEFAULT ‘*none*’);

Adds an entry to the error stack to provide context information that helps locate the source of an error. Use this procedure with predefined errors raised by calls to TOKEN( ) and RAISE( ), with custom–defined
exceptions, or even without exceptions whenever an error condition is detected.

TRANSLATE (tkn_name IN VARCHAR2) return VARCHAR2;

Translates the string value of a token by returning the value for the token as defined in WF_RESOURCES for your language setting.

Workflow Directory Service APIs

GetRoleUsers(role in varchar2,users out UserTable);
Returns a table of users for a given role.

GetUserRoles(user in varchar2,roles out RoleTable);
Returns a table of roles that a given user is assigned to.

GetRoleInfo(Role in varchar2,Display_Name out varchar2,Email_Address out varchar2,Notification_Preference out varchar2,Language out varchar2,Territory out varchar2);

Returns the following information about a role:
Display name
Email address
Notification Preference (‘QUERY’, ‘MAILTEXT’, ‘MAILHTML’,’MAILATTH’, ‘SUMMARY’)
Language
Territory

GetRoleInfo2(Role in varchar2,Role_Info_Tbl out wf_directory.wf_local_roles_tbl_type);

Returns the following information about a role in a SQL table:
Display name
Description
Notification Preference (‘QUERY’, ‘MAILTEXT’, ‘MAILHTML’,’SUMMARY’)
Language
Territory
Email address
FAX
Status
Expiration Date

IsPerformer (user in varchar2,role in varchar2);
Returns true or false to identify whether a user is a performer of a role.

CurrentUser return varchar2;

Returns the current Application Object Library username. This function is useful only for the version of Oracle Workflow embedded in Oracle Applications.

UserActive (username in varchar2) return boolean;
Determines if a user is currently active and available to participate in a workflow. Returns TRUE if the user is active, otherwise it returns FALSE.

GetUserName (p_orig_system in varchar2,p_orig_system_id in varchar2, p_name out varchar2, p_display_name out varchar2);

Returns a Workflow display name and username for a user given the system information from the original user and roles repository.

GetRoleName (p_orig_system in varchar2,p_orig_system_id in varchar2,p_name out varchar2,p_display_name out varchar2);

Returns a Workflow display name and role name for a role given the system information from the original user and roles repository.

GetRoleDisplayName (p_role_name in varchar2) return varchar2;

pragma restrict_references(GetRoleDisplayName, WNDS, WNPS); Returns a Workflow role’s display name given the role’s internal name.

SetAdHocUserStatus (user_name in varchar2,status in varchar2 default ‘ACTIVE’);

Sets the status of an ad hoc user as ‘ACTIVE’ or ‘INACTIVE’.

SetAdHocRoleStatus (role_name in varchar2,status in varchar2 default ‘ACTIVE’);

Sets the status of an ad hoc role as ‘ACTIVE’ or ‘INACTIVE’.

CreateAdHocUser (name in out varchar2,display_name in out varchar2,language in varchar2 default null,territory in varchar2 default null,description in varchar2 default null,notification_preference in varchar2 default ‘MAILHTML’,email_address in varchar2 default null,fax in varchar2 default null,status in varchar2 default ‘ACTIVE’,expiration_date in date default sysdate);

Creates a user at runtime by creating a value in the WF_LOCAL_USERS table. This is referred to as an ad hoc user.

CreateAdHocRole (role_name in out varchar2,role_display_name in out varchar2,language in varchar2 default null,territory in varchar2 default null,role_description in varchar2 default null,notification_preference in varchar2 default’MAILHTML’,role_users in varchar2 default null,email_address in varchar2 default null,fax in varchar2 default null,status in varchar2 default ‘ACTIVE’,expiration_date in date default sysdate);

Creates a role at runtime by creating a value in the WF_LOCAL_ROLES table. This is referred to as an ad hoc role.

AddUsersToAdHocRole (role_name in varchar2,role_users in varchar2);

Adds users to a existing ad hoc role.

SetAdHocUserExpiration (user_name in varchar2,expiration_date in date default sysdate);

Updates the expiration date for an ad hoc user.

SetAdHocRoleExpiration (role_name in varchar2,expiration_date in date default sysdate);

Updates the expiration date for an ad hoc role.

SetAdHocUserAttr (user_name in varchar2,display_name in varchar2 default null,notification_preference in varchar2 default null, language in varchar2 default null, territory in varchar2 default null, email_address in varchar2 default null,fax in varchar2 default null);

Updates the attributes for an ad hoc user.

SetAdHocRoleAttr (role_name in varchar2,display_name in varchar2 default null,notification_preference in varchar2 default null,language in varchar2 default null,territory in varchar2 default null,email_address in varchar2 default null,fax in varchar2 default null);

Updates the attributes for an ad hoc role.

RemoveUsersFromAdHocRole (role_name in varchar2,role_users in varchar2 default null);

Removes users from an existing ad hoc role.

Workflow Preferences API

get_pref (p_user_name in varchar2,p_preference_name in varchar2) return varchar2;

Retrieves the value of the specified preference for the specified user.

Workflow Monitor APIs

function GetDiagramURL(x_agent in varchar2,x_item_type in varchar2,x_item_key in varchar2,x_admin_mode in varchar2 default ‘NO’) return varchar2;

Can be called by an application to return a URL that allows access to the Workflow Monitor with an attached access key password. The URL displays the diagram for a specific instance of a workflow process
in the Workflow Monitor operating in either ‘ADMIN’ or ‘USER’ mode.

function GetEnvelopeURL(x_agent in varchar2,x_item_type in varchar2, x_item_key in varchar2,x_admin_mode in varchar2 default ‘NO’) return varchar2;

Can be called by an application to return a URL that allows access to the Workflow Monitor Notifications List with an attached access key password. The URL displays the Notifications List for a specific instance of a workflow process in the Workflow Monitor.

function GetAdvancedEnvelopeURL (x_agent in varchar2,x_item_type in varchar2,x_item_key in varchar2, x_admin_mode in varchar2 default ‘NO’, x_options in varchar2 default null) return varchar2;

Can be called by an application to return a URL that displays the Workflow Monitor Activities List with an attached access key password. The URL displays the Activities List for a specific instance of a workflow process in the Workflow Monitor. The Activities List allows you to apply advanced filtering options in displaying the list of activities for a process instance.

Oracle Workflow Views

WF_ITEM_ACTIVITY_STATUSES_V
This view contains denormalized information about a workflow process and its activities’ statuses. Use this view to create custom queries and reports on the status of a particular item or process. The column descriptions of the view are as follows:

WF_NOTIFICATION_ATTR_RESP_V
This view contains information about the Respond message attributes for a notification group. If you plan to create a custom “voting” activity, use this view to create the function that tallies the responses from the users in the notification group. See: Voting Activity: page

WF_RUNNABLE_PROCESSES_V
This view contains a list of all runnable workflow processes in the ACTIVITIES table.

WF_ITEMS_V
This view is a select only version of the WF_ITEMS table.

Document Management APIs

get_launch_document_url(username in varchar2,document_identifier in varchar2,display_icon in oolean, launch_document_url out varchar2);

Returns an anchor URL that launches a new browser window containing the DM integration screen that displays the specified document. The screen is a frame set of two frames. The upper frame contains a customizable company logo and a toolbar of Oracle Workflow–integrated document management functions. The lower frame displays the specified document.

get_launch_attach_url (username in varchar2,callback_function in varchar2,display_icon in Boolean,launch_attach_url out varchar2);

Returns an anchor URL that launches a new browser window containing a DM integration screen that allows you to attach a document. The screen is a frame set of two frames. The upper frame contains a customizable company logo and a toolbar of Oracle Workflow–integrated document management functions. The lower frame displays the search screen of the default document management system.

get_open_dm_display_window
Returns a javascript function that displays an attached document from the current UI. The javascript function is used by all the document management functions that the user can perform on an attached
document. Each DM function also gives the current DM integration screen a name so that the Document Transport Window can call back to the javascript function in the current window.

get_open_dm_attach_window
Returns a javascript function to open a Document Transport Window when a user tries to attach a document in the current UI. The javascript function is used by all the document management functions
that the user can perform to attach a document. Each DM function also gives the current DM integration screen a name so that the Document Transport Window can call back to the javascript function in the
current window.

set_document_id_html (frame_name in varchar2,form_name in varchar2, document_id_field_name in varchar2 document_name_field_name in varchar2, callback_url out varchar2);

Returns a callback URL that gets executed when a user selects a document from the DM system. Use this procedure to set the document that is selected from the document management Search function to the specified destination field of an HTML page. The destination field is the field from which the user launches the DM integration screen to attach a document. Pass the returned callback URL as an argument to the get_launch_attach_url API.

Notification Model

A notification activity in a workflow process consist of a design–time message and a list of message attributes. In addition, there may be a number of runtime named values called item type attributes from
which the message attributes draw their values. The Workflow Engine moves through the workflow process, evaluating each activity in turn. Once it encounters a notification activity, the engine makes a call to the Notification System Send( ) or SendGroup( ) API to send the notification.

Notification APIs

The following APIs can be called by a notification agent to manage notifications for a notification activity. The APIs are stored in the PL./SQL package called WF_NOTIFICATION.

function SEND (role in varchar2,msg_type in varchar2,msg_name in varchar2,
due_date in date default null,callback in varchar2 default null,context in varchar2 default null,send_comment in varchar2 default null priority in number default null) return number;

This function sends the specified message to a role, returning a notification ID if successful. The notification ID must be used in all future references to the notification.

function SendGroup (role in varchar2,msg_type in varchar2,msg_name in varchar2,due_date in date default null,callback in varchar2 default null,context in varchar2 default null,send_comment in varchar2 default null priority in number default null) return number;

This function sends a separate notification to all the users assigned to a specific role and returns a number called a notification group ID, if successful. The notification group ID identifies that group of users and the notification they each received.

procedure FORWARD (nid in number,new_role in varchar2,forward_comment in varchar2 default null);

This procedure delegates a notification to a new role to perform work, even though the original role recipient still maintains ownership of the notification activity. Also implicitly calls the Callback function specified in the Send or SendGroup function with FORWARD mode.

procedure TRANSFER(nid in number,new_role in varchar2,forward_comment in varchar2 default null);

This procedure forwards a notification to a new role and transfers ownership of the notification to the new role. It also implicitly calls the Callback function specified in the Send or SendGroup function with
TRANSFER mode.

procedure CANCEL(nid in number,cancel_comment in varchar2 default null);

This procedure may be invoked by the sender or administrator to cancel a notification. The notification status is then changed to ‘CANCELED’ but the row is not removed from the WF_NOTIFICATIONS table until a purge operation is performed. If the notification was delivered via e–mail and expects a response, a
‘Canceled’ e–mail is sent to the original recipient as a warning that the notification is no longer valid.

procedure CancelGroup (gid in number,cancel_comment in varchar2 default null);

This procedure may be invoked by the sender or administrator to cancel the individual copies of a specific notification sent to all users in a notification group. The notifications are identified by the notification
group ID (gid). The notification status is then changed to ‘CANCELED’ but the rows are not removed from the WF_NOTIFICATIONS table until a purge operation is performed. If the notification was delivered via e–mail and expects a response, a ‘Canceled’ e–mail is sent to the original recipient as a warning that the
notification is no longer valid. Generally, this function is called only if a notification activity has
‘Expanded Roles’ checked in its properties page. If Expanded Roles is not checked, then the Cancel( ) function is called instead.

procedure RESPOND (nid in number,respond_comment in varchar2 default null,responder in varchar2 default null);

This procedure may be invoked by the notification agent (Notification Viewer, Notification Web page, or E–mail agent) when the performer completes the response to the notification. The procedure marks the
notification as ‘CLOSED’ and communicates RESPOND attributes back to the database via the callback function (if supplied). This procedure also accepts the name of the individual that actually responded to the notification. This may be useful to know especially if the notification is assigned to a multi–user role. The information is stored in the RESPONDER column of the WF_NOTIFICATIONS table. The value stored in this column depends on how the user responds to the notification.

function RESPONDER (nid in number) returns varchar2;
This function returns the responder of a closed notification.

procedure VoteCount(gid in number,ResultCode in varchar2,ResultCount out number,PercentOfTotalPop out number,PercentOfVotes out number);

Counts the number of responses for a specified result code. Use this procedure only if you are writing your own custom Voting activity

function OpenNotificationsExist(gid in number) return boolean;

This function returns ‘TRUE’ if any notification associated with the specified notification group ID is ‘OPEN’, otherwise it returns ‘FALSE’. Use this procedure only if you are writing your own custom Voting
activity.

procedure Close (nid in number,responder in varchar2 default null);
This procedure Closes a notification.

procedure AddAttr (nid in number,aname in varchar2);
Adds a new runtime notification attribute.

procedure SetAttrText (nid in number,aname in varchar2,avalue in varchar2);
procedure SetAttrNumber(nid in number,aname in varchar2,avalue in number);
procedure SetAttrDate (nid in number,aname in varchar2,avalue in date);

Used at both send and respond time to set the value of notification attributes. The notification agent (sender) may set the value of SEND attributes. The performer (responder) may set the value of RESPOND
attributes.

procedure GetAttrInfo (nid in number,aname in varchar2,atype out varchar2,subtype out varchar2,format out varchar2);

Returns information about a notification attribute, such as its type, subtype, and format, if any is specified. The subtype is always SEND or RESPOND to indicate the attribute’s source.

procedure GetInfo (nid in number,role out varchar2,message_type out varchar2,message_name out varchar2,priority out number,due_date out date,status out varchar2);

Returns the role that the notification is sent to, the item type of the message, the name of the message, the notification priority, the due date and the status for the specified notification.

function GetText(some_text in varchar2,nid in number,disptype in varchar2 default ”) return varchar2;

function GetAttrText(nid in number,aname in varchar2) return varchar2;
function GetAttrNumber(nid in number,aname in varchar2) return number;
function GetAttrDate(nid in number,aname in varchar2) return date;

Returns the value of the specified message attribute.

function GetSubject(nid in number) return varchar2
Returns the subject line for the notification message. Any message attribute in the subject is token substituted with the value of the corresponding message attribute.

function GetBody(nid in number,disptype in varchar2 default ”)
return varchar2;

Returns the HTML or plain text message body for the notification, depending on the message body type specified. Any message attribute in the body is token substituted with the value of the corresponding
notification attribute. This function may return up to 32K characters. You cannot use this function in a view definition or in an Oracle Applications form. For views and forms, use GetShortBody( ) which truncates values at 1950 characters.

function TestContext (nid in number) return boolean;

Tests if the current context is correct by calling the Item Type Selector/Callback function. This function returns TRUE if the context check is OK, or if no Selector/Callback function is implemented. It returns FALSE if the context check fails.

function AccessCheck (access_str in varchar2) return varchar2;

Returns a username if the notification access string is valid and the notification is open, otherwise it returns null. The access string is automatically generated by the Notification Mailer and is used to verify the authenticity of both text and HTML versions of E–mail notifications.

function WorkCount (username in varchar2) return number;
Returns the number of open notifications assigned to a role.