Hi There,

In response to the beautiful article by Atilla Aykut Genç, Updating Exchange Rates Automatically in SAP Business One, I would like to explain how to obtain the same result using SAP Business One Integration Framework (B1if).

This is a basic solution to import exchange rates, implemented some times ago while studying B1if.

It has beem tested on an SAP Business One 9.0 environment.

Scenario

Import exchange rates from European Central Bank at a scheduled time using B1if.

The main advantage of using B1if is that no programming knowledge is needed, except a little XSL a XPath.

It also does not need any system privilege on the B1if server to install and run services, view logs, schedule tasks, etc

How To

Step 1: Create a Scenario Package

Create a new scenario package as the image below

/wp-content/uploads/2015/06/scenario_package_731512.png

Step 2: Create a new Scenario Step

Create a new step, assign it to the package created at Step 1.

/wp-content/uploads/2015/06/step_definition_731528.png

Define Inbound as Void (Timer based):


/wp-content/uploads/2015/06/inbound_definition_731529.png

Don’t forget to define your Timer. Save and Close.


Define Outbound as void also, save your step.


Step 3: Processing


Here is the processing flow:

/wp-content/uploads/2015/06/step_processing_731530.png

atom3

This atom prepares the connection information for the httpcall (atom2).


<xsl:template name="transform">
   <call xmlns="">
      <connect>
         <destProtocol>http</destProtocol>
         <destHost>www.ecb.europa.eu</destHost>
         <destPort>80</destPort>
         <destPath>/stats/eurofxref/eurofxref-daily.xml</destPath>
         <method>GET</method>
      </connect>
      <htta>
         <par id="htta.returnpltypedefault" value="xml"></par>
      </htta>
   </call>
</xsl:template>




atom2

This atom calls www.ecb.europa.eu and get Exchage Rates XML.

/wp-content/uploads/2015/06/http_call_731567.png

Note that Stop processing if fails is set to false, letting the step to continue and to be handed over later, at atom0. You could define an SLD entry for Reference for connectivity.

atom8

This atom calls SQL and get DirectRate / IndirectRate from OADM and gets all the currencies configured in your SAP Business One Company.

/wp-content/uploads/2015/06/sql_call_731568.png

Note: select your SLD entry SysId. See documentation to find out how to configure it at runtime.

brach/path

This verifies we had a readable XML from ECB.

/wp-content/uploads/2015/06/path_731569.png

atom7

This atom prepares the for-each loop that calls SBObob function to insert/update exchange rates. It also checks against atom8_2 result (list of defined currencies in Company) and prepare an ExchageRate nodeset.


<xsl:template xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref" name="transform">
   <xsl:for-each select="/vpf:Msg/vpf:Body/vpf:Payload[./@id=&apos;atom2&apos;]//*[./@rate]">
      <xsl:variable name="currency"><xsl:value-of select="./@currency" /></xsl:variable>
      <xsl:if test="/vpf:Msg/vpf:Body/vpf:Payload[./@id=&apos;atom8_2&apos;]/jdbc:ResultSet/jdbc:Row[jdbc:CurrCode = $currency]">
         <ExchangeRate xmlns="">
            <xsl:attribute name="date">
               <xsl:call-template name="b1ilib.strgReplace">
                  <xsl:with-param name="subs" select="&apos;.&apos;"></xsl:with-param>
                  <xsl:with-param name="var" select="&apos;-&apos;"></xsl:with-param>
                  <xsl:with-param name="wstrg" select="../@time"></xsl:with-param>
               </xsl:call-template>
            </xsl:attribute>
            <xsl:attribute name="currency">
               <xsl:value-of select="./@currency"></xsl:value-of>
            </xsl:attribute>
            <xsl:attribute name="rate">
               <xsl:choose>
                  <xsl:when test="/vpf:Msg/vpf:Body/vpf:Payload[./@id=&apos;atom8_1&apos;]/jdbc:ResultSet/jdbc:DirectRate = &apos;Y&apos;">
                     <xsl:value-of select="./@rate"></xsl:value-of>
                  </xsl:when>
                  <xsl:otherwise>
                     <xsl:value-of select="1 div ./@rate"></xsl:value-of>
                  </xsl:otherwise>
               </xsl:choose>
            </xsl:attribute>
         </ExchangeRate>
      </xsl:if>
   </xsl:for-each>
