Integrating Netsuite back-end into Snowflake – how to find the exact currency exchange rates?

I’m using imported back-end tables from Netsuite in Snowflake.

I’m trying to determine exact exchange rates for foreign currency transactions to get the identical totals Netsuite has on the front-end. So I tried using Netsuite’s “Currency” and “CurrencyRate” tables, but the amounts I am getting are incorrect.

The exchange rates from the table don’t seem the same as those used on the front-end. Every amount I get is off by a little, which adds up when there are many transactions.

Is there another table I should keep an eye out for, or do they do something completely different to keep track of all the effective exchange rates on the back-end? Here’s the query I have so far:

select 
    transline.transaction, 
    transline.id as line_item_id, 
    transline.creditforeignamount, 
    transline.rate, 
    transline.rateamount, 
    tran.currency as transaction_currency, 
    tran.recordtype, 
    tran.trandate::date as trandate, 
    tran.TRANID as netsuite_document_number, 
    tran.custbody_cust_primcurrfxrate, 
    transline.custcol_invoice_prim_curr_fx_rate, 
    cr.exchangerate as actualexchangerate, 
    round((case 
        when cr.exchangerate not like 'null' then (cr.exchangerate * transline.creditforeignamount)
        else creditforeignamount
    end), 2) as amount
from transactionline transline
inner join transaction tran 
on tran.id = transline.transaction
left join currencyrate cr 
on (tran.currency = cr.transactioncurrency 
and tran.custbody_cust_primcurrfxrate = cr.basecurrency 
and tran.closedate = cr.effectivedate)

Rookie Asked on July 13, 2022 in How To's.
Add Comment
0 Answer(s)

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Become a Sponsor   Become a Sponsor