Skip to Content
Technical Articles
Author's profile photo KENAN JADDENE
KENAN JADDENE
August 20, 2019 2 minute read

Getting Alert after Adding a Document in SAP Business One

You want to get an alert, or have an approval procedure to be applied when a new marketing document is added to SAP Business One,
to get this, we’ll execute the following query as an example;

SELECT docnum, cardcode, doctime, createdate FROM OINV
WHERE createdate = getdate() AND 
doctime >= REPLACE(CONVERT(nvarchar(5),GetDate(),108),':', '')-2

The previous query what does it mean?

We’re choosing the Document Number, the Customer Card Number, the Document Added Time, the Document Creation Date from the table OINV (AR Invoices Table),

Where we have two conditions here;

  • The document creation date = the current date (in SQL Server the clause getdate() is used)

 

  • The document added time, it will be represented as 4 digits (1311, 1408, …)
  • in our query case here, we want to be notified after the document is being added to the system by 3 minutes,
  • according to that we should compare the current date with the current hour with the document added time (hours and minutes) then take out from it 2 minutes
  • to do so, first of all, we should transfer the current date to the current time with 4 digits according to that we’re using the CONVERT clause
  • in the convert clause to change the type from date to time (hours and minutes), we’ll say; convert to nvarchar(5) from date GateDate(), with the type of converation 108, to get the hours format (hours;minutes)
  • we used the nvarchar data type because there are no specific number will be stored in the date cells,
  • according to this code CONVERT(nvarchar(5),GetDate(),108) we’re getting the time of the document we’re being added as 13:11
  • now we need to replace the ‘:’ with nothing, to be able to compare it to the document it added time in the system, according to that we need to use the REPLACE caluse as follow: REPLACE(CONVERT(nvarchar(5),GetDate(),108),’:’, ”)
  • To get the invoices that being added to the system in the last 2 minutes, we’re saying that; if the document added time (hours;minutes) is greater than or equal to the current time before 2 minutes, then execute and give us the result.

 

The current time now is 10;46 a.m.

Code Result
SELECT GetDate() 2019-08-20 10:47:16.000
SELECT CONVERT(nvarchar(5),GetDate(),108) 10:48
SELECT CONVERT(nvarchar(5),GetDate(),109) Aug 2
SELECT CONVERT(nvarchar(5),GetDate(),101) 08/20
SELECT CONVERT(nvarchar(5),GetDate(),102) 2019.
SELECT CONVERT(nvarchar(5),GetDate(),103) 20/08
SELECT CONVERT(nvarchar(5),GetDate(),104) 20.08
SELECT CONVERT(nvarchar(7),GetDate(),108) 10:49:3
SELECT REPLACE(CONVERT(nvarchar(5),GetDate(),108),’:’, ”) 1049

About the NVARCHAR data type:

  • It is a variable-length data type
  • Used to store Unicode characters
  • Occupies 2 bytes of space for each character

If your column will store a fixed-length Unicode characters like French, Arabic and so on characters then go for NCHAR. If the data stored in a column is Unicode and can vary in length, then go for NVARCHAR.

 

How to know the data type of a column for SAP Business One?

From inside SSMS, in the Object Explorer, Database > Tables > Table > Columns > beside every column name the data type is written (nvarchar, datetime, char, int, numeric, …)

 

Hope this helps, Regards.

For more visit us https://glyceria.com

KENAN JADDENE

 

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Dick Mauritz
      Dick Mauritz

      Thanks for sharing this interesting article!

      Author's profile photo KENAN JADDENE
      KENAN JADDENE
      Blog Post Author

      You're welcome Dick, hope this helps. BR,

      Kenan

      Author's profile photo Janki Mishra
      Janki Mishra

      Great Article!

      Author's profile photo Evans Wanderi
      Evans Wanderi

      Thanks for this KENAN JADDENE. Do you have a HANA version for this?

      Author's profile photo Ahmed Bahram Maghdid
      Ahmed Bahram Maghdid

      Dear Evans Wanderi, this is the query alert for SAP Business One HANA version:

      SELECT a."DocNum", a."CardCode", a."DocTime", a."CreateDate"
FROM OPOR a
WHERE a."CreateDate" = CURRENT_DATE AND a."DocTime" = REPLACE(CAST(CURRENT_TIME AS nvarchar(5)), ':', '') - 2
ORDER BY a."CreateDate", a."DocTime" DESC;

      Thanks to KENAN JADDENE for sharing this great idea.

      Author's profile photo Thomas Brandler
      Thomas Brandler

      Is it possible to re-write this for the HANA version specifically for ANY unassigned document in project management?

      Our Project Managers have several projects and dont want to browse through all of the project several days a week just to see whether or not the Unassigned Document - Assign now? Window will pop up or not.

      We want to send them an alert in the system once their attention is needed.

      Author's profile photo PQ Alb
      PQ Alb

      as least on MSSQL you need to change it to

      createdate = CAST(GETDATE() AS DATE)

      -- you need to truncate the hhmmss from the time