Back to Home

User Email Update

Overview

For some sites, business does not want the email to go out the advocate. In this case, users' email addresses from the user setup table needs to be updated to a mailbox instead of their Cigna email address. Also, they sometimes wants to change the email back to advocates' Cigna email address.

Update Email Address to Mailbox

Steps:

  1. Identify which advocates are affected by running the Script1 below. i.e. All emails need to be turned off for Samples 370, 700, 706, 1131, 1133, & 1828 effective with 1/27/2020 audits.
SELECT * FROM smart.sam_SetupUser WHERE UserNo IN (SELECT AdvocateNo FROM smart.Sample_Advocate WHERE SampleNo IN (370,700,706,1131,1133,1828))
  1. Update the email address and the WHERE condition using Script2 below.. If email address is not provided by the business, ask what email box to use.
UPDATE smart.sam_SetupUser SET EmailAddress = '' WHERE UserNo IN (SELECT AdvocateNo FROM smart.Sample_Advocate WHERE SampleNo IN (370,700,706,1131,1133,1828))

  1. Put into SAFE Script template, save the file as CEM_SAFE_SCRIPT.sql, send to Bud and Alexandra to request SAFE to run overnight.

  2. Check next morning to see if the email address' are updated.

Update Email address back to advocate's email address

  1. Use an Excel sheet to create the below format Script3
SELECT 'H15233' NTID,'Aisha.jackson@cigna.com' Email INTO #CSA UNION
SELECT 'H13663','Allison.hall2@cigna.com' UNION
SELECT 'H15253','Amanda.starnes@cigna.com' UNION
SELECT 'H15200','Anna.ewton@cigna.com' UNION
SELECT 'c96403','Ashley.king2@cigna.com' UNION
SELECT 'H10573','Beverly.tiano@cigna.com'

UPDATE a
    SET EmailAddress = b.Email
FROM smart.sam_setupuser a    INNER JOIN #CSA b oN a.NTID = b.NTID

  1. Put into SAFE Script template, save the file as CEM_SAFE_SCRIPT.sql, send to Bud and Alexandra to request SAFE to run overnight.

  2. Check next morning to see if the email address� are updated.