Getting Alert after Adding a Document in SAP Business One

[kenan_jaddeny]

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