REQUIREMENT:
Send an email to acknowledge the quantity update on the purchase order’s items. When a user or system modifies the number of products in a purchase order. Check that the PO is listed in the “corresponding PO” column of the sales order. If the purchase order is included in the “corresponding PO” column of the sales order, We will send the purchase order’s updated item information to the employee “Marie M Raymond”.
SOLUTION:
/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
*/
/************************************************************************************************
CDU - 770 Notify the purchase order's quantity update by email
*********************************************************************************************
*
* Author: Jobin & Jismi IT Services LLP
*
* Date Created : 04/February/2022
*
* Description : Send an email to acknowledge the quantity update on the purchase order's items to an employee
*
***********************************************************************************************/
define(['N/email', 'N/file', 'N/record', 'N/search'],
/**
* @param{email} email
* @param{file} file
* @param{record} record
* @param{search} search
*/
(email, file, record, search) => {
/**
* function to create salesorder search to check if PO’s document number matches with a salesorders PO# field
*/
function createSoSearchUsingPo(poID){
try{
var salesorderSearchObj = search.create({
type: "salesorder",
filters:
[
["type","anyof","SalesOrd"],
"AND",
["mainline","is","F"],
"AND",
["cogs","is","F"],
"AND",
["taxline","is","F"],
"AND",
["shipping","is","F"],
"AND",
["custcol_jj_tlf_po_cdu_462.internalid","anyof",poID]
],
columns:
[
search.createColumn({
name: "tranid",
summary: "GROUP",
label: "Document Number"
})
]
});
var searchResultCount = salesorderSearchObj.runPaged().count;
log.debug("salesorderSearchObj result count",searchResultCount);
var soDocNum = null, soArray = [];
if (searchResultCount>0) {
salesorderSearchObj.run().each(function (result) {
soDocNum = result.getValue({
name: "tranid",
summary: "GROUP",
label: "Document Number"
});
soArray.push(soDocNum);
return true;
});
log.debug("so document number", soDocNum)
return soArray;
}
else
return [];
}catch (e) {
log.debug('Error@createSoSearchUsingPo', e)
return [];
}
}
/**
* function to append data to csv file
*/
function createCSVfile(csvFileData, updatedArr, deletedArray, insertedArray, newRec, poDocNumber, vendorName){
try{
//iterating through updated line array
for (let i_update = 0; i_update<updatedArr.length; i_update++) {
csvFileData += updatedArr[i_update].itemName + ',' + newRec.id + ',' + poDocNumber + ',' + vendorName + ',' + 'Update' + ',' + updatedArr[i_update].oldQty + ',' + updatedArr[i_update].quantity;
csvFileData += '\r\n';
}
//iterating through inserted line array
for (let i_insert = 0; i_insert<insertedArray.length; i_insert++) {
csvFileData += insertedArray[i_insert].itemName + ',' + newRec.id + ',' + poDocNumber + ',' + vendorName + ',' + 'Insertion' + ',,' + insertedArray[i_insert].quantity + ',';
csvFileData += '\r\n';
}
//iterating through deleted line array
for (let i_delete = 0; i_delete<deletedArray.length; i_delete++) {
csvFileData += deletedArray[i_delete].itemName + ',' + newRec.id + ',' + poDocNumber + ',' + vendorName + ',' + 'Deletion' + ',' + deletedArray[i_delete].quantity + ',,';
csvFileData += '\r\n';
}
return csvFileData;
}catch (e) {
log.debug("Error@createCSVfile", e)
return csvFileData;
}
}
/**
* Defines the function definition that is executed before record is submitted.
* @param {Object} context
* @param {Record} context.newRecord - New record
* @param {Record} context.oldRecord - Old record
* @param {string} context.type - Trigger type; use values from the context.UserEventType enum
* @since 2015.2
*/
const beforeSubmit = (context) => {
try{
var newRec = context.newRecord;
var oldRec = context.oldRecord;
var qtyUpdated = 0, oldArr = [], newArr = [];
if (context.type == context.UserEventType.EDIT) {
var poDocNumber = newRec.getValue('tranid');
log.debug('poDocNumber', poDocNumber);
//create salesorder search
var salesOrder = createSoSearchUsingPo(newRec.id);
if (salesOrder.length > 0){
//title for CSV file
var titleArray = ["Item Name", "PO IntenalID", "PO#", "Vendor", "Update Type", "Old Quantity", "New Quantity"];
var csvFileData = titleArray.toString() + '\r\n';
var vendorName = oldRec.getText('entity');
//check if PO's item line is updated.
var itemLineCountNew = newRec.getLineCount({
sublistId: 'item'
});
var itemLineCountOld = oldRec.getLineCount({
sublistId: 'item'
});
//loop through item lines
var uniqueKey, item;
for (let i = 0; i<itemLineCountOld; i++){
var oldObj = {};
oldObj.quantity = oldRec.getSublistValue({
sublistId: 'item',
fieldId: 'quantity',
line: i
});
uniqueKey= oldRec.getSublistValue({
sublistId: 'item',
fieldId: 'lineuniquekey',
line: i
});
item = oldRec.getSublistValue({
sublistId: 'item',
fieldId: 'item',
line: i
})
oldObj.uniqueKey = uniqueKey+"_"+item;
oldObj.key = uniqueKey;
oldObj.itemName = oldRec.getSublistValue({
sublistId: 'item',
fieldId: 'item_display',
line: i
})
oldArr.push(oldObj);
}
log.debug("oldArray", oldArr)
var uniqueKeyNew, itemNew;
for (let j = 0; j<itemLineCountNew; j++) {
var newObj = {};
newObj.quantity = newRec.getSublistValue({
sublistId: 'item',
fieldId: 'quantity',
line: j
});
uniqueKeyNew = newRec.getSublistValue({
sublistId: 'item',
fieldId: 'lineuniquekey',
line: j
});
itemNew = newRec.getSublistValue({
sublistId: 'item',
fieldId: 'item',
line: j
})
newObj.uniqueKey = uniqueKeyNew+"_"+itemNew;
newObj.key = uniqueKeyNew;
newObj.itemName = newRec.getSublistValue({
sublistId: 'item',
fieldId: 'item_display',
line: j
})
newArr.push(newObj)
}
log.debug("newArray", newArr)
//compare the data's in both arrays for identifying the updated items
var itemsUpdatedArray = [];
for (let outIndex=0; outIndex < oldArr.length; outIndex++ ) {
for (let inIndex = 0 ; inIndex < newArr.length ; inIndex++) {
if (oldArr[outIndex].uniqueKey == newArr[inIndex].uniqueKey) {
if (oldArr[outIndex].quantity != newArr[inIndex].quantity) {
newArr[inIndex].oldQty = oldArr[outIndex].quantity
itemsUpdatedArray.push(newArr[inIndex])
}
}
}
}
log.debug("itemsUpdatedArray", itemsUpdatedArray)
//compare the data's in both arrays for identifying the deleted items
var deletedArray = [];
for (let outIndex=0; outIndex < oldArr.length; outIndex++ ) {
var flagVal = true;
for (let inIndex = 0 ; inIndex < newArr.length ; inIndex++) {
if (oldArr[outIndex].uniqueKey == newArr[inIndex].uniqueKey) {
flagVal = false
}
}
if(flagVal)
deletedArray.push(oldArr[outIndex])
}
log.debug("deletedArray", deletedArray)
//compare the data's in both arrays for identifying the inserted items
var insertedArray = [];
for (let i = 0 ; i < newArr.length ; i++) {
if (newArr[i].key == "") {
insertedArray.push(newArr[i])
}
}
log.debug("insertedArray", insertedArray)
if (itemsUpdatedArray.length>0 || deletedArray.length>0 || insertedArray.length>0){
//create csv file content
csvFileData = createCSVfile(csvFileData, itemsUpdatedArray, deletedArray, insertedArray, newRec, poDocNumber, vendorName)
log.debug("csvFileData", csvFileData)
var fileObj = file.create({
name: "PO Quantity Update.csv",
fileType: file.Type.CSV,
contents: csvFileData,
encoding: file.Encoding.UTF8,
// folder: 190829
});
// fileObj.save();
email.send({
author: //AUTHOR,
recipients: //RECIPIENT,
subject: 'Purchase Order Quantity Update: #'+poDocNumber,
body: 'Hi ,' +'\n'+ 'This email is to inform you that an item line quantity on purchase order #'+ poDocNumber +' has been updated.' + '\n' + 'Please find the attached document' + '\n' + 'Thank You.',
attachments: [fileObj]
});
}
}
}
}catch (e) {
log.debug("Error@beforeSubmit", e)
}
}
const afterSubmit = (context) => {
}
return {beforeSubmit, afterSubmit}
});