The problem this solves

A global retailer's subsidiaries keep their books in local currency — a Tokyo store's ledger is in yen, a São Paulo store's is in reais. The consolidated group reports in one currency, usually the parent's. Getting from 17 sets of local-currency books to one USD-denominated consolidated statement is currency translation, and it is one of the two or three things a consolidation system exists specifically to do — a spreadsheet cannot do it correctly at scale because the mechanics require three different rates applied to three different classes of account, and the residual has to plug to a specific equity line, not vanish.

Get translation wrong and the damage is not cosmetic. A misapplied rate on a $30M balance sheet line moves consolidated equity by real money, and auditors test this specifically at year-end.

Audience

Corporate consolidation accountants and controllers who own the group's translation policy. FP&A and treasury teams who need to understand why FX movement shows up in equity (CTA) rather than in the income statement. EPM architects scoping an FCCS or consolidation implementation.

A store and a period

Pick any of the 44 entities in the cube — 27 of them report in a currency other than USD. The demo builds a balanced local-currency balance sheet for that entity (from its existing revenue seed in the FP&A module) and translates it using that currency's average, closing, and historical rates for the selected year and scenario.

A rate-tagged translation table and a CTA waterfall

• Translation table: every BS line shown local-currency vs USD, with a colour-coded pill naming which rate translated it — Closing, Average, or Historical
• CTA line: shown as a plug, not an input — computed as whatever balances the translated statement
• CTA chart: every non-USD entity ranked by CTA, so a strengthening-EUR store and a devaluing-ARS store sit at opposite ends of the same chart

Between local books and the consolidated statement

Translation runs after each entity's local-currency trial balance is finalized and before intercompany eliminations and ownership adjustments are applied — this demo's sibling use cases, IC Eliminations and Ownership & NCI, both assume translated (USD) numbers as their starting point in a real consolidation.

Recommended integration points

• Month-end close: Treasury publishes the period's average and closing rates; consolidation re-translates every non-USD entity in one batch run
• FX exposure review: CFO asks "how much of this quarter's equity movement is FX, not operating performance?" — the CTA chart answers it directly
• Board reporting: explaining why EBITDA looked flat in local currency but consolidated USD revenue dropped — a rate story, not an operating story

The numbers

The honest checklist

• ✓You consolidate entities that keep their books in a currency other than the group reporting currency
• ✓Auditors or the board ask how much of equity movement is FX rather than operating performance
• ✓You need average, closing, and historical rates applied to the correct account class automatically, not by spreadsheet formula
• ✗Every entity already reports in the group currency — there is nothing to translate
• ✗You need remeasurement (functional currency ≠ local currency, gain/loss through the income statement) rather than translation (functional = local, adjustment through equity) — that is a different mechanic with a different rate table

Try it now

The live demo translates any of the 44 entities in real time, driven by NLQ or the filter dropdowns.

Things to try

• Type "Show CTA for Buenos Aires" into the NLQ bar and watch CTA turn sharply negative from the currency's devaluation between historical and closing rates
• Select NYC Flagship (USD) — confirm CTA is exactly $0, since a USD entity has nothing to translate
• Compare Berlin Mitte (EUR, appreciating) against Tokyo Shibuya (JPY, depreciating) on the CTA chart

Why three rates, not one

A single blended rate would be simpler to build and wrong. Assets and liabilities are settled at today's exchange rate, so they translate at the closing rate. Equity was contributed at a point in the past and stays fixed in translated terms unless the entity issues more stock, so it translates at the historical rate from that contribution date. Income is earned continuously through the period, so a period-average rate is the standard approximation for the income statement's translated value. Mixing these up — translating equity at the closing rate, say — produces a balance sheet that doesn't tie and an equity movement that has no accounting explanation.

Local BS → rate-tagged translation → CTA plug

The local-currency BS is deliberately constructed to balance exactly before translation (genConsolidationBS derives liabilities as a plug against assets and equity, not as an independent fraction). That discipline matters: if the local statement didn't tie, the CTA plug after translation would conflate a real FX effect with a synthetic-data bug, and the demo would teach the wrong lesson.

App UI — Component breakdown

17 currencies, three rates each

FX_RATES defines average, closing, and historical rates for every currency the retail cube touches, expressed as USD per one unit of local currency (so usd_amount = local_amount * rate). Two design choices worth calling out:

• Pegged currencies (AED, SAR) carry identical average/closing/historical rates — a UAE or Saudi entity has effectively zero CTA from FX, which is correct and a useful contrast against the free-floating currencies on the same chart.
• The historical rate is a simplification. A real FCCS application tracks the historical rate at the actual date each tranche of equity was contributed — potentially many dates across a subsidiary's life. This demo uses one fixed historical rate per currency, which is the right level of fidelity for illustrating the mechanism without requiring a multi-year contribution history the rest of the cube doesn't model.

CTA is derived, never entered

// translateToUSD() — the core of the mechanism
totalAssets_usd      = (cash + ar + inventory + ppe) × closingRate
totalLiabilities_usd = (ap + debt) × closingRate

equityBeforeCTA_usd  = commonStock × historicalRate
                      + openingRE   × historicalRate
                      + netIncome   × averageRate

CTA = totalAssets_usd - totalLiabilities_usd - equityBeforeCTA_usd
totalEquity_usd = equityBeforeCTA_usd + CTA   // now the statement balances

This is the entire mechanism. Because assets/liabilities move with the closing rate and equity mostly doesn't (except for the current period's income, which moves with the average rate), any exchange-rate movement between the historical rate and the closing rate shows up as a gap — and that gap is CTA. It sits in equity, not in net income, which is precisely why FX translation gains and losses don't distort reported operating performance even when they're large.

Why Argentina looks different on the chart

The demo's Buenos Aires entity uses an ARS historical rate of 0.00160 against a closing rate of 0.00095 — a roughly 40% devaluation baked into the illustrative rate table, deliberately steep so the CTA chart shows a real-world pattern: entities in historically inflationary economies (Argentina, and periodically several Latin American and a handful of other emerging markets) can produce CTA swings an order of magnitude larger than a Eurozone or UK entity in the same period.

In a real IFRS or US GAAP consolidation, once a country is classified as hyperinflationary (broadly, cumulative three-year inflation approaching or exceeding 100%), the accounting standard changes further — IAS 29 requires restating the local financial statements for inflation before translation, using the reporting-date rate for everything rather than the average/closing/historical split modeled here. That is a materially different, additional step this demo does not implement; flagging that boundary explicitly is more useful than pretending the simple model covers it.

Cost per query

The LLM's job stops at resolving which entity, year, and scenario the query means — the same three-field schema as the FP&A use case. Every dollar amount in the translation table and every bar in the CTA chart is client-side arithmetic against FX_RATES; the LLM never sees or computes a currency value. That split keeps the query cheap and keeps the financial math auditable independent of the LLM.

Cost model — At-scale projections

The LLM cost scales with query volume the same way every other use case's does — roughly $0.0002/query, so 1,000 translation queries a month runs about $0.20. The translation math itself runs entirely client-side against pre-seeded rates once the query resolves, so it does not add to that cost regardless of entity count or how many times a given entity is re-translated. The real-world cost driver that matters is operational, not compute: the treasury process that supplies and maintains the average/closing/historical rate table each period.

Key files

Tech stack — Every tool in this build

Known attack surfaces

Guardrails — What prevents bad translations

• CTA is always derived, never an input: there is no code path that lets a rate error silently disappear — it always surfaces as a CTA movement large enough to be noticed
• USD entities are a built-in sanity check: since USD/USD rates are all 1.0, any USD entity showing non-zero CTA would immediately indicate a bug in the translation function itself
• Balance construction: the local BS is guaranteed to balance before translation, isolating CTA to genuine currency effects

Who is asking, and what are they allowed to see?

