CREATE PROCEDURE [dbo].[sp_GetPartsMasterData](
	@PartNumber VARCHAR(500)
	--,@AlternatePartNumbers VARCHAR(500)
	,@ItemVetted VARCHAR(500) = ''
	,@Machine VARCHAR(500) = ''
	,@Category VARCHAR(500) = ''
	,@SubCategory VARCHAR(500) = ''
	,@StartIndex INT = 1
	,@MaxRows INT = 1000
)
AS

--Testing
--DECLARE 	@PartNumber VARCHAR(500) = '0603706305' 
--			--,@AlternatePartNumbers VARCHAR(500) = '' 
--			,@ItemVetted VARCHAR(500) = ''
--			,@Machine VARCHAR(500) = ''
--			,@Category VARCHAR(500) = ''
--			,@SubCategory VARCHAR(500) = ''
--			,@StartIndex INT = 1
--			,@MaxRows INT = 10000


SELECT	*
FROM	(
		SELECT	PM.itemID
				,PM.isActive
				,PM.surfaceId
				,SL.display AS [PartsMaster_PartsMasterDetails_PartsMasterItemVetted]
				--,PM.PartsMaster_PartsMasterDetails_PartsMasterItemVetted
				,PM.PartsMaster_PartsMasterDetails_PartsMasterMachine
				,PM.PartsMaster_PartsMasterDetails_PartsCategoryName
				,PM.PartsMaster_PartsMasterDetails_PartsSubCategoryName
				,PM.PartsMaster_PartsMasterDetails_PartsMasterPartNumber
				,PM.PartsMaster_PartsMasterDetails_PartsMasterCEPNumber
				,PM.PartsMaster_PartsMasterDetails_PartsMasterDescription
				,PM.PartsMaster_PartsMasterDetails_PartsMasterAdditionalDescription
				,PM.InventoryManagement_InventoryManagement_InventoryTotalAvailable
				,PM.recId
				,0 AS [linkUser]
				,4 AS [userType]
				,CAST(0 as bit) AS [showWizardStatus]
				,CAST(1 as bit) AS [allowEdit]
				,3 AS [editAccessType]
				,CAST(0 as bit) AS [allowView]
				,4 AS [viewAccessType]
				,CAST(1 as bit) AS [allowRemove]
				,3 AS [removeAccessType]
				,ROW_NUMBER() OVER (ORDER BY PartsMaster_PartsMasterDetails_PartsMasterCEPNumber DESC) [RowRank] 
		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,','))))
		GROUP BY PM.itemID
				,PM.isActive
				,PM.surfaceId
				,SL.display
				--,PM.PartsMaster_PartsMasterDetails_PartsMasterItemVetted
				,PM.PartsMaster_PartsMasterDetails_PartsMasterMachine
				,PM.PartsMaster_PartsMasterDetails_PartsCategoryName
				,PM.PartsMaster_PartsMasterDetails_PartsSubCategoryName
				,PM.PartsMaster_PartsMasterDetails_PartsMasterPartNumber
				,PM.PartsMaster_PartsMasterDetails_PartsMasterCEPNumber
				,PM.PartsMaster_PartsMasterDetails_PartsMasterDescription
				,PM.PartsMaster_PartsMasterDetails_PartsMasterAdditionalDescription
				,PM.InventoryManagement_InventoryManagement_InventoryTotalAvailable
				,PM.recId
		) P
WHERE	[RowRank] >= CAST(@StartIndex AS VARCHAR(50)) 
		AND [RowRank] < CAST((@StartIndex + @MaxRows) AS VARCHAR(50))
OPTION(RECOMPILE)


