Generate an alert in the system when any assembly that needs to be built using a “Work Order Build” or “Assembly Build” is called in a Sales Order.
Proposal for Generating Alerts for backordered Assembly Builds in Sales order:
- Identifying Back-Ordered Assembly Items:
- When a Sales Order (SO) is created or updated, check if any assembly item is backordered.
- Retrieve Bill of Materials (BOM) for Back-Ordered Assembly Items record:
- If an assembly item is backordered, retrieve the details of that assembly item record
- from that retrieve the Bill of Materials (BOM) revision details from assembly item record.
- collect the details of all the components from Bill of material Revision record.
- Check Component Availability:
- check those components are available any of work orders and assembly builds
- Send Alert:
- If all components are available, include the details of the sales orders, back-ordered assembly items, and assembly item work order/assembly-built details in a single Excel file. Send this file to the admin via email. The email can be scheduled to be sent once or twice a day.
Script
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
/************************************************************************************************
* * Alert for Assembly Build **
*
*
* **********************************************************************************************
*
* Author: Jobin & Jismi IT Services LLP
*
* Date Created : 03-June-2024
*
* Created By: Jobin & Jismi IT Services LLP
*
* Description : Map Reduce Script to send emails when an assembly item in sales order created is backordered and its components is
* available in Workorders and Assembly built.
*
*
* Script Details :
* Script Name : JJ MR Alert for Assembly MAGT-663
* Script ID : customscript_jj_mr_alertassembly_magt663
* Deployment ID 1 : customdeploy_jj_mr_alertassembly_magt663
*
* REVISION HISTORY
*
*
*
*
***********************************************************************************************/
define(['N/email', 'N/record', 'N/search', 'N/file'],
/**
* @param{email} email
* @param{record} record
* @param{search} search
*/
(email, record, search, file) => {
const MAC = 10;
const sender = 245434, recipient = 472570;
/** @defines the function that to get the details of Bill of material in a Assembly item record
* @param {itemid} - internal id of Assmebly item
*/
function getBillofMaterial(itemid) {
try {
let assemblyitemSearchObj = search.create({
type: "assemblyitem",
filters:
[
["type", "anyof", "Assembly"],
"AND",
["internalid", "anyof", itemid]
],
columns:
[
search.createColumn({
name: "billofmaterials",
join: "assemblyItemBillOfMaterials",
label: "Bill of Materials"
})
]
});
// var searchResultCount = assemblyitemSearchObj.runPaged().count;
let bomName;
assemblyitemSearchObj.run().each(function (result) {
bomName = result.getValue({ name: "billofmaterials", join: "assemblyItemBillOfMaterials" });
return true;
});
return bomName;
} catch (er) {
log.error('error @ getBillofMaterial', er);
return er.message;
}
}
/** @defines the function that to get the details of Bill of material Revision in a Assembly item record
* @param {brId} - internal id of bill of materials
*/
function getBillofMaterialRevision(brId) {
try {
if (brId) {
let bomrevisionSearchObj = search.create({
type: "bomrevision",
filters:
[
["billofmaterials", "anyof", brId],
"AND",
["isinactive", "is", "F"]
],
columns:
[
search.createColumn({
name: "item",
join: "component",
label: "Item"
})
]
});
let componentDetails = [];
// let searchResultCount = bomrevisionSearchObj.runPaged().count;
bomrevisionSearchObj.run().each(function (result) {
let componentItemId = result.getValue({
name: "item",
join: "component"
});
let componentItemName = result.getText({
name: "item",
join: "component"
});
componentDetails.push({
id: componentItemId,
name: componentItemName
});
return true;
});
return componentDetails;
}
else
return {};
} catch (er) {
log.error('error @ getBillofMaterialRevision', er);
return er.message;
}
}
/** @defines the function that to get the details of Assembly build records in which assembly
* item components includes
* @param {components} - components internal id and names
* @param {sub} - subsidiary which sales order belongs to
*/
const itemsAvailableinAssembly = (components, sub) => {
try {
let assemblyOrders = {};
for (const component of components) {
let componentId = component.id;
let componentName = component.name;
let assemblybuildSearchObj = search.create({
type: "assemblybuild",
filters:
[
["type", "anyof", "Build"],
"AND",
["mainline", "is", "F"],
"AND",
["item.isinactive", "is", "F"],
"AND",
["item.internalid", "anyof", componentId]
, "AND",
["subsidiary", "anyof", sub.intId],
"AND",
["closed", "is", "F"]
],
columns:
[
search.createColumn({ name: "tranid", label: "Document Number" }),
search.createColumn({ name: "subsidiary", label: "Subsidiary" }),
search.createColumn({ name: "item", label: "Item" }),
search.createColumn({
name: "internalid",
join: "item",
label: "Internal ID"
})
]
});
assemblybuildSearchObj.run().each(result => {
let tranid = result.getValue({ name: 'tranid' });
if (tranid) {
if (!assemblyOrders[componentName]) {
assemblyOrders[componentName] = { recordDetails: [] };
}
assemblyOrders[componentName].recordDetails.push(tranid);
}
return true;
});
}
// log.debug('assemblyOrders', assemblyOrders);
return assemblyOrders;
} catch (er) {
log.error('error @ itemsAvailableinAssembly', er);
return er.message;
}
}
/** @defines the function that to get the details of Workorder records in which assembly
* item components includes
* @param {components} - components internal id and names
* @param {sub} - subsidiary which sales order belongs to
*/
const itemAvailableInWorkorder = (components, sub) => {
try {
let workorders = {};
for (const component of components) {
let componentId = component.id;
let componentName = component.name;
let workorderSearchObj = search.create({
type: "workorder",
filters: [
["type", "anyof", "WorkOrd"],
"AND",
["item.internalidnumber", "equalto", componentId],
"AND",
["status", "anyof", "WorkOrd:A", "WorkOrd:B"],
"AND",
["item.type", "noneof", "Assembly"],
"AND",
["subsidiary", "anyof", sub.intId]
],
columns: [
search.createColumn({ name: "tranid", label: "Document Number" }),
search.createColumn({ name: "item", label: "Item" })
]
});
let searchResultCount = workorderSearchObj.runPaged().count;
workorderSearchObj.run().each(result => {
let tranid = result.getValue({ name: 'tranid' });
if (tranid) {
if (!workorders[componentName]) {
workorders[componentName] = { recordDetails: [] };
}
workorders[componentName].recordDetails.push(tranid);
}
return true;
});
// log.debug('workorders', workorders);
}
return workorders;
} catch (er) {
log.error('error @ itemAvailableInWorkorder', er);
return er.message;
}
}
const removeDuplicatesAndJoin = (nestedArray) => {
// Access the inner array
let innerArray = nestedArray[0];
let uniqueArray = innerArray.filter((value, index, self) => {
return self.indexOf(value) === index;
});
let joinedString = uniqueArray.join(';');
return joinedString;
};
/**
* 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 in the map/reduce process
* @since 2015.2
*/
const getInputData = (inputContext) => {
try {
log.debug("///// START////");
let salesorderSearchObj = search.create({
type: "salesorder",
filters:
[
["type", "anyof", "SalesOrd"],
"AND",
["status", "anyof", "SalesOrd:B", "SalesOrd:E", "SalesOrd:D"],
"AND",
["item.type", "anyof", "Assembly"],
"AND",
["formulanumeric: {quantity}-nvl({quantityshiprecv},0)-nvl({quantitycommitted},0)", "greaterthan", "0"],
"AND",
["formuladate: CASE WHEN {trandate} >= ADD_MONTHS(SYSDATE, -6) THEN {trandate} ELSE NULL END","isnotempty",""],
// ["trandate", "within", "thisfiscalyeartodate"],
"AND",
["subsidiary", "anyof", MAC]
],
columns:
[
search.createColumn({ name: "tranid", label: "Document Number" }),
search.createColumn({ name: "quantity", label: "Quantity" }),
search.createColumn({ name: "item", label: "Item" }),
search.createColumn({
name: "type",
join: "item",
label: "Type"
}),
search.createColumn({
name: "formulanumeric",
formula: "{quantity}-nvl({quantityshiprecv},0)-nvl({quantitycommitted},0)",
label: "Back ordered"
}),
search.createColumn({ name: "subsidiary", label: "Subsidiary" })
]
});
return salesorderSearchObj;
}
catch (er) {
log.error('error @ GETINPUTDATA', er);
return er.message;
}
}
/**
* Defines the function that is executed when the map entry point is triggered. This entry point is triggered automatically
* when the associated getInputData stage is complete. This function is applied to each key-value pair in the provided
* context.
* @param {Object} mapContext - Data collection containing the key-value pairs to process in the map stage. This parameter
* is provided automatically based on the results of the getInputData stage.
* @param {Iterator} mapContext.errors - Serialized errors that were thrown during previous attempts to execute the map
* function on the current key-value pair
* @param {number} mapContext.executionNo - Number of times the map function has been executed on the current key-value
* pair
* @param {boolean} mapContext.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} mapContext.key - Key to be processed during the map stage
* @param {string} mapContext.value - Value to be processed during the map stage
* @since 2015.2
*/
const map = (mapContext) => {
try {
let soDetails = JSON.parse(mapContext.value);
log.debug('soDetails', soDetails);
let soId = soDetails.values.tranid;
let itemIdDetails = {};
itemIdDetails.intId = soDetails.values.item.value;
itemIdDetails.name = soDetails.values.item.text;
let soSubsidiary = {};
soSubsidiary.intId = soDetails.values.subsidiary.value;
soSubsidiary.name = soDetails.values.subsidiary.text;
let bomDetails = getBillofMaterial(itemIdDetails.intId);
let listComponets = getBillofMaterialRevision(bomDetails);
mapContext.write({
key: { itemIdDetails, listComponets, soSubsidiary },
value: {
salesorderId: soId,
}
});
}
catch (er) {
log.error('error@ MAP', er);
return er.message;
}
}
/**
* 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
*/
const reduce = (reduceContext) => {
try {
let redKey = reduceContext.key;
let reducedKey = JSON.parse(redKey);
let redItemId = reducedKey.itemIdDetails;
let redcomponents = reducedKey.listComponets;
let redSub = reducedKey.soSubsidiary;
let redValues = reduceContext.values.map(value => JSON.parse(value));
let redSo = [];
redSo = redValues.map(result => result.salesorderId);
let availableInAssemblyBuilds = {};
let availableInWorkorders = {};
if (redcomponents.length > 0) {
availableInAssemblyBuilds = itemsAvailableinAssembly(redcomponents, redSub);
availableInWorkorders = itemAvailableInWorkorder(redcomponents, redSub);
}
let data = {};
data = {
sId: redSo,
assemblybuildId: availableInAssemblyBuilds,
workorderId: availableInWorkorders
}
reduceContext.write({
key: { redItemId, redSub },
value: data
});
} catch (er) {
log.error('error@ REDUCE', er);
return er.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
*/
const summarize = (summaryContext) => {
try {
let csvContent = 'Subsidiary Name, Assembly item Name, Sales order, Component, Available Assembly Build Document No., Available Work order No.n'
summaryContext.output.iterator().each(function (key, values) {
let summaryKey = JSON.parse(key);
let summaryData = JSON.parse(values);
let hasAssemblybuildContent = Object.keys(summaryData.assemblybuildId || {}).length > 0;
let hasWorkorderIdContent = Object.keys(summaryData.workorderId || {}).length > 0;
if (hasAssemblybuildContent || hasWorkorderIdContent) {
let subsidiaryName = summaryKey.redSub?.name || '';
let assemblyItemName = summaryKey.redItemId?.name || '';
let salesOrderIds = Array.isArray(summaryData.sId) ? summaryData.sId.join(';') : '';
let keysDetails = [];
csvContent += `${subsidiaryName}, ${assemblyItemName}, ${salesOrderIds},`;
for (let key in summaryData.assemblybuildId) {
keysDetails.push(key);
let assemblyOrderIds = [];
if (summaryData.assemblybuildId[key] && summaryData.assemblybuildId[key].recordDetails) {
assemblyOrderIds.push(summaryData.assemblybuildId[key].recordDetails);
}
let uniqueAssemblyOrderIds = removeDuplicatesAndJoin(assemblyOrderIds);
csvContent += `${key}, ${uniqueAssemblyOrderIds} n, , ,`;
}
for (let key in summaryData.workorderId) {
keysDetails.push(key);
let workOrderIds = [];
if (summaryData.workorderId[key] && summaryData.workorderId[key].recordDetails) {
workOrderIds.push(summaryData.workorderId[key].recordDetails);
}
let uniqueWorkOrderIds = removeDuplicatesAndJoin(workOrderIds);
csvContent += ` ${key}, ,${uniqueWorkOrderIds}n, , , `;
}
log.debug('csvContent', csvContent);
csvContent += `n`;
}
return true;
});
// Create CSV file
let csvFile = file.create({
name: 'Component_Availability.csv',
fileType: file.Type.CSV,
contents: csvContent
});
csvFile.folder = 3827822; // SuiteScripts folder
let csvFileId = csvFile.save();
// let recipient = 472570;
// Send email to admin
email.send({
author: sender,
recipients: recipient,
subject: 'Component Availability Details',
body: 'Hi n Please find the attached file containing the component availability details. n Thank you.',
attachments: [file.load({ id: csvFileId })]
});
log.debug("///// END ////");
}
catch (er) {
log.error('error@ SUMMARIZE', er);
return er.message;
}
}
return { getInputData, map, reduce, summarize }
});