The translation is based on closing rates.
I've attached below the member formula used to calculate our opening balances.
Imports System
Imports System.Data
Imports System.Data.Common
Imports System.IO
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Linq
Imports Microsoft.VisualBasic
Imports OneStream.Shared.Common
Imports OneStream.Shared.Wcf
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Database
Imports OneStream.Stage.Engine
Imports OneStream.Stage.Database
Imports OneStream.Finance.Engine
Imports OneStream.Finance.Database
Namespace OneStream.Formula
Public Class MainClass
Public Sub Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api as FinanceRulesApi, ByVal args as FinanceRulesArgs)
Try
'XFFormula
Dim curScenario As String = api.Pov.Scenario.Name
Dim curTime As Member = api.Pov.Time
Dim curYear As Integer = TimeDimHelper.GetSubComponentsFromId(curTime.MemberId).Year
Dim curMonth As Integer = TimeDimHelper.GetSubComponentsFromId(curTime.MemberId).Month
Dim ScenOpe As String = api.Scenario.Text(api.Pov.Scenario.MemberId, 1)
Dim referenceBR As New OneStream.BusinessRule.Finance.OpeningScenario.MainClass
Dim OpeScenario As String = referenceBR.fctOpeningScenario(si, api, args)
If Not api.Entity.HasChildren() Then
' If curScenario = "ACTUAL" 'And curYear = "xxx" Then
' api.Data.ClearCalculatedData("F#OpenBal_Calc:O#AdjConsolidated",False,True,True)
' ' P1
' If api.Time.IsFirstPeriodInYear Then
' 'Local entity prior year M12 closing balance brought forward - U4#Local_Total to U4#Local_Data and O#BeforeElim to O#Import
' api.Data.Calculate("F#OpenBal_Calc:O#Import = RemoveZeros(F#Closing:O#BeforeElim:T#POVPriorYearM12)")
' Else
' ' P2-12
' 'Local entity prior year M12 closing balance brought forward - U5#Local_Total to U5#Local_Data and O#BeforeElim to O#Import
' api.Data.Calculate("F#OpenBal_Calc:O#Import = RemoveZeros(F#OpenBal_Calc:O#BeforeElim:T#POVPrior1)")
' End If
' End If
'Exclude CY_ProfitLoss which should not roll forward
If curScenario = "ACTUAL" Then 'And curYear = "xxx" Then
'HvdK 20210720 - disable below Clear due to DV
api.Data.ClearCalculatedData("F#OpenBal_Calc:O#AdjConsolidated",False,True,True)
'******
' P1
'******
If curMonth = 1 Then
api.Data.Calculate("F#OpenBal_Calc = S#" & OpeScenario &":F#Closing:T#PovPriorYearM12)","A#GROUP_BS_STRUCTURES.Base.Remove(A#L701_OS, A#L701, A#INTERCO_EQUITY)")
api.Data.Calculate("A#L701:F#OpenBal_Calc = RemoveZeros(A#L701:S#" & OpeScenario &":F#Closing:T#PovPriorYearM12 + A#L701_OS:S#" & OpeScenario &":F#Closing:T#PovPriorYearM12)")
If api.Pov.Cons.Name = "Elimination" Then
api.Data.Calculate("F#OpenBal_Calc:O#Elimination = S#" & OpeScenario & ":O#Elimination:F#Closing:T#PovPriorYearM12","A#GROUP_BS_STRUCTURES.Base.Remove(A#L701_OS, A#L701, A#INTERCO_EQUITY)")
api.Data.Calculate("A#L701:F#OpenBal_Calc:O#Elimination = RemoveZeros(A#L701:S#" & OpeScenario &":O#Elimination:F#Closing:T#PovPriorYearM12 + A#L701_OS:S#" & OpeScenario &":O#Elimination:F#Closing:T#PovPriorYearM12)")
End If
'*********
' P2-P12
'*********
Else
api.Data.Calculate("F#OpenBal_Calc = F#OpenBal_Calc:T#PovPrior1)","A#GROUP_BS_STRUCTURES.Base.Remove(A#L701_OS)")
If api.Pov.Cons.Name = "Elimination" Then
api.Data.Calculate("F#OpenBal_Calc:O#Elimination = O#Elimination:F#OpenBal_Calc:T#PovPrior1","A#GROUP_BS_STRUCTURES.Base.Remove(A#L701_OS)")
End If
End If
End If
' If curScenario = "BUDGET" Then 'Source Opening balances from Actual PY M12
' If api.Time.IsFirstPeriodInYear Then 'If it is Month 1 - Get opening from the prior year Actual Closing
' 'Set the source Scenario
' Dim sSrcScenario As String = OpeScenario
' 'Set the destination scenario based on the Forecast POV being calculated
' Dim sDestScenario As String = api.Pov.Scenario.Name
' 'Get the Current Time ID - Needed for specific functions
' Dim iCurTimeId As Integer = api.Pov.Time.MemberId
' Dim sCurTime As String = api.Pov.Time.Name
' 'Get the Current Year ID - Needed for specific functions (to define Prior Year)
' Dim iCurYear As Integer = api.Time.GetYearFromId(api.Pov.Time.MemberId)
' 'Define the Prior Year End
' Dim iPriorYear As Integer = curYear - 1
' Dim sPriorYE As String = iPriorYear.XFToString & "M12"
' 'Clear any existing data (including durable)
' api.Data.ClearCalculatedData("S#" & sDestScenario & ":T#" & sCurTime &":F#OpenBal_Calc",True,True,True,True)
' '1 This section defines the destination and source data buffers as variables. This allows us to run a series of functions, hold them in memory and then use the results in other functions.
' '1a: This is the destination Data Buffer or the left side of the equation. Define the Destination Info that varies from the Source.
' Dim destination As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("S#" & curScenario &":T#" & sCurTime & ":F#OpenBal_calc:O#Import")
' '1b: There is one Source Data Buffers. This will define the right side of the equation.
' '1b1: Define actualPYE: This Data Buffer is looking at the priorYear M12 and pulling the YTD value for a specific intersection.
' Dim closingPYE As DataBuffer = api.Data.GetDataBuffer(DataApiScriptMethodType.Calculate, "S#" & sSrcScenario &":F#Closing:O#BeforeElim:T#" & sPriorYE &"",False, destination)
' '2: Due to extensibility, we need to convert the Source Data Buffers From Detailed Actual to a Summary Budget Destination Data Buffer.
' 'The summaryBudget Data Buffers below will use the results From our actualPYE Data Buffers In the previous section.
' '2a: Define the Data Buffer for each summaryBudget line.
' Dim summaryBudget As DataBuffer = api.Data.ConvertDataBufferExtendedMembers("Reporting", "Actual" , closingPYE)
' '3: In this section, we will set the name for each summaryBudget formula variable above and cache the results in memory.
' api.Data.FormulaVariables.SetDataBufferVariable("summaryBudget", summaryBudget, False)
' '4: Can Calculate here - Calculate a Formula Variables for Trailing 12 Month Avg. Example:(FormulaVariable1 + FormulaVariable2 + FormulaVariable3)/12
' Dim result As DataBuffer = api.Data.GetDataBufferUsingFormula("($summaryBudget)")
' '5: Set the Data Buffer results after calculation is completed using Accounts and O#Import for Destination Info.
' api.Data.SetDataBuffer(result,destination,,,,,,,,,,,,,True)
' '6: Clear L701_OS (RE Profit for the year) which does not roll-fwd
' api.Data.ClearCalculatedData("A#L701_OS:S#" & sDestScenario & ":T#" & sCurTime &":F#OpenBal_Calc",True,True,True,True)
' Else 'For all other months, source from prior month as this is more efficient than going across years
' api.Data.Calculate("F#OpenBal_Calc:O#Import:S#" + curScenario + " = RemoveZeros(S#" + curScenario + ":F#OpenBal_Calc:O#BeforeElim:T#POVPrior1)")
' End If
' Else If curScenario = "BUDGET_WORKING" Or curScenario = "BUDGET_PRE" Then' Source Opening balances from Sep FC ("BUDGET_PRE" is a copy of BUDGET_WORKING)
If curScenario = "BUDGET_WORKING" Or curScenario = "BUDGET_PRE" Or curScenario = "BUDGET" Then' Source Opening balances from Sep FC ("BUDGET_PRE" is a copy of BUDGET_WORKING)
' brapi.ErrorLog.LogMessage(si,"opening scen - " & OpeScenario)
api.Data.ClearCalculatedData("F#OpenBal_Calc",True,True,True,True)
If api.Time.IsFirstPeriodInYear Then 'If it is Month 1 - Get opening from the PY FCT09 P12 Closing
api.Data.Calculate("F#OpenBal_Calc:O#Import:S#" + curScenario + " = RemoveZeros(S#" + OpeScenario + ":F#Closing:O#BeforeElim:T#POVPriorYearM12)","A#GROUP_BS_STRUCTURES.Base.Remove(A#L701_OS, A#L701)",,,,,,,,,,,,,,)
api.Data.Calculate("A#L701:F#OpenBal_Calc:O#Import:S#" + curScenario + " = RemoveZeros(A#L701:S#" + OpeScenario + ":F#Closing:O#BeforeElim:T#POVPriorYearM12 + A#L701_OS:S#" + OpeScenario + ":F#Closing:O#BeforeElim:T#POVPriorYearM12)")
Else 'For all other months, source from prior month as this is more efficient than going across years
api.Data.Calculate("F#OpenBal_Calc:O#Import:S#" + curScenario + " = RemoveZeros(S#" + curScenario + ":F#OpenBal_Calc:O#BeforeElim:T#POVPrior1)")
End If
End If
End If
'HvdK 20210720 - Ret Earnings Opening should be calculated at parent entities as well
If api.Entity.HasChildren() Then
api.Data.Calculate("A#L701:F#OpenBal_Calc = RemoveZeros(A#L701:S#" & OpeScenario &":F#Closing:T#PovPriorYearM12 + A#L701_OS:S#" & OpeScenario &":F#Closing:T#PovPriorYearM12)")
End If
'End XFFormula
Catch ex As Exception
Throw New XFException(si, ex)
End Try
End Sub
'XFHelperFunctions
'End XFHelperfunctions
End Class
End Namespace
