Dynamic PickList as a DropDown

Sounds interesting !!! isn't it?

Let me agree that my Client is really smart in using the Siebel application in its own style. Well, he is actually dealing with the end users, who are using the application, so must be knowing what user wants. So, he (our Client) asked us to change the PickApplet into a dropdown which is available on "Template Name" field on "Sales Assessment List Applet" exposed on "Opportunity Assessment View".

First I thought, why he wants to change it at all?? But he was having the valid point in saying that "my users don't like opening the pick applet for selecting the assessment template name, when there is only 10-15 sales assessment templates are available in the application. So please make it visible like we have for static picklists."

hhhmm...... well, now I need to explore the possibility to achieve this. The requirement sounds bit tricky in the sense that for Dynamic PickLists, I always configured the PickApplet as well. So, to convert it into the dropdown is something new for me.

We already know that Static PickLists are displayed as dropdown, one might think of a solution to create a new List of Values with the same name as of Sales Assessment Templates and create a static picklist and use it for Template Name field. Also let the pickmap fire accordingly as per the previous configuration. But this is just a temporary solution, what if in future new Sales Assessment get added into the application, then every time we need to make a updation in List of Values as well.

So, lets do something interesting. Here is below what I tried :

1. Remove the PickApplet from "Template Name" field, available on Sales Assessment List Applet.

2. Keep the Read Only property set to TRUE.

3. Now check for the following property on the dynamic picklist (Sales Assessment Template Pick List) used :

a) Long List = FALSE

b) No Insert = TRUE

That's it. Compile the SRF with all the configuration changes mentioned above and observe the difference.

Try it out !!!!!

Alter Session Parameter for Siebel Query

I got surprised by the result of the execution time of one of the query, on which I was working on, as we were facing a performance issue in Production env while navigating one view.

As per the general process, I checked the Spool and Siebel Object Manager log to check for the query, which one is the culprit. I found the query which was taking around 70 seconds (as per the spool file). So that means I have won half of the battle, rest is to analyse the query and I will be thru. But now when I checked that query how it is behaving at the database, I ran the query on our Siebel Database (Oracle 9i) and got surprised when query returned the result in 200 seconds. This is something strange, how could this happened that if database is returning the result in 200 seconds then getting this result on UI is taking just 70 seconds.

In later investigation, I found that this is due to fact that whenever Siebel run any query on Oracle (Cost Based Optimizer), it sets few session variables for better executions of SQLs. So while verifying the SQL performance of a Siebel Client that is running on Oracle Cost-based optimizer mode, it is important to run the following alter session statements on the database :

For Oracle 9i

alter session set optimizer_mode = first_rows_10

alter session set hash_join_enabled = false

alter session set "_optimizer_sortmerge_join_enabled" = false

alter session set "_optimizer_join_sel_sanity_check" = true

For Oracle 10g

alter session set optimizer_mode = first_rows_10
alter session set "_optimizer_sortmerge_join_enabled" = false
alter session set "_optimizer_join_sel_sanity_check" = true

After setting these parameters, query started behaving the same way as it was behaving on the UI. So, now the only thing we need to check is what is the execution plan is being generated by Oracle, and I found that there was a "Full Scan" for one of the where clause, despite of the fact an index already exists on that column. The Explain Plan query being used was :

Explain Plan for

"Query"

Select * from Plan_table

Now, our investigation was narrowed down to the point that since index was not being used by the query, that's why it is resulting in performance issue. We asked our DBA to regenerate the statistics for that particular index and finally performance issue was resolved.

Automatic Trailing WildCards : A CFG Parameter

If you do a Query in any View of Siebel application and you observe some performance issue in getting the query result set, then "AutomaticTrailingWildCards" parameter is one of the culprit, which is available in Siebel.CFG under [SWE] section.

What it does

Lets say you want to query on "Name" field of Opportunity List Applet with value "siebelmantra", then system will fetch Opportunities with name like siebelmantra, siebelmantra1, siebelmantra Oppty, etc etc. That means it automatically suffix a "*" (asterick wild card character) after the string being queried. To confirm this, press "Alt + r" (Refine Query), you will see "siebelmantra*" was being queried in Name field, though you never mentioned "*" while querying. This is the effect due to the TRUE value of "AutomaticTrailingWildCards" parameter.

Recommendation

Open Siebel.cfg file and search for "AutomaticTrailingWildCards" parameter under [SWE] section and set its value to "FALSE". Restart the Siebel Servers and observe the difference by again querying on the UI.

