How to update Child BC records on update of Parent BC record?

Siebel provides various ways of achieving the same solution, but you need to do the analysis beforehand to pick the right one. Recently I got one basic requirement that:

As soon as the Opportunity Sales Stage gets "Approved", all the Activities (Child of Opportunity) status should automatically set to "Approved".
Any Siebel Geek can offer multiple ways to achieve this solution:
1. Write a small script on "WriteRecord" event of the Opportunity business component.

2. People against scripting will not like Point#1 above and they might suggest to create a Workflow process with an Update operation on Action business component. And call this Workflow process in WriteRecord event of Opportunity or by Runtime event or by Workflow Policy.

3. Trigger a Workflow Policy when Opportunity Sales Stage = "Approved" which will call a WF Policy action with a program of type DB Operation.

I found another way of achieving the same solution (scriptless) and want to share with you. Using Data Validation Manager (DVM), you can achieve this as well. If you know how to update a field (SetFieldValue) using DVM, you might know what I am talking about.

Solution Details: 1. Navigate to Administration - Data Validation -> Rule Sets view.
2. Create a new record with the following data:

Name : Set Activity Approved
Business Component : Opportunity
Business Object : Opportunity

3. Drilldown on Rule Set Name.
4. Create new record under Rules view.

Sequence# : 1
Name : Set Status to Approve
Expression : [Status]="Approved"
Business Component : Action
Apply To : All Records
Return Code : Select any return code from the pick applet

5. Navigate to "Action" sub-view under the "Rules" view.
6. Create new record in Action applet:

Sequence# : 1
Type : Business Component
Business Component : Action

7. Create new record in "Field Values" applet

Field : Status
Value : Approved

8. Navigate to Administration - Data Validation -> Rule Sets view again and Activate the Rule set you just created.
9. Status changes to "Active".
10. Navigate to Administration - Runtime Events -> Action Sets view.
11. Create a new record:

Name : Activity Approved
Active : True

12. Create new action record:

Name : Call DVM
Action Type : BusService
Sequence : 1
Active : True
Business Service Name : Data Validation Manager
Business Service Method : Validate
Business Service Contect : "Rule Set Name", "Set Activity Approved", "Enable Log", "Y"

13. Navigate to Administration - Runtime Events -> Events view.
14. Create new record:

Sequence : -1
Object Type : BusComp
Object Name : Opportunity
Event : WriteRecord
Conditional Expression : [Sales Stage]="Approved"
Action Set Name : Activity Approved

15. Reload the runtime events.
You are done.

Now this is the time to test the solution:
1. Navigate to Opportunity -> Activities View.
2. Change the Sales Stage to "Approved".
3. See the change in activities status.

Child Field Read Only depending on Parent Field Value

Sometimes it happens that you get a very simple requirement to implement and in a single glance you say, "Well, this is very easy to implement" and when you actually see the result on the UI after the configuration you have done, you start scratching your head to find out the reason for not getting the result as per the expectation.

Today, I am going to discuss a very simple requirement you might have faced earlier.

Requirement
I have two applet exposed on the UI: 1) Opportunity Form Applet 2) Quote List applet.
Opportunity being the parent applet and quote as the child as per below screen shot.


The simple requirement is to make "Comments" field on Quote List Applet editable, if and only if Sales Stage of Opportunity = Data Entry.

very simple isn't it! Anyone can easily say, go and use "Field Read Only Field" user property at Quote business component and you are done. I reacted to it in the similar manner and followed the below steps:
1. Pull "Sales Stage" value on Quote BC.
2. Create a calc field to set to Y, if Sales Stage = "Data Entry"

3. Create a BC User property, Field Read Only Field based on calc field.



Compile the SRF.

Navigate to Opportuity -> Quote view to verify the results. I created an Opportunity record, set the Sales Stage to "Data Entry" and then created a Quote record. "Comments" field was editable. Then I changed the Sales Stage to "Submitted" and per configuration I was expecting the "Comments" field to be read only, but to my surprise, it was not. Still, I was able to edit the field.


