Monday, July 18, 2011

How to send email containing multiple child records data?

I am pretty sure you will find this post very interesting, if you ever get a requirement to send a email in HTML format and email should have the details of the child records(dynamic data) as well.

XSLT (XSL Transformation) can do wonders for this kind of requirement where the basic idea is to get data in XML hierarchy from Siebel (which you can easily do via EAI Siebel Adapter and convert it into XML) and apply the XSL transformations to get the HTML output. And as far as you know how to play with HTML and XSL, you can create any kind of email format that you like to see. All you need is to use EAI XSLT Service business service with method Transform.

Below picture might give you some idea of it:



To understand the concept, let suppose:
1. As soon as an Opportunity gets submitted, you are required to send an email to the Sales person to inform him.
2. Email should have the details of child records created under that Opportunity.
3. Email should have some kind of formatting so that it looks good and eye-catching.

You might be thinking what is a big deal in it and why should I even use the XSLT (new term for lot many people) for it? There is a simple reason behind it, there is NO OOB way available in Siebel (as far as I know) by which you can include the data from Child records (multiple) in the email.

Let me first show you the proof-of-concept and later we will discuss about the technical solution.
1. I have below opportunity created with 2 records of Opportunity Products.

2. It has also 3 Quote records created under it.

3. As soon as Opportunity get submitted, system is required to send the email to the sales person.
4. Here below is the email, I would like to receive:


If you find that interesting, keep reading for the technical details of it.


Pre-requisite
Please note that for this demo, I have used a Integration Object (Opportunity) based on Opportunity BO with having only 3 ICs configured: Opportunity, Opportunity Product and Quote. And include only those fields whose values are required in the final email, for eg: Oppty Name, Product Name, Net Price, Quote Name etc. (Please refer the first picture of this post, XML part)


Technical Solution
1. Call a Workflow (based on Opportunity BO) as soon as Opportunity gets submitted.
(Note: System will automatically pass the row_id of Opportunity to "Object Id" process property of the Workflow Process)
Here below is the WF snapshot.


2. Process Properties

3. Step Details:


Code for XSLTBuffer
<?xml version="1.0" encoding="UTF-16"?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"><html><body><h2>You have got an Opportunity to act on:</h2><h4>Opportunity Name : <span style="color:#ff0000"><xsl:value-of select="ListOfOpportunity/Opportunity/Name"/></span></h4><h3>Here below are the Product Details:</h3><table border="1"><tr bgcolor="#9acd32"><th>Product</th><th>Quantity</th><th>Net Price</th><th>Revenue</th></tr><xsl:for-each select="ListOfOpportunity/Opportunity/ListOfOpportunityProduct/OpportunityProduct"><tr><td><xsl:value-of select="Product"/></td><td><xsl:value-of select="ProductQuantity"/></td><td><xsl:value-of select="ProductPrice"/></td><td><xsl:value-of select="Revenue"/></td></tr></xsl:for-each></table><h3>Here below are the Quote Details:</h3><table border="1"><tr bgcolor="#9acd32"><th>Quote Name</th><th>Revision</th><th>Status</th> </tr><xsl:for-each select="ListOfOpportunity/Opportunity/ListOfQuote2/Quote2"><tr><td><xsl:value-of select="Name2"/></td><td><xsl:value-of select="Revision"/></td><td><xsl:value-of select="Status"/></td></tr></xsl:for-each></table></body></html></xsl:template></xsl:stylesheet>

If you want to learn about XSLT more, then please refer the below link:
http://www.w3schools.com/xsl/

14 comments:

  1. Why don't you achieve the same requirement using the standard out of the box template items in the communications admin >> templates views ? This can be achieved without any configuration or am I missing the point ?

    ReplyDelete
  2. Intersting post.. and nice example of XSLT usage.. but I would agree with the above comment... XSLT might be doing the job but there are lot of other options to achieve the same thing... especially communication templates....

    ReplyDelete
  3. @Anonymous i think in email templates you cannot create the tables dynamicaly as we are able to do with the help of XSLT,i.e if we are having 4 products under an opportunity ,in email templates we can create only a static table of say 6 rows so in a way two rows will be empty(as there are 4 prod) which will not look gud.

    ReplyDelete
  4. Be aware , XSLT transformation is considered to be highly process intensive. too much of XSLT can eat lot of CPU and Memory

    ReplyDelete
  5. XSLT also gives additional mileage for data formatting and manipulation which email templates doesn't support.

    ReplyDelete
  6. Thankyou so much all of you for your valuable comments.

    I wanted to do some research before putting my comments here, so here is what I found:

    a) Yes, it is possible to have child data (dynamic) to be present in the same email using Template Items in "Administration-Communications" view. (May be I will publish a post for it in future)

    b) I tried really hard to get the formatting similar I demonstrated in the above post, but it was not looking good and formatted correctly. Table with heading looking wierd using Template Items, specially when you apply different formatting to the Header row.

    c) Also, as mentioned above by "Siebelish", XSLT gives you priviledge to do manipulation whereas by using template items(which is instead a hard-code structure) you can't achieve it.

    Overall, still I can say that XSLT is the best mechanism to achieve this kind of requirements.

    Cheers
    Gaurav

    ReplyDelete
    Replies
    1. Hi Gaurav,

      I tried using this method and there is one thing I observed.
      The EAI XSLT Service BS has one Mandatory Argument.
      Argument = XMLCharEncoding
      Type = Output

      I observed this, when the Validation of the workflow failed indicating that values for mandatory arguments have not been provided.

      So, I created another Process Property and assigned this O/P argument.

      After this, when I tried Simulating the workflow,
      it fails at the step for the EAI XSLT Service with the following error:

      Error invoking service 'EAI XSLT Service', method 'Transform' at step 'XSLT Transform'.(SBL-BPR-00162)


      Please help.


      Regards,
      Soumya.

      Delete
    2. Hi Soumya... XSLT service works without this output argument as well. Anyways, the error you are getting comes when there is a problem in the "XSLT Buffer". Please check it and just with a very simple one, just to confirm if it works. Then add more complexity to it per your requirement.

      Cheers
      Gaurav

      Delete
    3. Hi Gaurav,


      Am not even a beginner in XSLT :( ..tried modifying the code you have given in the post.

      I was trying to paste the code I am using, in this post. But the page does not allow me to.. Please tell me what can be done.


      Many Thanks,
      Soumya

      Delete
    4. I would recommend to follow this link first:
      http://www.w3schools.com/xsl/

      Delete
  7. A simple wow. really nice post for starters. Thank you.

    ReplyDelete
  8. Yes its Really nice post Gourav...Thanks

    ReplyDelete
  9. appreciate the solution.

    would like to point to another solution for f9 functinality.
    http://howtosiebel.blogspot.com.au/2013/06/not-able-to-send-child-buscomp-fields.html

    jim

    ReplyDelete
  10. @Gaurav thanks this post really helped in 2016, very very accurate, and it was perfect for my requirement.

    however since integration object do not take calculated field , i combined the html and my message template/

    Regards.

    ReplyDelete