Siebel: Querying for Distinct Records

Many a time during Siebel configuration, one is left wondering "so near yet so far" - to SQL that is. A recent scenario had this problem - send alerts to the activity's account's team members when it is past the due date. Sending more than one email for one or more pending activity is
not acceptable. One of the common things that we do when confronted with this problem is to just write a workflow or a piece of code somewhere that can:

1. Query for all activities past the due date


2. For each activity, get the account and query for the
   account's position team


3. For each member of the team, get the corresponding primary
   employee


4. Send notification/alerts and flag him to be ignored in
   future

Looking at this problem from a different angle, the solution
is somewhat more simple - get unique "account team member" - "activity"
combination that will be the potential notification candidate. Siebel
provides a "Distinct" property on business components that helps us to
achieve just this. Note that this property enforces a "DISTINCT" clause
on all fields present in the BC, which may force us to create a clone
when needed. Let us re-look at the solution now:

1. Create a BC based on S_EVT_ACT and with the following
   fields:
• Due Date (DUE)
• Status (EVT_STAT_CD)
• Account Id (OU_ID)
• Account Primary Position Id (S_ACCNT_POSTN.POSITION_ID)
• Join: S_EVT_ACT.OU_ID = S_ACCNT_POSTN.OU_EXT_ID)
• Account Primary Employee Email (S_CONTACT.EMAIL_ADDR)
• Join: S_ACCNT_POSTN.POSITION_ID = S_POSTN.PAR_ROW_ID
• Join: S_POSTN.PR_EMP_ID = S_CONTACT.PAR_ROW_ID


2. Enable "Distinct" flag on this BC


3. Create a simple workflow based on a business object which
   has the above BC as primary and which leverages the ""Outbound
   Communications Manager" service


4. Create a "Repeating Component Request" with the required
   search criteria - "[Due] = Today() AND [Status] = 'Open'"

Well, how is that? Of course both have the same number of steps and the second one actually looks more pronounced. But if you have done a fair bit of coding and solutions to problems such as these you know what to choose.

Siebel: Batch Script to Compile and Replace SRF

Well, I have been absent - you can't really blame me since I had nothing to say. But I am back and hope that matters at least a bit.

My recent work involved some digging around to compile SRF frequently and was surprised that there was hardly any ready-made way to do that. This is the state even though Siebel has been providing 'command line' syntax to do a number of things using Tools. So the following batch script was born for Microsoft Windows OS. It is not really that difficult to achieve it for other operating systems if you apply your mind a bit. I can no way call this script as my own, it is just putting together of many pieces. A brief explanation follows for the uninitiated.

The complete script can be found at the end of this post. To make it run just open notepad > copy the entire script and save the file as "CompUp.bat". If you just double click the file, there is no log created. It will be mighty helpful for the script to create appropriate logs - this will do it:
"d:\sba81\Compile\CompUp.bat 1>>d:\sb\Compile\CompUp.log 2>>&1"

Let us look at individual sections of the script:

Initiation
Set a few variables which can change across environments.

@set SBL_ROOT=d:\sba81
@set SBL_TOOLS_ROOT=d:\sba81\tools
@set SRF_BACKUP=d:\sba81\Compile\SRFArchive
@set SRF_FOLDER=%SBL_ROOT%\siebsrvr\objects\enu
@set BSCRIPT_FOLDER=%SBL_ROOT%\siebsrvr\webmaster\enu

@set OLD_SRF=siebel_sia.srf
@set NEW_SRF=siebel_sia-%TODAY:~10,4%%TODAY:~4,2%%TODAY:~7,2%%NOW:~0,2%.srf
@set CFG=%SBL_ROOT%\siebsrvr\bin\enu\epharma.cfg

@set SIEBEL_SERVER="my_Siebsrvr"
@set WEB_SERVER="my_Webserver"

Most of it is standard stuff, you might observe that the NEW_SRF is named such that repeated compiles on a single day will not cause any harm - assuming each compile takes at least an hour to complete. Pick any CFG of your choice. Siebel server, web server and gateway parameters are copied from the service names. To view service names go to Windows Start Menu > Run > Type 'services.msc' > Hit OK. In services windows go to 'Properties' of the appropriate service and locate the name - these do not have spaces.

Compile
Next comes the actual compile - use Siebel Tools command line options to initiate compile. When the batch file is being executed, you may actually see Tools open up, compile SRF and close.

@ECHO %DATE% %TIME%: Compile SRF with %SBL_TOOLS_ROOT%\bin\enu\tools.cfg
@%SBL_TOOLS_ROOT%\bin\siebdev.exe /c %SBL_TOOLS_ROOT%\bin\enu\tools.cfg /d ServerDataSrc /u /p /bc "Siebel Repository" %SRF_BACKUP%\%NEW_SRF%
@IF %ERRORLEVEL% NEQ 0 GOTO ERR_HANDLER

