USP_SYSTEMROLE_COPY

            CREATE procedure dbo.USP_SYSTEMROLE_COPY
            (
                @ID uniqueidentifier output,
                @SYSTEMROLEID uniqueidentifier,
                @ROLENAME nvarchar(255),
                @DESCRIPTION nvarchar(max) = '',
                @CHANGEAGENTID uniqueidentifier = null,
                @COPYUSERS bit = 0
            )
            as
            set nocount on;

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

            declare @CURRENTDATE datetime;
            set @CURRENTDATE = getdate();

            if @ID is null
                set @ID = newid();

            begin try

            -- Create system role

            insert into dbo.SYSTEMROLE
                (ID, NAME, DESCRIPTION, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values
                (@ID, @ROLENAME, @DESCRIPTION, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

            -- Copy tasks

            insert into dbo.SYSTEMROLETASK
                (ID,SYSTEMROLEID,TASKID,DISPLAYONHOMEPAGE,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                (select
                    newid(),
                    @ID,
                    TASKID,
                    DISPLAYONHOMEPAGE,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.SYSTEMROLETASK
                where SYSTEMROLEID = @SYSTEMROLEID and TASKID not in (select TASKID from dbo.SYSTEMROLETASK where SYSTEMROLEID = @ID));

            -- Copy users and site assignments

            if @COPYUSERS = 1 begin            
                declare @IDMAP table (OLDID uniqueidentifier, NEWID uniqueidentifier);

                insert into @IDMAP(OLDID, NEWID)
                    select ID, newid() from dbo.SYSTEMROLEAPPUSER where SYSTEMROLEID = @SYSTEMROLEID and SYSTEMROLEAPPUSER.APPUSERID not in (select APPUSERID from dbo.SYSTEMROLEAPPUSER where SYSTEMROLEID = @ID);

                insert into dbo.SYSTEMROLEAPPUSER(ID, SYSTEMROLEID, APPUSERID, SECURITYMODECODE, BRANCHSITEID, CONSTITUENTSECURITYMODECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select
                        IDMAP.NEWID,
                        @ID,
                        APPUSERID,
                        SECURITYMODECODE,
                        BRANCHSITEID,
                        CONSTITUENTSECURITYMODECODE,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                    from
                        dbo.SYSTEMROLEAPPUSER
                        inner join @IDMAP as IDMAP on IDMAP.OLDID = SYSTEMROLEAPPUSER.ID

                insert into dbo.SYSTEMROLEAPPUSERSITE (ID, SYSTEMROLEAPPUSERID, SITEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select
                        newid(),
                        IDMAP.NEWID,
                        SYSTEMROLEAPPUSERSITE.SITEID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                    from
                        @IDMAP as IDMAP
                        inner join dbo.SYSTEMROLEAPPUSERSITE on SYSTEMROLEAPPUSERSITE.SYSTEMROLEAPPUSERID = IDMAP.OLDID;    

                insert into dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY (ID, SYSTEMROLEAPPUSERID, CONSTITUENTSECURITYATTRIBUTEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select
                        newid(),
                        IDMAP.NEWID,
                        SYSTEMROLEAPPUSERCONSTITUENTSECURITY.CONSTITUENTSECURITYATTRIBUTEID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                    from
                        @IDMAP as IDMAP
                        inner join dbo.SYSTEMROLEAPPUSERCONSTITUENTSECURITY on SYSTEMROLEAPPUSERCONSTITUENTSECURITY.SYSTEMROLEAPPUSERID = IDMAP.OLDID;
            end

            -- Copy groups

            declare @ADIDMAP table (OLDID uniqueidentifier, NEWID uniqueidentifier);

            insert into @ADIDMAP(OLDID, NEWID)
                select ID, newid() from dbo.SYSTEMROLEADGROUP where SYSTEMROLEID = @SYSTEMROLEID and GROUPID not in (select GROUPID from dbo.SYSTEMROLEADGROUP where SYSTEMROLEID = @ID);

            insert into dbo.SYSTEMROLEADGROUP
                (ID, SYSTEMROLEID, GROUPID, LDAPQUERY, DISPLAYNAME, SEARCHROOT, INCLUDESUBCONTAINERS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SECURITYMODECODE, BRANCHSITEID)
                select
                    ADIDMAP.NEWID,
                    @ID,
                    GROUPID,
                    LDAPQUERY,
                    DISPLAYNAME,
                    SEARCHROOT,
                    INCLUDESUBCONTAINERS,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE,
                    SECURITYMODECODE, 
                    BRANCHSITEID
                from
                    dbo.SYSTEMROLEADGROUP
                    inner join @ADIDMAP as ADIDMAP on ADIDMAP.OLDID = SYSTEMROLEADGROUP.ID
                where 
                    SYSTEMROLEID = @SYSTEMROLEID and GROUPID not in (select GROUPID from dbo.SYSTEMROLEADGROUP where SYSTEMROLEID = @ID);

            insert into dbo.SYSTEMROLEADGROUPSITE (ID, SYSTEMROLEADGROUPID, SITEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                    newid(),
                    IDMAP.NEWID,
                    SYSTEMROLEADGROUPSITE.SITEID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    @IDMAP as IDMAP
                    inner join dbo.SYSTEMROLEADGROUPSITE on SYSTEMROLEADGROUPSITE.SYSTEMROLEADGROUPID = IDMAP.OLDID;

            -- Copy features

            -- Record operations

            insert into dbo.SYSTEMROLEPERM_RECORDOPERATION
                (ID,SYSTEMROLEID,RECORDOPERATIONCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                (select
                    newid(),
                    @ID,
                    RECORDOPERATIONCATALOGID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.SYSTEMROLEPERM_RECORDOPERATION
                where SYSTEMROLEID = @SYSTEMROLEID and RECORDOPERATIONCATALOGID not in (select RECORDOPERATIONCATALOGID from dbo.SYSTEMROLEPERM_RECORDOPERATION where SYSTEMROLEID = @ID));

            -- Forms

            insert into dbo.SYSTEMROLEPERM_DATAFORMINSTANCE
                (ID,SYSTEMROLEID,DATAFORMINSTANCECATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                (select
                    newid(),
                    @ID,
                    DATAFORMINSTANCECATALOGID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.SYSTEMROLEPERM_DATAFORMINSTANCE
                where SYSTEMROLEID = @SYSTEMROLEID and DATAFORMINSTANCECATALOGID not in (select DATAFORMINSTANCECATALOGID from dbo.SYSTEMROLEPERM_DATAFORMINSTANCE where SYSTEMROLEID = @ID));

            -- Datalists

            insert into dbo.SYSTEMROLEPERM_DATALIST
                (ID,SYSTEMROLEID,DATALISTCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                (select
                    newid(),
                    @ID,
                    DATALISTCATALOGID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.SYSTEMROLEPERM_DATALIST
                where SYSTEMROLEID = @SYSTEMROLEID and DATALISTCATALOGID not in (select DATALISTCATALOGID from dbo.SYSTEMROLEPERM_DATALIST where SYSTEMROLEID = @ID));

            -- Business processes

            insert into dbo.SYSTEMROLEPERM_BUSINESSPROCESS
                (ID,SYSTEMROLEID,BUSINESSPROCESSCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                (select
                    newid(),
                    @ID,
                    BUSINESSPROCESSCATALOGID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.SYSTEMROLEPERM_BUSINESSPROCESS
                where SYSTEMROLEID = @SYSTEMROLEID and BUSINESSPROCESSCATALOGID not in (select BUSINESSPROCESSCATALOGID from dbo.SYSTEMROLEPERM_BUSINESSPROCESS where SYSTEMROLEID = @ID));

            -- Dashboards

            insert into dbo.SYSTEMROLEPERM_DASHBOARD
                (ID,SYSTEMROLEID,DASHBOARDCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                (select
                    newid(),
                    @ID,
                    DASHBOARDCATALOGID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.SYSTEMROLEPERM_DASHBOARD
                where SYSTEMROLEID = @SYSTEMROLEID and DASHBOARDCATALOGID not in (select DASHBOARDCATALOGID from dbo.SYSTEMROLEPERM_DASHBOARD where SYSTEMROLEID = @ID));

            -- Smart queries

            insert into dbo.SYSTEMROLEPERM_SMARTQUERY
                (ID,SYSTEMROLEID,SMARTQUERYCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                (select
                    newid(),
                    @ID,
                    SMARTQUERYCATALOGID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.SYSTEMROLEPERM_SMARTQUERY
                where SYSTEMROLEID = @SYSTEMROLEID and SMARTQUERYCATALOGID not in (select SMARTQUERYCATALOGID from dbo.SYSTEMROLEPERM_SMARTQUERY where SYSTEMROLEID = @ID));

            -- KPIs

            insert into dbo.SYSTEMROLEPERM_KPI
                (ID,SYSTEMROLEID,KPICATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                (select
                    newid(),
                    @ID,
                    KPICATALOGID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.SYSTEMROLEPERM_KPI
                where SYSTEMROLEID = @SYSTEMROLEID and KPICATALOGID not in (select KPICATALOGID from dbo.SYSTEMROLEPERM_KPI where SYSTEMROLEID = @ID));

            -- Query views

            insert into dbo.SYSTEMROLEPERM_QUERYVIEW
                (ID,SYSTEMROLEID,QUERYVIEWCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                (select
                    newid(),
                    @ID,
                    QUERYVIEWCATALOGID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.SYSTEMROLEPERM_QUERYVIEW
                where SYSTEMROLEID = @SYSTEMROLEID and QUERYVIEWCATALOGID not in (select QUERYVIEWCATALOGID from dbo.SYSTEMROLEPERM_QUERYVIEW where SYSTEMROLEID = @ID));

            --JamesWill 04/21/2008 CR297382-040708 Search lists

            insert into dbo.SYSTEMROLEPERM_SEARCHLIST
                (ID, SYSTEMROLEID, SEARCHLISTCATALOGID, GRANTORDENY, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                (select
                    newid(),
                    @ID,
                    SEARCHLISTCATALOGID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.SYSTEMROLEPERM_SEARCHLIST
                where SYSTEMROLEID = @SYSTEMROLEID and SEARCHLISTCATALOGID not in (select SEARCHLISTCATALOGID from dbo.SYSTEMROLEPERM_SEARCHLIST where SYSTEMROLEID = @ID));

            --JamesWill 04/21/2008 CR297384-040708  Report params

            insert into dbo.SYSTEMROLEPERM_REPORT
                (ID, SYSTEMROLEID, REPORTCATALOGID, GRANTORDENY, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                (select
                    newid(),
                    @ID,
                    REPORTCATALOGID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.SYSTEMROLEPERM_REPORT
                where SYSTEMROLEID = @SYSTEMROLEID and REPORTCATALOGID not in (select REPORTCATALOGID from dbo.SYSTEMROLEPERM_REPORT where SYSTEMROLEID = @ID));

            -- Code tables

            insert into dbo.SYSTEMROLEPERM_CODETABLE
                (ID,SYSTEMROLEID,CODETABLECATALOGID,ADDPERMISSIONCODE,UPDATEPERMISSIONCODE,DELETEPERMISSIONCODE,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                (select
                    newid(),
                    @ID,
                    CODETABLECATALOGID,
                    ADDPERMISSIONCODE,
                    UPDATEPERMISSIONCODE,
                    DELETEPERMISSIONCODE,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.SYSTEMROLEPERM_CODETABLE
                where SYSTEMROLEID = @SYSTEMROLEID and CODETABLECATALOGID not in (select CODETABLECATALOGID from dbo.SYSTEMROLEPERM_CODETABLE where SYSTEMROLEID = @ID));

            -- Batch types

            insert into dbo.SYSTEMROLEPERM_BATCHTYPE
                (ID,SYSTEMROLEID,BATCHTYPECATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                (select
                    newid(),
                    @ID,
                    BATCHTYPECATALOGID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.SYSTEMROLEPERM_BATCHTYPE
                where SYSTEMROLEID = @SYSTEMROLEID and BATCHTYPECATALOGID not in (select BATCHTYPECATALOGID from dbo.SYSTEMROLEPERM_BATCHTYPE where SYSTEMROLEID = @ID));

            -- KPI instances

            insert into dbo.SYSTEMROLEKPIINSTANCE
                (ID,SYSTEMROLEID,KPIINSTANCEID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                (select
                    newid(),
                    @ID,
                    KPIINSTANCEID,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.SYSTEMROLEKPIINSTANCE
                where SYSTEMROLEID = @SYSTEMROLEID and KPIINSTANCEID not in (select KPIINSTANCEID from dbo.SYSTEMROLEKPIINSTANCE where SYSTEMROLEID = @ID));

            -- Code table entries

            insert into dbo.SYSTEMROLEPERM_CODETABLEENTRY
                (ID,SYSTEMROLEID,CODETABLECATALOGID,CODETABLEENTRYID,ISDENIED,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                (select
                    newid(),
                    @ID,
                    CODETABLECATALOGID,
                    CODETABLEENTRYID,
                    ISDENIED,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from
                    dbo.SYSTEMROLEPERM_CODETABLEENTRY
                where SYSTEMROLEID = @SYSTEMROLEID and CODETABLEENTRYID not in (select CODETABLEENTRYID from dbo.SYSTEMROLEPERM_CODETABLEENTRY where SYSTEMROLEID = @ID));

            -- Feature Permissions

            insert into dbo.SYSTEMROLEPERM_FEATUREGROUP
                (ID,SYSTEMROLEID,FEATUREGROUPCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                (select
                    newid(),
                    @ID,
                    FEATUREGROUPCATALOGID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                 from
                    dbo.SYSTEMROLEPERM_FEATUREGROUP
                 where SYSTEMROLEID = @SYSTEMROLEID and FEATUREGROUPCATALOGID not in (select FEATUREGROUPCATALOGID from dbo.SYSTEMROLEPERM_FEATUREGROUP where SYSTEMROLEID = @ID));

            -- System Privileges (JamesWill WI358431)

            insert into dbo.SYSTEMROLEPERM_SYSTEMPRIVILEGE
                (ID,SYSTEMROLEID,SYSTEMPRIVILEGECATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                (select
                    newid(),
                    @ID,
                    SYSTEMPRIVILEGECATALOGID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                 from
                    dbo.SYSTEMROLEPERM_SYSTEMPRIVILEGE
                 where SYSTEMROLEID = @SYSTEMROLEID and SYSTEMPRIVILEGECATALOGID not in (select SYSTEMPRIVILEGECATALOGID from dbo.SYSTEMROLEPERM_SYSTEMPRIVILEGE where SYSTEMROLEID = @ID));

            -- Map Entities (JamesWill WI358431)

            insert into dbo.SYSTEMROLEPERM_MAPENTITY
                (ID,SYSTEMROLEID,MAPENTITYCATALOGID,GRANTORDENY,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                (select
                    newid(),
                    @ID,
                    MAPENTITYCATALOGID,
                    GRANTORDENY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                 from
                    dbo.SYSTEMROLEPERM_MAPENTITY
                 where SYSTEMROLEID = @SYSTEMROLEID and MAPENTITYCATALOGID not in (select MAPENTITYCATALOGID from dbo.SYSTEMROLEPERM_MAPENTITY where SYSTEMROLEID = @ID));

            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch

            return 0;