Happy New Year!!

Time of year has arrived when travel plans, party plans presides over defects and status reports. So i take liberty of being non technical and thanks all readers across the globe for wonderful support and comments with very warm new year wishes.

May new year brings lot of happiness with less defects,more siebel patches, hefty pay hike, hot gal friend or boy friend (only for bachelors), trimmed waistline.

May you get a clean bill from your dentist, your cardiologist, your gastro-enterologist, your podiatrist, your mechanic and your plumber.

May your hair, your teeth, your abs and your stocks not fall; and may your blood pressure, your cholesterol, your white blood count and your mortgage interest rates not rise.

May New Year's Eve find you seated around the table, together with your beloved family and cherished friends.

May every day of the new year glow with good cheer and happiness for you and your family.

Happy 2k12!!

Happy Christmas and a Prosperous New Year

I'm getting ready for my seasonal break today, so I wanted to take this opportunity to wish all my readers a Very Happy Christmas!

Even if you don't celebrate the religious festival, I hope you enjoy the festive period. Thank you for reading at least some of my blog posts this year, and contributing to some very interesting requirements. I haven't been able to write something new lately, but you ca be sure of some pretty cool posts next year, Stay tuned!

And, if you haven't noticed already...Its snowing :)

Once again, we wish you an absolute cracker of a Christmas and a Great New Year, full of happiness!

Serialization - Sequence Generation

Christmas has arrived a week early for us. Generating records in numeric sequence numbers can be a burning requirement in any application but not when you are working with siebel.

Just when holidays were approaching and party mood was about take helm, we were asked to generate the payment requests associated with service request in sequence numbers (as third party system required that..no choice had to do it) . The first thought that struck was the usage of * Line Number (Sequence) BC based on the class CSSSequence. However the issue was for each Parent SR first payment notice starts from 1 as payment being child entity and the requirement was that sequence should start from 1000 and should be auto generated for every new record irrespective of Parent SR.

Fear of ending new year eve in office can do wonders and suddenly we found that this requirement could be fulfilled using "Serialization" instead of writing a script. This technique is extensively used for generating Case Serial Number/Claim Serial Numbers based on the region or year. We found it useful in our case. Mother of all siebel guides "Siebel Bookshelf" gives comprehensive details of configuring any custom BC to enable Serialization.
However, we had to run a minor update in order to start the sequence number from 1000. We created a new custom column "PAY_SEQ_NUM" in "S_CASE_SEQ_CTR" table and a custom field "Pay counter" which is used for sequencing in PUB counter business component for holding the sequence number. In order to start the sequence number from 1000 instead of 1 this field should pre-default with 1000. So that sequencing starts from 1000. There is also cool business service "PUB Case Serialization Service" which auto generates next sequence number in line.

PS: In order to have Serialization functionality available, make sure you are working on SIA version and you have access to Administration - Case view.

Happy Serialization!!

Hiding Custom Controls in Query Mode

"Yesterday it worked, Oops Today it is not working..."

This has to be my favourite line since i started my career. I was hit by the same nuke again by my team mate where we had to hide custom controls from the users in Query Mode. Some code was written to control the visibility of the custom buttons but it was failing in some conditions.

It is easy to control the above scenario when we have different applets for Edit and Query Mode. We can remove the custom button from the Query Applet and keep only on the Edit applet. However, things are little different in case we are using Edit List Mode. A custom code is viable solution but it is not full proof as there are chances that it may fail.

However Siebel comes again to our ally. For lucky developers who are working on SIA tools, there is a user property by which one can hide methods in Query Mode. 

Name: FINS Query Mode Disabled Method n

Value: Method Name 

The only catch here is the applet class should be "CSSFrameListFINApplication". So better test the entire functionality prior deployment.

Happy Configuration!!

Query within date range

Date range is one of the very common requirements across project. It eases out lot of tasks for end user, one can easily generate report or export data items within given date range. Recently we were asked to implement similar functionality by inputting From and To date from the end user and resulting in all Service Requests between the given date range. There could be multiple solutions to go about it but one potential one is detailed below.

1 - Create Two calculated fields in SR BC.FromDate and ToDate of type "DTYPE_DATE". Set the calculated value to "Today()".
2 - Expose these two fields in the Query mode of the Applet. In the Edit Mode hide these controls.
3 - Below piece of code is required for setting up the Search expression and clearing the specs on To Date and From Date.

var sFromDate = this.GetSearchSpec("FromDate");
var sToDate = this.GetSearchSpec("ToDate");
if(sFromDate != "" && sToDate != "")
{
 var sFrSubDate=sFromDate.substring(3,5)+"/"+sFromDate.substring(0,2)+"/"+sFromDate.substring(6,10);
var sToSubDate = sToDate.substring(3,5)+ "/" +sToDate.substring(0,2)+"/"+sToDate.substring(6,10);
var sSearchSpec = "[Created] >= '"+ sFrSubDate+ "' AND [Created] <= '"+ sToSubDate +"'";
this.ClearToQuery();
this.SetSearchExpr(sSearchSpec);
}

