USP_BBNC_INTEGRATIONCLIENTUSERADDUPDATE

Adds a constituent's Blackbaud Internet Solutions user information from the Blackbaud Internet Solutions integration service.
Parameters

Parameter	Parameter Type	Mode
@BBNCUSERID	int	IN
@CONSTITUENTMAPID	int	IN
@USERNAME	nvarchar(50)	IN
@USERADDEDDATE	datetime	IN
@LASTLOGINDATE	datetime	IN
@ACTIVE	bit	IN
@DELETED	bit	IN
@USERUPDATEDATE	datetime	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN
Definition

Copy


            CREATE procedure dbo.USP_BBNC_INTEGRATIONCLIENTUSERADDUPDATE
            (
                @BBNCUSERID int,
                @CONSTITUENTMAPID int,
                @USERNAME nvarchar(50),
                @USERADDEDDATE datetime,
                @LASTLOGINDATE datetime,
                @ACTIVE bit,
                @DELETED bit,
                @USERUPDATEDATE datetime,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as
                set nocount on;

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

                if @CHANGEDATE is null
                    set @CHANGEDATE  = getdate();

                begin try
                    if not (@BBNCUSERID > 0)
                        raiserror('ERR_BBNCUSERID_REQUIRED',16,1);

                    declare @CONSTITUENTID uniqueidentifier;
                    select
                        @CONSTITUENTID = [CONSTITUENT].[ID]
                    from
                        dbo.CONSTITUENT
                    where
                        [CONSTITUENT].[SEQUENCEID] = @CONSTITUENTMAPID;

                    if @ACTIVE = 1 and @DELETED = 0
                        update dbo.CONSTITUENT set
                            [NETCOMMUNITYMEMBER] = 1,
                            [ISCONSTITUENT] = 1,
                            [CHANGEDBYID] = @CHANGEAGENTID,
                            [DATECHANGED] = @CHANGEDATE
                        where
                            [CONSTITUENT].[ID] = @CONSTITUENTID
                            and [CONSTITUENT].[NETCOMMUNITYMEMBER] = 0;
                    else
                        update dbo.CONSTITUENT set
                            [NETCOMMUNITYMEMBER] = 0,
                            [CHANGEDBYID] = @CHANGEAGENTID,
                            [DATECHANGED] = @CHANGEDATE
                        where
                            [CONSTITUENT].[ID] = @CONSTITUENTID
                            and [CONSTITUENT].[NETCOMMUNITYMEMBER] = 1;

                    if exists
                    (
                        select
                            [ID]
                        from
                            dbo.NETCOMMUNITYCLIENTUSER
                        where
                            @BBNCUSERID = [BBNCUSERID]
                    )
                        update dbo.NETCOMMUNITYCLIENTUSER
                        set
                            [CONSTITUENTID] = @CONSTITUENTID,
                            [USERNAME] = coalesce(@USERNAME, ''),
                            [USERADDEDDATE] = @USERADDEDDATE,
                            [LASTLOGINDATE] = @LASTLOGINDATE,
                            [ACTIVE] = coalesce(@ACTIVE, 0),
                            [DELETED] = coalesce(@DELETED, 0),
                            [USERUPDATEDATE] = @USERUPDATEDATE,
                            [CHANGEDBYID] = @CHANGEAGENTID,
                            [DATECHANGED] = @CHANGEDATE
                        where
                            [BBNCUSERID] = @BBNCUSERID
                            and
                            (
                                coalesce([CONSTITUENTID], '00000000-0000-0000-0000-000000000000') <> coalesce(@CONSTITUENTID, '00000000-0000-0000-0000-000000000000')
                                or [USERNAME] <> coalesce(@USERNAME, '')
                                or ([USERADDEDDATE] <> @USERADDEDDATE or ([USERADDEDDATE] is null and @USERADDEDDATE is not null) or ([USERADDEDDATE] is not null and @USERADDEDDATE is null))
                                or ([LASTLOGINDATE] <> @LASTLOGINDATE or ([LASTLOGINDATE] is null and @LASTLOGINDATE is not null) or ([LASTLOGINDATE] is not null and @LASTLOGINDATE is null))
                                or [ACTIVE] <> coalesce(@ACTIVE, 0)
                                or [DELETED] <> coalesce(@DELETED, 0)
                                or ([USERUPDATEDATE] <> @USERUPDATEDATE or ([USERUPDATEDATE] is null and @USERUPDATEDATE is not null) or ([USERUPDATEDATE] is not null and @USERUPDATEDATE is null))
                            );
                    else
                        insert into dbo.NETCOMMUNITYCLIENTUSER
                        (
                            [BBNCUSERID],
                            [CONSTITUENTID],
                            [USERNAME],
                            [USERADDEDDATE],
                            [LASTLOGINDATE],
                            [ACTIVE],
                            [DELETED],
                            [USERUPDATEDATE],
                            [ADDEDBYID],
                            [CHANGEDBYID],
                            [DATEADDED],
                            [DATECHANGED]
                        )
                        values
                        (
                            @BBNCUSERID,
                            @CONSTITUENTID,
                            coalesce(@USERNAME, ''),
                            @USERADDEDDATE,
                            @LASTLOGINDATE,
                            coalesce(@ACTIVE, 0),
                            coalesce(@DELETED, 0),
                            @USERUPDATEDATE,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CHANGEDATE,
                            @CHANGEDATE
                        );
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;