Wire Payment Review

User Manual & Job Aid  |  Version 1.0  |  March 2026

1   What is the Wire Payment Review Tool?

The Wire Payment Review (WPR) tool is a macro-enabled Excel workbook that automates the quality-review process for outgoing wire payment batches. Each batch arrives as a bank-formatted template file — one format for RBC, a different format for US Bank — containing payment instructions that include vendor names, bank account numbers, SWIFT codes, and operating-unit identifiers. Before a batch can be approved and submitted, a reviewer must verify three things:

1. That each payment is associated with a recognised Oracle supplier number.
2. That the bank account number on the payment instruction matches a known account in the Oracle bank account registry for that supplier.
3. That none of the payees are inactive (deactivated) suppliers in the Oracle system.

Without automated tooling, these checks are performed row by row against the Oracle bank account register — a process that is time-consuming, error-prone, and difficult to document consistently. The WPR tool replaces that manual process with a structured, repeatable workflow driven by a single button.

What the tool does, step by step

2   Prerequisites — What You Need Before Running

Confirm that your environment and input files meet the requirements below before running any step. Attempting to run a step without the required inputs will produce a descriptive error message rather than silently writing incorrect data, but it is faster to check in advance.

2.1   Excel version and macro security

Supported versions

The WPR tool is compatible with Excel 2016, 2019, 2021, and Microsoft 365 on Windows. It is not compatible with Excel for Mac (the file dialog and certain Windows API calls are Windows-only). Excel Online (browser) is not supported — macros do not run there.

Enabling macros

When the workbook is opened, Excel displays a yellow security bar below the ribbon reading "SECURITY WARNING — Macros have been disabled." Click Enable Content. Without this, no buttons will function. If the bar does not appear, macros may have been silently blocked by Group Policy — contact your IT support team.

Adding a Trusted Location (recommended)

To avoid the Enable Content prompt on every open, add the folder containing the workbook to Excel's Trusted Locations: File > Options > Trust Center > Trust Center Settings > Trusted Locations > Add new location. Only do this for a folder you control. Once trusted, the workbook opens with macros enabled automatically.

2.2   Input file 1 — Bank wire template

What it is

The wire template file is the batch payment instruction file produced by the bank or the payments system. The tool supports two template formats:

File format

Both template types can be in any of the following formats: .xlsx, .xls, .csv, or .txt. The file must be closed before importing — Excel cannot read a workbook that is open in another window or another Excel session.

HSBC template

HSBC is listed in the Import Wizard (button 3) but the processing pipeline is not yet implemented. The data import step will place the file contents on the HSBC_Wire_Temp tab, but the Extract, Fuzzy Match, and Account Review steps will not process that tab until the HSBC pipeline is built in a future release.

2.3   Input file 2 — Oracle bank account registry

What it is

The Oracle bank account registry is an export from Oracle of all active bank accounts associated with vendors in the operating units you process. It is used to:

• Resolve vendor names to Oracle supplier numbers (via Fuzzy Match).
• Provide a list of all valid bank accounts per supplier (via Account Review).

Where it is referenced

The registry file is not imported via a file dialog. Its location is configured directly on the Run sheet:

How often it needs updating

The Oracle registry changes infrequently — typically after each quarterly account maintenance cycle. Once the registry has been loaded for the quarter, it does not need to be reloaded for every daily batch. Re-run the Rebuild Registry step only when the underlying Oracle file has been refreshed. See Section 16 — Step Independence for a full decision table.

2.4   Input file 3 — Inactive vendor list (optional)

What it is

The inactive vendor list is an Oracle report of all suppliers that have been deactivated, together with their deactivation dates. This file is used by the Inactive Check step to flag any payments destined for vendors that are no longer active in Oracle.

Expected format

The file must contain at minimum three columns with recognisable headers: Vendor Name (or VENDOR_NAME, Supplier Name), Vendor Number (or VENDOR_NUMBER, Supplier Number), and Vendor Inactive Date (or VENDOR INACTIVE DATE, Inactive Date). Column names are matched case-insensitively and several common synonym forms are recognised automatically.

Title row handling

Many Oracle inactive vendor reports are formatted with a large merged title cell in row 1 (for example, "Oracle AP Inactive Suppliers — Exported 14-Mar-2026") above the actual column headers in row 2. The Import Wizard automatically detects and deletes this title row after import. You do not need to remove it from the source file first.

When to update

Import a new inactive vendor list whenever Oracle issues an updated deactivation report — typically quarterly. Once imported, the Inactive_List_Unique tab persists across daily runs until explicitly overwritten by a new import.

3   Quick Start — Typical Workflow

A complete first-run from empty workbook to archived output takes approximately 15 minutes for a standard batch. Subsequent daily runs with existing registry data take under 5 minutes.

First run of the quarter

Subsequent daily runs (registry already built)

4   First-Time Setup

The WPR workbook ships with all VBA modules installed but requires a one-time setup procedure before the buttons on the Run sheet are connected to the macros.

4.1   Import the UserForms

The Run sheet buttons depend on two UserForm dialog boxes: frmImportWizard and frmRunWizard. These forms must be imported into the workbook once after it is first received. This is a one-time step per workbook.

4.2   Assign macros to buttons on the Run sheet

The buttons on the Run sheet must be connected to their macros once after the forms are in place.

To assign a macro to a button: right-click the button shape on the Run sheet and choose Assign Macro…. Locate the macro name in the list, select it, and click OK. Repeat for each button. Save the workbook when all buttons are assigned.

5   The Run Sheet — Buttons and Configuration Cells

5.1   Buttons

5.2   Configuration cells (column K)

All runtime settings are stored in the K column of the Run sheet. These values persist between runs — set them once when you first configure the workbook and update only when the underlying files or operating parameters change.

6   Phase 1 — Data Import Wizard

The Data Import Wizard (Import Data) loads raw data into the workbook's input tabs in preparation for processing. The wizard presents a menu of five data types. Select one type per import cycle; click Import Another? after each import to continue loading more types.

6.1   How the Import Wizard works

6.2   Data type reference

7   Phase 2 — Wire Review Wizard

Clicking Wire Review opens the Wire Review Wizard (frmRunWizard). This form controls which templates are processed and which steps are executed in the pipeline.

7.1   Template section (top)

Two checkboxes select which bank templates to process in this run:

At least one template must be selected. Selecting both runs the full pipeline for each template sequentially. HSBC is listed but not yet implemented.

7.2   Steps section (bottom)

Five checkboxes control which pipeline steps execute. All checkboxes start unchecked — you select only the steps appropriate for this run.

8   Step 1: Extract — How It Works

The Extract step reads raw data from the bank template tabs, applies format-specific parsing and cleaning rules, and writes a standardised output to the reviewer extract tab. Each bank template type has a distinct layout, and the Extract step handles both.

8.1   RBC extraction

The RBC template uses a row-per-payment layout where each payment occupies one row. Column headers are located by name (case-insensitive), and the search scans rows 1 through 3 to accommodate different RBC export versions where the header row position varies.

The following cleaning operations are applied during extraction:

• BANK / TRANSIT fields: embedded commas, quotes, and multi-line cell content are stripped; the first and second digit-only tokens are extracted as BANK and TRANSIT respectively.
• CREDIT VENDOR NAME / CREDIT BANK: multi-line cell content is split on commas and newlines; the last meaningful token (not a two-letter country code) is used.
• SWIFT / TEMPLATE NAME: the first meaningful token is used.
• OU code: extracted from the DEBIT ACCOUNT field — the three-digit numeric suffix that follows the currency code (USD, CAD, EUR, AUD, GBP) in the comma-delimited account string.
• Leading zeros: numeric-looking fields (BANK, TRANSIT, Bank Account, SWIFT, OU) are stored as text to preserve leading zeros.

Output columns on RBC_Extract

8.2   US extraction

The US Bank template uses a vertical label-value format. Each payment is represented as a block of rows in columns A (label) and B (value). A new payment block starts whenever the label Template ID is encountered. The extraction step scans the template top to bottom, grouping rows into payment records.

Label matching is case-insensitive. Labels with trailing colons (e.g. Creditor Name:) or extra spaces are normalised before comparison, so minor formatting variations in the source file are handled automatically.

Output columns on US_Extract

9   Step 2: Rebuild Registry — How It Works

The Rebuild Registry step reads the external Oracle bank account file, filters it to the configured operating units, deduplicates the records, and writes two output tabs.

9.1   What it reads

The source file is identified by K2 (folder path) and K3 (filename) on the Run sheet. The OU filter in K4 is applied during load — only records whose operating unit code matches one of the comma-separated entries in K4 are retained. If K4 is blank, all records are imported without filtering.

9.2   What it produces

10   Step 3: Fuzzy Match — How It Works

The Fuzzy Match step resolves each vendor name on the extract tab to an Oracle supplier number by comparing it against the Account_List_Unique reference. It uses a Levenshtein-distance similarity algorithm with OU-bucket prioritisation to produce accurate results even when vendor names contain spelling variations, abbreviations, or punctuation differences.

10.1   Processing phases

10.2   Output columns

10.3   Thresholds explained

11   Step 4: Account Review — How It Works

The Account Review step compares the bank account number on each payment instruction against the Oracle bank accounts registered for that supplier and operating unit. The goal is to confirm that the account being paid is a known, authorised account in the Oracle system.

11.1   What is compared

11.2   Matching logic

For each row, the engine retrieves all bank accounts from Accounts_Numbers_Data where the Supplier Number and operating unit match the values on the extract row. The primary account field is then compared against each Oracle account using three methods in descending priority:

1. Exact string match: the full account string (including any leading zeros or formatting characters) matches exactly.
2. Exact digits match: all non-numeric characters are stripped from both the payment account and the Oracle account; the resulting digit strings are compared.
3. Approximate suffix match: the trailing digits of both strings are compared against a baseline length (16 digits preferred; 12 digits as fallback; configurable minimum). A match is declared if the suffix similarity meets the configured threshold (default 0.70).

If the primary field does not match any Oracle account via any of the three methods, the secondary field is tried using the same three methods in the same order.

11.3   Output columns

12   Step 5: Inactive Check — How It Works

The Inactive Check step identifies payments addressed to vendors that have been deactivated in Oracle. It runs after all template pipelines have completed, processing all selected templates in a single pass.

12.1   Three sub-steps

12.2   Output columns

Three columns are appended to the extract tab for each template processed:

12.3   Inactive threshold (0.86)

The inactive match threshold is deliberately set higher than the main fuzzy match threshold (0.86 vs. 0.72) because a false inactive-vendor flag on a payment is a significant finding that will trigger a manual hold or investigation. A lower threshold would produce more flags but also more false positives — vendor names that superficially resemble an inactive vendor's name without being the same entity. The 0.86 threshold is calibrated to flag only high-confidence matches. Reviewers should still verify flagged rows before acting.

13   Understanding the Output Sheets

13.1   RBC_Extract — column reference

After a full pipeline run (Extract + Fuzzy Match + Account Review + Inactive Check), the RBC_Extract tab contains the following columns:

13.2   US_Extract — column reference

13.3   Account_List_Unique — reference sheet

This tab contains the deduplicated, active Oracle vendor list used as the lookup source for Fuzzy Match. It is not intended for direct review, but it can be opened to verify that the registry loaded correctly. Expected columns: VENDOR_NAME, VENDOR_NUMBER, OPERATING_UNIT_NAME. Row count should reflect the number of unique vendor + OU combinations in the filtered registry.

13.4   Inactive_List_Unique — reference sheet

This tab contains the deduplicated inactive vendor list used by the Inactive Check step. Expected columns: Supplier Name, Supplier Number, Vendor Inactive Date, Operating Unit Name. One row per unique supplier number; the most recent deactivation date is retained when a supplier appears multiple times in the source.

14   Archiving Results

Clicking Archive Results saves a clean, macro-free copy of all output and reference sheets to the configured archive folder.

14.1   What is included

Every sheet in the workbook except the Run sheet is copied to the archive file. This includes all extract tabs, registry tabs, and the Log tab. The Run sheet is excluded because it contains macros and configuration cells that are not relevant to the reviewer. The archive file opens with the first extract tab in focus.

14.2   Filename construction

The filename is built from the K column configuration cells in the following pattern:

[K6]_[K7]_WIRE_TEMPLATES_REVIEW[_K8]_DDMMMYYYY.xlsx

14.3   Handling existing files

If a file with the same name already exists in the archive folder, the tool prompts you with three options:

• Yes — Overwrite: the existing file is replaced by the new one.
• No — Use a different name: you are prompted to enter an alternative filename. Do not include the .xlsx extension — the tool appends it.
• Cancel: the archive operation is aborted. No file is written or modified.

15   Resetting the Workbook

Clicking Reset Workbook returns the workbook to a blank state by deleting all working sheets and clearing all input tabs. This is equivalent to starting a brand-new review cycle.

15.1   What is deleted

• RBC_Extract
• US_Extract
• Accounts_Numbers_Data
• Account_List_Unique
• Inactive_Oracle_Account
• Inactive_List_Unique
• Log
• Any other sheets that are not in the protected list below

15.2   What is preserved

• Run — configuration cells (K column) are not cleared
• US_Wire_Template — cleared (all cells removed, but tab kept)
• RBC_Wire_Temp — cleared
• HSBC_Wire_Temp — cleared
• Oracle_Account — cleared

16   Step Independence — What to Re-Run and When

17   Common Scenarios

Scenario A — First run of the quarter with a new Oracle bank account file

The quarterly Oracle bank account export has arrived. This is the first wire batch of the new quarter.

1. Save the new Oracle file to the configured registry folder (K2 path).
2. Update K3 on the Run sheet to the new file name.
3. Click Import Data → button 1 (US) or 2 (RBC) → select the wire template file → confirm.
4. Click Wire Review. Select the relevant template(s). Tick all five steps: Extract, Rebuild Registry, Fuzzy Match, Account Review, Inactive Check (if an inactive file is available). Click Run.
5. Review output. Archive. Distribute.

Scenario B — Daily run, same registry as yesterday

A new wire batch has arrived but the Oracle registry has not changed since yesterday's run.

1. Click Import Data → import the new template file(s).
2. Click Wire Review. Select the relevant template(s). Tick: Extract, Fuzzy Match, Account Review. Leave Rebuild Registry and Inactive Check unticked.
3. Review output. Archive.

Scenario C — Account Review shows NO MATCH for a payment that looks correct

The extract shows NO MATCH for a payment where the reviewer is confident the account is legitimate. Common causes:

• Format mismatch: the payment template stores the account as 0012-3456-789 but Oracle stores it as 0012345678 9. The Comments column will show the Oracle accounts — manually compare the digit strings.
• Wrong supplier match: the Fuzzy Match returned an incorrect Supplier Num, so Account Review looked up the wrong supplier's accounts. Check the Matched Trading Partner and Match Score columns. If the supplier number is wrong, correct it manually in the Supplier Num column and re-run Account Review alone.
• New account not yet in Oracle: the account was recently created and the Oracle registry predates it. This is a genuine finding — escalate to accounts payable master data for verification.
• OU mismatch: the account exists in Oracle but under a different OU than the one on the extract row. Verify the OU value on the extract and compare it against the Oracle registry for that supplier.

Scenario D — Inactive Check flags a vendor that is still active

A vendor row on the extract shows an Inactive Supplier Num and Vendor Inactive Date, but the vendor is believed to be active.

• Check the Inactive Name column — it shows the name from the inactive list that was matched. Confirm that this is genuinely the same entity as the payee, or a coincidentally similar name.
• Check the Match Score in the Inactive Supplier Num column's corresponding row in the Notes column to confirm the score is above 0.86.
• The inactive list may be out of date — confirm with the Oracle team whether the supplier in the inactive list has since been reactivated. If so, request an updated inactive vendor export and re-run the Inactive Check step alone.
• If the flag is a false positive (different entity with a similar name), document the reason in the Comments column of the archived file and proceed.

Scenario E — Running RBC and US in the same session

A batch includes both an RBC template and a US Bank template, both to be reviewed in the same run.

1. Click Import Data. Import the US template (button 1), then click "Import Another" and import the RBC template (button 2).
2. Click Wire Review. Tick both RBC and US in the template section. Tick all required steps. Click Run.
3. The macro processes the RBC pipeline first, then the US pipeline. Rebuild Registry (if ticked) runs once before both pipelines — the same Account_List_Unique is shared.
4. Inactive Check (if ticked) runs once after both pipelines and processes both RBC_Extract and US_Extract.
5. Archive saves both extract tabs in a single file.

Scenario F — Re-running only Account Review after a manual supplier number correction

A reviewer has manually corrected a Supplier Num value on the extract tab and now wants Account Review to re-check that row against the corrected supplier.

1. Click Wire Review. Select the relevant template(s).
2. In the steps section, tick Account Review only. Leave all other checkboxes unticked.
3. Click Run. Account Review reads the Supplier Num column as it now stands, including any manual corrections.

18   Troubleshooting

"Form Not Found" when clicking Wire Review or Import Data

Cause: frmRunWizard or frmImportWizard has not been imported into the workbook.
Fix: Open the VBE (Alt + F11). Check under Forms in the project tree. If the form is missing, follow the import steps in Section 4. Alternatively, run WPRWizardSetup.SetupAllWizardForms via Alt + F8.

"Registry path / filename is blank" error when clicking button 4

Cause: K2 or K3 on the Run sheet is empty.
Fix: Enter the folder path in K2 and the filename in K3. Verify the path by opening Windows Explorer and navigating to it directly. If on a network share, ensure you are connected to the network before running.

"Source file not found" when clicking button 4

Cause: The path + filename combination in K2 + K3 does not resolve to an existing file.
Fix: Check for typos in K3 (filename is case-sensitive on some network shares). Verify that the file has not been renamed or moved. Use Windows Explorer to confirm the exact filename including its extension.

Account_List_Unique is empty after Rebuild Registry

Cause: The OU codes in K4 do not match any OU in the Oracle registry file, or K4 contains entries in the wrong format.
Fix: Open the Oracle registry file directly and find the column that contains the operating unit identifier. Note the exact value (e.g. 115 not 115_US). Update K4 to match. Re-run Rebuild Registry.

Fuzzy Match produces no Supplier Num values (all blank)

Cause: Account_List_Unique is empty (registry not built), or the OU value on the extract rows does not match any OU in the registry and the global fallback scan also found no matches above 0.72.
Fix:

1. Verify Account_List_Unique has data. If empty, run Rebuild Registry first.
2. Check the OU column on the extract tab. RBC_Extract column J and US_Extract column G should contain three-digit OU codes (e.g. 115). If these are blank or contain unexpected values, re-check the Extract step's OU source (DEBIT ACCOUNT field for RBC, Run!K6 for US).
3. Review the Notes column — if it reads "No match found above threshold", the names may be too different from Oracle's stored names. Consider whether the correct vendor names are present in the registry, or if the registry needs to be refreshed.

Excel appears to freeze or shows "(Not Responding)" during Fuzzy Match

This is normal for large registry scans and is not an error. During the global fallback scan, the engine iterates over thousands of candidate names. A progress bar is displayed during this phase. Excel may briefly show "(Not Responding)" in the title bar during intensive inner loops — this is Windows' standard behaviour when an application is busy and occurs even though the macro is running correctly. The workbook will become responsive again once the scan for that row completes (typically within a few seconds). Do not close or force-quit the workbook during this time. The Inactive Check step shows similar behaviour for the same reason.

Archive file has incorrect column headers (older column layout)

Cause: The extract tab still contains output from an older run, and the current run only executed a subset of steps (e.g. Account Review only). The columns written by the skipped steps (e.g. Extract) reflect the previous run's layout.
Fix: If a clean, consistent output is needed, tick the Extract step (and all subsequent steps) to regenerate the extract tab from scratch before archiving.

Progress bar does not appear during Fuzzy Match

Cause: frmProgress (the progress bar UserForm) has not been imported into the workbook.
Fix: Open the VBE (Alt + F11). Check under Forms for frmProgress. If missing, import frmProgress.frm from the WireProjectRework folder. See SETUP_INSTRUCTIONS.md for the full import procedure. The Fuzzy Match step runs correctly without the progress form — the absence of the bar is a display issue only and does not affect results.

19   Glossary