This code is generic enough without any error handling. Please add conditions as per your requirement. The basic catch here is we need to clear the FromDate and ToDate specs and build a new expression involving range and any other existing search criteria. Any dates used with SetSearchSpec must use the format
MM/DD/YYYY, regardless of the regional control panel settings of the server or client computer so the date variables should be modified to give proper result.

Happy Crunching!!

Session Management WebServices

This post is for one who has been bamboozled by below interview question.


Question: "Can we have dynamic users for Created By/Updated By fields during upsert operations instead of SADMIN in siebel inbound webservices using standard authentication mechanism? "
Soln: Yes we can, provided we are running a version 8.0.0.4 or higher. Stateless session management siebel authorization is key here in the inbound services.
Below setup is required in order to make this thing work.


1 - SessionAccessService and SiebelUserProfileService - These two webservices should be active and point to correct object manager. They should use sesssion management(i.e WSSOAP = 1) mode. The authentication type for all the methods should be set to None.

2 - Import wsdl and Consume SessionAccessService WS. Make a call to SessionAccess service and obtain the session token for a new Siebel User Session. For this PING method is used. The SessionToken value from the response message represents the unique Session Key that is associated with the newly authenticated and established connection for the SADMIN user. This token will be resused in subsequent requests. 

3 - Import wsdl and Consume SiebelUserProfileService WS. Using the session token obtained in previous step call method "SetContactContext" of the SiebelUserProfileService. In order to maintain context it is critical to pass SessionToken key. We should also pass the input argument "Login Id" of the user 

of which we want to set the context. "SetContactContext" method is added to "SiebelUserProfileService" business service as part of ACR - 385. 

4 - Now once the proper context has been established, upsert call to the desired entity should be made using the original SessionToken Key within the SOAP Header section. check the thin client for the magic as updated by will display the new user login instead of user used to login object manager. 

I hope it helps you make more comfortable when someone asks this question . 

Happy Integration!!

O.C.M. revised

Winter is approaching in this part of world, so is my hibernation mode. For a while Email templates are keeping me busy and some usage of "CreateRequest" and "SendMessage" methods of Outbound Communications Manager business service has forced me to draw a comparison between the two. It may help you to identify which one to go for sending outbound emails.

Below diagram lists out the advantages of both worlds.

There were situations where i felt need of passing parameters to email templates just as we pass parameters to DataMaps, DVM's at runtime. I searched for it without any success. Please feel free to comment if you have any clue about passing parameters to email templates apart from field substitutions or more points to add on above comparison list.


Happy E-mailing!!

Tools Crash - Edit Web Layout

They say software upgrade is nothing but a replacement of old bugs with new bugs. so better be prepared when you are going for machine upgrade, a working code may meet dead end. Recently one of my friend upgraded his machine to Windows 7 with IE 9. To our initial delight siebel was working fine but devil 
really lies in details. 

While trying to modify existing applet and view web templates siebel started crashing. Whenever "Edit Web Layout" was clicked siebel stopped working. After some search it was found that because of IE 9 that siebel was crashing. The crash was found to be due to mshtml.dll that is related to the version of the Internet Explorer. Morever it was found that Internet Explorer 9 is not listed as a supported IE version in System Requirements and Supported Platforms document for Siebel Version 8.1 (I know this should have been referred prior installation) but thought of tools crashing cause of IE version never crossed mind. After IE 9 uninstall, tools started working fine perfectly.

Anybody facing a tool crash on edit web layout, make sure you don't have IE 9 installed on your machine. 

Happy crunching!!

Siebel Mobile Connector

“Be different. Think different. Better to be a pirate than to join the navy.” - Steve Jobs, a great Visionary.

In his quest of paperless and wireless world, Steve has given lot of things which has changed the way we live, we breathe. He blazed through the ever competitive zone of technology and design in a way no one ever thought of. Perhaps no one ever will.

Siebel has also paced up with the changing dynamics providing lot of features enabling customers to create voice & wireless applications with Siebel business content. The Siebel Mobile Connector is a standards-based API that delivers well-formed XML from an optimized Siebel application definition.Third-party applications created with the Siebel Mobile Connector uses standard Web protocols or specific Siebel interfaces such as a Java Data Bean or the COM Data Control to send and retrieve data between users and the Siebel database.

Basic siebel mobile connector architecture works as follow-

1 Using the third-party application’s user interface, the user requests information residing in the Siebel database by executing login commands.
2 The third-party application passes the information requested by the user to Siebel Web Engine (SWE) in the form of an XML document. The request is made through HyperText Transfer Protocol (HTTP), Java Data Bean, COM Data Control or any Siebel object interface.
3 The Siebel Mobile Connector, which is part of the Siebel Server, invokes SWE to retrieve information from the Siebel database.
4 This information is then passed back through Siebel Object Manager to SWE.
5 When SWE has the requested data, it returns it in the form of an XML document to the thirdparty application. If less than the total data set is wanted, the request specifies a style sheet that should be applied to the data. The style sheets are located at the Siebel Server.
6 The third-party application parses the XML document and presents the Siebel data to the user in its own user interface.

More about the siebel mobile connector and SWE commands is available in the bookshelf guide. Dedicating this post to the one who redefined innovation, relaid the limits.

