Skip to Content

Alerts for PO entry

Hi support

Can I please get some help writing query for an Alert to be triggered upon purchase order entry.

For every Purchase Order added to system an Alert needs to be triggered against assigned user/s.

I appreciate your help.

regards

Kelly

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

9 Answers

  • Posted on Jun 03, 2008 at 02:01 AM

    Kelly,

    Do you want that current login user will have an alert to inform the PO entry they made or some certain user want to know the PO entry that created by other certain users ?

    try to use this alert:

    SELECT T0.[DocNum], T0.[CardCode], 
    T0.[DocDate], T0.[DocDueDate], T1.[U_NAME] FROM 
    OPOR T0  INNER JOIN OUSR T1 ON 
    T0.UserSign = T1.userid
    

    you could modify it directly in the query.

    Assigning it to the alert management --> click open saved query.

    Assign the user that have to receive the alert if you need it.

    Rgds,

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 05, 2008 at 03:50 AM

    Hi Kelly,

    As you know, alerts can be fired only on specific

    frequency of time, not on creation of the documents, you may have a try by creating alert with query below, and set the Alert frequency as 1 min.This query is for the Purchase Order created in last 1 min:

    select T1.DocNum, T1.CardCode
    from POR1 T0,
         OPOR T1
    where T0.DocEntry  = T1.docentry
    and   T1.UpdateDate > getdate() -1
    and   T1.doctime >  cast(datepart(hh,getdate()) as varchar(2)) + cast(datepart(mi,getdate()) as varchar(2)) - 1
    

    Or you may use Approval Procedure for this purpose.

    Regards,

    Canna Mu

    SAP Business One Forums Team

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 06, 2008 at 01:07 AM

    thanks for feedback

    basically i need query to pick up all PO added in system by any user....I will then set to an Alert and define within alert the user/s to be notified of PO added. Please advise query to use for this result.

    thks

    Kelly

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 06, 2008 at 01:30 AM

    Yes am testing 2nd query suggestion now....however no alert is coming up. I have set up Alert as every minute as suggested. I have keyed in several POs and nothing is happening??

    pls advise.

    Kelly

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 06, 2008 at 02:03 AM

    Kelly,

    Please confirm the query you are using. If you could paste the same it would help.

    Also, let me know how you have defined the Alert.

    Have you selected the Query, selected the Users to whom the Alert is to be send by checking the box Int against the user. Checked Active?

    Please let me know

    Suda

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 06, 2008 at 02:16 AM

    Hi Suda

    I am using query below:

    select T1.DocNum, T1.CardCode

    from POR1 T0,

    OPOR T1

    where T0.DocEntry = T1.docentry

    and T1.UpdateDate > getdate() -1

    and T1.doctime > cast(datepart(hh,getdate()) as varchar(2)) + cast(datepart(mi,getdate()) as varchar(2)) - 1

    It is now working...can I get 1 line to display per PO not multi if more than 1 item on order...is this possible. If not, then this will do.

    thks for your help

    Kelly

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Kelly,

      Glad to know the query provided works for you.

      As you can see from the query, the field after Select is from T1 table, which is OPOR, header of Purchase Order, so you should only get one line for per order. If you have different result, please clarify by pasting query result here.

      Regards,

      Canna Mu

      SAP Business One Forums Team

  • Posted on Jun 06, 2008 at 02:29 AM

    HI Suda

    using this query the result is multi lines and displays the sames amount of lines per PO for items consisting on PO

    eg if PO order 4 items then Alert display this PO 4 times

    pls advise if possiblt on display 1 line per PO

    Kelly

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Using the DISTINCT keyword would work here

      SELECT DISTINCT T1.DocNum, T1.CardCode

      from POR1 T0,

      OPOR T1

      where T0.DocEntry = T1.docentry

      and T1.UpdateDate > getdate() -1

      and T1.doctime > cast(datepart(hh,getdate()) as varchar(2)) + cast(datepart(mi,getdate()) as varchar(2)) - 1

      Suda

  • Posted on Jun 06, 2008 at 03:22 AM

    Hi Suda

    in addition to prior post:

    "using this query the result is multi lines and displays the sames amount of lines per PO for items consisting on PO

    eg if PO order 4 items then Alert display this PO 4 times

    pls advise if possiblt on display 1 line per PO"

    I have found the alert displays PO already alerted on, is there a way to only display new result for each alert given...for example:

    if 1st Alert for teh day defined PO123 & PO124

    then the 2nd Alert triggered should not display these again

    pls advise

    Kelly

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      The Alert is the result of the query. If the Query results pick the PO's then the PO's will be displayed

      the alert system does not provide for an option to elimimate PO's that have been already displayed on Alert.

      If you definitely want the PO's not to be displayed again then you need to add a user field for Alerted

      this will be more like a Yes / No field. If the Po is alerted then then User can mark this field as Y and in the Query we should filter the results to pick only rows from PO's where Alerted = 'N'

      Hope this makes sense

      Suda

  • Posted on Jun 06, 2008 at 03:39 AM

    thks Suda

    Ok, so are you telling me the same list of POs will be listed in Alert for ever and a day if they are open POs?

    Can you please advise process of your suggestion on user field Yes/No.

    thks

    Kelly

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Add a user field in the Marketing document Header

      Name it Alerted

      define valid values Y - Yes and N - No

      Set default to N - No

      Now in your Query add to the Where condition

      AND T1.U_Alerted = 'N'

      When the user receives the PO in the Alert they have to change the Flag in this field to 'Y' - Yes

      So next time the Alert looks at the PO's it will not pick the PO's already alerted due to the 'Y' flag

      Suda

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.