Query for due date

[former_member418241]

Hi All,

How to write a query for an alert to be generated 5 days before the document due date?

thanks

SV Reddy

[Former Member · Accepted solution]

Write a query based approval like this

SELECT 1 where DATEDIFF(day,getDate(),opor.duedate)=4

[former_member418241]

Hi All,

Thanks for your replies, Now I understood it correctly.

Points assigned

SV Reddy

[former_member583013]

SV Reddy,

When you say, DueDate...which document are you referring to ?

This is the general format for the SQL which will display the DocNum, DocDueDate and Days Due in the Alert window

SELECT T0.DocNUM, T0.DocDueDate, DATEDIFF(day, GetDate(), T0.DocDueDate) [Days to Due Date\]

FROM [dbo\].[ORDR\] T0

WHERE DATEDIFF(day, GetDate(), T0.DocDueDate) <= 5

NOTE: In the query I have used the table ORDR as a sample to represent Sales Order document. You may replace this with the appropriate document table to suit your requirement.

Best Wishes

Suda

[former_member418241]

Hi Everybody for your valuable suggestions.

When we execute this query in normal mode by going to user query menu, it returns some records that can be displayed on to the screen in a separate window. When you attach this query to an Alert, how the Alert will trigger ?

Is it when the query returns more than 1 record then the name of the Alert will display in the inbox of the user ?

OR

Is it the result of the query displayed in the window?

What will happen when this query returns 0 records ? means is that true the alert wont be triggerred ?

What is the base criteria that an alert take in to consideration when to send a popup to the specified users?

Please reply

Thanks

[former_member583013]

Reddy,

It is very straight forward...If the Query returns results (even 1 row)..then the Alert message will show up on the User's Messages/Alerts Overview window.

Subject, Date, ..will be displayed. Click on the row will display the query results in the window below

[Former Member]

If you really want this alert, you have to limit it to selected BP or by certain scope. Otherwise, you will end up getting alert every hours or every couple of minutes depending on your alert frequency. I believe you will have more documents due than you can imagine. Once the condition triggered, the alert would be continusly for the whole day.

Daily report to show any documents due in 5 days might be more practical.

Thanks,

Gordon

[Former Member]

You could try:

SELECT *

FROM T0.OINV

WHERE T0.DueDate = (CONVERT(CHAR(8),GETDATE() + 2,10))

Sample Query - Items to deliver within 5 days

SELECT T0.DocNum, T0.CardCode, T0.CardName, T1.ItemCode, T1.Dscription, T1.Quantity, T1.ShipDate FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.ShipDate = (CONVERT(CHAR(8),GETDATE() + 5,10)) AND T1.OpenQty <> 0