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.