USE [envisage]
GO

/****** Object:  View [dbo].[VW_Scenario2Project]    Script Date: 31.07.2014 18:22:27 ******/
if exists (select 1 from sys.views where name like 'VW_Scenario2Project')
begin
	DROP VIEW [dbo].[VW_Scenario2Project]
end
GO

/****** Object:  View [dbo].[VW_Scenario2Project]    Script Date: 31.07.2014 18:22:27 ******/
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, 
	       SC.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
GO