Stay hungry.Stay foolish.R.I.P Steve !!!

B.I.P. Report Error Guide

We have memory , we have google and we have bookshelf but still errors keep coming to spoil evening. Recently while working with BIP reports i ran across with some issues. I have created a trouble shouting guide housing some of the errors which we got while running BIP report from siebel along with resolution.

BIP Reports Troubleshooting Guide

I hope this document helps someone who doesn't have access to support web or working in secure domain where access to external world is prohibited.

Happy Reporting!!!

Error Workflow Process

The biggest difference between time and space is that time can't be resued. So better reuse the existing one in order to earn some time. Reusability is one the fundamental block of siebel. Be it configuration or scripting, siebel always advocate usage of existing ones. One to enrich this feature is "Error Process Name" property at the workflow level.

This property is used to specify the workflow process which should be executed whenever error occurs in the base workflow. One can create a generic workflow for error logging mechanism and can specify that workflow in the "Error Process Name" property. This workflow can act as universal exception handler for logging error across the multiple workflows.


One of the interesting features here is the passing process properties from the base workflow to the error workflow. It should be kept in mind in order to pass user defined process properties to the error workflow, we must redefine those process properties in the error workflow, giving the same name and type.
For eg, if one has to pass the name of the base workflow to error workflow, one has to define common process property in the original workflow and in the error workflow and then this user property in the base workflow should be populated with the name of base workflow.

so don't reinvent the wheel, just realign it....

Happy Configuration!!!

Read Only Field N: Applet Version

Implementing C.R.U.D. can be cruel...


Read only syndrome was bugging me for quite a while as we had a requirement of making multiple columns to be readonly on certain applets depending on multiple conditions. First thought that burst into mind was using dynamic toggles but conditions were complex and number of applets to address was huge. The worst part was underneath field should remain editable for update by interface workflows. Once again situation demanded to dive into mysterious siebel to have a simpler solution and boy, siebel seldom disappoints.

I was able to achieve this by using Applet Level user property "Read Only Field N". This user property allows to make field read only on UI, by allowing field updates from workflow or scripting. The syntax goes like



Name: Read Only Field 1
Value: 'FieldName', 'ConditionFieldName'

where

FieldName = Name of the field which we want to be readonly
ConditionFieldName = Name of the field which specifies condition to be read only


However the siebel catch here is, the applet class should be "CSSFrameListQuoteItem". It may work for other classes but for me it did the trick with this class. So , please test thoroughly that it is not impacting your other functionality when you change class.


Happy Configuration!!

The Lost Functions

"Wide acceptance of an idea is not proof of its validity" - Unknown


Despite No-to-script being the motto of every project its usage is inevitable. Sometimes a two line code is easy to implement rather complex configuration. There are lots of methods and events exposed by siebel for developer's ease. In fix-run situations it is tough to remember all the methods/events available. Bookshelf definitely possess the entire wealth but here i will post a cheat sheet which has juiced up all the methods and events exposed by the siebel except special ones.

Siebel eScript cheat Sheet

This sheet can help someone to decide for which event to go who rarely use scripting or can help to better prepare for an interview.

Happy Scripting!!

PS: If you are not able to d/l this file let me know preferrable email id. you will have personalized copy of this.

Tips 'n Tricks for Oracle CRM On Demand

An excellent free book providing tips and tricks fro improving your Oracle CRM On Demand application. - by Mathieu Emanuelli and Joann Alejandro Bravo

Cheers!

Email Manager

Policy programs are like cheat codes. If a requirement permit asynchronous nature then policy program is gold. The key feature of policy program is it doesn't need compilation after changes and can be modified run time. Anybody good at SQL can toy around with the powers of policy program.

Recently we were asked to implement email functionality for SR status update changes. As obesessed with PP, i thought of writing new policies for it instead of modiying existing workflows. Things were smooth, life was cool and eveything was deployed successfully. However to my horror emails were not getting triggered. Policy conditions were configured correctly as record was getting created in S_ESCL_REQ table on violation. Situation worsened when email was going via F9 functionality. After some debugging it was observed that Email Manager Task was not running because of which emails were not flowing out of the system.

Email Manager component in conjuction with Communications Outbound Manager is the trump while using policy program of type "Send Message" for sending emails. Both are part of the Communications Management component group. Email Manager uses profiles set up in Communications Manager. The Communications Outbound
Manager does verification of the profile. Below is the snapshot of how Email Manager sends email message.

1 - Workflow monitor agent reads record from the S_ESCL_REQ and inserts a record into the S_APSRVR_REQ table for workflow actions that invokes the Send Email workflow policy programs.

2 - Email Manager picks up records from the S_APSRVR_REQ table, setting their status from QUEUED to ACTIVE then to SUCCEEDED during the course of the execution.

3 - Outbound Communications Manager is invoked to log onto the SMTP/POP3 profile and send the outbound message.

So in order for smooth email bombarding Email Manager should be up and task should be running.

Happy e-Mailing!!

Generating Data to UI mappings in Siebel just got easier!