</xsl:template>


for-each

Loops thru ExchangeRate nodes generated by atom7 XSL.

The for-each atom xpath  expression is


/vpf:Msg/vpf:Body/vpf:Payload[./@id='atom7']/ExchangeRate

B1 Function

This is the call to GetCurrencyRate of SBObob object. This is the same call as DI API, so you can use SDK help to get reference. B1if, in this case, has poor help.


/wp-content/uploads/2015/06/function_call_731582.png

Note: again you have to select for the right SysId.


Parameters:


Parameters is an X-Path that gets the full parameters string passed to SetCurrencyRate SBObob function.


concat(/ExchangeRate/@currency, &quot;,&quot;, /ExchangeRate/@date, &quot;,&quot;, /ExchangeRate/@rate, &quot;,true&quot;)

atom4

This atom corrects the join-unbranch flow and ensure a valid output message. It can be generated by B1if by pressing Generate button in the definition window.


<xsl:template name="transform">
   <xsl:copy-of select="$msg/*"/>
   <xsl:for-each select="/bfa:unbranch/bfa:join/vpf:Msg/vpf:Body/vpf:Payload">
      <xsl:copy-of select="."/>
   </xsl:for-each>
</xsl:template>



final (atom0)

In this example a final result and error notification are not managed.

Results


This is what I get at atom0 during a Test:



<Msg xmlns="urn:com.sap.b1i.vplatform:entity" MessageId="15062322054809461125A9FE6F2448E4" BeginTimeStamp="20150623220548" logmsg="0000" SubMessageId="" status="success" owntst="true" test="true">
  <Header>
    <IPO Id="vPlatform_Test"/>
    <Sender Id="" ObjId="Z.ecb"/>
    <Receiver Id=""/>
    <ReceiverList>
      <Receiver Id="" handover="P"/>
    </ReceiverList>
    <Identification Ident="Void" IdPar="n.a."/>
    <nsList/>
    <vBIU Id="Z.ecb" ver="1.0.0" SId="Z.UpdateRates" filter="" phase=""/>
    <Variables>
      <var id="userid" value=""/>
      <var id="username" value=""/>
      <var id="B1System" value="''"/>
    </Variables>
    <Properties/>
    <SysTypeProperties type="B1if" offline="" snd="senderdummy" rcv="receiverdummy" ssystype="" rsystype=""/>
    <LocalProperties type="B1if" biu="Z.ecb" snd="senderdummy" rcv="receiverdummy" sid="Z.UpdateRates" offline=""/>
    <VarProperties/>
  </Header>
  <Body>
    <Payload Role="T" Type="Call"/>
    <Payload Role="S">*** no test message specified ***</Payload>
    <Payload Role="X" id="atom3">
      <call xmlns="">
        <connect>
          <destProtocol>http</destProtocol>
          <destHost>www.ecb.europa.eu</destHost>
          <destPort>80</destPort>
          <destPath>/stats/eurofxref/eurofxref-daily.xml</destPath>
          <method>GET</method>
        </connect>
        <htta>
          <par id="htta.returnpltypedefault" value="xml"/>
        </htta>
      </call>
    </Payload>
    <Payload Role="C" id="atom2" statusNo="0" statusMsg="success" reference="atom3" sysid="" payload="atom3" throwException="false">
      <http.header>
        <http.header.info id="ETag" value="&quot;201be-658-5192e9248fc40&quot;"/>
        <http.header.info id="Date" value="Tue, 23 Jun 2015 20:05:48 GMT"/>
        <http.header.info id="Content-Length" value="1624"/>
        <http.header.info id="Last-Modified" value="Tue, 23 Jun 2015 12:31:37 GMT"/>
        <http.header.info id="Connection" value="keep-alive"/>
        <http.header.info id="Content-Type" value="text/xml"/>
        <http.header.info id="Accept-Ranges" value="bytes"/>
        <http.header.info id="Server" value="Apache/2.2.12 (Linux/SUSE)"/>
      </http.header>
      <gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
        <gesmes:subject>Reference rates</gesmes:subject>
        <gesmes:Sender>
          <gesmes:name>European Central Bank</gesmes:name>
        </gesmes:Sender>
        <Cube>
          <Cube time="2015-06-23">
            <Cube currency="USD" rate="1.1204"/>
            <Cube currency="JPY" rate="138.72"/>
            <Cube currency="BGN" rate="1.9558"/>
            <Cube currency="CZK" rate="27.189"/>
            <Cube currency="DKK" rate="7.4625"/>
            <Cube currency="GBP" rate="0.71010"/>
            <Cube currency="HUF" rate="310.24"/>
            <Cube currency="PLN" rate="4.1697"/>
            <Cube currency="RON" rate="4.4805"/>
            <Cube currency="SEK" rate="9.2071"/>
            <Cube currency="CHF" rate="1.0449"/>
            <Cube currency="NOK" rate="8.7255"/>
            <Cube currency="HRK" rate="7.5780"/>
            <Cube currency="RUB" rate="60.9175"/>
            <Cube currency="TRY" rate="3.0010"/>
            <Cube currency="AUD" rate="1.4538"/>
            <Cube currency="BRL" rate="3.4655"/>
            <Cube currency="CAD" rate="1.3825"/>
            <Cube currency="CNY" rate="6.9539"/>
            <Cube currency="HKD" rate="8.6859"/>
            <Cube currency="IDR" rate="14878.91"/>
            <Cube currency="ILS" rate="4.2341"/>
            <Cube currency="INR" rate="71.2742"/>
            <Cube currency="KRW" rate="1238.95"/>
            <Cube currency="MXN" rate="17.2284"/>
            <Cube currency="MYR" rate="4.1925"/>
            <Cube currency="NZD" rate="1.6381"/>
            <Cube currency="PHP" rate="50.536"/>
            <Cube currency="SGD" rate="1.5006"/>
            <Cube currency="THB" rate="37.819"/>
            <Cube currency="ZAR" rate="13.6514"/>
          </Cube>
        </Cube>
      </gesmes:Envelope>
    </Payload>
    <Payload Role="C" id="atom8_1" system="0010000100" mode="single" method="Automatic detection by key word(Automatic detection by key word)" plr="4" dbtype="SQL" blockExecution="false" compatibilityMode="true" delimiter=";" sql="select DirectRate from OADM" disable-output-escaping="false">
      <ResultSet xmlns="urn:com.sap.b1i.adapter:jdbcadapter" rowCount="1">
        <Row>
          <DirectRate>Y</DirectRate>
        </Row>
      </ResultSet>
    </Payload>
    <Payload Role="C" id="atom8_2" system="0010000100" mode="single" method="Automatic detection by key word(Automatic detection by key word)" plr="4" dbtype="SQL" blockExecution="false" compatibilityMode="true" delimiter=";" sql="select CurrCode from OCRN" disable-output-escaping="false">
      <ResultSet xmlns="urn:com.sap.b1i.adapter:jdbcadapter" rowCount="17">
        <Row>
          <CurrCode>CZK</CurrCode>
        </Row>
        <Row>
          <CurrCode>DKK</CurrCode>
        </Row>
        <Row>
          <CurrCode>NOK</CurrCode>
        </Row>
        <Row>
          <CurrCode>SEK</CurrCode>
        </Row>
        <Row>
          <CurrCode>AUD</CurrCode>
        </Row>
        <Row>
          <CurrCode>CAD</CurrCode>
        </Row>
        <Row>
          <CurrCode>EUR</CurrCode>
        </Row>
        <Row>
          <CurrCode>HUF</CurrCode>
        </Row>
        <Row>
          <CurrCode>LVL</CurrCode>
        </Row>
        <Row>
          <CurrCode>BGN</CurrCode>
        </Row>
        <Row>
          <CurrCode>LTL</CurrCode>
        </Row>
        <Row>
          <CurrCode>RON</CurrCode>
        </Row>
        <Row>
          <CurrCode>INR</CurrCode>
        </Row>
        <Row>
          <CurrCode>GBP</CurrCode>
        </Row>
        <Row>
          <CurrCode>USD</CurrCode>
        </Row>
        <Row>
          <CurrCode>JPY</CurrCode>
        </Row>
        <Row>
          <CurrCode>PLN</CurrCode>
        </Row>
      </ResultSet>
    </Payload>
    <Payload Role="X" id="atom4">
      <Payload Role="C" id="atom1" system="0010000100" systype="B1.9.0" b1login="Technical User" class="SBObob" function="SetCurrencyRate" parameters="USD,2015.06.23,0.8925383791503034,true" throwException="false" DIresult="" DImsg="">
        <CallResults xmlns="">
          <CallResult>
            <VoidReturn/>
          </CallResult>
        </CallResults>
      </Payload>
      <Payload Role="C" id="atom1" system="0010000100" systype="B1.9.0" b1login="Technical User" class="SBObob" function="SetCurrencyRate" parameters="JPY,2015.06.23,0.00720876585928489,true" throwException="false" DIresult="" DImsg="">
        <CallResults xmlns="">
          <CallResult>
            <VoidReturn/>
          </CallResult>
        </CallResults>
      </Payload>
      <Payload Role="C" id="atom1" system="0010000100" systype="B1.9.0" b1login="Technical User" class="SBObob" function="SetCurrencyRate" parameters="BGN,2015.06.23,0.5112997238981491,true" throwException="false" DIresult="" DImsg="">
        <CallResults xmlns="">
          <CallResult>
            <VoidReturn/>
          </CallResult>
        </CallResults>
      </Payload>
      <Payload Role="C" id="atom1" system="0010000100" systype="B1.9.0" b1login="Technical User" class="SBObob" function="SetCurrencyRate" parameters="CZK,2015.06.23,0.036779579977196664,true" throwException="false" DIresult="" DImsg="">
        <CallResults xmlns="">
          <CallResult>
            <VoidReturn/>
          </CallResult>
        </CallResults>
      </Payload>
      <Payload Role="C" id="atom1" system="0010000100" systype="B1.9.0" b1login="Technical User" class="SBObob" function="SetCurrencyRate" parameters="DKK,2015.06.23,0.13400335008375208,true" throwException="false" DIresult="" DImsg="">
        <CallResults xmlns="">
          <CallResult>
            <VoidReturn/>
          </CallResult>
        </CallResults>
      </Payload>
      <Payload Role="C" id="atom1" system="0010000100" systype="B1.9.0" b1login="Technical User" class="SBObob" function="SetCurrencyRate" parameters="GBP,2015.06.23,1.4082523588227012,true" throwException="false" DIresult="" DImsg="">
        <CallResults xmlns="">
          <CallResult>
            <VoidReturn/>
          </CallResult>
        </CallResults>
      </Payload>
      <Payload Role="C" id="atom1" system="0010000100" systype="B1.9.0" b1login="Technical User" class="SBObob" function="SetCurrencyRate" parameters="HUF,2015.06.23,0.003223310985043837,true" throwException="false" DIresult="" DImsg="">
        <CallResults xmlns="">
          <CallResult>
            <VoidReturn/>
          </CallResult>
        </CallResults>
      </Payload>
      <Payload Role="C" id="atom1" system="0010000100" systype="B1.9.0" b1login="Technical User" class="SBObob" function="SetCurrencyRate" parameters="PLN,2015.06.23,0.23982540710362857,true" throwException="false" DIresult="" DImsg="">
        <CallResults xmlns="">
          <CallResult>
            <VoidReturn/>
          </CallResult>
        </CallResults>
      </Payload>
      <Payload Role="C" id="atom1" system="0010000100" systype="B1.9.0" b1login="Technical User" class="SBObob" function="SetCurrencyRate" parameters="RON,2015.06.23,0.22318937618569357,true" throwException="false" DIresult="" DImsg="">
        <CallResults xmlns="">
          <CallResult>
            <VoidReturn/>
          </CallResult>
        </CallResults>
      </Payload>
      <Payload Role="C" id="atom1" system="0010000100" systype="B1.9.0" b1login="Technical User" class="SBObob" function="SetCurrencyRate" parameters="SEK,2015.06.23,0.10861183217299691,true" throwException="false" DIresult="" DImsg="">
        <CallResults xmlns="">
          <CallResult>
            <VoidReturn/>
          </CallResult>
        </CallResults>
      </Payload>
      <Payload Role="C" id="atom1" system="0010000100" systype="B1.9.0" b1login="Technical User" class="SBObob" function="SetCurrencyRate" parameters="NOK,2015.06.23,0.11460661280155865,true" throwException="false" DIresult="" DImsg="">
        <CallResults xmlns="">
          <CallResult>
            <VoidReturn/>
          </CallResult>
        </CallResults>
      </Payload>
      <Payload Role="C" id="atom1" system="0010000100" systype="B1.9.0" b1login="Technical User" class="SBObob" function="SetCurrencyRate" parameters="AUD,2015.06.23,0.6878525244187647,true" throwException="false" DIresult="" DImsg="">
        <CallResults xmlns="">
          <CallResult>
            <VoidReturn/>
          </CallResult>
        </CallResults>
      </Payload>
      <Payload Role="C" id="atom1" system="0010000100" systype="B1.9.0" b1login="Technical User" class="SBObob" function="SetCurrencyRate" parameters="CAD,2015.06.23,0.7233273056057866,true" throwException="false" DIresult="" DImsg="">
        <CallResults xmlns="">
          <CallResult>
            <VoidReturn/>
          </CallResult>
        </CallResults>
      </Payload>
      <Payload Role="C" id="atom1" system="0010000100" systype="B1.9.0" b1login="Technical User" class="SBObob" function="SetCurrencyRate" parameters="INR,2015.06.23,0.01403032233262527,true" throwException="false" DIresult="" DImsg="">
        <CallResults xmlns="">
          <CallResult>
            <VoidReturn/>
          </CallResult>
        </CallResults>
      </Payload>
    </Payload>
    <Payload Role="R" id="atom0" pltype="xml"/>
  </Body>
