Different Queryresult in Management Studio and coresuite service
Hello,
I want to update the currency rates with the coresuite service.
My fist step is to check, if there are already entries in the currency rate table “ORTT”. I use this code:
var alreadySet = new System.Collections.Generic.Dictionary<string, System.Collections.Generic.List<System.DateTime>>();
string alreadySetQuery =
@"SELECT Currency, RateDate
FROM ORTT
WHERE RateDate > DATEADD(day, DATEDIFF(day, 0, GETDATE()), -10) AND RateDate <= convert(varchar(10), GETDATE(), 112)
AND Rate != 0.0
ORDER BY Currency, RateDate ASC";
using (System.Data.Common.DbDataReader sdr = SwissAddonFramework.Utils.SQL.ExecuteReader(alreadySetQuery))
{
while (sdr.Read())
{
string curr = sdr["Currency"].ToString();
string d = sdr[1].ToString();
System.DateTime rateDate = sdr.GetDateTime(1);
if(!alreadySet.ContainsKey(curr))
alreadySet.Add(curr, new System.Collections.Generic.List<System.DateTime>());
alreadySet[curr].Add(rateDate);
if(showInformations)
CoresuiteServiceAPI.DebugLog.Info("Company", "curr: " + curr + " rateDate " + rateDate + " d: " + d);
}
}
The query has different results compared to querymanager or Management Studio.
Here is a screenshot of the data in SAP:

There are no entries available for “DKK” or “CHF”.
Here are the results from the Management Studio, which shows correctly no result for the 16th april.

When I ran the code through coresuite service it has a result for the 16th april.

For some reason the service has one more row with the current date. I have change the query in many ways using e.g. “between”, “<”, just use “getdate()” without convert and so on… I have replace SQLDataReader with DbDataReader …
I have run the code in a customize rule, where it works correct.
Now I have no idea, why the line with the current date appear and how to avoid its apearence.
Has anyone an idea, how to get only the rows which ware in the table?
Regards
Marco
-
I have find out, the I have to use coresuiteServiceAPI instead of SwissAddonFramework. The query was run in the other database.
0 -
hey, you could just use the API function for setting exchange rates, there is an parameter if existing rates should be updated/overwritten or not. the function can do the validation automatically.
we use that for fetching the ECB rates (XML) every night and set them using a small CS rule.
simple code example for the CS rule, of course use proper try/catch etc.:
// create company connection, set string companyDatabase to DB name var company = CoresuiteServiceAPI.SAPConnection.CreateConnection(companyDatabase).GetB1Connector().Company; SAPbobsCOM.SBObob oSBObob = (SAPbobsCOM.SBObob) company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoBridge); // double rate = the exchange rate from source - e.g. Convert.ToDouble(4.254) // string curr = currency code (EUR/USD…) as string // bool updateEntries = overwrite existing rates or keep them, in your case set to false // loop for each currency - set curr, rate and call API function: oSBObob.SetCurrencyRate(curr, DateTime.Now, rate, updateEntries);
0
Please sign in to leave a comment.
Comments
2 comments