13
May
2009
Siebel – SIA BC Utility Service – Loop multiple records
by Ankit BhardwajThis is a vanilla business service which provides an extensive functionality that can be used at the BC level for performing the tasks like querying and invoking a BC method. It is very useful as it provides many powerful features that can not be done through Siebel operation. One of the methods it supports is, “BCNextRecord”.
This method is used to query the records in the BC. Unlike Siebel Operation, this method provides the feature of searching the records sequentially in the queried records. It has the following arguments:
i) Business Component Name :- This argument requires the Business component name on which the query has to be made.
ii) Forward Only :- This argument specifies the mode of execution.
iii) From First :- This argument specifies that the query needs to be run from first record in the BC.
iv) Row Id :- This is an output argument that gives the row id of the record.
v) Search Specification :- This argument takes the search specification on the basis of which the query is executed.
vi) Total :- This argument is an Input/Output and gives the total records found in the query.
vii) Total Field :- This argument receives the fields of the BC in which the query is to be executed.
But there is a main disadvantage of using this BS is that if it does not find any record in the stated query, it raises an error.
Illustration of this BS by an example
To make the working clear of this Business Service, the following workflow is made that query a BC using this
SIA BC Utility Service Demo WF
In the above figure, there is a workflow “Vanilla BS Workflow” which is used to elaborate the use of this BS. In this example, the workflow is made to query on the “Order Entry – Orders” BC. The BS must be provided with certain input arguments and on the basis of which the query is executed on the mentioned BC and flow is further taken on the basis of the query results. The processing of this workflow can be given step by step as follows:
1. Query Records :- This step uses the “SIA BC Utility Service” which is called for executing a query on the BC. This service, in this WF, is used to query on the “Order Entry – Orders” BC. The arguments provided to the BS are:
Input Arguments:-
i.) Business Component Name :- This argument takes the literal value for the BC on which the query needs to be run. The value provided in this WF is “Order Entry – Orders”.
ii.) From First :- This argument is mapped with the process property and it executes the query from the first record in the BC. Here, it is mapped with the WF process property “ExecuteQuery” which has the default string set as ‘Y’. This WF process property is to be set as to control the functioning of the BS
iii.) Search Specification :- This argument takes the search specification by which the query will be run. In this WF, the search specification is given as “[Status] <> ‘Completed’ AND [Price List] IS NOT NULL AND [Agreement Name] IS NOT NULL”.
Output Arguments:-
i.) Row Id :- This output argument is mapped with the “Siebel Operation Object Id” WF process property that will store the Row Id of the record found in the Query.
ii.) Total :- This output argument is mapped with the “RecFound” WF process property that will store the total number of records found in the query.
iii.) ExecuteQuery:- This output argument has the literal value set to ‘N’ which was ‘Y’ in the input.
2. IsRecord? :- This is a decision point which checks the value of “Siebel Operation Object Id” and routes the flow accordingly. This decision point actually checks if the record found in the query. If the record exists, then it moves to the “Set Line Item Status” step otherwise the flow will end.
3. Process Records :- We are using a Siebel vanilla Subprocess step to process the data from different fields from each of the records one by one. It could be anything as per the business requirements, eg. sending outbound XMLs through B2B.
Input Arguments:-
i.) Header Id (example) :- This argument takes the value from the “Siebel Operation Object Id” and on the basis of this, the status will be set on the corresponding line item according to the code in the BS.
And, also many other field values from step (1) as per the business requirement.
Output Arguments:-
i.) Message :- This is an output argument that gives the error message that occurs in the BS. It is mapped with the “Error Message” process property of the WF.
4. Error Handling: Subprocess :- This is a subprocess which is called to catch an exception and to log the error in the “Error Logs” BC. The WF steps are connected to this subprocess. This subprocess have the following arguments:-
Input Arguments:-
i.) Error Message:- This argument takes the value from the “Error Message” process property of the WF and on the basis of this, the error will be logged in the custom BC named as “Error Logs” BC.
ii.) WF Name :- This argument takes the literal value of the WF name itself. The value passed in the property is “Vanilla BS Workflow”.
So, in this way, this BS will work. Keep using it!!!!!! 
Sounds useful. But in your example you’re not looping through multiple records. How would you do that?
Hi, the above example is infact a looping in multiple records. The method ‘Next Record’ is there to loop in the query records. This method takes the queried records one by one and thus makes a looping of the recordset returned. It then further goes via the IsRecord decision point and the WF processes further.
Hi Stuandgravy,
I guess you are right. Though the explanation seems to be fine, I guess the next step in the flow after ‘Set Line Item Status’ should be connected to the original ‘Query Records’ step for the loop to complete.
I will straight away get to the Author to correct this. I am surprised nobody who went through this post, could point this out before.
Thank you for your effort, Stuandgravy!
Hi Nitin,
Thanks for correcting me about this. The image is now showing the loop.
@ Ankit
Thank Stuandgravy, not me!
Thanks for the useful tutorial!
Unfortunately I have the following problem: when running the Business Service as part of a workflow. When I run it in the simulator, the ‘Total’ output variable does not get set.
Also, even when there should be 2 records found, the loop runs only once… Do you have any idea why?
Thanks again,
Rick
Hi Rick,
I used this in production in one of my implementations, and i can confidently say this works!
Also, as for the loop looping only once for 2 records as you say, I suggest you revisit your workflow in the way you are looping. Cross check if you are missing out on a particular condition set. Try with more than 2 records, say about 5, and let me know what you find.
This one definitely works. Yes, there is a catch which Ankit has not discussed. I will wait for someone to point it out here before I actually answer that part!
I have been working on another vanilla way to loop records in Siebel. Guess it’s time now to release it to everybody. There are easier ways to loop records for processing than using an unknown BS!
Ok, we’re getting curious now…what is it;-)
Keep watching this space, dude!
The only reason is that I have been really really caught up with lots of other stuff that I have been guilty of neglecting this blog. I really hope I get some time soon.
I really like to see how this community is shaping up with lots of people coming forward and contributing. This is not just a one person blog, but a community of Siebel Professionals. Really happy to see the progress in just 2 months!
I have found the following solution. It is impossible to loop through the records of a primary BC using the NextRecord method of a Siebel Operation. This was solved by copying the primary BC and putting it in the same BO, without a link.
Cheers, Rick. You have just hit the nail on the head!
However, the one you are referring to, is the NextRecord method in a Siebel Operation. This is possible only in Siebel 8.0, and not supported in previous versions of Siebel. This one comes LAST in my list of possible scriptless solutions.
I rate the solution above by my friend Ankit the BEST I have seen so far. I will shortly present another solution based on more conventional Siebel methodology rather than using something like”SIA BC Utility Service” with limited documentation on the web. Note that there is nothing wrong with the solution above, and I personally rate it the best I have ever come across!
I agree that the solution isn’t perfect: it requires you to sync the copied BC and the original BC which could mean a lot of extra work. Yet I was forced to use this, because the method provided above here didn’t work for me (Oracle is working on it, so perhaps we know more soon).
I also saw that in the vanilla Siebel workflows that use the same method as described above, the Query Records step was preceded by a normal Siebel operation step, excuting the same query.
I still think it’s quite sad that there is no easier way to loop through records of the primary BC. The method above is a nice option, but sadly it isn’t documented at all.
Thanks
Rick
I believe the documentation above is sufficient. I have used this in Production in one of my implementations, and so I know this works!
In any case, wait for my post tomorrow morning, sharp 8 AM IST. I will present my alternative solution.
One more thing I would like to know here…
Does this business service works on active BC instance or inactive BC instance.
If it works on Inactive BC then it will result in call to two business services and
need to create two BC instances (one by vanilla BS and the other inside Custom BS)
where as if we go for scripting we just need to call one business service and just one BC – BO instance
Hi Neel,
Too many questions. Let me try to handle them one by one!
“.. Does this business service works on active BC instance or inactive BC instance ..”
Nothing like an active or inactive BC Instance here. If you notice carefully, Ankit has mentioned about Business Component Name as an input to the BS. The BC you are querying must be there in the BO defined for the workflow, though.
“.. If it works on Inactive BC then it will result in call to two business services and ..”
I don’t really comprehend why I would need two different BS here.
Yes, the BS takes forward the context. So, the Primary BC and Links within the BO are important, just like the vanilla Siebel Operation step.
In the example above, we used only one BS, only once. And it worked perfectly.
“.. need to create two BC instances (one by vanilla BS and the other inside Custom BS) ..”
we don’t have a custom BS here. No scripting! Why do I need a custom BS at all?
“.. where as if we go for scripting we just need to call one business service and just one BC – BO instance ..”
I am deadset against scripting, my personal opinion. Not that I am commenting on whether which one’s better. This is an alternative to scripting for querying multiple records and processing them one by one.
The SIA BC Utility Service does not really have an overhead that I have noticed. Even if it did, I would still prefer this over scripting, for 200 different reasons. My personal choice!
My gut feeling tells me, that this one is, and is further going to be the most popular Siebel post on this blog!!
LOLZ!!
I just came across some particular references on the Internet, where the method outlined above has come under criticism. The author says, why do I need to use the Vanilla Business Service above if all I need to do is Set the Line Item Status?
Nice question, which actually puts the comment by Neel in pretty good perspective. I will explain.
Yes, it is a very baad (note the double ‘a’!) example that the author has chosen to actually explain the concept above! If we are in fact updating the Line Item status based on the Row Id, the entire concept loses its value, as you are in fact looping through the recordset retrieved from the DB, and also, updating it one by one in a separate step! I agree. In fact, the post, Updating Multiple Line Items in One Go on GeeksBlogging@dotCOM is much more relevant as we can just use a Single vanilla Siebel Operation step itself to update all the records at once. Still, why do I need scripts, Neel??
I can’t help smiling.
I guess a better example that the author could have taken, is to replace the Set Line Item Status step with a Subprocess, to process the record by record fields. That would essentially mean, you are not using scripts (I am deadset against them!) to query multiple records in Siebel, and actually using Vanilla Siebel methods to do the entire query and processing. I still have to see better solutions anywhere, or from anybody to implementing this requirement. In any case, it does NOT bring down the value of the concept explained above. Business Requirements can always vary.
Requirement statement: Query multiple records in Siebel, and process fields data one by one, possibly in outbound EAI Service calls. Tell me if you can do it without using scripts, and without using SIA BC Utility Service!
Cheers
Hi, the explanation has been enhanced to reflect a better business Use-case. Thanks for the fantastic observation though..
Final clarification:
1. I hope now it is clear what custom business service I was talking about in my first comment. I tried to put an explaination in comment but it just got too looong…
2. This is not a critisism, just an observation.
3. I don’t think example is baad, it is good… but I just thought it could be better
4. I never asked “Why you need script”, I understand that there are some validations that need to be done so you need a custom business service. It is okay!!
5. I would still do everything in custom BS for this particular requirement, for 200 other reasons… my personal choice!!
Yeah, I agree. It boils down to personal choices, scripts or vanilla. Maybe we should do some analysis on vanilla versus custom scripts some time.
Nice to see that we all concur on the BEST solution..
Hi When discussed this with oracle guys i came to know that this is not documented and one should avoid it’s use. There are some scenarios in the past where pointer never moved to next record resulting in infinite loop.
So for now one should refrain using this.
Hi Chandradeep,
You are right when you say that this BS is not documented. Thus, it’s not supported by Oracle either. But, there are so many similar undocumented methods we use in Siebel for customization, no?
As for your experience of the pointer not moving, well, we have run this in production. It’s processed millions of records till now. We NEVER faced an issue because of this BS.
In any case, we don’t recommend it. It’s a question of personal choice. Script it, or follow the untreaded, undocumented path!!
Cheers
True. But would also like to point out that this method don’t even throw an exception when no record found.
(Solution to this is we use echo step and explicitly set a custom error message).
Also count records Does not work properly.
Hi Chandra,
Nice to come across somebody who has done as much research with this BS as me!
As per my research, this is true only till an extent. This BS DOES throw an error in case of no records found. I found this different than my expectation when I began using it, as it broke the loop and went into the Error Branch. But, I guess that is a subject of a separate post. You are right when you say that we need to use Echo or some other similar method to update an appropriate error message in this case.
Do let us know of the issues in CountRecords. It’s a long time since I worked on this BS, and maybe you would like to add something to what we have discussed here. Also, let us know of the Siebel version you are trying this on.
In any case, you can also try the following alternative method of looping multiple records without using Scripts in Siebel:
http://geeksbloggingat.com/2009/07/09/siebel-eai-siebel-adapter-looping-multiple-records/
We appreciate your contribution so far, Chandra. Thanks!
Thanks Nitin for guiding me.
I am using this BS on siebel 7.8. I want to say about count records that it was not giving any output. We used one billing account number and tried to count assets present.We tried first with one ambiguous value then also there was no exception. Then we manually created one billing account having no assets which was in database still it showed same behaviour.
Well, I haven’t tried this out myself, but, if you say, could be an issue. I guess it’s becoming too long in the comments here. What I suggest is you write out a small article on your findings and carry it forward from where Ankit has left off.
I can ensure it’s published here in your name. Once again, thanks for sharing. Cheers!
Just want to say what a great blog you got here!
I’ve been around for quite a lot of time, but finally decided to show my appreciation of your work!
Thumbs up, and keep it going!
Cheers
Christian
Hi Guys
Really a nice post. from my end,tried to replicate the case.. However i donot see that “Siebel Operation Object Id” is getting populated any time. With what value would it get populated ideally. Is it the row Id of Primary BC of a BO on which the WF would be acting. In my case i have the BO defined for the WF and Primary BC is the one which i am using via this BS,still unable to see the value in “Siebel Operation Object Id”.
Pl guide me,what could be the case…
unlike the example taken where in we are getting the line item records using the order Header Id relation… i am trying to use it over a BC,by firing a query on Order Status and traversing through it to get the row Id’s of each of the record having the order status as “New”. as i need to get the rowId’s only for status as New.
Will this BS work for such scenarios? As i donot see it to be the case or i am doing something incorrectly.. Pl let me know
I’m using this BS in Siebel 7.8, but getting same records (row id output argument) again and again.
Suspected the reason as the literal value ‘Y’ passed to the input method argument “From First”. But on giving a value either by literal value or by process property(conditionally), i’m getting ‘null’ in row id output argument.
Need your valuable help/advice..
Thanks