/**
 * @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
        };
    });

/**

* @NApiVersion 2.1

* @NScriptType UserEventScript

* @NModuleScope SameAccount

*

* Update Amortization Journal Entries with a more meaningful memo field, and source transaction tagged in custom fields.

*

* Code was adapted from a sample shared at:

* <https://netsuite.smash-ict.com/learn-how-to-generate-meaningful-amortization-journal-entry-memo-lines/>

*

* Author: Michoel Chaikin <<mailto:michoel.chaikin@carsales.com.au|michoel.chaikin@carsales.com.au>>

*/

define(["N/record", "N/query", "N/redirect"], /**

* @param { import("N/record") } record

* @param { import("N/query")} query

* @param { import("N/redirect")} redirect

* @returns { any } script definition

*/

(record, query, redirect) => {

/**

* @param { import("N/types").EntryPoints.UserEvent.beforeLoadContext } context

*/

function

beforeLoad(context) {

// Journals created through the "Create Amortization Journal Entry" page do not trigger user event scripts unless

// workflow based Approval Routing is enabled for Journals (see SuiteAnswer # 85137)

// To ensure the script is executed, it is run on beforeLoad the first time the record is viewed

const isFromAmortization = context.newRecord.getValue("isfromamortization");

const firstLineAmortizationSource = context.newRecord.getSublistValue({

sublistId: "line",

line: 0,

fieldId: "custcol_cs_amortization_source",

});

if

(context.type !== context.UserEventType.VIEW ||

!context.newRecord.id ||

!isFromAmortization ||

firstLineAmortizationSource

) {

*return*;

}

const schedules = getSchedules(context.newRecord);

if

(schedules.length <= 0) {

log.debug({ title: "No schedules found on JE lines"

});

*return*;

}

const sourceTransactionDetails = getSourceTransactionDetails(schedules);

try

{

updateJournal(context, sourceTransactionDetails);

// Redirect back to the record so our updates are reflected

redirect.toRecord({ type: context.newRecord.type, id: context.newRecord.id });

} *catch*(error) {

// Updating could fail if the user doesn't have edit permission or the journal is in a closed period

log.error({ title: "Error updating journal", details: JSON.stringify(error) });

}

}

/**

* Retrieve the schedule numbers from the JE lines.

* We need to retrieve the schedules from the record because the fields are not accessible via SuiteQL

*

* @param { import("N/record").Record } rec

* @returns {string[]} Array of the schedule Internal Numbers

*/

function

getSchedules(rec) {

const schedules = [];

const sublistId = "line";

for

(*let*

line = 0; line < rec.getLineCount({ sublistId }); line++) {

const schedule = rec.getSublistValue({ sublistId, line, fieldId: "schedulenum"

});

if

(schedule) {

schedules.push(schedule);

}

}

return

[...*new*

Set(schedules)]; // Schedule is repeated for source and destination lines. Remove the duplicates

}

/**

* @typedef {{scheduleId: string, id: string, tranId: string, type: string, memo: string, lineSequenceNumber: string, lineId: string, item: string} } SourceTransactionDetails

*/

/**

* @param {string[]} schedules List of Internal IDs of the Amortization Schedules

* @returns { SourceTransactionDetails[] } Details from the source transactions

*/

function

getSourceTransactionDetails(schedules) {

SELECT

amortizationSchedule.id AS scheduleId,

transaction.id,

transaction.tranId,

transaction.type,

transactionLine.id AS lineid,

transactionLine.memo,

transactionLine.lineSequenceNumber,

transactionline.item

FROM

amortizationSchedule

JOIN transaction ON (transaction.id = amortizationSchedule.sourceTran)

JOIN transactionLine ON (

transactionLine.transaction = transaction.id

AND transactionLine.amortizationSched = amortizationSchedule.id)

WHERE

AmortizationSchedule.id IN (${schedules.join()})

return

query

.runSuiteQL({ query: sql })

.asMappedResults()

.map(result => ({

scheduleId: String(result.scheduleid),

id: String(result.id),

tranId: String(result.tranid),

type: String(result.type),

lineId: String(result.lineid),

memo: String(result.memo),

lineSequenceNumber: String(result.linesequencenumber),

item: String(result.item)

}));

}

/**

* @param { import("N/types").EntryPoints.UserEvent.beforeLoadContext } context

* @param { SourceTransactionDetails[] } sourceTransactionDetails

*/

function

updateJournal(context, sourceTransactionDetails) {

const rec = record.load({ type: context.newRecord.type, id: context.newRecord.id });

const sublistId = "line";

for

(*let*

line = 0; line < rec.getLineCount({ sublistId }); line++) {

const scheduleId = rec.getSublistValue({ sublistId, line, fieldId: "schedulenum"

});

const memo = rec.getSublistValue({ sublistId, line, fieldId: "memo"

});

const details = sourceTransactionDetails.find((x) => x.scheduleId === scheduleId);

if

(!details) {

*continue*;

}

if

(details) {

// Requirement from our accounting team to link back to the source transaction in reports.

// Thus, we use custom columns field to capture the amortization source info.

rec.setSublistValue({ sublistId, line, fieldId: "custcol_cs_amortization_source", value: details.id });

rec.setSublistValue({ sublistId, line, fieldId: "custcol_cs_amortization_source_line", value: details.lineId });

rec.setSublistValue({ sublistId, line, fieldId: "memo", value: formatMemo(memo, details) });

}

}

rec.save({ ignoreMandatoryFields: *true*

});

}

/**

* @param { string } existingMemo

* @param { SourceTransactionDetails } details

* @returns { string } formatted memo

*/

function

formatMemo(existingMemo, details) {

if

(existingMemo !== "Amortization Destination"

&& existingMemo !== "Amortization Source") {

return

existingMemo;

}

let

lineType;

if

(details.type === "Journal") {

lineType = "";

} *else*

{

lineType = details.item ? "Item "

: "Expense ";

}

return

}

return

{

beforeLoad

};

});