------------------------------------------------------------------------------------------------------------
-- publ_InventoryItems drop indexes
------------------------------------------------------------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_InventoryItems]') AND name = N'idx_publ_InventoryItems_UniqueNumber')
DROP INDEX [idx_publ_InventoryItems_UniqueNumber] ON [dbo].[publ_InventoryItems]
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_InventoryItems]') AND name = N'idx_publ_InventoryItems_itemID')
DROP INDEX [idx_publ_InventoryItems_itemID] ON [dbo].[publ_InventoryItems]
GO
------------------------------------------------------------------------------------------------------------
-- publ_InventoryItems column lengths
------------------------------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryItems' 
	AND COLUMN_NAME = 'InventoryItemPartDetails_InventoryItemPartDetails_InventoryItem6MonthUsagedays')
BEGIN
	ALTER TABLE [publ_InventoryItems] 
		ADD InventoryItemPartDetails_InventoryItemPartDetails_InventoryItem6MonthUsagedays [int] NULL
END
GO
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryItems' 
	AND COLUMN_NAME = 'InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemSafetyStock')
BEGIN
	ALTER TABLE [publ_InventoryItems] 
		ADD InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemSafetyStock [decimal](18, 2) NULL
END
GO
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryItems' 
	AND COLUMN_NAME = 'InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemLeadTimedays')
BEGIN
	ALTER TABLE [publ_InventoryItems] 
		ADD InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemLeadTimedays [int] NULL
END
GO
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryItems' 
	AND COLUMN_NAME = 'InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemReviewCycledays')
BEGIN
	ALTER TABLE [publ_InventoryItems] 
		ADD InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemReviewCycledays [int] NULL
END
GO
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryItems' 
	AND COLUMN_NAME = 'InventoryItemPartDetails_InventoryItemPartDetails_Supplier')
BEGIN
	ALTER TABLE [publ_InventoryItems] 
		ADD InventoryItemPartDetails_InventoryItemPartDetails_Supplier [varchar](128) NULL
END
GO
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE 
	TABLE_NAME = 'publ_InventoryItems' 
	AND COLUMN_NAME = 'InventoryItemPartDetails_InventoryItemPartDetails_PartsMasterPartNumber' 
	AND CHARACTER_MAXIMUM_LENGTH = - 1)
BEGIN
	ALTER TABLE publ_InventoryItems 
		ALTER COLUMN InventoryItemPartDetails_InventoryItemPartDetails_PartsMasterPartNumber VARCHAR(32) NULL
END
GO
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE 
	TABLE_NAME = 'publ_InventoryItems' 
	AND COLUMN_NAME = 'InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemsBrand' 
	AND CHARACTER_MAXIMUM_LENGTH = - 1)
BEGIN
	ALTER TABLE publ_InventoryItems 
		ALTER COLUMN InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemsBrand VARCHAR(16) NULL
END
GO
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE 
	TABLE_NAME = 'publ_InventoryItems' 
	AND COLUMN_NAME = 'InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemsCondition' 
	AND CHARACTER_MAXIMUM_LENGTH = - 1)
BEGIN
	ALTER TABLE publ_InventoryItems 
		ALTER COLUMN InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemsCondition VARCHAR(16) NULL
END
GO
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE 
	TABLE_NAME = 'publ_InventoryItems' 
	AND COLUMN_NAME = 'InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemsDescription' 
	AND CHARACTER_MAXIMUM_LENGTH = - 1)
BEGIN
	ALTER TABLE publ_InventoryItems 
		ALTER COLUMN InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemsDescription VARCHAR(1000) NULL
END
GO
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE 
	TABLE_NAME = 'publ_InventoryItems' 
	AND COLUMN_NAME = 'InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemsUniqueNumber' 
	AND CHARACTER_MAXIMUM_LENGTH = - 1)
BEGIN
	ALTER TABLE publ_InventoryItems 
		ALTER COLUMN InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemsUniqueNumber VARCHAR(64) NULL
