Automatic addition of FX Rate Types

NidhiMangtani
Contributor III

Hi All,

I am trying to automate addition of FX Rate Types on a button click with some logic. 

Inserting values into FxRateType and AuditFxRateType using the below code snippet

------------------------------------

Public Sub AddCurrentFXRate(ByVal si As SessionInfo, ByVal api As Object, ByVal sCurrentOpsName As String, ByVal dt As DataTable)

Dim dbConnApp As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)

Try

Dim sSQL As New Text.StringBuilder
Dim sFXRateTypeGuid As String = Guid.NewGuid.ToString

sSQL.AppendLine("INSERT INTO FxRateType
(UniqueID, Name, Description,
AccessGroupUniqueID, MaintenanceGroupUniqueID, XmlData)
VALUES ('" & sFXRateTypeGuid & "', '" & sCurrentOpsName & "', '" & sCurrentOpsName & "',
'" & dt.Rows(0)("AccessGroupUniqueID").ToString & "', '" & dt.Rows(0)("MaintenanceGroupUniqueID").ToString & "', ' ')")

sSQL.AppendLine("INSERT INTO AuditFxRateType
(AuditGuid, AuditSeqId, AuditUser,
AuditTime, AuditInsUpdateDel,
UniqueID, Name, Description,
AccessGroupUniqueID, MaintenanceGroupUniqueID, XmlData)
VALUES ('" & Guid.NewGuid.ToString & "', '1', '" & BRApi.Security.Authorization.GetUser(si, si.AuthToken.UserName).User.Name & "',
'" & DateTime.Now & "', '0',
'" & sFXRateTypeGuid & "', '" & sCurrentOpsName & "', '" & sCurrentOpsName & "',
'" & dt.Rows(0)("AccessGroupUniqueID").ToString & "', '" & dt.Rows(0)("MaintenanceGroupUniqueID").ToString & "', ' ')")

Using dbConnApp
dbConnApp.BeginTrans()
BRAPi.Database.ExecuteSql(dbConnApp, sSQL.tostring, False)
dbConnApp.CommitTrans()
End Using

Catch ex As Exception
dbConnApp.Dispose()
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try

End Sub

------------------------------------

The values get added into the tables fine, but the newly added rate type does not show in FX Rates interface even after refreshing the application. Would someone please guide what I am missing here?

Thanks,
Nidhi Mangtani
5 REPLIES 5

NicolasArgente
Valued Contributor

Hi Nidhi,
I would not take this approach. I would use the native OS function Like 
  objXFResult = BRApi.Finance.Data.SetFxRate(si, objfxRate)

For Each fxRate As Object In fxRates

                                                            'Process Average Rate

                                                            objFxRatePk.FxRateType = "AverageRate"

                                                            objFxRatePk.Time = period

                                                            objFxRatePk.SourceCurrency = sourceCurr

                                                            objFxRatePk.DestCurrency = fxRate.Key

                                                            objFxRate.FxRatePkUsingNames = objfxRatePk

                                                            objfxRate.Amount = fxRate.Value

                                                            objFxRate.HasData = True

                                                            objFxRate.IsInvalid = False



Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

NicolasArgente
Valued Contributor

This could also guide you : https://community.onestreamsoftware.com/t5/Application-Build/Copy-FXRates/m-p/631

Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

Hi Nicolas, 

Thanks for your response. I am not trying to set FX Rates. I am trying to create a new FX Rate Type on a button click.

Thanks,
Nidhi Mangtani

Krishna
Contributor III

BRAPI is much better than SQL in some cases. I would recommend API functions vs SQL.

Hello Krishna,

Thanks for your response.  I had tried using SaveCustomDataTable to insert data in the mentioned tables. Since these are not custom tables, it did not work. 

Thanks,
Nidhi Mangtani