
--	ADD publ_QuoteItems.IsCATList

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_QuoteItems' AND COLUMN_NAME = 'IsCATList')
BEGIN
	ALTER TABLE [publ_QuoteItems]
		ADD [IsCatList]  AS (CONVERT([bit],case when [QuoteItems_QuoteItems_QuoteItemsSupplier]='CAT' then (1) else (0) end))
END
GO

-- DROP quotes_vInventoryToQuoteItem

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

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

-- DROP quotes_vQuoteItems

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

-- CREATE quotes_vInventoryToQuoteItem

CREATE VIEW [dbo].[vw_CAPI1_InventoryToQuoteItems]
AS
SELECT
	MasterId				= mr.itemID
	,InventoryId			= i.itemID
	,ManagementId			= ISNULL(m.itemID, 0)
	,AdditionalDescription	= ISNULL(mr.PartsMaster_PartsMasterDetails_PartsMasterAdditionalDescription, '')
	,Available				= ISNULL(CONVERT(int, m.InventoryManagement_InventoryManagementStockDetails_InventoryManagementAvailable), 0)
	,AverageCost			= ISNULL(i.InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemAverageCost, 0)
	,Bin					= ISNULL(m.InventoryManagement_InventoryManagementLocation_InventoryManagementBin, '')
	,BinLocation			= ISNULL(m.InventoryManagement_InventoryManagementLocation_InventoryManagementBinLocation, '')
	,Brand					= ISNULL(i.InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemsBrand, '')
	,Category				= ISNULL(mr.PartsMaster_PartsMasterDetails_PartsCategoryName, '')
	,CEPNumber				= ISNULL(mr.PartsMaster_PartsMasterDetails_PartsMasterCEPNumber, '')
	,Condition				= ISNULL(i.InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemsCondition, '')
	,[Description]			= ISNULL(mr.PartsMaster_PartsMasterDetails_PartsMasterDescription, '')
	,MasterNumber			= ISNULL(UPPER(mr.PartsMaster_PartsMasterDetails_MasterNumber), '')
	,MinorCode				= ISNULL(mr.PartsMaster_PartsMasterDetails_PartsMinorCodeName, '')
	,PartNumber				= ISNULL(UPPER(mr.PartsMaster_PartsMasterDetails_PartsMasterPartNumber), '')
	,SubCategory			= ISNULL(mr.PartsMaster_PartsMasterDetails_PartsSubCategoryName, '')
	,UniqueNumber			= ISNULL(UPPER(i.InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemsUniqueNumber), '')
	,WarehouseLocation		= ISNULL(m.InventoryManagement_InventoryManagementLocation_WarehouseLocationName, '')
	,WeightKG				= ISNULL(mr.PartsMaster_PartsMasterDetails_PartsMasterWeightKG, 0)
FROM publ_PartsMaster mr 
	JOIN publ_InventoryItems i ON mr.itemID = i.ParentSurfaceItemId
	LEFT OUTER JOIN qt_InventoryManagement m ON i.itemID = m.ParentSurfaceItemId 
	and i.InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemsUniqueNumber = m.InventoryManagement_InventoryManagementLocation_InventoryManagementUniqueNumber
WHERE mr.isActive = 1 AND i.isActive = 1 AND ISNULL(m.isActive, 1) = 1
	and i.InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemTotalAvailable > 0
GO

-- CREATE quotes_vQuoteItems

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, 
	quoteItem.IsCatList,
	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
