Batch journal entry import fails with foreign key constraint violations

ERP Infor CloudSuite
, , , , , , , ,
1

Our batch import of 5000 journal entries is failing midway through processing with foreign key constraint violations. The import runs during month-end close using ION Batch API, and when it fails, we lose 2-3 hours of processing time.

The error occurs inconsistently - sometimes after 1000 entries, sometimes after 3000. The violation references the account_code foreign key to the chart of accounts table. We validate account codes in our source system before export, but new accounts are sometimes added to CloudSuite during the day.

<JournalEntry>
  <AccountCode>6500-250</AccountCode>
  <Amount>15000.00</Amount>
</JournalEntry>
<!-- Error: FK constraint violation on account_code

How can we handle these constraint errors gracefully without losing the entire batch?

2

Pre-validation helps, but you should also implement a staging table pattern. Import your journal entries into a staging table first, then run validation queries against the live tables. Only move validated entries to the production journal entry table. This way you can identify and fix bad records without affecting the entire batch. Plus you get an audit trail of what was imported.

3

You need pre-import validation to catch invalid account codes before submitting the batch. Query the chart of accounts table and compare your source data against it. Build a validation report that flags any account codes that don’t exist in CloudSuite. This prevents the batch from starting if there are invalid references.

4

Here’s a complete solution addressing all aspects of reliable batch processing:

Pre-Import Validation: Implement a validation service that runs before batch submission:

-- Pseudocode - Pre-import validation check:
1. Export journal entries from source system to staging file
2. Query CloudSuite: SELECT account_code FROM chart_of_accounts
3. Compare source account codes against CloudSuite list
4. Generate validation report with missing accounts
5. If validation fails: alert accounting team, halt import
6. If validation passes: proceed with batch import
// Prevents invalid batches from starting

Create a validation query:

SELECT DISTINCT s.account_code
FROM staging_journal_entries s
LEFT JOIN chart_of_accounts c ON s.account_code = c.account_code
WHERE c.account_code IS NULL;

Staging Table Pattern: Implement three-tier staging architecture:

  1. journal_entry_staging: Raw import destination

    • All fields from source system
    • import_batch_id, import_timestamp
    • status: ‘PENDING’

  2. journal_entry_validation_errors: Failed validations

    • Copy of staging record
    • error_type: ‘FK_VIOLATION’, ‘INVALID_AMOUNT’, etc.
    • error_message: Detailed constraint violation info
    • status: ‘ERROR’

  3. journal_entry_production: Validated entries

    • Same schema as staging
    • status: ‘IMPORTED’

Validation stored procedure:

CREATE PROCEDURE validate_journal_entries(batch_id)
BEGIN
  INSERT INTO journal_entry_validation_errors
  SELECT s.*, 'FK_VIOLATION', 'Invalid account code'
  FROM staging s
  WHERE NOT EXISTS (
    SELECT 1 FROM chart_of_accounts c
    WHERE c.account_code = s.account_code
  );

  INSERT INTO journal_entry_production
  SELECT s.* FROM staging s
  WHERE NOT EXISTS (
    SELECT 1 FROM validation_errors e
    WHERE e.entry_id = s.entry_id
  );
END;

Constraint Error Handling: Wrap validation in try-catch blocks:

<ErrorHandling>
  <OnConstraintViolation>
    <LogToErrorTable>true</LogToErrorTable>
    <EmailNotification>accounting-team@company.com</EmailNotification>
    <ContinueProcessing>true</ContinueProcessing>
  </OnConstraintViolation>
</ErrorHandling>

Batch Transaction Management: Implement mini-batch processing:

  • Split 5000 entries into 10 batches of 500
  • Process each batch in separate transaction
  • Track completion status in batch_control table
  • On failure: completed batches remain committed
  • Resume processing from failed batch after fixing errors

Batch control tracking:

CREATE TABLE batch_control (
  batch_id INT,
  sub_batch_number INT,
  total_entries INT,
  processed_entries INT,
  error_entries INT,
  status VARCHAR(20),
  completed_timestamp TIMESTAMP
);

This architecture ensures that account code mismatches are caught during pre-validation, any entries that slip through are logged to the error table without failing the entire batch, and partial batch completion is preserved. Your month-end close process becomes resilient - even if 10 out of 5000 entries have invalid account codes, the other 4990 import successfully and you get a clear error report for manual correction.

5

Don’t forget about batch transaction management. If you’re processing 5000 entries in a single transaction, one failure rolls back everything. Break it into smaller transactions of 500-1000 entries each. That way a failure in batch 3 doesn’t invalidate the successful processing of batches 1 and 2. You’ll need to track which batches completed successfully in case you need to restart.