https://netsuiteprofessionals.com logo
u

Umi

04/13/2022, 5:20 PM
Is there a column in transactionline that provides the net amount of a transaction line item in the company base currency? i.e. converting Euro lines to USD? If this is available, that would save a lot of steps for me in what I need to accomplish
1
I know this column exists in NetSuite when you look at individual requisitions or orders, but I am having trouble finding the correct column in SuiteQL
m

Matt Bernstein

04/13/2022, 6:46 PM
BUILTIN.CONSOLIDATE function I think is what you're looking for. I am consolidating to subsidiary with id 11 is consolidating all currencies to a US based currency that were posted in a foreign see query below
Copy code
SELECT
    transaction.id,
    transaction.number,
    transactionLine.foreignamount,
    BUILTIN.DF(transactionLine.subsidiary),
    transactionLine.subsidiary,
    BUILTIN.DF(transaction.currency) AS currency,
    TransactionAccountingLine.amount, 
    TransactionAccountingLine.debit, 
    TransactionAccountingLine.credit, 
    BUILTIN.CONSOLIDATE(TransactionAccountingLine.amount, 'INCOME', 'DEFAULT', 'DEFAULT', 11, transaction.postingperiod, 'DEFAULT')

FROM
    transactionLine
    INNER JOIN transaction ON transaction.id = transactionLine.transaction
    INNER JOIN TransactionAccountingLine ON (
        transaction.id = TransactionAccountingLine.transaction
        AND TransactionAccountingLine.transactionline = transactionLine.id
    )
WHERE
    TransactionAccountingLine.posting = 'T' 
FETCH NEXT 1000 ROWS ONLY
s

Selcuk Dogru

04/13/2022, 7:29 PM
you can also use
BUILTIN.CURRENCY_CONVERT(TL.netamount, 4, T.trandate)
to convert to any target currency
u

Umi

04/14/2022, 11:58 AM
These are great suggestions, but the project I am working on uses CData Sync to pull data and we unfortunately are not able to use BUILTIN in our queries. Is there another way to solve this without BUILTIN functions?
s

Selcuk Dogru

04/14/2022, 3:57 PM
Get the exchange rate and calculate on your end
Copy code
SELECT TOP 1 CR.exchangerate 
    FROM currencyrate CR 
    WHERE CR.basecurrency = C.id AND 
      CR.transactioncurrency = 4 AND 
      CR.effectivedate <= T.trandate
    ORDER BY CR.effectivedate DESC
4 Views