--****************************************************************************************************
--*
--*	This script will create the SQL Server tables needed for Project Web Access.
--*
--*	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_WEB_ADMIN
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_ADMIN
(
	-- Add Fake PK
	WADMIN_IDFAKE		   			int IDENTITY 		NOT NULL CONSTRAINT PK_MSP_WEB_ADMIN_WADMIN_IDFAKE PRIMARY KEY,
	WADMIN_AUTHENTICATION_TYPE 			int 			NOT NULL DEFAULT 3,
	WADMIN_NEW_ACCOUNT_PRIVILEGE 			int 			NOT NULL DEFAULT 3,
	WADMIN_IS_DELEGATION_ALLOWED 			tinyint 		NOT NULL DEFAULT 1 CHECK ((WADMIN_IS_DELEGATION_ALLOWED >= 0) AND (WADMIN_IS_DELEGATION_ALLOWED <= 1)),
	WADMIN_AUTH_REQUIRED_FOR_PUBLISH 		tinyint 		NOT NULL DEFAULT 0 CHECK ((WADMIN_AUTH_REQUIRED_FOR_PUBLISH >= 0) AND (WADMIN_AUTH_REQUIRED_FOR_PUBLISH <= 1)),
	WADMIN_WEEK_STARTS_ON 				int 			NOT NULL DEFAULT 1,
	WADMIN_MIN_PASSWORD_LENGTH 			int 			NOT NULL DEFAULT 8,
	WADMIN_NTFY_FROM_EMAIL 				nvarchar(100) 		NOT NULL,
	WADMIN_NTFY_EMAIL_TRAILER 			nvarchar(255) 		NULL,
	WADMIN_ORG_EMAIL_ADDRESS 			nvarchar(100) 		NOT NULL,
	WADMIN_DEFAULT_LANGUAGE 			int 			NOT NULL,
	WADMIN_DEFAULT_TRACKING_METHOD 			int 			NOT NULL DEFAULT 2 CHECK ((WADMIN_DEFAULT_TRACKING_METHOD >= 0) AND (WADMIN_DEFAULT_TRACKING_METHOD <= 4)),
	WADMIN_CAN_PUBLISH_CONSOLIDATED_PROJECTS 	tinyint 		NOT NULL DEFAULT 0 CHECK ((WADMIN_CAN_PUBLISH_CONSOLIDATED_PROJECTS >= 0) AND (WADMIN_CAN_PUBLISH_CONSOLIDATED_PROJECTS <= 1)),
	WADMIN_IS_TRACKING_METHOD_LOCKED 		tinyint 		NOT NULL DEFAULT 0 CHECK ((WADMIN_IS_TRACKING_METHOD_LOCKED >= 0) AND (WADMIN_IS_TRACKING_METHOD_LOCKED <= 1)),
	WADMIN_TRANS_HISTORY_DAYS 			int 			NOT NULL DEFAULT 30,
	WADMIN_TIMESHEET_SPAN 				tinyint 		NOT NULL  DEFAULT 1,
	WADMIN_WEEKLY_TIMESHEET_NUM_WEEKS 		tinyint 		NOT NULL DEFAULT 2,
	WADMIN_MONTHLY_REPORTS_PER_MONTH 		tinyint 		NOT NULL DEFAULT 2,
	WADMIN_MONTHLY_1PRD_1ST_START 			tinyint 		NOT NULL DEFAULT 1,
	WADMIN_MONTHLY_2PRDS_1ST_START 			tinyint 		NOT NULL  DEFAULT 1,
	WADMIN_MONTHLY_2PRDS_1ST_END 			tinyint 		NOT NULL DEFAULT 15,
	WADMIN_MONTHLY_3PRDS_1ST_START 			tinyint 		NOT NULL DEFAULT 1,
	WADMIN_MONTHLY_3PRDS_1ST_END 			tinyint 		NOT NULL DEFAULT 10,
	WADMIN_MONTHLY_3PRDS_2ND_END 			tinyint 		NOT NULL DEFAULT 20,
	WADMIN_MAX_HOUR_PER_DAY 			decimal(25,6) 		NOT NULL  DEFAULT 8,
	WADMIN_LOOKAHEAD 				int 			NOT NULL DEFAULT 10,
	WADMIN_TIMEPERIOD_GRANULARITY 			tinyint 		NOT NULL DEFAULT 2,
	WADMIN_LICENSES 				int 			NOT NULL DEFAULT 10,
	WADMIN_AUTO_CREATE_SUBWEBS 			tinyint 		NOT NULL DEFAULT 1,
	WADMIN_AUTO_ADD_USER_TO_SUBWEB 			tinyint 		NOT NULL DEFAULT 1,
	WADMIN_AUTO_ADD_USER_AS_ADV_AUTHOR_TO_SUBWEB 	tinyint 		NOT NULL DEFAULT 1,
	WADMIN_CURRENT_STS_SERVER_ID 			int 			NOT NULL DEFAULT 0,
	WADMIN_ENABLE_ENTERPRISE 			tinyint 		NOT NULL DEFAULT 1,
	WADMIN_DISPLAY_MASTER_IN_ENTERPRISE 		tinyint 		NOT NULL DEFAULT 0,
	WADMIN_ALLOW_LOCAL_BASE_CALS_IN_ENTERPRISE 	tinyint 		NOT NULL DEFAULT 0,
	WADMIN_NPE_LAST_RUN 				datetime 		NULL,
	WADMIN_NPE_NEXT_RUN 				datetime 		NULL,
	WADMIN_NPE_SCHEDULED_TIME 			int 			NULL,
	WADMIN_PUBDOCS_STS_SUBWEB_NAME 			nvarchar(128) 		NOT NULL DEFAULT (N'MS_ProjectServer_PublicDocuments'),
	WADMIN_ENFORCE_CURRENCY 			tinyint 		NOT NULL DEFAULT 1 CHECK ((WADMIN_ENFORCE_CURRENCY >= 0) AND (WADMIN_ENFORCE_CURRENCY <= 1)),
	WADMIN_LAST_STS_ADMIN_SYNCH_TIME 		datetime 		NULL,
	WADMIN_SMTP_SERVER_NAME 			nvarchar(256) 		NULL,
	WADMIN_SMTP_SERVER_PORT 			int 			NOT NULL DEFAULT 25,
	WADMIN_INTRANET_SERVER_URL 			nvarchar(255) 		NOT NULL DEFAULT (N''),
	WADMIN_EXTRANET_SERVER_URL 			nvarchar(255) 		NOT NULL DEFAULT (N''),
	WADMIN_ONLY_PRO_PUBLISH 			tinyint 		NOT NULL DEFAULT 0,
	WADMIN_PROTECT_ACTUALS 				tinyint 		NOT NULL DEFAULT 0,
	WADMIN_STS_TEMPLATE_LCID 			int 			NULL,
   	WADMIN_STS_TEMPLATE_ID 				nvarchar(50) 		NULL,
   	WADMIN_STS_PRIMARY_OWNER_EMAIL 			nvarchar(100) 		NULL,
   	WADMIN_STS_SECONDARY_OWNER_NAME			nvarchar(255) 		NULL,
   	WADMIN_STS_SECONDARY_OWNER_EMAIL 		nvarchar(100) 		NULL,
   	WADMIN_STS_PORTAL_URL 				nvarchar(1024) 		NULL,
   	WADMIN_STS_PORTAL_NAME 				nvarchar(255) 		NULL,
	WADMIN_BUILD_TEAM_BY_RBS        		tinyint   		NOT NULL Default 1,
  	WADMIN_VERSION_MAJOR          			tinyint         	NULL,
  	WADMIN_VERSION_MINOR          			tinyint         	NULL,
  	WADMIN_VERSION_BUILD          			int             	NULL,
  	WADMIN_VERSION_REVISION       			tinyint         	NULL,
	WADMIN_IS_HOSTED_ORG				tinyint 		NOT NULL DEFAULT 0
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_ADMIN_CUBE
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_ADMIN_CUBE
(
	WADMIN_CUBE_ID 					int IDENTITY (1, 1) 	NOT NULL CONSTRAINT PK_MSP_WEB_ADMIN_CUBE_WADMIN_CUBE_ID PRIMARY KEY CLUSTERED,
	WADMIN_CUBE_BUILD 				tinyint 		NOT NULL CHECK ((WADMIN_CUBE_BUILD >= 0) AND (WADMIN_CUBE_BUILD <= 1)),
	WADMIN_CUBE_OLAP_SERVER 			nvarchar(100) 		NOT NULL,
	WADMIN_CUBE_NAME 				nvarchar(100) 		NOT NULL,
	WADMIN_CUBE_DESCRIPTION 			nvarchar(255) 		NULL,
	WADMIN_CUBE_DATE_RANGE 				int 			NOT NULL,
	WADMIN_CUBE_DATE_RANGE_NEXT_NUM 		int 			NOT NULL,
	WADMIN_CUBE_DATE_RANGE_NEXT_UNIT 		int 			NOT NULL,
	WADMIN_CUBE_DATE_RANGE_PAST_NUM 		int 			NOT NULL,
	WADMIN_CUBE_DATE_RANGE_PAST_UNIT 		int 			NOT NULL,
	WADMIN_CUBE_DATE_RANGE_FROM 			datetime 		NOT NULL,
	WADMIN_CUBE_DATE_RANGE_TO 			datetime 		NOT NULL,
	WADMIN_CUBE_LOG_STATUS_TIMESTAMP 		datetime 		NULL,
	WADMIN_CUBE_LOG_STATUS 				int 			NULL,
	WADMIN_CUBE_LOG_STATUS_TEXT 			nvarchar(255) 		NULL,
	WADMIN_RES_DATE_RANGE 				int 			NOT NULL,
	WADMIN_RES_DATE_RANGE_NEXT_NUM 			int 			NOT NULL,
	WADMIN_RES_DATE_RANGE_NEXT_UNIT 		int 			NOT NULL,
	WADMIN_RES_DATE_RANGE_PAST_NUM 			int 			NOT NULL,
	WADMIN_RES_DATE_RANGE_PAST_UNIT 		int 			NOT NULL,
	WADMIN_RES_DATE_RANGE_FROM 			datetime 		NOT NULL,
	WADMIN_RES_DATE_RANGE_TO 			datetime 		NOT NULL,
	WADMIN_AUTO_LAST_RUN 				datetime 		NOT NULL,
	WADMIN_UPDATE_RECUR 				tinyint 		NOT NULL CHECK ((WADMIN_UPDATE_RECUR >= 0) AND (WADMIN_UPDATE_RECUR <= 1)),
	WADMIN_UPDATE_FREQ_EVERY 			int 			NOT NULL,
	WADMIN_UPDATE_FREQ_UNIT 			int 			NOT NULL,
	WADMIN_UPDATE_FREQ_START_DATE 			datetime 		NOT NULL,
	WADMIN_UPDATE_FREQ_START_TIME 			int 			NOT NULL,
	WADMIN_UPDATE_RES 				tinyint 		NOT NULL CHECK ((WADMIN_UPDATE_RES >= 0) AND (WADMIN_UPDATE_RES <= 1)),
	WADMIN_UPDATE_CUBE 				tinyint 		NOT NULL CHECK ((WADMIN_UPDATE_CUBE >= 0) AND (WADMIN_UPDATE_CUBE <= 1)),
	WADMIN_CUBE_EXTRANET_OLAP_SERVER            	nvarchar(255)           NULL,
        WADMIN_CUBE_BUILD_GUID         			uniqueidentifier        NULL
)
-- ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_ADMIN_LINKS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_ADMIN_LINKS
(
	WLINKS_ID 					int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_ADMIN_LINKS_WLINKS_ID PRIMARY KEY NONCLUSTERED,
	WLINKS_HREF 					nvarchar(500) 		NOT NULL,
	WLINKS_NAME 					nvarchar(255) 		NULL,
	WLINKS_HEIGHT 					nvarchar(255) 		NULL,
	WLINKS_TYPE 					int 			NOT NULL DEFAULT 0
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_ASSIGNMENTS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_ASSIGNMENTS
(
 	WASSN_ID 					int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_ASSIGNMENTS_WASSN_ID PRIMARY KEY NONCLUSTERED,
	WRES_ID 					int 			NULL,  		 
	WASSN_PARENT_ID 				int 			NULL, 	
	WPROJ_ID 					int 			NULL, 		
	WRES_ID_MGR 					int 			NULL, 		
	ASSN_UID 					int 			NULL,
	TASK_UID 					int 			NULL,
	TASK_ID 					int 			NULL,
	TASK_UID_SUMMARY 				int 			NULL,
	TASK_NAME 					nvarchar(255) 		NULL,
	ASSN_START_DATE 				datetime 		NULL,
	ASSN_FINISH_DATE 				datetime 		NULL,
	ASSN_WORK 					decimal(25,6) 		NULL DEFAULT 0,
	ASSN_ACT_WORK 					decimal(25, 6) 		NULL DEFAULT 0,
	ASSN_REM_WORK 					decimal(25,6) 		NULL DEFAULT 0,
	WASSN_LAST_WORK 				decimal(25,6) 		NULL DEFAULT 0,
	WASSN_COMMENTS 					ntext 			NULL,
	WASSN_HISTORY_NOTES 				ntext 			NULL,
	WASSN_NOTE_STATUS 				int 			NOT NULL DEFAULT 0,
	TASK_IS_MILESTONE 				tinyint 		NOT NULL DEFAULT 0 CHECK ((TASK_IS_MILESTONE >= 0) AND (TASK_IS_MILESTONE <= 1)),
	TASK_IS_SUMMARY 				tinyint 		NOT NULL DEFAULT 0 CHECK ((TASK_IS_SUMMARY >= 0) AND (TASK_IS_SUMMARY <= 1)),
	WASSN_IS_CONFIRMED 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WASSN_IS_CONFIRMED >= 0) AND (WASSN_IS_CONFIRMED <= 1)),
	WASSN_MGR_UPDATED 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WASSN_MGR_UPDATED >= 0) AND (WASSN_MGR_UPDATED <= 1)),
	WASSN_CREATED_BY_RES 				int 			NOT NULL DEFAULT 0,
	WASSN_REMOVED_BY_RESOURCE 			int 			NOT NULL DEFAULT 0,
	WASSN_CURRENT_TRACKING_MODE 			int 			NULL DEFAULT 0,
	WASSN_UPDATE_TRACKING_MODE 			int 			NULL DEFAULT 0,
	WASSN_SEND_UPDATE_NEEDED 			tinyint 		NOT NULL DEFAULT 0 CHECK ((WASSN_SEND_UPDATE_NEEDED >= 0) AND (WASSN_SEND_UPDATE_NEEDED <= 1)),
	WASSN_DELETED_IN_PROJ 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WASSN_DELETED_IN_PROJ >= 0) AND (WASSN_DELETED_IN_PROJ <= 1)),
	WASSN_RESOURCE_UPDATE 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WASSN_RESOURCE_UPDATE >= 0) AND (WASSN_RESOURCE_UPDATE <= 1)),
	WASSN_REQUEST_UPDATE 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WASSN_REQUEST_UPDATE >= 0) AND (WASSN_REQUEST_UPDATE <= 1)),
	WASSN_UPDATES_ACCEPTED 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WASSN_UPDATES_ACCEPTED >= 0) AND (WASSN_UPDATES_ACCEPTED <= 1)),
	WASSN_DELEG_ACCEPTED 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WASSN_DELEG_ACCEPTED >= 0) AND (WASSN_DELEG_ACCEPTED <= 1)),
	WASSN_DELEG_APPROVED 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WASSN_DELEG_APPROVED >= 0) AND (WASSN_DELEG_APPROVED <= 1)),
	WASSN_ACTUALS_PENDING 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WASSN_ACTUALS_PENDING >= 0) AND (WASSN_ACTUALS_PENDING <= 2)),
	WASSN_DELEG_PENDING 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WASSN_DELEG_PENDING >= 0) AND (WASSN_DELEG_PENDING <= 1)),
	WASSN_IS_DELEGATED 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WASSN_IS_DELEGATED >= 0) AND (WASSN_IS_DELEGATED <= 1)),		
	WASSN_IS_NEW_ASSN 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WASSN_IS_NEW_ASSN >= 0) AND (WASSN_IS_NEW_ASSN <= 1)),
	WASSN_UPDATE_STATUS 				int 			NOT NULL DEFAULT 0,
	WASSN_LAST_DELEG_ID 				int 			NULL,
	WASSN_PCT_COMP 					int 			NULL,
	WASSN_SEND_UPDATE_DATE 				datetime 		NULL,
	WASSN_SUMMARY_PROGRESS 				datetime 		NULL,
	WRES_ID_TEAM_LEAD 				int 			NULL,
	WNWRK_ID 					int 			NULL,
	WNWRK_ENTRY_ID 					nvarchar(300) 		NULL,
	RESERVED_DATA1 					int 			NULL,
	RESERVED_DATA2 					int 			NULL,
	RESERVED_DATA3 					int 			NULL,
	ASSN_BOOKING_TYPE 				smallint 		NOT NULL DEFAULT 0,
        WASSN_ASSIGNED_TO_EXISTING                      tinyint                 NOT NULL DEFAULT 0
)
-- ON [PRIMARY]
GO


