Emitting Events from 11i Order Management Process
By vebhhav.singh on Jan 17, 2009
To process an Order in 11i, Order Management uses internal Workflow technology. Folks who are not aware of Workflow technology…its on the same lines as a BPEL technology in Oracle’s Fusion Middleware stack. Here is a snapshot who have never seen an Oracle Workflow.
As you can see it contains a start activity and an end activity and bunch of other activities in between.
11i Order Management primarily uses two workflow processes two process a Sales Order. One for processing an header and the other one for processing lines. Depending on our requirement we will configure these workflow processes to send out an event at appropriate steps within the processing. To configure an event there is a standard activity, Event activity, which we will embed and populate it with relevant data.
Let’s look at a Header processing workflow. How do I get to it. Well in 11i most of the functionality is inside the database. Workflow technology is also implemented as a PL/SQL engine inside the database. These processes are executed at runtime by this engine. The picture that you saw above is a design time tool and can be run from your desktop. All the relevant files are stored as “wft” file extensions. As you can imagine now …these are loaded into database for execution.
So let’s pull the header workflow by connecting to the 11i database.
There are bunch of processes listed here. For header processing we have “R_STANDARD_HEADER”. This is what is shipped out of the box from 11i. Of course customers have the option of creating their own and customize it to their needs.
As you can see in the above workflow there is a standard “Start”, called as “Enter”, and an “End” activity. Besides this we have “Book Order, Manual” and “Close Order” activities. Let’s focus on “Book” and “Close” activity. Picture of these activities indicates that they invoke sub processes.
Booking Order Process
A Sales Order can be entered through the Sales Order form or it can be received from external systems through various order channels like web sites, EDI or XML. When they enter the Order management system they are in a status called as “Entered”. Next step in the Order processing cycle is to book the order. Normally you enter into Order Management’s Order Form and click on the button “Book”. Above mentioned workflow get’s triggered as a result of that.
Configuring Oracle Workflow
Let’s say we want to send the Order status to external system’s when the order is Booked. So let’s see if there is any event available out of the box. To do this we can browse through 11i Event’s screen by selecting responsibility “Workflow Administrator Web Applications” and clicking on “Administrator Workflow'” ---> “Business Events”. Give the search criterion as “oracle.apps.ont”. Here “ont” is the internal code for Order management module and “oracle.apps” is a standard used for all events shipped out of the box by 11i.
Alternatively you can browse the same through E-Business Suite adapter from Oracle BPEL or Oracle ESB.
Both of the approaches gives the same view.
Looking at the what is being shipped out its very clear that there is no event which get’s triggered when Order is booked….OK…hum… what needs to be done in order to inform external systems, UI’s etc when Order Booking happens. Here are the steps…
- Define a custom business event
- Insert a new “Events” activity in the Workflow
- Configure the activity
- Optionally….insert another custom activity to populate additional data
Let’s go through each of the above in detail.
Define a custom Business Event
Log into Oracle apps as “sysadmin/sysadmin”. Select the responsibility “Workflow Administrator Web Applications” and clicking on “Administrator Workflow'” ---> “Business Events”.
You can see “Create Event” button click on it to define the event and input the details as in the screen
Insert a new “Events” activity in the Workflow
In the 11i workflow builder look at the toolbar. You will notice there is an event activity. Drag and drop that in the Workflow panel
Your workflow so far looks like this
Configure the activity
Click on “Event Details” tab. Make sure you put the Event Name value as “oracle.apps.fnd.order_booked”. Another required value to raise the event is “Event Key”. This has to be a unique value. In our case we can map this to Order Header ID which is always unique.
Optionally you can define the variables that you want to pass as a part of the event. Map these variables through the “Node Attributes” tab
In the following diagram I have defined variables (Activity Attributes) starting with name as “X”. For example in this scenario when a Order Booked event is raised we want to know following
Order Number, Customer PO Number, Organization ID and Order Header ID
And map these variables from the “Node Attributes” tab.
Well in this scenario most of the data that we want is not available as pre-defined variables….but don’t worry its quite easy to get exactly what data you want to populate. So what we will do is define additional variables for the workflow and populate them through a custom PL/SQL program and then map them through the Event we created.
Custom activity to populate additional data
Check additional data you need …is that already available in the Workflow variables. If yes… good…otherwise create the required variables. In following diagram I have created additional variables starting with name as “XX_”
We have defined new variables for the workflow…now let’s populate them through the custom PL/SQL activity. Go back to our wonderful 11i Workflow builder and click on Function activity….drag it and drop it on the panel…
Function activity will prompt for input parameters. Most important one is the name of PL/SQL program that gets called from here….In this case it’s “XX_GET_ORD_DETAILS”
Your workflow now looks like following
To summarize what we did.
Created a custom event called as “XX_ORDERED_BOOKED_EVENT”. We created some additional attributes to pass some custom data…this is optional….Created a custom PL/SQL function activity “XX_GET_ORERE_DETAILS” to populate the additional variables in the custom event.
Wow…now we can not only take advantage of business events shipped by E-Business Suite but more importantly write our own to get appropriate information at the appropriate step in the workflow….Are there other ways to achieve the same task…..sure create a table….populate it through PL/SQL and keep on polling the table….hum…you are firing the SQL’s unnecessarily….and that too…every couple of minutes….
Business Event System shipped out of 11i is the most efficient way to get information about what’s happening with my processes in 11i. It’s a push approach vs a traditional pull approach. Another most important part to note here is that the information is real-time….As and when booking of order happens in 11i….external systems get’s notified…and on top of it…..only way to get information which is the context of business process…..after all we always want to provide information in the context of the way business runs..
That brings us to the fundamental point….how to put all this together so that it can makes sense to business people…well..more on this in my next blog….