Inbound E-mail Database Operations - Does not Validate Picklists

 

The “Inbound E-mail Database Operations” vanilla business service is a favorite with Siebel developers, it is used when Siebel’s rules regarding business objects comes in the way of your actual business requirement. It can be used to modify records into any Business Component under a business object different from your workflow’s BO. But I recently found an issue in its working when there are bounded picklists involved. Usually when one tries to set a value to a picklist field, and the picklist is configured as bounded, Siebel throws up a validation error saying the value cannot be found in the bounded picklist….. In the case of Inbound E-mail BS, the error is not thrown ie, an exception is not caused. Try this out on your Siebel installation.

The business component Action has a field “Type”, which has a predefault and a bounded picklist.

The picklist is bounded

Now I use the Business Service Simulator view to use the Inbound E-Mail BS’s InsertRecord method to insert an acitivity record.

I have set a incorrect value for all three picklist fields, the values are simply not present in the vanilla LOV system. When the simulation is run, we expect Siebel to throw up a picklist validation exception. Instead, we get a success message and an Activity is created.

Instead of taking the wrong value we provided, Siebel has taken the predefault value directly. If the input value was a valid one, Siebel creates the record correctly.

We had an automation workflow which received inputs from Inbound XML to create an activity, and when the values in the incoming XML were wrong, the activities were still getting created without validation errors.  The solution we implemented was to add a validation step in the workflow to ensure the records had correct, validated values.

If you are using this BS in your project, do check if the possibility of this error occuring in your business flow.

Tools–SIF from multiple objects

Siebel Tool’s “Add to Archive” feature which creates SIF files is a real lifesaver when code needs to be migrated in development phase.  You can create a SIF from different selections in one object selection, eg: you can SIF multiple applets together:

Or you can create a SIF from an entire project, in which case all the different objects in that project get added into a single file.

But is it possible to create a SIF file from different objects in different projects ? Siebel Tools documents how this can be done in its help system, but generally developers are not aware of this feature.

First select the first objects types to be added into the archive file, here it’s applets :

Next, without closing the popup applet in front, use the object explorer to navigate to the other object type which needs to be added. Here, I am adding Business Components from a totally different project. Again , select the business components to be added , right-click and select ‘Add to Archive’

The selected objects also get added to the same archive file, even if they are from different projects.

This can be repeated for all repository objects , giving a single file with required objects. However, on the target tools system, all the different projects have to be locked for object insertion. Tools will tell you which project needs to be locked  during the import process.

CMRFly - a new CRM system on Windows

I was just browsing my HackerNews RSS feed when I cam across a post with screenshots of CRMFly, a small CRM tool developed for the Windows platform. It looks like a very watered down version of the most common CRM functions in a small executable package. Some of the screenshots of the application show it's similarity to Microsoft Outlook. And for more, head up to CRMFly website.

Interestingly, the customer support site of the tool looks like it is built on Desk.com. Desk.com is a new offering from Salesforce.com for small and medium businesses.

Tasks

Track your projects and tasks in the same location as you track your leads and opportunities.   A project can have many tasks and Each Project is tied to a customer.

Configuration

With Configurable lists, you can control your workflow. Configure your own Task Statuses, Project Statuses, Task Contexts, Project Types, Product Types, Goal Types, Opportunity Statuses, Goal Types, and more.

Browser Scripts–‘this’ is a problem

 

One issue I have faced numerous times with Siebel’s browser scripts is that the ‘this’ reference is not recognized when invoked in a separate function.

This code works fine when written directly in the PreInvoke section of the applet:

function Applet_PreInvokeMethod(name, inputPropSet)
{
    if (name == "CustomMethod")
    {
        alert(this.Name());
        return ("CancelOperation");
    }
    return ("ContinueOperation");
}

 

But if you decide to move the code into a separate function of its own:

function Demo()
{
    alert(this.Name());
}

function Applet_PreInvokeMethod(name, inputPropSet)
{
    if (name == "CustomMethod")
    {
        Demo();
        return ("CancelOperation");
    }
    return ("ContinueOperation");
}

..the system will start giving errors saying method not defined.  This really gets in the way when there is not of field access and manipulation required in the function. One way out is to pass the this reference directly as an argument into the function.

function Demo(applet)
{
    alert(applet.Name());
}

function Applet_PreInvokeMethod(name, inputPropSet)
{
    if (name == "CustomMethod")
    {
        Demo(this);
        return ("CancelOperation");
    }
    return ("ContinueOperation");
}

 

