Join in siebel

Today I would like to share with everyone my understanding on what a join is. There are two types of join 

• 1) Implicit Join
• 2) Explicit Join

Implicit Joins- These are joins that exist in siebel and is pre-configured. These are not custom joins and not user created. Eg. Joins with the party table and its extension tables are implicit joins(S_PARTY and S_ORG_EXT). These joins will not have a join specification.

Explicit Joins - These are custom joins and user created. The concept of explicit joins can be better explained with the help of an example. Suppose you have an Account in Siebel and if all account has SSN which is stored in SSN table. For creating a join you store the primary key of the SSN table in the account table and you pull SSN for each account using the join.

Eg

Account(S_ORG_EXT)                                            SSN(S_SSN_TABLE)

Field - SSN(Stores row id of SSN record)           

The field SSN in Account BC stores the row id of the SSN record.

Now we can create a Join in the Account BC. Firstly we create a field SSN UI which displays the SSN numbers. Secondly we create a JOIN to S_SSN_TABLE. In the join specify the table to which we need to join in our case its S_SSN_TABLE. You can give any name as Alias name. Lastly we need to create join specification and it is here we specify the condition of  the join. We have the source field where we specify the field that stores the Primary Key of the table that needs to be joined. For our scenario its SSN and the destination Column is ROW_ID. Now in the UI each contact will have an SSN if the join is valid and the SSN will be fetched using the join

Join Constraint In Siebel

A join may be used to fetch values from a table other than the base table of a BC. Now if the join returns more than a single record then the data will not be displayed in the UI. Join constraint enables us to filter the records returned from a join.

For eg we have a BC  whose base table  is S_PARTY and the field is joined to S_PARTY_PER (Explicit join) and the join return more than one records. We can create a join constrain in which we can specify the Destination Column which will be a column in the joined table and in the Value field we can right an expression that would be compared against the destination column and the join will now return records that matches the constraint.

Enabling debug mode for Siebel Taskflow

Debug mode is very useful in debugging the code that we write and is really helpful in debugging E script. Have you ever came across  debug mode in Siebel task flow . This can be very helpful in checking the values that we pass through task properties and can help us in identifying the error-ed step in the task flow. Enabling the debug mode is very simple. To enable task flow debug mode , modify the client CFG file as follows :  

 Under the [InfraUIFramework] section , add the following line  :                                  EnableRestrictedMenu = TRUE    

After making the changes restart the client and you can see Debug Mode menu enabled under the tools section.

     

Increase Display value size for an LOV

Maybe you have created a static picklist in siebel and picklist may be using an LOV for which you have may defined values in the Application. What if you have lengthy LOV value to display? Suppose you have a picklist field reason and this field uses a picklist of type XXXXX and the LOV needs to show a list of values ranging from 30-40. Normally what we do is we create list of values in the application and the for the field reason you create a pick map and in the pick map you specify the field to be reason and the picklist field to be value. The picklist field value hold the Display value and using the pick map a copy operation is done whereby the value from the picklist field is copied to the BC field. Unfortunately we have a limitation and that is the value(Display Value) field in the picklist can store only upto 30 characters and if the the value that we need to store exceeds 30 characters then siebel will store the first 30 characters and the rest is truncated. My reserch ended up in aconclusion that we cannot extennd the size of the value field however i came across a workaround.

Instead of using the Display Value we can use the Description field which has a length of 250. You can enter the values in the description field and then use it to construct the pick map. This time for picklist field reason i have modified the pick map and the picklist field in the pick map was updated to Description. That helped me resolve my requirement.

Escript to get Last Day of Month in Siebel

Recently I came across an interesting requirement. In an applet, user has a date field and we need to check whether the date entered by the user is the last day of the month. If not we have to throw a validation. I was eager researching for some vanilla functionality to meet the requirement but I could not find one. Lastly I resorted to scripting. The approach was simple. Firstly from the date entered we will extract the month and year using getmonth() and  getfullyear() functions and for each month we will hard code the last day to 31 or 30 and when month is Feb we will check whether the year is a leap year or not. If its a leap year then last day is 29 else 28. So for every month we will store the last day n a variable and based on the date enter will return the appropriate last day.I have written a BC function the recieves a date as input and returns the last day of that month. The function was called from the BC Prewrite record and the function returned the last day which help me throw the validation. I am attaching the function and hope it helps

Rounding Off Decimal Issue - While exporting data from siebel to Excel

You all may be aware of the vanilla export functionality in siebel. Using this functionality users can export siebel data to CSV or TXT file. I encountered an issue while exporting data from siebel. In siebel applet we had a field which displayed decimal data upto 5 decimal values. But on the exporting the data the field was rounded off to 2 decimal places and in the CSV file I had rounded off values for the field.

For eg in siebel the field showed value 3.06789 and after export the values was rounded off to 3.07.

After analysis the issue was identified. Export showed this behavior because of the configuration made in Administration-Data->Locale. Here we have a field Number Fractional Digits which was pre-configured to 2. It was changed to 5 which helped me resolved the issue. The change was made for ENU only .If you are using a multi lingual application then you should make the changes accordingly to reflect the desired behavior.

Immediate Commit In Task- Siebel User Property

If you are worked on Siebel TBUI you may have come across the transactional property of a task flow. If a task flow is transactional siebel operations on a BC used in the task flow are not committed to the database immediately. Instead they are committed explicitly using a commit operation in the task flow. Now what if you want to commit immediately on a BC in a transactional task flow. Here is where the Immediate Commit In Task User property comes to play. Create a BC user property with the following syntax:-

Thats it!! You are done now any siebel operation done on that BC is immediately committed to the database.