How to Find NetSuite Revenue Recognition Journal Entries With SuiteQL

Finding Revenue Recognition Journal Entries in NetSuite

It's very difficult to identify which journal entries are associated with revenue recognition and which are not, especially when using saved searches. This can lead to Map/Reduce (MR) scripts that take longer than expected to run.

One approach is to use SuiteQL to find journal entries associated with revenue plan planned revenue records, then pass those results into the Map/Reduce script's getInputData() stage.

Using SuiteQL to Retrieve Revenue Recognition Journal Entries

The following example uses a posting period to retrieve journal entry IDs and dates associated with planned revenue records.

Example Map/Reduce script:

function getInputData() {
    const postingPeriod = getLastMonthPostingPeriodId();
    const RRJArray = buildRRJArray(postingPeriod);
    return RRJArray;
}

// Do other Map/Reduce processing here.

function buildRRJArray(lastMonthPeriodId) {
    const sql = `
        SELECT UNIQUE
            t.id,
            t.trandate,
            t.createddate
        FROM
            RevenuePlanPlannedRevenue rppr
        JOIN Transaction t
            ON rppr.journal = t.id
        WHERE
            rppr.postingperiod = ${lastMonthPeriodId}
    `;

    return query.runSuiteQL({ query: sql }).asMappedResults();
}

This example assumes that N/query is available as query and that getLastMonthPostingPeriodId() is defined elsewhere in the script.

How the SuiteQL Query Works

The key detail is that revenue plan planned revenue records can contain a reference to an associated journal entry.

The query uses the RevenuePlanPlannedRevenue record as its starting point and joins it to the Transaction record through rppr.journal = t.id.

The postingperiod filter limits the results to planned revenue records associated with the selected posting period. SELECT UNIQUE prevents duplicate rows with the same selected values.

We then use the filtered results to build an array of revenue recognition journal entries associated with those planned revenue records. That array becomes the input for the Map/Reduce script instead of a saved search.

What the Query Returns

The query selects three fields from the associated transaction:

• t.id — Internal ID of the associated transaction.
• t.trandate — Transaction date.
• t.createddate — Transaction creation date.

The asMappedResults() method returns the query results as an array of JavaScript objects, which can then be processed by the Map/Reduce script.

This approach can be useful when a saved search makes it difficult to isolate the revenue recognition journal entries needed for a particular posting period.