Lessons Learned Building Data Migration Tools for Oracle Fusion Cloud

If you have ever been involved in an Oracle Fusion Cloud implementation, you know that data migration is where optimism goes to die. The demos look clean. The documentation is comprehensive. And then you try to load 50,000 supplier records and discover that the third column in your CSV cannot contain a comma, even inside quotes, because the control file disagrees with your assumptions.

After building a full end-to-end migration pipeline -- from source extraction through FBDI generation, ESS job orchestration, and automated reconciliation -- here is what I wish someone had told me on day one.

Why FBDI matters more than you think

File-Based Data Import (FBDI) is Oracle's primary mechanism for bulk-loading transactional and setup data into Fusion. It is not glamorous. It is CSV files zipped together with very specific naming conventions, uploaded via REST or UCM, and processed by scheduled jobs.

But FBDI is the backbone of every Fusion implementation. Master data, open transactions, balances, configurations -- it all flows through FBDI templates. Understanding how those templates actually work (not just what the documentation says) is the difference between a migration that finishes on time and one that drags on for months.

The templates are position-based, not header-based. The CSV files must not contain a header row. Every column must align exactly with what the corresponding control file expects. Miss one column and everything after it shifts, producing cryptic validation errors that point you in the wrong direction.

The pitfalls nobody warns you about

Null handling is treacherous

Oracle Fusion has specific expectations for null values, and they vary by template. Some expect empty strings. Some expect #NULL. Some expect the literal text NULL. Some expect you to simply omit the column entirely, which is impossible when columns are position-based.

The only reliable approach is to study the actual control file for each template and test every permutation. Documentation will get you 80% of the way. The last 20% is trial and error.

Date formats are not negotiable

Fusion expects dates in YYYY/MM/DD format for most FBDI templates. Not YYYY-MM-DD. Not MM/DD/YYYY. Not ISO 8601. If your source system exports dates in any other format, you need a transformation layer, and you need to validate it on every single date column, not just the obvious ones.

I have seen migrations fail because a single date column buried on line 47 of a 52-column template had the wrong format. The error message said "invalid value" with no indication of which column.

Lookup values must match exactly

Fusion validates lookup codes during import, and the validation is case-sensitive and space-sensitive. If the lookup code in Fusion is US_CHECKING and your file contains US_Checking, the row will fail. If the lookup was configured with a trailing space during setup (it happens), your file needs that trailing space too.

Build a lookup validation layer into your pipeline. Query the actual lookup values from Fusion via BIP or REST before generating your files. Never hardcode lookup values based on documentation alone.

Testing strategies that actually work

Always use prefixes

When testing, prefix your data with a numeric identifier (we use 9001+ for testing, 1001+ for production). This makes it trivial to identify and clean up test data, and it prevents collisions between test runs.

Automate reconciliation from day one

Do not manually check whether your import succeeded. Build automated reconciliation that queries Fusion after the ESS job completes and compares what you sent against what actually loaded. Every row should have a clear status: LOADED, FAILED, or MISSING.

We use BIP (BI Publisher) reports to query the Fusion base tables and match records back to our staging data. This catches silent failures that the ESS job log does not report -- rows that were accepted by the job but rejected during downstream processing.

Test with bad data intentionally

Include deliberately bad rows in your test data. A supplier with a missing name. A PO with an invalid currency. An invoice with a date in 1900. Your pipeline should catch these before they reach Fusion, and your reconciliation should confirm they did not sneak through.

The worst migration bugs are the ones where bad data loads successfully and is not discovered until someone tries to process a transaction six months later.

The value of a real pipeline

Early in the project, we loaded data manually: generate a file, upload it through the UI, wait, check the log, repeat. This works for a handful of records. It does not work when you have 29 data objects, multiple business units, and a go-live window measured in hours.

Building a proper pipeline -- with staging tables, validation rules, automated FBDI generation, ESS orchestration, and BIP reconciliation -- was a significant investment. But it pays for itself the first time you need to do a full regression test. One command. All 29 objects. Results in your inbox.

Data migration is not a one-time event. It is an iterative process of loading, validating, fixing, and reloading. The faster your feedback loop, the better your data quality at go-live.

Final thought

The hardest part of data migration is not the technical plumbing. It is maintaining data quality discipline when the pressure is on and someone says "just load it, we will fix it later." You will not fix it later. Fix it now, in the pipeline, with automated validation that prevents bad data from ever reaching Fusion.

Your future self will thank you.