USP_DATAFORMTEMPLATE_EDITLOAD_FINANCIALINSTITUTION_3

The load procedure used by the edit dataform template "Financial Institution Edit Form 3"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@FINANCIALINSTITUTION	nvarchar(100)	INOUT	Institution name
@BRANCHNAME	nvarchar(100)	INOUT	Branch name
@ROUTINGNUMBER	nvarchar(9)	INOUT	Routing Number
@ISSPONSORINGINSTITUTION	bit	INOUT	Is sponsoring institution
@ACCOUNTNAME	nvarchar(50)	INOUT	Account name
@ACCOUNTNUMBER	nvarchar(50)	INOUT	Account number
@USERNUMBER	nvarchar(24)	INOUT	User number
@ADDRESSBLOCK	nvarchar(150)	INOUT	Address
@CITY	nvarchar(50)	INOUT	City
@STATEID	uniqueidentifier	INOUT	State
@POSTCODE	nvarchar(12)	INOUT	Postal code
@COUNTRYID	uniqueidentifier	INOUT	Country
@PHONENUMBER	nvarchar(100)	INOUT	Phone number
@SORTCODE	nvarchar(6)	INOUT	Sort code
@CLIENTNAME	nvarchar(100)	INOUT	Client name
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@REJECTIONCODES	xml	INOUT	Rejection codes
@BANKINGSYSTEMID	uniqueidentifier	INOUT
@BIC	nvarchar(11)	INOUT
@BANKCODE	nvarchar(25)	INOUT
@FINANCIALINSTITUTIONINUSE	bit	INOUT

Definition

Copy



            CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_FINANCIALINSTITUTION_3
            (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @FINANCIALINSTITUTION nvarchar(100) = null output,
                @BRANCHNAME nvarchar(100) = null output,
                @ROUTINGNUMBER nvarchar(9) = null output,
                @ISSPONSORINGINSTITUTION bit = null output,
                @ACCOUNTNAME nvarchar(50) = null output,
                @ACCOUNTNUMBER nvarchar(50) = null output,
                @USERNUMBER nvarchar(24) = null output,
                @ADDRESSBLOCK nvarchar(150) = null output,
                @CITY nvarchar(50) = null output,
                @STATEID uniqueidentifier = null output,
                @POSTCODE nvarchar(12) =  null output,
                @COUNTRYID uniqueidentifier = null output,
                @PHONENUMBER nvarchar(100) = null output,
                @SORTCODE nvarchar(6) = null output,
                @CLIENTNAME nvarchar(100) = null output,
                @TSLONG bigint = 0 output,
                @REJECTIONCODES xml = null output,
                @BANKINGSYSTEMID uniqueidentifier = null output,
                @BIC nvarchar(11) = null output,
                @BANKCODE nvarchar(25) = null output,
                @FINANCIALINSTITUTIONINUSE bit = null output
            )
            as
                set nocount on;

                set @DATALOADED = 0;
                set @TSLONG = 0;

                -- Open the symmetric key for decryption

                exec dbo.USP_GET_KEY_ACCESS;

                select
                    @DATALOADED = 1,
                    @FINANCIALINSTITUTION = FINANCIALINSTITUTION.FINANCIALINSTITUTION,
                    @BRANCHNAME = FINANCIALINSTITUTION.BRANCHNAME,
                    @ROUTINGNUMBER = FINANCIALINSTITUTION.ROUTINGNUMBER,
                    @ISSPONSORINGINSTITUTION = FINANCIALINSTITUTION.ISSPONSORINGINSTITUTION,
                    @ACCOUNTNAME = FINANCIALINSTITUTION.ACCOUNTNAME,
                    @ACCOUNTNUMBER = coalesce(convert(nvarchar(50), DecryptByKey(FINANCIALINSTITUTION.ACCOUNTNUMBER)),''),
                    @USERNUMBER = FINANCIALINSTITUTION.USERNUMBER,
                    @ADDRESSBLOCK = FINANCIALINSTITUTION.ADDRESSBLOCK,
                    @CITY = FINANCIALINSTITUTION.CITY,
                    @STATEID = FINANCIALINSTITUTION.STATEID,
                    @POSTCODE = FINANCIALINSTITUTION.POSTCODE,
                    @COUNTRYID = FINANCIALINSTITUTION.COUNTRYID,
                    @PHONENUMBER = FINANCIALINSTITUTION.PHONENUMBER,
                    @SORTCODE = FINANCIALINSTITUTION.SORTCODE,
                    @CLIENTNAME = FINANCIALINSTITUTION.CLIENTNAME,
                    @TSLONG = FINANCIALINSTITUTION.TSLONG,
                    @BANKINGSYSTEMID = FINANCIALINSTITUTION.BANKINGSYSTEMID,
                    @BIC = FINANCIALINSTITUTION.BIC,
                    @BANKCODE = FINANCIALINSTITUTION.BANKCODE
                from dbo.FINANCIALINSTITUTION
                where ID = @ID;

                set @FINANCIALINSTITUTIONINUSE = 0;
                if exists (select 1 from dbo.FINANCIALINSTITUTION inner join dbo.CONSTITUENTACCOUNT on CONSTITUENTACCOUNT.FINANCIALINSTITUTIONID = FINANCIALINSTITUTION.ID where FINANCIALINSTITUTION.ID = @ID)
                    select @FINANCIALINSTITUTIONINUSE = 1;

                if @BANKINGSYSTEMID is null
                    exec dbo.USP_BANKINGSYSTEM_GETDEFAULT @DEFAULTBANKINGSYSTEMID = @BANKINGSYSTEMID output;

                close symmetric key sym_BBInfinity;

                set @REJECTIONCODES = dbo.UFN_FINANCIALINSTITUTION_GETREJECTIONCODES_TOITEMLISTXML(@ID);

                return 0;