This is one of those tasks which is fairly simple to do. However, can be very time consuming considering you have to generate a mapping for an entire/multiple repositories. We've all have had to do this at some point, not enjoying it one bit!

Well, here is a code that will save you some time and your sanity :).

The below code generates a screen to Applet, and an Applet to BC mapping which can be then exported to excel.


Screen to Applet - 


select scr.name "Screen Name"
      ,nvl(nvl(ptabi.tab_text, scri.viewbar_text), scr.viewbar_text) "Screen"
      ,scrv.sequence "View Seq"
      ,vw.name "View Name"
      ,vwi.title "View"
      ,vw.busobj_name "Business Object"
      ,vwti.item_num "Item Num"
      ,ap.name "Applet Name"
      ,api.title "Applet"
      ,ap.buscomp_name "Business Component"
from   siebel.s_repository rep
       inner join siebel.s_screen scr on scr.repository_id = rep.row_id
       left outer join siebel.s_screen_intl scri on scri.screen_id = scr.row_id and scri.repository_id = rep.row_id and scri.name = 'ENU-STD'
       inner join siebel.s_screen_view scrv on scrv.screen_id = scr.row_id and scrv.repository_id = rep.row_id
       inner join siebel.s_application appl on rep.row_id = appl.repository_id
       left outer join siebel.s_page_tab ptab on ptab.application_id = appl.row_id and ptab.repository_id = rep.row_id and ptab.screen_name = scr.name
       left outer join siebel.s_page_tab_intl ptabi on ptabi.page_tab_id = ptab.row_id and ptabi.repository_id = rep.row_id and ptabi.name = 'ENU-STD'
       inner join siebel.s_view vw on vw.name = scrv.view_name and vw.repository_id = rep.row_id
       left outer join siebel.s_view_intl vwi on vwi.view_id = vw.row_id and vwi.repository_id = rep.row_id and vwi.name = 'ENU-STD'
       inner join siebel.s_view_web_tmpl vwt on vwt.view_id = vw.row_id and vwt.repository_id = rep.row_id
       left outer join siebel.s_view_wtmpl_it vwti on vwti.view_web_tmpl_id = vwt.row_id and vwti.repository_id = rep.row_id
       inner join siebel.s_applet ap on ap.name = vwti.applet_name and ap.repository_id = rep.row_id
       left outer join siebel.s_applet_intl api on api.applet_id = ap.row_id and api.repository_id = rep.row_id and api.name = 'ENU-STD'
where  rep.name = 'Siebel Repository'
and    appl.name = 'Siebel Power Communications'
and    nvl(rep.inactive_flg,'N') = 'N'
and    nvl(scr.inactive_flg,'N') = 'N'
and    nvl(scri.inactive_flg,'N') = 'N'
and    nvl(scrv.inactive_flg,'N') = 'N'
and    nvl(vw.inactive_flg,'N') = 'N'
and    nvl(vwi.inactive_flg,'N') = 'N'
and    nvl(vwt.inactive_flg,'N') = 'N'
and    nvl(vwti.inactive_flg,'N') = 'N'
and    nvl(ap.inactive_flg,'N') = 'N'
and    nvl(api.inactive_flg,'N') = 'N'
union
select scr.name "Screen Name"
      ,nvl(nvl(ptabi.tab_text, scri.viewbar_text), scr.viewbar_text) "Screen"
      ,scrv.sequence "View Seq"
      ,vw.name "View Name"
      ,vwi.title "View"
      ,vw.busobj_name "Business Object"
      ,vwti.item_num "Item Num"
      ,apta.name "Applet Name"
      ,api.title "Applet"
      ,apta.buscomp_name "Business Component"
from   siebel.s_repository rep
       inner join siebel.s_screen scr on scr.repository_id = rep.row_id
       left outer join siebel.s_screen_intl scri on scri.screen_id = scr.row_id and scri.repository_id = rep.row_id and scri.name = 'ENU-STD'
       inner join siebel.s_screen_view scrv on scrv.screen_id = scr.row_id and scrv.repository_id = rep.row_id
       inner join siebel.s_application appl on rep.row_id = appl.repository_id
       left outer join siebel.s_page_tab ptab on ptab.application_id = appl.row_id and ptab.repository_id = rep.row_id and ptab.screen_name = scr.name
       left outer join siebel.s_page_tab_intl ptabi on ptabi.page_tab_id = ptab.row_id and ptabi.repository_id = rep.row_id and ptabi.name = 'ENU-STD'
       inner join siebel.s_view vw on vw.name = scrv.view_name and vw.repository_id = rep.row_id
       left outer join siebel.s_view_intl vwi on vwi.view_id = vw.row_id and vwi.repository_id = rep.row_id and vwi.name = 'ENU-STD'
       inner join siebel.s_view_web_tmpl vwt on vwt.view_id = vw.row_id and vwt.repository_id = rep.row_id
       left outer join siebel.s_view_wtmpl_it vwti on vwti.view_web_tmpl_id = vwt.row_id and vwti.repository_id = rep.row_id
       inner join siebel.s_applet ap on ap.name = vwti.applet_name and ap.repository_id = rep.row_id
       inner join siebel.s_applet_toggle apt on apt.applet_id = ap.row_id and apt.repository_id = rep.row_id
       inner join siebel.s_applet apta on apta.name = apt.applet_name and apta.repository_id = rep.row_id
       left outer join siebel.s_applet_intl api on api.applet_id = apta.row_id and apta.repository_id = rep.row_id and api.name = 'ENU-STD'
