Triggering a custom workflow

d) Triggering a custom workflow

We are ready with our custom workflow definition and saved in database(Ref:- Custom Workflow Development ).Now we have to prepare pl/sql procedure that will trigger our custom workflow.Our custom workflow will be triggered when it(pl/Sql procedure) will be called by any other processes(viz. from a web page or from any other  pl/sql procedure etc).  The input parameter of this process must be as mentioned below. 1)    Person Id :- person id of the person who has submitted the request 2)    Leave Type:- Type of leave person has applied 3)    Leave From Date:- Start date of leave 4)    Leave to Date:- End date of leave We are taking input to ensures that we received all the information that user entered in web page application and required for workflow process. Item Key When  a workflow is triggered,It creates a instance of the workflow definition. To identify the instance of the workflow a string is required.  The string that uniquely identifies the instance of the workflow definition is known as item-key. Item-key can be alpha-numeric. Note:-  Different item-type can have same item-key but for same item-type it is unique. Analogy can be drawn with concurrent program internal name and request id. Development of Custom Procedure The following tasks needs to be performed to trigger  a custom workflow successfully 1) Create the process 2) Set attribute values 3) Start the process 4) commit transaction 1) Create the process Oracle provided us  API to create the instance of the workflow definition process. wf_engine.createprocess(itemtype => <Internal name of item type>,                                                 itemkey  => <Item key value>,                                                 process  => <Internal name of process>                                               ); Here v_process should be internal name of runnable process.Here it is ‘XX_TEST_PROC’. 2)Set attribute values Once the process is created, it actually creates the instance of the workflow definition. Once the instance is created, we need to set attribute values. To set the attribute values we need to call oracle provided workflow apis wf_engine.SetItemAttrText(itemtype => <Internal name of item type>,                                            itemkey  => <Item key value>,                                            aname    =><Internal name of attribute>,                                            avalue   =><Attribute value that need to set>); wf_engine.SetItemAttrDate(itemtype => <Internal name of item type>,                                            itemkey  => <Item key value>,                                            aname    =><Internal name of attribute>,                                            avalue   =><Attribute value that need to set>); wf_engine.SetItemAttrnumber(itemtype => <Internal name of item type>,                                                 itemkey  => <Item key value>,                                                 aname    =><Internal name of attribute>,                                                 avalue   =><Attribute value that need to set>); 3)Start the process wf_engine.startprocess(<Internal name of item type>type, <Item key value>); 4) Commit Transactions commit; Triggering Workflow from a pl/Sql block Instead of trigerring workflow from a Web-page just for sake of  illustration we will trigger workflow from a pl/sql block by passing necessary parameter values to our  TRIGGER_WORKFLOW procedure.   DECLARE BEGIN XX_TEST_LEAVE_PKG.TRIGGER_WORKFLOW(P_PERSON_ID        => 14760                                   ,P_LEAVE_TYPE       => 'Seek Leave'                                   ,P_LEAVE_FROM_DATE  => '01-MAR-2011'                                   ,P_TO_DATE          => '04-MAR-2011'                                   ); END;     Once the above block is exceuted it will trigger the custom workflow and notification will go to the approver for approval. Please see the screen shot below     To track the present status of the workflow follow the below mentioned navigation. Login as (Sysadmin) >> Workflow Administrator Web Applications >> Status Monitor>>   Enter the internal name of  item type of your workflow under "Type Internal Name" and click on "GO". You will find the list of workflows that was triggered. Select any of them and click on View Diagram.

Č

ċ

XX_TEST_LEAVE_PKG.pkg 

(6k)

Custom Workflow Development

c) Custom Workflow Development

Our Custom workflow design in pen and paper is almost finalized (Refer our previous discussion  Requirement Mapping & Custom Workflow Design ). We are also ready with the probable list of Workflow components. Now we will proceed with the step-wise development of our custom workflow. Design Components To design our discussed custom workflow, we need to create following workflow components Item Attribute 1)      Requestor person id            (Type-Number) 2)      Requestor User name         (Type-Text) 3)      Requestor full name           (Type-Text) 4)      Leave Type                         (Type-Text) 5)      Leave from date                 (Type-Date) 6)      Leave to date                      (Type-Date) 7)      Supervisor person id           (Type-Number) 8)      Supervisor User name        (Type-Text) 9)      Supervisor full name          (Type-Text) Lookup Type/Code Lookup code a) Approve b) Reject Lookup Type:- APPROVE_REJ Function Activity 1) Start (Type:- START) 2) End  (Type END) Message Message to Supervisor with the details of the applied leave. Notification notification to send message to supervisor and approve or reject the same. Workflow Development Now we will proceed with the stepwise development of workflow process and its components. 1) Open workflow Builder.Click on the Hammer icon to open it in Developer mode. 2) Click on the New icon, It will open up a pop-up window to create a item-type.     Internal Name:- XX_TEST     Display Name:- Test Leave Workflow Keep the other filed as it is(with its default value-as shown in picture) Note:-  1) We can also use the Quick start Wizard.Quick Start Wizard will help us to creates our new item type and new process activity.            2) The persistence type controls how long a status audit trail is maintained for each instance of the item type.                If we set Persistence to Permanent, the runtime status information is maintained indefinitely until you specifically purge the information by calling the               procedure WF_PURGE.TotalPerm().               If we set an item type's Persistence to Temporary, we must also specify the number of Defining Workflow Process days of persistence ('n'). 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, we can then use any of the WF_PURGE APIs to purge the item type's runtime status information. Here we are keeping Persistence to 'Temporary' and Number of Days to Zero(0). 3) Now to create Item Attribute, Right click on the "Attribute" and select 'New Attribute', it will open up a pop-up window which will help us to create item attribute   Internal Name:- LEAVE_FROM_DATE Display Name:- Leave From Date Type:-              Date Keep other field as it is . Define the other listed item attributes as mentioned above. 4) Now to create Item lookup Types/code, Right click on the "Item Types" and select 'New', it will open up a pop-up window which will help us to create Lookup Type Internal Name :- APPROVE_REJ Display Name :- Approve or Reject Keep the field and other tabs as it is, Now select the newly created Lookup Type (i.e, APPROVE_REJ) and richt click on it & select 'New Lookup Code'. It will open up a pop-up window that will help us to create new lookup code. Internal Name:- APPROVE Display Name:- Approve In a similar ways create the other lookup code(REJECT). 5) To create a message Right click on the "Message" in Navigator window and click on "New Message", it will open up a pop-up window that will help us to craete a new message. Internal Name:- XX_TEST_APPROVER_MESSAGE Display Name:- test Leave Approver Message Priority          :- Normal Select the Subject Line and the body of the message. Body of the message can be in 'Plain Text Mode' or in HTML mode. Subject Line:-Leave Approval for person &REQUESTER_EMP_NAME(&REQUESTER_EMP_NO) Body:- A sample body is given below. Dear <b>&SUP_EMP_NAME</b>,     The person &REQUESTER_EMP_NAME(&REQUESTER_EMP_NO) has submitted leave requisition. Request you please take necessary action on the requisition. <br></br> <u>Leave Details</u> <table> <tr><td>Leave Type</td><td>Start Date</td><td>End Date</td></tr> <tr>td>&LEAVE_TYPE</td><td>&LEAVE_FROM_DATE</td><td>&LEAVE_TO_DATE</td></tr> </table> <br></br> Regards HR Team Here we have used Token substitute an attribute.The method of doing this &<Internal name of Attribute>. In runtime Workflow replaces those(As mentioned in bold) with the corresponding attribute value. Note:- To Token substitute an attribute, we have to make those attribute as message attribute. To make a attribute available in message copy the respective item-attribute and paste it in message. All the message attribute will appear under the respective message.Keep the source as 'Send'. It means we are sending the value of the attribute to the performer of the notification(to which th message is attached). If the source is 'Respond', it means we want the value from the responder(will be discussed in detail later). In our example Supervisor either can approve the leave requisition or Reject the same. Hence we have provide the supervisor two result button (APPROVE ; REJECT). To create the button click on the result tab(as shown in the picture) above and the add the desired lookup type. Workflow will automatically place the buttons in run time. Provide the following details Display Name:- Approve/Reject Lookup Type:- Approve Or Reject 6) To create notification right click on the "Notifications" and select "New Notifications". It will open a pop-up window that will help us to create a Notification Internal Name:- XX_TEST_LEAVE_NOTIF Display Name:- Test Leave Notification Message:- Attach the newly created message. Result Type:- Since our notification will go to Supervisor and he/she can approve/reject leave requisition.Thus we need to have two different transition from the notification. Because of this we need to attach our newly created Lookup type. Keep the other tabs as it is, Note:- 1) Check Expand Roles to send an individual copy of the notification message to each user in the role. The notification remains in a user's notification queue              until the user responds to or closes the notification.              one should expand roles to send out a broadcast-type/FYI type(message that don't require any response from performer) message for which he/she wants              all users of that role to see.Otherwise if one user from the roles closes the notification, it will be closed from all the other users.           2) Here we don't have any option to attach performer with the notification. It will be available once we use the notification in the process. 7) To create a Function activity, right click on "Function" and select "New Function". It will opens up a pop-up window which will help us to create function. Internal Name:- START Display Name:- Start Function Name:- WF_STANDARD.NOOP** Function Type:- PL/SQL Result Type:- <None>(keep default value as it is) Cost          :- 0.00( Keep default value as it is) ** => WF_STANDARD.NOOP is oracle provided Seeded package.procedure mainlly used as Pl/Sql procedure for Start and End activity. Note:- Here we don't have any option to mark the function as 'START' or 'END'. This can be done once we use the same in a process. 8) Now to create Workflow process right click on "Processes" in Navigation Tree and select "New Process". It will pop-up a window which will help us to define process. Internal Name:-XX_TEST_PROC Display Name:- Test Leave Approval Process Result Type:- <None>(Keep the default value as it is) Runnable:- Checked. Note:- Check Runnable so that the process that this activity represents can be initiated as a top-level process and run independently.            If process activity represents a subprocess that should only be executed if it is called from a higher level process,then uncheck Runnable. 9) Till the point 8 we have discussed, how we can define each workflow components. Now we have bind them together to create a workflow process.     a) Double click on the newly created icon, it will open up a process window.Resize the window so that both the window(Navigator, process window) as shown in         the figure below.       b) First "Drag & Drop" START  & END Function activity to Process window. Once it is appear in process window, double click on the icon, it will open up the      Function activity window. Go to "Node" tab and select 'Start' for START activity in 'Start/End' field.(As shown in picture below.)      Do the same for END Activity.(Select 'End' in 'Start/End' field.) Note:-Start and End activity can be distinguished by the following identification mark          Start activities are marked with a small green arrow          End activities by a red arrow.     c) Now drag the notification from Navigator window to process window and double click on the notification icon. A notification properties details       window will open up.        As mentioned, we need to add performer in the notification.          Go to "Node" tab of the property window. Go to the Performer Section.       Type :- "Item Attribute" &       Value:-"Supervisor User Name".       d) Now we need to make transition between different activity. To create transition between source and destination hold down right mouse button and drag your           mouse from a source activity to destination activity.                         Note:- 1) If the source activity has no result code associated with it, then by default, no label appears on the transition.                       2) If the source activity has a result code associated with it, then a list of lookup values appears when you attempt to create a transition to the                          destination activity. Select a value to assign to the transition. You can also select the values <Default>, <Any>, or <Timeout> to define a                          transition to take if the activity returns a result that does not match the result of any other transition, if the activity returns any result, or if the                         activity times out, respectively. Saving Workflow Definition 1) Our workflow is ready to save it in database. Before saving it to database we must validate the definition. 2) Once the definition is validated, go to File >> Save. It will open up a pop-up window with the save option (database or Local). Select the database option and provide the database credentials to save. Our Custom Workflow is now ready to be triggered. The triggering mechanism will be discussed in next topic. References:-   1)  Oracle® Workflow Developer's Guide                               Release 12                               Part No. B31433-04

Requirement Mapping & Custom Workflow Design

b) Requirement Mapping & Custom Workflow Design

Now we are comfortable with ABC of workflow (refer our earlier post ABC of Workflow). As of now we know most of the workflow components and their uses. Lets design a simple custom workflow process and step-by-step we will make it more complex. Basic RequirementOur business requirement is when a person applies for a leave it should go his/her supervisor for approval and once it is approved save the information in database (say in custom table*). If the leave gets rejected, don’t store any information in database. * => Here we are storing the information in custom table instead of absence management table of oracle apps. Here our objective just to demonstrate how we can use function activity. Hence to keep the discussion simple and to avoid using oracle API,we are considering custom table. Pre-Requisite  Following are the pre-requisite to implement the solution 1) Web application which will call our custom procedure to trigger custom workflow process, is ready and accessible. 2) Oracle Workflow builder is installed and power user access is provided. 3) Access to development database is present. Design AppraochFollowing assumption holds true for our design approach and our discussion We are not considering the oracle provided seeded workflow processes (processes for oracle HRMS self-service functionality) and its extensibility and availability for customization. Whether we can achieve this through oracle provided workflow processes (processes for oracle HRMS self-service functionality) or not is out of scope of this discussion. Here our main goal is to make a discuss on how we can create a custom workflow process. To design a workflow processes we can consider any of the following approach a)      Top-Down Design approach. b)      Bottom-up approach. We will consider the Bottom-up approach. We will first create individual workflow components and then we will use those components and design our process.  Lets take a pen and paper and design the flow diagram. A:- User will log into the system and opens up the leave application form. Once he/she filled up with the relevant information he/she will submit the page. Once the page gets submitted, it should call our stored pl/sql procedure with the following information 1)      The requestor person id 2)      Leave Type 3)      Leave from date 4)    Leave to date Here the control is with the web page. B:-At this point we will take all the input values and will store it in the required item attributes.  So following item attributes needs to be defined 1)      Requestor person id 2)      Requestor User name 3)      Requestor full name 4)      Leave Type 5)      Leave from date 6)      Leave to date Here the control is with the pl/sql procedure.Workflow will be triggered at this level. C:- Since at this point we need to send notification to supervisor, hence the performer of  the notification is Supervisor.Thus we need to create a)  A notification b) A message c)  Item attribute to store supervisor full name d) Item attribute to store supervisor user name e)  Item attribute to store supervisor person id Here workflow is triggered,control is with workflow engine. D:- Since we have to store the information in a table hence we have to use a function that will call our procedure to store the information in database. E:- Supervisor can either Approve leave requisition or Reject the same. Hence the possible response could be a) Approve b) Reject. So, we have to create a lookup type containing two lookup code a) Approve b) Reject From the above discussion we can finalize our list of workflow components required for our workflow process Item Attribute 1)      Requestor person id            (Type-Number) 2)      Requestor User name         (Type-Text) 3)      Requestor full name           (Type-Text) 4)      Leave Type                         (Type-Text) 5)      Leave from date                 (Type-Date) 6)      Leave to date                      (Type-Date) 7)      Supervisor person id           (Type-Number) 8)      Supervisor User name        (Type-Text) 9)      Supervisor full name          (Type-Text) Notification     A single notification to send information to supervisor of requestor Message     A message needs to be created to pass the leave information to supervisor Lookup Type/Code A lookup type containing two lookup code a) Approve b) Reject  needs to be created. Function Activity Each process has to have a Start activity that identifies the beginning point of the process. An End activity should return a result that represents the completion result of the process. Start activities are marked with a small green arrow, and End activities by a red arrow.(As shown in picture below) Hence we have to create two function a) Start (should be marked as Start Activity)                                                        b) End   (should be marked as End Activity) When initiating a process, the Workflow engine begins at the Start activity with no IN transitions (no arrows pointing to the activity). If more than one Start activity qualifies, the engine runs each possible Start activity and transitions through the process until an End result is reached. Sample Design Sample Design diagram shows position of different nodes (A,B,C) its position and transfer of control. We understand from the below picture that  "Web Page Leave requisition details" activity and "Pl/Sql Procedure to trigger workflow" activity is outside the oracle workflow process.

ABC of Workflow

a) ABC of Workflow

Oracle Workflow is unique in providing a workflow solution for both internal processes and business process coordination between applications. It automates and streamlines business processes both within and beyond our  enterprise, supporting traditional applications based workflow as well as e-business integration workflow. This technology enables modeling, automation, and continuous improvement of business processes, routing information of any type according to user-defined business rules. Oracle Workflow can route supporting information to each decision maker in a business process, including people both inside and outside our enterprise. Oracle Workflow Builder is a graphical tool that help us  create, view, or modify a business process with simple drag and drop operations. Using the Workflow Builder,we can create and modify all workflow objects, including activities, item types, and messages etc. Workflow Components Depending on the Business process logic we may need to create/modify some or all of the of the following workflow process components. Item Type Item Type is a name/identifier of a workflow business process. Any component that we create for a workflow business process must be associated with a particular item type. when we save our workflow process definition is database it takes the item type as its name. Opening an item type from databse automatically retrieves all the attributes, messages, lookups, notifications, functions and processes associated with that item type. Example:- Here “Test Leave Workflow” is name of the Leave Approval business process . Item Attribute Item Attribute is nothing but global variable that can be accessed/ referenced by any activity within the itemtype. An item attribute often provides/stores information about an item that is necessary for the workflow process to complete. It also help us to share information with the stakeholders of a business process. There are different types of item attribute:- a)    Text:- The attribute value is a string of text Note:- Oracle Workflow does not permit HTML content to be passed in attributes of type text. If Oracle Workflow encounters HTML tags in a text attribute, escape characters will be applied to display the content as plain text rather than executing the HTML. b)    Number:- The attribute value is a number c)    Date     :- The attribute value is a date     d)    Lookup : -The attribute value is one of the lookup code values in a specified lookup type. e)    Form    :- The attribute value is an Oracle Applications internal form function. f)     URL    :-  The attribute value is a Universal Resource Locator (URL) to a network location. If we  reference a URL attribute in message attribute, the                         notification, when viewed from the Notification Details Web page or as an HTML-formatted e-mail, displays a link to the URL specified by the                       URL attribute. g)    Document:- Document attribute is mainly used to attach document in notification and help stakeholder to see the information. This attribute mainly                          used for external document integration purpose.The following document types can be used  :-                                                                            >>  PL/SQL document                                                             >>  PL/SQL CLOB Document                                                             >>  PL/SQL BLOB Document          --- Other types of attributes will be explained later. To keep our discussion simple at this stage, the other attribute types are skipped intentionally. Message & Notification Activity In our daily life, when we plan to send a letter to anybody, first we write a letter and then we put that inside an envelope, which contain the address of the recipient. In oracle workflow, Message can be compared with a letter and notification can be compared with envelope. In oracle workflow a message is attached with a notification. Recipient of the message is known as performer. Here performer needs to be added at notification level, so that workflow engine can understand to whom the notification needs to be delivered.In performer field we need to add  a value(may be dynamic by adding item attribute),so that workflow uniquely identify the  recipient from database.(Username of FND_USER table). Each message is associated with a particular item type. This allows the message to reference the item type's attributes for token replacement at runtime when the message is delivered. We can create a message with context-sensitive content (dynamic message body) by including message attribute tokens in the message subject and body that reference item type attributes. Note:- Unlike the real life envelope-letter analogy, here we can attach only a single message(letter) to a notification(envelope). Function Activity  Function Activity is defined by Pl/SQL stored procedure or other external procedure.Our discussion in oracle workflow will be restricted to Pl/SQL stored procedure only.Function activity helps us to embed our custom business logic applicable to workflow business process. Example:- We need to examine whether the person has applied for a leave for more than 10 days or less than equal to 10 days. Conclusion:-The Key features of Oracle Workflow are i)The Workflow Engine embedded in the Oracle Database implements process definitions at runtime ii) oracle uses the Business Event System which is an application service that uses the Oracle Advanced Queuing (AQ) infrastructure to communicate business      events between systems. iii)The Workflow Definitions Loader is a utility program tightly integrated with the workflow builder and oracle application. This helps us to download workflow    definition from database to flat file and upload it to database iv)Oracle Workflow lets us include our own PL/SQL procedures or external functions as activities in our workflows. Without modifying our application code, you can     have our own program run whenever the Workflow Engine detects that our program's prerequisites are satisfied. v)The Notification System sends notifications to and processes responses from users in a workflow. 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. vi)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 attachment that provides another means of responding to the notification. vii)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. References:-additional  1) https://metalink.oracle.com                                       2) Oracle Workflow Developer's Guide ( Release 12) B31433-04