Learn How to Generate Meaningful Rev. Rec. Journal Entry Memo Lines

12 Replies to “Learn How to Generate Meaningful Rev. Rec. Journal Entry Memo Lines”

1. I am a little confused. This code is broken up and needs put together. Is there a complete template that is available to use?

   1. Hi there, the code is broken up in parts to facilitate understanding. Our hope is that a user who reads and understands the logic will be able to piece it together and adapt to match their needs. Thus, no template is provided.

2. Thanks for getting the ball rolling on this. We had a slightly different usecase where we needed to pull the arrangement from the element and a column field from the source transaction over to the JE. Full code below. can be easily modified to include additional fields from the element or source transaction.

   /**
   *
   *
   * @param {Object} scriptContext
   * @param {Record} scriptContext.newRecord
   * @param {Record} scriptContext.oldRecord
   * @param {String} scriptContext.type
   */
   function afterSubmit(scriptContext) {
   try {
   let rec = scriptContext.newRecord;
   let customForm = rec.getValue({ fieldId: ‘customform’ });

   if (customForm == 176) {
   rec = record.load({ type: rec.type, id: rec.id });

   //Step 1: Retrieve the Revenue Plans from the Rev. Rec. JEs
   var memo, revenuePlan;
   var revenuePlansByLine = {};
   var revenuePlans = [];

   for (var i = 0; i < rec.getLineCount({ sublistId: 'line' }); ++i) {
   memo = rec.getSublistValue({ sublistId: 'line', fieldId: 'memo', line: i });

   revenuePlan = rec.getSublistValue({ sublistId: 'line', fieldId: 'sourcerevenueplan', line: i });
   if (revenuePlan) {
   if (memo === 'Rev Rec Source' || memo === 'Rev Rec Destination') {
   revenuePlansByLine[i] = revenuePlan;

   if (revenuePlans.indexOf(revenuePlan) 0) {
   //Step 2: Retrieve the Revenue Elements from the Revenue Plans
   let queryResults = query.runSuiteQL({
   query:
   `SELECT
   element.id,
   element.revenuearrangement,
   plan.recordnumber
   FROM
   RevenuePlan as plan
   INNER JOIN RevenueElement as element
   ON (plan.createdfrom = element.id)
   WHERE
   plan.recordnumber IN ( ‘${revenuePlans.join(“‘,'”)}’ )`
   // We need to escape the strings to avoid ‘Unknown identifier XXX’ error
   }).asMappedResults();

   let revenueElements = [];
   let revPlansByRevElement = {};

   queryResults.forEach(result => {
   revenueElements.push(result.id);
   revPlansByRevElement[result.id] = {‘planid’: result.recordnumber, ‘revarrangement’: result.revenuearrangement};
   });

   //Step 3: Get the Source Transaction Lines from the Revenue Elements
   let revElementsByTrxLine = {}
   search.create({
   type: ‘revenueelement’,
   filters: [
   [‘internalid’, ‘anyof’, revenueElements]
   ],
   columns: [
   ‘sourceTransaction.lineuniquekey’
   ]
   }).run().each(result => {
   let line = result.getValue({ join: ‘sourceTransaction’, name: ‘lineuniquekey’ });
   revElementsByTrxLine[line] = result.id
   return true;
   });

   //Step 4: Get the Desired Information from the Source Transactions
   let lineInfoByRevPlan = {};
   queryResults = query.runSuiteQL({
   query:
   `SELECT
   trx.tranid,
   trx.type,
   line.memo,
   line.linesequencenumber,
   line.item,
   line.uniquekey,
   line.custcol_kes_sf_product_name
   FROM
   TransactionLine AS line
   INNER JOIN Transaction AS trx
   ON (trx.id = line.transaction)
   WHERE
   line.uniquekey in ( ${Object.keys(revElementsByTrxLine).join(‘,’)} )`
   }).asMappedResults();

   queryResults.forEach(result => {
   let elementid = revElementsByTrxLine[result.uniquekey];

   if(elementid)
   {
   let elementplan = revPlansByRevElement[elementid];

   if(elementplan && elementplan.planid)
   {
   let lineType = ”;
   // Adjust as needed to get the desired info.
   // This implementation captures the transaction, line and memo.
   lineInfoByRevPlan[elementplan.planid] = {
   ‘memo’: result.memo,
   ‘custcol_kes_sf_product_name’: result.custcol_kes_sf_product_name,
   ‘custcol_kes_related_ra’: elementplan.revarrangement
   };
   }
   }
   });

   //Step 5: Update the JE Line Memos
   /*
   let lineInfoBySchedule = {};
   let 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 (let i = 0; i < rec.getLineCount('line'); ++i) {
   let plan = revenuePlansByLine[i];
   if (plan && lineInfoByRevPlan[plan]) {
   let lineinfo = lineInfoByRevPlan[plan];
   rec.setSublistValue({ sublistId: 'line', fieldId: 'custcol_kes_sf_product_name', line: i, value: lineinfo.custcol_kes_sf_product_name });
   rec.setSublistValue({ sublistId: 'line', fieldId: 'custcol_kes_related_ra', line: i, value: lineinfo.custcol_kes_related_ra });
   }
   }

   rec.save({ ignoreMandatoryFields: true });
   }
   }
   }
   catch (ex) {
   log.error({ 'title': 'afterSubmit', 'details': ex });
   }
   }

   1. Adam, glad you figured it out and thanks for sharing your code.

3. I’m having trouble in step 5 (the line update) – when you call the SuiteQL query in step 5 where you pass the value of schedule into the function lineInfoBySchedule – I can’t work out what value you’re supposed to pass as [schedule] that sits on the journal record – can you help?

4. Hi. I found this article because I needed to do exactly what you do here. populating memo and other fields on the rev rec journal from the source invoice/sales order. I used your snippets in my code, but I have found out that there is actually much easier way to get what you need. Without going into too much details, What I do is:
   1) get the plans from the JE line (same as you really)
   2) run a revenue element search, where I can join the plan as a filter, but also populate details from an invoice to get the memo, etc.
   3) save the values into an object, where the revenue plan is the key and my custom values are the values
   4) loop through the lines and based on the revenue plan assign the field values based on my custom object values.
   I’m more than happy to share some snippets of code with you.
   I just wanted to share a different approach where you can get to the result with just one simple search and avoid multiple queries:)

   1. Hi Michaela,

      Thanks for dropping by and for sharing your simplification! Like many things, it often takes another pair of eyes to see the “obvious”. Thanks for that!

      By all means, please share a snippet of your updated revenue element search for the benefit of other readers.

      Thanks

      1. Hi.

         no problem. it’s a fairly siple solution. The same applies also to the amortization journals, which I’ve seen a similar article on here, that describes the process through multiple complex queries and searches, where one search is needed again.

         Below is a snippet of my code, where I run the search and set the memo fields on the rev rec journals from the source transaction. Works beautifly for any other source fields line and header alike.

         var formula = “formulatext: case when {revenueplan.recordnumber} in (‘” + revenuePlans.join(“‘,'”) + “‘) then ‘yes’ else ‘no’ end”;

         var revenueelementSearchObj = search.create({
         type: “revenueelement”,
         filters: [
         [“revenueplan.revenueplantype”, “anyof”, “ACTUAL”],
         “AND”,
         [formula,”is”,”yes”]
         ],
         columns: [
         search.createColumn({
         name: “recordnumber”,
         join: “revenuePlan”,
         label: “Number”
         }),
         search.createColumn({
         name: “memo”,
         join: “sourceTransaction”,
         label: “Memo”
         }),

         ]
         });

         var fieldsObject = {};

         revenueelementSearchObj.run().each(function(result) {
         var revenueplan = result.getValue({
         name: “recordnumber”,
         join: “revenuePlan”
         });

         var memoNew = result.getValue({
         name: “memo”,
         join: “sourceTransaction”
         });

         fieldsObject[revenueplan] = {
         ‘memo’: memoNew,
         };

         return true;
         });

         var numLines = newTransactionRecord.getLineCount({
         sublistId: ‘line’
         });

         for (var i = 0; i < numLines; i++) {
         let revenuePlan = newTransactionRecord.getSublistValue({
         sublistId: 'line',
         fieldId: 'sourcerevenueplan',
         line: i
         });
         if (fieldsObject.hasOwnProperty(revenuePlan)) {
         newTransactionRecord.setSublistValue({
         sublistId: 'line',
         fieldId: 'memo',
         value: fieldsObject[revenuePlan].memo,
         line: i
         });

         }
         }

         1. Thank you so much for sharing this optimization! When I have some time, I plan to revise the article to incorporate it.

            I’m not sure you’re quite right about the amortization scenario. Someone else has pointed out an improvement that can combine two of the queries into one. But my understanding is still that we can’t get the desired output with just N/search. Did you actually try your simplified approach? I’m curious to learn more. Thanks

            1. Hi,
               yes, I actually do have working solution with just N/search deployed to numerous of my customer’s accounts.

            2. Excellent! Could you please share you solution for the benefit of others? You can post here or email to stories[at]netsuite-insights.com

               Thanks!

         2. Hi Michaela,

            I looked at this again and I don’t see how you can get to line-level information of the source transaction which is what I needed. You can easily get header information as you have illustrated but that is not the challenge. Please advise.

            Thanks,
            Chidi