294 lines
21 KiB
Transact-SQL
294 lines
21 KiB
Transact-SQL
USE [EnVisage]
|
||
GO
|
||
|
||
/* Scenario table */
|
||
IF NOT EXISTS(SELECT * FROM sys.columns
|
||
WHERE Name = N'Version' AND Object_ID = Object_ID(N'Scenario'))
|
||
BEGIN
|
||
ALTER table [dbo].[Scenario] Add [Version] [timestamp] NOT NULL
|
||
end
|
||
go
|
||
|
||
IF NOT EXISTS(SELECT * FROM sys.columns
|
||
WHERE Name = N'CreatedAt' AND Object_ID = Object_ID(N'Scenario'))
|
||
BEGIN
|
||
ALTER table [dbo].[Scenario] Add [CreatedAt] [datetime] NULL default GetDate()
|
||
end
|
||
go
|
||
|
||
UPDATE [dbo].[Scenario] SET [CreatedAt] = GetDate() WHERE [CreatedAt] IS NULL
|
||
GO
|
||
|
||
IF EXISTS(SELECT * FROM sys.columns
|
||
WHERE Name = N'LastUpdate' AND Object_ID = Object_ID(N'Scenario'))
|
||
BEGIN
|
||
ALTER table [dbo].[Scenario] alter column [LastUpdate] [datetime] NULL
|
||
end
|
||
go
|
||
|
||
IF EXISTS(SELECT * FROM sys.columns
|
||
WHERE Name = N'EntryTimeStamp' AND Object_ID = Object_ID(N'Scenario'))
|
||
BEGIN
|
||
ALTER table [dbo].[Scenario] drop column [EntryTimeStamp]
|
||
end
|
||
go
|
||
|
||
/* Update ScenarioDetail indexes */
|
||
IF EXISTS(SELECT * FROM sys.indexes
|
||
WHERE name='NCLX_IDX_ScenarioDetail_ParentId_ExpCid_WeekendDate' AND
|
||
object_id = OBJECT_ID('ScenarioDetail'))
|
||
BEGIN
|
||
DROP INDEX [NCLX_IDX_ScenarioDetail_ParentId_ExpCid_WeekendDate] ON [dbo].[ScenarioDetail]
|
||
CREATE NONCLUSTERED INDEX [NCLX_IDX_ScenarioDetail_ParentId_ExpCid_WeekendDate]
|
||
ON [dbo].[ScenarioDetail] ([ParentID],[ExpenditureCategoryId],[WeekEndingDate])
|
||
INCLUDE ([Id],[Quantity],[WeekOrdinal],[Cost])
|
||
END
|
||
GO
|
||
|
||
IF EXISTS(SELECT * FROM sys.indexes
|
||
WHERE name='NCLX_IDX_ScenarioDetail_ParentId_ExpCid_WeekendDate_2' AND
|
||
object_id = OBJECT_ID('ScenarioDetail'))
|
||
BEGIN
|
||
DROP INDEX [NCLX_IDX_ScenarioDetail_ParentId_ExpCid_WeekendDate_2] ON [dbo].[ScenarioDetail]
|
||
CREATE NONCLUSTERED INDEX [NCLX_IDX_ScenarioDetail_ParentId_ExpCid_WeekendDate_2]
|
||
ON [dbo].[ScenarioDetail] ([WeekEndingDate],[ParentID],[ExpenditureCategoryId])
|
||
INCLUDE ([Id],[Quantity],[WeekOrdinal],[Cost])
|
||
END
|
||
GO
|
||
|
||
/* Remove LastUpdate columns */
|
||
IF EXISTS(SELECT * FROM sys.columns
|
||
WHERE Name = N'LastUpdate' AND Object_ID = Object_ID(N'ScenarioDetail'))
|
||
BEGIN
|
||
ALTER table [dbo].[ScenarioDetail] drop column [LastUpdate]
|
||
end
|
||
go
|
||
|
||
IF EXISTS(SELECT * FROM sys.columns
|
||
WHERE Name = N'LastUpdate' AND Object_ID = Object_ID(N'TeamAllocation'))
|
||
BEGIN
|
||
ALTER table [dbo].[TeamAllocation] drop column [LastUpdate]
|
||
end
|
||
go
|
||
|
||
IF EXISTS(SELECT * FROM sys.columns
|
||
WHERE Name = N'LastUpdate' AND Object_ID = Object_ID(N'PeopleResourceAllocation'))
|
||
BEGIN
|
||
ALTER table [dbo].[PeopleResourceAllocation] drop column [LastUpdate]
|
||
end
|
||
go
|
||
|
||
IF EXISTS(SELECT * FROM sys.columns
|
||
WHERE Name = N'LastUpdate' AND Object_ID = Object_ID(N'PeopleResourceVacation'))
|
||
BEGIN
|
||
ALTER table [dbo].[PeopleResourceVacation] drop column [LastUpdate]
|
||
end
|
||
go
|
||
|
||
/* Update Dependent views and procs */
|
||
ALTER 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
|
||
|
||
ALTER 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
|
||
|