USP_REVENUEBATCH_GETCONSTITUENTACCOUNT

CREATE procedure dbo.USP_REVENUEBATCH_GETCONSTITUENTACCOUNT
(
    @CONSTITUENTACCOUNTID uniqueidentifier = null output,
    @FINANCIALINSTITUTIONID uniqueidentifier = null output,
    @BANKINGSYSTEMID uniqueidentifier = null output,
    @ROUTINGNUMBER nvarchar(9) = '' output,
    @SORTCODE nvarchar(6) = '' output,
    @BIC nvarchar(11) = '' output,
    @BANKCODE nvarchar(25) = '' output,
    @CONSTITUENTID uniqueidentifier = null,
    @ACCOUNTNUMBER nvarchar(max) = '',
    @FINANCIALINSTITUTIONNAME nvarchar(100) = '' output,
    @BRANCHNAME nvarchar(100) = ''
)
as
begin
    -- Open the symmetric key for decryption

    exec dbo.USP_GET_KEY_ACCESS;

    if @BANKINGSYSTEMID is null
    begin
        exec dbo.USP_BANKINGSYSTEM_GETDEFAULT @DEFAULTBANKINGSYSTEMID = @BANKINGSYSTEMID output
        if @BANKINGSYSTEMID is null
        begin
            if @ROUTINGNUMBER <> '' and @SORTCODE = '' and @BIC = '' and @BANKCODE = ''
            begin
                select
                    @BANKINGSYSTEMID = BANKINGSYSTEM.ID
                from
                    dbo.BANKINGSYSTEM
                where
                    BANKINGSYSTEM.CONDITIONSETTINGNAME = 'BankingSystem-ACH'
            end
            if @ROUTINGNUMBER  = '' and @SORTCODE <> '' and @BIC = '' and @BANKCODE = ''
            begin
                select
                    @BANKINGSYSTEMID = BANKINGSYSTEM.ID
                from
                    dbo.BANKINGSYSTEM
                where
                    BANKINGSYSTEM.CONDITIONSETTINGNAME = 'BankingSystem-BACS'            
            end
            if @ROUTINGNUMBER = '' and @SORTCODE = '' and @BIC <> '' and @BANKCODE = ''
            begin
                select
                    @BANKINGSYSTEMID = BANKINGSYSTEM.ID
                from
                    dbo.BANKINGSYSTEM
                where
                    BANKINGSYSTEM.CONDITIONSETTINGNAME = 'BankingSystem-SEPA'            
            end
            if @ROUTINGNUMBER = '' and @SORTCODE = '' and @BIC = '' and @BANKCODE <> ''
            begin
                select
                    @BANKINGSYSTEMID = BANKINGSYSTEM.ID
                from
                    dbo.BANKINGSYSTEM
                where
                    BANKINGSYSTEM.CONDITIONSETTINGNAME = 'BankingSystem-Other'            
            end
        end
    end

    --This is the value from one of the four columns {Routing number, Sort code, BIC, or Bank code} dependent on the banking system id being used

    --It is used later to create a financial institution name when a name is not specified

    declare @BANKNUMBER nvarchar(25)
    declare @CONDITIONSETTINGNAME nvarchar(50)
    select
        @CONDITIONSETTINGNAME = BANKINGSYSTEM.CONDITIONSETTINGNAME
    from
        BANKINGSYSTEM
    where
        BANKINGSYSTEM.ID = @BANKINGSYSTEMID

    declare @FINANCIALINSTITUTIONS table
    (
        FINANCIALINSTITUTIONID uniqueidentifier,
        FINANCIALINSTITUTIONNAME nvarchar(100),
        BRANCHNAME nvarchar(100)
    )

    if @CONDITIONSETTINGNAME = 'BankingSystem-ACH'
    begin
        set @BANKNUMBER = @ROUTINGNUMBER
        if @ROUTINGNUMBER = ''
            raiserror('BBERR_ROUTINGNUMBERREQUIRED', 13, 1)
        set @SORTCODE = ''
        set @BIC = ''
        set @BANKCODE = ''
        insert into @FINANCIALINSTITUTIONS (FINANCIALINSTITUTIONID, FINANCIALINSTITUTIONNAME, BRANCHNAME)
        select
            FINANCIALINSTITUTION.ID,
            FINANCIALINSTITUTION.FINANCIALINSTITUTION,
            FINANCIALINSTITUTION.BRANCHNAME
        from
            FINANCIALINSTITUTION
        where
            FINANCIALINSTITUTION.BANKINGSYSTEMID = @BANKINGSYSTEMID
            and FINANCIALINSTITUTION.ROUTINGNUMBER = @ROUTINGNUMBER
    end

    else if @CONDITIONSETTINGNAME = 'BankingSystem-BACS'
    begin   
        set @BANKNUMBER = @SORTCODE
        if @SORTCODE = ''
            raiserror('BBERR_SORTCODEREQUIRED', 13, 1)
        set @ROUTINGNUMBER = ''
        set @BIC = ''
        set @BANKCODE = ''
        insert into @FINANCIALINSTITUTIONS (FINANCIALINSTITUTIONID, FINANCIALINSTITUTIONNAME, BRANCHNAME)
        select
            FINANCIALINSTITUTION.ID,
            FINANCIALINSTITUTION.FINANCIALINSTITUTION,
            FINANCIALINSTITUTION.BRANCHNAME
        from
            FINANCIALINSTITUTION
        where
            FINANCIALINSTITUTION.BANKINGSYSTEMID = @BANKINGSYSTEMID
            and FINANCIALINSTITUTION.SORTCODE = @SORTCODE
    end

    else if @CONDITIONSETTINGNAME = 'BankingSystem-SEPA'
    begin
        set @BANKNUMBER = @BIC
        if @BIC = ''
            raiserror('BBERR_BICREQUIRED', 13, 1)
        set @ROUTINGNUMBER = ''
        set @SORTCODE = ''
        set @BANKCODE = ''
        insert into @FINANCIALINSTITUTIONS (FINANCIALINSTITUTIONID, FINANCIALINSTITUTIONNAME, BRANCHNAME)
        select
            FINANCIALINSTITUTION.ID,
            FINANCIALINSTITUTION.FINANCIALINSTITUTION,
            FINANCIALINSTITUTION.BRANCHNAME
        from
            FINANCIALINSTITUTION
        where
            FINANCIALINSTITUTION.BANKINGSYSTEMID = @BANKINGSYSTEMID
            and FINANCIALINSTITUTION.BIC = @BIC
    end

    else if @CONDITIONSETTINGNAME = 'BankingSystem-Other'
    begin
        set @BANKNUMBER = @BANKCODE
        if @BANKCODE = ''
            raiserror('BBERR_BANKCODEREQUIRED', 13, 1)
        set @ROUTINGNUMBER = ''
        set @SORTCODE = ''
        set @BIC = ''
        insert into @FINANCIALINSTITUTIONS (FINANCIALINSTITUTIONID, FINANCIALINSTITUTIONNAME, BRANCHNAME)
        select
            FINANCIALINSTITUTION.ID,
            FINANCIALINSTITUTION.FINANCIALINSTITUTION,
            FINANCIALINSTITUTION.BRANCHNAME
        from
            FINANCIALINSTITUTION
        where
            FINANCIALINSTITUTION.BANKINGSYSTEMID = @BANKINGSYSTEMID
            and FINANCIALINSTITUTION.BANKCODE = @BANKCODE
    end

    else if coalesce(@CONDITIONSETTINGNAME, '') = ''
        raiserror('BBERR_BANKINGSYSTEM_INVALIDCONDITIONSETTINGNAME', 13, 1)

    if (
        select
            count(CONSTITUENTACCOUNT.ID)
        from
            dbo.CONSTITUENTACCOUNT
            inner join @FINANCIALINSTITUTIONS as FINANCIALINSTITUTIONS on CONSTITUENTACCOUNT.FINANCIALINSTITUTIONID = FINANCIALINSTITUTIONS.FINANCIALINSTITUTIONID
        where
            CONSTITUENTACCOUNT.CONSTITUENTID = @CONSTITUENTID
            and coalesce(convert(nvarchar(50), DecryptByKey(CONSTITUENTACCOUNT.ACCOUNTNUMBER)),'') = @ACCOUNTNUMBER
        ) = 1
    begin
        select
            @CONSTITUENTACCOUNTID = CONSTITUENTACCOUNT.ID
        from
            dbo.CONSTITUENTACCOUNT
            inner join @FINANCIALINSTITUTIONS as FINANCIALINSTITUTIONS on CONSTITUENTACCOUNT.FINANCIALINSTITUTIONID = FINANCIALINSTITUTIONS.FINANCIALINSTITUTIONID
        where
            CONSTITUENTACCOUNT.CONSTITUENTID = @CONSTITUENTID
            and coalesce(convert(nvarchar(50), DecryptByKey(CONSTITUENTACCOUNT.ACCOUNTNUMBER)),'') = @ACCOUNTNUMBER
    end
    else
    begin
        declare @NUMBEROFFINANCIALINSTITUTIONS int
        select
            @NUMBEROFFINANCIALINSTITUTIONS = count(FINANCIALINSTITUTIONS.FINANCIALINSTITUTIONID)
        from
            @FINANCIALINSTITUTIONS as FINANCIALINSTITUTIONS

        if @NUMBEROFFINANCIALINSTITUTIONS > 1
        begin
            if @FINANCIALINSTITUTIONNAME = ''
                set @FINANCIALINSTITUTIONNAME = @BANKNUMBER

            select
                @FINANCIALINSTITUTIONID = FINANCIALINSTITUTIONS.FINANCIALINSTITUTIONID
            from
                @FINANCIALINSTITUTIONS as FINANCIALINSTITUTIONS
            where
                FINANCIALINSTITUTIONS.FINANCIALINSTITUTIONNAME = @FINANCIALINSTITUTIONNAME
                and FINANCIALINSTITUTIONS.BRANCHNAME = @BRANCHNAME
        end
        else if @NUMBEROFFINANCIALINSTITUTIONS = 1
        begin
            select
                @FINANCIALINSTITUTIONID = FINANCIALINSTITUTIONS.FINANCIALINSTITUTIONID
            from
                @FINANCIALINSTITUTIONS as FINANCIALINSTITUTIONS
        end
        else
        begin
            if @FINANCIALINSTITUTIONNAME = ''
                set @FINANCIALINSTITUTIONNAME = @BANKNUMBER
        end
    end
end