Contact Us
A person pointing at a document at his busy desk while another person watches attentively.

Why We Used This Query

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.

SQL Query for Journal Entries

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 the Query Returns

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

How to Save SuiteQL Queries

Saving a SuiteQL Query is a built-in feature of Tim Dietrich’s SuiteQL Query Tool. Enabling this functionality requires a few simple customizations: you'll need to create a folder in the File Cabinet and make a minor edit to the SuiteQL Query Tool script. The steps to set this up are outlined below.

Read the Article A person holding a white coffee cup before a plant.

Tagged with Financial Management