The demo answers neither question — it has a cookie gate and no notion of a user. In production these are the two questions everything else rests on, and they have different answers: authentication is who you are, authorization is what you may see. Corporate SSO settles the first. Only Oracle EPM Cloud can settle the second, and the single most important rule in this section is that this application must never become the place where that decision is made.

9.1 · The identity chain, end to end

9.2 · Federating the corporate identity provider

Oracle EPM Cloud does not replace your directory — it trusts it. The EPM Cloud identity domain is federated with the corporate IdP so authentication happens where it already happens, under policies security has already written.

For the browser application itself, use OIDC Authorization Code flow with PKCE. Not the implicit flow, which is deprecated and leaks tokens through the URL, and never a resource-owner password grant — a finance tool should not be capable of handling a password at all.

9.3 · From group membership to EPM roles

Roles are granted to groups, never to individuals, and the groups come from the directory. That one discipline is what makes joiner/mover/leaver work without anyone having to remember this application exists.

Entra ID group                    →  EPM role / entitlement
──────────────────────────────────────────────────────────────────
FIN-EPM-Analysts                  →  Planning User
FIN-EPM-Controllers-EMEA          →  Power User + EMEA data scope
FIN-EPM-Admins                    →  Service Administrator
──────────────────────────────────────────────────────────────────
Provisioned by SCIM. Remove the user from the group and the
entitlement disappears on the next sync — including here.

For this use case the relevant native entitlement is: FCCS User with access to the consolidation application and the relevant scenario.

9.4 · The architectural decision: who enforces?

This is the choice that determines whether the deployment is defensible. Both patterns appear in the diagram above; the difference is where the security boundary actually sits.

The shortcut to refuse. Pattern B built with a Service Administrator account and no filtering at all is the most common way this gets delivered, because it works perfectly in UAT — testers are usually over-entitled, so nobody notices that everyone can see everything. It fails at the first access review, and by then it is in production with real users depending on it.

9.5 · Data-level security is the part that matters

Role membership decides whether a user can open the application. It does not decide which rows they get back, and confusing the two is the most expensive mistake available here.

• For this use case: FCCS data access by Entity and Scenario; the Entity hierarchy carries the same parent/child security as the consolidation itself.
• Apply it before aggregation, not after. Filtering a total that has already been computed across entities the user cannot see still leaks the total.
• The NLQ layer needs its own check. Layer 4 already validates that the resolved point of view uses approved members; production adds a second test — that the resolved POV sits inside this user’s scope — and it runs before the data call, not after. A natural-language interface is very good at asking for things politely; the authorization check must not care how the question was phrased.
• Fail closed. If entitlements cannot be resolved, return nothing and say so. An empty result is a support ticket; a permissive default is an incident.

9.6 · Provisioning, sessions and the leaver problem

• SCIM provisioning from Entra or Okta into the EPM Cloud identity domain (OCI IAM, formerly IDCS), covering joiner, mover and leaver. The mover is the case people forget — somebody changing region should lose the old scope, not accumulate both.
• No local user store. If this application keeps its own copy of who may do what, a leaver keeps access until somebody remembers to update it. Nobody ever does.
• Short-lived access tokens with refresh-token rotation; align session timeout with the data classification rather than with convenience.
• MFA and Conditional Access at the IdP — not reimplemented here. Device compliance and location policy come free with federation.
• Quarterly recertification of both the groups that grant access and the service account’s own entitlements, evidenced and signed.
• Break-glass access is a named, monitored, time-boxed account — never a shared credential in a password manager.

9.7 · What this means for Currency Translation

9.8 · Security configuration checklist

• ✓Oracle EPM Cloud federated with the corporate IdP over SAML 2.0 or OIDC; the cookie gate removed entirely
• ✓Browser app uses OIDC Authorization Code + PKCE — no implicit flow, no password grant
• ✓MFA and Conditional Access enforced at the IdP, not reimplemented in the application
• ✓Roles granted to directory groups, never to individuals; SCIM covers joiner, mover and leaver
• ✓Enforcement pattern chosen deliberately — Pattern A where the API supports it, or Pattern B with filtering centralised and tested
• ✓Data-level security applied before aggregation, and the resolved POV checked against the user’s scope before the data call
• ✓No local user table and no local role table anywhere in the application
• ✓Every query logged against the real end user, even when a service account makes the call
• ✓Authorization failures fail closed and are logged as security events rather than swallowed
• ✓Quarterly recertification of access groups and of the service account’s own entitlements

