Scenario:
Need to send automatic emails when the sales order doesn’t have backordered line items and close the sales orders when they are not picked/packed/shipped even after 30 days. Send a warning email on the 15th and 25th day to notifying the customer about the order will be automatically closed. Create a button to provide the option to reopen the SO if the user would like to open the SO again.
Solution:
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
/*********************************************************************************************
* CDUS-2438 : Send Email When An Order Doesn't Have BO Item
* *******************************************************************************************
*
* Date: 02-02-2023
*
* Author: Jobin & Jismi IT Services LLP
* Description : Script for sending notification emails to customer when there is no BO item
* in sales order
*
********************************************************************************************/
define(['N/email', 'N/error', 'N/file', 'N/record', 'N/search'],
/**
* @param{email} email
* @param{error} error
* @param{file} file
* @param{record} record
* @param{search} search
*/
(email, error, file, record, search) => {
let main = {
getInputData: function (inputContext) {
let dataArray = []
let backOrdObj = main.searchSalesOrderWithoutBO("F")
dataArray = dataArray.concat(backOrdObj)
let dueBackOrdObj = main.searchSalesOrderWithoutBO("T")
dataArray = dataArray.concat(dueBackOrdObj)
return dataArray
},
reduce: function (reduceContext) {
let recordPicked = false
let dataObj = JSON.parse(reduceContext.values)
log.debug("data obj in reduce", dataObj)
//send email to all back ordered sales orders
if (!main.checkForParameter(dataObj.csr))
return;
let csrEmail = search.lookupFields({
type: search.Type.EMPLOYEE,
id: dataObj.csr,
columns: ['email']
}).email;
//check if the order is picked/packed/shipped after the mail is sent
let mailSentDate = new Date(dataObj.emailSentDate)
let IFdate = new Date(dataObj.IFdate)
if (IFdate && (IFdate > mailSentDate))
recordPicked = true;
if (dataObj.isMailSent == "NO"){
main.emailBOorders(dataObj, csrEmail)
}
else if ((dataObj.isMailSent == "YES") && (dataObj.dueDate == 15) && (recordPicked == false)){
main.emailBOorders_due15(dataObj, csrEmail)
}
else if ((dataObj.isMailSent == "YES") && (dataObj.dueDate == 25) && (recordPicked == false)){
main.emailBOorders_due25(dataObj, csrEmail)
}
else if ((dataObj.isMailSent == "YES") && (dataObj.dueDate == 30) && (dataObj.ifCount == 0) && (recordPicked == false)){
main.closeBOorders(dataObj, csrEmail)
}
},
summarize: function (summarizeContext) {
},
searchSalesOrderWithoutBO: function (yesno) {
let salesorderSearchObj = search.create({
type: "salesorder",
filters:
[
["type", "anyof", "SalesOrd"],
"AND",
["cogs", "is", "F"],
"AND",
["taxline", "is", "F"],
"AND",
["shipping", "is", "F"],
"AND",
["mainline", "is", "F"],
"AND",
["status","noneof","SalesOrd:C","SalesOrd:F","SalesOrd:G","SalesOrd:H","SalesOrd:A"],
"AND",
["custbody_jj_mail_sent", "is", yesno],
"AND",
["item.type", "noneof", "Description", "Discount", "Markup", "NonInvtPart", "OthCharge", "Payment", "Service", "Subtotal", "Group"],
"AND",
["quantity", "isnotempty", ""],
"AND",
["datecreated","after", "1/15/2023 11:59 pm"],
"AND",
["max(formulanumeric: {quantity}-nvl({quantitycommitted},0)-nvl({quantityshiprecv},0))", "equalto", "0"]
],
columns:
[
search.createColumn({
name: "internalid",
summary: "GROUP",
label: "Internal ID"
}),
search.createColumn({
name: "tranid",
summary: "GROUP",
label: "Document Number"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "{quantity}-nvl({quantitycommitted},0)-nvl({quantityshiprecv},0)",
label: "Formula (Numeric)"
}),
search.createColumn({
name: "location",
summary: "MAX",
label: "Location"
}),
search.createColumn({
name: "formulatext",
summary: "MAX",
formula: "NVL(NVL({customer.email}, {customer.custentity_jj_operation_email}), {customer.custentity_jj_accounting_email})",
label: "Formula (Text)"
}),
search.createColumn({
name: "internalid",
join: "location",
summary: "MAX",
label: "Internal ID"
}),
search.createColumn({
name: "otherrefnum",
summary: "MAX",
label: "PO/Check Number"
}),
search.createColumn({
name: "formuladatetime",
summary: "MAX",
formula: "CASE WHEN ({systemnotes.field}='BO mail sent' AND {systemnotes.newvalue}='T') THEN {systemnotes.date} END",
label: "Email sent date"
}),
search.createColumn({
name: "formuladatetime",
summary: "MAX",
formula: "CASE WHEN ({applyingtransaction.type}= 'Item Fulfillment') THEN {applyingtransaction.datecreated} END",
label: "IF created date"
}),
search.createColumn({
name: "formulanumeric",
summary: "MAX",
formula: "CASE WHEN ({systemnotes.field}='BO mail sent' AND {systemnotes.newvalue}='T') THEN CEIL({today}-{systemnotes.date}) END",
label: "Formula (Numeric)"
}),
search.createColumn({
name: "custentity_jj_csr",
join: "customer",
summary: "GROUP",
label: "CSR(customer service)"
}),
search.createColumn({
name: "formulanumeric",
summary: "COUNT",
formula: "CASE WHEN ({applyingtransaction.type}= 'Item Fulfillment') THEN {applyingtransaction.internalid} END",
label: "Formula (Numeric)"
})
]
});
let searchResultCount = salesorderSearchObj.runPaged().count;
log.debug("salesorderSearchObj result count", searchResultCount);
if (searchResultCount > 0) {
let returnArray = []
salesorderSearchObj.run().each(function (result) {
let returnObj = {}
returnObj["ordInternalId"] = result.getValue({
name: "internalid",
summary: "GROUP",
label: "Internal ID"
});
returnObj["ordDocNumber"] = result.getValue({
name: "tranid",
summary: "GROUP",
label: "Document Number"
});
returnObj["customerEmail"] = result.getValue({
name: "formulatext",
summary: "MAX",
formula: "NVL(NVL({customer.email}, {customer.custentity_jj_operation_email}), {customer.custentity_jj_accounting_email})",
label: "Formula (Text)"
});
returnObj["poNumber"] = result.getValue({
name: "otherrefnum",
summary: "MAX",
label: "PO/Check Number"
});
returnObj["emailSentDate"] = result.getValue(result.columns[7]);
returnObj["IFdate"] = result.getValue(result.columns[8]);
returnObj["isMailSent"] = (yesno == "F")?"NO":"YES";
returnObj["dueDate"] = result.getValue({
name: "formulanumeric",
summary: "MAX",
formula: "CASE WHEN ({systemnotes.field}='BO mail sent' AND {systemnotes.newvalue}='T') THEN CEIL({today}-{systemnotes.date}) END",
label: "Formula (Numeric)"
}) || 0;
returnObj["csr"] = result.getValue({
name: "custentity_jj_csr",
join: "customer",
summary: "GROUP",
label: "CSR(customer service)"
})
returnObj["ifCount"] = result.getValue({
name: "formulanumeric",
summary: "COUNT",
formula: "CASE WHEN ({applyingtransaction.type}= 'Item Fulfillment') THEN {applyingtransaction.internalid} END",
label: "Formula (Numeric)"
})
if (returnObj["dueDate"] == 0 || 15 || 25 || 30)
returnArray.push(returnObj)
return true;
});
return returnArray;
}else
return []
},
emailBOorders: function (data, csrEmail){
if (main.checkForParameter(csrEmail) && main.checkForParameter(data.customerEmail)) {
let po_so = data.poNumber ? data.poNumber + "/" + data.ordDocNumber : data.ordDocNumber
let subject = "Your order " + po_so + " MUST be picked up or ship within 30 days!"
let body = "Your " + po_so + " Must be picked up within 30 days or will be automatically cancelled.<br>" +
"please let us know when you would like to pick up your order immediately to avoid cancellation. For a better and faster experience," +
" please notify us 72 hours before your arrival for pickup so that we can prepare this order in advance <br><br>" + "Thank You!<br>" + csrEmail
let author = data.csr
let recipient = data.customerEmail
//send BO email
email.send({
author: author,
recipients: recipient,
subject: subject,
body: body,
relatedRecords: {
transactionId: data.ordInternalId
}
});
//check the email sent check box in SO
record.submitFields({
type: record.Type.SALES_ORDER,
id: data.ordInternalId,
values: {
custbody_jj_mail_sent: true
}
});
}
},
emailBOorders_due15: function (data, csrEmail) {
if (main.checkForParameter(csrEmail) && main.checkForParameter(data.customerEmail)) {
let po_so = data.poNumber ? data.poNumber + "/" + data.ordDocNumber : data.ordDocNumber
let subject = "Your order " + po_so + " MUST be picked up or ship immediately to avoid cancellation!"
let body = "Your order " + po_so + " Must be picked up or Must ship within the next 15 days or will be " +
"automatically cancelled, please let us know when you would like to pick up your order " +
"immediately to avoid cancellation. For a better and faster experience, please notify us 72 " +
"hours before your arrival for pickup so we that can prepare this order in advance – " +
"<b><u>Inventory allocated to your order will be lost once the order has been cancelled</u></b>" + "<br><br>Thank You!<br>" + csrEmail;
let author = data.csr
let recipient = data.customerEmail
//send BO email
email.send({
author: author,
recipients: recipient,
subject: subject,
body: body,
relatedRecords: {
transactionId: data.ordInternalId
}
});
}
},
emailBOorders_due25: function (data, csrEmail) {
if (main.checkForParameter(csrEmail) && main.checkForParameter(data.customerEmail)) {
let po_so = data.poNumber ? data.poNumber + "/" + data.ordDocNumber : data.ordDocNumber
let subject = "This is your last notice before your order " + po_so + " gets cancelled!"
let body = "Your " + po_so + " Must be picked up or Must ship within the next 5 days <b><u>or will be automatically cancelled</u></b>"+
", please let us know when you would like to pick up your order " +
"immediately to avoid cancellation. For a better and faster experience, please notify us 72 " +
"hours before your arrival for pickup so we that can prepare this order in advance - " + "<b><u>Inventory allocated to your order will be lost once the order has been cancelled</u></b>" + "<br><br>Thank You!<br>" + csrEmail;
let author = data.csr
let recipient = data.customerEmail
//send BO email
email.send({
author: author,
recipients: recipient,
subject: subject,
body: body,
relatedRecords: {
transactionId: data.ordInternalId
}
});
}
},
closeBOorders: function (data, csrEmail) {
if (main.checkForParameter(csrEmail) && main.checkForParameter(data.customerEmail)) {
log.debug("***test2***")
let po_so = data.poNumber ? data.poNumber + "/" + data.ordDocNumber : data.ordDocNumber
let subject = "Order cancellation receipt – " + po_so
let body = "Please note that our system automatically cancelled your " + po_so +" because it was left open " +
"in our system over the allowed grace period of 30 days.<br>" +
"You may contact your CSR if ever you didn’t want this order to expire and they may be able to reopen your order.<br><br>" + "Thank You!<br>" + csrEmail;
let author = data.csr
let recipient = data.customerEmail
//send BO email
email.send({
author: author,
recipients: recipient,
subject: subject,
body: body,
relatedRecords: {
transactionId: data.ordInternalId
}
});
//close the order
let orderRec = record.load({
type: record.Type.SALES_ORDER,
id: data.ordInternalId,
isDynamic: true
})
let lineCount = orderRec.getLineCount({
sublistId: 'item'
})
for (let i=0; i<lineCount; i++){
let itemLine = orderRec.selectLine({
sublistId: 'item',
line: i
})
orderRec.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'isclosed',
value: true
})
orderRec.commitLine({
sublistId: 'item'
});
}
orderRec.setValue({fieldId: 'custbody_jj_closed_order', value:true})
orderRec.save({ignoreMandatoryFields: true, enableSourcing: false})
}
},
/**
* @description Check whether the given parameter argument has value on it or is it empty.
* ie, To check whether a value exists in parameter
* @author Manu Antony
* @param {*} parameter parameter which contains/references some values
* @param {*} parameterName name of the parameter, not mandatory
* @returns {Boolean} true if there exist a value else false
*/
checkForParameter: function (parameter, parameterName) {
if (parameter !== "" && parameter !== null && parameter !== undefined && parameter !== false && parameter !== "null" && parameter !== "undefined" && parameter !== " " && parameter !== 'false') {
return true;
} else {
if (parameterName)
log.debug('Empty Value found', 'Empty Value for parameter ' + parameterName);
return false;
}
}
}
for (let key in main) {
if (typeof main[key] === 'function') {
main[key] = trycatch(main[key], key);
}
}
function trycatch(myfunction, key) {
return function () {
try {
return myfunction.apply(this, arguments);
} catch (e) {
log.debug("e in " + key, e);
return [];
}
}
}
return main;
});