116 lines
2.7 KiB
Transact-SQL
116 lines
2.7 KiB
Transact-SQL
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
|
|
|
|
|