Tuesday, February 9, 2016

EAI Integration Map expressions

While there are many posts I have seen that talk about expressions supported by 'EAI Data Transformation Engine', I have never seen an attempt to compile a list of supported functions and examples of there uses.  So this will be a humble beginning that will hopefully grow over time.  Note that these functions are mostly VB so if trying out one that is not listed, start with what is supported in VB.  They can also be found in Siebel Bookshelf as Siebel Query Language expressions

Do not include XML element (use System type)
IfNull([Middle Name], [Conflict Id])

Transforming Dates
(from 'YYYYMMDD' to 'MM/DD/YYYY'):
Right(Left([Source Field Name],7),2) +"/"+Right([Source Field Name], 2)+"/"+Left([Source Field Name], 4)

(from Siebel Date to externally recognized format):
ToChar([Birth Date], 'YYYY-MM-DD')
ToChar([Completion Date], 'YYYY-MM-DDThh:mm:ss')

Conditional Logic:
IIF([Source Field Name] = "false", "N", "Y")

SSN formatting (Strip hyphens):
IIF(InStr([PPT Social Security Number], "-") > 0, Left([PPT Social Security Number], 3)+Mid([PPT Social Security Number], 5, 2)+Right([PPT Social Security Number], 4), [PPT Social Security Number])

EAI Lookup for an Inbound Interface:
  • EAILookupSiebel("XXX",[Source Field Name])
    • XXX is the Type in the EAI Lookup table.  This needs also needs to be setup as a value under the EAI_LOOKUP_MAP_TYPE LOV type.
  • IIF([Source Field Name] IS NULL, "", EAILookupSiebel("XXX",[Source Field Name]))
    • EAILookupSiebel fails if no value is found so minimize this possibility unless an exception is desired
Extract the file name from a File Path:
Mid([Source Field Name], InStr([Source Field Name], "/", -1) + 1)

1 comment:


  1. If you want to avoid an error with a missing value in Map, you can do this:
    EAILookupSiebel("XXX",[Source Field Name],"DefaultValue")
    or like this if you want see original Value
    EAILookupSiebel("XXX",[Source Field Name],[Source Field Name])

    ReplyDelete