The Siebel Scholar

Workflow Monitoring - On Demand

noreply@blogger.com (Mik) — Thu, 20 Jun 2019 18:27:00 +0000

The out of the box Workflow Monitoring level specified when deploying a version of a workflow is a great tool but it requires it having been explicitly turned on in order to be of value. I have found that while this was turned on for all users in a production environment, we would have a massive surplus of log data that would fill up the logging tables unnecessarily, some users logins may be setup to only do troubleshooting. Alternatively, a specific user could have logging turned up for so that all workflows are tracked rather than having to know all the workflow process names where an error is going to occur as the user may not know before hand which workflow is causing the problem. What is needed is to add logic to the 'Workflow Process Manager' business service. The PreInvoke needs the following script added:

function Service_PreInvokeMethod (MethodName, Inputs, Outputs) {
  if (MethodName.indexOf("Run") >=0) {
    TheApplication().Utility.logRaiseWF(Inputs, MethodName);
  }
  return (ContinueOperation);
}

The Invoke needs the following script added:

function Service_InvokeMethod (MethodName, Inputs, Outputs) {
  if (MethodName.indexOf("Run") >=0) {
    TheApplication().Utility.logResetWF(Inputs, MethodName);
  }
}

The Utility service is enabled in the Application start event. The 'XXX Utilities' business service uses two additional methods.

function logRaiseWF(Inputs, callingMethod) {
// Method called from Workflow Process Manager, PreInvoke method for methods having 'Run' in the method name if the
// Utilities Service is enabled for the OM Component the session is running within.

  //If The Troubleshoot process property is passed (when invoking from a Named Method user prop) or as a child prop (when
  //invoked from a WF as a subprocess) or the user's log level is 3 or higher, temporarily set WF monitoring for the WF
  //process to detail.  logResetWF is called immediately after invokation to turn WF monitoring off.
  if (gCurrentLogLvl >= 3 || 
   Inputs.GetProperty("Troubleshoot") == "Y" || 
   (Inputs.GetChildCount() > 0 && Inputs.GetChild(0).GetProperty("Troubleshoot") == "Y")) { 
    if (TheApplication().GetProfileAttr("IsStandaloneWebClient") == "TRUE") {
      logStep("PPT Utilities.logRaiseWF.callingMethod: "+callingMethod+" - ProcessName: "+Inputs.GetProperty("ProcessName")); 
      logPS(Inputs);
    } else {
      logSetWFLevel(Inputs, "3 - Detail");
    }
  }

  //The first WF Called in a stack uses the RunProcess method.  If subprocess is called from a WF, the _ method is used. 
  //All payloads captured after the initial RunProcess call are stored in an array and dumped if an error occurs.  If 
  //system preference 'PPT Hold Buffer Max' is greater than 0 then the array is instead always kept at that count rather
  //than resetting on WF start
  if (gsTraceIntfaceReqResp != "FALSE" && callingMethod.indexOf("Run")== 0 && gHoldBufferMax == 0) gHoldReqResp = [];
}

function logResetWF(Inputs, callingMethod) {
// Method called from Workflow Process Manager, Invoke method for methods having 'Run' in the method name if the
// Utilities Service is enabled for the OM Component the session is running within.

  //Conditions controlling whether to reset WF monitoring level must mirror those in the logRaiseWF function
  if (gCurrentLogLvl >= 3 || 
   Inputs.GetProperty("Troubleshoot") == "Y" || 
   (Inputs.GetChildCount() > 0 && Inputs.GetChild(0).GetProperty("Troubleshoot") == "Y")) { 
    if (TheApplication().GetProfileAttr("IsStandaloneWebClient") == "TRUE") {
      logStep("PPT Utilities.logResetWF.callingMethod: "+callingMethod+" - ProcessName: "+Inputs.GetProperty("ProcessName")); 
    } else {
      logSetWFLevel(Inputs, "0 - None");
    }
  }
}

Finally, these wrapper functions call a function to actually set and reset the monitoring level on a deployed workflow:

function logSetWFLevel (Inputs, logLevel) {
//Use:  
//Returns: 
  var boWF:BusObject = TheApplication().GetBusObject("PPT Workflow Process Deployment");
  var bcWF:BusComp = boWF.GetBusComp("Workflow Process Deployment");
  var found:Boolean = false;
  var propName = Inputs.GetFirstProperty();

  while (propName != "") {
    if (propName.indexOf("ProcessName") == 0) {
      with (bcWF) {
        ActivateField("Monitoring Level");
        ClearToQuery();
        SetSearchSpec("Name", Inputs.GetProperty(propName));
        SetSearchSpec("Deployment Status", "Active");
        ExecuteQuery(ForwardOnly);
        found = FirstRecord();
        if (found) {
          SetFieldValue("Monitoring Level", logLevel);
          WriteRecord();
        }
      }     
    }
    propName = Inputs.GetNextProperty();
  }
}

To minimize the times where logging is actually raised, one of the following conditions must be true:

An input process property called 'Troubleshoot' has a 'Y' value. I use this process prop across most of my workflows and only pass the 'Y' value when passed as a command/Named Method BC User property from a button on an admin view or an applet gear menu option. It is useful to expose manually triggering a workflow this way to replicate a process that is normally triggered by the system in some way.
Users log level is 3 or higher

Note that if using the thick client, logging is NOT turned on so minimize SQL in the siebel.log file and since this information can easily be set through the SIEBEL_LOG_EVENTS system variable along with the Step and Process execution events.

XML Logger - EAI Data Transformation Engine

noreply@blogger.com (Mik) — Thu, 20 Jun 2019 17:37:00 +0000

If you are already capturing the XML payloads of a web service using the XML Logger, then extending it to further troubleshoot how you might have ended up with that payload might be useful since integration workflows frequently undergo multiple transformations. The existing logRequest and logResponse as invoked by the PreInvoke method of the 'XXX Utilities' business service are central to the actual logic. What is needed now is to add logic to the 'EAI Data Transformation Engine' business service. The PreInvoke needs the following script added:

function Service_PreInvokeMethod (MethodName, Inputs, Outputs){
  if (MethodName=="Execute") {
    TheApplication().Utility.InvokeMethod("logTransformRequest", Inputs, TheApplication().NewPropertySet());
  } 
  return (ContinueOperation);
}

The Invoke needs the following script added:

function Service_InvokeMethod (MethodName, Inputs, Outputs) {
  if (MethodName=="Execute") {
    TheApplication().Utility.InvokeMethod("logTransformResponse", Outputs, TheApplication().NewPropertySet());
  } 
}

The logRequest and logResponse methods set the direction attribute to 'EAI Transform' to distinguish it and uses the MapName attribute as the functional name. The Record Id is assumed to be an element called 'Id' in the first top level container.

I made a personal decision that logging these payloads was not universally necessary and therefore wanted to make logging these records conditional. In my case I made it run in two scenarios:

If the executing user's log level is 5 and payload logging is enabled
If the payload logging is not generally enabled but an error occurred subsequently in the executing workflow of a subprocess it called.

Get Process and Thread Ids

noreply@blogger.com (Mik) — Thu, 20 Jun 2019 17:18:00 +0000

It is sometimes useful to know the PID or Thread. For instance if an error occurs and I want to get the specific server OM Log file I can find that file if I knew the Thread and PID. I am not aware of any way to get these values directly though it is obvious the Siebel application OM has these values internally. The TraceOn function allows these values to be used while opening and naming the trace file:

TheApplication().TraceOn("TraceFile_$p_$t.log", "Allocation", "All");

results in name like

TraceFile_7382_8188.log

So the trick is to create this file then read the relevant values out of the name. To do so, I create the file with a unique name that will be known to the script creating it:

var unique = TheApplication().LoginName()+"-"+TimeStamp("DateTimeMilli");
TheApplication().TraceOn(path+"Trace-"+unique+"_$p_$t.log", "Allocation", "All");

To get the values I am interested in, I need to output the directory listing of only this known file to a log I can then open and read, then use format of the name of this file to extract the two values I am interested in:

function SetThreadPID() {
  var pid = TheApplication().GetProfileAttr("XXX OS PID");
  var threadId = TheApplication().GetProfileAttr("PPT OS Thread ID");
  var line, pidThread, path, command, outs;

  try {
    if (threadId == "") {
      if (TheApplication().GetProfileAttr("IsStandaloneWebClient") == "TRUE") {
        path = gsLogPath;
      } else {
        path = TheApplication().GetProfileAttr("Syspref Error Trace Temp Loc");
      }

      if (path != "" && path.toUpperCase() != "FALSE") {
        var unique = TheApplication().LoginName()+"-"+TimeStamp("DateTimeMilli");
        TheApplication().TraceOn(path+"Trace-"+unique+"_$p_$t.log", "Allocation", "All");
        TheApplication().Trace("TEST");
        TheApplication().TraceOff();  
    
        command = "dir "+path+"Trace-"+unique+"_*.log > "+path+"Trace-"+unique+".log";
        outs = Clib.system(command);
        var fp:File = Clib.fopen(path+"Trace-"+unique+".log","r");
        if (fp != null){
          while(Clib.feof(fp) == 0){
            line = Clib.fgets(fp);
            if (line.length > 0){
              if (line.indexOf(unique)>=0) {
                pidThread = line.substring(line.indexOf(unique)+unique.length+1, line.length - 5)
                pid = pidThread.substring(0, pidThread.indexOf("_"));
                threadId = pidThread.substring(pidThread.indexOf("_")+1);
                TheApplication().SetProfileAttr("PPT OS PID", pid);
                TheApplication().SetProfileAttr("PPT OS Thread ID", threadId);
                Clib.fclose(fp);
                outs = Clib.remove(path+"Trace-"+unique+"_"+pid+"_"+threadId+".log")
                outs = Clib.remove(path+"Trace-"+unique+".log")
                break;
              }
            }
          }
        }
      }
    }
  } catch(e) {
    RaiseError(e);
  } finally {
    fp = null;
  }
}

Once the PID and Thread are stored in profile attributes they are available to the business layer to for instance set a PID or thread column on a custom error table.

Note that 'Syspref Error Trace Temp Loc' is a custom field added to the 'Personalization Profile' BC which has the calculation:

SystemPreference("PPT Error Trace Temp Loc")

This is then set to a directory that the Siebel application server has access to (the Siebel temp directory can generally be used safely).

Use Open UI to Dynamiclly Manipulate Detail Tabs

noreply@blogger.com (Mik) — Thu, 20 Jun 2019 15:06:00 +0000

In a screen with many view tabs it may be useful for process automation to minimize clicking on detail tabs if user does not need to navigate there when no records are present. Open UI allows changing the view tab labels to provide indicators to signal to the user information about that tab. In order to do so, join fields or calculations based on MV fields relevant to the child BC need to exist and be exposed as controls on the parent BC applet (they can be hidden).

One additional feature is to hide the view tabs not requiring navigation for UI optimization but keeping them available in case the user needs them. Siebel uses a UI dropdown widget when there are too many views to fit horizontally across. We can leverage this widget to conditionally place additional views based on the values in BC fields.

The following script is attached to a navigation manifest event:

if(typeof(SiebelAppFacade.pptCustomNavigationPR) === "undefined"){ 
  SiebelJS.Namespace("SiebelAppFacade.pptCustomNavigationPR"); 
  define ("siebel/custom/pptCustomNavigationPR", ["siebel/accnavigationphyrender"], function () { 
    SiebelAppFacade.pptCustomNavigationPR = (function(){ 
      var PM; 
      var PRName = ""; 
      function pptCustomNavigationPR(pm){ 
      SiebelAppFacade.pptCustomNavigationPR.superclass.constructor.apply(this,arguments);} 
      SiebelJS.Extend(pptCustomNavigationPR, SiebelAppFacade.AccNavigationPhyRenderer); 
            
      pptCustomNavigationPR.prototype.Init = function() { 
        SiebelAppFacade.pptCustomNavigationPR.superclass.Init.apply(this, arguments); 
        PM = this.GetPM(); 
        PRName = PM.GetPMName(); 
      }; 
      /*pptCustomNavigationPR.prototype.ShowUI = function(){ 
        SiebelAppFacade.pptCustomNavigationPR.superclass.ShowUI.apply(this, arguments); 
        //implement ShowUI method here 
      }; 
      pptCustomNavigationPR.prototype.BindEvents = function(){ 
        SiebelAppFacade.pptCustomNavigationPR.superclass.BindEvents.apply(this, arguments); 
        //implement BindEvents method here 
      };*/
            
      pptCustomNavigationPR.prototype.BindData = function(bRefresh){ 
        SiebelAppFacade.pptCustomNavigationPR.superclass.BindData.call(this, bRefresh); 
                
        //Prototype for child record counter on view tabs 
        if (PRName == "NavigationDetailObject_PM"){ 
          //the framework is processing detail navigation, this is a good place for code that manipulates view tabs. Get applet, control, value and properties 
          //Code assumes the top form applet has a control that exposes a count 
          var oView = SiebelApp.S_App.GetActiveView();
                                  
          if (typeof(oView) != 'undefined' && oView != null && oView.hasOwnProperty("GetName") == true) {
            var sViewName = oView.GetName();
                 
            //Limit execution of this script to only views matching a naming convention as it must have a parent form applet containing the hidden calculated fields
            if ( sViewName.indexOf("XXX Search Text") >= 0 ){
              var suppressTabs = true;

              //This applet must have controls containing the BC fields that will be used in the array
              var applet = oView.GetAppletMap()["XXX Parent Form Applet"];

              //Declared Array where index is UI display name of the detail tab and value is either BC Field Name or '-'.  If field name, non 0/non null value indicate tab should
              //be displayed.  '-' indicates tab should always be hidden.  If tab should always be displayed, do not put it in the array
              var tabList = {"Contacts":"XXX Contact Count","Activities":"XXX Activity Count","Service Requests":"XXX SR Count","Notes":"XXX Notes Flag","Fees":"XXX Fees Flag","Audit Trail":"-"};
              var hitCount, fieldName;
              var tabIndex = 0;
              var tabs = [];
              var tabScreens = [];
              var showWidget = false;
              var lastTab;
      
              //loop through each visible detail tab... 
              $(".siebui-subview-navs .siebui-nav-tabScreen .ui-tabs-nav a").each(function(index){ 
                //get the current tab label text 
                var currentLabel = $(this).text(); 
                  
                //check if tab is in array of labels that need modification
                fieldName = tabList[currentLabel];
                if (typeof(fieldName)!='undefined' && fieldName != ""){  //we found the tab 
                  if (fieldName == "-") hitCount = "";
                  else hitCount = applet.GetBusComp().GetFieldValue(fieldName);

                  //Either modify the label if an indicator needs to be appended or if tab is to be suppressed, add to an array of labels to appear in the option list
                  if (hitCount != "" && hitCount != "0") {
                  //now change the text 
                    $(this).text(currentLabel + " (" + hitCount + ")"); 
                    lastTab = $(this);
                  } else if (suppressTabs) {
                  //If this tab should be generally suppressed, there are no indicators needing to be displayed, and it is not currently selected
                    if (fieldName != "" && (hitCount == "" || hitCount == 0) && $(this).parent().attr("tabindex")!= "0") {
                      showWidget = true;
                      tabs[tabIndex] = currentLabel;
                      tabScreens[tabIndex++] = $(this).attr("data-tabindex").substring(9);
                      $(this).remove();
                    } else {
                      lastTab = $(this);
                    }
                  }   
                }
              }); 
              
              //If any tabs need to be suppressed, display a drop down at the end of the detail tab row with list of view tabs that have been suppressed
              if (showWidget == true) {
                var j=0;
                var htmlstring = lastTab.parent().parent().html();
                var append = '<li><select aria-atomic="true" aria-label="Third" bar="" class="siebui-nav-links siebui-nav-viewlist" id="j_s_vctrl_div_tabScreen" level="" role="combo" view="">==$0<option hidden="" value=""></option>';</select></li>
                while (j < tabIndex) {
                  append = append + '<option value="tabScreen'+tabScreens[j]+'">'+tabs[j++]+'</option>';
                }
                append = append + '</select></li>';
                lastTab.parent().parent().html(htmlstring+append);
              }
            }
          }
        }        
      }; 
              
      return pptCustomNavigationPR; 
    }()); 
    return "SiebelAppFacade.pptCustomNavigationPR"; 
  }); 
}

Thick Client Event Logging

noreply@blogger.com (Mik) — Tue, 08 Mar 2016 17:55:00 +0000

There are surprisingly few blog posts out there about vanilla options for logging in the thick client. Perhaps this is because everyone knows how to do it and if so feel free to ignore this. But perhaps it is because most people just struggle through using inefficient methodologies.

Here is a simple tip for troubleshooting when using the thick client. There is an OS environment variable called SIEBEL_LOG_EVENTS (if it does not exist you can create it). Many developers know how to set this to an integer between 0 and 5, but values of 4 and 5 where good detail is provided create a file that unreasonably large and hard to parse. When troubleshooting on the thin client you can set individual event log levels from the Administration - Server Configuration screen, component events view. You can do the same when using a thick client though you need to do it using the SIEBEL_LOG_EVENTS variable. You can use any combination of event aliases and levels, but the value I have found useful is the following:

StpExec=4,PrcExec=4,ObjMgrSqlLog=4,SQLParseAndExecute=4,ObjMgrBusServiceLog=4,EventContext=4,ProcessRequest=4,ObjMgrDBConnLog=5,SecAdpLog=5,ObjMgrSessionLog=5,ObjMgrBusCompLog=2

Basically you can enter any comma separated list of event aliases.