END
GO
------------------------------------------------------------------------------------------------------------
-- publ_InventoryItems add PartType
------------------------------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'publ_InventoryItems' 
	AND COLUMN_NAME = 'PartType')
BEGIN
	ALTER TABLE [publ_InventoryItems] 
		ADD PartType [varchar](16) NULL
END
GO
------------------------------------------------------------------------------------------------------------
-- publ_InventoryItems create indexes
------------------------------------------------------------------------------------------------------------
CREATE NONCLUSTERED INDEX [idx_publ_InventoryItems_itemID] ON [dbo].[publ_InventoryItems]
(
	[itemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_publ_InventoryItems_UniqueNumber] ON [dbo].[publ_InventoryItems]
(
	[InventoryItemPartDetails_InventoryItemPartDetails_InventoryItemsUniqueNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
------------------------------------------------------------------------------------------------------------
-- add publ_PartsMaster drop indexes
------------------------------------------------------------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_PartsMaster]') AND name = N'ix_PartsMaster_itemId')
DROP INDEX [ix_PartsMaster_itemId] ON [dbo].[publ_PartsMaster]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_PartsMaster]') AND name = N'idx_publ_PartsMaster_PartNumber')
DROP INDEX [idx_publ_PartsMaster_PartNumber] ON [dbo].[publ_PartsMaster]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_PartsMaster]') AND name = N'idx_publ_PartsMaster_MasterPartNumber')
DROP INDEX [idx_publ_PartsMaster_MasterPartNumber] ON [dbo].[publ_PartsMaster]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_PartsMaster]') AND name = N'idx_publ_PartsMaster_MasterNumber')
DROP INDEX [idx_publ_PartsMaster_MasterNumber] ON [dbo].[publ_PartsMaster]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_PartsMaster]') AND name = N'idx_publ_PartsMaster_CEPNumber')
DROP INDEX [idx_publ_PartsMaster_CEPNumber] ON [dbo].[publ_PartsMaster]
GO
------------------------------------------------------------------------------------------------------------
-- add publ_PartsMaster column lengths
------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE 
	TABLE_NAME = 'publ_PartsMaster' 
	AND COLUMN_NAME = 'PartsMaster_PartsMasterDetails_PartsMasterDescription' 
	AND CHARACTER_MAXIMUM_LENGTH = - 1)
BEGIN
	ALTER TABLE publ_PartsMaster 
		ALTER COLUMN PartsMaster_PartsMasterDetails_PartsMasterDescription VARCHAR(1000) NULL
END
GO
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE 
	TABLE_NAME = 'publ_PartsMaster' 
	AND COLUMN_NAME = 'PartsMaster_PartsMasterDetails_PartsMasterAdditionalDescription' 
	AND CHARACTER_MAXIMUM_LENGTH = - 1)
BEGIN
	ALTER TABLE publ_PartsMaster 
		ALTER COLUMN PartsMaster_PartsMasterDetails_PartsMasterAdditionalDescription VARCHAR(4000) NULL
END
GO
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE 
	TABLE_NAME = 'publ_PartsMaster' 
	AND COLUMN_NAME = 'PartsMaster_PartsMasterDetails_PartsMasterDimensionalDescription' 
	AND CHARACTER_MAXIMUM_LENGTH = - 1)
BEGIN
	ALTER TABLE publ_PartsMaster 
		ALTER COLUMN PartsMaster_PartsMasterDetails_PartsMasterDimensionalDescription VARCHAR(256) NULL
END
GO
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE 
	TABLE_NAME = 'publ_PartsMaster' 
	AND COLUMN_NAME = 'PartsMaster_PartsMasterDetails_PartsMasterClearingDescription' 
	AND CHARACTER_MAXIMUM_LENGTH = - 1)
