Skip to content
| Marketplace
Sign in
Visual Studio Code>Other>FuiSQL Conversion WorkbenchNew to Visual Studio Code? Get it now.
FuiSQL Conversion Workbench

FuiSQL Conversion Workbench

Preview

FuiSQL

|
2 installs
| (0) | Free
Load FBDI files (CSV, ZIP, XLSM) into a local database and pre-validate them against rules and live Oracle Fusion lookups before you upload. Produces an error report.
Installation
Launch VS Code Quick Open (Ctrl+P), paste the following command, and press enter.
Copied to clipboard
More Info

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.

  1. Connect to Fusion (optional): file checks work without it; lookups need a connection. Import from FuiSQL here.
  2. 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.
  3. Load your files: .csv, .zip of CSVs, or .xlsm/.xlsx. Sheets are grouped under their FBDI.
  4. Validate: one item per FBDI (or all), each showing its last result. Validating one FBDI leaves the others' results alone.
  5. 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.

  • Contact us
  • Jobs
  • Privacy
  • Manage cookies
  • Terms of use
  • Trademarks
  • Your Privacy Choices
  • Consumer Health Privacy
© 2026 Microsoft