Currencies, Exchange Rates & VAT Regions

How RMS Handles Multi-Currency#

Oracle RMS is designed from the ground up to operate across multiple currencies. Every monetary value in the system — supplier costs, retail prices, order totals, stock ledger entries — is stored alongside a currency code, and the system maintains a complete exchange rate history to convert between them.

At the top of every financial transaction in RMS sits the concept of the primary currency — the currency in which the retailer's books are kept and to which all other monetary values eventually convert. The primary currency is set in SYSTEM_OPTIONS and cannot be changed after go-live without a full data migration.

Below the primary currency, RMS supports three scenarios:

• Single-currency operations — the vast majority of Indian retailers. Everything from suppliers to stores uses INR.
• Multi-currency purchasing — the retailer buys from international suppliers in USD, EUR, or GBP, but sells in INR. RMS converts costs at the point of PO creation using the configured exchange rate type.
• Multi-currency retail — full multi-currency operations including different retail prices in different currencies, typically for cross-border or franchise scenarios.

The CURRENCIES Table — Currency Master#

The CURRENCIES table is the master list of all currencies the RMS instance recognises. A currency must exist here before it can be referenced anywhere else in the system — on suppliers, locations, exchange rates, or price zones.

Exchange Rate Types — Operational vs Buy vs Sell#

Oracle RMS does not use a single exchange rate. It maintains three separate rate types that serve different business purposes. Understanding which rate type is used where is essential for any developer working on purchasing or finance integrations.

CURRENCY_RATES — The Daily Rate Feed#

Exchange rates in RMS are date-effective. Every day's rate for every currency pair is stored as a separate row in CURRENCY_RATES, creating a full history. The batch job CURRCONV is responsible for loading new exchange rates into this table — it's one of the most critical foundation batches that must run before any purchasing activities.

Primary Currency & Cost Conversion Flow#

Understanding exactly how RMS converts costs when a PO is created against a foreign-currency supplier is important for both developers and finance teams. The conversion follows a strict chain:

1. Supplier cost is stored in ITEM_SUPPLIER.UNIT_COST in the supplier's currency (SUPS.CURRENCY_CODE)
2. At PO creation, RMS looks up CURRENCY_RATES for FROM_CURRENCY = supplier_currency, TO_CURRENCY = primary_currency, RATE_TYPE = 'B', with the effective date ≤ the PO's order date
3. The converted cost is stored on ORDLOC.UNIT_COST in the primary currency
4. All stock ledger entries use the primary currency cost

VAT Regions — How Tax Geography Works in RMS#

