USP_DATALIST_FINANCIALINSTITUTION

CREATE procedure dbo.USP_DATALIST_FINANCIALINSTITUTION 
as
    set nocount on;

    -- Open the symmetric key for encryption

    exec dbo.USP_GET_KEY_ACCESS;

    select    
            FINANCIALINSTITUTION.ID,
            FINANCIALINSTITUTION.FINANCIALINSTITUTION,
            FINANCIALINSTITUTION.BRANCHNAME,
            FINANCIALINSTITUTION.ROUTINGNUMBER,
            coalesce(convert(nvarchar(50), DecryptByKey(FINANCIALINSTITUTION.ACCOUNTNUMBER)),'') ACCOUNTNUMBER,
            FINANCIALINSTITUTION.SORTCODE,
            (select count(ID) from dbo.FINANCIALINSTITUTIONREJECTIONCODE where FINANCIALINSTITUTIONID = FINANCIALINSTITUTION.ID) NUMBEROFREJECTIONCODES,
            BANKINGSYSTEM.NAME BANKINGSYSTEMNAME,
            BANKINGSYSTEM.CONDITIONSETTINGNAME BANKINGSYSTEMCONDITIONSETTINGNAME,
            FINANCIALINSTITUTION.BIC,
            FINANCIALINSTITUTION.BANKCODE
    from
        dbo.FINANCIALINSTITUTION
        left join dbo.BANKINGSYSTEM on BANKINGSYSTEM.ID = FINANCIALINSTITUTION.BANKINGSYSTEMID
    order by
        FINANCIALINSTITUTION.FINANCIALINSTITUTION;

    close symmetric key sym_BBInfinity;