USP_CLIENTROLE_REFRESH

Executes the "Client Role Refresh" record operation.
Parameters

Parameter	Parameter Type	Mode	Description
@ID	int	IN	Input parameter indicating the ID of the record being updated.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the update.
Definition

Copy


create procedure dbo.USP_CLIENTROLE_REFRESH
(
    @ID int,
    @CHANGEAGENTID uniqueidentifier
)
with execute as owner
as begin

    --Find selection ID for this role

    declare @QUERYID int
    declare @BBSYSTEM int
    select @QUERYID = CR.REQueryID, @BBSYSTEM = CR.BBSystem from dbo.ClientRoles CR where CR.ID = @ID

    --Look up info about this selection

    declare @SELECTIONINFO table(DBOBJECTNAME nvarchar(255), OBJECTTYPE tinyint)
    insert into @SELECTIONINFO
    exec dbo.USP_BBNC_GETSELECTIONLISTINFO @QUERYID

    --Extract info about selection

    declare @DBOBJECTNAME nvarchar(255)
    declare @OBJECTTYPE int
    select @DBOBJECTNAME = DBOBJECTNAME, @OBJECTTYPE = OBJECTTYPE from @SELECTIONINFO

    --Build SQL for upating roles based on the selection

    if @OBJECTTYPE = 1
        set @DBOBJECTNAME = @DBOBJECTNAME + '()'

    declare @SQLSTRING nvarchar(4000)


    set @SQLSTRING  = ' declare @USER table(ID int)'
    set @SQLSTRING += ' insert into @USER'
    set @SQLSTRING += ' select VWBOU.USERID'
    set @SQLSTRING += ' from dbo.' + @DBOBJECTNAME + ' as R'
    set @SQLSTRING += ' inner join dbo.CONSTITUENT C on C.ID = R.ID'
    set @SQLSTRING += ' inner join vwBackOfficeLinkedUsers VWBOU on VWBOU.BackOfficeRecordID = C.SEQUENCEID'
    set @SQLSTRING += ' where VWBOU.BackOfficeSystemID = ' + CAST(@BBSYSTEM as nvarchar(10))

    set @SQLSTRING += ' begin transaction'

    --Delete existing role-based users

    set @SQLSTRING += ' delete from dbo.USERROLES where CLIENTROLESID = ' +  CAST(@ID as nvarchar(10)) + ' and MANUALLYADDED = 0'

    --Insert the new role-based users

    set @SQLSTRING += ' insert into dbo.USERROLES (CLIENTUSERSID, CLIENTROLESID, MANUALLYADDED) '
    set @SQLSTRING += ' select ID, ' + CAST(@ID as nvarchar(10)) + ', 0'
    set @SQLSTRING += ' from @USER'                

    --Get Update Date

    set @SQLSTRING += ' DECLARE @UPDATEDATE datetime'
    set @SQLSTRING += ' SET @UPDATEDATE = getutcdate()'

    --Update client user's Membership Refreshed On date

    set @SQLSTRING += ' update dbo.CLIENTUSERS'
    set @SQLSTRING += ' set MEMBERSHIPREFRESHEDON = @UPDATEDATE'
    set @SQLSTRING += ' from dbo.CLIENTUSERS'
    set @SQLSTRING += ' inner join dbo.USERROLES UR on UR.CLIENTUSERSID = CLIENTUSERS.ID'
    set @SQLSTRING += ' where UR.ClientRolesID = ' + CAST(@ID as nvarchar(10))

    --Update roles Refreshed Date

    set @SQLSTRING += ' update dbo.CLIENTROLES set REFRESHEDDATE  = @UPDATEDATE'
    set @SQLSTRING += ' where ID = ' + CAST(@ID as nvarchar(10))

    set @SQLSTRING += ' commit transaction'
    execute(@SQLSTRING)

end