Bulk Loading the Core (formerly Courion) Access Catalog (8.5.4) from a Custom Database Table

This is a custom process designed to load the Core Access (formerly Access Request Manager (ARM)) Catalog using a database table. This table can be populated using an SQL query or  a spreadsheet.

Both Roles (aka Groupings) and Entitlements can be loaded with this solution. Please note that this process is designed to load new records into the access catalog and not designed to perform updates.

AAS 8.5.4 and previous versions of the product offering do not have a standard Out of the Box feature for bulk Loading Roles (aka Groupings) and this article is meant to assist you in accomplishing this task.

Compatibility

Built and tested against Core Access Assurance Suite 8.5 Update 4 (AAS 8.5.4) but with minimal effort it can be adapted to work with previous versions of the product.

Database Objects

● Table – CoreAccessCatalogStagingTable
● Function –  CoreAccessCatalogGetProfileUID
● Function –  CoreAccessCatalogSplitStringList
● Stored Procedure – CoreAccessCatalogBulkLoad

Prerequisite

● Lay down the database schema provided below to create the necessary objects.
● The table CoreAccessCatalogStagingTable must be properly populated (see sample script below).
● The Core Access Profile table must contain the email address of users who are designated as Owners and Approvers of items within the CoreAccessCatalogStagingTable.

Process

Once the CoreAccessCatalogStagingTable database table is properly populated, execute the stored procedure(SP) CoreAccessCatalogBulkLoad. While the SP is executing,  Entitlements are loaded into the Catalog first and then associated Roles/Groupings if they exist.

For Approvers and Owners of Roles/Groupings, their ProfileUID are retrieved using the CoreAccessCatalogGetProfileUID function. This function accepts an email address value and returns associated ProfileUID from the Profile table.

When the SP has executed successfully the Access Catalog will be populated with the Access items from the CoreAccessCatalogStagingTable.

Documented along with this process are 5 examples of how to properly populate the CoreAccessCatalogStagingTable with Roles/Grouping and associated Entitlements. This will help you understand how the different ControlTypes can be used and also how the a list of Approvers and Tags can be loaded into the system.

Database Schema
CREATE TABLE [dbo].[CoreAccessCatalogStagingTable](
	[SId] [bigint] IDENTITY(1,1) NOT NULL,
	[Category] [nvarchar](250) NULL,
	[CategoryDescription] [nvarchar](1000) NULL,
	[Grouping] [nvarchar](250) NULL,
	[GroupingDescription] [nvarchar](1000) NULL,
	[GroupingOwner] [nvarchar](250) NULL,
	[GroupingDefinitionApprovers] [nvarchar](max) NULL,
	[GroupingApprovers] [nvarchar](max) NULL,
	[GroupingTags] [nvarchar](max) NULL,
	[EntitlementName] [nvarchar](4000) NULL,
	[EntitlementDescription] [nvarchar](max) NULL,
	[EntitlementAttributeName] [nvarchar](4000) NULL,
	[EntitlementAttributeValue] [nvarchar](4000) NULL,
	[EntitlementTags] [nvarchar](max) NULL,
	[TargetID] [nvarchar](255) NULL,
	[isEditable] [bit] NULL,
	[Automated] [bit] NULL,
	[UserVisible] [bit] NULL,
	[Required] [bit] NULL,
	[ControlType] [nvarchar](15) NULL,
	[ControlLabel] [nvarchar](50) NULL,
	[ControlValues] [nvarchar](max) NULL
) ON [PRIMARY]

CREATE FUNCTION [dbo].[CoreAccessCatalogSplitStringList]
(@objectGUID varchar(50),@delimiter	varchar(8),	@list varchar(8000)) RETURNS TABLE 
AS    
	RETURN
	SELECT	@objectGUID AS ObjectGUID,SUBSTRING( List, N, CHARINDEX( @delimiter, List + @delimiter, N ) - N ) AS Item
	FROM
	(
	SELECT @list AS List
	) AS TMP
	INNER JOIN dbo.Nums WITH(NOLOCK) ON N <= LEN( List )AND SUBSTRING( @delimiter + List, N, 1 ) = @delimiter;
GO

CREATE FUNCTION [dbo].[CoreAccessCatalogGetProfileUID] 
(@Email nvarchar(200))
RETURNS nvarchar(4000)
AS
BEGIN
	RETURN (SELECT Top 1 CASE WHEN  COUNT(*)>0 THEN MAX(ProfileUID) ELSE 'DefaultProfileUID' END FROM dbo.[Profile] WHERE Email=RTRIM(LTRIM(@Email)))
END

ALTER PROCEDURE [dbo].[CoreAccessCatalogBulkLoad]
AS
BEGIN
SET NOCOUNT ON

DECLARE @TotalEntitlement Bigint = 0
DECLARE @TotalGrouping Bigint = 0

DECLARE @DefaultOwner nvarchar(50)='DefaultProfileUID'
DECLARE @Category nvarchar(250)
DECLARE @CategoryDescription nvarchar(1000)
DECLARE @Grouping nvarchar(250)
DECLARE @GroupingDescription nvarchar(1000)
DECLARE @GroupingOwner nvarchar(250)
DECLARE @GroupingDefinitionApprovers nvarchar(max)
DECLARE @GroupingApprovers nvarchar(max)
DECLARE @GroupingTags nvarchar(max)
DECLARE @EntitlementName nvarchar(4000)
DECLARE @EntitlementDescription nvarchar(max)
DECLARE @EntitlementAttributeName nvarchar(4000)
DECLARE @EntitlementAttributeValue nvarchar(4000)
DECLARE @EntitlementTags nvarchar(max)
DECLARE @TargetID nvarchar(250)
DECLARE @ZTargetIDPk int
DECLARE @AccessType_Id nvarchar(150)
DECLARE @AccessType_Key bigint
DECLARE @Role_Id nvarchar(150)
DECLARE @Role_Key bigint
DECLARE @Tag_Id nvarchar(150)
DECLARE @TagName nvarchar(50)
DECLARE @TagOwner nvarchar(50) = @DefaultOwner
DECLARE @Tag_Key bigint

DECLARE @Entitlement_Id nvarchar(150)
DECLARE @entitlement_key bigint
DECLARE @EntitlementConfiguration_key bigint
DECLARE @isEnabled bit = 1
DECLARE @isActive bit = 1
DECLARE @State bit = 1
DECLARE @isTemplate bit = 0
DECLARE @isApproved bit = 1
DECLARE @isAssignable bit = 1
DECLARE @isInheritable bit = 0
DECLARE @IsUserCreated bit = 0
DECLARE @isEditable Bit = 0
DECLARE @Automated Bit = 1
DECLARE @isSystemType bit = 0
DECLARE @UserVisible Bit = 1
DECLARE @SingleValueAttribute Bit = 1
DECLARE @CustomControl Bit = 0
DECLARE @Required Bit
DECLARE @Provisioner nvarchar(500)= ''
DECLARE @ProvisionerEmail nvarchar(250)= ''
DECLARE @ClosedLoop Bit = 0
DECLARE @changed_on datetime = GETDATE()
DECLARE @ControlType  nvarchar(15)
DECLARE @ControlLabel  nvarchar(50)
DECLARE @HelpText nvarchar(500)= ''
DECLARE @ControlValues  nvarchar(max)
DECLARE @changed_by nvarchar(150) = 'CoreAccessBulkLoadProcess'
DECLARE @ProfileUID nvarchar(50)

--This makes sure that a Default ProfileUID exist
IF NOT EXISTS(SELECT 1 FROM Profile WHERE ProfileUID =@DefaultOwner)
	BEGIN
		INSERT Profile (ProfileUID,FirstName,Lastname,Active,[Status]) VALUES (@DefaultOwner,'Default','ProfileUID',0,dbo.GetPickListID('Inactive'))
	END
	
CREATE  TABLE #DefinitionApprovers(SId int Identity(1,1),ProfileUID nvarchar(50) NULL,Role_Key BigInt NULL)
DECLARE @DefinitionApproversCount int
DECLARE @DefinitionApproversCounter int

CREATE  TABLE #Approvers(SId int Identity(1,1),ProfileUID nvarchar(50) NULL,Role_Key BigInt NULL)
DECLARE @ApproversCount int
DECLARE @ApproversCounter int

CREATE  TABLE #Tag(SId int Identity(1,1),Name nvarchar(50) NULL, Description nvarchar(250) NULL)
DECLARE @TagCount int
DECLARE @TagCounter int

CREATE  TABLE #ControlValues(SId int Identity(1,1),item nvarchar(50) NULL)
DECLARE @ControlValuesCount int
DECLARE @ControlValuesCounter int      

DECLARE @Counter int
DECLARE @TotalRecords int

SELECT  @TotalRecords=COUNT(*) 
FROM CoreAccessCatalogStagingTable

SET @Counter=1
WHILE @Counter <= @TotalRecords 
	BEGIN
		SELECT 
		@Category=Category,
		@CategoryDescription=CategoryDescription,
		@Grouping=[Grouping],
		@GroupingDescription=GroupingDescription,
		@GroupingOwner=GroupingOwner,
		@GroupingDefinitionApprovers=GroupingDefinitionApprovers,
		@GroupingApprovers=GroupingApprovers,
		@GroupingTags=GroupingTags,
		@EntitlementName=EntitlementName,
		@EntitlementDescription=EntitlementDescription,
		@EntitlementAttributeName=EntitlementAttributeName,
		@EntitlementAttributeValue=ISNULL(EntitlementAttributeValue,''),
		@EntitlementTags=EntitlementTags,
		@TargetID=TargetID,
		@isEditable=isEditable,
		@Automated=Automated,
		@UserVisible=UserVisible,
		@isAssignable=UserVisible,
		@Required=[Required],
		@ControlType=ControlType,
		@ControlLabel=ControlLabel,
		@ControlValues=ISNULL(ControlValues,'')
		FROM CoreAccessCatalogStagingTable  
		WHERE SId=@Counter 
					--Start processing entry only when EntitlementName is present
			       IF (LEN(@EntitlementName)>0 AND @EntitlementName IS NOT NULL)
					  BEGIN
					  --Continue to process only if Entitlement is unique to a Target
							IF NOT EXISTS(SELECT 1 FROM Entitlement E INNER JOIN Entitlement_Mapping_Target T on E.entitlement_key = T.entitlement_key INNER JOIN Mapping_Target M on M.ZTargetIDPk=T.ZTargetIDPk  INNER JOIN EntitlementConfiguration C on C.EntitlementConfiguration_key = E.DefaultEntitlementConfiguration_key WHERE E.BusinessName=@EntitlementName AND M.TargetID =@TargetID AND E.Name = @EntitlementAttributeName AND C.Value=@EntitlementAttributeValue)
								  BEGIN
								  --Determine Entitlement Configuration
									 IF (@ControlType IN ('RadioButton','DropDown'))
										BEGIN
										--Identify Control Values
										Truncate Table #ControlValues									

										INSERT #ControlValues(item) 
										SELECT Item from CoreAccessCatalogSplitStringList('placeholder',';',@ControlValues) 
										WHERE LEN(Item)>0

										SET @ControlValues=''

										SELECT @ControlValuesCount=Count(*) FROM #ControlValues
											IF (@ControlValuesCount>0)
												BEGIN
												SET @ControlValuesCounter = 1
													WHILE @ControlValuesCounter <=@ControlValuesCount
														BEGIN
															SELECT @ControlValues= CASE WHEN @ControlValuesCounter = @ControlValuesCount THEN @ControlValues  + item ELSE @ControlValues  + item + CHAR(13) + CHAR(10) END 
															FROM #ControlValues 
															WHERE SId=@ControlValuesCounter
															SET @ControlValuesCounter = @ControlValuesCounter + 1
														END

												END
									INSERT EntitlementConfiguration([Description], Value, isEditable, SingleValueAttribute, Automated, CustomControl, ControlType, ControlLabel, ControlValues, [Required], HelpText, Provisioner, ProvisionerEmail, ClosedLoop, UserVisible, isActive, _changed_on, _changed_by)
									VALUES(@EntitlementDescription, @EntitlementAttributeValue, @isEditable, @SingleValueAttribute, @Automated, @CustomControl, @ControlType, @ControlLabel, @ControlValues, @Required, @HelpText, @Provisioner, @ProvisionerEmail, @ClosedLoop, @UserVisible, @isActive, @changed_on, @changed_by)
									SET @EntitlementConfiguration_key= SCOPE_IDENTITY()	
								  END
							 ELSE
								  BEGIN
									  INSERT EntitlementConfiguration([Description], Value, isEditable, SingleValueAttribute, Automated, CustomControl, ControlType, ControlLabel, ControlValues, [Required], HelpText, Provisioner, ProvisionerEmail, ClosedLoop, UserVisible, isActive, _changed_on, _changed_by)
									  VALUES(@EntitlementDescription, @EntitlementAttributeValue, @isEditable, @SingleValueAttribute, @Automated, @CustomControl, @ControlType, @ControlLabel, @ControlValues, @Required, @HelpText, @Provisioner, @ProvisionerEmail, @ClosedLoop, @UserVisible, @isActive, @changed_on, @changed_by)
									  SET @EntitlementConfiguration_key= SCOPE_IDENTITY()
								END						

								   --Begin insert entitlement
								   EXEC [dbo].[Get_NextId] N'Entitlement', @Entitlement_Id OUTPUT
								   INSERT Entitlement(Entitlement_Id, DefaultEntitlementConfiguration_key, BusinessName, Name, IsUserCreated, isActive, _changed_on, _changed_by)
								   VALUES(@Entitlement_Id, @EntitlementConfiguration_key, @EntitlementName, @EntitlementAttributeName, @IsUserCreated, @isActive, @changed_on, @changed_by)								

									SET @entitlement_key= SCOPE_IDENTITY()
									SET @TotalEntitlement = @TotalEntitlement + 1

									--Identify entitlement Tag where character less or equal to 50 characters
									Truncate Table #Tag
									INSERT #Tag(Name,[Description]) 
									SELECT LEFT(Item,50),ObjectGUID 
									FROM CoreAccessCatalogSplitStringList(@entitlement_key,';',@EntitlementTags) 
									WHERE LEN(Item)<=50 AND Item IS NOT NULL AND Item !=''

									--Begin insert entitlement Tag
									SELECT @TagCount=Count(*) FROM #Tag
								IF (@TagCount>0)
									BEGIN
									SET @TagCounter = 1
										WHILE @TagCounter <=@TagCount 
											BEGIN
												SELECT @TagName=Name FROM #Tag WHERE SId=@TagCounter
													IF NOT EXISTS(SELECT 1 FROM Tag WHERE Name =@TagName)
													BEGIN
														EXEC [dbo].[Get_NextId] N'Tag', @Tag_Id OUTPUT
														INSERT Tag(Tag_id, Name,[Owner], [State], isActive, _changed_on, _changed_by) 
														SELECT @Tag_id, @TagName,@TagOwner, @State, @isActive, @changed_on, @changed_by
														SET @Tag_key= SCOPE_IDENTITY()
													END
												ELSE
													BEGIN
													SELECT Top 1 @Tag_key =Tag_key FROM Tag WHERE Name = @TagName
													END									

										IF NOT EXISTS(SELECT 1 FROM Entitlement_Tag WHERE Entitlement_key=@Entitlement_key AND Tag_key=@Tag_key)
											BEGIN
												INSERT Entitlement_Tag (Entitlement_key,Tag_key, isActive, _changed_on, _changed_by) 
												VALUES (@Entitlement_key,@Tag_key, @isActive, @changed_on, @changed_by)
											END
										SET @TagCounter = @TagCounter + 1
									END
								END							

								--Set Mapping_Target
								IF NOT EXISTS(SELECT 1 FROM Mapping_Target WHERE TargetID=@TargetID)
									BEGIN
										INSERT Mapping_Target(TargetID,IsUserCreated) VALUES(@TargetID,@IsUserCreated)
										SET @ZTargetIDPk= SCOPE_IDENTITY()
									END
								ELSE
									BEGIN
										SELECT Top 1 @ZTargetIDPk=ZTargetIDPk FROM Mapping_Target WHERE TargetID=@TargetID
									END							

								--Set Entitlement_Mapping_Target
								 INSERT Entitlement_Mapping_Target(entitlement_key,ZTargetIDPk, isActive, _changed_on, _changed_by) 
								 VALUES(@entitlement_key,@ZTargetIDPk, @isActive, @changed_on, @changed_by)						  

								  --Start further processing if AccessType is present
								  IF (LEN(@Category)>0 AND @Category IS NOT NULL)
									  BEGIN
										IF NOT EXISTS(SELECT 1 FROM AccessType WHERE Name = @Category)
											  BEGIN
											  --Set Accesstype 
												SET @AccessType_Id = NULL
												 EXEC [dbo].[Get_NextId] N'AccessType', @AccessType_Id OUTPUT
												 SET @AccessType_Id = 'Accesstype' + @AccessType_Id
												 INSERT AccessType(accesstype_id,Name,[Description],isEnabled,isActive,isSystemType,_changed_on,_changed_by) 
												 VALUES(@AccessType_Id,@Category,@CategoryDescription,@isEnabled,@isActive,@isSystemType,@changed_on,@changed_by)
												 SET @AccessType_Key= SCOPE_IDENTITY()
											  END
										  ELSE
											  BEGIN
												  UPDATE AccessType Set [Description] = @CategoryDescription WHERE Name = @Category
												  SELECT top 1 @AccessType_Key=AccessType_Key FROM AccessType WHERE Name = @Category 
											  END
											  							  
										--Continue processing if Grouping and Grouping Owner is present
											  IF (LEN(@Grouping)>0 AND @Grouping IS NOT NULL AND LEN(@GroupingOwner)>0 AND @GroupingOwner IS NOT NULL)
												BEGIN
												--Confirm that owner Profile exist before adding Grouping
												IF EXISTS(SELECT 1 FROM Profile WHERE ProfileUID=dbo.CoreAccessCatalogGetprofileUID(@GroupingOwner))
													BEGIN
													SET @GroupingOwner = dbo.CoreAccessCatalogGetprofileUID(@GroupingOwner)
													
												--Continue processing if Grouping does not exist
										     IF NOT EXISTS(SELECT 1 FROM Role R INNER JOIN AccessType AT on R.accesstype_key=AT.accesstype_key WHERE AT.Name=@Category AND R.Name = @Grouping AND R.[Description] = @GroupingDescription AND R.Version IN (SELECT MAX(version) FROM Role WHERE Name = @Grouping AND R.[Description] = @GroupingDescription))
										     BEGIN
										     --Begin insert Grouping
												 SET @Role_Id = NULL
												 EXEC [dbo].[Get_NextId] N'Role', @Role_Id OUTPUT
												 SET @Role_Id = 'Role' + @Role_Id
												 INSERT [Role](Role_id,accesstype_key,[Version],Name,[Description],isTemplate,[Type],Creator,[Owner],isApproved,isAssignable,isInheritable,isActive,_changed_on,_changed_by)
												 VALUES(@Role_Id,@AccessType_Key,1,@Grouping,@GroupingDescription,@isTemplate,0,@GroupingOwner,@GroupingOwner,@isApproved,@isAssignable,@isInheritable,@isActive,@changed_on,@changed_by)
												 SET @Role_Key= SCOPE_IDENTITY()
												 SET @TotalGrouping = @TotalGrouping + 1
											 END
										 -- ELSE  BEGIN SELECT 'DO NOTHING' END

											--Set Role_Entitlement
								 					INSERT Role_Entitlement(role_key, entitlement_key, EntitlementConfiguration_key, isActive, _changed_on, _changed_by)
								 					VALUES (@Role_Key, @entitlement_key, @EntitlementConfiguration_key, @isActive, @changed_on, @changed_by)
											
											--Set Role Definition Approvers
										Truncate Table #DefinitionApprovers
										INSERT #DefinitionApprovers(ProfileUID,Role_Key) 
										SELECT dbo.CoreAccessCatalogGetprofileUID(Item),ObjectGUID 
										FROM CoreAccessCatalogSplitStringList(@Role_Key,';',@GroupingDefinitionApprovers)

										SELECT @DefinitionApproversCount=Count(*) FROM #DefinitionApprovers
								IF (@DefinitionApproversCount>0)
									BEGIN
									SET @DefinitionApproversCounter = 1
									WHILE @DefinitionApproversCounter <=@DefinitionApproversCount 
									BEGIN
										SELECT Top 1 @ProfileUID=ProfileUID FROM #DefinitionApprovers WHERE SId=@DefinitionApproversCounter
										IF EXISTS(SELECT 1 FROM Profile WHERE ProfileUID=@ProfileUID)
											BEGIN
											IF NOT EXISTS(SELECT 1 FROM RoleApprover WHERE ProfileUID = @ProfileUID AND role_key=@Role_Key)
												BEGIN
												INSERT RoleApprover(ProfileUID, role_key, IsDefinitionApprover, IsAddApprover,IsRemoveApprover, ApprovalType, isActive, _changed_on, _changed_by)
												SELECT ProfileUID, @Role_Key, 1, 0,0, 11, @isActive, @changed_on, @changed_by FROM #DefinitionApprovers WHERE SId=@DefinitionApproversCounter
												END
											ELSE
												BEGIN
												UPDATE RoleApprover SET IsDefinitionApprover=1 WHERE  ProfileUID = @ProfileUID AND role_key=@Role_Key
												END	
											END
										--ELSE BEGIN SELECT 'DO NOTHING' END	
										SET @DefinitionApproversCounter = @DefinitionApproversCounter + 1
									END
								END								

										--Set Role Approvers
								Truncate Table #Approvers
								INSERT #Approvers(ProfileUID,Role_Key) 
								SELECT dbo.CoreAccessCatalogGetprofileUID(Item),ObjectGUID 
								FROM CoreAccessCatalogSplitStringList(@Role_Key,';',@GroupingApprovers)

								SELECT @ApproversCount=Count(*) 
								FROM #Approvers

								IF (@ApproversCount>0)
								BEGIN
									SET @ApproversCounter = 1
									WHILE @ApproversCounter <=@ApproversCount 
									BEGIN
										SELECT Top 1 @ProfileUID=ProfileUID FROM #Approvers WHERE SId=@ApproversCounter
										IF EXISTS(SELECT 1 FROM Profile WHERE ProfileUID=@ProfileUID) 
										BEGIN
											IF NOT EXISTS(SELECT 1 FROM RoleApprover WHERE ProfileUID = @ProfileUID AND role_key=@Role_Key)
												BEGIN
												INSERT RoleApprover(ProfileUID, role_key, IsDefinitionApprover, IsAddApprover,IsRemoveApprover, ApprovalType, isActive, _changed_on, _changed_by)
												SELECT ProfileUID, @Role_Key, 0, 1,1, 11, @isActive, @changed_on, @changed_by FROM #Approvers WHERE SId=@ApproversCounter
												END
											ELSE
												BEGIN
												UPDATE RoleApprover SET IsAddApprover=1,IsRemoveApprover=1 WHERE  ProfileUID = @ProfileUID AND role_key=@Role_Key
												END	

										END	
									--ELSE BEGIN SELECT 'DO NOTHING' END
										SET @ApproversCounter = @ApproversCounter + 1
									END

								END			 		

								--Identify Role/Grouping Tag where character less or equal to 50 characters	

								Truncate Table #Tag
								INSERT #Tag(Name,[Description]) 
								SELECT LEFT(Item,50),ObjectGUID FROM CoreAccessCatalogSplitStringList(@Role_Key,';',@GroupingTags)  
								WHERE LEN(Item)<=50 AND Item IS NOT NULL AND Item !=''								

								SELECT @TagCount=Count(*) FROM #Tag
								IF (@TagCount>0)
									BEGIN
										SET @TagCounter = 1
										WHILE @TagCounter <=@TagCount 
											BEGIN
											SELECT @TagName=Name  FROM #Tag WHERE SId=@TagCounter
											IF NOT EXISTS(SELECT 1 FROM Tag WHERE Name =@TagName )
												BEGIN
													EXEC [dbo].[Get_NextId] N'Tag', @Tag_Id OUTPUT
													INSERT Tag(Tag_id, Name,[Owner], [State], isActive, _changed_on, _changed_by) 
													SELECT @Tag_id, @TagName,@TagOwner, @State, @isActive, @changed_on, @changed_by
													SET @Tag_key= SCOPE_IDENTITY()
												END
											ELSE
												BEGIN
												SELECT TOP 1 @Tag_key =Tag_Key FROM Tag WHERE Name=@TagName
												END									

											IF NOT EXISTS(SELECT 1 FROM Role_Tag WHERE Role_key =@Role_Key AND Tag_key=@Tag_key)
												BEGIN
													INSERT Role_Tag (Role_key,Tag_key, isActive, _changed_on, _changed_by) 
													VALUES (@Role_Key,@Tag_key, @isActive, @changed_on, @changed_by)
												END	
												SET @TagCounter = @TagCounter + 1
										END
									END
								END
											--ELSE BEGIN SELECT 'DO NOTHING' END	  
								END
								END
								END
						
					  END
			SET @Counter=@Counter + 1
	END
END

Sample Grouping and Entitlement

Execute the following sequel code to populate the CoreAccessCatalogStagingTable with sample Groupings and Entitlements

INSERT INTO [CoreAccessCatalogStagingTable] ([Category],[CategoryDescription],[Grouping],[GroupingDescription],[GroupingOwner],[GroupingDefinitionApprovers],[GroupingApprovers],[GroupingTags],[EntitlementName],[EntitlementDescription],[EntitlementAttributeName],[EntitlementAttributeValue],[EntitlementTags],[TargetID],[isEditable],[Automated],[UserVisible],[Required],[ControlType],[ControlLabel],[ControlValues]) VALUES ('Category Name 1','Category Name 1 Description Text','Grouping Name 1','Grouping Name 1 Description','emailAddress1','emailAddress2;emailAddress3;','emailAddress2;emailAddress3;','tagname1;tagname2;tagname3','Entitlement Name 1','Entitlement Name 1 Description','Attributename','Attributevalue','tagname1;tagname2;tagname3','TargetID 1',0,1,1,1,'TextBox','ControlLabel 1',NULL) 
INSERT INTO [CoreAccessCatalogStagingTable] ([Category],[CategoryDescription],[Grouping],[GroupingDescription],[GroupingOwner],[GroupingDefinitionApprovers],[GroupingApprovers],[GroupingTags],[EntitlementName],[EntitlementDescription],[EntitlementAttributeName],[EntitlementAttributeValue],[EntitlementTags],[TargetID],[isEditable],[Automated],[UserVisible],[Required],[ControlType],[ControlLabel],[ControlValues]) VALUES ('Category Name 2','Category Name 2 Description Text','Grouping Name 2','Grouping Name 2 Description','emailAddress1','emailAddress2;emailAddress3;','emailAddress2;emailAddress3;','tagname1;tagname2;tagname3','Entitlement Name 2','Entitlement Name 2 Description','Attributename',NULL,'tagname1;tagname2;tagname3','TargetID 2',1,1,1,1,'DropDown','ControlLabel 2','Option 1;Option 2;Option 3') 
INSERT INTO [CoreAccessCatalogStagingTable] ([Category],[CategoryDescription],[Grouping],[GroupingDescription],[GroupingOwner],[GroupingDefinitionApprovers],[GroupingApprovers],[GroupingTags],[EntitlementName],[EntitlementDescription],[EntitlementAttributeName],[EntitlementAttributeValue],[EntitlementTags],[TargetID],[isEditable],[Automated],[UserVisible],[Required],[ControlType],[ControlLabel],[ControlValues]) VALUES ('Category Name 3','Category Name 3 Description Text','Grouping Name 3','Grouping Name 3 Description','emailAddress1','emailAddress2;emailAddress3;','emailAddress2;emailAddress3;','tagname1;tagname2;tagname3','Entitlement Name 3','Entitlement Name 3 Description','Attributename',NULL,'tagname1;tagname2;tagname3','TargetID 3',1,1,1,1,'RadioButton','ControlLabel 3','Option 1;Option 2;Option 3') 
INSERT INTO [CoreAccessCatalogStagingTable] ([Category],[CategoryDescription],[Grouping],[GroupingDescription],[GroupingOwner],[GroupingDefinitionApprovers],[GroupingApprovers],[GroupingTags],[EntitlementName],[EntitlementDescription],[EntitlementAttributeName],[EntitlementAttributeValue],[EntitlementTags],[TargetID],[isEditable],[Automated],[UserVisible],[Required],[ControlType],[ControlLabel],[ControlValues]) VALUES ('Category Name 4','Category Name 4 Description Text','Grouping Name 4','Grouping Name 4 Description','emailAddress1','emailAddress2;emailAddress3;','emailAddress2;emailAddress3;','tagname1;tagname2;tagname3','Entitlement Name 4','Entitlement Name 4 Description','Attributename',NULL,'tagname1;tagname2;tagname3','TargetID 4',1,1,1,1,'TextArea','ControlLabel 4',NULL) 
INSERT INTO [CoreAccessCatalogStagingTable] ([Category],[CategoryDescription],[Grouping],[GroupingDescription],[GroupingOwner],[GroupingDefinitionApprovers],[GroupingApprovers],[GroupingTags],[EntitlementName],[EntitlementDescription],[EntitlementAttributeName],[EntitlementAttributeValue],[EntitlementTags],[TargetID],[isEditable],[Automated],[UserVisible],[Required],[ControlType],[ControlLabel],[ControlValues]) VALUES ('Category Name 5','Category Name 5 Description Text','Grouping Name 5','Grouping Name 5 Description','emailAddress1','emailAddress2;emailAddress3;','emailAddress2;emailAddress3;','tagname1;tagname2;tagname3','Entitlement Name 5','Entitlement Name 5 Description','Attributename',NULL,'tagname1;tagname2;tagname3','TargetID 5',1,1,1,1,'Boolean','ControlLabel 5',NULL)