In this blog i want to highlight some ways you can track who the current user logged into SAP Business One is. I will show you how to do this via FMS, the UI API (2 ways) and finally by using the SP_TransactionNotification stored procedure.
1. Via Formatted Search
If your business process requires you to display the current user, for example in a field on a SAP Business One form, then a FMS is a good way to achieve this.
You can write a simple query and assign it to a particular field and it will return the current logged in user for you:
SELECT T0.U_NAME FROM OUSR T0 WHERE INTERNAL_K = $[USER]
2. Reading the value from Main Module Screen via UI API
Via the UI you can read the value from the top of the Modules Main Menu.
You will need to read the caption value from the static text which has a UID of 8 on Form 169.
The code you need to use is and you can include it in the part of your code that most fits your business requirement:
Dim oForm As SAPbouiCOM.Form
Dim oStatic As SAPbouiCOM.StaticText
Dim sLoggedInUser As String
oForm = SBO_Application.Forms.GetForm(169, 0)
oStatic = oForm.Items.Item("8").Specific
sLoggedInUser = oStatic.Caption
MsgBox("The current logged in User is " & sLoggedInUser)
3. Track user logins/logouts via UI API Events
To do this we first need to create a user defined table in SAP Business One - in my example it's called USER_DATA. We also define 3 user defined fields (UDFs).
- UserCode records the current SAP Business One User
- LoginTimeDetails records the date and time the user logged in
- Direction records if the user logged in or logged out
In the code we will catch the application Company Changed event and then write the usercode, time and direction into the user defined table.
If EventType = SAPbouiCOM.BoAppEventTypes.aet_CompanyChanged Then
Try
oRS = oCompany.GetBusinessObject
(SAPbobsCOM.BoObjectTypes.BoRecordset)
oUserName = SBO_Application.Company.UserName
oDate = DateTime.Now
oRS.DoQuery("Select Max(Code) from [@USER_DATA]") oCurrentLogCode = oRS.Fields.Item(0).Value
oCode = oCurrentLogCode
oName = oCurrentLogCode
If oCurrentLogCode = "" Then
oCode = 1
oName = 1
Else
oCode = oCode + 1
oName = oName + 1
End If
oRS.DoQuery("INSERT INTO [@USER_DATA] (Code, Name,
U_UserCode, U_LoginTimes, U_Direction) VALUES (" & oCode & "," &
oName & ",'" & oCurrentUser & "','" & oDate & "','Logged Out')")
oRS.DoQuery("INSERT INTO [@USER_DATA] (Code, Name,
U_UserCode, U_LoginTimes, U_Direction) VALUES (" & oCode + 1 &
"," & oName + 1 & ",'" & oUserName & "','" & oDate & "','Logged In')") Catch ex As Exception MsgBox(ex.Message.ToString())
End Try
oCurrentUser = oUserName
End If
4. Via the SP_TransactionNotification
If not already done create the same User Defined Table as described above in 3 or simply add the additonal user defined fields to the existing table. The UDFs are:
- UserCode records the current SAP Business One User
- LoginDate records the date the user logged in
- LoginTime records the time the user logged in
We use the same principle in this SP_TransactionNotification as we used via the UI code above but we will record Date and Time in two different UDFs and we will also only log a new entry if Login Time is null or its more that 60 seconds since the same user logged in.
DECLARE @usercode NVARCHAR(8)
DECLARE @CurrentLoginDate DATETIMEDECLARE @CurrentLoginTime NVARCHAR(8)
DECLARE @LastLoginTime NVARCHAR (8)
DECLARE @Code NVARCHAR (8)
DECLARE @Name NVARCHAR (30)
IF @object_type = '12' and @transaction_type = 'U'
BEGIN
SET @usercode = (SELECT user_code FROM [OUSR] WHERE INTERNAL_K = @list_of_cols_val_tab_del) SET @CurrentLoginDate = GETDATE()
SET @CurrentLoginTime = CONVERT(VARCHAR(8), GETDATE(), 108)
SET @LastLoginTime = (SELECT MAX(U_LoginTime) FROM [@USER_DATA] WHERE U_UserCode = @usercode)
IF (Select Max(Code) from [@USER_DATA]) IS NULL
BEGIN
SET @Code = 1SET @Name = 1END
ELSE
BEGIN
SET @Code = (Select Max(Code) from [@USER_DATA]) + 1SET @Name = (Select Max(Name) from [@USER_DATA]) + 1
END
IF DATEDIFF(s, @LastLoginTime, @CurrentLoginTime) > 60 OR @LastLoginTime IS NULL
BEGIN INSERT INTO [@USER_DATA] (Code, Name, U_UserCode, U_LoginDate, U_LoginTime) VALUES (@Code, @Name, @usercode, @CurrentLoginDate, @CurrentLoginTime)
END
END
Please note in this example i have directly updated via SQL Update stataements a User defined Table. This is permitted as this is not a UDO User defined table.