USP_BBNC_GETORCREATEACCOUNT

Gets or create an account for use in committing donation transactions from Blackbaud Internet Solutions.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CONSTITUENTID	uniqueidentifier	IN
@FINANCIALINSTITUTIONID	uniqueidentifier	IN
@ACCOUNTTYPECODE	tinyint	IN
@ACCOUNTNUMBER	nvarchar(50)	IN
@ACCOUNTNAME	nvarchar(100)	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN

Definition

Copy



            CREATE procedure dbo.USP_BBNC_GETORCREATEACCOUNT
            (
                @ID uniqueidentifier = null output,
                @CONSTITUENTID uniqueidentifier,
                @FINANCIALINSTITUTIONID uniqueidentifier,
                @ACCOUNTTYPECODE tinyint,
                @ACCOUNTNUMBER nvarchar(50),
        @ACCOUNTNAME nvarchar(100),
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null
            )
            as
            set nocount on;

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

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

      -- Open the symmetric key for decryption

                        exec dbo.USP_GET_KEY_ACCESS;

            begin try
                set @ID = null;

                select top 1 
                    @ID = ID 
                from dbo.CONSTITUENTACCOUNT
                where CONSTITUENTID = @CONSTITUENTID
                    and FINANCIALINSTITUTIONID = @FINANCIALINSTITUTIONID
                    and coalesce(convert(nvarchar(50), DecryptByKey(ACCOUNTNUMBER)),'') = @ACCOUNTNUMBER
                    and ACCOUNTTYPECODE = @ACCOUNTTYPECODE
                  and ACCOUNTNAME = @ACCOUNTNAME

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

                    insert into dbo.CONSTITUENTACCOUNT(ID, CONSTITUENTID, FINANCIALINSTITUTIONID, ACCOUNTNUMBER, ACCOUNTNUMBERINDEX, ACCOUNTTYPECODE, EFTSTATUSCODE, ACCOUNTNAME, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values(@ID, @CONSTITUENTID, @FINANCIALINSTITUTIONID, EncryptByKey(Key_GUID('sym_BBInfinity'), @ACCOUNTNUMBER), dbo.UFN_GET_MAC_FOR_TEXT(@ACCOUNTNUMBER, 'dbo.CONSTITUENTACCOUNT'), @ACCOUNTTYPECODE, 0, @ACCOUNTNAME, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                end                
            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch

            return 0;