USP_REVENUEBATCH_GETORCREATECONSTITUENTACCOUNT

Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTACCOUNTID	uniqueidentifier	INOUT
@CONSTITUENTID	uniqueidentifier	IN
@ACCOUNTNAME	nvarchar(100)	IN
@ACCOUNTNUMBER	nvarchar(max)	IN
@ACCOUNTTYPE	tinyint	IN
@FINANCIALINSTITUTIONID	uniqueidentifier	INOUT
@FINANCIALINSTITUTIONNAME	nvarchar(100)	IN
@BANKINGSYSTEMID	uniqueidentifier	IN
@BRANCHNAME	nvarchar(100)	IN
@ROUTINGNUMBER	nvarchar(9)	IN
@SORTCODE	nvarchar(6)	IN
@BIC	nvarchar(11)	IN
@BANKCODE	nvarchar(25)	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@BATCHID	uniqueidentifier	IN
Definition

Copy


CREATE procedure dbo.USP_REVENUEBATCH_GETORCREATECONSTITUENTACCOUNT
(
    @CONSTITUENTACCOUNTID uniqueidentifier = null output,
    @CONSTITUENTID uniqueidentifier = null,
    @ACCOUNTNAME nvarchar(100) = '',
    @ACCOUNTNUMBER nvarchar(max) = '',
    @ACCOUNTTYPE tinyint = null,
    @FINANCIALINSTITUTIONID uniqueidentifier = null output,
    @FINANCIALINSTITUTIONNAME nvarchar(100) = '',
    @BANKINGSYSTEMID uniqueidentifier = null,
    @BRANCHNAME nvarchar(100) = '',
    @ROUTINGNUMBER nvarchar(9) = '',
    @SORTCODE nvarchar(6) = '',
    @BIC nvarchar(11) = '',
    @BANKCODE nvarchar(25) = '',
    @CURRENTAPPUSERID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @BATCHID uniqueidentifier = null
)
as
begin

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

    declare @CURRENTDATE datetime    
    set @CURRENTDATE = getdate()

    declare @COUNTRYID uniqueidentifier
    exec @COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT

    if @FINANCIALINSTITUTIONID is not null
    begin

        -- Open the symmetric key for decryption

        exec dbo.USP_GET_KEY_ACCESS;

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

        close symmetric key sym_BBInfinity;    
    end
    else if @ROUTINGNUMBER <> '' or @SORTCODE <> '' or @BIC <> '' or @BANKCODE <> ''
    begin
        exec dbo.USP_REVENUEBATCH_GETCONSTITUENTACCOUNT
            @CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID output,
            @FINANCIALINSTITUTIONID = @FINANCIALINSTITUTIONID output,
            @BANKINGSYSTEMID = @BANKINGSYSTEMID output,
            @ROUTINGNUMBER = @ROUTINGNUMBER output,
            @SORTCODE = @SORTCODE output,
            @BIC = @BIC output,
            @BANKCODE = @BANKCODE output,
            @CONSTITUENTID = @CONSTITUENTID,
            @ACCOUNTNUMBER = @ACCOUNTNUMBER,
            @FINANCIALINSTITUTIONNAME = @FINANCIALINSTITUTIONNAME output,
            @BRANCHNAME = @BRANCHNAME
    end

    if @CONSTITUENTACCOUNTID is null
    begin

        if @FINANCIALINSTITUTIONID is null
        begin

            if @ROUTINGNUMBER <> '' or @SORTCODE <> '' or @BIC <> '' or @BANKCODE <> ''
            begin
                set @FINANCIALINSTITUTIONID = newid()

                insert into dbo.BATCHREVENUEFINANCIALINSTITUTION
                (
                    ID, 
                    FINANCIALINSTITUTION, 
                    BRANCHNAME, 
                    ROUTINGNUMBER,
                    SORTCODE,
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED,
                    BANKINGSYSTEMID,
                    BIC,
                    BANKCODE,
                    COUNTRYID
                )
                values
                (
                    @FINANCIALINSTITUTIONID, 
                    @FINANCIALINSTITUTIONNAME, 
                    @BRANCHNAME, 
                    @ROUTINGNUMBER,
                    @SORTCODE,
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CURRENTDATE, 
                    @CURRENTDATE,
                    @BANKINGSYSTEMID,
                    @BIC,
                    @BANKCODE,
                    @COUNTRYID
                )
            end
        end

        --Only add a new account if all of the required values for account are set

        if (@FINANCIALINSTITUTIONID is not null) and (coalesce(@ACCOUNTNUMBER, '') <> '') and @ACCOUNTTYPE is not null
        begin
            exec dbo.USP_REVENUEBATCHCONSTITUENTACCOUNT_ADD
                @ID = @CONSTITUENTACCOUNTID output,
                @CONSTITUENTID = @CONSTITUENTID,
                @FINANCIALINSTITUTIONID = @FINANCIALINSTITUTIONID,
                @CHANGEAGENTID = @CHANGEAGENTID,
                @ACCOUNTNUMBER = @ACCOUNTNUMBER,
                @ACCOUNTTYPE = @ACCOUNTTYPE,
                @EFTSTATUSCODE = 0,
                @ACCOUNTNAME = @ACCOUNTNAME,
                @BATCHID = @BATCHID
        end
        --Otherwise, if any of the fields for a new account are set (but not all of them are set) raise an error

        --since it appears 

        else if (@FINANCIALINSTITUTIONID is not null) or (coalesce(@ACCOUNTNUMBER, '') <> '') or @ACCOUNTTYPE is not null
        begin
            raiserror('BBERR_ALLACCOUNTINFOREQUIRED', 13, 1)
        end
    end

end