72 lines
1.8 KiB
Transact-SQL
72 lines
1.8 KiB
Transact-SQL
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,
|
|
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
|
|
|
|
|