USP_DATAFORMTEMPLATE_ADD_FINANCIALINSTITUTION

The save procedure used by the add dataform template "Financial Institution Add Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@FINANCIALINSTITUTION	nvarchar(100)	IN	Financial institution
@BRANCHNAME	nvarchar(100)	IN	Branch
@ROUTINGNUMBER	nvarchar(9)	IN	Routing Number
@ISSPONSORINGINSTITUTION	bit	IN	Is sponsoring institution
@ACCOUNTNAME	nvarchar(50)	IN	Account name
@ACCOUNTNUMBER	nvarchar(50)	IN	Account number
@USERNUMBER	nvarchar(24)	IN	User number
@ADDRESSBLOCK	nvarchar(150)	IN	Address
@CITY	nvarchar(50)	IN	City
@STATEID	uniqueidentifier	IN	State
@POSTCODE	nvarchar(12)	IN	Zip
@COUNTRYID	uniqueidentifier	IN	Country
@PHONENUMBER	nvarchar(100)	IN	Phone number
@SORTCODE	nvarchar(6)	IN	Sort code
@CLIENTNAME	nvarchar(100)	IN	Client name
@REJECTIONCODES	xml	IN	Rejection codes
@BANKINGSYSTEMID	uniqueidentifier	IN
@BIC	nvarchar(11)	IN
@BANKCODE	nvarchar(25)	IN
Definition

Copy


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

                declare @CURRENTDATE datetime;

                begin try
                    if @ID is null
                        set @ID = newid();

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

                    set @CURRENTDATE = getdate();

                    -- Sponsoring institution insert (account number encryption)

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

                        exec dbo.USP_GET_KEY_ACCESS;
                        insert into dbo.FINANCIALINSTITUTION
                        (
                            ID,
                            FINANCIALINSTITUTION,
                            BRANCHNAME,
                            ROUTINGNUMBER,
                            ISSPONSORINGINSTITUTION,
                            ACCOUNTNAME,
                            ACCOUNTNUMBER,
                            USERNUMBER,
                            ADDRESSBLOCK,
                            CITY,
                            STATEID,
                            POSTCODE,
                            COUNTRYID,
                            PHONENUMBER,
                            SORTCODE,
                            CLIENTNAME,
                            ADDEDBYID, 
                            CHANGEDBYID, 
                            DATEADDED, 
                            DATECHANGED,
                            BANKINGSYSTEMID,
                            BIC,
                            BANKCODE
                        )
                        values
                        (
                            @ID,
                            @FINANCIALINSTITUTION,
                            @BRANCHNAME, 
                            @ROUTINGNUMBER,
                            @ISSPONSORINGINSTITUTION,
                            @ACCOUNTNAME,
                            EncryptByKey(Key_GUID('sym_BBInfinity'), @ACCOUNTNUMBER),
                            @USERNUMBER,
                            @ADDRESSBLOCK,
                            @CITY,
                            @STATEID,
                            @POSTCODE,
                            @COUNTRYID,
                            @PHONENUMBER,
                            @SORTCODE,
                            @CLIENTNAME,
                            @CHANGEAGENTID, 
                            @CHANGEAGENTID, 
                            @CURRENTDATE, 
                            @CURRENTDATE,
                            @BANKINGSYSTEMID,
                            @BIC,
                            @BANKCODE
                        );
                        close symmetric key sym_BBInfinity;
                    end
                    else
                    begin
                        insert into dbo.FINANCIALINSTITUTION
                        (
                            ID, 
                            FINANCIALINSTITUTION,
                            BRANCHNAME,
                            ROUTINGNUMBER,
                            ISSPONSORINGINSTITUTION,
                            ADDRESSBLOCK,
                            CITY,
                            STATEID,
                            POSTCODE,
                            COUNTRYID,
                            PHONENUMBER,
                            SORTCODE,
                            ADDEDBYID,
                            CHANGEDBYID,
                            DATEADDED,
                            DATECHANGED,
                            BANKINGSYSTEMID,
                            BIC,
                            BANKCODE
                        )
                        values
                        (
                            @ID,
                            @FINANCIALINSTITUTION,
                            @BRANCHNAME,
                            @ROUTINGNUMBER,
                            @ISSPONSORINGINSTITUTION,
                            @ADDRESSBLOCK,
                            @CITY,
                            @STATEID,
                            @POSTCODE,
                            @COUNTRYID,
                            @PHONENUMBER,
                            @SORTCODE,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE,
                            @BANKINGSYSTEMID,
                            @BIC,
                            @BANKCODE
                        );
                    end

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

                return 0;