How to create a Constraint PickList?

Today, I am going to share a very basic, and I would say a, "unique feature" in Siebel by which we can restrict values being displayed inside the PickList.

Here you go, "lets assume I need to create two fields a) Country b) State, on Opportunity business Component, along with their respective picklists, in such a way that system will display the list of States in the dropdown which belongs to the 'Country' selected in the Country Field."

Here below are the steps need to follow :

1. Check for the available columns (Characters Length: 30) in S_OPTY table, which is not being used by any of the field in Opportunity Business Component. Lets assume two fields for the purpose are ATTRIB_01, ATTRIB_02.

2. Create two PickLists for Country & State fields.
a) For Country

PickList Name : Country PickList
Project : Oppty
Bounded : TRUE
Business Component : PickList Generic
Static : TRUE
Type Field :
Type
Type Value : COUNTRY_LIST

b) For State

PickList Name : State PickList
Project : Oppty
Bounded : TRUE
Business Component : PickList Generic
Static : TRUE
Type Field :
Type
Type Value : STATE_LIST

3. Create LOV values in "Administration - Data -> List of Values" view, with the following values (I am assuming you know how to create LOV values)

a) For Country : (Type = COUNTRY_LIST)
Values = US, India

b) For State : (Type = STATE_LIST)
Values = Georgia, Ohio, Texas, NewYork, NewJersey (Put Description = US, for all)
Values = Uttar Pradesh, Madhya Pradesh, Karnatka, Gujrat (Put Description = India, for all)

4. Create two fields on Opportunity Business Component.
a) For Country

Name : Country
Column : ATTRIB_01
PickList : Country PickList

Country's Field PickMap

1. Field: Country
PickList Field: Value
Constrain: FALSE

b) State

Name : State
Column : ATTRIB_02
PickList : State PickList

State's Field PickMap

1. Field: State
PickList Field: Value
Constrain: FALSE

2. Field : Country
PickList Field : Desription
Constrain: TRUE

5. Create two fields on Opportunity List Applet :
a) For Country

Name : Country
Field : Country
Display Name - StringOverride : Country
Runtime : TRUE

b) For State

Name : State
Field : State
Display Name - StringOverride : State
Runtime : TRUE

6. Expose the two fields created on the Edit List webapplet layout of the Opportunity List Applet.

7. Compile the SRF with all the configuration done so far and see the magic on the UI.

Enjoyyyyyyy!!!!!

"Long List" property of PickList

Here is one of the vanilla feature I want like to share, you might be knowing already, but recently I came across.

On Opportunity form applet, "Country" field is configured as a dropdown (static picklist) from which user can select the value from dropdown list. While "Account" field is configured as a dynamic picklist and with the help of pickapplet, user can select the value. The bottom line is, both fields are using some picklist to help the user for selecting the value.

The one difference, I observed, in these two cases is that whenever I try to open the dropdown list to select some new value in "Country" field, system opens up the dropdown list and does an additional step in locating the current value in the list. That means you will see the current value of field being selected when dropdown list get opened.

While this was not the case with "Account" field. Whenever I open up the pickapplet (Account Pick Applet) to select some new value, the cursor always point to the first record in the pickapplet.

I think you got the point which I want to highlight here and let me tell you the reason behind this.

This behaviuor is due to the "Long List" property of PickList.

This property is responsible for positioning the cursor to the current value in the dropdown list, if FALSE. That means if "Long List" = FALSE, then we are telling Siebel that "my field doesn't contain a big list, so please always locate the current value in the list whenever dropdown list get opened." And since, dynamic picklist are generally big in record counts, so we rarely use this property to be TRUE for them.

Note: Long List = FALSE, is not recommended for dynamic PickList. It will result in performance issues when you open the pickapplet

Error retrieving next record from the database.

Let me tell you a story of one of the change request that we did in our Siebel application and became a learning lesson for us.

While navigating to "All Quotes View", system was giving the error : "Error retrieving next record from the database. (SBL-DBC-00104)". But when I tried navigating to "My Quotes View", no issues. Since the Applet, Business Component, Business Object, PDQs etc etc, being used in both the views were the same, the only thing which was different is "Data". That means there is something wrong with the data which is creating this problem.

So, as generally we do, I checked the Siebel Object Manager log and tried to find out what exactly was happening in the background. I queried for error code "(SBL-DBC-00104)" in the log file and found an Oracle code with error saying : "ORA-24345 Truncation or Null Fetch Error".

I checked for the possible reason due to which Oracle returns this error. Oracle says : "Please ensure that the buffer size is long enough to store the returned data.".

That means I was going into the right direction and data which is being displayed in "All Quotes View" was creating this problem. If I could relate this particular issue with Siebel, we have one Field User Property : "Text Length Override", which restricts the field to display some specific number of characters, no matter what is there in the database.

So, when I checked for all the fields in "Quote" business component, there was one field "Comments" which has been restricted to show only 500 charcters while its length was 1000. Now the only thing I need to confirm was if there is any record in S_DOC_QUOTE table having comments > 500 characters.

select row_id, comments

from S_DOC_QUOTE

where len(comments) > 500

And I was lucky to find one. So I just truncated it to 500 characters and again tried navigating to "All Quotes View" and eveything worked fine.

Learning Lesson: Before applying "Text Length Override" field user property, please make sure you should not have any data in the database which is voilating this constraint.

.

Siebel Vanilla Merge Records functionality

A very simple way to eliminate the duplicate records from the database without loosing any of the child entities information, is available in Siebel as a Vanilla process which is known as “Merge Records”.

It is available as a Menu-item in application level Menu -> Edit -> Merge Records.

Merge Record process can handle records at one time, I have tried merging around 50 records and it worked fine for me, not sure what is the maximum limit but I don’t think we will ever need more than this in live scenario.

The point to be noted while doing merge records is that the record you select last will become the destination record while all other selected records will behave as a source record. So, after the completion of Merge Records process, you will see only one record on the UI, which you have selected last and rest all the records will get merged into that.

Now, lets see what a Merge Records process can do and what it can’t?

What it can do and cannot do?
1. All the child records of the source records will get merge into the child records of the destination record, with the exception of duplicate records. That means, if you are merging two Opportunity records, then Opportunity Product records of source will get merge into the destination one. Similarly for Contacts, Attachments, Activities etc etc…

2. Merge Records process never touches any of the field on the destination record. All the field values remains untouched.

3. In a nutshell, we can say that it copies all the records of the source records, which are associated via link (One-to-Many Link or Many-to-Many Link), to the destination record. For eg : While applying merge records on two Opportunity records will copy the Sales Team of the source to destination.

Lets take an example of two Opportunities. Details of Opportunity records is mentioned below along with the child records details.

Opportunity Source Record
Oppty Name : Source Oppty
Account : Avatar
Contacts Associated : Ed Holden, Jason Rubin, David Schmidt
Products Associated : Bluetooth PC Card, Bluetooth PC Card II, Bluetooth USB Adapter
Sales Team : Paul Mason, Siebel Administrator, Sam Moss

Opportunity Destination Record
Oppty Name : Destination Oppty
Account : Avaya
Contacts Associated : Laura Abate, Jason Rubin, Robert Abel
Products Associated : Bluetooth USB Adapter, Bluetooth USB Adapter II
Sales Team : Siebel Administrator, Sandy Meizen


After Merge Records
Oppty Name : Destination Oppty
Account : Avaya
Contacts Associated : Laura Abate, Jason Rubin, Robert Abel, Ed Holden, David Schmidt
Products Associated : Bluetooth USB Adapter, Bluetooth USB Adapter II, Bluetooth PC Card, Bluetooth PC Card II
Sales Team : Siebel Administrator, Sandy Meizen, Paul Mason, Sam Moss



check it out !!!!!!!!!!

Early Instantiation of Business Component

In this article, I want to showcase a nice way that we should use while scripting in Siebel. This also comes under the Siebel scripting best practices.

I need to write a Script for a simple requirement : "I want to get a "Opportunity Name" and "Product Name" from "Opportunity" & "Opportunity Product" business component respectively. Opportunity Product is a child business component of Opportunity. Lets suppose, The Opportunity Id in the requirement is : "1-6JKM".

Here are few ways by which we can code :

First Method
var BO = TheApplication().GetBusObject("Opportunity");
var OBC = BO.GetBusComp("Opportunity");
with (OBC)
{
ClearToQuery();
SetViewMode(3);
SetSearchSpec("Id","1-6JKM");
ExecuteQuery();

if(FirstRecord())
{
var OPBC = BO.GetBusComp("Opportunity Product");
with (OPBC)
{
ClearToQuery();
SetSearchSpec("Oppty Id","1-6JKM");
ExecuteQuery();
if(FirstRecord())
{
TheApplication().RaiseErrorText("Opportunity Name = " + OBC.GetFieldValue("Name") + ", Product = " + OPBC.GetFieldValue("Product"));
}
}
OPBC = null;
}
}
OBC = null;
BO = null;


Second Method
var BO = TheApplication().GetBusObject("Opportunity");
var OBC = BO.GetBusComp("Opportunity");
with (OBC)
{
ClearToQuery();
SetViewMode(3);
SetSearchSpec("Id","1-6JKM");
ExecuteQuery();

if(FirstRecord())
{
var OPBC = BO.GetBusComp("Opportunity Product");
with (OPBC)
{
ExecuteQuery();
if(FirstRecord())
{
TheApplication().RaiseErrorText("Opportunity Name = " + OBC.GetFieldValue("Name") + ", Product = " + OPBC.GetFieldValue("Product"));
}
}
OPBC = null;
}
}
OBC = null;
BO = null;

Third Method
var BO = TheApplication().GetBusObject("Opportunity");
var OBC = BO.GetBusComp("Opportunity");
var OPBC = BO.GetBusComp("Opportunity Product");
with (OBC)
{
ClearToQuery();
SetViewMode(3);
SetSearchSpec("Id","1-6JKM");
ExecuteQuery();

if(FirstRecord())
{

if (OPBC.FirstRecord())
{
TheApplication().RaiseErrorText("Opportunity Name = " + OBC.GetFieldValue("Name") + ", Product = " + OPBC.GetFieldValue("Product"));
}
}
}
OPBC = null;
OBC = null;
BO = null;


If you had a glance on three different ways mentioned above, you will realize the third method is the most efficient method. Let me try to explain what exactly we have tried to achieve here.

First method is the simplest one, where to get any field value we query for Business Component with the row_id and do a GetFieldValue on the resultant record.

Second method is bit tricky, as you can see no need to query on Opportunity Product. "ExecuteQuery" is sufficient to fetch the records. This is the power of Link mentioned for the "Opportunity Product" in "Opportunity" business Object. Siebel is smart enough to initialize the link along with business component, whenever instantiated.

And best one is the third method, as you can see the Opportunity Product is instantiated right in the beginning. So when we query on Opportunity business component, the Siebel will automatically returns the associated Opportunity Product records as well. No need to put any extra "ExecuteQuery". This is what we call "Early Instantiation" of Business Component.

How to do "Generate Triggers" for new WF Policy to work?

"Generate Triggers", this is one of the step that one needs to perform whenever he created a new WF Policy and wants it to work as per the conditions specified for the policy.

Here is the process to do that :
1. Navigate to "Administration - Server Management -> Jobs" view and click on New.

2. Put the Component/Job = "Generate Triggers"

3. Now, in the Job Parameters applet, you need to provide the following four parameters :

a) EXEC : TRUE
b) Remove : FALSE
c) Privileged User : "Database Table Owner Name"
d) Privileged User Password : "Table Owner Password"

4. Click on Start.

5. After few seconds, refresh the view (by clicking on "Execute Query" button), you will see Execution Server will get assigned to the request and Status changes to "Active".

6. After few minutes, again refresh the record and once the trigger gets generated, Status changes to "Success".

Default Task of Monitor Agent

I was running into the issues when somehow emails were not going via Workflow Policy. So as per the general debugging process I started finding the culprit. Here are the steps I followed :

1. Query for S_ESCL_REQ table, whether if the policy get trigger, at all, and I found the record was there with the specific rule_id. The query for this purpose is :

select * from s_escl_req
where rule_id = "WF Policy RowId"

2. Since the "Duration" was set to "0" (Zero), then there is no question of record getting created in S_ESCL_STATE. So I skipped that step to check whether the record exist in S_ESCL_STATE.

3. Now, uptill this point, I can make out that WF Policy is doing its job absolutely fine as it is getting triggered and record gets created in S_ESCL_REQ table, but it is "WF Policy Monitor Agent" which is not processing it, and could be the culprit.

4. Then I extend my search to find out what is the that monitor agent who is responsible to process these record. For that I checked the "WF Policy Group Name" which is associated with the WF Policy. Then I went to "Administration - Server Configuration -> Enterprises -> Component Definitions" view and in Components applet query for :

Component Type : Workflow Monitor Agent

to find out the monitor agent name associated with Policy Group

5. Now in Component Parameter Applet, query for Parameter = "Group Name". Scroll down the component list unless you find the desired WF Policy Group Name in the bottom applet.

6. I found the expected Monitor Agent name and now the next step is to check whether this monitor agent is running or not.

7. I naviagated to "Administration - Server Management -> Enterprises" and Query for the Server Name on which all the monitor agents run. In the Component applet, I was surprised to see that Monitor agent was in running state. This was really strange for anyone, if everything is running fine then why the email was not going.

8. To dig it more, I drilldown on Monitor Agent name to check for its task in running state and there I found something strange that "No Task was there". And most of the time it happens that if Monitor Agent is not processing anyting it generally shows the task status as "Sleeping for 30 seconds" or something like that.

9. So, here is the point when I got to know what the exactly issue is. If no task is showing in the Task applet that means the "Default Task" parameter of that WF Policy Monitor Agent is set to 0 (Zero), which is required to change to 1 (One).

10. So I just navigated back to "Administration - Server Configuration -> Enterprises -> Component Definitions" view , query for the Monitor Agent and in the bottom Parameters applet, just change the "Default Task" from 0 (Zero) to 1 (One).

11. Restarted the Monitor Agent once and it started processing the request.

hoooooooooo, what a great relief, everything seems working fine now.

SBL-DCK-00163: Error loading dictionary

Sometimes it happens that you try connecting to Siebel dedicated client and system pops up the error message that "User Id or Password is not correct.".
There could be many reasons behind this error, but the root cause you will only get to know if open object manager log file. Today, when I was trying opening the dedicated client, got the same error and when I checked into the Siebel Object Manager log, found an error statement which says : "SBL-DCK-00163: Error loading dictionary".

This error comes when there is something wrong with the diccache.dat file, which stored in "Bin" directory. Actually this the dictionary file which contains information about tables, columns, indexes etc and internally used by the server programs for performance enhancements.

So, coming back to the resolution of the error, to get rid of this error you just need to delete the "diccache.dat" from "Bin" folder and try open the dedicated client again.

System will generate the fresh file again with the latest information from the server and it will work fine.

Dedicated client's spool file

Spool file generated by dedicated client is one of the unique feature which Siebel provides and help Siebel Developers while debugging many Performance issues in the application.
For every action we perform in Siebel, like navigate to view, create a new record, query for some data etc, Siebel generates a SQL query which runs at the database and data is being displayed on the UI.

Siebel spool file is a text file that contains each query executed at the database. So whenever you see any performance issue (like navigation on some view is taking time, query on some applet taking time), you can use this file to check for the query which is culprit and responsible for the delay.

We just need to perform the below mentioned steps to make the spool enable in the system :
a) Right click on the icon, which is being used to open the dedicated client.
b) Click on Properties, system will popup the icon's properties window.
c) Append the below mentioned line in "Target" section :
/s c:\spool.log
d) Click on Ok.
e) Open a new Siebel dedicated client session.

Now you will see a file gets generated with a name "spool.log" which contains all the query which is being executed at the database.

How I can use it for performance Issue

As soon as you see any performance issue in the application, take the same SRF that is being used in thin-client and open a Siebel dedicated client session with the same SRF after enabling the Spool.
Perform the same action resulting performance issue, in dedicated client. Once you see the data on the UI (lets say after 10-15 mins), open the spool file and check for the query which is taking such big amount of time. This way you will get to know which the culprit query is and run that query on the database directly to check if there is something wrong with the query.
If yes, you need to check for the configuration done last which is making query to perform slow. If No, then your DBAs might help you in improving the performance by creating any new index or regenerating the table/index statistics, if required.

License Keys in Sample Database

Here is one of the trick that I just tried with Siebel Sample Database and looks good to me.

When I open the Siebel Dedicated Client with the Sample Database, I was not able to some of the Product Configurator views under "Administration-Product" screen. I went back to check for the Views/Responsibilities assigned to me and found all the views were there under my responsibility. So the problem was somewhere else.

Later I found that, "If some of the License Keys are missing in the database, then it might happen that the associated views to it will not be visible in the application". I found the license keys which was required to make the views related to "Product Configurator", from "http://licensecodes.oracle.com/siebel".

Now I just need to enter these license keys, so I navigated to "Administration-Application -> License Keys" view and got surprised as everything was read only and system was not allowing me enter any more license keys.

Now, this is really fuzzy situation that I am having the license keys but not able to enter it into the system. After lots of head-scratching, I found one way by which we can do that.

What you need to do is, just change the "ConnectString" parameter in Siebel.cfg file to point to the Sample Database and try connect to the Local database (which is now actually pointing to the Sample Database) via dedicated now.

TA DA..... you will the License keys applet is now editable. You can enter whatever license keys you want.

ENjoy !!!!!!