b1nary0mega
12/16/2015 - 7:30 PM

Move all folks from one unit to another (this example was PCT's from CH3 to 334).

Move all folks from one unit to another (this example was PCT's from CH3 to 334).

/* Get a list of all the folks that will be affected */
SELECT DISTINCT EMPLOYEE_ID,
  LAST_NAME,
  FIRST_NAME,
  TITLE,
  POS_CODE
FROM EMPLOYEE_PROFILE
WHERE UNIT_ID = 'CH3'
AND TITLE     = 'Patient Care Technician';
/* Delete all entered times for 9/20 forward that may create a key violation */
DELETE
FROM EMPLOYEE_TIMES
WHERE EMPLOYEE_ID IN
  ( SELECT DISTINCT EMPLOYEE_ID
  FROM EMPLOYEE_PROFILE
  WHERE UNIT_ID = 'CH3'
  AND TITLE     = 'Patient Care Technician'
  )
AND UNIT_ID   = '334'
AND END_DTTM >= TO_DATE('09-20-15 00:00:00','mm-dd-yy hh24:mi:ss');
/* Move all time over to new unit for 9/20 forward */
UPDATE EMPLOYEE_TIMES
SET UNIT_ID        = '334'
WHERE EMPLOYEE_ID IN
  ( SELECT DISTINCT EMPLOYEE_ID
  FROM EMPLOYEE_PROFILE
  WHERE UNIT_ID = 'CH3'
  AND TITLE     = 'Patient Care Technician'
  )
AND UNIT_ID   = 'CH3'
AND END_DTTM >= TO_DATE('09-20-15 00:00:00','mm-dd-yy hh24:mi:ss');
/* Delete any active employee profiles that may create a key violation */
DELETE
FROM EMPLOYEE_PROFILE
WHERE UNIT_ID    = 'CH3'
AND TITLE        = 'Patient Care Technician'
AND EMPLOYEE_ID IN
  ( SELECT DISTINCT EMPLOYEE_ID
  FROM EMPLOYEE_PROFILE
  WHERE UNIT_ID         = '334'
  AND TITLE             = 'Patient Care Technician'
  AND MSS_UNIT_TERM_DT IS NULL
  );
/* Move Employee Profiles to correct unit */
UPDATE EMPLOYEE_PROFILE
SET UNIT_ID   = '334'
WHERE UNIT_ID = 'CH3'
AND TITLE     = 'Patient Care Technician';

COMMIT;

/* Get a list of all the folks that will be affected */
SELECT DISTINCT EMPLOYEE_ID,
  LAST_NAME,
  FIRST_NAME,
  TITLE,
  POS_CODE
FROM EMPLOYEE_PROFILE
WHERE UNIT_ID = '334'
AND TITLE     = 'Patient Unit Secretary';
/* Delete all entered times for 9/20 forward that may create a key violation */
DELETE
FROM EMPLOYEE_TIMES
WHERE EMPLOYEE_ID IN
  ( SELECT DISTINCT EMPLOYEE_ID
  FROM EMPLOYEE_PROFILE
  WHERE UNIT_ID = '334'
  AND TITLE     = 'Patient Unit Secretary'
  )
AND UNIT_ID   = 'CH3'
AND END_DTTM >= TO_DATE('09-20-15 00:00:00','mm-dd-yy hh24:mi:ss');
/* Move all time over to new unit for 9/20 forward */
UPDATE EMPLOYEE_TIMES
SET UNIT_ID        = 'CH3'
WHERE EMPLOYEE_ID IN
  ( SELECT DISTINCT EMPLOYEE_ID
  FROM EMPLOYEE_PROFILE
  WHERE UNIT_ID = '334'
  AND TITLE     = 'Patient Unit Secretary'
  )
AND UNIT_ID   = '334'
AND END_DTTM >= TO_DATE('09-20-15 00:00:00','mm-dd-yy hh24:mi:ss');
/* Delete any active employee profiles that may create a key violation */
DELETE
FROM EMPLOYEE_PROFILE
WHERE UNIT_ID    = '334'
AND TITLE        = 'Patient Unit Secretary'
AND EMPLOYEE_ID IN
  ( SELECT DISTINCT EMPLOYEE_ID
  FROM EMPLOYEE_PROFILE
  WHERE UNIT_ID         = 'CH3'
  AND TITLE             = 'Patient Unit Secretary'
  AND MSS_UNIT_TERM_DT IS NULL
  );
/* Move Employee Profiles to correct unit */
UPDATE EMPLOYEE_PROFILE
SET UNIT_ID   = 'CH3'
WHERE UNIT_ID = '334'
AND TITLE     = 'Patient Unit Secretary';

COMMIT;

/* Update all position codes so they are listed as primary for requested unit */
UPDATE EMPLOYEE_PROFILE
SET POS_CODE = 401
WHERE TITLE = 'Patient Care Technician'
AND MSS_UNIT_TERM_DT IS NULL
AND UNIT_ID = '334';

UPDATE EMPLOYEE_PROFILE
SET POS_CODE = 467
WHERE TITLE = 'Patient Unit Secretary'
AND MSS_UNIT_TERM_DT IS NULL
AND UNIT_ID = 'CH3';

COMMIT;