ShivaPrasad
21 days agoNew Contributor II
Workflow Certified Status with User Details
Hi Team,
We have a requirement like, Finance team wants to have one report with the details of Workflow Status, who certified the respective entity (name of the user) along with certification time stamp.
used the below Dash Board data set rule. followed the below steps to get the grid view
- Attached the Business rule to Data Adapter
- Attached the Data Adapter to BI Viewer.
But here i am getting two certified status for the entity. we need the latest certified status. can you please help us to resolve this.
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
Namespace OneStream.BusinessRule.DashboardDataSet.WF_Certification_Status
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardDataSetArgs) As Object
Try
Select Case args.FunctionType
Case Is = DashboardDataSetFunctionType.GetDataSetNames
Dim DSnames As New List(Of String)()
DSnames.Add("GetWFStatusandCertificationF")
Return DSnames
Case Is = DashboardDataSetFunctionType.GetDataSet
If args.DataSetName.XFEqualsIgnoreCase("GetWFStatusandCertificationF") Then
Return GetWFStatusandCertificationF(si, args)
End If
End Select
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Private Function GetWFStatusandCertificationF(ByVal si As SessionInfo, ByVal args As DashboardDataSetArgs) As DataTable
Dim WFTable As New DataTable("WFSTATUS")
Dim CertTable2 As New DataTable("WFCertSTATUS2")
Dim CombinedTable As New DataTable("CombinedWFandCertStatus")
CombinedTable.Columns.Add("ProfileKey")
CombinedTable.Columns.Add("ProfileName")
CombinedTable.Columns.Add("ScenarioName")
CombinedTable.Columns.Add("TimeName")
CombinedTable.Columns.Add("StatusText")
CombinedTable.Columns.Add("LastExecutedStepStatus")
CombinedTable.Columns.Add("LastExecutedStepTimeUTC")
CombinedTable.Columns.Add("LastExecutedStepTimeEST")
CombinedTable.Columns.Add("SignOffState")
CombinedTable.Columns.Add("UserName")
CombinedTable.Columns.Add("TimeStamp")
Dim WFName As String = "ASIA"
Dim WFScenario As String = "Actual"
Dim WFTime As String = "2025M1"
Dim methodTypeId As String = XFCommandMethodTypeId.WorkflowStatus
Dim resultDataTableName As String = "WFSTATUS"
Dim methodQuery As String
Dim customSubVars As New Dictionary(Of String, String)
brapi.ErrorLog.LogMessage(si,"1")
methodQuery = "{" & WFName & "}{" & WFScenario & "}{" & WFTime & "}{AllProfiles}{Descendants}{ProfileName like '*.Confirm*'}"
'methodQuery = "{" & WFName & "}{" & WFScenario & "}{" & WFTime & "}{'%Confirm_Certify'}{Descendants}{ProfileName like '*.Confirm*'}"
Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
WFTable.Columns.Add("ProfileKey")
WFTable.Columns.Add("ProfileName")
WFTable.Columns.Add("ScenarioName")
WFTable.Columns.Add("TimeName")
WFTable.Columns.Add("StatusText")
WFTable.Columns.Add("LastExecutedStepStatus")
WFTable.Columns.Add("LastExecutedStepTimeUTC")
WFTable.Columns.Add("LastExecutedStepTimeEST")
Dim objDataSet As DataSet = BRApi.Database.ExecuteMethodCommand(dbConnApp, methodTypeId, methodQuery, resultDataTableName, customSubVars)
'Dim FilterData As Datarow() = WFTable.Select("ProfileKey", "ProfileName","ScenarioName", "TimeName", "StatusText", "LastExecutedStepStatus","LastExecutedStepTimeUTC","LastExecutedStepTimeEST Desc")
For Each row As DataRow In objDataSet.Tables("WFSTATUS").Rows
WFTable.Rows.Add(row.Item("ProfileKey"), row.Item("ProfileName"), row.Item("ScenarioName"), row.Item("TimeName"), row.Item("StatusText"), row.Item("LastExecutedStepStatus"), row.Item("LastExecutedStepTime"), row.Item("LastExecutedStepTime"))
Next
' For Each row As DataRow In FilterData
' WFTable.Rows.Add(row.Item("ProfileKey"), row.Item("ProfileName"), row.Item("ScenarioName"), row.Item("TimeName"), row.Item("StatusText"), row.Item("LastExecutedStepStatus"), row.Item("LastExecutedStepTime"), row.Item("LastExecutedStepTime"))
' Next
End Using
brapi.ErrorLog.LogMessage(si,"2")
methodTypeId = XFCommandMethodTypeId.CertificationForWorkflowUnit
methodQuery = "{" & WFName & "}" & "{" & WFScenario & "}" & "{" & WFTime & "}" & "{True}" & "{}"
Using DBConnAppForCert2 As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
CertTable2.Columns.Add("ProfileKey")
CertTable2.Columns.Add("ScenarioKey")
CertTable2.Columns.Add("TimeKey")
CertTable2.Columns.Add("SignOffState")
CertTable2.Columns.Add("UserName")
CertTable2.Columns.Add("TimeStamp")
Dim objDataSet As DataSet = BRApi.Database.ExecuteMethodCommand(DBConnAppForCert2, methodTypeId, methodQuery, "WFCertSTATUS2", customSubVars)
For Each row As DataRow In objDataSet.Tables("WFCertSTATUS2_SignOffGroups").Rows
CertTable2.Rows.Add(row.Item("ProfileKey"), row.Item("ScenarioKey"), row.Item("TimeKey"), row.Item("SignOffState"), row.Item("UserName"), row.Item("TimeStamp"))
Next
End Using
brapi.ErrorLog.LogMessage(si,"3")
For Each wfRow As DataRow In WFTable.Rows
Dim profileKey As Object = wfRow("ProfileKey")
Dim certRows As DataRow() = CertTable2.Select("ProfileKey = '" & profileKey.ToString() & "'")
For Each certRow As DataRow In certRows
Dim newRow As DataRow = CombinedTable.NewRow()
newRow("ProfileKey") = wfRow("ProfileKey")
newRow("ProfileName") = wfRow("ProfileName")
newRow("ScenarioName") = wfRow("ScenarioName")
newRow("TimeName") = wfRow("TimeName")
newRow("StatusText") = wfRow("StatusText")
newRow("LastExecutedStepStatus") = wfRow("LastExecutedStepStatus")
newRow("LastExecutedStepTimeUTC") = wfRow("LastExecutedStepTimeUTC")
newRow("LastExecutedStepTimeEST") = wfRow("LastExecutedStepTimeEST")
newRow("SignOffState") = certRow("SignOffState")
newRow("UserName") = certRow("UserName")
newRow("TimeStamp") = certRow("TimeStamp")
CombinedTable.Rows.Add(newRow)
Next
Next
brapi.ErrorLog.LogMessage(si,"4")
Return CombinedTable
End Function
End Class
End Namespace