Sunday, July 25, 2010

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.

12 comments:

  1. Hi,
    thanks for sharing this info and was useful for me.

    ReplyDelete
  2. I am delighted to go through about other peoples success through laser like aim and you have it.Continued achievement to you.
    People tend to place more trust in blogs of their peers rather than paid advertising.
    One Way Link Building Services

    ReplyDelete
  3. This is nice work. I had implemented a similiar solution for a previous project. In my case, there was a parent child relation ship from Service Requests to activities, and requirement was for a PDQ which would query out all SRs with open Activities. PDQs can be run only for the primary BC in the view. So we setup some joins which would bring Activity values up the parent SR,and PDQ would use these joined-field values, though they were not exposed in the UI.

    Thanks for finally documenting this !!

    ReplyDelete
  4. This is brilliant thinking outside the (Siebel) box! Thanks a lot.

    ReplyDelete
  5. But it will only pull 1 value if you are using the join right? In that case, if one queries for a certain OPTY id, it may not even return that id. So how did you acheive that using a join? Will it bring the exact OPTY Id when queried for it coz it would have a 1:M cardinality.

    ReplyDelete
  6. Hello! Thank you very much for your post! I just don't understand how Siebel will set up the query every time a user consults an Account and which child record would be displayed?
    Thank you

    ReplyDelete
  7. Hi Gaurav,

    Have you tried executing a query with non existent asset number or invoice number? It will still return all the records. isn't?

    JIM

    ReplyDelete
    Replies
    1. Hi JIM,

      I have already tried this. It will not return any record if you query for non existent data.

      Cheers
      Gaurav

      Delete
  8. so that means you are saying these joins are inner joins?
    if they are outer join joins and if joined table does not return any data . parent table account in our case should still fetch records?


    can you explain how you have worked around outer join behaviour.? try making account a list applet and then see.

    jim

    ReplyDelete
    Replies
    1. Hi Jim,

      1. These joins are Outer Joins and not inner joins.
      2. Yes
      3. I have provided the configuration in the post, please follow that. Once done you can take a look at the spooled query how it is getting generated when you search for Asset# or Invoice#.

      Cheers
      Gaurav

      Delete
  9. Hi Gaurav

    thank you for your blog, however I have a question regarding the configuration, the joins are bulit based on primary fields on the destination tables. As an example i have accounts and addresses, they have M:M relationship. I want to make a query on address and I want to get all accounts which have this addres or these addresses (querying the address with a wild card * ) and if an address is not associated with an account still i want this address get populated in the list applet. and if an address is associated more than one account then i want all accounts also get displayed on the list applet. you suggestion seems will not work for this scenario right?

    thank you
    charlie

    ReplyDelete
    Replies
    1. Hi Charlie,
      Your requirement is quite straight forward if the list applet is based on the intersection table of Account and Address i.e. S_CON_ADDR

      Cheers
      Gaurav

      Delete