1 Reply Latest reply on Oct 19, 2018 12:23 PM by

    Query/Alert for AP invoice based on BP Group

      Hi There,

       

      I'm trying to figure out how to build a query to use with an alert to notify me when an AP invoice is created on a vendor account set to a certain BP Group. The problem we're working on is our company credit card payments. We are entering all credit card receipts as AP invoices, and I'm looking for an efficient way to notify the correct person that an invoice has been entered so that the payment can be applied. We're trying to alleviate some of the steps off our AP clerk's desk, and to help insure that the liability is not showing in the wrong liability account.

       

      Any tips, suggestions... would be greatly appreciated.

       

      Thanks!

      Ashley

      • Reply
        • 1. Re: Query/Alert for AP invoice based on BP Group

          Hello Ashley.  You did not mention how often you want the Alert to run, but I imagine it is quite often.

           

          You can set up the SQL WHERE clause to indicate whether the AP Invoice has been processed or not.  The SQL can be coded so only those AP Invoices not processed show on the SQL Alert  - you can run it every hour on the Alerts Management Windowe under the Administration Modul.  The SQL would have a WHERE clause with:

           

          WHERE T0.DocStatus = 'O' AND  T0.Canceled = 'N'

           

          Of course, you would have an "AND" in the where clause such as

           

          AND TX.GroupCode = XXX (XXX =  GroupCode on OCRG based on the Vendor CardCode in the AP Invoice)

           

          Other ideas - Your WHERE clause in your SQL can be done with some Date and Hour Differences. 

           

          WHERE DATEDIFF (DD, T0.DocDate, GETDATE()) < 1 for all done today (maybe a "3" so you cover weekends)

           

          WHERE DATEDIFF (HH, T0.DocDate, GETDATE()) < 1 for all done in the past hour

           

          Hope that helps!!!