Scenario: Looking to receive an weekly email with the number of outstanding invoices, age, and dollar amount by Reviewer and Month. Modify SQL as needed if you need multi-company support, etc.

Example Solution:

  1. Open the VA Inquiries form
  2. Create a new Record by entering CTS_APUISummary in the Query Name Field
  3. Enter Unapproved Invoice Summary Query for Weekly Email in the Title Field
  4. Enter Unapproved Invoice Summary Query for Weekly Email created by CTS-Kevin Halme on 2022.08.19 in the Description Field
  5. Select SQL as Type
  6. Check/Uncheck Assignable in Work Center as desired (Note Vista bug that doesn’t default this field correctly)
  7. Enter SQL Query #1 (found at the bottom) in the Select field.
  8. Save and close the form.
  9. Open WF Notifier Job Manager
  10. Enter CTS_APUISummary in the Job Name & Description Fields.
  11. Set Query type to 1-VA Inquiry
  12. Select CTS_APUISummary as the query.
  13. Set the desired schedule
  14. On the email tab, enter the desired recipient, subject, and header.
  15. Change Format to 1-Rich Text
  16. In the Body field, you can insert table by click Table button and add all columns to the table, format table as desired.
  17. Check the Consolidate Notifications field at the bottom of the form.
  18. On the Consolidations Group tab, add [To] to the Grouping Criteria.
  19. On the Email tab, press Test Email button to test the notifier to make sure it functions as expected.

SQL Query #1

select DateName(month,APUI.UIMth) + ' ' + CAST(YEAR(APUI.UIMth) AS VARCHAR(4)) AS Month , HQRV.Name as Reviewer,count(DISTINCT (convert(varchar(20),APUI.UIMth,101)+cast(APUI.UISeq as varchar(8)))) as [# Invoices]
,FORMAT(sum(isnull(APUL.GrossAmt,0)+isnull(TaxAmt,0)),'C') as Amount,CAST(AVG(CAST(DATEDIFF(dd,Cast(APUR.DateAssigned as Date),GETDATE()) as decimal(5,1))) as decimal(5,1)) as [Average Days]
FROM APUI 
INNER JOIN APUL ON APUI.APCo=APUL.APCo AND APUI.UIMth=APUL.UIMth AND APUI.UISeq=APUL.UISeq 
INNER JOIN APUR ON APUL.APCo=APUR.APCo AND APUL.Line=APUR.Line AND APUL.UIMth=APUR.UIMth AND APUL.UISeq=APUR.UISeq 
LEFT OUTER JOIN HQRV ON APUR.Reviewer=HQRV.Reviewer
WHERE APUI.APCo=1 AND HQRV.Reviewer <> 'AP'
AND APUR.ApprvdYN<>'Y' AND APUR.Line<>-1
GROUP BY APUI.UIMth, HQRV.Name
ORDER BY APUI.UIMth, HQRV.Name