SESSION SQL

Disclaimer: This post may not appeal EIM war horses but might be of some interest to newbies in EIM.

There are two types of Data, the one you look up and the one you make up. EIM is the technique which is used for the later type. As per siebel Bookshelf "Siebel EIM manages the exchange of data between Siebel database tables and other corporate databases". The EIM process constitue of two major steps:

1 - The data is first copied from external source(external table, csv , excel) into the interface tables.

2 - This data from the interface tables is then loaded into predefined destination columns in the base tables using the configuration file.

This configuration file, popularly known as ifb file, hosts the magical words which can be used to import, update, delete, extract, merge data. A more detailed list of paramters is available in bookshelf. One of the interesting parameters in the list is "SESSION SQL" which can perform operations after the EIM execution. This can be very handy if anybody wants to update or delete from the Base table or Interface table after EIM processing is done as defined in the configuration file. A sample requirement could be setting Primary Organization in Contact. If EIM champs are still reading then definitely thought of using "EIM Explicit Primary Mapping" will be lurking in mind. But unfortunately the Primary Organization Id (BU_ID) is not mapped in the explicit primary settings in EIM_CONTACT table.

But still we can achieve the desired task by using "SESSION SQL". The idea is we can update BU_ID in the S_CONTACT with the ROW_ID of Organization which we want to be primary after we are done with the loading of "S_CONTACT_BU" base table. The key here is to have an identifier defined in the interface table that will specify which BU is the primary in the Interface table. We can either extend interface table or reuse any of the existing column(ensure that is not used in the EIM operation).

[Siebel Interface Manager]

.

.

.

.

[Update Primary Org]

SESSION SQL ="UPDATE SIEBEL.S_CONTACT SCON SET SCON.BU_ID = (SELECT SBU.ROW_ID FROM SIEBEL.S_BU SBU,SIEBEL.EIM_CONTACT ECON WHERE SBU.NAME = ECON.NAME AND ECON.X_PR_FLG = 'Y' AND ECON.T_CONTACT__RID = SCON.ROW_ID) WHERE SCON.ROW_ID IN (SELECT T_CONTACT__RID FROM SIEBEL.EIM_CONTACT WHERE T_CONTACT__RID IS NOT NULL AND X_PR_FLG = 'Y')"

TYPE = IMPORT

BATCH = 9999

TABLE = EIM_CONTACT

ONLY BASE TABLES = S_PARTY, S_CONTACT, S_CONTACT_BU

Interestingly we can have only one SESSION SQL statement per block and did i tell you we can also call procedures from this awesome parameter for complex processing.

Happy Data Load!!

Hierarchy Extraction ++

This is an extension to my previous post regarding Hierarchy Extraction which compares different ways to extract property sets. I have quoted in the earlier post regarding the length limitation(75 characters) of Workflow Utilities echo method using dot notation. But Siebel never run out of altervatives and each one gives me something different.

In order to overcome the length limitation of the dot notation one can make use of "Aliases" which lasts upto 255 characters. Workflow Processes can have Process Properties of type "Alias" (ignorant me). Basically this data type acts as a pointer to a specific attribute inside a Property Set. So in order to fetch Sr Number or Account Id, mentioned in previous post, we have to define two process properties of data type "Alias" with Default value as below

SR Number: SiebelMessage/ListOfService/Service Request/@SR Number

Activity Id: SiebelMessage/ListOfService/Service Request/ListOfAction/Action/@Activity Id

In the workflow in order to get the values from siebel message we need to pass input/Output variables as shown below:

Input Arguments

Output Arguments

The other advantage of using Aliases is that it doesn't error out reading a non-existing attribute from a Property Set.

Happy Extracting!!

Prompt:Browser Script::LoadPopupApplet:Server Script

Whether we can repeat browser side capabilities via server side, like Alert/Prompt, always charms any developer. Server side "Alert" is beautifully explained on the siebel Impossible scriptless challenge using "LS SubjectVisits Service" business service with "ShowConfirmationDlg" method. Recently i was asked to implement Prompt sort of functionality using server side code. After some hits and lots of misses i came up with not so beautiful solution but it worked.

