In the fifth part of this series we look at the latest addition to the Connector Framework API, the ExecuteQuery operation. As such, it does not have any seed data or metadata in the Repository, but assuming you imported the most up-to-date WSDL back in part two of this series, you should at least have the Integration Objects. Don’t forget to deploy them to the runtime database and stop/start the EAI Object Manager before you go any further.

So what is ExecuteQuery actually for? As it’s name suggests, it allows us to do a query for “supplementary data” at any point in an Interview. The idea being that rather than load too much information at the Load operation, we should focus on loading just what we need and then, accordingly, use ExecuteQuery for the rest.

So far so good. But it is not as fun as it sounds. There are three main stumbling blocks to implementing this Workflow Process and associated Web Service operation:

1. The format of the conditions applied to the Query
2. The lack of example Workflow Process
3. Difficulty in designing a generic solution in the same vein as the other examples from the White Paper

This article aims to explain clearly what ExecuteQuery is, with an example, and how to implement it in Oracle Policy Modeling and in a Siebel Workflow Process. So we will have completed the first two steps. The third one, I have to admit, was on the radar at one point but in the end the workload required was too large and I just left it to one side. Maybe when the need arises, I will come back to it one day.

Setting up ExecuteQuery as a Workflow Process

Obviously there is a need for a Workflow Process. And the good news is that the standard Load Workflow Process (like the one in part three of this series) will do just fine for a starting point. Make sure you clone the Workflow Process and that you strip it down to the minimum steps. Here are some discussion / implementation points for ExecuteQuery.

In the flow above, note the basic steps (extract properties, make a query, then convert it into something Intelligent Advisor can understand) are very similar to the Load. The Write Request and Write to File steps, as always help to understand the basic format of ExecuteQuery.

Now let’s look at a couple of key items that relate to one of the stumbling blocks:

In the image above, the Query step is highlighted. An example query is shown, using a fixed field name and a variable for the value. That variable obviously is extracted in the previous step, and was inserted by a rule designer at design-time in Oracle Policy Modeling.

In the screen capture above, note the unfriendly structure of the request – the criteria we need is stuck in a new branch specific to ExecuteQuery. So let’s take a higher level look at the sort of thing that is coming in / going out of ExecuteQuery:

In the SOAP UI project above, notice how the <comparison-expr> tag is used to encapsulate the field, the operator and the value. Keen-eyed observers will note that the format is all text, so you can imagine that “NotEquals”, “LessThan” and lots more will show up as your rule designers build more sophisticated rule projects. And you will have to parse them and turn them into Siebel Query Language. And that is why I stated earlier that there as a stumbling-block to creating a generic Workflow Process. It would require a Business Service capable of parsing not just the usual things like TableName and fields, but also basically turning the comparison and other tags in that first section into Siebel syntax. Not a small job.

Perhaps it is not surprising then that most clients seem to content themselves with the approach I am showing above, basically giving a limited degree of customization (“pick any SR Type, that will work”) rather than building out a completely generic approach.

One final item in the process is the XSLT transformation, for which you can take the LoadResponse.XSL file and just edit the main tag to execute-query-response. You’ll need to make sure that is referenced in the Transformation step near the end.

Now that we have our XSL and Workflow Process, the Web Service operations can be updated to include ExecuteQuery. And from the Oracle Policy Modeling tool, we can begin to see the changes :

The rule designer, as well as adding a standard entity (like the Service Request linked to the Account you can see at the top of the hierarchy), can add an “unrelated mapping”. And this is the primary purpose of the ExecuteQuery – to go get data that is not linked to the original load.

In the screenshot below I have added Service Request as an unrelated mapping. I called the normal one “the linked sr” and the other “the service request” to distinguish but that’s just me.

In the “unrelated” entity I can now perform the key step of creating a filter criteria – and in our case, since our little Workflow Process only supports the SR Type, so it shall be the source of our filter. Note how the syntax is not standard Intelligent Advisor natural language, rather a sort of limited Query Language.

Also note that the record limit is something the Siebel developer will have to implement as well.

So having deployed the Project and launched it with the id=ROW_ID parameter, let’s look at a couple of Screens :

Above is the standard Account and SR linked together, and this project is set up to allow for the creation of a new Service Request. Nothing has changed here. But the second Screen displays the unrelated entity.

Here we see all the service requests that are in the database that correspond to the query (SR Type = “Installation” ) and there only the ability to read this data – there is no Save for ExecuteQuery, you are only loading data for some calculation or logical purpose, not to provide an editing capability.

The data, for the record, came from my database like this (I only have 2 Service Requests in my database) :

And this demonstrates the ability of the unrelated entity to go and get whatever you need, irrespective of the initial Load context.

Thus we conclude part five of our Siebel 21 and Intelligent Advisor series. Watch out for part six, soon!

• Intelligent Advisor and Siebel 21 #4 (Checkpoints)
• Intelligent Advisor and Siebel 21 #3 (Save)
• Intelligent Advisor and Siebel 21 #2 (Load and Getmetadata, CheckAlive)
• Intelligent Advisor and Siebel 21 #1 (First Steps)