USE [EnVisage]
GO

/****** Object:  View [dbo].[VW_Scenario2Project]    Script Date: 11/14/2014 3:03:13 PM ******/
DROP VIEW [dbo].[VW_Scenario2Project]
GO

/****** Object:  View [dbo].[VW_Scenario2Project]    Script Date: 11/14/2014 3:03:13 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

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.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