--**************************************************************************************************
--*
--*	This script will create the SQL Server tables needed for Microsoft Project Server
--*
--*	Privileges needed: member of db_owner or db_ddladmin
--*
--*	Comments
--*	-----------------------------------------------------------------------------------------
--*     1 - In order to specify non-default filegroups, uncomment the "-- ON [PRIMARY]" line 
--*         within each CREATE statement and replace "Primary" with the desired filegroup
--*
--**************************************************************************************************

--**************************************************************************************************
--*		MSP_ASSN_ENTERPRISE
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_ASSN_ENTERPRISE')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_ASSN_ENTERPRISE
GO


CREATE TABLE dbo.MSP_ASSN_ENTERPRISE
(
	RESERVED_DATA 			char(1) 		NULL,
	PROJ_ID 			int 			NULL,
	ASSN_ENTSUMPROJID 		int 			NULL,
	ASSN_ACT_FINISH 		datetime 		NULL,
	ASSN_ACT_START 			datetime 		NULL,
	ASSN_ACWP 			decimal(25,6) 		NULL,
	ASSN_BCWP 			decimal(25,6) 		NULL,
	ASSN_BCWS 			decimal(25,6) 		NULL,
	ASSN_RES_TYPE 			bit 			NULL DEFAULT 0,
	ASSN_IS_OVERALLOCATED 		bit 			NULL DEFAULT 0,
	ASSN_WORK_CONTOUR 		smallint 		NULL,
	ASSN_START_VAR 			int 			NULL,
	ASSN_FINISH_VAR 		int 			NULL,
	ASSN_UPDATE_NEEDED 		bit 			NULL DEFAULT 0,
	EXT_EDIT_REF_DATA 		text 			NULL,
	ASSN_UID 			int 			NULL,
	ASSN_HAS_LINKED_FIELDS 		bit 			NULL DEFAULT 0,
	ASSN_IS_CONFIRMED 		bit 			NULL DEFAULT 0,
	ASSN_RESPONSE_PENDING 		bit 			NULL DEFAULT 0,
	ASSN_HAS_NOTES 			bit 			NULL DEFAULT 0,
	ASSN_TEAM_STATUS_PENDING 	bit 			NULL DEFAULT 0,
	TASK_UID 			int 			NULL,
	RES_UID 			int 			NULL,
	ASSN_START_DATE 		datetime 		NULL,
	ASSN_FINISH_DATE 		datetime 		NULL,
	ASSN_DELAY 			int 			NULL,
	ASSN_DELAY_FMT 			smallint 		NULL,
	ASSN_LEVELING_DELAY 		int 			NULL,
	ASSN_COST_RATE_TABLE 		smallint 		NULL,
	ASSN_BASE_START 		datetime 		NULL,
	ASSN_BASE_FINISH 		datetime 		NULL,
	ASSN_MATERIAL_RATE_FMT 		smallint 		NULL,
	ASSN_UNITS 			decimal(25,6) 		NULL,
	ASSN_WORK 			decimal(25,6) 		NULL,
	ASSN_ACT_WORK 			decimal(25,6) 		NULL,
	ASSN_REG_WORK 			decimal(25,6) 		NULL,
	ASSN_REM_WORK 			decimal(25,6) 		NULL,
	ASSN_BASE_WORK 			decimal(25,6) 		NULL,
	ASSN_COST 			decimal(25,6) 		NULL,
	ASSN_ACT_COST 			decimal(25,6) 		NULL,
	ASSN_REM_COST 			decimal(25,6) 		NULL,
	ASSN_BASE_COST 			decimal(25,6) 		NULL,
	ASSN_BASE_COST_PER_USE 		decimal(25,6) 		NULL,
	ASSN_OVT_WORK 			decimal(25,6) 		NULL,
	ASSN_ACT_OVT_WORK 		decimal(25,6) 		NULL,
	ASSN_REM_OVT_WORK 		decimal(25,6) 		NULL,
	ASSN_ACT_OVT_COST 		decimal(25,6) 		NULL,
	ASSN_REM_OVT_COST 		decimal(25,6) 		NULL,
	ASSN_RTF_NOTES 			image 			NULL,
	ASSN_BOOKING_TYPE 		smallint 		NULL,
        ASSN_ACT_WORK_PROT 		decimal(25,6) 		NULL,
        ASSN_ACT_OVT_WORK_PROT 		decimal(25,6) 		NULL,
        ASSN_CREATION_DATE		datetime		NULL,
        ASSN_PROJ_VERSION_ID		int			NULL,
        ASSN_DEFAULT_VERSION_PROJ_ID	int			NULL
)
-- ON [PRIMARY]
GO


CREATE CLUSTERED INDEX I_MSP_ASSN_ENTERPRISE
ON dbo.MSP_ASSN_ENTERPRISE (ASSN_ENTSUMPROJID, ASSN_UID) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO


CREATE INDEX I_MSP_ASSN_ENTERPRISE_ASSN_VERSION_ID_DEFAULT_VERSION_PROJ_ID
ON dbo.MSP_ASSN_ENTERPRISE (ASSN_PROJ_VERSION_ID, ASSN_DEFAULT_VERSION_PROJ_ID) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO


CREATE INDEX I_MSP_ASSN_ENTERPRISE_ASSN_ENTSUMPROJID_RES_UID
ON dbo.MSP_ASSN_ENTERPRISE (ASSN_ENTSUMPROJID, RES_UID) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_ASSIGNMENTS
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_ASSIGNMENTS')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_ASSIGNMENTS
GO