CREATE INDEX I_ASSIGN_WRESID_TEAM_LEAD
ON dbo.MSP_WEB_ASSIGNMENTS(WRES_ID_TEAM_LEAD)
-- ON [PRIMARY]
GO


CREATE INDEX I_ASSIGN_PROJSRCID_ASSNPROJUID_WRESID
ON dbo.MSP_WEB_ASSIGNMENTS (WPROJ_ID, ASSN_UID, WRES_ID)
WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO


CREATE INDEX I_ASSIGN_TASKPROJUID
ON dbo.MSP_WEB_ASSIGNMENTS (TASK_UID)
-- ON [PRIMARY]
GO


CREATE CLUSTERED INDEX CI_ASSIGN_RESID
ON dbo.MSP_WEB_ASSIGNMENTS (WRES_ID)
-- ON [PRIMARY]
GO


CREATE INDEX I_ASSIGN_MGRRESID
ON dbo.MSP_WEB_ASSIGNMENTS (WRES_ID_MGR)
-- ON [PRIMARY]
GO


CREATE INDEX I_ASSIGN_LASTDELEGID
ON dbo.MSP_WEB_ASSIGNMENTS (WASSN_LAST_DELEG_ID)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_CALENDAR_TRANSACTIONS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_CALENDAR_TRANSACTIONS
(
	WCTRANS_ID 					int IDENTITY (1,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_CALENDAR_TRANSACTIONS_WCTRANS_ID PRIMARY KEY CLUSTERED,
	WNONWORK_ID 					int 			NOT NULL,		
	WCTRANS_DATE 					datetime 		NULL,
	WCTRANS_ACTION 					int 			NOT NULL DEFAULT 0,
	WRES_ID_CTRANS_RECEIVER 			int 			NULL,
	WRES_ID_CTRANS_SENDER 				int 			NULL
)
-- ON [PRIMARY]
GO


CREATE INDEX IX_MSP_WEB_CALENDAR_TRANSACTIONS_WNONWORK_ID 
ON dbo.MSP_WEB_CALENDAR_TRANSACTIONS(WNONWORK_ID)
-- ON [PRIMARY]
GO


CREATE INDEX IX_MSP_WEB_CALENDAR_TRANSACTIONS_WRES_ID_CTRANS_RECEIVER 
ON dbo.MSP_WEB_CALENDAR_TRANSACTIONS(WRES_ID_CTRANS_RECEIVER)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_CONVERSIONS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_CONVERSIONS
(
	STRING_TYPE_ID 					int 			NOT NULL,
	CONV_VALUE 					int 			NOT NULL,   
	LANG_ID 					int 			NOT NULL,	   
	CONV_STRING 					nvarchar(1000) 		NULL
)
-- ON [PRIMARY]
GO


ALTER TABLE dbo.MSP_WEB_CONVERSIONS ADD CONSTRAINT
  PK_MSP_WEB_CONVERSIONS PRIMARY KEY CLUSTERED (LANG_ID, CONV_VALUE, STRING_TYPE_ID)
GO

--****************************************************************************************************
--*		MSP_WEB_DELEGATION_ASSIGNMENTS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_DELEGATION_ASSIGNMENTS
(
	WDELEG_ID 					int 			NOT NULL,  
	WASSN_ID 					int 			NOT NULL,
	WDELEG_APPROVE 					tinyint 		NOT NULL DEFAULT 1 CHECK ((WDELEG_APPROVE >= 0) AND (WDELEG_APPROVE <= 1)),
	WDELEG_ACCEPT 					tinyint 		NOT NULL DEFAULT 1 CHECK ((WDELEG_ACCEPT >= 0) AND (WDELEG_ACCEPT <= 1)),
	WDELEG_KEEP_COPY 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WDELEG_KEEP_COPY >= 0) AND (WDELEG_KEEP_COPY <= 1)),
	CONSTRAINT PK_MSP_WEB_DELEGATION_ASSIGNMENTS_WDELEG_ID_WASSN_ID PRIMARY KEY CLUSTERED (WDELEG_ID, WASSN_ID) 
)
-- ON [PRIMARY]
GO


CREATE INDEX I_DELEGASSIGN_ASSNID
ON MSP_WEB_DELEGATION_ASSIGNMENTS (WASSN_ID)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_DELEGATIONS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_DELEGATIONS
(
	WDELEG_ID 					int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_DELEGATIONS_WDELEG_ID PRIMARY KEY CLUSTERED, 
	WRES_ID_DELEGATOR 				int 			NULL, 
	WRES_ID_DELEGATEE 				int 			NULL, 
	WDELEG_KEEP_COPY 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WDELEG_KEEP_COPY >= 0) AND (WDELEG_KEEP_COPY <= 1)),
	WDELEG_DATE 					datetime 		NULL,
	WDELEG_REJECT 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WDELEG_REJECT >= 0) AND (WDELEG_REJECT <= 1))
)
-- ON [PRIMARY]
GO


CREATE INDEX I_DELEG_WRESIDDELEGATOR
ON dbo.MSP_WEB_DELEGATIONS (WRES_ID_DELEGATOR)
-- ON [PRIMARY]
GO