Another way I have seen recently is to use a global variable for the applet and use that instead of the this. The variable has to be initialized in Applet_Load event

declarations()
{
    var g_oApplet;
}
function Applet_Load()
{
    g_oApplet = this;
}
function Demo()
{
    alert(g_oApplet.Name());
}
function Applet_PreInvokeMethod(name, inputPropSet)
{
    if (name == "CustomMethod")
    {
        Demo();
        return ("CancelOperation");
    }
    return ("ContinueOperation");
}

FINS CAP Buscomp Handler’s empty query problem

 

Currently I am working on a Siebel Financial Applications project using a lot of Business Rules Processor (BRPs) . The BRP way of working with Business Components is by using the various methods available under the  FINS CAP Buscomp Handler Business Service.

FINS CAP Buscomp Handler Business Service provides the following five methods:

• Query
• NextRecord
• GetFieldValue
• SetFieldValue
• InsertRecord

The BS works without a Business Object context, ie while specifying the Business Component on which to operate, the Business Object name is not provided. It is the only BS I know in Siebel which operates directly on business components without taking the BO context. But as we realized, this is not always the best way of operating. As the amount of data grew, we found the BRPs going slower and performance degradation.

On spooling out the SQL when the BRPs were running, we found that null queries being run in the tables, without a search criteria. When the InsertRecord method of the BS is used to insert a record into say..Opportunity BC which is based on S_OPTY table, the BS was running this query first.