CREATE TABLE dbo.MSP_ASSIGNMENTS
(
   RESERVED_DATA 			char(1) 		NULL,
   PROJ_ID 				int 			NULL,
   ASSN_ENTSUMPROJID 			int 			NULL,
   ASSN_ACT_FINISH 			datetime 		NULL,
   ASSN_ACT_START 			datetime 		NULL,
   ASSN_ACWP 				decimal(25,6) 		NULL,
   ASSN_BCWP 				decimal(25,6) 		NULL,
   ASSN_BCWS 				decimal(25,6) 		NULL,
   ASSN_RES_TYPE 			bit 			NULL DEFAULT 0,
   ASSN_IS_OVERALLOCATED 		bit 			NULL DEFAULT 0,
   ASSN_WORK_CONTOUR 			smallint 		NULL,
   ASSN_START_VAR 			int 			NULL,
   ASSN_FINISH_VAR 			int 			NULL,
   ASSN_UPDATE_NEEDED 			bit 			NULL DEFAULT 0,
   EXT_EDIT_REF_DATA 			text 			NULL,
   ASSN_UID 				int 			NULL,
   ASSN_HAS_LINKED_FIELDS 		bit 			NULL DEFAULT 0,
   ASSN_IS_CONFIRMED 			bit 			NULL DEFAULT 0,
   ASSN_RESPONSE_PENDING 		bit 			NULL DEFAULT 0,
   ASSN_HAS_NOTES 			bit 			NULL DEFAULT 0,
   ASSN_TEAM_STATUS_PENDING 		bit 			NULL DEFAULT 0,
   TASK_UID 				int 			NULL,
   RES_UID 				int 			NULL,
   ASSN_START_DATE 			datetime 		NULL,
   ASSN_FINISH_DATE 			datetime 		NULL,
   ASSN_DELAY 				int 			NULL,
   ASSN_DELAY_FMT 			smallint 		NULL,
   ASSN_LEVELING_DELAY 			int 			NULL,
   ASSN_COST_RATE_TABLE 		smallint 		NULL,
   ASSN_BASE_START 			datetime 		NULL,
   ASSN_BASE_FINISH 			datetime 		NULL,
   ASSN_MATERIAL_RATE_FMT 		smallint 		NULL,
   ASSN_UNITS 				decimal(25,6) 		NULL,
   ASSN_WORK 				decimal(25,6) 		NULL,
   ASSN_ACT_WORK 			decimal(25,6) 		NULL,
   ASSN_REG_WORK 			decimal(25,6) 		NULL,
   ASSN_REM_WORK 			decimal(25,6) 		NULL,
   ASSN_BASE_WORK 			decimal(25,6) 		NULL,
   ASSN_COST 				decimal(25,6) 		NULL,
   ASSN_ACT_COST 			decimal(25,6) 		NULL,
   ASSN_REM_COST 			decimal(25,6) 		NULL,
   ASSN_BASE_COST 			decimal(25,6) 		NULL,
   ASSN_BASE_COST_PER_USE 		decimal(25,6) 		NULL,
   ASSN_OVT_WORK 			decimal(25,6) 		NULL,
   ASSN_ACT_OVT_WORK 			decimal(25,6) 		NULL,
   ASSN_REM_OVT_WORK 			decimal(25,6) 		NULL,
   ASSN_ACT_OVT_COST 			decimal(25,6) 		NULL,
   ASSN_REM_OVT_COST 			decimal(25,6)		NULL,
   ASSN_RTF_NOTES 			image 			NULL,
   ASSN_BOOKING_TYPE 			smallint 		NULL,
   ASSN_ACT_WORK_PROT 			decimal(25,6) 		NULL,
   ASSN_ACT_OVT_WORK_PROT 		decimal(25,6) 		NULL,
   ASSN_CREATION_DATE			datetime		NULL
)
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX I_MSP_ASSIGNMENTS
ON dbo.MSP_ASSIGNMENTS (PROJ_ID, ASSN_UID) WITH FILLFACTOR = 40 
GO

--**************************************************************************************************
--*		MSP_ATTRIBUTE_STRINGS
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_ATTRIBUTE_STRINGS')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_ATTRIBUTE_STRINGS
GO

CREATE TABLE dbo.MSP_ATTRIBUTE_STRINGS
(
   RESERVED_DATA 			char(1) 		NULL,
   PROJ_ID 				int 			NULL,
   AS_ID 				int 			NULL,
   AS_POSITION 				int 			NULL,
   AS_VALUE 				varchar(255) 		NULL 
)
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX I_MSP_ATTRIBUTE_STRINGS
ON dbo.MSP_ATTRIBUTE_STRINGS (PROJ_ID, AS_ID, AS_POSITION) WITH FILLFACTOR = 40 
GO


--**************************************************************************************************
--*		MSP_AVAILABILITY
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_AVAILABILITY')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_AVAILABILITY
GO


CREATE TABLE dbo.MSP_AVAILABILITY 
(
   RESERVED_DATA 			char(1) 		NULL,
   PROJ_ID 				int 			NULL,
   AVAIL_UID 				int 			NULL,
   RES_UID 				int 			NULL,
   AVAIL_FROM 				datetime 		NULL,
   AVAIL_TO 				datetime 		NULL,
   AVAIL_UNITS 				decimal(25,6) 		NULL 
)
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX I_MSP_AVAILABILITY
ON MSP_AVAILABILITY (PROJ_ID, AVAIL_UID) WITH FILLFACTOR = 40 
-- ON [PRIMARY]
GO


--**************************************************************************************************
--*		MSP_CALENDAR_DATA
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_CALENDAR_DATA')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_CALENDAR_DATA
GO


CREATE TABLE dbo.MSP_CALENDAR_DATA 
(
   RESERVED_DATA 			char(1) 		NULL,
   PROJ_ID 				int 			NULL,
   CD_UID 				int 			NULL,
   CAL_UID 				int 			NULL,
   CD_DAY_OR_EXCEPTION 			smallint 		NULL,
   CD_WORKING 				smallint 		NULL,
   CD_FROM_DATE 			datetime 		NULL,
   CD_TO_DATE 				datetime 		NULL,
   CD_FROM_TIME1 			datetime 		NULL,
   CD_TO_TIME1 				datetime 		NULL,
   CD_FROM_TIME2 			datetime 		NULL,
   CD_TO_TIME2 				datetime 		NULL,
   CD_FROM_TIME3 			datetime 		NULL,
   CD_TO_TIME3 				datetime 		NULL,
   CD_FROM_TIME4 			datetime 		NULL,
   CD_TO_TIME4 				datetime 		NULL,
   CD_FROM_TIME5 			datetime 		NULL,
   CD_TO_TIME5 				datetime 		NULL 
)
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX I_MSP_CALENDAR_DATA 
ON MSP_CALENDAR_DATA (PROJ_ID, CD_UID) WITH FILLFACTOR = 40 
-- ON [PRIMARY]
GO


--**************************************************************************************************
--*		MSP_CALENDARS
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_CALENDARS')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_CALENDARS
GO


CREATE TABLE dbo.MSP_CALENDARS
(
   RESERVED_DATA 			char(1) 		NULL,
   PROJ_ID 				int 			NULL,
   CAL_UID 				int 			NULL,
   CAL_BASE_UID 			int 			NULL,
   RES_UID 				int 			NULL,
   CAL_IS_BASE_CAL 			bit 			NULL DEFAULT 0,
   CAL_NAME 				varchar(255) 		NULL,
   CAL_ENTUID 				int
)
-- ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX I_MSP_CALENDARS
ON MSP_CALENDARS (PROJ_ID, CAL_UID) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_CODE_FIELDS
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_CODE_FIELDS')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_CODE_FIELDS
GO


CREATE TABLE dbo.MSP_CODE_FIELDS 
(
   PROJ_ID 				int 			NULL,
   CODE_CATEGORY 			int 			NULL,
   CODE_REF_UID 			int 			NULL,
   CODE_FIELD_ID 			int 			NULL,
   CODE_UID 				int 			NULL 
) 
-- ON [PRIMARY]
GO


CREATE UNIQUE INDEX I_MSP_CODE_FIELDS 
ON MSP_CODE_FIELDS (PROJ_ID, CODE_CATEGORY, CODE_REF_UID, CODE_FIELD_ID) 
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_CONVERSIONS
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_CONVERSIONS')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_CONVERSIONS
GO


CREATE TABLE dbo.MSP_CONVERSIONS 
(
   STRING_TYPE_ID 			int 			NULL,
   CONV_VALUE 				int 			NULL,
   CONV_LANG_ID 			int 			NULL,
   CONV_STRING 				varchar(200)		NULL 
) 
-- ON [PRIMARY]
GO


CREATE UNIQUE INDEX I_MSP_CONVERSIONS 
ON MSP_CONVERSIONS (CONV_VALUE, CONV_LANG_ID, STRING_TYPE_ID) 
-- ON [PRIMARY]
GO


--**************************************************************************************************
--*		MSP_DATE_FIELDS
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_DATE_FIELDS')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_DATE_FIELDS
GO


CREATE TABLE dbo.MSP_DATE_FIELDS 
(
   PROJ_ID 				int 			NULL,
   DATE_CATEGORY 			int 			NULL,
   DATE_REF_UID 			int 			NULL,
   DATE_FIELD_ID 			int 			NULL,
   DATE_VALUE 				datetime 		NULL 
) 
-- ON [PRIMARY]
GO


CREATE UNIQUE INDEX I_MSP_DATE_FIELDS 
ON MSP_DATE_FIELDS (PROJ_ID, DATE_CATEGORY, DATE_REF_UID, DATE_FIELD_ID) 
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_DURATION_FIELDS
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_DURATION_FIELDS')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_DURATION_FIELDS
GO


CREATE TABLE dbo.MSP_DURATION_FIELDS 
(
   PROJ_ID 				int 			NULL,
   DUR_CATEGORY 			int 			NULL,
   DUR_REF_UID 				int 			NULL,
   DUR_FIELD_ID 			int 			NULL,
   DUR_VALUE 				int 			NULL,
   DUR_FMT 				smallint 		NULL 
) 
-- ON [PRIMARY]
GO


CREATE UNIQUE INDEX I_MSP_DURATION_FIELDS 
ON MSP_DURATION_FIELDS (PROJ_ID, DUR_CATEGORY, DUR_REF_UID, DUR_FIELD_ID) 
-- ON [PRIMARY]
GO


--**************************************************************************************************
--*		MSP_FIELD_ATTRIBUTES
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_FIELD_ATTRIBUTES')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_FIELD_ATTRIBUTES
GO


CREATE TABLE dbo.MSP_FIELD_ATTRIBUTES 
(
   RESERVED_DATA 			char(1) 		NULL,
   PROJ_ID 				int 			NULL,
   ATTRIB_FIELD_ID 			int 			NULL,
   ATTRIB_ID 				int 			NULL,
   ATTRIB_VALUE 			int 			NULL,
   AS_ID 				int 			NULL,
   ATTRIB_EXT_EDITED 			bit 			NULL DEFAULT 0 
)
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX I_MSP_FIELD_ATTRIBUTES 
ON MSP_FIELD_ATTRIBUTES (PROJ_ID, ATTRIB_FIELD_ID, ATTRIB_ID) WITH FILLFACTOR = 40 
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_FLAG_FIELDS
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_FLAG_FIELDS')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_FLAG_FIELDS
GO


CREATE TABLE dbo.MSP_FLAG_FIELDS 
(
   PROJ_ID 				int 			NULL,
   FLAG_CATEGORY 			int 			NULL,
   FLAG_REF_UID 			int 			NULL,
   FLAG_FIELD_ID 			int 			NULL,
   FLAG_VALUE 				bit 			NULL DEFAULT 0 
) 
-- ON [PRIMARY]
GO


CREATE UNIQUE INDEX I_MSP_FLAG_FIELDS
ON MSP_FLAG_FIELDS (PROJ_ID, FLAG_CATEGORY, FLAG_REF_UID, FLAG_FIELD_ID) 
-- ON [PRIMARY]
GO


--**************************************************************************************************
--*		MSP_LINKS
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_LINKS')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_LINKS
GO


