Hey all, I am trying fetch a trial balance from NS using suiteQL. Currently, I have a query which is mostly correct, but there are small discrepancies. One is a deferred revenue account, for example where the final balance has a greater credit value than what is show in the TB observed in the "Reports" section. Can anyone tell me if: 1. there is somewhere I can find a specification of what parameters make up Netsuite's trial balance 2. or if they recognize any gotchas I might be missing here? Here's the query:

SELECT ACCOUNT.id AS acct_id,
    ACCOUNT.acctnumber AS acct_code,
    SUM(TransactionAccountingLine.netamount) AS acct_balance
FROM TRANSACTION,
    ACCOUNT,
    TransactionAccountingLine,
    transactionLine
WHERE TransactionAccountingLine.ACCOUNT = ACCOUNT.ID(+)
    AND transactionLine.TRANSACTION = TransactionAccountingLine.TRANSACTION
    AND transactionLine.ID = TransactionAccountingLine.transactionline
    AND TRUNC(TRANSACTION.trandate) <= TO_DATE('2021-01-31', 'YYYY-MM-DD')
    AND TRANSACTION.ID = transactionLine.TRANSACTION
    AND ACCOUNT.isinactive = 'F'
    AND TRANSACTION.posting = 'T'
    AND ACCOUNT.ID IS NOT NULL
    AND ACCOUNT.issummary = 'F'
GROUP BY (ACCOUNT.id, ACCOUNT.acctnumber)

There are a lot more gotchas, especially if you are trying to tie back to the TB report line for line, though you are certainly on the right track. Although it's possible to accomplish this in one query, it is not for the faint of heart, especially if you are trying to include balance sheet accounts, income statement accounts, retained earnings, and cumulative translation adjustment. A couple questions for you out of curiosity: 1. What's the use case? 2. Are you actually trying to reproduce the entire TB report line for line?

Andrew, Are you using oneworld or non-oneworld? Also, are you subsidiaries base USD or not? I was able to create a trial balance query by combining separate Income Statement and Balance Sheet and it ties to the penny in a USD-base multi-sub environment