USP_SECURITY_SYSTEMROLE_LOADFROMXMLDOCUMENT

CREATE procedure dbo.USP_SECURITY_SYSTEMROLE_LOADFROMXMLDOCUMENT
    @SYSTMEMROLEXML xml,
    @CHANGEAGENTID uniqueidentifier=null

with execute as owner

as

    /*
    Adds or modifies the tasks, queryviews, and features
    assigned to a System Role from a document created 
    via the UFN_SECURITY_SYSTEMROLE_TOXMLDOCUMENT function.
    */

    set nocount on;

    begin try

        if @CHANGEAGENTID is null
            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

        --Cache CONTEXT INFO

        declare @contextCache varbinary(128);
        set @contextCache = CONTEXT_INFO();

        if not @CHANGEAGENTID is null
            set CONTEXT_INFO @CHANGEAGENTID;

        declare @SYSTEMROLEID uniqueidentifier ;        
        set @SYSTEMROLEID=
        @SYSTMEMROLEXML.value(
        '(SystemRole/RecordInformation/ID)[1]'
        ,'uniqueidentifier')

        if @SYSTEMROLEID is null
            raiserror('No value found at SystemRole/RecordInformation/ID ',16,10);

        declare @NAME nvarchar(255) ;        
        set @NAME=
        @SYSTMEMROLEXML.value(
        '(SystemRole/RecordInformation/Name)[1]'
        ,'nvarchar(255)')

        if @NAME is null 
            raiserror('No value found at SystemRole/RecordInformation/Name ',16,11);

        if LEN(@NAME)=0 
            raiserror('An empty element was found at SystemRole/RecordInformation/Name . Name is required.',16,12);

        declare @DESCRIPTION nvarchar(max) ;        
        set @DESCRIPTION=
        COALESCE(@SYSTMEMROLEXML.value(
        '(SystemRole/RecordInformation/Description)[1]'
        ,'nvarchar(max)'),'');

        if exists (select ID from dbo.SYSTEMROLE where ID=@SYSTEMROLEID)
            begin
                update dbo.SYSTEMROLE
                set NAME=@NAME,DESCRIPTION=@DESCRIPTION,CHANGEDBYID=@CHANGEAGENTID,DATECHANGED=GETDATE()
                where ID=@SYSTEMROLEID;
            end
        else
            begin
                insert into dbo.SYSTEMROLE
                (ID,NAME,DESCRIPTION,ADDEDBYID,CHANGEDBYID)
                values (@SYSTEMROLEID,@NAME,@DESCRIPTION,@CHANGEAGENTID,@CHANGEAGENTID);

            end

        declare @MISSINGFEATUREID nvarchar(max);
        declare @perm table (FEATUREID uniqueidentifier not null,GRANTORDENY bit not null default 0);

        --Start Tasks

            --Tasks need to go first because triggers fire that

            --assign rights to features thay may need to get overwritten by deny features in the document.


            declare @tasks table (TASKID uniqueidentifier not null, DISPLAYONHOMEPAGE bit null)

            insert into @tasks (TASKID, DISPLAYONHOMEPAGE)
            select
            T.c.value('(TaskID)[1]','uniqueidentifier'),
            T.c.value('(DisplayOnHomePage)[1]','bit')
            from   @SYSTMEMROLEXML.nodes('SystemRole/Tasks/Task') T(c);

            delete from dbo.SYSTEMROLETASK
            where SYSTEMROLEID=@SYSTEMROLEID;

            insert into dbo.SYSTEMROLETASK
            (SYSTEMROLEID,TASKID,DISPLAYONHOMEPAGE,ADDEDBYID,CHANGEDBYID)
            select  distinct
            @SYSTEMROLEID,
            TASKID,
            coalesce(DISPLAYONHOMEPAGE, 0),
            @CHANGEAGENTID,
            @CHANGEAGENTID
            from @tasks t
            inner join dbo.TASKCATALOG on t.TASKID = TASKCATALOG.ID;
        -- End Tasks


        --Forms

            insert into @perm (FEATUREID,GRANTORDENY)
            select
            T.c.value('(FeatureID)[1]','uniqueidentifier') ,
            case T.c.value('(GrantState)[1]','nvarchar(5)') when 'Grant' then
            1 else 0 end

            from   @SYSTMEMROLEXML.nodes('SystemRole/FeaturePerms/DataForms/DataForm') T(c);

            delete from dbo.SYSTEMROLEPERM_DATAFORMINSTANCE
            where SYSTEMROLEID=@SYSTEMROLEID;


            insert into dbo.SYSTEMROLEPERM_DATAFORMINSTANCE
            (SYSTEMROLEID,DATAFORMINSTANCECATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID)
            select  distinct
            @SYSTEMROLEID,
            FEATUREID,
            GRANTORDENY,
            @CHANGEAGENTID,
            @CHANGEAGENTID
            from @perm p
            inner join dbo.DATAFORMINSTANCECATALOG on p.FEATUREID = DATAFORMINSTANCECATALOG.ID;

        --Data Lists

            delete from dbo.SYSTEMROLEPERM_DATALIST
            where SYSTEMROLEID=@SYSTEMROLEID;

            delete from @perm;

            insert into @perm (FEATUREID,GRANTORDENY)
            select 
            T.c.value('(FeatureID)[1]','uniqueidentifier') ,
            case T.c.value('(GrantState)[1]','nvarchar(5)') when 'Grant' then
            1 else 0 end

            FROM   @SYSTMEMROLEXML.nodes('SystemRole/FeaturePerms/DataLists/DataList') T(c);                    

            insert into dbo.SYSTEMROLEPERM_DATALIST
            (SYSTEMROLEID,DATALISTCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID)
            select  distinct
            @SYSTEMROLEID,
            FEATUREID,
            GRANTORDENY,
            @CHANGEAGENTID,
            @CHANGEAGENTID
            from @perm p
            inner join dbo.DATALISTCATALOG on p.FEATUREID = DATALISTCATALOG.ID;

        --RecordOperations

            delete from dbo.SYSTEMROLEPERM_RECORDOPERATION
            where SYSTEMROLEID=@SYSTEMROLEID;

            delete from @perm;

            insert into @perm (FEATUREID,GRANTORDENY)
            select 
            T.c.value('(FeatureID)[1]','uniqueidentifier') ,
            case T.c.value('(GrantState)[1]','nvarchar(5)') when 'Grant' then
            1 else 0 end

            from @SYSTMEMROLEXML.nodes('SystemRole/FeaturePerms/RecordOperations/RecordOperation') T(c);

            insert into dbo.SYSTEMROLEPERM_RECORDOPERATION
            (SYSTEMROLEID,RECORDOPERATIONCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID)
            select  distinct
            @SYSTEMROLEID,
            FEATUREID,
            GRANTORDENY,
            @CHANGEAGENTID,
            @CHANGEAGENTID
            from @perm p
            inner join dbo.RECORDOPERATIONCATALOG on p.FEATUREID = RECORDOPERATIONCATALOG.ID;

        --BusinessProcesses

            delete from dbo.SYSTEMROLEPERM_BUSINESSPROCESS
            where SYSTEMROLEID=@SYSTEMROLEID;

            delete from @perm;

            insert into @perm (FEATUREID,GRANTORDENY)
            select 
            T.c.value('(FeatureID)[1]','uniqueidentifier') ,
            case T.c.value('(GrantState)[1]','nvarchar(5)') when 'Grant' then
            1 else 0 end

            from   @SYSTMEMROLEXML.nodes('SystemRole/FeaturePerms/BusinessProcesses/BusinessProcess') T(c);

            insert into dbo.SYSTEMROLEPERM_BUSINESSPROCESS
            (SYSTEMROLEID,BUSINESSPROCESSCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID)
            SELECT  DISTINCT
            @SYSTEMROLEID,
            FEATUREID,
            GRANTORDENY,
            @CHANGEAGENTID,
            @CHANGEAGENTID
            from @perm p
            inner join dbo.BUSINESSPROCESSCATALOG on p.FEATUREID = BUSINESSPROCESSCATALOG.ID;

        --Dashboards

            DELETE FROM dbo.SYSTEMROLEPERM_DASHBOARD
            WHERE SYSTEMROLEID=@SYSTEMROLEID;

            delete from @perm;

            insert into @perm (FEATUREID,GRANTORDENY)
            select 
            T.c.value('(FeatureID)[1]','uniqueidentifier') ,
            case T.c.value('(GrantState)[1]','nvarchar(5)') when 'Grant' then
            1 else 0 end

            from   @SYSTMEMROLEXML.nodes('SystemRole/FeaturePerms/Dashboards/Dashboard') T(c);

            insert into dbo.SYSTEMROLEPERM_DASHBOARD
            (SYSTEMROLEID,DASHBOARDCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID)
            select  distinct
            @SYSTEMROLEID,
            FEATUREID,
            GRANTORDENY,
            @CHANGEAGENTID,
            @CHANGEAGENTID
            from @perm p
            inner join dbo.DASHBOARDCATALOG on p.FEATUREID = DASHBOARDCATALOG.ID;

        --QueryViews

            delete from dbo.SYSTEMROLEPERM_QUERYVIEW
            where SYSTEMROLEID=@SYSTEMROLEID;

            delete from @perm;

            insert into @perm (FEATUREID,GRANTORDENY)
            select
            T.c.value('(FeatureID)[1]','uniqueidentifier') ,
            CASE T.c.value('(GrantState)[1]','nvarchar(5)') when 'Grant' then
            1 else 0 end

            from   @SYSTMEMROLEXML.nodes('SystemRole/FeaturePerms/QueryViews/QueryView') T(c);

            insert into dbo.SYSTEMROLEPERM_QUERYVIEW
            (SYSTEMROLEID,QUERYVIEWCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID)
            select  distinct
            @SYSTEMROLEID,
            FEATUREID,
            GRANTORDENY,
            @CHANGEAGENTID,
            @CHANGEAGENTID
            from @perm p
            inner join dbo.QUERYVIEWCATALOG on p.FEATUREID = QUERYVIEWCATALOG.ID;

        --Smart Queries

            delete from dbo.SYSTEMROLEPERM_SMARTQUERY
            where SYSTEMROLEID = @SYSTEMROLEID;

            delete from @perm 

            insert into @perm (FEATUREID, GRANTORDENY)
            select
            T.c.value('(FeatureID)[1]','uniqueidentifier'), 
            case T.c.value('(GrantState)[1]','nvarchar(5)') when 'Grant' then 1 else 0 end
            from @SYSTMEMROLEXML.nodes('SystemRole/FeaturePerms/SmartQueries/SmartQuery') T(c);

            insert into dbo.SYSTEMROLEPERM_SMARTQUERY (SYSTEMROLEID,SMARTQUERYCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID)
                select
                    @SYSTEMROLEID,
                    FEATUREID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID
                from @perm p
                inner join dbo.SMARTQUERYCATALOG on p.FEATUREID = SMARTQUERYCATALOG.ID;
        --End Smart Queries    


        --KPIs

            delete from dbo.SYSTEMROLEPERM_KPI
            where SYSTEMROLEID = @SYSTEMROLEID;

            delete from @perm 

            insert into @perm (FEATUREID, GRANTORDENY)
            select
            T.c.value('(FeatureID)[1]','uniqueidentifier'), 
            case T.c.value('(GrantState)[1]','nvarchar(5)') when 'Grant' then 1 else 0 end
            from @SYSTMEMROLEXML.nodes('SystemRole/FeaturePerms/Kpis/Kpi') T(c);

            insert into dbo.SYSTEMROLEPERM_KPI (SYSTEMROLEID,KPICATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID)
                select
                    @SYSTEMROLEID,
                    FEATUREID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID
                from @perm p
                inner join dbo.KPICATALOG on p.FEATUREID = KPICATALOG.ID;
        --End KPIs


        -- Code Tables

            delete from dbo.SYSTEMROLEPERM_CODETABLE
            where SYSTEMROLEID = @SYSTEMROLEID;

            declare @CODETABLEPERMS table (CODETABLEID uniqueidentifier not null, ADDPERMISSIONCODE tinyint, UPDATEPERMISSIONCODE tinyint, DELETEPERMISSIONCODE tinyint)

            insert into @CODETABLEPERMS (CODETABLEID, ADDPERMISSIONCODE, UPDATEPERMISSIONCODE, DELETEPERMISSIONCODE)
            select
            T.c.value('(CodeTableID)[1]', 'uniqueidentifier'),
            case T.c.value('(AddPermission)[1]', 'nvarchar(7)') when 'NotSet' then 0 when 'Granted' then 1 else 2 end,
            case T.c.value('(UpdatePermission)[1]', 'nvarchar(7)') when 'NotSet' then 0 when 'Granted' then 1 else 2 end,
            case T.c.value('(DeletePermission)[1]', 'nvarchar(7)') when 'NotSet' then 0 when 'Granted' then 1 else 2 end
            from @SYSTMEMROLEXML.nodes('SystemRole/CodeTables/CodeTable') T(c);

            --Delete all the permission entries where no permission is actually set

            delete from @CODETABLEPERMS
            where ADDPERMISSIONCODE = 0 and UPDATEPERMISSIONCODE = 0 and DELETEPERMISSIONCODE = 0;

            insert into dbo.SYSTEMROLEPERM_CODETABLE
            (SYSTEMROLEID, CODETABLECATALOGID, ADDPERMISSIONCODE, UPDATEPERMISSIONCODE, DELETEPERMISSIONCODE, ADDEDBYID, CHANGEDBYID)
            select distinct
            @SYSTEMROLEID,
            CODETABLEID,
            ADDPERMISSIONCODE,
            UPDATEPERMISSIONCODE,
            DELETEPERMISSIONCODE,
            @CHANGEAGENTID,
            @CHANGEAGENTID
            from @CODETABLEPERMS ctp
            inner join dbo.CODETABLECATALOG on ctp.CODETABLEID = CODETABLECATALOG.ID
        --End Code Tables


        --Feature groups

            delete from dbo.SYSTEMROLEPERM_FEATUREGROUP
            where SYSTEMROLEID = @SYSTEMROLEID;

            delete from @perm 

            insert into @perm (FEATUREID, GRANTORDENY)
            select
            T.c.value('(FeatureGroupCatalogID)[1]','uniqueidentifier'), 
            case T.c.value('(GrantState)[1]','nvarchar(5)') when 'Grant' then 1 else 0 end
            from @SYSTMEMROLEXML.nodes('SystemRole/FeatureGroups/FeatureGroup') T(c);

            insert into dbo.SYSTEMROLEPERM_FEATUREGROUP (SYSTEMROLEID,FEATUREGROUPCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID)
                select
                    @SYSTEMROLEID,
                    FEATUREID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID
                from @perm p
                inner join dbo.FEATUREGROUPCATALOG on p.FEATUREID = FEATUREGROUPCATALOG.ID;
        --End feature groups


        /*
        Constituent security has been moved down to the system role application user level
        --Ring Fence
            declare @CONSTIT_SECURITY_RINGFENCENAME nvarchar(100);
            set @CONSTIT_SECURITY_RINGFENCENAME=@SYSTMEMROLEXML.value('(SystemRole/ConstitSecurityRingFence/Name)[1]','nvarchar(100)');

            if @CONSTIT_SECURITY_RINGFENCENAME is not null 
            begin
                -- Remove any existing record associated with the system role in SYSTEMROLE_CONSTIT_SECURITY_ATTRIBUTE
                delete from dbo.SYSTEMROLE_CONSTIT_SECURITY_ATTRIBUTE
                where ID = @SYSTEMROLEID

                if @CONSTIT_SECURITY_RINGFENCENAME = 'Records with no groups assigned'
                begin
                    -- If the role can only access records outside of ringfences,
                    -- then simply update the role's record security mode accordingly
                    update dbo.SYSTEMROLE
                    set RECORDSECURITYMODE = 1
                    where ID = @SYSTEMROLEID;
                end
                else
                begin
                    -- Otherwise, create the specified security attribute and 
                    -- assign it to the role.
                    declare @CONSTIT_SECURITY_RINGFENCEID uniqueidentifier;
                    select @CONSTIT_SECURITY_RINGFENCEID = ID
                    from dbo.CONSTIT_SECURITY_ATTRIBUTE
                    where NAME = @CONSTIT_SECURITY_RINGFENCENAME;

                    if @CONSTIT_SECURITY_RINGFENCEID is null
                    begin
                        declare @CONSTIT_SECURITY_RINGFENCEDESCRIPTION nvarchar(255);
                        set @CONSTIT_SECURITY_RINGFENCEDESCRIPTION=coalesce(@SYSTMEMROLEXML.value('(SystemRole/ConstitSecurityRingFence/Description)[1]','nvarchar(255)'),'');

                        set @CONSTIT_SECURITY_RINGFENCEID = newid();
                        insert into dbo.CONSTIT_SECURITY_ATTRIBUTE
                        (ID,NAME,DESCRIPTION,ADDEDBYID,CHANGEDBYID)
                        values
                        (@CONSTIT_SECURITY_RINGFENCEID,@CONSTIT_SECURITY_RINGFENCENAME,@CONSTIT_SECURITY_RINGFENCEDESCRIPTION,@CHANGEAGENTID,@CHANGEAGENTID);

                    end

                    insert into dbo.SYSTEMROLE_CONSTIT_SECURITY_ATTRIBUTE
                    (ID,CONSTIT_SECURITY_ATTRIBUTEID,ADDEDBYID,CHANGEDBYID)
                    values
                    (@SYSTEMROLEID,@CONSTIT_SECURITY_RINGFENCEID,@CHANGEAGENTID,@CHANGEAGENTID);
                end
            end
        --End Ring Fence
        */

        /*
        Site security has been moved down to the system role application user level
        --Site Security
            declare @SITE nvarchar(1024);
            set @SITE = @SYSTMEMROLEXML.value('(SystemRole/SiteSecurity)[1]', 'nvarchar(1024)');

            if @SITE is not null
            begin
                if @SITE = 'Records with no site'
                begin
                    -- If the role can only access records that are not
                    -- assigned to sites, then simply update the role's
                    -- site security mode accordingly
                    update dbo.SYSTEMROLE
                    set SITESECURITYMODE = 1
                    where ID = @SYSTEMROLEID;
                end
                else
                begin
                    -- Otherwise, set the role's site id                
                    declare @SITEID uniqueidentifier;
                    select @SITEID = ID
                    from dbo.SITE
                    where NAME = @SITE;

                    -- Unlike constituent ring fences, a site
                    -- that does not exist is not created implicitly.
                    -- Instead, the role is loaded with no site
                    -- affiliation.
                    if @SITEID is not null
                    begin
                        update dbo.SYSTEMROLE
                        set SITEID = @SITEID
                        where ID = @SYSTEMROLEID;
                    end
                end
            end
        --End Site Security
        */

        --Batch Types

            delete from @perm;

            insert into @perm (FEATUREID,GRANTORDENY)
            select
            T.c.value('(BatchTypeID)[1]','uniqueidentifier') ,
            case T.c.value('(GrantState)[1]','nvarchar(5)') when 'Grant' then
            1 else 0 end

            from   @SYSTMEMROLEXML.nodes('SystemRole/BatchTypes/BatchType') T(c);

            delete from dbo.SYSTEMROLEPERM_BATCHTYPE
            where SYSTEMROLEID=@SYSTEMROLEID;

            insert into dbo.SYSTEMROLEPERM_BATCHTYPE
            (SYSTEMROLEID,BATCHTYPECATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID)
            select  distinct
            @SYSTEMROLEID,
            FEATUREID,
            GRANTORDENY,
            @CHANGEAGENTID,
            @CHANGEAGENTID
            from @perm p
            inner join dbo.BATCHTYPECATALOG on p.FEATUREID = BATCHTYPECATALOG.ID;
        --End Batch Types


        --Start KPI Instances

            declare @kpiinstances table (KPIINSTANCEID uniqueidentifier not null)

            insert into @kpiinstances (KPIINSTANCEID)
            select
            T.c.value('(KpiInstanceID)[1]','uniqueidentifier')
            from   @SYSTMEMROLEXML.nodes('SystemRole/KpiInstances/KpiInstance') T(c);

            delete from dbo.SYSTEMROLEKPIINSTANCE
            where SYSTEMROLEID=@SYSTEMROLEID;

            insert into dbo.SYSTEMROLEKPIINSTANCE
            (SYSTEMROLEID,KPIINSTANCEID,ADDEDBYID,CHANGEDBYID)
            select  distinct
            @SYSTEMROLEID,
            KPIINSTANCEID,
            @CHANGEAGENTID,
            @CHANGEAGENTID
            from @kpiinstances t
            inner join dbo.KPIINSTANCE on t.KPIINSTANCEID = KPIINSTANCE.ID;
        -- End KPI Instances


        --Report params

            delete from dbo.SYSTEMROLEPERM_REPORT
            where SYSTEMROLEID=@SYSTEMROLEID;

            delete from @perm;

            insert into @perm (FEATUREID,GRANTORDENY)
            select 
            T.c.value('(FeatureID)[1]','uniqueidentifier') ,
            case T.c.value('(GrantState)[1]','nvarchar(5)') when 'Grant' then
            1 else 0 end

            FROM   @SYSTMEMROLEXML.nodes('SystemRole/FeaturePerms/ReportParams/ReportParam') T(c);                    

            insert into dbo.SYSTEMROLEPERM_REPORT
            (SYSTEMROLEID,REPORTCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID)
            select  distinct
            @SYSTEMROLEID,
            FEATUREID,
            GRANTORDENY,
            @CHANGEAGENTID,
            @CHANGEAGENTID
            from @perm p
            inner join dbo.REPORTCATALOG on p.FEATUREID = REPORTCATALOG.ID;    
        --End report params


        --Begin System Privileges

            delete from dbo.SYSTEMROLEPERM_SYSTEMPRIVILEGE
            where SYSTEMROLEID=@SYSTEMROLEID;

            delete from @perm;

            insert into @perm (FEATUREID,GRANTORDENY)
            select 
            T.c.value('(FeatureID)[1]','uniqueidentifier') ,
            case T.c.value('(GrantState)[1]','nvarchar(5)') when 'Grant' then
            1 else 0 end

            FROM   @SYSTMEMROLEXML.nodes('SystemRole/FeaturePerms/SysPrivs/SysPriv') T(c);                    

            insert into dbo.SYSTEMROLEPERM_SYSTEMPRIVILEGE
            (SYSTEMROLEID,SYSTEMPRIVILEGECATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID)
            select  distinct
            @SYSTEMROLEID,
            FEATUREID,
            GRANTORDENY,
            @CHANGEAGENTID,
            @CHANGEAGENTID
            from @perm p
            inner join dbo.SYSTEMPRIVILEGECATALOG on p.FEATUREID = SYSTEMPRIVILEGECATALOG.ID;
        --End System Privileges


        --Begin Search Lists

            delete from dbo.SYSTEMROLEPERM_SEARCHLIST
            where SYSTEMROLEID=@SYSTEMROLEID;

            delete from @perm;

            insert into @perm (FEATUREID,GRANTORDENY)
            select 
            T.c.value('(FeatureID)[1]','uniqueidentifier') ,
            case T.c.value('(GrantState)[1]','nvarchar(5)') when 'Grant' then
            1 else 0 end

            FROM   @SYSTMEMROLEXML.nodes('SystemRole/FeaturePerms/SearchLists/SearchList') T(c);                    

            insert into dbo.SYSTEMROLEPERM_SEARCHLIST
            (SYSTEMROLEID,SEARCHLISTCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID)
            select  distinct
            @SYSTEMROLEID,
            FEATUREID,
            GRANTORDENY,
            @CHANGEAGENTID,
            @CHANGEAGENTID
            from @perm p
            inner join dbo.SEARCHLISTCATALOG on p.FEATUREID = SEARCHLISTCATALOG.ID;
        --End Search Lists


        --Begin Map Entities

            delete from dbo.SYSTEMROLEPERM_MAPENTITY
            where SYSTEMROLEID=@SYSTEMROLEID;

            delete from @perm;

            insert into @perm (FEATUREID,GRANTORDENY)
            select 
            T.c.value('(FeatureID)[1]','uniqueidentifier') ,
            case T.c.value('(GrantState)[1]','nvarchar(5)') when 'Grant' then
            1 else 0 end

            FROM   @SYSTMEMROLEXML.nodes('SystemRole/FeaturePerms/MapEntities/MapEntity') T(c);                    

            insert into dbo.SYSTEMROLEPERM_MAPENTITY
            (SYSTEMROLEID,MAPENTITYCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID)
            select  distinct
            @SYSTEMROLEID,
            FEATUREID,
            GRANTORDENY,
            @CHANGEAGENTID,
            @CHANGEAGENTID
            from @perm p
            inner join dbo.MAPENTITYCATALOG on p.FEATUREID = MAPENTITYCATALOG.ID;
        --End Map Entities


        --Begin Code Table Entries

            declare @codetableentry table (CODETABLECATALOGID uniqueidentifier not null,CODETABLEENTRYID uniqueidentifier not null,ISDENIED bit not null default 1);

            insert into @codetableentry (CODETABLECATALOGID,CODETABLEENTRYID,ISDENIED)
            select
            T.c.value('(CodeTableCatalogID)[1]','uniqueidentifier') ,
            T.c.value('(CodeTableEntryID)[1]','uniqueidentifier') ,
            case T.c.value('(GrantState)[1]','nvarchar(5)') when 'Grant' then
            0 else 1 end

            from   @SYSTMEMROLEXML.nodes('SystemRole/CodeTableEntries/CodeTableEntry') T(c);

            delete from dbo.SYSTEMROLEPERM_CODETABLEENTRY
            where SYSTEMROLEID=@SYSTEMROLEID;

            insert into dbo.SYSTEMROLEPERM_CODETABLEENTRY
            (SYSTEMROLEID,CODETABLECATALOGID,CODETABLEENTRYID,ISDENIED,ADDEDBYID,CHANGEDBYID)
            select  distinct
            @SYSTEMROLEID,
            c.CODETABLECATALOGID,
            c.CODETABLEENTRYID,
            c.ISDENIED,
            @CHANGEAGENTID,
            @CHANGEAGENTID
            from @codetableentry c
            inner join dbo.CODETABLECATALOG on c.CODETABLECATALOGID = CODETABLECATALOG.ID;
        --End Code Table Entries

    end try

    begin catch

        --Restore CONTEXT_INFO

        if not @contextCache is null
            set CONTEXT_INFO @contextCache;

        exec dbo.USP_RAISE_ERROR;
        return 1;

    end catch 

    --Restore CONTEXT_INFO

    if not @contextCache is null
        set CONTEXT_INFO @contextCache;

    return 0;