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

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

-- 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

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

-- 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#

Key Takeaways#