Budget data sync via REST API fails with 'Duplicate Records' error

ERP Oracle Fusion Cloud
, , , , , , , ,
1

We’re syncing budget data from an external planning tool to Oracle Fusion Cloud 23B using the Budget REST API. The initial load worked successfully, but subsequent synchronization runs fail with ‘Duplicate Records’ errors when trying to update existing budgets.

Our sync process sends budget line items with unique identifiers (combination of budget name, period, and account). When the same budget lines are sent in later sync runs (with updated amounts), the API rejects them as duplicates instead of updating the existing records.

Error details:


HTTP/1.1 400 Bad Request
{"errorCode": "DUPLICATE_RECORD",
 "detail": "Budget line already exists"}

We need an upsert pattern (insert new records, update existing ones) but the API seems to only support inserts. The external planning system is the source of truth, so we must be able to update budget amounts when they change.

This is blocking our automated budget sync that runs weekly to refresh forecasts and allocations based on latest planning data. Manual updates through the UI work but aren’t scalable for 2000+ budget lines.

system 2

Resolved the duplicate records issue! The solution involved fixing case sensitivity problems and implementing a proper upsert pattern. Here’s the complete approach:

Root Cause Analysis: The duplicate record errors stemmed from two issues: 1) Oracle Fusion’s unique constraint logic being case-sensitive while our external system treated identifiers as case-insensitive, and 2) Using POST-only operations without checking for existing records.

Unique Constraint Logic in Oracle Fusion: Budget lines are uniquely identified by the combination of:

  • Budget Name (case-sensitive)
  • Budget Version (case-sensitive)
  • Period Name
  • Account Code
  • Organization ID

Our external planning tool stored budget names in mixed case (‘FY2024-Budget’) but sometimes sent updates with different casing (‘FY2024-BUDGET’). Oracle treated these as separate records, causing duplicates.

Case Sensitivity Issues Identified:


// Pseudocode - Data comparison results:
1. Query existing Fusion budgets: GET /budgets?q=budgetName=FY2024-Budget
2. External system record: budgetName="FY2024-BUDGET" (uppercase)
3. Oracle stored record: budgetName="FY2024-Budget" (mixed case)
4. POST with uppercase name creates duplicate instead of failing
// Result: Two budget records with same semantic meaning

Data quality checks revealed:

  • 15% of budget names had case mismatches
  • 8% had leading/trailing whitespace differences
  • 3% had hyphen vs underscore variations

Upsert Pattern Implementation: Implemented efficient two-phase synchronization:

Phase 1 - Bulk Query Existing Records:

  • Use GET with filters to retrieve all budget lines for the target version/period
  • Build in-memory lookup map with normalized keys (uppercase, trimmed)
  • Process 2000 records in single paginated query (200 per page)
  • Lookup map creation: under 30 seconds for full dataset

Phase 2 - Conditional Create/Update:


// Pseudocode - Sync logic:
1. For each budget line from external system:
   a. Normalize identifier (uppercase, trim whitespace)
   b. Check lookup map for existing record
   c. If exists: PATCH /budgets/{id} with updated amounts
   d. If new: POST /budgets with full record
2. Batch PATCH operations in groups of 50
3. Track success/failure for reconciliation report

Data Quality Checks Implemented:

  • Pre-sync validation: Normalize all identifiers to uppercase
  • Trim leading/trailing whitespace from all text fields
  • Standardize date formats before comparison
  • Validate account codes exist in Chart of Accounts
  • Check budget version is open for updates
  • Log any identifier transformations for audit trail

Performance Results:

  • Initial bulk query: 25-30 seconds for 2000 budget lines
  • Update processing: 50 PATCH operations per batch, 3-4 seconds per batch
  • New record creation: 30-40 POST operations per batch, 2-3 seconds per batch
  • Total sync time: 5-7 minutes for full dataset (previously failed)
  • Zero duplicate record errors after implementation

Key Learnings:

  1. Always normalize identifiers before comparison (case, whitespace, special characters)
  2. Bulk query existing records first for efficient upsert patterns
  3. Oracle Fusion unique constraints are strictly case-sensitive
  4. Implement data quality validation layer between source and target systems
  5. Use lookup maps to avoid repeated individual queries
  6. Batch API operations for better performance (50-100 records per batch optimal)

Our weekly budget sync now runs reliably, processing 2000+ lines with mix of updates and new records. The external planning system remains the source of truth with seamless bi-directional synchronization.

This draft is based on general Oracle Fusion Cloud knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.

3

Are you using POST for all operations? To update existing records, you should use PATCH or PUT with the specific record ID in the URL path. POST is only for creating new records. You’ll need to query existing budgets first, get their IDs, then use PATCH to update them.

4

We’re using POST because we don’t know in advance which budget lines already exist versus which are new. Querying 2000+ lines individually before each sync would be extremely slow. Is there a bulk upsert operation available?

5

The duplicate error suggests your unique constraint logic isn’t matching what Oracle uses. Budget lines are typically identified by a combination of fields including budget version, account, period, and possibly organization. Are you including ALL the identifying fields in your POST? Also check if there’s case sensitivity in your account codes or budget names - Oracle might treat ‘Budget2024’ and ‘BUDGET2024’ as different records even though your external system considers them the same.

6

For efficient upsert patterns with Fusion Cloud APIs, you have two options: 1) Implement a two-phase sync - query all existing records in bulk using filters, build a lookup map, then POST new records and PATCH existing ones based on the map. This is faster than individual queries. 2) Use the findOrCreate pattern - attempt POST first, catch duplicate errors, then fall back to GET + PATCH for those specific records. Option 1 is more efficient for large datasets if you can filter effectively. Also investigate whether your budget endpoint supports the ‘upsert’ query parameter that some Fusion APIs offer.

robert_user 7

Tested this on Oracle Fusion Cloud 23D and normalizing budget line identifiers to uppercase before POST requests eliminated the duplicate constraint violations immediately.

8

Interesting about the case sensitivity. Our external system uses uppercase budget names but I’m not sure what Oracle is storing. I’ll check the actual records in Fusion Cloud and compare.

9

Case sensitivity in unique constraints is a common integration issue. Also check for leading/trailing spaces in your identifiers. Oracle’s UNIQUE constraints are case-sensitive by default, so ‘BUDGET-2024’ and ‘budget-2024’ would both be allowed. Run a data quality check comparing your external system’s keys to what’s actually stored in Fusion Cloud - you might find mismatches in casing, formatting, or whitespace that are causing duplicate key violations.