Jira code: HTL-6
To show the list of sales orders as a Key Performance Indicator (KPI) for each stage of – DIFOTIS. The KPI contains the details of total sales orders and indicators against each one that has been left out for the next stage. They also require to produce a Month to Date trend graph on their employee’s dashboard. A NetSuite trend graph is the best alternative to a report, because it illustrates the differences between KPI data and time.
User event script
/**
* @NApiVersion 2.x
* @NScriptType UserEventScript
* @NModuleScope SameAccount
* @ScriptTitle HTL 7 UE Set Time Order received
* @ScriptID customscript_jj_htl7_ue_set_time_order
* @ScriptDeploymentID customdeploy_jj_htl7_ue_set_time_order
* @AppliedTo Sales Order
* @Description To set Time order Received & Difotis , DIFOTIS ONTIME / INSPEC
* criteria for Time order Received:
* 1.If SO is not connected to a case or is connected to a case that the “Assigned To” field doesn’t equal “Orders”, grab current Date/Time and update the Sales Order custom field TIME ORDER RECEIVED Field ID: custbody26. Else
* 2.If SO is connected to a case and the “Assigned To” = Orders:
* a.if SO is changing from Pending Approval to Pending Fulfilment grab current Date/Time and update the Sales Order custom field TIME ORDER RECEIVED Field ID: custbody26. Else
* B. if SO has NOT changed from Pending Approval to Pending Fulfilment – go to Case System notes and grab the last Date/Time where “Type” = “Set” and update the Sales Order custom field TIME ORDER RECEIVED Field ID : custbody26.
* Criteria for Difotis
* 1. if Time Order Received < 1pm then difotis = Time order received
* 2. else if Time order received >1pm then difotis = next business day
* Criteria for DIFOTIS ONTIME / INSPEC
* 1.‘DIFOTIS On Time/In Spec’ field will be defaulted to ‘Out of spec’
* 2.If Ship Complete true ‘DIFOTIS On Time/In Spec’ field will change to ‘Not included’.
* 3.If a SO only has Items starting with “8” then ‘DIFOTIS On Time/In Spec’ field will change to ‘Not included’.
* 4.
*
* @CreatedBy AJ JJ 13/08/2018
* @RevisedBy AJ JJ 30/08/2018 set DIFOTIS ONTIME / INSPEC
*/
define(['N/file', 'N/url', 'N/search', 'N/runtime', 'N/record', 'N/https', 'N/ui/serverWidget', 'N/format', 'SuiteScripts/Jobin & Jismi/moment', 'N/config'],
function(file, url, search, runtime, record, https, serverWidget, format, moment, config) {
var oldStatus;
/***************************************************************************
* Function to get next month N date
*
* created by aj 28/7/2018
****************************************************************************/
function checkDayAndMonth(dates, months, years) {
var date = dates;
var month = months;
var year = years;
if ((date > 31) && ((month == 1) || (month == 3) || (month == 5) || (month == 7) || (month == 8) || (month == 10))) {
date = date - 31;
month = month + 1;
year = year
} else if ((date > 30) && ((month == 4) || (month == 6) || (month == 9) || (month == 11))) {
date = date - 30;
month = month + 1;
year = year;
} else if ((date > 28) && ((month == 2))) {
date = date - 28;
month = month + 1;
year = year;
}
if ((month == 12) && (date > 31)) {
date = date - 31;
month = 1;
year = year + 1;
}
var obj = {};
obj['date'] = date;
obj['month'] = month;
obj['year'] = year;
var dateMonth = JSON.stringify(obj);
return dateMonth;
}
/***************************************************************************
* Fn to set Difotis
***************************************************************************/
function setValueInDifotis(date) {
try {
var dates = date;
try {
var conf = config.load({
type: 'userpreferences'
});
var tz = conf.getValue({ fieldId: 'TIMEZONE' });
} catch (e) { log.debug("ZONEEEE Err", e);
log.error("ZONEEEE Err", e); }
var NZT_Time = format.format({
value: dates,
type: format.Type.DATETIME,
timezone: tz
});
time_now1 = NZT_Time.split(' ');
var nzt = moment(date).utcOffset(+13).format('DD/MM/YYYY');
var month = new Date(moment(date).utcOffset(+13).format('MM/DD/YYYY')).getMonth();
month = parseInt(month) + 1;
var date1 = new Date(moment(date).utcOffset(+13).format('MM/DD/YYYY')).getDate();
var year12 = new Date(moment(date).utcOffset(+13).format('MM/DD/YYYY')).getFullYear();
var day = new Date(moment(date).utcOffset(+13).format('MM/DD/YYYY')).getDay();
var time_now = moment(date).utcOffset(+13).format('HH');
var time_now_without_zone = moment(date).utcOffset(+13).format('HH:MM:SS');
if (time_now >= 13) {
if (day == 5) {
date1 = date1 + 3;
} else if (day == 6) {
date1 = date1 + 2;
} else {
date1 = date1 + 1;
}
} else if (day == 6) {
date1 = date1 + 2;
} else if (day == 0) {
date1 = date1 + 1;
}
var dateMonth = checkDayAndMonth(date1, month, year12);
var datesNmonths = JSON.parse(dateMonth);
var date_dif = datesNmonths['date'];
var month_dif = datesNmonths['month'];
var yaer_dif = datesNmonths['year'];
// to set the value of DIFOTIS date
var D_date = date_dif + "/" + month_dif + "/" + yaer_dif;
var difotis = format.parse({
value: D_date,
type: format.Type.DATE
});
return difotis;
} catch (e) {
log.debug("err@Setting of DIFOTIS", e.message);
log.error("err@Setting of DIFOTIS", e.message);
}
}
/***************************************************************************
* fn to set time order & Difotis
***************************************************************************/
function setTimeOrderAndDifotis(recordId, newStatus, oldStatus, type, difotisTrigger) {
try {
if ((difotisTrigger == true) || type == "approve" || type == "create") {
var soObj = record.load({
type: "salesorder",
id: recordId
});
var date = new Date();
var dateTime = format.format({
value: date,
type: format.Type.DATETIME,
timezone: format.Timezone.PACIFIC_AUCKLAND
});
soObj.setText({
fieldId: 'custbody26',
text: dateTime,
ignoreFieldChange: true
});
var difotis = setValueInDifotis(date);
soObj.setValue({
fieldId: 'custbody34',
value: difotis,
ignoreFieldChange: true
});
/*** to get the value ship complete **/
var isShipComplete = soObj.getValue({
fieldId: 'shipcomplete'
});
if (isShipComplete == true) {
soObj.setText({
fieldId: 'custbody_jj_difotis_ontime_inspec',
text: 'Not included',
ignoreFieldChange: true
});
soObj.setValue({
fieldId: 'custbody34',
value: '',
ignoreFieldChange: true
});
}
/*** to get the value ship complete End **/
/*** to get if the all items are 8 coded item **/
var lineNum = soObj.getLineCount({
sublistId: 'item'
});
var isBackorderd = 0;
if (lineNum > -1) {
for (var i = 0; i < lineNum; i++) {
var itemName = soObj.getSublistText({
sublistId: 'item',
fieldId: 'item',
line: i
});
log.debug("itemName", itemName);
if ((itemName.indexOf("8")) == 0) {
log.debug("starts with 8", "true");
isBackorderd = parseInt(isBackorderd) + 1;
}
}
if (parseInt(lineNum) == isBackorderd) {
soObj.setText({
fieldId: 'custbody_jj_difotis_ontime_inspec',
text: 'Not included',
ignoreFieldChange: true
});
}
}
soObj.setValue({
fieldId: 'custbody_jj_htl_19_difotistrigger',
value: false
});
soObj.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
}
} catch (e) {
log.error("Err @ SetTime order", e.message);
}
}
/********************************************************
* Fn to create search for SO is related to case or not
*********************************************************/
function searchCase(filterArray) {
try {
var salesorderSearchObj = search.create({
type: "salesorder",
filters: filterArray,
columns: [
search.createColumn({ name: "internalid", label: "Internal ID" })
]
});
var searchResultCount = salesorderSearchObj.runPaged().count;
return searchResultCount;
} catch (e) {
log.error("Err@ search creation ", e.message);
}
}
function fetchLines(deliveryObject, fields) {
var numLines = deliveryObject.getLineCount({
sublistId: 'item'
});
var lineObj = {};
for (var i = 0; i < numLines; i++) {
lineObj[i] = {};
for (var k = 0; k < fields.length; k++) {
lineObj[i][fields[k]] = deliveryObject.getSublistValue({
fieldId: fields[k],
sublistId: 'item',
line: i
});
}
}
return JSON.stringify(lineObj);
}
function beforeSubmit(scriptContext) {
try {
var currRecord = scriptContext.newRecord;
var oldRecord = scriptContext.oldRecord;
var oldStatus = oldRecord.getValue({
fieldId: 'orderstatus'
});
var newStatus = currRecord.getValue({
fieldId: 'orderstatus'
});
var oldShipcomplete = oldRecord.getValue({
fieldId: 'shipcomplete'
});
var newShipcomplete = currRecord.getValue({
fieldId: 'shipcomplete'
});
var difotisTrigger = currRecord.getValue({
fieldId: 'custbody_jj_htl_19_difotistrigger'
});
var fields = ["item", "amount", "quantity"]
var oldItemobj = fetchLines(oldRecord, fields);
var newItemobj = fetchLines(currRecord, fields);
if ((newStatus != "A" && newStatus != oldStatus) || scriptContext.type == "approve" || (newShipcomplete != oldShipcomplete) || (newItemobj != oldItemobj)) {
currRecord.setValue({
fieldId: 'custbody_jj_htl_19_difotistrigger',
value: true
});
}
} catch (e) {
log.error("Err@before submit", e.message);
}
}
/***************************************************************************
* Function definition to be triggered before record is loaded.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.newRecord - New record
* @param {Record} scriptContext.oldRecord - Old record
* @param {string} scriptContext.type - Trigger type
* @Since 2015.2
***************************************************************************/
function afterSubmit(scriptContext) {
try {
if (scriptContext.type == "create" || scriptContext.type == "approve" || scriptContext.type == "edit") {
var currRecord = scriptContext.newRecord;
var oldRecord = scriptContext.oldRecord;
// to get the case not connected to SO
var recordId = scriptContext.newRecord.id;
try {
var TORV = oldRecord.getValue({
fieldId: 'custbody26'
});
var TORTEXT = oldRecord.getText({
fieldId: 'custbody26'
});
var oldStatus = oldRecord.getValue({
fieldId: 'orderstatus'
});
} catch (e) {
var oldStatus = null;
}
var newStatus = currRecord.getValue({
fieldId: 'orderstatus'
});
var difotisTrigger = currRecord.getValue({
fieldId: 'custbody_jj_htl_19_difotistrigger'
});
if ((difotisTrigger == true) || scriptContext.type == "approve") {
//search for SO is connected to SO
var filterArray = [];
filterArray.push(["internalid", "anyof", recordId],
"AND", ["custbody36", "noneof", "@NONE@"],
"AND", ["mainline", "is", "T"]);
var searchResultCount = searchCase(filterArray);
// if case is connected to SO
if (searchResultCount > 0) {
var filterArrayNew = [];
// fn to search again
filterArrayNew.push(["internalid", "anyof", recordId],
"AND", ["custbody36", "noneof", "@NONE@"],
"AND", ["mainline", "is", "T"], "AND", ["case.assigned", "anyof", "29091"]);
var isAssignedOrder = searchCase(filterArrayNew);
// if case assigned = "Orders"
if (isAssignedOrder > 0) {
if (scriptContext.type == "approve") {
var set_values = setTimeOrderAndDifotis(recordId, newStatus, oldStatus, scriptContext.type, difotisTrigger);
}
// if SO has NOT changed from Pending Approval to Pending Fulfilment
else {
// create a search for getting the latest system notes field
/*****
** To Check the system note field
*/
var soObj = record.load({
type: "salesorder",
id: recordId
});
var caseNumber = currRecord.getValue({
fieldId: 'custbody36'
});
if (caseNumber == "") {
var searchResultCount1 = 0;
} else {
var searchingForsystemNotes = search.create({
type: search.Type.SUPPORT_CASE,
filters: [
["internalidnumber", "equalto", caseNumber]
],
columns: [
search.createColumn({
name: "formuladate",
formula: "case when {systemnotes.type} = 'Set' then TO_DATE({systemnotes.date}) end",
sort: search.Sort.DESC,
label: "Formula (Date)"
})
]
});
var searchResultCount1 = searchingForsystemNotes.runPaged().count;
}
// to get the latest value
if (searchResultCount1 > 0) {
var resultSet = searchingForsystemNotes.run();
var searchResults = resultSet.getRange({
start: 0,
end: 1
});
var difotis_date = searchResults[0].getValue({
name: 'formuladate'
});
// to set in the difotis field
/*Updated by Rambo 04-09-18*/
soObj.setValue({
fieldId: 'custbody26',
value: difotis_date,
ignoreFieldChange: true
});
soObj.setValue({
fieldId: 'custbody34',
value: setValueInDifotis(difotis_date),
ignoreFieldChange: true
});
} else {
var set_values = setTimeOrderAndDifotis(recordId, newStatus, oldStatus, scriptContext.type, difotisTrigger);
}
if (scriptContext.type == "approve") {
soObj.setText({
fieldId: 'custbody_jj_difotis_ontime_inspec',
text: 'Out of spec',
ignoreFieldChange: true
});
}
soObj.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
/***************************************************************************
** To Check the system note field
Ends here
***************************************************************************/
}
}
// if case assigned != orders
else {
//fn call to set time order & DIFOTIS
var set_values = setTimeOrderAndDifotis(recordId, newStatus, oldStatus, scriptContext.type, difotisTrigger);
}
}
// else if SO is not connected to CASE
else {
var set_values = setTimeOrderAndDifotis(recordId, newStatus, oldStatus, scriptContext.type, difotisTrigger);
}
try {
var checkOntimespec = currRecord.getText({ fieldId: "custbody_jj_difotis_ontime_inspec" });
} catch (e) {
var checkOntimespec = currRecord.getValue({ fieldId: "custbody_jj_difotis_ontime_inspec" });
}
if (checkOntimespec == "Not included" || checkOntimespec == "") {
var soObj = record.load({
type: "salesorder",
id: recordId
});
var check = checkoutSpec(soObj);
soObj.setText({
fieldId: "custbody_jj_difotis_ontime_inspec",
text: check
});
soObj.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
} else if (checkOntimespec == "Out of spec" && newStatus != "A" && scriptContext.type == "create") {
var soObj = record.load({
type: "salesorder",
id: recordId
});
var check = checkoutSpec(soObj);
soObj.setText({
fieldId: "custbody_jj_difotis_ontime_inspec",
text: check
});
soObj.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
}
} else if (newStatus == oldStatus && scriptContext.type == "edit") {}
// AJ MODIFICATION 14/11/18 For err correction
else if(scriptContext.type == "edit" && difotisTrigger != true)
{
}
else if(scriptContext.type == "create" && newStatus == "B")
{
var set_values = setTimeOrderAndDifotis(recordId, newStatus, oldStatus, scriptContext.type, difotisTrigger);
}
else if(scriptContext.type == "edit" && difotisTrigger == true)
{
if(newStatus == "E" && newStatus !=oldStatus)
{
var soObj = record.load({
type: "salesorder",
id: recordId
});
soObj.setText({
fieldId: 'custbody_jj_difotis_ontime_inspec',
text: 'Out of spec',
ignoreFieldChange: true
});
soObj.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
}
}
// AJ MODIFICATION 14/11/18 For err correction ends
else {
var soObj = record.load({
type: "salesorder",
id: recordId
});
soObj.setText({
fieldId: "custbody_jj_difotis_ontime_inspec",
text: "Not included"
});
soObj.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
}
}
} catch (e) {
log.error("Err@ after submit", e.message);
}
}
function checkoutSpec(soObj) {
var nonInv = 0;
/*** to get if the all items are 8 coded item **/
var lineNum = soObj.getLineCount({
sublistId: 'item'
});
var isBackorderd = 0;
if (lineNum > -1) {
for (var i = 0; i < lineNum; i++) {
var itemName = soObj.getSublistText({
sublistId: 'item',
fieldId: 'item',
line: i
});
if ((itemName.indexOf("8")) == 0) {
isBackorderd = parseInt(isBackorderd) + 1;
}
//AJ Sep 26 2018
var itemType = soObj.getSublistValue({
sublistId: 'item',
fieldId: 'itemtype',
line: i
});
if (itemType != 'InvtPart' && itemType != 'Assembly') {
nonInv = parseInt(nonInv) + 1;
}
//else if()
}
if (parseInt(lineNum) == isBackorderd || parseInt(lineNum) == nonInv || parseInt(nonInv) + parseInt(isBackorderd) == lineNum) {
return "Not included"
} else {
return "Out of spec"
}
}
}
return {
beforeSubmit: beforeSubmit,
afterSubmit: afterSubmit
};
});
Anju,
please post an image of the output