Building Link as a Join??

The very first expression that any Siebelized person would have after reading the title of this post would be, "hey, what is that?? Join and Link are two different objects in Siebel. Join used for building 1-1 and M-1 relationship and link is being used for building 1-M and M-M relationship. So how come you can build a link via join??".......

I knowwww and I am there with you what you just thought and completely agree to this fact too :) but keep in mind you get functionalities working in Siebel by SQLs running in the background on the database and as far as you know how to play with the configuration to achieve desired results (provided it will not result in performance issue) you can do wonders.

Alrightyyyy then, lets see what I got for you today to let you know an interesting fact in Siebel Configuration that can be used to achieve some different kind of requirement. The requirement we got to implement was:

"Create the Account Screen, which has got various sub-tabs like Opportunities, Quote, Assets, Service Requests, Invoices. So this is a kind of Parent-child relationship which is available in Siebel vanilla, nothing new.



But we were asked to provide an ability to query on the Accounts header form applet with a) Opportunity Id b) Quote Id c) Asset Number d) Service Request# e) Invoice#. That means if I have an Asset Number with me and query it on Account form applet in some field, then system should return the corresponding Account record to which this Asset Number is associated with. Similarly for Invoice# and so on. You might be thinking why this ever be a requirement and for what purpose, can't we navigate to Assets screen for query the Asset Number and drilldown from there to Account Screen and similar for other entities? You are right, you can do that as well but in our requirement user want to have the ability to query in a single place. "

I think the requirement is clear now. The very first solution come to the mind can be achieved by following the below steps:

a. Create MVLs (which in-turn use some "Link") on Account business component.
b. "Use Primary Join" property of these MVLs should be "False".
c. Create MVFs for based on each MVL and expose on Account form applet.
d. Let the user query on any of the MVF and system will return the corresponding Account record.

This is very straightforward solution, but HUGE performance impact. When the view will get loaded, system will run (1 + (Number of MVFs)) queries for each account you will traverse on the UI. Lets try another good option here and create "Join" on the business component instead of "Link".

Here is below what I tried:

a) On Account business component, create following joins (as per the need)

i. Field Name: S_INVOICE
Source Field : ROW Id (Row_id of S_ORG_EXT)
Destination Column : ACCNT_ID (Foreign Key to S_ORG_EXT on S_INVOICE)
Alias : INVOICE

ii. Table Name: S_ASSET
Source Field : ROW Id (Row_Id of S_ORG_EXT)
Destination Column : OWNER_ACCNT_ID (Foreign Key to S_ORG_EXT on S_ASSET)
Alias : ASSET

iii. Table Name: S_OPTY
Source Field : ROW Id (Row_Id of S_ORG_EXT)
Destination Column : PR_DEPT_OU_ID (Foreign Key to S_ORG_EXT on S_OPTY)
Alias : OPTY

b) Now create corresponding join fields:

i. Field Name : Invoice Number
Join : INVOICE
Column : INVC_NUM

ii. Field Name : Asset Number
Join : ASSET
Column : ASSET_NUM

iii. Field Name : Opty Id
Join : OPTY
Column : OPTY_ID

c) Expose the fields on the UI and query for any Invoice#, Asset#, OptyId on the Account form applet and system will bring the Account record for you.

Let's see how it is working on the UI. I queried for Invoice# = "410194-13385102"




and now let's navigate to "All Invoices" view to see if system has returned the right Account or not. And yessss, this is the right account, I can see the Invoice#.




You can try it at your end and the best part is, if you see the SQL spool generated by the system, you will find that only single query is returning the data, not likely the case with MVFs, and there is no performance issue at all.

Purging Vs Deleting Workflow Process Instances

This is one of the important information you might be interested in, if you frequently debug a Workflow Process at run-time by increasing the Monitoring Level under "Administration - Business Process -> Workflow Deployment" view.

Whenever you increased the Monitoring Level to "4-Debug", system starts capturing the WF steps details along with the each Process Property at each step, to give a better picture of what has exactly happened at each step and it is very helpful for debug purpose. You can see all Run-time degugging details in "Administration - Business Process -> Workflow Instance Monitor" view.

"Process Instances" gives you all the Workflow Instances run once the Monitoring Level has been increased. It all tells the currently running WF Step.

"Step Instances" captures the details of each step along with its start and end time.

"Process Properties" captures the value of each process property in each WF step.

Though this information is useful but it is a good practice to decreased the log level back to "0-None", once you are done with debugging and also the most important thing you should do is "Purge" all the Workflow Process Instances. Navigate to "Administration Business Process -> Workflow Instance Monitor -> Process Instances", query for the workflow process and click "Purge".

Now, why I am saying this last step of purging these records as "very important"?

Let me tell me what actually happened when I was debugging one of the workflow issue. I increased the monitoring level to 4 for debugging purpose and that workflow was having around 30 workflow steps and around 50 process properties. Additionally, this workflow runs on the child activities (average number around 50) of the Service Request. So you try calculate the number of process property instances record (in S_WFA_STPRP_LOG), system will create in a single workflow run = 30 x 50 x 50 = 75000. Big number isn't it?? So if I debug this workflow few number of times, the number of records will be really huge. But remember, as far as you are purging the records after debugging finishes, then no issues, but if you don't then these records will pile up and some day your database size will run short out of physical space.
So what actually happened was, I made few changes in the workflow and deployed again. Went to "Administration Business Process -> Workflow Deployment" and queried the workflow process and hit "Activate". In the below "child items" applet, system creates a new version of the workflow process with Deployment Status = Active and the older record becomes gets its status = Inactive. So I thought this record is Inactive, let me delete this old instance. I did that few number of times and never realizes that it will just delete this record from "S_WFA_DPLOY_DEF" table and its corresponding child records in "S_WFA_STPRP_LOG" table will keep sitting there. And there is no way you can "Purge" these records from the UI using "Purge" button because the parent record has been deleted earlier.

So it end up having millions of records in this table without parent record and later we realized the database is running short of physical memory and when I checked for the tables in the database which all having huge number of records, we found "S_WFA_STPRP_LOG" table was leading the race. I did the clean-up of the child records via running some SQLs in the database by finding the orphan records in "S_WFA_STPRP_LOG" and learned the lesson using "Purge" button after completing the debugging.

Hope this will also help you in future.

How to create Inbound Web Service in Siebel?

In the last post, we talked about creating an Outbound Web Service in Siebel where we receives the WSDL file from other system and consume it into Siebel Tools to generate necessary artifacts.

Today I found a very nice tutorial for creating an Inbound Web Service in Siebel. In this post you will find how to publish the WSDL from Siebel and let the outside world do data manipulation inside Siebel.

You can easily find it at the below links:

http://st-curriculum.oracle.com/obe/fmw/soa_apps_integ/10g/10_132_siebel/siebel.htm

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/soa_apps_integ/10g/10_132_siebel/siebel.htm


At the end of above tutorial, you will be generating the WSDL file from Siebel thin client which need to provide to other system to test the Inbound Web Service you created. Please refer the following post for testing the WSDL:


Test WSDL