Strip off Number format in DTYPE_PHONE field (No Script)




There is an interesting post Scriptless Siebel - A Challenge over at Alex's Siebel Essentials blog.

One of his readers posts this challenge.

  • Scenario
    "We were sending a Contact Record to external system via HTTP.That contact Record had a field called Work Phone of Dtype_Phone.I guess you might be aware that Dtype_Phones are stored along with Format string which consists of zeroes, if user enterd some space in Phone number like - If User Enters +44123 456 789 then it will be stored in database as -
    +44123456789CRLF000 000 000.

    Now I was using a WF which use EAI Siebel adapter to get Siebel Message. Then I would convert that into xml and send to external system.External system said to remove those zeroes and send.Though there is a delimitter in form of new line between zeroes and actual number, I was unable to do this task in config."
If i am understanding the requirement correctly, it should be quite simple to transform the message in WF, before sending it to the external system.

Assuming our XML looks like this.

<phone>+44123456789
000 000 000</phone>
//Note: there this a real CRLF character between the phone and the phone format

We can use the following XSLT to split the field to return us just the phone number without the format.

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="text"/>
<xsl:template match="/">
<xsl:variable name="CRLF">&#10;
<xsl:variable name="num" select="substring-before(phone, $CRLF)" />
<xsl:variable name="pattern" select="substring-after(phone, $CRLF)" />
phone: <xsl:value-of select="$num" /> <xsl:value-of select="$CRLF"/>
pattern: <xsl:value-of select="$pattern" /> <xsl:value-of select="$CRLF"/>
</xsl:template>
</xsl:stylesheet>


However, i think we can make it better, so i would like to propose a new challenge.

Following the above scenario, get the DTYPE_PHONE value, extrapolate the pattern and apply it to the phone number on the fly before sending out to the external system (this of course must be done without any sort of custom script).

eg. The system must take this format "000 000 000" and apply it to "+44123456789" to output "+44 123 456 789"

Stay tuned to see the solution to the problem.





12 comments:

  1. how can the xlst be applied to the xml during webservice integration ?

    ReplyDelete
  2. In which step we need to invoke EAI XSLT Service when we are using Web Service. Also I think the same requirement could be achieved if the motive is to strip the trailing format. so if you have +44123456789CRLF000 000 000 then it will show +44123456789 and if you have +44123456789 then it will show +44123456789.

    You can use following source expression:(NOTE: I did not test it personally but I guess it will work)

    IIf(InStr([Work Phone #],InvokeServiceMethod("SSSE Address Parser (eScript)","GetCRLF","Name=Val","CRLF")),Left([Work Phone #],(InStr([Work Phone #],InvokeServiceMethod("SSSE Address Parser (eScript)","GetCRLF","Name=Val","CRLF")) - 1)),[Work Phone #])

    ReplyDelete
  3. Nice find on the BS! It looks like it would work, but the disadvantage is that it wont scale well.

    ReplyDelete
  4. Ok for web services, I have figured out...It;s in HTTP Transport where we have to intercept the outgoing XML and applying XSLT before passing it to external system. I will check it soon.

    ReplyDelete
  5. You can also use the profile attribute with value set to \r\n if you do not want to use business service in source expr. Also from the wf, before invoking data mapper step, you can set the profile attribute using BS:'SIS OM PMT Service' method 'Set Profile Attribute'

    The source expr look like

    IIf(InStr([Work Phone #],GetProfileAttr("CRLF")),Left([Work Phone #],(InStr([Work Phone #],GetProfileAttr("CRLF")) - 1)),[Work Phone #])

    ReplyDelete
  6. I incorrectly posted the reply to your EAI XSLT Service question in the Impossible Solutions post, the concept will be the same.

    See
    http://siebel-essentials.blogspot.com/2009/04/scriptless-siebel-challenge-phone_16.html

    The idea of setting the profileattr to \r\n is intruiging.

    ReplyDelete
  7. If we use the Integration Object user Property

    as

    UseFormattedValues = 'Y' it will do send the phone numbers to external application through webservices without appended formatted zeros.

    ReplyDelete
  8. Hi Raju, the draw back of using this user property is that it will apply this formatting to every field in the IO. eg, currency, date, etc

    This is not desirable, when the remote system just wants the raw data, and control its own presentation.

    ReplyDelete
  9. In the Integration Field Map >> Source Expression use the expression:

    ToChar([Work Phone #])

    This removes the international prefix (e.g. +44) and the trailing format mask. Simple. I'm using this appraoch in Siebel 7.8 using a workflow deployed as a web service.

    ReplyDelete
  10. I have tried using the expression

    IIf(InStr([Work Phone #],GetProfileAttr("CRLF")),Left([Work Phone #],(InStr([Work Phone #],GetProfileAttr("CRLF")) - 1)),[Work Phone #])

    everything works fine, only problem now is symbol "?" getting added to phone numbers in some cases.

    any suggestions ?

    ReplyDelete
  11. The "?" is most likely to be caused by invisible character in your phone string. Check for a line feed at the end of your phone, and that your CRLF is a chr(10).

    ReplyDelete

Comments are open to all, please make it constructive.