It might happen that change of "Sales Stage" at the Opportunity level is not getting reflected at the quote level. Let me try running a blank query (Alt+Q, then enter) to refresh and now..... yes, "Comments" field get read-only. So, basically the problem is, Quote BC is not aware of the change in Sales Stage at Opportunity level, unless you refresh it.

(Note: this is not the case with "Parent Read Only Field" user property. Change at parent field immediately gets reflected at the child level. You can refer this post for more details.)

Now, the problem here is to get the Quote list applet refreshed, if some change happens at Opportunity. One might point out that you should have "Immediate Post Changes" as True for "Sales Stage". But, keep in mind that "Immediate Post Changes" will only refresh the fields of the same business component, not of the child BC.

One solution, I can think of is to refresh the Opportunity business component in such a way that it should not loose the record context and consequently, Quote BC will automatically gets refreshed. But, I didn't want to do the scripting on Opportunity WriteRecord event, just to refresh the Quote BC, something like:

function BusComp_WriteRecord ()
{

TheApplication().GetService("FINS Teller UI Navigation").InvokeMethod("RefreshCurrentApplet", TheApplication().NewPropertySet(), TheApplication().NewPropertySet());

}

So, I found a better way to achieve to refresh the Opportunity form applet. Just create the following user property on the Opportunity applet:


Compile the SRF and check the result on the UI. Voila, everything is working as desired now.


.

How to send email containing multiple child records data?

I am pretty sure you will find this post very interesting, if you ever get a requirement to send a email in HTML format and email should have the details of the child records(dynamic data) as well.

XSLT (XSL Transformation) can do wonders for this kind of requirement where the basic idea is to get data in XML hierarchy from Siebel (which you can easily do via EAI Siebel Adapter and convert it into XML) and apply the XSL transformations to get the HTML output. And as far as you know how to play with HTML and XSL, you can create any kind of email format that you like to see. All you need is to use EAI XSLT Service business service with method Transform.

Below picture might give you some idea of it:

To understand the concept, let suppose:
1. As soon as an Opportunity gets submitted, you are required to send an email to the Sales person to inform him.
2. Email should have the details of child records created under that Opportunity.
3. Email should have some kind of formatting so that it looks good and eye-catching.

You might be thinking what is a big deal in it and why should I even use the XSLT (new term for lot many people) for it? There is a simple reason behind it, there is NO OOB way available in Siebel (as far as I know) by which you can include the data from Child records (multiple) in the email.

Let me first show you the proof-of-concept and later we will discuss about the technical solution.
1. I have below opportunity created with 2 records of Opportunity Products.

2. It has also 3 Quote records created under it.

3. As soon as Opportunity get submitted, system is required to send the email to the sales person.
4. Here below is the email, I would like to receive:


If you find that interesting, keep reading for the technical details of it.

Pre-requisite
Please note that for this demo, I have used a Integration Object (Opportunity) based on Opportunity BO with having only 3 ICs configured: Opportunity, Opportunity Product and Quote. And include only those fields whose values are required in the final email, for eg: Oppty Name, Product Name, Net Price, Quote Name etc. (Please refer the first picture of this post, XML part)

Technical Solution
1. Call a Workflow (based on Opportunity BO) as soon as Opportunity gets submitted.
(Note: System will automatically pass the row_id of Opportunity to "Object Id" process property of the Workflow Process)
Here below is the WF snapshot.

2. Process Properties

3. Step Details:


Code for XSLTBuffer
<?xml version="1.0" encoding="UTF-16"?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"><html><body><h2>You have got an Opportunity to act on:</h2><h4>Opportunity Name : <span style="color:#ff0000"><xsl:value-of select="ListOfOpportunity/Opportunity/Name"/></span></h4><h3>Here below are the Product Details:</h3><table border="1"><tr bgcolor="#9acd32"><th>Product</th><th>Quantity</th><th>Net Price</th><th>Revenue</th></tr><xsl:for-each select="ListOfOpportunity/Opportunity/ListOfOpportunityProduct/OpportunityProduct"><tr><td><xsl:value-of select="Product"/></td><td><xsl:value-of select="ProductQuantity"/></td><td><xsl:value-of select="ProductPrice"/></td><td><xsl:value-of select="Revenue"/></td></tr></xsl:for-each></table><h3>Here below are the Quote Details:</h3><table border="1"><tr bgcolor="#9acd32"><th>Quote Name</th><th>Revision</th><th>Status</th> </tr><xsl:for-each select="ListOfOpportunity/Opportunity/ListOfQuote2/Quote2"><tr><td><xsl:value-of select="Name2"/></td><td><xsl:value-of select="Revision"/></td><td><xsl:value-of select="Status"/></td></tr></xsl:for-each></table></body></html></xsl:template></xsl:stylesheet>

If you want to learn about XSLT more, then please refer the below link:
http://www.w3schools.com/xsl/

How to make all Child BCs read-only when Parent BC becomes read-only?

Today I am going to discuss one interesting requirement where you are required to make all the Child Business components read only as soon as the Parent business component becomes read-only.

Actual scenario goes like this:
As per the business requirement, we need to have the Opportunity business component read-only when Sales Stage is Approved. For this simple requirement we configure the BC User Property: "BC Read Only Field" based on a calculated field "OpptyReadOnlyCalc" as defined below:

User Property
Name = BC Read Only Field
Value = OpptyReadOnlyCalc

Field Details
Name = OpptyReadOnlyCalc
Calculated = True
Calculated Value = IIf([Sales Stage] = "Approved", "Y", "N")

This was working pretty fine, but complexity get added to it when we are required to make all Child business components read-only as well. Though the Opportunity record was coming read-only on the UI, but it was allowing us to create the child record as per below screen-shot:

You can see in the above screen shot (marked in Red), all the vanilla buttons i.e. Add, New, Delete are enabled. Requirement was to disable these buttons and user not allowed to do any operation on the child BCs as well as soon as Opportunity become read-only.

Solution:
To achieve this requirement, you are required to create two more BC user properties on Opportunity Business component.

User Properties

Name = Aspect Child BC ReadOnly: ReadOnly
Value = OpptyReadOnlyCalc

Name = Default Aspect
Value = ReadOnly

Thats it, compile the SRF and Voilà, now observe the difference on the UI.

If you want to learn about Aspect User Properties, follow the below link:
http://download.oracle.com/docs/cd/B40099_02/books/ToolsDevRef/ToolsDevRef_UserProps43.html
.

Use GetAssocBusComp () with caution!!

Sometimes we write script and never realize it might result in error due to record set on which it is getting operated and it becomes issue in live environment. Similar kind of scenario I observed while using "GetAssocBusComp()". Siebel geeks who have used this method before must be aware that this is being used to get the Association business component and it's "Associate" method is being used to add a record in the MVG.

So, I got a business service being called inside a workflow asynchronously where below small piece of code was failing for some set of records while it was working fine for other records:

var SRId = Inputs.GetProperty("SRId");
var SRBOBusObject = TheApplication().GetBusObject("Service Request");
var SRBC:BusComp = boinactive.GetBusComp("Service Request");
var SerialMVGBC:BusComp;
var SerialAssocBC:BusComp;
with(SRBC)
{

ClearToQuery();
SetViewMode(AllView);
SetSearchSpec("Id", SRId);
ExecuteQuery();
if(FirstRecord())
{

SerialMVGBC = SRBC.GetMVGBusComp("Serial Number");
SerialAssocBC = SerialMBVGBC.GetAssocBusComp();
// While debugging I found that code was failing
// for some records at above step
................
................

}

}

When I debug the issue and checked for the logs, the error I saw was:

No association list is available in this applet.(SBL-DAT-00276)

Strange part was, for some of the records this code was working fine and failing for some records.

While debugging I realize that the record on which this code was failing, was Read-Only. This is the due to the fact that when Service Request status change to "Closed" it gets read-only and the error was quite confusing in the way when it said "No association list is available".

Might be helpful for you in future if you face this error, please check record should not be read-only for any reason, not even due to "BC Read Only Field" user property on the business component.

Note: this is the reason you don't see the "Associate Applet" inside the MVG Shuttle applet configured on any MVF on the UI, if the record is Read-only.

Workaround

1. If you are operating on Service Request business component, then you can make use of "Always Enabled: <_MVL Dest BusCompName>" user property and then GetAssocBusComp() method would work even on read-only records. Drawback of this workaround would be, on the UI user can open the MVG applet and can associate a record in it even if the Service Request record has status = Closed.

2. Another better way is to operate on business component based on the inter-table. And just do a NewRecord().

.

How to send Email in HTML Format?...... contd

In continuation of the previous post, I found another good way to achieve the requirement of sending email in HTML format and moreover you are required to replace the field values dynamically from business component. So the extra information you need to provide to Outbound Communication Manager is the ROW_ID of the record in context.

Suppose, here below is the Email Template need to send in HTML format:

Here below is the code to achieve it:

var inp = TheApplication().NewPropertySet();
var out = TheApplication().NewPropertySet();
var svc = TheApplication().GetService("Outbound Communications Manager");
inp.SetProperty("CommProfileOverride", "SiebelMantra Profile");
inp.SetProperty("SourceBusObj", "Service Request");
inp.SetProperty("RecipientBusComp", "Service Request");
inp.SetProperty("SourceIdList", "1-B9PGUA");
inp.SetProperty("TestAddress", "siebelmantra@gmail.com");
inp.SetProperty("PackageNameList", "Test");
svc.InvokeMethod("CreateRequest", inp, out);

Here below is the email with necessary SR Number and status:

Siebel makes life "dynamically" colorful, isn't it ;)

How to send Email in HTML Format?

If you get a requirement to send an email from Siebel, the very simple way is to make use of OOB business service, i.e "Outbound Communications Manager" with the method "SendMessage". Following input parameters would be enough for this purpose:

a) MsgToList
b) MsgSubject
c) MsgBody
d) CommProfile

Here below is the working example for the same:

var inp = TheApplication().NewPropertySet();
var out = TheApplication().NewPropertySet();
var svc = TheApplication().GetService("Outbound Communications Manager");
inp.SetProperty("CommProfile", "SiebelMantra Profile");
inp.SetProperty("MsgToList", siebelmantra@gmail.com);
inp.SetProperty("MsgSubject", "Test Email");
inp.SetProperty("MsgBody", "This is a test email");
svc.InvokeMethod("SendMessage", inp, out);

Here below is the email I received:

So, everything is working as desired, but what if you are required to send a HTML email which contains text with different colors and formatting? Well, one can quickly answer that you can use "MsgHTMLBody" argument in the above code instead of using "MsgBody". Yes, that is correct but the only it got is that you need to pass the complete HTML code as the value to this argument, but wait a minute why to take all this pain when Siebel provides a mechanism to keep the email template in HTML format for you and just need to create it from the UI. Yes, you guessed it right, I am talking about Administration - Communications -> All Templates view.

Just create a new Email Template with :
a) Status = Active
b) HTML Template = True

Now, here below is the small code to make this work:

var inp = TheApplication().NewPropertySet();
var out = TheApplication().NewPropertySet();
var svc = TheApplication().GetService("Inbound E-mail Database Operations");
inp.SetProperty("BusObj", "Comm Package");
inp.SetProperty("BusComp", "Comm Package");
inp.SetProperty("Name", "Test");
inp.SetProperty("QueryFields", "Name");
inp.SetProperty("ValueFields", "Template Text");
svc.InvokeMethod("FindRecord", inp, out);

var inp1 = TheApplication().NewPropertySet();
var out1 = TheApplication().NewPropertySet();
var svc1 = TheApplication().GetService("Outbound Communications Manager");
inp1.SetProperty("CommProfile", "SiebelMantra Profile");
inp1.SetProperty("MsgToList", siebelmantra@gmail.com);
inp1.SetProperty("MsgSubject", "Test Email");
inp1.SetProperty("MsgHTMLBody", out.GetProperty("Template Text"));
svc1.InvokeMethod("SendMessage", inp1, out1);

and when I executed the above code, here below is the email in HTML format I received in my Inbox:

Siebel makes life so colorful, isn't it ;)

Query performance issue : Dedicated Client Vs Thin Client

Very strange behaviour I observed today while working on a performance issue where whenever user do a query in Purchase Order Pick applet, query was taking more than 1 minute to execute in thin client but the same query behaves perfectly fine in dedicated client.

So while working on any performance issue, you always want to see the performance on dedicated client as well, so I did the same.

1. Took the same SRF running in thin client, and connect it to the Dedicated client.
2. Followed the same steps: open the pick applet and queried the Purchase Order#

To my surprise, on dedicated client the PO# query took only 6 seconds which is quite acceptable performance. So, this is the issue I faced first time when the query is running slow in thin client but not in dedicated. Now, lets try to find out what exactly is going on behind the scene.

I referred the spool file and took out the culprit query. Execution time mentioned in the query was same around 6 seconds. I ran the "Alter Sessions" commands as mentioned in my earlier post and ran the query. It again took only 6 seconds.

***** SQL Statement Execute Time: 6.014 seconds *****

hmmm..... now it looks like everything seems working fine from configuration perspective but why is there a difference in the query execution when run in Dedicated client Vs Thin client???

I was looking into the issue more and found that when I did a query in PO# field with value "1234" and it returned multiple records with PO# like: 1234, 12345, 1234a etc. So, it is quite clear that "AutomaticTrailingWildCards" parameter in the CFG was not set to False. But, anyways the query should perform similar in both the dedicated and thin client, no matter this parameter is set to False or not. Though, this thought is quite logical but even then I wanted to give a try by disabling it. So, I thought of disabling it only on the "PO#" field on the pick applet, so that whenever user do a query on PO# field, system should not put the "like" keyword in the SQL generated at the backend.

I put the following User Property on the business component:

Name : Disable Automatic Trailing Wildcard Field List
Value : Purchase Order Number

I compiled the SRF, tested it in dedicated client, it worked fine. Now it is the time to test it in thin client as well. At this moment I was not sure how it would behave. I put the same SRF on thin client and performed the same steps for querying the Purchase Order#, I got the result in around 6 seconds. A big smile and relief too, but again I don't have the correct explanation yet for this problem why is it happening, but this user property came as the life saver :)

If you ever faced this kind of performance issue and know the exact reason please comment.

Constraining Business Component to accept unique value for some fields

One of the interesting requirements I heard recently to put a check on the business component to accept only those records which has the unique value in specified fields. Actual requirement goes this way:

“Opportunity Product” is the child business component of “Opportunity” Business Component. System should not allow adding the Opportunity Product record with the same “Product” associated with it. It should prompt a message; you cannot add same product to more than 1 line item. Something like below screenshot:

Immediate reaction could be put a script on PreWrite event of “Opportunity Product” business component (Non-Active Instance) to check if there is any other record with the same Product value and RowId not = Current record rowid (this is important because you want to check for the other record, if exists). And then prompt the message RaiseErrorText.

Okayy fine, the above solution would work for sure but back in mind people always think for some script less solution. Frankly speaking, I won’t prefer calling a workflow process here for just sake of providing the script less solution like invoke a WF process on PreWrite event to check if there any record exists with the same Product and then thru the error.

A friend of mine, Varun Gulati suggested a nice way to achieve this requirement. So, the function which will do this wonder is “BCHasRows()”. Here below is the solution:

1. Create a calculated field on “Opportunity Product” BC:

a. Name = Duplicate Product Calc
b. Calculated = True
c. Calculated Value =
BCHasRows("Opportunity", "Opportunity Product", "[Product] = '"+[Product]+"'"+"AND [RowId] <> '"+[RowId]+"'" + "AND [Oppty Id] = '" + [Oppty Id] + "'", "All")

2. Put the validation on “Product Id” field :

a. Validation =
[Duplicate Product Calc] = 'N'
b. Validation Message – String Override =
You cannot add same Product to more than 1 line item.

And that’s it. This solution looks very easy and clean isn’t it? Not a single line of code is required.

Basically, “BCHasRows()” take the Business Object, Business Component, Search Spec and Visibility as the input. Evaluates the Search spec on the BC and returns Y or N. Please note that the validation message has been put on “Product Id” field, and not on “Product” field, because I observed that “Validation” is getting fired on the base fields only (not on Join fields).
Give it a try!!