This technical essay explains how to implement GB bank branch sort code validation in Oracle HRMS. To implement sort code validation you require expertise in these areas:
Oracle HRMS: functional and technical knowledge of organization and personal payment methods, and familiarity with the GB Bank Details key flexfield structure.
PL/SQL: you use PL/SQL APIs to load reference bank branch data into Oracle HRMS.
Oracle HRMS enables you to validate sort codes for GB bank accounts You can validate from Oracle HRMS Self Service or from the Professional User Interface. You need only enter the sort code, account name, and account number. Oracle HRMS then displays the bank and branch name to verify that your choice of sort code is correct.
You use the supplied APIs to transfer the reference bank branch date to Oracle HRMS,
Customers supply the bank branch data. However, customers can only supply file-based branch data rather than branch data validated by web services.
Sort code validation includes these features:
The Organization Payment Method and Personal Payment Method professional forms and the Manage Payroll Payments self-service form support sort code validation.
Professional and self-service users need only specify the sort code, account name, and account number when entering account information. Sufficient additional information (bank and branch name) is displayed with the sort code to identify the bank branch.
Bank account data is still written to PAY_EXTERNAL_ACCOUNTS table so that you do not need to change code that uses bank account information, for example, BACS tape creation.
To minimize the creation of new records in the PAY_EXTERNAL_ACCOUNTS table we only update bank account information when there are changes to the sort code, account name, or account number. So, changes to the case of the account name, or removal of left-padded zeroes from the account number are handled in flexfield format only in PAY_EXTERNAL_ACCOUNTS. The value is accepted even if the sort code does not exist in PAY_BANK_BRANCHES (or is otherwise disabled) as long as no changes are made. We avoid upgrading existing data unnecessarily and save on the duplication of data by avoiding the need for synchronisation with potentially changing reference branch information.
You can set a profile option to turn sort code validation on or off.
You use the supplied PL/SQL APIs to maintain the reference bank branch data in Oracle HRMS. You can disable unwanted reference branch information to prevent it from being used when you create new bank accounts.
Perform each of these steps to enable sort code validation:
Find a source for the reference bank information. You normally obtain this information from a third party supplier and load it to Oracle HRMS. Ensure that the source of your reference bank information is file-based rather than web-based.
Define mappings between bank name strings in the supplied reference data and lookup codes in the GB_BANKS lookup type.
Create a loader program to maintain reference branch data in Oracle HRMS. We recommend that the loader program uses the supplied APIs for creating and updating reference branch records from the third party source data. Oracle HRMS does not provide forms for maintaining the reference branch data.
Enable the sort code validation by setting the profile option value.
Maintain updates to the reference bank branch information. You must maintain the GB_BANKS lookup for new banks regardless of whether sort code validation is enabled.
If you update GB_BANKS lookup data, you must also update the bank name mappings, as outlined in step 2.
Run the loader program to update the Oracle HRMS reference branch data.
The GB Bank Account flexfield stores lookup codes rather than bank names for the GB_BANKS lookup. Your payroll processing software retrieves the corresponding meaning when it needs to display a bank name.
You must define a mapping between the source data bank names and GB_BANKS lookup codes because the third party sourced bank names are unlikely to be exactly the same as the GB_BANKS lookup meanings. The APIs for loading the GB bank reference data into Oracle HRMS take a GB_BANKS lookup code parameter. Make sure that your loader program includes code to implement the mapping.
See Appendix A for a sample mapping file and PL/SQL code to extract information from the mapping file.
The PAY_BANK_BRANCHES tables stores the reference bank information. Oracle HRMS provides PL/SQL APIs for creating and updating GB bank branch data on PAY_BANK_BRANCHES.
We recommend that you always use the supplied APIs to upload the data. This is because the APIs validate the data before converting it to the correct format. Do not make direct inserts or updates to the table. There is no validation for direct table changes.
This table lists the columns in the PAY_BANK_BRANCHES table:
| Column | Data Type |
|---|---|
| BRANCH_CODE | VARCHAR2(30) NOT NULL |
| LEGISLATION_CODE | VARCHAR2(30) NOT NULL |
| BANK_CODE | VARCHAR2(80) NOT NULL |
| BRANCH | VARCHAR2(80) NOT NULL |
| LONG_BRANCH | VARCHAR2(240) |
| EXTRA_INFORMATION1 | VARCHAR2(80) |
| EXTRA_INFORMATION2 | VARCHAR2(80) |
| EXTRA_INFORMATION3 | VARCHAR2(80) |
| EXTRA_INFORMATION4 | VARCHAR2(80) |
| EXTRA_INFORMATION5 | VARCHAR2(80) |
| ENABLED_FLAG | VARCHAR2(30) |
| START_DATE_ACTIVE | DATE |
| END_DATE_ACTIVE | DATE |
When you create a new bank account, the values for these columns, along with the account name and account number are copied from the bank branch record to the corresponding PAY_EXTERNAL_ACCOUNTS flexfield columns.
Note: Your installation of Oracle HRMS must have an unmodified version of the GB Bank Details flexfield structure to enable the sort code validation to work successfully.
This table shows how each of the columns in the PAY_BANK_BRANCH table is used:
| Column | Usage | Format | Max Size (Bytes) | GB Bank Details Flex Segment |
|---|---|---|---|---|
| LEGISLATION_CODE | Mandatory - identifies the row as a GB row. | Fixed string - GB | 30 | n/a |
| BRANCH_CODE | Mandatory - identifies the sort code. | Fixed length numeric (0-9) string Left-padded with 0 if necessary Values from the set 000000 - 999999 |
6 | 3 |
| BRANCH | Mandatory - identifies the branch name | Mixed case alphanumeric, variable length string | 35 | 2 |
| BANK_CODE | Mandatory - identifies the GB bank code | Lookup code for GB banks lookup | 30 | 1 |
| LONG_BRANCH | Optional but recommended - identifies the branch address | Free-form text | 240 | n/a |
| EXTRA_INFORMATION1 | Not used | n/a | n/a | n/a |
| EXTRA_INFORMATION2 | Not used | n/a | n/a | n/a |
| EXTRA_INFORMATION3 | Not used | n/a | n/a | n/a |
| EXTRA_INFORMATION4 | Not used | n/a | n/a | n/a |
| EXTRA_INFORMATION5 | Not used | n/a | n/a | n/a |
| ENABLED_FLAG | Identifies whether this branch is enabled or disabled. You must enable the branch if you want to display the branch record in an LOV search. |
NULL or Y indicates that the branch record is enabled | 30 | n/a |
| START_DATE_ACTIVE | Identifies the date from which this row is enabled. You must enter a start date if you want to display the branch record in an LOV search. |
NULL is equivalent to the start of time value | n/a | n/a |
| END_DATE_ACTIVE | Identifies the date until which this row is enabled. You must enter an end date if you want to display the branch record in an LOV search. |
NULL is equivalent to the end of time value | n/a | n/a |
We recommend that you:
Write the loader program in PL/SQL
Make loader program call the PAY_BANK_BRANCHES_PKG APIs.
Use these APIs:
INSERT_GB_ROW
UPDATE_GB_ROW
DELETE_ROW
LOCK_ROW
See Appendix B for further details on APIs.
To add data to a loader program:
Perform the startup actions. For example, Initialise Logging and Set Up Your Mapping Structures.
Open the data source
If you do not require any further data, go to step 7
If you do require further data, fetch the next record and convert it to API call format
Call the API.
Repeat steps 4 and 5 until you no longer require any further data.
Perform the cleanup and reporting actions.
The actual loader program code depends on the structure of the input data.
See Appendix C for a sample program listing.
Use the profile option HR: Enable Bank Branch Validation to turn the sort code validation on and off:
If you set the profile option to Yes, then sort code validation is on.
If you set the profile option to No, then sort code validation is off.
If you turn the validation on, your Organization Payment Method (PAYWSDPM) and Personal Payment Method (PAYWSEPM) forms display a different UI for the entry of bank account information.
Oracle HRMS provides these APIs for creating payment methods:
Organization Payment Method (PAY_ORG_PAYMENT_METHOD_API)
Personal Payment Method (HR_PERSONAL PAY_METHOD_API)
We have not modified these APIs as part of the sort code changes. However, when you call the API you can use the sort code to fetch data from PAY_BANK_BRANCHES to set up the branch information segments.
In this sample mapping file we use the # symbol to denote a comment line and the | symbol to denote a field separator. We have arranged the data by bank name, and that is the order in which it is processed. You can create the file manually and then sort it with a standard utility such as the UNIX sort program.
# # Bank Name to Code Mapping File # Bank Name|Bank Code # Abbey National PLC|09 Alliance and Leicester Building Society|16-50 Allied Irish Banks PLC|23 Bank of Scotland|80 Bank of England|10 Bank of Ireland|90 Bank of Scotland|12 Bank of Wales Plc|12-23 Barclays Bank PLC|20 Birmingham and Midshires Building Society|20-07 Bradford and Bingley Building Society|13 Bristol and West Building Society|12-22 Britannia Building Society|08-06-02 C and G Channel Islands Ltd|40-48 Celtic Bank Ltd.|20-44 Chelsea Building Society|08-02 Citibank N.A.|08-06-02 Clydesdale Bank Plc|82 Coutts and Co.|18 First Trust Bank PLC|93 Gateway Building Society|12-22-25 Girobank PLC|72 Halifax PLC|11 Investec Bank (UK) Ltd|08-60-68 Isle of Man Bank Ltd|55 Leeds Permanent Building Society|30-14 Lloyds Bank PLC|30 Midland Bank PLC|40 NWS Bank PLC|40 National Westminster Bank PLC|60 Nationwide Building Society|07 Northern Bank Ltd|95 Northern Rock Building Society|08-60-64 Norwich and Peterborough Building Society|08-60-81 Sainsbury's Bank PLC|12-60 Standard Charter Bank|60-91 TSB Bank Northern Ireland PLC|77-45 TSB Bank PLC|77 TSB Bank of Scotland PLC|87-34 TSB Channel Islands Ltd|77-47 The Royal Bank of Scotland PLC|16 Town and Country Building Society|08-60-8 Ulster Bank Ltd|98 Unity Trust Bank PLC|08-60 Woolwich Building Society|10-80 Yorkshire Bank PLC|05
This sample code contains procedures to:
Create a name-to-code mapping table.
Search the table.
The search code assumes that the bank names are sorted alphabetically.
--
-- Types for holding bank name -> hr_lookupcode map.
--
type t_name2code is record
(name varchar2 (100)
, code varchar2(30)
);
type t_name2code_table is table of t_name2code index by
binary integer;
-- Name-To-Code mapping table
g_N2C_TBL t_name2code_table;
-- Record separator
SEPARATOR constant varchar2(1) := '|';
-- Comment line start character
COMMENT_START constant varchar2(1) := '#';
/* ----------------- build N2C_TBL ---------------------
Name: build N2C_TBL
Description: procedure to build the Name-To-Code mapping table
Comment lines in the file begin with '#'
The file record format is <Name>|<code>
For example:
Big Bank PLC|BIG_BANK
dir is a directory defined as a database directory for PL/SQL file I/O. See My Oracle Support Knowledge Document 2525754.1, Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2.
----------------------------------------------------------------------- */
procedure buildN2C_TBL(dir in varchar2, file in varchar2) is
fhandle utl_file.file_type;
fbuf varchar2(2000);
more boolean := true;
i binary_integer := 1;
seppos number;
begin
-- Open the mapping file
fhandle := utl_file.fopen(dir, file, 'r');
while more loop
begin
utl_file.get_line(fhandle,fbuf);
-- Extract the bank name and code, avoiding comment lines
if instr(fbuf, COMMENT_START) < > 1 then
seppos := instr(fbuf, SEPARATOR);
if seppos > = 1 then
g_N2C_TBL(i).name := substr(fbuf, 1, seppos -1);
g_N2C_TBL(i).code := substr(fbuf, seppos +1);
i := i + 1
end if;
end if;
exception
-- NO_DATA_FOUND indicates EOF
when no_data_found then
utl_file.fclose(fhandle);
more := false
-- Error exception
when others then
utl_file.fclose(fhandle);
raise;
end;
end loop;
end buildN2C_TBL;
/* ---------------------- searchN2CTBL ----------------------------
Name: searchN2C_TBL
Description: Function to search the Name-To-Code mapping table.
If the function finds a match, it returns the lookup code.
If the function does not find a match, it returns NULL.
Notes:
The function uses a binary chop, when searching the mapping table.
Make sure that you order the mapping table alphabetically (lowest value first).
-----------------------------------------------------------------------------*/
function searchN2CTBL(sval in varchar2) return varchar2 is
low number;
high number;
mid number;
begin
low := 1
hi :=g_N2C_TBL.count;
while hi >= low loop
mid := trunc((hi + low)/2);
if g_N2C_TBL(mid).name = sval then;
return g_N2C_TBL(mid).code;
elsif g_N2C_TBL(mid).name > sval then
hi := mid -1;
else
low := mid +1
end if;
end loop;
return null;
end searchN2CTBL;
This appendix describes the PAY_BANK_BRANCHES_PKG APIs.
------------------- < DELETE_ROW > ---------------------------
-- Name: DELETE_ROW
-- Notes: All failures are reported as exceptions.
PROCEDURE DELETE_ROW
(P_BRANCH_CODE IN VARCHAR2)
,P_LEGISLATION_CODE IN VARCHAR2
);
-------------------- <LOCK ROW > ------------------------------
--
-- Name: LOCK_ROW
--
-- Notes: All failures are reported as exceptions.
--
PROCEDURE LOCK_ROW
(P_BRANCH_CODE IN VARCHAR2)
,P_LEGISLATION_CODE IN VARCHAR2
);
-------------------- <INSERT_GB _ROW > ------------------------------
-- Name: INSERT_GB_ROW
-- Description: GB Legislation cover for INSERT_ROW
-- Notes:
-- P_SORT_CODE is 0-left padded on output.
-- P_BUILDING_SOCIETY_ACCT is ignored.
-- The converted values are passed to INSERT_ROW.
-- An exception is raised on failure.
PROCEDURE INSERT_GB_ROW
(P_SORT_CODE IN OUT NOCOPY VARCHAR2
,P_BANK_CODE IN VARCHAR2
,P_BANK IN VARCHAR2
,P_LONG_BRANCH IN VARCHAR2 DEFAULT NULL
,P_BUILDING_SOCIETY_ACCT IN OUT NOCOPY VARCHAR2
,P_ENABLED_FLAG IN VARCHAR2 DEFAULT 'Y'
,P_START_DATE_ACTIVE IN DATE DEFAULT HR_API.G_SOT
,P_END_DATE_ACTIVE IN DATE DEFAULT HR_API.G_EOT
);
-------------------- <UPDATE_GB _ROW > ------------------------------
-- Name: UPDATE_GB_ROW
-- Description: GB legislation cover for UPDATE_ROW.
-- Notes:
-- The HR_API constants, (HR_API_VARCHAR2 or HR_API.G_DATE) are 'no change' values.
-- P_BUILDING_SOCIETY_ACCT is ignored.
-- The converted value is passed to UPDATE_ROW.
-- An exception is raised upon failure.
--
PROCEDURE UPDATE_GB_ROW
(P_SORT_CODE IN VARCHAR2
,P_BRANCH IN VARCHAR2 DEFAULT HR_API.G_VARCHAR2
,P_LONG_BRANCH IN VARCHAR2 DEFAULT HR_API.G_VARCHAR2
,P_BUILDING_SOCIETY_ACCT IN OUT NOCOPY VARCHAR2
,P_ENABLED_FLAG IN VARCHAR2 DEFAULT HR_API.G_DATE
,P_START_DATE_ACTIVE IN DATE DEFAULT HR_API.G_DATE
,P_END_DATE_ACTIVE IN DATE DEFAULT HR_API.G_DATE
);
--------------------------------------------------------------------
# denotes a comment line.
| denotes a field separator
Each line contains a single record
Each record has three mandatory and two optional fields
---------------------------------------------------------------------
#
# Bank Branch Records File
# Bank|Sort Code|Branch|Branch Address|Building Society Account
Abbey National PLC|000000|Town0 Branch|Abbey National PLC, Town0 Branch, The High Street, Town0, AC0 0YZ|B/S Acct0 Bradford and Bingley Building Society|000104|Town104 Branch|Bradford and Bingley Building Society, Town104 Branch, The High Street, Town104, AC92 8YZ| First Trust Bank PLC|000208|Town208 Branch|First Trust Bank PLC, Town208 Branch, The High Street, Town208, AC84 6YZ| National Westminster Bank PLC|000312|Town312 Branch|National Westminster Bank PLC, Town312 Branch, The High Street, Town312, AC76 4YZ| TSB Channel Islands Ltd.|000416|Town416 Branch|TSB Channel Islands Ltd., Town416 Branch, The High Street, Town416, AC68 2YZ| Bank Of Scotland|000520|Town520 Branch|Bank Of Scotland, Town520 Branch, The High Street, Town520, AC60 0YZ| C and G channel Islands Ltd|000624|Town624 Branch|C and G channel Islands Ltd, Town624 Branch, The High Street, Town624, AC52 8YZ| Halifax PLC|000728|Town728 Branch|Halifax PLC, Town728 Branch, The High Street, Town728, AC44 6YZ| Northern Rock Building Society|000832|Town832 Branch|Northern Rock Building Society, Town832 Branch, The High Street, Town832, AC36 4YZ| Ulster Bank Ltd|000936|Town936 Branch|Ulster Bank Ltd, Town936 Branch, The High Street, Town936, AC28 2YZ| Bank of Scotland|001040|Town1040 Branch|Bank of Scotland, Town1040 Branch, The High Street, Town1040, AC20 0YZ|Citibank N.A.|001144|Town1144 Branch|Citibank N.A., Town1144 Branch, The High Street, Town1144, AC12 8YZ|Leeds Permanent Building Society|001248|Town1248 Branch|Leeds Permanent Building Society, Town1248 Branch, The High Street, Town1248, AC4 6YZ| Standard Charter Bank|001352|Town1352 Branch|Standard Charter Bank, Town1352 Branch, The High Street, Town1352, AC96 4YZ| Yorkshire Bank PLC|001456|Town1456 Branch|Yorkshire Bank PLC, Town1456 Branch, The High Street, Town1456, AC88 2YZ| Birmingham and Midshires Building Society|001560|Town1560 Branch|Birmingham and Midshires Building Society, Town1560 Branch, The High Street, Town1560, AC80 0YZ| Coutts and Co.|001664|Town1664 Branch|Coutts and Co., Town1664 Branch, The High Street, Town1664, AC72 8YZ| NWS Bank Plc|001768|Town1768 Branch|NWS Bank Plc, Town1768 Branch, The High Street, Town1768, AC64 6YZ| TSB Bank of Scotland Plc|001872|Town1872 Branch|TSB Bank of Scotland Plc, Town1872 Branch, The High Street, Town1872, AC56 4YZ| Allied Irish Banks PLC|001976|Town1976 Branch|Allied Irish Banks PLC, Town1976 Branch, The High Street, Town1976, AC48 2YZ| Britannia Building Society|002080|Town2080 Branch|Britannia Building Society, Town2080 Branch, The High Street, Town2080, AC40 0YZ| Girobank PLC|002184|Town2184 Branch|Girobank PLC, Town2184 Branch, The High Street, Town2184, AC32 8YZ| Northern Bank Ltd|002288|Town2288 Branch|Northern Bank Ltd, Town2288 Branch, The High Street, Town2288, AC24 6YZ| Town and Country Building Society|002392|Town2392 Branch|Town and Country Building Society, Town2392 Branch, The High Street, Town2392, AC16 4YZ| Bank of Ireland|002496|Town2496 Branch|Bank of Ireland, Town2496 Branch, The High Street, Town2496, AC8 2YZ| Chelsea Building Society|002600|Town2600 Branch|Chelsea Building Society, Town2600 Branch, The High Street, Town2600, AC0 0YZ| Isle of Man Bank Ltd|002704|Town2704 Branch|Isle of Man Bank Ltd, Town2704 Branch, The High Street, Town2704, AC92 8YZ| Sainsburys Bank PLC|002808|Town2808 Branch|Sainsburys Bank PLC, Town2808 Branch, The High Street, Town2808, AC84 6YZ| Woolwich Building Society|002912|Town2912 Branch|Woolwich Building Society, Town2912 Branch, The High Street, Town2912, AC76 4YZ| Barclays Bank PLC|003016|Town3016 Branch|Barclays Bank PLC, Town3016 Branch, The HighStreet, Town3016, AC68 2YZ| Co-operative Bank PLC|003120|Town3120 Branch|Co-operative Bank PLC, Town3120 Branch, The High Street, Town3120, AC60 0YZ| Midland Bank PLC|003224|Town3224 Branch|Midland Bank PLC, Town3224 Branch, The High Street, Town3224, AC52 8YZ| TSB Bank PLC|003328|Town3328 Branch|TSB Bank PLC, Town3328 Branch, The High Street, Town3328, AC44 6YZ| Alliance and Leicester Building Society|003432|Town3432 Branch|Alliance and LeicesterBuilding Society, Town3432 Branch, The High Street, Town3432, AC36 4YZ| Bristol and West Building Society|003536|Town3536 Branch|Bristol and West Building Society, Town3536 Branch, The High Street, Town3536, AC28 2YZ| Gateway Building Society|003640|Town3640 Branch|Gateway Building Society, Town3640 Branch, The High Street, Town3640, AC20 0YZ| Nationwide Building Society|003744|Town3744 Branch|Nationwide Building Society, Town3744 Branch, The High Street, Town3744, AC12 8YZ| The Royal Bank of Scotland PLC|003848|Town3848 Branch|The Royal Bank of Scotland PLC,Town3848 Branch, The High Street, Town3848, AC4 6YZ| Bank of England|003952|Town3952 Branch|Bank of England, Town3952 Branch, The High Street, Town3952, AC96 4YZ| Celtic Bank Ltd.|004056|Town4056 Branch|Celtic Bank Ltd., Town4056 Branch, The High Street, Town4056, AC88 2YZ|
This is a sample loader script to process a bank name mapping file BANKMAP.txt (similar to that in Appendix A) and branch data file BRANCHES.txt as shown above. The data files must be in one of the directories defined as database directories for PL/SQL file I/O. See My Oracle Support Knowledge Document 2525754.1, Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2.
If you use the sample bank name mapping data from Appendix A, you must ensure that the codes correspond to actual codes in you application. If you use the sample branch data from this section , then the data file must contain one record for each line.
set serverout on size 1000000
REM
REM Example program for inserting GB branch records into
REM PAY_BANK_BRANCHES. Oracle does not support this code.
REM Customers can modify this code for their own use.
REM
REM The code reads a mapping file that contains bank names
REM (from the third party bank branch information) and the corresponding
REM GB_BANKS lookup code. This mapping code is used to build a
REM name-to-code mapping table.
REM
REM The code then reads the third party branch information table and
REM inserts, or updates, records into PAY_BANK_BRANCHES using the
REM GB-specific APIs
REM
declare
g_file_dir varchar2(2000) := '&DIR';
--
-- Types for holding bank name -> hr_lookup code map
--
type t_name2code is record
(name varchar2(100)
,code varchar2(30)
);
type t_name2code_table is table of t_name2code index by
binary integer;
--
-- Name-To-Code mapping table.
--
g_N2C_TBL t_name2code_table;
--
-- Record separator
--
SEPARATOR constant varchar2(1) := '|';
--
-- Comment line start character
--
COMMENT_START constant varchar2(1) := '#';
--
/* ----------------- build N2C_TBL ---------------------
Name: build N2C_TBL
Description: procedure to build the Name-To-Code mapping table
Comment lines in the file begin with '#'
The file record format is <Name>|<code>
For example:
Big Bank PLC|BIG_BANK
--
dir is a directory defined as a database directory for PL/SQL file I/O. See My Oracle Support Knowledge Document 2525754.1, Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2.
--
Notes: both fields are mandatory, otherwise lines are rejected.
----------------------------------------------------------------------- */
procedure buildN2C_TBL(dir in varchar2, file in varchar2) is
fhandle utl_file.file_type;
fbuf varchar2(2000);
more boolean := true;
i binary_integer := 1;
seppos number;
begin
--
-- Open the mapping file
--
fhandle := utl_file.fopen(dir, file, 'r');
while more loop
begin
utl_file.get_line(fhandle,fbuf);
--
-- Extract the bank name and code, avoiding comment lines
--
if instr(fbuf, COMMENT_START) < > 1 then
seppos := instr(fbuf, SEPARATOR);
if seppos > = 1 then
g_N2C_TBL(i).name := substr(fbuf, 1, seppos -1);
g_N2C_TBL(i).code := substr(fbuf, seppos +1);
i := i + 1
end if;
end if;
exception
--
-- NO_DATA_FOUND indicates EOF
--
when no_data_found then
utl_file.fclose(fhandle);
more := false
--
-- Error exception
--
when others then
utl_file.fclose(fhandle);
raise;
end;
end loop;
end buildN2C_TBL;
--
/* ---------------------- searchN2CTBL ----------------------------
Name: searchN2C_TBL
Description: Function to search the Name-To-Code mapping table.
If the function finds a match, it returns the lookup code.
If the function does not find a match, it returns NULL.
Notes:
The function uses a binary chop, when searching the mapping table.
Make sure that you order the mapping table alphabetically (lowest value first).
-----------------------------------------------------------------------------*/
function searchN2CTBL(sval in varchar2) return varchar2 is
low number;
high number;
mid number;
begin
--
low := 1
hi :=g_N2C_TBL.count;
while hi >= low loop
mid := trunc((hi + low)/2);
if g_N2C_TBL(mid).name = sval then;
return g_N2C_TBL(mid).code;
elsif g_N2C_TBL(mid).name > sval then
hi := mid -1;
else
low := mid +1
end if;
end loop;
return null;
end searchN2CTBL;
/*------------ parse_record ------------------------------
Name: parse_record
Description: procedure to parse a bank file line
Comment lines in the file begin with '#'. The file record format is:
Bank|Sort Code|Branch|Branch Address|Building Society Account
You must include all the separators, even if a field is blank.
For example, this record does not have a building society account:
Big Bank PLC|123456|My TownHigh Street |
Big Bank PLC, My Town High Street Branch, My Town, AB12 3CD |
Notes: Three fields are mandatory (Bank, Sort Code, and Branch).
The procedure ignores any lines that do not have the correct format.
---------------------------------------------------------------------*/
procedure parse_record
(p_line in varchar2
,p_ignore out nocopy boolean
,p_bank_code out nocopy varchar2
,p_sort_code out nocopy varchar2
,p_branch out nocopy varchar2
,p_long_branch out nocopy varchar2
,p_bs_acct out nocopy varchar2
) is
l_sep number
l_bank varchar2(2000);
l_line varchar2(2000); := p_line
l_val varchar2(2000);
begin
p_ignore := false
--
-- Ignore comment lines
--
if instr(p_line, COMMENT_START) = 1 then
p_ignore := true;
return;
end if;
--
-- Field 1: Bank Name - (need to map to get code).
--
l_sep := instr(l_line, SEPARATOR);
-- Ignore line without separator.
if l_sep = 0 then
p_ignore := true
return;
end if;
-- Extract the bank and find the code.
l_bank := substr(l_line, 1, l_sep -1);
if l_bank is not null then
l_val := searchN2CTBL(l_bank)
else
l_val := null;
end if;
-- Ignore lines where the codes cannot be extracted.
if l_val is null then
p_ignore := true;
return;
end if;
p_bank_code := l_val;
--
-- Field2: Sort Code (mandatory field)
--
l_line := substr(l_line, l_sep +1)
l_sep := instr(l_line, SEPARATOR);
-- Ignore if the separator is not found.
if l_sep = 0 then
p_ignore := true;
return;
end if;
l_val := substr(l_line, l_sep -1);
-- The value is mandatory
if l_val is null then
p_ignore := true;
return;
end if;
p_sort_code := l_val;
--
-- Field 3: Branch (mandatory field)
--
l_line := substr(l_line, l_sep +1)
l_sep := instr(l_line, SEPARATOR);
if l_sep = 0 then
p_ignore := true;
return;
end if;
l_val := substr(l_line, l_sep -1);
if l_val is null then
dbms_output.put_line('Branch is NULL');
p_ignore := true;
return;
end if;
p_branch := l_val;
--
-- Field 4: Long Branch (non-mandatory field).
--
l_line := substr(l_line, l_sep +1)
l_sep := instr(l_line, SEPARATOR);
if l_sep = 0 then
p_ignore := true;
return;
end if;
p_long_branch := substr(l_line, l, l_sep -1);
p_branch := l_val;
--
-- Field 5: Building Society Account (non-mandatory field).
--
p_bs_acct := substr(l_line, l_sep + l);
end parse_record
/*--------------------------- process_records -----------------------------
Name: process_records
Description: procedure to process the bank branch records.
dir is a directory defined as a database directory for PL/SQL file I/O. See My Oracle Support Knowledge Document 2525754.1, Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2.
-----------------------------------------------------------------------------*/
procedure process_records(dir in varchar2.file in varchar2) is
--
-- Cursor to determine whether the record already exists in PAY_BANK_BRANCHES.
--
cursor branch_exists(p_sort_code in varchar2) is
select null
from pay_bank_branches
where branch_code = p_sort_code
and legislation_code = 'GB'
;
fhandle utl_file.file_type;
fbuf varchar2(2000);
more boolean := true
i binary_integer := 1;
l_ignore boolean;
l_bank_code varchar2(2000);
l_sort_code varchar2(2000);
l_branch varchar2(2000);
l_long_branch varchar2(2000);
l_bs_acct varchar2(2000);
l_exists varchar2(2000);
begin
fhandle := utl_file.fopen(dir, file, 'r');
while more loop
begin
utl_file.get_line(fhandle,fbuf);
--
-- Get the next record.
--
--
-- Blank out the non-mandatory parameters as the code uses NOCOPY
-- parameter passing
--
l_long_branch := null;
l_bs_acct := null
parse_record
(p_line => fbuf
,p_ignore => l_ignore
,p_bank_code => l_bank_code
,p_sort_code => l_sort_code
,p_branch => l_branch
,p_long_branch => l_long_branch
,p_bs_acct =>
);
i := i + 1;
if not l_ignore then
--
Determine whether an insert or update is necessary.
--
open branch_exists(l_sort_code);
fetch branch_exists into l_exists;
if branch_exists%notfound then
dbms_output.put_line('INSERT:'|| l_sort_code);
pay_bank_branches_pkg.insert_gb_row
(p_sort_code => l_sort_code
,p_bank_code => l_bank_code
,p_branch => l_branch
,p_long_branch => l_long_branch
,p_building_society_acct => l_bs_acct
);
else
dbms_output.put_line('UPDATE:'|| l_sort_code);
pay_bank_branches_pkg.update_gb_row
(p_sort_code => l_sort_code
,p_branch => l_branch
,p_long_branch => l_long_branch
,p_building_society_acct => l_bs_acct
);
endif;
close branch_exists;
--
-- Commit on every 100 records.
--
if mod(i, 100) = 0 then
commit;
end if;
end if;
exception
--
-- NO_DATA_FOUND indicates EOF
--
when no_data_found then
utl_file.fclose(fhandle);
more := false
--
-- Error exception
--
when others then
utl_file.fclose(fhandle);
raise;
end
end loop;
--
-- Commit at the end
--
commit;
end process_records
begin
--
-- Build the search table
--
buildN2C_TBL(dir => g_file_dir, file => 'BANKMAP.txt');
--
-- Process the bank records.
--
process_records(dir => g_file_dir, file => 'BRANCHES.txt');
end;
/