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