USE [EnVisage]
GO

ALTER VIEW [dbo].[VW_ProjectPerformance]
AS
SELECT
	A.ForecastScenarioId, A.ActualsScenarioId, A.ForecastScenarioType,
	A.ForecastTotalCost, A.ActualsTotalCost, A.VariationPercent,
	A.ForecastTotalQuantity, A.ActualsTotalQuantity, A.VariationQuantityPercent,
	B.Status, B.BUDirectCosts, B.EndDate AS ScenarioEndDate, 
	C.Id AS ProjectId, C.Name AS ProjectName, C.TypeId AS ProjectTypeId, C.Priority AS ProjectPriority,
	D.Name AS ProjectTypeName, 
	B.BUDirectCosts - A.ForecastTotalCost + A.ActualsTotalCost AS ActualsCostForecasted, 
	A.ActualsTotalCost - A.ForecastTotalCost AS ActualsCostVariation,
	A.ActualsTotalQuantity - A.ForecastTotalQuantity AS ActualsQuantityVariation,
	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