USE [EnVisage]
GO

/****** Object:  View [dbo].[VW_Scenario2Project]    Script Date: 26.02.2015 12:56:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER 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.EntryTimeStamp, SC.GrowthScenario, SC.Actuals_BUDirectCosts, SC.Actuals_BUDirectCosts_LM, SC.SystemAttributeObjectID, s2g.GroupId, 
                         SC.CostSavings
FROM            dbo.Project AS SH 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