Foreign Exchange Rates & Fiscal Period FX

Overview

The FX rates data model supports multi-currency financial operations across Client Processing. It manages:

1. fx_rate_source — Authoritative sources for FX rates (HMRC, ECB, FED, etc.)
2. fx_rate — Daily spot rates and monthly averages with date-range validity
3. fiscal_period_fx_rate (pending CP-DM-004) — Period-end cutoff rates for revenue recognition and settlement closing

Architecture

Two-Layer Rate Strategy

Daily/Monthly Rates (fx_rate table)

• Used for transactional lookups and reporting
• Multiple sources (HMRC, ECB, FED)
• Supports both spot and averaged rates
• Date-range effective dating

Period-End Rates (fiscal_period_fx_rate table, pending)

• Locked rates for a specific fiscal period
• Used for revenue recognition and settlement final-close calculations
• Ensures consistent rates within a period (no intra-period rate changes)
• Replaces intraday rate volatility with period-level stability

---

2. Data Model

2.1 Entity-Relationship Diagram

erDiagram
    fx_rate_source ||--o{ fx_rate : "provides"
    fiscal_period ||--o{ fiscal_period_fx_rate : "locked rates for"
    fx_rate_source }o--|| fx_rate : "source of"

---

2.2 fx_rate_source (Reference Table)

Catalogs the authoritative sources for FX rate data, allowing tracking of rate provenance and characteristics.

Field	Type	Required	Default	Description
fx_rate_source_id	serial	Yes	Auto	Primary key. Referenced by fx_rate.fx_rate_source_id.
fx_rate_source_cd	varchar(20)	Yes	—	Code for the source (e.g., HMRC, ECB, FED, MANUAL). Unique identifier.
fx_rate_source_name	varchar(100)	Yes	—	Display name (e.g., "Her Majesty's Revenue & Customs", "European Central Bank").
fx_rate_source_desc	varchar(500)	No	—	Description of the source, methodology, or notes.
fx_rate_source_url	varchar(500)	No	—	URL for source documentation or rate feed.
default_base_currency_cd	varchar(3)	No	—	Default base currency for rates from this source (e.g., GBP for HMRC, EUR for ECB). ISO 4217.
active_ind	boolean	No	true	true = source is actively used; false = deprecated.

Primary Key: fx_rate_source_id

Unique Constraints: fx_rate_source_cd (one code per source)

Common Sources:

• HMRC — UK Her Majesty's Revenue & Customs (GBP rates)
• ECB — European Central Bank (EUR rates)
• FED — US Federal Reserve
• MANUAL — Manually entered rates (fallback when automated feeds unavailable)

---

2.3 fx_rate (Daily & Monthly Rates)

Stores foreign exchange rates from multiple sources with support for both monthly averages and daily spot rates. Rates are date-range effective, allowing historical lookups and rate history.

Field	Type	Required	Default	Description
fx_rate_id	serial	Yes	Auto	Primary key.
fx_rate_source_id	integer	Yes	—	FK to fx_rate_source.fx_rate_source_id. Which source provided this rate.
base_currency_cd	varchar(3)	Yes	—	ISO 4217 currency code (e.g., GBP, USD, EUR). Base unit.
quote_currency_cd	varchar(3)	Yes	—	ISO 4217 currency code. Quote unit (the currency amount per 1 base unit).
rate	decimal(18,8)	Yes	—	Exchange rate value. Direction: rate = amount of quote_currency_cd per 1 unit of base_currency_cd. Example: base_currency_cd=GBP, quote_currency_cd=USD, rate=1.33 means 1 GBP = 1.33 USD. Precision: 18 digits total, 8 decimal places. Supports both tiny rates (0.4091) and large rates (119710.1830).
inverse_rate	decimal(18,8)	No	—	Pre-calculated inverse: 1 / rate. Stored for performance optimization when quote→base lookups are frequent. Optional; compute on-the-fly if not populated.
fx_rate_type_cd	varchar(20)	Yes	—	Rate classification: MONTHLY_AVG (monthly average), DAILY_SPOT (daily snapshot), or MONTHLY_END (month-end official). See Section 5.
effective_start_dt	date	Yes	—	Rate valid from this date (inclusive).
effective_end_dt	date	Yes	—	Rate valid until this date (inclusive). For daily rates, start_dt = end_dt. For monthly rates, spans the entire month.
source_country_name	varchar(100)	No	—	Traceability metadata: country or territory name from the source (e.g., from HMRC "Country/Territories" column).
source_currency_name	varchar(100)	No	—	Traceability metadata: currency name from the source (e.g., from HMRC "Currency" column).
created_by, created_dt	varchar(100), timestamp	—	—	Audit trail: who loaded this rate and when.
updated_by, updated_dt	varchar(100), timestamp	—	—	Audit trail: last updater and timestamp.

Primary Key: fx_rate_id

Indexes:

• idx_fx_rate_lookup on (base_currency_cd, quote_currency_cd, effective_start_dt, effective_end_dt) — Primary lookups for a currency pair on a date
• idx_fx_rate_source on (fx_rate_source_id) — All rates from a specific source
• idx_fx_rate_dates on (effective_start_dt, effective_end_dt) — Date range queries for rate history

Rate Direction Convention

rate = amount of quote_currency_cd per 1 unit of base_currency_cd

Example:
  base_currency_cd: GBP
  quote_currency_cd: USD
  rate: 1.33
  
  Interpretation: 1 GBP = 1.33 USD
  
  To convert £100 to USD: 100 GBP × 1.33 = 133 USD
  To convert $100 to GBP: 100 USD ÷ 1.33 = 75.19 GBP (using inverse_rate: 100 × 0.7519 = 75.19)

---

2.4 fiscal_period_fx_rate (Period-End Rates) — PENDING CP-DM-004

Status: TABLE DESIGN PENDING; NOT YET CREATED IN DATABASE

This table will store locked FX rates per fiscal period for use in revenue recognition and settlement final closing. Unlike transactional fx_rate which is date-effective, fiscal_period_fx_rate locks a rate for the entire period, eliminating intra-period rate volatility.

Pending Decisions (CP-DM-004):

1. Should one rate per currency pair per period be enforced (unique constraint)?
2. Which rate type takes precedence when multiple rates exist (MONTHLY_END > MONTHLY_AVG > DAILY_SPOT)?
3. Should this table link to fx_rate or store denormalized rate values?

Proposed Schema:

Field	Type	Required	Default	Description
fiscal_period_fx_rate_id	serial	Yes	Auto	Primary key.
fiscal_period_id	integer	Yes	—	FK to fiscal_period.fiscal_period_id. The period this rate applies to.
base_currency_cd	varchar(3)	Yes	—	ISO 4217 base currency code (e.g., GBP).
quote_currency_cd	varchar(3)	Yes	—	ISO 4217 quote currency code (e.g., USD).
rate	decimal(18,8)	Yes	—	Locked FX rate for the period. Same direction convention as fx_rate: amount of quote currency per 1 base unit.
rate_source_cd	varchar(20)	No	—	Which source was used for this period-end rate (HMRC, ECB, etc.). Traceability.
rate_effective_dt	date	No	—	The specific date this rate was taken from (e.g., last day of period, or from MONTHLY_END fx_rate).
created_by, created_dt	varchar(100), timestamp	—	—	Audit trail.
updated_by, updated_dt	varchar(100), timestamp	—	—	Audit trail.

Proposed Indexes:

• PK: (fiscal_period_id, base_currency_cd, quote_currency_cd) — Guarantees one rate per currency pair per period
• idx_fiscal_period_fx_lookup on (base_currency_cd, quote_currency_cd, fiscal_period_id) — Find period-end rates for a pair

Use Cases:

• Revenue Recognition: Recognize multi-currency revenue at the period-end rate, ensuring consistent FX impact within the period
• Settlement Closing: Apply period-end rates when settling foreign currency payables, locking in the FX gain/loss for the period
• GL Posting: Generate FX revaluation entries using period-end rates for all period-end balance-sheet accounts

---

3. Lookups & Rate Selection

3.1 Transactional Rate Lookup (fx_rate)

When processing a transaction on date T for a currency pair (base, quote):

1. Query fx_rate where:
   • base_currency_cd = base
   • quote_currency_cd = quote
   • effective_start_dt ≤ T ≤ effective_end_dt
2. If multiple rates match (unlikely with unique constraints), prefer:
   • fx_rate_type_cd = DAILY_SPOT (most specific)
   • Fallback: MONTHLY_AVG
3. If no rate found, raise error or use fallback rate (e.g., manual override, previous day's rate)

Index: idx_fx_rate_lookup on (base_currency_cd, quote_currency_cd, effective_start_dt, effective_end_dt) optimizes this query.

3.2 Period-End Rate Lookup (fiscal_period_fx_rate, pending)

When closing a fiscal period or recognizing revenue for the period:

1. Query fiscal_period_fx_rate where:
   • fiscal_period_id = period being closed
   • base_currency_cd = base
   • quote_currency_cd = quote
2. Use the locked rate for all period-end calculations
3. If no rate exists, halt closing and require manual rate entry

Benefit: All revenue, GL entries, and settlements using this period use the same FX rate, preventing rate discrepancies within the period.

---

4. Audit & Change Tracking

All FX tables include audit columns:

• created_by, created_dt — Who loaded the rate and when
• updated_by, updated_dt — Last updater and timestamp

Rate Changes:

• New rate added: New row inserted (no update to existing rates)
• Rate correction: Update updated_by and updated_dt; log change reason in comments or audit log

Immutable rates: Once a period-end rate is locked in fiscal_period_fx_rate, it should not be changed without explicit approval from Finance (prevents revenue restatements).

---

5. Code Master Values

5.1 FX_RATE_TYPE_CD

Used by fx_rate.fx_rate_type_cd to classify rate source and frequency.

Code	Description	When Used
DAILY_SPOT	Daily spot rate on a specific date	Transactional processing; high granularity
MONTHLY_AVG	Average rate over the month	Batch processing; smoother volatility handling
MONTHLY_END	Official month-end rate	Period-end closing; revenue recognition baselines

---

6. References & Cross-Links

Document	Relationship
Parties Data Model	party.currency_cd determines which FX rates are applicable. Party bank accounts may be in non-functional currencies.
Accounting Data Model	fiscal_period.fiscal_period_id is the FK in fiscal_period_fx_rate. GL entries reference period-end rates for FX revaluations.
Sales Items & Payment Terms	sales_item.currency_cd and payment_term.gross_amt are in potentially multiple currencies. FX rates convert to functional currency for revenue recognition.
Tax & Withholding	Foreign currency withholding amounts are converted to functional currency using FX rates.
Related Jira Tasks	CP-DM-004 — Create fiscal_period_fx_rate table and finalize design decisions

---

7. Migration & Rollout

Loading FX Rates

Initial Load (fx_rate table):

1. Extract rates from HMRC, ECB, FED, or other source
2. Insert into fx_rate with effective_start_dt, effective_end_dt, and fx_rate_type_cd
3. No service restart required; application queries on-demand

Period-End Rate Lock (fiscal_period_fx_rate table, pending):

1. At fiscal period close, run a job to identify applicable FX rates per currency pair
2. Insert locked rate into fiscal_period_fx_rate
3. Downstream revenue recognition and GL posting use only fiscal_period_fx_rate rates for the period

Testing Scenarios

• Rate not found: Verify fallback behavior (error vs. override)
• Multiple rates for same period: Verify precedence logic (DAILY_SPOT > MONTHLY_AVG)
• Rate change mid-period (transaction date ≠ period-end date): Verify correct rate is used
• Period close with missing rates: Verify system halts and requires manual entry before proceeding

---

8. Future Enhancements

• Real-time rate feeds: Automated ingestion from Bloomberg, Reuters, or bank APIs
• Rate forecasting: Predict future rates for budget/forecast scenarios
• Multi-base currency support: Allow periods with non-GBP functional currencies
• Rate variance analysis: Track and report FX impact on revenue and GL balances