Updating Exchange Rates Automatically in SAP Business One

This is a personal itch I had to scratch, instead waiting for our accountant to update the exchange rates in SBO to allow us enter new orders, I wrote a small PowerShell script to do that for us.

The script is quite simple and I didn’t put much effort to cover all corner cases so keep that in mind if you intend to use it. What the script dose, it connect to BOI API service at http://www.boi.org.il/currency.xml and fetch the exchange rates for the current date as, if no ER (exchange rate) was published that day, it will go back one day (up to 7 days) and will try to fetch the ER of the day before.

# Taken from http://www.boi.org.il/he/Markets/Pages/explainxml.aspx
# the Key in hash is used in our table, and the value is for BOI
$currency_code = @{  "$"   = "01";   "לש"  = "02";  "Eur" = "27"; }

$API_URL = 'http://www.boi.org.il/currency.xml?'
$rdate = [DateTime]::Now
$script:recursive_limit = 7
$SQLServer = "localhost"
$DB_USER = "scripts_user"
$DB_PASS = "[email protected]$$w0rd"
$SQLDBNAME = "test_db"


$proxy = [System.Net.WebRequest]::GetSystemWebProxy()
$proxy.Credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials
$web = New-Object System.Net.WebClient
$web.proxy = $proxy
$web.UseDefaultCredentials = $true

function notify_by_mail([string]$body=$null, [string]$subject = "SAP currency update script") 
{
    send-mailmessage `
        -from "PowerShell script <[email protected]>" `
        -to "rabin <[email protected]>" `
        -subject $subject `
        -body $body `
        -priority High -dno onSuccess, onFailure -smtpServer smtp.localhost
}

function upsert([string]$rate="0.0", [string]$currency, [string]$date = [DateTime]::Now.ToString('yyyyMMdd'))
{

## The magic query, this will INSERT (if missing) / UPDATE (if exists) -- "UPSERT"
$query = "
MERGE dbo.ORTT AS target 
    USING (
        VALUES ( '$date',  N'$currency',  $rate,  'I',  9)
        ) AS source (RateDate,Currency,Rate,DataSource,UserSign) 
    ON target.RateDate = '$date' AND target.Currency = N'$currency' 
    WHEN MATCHED THEN 
        UPDATE SET 
             RateDate = source.RateDate,
       Currency = source.Currency,
       Rate = source.Rate,
       DataSource = source.DataSource,
       UserSign = source.UserSign 
  WHEN NOT MATCHED THEN 
    INSERT (RateDate,Currency,Rate,DataSource,UserSign) VALUES (source.RateDate,source.Currency,source.Rate,source.DataSource,source.UserSign);
";

    try {
        return Invoke-Sqlcmd -Database $SQLDBNAME -Username $DB_USER -Password $DB_PASS -Query $query -Debug
        #return Invoke-Sqlcmd -Database $SQLDBNAME -Username $DB_USER -Password $DB_PASS -Query "SELECT * FROM ORTT WITH (nolock) WHERE RateDate='$date' ORDER BY ratedate DESC"
    }
    catch {
            $ErrorMessage = $_.Exception.Message
            $FailedItem   = $_.Exception.ItemName
            notify_by_mail -body "$ErrorMessage" -subject "SAP Powershell script Error: $FailedItem"
            exit
    }

}


function fetch_rate([string]$currency, [string]$rdate) 
{
    $url = $API_URL + 'rdate=' + $rdate + '&curr=' + $currency_code.Item($currency_key); Write-Debug "Trying: $url";
    try 
    {
        [xml]$webpage = $web.DownloadString($url)
        $error_node = $webpage.SelectSingleNode("//CURRENCIES/ERROR1")

        if (-not $error_node) 
        {
            $rate = $webpage.CURRENCIES.CURRENCY.RATE
            return $rate
        }
        else 
        {       
            Write-Debug "No exchange rate published for this date[$rdate]"

            $new_rdate = [DateTime]::ParseExact($webpage.CURRENCIES.REQUESTED_DATE, "yyyyMMdd", $null).AddDays(-1)
            if ($script:recursive_limit -ge 0 ) 
            {
                $script:recursive_limit--
                $rate = fetch_rate -currency "$currency" -rdate $new_rdate.ToString('yyyyMMdd')
        
                return $rate
            }
            else {
                #return "ERROR: Recursive limit reached"
                notify_by_mail -body "ERROR: Recursive limit reached"
                exit
            }
        }   
    }
    catch {
        $ErrorMessage = $_.Exception.Message
        $FailedItem   = $_.Exception.ItemName
        notify_by_mail -body "$ErrorMessage" -subject "SAP Powershell script Error: $FailedItem"
    }

    return "Error"
}

foreach ($currency_key in $currency_code.keys) {
    $rate =  fetch_rate -currency "$currency_code" -rdate $rdate.ToString('yyyyMMdd')
    $format = @{Expression={$_.RateDate};Label=”Date”;width=22},@{Expression={$_.Currency};Label=”Currency”; width=8},@{Expression={$_.Rate};Label="Rate"}
    upsert -rate $rate -currency $currency_key |  format-table $format   
}

Exit-PSSession

 

Leave a Reply

Your email address will not be published. Required fields are marked *