Automated bank statement import using treasury API reduced reconciliation time by 60% for global finance team

ERP Oracle JD Edwards
, , , , , , , ,
1

We’re experiencing a puzzling issue with automated bank statement imports in JD Edwards Cash Management 9.2.0. The import process works perfectly in our development environment but consistently fails in QA.

The setup is identical - same CSV template format, same bank account configurations, same import program settings. We haven’t made any code changes to the import logic recently. In dev, the CSV files process smoothly and create bank transactions correctly. In QA, the import program runs but generates error logs indicating “format mismatch” even though we’re using the exact same file.


ERROR: Bank Statement Import Failed
File: BANK_STMT_20250315.csv
Error Code: BSI-4012
Message: Format validation failed at row 15

The error appears inconsistent - sometimes row 15, sometimes row 23. We’ve verified file encoding (UTF-8), line endings, and CSV delimiters are identical. Has anyone encountered environment-specific import failures with matching configurations? What could cause the same file to behave differently across environments?

2

I’ve seen similar behavior with bank imports. First thing to check - are your UDC values (User Defined Codes) synchronized between dev and QA? The import validation often references UDC tables for bank codes, transaction types, and currency codes. If QA has different or missing UDC entries, it could cause format validation errors even with identical files. Check system code 00/BT (Bank Transaction Types) and 00/BC (Bank Codes) in both environments.

3

Check your processing options for the bank statement load program (R09110Z). There’s a specific PO that controls date format validation - it might be set differently in QA. Also verify the XJDE.INI file settings on your QA enterprise server. The [JDE-CG IMPORT] section has parameters like DateFormat and DecimalSeparator that can override application-level settings. If someone modified the INI file during QA setup, it could explain why the same CSV behaves differently. Compare the INI files between your dev and QA servers, particularly the import-related sections.

4

The XJDE.INI DateFormat setting is global and affects multiple import processes, but most JDE applications use the date format from user preferences or processing options, not the INI file. That said, you should test carefully. I’d recommend creating a backup of your current INI file, then temporarily modify just the [JDE-CG IMPORT] section to add a specific override for bank imports rather than changing the global DateFormat. You can also consider standardizing your CSV generation to match QA’s expected format instead of changing the server configuration.

5

Based on the troubleshooting so far, you’re dealing with an environment configuration mismatch rather than a code or template issue. Here’s a systematic resolution approach addressing all three focus areas:

Environment Configuration Analysis: Since the file imports work in dev but not QA with the same CSV template, the root cause lies in environment-specific settings. You’ve correctly identified the XJDE.INI DateFormat discrepancy (DD/MM/YYYY in QA vs MM/DD/YYYY in dev). This explains why validation fails at inconsistent row numbers - it depends on which dates in your CSV happen to be ambiguous (e.g., 03/12/2025 could be March 12 or December 3).

Recommended Solution:

  1. INI File Approach - Modify QA’s XJDE.INI [JDE-CG IMPORT] section to add: BankImportDateFormat=MM/DD/YYYY. This creates an import-specific override without affecting other processes.

  2. Verify No Code Changes - Since you confirmed no recent changes to import logic, document this configuration difference in your environment setup guide to prevent future issues during refreshes.

  3. Validation Testing - After INI modification:

    • Test with your problematic CSV file
    • Verify F0911Z1 work table receives correct date values
    • Run the bank reconciliation process to confirm transactions post correctly
    • Test at least 3 different CSV files with various date patterns

  4. Long-term Fix - Consider implementing environment parity checks in your deployment process. Create a configuration comparison script that flags differences in:

    • XJDE.INI critical sections
    • UDC values used by imports
    • Processing option defaults
    • F0911Z1 table structure

Alternative Approach: If changing server configuration isn’t feasible, modify your CSV generation process to use ISO 8601 format (YYYY-MM-DD) which JDE handles consistently regardless of INI settings.

Post-Implementation: Document this resolution in your runbook and add a validation step to your QA refresh procedures to verify import configuration settings match production requirements, not just dev settings.

6

Good point about UDC codes. I compared the 00/BT and 00/BC tables between environments - they match perfectly. Same values, same descriptions, same hard-coded flags. The CSV file references transaction type ‘DEP’ for deposits and ‘WTH’ for withdrawals, both exist in QA’s UDC tables. Still getting the format mismatch error on random rows. Could this be related to data type validation in the F0911Z1 work table?