Showing posts with label Siebel. Show all posts
Showing posts with label Siebel. Show all posts

Tuesday, April 4, 2017

OpenUI , I get it now

 

After years of questioning it , I am starting to understand the reason why people are crazy about OpenUI.  For decades, the crude activex based frontend was a big pain area in Siebel. Customers were always using an older, outdated version of Internet Explorer for Siebel CRM, because the latest version never supported it. There was also that crazy 32bit, 64bit IE version problem.

So when the move to a open standards based frontend was announced, it was welcomed with open arms. No more IE. Any browser, any device. But  then started the crazy ideas, crazy configurations, outrageous themes. People started implementing all their business processes purely in OpenUI ! I have stayed away fro this kind of work, for a long time.

But for the past 6 months, I have been working on pure OpenUI requirements. For integration. Yes, Siebel EAI integrations triggered from OpenUI ! Only reason I agreed to it, was because that was exactly what my customer wanted. And the challenge was too sweet to ignore. Javascript is a horrible language, but with OpenUI, it opens up possibilites endless.  Whatever restriciton Siebel puts up to you, can be bypassed in Javascript. I was able to implement a purely asynchronous VBC like applet in OpenUI, something which is not possible using traditional Siebel code.

But OpenUI works poses its own challenges. Unlike tools, there is no object locking system, so better have some kind of CVS in place  if you have multiple people working on OpeUI files. Also, test in every browser. Do not assume that IE or Edge will honour the code the same way Firefox does. Test your code specially with the  Show More button on the applet (tip),  because Show More has lots of issues in OpenUI.

And finally, Siebel still provides the best architecture for a CRM system. Try to implement whatever you can OOB, and OpenUI should be the last option. Unless you want to get into maintenance hell.

 

Friday, December 30, 2016

Back to Siebel



I’ve come to understand that Oracle Cloud is a joke. At least for the time being, it can only be used for medium sized companies who have standard sales and service business processes. The amount of extensibility available is very less. And although one can write their own customization on Oracle PaaS and Java Clouds, that kind of beats the purpose anyway. The whole point of modern cloud is to reduce the amount of code written.

I’m hoping to ride out this last wave of Siebel projects before I too am forced to move to the cloud for good. 

For the time being, it’s good to be back. Happy New Year !




Tuesday, August 16, 2016

Importing WSDLs with cyclic references into Siebel


Siebel has some issues consuming WSDLs which had a little complicated schema structure. Like cyclic references. That is when one of the children elements of a complextype is the same complextype again.

In this example schema, element "LineItemARBOType" has a child of type "LineItemARBOType" again.
cleartext.blogspot.com
image
This WSDLs poses no problem when consumed into a programming language like .NET or Java, because the parser simply creates a class and references itself in it. But these kind of WSDLs were not supported in Siebel. Trying to import a WSDL with cyclic references would result in this error from tools.:
There are no web service definitions to be generated.(SBL-EAI-04340)
Following are warnings generated in the process:
Service 'xx' can not be imported, because none of its ports could be imported.(SBL-EAI-04333)
Port 'xx' can not be imported. PortType 'xx' can not be imported. The http://schemas.xmlsoap.org/wsdl/:operation 'xx' was ignored. Error importing XML schema via method 'xx' for 'element' 'xx' in 'xx'(SBL-EAI-08009)
Cycle detected in the 'xx' schema: xx 'xx' has a cycle (SBL-EAI-09008).
(SBL-EAI-04331).


But turns out, Oracle has fixed this issue in IP2014 (8.1.1.14) release of Siebel. Via bug:Bug 10539615. WSDLs with cyclic references can be consumed into tools IP2014 and upwards. But it creates hundred of integration component entries (sometimes even thousands).
cleartext.blogspot.com

Friday, April 8, 2016

Oracle Sales Cloud: Getting around OSC’s WSDL parsing , Siebel UCM

 

Recently working on getting Oracle Sales Cloud integrated to Siebel UCM for Accounts. Oracle Sales Cloud can read a WSDL specification from a URL, but you still have to build the request message using Groovy script (would be nice to shoot whoever designed this). So you have to use Groovy script and write a global function to build up the message. Turns out, you can only add elements and attributes to the message which is already in the parsed WSDL. Nothing new can be added. © 2016 cleartext.blogspot.com

The problem here is that for integrations to Siebel UCM, a hidden attribute named ExternalSystemId has to be populated in the incoming message. This attribute is not in the WSDL when it gets generated from Siebel UCM. But it has to be sent in the SOAP request (would also be nice to shoot whoever designed this).  © 2016 cleartext.blogspot.com

Error invoking service 'UCM Transaction Manager', method 'SOAPExecute' at step 'Transaction Manager'.(SBL-BPR-00162)
--
<?> Failed to find ExternalSystemId in input message(SBL-IAI-00436)

 

This is what you get when you consume the UCM WSDL in SOAP UI.

image

The actual message has to be (see highlighted changes) © 2016 cleartext.blogspot.com

image

 

If you add the groovy script to add this attribute, OSC will simply ignore it, and the attribute is not send to UCM. The only viable workaround is to Edit the WSDL AFTER it is generated, but BEFORE it is given to Sales Cloud !  © 2016 cleartext.blogspot.com

1: Generate the WSDL from UCM.

2: Open it in an XML editor , use XMLSPY if you have it. © 2016 cleartext.blogspot.com

3: Find the definition of the top container element int the WSDL: © 2016 cleartext.blogspot.com

image

4: Add this text (highlighted) : © 2016 cleartext.blogspot.com

image

<xsd:attribute name="ExternalSystemId" type="xsd:string"/>

5: Now validate, save and upload this WSDL to your public folder from where OSC can read it. OSC does not consume WSDLs, but reads the definition on the fly. © 2016 cleartext.blogspot.com

6: Now add the groovy script to populate this new attribute with the registered SystemId Name.

 

Phew !!

image

© 2016 cleartext.blogspot.com© 2016 cleartext.blogspot.com

Wednesday, November 25, 2015

Siebel and Stackoverflow woes

 

Ok, we are nearing the end of another year, and there's one more month till Christmas. I have now fully moved out of Siebel, and embraced the new family of Oracle Fusion cloud applications. But I do keep track of what happens in the Siebel world. If things finally start to fix themselves (that would be a big miracle), Siebel might make a come back. I am a member of various forums where they track Siebel queries, and one of them is stackoverflow. In some of my previous organizations, I could not even access this site, due to firewall restrictions, but time and again the resources on the site have helped my own project problems. Though I have not yet asked any question on the site, just answered other's queries, and read what other's have left there. I have not asked direct questions because I want to solve my problems in my own capacity, Maybe its my ego, but I won't put up my requirement on the page and wait for some one else to solve it for me at no cost at all.

tmpB72D

But that hasn't stopped others. And following the questions on the site gives you a good idea of what issues other Siebel developers are struggling with. At the moment of writing, these are the stats on the Siebel tag at stackoverflow.

tmp516F

Two years ago, there was a lot of activity around this tag, people were actively asking for and receiving help, the range of topics was wide. Here is a screenshot of the site from the waybackmachine from the start of this year.

tmpF712

Yep, back then, all questions were answered and there was more activity. But now the tag attracts weird questions mainly from css and jquery , because Siebel now uses OpenUI. There appears to be a bunch of noobs with only some basic Siebel training, who visit the site to mainly put up their own requirements, then wait patiently for someone else to solve it for them.

Like this guy:

tmpB41A

Or this classical one which is a link issue, and  which is even documented supportweb.

tmp27A

Clearly, the Siebel pool of resources is decreasing in size. And its the same trend everywhere. Well, Siebel was always closed technology, at times the only place you could get help was on the Siebel support centre, specially about 10 years ago.

Others too have noticed the decline of activity on IT Toolbox and have created a new Siebel page on Slack.  Good old Neel of SiebelUnleashed fame too has abandoned ship. In a very candid and frank interview, he clearly speaks out the writing which has been on the wall for years.

 

"Siebel jobs are going to be there for a long time but unfortunately I don't see many new implementation of Siebel."

"People are sticking to Siebel either because they are heavily invested or they believe other products don't offer what they need."

tmpA6C7

 

So I am still (and thousands more) waiting for that miracle to bring Siebel consultants back in demand.

Anyway, back to Stackoverflow. I was pondering on these questions when I ran into a fantastic article about how stackoverflow may be the best and worst part of programming today. According to their stats, only 27% of question remain unanswered.

 

tmp7D2A

Wednesday, June 10, 2015

Siebel IP 2016 - No More High Interactivity

 

This is from Siebel Innovation Pack 2016: Client Platform Support - Statement of Direction (Doc ID 2017902.1)

tmp2540

 

 

Also it adds:

 

tmpBDD8

Saturday, January 10, 2015

Siebel BIP Performance Tuning

 

1) Increasing the amount of heap memory available to the Java Virtual Machine (JVM)

Increasing the memory available to the JVM can improve the performance of the report generation and ensure that this error is not encountered.

"C:\OraHome_1\jdk\bin\java" -XX:MaxPermSize=128m -Xms512m -Xmx512m -Duser.language=en -Duser.dir=C:\OraHome_1\oc4j_bi\bin -Duser.country=US -jar "%OC4J_JAR%" %CMDARGS%

OS

Memory Value

Windows (32-Bit)

Any value of up to 1.4GB (based on available RAM)

Windows (64-Bit)   

1.4 GB or higher (based on available RAM

2) Changing the Server Request Processor DB Polling Interval

By decreasing the polling interval the Server Request Processor can check the S_SRM_REQUEST table more regularly thereby reducing the period before the request processing is started. Once the change has been made it is necessary to shutdown and restart the affected Siebel Server before the change will take effect.It is recommended that this change be made only on the Siebel Servers hosting the XMLPReportServer component as this change will result in an increased number of queries being issued against the S_SRM_REQUEST table in the database.

change param PollIntvl=1 for comp SRProc

3) Minimize the size of Integration Objects/Components

An excessive number of unrequired fields in the report will lead to increased data generation times from the XMLPReportServer component and will result in an increased data file size for BI Publisher to process.

4) Minimal Force Active fields on  BC-

Ensuring that a minimal number of fields are flagged as Force Active will help to ensure that the number of fields being returned when querying against the Integration Component for report generation is further reduced.

5) XMLPReportServer PreloadSRF parameter to TRUE

Prelolading the SRF into the process ensures that any performance overhead during the SRF load is encountered during component startup and not during report processing. This parameter is listed under Advanced tab.

6) Inactive Web Services not used in Enterprise

Due to Bug 10587893 at present the component will load all of the active webservices configured in the enterprise rather than just those which it may access, such as PublicReportService or PublicReportService_v11. This leads to a performance overhead during the first report request for any process as these can take a significant period of time to load. In order to reduce the impact of this loading process review the webservices configured as 'Active' in the Siebel Enterprise through the Administration - WebServices > Outbound Webservices and Inbound Webservices views.  Set any webservices which are not used in the enterprise to 'Inactive'..

7) Minimize logging levels on all report generation components

This will include the requesting Object Manager, the XMLPReportServer component, the EAI Object Manager, the File System Manager component, and the BI Publisher Server itself.

8) Eliminate any un-necessary scripting during report generation

 

9) Create Separate Named Datasource For Siebel BI Publisher Reports With Large Data Volume

Generating Large Reports (over 10,000 records) - When running report generation there may be scenarios in which more than 10,000 records need to be retrieved. The standard ServerDataSrc within the Siebel Enterprise has a row limit of 10,000 for any single query and therefore in order to support large report generation a custom data source is required without this restriction.

Wednesday, January 7, 2015

IP 2014 - Aurora theme

 

 

This is my reaction on seeing the new Aurora theme in Siebel IP 2014.

mark-13

Friday, January 2, 2015

Siebel Quizzes !

 

If you haven't already checked out SiebelHub.com, I suggest you head there right now. Specially their Quiz section. Its the best place to brush up your brain cells, and learn of the latest happening in the Siebel world. You will need a linkedin account to participate.

Good Luck Quizzing !

Thursday, November 20, 2014

On Siebel Tools Wizards

 

The other day I was debugging a business component configuration to identify the cause of wrong data appearing in the UI, as well as a degradation in performance. After spooling the SQL, I found the reason was that some Multi-Value-Fields were configured incorrectly. I showed the developer an easier and more accurate way of doing configuring MVFs, turns out he or no-one in the team knew about Siebel Tool's many wizards.

 

To add an MVF to , say the Service Request BC:

1: Lock the BusComp, and right click.

 FADV Go Live Test 032

2: Choose the "New MVG" option from the menu:

FADV Go Live Test 033

3: Now you just need to follow the Wizard. Enter the Detail Business Component name and name for the MVL:

FADV Go Live Test 035

4: The link has to be configured ahead and should be available in the next screen. Here I am adding a BusComp for BIP reporting purpose. There is a small bug in this Wizard, it shows the caption of Link's SearchSpec and SortSpec are swapped. so that Sortspec is actually the link searchspec.

FADV Go Live Test 037

If there is no direct link between the entities, the Wizard will allow you to use indirect links, where you have to specify the Source field from the Master Business Component

image

 

5: The next screen is the most important, it allows setting of Primary options in the MVL:

FADV Go Live Test 038

6: Further configuration in the link, since this is a reporting link, I have made it read only by setting NoInsert,NoUpdate and NoDelete to true.

FADV Go Live Test 039

7: Now the MVL is configured, you can start configuring which all fields from the Detail Business Component should appear in the Master Business Component, in this case, Service Request:

FADV Go Live Test 040

8: That's it !

FADV Go Live Test 041

9: On Clicking Finish, Tools will start another Wizard to help you configure the MVG applets. After the first screen , the rest is similar to the Applet Wizard.

image

 

 

 

 

When I need to find in which applets a certain field is exposed, I use the similar PickList Wizard to find that out quickly.

1: For instance, to see all applets where the "Abstract" field of Service Request BC is exposed, simply right click on the field and choose "Add Picklist"

image

 

2:  The first screen of the Wizard shows all the applets where this field is exposed:

image

