-- To get comments and questions about this script contact SA
USE [EnVisage]
GO

ALTER VIEW [dbo].[VW_ExpCategoriesInScenario]
AS
SELECT        dbo.ExpenditureCategory.Id, dbo.ExpenditureCategory.Name as ExpenditureCategoryName, dbo.Expenditure.Name AS ExpenditureName, 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.ExpenditureCategory.Name, 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.Name AS ExpenditureCategoryName, 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.Name as ExpenditureCategoryName,  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.GLAccount AS LG ON EC.GLId = LG.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.ExpenditureCategory.Name as ExpenditureCategoryName, dbo.Expenditure.Name AS ExpenditureName, 
                         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