IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CAPI1_GetSupplierPricelistParts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_CAPI1_GetSupplierPricelistParts]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CAPI1_GetSupplierCATListParts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_CAPI1_GetSupplierCATListParts]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CAPI1_GetQuoteItemsSuppliers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_CAPI1_GetQuoteItemsSuppliers]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CAPI1_GetPartsAndAlternates]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_CAPI1_GetPartsAndAlternates]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CAPI1_GetInventoryItems]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_CAPI1_GetInventoryItems]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_CAPI1_SupplierCATListParts]'))
DROP VIEW [dbo].[vw_CAPI1_SupplierCATListParts]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_CAPI1_PartAlternates]'))
DROP VIEW [dbo].[vw_CAPI1_PartAlternates]
GO
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_CAPI1_PartMasters]'))
DROP VIEW [dbo].[vw_CAPI1_PartMasters]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE view [dbo].[vw_CAPI1_PartMasters]
as
SELECT [itemID]
      ,CEPNumber				= ISNULL(LTRIM(RTRIM(UPPER([PartsMaster_PartsMasterDetails_PartsMasterCEPNumber]))), '')
	  ,MasterNumber				= ISNULL(LTRIM(RTRIM(UPPER([PartsMaster_PartsMasterDetails_MasterNumber]))), '')
      ,PartNumber				= ISNULL(LTRIM(RTRIM(UPPER([PartsMaster_PartsMasterDetails_PartsMasterPartNumber]))), '')
	  ,AlternateNumber			= ''
      ,Description				= ISNULL([PartsMaster_PartsMasterDetails_PartsMasterDescription], '')
      ,AdditionalDescription	= ISNULL([PartsMaster_PartsMasterDetails_PartsMasterAdditionalDescription], '')
      ,DimensionalDescription	= ISNULL([PartsMaster_PartsMasterDetails_PartsMasterDimensionalDescription], '')
      ,WeightKG					= ISNULL([PartsMaster_PartsMasterDetails_PartsMasterWeightKG], 0)
      ,Category					= ISNULL([PartsMaster_PartsMasterDetails_PartsCategoryName], '')
      ,SubCategory				= ISNULL([PartsMaster_PartsMasterDetails_PartsSubCategoryName], '')
      ,MinorCode				= ISNULL([PartsMaster_PartsMasterDetails_PartsMinorCodeName], '')
      ,TotalAvailable			= ISNULL([InventoryManagement_InventoryManagement_InventoryTotalAvailable], 0)
  FROM [publ_PartsMaster] WITH (NOLOCK)
  WHERE isActive = 1
  AND [PartsMaster_PartsMasterDetails_PartsMasterPartNumber] != ''
  AND [PartsMaster_PartsMasterDetails_MasterNumber] != ''
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE view [dbo].[vw_CAPI1_PartAlternates]
as
SELECT 
	m.[itemID]
	,m.CEPNumber
	,m.MasterNumber
	,m.PartNumber
	,AlternateNumber = ISNULL(LTRIM(RTRIM(UPPER(a.[PartsAlternates_PartsAlternates_AlternatePartNumber]))), '')
	,m.Description
	,m.AdditionalDescription
	,m.DimensionalDescription
	,m.WeightKG
	,m.Category
	,m.SubCategory
	,m.MinorCode
	,m.TotalAvailable
FROM [publ_PartsAlternates] a  WITH (NOLOCK)
JOIN vw_CAPI1_PartMasters m  WITH (NOLOCK)
	ON ISNULL(LTRIM(RTRIM(UPPER(a.[PartsAlternates_PartsAlternates_PartsMasterPartNumber]))), '') = m.PartNumber
WHERE isActive = 1
	--AND a.[PartsAlternates_PartsAlternates_AlternatePartNumber] != m.PartNumber
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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] = ISNULL(LTRIM(RTRIM(UPPER([SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber]))), '')
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers] = ISNULL(LTRIM(RTRIM(UPPER([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

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_CAPI1_GetInventoryItems] 
	-- Add the parameters for the stored procedure here
	@partNumbers nvarchar(max)
	,@delimeter nvarchar(64) = '|'
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @pNums varchar(max)
	DECLARE @delim varchar(16)

	SET @pNums = @partNumbers
	SET @delim = @delimeter

    -- Insert statements for procedure here
	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(LTRIM(RTRIM(UPPER(mr.PartsMaster_PartsMasterDetails_MasterNumber))), '')
		,MinorCode				= ISNULL(mr.PartsMaster_PartsMasterDetails_PartsMinorCodeName, '')
		,PartNumber				= ISNULL(LTRIM(RTRIM(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
		AND (
			ISNULL(LTRIM(RTRIM(UPPER(mr.PartsMaster_PartsMasterDetails_MasterNumber))), '') IN (SELECT LTRIM(RTRIM(UPPER(splitdata))) splitdata FROM SplitString(@pNums, @delim) WHERE LTRIM(RTRIM(UPPER(splitdata))) != '')
			OR ISNULL(LTRIM(RTRIM(UPPER(mr.PartsMaster_PartsMasterDetails_PartsMasterPartNumber))), '') IN (SELECT LTRIM(RTRIM(UPPER(splitdata))) splitdata FROM SplitString(@pNums, @delim) WHERE LTRIM(RTRIM(UPPER(splitdata))) != '')
			)
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_CAPI1_GetPartsAndAlternates] (
	@partNumbers nvarchar(max)
	,@delimeter nvarchar(64)
)
AS
BEGIN
	DECLARE @pNums varchar(max)
	DECLARE @delim varchar(16)

	SET @pNums = @partNumbers
	SET @delim = @delimeter

	DECLARE @data TABLE (
		itemID int not null
		,CEPNumber varchar(64) not null
		,MasterNumber varchar(64) not null
		,PartNumber varchar(64) not null
		,AlternateNumber varchar(64) not null
		,Description varchar(3000) not null
		,AdditionalDescription varchar(8000) not null
		,DimensionalDescription varchar(1000) not null
		,WeightKG decimal not null
		,Category varchar(64) not null
		,SubCategory varchar(64) not null
		,MinorCode varchar(64) not null
		,TotalAvailable int not null
	)

	INSERT INTO @data 
	SELECT *
	FROM
	(
		SELECT *
		FROM vw_CAPI1_PartMasters 
		WHERE PartNumber IN (SELECT LTRIM(RTRIM(UPPER(splitdata))) splitdata FROM SplitString(@pNums, @delim) WHERE LTRIM(RTRIM(UPPER(splitdata))) != '')
		UNION ALL
		SELECT * FROM vw_CAPI1_PartAlternates
		WHERE PartNumber IN (SELECT LTRIM(RTRIM(UPPER(splitdata))) splitdata FROM SplitString(@pNums, @delim) WHERE LTRIM(RTRIM(UPPER(splitdata))) != '')
		UNION ALL
		SELECT * FROM vw_CAPI1_PartAlternates
		WHERE AlternateNumber IN (SELECT LTRIM(RTRIM(UPPER(splitdata))) splitdata FROM SplitString(@pNums, @delim) WHERE LTRIM(RTRIM(UPPER(splitdata))) != '')
	) t

	SELECT [itemID],[CEPNumber],[MasterNumber],[PartNumber],[AlternateNumber],[Description]
      ,[AdditionalDescription],[DimensionalDescription],[WeightKG],[Category],[SubCategory]
      ,[MinorCode],[TotalAvailable]
	FROM @data	
	GROUP BY [itemID],[CEPNumber],[MasterNumber],[PartNumber],[AlternateNumber],[Description]
      ,[AdditionalDescription],[DimensionalDescription],[WeightKG],[Category],[SubCategory]
      ,[MinorCode],[TotalAvailable]
	HAVING PartNumber != AlternateNumber
	ORDER BY MasterNumber, PartNumber, AlternateNumber

	RETURN
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_CAPI1_GetQuoteItemsSuppliers] 
	-- Add the parameters for the stored procedure here
	@quoteId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @qId int
	SET @qId = @quoteId

    -- Insert statements for procedure here
	SELECT 
		MAX(QuoteItems_QuoteItems_QuoteItemsSupplierCurrency) AS Currency
		,Supplier = QuoteItems_QuoteItems_QuoteItemsSupplier
	FROM publ_QuoteItems 
	WHERE isActive = 1 
		AND QuoteItems_QuoteItems_QuoteItemsSupplier <> 'CEP Inventory' 
		AND ParentSurfaceItemId = @qId
	GROUP BY QuoteItems_QuoteItems_QuoteItemsSupplier
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_CAPI1_GetSupplierCATListParts] 
	-- Add the parameters for the stored procedure here
	@partNumbers nvarchar(max)
	,@delimeter nvarchar(64)
AS
BEGIN
	DECLARE @pNums varchar(max)
	DECLARE @delim varchar(16)

	SET @pNums = @partNumbers
	SET @delim = @delimeter

	SELECT [itemID]
      ,[isActive]
      ,[surfaceId]
      ,[ParentSurfaceItemId]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLastUpdatedOn]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLastUpdatedBy]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPricelistStatus]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsExpiryDate]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime]
      ,[SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber] = LTRIM(RTRIM(UPPER([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
	AND LTRIM(RTRIM(UPPER([SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber])))
		IN (SELECT LTRIM(RTRIM(UPPER(splitdata))) splitdata FROM SplitString(@pNums, @delim) WHERE LTRIM(RTRIM(UPPER(splitdata))) != '')
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_CAPI1_GetSupplierPricelistParts] 
	-- Add the parameters for the stored procedure here
	@partNumbers nvarchar(max)
	,@delimeter nvarchar(64) = '|'
AS
BEGIN
	DECLARE @pNums varchar(max)
	DECLARE @delim varchar(16)

	SET @pNums = @partNumbers
	SET @delim = @delimeter

	SELECT 
        PartNumber, 
		AlternateNumber,
		SupplierName, 
        Brand, 
        Condition, 
        Currency, 
        LeadTime, 
        [Availability], 
        Note, 
		SalesPrice,
        WeightKG,
		[Description]
        FROM 
		( 
            SELECT 
			SupplierName	= ISNULL(p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName, ''),  
			PartNumber		= LTRIM(RTRIM(UPPER(ISNULL(p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber, '')))),
			AlternateNumber = LTRIM(RTRIM(UPPER(ISNULL(m.PartsAlternates_PartsAlternates_PartsMasterPartNumber, p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber)))), 
			Brand			= ISNULL(p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand, ''), 
			Condition		= ISNULL(p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition, ''), 
			Currency		= ISNULL(p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency, 'ZAR'), 
			LeadTime		= ISNULL(p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime, ''), 
			--AlternateNumbers = ISNULL(p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers, ''), 
			[Availability]	= ISNULL(p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAvailability, 0), 
			Note			= ISNULL(p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsNote, ''), 
			SalesPrice		= ISNULL(p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSalesPrice, 0),  
			WeightKG		= ISNULL(p.SupplierPricelistParts_SupplierPricelistParts_WeightKG, 0),
			[Description]	= ISNULL(p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsDescription, ''),
			PriceRank		= ROW_NUMBER() OVER (PARTITION BY  
				p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName,   
				ISNULL(m.PartsAlternates_PartsAlternates_PartsMasterPartNumber, p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber),  
				p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand,  
				p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition,  
				p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency,  
				p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime,  
				p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAlternateNumbers,  
				p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsNote,  
				p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAvailability   
				ORDER BY p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSalesPrice DESC)  
            FROM publ_SupplierPricelistParts p   
				LEFT OUTER JOIN publ_PartsAlternates m ON 
					m.PartsAlternates_PartsAlternates_AlternatePartNumber = p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber   
            WHERE p.isActive = 1 
				AND p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPricelistStatus = 'Current'  
				AND p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName IN ('CAT', 'SURPLUS WAREHOUSE')  
				AND p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber IN (SELECT LTRIM(RTRIM(UPPER(splitdata))) splitdata FROM SplitString(@pNums, @delim) WHERE LTRIM(RTRIM(UPPER(splitdata))) != '')
    ) t   
    WHERE t.PriceRank = 1  
    UNION  
    SELECT 
		PartNumber		= LTRIM(RTRIM(UPPER(ISNULL(SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber, '')))), 
		AlternateNumber = LTRIM(RTRIM(UPPER(ISNULL(p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber, '')))),
		SupplierName	= ISNULL(SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName, ''), 
		Brand			= ISNULL(SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand, ''), 
		Condition		= ISNULL(SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition, ''), 
		Currency		= ISNULL(SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency, 'ZAR'), 
		LeadTime		= ISNULL(SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsLeadTime, ''), 
		[Availability]	= ISNULL(SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAvailability, 0), 
		Note			= ISNULL(SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsNote, ''), 
		SalesPrice		= ISNULL(SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSalesPrice, 0),
		WeightKG		= ISNULL(p.SupplierPricelistParts_SupplierPricelistParts_WeightKG, 0),
		[Description]	= ISNULL(p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsDescription, '')
    FROM publ_SupplierPricelistParts p 
    WHERE p.isActive = 1
		AND p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber IN (SELECT LTRIM(RTRIM(UPPER(splitdata))) splitdata FROM SplitString(@pNums, @delim) WHERE LTRIM(RTRIM(UPPER(splitdata))) != '')
		AND p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPricelistStatus = 'Current' 
		AND p.SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName NOT IN ('CAT', 'SURPLUS WAREHOUSE') 
END
GO
