FuiSQL Conversion Workbench
A VS Code extension for conversion teams: load FBDI files into a local database, run field-level checks and
live Oracle Fusion lookups, and get an error report before you upload and wait for the import to reject rows.
Companion to FuiSQL. It copies FuiSQL's connection stack (password and OAuth/JWT sign-in, secrets in VS Code
Secret Storage, BI Publisher SOAP) so it runs standalone, and it uses the same BI Publisher report
(/Custom/FuiSQL/FuiSQLReport.xdo, parameter sql_query). If FuiSQL already works against an environment,
this works too.
Connections are stored separately. If FuiSQL is installed, run Import Connections from FuiSQL (also in the
sidebar and under Manage Connections): FuiSQL shows a picker, you tick the connections to share, and they are
copied here with their saved passwords or keys. Importing again updates a connection with the same host, user and
sign-in type instead of duplicating it. This needs a FuiSQL version that includes the sharing API.
Workflow
The FuiSQL sidebar is five numbered steps, with a Next: line at the top that always names what to do now.
- Connect to Fusion (optional): file checks work without it; lookups need a connection. Import from FuiSQL here.
- Choose the FBDI: every FBDI with rules is listed by its proper name ("Lease Contract Import"), with its sheets under it.
The standard FBDIs ship with the extension. Add an FBDI from its template reads any Oracle FBDI
.xlsm and
asks for a name; Customise for this project layers your own rules on a standard.
- Load your files:
.csv, .zip of CSVs, or .xlsm/.xlsx. Sheets are grouped under their FBDI.
- Validate: one item per FBDI (or all), each showing its last result. Validating one FBDI leaves the others' results alone.
- Review the report: opens one FBDI at a time, as tabs. Each shows how many rows are affected, what to fix first
(with the most common values and what each check means), per-sheet counts, and every finding. Click a finding to see its row. Right-click it and choose Show the query behind this to see exactly what ran: the read-only query sent to Fusion (for lookups), the SQL run on the local copy of your file, or, for built-in format rules, the test applied in code.
Export CSV/HTML exports the FBDI you are reading.
A sheet named after its table (for example PJC_TXN_XFACE_STAGE_ALL) can have a readable title
("title": "Miscellaneous Expenditure Items"), set in a rule pack or in an overlay's override.
Standard rules and project rules
Rules are layered. The standard layer is one document per FBDI in rules/standard/ (shipped with the extension and
loaded automatically): format, lengths, required columns, valid values, Fusion lookups and relations that hold for every
customer. A project layer sits on top and holds only what that project changes. Currently available: lease-import
(Lease Contract Import).
To customise for a project, run Create Project Rules for an FBDI. It writes fbdi-rules/<id>.project.json into the workspace
(picked up automatically; files elsewhere are added with Add Rules File):
{
"extends": "lease-import",
"overrides": [{
"pack": "Leases",
"columns": { "Lease Name": { "maxLength": 40 }, "Lease Number": { "required": true } }, // null removes a property
"checks": [{ "id": "ACME_NAME_PREFIX", "severity": "warning", "description": "Lease names start with ACME-",
"sql": "SELECT _row, 'Lease Name' AS \"column\", lease_name AS value FROM {{table}} WHERE lease_name NOT LIKE 'ACME-%'" }],
"relations": [{ "id": "leases_to_rights", "child": "Rights", "parentKey": ["Interface Lease ID"], "childKey": ["Interface Lease ID"], "parentWithoutChild": "warning" }],
"disable": { "checks": ["DOCUMENT_END_BEFORE_START"], "columns": ["Reference 1"] }
}]
}
columns changes properties of one column; lookups, checks and relations add an entry or replace the one with the same id; disable
switches off standard columns, checks, lookups or relations. Later files win.
- Anything that matches nothing (a misspelt pack, column or id) is reported as a warning instead of being ignored.
- In a check,
{{table}} is the loaded table of the pack the check belongs to and {{table:Other Pack}} is another pack's, so one check works whether
the data came from an XLSM sheet or a CSV. A check whose table was not loaded is skipped. Columns are the header names in lower_snake_case.
- A plain rules file (not an overlay) whose
files match a standard pack replaces it. The warning says so; prefer an overlay.
- The sidebar shows each pack's layer: standard, customised, or project.
Adding or refreshing a standard FBDI (maintainers): put hand-written checks in rules/curated/<id>.json (same format as an overlay), then
npm run rules:build -- <template.xlsm> <id> "<Display name>" "<Product>"
This generates rules from the template, applies the curated layer, and writes rules/standard/<id>.json deterministically. Never edit the
standard file by hand: npm test fails if it differs from what the script produces. The template itself is not kept in the repository, only a hash of it.
Needs FuiSQL's schema database to record each sheet's interface tables. The standard files include Oracle's column descriptions from the template comments.
Control files, the template's macro, and the upload ZIP
Each FBDI template carries a macro that turns its sheets into the CSV files of the upload ZIP, and Oracle publishes a SQL*Loader
control file for each CSV. The extension reads both and ships what they say. The macro is read as text from xl/vbaProject.bin
and never run.
- The macro says what each CSV is called (
FaMassAdditions.csv; sometimes unlike the control file's name, for example
PjcTxnXfaceStageAll_misc.csv against PjcTxnXfaceStageAll.ctl, and both are accepted), the name of the ZIP, and, for a few
FBDIs (Project Miscellaneous Costs, Payables Payment Request), how it rearranges the sheet's columns into the CSV's order.
- The control file says the interface table, the CSV columns in order, which fields Fusion fills in itself, the date format
(
YYYY/MM/DD) and which columns are numbers. Its DB column names replace the template comments' where they disagree (the
comments for account and location segments point at the _ID column). One file can hold several INTO TABLE sections (one per
transaction or record type); the section that fits the sheet is used.
- Both are checked against each other. Where a control file does not line up with its sheet, the standard keeps the template's
own names and marks the pack
csv.controlMismatch, and the build says so. No mismatch is hidden.
npm run rules:build-all builds every FBDI in scripts/fbdi-catalog.json from Oracle's files in samples/<folder>/ (the catalog
names each FBDI's page, template and control files; the files themselves are not kept in this repository). One FBDI:
node scripts/build-standard.js <template.xlsm> <fbdi-id> "<Name>" "<Product>" "" <folder-with-.ctl-files>
Create Upload ZIP from an FBDI Template (sidebar, step 5) does what the macro does without Excel: one CSV per sheet that has
data, header dropped, rows down to the first row with an empty first column (as the macro stops there too), columns in the CSV's
order, dates as YYYY/MM/DD, a final END column, UTF-8, CRLF, zipped under the macro's name. A value with a comma, quote or line
break is quoted, which the macro's Windows path does not do. For an FBDI the rules do not know, the file's own macro names the CSVs.
Not done yet: FBDIs that merge several sheets into one CSV (Receivables lockbox, Intercompany), and a few whose CSV column order
is unverified (they are listed as mismatches by the build).
Watching the load in Fusion
After you upload a ZIP and run the import, step 6 of the sidebar shows what happened to the rows, using the same connection as the lookups.
- Check Fusion interface tables counts the rows in the FBDI's interface tables per load request and status (every FBDI table has
a
LOAD_REQUEST_ID), compares them with the previous check, and notes load requests whose rows are all gone since then. Imported
rows are often removed from the interface tables, so "gone" means imported or purged, not proven imported. It then reads the
rejections of the newest load requests and traces each to the row of your loaded file when it can.
- Rejection sources are in
rules/monitor/<fbdi id>.json: Leases (FLA_INTERFACE_REJECTIONS), Payables invoices
(AP_INTERFACE_REJECTIONS) and AutoInvoice (RA_INTERFACE_ERRORS_ALL). An FBDI whose interface rows carry their own error column
(Fixed Assets, GL budgets, ...) is read from that column. For the rest only counts are shown until a source is added.
npm test checks every count and rejection query against the Fusion schema.
- Read an import status report reads the text output of the import job (for example the "Expense Lease Contract Import Status
Report") and traces each rejection to the row of your file, without a connection. The lease report in the tests gives the same 463
rejections, in the same groups, as were counted by hand.
- Nothing is written to Fusion: these are SELECTs only.
Rules from rejections
What Fusion rejects can become checks that find the same rows before the next upload. Turn rejections into rules (sidebar, step 6)
works on the rejections last read from an import status report or from Fusion, and on the file you loaded. It understands three
kinds of message:
- between columns: "the primary accumulated amortization value that's less than the primary gross right-of-use value". The
columns are found by the words; if one phrase is not a column ("the amortization end date"), the columns that could be it are
tried and the data picks the one that fits.
- against a date in the message: "an option start date after 30-04-2040". The column that holds that date, on the row or its
parent, is found by looking in the rejected rows.
- unique: a duplicate check, and when the table is known in the Fusion schema, a lookup that finds a value already in Fusion.
Each draft is run on the loaded file and measured: how many of the rejected rows it catches, how many other rows it flags that Fusion
also rejected for something else, and how many it flags that are not in the report. A check that skips blank and zero values is
chosen when flagging them would mark rows Fusion accepted. A message that cannot be turned into a check (a date Fusion calculated
itself, a "valid value" with no known table) is listed with the reason. Nothing is added until you approve it; approved checks go
into the project's overlay (fbdi-rules/<fbdi>.project.json) with the rejection message as their help, and run with the next validation.
On the lease import report in the tests, the four drafted comparison checks catch every rejected row (215 of 215, 14 of 14, 13 of 13,
4 of 4) with no other rows flagged, and over the whole loaded file flag the same 285, 6, 253 and 21 rows as the hand-written ones.
Generating rules from an FBDI template
Generate Rules from XLSM Template reads the template itself and writes a rule pack per worksheet:
- columns and
* required markers from the header row;
- type, length, decimals,
Y/N flags, numeric ranges and code lists from each header cell's comment (the comment holds
DB_COLUMN, VARCHAR2(240) and a description). "Possible values are Finance Lease, Operating Lease" is kept as text only: those are
display names, and a file may carry codes. A column the template marks obsolete gets a warning if it holds data;
- Fusion lookups for columns the reference catalog knows (business unit, legal entity, ledger, currency, supplier number,
project number, user) and for any "populated from the ORA_X lookup" statement;
- parent/child relations from the
Interface ... ID columns that tie sheets together (Leases to Assets, Payments to Payment Lines, ...).
Child rows without a parent are errors; a parent without children is info, because a lease with no Rights sheet rows is often fine.
Verify Rules Against Fusion Schema checks the rules offline against FuiSQL's schema database (needs FuiSQL installed, with its
schema built): every table, view and column in every lookup query exists; every template column exists in its interface table
(FLA_LEASES_INT, ...) with a compatible data type. Test Rules Against Fusion then runs each lookup query on the connected
environment, one row each, and reports Fusion's own error for any that fail. The offline check proves the names are real; only the live test
proves a query means what you need and that your user may read it.
On an .xlsm, length and type findings from template comments are warnings: the template's macro can convert a typed value
(a "Line Type" of BASE_RENT becomes a one-character flag) when it writes the CSV, so check the generated CSV. Required stays an error.
What is checked
| Check |
Source |
REQUIRED, MAX_LENGTH, TYPE, SCALE, DATE_FORMAT, ALLOWED_VALUE, PATTERN |
column rules |
WHITESPACE, CONTROL_CHAR, LINE_BREAK |
always, on every cell |
STRUCTURE |
wrong number of fields in a CSV row; pack column missing from an XLSM header |
DUPLICATE_KEY |
uniqueKeys, as SQL on the local DB |
LOOKUP |
value not in a result set queried from Fusion |
ORPHAN_CHILD, MISSING_CHILD |
relations: a child row whose key has no parent row, and a parent row with no child row |
| your own ids |
checks: SQL over the loaded data |
FBDI CSVs have no header row, so columns are identified by position from the rule pack. XLSM sheets are read by
header: the row with the most filled cells in the first 15 rows, * meaning required.
Rule packs
{ "packs": [{
"name": "GL Journal Import",
"files": ["GlInterface"], // file or sheet name, no extension; trailing * = prefix
"columns": [ { "name": "Ledger ID", "required": true, "type": "integer", "lookup": "ledger" }, ... ],
"uniqueKeys": [["Ledger ID", "Accounting Date"]],
"relations": [{ "id": "LINES", "child": "ApInvoiceLinesInterface", "parentKey": ["Invoice ID"], "childKey": ["Invoice ID"] }],
"lookups": [{ "id": "ledger", "sql": "SELECT ledger_id, name FROM gl_ledgers WHERE ledger_id IN ({{values}})" }],
"checks": [{ "id": "MY_CHECK", "severity": "warning", "sql": "SELECT _row, 'Col' AS \"column\", col AS value FROM f_glinterface WHERE ..." }]
}]}
- Lookups run on Fusion. The first returned column is the valid value.
{{values}} is replaced by the distinct values
in your file (500 per query), so large tables are never pulled whole. Results are kept locally as ref_<id>, so
checks can join them (for example ledger currency against journal currency).
- Relations cross-check two files, for example invoice headers and lines. Declare it on the parent pack:
child is the
child file or sheet name, parentKey and childKey are matching column lists (composite keys must match as a whole).
Both directions run by default; set childWithoutParent or parentWithoutChild to warning, info or off for the
ones you expect (a header with no lines can be legitimate). Keys are compared trimmed and case-sensitively unless
caseInsensitive is true, and blank keys are left to required. If either file is not loaded the relation is
skipped with one info finding rather than reporting every row. See examples/ap-invoices.rules.json.
- Custom checks are one read-only
SELECT against the local DB. Return _row, and optionally column, value, message.
Tables are f_<file> and columns lower_snake_case (Accounting Date becomes accounting_date). Every value is TEXT:
cast it yourself.
- Mistakes in a pack (unknown column in a key, bad regex, undefined lookup) are reported when it is loaded.
Notes
- Lookup SQL runs through your BI Publisher report, so it can only read what that report's data model and the
connected user may read. If a lookup fails, the report shows a
LOOKUP_FAILED finding with Fusion's ORA error; other checks still run.
- Without a connection, lookups are skipped and listed as
LOOKUP_SKIPPED. File checks still run.
- The shipped example pack is illustrative: confirm column names, order and lengths against your release's FBDI template.
- Local data lives in the extension's global storage (
fbdi.db). "Clear Loaded Data" keeps past reports.
- As in FuiSQL, TLS certificate validation is relaxed for Fusion calls (dev environments).
Develop
npm install
npm test # compiles, then runs the node test suite
Press F5 in VS Code to launch an Extension Development Host.
License
Proprietary software. Copyright © 2026 FuiSQL. All rights reserved. The license is in the extension's LICENSE.md.