Scenario: Looking to receive an daily email of any ACH account changes on Vendors in the past day.

Example Solution:

  1. Open the VA Inquiries form
  2. Create a new Record by entering CTS_VendorInfoChangeNotifier in the Query Name Field
  3. Enter Vendor ACH Information Change Notifier in the Title Field
  4. Enter Created on 9/5/2023 by CTS. This is the source code for the notification emails to be sent when there are AP vendor ACH information changes 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_APVendorInformationChanges in the Job Name & Description Fields.
  11. Set Query type to 1-VA Inquiry
  12. Select CTS_VendorInfoChangeNotifier 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
    Co,
    Vendor,
    FieldName,
    CASE
        WHEN FieldName = 'BankAcct' THEN '******' + RIGHT(RTRIM(OldValue), 4)
        ELSE OldValue
    END AS OldValue,
    CASE
        WHEN FieldName = 'BankAcct' THEN '******' + RIGHT(RTRIM(NewValue), 4)
        ELSE NewValue
    END AS NewValue,
    ChangedBy,
    ChangedOn
FROM (
    SELECT
        Co,
        SUBSTRING(KeyString, PATINDEX('%:%', KeyString) + 2, (PATINDEX('%Vendor:%', KeyString) - PATINDEX('%:%', KeyString)) - 3) AS VendorGroup,
        SUBSTRING(KeyString, PATINDEX('%Vendor:%', KeyString) + 8, LEN(KeyString) - PATINDEX('%Vendor:%', KeyString)) AS Vendor,
        FieldName,
        OldValue,
        NewValue,
        UserName AS ChangedBy,
        DateTime AS ChangedOn
    FROM bHQMA
    WHERE TableName = 'bAPVM'
        AND DateTime >= DATEADD(d, -1, GETDATE())
        AND FieldName IS NOT NULL
        AND FieldName IN ('BankAcct','RoutingId','AcctType')
) AS VendorChanges