USP_DATAFORMTEMPLATE_ADD_SPONSORSHIPBATCHCONSTITUENTACCOUNT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SPONSORSHIPBATCHCONSTITUENTACCOUNT
(
    @ID uniqueidentifier = null output,
    @CURRENTAPPUSERID uniqueidentifier,
    @CONSTITUENTID uniqueidentifier,
    @FINANCIALINSTITUTIONID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,    
    @ACCOUNTNUMBER nvarchar(50) = '',
    @ACCOUNTTYPE tinyint = null,
    @EFTSTATUSCODE tinyint = 0,
    @ACCOUNTNAME nvarchar(100) = ''
)
as

    set nocount on;

    declare @CURRENTDATE datetime;

    -- Open the symmetric key for decryption
    exec dbo.USP_GET_KEY_ACCESS;

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

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

        set @CURRENTDATE = getdate();

        -- Determine if the constituent is a standard constituent or a revenue batch constituent
        if exists(select 1 from dbo.CONSTITUENT where ID = @CONSTITUENTID)
        begin
            -- Check constituent security
            if (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 0) and
                (dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID, '9FD35458-E734-4a26-8D9D-089C0FEDB726', @CONSTITUENTID) = 0)
            begin
                raiserror('BBERR_DB_RECORDSECURITY_PERMISSION_DENIED', 13, 1)
                return 1
            end

            declare @COUNT int
            select @COUNT = count(*)
            from dbo.CONSTITUENTACCOUNT
            where
                coalesce(convert(nvarchar(50), DecryptByKey([CONSTITUENTACCOUNT].ACCOUNTNUMBER)),'') = @ACCOUNTNUMBER and 
                FINANCIALINSTITUTIONID = @FINANCIALINSTITUTIONID and
                CONSTITUENTID = @CONSTITUENTID

            if @COUNT > 0
                raiserror('DUPLICATEACCOUNT', 13, 1)

            insert into dbo.CONSTITUENTACCOUNT 
            (
                ID,
                CONSTITUENTID,
                FINANCIALINSTITUTIONID,
                ACCOUNTNUMBER,
                ACCOUNTNUMBERINDEX,
                ACCOUNTTYPECODE,
                EFTSTATUSCODE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED,
            ACCOUNTNAME
            )
            values
            (
                @ID,
                @CONSTITUENTID,
                @FINANCIALINSTITUTIONID,
                EncryptByKey(Key_GUID('sym_BBInfinity'), @ACCOUNTNUMBER),
                dbo.UFN_GET_MAC_FOR_TEXT(@ACCOUNTNUMBER, 'dbo.CONSTITUENTACCOUNT'),
                @ACCOUNTTYPE,
                @EFTSTATUSCODE,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE,
            @ACCOUNTNAME
            );
        end
        else
        begin
            declare @BCOUNT int
            select @BCOUNT = count(*)
            from dbo.BATCHSPONSORSHIPCONSTITUENTACCOUNT
            where
                coalesce(convert(nvarchar(50), DecryptByKey([BATCHSPONSORSHIPCONSTITUENTACCOUNT].ACCOUNTNUMBER)),'') = @ACCOUNTNUMBER and 
                FINANCIALINSTITUTIONID = @FINANCIALINSTITUTIONID and
                CONSTITUENTID = @CONSTITUENTID

            if @BCOUNT > 0
                raiserror('DUPLICATEACCOUNT', 13, 1)

            insert into dbo.BATCHSPONSORSHIPCONSTITUENTACCOUNT 
            (
                ID,
                CONSTITUENTID,
                FINANCIALINSTITUTIONID,
                ACCOUNTNUMBER,
                ACCOUNTNUMBERINDEX,
                ACCOUNTTYPECODE,
                EFTSTATUSCODE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED,
            ACCOUNTNAME
            )
            values
            (
                @ID,
                @CONSTITUENTID,
                @FINANCIALINSTITUTIONID,
                EncryptByKey(Key_GUID('sym_BBInfinity'), @ACCOUNTNUMBER),                                    
                dbo.UFN_GET_MAC_FOR_TEXT(@ACCOUNTNUMBER, 'dbo.BATCHSPONSORSHIPCONSTITUENTACCOUNT'),
                @ACCOUNTTYPE,
                @EFTSTATUSCODE,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE,
            @ACCOUNTNAME
            );                                
        end
    end try

    begin catch
         exec dbo.USP_RAISE_ERROR
         return 1
    end catch