From demo to a governed enterprise deployment

Everything above runs on synthetic data, a public LLM API key, a cookie gate, and no audit trail — deliberately, so the mechanics are inspectable. Taking Currency Translation to production is not a rewrite; the 4-layer pipeline and the data-layer contract survive intact. It is a controlled-change program across six workstreams: architecture, LLM platform, security, SOX/audit, environment promotion, and operations. This section is the checklist we run with clients.

10.1 · Production reference architecture

10.2 · Choosing the LLM platform

The demo’s DeepSeek call is a placeholder for a single adapter, callLLM(system, user), behind Layer 3. Swapping the provider changes one function and zero business logic. Pick the platform the organisation already governs — the security and procurement review is the long pole, not the integration.

Put a model gateway in front of whichever you choose (Azure API Management, OCI API Gateway, Kong AI Gateway, LiteLLM, or Portkey): it owns key custody, per-team spend caps, routing and fallback between models, prompt/response logging, and lets you retire a deprecated model without touching the application.

10.3 · Security controls

10.4 · SOX, audit, and model-risk controls

A read-only NLQ layer does not change a financial-reporting control, but it is an interface to a SOX-relevant system and lands squarely in ITGC scope. Treat prompts, schemas, and eval sets as code — that single decision satisfies most of what an auditor will ask for.

10.5 · Dev → Test → Prod promotion

• Promoted artifacts: application build, prompt templates (versioned), approved-member schema snapshot, eval set, infrastructure config (IaC) — all from the same Git tag.
• Pipeline: branch → PR review → CI (tests + evals) → deploy to Test → UAT sign-off → CAB → deploy to Prod → smoke test. Hosting can stay on Cloudflare Pages/Workers or move to OCI Functions + API Gateway or the org’s standard platform — the code does not care.
• Configuration: per-environment secrets and endpoints injected at deploy; the same build runs in every environment.
• Metadata sync: a scheduled job refreshes dimension metadata into Layer 2 grounding with change detection, so a new entity or account appears in the approved lists without a code release.
• Rollback: previous build and previous prompt version retained; rollback is a redeploy, and because prompts are versioned it also reverts a prompt regression.

10.6 · Operating it

• SLOs: p95 latency, availability of the read path (the deterministic fallback keeps it alive when the LLM is down — already built), fallback rate as a quality signal, eval pass rate per release.
• Cost governance: per-user and per-team token budgets at the gateway; alert on anomalies; the unit-cost model earlier in this kit is the baseline.
• Model lifecycle: providers retire models on a schedule — re-run the eval suite on the successor before switching, and record the switch as a change.
• Incident runbook: LLM outage → fallback parser; EPM API outage → cached metadata with a stale banner; guardrail spike → review logs for injection attempts.

10.7 · What changes for Currency Translation

10.8 · Production readiness checklist

• ✓LLM platform selected from the governed list, DPA / zero-retention terms on file, gateway in front of it
• ✓SSO integrated; authorisation derived from EPM security groups; cookie gate removed
• ✓Read-only EPM service account per pod, credential in a vault, rotation scheduled
• ✓Prompts, schema, and eval set version-controlled and under change management
• ✓Append-only audit log wired to the SIEM with the agreed retention
• ✓Golden-query eval suite passing in CI; results archived as release evidence
• ✓Dev / Test / Prod pipeline with gates, IaC, and a rehearsed rollback
• ✓Model-risk register entry and owner named; first re-validation date set
• ✓Metadata refresh job scheduled with change detection
• ✓SLOs, cost caps, and the incident runbook agreed with platform operations