In Oracle RMS, tax is not calculated at the item level in isolation. Tax is determined by the intersection of where the item is sold (the VAT Region) and what the item is (the VAT Code on the item's Department or the item itself). This two-axis model allows a single item to be taxed differently in different states or countries.

A VAT Region is a geographic grouping — it could be a country, a state, a tax jurisdiction, or any other geographic boundary that has its own tax rules. In India, for example, you might have a VAT Region per GST state code, or a single national VAT Region if you operate a simplified tax structure.

VAT Codes & the Tax Calculation Chain#

VAT Codes define the actual tax rates and calculation methods. They link to VAT Regions via the VAT_CODE_RATES table, which stores the actual percentage for each VAT Code within each VAT Region. This allows the same VAT Code (e.g., "Standard Rate") to map to 18% in one region and 12% in another.

Key Tables — Quick Reference#

Practical SQL Examples#

1. Get the current Buying rate for a currency pair

-- Most recent Buying rate from USD → INR
-- Uses MAX(RATE_DATE) to get the latest rate on or before today
SELECT cr.from_currency,
cr.to_currency,
cr.rate_date,
cr.conversion_rate
FROM   currency_rates cr
WHERE  cr.from_currency = 'USD'
AND  cr.to_currency   = 'INR'
AND  cr.rate_type     = 'B'
AND  cr.rate_date     = (
SELECT MAX(cr2.rate_date)
FROM   currency_rates cr2
WHERE  cr2.from_currency = cr.from_currency
AND  cr2.to_currency   = cr.to_currency
AND  cr2.rate_type     = cr.rate_type
AND  cr2.eff_date  = TRUNC(SYSDATE)
);

2. Convert a supplier cost to primary currency

-- Convert unit cost from supplier currency to INR (primary)
-- for all active item-supplier records for a given supplier
SELECT im.item,
im.item_desc,
is2.unit_cost                                              orig_cost,
s.currency_code                                           orig_currency,
ROUND(is2.unit_cost * cr.conversion_rate, 2)             inr_cost,
cr.rate_date                                              rate_used
FROM   item_supplier  is2
JOIN   item_master    im   ON im.item     = is2.item
JOIN   sups           s    ON s.supplier  = is2.supplier
JOIN   currency_rates cr   ON  cr.from_currency = s.currency_code
AND cr.to_currency   = 'INR'
AND cr.rate_type     = 'B'
AND cr.rate_date     = (
SELECT MAX(cr2.rate_date)
FROM   currency_rates cr2
WHERE  cr2.from_currency = cr.from_currency
AND  cr2.to_currency   = cr.to_currency
AND  cr2.rate_type     = cr.rate_type
)
WHERE  is2.supplier  = :v_supplier
AND  im.status     = 'A'
AND  s.currency_code != 'INR'   -- only foreign-currency suppliers
ORDER BY im.item;

3. VAT rate applicable at a store for an item's department

-- Full VAT calculation chain: item → dept → vat_code → store → vat_region → rate
SELECT im.item,
im.item_desc,
dp.vat_code,
vc.vat_code_desc,
s.store,
s.store_name,
vr.vat_region_name,
vcr.vat_rate,
vc.incl_in_retail_ind
FROM   item_master      im
JOIN   deps             dp   ON dp.dept        = im.dept
JOIN   vat_codes        vc   ON vc.vat_code    = dp.vat_code
JOIN   store            s    ON s.store        = :v_store
JOIN   vat_region       vr   ON vr.vat_region  = s.vat_region
JOIN   vat_code_rates   vcr  ON  vcr.vat_region = s.vat_region
AND vcr.vat_code   = dp.vat_code
AND vcr.eff_date   = (
SELECT MAX(vcr2.eff_date)
FROM   vat_code_rates vcr2
WHERE  vcr2.vat_region = vcr.vat_region
AND  vcr2.vat_code   = vcr.vat_code
)
WHERE  im.item    = :v_item;

4. PL/SQL — validate currency rates exist for today before batch

-- Pre-batch validation: confirm today's buying rates exist
-- for all currencies used by active suppliers
-- Run this before CURRCONV-dependent batches
PROCEDURE validate_currency_rates_today IS
CURSOR c_missing IS
SELECT DISTINCT s.currency_code
FROM   sups s
WHERE  s.sup_status    = 'A'
AND  s.currency_code != (SELECT currency_code FROM system_options)
AND  NOT EXISTS (
SELECT 1
FROM   currency_rates cr
WHERE  cr.from_currency = s.currency_code
AND  cr.to_currency   = (SELECT currency_code FROM system_options)
AND  cr.rate_type     = 'B'
AND  cr.rate_date     = TRUNC(SYSDATE)
);
BEGIN
FOR r IN c_missing LOOP
-- Log or raise alert for each missing currency rate
INSERT INTO batch_error_log (
batch_name, error_date, error_message
) VALUES (
'PRE_BATCH_CURRENCY_CHECK',
SYSDATE,
'Missing today''s Buying rate for currency: ' || r.currency_code
);
END LOOP;
COMMIT;
END validate_currency_rates_today;

Common Gotchas for Developers#

• !

  Using Operational rate instead of Buying rate for PO costs. RMS uses RATE_TYPE = 'B' (Buying) for purchase order cost conversion. Using 'O' (Operational) gives a different number. This discrepancy is invisible in single-rate setups but breaks immediately when your treasury feeds different buy/sell/operational rates.

• !

  Not handling the "latest rate on or before date" pattern. Exchange rates in CURRENCY_RATES are effective-dated. A simple WHERE RATE_DATE = TRUNC(SYSDATE) will return zero rows on weekends and holidays when no rate feed runs. Always use the MAX subquery pattern to get the most recent rate on or before the transaction date.

• !

  Confusing INCL_IN_RETAIL_IND with INCL_IN_COST_IND. In India, retail prices are MRP-inclusive (INCL_IN_RETAIL = Y), but costs from suppliers are typically exclusive (INCL_IN_COST = N). Getting these backwards causes all margin calculations to be wrong — and the error only surfaces when comparing cost and retail in reporting.

• !

  Setting DECIMALS_USED incorrectly at currency setup. Once transactions exist in the system using a currency, changing DECIMALS_USED is a data migration, not a config change. Setting 2 decimals for JPY (which uses 0) or vice versa causes systemic rounding errors across all cost and retail calculations. Audit this at setup time.

• !

  Forgetting that VAT_CODE is on DEPS, not ITEM_MASTER. Individual items inherit their VAT treatment from their Department's VAT_CODE. Items can have their own VAT code override, but the default is always at Department level. Queries that look for VAT code on ITEM_MASTER without a fallback to DEPS will miss the vast majority of items.

Key Takeaways#