BEGIN
	ALTER TABLE publ_PartsMaster 
		ALTER COLUMN PartsMaster_PartsMasterDetails_PartsMasterClearingDescription VARCHAR(512) NULL
END
GO
------------------------------------------------------------------------------------------------------------
-- add publ_PartsMaster create indexes
------------------------------------------------------------------------------------------------------------
CREATE NONCLUSTERED INDEX [idx_publ_PartsMaster_CEPNumber] ON [dbo].[publ_PartsMaster]
(
	[PartsMaster_PartsMasterDetails_PartsMasterCEPNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_publ_PartsMaster_MasterNumber] ON [dbo].[publ_PartsMaster]
(
	[PartsMaster_PartsMasterDetails_MasterNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_publ_PartsMaster_MasterPartNumber] ON [dbo].[publ_PartsMaster]
(
	[PartsMaster_PartsMasterDetails_PartsMasterPartNumber] ASC,
	[PartsMaster_PartsMasterDetails_MasterNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_publ_PartsMaster_PartNumber] ON [dbo].[publ_PartsMaster]
(
	[PartsMaster_PartsMasterDetails_PartsMasterPartNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_PartsMaster_itemId] ON [dbo].[publ_PartsMaster]
(
	[itemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
------------------------------------------------------------------------------------------------------------
-- qt_InventoryManagement.UniqueManagementNumber
------------------------------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE 
	TABLE_NAME = 'qt_InventoryManagement' 
	AND COLUMN_NAME = 'UniqueManagementNumber')
BEGIN
	ALTER TABLE qt_InventoryManagement 
		ADD [UniqueManagementNumber]  AS (([InventoryManagement_InventoryManagementLocation_InventoryManagementUniqueNumber]+'-')+[InventoryManagement_InventoryManagementLocation_InventoryManagementBinLocation])
END
GO
------------------------------------------------------------------------------------------------------------
-- publ_PartsAlternates drop indexes
------------------------------------------------------------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_PartsAlternates]') AND name = N'ix_PartsAlternates_ParentSurfaceItemId')
DROP INDEX [ix_PartsAlternates_ParentSurfaceItemId] ON [dbo].[publ_PartsAlternates]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_PartsAlternates]') AND name = N'idx_publ_PartsAlternates_MasterPartNumber')
DROP INDEX [idx_publ_PartsAlternates_MasterPartNumber] ON [dbo].[publ_PartsAlternates]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_PartsAlternates]') AND name = N'idx_publ_PartsAlternates_MasterAlternateNumber')
DROP INDEX [idx_publ_PartsAlternates_MasterAlternateNumber] ON [dbo].[publ_PartsAlternates]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_PartsAlternates]') AND name = N'idx_publ_PartsAlternates_itemID')
DROP INDEX [idx_publ_PartsAlternates_itemID] ON [dbo].[publ_PartsAlternates]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_PartsAlternates]') AND name = N'idx_publ_PartsAlternates_CEPNumber')
DROP INDEX [idx_publ_PartsAlternates_CEPNumber] ON [dbo].[publ_PartsAlternates]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[publ_PartsAlternates]') AND name = N'idx_publ_PartsAlternates_AlternatePartNumber')
DROP INDEX [idx_publ_PartsAlternates_AlternatePartNumber] ON [dbo].[publ_PartsAlternates]
GO
------------------------------------------------------------------------------------------------------------
-- publ_PartsAlternates create indexes
------------------------------------------------------------------------------------------------------------
CREATE NONCLUSTERED INDEX [idx_publ_PartsAlternates_AlternatePartNumber] ON [dbo].[publ_PartsAlternates]
(
	[PartsAlternates_PartsAlternates_AlternatePartNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_publ_PartsAlternates_CEPNumber] ON [dbo].[publ_PartsAlternates]
(
	[PartsAlternates_PartsAlternates_PartsMasterCEPNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_publ_PartsAlternates_itemID] ON [dbo].[publ_PartsAlternates]
(
	[itemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_publ_PartsAlternates_MasterAlternateNumber] ON [dbo].[publ_PartsAlternates]
(
	[PartsAlternates_PartsAlternates_PartsMasterPartNumber] ASC,
	[PartsAlternates_PartsAlternates_AlternatePartNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_publ_PartsAlternates_MasterPartNumber] ON [dbo].[publ_PartsAlternates]
(
	[PartsAlternates_PartsAlternates_PartsMasterPartNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_PartsAlternates_ParentSurfaceItemId] ON [dbo].[publ_PartsAlternates]
(
	[ParentSurfaceItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

------------------------------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CAPI1_GetAllPartsAndAlternates]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_CAPI1_GetAllPartsAndAlternates]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_CAPI1_Parts]'))
DROP VIEW [dbo].[vw_CAPI1_Parts]
GO

CREATE VIEW [dbo].[vw_CAPI1_Parts]
AS
SELECT
	ISNULl(ROW_NUMBER() OVER(ORDER BY m.itemID), 0) RowId
	,m.[itemID]
	,ISNULL(CAST(0 AS BIT), 0) IsAlternate
	,m.[PartsMaster_PartsMasterDetails_PartsMasterCEPNumber] CEPNumber
	,m.[PartsMaster_PartsMasterDetails_MasterNumber] MasterNumber
	,m.[PartsMaster_PartsMasterDetails_PartsMasterPartNumber] PartNumber
	,'' AlternateNumber
	,m.[PartsMaster_PartsMasterDetails_PartsMasterDescription] [Description]
	,ISNULL(m.[PartsMaster_PartsMasterDetails_PartsMasterAdditionalDescription], '') AdditionalDescription
	,ISNULL(m.[PartsMaster_PartsMasterDetails_PartsMasterDimensionalDescription], '') DimensionalDescription
	,ISNULL(m.[PartsMaster_PartsMasterDetails_PartsMasterWeightKG], 0) WeightKG
	,ISNULL(m.[PartsMaster_PartsMasterDetails_PartsCategoryName], '') CategoryName
	,ISNULL(m.[PartsMaster_PartsMasterDetails_PartsSubCategoryName], '') SubCategoryName
	,ISNULL(m.[PartsMaster_PartsMasterDetails_PartsMinorCodeName], '') MinorCodeName
	,ISNULL(m.[InventoryManagement_InventoryManagement_InventoryTotalAvailable], 0) TotalAvailable
	,ISNULL(CAST(m.[PartsMaster_PartsMasterDetails_PartsMasterItemVetted] AS BIT), 0) IsVetted
	,ISNULL(m.[PartsMaster_PartsMasterDetails_PartsMasterItemVettedBy], '') VettedBy
	,UniqueNumber = convert(varchar(16),m.[itemID])+'-'+m.[PartsMaster_PartsMasterDetails_PartsMasterCEPNumber]
FROM [publ_PartsMaster] m
WHERE m.PartsMaster_PartsMasterDetails_MasterNumber != ''
	AND m.PartsMaster_PartsMasterDetails_PartsMasterPartNumber != ''
	AND m.isActive = 1
UNION ALL
SELECT
	ISNULl(ROW_NUMBER() OVER(ORDER BY m.itemID), 0) RowId
	,m.[itemID]
	,ISNULL(CAST(1 AS BIT), 1) IsAlternate
    ,m.[PartsMaster_PartsMasterDetails_PartsMasterCEPNumber] CEPNumber
    ,m.[PartsMaster_PartsMasterDetails_PartsMasterPartNumber] MasterNumber
	,m.[PartsMaster_PartsMasterDetails_PartsMasterPartNumber] PartNumber
    ,a.[PartsAlternates_PartsAlternates_AlternatePartNumber] AlternateNumber
	,m.[PartsMaster_PartsMasterDetails_PartsMasterDescription] [Description]
	,ISNULL(m.[PartsMaster_PartsMasterDetails_PartsMasterAdditionalDescription], '') AdditionalDescription
	,ISNULL(m.[PartsMaster_PartsMasterDetails_PartsMasterDimensionalDescription], '') DimensionalDescription
	,ISNULL(m.[PartsMaster_PartsMasterDetails_PartsMasterWeightKG], 0) WeightKG
	,ISNULL(m.[PartsMaster_PartsMasterDetails_PartsCategoryName], '') CategoryName
	,ISNULL(m.[PartsMaster_PartsMasterDetails_PartsSubCategoryName], '') SubCategoryName
	,ISNULL(m.[PartsMaster_PartsMasterDetails_PartsMinorCodeName], '') MinorCodeName
	,ISNULL(m.[InventoryManagement_InventoryManagement_InventoryTotalAvailable], 0) TotalAvailable
	,ISNULL(CAST(m.[PartsMaster_PartsMasterDetails_PartsMasterItemVetted] AS BIT), 0) IsVetted
	,ISNULL(m.[PartsMaster_PartsMasterDetails_PartsMasterItemVettedBy], '') VettedBy
	,UniqueNumber = convert(varchar(16),m.[itemID])+'-'+m.[PartsMaster_PartsMasterDetails_PartsMasterCEPNumber]+'-'+a.[PartsAlternates_PartsAlternates_AlternatePartNumber]
FROM [publ_PartsAlternates] a
	JOIN [publ_PartsMaster] m on a.PartsAlternates_PartsAlternates_PartsMasterPartNumber = m.PartsMaster_PartsMasterDetails_PartsMasterPartNumber
	WHERE m.PartsMaster_PartsMasterDetails_MasterNumber != ''
	AND a.PartsAlternates_PartsAlternates_AlternatePartNumber != ''
	AND a.isActive = 1
	AND m.isActive = 1
	AND m.PartsMaster_PartsMasterDetails_PartsMasterPartNumber != a.PartsAlternates_PartsAlternates_AlternatePartNumber
GO

CREATE PROCEDURE [dbo].[sp_CAPI1_GetAllPartsAndAlternates] (
	@partNumbers nvarchar(max)
	,@delimeter nvarchar(64)
)
AS
BEGIN
	-- will hold the individual part numbers as rows
	DECLARE @partNums TABLE (
		partNumber varchar(64)
	)

	INSERT INTO @partNums (partNumber)
	SELECT ltrim(rtrim(splitdata)) splitdata FROM SplitString(@partNumbers, @delimeter)
		-- exclude empty strings
		WHERE splitdata != ''

	SELECT * 
		FROM vw_CAPI1_Parts t
		WHERE t.MasterNumber IN (SELECT partNumber FROM @partNums)
	UNION ALL
	(
		SELECT m.*
		  FROM vw_CAPI1_Parts a
			JOIN vw_CAPI1_Parts m 
				ON a.PartNumber = m.PartNumber
			WHERE a.AlternateNumber IN (SELECT partNumber FROM @partNums)
	)
END
GO

------------------------------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_CAPI1_SupplierCATListParts]'))
DROP VIEW [dbo].[vw_CAPI1_SupplierCATListParts]
GO

CREATE VIEW [dbo].[vw_CAPI1_SupplierCATListParts]
AS
SELECT [itemID]
      ,[isActive]
      ,[surfaceId]
      ,[ParentSurfaceItemId]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLastUpdatedOn]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLastUpdatedBy]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPricelistStatus]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsExpiryDate]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsDescription]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAdditionalDescription]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition]
      ,[SupplierPricelistParts_SupplierPricelistParts_WeightKG]
      ,[SupplierPricelistParts_SupplierPricelistParts_WeightLBS]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSalesPrice]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAvailability]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsNote]
      ,[recId]
  FROM [publ_SupplierPricelistParts]
  Where 
	isActive = 1
	and 'Current' = SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPricelistStatus
	and 'CAT' = SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName

