Originally posted by Chad Fortman
Samuel,
A while ago I created a Business Rule function that I think is in the same vein as what you are trying to accomplish. Below is the code. This takes the Connection Name and a user friendly transaction type (string) as parameters. First it determines what consolidated account the user is looking for and feeds it to a query against the OneStream Application Database to pull all source ledger accounts that are mapped to the consolidated account. The source account numbers are returned in a data table. I use this in our intercompany dashboards which are dynamic because, of this query. Our accounting team maintains the transformation rules which are used to determine what source GL accounts should be included in our intercompany data pulls. Basically the dashboard is generic across our divisions, they just tell me what division and what intercompany they want (AP, AP, MISC) and I can pull their transaction level detail from the source GL. If something is wrong or out of balance, they just need to verify the mapping is correct.
Const sARAccount As String = "39503" Const sMiscAccount As String = "39504" Const sAPAccount As String = "39505" Public Function GetTransformationRules(ByVal si As SessionInfo, ByVal conn As String, ByVal trans As String) As DataTable Try Dim query As New Text.StringBuilder Dim dtAccounts As DataTable query.AppendLine("SELECT * ") query.AppendLine("FROM StageRules ") If trans = "AR" Then query.AppendLine("WHERE OutputValue IN ('" + sARAccount + "')") Else If trans = "AP" Then query.AppendLine("WHERE OutputValue IN ('" + sAPAccount + "')") Else If trans = "MISC" Then query.AppendLine("WHERE OutputValue IN ('" + sMiscAccount + "')") End If query.AppendLine("AND RulesGroupKey IN (") query.AppendLine(" SELECT CAST(UniqueID AS varchar(100))") query.AppendLine(" FROM StageRuleGroups WHERE RuleGroupName LIKE '%Accounts%'") query.AppendLine(" AND DimensionName IN ('Ac')") query.AppendLine(" AND CAST(UniqueID AS varchar(100)) IN (") query.AppendLine(" SELECT RuleGroupKey ") query.AppendLine(" FROM StageRuleProfileMembers ") query.AppendLine(" WHERE CAST(RuleProfileKey AS varchar(100)) = (") query.AppendLine(" SELECT CAST(UniqueID AS varchar(100))") query.AppendLine(" FROM StageRuleProfiles ") query.AppendLine(" WHERE CAST(UniqueID AS varchar(100)) = (") query.AppendLine(" SELECT TOP 1 CAST(ProfileAttributeValue AS varchar(100))") query.AppendLine(" FROM WorkflowProfileAttributes ") query.AppendLine(" WHERE CAST(ProfileKey AS varchar(100)) = (") query.AppendLine(" SELECT TOP 1 CAST(h.ProfileKey AS varchar(100))") query.AppendLine(" FROM WorkflowProfileHierarchy AS h") query.AppendLine(" JOIN WorkflowProfileAttributes AS a ON a.ProfileKey = h.ProfileKey") query.AppendLine(" AND a.ProfileAttributeValue = '" + conn + "'") query.AppendLine(" WHERE h.ProfileName LIKE '%Import%'") query.AppendLine(" AND h.CubeName = 'Consolidated'") query.AppendLine(" )") query.AppendLine(" AND AttributeIndex = 4000") query.AppendLine(" AND ScenarioTypeID = 0") query.AppendLine(" )") query.AppendLine(" )") query.AppendLine(" )") query.AppendLine(")") Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si) dtAccounts = BRApi.Database.ExecuteSql(dbConn, query.ToString(), True) End Using If Not dtAccounts.Rows Is Nothing And dtAccounts.Rows.Count > 0 Then Return dtAccounts Else If trans = "AR" Then Throw New System.Exception("No " + sARAccount + " account mappings exist for: " + conn + ".") Else If trans = "AP" Then Throw New System.Exception("No " + sAPAccount + " account mappings exist for: " + conn + ".") Else If trans = "MISC" Then Throw New System.Exception("No " + sMiscAccount + " account mappings exist for: " + conn + ".") End If Return Nothing End If Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function
