b1nary0mega
9/18/2015 - 1:38 PM

update an employee name

update an employee name

/*------------------------------------------------------------------------------
DATE: 02/27/2012
AUTH: James R. Aylesworth
DESC: This script is designed to update an employee's information when a name 
change has occurred

USE: Change 000000 to match employee's ID
     Change defaults to new information
     Run the script (F5)
     
** WARNING **
This script commits changes AS IT RUNS! This is so that the trailing tables can
get the new information from the first updated table, EMPLOYEE_PROFILES.
------------------------------------------------------------------------------*/

DEFINE emplID = '123456';
DEFINE new_first_name = 'Jane';
DEFINE new_last_name = 'Doe';
DEFINE new_domain_id = 'jdoe';
DEFINE new_email = 'Jane_Doe@URMC.Rochester.edu';


--update the EMPLOYEE_PROFILES table
UPDATE
  (SELECT * FROM EMPLOYEE_PROFILE WHERE EMPLOYEE_ID = &emplID
  )
set 
  FIRST_NAME  = '&new_first_name',
  LAST_NAME   = '&new_last_name',
  DOMAIN_ID   = '&new_domain_id',
  EMAIL       = '&new_email';
  
commit;

--update the EVAL_EMPL_PR table
UPDATE
  ( SELECT * FROM EVAL_EMPL_PR WHERE EMPL_ID = &emplID
  )
SET EMPL_NAME =
  (SELECT DISTINCT first_name
    || ' '
    || last_name AS "EMPL_NAME"
  FROM employee_profile
  where EMPLOYEE_ID = &emplID
  );

commit;

--update the EVAL_PG08 table (Strong Commitment)
update
  ( SELECT * FROM EVAL_PG08 WHERE EMPL_ID = &emplID
  )
set DIG_SIGN_NAME =
  (SELECT DISTINCT first_name
    || ' '
    || last_name AS "EMPL_NAME"
  from EMPLOYEE_PROFILE
  where EMPLOYEE_ID = &emplID
  );
  
commit;