Skip to Content

How to setup Alerts using a field DocTime

Was creating some alerts and came across some situations. First the query that will trigger the alert should contain some filters, which DocType, DocStatus, DocDate so far so good. The problem arose when I needed to be more specific and put a limit on time. In recent versions of SAP Business One is a field marketing documents called DocTime then made ​​the following combination.

SELECT T0.DocEntry, T0.DocStatus, T0.DocDate, T0.CardCode, T0.CardName, T0.ObjType

FROM OINV T0 WHERE T0.DocStatus = ‘O’ AND T0.ObjType = ’13’

AND  CONVERT(VARCHAR,A.CREATEDATE,103) = CONVERT(VARCHAR,GETDATE(), 103)

AND (CONVERT(varchar, datepart(hour, getdate())) + + CONVERT(varchar, datepart(minute, getdate())) T0.DocTime) <= 15

Before that it was need create a user field to record the time and then use in a comparison. Today it is possible to do this directly with field Native systems. And this comparison can be done directly by two ways, it will depend on whether you’re with SQL2008 or SQL2012.

SELECT T0.DocEntry, T0.DocStatus, T0.DocDate, T0.CardCode, T0.CardName, T0.ObjType

FROM OINV T0 WHERE T0.DocStatus = ‘O’ AND T0.ObjType = ’13’

AND  CONVERT(VARCHAR,A.CREATEDATE,103) = CONVERT(VARCHAR,GETDATE(), 103)

AND  CONVERT(CHAR,(T0.U_HOUR T0.U_REMINDER),108)>= CONVERT (CHAR,GETDATE(),108)

In SQL2012 to use SELECT FORMAT(SYSDATETIME(), ‘HHmm’)

In SQL2008 to use SELECT CONVERT(varchar, datepart(hour, getdate())) + + CONVERT(varchar, datepart(minute, getdate())

..compared with the DocTime

Comments and suggestions are welcome!

References:

http://www.sql-server-helper.com/sql-server-2012/format-function-vs-convert-function.aspx

http://blog.sqlauthority.com/2012/07/16/sql-server-2012-functions-format-and-concat-an-interesting-usage/

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/a0c16e74-974b-2d10-ec98-a2c00675b392?QuickLink=index&overridelayout=true&48056389120629

Regards,

Marcelo Silva Santos

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Kevin Vos

    Thanks Marcelo, good one.

    Let’s make sure that the Alert Frequency set in SAP is less then the minutes in your query, in this case 15 minutes.

    Another point, less important, is that your query does not take into consideration a 24h business day due to the Compare Date.

    For a business, let’s say a call center that works from Europe with  US customers placing invoices, you gonna have a problem for the 15 first minutes after midnight, every nights πŸ˜‰

    Regards,

    Frederic Bada

    (0) 

Leave a Reply