One coda is that if I were trying to troubleshoot a WF issue, I could open this log and do a find for the word 'Instantiating'. The first instance I would find is the Start step of the WF Process followed by the values of the process properties being set by that step:

PrcExec Create 4 00000002569f1a98:0 2016-01-20 15:31:33 Instantiating process definition 'PPT Passport History Response Integration'.
PrcExec PropSet 4 00000002569f1a98:0 2016-01-20 15:31:33 Setting runtime value of property 'Namespace: 'USER' Name: 'ConfigItem' Datatype: 'String'' to:
PrcExec PropSet 4 00000002569f1a98:0 2016-01-20 15:31:33 Start
PrcExec PropSet 4 00000002569f1a98:0 2016-01-20 15:31:33 Setting runtime value of property 'Namespace: 'USER' Name: 'ObjectName' Datatype: 'String'' to:
PrcExec PropSet 4 00000002569f1a98:0 2016-01-20 15:31:33 Workflow - PPT Test Error Process
PrcExec PropSet 4 00000002569f1a98:0 2016-01-20 15:31:33 Setting runtime value of property 'Namespace: 'USER' Name: 'CurrentStep' Datatype: 'String'' to:
PrcExec PropSet 4 00000002569f1a98:0 2016-01-20 15:31:33 Convert Siebel Message PPH

Subsequent occurrences look like this:

StpExec Create 4 00000002569f1a98:0 2016-01-20 15:31:33 Instantiating step definition 'Start'.
StpExec End 4 00000002569f1a98:0 2016-01-20 15:31:33 Stopping step instance of 'Start' with a 'Completed' status.

In this way you can step through the WF. The advantage of this logging level over say looking at the WF Instance Monitor or only using WF Simulator, is you will be able to see the SQL executed and the bind variables used, what BCs were instantiated along the way, and what BS methods might have been called.

EAI Integration Map expressions

noreply@blogger.com (Mik) — Tue, 09 Feb 2016 13:53:00 +0000

While there are many posts I have seen that talk about expressions supported by 'EAI Data Transformation Engine', I have never seen an attempt to compile a list of supported functions and examples of there uses. So this will be a humble beginning that will hopefully grow over time. Note that these functions are mostly VB so if trying out one that is not listed, start with what is supported in VB. They can also be found in Siebel Bookshelf as Siebel Query Language expressions

Do not include XML element (use System type)
IfNull([Middle Name], [Conflict Id])

Transforming Dates
(from 'YYYYMMDD' to 'MM/DD/YYYY'):
Right(Left([Source Field Name],7),2) +"/"+Right([Source Field Name], 2)+"/"+Left([Source Field Name], 4)

(from Siebel Date to externally recognized format):
ToChar([Birth Date], 'YYYY-MM-DD')
ToChar([Completion Date], 'YYYY-MM-DDThh:mm:ss')

Conditional Logic:
IIF([Source Field Name] = "false", "N", "Y")

SSN formatting (Strip hyphens):
IIF(InStr([PPT Social Security Number], "-") > 0, Left([PPT Social Security Number], 3)+Mid([PPT Social Security Number], 5, 2)+Right([PPT Social Security Number], 4), [PPT Social Security Number])

EAI Lookup for an Inbound Interface:

EAILookupSiebel("XXX",[Source Field Name])

XXX is the Type in the EAI Lookup table. This needs also needs to be setup as a value under the EAI_LOOKUP_MAP_TYPE LOV type.

IIF([Source Field Name] IS NULL, "", EAILookupSiebel("XXX",[Source Field Name]))

EAILookupSiebel fails if no value is found so minimize this possibility unless an exception is desired

Extract the file name from a File Path:
Mid([Source Field Name], InStr([Source Field Name], "/", -1) + 1)

Interesting Web Service approach

noreply@blogger.com (Mik) — Thu, 22 Oct 2015 15:35:00 +0000

I was recently working on a client where all the integration used HTTP Web Services but were not implemented using what I think most Siebel Developers would think of as the "Best Practice". Basically, the payloads were created using XSLT and the actual call to the Web Service was invoked using 'EAI HTTP Transport' rather than a custom WS Proxy BS created using the Tools wizard.

The reasoning provided to me behind using XSLT to create the payload is that it was more flexible. What do I mean by that? Well assume an outbound interface is needed where the "Best Practice" alternative would be to use 'EAI Siebel Adapter' to query an Integration Objects , then to use 'EAI Data Transformation Engine' to transform the payload into an IO (that was initially created by consuming a WSDL) recognizable by the Web Service. The limitation in this approach is around a couple pieces that Siebel Tools wizards have trouble with. The first is that some modern standards compliant WSDL definitions that use recursive data types cannot be imported at all. The second is that the SOAP Header follows a standard that is somewhat outdated, is not configurable, and basically requires scripting to create a custom header anyway. So the question is whether it is better to create the custom header using a scripted Filter Service, or to create the payload using XSLT. Once you go down the path of using XSLT, you basically cant use a Proxy Service anymore (since the proxy would be adding the SOAP envelope) so 'EAI HTTP Transport' is used instead.

I can think of a number of downsides to this approach:

Deployment Complexity increases

XSLT files must be deployed to the File System and kept in sync across however many app servers (and failover servers) are used by EAI in the respective environments
No WSDL is actually consumed in this approach so web service end points must be stored somewhere which will likely be different in each environment

More Steps in the WF

'EAI XSLT Service' uses UTF-16 input so it is likely Encoding will need to occur both to and from using 'Transcode Service'
Reading the XSL File from the file system

Mainainability

Siebel resources that know XSLT are presumably more rare than Siebel resources familiar with more "Best Practice" approaches

Performance

Calls to the File System to get the XSLT file might add significant load to a high volume interface

Data Integrity

The integrity of the outbound message data structure is not really enforceable in Siebel. Using XSLT requires the developer to create a payload that is correct as only the external system would be able to validate it. This is perhaps debateable because ultimately the developer will probably need to resolve this one way or the other during development. While I personally believe it is easier to troubleshoot problems that are actually identified within Siebel due to the strict defintion of the messages maintained in Siebel, I will concede that might be personal preference.

I am curious what others think and whether developers that use this approach can defend it better than I can. Ultimately, I think a scripted filter service is a better solution to the custom Soap Header issue, though I think this approach seems reasonable if the WSDL cannot be consumed, and modifying it is not possible.

Scriptless OK/Cancel popup

noreply@blogger.com (Mik) — Thu, 22 Oct 2015 14:21:00 +0000

In the early days of configuring in Siebel, if a user wanted a confirmation or warning message before proceeding, it would require Browser Script to implement and most Siebel configurators would try to discourage the requirement on purely technical grounds. And to be fair, an application littered with popup warnings may not be a great idea on functional grounds either, but there are probably good reasons to implement a warning message on occasion and it would be nice if it could be done in a way that does not have technical repercussions. So here you go.

On a BC, configure a 'Named Method' user property with value:
"YourMethodName", "INVOKESVC", "FDNS IDENT Encounter", "LS Pharma Signature UI Service", "ShowConfirmDialog", "'Cancel Method Name'", "YourCancelMethodName", "'OK Method Name'", "YourOKMethodName", "'Confirm Text'", "'Are you sure you want to Proceed or some other message?'"

The method 'YourMethodName' would be invoked according to your requirements. In a simple case, a custom action button on an applet could invoke this method but it could really be invoked anywhere.

The methods 'YourOKMethodName' and 'YourCancelMethodName' need to be callable methods, either that you also configured as additional Named Methods, or vanilla methods (or scripted ones defined in PreInvoke but that would sort of defeat the point).

When 'YourMethodName' is invoked, a popup message containing the message parameter is shown with an Ok and Cancel button. Clicking either button calls the methods defined. Enjoy

Vanilla Merge Behavior

noreply@blogger.com (Mik) — Thu, 22 Oct 2015 14:11:00 +0000

I recently encountered an issue when adding a DB View based EBC to a BO. When I attempted to perform a MergeRecords operation on two records in the primary BC (Contact in this case), I got an error:

[1] An error has occurred writing to a record
Please continue or ask your system administrator to check your application configuration if the problem persists.(SBL-DBC-00111)
[2]ORA-06550: line 137, column 15:
PL/SQL: ORA-01031: insufficient privleges
ORA-06550: line 137, column 1:
PL/SQL: SQL Statement ignored

It turns out this error is caused because siebel is attempting to update a column in a DB View. Why would it try to do that you might ask?

If we reverse engineer what is happening, we find that when performing a MergeRecords operation, Siebel determines the underlying table of the active business component and uses the SRF to find all links where the identified table is shared with the source business component of the Link and the source field is ‘Id’ (or null which is the same thing). The merge algorithm then takes this list to write the SQL to update the appropriate destination field to the new value of the Id field on the Source BC. Since merge is a data integrity operation, the use of Links using the ‘Id’ field is a proxy for those links configured to have a data integrity implication.

Ideally, Siebel would provide a configurable mechanism to exclude a particular link from a Merge, or, at a minimum, to recognize that when a link points to a destination BC that is based on a table object whose type is ‘External View’, no update is possible and hence should not be attempted. Alas that is not the case.

Therefore a way to trick the algorithm into excluding this link is to define the link on one which is not based on data integrity, and instead make it just informational. This can be done by making the Source field of the link something other than Id. But since we do not want to actually change the definition of the view this link points to, a column whose value matches the ROW_ID column is desireable. In the case of the Contact BC, there are a couple of potential options. PERSON_UID defaults to the Id field but since this column might be populated by EIM to be a value other than it’s ultimate row id, the values may not match on that data set. But since Contact is based on the Party model, the PAR_ROW_ID should always match since this points to the S_PARTY record and the same ROW_ID is always used. This column is not exposed on the Contact BC though so it needs to first be exposed and then the new BC field can be used in the links.

The XML Logger - Reviewing the Payload

noreply@blogger.com (Mik) — Fri, 10 May 2013 14:04:00 +0000

In my last post, I talked about how to capture XML Payloads by splitting large values across a series of DB records. In order to look at the data, we need to reassemble the payloads into a single text block again. I expose my Payload BC in a view tied to either the User's session:

or to the record on which the interface was executed:

The latter is accomplished through the payload parsing I talked about which allows us to create a view which links an object record to the payload record once the transaction id is stored on the payload record. On these views, I expose I nice looking form applet which displays both the request and response sides of the interface. The form fields are actually calculated fields, defined as DTYPE_TEXT, with the following expression:

InvokeServiceMethod("XXX Utilities", "ConcatenateField", "bo.bc='XXX User Session.XXX User Session XML Detail', FieldName='Log Text', SearchExpr='[Parent Id]=""+[Id]+"" AND [Field]="Request"'", "Out")

where:

'XXX Utilities' is my eScript framework service with many commonly used functions
'ConcatenateField' is a method on that service 'bo.bc' is a parameter name for that method
'XXX User Session' is the name of the business object where my user sessions are stored
'XXX User Session XML Detail' is the name of the business component containing the split up log data 'FieldName' is another parameter for this method
'Log Text' is the name of the field on the 'XXX User Session XML Detail' BC where the split payload text is stored defined as DTYPE_CLOB
'SearchExpr' is another parameter for this method

Finally the search expression looks a bit complicated as passing quotes to the InvokeServiceMethod is difficult. I have improvised by using a commonly used XML expression of " which the method then recognizes and converts back to a quote. Here is the method:

function ConcatenateField(Inputs, Outputs) {
//Inputs: bo.bc  "boName.bcName"
//   FieldName
//   SearchExpr BC Search Expression (Optional)
 var retValue = "";
 var found = false;
 var search = Inputs.GetProperty("SearchExpr");
 try {
  var arSplit = Inputs.GetProperty("bo.bc").split(".");
  var bcQuery:BusComp;
  if (arSplit[0] == "ACTIVE") 
   bcQuery = TheApplication().ActiveBusObject().GetBusComp(arSplit[1]);
  else 
   bcQuery = TheApplication().GetBusObject(arSplit[0]).GetBusComp(arSplit[1]);
   
  var delimeter = (Inputs.GetProperty("delimeter") != "" ? Inputs.GetProperty("delimeter") : "\n");
 
  with (bcQuery) {
   if (search != "") {
    ClearToQuery();
    arSplit = Inputs.GetProperty("SearchExpr").split(""");
    search = arSplit.join("'");
    SetSearchExpr(search);
    ActivateField(Inputs.GetProperty("FieldName"));
    SetViewMode(AllView);
    ExecuteQuery(ForwardOnly);
   }
  
   found = FirstRecord();
   while(found) {
    retValue += GetFieldValue(Inputs.GetProperty("FieldName"));
    found = NextRecord();
    if (found) retValue += delimeter;
   }

      Outputs.SetProperty("Out", retValue);
  }
 } catch(e) {
  TheApplication().RaiseError(e);
 } finally {
  bcQuery = null;
  arSplit = null;
 }
}

The XML Logger

noreply@blogger.com (Mik) — Tue, 07 May 2013 22:55:00 +0000

In my last post, I promised to try to bring us up to date on the current implementation of the logging framework, and specifically the XML Logger component of it. The framework is initialized on the Application Start event making all these methods available from script. From an XML Logging perspective, it can be initiated in one of two ways:

As a standard business service from within the Integration WF (or business service). In this approach, just before the outbound WS or HTTP call, you would call the logRequest or logResponse methods of the framework business service, passing in at a minimum the property set about to be interfaced. There are many other attributes of the payload record which can be optionally used which I won't go into detail over. You can always add attributes to meet your needs and you don't need to populate any of them really.
As a Filter Service. This is used for Web Services and is useful in that it can be turned on or off without modifying any existing Integration WFs. On the Web Service admin views, for each web service operation that you want to log, just specify the Request/Response Filter Service as the Framework business service and the Request/Response Filter Method as logRequest/logResponse respectively.
Can be implemented to capture other payloads as needed, for instance before and after messages of the EAI Data Transformation Service

Ok, now for the nitty gritty. What do the logRequest/logResponse methods do? Both are similar and different only in that all interface logging records have a placeholder for a request and a response payload, which the two methods are differentiated to populate. The main input to these methods is an XML payload. At a high level, here is the algorithm:

Navigate the property set until the first 'ListOf*' tag is found which is assumed to be the beginning of the Integration Object data.
Call a method to Parse the remaining child data to correctly name and categorize the interface based on the IO Type and iddentify the record id and unique identifier attributes. This allows for optional scripting to tailor the logging service to your client's unique needs
Call the logInterface method which:

Checks if in an EAI Transaction. If so, add the payload to an array, otherwise continue (This is currently only implemented to support outbound interfaces when using the Filter service implementation)
Creates a session record if one does not already exist (Inbound interfaces executed by an EAI OM typically)
Deal with anonymous logins (when used on an inbound interface the request method will be executed under the Anonymous login but the response method with be performed by the interface user id)
Creates a payload record to store the attributes extracted from the payload parsing
Split the payload into chunks no larger than the BLOB length and create detail records for each chunk

First the PreInvoke method. This mostly speaks for itself but since we may want to save processing overhead the calling of the parseInterface method is parameterized and controlled by which method is actually invoked.

function Service_PreInvokeMethod (MethodName, Inputs, Outputs){
  var retValue      = CancelOperation;

  switch(MethodName) {
  case "logInterface":     
    var key = logInterface(Inputs, Outputs);
    Outputs.SetProperty("key", key)
    break;
  case "logParseRequest":     
    logRequest(Inputs, Outputs, true, "");
    break;
  case "logParseResponse":     
    logResponse(Inputs, Outputs, true, "");
    break;
  case "logRequest":     
    logRequest(Inputs, Outputs, false, "");
    break;
  case "logResponse":     
    logResponse(Inputs, Outputs, false, "");
    break;
  case "logTransformRequest":     
    logRequest(Inputs, Outputs, false, "Transform");
    break;
  case "logTransformResponse":     
    logResponse(Inputs, Outputs, false, "Transform");
    break;
  }
  return (retValue);
}

Next the logRequest and logResponse Methods. Like I said they are very similar except for which field the payload is passed to. Also the logResponse method has some additional logic for parsing SOAP faults.

function logRequest(Inputs, Outputs, parse, mode) {
/* ***********************************
Purpose: Log interface request from/to an external interface in the session log
Usage: In Web Service definition, operations applet, set the Request Filter BS to 'PPT Utilities'
  and the method to logRequest.  Clear the cache
Arguments: 1 - SoapMessage will implicily be passed as a child property set
**************************************** */
try {
  var soapEnv, soapBody, divePS, direction, progress;
  var bodyType="";
  var msgType="";
  var parseResults = new Object();
  var key = TimeStamp("DateTimeMilli");
  var max = 3;
  var dives = 0;

  if(Inputs.GetChildCount() > 0) {
    // Get the SOAP envelope from the SOAP hierarchy.  If payload is passed as an input property set, skip down an extra level
    soapEnv = Inputs.GetChild(0);        //Like env:Envelope

    //Minimize processing if payloads/logging information will not be stored
    if (gHoldBufferDump == true || gsTraceIntfaceReqResp == "TRUE") {
      //if called from EAI Data Transformation Engine and user logging level is 5 capture passing specific props
      if (mode=="Transform" && (gHoldBufferDump == true || gCurrentLogLvl >= 5)) {
        parseResults.recId = soapEnv.GetChild(0).GetChild(0).GetProperty("Id");
        parseResults.recBC = soapEnv.GetChild(0).GetChild(0).GetType();
        msgType = Inputs.GetProperty("MapName");
        parseResults.funcName = Inputs.GetProperty("MapName");
        direction = "EAI Transform";
      } else {
        try { // Try to process the message to get functional data 
          if (soapEnv.GetType().toUpperCase().indexOf("ENVELOPE") <0 ) soapEnv = soapEnv.GetChild(0);
          direction = Inputs.GetProperty("WebServiceType")+" "+Inputs.GetProperty("Direction");
          for (var i=0; i < soapEnv.GetChildCount(); i++) {   
            bodyType = soapEnv.GetChild(i).GetType();    //Like env:Body
            if (bodyType.toUpperCase() == "BODY" || bodyType.substr(bodyType.indexOf(":")+1).toUpperCase() == "BODY") {
              soapBody = soapEnv.GetChild(i);
              for (var j=0; j < soapBody.GetChildCount(); j++) {   
                msgType = soapBody.GetChild(j).GetType();  //Full Port name of the WS 
  
                //Parse to check for faults and create a text string to be used in the key
                if (msgType.indexOf(":") >= 0)  msgType = msgType.substr(msgType.indexOf(":")+1); //strip namespace
                if (msgType.indexOf(" ") >= 0)  msgType = msgType.substr(0, msgType.indexOf(" ")); //strip namespace declaration
                if (msgType.indexOf("_") >= 0)  msgType = msgType.substr(0, msgType.lastIndexOf("_"));//strip port operation
  
                //if true, attempt to find Row Id in payload to stamp on log record so log can be linked to Siebel record  
                if (parse == true) {
                  divePS = soapBody.GetChild(j); //.GetChild(0)
                  while (divePS.GetType().indexOf("ListOf") < 0 && dives <= max) {
                    if (divePS.GetChildCount() > 0) {
                      divePS = divePS.GetChild(0);
                      dives++;
                    } else dives = max + 1;
                  }
   
                  //If a ListOf... container is found, this is a SiebelMessage generated by Siebel. Otherwise parse the SOAP Body
                  if (divePS.GetType().indexOf("ListOf") >= 0) parseInterface(divePS, parseResults);
                  else parseInterface(soapBody, parseResults);
                }
              } 
        
              break;
            }
   }         
 } catch(e) {
          //If an error occurs while parsing, just try to write the message whole
        }
            
      } //SOAP Message scenario

      //If msgType is identified then insert a log for this payload
      if (msgType != "") {
        msgType = msgType.replace(/_spc/g, "").replace(/\s/g, "");
        key = msgType+"_"+key;

        TheApplication().SetProfileAttr("InterfaceKeyInbound", key);
        progress = logInterface(key, soapEnv, null, direction, parseResults.recId, parseResults.recBC, "Pending", msgType, parseResults.funcName, null, null, null, parseResults.ref1, null, null, parseResults.refField);
      }
    } else if (gsTraceIntfaceReqResp == "FALSE") {
      //Do Nothing
    } else { //Store payloads in case an error occurs
      //var holdPayload = ["Request", Inputs, parse, mode];
      gHoldReqResp.push(["Request", Inputs, parse, mode]);
      if (gHoldBufferMax > 0 && gHoldReqResp.length > gHoldBufferMax) gHoldReqResp.shift();
    }
  } // Inputs.GetChildCount()
  Outputs.InsertChildAt(soapEnv,0);
} catch(e) {
  RaiseError(e);
} finally {
  divePS = null;
  soapBody = null;
  soapEnv = null;
  parseResults =  null;
}

}

function logResponse(Inputs, Outputs, parse, mode) {
/* ***********************************
Purpose: Log interface response from/to an external interface in the session log
Usage: In Web Service definition, operations applet, set the Response Filter BS to 'PPT Utilities'
  and the method to logResponse.  Clear the cache
Arguments: 1 - SoapMessage will implicily be passed as a child property set
**************************************** */
try {
  var soapEnv, soapBody, divePS, direction, progress;
  var bodyType="";
  var msgType="";
  var parseResults = new Object();
  var fault=null;
  var key = TheApplication().GetProfileAttr("InterfaceKeyInbound");
  var max = 3;
  var dives = 0;
  var dump = false;

  if(Inputs.GetChildCount() > 0) {
    // Get the SOAP envelope from the SOAP hierarchy
    soapEnv = Inputs.GetChild(0);

    //Minimize processing if payloads/logging information will not be stored
    if (gHoldBufferDump == true || gsTraceIntfaceReqResp == "TRUE") {
      if (mode=="Transform" && (gHoldBufferDump == true || gCurrentLogLvl >= 5)) {
        dump = true;
        direction = "EAI Transform";
        if (soapEnv.GetChild(0).GetChild(0).PropertyExists("Id")) {
          parseResults.recId = soapEnv.GetChild(0).GetChild(0).GetProperty("Id");
          parseResults.recBC = soapEnv.GetChild(0).GetChild(0).GetType();
        }
      } else if (mode=="") {
        dump = true;
        try { // Try to process the message to get functional data
          direction = Inputs.GetProperty("WebServiceType")+" "+Inputs.GetProperty("Direction");
          //Soap Envelope Request may have a Header and a Body so loop to the Body
          for (var i=0; i < soapEnv.GetChildCount(); i++) {
            bodyType = soapEnv.GetChild(i).GetType();
            if (bodyType.toUpperCase() == "BODY" || bodyType.substr(bodyType.indexOf(":")+1).toUpperCase() == "BODY") {
              soapBody = soapEnv.GetChild(i);
  
              //Soap Body typically has a container for the Message
              for (var j=0; j < soapBody.GetChildCount(); j++) {   
                msgType = soapBody.GetChild(j).GetType();
   
                //Parse to check for faults and create a text string to be used in the key
                if (msgType.indexOf(":") >= 0)  msgType = msgType.substr(msgType.indexOf(":")+1)
                if (msgType.indexOf(" ") >= 0)  msgType = msgType.substr(0, msgType.indexOf(" "))
                if (msgType.indexOf("_") >= 0)  msgType = msgType.substr(0, msgType.lastIndexOf("_"))
  
                if (msgType.toUpperCase() == "FAULT") fault = soapBody.GetChild(j).GetProperty("faultstring");
                else if (parse == true) { 
                  //if true, attempt to find Row Id in payload to stamp on log record so log can be linked to Siebel record  
                  divePS = soapBody.GetChild(j); //focus on the Message level
                  while (divePS.GetType().indexOf("ListOf") < 0 && dives <= max) {
                    if (divePS.GetChildCount() > 0) {
                      divePS = divePS.GetChild(0);
                      dives++;
                    } else dives = max + 1;
                  }
   
                  //If a ListOf... container is found, this is a SiebelMessage generated by Siebel. Otherwise parse the child of the Body
                  if (divePS.GetType().indexOf("ListOf") >= 0) parseInterface(divePS, parseResults);
                  else parseInterface(soapBody, parseResults);
                }
              }
              break;
            }
          }
        } catch(e) {
          //If an error occurs while parsing, just try to write the message whole
        }
      }
 
      if (key == "") {
        key = TimeStamp("DateTimeMilli");
        if (msgType != "") {
          msgType = msgType.replace(/_spc/g, "");
          key = msgType+"_"+key;
        }
      }
         
      if (dump == true) {
        if (fault != null) {
          logInterface(key, null, soapEnv, null, parseResults.recId, parseResults.recBC, "error", null, null, fault);
        } else {
          var recId = (parseResults.recId != "" ? parseResults.recId : null);
          var recBC = (parseResults.recBC != "" ? parseResults.recBC : null);
          var ref1 = (parseResults.ref1 != "" ? parseResults.ref1 : null);
          var funcName = (parseResults.funcName != "" ? parseResults.funcName : null);
          progress = logInterface(key, null, soapEnv, direction, recId, recBC, "Complete", msgType, funcName, null, null, null, ref1);
        }
      }
    } else if (gsTraceIntfaceReqResp == "FALSE") {
      //Do Nothing
    } else { //Store payloads in case an error occurs
      gHoldReqResp.push(["Response", Inputs, parse, mode]);
      if (gHoldBufferMax > 0 && gHoldReqResp.length > gHoldBufferMax) gHoldReqResp.shift();
    }
  } // Inputs.GetChildCount()
  Outputs.InsertChildAt(soapEnv,0);
} catch(e) {
  RaiseError(e);
} finally {
  divePS = null;
  soapBody = null;
  soapEnv = null;
  parseResults =  null;
  TheApplication().SetProfileAttr("InterfaceKeyInbound", "");
}
}

The parseInterface method passes the ListOf container of the Integration Object to a switch statement where each BC type can be evaluated. This is useful only when using the Filter Service triggering mechanism otherwise the Record Id and Interface Name attributes can just be explicitly passed as parameters. A case section should be created for each Integration Object being processed. This function really needs to be manually manipulated for every implementation and integration point to explicitly specify how to find the record id for a particular integration.

function parseInterface(ListOfIC, oReturn) {    //Input ListOfIC is a ListOf...
//Called from logRequest and logResponse to parse a message and get the row id or reference ids of different
//objects
try {
  if (ListOfIC.GetChildCount() > 0) {
    var IC = ListOfIC.GetChild(0);   //Integration Component Instance
    var icNameSpace = "";
    var intCompType = IC.GetType();
    var propName;
    var stop = false;
    var childIC;
    var childFlds;
      
    if (intCompType.indexOf(":") >= 0) {
      icNameSpace = intCompType.substr(0, intCompType.indexOf(":")+1);
      intCompType = intCompType.substr(intCompType.indexOf(":")+1);
    }

    //For these types, dive an additional level
    switch(intCompType) {
    case "ATPCheckInterfaceRequestOrders":
      IC = IC.GetChild(0);
      intCompType = IC.GetType();
      if (intCompType.indexOf(":") >= 0) {
        icNameSpace = intCompType.substr(0, intCompType.indexOf(":")+1);
        intCompType = intCompType.substr(intCompType.indexOf(":")+1);
      }
      break;
    }
 
    for (var flds = 0; flds < IC.GetChildCount(); flds++) { //Loop through Fields
      propName = IC.GetChild(flds).GetType();
      switch (intCompType) {
      case "Quote":
      case "SWIQuote":
        oReturn.recBC = "Quote";
        if (propName == icNameSpace+"Id") {
          oReturn.recId = IC.GetChild(flds).GetValue();
          stop = true;
        }
        break;

      case "ProductIntegration":
        oReturn.recBC = "Internal Product";
        if (propName == icNameSpace+"ListOfProductDefinition") {
          childIC = IC.GetChild(flds).GetChild(0);
          for (childFlds = 0; childFlds < childIC.GetChildCount(); childFlds++) { //Loop through Fields
            propName = childIC.GetChild(childFlds).GetType();
            if (propName == icNameSpace+"Id" || propName == icNameSpace+"ProductId") {
              oReturn.recId = childIC.GetChild(childFlds).GetValue();
              stop = true;
            }
            if (stop) break;                              
          }
        }
        break;
     
      default:
        if (propName.indexOf("Id") >= 0) {
          oReturn.recId = IC.GetChild(flds).GetValue();
          stop = true;
        }
        oReturn.recBC = intCompType;
        stop = true;
      }
      if (stop) break;                              
    }
  }
} catch(e) {
  RaiseError(e);
} finally {
  childIC = null;
  IC = null;
}
}

The logInterface method is called by both logRequest and logResponse to manage the session records for inbound interfaces and create the actual payload record. I will have to go into more detail about the Anonymous login processing at some other time. Suffice to say this works under a variety of web service setups.

function logInterface() {
if (gHoldBufferDump == true || gsTraceIntfaceReqResp == "TRUE") {
  try {
    var key, dir, recId, recBC, status, srcObj, name, logText, userText, retCode, ref1, ref2, ref3, refField, findResponseMethod;
    var request:PropertySet, response:PropertySet;
    var progress = "";

    //if attributes passed as an input property set, set variables from them
    if (typeof(arguments[0]) == "object") {
      progress = progress+"\n"+"REF1: typeof(arguments[0]) == object";
      var Inputs:PropertySet = arguments[0];
      name = Inputs.GetProperty("FunctionalName");
      key = Inputs.GetProperty("Key");
   
      for (var i=0;i < Inputs.GetChildCount();i++) {
        if (Inputs.GetChild(i).GetType() == "Request") request = Inputs.GetChild(0);
        if (Inputs.GetChild(i).GetType() == "Response") response = Inputs.GetChild(0);
      }
      dir = Inputs.GetProperty("Direction");
      recId = Inputs.GetProperty("LinkId");
      recBC = Inputs.GetProperty("LinkBC");
      status = Inputs.GetProperty("Status");
      srcObj = Inputs.GetProperty("SourceObject");
      logText = Inputs.GetProperty("LogText");
      userText = Inputs.GetProperty("UserText");
      retCode = Inputs.GetProperty("ReturnCode");
      ref1 = Inputs.GetProperty("Reference1");
      ref2 = Inputs.GetProperty("Reference2");
      ref3 = Inputs.GetProperty("Reference3");
      refField = Inputs.GetProperty("RefField");
    } else {
      progress = progress+"\n"+"REF1: else typeof(arguments[0])";
      key = arguments[0];
      request = arguments[1];
      response = arguments[2];
      dir = arguments[3];
      recId = arguments[4];
      recBC = arguments[5];
      status = arguments[6];
      srcObj = arguments[7];
      name = arguments[8];
      logText = arguments[9];
      userText = arguments[10];
      retCode = arguments[11];
      ref1 = arguments[12];
      ref2 = arguments[13];
      ref3 = arguments[14];
      refField = arguments[15];
    }
  
    //When called though WF as Payload logger, generate the key if not provided
    if (key == "" || key == undefined || key == null) {
      if (name == "" || name == undefined || name == null) name = "None";
      key = name.replace(/_spc/g, "").replace(/\s/g, "")+TimeStamp();
    }

    var found:Boolean = false;
    var sessionId:String = "";
    var guestSessionId:String = "";
    var guestSessionFound = false;
    var createSession = false;
    var firstMessage = false;
    var boSessionFlat;
    var bcSessionXMLFlat;
    var useGuestSession = (response != null && gsMergeGuestSessions=="TRUE" && gsGuest != "" ? true : false);
    var boSession:BusObject = TheApplication().GetBusObject("PPT User Session");
    var bcSession:BusComp;
 
    if (gHoldBuffer == true) {
      progress = progress+"\n"+"REF2: gHoldBuffer == true";
      //If in a EAI Txn, store all payloads in an array so they can be written after the commit or rollback
      gHoldPayloads[gHoldPayloads.length] = arguments;
    } else {
      progress = progress+"\n"+"REF2: gsTraceIntfaceReqResp == TRUE & key == "+key;
      //If an interface is being logged for the first time, need to instantiate the session
      if (gSessionId == "") {
        progress = progress+"\n"+"REF3: gSessionId == ''";
        //If Guest connections are not used, a simplified session management can be used 
        if (gsGuest == "" && key != "") {
          progress = progress+"\n"+"REF4: gsGuest == '' & key == "+key;
          bcSession = boSession.GetBusComp("PPT User Session");
          with (bcSession) {
            NewRecord(NewBefore);
            SetFieldValue("Employee Id",TheApplication().LoginId());
            SetFieldValue("Session Stamp",gsLogSession);
            WriteRecord();
            gSessionId = GetFieldValue("Id");
          }
          createSession = false; //skip logic below to create/merge a guest session
          firstMessage = true; //will always insert the input message rather than querying to update
          //Reset the variable to check whether to merge the guest session.  This allows a log buffer dump
          gsMergeGuestSessions = "FALSE";
        } else {
          progress = progress+"\n"+"REF4: else: gsGuest == '' & key == "+key;
          createSession = true;
  
          //confirm that current session has not been created yet
          bcSession = boSession.GetBusComp("PPT User Session");
          with (bcSession) {
            ClearToQuery();
            SetSearchSpec("Session Stamp", gsLogSession);
            ExecuteQuery(ForwardOnly);
            found = FirstRecord();
 
            if (found == true) {
              gSessionId = GetFieldValue("Id");
              createSession = false;
            } else {
              firstMessage = true;
            }
          }
        }
      }
 
      if (createSession == true || useGuestSession == true) {
        progress = progress+"\n"+"REF5: createSession == true || useGuestSession == true";
        bcSession = boSession.GetBusComp("PPT User Session");
 
        //Because EAI logins can trigger logging from the anonymous login, the response logging will trigger
        //from a different session. Query for the most recent corresponding request log and update it
        if (useGuestSession) {
          progress = progress+"\n"+"REF6: useGuestSession";
          boSessionFlat = TheApplication().GetBusObject("PPT User Session Flat");
          bcSessionXMLFlat = boSessionFlat.GetBusComp("PPT User Session XML");
          bcSessionXMLFlat.ActivateField("Parent Id");
          bcSessionXMLFlat.ClearToQuery();
          if (typeof(srcObj) != "undefined" && srcObj != null) bcSessionXMLFlat.SetSearchSpec("Source Object", srcObj);
          if (typeof(ref1) != "undefined" && ref1 != null) bcSessionXMLFlat.SetSearchSpec("Reference Id 1", ref1);
          bcSessionXMLFlat.SetSearchSpec("Response", "IS NULL");
          bcSessionXMLFlat.SetSearchSpec("Employee Login", gsGuest);
          bcSessionXMLFlat.SetSortSpec("Created (DESC)");
          bcSessionXMLFlat.ExecuteQuery(ForwardBackward);
          guestSessionFound = bcSessionXMLFlat.FirstRecord();
    
          if (guestSessionFound == true) guestSessionId = bcSessionXMLFlat.GetFieldValue("Parent Id");
        }
    
        if (guestSessionFound == false && createSession == true) {
          progress = progress+"\n"+"REF7: guestSessionFound == false & createSession == true";
          //Anonymous login session not found and there is no current session.  Create a new one
          with (bcSession) {
            NewRecord(NewBefore);
            SetFieldValue("Employee Id",TheApplication().LoginId());
            SetFieldValue("Session Stamp",gsLogSession);
            WriteRecord();
            gSessionId = GetFieldValue("Id");
          }
        } else if (guestSessionFound == true && createSession == false) {
          progress = progress+"\n"+"REF7: guestSessionFound == true & createSession == false";
          //Anonymous login session found and there is a current session. 
          //Link child records to the parent session for the Interface User and delete the guest session (faster than a merge)
          while (guestSessionFound) {
            bcSessionXMLFlat.SetFieldValue("Parent Id",gSessionId);
            bcSessionXMLFlat.WriteRecord();
            guestSessionFound = bcSessionXMLFlat.NextRecord();
          }
          with (bcSession) {
            ClearToQuery();
            SetSearchSpec("Id", guestSessionId);
            SetSortSpec("");
            ExecuteQuery(ForwardOnly);
            guestSessionFound = FirstRecord();
     
            if (guestSessionFound == true) DeleteRecord();
            ClearToQuery();
            SetSortSpec("");
            SetSearchSpec("Id", gSessionId);
            ExecuteQuery(ForwardOnly);
          }
        } else if (guestSessionFound == true && createSession == true) {
          progress = progress+"\n"+"REF7: guestSessionFound == true & createSession == true";
          //Anonymous login session found and there is no current session.  Update the guest session to EAI values
          with (bcSession) {
            ActivateField("Employee Id");
            ActivateField("Session Stamp");
            ClearToQuery();
            SetSearchSpec("Id", guestSessionId);
            SetSortSpec("");
            ExecuteQuery(ForwardBackward);
            found = FirstRecord();
     
            if (found == true) {
              SetFieldValue("Employee Id",TheApplication().LoginId());
              SetFieldValue("Session Stamp",gsLogSession);
              WriteRecord();
            }
          }
        } else {
          progress = progress+"\n"+"REF7: Anonymous login session not found and there is a current session.  Do Nothing";
          //Anonymous login session not found and there is a current session.  Do Nothing
        }
 
        //Reset the variable to check whether to merge the guest session.  This allows a log buffer dump
        gsMergeGuestSessions = "FALSE";
      }
      var bcSessionXML = boSession.GetBusComp("PPT User Session XML");
      var bcSessionXMLDtl = boSession.GetBusComp("PPT User Session XML Detail");
 
      with (bcSessionXML) {
        if (firstMessage) {
          progress = progress+"\n"+"REF8: firstMessage";
          //This is an insert so no query needed to update an existing record
          found = false;
          findResponseMethod = "Do Not Search";
        } else if (useGuestSession && guestSessionFound) {
          progress = progress+"\n"+"REF8: useGuestSession & guestSessionFound";
          //If this is the first update after a guest session is used, the key will not match but there should only be one XML record
          ClearToQuery();
          SetSearchSpec("Created By Login", gsGuest);
          SetSearchSpec("Parent Id", gSessionId);
          SetSearchSpec("Request", "IS NOT NULL");
          SetSearchSpec("Response", "IS NULL");
          ExecuteQuery(ForwardBackward);
          found = FirstRecord();
          findResponseMethod = "Session/Guest Login: "+gSessionId+"/"+gsGuest;
        } else if ((typeof(response) != "undefined" && response != null) ||
          (typeof(ref1) != "undefined" && ref1 != null && typeof(request) != "undefined" && request != null && gsReplaceSyncResponseWAsyncRequest == "TRUE")) {
          progress = progress+"\n"+"REF8: normal response update OR inbound request to a previously sent asynchronous request";
          //This is a normal response update to an existing message record or 
          //it is an inbound request to a previously sent asynchronous request with a matching Reference Id AND we want to log the Async request as the response
          ClearToQuery();
          SetSearchSpec("Parent Id", gSessionId);
 
          //If this is an Inbound request and Ref1 is provided, lookup by Ref1 
          if (typeof(ref1) != "undefined" && ref1 != null && typeof(request) != "undefined" && request != null) {
            SetSearchSpec("Reference Id 1", ref1.substring(0, 100));
            findResponseMethod = "Reference 1: "+ref1.substring(0, 100);
          } else {
            SetSearchSpec("Name", key);
            findResponseMethod = "Key: "+key;
          }
          SetSortSpec("");
          ExecuteQuery(ForwardBackward);
          found = FirstRecord();
        }
    
        //This is a normal request or an existing record could not be found for some reason
        if (found == false) {
          progress = progress+"\n"+"REF9: This is a normal request or an existing record could not be found for some reason ("+findResponseMethod+")";
          NewRecord(NewBefore);
          SetFieldValue("Name",key);
          SetFieldValue("Parent Id",gSessionId);
        }
    
        if (useGuestSession == true) {
          progress = progress+"\n"+"REF10: Set Name Key: "+key.substring(0, 100);
          SetFieldValue("Name", key.substring(0, 100));
        }
 
        //Since payloads can be any size, very large ones greater than CLOB size 131k need to be split with the text being 
        //put into multiple records
        if (typeof(request) != "undefined" && request != null) {
          progress = progress+"\n"+"REF11: Set Request";
          SetFieldValue("Request Time", TimeStamp("DateTimeFormatted"));
          splitPayload(bcSessionXML, bcSessionXMLDtl, "Request", request, 131072)
        }
        if (typeof(response) != "undefined" && response != null) {
          progress = progress+"\n"+"REF12: Set Response";
          SetFieldValue("Response Time", TimeStamp("DateTimeFormatted"));
          splitPayload(bcSessionXML, bcSessionXMLDtl, "Response", response, 131072)
        }
  
        if (typeof(dir) != "undefined" && dir != null && dir != "")   SetFieldValue("Direction", dir.substring(0, 30));
        if (typeof(recId) != "undefined" && recId != null && recId != "") SetFieldValue("Record Id", recId.substring(0, 15));
        if (typeof(recBC) != "undefined" && recBC != null && recBC != "") SetFieldValue("Record BC", recBC.substring(0, 50));
        if (typeof(status) != "undefined" && status != null && status !="") SetFieldValue("Status", status.substring(0, 30));
        if (typeof(srcObj)!="undefined" && srcObj != null && srcObj !="") SetFieldValue("Source Object", srcObj.substring(0, 50));
        if (typeof(name) != "undefined" && name != null && name != "")  SetFieldValue("Functional Name", name.substring(0, 50));
        if (typeof(logText)!= "undefined" && logText != null && logText !="") SetFieldValue("Log Text", logText.substring(0, 131072));
        if (typeof(userText)!= "undefined" && userText != null && userText !="") SetFieldValue("User Text", userText.substring(0, 255));
        if (typeof(retCode) != "undefined" && retCode != null && retCode != "")  SetFieldValue("Return Code", retCode.substring(0, 30));
        if (typeof(refField) != "undefined" && refField != null && refField!="") {
          SetFieldValue(refField, ref1);
          SetFieldValue("Reference Id 2", refField);
        } else {
          if (typeof(ref1) != "undefined" && ref1 != null)   SetFieldValue("Reference Id 1", ref1.substring(0, 100));
          if (typeof(ref2) != "undefined" && ref2 != null)   SetFieldValue("Reference Id 2", ref2.substring(0, 100));
        }
        if (typeof(ref3) != "undefined" && ref3 != null)   SetFieldValue("Reference Id 3", ref3.substring(0, 100));
        progress = progress+"\n"+"REF13: PreWrite";
        WriteRecord();
      }
      if (gsLogMode == "FILE") {
        progress = progress+"\n"+"REF14: gsLogMode == FILE";
        if (typeof(request) != "undefined" && request != null)  PropSetToFile(key+"_Request", request);
        if (typeof(response) != "undefined" && response != null) PropSetToFile(key+"_Response", response);
      }
    }
  } catch(e) {
    RaiseError(e, progress);
  } finally {
    request = null;
    response = null;
    Inputs = null;
    bcSessionXML = null;
    bcSessionXMLFlat = null;
    boSessionFlat = null;
    bcSession = null;
    boSession = null;
    TheApplication().SetProfileAttr("C1 Session Id", gSessionId);
  }
  return(progress);
}
}

The trimPS method is actually deprecated in my service but I include it in case it is useful to anyone. I basically just takes a property set, converts it to text, then sets a specified field on an instantiated BC passed as an input to the converted text of the property set. The assumption in using this method is that there is a limit to how large the stored payload can be.

function trimPS(ps, fieldName, bc, maxLength){
try {
  var psIn      = TheApplication().NewPropertySet();
  var psOut     = TheApplication().NewPropertySet();
  var bsService = TheApplication().GetService ("XML Converter (Data in Child PropSet)");
  psIn.SetProperty("EscapeNames", "False");
  var text:String = "";

  psIn.AddChild(ps);
  bsService.InvokeMethod("PropSetToXML", psIn, psOut);
  bc.SetFieldValue(fieldName, ToString(psOut).substring(0, maxLength));
  bc.SetFieldValue(fieldName+" Time", TimeStamp("DateTimeFormatted"));
  text = bc.GetFieldValue(fieldName);
  text = text.lTrim();
  if (text.substring(0, 14) == "PropertySet [ ") text = text.substring(14);
  text = text.rTrim("]");
  bc.SetFieldValue(fieldName,text);
} catch (e) {
  throw(e);
} finally { 
  psOut = null;
  psIn = null;
  bsService = null;
}
}

The splitPayload method is what replace the trimPS method. It is no longer limited in being able store only a certain character length of payload as this method splits the payload into chunks of a specified size and inserts records into the instantiated BC passed as an input.

function splitPayload(parentbc, detailbc, fieldName, ps, maxLength) {
try {
  var psIn      = TheApplication().NewPropertySet();
  var psOut     = TheApplication().NewPropertySet();
  var bsService = TheApplication().GetService ("XML Converter (Data in Child PropSet)");
  psIn.SetProperty("EscapeNames", "False");
  var text:String = "";
  var stripPS = false;
  var textPS = "";
  
  psIn.AddChild(ps);
  bsService.InvokeMethod("PropSetToXML", psIn, psOut);
  textPS = ToString(psOut);

  if (textPS.length > maxLength) {
    while (textPS.length > 0) {
      detailbc.NewRecord(NewAfter);
      detailbc.SetFieldValue("Field", fieldName);
      detailbc.SetFieldValue("Log Text", textPS.substring(0, maxLength));
 
      //Service adds a Prefix that needs to be removed
      text = detailbc.GetFieldValue("Log Text");
      text = text.lTrim();
      if (text.substring(0, 14) == "PropertySet [ ") {
        stripPS = true;
        text = text.substring(14);
      }
 
      textPS = textPS.substring(maxLength, textPS.length); 
 
      //If the Text is broken up across multiple records, need to remove the trailing ] from the last record
      if (stripPS && textPS.length < maxLength) {
        text = text.rTrim("]");
      }
      detailbc.SetFieldValue("Log Text",text);
      detailbc.WriteRecord();
    }
  } else {
    parentbc.SetFieldValue(fieldName, textPS);
    text = parentbc.GetFieldValue(fieldName);
    text = text.lTrim();
    if (text.substring(0, 14) == "PropertySet [ ") {
      stripPS = true;
      text = text.substring(14).rTrim("]");
      parentbc.SetFieldValue(fieldName, text);
    }
//  parentbc.WriteRecord();
  }
} catch (e) {
  RaiseError(e);
} finally { 
  psOut = null;
  psIn = null;
  bsService = null;
}
}

Next: Viewing the Payload

State of Logging - An Introduction

noreply@blogger.com (Mik) — Tue, 07 May 2013 20:33:00 +0000

I was reading Jason Le's recent post about XML Logging and I realized the problem statement he was describing was intimately familiar to me because it is something I have been dealing with over the years and have evolved a solution for it. I say evolved because it has gone though so many iterations by this point I am not sure where to begin. So let me start with a verbal explanation, and then I will get into the details.

I built an eScript Logging Service back in the day to help with tracing processing through custom script. This was written out to a text file in the file system. I don't recall the sequence of events after that but at some point I added an XML logging utility to this so that the XML files would be written to the file system as well. This was initially an explicit call to a business service within the integration WF to pass an XML Property Set so it could be written out. I then moved to a client which was using a business service to create custom SOAP headers so I modified the logging service so that it could be called as a custom filtering service itself. This allowed the exact payload to be captured as it was leaving/returning to the Siebel application.

At some point I ended up on a client that had UNIX Application servers, needed me to do production support post go-live AND was very restrictive about granting access to the production file system. Rather than deal with the UNIX and access issues, I opted to modify the service so it could operate in two modes. When using a thick client, it would continue to write all information out to the local file system as defined by the SIEBEL_LOG_DIR environment variable. But I allowed thin client users to output logging data to a new set of custom tables. Basically when a user logs in, a session record is created. All escript logging data is written out to a 1:M detail table in buffered chunks to minimize performance hits. XML Logging does the same (though in a slightly different way). Each detail record has the opportunity to store a row id which allows a session to be linked to a particular record that was logged, like an Order Id. This allows for audit views to be created which show all the user sessions with logging information tied to a particular record.

The other problem I needed to solve was that when an error occurs, Siebel rolls back transactions. Therefore all interfaces use the EAI Transaction Service and I have modified this service to set a flag so that when in a transaction, all XML payloads are stored in memory until the transaction is either aborted or committed, at which point all the messages are written to the DB in the order they were executed.

To view logging information, whether XML or escript, requires the many detail records to be reassembled into a viewable format. There is a method of the service that basically just queries all the detail records for a particular session or XML Payload and concatenates the text into a single value. This method can be invoked from a calculated field and the calculated field exposed on a form applet. Because form applet fields are not the best way to see the data, I typically copy the data out of the field and into a text editor of choice. For instance I use Notepad++ with a Pretty Print add on to view XML payloads.

It will take several posts to go over the implementation in detail, but hopefully this whets people's appetites

Next: The XML Logger

Miscellaneous SQL Repository Research

noreply@blogger.com (Mik) — Wed, 12 Dec 2012 18:33:00 +0000

I frequently use SQL to track down potential issues in the repository. Here are a couple of statements that help solve common problems:

Fields Inactivated in the BC but Active in the Integration Object

select io.name int_obj, ic.name int_comp, icf.NAME int_field from siebel.S_INT_FIELD icf, siebel.S_INT_COMP ic, siebel.S_INT_OBJ io, siebel.s_repository r, siebel.s_buscomp bc, siebel.s_field f
where icf.INT_COMP_ID = ic.row_id and ic.INT_OBJ_ID = io.row_id and io.repository_id = r.row_id and r.name = 'Siebel Repository'
and ic.EXT_NAME = bc.name and icf.EXT_NAME = f.name and f.buscomp_id = bc.row_id and bc.repository_id = r.row_id and f.inactive_flg = 'Y'
and io.inactive_flg = 'N' and ic.inactive_flg = 'N' and icf.inactive_flg = 'N' and f.last_upd > to_date('11/01/2012', 'MM/DD/YYYY');

Identify potential causes of the Truncation or Null Fetch error

This can sometimes be caused by not compiling the Table object too. The first query finds business component joined fields where the BC field length or data type does not match the column length. The second does the same but only analyzes base table columns and adds a last updated parameter which could be added or removed from either. The last updated date is useful because vanilla actually has numerous instances of this potential problem. I say potential because any records returned need to be checked against the data. The error will only occur if the data in the column is longer than the BC length specified. The Third query is an example of how to check assuming for instance S_ORDER_ITEM.ATTRIB_40 is mapped to a DTYPE_ID BC field..

select bc.name Bus_comp, f.name field, f.join_name Join, j.DEST_TBL_NAME Tbl_name, f.col_name Col_name, f.type Field_Type, f.textlen Fld_Lgth, c.DATA_TYPE Col_type, c.LENGTH col_lgth
from siebel.s_field f, siebel.s_repository r, siebel.s_join j, siebel.s_buscomp bc, siebel.s_column c, siebel.s_table t
where bc.REPOSITORY_ID = r.row_id and r.name = 'Siebel Repository'
and bc.row_id = f.buscomp_id and j.name = f.join_name and j.buscomp_id = bc.row_id
and c.TBL_ID = t.row_id and t.NAME = j.DEST_TBL_NAME and c.name = f.col_name
and r.row_id = t.REPOSITORY_ID and r.row_id = f.REPOSITORY_ID and r.row_id = c.REPOSITORY_ID
and t.INACTIVE_FLG = 'N' and c.INACTIVE_FLG = 'N' and bc.INACTIVE_FLG = 'N' and f.INACTIVE_FLG = 'N'
and ((f.type = 'DTYPE_ID' and c.length > 15)
or (f.type = 'DTYPE_PHONE' and c.length > 40)
or (f.type = 'DTYPE_BOOL' and c.length > 1)
or (f.type = 'DTYPE_TEXT' and c.DATA_TYPE like 'Date%'))
order by j.DEST_TBL_NAME, f.col_name;

select bc.name Bus_comp, f.name field, t.NAME tbl_name, f.col_name Col_name, f.type Field_Type, f.textlen Fld_Lgth, c.DATA_TYPE Col_type, c.LENGTH col_lgth
from siebel.s_field f, siebel.s_repository r, siebel.s_buscomp bc, siebel.s_column c, siebel.s_table t
where bc.REPOSITORY_ID = r.row_id and r.name = 'Siebel Repository'
and bc.row_id = f.buscomp_id
and c.TBL_ID = t.row_id and t.NAME = bc.TABLE_NAME and c.name = f.col_name
and r.row_id = t.REPOSITORY_ID and r.row_id = f.REPOSITORY_ID and r.row_id = c.REPOSITORY_ID
and t.INACTIVE_FLG = 'N' and c.INACTIVE_FLG = 'N' and bc.INACTIVE_FLG = 'N' and f.INACTIVE_FLG = 'N'
and ((f.type = 'DTYPE_ID' and c.length > 15)
or (f.type = 'DTYPE_PHONE' and c.length > 40)
or (f.type = 'DTYPE_BOOL' and c.length > 1)
or (f.type = 'DTYPE_TEXT' and c.DATA_TYPE like 'Date%'))
and f.last_upd > to_date('06/01/2010', 'MM/DD/YYYY')
order by t.NAME, f.col_name;

select ATTRIB_40 from siebel.S_ORDER_ITEM_XM where length(ATTRIB_40) > 15;

WF Deployed but not activated:

select wpr.proc_name, wpr.version from siebel.S_WFR_PROC wpr, siebel.s_repository r
where r.row_id = wpr.REPOSITORY_ID and r.name = 'Siebel Repository' and wpr.STATUS_CD = 'COMPLETED'
and wpr.name like 'PPT%'
and exists (select 'x' from siebel.S_WFA_DPLOY_DEF wpd where wpd.NAME = wpr.proc_name and wpd.TYPE_CD = 'PROCESS' and wpd.DEPLOY_STATUS_CD = 'ACTIVE'
and wpd.REPOSITORY_VERSION <> wpr.VERSION)
and wpr.version = (select max(wpr1.version) from siebel.S_WFR_PROC wpr1
where r.row_id = wpr1.REPOSITORY_ID and wpr1.STATUS_CD = 'COMPLETED' and wpr1.proc_name = wpr.proc_name)
order by wpr.proc_name;

BC Fields not configured as boolean that should be

select bc.name, f.name, f.type, f.CALCVAL from siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r
where f.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository'
and (f.CALCVAL like '%,%Y%,%N%)' OR f.CALCVAL like '%,%N%,%Y%)')
and f.TYPE <> 'DTYPE_BOOL'
and f.inactive_flg = 'N' and bc.name like 'PPT%' and bc.inactive_flg = 'N'
order by bc.name, f.name;

select bc.name, f.name, f.type, f.CALCVAL from siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r
where f.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository'
and f.TEXTLEN = 1
and f.TYPE <> 'DTYPE_BOOL'
and f.inactive_flg = 'N' and bc.name like 'PPT%' and bc.inactive_flg = 'N'
order by bc.name, f.name;

Applet controls where checkbox not configured correctly

select a.name, lc.name, lc.field_name, lc.HTML_TYPE, lc.HTML_ICON_MAP, f.type from siebel.s_list_column lc, siebel.s_list l, siebel.s_applet a, siebel.s_repository r, siebel.s_field f, siebel.s_buscomp bc
where lc.list_id = l.row_id and l.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and lc.inactive_flg = 'N' and a.inactive_flg = 'N'
and (lc.HTML_ICON_MAP is null or lc.HTML_TYPE <> 'CheckBox')
and f.name = lc.FIELD_NAME and f.BUSCOMP_ID = bc.row_id and bc.name = a.BUSCOMP_NAME and f.TYPE = 'DTYPE_BOOL' and a.name like 'PPT%';

Integration Object Maps that have changed since a prior release:

select release, Map_Name, change_level, Field_Upd, Field_User, Comp_Upd, Comp_User, Obj_Upd, Obj_user, Comp_Name, src_expr, dst_int_fld_name, Obj_Comments
from ( -- Object level
select o.x_release release, o.name Map_Name, '1-Object' change_level, null Field_Upd, null Field_User, null Comp_Upd, null Comp_User, o.last_upd Obj_Upd, ou.login Obj_user, null Comp_Name, null src_expr, null dst_int_fld_name, o.comments Obj_Comments
from siebel.S_INT_OBJMAP o, siebel.s_user ou
where o.last_upd_by = ou.row_id and o.last_upd >= to_date('04/23/2019', 'MM/DD/YYYY')
UNION ALL -- Component level
select o.x_release release, o.name Map_Name, '2-Component' change_level, null Field_Upd, null Field_User, c.last_upd Comp_Upd, cu.login Comp_User, null Obj_Upd, null Obj_user, c.name Comp_Name, c.SRC_SRCHSPEC src_expr, null dst_int_fld_name, o.comments Obj_Comments
from siebel.S_INT_OBJMAP o, siebel.S_INT_COMPMAP c, siebel.s_user cu, siebel.s_user ou
where c.int_obj_map_id = o.row_id and c.last_upd_by = cu.row_id and o.last_upd_by = ou.row_id --and o.x_release = '1.27.12'
and c.last_upd >= to_date('4/23/2019', 'MM/DD/YYYY')
UNION ALL -- Field level
select o.x_release release, o.name Map_Name, '3-Field' change_level, f.last_upd Field_Upd, fu.login Field_User, null Comp_Upd, null Comp_User, null Obj_Upd, null Obj_user, c.name Comp_Name, f.src_expr, f.dst_int_fld_name, o.comments Obj_Comments
from siebel.S_INT_OBJMAP o, siebel.S_INT_COMPMAP c, siebel.S_INT_FLDMAP f, siebel.s_user fu, siebel.s_user cu, siebel.s_user ou
where f.int_comp_map_id = c.row_id and c.int_obj_map_id = o.row_id and f.last_upd_by = fu.row_id and c.last_upd_by = cu.row_id and o.last_upd_by = ou.row_id --and o.x_release = '1.27.12'
and (f.last_upd >= to_date('4/23/2019', 'MM/DD/YYYY'))
) where release <> '1.27.13'
order by Map_Name, change_level;

Repository Search by SQL

noreply@blogger.com (Mik) — Wed, 12 Dec 2012 18:21:00 +0000

Tools offers a Repository Search feature which is quite robust but it has some significant drawbacks:

Tedious to specify only those items which might have the object name you are looking for
Time consuming to do a full search or a search of certain objects with high record counts
Does not search UI admin objects where a field might be referenced

There have been times when I wanted to inactivate objects in order to streamline the application and have had difficulty identifying all the references. Or if you see an error in the Siebel log, it is sometimes hard to forensically determine its source.

I have written a series of SQL statements to search for the items directly against the server tools DB. This SQL looks at both repository tables where a particular object type might be referenced as well as UI administrative objects. It is not guaranteed to be 100% comprehensive and should be modified to the particular needs of how a client has used the application. For instance, if Variable Maps are not modified from vanilla, it is probably not necessary to query the tables that store them.

Fields
Applets

SQL Applet Search

noreply@blogger.com (Mik) — Wed, 12 Dec 2012 18:20:00 +0000

Here is the SQL to find Applet References. You will notice there are two queries of Toggle Applets. One rolls an applet all the way up to the view it appears in while the other shows toggle applets that do not appear in any view. Therefore, in a toggle sequence, there may be one applet that appears twice. The wc or wildcard bind variable adds a trailing wildcard

var :applet = "Quote Form Applet"; -- Applet Name
var :wc = "N";

select attr_type, screen, viewname, obj_name, attr_name, Applet_Mode from (
select 'View web template item' attr_type, s.name screen, v.name viewname, '' obj_name, vwti.APPLET_NAME attr_name, vwti.APPLET_MODE_CD Applet_Mode from siebel.s_screen s, siebel.S_SCREEN_VIEW sv, siebel.S_VIEW_WTMPL_IT vwti, siebel.S_VIEW_WEB_TMPL vwt, siebel.s_view v, siebel.s_repository r
where vwti.APPLET_NAME like :applet||decode(:wc,'Y','%','') and vwti.VIEW_WEB_TMPL_ID = vwt.row_id and vwt.VIEW_ID = v.row_id and v.name = sv.VIEW_NAME and sv.SCREEN_ID = s.row_id and s.repository_id = r.row_id
and v.repository_id = r.row_id and r.name = 'Siebel Repository' and vwti.inactive_flg = 'N' and vwt.inactive_flg = 'N' and sv.inactive_flg = 'N' and v.inactive_flg = 'N' and s.inactive_flg = 'N'

union all

select 'Form Applet Pick Applet' attr_type, '' screen, '' viewname, a.name obj_name, c.PICK_APPLET_NAME attr_name, '' Applet_Mode from siebel.s_control c, siebel.s_applet a, siebel.s_repository r
where c.PICK_APPLET_NAME like :applet||decode(:wc,'Y','%','') and c.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and c.inactive_flg = 'N' and a.inactive_flg = 'N'

union all

select 'Form Applet MVG Applet' attr_type, '' screen, '' viewname, a.name obj_name, c.MVG_APPLET_NAME attr_name, '' Applet_Mode from siebel.s_control c, siebel.s_applet a, siebel.s_repository r
where c.MVG_APPLET_NAME like :applet||decode(:wc,'Y','%','') and c.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and c.inactive_flg = 'N' and a.inactive_flg = 'N'

union all

select 'List Applet Pick Applet' attr_type, '' screen, '' viewname, a.name obj_name, lc.PICK_APPLET_NAME attr_name, '' Applet_Mode from siebel.s_list_column lc, siebel.s_list l, siebel.s_applet a, siebel.s_repository r
where lc.PICK_APPLET_NAME like :applet||decode(:wc,'Y','%','') and lc.list_id = l.row_id and l.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and lc.inactive_flg = 'N' and a.inactive_flg = 'N'

union all

select 'List Applet MVG Applet' attr_type, '' screen, '' viewname, a.name obj_name, lc.MVG_APPLET_NAME attr_name, '' Applet_Mode from siebel.s_list_column lc, siebel.s_list l, siebel.s_applet a, siebel.s_repository r
where lc.MVG_APPLET_NAME like :applet||decode(:wc,'Y','%','') and lc.list_id = l.row_id and l.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and lc.inactive_flg = 'N' and a.inactive_flg = 'N'

union all

select 'Associate Applet' attr_type, '' screen, '' viewname, a.name obj_name, a.ASSOC_APPLET_NAME attr_name, '' Applet_Mode from siebel.s_applet a, siebel.s_repository r
where a.ASSOC_APPLET_NAME like :applet||decode(:wc,'Y','%','') and a.repository_id = r.row_id and r.name = 'Siebel Repository' and a.inactive_flg = 'N'
union all
select 'Applet Toggle' attr_type, '' screen, '' viewname, a.name obj_name, t.APPLET_NAME attr_name, '' Applet_Mode from siebel.S_APPLET_TOGGLE t, siebel.s_applet a, siebel.s_repository r
where t.APPLET_NAME like :applet||decode(:wc,'Y','%','') and t.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and t.inactive_flg = 'N' and a.inactive_flg = 'N'

union all

select 'Applet Toggle' attr_type, s.name screen, v.name viewname, a.name obj_name, t.APPLET_NAME attr_name, vwti.APPLET_MODE_CD Applet_Mode from siebel.s_screen s, siebel.S_SCREEN_VIEW sv, siebel.S_VIEW_WTMPL_IT vwti, siebel.S_VIEW_WEB_TMPL vwt, siebel.s_view v, siebel.S_APPLET_TOGGLE t, siebel.s_applet a, siebel.s_repository r
where t.APPLET_NAME like :applet||decode(:wc,'Y','%','') and vwti.APPLET_NAME = a.name and vwti.VIEW_WEB_TMPL_ID = vwt.row_id and vwt.VIEW_ID = v.row_id and v.name = sv.VIEW_NAME and sv.SCREEN_ID = s.row_id and t.applet_id = a.row_id
and a.repository_id = r.row_id and v.repository_id = r.row_id and s.repository_id = r.row_id and r.name = 'Siebel Repository'
and t.inactive_flg = 'N' and a.inactive_flg = 'N' and vwti.inactive_flg = 'N' and vwt.inactive_flg = 'N' and sv.inactive_flg = 'N' and v.inactive_flg = 'N'
union all
select 'Run Time Event - Object' attr_type, '' screen, '' viewname, rt.EVT_NAME obj_name, rt.OBJ_NAME attr_name, '' Applet_Mode from siebel.S_CT_EVENT rt
where rt.OBJ_NAME like decode(:wc,'Y','%','')||:applet||decode(:wc,'Y','%','') and rt.OBJ_TYPE_CD = 'Applet'

union all

select 'Personalization - Applet Condition' attr_type, '' screen, '' viewname, A.VIS_COND_EXPR obj_name, a.APPLET_NAME attr_name, '' Applet_Mode from siebel.S_CT_APPLET a
where a.APPLET_NAME like decode(:wc,'Y','%','')||:applet||decode(:wc,'Y','%','') and (a.EFF_END_DT is null or a.EFF_END_DT >= sysdate) and (a.EFF_START_DT is null or a.EFF_START_DT <= sysdate)
)

SQL Field Search

noreply@blogger.com (Mik) — Wed, 12 Dec 2012 18:15:00 +0000

This is a Query to find references to a particular field. Just replace the bind variables. Use your imagination in modifying this query to find Profile attributes. The objects searched for are those relevant to most Implementations and do not search more specialized features (such as tree applets for instance). If those features are used, you will need to add another Union to the query. Also, because of the limitations in searching Oracle LONG data types, this query does not search Script. I keep a minimized repository search window open in my Tools session with all the script objects selected so that I can quickly drag the window up and paste in what I am searching for.

var :attr_val = "CMI Failure Id";             -- Field Name or string to search for

var :buscomp = "CMI Quote Simple";    -- Business Component of the relevant object if applicable
var :wc = "Y";                                            -- Trailing Wildcard. N indicates an exact match

select attr_type, obj_name, attr_name, attr_val from (
select 'Applet List Column' attr_type, a.name obj_name, lc.name attr_name, lc.field_name attr_val from siebel.s_list_column lc, siebel.s_list l, siebel.s_applet a, siebel.s_repository r
where lc.field_name like :attr_val||decode(:wc,'Y','%','') and lc.list_id = l.row_id and l.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and lc.inactive_flg = 'N' and a.inactive_flg = 'N' and a.BUSCOMP_NAME = :buscomp

union all

select 'Applet Control' attr_type, a.name obj_name, c.name attr_name, c.field_name attr_val from siebel.s_control c, siebel.s_applet a, siebel.s_repository r
where c.field_name like :attr_val||decode(:wc,'Y','%','') and c.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and c.inactive_flg = 'N' and a.inactive_flg = 'N' and a.BUSCOMP_NAME = :buscomp

union all

select 'Applet User Prop' attr_type, a.name obj_name, up.name attr_name, up.Value attr_val from siebel.S_APPLET_UPROP up, siebel.s_applet a, siebel.s_repository r
where up.Value like '%'||:attr_val||'%' and up.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and up.inactive_flg = 'N' and a.inactive_flg = 'N' and a.BUSCOMP_NAME = :buscomp

union all

select 'Applet Toggle' attr_type, a.name obj_name, t.name attr_name, t.AUTO_TOG_FLD_NAME attr_val from siebel.S_APPLET_TOGGLE t, siebel.s_applet a, siebel.s_repository r
where t.AUTO_TOG_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and t.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and a.inactive_flg = 'N' and t.inactive_flg = 'N' and a.BUSCOMP_NAME = :buscomp

union all

select 'Applet Drilldown - Source' attr_type, a.name obj_name, d.name attr_name, d.SRC_FIELD_NAME attr_val from siebel.S_DDOWN_OBJECT d, siebel.s_applet a, siebel.s_repository r
where d.SRC_FIELD_NAME like :attr_val||decode(:wc,'Y','%','') and d.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository' and d.inactive_flg = 'N' and a.inactive_flg = 'N' and a.BUSCOMP_NAME = :buscomp

union all

select 'Applet Dynamic Drilldown' attr_type, a.name obj_name, d.name attr_name, dd.FIELD_NAME attr_val from siebel.S_DDOWN_DYNDEST dd, siebel.S_DDOWN_OBJECT d, siebel.s_applet a, siebel.s_repository r
where dd.FIELD_NAME like :attr_val||decode(:wc,'Y','%','') and dd.DDOWN_OBJECT_ID = d.row_id
and d.applet_id = a.row_id and a.repository_id = r.row_id and r.name = 'Siebel Repository'
and dd.inactive_flg = 'N' and d.inactive_flg = 'N' and a.inactive_flg = 'N' and a.BUSCOMP_NAME = :buscomp

union all

select 'Join Spec' attr_type, bc.name obj_name, j.name attr_name, js.SRC_FLD_NAME attr_val from siebel.S_JOIN_SPEC js, siebel.S_JOIN j, siebel.s_buscomp bc, siebel.s_repository r
where js.SRC_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and js.JOIN_ID = j.row_id and j.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and j.inactive_flg = 'N' and bc.name = :buscomp

union all

select 'Predefault - Same' attr_type, bc.name obj_name, f.name attr_name, f.PREDEFVAL attr_val from siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r
where f.PREDEFVAL like '%'||:attr_val||'%' and f.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and f.inactive_flg = 'N' and bc.name = :buscomp

union all

select 'Predefault - Other' attr_type, bc.name obj_name, f.name attr_name, f.PREDEFVAL attr_val from siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r
where f.PREDEFVAL like '%'||:buscomp||'.'||:attr_val||'%' and f.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and f.inactive_flg = 'N'

union all

select 'Calculated Field - Same' attr_type, bc.name obj_name, f.name attr_name, f.CALCVAL attr_val from siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r
where f.CALCVAL like '%'||:attr_val||'%' and f.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and f.inactive_flg = 'N' and bc.name = :buscomp

union all

select 'Calculated Field - Other' attr_type, bc.name obj_name, f.name attr_name, f.CALCVAL attr_val from siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r
where f.CALCVAL like '%ParentFieldValue%'||:attr_val||'%' and f.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and f.inactive_flg = 'N'

union all

select 'BC User Prop' attr_type, bc.name obj_name, up.name attr_name, up.VALUE attr_val from siebel.S_BUSCOMP_UPROP up, siebel.s_buscomp bc, siebel.s_repository r
where up.VALUE like '%'||:attr_val||'%' and up.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and up.inactive_flg = 'N' and bc.name = :buscomp

union all

select 'BC User Prop' attr_type, bc.name obj_name, up.name attr_name, up.VALUE attr_val from siebel.S_BUSCOMP_UPROP up, siebel.s_buscomp bc, siebel.s_repository r
where up.NAME like '%'||:attr_val||decode(:wc,'Y','%','') and up.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and up.inactive_flg = 'N' and bc.name = :buscomp

union all

select 'BC User Prop - Child' attr_type, bc.name obj_name, up.name attr_name, up.VALUE attr_val from siebel.S_BUSCOMP_UPROP up, siebel.s_buscomp bc, siebel.s_repository r
where up.NAME like 'Parent%' and up.VALUE like '%'||:buscomp||'%'||:attr_val||'%' and up.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and up.inactive_flg = 'N' and bc.name <> :buscomp

union all

select 'BC User Prop - Child' attr_type, bc.name obj_name, up.name attr_name, up.VALUE attr_val from siebel.S_BUSCOMP_UPROP up, siebel.s_buscomp bc, siebel.s_repository r
where up.NAME like 'Parent%'||:buscomp and up.VALUE like :attr_val||decode(:wc,'Y','%','') and up.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and up.inactive_flg = 'N' and bc.name <> :buscomp

union all

select 'Pick Map - Same' attr_type, bc.name obj_name, f.name attr_name, pm.NAME attr_val from siebel.s_pickmap pm, siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r
where pm.FIELD_NAME like :attr_val||decode(:wc,'Y','%','') and pm.FIELD_ID = f.row_id and f.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and f.inactive_flg = 'N' and pm.inactive_flg = 'N' and bc.name = :buscomp

union all

select 'Pick Map - Other' attr_type, bc.name obj_name, f.name attr_name, pm.NAME attr_val from siebel.s_pickmap pm, siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r, siebel.S_PICKLIST pl
where pm.PICK_FIELD_NAME like :attr_val||decode(:wc,'Y','%','') and pm.FIELD_ID = f.row_id and f.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and r.row_id = pl.repository_id
and f.inactive_flg = 'N' and pm.inactive_flg = 'N' and f.PICKLIST_NAME = pl.name and pl.BUSCOMP_NAME = :buscomp

union all

select 'MVL Primary Id Field' attr_type, bc.name obj_name, mvl.name attr_name, mvl.PRIMEID_FLD_NAME attr_val from siebel.S_MVLINK mvl, siebel.s_buscomp bc, siebel.s_repository r
where mvl.PRIMEID_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and mvl.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and mvl.inactive_flg = 'N' and bc.name = :buscomp

union all

select 'MVL Source Id Field' attr_type, bc.name obj_name, mvl.name attr_name, mvl.SRC_FLD_NAME attr_val from siebel.S_MVLINK mvl, siebel.s_buscomp bc, siebel.s_repository r
where mvl.SRC_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and mvl.BUSCOMP_ID = bc.row_id and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and mvl.inactive_flg = 'N' and bc.name = :buscomp

union all

select 'MVF Destination Field' attr_type, bc.name obj_name, f.name attr_name, mvl.DEST_BC_NAME||'.'||f.DEST_FLD_NAME attr_val from siebel.s_field f, siebel.s_buscomp bc, siebel.s_repository r, siebel.S_MVLINK mvl
where f.DEST_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and f.BUSCOMP_ID = bc.row_id and mvl.NAME = f.MVLINK_NAME and mvl.BUSCOMP_ID = bc.row_id and mvl.DEST_BC_NAME = :buscomp
and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and f.inactive_flg = 'N'

union all

select 'Link - Destination' attr_type, l.name obj_name, '' attr_name, l.DST_FLD_NAME attr_val from siebel.s_link l, siebel.s_repository r
where l.DST_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and l.CHILD_BC_NAME = :buscomp and l.repository_id = r.row_id and r.name = 'Siebel Repository' and l.inactive_flg = 'N'

union all

select 'Link - Search Spec' attr_type, l.name obj_name, '' attr_name, l.SRCHSPEC attr_val from siebel.s_link l, siebel.s_repository r
where l.SRCHSPEC like '%'||:attr_val||decode(:wc,'Y','%',']%') and l.CHILD_BC_NAME = :buscomp and l.repository_id = r.row_id and r.name = 'Siebel Repository' and l.inactive_flg = 'N'

union all

select 'Applet - Search Spec' attr_type, a.name obj_name, '' attr_name, a.SRCHSPEC attr_val from siebel.s_applet a, siebel.s_repository r
where a.SRCHSPEC like '%'||:attr_val||decode(:wc,'Y','%',']%') and a.BUSCOMP_NAME = :buscomp and a.repository_id = r.row_id and r.name = 'Siebel Repository' and a.inactive_flg = 'N'

union all

select 'Picklist - Search Spec' attr_type, p.name obj_name, '' attr_name, p.SRCHSPEC attr_val from siebel.S_PICKLIST p, siebel.s_repository r
where p.SRCHSPEC like '%'||:attr_val||decode(:wc,'Y','%',']%') and p.BUSCOMP_NAME = :buscomp and p.repository_id = r.row_id and r.name = 'Siebel Repository' and p.inactive_flg = 'N'

union all

select 'Bus Comp - Search Spec' attr_type, bc.name obj_name, '' attr_name, bc.SRCHSPEC attr_val from siebel.s_buscomp bc, siebel.s_repository r
where bc.SRCHSPEC like '%'||:attr_val||decode(:wc,'Y','%',']%') and bc.NAME = :buscomp and bc.repository_id = r.row_id and r.name = 'Siebel Repository' and bc.inactive_flg = 'N'

union all

select 'Action Set - Conditional Expression' attr_type, cas.name obj_name, ca.name attr_name, ca.COND_EXPR attr_val from siebel.S_CT_EVENT rt, siebel.s_ct_action ca, siebel.s_ct_action_set cas
where rt.CT_ACTN_SET_ID = cas.row_id and ca.CT_ACTN_SET_ID = cas.row_id and ca.COND_EXPR like '%'||:attr_val||decode(:wc,'Y','%',']%') and rt.OBJ_NAME = :buscomp and rt.OBJ_TYPE_CD = 'BusComp'

union all

select 'Action Set - Attribute Set' attr_type, cas.name obj_name, ca.name attr_name, ca.SET_RHS_EXPR attr_val from siebel.S_CT_EVENT rt, siebel.s_ct_action ca, siebel.s_ct_action_set cas
where rt.CT_ACTN_SET_ID = cas.row_id and ca.CT_ACTN_SET_ID = cas.row_id and ca.SET_RHS_EXPR like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%') and rt.OBJ_NAME = :buscomp and rt.OBJ_TYPE_CD = 'BusComp'

union all

select 'Run Time Event - Conditional Expression' attr_type, rt.OBJ_NAME obj_name, rt.EVT_NAME attr_name, rt.ACTN_COND_EXPR attr_val from siebel.S_CT_EVENT rt
where rt.ACTN_COND_EXPR like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%') and rt.OBJ_NAME = :buscomp and rt.OBJ_TYPE_CD = 'BusComp'

union all

select 'Run Time Event - Sub Event' attr_type, rt.OBJ_NAME obj_name, rt.EVT_NAME attr_name, rt.EVT_SUB_NAME attr_val from siebel.S_CT_EVENT rt
where rt.EVT_SUB_NAME like :attr_val||decode(:wc,'Y','%','') and rt.OBJ_NAME = :buscomp and rt.OBJ_TYPE_CD = 'BusComp'

union all

select 'Integration Component Field' attr_type, io.name obj_name, ic.name attr_name, icf.NAME attr_val from siebel.S_INT_FIELD icf, siebel.S_INT_COMP ic, siebel.S_INT_OBJ io, siebel.s_repository r
where icf.EXT_NAME like :attr_val||decode(:wc,'Y','%','') and icf.INT_COMP_ID = ic.row_id and ic.INT_OBJ_ID = io.row_id and io.repository_id = r.row_id and r.name = 'Siebel Repository'
and io.inactive_flg = 'N' and ic.inactive_flg = 'N' and icf.inactive_flg = 'N' and ic.EXT_NAME = :buscomp

union all

select 'IO Maps - Source Fields' attr_type, o.name obj_name, C.name attr_name, F.SRC_EXPR attr_val from siebel.S_INT_FLDMAP F, siebel.S_INT_COMPMAP C, siebel.S_INT_OBJMAP O, siebel.S_INT_OBJ IO,
siebel.S_INT_COMP IC, siebel.S_INT_FIELD ICF, siebel.s_repository r
where C.int_obj_map_id = O.row_id and F.int_comp_map_id = C.row_id and F.SRC_EXPR like '%'||ICF.NAME||'%' and IC.EXT_NAME like decode(:bcwc,'Y','%','')||:buscomp||decode(:bcwc,'Y','%','') and O.src_int_obj_name = IO.NAME and c.src_int_comp_name = IC.NAME
and ICF.ext_name = :attr_val||decode(:wc,'Y','%','') and IO.repository_id = r.row_id and r.name = 'Siebel Repository' and ICF.INT_COMP_ID = IC.row_id
and IC.INT_OBJ_ID = IO.row_id and io.inactive_flg = 'N' and ic.inactive_flg = 'N' and icf.inactive_flg = 'N' and (O.COMMENTS is null or O.COMMENTS not like 'ARCHIVE%')

union all

select 'IO Maps - Destination Fields' attr_type, o.name obj_name, C.name attr_name, F.dst_int_fld_name attr_val from siebel.S_INT_FLDMAP F, siebel.S_INT_COMPMAP C, siebel.S_INT_OBJMAP O, siebel.S_INT_OBJ IO,
siebel.S_INT_COMP IC, siebel.S_INT_FIELD ICF, siebel.s_repository r
where C.int_obj_map_id = O.row_id and F.int_comp_map_id = C.row_id and F.dst_int_fld_name = ICF.NAME and IC.EXT_NAME like decode(:bcwc,'Y','%','')||:buscomp||decode(:bcwc,'Y','%','') and O.dst_int_obj_name = IO.NAME and c.dst_int_comp_name = IC.NAME
and ICF.ext_name = :attr_val||decode(:wc,'Y','%','') and IO.repository_id = r.row_id and r.name = 'Siebel Repository' and ICF.INT_COMP_ID = IC.row_id
and IC.INT_OBJ_ID = IO.row_id and io.inactive_flg = 'N' and ic.inactive_flg = 'N' and icf.inactive_flg = 'N' and (O.COMMENTS is null or O.COMMENTS not like 'ARCHIVE%')

union all

select 'Data Maps - Source Fields' attr_type, o.name obj_name, C.name attr_name, f.src_fld_name attr_val from siebel.S_FIELD_DMAP F, siebel.S_BUSCOMP_DMAP C, siebel.S_BUSOBJ_DMAP O
where C.BUSOBJ_DMAP_ID = O.row_id and F.BUSCOMP_DMAP_ID = C.row_id and F.SRC_FLD_NAME like '%'||:attr_val||'%' and c.SRC_BUSCOMP_NAME = :buscomp

union all

select 'Data Maps - Destination Fields' attr_type, o.name obj_name, C.name attr_name, f.DST_FLD_NAME attr_val from siebel.S_FIELD_DMAP F, siebel.S_BUSCOMP_DMAP C, siebel.S_BUSOBJ_DMAP O
where C.BUSOBJ_DMAP_ID = O.row_id and F.BUSCOMP_DMAP_ID = C.row_id and F.DST_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and c.DST_BUSCOMP_NAME = :buscomp

union all

select 'WF Branch Criteria - Field' attr_type, wf.name obj_name, s.name attr_name, b.name attr_val from siebel.S_WFR_COND_CRIT cc, siebel.S_WFR_STP_BRNCH b, siebel.S_WFR_STP s, siebel.S_WFR_PROC wf, siebel.s_repository r
where cc.BUSCOMP_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and cc.BRANCH_ID = b.row_id and b.STEP_ID = s.row_id and s.PROCESS_ID = wf.row_id and wf.repository_id = r.row_id and r.name = 'Siebel Repository'
and wf.STATUS_CD = 'COMPLETED' and cc.BUSCOMP_NAME = :buscomp

union all

select 'WF Branch Criteria - Expression' attr_type, wf.name obj_name, s.name attr_name, b.name attr_val from siebel.S_WFR_COND_VAL cv, siebel.S_WFR_COND_CRIT cc, siebel.S_WFR_STP_BRNCH b, siebel.S_WFR_STP s, siebel.S_WFR_PROC wf, siebel.s_repository r
where cv.LO_CHAR5 like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%') and cv.COND_CRIT_ID = cc.row_id and cc.BRANCH_ID = b.row_id and b.STEP_ID = s.row_id and s.PROCESS_ID = wf.row_id and wf.repository_id = r.row_id and r.name = 'Siebel Repository'
and wf.STATUS_CD = 'COMPLETED' and cc.BUSCOMP_NAME = :buscomp

union all

select 'WF Branch Step - Argument' attr_type, wf.name obj_name, s.name attr_name, a.name attr_val from siebel.S_WFR_STP_ARG a, siebel.S_WFR_STP s, siebel.S_WFR_PROC wf, siebel.s_repository r
where a.BUSCOMP_FLD_NAME like :attr_val||decode(:wc,'Y','%','') and a.STEP_ID = s.row_id and s.PROCESS_ID = wf.row_id and wf.repository_id = r.row_id and r.name = 'Siebel Repository'
and wf.STATUS_CD = 'COMPLETED' and a.BUSCOMP_NAME = :buscomp

union all

select 'DVM - Rule' attr_type, rs.name obj_name, r.name attr_name, r.RULE_EXPR attr_val from siebel.S_VALDN_RULE R, siebel.S_VALDN_RL_SET RS
where R.RULE_SET_ID = rs.row_id and r.RULE_EXPR like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%') and r.BUSCOMP_NAME = :buscomp and rs.status_cd = 'Active'

union all

select 'Personalization - Applet Condition' attr_type, a.APPLET_NAME obj_name, a.APPLET_NAME attr_name, A.VIS_COND_EXPR attr_val from siebel.S_CT_APPLET a, siebel.S_APPLET ra, siebel.s_repository r
where a.APPLET_NAME = ra.name and a.VIS_COND_EXPR like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%') and r.name = 'Siebel Repository' and ra.repository_id = r.row_id and ra.inactive_flg = 'N'
and ra.buscomp_name like decode(:bcwc,'Y','%','')||:buscomp||decode(:bcwc,'Y','%','') and ra.INACTIVE_FLG = 'N' and (a.EFF_END_DT is null or a.EFF_END_DT >= sysdate) and (a.EFF_START_DT is null or a.EFF_START_DT <= sysdate)

union all

select 'Personalization - Applet Rule Set' attr_type, a.APPLET_NAME obj_name, to_char(ar.SEQ_NUM) attr_name, ar.COND_EXPR attr_val from siebel.S_CT_APPLET a, siebel.S_CT_APLT_RLST ar, siebel.S_APPLET ra, siebel.s_repository r
where a.APPLET_NAME = ra.name and ar.ct_applet_id = a.row_id and ar.COND_EXPR like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%') and r.name = 'Siebel Repository' and ra.inactive_flg = 'N' and ra.repository_id = r.row_id
and ra.buscomp_name like decode(:bcwc,'Y','%','')||:buscomp||decode(:bcwc,'Y','%','') and ra.INACTIVE_FLG = 'N' and (a.EFF_END_DT is null or a.EFF_END_DT >= sysdate) and (a.EFF_START_DT is null or a.EFF_START_DT <= sysdate)
and (ar.EFF_END_DT is null or ar.EFF_END_DT >= sysdate) and (ar.EFF_START_DT is null or ar.EFF_START_DT <= sysdate)

union all

select 'Personalization - Rule Condition' attr_type, rs.name obj_name, r.Name attr_name, r.COND_EXPR attr_val from siebel.S_CT_RULE R, siebel.S_CT_RULE_SET RS, siebel.S_CT_APPLET a, siebel.S_CT_APLT_RLST ar, siebel.S_APPLET ra, siebel.s_repository rep
where a.APPLET_NAME = ra.name and ar.ct_applet_id = a.row_id and rep.name = 'Siebel Repository' and ra.repository_id = rep.row_id and ra.inactive_flg = 'N' and rs.row_id = ar.ct_rule_set_id
and R.CT_RULE_SET_ID = rs.row_id and r.COND_EXPR like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%')
and ra.BUSCOMP_NAME = :buscomp and rs.ACTIVE_FLG = 'Y' and r.ACTIVE_FLG = 'Y' and ra.INACTIVE_FLG = 'N' and (a.EFF_END_DT is null or a.EFF_END_DT >= sysdate) and (a.EFF_START_DT is null or a.EFF_START_DT <= sysdate)
and (ar.EFF_END_DT is null or ar.EFF_END_DT >= sysdate) and (ar.EFF_START_DT is null or ar.EFF_START_DT <= sysdate)

union all

select 'Personalization - Rule Include Condition' attr_type, rs.name obj_name, r.Name attr_name, r.INCL_RULE_EXPR attr_val from siebel.S_CT_RULE R, siebel.S_CT_RULE_SET RS, siebel.S_CT_APPLET a, siebel.S_CT_APLT_RLST ar, siebel.S_APPLET ra, siebel.s_repository rep
where a.APPLET_NAME = ra.name and ar.ct_applet_id = a.row_id and rep.name = 'Siebel Repository' and ra.repository_id = rep.row_id and ra.inactive_flg = 'N' and rs.row_id = ar.ct_rule_set_id
and R.CT_RULE_SET_ID = rs.row_id and r.INCL_RULE_EXPR like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%')
and ra.BUSCOMP_NAME = :buscomp and rs.ACTIVE_FLG = 'Y' and r.ACTIVE_FLG = 'Y' and ra.INACTIVE_FLG = 'N' and (a.EFF_END_DT is null or a.EFF_END_DT >= sysdate) and (a.EFF_START_DT is null or a.EFF_START_DT <= sysdate)
and (ar.EFF_END_DT is null or ar.EFF_END_DT >= sysdate) and (ar.EFF_START_DT is null or ar.EFF_START_DT <= sysdate)

union all

select 'Personalization - Rule Exclude Condition' attr_type, rs.name obj_name, r.Name attr_name, r.EXCL_RULE_EXPR attr_val from siebel.S_CT_RULE R, siebel.S_CT_RULE_SET RS, siebel.S_CT_APPLET a, siebel.S_CT_APLT_RLST ar, siebel.S_APPLET ra, siebel.s_repository rep
where a.APPLET_NAME = ra.name and ar.ct_applet_id = a.row_id and rep.name = 'Siebel Repository' and ra.repository_id = rep.row_id and ra.inactive_flg = 'N' and rs.row_id = ar.ct_rule_set_id
and R.CT_RULE_SET_ID = rs.row_id and r.EXCL_RULE_EXPR like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%')
and ra.BUSCOMP_NAME = :buscomp and rs.ACTIVE_FLG = 'Y' and r.ACTIVE_FLG = 'Y' and ra.INACTIVE_FLG = 'N' and (a.EFF_END_DT is null or a.EFF_END_DT >= sysdate) and (a.EFF_START_DT is null or a.EFF_START_DT <= sysdate)
and (ar.EFF_END_DT is null or ar.EFF_END_DT >= sysdate) and (ar.EFF_START_DT is null or ar.EFF_START_DT <= sysdate)

union all

select 'Batch Job - Search' attr_type, a.DISPLAY_NAME obj_name, wf.NAME attr_name, ps.VALUE attr_val
from siebel.S_SRM_REQUEST q, siebel.S_SRM_ACTION a, siebel.S_SRM_REQ_PARAM ps, siebel.S_SRM_ACT_PARAM psp, siebel.S_SRM_REQ_PARAM pw, siebel.S_SRM_ACT_PARAM pwp, siebel.S_WFR_PROC wf, siebel.s_repository r, siebel.S_BUSOBJ bo
where ps.REQ_ID = q.row_id and ps.VALUE like decode(:wc,'Y','%','%[')||:attr_val||decode(:wc,'Y','%',']%') and q.STATUS = 'ACTIVE' AND q.REQ_TYPE_CD = 'RPT_PARENT' and ps.ACTPARAM_ID = psp.ROW_ID
and psp.NAME = 'Search Specification' and pw.REQ_ID = q.row_id and pw.ACTPARAM_ID = pwp.ROW_ID and pwp.NAME = 'Workflow Process Name' and a.row_id = q.action_id and pw.value = wf.PROC_NAME and wf.REPOSITORY_ID = r.row_id
and r.name = 'Siebel Repository' and bo.repository_id = r.row_id and wf.STATUS_CD = 'COMPLETED' and wf.BUSOBJ_NAME = bo.name and bo.PR_BUSCOMP_NAME like decode(:bcwc,'Y','%','')||:buscomp||decode(:bcwc,'Y','%','')
);

My BI Quick Reference

noreply@blogger.com (Mik) — Tue, 03 Jan 2012 15:55:00 +0000

I know there are a bunch of cheat sheets out there, but I frequently don't find everything I am looking for in one place so figured I would just start building my own.

Siebel Functions

Include statement

<?namespace:psfn=http://www.oracle.com/XSL/Transform/java/com.siebel.xmlpublisher.reports.XSLFunctions?>

Date Conversion

<?psfn:totext(OrderDate,"MM/dd/yyyy","MM/dd/yyyy hh:mm:ss")?>

Loops:

Basic Loop where QuoteItem is the Integration Component/XML Group

<?for-each:QuoteItem?>

Add a where clause to constrain rows in the loop. Multiple constraints can be added back to back with each bracketed section representing an AND. An OR would need to be done inside a single bracketed expression. the .// is an xpath expression to determine the XML group of the field

<?for-each:QuoteItem[.//LineType='Sales']?>
<?for-each:QuoteItem[.//LineType='Sales'][.//LineNumber<100]?>
<?for-each:QuoteItem[.//LineType='Sales' or .//LineType='Service']?>

Groupings

Loop that groups by the column LineType and sorts by the grouping

<?for-each-group:QuoteItem;./LineType?><?sort:current-group()/LineType;'ascending';data-type='text'?>

A nested loop showing the sub group records for the loop above:

<?for-each:current-group()?><?sort:Product;'ascending';data-type='text'?>

Loop that groups records together by a particular column and makes each grouping a column in a table

<?for-each-group@column:QuoteItem;./LineType?>

Similar to grouping by column is to group by section. In this case each grouping creates a heading when the report breaks across multiple pages.

<?for-each@section:G_CUSTOMER?>

Group Expressions

This expression is used to sum a column from series of records outside the context of a loop. The expression in the bracket specifies those records to include, in this case only non null values. This is an XPath expression.

<?sum(.//ItemExtendedPriceTotal[.!=''])?>

Conditionals:

If/else

<?if:Comment!=''?>
<?Comment?>
<?end if?>

Keep in mind that Carriage returns outside the expression will still appear so consider this when judging where to put the donditional

Switch/Case/Select/Choose:

<?choose:?> <?when:MY_FIELD='value tested'?> <?call:template?> <?end when?> <?when:MY_FIELD_2='value tested 2'?> <?call:template_2?> <?end when?> <?otherwise:?> <?call:template_other?> <?end otherwise?> <?end choose?>

Embedding a 64 Bit Image

For this to work, there must be a 64 Bit attachment embedded in the Integration Object being sent to BI. In this example,QuoteAttachment is the element name of the IC Field of type DTYPE_ATTACHMENT containing the 64 Bit inline image.

<fo:instream-foreign-object content-type="image/jpg"><?QuoteAttachment?></fo:instream-foreign-object>

Additional attributes for the fo:instream-foreign-object tag in addition to content-type can resize the image: Specify in pixels as follows:

<fo:instream-foreign-object content type="image/jpg" height="300 px" width="4 px">

... or in centimeters:

<fo:instream-foreign-object content type="image/jpg" height="3 cm" width="4 cm">

... or as a percentage of the original dimensions:

<fo:instream-foreign-object content type="image/jpg" height="300%" width="300%"> ...

Performance Tuning Methodology

noreply@blogger.com (Mik) — Tue, 13 Sep 2011 17:19:00 +0000

I recently had an opportunity to do a bit of performance tuning on a newly deployed production App and thought I would share a methodology for tackling some of the low hanging fruit, sort of the 80/20 rule of siebel performance tuning. My experience is that with Siebel 7.8 and higher, on Oracle 10 and higher, most performance issues are Siebel configuration issues. Of those, most of the issues fall into one of two categories:

Missing Indexes
Sort Specs

When customizing Siebel, you will frequently create new relationships between logical objects via a new foreign key. There should always be a corresponding index for that foreign key on the M side of the 1:M or M:M linked table. Typically, it is just a single Id column but if for some reason, there are multiple columns (perhaps a join spec and a join constraint) make sure all of the columns from the child table are part of the index.

Be aware that all the perfectly planned indexes in the world will frequently be ignored if there is a sort spec on a query. The sort essentially takes precedence and any index that optimizes the sort will usually be used to the exclusion of other indexes that perhaps optimize what a user is doing on that view. I frequently see performance problems on visibility views (All/All Across) without any query refinement at all. When this occurs, it is usually because of the All Mode Sort user property settings. If you are seeing performance problems on an All view, try changing the settings of that property for the BC to fix the issue.

Here is a general methodology for identifying and fixing performance issues.

For the OM component having the issue, change the Server Configuration event logging level to 4 for these events:

SQL Parse and Execute
Object Manager SQL Log

Execute the operation that performs the slow query
Open the corresponding OM log and find the SQL statement representing the slow query
Finding the statement can be done in a couple of ways, but I use the following:

Query for this string in the log '***** SQL Statement Execute Time'
Look at the seconds for this line and the subsequent '***** SQL Statement Initial Fetch Time' to see a large value

Copy the preceeding SQL statement into a SQL editor such as Toad or Benthic or whatever your fancy, swapping out the bind variables
Run an explain plan on the statement

Look for a line that says Table Access Full. If you see such a line, look at the table being accessed this way and look back at the where clause to see how the SQL statement is joining to that table. Then look in tools to see if there is an index for that table on the columns in the join to that table.
If indexes are not an issue, but there is an Order By in the SQL statement, try commenting out the Order By and rerunning the explain plan to see how it changes. If you see the explain plan change significantly (Cost goes down) than confirm that you really need the sort spec in the particular scenario you are in.

This is really just meant to be a way to find low hanging fruit performance issues. It is important to configure with performance in mind (especially when using script or workflow algoritms). Other sources of performance bottlenecks include (but are not limited to):

Synchonous Interface round trips
File System operations
Network bandwidth (especially if using a VPN)
Memory or CPU bound servers

eScript Framework on 8.1

noreply@blogger.com (Mik) — Mon, 12 Sep 2011 22:38:00 +0000

Converting the eScript framework to 8.1 proved a bit troublesome for me as the Siebel strong type engine has apparently dropped support for prototyping Siebel objects, such as a Business Service. This makes the implementation a bit less clean since without being able to declare a prototype of the Log or Frame objects on application start, we are left with having to have every framework function be a child of the Application object. This being the case, I consolidated the Frame and Log objects from the 7.8 framework into a single Utility object since there was not as much advantage in separating them. Instead of the elegant 7.8 calls:

Log.Stack("MyMethod",1);
Log.Step("Log the time as "+Frame.Timestamp("DateTimeMilli"),3);
Log.Vars("VariableName", varValue,3)
Log.Unstack("",1);

we instead do this:

TheApplication().logStack("Write",this)
TheApplication().Utility.logStep("Log the time as "+
    TheApplication().Utility.Timestamp("DateTimeMilli"));
TheApplication().Utility.logVars("VariableName", varValue)
TheApplication().Unstack("");

Oh well. To mitigate this somewhat, I have added a number of enhancements since the initial series of posts, which I will try to discuss sometime soon.

Automatically tie log level to the function doing the logging (Stack/Unstack vs variables for instance), hence no need for the numeric last parameter to all logging functions (though it is still optional as an override)
Added support for unix file systems
Standardize the identification of logging record Ids (by passing the 'this' reference it will append the row id for methods with Write, Delete and Invoke in the name)

To implement the basic framework in 8.1, you need something like this in the Application Start event:
this.Utility = TheApplication().GetService("ETAR Utilities");
this.Utility.Init();

Here is the Declarations section:

var gsOutPutFileName;
var gsFileName;
var gsLogMode;
var giIndent = 2; //Indent child prop sets this many spaces to the right for each level down.
var giPSDepth = 0; // How deep in the property set tree, what level
var gaFunctionStack = new Array(); //used in debugStack function to store called functions
var giStackIndex = 0; //Where in the function stack the current function resides
var gsIndent = ''; //used in debug methods to identify stack indents
var giLogBuffer = 0;
var giLogLines = 0;
var gsLogPath = "";
var gsLogCache = "";
var gsLogSession = "";
var giErrorStack = 0;
var ge = new Object();
var gStack = new Object();
var gCurrentLogLvl;

The Utilities business service is a cached service in tools. It's Init function looks like this:

giErrorStack = 0;
ExtendObjects();
gsLogMode = GetSysPref("Framework Log Mode");
gsLogMode = (gsLogMode == "" ? "FILE" : gsLogMode.toUpperCase());
gsLogSession = TimeStamp("DateTimeMilli");

if (TheApplication().GetProfileAttr("ETAR User Log Level") != "")
    gCurrentLogLvl = TheApplication().GetProfileAttr("ETAR User Log Level");
else gCurrentLogLvl = GetSysPref("CurrentLogLevel");
giLogBuffer = GetSysPref("Log Buffer");
gsLogPath = GetSysPref("Framework Log Path");
try {
     var os;
     os = Clib.getenv("OS");
} catch(e) { os = "UNIX Based"; }
try {
   gsFileName = "Trace-"+TheApplication().LoginName()+"-"+gsLogSession+".txt"
  //A Windows OS indicates a thick client. Assume the path is the dynamicly
  //determined Siebel_Home\Log directory, or ..\log
  if (os.substring(0, 7) == "Windows") {
//  gsLogPath = gsLogPath.replace(/\\$/, "");  //Remove trailing backslash if used
//  gsLogPath = gsLogPath.replace(/\x47/, "\\");  //switch invalid OS directory seperators
    gsLogPath = "..\\Log\\";
    gsOutPutFileName = gsLogPath+gsFileName;
  } else {
    gsLogPath = gsLogPath.replace(/\x47$/, "");  //Remove trailing backslash if used
    gsLogPath = gsLogPath.replace(/\\/, "/");  //switch invalid OS directory seperators
    gsLogPath = gsLogPath+"/";
    gsOutPutFileName = gsLogPath+gsFileName;
 }
} catch(e) {
  gsLogPath = "";
  gsOutPutFileName = gsFileName;
}

Tools Bleg

noreply@blogger.com (Mik) — Tue, 29 Mar 2011 13:38:00 +0000

Don't get me wrong. I love Siebel Tools. Compared to other enterprise systems where development for the most part involves modifying script, Siebel has a very elegant development platform. OK, all that being said, after developing in Tools for over eleven years (odd writing that), there are some things I would love to do better to make my development experience more efficient. So to that end I thought I would put some thoughts out into the cloud to see if anyone has thought of a workaround for any of these items:

Column Preferences. Is it just me or does the Tools client not save preferences the way the Siebel client does. Rearranging columns usually works, but changing widths do not seem to save.
PDQs. The idea of Bookmarks is nice but I hate the fact that drilling down or using them loses the context of my exporer pane when I go back. PDQs on every object like within the Siebel Client (and the ability to set default PDQs for each view) would do wonders.
Drilldowns. Speaking of drilldowns, is it really necessary for drilling down to collapse the rest of my explorer pane, hence refreshing all the queries on other objects?
Expose Tab Order on Applets. I am tempted to try this one out myself one day because it seems doable. Who knows.
Applet Wizard. Not for creating a new one. That is ok. But to synchronize with a BC down the road when I want to add a new column. A wizard would just be a much easier way to add a new column rather than adding a control or list column, then adding it to the web template.
Allow sync of Meta Data needed by Tools without Remote Sync. This might be a bit more out there but I find it annoying that Users (Help about record) and LOVs cannot be synced with a 'Get'. I know you can get them with a remote sync, but more and more, a lot of client's do not use Remote or use it so infrequently that it is not emphasized and it is a pain to keep my remote client in sync with the server in a development environment anyway. This might sound minor, but like I said, it annoys me.

I have mainly limited this list to just applying functionality that already exists in the Siebel Client or to exposing data which I am pretty sure is there to be exposed. Not really trying to create a forum for adding "New" features. I may add to this list in the future, but feel free to add your own wishes/solutions in comments.

Building a BI Developer's SuperView

noreply@blogger.com (Mik) — Thu, 20 Jan 2011 14:36:00 +0000

Another limitation I find irritating when it comes to building BI templates is how basic the sample file generator is. My main beef is that it just takes the first 10 records in the Integration Object and spits them out. If you have a complicated IO with child ICs, it is possible, and even likely that those first ten records do not have the child detail records you need to test your report output. There are some ways around this, like hard coding a search spec on the BC against a thick client partial compile to generate a file with data you want, but that seems so inelegant. My other tick regarding this feature is that the report developer once again either needs to have a Siebel thick client or access to the Siebel Server file system to actually get the xml file produced. It seems like the whole point of all the BI Administration views is to avoid having to go to the file system. What to do...

Caveat Emptor. Configuration steps below are to give you an idea. I am posting this after I finished to highlight what I recall as the important pieces so not every step is included. You will need to create the new custom table CX_TMPL (or use another), create all links, applets, view objects, make BO/Screen changes and deploy them.

First I build a view with the same IO BC based applet as the vanilla view on top, and child applets for both attachments and a new object which is essentially a search spec. First the attachment bc. This is a new BC which you can copy from an existing attachment BC and change the names around. Here is mine, called 'Sample IO Attachment' based on S_FILE_ATT. Use the field name prefix 'Sample' instead of which ever prefix is used on the BC you are copying (Be sure to set the User Property DefaultPrefix to 'Sample' too):

Name	Join	Column	Force Active	Predefault Value	Text Length	Type
IO Id		PAR_ROW_ID	Y	Parent: 'Repository Integration Object.Id'	15	DTYPE_ID
Parent Key		X_PARENT_KEY		Parent: 'Repository Integration Object.Name'	100	DTYPE_TEXT

The Search Spec applet is based on a custom BC, 'Report IO Sample File Template', based on the new table, CX_TMPL (I use this table for other things too so I type spec each record):

Name	Join	Column	Force Active	Predefault Value	Text Length	Type
Name		NAME		Field: "Id"	100	DTYPE_TEXT
Parent Id	S_INT_OBJ	ROW_ID	Y	Parent: "Repository Integration Object.Id"	15	DTYPE_ID
Parent Name		PARENT_FLD		Parent: "Repository Integration Object.Name"	50	DTYPE_TEXT
Search Specification		CONSTRAINT	Y		250	DTYPE_TEXT
Type		TYPE		SAMPLE_IO_CONSTRAINT	30	DTYPE_TEXT
Number of Records		LN_NUM		10		DTYPE_INTEGER

The join, S_INT_OBJ, is based on the specification of Parent Name = NAME. Using name instead of Id allows the search specs to remain visible after repository moves.

You will also need the following Named Method User Property:

"GenerateConstrainedData", "INVOKESVC", "Report IO Sample File Template", "Workflow Process Manager", "RunProcess", "'ProcessName'", "'Export Sample IO To File'", "SearchConstraint", "[Search Specification]", "'IOName'", "[Parent Name]", "Path", "'..\XMLP\Data'", "Object Id", "[Parent Id]", "PageSize", "[Number of Records]"

This user property is to activate the button you will need to place on the applet based on this BC. On that applet (based on class CSSSWEFrameListBase), add a button which invokes the method 'GenerateConstrainedData'. No additional script should be needed there.

Create a Service Flow Workflow Process called 'Export Sample IO To File'

Here are the Process Properties:

Name	In/Out	Data Type
FileName	In	String
IOName	In	String
PageSize	In	String
Path	In	String
SearchConstraint	In	String
SiebelMessage	None	Hierarchy
ViewMode	In	String

The first 'Echo' step is a Business Service based on Workflow Utilities, Echo method. This step sets up all the variables used later in the process. Here are the arguments:

I/O	Argument	Type	Value/Property Name
Input	IOName	Process Property	IOName
Input	PageSize	Process Property	PageSize
Input	Path	Process Property	Path
Input	SearchConstraint	Process Property	SearchConstraint
Input	ViewMode	Process Property	ViewMode
Output	FileName	Expression	IIF([&FileName] is not null, [&FileName], [&IOName])

The next 'Export IO' step is a Business Service based on EAI Siebel Adapter, QueryPage method. This step queries the integration object. Here are the arguments:

I/O	Argument	Type	Value/Property Name
Input	OutputIntObjectName	Process Property	IOName
Input	PageSize	Process Property	PageSize
Input	SearchSpec	Process Property	SearchConstraint
Input	ViewMode	Process Property	ViewMode
Output	SiebelMessage	Output Argument	SiebelMessage

The next 'Write to File' step is the Business Service, EAI XML Write to File, WriteEAIMsg method. This step writes the property set out as an XML document to the file system. Here are the arguments:

I/O	Argument	Type	Value/Property Name
Input	FileName	Expression	[&Path]+"\"+[&Process Instance Id]+"_"+[&FileName]+".xml"
Input	SiebelMessage	Process Property	SiebelMessage

The final 'Attach' step is another Business Service, this one custom. The basic logic here is to add an Attachment to the file system which is first described in Oracle document 477534.1 (I have made some improvements which I will perhaps discuss another day). Here are the arguments:

I/O	Argument	Type	Value/Property Name
Input	AttBusinessComponent	Literal	Sample IO Attachment
Input	AttachmentFieldName	Literal	SampleFileName
Input	BusinessObject	Literal	Repository Integration Object
Input	File	Expression	[&Path]+"\"+[&Process Instance Id]+"_"+[&FileName]+".xml"
Input	ObjectId	Process Property	Object Id
Input	PrimaryBusinessComponent	Literal	Repository Integration Object

BI - Upload Limitation

noreply@blogger.com (Mik) — Wed, 19 Jan 2011 20:35:00 +0000

I have recently been designated the BI technical resource on my project so am looking at the BI capabilities (on 7.8) for the first time. Despite a fairly complicated and mistake laden patch upgrade which I do not even want to get into, it is a pretty powerful tool, much better architected than Actuate. Anyway, there are also some pretty glaring limitations as well on how it is administered that require so little effort to fix, I decided to just go ahead and fix them.

My main beef is that the architecture requires your BI report developer to have access to both the BI file system and the Siebel Server file system. I suppose you could set this up in a way that minimizes security risk, but it just seems so unnecessary. Essentially, to upload a new BI Report Template, the developer creates a record in the BI Report Template administration view, attaches the two template files (an RTF and an XLF) and clicks the upload button. So far, so good. The problem is that these template files must also exist in a specific place in the Siebel Server file system as well to generate a report. But the code behind that button does not take the extra step to just copy the files to where they need to go. Also, there is an existing product defect where modifications to an existing report record require the developer to go into the BI File system and delete the entire directory containing that report template. So that is where I step in.

First I added two new System Parameters indicating the locations of the BI and Siebel home directories. There is a way to grab environment variables through script but I did not feel like investigating this so let's call that phase II. For example, here are my two:

Name	Value
BIHomeDirectory	E:\OraHome
SiebelHomeDirectory	E:\sea78\siebsrvr

Then, we need to trap the call to upload the templates file. This call is performed from 'Report Template BC' by the 'Upload' method. We need to always delete the directory before this upload is called. We also want to delete the existing template file from the Siebel server file system. Here is a script to place in the PreInvoke method to accomplish that (there are also some references to the Log and Frame objects):


switch (MethodName) {
  case "Upload":
    try {
      Log.StartStack("Business Component", this.Name()
        +".PreInvoke", MethodName, 1);
      this.WriteRecord();
      var sReturn, sCommand;
      var sSiebel = Frame.GetSysPref("SiebelHomeDirectory")
        +"\\XMLP\\TEMPLATES";
      var sPath = Frame.GetSysPref("BIHomeDirectory");
      var sFile = this.GetFieldValue("ReportTmplFileName")
        +"."+this.GetFieldValue("ReportTmplFileExt");

      sPath = sPath
        +"\\XMLP\\XMLP\\Reports\\SiebelCRMReports\\"
        +this.GetFieldValue("Report Name");
      Log.stepVars("BI Report Path", sPath, 3);

      sCommand = 'rmdir "'+sPath+'" /S /Q';
      sReturn = Clib.system(sCommand);
      Log.stepVars("Command",sCommand,"Success?",sReturn,3);

      sCommand = 'del "'+sSiebel+'\\'+sFile+'"';
      sReturn = Clib.system(sCommand);
      Log.stepVars("Command",sCommand,"Success?",sReturn,3);
    } catch(e) {
      Log.RaiseError(e);
    } finally {
      Log.Unstack("", 1);
    }
    break;
}
return (ContinueOperation);

Ok. That addresses the product defect for updates. Now the second part is to copy these template files to the Siebel server file system once the template files are uploaded. The following script can be added to the InvokeMethod event:


switch (MethodName) {
  case "Upload":
    try {
      Log.StartStack("Business Component", this.Name()
        +".Invoke", MethodName, 1);
      var sReturn, sCommand;
    
      var sSiebel = Frame.GetSysPref("SiebelHomeDirectory")+
        "\\XMLP\\TEMPLATES";
      var sPath = Frame.GetSysPref("BIHomeDirectory");
      var sFile = this.GetFieldValue("ReportTmplFileName")
        +"."+this.GetFieldValue("ReportTmplFileExt");

      sPath = sPath
        +"\\XMLP\\XMLP\\Reports\\SiebelCRMReports\\"
        +this.GetFieldValue("Report Name");
      Log.stepVars("Source Path",sPath,"Target Path", 
        sSiebel,"File to copy",sFile, 3);
      sCommand = 'copy "'+sPath+'\\'+sFile+'" "'+sSiebel
        +'\\'+sFile+'"';
      sReturn = Clib.system(sCommand); 
      Log.stepVars("Command",sCommand,"Success?",sReturn,3);
    } catch(e) {
      Log.RaiseError(e);
    } finally {
      Log.Unstack("", 1);
    }
    break;
}

And there you go.

ADM - List Of Values

noreply@blogger.com (Mik) — Fri, 07 Jan 2011 13:01:00 +0000

There are plenty of posts on support web discussing the issues with migrating LOVs, but for my own sanity, I thought I would summarize all of the relevant issues in one place.

First, we need to address the defects. These are documented on support in Document 731411.1 but I will summarize here:

(1) Go to BC 'List Of Values Child (UDA)'
(2) Add a new field 'Parent Type' based on Join 'Parent LOV' and Column 'TYPE' with Text Length '30'.
(3) Expand the pickmap for the 'Parent' field. Replace pickmap field 'Type' with 'Parent Type' and uncheck Constrain flg.
(4) Go to the integration object 'UDA List Of Values'
(5) Find the Integration component 'List Of Values Child (UDA)'
(6) Add a new field to the integration component with Name = 'Parent Type'. Data Type = 'DTYPE_TEXT', Length = '30', Type = 'Data', External Name = 'Parent Type', External Data Type = 'DTYPE_TEXT', External Length = '30', External Sequence = '38', XML Tag = 'ParentType'
(8) Compile changes.

The SR then goes into some more detail on why after all that it still does not quite work. To understand, we need to see that the LOV ADM Integration Object is Hierarchical in one dimension. That is, there is the LOV_TYPE record and then there are the value records. But LOVs are frequently Hierarchical in two dimensions, by virtue of the Parent value. What I mean is that a given LOV value record will always have one 'parent' record, it's type or technical parent, and may have a second parent record, it's functional parent, if you will.

ADM loads the first, technical parent in the standard way, through the relationships of the Integration Object. To load the functional parent though, ADM must run in two passes, the first to create all the parent and child records, and the second to relate them. This is necessary because we cannot guarantee the sequence with which LOV value records will be placed in the extract file. If these value records do not exist in the target already, and the parent is alphabetically (or however else we chose to sort the records) after the child, then it would error if ADM did not take this approach. So how ADM takes two passes is by virtue of the ADM Data Type explorer. You will notice that the explorer does not actually specify the foreign key fields of an object to link them to each other. Its only purpose is to run ADM in multiple passes. But the twist is that ADM will actually process dependent data types setup in the explorer in reverse order, importing the children before the parent. I personally find this confusing from a terminology perspective. Perhaps a better way of naming these Data Types is to use 'LOV-2ndPass' instead of 'LOV-HierParent' and 'LOV-1stPass' instead of 'LOV-HierChild'. This way when we set up the search specifications for an ADM Export session, it is clear what we are trying to do.

OK, one more wrinkle to throw into the mix (just when you thought it was all making sense). There is actually a third parent relationship involved. That is the records that populate the S_LOV_REL table. I will be honest; I do not use the LOV explorer view that often and I don't really know what the point of this table is. In theory it can make LOVs M:M but I just don't think this is practical. Nevertheless, there are some vanilla uses of LOVs where these records are in fact used that way. The one that comes to mind is in payments, where the PAYMENT_TYPE_CODE values are children of the PAYMENT_METHOD_CODE and there are S_LOV_REL records created to store the relationships. The same issue applies when migrating these relationships. The related value must exist prior to the relationship being built.

One final note. I think the whole not deleting LOVs is well intended but more likely to cause confusion than solve anything. Here is why. Users can and will just change the Name/Value of a value record to something else in which case any sense of history is lost anyway. There are no foreign key relationships to LOVs so business data using these values is unaffected regardless. But others may disagree so this step is completely optional. I remove the no delete properties from the 'List Of Values Child (UDA)' BC and Integration Component. (I also allow deletes from the GUI but that is a separate issue). So my migration methodology is to synchronize values between environments for an initial release. You would take a different approach on a point release where values are likely to have been added directly to production and therefore may not exist in your DEV and TEST environments.

Anyway, what are we trying to do. Quite simply, we are trying to create all the Value records in pass 1, then we need to relate them to each other in Pass 2. I have already discussed how to group LOVs together for a release. This is where I diverge from Siebel's example because I am trying to think of real life scenarios where I am deploying releases, not just one LOV_TYPE. When creating the ADM Project/Session, here are the session items I use:

Data Type	Child Delete	Deployment Filter
LOV-2ndPass	Y	[Release] = '1.1'
LOV-1stPass	N	[Release] = '1.1' AND [List Of Values Relationship.Name] IS NULL

What this means is that the first pass includes all LOV_TYPE records that have been marked for this release and all LOV value records related to them. The second part of the expression basically just insures that no relationship records are included in the first pass. When ADM attempts to set the parent value on a child, it may not be able to find it so it will log a warning and move on. In the second pass, ADM will load all the Relationship records and set the Parent values that it missed on the first pass. I have also set child delete to true on the second pass so that this job effectively synchronizes the value records for the type records marked.

A Basic Interface - Integration Object User Props

noreply@blogger.com (Mik) — Mon, 15 Nov 2010 11:51:00 +0000

I know this is meant to be a basic interface with so complexity, but let's be realistic about the requirements we are likely to get. Even a simple upsert of something as basic as a service request is likely to require a bit of digging into bookshelf so that the interface is able to mimic basic GUI functionality. I will discuss some of the most commonly used User Properties necessary to implement even an advanced interface. When in doubt about the syntax of any of these properties, take a look for an example in the Tools flat view.

PICKLIST

This is the most common Integration Component Field User Property you will see and it basically tells the EAI Siebel Adapter to validate the picklist in the interface. This property is generally created by the wizard so I bring it up only because validating the picklist here will allow for several different ways to interpret a picklist field value described by some of the user properties below.

PicklistUserKeys

In the GUI, when you type an account name in the Account field on another BC that has a picklist of Accounts, and there is more than one record matching that name (with different locations), a pick applet will pop open with the constrained list of accounts having that name. The GUI is letting a user decide which of the multiple records returned was meant to be picked. An interface does not have that luxury, so the PicklistUserKeys Integration Component Field User Property is provided to mimic this action. The value of this property should be a comma separated list of fields representing the logical key of the picklist record to look up. These fields must all be present in the integration component (though there values can be null). The 'PICKLIST' user property must also exist for the field where this property is used and its value must be 'Y'.

Ignore Bounded PickList

When a picklist is validated in the interface and the value passed is not found, the EAI Siebel Adapter stops processing and returns an error. If the data is expected to sometimes be missing though, you may want the foreign key to just be left blank. For instance, maybe the service request, in our example is tied to an order via a back office order number, but the order was never loaded. Add this user property with a value of 'Y' in combination with the PICKLIST user property with a value of 'Y'. The EAI Siebel Adapter will look up the record by the user key provided (can also be used in combination with PickListUserKeys) but if it is not found, will set the field to blank in the integration object before applying the data. Keep in mind that this property will only work as expected if the Picklist object the underlying BC uses to constrain the field is set to No Insert equals True, otherwise, the EAI Siebel Adapter will try to insert a record. Also note that in bookshelf there is a typo in that there should be spaces between the words of the property name.

FieldDependency

It is easy in the GUI to determine the order of the fields being picked, either by training or by sequencing the fields in a particular way during applet design. This may help set the fields that will be used to constrain the value of another field, frequently in a hierarchical picklist. In EAI, we achieve this result through this user property. It can be used multiple times with a sequence number, just like other BC and applet user properties. The value is a field integration component field name. Siebel claims that pickmapped constraints are automatically taken into account, and that may typically be the case, but I have seen times when it does not work, so this is a good fall back.

A Basic Interface - Web Service Workflow

noreply@blogger.com (Mik) — Fri, 29 Oct 2010 13:41:00 +0000

Just about every interface consists of two basic components: the integration object(s) and the workflow or business service. I will demonstrate a workflow approach which will give you more opportunity to customize down the road.

It is here that we begin to differentiate the integration by the communication mechanism. Because I am designating this integration as a Web Service, that will drive the type of data this workflow will expect as an input and output. The workflow I build will eventually be exposed as a WSDL to be consumed by an external program. That WSDL should have the definition of the message it is expecting, in this case, the XSD, or definition of the Integration Object we just built. How we accomplish this is to set the Input Process Property to a Data Type of 'Integration Object' and to actually specify the integration object we built, in the Integration Object attribute of the process property.

You can also see my place holder for the SR Number that I want to return to the external system in the response message. The 'IncomingXML' property is already in the format needed to be passed to the EAI Siebel Adapter, so there is no conversion necessary. And we are assuming that the data being passed is exactly as it should be applied. You will create the following steps which I will explain (other than Start and End which are self explanatory):
The 'Upsert SR' is a Business Service calling 'EAI Siebel Adapter'. Now here is the another design decision to be made. Each of the available methods differentiate exactly how the data should be applied. But there are two broad determinations. If we were to use the Execute method, then the 'operation' element which exists in each component of the IO would be used to determine how the data should be applied. This gives more control the calling system (or a data map which I will discuss later). The other set of methods essentially comprise a One Size Fit All to applying all the data uniformly. I will use the latter approach here and set the method to 'Upsert'. There is only one component in my IO, so if it exists, it will be updated, otherwise it will be inserted. The input arguments for this step are the IncomingXML message from the external system and a parameter telling the EAI Siebel Adapter to create the Status Object.

There is one Output Argument. We no longer care about the input message at this point because it will have been applied so we just overwrite it with the return, which in this case will be the status key.
The last step in the WF is another Business Service step calling 'PRM ANI Utility Service', 'GetProperty' method. This business service has a plethora of useful methods for manipulating property sets. This particular method will extract the value of a field from an integration object instance. Here are the inputs:
The output is to set the process property 'SRNumber' with the Output Argument, 'Property Value'. When the return message is sent back to the calling system, this property will exist with the generated SR Number.

Simulating/Troubleshooting this WF from within tools is difficult as built so I sometimes add a bypass step off the start branch to read the integration object from a file. I may talk about this later but want to keep this post pretty straightforward. So for now, this workflow can just be deployed, checked in and activated.