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;