</Msg>




Notes

  • Thanks to Atilla who give me the idea to write this little article.
  • For production environment you would add error management, some Configuration (SysId entries), maybe user notification (SAP Business One internal messaging, email, etc).
  • Different web servers may respond with different XML so this scenario will actually work with ECB only.
  • Please note that this is my very first document here in SCN: don’t hesitate to inform me if there’s something wrong or something that could be done better!

Regards,

Pierre

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Bramnha Tiwari

    Dear   Pierre,

    Thanks for the knowledge sharing. As per your logic i tried but while at B1call function it throws a error


    <Msg xmlns=”urn:com.sap.b1i.vplatform:entity>

      <Body>

    <Payload Role=”C id=”atom8 system=”0010000107 systype=”B1.9.0 b1login=”Technical Userclass=”SBObob function=”SetCurrencyRate parameters=”concat(/ExchangeRate/@time, &quot;,&quot;,/ExchangeRate/@currency, &quot;,&quot;, /ExchangeRate/@rate,&quot;,true&quot;) throwException=”false DIresult=” DImsg=”>

    <CallResults xmlns=” HasError=”true>

    The method’s paramters trying to call mismatch the existing method’s

    </CallResults>

    </Payload>

    </Body>

    </Msg>

    Did any one come across this error please guide me

    Regards:

    Navin

    (0) 
    1. Pierre Canali Post author

      Hi Navin,

      if you are using newer version of b1if you cannot use entities inside function call.

      Try with this:

      concat(/ExchangeRate/@currency,’,’, /ExchangeRate/@date,’,’, /ExchangeRate/@rate, ‘,’,’true’)


      Let me know if it worked.


      Regards


      Pierre

      (0) 
      1. Bramnha Tiwari

        Hi Pierre,

        I have changed the parameter as you had mentioned but still i get a error message while debug

        The method’s parameters trying to call mismatch the existing method’s.

        At B1 call function atom it displays


        <Message_Is_Not_Wellformed/>


        I also have a doubt in the Atom 7 you had defined xsl template with a URL, while i am running that url in browser it shows page is not found.


        http://www.gesmes.org/xml/2002-08-01

        http://www.ecb.int/vocabulary/2002-08-01/eurofxref





        Regards:

        Navin

        (0) 

Leave a Reply