/****************************************************************************************************
**
**	This script will create the SQL Server tables needed for Microsoft Project data integrity
**
**	Privileges needed: member of db_owner or db_ddladmin
**
****************************************************************************************************/
IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_BACKUP_ASSN')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_BACKUP_ASSN
GO


IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_BACKUP_LINK')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_BACKUP_LINK
GO


IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_BACKUP_RES')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_BACKUP_RES
GO

IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_BACKUP_TASK')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.MSP_BACKUP_TASK
GO




CREATE PROCEDURE dbo.MSP_BACKUP_ASSN

	@PROJ_ID							INTEGER,
	@ASSN_UID							INTEGER
AS

	DECLARE @VALUE_LIST					VARCHAR(255)
	DECLARE @ASSN_UNITS					VARCHAR(255)
	DECLARE @ASSN_START_DATE			VARCHAR(255)
	DECLARE @ASSN_FINISH_DATE			VARCHAR(255)
	DECLARE @ASSN_DELAY					VARCHAR(255)
	DECLARE @ASSN_LEVELING_DELAY		VARCHAR(255)
	DECLARE @ASSN_ACT_START				VARCHAR(255)
	DECLARE @ASSN_ACT_FINISH			VARCHAR(255)
	DECLARE @ASSN_ACT_WORK				VARCHAR(255)
	DECLARE @ASSN_REM_WORK				VARCHAR(255)
	DECLARE @ASSN_REG_WORK				VARCHAR(255)
	DECLARE @ASSN_WORK					VARCHAR(255)
	DECLARE @ASSN_ACT_OVT_WORK			VARCHAR(255)
	DECLARE @ASSN_REM_OVT_WORK			VARCHAR(255)
	DECLARE @ASSN_OVT_WORK				VARCHAR(255)

	SELECT
		@ASSN_UNITS = CONVERT(VARCHAR(255),ASSN_UNITS),
		@ASSN_START_DATE = CONVERT(VARCHAR(255),ASSN_START_DATE),
		@ASSN_FINISH_DATE = CONVERT(VARCHAR(255),ASSN_FINISH_DATE),
		@ASSN_DELAY = CONVERT(VARCHAR(255),ASSN_DELAY),
		@ASSN_LEVELING_DELAY = CONVERT(VARCHAR(255),ASSN_LEVELING_DELAY),
		@ASSN_ACT_START = CONVERT(VARCHAR(255),ASSN_ACT_START),
		@ASSN_ACT_FINISH = CONVERT(VARCHAR(255),ASSN_ACT_FINISH),
		@ASSN_ACT_WORK = CONVERT(VARCHAR(255),ASSN_ACT_WORK),
		@ASSN_REM_WORK = CONVERT(VARCHAR(255),ASSN_REM_WORK),
		@ASSN_REG_WORK = CONVERT(VARCHAR(255),ASSN_REG_WORK),
		@ASSN_WORK = CONVERT(VARCHAR(255),ASSN_WORK),
		@ASSN_ACT_OVT_WORK = CONVERT(VARCHAR(255),ASSN_ACT_OVT_WORK),
		@ASSN_REM_OVT_WORK = CONVERT(VARCHAR(255),ASSN_REM_OVT_WORK),
		@ASSN_OVT_WORK = CONVERT(VARCHAR(255),ASSN_OVT_WORK)
	FROM
		MSP_ASSIGNMENTS
	WHERE
		PROJ_ID = @PROJ_ID AND
		ASSN_UID = @ASSN_UID

	SELECT @VALUE_LIST = '1,'
	IF (@ASSN_UNITS IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @ASSN_UNITS
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@ASSN_START_DATE IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @ASSN_START_DATE
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@ASSN_FINISH_DATE IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @ASSN_FINISH_DATE
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@ASSN_DELAY IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @ASSN_DELAY
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@ASSN_LEVELING_DELAY IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @ASSN_LEVELING_DELAY
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@ASSN_ACT_START IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @ASSN_ACT_START
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@ASSN_ACT_FINISH IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @ASSN_ACT_FINISH
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@ASSN_ACT_WORK IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @ASSN_ACT_WORK
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@ASSN_REM_WORK IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @ASSN_REM_WORK
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@ASSN_REG_WORK IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @ASSN_REG_WORK
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@ASSN_WORK IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @ASSN_WORK
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@ASSN_ACT_OVT_WORK IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @ASSN_ACT_OVT_WORK
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@ASSN_REM_OVT_WORK IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @ASSN_REM_OVT_WORK
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@ASSN_OVT_WORK IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @ASSN_OVT_WORK

	UPDATE MSP_ASSIGNMENTS SET EXT_EDIT_REF_DATA = @VALUE_LIST WHERE PROJ_ID = @PROJ_ID AND ASSN_UID = @ASSN_UID

GO


CREATE PROCEDURE dbo.MSP_BACKUP_LINK

	@PROJ_ID				INTEGER,
	@LINK_UID				INTEGER
AS

	DECLARE @VALUE_LIST			VARCHAR(255)
	DECLARE @LINK_TYPE			VARCHAR(255)
	DECLARE @LINK_LAG			VARCHAR(255)

	SELECT
		@LINK_TYPE = CONVERT(VARCHAR(255),LINK_TYPE),
		@LINK_LAG = CONVERT(VARCHAR(255),LINK_LAG)
	FROM
		MSP_LINKS
	WHERE
		PROJ_ID = @PROJ_ID AND
		LINK_UID = @LINK_UID

	SELECT @VALUE_LIST = '1,'
	IF (@LINK_TYPE IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @LINK_TYPE
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@LINK_LAG IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @LINK_LAG

	UPDATE MSP_LINKS SET EXT_EDIT_REF_DATA = @VALUE_LIST WHERE PROJ_ID = @PROJ_ID AND LINK_UID = @LINK_UID

GO

CREATE PROCEDURE dbo.MSP_BACKUP_RES

	@PROJ_ID				INTEGER,
	@RES_UID				INTEGER
AS

	DECLARE @VALUE_LIST			VARCHAR(255)
	DECLARE @RES_ACCRUE_AT			VARCHAR(255)
	DECLARE @RES_AVAIL_FROM			VARCHAR(255)
	DECLARE @RES_AVAIL_TO			VARCHAR(255)

	SELECT
		@RES_ACCRUE_AT = CONVERT(VARCHAR(255),RES_ACCRUE_AT),
		@RES_AVAIL_FROM = CONVERT(VARCHAR(255),RES_AVAIL_FROM),
		@RES_AVAIL_TO = CONVERT(VARCHAR(255),RES_AVAIL_TO)
	FROM
		MSP_RESOURCES
	WHERE
		PROJ_ID = @PROJ_ID AND
		RES_UID = @RES_UID

	SELECT @VALUE_LIST = '1,'
	IF (@RES_ACCRUE_AT IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @RES_ACCRUE_AT
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@RES_AVAIL_FROM IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @RES_AVAIL_FROM
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@RES_AVAIL_TO IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @RES_AVAIL_TO

	UPDATE MSP_RESOURCES SET EXT_EDIT_REF_DATA = @VALUE_LIST WHERE PROJ_ID = @PROJ_ID AND RES_UID = @RES_UID

GO


CREATE PROCEDURE dbo.MSP_BACKUP_TASK

	@PROJ_ID				INTEGER,
	@TASK_UID				INTEGER
AS
  
	DECLARE @VALUE_LIST				VARCHAR(255)
	DECLARE @TASK_ACT_WORK			VARCHAR(255)
	DECLARE @TASK_WORK				VARCHAR(255)
	DECLARE @TASK_REM_WORK			VARCHAR(255)
	DECLARE @TASK_DUR				VARCHAR(255)
	DECLARE @TASK_ACT_DUR			VARCHAR(255)
	DECLARE @TASK_REM_DUR			VARCHAR(255)
	DECLARE @TASK_START_DATE		VARCHAR(255)
	DECLARE @TASK_FINISH_DATE		VARCHAR(255)
	DECLARE @TASK_CONSTRAINT_TYPE	VARCHAR(255)
	DECLARE @TASK_CONSTRAINT_DATE	VARCHAR(255)
	DECLARE @TASK_ACT_START			VARCHAR(255)
	DECLARE @TASK_ACT_FINISH		VARCHAR(255)
	DECLARE @TASK_STOP_DATE			VARCHAR(255)
	DECLARE @TASK_RESUME_DATE		VARCHAR(255)
	DECLARE @TASK_PCT_COMP			VARCHAR(255)
	DECLARE @TASK_COST				VARCHAR(255)
	DECLARE @TASK_FIXED_COST		VARCHAR(255)
	DECLARE @TASK_ACT_COST			VARCHAR(255)
	DECLARE @TASK_LEVELING_DELAY	VARCHAR(255)
	DECLARE @TASK_PCT_WORK_COMP		VARCHAR(255)
	DECLARE @TASK_FIXED_COST_ACCRUAL	VARCHAR(255)

	SELECT
		@TASK_ACT_WORK = CONVERT(VARCHAR(255),TASK_ACT_WORK),
		@TASK_WORK = CONVERT(VARCHAR(255),TASK_WORK),
		@TASK_REM_WORK = CONVERT(VARCHAR(255),TASK_REM_WORK),
		@TASK_DUR = CONVERT(VARCHAR(255),TASK_DUR),
		@TASK_ACT_DUR = CONVERT(VARCHAR(255),TASK_ACT_DUR),
		@TASK_REM_DUR = CONVERT(VARCHAR(255),TASK_REM_DUR),
		@TASK_START_DATE = CONVERT(VARCHAR(255),TASK_START_DATE),
		@TASK_FINISH_DATE = CONVERT(VARCHAR(255),TASK_FINISH_DATE),
		@TASK_CONSTRAINT_TYPE = CONVERT(VARCHAR(255),TASK_CONSTRAINT_TYPE),
		@TASK_CONSTRAINT_DATE = CONVERT(VARCHAR(255),TASK_CONSTRAINT_DATE),
		@TASK_ACT_START = CONVERT(VARCHAR(255),TASK_ACT_START),
		@TASK_ACT_FINISH = CONVERT(VARCHAR(255),TASK_ACT_FINISH),
		@TASK_STOP_DATE = CONVERT(VARCHAR(255),TASK_STOP_DATE),
		@TASK_RESUME_DATE = CONVERT(VARCHAR(255),TASK_RESUME_DATE),
		@TASK_PCT_COMP = CONVERT(VARCHAR(255),TASK_PCT_COMP),
		@TASK_COST = CONVERT(VARCHAR(255),TASK_COST),
		@TASK_FIXED_COST = CONVERT(VARCHAR(255),TASK_FIXED_COST),
		@TASK_ACT_COST = CONVERT(VARCHAR(255),TASK_ACT_COST),
		@TASK_LEVELING_DELAY = CONVERT(VARCHAR(255),TASK_LEVELING_DELAY),
		@TASK_PCT_WORK_COMP = CONVERT(VARCHAR(255),TASK_PCT_WORK_COMP),
		@TASK_FIXED_COST_ACCRUAL = CONVERT(VARCHAR(255),TASK_FIXED_COST_ACCRUAL)
	FROM
		MSP_TASKS
	WHERE
		PROJ_ID = @PROJ_ID AND
		TASK_UID = @TASK_UID

	SELECT @VALUE_LIST = '1,'
	IF (@TASK_ACT_WORK IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_ACT_WORK
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_WORK IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_WORK
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_REM_WORK IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_REM_WORK
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_DUR IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_DUR
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_ACT_DUR IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_ACT_DUR
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_REM_DUR IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_REM_DUR
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_START_DATE IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_START_DATE
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_FINISH_DATE IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_FINISH_DATE
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_CONSTRAINT_TYPE IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_CONSTRAINT_TYPE
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_CONSTRAINT_DATE IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_CONSTRAINT_DATE
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_ACT_START IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_ACT_START
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_ACT_FINISH IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_ACT_FINISH
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_STOP_DATE IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_STOP_DATE
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_RESUME_DATE IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_RESUME_DATE
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_PCT_COMP IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_PCT_COMP
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_COST IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_COST
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_FIXED_COST IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_FIXED_COST
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_ACT_COST IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_ACT_COST
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_LEVELING_DELAY IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_LEVELING_DELAY
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_PCT_WORK_COMP IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_PCT_WORK_COMP
	SELECT @VALUE_LIST = @VALUE_LIST + ','
	IF (@TASK_FIXED_COST_ACCRUAL IS NOT NULL) SELECT @VALUE_LIST = @VALUE_LIST + @TASK_FIXED_COST_ACCRUAL

	UPDATE MSP_TASKS SET EXT_EDIT_REF_DATA = @VALUE_LIST WHERE PROJ_ID = @PROJ_ID AND TASK_UID = @TASK_UID

GO

 

if exists (select * from sysobjects where id = object_id(N'[dbo].[MSP_GET_GLB_DATA]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MSP_GET_GLB_DATA]
GO


create procedure dbo.MSP_GET_GLB_DATA
            @proj_id integer,
            @assn_ent_proj_id integer,
            @assn_uid integer,
	    @cntr_category integer

as
select GLB_BINARY_DATA
from MSP_RES_GLOBAL_BINARY_RES_READVIEW
where (PROJ_ID = @proj_id)
            and (GLB_CATEGORY = @cntr_category)
            and (GLB_REF_ENT_PROJ_ID = @assn_ent_proj_id)
            and (GLB_REF_UID = @assn_uid)
GO


GRANT  EXECUTE  ON [dbo].[MSP_GET_GLB_DATA]  TO [MSProjectRole]
GO

 
if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_get_glb_data]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_get_glb_data]
GO

 

 

create procedure dbo. sp_get_glb_data
            @proj_id integer,
            @assn_ent_proj_id integer,
            @assn_uid integer,
            @cntr_category integer

 

as

select GLB_BINARY_DATA
from MSP_RES_GLOBAL_BINARY_RES_READVIEW
where (PROJ_ID = @proj_id)
            and (GLB_CATEGORY = @cntr_category)
            and (GLB_REF_ENT_PROJ_ID = @assn_ent_proj_id)
            and (GLB_REF_UID = @assn_uid)
GO

 

 

GRANT  EXECUTE  ON [dbo].[sp_get_glb_data]  TO [MSProjectRole]
GO