3: Nope, the list cannot be exposed from the wizard, you can only see the applet names. But if there are too many applets, I use Sys Exporter (that's a bad name for useful software) to export out the list into CSV:

image

 

There are rumours that in future releases, Oracle might remove Siebel Tools completely, I just hope they retain all those wizards to speedup development.

Wednesday, October 15, 2014

Siebel public facing applications SSL SHA1 – SHA 2 encryption message changes

 

Microsoft and Google announced SHA-1 deprecation plans that may affect websites with SHA-1 certificates expiring as early as after December 31, 2015.

As per oracle there is no plan to support SHA-2 before 2016 that is Innovation pack 2015.

According to Google’s blog on “Gradually Sunsetting SHA-1”, Chrome version 39 and later will display visual security indicators on sites with SHA-1 SSL certificates with validity beyond January 1, 2016. The production release of Chrome 39 is expected to be in November, 2014. The sites will be treated with one of the following indicators: “secure, but with minor errors” (lock with yellow triangle), “neutral, lacking security” (blank page icon) and “affirmative insecure” (lock with a red X). In order to prevent online users on Chrome version 39 and later from experiencing these indicators, SHA-1 SSL certificates expiring after December 31, 2015 must be replaced with SHA-256 (SHA-2) certificates.

Microsoft’s SHA-1 deprecation plan differs in the activation time and browser behavior. Microsoft’s security advisory on “Deprecation of SHA-1 Hashing Algorithm for Microsoft Root Certificate Program” informed us that Windows will cease accepting SHA-1 SSL certificates on January 1, 2017. To continue to work with Microsoft platforms, all SHA-1 SSL certificates issued before or after this announcement must be replaced with a SHA-2 equivalent by January 1, 2017.

 

https://www.symantec.com/page.jsp?id=sha2-transition

http://blog.chromium.org/2014/09/gradually-sunsetting-sha-1.html

https://technet.microsoft.com/library/security/2880823

Thursday, August 28, 2014

Quote Catalog not Loading

 

We faced a peculiar issue on the Quote Catalog view , Siebel version 8.1.1.11 SIA [23030].

When users went to the Quote Catalog view, which is where one starts adding products to the quote for the Pricing Engine, they noticed the Catalog did not load. This happened usually on the the first quote after login.

image

But once the Price List of the Quote was selected, or reselected, and the view was refreshed, the Catalog Items would start appearing.

The Business Component is [Quote Catalog Internal Product by Price List Optional] and it has a user property [SetPriceListSearch] set to ‘Y’. A simple search brings the bookshelf where it is mentioned that

‘This user property indicates whether the [Price List Id] = GetProfileAttr("PriceListId") search specification is applied to the business component. By default, it is set to Y, so only products in the current price list are displayed.’

Turns out, this is one of those many vanilla Profile Attributes managed by Siebel itself. It is set  to the Quote’s Price List’s Id when a Price List value is selected in the Quote. Otherwise, it is blank.

Try out this in the browser ui: javascript:alert(theApplication().GetProfileAttr("PriceListId"));

image

So the issue happens only for the first Quote selected by the user after login, if he has not changed the PriceList value of the Quote. The profile attribute is not set, even if there is already a Price List value at the Quote.

Inorder to fix this, I added a simple RunTimeEvent on the QuoteForm applets’ DisplayApplet event.

 

image

Action Set:

image

This would set the value of this ProfileAttribute when the Quote applet is loaded. With this fix in place, the Catalog items now appear correctly.

image

Trying out : javascript:alert(theApplication().GetProfileAttr("PriceListId"));

image

The other way to fix this is to disable the BC User property, so that items from every Price list is visible. But this would depend on your business use case.

Friday, June 13, 2014

Oracle’s letter to Siebel Customers

 

Oracle today sent out this e-mail to their Siebel Customers.

 

image

Wednesday, June 4, 2014

Siebel Log files - Top Errors

 

I am working on a brand new Siebel implementation, and we are seeing a lot of errors..Really. Huge log files with repeating errors on them. If you increase the Siebel component logging to maximum, Siebel will spit out Warnings to Errors, and everything in between. It becomes difficult to find out where the actual problem is.

I figured one way is to find out the most commonly occurring errors. Fortunately, we are on a Linux server system. So a little bit of googling, and using linux’s many commands solved the problem.

Running this command in the ses/siebsrvr/enterprises/*/logs folder prints the top occuring errors in the last 10 log files in reverse order:

grep "SBL-" `ls –tr *.log | tail -10`   | grep -v "Warning" | grep -v ".spf" | grep -v 'err=' | cut -f 6-10 | uniq -c | sort  –n

image

Here is a screenshot of errors for me. The command ignores Siebel Warnings, and errors related to .spf files, and also some errors from source files.

Hope it helps !

And what about Windows you ask ? Well, Windows guys are on their own. You can download windows ports of Unix commands.

Wednesday, January 1, 2014

BIP - Siebel functions

 

If you use Siebel functions in your BIP reports for date conversion, you will get “class-not-found” errors during your report generation.

 

image

 

The fix for this is to point BIP to the path where your Siebel JARs are installed.

In your Work designer: Go to Addins-> Tools –> Options, and add a the path to the Java option field, under Preview.

For me, Siebel is installed on D drive.

-Xmx256M  -Xbootclasspath/a:D:\Siebel\8.1\Tools_1\CLASSES\SiebelXMLP.jar;D:\Siebel\8.1\Tools_1\CLASSES\XMLP.jar;D:\Siebel\8.1\Tools_1\CLASSES\siebel.jar;D:\Siebel\8.1\Tools_1\CLASSES\XSLFunctions.jar;D:\Siebel\8.1\Tools_1\CLASSES\SiebelCustomXMLP.jar;D:\Siebel\8.1\Tools_1\CLASSES\SiebelCustomXMLP_SIA.jar

 

image

Tuesday, October 1, 2013

Take a shot at the Siebel 20th Anniversary Quiz

 

The good folks at On-Demand education have setup an online Quiz to commemorate the 20th Anniversary of Siebel. There competition seems fierce, till last week I saw only 30 names on the leaderboard, now it’s double that.  The questions are from everywhere, technical and functional Siebel thingies and some history.  You can find some of the answers of the questions on @lex’s awesome Siebel site, and he was involved in setting up of the questions.

image

The page is still open, but the correct answers are not disclosed. So go ahead and give it a try !

Siebel Anniversary Quiz

Friday, November 30, 2012

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.

image

The picklist is bounded

image

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

image

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.

image

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.

Sunday, November 11, 2012

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");
}

Sunday, September 23, 2012

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.

Saturday, July 7, 2012

Tools of the trade


Five years of Siebel EAI development, and I am still learning every day. One thing I quickly realized was that the Siebel Tools and Application system is not like the contemporary Integrated Development Environment (IDE) used in Java and .NET development. Sure, Tools can solve most of your problems, but every Siebel developer will eventually face a problem which has to be solved using some other tool/utility/script which may have to be written from scratch. Today, I thought I’d share what’s in my arsenal of tools.

Siebel Development

  1. A good text editor is essential for any development need. For years I have been relying on window’s default notepad utility, which is good enough. But it is seriously lacking in features, and hangs when you try to open huge files. I use Notepad2 , Notepad++ and EditPlus. Each has first class CR/LF support, ANSI to Unicode switching, whitespace and line ending graphics and Mouse Wheel Zooming. EditPlus even has ability search and replace special characters (\n,\t) and works nicely with huuge files.
  2. If you work deals with XMLs/XSLs, you will need a good XML/XSLT/WSDL editor. XmlSpy  has been my number one choice, followed by Oxygen. Though I have noticed that there are some minor differences in the XMLSpy XPATH engine between older and newer versions. The XPATH engine in Spy version 6 is similar to Siebel’s own EAI XSLT service. Some of the fine working XSLTs were throwing validation errors in the newer version of Spy, but they worked perfect in Siebel.
  3. SOAP UI. This one deserves a special mention. Not only is it free, it has support for secure webservices , proxies and many other protocols. Its so easy to set up a stub/mockservices, and these have saved my life more than once.
  4. Fiddler. I first used this way back in college, when I wanted to intercept and see the http messages being sent from my system for a project. It’s a free utility from Microsoft, and comes in handy in diagnosing web service related issues. Its got a scripting system, and can be used as mockservice too.
  5. Network tools like PuTTy, WinSCP and FileZilla come here. I also use PuTTY to setup ssh tunnels when I want to route a request via another server.
  6. UnixUtils. A unix developer swears by the huge number of commands at his disposal. Sadly, windows does not provide that many commands, and I thank the geeks for porting these awesome Unix commands over to Win32. These allow me to build bat files and scripts to solve recurring issues.
  7. Although UnixUtils has windows port of grep and tail commands, the UI of BareGrep and BareTail and much more developer friendly.
  8. Oracle SQLDeveloper, TOra and my old favorite, TOAD. You can’t be a Siebel developer without knowing SQL !
  9. I’m sure all you Siebel developers have been in this situation: You have list of data you need to query in Siebel :
val1
val2
val3

The searchspec you need to build is : “val OR val2 OR val3” . Well, that’s all that Orit does. Frankly, I don’t know who developed this utility, but it is something developed by a Siebel developer. All it does is simply concatenate column wise data with ‘OR’ in between.

Other Siebel Things I found on the net

Its sad that Siebel does not provide an Out-of-box documentation feature, which can be used during and development. This is just one of the many problems I have faced.Nice thing is others have faced the same problem, and developed their own solutions for the problem.
  1. The good folks at the Only Siebel blog have released Excel Macro tools which can pull config data for documentation.
  2. Oli has graciously released a lot of his tools for free download. There are script analysers, data loaders and Tracer Tools.
  3. Some useful utilities at the website of Sea Marvel Tech Solutions
  4. Wait…one more utility for Siebel Documentation here.
  5. Here is my own attempt at making an SQL Tracer.
  6. Siebel provides dbisqlc to connect and run SQL statements on your localdb. But I find it painful when there are too many columns in the output, I have to scroll to see the data, and export. Sel2XL is a nice excel macro which does the same thing, and it allows excel formatting.
  7. Sometimes, the easiest way out is direct SQL into the the database. There are SQLs for siebel issues, EIM Mappings, for UI mappings, and so many other issues.

General Use

  1. Window’s default search feature can get irritatingly slow, specially if you need to search across drives and have to run complex search patters. Everything is an awesome ,high speed ,instant search utility for the NTFS file system. You have to try it to see the magic. And did I mention it is free ?
  2. Teracopy is the default copy utility on my systems. Sure, the latest multicopy feature on Windows 7 & 8 are cool, but I still feel Teracopy’s interface is better.
  3. Free Download Manager. For all those loong downloads.
  4. Microsft’s Virtual PC. I use this to setup virtual machines for my Siebel POCs. Its free and no fuss to setup and use. This new version of Windows Virtual PC lets you run Windows XP applications next to your Windows 7 apps for the ultimate in backward-compatibility.
  5. I have been using Liberkey suite since they came out some years ago. They have a free utility for all your day to day needs. NirSoft Utilities Collection is also a good choice.
  6. Autohotkey, DoItAgain. Ultimate Windows Automation.