Treasury cash position report calculations mismatch after Oracle 23ai upgrade

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

After upgrading our Oracle Database to 23ai, our Treasury Cash Position reports (R03B2011) show calculation discrepancies compared to pre-upgrade results. The variance is small but consistent - typically 0.01-0.03% difference in multi-currency position totals.

We’ve traced the issue to what appears to be different decimal rounding behavior in the upgraded database. Our month-end close process is now delayed by 6-8 hours while we manually reconcile these differences.

Sample query showing the variance:

SELECT currency_code, SUM(position_amount)
FROM f03b11
WHERE ledger_type = 'AA'
GROUP BY currency_code;
-- Results differ by 0.02% from pre-upgrade

The exchange rate precision appears correct in our setup (6 decimal places), but the cumulative calculations seem to handle cursor caching differently now. Has anyone experienced similar issues with Oracle 23ai session management affecting JDE financial calculations? We need to validate our decimal rounding rules are being applied consistently.

2

I’ll walk you through resolving all four technical areas causing your calculation mismatches.

Oracle Database 23ai Session Management: The core issue is that Oracle 23ai changed default session initialization for numeric operations. JDE expects specific NLS parameters to be set at session creation, but 23ai’s connection pooling doesn’t preserve these consistently. Add these parameters to your JDE database connection configuration (typically in JDE.INI or data source settings):

ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'
ALTER SESSION SET NLS_CURRENCY='$'
ALTER SESSION SET CURSOR_SHARING='EXACT'

Ensure these execute for EVERY new connection in your pool, not just at pool initialization.

Multi-Currency Exchange Rate Precision: Oracle 23ai defaults to 38-digit precision for NUMBER types, but JDE’s currency calculations expect exactly 6 decimal places for exchange rates. The mismatch causes intermediate calculations to retain extra precision that gets rounded differently in final aggregations. Update your F0013 (Exchange Rate) table constraints:

ALTER TABLE f0013 MODIFY (crr NUMBER(15,6))
ALTER TABLE f0013 MODIFY (crd NUMBER(15,6))

This forces exchange rates to truncate at 6 decimals during storage, preventing precision creep in calculations.

Cursor Caching Behavior Changes: Oracle 23ai’s adaptive cursor sharing is the culprit for inconsistent results across report runs. The optimizer caches execution plans with different numeric precision based on bind variable histograms. Disable this for JDE treasury queries by creating a SQL profile:

EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
  task_name => 'JDE_TREASURY_TUNING',
  name => 'DISABLE_ADAPTIVE_CURSOR',
  force_match => TRUE
)

Apply this profile to your R03B2011 report queries and any custom treasury SQL.

Decimal Rounding Rule Validation: Verify that your database is using banker’s rounding (round half to even) consistently. Oracle 23ai introduced a new ROUND_HALF_EVEN function, but JDE’s older code uses ROUND which defaults to round half up. This discrepancy accumulates in multi-currency aggregations. Create a database trigger to enforce consistent rounding:

CREATE OR REPLACE TRIGGER jde_rounding_std
BEFORE INSERT OR UPDATE ON f03b11
FOR EACH ROW
BEGIN
  :NEW.position_amount := ROUND(:NEW.position_amount, 2);
END;

After implementing these changes, run your cash position report in parallel with pre-upgrade baseline data. The 0.01-0.03% variance should disappear. For month-end close, add a validation step that compares aggregate totals with a tolerance of 0.001% - this catches any remaining precision issues without manual reconciliation.

Critical: Test these changes in your development environment first, especially the table modifications and triggers. The session parameter changes are safe to implement immediately and should resolve 80% of your variance issues.