CREATE INDEX I_DELEG_WRESIDDELEGATEE
ON dbo.MSP_WEB_DELEGATIONS (WRES_ID_DELEGATEE)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_GANTT_SCHEMES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_GANTT_SCHEMES
(
	WGANTT_SCHEME_ID 				int 			NOT NULL CONSTRAINT PK_MSP_WEB_GANTT_SCHEMES_WGANTT_SCHEME_ID PRIMARY KEY NONCLUSTERED,
	WGANTT_SCHEME_TYPE 				int 			NULL DEFAULT 0,
	WGANTT_SCHEME_NAME 				nvarchar(50) 		NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_GANTT_SETTINGS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_GANTT_SETTINGS
(
	WGANTT_SCHEME_ID 				int 			NOT NULL, 		
	WGANTT_STYLE_ID 				int 			NOT NULL,		
	WGANTT_SHOW 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WGANTT_SHOW >= 0) AND (WGANTT_SHOW <= 1)),
	WGANTT_BAR_TYPE 				int 			NULL,
	WGANTT_BAR_PATTERN 				int 			NULL,
	WGANTT_BAR_COLOR 				int 			NULL,
	WGANTT_START_SHAPE 				int 			NULL,
	WGANTT_START_COLOR 				int 			NULL,
	WGANTT_END_SHAPE 				int 			NULL,
	WGANTT_END_COLOR 				int 			NULL,
	CONSTRAINT PK_MSP_WEB_GANTT_SETTINGS_WGANTT_SCHEME_ID_WGANTT_STYLE_ID PRIMARY KEY CLUSTERED (WGANTT_SCHEME_ID, WGANTT_STYLE_ID)
-- ON [PRIMARY]
)
GO

