Exporting SuiteAnalytics Dataset as SQL
Learn how to export NetSuite datasets as SQL queries to uncover complex joins, field relationships, and improve script performance.
Read the Article
Starting from a revenue recognition journal entry, clients often want more data on the JE lines. However, we can’t easily touch JE lines with searches AND the info we want is several joins away.
SELECT
t.id AS journal_entry_id,
rppr.revenueplan AS revenue_plan_id,
rp.createdfrom AS revenue_element_id,
rp.recordnumber AS rev_plan_name,
re.sourcerecordtype AS source_rec_type,
re.source AS source_transaction_name,
re.item AS revenue_element_item,
re.referenceid AS source_transaction_id,
tl.uniquekey AS source_transaction_line_key,
tl.linesequencenumber AS source_transaction_line_num
FROM
revenueplanplannedrevenue rppr
JOIN transaction t ON rppr.journal = t.id
JOIN revenueplan rp ON rp.id = rppr.revenueplan
JOIN revenueelement re ON re.id = rp.createdfrom
JOIN sourcedetails sd ON sd.revenueelement = re.id
JOIN transactionline tl ON tl.uniquekey = sd.sourceid
WHERE
t.id = ${ journalId }What this gives us, given a particular journal entry id, is data about the rev rec plan, the revenue element, the originating invoice, and the line on the invoice that it is generated from. In this instance, I wanted the line number (source_transaction_line_num) in a human readable format, but you can also get the line unique key if you want (source_transaction_line_key)
The invoice id here is (source_transaction_name). Note that it might not always be an invoice but that is the most common in the environment I was working in.
Related Articles
Tagged with Financial Management