CREATE TABLE dbo.MSP_LINKS 
(
   RESERVED_DATA 			char(1) 		NULL,
   PROJ_ID 				int 			NULL,
   EXT_EDIT_REF_DATA 			text 			NULL,
   LINK_UID 				int 			NULL,
   LINK_IS_CROSS_PROJ 			bit 			NULL DEFAULT 0,
   LINK_PRED_UID 			int 			NULL,
   LINK_SUCC_UID 			int 			NULL,
   LINK_TYPE 				smallint 		NULL,
   LINK_LAG_FMT 			smallint 		NULL,
   LINK_LAG 				int 			NULL 
)
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX I_MSP_LINKS 
ON MSP_LINKS (PROJ_ID, LINK_UID) WITH  FILLFACTOR = 40 
-- ON [PRIMARY]
GO


--**************************************************************************************************
--*		MSP_NUMBER_FIELDS
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_NUMBER_FIELDS')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_NUMBER_FIELDS
GO


CREATE TABLE dbo.MSP_NUMBER_FIELDS 
(
   PROJ_ID 				int 			NULL,
   NUM_CATEGORY 			int 			NULL,
   NUM_REF_UID 				int 			NULL,
   NUM_FIELD_ID 			int 			NULL,
   NUM_VALUE 				decimal(25,6) 		NULL 
) 
-- ON [PRIMARY]
GO


CREATE UNIQUE INDEX I_MSP_NUMBER_FIELDS 
ON MSP_NUMBER_FIELDS (PROJ_ID, NUM_CATEGORY, NUM_REF_UID, NUM_FIELD_ID) 
-- ON [PRIMARY]
GO



--**************************************************************************************************
--*		MSP_OUTLINE_CODES
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_OUTLINE_CODES')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_OUTLINE_CODES
GO


CREATE TABLE dbo.MSP_OUTLINE_CODES 
(
   RESERVED_DATA 			char(1) 		NULL,
   PROJ_ID 				int 			NULL,
   CODE_UID 				int 			NULL,
   OC_PARENT 				int 			NULL,
   OC_FIELD_ID 				int 			NULL,
   OC_NAME 				text 			NULL,
   OC_DESCRIPTION 			text 			NULL,
   OC_CACHED_IS_VALID 			bit 			NOT NULL DEFAULT 0,
   OC_CACHED_FULL_NAME 			text
)
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX I_MSP_OUTLINE_CODES
ON MSP_OUTLINE_CODES (PROJ_ID, CODE_UID) WITH FILLFACTOR = 40 
-- ON [PRIMARY]
GO


--**************************************************************************************************
--*		MSP_PROJ_SECURITY
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_PROJ_SECURITY')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_PROJ_SECURITY
GO


CREATE TABLE dbo.MSP_PROJ_SECURITY 
(
   PROJ_ID 				int 			NOT NULL DEFAULT 0,
   SEC_SPID 				smallint 		NOT NULL DEFAULT 0,
   SEC_SPIDDATESTAMP 			datetime 		NOT NULL DEFAULT GetDate(),
   SEC_READCOUNT 			tinyint 		NOT NULL DEFAULT 0,
   SEC_WRITECOUNT 			tinyint 		NOT NULL DEFAULT 0
)
-- ON [PRIMARY]
GO


CREATE UNIQUE INDEX I_MSP_PROJ_SECURITY 
ON MSP_PROJ_SECURITY (SEC_SPID, PROJ_ID) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_PROJECTS
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_PROJECTS')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_PROJECTS
GO


