Jira Code: AN-60
When an item’s inventory levels for a certain lot gets below 1(greater than 0), we would like a script to delete the levels so that there is nothing, or “0” left.
Map Reduce Script: AN-60 JJ MR Inventory Adjustments
/**
* @NApiVersion 2.x
* @NScriptType MapReduceScript
* @NModuleScope SameAccount
*/
/*******************************************************************************
* CLIENTNAME:Aurora Naturals
* AN-60
* When an item's inventory levels for a certain lot gets below 1, we would like a script to delete the levels so that there is nothing, or "0" left.
* **************************************************************************
* Date : 11-05-2019
*
* Author: Jobin & Jismi IT Services LLP
* Script Description : This script is to create inventory adjustment records if the inventory level of
* lot-numbered item is less than 0.Also create an excel and send the details of items as an email.
*
* Date created :11-05-2019
*
* REVISION HISTORY
*
* Revision 1.0 ${11-05-2019} nd : created
*
******************************************************************************/
define(['N/search','N/record','N/encode','N/email','N/file'],
function(search,record,encode,email,file) {
/**
* Marks the beginning of the Map/Reduce process and generates input data.
*
* @typedef {Object} ObjectRef
* @property {number} id - Internal ID of the record instance
* @property {string} type - Record type id
*
* @return {Array|Object|Search|RecordRef} inputSummary
* @since 2015.1
*/
var obj={};
var i=0;
function getInputData() {
try{
var itemSearchObj = search.create({
type: "item",
filters:
[
["inventorylocation.isinactive","is","F"],
"AND",
["formulanumeric: {inventorynumberbinonhand.quantityonhand}","greaterthan","0"],
"AND",
["formulanumeric: {inventorynumberbinonhand.quantityonhand}","lessthan","1"],
"AND",
["islotitem","is","T"],
"AND",
["type","anyof","InvtPart"]/*,
"AND",
["internalid","anyof","581","473"]*/
// "AND",
// ["islotitem","t",""]
],
columns:
[
search.createColumn({name: "internalid", label: "Internal ID"}),
search.createColumn({
name: "itemid",
sort: search.Sort.ASC,
label: "Name"
}),
search.createColumn({
name: "binnumber",
join: "inventoryNumberBinOnHand",
label: "Bin Number"
}),
search.createColumn({
name: "inventorynumber",
join: "inventoryNumberBinOnHand",
label: "Inventory Number"
}),
search.createColumn({
name: "quantityonhand",
join: "inventoryNumberBinOnHand",
label: "On Hand"
}),
search.createColumn({
name: "quantityavailable",
join: "inventoryNumberBinOnHand",
label: "Available"
}),
search.createColumn({
name: "location",
join: "inventoryNumberBinOnHand",
label: "Location"
})
]
});
var searchResult = itemSearchObj.run().getRange({
start: 0,
end: 1000
});
// log.debug('searchResult.length',searchResult.length)
return searchResult;
}catch(err){
log.debug('error @ getInputData',err)
}
}
/**
* Executes when the map entry point is triggered and applies to each key/value pair.
*
* @param {MapSummary} context - Data collection containing the key/value pairs to process through the map stage
* @since 2015.1
*/
function map(context) {
try{
var searchResult = JSON.parse(context.value);
// log.debug('searchResult',searchResult)
var item_internalId = searchResult.id;
// log.debug('item_internalId',item_internalId)
context.write({
key: item_internalId,
value: context.value
});
}catch(err){
log.debug('error @ map',err)
}
}
/**
* Executes when the reduce entry point is triggered and applies to each group.
*
* @param {ReduceSummary} context - Data collection containing the groups to process through the reduce stage
* @since 2015.1
*/
function reduce(context) {
try{
++i;
log.debug('i',i)
var item_intId = context.key;
var parsed_value = JSON.parse(context.values[0]);
var item_name = parsed_value["values"]["itemid"];
// log.debug('item_name',item_name)
var bin_nmbr = parsed_value["values"]["inventoryNumberBinOnHand.binnumber"][0].value;
var bin_nmbrTxt = parsed_value["values"]["inventoryNumberBinOnHand.binnumber"][0].text;
// log.debug('bin_nmbr',bin_nmbr)
var location = parsed_value["values"]["inventoryNumberBinOnHand.location"][0].value;
var locationTXT = parsed_value["values"]["inventoryNumberBinOnHand.location"][0].text;
// log.debug('location',location)
var inventory_number = parsed_value["values"]["inventoryNumberBinOnHand.inventorynumber"][0].value;
var inventory_numberTXT = parsed_value["values"]["inventoryNumberBinOnHand.inventorynumber"][0].text;
// log.debug('inventory_number',inventory_number)
var item_intId = parsed_value["values"]["internalid"][0].text;
// log.debug('item_intId',item_intId)
var qty_onHand = parsed_value["values"]["inventoryNumberBinOnHand.quantityonhand"];
// log.debug('qty_onHand',qty_onHand)
var objRecord = record.create({
type: record.Type.INVENTORY_ADJUSTMENT,
isDynamic: true,
});
objRecord.setValue({
fieldId: 'subsidiary',
value: 1
// ignoreFieldChange: true
});
objRecord.setValue({
fieldId: 'account',
value: 638
// ignoreFieldChange: true
});
// objRecord.setValue({
// fieldId: 'account',
// value: 638
// // ignoreFieldChange: true
// });
var lineNum = objRecord.selectNewLine({
sublistId: 'inventory'
});
// log.debug('lineNum',lineNum);
objRecord.setCurrentSublistValue({
sublistId: 'inventory',
fieldId: 'item',
// line: lineNum,
value: item_intId
});
objRecord.setCurrentSublistValue({
sublistId: 'inventory',
fieldId: 'adjustqtyby',
// line: lineNum,
value: -(qty_onHand)
});
objRecord.setCurrentSublistValue({
sublistId: 'inventory',
fieldId: 'location',
// line: lineNum,
value: location
});
subrecordInvDetail = objRecord.getCurrentSublistSubrecord({
sublistId: 'inventory',
fieldId: 'inventorydetail'
});
// log.debug('subrecordInvDetail',subrecordInvDetail);
subrecordInvDetail.selectNewLine({//select inventory detail(sub record) line
sublistId: 'inventoryassignment'
});
subrecordInvDetail.setCurrentSublistValue({
sublistId: 'inventoryassignment',
fieldId: 'issueinventorynumber',
value: inventory_number
});
subrecordInvDetail.setCurrentSublistValue({
sublistId: 'inventoryassignment',
fieldId: 'binnumber',
value: bin_nmbr
});
subrecordInvDetail.setCurrentSublistValue({
sublistId: 'inventoryassignment',
fieldId: 'quantity',
value:-(qty_onHand)
});
subrecordInvDetail.commitLine({
sublistId: 'inventoryassignment'
});
objRecord.commitLine({
sublistId: 'inventory'
});
var recordId=objRecord.save();
log.debug('recordId',recordId)
if(recordId){
obj[i]=[item_name]
obj[i][1]=[bin_nmbrTxt]
obj[i][2]=[locationTXT]
obj[i][3]=[inventory_numberTXT]
obj[i][4]=[qty_onHand]
obj[i][5]=[0]
obj[i][6]=[0]
obj[i][7]=[item_intId]
obj[i][9]=[location]
obj[i][10]=['2855 Spoilage, Scrap']
}
context.write({
key: i ,
value:obj
});
}catch(err){
log.debug('error @ reduce',err)
}
}
/**
* Executes when the summarize entry point is triggered and applies to the result set.
*
* @param {Summary} summary - Holds statistics regarding the execution of a map/reduce script
* @since 2015.1
*/
function summarize(summary) {
try{
// Create a variable to track how many key/value pairs were written.
var totalRecordsUpdated = 0;
// If the number of key/value pairs is expected to be manageable, log
// each one.
var obj_result
summary.output.iterator().each(function (key, value){
log.debug('key',key);
log.debug('value',value)
obj_result=value;
totalRecordsUpdated++;
return true;
});
log.debug('obj_result',obj_result)
var XML = "";
var myXMLFile = file.load({
id: '78883'
});
log.debug('myXMLFile',myXMLFile)
var myXMLFile_value = myXMLFile.getContents();
var obj = JSON.parse(obj_result);
var TABLE = "";
for(var key in obj) {
log.debug('obj[key][7]',obj[key][7])
var inventoryitemSearchObj = search.create({
type: "inventoryitem",
filters:
[
["type","anyof","InvtPart"],
"AND",
["inventorylocation","anyof",obj[key][9]],
"AND",
["internalidnumber","equalto",obj[key][7]]
],
columns:
[
search.createColumn({name: "internalid", label: "Internal ID"}),
search.createColumn({
name: "itemid",
sort: search.Sort.ASC,
label: "Name"
}),
search.createColumn({name: "currentstandardcost", label: "Current Standard Cost"})
]
});
var searchResult1 = inventoryitemSearchObj.run().getRange({
start: 0,
end: 1
});
var std_cost;
var intrId;
for (var j = 0; j<searchResult1.length; j++) {
std_cost = searchResult1[j].getValue({
name: "currentstandardcost"
});
}
if(std_cost){
obj[key][8] = parseFloat(std_cost)*parseFloat(obj[key][4]);
log.debug('parseFloat(std_cost)',parseFloat(std_cost))
log.debug('parseFloat(obj[key][4])',parseFloat(obj[key][4]))
log.debug('parseFloat(obj[key][8])',parseFloat(obj[key][8]))
}
// log.debug('key after std cost',key)
var strVar = "";
strVar += "<Row>";
strVar += "<Cell><Data ss:Type=\"String\">" + obj[key][0] +"<\/Data><\/Cell>";
strVar += "<Cell><Data ss:Type=\"String\">" + obj[key][1] + "<\/Data><\/Cell>";
strVar += "<Cell><Data ss:Type=\"String\">" + obj[key][2] + "<\/Data><\/Cell>";
strVar += "<Cell><Data ss:Type=\"String\">" + obj[key][3] + "<\/Data><\/Cell>";
strVar += "<Cell><Data ss:Type=\"String\">" + obj[key][4]+ "<\/Data><\/Cell>";
strVar += "<Cell><Data ss:Type=\"Number\">" + obj[key][8]+ "<\/Data><\/Cell>";
strVar += "<Cell><Data ss:Type=\"Number\">" + obj[key][5] + "<\/Data><\/Cell>";
// strVar += "<Cell><Data ss:Type=\"String\">" + obj[key][7] + "<\/Data><\/Cell>";
strVar += "<Cell><Data ss:Type=\"Number\">" + obj[key][6] + "<\/Data><\/Cell>";
strVar += "<Cell><Data ss:Type=\"String\">" + obj[key][10] + "<\/Data><\/Cell>";
strVar += "<\/Row>";
TABLE = TABLE + strVar;
}
myXMLFile_value = myXMLFile_value.replace('<!-- REPLACEWITHTABLEBODY -->', TABLE);
log.debug('obj after std cost',obj)
// log.debug('obj.length',obj.length)
var strXmlEncoded = encode.convert({
string: myXMLFile_value,
inputEncoding: encode.Encoding.UTF_8,
outputEncoding: encode.Encoding.BASE_64
});
var date = new Date();
var fileObj = file.create({
name: 'InventoryAdjustment-'+date+'.xls',
fileType: file.Type.EXCEL,
contents: strXmlEncoded
});
fileObj.folder = 6308;
var fileId = fileObj.save();
email.send({
author: -5,
recipients: 'it@auroraproduct.com',
// recipients: 'navia@jobinandjismi.com',
subject: 'Inventory Adjustment Details',
body: 'Inventory Adjustment - '+date,
attachments: [fileObj],
});
}catch(err){
log.debug('error @ summarize',err)
}
}
return {
getInputData: getInputData,
map: map,
reduce: reduce,
summarize: summarize
};
});