How to disable Query on an Applet?

This is a very simple requirement that you might come across during your project. And yes, this has got the very simple answer too:

Create two applet user properties as mentioned below:

·         User Prop 1:

Name - CanInvokeMethod: NewQuery

Value - False

·         User Prop 2 :

Name - CanInvokeMethod: RefineQuery

Value - False

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 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
.

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.

Difference between Today() and TimeStamp() while used in Calculated field !!

Here is something interesting I found today while working on a requirement in which we need to capture the timestamp of the record when a field get updated.

So the exact requirement was :

Capture the timestamp of the record when the Status of the Service Request changes to Approved. The field used for this purpose named as "SR Approved On".

Now this requirement seems very simple and the very first idea come into any Developer's mind is that use the BusComp user property for this i.e. "On Field Update Set" and set the value for "SR Approved On" using Today(). I did the same thing and here is what I tried:

1. Create a BC User Property :

a) Name : On Field Update Set 1

b) Value : "Status", "SR Approved On", "IIF ([Status] = 'Approved', Today(), "")"

Everything looks okayy, but the only thing I wonder is that "SR Approved On" stores the correct date but not the time. "Time" it stores as "12:00:00" for each record.

Hmmmm.... what could be reason?? If you are thinking that underlying column for "SR Approved On" field is only date field, then this is not the case here. I verified the below things:

a) Underlying column for "SR Approved On" is of type "UTC Date Time".

b) Type of "SR Approved On" at the BusComp level is "DTYPE_UTCDATETIME".

So, this fact is totally ruled out that "SR Approved On" can only store Date but not time. That means problem is somewhere else or may be "Today()" is returning only Date not time. But still I didn't convince with this because I am in the impression like we use sysdate in Oracle SQLs it should work that way.

To verify this, I created a Calc Field on Service Request BC:

a) Field Name : Current Time

b) Calculated : True

c) Calculated Value : Today()

Exposed the field on Service Request List Applet and to my surprised I found that it displays the current date but time as "12:00:00 AM" for all the records.

Well, that means we cannot get current timestamp out of "Today()". I need to used something else and let me tell you this was my totally wild guess of using "TimeStamp()" function, which I never used before and it worked pretty well when I changed the Calculated value to "TimeStamp()".

Finally, I got the issue resolved, just changed the User property value to :

a) Name : On Field Update Set 1

b) Value : "Status'', "SR Approved On", "IIF ([Status] = 'Approved', TimeStamp(), "")"

Might be helpful for you, if not used it before !!

.

How to apply Oracle hint in query via Siebel Configuration?

Our team was facing one performance issue in our application in which navigation to a view was taking hell lot of time. As every Siebel developer does, we also spooled the query which is running in the background and found the culprit query for the issue. SQL query seems to be very simple:

SELECT T1.CONFLICT_ID, T1.LAST_UPD, T1.CREATED, T1.LAST_UPD_BY,
T1.CREATED_BY, T1.MODIFICATION_NUM, T1.ROW_ID,
T1.ACCNT_TYPE_CD, T1.NAME, T1.OU_NUM, T1.DOM_ULT_DUNS_NUM, T1.BU_ID

FROM
SIEBEL.S_ORG_EXT T1

WHERE

(T1.ACCNT_TYPE_CD = 'Establishment')

But why this simple query is taking time?? This seems to be a very simple query which is resulting in performance issue. Ok, no probs, lets try running this query at the database and see how it behaves, so I followed the instructions I mentioned in my earlier post. i.e.

http://siebelmantra.blogspot.com/2009/01/alter-session-parameter-for-siebel.html

After setting the session parameters, I ran the query and it took 105 seconds. hmmm..... then I checked the execution plan and found that it was doing the Full Table scan. If you look at the query, you will easily point out that create a index on ACCNT_TYPE_CD and hopefully you are done, but this is not the case. We already have a index on this field but not sure why it is not being used in the query, then you might say that get the statistics regenerated for it your issue get resolved, but that we have already tried, still not able to figure out what the cause is. Finally, our DBA helped us out here. DBAs are just magician of databases, I don't know what kind of magic stick they just use and sql query itself come to them and tell them what needs to be done here. :)..... anyways, joke apart, our DBA analysed the query and suggested that we should use "ALL_ROWS" in the query, the resultset is coming in just 2 seconds. I was surprised after hearing this because I never came across a scenario where oracle hint ALL_ROWS actually solves the performance issue, and also Siebel itself uses the Optimizer Mode = FIRST_ROWS, for session it establish with the oracle database, before running any sql query. But to my surprise, after putting the hint into the query, it ran fine with just 2 seconds on the database.

Anyways, this is also the first time learning for me, but the challenge is how to put this Oracle Hint "ALL_ROWS" in the query from Siebel configuration? And after lot much of search I found something that is worth sharing here. But before applying this, you need to very careful and get the approval from DBAs or your solution architects, so that it should not impact anywhere else in the application. Now, here is below how you can force a Siebel query to use the Oracle Hint. Identify the business component and add the following User Property there :

Property Name : OracleCBOHint

Property Value : ALL_ROWS

Compile the SRF and check the spool. You will see the difference in the query this time.

Check it out !!!

.

Error while running blank query on an applet !!

Today while working on one of the issue in UAT env, when I navigated to Assets screen to see list of all assets, ran a blank query, I got the following error :

There were more rows than could be returned. Please refine your query to bring back fewer rows(SBL-DAT-00500).

In the very first sight, it seems query is bringing a large number of records which Siebel can't handle. hmmm... okayy, lets try running a query which will bring less records so I put a query on "Type" field on the applet and ran the query and it worked fine. That means there is some limitation in Siebel while fetching the records and if the number of records goes beyond that limit it gives up. So, one might ask what is that upper limit??

Upper limit for number of records that Siebel can pull from a single query is equal to the value set for "MaxCursorSize" parameter available in your siebel.cfg file. Here below can be the values :

a) 0 : If this parameter set to 0 (zero), that means Siebel can fetch 10,000 records in a single query. This is the recommended value.

b) -1 : it indicates infinite number of records, results in low performance and not recommended by Siebel.

c) >0 : any number greater than 0 will fetch that many records in one query.

So, what happens is if you run a query on UI and you try to scroll the records and crosses the limit what has been set by "MaxCursorSize" parameter, you will get the above mentioned error. Moreover, the parameter is applied for each and every query that Siebel runs with the exception that it might override by "Maximum Cursor Override" property at the business component level.

I checked for the value of "MaxCursorSize" parameter in CFG and it was set to 0. Just to cross verify I also ran a query in the database and confirmed that number of records in S_ASSET records were more than 10,000 records. But, wait a minute, I didn't even scroll once on the Asset Screen and I just tried to navigate on the default view of the screen and still I received this error. This is something else is going on here, isn't it?

One more thing to notice here is that I realized that even I have more than 10,000 Accounts records in the application, but there is no issue when I navigate to All Accounts view. That means something special does exist with Asset business component which is causing this issue. And here below is the reason for it :

"Hierarchy Parent Field" property of business component was set to "Parent Asset Id" and due to this while running a blank query on the Assets UI, resulted in putting a "/*+ ALL_ROWS */" hint in the SQL that Siebel runs in the background. This is the difference I observed when I just removed the "Hierachy Parent Field" and compiled the SRF again and blank query worked fine this time. No issues. But this doesn't mean that this is the solution for the issue, you can't just remove this property to avoid this error because it get reflect in all the applet based on this business component. So here is the solution for this :

Use "Disable Buscomp Hierarchy", a user property available on the applet and set its value to True. Since it is applet based user property, only applies to the applet on which it is used.

Disable Buscomp Hierarchy = True

What it does is : it will just ignore the effect of "Hierarchy Parent Field" on the business component and run the query without "/*+ ALL_ROWS */" hint in the background to bring the records as per the normal process.

I put this user property on the All Assets List Applet and ran a blank query again, everything worked fine.

Hope it helps !!

.

BC Read Only Field v/s Parent Read Only Field : A Case Study

We got one requirement to make the Child and Parent Child Applet readonly in a view, if the GrandParent (the applet which is driving the visibility of the view) has got the Status = "Inactive". Sounds interesting, right?

Okayy, let me rephrase it and try to explain you with more details what exactly we want to see.

We have a View in which the following applets are exposed :
a) Accounts Form Applet - Parent
b) Service Request Form Applet - Child
c) Activities List Applet - Grandchild

Accounts applet has a field "Status" exposed on the UI and the requirement is that once Account Status = "Inactive", user cannot do any modification/insertion/deletion on Service Request and Activities applet. Both Child (Service Request) and Grandchild (Activities) applet should become readonly.

I can think of two different User Properties that we can use to achieve this. Lets see the both solution :

First Solution

So, the very first BC user property comes into mind is "BC Read Only Field", which just require a BC Field as a value and depending upon the field's value (Y/N), the BC become readonly. To achieve the solution with this user property, follow the below steps :

a) Create Calc field in Service Request BC:
Field Name : SRReadOnly
Calculated : TRUE
Calc Value : iif(ParentBCName() = "Account" AND ParentFieldValue("Status") = "Inactive", "Y", "N")

b) Create BC User Property in Service Request:
Name : BC Read Only Field
Value : SRReadOnly

c) Create Calc field in Action BC :
Field Name : ActivityReadOnly
Calculated : TRUE
Calc Value : iif(ParentBCName() = "Service Request", ParentFieldValue("SRReadOnly"), "N")

d) Create BC User Property in Action :
Name : BC Read Only Field
Value : ActivityReadOnly

e) Set Link Specification = True for "Status" field at Account BC.

Second Solution

The next BC User property that we can use here is "Parent Read Only Field", which can be used as follows :

Name : Parent Read Only Field: <_parentbuscompname>
Value : <_parentfieldname>

a) Create a calc field on Account BC :
Name : AccntInactive
Calc : TRUE
Calc Value : iif([Status] = "Inactive", "Y", "N")

b) Create a BC User Property in Service Request :
Name : Parent Read Only Field: Account
Value : AccntInactive

c) Create a calc field on Service Request BC :
Name : AccntInactive
Calc : TRUE
Calc Value : iif(ParentBCName() = "Account", ParentFieldValue("AccntInactive"), "N")

d) Create BC User Property in Action :
Name : Parent Read Only Field: Service Request
Value : AccntInactive

Compile the SRf after using each solution and observe the changes by setting Account Status Active/Inactive.

If you would compare the two solution, both looks same from configuration perspective as both require two calc fields and two user properties in all. But from scalability standpoint, second solution is better as you can easily use as many instances of "Parent Read Only Field" you want, in case Activities/Service Request need to show read only, when exposed under some other Parent BC like Opportunity, Quote etc. While in case of "BC Read Only field" you need to accomodate the business logic inside the calc field itself, as we can have only one instance of this user property at one time.


Third Solution

I can think of one more solution for this problem by, and I think the most efficient, by using both user property. Here it goes :

a) Create a calc field on Service Request BC :
Name : AccntInactive
Calc : TRUE
Calc Value : iif(ParentBCName() = "Account" AND ParentFieldValue("Status") = "Inactive", "Y", "N")

b) Create a BC User Property in Service Request :
Name : BC Read Only Field
Value : AccntInactive

d) Create BC User Property in Action :
Name : Parent Read Only Field: Service Request
Value : AccntInactive

Third solution, we can actually save one more calc field and solution is achieved by creating 1 Calc field and 2 user properties.

Your comments are most welcome, incase you have any other good ideas around this scenario.

Keep commenting !!!!!

.

How to Enable a Customized button on an Applet?

Applets (Form applet / List applet) in Siebel are so robust in nature, that apart from using vanilla functionalities, user can also invoke its customized process/functionalities. And the very simple way is to use a Customized button on the applet with the proper caption, so that when user clicks on the button, the respective process gets start.

So, whenever there is a requirement to put a Customized button on any applet, it is required to enable that button so that user can click on it. Siebel provides various ways to enable a button on any applet, today we will talk about all those ways :

a) PreCanInvoke Event Script

b) Named Method n : Applet User Property

c) "EventMethod" methods.

d) "CanInvokeMethod" : Applet User Property (Only used in Siebel 8.0 and above)

All the above listed ways are serving the same purpose i.e. enable a button on applet UI. Lets see in detail, how each one of them works. Lets assume the method being invoked by button click is : "siebelmantratestmethod".

1. First Method - PreCanInvoke Event Script

if (MethodName == "siebelmantratestmethod")

{

CanInvoke = "True";

return (CancelOperation);

}

2. Second Method - Named Method n : Applet User Property

Create an applet user property with the following details :

Name : Named Method 1: siebelmantratestmethod

Value : 'INVOKE', 'siebelmantratestmethod'

3. Third Method - "EventMethod" methods

a) No need to create an user property.

b) No need to write script on PreCanInvoke event

c) Just prefix "EventMethod" with the name of the "Method Invoked" (property of the control).

Method Invoked = EventMethodsiebelmantratestmethod

4. Fourth Method - "CanInvokeMethod" applet user property (Only available in Siebel 8.0 and above)

Create an applet user user property with the following details :

Name = CanInvokeMethod: siebelmantratestmethod

Value = TRUE

Points to be noted :

a) PreCanInvoke event script should be avoided as much as we can. The only scenario in which we need to write code in PreCanInvoke event is "Conditional" enabling/disabling of button.

b) EventMethod and NamedMethod are the mostly used ways and recommended ways for enabling the button. I am still not sure what is difference between the two. If you know about this, your comments are most welcome.

c) CanInvokeMethod - applet user property gives us the advantage of conditionally enabling of a button. But only available in Siebel 8.0 and above.

.

Required : A Field User Property

Siebel provides various ways in making any Field mandatory while saving the record. Some of the common ways we use in our daily Siebel implementation are :

a) "Required" property of BusComp's field. Setting this property to TRUE, made the field mandatory while saving the record.

b) "Required", a field user property which calculates an expression to decide whether the field should be mandatory or not. This is very useful for making field mandatory, conditionally.

c) Last one is writing script on "PreWriteRecord" event of business component.

I got the requirement to make "Spouse Name" field mandatory on the UI, when user checked the field "Married" to True.

The requirement sounds very simple but we should decide the best possible way to implement it. As I mentioned above, we can implement this requirement via two ways : first one is by writing code on PreWriteRecord event and the second one is by using "Required" field user property. Lets see the both implementation.

First Implementation - Scripting on PreWriteRecord() Event

if (this.GetFieldValue("Married") == "Y" && this.GetFieldValue("Spouse Name") == "")

{

TheApplication().RaiseErrorText("Spouse Name is a mandatory field");

}

Second Implementation - Required Field User Property

Create Field User Prop for "Spouse Name" field :

Name : Required

Value : IIf ([Married] = "Y", "Y", "N")

Compile the SRF by using one implementation at a time and see the difference.

Pros & Cons
1. Scripting solution is good if you need to show a customized message to the user, in case he forget to fill in the mandatory field. But scripting should be avoided as much as we can.

2. Using "Required" field user property is the recommended solution for such kind of scenarios.

.

Field Read Only Field : Business Component User Property

Vishnu (regular reader of SiebelMantra) asked me to put a post regarding making a field read only when some other field get set to some specific value. So this post is dedicated to you Vishnu.

Some might of you already know which User Property I am talking about. This is BusComp User Property : "Field Read Only Field".

Purpose :

This is a field user property which makes a field read only on the applet, depending upon the value of some other field.

Lets take an example and try to configure it. Suppose, on Opportunity business Component, there are two fields : a) Sales Stage b) Revenue. The requirement is as soon as "Sales Stage" = "Lost", user is not allowed to make any more change in "Revenue" field.

Lets how we can achieve this :

1. Create a calculated field:

Name = Revenue Read Only Calc

Calculated = TRUE

Calculated Value = iif([Sales Stage] = "Lost", "Y", "N")

2. Create one User Property at Opportunity Business Component :

Name = Field Read Only Field: Revenue

Value = Revenue Read Only Calc

That's it !! Compile the Opportunity Business Component and try to change the value of Sales Stage on the UI.

You can play with the Calculated expression to test some more scenarios using AND, OR clause.

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.

.