Wednesday, September 26, 2012

Oracle Workflow - 11i

Introduction to Oracle Workflow

Business processes today involve getting many types of information to multiple people according to rules that are constantly changing. Oracle Workflow lets you automate and continuously improve business processes, routing information of any type according to business rules you can easily change to people both inside and outside your enterprise.

Routing Information

With so much information available, and in so many different forms, how do you get the right information to the right people? Oracle Workflow lets you provide each person with all the information they need to act. Oracle Workflow can route supporting information to each decision-maker in a business process.

Defining and Modifying Business Rules

Oracle Workflow lets you define and continuously improve your business processes using a drag–and–drop process designer. Unlike workflow systems that simply route documents from one user to another with some approval steps, Oracle Workflow lets you model sophisticated business processes. You can define processes that loop, branch into parallel flows and then rendezvous, decompose into subflows, and more. Because Oracle Workflow can decide which path to take based on the result of a stored procedure, you can use the full power of PL/SQL, to express any business rule that affects a workflow process.

Delivering Electronic Notifications

Oracle Workflow extends the reach of business process automation throughout the enterprise and beyond to include any E–mail or Internet user. Oracle Workflow lets people receive notifications of items awaiting their attention via E–mail, and act based on their E–mail responses. You can even view your list of things to do, including necessary supporting information, and respond using a standard Web browser or an Oracle Applications Notification form.

Major features of Oracle Workflow

Oracle Workflow Builder

Oracle Workflow Builder lets you create, view, or modify a business process with simple drag and drop operations. Using the Workflow Builder, you can create and modify all workflow objects, including activities, item types, and messages. Oracle Workflow Builder is a graphical tool for creating, viewing, and modifying workflow process definitions. It contains a Navigator window that you use to define the activities and components of your business process. You then assemble the activities in a process window to create a process diagram.

Workflow Definitions Loader

The Workflow Definitions Loader is a utility program that moves workflow definitions between database and corresponding flat file representations. You can use it to move workflow definitions from a development to a production database, or to apply upgrades to existing definitions. In addition to being a standalone server program, the Workflow Definitions Loader is also integrated into Oracle Workflow Builder, allowing you to open and save workflow definitions in both a database and file.

Complete Programmatic Extensibility

Oracle Workflow lets you include your own PL/SQL procedures as activities in your workflows. Without modifying your application code, you can have your own program run whenever the Workflow Engine detects that your program’s prerequisites are satisfied.

Electronic Notifications

Oracle Workflow lets you include users in your workflows to handle activities that cannot be automated, such as approvals for requisitions or sales orders. Electronic notifications are routed to a role, which can be an individual user or a group of users. Any user associated with that role can act on the notification. Each notification includes a message associated with it, which contains all the information a user needs to make a decision, as well as possible responses. Oracle Workflow interprets each response and moves on to the next workflow activity.

Personal Inbox

Users who connect to Oracle Applications can see all the notifications awaiting their attention in a common Notification Viewer form, or Personal Inbox. Choosing a notification takes users to a Notification Details window that describes any actions they need to take. The Notification Details window can take users directly to an Oracle Application form where they can perform the necessary action.

Electronic Mail Integration

Electronic mail (E–mail) users can receive notifications of outstanding work items and can respond to those notifications using their E–mail application of choice. An E–mail notification can include an HTML attachment that provides another means of responding to the notification.

Internet–Enabled Workflow

Any user with access to a standard Web browser can be included in a workflow. Web users can access a Notification Web page to see their outstanding work items, then navigate to additional pages to see more details or provide a response.

Monitoring and Administration

Workflow administrators and users can view the progress of a work item in a workflow process by connecting to the Workflow Monitor using a standard Web browser that supports Java. The Workflow Monitor displays an annotated view of the process diagram for a particular instance of a workflow process, so that users can get a graphical depiction of their work item status. The Workflow Monitor also displays a separate status summary for the work item, the process, and each activity in the process.

Workflow Processes

Oracle Workflow manages business processes according to rules that you define. The rules, which we call a workflow process definition, include the activities that occur in the process and the relationship between those activities. An activity in a process definition can be an automated function defined by a PL/SQL stored procedure, a notification to a user or role that may optionally request a response, or a subflow that itself is made up of a more granular set of activities. A workflow process is initiated when an application calls a set of Oracle Workflow Engine APIs. The Workflow Engine takes over by driving the relevant work item defined by the application, through a specific workflow process definition. According to the workflow process definition, the Workflow Engine performs automated steps and invokes appropriate agents when external processing is required. The following diagram depicts a simplified workflow process definition that routes a requisition to a manager or set of managers for approval.

 

Workflow Process Components

Depending on the workflow process you wish to create, you need to define all or some of the following types of components to make up the process:

Item Types

An item type is a classification of the components that make up a workflow process.
You must associate any component that you create for a process, such as a function activity or a message, with a particular item type.
For example, purchase order requisition can be an item type while a purchase order requisition identified by a particular ID number is an item of that item type.



Item Type Attributes

An item type attribute is a property associated with a given item type. It acts as a global variable that can be referenced or updated by any activity within a process.
Attribute types
•Text—The attribute value is a string of text.
•Number—The attribute is a number.
•Date—The attribute value is a date with the optional format mask you specify.
•Lookup—The attribute value is one of the lookup code values in a specified lookup type.
•Form—The attribute value is an Oracle Applications internal form function name and its optional form function parameters.
•URL—The attribute value is a Universal Resource Locator (URL) to a network location.
•Document—The attribute value is an attached document.
•Role—The attribute value is the internal name of a role.
•Attribute—The attribute value is the internal name of another existing item type attribute that you want to maintain references to in a process.

Lookup Types

Lists the lookup types associated with the current item type. A lookup type has one or more values called lookup codes associated with it. A lookup type is a list of values that can be referenced by a message, or by a notification, function, or process as its possible result type.

Messages

Lists the messages that a notification activity associated with the current item type can send to a user or role. A message can have message attributes associated with it.

Notification Activity

Lists the notification activities associated with the current item type. A notification activity sends a message to a user or role. Oracle Workflow sends a notification to a role when the Workflow Engine executes a notification activity in a workflow process. The notification activity may designate the role as being responsible for performing some human action or may simply relay process–related information to the role.

Functions

Lists the function activities associated with the current item type. A function activity represents a PL/SQL stored procedure that the Workflow Engine executes automatically.
All PL/SQL stored procedures that are called by function or notification activities in an Oracle Workflow process should follow some standard API format so that the Workflow Engine can properly execute the activity.

 

 

Processes

Lists the process activities or workflow process definitions for the current item type.
The Process window in Oracle Workflow Builder graphically represents the activities (icons) and transitions (arrows) for a particular process. Each activity is a node, a logical step that contributes toward the completion of a process.
Notification, function, and process activities make up the nodes of a process.



Fig.  Process Diagram

Fig. Workflow Process Components

Defining PL/SQL procedures for Oracle workflow

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

Standard API Format

1...procedure <procedure name> ( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out varchar2 ) is
2...<local declarations>
3...begin
if ( funcmode = ’RUN’ ) then
<your RUN executable statements>
resultout := ’COMPLETE:<result>’;
return;
end if;
4...if ( funcmode = ’CANCEL’ ) then
<your CANCEL executable statements>
resultout := ’COMPLETE’;
return;
end if;
5...if ( funcmode = ’RESPOND’ ) then
<your RESPOND executable statements>
resultout := ’COMPLETE’;
return;
end if;
6...if ( funcmode = ’FORWARD’ ) then
<your FORWARD executable statements>
resultout := ’COMPLETE’;
return;
end if;
7...if ( funcmode = ’TRANSFER’ ) then
<your TRANSFER executable statements>
resultout := ’COMPLETE’;
return;
end if;
8...if ( funcmode = ’TIMEOUT’ ) then
<your TIMEOUT executable statements>
resultout := ’COMPLETE’;
return;
end if;
9...if ( funcmode = ’<other funcmode>’ ) then
resultout := ’ ’;
return;
end if;
10...exception
when others then
WF_CORE.CONTEXT (’<package name>’, ’<procedure name>’, <itemtype>, <itemkey>,
to_char(<actid>), <funcmode>);
raise;
11...end <procedure name>;

Parameters

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

itemtype - The internal name for the item type.
itemkey-A string that represents a primary key generated by the workflow–enabled application for the item type. The string uniquely identifies the item within an item type.
actid- The ID number of the activity that this procedure is called from.
funcmode-The execution mode of the activity. If the activity is a function activity, the mode is either ’RUN’ or ’CANCEL’. If the activity is a notification activity, with a post–notification function, then the mode can be ’RESPOND’, ’FORWARD’, ’TRANSFER’, or
’TIMEOUT’.
resultout-this parameter represents the expected result that is returned when the procedure completes. The possible results are:
COMPLETE:<result_code>—activity completes with the indicated result code.
WAITING—activity is pending, waiting on another activity to complete before it completes.
DEFERRED:<date>—activity is deferred to a background engine for execution until a given date.
NOTIFIED:<notification_id>:<assigned_user>— an external entity is notified that an action must be performed.
ERROR:<error_code>—activity encounters an error and returns the indicated error code.

Triggering Mechanism

Determine how the process will be triggered.
Example:
CREATE OR REPLACE TRIGGER <trigger_name>
AFTER INSERT ON <table_name>
FOR EACH ROW
BEGIN
  IF :new.<field_name> = <condition> THEN
    <package_name>.init_wf( :new.<parameter1>,
                              :new.<parameter2>,
                              :new.<parameter3>,
                              :new.<parameter4>,
                              :new.<parameter5>);
  END IF;
END;

Oracle workflow APIs

The APIs consist of views and PL/SQL functions and procedures that you can use to access the Workflow Engine, the Notification System, and workflow data.

Overview of the Workflow Engine

The Workflow Engine manages all automated aspects of a workflow process for each item. The engine is implemented in server–side PL/SQL and is activated whenever a call to a workflow procedure or function is made.
The Workflow Engine performs the following services for a client application:
•It manages the state of all activities for an item, and in particular, determines which new activity to transition to whenever a prerequisite activity completes.
•It automatically executes function activities and sends notifications.
•It maintains a history of an activity’s status.
•It detects error conditions and executes error processes.

Workflow Engine APIs

An application program or a workflow function can call the Workflow Engine APIs 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.
Eg.
CreateProcess -Creates a new runtime process for an application item. Application calls CreateProcess to set up the information needed to start the defined process.
StartProcess -Begins execution of the specified process. The engine locates the activity marked as START and then executes it.

Additional Workflow Engine Features

Completion Processing
Engine processing is triggered whenever a process activity completes and calls the Workflow Engine API. The engine then attempts to execute (or mark for deferred execution) all activities that are dependent on the completed activity.

Deferred Processing
The engine has a deferred processing feature that allows long–running tasks to be handled by background engines instead of in real time. Deferring the execution of activity functions to background engines allows the Workflow Engine to move forward to process other activities that are currently active.
Each activity has a user–defined processing cost. You can set this cost to be small if the activity merely sets an item attribute, or you may set it to be very high if the activity performs a resource–intensive operation. If the result of a completed activity triggers the execution of a costly function, you might want to defer the execution of that costly function to a background engine.

Error Processing
The Workflow Engine traps errors produced by function activities by setting a savepoint before each function activity. If an activity produces an unhandled exception, the engine performs a rollback to the savepoint, and sets the activity to the ERROR status.

Notification Handling

Overview of Notification Handling

Oracle Workflow sends a notification to a role when the Workflow Engine executes a notification activity in a workflow process. The notification activity may designate the role as being responsible for performing some human action or may simply relay process–related information to the role.
You can have your workflow notifications delivered to you as E–mail messages if your notification preference is set to ’Plain text mail’, ’HTML mail’, or ’Plain text mail with attachments’ in the User Preferences web page and your workflow administrator sets up the Notification Mailer to run.

Document Management Integration in Notifications

To access a referenced document management (DM) system document in a specific workflow notification, you can view the notification from the Detail Notification web page and select the document link to display the document in a DM integration screen.

Implementing the Notification Mailer

The Notification Mailer is a program that performs E–mail send and response processing for the Oracle Workflow Notification System. It polls the database for messages that have to be sent, and performs the following action for each message:
Ø  Resolves the recipient role to a single E–mail address, which itself can be a mail list.
Ø  Switches its database session to the role’s preferred language and territory as defined by the directory service.
Ø  Generates the message and any optional attachments using the appropriate message template.
Ø  Sends the message via UNIX Sendmail, Oracle Office/InterOffice, or any MAPI–compliant mail application on Windows NT.

The Notification Mailer also processes responses by interpreting the text of messages mailed to its response mail account and calling the appropriate notification response function to complete the notification. Once you set up the Notification Mailer to run, it continually polls the database for messages to send and checks its response mail account for responses to process. You do not have to do anything else unless you have a need to shut it down and restart it again with different parameters.

Monitoring Workflow Processes

The Workflow Monitor is a tool that allows you to view and administer the status of a specific instance of a workflow process. You can use the point–and–click interface to display detailed status information about activities in the process as well as about the process as a whole. The Workflow Monitor can be run in ’USER’ or ’ADMIN’ mode, where ’ADMIN’ mode provides additional details and functionality pertinent only to a workflow administrator.
The Workflow Monitor consists of the following sections:

Process Title
The process title appears in the upper left of the Workflow Monitor and displays the name of the workflow process and the name of the item type and user key that uniquely identify a running instance of that process in the process diagram window.

Process Diagram Window
The process diagram window is a scrolling canvas that displays the diagram of the workflow process or subprocess currently listed in the process title.
Colour of BoxStatePossible Status Code
RedErrorERROR
GreenActiveACTIVE, NOTIFIED,
DEFERRED
YellowSuspendedHOLD
<none>NormalCOMPLETE,
WAITING, NULL
Any transition (arrow) that has been traversed appears with a thick green line, while an untraversed transition appears with a thin black line.

You can select the subprocess activity and choose Zoom In to drill down to the subprocess’ diagram. Choose Zoom Out to navigate back to the process at the previous level.

Detail Tab Window
The detail tab window, which appears below the process diagram, is a vertically scrollable display area that provides information about a selected process or activity.
Current Location : Process Display Name/Activity Display Name
Item Type             : Item Type display name (internal name)
Activity Name      : Activity display name (internal name)
Message              : Message internal name
Function              : Name of PL/SQL procedure called by activity
Result Type         : Result type display name (internal name)

Administration Buttons
The administration buttons appear beneath the detail tab window only when the Workflow Monitor is run in ’ADMIN’ mode.
Abort Process—Calls WF_ENGINE.Abort Process to abort the selected process and cancel any outstanding notifications
Suspend Process—Calls WF_ENGINE.SuspendProcess to suspend the selected process so that no further activities can be transitioned to.
Resume Process—Calls WF_ENGINE.ResumeProcess to resume the suspended process to normal execution status.


Exercise

Workflow Process Diagram
Notification               to
   SYSADMIN
 
Oval:  StartFlowchart: Decision:    Alert
Condition
Type
                                                                        
                                                                             Periodic










                                                                                
         
                                                                    Event                        


Lesson 1: Create Workflow Process
1.      Oracle Workflow Builder 2.5
2.      File/Quick Start Wizard
Item Type
Internal NameTRNG_WF
Display NameTraining Workflow
Persistence TypeTemporary
Number of Days0
Process
Internal NameTRNG_WF
Display NameTraining Workflow
Item Attributes

Internal NameDisplay NameTypeLength
ALERT_IDALERT_IDAlert IdNumber
ALERT_NAMEALERT_NAMEAlert NameText50
CREATED_BYCREATED_BYCreated ByNumber
CREATION_DATECREATION_DATECreation DateDate
ALERT_CONDITION_TYPEALERT_CONDITION_TYPEAlert Condition TypeText1
ENABLED_FLAGENABLED_FLAGEnabled FlagText1
TABLE_IDTABLE_IDTable IdNumber
TABLE_APPLICATION_IDTABLE_APPLICATION_IDTable Application IdNumber
INSERT_FLAGINSERT_FLAGInsert FlagText1
UPDATE_FLAGUPDATE_FLAGUpdate FlagText1
TABLE_NAMETABLE_NAMETable NameText30
APPLICATION_NAMEAPPLICATION_NAMEApplication NameText30
USER_NAMEUSER_NAMEUser NameText30
Messages
Internal NameOPERATIONS
Display NameOperations
DescriptionOperations
PriorityNormal
SubjectEvent alert info
Text BodyEvent Alert
-----------------
Alert Name            :&ALERT_NAME
Created By            :&USER_NAME
Creation Date       :&CREATION_DATE
Enabled                :&ENABLED_FLAG
Table Name          :&TABLE_NAME
Application Name  :&APPLICATION_NAME
Insert Flag              :&INSERT_FLAG
Update Flag           :&UPDATE_FLAG


Internal NameSYSADMIN
Display NameSysadmin
DescriptionSysadmin
PriorityNormal
SubjectPeriodic alert info
Text BodyPeriodic Alert
---------------------
Alert Name     :&ALERT_NAME
Created By     :&USER_NAME
Creation Date :&CREATION_DATE
Enabled         :&ENABLED_FLAG


Notifications
Internal NameOPERATIONS
Display NameNotification to Operations
DescriptionOperations
Result TypeNone
MessageOperations
Node/Performer
TypeConstant
ValueOPERATIONS


Internal NameSYSADMIN
Display NameNotification to SYSADMIN
DescriptionSysadmin
Result TypeNone
MessageSysadmin
Node/Performer
TypeConstant
ValueSYSADMIN


Function
Internal NameALERT_INFO
Display NameAlert Information
DescriptionAlert Information
Function NameTRNG_PACK_WF.ALERT_INFO
Function TypePL/SQL
Result TypeBoolean

Lesson 2: Create Package
(a)    Package Specification
CREATE OR REPLACE PACKAGE TRNG_PACK_WF IS
/*******************************************************************************************
Procedure     :  INIT_WF
Description   : Procedure to initialize and run workflow
Procedure     :  ALERT_INFO
Description   :  Procedure  to get Alert Information
****************************************************************************************************/
PROCEDURE INIT_WF(p_alert_id                  IN NUMBER,
                                        p_alert_name            IN VARCHAR2,
                                        p_created_by              IN NUMBER,
                                        p_creation_date           IN DATE,
                                        p_alert_condition_type          IN VARCHAR2,
                                        p_enabled_flag            IN VARCHAR2,
                                        p_table_id                    IN NUMBER,
                                        p_table_application_id          IN NUMBER,
                                        p_insert_flag               IN VARCHAR2,
                                        p_update_flag            IN VARCHAR2);

PROCEDURE ALERT_INFO(p_itemtype        IN VARCHAR2,
                                        p_itemkey                 IN VARCHAR2,
                                        p_actid                      IN NUMBER,
                                        p_funcmode             IN VARCHAR2,
                                        p_resultout          OUT VARCHAR2);
END;
/

--> (a)   Package Body

CREATE OR REPLACE PACKAGE BODY TRNG_PACK_WF IS
/******************************************************************************************
Procedure     :  INIT_WF
Description   : Procedure to initialize and run workflow
**************************************************************************************************/
PROCEDURE INIT_WF(p_alert_id                  IN NUMBER,
                                        p_alert_name            IN VARCHAR2,
                                        p_created_by              IN NUMBER,
                                        p_creation_date           IN DATE,
                                        p_alert_condition_type          IN VARCHAR2,
                                        p_enabled_flag            IN VARCHAR2,
                                        p_table_id                    IN NUMBER,
                                        p_table_application_id          IN NUMBER,
                                        p_insert_flag               IN VARCHAR2,
                                        p_update_flag            IN VARCHAR2) IS
l_itemtype       WF_ITEMS.ITEM_TYPE%TYPE := 'TRNG_WF';
l_itemkey         WF_ITEMS.ITEM_KEY%TYPE  := to_char(p_alert_id);
l_run_wf_in_background   CONSTANT WF_ENGINE.THRESHOLD%TYPE:= -1;
l_WorkflowProcess  VARCHAR2(30) := 'TRNG_WF';
BEGIN
/* Create Process */
      WF_ENGINE.CREATEPROCESS(itemtype => l_itemtype, itemkey =>  l_itemkey , process => l_WorkflowProcess);
      WF_ENGINE.THRESHOLD := l_run_wf_in_background;
/* set item attributes*/
WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype, itemkey =>  l_itemkey , aname => 'ALERT_ID', avalue => p_alert_id);
WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey =>  l_itemkey , aname => 'ALERT_NAME', avalue => p_alert_name);
WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype, itemkey =>  l_itemkey , aname => 'CREATED_BY', avalue => p_created_by);
WF_ENGINE.SETITEMATTRDATE(itemtype => l_itemtype, itemkey =>  l_itemkey , aname => 'CREATION_DATE', avalue => p_creation_date);
WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey =>  l_itemkey , aname => 'ALERT_CONDITION_TYPE', avalue => p_alert_condition_type);
WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey =>  l_itemkey , aname => 'ENABLED_FLAG', avalue => p_enabled_flag);
WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype, itemkey =>  l_itemkey , aname => 'TABLE_ID', avalue => p_table_id);
WF_ENGINE.SETITEMATTRNUMBER(itemtype => l_itemtype, itemkey =>  l_itemkey , aname => 'TABLE_APPLICATION_ID', avalue => p_table_application_id);
WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey =>  l_itemkey , aname => 'INSERT_FLAG', avalue => p_insert_flag);
WF_ENGINE.SETITEMATTRTEXT(itemtype => l_itemtype, itemkey =>  l_itemkey , aname => 'UPDATE_FLAG', avalue => p_update_flag);
/* Start Process */
WF_ENGINE.STARTPROCESS(itemtype => l_itemtype, itemkey => l_itemkey);
EXCEPTION
     WHEN OTHERS THEN
          WF_CORE.CONTEXT('trng_wf','init_wf',l_itemtype,l_itemkey,to_char(p_alert_id));
            Raise;
END init_wf;

/***************************************************************************************************
Procedure     :  ALERT_INFO
Description   :  Procedure  to get Alert Information
****************************************************************************************************/
PROCEDURE ALERT_INFO(p_itemtype        IN VARCHAR2,
                                        p_itemkey                 IN VARCHAR2,
                                        p_actid                      IN NUMBER,
                                        p_funcmode             IN VARCHAR2,
                                        p_resultout          OUT VARCHAR2) IS
no_info  EXCEPTION;
l_alert_id ALR_ALERTS.ALERT_ID%TYPE := WF_ENGINE.GETITEMATTRNUMBER(p_itemtype,p_itemkey,'ALERT_ID');
l_alert_condition_type          ALR_ALERTS.ALERT_CONDITION_TYPE%TYPE := WF_ENGINE.GETITEMATTRTEXT(p_itemtype,p_itemkey,'ALERT_CONDITION_TYPE');
l_table_id          ALR_ALERTS.TABLE_ID%TYPE := WF_ENGINE.GETITEMATTRNUMBER(p_itemtype,p_itemkey,'TABLE_ID');
l_table_application_id          ALR_ALERTS.TABLE_ID%TYPE := WF_ENGINE.GETITEMATTRNUMBER(p_itemtype,p_itemkey,'TABLE_APPLICATION_ID');
l_created_by          ALR_ALERTS.CREATED_BY%TYPE := WF_ENGINE.GETITEMATTRNUMBER(p_itemtype,p_itemkey,'CREATED_BY');
l_table_name FND_TABLES.TABLE_NAME%TYPE;
l_application_name FND_APPLICATION_TL.APPLICATION_NAME%TYPE;
l_user_name FND_USER.USER_NAME%TYPE;
BEGIN
   IF (p_funcmode = 'RUN') THEN
      BEGIN
          Select Table_name
           Into    l_table_name
            From  FND_TABLES
           Where Table_id = l_table_id
            And     Application_Id = l_table_application_id;
      EXCEPTION When No_Data_Found Then
          L_table_name := '*********';
      END;
WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype, itemkey =>  p_itemkey , aname => 'TABLE_NAME', avalue => l_table_name);
     BEGIN
          Select Application_name
           Into    l_application_name
            From  FND_APPLICATION_TL
           Where Application_Id = l_table_application_id;
      EXCEPTION When No_Data_Found Then
          L_application_name := '*********';
      END;
WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype, itemkey =>  p_itemkey , aname => 'APPLICATION_NAME', avalue => l_application_name);
      BEGIN
          Select User_Name
           Into    l_user_name
            From  FND_USER
           Where User_Id = l_created_by;
      EXCEPTION When No_Data_Found Then
          L_user_name := '*********';
      END;
 WF_ENGINE.SETITEMATTRTEXT(itemtype => p_itemtype, itemkey =>  p_itemkey , aname => 'USER_NAME', avalue => l_user_name);
      IF (L_alert_condition_type = 'P') THEN
           P_resultout := 'COMPLETE:T';
      ELSIF (L_alert_condition_type = 'E') THEN
            P_resultout := 'COMPLETE:F';
      END IF;
   ELSIF (p_funcmode = 'CANCEL') THEN
        Raise no_info;
   ELSIF (p_funcmode = 'TIMEOUT') THEN
       Raise no_info;
   END IF;
EXCEPTION
  WHEN no_info THEN
     P_resultout := 'COMPLETE:F';
     Return;
  WHEN others THEN
      WF_CORE.CONTEXT('TRNG_WF','ALERT_INFO',p_itemtype,p_itemkey, to_char(l_alert_id));
      Return;
END alert_info;
END;
/

Lesson 3: Create Trigger for Workflow
Create or replace trigger trng_wf
after insert on alr_alerts
for each row
begin
  trng_pack_wf.init_wf(:new.alert_id,
                       :new.alert_name,
                       :new.created_by,
                       :new.creation_date,
                       :new.alert_condition_type,
                       :new.enabled_flag,
                       :new.table_id,
                       :new.table_application_id,
                       :new.insert_flag,
                       :new.update_flag);
end;
/

Lesson 4: View Workflow Process & Notifications
Navigation Path: System administrator/Workflow/Processes
Navigation Path: System administrator/Workflow/Notifications

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect