USP_BBNC_GETORCREATEFINANCIALINSTITUTION_1_1

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@FINANCIALINSTITUTION	nvarchar(100)	IN
@BRANCHNAME	nvarchar(100)	IN
@ROUTINGNUMBER	nvarchar(9)	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN
@SORTCODE	nvarchar(6)	IN
@BICCODE	nvarchar(11)	IN
@BANKCODE	nvarchar(25)	IN
@BSBNUMBER	nvarchar(6)	IN
Definition

Copy


            create procedure dbo.USP_BBNC_GETORCREATEFINANCIALINSTITUTION_1_1
            (
                @ID uniqueidentifier = null output,
                @FINANCIALINSTITUTION nvarchar(100),
                @BRANCHNAME nvarchar(100),
                @ROUTINGNUMBER nvarchar(9),
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
                @SORTCODE nvarchar(6) = '',
        @BICCODE nvarchar(11)= '',
        @BANKCODE nvarchar(25)= '',
        @BSBNUMBER nvarchar(6)= ''

            )
            as
            set nocount on;

            if @CHANGEDATE is null
                set @CHANGEDATE = getdate();

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

            declare @COUNTRYID uniqueidentifier;

            begin try
                set @ID = null;

                select @COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT();

                select top 1 
                    @ID = ID 
                from dbo.FINANCIALINSTITUTION
                where FINANCIALINSTITUTION = @FINANCIALINSTITUTION 
                and BRANCHNAME = @BRANCHNAME 
                and ROUTINGNUMBER = @ROUTINGNUMBER
                and SORTCODE = @SORTCODE
              and BIC= @BICCODE
              and BANKCODE=@BANKCODE
              and BSB=@BSBNUMBER;

                if @ID is null
                begin
                    set @ID = newid();
                    --select default banking system for UK/US

                    declare @BANKINGSYSTEMID uniqueidentifier;

                    if @ROUTINGNUMBER <> '' and @BICCODE= '' and @SORTCODE = '' and @BSBNUMBER = ''
                        begin
                            select @BANKINGSYSTEMID = BANKINGSYSTEM.ID from dbo.BANKINGSYSTEM where BANKINGSYSTEM.CONDITIONSETTINGNAME = 'BankingSystem-ACH'
                        end

                    if @ROUTINGNUMBER = '' and @BICCODE= '' and @BSBNUMBER = '' and @SORTCODE <> ''
                        begin
                            select @BANKINGSYSTEMID = BANKINGSYSTEM.ID from dbo.BANKINGSYSTEM where BANKINGSYSTEM.CONDITIONSETTINGNAME = 'BankingSystem-BACS'
                        end

                  if @ROUTINGNUMBER = '' and @SORTCODE = '' and  @BSBNUMBER = '' and  @BICCODE <> ''
                        begin
                            select @BANKINGSYSTEMID = BANKINGSYSTEM.ID from dbo.BANKINGSYSTEM where BANKINGSYSTEM.CONDITIONSETTINGNAME = 'BankingSystem-SEPA'
                        end

                  if @ROUTINGNUMBER = '' and @SORTCODE = '' and @BICCODE= '' and @BSBNUMBER <> ''
                        begin
                            select @BANKINGSYSTEMID = BANKINGSYSTEM.ID from dbo.BANKINGSYSTEM where BANKINGSYSTEM.CONDITIONSETTINGNAME = 'BankingSystem-ACH'
                        end

                  if @ROUTINGNUMBER = '' and @SORTCODE = '' and @BICCODE= '' and @BSBNUMBER = ''
                        begin
                            select @BANKINGSYSTEMID = BANKINGSYSTEM.ID from dbo.BANKINGSYSTEM where BANKINGSYSTEM.CONDITIONSETTINGNAME = 'BankingSystem-Other'
                        end

                    insert into dbo.FINANCIALINSTITUTION(ID, FINANCIALINSTITUTION, BRANCHNAME, ROUTINGNUMBER,BSB, SORTCODE,BIC, COUNTRYID, BANKINGSYSTEMID, BANKCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values(@ID, @FINANCIALINSTITUTION, @BRANCHNAME, @ROUTINGNUMBER,@BSBNUMBER, @SORTCODE,@BICCODE, @COUNTRYID, @BANKINGSYSTEMID, @BANKCODE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                end
            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch

            return 0;