CREATE PROCEDURE [dbo].[sp_GetPartsMasterCount](
	@PartNumber VARCHAR(500)
	--,@AlternatePartNumbers VARCHAR(500)
	,@ItemVetted VARCHAR(500) = ''
	,@Machine VARCHAR(500) = ''
	,@Category VARCHAR(500) = ''
	,@SubCategory VARCHAR(500) = ''
)
AS


SELECT	COUNT(1) AS [Count]
FROM	publ_PartsMaster PM WITH(INDEX(ix_PartsMaster_itemId))
		LEFT JOIN dbo.publ_PartsAlternates PA WITH(INDEX(ix_PartsAlternates_ParentSurfaceItemId))
			ON PA.ParentSurfaceItemId = PM.itemId AND PA.isActive = 1
		LEFT JOIN dbo.pal_SurfaceLookup SL
			ON SL.recId = PM.PartsMaster_PartsMasterDetails_PartsMasterItemVetted
		WHERE PM.isActive = 1 AND (@PartNumber = '' 
					OR (@PartNumber <> '' 
						AND PA.PartsAlternates_PartsAlternates_AlternatePartNumber like '%'+@PartNumber+'%'
						--AND (PM.PartsMaster_PartsMasterDetails_PartsMasterPartNumber like '%'+@PartNumber+'%'
						--	OR PA.PartsAlternates_PartsAlternates_AlternatePartNumber like '%'+@PartNumber+'%'
						--AND (PM.PartsMaster_PartsMasterDetails_PartsMasterPartNumber = @PartNumber
						--	OR PA.PartsAlternates_PartsAlternates_AlternatePartNumber = @PartNumber
						))
				AND (@ItemVetted = '' OR (@ItemVetted <> '' AND SL.display IN (SELECT * FROM dbo.SplitString(@ItemVetted,','))))
				AND (@Machine = '' OR (@Machine <> '' AND PM.PartsMaster_PartsMasterDetails_PartsMasterMachine IN (SELECT * FROM dbo.SplitString(@Machine,','))))
				AND (@Category = '' OR (@Category <> '' AND PM.PartsMaster_PartsMasterDetails_PartsCategoryName IN (SELECT * FROM dbo.SplitString(@Category,','))))
				AND (@SubCategory = '' OR (@SubCategory <> '' AND PM.PartsMaster_PartsMasterDetails_PartsSubCategoryName IN (SELECT * FROM dbo.SplitString(@SubCategory,','))))
OPTION (RECOMPILE)