CREATE TABLE dbo.MSP_PROJECTS 
(
   PROJ_ID 				int 			NULL,
   PROJ_NAME 				varchar(255) 		NULL,
   PROJ_PROP_AUTHOR 			varchar(255) 		NULL,
   PROJ_OPT_CALC_ACT_COSTS 		bit 			NULL DEFAULT 0,
   PROJ_PROP_CATEGORY 			varchar(255) 		NULL,
   PROJ_PROP_COMPANY 			varchar(255) 		NULL,
   PROJ_OPT_CRITICAL_SLACK_LIMIT 	int 			NULL,
   PROJ_OPT_CURRENCY_DIGITS 		smallint 		NULL,
   PROJ_OPT_CURRENCY_POSITION 		smallint 		NULL,
   PROJ_OPT_CURRENCY_SYMBOL 		varchar(20) 		NULL,
   PROJ_OPT_NEW_ARE_EFFORT_DRIVEN 	bit 			NULL DEFAULT 0,
   PROJ_INFO_CURRENT_DATE 		datetime 		NULL,
   PROJ_OPT_DEF_FINISH_TIME 		datetime 		NULL,
   PROJ_OPT_DEF_FIX_COST_ACCRUAL 	smallint 		NULL,
   PROJ_OPT_MINUTES_PER_DAY 		int 			NULL,
   PROJ_OPT_MINUTES_PER_WEEK 		int 			NULL ,
   PROJ_OPT_DEF_OVT_RATE 		decimal(25,6) 		NULL,
   PROJ_OPT_DEF_STD_RATE 		decimal(25,6) 		NULL,
   PROJ_OPT_DEF_START_TIME 		datetime 		NULL,
   PROJ_OPT_DEF_TASK_TYPE 		smallint 		NULL,
   PROJ_OPT_DUR_ENTRY_FMT 		smallint 		NULL,
   PROJ_INFO_FINISH_DATE 		datetime 		NULL,
   PROJ_OPT_HONOR_CONSTRAINTS 		bit 			NULL DEFAULT 0,
   PROJ_PROP_KEYWORDS 			varchar(255) 		NULL,
   PROJ_LAST_SAVED 			datetime 		NULL,
   PROJ_PROP_MANAGER 			varchar(255) 		NULL,
   PROJ_OPT_MULT_CRITICAL_PATHS 	bit 			NULL DEFAULT 0,
   PROJ_CREATION_DATE 			datetime 		NULL,
   PROJ_POOL_ATTACHED_TO 		varchar(255) 		NULL,
   PROJ_INFO_CAL_NAME 			varchar(255) 		NULL,
   PROJ_IS_RES_POOL			bit	 		NULL DEFAULT 0,
   PROJ_INFO_SCHED_FROM 		smallint 		NULL,
   PROJ_OPT_SPLIT_IN_PROGRESS 		bit 			NULL DEFAULT 0,
   PROJ_OPT_SPREAD_ACT_COSTS 		bit 			NULL DEFAULT 0,
   PROJ_OPT_SPREAD_PCT_COMP 		bit 			NULL DEFAULT 0,
   PROJ_INFO_START_DATE 		datetime 		NULL,
   PROJ_INFO_STATUS_DATE 		datetime 		NULL,
   PROJ_PROP_SUBJECT 			varchar(255) 		NULL,
   PROJ_PROP_TITLE 			varchar(255) 		NULL,
   PROJ_OPT_TASK_UPDATES_RES 		bit 			NULL DEFAULT 0,
   PROJ_OPT_WORK_ENTRY_FMT 		smallint 		NULL,
   PROJ_OPT_CALC_SUB_AS_SUMMARY 	bit 			NULL DEFAULT 0,
   PROJ_OPT_WEEK_START_DAY 		smallint 		NULL,
   PROJ_OPT_FY_START_MONTH 		smallint 		NULL,
   PROJ_OPT_FY_USE_START_YR 		bit 			NULL DEFAULT 0,
   PROJ_OPT_DAYS_PER_MONTH 		smallint 		NULL,
   PROJ_OPT_NEW_TASK_EST 		bit 			NULL DEFAULT 0,
   PROJ_OPT_SHOW_EST_DUR 		bit 			NULL DEFAULT 0,
   PROJ_OPT_EXPAND_TIMEPHASED 		bit 			NULL DEFAULT 0,
   PROJ_EXT_EDITED_DATE 		bit 			NULL DEFAULT 0,
   PROJ_EXT_EDITED_DUR 			bit 			NULL DEFAULT 0,
   PROJ_EXT_EDITED_NUM 			bit 			NULL DEFAULT 0,
   PROJ_EXT_EDITED_FLAG 		bit 			NULL DEFAULT 0,
   PROJ_EXT_EDITED_CODE 		bit 			NULL DEFAULT 0,
   PROJ_EXT_EDITED_TEXT 		bit 			NULL DEFAULT 0,
   PROJ_EXT_EDITED 			bit 			NULL DEFAULT 0,
   PROJ_DATA_SOURCE 			varchar(128) 		NULL,
   PROJ_READ_ONLY 			varchar(10) 		NULL,
   PROJ_READ_WRITE 			varchar(4) 		NULL,
   PROJ_READ_COUNT 			varchar(10) 		NULL,
   PROJ_LOCKED 				varchar(4) 		NULL,
   PROJ_MACHINE_ID 			varchar(100) 		NULL,
   RESERVED_BINARY_DATA 		image 			NULL,
   PROJ_TYPE 				int 			NOT NULL DEFAULT 0,
   PROJ_CHECKEDOUT 			int 			NOT NULL DEFAULT 0,
   PROJ_CHECKOUTBY 			varchar(255)		NULL,
   PROJ_CHECKOUTDATE 			datetime		NULL,
   PROJ_PROJECT 			varchar(203) 		NULL,
   PROJ_VERSION 			varchar(50) 		NULL,
   PROJ_CREATION_DATE_EX   		datetime        	NULL,
   PROJ_ACTUALS_SYNCH			tinyint			NULL,
   PROJ_ADMINPROJECT			tinyint			NULL,
   PROJ_ENT_LIST_SEPARATOR              varchar(2)              NULL,
   PROJ_IGNORE_FRONT_END                bit                     NULL DEFAULT 0
) 
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX I_MSP_PROJECTS
ON MSP_PROJECTS (PROJ_ID) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO


CREATE UNIQUE INDEX I_PROJ_NAME
ON MSP_PROJECTS (PROJ_NAME) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO


CREATE INDEX I_MSP_PROJECTS_PROJ_TYPE
ON MSP_PROJECTS (PROJ_TYPE) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_RES_GLOBAL_BINARY
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_RES_GLOBAL_BINARY')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_RES_GLOBAL_BINARY
GO


CREATE TABLE dbo.MSP_RES_GLOBAL_BINARY
( 
   PROJ_ID 				int,
   GLB_CATEGORY 			int,
   GLB_REF_UID 				int,
   GLB_REF_ENT_PROJ_ID 			int,
   GLB_BINARY_DATA 			image
) 
-- ON [PRIMARY]
GO


CREATE CLUSTERED INDEX I_MSP_RES_GLOBAL_BINARY
ON dbo.MSP_RES_GLOBAL_BINARY (GLB_REF_ENT_PROJ_ID, GLB_REF_UID) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_RES_SECURITY
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_RES_SECURITY')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_RES_SECURITY
GO


CREATE TABLE dbo.MSP_RES_SECURITY 
(
   ERUID 				int 			NOT NULL DEFAULT 0,
   SEC_SPID 				smallint 		NOT NULL DEFAULT 0,
   SEC_SPIDDATESTAMP 			datetime 		NOT NULL DEFAULT GetDate(),
   SEC_READCOUNT 			tinyint 		NOT NULL DEFAULT 0,
   SEC_WRITECOUNT 			tinyint 		NOT NULL DEFAULT 0 
)
-- ON [PRIMARY]
GO


CREATE UNIQUE INDEX I_MSP_RES_SECURITY 
ON MSP_RES_SECURITY (SEC_SPID, ERUID) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_RESOURCE_RATES
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_RESOURCE_RATES')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_RESOURCE_RATES
GO


CREATE TABLE dbo.MSP_RESOURCE_RATES 
(
   RESERVED_DATA 			char(1) 		NULL,
   PROJ_ID 				int 			NULL,
   RR_UID 				int 			NULL,
   RES_UID 				int 			NULL,
   RR_RATE_TABLE 			smallint 		NULL,
   RR_FROM_DATE 			datetime 		NULL,
   RR_TO_DATE 				datetime 		NULL,
   RR_STD_RATE 				decimal(25,6) 		NULL,
   RR_STD_RATE_FMT 			smallint 		NULL,
   RR_OVT_RATE 				decimal(25,6) 		NULL,
   RR_OVT_RATE_FMT 			smallint 		NULL,
   RR_PER_USE_COST 			decimal(25,6) 		NULL 
)
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX I_MSP_RESOURCE_RATES
ON MSP_RESOURCE_RATES (PROJ_ID, RR_UID) WITH FILLFACTOR = 40 
-- ON [PRIMARY]
GO


