IF EXISTS(SELECT 1 FROM sys.views WHERE Name = 'quotes_vQuotes')
DROP VIEW [dbo].[quotes_vQuotes]
GO

CREATE VIEW [dbo].[quotes_vQuotes]
AS
SELECT
	quote.recId,   
	quote.itemID, 
	quote.isActive, 
	quote.surfaceId, 

	quote.None_QuoteInformation_ContactName,
	quote.None_QuoteInformation_CustomerTradingName,
	quote.None_QuoteInformation_QuoteDate,
	quote.None_QuoteInformation_QuoteLastUpdated,
	quote.None_QuoteInformation_QuoteStatus,
	quote.None_QuoteInformation_QuoteUpdatedBy,

	quote.QuoteCustomerDetails_QuoteCustomerDetails_ContactNameCopy,
	quote.QuoteCustomerDetails_QuoteCustomerDetails_CustomerCode,
	quote.QuoteCustomerDetails_QuoteCustomerDetails_CustomerCurrency,
	quote.QuoteCustomerDetails_QuoteCustomerDetails_CustomerIsParent,
	quote.QuoteCustomerDetails_QuoteCustomerDetails_CustomerLegalEntity,
	quote.QuoteCustomerDetails_QuoteCustomerDetails_CustomerParentCompany,
	quote.QuoteCustomerDetails_QuoteCustomerDetails_CustomerPhysicalAddress,
	quote.QuoteCustomerDetails_QuoteCustomerDetails_CustomerPostalAddress,
	quote.QuoteCustomerDetails_QuoteCustomerDetails_CustomerTradingNameCopy,
	quote.QuoteCustomerDetails_QuoteCustomerDetails_CustomerType,
	quote.QuoteCustomerDetails_QuoteCustomerDetails_CustomerVATNumber,
	quote.QuoteCustomerDetails_QuoteCustomerDetails_CustomerVATRate,
	quote.QuoteCustomerDetails_QuoteCustomerDetails_QuoteContactEmail,
	quote.QuoteCustomerDetails_QuoteCustomerDetails_QuoteCustomerDefaultDiscount,
	quote.QuoteCustomerDetails_QuoteCustomerDetails_QuoteCustomerDefaultMargin,
	quote.QuoteCustomerDetails_QuoteCustomerDetails_QuoteMobileNumber,
	quote.QuoteCustomerDetails_QuoteCustomerDetails_QuoteTelephoneNumber,

	quote.QuoteEnquiryItems_QuoteEnquiryItems_QuoteRequestItemsUploadList, 
	
	quote.QuoteFinal_QuoteFinalItems_QuoteFinalItemsFinaliseQuote,
	quote.QuoteFinal_QuoteFinalItems_QuoteFinalItemsNotes,
	quote.QuoteFinal_QuoteFinalItems_QuoteFinalItemsRefresh,
	quote.QuoteFinal_QuoteFinalItems_QuoteFinalItemsSubtotalexclVAT,
	quote.QuoteFinal_QuoteFinalItems_QuoteFinalItemsTotalinclVAT,
	quote.QuoteFinal_QuoteFinalItems_QuoteFinalItemsTotalWeightKG,
	quote.QuoteFinal_QuoteFinalItems_QuoteFinalItemsVAT,

	quote.QuoteInformation_ContactName,
	quote.QuoteInformation_CustomerTradingName,
	quote.QuoteInformation_QuoteMonth,
	quote.QuoteInformation_QuoteNumber,

	quote.QuoteItems_QuoteItems_QuoteItemsDefaultMargin,
	quote.QuoteItems_QuoteItems_QuoteItemsDefaultMarginApplyToAll,
	quote.QuoteItems_QuoteItems_QuoteItemsGrossProfit,
	quote.QuoteItems_QuoteItems_QuoteItemsHideZeroQuantities,
	quote.QuoteItems_QuoteItems_QuoteItemsNotes,
	quote.QuoteItems_QuoteItems_QuoteItemsSubtotalexclVAT,
	quote.QuoteItems_QuoteItems_QuoteItemsSupplyTypes,
	quote.QuoteItems_QuoteItems_QuoteItemsTotalCost,
	quote.QuoteItems_QuoteItems_QuoteItemsTotalinclVAT,
	quote.QuoteItems_QuoteItems_QuoteItemsTotalMargin,
	quote.QuoteItems_QuoteItems_QuoteItemsTotalWeightKG,
	quote.QuoteItems_QuoteItems_QuoteItemsVAT,
	
	CreatedBy = item.createdBy, 
	CreatedByDisplay = createUser.userDisplay, 
	CreatedDate = item.dateCreated, 
	
	UpdatedBy = item.updatedBy, 
	UpdatedByDisplay = updateUser.userDisplay, 
	UpdatedDate = item.dateUpdated, 
	
	StatusDisplay = status.display

	,IsFinalized = CAST((CASE 
		WHEN (select COUNT(*) from publ_QuoteItemsFinal final where final.ParentSurfaceItemId = quote.itemID) > 0 THEN 1 
		ELSE 0 END) AS BIT)
FROM            dbo.publ_Quotes AS quote left outer JOIN
                         dbo.pal_SurfaceItem AS item ON quote.itemID = item.recId left outer JOIN
                         dbo.pal_vUserShared AS createUser ON item.createdBy = createUser.recId left outer JOIN
                         dbo.pal_vUserShared AS updateUser ON item.updatedBy = updateUser.recId left outer JOIN
                         dbo.pal_SurfaceLookup AS status ON quote.None_QuoteInformation_QuoteStatus = status.recId
WHERE        (quote.isActive = 1) AND (item.isDeleted = 0)
GO
