USE [IHBoxSystem]
GO
IF DATABASE_PRINCIPAL_ID('ihconfiguration_accountinformation_readWrite') IS NULL
BEGIN
	CREATE ROLE ihconfiguration_accountinformation_readWrite;
end
EXEC sp_addrolemember 'ihconfiguration_accountinformation_readWrite', 'admin'


EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [ihconfiguration_accountinformation_readWrite] ,@name = N'securedsp-CAT.GetMaterialSystems'	   ,@value = N'null'
EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [ihconfiguration_accountinformation_readWrite] ,@name = N'securedsp-CAT.UpdateMaterialsMaterialSystems'	   ,@value = N'null'
EXEC sp_addextendedproperty  @level0type = N'USER' ,@level0name = [ihconfiguration_accountinformation_readWrite] ,@name = N'securedsp-CAT.GetMaterialsMaterialSystemsSP'	   ,@value = N'null'

USE [MES]
GO


CREATE PROCEDURE [CAT].[GetMaterialSystems]
AS
BEGIN
	PRINT OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) 

	-----------------------
	-- Begin Query
	-----------------------
	SELECT 
		* 
	FROM CAT.MaterialSystems
	ORDER BY ID ASC
END


USE [MES]
GO
/****** Object:  StoredProcedure [CAT].[GetMaterialsMaterialSystemsSP]    Script Date: 21 may. 2020 18:34:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [CAT].[GetMaterialsMaterialSystemsSP]
	@id INT = NULL
AS
BEGIN
	PRINT OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) + ' Parameters:'
		+ '@id: ' + ISNULL(CAST(@id AS VARCHAR), '')



	-----------------------
	-- Begin Query
	-----------------------
	select Systems = CAT.GetMaterialsMaterialSystems(@id)
END
GO

/*---Last version:  6th july 2020 ----*/
USE [MES]
GO
/****** Object:  StoredProcedure [CAT].[GetMaterials]    Script Date: 6 jul. 2020 11:44:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [CAT].[GetMaterials]
AS
BEGIN
	PRINT OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)

	-----------------------
	-- Begin Query
	-----------------------
	SELECT
		cat.Id,
		cat.[Name],
		cat.[Description],
		cat.L1Code,
		cat.Active,
		Systems= CAT.GetMaterialsMaterialSystems(cat.Id),
		RowNum = ROW_NUMBER() OVER (ORDER BY cat.Name ASC)
	FROM CAT.Materials cat


END