--**************************************************************************************************
--*		MSP_RESOURCES
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_RESOURCES')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_RESOURCES
GO


CREATE TABLE dbo.MSP_RESOURCES 
(
   RESERVED_DATA 			char(1) 		NULL,
   PROJ_ID 				int 			NULL,
   RES_ACWP 				decimal(25,6) 		NULL,
   RES_BCWP 				decimal(25,6) 		NULL,
   RES_BCWS 				decimal(25,6) 		NULL,
   RES_NUM_OBJECTS 			int 			NULL,
   EXT_EDIT_REF_DATA 			text 			NULL,
   RES_UID 				int 			NULL,
   RES_ID 				int 			NULL,
   RES_HAS_LINKED_FIELDS 		bit 			NULL DEFAULT 0,
   RES_IS_OVERALLOCATED 		bit 			NULL DEFAULT 0,
   RES_TYPE 				bit 			NULL DEFAULT 0,
   RES_HAS_NOTES 			bit 			NULL DEFAULT 0,
   RES_CAN_LEVEL 			bit 			NULL DEFAULT 0,
   RES_STD_RATE_FMT 			smallint 		NULL,
   RES_OVT_RATE_FMT 			smallint 		NULL,
   RES_ACCRUE_AT 			smallint 		NULL,
   RES_WORKGROUP_MESSAGING		smallint 		NULL,
   RES_CAL_UID 				int 			NULL,
   RES_AVAIL_FROM 			datetime 		NULL,
   RES_AVAIL_TO 			datetime 		NULL,
   RES_STD_RATE 			decimal(25,6) 		NULL,
   RES_OVT_RATE 			decimal(25,6) 		NULL,
   RES_MAX_UNITS 			decimal(25,6) 		NULL,
   RES_WORK 				decimal(25,6) 		NULL,
   RES_ACT_WORK 			decimal(25,6) 		NULL,
   RES_BASE_WORK 			decimal(25,6) 		NULL,
   RES_OVT_WORK 			decimal(25,6) 		NULL,
   RES_COST_PER_USE 			decimal(25,6) 		NULL,
   RES_REM_WORK 			decimal(25,6) 		NULL,
   RES_REG_WORK 			decimal(25,6) 		NULL,
   RES_ACT_OVT_WORK 			decimal(25,6) 		NULL,
   RES_REM_OVT_WORK 			decimal(25,6) 		NULL,
   RES_PEAK 				decimal(25,6) 		NULL,
   RES_ACT_COST 			decimal(25,6) 		NULL,
   RES_COST 				decimal(25,6) 		NULL,
   RES_BASE_COST 			decimal(25,6) 		NULL,
   RES_REM_COST 			decimal(25,6) 		NULL,
   RES_OVT_COST 			decimal(25,6) 		NULL,
   RES_ACT_OVT_COST 			decimal(25,6) 		NULL,
   RES_REM_OVT_COST 			decimal(25,6) 		NULL,
   RES_NAME 				varchar(255) 		NULL,
   RES_INITIALS 			varchar(255) 		NULL,
   RES_PHONETICS 			text 			NULL,
   RES_MATERIAL_LABEL 			text 			NULL,
   RES_RTF_NOTES 			image 			NULL,
   RES_RTYPE 				smallint		NULL,
   RES_CHECKEDOUT 			int 			NOT NULL DEFAULT 0,
   RES_CHECKOUTBY 			varchar(255)		NULL,
   RES_CHECKOUTDATE 			datetime		NULL,
   RES_ENTERPRISE_TIMESTAMP 		datetime		NULL,
   RES_EUID 				int			NULL,
   RES_BOOKING_TYPE 			smallint 		NULL,
   RES_AD_GUID 				uniqueidentifier 	NULL,
   RES_ACT_WORK_PROT 			decimal(25,6) 		NULL,
   RES_ACT_OVT_WORK_PROT 		decimal(25,6) 		NULL,
   RES_CREATION_DATE			datetime		NULL
)
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX I_MSP_RESOURCES 
ON MSP_RESOURCES (PROJ_ID, RES_UID) WITH FILLFACTOR = 40 
-- ON [PRIMARY]
GO


--**************************************************************************************************
--*		MSP_STRING_TYPES
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_STRING_TYPES')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_STRING_TYPES
GO


CREATE TABLE dbo.MSP_STRING_TYPES 
(
   STRING_TYPE_ID 			int 			NULL,
   STRING_LANG_ID 			int 			NULL,
   STRING_TYPE 				varchar(200) 		NULL 
) 
-- ON [PRIMARY]
GO


CREATE UNIQUE INDEX I_MSP_STRING_TYPES
ON MSP_STRING_TYPES (STRING_TYPE_ID, STRING_LANG_ID) 
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_TASKS
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_TASKS')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_TASKS
GO