Lets consider a scenario where on click of "Summary" button, code should check the status of Service Request if it is "open" then it should prompt user to enter notes and capture these notes and put it in the Summay of service request.

The solution is blend of List of Value BC and pop up applet. I found a business service "SLM Save List Service"(thanks to Google again...wonder what life would have been w/o Google...) which could be used to invoke a popup applet from server side code. "LoadPopupApplet" mehtod is key here. It takes input as the name of applet to popup and the mode in which the applet should be displayed. I followed below steps to achieve desired requirement.

1 - Create a LOV of type "SR_POPUP". Keep the description blank and fill any random value in Name and Value. We will use the description field to be displayed in the Popup applet. Description will be used as temporary storage to user input.

2 - Create a pop up applet "SR Type Popup Applet" based on the "List Of Values" business component with search spec "([Type] = 'SR_POPUP' AND [Active] = 'Y')" . Only expose the Description field in this applet. Ensure that applet should have Edit web layout defined.

3 - Now comes the part where we write the script to popup the above applet and consume the input provided by user. The below code is written to invoke the applet and clear the description field once the processing is done.

For the "Summary" method in the "Service Request List Applet", below mentioned code is written.

if(MethodName == "Summary")

{

//Initial Processing to check the status of SR

if(this.Buscomp().GetFieldValue("Status") == "Open")

{

//Processing to clear the keyed in description by user

var sLOVBC = TheApplication().GetBusObject("List Of Values").GetBusComp("List Of Values");

with(sLOVBC)

{

ClearToQuery();

SetViewMode(3);

ActivateField("Description");

SetSearchSpec("Type","SR_POPUP");

ExecuteQuery();

if(FirstRecord())

{

SetFieldValue("Description","");

WriteRecord();

}

}

// code to popup the applet

var sBS = TheApplication().GetService("SLM Save List Service");

var sInput = TheApplication().NewPropertySet();

var sOutput = TheApplication().NewPropertySet();

sInput.SetProperty("Applet Name","SR Type Popup Applet");

sInput.SetProperty("Applet Mode","6");

sBS.InvokeMethod("LoadPopupApplet",sInput,sOutput);

}

return (CancelOperation);

}

For the "PickRecord" method in "SR Type Popup Applet" following code needs to be written for setting the "Abstract" field in SR.

if(MethodName == "PickRecord")

{

TheApplication().ActiveBusObject().GetBusComp("Service Request").SetFieldValue("Abstract",this.BusComp().GetFieldValue("Description"));

this.BusComp().SetFieldValue("Description","");

this.InvokeMethod("CloseApplet");

return (CancelOperation);

}

This is approach by which one can popup an applet and allow user to enter something and then do further executions. Please do share alternates to prompt in server script.

Happy Prompting!!

Fetching IP Address in Siebel

Security is like superstition. Hits are always counted while misses doesn't. Recently i was asked to enhance security measure of our siebel ebusiness applications by tracking the IP address along with the login of the client. The first thought that came to mind was @#$@#$@# but then again why-to-fear-when-google-is-here happened. I found a lesser heard OOB business service which returns the session variables, cookies information and many more useful stuff.

"Web Engine HTTP TXN” business service can do the trick for you. Details of this BS is avialable on the support web in "TECHNICAL NOTE 317". "GetAllServerVariables" and "GetServerVariables" are key methods here for fetching the Login and IP address respectively. I followed below steps in order to capture the IP address of the logged in user.

1 - Create a action set which calls a workflow. In this workflow we will have two steps:

• First step calls "Web Engine HTTP TXN" with method "GetServerVariables". The input argument to this BS should be "REMOTE_HOST" and "AUTH_USER_ID". This returns the host ip address and logged in user id respectively.
• Second step makes use of "Inbound E-mail Database Operations" business service with method "InsertRecord". It inserts the fetched IP address and User Id values in the desired audit table.

