b1nary0mega
9/29/2015 - 6:09 PM
Delete and then rebuild the HFM_RN unit
Delete and then rebuild the HFM_RN unit
/*
FILE:Delete and then Rebuild HFM_RN.sql
AUTH: James R. Aylesworth
DATE: Sep 29, 2015
*****************************************************
Use with CAUTION:
This file will completely obliterate the old unit
from MSS!
This file will NOT remove any of employee profiles,
BUT it will remove any pertanent scheduling info!
It will then re-insert the new settings for the unit.
*****************************************************
*/
SET DEFINE ON;
DEFINE UNIT_NAME = 'HFM_RN';
/*DELETE ALL OUT UNIT DATA*/
DELETE
FROM employee_times
WHERE unit_id = '&UNIT_NAME';
DELETE FROM finalized WHERE unit_id = '&UNIT_NAME';
DELETE FROM groups WHERE unit_id = '&UNIT_NAME';
DELETE FROM ot_req WHERE unit_id = '&UNIT_NAME';
DELETE FROM slots WHERE unit_id = '&UNIT_NAME';
DELETE FROM slot_limits WHERE unit_id = '&UNIT_NAME';
DELETE FROM sub_group WHERE unit_id = '&UNIT_NAME';
DELETE FROM time_block_spec WHERE unit_id = '&UNIT_NAME';
DELETE FROM unit_events WHERE unit_id = '&UNIT_NAME';
DELETE FROM unit_spec WHERE unit_id = '&UNIT_NAME';
DELETE FROM UNIT_NP_TIME WHERE UNIT_ID = '&UNIT_NAME';
DELETE FROM UNIT_TIMES WHERE UNIT_ID = '&UNIT_NAME';
/*REMOVE ALL OLD PROFILE SCHEDULE DATA*/
UPDATE
( SELECT * FROM EMPLOYEE_PROFILE WHERE UNIT_ID = '&UNIT_NAME'
)
SET CONFLICTS = NULL,
REDSHIFTS = NULL,
WEEKEND_HRS = NULL,
WEEKEND_CONTRACT = NULL,
MINIMUM_NIGHT_SHIFT = NULL,
MINIMUM_EVENING_SHIFT = NULL,
GROUP_CHOICE_ID = 'GA',
SG_CHOICE_ID = '1',
SCHOOL = NULL,
PR_UNITS = NULL;
/*UPDATE EMPLS ALIAS*/
UPDATE employee_profile
SET ms_alias = (
CASE
WHEN (title LIKE 'Manager%')
THEN SUBSTR(first_name,1,1)
|| SUBSTR(last_name,1,4)
|| '_MGR'
WHEN (title LIKE 'RN-%')
THEN SUBSTR(first_name,1,1)
|| SUBSTR(LAST_NAME,1,5)
|| '_RN'
WHEN (title LIKE 'Nurse Practitioner-%')
THEN SUBSTR(first_name,1,1)
|| SUBSTR(LAST_NAME,1,5)
|| '_NP'
WHEN (title LIKE 'Sec 3%')
THEN SUBSTR(first_name,1,1)
|| SUBSTR(last_name,1,4)
|| '_SEC'
WHEN (title LIKE 'Supervisor%')
THEN SUBSTR(first_name,1,1)
|| SUBSTR(last_name,1,4)
|| '_SUP'
WHEN (ms_alias IS NULL)
THEN SUBSTR(first_name,1,1)
|| SUBSTR(last_name,1,8)
ELSE ms_alias
END )
WHERE UNIT_ID='&UNIT_NAME'
AND DEPT ='891';
/*PUT HEIDI AND MINDY IN ADMIN GROUP*/
UPDATE
(SELECT *
FROM EMPLOYEE_PROFILE
WHERE EMPLOYEE_ID IN ('15107','6657')
AND UNIT_ID = '&UNIT_NAME'
)
SET GROUP_CHOICE_ID = 'GB';
/*INSERT THE NEW UNIT_SPEC*/
REM INSERTING into UNIT_SPEC
INSERT
INTO UNIT_SPEC
(
UNIT_ID,
UNIT_NAME,
FACILITY_ID,
DAY_START_TIME,
SLOTS_PER_DAY,
HOURS_PER_SLOT,
STRT_SLT_WKND,
END_SLT_WKND,
MSTR_STRT_DT,
DEPT,
MSS_UNIT_ID,
CALL,
ACTIVE,
SERVICE_ID
)
VALUES
(
'HFM_RN',
'Highland Family Med. RN',
'HH',
23,10,
2.4,
NULL,
NULL,
TO_DATE('21-DEC-08 00:00:00','DD-MON-RR HH24:MI:SS'),
891,
NULL,
2,
'Y',
0
);
/*INSERT THE NEW GROUPS*/
REM INSERTING into GROUPS
INSERT
INTO GROUPS
(
GROUP_NAME,
UNIT_ID,
CALC_WITH,
SORT_ORDER,
GROUP_ID,
GRP_PR_TIME_CODES,
GRP_NP_TIME_CODES,
STOP_DATE
)
VALUES
(
'RN',
'HFM_RN',
NULL,
1,
'GA',
'130D4,1DE,430E5,530E4,830D,830D4,9D,D',
'CONFLICT,SCHOOL',
NULL
);
INSERT
INTO GROUPS
(
GROUP_NAME,
UNIT_ID,
CALC_WITH,
SORT_ORDER,
GROUP_ID,
GRP_PR_TIME_CODES,
GRP_NP_TIME_CODES,
STOP_DATE
)
VALUES
(
'ADMIN',
'HFM_RN',
NULL,
2,
'GB',
'130D4,1DE,430E5,530E4,830D,830D4,9D,D',
'CONFLICT,SCHOOL',
NULL
);
/*INSERT THE NEW SUB_GROUP*/
REM INSERTING into SUB_GROUP
INSERT
INTO SUB_GROUP
(
UNIT_ID,
SG_NAME,
SG_ID,
BB_INCL_IN_NUMBERS,
SG_ALPHA,
STOP_DATE
)
VALUES
(
'HFM_RN',
'RN',
'1',
NULL,
'A',
NULL
);
INSERT
INTO SUB_GROUP
(
UNIT_ID,
SG_NAME,
SG_ID,
BB_INCL_IN_NUMBERS,
SG_ALPHA,
STOP_DATE
)
VALUES
(
'HFM_RN',
'Group Visit',
'2',
NULL,
'B',
NULL
);
INSERT
INTO SUB_GROUP
(
UNIT_ID,
SG_NAME,
SG_ID,
BB_INCL_IN_NUMBERS,
SG_ALPHA,
STOP_DATE
)
VALUES
(
'HFM_RN',
'Nurse Leader',
'3',
NULL,
'C',
NULL
);
INSERT
INTO SUB_GROUP
(
UNIT_ID,
SG_NAME,
SG_ID,
BB_INCL_IN_NUMBERS,
SG_ALPHA,
STOP_DATE
)
VALUES
(
'HFM_RN',
'Orient',
'4',
NULL,
'D',
NULL
);
/*INSERT THE NEW SLOTS*/
REM INSERTING into SLOTS
INSERT
INTO SLOTS
(
UNIT_ID,
SLOT_ID,
SLOT_NAME,
HOURS,
DISCRIPTION,
SHIFT_CODE
)
VALUES
(
'HFM_RN',
1,
'23',
9,
'11p to 8a',
'N '
);
INSERT
INTO SLOTS
(
UNIT_ID,
SLOT_ID,
SLOT_NAME,
HOURS,
DISCRIPTION,
SHIFT_CODE
)
VALUES
(
'HFM_RN',
2,
'8',
0.5,
'8a to 8:30a',
'D '
);
INSERT
INTO SLOTS
(
UNIT_ID,
SLOT_ID,
SLOT_NAME,
HOURS,
DISCRIPTION,
SHIFT_CODE
)
VALUES
(
'HFM_RN',
3,
'830',
0.5,
'8:30a to 9a',
'D '
);
INSERT
INTO SLOTS
(
UNIT_ID,
SLOT_ID,
SLOT_NAME,
HOURS,
DISCRIPTION,
SHIFT_CODE
)
VALUES
(
'HFM_RN',
4,
'9',
3.5,
'9a to 12:30p',
'D '
);
INSERT
INTO SLOTS
(
UNIT_ID,
SLOT_ID,
SLOT_NAME,
HOURS,
DISCRIPTION,
SHIFT_CODE
)
VALUES
(
'HFM_RN',
5,
'1230',
1,
'12:30p to 1:30p',
'D '
);
INSERT
INTO SLOTS
(
UNIT_ID,
SLOT_ID,
SLOT_NAME,
HOURS,
DISCRIPTION,
SHIFT_CODE
)
VALUES
(
'HFM_RN',
6,
'1330',
3,
'1:30p to 4:30p',
'D '
);
INSERT
INTO SLOTS
(
UNIT_ID,
SLOT_ID,
SLOT_NAME,
HOURS,
DISCRIPTION,
SHIFT_CODE
)
VALUES
(
'HFM_RN',
7,
'1630',
0.5,
'4:30p to 5p',
'E '
);
INSERT
INTO SLOTS
(
UNIT_ID,
SLOT_ID,
SLOT_NAME,
HOURS,
DISCRIPTION,
SHIFT_CODE
)
VALUES
(
'HFM_RN',
8,
'17',
0.5,
'5p to 5:30p',
'E '
);
INSERT
INTO SLOTS
(
UNIT_ID,
SLOT_ID,
SLOT_NAME,
HOURS,
DISCRIPTION,
SHIFT_CODE
)
VALUES
(
'HFM_RN',
9,
'1730',
4,
'5:30p to 9:30p',
'E '
);
INSERT
INTO SLOTS
(
UNIT_ID,
SLOT_ID,
SLOT_NAME,
HOURS,
DISCRIPTION,
SHIFT_CODE
)
VALUES
(
'HFM_RN',
10,
'2130',
1.5,
'9:30p to 11p',
'E '
);
/*INSERT THE NEW SLOT_LIMITS*/
REM INSERTING into SLOT_LIMITS
INSERT
INTO SLOT_LIMITS
(
UNIT_ID,
ROW_ID,
SUNDAY,
MONDAY,
TUESDAY,
WEDNESDAY,
THURSDAY,
FRIDAY,
SATURDAY,
ROW_TYPE,
HOLIDAY
)
VALUES
(
'HFM_RN',
'1',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'SUB_GROUP',
'0,0,0,0,0,0,0,0,0,0'
);
INSERT
INTO SLOT_LIMITS
(
UNIT_ID,
ROW_ID,
SUNDAY,
MONDAY,
TUESDAY,
WEDNESDAY,
THURSDAY,
FRIDAY,
SATURDAY,
ROW_TYPE,
HOLIDAY
)
VALUES
(
'HFM_RN',
'2',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'SUB_GROUP',
'0,0,0,0,0,0,0,0,0,0'
);
INSERT
INTO SLOT_LIMITS
(
UNIT_ID,
ROW_ID,
SUNDAY,
MONDAY,
TUESDAY,
WEDNESDAY,
THURSDAY,
FRIDAY,
SATURDAY,
ROW_TYPE,
HOLIDAY
)
VALUES
(
'HFM_RN',
'3',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'SUB_GROUP',
'0,0,0,0,0,0,0,0,0,0'
);
INSERT
INTO SLOT_LIMITS
(
UNIT_ID,
ROW_ID,
SUNDAY,
MONDAY,
TUESDAY,
WEDNESDAY,
THURSDAY,
FRIDAY,
SATURDAY,
ROW_TYPE,
HOLIDAY
)
VALUES
(
'HFM_RN',
'4',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'0,0,0,0,0,0,0,0,0,0',
'SUB_GROUP',
'0,0,0,0,0,0,0,0,0,0'
);
/*INSERT THE NEW UNIT_TIMES*/
REM INSERTING into UNIT_TIMES
INSERT
INTO UNIT_TIMES
(
UNIT_ID,
TIME_CODE,
END_TIME,
NUMBER_OF_SLOTS,
DISCRIPTION,
ASSOC_HOURS,
ACT_END_TM
)
VALUES
(
'HFM_RN',
'130D4',
'8',
'3',
'1:30p to 5:30p',
'4',
'17:30'
);
INSERT
INTO UNIT_TIMES
(
UNIT_ID,
TIME_CODE,
END_TIME,
NUMBER_OF_SLOTS,
DISCRIPTION,
ASSOC_HOURS,
ACT_END_TM
)
VALUES
(
'HFM_RN',
'1DE',
'9',
'4',
'1p to 9:30p',
'8',
'21:30'
);
INSERT
INTO UNIT_TIMES
(
UNIT_ID,
TIME_CODE,
END_TIME,
NUMBER_OF_SLOTS,
DISCRIPTION,
ASSOC_HOURS,
ACT_END_TM
)
VALUES
(
'HFM_RN',
'430E5',
'9',
'3',
'4:30p to 9:30p',
'5',
'21:30'
);
INSERT
INTO UNIT_TIMES
(
UNIT_ID,
TIME_CODE,
END_TIME,
NUMBER_OF_SLOTS,
DISCRIPTION,
ASSOC_HOURS,
ACT_END_TM
)
VALUES
(
'HFM_RN',
'530E4',
'9',
'1',
'5:30p to 9:30p',
'4',
'21:30'
);
INSERT
INTO UNIT_TIMES
(
UNIT_ID,
TIME_CODE,
END_TIME,
NUMBER_OF_SLOTS,
DISCRIPTION,
ASSOC_HOURS,
ACT_END_TM
)
VALUES
(
'HFM_RN',
'830D',
'7',
'5',
'8:30a to 5p',
'8',
'17:00'
);
INSERT
INTO UNIT_TIMES
(
UNIT_ID,
TIME_CODE,
END_TIME,
NUMBER_OF_SLOTS,
DISCRIPTION,
ASSOC_HOURS,
ACT_END_TM
)
VALUES
(
'HFM_RN',
'830D4',
'4',
'2',
'8:30a to 12:30p',
'4',
'12:30'
);
INSERT
INTO UNIT_TIMES
(
UNIT_ID,
TIME_CODE,
END_TIME,
NUMBER_OF_SLOTS,
DISCRIPTION,
ASSOC_HOURS,
ACT_END_TM
)
VALUES
(
'HFM_RN',
'9D',
'8',
'5',
'9a to 5:30p',
'8',
'17:30'
);
INSERT
INTO UNIT_TIMES
(
UNIT_ID,
TIME_CODE,
END_TIME,
NUMBER_OF_SLOTS,
DISCRIPTION,
ASSOC_HOURS,
ACT_END_TM
)
VALUES
(
'HFM_RN',
'D',
'6',
'5',
'8a to 4:30p',
'8',
'16:30'
);
/*INSERT THE NEW TIME_BLOCK_SPEC*/
REM INSERTING into TIME_BLOCK_SPEC
INSERT
INTO TIME_BLOCK_SPEC
(
UNIT_ID,
REMIND_1,
REMIND_2,
REMIND_3,
REMIND_4,
REMIND_5,
REMIND_6,
REMIND_7,
REMIND_8,
REMIND_9,
REMIND_10,
PREF_1,
PREF_2,
PREF_3,
PREF_4,
PREF_5,
PREF_6,
PREF_7,
PREF_8,
PREF_9,
PREF_10,
THIS_TB_STRT_DATE,
SJ_STRT_DT,
SJ_END_DT,
UTOPIA_DUE,
UTOPIA_DUE_TM,
SJ_STRT_TM,
SJ_END_TM,
FS_POST_DT,
FS_POST_TM
)
VALUES
(
'HFM_RN',
'REMIND_1',
'REMIND_2',
'REMIND_3',
'REMIND_4',
'REMIND_5',
'REMIND_6',
'REMIND_7',
'REMIND_8',
'REMIND_9',
'REMIND_10',
'PREF_1',
'PREF_2',
'PREF_3',
'PREF_4',
'PREF_5',
'PREF_6',
'PREF_7',
'PREF_8',
'PREF_9',
'PREF_10',
to_date('04-OCT-15 00:00:00','DD-MON-RR HH24:MI:SS'),
30,21,31,
'23:59',
'00:00',
'23:00',
18,
'15:00'
);
INSERT
INTO TIME_BLOCK_SPEC
(
UNIT_ID,
REMIND_1,
REMIND_2,
REMIND_3,
REMIND_4,
REMIND_5,
REMIND_6,
REMIND_7,
REMIND_8,
REMIND_9,
REMIND_10,
PREF_1,
PREF_2,
PREF_3,
PREF_4,
PREF_5,
PREF_6,
PREF_7,
PREF_8,
PREF_9,
PREF_10,
THIS_TB_STRT_DATE,
SJ_STRT_DT,
SJ_END_DT,
UTOPIA_DUE,
UTOPIA_DUE_TM,
SJ_STRT_TM,
SJ_END_TM,
FS_POST_DT,
FS_POST_TM
)
VALUES
(
'HFM_RN',
'REMIND_1',
'REMIND_2',
'REMIND_3',
'REMIND_4',
'REMIND_5',
'REMIND_6',
'REMIND_7',
'REMIND_8',
'REMIND_9',
'REMIND_10',
'PREF_1',
'PREF_2',
'PREF_3',
'PREF_4',
'PREF_5',
'PREF_6',
'PREF_7',
'PREF_8',
'PREF_9',
'PREF_10',
to_date('27-DEC-15 00:00:00','DD-MON-RR HH24:MI:SS'),
30,21,31,
'23:59',
'00:00',
'23:00',
18,
'15:00'
);
INSERT
INTO TIME_BLOCK_SPEC
(
UNIT_ID,
REMIND_1,
REMIND_2,
REMIND_3,
REMIND_4,
REMIND_5,
REMIND_6,
REMIND_7,
REMIND_8,
REMIND_9,
REMIND_10,
PREF_1,
PREF_2,
PREF_3,
PREF_4,
PREF_5,
PREF_6,
PREF_7,
PREF_8,
PREF_9,
PREF_10,
THIS_TB_STRT_DATE,
SJ_STRT_DT,
SJ_END_DT,
UTOPIA_DUE,
UTOPIA_DUE_TM,
SJ_STRT_TM,
SJ_END_TM,
FS_POST_DT,
FS_POST_TM
)
VALUES
(
'HFM_RN',
'REMIND_1',
'REMIND_2',
'REMIND_3',
'REMIND_4',
'REMIND_5',
'REMIND_6',
'REMIND_7',
'REMIND_8',
'REMIND_9',
'REMIND_10',
'PREF_1',
'PREF_2',
'PREF_3',
'PREF_4',
'PREF_5',
'PREF_6',
'PREF_7',
'PREF_8',
'PREF_9',
'PREF_10',
to_date('15-NOV-15 00:00:00','DD-MON-RR HH24:MI:SS'),
30,21,31,
'23:59',
'00:00',
'23:00',
18,
'15:00'
);
INSERT
INTO TIME_BLOCK_SPEC
(
UNIT_ID,
REMIND_1,
REMIND_2,
REMIND_3,
REMIND_4,
REMIND_5,
REMIND_6,
REMIND_7,
REMIND_8,
REMIND_9,
REMIND_10,
PREF_1,
PREF_2,
PREF_3,
PREF_4,
PREF_5,
PREF_6,
PREF_7,
PREF_8,
PREF_9,
PREF_10,
THIS_TB_STRT_DATE,
SJ_STRT_DT,
SJ_END_DT,
UTOPIA_DUE,
UTOPIA_DUE_TM,
SJ_STRT_TM,
SJ_END_TM,
FS_POST_DT,
FS_POST_TM
)
VALUES
(
'HFM_RN',
'REMIND_1',
'REMIND_2',
'REMIND_3',
'REMIND_4',
'REMIND_5',
'REMIND_6',
'REMIND_7',
'REMIND_8',
'REMIND_9',
'REMIND_10',
'PREF_1',
'PREF_2',
'PREF_3',
'PREF_4',
'PREF_5',
'PREF_6',
'PREF_7',
'PREF_8',
'PREF_9',
'PREF_10',
TO_DATE('21-DEC-08 00:00:00','DD-MON-RR HH24:MI:SS'),
27,20,27,
'07:00',
'15:00',
'07:00',
17,
'15:00'
);
/*COMMIT ALL CHANGES*/
COMMIT;