#91 FX config: surface D365 rates from CH, add ISO 4217 currency seed + manual Exchange Rate doctype

[Anonymous]

Originally created by: grynn-in

Summary

Configure FX/currency handling properly. The repo ingests FX rates from D365 but has no ISO 4217 currency reference, no way to surface rates in the app, and no place to enter a manual rate (e.g. a hand-keyed USD→CHF). This issue defines the design and the work, split by system-of-record.

Current state (all FX is D365-sourced)

D365 OData ExchangeRates + ExchangeRateTypes
  → Airbyte → epm_raw
  → stg_d365_fo__exchange_rates.sql   (×100 when ConversionFactor='One')
  → stg_exchange_rates.sql            (canonical UNION, var: erp_sources)
  → bronze_exchange_rate_currency_pairs.sql
  → silver_exchange_rates.sql         (÷100)
  → gold_consolidated_trial_balance.sql  (rate_lookup)

Gaps:

• No ISO 4217 currency reference anywhere (no seed, no dimension) — from_currency/to_currency are unvalidated free strings with no names.
• No manual FX entry — rates only ever come from D365.
• No read surface — the app can already SELECT from CH (api.py:111,615,842) but nothing exposes rates.
• Note: the Historical Equity Rate doctype is not a general FX table — it is an IAS-21 equity-translation override keyed by group+entity+account, with no currency-pair fields. A USD→CHF rate cannot live there.

D365 reference model (what we mirror)

D365 F&O — General ledger ▸ Currencies — uses three tables:

D365 table	Purpose	Entity
Currency	ISO 4217 code, name, symbol	Currency / CurrencyISOCodeEntity
Exchange rate type	Default / Average / Budget	ExchangeRateType (already ingested)
Exchange rate	rate per pair + start date + display factor (One/Hundred)	ExchangeRate keyed by ExchangeRateCurrencyPair

The display factor is exactly what our adapter's ×100 when ConversionFactor='One' already handles. D365 can also auto-import rates from providers (OANDA, ECB).

Design decision — follow the system of record

Direction of data flow is already split on where data originates (D365-origin → app reads from CH; app-origin → doctype writes to CH via sync_doctype). Creating a doctype to re-hold D365 rates would duplicate the source of truth and sync_doctype's TRUNCATE+INSERT would clobber the D365 rates. So we split by layer:

Data	System of record	Decision
Actual / closing / average FX rates	D365 (silver_exchange_rates)	Surface from CH — read-only view/report in Frappe. No doctype.
ISO 4217 currency reference	Static reference	No new doctype. Reuse Frappe core Currency (full ISO 4217). Add currencies.csv dbt seed for the CH-side dimension.
Manual rates not in D365 (hand-keyed, budget/plan, overrides)	The app	New Exchange Rate doctype → epm_staging.manual_exchange_rates, UNION'd into stg_exchange_rates as a new erp_source.

Proposed work

A. ISO 4217 currency seed (open_epm / dbt) — always needed

• [ ] Add dbt_project/seeds/currencies.csv — currency_code, currency_name, symbol, minor_unit (full ISO 4217 list).
• [ ] Register in _seeds.yml with a unique/not_null test on currency_code.
• [ ] Optional bronze/silver currencies dimension; add a dbt relationships test so from_currency/to_currency in silver_exchange_rates validate against the seed.

B. Surface D365 rates in the app (konsol) — always needed

• [ ] Read-only view/report querying silver_exchange_rates (or epm_gold equivalent) via the existing clickhouse.execute read pattern (api.py).
• [ ] Display currency pair, rate, valid_from/valid_to, rate type, with ISO names joined from the currency seed.

C. Manual Exchange Rate doctype (konsol) — only if manual rates are needed (see open question)

• [ ] New Exchange Rate doctype: from_currency (Link→Currency), to_currency (Link→Currency), rate_date, exchange_rate, rate_type, display_factor.
• [ ] DDL: epm_staging.manual_exchange_rates in clickhouse/init-db.sql.
• [ ] Sync to CH following the existing doctype pattern — but with a docstatus=1 filter (see related bug below; do not repeat the leak).
• [ ] Extend stg_exchange_rates.sql erp_sources to UNION a 'manual' adapter (stg_manual__exchange_rates.sql) so manual + D365 rates coexist; define precedence vs D365 on pair/date collisions.
• [ ] Seed sample rates incl. USD→CHF, EUR→CHF.

Open scoping question (decides whether C is in scope)

Do we need to hand-enter rates D365 doesn't supply?

• If D365 is the authoritative FX source and we only need to see rates → A + B only, drop C (no new doctype).
• If accountants must key manual/plan/override rates → include C.

Related

• The Historical Equity Rate doctype has 6 separate correctness/design bugs (sync ignores docstatus; dbt lookup ignores period_date; free-text keys; no validation; narrow permissions; autoname length). Tracked separately — the docstatus fix in particular must be applied to sync_doctype so task C doesn't inherit the same leak.

Repos affected

• open_epm (this repo): A (dbt seed/tests), C dbt adapter.
• konsol (Frappe app at docker/frappe/konsol): B (read view), C (doctype + DDL + sync).

[Anonymous]

Originally posted by: grynn-in

Decision (next round). Part A done (#98/#108). Do Part B next — a read-only ClickHouse view of FX rates surfaced via api.py/konsol.clickhouse (cheap, high value: lets users see the rates that drive translation). Part C (manual Exchange Rate doctype → epm_staging.manual_exchange_rates UNION'd as a 'manual' source) is more work — after B. Keeping open.

[Anonymous]

Originally posted by: grynn-in

📋 Decision brief (options + trade-offs + recommendation): docs/developer-guide/decisions/konsolidat-91-surface-fx.md — merged in [#133].

[Anonymous]

Originally posted by: grynn-in

Status check, 11 Sep 2026 — A and B are done; only C remains, and it is still gated on the open question.

A. ISO 4217 currency reference — done, differently. Not as dbt_project/seeds/currencies.csv: [#146] showed that seeds materialise into epm_gold, which is where konsol also writes, so a seed is the wrong home for anything the app owns. The list is now konsol's ISO Currency doctype (grynn-in/konsol#119), published to epm_gold.currencies as a declared source, with the unique/not_null tests on currency_code moved onto the source. 66 codes including the six Frappe itself does not ship (ANG, AZN, GEL, TJS, TMT, XDR), and the correct ISO exponents — JPY 0, KWD 3.

assert_exchange_rate_currencies_are_iso validates FX codes against it and passes.

B. Surface D365 rates in the app — done. konsol/api.py:1684 reads epm_silver.silver_exchange_rates through the clickhouse.execute pattern, with orchestrator/fx.py and 24 tests behind it.

C. Manual Exchange Rate doctype — not started, and still waiting on the scoping question in this issue: do accountants need to hand-enter rates D365 does not supply? If D365 is authoritative and we only need to see rates, C should be dropped and this issue closed.

Note for whoever picks C up: the docstatus leak this issue warned about inheriting is fixed centrally in resolve_sync_filters, so a new submittable doctype gets the filter automatically.

[Anonymous]

Originally posted by: grynn-in

Parts A and B are done: the ISO Currency doctype is published to epm_gold.currencies and the D365 rates are surfaced. Part C, a manual Exchange Rate doctype, is superseded by grynn-in/konsol#103: one governed group rate table, with ERP rates demoted to a pre-fill. Closing in favour of that issue.

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed