USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTACCOUNT_3

The load procedure used by the edit dataform template "Constituent Financial Account 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.
@CONSTITUENTID	uniqueidentifier	INOUT	ConsitutentID
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@FINANCIALINSTITUTIONID	uniqueidentifier	INOUT	Financial institution
@FINANCIALINSTITUTIONBRANCHNAME	nvarchar(100)	INOUT	Branch
@FINANCIALINSTITUTIONROUTINGNUMBER	nvarchar(9)	INOUT	Routing number
@FINANCIALINSTITUTIONSORTCODE	nvarchar(8)	INOUT	Sort code
@ACCOUNTNUMBER	nvarchar(50)	INOUT	Account number
@ACCOUNTTYPE	tinyint	INOUT	Account type
@EFTSTATUSCODE	tinyint	INOUT	EFT status
@ACCOUNTNAME	nvarchar(100)	INOUT	Name on account
@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.
@FINANCIALINSTITUTIONBANKINGSYSTEMID	uniqueidentifier	INOUT
@FINANCIALINSTITUTIONBIC	nvarchar(11)	INOUT
@FINANCIALINSTITUTIONBANKCODE	nvarchar(25)	INOUT

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTACCOUNT_3
                    (
                    @ID uniqueidentifier,
                    @CONSTITUENTID uniqueidentifier = null output,
                    @DATALOADED bit = 0 output,
                    @FINANCIALINSTITUTIONID uniqueidentifier = null output,
                    @FINANCIALINSTITUTIONBRANCHNAME nvarchar(100) = null output,
                    @FINANCIALINSTITUTIONROUTINGNUMBER nvarchar(9) = null output,
                    @FINANCIALINSTITUTIONSORTCODE nvarchar(8) = null output,
                    @ACCOUNTNUMBER nvarchar(50) = null output,
                    @ACCOUNTTYPE tinyint = null output,
                    @EFTSTATUSCODE tinyint = null output,
                    @ACCOUNTNAME nvarchar(100) = null output,
                    @TSLONG bigint = 0 output,
                    @FINANCIALINSTITUTIONBANKINGSYSTEMID uniqueidentifier = null output,
                    @FINANCIALINSTITUTIONBIC nvarchar(11) = null output,
                    @FINANCIALINSTITUTIONBANKCODE nvarchar(25) = null output
                    )
                    as
                        set nocount on;

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


                        begin try
              --open the symmetric key for decryption

                          exec dbo.USP_GET_KEY_ACCESS;
                            select 
                                @CONSTITUENTID = [CA].CONSTITUENTID,
                                @DATALOADED = 1,
                                @FINANCIALINSTITUTIONID = [FI].ID,
                                @FINANCIALINSTITUTIONBRANCHNAME = [FI].BRANCHNAME,
                                @FINANCIALINSTITUTIONROUTINGNUMBER = [FI].ROUTINGNUMBER,
                                @FINANCIALINSTITUTIONSORTCODE = [FI].SORTCODE,
                                @ACCOUNTNUMBER = coalesce(convert(nvarchar(50), DecryptByKey([CA].ACCOUNTNUMBER)),''),
                                @ACCOUNTTYPE = [CA].ACCOUNTTYPECODE,
                                @EFTSTATUSCODE = [CA].EFTSTATUSCODE,
                                @ACCOUNTNAME = [CA].ACCOUNTNAME,
                                @TSLONG = [CA].TSLONG,
                                @FINANCIALINSTITUTIONBANKINGSYSTEMID = [FI].BANKINGSYSTEMID,
                                @FINANCIALINSTITUTIONBIC = [FI].BIC,
                                @FINANCIALINSTITUTIONBANKCODE = [FI].BANKCODE
                            from
                                dbo.CONSTITUENTACCOUNT [CA]
                            left join
                                dbo.FINANCIALINSTITUTION [FI]
                            on
                                [FI].ID = [CA].FINANCIALINSTITUTIONID
                            where
                                [CA].ID = @ID;

                            exec dbo.USP_CLOSE_KEY_ACCESS;
                        end try        
                        begin catch
              exec dbo.USP_CLOSE_KEY_ACCESS;
                            exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch

                        return 0;