CREATE TABLE dbo.MSP_TASKS 
(
   RESERVED_DATA 			char(1) 		NULL,
   PROJ_ID 				int 			NULL,
   TASK_ACWP 				decimal(25,6) 		NULL,
   TASK_BCWP 				decimal(25,6) 		NULL,
   TASK_BCWS 				decimal(25,6) 		NULL,
   TASK_DUR_VAR 			int 			NULL,
   TASK_FINISH_VAR 			int 			NULL,
   TASK_OUTLINE_NUM 			varchar(255) 		NULL,
   TASK_START_VAR 			int 			NULL,
   TASK_IS_OVERALLOCATED 		bit 			NULL DEFAULT 0,
   TASK_OVT_WORK 			decimal(25,6) 		NULL,
   TASK_VAC 				decimal(25,6) 		NULL,
   TASK_REG_WORK 			decimal(25,6) 		NULL,
   TASK_NUM_OBJECTS 			int 			NULL,
   TASK_TOTAL_SLACK 			int 			NULL,
   EXT_EDIT_REF_DATA 			text 			NULL,
   TASK_UID 				int 			NULL,
   TASK_ID 				int 			NULL,
   TASK_HAS_LINKED_FIELDS 		bit 			NULL DEFAULT 0,
   TASK_IS_MILESTONE 			bit 			NULL DEFAULT 0,
   TASK_IS_CRITICAL 			bit 			NULL DEFAULT 0,
   TASK_IS_SUMMARY 			bit 			NULL DEFAULT 0,
   TASK_IS_SUBPROJ 			bit			NULL DEFAULT 0,
   TASK_IS_MARKED 			bit 			NULL DEFAULT 0,
   TASK_IGNORES_RES_CAL 		bit 			NULL DEFAULT 0,
   TASK_IS_ROLLED_UP 			bit 			NULL DEFAULT 0,
   TASK_IS_FROM_FINISH_SUBPROJ 		bit 			NULL DEFAULT 0,
   TASK_BAR_IS_HIDDEN 			bit 			NULL DEFAULT 0,
   TASK_IS_RECURRING 			bit 			NULL DEFAULT 0,
   TASK_IS_RECURRING_SUMMARY 		bit 			NULL DEFAULT 0,
   TASK_IS_EXTERNAL 			bit 			NULL DEFAULT 0,
   TASK_IS_EFFORT_DRIVEN 		bit 			NULL DEFAULT 0,
   TASK_IS_COLLAPSED 			bit 			NULL DEFAULT 0,
   TASK_HAS_NOTES 			bit			NULL DEFAULT 0,
   TASK_IS_READONLY_SUBPROJ 		bit 			NULL DEFAULT 0,
   TASK_LEVELING_CAN_SPLIT 		bit 			NULL DEFAULT 0,
   TASK_LEVELING_ADJUSTS_ASSN 		bit 			NULL DEFAULT 0,
   TASK_DUR_IS_EST 			bit 			NULL DEFAULT 0,
   TASK_EARLY_FINISH 			datetime 		NULL,
   TASK_LATE_START 			datetime 		NULL,
   TASK_STOP_DATE 			datetime 		NULL,
   TASK_RESUME_DATE 			datetime 		NULL,
   TASK_FREE_SLACK 			int 			NULL,
   TASK_OUTLINE_LEVEL 			smallint 		NULL,
   TASK_DUR 				int 			NULL,
   TASK_DUR_FMT 			smallint 		NULL,
   TASK_ACT_DUR 			int 			NULL,
   TASK_REM_DUR 			int 			NULL,
   TASK_BASE_DUR 			int 			NULL,
   TASK_BASE_DUR_FMT 			smallint 		NULL,
   TASK_CONSTRAINT_TYPE 		smallint 		NULL,
   TASK_LEVELING_DELAY 			int 			NULL,
   TASK_LEVELING_DELAY_FMT 		smallint 		NULL,
   TASK_START_DATE 			datetime 		NULL,
   TASK_FINISH_DATE 			datetime 		NULL,
   TASK_ACT_START 			datetime 		NULL,
   TASK_ACT_FINISH 			datetime 		NULL,
   TASK_BASE_START 			datetime 		NULL,
   TASK_BASE_FINISH 			datetime 		NULL,
   TASK_CONSTRAINT_DATE 		datetime 		NULL,
   TASK_PRIORITY 			smallint 		NULL,
   TASK_PCT_COMP 			smallint 		NULL,
   TASK_PCT_WORK_COMP 			smallint 		NULL,
   TASK_TYPE 				smallint 		NULL,
   TASK_FIXED_COST_ACCRUAL 		smallint 		NULL,
   TASK_CREATION_DATE 			datetime 		NULL,
   TASK_PRELEVELED_START 		datetime 		NULL,
   TASK_PRELEVELED_FINISH 		datetime 		NULL,
   TASK_EARLY_START 			datetime 		NULL,
   TASK_LATE_FINISH 			datetime 		NULL,
   TASK_CAL_UID 			int 			NULL,
   TASK_DEADLINE 			datetime 		NULL,
   TASK_WORK 				decimal(25,6) 		NULL,
   TASK_BASE_WORK 			decimal(25,6) 		NULL,
   TASK_ACT_WORK 			decimal(25,6) 		NULL,
   TASK_REM_WORK 			decimal(25,6) 		NULL,
   TASK_COST 				decimal(25,6) 		NULL,
   TASK_FIXED_COST 			decimal(25,6) 		NULL,
   TASK_ACT_COST 			decimal(25,6) 		NULL,
   TASK_REM_COST 			decimal(25,6) 		NULL,
   TASK_BASE_COST 			decimal(25,6) 		NULL,
   TASK_ACT_OVT_WORK 			decimal(25,6) 		NULL,
   TASK_REM_OVT_WORK 			decimal(25,6) 		NULL,
   TASK_OVT_COST 			decimal(25,6) 		NULL,
   TASK_ACT_OVT_COST 			decimal(25,6) 		NULL,
   TASK_REM_OVT_COST 			decimal(25,6) 		NULL,
   TASK_WBS 				text 			NULL ,
   TASK_NAME 				varchar(255) 		NULL,
   TASK_WBS_RIGHTMOST_LEVEL 		text 			NULL,
   TASK_RTF_NOTES 			image 			NULL,
   TASK_PHY_PCT_COMP 			smallint		NULL,
   TASK_EAC 				decimal(25,6) 		NULL,
   TASK_EVMETHOD 			smallint 		NULL,
   TASK_ACT_WORK_PROT 			decimal(25,6) 		NULL,
   TASK_ACT_OVT_WORK_PROT 		decimal(25,6) 		NULL
)
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX I_MSP_TASKS 
ON MSP_TASKS (PROJ_ID, TASK_UID) WITH FILLFACTOR = 40 
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_TEXT_FIELDS
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_TEXT_FIELDS')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_TEXT_FIELDS
GO


CREATE TABLE dbo.MSP_TEXT_FIELDS 
(
   PROJ_ID 				int 			NULL,
   TEXT_CATEGORY 			int 			NULL,
   TEXT_REF_UID 			int 			NULL,
   TEXT_FIELD_ID 			int 			NULL,
   TEXT_VALUE 				varchar(255) 		NULL 
) 
-- ON [PRIMARY]
GO


CREATE UNIQUE INDEX I_MSP_TEXT_FIELDS 
ON MSP_TEXT_FIELDS (PROJ_ID, TEXT_CATEGORY, TEXT_REF_UID, TEXT_FIELD_ID) 
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_TIMEPHASED_DATA
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_TIMEPHASED_DATA')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_TIMEPHASED_DATA
GO

CREATE TABLE dbo.MSP_TIMEPHASED_DATA 
(
   RESERVED_DATA 			char(1) 		NULL,
   PROJ_ID 				int 			NULL,
   TD_UID 				int 			NULL,
   TD_FIELD_ID 				smallint 		NULL,
   TD_CATEGORY 				smallint 		NULL,
   TD_REF_UID 				int 			NULL,
   TD_START 				datetime 		NULL,
   TD_FINISH 				datetime 		NULL,
   TD_UNITS 				smallint 		NULL,
   TD_VALUE1 				decimal(25,6) 		NULL,
   TD_VALUE2 				decimal(25,6) 		NULL,
   TD_VALUE3 				decimal(25,6) 		NULL,
   TD_VALUE4 				decimal(25,6) 		NULL,
   TD_VALUE5 				decimal(25,6) 		NULL,
   TD_VALUE6 				decimal(25,6) 		NULL,
   TD_VALUE7 				decimal(25,6) 		NULL,
   TD_EXT_EDITED 			bit 			NULL DEFAULT 0 
)
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX I_MSP_TIMEPHASED_DATA 
ON MSP_TIMEPHASED_DATA (PROJ_ID, TD_UID) WITH FILLFACTOR = 40 
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_ASSIGNMENT_BASELINES
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_ASSIGNMENT_BASELINES')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_ASSIGNMENT_BASELINES
GO


CREATE TABLE dbo.MSP_ASSIGNMENT_BASELINES 
(
   PROJ_ID 				int 			NULL,
   ASSN_UID 				int 			NULL,
   AB_REF_ENT_PROJ_ID 			int 			NULL,
   AB_BASE_NUM 				smallint		NULL,
   AB_BASE_START 			datetime 		NULL,
   AB_BASE_FINISH 			datetime 		NULL,
   AB_BASE_WORK 			decimal(25,6) 		NULL,
   AB_BASE_COST 			decimal(25,6) 		NULL,
   AB_BASE_COST_PER_USE 		decimal(25,6) 		NULL 
)
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX I_MSP_ASSIGNMENT_BASELINES 
ON MSP_ASSIGNMENT_BASELINES (PROJ_ID, ASSN_UID, AB_REF_ENT_PROJ_ID, AB_BASE_NUM)
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_RESOURCE_BASELINES
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_RESOURCE_BASELINES')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_RESOURCE_BASELINES
GO


CREATE TABLE dbo.MSP_RESOURCE_BASELINES 
(
   PROJ_ID				int 			NULL,
   RES_UID 				int 			NULL,
   RB_BASE_NUM 				smallint 		NULL,
   RB_BASE_WORK 			decimal(25,6) 		NULL,
   RB_BASE_COST 			decimal(25,6) 		NULL 
)
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX I_MSP_RESOURCE_BASELINES 
ON MSP_RESOURCE_BASELINES (PROJ_ID, RES_UID, RB_BASE_NUM)
-- ON [PRIMARY]
GO


--**************************************************************************************************
--*		MSP_TASK_BASELINES
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_TASK_BASELINES')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_TASK_BASELINES
GO


CREATE TABLE dbo.MSP_TASK_BASELINES 
(
   PROJ_ID 				int 			NULL,
   TASK_UID 				int 			NULL,
   TB_BASE_NUM 				smallint 		NULL,
   TB_BASE_DUR 				int 			NULL,
   TB_BASE_DUR_FMT 			smallint 		NULL,
   TB_BASE_START 			datetime 		NULL,
   TB_BASE_FINISH 			datetime 		NULL,
   TB_BASE_WORK 			decimal(25,6) 		NULL,
   TB_BASE_COST 			decimal(25,6) 		NULL 
)
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX I_MSP_TASK_BASELINES 
ON MSP_TASK_BASELINES (PROJ_ID, TASK_UID, TB_BASE_NUM)
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_MV_FIELDS
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_MV_FIELDS')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_MV_FIELDS
GO
 

CREATE TABLE dbo.MSP_MV_FIELDS 
( 
   RESERVED_DATA 			char(1)			NULL, 
   PROJ_ID 				int			NULL, 
   CODE_MV_UID 				int			NULL, 
   CODE_CATEGORY 			int			NULL, 
   CODE_REF_UID 			int			NULL, 
   CODE_FIELD_ID 			int			NULL, 
   CODE_UID 				int			NULL 
) 
-- ON [PRIMARY]
GO
 

CREATE CLUSTERED INDEX I_MSP_MV_FIELDS 
ON dbo.MSP_MV_FIELDS (PROJ_ID, CODE_MV_UID) WITH FILLFACTOR = 40
-- ON [PRIMARY]

--**************************************************************************************************
--*		MSP_RESGLOBAL_SECURITY
--**************************************************************************************************

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_RESGLOBAL_SECURITY')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.MSP_RESGLOBAL_SECURITY
GO
 

CREATE TABLE dbo.MSP_RESGLOBAL_SECURITY
( 
   SEC_RES_UID				int			NULL,
   SEC_CAL_UID				int			NULL
) 
-- ON [PRIMARY]
GO

 

-- End of script