* ABM - Activity Based Management
* AD - Applications DBA
* AHL - Oracle Advanced Service Online
* AK - Oracle Common Modules-AK
* ALR - Oracle Alert
* AMF - Oracle Fulfillment Services
* AMS - Oracle Marketing
* AP - Oracle Payables
* AR - Oracle Receivables
* AS - Oracle Sales
* ASG - Oracle CRM Gateway for Mobile Services
* ASL - Oracle Mobile Field Sales Laptop
* ASO - Oracle Order Capture
* AST - TeleSales
* AX - Global Accounting Engine
* AZ - Application Implementation
* BEN - Oracle Advanced Benefits
* BIC - Customer Intelligence
* BIL - Sales Intelligence
* BIM - Marketing Intelligence
* BIS - Oracle Applications BIS
* BIX - Call Center Intelligence
* BNE - Oracle Web ADI
* BOM - Oracle Bills of Material
* BSC - Balanced Scorecard
* CCT - Oracle Call Center and Telephony
* CE - Oracle Cash Management
* CHV - Oracle Supplier Scheduling
* CLN - Supply Chain Trading Connector for RosettaNet
* CN - Oracle Sales Compensation
* CS - Oracle Service
* CSC - Customer Care
* CSD - Depot Repair
* CSE - Oracle Enterprise Install Base
* CSF - Field Service
* CSI - Install Base
* CSM - Oracle Field Service Palm
* CSP - Oracle Spares Management
* CSR - Oracle Scheduler
* CSS - Support
* CUG - Oracle Citizen Interaction Center
* CUN - Oracle Network Logistics NATS
* CZ - Oracle Configurator
* EAM - Oracle Enterprise Asset Management
* EC - Oracle e-Commerce Gateway
* ECX - Oracle XML Gateway
* EDR - Oracle E Records
* ENG - Oracle Engineering
* FII - Financials Intelligence
* FLM - Oracle Flow Manufacturing
* FND - Application Object Library
* FTE - Oracle Transportation Hub
* FV - Oracle Federal Financials
* GHR - Oracle Federal HR
* GL - Oracle General Ledger
* GMA - Oracle Process Manufacturing Systems
* GMD - Oracle Process Manufacturing Product Development
* GME - Oracle Process Manufacturing Process Execution
* GMF - Oracle Process Manufacturing Financials
* GMI - Oracle Process Manufacturing Inventory
* GML - Oracle Process Manufacturing Logistics
* GMP - Oracle Process Manufacturing Process Planning
* GMS - Oracle Grants Accounting
* GR - Oracle Process Regulatory Mgmt
* HRI - Human Resources Intelligence
* HXC - Oracle Time and Labor
* HXT - Oracle Time and Labor Rules
* IBA - iMarketing
* IBC - Oracle iContent
* IBE - iStore
* IBU - iSupport
* IBY - iPayment
* ICX - Oracle Self-Service Web Applications
* IEB - Oracle Interaction Blending
* IEC - Oracle Campaign Plus
* IEM - Oracle eMail Center
* IEO - Call Center Technology
* IES - Scripting
* IEU - Oracle Universal Work Queue
* IEX - Oracle Collections
* IGC - Commitment Administration
* IGF - Student Systems Fin Aid
* IGI - Oracle International Public Sector Financials
* IGS - Oracle Student Sytems
* IGW - Oracle Grants Proposal
* INV - Oracle Inventory
* IPD - Oracle Product Development Exchange
* ISC - Supply Chain Intelligence
* JTF - CRM Foundation
* JTM - Oracle CRM Mobile Foundation
* JTS - Oracle CRM Self Service Administration
* MRP - Oracle Master Scheduling/MRP
* MSC - Oracle Advanced Supply Chain Planning
* MSD - Oracle Demand Planning
* OFA - Oracle Assets
* OKC - Oracle Contracts Core
* OKI - Oracle Contracts Intelligence
* OKL - Oracle Lease Management
* OKR - Oracle Contracts for Rights
* OKS - Oracle Contracts Service Module
* ONT - Oracle Order Management
* OPI - Operations Intelligence
* OTA - Oracle Training Administration
* OZF - Funds & Budgets
* PA - Oracle Projects
* PAY - Oracle Payroll
* PER - Oracle Human Resources
* PJM - Oracle Project Manufacturing
* PN - Oracle Property Manager
* PO - Oracle Purchasing
* POA - Purchasing Intelligence
* POM - Oracle Exchange
* PON - Oracle Sourcing
* POS - Internet Supplier Portal
* PQH - Oracle Public Sector HR
* PSA - Public Sector Applications
* PSB - Oracle Public Sector Budgeting
* PSP - Oracle Labor Distribution
* PV - Partner Relationship Management
* QA - Oracle Quality
* QP - Oracle Pricing
* QRM - Oracle Risk Management
* RG - Application Report Generator
* RLM - Oracle Release Management
* VEA - Oracle Automotive
* WIP - Oracle Work in Process
* WMS - Oracle Warehouse Management System
* WSH - Oracle Shipping
* WSM - Shop Floor Management
* XDP - Oracle Provisioning
* XNC - Oracle Sales for Communications
* XNI - Oracle Install Base Intelligence
* XNP - Oracle Number Portability
* XNS - Oracle Service for Communications
* XTR - Oracle Treasury
Feb 17, 2010
Feb 5, 2010
Oracle Workflow APIs in Detail...
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.
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:
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.
WorkFlow Commands and Instructions:
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 Download 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.
------------
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 Download 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.
Allow Modification to Customized Objects in WorkFlow.
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
“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
Predefined Oracle workflow activities:
Predefined Oracle workflow activities:
And/Or Activities: In cases where multiple parallel branches transition to a single node, you can decide whether that node should transition forward when any of those parallel branches complete or when all of the parallel branches complete. Use the And activity as the node for several converging branches to ensure that all branches complete before continuing. Use the Or activity as the node for several converging branches to allow the process to continue whenever any one of the branches completes.
And: Completes when the activities from all converging branches complete. Calls a PL/SQL procedure named WF_STANDARD.ANDJOIN.
Or:Completes when the activities from at least one converging branch complete. Calls a PL/SQL procedure named WF_STANDARD.ORJOIN.
Comparison Activities
The comparison activities provide a standard way to compare two numbers, dates, or text strings.
Compare Date: Use to compare the value of an item type attribute of type Date with a constant date.
Compare Number: Use to compare the value of an item type attribute of type Number with a constant number.
Compare Text: Use to compare the value of two item type attributes of type Text.
All the Comparison activities call a PL/SQL procedure named WF_STANDARD.COMPARE.
Compare Execution Time Activity
The Compare Execution Time activity provides a standard way to compare the elapsed execution time of a process with a constant test time. The Compare Execution Time activity calls a PL/SQL procedure named WF_STANDARD.COMPAREEXECUTIONTIME.
Wait Activity
The Wait activity pauses the process for the time you specify. You can either wait until:
1. a specific date
2. a given day of the month
3. a given day of the week
4. a period of time after this activity is encountered
This activity calls the PL/SQL procedure named WF_STANDARD.WAIT.
Block Activity
The Block activity lets you pause a process until some external program or manual step completes and makes a call to the CompleteActivity Workflow Engine API. Use the Block activity to delay a process until
some condition is met, such as the completion of a concurrent program. Make sure your program issues a CompleteActivity call when it completes to resume the process at the Block activity. This activity calls the PL/SQL procedure named WF_STANDARD.BLOCK.
Defer Thread Activity
The Defer Thread activity defers the subsequent process thread to the background queue without requiring you to change the cost of each activity in that thread to a value above the Workflow Engine threshold. This activity always interrupts the process thread by causing a disconnect to occur in the current database session, even if the thread is already deferred. This activity calls the PL/SQL procedure named WF_STANDARD.DEFER.
Launch Process Activity
The Launch Process activity lets you launch another workflow process from the current process. This activity calls the PL/SQL procedure named WF_STANDARD.LAUNCHPROCESS.
Noop Activity
The Noop activity acts as a place holder activity that performs no action. You can use this activity anywhere you want to place a node without performing an action. You can change the display name of this
activity to something meaningful when you include it in a process, so that it reminds you of what you want this activity to do in the future. This activity calls the PL/SQL procedure named WF_STANDARD.NOOP.
Loop Counter Activity
Use the Loop Counter activity to limit the number of times the Workflow Engine transitions through a particular path in a process. The Loop Counter activity can have a result of Loop or Exit. This Loop Counter activity calls the PL/SQL procedure named WF_STANDARD.LOOPCOUNTER.
Start Activity
The Start activity marks the start of a process and does not perform any action. Although it is not necessary you may include it in your process diagram to visually mark the start of a process as a separate node. This
activity calls the PL/SQL procedure named WF_STANDARD.NOOP.
End Activity
The End activity marks the end of a process and does not perform any action. You can use it to return a result for a completed process by specifying a Result Type for the activity. Although it is not necessary,
you may include it in your process diagram to visually mark the end of your process as a separate node. This activity calls the PL/SQL procedure named WF_STANDARD.NOOP.
Role Resolution Activity
The Role Resolution activity lets you identify a single user from a role comprised of multiple users. In a process diagram, place the Role Resolution activity in front of a notification activity and specify the
performer of that notification activity to be a role consisting of several users. The Role Resolution activity selects a single user from that role and assigns the notification activity to that user. This activity calls the PL/SQL procedure named WF_STANDARD.ROLERESOLUTION.
Notify Activity
The Notify function activity lets you send a notification, where the message being sent is determined dynamically at runtime by a prior function activity. To use the Notify activity, you must model a prerequisite function activity into the process that selects one of several predefined messages for the Notify activity to send.
Vote Yes/No Activity
The Vote Yes/No activity lets you send a notification to a group of users in a role and tally the Yes/No responses from those users. The results of the tally determine the activity that the process transitions to
next. The Vote Yes/No activity, classified as a notification activity, first sends a notification message to a group of users and then performs a PL/SQL post–notification function to tally the users' responses (votes).
Master/Detail Coordination Activities
The Master/Detail coordination activities let you coordinate the flow of master and detail processes. For example, a master process may spawn detail processes that need to be coordinated such that the master
process continues only when each detail process reaches a certain point in its flow or vice versa.
Wait for Flow Activity
Place this activity in a master or detail process to pause the flow until the other corresponding detail or master process completes a specified activity. This activity calls a PL/SQL procedure named
WF_STANDARD.WAITFORFLOW.
Continue Flow Activity Use this activity to mark the position in the corresponding detail or master process where, upon completion, you want the halted process to continue. This activity calls a PL/SQL procedure named WF_STANDARD.CONTINUEFLOW.
Assign Activity
The Assign activity lets you assign a value to an item attribute. This activity calls the PL/SQL procedure named WF_STANDARD.ASSIGN.
Get Monitor URL Activity
The Get Monitor URL activity generates the URL for the Workflow Monitor diagram window and stores it in an item attribute that you specify. This activity calls the PL/SQL procedure named F_STANDARD.GETURL.
AND/OR Activities :
In cases where multiple parallel branches transition to a single node, you can decide whether that node should transition forward when
Any of those parallel branches complete (OR) => Uses the WF_STANDARD.AndJoin procedure
When all of the parallel branches complete. (AND) => Uses the WF_STANDARD.OrJoin procedure
COMPARISON Activity :
The comparison activities provide a standard way to compare two numbers, dates, or text strings.
Compare Date compares a date-type item with a constant date
Compare Number compares a number-type item with a constant number
Compare Text compares two text-type item types.
All of these use the procedure :
WF_STANDARD.Compare.
WAIT Activity :
The Wait activity pauses the process for the time you specify. One can wait until -
A specific date
A given day of the month
A given day of the week
A period of time after this activity is encountered
This activity calls the PL/SQL procedure named WF_STANDARD.WAIT.
And/Or Activities: In cases where multiple parallel branches transition to a single node, you can decide whether that node should transition forward when any of those parallel branches complete or when all of the parallel branches complete. Use the And activity as the node for several converging branches to ensure that all branches complete before continuing. Use the Or activity as the node for several converging branches to allow the process to continue whenever any one of the branches completes.
And: Completes when the activities from all converging branches complete. Calls a PL/SQL procedure named WF_STANDARD.ANDJOIN.
Or:Completes when the activities from at least one converging branch complete. Calls a PL/SQL procedure named WF_STANDARD.ORJOIN.
Comparison Activities
The comparison activities provide a standard way to compare two numbers, dates, or text strings.
Compare Date: Use to compare the value of an item type attribute of type Date with a constant date.
Compare Number: Use to compare the value of an item type attribute of type Number with a constant number.
Compare Text: Use to compare the value of two item type attributes of type Text.
All the Comparison activities call a PL/SQL procedure named WF_STANDARD.COMPARE.
Compare Execution Time Activity
The Compare Execution Time activity provides a standard way to compare the elapsed execution time of a process with a constant test time. The Compare Execution Time activity calls a PL/SQL procedure named WF_STANDARD.COMPAREEXECUTIONTIME.
Wait Activity
The Wait activity pauses the process for the time you specify. You can either wait until:
1. a specific date
2. a given day of the month
3. a given day of the week
4. a period of time after this activity is encountered
This activity calls the PL/SQL procedure named WF_STANDARD.WAIT.
Block Activity
The Block activity lets you pause a process until some external program or manual step completes and makes a call to the CompleteActivity Workflow Engine API. Use the Block activity to delay a process until
some condition is met, such as the completion of a concurrent program. Make sure your program issues a CompleteActivity call when it completes to resume the process at the Block activity. This activity calls the PL/SQL procedure named WF_STANDARD.BLOCK.
Defer Thread Activity
The Defer Thread activity defers the subsequent process thread to the background queue without requiring you to change the cost of each activity in that thread to a value above the Workflow Engine threshold. This activity always interrupts the process thread by causing a disconnect to occur in the current database session, even if the thread is already deferred. This activity calls the PL/SQL procedure named WF_STANDARD.DEFER.
Launch Process Activity
The Launch Process activity lets you launch another workflow process from the current process. This activity calls the PL/SQL procedure named WF_STANDARD.LAUNCHPROCESS.
Noop Activity
The Noop activity acts as a place holder activity that performs no action. You can use this activity anywhere you want to place a node without performing an action. You can change the display name of this
activity to something meaningful when you include it in a process, so that it reminds you of what you want this activity to do in the future. This activity calls the PL/SQL procedure named WF_STANDARD.NOOP.
Loop Counter Activity
Use the Loop Counter activity to limit the number of times the Workflow Engine transitions through a particular path in a process. The Loop Counter activity can have a result of Loop or Exit. This Loop Counter activity calls the PL/SQL procedure named WF_STANDARD.LOOPCOUNTER.
Start Activity
The Start activity marks the start of a process and does not perform any action. Although it is not necessary you may include it in your process diagram to visually mark the start of a process as a separate node. This
activity calls the PL/SQL procedure named WF_STANDARD.NOOP.
End Activity
The End activity marks the end of a process and does not perform any action. You can use it to return a result for a completed process by specifying a Result Type for the activity. Although it is not necessary,
you may include it in your process diagram to visually mark the end of your process as a separate node. This activity calls the PL/SQL procedure named WF_STANDARD.NOOP.
Role Resolution Activity
The Role Resolution activity lets you identify a single user from a role comprised of multiple users. In a process diagram, place the Role Resolution activity in front of a notification activity and specify the
performer of that notification activity to be a role consisting of several users. The Role Resolution activity selects a single user from that role and assigns the notification activity to that user. This activity calls the PL/SQL procedure named WF_STANDARD.ROLERESOLUTION.
Notify Activity
The Notify function activity lets you send a notification, where the message being sent is determined dynamically at runtime by a prior function activity. To use the Notify activity, you must model a prerequisite function activity into the process that selects one of several predefined messages for the Notify activity to send.
Vote Yes/No Activity
The Vote Yes/No activity lets you send a notification to a group of users in a role and tally the Yes/No responses from those users. The results of the tally determine the activity that the process transitions to
next. The Vote Yes/No activity, classified as a notification activity, first sends a notification message to a group of users and then performs a PL/SQL post–notification function to tally the users' responses (votes).
Master/Detail Coordination Activities
The Master/Detail coordination activities let you coordinate the flow of master and detail processes. For example, a master process may spawn detail processes that need to be coordinated such that the master
process continues only when each detail process reaches a certain point in its flow or vice versa.
Wait for Flow Activity
Place this activity in a master or detail process to pause the flow until the other corresponding detail or master process completes a specified activity. This activity calls a PL/SQL procedure named
WF_STANDARD.WAITFORFLOW.
Continue Flow Activity Use this activity to mark the position in the corresponding detail or master process where, upon completion, you want the halted process to continue. This activity calls a PL/SQL procedure named WF_STANDARD.CONTINUEFLOW.
Assign Activity
The Assign activity lets you assign a value to an item attribute. This activity calls the PL/SQL procedure named WF_STANDARD.ASSIGN.
Get Monitor URL Activity
The Get Monitor URL activity generates the URL for the Workflow Monitor diagram window and stores it in an item attribute that you specify. This activity calls the PL/SQL procedure named F_STANDARD.GETURL.
AND/OR Activities :
In cases where multiple parallel branches transition to a single node, you can decide whether that node should transition forward when
Any of those parallel branches complete (OR) => Uses the WF_STANDARD.AndJoin procedure
When all of the parallel branches complete. (AND) => Uses the WF_STANDARD.OrJoin procedure
COMPARISON Activity :
The comparison activities provide a standard way to compare two numbers, dates, or text strings.
Compare Date compares a date-type item with a constant date
Compare Number compares a number-type item with a constant number
Compare Text compares two text-type item types.
All of these use the procedure :
WF_STANDARD.Compare.
WAIT Activity :
The Wait activity pauses the process for the time you specify. One can wait until -
A specific date
A given day of the month
A given day of the week
A period of time after this activity is encountered
This activity calls the PL/SQL procedure named WF_STANDARD.WAIT.
Parameters for a PL/SQL in Workflow API
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;
========================================================================
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:
WAITING : Pending for some other activity
DEFERRED:
NOTIFIED:
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 (’
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;
========================================================================
Various PL/SQL APIs exist for the working of Oracle Workflow:
Various PL/SQL APIs exist for the working of Oracle Workflow:
· WF_ENGINE
· WF_CORE
· WF_PURGE
· WF_MONITOR
· WF_QUEUE
· WF_NOTIFICATIONS
WF_ENGINE : The state of a workflow item is defined by the various states of all activities that are part of the process for that item. The engine changes activity states in response to an API call to update the activity family of calls.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.
· These APIs are defined in a PL/SQL package called WF_ENGINE :
* SetItemAttrText, SetItemAttrNumber, SetItemAttrDate
Used for setting a text/number/Date attribute value of a workflow instance.
* GetItemAttrText => Used for getting a text attributr value of a workflow instance.
* CreateProcess => Create a new runtime process for an application item.
* SetitemUserKey
* GetItemUserKey
* Background => Runs a background engine for processing deferred and/or timed out activities using the parameters specified.
* SetItemOwner
* StartProcess
* LaunchProcess.
WF_CORE :
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.
WF_CORE Procedures :
· CLEAR
· GET_ERROR
· TOKEN
· RAISE
· CONTEXT
· TRANSLATE
WF_PURGE : WF_PURGE can be used to purge obsolete runtime data for completed items and processes, and to purge information for obsolete activity versions that are no longer in use. One may want to periodically purge this obsolete data from your system to increase performance.
WF_PURGE Procedures :
· Items
· Activities
· Notifications
· Total
· TotalPERM
· The WF_USER_ROLES view is an intersection of the users and roles in WF_USERS and WF_ROLES:
User_Name
User_Orig_System
User_Orig_System_id
Role_Name
Role_Orig_System
Role_Orig_System_id
· WF_ITEM_TYPES : It stores all the Item_type definitions
NAME
PROTECT_LEVEL
CUSTOM_LEVEL
PERSISTENCE_TYPE
PERSISTENCE_DAYS
WF_SELECTOR
· WF_ITEM_ATTRIBUTES : The WF_ITEM_ATTRIBUTES table stores definitions of attributes associated with a process. Each row includes the sequence in which the attribute is used as well as the format of the attribute data.
ITEM_TYPE
NAME
SEQUENCE
TYPE
PROTECT_LEVEL
CUSTOM_LEVEL
SUBTYPE
FORMAT
· WF_ITEMS : WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system.
ITEM_TYPE
ITEM_KEY
ROOT_ACTIVITY
OWNER_ROLE
PARENT_ITEM_TYPE
PARENT_ITEM_KEY
BEGIN_DATE
END_DATE
USER_KEY
· WF_PROCESS_ACTIVITIES : WF_PROCESS_ACTIVITIES stores the data for an activity within a specific process. A process activity is a Modeled workflow process, which can be included as an activity in other processes to represent a sub–process
PROCESS_ITEM_TYPE
PROCESS_NAME
PROCESS_VERSION
ACTIVITY_ITEM_TYPE
ACTIVITY_NAME
· WF_ACTIVITIES : WF_ACTIVITIES table stores the definition of an activity.
ITEM_TYPE , NAME , VERSION , TYPE , RERUN , EXPAND_ROLE,
PROTECT_LEVEL , CUSTOM_LEVEL , BEGIN_DATE , END_DATE
· WF_ITEM_TYPES : It stores all the Item_type definitions
NAME
PROTECT_LEVEL
CUSTOM_LEVEL
PERSISTENCE_TYPE
PERSISTENCE_DAYS
WF_SELECTOR
WF_LOOKUPS -Workflow Lookups.
WF_LOOKUP_TYPES -Workflow lookup types.
WF_MESSAGES - Messages which r sent as notifications
WF_MESSAGE_ATRIBUTES : Message attributes(additional info to be sent to or received from an individual via notifications)
WF_NOTIFICATIONS , WF_NOTIFICATIONS_ATTRIBUTES.
Views Used in Workflow : Workflow uses views for effective querying of data. These views are de-normalized so as to provide a faster access to data over networks.Some of the important views are:
WF_ITEMS_V
WF_ITEM_ACTIVITY_STATUSES_V : WorkFlow Process activity results & error information.
WF_NOTIFICATIONS_VIEWS
WF_ROLES : Contains information on the roles of which users can be members like Role Name,Description,Language and territory.
WF_USERS : Contains Information on the user names, display names for the users, their notification preference and their e-mail addresses.
WF_USER_ROLES : Contains information on the association of the users to the roles.
· WF_ENGINE
· WF_CORE
· WF_PURGE
· WF_MONITOR
· WF_QUEUE
· WF_NOTIFICATIONS
WF_ENGINE : The state of a workflow item is defined by the various states of all activities that are part of the process for that item. The engine changes activity states in response to an API call to update the activity family of calls.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.
· These APIs are defined in a PL/SQL package called WF_ENGINE :
* SetItemAttrText, SetItemAttrNumber, SetItemAttrDate
Used for setting a text/number/Date attribute value of a workflow instance.
* GetItemAttrText => Used for getting a text attributr value of a workflow instance.
* CreateProcess => Create a new runtime process for an application item.
* SetitemUserKey
* GetItemUserKey
* Background => Runs a background engine for processing deferred and/or timed out activities using the parameters specified.
* SetItemOwner
* StartProcess
* LaunchProcess.
WF_CORE :
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.
WF_CORE Procedures :
· CLEAR
· GET_ERROR
· TOKEN
· RAISE
· CONTEXT
· TRANSLATE
WF_PURGE : WF_PURGE can be used to purge obsolete runtime data for completed items and processes, and to purge information for obsolete activity versions that are no longer in use. One may want to periodically purge this obsolete data from your system to increase performance.
WF_PURGE Procedures :
· Items
· Activities
· Notifications
· Total
· TotalPERM
· The WF_USER_ROLES view is an intersection of the users and roles in WF_USERS and WF_ROLES:
User_Name
User_Orig_System
User_Orig_System_id
Role_Name
Role_Orig_System
Role_Orig_System_id
· WF_ITEM_TYPES : It stores all the Item_type definitions
NAME
PROTECT_LEVEL
CUSTOM_LEVEL
PERSISTENCE_TYPE
PERSISTENCE_DAYS
WF_SELECTOR
· WF_ITEM_ATTRIBUTES : The WF_ITEM_ATTRIBUTES table stores definitions of attributes associated with a process. Each row includes the sequence in which the attribute is used as well as the format of the attribute data.
ITEM_TYPE
NAME
SEQUENCE
TYPE
PROTECT_LEVEL
CUSTOM_LEVEL
SUBTYPE
FORMAT
· WF_ITEMS : WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system.
ITEM_TYPE
ITEM_KEY
ROOT_ACTIVITY
OWNER_ROLE
PARENT_ITEM_TYPE
PARENT_ITEM_KEY
BEGIN_DATE
END_DATE
USER_KEY
· WF_PROCESS_ACTIVITIES : WF_PROCESS_ACTIVITIES stores the data for an activity within a specific process. A process activity is a Modeled workflow process, which can be included as an activity in other processes to represent a sub–process
PROCESS_ITEM_TYPE
PROCESS_NAME
PROCESS_VERSION
ACTIVITY_ITEM_TYPE
ACTIVITY_NAME
· WF_ACTIVITIES : WF_ACTIVITIES table stores the definition of an activity.
ITEM_TYPE , NAME , VERSION , TYPE , RERUN , EXPAND_ROLE,
PROTECT_LEVEL , CUSTOM_LEVEL , BEGIN_DATE , END_DATE
· WF_ITEM_TYPES : It stores all the Item_type definitions
NAME
PROTECT_LEVEL
CUSTOM_LEVEL
PERSISTENCE_TYPE
PERSISTENCE_DAYS
WF_SELECTOR
WF_LOOKUPS -Workflow Lookups.
WF_LOOKUP_TYPES -Workflow lookup types.
WF_MESSAGES - Messages which r sent as notifications
WF_MESSAGE_ATRIBUTES : Message attributes(additional info to be sent to or received from an individual via notifications)
WF_NOTIFICATIONS , WF_NOTIFICATIONS_ATTRIBUTES.
Views Used in Workflow : Workflow uses views for effective querying of data. These views are de-normalized so as to provide a faster access to data over networks.Some of the important views are:
WF_ITEMS_V
WF_ITEM_ACTIVITY_STATUSES_V : WorkFlow Process activity results & error information.
WF_NOTIFICATIONS_VIEWS
WF_ROLES : Contains information on the roles of which users can be members like Role Name,Description,Language and territory.
WF_USERS : Contains Information on the user names, display names for the users, their notification preference and their e-mail addresses.
WF_USER_ROLES : Contains information on the association of the users to the roles.
WHAT DOES ORACLE WORKFLOW COMPRISE OF?
WHAT DOES ORACLE WORKFLOW COMPRISE OF?
Processes (Item Types)
Item Type: A grouping of workflow components. All components of a workflow process must be associated with a specific item type. A set of rules which determine how the business methodology is to be routed.
Item Type : A grouping of all items of a category that share the same set of attributes For e.g.: PO Requisition is an Item Type used to group all PO requisitions created. It is like say Class in Java
Item : A specific process or transaction managed by WF process. Like instance of a class is Object.A specific run of Leave request Process will be reffered to as Item.
Attributes : These are the various input or output variables that are needed by the business process.Text,Attribute ,Date ,URL,Number,Lookup,Form are some of the attribute types.Lookup
Type: List of values that can be referenced by any component.
Activities : An activity is a unit of work that contributes toward the accomplishment of a process. An activity can be a notification, a function, an event, or a process. These are the actions performed during the business process
Function Activity: Automated unit of work defined as a PL/SQL stored procedure.
Notification Activity: Activity that sends a message to a performer.
Node : An instance of an activity in a process diagram.
Notification : An instance of a message delivered to a user.
Message: The message may request the performer, the role receiving the message, to do some work or may simply provide information. Each notification carries a message with it which is the actual text of the notification.
Performer : A user/role assigned to perform a particular (human) activity. Generally, assigned to a notification.
Role : One or more users grouped by a common responsibility or position.
Lookup type :A predefined list of values used to interact with users or define the results of an
activity.
Lookup Code : An internal name of a value defined in lookup type.
Result Type :The name of the lookup type that contains an activity’s possible result values.
Timeout : The amount of time during which a notification activity must be performed before the WF engine transitions to an alternate activity (if defined) or error process.
Transition:The relationship between completion of one activity and initialization of another activity. (Arrows)
Access Level : A numeric value ranging from 0 – 1000 defining at which level the user operates and whether user can modify the workflow definition.
Protection Level :A numeric value ranging from 0 – 1000 that represents who the workflow definition is protected from for modification
Access levels and Protection levels work in collaboration to decide who can modify what in an Oracle Workflow.
Process Activity : A Process activity represents a collection of activities in relationship. When a Process Activity is contained in another process it is called a sub-process.In other words, activities in a process can also be process themselves.
PERSISTENCE TYPE : Permanent , Temporary and Synchronous
When you define an item type, you must also specify its persistence type.The persistence type controls how long a status audit trail is maintained for each instance of the item type.
· If you set Persistence to Permanent, the runtime status information indifinitely until you specifically purge the information by calling the procedure WF_PURGE.TotalPerm().
· If you set an item type's Persistence to Temporary, you must also specify the number of days of persistence.The status audit trail for each instance of a temporary item type is maintained for at least 'n' days of persistence after its completion date.After the 'n' days of persistence, you can then use any of the WF_PURGE API's to purge the item type's runtime status information.
EVENTS : A business event is an occurrence in an internet or intranet application or program thay might be to other objects in a system or to external agents. For instance, the creation of a purchase order is an example of a business event in a purchasing application.You can define your significant events in the Event manager.
· The Workflow Engine traps errors produced by function activities by setting a savepoint before each function .If an activity produces an unhandled exception, the engine performs a rollback to the savepoint, and sets the activity to the ERROR status.The Workflow Engine never issues a commit as it is the responsibility of the calling application to commit.
Processes (Item Types)
Item Type: A grouping of workflow components. All components of a workflow process must be associated with a specific item type. A set of rules which determine how the business methodology is to be routed.
Item Type : A grouping of all items of a category that share the same set of attributes For e.g.: PO Requisition is an Item Type used to group all PO requisitions created. It is like say Class in Java
Item : A specific process or transaction managed by WF process. Like instance of a class is Object.A specific run of Leave request Process will be reffered to as Item.
Attributes : These are the various input or output variables that are needed by the business process.Text,Attribute ,Date ,URL,Number,Lookup,Form are some of the attribute types.Lookup
Type: List of values that can be referenced by any component.
Activities : An activity is a unit of work that contributes toward the accomplishment of a process. An activity can be a notification, a function, an event, or a process. These are the actions performed during the business process
Function Activity: Automated unit of work defined as a PL/SQL stored procedure.
Notification Activity: Activity that sends a message to a performer.
Node : An instance of an activity in a process diagram.
Notification : An instance of a message delivered to a user.
Message: The message may request the performer, the role receiving the message, to do some work or may simply provide information. Each notification carries a message with it which is the actual text of the notification.
Performer : A user/role assigned to perform a particular (human) activity. Generally, assigned to a notification.
Role : One or more users grouped by a common responsibility or position.
Lookup type :A predefined list of values used to interact with users or define the results of an
activity.
Lookup Code : An internal name of a value defined in lookup type.
Result Type :The name of the lookup type that contains an activity’s possible result values.
Timeout : The amount of time during which a notification activity must be performed before the WF engine transitions to an alternate activity (if defined) or error process.
Transition:The relationship between completion of one activity and initialization of another activity. (Arrows)
Access Level : A numeric value ranging from 0 – 1000 defining at which level the user operates and whether user can modify the workflow definition.
Protection Level :A numeric value ranging from 0 – 1000 that represents who the workflow definition is protected from for modification
Access levels and Protection levels work in collaboration to decide who can modify what in an Oracle Workflow.
Process Activity : A Process activity represents a collection of activities in relationship. When a Process Activity is contained in another process it is called a sub-process.In other words, activities in a process can also be process themselves.
PERSISTENCE TYPE : Permanent , Temporary and Synchronous
When you define an item type, you must also specify its persistence type.The persistence type controls how long a status audit trail is maintained for each instance of the item type.
· If you set Persistence to Permanent, the runtime status information indifinitely until you specifically purge the information by calling the procedure WF_PURGE.TotalPerm().
· If you set an item type's Persistence to Temporary, you must also specify the number of days of persistence.The status audit trail for each instance of a temporary item type is maintained for at least 'n' days of persistence after its completion date.After the 'n' days of persistence, you can then use any of the WF_PURGE API's to purge the item type's runtime status information.
EVENTS : A business event is an occurrence in an internet or intranet application or program thay might be to other objects in a system or to external agents. For instance, the creation of a purchase order is an example of a business event in a purchasing application.You can define your significant events in the Event manager.
· The Workflow Engine traps errors produced by function activities by setting a savepoint before each function .If an activity produces an unhandled exception, the engine performs a rollback to the savepoint, and sets the activity to the ERROR status.The Workflow Engine never issues a commit as it is the responsibility of the calling application to commit.
What are Components of Oracle Workflow :
Components of Oracle Workflow :
Workflow Builder
Workflow Engine
Workflow Definitions Loader
Notification Systems
Workflow Monitor
Notification systems
Business Event System's
Workflow XML Loader
Directory Service's.
Workflow Builder :Work Flow builder is a Graphical interface to create and modify a business process with simple drag and drop operations.
It has two components -
* Navigator Window
* Process Window
A workflow builder would be used by a person to design and modify a workflow. It is more of a designer’s tool rather than an end-user’s tool.
Workflow Engine : The Workflow Engine embedded in the Oracle8 server monitors workflow states and coordinates the routing of activities for a process.Changes in workflow state, such as the completion of workflow activities, are signaled to the engine via a PL/SQL API or a Java API. Based on flexibly–defined workflow rules, the engine determines which activities are eligible to run, and then runs them. The Workflow Engine supports sophisticated workflow rules, including looping, branching,parallel flows, and sub-flows.
Workflow Definitions Loader :The Workflow Definitions Loader is a utility program that moves workflow definitions between database and corresponding flat file representations.It allows opening and saving workflow definitions in both a database and file.
Notification System : Each notification includes a message that contains all the information a user needs to make a decision.
Workflow Monitor : The Workflow Monitor displays an annotated view of the process diagram for a particular instance of a workflow process.Users can get a graphical depiction of their work item status. It also displays a separate status summary for the work item, the process, and each activity in the process.
Workflow Builder
Workflow Engine
Workflow Definitions Loader
Notification Systems
Workflow Monitor
Notification systems
Business Event System's
Workflow XML Loader
Directory Service's.
Workflow Builder :Work Flow builder is a Graphical interface to create and modify a business process with simple drag and drop operations.
It has two components -
* Navigator Window
* Process Window
A workflow builder would be used by a person to design and modify a workflow. It is more of a designer’s tool rather than an end-user’s tool.
Workflow Engine : The Workflow Engine embedded in the Oracle8 server monitors workflow states and coordinates the routing of activities for a process.Changes in workflow state, such as the completion of workflow activities, are signaled to the engine via a PL/SQL API or a Java API. Based on flexibly–defined workflow rules, the engine determines which activities are eligible to run, and then runs them. The Workflow Engine supports sophisticated workflow rules, including looping, branching,parallel flows, and sub-flows.
Workflow Definitions Loader :The Workflow Definitions Loader is a utility program that moves workflow definitions between database and corresponding flat file representations.It allows opening and saving workflow definitions in both a database and file.
Notification System : Each notification includes a message that contains all the information a user needs to make a decision.
Workflow Monitor : The Workflow Monitor displays an annotated view of the process diagram for a particular instance of a workflow process.Users can get a graphical depiction of their work item status. It also displays a separate status summary for the work item, the process, and each activity in the process.
What is Workflow :
Training in Oracle WorkFlow :
Processes that involve multiple users and the routing of data between the users are termed as 'WORKFLOW'.
What are Workflow Components: Workflow typically consist of 4 R’s
Rules : Rules are your company's business practices captured in software. Rules determine what activities are required to process your business data.
Roles : Roles describe how people fit into the workflow. A role is a class of users who perform the same type of work, such as clerks or managers. Your business rules typically specify what user role needs to do a specific activity. Roles allow work to be directed to Types of people than individuals hence allow flexible and easier to manitain Workflows.
Routings : Routings connect the activities in the workflow. They are the system's means of moving information from one place to another, from one step to the next. Routings specify where the information goes and what form it takes—based on results at decision point or responses to email message or completion of activity. Routings bring the flow into workflow. They get the right information to the right people at the right time, enabling users to work together to accomplish the company's goals.
Real Time : Real Time means Workflow should be able escalate an activity to next level action or role in case a certain activity is not executed in specific time, for example if the Department Manager doesn’t approve or reject the leave request within 48 hrs then it should be escalated to VP-HR.
WHAT CAN WORKFLOW DO : · Routes information to various users based on certain rules. · Tracks the responses from the users and takes actions accordingly. · Enables people to receive E-Mail notifications · Allows incorporation of custom packages and procedures · E-Mail integration of choice. · Internet enabled workflow allows you to view Workflow notifications through any Internet browser. · One can also monitor the processes through a browser.
Processes that involve multiple users and the routing of data between the users are termed as 'WORKFLOW'.
What are Workflow Components: Workflow typically consist of 4 R’s
Rules : Rules are your company's business practices captured in software. Rules determine what activities are required to process your business data.
Roles : Roles describe how people fit into the workflow. A role is a class of users who perform the same type of work, such as clerks or managers. Your business rules typically specify what user role needs to do a specific activity. Roles allow work to be directed to Types of people than individuals hence allow flexible and easier to manitain Workflows.
Routings : Routings connect the activities in the workflow. They are the system's means of moving information from one place to another, from one step to the next. Routings specify where the information goes and what form it takes—based on results at decision point or responses to email message or completion of activity. Routings bring the flow into workflow. They get the right information to the right people at the right time, enabling users to work together to accomplish the company's goals.
Real Time : Real Time means Workflow should be able escalate an activity to next level action or role in case a certain activity is not executed in specific time, for example if the Department Manager doesn’t approve or reject the leave request within 48 hrs then it should be escalated to VP-HR.
WHAT CAN WORKFLOW DO : · Routes information to various users based on certain rules. · Tracks the responses from the users and takes actions accordingly. · Enables people to receive E-Mail notifications · Allows incorporation of custom packages and procedures · E-Mail integration of choice. · Internet enabled workflow allows you to view Workflow notifications through any Internet browser. · One can also monitor the processes through a browser.
Feb 2, 2010
Examples of Collections
Example:
1- Define Record type
2- Define PLSQL table type
3- Define the PLSQL table.
1- TYPE Seasolo_ERP_Call_Rec_Type IS RECORD(
PART_NO VARCHAR2(50),
C_M_FLAG VARCHAR2(50),
QUANTITY VARCHAR2(50),
REQ_DOCK_DATE VARCHAR2(50),
LINE_KEY VARCHAR2(50),
SEA_PART_NO seaeng_ccitemnumber.ccitemnumber%TYPE,
SEA_PART_DESC VARCHAR2(240),
SEA_MOD_NO seaeng_ccfamilymodelinfo.stmodelnumber%TYPE,
CUST_PART_NO VARCHAR2(30),
LIST_PRICE VARCHAR2(50),
SPA_NO VARCHAR2(50),
SPA_ID VARCHAR2(50),
WARR_PRICE VARCHAR2(50),
WARR_MONTH VARCHAR2(50),
TOTAL_WARR_MONTH VARCHAR2(50),
CPT_NAME VARCHAR2(6),
BILLING_FLAG VARCHAR2(50),
PRICING_RULE VARCHAR2(50),
DESCRIPTION VARCHAR2(50),
MIN_ORDER_QTY VARCHAR2(50),
MIN_ORDER_QTY_MULTI VARCHAR2(50),
ERROR_CODE VARCHAR2(50),
ERROR_MSG VARCHAR2(1000),
FREIGHT_TERM VARCHAR2(50),
FREIGHT_QUAL VARCHAR2(50),
REGION seaoe_eta_zones.zone_description%TYPE);
2- TYPE Chg_Req_Tab_Type IS TABLE OF Seasolo_ERP_Call_Rec_Type
INDEX BY PLS_INTEGER;
3- l_tab Chg_Req_Tab_Type;
Manipulation à
l_tab.delete;
l_ctr := 1;
For cur1 in cur_abc
loop
if l_ctr = 1 then
l_tab(1).c1 := cur1.c1;
l_tab(1).c2 := cur1.c2;
l_ctr := l_ctr + 1;
else
l_tab(l_tab.last + 1).c1 := cur1.c1;
l_tab(l_tab.last + 1).c2 := cur1.c2;
end if;
end loop;
for indx in 1..l_tab.count
loop
dbms_output.put_line( 'avg sal = ', l_tab(indx).c1 + 20);
end loop;
Also bulk collect to a PLSQL table...
SELECT * BULK COLLECT
INTO l_chg_req_tab
FROM seacmgt_change_request_log
WHERE erp_interfaced_flag = 'N'
AND error_code IS NULL
AND error_message IS NULL
AND return_status IS NULL
AND operation_code IN ('ADD_ORDER', 'ADD_LINE')
AND active_flag = 'Y'
AND cart_key IN
( SELECT cart_key
FROM seacmgt_change_request_log
WHERE erp_interfaced_flag = 'N'
AND error_code IS NULL
AND error_message IS NULL
AND return_status IS NULL
AND operation_code = 'ADD_ORDER'
AND active_flag = 'Y')
ORDER BY change_request_key ASC;
1- Define Record type
2- Define PLSQL table type
3- Define the PLSQL table.
1- TYPE Seasolo_ERP_Call_Rec_Type IS RECORD(
PART_NO VARCHAR2(50),
C_M_FLAG VARCHAR2(50),
QUANTITY VARCHAR2(50),
REQ_DOCK_DATE VARCHAR2(50),
LINE_KEY VARCHAR2(50),
SEA_PART_NO seaeng_ccitemnumber.ccitemnumber%TYPE,
SEA_PART_DESC VARCHAR2(240),
SEA_MOD_NO seaeng_ccfamilymodelinfo.stmodelnumber%TYPE,
CUST_PART_NO VARCHAR2(30),
LIST_PRICE VARCHAR2(50),
SPA_NO VARCHAR2(50),
SPA_ID VARCHAR2(50),
WARR_PRICE VARCHAR2(50),
WARR_MONTH VARCHAR2(50),
TOTAL_WARR_MONTH VARCHAR2(50),
CPT_NAME VARCHAR2(6),
BILLING_FLAG VARCHAR2(50),
PRICING_RULE VARCHAR2(50),
DESCRIPTION VARCHAR2(50),
MIN_ORDER_QTY VARCHAR2(50),
MIN_ORDER_QTY_MULTI VARCHAR2(50),
ERROR_CODE VARCHAR2(50),
ERROR_MSG VARCHAR2(1000),
FREIGHT_TERM VARCHAR2(50),
FREIGHT_QUAL VARCHAR2(50),
REGION seaoe_eta_zones.zone_description%TYPE);
2- TYPE Chg_Req_Tab_Type IS TABLE OF Seasolo_ERP_Call_Rec_Type
INDEX BY PLS_INTEGER;
3- l_tab Chg_Req_Tab_Type;
Manipulation à
l_tab.delete;
l_ctr := 1;
For cur1 in cur_abc
loop
if l_ctr = 1 then
l_tab(1).c1 := cur1.c1;
l_tab(1).c2 := cur1.c2;
l_ctr := l_ctr + 1;
else
l_tab(l_tab.last + 1).c1 := cur1.c1;
l_tab(l_tab.last + 1).c2 := cur1.c2;
end if;
end loop;
for indx in 1..l_tab.count
loop
dbms_output.put_line( 'avg sal = ', l_tab(indx).c1 + 20);
end loop;
Also bulk collect to a PLSQL table...
SELECT * BULK COLLECT
INTO l_chg_req_tab
FROM seacmgt_change_request_log
WHERE erp_interfaced_flag = 'N'
AND error_code IS NULL
AND error_message IS NULL
AND return_status IS NULL
AND operation_code IN ('ADD_ORDER', 'ADD_LINE')
AND active_flag = 'Y'
AND cart_key IN
( SELECT cart_key
FROM seacmgt_change_request_log
WHERE erp_interfaced_flag = 'N'
AND error_code IS NULL
AND error_message IS NULL
AND return_status IS NULL
AND operation_code = 'ADD_ORDER'
AND active_flag = 'Y')
ORDER BY change_request_key ASC;
Collection Methods
Collection Methods
PL/SQL collections contain a number of built-in methods that prove useful when working with them. Here is some of them.
COUNT:
This method returns the number of elements in the collection.
Example:
declare
Type book is VARRAY(2) OF VARCHAR2(60);
book_tab book;
begin
book_tab:=book('The Angel','Hope on your way');
dbms_output.put_line('Number of elements in the Varray is 'book_tab.count());
end;
Output:
Number of elements in the Varray is 2
EXISTS:
Ø Returns Boolean true if element at specified index exists
Ø Returns Boolean false if element at specified index does not exists
Example:
DECLARE
TYPE numlist IS TABLE OF INTEGER;
n numlist := numlist (1, 3, 5, 7);
BEGIN
IF n.EXISTS (1)
THEN
DBMS_OUTPUT.put_line ('First element exists ');
ELSE
DBMS_OUTPUT.put_line ('First element Not exists');
END IF;
IF n.EXISTS (5)
THEN
DBMS_OUTPUT.put_line ('Fifth element exists ');
ELSE
DBMS_OUTPUT.put_line ('Fifth element Not exists');
END IF;
END;
Output:
First element exists
Fifth element Not exists
EXTEND :
Increases size of Collection by 1 or number specified
Note: Cannot use with Associative Array.
Ø EXTEND appends one null element to a collection.
Ø EXTEND(n) appends n null elements to a collection.
Ø EXTEND(n,i) appends n copies of the ith element to a collection.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(2,4,6,8); -- Collection starts with 4 elements.
BEGIN
DBMS_OUTPUT.PUT_LINE ('There are ' n.COUNT ' elements in N.');
n.EXTEND(3); -- Add 3 new elements at the end.
DBMS_OUTPUT.PUT_LINE ('Now there are ' n.COUNT ' elements in N.');
n.EXTEND;
DBMS_OUTPUT.PUT_LINE ('Now there are ' n.COUNT ' elements in N.');
END;
Output:
There are 4 elements in N.
Now there are 7 elements in N.
Now there are 8 elements in N.
FIRST and LAST:
Ø FIRST-Navigates to the first element in the Collection.
Ø LAST -Navigates to last element in the Collection.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,3,5,7);
counter INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('N''s first subscript is ' n.FIRST);
DBMS_OUTPUT.PUT_LINE('N''s last subscript is ' n.LAST);
END;
Output:
N's first subscript is 1.
N's last subscript is 4.
PRIOR and NEXT:
Ø PRIOR -Navigates to the previous element.
Ø NEXT -Navigates to the next element.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1966,1971,1984,1989,1999);
BEGIN
DBMS_OUTPUT.PUT_LINE('The element after #2 is #' n.NEXT(2));
DBMS_OUTPUT.PUT_LINE('The element before #2 is #' n.PRIOR(2));
END;
Output:
The element after #2 is 3
The element before #2 is 1
TRIM:
Removes the last element, or the last n elements if a number is specified.
Ø TRIM removes one element from the end of a collection.
Ø TRIM(n) removes n elements from the end of a collection.
Note: Cannot use with Associative Array.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,2,3,5,7,11);
BEGIN
n.TRIM(2); -- Remove last 2 elements.
DBMS_OUTPUT.PUT_LINE('There are ' n.COUNT ' elements in N.');
n.TRIM; -- Remove last element.
DBMS_OUTPUT.PUT_LINE ('There are ' n.COUNT ' elements in N.');
END;
Output:
There are 4 elements in N.
There are 3 elements in N.
DELETE:
Removes all elements of a Collection, or the nth element, if a parameter is specified.
Ø DELETE removes all elements from a collection.
Ø DELETE(n) removes the nth element from an associative array or nested table.
Ø DELETE(n) does nothing,if n is null,
Ø DELETE(m,n) removes all elements in the range m..n from an associative array or nested table.
Ø DELETE(m,n) does nothing,if m is larger than n or if m or n is null,
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(10,20,30,40,50,60,70,80,90,100);
BEGIN
n.DELETE(2); -- deletes element 2
n.DELETE(3,6); -- deletes elements 3 through 6
n.DELETE(7,7); -- deletes element 7
n.DELETE(6,3); -- does nothing since 6 > 3
n.DELETE; -- deletes all elements
END;
PL/SQL collections contain a number of built-in methods that prove useful when working with them. Here is some of them.
COUNT:
This method returns the number of elements in the collection.
Example:
declare
Type book is VARRAY(2) OF VARCHAR2(60);
book_tab book;
begin
book_tab:=book('The Angel','Hope on your way');
dbms_output.put_line('Number of elements in the Varray is 'book_tab.count());
end;
Output:
Number of elements in the Varray is 2
EXISTS:
Ø Returns Boolean true if element at specified index exists
Ø Returns Boolean false if element at specified index does not exists
Example:
DECLARE
TYPE numlist IS TABLE OF INTEGER;
n numlist := numlist (1, 3, 5, 7);
BEGIN
IF n.EXISTS (1)
THEN
DBMS_OUTPUT.put_line ('First element exists ');
ELSE
DBMS_OUTPUT.put_line ('First element Not exists');
END IF;
IF n.EXISTS (5)
THEN
DBMS_OUTPUT.put_line ('Fifth element exists ');
ELSE
DBMS_OUTPUT.put_line ('Fifth element Not exists');
END IF;
END;
Output:
First element exists
Fifth element Not exists
EXTEND :
Increases size of Collection by 1 or number specified
Note: Cannot use with Associative Array.
Ø EXTEND appends one null element to a collection.
Ø EXTEND(n) appends n null elements to a collection.
Ø EXTEND(n,i) appends n copies of the ith element to a collection.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(2,4,6,8); -- Collection starts with 4 elements.
BEGIN
DBMS_OUTPUT.PUT_LINE ('There are ' n.COUNT ' elements in N.');
n.EXTEND(3); -- Add 3 new elements at the end.
DBMS_OUTPUT.PUT_LINE ('Now there are ' n.COUNT ' elements in N.');
n.EXTEND;
DBMS_OUTPUT.PUT_LINE ('Now there are ' n.COUNT ' elements in N.');
END;
Output:
There are 4 elements in N.
Now there are 7 elements in N.
Now there are 8 elements in N.
FIRST and LAST:
Ø FIRST-Navigates to the first element in the Collection.
Ø LAST -Navigates to last element in the Collection.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,3,5,7);
counter INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('N''s first subscript is ' n.FIRST);
DBMS_OUTPUT.PUT_LINE('N''s last subscript is ' n.LAST);
END;
Output:
N's first subscript is 1.
N's last subscript is 4.
PRIOR and NEXT:
Ø PRIOR -Navigates to the previous element.
Ø NEXT -Navigates to the next element.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1966,1971,1984,1989,1999);
BEGIN
DBMS_OUTPUT.PUT_LINE('The element after #2 is #' n.NEXT(2));
DBMS_OUTPUT.PUT_LINE('The element before #2 is #' n.PRIOR(2));
END;
Output:
The element after #2 is 3
The element before #2 is 1
TRIM:
Removes the last element, or the last n elements if a number is specified.
Ø TRIM removes one element from the end of a collection.
Ø TRIM(n) removes n elements from the end of a collection.
Note: Cannot use with Associative Array.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,2,3,5,7,11);
BEGIN
n.TRIM(2); -- Remove last 2 elements.
DBMS_OUTPUT.PUT_LINE('There are ' n.COUNT ' elements in N.');
n.TRIM; -- Remove last element.
DBMS_OUTPUT.PUT_LINE ('There are ' n.COUNT ' elements in N.');
END;
Output:
There are 4 elements in N.
There are 3 elements in N.
DELETE:
Removes all elements of a Collection, or the nth element, if a parameter is specified.
Ø DELETE removes all elements from a collection.
Ø DELETE(n) removes the nth element from an associative array or nested table.
Ø DELETE(n) does nothing,if n is null,
Ø DELETE(m,n) removes all elements in the range m..n from an associative array or nested table.
Ø DELETE(m,n) does nothing,if m is larger than n or if m or n is null,
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(10,20,30,40,50,60,70,80,90,100);
BEGIN
n.DELETE(2); -- deletes element 2
n.DELETE(3,6); -- deletes elements 3 through 6
n.DELETE(7,7); -- deletes element 7
n.DELETE(6,3); -- does nothing since 6 > 3
n.DELETE; -- deletes all elements
END;
Nested Table
What is the Nested Table
Ans : A nested table is collection of rows, represented as a column within the table. A nested table is collection of rows, represented as a column within the table. The syntax for declaring a Nested Table is similar to the syntax for declaring the traditional PL/SQL Table.
CREATE TYPE genres_tab IS TABLE OF book_genre.genre_name%TYPE;
Size specification is required in NESTED table as varrays ?
Ans : NO
What is the TABLE command in NESTED table collection ?
Ans : To operate on collection elements, use the TABLE command. The TABLE command operator informs Oracle that you want your operations to be directed at the collection, instead of its parent table.
Using TABLE allows you to 'unnest' a collection and display its elements as you would a database table's results, top down.
Examples:
v Select the collection elements using table command as follows
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal') COLUMN_VALUE ------------------------------ CDC2 Sozhinganallur Chennai
v Update one element in a collection
UPDATE TABLE(SELECT place FROM employee WHERE name ='Neal') SET
column_value = 'CDC5' WHERE column_value = 'CDC2';
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal') COLUMN_VALUE ------------------------------ CDC5 Sozhinganallur Chennai
v Delete one element in a collection
DELETE from TABLE(SELECT place FROM employee WHERE name ='Neal')
WHERE column_value = 'CDC5'
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal'); COLUMN_VALUE ------------------------------ Sozhinganallur Chennai
v Insert one element in a collection
INSERT INTO TABLE(SELECT place FROM employee WHERE name ='Neal') VALUES ('CDC5')
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal'); COLUMN_VALUE ------------------------------ CDC5 Sozhinganallur Chennai
Que: What is the Associative Array
Ans: The “Associative Arrays” are also known as “Index-By” tables in PL/SQL. An “Associative Array” can hold a huge amount of information in several memory locations, identified by some “index.” This “index” could simply be an integer or string, or something else.
In general, an “Associative Array” stores pairs of data (either sequentially or non- sequentially). Each pair of data would generally contain a “key” and a “value.” If stored sequentially, the “key” would be nothing but a consecutive number holding the “value.” While storing information into an “Associative Array,” the user needs to specify both “key” and “value.”
The main drawback to the Associative Array type is that, like the PL/SQL Table type before it, you are not able to store them in the database.
Syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL]
INDEX BY key_type [BINARY_INTEGER PLS_INTEGER VARCHAR2(size_limit)];
Þ type_name is a type specifier used later to declare collections
Þ element_type is any PL/SQL datatype
Þ key_type can be numeric, either BINARY_INTEGER or PLS_INTEGER. It can also be VARCHAR2 or one of its subtypes VARCHAR, STRING, or LONG.
Þ
Please give us some example ,how to use the associative array
Ans:
v Declare and populate a associative array using the key_type BINARY_INTEGER
DECLARE
TYPE phone_num IS TABLE OF VARCHAR2 (60)
INDEX BY BINARY_INTEGER;
pno phone_num;
BEGIN
pno (1) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone no(1) is ' pno (1));
pno (100) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone_no(100) is ' pno (100));
END;
Output:
phone_no(1) is 022 667 943 2666
phone_no(100) is 022 667 943 2666
As you can see from the above example the elements (cells) of the array are created automatically by Oracle.
v Declare and populate a associative array using the key_type VARCHAR2
DECLARE
TYPE phone_num IS TABLE OF VARCHAR2 (60)
INDEX BY BINARY_INTEGER;
pno phone_num;
BEGIN
pno (‘office’) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone no(office) is ' pno (‘office’));
pno (‘home’) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone_no(home) is ' pno (‘office’));
END;
Output:
phone_no(office) is 022 667 943 2666
phone_no(home) is 022 667 943 2666
v Select records from table and store it in the associative array as follows:
The employee table contains the following data:
1. Select * from employee
ENO NAME PLACE
------------- ------------- -------------
11004797 Neal Ram Mumbai
11004715 VickyMumbai
11004697 Hiren Mumbai
2. Inserting the above records in another table using associative array as follows
Create table employ(eno number, name varchar2(60),place varchar2(60));
DECLARE CURSOR all_emps IS SELECT * FROM employee ORDER BY name; TYPE emp_table IS TABLE OF employee%ROWTYPE INDEX BY BINARY_INTEGER; emps emp_table; emps_max BINARY_INTEGER;BEGIN emps_max := 0;
FOR emp IN all_emps LOOP emps_max := emps_max + 1; emps(emps_max).eno := emp. eno; emps(emps_max).name := emp. name; emps(emps_max).place := emp. place;
Insert into employ values (emps(emps_max).eno, emps(emps_max).name, emps(emps_max).place);Dbms_output.put_line(‘ENO ENAME PLACE ‘);
Dbms_output.put_line(‘--------------------------------------------------------- ‘);
Dbms_output.put_line(emps(emps_max).eno ’ ‘emps(emps_max).name ’ ‘ emps(emps_max).place);
END LOOP;
END;
Output:
ENO ENAME PLACE
---------------------------------------------------------
11004797 Neal Ram Mumbai
11004715 VickyMumbai
11004697 Hiren Mumbai
Select * from employ
This Query returns the following
ENO ENAME PLACE
---------------------------------------------------------
11004797 Neal Ram Mumbai
11004715 VickyMumbai
11004697 Hiren Mumbai
Thus the records in employee table is inserted into the employ table.
Ans : A nested table is collection of rows, represented as a column within the table. A nested table is collection of rows, represented as a column within the table. The syntax for declaring a Nested Table is similar to the syntax for declaring the traditional PL/SQL Table.
CREATE TYPE genres_tab IS TABLE OF book_genre.genre_name%TYPE;
Size specification is required in NESTED table as varrays ?
Ans : NO
What is the TABLE command in NESTED table collection ?
Ans : To operate on collection elements, use the TABLE command. The TABLE command operator informs Oracle that you want your operations to be directed at the collection, instead of its parent table.
Using TABLE allows you to 'unnest' a collection and display its elements as you would a database table's results, top down.
Examples:
v Select the collection elements using table command as follows
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal') COLUMN_VALUE ------------------------------ CDC2 Sozhinganallur Chennai
v Update one element in a collection
UPDATE TABLE(SELECT place FROM employee WHERE name ='Neal') SET
column_value = 'CDC5' WHERE column_value = 'CDC2';
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal') COLUMN_VALUE ------------------------------ CDC5 Sozhinganallur Chennai
v Delete one element in a collection
DELETE from TABLE(SELECT place FROM employee WHERE name ='Neal')
WHERE column_value = 'CDC5'
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal'); COLUMN_VALUE ------------------------------ Sozhinganallur Chennai
v Insert one element in a collection
INSERT INTO TABLE(SELECT place FROM employee WHERE name ='Neal') VALUES ('CDC5')
SELECT column_value FROM TABLE(SELECT place FROM employee WHERE name = 'Neal'); COLUMN_VALUE ------------------------------ CDC5 Sozhinganallur Chennai
Que: What is the Associative Array
Ans: The “Associative Arrays” are also known as “Index-By” tables in PL/SQL. An “Associative Array” can hold a huge amount of information in several memory locations, identified by some “index.” This “index” could simply be an integer or string, or something else.
In general, an “Associative Array” stores pairs of data (either sequentially or non- sequentially). Each pair of data would generally contain a “key” and a “value.” If stored sequentially, the “key” would be nothing but a consecutive number holding the “value.” While storing information into an “Associative Array,” the user needs to specify both “key” and “value.”
The main drawback to the Associative Array type is that, like the PL/SQL Table type before it, you are not able to store them in the database.
Syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL]
INDEX BY key_type [BINARY_INTEGER PLS_INTEGER VARCHAR2(size_limit)];
Þ type_name is a type specifier used later to declare collections
Þ element_type is any PL/SQL datatype
Þ key_type can be numeric, either BINARY_INTEGER or PLS_INTEGER. It can also be VARCHAR2 or one of its subtypes VARCHAR, STRING, or LONG.
Þ
Please give us some example ,how to use the associative array
Ans:
v Declare and populate a associative array using the key_type BINARY_INTEGER
DECLARE
TYPE phone_num IS TABLE OF VARCHAR2 (60)
INDEX BY BINARY_INTEGER;
pno phone_num;
BEGIN
pno (1) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone no(1) is ' pno (1));
pno (100) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone_no(100) is ' pno (100));
END;
Output:
phone_no(1) is 022 667 943 2666
phone_no(100) is 022 667 943 2666
As you can see from the above example the elements (cells) of the array are created automatically by Oracle.
v Declare and populate a associative array using the key_type VARCHAR2
DECLARE
TYPE phone_num IS TABLE OF VARCHAR2 (60)
INDEX BY BINARY_INTEGER;
pno phone_num;
BEGIN
pno (‘office’) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone no(office) is ' pno (‘office’));
pno (‘home’) := '022 667 943 2666';
DBMS_OUTPUT.put_line ('phone_no(home) is ' pno (‘office’));
END;
Output:
phone_no(office) is 022 667 943 2666
phone_no(home) is 022 667 943 2666
v Select records from table and store it in the associative array as follows:
The employee table contains the following data:
1. Select * from employee
ENO NAME PLACE
------------- ------------- -------------
11004797 Neal Ram Mumbai
11004715 VickyMumbai
11004697 Hiren Mumbai
2. Inserting the above records in another table using associative array as follows
Create table employ(eno number, name varchar2(60),place varchar2(60));
DECLARE CURSOR all_emps IS SELECT * FROM employee ORDER BY name; TYPE emp_table IS TABLE OF employee%ROWTYPE INDEX BY BINARY_INTEGER; emps emp_table; emps_max BINARY_INTEGER;BEGIN emps_max := 0;
FOR emp IN all_emps LOOP emps_max := emps_max + 1; emps(emps_max).eno := emp. eno; emps(emps_max).name := emp. name; emps(emps_max).place := emp. place;
Insert into employ values (emps(emps_max).eno, emps(emps_max).name, emps(emps_max).place);Dbms_output.put_line(‘ENO ENAME PLACE ‘);
Dbms_output.put_line(‘--------------------------------------------------------- ‘);
Dbms_output.put_line(emps(emps_max).eno ’ ‘emps(emps_max).name ’ ‘ emps(emps_max).place);
END LOOP;
END;
Output:
ENO ENAME PLACE
---------------------------------------------------------
11004797 Neal Ram Mumbai
11004715 VickyMumbai
11004697 Hiren Mumbai
Select * from employ
This Query returns the following
ENO ENAME PLACE
---------------------------------------------------------
11004797 Neal Ram Mumbai
11004715 VickyMumbai
11004697 Hiren Mumbai
Thus the records in employee table is inserted into the employ table.
Subscribe to:
Posts (Atom)
OraApps Search
Custom Search