/*
Run this script on SQL Server 2008 or later. There may be flaws if running on earlier versions of SQL Server.
*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VW_Expenditure2Category]') AND type in (N'V'))
DROP VIEW [dbo].[VW_Expenditure2Category]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE 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.Expenditure_Category EC 
	INNER JOIN Expenditure LE ON EC.ExpenditureId = LE.Id 
	LEFT OUTER JOIN SystemAttributes LS1 ON EC.SystemAttributeOne = LS1.Id 
	LEFT OUTER JOIN SystemAttributes LS2 ON EC.SystemAttributeTwo = LS2.Id 
	LEFT OUTER JOIN CreditDepartment LC ON EC.CreditId = LC.Id 
	LEFT OUTER JOIN UOM LU ON EC.UOMId = LU.Id 
	LEFT OUTER JOIN GLDepartment LG ON EC.GLId = LG.Id
GO