Tuesday, May 18, 2010

Case sensitive and Insensitive Query

Siebel earlier used to have one Field Level Property Use Default Senstivity. This property is obsolete now.

To provide Case Insensitive Query on the Fields, Siebel 8.1 version has introduced a CIAI (Case and Accent Insensitive). Accent Insensitive does not work in Siebel 8.1.1.2 vesrion. You can use SoundsLike Operator for the Accent based query.
On the column level, right click at Column.
Siebel will run you through a wizard and create a new column and indexes to support the Case Insensitive Query.

Note: You need to lock the project to run through the wizard.

Calculate Commit Time using Business Hours

 This post is a supplement to one of my previous post Calculate Business Hours

I had one requirement where I need to calculate the Date Time, user has to finish the work.
The time taken should not include the Holidays. It should only consider Working Hours to calculate the time taken.

As I told you in my previous post that the Business Service 'FS Holiday API Service' provides 3 methods:
   1. GetElapsedBusinessTime
   2. GetResponseTime
   3. IsHoliday

GetElapsedBusinessTime: It majorly requires input as the below:
  • Service Calender Id
  • Start Time
  • End Time
As the result it provides you the time spent in between the Start and End Time excluding Holiday and including only working hours.
Note: This method has a bug in Siebel 8.1.1.2 (21215):
Defined our Schedule from Sunday to Thursday, time from 7 AM to 7 PM. The timezone is (GMT+04:00) Abu Dhabi, Muscat. It is observed that whenever there is data like:
Start Time: 4/15/2010 10:31:34
Schedule Time Zone: (GMT+04:00) Abu Dhabi, Muscat
Calender Id: <>
End Time: 4/18/2010 08:31:33
Time Unit: Hours.
And the end time is coming on Sunday. like April 18, 2010, April 25, 2010 or 2 May 2010.
It is failing and crashing the server.

GetResponseTime: It majorly requires input as the below:
  • Service Calender Id
  • Start Time
  • Response Time
As the result it provides you the Date time or Commit Time, for example time till which user has to finish the work excluding Holiday and including only working hours.

IsHoliday: 
  • Calender Id
  • Date Time
As a result, it gives if that Date Time is lying on Holiday.

Oracle does not provide any OOTB functionality in OBIEE 10.1.3.4.1 (reporting) to cater this requirement. Our OBIEE team has to write a Procedure.

Sharing is the power.

Saturday, May 1, 2010

SoundsLike Operator

Searching for exact Contacts in Call Centre with First and Last Names is not that much easier as we may think.Different people uses different spelling for their names for example John can be spelled like Joan, Jon, Joanne, Joann.

How to search a contact with similar pronouncing name. This was one of the requirement I came across. One of my friend Jalaj suggested SoundsLike Operator. Siebel provides a SoundsLike Operator which can be used to find the similar pronouncing names.
Let me show you how it works, you need to query in the Applet Field with SoundsLike('John').
In return or clicking on Go, it will give results like in below.
This Operator can also be used to BusComp Prequery event to modify the searchexpr with SoundsLike Operator.

Sharing is the power