Skip to Content

Alert Salespersons when stock is booked

Hi Support

Can I please get some help writing query for required Alert setup.

I need Alert to trigger relevant assigned Salespersons when stock is booked in.

Meaning... alert to notify user/s when stock is received into warehouse "01" from PO. Can be done via Goods Receipt and/or AP invoice process.

I appreciate your help.

regards

Kelly

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

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

    Kelly,

    Do you want good receipt PO (GRPO) or good receipt ?

    try to use this query if it is A/P invoice and good receipts (under inventory transactions) :

    SELECT T0.[Base_ref] as 'Doc No.', T0.[DocDate], 
    T0.[ItemCode], T0.[Warehouse] FROM 
    OINM T0 WHERE (T0.[TransType]  = '18' or 
     T0.[TransType] = '59') and  T0.[Warehouse]  = '01'
    

    if it is GRPO, just change '59' to be '20'.

    Rgds,

    Add a comment
    10|10000 characters needed characters exceeded

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

    Kelly,

    Writing a simple query like Jimmy has shown is the easy part but when you talk about alerts to a particular assigned Salesperson what you need to understand is Alerts unless chosen to go a particular user cannot automatically determine the SalesRep on the document.

    Determining the baseline logic of how the query is going to deliver the Alert is important.

    If you could explain your scenario in a little more detail with an example perhaps would benefit address the solutions

    Best wishes

    Suda

    Add a comment
    10|10000 characters needed characters exceeded

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

    hi

    Basically I need query to report on stock coming in to warehouse "01" via GRPO and AP Invoice process from base document PO.

    Alert to pick up this query and will be set to applicable user/s to alert them of rthe stock in warehouse that has arrived.

    I hope this helps.

    Please advise query to use.

    thks

    Kelly

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Kelly,

      Could you please answer the following. If you want to pick the quantities from GRPO and AP Invoice they will show duplicate quantities if they are accounted twice.

      Please confirm iyour Purchase Process steps. Is it PO > GRPO > AP Invoice

      Or PO > AP Inv

      If it is both then the query will have to be different.

      Also let me know what information you want to display on the ALERT

      Suda

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

    Hi Suda

    No I only require to pick up qty received into warehouse once. The process can be a combination ie sometimes from PO to AP Invoice and other times from PO to GRPO then AP Invoice.

    Pls advise back

    thanks heaps

    Kelly

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Ok.

      I am going to give you the base query which can be altered based on your information requirements

      SELECT DISTINCT T0.DOCNUM, T0.DOCDATE, T1.ITEMCODE, T1.QUANTITY

      FROM [DBO\].[OPDN\] INNER JOIN [DBO\].[PDN1\] T1 ON T1.DOCENTRY = T0.DOCENTRY

      WHERE T1.WHSCODE = '01' AND T0.DOCDATE = GETDATE()

      UNION ALL

      SELECT DISTINCT T0.DOCNUM, T0.DOCDATE, T1.ITEMCODE, T1.QUANTITY

      FROM [DBO\].[OPCH\] INNER JOIN [DBO\].[PCH1\] T1 ON T1.DOCENTRY = T0.DOCENTRY

      WHERE T1.WHSCODE = '01' AND T0.DOCDATE = GETDATE() AND T1.BASETYPE = 22

      The above query will display all GRPO's and all AP Invoices which have been added that day. In the where clause the condition T0.DOCDATE = GETDATE() filters this.

      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.