USE [envisage]
GO

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

/****** Object:  View [dbo].[VW_Scenario2ProjectCln]    Script Date: 31.07.2014 18:23:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE VIEW [dbo].[VW_Scenario2ProjectCln]
AS
	SELECT 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.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
	  FROM Project SH, Scenario SC
	 WHERE (SC.Type IN (5, 6, 7, 11))
	   AND (SH.ID = SC.ParentID)
	UNION
	SELECT 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.ClientID, 
	       SH.TypeID,
	       SH.StatusID,
	       SC.Color,
	       CL.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,
	       CL.SystemAttributeObjectID
	  FROM Project SH, Scenario SC, ScenarioCln CL
	 WHERE (SH.ID = SC.ParentID)
	   AND (CL.ID = SC.ID)

GO