USE [Users_mdb]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[PurchaseLineItem]'))
DROP VIEW [dbo].[PurchaseLineItem]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[PurchaseLineItem]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[PurchaseLineItem]
AS
SELECT     A.nID, A.Line_Item_Number, A.Line_Item, A.Quantity, A.Line_Item_Description, A.Line_Item_Unit_Price, A.Line_Item_Total, A.Sub_Type, 
                      ISNULL(C.tBriefDescription, A.Line_Item_Description) + '' - Line #'' + CAST(A.Line_Item_Number AS nvarchar) AS tName, A.Start_Date, A.End_Date, A.Line_Item_Status, A.Expires_, A.Asset_Type, 
                      A.Purchased_For_Department, A.Purchased_For_Location, C.Company, C.PO_Number, C.Invoice_Date, C.Invoice_Number, C.tBriefDescription, C.Auto_Renew, 
                      C.Auto_Renewal_Date, C.Auto_Renewal_Interval, C.nSubstateID, C.nOriginatorID, C.SubTotal, C.Total_with_Tax, C.Account_Number, C.Requisition_Number, 
                      C.nID AS nPurchaseID
FROM         [Purchases&Agreements01_Dat].dbo.tblDts AS A LEFT OUTER JOIN
                      [Purchases&Agreements01_Dat].dbo.tblLineItem AS B ON A.nID = B.nRelID LEFT OUTER JOIN
                      [Purchases&Agreements01_Dat].dbo.tblDts AS C ON B.nID = C.nID
WHERE     (A.Type = 3)
' 
GO
