Configure Oracle

Install BankFinder on your Oracle Server then copy the bforacle.so (Unix) or bforacle.dll (Windows) to an appropriate location on the same server. Although the library can be placed anywhere, a suitable location would be the lib subfolder of your Oracle home path.

Ensure your listener.ora and tnsnames.ora in the network\admin subfolder of your Oracle home path are correctly configured to use this external process.

Your listener.ora file should be similar to the following:

# LISTENER.ORA Network Configuration File: C:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
    (DESCRIPTION_LIST =
        (DESCRIPTION =
            (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
            )
            (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = servername)(PORT = 1521))
            )
        )
    )
SID_LIST_LISTENER =
    (SID_LIST =
        (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME = C:\oracle\ora92)
            (PROGRAM = extproc)
            (ENVS="EXTPROC_DLLS=ANY") 
        )
        (SID_DESC =
            (GLOBAL_DBNAME = name.domain)
            (ORACLE_HOME = C:\oracle\ora92)
            (SID_NAME = name)
        )
        (SID_DESC =
            (GLOBAL_DBNAME = Test)
            (ORACLE_HOME = C:\oracle\ora92)
            (SID_NAME = Test)
        )

You should see something similar to this in your own file. Generally this will all be set up fine for you by Oracle. Your database names and paths will obviously be different in your own configuration file. The important thing to check is that in the SID_LIST_LISTENER section you have a SID_DESC to run external procedures similar to the first entry in that list above. You will need to add to that section a line similar to:

(ENVS="EXTPROC_DLLS=ANY")

To allow the DLL to run if it is not in your Oracle bin or lib folder. Using "ANY" is useful for testing, but for additional security you may prefer to use a line like:

(ENVS="EXTPROC_DLLS=c:\oracle\ora92\lib\bforacle.dll")

Please note that you can colon separate entries in that line if you already have another DLL listed, e.g.

(ENVS="EXTPROC_DLLS=c:\oracle\ora92\lib\bforacle.dll:c:\inhouse\myinhouse.dll")

Your tnsnames.ora file along with details of your database names also needs to include details for the extproc listener as well, similar to the following:

EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
        )
            (CONNECT_DATA =
                (SID = PLSExtProc)
                (PRESENTATION = RO)
            )
        )

Again this should have been configured for you when you installed Oracle.

If you need further assistance configuring Oracle to use our DLL then please email Support. If you query relates to more complex issues regarding Oracle database configuration generally then please contact your normal database support provider.

Add the Functions

To declare all the functions in the bfOracle.dll to enable you to use all the functionality of AFD BankFinder through SQL statements or stored procedures you will need to add the following Functions and Procedures to your database. The easiest way to do this is to launch SQL Plus on your Oracle server and enter the following statements. You can enter and/or manage these how you wish of course. Please note that due to limitations under some versions of UNIX some of the longer function calls may need to be entered on fewer lines (i.e. with longer lines).

Windows:

create library afdbank as 'c:\oracle\ora92\lib\bforacle.dll';

Unix:

create library afdbank as '/opt/Oracle/OraHome/lib/bforacle.so';

CREATE OR REPLACE FUNCTION BFGetFirst(
flags IN OUT BINARY_INTEGER, lookupStr varchar2, sortCode varchar2, bankBIC varchar2, branchBIC varchar2, postcode varchar2, branchName varchar2, bankName varchar2,
town varchar2, phone varchar2, searchText varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoGetFirst2" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (
flags long, lookupStr string, sortCode string, bankBIC string, branchBIC string, postcode string, branchName string, bankName string, town string, phone string, searchText string);
/
CREATE OR REPLACE FUNCTION BFGetNext
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoGetNext" -- Name of function call. Quotes preserve lower case.
LANGUAGE C;
/
CREATE OR REPLACE FUNCTION BFGetFirstRedirect(sortCode IN OUT varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoGetFirstRedirect" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (sortCode string);
/
CREATE OR REPLACE FUNCTION BFGetNextRedirect(sortCode OUT varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoGetNextRedirect" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (sortCode string);
/
CREATE OR REPLACE FUNCTION BFJumpToRecord (recNo BINARY_INTEGER)
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoJumpToRecord" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (recNo long);
/
CREATE OR REPLACE FUNCTION BFValidateAccount(flags IN OUT BINARY_INTEGER, sortCode IN OUT varchar2, accountNo IN OUT varchar2, typeOfAccountCode OUT BINARY_INTEGER) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoValidateAccount2" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (flags long, sortCode string, accountNo string, typeOfAccountCode long);
/
CREATE OR REPLACE FUNCTION BFValidateCard(revisionID BINARY_INTEGER, cardNo IN varchar2, expiryDate IN varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoValidateCard" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (revisionID long, cardNo string, expiryDate string);
/
CREATE OR REPLACE PROCEDURE BFGetBankRecord(
recNo OUT BINARY_INTEGER, sortCode OUT varchar2, bankBIC OUT varchar2, branchBIC OUT varchar2, subBranchSuffix OUT varchar2, shortBranchTitle OUT varchar2, centralBankCountryCode OUT varchar2, supervisoryBody OUT varchar2, deletedDate OUT varchar2, branchTypeIndicator OUT varchar2, mainBranchSortCode OUT varchar2, majorLocationName OUT varchar2, minorLocationName OUT varchar2, branchName OUT varchar2, branchName2 OUT varchar2, fullBranchTitle OUT varchar2, ownerBankShortName OUT varchar2, ownerBankFullName OUT varchar2, ownerBankCode OUT varchar2, property OUT varchar2, street OUT varchar2, locality OUT varchar2, town OUT varchar2, county OUT varchar2, postcode OUT varchar2, stdCode OUT varchar2, phone OUT varchar2, faxStdCode OUT varchar2, fax OUT varchar2, countryIndicator OUT varchar2, reserved1 OUT varchar2, reserved2 OUT varchar2, reserved3 OUT varchar2, reserved4 OUT varchar2, reserved5 OUT varchar2) AS EXTERNAL LIBRARY afdbank
NAME "DoGetBankRecord2"
LANGUAGE C
PARAMETERS (recNo long, sortCode string, bankBIC string, branchBIC string, 
subBranchSuffix string, shortBranchTitle string, centralBankCountryCode string,
supervisoryBody string, deletedDate string, branchTypeIndicator string,
mainBranchSortCode string, majorLocationName string, minorLocationName string,
branchName string, branchName2 string, fullBranchTitle string,
ownerBankShortName string, ownerBankFullName string, ownerBankCode string,
property string, street string, locality string, town string,
county string, postcode string, stdCode string, phone string, faxStdCode string, fax string, countryIndicator string, reserved1 string, reserved2 string, reserved3 string, reserved4 string, reserved5 string);
/
CREATE OR REPLACE PROCEDURE BFGetBACSRecord(
status OUT varchar2, lastChange OUT varchar2, closedClearing OUT varchar2, 
redirectedFromFlag OUT varchar2, redirectedToSortCode OUT varchar2, 
settlementBankCode OUT varchar2, settlementBankShortName OUT varchar2,
settlementBankFullName OUT varchar2, settlementSection OUT varchar2,
settlementSubSection OUT varchar2, handlingBankCode OUT varchar2,
handlingBankShortName OUT varchar2, handlingBankFullName OUT varchar2,
handlingBankStream OUT varchar2, accountNumberedFlag OUT varchar2,
DDIVoucherFlag OUT varchar2, allowedDirectDebits OUT varchar2,
allowedBankGiroCredits OUT varchar2, allowedBuildingSocietyCredits OUT varchar2,
allowedDividendPayments OUT varchar2, allowedDirectDebitInstructions OUT varchar2,
allowedUnpaidChequeClaims OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetBACSRecord"
LANGUAGE C
PARAMETERS (status string, lastChange string, closedClearing string, 
redirectedFromFlag string, redirectedToSortCode string, 
settlementBankCode string, settlementBankShortName string,
settlementBankFullName string, settlementSection string,
settlementSubSection string, handlingBankCode string,
handlingBankShortName string, handlingBankFullName string,
handlingBankStream string, accountNumberedFlag string,
DDIVoucherFlag string, allowedDirectDebits string,
allowedBankGiroCredits string, allowedBuildingSocietyCredits string,
allowedDividendPayments string, allowedDirectDebitInstructions string,
allowedUnpaidChequeClaims string);
/
CREATE OR REPLACE PROCEDURE BFGetCHAPSRecord(
pStatus OUT varchar2, pLastChange OUT varchar2, pClosedClearing OUT varchar2, 
pSettlementBankCode OUT varchar2, pSettlementBankShortName OUT varchar2,
pSettlementBankFullName OUT varchar2,
eStatus OUT varchar2, eLastChange OUT varchar2, eClosedClearing OUT varchar2, 
eEuroRoutingBICBank OUT varchar2, eEuroRoutingBICBranch OUT varchar2,
eSettlementBankCode OUT varchar2, eSettlementBankShortName OUT varchar2,
eSettlementBankFullName OUT varchar2, eReturnIndicator OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetCHAPSRecord"
LANGUAGE C
PARAMETERS (pStatus string, pLastChange string, pClosedClearing string, 
pSettlementBankCode string, pSettlementBankShortName string,
pSettlementBankFullName string,
eStatus string, eLastChange string, eClosedClearing string, 
eEuroRoutingBICBank string, eEuroRoutingBICBranch string,
eSettlementBankCode string, eSettlementBankShortName string,
eSettlementBankFullName string, eReturnIndicator string);
/
CREATE OR REPLACE PROCEDURE BFGetCCCCRecord(
status OUT varchar2, lastChange OUT varchar2, closedClearing OUT varchar2, 
settlementBankCode OUT varchar2, settlementBankShortName OUT varchar2,
settlementBankFullName OUT varchar2, debitAgencySortCode OUT varchar2,
returnIndicator OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetCCCCRecord"
LANGUAGE C
PARAMETERS (status string, lastChange string, closedClearing string, 
settlementBankCode string, settlementBankShortName string,
settlementBankFullName string, debitAgencySortCode string,
returnIndicator string);
/
CREATE OR REPLACE PROCEDURE BFGetBankLine(line OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetBankLine"
LANGUAGE C
PARAMETERS (line string);
/

Full details of each of these Function calls is given in the Function reference.

Using the API

Run the BFLookup function to check everything is working. You can then add the BankFinder functionality to your Oracle system using PL/SQL.

BankFinder Examples

BFLookup

To test you have correctly installed and configured BankFinder for Oracle on your server you can paste the following test procedure into SQL Plus to perform a simple lookup.

CREATE OR REPLACE PROCEDURE BFLookup (lookupStr varchar2) AS
  line varchar2(255);
  retVal integer;
  flags integer;
BEGIN
  -- Set flags to 0 to return branches on both the UK and Irish clearing systems
  -- Set flags to 1 to return branches on the UK (BACS) clearing system only
  -- Set flags to 2 to return branches on the Irish (IPSO) clearing system only
  flags := 0;
  retVal := BFGetFirst(flags, lookupStr, '', '', '', '', '', '', '', '', '');
  IF retVal < 0 THEN
    IF retVal < -2 THEN
      dbms_output.put_line(retVal);
    ELSE
      dbms_output.put_line('No Matching Records Found');
    END IF;
  ELSE
    LOOP
      IF retVal <>  5 THEN   -- 5 is a record break
        BFGetBankLine(line);
        dbms_output.put_line(line);
      END IF;
      retVal := BFGetNext();
      EXIT WHEN retVal < 0;
    END LOOP;
  END IF;
END;
/

Before executing this procedure, set serveroutput to on so that you will see it's output, by entering the following line into SQL Plus:

set serveroutput on;

Now to test a lookup execute the function supplying it with a valid lookup string, for example:

execute BFLookup('517032');

You will then see a list of the bank branches using the sortcode 517032.

See the function reference and the details of our Oracle Forms example for details of how the functions called in this procedure work and how to use them in your own database application.

Oracle Forms

You can use BankFinder for Oracle in Oracle Forms easily once you have installed and configured the library to run on your Oracle server. Code can be written to call the external procedures and the results can be assigned to on-screen controls for display or to table fields etc. simply by writing SQL in your Oracle Forms trigger methods.

You may find looking at the code contained in that form and the descriptions of the methods here invaluable in assisting in your integration of Oracle with BankFinder in other applications as well.

You can save the Oracle Form source code to try out in Oracle Forms Developer yourself from here. It demonstrates the use of a lookup, search, and account and card validation using BankFinder for Oracle. Simply load the afdbank.fmb file into Oracle Forms and connect to the database as a user which is configured to access BankFinder for Oracle Procedures and Functions. You can then run the form as well as examine it's code to use as a basis for your own integration.

The 'Fast Find' Button WHEN_MOUSE_CLICK Trigger

This trigger contains the following code:

DECLARE
    fastFindStr varchar(255);
    line varchar2(512);
    retVal integer;
    listElement Number;
    indexValue integer;
    recNo integer;
    sortCode varchar(255); 
    bankBIC varchar(255); 
    branchBIC varchar(255); 
    subBranchSuffix varchar(255); 
    shortBranchTitle varchar(255); 
    centralBankCountryCode varchar(255); 
    supervisoryBody varchar(255); 
    deletedDate varchar(255); 
    branchTypeIndicator varchar(255); 
    mainBranchSortCode varchar(255); 
    majorLocationName varchar(255); 
    minorLocationName varchar(255); 
    branchName varchar(255); 
    branchName2 varchar(255); 
    fullBranchTitle varchar(255); 
    ownerBankShortName varchar(255); 
    ownerBankFullName varchar(255); 
    ownerBankCode varchar(255); 
    property varchar(255); 
    street varchar(255); 
    locality varchar(255); 
    town varchar(255); 
    county varchar(255); 
    postcode varchar(255); 
    stdCode varchar(255); 
    phone varchar(255); 
    faxStdCode varchar(255); 
    fax varchar(255); 
    countryIndicator varchar(255); 
    reserved1 varchar(255); 
    reserved2 varchar(255); 
    reserved3 varchar(255); 
    reserved4 varchar(255); 
    reserved5 varchar(255); 
    flags integer;
BEGIN
    -- Set flags to 0 to return branches on both the UK and Irish clearing systems
    -- Set flags to 1 to return branches on the UK (BACS) clearing system only
    -- Set flags to 2 to return branches on the Irish (IPSO) clearing system only
    flags := 0;
    fastFindStr := :block4.find;
    retVal := BFGetFirst(flags, fastFindStr, ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ');
    CLEAR_LIST('results');
    listElement := 0;
    IF retVal < 0 THEN
        MESSAGE('No Matching Records Found', ACKNOWLEDGE);
    ELSE
        LOOP
            IF retVal <> 5 THEN -- 5 is a record break
                BFGetBankRecord(recNo, sortCode, bankBIC, branchBIC, subBranchSuffix, shortBranchTitle, centralBankCountryCode, supervisoryBody, deletedDate, branchTypeIndicator, mainBranchSortCode, majorLocationName, minorLocationName, branchName, branchName2, fullBranchTitle, ownerBankShortName, ownerBankFullName, ownerBankCode, property, street, locality, town, county, postcode, stdCode, phone, faxStdCode, fax, countryIndicator, reserved1, reserved2, reserved3, reserved4, reserved5);
                BFGetBankLine(line);
                listElement := listElement + 1;
                indexValue := recNo;
                Add_List_Element('results', listElement, line, listElement);
                Add_List_Element('resultindex', listElement, indexValue, listElement);
            END IF; 
            retVal := BFGetNext();
            EXIT WHEN retVal < 0;
        END LOOP;
    END IF;
END;

This trigger method queries BankFinder for the fast find string specified in the find Text Item. It retrieves all matching bank records and uses the ADD_LIST_ELEMENT built-in subprogram to add each result to the results List Item. A hidden List Item 'resultsindex' is used to store the indexes of each record so they can be retrieved when an item is selected in the list. Rather than adding items to the List Item the 'line' variable can, of course, be assigned to any database field or control on the form as desired.

The 'Search' Button WHEN-BUTTON-PRESSED Trigger

This trigger contains the following code:

DECLARE
    sortCode varchar(255); 
    bankBIC varchar(255); 
    branchBIC varchar(255); 
    postcode varchar(255); 
    branchName varchar(255); 
    bankName varchar(255);
    town varchar(255); 
    phone varchar(255); 
    searchText varchar(255); 
    retVal integer;
    listElement Number;
    indexValue integer;
    searchLimit integer;
    line varchar2(512);
    recNo integer;
    subBranchSuffix varchar(255); 
    shortBranchTitle varchar(255); 
    centralBankCountryCode varchar(255); 
    supervisoryBody varchar(255); 
    deletedDate varchar(255); 
    branchTypeIndicator varchar(255); 
    mainBranchSortCode varchar(255); 
    majorLocationName varchar(255); 
    minorLocationName varchar(255); 
    branchName2 varchar(255); 
    fullBranchTitle varchar(255); 
    ownerBankShortName varchar(255); 
    ownerBankFullName varchar(255); 
    ownerBankCode varchar(255); 
    property varchar(255); 
    street varchar(255); 
    locality varchar(255); 
    county varchar(255); 
    stdCode varchar(255); 
    faxStdCode varchar(255);
    fax varchar(255); 
    countryIndicator varchar(255); 
    reserved1 varchar(255); 
    reserved2 varchar(255); 
    reserved3 varchar(255); 
    reserved4 varchar(255); 
    reserved5 varchar(255); 
    flags integer;
BEGIN
    -- Set flags to 0 to return branches on both the UK and Irish clearing systems
    -- Set flags to 1 to return branches on the UK (BACS) clearing system only
    -- Set flags to 2 to return branches on the Irish (IPSO) clearing system only
    flags := 0;
    searchLimit := 1000; -- Set to stop Oracle going off retrieving infinite results with a vague search
    sortCode:= :txtSortCode;
    if sortCode IS NULL THEN sortCode := ' '; END IF;
    postcode := :txtPostcode;
    if postcode IS NULL THEN postcode := ' '; END IF;
    branchName := :txtBranchName;
    if branchName IS NULL THEN branchName := ' '; END IF;
    bankName := :txtBankName;
    if bankName IS NULL THEN bankName := ' '; END IF;
    town := :txtTown;
    if town IS NULL THEN town := ' '; END IF;
    phone := :txtPhone;
    if phone IS NULL THEN phone := ' '; END IF;
    bankBIC := :txtBankBIC;
    if bankBIC IS NULL THEN bankBIC := ' '; END IF;
    branchBIC := :txtBranchBIC;
    if branchBIC IS NULL THEN branchBIC := ' '; END IF;
    searchText := ' ';
    -- Do the search 
    CLEAR_LIST('results');
    retVal := BFGetFirst(flags, ' ', sortCode, bankBIC, branchBIC, postcode, branchName, bankName, town, phone, searchText);
    listElement := 0;
    IF retVal < 0 THEN
        MESSAGE('No Matching Records Found', ACKNOWLEDGE);
    ELSE
        LOOP
            IF retVal <> 5 THEN -- 5 is a record break
                BFGetBankRecord(recNo, sortCode, bankBIC, branchBIC, subBranchSuffix, shortBranchTitle, centralBankCountryCode, supervisoryBody, deletedDate, branchTypeIndicator, mainBranchSortCode, majorLocationName, minorLocationName, branchName, branchName2, fullBranchTitle, ownerBankShortName, ownerBankFullName, ownerBankCode, property, street, locality, town, county, postcode, stdCode, phone, faxStdcode, fax, countryIndicator, reserved1, reserved2, reserved3, reserved4, reserved5);
                BFGetBankLine(line);
                listElement := listElement + 1;
                indexValue := recNo;
                Add_List_Element('results', listElement, line, listElement);
                Add_List_Element('resultindex', listElement, indexValue, listElement);
            END IF; 
            EXIT WHEN listElement > searchLimit;
            retVal := BFGetNext();
            EXIT WHEN retVal < 0;
        END LOOP;
    END IF;
END;

This trigger method queries BankFinder for the search criteria specified in the text fields.

It retrieves all matching addresses and uses the ADD_LIST_ELEMENT built-in subprogram to add each result to the results List Item. A hidden List Item 'resultsindex' is used to store the indexes of each record so they can be retrieved when an item is selected in the list. Rather than adding items to the List Item the 'line' variable can, of course, be assigned to any database field or control on the form as desired.

The Results List Item WHEN-LIST-CHANGED trigger retrieves and displays the full address details for the selected address.

The 'Validate Account' Button WHEN-BUTTON-PRESSED Trigger

This trigger contains the following code:

DECLARE
    sortCode varchar(255);
    accountNo varchar2(255);
    retVal integer;
    typeOfAccountCode integer;
    flags integer;
    clearingSystem varchar2(255);
BEGIN
    -- Set flags to 0 to return branches on both the UK and Irish clearing systems
    -- Set flags to 1 to return branches on the UK (BACS) clearing system only
    -- Set flags to 2 to return branches on the Irish (IPSO) clearing system only
    flags := 0;
    sortcode := :txtSort;
    if sortCode IS NULL THEN sortCode := ' '; END IF;
    accountNo := :txtAccount;
    if accountNo IS NULL THEN accountNo := ' '; END IF;
    retVal := BFValidateAccount(flags, sortCode, accountNo, typeOfAccountCode);
    IF retVal >= 0 THEN
        -- Update fields in-case of translation
        :txtSort := sortCode;
        :txtAccount := accountNo;
        CASE flags
          WHEN 1 THEN clearingSystem := 'UK (BACS)';
          WHEN 2 THEN clearingSystem := 'IRISH (IPSO)';
          WHEN 3 THEN clearingSystem := 'UK (BACS) & IRISH(IPSO)';
        END CASE;
        -- Inform user of success
        IF retVal = 0 THEN
            MESSAGE('Account Number Valid - Branch a member of the clearing System ' || clearingSystem || '. (Type Of Account Code:' || TO_CHAR(typeOfAccountCode) || ')', ACKNOWLEDGE);
        ELSE
            MESSAGE('Validation Not Avaliable - Branch a member of the clearing System ' || clearingSystem || '. (Type Of Account Code:' || TO_CHAR(typeOfAccountCode) || ')', ACKNOWLEDGE);
        END IF;
    ELSE
        CASE retVal
            WHEN -2 THEN MESSAGE('Sort Code Does Not Exist', ACKNOWLEDGE);
            WHEN -4 THEN MESSAGE('Error opening BankFinder data files', ACKNOWLEDGE);
            WHEN -7 THEN MESSAGE('Data Licence Invalid or Expired', ACKNOWLEDGE);
            WHEN -13 THEN MESSAGE('Invalid Sort Code', ACKNOWLEDGE);
            WHEN -14 THEN MESSAGE('Invalid Account Number', ACKNOWLEDGE);
        END CASE;
    END IF;
END;

This trigger method queries BankFinder to find out if the sort code and account number entered are valid. You should note that if successfully validated the sortcode and account number may be updated should any account number translation be necessary (e.g. with 10 digit account numbers). You should therefore use the sortcode and account number returned by the BFValidateAccount function for any subsequent processing and not the originally entered numbers. The typeOfAccountCode return value is necessary for BACS processing with some translated account numbers.

The 'Validate Card' Button WHEN-BUTTON-PRESSED Trigger

This trigger contains the following code:

DECLARE
    cardNo varchar(255);
    expiryDate varchar2(255);
    retVal integer;
    revisionID integer;
BEGIN
    revisionID := 2;
    cardNo := :txtCard;
    if cardNo IS NULL THEN cardNo := ' '; END IF;
    expiryDate := :txtExpiry;
    if expiryDate IS NULL THEN expiryDate := ' '; END IF;
    retVal := BFValidateCard(revisionID, cardNo, expiryDate);
    CASE retVal
        WHEN 1 THEN MESSAGE('Valid Mastercard', ACKNOWLEDGE);
        WHEN 2 THEN MESSAGE('Valid Visa', ACKNOWLEDGE);
        WHEN 3 THEN MESSAGE('Valid American Express', ACKNOWLEDGE);
        WHEN 4 THEN MESSAGE('Valid Switch', ACKNOWLEDGE);
        WHEN 5 THEN MESSAGE('Valid Visa Delta', ACKNOWLEDGE);
        WHEN 6 THEN MESSAGE('Valid Visa UK Electron', ACKNOWLEDGE);
        WHEN 7 THEN MESSAGE('Valid Visa Purchasing', ACKNOWLEDGE);
        WHEN 8 THEN MESSAGE('Valid Switch and Maestro', ACKNOWLEDGE);
        WHEN 9 THEN MESSAGE('Valid Maestro', ACKNOWLEDGE);
        WHEN 10 THEN MESSAGE('Valid Solo and Maestro', ACKNOWLEDGE);
        WHEN 11 THEN MESSAGE('Valid JCB', ACKNOWLEDGE);
        WHEN -4 THEN MESSAGE('Error opening BankFinder data files', ACKNOWLEDGE);
        WHEN -7 THEN MESSAGE('Data Licence Invalid or Expired', ACKNOWLEDGE);
        WHEN -15 THEN MESSAGE('Invalid Expiry Date', ACKNOWLEDGE);
        WHEN -16 THEN MESSAGE('Card Expired', ACKNOWLEDGE);
        WHEN -18 THEN MESSAGE('Card Number Invalid', ACKNOWLEDGE);
        WHEN -19 THEN MESSAGE('Visa ATM Card Only', ACKNOWLEDGE);
        WHEN -20 THEN MESSAGE('Card Number Valid, but not of a recognised type', ACKNOWLEDGE);
    END CASE;
END;

This trigger method queries BankFinder to find out if the card number and expiry date entered are valid and lets you know what type of card the number corresponds with.

The 'Results' List Item WHEN-LIST-CHANGED Trigger

This trigger contains the following code:

DECLARE
    listIndex integer;
    retVal integer;
    recNo integer;
    sortCode varchar(255); 
    bankBIC varchar(255); 
    branchBIC varchar(255); 
    subBranchSuffix varchar(255); 
    shortBranchTitle varchar(255); 
    centralBankCountryCode varchar(255); 
    supervisoryBody varchar(255); 
    deletedDate varchar(255); 
    branchTypeIndicator varchar(255); 
    mainBranchSortCode varchar(255); 
    majorLocationName varchar(255); 
    minorLocationName varchar(255); 
    branchName varchar(255); 
    branchName2 varchar(255); 
    fullBranchTitle varchar(255); 
    ownerBankShortName varchar(255); 
    ownerBankFullName varchar(255); 
    ownerBankCode varchar(255); 
    property varchar(255); 
    street varchar(255); 
    locality varchar(255); 
    town varchar(255); 
    county varchar(255); 
    postcode varchar(255); 
    stdCode varchar(255); 
    phone varchar(255); 
    faxStdCode varchar(255); 
    fax varchar(255); 
    countryIndicator varchar(255); 
    reserved1 varchar(255); 
    reserved2 varchar(255); 
    reserved3 varchar(255); 
    reserved4 varchar(255); 
    reserved5 varchar(255); 
BEGIN
    listIndex := :block4.results;
    IF listIndex > 0 THEN
        recNo := GET_LIST_ELEMENT_LABEL('resultIndex', listIndex); 
        retVal := BFJumpToRecord(recNo);
        IF retVal = 0 THEN
            BFGetBankRecord(recNo, sortCode, bankBIC, branchBIC, subBranchSuffix, shortBranchTitle, centralBankCountryCode, supervisoryBody, deletedDate, branchTypeIndicator, mainBranchSortCode, majorLocationName, minorLocationName, branchName, branchName2, fullBranchTitle, ownerBankShortName, ownerBankFullName, ownerBankCode, property, street, locality, town, county, postcode, stdCode, phone, faxStdCode, fax, countryIndicator, reserved1, reserved2, reserved3, reserved4, reserved5); 
            -- Use BFGetBACSRecord, BFGetCHAPSRecord and/or BFGetCCCCRecord to get other bank information you require
            :TxtSortCode := sortCode;
            :TxtPostcode := postcode;
            :TxtBranchName := branchName;
            :TxtBankName := ownerBankShortName;
            :TxtTown := town;
            :TxtPhone := stdCode || ' ' || phone;
            :TxtBankBIC := bankBIC;
            :TxtBranchBIC := branchBIC;
            :TxtSuffix := subBranchSuffix;
        END IF; 
    END IF;
END;

This trigger method look's up the bank record which the user has selected and fills it's details into the Text Item's for these fields on the sample Oracle Form. You should note that if you require additional bank information you can also call the BFGetBACSRecord, BFGetCHAPSRecord and/or BFGetCCCCRecord procedures to retrieve those details. The list is filled when a lookup or search is carried out on the example form.

It retrieves the record number from the hidden resultsindex List Item which it uses in a call to BFJumpToRecord to return to the appropriate record in BankFinder corresponding to the one selected in the results list.

Bankfinder Functions

BFGetFirst and BFGetNext

Used to carry out a Bank lookup or search and retrieve the matching branches.

Declarations

CREATE OR REPLACE FUNCTION BFGetFirst(
flags IN OUT BINARY_INTEGER, lookupStr varchar2, sortCode varchar2, bankBIC varchar2, branchBIC varchar2, postcode varchar2, branchName varchar2, bankName varchar2,
town varchar2, phone varchar2, searchText varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoGetFirst2" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (
flags long, lookupStr string, sortCode string, bankBIC string, branchBIC string, postcode string, branchName string, bankName string, town string, phone string, searchText string);
/
CREATE OR REPLACE FUNCTION BFGetNext
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoGetNext" -- Name of function call. Quotes preserve lower case.
LANGUAGE C;

Usage (1) - Lookup

lookupStr varchar(255);
retVal integer; 
flags integer;
BEGIN
-- Set flags to 0 to return branches on both the UK and Irish clearing systems
-- Set flags to 1 to return branches on the UK (BACS) clearing system only
-- Set flags to 2 to return branches on the Irish (IPSO) clearing system only
flags := 0;
lookupStr := '517032'; 
retVal := BFGetFirst(flags, lookupStr, '', '', '', '', '', '', '', '', ''); 
IF retVal < 0 THEN 
   -- No matching records found - Display error? 
ELSE 
  LOOP 
    IF retVal <> 5 THEN -- 5 is a record break 
      -- Display/store details, e.g. use BFGetBankLine for a list item 
      -- or BFGetBankDetails, BFGetBACSDetails, 
      -- BFGetCHAPSDetails and/or BFGetCCCCDetails to retrieve full
      -- bank information. 
    END IF; 
    -- Get Next Item 
    retVal := BFGetNext(); 
    -- Exit when no more records left 
    EXIT WHEN retVal < 0; 
  END LOOP; 
END IF; 
END;
sortCode varchar(255);
bankBIC varchar(255); 
branchBIC varchar(255); 
postcode varchar(255); 
branchName varchar(255); 
bankName varchar(255); 
town varchar(255); 
phone varchar(255); 
searchText varchar(255); 
retVal integer; 
flags integer;
BEGIN
-- Set flags to 0 to return branches on both the UK and Irish clearing systems
-- Set flags to 1 to return branches on the UK (BACS) clearing system only
-- Set flags to 2 to return branches on the Irish (IPSO) clearing system only
flags := 0;
-- Set the search criteria 
sortCode = ''; 
bankBIC = ''; 
branchBIC = ''; 
postcode = ''; 
branchName = ''; 
bankName = 'Natwest'; 
town = 'Birmingham'; 
phone = ''; 
searchText = ''; 
-- Do the search 
retVal := BFGetFirst(flags, '', sortCode, bankBIC, branchBIC, postcode,
branchName, bankName, town, phone, searchText); 
IF retVal < 0 THEN 
  -- No matching records found - Display error? 
ELSE 
  LOOP 
    IF retVal <> 5 THEN -- 5 is a record break 
      -- Display/store details, e.g. use BFGetBankLine for a list item 
      -- or BFGetBankDetails, BFGetBACSDetails, 
      -- BFGetCHAPSDetails and/or BFGetCCCCDetails to retrieve full
      -- bank information. 
    END IF; 
    -- Get Next Item 
    retVal := BFGetNext(); 
    -- Exit when no more records left 
    EXIT WHEN retVal < 0; 
  END LOOP; 
END IF; 
END;

Result

retVal indicates the return value of the BFGetFirst/BFGetNext function:

  • 0 if success (result retrieved)
  • -2 if no matching records found
  • -4 if there is an error opening the BankFinder data files
  • -6 indicates the end of the search (returned by BFGetNext when no results are left to retrieve)
  • -7 if there is a data licence error or your data licence has expired
  • -10 if no lookup/search data was supplied

BFGetFirstRedirect and BFGetNextRedirect

These functions are used to get a list of banks that are redirected to a sort code. When you lookup a bank record if the 'Redirected From' field contains 'R' this means that other sort codes are redirected to this one. Call BFGetFirstRedirect with that records sort code and it will return the first sort code that has been redirected to it, then repeatedly call GetNextRedirect until it returns -6 to get the rest of the records.

Declarations

CREATE OR REPLACE FUNCTION BFGetFirstRedirect(sortCode IN OUT varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoGetFirstRedirect" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (sortCode string);
CREATE OR REPLACE FUNCTION BFGetNextRedirect(sortCode OUT varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoGetNextRedirect" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (sortCode string);

Usage

sortCode varchar(255); 
retVal integer; 
BEGIN 
sortCode := '401118'
-- Call BFGetFirstRedirect to get the first record 
retVal := BFGetFirstRedirect(sortCode); 
LOOP 
  EXIT WHEN retVal < 0; 
  -- Display or store sortCode which will have been replaced with the redirected
  -- sortcode 
  -- Retrieve next record: 
  retVal := BFGetNextRedirect(sortCode);
END LOOP; 
END;

Result

retVal indicates the return value of the BFGetFirstRedirect/BFGetNextRedirect function:

  • 0 if success
  • -2 if no matching records found
  • -4 if there is an error opening the BankFinder data files
  • -6 if no matching/further matching sort codes were found
  • -7 if there is a data licence error or your data licence has expired

BFJumpToRecord

This function is used to jump back to a record that has previously been accessed. It is useful if you are listing results and need to recall a record when the user selects it. Each Bank record has a record number simply pass that to this function, you will be moved to that position from where you can call off the data you then require using the BFGetBankRecord, BFGetBACSRecord, BFGetCHAPSRecord and BFGetCCCCRecord methods as desired.

Declaration

CREATE OR REPLACE FUNCTION BFJumpToRecord (recNo BINARY_INTEGER)
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoJumpToRecord" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (recNo long);

Usage

recNo integer; 
countyType integer; 
BEGIN 
recNo := -- Record Number you wish to jump back too 
retVal := BFJumpToRecord(recNo); 
-- If retVal = 0 then successful jump 
END;

Result

retVal indicates the return value of the BFJumpToRecord function:

  • 0 if success
  • -2 if the record number supplied does not exist
  • -4 if there is an error opening the BankFinder data files
  • -7 if there is a data licence error or your data licence has expired

BFValidateAccount

This function is used to verify that an Account Number is valid and therefore can be used to help eliminate errors in capturing an account number. The sortcode and accountNo parameters will be updated with the translated numbers for BACS processing if applicable, so you should use the values returned by the function in any subsequent processing. The TypeOfAccountCode parameter is normally zero but for some account translations will be set and is necessary for BACS submission. You may also want to lookup the Sort Code using the BFGetFirst and BFGetNext methods to verify the bank details are correct.

Declaration

CREATE OR REPLACE FUNCTION BFValidateAccount(flags IN OUT BINARY_INTEGER, sortCode IN OUT varchar2, accountNo IN OUT varchar2, typeOfAccountCode OUT BINARY_INTEGER) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoValidateAccount2" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (flags long, sortCode string, accountNo string, typeOfAccountCode long);
/

Usage

sortCode varchar(255); 
accountNo varchar(255); 
typeOfAccountCode integer; 
retVal integer; 
flags integer;
BEGIN 
-- Set flags to 0 to return branches on both the UK and Irish clearing systems
-- Set flags to 1 to return branches on the UK (BACS) clearing system only
-- Set flags to 2 to return branches on the Irish (IPSO) clearing system only
flags := 0;
-- Set the account details to validate 
sortCode := '401101'; 
accountNo := '61893939'; 
-- Call the account validation function 
retVal := BFValidateAccount(flags, sortCode, accountNo, typeOfAccountCode);
IF retVal >= 0 THEN 
  -- Successfully validated - process it here - sortCode, accountNo, and
  -- typeOfAccountCode variables will have been updated with any necessary
  -- account translation 
  -- Flags will be 1 if the account is at a branch on the UK (BACS) System
  -- Flags will be 2 if the account is at a branch on the Irish (IPSO) System
  -- Flags will be 3 if the account is held at a branch on both clearing systems
ELSE 
  -- Validation failed - inform user (See below for error codes) 
END IF 
END;

Result

retVal indicates the return value of the BFValidateAccount function:

  • 0 if success and validation was successful for the account code
  • 1 also success, but no validation is available for this sort code
  • -2 if no matching sort code was found
  • -4 if there is an error opening the BankFinder data files
  • -7 if there is a data licence error or your data licence has expired
  • -13 if the sort code supplied is invalid (it should be in the format XXXXX, XX-XX-XX, or XX XX XX, where X is a digit)
  • -14 if the account number was invalid (incorrect length or the checksum was not valid)

BFValidateCard

This function is used to verify that a Debit / Credit Card Number and expiry date is valid and to determine what the card type is. It can be used to help eliminate errors in capturing debit or credit card details.

Declaration

CREATE OR REPLACE FUNCTION BFValidateCard(revisionID BINARY_INTEGER, cardNo IN varchar2, expiryDate IN varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoValidateCard" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (revisionID long, cardNo string, expiryDate string);

Usage

cardNo varchar(255); 
expiryDate varchar(255); 
revisionID integer; 
retVal integer; 
BEGIN 
-- Revision ID should be set to 2 
revisionID = 2; 
-- Set the card details to validate 
cardNo := '6439401101618939396'; 
expiryDate := '04/02'; -- Must be in the format MM/YY or
-- set to an empty string if you don't want to check the expiry date
-- Call the card validation function 
retVal := BFValidateCard(revisionID, cardNo, expiryDate); 
IF retVal >= 0 THEN 
  -- Successfully validated - check return value for card type and process it here 
ELSE 
  -- Validation failed - inform user (See below for error codes) 
END IF 
END;

Result

retVal indicates the return value of the BFValidateCard function:

Returns Card Type (>0) if success:

  • 1 if Mastercard
  • 2 if Visa (not including Debit types listed below with values 5, 6 and 7 which are also Visa cards)
  • 3 if American Express
  • 4 if Switch (Will normally also need to get an Issue Number from the user, note Return value 8 is also Switch)
  • 5 if Visa Delta
  • 6 if Visa UK Electron
  • 7 if Visa Purchasing
  • 8 if Switch and Maestro combined (Will normally also need to get an Issue Number from the user for Switch)
  • 9 if Maestro (note Return value 8 is also Maestro)
  • 10 if Solo and Maestro combined
  • 11 if JCB

Returns <0 if fails:

  • -4 if there is an error opening the BankFinder data files
  • -7 if there is a data licence error or your data licence has expired
  • -15 if an invalid expiry date was specified (it should be in the format MM/YY, or set to blank not to verify)
  • -16 if the card has passed it's expiry date
  • -18 if the card number was invalid (Incorrect length, invalid number for the type of card, or the checksum was not valid)
  • -19 if the card is Visa but is only accepted by ATM machines (i.e. not for electronic use)
  • -20 if the card number itself is valid but the card could not be fully validated as it is not of one of the recognised types.

BFGetBankRecord

Used to return general bank details. This should be called after a call to BFGetFirst, BFGetNext or BFJumpToRecord.

Declaration

CREATE OR REPLACE PROCEDURE BFGetBankRecord(
recNo OUT BINARY_INTEGER, sortCode OUT varchar2, bankBIC OUT varchar2, branchBIC OUT varchar2, subBranchSuffix OUT varchar2, shortBranchTitle OUT varchar2, centralBankCountryCode OUT varchar2, supervisoryBody OUT varchar2, deletedDate OUT varchar2, branchTypeIndicator OUT varchar2, mainBranchSortCode OUT varchar2, majorLocationName OUT varchar2, minorLocationName OUT varchar2, branchName OUT varchar2, branchName2 OUT varchar2, fullBranchTitle OUT varchar2, ownerBankShortName OUT varchar2, ownerBankFullName OUT varchar2, ownerBankCode OUT varchar2, property OUT varchar2, street OUT varchar2, locality OUT varchar2, town OUT varchar2, county OUT varchar2, postcode OUT varchar2, stdCode OUT varchar2, phone OUT varchar2, faxStdCode OUT varchar2, fax OUT varchar2, countryIndicator OUT varchar2, reserved1 OUT varchar2, reserved2 OUT varchar2, reserved3 OUT varchar2, reserved4 OUT varchar2, reserved5 OUT varchar2) AS EXTERNAL LIBRARY afdbank
NAME "DoGetBankRecord2"
LANGUAGE C
PARAMETERS (recNo long, sortCode string, bankBIC string, branchBIC string, 
subBranchSuffix string, shortBranchTitle string, centralBankCountryCode string,
supervisoryBody string, deletedDate string, branchTypeIndicator string,
mainBranchSortCode string, majorLocationName string, minorLocationName string,
branchName string, branchName2 string, fullBranchTitle string,
ownerBankShortName string, ownerBankFullName string, ownerBankCode string,
property string, street string, locality string, town string,
county string, postcode string, stdCode string, phone string, faxStdCode string, fax string, countryIndicator string, reserved1 string, reserved2 string, reserved3 string, reserved4 string, reserved5 string);
/

Usage

recNo integer; 
sortCode varchar(255); 
bankBIC varchar(255); 
branchBIC varchar(255); 
subBranchSuffix varchar(255); 
shortBranchTitle varchar(255); 
centralBankCountryCode varchar(255); 
supervisoryBody varchar(255); 
deletedDate varchar(255); 
branchTypeIndicator varchar(255); 
mainBranchSortCode varchar(255); 
majorLocationName varchar(255); 
minorLocationName varchar(255); 
branchName varchar(255); 
branchName2 varchar(255); 
fullBranchTitle varchar(255); 
ownerBankShortName varchar(255); 
ownerBankFullName varchar(255); 
ownerBankCode varchar(255); 
property varchar(255); 
street varchar(255); 
locality varchar(255); 
town varchar(255); 
county varchar(255); 
postcode varchar(255); 
stdCode varchar(255); 
phone varchar(255); 
faxStdCode varchar(255); 
fax varchar(255); 
countryIndicator varchar(255); 
reserved1 varchar(255); 
reserved2 varchar(255); 
reserved3 varchar(255); 
reserved4 varchar(255); 
reserved5 varchar(255); 
BEGIN 
-- This assumes BFGetFirst, BFGetNext or BFJumpToRecord has already
-- been called 
BFGetBankRecord(recNo, sortCode, bankBIC, branchBIC, subBranchSuffix, shortBranchTitle, centralBankCountryCode, supervisoryBody, deletedDate, branchTypeIndicator, mainBranchSortCode, majorLocationName, minorLocationName, branchName, branchName2, fullBranchTitle, ownerBankShortName, ownerBankFullName, ownerBankCode, property, street, locality, town, county, postcode, stdCode, phone, faxStdCode, fax, countryIndicator, reserved1, reserved2, reserved3, reserved4, reserved5); 
-- parameters supplied to BFGetBankRecord now contain the general bank
-- fields so these can now be displayed or assigned to your database table. 
END;

BFGetBACSRecord

Applicable to UK (BACS) Records Only

Used to return bank details for the BACS clearance system. This should be called after a call to BFGetFirst, BFGetNext or BFJumpToRecord..

Declaration

CREATE OR REPLACE PROCEDURE BFGetBACSRecord(
status OUT varchar2, lastChange OUT varchar2, closedClearing OUT varchar2, 
redirectedFromFlag OUT varchar2, redirectedToSortCode OUT varchar2, 
settlementBankCode OUT varchar2, settlementBankShortName OUT varchar2,
settlementBankFullName OUT varchar2, settlementSection OUT varchar2,
settlementSubSection OUT varchar2, handlingBankCode OUT varchar2,
handlingBankShortName OUT varchar2, handlingBankFullName OUT varchar2,
handlingBankStream OUT varchar2, accountNumberedFlag OUT varchar2,
DDIVoucherFlag OUT varchar2, allowedDirectDebits OUT varchar2,
allowedBankGiroCredits OUT varchar2, allowedBuildingSocietyCredits OUT varchar2,
allowedDividendPayments OUT varchar2, allowedDirectDebitInstructions OUT varchar2,
allowedUnpaidChequeClaims OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetBACSRecord"
LANGUAGE C
PARAMETERS (status string, lastChange string, closedClearing string, 
redirectedFromFlag string, redirectedToSortCode string, 
settlementBankCode string, settlementBankShortName string,
settlementBankFullName string, settlementSection string,
settlementSubSection string, handlingBankCode string,
handlingBankShortName string, handlingBankFullName string,
handlingBankStream string, accountNumberedFlag string,
DDIVoucherFlag string, allowedDirectDebits string,
allowedBankGiroCredits string, allowedBuildingSocietyCredits string,
allowedDividendPayments string, allowedDirectDebitInstructions string,
allowedUnpaidChequeClaims string);

Usage

status varchar(255); 
lastChange varchar(255); 
closedClearing varchar(255); 
redirectedFromFlag varchar(255); 
redirectedToSortCode varchar(255); 
settlementBankCode varchar(255); 
settlementBankShortName varchar(255); 
settlementBankFullName varchar(255); 
settlementSection varchar(255); 
settlementSubSection varchar(255); 
handlingBankCode varchar(255); 
handlingBankShortName varchar(255); 
handlingBankFullName varchar(255); 
handlingBankStream varchar(255); 
accountNumberedFlag varchar(255); 
DDIVoucberFlag varchar(255); 
allowedDirectDebits varchar(255); 
allowedBankGiroCredits varchar(255); 
allowedBuildingSocietyCredits varchar(255); 
allowedDividendInterestPayments varchar(255); 
allowedDirectDebitInstructions varchar(255); 
allowedUnpaidChequeClaims varchar(255); 
BEGIN 
-- This assumes BFGetFirst, BFGetNext or BFJumpToRecord has
-- already been called 
BFGetBACSRecord(status, lastChange, closedClearing, redirectedFromFlag, redirectedToSortCode, settlementBankCode, settlementBankShortName, settlementBankFullName, settlementSection, settlementSubSection, handlingBankCode, handlingBankShortName, handlingBankFullName, handlingBankStream, accountNumberedFlag, DDIVoucherFlag, allowedDirectDebits, allowedBankGiroCredits, allowedBuildingSocietyCredits, allowedDividendInterestPayments, allowedDirectDebitInstructions, allowedUnapidChequeClaims); 
-- parameters supplied to BFGetBACSRecord now contain the BACS fields
-- so these can now be displayed or assigned to your database table. 
END;

BFGetCHAPSRecord

Applicable to UK (BACS) Records Only

Used to return bank details for both the CHAPS Sterling and Euro clearance system. This should be called after a call to BFGetFirst, BFGetNext or BFJumpToRecord.

Declaration

CREATE OR REPLACE PROCEDURE BFGetCHAPSRecord(
pStatus OUT varchar2, pLastChange OUT varchar2, pClosedClearing OUT varchar2, 
pSettlementBankCode OUT varchar2, pSettlementBankShortName OUT varchar2,
pSettlementBankFullName OUT varchar2,
eStatus OUT varchar2, eLastChange OUT varchar2, eClosedClearing OUT varchar2, 
eEuroRoutingBICBank OUT varchar2, eEuroRoutingBICBranch OUT varchar2,
eSettlementBankCode OUT varchar2, eSettlementBankShortName OUT varchar2,
eSettlementBankFullName OUT varchar2, eReturnIndicator OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetCHAPSRecord"
LANGUAGE C
PARAMETERS (pStatus string, pLastChange string, pClosedClearing string, 
pSettlementBankCode string, pSettlementBankShortName string,
pSettlementBankFullName string,
eStatus string, eLastChange string, eClosedClearing string, 
eEuroRoutingBICBank string, eEuroRoutingBICBranch string,
eSettlementBankCode string, eSettlementBankShortName string,
eSettlementBankFullName string, eReturnIndicator string);

Usage

pStatus varchar(255); 
pLastChange varchar(255); 
pClosedClearing varchar(255); 
pSettlementBankCode varchar(255); 
pSettlementBankShortName varchar(255); 
pSettlementBankFullName varchar(255); 
eStatus varchar(255); 
eLastChange varchar(255); 
eClosedClearing varchar(255); 
eEuroRoutingBICBank varchar(255); 
eEuroRoutingBICBranch varchar(255); 
eSettlementBankCode varchar(255); 
eSettlementBankShortName varchar(255); 
eSettlementBankFullName varchar(255); 
eReturnIndicator varchar(255); 
BEGIN 
-- This assumes BFGetFirst, BFGetNext or BFJumpToRecord has already
-- been called 
BFGetCHAPSRecord(pStatus, pLastChange, pClosedClearing, pSettlementBankCode, pSettlementBankShortName, pSettlementBankFullName, eStatus, eLastChange, eClosedClearing, eEuroRoutingBICBank, eEuroRoutingBICBranch, eSettlementBankCode, eSettlementBankShortName, eSettlementBankFullName, eReturnIndicator); 
-- parameters supplied to BFGetCHAPSRecord now contain the CHAPS fields
-- so these can now be displayed or assigned to your database table. 
END; 

BFGetCCCCRecord

Applicable to UK (BACS) Records Only

Used to return bank details for both the C&CCC clearance system. This should be called after a call to BFGetFirst, BFGetNext or BFJumpToRecord..

Declaration

CREATE OR REPLACE PROCEDURE BFGetCCCCRecord(
status OUT varchar2, lastChange OUT varchar2, closedClearing OUT varchar2, 
settlementBankCode OUT varchar2, settlementBankShortName OUT varchar2,
settlementBankFullName OUT varchar2, debitAgencySortCode OUT varchar2,
returnIndicator OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetCCCCRecord"
LANGUAGE C
PARAMETERS (status string, lastChange string, closedClearing string, 
settlementBankCode string, settlementBankShortName string,
settlementBankFullName string, debitAgencySortCode string,
returnIndicator string);

Usage

status varchar(255); 
lastChange varchar(255); 
closedClearing varchar(255); 
settlementBankCode varchar(255); 
settlementBankShortName varchar(255); 
settlementBankFullName varchar(255); 
debitAgencySortCode varchar(255); 
returnIndicator varchar(255); 
BEGIN 
-- This assumes BFGetFirst, BFGetNext or BFJumpToRecord has already
-- been called 
BFGetCCCCRecord(status, lastChange, closedClearing, settlementBankCode, settlementBankShortName, settlementBankFullName, debitAgencySortCode, returnIndicator); 
-- parameters supplied to BFGetCCCCRecord now contain the C&CCC fields
-- so these can now be displayed or assigned to your database table. 
END;

BFGetBankLine

Used to get a line containing bank details following a call to BFGetFirst, BFGetNext or BFJumpToRecord. This line is formatted for list display and to provide for user selection.

Declaration

CREATE OR REPLACE PROCEDURE BFGetBankLine(line OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetBankLine"
LANGUAGE C
PARAMETERS (line string);

Usage

line varchar(512); 
BEGIN 
-- This assumes BFGetFirst, BFGetNext or BFJumpToRecord has already
-- been called 
BFGetBankLine(line); 
-- line now contains the bank details line 
END;

Result

The bank line returned as a string for display. Normally you would provide the user with a list of these called from each iteration of a call to BFGetFirst and subsequent calls to BFGetNext for a lookup or search.