--****************************************************************************************************
--*		MSP_WEB_GANTT_STYLES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_GANTT_STYLES
(
	WGANTT_STYLE_ID 				int 			NOT NULL CONSTRAINT PK_MSP_WEB_GANTT_STYLES_WGANTT_STYLE_ID PRIMARY KEY NONCLUSTERED,
	WGANTT_STYLE_CONV_VALUE 			int 			NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_GROUP_SCHEMES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_GROUP_SCHEMES
(
	WGROUP_SCHEME_ID 				int 			NOT NULL CONSTRAINT PK_MSP_WEB_GROUP_SCHEMES_WGROUP_SCHEME_ID PRIMARY KEY CLUSTERED,
	WGROUP_SCHEME_NAME 				nvarchar(50) 		NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_GROUP_SETTINGS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_GROUP_SETTINGS
(
	WGROUP_SETTING_ID 				int IDENTITY(1,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_GROUP_SETTINGS_WGROUP_SETTING_ID PRIMARY KEY NONCLUSTERED,
	WGROUP_SCHEME_ID 				int 			NOT NULL, 		
	WGROUP_STYLE_ID 				int 			NULL,		
	WGROUP_ROW_COLOR 				int 			NULL,
	WGROUP_ROW_PATTERN 				int 			NULL,
	WGROUP_TEXT_COLOR 				int 			NULL,
	WGROUP_FONT_STYLE 				int 			NULL
)
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX UQ_MSP_WEB_GROUP_SETTINGS
ON dbo.MSP_WEB_GROUP_SETTINGS(WGROUP_SCHEME_ID, WGROUP_STYLE_ID)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_GROUP_STYLES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_GROUP_STYLES
(
	WGROUP_STYLE_ID 				int 			NOT NULL CONSTRAINT PK_MSP_WEB_GROUP_STYLES_WGROUP_STYLE_ID PRIMARY KEY CLUSTERED,
	WGROUP_STYLE_CONV_VALUE 			int 			NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_INSTALLED_LANGUAGES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_INSTALLED_LANGUAGES
(
	WLANG_LCID 					int 			NOT NULL CONSTRAINT PK_MSP_WEB_INSTALLED_LANGUAGES_WLANG_LCID PRIMARY KEY CLUSTERED,
	CONV_VALUE 					int 			NOT NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_MESSAGES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_MESSAGES
(
	WMSG_ID 					int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_MESSAGES_WMSG_ID PRIMARY KEY NONCLUSTERED,
	WMSG_PROJ_TYPE 					int 			NULL,
	WRES_ID_SENDER 					int 			NULL,	
	WRES_ID_RECEIVER 				int 			NULL,		
	WMSG_SUBJECT 					nvarchar(255) 		NULL,
	WMSG_BODY 					nvarchar(3000) 		NULL,
	WMSG_TIME					datetime 		NULL,
	WPROJ_ID 					int 			NOT NULL, 			
	WMSG_ACTUAL_TYPE 				int 			NULL,
	WMSG_PERIOD_START 				datetime 		NULL,
	WMSG_PERIOD_FINISH 				datetime 		NULL,
	WMSG_PERIOD_BROKEN_BY 				int 			NULL DEFAULT -1,
	WMSG_PERIOD_WEEK_STARTS_ON 			int 			NULL DEFAULT -1,
	WMSG_WAS_READ 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WMSG_WAS_READ >= 0) AND (WMSG_WAS_READ <= 1)),
	WMSG_PROCESS_ST 				int 			NOT NULL DEFAULT 0,
	WMSG_RES_CAN_DECLINE 				tinyint 		NOT NULL DEFAULT 1 CHECK ((WMSG_RES_CAN_DECLINE >= 0) AND (WMSG_RES_CAN_DECLINE <= 1))
)
-- ON [PRIMARY]
GO


CREATE INDEX I_MESSAGES_PROJSRCID 
ON dbo.MSP_WEB_MESSAGES (WPROJ_ID)
-- ON [PRIMARY]
GO


CREATE INDEX I_MESSAGES_SENDERID
ON dbo.MSP_WEB_MESSAGES (WRES_ID_SENDER)
-- ON [PRIMARY]
GO


CREATE INDEX I_MESSAGES_WMSGRECIPIENTID 
ON dbo.MSP_WEB_MESSAGES (WRES_ID_RECEIVER)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_MESSAGES_ASSIGNMENTS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_MESSAGES_ASSIGNMENTS
(
	WMSG_ID 					int 			NOT NULL,		
	WASSN_ID 					int 			NOT NULL,		
	WASSN_IS_PROCESSED 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WASSN_IS_PROCESSED >= 0) AND (WASSN_IS_PROCESSED <= 1)),
	CONSTRAINT PK_MSP_WEB_MESSAGES_ASSIGNMENTS_WMSG_ID_WASSN_ID PRIMARY KEY CLUSTERED (WMSG_ID, WASSN_ID) 
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_MESSAGES_NONWORKING
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_MESSAGES_NONWORKING
(
	WMSGNONWORK_ID 					int IDENTITY(1,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_MESSAGES_NONWORKING_WMSGNONWORK_ID PRIMARY KEY CLUSTERED,
	WMSG_ID 					int 			NOT NULL,		
	WNONWORK_ID 					int 			NOT NULL,	
	CONSTRAINT UQ_MSP_WEB_MESSAGES_NONWORKING_WMSG_ID_WNONWORK_ID UNIQUE (WMSG_ID, WNONWORK_ID)
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_MGR_RULES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_MGR_RULES
(
	WRULE_ID 					int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_MGR_RULES_WRULE_ID PRIMARY KEY NONCLUSTERED, 
	WRULE_NAME 					nvarchar(255) 		NULL,  		
	WRES_ID_MGR 					int 			NULL,				
	WRULE_IS_ENABLED 				tinyint 		NOT NULL DEFAULT 1 CHECK ((WRULE_IS_ENABLED >= 0) AND (WRULE_IS_ENABLED <= 1)),
	WRULE_TYPE 					int 			NULL DEFAULT (-1),
	WRULE_CONDITION_TYPE 				int 			NULL DEFAULT 0,
	WRULE_FIELD1_ID 				int 			NULL,
	WRULE_FIELD2_ID 				int 			NULL,
	WRULE_OPERATOR 					int 			NULL,
	WRULE_VALTYPE 					int 			NULL,
	WRULE_INT_VAL 					int 			NULL,
	WRULE_DATE_VAL 					datetime 		NULL,
	WRULE_DECIMAL_VAL 				decimal(25,6) 		NULL,
	WRULE_VARCHAR_VAL 				nvarchar(255) 		NULL,
	WRULE_IS_EXCL_PROJID 				tinyint 		NULL DEFAULT 0 CHECK ((WRULE_IS_EXCL_PROJID >= 0) AND (WRULE_IS_EXCL_PROJID <= 1)),
	WRULE_IS_EXCL_RES1ID				tinyint 		NULL DEFAULT 0 CHECK ((WRULE_IS_EXCL_RES1ID >= 0) AND (WRULE_IS_EXCL_RES1ID <= 1)),
	WRULE_IS_EXCL_RES2ID 				tinyint 		NULL DEFAULT 0 CHECK ((WRULE_IS_EXCL_RES2ID >= 0) AND (WRULE_IS_EXCL_RES2ID <= 1)),
	WRULE_DESCRIPTION 				nvarchar(255)		NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_MGR_RULES_LISTS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_MGR_RULES_LISTS
(
	WRULE_ID 					int 			NOT NULL,				
	ITEM_TYPE 					int 			NOT NULL,
	ITEM_ID 					int 			NOT NULL,
	CONSTRAINT PK_MSP_WEB_MGR_RULES_LISTS_WRULE_ID_ITEM_TYPE_ITEM_ID PRIMARY KEY CLUSTERED (WRULE_ID, ITEM_TYPE, ITEM_ID)
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_MODELS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_MODELS
(
	WMOD_MODEL_UID 					int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_MODELS_WMOD_MODEL_UID PRIMARY KEY CLUSTERED,
	WMOD_MODEL_NAME 				nvarchar(255) 		NULL, 			
	WMOD_MODEL_DESC 				nvarchar(255) 		NULL,
	WMOD_CREATE_DATE 				datetime 		NOT NULL DEFAULT getdate(),
	WMOD_UPDATE_DATE 				datetime 		NOT NULL DEFAULT getdate(),
	WMOD_CREATED_BY 				nvarchar(255) 		NULL,
	WMOD_LAST_UPDATED_BY 				nvarchar(255) 		NULL,
	WMOD_LOCKED_BY 					nvarchar(255) 		NULL,
	WMOD_CREATED_BY_ID 				int 			NOT NULL DEFAULT 0,
	WMOD_LAST_UPDATED_BY_ID 			int 			NOT NULL DEFAULT 0,
	WMOD_LOCKED_BY_ID 				int 			NOT NULL DEFAULT 0,
	WMOD_RESERVED_BINARY_DATA 			image 			NULL,
	WMOD_MODEL 					image 			NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_NONWORKING
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_NONWORKING
(
	WNONWORK_ID 					int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_NONWORKING_WNONWORK_ID PRIMARY KEY NONCLUSTERED,
	WNONWORK_SUBJECT 				nvarchar(255) 		NULL,
	WNONWORK_START 					datetime 		NULL,
	WNONWORK_END 					datetime 		NULL,
	WNONWORK_IS_ALL_DAY 				tinyint 		NOT NULL DEFAULT 1 CHECK ((WNONWORK_IS_ALL_DAY >= 0) AND (WNONWORK_IS_ALL_DAY <= 1)),
	WNONWORK_IS_WORKING 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WNONWORK_IS_WORKING >= 0) AND (WNONWORK_IS_WORKING <= 1))
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_NONWORKING_CATEGORIES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_NONWORKING_CATEGORIES
(
	WNWRK_ID 					int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_NONWORKING_CATEGORIES_WNWRK_ID PRIMARY KEY NONCLUSTERED, 
	WNWRK_NAME 					nvarchar(255) 		NOT NULL, 
	WNWRK_CODE 					nvarchar(255) 		NULL,
	WNWRK_ORDER 					int 			NOT NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_NOTIFICATION_EVENTS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_NOTIFICATION_EVENTS
(
	WNTFY_EVENT_ID 					int IDENTITY (2000,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_NOTIFICATION_EVENTS_EVENT_ID PRIMARY KEY CLUSTERED,
	WNTFY_EVENT_DEFAULT 				tinyint 		NOT NULL DEFAULT 1 CHECK ((WNTFY_EVENT_DEFAULT >= 0) AND (WNTFY_EVENT_DEFAULT <= 1)),
	WNTFY_EVENT_ACTION_ID 				int 			NOT NULL DEFAULT 0,
	WNTFY_EVENT_SHOW_UI 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WNTFY_EVENT_SHOW_UI >= 0) AND (WNTFY_EVENT_SHOW_UI <= 1)),
	WNTFY_EVENT_QUERY 				nvarchar(1000) 		NULL,
	WNTFY_EMAIL_SUBJECT 				int 			NOT NULL DEFAULT 0,
	WNTFY_EMAIL_TITLE 				int 			NOT NULL DEFAULT 0,
	WNTFY_EMAIL_CONTENT 				int 			NOT NULL DEFAULT 0,
	WNTFY_EMAIL_TAIL1 				int 			NOT NULL DEFAULT 0,
	WNTFY_EMAIL_TAIL2 				int 			NOT NULL DEFAULT 0,
	WNTFY_EVENT_DESC 				nvarchar(100) 		NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_NOTIFICATIONS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_NOTIFICATIONS
(
	WNTFY_OWNER_ID 					int 			NOT NULL,
	WNTFY_EVENT_ID 					int 			NOT NULL,
	WNTFY_IS_ENABLED 				tinyint 		NOT NULL DEFAULT 1 CHECK ((WNTFY_IS_ENABLED >= 0) AND (WNTFY_IS_ENABLED <= 1))
)
-- ON [PRIMARY]
GO


ALTER TABLE dbo.MSP_WEB_NOTIFICATIONS
ADD CONSTRAINT PK_MSP_WEB_NOTIFICATIONS_WNTFY_OWNER_ID_WNTFY_EVENT_ID PRIMARY KEY CLUSTERED
(
	WNTFY_OWNER_ID,
	WNTFY_EVENT_ID
)
GO

--****************************************************************************************************
--*		MSP_WEB_OBJECTS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_OBJECTS
(
	WOBJ_ID 					int IDENTITY(1,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_OBJECTS_WOBJ_ID PRIMARY KEY CLUSTERED,
	WOBJ_TYPE 					int 			NOT NULL,		
	WOBJ_PROJ_ID 					int 			NOT NULL DEFAULT 0,
	WOBJ_TASK_ID 					int 			NULL,
	WOBJ_TP_ID 					int 			NULL,		
	WOBJ_LIST_NAME 					uniqueidentifier 	NULL,
	WOBJ_DESC 					ntext 			NULL,
	WOBJ_DOC_REF_CNT 				int 			NOT NULL DEFAULT 0,
	WOBJ_ISSUE_REF_CNT 				int 			NOT NULL DEFAULT 0,
	WOBJ_OTHER_REF_CNT 				int 			NOT NULL DEFAULT 0,
	WOBJ_RISK_REF_CNT 				int 			NOT NULL DEFAULT 0
)
-- ON [PRIMARY]
GO


CREATE INDEX I_OBJ_TYPEPROJTASK
ON dbo.MSP_WEB_OBJECTS (WOBJ_TYPE, WOBJ_PROJ_ID, WOBJ_TASK_ID)
-- ON [PRIMARY]
GO


CREATE INDEX I_OBJ_TYPEPROJLISTTP
ON dbo.MSP_WEB_OBJECTS (WOBJ_TYPE, WOBJ_PROJ_ID, WOBJ_LIST_NAME, WOBJ_TP_ID)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_OBJECT_LINKS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_OBJECT_LINKS
(
	WOBJLINK_ID 					int IDENTITY(1,1) 	NOT NULL CONSTRAINT MSP_WEB_OBJECT_LINKS_WOBJLINK_ID PRIMARY KEY CLUSTERED,
	WOBJLINK_OBJECT1 				int 			NOT NULL,		
	WOBJLINK_OBJECT2 				int 			NOT NULL,		
 	WOBJLINK_TYPE 					int 			NOT NULL,   		
)
-- ON [PRIMARY]
GO

CREATE INDEX I_OBJLINKS_OBJ1OBJ2
ON dbo.MSP_WEB_OBJECT_LINKS (WOBJLINK_OBJECT1, WOBJLINK_OBJECT2)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_PROJECT_WORKGROUP_INFO
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_PROJECT_WORKGROUP_INFO
(				
	WPROJ_ID 					int 			NOT NULL,			
	CUSTFIELD_INFO_ID 				int 			NOT NULL,		
	PICKLIST_INFO 					ntext 			NULL,
	CONSTRAINT PK_MSP_WEB_PROJECT_WORKGROUP_INFO_WPROJ_ID_CUSTFIELD_INFO_ID	PRIMARY KEY CLUSTERED (WPROJ_ID,CUSTFIELD_INFO_ID)
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_PROJECTS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_PROJECTS
(
	WPROJ_ID 					int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_PROJECTS_WPROJ_ID PRIMARY KEY CLUSTERED,
	PROJ_NAME 					nvarchar(255) 		NULL,  
	PROJ_TIMESTAMP 					nvarchar(32) 		NULL,
	WPROJ_DESCRIPTION 				ntext 			NULL,
	WLOCAL_PATH 					nvarchar(260) 		NULL,
	WPATH 						nvarchar(260) 		NULL,
	WRES_ID 					int 			NULL, 		
	WDSN_ID 					int 			NULL, 		
	WPROJ_DELEG_ALLOWED 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WPROJ_DELEG_ALLOWED >= 0) AND (WPROJ_DELEG_ALLOWED <= 1)),
	WPROJ_IS_NONWORKING				tinyint 		NOT NULL DEFAULT 0 CHECK ((WPROJ_IS_NONWORKING >= 0) AND (WPROJ_IS_NONWORKING <= 1)),
	WPROJ_SCOPE 					tinyint 		NOT NULL DEFAULT 0,
	WPROJ_IS_CONSOLIDATED_PROJECT 			tinyint 		NOT NULL DEFAULT 0 CHECK ((WPROJ_IS_CONSOLIDATED_PROJECT >= 0) AND (WPROJ_IS_CONSOLIDATED_PROJECT <= 1)),
	WPROJ_RES_CAN_DECLINE 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WPROJ_RES_CAN_DECLINE >= 0) AND (WPROJ_RES_CAN_DECLINE <= 1)),
	WPROJ_DEFAULT_WPROJ_ID 				int 			NOT NULL DEFAULT 0,
	WPROJ_TRACKING_METHOD 				int 			NULL DEFAULT 0,
	WPROJ_LAST_PUB 					datetime 		NOT NULL DEFAULT GETDATE(),
	PROJ_ID 					int 			NULL,			
	WPROJ_TYPE 					int 			NOT NULL DEFAULT 0,
	OPT_DEF_START_TIME 				datetime		NULL, 		
	OPT_DEF_FINISH_TIME 				datetime		NULL,		
	WPROJ_STS_SUBWEB_NAME 				nvarchar(128) 		NULL DEFAULT 'NONE',
	WSTS_SERVER_ID 					int 			NOT NULL DEFAULT 0,
	WPROJ_ISSUE_LIST_NAME 				uniqueidentifier 	NULL,
        WPROJ_RISK_LIST_NAME 				uniqueidentifier 	NULL, 
	WPROJ_ADMINPROJECT           			tinyint      		NOT NULL DEFAULT 0 CHECK ((WPROJ_ADMINPROJECT >= 0) AND (WPROJ_ADMINPROJECT <= 1)),
	CONSTRAINT UQ_MSP_WEB_PROJECTS_PROJ_ID_PROJ_TIMESTAMP_PROJ_NAME_WRES_ID UNIQUE (PROJ_ID, PROJ_TIMESTAMP, PROJ_NAME, WRES_ID)
)
-- ON [PRIMARY]
GO


CREATE INDEX I_FILES_OWNERRESID 
ON dbo.MSP_WEB_PROJECTS (WRES_ID)
-- ON [PRIMARY]
GO


IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_WEB_PROJECTS_AUI_TRG')
AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER dbo.MSP_WEB_PROJECTS_AUI_TRG
GO


CREATE TRIGGER dbo.MSP_WEB_PROJECTS_AUI_TRG
ON dbo.MSP_WEB_PROJECTS
FOR INSERT
AS 

  DECLARE @new_PROJ_ID int
  DECLARE @new_PROJ_TIMESTAMP nvarchar(32)
  DECLARE @new_WRES_ID int
  DECLARE @DupCount int

  SELECT @new_PROJ_ID = PROJ_ID FROM inserted
  SELECT @new_PROJ_TIMESTAMP = PROJ_TIMESTAMP FROM inserted
  SELECT @new_WRES_ID = WRES_ID FROM inserted

  -- if PROJ_ID is NULL then simply insert row, as we allow NULL PROJ_ID's
  IF @new_PROJ_ID IS NULL
  BEGIN
    RETURN -- Since we are inserting a NULL PROJ_ID this is ok.  Exit trigger code.
  END

  -- if PROJ_ID is NOT NULL then ensure same PROJ_ID does not already exist in the table
  IF @new_PROJ_ID IS NOT NULL
  BEGIN
    SELECT @DupCount = count(*) FROM MSP_WEB_PROJECTS WHERE PROJ_ID = @new_PROJ_ID
    IF @DupCount = 0
    BEGIN
      RETURN
    END
	  IF @DupCount > 1
    BEGIN
      
			DELETE 
			FROM MSP_WEB_PROJECTS 
			WHERE PROJ_ID = @new_PROJ_ID 
			AND PROJ_TIMESTAMP = @new_PROJ_TIMESTAMP
      AND WRES_ID = @new_WRES_ID

			--RAISERROR ('Attempted to add a duplicate PROJ_ID to MSP_WEB_PROJECTS!', 16, 1, @new_PROJ_ID, @new_PROJ_TIMESTAMP)
      RAISERROR ('Attempted to insert duplicate PROJ_ID (%d), PROJ_TIMESTAMP (%s), IN MSP_WEB_PROJECTS', 16, 1, @new_PROJ_ID, @new_PROJ_TIMESTAMP)

    END
  END

GO


--****************************************************************************************************
--*		MSP_WEB_REMINDER_TYPES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_REMINDER_TYPES
(
	WRMND_TYPE_ID 					int 			NOT NULL CONSTRAINT PK_MSP_WEB_REMINDER_TYPES_WRMND_TYPE_ID PRIMARY KEY CLUSTERED,
	WRMND_TABLE_ID 					int 			NOT NULL,
	WRMND_IS_SELF 					tinyint 		NOT NULL CHECK ((WRMND_IS_SELF >= 0) AND (WRMND_IS_SELF <= 1)),
	WRMND_QUERY 					nvarchar(1000) 		NULL,
	WRMND_EMAIL_TITLE 				int 			NOT NULL DEFAULT 0,
	WRMND_EMAIL_TAIL1 				int 			NOT NULL DEFAULT 0,
	WRMND_TYPE_DESC 				nvarchar(100) 		NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_REMINDERS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_REMINDERS
(
	WRMND_OWNER_ID 					int 			NOT NULL,			
	WRMND_TYPE_ID 					int 			NOT NULL,
	WRMND_IS_ENABLED 				tinyint 		NOT NULL CHECK ((WRMND_IS_ENABLED >= 0) AND (WRMND_IS_ENABLED <= 1)),
	WRMND_SEND_TO 					smallint 		NOT NULL DEFAULT 0,
	WRMND_PARAMETERS 				nvarchar(20) 		NULL,
	WRMND_CRITERIA 					nvarchar(510) 		NULL,
	WRMND_START_DATE 				datetime 		NULL,
	WFREQ 						int 			NOT NULL,
	WFREQPAR1 					int 			NULL,
	WFREQPAR2 					int 			NULL,
	WFREQPAR3 					int 			NULL,
	WFREQPAR4 					int 			NULL,
	WFREQPAR5 					int 			NULL,
	WFREQPAR6 					int 			NULL,
	WFREQ_DATE 					datetime 		NULL,
	WRMND_NEXT_UPDATE 				datetime 		NULL,
	CONSTRAINT PK_MSP_WEB_REMINDERS_WRMND_OWNER_ID_WRMND_TYPE_ID PRIMARY KEY CLUSTERED (WRMND_OWNER_ID, WRMND_TYPE_ID)
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_REMINDERS_TEMP
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_REMINDERS_TEMP
(
	WRMND_ID_RECEIVER 				int 			NOT NULL,
	WRMND_TABLE_ID 					int 			NOT NULL,
	WRMND_MSG_SENT 					tinyint 		NOT NULL CHECK ((WRMND_MSG_SENT >= 0) AND (WRMND_MSG_SENT <= 1)),
	WRMND_MESSAGE 					ntext 			NULL,
	CONSTRAINT PK_MSP_WEB_REMINDERS_TEMP_WRMND_ID_RECEIVER_WRMND_TABLE_ID PRIMARY KEY CLUSTERED (WRMND_ID_RECEIVER, WRMND_TABLE_ID)
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_RESERVED_DATA
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_RESERVED_DATA
(
	RESERVED_IDFAKE 				int IDENTITY 		NOT NULL CONSTRAINT PK_MSP_WEB_RESERVED_DATA PRIMARY KEY,
	RESERVED_DATA1 					int 			NULL,
	RESERVED_DATA2 					int 			NULL,
	RESERVED_DATA3 					int 			NOT NULL DEFAULT 2,
	RESERVED_DATA4 					int 			NULL DEFAULT -1
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_RESOURCE_SETTINGS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_RESOURCE_SETTINGS
(
	WSET_ID 					int IDENTITY(1,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_RESOURCE_SETTINGS_WSET_ID PRIMARY KEY NONCLUSTERED,
	WSEC_PAGE_ID 					int 			NOT NULL,  		
	WRES_GUID 					uniqueidentifier 	NOT NULL, 	
	WSET_VALUE 					ntext 			NOT NULL
)
-- ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX IX_MSP_WEB_RESOURCE_SETTINGS_WRES_GUID_WSET_DATASPACE
ON dbo.MSP_WEB_RESOURCE_SETTINGS(WRES_GUID, WSEC_PAGE_ID)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_RESOURCES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_RESOURCES
(
	WRES_ID 					int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_RESOURCES_WRES_ID PRIMARY KEY CLUSTERED,  
	WRES_GUID 					uniqueidentifier 	NOT NULL DEFAULT NEWID(),
	RES_NAME 					nvarchar(255) 		NOT NULL,
	RES_EUID 					int 			NULL,
	WRES_USE_NT_LOGON 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WRES_USE_NT_LOGON >= 0) AND (WRES_USE_NT_LOGON <= 1)),
	WRES_NT_ACCOUNT 				nvarchar(255) 		NOT NULL DEFAULT (N''),
	WRES_EMAIL 					nvarchar(255) 		NULL,
	WRES_EMAIL_LANGUAGE 				int 			NOT NULL DEFAULT 0,
	WRES_LAST_UPDATE_TIME 				datetime 		NULL,
	WRES_LAST_CHECKED_TIME 				datetime 		NULL,
	WRES_IS_OFFLINE 				tinyint			NOT NULL DEFAULT 0 CHECK ((WRES_IS_OFFLINE >= 0) AND (WRES_IS_OFFLINE <= 1)),
	WRES_LAST_CONNECT 				datetime 		NULL,
	WRES_IS_MANAGER 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WRES_IS_MANAGER >= 0) AND (WRES_IS_MANAGER <= 1)),
	WRES_IS_ADMIN 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WRES_IS_ADMIN >= 0) AND (WRES_IS_ADMIN <= 1)),
	WRES_IS_ENABLED 				tinyint 		NOT NULL DEFAULT 1 CHECK ((WRES_IS_ENABLED >= 0) AND (WRES_IS_ENABLED <= 1)),
	WRES_IS_PC2000 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WRES_IS_PC2000 >= 0) AND (WRES_IS_PC2000 <= 1)),
	WRES_PASSWORD 					nvarchar(255) 		NOT NULL DEFAULT(N''),
	WRES_DEL_TASKUPDATE_MSG 			tinyint 		NOT NULL DEFAULT 0 CHECK ((WRES_DEL_TASKUPDATE_MSG >= 0) AND (WRES_DEL_TASKUPDATE_MSG <= 1)),
	WRES_DEL_DELEG_MSG 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WRES_DEL_DELEG_MSG >= 0) AND (WRES_DEL_DELEG_MSG <= 1)),
	WRES_DEL_NEWTASK_MSG 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WRES_DEL_NEWTASK_MSG >= 0) AND (WRES_DEL_NEWTASK_MSG <= 1)),
	WRES_DEL_REASSN_MSG 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WRES_DEL_REASSN_MSG >= 0) AND (WRES_DEL_REASSN_MSG <= 1)),
	WRES_INCL_TEAMLEAD_RESOURCES 			tinyint 		NOT NULL DEFAULT 0 CHECK ((WRES_INCL_TEAMLEAD_RESOURCES >= 0) AND (WRES_INCL_TEAMLEAD_RESOURCES<= 1)),
	WRES_INCL_TEAMASSIGN_RESOURCES 			tinyint 		NOT NULL DEFAULT 0 CHECK ((WRES_INCL_TEAMASSIGN_RESOURCES >= 0) AND (WRES_INCL_TEAMASSIGN_RESOURCES <= 1)),
	WRES_CAN_LOGIN 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WRES_CAN_LOGIN >= 0) AND (WRES_CAN_LOGIN <= 1)),
	WRES_COUNT_LICENSE 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WRES_COUNT_LICENSE >= 0) AND (WRES_COUNT_LICENSE <= 1)),
	RES_PHONETICS 					nvarchar(255) 		NULL,
	RESERVED_DATA1 					int 			NOT NULL,
	RESERVED_DATA2 					int 			NOT NULL,
	RESERVED_DATA3 					int 			NOT NULL,
	RESERVED_DATA4 					int 			NOT NULL,
        WRES_AD_GUID 					uniqueidentifier 	NULL,
	WRES_RBS_UID           				int      		NULL,
        WRES_WSS_VALIDATOR_GUID				nvarchar(32)		NULL
)
-- ON [PRIMARY]
GO

