Generate BPA Files V2 — User Manual

Tool: Generate BPA Files V2  |  Version: 2.0  |  Platform: Microsoft Excel (Windows)

1. What Does This Tool Do?

The Generate BPA Files V2 macro automates the production of Oracle ERP upload files from a daily RFQ (Request for Quotation) spreadsheet. Without this tool, a user would need to manually copy data from the RFQ file into several Oracle upload templates — a process that is slow and error-prone.

This tool handles that automatically:

2. Workbook Overview

The Run Sheet

When you open the workbook, go to the Run tab. This is the main control panel. All five buttons are here.

The Workbook Tabs

3. First-Time Setup

Complete these steps once before your first run. You will not need to repeat them.

3.1 — Fill In the Run Sheet

Open the Run tab. The left column shows labels; type your values in column B.

3.2 — Set Up the Email Template

Open the Email_Template tab and fill in the following rows:

3.3 — Populate the BUYER_ID Tab

Open the BUYER_ID tab. This table maps each buyer's full name (as it appears in the RFQ file) to their Oracle Buyer ID code. There are two columns:

• Buyer Full Name — must match exactly how the name appears in the source file (not case-sensitive).
• BUYER_ID — the Oracle buyer code for that person.

3.4 — Create Your Template Configs (Open Mapper)

This is a one-time step. You need to tell the tool how to map data from the To Do sheet into each of the four output templates. This is done using the Open Mapper button. See Section 6a for full instructions.

4. The Two-Step Workflow

Every time you need to generate BPA files, you follow two steps:

5. Step 1 — Build To Do Sheet

This step reads your RFQ source file, validates every row, and writes the clean result to the To Do tab.

What Happens Behind the Scenes

• Rows are skipped if the BPA number is not exactly 9 digits.
• Rows are skipped if the price is zero or negative.
• Rows are skipped if the buyer name is not found in the BUYER_ID tab.
• Rows are skipped if the lead time value is missing or cannot be parsed as a number.
• All skipped rows are recorded in the Log tab with the reason.
• For each vendor, the macro searches the Sourcing_Rule tab and fills in the best matching sourcing rule name. If multiple rules match, a dropdown is placed in the cell so you can choose.

6. Step 2 — Generate Output Files

Before you can use Run Template Batch, you need to create one saved configuration for each output template. You do this once using Open Mapper. You need to create four configs:

Follow these steps for each of the four configs:

Once your four configs exist, this is all you need to do to generate the output files.

7. Reviewing and Editing the To Do Sheet

After clicking Build To Do, always open the To Do tab before running the batch. The most important column to check is Sourcing_Rule_Name.

Sourcing Rule Name

The macro automatically matches each vendor to a sourcing rule from the Sourcing_Rule reference tab using the vendor name, currency, and rank. The result is placed in the Sourcing_Rule_Name column.

• If a single best match is found, it is written directly into the cell.
• If multiple matches are found, a dropdown arrow appears in the cell. Click the dropdown and select the correct sourcing rule.
• If no match is found, the cell is left blank. You can type the rule name manually or leave it blank (the row will still be processed, but without a sourcing rule).

Other Columns to Check

Checking Skipped Rows

If the row count in the To Do sheet seems lower than expected, check the Log tab. Every skipped row appears there with:

• The row number from the source file.
• The BPA number and item index (if available).
• The reason it was skipped (e.g. Invalid BPA (not 9 digits), Buyer missing, etc.).

8. Template Tabs Explained

The four TEMP_ tabs are blank Oracle upload templates embedded in the workbook. The macro copies data into a fresh copy of these templates for each output file — the originals are never modified.

9. Reference Tabs

BUYER_ID

Maps buyer full names to Oracle buyer codes. The macro uses this during Build To Do to resolve the buyer code for each row.

Sourcing_Rule

Contains Oracle sourcing rule data used to auto-populate the Sourcing_Rule_Name column on the To Do sheet during Build To Do. This tab is normally populated using the Oracle Refresh button, but you can also edit it manually to add or adjust rules — for example, to set preferred Rank values.

How Sourcing Rule Matching Works

When multiple sourcing rules exist for the same vendor, the macro selects the best one using the following priority order (highest to lowest):

1. Currency match — the rule whose Currency matches the row's currency in the RFQ is preferred.
2. Rank — among rules with equal currency match, the one with the lowest Rank number wins.
3. Fuzzy vendor name score — if Rank is blank for all candidates, the closest name match is used.
4. First match — if everything else is equal, the first row in the tab wins.

If more than one rule is equally good, a dropdown is placed in the Sourcing_Rule_Name cell on the To Do sheet so the user can make the final selection manually.

Setting Up Rank Values

The Rank column gives you direct control over which sourcing rule is selected when a vendor has multiple options. This is the recommended approach when you always want a specific rule for a vendor.

BPA_Filters

Stores column filter definitions that control which rows from the source file are included in the To Do sheet. The tab has three columns:

Two Types of Rows: Named Presets and Active

There are two distinct roles a row in this tab can have, determined entirely by the Set_Name value:

How the Workflow Works

1. Open Build To Do and set your column filters in the form.
2. Save Set — type a name (e.g. BPA_STANDARD_YES_BLANK) and click Save Set. The macro writes those filter rows to the tab under that name. This is your saved template — you can reload it any time.
3. Click Run — the macro takes whatever filters are currently in the form and writes them to the tab under the name Active, overwriting the previous Active rows. Only the Active rows are applied during Build To Do.
4. Next time — open Build To Do, click Load Set, pick your saved preset (e.g. BPA_STANDARD_YES_BLANK), and the form refills with those values. Click Run and they become Active again.

BPA_SkipRules

Defines the validation rules applied to each source row during Build To Do. Each row in this tab is one rule. Rules with Enabled = YES are active.

10. Deployment Scenarios and Sharing the Workbook

The Workbook is Self-Contained and Portable

Every configuration the macro needs — column mappings, filter sets, skip rules, buyer IDs, email templates, and output paths — is stored inside the workbook itself, across its various tabs. This means the workbook can be copied, emailed, or placed on a shared drive and it will work immediately, with no installation and no external configuration files to manage.

Each user who receives a copy has complete, independent control over their own setup. Changes one user makes to their copy have no effect on anyone else.

Adjusting the Output Path Without Recreating Configs

When a workbook is passed from one person to another, the saved configurations are already complete — the column mappings, filter settings, and file patterns are all correct. The only value that typically needs updating is the Output_Path, which points to a folder on the original user's computer.

You do not need to delete and recreate configs just to change where files are saved. Go directly to the Saved_Configs tab, find the Output_Path column on the meta-row of each config (the bold blue header row for each config block), and type your own folder path.

Use Case 1 — Individual User, Personal Workbook

This is the standard setup. Each user has their own copy of the workbook on their computer.

Use Case 2 — Team Sharing a Single Workbook

A team can share one workbook placed on a network drive, SharePoint sync folder, or shared OneDrive location. Because all configuration lives inside the workbook, the team immediately benefits from any improvements made by one member.

OneDrive and SharePoint Compatibility

The macro works with OneDrive and SharePoint provided the files are synced to a local folder on your computer. The macro uses standard Windows file paths — it cannot access web URLs directly.

11. Send Email

After the batch run is complete and you have confirmed the output files are correct, click Send Email.

The macro:

1. Reads the To, CC, Subject, and Body from the Email_Template tab.
2. Reads the list of files created in the last batch run from File_Log.
3. Opens a new email draft in Microsoft Outlook with all output files attached.
4. Replaces {DATE} in the subject with today's date, and {FILES} in the body with the file names.

12. Oracle Refresh

The Sourcing_Rule tab must be kept up to date for the sourcing rule matching to work correctly. When Oracle data changes (new vendors, updated rules, or site code changes), use Oracle Refresh to update it.

13. Troubleshooting