/*
Run this script on SQL Server 2008 or later. There may be flaws if running on earlier versions of SQL Server.
*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VW_Scenario2Project]') AND type in (N'V'))
DROP VIEW [dbo].[VW_Scenario2Project]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[VW_Scenario2Project]
AS
	SELECT distinct SC.Id,
	       SC.ParentId,
	       SC.TemplateId, 
	       SC.Type,
	       SC.Name, 
	       case when isnull(t.IsRevenueGenerating, 0) = 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 as GroupId
	  FROM Project SH RIGHT OUTER JOIN Scenario SC ON SH.Id = SC.ParentId
	  LEFT JOIN Scenario2Group s2g ON s2g.ScenarioId = SC.Id
	  JOIN [Type] t ON t.Id = SH.TypeId
GO