76 lines
4.8 KiB
Transact-SQL
76 lines
4.8 KiB
Transact-SQL
USE [EnVisage]
|
|
GO
|
|
|
|
/****** Object: View [dbo].[VW_ExpCategoriesInScenario] Script Date: 10.11.2014 19:25:31 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
ALTER VIEW [dbo].[VW_ExpCategoriesInScenario]
|
|
AS
|
|
SELECT dbo.ExpenditureCategory.Id, dbo.Expenditure.Name, dbo.ExpenditureCategory.Type AS ECType, dbo.Scenario.Type AS ScenarioType, dbo.Scenario.Id AS ScenarioID,
|
|
dbo.Scenario.Name AS ScenarioName, dbo.ExpenditureCategory.SortOrder, dbo.ExpenditureCategory.UseType, dbo.ExpenditureCategory.GLId
|
|
FROM dbo.Scenario INNER JOIN
|
|
dbo.ScenarioDetail ON dbo.Scenario.Id = dbo.ScenarioDetail.ParentID INNER JOIN
|
|
dbo.ExpenditureCategory ON dbo.ScenarioDetail.ExpenditureCategoryId = dbo.ExpenditureCategory.Id INNER JOIN
|
|
dbo.Expenditure ON dbo.ExpenditureCategory.ExpenditureId = dbo.Expenditure.Id
|
|
GROUP BY dbo.ExpenditureCategory.Id, dbo.Expenditure.Name, dbo.ExpenditureCategory.Type, dbo.Scenario.Type, dbo.Scenario.Id, dbo.Scenario.Name,
|
|
dbo.ExpenditureCategory.SortOrder, dbo.ExpenditureCategory.UseType, dbo.ExpenditureCategory.GLId
|
|
|
|
GO
|
|
|
|
|
|
ALTER VIEW [dbo].[VW_Expenditure2Calculation]
|
|
AS
|
|
SELECT dbo.Expenditure2Expenditure.Id, dbo.ExpenditureCategory.Id AS ExpenditureCategoryID, dbo.ExpenditureCategory.GLId, dbo.ExpenditureCategory.UOMId,
|
|
dbo.ExpenditureCategory.Type, dbo.ExpenditureCategory.UseType, dbo.ExpenditureCategory.CGEFX, dbo.ExpenditureCategory.SortOrder,
|
|
dbo.ExpenditureCategory.SystemAttributeOne, dbo.ExpenditureCategory.SystemAttributeTwo, dbo.Expenditure.Name AS ExpenditureName,
|
|
dbo.Expenditure2Expenditure.FactorType, dbo.Expenditure2Expenditure.FactorInt, dbo.Expenditure2Expenditure.ProcessOrder,
|
|
dbo.Expenditure2Expenditure.ParentId, dbo.ExpenditureCategory.CreditId, dbo.ExpenditureCategory.WksSubjectToFee
|
|
FROM dbo.Expenditure2Expenditure LEFT OUTER JOIN
|
|
dbo.Expenditure INNER JOIN
|
|
dbo.ExpenditureCategory ON dbo.Expenditure.Id = dbo.ExpenditureCategory.ExpenditureId ON dbo.Expenditure2Expenditure.ChildId = dbo.ExpenditureCategory.Id
|
|
|
|
GO
|
|
|
|
|
|
ALTER VIEW [dbo].[VW_Expenditure2Category]
|
|
AS
|
|
SELECT EC.Id, EC.ExpenditureId, EC.GLId, EC.UOMId, EC.Type, EC.UseType, EC.CGEFX, EC.SortOrder, LE.Name AS ExpenditureName, LG.GLNumber, LG.Name AS GLName,
|
|
LU.Name AS UOMName, EC.CreditId, EC.SystemAttributeOne, EC.SystemAttributeTwo, LC.Name AS CreditName, EC.WksSubjectToFee,
|
|
LS1.Name AS SystemAttributeNameOne, LS1.Type AS SystemAttributeTypeOne, LS2.Name AS SystemAttributeNameTwo, LS2.Type AS SystemAttributeTypeTwo
|
|
FROM dbo.ExpenditureCategory AS EC INNER JOIN
|
|
dbo.Expenditure AS LE ON EC.ExpenditureId = LE.Id LEFT OUTER JOIN
|
|
dbo.SystemAttributes AS LS1 ON EC.SystemAttributeOne = LS1.Id LEFT OUTER JOIN
|
|
dbo.SystemAttributes AS LS2 ON EC.SystemAttributeTwo = LS2.Id LEFT OUTER JOIN
|
|
dbo.CreditDepartment AS LC ON EC.CreditId = LC.Id LEFT OUTER JOIN
|
|
dbo.UOM AS LU ON EC.UOMId = LU.Id LEFT OUTER JOIN
|
|
dbo.GLDepartment AS LG ON EC.GLId = LG.Id
|
|
|
|
GO
|
|
|
|
|
|
ALTER VIEW [dbo].[VW_Expenditure2FeeCalculation]
|
|
AS
|
|
SELECT dbo.FeeCalculation.Id, dbo.FeeCalculation.ExpenditureCategoryId, dbo.FeeCalculation.MinShot, dbo.FeeCalculation.MaxShot, dbo.FeeCalculation.Quantity,
|
|
dbo.ExpenditureCategory.WksSubjectToFee
|
|
FROM dbo.FeeCalculation INNER JOIN
|
|
dbo.ExpenditureCategory ON dbo.FeeCalculation.ExpenditureCategoryId = dbo.ExpenditureCategory.Id
|
|
|
|
GO
|
|
|
|
|
|
ALTER VIEW [dbo].[VW_ScenarioAndProxyDetails]
|
|
AS
|
|
SELECT dbo.ScenarioDetail.Id, dbo.ScenarioDetail.ParentID, dbo.ScenarioDetail.ExpenditureCategoryId, dbo.ScenarioDetail.WeekEndingDate, dbo.ScenarioDetail.Quantity,
|
|
dbo.ScenarioDetail.LastUpdate, dbo.ScenarioDetail.WeekOrdinal, dbo.ScenarioDetail.Cost, dbo.Expenditure.Name AS ExpenditureCategoryName,
|
|
dbo.ExpenditureCategory.GLId, dbo.ExpenditureCategory.UOMId, dbo.ExpenditureCategory.CreditId, dbo.ExpenditureCategory.Type,
|
|
dbo.ExpenditureCategory.UseType, dbo.ExpenditureCategory.CGEFX, dbo.ExpenditureCategory.SystemAttributeOne, dbo.ExpenditureCategory.SystemAttributeTwo,
|
|
dbo.ExpenditureCategory.SortOrder
|
|
FROM dbo.ScenarioDetail INNER JOIN
|
|
dbo.ExpenditureCategory ON dbo.ExpenditureCategory.Id = dbo.ScenarioDetail.ExpenditureCategoryId INNER JOIN
|
|
dbo.Expenditure ON dbo.ExpenditureCategory.ExpenditureId = dbo.Expenditure.Id
|
|
|
|
GO |