Alerts

[Former Member]

Hi Experts,

I need a alert like, if the sales order or any other document is not posting more than 5 days, alert should come in sap screen and as well as mail. wat is the query? i have to write.

thanks in advance.

[Former Member · Accepted solution]

Hi Anand...

Use the following query to Catch the Doc. Which is not posted for more than 5 days...

SELECT T0.[DocNum], T0.[ObjType], T0.[CardCode], T0.[DocDate] FROM ODRF T0 WHERE DAY(GETDATE() - T0.[DocDate]) >= 5

[Former Member · Accepted solution]

Hi, Manikandan,

Ur query is working properly, But i didnt get the conclusion from this query result

because the document no. link is not showing why it is?

thanks.

[Former Member · Accepted solution]

Hi Anand..

In Query output tou wont get any link button on doc. num..... but It will be visible in Alert Window... Try This... I am Getting Exact output and i can able to navigate to the particular doc. from alert window by clicking the Orange arrow(Link Button)

[Former Member · Accepted solution]

Is it working?

[Former Member · Accepted solution]

yes thank u so much Mr.Manikandan.

[Former Member]

hi,

Try to Use this Query in Query Generater dont put this Query to MSSQL Sever Management Studio.

Declare @a Datetime
Set @a = (CONVERT(nvarchar(30), GETDATE(),101))

SELECT  Distinct 'True' FROM ORDR T0 WHERE T0.DocDate != (@a-5) or T0.DocDate != (@a-4) or 
T0.DocDate != (@a-3) or T0.DocDate != (@a-2) or T0.DocDate != (@a-1)

Regards,

Madhan.

Edited by: Madhan Babu C on Jul 31, 2009 1:34 PM

[Former Member]

Hi Madhan...

Your Query giving only true or false value, and also your thing is giving only for sales order....

But he is asking for all doc. which has doc dates deviates from 5 days....

[Former Member]

Hi Anand...

Use the following query to Catch the Doc. Which is not posted for more than 5 days...

SELECT T0.[DocNum], T0.[ObjType], T0.[CardCode], T0.[DocDate] FROM ODRF T0 WHERE DAY(GETDATE() - T0.[DocDate]) >= 5

[Former Member]

Hi Anand....

It is not possible by standard product, we have to write query for this condition and call that query in Alert Management Window...

[Former Member]

hi Anand,

Try this Alert Query,

It will come Alert The Sales Order Document is not posting more than 5 days, Alert should come in SAP Screen

and as well as mail.

Declare @a Datetime
Set @a = (CONVERT(nvarchar(30), GETDATE(),101))

SELECT  Distinct 'True' FROM ORDR T0 WHERE T0.DocDate <> (@a-5) or T0.DocDate <> (@a-4) or T0.DocDate <> (@a-3) 
or T0.DocDate <> (@a-2) or T0.DocDate <> (@a-1)

Save as Query Manager,and

->> Goto Administration.

->> Alert Management, and Open Alert Management Window.

Create New Alert for Sales Order Document is not posting more than 5 days,

->> Create Alert Name and Click Open Save Query Button then Open Query Manager Window and your Saved Query.

->> Click Active Button.

->> Check Your Praticular Users for Alert.

->> Set Time Frequency. ex: Every 1 Mintues, Hour, Days, Weeks, Month.

->> Finally Click Add and Save the Alert.

Regards,

Madhan.

[Former Member]

Hi,

Showing this error,

1 ).[Microsoft][SQL Native client](SQL Server)u2019Must declare the scalar u2018@au2019 2. ).[Microsoft][SQL Native client](SQL Server)

[Former Member]

Is my query is working?

[Former Member]

Hi Anand

Are you setting the Cancellation or Required By dates in the Sales Order. The system default is 30 days and this can be set under Administration > System Initialization > Document Settings > Per Document TAB and choose Sales Order. My suggestion would be a query that checks if Cancellation date and GetDate() are within a day or so of each other (for example).

The alternative is to use the create date and compare with GetDate().

Kind regards

Peter Juby

[kvbalakumar]

Dear Anand,

What's your exact need? Whether alert should come for the Draft Documents which were not posted more than 5 days or if a document is not copied to higher level document within 5 days or No postings had been done for an individual document for more than 5 days?

regards,

Bala

Edited by: Balakumar Viswa on Jul 31, 2009 10:18 AM

[Former Member]

Dear Bala,

I need, if suppose not posted the any document for more than 5days. i need alert for that,