Requirement:
A transfer order needs to be created for items in location A having quantity ordered less than reorder point from location B to A if B has enough quantity and also another transfer order will be created for the items having quantity available in A greater than the preferred stock level from location A to B.
Solution
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
/************************************************************************************************
* Allied Power and Control
*
* APCN-472 : Transfer Order Creation for items having quantity available less than reorder point
*
* **********************************************************************************************
*
* Author: Jobin and Jismi IT Services
*
* Date Created : 06-May-2023
*
* Description : This script is a scheduled script to create transfer order from location BL to Main for the items
* that are having qty available in location main less then the re-order point.
*
* REVISION HISTORY
*
* @version 1.0 06-May-2023 : Created the initial build by JJ0152
*
*
***********************************************************************************************/
define(['N/email', 'N/file', 'N/http', 'N/record', 'N/runtime', 'N/search'],
/**
* @param{email} email
* @param{file} file
* @param{http} http
* @param{record} record
* @param{runtime} runtime
* @param{search} search
*/
(email, file, http, record, runtime, search) => {
"use strict";
/**
* The global object that contains all the necessary functions in this script.
*/
const DATASETS = {
/**
* Function to fetch the items that crossed the re-order point in Main location using saved search and
* also to get the quantity available in location BL.
* @param {*} location
* @param {*} itemId
* @returns
*/
fetchReorderPointCrossedItems: function (location, itemId) {
try {
let filter;
if (location == "Main") {
filter = [
["type", "anyof", "Assembly", "InvtPart"],
"AND",
["inventorylocation", "anyof", "1"],
"AND",
["internalid", "anyof", "134962"],
"AND",
["isinactive", "is", "F"],
"AND",
[[["formulanumeric: CASE WHEN NVL({locationquantityavailable}, 0) < {locationreorderpoint} THEN 1 ELSE 0 END", "equalto", "1"], "AND", ["locationreorderpoint", "greaterthan", "0"]], "OR", [["formulanumeric: CASE WHEN NVL({locationquantityavailable}, 0) > {locationpreferredstocklevel} THEN 1 ELSE 0 END", "equalto", "1"], "AND", ["locationpreferredstocklevel", "greaterthan", "0"]]]
]
}
else {
filter = [
["internalid", "anyof", itemId],
"AND",
["inventorylocation", "anyof", "7"]
]
}
let itemSearchObj = search.create({
type: "item",
filters: filter,
columns:
[
search.createColumn({ name: "internalid", label: "Internal ID" }),
search.createColumn({ name: "locationreorderpoint", label: "Location Reorder Point" }),
search.createColumn({ name: "locationquantityavailable", label: "Location Available" }) || 0,
search.createColumn({
name: "formulanumeric",
formula: "NVL({locationquantityavailable}, 0) - NVL({locationquantitybackordered}, 0)",
label: "qtyAvailable"
}),
search.createColumn({
name: "itemid",
sort: search.Sort.ASC,
label: "Name"
}),
search.createColumn({ name: "locationpreferredstocklevel", label: "Location Preferred Stock Level" })
]
});
let searchResultCount = itemSearchObj.runPaged().count;
if (location == "Main" && searchResultCount > 0) {
return { status: 'true', qty: itemSearchObj, reason: "" };
}
else if (location == "BL") {
let qtyAvailable;
if (searchResultCount > 0) {
itemSearchObj.run().each(function (result) {
qtyAvailable = result.getValue({ name: "locationquantityavailable", label: "Location Available" }) || 0;
return false;
});
}
else {
qtyAvailable = 0;
}
return { status: 'true', qty: qtyAvailable, reason: "" };
}
else {
return { status: 'false', qty: "", reason: "" };
}
} catch (e) {
log.error("Error @ fetchReorderPointCrossedItems", e.message);
return { status: 'false', qty: "", reason: e.message };
}
},
/**
* Function to create the transfer order from BL to Main for the items having quantity available in Main less than its re-order point also
* to create TO from Main to BL if qty available in Main is greater than preferred stock level.
* @param {*} itemDetailArray
* @returns
*/
createTransferOrder: function (itemDetailArray) {
let returnErrorArr = [];
let transferOrderId;
try {
let lists = (itemDetailArray[0].values.transOrderFrom == 'bl') ? {
fromLocation: 7,
toLocation: 1
} : {
fromLocation: 1,
toLocation: 7
};
lists.subsidiary = 1;
lists.incoTerm = 2;
lists.shipcarrier = 'FedEx/USPS/More';
lists.shipMethod = 1916;
let transferOrderRecord = record.create({
type: record.Type.TRANSFER_ORDER,
isDynamic: true
});
// Set the header fields
transferOrderRecord.setValue({
fieldId: 'subsidiary',
value: lists.subsidiary
});
transferOrderRecord.setValue({
fieldId: 'location',
value: lists.fromLocation
});
transferOrderRecord.setValue({
fieldId: 'transferlocation',
value: lists.toLocation
});
transferOrderRecord.setValue({
fieldId: 'incoterm',
value: lists.incoTerm
});
transferOrderRecord.setValue({
fieldId: 'memo',
value: 'JJ Created Transfer Order'
});
transferOrderRecord.setValue({
fieldId: 'custbody_jj_note_apcn_476',
value: "FOR RESTOCKING"
})
transferOrderRecord.setValue({ //The TO will be created in 'Pending Fulfillment' status.
fieldId: 'orderstatus',
value: "B"
})
transferOrderRecord.setText({
fieldId: 'shipcarrier',
value: lists.shipcarrier
});
transferOrderRecord.setText({
fieldId: 'shipmethod',
value: lists.shipMethod
});
// Set the line item fields
let quantity, qtyNeeded, qtyAvailableInBL, itemEntered = false, lineCount = 0;
for (let i = 0; i < itemDetailArray.length; i++) {
let quantity;
transferOrderRecord.selectNewLine({
sublistId: 'item'
});
transferOrderRecord.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'item',
value: itemDetailArray[i].id
});
if (itemDetailArray[i].values.transOrderFrom == 'bl') {
qtyNeeded = Number(itemDetailArray[i].values.locationreorderpoint) - Number(itemDetailArray[i].values.formulanumeric);
qtyAvailableInBL = itemDetailArray[i].values.quantityAvailableInBL;
if (qtyNeeded <= 0) {
continue;
}
quantity = (qtyAvailableInBL >= qtyNeeded) ? qtyNeeded : qtyAvailableInBL;
}
else {
quantity = itemDetailArray[i].values.quantityToBeMovedToBL;
}
transferOrderRecord.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'quantity',
value: quantity
});
transferOrderRecord.commitLine({
sublistId: 'item'
});
itemEntered = true //flag to identify atleast 1 item line is entered
itemDetailArray.splice(i, 1); //Removing the entered item from the array
lineCount++, i--;
if (lineCount == 500) { //since there are limitation in the line count that can be entered in TO, we are limitting it to 500 lines here to avoid errors
break;
}
}
// Save the transfer order record
if (itemEntered) {
transferOrderId = transferOrderRecord.save({
enableSourcing: false,
ignoreMandatoryFields: false
});
log.debug("created TO", transferOrderId)
if (itemDetailArray.length > 0) {
this.createTransferOrder(itemDetailArray)
}
}
return { status: "true", reason: transferOrderId }
} catch (e) {
log.error("Error @ createTransferOrder", e.message);
return { status: "false", reason: e.message }
}
},
/**
* Function to create a CSV file listing the error details
* @param {*} errorArray
* @returns
*/
createFileForErrors: function (errorArray) {
try {
let titleArray = ["Item Name", "Item Internal ID", "Error Reason"];
let csvFileData = titleArray.toString() + '\r\n';
for (let i = 0; i < errorArray.length; i++) {
//append to csv file
csvFileData += JSON.stringify(errorArray[i].item) + ',' + errorArray[i].id + ',' + errorArray[i].reason + ',';
csvFileData += '\r\n';
}
//filename
let today = new Date(), dd = String(today.getDate()).padStart(2, '0'), mm = String(today.getMonth() + 1).padStart(2, '0'), yyyy = today.getFullYear();
let todayDate = mm + '-' + dd + '-' + yyyy;
let fileFolder = '/SuiteScripts/Jobin and Jismi IT Services LLP/APCN-473/Error in scheduled transOrd creation';
let fileObj = file.create({
name: 'transorder_error_report_' + todayDate + '.csv',
fileType: file.Type.CSV,
folder: fileFolder, //970982,
contents: csvFileData,
encoding: file.Encoding.UTF8,
});
return fileObj;
} catch (e) {
log.error("Error @ createFileForErrors", e.message)
return false;
}
},
/**
* Function to send error email to client
* @param {*} errorFile
*/
sendEmails: function (type, dataFile) {
try {
if (type == "error") {
//send error email
email.send({
author: -5,
recipients: -5,
subject: 'Error while creating transfer order for re-order point crossed items',
body: 'Dear Jacob,<br/><br/> Please find the attached file that lists the errors occured while creating tranfer order for the items having available quantity less than the re-order point. <br><br>Thank You. ',
attachments: [dataFile]
});
}
else {
//create the table listing items having zero available in BL
let emailBody = '<div>Dear Jacob,<br/><br/> Please find attached list of items in which the quantity available in location BL is zero.<br/> <br /></div> <table style=" border-collapse: collapse;"><tr style="height:50px;"> <th style="border:solid black; background-color:#45c0e5; font-weight: bold; width:170px; text-align:center;">ITEM NAME</th><th style="border:solid black; background-color:#45c0e5; font-weight: bold; width:170px; text-align:center;">ITEM INTERNAL ID</th></tr>';
for (let i = 0; i < dataFile.length; i++) {
emailBody += '<tr style="height:40px;"><td style="border:solid black; width:170px; text-align:center;">' + dataFile[i].itemName + '</td><td style="border:solid black; width:170px; text-align:center;">' + dataFile[i].itemId + '</td></tr>';
}
emailBody += '</table><br/><br/>';
emailBody += '<div>Thank You.';
//send email
email.send({
author: -5,
recipients: -5,
subject: 'Some items are having zero quantity in location BL',
body: emailBody
});
}
} catch (e) {
log.error("Error @ sendErrorEmail", e.message)
}
},
/**
* Function to calculate the total quantity of an item in unreceived transfer orders
* @param {*} itemArray
* @param {*} itemDetailArray
* @returns
*/
getQuantityInTransOrder: function (itemArray, itemDetailArray) {
try {
let transOrdQty = {}
let transferorderSearchObj = search.create({
type: "transferorder",
filters:
[
["type", "anyof", "TrnfrOrd"],
"AND",
["mainline", "is", "F"],
"AND",
["taxline", "is", "F"],
"AND",
["shipping", "is", "F"],
"AND",
["cogs", "is", "F"],
"AND",
["closed", "is", "F"],
"AND",
["status", "noneof", "TrnfrOrd:H", "TrnfrOrd:A", "TrnfrOrd:G", "TrnfrOrd:C"],
// "AND",
// ["location", "anyof", "7"],
"AND",
["tolocation.internalid", "anyof", "1"],
"AND",
["item.internalid", "anyof", itemArray]
],
columns:
[
search.createColumn({
name: "quantitycommitted",
summary: "SUM",
label: "Quantity Committed"
}),
search.createColumn({
name: "internalid",
join: "item",
summary: "GROUP",
label: "Internal ID"
})
]
});
let searchResultCount = transferorderSearchObj.runPaged().count;
transferorderSearchObj.run().each(function (result) {
transOrdQty[result.getValue({
name: "internalid",
join: "item",
summary: "GROUP",
label: "Internal ID"
})] = result.getValue({
name: "quantitycommitted",
summary: "SUM",
label: "Quantity Committed"
})
return true;
});
//adding the qty of unreceived TOs to the quantity available in array 'itemDetailArray'
for (let i = 0; i < itemDetailArray.length; i++) {
itemDetailArray[i].values.locationquantityavailable = Number(itemDetailArray[i].values.locationquantityavailable) + Number((transOrdQty[itemDetailArray[i].id] ? transOrdQty[itemDetailArray[i].id] : 0))
}
return itemDetailArray;
} catch (e) {
log.error("Error @ getQuantityInTransOrder", e.message)
return [];
}
},
/**
* @description the function to check whether a value exists in parameter
* @param parameter -passing parameter
* @param parameterName - passing parameter name
* @returns{Boolean}
*/
checkForParameter: function (parameter) {
try {
if (parameter !== "" && parameter !== null && parameter !== undefined && parameter !== false && parameter !== "null" && parameter !== "undefined" && parameter !== " " && parameter !== 'false') {
return true;
}
else {
return false;
}
} catch (e) {
log.error("Error @ checkForParameter", e.message)
return false;
}
}
}
/**
* The global object that contains the entry points of MR script
*/
const MAIN = {
/**
* Defines the function that is executed at the beginning of the map/reduce process and generates the input data.
* @param {Object} inputContext
* @param {boolean} inputContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {Object} inputContext.ObjectRef - Object that references the input data
* @typedef {Object} ObjectRef
* @property {string|number} ObjectRef.id - Internal ID of the record instance that contains the input data
* @property {string} ObjectRef.type - Type of the record instance that contains the input data
* @returns {Array|Object|Search|ObjectRef|File|Query} The input data to use inofithe map/reduce process
* @since 2015.2
*/
getInputData: function (inputContext) {
try {
let itemsCrossedReorder = DATASETS.fetchReorderPointCrossedItems("Main");
log.debug("items that crossed their reorder point", itemsCrossedReorder)
if (DATASETS.checkForParameter(itemsCrossedReorder.status)) {
return itemsCrossedReorder.qty
}
else {
return [];
}
} catch (e) {
log.error("Error @ getInputData", e.message);
return [];
}
},
/**
* Defines the function that is executed when the reduce entry point is triggered. This entry point is triggered
* automatically when the associated map stage is complete. This function is applied to each group in the provided context.
* @param {Object} reduceContext - Data collection containing the groups to process in the reduce stage. This parameter is
* provided automatically based on the results of the map stage.
* @param {Iterator} reduceContext.errors - Serialized errors that were thrown during previous attempts to execute the
* reduce function on the current group
* @param {number} reduceContext.executionNo - Number of times the reduce function has been executed on the current group
* @param {boolean} reduceContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {string} reduceContext.key - Key to be processed during the reduce stage
* @param {List<String>} reduceContext.values - All values associated with a unique key that was passed to the reduce stage
* for processing
* @since 2015.2
*/
reduce: function (reduceContext) {
let itemDetail = JSON.parse(reduceContext.values)
try {
//if qty available is greater than preferred stock level then we need to create the TO for the excess qty of item from Main to BL
//if qty available is less than reorder point, then we need to create TO from BL to main to make the qty available = reorder point
if (Number(itemDetail.values.locationquantityavailable) > Number(itemDetail.values.locationpreferredstocklevel)) {
itemDetail.values.quantityToBeMovedToBL = Number(itemDetail.values.locationquantityavailable) - Number(itemDetail.values.locationpreferredstocklevel)
itemDetail.values.transOrderFrom = 'main';
reduceContext.write({
key: "itemDetail",
value: JSON.stringify(itemDetail)
})
}
else {
//get the items quantity available in location BL
let qtyInBL = DATASETS.fetchReorderPointCrossedItems("BL", itemDetail.id);
if (DATASETS.checkForParameter(qtyInBL.status)) { //if the function returns a success response, then write the item details to summarize to create the TO
itemDetail.values.quantityAvailableInBL = qtyInBL.qty
itemDetail.values.transOrderFrom = 'bl'; //prod
reduceContext.write({
key: "itemDetail",
value: JSON.stringify(itemDetail)
})
}
else if (qtyInBL.status == 'failure' && DATASETS.checkForParameter(qtyInBL.reason)) { //if the function returns a failure response, then write the error details to summarize to create the error file
reduceContext.write({
key: "errorDetail",
value: JSON.stringify({ 'item': itemDetail.values.itemid, 'id': itemDetail.id, 'reason': qtyInBL.reason })
})
}
}
} catch (e) {
log.error("Error @ reduce", e.message);
reduceContext.write({
key: "errorDetail",
value: JSON.stringify({ 'item': itemDetail.values.itemid, 'id': itemDetail.id, 'reason': e.message })
})
}
},
/**
* Defines the function that is executed when the summarize entry point is triggered. This entry point is triggered
* automatically when the associated reduce stage is complete. This function is applied to the entire result set.
* @param {Object} summaryContext - Statistics about the execution of a map/reduce script
* @param {number} summaryContext.concurrency - Maximum concurrency number when executing parallel tasks for the map/reduce
* script
* @param {Date} summaryContext.dateCreated - The date and time when the map/reduce script began running
* @param {boolean} summaryContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {Iterator} summaryContext.output - Serialized keys and values that were saved as output during the reduce stage
* @param {number} summaryContext.seconds - Total seconds elapsed when running the map/reduce script
* @param {number} summaryContext.usage - Total number of governance usage units consumed when running the map/reduce
* script
* @param {number} summaryContext.yields - Total number of yields when running the map/reduce script
* @param {Object} summaryContext.inputSummary - Statistics about the input stage
* @param {Object} summaryContext.mapSummary - Statistics about the map stage
* @param {Object} summaryContext.reduceSummary - Statistics about the reduce stage
* @since 2015.2
*/
summarize: function (summaryContext) {
try {
let itemDetailArrayMain = [], itemDetailArrayBl = [], errorArray = [], zeroQtyInBL = [], itemArray = [];
summaryContext.output.iterator().each(function (key, value) {
if ((key == "itemDetail") && (JSON.parse(value).values.transOrderFrom == 'bl') && (JSON.parse(value).values.quantityAvailableInBL == 0)) { //need to send email to client for items having zero qty in BL location.
zeroQtyInBL.push({ 'itemName': JSON.parse(value).values.itemid, 'itemId': JSON.parse(value).id });
}
else if ((key == "itemDetail") && JSON.parse(value).values.transOrderFrom == 'main') {
itemDetailArrayBl.push(JSON.parse(value));
//(key == "itemDetail") ? (itemDetailArrayBl.push(JSON.parse(value)), itemArray.push(JSON.parse(value).id)) : errorArray.push(JSON.parse(value));
}
else if ((key == "itemDetail") && JSON.parse(value).values.transOrderFrom == 'bl') {
itemDetailArrayMain.push(JSON.parse(value));
itemArray.push(JSON.parse(value).id);
//(key == "itemDetail") ? (itemDetailArrayMain.push(JSON.parse(value)), itemArray.push(JSON.parse(value).id)) : errorArray.push(JSON.parse(value));
}
else {
errorArray.push(JSON.parse(value));
}
return true;
})
//get the quantity of these items in un-received transfer orders and add this to quantity available
//Quantity available should be the sum of location quantity available and the quantity of item entered in unreceived TOs
if (itemDetailArrayMain.length > 0) {
let updatedItemArray = DATASETS.getQuantityInTransOrder(itemArray, itemDetailArrayMain)
// create transfer order from BL to Main
let transOrdFromBL = DATASETS.createTransferOrder(updatedItemArray)
if (!DATASETS.checkForParameter(transOrdFromBL.status)) {
errorArray.push({ 'item': '', 'id': '', 'reason': 'TRANSFER_ORDER_CREATION_FAILED ' + transOrdFromBL.reason })
}
}
if (itemDetailArrayBl.length > 0) {
// create transfer order from Main to BL
let transOrdFromMain = DATASETS.createTransferOrder(itemDetailArrayBl)
if (!DATASETS.checkForParameter(transOrdFromMain.status)) {
errorArray.push({ 'item': '', 'id': '', 'reason': 'TRANSFER_ORDER_CREATION_FAILED ' + transOrdFromMain.reason })
}
}
//send email to client listing items having 0 qty available in BL
if (zeroQtyInBL.length > 0) {
DATASETS.sendEmails("zero", zeroQtyInBL)
}
//create csv file for sending error email
log.debug("Array listing all the errors occurred", errorArray)
if (errorArray.length > 0) {
let erroFile = DATASETS.createFileForErrors(errorArray)
if (erroFile) { //send error email
DATASETS.sendEmails("error", erroFile)
}
}
} catch (e) {
log.error("Error @ summarize", e.message)
}
}
}
return MAIN;
});