where  rep.name = 'Siebel Repository'
and    appl.name = 'Siebel Power Communications'
and    nvl(rep.inactive_flg,'N') = 'N'
and    nvl(scr.inactive_flg,'N') = 'N'
and    nvl(scri.inactive_flg,'N') = 'N'
and    nvl(scrv.inactive_flg,'N') = 'N'
and    nvl(vw.inactive_flg,'N') = 'N'
and    nvl(vwi.inactive_flg,'N') = 'N'
and    nvl(vwt.inactive_flg,'N') = 'N'
and    nvl(vwti.inactive_flg,'N') = 'N'
and    nvl(ap.inactive_flg,'N') = 'N'
and    nvl(api.inactive_flg,'N') = 'N'
order by "Screen"
        ,"View Seq"
        ,"View Name"
        ,"Item Num"
        ,"Applet Name"

Output looks like -

Applet to BC mapping -

select  "Applet Name"
,"BC Name"
,"BC Field"
,"Required"
,"Calculated"
,"Calculated Value"
,"Join Name"
,"Table"
,"Column"
,"Data Type"
,"Length"
,"Multi-valued"
,"MV Link"
,"Pick List"
,"LOV Name"
,min("Caption") "Caption"
,"Display Order"
from (
select ap.name "Applet Name"
      ,bc.name "BC Name"
      ,fld.name "BC Field"
      ,fld.required "Required"
      ,fld.calculated "Calculated"
      ,fld.calcval "Calculated Value"
      ,fld.join_name "Join Name"
      ,(case when fld.mvlink_name is null then nvl(nvl(jotab.name, fld.join_name), case when fld.calculated = 'Y' then null else bc.table_name end) else null end) "Table"
      ,fld.col_name "Column"
      ,fld.type "Data Type"
      ,(case when fld.prec_num is null then to_char(fld.textlen)
             else to_char(fld.prec_num) || to_char(case when fld.scale is null or fld.scale = 0 then '' else ',' || fld.scale end)
        end) "Length"
      ,fld.multi_valued "Multi-valued"
      ,fld.mvlink_name "MV Link"
      ,pl.name "Pick List"
      ,pl.type_value "LOV Name"
      ,coi.caption "Caption"
      ,co.sequence "Display Order"
from   siebel.s_control co
       inner join siebel.s_control_intl coi on coi.control_id = co.row_id and coi.name = 'ENU-STD'
       inner join siebel.s_applet ap on co.applet_id = ap.row_id
       inner join siebel.s_buscomp bc on ap.buscomp_name = bc.name
       inner join siebel.s_field fld on fld.name = co.field_name and fld.buscomp_id = bc.row_id
       inner join siebel.s_repository rep on bc.repository_id = rep.row_id
       left outer join siebel.s_join jo on jo.buscomp_id = fld.buscomp_id and fld.join_name = jo.name
       left outer join siebel.s_table jotab on jotab.name = jo.dest_tbl_name and jotab.repository_id = rep.row_id
       left outer join siebel.s_picklist pl on fld.picklist_name = pl.name and pl.repository_id = rep.row_id
where  rep.name = 'Siebel Repository'
and    ap.repository_id = rep.row_id
and    co.repository_id = rep.row_id
and    bc.repository_id = rep.row_id
and    fld.repository_id = rep.row_id
and    nvl(co.inactive_flg,'N') = 'N'
and    nvl(ap.inactive_flg,'N') = 'N'
and    nvl(bc.inactive_flg,'N') = 'N'
and    nvl(fld.inactive_flg,'N') = 'N'
and    nvl(rep.inactive_flg,'N') = 'N'
and    nvl(jo.inactive_flg,'N') = 'N'
union all
select ap.name "Applet Name"
      ,bc.name "BC Name"
      ,fld.name "BC Field"
      ,fld.required "Required"
      ,fld.calculated "Calculated"
      ,fld.calcval "Calculated Value"
      ,fld.join_name "Join Name"
      ,(case when fld.mvlink_name is null then nvl(nvl(jotab.name, fld.join_name), case when fld.calculated = 'Y' then null else bc.table_name end) else null end) "Table"
      ,fld.col_name "Column"
      ,fld.type "Data Type"
      ,(case when fld.prec_num is null then to_char(fld.textlen)
             else to_char(fld.prec_num) || to_char(case when fld.scale is null or fld.scale = 0 then '' else ',' || fld.scale end)
        end) "Length"
      ,fld.multi_valued "Multi-valued"
      ,fld.mvlink_name "MV Link"
      ,pl.name "Pick List"
      ,pl.type_value "LOV Name"
      ,coi.display_name "Caption"
      ,co.sequence "Display Order"
from   siebel.s_list li
       inner join siebel.s_applet ap on li.applet_id = ap.row_id
       inner join siebel.s_list_column co on co.list_id = li.row_id
       left outer join siebel.s_list_col_intl coi on coi.list_column_id = co.row_id and coi.name = 'ENU-STD'
       inner join siebel.s_buscomp bc on ap.buscomp_name = bc.name
       inner join siebel.s_field fld on fld.name = co.field_name and fld.buscomp_id = bc.row_id
       inner join siebel.s_repository rep on bc.repository_id = rep.row_id
       left outer join siebel.s_join jo on jo.buscomp_id = fld.buscomp_id and fld.join_name = jo.name
       left outer join siebel.s_table jotab on jotab.name = jo.dest_tbl_name and jotab.repository_id = rep.row_id
       left outer join siebel.s_picklist pl on fld.picklist_name = pl.name and pl.repository_id = rep.row_id
where  rep.name = 'Siebel Repository'
and    li.repository_id = rep.row_id
and    ap.repository_id = rep.row_id
and    co.repository_id = rep.row_id
and    bc.repository_id = rep.row_id
and    fld.repository_id = rep.row_id
and    nvl(li.inactive_flg,'N') = 'N'
and    nvl(co.inactive_flg,'N') = 'N'
and    nvl(ap.inactive_flg,'N') = 'N'
and    nvl(bc.inactive_flg,'N') = 'N'
and    nvl(fld.inactive_flg,'N') = 'N'
and    nvl(rep.inactive_flg,'N') = 'N'
and    nvl(jo.inactive_flg,'N') = 'N'
)
group by  "Applet Name"
,"BC Name"
,"BC Field"
,"Required"
,"Calculated"
,"Calculated Value"
,"Join Name"
,"Table"
,"Column"
,"Data Type"
,"Length"
,"Multi-valued"
,"MV Link"
,"Pick List"
,"LOV Name"
,"Display Order"
order by "Applet Name"
        ,"BC Name"
        ,"MV Link" desc
        ,"Table"
  ,"Display Order"

Output looks like -

So there you go...you could later consolidate both to have a full UI to Data level mapping.

Oh! Almost forgot, Your welcome ;)

Cheers!

Dynamically Skills Refresh in AM

It is a real divine feeling to watch how Assignment Manager works. One of the joys of AM is system takes care of assigning everything you have created. But as they say, Devi-lies-in-details, issue starts when you dig deep.

Recently we were asked to implement AM for assignment of Service Request only to Logged in Employees. System should not assign SR to any employee which is not available in system.

First look of this problem seems this to be a cake walk. We created Employee availability as skill which was updated every time when he logs in our logs out of the system. But however on actual implementation we realized that skills of employees were not getting refreshed dynamically. It required "Release" button to be pressed every time in order to flush the employee skills and to update rulecache.dat file. Once again Support Web came to rescue and job was saved.

When the employees to assign are not Dynamic Candidates, AM during assignment looks into the cache for the candidate employees instead reading skills directly from the database. However with certain parameters we can achieve dynamic refresh. I will try to club all the possible ways to refresh the skills(One can definitely add more).

There are three possible ways, based on the flavour of Assignment (dynamic, batch , interactive).

1 - 'Refresh People Skills Interval(MaxSkillsAge)' Parameter - The value for this parameter should be greater than 0 seconds. This will refresh skill in specified seconds.

2 - 'Ignore Assignment Rule Code' Parameter - The value for this parameter should be set to TRUE in order to avoid cache.

The above two solutions work fine for Batch Assignment but still for dynamic assignments we were not able to assign service request properly. One reason was dynamic assignment makes use of WorkMon agent and this component doesn't have above parameters.

3 - This approach should be thoroughly tested prior implementation as it can very taxing on your system if you have large number of assignment rules. The idea is to recursively Release the assignment rules using a batch job which runs below business service.

function Service_PreInvokeMethod (MethodName, Inputs, Outputs)

{

var busobj;

var buscomp;

busobj = TheApplication().GetBusObject("Assignment");

buscomp = busobj.GetBusComp("Assignment Group");

buscomp.InvokeMethod("Release");

return (CancelOperation);

}

Disclaimer: If you have a lot of rules and data to load, it will take AsgnSrvr component some time to completely this process, which also means that AsgnSrvr will not be able to accept any incoming assignment requests during this process startup/shutdown and rulecache.dat update until the cache file is completely updated.

With the above three mechanisms one can dynamically refresh the skills or can always bank on Dynamic Candidate mechanism for smooth assignments.

Happy Assignment!!!

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

BIP Reports as Siebel Attachments

Continuing in the vein of BIP Reporting series, lets discuss another reporting requirement.

"Generate the SLA for the Service Request based on the associated Agreement and add the generated SLA as attachment to the Service Request."

The closest solution which i was able to achieve was based on the support web solution "Siebel BI Publisher 8 Reports Business Service [ID 823360.1]". The key idea here is to generate BIP report and associate that report as attachment to the Service Request. Below workflow was created in order to accomplish the desired task.

Lets discuss each step in detail.

1 - Report Output

This subprocess step invokes the "BIP Create Report Output" workflow process. This workflow creates a new record in the "Report Output BC" buscomp to be used in next step.

Input Arguments

SubProcess Input: Report Name

Type: Literal

Value: SR SLA Report

The input is name of BIP Report to be executed.

Output Arguments

PropertyName: ReportId

Type: Output Argument

Subprocess Output: ReportOutputId

2 - Generate Report

This step is the heart of this workflow. "XMLP Driver Service" business service generates the BIP report on the server based on the search specification given in the Bookmark argument using the integration object as mentioned. Generated report is associated with the row id (ReportId) obtained in the earlier step. One can also see generated report in "My BIP Reports view".This step takes following input argument.

Business Service Name:XMLP Driver Service

Business Service Method: GenerateBIPReport

Input Arguments

InputArgument: BIPUserId

Type: Literal

Value: Name of the BIP User Id

InputArgument: BIPPassword

Type: Literal

Value: Pwd of the BIP User Id

InputArgument: LoginId

Type: Expression

Value: LoginId()

InputArgument: MethodName

Type: Literal

Value: GenerateBIPReport

InputArgument: ReportName

Type: Literal

Value: SR SLA Report

InputArgument: ReportOutputId

Type: Process Property

Value: ReportId

InputArgument: ReportOutputType

Type: Literal

Value: PDF

InputArgument: ReportSelected

Type: Literal

Value: SR SLA Report

InputArgument: ViewMode

Type: Literal

Value: All

InputArgument: LanguageCode

Type: Literal

Value: ENU

InputArgument: Bookmark

Type: Expression

Value: "[Id] = '" + [&Object Id] + "'"

InputArgument: IntegrationObject

Type: Literal

Value: BIP SR IO

InputArgument: LDAPEnabled

Type: Literal

Value: True

3 - Get Report Path

Once the report is generted the next step is to get the path of the report file stored in the file system. This could be easily done using "FINS Industry BC Facility Service" business service. The output of this the absolute path name of the report file.

Business Service Name: FINS Industry BC Facility Service

Busienss Service Method: GetFile

Input Arguments

InputArgument: AttachmentId

Type: Process Property

Property Name: ReportId

InputArgument: BusObjName

Type: Literal

Value: Report Administration

InputArgument: FileBusCompName

Type: Literal

Value: Report Output BC

InputArgument: FileNameField

Type: Literal

Value: ReportOutputFileName

InputArgument: RootBusCompName

Type: Literal

Value: Report Output BC

InputArgument: RowId

Type: Process Property

Property Name: ReportId

Output Arguments

Property Name: Sourcepath

Type: Output Argument

Output Argument: Value

4 - Create Attachment

Once we have the absolute path of report in the file system, next step is to associate report as attachment to the service Request buscomp. This is achieved using the same business service used in earlier step but different method.

Business Service Name: FINS Industry BC Facility Service

Busienss Service Method: CreateFile

Input Arguments

InputArgument: BusObjName

Type: Literal

Value: Service Request

InputArgument: FileBusCompName

Type: Literal

Value: Service Request Attachment

InputArgument: FileNameField

Type: Literal

Value: ActivityFileName

InputArgument: RootBusCompName

Type: Literal

Value: Service Request

InputArgument: RowId

Type: Process Property

Property Name: Object Id

InputArgument: SourcePath

Type: Process Property

Property Name: SourcePath

This will create attachment record in the Service Request Attachment buscomp for the specified service Request.

This type of requirement can be frequent across Public Sector or Life Sciences domain and this post might just help in conquering those. During the heist of creating attachment i also found that one can also email the generated BIP report. One can look at Alex post for more details regarding this.

Happy Reporting!!

BI Publisher Integration

Welcome to the world of BI Publisher. BIP reports can be soother for those who are bitten by actuate. Architecture wise both works same but the key ease is in the development of report template for BIP. A typical comparison between Actuate and BIP is depicted below.

With Siebel 8.1.x.x release and BIP Enterprise server 10.1.3.4, we have tightly coupled integration with BIP reports just like actuate. However users on earlier versions can still avail benefits of BIP reports. Reports can be written in BI Publisher using web services or SQL to retrieve data from the siebel application. The reports can be executed from siebel application on button click with flexibility of passing parameters at run time.

The attached document from oracle unearth the secrets of executing reports.

Happy Reporting!!

Authentication for Siebel Reports

The best part from oracle is they keep coming with patches and tough part is we need to adhere them. In siebel 8.0 we were not having built in integration with BIP reports so one has to resort to either web service call or SQL strategy for executing BIP reports. With advancement of 8.1.x we have tightly coupled integration with BIP reports.

With integration comes authentication. Typically there are several options to choose for configuring security and authentication for Siebel Reports. When using WebServices or SQL based reports generally "Web Single Sign-On Authentication" mechanism is used as it requires Users logging into BI Publisher via its administrative interface.

However this authentication fails when one is using integration based approach which comes with Siebel 8.1. Since the release of Siebel 8.1.1.1 and BI Publisher 10.1.3.4.1 a new security model is introduced in BI Publisher, Siebel Security. When using the Siebel Security model, BI Publisher relies on Siebel for its authentication and authorization. This is primarily used for IO based reports. But now the question arises if we want to run webservice/sql based reports in conjunction with tightly coupled BIP reports which authentication to go for.

