Scenario: Looking to receive an email notice when Employee ACH (DD) Accounts Change

Example Solution:

  1. Open the WF Notifier Queries form
  2. Create a new Record by entering CTS_BankInfoChange in the Query Name Field
  3. Enter Reports any bank info changes in the Title Field
  4. Enter Created by CTS-Kevin Halme on 2023.05.29 to report employee bank info changes in the past 1 day in the Description Field
  5. Create the SQL View below and grant select to VCSUsers
  6. Enter SQL Query #1 (found at the bottom) in the Select field.
  7. Enter SQL Query #2 (found at the bottom) in the From where clause field
  8. Press the Test Query button to test the query.
  9. If the query tests ok, save and close the form.
  10. Open WF Notifier Job Manager
  11. Enter CTS_BankInfoChange in the Job Name & Description Fields.
  12. Set Query type to 0-WF Notifier Query
  13. Select CTS_BankInfoChange as the query.
  14. Set the desired schedule
  15. On the email tab, enter the desired recipient, subject, and header.
  16. Change the Format to 1-Rich Text
  17. Create a table with the Table Button and FOrmat as desired.
  18. Check the Consolidate Notifications field at the bottom of the form.
  19. On the Consolidations Group tab, add [PRCo] to the Grouping Criteria.
  20. On the Email tab, press test email button to test the notifier to make sure it functions as expected.

SQL View (install via SSMS)

 

CREATE VIEW [dbo].[CTS_PayRateChangeNotifier] AS

--GRANT SELECT ON [dbo].[CTS_PayRateChangeNotifier] TO [VCSUsers]

select PREH.PRCo , PREH.FullName as [Full Name] , PREH.Craft , PREH.Class , FORMAT(Cast(HQMA.OldValue as decimal (10,2)),'C') as [Old Rate] , FORMAT(cast(HQMA.NewValue as decimal (10,2)),'C') as [New Rate] , FORMAT((cast(HQMA.NewValue as decimal (10,2)) / nullif(cast(HQMA.OldValue as decimal (10,2)),0))-1,'P') as [Percent Change] , FORMAT(HQMA.DateTime, 'MM/dd/yy hh:mm tt') as [Changed On] , HQMA.UserName as [Changed By]

from HQMA

INNER JOIN PREHFullName PREH ON HQMA.KeyString = ''

WHERE TableName = 'bPREH' and RecType = 'C' and DateTime >= DATEADD(d,-7,getdate()) and FieldName = 'HrlyRate' GO

SQL Query #1

SELECT [PRCo]
,[Full Name]
,[TabChanged]
,[Changed On]
,[Changed By]

SQL Query #2

FROM (SELECT [PRCo] ,[Full Name] ,[TabChanged] ,[Changed On] ,[Changed By] FROM [Viewpoint].[dbo].[CTS_DDInfoChangeNotifier]

UNION ALL

SELECT NULL,'No changes found in the past day',NULL,NULL,NULL WHERE NOT EXISTS (SELECT * FROM [CTS_PayRateChangeNotifier]) ) t1