CREATE UNIQUE INDEX I_RESOURCES_WRESNAME 
ON dbo.MSP_WEB_RESOURCES (RES_NAME) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO


CREATE INDEX IX_MSP_WEB_RESOURCES_WRES_NT_ACCOUNT 
ON dbo.MSP_WEB_RESOURCES (WRES_NT_ACCOUNT)
-- ON [PRIMARY]
GO


CREATE INDEX IX_MSP_WEB_RESOURCES_WRES_GUID
ON dbo.MSP_WEB_RESOURCES (WRES_GUID)
-- ON [PRIMARY]
GO


CREATE INDEX IX_MSP_WEB_RESOURCES_RES_EUID
ON dbo.MSP_WEB_RESOURCES (RES_EUID)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_SECURITY_CATEGORY_OBJECTS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_SECURITY_CATEGORY_OBJECTS
(
	WSEC_CAT_ID 					int 			NOT NULL,	
	WSEC_OBJ_TYPE 					tinyint 		NOT NULL,	
	WSEC_OBJ_ID 					int 			NOT NULL,
	CONSTRAINT PK_MSP_WEB_SECURITY_CATEGORY_OBJECTS_WSEC_CAT_ID_WSEC_OBJ_TYPE_WSEC_OBJ_ID PRIMARY KEY CLUSTERED (WSEC_CAT_ID, WSEC_OBJ_TYPE, WSEC_OBJ_ID)
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_SECURITY_CATEGORY_RULES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_SECURITY_CATEGORY_RULES
(
	WSEC_CAT_ID 					int 			NOT NULL,	
	WSEC_OBJ_TYPE 					tinyint 		NOT NULL,
	WSEC_OBJ_RULE_TYPE 				tinyint 		NOT NULL DEFAULT 0, 
	CONSTRAINT PK_MSP_WEB_SECURITY_CATEGORY_RULES_WSEC_CAT_ID_WSEC_OBJ_TYPE_WSEC_OBJ_RULE_TYPE PRIMARY KEY CLUSTERED (WSEC_CAT_ID, WSEC_OBJ_TYPE, WSEC_OBJ_RULE_TYPE)
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_SECURITY_CATEGORIES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_SECURITY_CATEGORIES
(
	WSEC_CAT_ID 					int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_CATEGORIES_WSEC_CAT_ID PRIMARY KEY CLUSTERED,
	WSEC_CAT_NAME 					nvarchar(255) 		NULL, 
	WSEC_CAT_DESC 					nvarchar(255) 		NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_SECURITY_FEATURES_ACTIONS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_SECURITY_FEATURES_ACTIONS
(
	WSEC_FEA_ACT_ID 				int IDENTITY(10000,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_FEATURES_ACTIONS_WSEC_FEA_ACT_ID PRIMARY KEY CLUSTERED,
	WSEC_FEA_ACT_NAME_ID 				int 			NOT NULL,
	WSEC_FEA_ACT_PARENT 				int 			NOT NULL DEFAULT 0, 
	WSEC_IS_ACTION 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WSEC_IS_ACTION >= 0) AND (WSEC_IS_ACTION <= 1)),
	WSEC_ON_OBJECT 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WSEC_ON_OBJECT >= 0) AND (WSEC_ON_OBJECT <= 1)),
	WSEC_OBJ_TYPE_ID 				int 			NOT NULL DEFAULT 0
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_SECURITY_GROUP_MEMBERS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_SECURITY_GROUP_MEMBERS
(
	WSEC_GRP_GUID 					uniqueidentifier 	NOT NULL,	
	WRES_GUID 					uniqueidentifier 	NOT NULL, 		
	CONSTRAINT PK_MSP_WEB_SECURITY_GROUP_MEMBERS_WRES_GUID_WRES_GUID_WSEC_GPR_GUID PRIMARY KEY CLUSTERED(WRES_GUID,WSEC_GRP_GUID)
)
-- ON [PRIMARY]
GO


CREATE INDEX IX_MSP_WEB_SECURITY_GROUP_MEMBERS_WRES_GUID
ON dbo.MSP_WEB_SECURITY_GROUP_MEMBERS(WSEC_GRP_GUID)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_SECURITY_GROUPS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_SECURITY_GROUPS
(
	WSEC_GRP_ID 					int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_GROUPS_WSEC_GRP_ID PRIMARY KEY NONCLUSTERED,
	WSEC_GRP_GUID 					uniqueidentifier 	NOT NULL DEFAULT (newid()),
	WSEC_GRP_NAME 					nvarchar(100) 		NOT NULL,
	WSEC_GRP_DESC 					nvarchar(255) 		NULL,
	WSEC_GRP_DASHBOARD_URL 				nvarchar(255) 		NULL,
	WSEC_GRP_AD_GROUP 				nvarchar(64) 		NULL,
	WSEC_GRP_AD_LOG 				smallint 		NULL
)
-- ON [PRIMARY]
GO


CREATE CLUSTERED INDEX IX_MSP_WEB_SECURITY_GROUPS
ON dbo.MSP_WEB_SECURITY_GROUPS(WSEC_GRP_GUID)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_SECURITY_MENUS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_SECURITY_MENUS
(
	WSEC_MENU_ID 					int IDENTITY(10000,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_MENUS_WSEC_MENU_ID PRIMARY KEY CLUSTERED,
	WSEC_MENU_NAME_ID 				int 			NULL,
	WSEC_MENU_CUSTOM_NAME 				nvarchar(100)		NULL,
	WSEC_MENU_PARENT_ID 				int 			NULL DEFAULT 0,  
	WSEC_MENU_SEQ 					smallint 		NULL,
	WSEC_MENU_DESC_ID 				int 			NULL,
	WSEC_MENU_CUSTOM_DESC 				nvarchar(100) 		NULL,
	WSEC_MENU_PAGE_ID 				int 			NULL,
	WSEC_MENU_IS_CUSTOM 				tinyint 		NOT NULL CHECK ((WSEC_MENU_IS_CUSTOM >= 0) AND (WSEC_MENU_IS_CUSTOM <= 1)),
	WSEC_MENU_IS_TOP_LEVEL				tinyint 		NOT NULL CHECK ((WSEC_MENU_IS_TOP_LEVEL >= 0) AND (WSEC_MENU_IS_TOP_LEVEL <= 1)),
	WSEC_MENU_LINKGROUP_NAME_ID 			int 			NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_SECURITY_OBJECT_RULES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_SECURITY_OBJECT_RULES
(
	WSEC_OBJ_TYPE 					tinyint 		NOT NULL,		
	WSEC_OBJ_RULE_TYPE 				tinyint 		NOT NULL DEFAULT 0, 
	WSEC_OBJ_RULE_DESC 				nvarchar(255) 		NULL
	CONSTRAINT PK_MSP_WEB_SECURITY_OBJECT_RULES_WSEC_OBJ_TYPE_WSEC_OBJ_RULE_TYPE PRIMARY KEY CLUSTERED (WSEC_OBJ_TYPE, WSEC_OBJ_RULE_TYPE)
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_SECURITY_OBJECT_TYPES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_SECURITY_OBJECT_TYPES
(
	WSEC_OBJ_TYPE_ID 				int IDENTITY(1000,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_OBJECT_TYPES_WSEC_OBJ_TYPE_ID PRIMARY KEY CLUSTERED,
	WSEC_OBJ_TYPE_NAME 				nvarchar(100) 		NOT NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_SECURITY_ORG_PERMISSIONS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_SECURITY_ORG_PERMISSIONS
(
	WSEC_FEA_ACT_ID 				int 			NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_ORG_PERMISSIONS_WSEC_FEA_ACT_ID PRIMARY KEY CLUSTERED,
	WSEC_ALLOW 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WSEC_ALLOW >= 0) AND (WSEC_ALLOW <= 1)),
	WSEC_DENY 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WSEC_DENY >= 0) AND (WSEC_DENY <= 1)),
	WSEC_ACCESS 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WSEC_ACCESS >= 0) AND (WSEC_ACCESS <= 1)),
	WSEC_PAID 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WSEC_PAID >= 0) AND (WSEC_PAID <= 1))
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_SECURITY_PAGES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_SECURITY_PAGES
(
	WSEC_PAGE_ID 					int IDENTITY(10000,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_PAGES_WSEC_PAGE_ID PRIMARY KEY CLUSTERED,
	WSEC_PAGE_MENU_ID 				int 			NULL,				
	WSEC_PAGE_URL 					nvarchar(255) 		NULL,
	WSEC_PAGE_CUSTOM_URL 				nvarchar(255) 		NULL,
	WSEC_PAGE_HELP_URL 				nvarchar(255) 		NULL,
	WSEC_PAGE_ACT_ID 				int 			NULL,		
	WSEC_PAGE_MASTER_PAGE_ID 			int 			NULL,
	WSEC_PAGE_SESSION_SETTINGS 			ntext 			NULL,
	WSEC_PAGE_DATABASE_SETTINGS 			ntext 			NULL,
	WSEC_PAGE_CAN_OFFLINE 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WSEC_PAGE_CAN_OFFLINE >= 0) AND (WSEC_PAGE_CAN_OFFLINE <= 1))
)
-- ON [PRIMARY]
GO


CREATE INDEX IX_MSP_WEB_SECURITY_PAGES_WSEC_PAGE_MENU_ID
ON dbo.MSP_WEB_SECURITY_PAGES(WSEC_PAGE_MENU_ID)
-- ON [PRIMARY]
GO


CREATE INDEX IX_MSP_WEB_SECURITY_PAGES_WSEC_PAGE_ACT_ID
ON dbo.MSP_WEB_SECURITY_PAGES(WSEC_PAGE_ACT_ID)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_SECURITY_SP_CAT_PERMISSIONS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_SECURITY_SP_CAT_PERMISSIONS
(
	WSEC_REL_ID 					int 			NOT NULL,					
	WSEC_FEA_ACT_ID 				int 			NOT NULL,				
	WSEC_ALLOW					tinyint 		NOT NULL DEFAULT 0 CHECK ((WSEC_ALLOW >= 0) AND (WSEC_ALLOW <= 1)),
	WSEC_DENY 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WSEC_DENY >= 0) AND (WSEC_DENY <= 1)),
	WSEC_ACCESS 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WSEC_ACCESS >= 0) AND (WSEC_ACCESS <= 1)),
	CONSTRAINT PK_MSP_WEB_SECURITY_SP_CAT_PERMISSIONS_WSEC_REL_ID_WSEC_FEA_ACT_ID PRIMARY KEY CLUSTERED (WSEC_REL_ID, WSEC_FEA_ACT_ID) 
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_SECURITY_SP_CAT_RELATIONS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_SECURITY_SP_CAT_RELATIONS
(
	WSEC_REL_ID 					int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_SP_CAT_RELATIONS_WSEC_REL_ID PRIMARY KEY CLUSTERED,
	WSEC_SP_GUID 					uniqueidentifier 	NOT NULL,
	WSEC_CAT_ID 					int 			NOT NULL 
)
-- ON [PRIMARY]
GO  


CREATE INDEX  IX_MSP_WEB_SECURITY_SP_CAT_RELATIONS
ON dbo.MSP_WEB_SECURITY_SP_CAT_RELATIONS(WSEC_SP_GUID, WSEC_CAT_ID)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_SECURITY_TEMPLATE_PERMISSIONS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_SECURITY_TEMPLATE_PERMISSIONS
(
	WSEC_TMPL_ID 					int 			NOT NULL,
	WSEC_ACT_ID 					int 			NOT NULL, 
	WSEC_ALLOW 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WSEC_ALLOW >= 0) AND (WSEC_ALLOW <= 1)),
	WSEC_DENY 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WSEC_DENY >= 0) AND (WSEC_DENY <= 1)),
	WSEC_ACCESS 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WSEC_ACCESS >= 0) AND (WSEC_ACCESS <= 1)),
	CONSTRAINT PK_MSP_WEB_SECURITY_TEMPLATE_PERMISSIONS_WSEC_TMPL_ID_WSEC_ACT_ID PRIMARY KEY CLUSTERED (WSEC_TMPL_ID,WSEC_ACT_ID)	
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_SECURITY_TEMPLATES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_SECURITY_TEMPLATES
(
	WSEC_TMPL_ID 					int IDENTITY(1000,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_SECURITY_TEMPLATES_WSEC_TMPL_ID PRIMARY KEY CLUSTERED,
	WSEC_TMPL_NAME 					nvarchar(100) 		NOT NULL,
	WSEC_TMPL_DESC 					nvarchar(255) 		NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_STATUS_DISTRIBUTION
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_STATUS_DISTRIBUTION
(
	WRES_ID_DISTR_RECIP 				int 			NOT NULL,	
	WRESP_ID 					int 			NOT NULL, 			
	CONSTRAINT PK_MSP_WEB_STATUS_DISTRIBUTION_WRES_ID_DISTR_RECIP_WRESP_ID PRIMARY KEY CLUSTERED (WRES_ID_DISTR_RECIP, WRESP_ID)
)
-- ON [PRIMARY]
GO 

--****************************************************************************************************
--*		MSP_WEB_STATUS_FREQUENCIES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_STATUS_FREQUENCIES
(
	WSR_ID 						int 			NOT NULL,
	WREPORT_START_DATE 				datetime 		NOT NULL,
	WFREQ 						int 			NOT NULL,
	WFREQPAR1 					int 			NULL,
	WFREQPAR2 					int 			NULL,
	WFREQPAR3 					int 			NULL,
	WFREQPAR4 					int 			NULL,
	WFREQPAR5 					int 			NULL,
	WFREQPAR6 					int 			NULL,
	WFREQPAR_DATE 					datetime 		NULL,
	CONSTRAINT PK_MSP_WEB_STATUS_FREQUENCIES_WSR_ID_WREPORT_START_DATE PRIMARY KEY CLUSTERED (WSR_ID, WREPORT_START_DATE)
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_STATUS_REPORTS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_STATUS_REPORTS
(
	WSR_ID 						int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_STATUS_REPORTS_WSR_ID PRIMARY KEY NONCLUSTERED,
	WRES_ID_MGR 					int 			NOT NULL,		
	WREPORT_NAME 					nvarchar(255) 		NULL, 	
	WREPORT_UNREQUESTED 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WREPORT_UNREQUESTED >= 0) AND (WREPORT_UNREQUESTED <= 1)),
	WREPORT_IS_ENABLED 				tinyint 		NOT NULL DEFAULT 1 CHECK ((WREPORT_IS_ENABLED >= 0) AND (WREPORT_IS_ENABLED <= 1)),
	WREPORT_FORMAT 					ntext			NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_STATUS_REQUESTS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_STATUS_REQUESTS
(
	WRES_ID_RECEIVER 				int 			NOT NULL,	 
	WSR_ID 						int 			NOT NULL,		
	WDUE_ON 					datetime 		NULL,
	WREQ_IS_AUTOMERGE 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WREQ_IS_AUTOMERGE >= 0) AND (WREQ_IS_AUTOMERGE <= 1)),
	WREQ_IS_NEW_REQUEST 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WREQ_IS_NEW_REQUEST >= 0) AND (WREQ_IS_NEW_REQUEST <= 1)),
	WREQ_IS_SENT 					tinyint 		NOT NULL DEFAULT 0 CHECK ((WREQ_IS_SENT >= 0) AND (WREQ_IS_SENT <= 1)),
	WREQ_IS_ENABLED 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WREQ_IS_ENABLED >= 0) AND (WREQ_IS_ENABLED <= 1)),
	CONSTRAINT PK_MSP_WEB_STATUS_REQUESTS_WRES_ID_RECEIVER_WSR_ID PRIMARY KEY CLUSTERED (WSR_ID, WRES_ID_RECEIVER)
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_STATUS_RESPONSES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_STATUS_RESPONSES
(
	WRESP_ID 					int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_STATUS_RESPONSES_WRESP_ID PRIMARY KEY NONCLUSTERED,
	WRES_ID 					int 			NULL, 		 
	WSR_ID 						int 			NULL,  		
	WSRESP_PERIOD_START 				datetime 		NULL,
	WSRESP_PERIOD_FINISH 				datetime 		NULL,
	WSUBMIT_STATUS 					int 			NULL,
	WSUBMIT_DATE 					datetime 		NULL,
	WUPDATE_STATUS 					int 			NULL,
	WNUM_SECTIONS 					int 			NULL,
	WRESP_IS_MATCHING 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WRESP_IS_MATCHING >= 0) AND (WRESP_IS_MATCHING <= 1)),
	WRESP_IS_NEW_RESPONSE				tinyint 		NOT NULL DEFAULT 0 CHECK ((WRESP_IS_NEW_RESPONSE >= 0) AND (WRESP_IS_NEW_RESPONSE <= 1)),
	WRESP_IS_MERGED 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WRESP_IS_MERGED >= 0) AND (WRESP_IS_MERGED <= 1)),
	WRESP_TEXT 					ntext 			NULL
)
-- ON [PRIMARY]
GO


CREATE INDEX I_WSR_ID
ON dbo.MSP_WEB_STATUS_RESPONSES (WSR_ID, WRES_ID)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_STRING_TYPES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_STRING_TYPES
(
	STRING_TYPE_ID 					int 			NOT NULL,
	STRING_LANG_ID 					int 			NOT NULL,
	STRING_TYPE 					nvarchar(200) 		NULL
	CONSTRAINT PK_MSP_WEB_STRING_TYPES_STRING_TYPE_ID_STRING_LANG_ID PRIMARY KEY CLUSTERED (STRING_TYPE_ID, STRING_LANG_ID)
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_STS_SERVERS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_STS_SERVERS
(
	WSTS_SERVER_ID 					int IDENTITY(1,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_STS_SERVERS_WSTS_SERVER_ID PRIMARY KEY,
	WSTS_SERVER_NAME 				nvarchar(255) 		NULL DEFAULT 'NONE', 
	WSTS_SERVER_WEB_PORT 				int 			NOT NULL DEFAULT 80,
	WSTS_SERVER_ADMIN_PORT 				int 			NOT NULL DEFAULT 0,
	WSTS_WEB_PORT_IS_SSL 				tinyint 		NOT NULL DEFAULT 0,
	WSTS_ADMIN_PORT_IS_SSL 				tinyint 		NOT NULL DEFAULT 0,
	WSTS_MANAGED_PATH	  			nvarchar(60)		NULL DEFAULT 'projects',
	WSTS_EXTRANET_SERVER_NAME			nvarchar(255)		NULL,
        WSTS_ADMIN_SERVER_NAME				nvarchar(255)		NULL,
        WSTS_NETBIOS_SERVER_NAME			nvarchar(255)		NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_TRANSACTIONS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_TRANSACTIONS
(
	WTRANS_ID 					int IDENTITY(1,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_TRANSACTIONS_WTRANS_ID PRIMARY KEY,
	WASSN_ID 					int 			NOT NULL,		
	WTRANS_DATE 					datetime 		NULL,
	WTRANS_PROJ_UPDATE_DATE				datetime 		NULL,
	WTRANS_STATE 					int 			NOT NULL DEFAULT 0,
	WTRANS_TYPE 					int 			NOT NULL DEFAULT 0,
	WTRANS_ACTION 					int 			NOT NULL DEFAULT 0,
	WRES_ID_TRANS_SENDER 				int 			NULL,
	WDELEG_ID 					int 			NULL,
	WTRANS_DESC 					nvarchar(255) 		NULL,
   	WTRANS_ADJUST					tinyint			NOT NULL DEFAULT 0
)
-- ON [PRIMARY]
GO


CREATE INDEX IX_MSP_WEB_TRANSACTIONS_WASSN_ID
ON dbo.MSP_WEB_TRANSACTIONS(WASSN_ID)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_VIEW_DSNS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_VIEW_DSNS
(
	WDSN_ID 					int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_VIEW_DSNS_WDSN_ID PRIMARY KEY NONCLUSTERED, 
	WDSN_NAME 					nvarchar(255) 		NOT NULL,
	WDSN_DESCRIPTION 				nvarchar(255) 		NULL,
	WDSN_LOGIN_ID 					nvarchar(255) 		NULL,
	WDSN_PASSWORD 					nvarchar(255) 		NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_VIEW_FAVORITES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_VIEW_FAVORITES
(
	WVIEW_FAV_ID 					int IDENTITY(1,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_VIEW_FAVORITES_WVIEW_FAV_ID PRIMARY KEY CLUSTERED,
	WRES_ID 					int 			NOT NULL,		
	WVIEW_TYPE 					int 			NOT NULL,
	WPROJ_ID 					int  			NOT NULL, 	
	WVIEW_OWC_PIVOT_XML 				ntext 			NULL,
	WVIEW_OWC_CHART_XML 				ntext 			NULL,
	WVIEW_FAV_NAME 					nvarchar(100) 		NULL,
	WVIEW_FAV_URL 					ntext 			NULL,
	WVIEW_FAV_COLUMN_ORDER 				ntext 			NULL,
	WVIEW_FAV_AUTOFILTER 				ntext 			NULL,
	WSEC_MENU_ID 					int 			NOT NULL DEFAULT -99999, 
	WVIEW_DEFAULT_SETTINGS ntext NULL,
	WVIEW_TIMESTAMP uniqueidentifier NOT NULL DEFAULT ('{00000000-0000-0000-0000-000000000000}')
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_VIEW_FIELDS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_VIEW_FIELDS
(
	WFIELD_ID 					int 			NOT NULL CONSTRAINT PK_MSP_WEB_VIEW_FIELDS_WFIELD_ID PRIMARY KEY NONCLUSTERED,
	WFIELD_NAME_OLEDB 				nvarchar(255) 		NULL,
	WFIELD_NAME_SQL 				nvarchar(255) 		NULL,
	WFIELD_NAME_CONV_VALUE 				int 			NULL,
	WFIELD_TEXTCONV_TYPE 				int 			NULL,
	WTABLE_ID 					int 			NULL,		
	WFIELD_IN_PROJECT_VIEW 				tinyint 		NOT NULL DEFAULT 0,
	WFIELD_IN_PORTFOLIO_VIEW 			tinyint 		NOT NULL DEFAULT 0,
	WFIELD_IN_WEBCLIENT_VIEW 			tinyint 		NOT NULL DEFAULT 0,
	WFIELD_IS_CUSTOM_FIELD 				tinyint 		NOT NULL DEFAULT 0,
	WFIELD_IS_GANTT_DEFAULT 			tinyint 		NOT NULL DEFAULT 0,
	WFIELD_IS_ENTRES_FIELD 				tinyint 		NOT NULL DEFAULT 0
)
-- ON [PRIMARY]
GO


CREATE INDEX I_WFIELD_NAME_OLEDB 
ON dbo.MSP_WEB_VIEW_FIELDS (WFIELD_NAME_OLEDB)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_VIEW_REPORTS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_VIEW_REPORTS
(
	WVIEW_ID 					int IDENTITY(100,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_VIEW_REPORTS_WVIEW_ID PRIMARY KEY NONCLUSTERED,
	WVIEW_NAME 					nvarchar(255) 		NOT NULL,
	WVIEW_DESCRIPTION 				nvarchar(255) 		NULL,
	WVIEW_TYPE 					int 			NULL,
	WVIEW_DISPLAY_TYPE 				int 			NULL,
	WVIEW_WORK_TYPE 				int 			NULL,
	WGANTT_SCHEME_ID 				int 			NULL,
	WTABLE_ID 					int 			NULL,		
	WVIEW_FILTER_PARAM1 				nvarchar(255) 		NULL,
	WVIEW_FILTER_PARAM2 				nvarchar(255) 		NULL,
	WVIEW_FILTER_PARAM3 				nvarchar(255) 		NULL,
	WVIEW_REPORT_KIND 				int 			NOT NULL DEFAULT 0,
	WVIEW_PATH 					nvarchar(1024) 		NULL,
	WVIEW_OWC_PIVOT_XML 				ntext 			NULL,
	WVIEW_OWC_CHART_XML 				ntext 			NULL,
	WGROUP_SCHEME_ID 				int 			NULL,
	WVIEW_DEFAULT_SETTINGS 				ntext 			NULL,
	WVIEW_TIMESTAMP 				uniqueidentifier 	NOT NULL DEFAULT ('{00000000-0000-0000-0000-000000000000}'),
        WVIEW_GROUPING_SORTING_PARAMS 			nvarchar(256) 		NULL,
        WVIEW_FILTER_BY_RBS 				tinyint 		NOT NULL DEFAULT 0 CHECK ((WVIEW_FILTER_BY_RBS >= 0) AND (WVIEW_FILTER_BY_RBS <= 1)),
        WVIEW_OUTLINE_LEVEL                             smallint                NOT NULL DEFAULT -1,
        WVIEW_SPLITTER_POS                              int                     NOT NULL DEFAULT 400
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_VIEW_REPORTS_FIELDS
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_VIEW_REPORTS_FIELDS
(
	WVIEW_ID 					int 			NOT NULL,			
	WFIELD_ID 					int 			NOT NULL,			
	WVIEW_FIELD_ORDER 				int 			NULL,
        WVIEW_FIELD_WIDTH                               int                     NOT NULL DEFAULT 100,
        WVIEW_FIELD_AUTOSIZE                            tinyint                 NOT NULL DEFAULT 1,
	CONSTRAINT PK_MSP_WEB_VIEW_REPORTS_FIELDS_WVIEW_ID_WFIELD_ID PRIMARY KEY CLUSTERED  (WVIEW_ID, WFIELD_ID)
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_VIEW_TABLES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_VIEW_TABLES
(
	WTABLE_ID 					int 			NOT NULL CONSTRAINT PK_MSP_WEB_VIEW_TABLES_WTABLE_ID PRIMARY KEY NONCLUSTERED, 
	WTABLE_NAME 					nvarchar(255) 		NOT NULL
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_WORK
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_WORK
(
	WRES_ID 					int 			NOT NULL,		
	WASSN_ID 					int 			NOT NULL,		
	WWORK_START 					datetime 		NOT NULL,
	WWORK_FINISH 					datetime 		NOT NULL,
	WWORK_TYPE 					int 			NOT NULL,
	WWORK_VALUE 					decimal(25,6) 		NULL,
	WWORK_UPDATE_STATUS 				int 			NOT NULL DEFAULT 0,
	WWORK_READONLY 					tinyint 		NOT NULL DEFAULT 0,
	RESERVED_DATA1 					int 			NULL DEFAULT 0,
        WWORK_APPROVAL_STATUS                           int                     NOT NULL DEFAULT 0,
	CONSTRAINT PK_MSP_WEB_WORK_WASSN_ID_WWORK_START_WWORK_FINISH_WWORK_TYPE PRIMARY KEY NONCLUSTERED (WASSN_ID, WWORK_START, WWORK_FINISH, WWORK_TYPE)
)
-- ON [PRIMARY]
GO

CREATE CLUSTERED INDEX I_ACTUALS_WRESID_WASSNID 
ON dbo.MSP_WEB_WORK (WRES_ID, WASSN_ID) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_WORKGROUP_FIELDS
--****************************************************************************************************


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


CREATE TABLE dbo.MSP_WEB_WORKGROUP_FIELDS
(
	WASSN_ID 					int 			NOT NULL, 
	FIELD_ID 					int 			NOT NULL,
	CUSTFIELD_TYPE 					int 			NULL,
	INT_VAL 					int 			NULL,
	DATE_VAL 					datetime 		NULL,
	DECIMAL_VAL 					decimal(25,6) 		NULL,
	VARCHAR_VAL 					nvarchar(255) 		NULL,
	INDICATOR_VAL 					int 			NULL,
	DURATION_UNIT 					int 			NULL,
	WWORKGRP_UPDATE_STATUS 				int 			NOT NULL DEFAULT 0,
	WWORKGRP_INFO_IS_READONLY 			tinyint 		NOT NULL DEFAULT 0 CHECK ((WWORKGRP_INFO_IS_READONLY >= 0) AND (WWORKGRP_INFO_IS_READONLY <= 1))
	CONSTRAINT PK_MSP_WEB_WORKGROUP_FIELDS_WASSN_ID_FIELD_ID PRIMARY KEY CLUSTERED (WASSN_ID, FIELD_ID)
)
-- ON [PRIMARY]
GO

--****************************************************************************************************
--*		MSP_WEB_WORKGROUP_FIELDS_INFO
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_WORKGROUP_FIELDS_INFO
(
	CUSTFIELD_INFO_ID 				int IDENTITY(10000,1) 	NOT NULL, 
	FIELD_ID 					int 			NOT NULL,  
	CUSTFIELD_NAME 					nvarchar(255) 		NULL,  
	CONV_VALUE 					int 			NULL
)
-- ON [PRIMARY]
GO


ALTER TABLE dbo.MSP_WEB_WORKGROUP_FIELDS_INFO ADD CONSTRAINT
  PK_MSP_WEB_WORKGROUP_FIELDS_INFO PRIMARY KEY CLUSTERED (CUSTFIELD_INFO_ID)
GO


CREATE NONCLUSTERED INDEX I_MSP_WEB_WORKGROUP_FIELDS_INFO_FIELD_ID 
ON dbo.MSP_WEB_WORKGROUP_FIELDS_INFO (FIELD_ID)
WITH FILLFACTOR = 40
GO

--**************************************************************************************************
--*		MSP_WEB_ADMIN_AD
--**************************************************************************************************

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

CREATE TABLE dbo.MSP_WEB_ADMIN_AD 
(
	WADMIN_AD_ID 					int IDENTITY(1,1) 	NOT NULL,
	WADMIN_AD_GRP_ENABLE_UPDATE 			tinyint 		NOT NULL DEFAULT 0 CHECK (WADMIN_AD_GRP_ENABLE_UPDATE IN (0, 1)),
	WADMIN_AD_GRP_AUTO_LAST_RUN 			datetime 		NULL,
	WADMIN_AD_GRP_LOG_TIMESTAMP 			datetime 		NULL,
	WADMIN_AD_GRP_LOG_STATUS 			int 			NULL CHECK (WADMIN_AD_GRP_LOG_STATUS IN (0, 1, 2)),
	WADMIN_AD_GRP_LOG_STATUS_TEXT 			nvarchar(255) 		NULL,
	WADMIN_AD_GRP_UPDATE_FREQ_EVERY 		int 			NOT NULL DEFAULT 0,
	WADMIN_AD_GRP_UPDATE_FREQ_UNIT 			int 			NOT NULL DEFAULT 1 CHECK (WADMIN_AD_GRP_UPDATE_FREQ_UNIT IN (0, 1, 2)),
	WADMIN_AD_GRP_START_DATE 			datetime 		NULL,
	WADMIN_AD_GRP_START_TIME 			int 			NULL,
	WADMIN_AD_GRP_UPDATE				tinyint 		NULL, 
	WADMIN_AD_ERESPOOL_ENABLE_UPDATE 		tinyint 		NOT NULL DEFAULT 0 CHECK (WADMIN_AD_ERESPOOL_ENABLE_UPDATE IN (0, 1)),
	WADMIN_AD_ERESPOOL_GROUP 			nvarchar(64) 		NULL,
	WADMIN_AD_ERESPOOL_AUTO_LAST_RUN 		datetime 		NULL,
	WADMIN_AD_ERESPOOL_LOG_TIMESTAMP 		datetime 		NULL,
	WADMIN_AD_ERESPOOL_LOG_STATUS 			int 			NULL CHECK (WADMIN_AD_ERESPOOL_LOG_STATUS IN (0, 1, 2, 3)),
	WADMIN_AD_ERESPOOL_LOG_STATUS_TEXT 		nvarchar(255) 		NULL,
	WADMIN_AD_ERESPOOL_UPDATE_FREQ_EVERY 		int 			NOT NULL DEFAULT 0,
	WADMIN_AD_ERESPOOL_UPDATE_FREQ_UNIT 		int 			NOT NULL DEFAULT 1 CHECK (WADMIN_AD_ERESPOOL_UPDATE_FREQ_UNIT IN (0, 1, 2)),
	WADMIN_AD_ERESPOOL_START_DATE 			datetime 		NULL,
	WADMIN_AD_ERESPOOL_START_TIME 			int 			NULL,
	WADMIN_AD_ERESPOOL_UPDATE 			tinyint 		NOT NULL DEFAULT 0
)
-- ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_WEB_TIME_PERIODS
--**************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_TIME_PERIODS 
(
   WPRD_ID 						int IDENTITY(100,1) 	NOT NULL,
   WPRD_START_DATE 					datetime 		NOT NULL,
   WPRD_FINISH_DATE 					datetime 		NOT NULL,
   WPRD_STATE 						int 			NOT NULL DEFAULT 0,
   RESERVED_DATA1 					float 			NOT NULL DEFAULT 0
)
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_WEB_WORK_ADJUSTED
--**************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_WORK_ADJUSTED 
(
   WADJUST_ID						int IDENTITY(100,1) 	NOT NULL,
   WASSN_ID 						int			NOT NULL,
   WWORK_DATE						datetime 		NOT NULL,
   WWORK_TYPE 						int 			NOT NULL,
   WWORK_VALUE						decimal(25,6) 		NOT NULL,
   WWORK_VALUE_ADJUSTED					decimal(25,6) 		NOT NULL,
   WWORK_ADJUST_DATE 					datetime		NOT NULL,
   WRES_ID 						int 			NOT NULL
)
-- ON [PRIMARY]
GO



--**************************************************************************************************
--*		MSP_WEB_WORK_APPROVAL
--**************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_WORK_APPROVAL 
(
   WAPPROVAL_ID						int IDENTITY(1,1) 	NOT NULL CONSTRAINT PK_MSP_WEB_WORK_APPROVAL_WAPPROVAL_ID PRIMARY KEY NONCLUSTERED, 
   WASSN_ID 						int			NOT NULL,
   WRES_ID 						int 			NOT NULL,
   WPRD_START_DATE					datetime 		NOT NULL,
   WPRD_FINISH_DATE					datetime		NOT NULL,
   WRES_ID_APPROVER 					int 			NOT NULL DEFAULT -1,
   WAPPROVAL_STATUS					tinyint			NOT NULL DEFAULT 0,
   WAPPROVAL_NOTIFICATION_SENT     			tinyint			NOT NULL DEFAULT 0
)
-- ON [PRIMARY]
GO

CREATE CLUSTERED INDEX I_APPROVAL_WRESID_WASSNID 
ON dbo.MSP_WEB_WORK_APPROVAL (WRES_ID, WASSN_ID, WAPPROVAL_ID) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO

CREATE INDEX I_APPROVAL_WASSNID_START_FINISH 
ON dbo.MSP_WEB_WORK_APPROVAL (WASSN_ID, WPRD_START_DATE, WPRD_FINISH_DATE) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO

CREATE INDEX I_APPROVAL_WASSNID_WRESID_NOTIF_SENT_STATUS
ON dbo.MSP_WEB_WORK_APPROVAL (WASSN_ID, WRES_ID, WAPPROVAL_NOTIFICATION_SENT, WAPPROVAL_STATUS) WITH FILLFACTOR = 40
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_VERSIONS
--**************************************************************************************************

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


CREATE TABLE dbo.MSP_VERSIONS
(
   VERS_ID 				int IDENTITY(1, 1) 	NOT NULL CONSTRAINT PK_MSP_VERSIONS_VERS_ID PRIMARY KEY CLUSTERED,
   VERS_VERSION 			varchar(50) 		NOT NULL,
   VERS_DEFAULT 			tinyint 		NOT NULL DEFAULT 0 CHECK ((VERS_DEFAULT >= 0) AND (VERS_DEFAULT <= 1)),
   VERS_TYPE 				int 			NOT NULL DEFAULT 0,
   VERS_WGANTT_STYLE_ID 		int 			NOT NULL DEFAULT 6
)
-- ON [PRIMARY]
GO

--**************************************************************************************************
--*		MSP_WEB_SECURITY_USERDEFINED
--**************************************************************************************************

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

CREATE TABLE dbo.MSP_WEB_SECURITY_USERDEFINED
(
	WSEC_PERMISSION_NUMBER int NOT NULL,	
	WSEC_ITEM_TYPE int NOT NULL,
	WSEC_ITEM_ID int NOT NULL	
)
GO

CREATE CLUSTERED INDEX I_WEB_SECURITY_USERDEFINED_PERM_NUMBER
ON dbo.MSP_WEB_SECURITY_USERDEFINED (WSEC_PERMISSION_NUMBER)
WITH FILLFACTOR = 40
GO

--****************************************************************************************************
--*		MSP_WEB_WSS_LIST_SYNCHRONIZE_TIMES
--****************************************************************************************************

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


CREATE TABLE dbo.MSP_WEB_WSS_LIST_SYNCHRONIZE_TIMES
(
  WPROJ_ID				int			NOT NULL,
  WOBJ_LIST_NAME			uniqueidentifier	NOT NULL,
  WPROJ_WSS_LAST_LIST_SYNC_TIME		nvarchar(50) 		NOT NULL DEFAULT (N'2000-01-01T00:00:00Z')
)
-- ON PRIMARY
GO


ALTER TABLE dbo.MSP_WEB_WSS_LIST_SYNCHRONIZE_TIMES ADD CONSTRAINT
  PK_MSP_WEB_WSS_LIST_SYNCHRONIZE_TIMES PRIMARY KEY CLUSTERED (WPROJ_ID, WOBJ_LIST_NAME)
GO



--  End of script  --