One obvious point here is that you need Tools to be installed on the server. If you are using a box that cannot have Tools (or using other operating systems), you may need to write additional statements to telnet (or something similar) to the Tools box, compile SRF and transfer the same to Siebel server machine. If everything runs fine you will have the SRF compiled, hurray! On the other hand, if there is an error the batch execution just stops without executing further statements. These are scattered throughout the script to direct batch execution in case of errors.

Replace SRF
Now, stop the server to enable us to replace SRF.

net stop %SIEBEL_SERVER%
@sleep 120

The sleep is required to wait for the server to actually stop. During execution you may note that 'net stop' command tells us that the service could not be stopped. What really happens is that it waits for too small a time for server to come down. 'sleep' can hold the further execution till defined time (120 seconds) to enable the siebel server to completely shutdown. Next step: replace srf.

move /y %SRF_FOLDER%\%OLD_SRF% %SRF_BACKUP%\%NEW_SRF%_old
move /y %SRF_BACKUP%\%NEW_SRF% %SRF_FOLDER%\%OLD_SRF%

Copy the newly compiled SRF to Siebel server, while rename the old SRF to denote the SRF it was replaced with. Next generate browser script:

%SBL_ROOT%\siebsrvr\bin\genbscript %CFG% %BSCRIPT_FOLDER%

and start Siebel server:

net start %SIEBEL_SERVER%

Just in case you were wondering, we don't need to wait for the Siebel server to actually startup. Restart Web Server service to put the new browser script in action.

@REM net stop %WEB_SERVER%
@REM net start %WEB_SERVER%

As an alternative you can always follow the SWE command route to refresh browser scripts. Well, that was easy wasn't it?

Complete Script
You can download the batch file here.

@REM Compile and Upload Utility - Batch file to compile SRF and 'activate' it on Siebel server
@REM This is the Windows version! Use command "d:\sba81\Compile\CompUp.bat 1>>d:\sb\Compile\CompUp.log 2>>&1" to redirect o/p & error to log
@REM

@set COMPUP_LOG=d:\sba81\Compile\CompUp.log

@ECHO ====================================================================================================================
@ECHO %DATE% %TIME%: CompUp starting up..
@ECHO %DATE% %TIME%: Set variables

@set TODAY=%Date: =0%
@set NOW=%Time: =0%
@set SBL_ROOT=d:\sba81
@set SBL_TOOLS_ROOT=d:\sba81\tools
@set SRF_BACKUP=d:\sba81\Compile\SRFArchive
@set SRF_FOLDER=%SBL_ROOT%\siebsrvr\objects\enu
@set BSCRIPT_FOLDER=%SBL_ROOT%\siebsrvr\webmaster\enu

@set OLD_SRF=siebel_sia.srf
@set NEW_SRF=siebel_sia-%TODAY:~10,4%%TODAY:~4,2%%TODAY:~7,2%%NOW:~0,2%.srf
@set CFG=%SBL_ROOT%\siebsrvr\bin\enu\epharma.cfg

@set SIEBEL_SERVER="my_Siebsrvr"
@set WEB_SERVER="my_Webserver"

@ECHO %DATE% %TIME%: Compile SRF with %SBL_TOOLS_ROOT%\bin\enu\tools.cfg
@%SBL_TOOLS_ROOT%\bin\siebdev.exe /c %SBL_TOOLS_ROOT%\bin\enu\tools.cfg /d ServerDataSrc /u /p /bc "Siebel Repository" %SRF_BACKUP%\%NEW_SRF%
@IF %ERRORLEVEL% NEQ 0 GOTO ERR_HANDLER

@ECHO %DATE% %TIME%: Stop Siebel server %SIEBEL_SERVER%..
net stop %SIEBEL_SERVER%
@sleep 120
@IF %ERRORLEVEL% NEQ 0 GOTO ERR_HANDLER

@ECHO %DATE% %TIME%: Copy SRF
move /y %SRF_FOLDER%\%OLD_SRF% %SRF_BACKUP%\%NEW_SRF%_old
@IF %ERRORLEVEL% NEQ 0 GOTO ERR_HANDLER
move /y %SRF_BACKUP%\%NEW_SRF% %SRF_FOLDER%\%OLD_SRF%
@IF %ERRORLEVEL% NEQ 0 GOTO ERR_HANDLER

@ECHO %DATE% %TIME%: Generate browser script
%SBL_ROOT%\siebsrvr\bin\genbscript %CFG% %BSCRIPT_FOLDER%

@ECHO %DATE% %TIME%: Start Siebel server %SIEBEL_SERVER%
net start %SIEBEL_SERVER%
@IF %ERRORLEVEL% NEQ 0 GOTO ERR_HANDLER

@REM ECHO %DATE% %TIME%: Restart Webserver %WEB_SERVER%
@REM net stop %WEB_SERVER%
@IF %ERRORLEVEL% NEQ 0 GOTO ERR_HANDLER
@REM net start %WEB_SERVER%
@IF %ERRORLEVEL% NEQ 0 GOTO ERR_HANDLER

@GOTO End

:ERR_HANDLER
@ECHO Job failed! Error Code: %ERRORLEVEL%. Refer log file for further details.

:End
@ECHO %DATE% %TIME%: CompUp end!