04-06-2022 04:34 AM - last edited Thursday by JackLacava
I am looking for a Business Rule (DataQualityEventhandler) to send e-mail notifications when "Set IC Transaction Status" is used. I know how to send e-mail when the "Set IC Transaction Status" is used. The challenge is to get the comment and other information from the ICMatchStatus table and to pick up the e-mail addresses to send to. So, I want to check for the entity in the ICMatchStatus table and all users with access to that entity should then receive the e-mail notification. I want to use the e-mail on the users to create the e-mail list for sending e-mails.
It's more or less the same as with the journals. If a journal is for example created, then an e-mail is sent to the users that can approve the journal. The e-mail will then also contain the journal they are suppose to approve and/or post. This I have, but the ICTransactionStatus is a bit
04-06-2022 10:50 AM
This might need some collaboration as I've no clue what you are talking about ;).
So when you use BREventOperationType.DataQuality.ICTransStatus.AddICTransactionStatus or the Finalize one, can you check what the arg inputs are? Some of those inputs could be lead you to create the where clause that help in getting the data from the ICMatchStatus table.
04-06-2022 11:23 AM
My client wants an e-mail notification when IC Transactions Status are used. See print screen.
See print screen below to see the BR I am testing with
This is working with a hard-coded e-mail address and content for the e-mail, but I want to have the information from the comment and be able to send it to the correct users. Tom created something for Journals that I have been using, so I am looking for something similar to that, but for this "Set IC Transaction Status".
04-06-2022 12:49 PM - edited 04-06-2022 03:32 PM
Totally untested. But I think this will do it.
#Region "SetICStatus Helpers" Private Sub XFR_FinalizeAddICTransactionStatus(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal args As DataQualityEventHandlerArgs) Try Dim objICMatchStatusInfo As ICMatchStatusInfo = DirectCast(args.Inputs(0), ICMatchStatusInfo) If objICMatchStatusInfo.TransactionState = 40 'Finalized Dim intEntityID As Integer = objICMatchStatusInfo.ICMatchTransaction.EntityId Dim entMbr As Member =BRApi.Finance.Members.ReadMemberNoCache(si, DimTypeId.Entity, intEntityID) Dim readWriteGroup As GroupInfo = BRApi.Security.Admin.GetGroupInfoEx(si, entMbr.ReadWriteDataGroupUniqueID).GroupInfo Dim readWriteGroup2 As GroupInfo = BRApi.Security.Admin.GetGroupInfoEx(si, entMbr.ReadWriteDataGroupUniqueID2).GroupInfo Dim usrList As New List(Of UserInfo) Me.GetUsersInGroup(si, readWriteGroup.Group.UniqueID, usrList) Me.GetUsersInGroup(si, readWriteGroup2.Group.UniqueID, usrList) For Each userSummary As UserInfo In usrList.Distinct() 'Send the email Me.CreateMessageAndSendMail(si, objICMatchStatusInfo, userSummary) Next End If Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Sub Private Sub GetUsersInGroup(ByVal si As SessionInfo, ByVal groupID As Guid, ByRef usrList As list(Of UserInfo)) Try Using dbFwConn As DbConnInfoFW = BRApi.Database.CreateFrameworkDbConnInfo(si) Dim sql As New Text.StringBuilder sql.AppendLine("WITH ChildUser as ( SELECT ChildKey,ChildIsAUser FROM SecGroupChild WHERE ChildIsAUser = 0 UNION ALL SELECT b.ChildKey,b.ChildIsAUser FROM SecGroupChild as b INNER JOIN ChildUser as a on a.ChildKey= b.GroupKey ) Select distinct ChildKey from ( SELECT distinct ChildKey FROM ChildUser where ChildIsAUser <> 0 UNION ALL SELECT distinct ChildKey FROM SecGroupChild WHERE ChildIsAUser = 1)p") Dim userDT As DataTable = BRApi.Database.ExecuteSqlUsingReader(dbFwConn, sql.ToString, True) If userDT.Rows.Count > 0 Then For Each userDR As DataRow In userDT.rows usrList.Add(BRApi.Security.Admin.GetUser(si, DirectCast(userDR("ChildKey"),Guid))) Next End If End Using Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Sub Private Sub CreateMessageAndSendMail(ByVal si As SessionInfo, objICMatchStatus As ICMatchStatusInfo, userSummary As UserInfo) '------------------------------------------------------------------------------------------------------------ 'Reference Code: CreateMessageAndSendMail ' 'Description: Create the email message body and execute the "Send Mail" function. ' '------------------------------------------------------------------------------------------------------------ Try Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si) Dim emailInfo As New Text.StringBuilder emailInfo.AppendLine("To: " & userSummary.User.Email) 'Create the message title Dim messageTitle As New Text.StringBuilder messageTitle.Append("IC Match for [") messageTitle.Append(objICMatchStatus.ICMatchTransaction.EntityId) messageTitle.Append(", ") messageTitle.Append(objICMatchStatus.ICMatchTransaction.PartnerId) messageTitle.Append("] is ready for you to review.") messageTitle.AppendLine("") 'Create the message body Dim messageBody As New Text.StringBuilder messageBody.AppendLine("IC Match Information:") messageBody.AppendLine("User Name........... " & objICMatchStatus.UserName) messageBody.AppendLine("Time Completed...... " & objICMatchStatus.TimeStamp) messageBody.AppendLine("Comments............ " & objICMatchStatus.Comments) messageBody.AppendLine("") 'Test the email by writing a log message ErrorHandler.LogMessage(si, emailInfo.ToString & vbcrlf & messageTitle.ToString & vbcrlf & messageBody.ToString) 'Send an email message 'Me.SendMail(si, userSummary.User.Email, messageTitle.ToString, messageBody.ToString) End Using Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Sub #End Region
04-06-2022 12:52 PM
Also getting the users from a child group using the SQL CTE was tested using a small set. It is a recursive query and gets you all users from child groups as well.
04-06-2022 03:35 PM
This is what I am looking for. It's not working as is without some changes. The send e-mail syntax is the old one, so I replaced it with this, BRApi.Utilities.SendMail(si, "OneStreamEmail", distributionList, subject, messageBody.ToString, fileAttachmentPaths). It seems to be some issues with the e-mail subject/ title, so I just changed it to one line with text combined with system information, not using the append function. I also removed the If-statement to only run for "Finalized" status since I want a notification for all status updates.
Thanks a lot for this, will save me for a lot work trying to write all this from scratch,
04-06-2022 04:03 PM
You can get the member name using the ids for the title. I was lazy 🙂 I used the existing one in GolfStream for the emailing part.
04-06-2022 04:29 PM
I saw that, so I will update it to look up the name.
I saw your reply now regarding Sql and child groups. Is the current file not giving us all users with access to the active entity (I did not look much at the user part)?
04-06-2022 04:39 PM
I'm getting the entityid, then go and look up the ReadWriteGroup and ReadWriteGroup2 of the entity. Use those two groups and get all the users in it or the users in the child groups. It is a recursive query that gets all users from all the groups. I tested it for a small set only. I've not used this anywhere else except to write it up when I saw your request.
04-07-2022 01:38 AM
I will need to test it properly at the client's environment, but it looks good so far. I noticed one more thing. The intention is to send an e-mail to the partner when an entity is updating the Set IC Transaction status, so I changed this line from EntityId to PartnerId, "Dim intEntityID As Integer = objICMatchStatusInfo.ICMatchTransaction.PartnerId".
Again, thanks a lot for your effort.