Learn How to Generate Meaningful Amortization Journal Entry Memo Lines

Larry, the Senior Developer at Asoville, has been tasked with what seems like a straightforward question: Is there a way to change the memos on Amortization Journal Entry (AJE) lines from the default “Amortization Source” and “Amortization Destination”? Perhaps, you are interested in the answer too. Let’s learn from Larry!

TL;DR

Short answer: It is possible via scripting to replace the default amortization journal entry line memos with something more useful, e.g. information from the source transaction. This article explains the pattern and provides a deployment-ready code sample.

The solution described in this article works if you use a custom JE approval workflow. If you use the native JE approval option or no JE approvals at all, refer to the companion article here for an adapted solution.

Problem

NetSuite’s Amortization feature ^[I]NetSuite (March 4, 2011). Amortization Feature Overview. Available at: https://netsuite.custhelp.com/app/answers/detail/a_id/7864. [Accessed on: July 30, 2021] “enables you to record the general ledger impact of item purchases and expense charges across multiple future periods”. System-generated Amortization Journal Entries (AJEs) are used to post the amortized expenses to the general ledger.

One major annoyance of AJEs is that the line memos do not reflect the memos from the source transaction but simply state “Amortization Source” or “Amortization Destination”. This makes analysis of AJEs cumbersome as the user has to drill down to the amortization schedule to get to the source transaction (e.g. vendor bill, vendor credit, or journal entry) and then find the corresponding source line.

As far as I know, NetSuite does not offer any option to automatically grab the memo from AJE source transactions. As such, we need to take matters into our own hands.

The request from Larry’s finance team was to show the transaction number and memo from the source transaction in the corresponding lines of the AJE. Reasonable, right?

Solution

Before presenting the script, it is useful to understand the underlying data model:

A transaction line can be associated with an amortization template. Upon saving the transaction, NetSuite automatically generates an amortization schedule based on that template. Thus, an amortization schedule is associated with exactly one transaction line. The amortization schedule produces amortization journal entries to recognize expenses. A single amortization journal entry typically consolidates multiple amortization schedules. As such, each debit/credit pair of lines in the AJE may be related to a different source transaction!

As illustrated above, there are multiple hops from the AJE to the source transaction. And, as you may know, NetSuite’s search module has a limitation of a single hop/join. As such, it is not easy or efficient to write searches to access the source transaction lines from the AJE, especially given that one AJE typically references multiple schedules and source transactions. To be clear, it is possible to solve this problem using only searches. However, the code will be longer and require much more governance units.

Gladly, NetSuite now features a querying module^[II]NetSuite (August 28, 2018). N/query Module. Available at: https://netsuite.custhelp.com/app/answers/detail/a_id/77359. [Accessed on: July 30, 2021] that supports multi-level joins! Although this functionality is relatively new, it is very promising and suitable for the current challenge. Refer to this article to learn more about N/query vs. N/search.

Using N/query to Address The Multi-Level Join Challenge

The following User Event script solves the problem. A decent degree of proficiency with SuiteScript and SQL is necessary to fully understand this code sample.

Tip: This code is written in SuiteScript 2.1. If you prefer SuiteScript 2.0 for whatever reason, you’ll need to replace the arrow functions and template strings with anonymous functions and concatenated strings, respectively.

/**
 * @NApiVersion 2.1
 * @NScriptType UserEventScript
 * @NModuleScope SameAccount
 */

define(['N/record', 'N/query'],
    function (record, query) {
        function afterSubmit(context) {
            // We need to run this in afterSubmit as it appears from tests that the schedulenum field
            // was blank/uninitialized in the beforeSubmit hook when the JE is created (i.e. context.type = 'create').
            // That could be an issue if JEs are set up to automatically post. As such, we use afterSubmit to avoid any problem.
            if (context.type === 'delete') {
                return;
            }

            var rec = context.newRecord;
            if (rec.getValue('approvalstatus') === '2' /*= Approved*/) {
                log.debug(arguments.callee.name, `Ignoring non-amortization or already approved (i.e. non-editable) JE: ${rec.getValue('tranid')} (ID: ${rec.id})`);
                return;
            }

            // Since we're in the afterSubmit event, we need to load the record in order to persist changes.
            rec = record.load({ type: rec.type, id: rec.id });

            const LINE_SUBLIST = 'line';
            var memo, schedule;
            var schedulesByLine = {}
            var schedules = [];

            // Note: We resort to looping here because the schedulenum field is currently not exposed via SuiteQL
            //       and the exposed amortizationsched field is null on AJEs.
            //       Therefore, we cannot directly join all data in one query.
            for (var i = 0; i < rec.getLineCount(LINE_SUBLIST); ++i) {
                memo = rec.getSublistValue({ sublistId: LINE_SUBLIST, fieldId: 'memo', line: i });
                schedule = rec.getSublistValue({ sublistId: LINE_SUBLIST, fieldId: 'schedulenum', line: i });
                if (schedule && (memo === 'Amortization Source' || memo === 'Amortization Destination')) {
                    schedulesByLine[i] = schedule;

                    if (schedules.indexOf(schedule) < 0) {
                        schedules.push(schedule);
                    }
                }
            }

            if (schedules.length > 0) {
                log.audit(arguments.callee.name, 'Executing query to retrieve source transactions from schedules: ' + schedules);

                var queryResults = query.runSuiteQL({
                    query: 
                        `SELECT 
                            sch.id, 
                            sch.sourcetran
                        FROM 
                            AmortizationSchedule AS sch
                        WHERE 
                            sch.id IN (${schedules.join()})`
                }).asMappedResults();

                // Goal: For each source transaction, retrieve data from the line tagged with
                //       the specified amortization schedule (1:1 relationship guaranteed by data model).
                var whereClause = '';
                queryResults.forEach(result => {
                    if (whereClause) {
                        whereClause += ' OR ';
                    }
                    whereClause += '(line.transaction = ' + result.sourcetran + ' AND line.amortizationsched = ' + result.id + ')';
                });

                queryResults = query.runSuiteQL({
                    query: 
                        `SELECT 
                            trx.tranid, 
                            trx.type, 
                            line.memo, 
                            line.amortizationsched, 
                            line.linesequencenumber, 
                            line.item
                        FROM 
                            TransactionLine AS line 
                            INNER JOIN Transaction AS trx 
                                ON (trx.id = line.transaction)
                        WHERE ${whereClause}`
                }).asMappedResults();

                var lineInfoBySchedule = {};
                var lineType;
                queryResults.forEach(result => {
                    lineType = (result.type === 'Journal') ? '' : (result.item ? 'Item ' : 'Expense ');

                    // Adjust as needed to get the desired info. 
                    // This implementation captures the transaction, line and memo.
                    lineInfoBySchedule[result.amortizationsched] = 
                        `[${result.tranid} @${lineType}Line ${result.linesequencenumber}] ${result.memo}`;
                });

                // Now we have all the information, we can update the lines of the AJE
                for (i = 0; i < rec.getLineCount(LINE_SUBLIST); ++i) {
                    schedule = schedulesByLine[i];
                    if (schedule) {
                        memo = rec.getSublistValue({ sublistId: LINE_SUBLIST, fieldId: 'memo', line: i });
                        memo = lineInfoBySchedule[schedule] + ' (' + memo + ')';
                        rec.setSublistValue({ sublistId: LINE_SUBLIST, fieldId: 'memo', line: i, value: memo });
                    }
                }

                rec.save({ ignoreMandatoryFields: true });
            } else {
                log.debug(arguments.callee.name, 'No schedules found on JE lines');
            }
        }

        return {
            afterSubmit: afterSubmit
        };
    });

The script consists of four main parts:

1. Identify matching lines whose memos need to be replaced.
2. Retrieve the source transactions from the amortization schedules linked to those lines.
3. Get the desired information from the source transactions.
4. Update the AJE line memos.

Note: In the ideal world, we should be able to write a single SQL query to cover items (1) – (3) above. However, due to the following limitations at the time of writing, we have to resort to discrete steps.

Data Limitations

Although the field AmortizationSched is exposed in the SuiteAnalytics API (which N\query uses), the value is empty on AJEs. As per SuiteAnswers Answer ID 80572, the schedulenum field can be used to retrieve the amortization schedule from an AJE line. Ironically, this field is not exposed via the SuiteAnalytics API. As such, we have no way to join from the AJE to the amortization schedule to the transaction lines in one query. Thus, we resort to looping to get the schedules and then writing two separate SQL queries to get all the data that we need.

As stated, SuiteAnalytics is relatively new and is based on a different data model than the one that drives saved searches. The fact that the AmortizationSched field on AJEs is blank seems to be a glitch that may be fixed in the future. At that point, we can revisit this solution and simplify it.

Wrap Up

NetSuite’s SuiteAnalytics module is a breath of fresh air especially for developers with SQL knowledge. It is definitely worth your while as a NetSuite developer/technical consultant to start paying attention to this module as it opens doors to solve problems that are otherwise impossible or extremely difficult to solve with traditional search-based approaches.

Tim Dietrich has developed an excellent (and free!) SuiteQL Query Tool that allows you to explore SuiteAnalytics, construct and run your queries, etc. I highly recommend Tim’s tool.

Larry used this tool to produce the queries he needed to solve our challenge and only started scripting after he was sure that he could “reach” the data that he needed.

Back at Asoville, Larry and his team are very satisfied with the results. For Larry, this first exposure to SuiteQL and a forced refresher of the SQL he learned back in college, was a pleasant experience. One more tool in his toolbox to tackle upcoming challenges and one more weapon in the ongoing, never-ending debate: “To script or not to script?”. In cases like this where there’s really no non-scripting answer, I guess the more relevant question is: “To SuiteQL or to search?”.

If you’ve found this article useful, let us know! Consider subscribing to our no-nonsense email list to get notified of NetSuite insights as soon as we publish them. You can also become a NetSuite Insights contributor and share your NetSuite gems via this distinguished platform. Keep learning. Keep sharing!

Related Posts

1. Learn How to Generate Meaningful Amortization JE Memo Lines When Using Native or No JE Approval
2. Learn How to Generate Meaningful Rev. Rec. Journal Entry Memo Lines