Scenario: Looking to receive an email notice when an Applicant submits their application. Note: This solution assumes the recruiters have VA User profiles, if not you can join PREHFullName or HRRM to get the email address. 

Example Solution:

  1. Create the SQL View below and grant select to VCSUsers
  2. Open the WF Notifier Queries form
  3. Create a new Record by entering CTS_ApplicantSubmission in the Query Name Field
  4. Enter Sends notification for every applicant submission in the Title Field
  5. Enter Created by CTS-Kevin Halme on 2024.02.08 to send notification for every applicant submission in the Description Field
  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. Check the Event Based Query option
  9. On the Email Fields Check the Key Fields box for the following columns: OnboardID, DateSubmitted, RecruiterEmail     
  10. Press the Test Query button to test the query.
  11. If the query tests ok, save and close the form.
  12. Open CTS_ApplicantSubmission
  13. Enter CTS_ApplicantSubmission in the Job Name & Description Fields.
  14. Set Query type to 0-WF Notifier Query
  15. Select CTS_ApplicantSubmission as the query.
  16. Set the desired schedule
  17. On the email tab, enter the desired recipient (the query returns the assign recruiter’s email in [RecruiterEmail]), subject, and header.
  18. Create the body using the fields, if desired a link to the applicant record can be included using the [Link] field.
  19. 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_ApplicantSubmission]
 
AS
 
/*
Created on 2022.04.27 by CTS-Kevin Halme for Portal Recruiter Notification on Applicant Submission
*/
 
 
SELECT RR.RecruiterUserKeyID                                                                                                              
,      UP.FullName                                                                                                                        
,      UP.EMail                                                                                                                            as RecruiterEmail
,      OV.FirstName                                                                                                                       
,      OV.LastName                                                                                                                        
,      OV.MidName                                                                                                                         
,      OV.Email                                                                                                                            as ApplicantEmail
,      OV.HRRMFullName                                                                                                                    
,      OV.IsApplyOnline                                                                                                                   
,      OV.IsReqPosted                                                                                                                     
,      OV.[Job]                                                                                                                           
,      OV.JobName                                                                                                                         
,      OV.OnboardProfileName                                                                                                              
,      OV.PositionTitle                                                                                                                   
,      CAST(OV.DateSubmitted as Date)                                                                                                      as DateSubmitted
,      OV.DateInitiated                                                                                                                   
,      OV.OnboardID                                                                                                                       
,      config.ConfigValue + ‘Hiring/ApplicantDetail/#/’+cast(OV.ReqID as varchar(50))+’/’+cast(OV.OnboardID as varchar(50))+’/0/RD’ as Link
FROM      [HFFS].[HFFS_VW_OnboardView]  OV
LEFT JOIN [HFFS].[HFFS_Req]          R  ON OV.ReqID = R.ReqID
LEFT JOIN [HFFS].[HFFS_ReqRecruiter] RR ON RR.ReqID = R.ReqID
LEFT JOIN [HFFS].[HFFS_HRUsers]      HR ON RR.RecruiterUserKeyID = HR.UserKeyID
LEFT JOIN DDUP UP ON HR.PRCo = UP.PRCo
AND HR.Employee = UP.Employee
LEFT JOIN [HFFS].[HFFS_Config] config on ‘HRPortalAddress’ = ConfigName
WHERE RecruiterUserKeyID > 0
GO
 
–GRANT SELECT ON [CTS_ApplicantSubmission] TO [VCSUsers]

SQL Query #1

SELECT RecruiterUserKeyID as [RecruiterUserKeyID]
, FullName as [FullName]
, RecruiterEmail as [RecruiterEmail]
, FirstName as [FirstName]
, LastName as [LastName]
, MidName as [MidName]
, ApplicantEmail as [ApplicantEmail]
, HRRMFullName as [HRRMFullName]
, IsApplyOnline as [IsApplyOnline]
, IsReqPosted as [IsReqPosted]
, Job as [Job]
, JobName as [JobName]
, OnboardProfileName as [OnboardProfileName]
, PositionTitle as [PositionTitle]
, DateSubmitted as [DateSubmitted]
, DateInitiated as [DateInitiated]
, OnboardID as [OnboardID]
, Link as [Link],[Changed By]

SQL Query #2

FROM CTS_ApplicantSubmission