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

CREATE VIEW [dbo].[quotes_vQuoteItems]
AS
SELECT        
	quoteItem.itemID, 
	quoteItem.isActive, 
	quoteItem.surfaceId, 
	quoteItem.ParentSurfaceItemId, 
	quoteItem.QuoteItems_QuoteItems_QuoteItemsSequenceNumber, 
	quoteItem.QuoteItems_QuoteItems_InventoryItemsUniqueNumber, 
    quoteItem.QuoteItems_QuoteItems_PartsMasterPartNumber, 
	quoteItem.QuoteItems_QuoteItems_QuoteItemsPartNumber, 
	quoteItem.QuoteItems_QuoteItems_QuoteItemsDescription, 
    quoteItem.QuoteItems_QuoteItems_QuoteItemsBrand, 
	quoteItem.QuoteItems_QuoteItems_QuoteItemsCondition, 
	quoteItem.QuoteItems_QuoteItems_QuoteItemsSupplier, 
	quoteItem.QuoteItems_QuoteItems_QuoteItemsLeadTime, 
    quoteItem.QuoteItems_QuoteItems_QuoteItemsWeightKG, 
	quoteItem.QuoteItems_QuoteItems_QuoteItemsCostPrice, 
	quoteItem.QuoteItems_QuoteItems_QuoteItemsSellingPrice, 
    quoteItem.QuoteItems_QuoteItems_QuoteItemsMargin, 
	quoteItem.QuoteItems_QuoteItems_QuoteItemsCategory, 
	quoteItem.QuoteItems_QuoteItems_QuoteItemsQuantityAvailable, 
    quoteItem.QuoteItems_QuoteItems_QuoteItemsSubcategory, 
	quoteItem.QuoteItems_QuoteItems_QuoteItemsQuantitySelected, 
	quoteItem.QuoteItems_QuoteItems_QuoteItemsWarehouseLocation, 
    quoteItem.QuoteItems_QuoteItems_QuoteItemsMinorCode, 
	quoteItem.QuoteItems_QuoteItems_QuoteItemsBinLocation, 
	quoteItem.recId, 
	quoteItem.QuoteItems_QuoteItems_QuoteItemsSupplierSequenceNumber, 
    quoteItem.QuoteItems_QuoteItems_QuoteItemsSupplierCurrency, 
	IsCatList = CAST((CASE WHEN quoteItem.QuoteItems_QuoteItems_QuoteItemsSupplier = 'CAT' THEN 1 ELSE 0 END) AS BIT),
	CreatedBy = item.createdBy,
	CreatedDate = item.dateCreated,
	UpdatedDate = item.dateUpdated,
	UpdatedBy = item.updatedBy,
	IsPriceChecked = CAST((CASE WHEN (priceCheck.OldSellPrice IS NULL) THEN 0 ELSE 1 END) AS BIT),
	PriceCheck_Date = priceCheck.CheckDate, 
	PriceCheck_QuoteNumber = priceCheck.QuoteNumber, 
	PriceCheck_QuoteDate = priceCheck.QuoteDate,
	PriceCheck_OldSellPrice = priceCheck.OldSellPrice, 
    PriceCheck_NewSellPrice = priceCheck.NewSellPrice, 
	PriceCheck_ReferenceQuoteItemId = priceCheck.ReferenceQuoteItemId,
	CatListCostPrice = priceCheck.CatListCostPrice
FROM            
	dbo.publ_QuoteItems AS quoteItem LEFT OUTER JOIN
	dbo.pal_SurfaceItem AS item ON quoteItem.itemID = item.recId LEFT OUTER JOIN
	dbo.quotes_QuoteItemsPriceCheck AS priceCheck ON quoteItem.recId = priceCheck.QuoteItemId
WHERE        
	(quoteItem.isActive = 1)
GO
