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)