2 - Invoke the above created action set by using Application Login run time event.

This business service is extensively used for session management and cookies handling. Digging deep will reveal the true power of this BS.

Happy Securing!!

Access control with EAI Siebel Adapter

User properties in siebel are as thrilling as gadgets in bond movie. Yes, we can define visibility view modes at integration object level. "ViewMode" user property defined at IC level does the magic. This user property can take different values, as defined by LOV type "REPOSITORY_BC_VIEWMODE_TYPE".

However,as i keep saying the beauty of siebel is the flexibility it provides. One can even use "ViewMode" input argument in the Query/Query Page methods of "EAI Siebel Adapter" business service. However this argument is not exposed in the tools but one can use it. We can see presence of this argument in Query method of another business service "XMLP Siebel Adapter", which is used in the BIP reporting. This BS can also be used to extract the siebel message.

Happy Crunching!!

Hierarchy Extraction

Blue is the color of champions and so is the color of siebel. After a month of ups and down services return to normal as cup is brought home,so as me. Time to talk some business now. There are numerous instances in the integration where extracting a hierarchy or reading a property set is required. This could be accomplished in multiple ways. Here we will discuss the plus and minus of different services which can be utilized to perform desired task.

Lets consider an Integration Object with a simple hierarchy with Service Request being parent and Activity being child. Our main aim is to extract SR Number and Activity Id in the hierarchy. Potentially there could be multiple ways to extract property set but here we will discuss most commonly used business services.

1 - Workflow Utilities(My favourite) - This is one of the most powerful business service given by siebel and can do wonders. After using in n number of scenarios still i am not sure what all things this gem can do. "Echo" method can be used to extract any sub-hierarchy or read any property set from hierarchy.

Usage: In the workflow We have extracted siebel message in the SiebelMessage hierarchy. The Input to this business service will be the hierarchy and in the output argument we need to define the exact hierarchy path of SR Number, i.e. "SiebelMessage.ListOfService.Service Request.SR Number"

Input Arguments

Output Arguments

Advantage:

• Ease of use.
• Allows Multiple extraction of property set in one go.
• In case we have multiple child and we have defined any property set to extract it fetches the first child instance in the hierarchy. Consider if we have to extract Activity Id in the above hierarchy and there are two activities for the SR then it will fetch the activity id of first instance. The hierarchy path defined will be "SiebelMessage.ListOfService.Service Request.ListOfAction.Action.Activity Id".

Disadvantage:

• It allows only 75 characters in the output argument as a result sub-hierarchy extraction is required to reach destination property set.

2 - PRM ANI Utility Service - This is one of the most commonly used business service for hierarchy extraction."GetProperty" is the method which does the trick.

Usage: It takes the IO hierarchy structure and Property Name as input and returns value as output.

Input Arguments

Output Arguments

Advantage:

• Easy to use as IO hierarchy is clearly defined in system

Disadvantage:

• It returns only one property value per step. We can't read multiple property sets in one go.
• If there are multiple child record instances under same tag name in hierarchy then this BS throws an error.

3 - FINS Industry XML Query Service - This is one of the lesser documented BS but can be effectively used for XML hierarchy extraction. We need to have XML hierarchy in place before we extract any property set. The real charm lies in the XPath notation which is used to specify the location in XML hierarchy.

Usage: It takes input as XML hierarchy along with the input arguments defined which needs to be extracted.

Input Arguments

Output Arguments

Advantage:

• It takes destination property location as XPath notation, which gives more flexibility in defining the destination and thus character limitation constraint could be avoided.
• Allows Multiple extraction of property set in one go.

Disadvantage:

• It fails to fetch value in repeated instances of IC's, i.e. in case of multiple child instances with same tag name.

So, we have a choice which one to avoid based on our requirements. Comments are welcome here if anybody can add more to the advantages/disadvantages list.

Bleed Blue!!