USP_DATAFORMTEMPLATE_EDIT_FINANCIALINSTITUTION_5

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@FINANCIALINSTITUTION	nvarchar(100)	IN
@BRANCHNAME	nvarchar(100)	IN
@ROUTINGNUMBER	nvarchar(9)	IN
@ISSPONSORINGINSTITUTION	bit	IN
@ACCOUNTNAME	nvarchar(50)	IN
@ACCOUNTNUMBER	nvarchar(50)	IN
@USERNUMBER	nvarchar(24)	IN
@ADDRESSBLOCK	nvarchar(150)	IN
@CITY	nvarchar(50)	IN
@STATEID	uniqueidentifier	IN
@POSTCODE	nvarchar(12)	IN
@COUNTRYID	uniqueidentifier	IN
@PHONENUMBER	nvarchar(100)	IN
@SORTCODE	nvarchar(6)	IN
@CLIENTNAME	nvarchar(100)	IN
@REJECTIONCODES	xml	IN
@BANKINGSYSTEMID	uniqueidentifier	IN
@BIC	nvarchar(11)	IN
@BANKCODE	nvarchar(25)	IN
Definition

Copy


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

                declare @CURRENTDATE datetime;

                begin try
                    if @CHANGEAGENTID is null  
                        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                    set @CURRENTDATE = getdate();

                    -- Sponsoring institution update (account number encryption)

                    if @ISSPONSORINGINSTITUTION = 1
                    begin
                        -- Open the symmetric key for encryption

                        exec dbo.USP_GET_KEY_ACCESS;

                        update dbo.FINANCIALINSTITUTION
                            set FINANCIALINSTITUTION = @FINANCIALINSTITUTION,
                            BRANCHNAME = @BRANCHNAME,
                            ROUTINGNUMBER = @ROUTINGNUMBER,
                            ISSPONSORINGINSTITUTION = @ISSPONSORINGINSTITUTION,
                            ACCOUNTNAME = @ACCOUNTNAME,
                            ACCOUNTNUMBER = EncryptByKey(Key_GUID('sym_BBInfinity'), @ACCOUNTNUMBER),
                            USERNUMBER = @USERNUMBER,
                            ADDRESSBLOCK = @ADDRESSBLOCK,
                            CITY = @CITY ,
                            STATEID = @STATEID,
                            POSTCODE = @POSTCODE,
                            COUNTRYID = @COUNTRYID,
                            PHONENUMBER = @PHONENUMBER,
                            SORTCODE = @SORTCODE,
                            CLIENTNAME = @CLIENTNAME,
                            DATECHANGED = @CURRENTDATE,
                            CHANGEDBYID = @CHANGEAGENTID,
                            BANKINGSYSTEMID = @BANKINGSYSTEMID,
                            BIC = @BIC,
                            BANKCODE = @BANKCODE
                        where ID = @ID;

                        close symmetric key sym_BBInfinity;
                    end
                    else
                        update dbo.FINANCIALINSTITUTION
                            set FINANCIALINSTITUTION = @FINANCIALINSTITUTION,
                            BRANCHNAME = @BRANCHNAME,
                            ROUTINGNUMBER = @ROUTINGNUMBER,
                            ISSPONSORINGINSTITUTION = @ISSPONSORINGINSTITUTION,
                            ACCOUNTNAME = '',
                            ACCOUNTNUMBER = '',
                            USERNUMBER = '',
                            ADDRESSBLOCK = @ADDRESSBLOCK,
                            CITY = @CITY ,
                            STATEID = @STATEID,
                            POSTCODE = @POSTCODE,
                            COUNTRYID = @COUNTRYID,
                            PHONENUMBER = @PHONENUMBER,
                            SORTCODE = @SORTCODE,
                            DATECHANGED = @CURRENTDATE,
                            CHANGEDBYID = @CHANGEAGENTID,
                            BANKINGSYSTEMID = @BANKINGSYSTEMID,
                            BIC = @BIC,
                            BANKCODE = @BANKCODE
                        where 
                            ID = @ID;

                        exec dbo.USP_FINANCIALINSTITUTION_GETREJECTIONCODES_UPDATEFROMXML @ID, @REJECTIONCODES, @CHANGEAGENTID, @CURRENTDATE;    
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;