SELECT
      T1.CONFLICT_ID,
      T1.LAST_UPD,
      T1.CREATED,
      T1.LAST_UPD_BY,
      T1.CREATED_BY,
      T1.MODIFICATION_NUM,
      T1.ROW_ID,
      T14.USAGE,
      T8.TRDIN_EXPIRE_DAYS,
      T7.NAME,
      T1.PR_DEPT_OU_ID,
      T7.INTEGRATION_ID,
      T7.LOC,
      T7.OU_NUM,
      T10.NAME,
      T7.CURR_PRI_LST_ID,
      T7.PR_BL_ADDR_ID,
      T7.PR_BL_PER_ID,
      T7.PR_SHIP_ADDR_ID,
      T7.PR_SHIP_PER_ID,
      T1.CONSUMER_OPTY_FLG,
      T13.BL_ACCNT_ID,
      T13.BL_CON_ID,
      T1.CHANNEL_TYPE_CD,
      T1.CURCY_CD,
      T1.CUST_ACCNT_ID,
      T14.PROJ_STAT_CD,
      T1.CLOSED_FLG,
      T13.GROUP_TYPE_CD,
      T13.DEPARTURE_DT,
      T13.ARRIVAL_DT,
      T4.STATUS_INBND_CD,
      T7.ROW_ID,
      T1.PR_CON_ID,
      T1.NAME,
      T1.NEW_LOAN_FLG,
      T13.OPTY_MARKET_CD,
      T12.STAGE_STATUS_CD,
      T13.OPTY_SEGMENT_CD,
      T4.STATUS_CD,
      T1.APPL_OWNER_TYPE_CD,
      T1.PAR_OPTY_ID,
      T5.NAME,
      T9.PAR_POSTN_ID,
      T5.PROJ_PRPTY_ID,
      T1.ALIAS_NAME,
      T1.PR_OU_INDUST_ID,
      T1.PR_OU_ADDR_ID,
      T1.PR_REP_DNRM_FLG,
      T1.PR_REP_MANL_FLG,
      T1.PR_REP_SYS_FLG,
      T1.PR_CMPT_OU_ID,
      T6.COUNTRY,
      T9.PR_EMP_ID,
      T1.PR_OPTYORG_ID,
      T1.PR_OPTYPRD_ID,
      T1.BU_ID,
      T1.PR_PRTNR_ID,
      T1.PR_POSTN_ID,
      T1.SUM_REVN_AMT,
      T1.SUM_CLASS_CD,
      T1.SUM_EFFECTIVE_DT,
      T1.SUM_COMMIT_FLG,
      T1.SUM_COST_AMT,
      T1.SUM_DOWNSIDE_AMT,
      T1.SUM_REVN_ITEM_ID,
      T1.SUM_MARGIN_AMT,
      T1.SUM_TYPE_CD,
      T1.SUM_UPSIDE_AMT,
      T1.SUM_WIN_PROB,
      T11.LOGIN,
      T1.PR_SRC_ID,
      T6.STATE,
      T1.PR_TERR_ID,
      T1.PROG_NAME,
      T1.PROJ_PRPTY_ID,
      T13.REL_TYPE_CD,
      T1.SALES_METHOD_ID,
      T12.NAME,
      T1.STG_START_DT,
      T1.CURR_STG_ID,
      T12.STG_ORDER,
      T1.SECURE_FLG,
      T1.OPTY_CD,
      T1.PGROUP_PUBLIC_FLG,
      T1.BU_ID,
      T2.FCST_CLS_DT,
      T2.FCST_REVN_CURCY_CD,
      T16.LOGIN,
      T17.EFFECTIVE_DT,
      T17.COST_AMT,
      T17.DOWNSIDE_AMT,
      T17.MARGIN_AMT,
      T17.WIN_PROB,
      T17.REVN_AMT,
      T17.ACCNT_ID,
      T17.CLASS_CD,
      T17.REVN_AMT_CURCY_CD,
      T17.QTY,
      T17.CRDT_POSTN_ID,
      T17.TYPE_CD,
      T17.UPSIDE_AMT,
      T19.FST_NAME,
      T19.LAST_NAME,
      T20.SRC_CD,
      T13.ROW_ID,
      T13.PAR_ROW_ID,
      T13.MODIFICATION_NUM,
      T13.CREATED_BY,
      T13.LAST_UPD_BY,
      T13.CREATED,
      T13.LAST_UPD,
      T13.CONFLICT_ID,
      T13.PAR_ROW_ID,
      T14.ROW_ID,
      T14.PAR_ROW_ID,
      T14.MODIFICATION_NUM,
      T14.CREATED_BY,
      T14.LAST_UPD_BY,
      T14.CREATED,
      T14.LAST_UPD,
      T14.CONFLICT_ID,
      T14.PAR_ROW_ID,
      T2.ROW_ID,
      T3.ROW_ID,
      T17.ROW_ID,
      T18.ROW_ID,
      T20.ROW_ID
   FROM
       SIEBEL.S_OPTY T1
          INNER JOIN SIEBEL.S_OPTY_POSTN T2 ON T1.PR_POSTN_ID = T2.POSITION_ID AND T1.ROW_ID = T2.OPTY_ID
          INNER JOIN SIEBEL.S_PARTY T3 ON T2.POSITION_ID = T3.ROW_ID
          LEFT OUTER JOIN SIEBEL.S_SYS_KEYMAP T4 ON T1.ROW_ID = T4.SIEBEL_SYS_KEY
          LEFT OUTER JOIN SIEBEL.S_OPTY T5 ON T1.PAR_OPTY_ID = T5.ROW_ID
          LEFT OUTER JOIN SIEBEL.S_ADDR_PER T6 ON T1.PR_OU_ADDR_ID = T6.ROW_ID
          LEFT OUTER JOIN SIEBEL.S_ORG_EXT T7 ON T1.PR_DEPT_OU_ID = T7.PAR_ROW_ID
          LEFT OUTER JOIN SIEBEL.S_ORG_EXT_ATX T8 ON T1.BU_ID = T8.PAR_ROW_ID
          LEFT OUTER JOIN SIEBEL.S_POSTN T9 ON T1.PR_POSTN_ID = T9.PAR_ROW_ID
          LEFT OUTER JOIN SIEBEL.S_PRI_LST T10 ON T7.CURR_PRI_LST_ID = T10.ROW_ID
          LEFT OUTER JOIN SIEBEL.S_USER T11 ON T9.PR_EMP_ID = T11.PAR_ROW_ID
          LEFT OUTER JOIN SIEBEL.S_STG T12 ON T1.CURR_STG_ID = T12.ROW_ID
          LEFT OUTER JOIN SIEBEL.S_OPTY_TNTX T13 ON T1.ROW_ID = T13.PAR_ROW_ID
          LEFT OUTER JOIN SIEBEL.S_OPTY_DSGN_REG T14 ON T1.ROW_ID = T14.PAR_ROW_ID
          LEFT OUTER JOIN SIEBEL.S_POSTN T15 ON T2.POSITION_ID = T15.PAR_ROW_ID
         LEFT OUTER JOIN SIEBEL.S_USER T16 ON T15.PR_EMP_ID = T16.PAR_ROW_ID
          LEFT OUTER JOIN SIEBEL.S_REVN T17 ON T1.SUM_REVN_ITEM_ID = T17.ROW_ID
          LEFT OUTER JOIN SIEBEL.S_PARTY T18 ON T1.PR_CON_ID = T18.ROW_ID
          LEFT OUTER JOIN SIEBEL.S_CONTACT T19 ON T1.PR_CON_ID = T19.PAR_ROW_ID
          LEFT OUTER JOIN SIEBEL.S_SRC T20 ON T1.PR_SRC_ID = T20.ROW_ID
   ORDER BY
      T17.EFFECTIVE_DT DESC.

