Learn How to Generate Meaningful Amortization JE Memo Lines When Using Native or No JE Approval

/**
 * @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 <
        
            mi*************@ca******.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) {
    const sql = `
      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 `${existingMemo}: [${details.tranId} ${lineType}Line # ${details.lineSequenceNumber}] ${details.memo}`;
  }

  return {
    beforeLoad
  };
});