CERTEXPSQL

CERTEXPSQL.wdl

Workflow Overview

The workflow is called "Main Flow" and contains a series of steps (activities) and transitions (arrows) between them.

Step-by-Step Workflow

1. Start

• Description: Start node

• Function: Begins the process

2. Set Date (A4)

• Description: Runs a small C# code block to update date variables (EXP30, EXP90, EXP180) to the current date plus 30, 90, 180 days.

• How: Runs this code:

• Variables.EXP30 = DateTime.Now.AddDays(30).ToShortDateString();

• Variables.EXP90 = DateTime.Now.AddDays(90).ToShortDateString();

Variables.EXP180 = DateTime.Now.AddDays(180).ToShortDateString();

• Purpose: So later steps can use those dates.

3. CertExpire (A1)

• Description: Run SQL to find expiring certificates.

• Action:

  • Executes a SQL query that looks for credentials (certificates) expiring on exactly 30, 90, or 180 days from today.

  • Joins two tables: hr_cntycred and hr_empmstr (connecting people to their credentials).

  • Pulls information like SSN, credential type, document number, description, expiration date, employee first and last name, ID, worksite, and email.

4. Email Folks (A2)

• Description: Email notification step

• Action:

  • Sends an email to each relevant user whose certifications are about to expire.

  • Email content:

    • "Dear [Name], the following items will be expiring on [cert end date]. Type: [cred_type], Desc: [subj_desc], Doc#: [document]."

  • Email goes to the employee's email address in the e_mail field in the data.

  • Retry if failed.

5. End

• Description: Workflow ends.