Multi-Currency ERP Integration: How to Connect Currency APIs to NetSuite, SAP, and Accounting Systems

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 * @NModuleScope SameAccount
 */
define(['N/http', 'N/currency', 'N/runtime', 'N/log'],
  (http, currency, runtime, log) => {

  const API_KEY = runtime.envVars.CURRENCY_API_KEY;
  const API_URL = 'https://currency-exchange.app/api/v1-rates';
  const CURRENCY_PAIRS = [
    { from: 'USD', to: 'EUR' },
    { from: 'USD', to: 'GBP' },
    { from: 'USD', to: 'JPY' },
    { from: 'USD', to: 'CAD' },
    { from: 'USD', to: 'AUD' },
  ];

  function execute(context) {
    // Fetch latest rates from the API
    const response = http.request({
      method: http.Method.GET,
      url: `${API_URL}?base=USD`,
      headers: {
        'x-api-key': API_KEY,
        'Content-Type': 'application/json',
      },
    });

    if (response.code !== 200) {
      log.error({
        title: 'Rate fetch failed',
        details: `Status: ${response.code}, Body: ${response.body}`,
      });
      return;
    }

    const rates = JSON.parse(response.body);

    // Write each rate to NetSuite
    for (const pair of CURRENCY_PAIRS) {
      const rate = rates.rates?.[pair.to];
      if (!rate) {
        log.error({
          title: 'Missing rate',
          details: `No rate returned for ${pair.from}/${pair.to}`,
        });
        continue;
      }

      try {
        currency.exchangeRate({
          from: pair.from,
          to: pair.to,
          date: new Date(),
          rate: rate,
        });
        log.audit({
          title: 'Rate updated',
          details: `${pair.from}/${pair.to} = ${rate}`,
        });
      } catch (e) {
        log.error({
          title: 'Rate write failed',
          details: `${pair.from}/${pair.to}: ${e.message}`,
        });
      }
    }
  }

  return { execute };
});

DATA: lv_from_curr TYPE tcurc-waers VALUE 'USD',
      lv_to_curr   TYPE tcurc-waers VALUE 'EUR',
      lv_rate      TYPE bapi1093_0-exch_rate,
      lv_valid_from TYPE sy-datum,
      lv_return    TYPE bapiret2.

* Fetch rate from currency API via HTTP client
lv_rate = zcl_currency_api=>get_exchange_rate(
  iv_from = lv_from_curr
  iv_to   = lv_to_curr
  iv_date = sy-datum
).

* Write to SAP exchange rate table
CALL FUNCTION 'BAPI_EXCHANGERATE_CREATE'
  EXPORTING
    exch_rate       = VALUE bapi1093_0(
      rate_type    = 'M'
      from_curr    = lv_from_curr
      to_currty    = lv_to_curr
      valid_from   = lv_valid_from
      exch_rate    = lv_rate
    )
  IMPORTING
    return          = lv_return.

IF lv_return-type CA 'EA'.
  " Log error for review
ENDIF.

interface QboExchangeRate {
  CurrencyCode: string;
  RateValue: number;
  AsOfDate: string;
}

async function syncRatesToQuickBooks(
  currencyPairs: Array<{ from: string; to: string }>
): Promise<void> {
  for (const pair of currencyPairs) {
    // 1. Fetch rate from currency API
    const rateResponse = await fetch(
      'https://currency-exchange.app/api/v1-rates',
      {
        headers: {
          'x-api-key': process.env.CURRENCY_API_KEY!,
        },
      }
    );
    const rateData = await rateResponse.json();
    const rate = rateData.rates?.[pair.to];
    if (!rate) continue;

    // 2. Push to QuickBooks Online
    const qboResponse = await fetch(
      `https://quickbooks.api.intuit.com/v3/company/${process.env.QBO_REALM_ID}/exchangerate?currency=${pair.to}&asofdate=${new Date().toISOString().split('T')[0]}`,
      {
        method: 'PUT',
        headers: {
          'Authorization': `Bearer ${await getQboToken()}`,
          'Content-Type': 'application/json',
          'Accept': 'application/json',
        },
        body: JSON.stringify({
          CurrencyCode: pair.to,
          RateValue: rate,
          AsOfDate: new Date().toISOString().split('T')[0],
        } as QboExchangeRate),
      }
    );

    if (!qboResponse.ok) {
      console.error(
        `QBO rate update failed for ${pair.to}: ${qboResponse.status}`
      );
    }
  }
}

interface RateEntry {
  from: string;
  to: string;
  rate: number;
  timestamp: string;
  source: string;
}

interface ValidationResult {
  valid: boolean;
  reason?: string;
}

function validateRate(
  current: RateEntry,
  previous: RateEntry | null,
  maxChangePercent: number
): ValidationResult {
  if (!previous) return { valid: true };

  const changePercent =
    Math.abs((current.rate - previous.rate) / previous.rate) * 100;

  if (changePercent > maxChangePercent) {
    return {
      valid: false,
      reason: 'Rate changed ' + changePercent.toFixed(2) + '% (' + previous.rate + ' to ' + current.rate + '), exceeds ' + maxChangePercent + '% threshold',
    };
  }
  return { valid: true };
}

async function syncRates(
  pairs: Array<{ from: string; to: string }>,
  options: {
    maxChangePercent: number;
    onRateUpdate: (rate: RateEntry) => Promise<void>;
    onValidationError: (
      rate: RateEntry, result: ValidationResult
    ) => Promise<void>;
    logger: {
      info: (msg: string) => void;
      warn: (msg: string) => void;
      error: (msg: string) => void;
    };
  }
): Promise<void> {
  const { maxChangePercent, onRateUpdate, onValidationError, logger } = options;

  for (const pair of pairs) {
    // Fetch current rate
    const response = await fetch(
      'https://currency-exchange.app/api/v1-rates',
      {
        headers: { 'x-api-key': process.env.CURRENCY_API_KEY! },
      }
    );

    if (!response.ok) {
      logger.error(`Fetch failed for ${pair.from}/${pair.to}`);
      continue;
    }

    const data = await response.json();
    const rate = data.rates?.[pair.to];
    if (typeof rate !== 'number') {
      logger.warn(`No rate data for ${pair.from}/${pair.to}`);
      continue;
    }

    const entry: RateEntry = {
      from: pair.from,
      to: pair.to,
      rate,
      timestamp: new Date().toISOString(),
      source: 'currency-exchange.app',
    };

    // Validate against previous rate (stored in DB)
    const previous = await getPreviousRate(pair.from, pair.to);
    const validation = validateRate(entry, previous, maxChangePercent);

    if (!validation.valid) {
      logger.warn(
        `Validation failed for ${pair.from}/${pair.to}: ${validation.reason}`
      );
      await onValidationError(entry, validation);
      continue; // Don't push unvalidated rates to ERP
    }

    // Push to ERP via configured adapter
    await onRateUpdate(entry);
    logger.info(
      `Updated ${pair.from}/${pair.to}: ${rate} at ${entry.timestamp}`
    );

    // Store in rate history table
    await storeRate(entry);
  }
}