vkhaidanov
/
EnVisageOnline
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
EnVisageOnline/EnVisage1_EnVisageQA2160316...

4950 lines
490 KiB
Transact-SQL
Raw Permalink Blame History

This file contains invisible Unicode characters

This file contains invisible Unicode characters that are indistinguishable to humans but may be processed differently by a computer. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

/*
Script created by SQL Examiner 6.0.0.114 at 03/16/2016 22:11:22.
Run this script on (local).EnVisage1 to make it the same as C:\DevProjects\EnVisageOnline\EnVisageQA2160316.bak
*/
USE [EnVisage1]
GO
--step 1: create user envisage----------------------------------------------------------------------
CREATE USER [envisage] WITHOUT LOGIN WITH DEFAULT_SCHEMA = [dbo]
GO
--step 2: add security account envisage to the role db_owner----------------------------------------
EXEC sp_addrolemember N'db_owner', N'envisage'
GO
--step 3: create user envisageQA--------------------------------------------------------------------
CREATE USER [envisageQA] WITHOUT LOGIN WITH DEFAULT_SCHEMA = [dbo]
GO
--step 4: add security account envisageQA to the role db_owner--------------------------------------
EXEC sp_addrolemember N'db_owner', N'envisageQA'
GO
SET NOCOUNT ON
SET NOEXEC OFF
SET ARITHABORT ON
SET XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRAN
GO
--step 5: create table dbo.__MigrationHistory-------------------------------------------------------
CREATE TABLE [dbo].[__MigrationHistory] (
[MigrationId] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ContextKey] [nvarchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Model] [varbinary](max) NOT NULL,
[ProductVersion] [nvarchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 5 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 5 is completed with errors' SET NOEXEC ON END
GO
--step 6: dbo.__MigrationHistory: add primary key PK_dbo.__MigrationHistory-------------------------
ALTER TABLE [dbo].[__MigrationHistory] ADD CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY CLUSTERED ([MigrationId], [ContextKey])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 6 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 6 is completed with errors' SET NOEXEC ON END
GO
--step 7: create table dbo.AspNetRoles--------------------------------------------------------------
CREATE TABLE [dbo].[AspNetRoles] (
[Id] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Name] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 7 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 7 is completed with errors' SET NOEXEC ON END
GO
--step 8: dbo.AspNetRoles: add primary key PK_dbo.AspNetRoles---------------------------------------
ALTER TABLE [dbo].[AspNetRoles] ADD CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 8 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 8 is completed with errors' SET NOEXEC ON END
GO
--step 9: create table dbo.AspNetUserClaims---------------------------------------------------------
CREATE TABLE [dbo].[AspNetUserClaims] (
[Id] [int] IDENTITY(1, 1) NOT NULL,
[ClaimType] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClaimValue] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[User_Id] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 9 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 9 is completed with errors' SET NOEXEC ON END
GO
--step 10: add index IX_User_Id to table dbo.AspNetUserClaims---------------------------------------
CREATE NONCLUSTERED INDEX [IX_User_Id] ON [dbo].[AspNetUserClaims]([User_Id]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 10 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 10 is completed with errors' SET NOEXEC ON END
GO
--step 11: dbo.AspNetUserClaims: add primary key PK_dbo.AspNetUserClaims----------------------------
ALTER TABLE [dbo].[AspNetUserClaims] ADD CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 11 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 11 is completed with errors' SET NOEXEC ON END
GO
--step 12: create table dbo.AspNetUsers-------------------------------------------------------------
CREATE TABLE [dbo].[AspNetUsers] (
[Id] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UserName] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PasswordHash] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SecurityStamp] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Discriminator] [nvarchar](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [nvarchar](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone] [nvarchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [smallint] NOT NULL,
[PreferredResourceAllocation] [bit] NOT NULL,
[PreferredTotalsDisplaying] [bit] NOT NULL,
[FirstName] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastLoginDate] [datetime] NULL,
[LoginDate] [datetime] NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 12 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 12 is completed with errors' SET NOEXEC ON END
GO
--step 13: dbo.AspNetUsers: add default DF__AspNetUser__Type__0C85DE4D------------------------------
ALTER TABLE [dbo].[AspNetUsers] ADD CONSTRAINT [DF__AspNetUser__Type__0C85DE4D] DEFAULT ((0)) FOR [Type]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 13 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 13 is completed with errors' SET NOEXEC ON END
GO
--step 14: dbo.AspNetUsers: add default DF_AspNetUsers_PreferredResourceAllocation------------------
ALTER TABLE [dbo].[AspNetUsers] ADD CONSTRAINT [DF_AspNetUsers_PreferredResourceAllocation] DEFAULT ((0)) FOR [PreferredResourceAllocation]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 14 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 14 is completed with errors' SET NOEXEC ON END
GO
--step 15: dbo.AspNetUsers: add primary key PK_dbo.AspNetUsers--------------------------------------
ALTER TABLE [dbo].[AspNetUsers] ADD CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 15 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 15 is completed with errors' SET NOEXEC ON END
GO
--step 16: dbo.AspNetUserClaims: add foreign key FK_dbo.AspNetUserClaims_dbo.AspNetUsers_User_Id----
ALTER TABLE [dbo].[AspNetUserClaims] ADD CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_User_Id] FOREIGN KEY ([User_Id]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 16 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 16 is completed with errors' SET NOEXEC ON END
GO
--step 17: create table dbo.AspNetUserLogins--------------------------------------------------------
CREATE TABLE [dbo].[AspNetUserLogins] (
[UserId] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LoginProvider] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ProviderKey] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 17 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 17 is completed with errors' SET NOEXEC ON END
GO
--step 18: add index IX_UserId to table dbo.AspNetUserLogins----------------------------------------
CREATE NONCLUSTERED INDEX [IX_UserId] ON [dbo].[AspNetUserLogins]([UserId]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 18 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 18 is completed with errors' SET NOEXEC ON END
GO
--step 19: dbo.AspNetUserLogins: add primary key PK_dbo.AspNetUserLogins----------------------------
ALTER TABLE [dbo].[AspNetUserLogins] ADD CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED ([UserId], [LoginProvider], [ProviderKey])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 19 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 19 is completed with errors' SET NOEXEC ON END
GO
--step 20: dbo.AspNetUserLogins: add foreign key FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId-----
ALTER TABLE [dbo].[AspNetUserLogins] ADD CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 20 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 20 is completed with errors' SET NOEXEC ON END
GO
--step 21: create table dbo.AspNetUserRoles---------------------------------------------------------
CREATE TABLE [dbo].[AspNetUserRoles] (
[UserId] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RoleId] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 21 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 21 is completed with errors' SET NOEXEC ON END
GO
--step 22: add index IX_RoleId to table dbo.AspNetUserRoles-----------------------------------------
CREATE NONCLUSTERED INDEX [IX_RoleId] ON [dbo].[AspNetUserRoles]([RoleId]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 22 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 22 is completed with errors' SET NOEXEC ON END
GO
--step 23: add index IX_UserId to table dbo.AspNetUserRoles-----------------------------------------
CREATE NONCLUSTERED INDEX [IX_UserId] ON [dbo].[AspNetUserRoles]([UserId]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 23 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 23 is completed with errors' SET NOEXEC ON END
GO
--step 24: dbo.AspNetUserRoles: add primary key PK_dbo.AspNetUserRoles------------------------------
ALTER TABLE [dbo].[AspNetUserRoles] ADD CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED ([UserId], [RoleId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 24 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 24 is completed with errors' SET NOEXEC ON END
GO
--step 25: dbo.AspNetUserRoles: add foreign key FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId-------
ALTER TABLE [dbo].[AspNetUserRoles] ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 25 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 25 is completed with errors' SET NOEXEC ON END
GO
--step 26: dbo.AspNetUserRoles: add foreign key FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId-------
ALTER TABLE [dbo].[AspNetUserRoles] ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 26 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 26 is completed with errors' SET NOEXEC ON END
GO
--step 27: create table dbo.Attachments-------------------------------------------------------------
CREATE TABLE [dbo].[Attachments] (
[Id] [uniqueidentifier] NOT NULL,
[ParentId] [uniqueidentifier] NOT NULL,
[ParentType] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SourceFileName] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FileName] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ContentType] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FileSize] [int] NULL,
[Created] [datetime] NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 27 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 27 is completed with errors' SET NOEXEC ON END
GO
--step 28: dbo.Attachments: add primary key PK_Attachments------------------------------------------
ALTER TABLE [dbo].[Attachments] ADD CONSTRAINT [PK_Attachments] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 28 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 28 is completed with errors' SET NOEXEC ON END
GO
--step 29: create table dbo.Calendar----------------------------------------------------------------
CREATE TABLE [dbo].[Calendar] (
[ShowName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Category] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WeekEndingDate] [datetime] NULL,
[Quantity] [int] NULL,
[Cost] [decimal](15, 4) NULL,
[Type] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UseQuantityCostCalculated] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CGEFX] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GLDept] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Credit_Dept] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[uom] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HrsWeek] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IncomeStmtType] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserDef1] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AverageRate] [decimal](15, 4) NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 29 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 29 is completed with errors' SET NOEXEC ON END
GO
--step 30: create table dbo.Client------------------------------------------------------------------
CREATE TABLE [dbo].[Client] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GLAccountId] [uniqueidentifier] NULL,
[ClientNumber] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 30 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 30 is completed with errors' SET NOEXEC ON END
GO
--step 31: dbo.Client: add default DF_Client_Id-----------------------------------------------------
ALTER TABLE [dbo].[Client] ADD CONSTRAINT [DF_Client_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 31 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 31 is completed with errors' SET NOEXEC ON END
GO
--step 32: dbo.Client: add primary key PK_Client----------------------------------------------------
ALTER TABLE [dbo].[Client] ADD CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 32 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 32 is completed with errors' SET NOEXEC ON END
GO
--step 33: create table dbo.GLAccount---------------------------------------------------------------
CREATE TABLE [dbo].[GLAccount] (
[Id] [uniqueidentifier] NOT NULL,
[GLNumber] [nvarchar](160) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Name] [nvarchar](800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 33 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 33 is completed with errors' SET NOEXEC ON END
GO
--step 34: dbo.GLAccount: add primary key PK_GLDepartmentID-----------------------------------------
ALTER TABLE [dbo].[GLAccount] ADD CONSTRAINT [PK_GLDepartmentID] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 34 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 34 is completed with errors' SET NOEXEC ON END
GO
--step 35: dbo.Client: add foreign key FK_Client_GLAccount------------------------------------------
ALTER TABLE [dbo].[Client] ADD CONSTRAINT [FK_Client_GLAccount] FOREIGN KEY ([GLAccountId]) REFERENCES [dbo].[GLAccount] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 35 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 35 is completed with errors' SET NOEXEC ON END
GO
--step 36: create table dbo.Company-----------------------------------------------------------------
CREATE TABLE [dbo].[Company] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ParentCompanyId] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 36 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 36 is completed with errors' SET NOEXEC ON END
GO
--step 37: dbo.Company: add default DF_Studio_Id----------------------------------------------------
ALTER TABLE [dbo].[Company] ADD CONSTRAINT [DF_Studio_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 37 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 37 is completed with errors' SET NOEXEC ON END
GO
--step 38: dbo.Company: add primary key PK_Studio---------------------------------------------------
ALTER TABLE [dbo].[Company] ADD CONSTRAINT [PK_Studio] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 38 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 38 is completed with errors' SET NOEXEC ON END
GO
--step 39: dbo.Company: add foreign key FK_Company_Company------------------------------------------
ALTER TABLE [dbo].[Company] ADD CONSTRAINT [FK_Company_Company] FOREIGN KEY ([ParentCompanyId]) REFERENCES [dbo].[Company] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 39 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 39 is completed with errors' SET NOEXEC ON END
GO
--step 40: create table dbo.Company2Client----------------------------------------------------------
CREATE TABLE [dbo].[Company2Client] (
[Id] [uniqueidentifier] NOT NULL,
[CompanyId] [uniqueidentifier] NOT NULL,
[ClientId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 40 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 40 is completed with errors' SET NOEXEC ON END
GO
--step 41: dbo.Company2Client: add primary key PK_Company2Client------------------------------------
ALTER TABLE [dbo].[Company2Client] ADD CONSTRAINT [PK_Company2Client] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 41 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 41 is completed with errors' SET NOEXEC ON END
GO
--step 42: dbo.Company2Client: add unique UK_Company2Client-----------------------------------------
ALTER TABLE [dbo].[Company2Client] ADD CONSTRAINT [UK_Company2Client] UNIQUE NONCLUSTERED ([CompanyId], [ClientId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 42 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 42 is completed with errors' SET NOEXEC ON END
GO
--step 43: dbo.Company2Client: add foreign key FK_Company2Client_ClientId---------------------------
ALTER TABLE [dbo].[Company2Client] ADD CONSTRAINT [FK_Company2Client_ClientId] FOREIGN KEY ([ClientId]) REFERENCES [dbo].[Client] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 43 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 43 is completed with errors' SET NOEXEC ON END
GO
--step 44: dbo.Company2Client: add foreign key FK_Company2Client_CompanyId--------------------------
ALTER TABLE [dbo].[Company2Client] ADD CONSTRAINT [FK_Company2Client_CompanyId] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Company] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 44 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 44 is completed with errors' SET NOEXEC ON END
GO
--step 45: create table dbo.Company2View------------------------------------------------------------
CREATE TABLE [dbo].[Company2View] (
[Id] [uniqueidentifier] NOT NULL,
[CompanyId] [uniqueidentifier] NOT NULL,
[ViewId] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 45 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 45 is completed with errors' SET NOEXEC ON END
GO
--step 46: dbo.Company2View: add primary key PK_Company2GLDepartment--------------------------------
ALTER TABLE [dbo].[Company2View] ADD CONSTRAINT [PK_Company2GLDepartment] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 46 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 46 is completed with errors' SET NOEXEC ON END
GO
--step 47: dbo.Company2View: add unique UK_Company2View---------------------------------------------
ALTER TABLE [dbo].[Company2View] ADD CONSTRAINT [UK_Company2View] UNIQUE NONCLUSTERED ([CompanyId], [ViewId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 47 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 47 is completed with errors' SET NOEXEC ON END
GO
--step 48: create table dbo.View--------------------------------------------------------------------
CREATE TABLE [dbo].[View] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 48 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 48 is completed with errors' SET NOEXEC ON END
GO
--step 49: dbo.View: add default DF_Department_Id---------------------------------------------------
ALTER TABLE [dbo].[View] ADD CONSTRAINT [DF_Department_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 49 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 49 is completed with errors' SET NOEXEC ON END
GO
--step 50: dbo.View: add primary key PK_Department--------------------------------------------------
ALTER TABLE [dbo].[View] ADD CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 50 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 50 is completed with errors' SET NOEXEC ON END
GO
--step 51: dbo.Company2View: add foreign key FK_Company2Department_Department-----------------------
ALTER TABLE [dbo].[Company2View] ADD CONSTRAINT [FK_Company2Department_Department] FOREIGN KEY ([ViewId]) REFERENCES [dbo].[View] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 51 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 51 is completed with errors' SET NOEXEC ON END
GO
--step 52: dbo.Company2View: add foreign key FK_Company2GLDepartment_CompanyId----------------------
ALTER TABLE [dbo].[Company2View] ADD CONSTRAINT [FK_Company2GLDepartment_CompanyId] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Company] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 52 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 52 is completed with errors' SET NOEXEC ON END
GO
--step 53: create table dbo.Contact-----------------------------------------------------------------
CREATE TABLE [dbo].[Contact] (
[Id] [uniqueidentifier] NOT NULL,
[Type] [int] NOT NULL,
[LastName] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirstName] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MiddleInt] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ParentId] [uniqueidentifier] NULL,
[ContactClassification] [int] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 53 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 53 is completed with errors' SET NOEXEC ON END
GO
--step 54: dbo.Contact: add default DF_Contact_Id---------------------------------------------------
ALTER TABLE [dbo].[Contact] ADD CONSTRAINT [DF_Contact_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 54 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 54 is completed with errors' SET NOEXEC ON END
GO
--step 55: dbo.Contact: add default DF__Contact__Type__56E8E7AB-------------------------------------
ALTER TABLE [dbo].[Contact] ADD CONSTRAINT [DF__Contact__Type__56E8E7AB] DEFAULT ((0)) FOR [Type]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 55 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 55 is completed with errors' SET NOEXEC ON END
GO
--step 56: dbo.Contact: add default DF_Contact_ContactClassification--------------------------------
ALTER TABLE [dbo].[Contact] ADD CONSTRAINT [DF_Contact_ContactClassification] DEFAULT ((0)) FOR [ContactClassification]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 56 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 56 is completed with errors' SET NOEXEC ON END
GO
--step 57: dbo.Contact: add primary key PK_Contact--------------------------------------------------
ALTER TABLE [dbo].[Contact] ADD CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 57 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 57 is completed with errors' SET NOEXEC ON END
GO
--step 58: create table dbo.Contact2Project---------------------------------------------------------
CREATE TABLE [dbo].[Contact2Project] (
[Id] [uniqueidentifier] NOT NULL,
[ContactId] [uniqueidentifier] NOT NULL,
[ShowId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 58 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 58 is completed with errors' SET NOEXEC ON END
GO
--step 59: dbo.Contact2Project: add default DF_Contact2Show_Id--------------------------------------
ALTER TABLE [dbo].[Contact2Project] ADD CONSTRAINT [DF_Contact2Show_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 59 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 59 is completed with errors' SET NOEXEC ON END
GO
--step 60: dbo.Contact2Project: add primary key PK_Contact2Show-------------------------------------
ALTER TABLE [dbo].[Contact2Project] ADD CONSTRAINT [PK_Contact2Show] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 60 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 60 is completed with errors' SET NOEXEC ON END
GO
--step 61: dbo.Contact2Project: add unique UK_Contact2Project---------------------------------------
ALTER TABLE [dbo].[Contact2Project] ADD CONSTRAINT [UK_Contact2Project] UNIQUE NONCLUSTERED ([ContactId], [ShowId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 61 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 61 is completed with errors' SET NOEXEC ON END
GO
--step 62: dbo.Contact2Project: add foreign key FK_Contact2Show_Contact-----------------------------
ALTER TABLE [dbo].[Contact2Project] ADD CONSTRAINT [FK_Contact2Show_Contact] FOREIGN KEY ([ContactId]) REFERENCES [dbo].[Contact] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 62 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 62 is completed with errors' SET NOEXEC ON END
GO
--step 63: create table dbo.Project-----------------------------------------------------------------
CREATE TABLE [dbo].[Project] (
[Id] [uniqueidentifier] NOT NULL,
[CompanyId] [uniqueidentifier] NULL,
[ClientId] [uniqueidentifier] NULL,
[TypeId] [uniqueidentifier] NOT NULL,
[StatusId] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectNumber] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Color] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Details] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Priority] [int] NOT NULL,
[Probability] [decimal](5, 4) NOT NULL,
[IsRevenueGenerating] [bit] NOT NULL,
[Deadline] [datetime] NULL,
[ParentProjectId] [uniqueidentifier] NULL,
[HasChildren] [bit] NOT NULL,
[PerformanceRedThreshold] [decimal](3, 2) NULL,
[PerformanceYellowThreshold] [decimal](3, 2) NULL,
[PartNum] [int] NULL,
[EditTimestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 63 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 63 is completed with errors' SET NOEXEC ON END
GO
--step 64: dbo.Project: add default DF_Show_Id------------------------------------------------------
ALTER TABLE [dbo].[Project] ADD CONSTRAINT [DF_Show_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 64 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 64 is completed with errors' SET NOEXEC ON END
GO
--step 65: dbo.Project: add default DF_Project_HasChildren------------------------------------------
ALTER TABLE [dbo].[Project] ADD CONSTRAINT [DF_Project_HasChildren] DEFAULT ((0)) FOR [HasChildren]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 65 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 65 is completed with errors' SET NOEXEC ON END
GO
--step 66: dbo.Project: add primary key PK_Show-----------------------------------------------------
ALTER TABLE [dbo].[Project] ADD CONSTRAINT [PK_Show] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 66 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 66 is completed with errors' SET NOEXEC ON END
GO
--step 67: dbo.Project: add foreign key FK_Project_Project------------------------------------------
ALTER TABLE [dbo].[Project] ADD CONSTRAINT [FK_Project_Project] FOREIGN KEY ([ParentProjectId]) REFERENCES [dbo].[Project] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 67 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 67 is completed with errors' SET NOEXEC ON END
GO
--step 68: dbo.Project: add foreign key FK_Show_Client----------------------------------------------
ALTER TABLE [dbo].[Project] ADD CONSTRAINT [FK_Show_Client] FOREIGN KEY ([ClientId]) REFERENCES [dbo].[Client] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 68 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 68 is completed with errors' SET NOEXEC ON END
GO
--step 69: create table dbo.Status------------------------------------------------------------------
CREATE TABLE [dbo].[Status] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](1600) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Color] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsSystem] [bit] NOT NULL,
[Probability100] [bit] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 69 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 69 is completed with errors' SET NOEXEC ON END
GO
--step 70: dbo.Status: add default DF_Status_Id-----------------------------------------------------
ALTER TABLE [dbo].[Status] ADD CONSTRAINT [DF_Status_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 70 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 70 is completed with errors' SET NOEXEC ON END
GO
--step 71: dbo.Status: add default DF_Status_IsSystem-----------------------------------------------
ALTER TABLE [dbo].[Status] ADD CONSTRAINT [DF_Status_IsSystem] DEFAULT ((0)) FOR [IsSystem]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 71 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 71 is completed with errors' SET NOEXEC ON END
GO
--step 72: dbo.Status: add default DF_Status_Probability100-----------------------------------------
ALTER TABLE [dbo].[Status] ADD CONSTRAINT [DF_Status_Probability100] DEFAULT ((0)) FOR [Probability100]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 72 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 72 is completed with errors' SET NOEXEC ON END
GO
--step 73: dbo.Status: add primary key PK_Status----------------------------------------------------
ALTER TABLE [dbo].[Status] ADD CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 73 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 73 is completed with errors' SET NOEXEC ON END
GO
--step 74: dbo.Project: add foreign key FK_Show_Status----------------------------------------------
ALTER TABLE [dbo].[Project] ADD CONSTRAINT [FK_Show_Status] FOREIGN KEY ([StatusId]) REFERENCES [dbo].[Status] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 74 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 74 is completed with errors' SET NOEXEC ON END
GO
--step 75: dbo.Project: add foreign key FK_Show_Studio----------------------------------------------
ALTER TABLE [dbo].[Project] ADD CONSTRAINT [FK_Show_Studio] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Company] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 75 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 75 is completed with errors' SET NOEXEC ON END
GO
--step 76: create table dbo.Type--------------------------------------------------------------------
CREATE TABLE [dbo].[Type] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsSystem] [bit] NOT NULL,
[PerformanceRedThreshold] [decimal](3, 2) NULL,
[PerformanceYellowThreshold] [decimal](3, 2) NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 76 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 76 is completed with errors' SET NOEXEC ON END
GO
--step 77: dbo.Type: add default DF_Type_Id---------------------------------------------------------
ALTER TABLE [dbo].[Type] ADD CONSTRAINT [DF_Type_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 77 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 77 is completed with errors' SET NOEXEC ON END
GO
--step 78: dbo.Type: add primary key PK_Type--------------------------------------------------------
ALTER TABLE [dbo].[Type] ADD CONSTRAINT [PK_Type] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 78 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 78 is completed with errors' SET NOEXEC ON END
GO
--step 79: dbo.Project: add foreign key FK_Show_Type------------------------------------------------
ALTER TABLE [dbo].[Project] ADD CONSTRAINT [FK_Show_Type] FOREIGN KEY ([TypeId]) REFERENCES [dbo].[Type] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 79 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 79 is completed with errors' SET NOEXEC ON END
GO
--step 80: dbo.Contact2Project: add foreign key FK_Contact2Show_Show--------------------------------
ALTER TABLE [dbo].[Contact2Project] ADD CONSTRAINT [FK_Contact2Show_Show] FOREIGN KEY ([ShowId]) REFERENCES [dbo].[Project] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 80 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 80 is completed with errors' SET NOEXEC ON END
GO
--step 81: create table dbo.CostSaving--------------------------------------------------------------
CREATE TABLE [dbo].[CostSaving] (
[Id] [uniqueidentifier] NOT NULL,
[ScenarioId] [uniqueidentifier] NOT NULL,
[Year] [int] NOT NULL,
[Month] [smallint] NOT NULL,
[Cost] [decimal](15, 4) NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 81 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 81 is completed with errors' SET NOEXEC ON END
GO
--step 82: dbo.CostSaving: add default DF_CostSaving_Id---------------------------------------------
ALTER TABLE [dbo].[CostSaving] ADD CONSTRAINT [DF_CostSaving_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 82 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 82 is completed with errors' SET NOEXEC ON END
GO
--step 83: dbo.CostSaving: add primary key PK_CostSaving--------------------------------------------
ALTER TABLE [dbo].[CostSaving] ADD CONSTRAINT [PK_CostSaving] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 83 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 83 is completed with errors' SET NOEXEC ON END
GO
--step 84: create table dbo.Scenario----------------------------------------------------------------
CREATE TABLE [dbo].[Scenario] (
[Id] [uniqueidentifier] NOT NULL,
[ParentId] [uniqueidentifier] NULL,
[TemplateId] [uniqueidentifier] NULL,
[Type] [int] NOT NULL,
[Name] [nvarchar](800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectedRevenue] [decimal](15, 4) NULL,
[ExpectedGrossMargin] [decimal](15, 4) NULL,
[CalculatedGrossMargin] [decimal](15, 4) NULL,
[CGSplit] [decimal](5, 4) NULL,
[EFXSplit] [decimal](5, 4) NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[Duration] [int] NULL,
[TDDirectCosts] [decimal](15, 4) NULL,
[BUDirectCosts] [decimal](15, 4) NULL,
[Shots] [int] NULL,
[TDRevenueShot] [decimal](15, 4) NULL,
[BURevenueShot] [decimal](15, 4) NULL,
[LastUpdate] [datetime] NULL,
[Color] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [int] NULL,
[UseLMMargin] [int] NULL,
[ExpectedGrossMargin_LM] [decimal](15, 4) NULL,
[CalculatedGrossMargin_LM] [decimal](15, 4) NULL,
[TDDirectCosts_LM] [decimal](15, 4) NULL,
[BUDirectCosts_LM] [decimal](15, 4) NULL,
[BURevenueShot_LM] [decimal](15, 4) NULL,
[ShotStartDate] [datetime] NULL,
[Actuals_BUDirectCosts] [decimal](15, 4) NULL,
[Actuals_BUDirectCosts_LM] [decimal](15, 4) NULL,
[SystemAttributeObjectID] [uniqueidentifier] NULL,
[ProjectedExpense] [decimal](15, 4) NULL,
[FreezeRevenue] [bit] NOT NULL,
[GrowthScenario] [bit] NOT NULL,
[CostSavings] [decimal](15, 4) NULL,
[CostSavingsStartDate] [datetime] NULL,
[CostSavingsEndDate] [datetime] NULL,
[CostSavingsType] [smallint] NULL,
[CostSavingsDescription] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ROIDate] [datetime] NULL,
[EditTimestamp] [timestamp] NOT NULL,
[DateCreated] [datetime] NULL,
[CaptureHistory] [int] NULL,
[IsBottomUp] [bit] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 84 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 84 is completed with errors' SET NOEXEC ON END
GO
--step 85: dbo.Scenario: add default DF__Scenario__Id__6166761E-------------------------------------
ALTER TABLE [dbo].[Scenario] ADD CONSTRAINT [DF__Scenario__Id__6166761E] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 85 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 85 is completed with errors' SET NOEXEC ON END
GO
--step 86: dbo.Scenario: add default DF__Scenario__Projec__5F7E2DAC---------------------------------
ALTER TABLE [dbo].[Scenario] ADD CONSTRAINT [DF__Scenario__Projec__5F7E2DAC] DEFAULT ((0)) FOR [ProjectedRevenue]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 86 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 86 is completed with errors' SET NOEXEC ON END
GO
--step 87: dbo.Scenario: add default DF__Scenario__LastUp__607251E5---------------------------------
ALTER TABLE [dbo].[Scenario] ADD CONSTRAINT [DF__Scenario__LastUp__607251E5] DEFAULT (getdate()) FOR [LastUpdate]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 87 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 87 is completed with errors' SET NOEXEC ON END
GO
--step 88: dbo.Scenario: add default DF__Scenario__Create__37FA4C37---------------------------------
ALTER TABLE [dbo].[Scenario] ADD CONSTRAINT [DF__Scenario__Create__37FA4C37] DEFAULT (getdate()) FOR [DateCreated]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 88 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 88 is completed with errors' SET NOEXEC ON END
GO
--step 89: dbo.Scenario: add default D_Scenario_IsBottomUp------------------------------------------
ALTER TABLE [dbo].[Scenario] ADD CONSTRAINT [D_Scenario_IsBottomUp] DEFAULT ((0)) FOR [IsBottomUp]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 89 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 89 is completed with errors' SET NOEXEC ON END
GO
--step 90: dbo.Scenario: add primary key PK_Scenario------------------------------------------------
ALTER TABLE [dbo].[Scenario] ADD CONSTRAINT [PK_Scenario] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 90 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 90 is completed with errors' SET NOEXEC ON END
GO
--step 91: dbo.Scenario: add foreign key FK_Scenario_Project----------------------------------------
ALTER TABLE [dbo].[Scenario] ADD CONSTRAINT [FK_Scenario_Project] FOREIGN KEY ([ParentId]) REFERENCES [dbo].[Project] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 91 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 91 is completed with errors' SET NOEXEC ON END
GO
--step 92: dbo.Scenario: add foreign key FK_Scenario_TemplateScenario-------------------------------
ALTER TABLE [dbo].[Scenario] ADD CONSTRAINT [FK_Scenario_TemplateScenario] FOREIGN KEY ([TemplateId]) REFERENCES [dbo].[Scenario] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 92 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 92 is completed with errors' SET NOEXEC ON END
GO
--step 93: dbo.CostSaving: add foreign key FK_CostSaving_Scenario-----------------------------------
ALTER TABLE [dbo].[CostSaving] ADD CONSTRAINT [FK_CostSaving_Scenario] FOREIGN KEY ([ScenarioId]) REFERENCES [dbo].[Scenario] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 93 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 93 is completed with errors' SET NOEXEC ON END
GO
--step 94: create table dbo.CreditDepartment--------------------------------------------------------
CREATE TABLE [dbo].[CreditDepartment] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreditNumber] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 94 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 94 is completed with errors' SET NOEXEC ON END
GO
--step 95: dbo.CreditDepartment: add default DF__CreditDepart__Id__57DD0BE4-------------------------
ALTER TABLE [dbo].[CreditDepartment] ADD CONSTRAINT [DF__CreditDepart__Id__57DD0BE4] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 95 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 95 is completed with errors' SET NOEXEC ON END
GO
--step 96: dbo.CreditDepartment: add primary key PK_CreditDepartmentID------------------------------
ALTER TABLE [dbo].[CreditDepartment] ADD CONSTRAINT [PK_CreditDepartmentID] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 96 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 96 is completed with errors' SET NOEXEC ON END
GO
--step 97: create table dbo.CreditDepartmentConversion----------------------------------------------
CREATE TABLE [dbo].[CreditDepartmentConversion] (
[ID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Name] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 97 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 97 is completed with errors' SET NOEXEC ON END
GO
--step 98: create table dbo.DetailsStore------------------------------------------------------------
CREATE TABLE [dbo].[DetailsStore] (
[ExpenditureCategory] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UseQuantityCostCalculated] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CGEFX] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GLDept] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreditDept] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[uom] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HrsWeek] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IncomeStmtType] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserDefined1] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AverageRate] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 98 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 98 is completed with errors' SET NOEXEC ON END
GO
--step 99: create table dbo.Expenditure-------------------------------------------------------------
CREATE TABLE [dbo].[Expenditure] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[jobcode] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 99 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 99 is completed with errors' SET NOEXEC ON END
GO
--step 100: dbo.Expenditure: add default DF__Expenditure__Id__58D1301D------------------------------
ALTER TABLE [dbo].[Expenditure] ADD CONSTRAINT [DF__Expenditure__Id__58D1301D] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 100 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 100 is completed with errors' SET NOEXEC ON END
GO
--step 101: dbo.Expenditure: add primary key PK_ExpenditureID---------------------------------------
ALTER TABLE [dbo].[Expenditure] ADD CONSTRAINT [PK_ExpenditureID] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 101 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 101 is completed with errors' SET NOEXEC ON END
GO
--step 102: create table dbo.Expenditure2Expenditure------------------------------------------------
CREATE TABLE [dbo].[Expenditure2Expenditure] (
[Id] [uniqueidentifier] NOT NULL,
[ParentId] [uniqueidentifier] NOT NULL,
[ChildId] [uniqueidentifier] NOT NULL,
[FactorType] [int] NULL,
[ProcessOrder] [int] NULL,
[FactorInt] [decimal](6, 3) NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 102 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 102 is completed with errors' SET NOEXEC ON END
GO
--step 103: dbo.Expenditure2Expenditure: add default DF__Expenditure2__Id__5AB9788F-----------------
ALTER TABLE [dbo].[Expenditure2Expenditure] ADD CONSTRAINT [DF__Expenditure2__Id__5AB9788F] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 103 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 103 is completed with errors' SET NOEXEC ON END
GO
--step 104: dbo.Expenditure2Expenditure: add primary key PK_Expenditure2ExpenditureID---------------
ALTER TABLE [dbo].[Expenditure2Expenditure] ADD CONSTRAINT [PK_Expenditure2ExpenditureID] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 104 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 104 is completed with errors' SET NOEXEC ON END
GO
--step 105: dbo.Expenditure2Expenditure: add unique UK_Expenditure2Expenditure----------------------
ALTER TABLE [dbo].[Expenditure2Expenditure] ADD CONSTRAINT [UK_Expenditure2Expenditure] UNIQUE NONCLUSTERED ([ParentId], [ChildId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 105 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 105 is completed with errors' SET NOEXEC ON END
GO
--step 106: create table dbo.ExpenditureCategory----------------------------------------------------
CREATE TABLE [dbo].[ExpenditureCategory] (
[Id] [uniqueidentifier] NOT NULL,
[ExpenditureId] [uniqueidentifier] NOT NULL,
[GLId] [uniqueidentifier] NOT NULL,
[UOMId] [uniqueidentifier] NOT NULL,
[CreditId] [uniqueidentifier] NOT NULL,
[Type] [int] NULL,
[UseType] [int] NULL,
[CGEFX] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SortOrder] [int] NULL,
[WksSubjectToFee] [int] NULL,
[SystemAttributeOne] [uniqueidentifier] NULL,
[SystemAttributeTwo] [uniqueidentifier] NULL,
[Name] [nvarchar](800) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 106 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 106 is completed with errors' SET NOEXEC ON END
GO
--step 107: dbo.ExpenditureCategory: add default DF__Expenditure___Id__59C55456---------------------
ALTER TABLE [dbo].[ExpenditureCategory] ADD CONSTRAINT [DF__Expenditure___Id__59C55456] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 107 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 107 is completed with errors' SET NOEXEC ON END
GO
--step 108: dbo.ExpenditureCategory: add primary key PK_Expenditure_CategoryID----------------------
ALTER TABLE [dbo].[ExpenditureCategory] ADD CONSTRAINT [PK_Expenditure_CategoryID] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 108 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 108 is completed with errors' SET NOEXEC ON END
GO
--step 109: dbo.ExpenditureCategory: add foreign key FK_Expenditure_Category_CreditDepartment-------
ALTER TABLE [dbo].[ExpenditureCategory] ADD CONSTRAINT [FK_Expenditure_Category_CreditDepartment] FOREIGN KEY ([CreditId]) REFERENCES [dbo].[CreditDepartment] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 109 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 109 is completed with errors' SET NOEXEC ON END
GO
--step 110: dbo.ExpenditureCategory: add foreign key FK_Expenditure_Category_GLDepartment-----------
ALTER TABLE [dbo].[ExpenditureCategory] ADD CONSTRAINT [FK_Expenditure_Category_GLDepartment] FOREIGN KEY ([GLId]) REFERENCES [dbo].[GLAccount] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 110 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 110 is completed with errors' SET NOEXEC ON END
GO
--step 111: create table dbo.UOM--------------------------------------------------------------------
CREATE TABLE [dbo].[UOM] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UOMValue] [decimal](15, 4) NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 111 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 111 is completed with errors' SET NOEXEC ON END
GO
--step 112: dbo.UOM: add default DF_UOM_Id----------------------------------------------------------
ALTER TABLE [dbo].[UOM] ADD CONSTRAINT [DF_UOM_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 112 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 112 is completed with errors' SET NOEXEC ON END
GO
--step 113: dbo.UOM: add primary key PK_UOM---------------------------------------------------------
ALTER TABLE [dbo].[UOM] ADD CONSTRAINT [PK_UOM] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 113 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 113 is completed with errors' SET NOEXEC ON END
GO
--step 114: dbo.ExpenditureCategory: add foreign key FK_Expenditure_Category_UOM--------------------
ALTER TABLE [dbo].[ExpenditureCategory] ADD CONSTRAINT [FK_Expenditure_Category_UOM] FOREIGN KEY ([UOMId]) REFERENCES [dbo].[UOM] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 114 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 114 is completed with errors' SET NOEXEC ON END
GO
--step 115: dbo.ExpenditureCategory: add foreign key FK_ExpenditureCategory_Expenditure-------------
ALTER TABLE [dbo].[ExpenditureCategory] ADD CONSTRAINT [FK_ExpenditureCategory_Expenditure] FOREIGN KEY ([ExpenditureId]) REFERENCES [dbo].[Expenditure] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 115 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 115 is completed with errors' SET NOEXEC ON END
GO
--step 116: dbo.Expenditure2Expenditure: add foreign key FK_Child_ExpenditureCategory---------------
ALTER TABLE [dbo].[Expenditure2Expenditure] ADD CONSTRAINT [FK_Child_ExpenditureCategory] FOREIGN KEY ([ChildId]) REFERENCES [dbo].[ExpenditureCategory] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 116 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 116 is completed with errors' SET NOEXEC ON END
GO
--step 117: dbo.Expenditure2Expenditure: add foreign key FK_Parent_ExpenditureCategory--------------
ALTER TABLE [dbo].[Expenditure2Expenditure] ADD CONSTRAINT [FK_Parent_ExpenditureCategory] FOREIGN KEY ([ParentId]) REFERENCES [dbo].[ExpenditureCategory] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 117 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 117 is completed with errors' SET NOEXEC ON END
GO
--step 118: create table dbo.FeeCalculation---------------------------------------------------------
CREATE TABLE [dbo].[FeeCalculation] (
[Id] [uniqueidentifier] NOT NULL,
[ExpenditureCategoryId] [uniqueidentifier] NOT NULL,
[MinShot] [int] NOT NULL,
[MaxShot] [int] NOT NULL,
[Quantity] [int] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 118 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 118 is completed with errors' SET NOEXEC ON END
GO
--step 119: dbo.FeeCalculation: add default DF__Fee_Calculat__Id__5BAD9CC8--------------------------
ALTER TABLE [dbo].[FeeCalculation] ADD CONSTRAINT [DF__Fee_Calculat__Id__5BAD9CC8] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 119 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 119 is completed with errors' SET NOEXEC ON END
GO
--step 120: dbo.FeeCalculation: add primary key PK_FeeCalculationID---------------------------------
ALTER TABLE [dbo].[FeeCalculation] ADD CONSTRAINT [PK_FeeCalculationID] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 120 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 120 is completed with errors' SET NOEXEC ON END
GO
--step 121: dbo.FeeCalculation: add foreign key FK_FeeCalculation_ExpenditureCategory---------------
ALTER TABLE [dbo].[FeeCalculation] ADD CONSTRAINT [FK_FeeCalculation_ExpenditureCategory] FOREIGN KEY ([ExpenditureCategoryId]) REFERENCES [dbo].[ExpenditureCategory] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 121 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 121 is completed with errors' SET NOEXEC ON END
GO
--step 122: create table dbo.Firstmark-Private Loans 20150109---------------------------------------
CREATE TABLE [dbo].[Firstmark-Private Loans 20150109] (
[CompanyId] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TypeId] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StatusId] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Name] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[clientID] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectNumber] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Color] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Details] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Priority] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Probability] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsRevenueGenerating] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsTemplate] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 122 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 122 is completed with errors' SET NOEXEC ON END
GO
--step 123: create table dbo.FiscalCalendar---------------------------------------------------------
CREATE TABLE [dbo].[FiscalCalendar] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [int] NOT NULL,
[YearInt] [int] NOT NULL,
[QuarterInt] [int] NOT NULL,
[PeriodInt] [int] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[SystemName] [nvarchar](800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NonWorking] [tinyint] NOT NULL,
[AdjustingPeriod] [bit] NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 123 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 123 is completed with errors' SET NOEXEC ON END
GO
--step 124: dbo.FiscalCalendar: add default DF_FiscalYear_Id----------------------------------------
ALTER TABLE [dbo].[FiscalCalendar] ADD CONSTRAINT [DF_FiscalYear_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 124 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 124 is completed with errors' SET NOEXEC ON END
GO
--step 125: dbo.FiscalCalendar: add default DF__FiscalYea__NonWo__5CA1C101--------------------------
ALTER TABLE [dbo].[FiscalCalendar] ADD CONSTRAINT [DF__FiscalYea__NonWo__5CA1C101] DEFAULT ((0)) FOR [NonWorking]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 125 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 125 is completed with errors' SET NOEXEC ON END
GO
--step 126: dbo.FiscalCalendar: add primary key PK_FiscalYear---------------------------------------
ALTER TABLE [dbo].[FiscalCalendar] ADD CONSTRAINT [PK_FiscalYear] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 126 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 126 is completed with errors' SET NOEXEC ON END
GO
--step 127: create table dbo.FiscalCalendarConvert--------------------------------------------------
CREATE TABLE [dbo].[FiscalCalendarConvert] (
[Id] [uniqueidentifier] NOT NULL,
[ConvPeriodInt] [int] NOT NULL,
[SourcePeriodInt] [int] NOT NULL,
[ConvYearInt] [int] NOT NULL,
[SourceYearInt] [int] NOT NULL,
[ConvQuarterInt] [int] NOT NULL,
[SourceQuarterInt] [int] NOT NULL,
[ConvStartDate] [datetime] NOT NULL,
[SourceStartDate] [datetime] NOT NULL,
[ConvEndDate] [datetime] NOT NULL,
[SourceEndDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 127 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 127 is completed with errors' SET NOEXEC ON END
GO
--step 128: dbo.FiscalCalendarConvert: add default DF_FiscalYearConvert_Id--------------------------
ALTER TABLE [dbo].[FiscalCalendarConvert] ADD CONSTRAINT [DF_FiscalYearConvert_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 128 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 128 is completed with errors' SET NOEXEC ON END
GO
--step 129: dbo.FiscalCalendarConvert: add primary key PK_FiscalYearConvert-------------------------
ALTER TABLE [dbo].[FiscalCalendarConvert] ADD CONSTRAINT [PK_FiscalYearConvert] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 129 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 129 is completed with errors' SET NOEXEC ON END
GO
--step 130: create table dbo.FiscalCalendarSettings-------------------------------------------------
CREATE TABLE [dbo].[FiscalCalendarSettings] (
[Id] [uniqueidentifier] NOT NULL,
[CalendarType] [smallint] NOT NULL,
[StartingPoint] [datetime] NOT NULL,
[WeekendingDay] [smallint] NOT NULL,
[YearType] [smallint] NOT NULL,
[UseAdjustingPeriod] [bit] NOT NULL,
[EffectiveChangeDate] [datetime] NOT NULL,
[CreatedAt] [datetime] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 130 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 130 is completed with errors' SET NOEXEC ON END
GO
--step 131: dbo.FiscalCalendarSettings: add default DF_FiscalCalendarSettings_UseAdjustingPeriod----
ALTER TABLE [dbo].[FiscalCalendarSettings] ADD CONSTRAINT [DF_FiscalCalendarSettings_UseAdjustingPeriod] DEFAULT ((0)) FOR [UseAdjustingPeriod]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 131 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 131 is completed with errors' SET NOEXEC ON END
GO
--step 132: dbo.FiscalCalendarSettings: add default DF_FiscalCalendarSettings_CreatedAt-------------
ALTER TABLE [dbo].[FiscalCalendarSettings] ADD CONSTRAINT [DF_FiscalCalendarSettings_CreatedAt] DEFAULT (getdate()) FOR [CreatedAt]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 132 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 132 is completed with errors' SET NOEXEC ON END
GO
--step 133: add index IX_FiscalCalendarSettings_1 to table dbo.FiscalCalendarSettings---------------
CREATE UNIQUE NONCLUSTERED INDEX [IX_FiscalCalendarSettings_1] ON [dbo].[FiscalCalendarSettings]([EffectiveChangeDate]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 133 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 133 is completed with errors' SET NOEXEC ON END
GO
--step 134: dbo.FiscalCalendarSettings: add primary key PK_FiscalCalendarSettings-------------------
ALTER TABLE [dbo].[FiscalCalendarSettings] ADD CONSTRAINT [PK_FiscalCalendarSettings] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 134 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 134 is completed with errors' SET NOEXEC ON END
GO
--step 135: create extended property 'MS_Description' for column dbo.FiscalCalendarSettings.CalendarType
exec sp_addextendedproperty N'MS_Description', N'Values from _FiscalCalendarType_ enum', 'SCHEMA', N'dbo', 'TABLE', N'FiscalCalendarSettings', 'COLUMN', N'CalendarType'
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 135 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 135 is completed with errors' SET NOEXEC ON END
GO
--step 136: create extended property 'MS_Description' for column dbo.FiscalCalendarSettings.WeekendingDay
exec sp_addextendedproperty N'MS_Description', N'DateTime.DayOfWeek value', 'SCHEMA', N'dbo', 'TABLE', N'FiscalCalendarSettings', 'COLUMN', N'WeekendingDay'
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 136 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 136 is completed with errors' SET NOEXEC ON END
GO
--step 137: create extended property 'MS_Description' for column dbo.FiscalCalendarSettings.YearType
exec sp_addextendedproperty N'MS_Description', N'Values from _CalendarYearType_ enum', 'SCHEMA', N'dbo', 'TABLE', N'FiscalCalendarSettings', 'COLUMN', N'YearType'
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 137 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 137 is completed with errors' SET NOEXEC ON END
GO
--step 138: create table dbo.FiscalCalendarTemp-----------------------------------------------------
CREATE TABLE [dbo].[FiscalCalendarTemp] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [int] NOT NULL,
[YearInt] [int] NOT NULL,
[QuarterInt] [int] NOT NULL,
[PeriodInt] [int] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[SystemName] [nvarchar](800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NonWorking] [tinyint] NOT NULL,
[AdjustingPeriod] [bit] NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 138 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 138 is completed with errors' SET NOEXEC ON END
GO
--step 139: dbo.FiscalCalendarTemp: add default DF_FiscalYearTemp_Id--------------------------------
ALTER TABLE [dbo].[FiscalCalendarTemp] ADD CONSTRAINT [DF_FiscalYearTemp_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 139 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 139 is completed with errors' SET NOEXEC ON END
GO
--step 140: dbo.FiscalCalendarTemp: add default DF__FiscalCal__NonWo__7DCDAAA2----------------------
ALTER TABLE [dbo].[FiscalCalendarTemp] ADD CONSTRAINT [DF__FiscalCal__NonWo__7DCDAAA2] DEFAULT ((0)) FOR [NonWorking]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 140 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 140 is completed with errors' SET NOEXEC ON END
GO
--step 141: dbo.FiscalCalendarTemp: add primary key PK_FiscalYearTemp-------------------------------
ALTER TABLE [dbo].[FiscalCalendarTemp] ADD CONSTRAINT [PK_FiscalYearTemp] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 141 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 141 is completed with errors' SET NOEXEC ON END
GO
--step 142: create table dbo.GLAccountAccess--------------------------------------------------------
CREATE TABLE [dbo].[GLAccountAccess] (
[Id] [uniqueidentifier] NOT NULL,
[ParentId] [uniqueidentifier] NULL,
[GLDepartmentArray] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GLDepartmentAccess] [nvarchar](384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 142 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 142 is completed with errors' SET NOEXEC ON END
GO
--step 143: dbo.GLAccountAccess: add default DF_GLDepartmentAccess_Id-------------------------------
ALTER TABLE [dbo].[GLAccountAccess] ADD CONSTRAINT [DF_GLDepartmentAccess_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 143 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 143 is completed with errors' SET NOEXEC ON END
GO
--step 144: create table dbo.History----------------------------------------------------------------
CREATE TABLE [dbo].[History] (
[Id] [uniqueidentifier] NOT NULL,
[EntityId] [uniqueidentifier] NULL,
[XML] [xml] NOT NULL,
[TimeStamp] [date] NOT NULL,
[ModifiedBy] [uniqueidentifier] NOT NULL,
[EntityType] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ModificationType] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 144 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 144 is completed with errors' SET NOEXEC ON END
GO
--step 145: dbo.History: add default DF_History_Id--------------------------------------------------
ALTER TABLE [dbo].[History] ADD CONSTRAINT [DF_History_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 145 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 145 is completed with errors' SET NOEXEC ON END
GO
--step 146: dbo.History: add default DF_History_TimeStamp-------------------------------------------
ALTER TABLE [dbo].[History] ADD CONSTRAINT [DF_History_TimeStamp] DEFAULT (getdate()) FOR [TimeStamp]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 146 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 146 is completed with errors' SET NOEXEC ON END
GO
--step 147: add index IX_History_EntityId to table dbo.History--------------------------------------
CREATE NONCLUSTERED INDEX [IX_History_EntityId] ON [dbo].[History]([EntityId]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 147 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 147 is completed with errors' SET NOEXEC ON END
GO
--step 148: dbo.History: add primary key PK_History-------------------------------------------------
ALTER TABLE [dbo].[History] ADD CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 148 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 148 is completed with errors' SET NOEXEC ON END
GO
--step 149: create table dbo.Holiday----------------------------------------------------------------
CREATE TABLE [dbo].[Holiday] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](2040) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WorkingDays] [bit] NOT NULL,
[HolidayGroupId] [uniqueidentifier] NOT NULL,
[EffectiveChangeDate] [datetime] NOT NULL,
[CreatedAt] [datetime] NOT NULL,
[Options] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NULL,
[CompanyImpact] [bit] NOT NULL,
[IsInclude] [bit] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 149 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 149 is completed with errors' SET NOEXEC ON END
GO
--step 150: dbo.Holiday: add default DF__Holiday__Id__5D95E53A--------------------------------------
ALTER TABLE [dbo].[Holiday] ADD CONSTRAINT [DF__Holiday__Id__5D95E53A] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 150 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 150 is completed with errors' SET NOEXEC ON END
GO
--step 151: dbo.Holiday: add default DF__Holiday__Effecti__361203C5---------------------------------
ALTER TABLE [dbo].[Holiday] ADD CONSTRAINT [DF__Holiday__Effecti__361203C5] DEFAULT (CONVERT([datetime],'1950-01-01',(0))) FOR [EffectiveChangeDate]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 151 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 151 is completed with errors' SET NOEXEC ON END
GO
--step 152: dbo.Holiday: add default DF__Holiday__Created__370627FE---------------------------------
ALTER TABLE [dbo].[Holiday] ADD CONSTRAINT [DF__Holiday__Created__370627FE] DEFAULT (getdate()) FOR [CreatedAt]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 152 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 152 is completed with errors' SET NOEXEC ON END
GO
--step 153: dbo.Holiday: add default DF__Holiday__Company__025D5595---------------------------------
ALTER TABLE [dbo].[Holiday] ADD CONSTRAINT [DF__Holiday__Company__025D5595] DEFAULT ((1)) FOR [CompanyImpact]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 153 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 153 is completed with errors' SET NOEXEC ON END
GO
--step 154: dbo.Holiday: add default DF__Holiday__IsInclu__04459E07---------------------------------
ALTER TABLE [dbo].[Holiday] ADD CONSTRAINT [DF__Holiday__IsInclu__04459E07] DEFAULT ((1)) FOR [IsInclude]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 154 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 154 is completed with errors' SET NOEXEC ON END
GO
--step 155: add index IX_Holiday_3 to table dbo.Holiday---------------------------------------------
CREATE NONCLUSTERED INDEX [IX_Holiday_3] ON [dbo].[Holiday]([CompanyImpact], [IsInclude], [WorkingDays]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 155 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 155 is completed with errors' SET NOEXEC ON END
GO
--step 156: dbo.Holiday: add primary key PK_Holiday-------------------------------------------------
ALTER TABLE [dbo].[Holiday] ADD CONSTRAINT [PK_Holiday] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 156 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 156 is completed with errors' SET NOEXEC ON END
GO
--step 157: create table dbo.Holiday2ExpenditureCategory--------------------------------------------
CREATE TABLE [dbo].[Holiday2ExpenditureCategory] (
[Id] [uniqueidentifier] NOT NULL,
[HolidayId] [uniqueidentifier] NOT NULL,
[ExpenditureCategoryId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 157 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 157 is completed with errors' SET NOEXEC ON END
GO
--step 158: dbo.Holiday2ExpenditureCategory: add default DF_Holiday2ExpenditureCategory_Id----------
ALTER TABLE [dbo].[Holiday2ExpenditureCategory] ADD CONSTRAINT [DF_Holiday2ExpenditureCategory_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 158 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 158 is completed with errors' SET NOEXEC ON END
GO
--step 159: add index IX_Holiday2ExpenditureCategory to table dbo.Holiday2ExpenditureCategory-------
CREATE NONCLUSTERED INDEX [IX_Holiday2ExpenditureCategory] ON [dbo].[Holiday2ExpenditureCategory]([HolidayId]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 159 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 159 is completed with errors' SET NOEXEC ON END
GO
--step 160: add index IX_Holiday2ExpenditureCategory_1 to table dbo.Holiday2ExpenditureCategory-----
CREATE NONCLUSTERED INDEX [IX_Holiday2ExpenditureCategory_1] ON [dbo].[Holiday2ExpenditureCategory]([ExpenditureCategoryId]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 160 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 160 is completed with errors' SET NOEXEC ON END
GO
--step 161: dbo.Holiday2ExpenditureCategory: add primary key PK_Holiday2ExpenditureCategory---------
ALTER TABLE [dbo].[Holiday2ExpenditureCategory] ADD CONSTRAINT [PK_Holiday2ExpenditureCategory] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 161 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 161 is completed with errors' SET NOEXEC ON END
GO
--step 162: dbo.Holiday2ExpenditureCategory: add foreign key FK_Holiday2ExpenditureCategory_ExpenditureCategory
ALTER TABLE [dbo].[Holiday2ExpenditureCategory] ADD CONSTRAINT [FK_Holiday2ExpenditureCategory_ExpenditureCategory] FOREIGN KEY ([ExpenditureCategoryId]) REFERENCES [dbo].[ExpenditureCategory] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 162 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 162 is completed with errors' SET NOEXEC ON END
GO
--step 163: dbo.Holiday2ExpenditureCategory: add foreign key FK_Holiday2ExpenditureCategory_Holiday-
ALTER TABLE [dbo].[Holiday2ExpenditureCategory] ADD CONSTRAINT [FK_Holiday2ExpenditureCategory_Holiday] FOREIGN KEY ([HolidayId]) REFERENCES [dbo].[Holiday] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 163 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 163 is completed with errors' SET NOEXEC ON END
GO
--step 164: create table dbo.Holiday2PeopleResource-------------------------------------------------
CREATE TABLE [dbo].[Holiday2PeopleResource] (
[Id] [uniqueidentifier] NOT NULL,
[HolidayId] [uniqueidentifier] NOT NULL,
[ResourceId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 164 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 164 is completed with errors' SET NOEXEC ON END
GO
--step 165: dbo.Holiday2PeopleResource: add default DF_Holiday2PeopleResource_Id--------------------
ALTER TABLE [dbo].[Holiday2PeopleResource] ADD CONSTRAINT [DF_Holiday2PeopleResource_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 165 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 165 is completed with errors' SET NOEXEC ON END
GO
--step 166: add index IX_Holiday2PeopleResource to table dbo.Holiday2PeopleResource-----------------
CREATE NONCLUSTERED INDEX [IX_Holiday2PeopleResource] ON [dbo].[Holiday2PeopleResource]([HolidayId]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 166 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 166 is completed with errors' SET NOEXEC ON END
GO
--step 167: add index IX_Holiday2PeopleResource_1 to table dbo.Holiday2PeopleResource---------------
CREATE NONCLUSTERED INDEX [IX_Holiday2PeopleResource_1] ON [dbo].[Holiday2PeopleResource]([ResourceId]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 167 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 167 is completed with errors' SET NOEXEC ON END
GO
--step 168: dbo.Holiday2PeopleResource: add primary key PK_Holiday2PeopleResource-------------------
ALTER TABLE [dbo].[Holiday2PeopleResource] ADD CONSTRAINT [PK_Holiday2PeopleResource] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 168 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 168 is completed with errors' SET NOEXEC ON END
GO
--step 169: dbo.Holiday2PeopleResource: add foreign key FK_Holiday2PeopleResource_Holiday-----------
ALTER TABLE [dbo].[Holiday2PeopleResource] ADD CONSTRAINT [FK_Holiday2PeopleResource_Holiday] FOREIGN KEY ([HolidayId]) REFERENCES [dbo].[Holiday] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 169 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 169 is completed with errors' SET NOEXEC ON END
GO
--step 170: create table dbo.PeopleResource---------------------------------------------------------
CREATE TABLE [dbo].[PeopleResource] (
[Id] [uniqueidentifier] NOT NULL,
[FirstName] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsActiveEmployee] [bit] NOT NULL,
[TeamId] [uniqueidentifier] NULL,
[ExpenditureCategoryId] [uniqueidentifier] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[EmployeeID] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [nvarchar](320) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WorkWeekId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 170 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 170 is completed with errors' SET NOEXEC ON END
GO
--step 171: dbo.PeopleResource: add default DF__PeopleResour__Id__5E8A0973--------------------------
ALTER TABLE [dbo].[PeopleResource] ADD CONSTRAINT [DF__PeopleResour__Id__5E8A0973] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 171 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 171 is completed with errors' SET NOEXEC ON END
GO
--step 172: dbo.PeopleResource: add default DF_Resource_IsActiveEmployee----------------------------
ALTER TABLE [dbo].[PeopleResource] ADD CONSTRAINT [DF_Resource_IsActiveEmployee] DEFAULT ((0)) FOR [IsActiveEmployee]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 172 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 172 is completed with errors' SET NOEXEC ON END
GO
--step 173: add index IX_PeopleResource to table dbo.PeopleResource---------------------------------
CREATE NONCLUSTERED INDEX [IX_PeopleResource] ON [dbo].[PeopleResource]([StartDate], [EndDate]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 173 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 173 is completed with errors' SET NOEXEC ON END
GO
--step 174: add index IX_PeopleResource_1 to table dbo.PeopleResource-------------------------------
CREATE NONCLUSTERED INDEX [IX_PeopleResource_1] ON [dbo].[PeopleResource]([TeamId]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 174 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 174 is completed with errors' SET NOEXEC ON END
GO
--step 175: add index IX_PeopleResource_2 to table dbo.PeopleResource-------------------------------
CREATE NONCLUSTERED INDEX [IX_PeopleResource_2] ON [dbo].[PeopleResource]([ExpenditureCategoryId]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 175 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 175 is completed with errors' SET NOEXEC ON END
GO
--step 176: add index IX_PeopleResource_3 to table dbo.PeopleResource-------------------------------
CREATE NONCLUSTERED INDEX [IX_PeopleResource_3] ON [dbo].[PeopleResource]([WorkWeekId]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 176 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 176 is completed with errors' SET NOEXEC ON END
GO
--step 177: dbo.PeopleResource: add primary key PK_Resource-----------------------------------------
ALTER TABLE [dbo].[PeopleResource] ADD CONSTRAINT [PK_Resource] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 177 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 177 is completed with errors' SET NOEXEC ON END
GO
--step 178: create table dbo.WorkWeek---------------------------------------------------------------
CREATE TABLE [dbo].[WorkWeek] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Monday] [bit] NOT NULL,
[Tuesday] [bit] NOT NULL,
[Wednesday] [bit] NOT NULL,
[Thursday] [bit] NOT NULL,
[Friday] [bit] NOT NULL,
[Saturday] [bit] NOT NULL,
[Sunday] [bit] NOT NULL,
[IsDefault] [bit] NOT NULL,
[IsSystem] [bit] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 178 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 178 is completed with errors' SET NOEXEC ON END
GO
--step 179: dbo.WorkWeek: add primary key PK_WorkWeek-----------------------------------------------
ALTER TABLE [dbo].[WorkWeek] ADD CONSTRAINT [PK_WorkWeek] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 179 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 179 is completed with errors' SET NOEXEC ON END
GO
--step 180: dbo.PeopleResource: add foreign key FK_PeopleResource_WorkWeekId------------------------
ALTER TABLE [dbo].[PeopleResource] ADD CONSTRAINT [FK_PeopleResource_WorkWeekId] FOREIGN KEY ([WorkWeekId]) REFERENCES [dbo].[WorkWeek] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 180 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 180 is completed with errors' SET NOEXEC ON END
GO
--step 181: dbo.PeopleResource: add foreign key FK_Resource_Expenditure_Category--------------------
ALTER TABLE [dbo].[PeopleResource] ADD CONSTRAINT [FK_Resource_Expenditure_Category] FOREIGN KEY ([ExpenditureCategoryId]) REFERENCES [dbo].[ExpenditureCategory] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 181 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 181 is completed with errors' SET NOEXEC ON END
GO
--step 182: create table dbo.Team-------------------------------------------------------------------
CREATE TABLE [dbo].[Team] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CompanyId] [uniqueidentifier] NULL,
[CostCenterId] [uniqueidentifier] NULL,
[ReportsTo] [uniqueidentifier] NULL,
[PlannedCapacityScenarioId] [uniqueidentifier] NULL,
[ActualCapacityScenarioId] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 182 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 182 is completed with errors' SET NOEXEC ON END
GO
--step 183: dbo.Team: add default DF__Team__Id__625A9A57--------------------------------------------
ALTER TABLE [dbo].[Team] ADD CONSTRAINT [DF__Team__Id__625A9A57] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 183 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 183 is completed with errors' SET NOEXEC ON END
GO
--step 184: dbo.Team: add primary key PK_Team-------------------------------------------------------
ALTER TABLE [dbo].[Team] ADD CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 184 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 184 is completed with errors' SET NOEXEC ON END
GO
--step 185: dbo.Team: add foreign key FK_Team_Company-----------------------------------------------
ALTER TABLE [dbo].[Team] ADD CONSTRAINT [FK_Team_Company] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Company] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 185 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 185 is completed with errors' SET NOEXEC ON END
GO
--step 186: dbo.Team: add foreign key FK_Team_Contact-----------------------------------------------
ALTER TABLE [dbo].[Team] ADD CONSTRAINT [FK_Team_Contact] FOREIGN KEY ([ReportsTo]) REFERENCES [dbo].[Contact] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 186 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 186 is completed with errors' SET NOEXEC ON END
GO
--step 187: dbo.Team: add foreign key FK_Team_CreditDepartment--------------------------------------
ALTER TABLE [dbo].[Team] ADD CONSTRAINT [FK_Team_CreditDepartment] FOREIGN KEY ([CostCenterId]) REFERENCES [dbo].[CreditDepartment] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 187 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 187 is completed with errors' SET NOEXEC ON END
GO
--step 188: dbo.Team: add foreign key FK_Team_Scenario----------------------------------------------
ALTER TABLE [dbo].[Team] ADD CONSTRAINT [FK_Team_Scenario] FOREIGN KEY ([PlannedCapacityScenarioId]) REFERENCES [dbo].[Scenario] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 188 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 188 is completed with errors' SET NOEXEC ON END
GO
--step 189: dbo.Team: add foreign key FK_Team_Scenario1---------------------------------------------
ALTER TABLE [dbo].[Team] ADD CONSTRAINT [FK_Team_Scenario1] FOREIGN KEY ([ActualCapacityScenarioId]) REFERENCES [dbo].[Scenario] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 189 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 189 is completed with errors' SET NOEXEC ON END
GO
--step 190: dbo.PeopleResource: add foreign key FK_Resource_Team------------------------------------
ALTER TABLE [dbo].[PeopleResource] ADD CONSTRAINT [FK_Resource_Team] FOREIGN KEY ([TeamId]) REFERENCES [dbo].[Team] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 190 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 190 is completed with errors' SET NOEXEC ON END
GO
--step 191: dbo.Holiday2PeopleResource: add foreign key FK_Holiday2PeopleResource_PeopleResource----
ALTER TABLE [dbo].[Holiday2PeopleResource] ADD CONSTRAINT [FK_Holiday2PeopleResource_PeopleResource] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[PeopleResource] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 191 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 191 is completed with errors' SET NOEXEC ON END
GO
--step 192: create table dbo.Holiday2Team-----------------------------------------------------------
CREATE TABLE [dbo].[Holiday2Team] (
[Id] [uniqueidentifier] NOT NULL,
[HolidayId] [uniqueidentifier] NOT NULL,
[TeamId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 192 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 192 is completed with errors' SET NOEXEC ON END
GO
--step 193: dbo.Holiday2Team: add default DF_Holiday2Team_Id----------------------------------------
ALTER TABLE [dbo].[Holiday2Team] ADD CONSTRAINT [DF_Holiday2Team_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 193 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 193 is completed with errors' SET NOEXEC ON END
GO
--step 194: add index IX_Holiday2Team to table dbo.Holiday2Team-------------------------------------
CREATE NONCLUSTERED INDEX [IX_Holiday2Team] ON [dbo].[Holiday2Team]([HolidayId]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 194 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 194 is completed with errors' SET NOEXEC ON END
GO
--step 195: add index IX_Holiday2Team_1 to table dbo.Holiday2Team-----------------------------------
CREATE NONCLUSTERED INDEX [IX_Holiday2Team_1] ON [dbo].[Holiday2Team]([TeamId]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 195 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 195 is completed with errors' SET NOEXEC ON END
GO
--step 196: dbo.Holiday2Team: add primary key PK_Holiday2Team---------------------------------------
ALTER TABLE [dbo].[Holiday2Team] ADD CONSTRAINT [PK_Holiday2Team] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 196 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 196 is completed with errors' SET NOEXEC ON END
GO
--step 197: dbo.Holiday2Team: add foreign key FK_Holiday2Team_Holiday-------------------------------
ALTER TABLE [dbo].[Holiday2Team] ADD CONSTRAINT [FK_Holiday2Team_Holiday] FOREIGN KEY ([HolidayId]) REFERENCES [dbo].[Holiday] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 197 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 197 is completed with errors' SET NOEXEC ON END
GO
--step 198: dbo.Holiday2Team: add foreign key FK_Holiday2Team_Team----------------------------------
ALTER TABLE [dbo].[Holiday2Team] ADD CONSTRAINT [FK_Holiday2Team_Team] FOREIGN KEY ([TeamId]) REFERENCES [dbo].[Team] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 198 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 198 is completed with errors' SET NOEXEC ON END
GO
--step 199: create table dbo.HolidayAllocation------------------------------------------------------
CREATE TABLE [dbo].[HolidayAllocation] (
[Id] [uniqueidentifier] NOT NULL,
[HolidayId] [uniqueidentifier] NOT NULL,
[HolidayGroupId] [uniqueidentifier] NOT NULL,
[WeekEndingDate] [datetime] NOT NULL,
[Monday] [bit] NOT NULL,
[Tuesday] [bit] NOT NULL,
[Wednesday] [bit] NOT NULL,
[Thursday] [bit] NOT NULL,
[Friday] [bit] NOT NULL,
[Saturday] [bit] NOT NULL,
[Sunday] [bit] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 199 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 199 is completed with errors' SET NOEXEC ON END
GO
--step 200: dbo.HolidayAllocation: add default DF_HolidayAllocation_Id------------------------------
ALTER TABLE [dbo].[HolidayAllocation] ADD CONSTRAINT [DF_HolidayAllocation_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 200 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 200 is completed with errors' SET NOEXEC ON END
GO
--step 201: add index IX_HolidayAllocation to table dbo.HolidayAllocation---------------------------
CREATE UNIQUE NONCLUSTERED INDEX [IX_HolidayAllocation] ON [dbo].[HolidayAllocation]([WeekEndingDate], [HolidayGroupId]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 201 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 201 is completed with errors' SET NOEXEC ON END
GO
--step 202: dbo.HolidayAllocation: add primary key PK_HolidayAllocation-----------------------------
ALTER TABLE [dbo].[HolidayAllocation] ADD CONSTRAINT [PK_HolidayAllocation] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 202 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 202 is completed with errors' SET NOEXEC ON END
GO
--step 203: dbo.HolidayAllocation: add foreign key FK_HolidayAllocation_Holiday---------------------
ALTER TABLE [dbo].[HolidayAllocation] ADD CONSTRAINT [FK_HolidayAllocation_Holiday] FOREIGN KEY ([HolidayId]) REFERENCES [dbo].[Holiday] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 203 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 203 is completed with errors' SET NOEXEC ON END
GO
--step 204: create table dbo.NonProjectTime---------------------------------------------------------
CREATE TABLE [dbo].[NonProjectTime] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[NonProjectTimeCategoryId] [uniqueidentifier] NOT NULL,
[Cost] [decimal](15, 4) NOT NULL,
[Details] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Permanent] [bit] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 204 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 204 is completed with errors' SET NOEXEC ON END
GO
--step 205: dbo.NonProjectTime: add default DF_NonProjectTime_Id------------------------------------
ALTER TABLE [dbo].[NonProjectTime] ADD CONSTRAINT [DF_NonProjectTime_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 205 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 205 is completed with errors' SET NOEXEC ON END
GO
--step 206: dbo.NonProjectTime: add primary key PK_NonProjectTime-----------------------------------
ALTER TABLE [dbo].[NonProjectTime] ADD CONSTRAINT [PK_NonProjectTime] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 206 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 206 is completed with errors' SET NOEXEC ON END
GO
--step 207: create table dbo.NonProjectTimeCategory-------------------------------------------------
CREATE TABLE [dbo].[NonProjectTimeCategory] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 207 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 207 is completed with errors' SET NOEXEC ON END
GO
--step 208: dbo.NonProjectTimeCategory: add default DF_NonProjectTimeCategory_Id--------------------
ALTER TABLE [dbo].[NonProjectTimeCategory] ADD CONSTRAINT [DF_NonProjectTimeCategory_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 208 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 208 is completed with errors' SET NOEXEC ON END
GO
--step 209: dbo.NonProjectTimeCategory: add primary key PK_NonProjectTimeCategory-------------------
ALTER TABLE [dbo].[NonProjectTimeCategory] ADD CONSTRAINT [PK_NonProjectTimeCategory] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 209 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 209 is completed with errors' SET NOEXEC ON END
GO
--step 210: dbo.NonProjectTime: add foreign key FK_NonProjectTime_NonProjectTimeCategory------------
ALTER TABLE [dbo].[NonProjectTime] ADD CONSTRAINT [FK_NonProjectTime_NonProjectTimeCategory] FOREIGN KEY ([NonProjectTimeCategoryId]) REFERENCES [dbo].[NonProjectTimeCategory] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 210 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 210 is completed with errors' SET NOEXEC ON END
GO
--step 211: create table dbo.NonProjectTimeAllocation-----------------------------------------------
CREATE TABLE [dbo].[NonProjectTimeAllocation] (
[Id] [uniqueidentifier] NOT NULL,
[NonProjectTimeId] [uniqueidentifier] NOT NULL,
[PeopleResourceId] [uniqueidentifier] NOT NULL,
[WeekEndingDate] [datetime] NOT NULL,
[HoursOff] [int] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 211 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 211 is completed with errors' SET NOEXEC ON END
GO
--step 212: dbo.NonProjectTimeAllocation: add default DF_NonProjectTimeAllocation_Id----------------
ALTER TABLE [dbo].[NonProjectTimeAllocation] ADD CONSTRAINT [DF_NonProjectTimeAllocation_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 212 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 212 is completed with errors' SET NOEXEC ON END
GO
--step 213: dbo.NonProjectTimeAllocation: add primary key PK_NonProjectTimeAllocation---------------
ALTER TABLE [dbo].[NonProjectTimeAllocation] ADD CONSTRAINT [PK_NonProjectTimeAllocation] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 213 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 213 is completed with errors' SET NOEXEC ON END
GO
--step 214: dbo.NonProjectTimeAllocation: add foreign key FK_NonProjectTimeAllocation_NonProjectTime
ALTER TABLE [dbo].[NonProjectTimeAllocation] ADD CONSTRAINT [FK_NonProjectTimeAllocation_NonProjectTime] FOREIGN KEY ([NonProjectTimeId]) REFERENCES [dbo].[NonProjectTime] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 214 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 214 is completed with errors' SET NOEXEC ON END
GO
--step 215: dbo.NonProjectTimeAllocation: add foreign key FK_NonProjectTimeAllocation_PeopleResource
ALTER TABLE [dbo].[NonProjectTimeAllocation] ADD CONSTRAINT [FK_NonProjectTimeAllocation_PeopleResource] FOREIGN KEY ([PeopleResourceId]) REFERENCES [dbo].[PeopleResource] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 215 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 215 is completed with errors' SET NOEXEC ON END
GO
--step 216: create table dbo.Note-------------------------------------------------------------------
CREATE TABLE [dbo].[Note] (
[Id] [uniqueidentifier] NOT NULL,
[ParentId] [uniqueidentifier] NULL,
[UserId] [uniqueidentifier] NULL,
[DateAdded] [datetime] NULL,
[NoteDetail] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DomainId] [uniqueidentifier] NULL,
[SystemAttributeId] [uniqueidentifier] NULL,
[Title] [nvarchar](400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 216 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 216 is completed with errors' SET NOEXEC ON END
GO
--step 217: dbo.Note: add default DF_Note_Id--------------------------------------------------------
ALTER TABLE [dbo].[Note] ADD CONSTRAINT [DF_Note_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 217 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 217 is completed with errors' SET NOEXEC ON END
GO
--step 218: dbo.Note: add primary key PK__Note__3214EC07160F4887------------------------------------
ALTER TABLE [dbo].[Note] ADD CONSTRAINT [PK__Note__3214EC07160F4887] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 218 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 218 is completed with errors' SET NOEXEC ON END
GO
--step 219: create table dbo.Notification-----------------------------------------------------------
CREATE TABLE [dbo].[Notification] (
[Id] [uniqueidentifier] NOT NULL,
[ParentId] [uniqueidentifier] NULL,
[Description] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Title] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Link] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NotificationGroup] [int] NOT NULL,
[NotificationType] [int] NOT NULL,
[NotificationDate] [datetime] NOT NULL,
[NotificationViewed] [bit] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 219 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 219 is completed with errors' SET NOEXEC ON END
GO
--step 220: dbo.Notification: add primary key PK_Notification---------------------------------------
ALTER TABLE [dbo].[Notification] ADD CONSTRAINT [PK_Notification] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 220 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 220 is completed with errors' SET NOEXEC ON END
GO
--step 221: create table dbo.NotificationControl----------------------------------------------------
CREATE TABLE [dbo].[NotificationControl] (
[Id] [uniqueidentifier] NOT NULL,
[NotificationType] [int] NOT NULL,
[HowtoSend] [int] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 221 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 221 is completed with errors' SET NOEXEC ON END
GO
--step 222: dbo.NotificationControl: add primary key PK_NotificationControl-------------------------
ALTER TABLE [dbo].[NotificationControl] ADD CONSTRAINT [PK_NotificationControl] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 222 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 222 is completed with errors' SET NOEXEC ON END
GO
--step 223: create table dbo.PasswordResetRequest---------------------------------------------------
CREATE TABLE [dbo].[PasswordResetRequest] (
[Id] [uniqueidentifier] NOT NULL,
[UserId] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Token] [uniqueidentifier] NOT NULL,
[ValidUntil] [datetime] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 223 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 223 is completed with errors' SET NOEXEC ON END
GO
--step 224: dbo.PasswordResetRequest: add default DF_PasswordResetRequest_Id------------------------
ALTER TABLE [dbo].[PasswordResetRequest] ADD CONSTRAINT [DF_PasswordResetRequest_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 224 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 224 is completed with errors' SET NOEXEC ON END
GO
--step 225: dbo.PasswordResetRequest: add primary key PK_PasswordResetRequest-----------------------
ALTER TABLE [dbo].[PasswordResetRequest] ADD CONSTRAINT [PK_PasswordResetRequest] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 225 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 225 is completed with errors' SET NOEXEC ON END
GO
--step 226: dbo.PasswordResetRequest: add foreign key FK_PasswordResetRequest_AspNetUsers-----------
ALTER TABLE [dbo].[PasswordResetRequest] ADD CONSTRAINT [FK_PasswordResetRequest_AspNetUsers] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 226 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 226 is completed with errors' SET NOEXEC ON END
GO
--step 227: create table dbo.PeopleResource2Team----------------------------------------------------
CREATE TABLE [dbo].[PeopleResource2Team] (
[Id] [uniqueidentifier] NOT NULL,
[PeopleResourceId] [uniqueidentifier] NOT NULL,
[TeamId] [uniqueidentifier] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NULL,
[Allocation] [smallint] NOT NULL,
[DateCreated] [datetime] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 227 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 227 is completed with errors' SET NOEXEC ON END
GO
--step 228: dbo.PeopleResource2Team: add default DF_PeopleResource2Team_Id--------------------------
ALTER TABLE [dbo].[PeopleResource2Team] ADD CONSTRAINT [DF_PeopleResource2Team_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 228 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 228 is completed with errors' SET NOEXEC ON END
GO
--step 229: dbo.PeopleResource2Team: add default DF_PeopleResource2Team_Allocation------------------
ALTER TABLE [dbo].[PeopleResource2Team] ADD CONSTRAINT [DF_PeopleResource2Team_Allocation] DEFAULT ((100)) FOR [Allocation]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 229 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 229 is completed with errors' SET NOEXEC ON END
GO
--step 230: dbo.PeopleResource2Team: add default DF_PeopleResource2Team_DateCreated-----------------
ALTER TABLE [dbo].[PeopleResource2Team] ADD CONSTRAINT [DF_PeopleResource2Team_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 230 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 230 is completed with errors' SET NOEXEC ON END
GO
--step 231: add index IX_PeopleResource2Team1 to table dbo.PeopleResource2Team----------------------
CREATE NONCLUSTERED INDEX [IX_PeopleResource2Team1] ON [dbo].[PeopleResource2Team]([PeopleResourceId]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 231 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 231 is completed with errors' SET NOEXEC ON END
GO
--step 232: add index IX_PeopleResource2Team2 to table dbo.PeopleResource2Team----------------------
CREATE NONCLUSTERED INDEX [IX_PeopleResource2Team2] ON [dbo].[PeopleResource2Team]([TeamId]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 232 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 232 is completed with errors' SET NOEXEC ON END
GO
--step 233: add index IX_PeopleResource2Team3 to table dbo.PeopleResource2Team----------------------
CREATE NONCLUSTERED INDEX [IX_PeopleResource2Team3] ON [dbo].[PeopleResource2Team]([StartDate], [EndDate]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 233 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 233 is completed with errors' SET NOEXEC ON END
GO
--step 234: dbo.PeopleResource2Team: add primary key PK_PeopleResource2Team-------------------------
ALTER TABLE [dbo].[PeopleResource2Team] ADD CONSTRAINT [PK_PeopleResource2Team] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 234 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 234 is completed with errors' SET NOEXEC ON END
GO
--step 235: dbo.PeopleResource2Team: add foreign key FK_PeopleResource2Team_PeopleResource----------
ALTER TABLE [dbo].[PeopleResource2Team] ADD CONSTRAINT [FK_PeopleResource2Team_PeopleResource] FOREIGN KEY ([PeopleResourceId]) REFERENCES [dbo].[PeopleResource] ([Id]) ON UPDATE CASCADE
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 235 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 235 is completed with errors' SET NOEXEC ON END
GO
--step 236: dbo.PeopleResource2Team: add foreign key FK_PeopleResource2Team_Team--------------------
ALTER TABLE [dbo].[PeopleResource2Team] ADD CONSTRAINT [FK_PeopleResource2Team_Team] FOREIGN KEY ([TeamId]) REFERENCES [dbo].[Team] ([Id]) ON UPDATE CASCADE
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 236 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 236 is completed with errors' SET NOEXEC ON END
GO
--step 237: create table dbo.PeopleResourceActuals--------------------------------------------------
CREATE TABLE [dbo].[PeopleResourceActuals] (
[Id] [uniqueidentifier] NOT NULL,
[PeopleResourceId] [uniqueidentifier] NOT NULL,
[ParentId] [uniqueidentifier] NOT NULL,
[Quantity] [decimal](18, 6) NOT NULL,
[Cost] [decimal](15, 4) NOT NULL,
[RowCreateDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 237 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 237 is completed with errors' SET NOEXEC ON END
GO
--step 238: dbo.PeopleResourceActuals: add default DF_PeopleResourceActuals_Id----------------------
ALTER TABLE [dbo].[PeopleResourceActuals] ADD CONSTRAINT [DF_PeopleResourceActuals_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 238 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 238 is completed with errors' SET NOEXEC ON END
GO
--step 239: dbo.PeopleResourceActuals: add primary key PK_PeopleResourceActuals---------------------
ALTER TABLE [dbo].[PeopleResourceActuals] ADD CONSTRAINT [PK_PeopleResourceActuals] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 239 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 239 is completed with errors' SET NOEXEC ON END
GO
--step 240: dbo.PeopleResourceActuals: add unique UK_PeopleResourceActuals--------------------------
ALTER TABLE [dbo].[PeopleResourceActuals] ADD CONSTRAINT [UK_PeopleResourceActuals] UNIQUE NONCLUSTERED ([PeopleResourceId], [ParentId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 240 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 240 is completed with errors' SET NOEXEC ON END
GO
--step 241: dbo.PeopleResourceActuals: add foreign key FK_PeopleResourceActuals_PeopleResource------
ALTER TABLE [dbo].[PeopleResourceActuals] ADD CONSTRAINT [FK_PeopleResourceActuals_PeopleResource] FOREIGN KEY ([PeopleResourceId]) REFERENCES [dbo].[PeopleResource] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 241 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 241 is completed with errors' SET NOEXEC ON END
GO
--step 242: create table dbo.PeopleResourceAllocation-----------------------------------------------
CREATE TABLE [dbo].[PeopleResourceAllocation] (
[Id] [uniqueidentifier] NOT NULL,
[ScenarioId] [uniqueidentifier] NOT NULL,
[PeopleResourceId] [uniqueidentifier] NOT NULL,
[ExpenditureCategoryId] [uniqueidentifier] NOT NULL,
[WeekEndingDate] [datetime] NULL,
[Quantity] [decimal](18, 6) NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 242 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 242 is completed with errors' SET NOEXEC ON END
GO
--step 243: dbo.PeopleResourceAllocation: add default DF_PeopleResourceAllocation_Id----------------
ALTER TABLE [dbo].[PeopleResourceAllocation] ADD CONSTRAINT [DF_PeopleResourceAllocation_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 243 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 243 is completed with errors' SET NOEXEC ON END
GO
--step 244: dbo.PeopleResourceAllocation: add primary key PK_PeopleResourceAllocation---------------
ALTER TABLE [dbo].[PeopleResourceAllocation] ADD CONSTRAINT [PK_PeopleResourceAllocation] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 244 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 244 is completed with errors' SET NOEXEC ON END
GO
--step 245: dbo.PeopleResourceAllocation: add foreign key FK_PeopleResourceAllocation_ExpenditureCategory
ALTER TABLE [dbo].[PeopleResourceAllocation] ADD CONSTRAINT [FK_PeopleResourceAllocation_ExpenditureCategory] FOREIGN KEY ([ExpenditureCategoryId]) REFERENCES [dbo].[ExpenditureCategory] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 245 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 245 is completed with errors' SET NOEXEC ON END
GO
--step 246: dbo.PeopleResourceAllocation: add foreign key FK_PeopleResourceAllocation_PeopleResource
ALTER TABLE [dbo].[PeopleResourceAllocation] ADD CONSTRAINT [FK_PeopleResourceAllocation_PeopleResource] FOREIGN KEY ([PeopleResourceId]) REFERENCES [dbo].[PeopleResource] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 246 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 246 is completed with errors' SET NOEXEC ON END
GO
--step 247: dbo.PeopleResourceAllocation: add foreign key FK_PeopleResourceAllocation_Scenario------
ALTER TABLE [dbo].[PeopleResourceAllocation] ADD CONSTRAINT [FK_PeopleResourceAllocation_Scenario] FOREIGN KEY ([ScenarioId]) REFERENCES [dbo].[Scenario] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 247 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 247 is completed with errors' SET NOEXEC ON END
GO
--step 248: create table dbo.PeopleResourceVacation-------------------------------------------------
CREATE TABLE [dbo].[PeopleResourceVacation] (
[Id] [uniqueidentifier] NOT NULL,
[PeopleResourceId] [uniqueidentifier] NOT NULL,
[VacationId] [uniqueidentifier] NOT NULL,
[WeekEndingDate] [datetime] NOT NULL,
[HoursOff] [int] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 248 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 248 is completed with errors' SET NOEXEC ON END
GO
--step 249: dbo.PeopleResourceVacation: add default DF_PeopleResourceVacation_Id--------------------
ALTER TABLE [dbo].[PeopleResourceVacation] ADD CONSTRAINT [DF_PeopleResourceVacation_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 249 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 249 is completed with errors' SET NOEXEC ON END
GO
--step 250: dbo.PeopleResourceVacation: add primary key PK_PeopleResourceVacation-------------------
ALTER TABLE [dbo].[PeopleResourceVacation] ADD CONSTRAINT [PK_PeopleResourceVacation] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 250 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 250 is completed with errors' SET NOEXEC ON END
GO
--step 251: dbo.PeopleResourceVacation: add foreign key FK_PeopleResourceVacation_PeopleResource----
ALTER TABLE [dbo].[PeopleResourceVacation] ADD CONSTRAINT [FK_PeopleResourceVacation_PeopleResource] FOREIGN KEY ([PeopleResourceId]) REFERENCES [dbo].[PeopleResource] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 251 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 251 is completed with errors' SET NOEXEC ON END
GO
--step 252: create table dbo.Vacation---------------------------------------------------------------
CREATE TABLE [dbo].[Vacation] (
[Id] [uniqueidentifier] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[PeopleResourceId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 252 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 252 is completed with errors' SET NOEXEC ON END
GO
--step 253: dbo.Vacation: add default DF_Vacation_Id------------------------------------------------
ALTER TABLE [dbo].[Vacation] ADD CONSTRAINT [DF_Vacation_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 253 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 253 is completed with errors' SET NOEXEC ON END
GO
--step 254: dbo.Vacation: add primary key PK_Vacation-----------------------------------------------
ALTER TABLE [dbo].[Vacation] ADD CONSTRAINT [PK_Vacation] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 254 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 254 is completed with errors' SET NOEXEC ON END
GO
--step 255: dbo.Vacation: add foreign key FK_Vacation_PeopleResource--------------------------------
ALTER TABLE [dbo].[Vacation] ADD CONSTRAINT [FK_Vacation_PeopleResource] FOREIGN KEY ([PeopleResourceId]) REFERENCES [dbo].[PeopleResource] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 255 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 255 is completed with errors' SET NOEXEC ON END
GO
--step 256: dbo.PeopleResourceVacation: add foreign key FK_PeopleResourceVacation_Vacation----------
ALTER TABLE [dbo].[PeopleResourceVacation] ADD CONSTRAINT [FK_PeopleResourceVacation_Vacation] FOREIGN KEY ([VacationId]) REFERENCES [dbo].[Vacation] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 256 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 256 is completed with errors' SET NOEXEC ON END
GO
--step 257: create table dbo.ProjectAccess----------------------------------------------------------
CREATE TABLE [dbo].[ProjectAccess] (
[PrincipalId] [uniqueidentifier] NOT NULL,
[ProjectId] [uniqueidentifier] NOT NULL,
[Read] [int] NOT NULL,
[Write] [int] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 257 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 257 is completed with errors' SET NOEXEC ON END
GO
--step 258: dbo.ProjectAccess: add default DF_ProjectAccess_Read------------------------------------
ALTER TABLE [dbo].[ProjectAccess] ADD CONSTRAINT [DF_ProjectAccess_Read] DEFAULT ((0)) FOR [Read]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 258 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 258 is completed with errors' SET NOEXEC ON END
GO
--step 259: dbo.ProjectAccess: add default DF_ProjectAccess_Write-----------------------------------
ALTER TABLE [dbo].[ProjectAccess] ADD CONSTRAINT [DF_ProjectAccess_Write] DEFAULT ((0)) FOR [Write]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 259 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 259 is completed with errors' SET NOEXEC ON END
GO
--step 260: dbo.ProjectAccess: add primary key PK_ProjectAccess-------------------------------------
ALTER TABLE [dbo].[ProjectAccess] ADD CONSTRAINT [PK_ProjectAccess] PRIMARY KEY CLUSTERED ([PrincipalId], [ProjectId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 260 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 260 is completed with errors' SET NOEXEC ON END
GO
--step 261: dbo.ProjectAccess: add foreign key FK_ProjectAccess_Project-----------------------------
ALTER TABLE [dbo].[ProjectAccess] ADD CONSTRAINT [FK_ProjectAccess_Project] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 261 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 261 is completed with errors' SET NOEXEC ON END
GO
--step 262: create table dbo.Rate-------------------------------------------------------------------
CREATE TABLE [dbo].[Rate] (
[Id] [uniqueidentifier] NOT NULL,
[ParentId] [uniqueidentifier] NULL,
[ExpenditureCategoryId] [uniqueidentifier] NOT NULL,
[Rate] [decimal](15, 4) NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[FreezeRate] [int] NOT NULL,
[Type] [smallint] NOT NULL,
[DerivedId] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 262 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 262 is completed with errors' SET NOEXEC ON END
GO
--step 263: dbo.Rate: add default DF_Rate_Id--------------------------------------------------------
ALTER TABLE [dbo].[Rate] ADD CONSTRAINT [DF_Rate_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 263 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 263 is completed with errors' SET NOEXEC ON END
GO
--step 264: dbo.Rate: add default DF__Rate__TypeInt__1209AD79---------------------------------------
ALTER TABLE [dbo].[Rate] ADD CONSTRAINT [DF__Rate__TypeInt__1209AD79] DEFAULT ((0)) FOR [Type]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 264 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 264 is completed with errors' SET NOEXEC ON END
GO
--step 265: dbo.Rate: add primary key PK_RateID-----------------------------------------------------
ALTER TABLE [dbo].[Rate] ADD CONSTRAINT [PK_RateID] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 265 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 265 is completed with errors' SET NOEXEC ON END
GO
--step 266: dbo.Rate: add foreign key FK_Rate_ExpenditureCategory-----------------------------------
ALTER TABLE [dbo].[Rate] ADD CONSTRAINT [FK_Rate_ExpenditureCategory] FOREIGN KEY ([ExpenditureCategoryId]) REFERENCES [dbo].[ExpenditureCategory] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 266 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 266 is completed with errors' SET NOEXEC ON END
GO
--step 267: create table dbo.Scenario2Group---------------------------------------------------------
CREATE TABLE [dbo].[Scenario2Group] (
[Id] [uniqueidentifier] NOT NULL,
[ScenarioId] [uniqueidentifier] NOT NULL,
[GroupId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 267 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 267 is completed with errors' SET NOEXEC ON END
GO
--step 268: dbo.Scenario2Group: add primary key PK_Scenario2Group-----------------------------------
ALTER TABLE [dbo].[Scenario2Group] ADD CONSTRAINT [PK_Scenario2Group] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 268 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 268 is completed with errors' SET NOEXEC ON END
GO
--step 269: dbo.Scenario2Group: add unique UK_Scenario2Group----------------------------------------
ALTER TABLE [dbo].[Scenario2Group] ADD CONSTRAINT [UK_Scenario2Group] UNIQUE NONCLUSTERED ([ScenarioId], [GroupId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 269 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 269 is completed with errors' SET NOEXEC ON END
GO
--step 270: create table dbo.SystemAttributes-------------------------------------------------------
CREATE TABLE [dbo].[SystemAttributes] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [int] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 270 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 270 is completed with errors' SET NOEXEC ON END
GO
--step 271: dbo.SystemAttributes: add default DF_SystemAttributes_Id--------------------------------
ALTER TABLE [dbo].[SystemAttributes] ADD CONSTRAINT [DF_SystemAttributes_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 271 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 271 is completed with errors' SET NOEXEC ON END
GO
--step 272: dbo.SystemAttributes: add primary key PK_SystemAttributesID-----------------------------
ALTER TABLE [dbo].[SystemAttributes] ADD CONSTRAINT [PK_SystemAttributesID] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 272 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 272 is completed with errors' SET NOEXEC ON END
GO
--step 273: dbo.Scenario2Group: add foreign key FK_Scenario2Group_GroupId---------------------------
ALTER TABLE [dbo].[Scenario2Group] ADD CONSTRAINT [FK_Scenario2Group_GroupId] FOREIGN KEY ([GroupId]) REFERENCES [dbo].[SystemAttributes] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 273 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 273 is completed with errors' SET NOEXEC ON END
GO
--step 274: dbo.Scenario2Group: add foreign key FK_Scenario2Group_ScenarioId------------------------
ALTER TABLE [dbo].[Scenario2Group] ADD CONSTRAINT [FK_Scenario2Group_ScenarioId] FOREIGN KEY ([ScenarioId]) REFERENCES [dbo].[Scenario] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 274 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 274 is completed with errors' SET NOEXEC ON END
GO
--step 275: create table dbo.ScenarioAccess---------------------------------------------------------
CREATE TABLE [dbo].[ScenarioAccess] (
[Id] [uniqueidentifier] NOT NULL,
[ParentId] [uniqueidentifier] NULL,
[ScenarioTypeArray] [nvarchar](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ScenarioStatusArray] [nvarchar](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ScenarioAccess] [nvarchar](384) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 275 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 275 is completed with errors' SET NOEXEC ON END
GO
--step 276: dbo.ScenarioAccess: add default DF_ScenarioAccess_Id------------------------------------
ALTER TABLE [dbo].[ScenarioAccess] ADD CONSTRAINT [DF_ScenarioAccess_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 276 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 276 is completed with errors' SET NOEXEC ON END
GO
--step 277: create table dbo.ScenarioDetail---------------------------------------------------------
CREATE TABLE [dbo].[ScenarioDetail] (
[Id] [uniqueidentifier] NOT NULL,
[ParentID] [uniqueidentifier] NULL,
[ExpenditureCategoryId] [uniqueidentifier] NULL,
[WeekEndingDate] [datetime] NULL,
[Quantity] [decimal](18, 6) NULL,
[WeekOrdinal] [int] NULL,
[Cost] [decimal](15, 4) NULL,
[CaptureHistory] [int] NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 277 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 277 is completed with errors' SET NOEXEC ON END
GO
--step 278: dbo.ScenarioDetail: add default DF_Scenario_Detail_Id-----------------------------------
ALTER TABLE [dbo].[ScenarioDetail] ADD CONSTRAINT [DF_Scenario_Detail_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 278 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 278 is completed with errors' SET NOEXEC ON END
GO
--step 279: add index DF_Scenario_Detail_Unique to table dbo.ScenarioDetail-------------------------
CREATE UNIQUE NONCLUSTERED INDEX [DF_Scenario_Detail_Unique] ON [dbo].[ScenarioDetail]([ParentID], [ExpenditureCategoryId], [WeekEndingDate]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 279 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 279 is completed with errors' SET NOEXEC ON END
GO
--step 280: add index IScenarioDetailsParentID to table dbo.ScenarioDetail--------------------------
CREATE NONCLUSTERED INDEX [IScenarioDetailsParentID] ON [dbo].[ScenarioDetail]([ParentID]) INCLUDE([ExpenditureCategoryId], [WeekEndingDate], [Quantity]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 280 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 280 is completed with errors' SET NOEXEC ON END
GO
--step 281: add index NCLX_IDX_ScenarioDetail.ParentID to table dbo.ScenarioDetail------------------
CREATE NONCLUSTERED INDEX [NCLX_IDX_ScenarioDetail.ParentID] ON [dbo].[ScenarioDetail]([ParentID]) INCLUDE([ExpenditureCategoryId], [WeekEndingDate], [Cost]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 281 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 281 is completed with errors' SET NOEXEC ON END
GO
--step 282: add index NCLX_IDX_ScenarioDetail_ParentId_ExpCid_WeekendDate to table dbo.ScenarioDetail
CREATE NONCLUSTERED INDEX [NCLX_IDX_ScenarioDetail_ParentId_ExpCid_WeekendDate] ON [dbo].[ScenarioDetail]([ParentID], [ExpenditureCategoryId], [WeekEndingDate]) INCLUDE([Id], [Quantity], [WeekOrdinal], [Cost]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 282 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 282 is completed with errors' SET NOEXEC ON END
GO
--step 283: add index NCLX_IDX_ScenarioDetail_ParentId_ExpCid_WeekendDate_2 to table dbo.ScenarioDetail
CREATE NONCLUSTERED INDEX [NCLX_IDX_ScenarioDetail_ParentId_ExpCid_WeekendDate_2] ON [dbo].[ScenarioDetail]([WeekEndingDate], [ParentID], [ExpenditureCategoryId]) INCLUDE([Id], [Quantity], [WeekOrdinal], [Cost]) WITH ( FILLFACTOR = 90 ) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 283 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 283 is completed with errors' SET NOEXEC ON END
GO
--step 284: dbo.ScenarioDetail: add primary key PK_Scenario_Detail----------------------------------
ALTER TABLE [dbo].[ScenarioDetail] ADD CONSTRAINT [PK_Scenario_Detail] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 284 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 284 is completed with errors' SET NOEXEC ON END
GO
--step 285: dbo.ScenarioDetail: add foreign key FK_ScenarioDetail_ExpenditureCategory---------------
ALTER TABLE [dbo].[ScenarioDetail] ADD CONSTRAINT [FK_ScenarioDetail_ExpenditureCategory] FOREIGN KEY ([ExpenditureCategoryId]) REFERENCES [dbo].[ExpenditureCategory] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 285 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 285 is completed with errors' SET NOEXEC ON END
GO
--step 286: create table dbo.Security---------------------------------------------------------------
CREATE TABLE [dbo].[Security] (
[PrincipalId] [uniqueidentifier] NOT NULL,
[Read] [int] NOT NULL,
[Write] [int] NOT NULL,
[SecurityObject] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 286 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 286 is completed with errors' SET NOEXEC ON END
GO
--step 287: dbo.Security: add default DF_Security_Read----------------------------------------------
ALTER TABLE [dbo].[Security] ADD CONSTRAINT [DF_Security_Read] DEFAULT ((0)) FOR [Read]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 287 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 287 is completed with errors' SET NOEXEC ON END
GO
--step 288: dbo.Security: add default DF_Security_Write---------------------------------------------
ALTER TABLE [dbo].[Security] ADD CONSTRAINT [DF_Security_Write] DEFAULT ((0)) FOR [Write]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 288 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 288 is completed with errors' SET NOEXEC ON END
GO
--step 289: dbo.Security: add primary key PK_Security-----------------------------------------------
ALTER TABLE [dbo].[Security] ADD CONSTRAINT [PK_Security] PRIMARY KEY CLUSTERED ([PrincipalId], [SecurityObject])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 289 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 289 is completed with errors' SET NOEXEC ON END
GO
--step 290: create table dbo.Skill------------------------------------------------------------------
CREATE TABLE [dbo].[Skill] (
[Id] [uniqueidentifier] NOT NULL,
[DateCreated] [datetime] NOT NULL,
[Name] [nvarchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EditTimestamp] [timestamp] NOT NULL,
[ParentId] [uniqueidentifier] NULL,
[HasChildren] [bit] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 290 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 290 is completed with errors' SET NOEXEC ON END
GO
--step 291: dbo.Skill: add default DF_Skill_Id------------------------------------------------------
ALTER TABLE [dbo].[Skill] ADD CONSTRAINT [DF_Skill_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 291 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 291 is completed with errors' SET NOEXEC ON END
GO
--step 292: dbo.Skill: add primary key PK_Skill-----------------------------------------------------
ALTER TABLE [dbo].[Skill] ADD CONSTRAINT [PK_Skill] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 292 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 292 is completed with errors' SET NOEXEC ON END
GO
--step 293: dbo.Skill: add foreign key FK_Skill_Skill-----------------------------------------------
ALTER TABLE [dbo].[Skill] ADD CONSTRAINT [FK_Skill_Skill] FOREIGN KEY ([ParentId]) REFERENCES [dbo].[Skill] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 293 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 293 is completed with errors' SET NOEXEC ON END
GO
--step 294: create table dbo.Skill2Resource---------------------------------------------------------
CREATE TABLE [dbo].[Skill2Resource] (
[Id] [uniqueidentifier] NOT NULL,
[Type] [smallint] NOT NULL,
[EffectiveDate] [datetime] NOT NULL,
[SkillId] [uniqueidentifier] NOT NULL,
[ResourceId] [uniqueidentifier] NOT NULL,
[Level] [smallint] NULL,
[Interested] [bit] NOT NULL,
[DateCreated] [datetime] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 294 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 294 is completed with errors' SET NOEXEC ON END
GO
--step 295: dbo.Skill2Resource: add default DF_Skill2Resource_Interested----------------------------
ALTER TABLE [dbo].[Skill2Resource] ADD CONSTRAINT [DF_Skill2Resource_Interested] DEFAULT ((0)) FOR [Interested]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 295 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 295 is completed with errors' SET NOEXEC ON END
GO
--step 296: dbo.Skill2Resource: add default DF_Skill2Resource_DateCreated---------------------------
ALTER TABLE [dbo].[Skill2Resource] ADD CONSTRAINT [DF_Skill2Resource_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 296 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 296 is completed with errors' SET NOEXEC ON END
GO
--step 297: dbo.Skill2Resource: add primary key PK_Skill2Resource-----------------------------------
ALTER TABLE [dbo].[Skill2Resource] ADD CONSTRAINT [PK_Skill2Resource] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 297 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 297 is completed with errors' SET NOEXEC ON END
GO
--step 298: dbo.Skill2Resource: add unique UK_Skill2Resource----------------------------------------
ALTER TABLE [dbo].[Skill2Resource] ADD CONSTRAINT [UK_Skill2Resource] UNIQUE NONCLUSTERED ([ResourceId], [SkillId], [Type], [EffectiveDate], [DateCreated])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 298 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 298 is completed with errors' SET NOEXEC ON END
GO
--step 299: dbo.Skill2Resource: add foreign key FK_Skill2Resource_ResourceId------------------------
ALTER TABLE [dbo].[Skill2Resource] ADD CONSTRAINT [FK_Skill2Resource_ResourceId] FOREIGN KEY ([ResourceId]) REFERENCES [dbo].[PeopleResource] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 299 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 299 is completed with errors' SET NOEXEC ON END
GO
--step 300: dbo.Skill2Resource: add foreign key FK_Skill2Resource_SkillId---------------------------
ALTER TABLE [dbo].[Skill2Resource] ADD CONSTRAINT [FK_Skill2Resource_SkillId] FOREIGN KEY ([SkillId]) REFERENCES [dbo].[Skill] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 300 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 300 is completed with errors' SET NOEXEC ON END
GO
--step 301: create table dbo.StrategicGoal----------------------------------------------------------
CREATE TABLE [dbo].[StrategicGoal] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[Color] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 301 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 301 is completed with errors' SET NOEXEC ON END
GO
--step 302: dbo.StrategicGoal: add primary key PK_StrategicGoal-------------------------------------
ALTER TABLE [dbo].[StrategicGoal] ADD CONSTRAINT [PK_StrategicGoal] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 302 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 302 is completed with errors' SET NOEXEC ON END
GO
--step 303: create table dbo.StrategicGoal2Company--------------------------------------------------
CREATE TABLE [dbo].[StrategicGoal2Company] (
[Id] [uniqueidentifier] NOT NULL,
[StrategicGoalId] [uniqueidentifier] NOT NULL,
[CompanyId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 303 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 303 is completed with errors' SET NOEXEC ON END
GO
--step 304: dbo.StrategicGoal2Company: add primary key PK_StrategicGoal2Company---------------------
ALTER TABLE [dbo].[StrategicGoal2Company] ADD CONSTRAINT [PK_StrategicGoal2Company] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 304 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 304 is completed with errors' SET NOEXEC ON END
GO
--step 305: dbo.StrategicGoal2Company: add unique UK_StrategicGoal2Company--------------------------
ALTER TABLE [dbo].[StrategicGoal2Company] ADD CONSTRAINT [UK_StrategicGoal2Company] UNIQUE NONCLUSTERED ([StrategicGoalId], [CompanyId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 305 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 305 is completed with errors' SET NOEXEC ON END
GO
--step 306: dbo.StrategicGoal2Company: add foreign key FK_StrategicGoal2Company_Company-------------
ALTER TABLE [dbo].[StrategicGoal2Company] ADD CONSTRAINT [FK_StrategicGoal2Company_Company] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Company] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 306 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 306 is completed with errors' SET NOEXEC ON END
GO
--step 307: dbo.StrategicGoal2Company: add foreign key FK_StrategicGoal2Company_Goal----------------
ALTER TABLE [dbo].[StrategicGoal2Company] ADD CONSTRAINT [FK_StrategicGoal2Company_Goal] FOREIGN KEY ([StrategicGoalId]) REFERENCES [dbo].[StrategicGoal] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 307 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 307 is completed with errors' SET NOEXEC ON END
GO
--step 308: create table dbo.StrategicGoal2Project--------------------------------------------------
CREATE TABLE [dbo].[StrategicGoal2Project] (
[Id] [uniqueidentifier] NOT NULL,
[ProjectId] [uniqueidentifier] NOT NULL,
[StrategicGoalId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 308 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 308 is completed with errors' SET NOEXEC ON END
GO
--step 309: dbo.StrategicGoal2Project: add primary key PK_StrategicGoal2Project---------------------
ALTER TABLE [dbo].[StrategicGoal2Project] ADD CONSTRAINT [PK_StrategicGoal2Project] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 309 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 309 is completed with errors' SET NOEXEC ON END
GO
--step 310: dbo.StrategicGoal2Project: add unique UK_StrategicGoal2Project--------------------------
ALTER TABLE [dbo].[StrategicGoal2Project] ADD CONSTRAINT [UK_StrategicGoal2Project] UNIQUE NONCLUSTERED ([ProjectId], [StrategicGoalId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 310 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 310 is completed with errors' SET NOEXEC ON END
GO
--step 311: dbo.StrategicGoal2Project: add foreign key FK_StrategicGoal2Project_Project-------------
ALTER TABLE [dbo].[StrategicGoal2Project] ADD CONSTRAINT [FK_StrategicGoal2Project_Project] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 311 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 311 is completed with errors' SET NOEXEC ON END
GO
--step 312: dbo.StrategicGoal2Project: add foreign key FK_StrategicGoal2Project_StrategicGoal-------
ALTER TABLE [dbo].[StrategicGoal2Project] ADD CONSTRAINT [FK_StrategicGoal2Project_StrategicGoal] FOREIGN KEY ([StrategicGoalId]) REFERENCES [dbo].[StrategicGoal] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 312 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 312 is completed with errors' SET NOEXEC ON END
GO
--step 313: create table dbo.supt_ImportErrors------------------------------------------------------
CREATE TABLE [dbo].[supt_ImportErrors] (
[Id] [uniqueidentifier] NOT NULL,
[ProcessID] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RecordNbr] [int] NOT NULL,
[Message] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateTimeProcessed] [datetime] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 313 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 313 is completed with errors' SET NOEXEC ON END
GO
--step 314: dbo.supt_ImportErrors: add default DF__supt_ImportE__Id__1A69E950-----------------------
ALTER TABLE [dbo].[supt_ImportErrors] ADD CONSTRAINT [DF__supt_ImportE__Id__1A69E950] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 314 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 314 is completed with errors' SET NOEXEC ON END
GO
--step 315: dbo.supt_ImportErrors: add primary key PK_supt_ImportErrors-----------------------------
ALTER TABLE [dbo].[supt_ImportErrors] ADD CONSTRAINT [PK_supt_ImportErrors] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 315 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 315 is completed with errors' SET NOEXEC ON END
GO
--step 316: create table dbo.supt_ImportMessages----------------------------------------------------
CREATE TABLE [dbo].[supt_ImportMessages] (
[Id] [uniqueidentifier] NOT NULL,
[GroupID] [uniqueidentifier] NOT NULL,
[MessageType] [int] NOT NULL,
[ProcessID] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RecordNbr] [int] NOT NULL,
[Message] [nvarchar](1500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateTimeProcessed] [datetime] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 316 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 316 is completed with errors' SET NOEXEC ON END
GO
--step 317: dbo.supt_ImportMessages: add default DF__supt_ImportE__Id__7E8CC4B1---------------------
ALTER TABLE [dbo].[supt_ImportMessages] ADD CONSTRAINT [DF__supt_ImportE__Id__7E8CC4B1] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 317 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 317 is completed with errors' SET NOEXEC ON END
GO
--step 318: dbo.supt_ImportMessages: add primary key PK_supt_ImportMessages-------------------------
ALTER TABLE [dbo].[supt_ImportMessages] ADD CONSTRAINT [PK_supt_ImportMessages] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 318 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 318 is completed with errors' SET NOEXEC ON END
GO
--step 319: create table dbo.supt_tbl_DbMaintenanceCommands-----------------------------------------
CREATE TABLE [dbo].[supt_tbl_DbMaintenanceCommands] (
[id] [int] IDENTITY(1, 1) NOT NULL,
[command] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[executed] [bit] NULL,
[Results] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 319 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 319 is completed with errors' SET NOEXEC ON END
GO
--step 320: create table dbo.supt_tbl_MongoDBBackup-------------------------------------------------
CREATE TABLE [dbo].[supt_tbl_MongoDBBackup] (
[id] [uniqueidentifier] NOT NULL,
[BackupDate] [datetime] NOT NULL,
[BackupedBy] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BackupData] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CollectionName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 320 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 320 is completed with errors' SET NOEXEC ON END
GO
--step 321: dbo.supt_tbl_MongoDBBackup: add default DF_supt_tbl_MongoDBBackup_id--------------------
ALTER TABLE [dbo].[supt_tbl_MongoDBBackup] ADD CONSTRAINT [DF_supt_tbl_MongoDBBackup_id] DEFAULT (newid()) FOR [id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 321 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 321 is completed with errors' SET NOEXEC ON END
GO
--step 322: dbo.supt_tbl_MongoDBBackup: add primary key PK_supt_tbl_MongoDBBackup-------------------
ALTER TABLE [dbo].[supt_tbl_MongoDBBackup] ADD CONSTRAINT [PK_supt_tbl_MongoDBBackup] PRIMARY KEY CLUSTERED ([id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 322 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 322 is completed with errors' SET NOEXEC ON END
GO
--step 323: create table dbo.supt_tbl_ProjectIds----------------------------------------------------
CREATE TABLE [dbo].[supt_tbl_ProjectIds] (
[Id] [uniqueidentifier] NOT NULL,
[ProjectID] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ParentId] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectValue] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[isTask] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 323 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 323 is completed with errors' SET NOEXEC ON END
GO
--step 324: dbo.supt_tbl_ProjectIds: add default DF_supt_tbl_ProjectIds_Id--------------------------
ALTER TABLE [dbo].[supt_tbl_ProjectIds] ADD CONSTRAINT [DF_supt_tbl_ProjectIds_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 324 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 324 is completed with errors' SET NOEXEC ON END
GO
--step 325: dbo.supt_tbl_ProjectIds: add primary key PK_supt_tbl_ProjectIds-------------------------
ALTER TABLE [dbo].[supt_tbl_ProjectIds] ADD CONSTRAINT [PK_supt_tbl_ProjectIds] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 325 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 325 is completed with errors' SET NOEXEC ON END
GO
--step 326: create table dbo.supt_tbl_RecParser-----------------------------------------------------
CREATE TABLE [dbo].[supt_tbl_RecParser] (
[Id] [uniqueidentifier] NOT NULL,
[ProcessID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ToType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ToName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ToField] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartPos] [int] NULL,
[Length] [int] NULL,
[index] [int] NULL,
[isDelimited] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Delimitor] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AdditionalFormat] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Overlay] [bit] NULL,
[DBFieldFormat] [int] NULL,
[TableOrder] [int] NULL,
[ForeignKey] [bit] NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 326 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 326 is completed with errors' SET NOEXEC ON END
GO
--step 327: dbo.supt_tbl_RecParser: add default DF_supt_tbl_RecParser_Id----------------------------
ALTER TABLE [dbo].[supt_tbl_RecParser] ADD CONSTRAINT [DF_supt_tbl_RecParser_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 327 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 327 is completed with errors' SET NOEXEC ON END
GO
--step 328: dbo.supt_tbl_RecParser: add primary key PK_supt_tbl_FileParser--------------------------
ALTER TABLE [dbo].[supt_tbl_RecParser] ADD CONSTRAINT [PK_supt_tbl_FileParser] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 328 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 328 is completed with errors' SET NOEXEC ON END
GO
--step 329: create table dbo.SystemSettings---------------------------------------------------------
CREATE TABLE [dbo].[SystemSettings] (
[Id] [uniqueidentifier] NOT NULL,
[Type] [int] NOT NULL,
[Value] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 329 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 329 is completed with errors' SET NOEXEC ON END
GO
--step 330: dbo.SystemSettings: add primary key PK_SystemSettings-----------------------------------
ALTER TABLE [dbo].[SystemSettings] ADD CONSTRAINT [PK_SystemSettings] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 330 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 330 is completed with errors' SET NOEXEC ON END
GO
--step 331: create table dbo.Team2Project-----------------------------------------------------------
CREATE TABLE [dbo].[Team2Project] (
[Id] [uniqueidentifier] NOT NULL,
[TeamId] [uniqueidentifier] NOT NULL,
[ProjectId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 331 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 331 is completed with errors' SET NOEXEC ON END
GO
--step 332: dbo.Team2Project: add primary key PK_Team2Project---------------------------------------
ALTER TABLE [dbo].[Team2Project] ADD CONSTRAINT [PK_Team2Project] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 332 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 332 is completed with errors' SET NOEXEC ON END
GO
--step 333: dbo.Team2Project: add unique UK_Team2Project--------------------------------------------
ALTER TABLE [dbo].[Team2Project] ADD CONSTRAINT [UK_Team2Project] UNIQUE NONCLUSTERED ([TeamId], [ProjectId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 333 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 333 is completed with errors' SET NOEXEC ON END
GO
--step 334: dbo.Team2Project: add foreign key FK_Team2Project_Project-------------------------------
ALTER TABLE [dbo].[Team2Project] ADD CONSTRAINT [FK_Team2Project_Project] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 334 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 334 is completed with errors' SET NOEXEC ON END
GO
--step 335: dbo.Team2Project: add foreign key FK_Team2Project_Team----------------------------------
ALTER TABLE [dbo].[Team2Project] ADD CONSTRAINT [FK_Team2Project_Team] FOREIGN KEY ([TeamId]) REFERENCES [dbo].[Team] ([Id]) ON DELETE CASCADE
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 335 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 335 is completed with errors' SET NOEXEC ON END
GO
--step 336: create table dbo.Team2Scenario----------------------------------------------------------
CREATE TABLE [dbo].[Team2Scenario] (
[Id] [uniqueidentifier] NOT NULL,
[ScenarioId] [uniqueidentifier] NOT NULL,
[TeamId] [uniqueidentifier] NOT NULL,
[Allocation] [int] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 336 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 336 is completed with errors' SET NOEXEC ON END
GO
--step 337: dbo.Team2Scenario: add default DF_Team2Scenario_Id--------------------------------------
ALTER TABLE [dbo].[Team2Scenario] ADD CONSTRAINT [DF_Team2Scenario_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 337 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 337 is completed with errors' SET NOEXEC ON END
GO
--step 338: dbo.Team2Scenario: add default DF_Team2Scenario_Allocation------------------------------
ALTER TABLE [dbo].[Team2Scenario] ADD CONSTRAINT [DF_Team2Scenario_Allocation] DEFAULT ((0)) FOR [Allocation]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 338 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 338 is completed with errors' SET NOEXEC ON END
GO
--step 339: dbo.Team2Scenario: add primary key PK_Team2Scenario-------------------------------------
ALTER TABLE [dbo].[Team2Scenario] ADD CONSTRAINT [PK_Team2Scenario] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 339 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 339 is completed with errors' SET NOEXEC ON END
GO
--step 340: dbo.Team2Scenario: add unique UK_Team2Scenario------------------------------------------
ALTER TABLE [dbo].[Team2Scenario] ADD CONSTRAINT [UK_Team2Scenario] UNIQUE NONCLUSTERED ([TeamId], [ScenarioId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 340 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 340 is completed with errors' SET NOEXEC ON END
GO
--step 341: dbo.Team2Scenario: add unique UQ_Team2Scenario------------------------------------------
ALTER TABLE [dbo].[Team2Scenario] ADD CONSTRAINT [UQ_Team2Scenario] UNIQUE NONCLUSTERED ([TeamId], [ScenarioId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 341 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 341 is completed with errors' SET NOEXEC ON END
GO
--step 342: dbo.Team2Scenario: add foreign key FK_Team2Scenario_Scenario----------------------------
ALTER TABLE [dbo].[Team2Scenario] ADD CONSTRAINT [FK_Team2Scenario_Scenario] FOREIGN KEY ([ScenarioId]) REFERENCES [dbo].[Scenario] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 342 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 342 is completed with errors' SET NOEXEC ON END
GO
--step 343: dbo.Team2Scenario: add foreign key FK_Team2Scenario_Team--------------------------------
ALTER TABLE [dbo].[Team2Scenario] ADD CONSTRAINT [FK_Team2Scenario_Team] FOREIGN KEY ([TeamId]) REFERENCES [dbo].[Team] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 343 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 343 is completed with errors' SET NOEXEC ON END
GO
--step 344: create table dbo.Team2View--------------------------------------------------------------
CREATE TABLE [dbo].[Team2View] (
[Id] [uniqueidentifier] NOT NULL,
[TeamId] [uniqueidentifier] NOT NULL,
[ViewId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 344 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 344 is completed with errors' SET NOEXEC ON END
GO
--step 345: dbo.Team2View: add primary key PK_Team2View---------------------------------------------
ALTER TABLE [dbo].[Team2View] ADD CONSTRAINT [PK_Team2View] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 345 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 345 is completed with errors' SET NOEXEC ON END
GO
--step 346: dbo.Team2View: add unique UK_Team2View--------------------------------------------------
ALTER TABLE [dbo].[Team2View] ADD CONSTRAINT [UK_Team2View] UNIQUE NONCLUSTERED ([TeamId], [ViewId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 346 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 346 is completed with errors' SET NOEXEC ON END
GO
--step 347: dbo.Team2View: add foreign key FK_Team2View_TeamId--------------------------------------
ALTER TABLE [dbo].[Team2View] ADD CONSTRAINT [FK_Team2View_TeamId] FOREIGN KEY ([TeamId]) REFERENCES [dbo].[Team] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 347 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 347 is completed with errors' SET NOEXEC ON END
GO
--step 348: dbo.Team2View: add foreign key FK_Team2View_View----------------------------------------
ALTER TABLE [dbo].[Team2View] ADD CONSTRAINT [FK_Team2View_View] FOREIGN KEY ([ViewId]) REFERENCES [dbo].[View] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 348 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 348 is completed with errors' SET NOEXEC ON END
GO
--step 349: create table dbo.TeamAllocation---------------------------------------------------------
CREATE TABLE [dbo].[TeamAllocation] (
[Id] [uniqueidentifier] NOT NULL,
[ScenarioId] [uniqueidentifier] NOT NULL,
[TeamId] [uniqueidentifier] NOT NULL,
[ExpenditureCategoryId] [uniqueidentifier] NOT NULL,
[WeekEndingDate] [datetime] NOT NULL,
[Quantity] [decimal](18, 6) NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 349 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 349 is completed with errors' SET NOEXEC ON END
GO
--step 350: dbo.TeamAllocation: add unique IX_TeamAllocation----------------------------------------
ALTER TABLE [dbo].[TeamAllocation] ADD CONSTRAINT [IX_TeamAllocation] UNIQUE NONCLUSTERED ([ScenarioId], [ExpenditureCategoryId], [TeamId], [WeekEndingDate])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 350 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 350 is completed with errors' SET NOEXEC ON END
GO
--step 351: dbo.TeamAllocation: add primary key PK_TeamAllocation-----------------------------------
ALTER TABLE [dbo].[TeamAllocation] ADD CONSTRAINT [PK_TeamAllocation] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 351 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 351 is completed with errors' SET NOEXEC ON END
GO
--step 352: dbo.TeamAllocation: add foreign key FK_TeamAllocation_ExpenditureCategoryId-------------
ALTER TABLE [dbo].[TeamAllocation] ADD CONSTRAINT [FK_TeamAllocation_ExpenditureCategoryId] FOREIGN KEY ([ExpenditureCategoryId]) REFERENCES [dbo].[ExpenditureCategory] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 352 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 352 is completed with errors' SET NOEXEC ON END
GO
--step 353: dbo.TeamAllocation: add foreign key FK_TeamAllocation_ScenarioId------------------------
ALTER TABLE [dbo].[TeamAllocation] ADD CONSTRAINT [FK_TeamAllocation_ScenarioId] FOREIGN KEY ([ScenarioId]) REFERENCES [dbo].[Scenario] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 353 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 353 is completed with errors' SET NOEXEC ON END
GO
--step 354: dbo.TeamAllocation: add foreign key FK_TeamAllocation_TeamId----------------------------
ALTER TABLE [dbo].[TeamAllocation] ADD CONSTRAINT [FK_TeamAllocation_TeamId] FOREIGN KEY ([TeamId]) REFERENCES [dbo].[Team] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 354 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 354 is completed with errors' SET NOEXEC ON END
GO
--step 355: create table dbo.Template_Store---------------------------------------------------------
CREATE TABLE [dbo].[Template_Store] (
[Scenario_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Category] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Week] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WeekEndDate] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Quantity] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cost] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Use_Quantity_Cost_Calculated] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CG_EFX] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GL_Dept] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Credit_Dept] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[uom] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Hrs__Week] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Income_Stmt_Type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[User_Defined_1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Average_Rate] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 355 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 355 is completed with errors' SET NOEXEC ON END
GO
--step 356: create table dbo.Template2TemplateGroup-------------------------------------------------
CREATE TABLE [dbo].[Template2TemplateGroup] (
[Id] [uniqueidentifier] NOT NULL,
[TemplateId] [uniqueidentifier] NOT NULL,
[TemplateGroupId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 356 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 356 is completed with errors' SET NOEXEC ON END
GO
--step 357: dbo.Template2TemplateGroup: add default DF_Template2TemplateGroup_Id--------------------
ALTER TABLE [dbo].[Template2TemplateGroup] ADD CONSTRAINT [DF_Template2TemplateGroup_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 357 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 357 is completed with errors' SET NOEXEC ON END
GO
--step 358: dbo.Template2TemplateGroup: add primary key PK_Template2TemplateGroup-------------------
ALTER TABLE [dbo].[Template2TemplateGroup] ADD CONSTRAINT [PK_Template2TemplateGroup] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 358 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 358 is completed with errors' SET NOEXEC ON END
GO
--step 359: dbo.Template2TemplateGroup: add unique UK_Template2TemplateGroup------------------------
ALTER TABLE [dbo].[Template2TemplateGroup] ADD CONSTRAINT [UK_Template2TemplateGroup] UNIQUE NONCLUSTERED ([TemplateId], [TemplateGroupId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 359 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 359 is completed with errors' SET NOEXEC ON END
GO
--step 360: dbo.Template2TemplateGroup: add foreign key FK_Template2TemplateGroup_Scenario----------
ALTER TABLE [dbo].[Template2TemplateGroup] ADD CONSTRAINT [FK_Template2TemplateGroup_Scenario] FOREIGN KEY ([TemplateId]) REFERENCES [dbo].[Scenario] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 360 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 360 is completed with errors' SET NOEXEC ON END
GO
--step 361: create table dbo.TemplateGroup----------------------------------------------------------
CREATE TABLE [dbo].[TemplateGroup] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 361 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 361 is completed with errors' SET NOEXEC ON END
GO
--step 362: dbo.TemplateGroup: add default DF_TemplateGroup_Id--------------------------------------
ALTER TABLE [dbo].[TemplateGroup] ADD CONSTRAINT [DF_TemplateGroup_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 362 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 362 is completed with errors' SET NOEXEC ON END
GO
--step 363: dbo.TemplateGroup: add primary key PK_TemplateGroup-------------------------------------
ALTER TABLE [dbo].[TemplateGroup] ADD CONSTRAINT [PK_TemplateGroup] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 363 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 363 is completed with errors' SET NOEXEC ON END
GO
--step 364: dbo.Template2TemplateGroup: add foreign key FK_Template2TemplateGroup_TemplateGroup-----
ALTER TABLE [dbo].[Template2TemplateGroup] ADD CONSTRAINT [FK_Template2TemplateGroup_TemplateGroup] FOREIGN KEY ([TemplateGroupId]) REFERENCES [dbo].[TemplateGroup] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 364 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 364 is completed with errors' SET NOEXEC ON END
GO
--step 365: create table dbo.Transaction------------------------------------------------------------
CREATE TABLE [dbo].[Transaction] (
[Id] [uniqueidentifier] NOT NULL,
[ParentId] [uniqueidentifier] NULL,
[UserID] [uniqueidentifier] NULL,
[DateAdded] [datetime] NULL,
[XMLDetail] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 365 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 365 is completed with errors' SET NOEXEC ON END
GO
--step 366: create table dbo.Type2TypeGroup---------------------------------------------------------
CREATE TABLE [dbo].[Type2TypeGroup] (
[Id] [uniqueidentifier] NOT NULL,
[TypeId] [uniqueidentifier] NOT NULL,
[TypeGroupId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 366 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 366 is completed with errors' SET NOEXEC ON END
GO
--step 367: dbo.Type2TypeGroup: add default DF_Type2TypeGroup_Id------------------------------------
ALTER TABLE [dbo].[Type2TypeGroup] ADD CONSTRAINT [DF_Type2TypeGroup_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 367 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 367 is completed with errors' SET NOEXEC ON END
GO
--step 368: dbo.Type2TypeGroup: add primary key PK_Type2TypeGroup-----------------------------------
ALTER TABLE [dbo].[Type2TypeGroup] ADD CONSTRAINT [PK_Type2TypeGroup] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 368 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 368 is completed with errors' SET NOEXEC ON END
GO
--step 369: dbo.Type2TypeGroup: add unique UK_Type2TypeGroup----------------------------------------
ALTER TABLE [dbo].[Type2TypeGroup] ADD CONSTRAINT [UK_Type2TypeGroup] UNIQUE NONCLUSTERED ([TypeId], [TypeGroupId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 369 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 369 is completed with errors' SET NOEXEC ON END
GO
--step 370: dbo.Type2TypeGroup: add foreign key FK_Type2TypeGroup_Type------------------------------
ALTER TABLE [dbo].[Type2TypeGroup] ADD CONSTRAINT [FK_Type2TypeGroup_Type] FOREIGN KEY ([TypeId]) REFERENCES [dbo].[Type] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 370 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 370 is completed with errors' SET NOEXEC ON END
GO
--step 371: create table dbo.TypeGroup--------------------------------------------------------------
CREATE TABLE [dbo].[TypeGroup] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 371 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 371 is completed with errors' SET NOEXEC ON END
GO
--step 372: dbo.TypeGroup: add default DF_TypeGroup_Id----------------------------------------------
ALTER TABLE [dbo].[TypeGroup] ADD CONSTRAINT [DF_TypeGroup_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 372 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 372 is completed with errors' SET NOEXEC ON END
GO
--step 373: dbo.TypeGroup: add primary key PK_TypeGroup---------------------------------------------
ALTER TABLE [dbo].[TypeGroup] ADD CONSTRAINT [PK_TypeGroup] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 373 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 373 is completed with errors' SET NOEXEC ON END
GO
--step 374: dbo.Type2TypeGroup: add foreign key FK_Type2TypeGroup_TypeGroup-------------------------
ALTER TABLE [dbo].[Type2TypeGroup] ADD CONSTRAINT [FK_Type2TypeGroup_TypeGroup] FOREIGN KEY ([TypeGroupId]) REFERENCES [dbo].[TypeGroup] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 374 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 374 is completed with errors' SET NOEXEC ON END
GO
--step 375: create table dbo.User-------------------------------------------------------------------
CREATE TABLE [dbo].[User] (
[Id] [uniqueidentifier] NOT NULL,
[Type] [int] NOT NULL,
[UserName] [nvarchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Password] [nvarchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MiddleInit] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmailAddress] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Phone] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fax] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MobilePhone] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastLoginDate] [datetime] NOT NULL,
[CreateDate] [datetime] NOT NULL,
[CreatedBy] [nvarchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastUpdateDate] [datetime] NOT NULL,
[UserStatus] [int] NOT NULL,
[ModifiedBy] [int] NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 375 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 375 is completed with errors' SET NOEXEC ON END
GO
--step 376: dbo.User: add default DF_User_Id--------------------------------------------------------
ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 376 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 376 is completed with errors' SET NOEXEC ON END
GO
--step 377: create table dbo.User2Team--------------------------------------------------------------
CREATE TABLE [dbo].[User2Team] (
[Id] [uniqueidentifier] NOT NULL,
[UserId] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TeamId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 377 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 377 is completed with errors' SET NOEXEC ON END
GO
--step 378: dbo.User2Team: add default DF_User2Team_Id----------------------------------------------
ALTER TABLE [dbo].[User2Team] ADD CONSTRAINT [DF_User2Team_Id] DEFAULT (newid()) FOR [Id]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 378 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 378 is completed with errors' SET NOEXEC ON END
GO
--step 379: dbo.User2Team: add primary key PK_User2Team---------------------------------------------
ALTER TABLE [dbo].[User2Team] ADD CONSTRAINT [PK_User2Team] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 379 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 379 is completed with errors' SET NOEXEC ON END
GO
--step 380: dbo.User2Team: add unique UK_User2Team--------------------------------------------------
ALTER TABLE [dbo].[User2Team] ADD CONSTRAINT [UK_User2Team] UNIQUE NONCLUSTERED ([TeamId], [UserId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 380 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 380 is completed with errors' SET NOEXEC ON END
GO
--step 381: dbo.User2Team: add foreign key FK_User2Team_AspNetUsers---------------------------------
ALTER TABLE [dbo].[User2Team] ADD CONSTRAINT [FK_User2Team_AspNetUsers] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 381 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 381 is completed with errors' SET NOEXEC ON END
GO
--step 382: dbo.User2Team: add foreign key FK_User2Team_Team----------------------------------------
ALTER TABLE [dbo].[User2Team] ADD CONSTRAINT [FK_User2Team_Team] FOREIGN KEY ([TeamId]) REFERENCES [dbo].[Team] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 382 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 382 is completed with errors' SET NOEXEC ON END
GO
--step 383: create table dbo.User2View--------------------------------------------------------------
CREATE TABLE [dbo].[User2View] (
[Id] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[ViewId] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 383 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 383 is completed with errors' SET NOEXEC ON END
GO
--step 384: dbo.User2View: add primary key PK_User2GLDepartment-------------------------------------
ALTER TABLE [dbo].[User2View] ADD CONSTRAINT [PK_User2GLDepartment] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 384 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 384 is completed with errors' SET NOEXEC ON END
GO
--step 385: dbo.User2View: add unique UK_User2View--------------------------------------------------
ALTER TABLE [dbo].[User2View] ADD CONSTRAINT [UK_User2View] UNIQUE NONCLUSTERED ([ViewId], [UserId])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 385 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 385 is completed with errors' SET NOEXEC ON END
GO
--step 386: dbo.User2View: add foreign key FK_User2Department_Department----------------------------
ALTER TABLE [dbo].[User2View] ADD CONSTRAINT [FK_User2Department_Department] FOREIGN KEY ([ViewId]) REFERENCES [dbo].[View] ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 386 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 386 is completed with errors' SET NOEXEC ON END
GO
--step 387: create table dbo.UserPreferences--------------------------------------------------------
CREATE TABLE [dbo].[UserPreferences] (
[Id] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[Url] [nvarchar](450) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Section] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Data] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 387 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 387 is completed with errors' SET NOEXEC ON END
GO
--step 388: dbo.UserPreferences: add primary key PK_UserPreferences---------------------------------
ALTER TABLE [dbo].[UserPreferences] ADD CONSTRAINT [PK_UserPreferences] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 388 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 388 is completed with errors' SET NOEXEC ON END
GO
--step 389: create table dbo.UserQuickLink----------------------------------------------------------
CREATE TABLE [dbo].[UserQuickLink] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[Url] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PageState] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 389 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 389 is completed with errors' SET NOEXEC ON END
GO
--step 390: dbo.UserQuickLink: add primary key PK_UserQuickLink-------------------------------------
ALTER TABLE [dbo].[UserQuickLink] ADD CONSTRAINT [PK_UserQuickLink] PRIMARY KEY CLUSTERED ([Id])
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 390 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 390 is completed with errors' SET NOEXEC ON END
GO
--step 391: create view dbo.BLL_Objects-------------------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[BLL_Objects] AS
SELECT Id, 'Company' AS EntityName FROM Company
UNION ALL
SELECT Id, 'View' FROM [View]
UNION ALL
SELECT Id, 'Team' FROM Team
UNION ALL
SELECT Id, 'Resource' FROM PeopleResource
UNION ALL
SELECT Id, 'Scenario' FROM Scenario
UNION ALL
SELECT Id, 'Project' FROM Project
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 391 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 391 is completed with errors' SET NOEXEC ON END
GO
--step 392: create view dbo.GLAccountClientsCount---------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[GLAccountClientsCount] AS
select A.Id, Count(B.Id) AS ClientCount
from GLAccount A
left join Client B ON (B.GLAccountId = A.Id)
group by A.Id
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 392 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 392 is completed with errors' SET NOEXEC ON END
GO
--step 393: create view dbo.GLAccountExpCatsCount---------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[GLAccountExpCatsCount] AS
select A.Id, Count(E.Id) AS ExpCatCount
from GLAccount A
left join ExpenditureCategory E ON (E.GLId = A.Id)
group by A.Id
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 393 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 393 is completed with errors' SET NOEXEC ON END
GO
--step 394: create view dbo.SC_TotalCGSeatsAcrossProject--------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[SC_TotalCGSeatsAcrossProject]
AS
SELECT SD.ParentId AS ScenarioId,
SD.WeekEndingDate AS WeekEndingDate,
SUM(SD.Quantity) AS Quantity,
SUM(SD.Cost) AS Cost,
EC.CGEFX AS CGEFX,
SC.Type AS ScenarioType,
SC.Name AS ScenarioName,
SH.ID AS ProjectObjectId,
SH.Name AS ProjectName,
SH.TypeId AS ProjectTypeId,
LS.Id AS ShowStatusId,
LS.Name AS ProjectStatusName,
SH.Probability,
SH.Color AS ProjectColor,
SC.Color As ScenarioColor,
SC.Status AS Status,
SC.SystemAttributeObjectID
FROM ScenarioDetail SD, ExpenditureCategory EC, Scenario SC, Project SH, Status LS
WHERE (EC.Id = SD.ExpenditureCategoryId)
AND (EC.Type = 1)
AND (SC.Type > 1)
AND (SC.Id = SD.ParentId)
AND (SH.Id = SC.ParentId)
AND (LS.Id = SH.StatusId)
GROUP BY SD.ParentId, SD.WeekEndingDate, EC.CGEFX,
SC.Type, SC.Status, SC.Name, SH.Probability, SC.Color, SC.SystemAttributeObjectID,
SH.Id, SH.Name, SH.Id, SH.Color, SH.TypeId,
LS.Id, LS.Name
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 394 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 394 is completed with errors' SET NOEXEC ON END
GO
--step 395: create view dbo.SC_TotalSeatsAcrossExpCats----------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[SC_TotalSeatsAcrossExpCats]
AS
SELECT SD.ParentID AS ScenarioObjectID,
SD.WeekEndingDate AS WeekEndingDate,
SUM(SD.Quantity) AS Quantity,
SUM(SD.Cost) AS Cost,
EC.CGEFX AS CGEFX,
SC.Type AS ScenarioType,
SC.Name AS ScenarioName,
SH.ID AS ShowObjectID,
SH.Name AS ShowName,
SH.TypeID AS ShowTypeObjectID,
LS.ID AS ShowStatusObjectID,
LS.Name AS ShowStatusName,
SH.Probability,
SH.Color AS ShowColor,
SC.Color AS ScenarioColor,
EC.ID AS ExpenditureCatObjectID,
SC.Status,
SC.SystemAttributeObjectID,
SG.GroupId,
EC.SystemAttributeOne,
EC.SystemAttributeTwo
FROM Scenario SC
LEFT JOIN Scenario2Group SG ON SG.ScenarioId = SC.ID
JOIN ScenarioDetail SD ON SC.ID = SD.ParentID
JOIN Project SH ON SC.ParentID = SH.ID
JOIN [Status] LS ON LS.ID = SH.StatusID
JOIN ExpenditureCategory EC ON EC.ID = SD.ExpenditureCategoryID
WHERE (SC.Type > 1)
GROUP BY SD.WeekEndingDate, SC.Type, SC.Status, SD.ParentID, SC.Name, SC.Color, SH.Probability,
SC.SystemAttributeObjectID,
SH.ID, SH.Name, SH.TypeID, SH.Color,
EC.CGEFX, EC.ID,
LS.ID, LS.Name, SG.GroupId,
EC.SystemAttributeOne,
EC.SystemAttributeTwo
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 395 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 395 is completed with errors' SET NOEXEC ON END
GO
--step 396: create view dbo.VW_ConvertFiscalCalendarToSatThruFri------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_ConvertFiscalCalendarToSatThruFri]
AS
SELECT TOP 100 PERCENT dbo.FiscalCalendar.Id, dbo.FiscalCalendarTemp.PeriodInt AS ConvPeriodInt, dbo.FiscalCalendar.PeriodInt AS SourcePeriodInt,
dbo.FiscalCalendarTemp.YearInt AS ConvYearInt, dbo.FiscalCalendar.YearInt AS SourceYearInt, dbo.FiscalCalendarTemp.QuarterInt AS ConvQuarterInt,
dbo.FiscalCalendar.QuarterInt AS SourceQuarterInt, dbo.FiscalCalendarTemp.StartDate AS ConvStartDate, dbo.FiscalCalendar.StartDate AS SourceStartDate,
dbo.FiscalCalendarTemp.EndDate AS ConvEndDate, dbo.FiscalCalendar.EndDate AS SourceEndDate
FROM dbo.FiscalCalendar LEFT OUTER JOIN
dbo.FiscalCalendarTemp ON dbo.FiscalCalendar.YearInt = dbo.FiscalCalendarTemp.YearInt AND dbo.FiscalCalendar.PeriodInt = dbo.FiscalCalendarTemp.PeriodInt
WHERE (dbo.FiscalCalendar.Type = 0) AND (dbo.FiscalCalendarTemp.Type = 0)
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 396 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 396 is completed with errors' SET NOEXEC ON END
GO
--step 397: create view dbo.VW_ExpenditureCategory--------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_ExpenditureCategory] AS
SELECT A.*,
(CASE
WHEN (A.CreditId IS NULL) THEN A.Name
ELSE A.Name + ' (' + B.Name + ')'
END) AS ExpCategoryWithCcName
FROM ExpenditureCategory A
LEFT JOIN CreditDepartment B ON (A.CreditId = B.Id)
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 397 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 397 is completed with errors' SET NOEXEC ON END
GO
--step 398: create view dbo.VW_ExpCategoriesInScenario----------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_ExpCategoriesInScenario]
AS
SELECT C.Id, C.Name as ExpenditureCategoryName,
D.Name AS ExpenditureName, C.[Type] AS ECType,
A.[Type] AS ScenarioType, A.Id AS ScenarioID,
A.Name AS ScenarioName, C.SortOrder, C.UseType, C.GLId,
C.ExpCategoryWithCcName
FROM dbo.Scenario A INNER JOIN
dbo.ScenarioDetail B ON A.Id = B.ParentID INNER JOIN
dbo.VW_ExpenditureCategory C ON B.ExpenditureCategoryId = C.Id INNER JOIN
dbo.Expenditure D ON C.ExpenditureId = D.Id
GROUP BY C.Id, C.Name, D.Name, C.[Type], A.[Type], A.Id, A.Name,
C.SortOrder, C.UseType, C.GLId, C.ExpCategoryWithCcName
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 398 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 398 is completed with errors' SET NOEXEC ON END
GO
--step 399: create view dbo.VW_Expenditure2Calculation----------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_Expenditure2Calculation]
AS
SELECT dbo.Expenditure2Expenditure.Id, E.Id AS ExpenditureCategoryID, E.Name AS ExpenditureCategoryName, E.GLId, E.UOMId,
E.Type, E.UseType, E.CGEFX, E.SortOrder,
E.SystemAttributeOne, E.SystemAttributeTwo, dbo.Expenditure.Name AS ExpenditureName,
dbo.Expenditure2Expenditure.FactorType, dbo.Expenditure2Expenditure.FactorInt, dbo.Expenditure2Expenditure.ProcessOrder,
dbo.Expenditure2Expenditure.ParentId, E.CreditId, E.WksSubjectToFee, E.ExpCategoryWithCcName
FROM dbo.Expenditure2Expenditure LEFT OUTER JOIN
dbo.Expenditure INNER JOIN
dbo.VW_ExpenditureCategory E ON dbo.Expenditure.Id = E.ExpenditureId ON dbo.Expenditure2Expenditure.ChildId = E.Id
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 399 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 399 is completed with errors' SET NOEXEC ON END
GO
--step 400: create view dbo.VW_Expenditure2Category-------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_Expenditure2Category]
AS
SELECT EC.Id, EC.ExpenditureId, EC.Name as ExpenditureCategoryName, EC.GLId, EC.UOMId, EC.Type, EC.UseType, EC.CGEFX, EC.SortOrder, LE.Name AS ExpenditureName, LG.GLNumber, LG.Name AS GLName,
LU.Name AS UOMName, EC.CreditId, EC.SystemAttributeOne, EC.SystemAttributeTwo, LC.Name AS CreditName, EC.WksSubjectToFee,
LS1.Name AS SystemAttributeNameOne, LS1.Type AS SystemAttributeTypeOne, LS2.Name AS SystemAttributeNameTwo, LS2.Type AS SystemAttributeTypeTwo,
EC.ExpCategoryWithCcName
FROM dbo.VW_ExpenditureCategory AS EC INNER JOIN
dbo.Expenditure AS LE ON EC.ExpenditureId = LE.Id LEFT OUTER JOIN
dbo.SystemAttributes AS LS1 ON EC.SystemAttributeOne = LS1.Id LEFT OUTER JOIN
dbo.SystemAttributes AS LS2 ON EC.SystemAttributeTwo = LS2.Id LEFT OUTER JOIN
dbo.CreditDepartment AS LC ON EC.CreditId = LC.Id LEFT OUTER JOIN
dbo.UOM AS LU ON EC.UOMId = LU.Id LEFT OUTER JOIN
dbo.GLAccount AS LG ON EC.GLId = LG.Id
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 400 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 400 is completed with errors' SET NOEXEC ON END
GO
--step 401: create view dbo.VW_Expenditure2FeeCalculation-------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_Expenditure2FeeCalculation]
AS
SELECT dbo.FeeCalculation.Id, dbo.FeeCalculation.ExpenditureCategoryId, dbo.FeeCalculation.MinShot, dbo.FeeCalculation.MaxShot, dbo.FeeCalculation.Quantity,
dbo.ExpenditureCategory.WksSubjectToFee
FROM dbo.FeeCalculation INNER JOIN
dbo.ExpenditureCategory ON dbo.FeeCalculation.ExpenditureCategoryId = dbo.ExpenditureCategory.Id
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 401 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 401 is completed with errors' SET NOEXEC ON END
GO
--step 402: create view dbo.VW_HolidayGroupItems----------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_HolidayGroupItems] AS
SELECT ROW_NUMBER ()
OVER (PARTITION BY HolidayGroupId ORDER BY EffectiveChangeDate) AS RN, *
FROM Holiday
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 402 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 402 is completed with errors' SET NOEXEC ON END
GO
--step 403: create view dbo.VW_Holiday--------------------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_Holiday] AS
SELECT A.Id, A.Name, A.WorkingDays, A.HolidayGroupId, A.EffectiveChangeDate, A.CreatedAt,
A.Options, A.StartDate, A.EndDate, A.CompanyImpact, A.IsInclude, DATEADD(DAY, -1, B.EffectiveChangeDate) AS ValidTo FROM VW_HolidayGroupItems A
LEFT JOIN VW_HolidayGroupItems B ON ((B.HolidayGroupId = A.HolidayGroupId) AND (B.RN = A.RN + 1))
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 403 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 403 is completed with errors' SET NOEXEC ON END
GO
--step 404: create view dbo.VW_WorkWeek-------------------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_WorkWeek] AS
SELECT WW.*,
(CAST(WW.Sunday as decimal) + CAST(WW.Monday as decimal) + CAST(WW.Tuesday as decimal) +
CAST(WW.Wednesday as decimal) + CAST(WW.Thursday as decimal) + CAST(WW.Friday as decimal) +
CAST(WW.Saturday as decimal)) AS WorkDaysAmount
FROM WorkWeek WW
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 404 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 404 is completed with errors' SET NOEXEC ON END
GO
--step 405: create view dbo.VW_Holidays2Resources---------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_Holidays2Resources] AS
-- Holidays for specified resources, Teams and ECs
(SELECT H.HolidayGroupId, PR.Id AS ResourceId, PR.WorkWeekId, PR.ExpenditureCategoryId, PR.TeamId
FROM Holiday H
LEFT JOIN Holiday2Team H2T ON (H2T.HolidayId = H.Id)
LEFT JOIN Holiday2ExpenditureCategory H2E ON (H2E.HolidayId = H.Id)
LEFT JOIN Holiday2PeopleResource H2P ON (H2P.HolidayId = H.Id)
LEFT JOIN PeopleResource PR ON
(PR.TeamId = H2T.TeamId) OR (PR.ExpenditureCategoryId = H2E.ExpenditureCategoryId) OR (H2P.ResourceId = PR.Id)
WHERE (H.CompanyImpact = 0) AND (H.IsInclude = 1) AND (H.WorkingDays = 0)
UNION
-- Holidays for all company resources
SELECT H.HolidayGroupId, PR.Id AS ResourceId, PR.WorkWeekId, PR.ExpenditureCategoryId, PR.TeamId
FROM Holiday H
LEFT JOIN PeopleResource PR ON (1 = 1)
WHERE (H.CompanyImpact = 1) AND (H.WorkingDays = 0)
UNION
-- Holidays for all resources, except specified resources, teams and ECs
SELECT H.HolidayGroupId, PR.Id AS ResourceId, PR.WorkWeekId, PR.ExpenditureCategoryId, PR.TeamId
FROM Holiday H
LEFT JOIN PeopleResource PR ON (1 = 1)
WHERE (H.CompanyImpact = 0) AND (H.IsInclude = 0) AND (H.WorkingDays = 0)
)
EXCEPT
SELECT H.HolidayGroupId, PR.Id AS ResourceId, PR.WorkWeekId, PR.ExpenditureCategoryId, PR.TeamId
FROM Holiday H
LEFT JOIN Holiday2Team H2T ON (H2T.HolidayId = H.Id)
LEFT JOIN Holiday2ExpenditureCategory H2E ON (H2E.HolidayId = H.Id)
LEFT JOIN Holiday2PeopleResource H2P ON (H2P.HolidayId = H.Id)
LEFT JOIN PeopleResource PR ON
(PR.TeamId = H2T.TeamId) OR (PR.ExpenditureCategoryId = H2E.ExpenditureCategoryId) OR (H2P.ResourceId = PR.Id)
WHERE (H.CompanyImpact = 0) AND (H.IsInclude = 0) AND (H.WorkingDays = 0)
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 405 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 405 is completed with errors' SET NOEXEC ON END
GO
--step 406: create view dbo.VW_ResourceWorkingDays--------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_ResourceWorkingDays] AS
select A.ResourceId, B.HolidayGroupId, B.WeekEndingDate, A.ExpenditureCategoryId, A.TeamId, WW.WorkDaysAmount,
MIN(CAST((B.Sunday & WW.Sunday) as decimal)) AS Sunday,
MIN(CAST((B.Monday & WW.Monday) as decimal)) AS Monday,
MIN(CAST((B.Tuesday & WW.Tuesday) as decimal)) AS Tuesday,
MIN(CAST((B.Wednesday & WW.Wednesday) as decimal)) AS Wednesday,
MIN(CAST((B.Thursday & WW.Thursday) as decimal)) AS Thursday,
MIN(CAST((B.Friday & WW.Friday) as decimal)) AS Friday,
MIN(CAST((B.Saturday & WW.Saturday) as decimal)) AS Saturday
from VW_Holidays2Resources A
INNER JOIN HolidayAllocation B ON (B.HolidayGroupId = A.HolidayGroupId)
INNER JOIN VW_WorkWeek WW ON (WW.Id = A.WorkWeekId)
GROUP BY A.ResourceId, B.HolidayGroupId, B.WeekEndingDate, A.ExpenditureCategoryId, A.TeamId, WW.WorkDaysAmount
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 406 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 406 is completed with errors' SET NOEXEC ON END
GO
--step 407: create view dbo.VW_HolidayAllocation----------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_HolidayAllocation] AS
SELECT WD.ResourceId AS PeopleResourceId, WD.ExpenditureCategoryId, WD.TeamId, WD.WeekEndingDate,
(CASE (WD.WorkDaysAmount)
WHEN 0 THEN 0
ELSE
(ISNULL(WD.Sunday + WD.Monday + WD.Tuesday + WD.Wednesday + WD.Thursday + WD.Friday + WD.Saturday, 0) /
WD.WorkDaysAmount)
END) AS AdjustmentKoeff
FROM VW_ResourceWorkingDays WD
--INNER JOIN ExpenditureCategory EC ON (EC.Id = WD.ExpenditureCategoryId)
--INNER JOIN UOM ON (UOM.Id = EC.UOMId)
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 407 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 407 is completed with errors' SET NOEXEC ON END
GO
--step 408: create view dbo.VW_Holidays2TeamsAndExpenditures----------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_Holidays2TeamsAndExpenditures] AS
-- Holidays for specified Teams and ECs
(SELECT H.HolidayGroupId, T.Id as TeamId, EC.Id as ExpenditureCategoryId
FROM Holiday H
LEFT JOIN Holiday2Team H2T ON (H2T.HolidayId = H.Id)
LEFT JOIN Holiday2ExpenditureCategory H2E ON (H2E.HolidayId = H.Id)
LEFT JOIN Team T on T.Id = H2T.TeamId or (H2T.Id IS NULL AND H2E.Id IS NOT NULL)
LEFT JOIN ExpenditureCategory EC on EC.Id = H2E.ExpenditureCategoryId or (H2E.Id IS NULL AND H2T.Id IS NOT NULL)
WHERE (H.CompanyImpact = 0) AND (H.IsInclude = 1) AND (H.WorkingDays = 0)
UNION
-- Holidays for all company resources
SELECT H.HolidayGroupId, T.Id as TeamId, EC.Id as ExpenditureCategoryId
FROM Holiday H
LEFT JOIN Team T ON (1 = 1)
LEFT JOIN ExpenditureCategory EC ON (1 = 1)
WHERE (H.CompanyImpact = 1) AND (H.WorkingDays = 0)
UNION
-- Holidays for all resources, except specified resources, teams and ECs
SELECT H.HolidayGroupId, T.Id as TeamId, EC.Id as ExpenditureCategoryId
FROM Holiday H
LEFT JOIN Team T ON (1 = 1)
LEFT JOIN ExpenditureCategory EC ON (1 = 1)
WHERE (H.CompanyImpact = 0) AND (H.IsInclude = 0) AND (H.WorkingDays = 0)
)
EXCEPT
(SELECT H.HolidayGroupId, H2T.TeamId, EC.Id as ExpenditureCategoryId
FROM Holiday H
INNER JOIN Holiday2Team H2T ON (H2T.HolidayId = H.Id)
LEFT JOIN ExpenditureCategory EC ON (1 = 1)
WHERE (H.CompanyImpact = 0) AND (H.IsInclude = 0) AND (H.WorkingDays = 0)
UNION
SELECT H.HolidayGroupId, T.Id as TeamId, H2E.ExpenditureCategoryId
FROM Holiday H
INNER JOIN Holiday2ExpenditureCategory H2E ON (H2E.HolidayId = H.Id)
LEFT JOIN Team T ON (1 = 1)
WHERE (H.CompanyImpact = 0) AND (H.IsInclude = 0) AND (H.WorkingDays = 0)
)
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 408 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 408 is completed with errors' SET NOEXEC ON END
GO
--step 409: create view dbo.VW_PlanningCapacityWorkingDays------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_PlanningCapacityWorkingDays] AS
select B.HolidayGroupId, B.WeekEndingDate, A.ExpenditureCategoryId, A.TeamId, WW.WorkDaysAmount,
MIN(CAST((B.Sunday & WW.Sunday) as decimal)) AS Sunday,
MIN(CAST((B.Monday & WW.Monday) as decimal)) AS Monday,
MIN(CAST((B.Tuesday & WW.Tuesday) as decimal)) AS Tuesday,
MIN(CAST((B.Wednesday & WW.Wednesday) as decimal)) AS Wednesday,
MIN(CAST((B.Thursday & WW.Thursday) as decimal)) AS Thursday,
MIN(CAST((B.Friday & WW.Friday) as decimal)) AS Friday,
MIN(CAST((B.Saturday & WW.Saturday) as decimal)) AS Saturday
from VW_Holidays2TeamsAndExpenditures A
INNER JOIN HolidayAllocation B ON (B.HolidayGroupId = A.HolidayGroupId)
INNER JOIN VW_WorkWeek WW ON WW.Id = (select top 1 Id from WorkWeek where IsSystem=1)
GROUP BY B.HolidayGroupId, B.WeekEndingDate, A.ExpenditureCategoryId, A.TeamId, WW.WorkDaysAmount
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 409 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 409 is completed with errors' SET NOEXEC ON END
GO
--step 410: create view dbo.VW_PlanningCapacityAdjusted---------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_PlanningCapacityAdjusted]
AS
SELECT distinct sc.Id as ScenarioId, WD.ExpenditureCategoryId, WD.TeamId, WD.WeekEndingDate,
(CASE (WD.WorkDaysAmount)
WHEN 0 THEN 0
ELSE (ISNULL(WD.Sunday + WD.Monday + WD.Tuesday + WD.Wednesday + WD.Thursday + WD.Friday + WD.Saturday, 0) / WD.WorkDaysAmount)
END) AS AdjustmentKoeff
FROM Scenario sc
inner join ScenarioDetail sd on sd.ParentID = sc.Id
inner join Team t on t.PlannedCapacityScenarioId = sc.Id
left join VW_PlanningCapacityWorkingDays WD on WD.TeamId = t.Id and WD.WeekEndingDate = sd.WeekEndingDate
where WD.WeekEndingDate IS NOT NULL
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 410 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 410 is completed with errors' SET NOEXEC ON END
GO
--step 411: create view dbo.VW_ProjectAccess--------------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_ProjectAccess] AS
SELECT K.UserId, L.ProjectId, L.[Read], L.Write FROM AspNetUserRoles K
INNER JOIN ProjectAccess L ON (L.PrincipalId = K.RoleId)
WHERE NOT EXISTS(
SELECT B.* FROM ProjectAccess B
WHERE (B.PrincipalId = K.UserId) AND (B.ProjectId = L.ProjectId)
)
UNION
SELECT M.PrincipalId, M.ProjectId, M.[Read], M.Write FROM ProjectAccess M
INNER JOIN AspNetUsers N ON (N.Id = M.PrincipalId)
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 411 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 411 is completed with errors' SET NOEXEC ON END
GO
--step 412: create view dbo.VW_ProjectAccessByUser--------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_ProjectAccessByUser] AS
select A.*, B.UserId,
ISNULL(C.Name, '') AS StatusName, ISNULL(D.Name, '') AS CompanyName, ISNULL(E.Name, '') AS ClientName,
ISNULL(F.Name, '') AS TypeName, G.Id AS ActiveScenarioId, ISNULL(G.Name, '') AS ActiveScenarioName,
ISNULL(
(
STUFF((SELECT ',' + t.Name AS [text()]
FROM dbo.Team2Project t2p
JOIN dbo.Team t
ON t2p.TeamId = t.Id
WHERE t2p.ProjectId = A.Id
ORDER BY t.Name ASC
FOR XML PATH('')), 1, 1, '')
), '') AS Teams,
B.[Read], B.Write from Project A
INNER JOIN VW_ProjectAccess B ON (B.ProjectId = A.Id)
LEFT JOIN [Status] C ON (C.Id = A.StatusId)
LEFT JOIN Company D ON (D.Id = A.CompanyId)
LEFT JOIN Client E ON (E.Id = A.ClientId)
LEFT JOIN [Type] F ON (F.Id = A.TypeId)
LEFT JOIN Scenario G ON (G.ParentId = A.Id) AND (G.[Type] = 2) AND (G.[Status] = 1) -- Poftfolio scenario & active
WHERE (B.[Read] = 1) OR (B.Write = 1)
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 412 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 412 is completed with errors' SET NOEXEC ON END
GO
--step 413: create view dbo.VW_ScenarioActualsPeriod------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- Period of actuals existance for every actuals scenario
CREATE VIEW [dbo].[VW_ScenarioActualsPeriod] AS
SELECT A.ParentId AS ScenarioId, MIN(A.WeekEndingDate) AS StartDate, MAX(A.WeekEndingDate) AS EndDate
FROM ScenarioDetail A
INNER JOIN Scenario B ON (A.ParentId = B.Id)
WHERE B.[Type] = 9
GROUP BY A.ParentID
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 413 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 413 is completed with errors' SET NOEXEC ON END
GO
--step 414: create view dbo.VW_ScenarioRelations----------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- The corresponding actuals scenario Id for every forecast scenario
CREATE VIEW [dbo].[VW_ScenarioRelations] AS
SELECT A.Id AS ForecastScenarioId, A.[Type] AS ForecastScenarioType, B.Id AS ActualsScenarioId
FROM Scenario A
INNER JOIN Scenario B ON (A.ParentId = B.ParentId)
WHERE A.[Type] <> 9
AND B.[Type] = 9
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 414 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 414 is completed with errors' SET NOEXEC ON END
GO
--step 415: create view dbo.VW_ScenarioActualsReplacePoints-----------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- Data points for every forecast scenario, that are missing in its corresponding actuals scenario
-- Forecast data from these points of scenario must be added to actuals to fill actuals gaps
CREATE VIEW [dbo].[VW_ScenarioActualsReplacePoints] AS
SELECT A.ParentID AS ForecastScenarioId, A.ExpenditureCategoryId, A.WeekEndingDate FROM ScenarioDetail A
INNER JOIN VW_ScenarioRelations C ON (A.ParentID = C.ForecastScenarioId)
INNER JOIN VW_ScenarioActualsPeriod D ON (C.ActualsScenarioId = D.ScenarioId) AND (A.WeekEndingDate <= D.EndDate)
WHERE C.ForecastScenarioType <> 9
EXCEPT
SELECT M.ForecastScenarioId, K.ExpenditureCategoryId, K.WeekEndingDate FROM ScenarioDetail K
INNER JOIN Scenario L ON (K.ParentID = L.Id)
INNER JOIN VW_ScenarioRelations M ON (K.ParentID = M.ActualsScenarioId)
WHERE L.[Type] = 9
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 415 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 415 is completed with errors' SET NOEXEC ON END
GO
--step 416: create view dbo.VW_ScenarioPseudoActuals------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_ScenarioPseudoActuals] AS
SELECT /* M.Id, */ O.ForecastScenarioId, M.ParentID as ActualsScenarioId, M.ExpenditureCategoryId, M.WeekEndingDate, M.Quantity,
M.Cost, 0 AS IsForecast
FROM ScenarioDetail M
INNER JOIN Scenario N ON (M.ParentID = N.Id)
INNER JOIN VW_ScenarioRelations O ON (M.ParentID = O.ActualsScenarioId)
WHERE N.[Type] = 9
UNION ALL
(
SELECT /* A.Id, */ A.ParentID AS ForecastScenarioId, C.ActualsScenarioId, A.ExpenditureCategoryId, A.WeekEndingDate, A.Quantity,
A.Cost, 1 AS IsForecast
FROM ScenarioDetail A
INNER JOIN VW_ScenarioActualsReplacePoints B ON (A.ParentID = B.ForecastScenarioId)
AND (A.ExpenditureCategoryId = B.ExpenditureCategoryId)
AND (A.WeekEndingDate = B.WeekEndingDate)
INNER JOIN VW_ScenarioRelations C ON (A.ParentID = C.ForecastScenarioId)
)
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 416 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 416 is completed with errors' SET NOEXEC ON END
GO
--step 417: create view dbo.VW_ScenarioActualsTotalCost---------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_ScenarioActualsTotalCost] AS
SELECT A.ForecastScenarioId, A.ActualsScenarioId, SUM(A.Cost) AS TotalCost
FROM VW_ScenarioPseudoActuals A
GROUP BY A.ForecastScenarioId, A.ActualsScenarioId
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 417 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 417 is completed with errors' SET NOEXEC ON END
GO
--step 418: create view dbo.VW_ScenarioForecastTotalCost--------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_ScenarioForecastTotalCost] AS
SELECT A.ParentID AS ForecastScenarioId, SUM(A.Cost) AS TotalCost
FROM ScenarioDetail A
INNER JOIN VW_ScenarioRelations B ON (A.ParentID = B.ForecastScenarioId)
INNER JOIN VW_ScenarioActualsPeriod C ON (B.ActualsScenarioId = C.ScenarioId) AND (A.WeekEndingDate <= C.EndDate)
GROUP BY A.ParentID
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 418 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 418 is completed with errors' SET NOEXEC ON END
GO
--step 419: create view dbo.VW_ScenarioPerformance--------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_ScenarioPerformance] AS
SELECT A.ForecastScenarioId, A.ActualsScenarioId, A.ForecastScenarioType,
B.TotalCost AS ForecastTotalCost, C.TotalCost AS ActualsTotalCost,
(CASE B.TotalCost
WHEN 0 THEN NULL
ELSE ABS((B.TotalCost - C.TotalCost) / B.TotalCost)
END) AS VariationPercent
FROM VW_ScenarioRelations A
LEFT JOIN VW_ScenarioForecastTotalCost B ON (A.ForecastScenarioId = B.ForecastScenarioId)
LEFT JOIN VW_ScenarioActualsTotalCost C ON ((A.ForecastScenarioId = C.ForecastScenarioId)
AND (A.ActualsScenarioId = C.ActualsScenarioId))
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 419 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 419 is completed with errors' SET NOEXEC ON END
GO
--step 420: create view dbo.VW_ProjectPerformance---------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_ProjectPerformance]
AS
SELECT A.ForecastScenarioId, A.ActualsScenarioId, A.ForecastScenarioType, A.ForecastTotalCost, A.ActualsTotalCost, A.VariationPercent, C.Id AS ProjectId,
C.Name AS ProjectName, B.Status, B.BUDirectCosts, B.EndDate AS ScenarioEndDate, C.TypeId AS ProjectTypeId, D.Name AS ProjectTypeName,
C.Priority AS ProjectPriority, B.BUDirectCosts - A.ForecastTotalCost + A.ActualsTotalCost AS ActualsCostForecasted,
A.ActualsTotalCost - A.ForecastTotalCost AS ActualsCostVariation, B.BUDirectCosts / C.Priority AS ProjectWeight, E.Id AS FiscalPeriodId,
S2G.GroupId AS ScenarioGroupId
FROM dbo.VW_ScenarioPerformance AS A INNER JOIN
dbo.Scenario AS B ON A.ForecastScenarioId = B.Id INNER JOIN
dbo.Project AS C ON B.ParentId = C.Id INNER JOIN
dbo.Type AS D ON C.TypeId = D.Id INNER JOIN
dbo.FiscalCalendar AS E ON B.EndDate >= E.StartDate AND B.EndDate <= E.EndDate LEFT OUTER JOIN
dbo.Scenario2Group AS S2G ON B.Id = S2G.ScenarioId
WHERE (B.Status = 1) AND (B.Type <> 9) AND (E.Type = 2)
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 420 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 420 is completed with errors' SET NOEXEC ON END
GO
--step 421: create view dbo.VW_Scenario2Project-----------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/* Update Dependent views and procs */
CREATE VIEW [dbo].[VW_Scenario2Project]
AS
SELECT DISTINCT
SC.Id, SC.ParentId, SC.TemplateId, SC.Type, SC.Name,
CASE WHEN SH.IsRevenueGenerating = 1 THEN SC.ProjectedRevenue ELSE SC.ProjectedExpense + ((isnull(SC.ExpectedGrossMargin, 0) / 100)
* isnull(SC.BUDirectCosts, 0)) END AS ProjectedRevenue, SC.ExpectedGrossMargin, SC.CalculatedGrossMargin, SC.CGSplit, SC.EFXSplit, SC.StartDate, SC.EndDate,
SC.Duration, SH.Priority, SH.Probability, SC.TDDirectCosts, SC.BUDirectCosts, SC.Shots, SC.TDRevenueShot, SC.BURevenueShot, SC.FreezeRevenue,
SC.LastUpdate, SH.Id AS ShowObjectID, SH.Name AS ShowName, SH.CompanyId, SH.ClientId, SH.TypeId, SH.StatusId, SC.Color, SC.Status, SC.UseLMMargin,
SC.ExpectedGrossMargin_LM, SC.CalculatedGrossMargin_LM, SC.TDDirectCosts_LM, SC.BUDirectCosts_LM, SC.BURevenueShot_LM, SC.ShotStartDate,
SC.GrowthScenario, SC.Actuals_BUDirectCosts, SC.Actuals_BUDirectCosts_LM, SC.SystemAttributeObjectID, s2g.GroupId,
SC.CostSavings, ParentProject.Id as ParentProjectId, ParentProject.Name as ParentProjectName, ParentProject.Color as ParentProjectColor
FROM dbo.Project AS SH
LEFT OUTER JOIN dbo.Project as ParentProject ON ParentProject.Id = SH.ParentProjectId
RIGHT OUTER JOIN dbo.Scenario AS SC ON SH.Id = SC.ParentId
LEFT OUTER JOIN dbo.Scenario2Group AS s2g ON s2g.ScenarioId = SC.Id
INNER JOIN dbo.Type AS t ON t.Id = SH.TypeId
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 421 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 421 is completed with errors' SET NOEXEC ON END
GO
--step 422: create view dbo.VW_ScenarioAndProxyDetails----------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/* Update Dependent views and procs */
CREATE VIEW [dbo].[VW_ScenarioAndProxyDetails]
AS
SELECT dbo.ScenarioDetail.Id, dbo.ScenarioDetail.ParentID, dbo.ScenarioDetail.ExpenditureCategoryId, dbo.ScenarioDetail.WeekEndingDate, dbo.ScenarioDetail.Quantity,
dbo.ScenarioDetail.WeekOrdinal, dbo.ScenarioDetail.Cost, A.Name as ExpenditureCategoryName, dbo.Expenditure.Name AS ExpenditureName,
A.GLId, A.UOMId, A.CreditId, A.Type,
A.UseType, A.CGEFX, A.SystemAttributeOne, A.SystemAttributeTwo,
A.SortOrder, A.ExpCategoryWithCcName
FROM dbo.ScenarioDetail INNER JOIN
dbo.VW_ExpenditureCategory A ON A.Id = dbo.ScenarioDetail.ExpenditureCategoryId INNER JOIN
dbo.Expenditure ON A.ExpenditureId = dbo.Expenditure.Id
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 422 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 422 is completed with errors' SET NOEXEC ON END
GO
--step 423: create view dbo.VW_Skill2Resource-------------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_Skill2Resource] AS
SELECT A.*, ISNULL(D.Id, A.SkillId) AS SkillGroupId,
CAST((CASE WHEN (D.Id IS NULL) THEN 1 ELSE 0 END) AS bit) AS AssignedToGroup,
E.Id AS TeamId, E.CompanyId, abs(datediff(D, dateadd(y, -1, GETUTCDATE()), A.effectivedate)) as PastYearDateOffset,
abs(datediff(D, A.effectivedate, dateadd(y, 1, GETUTCDATE()))) as FutureYearDateOffset
FROM Skill2Resource A
INNER JOIN Skill B ON (B.Id = A.SkillId)
INNER JOIN PeopleResource C ON (C.Id = A.ResourceId)
LEFT JOIN Skill D ON (D.Id = B.ParentId)
LEFT JOIN Team E ON (E.Id = C.TeamId)
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 423 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 423 is completed with errors' SET NOEXEC ON END
GO
--step 424: create procedure dbo.sp_AddBulkPeopleResource-------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[sp_AddBulkPeopleResource] (
@FirstName nvarchar(250),
@LastName nvarchar(250),
@Title nvarchar(200),
@CreditNumber nvarchar(100),
@StartDate datetime=null,
@EndDate datetime=null,
@JobCode nvarchar(25),
@EmployeeID nvarchar(100),
@ProcessID nvarchar(100),
@RecordNumber int
) AS
BEGIN
BEGIN TRANSACTION
declare @isActive bit =1;
declare @defaultUofMTypeCode int =5
declare @defaultGLTypeCode int=6;
declare @defaultECTypeForPeopleResource int =1
declare @defaultECUseTypeForPeopleResource int =1
declare @defaultECCGEFXForPeopleResource nvarchar(10) ='CG'
-- declare @EndDate datetime
--using title and creditnumber determine if we have matching records to get an EC
declare @ECID uniqueidentifier
declare @CreditDepertmentID uniqueidentifier
declare @EXID uniqueidentifier
-- get credit department id
select @CreditDepertmentID=Id from CreditDepartment where CreditNumber=@CreditNumber
--get expenditure id based on title
select @EXID=Id from Expenditure where Jobcode=@JobCode
if (@EXID is null)
select @EXID=Id from Expenditure where Name=@Title
if not @EXID is null
begin
select @ECID=Id FROM ExpenditureCategory WHERE ExpenditureId=@EXID AND CreditId=@CreditDepertmentID
end
--add new EC??
if @EXID is null and not @CreditDepertmentID is null
begin
select @EXID=NEWID();
insert into Expenditure (Id, Name,JobCode) values(@EXID,@Title,@JobCode)
end
IF @ECID IS null and not @EXID is null and not @CreditDepertmentID is null
begin
declare @UofM uniqueidentifier
declare @GLid uniqueidentifier
select top 1 @UofM=convert(uniqueidentifier,Value) from SystemSettings where Type=@defaultUofMTypeCode
select top 1 @GLid=convert(uniqueidentifier,Value) from SystemSettings where Type=@defaultGLTypeCode
select @ECID =NEWID();
insert into ExpenditureCategory(Id,ExpenditureId,GLId,UOMId,CreditId,Type,UseType,CGEFX,WksSubjectToFee,SystemAttributeOne,SystemAttributeTwo,Name) values
(@ECID,@EXID,@GLid,@UofM,@CreditDepertmentID,@defaultECTypeForPeopleResource,@defaultECUseTypeForPeopleResource,
@defaultECCGEFXForPeopleResource,null,null,null,@title)
end
else if not @ECID is null
begin
update ExpenditureCategory set Name=@title where Id=@ECID
end
if @CreditDepertmentID is null
begin
insert into supt_ImportErrors (ProcessID,RecordNbr,Message,DateTimeProcessed) values (@ProcessID,@RecordNumber,'NO CreditCode for "'+@CreditNumber+'" Record skipped',GETDATE())
goto exit_commit
end
--if we have a credit department id and expenditure id we can get
--excpediture category id used to tie to the people resourse
if @ECID is null
begin
insert into supt_ImportErrors (ProcessID,RecordNbr,Message,DateTimeProcessed) values (@ProcessID,@RecordNumber,'NO Expenditure category found for Expenditure:"'+@title+'" Credit Code:"'+@CreditNumber+'" Record skipped',GETDATE())
goto exit_commit
end
--see if we have an existing people resource record.
--first search is by first name and employeeid, second is by first and last name
declare @CurPeopleResourceRecID uniqueidentifier
select @CurPeopleResourceRecID= Id FROM PeopleResource where FirstName=@FirstName and EmployeeID=@EmployeeID
if @CurPeopleResourceRecID is null
begin
select @CurPeopleResourceRecID= Id FROM PeopleResource where FirstName=@FirstName and LastName=@LastName
end
-- if we do not have an existing record create a new one
if @CurPeopleResourceRecID is null
begin
if not isnull(@EndDate,'')=''
begin
declare @nbrDays int=0
SELECT @nbrDays= DATEDIFF(day,GETDATE(),@EndDate)
if (@nbrDays <= 0 )
set @isActive=0
end
if isnull(@EndDate,'')=''
set @EndDate=DATEADD (YEAR , 50 , getdate() )
if isnull(@StartDate,'')=''
set @StartDate=getdate()
--inserting a new record, the start date should be today, the end date will be today +50 years.
insert into PeopleResource ( FirstName,LastName,IsActiveEmployee,ExpenditureCategoryId,StartDate,EndDate,EmployeeID) values (@FirstName,@LastName,@isActive,@ECID,@StartDate,@EndDate,@EmployeeID)
end
--we have an existing record determine if we have to set the start/end dates based on isActive value
else
begin
declare @curECID uniqueidentifier
declare @StartDateCur datetime = GetDate();
declare @EndDateCur datetime = GetDate();
declare @isActiveCur bit
select @curECID=ExpenditureCategoryId,@isActiveCur=IsActiveEmployee,@StartDateCur=StartDate,@EndDateCur=EndDate FROM PeopleResource where FirstName=@FirstName and LastName=@LastName
if @isActiveCur <> @isActive
begin
if @isActive = 0
begin
if isnull(@EndDate ,'') = ''
set @EndDate=getdate();
end
else
begin
if @StartDate is null
set @StartDate=DATEADD(DAY,-1,getdate());
if isnull(@EndDate ,'') = ''
set @EndDate=DATEADD (YEAR , 50 , getdate() );
end
end
else
begin
set @StartDate=@StartDateCur
set @EndDate=@EndDateCur
end
update PeopleResource set StartDate=@StartDate,EndDate=@EndDate, IsActiveEmployee=@isActive, ExpenditureCategoryId=@ECID ,LastName=@LastName, EmployeeID=@EmployeeID where ID=@CurPeopleResourceRecID
end
exit_commit:
COMMIT TRANSACTION
END
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 424 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 424 is completed with errors' SET NOEXEC ON END
GO
--step 425: create procedure dbo.sp_DBMaintenance---------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[sp_DBMaintenance] AS
begin
truncate table supt_tbl_DbMaintenanceCommands
DBCC CHECKIDENT (supt_tbl_DbMaintenanceCommands, reseed, 34)
SELECT [Object Name] = object_name(ps.object_id),
[Index Name] = i.name,
[Fragmentation %] = cast(avg_fragmentation_in_percent as varchar(15)),
[Stats Updated] = STATS_DATE(i.object_id, i.index_id)
FROM sys.dm_db_index_physical_stats(db_id(), null, null, null, 'sampled') AS ps
JOIN sys.indexes AS i
ON ps.[object_id] = i.[object_id]
AND ps.index_id = i.index_id
WHERE (ps.avg_fragmentation_in_percent >= 7
OR ps.avg_page_space_used_in_percent < 75)
AND ps.page_count > 8
AND ps.index_id > 0
-- Update Statistics (previous update date is listed after each statement generated)
insert into supt_tbl_DbMaintenanceCommands (command,executed)
select 'Update Statistics [' + schema_name(o.schema_id) + '].[' + o.name + '] [' + i.name + '] WITH SAMPLE 100 Percent;' ,0
from sys.objects o
join sys.indexes i
on o.object_id = i.object_id
and OBJECTPROPERTY(o.object_id, N'IsUserTable') = 1
and i.index_id > 0
order by isnull(convert(char(10), STATS_DATE(o.object_id, i.index_id), 101), 'N/A') asc
insert into supt_tbl_DbMaintenanceCommands (command,executed)
-- reorganize indexes (fragmentation will show after each statement generated)
SELECT 'alter index [' + i.name + '] on [' + object_name(ps.object_id) + '] reorganize -- ' + cast(avg_fragmentation_in_percent as varchar(15)),0
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
JOIN sys.indexes AS i
ON ps.[object_id] = i.[object_id]
AND ps.index_id = i.index_id
AND i.type_desc <> 'HEAP'
where avg_fragmentation_in_percent > 7
insert into supt_tbl_DbMaintenanceCommands (command,executed)
-- rebuild indexes (fragmentation will show after each statement generated)
SELECT 'alter index [' + i.name + '] on [' + object_name(ps.object_id) + '] rebuild -- ' + cast(avg_fragmentation_in_percent as varchar(15)),0
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
JOIN sys.indexes AS i
ON ps.[object_id] = i.[object_id]
AND ps.index_id = i.index_id
AND i.type_desc <> 'HEAP'
where avg_fragmentation_in_percent > 7
insert into supt_tbl_DbMaintenanceCommands (command,executed)
-- find indexes with 0% fill factor and rebuild with 90%
select 'alter index [' + b.name + '] on [' + schema_name(a.schema_id) + '].[' + a.name + '] rebuild with (online = off' + case b.fill_factor when 0 then ', fillfactor = 90' else '' end + ');' ,0
from sys.objects a
join sys.indexes b
on a.object_id = b.object_id
where a.type = 'u'
and a.name <> 'dtproperties'
and b.fill_factor = 0
and b.name is not NULL
declare @max int =0
select @max=max(id) from supt_tbl_DbMaintenanceCommands where isnull(executed,0) = 0
declare @on int=1;
while (@on <= @max)
begin
declare @cmd nvarchar(max)
select @cmd=command from supt_tbl_DbMaintenanceCommands where id = @on
if not isnull(@cmd,'') = ''
begin
declare @results nvarchar(max)
declare @cd bit=1
BEGIN TRY
EXECUTE sp_executesql @cmd
set @results='success';
END TRY
BEGIN CATCH
SELECT @results= ERROR_MESSAGE(),@cd=null
END CATCH
update supt_tbl_DbMaintenanceCommands set Results=@results, executed=@cd where id=@on
end
set @on=@on+1
end
end
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 425 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 425 is completed with errors' SET NOEXEC ON END
GO
--step 426: create procedure dbo.sp_DeletePeopleResource--------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[sp_DeletePeopleResource] (@id uniqueidentifier)
AS
BEGIN
begin transaction
delete from Holiday2PeopleResource where ResourceId = @id
delete from NonProjectTimeAllocation where PeopleResourceId = @id
delete from PeopleResourceVacation where PeopleResourceId = @id
delete from PeopleResourceAllocation where PeopleResourceId = @id
delete from Skill2Resource where ResourceId = @id
delete from PeopleResource2Team where PeopleResourceId = @id
delete from PeopleResource where Id = @id
commit transaction
END
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 426 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 426 is completed with errors' SET NOEXEC ON END
GO
--step 427: create procedure dbo.sp_DeleteScenario--------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[sp_DeleteScenario] (@aScenarioOID uniqueidentifier) AS
BEGIN
BEGIN TRANSACTION
--SA. ENV-885. Remove links to the template, if the scenario, we are going to delete, is a template
update dbo.Scenario set TemplateId = null where TemplateId = @aScenarioOID
delete from dbo.TeamAllocation where ScenarioId = @aScenarioOID
delete from dbo.Team2Scenario where ScenarioId = @aScenarioOID
delete from dbo.CostSaving where ScenarioId = @aScenarioOID --SA. ENV-885
delete from dbo.PeopleResourceAllocation where ScenarioId = @aScenarioOID --SA. ENV-885
delete from dbo.Scenario2Group where ScenarioId = @aScenarioOID --SA. ENV-885
delete from Note where ParentId = @aScenarioOID
delete from Rate where ParentId = @aScenarioOID
delete from ScenarioDetail where ScenarioDetail.ParentID = @aScenarioOID
delete from ScenarioAccess where ScenarioAccess.ParentId = @aScenarioOID
delete from Scenario where id = @aScenarioOID
COMMIT TRANSACTION
END
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 427 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 427 is completed with errors' SET NOEXEC ON END
GO
--step 428: create procedure dbo.sp_DeleteProject---------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[sp_DeleteProject] (@id uniqueidentifier) AS
BEGIN
DECLARE @ProjectPartsCount int
DECLARE @ScenarioId uniqueidentifier
DECLARE @ProjectPartId uniqueidentifier
SELECT @ProjectPartsCount = COUNT(1) FROM Project WHERE ParentProjectId = @id
IF (@ProjectPartsCount > 0)
BEGIN
-- Delete project parts (if has any)
DECLARE PartsToDelete CURSOR FOR
SELECT Id from Project where ParentProjectId = @id
FOR READ ONLY
--PRINT 'Parts for project found: ' + STR(@ProjectPartsCount);
OPEN PartsToDelete;
FETCH NEXT FROM PartsToDelete INTO @ProjectPartId;
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'Deleting part ' + CAST(@ProjectPartId AS VARCHAR(50));
EXEC sp_DeleteProject @ProjectPartId
FETCH NEXT FROM PartsToDelete INTO @ProjectPartId;
END;
CLOSE PartsToDelete;
DEALLOCATE PartsToDelete;
--PRINT 'All parts deleted';
END
-- Delete scenarios
DECLARE ScenariosToDelete CURSOR FOR
SELECT Id from Scenario where ParentId = @id
FOR READ ONLY
--PRINT 'Deleting scenarios for ' + CAST(@id AS VARCHAR(50)) + '...';
OPEN ScenariosToDelete;
FETCH NEXT FROM ScenariosToDelete INTO @ScenarioId;
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'Deleting scenario ' + CAST(@ScenarioId AS VARCHAR(50));
EXEC sp_DeleteScenario @ScenarioId
FETCH NEXT FROM ScenariosToDelete INTO @ScenarioId;
END;
CLOSE ScenariosToDelete;
DEALLOCATE ScenariosToDelete;
--PRINT 'All scenarios deleted';
--PRINT 'Clearing tables fro project ' + CAST(@Id AS VARCHAR(50));
--PRINT 'Deleting from [StrategicGoal2Project]';
delete from StrategicGoal2Project where ProjectId =@Id
--PRINT 'Deleting from [Team2Project]';
delete from Team2Project where ProjectId =@Id
--PRINT 'Deleting from [Contact2Project]';
delete from Contact2Project where ShowId =@Id
--PRINT 'Deleting from [ProjectAccess]';
delete from ProjectAccess where ProjectId =@Id
--PRINT 'Deleting from [Attachments]';
delete from Attachments where ParentId = @id
--PRINT 'Deleting from [Project]';
delete from Project where id = @id
END
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 428 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 428 is completed with errors' SET NOEXEC ON END
GO
--step 429: create procedure dbo.sp_DeleteUser------------------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[sp_DeleteUser] (@Id uniqueidentifier) AS
BEGIN
BEGIN TRANSACTION
delete from PasswordResetRequest where UserId=@Id
delete from User2Team where UserId = @Id
delete from User2View where UserId = @Id
delete from UserPreferences where UserId = @Id
delete from AspNetUserClaims where [User_Id] = @Id
delete from AspNetUserLogins where [UserId] = @Id
delete from AspNetUserRoles where [UserId] = @Id
delete from AspNetUsers where id = @Id
COMMIT TRANSACTION
END
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 429 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 429 is completed with errors' SET NOEXEC ON END
GO
--step 430: create procedure dbo.sp_SetScenarioBottomUpCosts----------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[sp_SetScenarioBottomUpCosts] (@aScenarioOID uniqueidentifier) AS
DECLARE @lBottomUpCosts decimal(15,4)
DECLARE @lBottomUpCosts_LM decimal(15,4)
DECLARE @lActuals_BottomUpCosts decimal(15,4)
DECLARE @lActuals_BottomUpCosts_LM decimal(15,4)
DECLARE @lScenarioStartDate datetime
DECLARE @lScenarioEndDate datetime
DECLARE @lActualsStartDate datetime
DECLARE @lActualsEndDate datetime
DECLARE @lScenarioType int
DECLARE @lActualsId uniqueidentifier
DECLARE @lSCUnderShowID uniqueidentifier
DECLARE @lGrowthScenario int
DECLARE @lIsRevenueGenerating bit
BEGIN
SELECT @lScenarioType = Type, @lScenarioStartDate = StartDate, @lScenarioEndDate = EndDate, @lGrowthScenario = GrowthScenario
FROM Scenario
WHERE (Id = @aScenarioOID)
BEGIN TRANSACTION updatebottomupdirectcosts
SELECT @lIsRevenueGenerating = p.IsRevenueGenerating
FROM Scenario s inner join Project p on s.ParentId = p.Id
WHERE (s.Id = @aScenarioOID)
IF ((@lScenarioType = 2) OR (@lScenarioType = 3))
BEGIN
SELECT @lBottomUpCosts = SUM (Cost)
FROM ScenarioDetail
WHERE (ParentID = @aScenarioOID);
SELECT @lBottomUpCosts_LM = SUM (Cost)
FROM ScenarioDetail
WHERE (ParentID = @aScenarioOID)
AND (ExpenditureCategoryId IN
(SELECT Id FROM VW_Expenditure2Category
WHERE [Type] IN (1, 2)))
IF (@lGrowthScenario = 0)
BEGIN
/* calculate actuals buttom up cost which is actuals plus forecast */
/* retrieve (only) actuals for the same show, there should only be one */
SELECT @lActualsId = Id, @lActualsStartDate = StartDate, @lActualsEndDate = EndDate
FROM Scenario
WHERE (Type = 9)
AND (ParentId = (SELECT ParentId FROM Scenario
WHERE Id = @aScenarioOID))
/* sum up buttom up costs from actuals */
SELECT @lActuals_BottomUpCosts = 0
SELECT @lActuals_BottomUpCosts_LM = 0
SELECT @lActuals_BottomUpCosts = SUM (Cost)
FROM ScenarioDetail
WHERE ((ParentId = @lActualsId)
AND (WeekEndingDate >= @lActualsStartDate)
AND (WeekEndingDate <= @lActualsEndDate))
OR ((ParentId = @aScenarioOID)
AND (WeekEndingDate > @lActualsEndDate))
SELECT @lActuals_BottomUpCosts_LM = SUM (Cost)
FROM ScenarioDetail
WHERE ((ParentID = @lActualsId)
AND (WeekEndingDate >= @lActualsStartDate)
AND (WeekEndingDate <= @lActualsEndDate)
AND (ExpenditureCategoryId IN
(SELECT Id FROM VW_Expenditure2Category
WHERE Type IN (1, 2))))
OR ((ParentId = @aScenarioOID)
AND (WeekEndingDate > @lActualsEndDate)
AND (ExpenditureCategoryId IN
(SELECT Id FROM VW_Expenditure2Category
WHERE Type IN (1, 2))))
IF (@lIsRevenueGenerating = 1)
UPDATE Scenario
SET BUDirectCosts = @lBottomUpCosts, BURevenueShot = (@lBottomUpCosts / Shots),
Actuals_BUDirectCosts = @lActuals_BottomUpCosts,
CalculatedGrossMargin = (ProjectedRevenue - @lBottomUpCosts) / ProjectedRevenue,
BUDirectCosts_LM = @lBottomUpCosts_LM,
Actuals_BUDirectCosts_LM = @lActuals_BottomUpCosts_LM,
BURevenueShot_LM = (@lBottomUpCosts_LM / Shots),
CalculatedGrossMargin_LM = (ProjectedRevenue - @lBottomUpCosts_LM) / ProjectedRevenue
WHERE (Id = @aScenarioOID)
ELSE
UPDATE Scenario SET BUDirectCosts = @lBottomUpCosts, BURevenueShot = (@lBottomUpCosts / Shots),
Actuals_BUDirectCosts = @lActuals_BottomUpCosts,
CalculatedGrossMargin = 0,
BUDirectCosts_LM = @lBottomUpCosts_LM,
Actuals_BUDirectCosts_LM = @lActuals_BottomUpCosts_LM,
BURevenueShot_LM = (@lBottomUpCosts_LM / Shots),
CalculatedGrossMargin_LM = 0
WHERE (Id = @aScenarioOID)
END
ELSE
IF (@lIsRevenueGenerating = 1)
UPDATE Scenario
SET BUDirectCosts = @lBottomUpCosts, BURevenueShot = (@lBottomUpCosts / Shots),
CalculatedGrossMargin = (ProjectedRevenue - @lBottomUpCosts) / ProjectedRevenue,
BUDirectCosts_LM = @lBottomUpCosts_LM,
BURevenueShot_LM = (@lBottomUpCosts_LM / (Shots)),
CalculatedGrossMargin_LM = (ProjectedRevenue - @lBottomUpCosts_LM) / ProjectedRevenue
WHERE (Id = @aScenarioOID)
ELSE
UPDATE Scenario
SET BUDirectCosts = @lBottomUpCosts, BURevenueShot = (@lBottomUpCosts / Shots),
CalculatedGrossMargin = 0,
BUDirectCosts_LM = @lBottomUpCosts_LM,
BURevenueShot_LM = (@lBottomUpCosts_LM / Shots),
CalculatedGrossMargin_LM = 0
WHERE (Id = @aScenarioOID)
END
ELSE IF (@lScenarioType = 9)
BEGIN
/* recalculate actuals buttom up costs */
SELECT @lActuals_BottomUpCosts = 0
SELECT lActuals_BottomUpCosts_LM = 0
SELECT @lActuals_BottomUpCosts = SUM (Cost)
FROM ScenarioDetail
WHERE (ParentId = @aScenarioOID)
AND (WeekEndingDate <= @lScenarioEndDate)
SELECT @lActuals_BottomUpCosts_LM = SUM (Cost)
FROM ScenarioDetail
WHERE (ParentId = @aScenarioOID)
AND (WeekEndingDate <= @lScenarioEndDate)
AND (ExpenditureCategoryId IN
(SELECT Id FROM VW_Expenditure2Category
WHERE Type IN (1, 2)))
UPDATE Scenario
SET BUDirectCosts = @lActuals_BottomUpCosts,
Actuals_BUDirectCosts = @lActuals_BottomUpCosts,
BUDirectCosts_LM = @lActuals_BottomUpCosts_LM,
Actuals_BUDirectCosts_LM = @lActuals_BottomUpCosts_LM
WHERE (Id = @aScenarioOID)
/* recalculate buttom up costs of all portfolio, efc, cg scenarios under the same show as the actuals scenario. */
DECLARE scenarios_undershow CURSOR FOR
SELECT Id FROM Scenario
WHERE (ParentId = (SELECT ParentId FROM Scenario
WHERE Id = @aScenarioOID))
AND (Type IN (2, 3))
AND (GrowthScenario = 0)
OPEN scenarios_undershow
FETCH NEXT FROM scenarios_undershow INTO @lSCUnderShowID
WHILE (@@fetch_status <> -1)
BEGIN
SELECT @lBottomUpCosts = 0
SELECT @lBottomUpCosts_LM = 0
SELECT @lBottomUpCosts = SUM (Cost)
FROM ScenarioDetail
WHERE (ParentId = @lSCUnderShowID)
AND (WeekEndingDate > @lScenarioEndDate)
SELECT @lBottomUpCosts_LM = SUM (Cost)
FROM ScenarioDetail
WHERE (ParentId = @lSCUnderShowID)
AND (WeekEndingDate > @lScenarioEndDate)
AND (ExpenditureCategoryId IN
(SELECT Id FROM VW_Expenditure2Category
WHERE Type IN (1, 2)))
UPDATE Scenario
SET Actuals_BUDirectCosts = @lActuals_BottomUpCosts + @lBottomUpCosts,
Actuals_BUDirectCosts_LM = @lActuals_BottomUpCosts_LM + @lBottomUpCosts_lm
WHERE (Id = @lSCUnderShowID)
FETCH NEXT FROM scenarios_undershow INTO @lSCUnderShowID
END
DEALLOCATE scenarios_undershow
END
COMMIT TRANSACTION
END
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 430 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 430 is completed with errors' SET NOEXEC ON END
GO
--step 431: create function dbo.resourceAvailability_f----------------------------------------------
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[resourceAvailability_f] (@StartDate datetime, @EndDate datetime)
RETURNS @restable TABLE
(FirstName varchar(100),
LastName varchar(100),
ExpenditureCategory varchar(100),
AvailableHours float,
AllocatedHours float,
TrainingHours float,
VacationHours float,
ResourceAvailability float,
TeamName varchar(100)
)
AS
BEGIN
INSERT INTO @restable
select
vw.FirstName, vw.LastName, vw.Name as ExpenditureCategory,
sum(vw.AvailableHours) as AvailableHours, sum(vw.AllocatedHours) as AllocatedHours,
sum(vw.TrainingHours) as TrainingHours, sum(vw.VacationHours) as VacationHours,
sum(vw.AvailableHours)-sum(vw.AllocatedHours)-sum(vw.TrainingHours)-sum(vw.VacationHours) as ResourceAvailability,
vw.TeamName
From(
select
pr.FirstName,
pr.LastName,
ec2.Name,
t.Name as TeamName,
fc.EndDate,
((select UOM.UOMValue
from UOM, ExpenditureCategory ec
where UOM.Id=ec.UOMId and ec.Id=pr.ExpenditureCategoryId)
*((cast( ww.Monday as int) + cast(ww.Tuesday as int) + cast(ww.Wednesday as int) + cast(ww.Thursday as int) + cast(ww.Friday as int) + cast(ww.Saturday as int) + cast(ww.Sunday as int)) /5.0)
) as AvailableHours,
ISNULL((select SUM (pa.Quantity)
From PeopleResourceAllocation pa
inner join Scenario s on s.Id = pa.ScenarioId and s.Status = 1
Where pa.PeopleResourceId=pr.Id and pa.WeekEndingDate = fc.EndDate), 0) as AllocatedHours,
ISNULL((select SUM (pt.HoursOff)
From NonProjectTimeAllocation pt
Where pt.PeopleResourceId=pr.Id and pt.WeekEndingDate = fc.EndDate), 0) as TrainingHours,
0 as VacationHours
from FiscalCalendar fc
join PeopleResource pr on (not(fc.StartDate > pr.EndDate) and not(fc.EndDate < pr.StartDate))
join ExpenditureCategory ec2 on ec2.Id=pr.ExpenditureCategoryId
join WorkWeek ww on ww.Id = pr.WorkWeekId
join Team t on t.Id = pr.TeamId
where fc.StartDate >= @StartDate and fc.StartDate <= @EndDate and fc.Type = 0
) as vw
group by vw.FirstName, vw.LastName, vw.Name, vw.TeamName--, vw.EndDate
RETURN
END
GO
IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'step 431 is completed with errors' ROLLBACK TRAN END
GO
IF @@TRANCOUNT = 0 BEGIN PRINT 'step 431 is completed with errors' SET NOEXEC ON END
GO
----------------------------------------------------------------------
IF @@TRANCOUNT > 0 BEGIN COMMIT TRAN PRINT 'Synchronization is successfully completed.' END
GO
SET NOEXEC OFF
GO
Reference in New Issue View Git Blame Copy Permalink