If you analyze the last part of the SQL, there is no “WHERE” clause with a search specification , nor are there and bind variables. This query will simply return all records present in the Opportunity Business Component. That’s right, its an empty query on the S_OPTY table and all other tables left joined. And this is fired every time the InsertRecord method is fired. I think Siebel tries to see if the record going to be inserted is not a duplicate of an existing record in the system, and it does this by first firing and empty query and then comparing the result with what we are trying to insert. But as the number of records in the tables grow, the performance degrades. And this is a vanilla/OOTB business service.

Anyway, we had to do away with “FINS CAP Buscomp Handler:InsertRecord” and replaced it with another vanilla BS: “Inbound E-mail Database Operations” Business Service and its “InsertRecord” method. The syntax is not exactly same, some modifications are required. But after using this BS, we found a tremendous improvement in speed in the system.

Reddit's database has only two tables

Steve Huffman talks about Reddit’s approach to data storage in a High Scalability post from 2010. I was surprised to learn that they only have two tables in their database.

Lesson: Don’t worry about the schema.
[Reddit] used to spend a lot of time worrying about the database, keeping everthing nice and normalized. You shouldn’t have to worry about the database. Schema updates are very slow when you get bigger. Adding a column to 10 million rows takes locks and doesn’t work. They used replication for backup and for scaling. Schema updates and maintaining replication is a pain. They would have to restart replication and could go a day without backups. Deployments are a pain because you have to orchestrate how new software and new database upgrades happen together.
Instead, they keep a Thing Table and a Data Table. Everything in Reddit is a Thing: users, links, comments, subreddits, awards, etc. Things keep common attribute like up/down votes, a type, and creation date. The Data table has three columns: thing id, key, value. There’s a row for every attribute. There’s a row for title, url, author, spam votes, etc. When they add new features they didn’t have to worry about the database anymore. They didn’t have to add new tables for new things or worry about upgrades. Easier for development, deployment, maintenance.
The price is you can’t use cool relational features. There are no joins in the database and you must manually enforce consistency. No joins means it’s really easy to distribute data to different machines. You don’t have to worry about foreign keys are doing joins or how to split the data up. Worked out really well. Worries of using a relational database are a thing of the past.

This fits with a piece I read the other day about how MongoDB has high adoption for small projects because it lets you just start storing things, without worrying about what the schema or indexes need to be. Reddit’s approach lets them easily add more data to existing objects, without the pain of schema updates or database pivots.

.

What is sfsutl, How Should SFSUTL.EXE be Used?

sfsutl.exe can be found in Siebsrvr\bin directory, in siebel versions 6 and older SFSUTL.EXE used to delte the orphan and old versions of attachments.

in Siebel 7 and Siebel 8 a new utility called SFSCLEANUP has been introduced.
sfsutl can be used to verify the integrity between database records and files in the Siebel File System, or to remove files from the Siebel File System that no longer have a corresponding database record.
SFSUTL works in two modes:

1. verify or report

2.move or delete.
1.Verify or Report Mode:
To use the utility in verify or report mode, enter a command string similar to the following:
sfsutl /u sadmin /p sadmin /c siebsrvr_siebel /d dbo /f e:\siebfile /x c:\sfsutl_output.txt
The output from this command could be easily imported into Microsoft utilities like Excel or Access for sorting and manipulation. See below for sample output from this command. There are three key statuses to note:
•Keeping - The file corresponds to a database record.
•Discard - The file does not correspond to a database record, and would be moved or deleted if sfsutl were run in that mode.
•Missing - An expected file does not exist for a database record.
2.Move or Delete Mode:
To use the utility in move or delete mode:
1.Create a separate or new folder (this will be used by sfsutl when moving the discarded files). It is recommended that users do not create this new folder as a subfolder of the existing Siebel File System. For example, if the Siebel File System is in e:\siebfile then create the new folder as e:\siebfilediscards.
2.Enter a command string similar to the following:
sfsutl /u sadmin /p sadmin /c siebsrvr_siebel /d dbo /f e:\siebfile /m e:\siebfilediscards /x c:\sfsutl_output.txt