User Defined Query and Alerts

[Former Member]

Hello guys, I need to set up an alert that happens whenever a Sales Order or Purchase Order is added. I am happy with the alerts procedure but I cannot set up a query.

Can anyone please help me set up queries that simply mean 'when SO added' and 'when PO added'?

Many thanks

[Former Member · Accepted solution]

From the SAP Main Menu goto Reports --> Query Generator. From here you can use the SAP Query builder to create whatever query you are trying to make. Then follow the steps provided by Jimmy to assign an alert to that query. Sometimes I find it easier to use a program external of SAP to write the query and then copy/paste it into the query generator.

[Former Member · Accepted solution]

i have successfully created this query, thank you so much to Cameron and Jimmy.

My next step is to try and implement the code/query for amending the Item Availability Master Data box.......

[zal_parchem2]

DANG - made a mistake above -

<b>Do NOT put a comma after the line of:

T0.DocDate AS 'Post Date'</b>

(there should not be a comma on the last field selection or you will get an error in SQL. hahaha - shows how one can make a mistake when retyping SQL - I even did it myself!!!!)

[zal_parchem2]

Good answer Cameron and Jimmy...a pretty nifty trick that a few folks know about...

Just one more tip for your query Groovy - you might want to include the field of T0.UserSign in the query and order the query results with ORDER BY T0.DocNum DESC so the latest number comes up on the top of the list...

We set up alerts with cash application functions and it was always easier for the person receiving the alert to have that info on top instead of scrolling down. He just checked the content by using the golden "drill down" arrown next to each entry and then went on his business...

[Former Member]

Ok as my knowledge of this code is minimal, would you clarify?

do i just make sure i include T0.UserSign ORDER BY T0.DocNum DESC somewhere in the existing code?

thanks

[zal_parchem2]

well Groovy - trying to post the answer but the system says it will not post!

[zal_parchem2]

OK Groovy - really glad you are following up on this. Many financial folks like to have this kind of review (especially when they first get onto SAP B1) since it gives them a comfort level of being able to see what is done.

Good time for you to take a look at the Query Generator and learn a bit of a new skill (PATH: Reports > Query Generator)! Once you get the hang of it, you can be a real whiz!

Here is the exact SQL used in their Alert Messaging System. Type this in exactly as you see it below in the Demo or Test System just to try it out and get a hang of it...

<b>SELECT

T0.DocNum AS 'Doc Num',

T0.UserSign AS 'User ID',

T0.DocStatus AS 'Doc Status',

T0.DocDate AS 'Post Date',

FROM ORDR T0

WHERE T0.DocStatus = 'O'

ORDER BY T0.DocNum DESC</b>

A few comments - the DocNum and UserSign fileds display the famous golden 'drill down' arrows which allow the person to take a look at the document and whoever process the transaction.

An added bonus is that you can use this exact SQL to query many different tables and use them as their own individual alerts (for example, just substitute ORIN for ORDR and the system will display the AR Credit Memo - all the field names are the same!).

A WORD OF CAUTION: Remember this SQL is picking up orders with the status of 'O(pen)'. It is very important how often you run this Alert Message because it might not report all orders created depending upon the time between order creation and SQL running to generate the alert. Consider the following scenario: A firm has the alert come up weekly on Friday. Monday a person creates an order 123, the warehouse ships on Tuesday. Home office bills the order and closes it on Thursday. When the alert message is generatedon FRIDAY, 123 will NOT be in the list since it now has a status of 'C(losed)'. That scenario requires different 'WHERE' selection criteria dealing with dates...

Good luck - Zal

[Former Member]

wow Zal, that's the kind of answer i like!

i will have a go at this later this afternoon, and see if i can use your comments to get an understanding of SQL

Thanks again

[zal_parchem2]

you are more than welcome Groovy - that is what this forum is all about: help and be helped...share and share alike!

Good luck and take care - Zal

[Former Member]

Is there are a standard query in SBO also for Dunning?

Thanks in advance,

Chief

[former_member186095]

Hi,

The steps are :

1. Creating query for the alerts, e.g. SELECT T0.[DocNum], T0.[DocType], T0.[DocStatus], T0.[DocDate] FROM ORDR T0 WHERE T0.[DocStatus] ='o'

2. Create new alert in the alert management (AM)under administration module --> ctrl+A --> click open saved query to assign the alert in the AM. Thick the Int column especially for users that have to receive the alerts. There are 4 columns that is used as tools to notify or receive alert.

Set frequency and select priority.Other can be skipped.

3. Sign out from SAP B1 and then login again. To make sure the alert is working or not, just create a SO, set time every 3 minutes and then just wait. The alert message will popped up.

The query above is also similar with query for purchase order or other AP documents.

let me know if you can't find the solution yet

Rgds,

[Former Member]

that's very good of you, Jimmy, but how do i get to the page where I create the query?

Is it query generator? query wizard? query manager?

I'm wondering if this is a user access thing because i see that the CREATE REPORT button is 'greyed out' (inactive) in the Query Manager window.