Inner Join Extension Table - User Property

Party business components are fundamental element of the application architecture. They are like piece of beautifully composed music crystallized together in order to reflect the complexities in business environment. Party model helps in the grouping of data according to business logic. The primary difference

between Party and Non-Party BC's is in the manner data is stored. For Non-Party BC's data is primarily stored in base tables while for party BC data is stored in extension tables. There are 8 prominent extension tables used for party data storage.

Now the question surfaces is how siebel determines which extension table to be used for Person-related BC or which extension table should be used for Organization-related BC. Where this mapping in the Account or Contact BC is given that decides which extension table to use? Answer lies in the Buscomp user properties. "Inner Join Extension Table n" user property specifies an extension table to S_PARTY for which the join to the extension table is an inner join. We can have multiple tables that are implicitly joined to S_PARTY extension table. Employee and Organization are examples of multiple extension tables.

Once we have discovered the source between S_PARTY and its extension tables how about creating Custom Party types(Bookshelf gives warning for these types though). A curious case here is can i create a custom table and use it as an extension table for my custom party type using this user property. Well, lets try this and extend the party group.

Happy Party-ing!!

Applet Message

Applet Messages are quite useful in scenarios where one has to display help text to end user in the applet itself. We do have ihelp also but ihelp's are view specific and if we have multiple toggles defined in applet with different operations on each applet then ihelp may not be a good option. One can also go for direct modification of applet WebTemplates but again it will include maintainence overhead. An applet message allows to combine static text and dynamic data which can be displayed on applet. With little bit of HTML one can also add zing to the message.

Siebel bookshelf has elaborated the usage and configuration of Applet Messages how ever one thing is missed there(correct HTML Type) that i will try to fill out here. Let's say we need to add some help text on the SR form applet. Following steps are required to add an Applet Message.

1 - The applet message is configured using the Applet Message and Applet Message Variable object types. Query for the applet you want to add message and navigate to Applet Message object type. Add message in the Text Message. We can use %1, %2 for dynamic data to be defined in Applet Message Variable.

2 - Once the message is confiured,time to create a message control. Following properties should be set:

Name = Any Name

Field = Name of the Applet Message

Field Type = Message

HTML Type = Plain Text (Make sure this is set to Plain text and not to text or text area)

HTML Display Mode = DontEncodeData (If HTML attributes are being used for color and font changes)

3 - Once control is created, we can expose this on the applet. We can map control anywhere on the template where we want to display the message.

The advantage of using Applet message is we can change this on runtime also using dynamic data, where date being displayed from calculated fields using lookup value. This is something which we can be used to enrich UI for end user assistance.

Happy Configuration!!!

Removal of Null Tags

A lot has been said and written about removing null tags in xml messages. Any integration ace will always suggest to go for XSLT to transform XML while an siebel eScript enthusiast will defend stating business service will be ideal way to achieve this. But a laggard like me is always in search of solution which is easier to implement and flexible enough to handle frequent changes. After peppering siebel forums one of my friend came up with devilicious idea of using the data mappers itself to remove the null tags.

As per siebel booskshelf:

"The Integration Component Fields list displays the list of fields for that component. Note the system fields Conflict Id, Created, Id, Mod Id, Updated, operation, and searchspec in the list. This setting prevents the EAI Siebel Adapter Query and QueryPage method from outputting these fields."

It says that system fields are not the part of output XML. Any integration field of type "System" is not included in the ouput siebel message of EAI Siebel Adapter. So key here is, if the tag is null we can replace it in datamapper with any system field.

Consider a scenario if description is blank in Service Request then we don't want Description Tag send to external system in the outbound message. In order to remove the null tag we can use following expression in the DataMap:

IIF([Description] IS NULL,[Created],[Description])

In this Description and Created are integration fields of IC. Created is of type System, so if description is coming as null then we are passing Created. Here as Created is system field it will not come up in the ouput message after conversion. Now one may say how to remove entire instance if all the tags are null. Siebel also has solution to that. Try out Source Search Expression for this scenario.

Happy Integration!!

PS: Watch Out for Vinay's world for XSLT to filter out null tags.

"Siebel-Authored" flag in Data Map

Lot of things in siebel are still not documented. Talk about those magical user properties or custom classes with super natural powers, most of them needs to be discovered by trial only. Recently while working on the Siebel ASI, my eyes started gleaming when i saw "Siebel-Authored" flag in Data Map editor screen. My initial hollow guess was this flag might be used by siebel to identify predefault data mappers which are used by ASI. But after checking in sample i confronted that this flag was blank for all records.

Out of curiosity i started searching for usage of this flag but was not able to hunt down much. Please feel free to add comment in case you have any clue about this flag.

Happy Cracking!!

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!!