The intermediate solution is LDAP authentication which supports both types of reports in conjunction. The key points to implement here are:

1) Users logging into Siebel sould be authenticated via LDAP

2) Any reports requested by Siebel users and executed in BI Publisher should cause BI Publisher to request authentication via Siebel (and hence indirectly via LDAP). This will be for Tightly coupled IO based reports.

3) Users logging into the BI Publisher administrative interface sould be authenticated via BI Publisher’s own LDAP authentication mechanism. This will be for reports based on the WebServices or SQL.

However, The BI Publisher LDAP Security model has one disadvantage, it requires hardcoded LDAP Security Groups like XMLP_DEVELOPER,XMLP_ADMIN. BIP gurus can definitely throw more light on this.

Happy Reporting!!

Different Query Mode in Edit List

I crunched one. This post is an addendum to one of my previous "Food for Thought" post where the main challenge was to display different columns in Query Mode when applet is displayed in Edit List Mode. with some configuration-Scripting-Luck i am able to achieve this scenario.

The key ingredient here is the usage of Dynamic Toggle. Following necessary steps are required to achieve desired solution.

1 - Create a clone of the list applet which should be displayed in Query Mode. Remove unnecessary Columns which are not required to be displayed while querying. Consider two applets are:

a) SR List Applet

b) SR Query List Applet

2 - Make the "SR Query List Applet" as the default applet to be displayed in the view.

3 - Create a calculated field in the Service Request BC with following values:

Name: QueryCalc

Value: IIF(GetProfileAttr('QueryMe') = 'Y','Y','N')

4 - Now create a dynamic toggle in the "SR Query List Applet". Set the "SR List Applet" as toggle applet with following Value in the condition.

Auto Toggle Field: QueryCalc

Auto Toggle Value: Y

5 - Once toggle is created, its time to set Profile Attribute. Following piece of code is required in below applets:

a) SR Query List Applet

Following script is written to toggle to SR List Applet.

function WebApplet_PreInvokeMethod (MethodName)

{

if(MethodName == "ExecuteQuery" || MethodName == "UndoQuery")

TheApplication().SetProfileAttr("QueryMe","Y"); //This is used to toggle back to Applet with More Fields

}

function WebApplet_Load ()

{

TheApplication().SetProfileAttr("QueryMe","Y"); //This is used to toggle back to Applet with More Fields

}

b) SR List Applet

In this applet we will create our own custom method and will invoke NewQuery.

function WebApplet_PreInvokeMethod (MethodName)

{

if(MethodName == "TestQuery") //Instead of Query We are calling our own custom method. Test Query

{

TheApplication().SetProfileAttr("QueryMe","N");

this.BusComp().SetSearchSpec("Id","");

this.BusComp().ExecuteQuery();

this.InvokeMethod("NewQuery");//New query is executed on the default applet in the view

return(CancelOperation);

}

}

The key here is to execute a blank query and then do New query so that the default applet in the View appears,i.e. the SR Query List Applet. Compile the objects and look out for super cool stuff. Siebel Rocks!!!

Disclaimer: There are caveats in this approach. This should be thoroulgy tested before you implement in your scenario.

Happy Crunching!!

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

SecUsernameFieldSubstitution - User Property

Issues are inevitable as sugar and cholesterol.

In my recent heist in configuring recipient groups i was dumbstruck with some untold user property. We wanted to add Person as recipient group for Service Request. As per siebel support we added desired values in the "COMM_RECIP_SRC" LOV table. Email started to flowing using this recepient group. But the interesting part is "Person" BC is not defined with the Recipient* user properties instead of that only one user property is defined as

Name - SecUsernameFieldSubstitution: EMail Addr

Value - Email Address

Unknown of its usage thought of throwing this to siebel forum. A little insight on this user property will be of great help.

Happy Cracking!!

Popup visibility - Division Based

It is very critical that one should have appropriate access to information available. Thus siebel has given comprehensive access control mechanism which helps in restricting data visibility. We can have visibility filter at multiple levels including View, Business Component, Picklists, Drilldowns, Links .

However the modes which we can set is restricted to following values:

All

Personal

Sales Rep

Manager

Organization

Sub Organization

Group

Catalog

Recently i was asked to have a visibility control based on the Division during the assignment of Service Request. One should see employees related to his division only when he opens employee pick applet.

We opted for Personalization to implement this as it gives a flexibility to change the filter dynamically without any srf change. Steps required are:

1 - Set the Popup Visibility Type to "All" for employee buscomp. (Please check before doing this as this will impact all Picklists based on Employee BC)

2 - Create a Personalization Rule set for "Employee Pick Applet" with below Include Expression:

"EXISTS ([Division] = GetProfileAttr('Division Name'))"

where "Division Name" is the division of Primary Position which is pulled in Personalization Profile BC using join. However this approach fails when user has got multiple positions and he changes the active position from "Change Position" View via User Preferences. In such scenarios we need to set/reset Profile attribute after Change Position is executed.

Happy Configuration!!