Document Approval Email Notifications in SAP Business One

[ogassem]

Creating an automated email notification system for document approval in SAP Business One using PowerShell can streamline your business processes and ensure that documents are approved promptly. In this tutorial, I'll guide you through the complete process, including setting up the database, creating a PowerShell script, and running it as a service.

Overview of the Process

We'll accomplish the following:

1. Set Up the User Defined Table (UDT) in SAP Business One: A table to log email notifications that have been sent.
2. Write a PowerShell Script: This script will run every 5 minutes, query the database for documents awaiting approval, and send email notifications.
3. Create a Windows Service: Automate the execution of the PowerShell script by creating a Windows service.
4. Testing: Ensure that everything works as expected.

Step 1: Set Up the User Defined Table in SAP Business One

To begin, you'll need to create a User Defined Table (UDT) in SAP Business One that will keep track of the emails sent. This will prevent duplicate notifications for the same document.

How to Create a User Defined Table

1. Log into SAP Business One.

2. Navigate to: Tools > Customization Tools > User-Defined Tables - Setup.

3. Create a New Table with the following details:

   • Table Name: @Z_APPR_EMAILS
   • Description: Approval Email Logs
   • Type: No Object

4. Add Fields to the Table:

   • U_email: Email address to which the notification was sent.
   • U_userid: User ID associated with the document.
   • U_internalNumber: Internal Document Number.
   • U_ObjType: Object Type of the document.
   • U_status: Status of the email (e.g., Sent, Failed).
   • U_sentDate: Date the email was sent.
   • U_sentTime: Time the email was sent.

Step 2: Write the PowerShell Script

We'll create a PowerShell script that connects to the SQL Server database, retrieves documents awaiting approval, and sends email notifications.

PowerShell Script: SendEmailNotifications.ps1

Here's the complete script:

# Main loop to run every 5 minutes
while ($true) {

# Define SQL Server connection parameters
$serverInstance = "<Database Server Name>"
$database = "<Database Name>"
$username = "<UserName>"
$password = "<Password>"
$query = @"
SELECT T0.[WddCode], T0.[DocEntry], T0.[ObjType], T0.[DocDate], T0.[CreateDate], T0.[CurrStep], T1.[UserID], T2.[U_NAME], T2.[E_Mail] as "Email"
FROM OWDD T0
INNER JOIN WDD1 T1 ON T0.[WddCode]= T1.[WddCode] AND T0.[CurrStep]=T1.[StepCode]
INNER JOIN OUSR T2 ON T1.[UserID] = T2.[USERID]
LEFT JOIN [dbo].[@Z_APPR_EMAILS] T3 ON T3.[U_userid]=T1.[UserID] AND T3.[U_internalNumber]=T0.[WddCode] AND T3.[U_ObjType]=T0.[ObjType]
WHERE T0.[Status] ='W' AND T0.[WddCode]=4 AND T3.[Code] IS NULL
"@

# Create SQL connection string
$connectionString = "Server=$serverInstance;Database=$database;User ID=$username;Password=$password;"

# Create SQL connection
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

# Create SQL command
$command = $connection.CreateCommand()
$command.CommandText = $query

# Open SQL connection
$connection.Open()

# Execute SQL query
$reader = $command.ExecuteReader()

# Process query results and send email notifications
if ($reader.HasRows) {
Write-Host "Query Results:"
while ($reader.Read()) {
$row = @{
WddCode = $reader["WddCode"]
DocEntry = $reader["DocEntry"]
ObjType = $reader["ObjType"]
DocDate = $reader["DocDate"]
CreateDate = $reader["CreateDate"]
CurrStep = $reader["CurrStep"]
UserID = $reader["UserID"]
U_NAME = $reader["U_NAME"]
Email = $reader["Email"]
}
Write-Output $row

# Send email notification
$to = $row.Email
$subject = "Document Approval Notification"
$body = "Hello $($row.U_NAME),<br><br>A document is waiting for your approval, "
$body += "please log in to the system to review and approve this document.<br><br>Regards,<br>SAP automatic notification"
Send-MailMessage -To $to -From "sap.notification@yourcompany.com" -Subject $subject -Body $body -BodyAsHtml -SmtpServer "smtp.yourcompany.com"

# Calculate the current time in hours and minutes combined into a single integer
$currentDateTime = Get-Date
$currentTime = $currentDateTime.ToString("HHmm")

# Calculate the current date in YYYYMMDD format
$currentDate = $currentDateTime.ToString("yyyyMMdd", [System.Globalization.CultureInfo]::InvariantCulture)

# Close the DataReader before executing the insert command
$reader.Close()

# Get the latest Code number
$maxCodeQuery = "SELECT MAX(CAST(Code AS INT)) FROM [dbo].[@Z_APPR_EMAILS]"
$maxCodeCommand = $connection.CreateCommand()
$maxCodeCommand.CommandText = $maxCodeQuery
$latestCode = $maxCodeCommand.ExecuteScalar()
$newCode = $latestCode + 1

# Insert line into the database
$insertQuery = "INSERT INTO [dbo].[@Z_APPR_EMAILS] (Code, Name, U_email, U_userid, U_internalNumber, U_ObjType, U_status, U_sentDate, U_sentTime) VALUES ('$newCode', '$newCode', '$to', '$($row.UserID)', '$($row.WddCode)', '$($row.ObjType)', 'Sent', $currentDate, $currentTime)"
$insertCommand = $connection.CreateCommand()
$insertCommand.CommandText = $insertQuery
$insertCommand.ExecuteNonQuery() | Out-Null
Write-Host "Line inserted into database for document $($row.DocEntry)." -ForegroundColor Cyan

# Reopen the DataReader after executing the insert command
$reader = $command.ExecuteReader()
}
} else {
Write-Host "No documents awaiting approval found." -ForegroundColor Yellow
}

# Close SQL reader and connection
$reader.Close()
$connection.Close()

# Sleep for 5 minutes before next iteration
Start-Sleep -Seconds 300
}

Explanation of the Script

1. Connection Parameters: Establish a connection with your SQL Server database by providing server instance, database name, username, and password.

2. SQL Query: Retrieve documents awaiting approval. This query checks if the document's status is 'W' and if no entry exists in the @Z_APPR_EMAILS table.

3. Sending Emails: For each document found, the script sends an email using Send-MailMessage.

4. Logging: After sending the email, the script logs the sent email details in the @Z_APPR_EMAILS table.

5. Continuous Execution: The script runs continuously in a loop with a 5-minute pause between iterations.

Step 3: Create a Windows Service

Creating a Windows service allows your script to run automatically without manual intervention. Follow these steps to create and manage the service.

PowerShell Service Script

Here's the script to set up your PowerShell script as a Windows service:

# Define service name and description
$serviceName = "EmailNotificationService"
$serviceDisplayName = "Email Notification Service"
$serviceDescription = "Service to send email notifications for document approval"

# Define script path
$scriptPath = "C:\scripts\SendEmailNotifications.ps1"

# Create service
New-Service -Name $serviceName -DisplayName $serviceDisplayName -Description $serviceDescription -BinaryPathName "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -File $scriptPath" -StartupType

Step 4: Testing

• Create a Test Document for Approval:

  • Log into SAP Business One and create a new document that requires approval.

• Check for Email Notification:

  • Wait a few minutes and check the email inbox of the user responsible for approving the document.
  • You should receive an email similar to the one below:

• Verify Log Entry in @Z_APPR_EMAILS Table: