Jira Code: TOUC-137
A scheduled script is run which creates a saved search to find quantity onhand and quantity sold items of Preferred Bin in the main warehouse. It fetches the items name, quantity onhand and quantity sold.
If quantity onhand – quantity sold is less than or equal to zero, and quantity sold greater than zero, sets quantity onhand zero, if quantity onhand less than zero else sets the default value of quantity onhand. Obtains the value of ‘X’ amount items =(quantity sold – quantity onhand) which then creates a task which sends an email notification to the user with the item name and the quantity(X) of that items to be added.
/**
* @NApiVersion 2.x
* @NScriptType ScheduledScript
* @NModuleScope SameAccount
*/
/**
* Script Description
* To run a 10 day sales report to see how many were sold and then create a task to say put x amount in the preferred bin.
**/
/*******************************************************************************
* TOUC
* **************************************************************************
*
* Date: 28-08-2018
*
* Author: Jobin & Jismi IT Services LLP
*
*
* REVISION HISTORY
*
* Revision 1 $ 28-08-2018 rijoy : Created
*
*****************************************************************************
**/
define(['N/email', 'N/file','N/record', 'N/runtime', 'N/search', 'N/task'],
/**
* @param {email} email
* @param {file} file
* @param {format} format
* @param {http} http
* @param {https} https
* @param {record} record
* @param {runtime} runtime
* @param {search} search
* @param {task} task
*/
function(email, file, record, runtime, search, task) {
/**
* Definition of the Scheduled script trigger point.
*
* @param {Object} scriptContext
* @param {string} scriptContext.type - The context in which the script is executed. It is one of the values from the scriptContext.InvocationType enum.
* @Since 2015.2
*/
function execute(scriptContext) {
try{
var item_Name=[];
var qty_Onhand=[];
var quantity_Sold=[];
/*fucntion to create saved search for the items in the bin determine 10 days worth of stock*/
var transactionSearchObj = search.create({
type: "transaction",
filters:
[
["posting","is","T"],
"AND",
["transactionlinetype","noneof","WIPVARIANCE"],
"AND",
["trandate","within","daysago10","daysago0"],
"AND",
["item.preferredlocation","anyof","7"],
"AND",
["item.preferredbin","is","T"]
],
columns:
[
search.createColumn({
name: "itemid",
join: "item",
summary: "GROUP",
label: "Name"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "Case when {accounttype} = 'Other Current Asset' then {quantity} else 0 end",
label: "Quantity On Hand"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "Case when {Type} in ('Cash Sales', 'Invoice', 'Credit Memo') and {accounttype} = 'Income' then {quantity} else 0 end",
label: "Quantity Sold"
})
]
});
var searchResultCount = transactionSearchObj.runPaged().count;
log.debug("transactionSearchObj result count",searchResultCount);
var qty_Total;
var start = 0;
var count=0;
var end = 1000;
var resultarray = [];
var result;
var singleresult;
for (var i = 0; i < Math.ceil(searchResultCount / 1000); i++)
{
result = transactionSearchObj.run().getRange({
start: start,
end: end
});
for (var j = 0; j < result.length; j++) {
singleresult = result[j];
resultarray.push(singleresult);
item_Name.push(singleresult.getValue(transactionSearchObj.columns[0]));
qty_Onhand.push(singleresult.getValue(transactionSearchObj.columns[1]));
quantity_Sold.push(singleresult.getValue(transactionSearchObj.columns[2]));
if(((qty_Onhand[j]-quantity_Sold[j])<=0)&&(quantity_Sold[j]>0))
{
count++;
qty_Total=quantity_Sold[j]-qty_Onhand[j];
log.debug({
title:'qty_Total',
details:qty_Total
});
checkTask(qty_Total,item_Name[j]);/*Function call to check duplicate task*/
}
}
start = end;
end = end + 1000;
}
log.debug({
title:'COUNT',
details:count
});
log.debug({
title:'item_Name',
details:item_Name
});
log.debug({
title:'qty_Onhand',
details:qty_Onhand
});
log.debug({
title:'quantity_Sold',
details:quantity_Sold
});
/*context.response.write(JSON.stringify(resultarray));*/
}
catch(e){
log.debug({
title:'err@Get',
details:e
});
}
}
return {
execute: execute
};
/* Function to check duplicate task created*/
function checkTask(quantity_Total,ItemName)
{
var Id;
var title;
var taskSearchObj = search.create({
type: "task",
filters:
[
["assigned","anyof","6380"],
"AND",
["title","is","Need to Update Stock Quantity:"+ItemName],
"AND",
["status","noneof","COMPLETE"],
],
columns:
[
search.createColumn({
name: 'internalid',
sort: search.Sort.ASC,
})
]
});
var searchResultCount = taskSearchObj.runPaged().count;
log.debug("taskSearchObj result count",searchResultCount);
taskSearchObj.run().each(function(result){
Id =result.getValue({
name:'internalid',
});
});
if(searchResultCount>0)
{
var loadTask = record.load({
id:Id,
type: record.Type.TASK,
isDynamic: true,
});
loadTask.setValue({
fieldId: 'title',
value: 'Need to Update Stock Quantity:'+ItemName,
});
loadTask.setValue({
fieldId: 'assigned',
value:6380
});
loadTask.setValue({
fieldId: 'sendemail',
value:false,
});
loadTask.setValue({
fieldId:'priority',
value:'HIGH',
});
loadTask.setText({
fieldId: 'message',
text:'Add ' +quantity_Total+ ' Pieces of Item:'+ItemName
});
var loadTaskId = loadTask.save();
log.debug({
title:'loadTaskId',
details:loadTaskId
});
}
else
{
createTask(quantity_Total,ItemName);
}
}
/*Function to create task*/
function createTask(Cquantity_Total,CitemName)
{
var newTask = record.create({
type: record.Type.TASK,
isDynamic: true
});
newTask.setValue({
fieldId: 'title',
value: 'Need to Update Stock Quantity:'+CitemName,
});
newTask.setValue({
fieldId: 'assigned',
value:6380
});
newTask.setValue({
fieldId: 'sendemail',
value:true
});
newTask.setValue({
fieldId:'priority',
value:'HIGH'
});
newTask.setText({
fieldId: 'message',
text:'Add ' +Cquantity_Total+ ' Pieces of Item:'+CitemName
});
var newTaskId = newTask.save();
log.debug({
title:'newTaskId',
details:newTaskId
});
}
});