Getting Item Earliest Available Date Programatically

December 2, 2016 Adolfo Garza Comments 39 comments

I was browsing through the Netsuite user group and I stumbled upon this interesting question:

I have a client that wants to automate the process of checking/calculating the ‘Earliest Available Date’ from the ‘Check Item Availability’ feature on the item level of a sales order. I know that this calculation is done using the ATP lead time, but I am not sure how to initiate this calculation via SuiteScript or Saved Search. I have shown this issue to NetSuite support and they told me that it is not currently possible to access the “Earliest Available Date” by either Saved Search or API. It literally requires someone to click the “Check Item Availability” which loads the popup dialog and shows the date. My client would obviously like to avoid having to drill into every sales order and check this date.

Does anyone know of a way to display the ‘Earliest available date’ via Saved Search or API? Or many another way to calculate it via some other ATP lead time calculation.

I was able to reverse engineer the process that Netsuite uses to calculate the “Earliest Available date” and I came up with a solution that works within the browser console. The solution loads the popup in the back, parses the HTML and gets the data. This may help you get started on designing a more robust solution.

Here’s a sample output:

{

"transactionnumber" : "SLS00000684",

"location" : "1",

"subsidiary" : "1",

"quantity" : "20",

"item" : "553",

"type" : "InvtPart",

"availableDate" : "12/1/2016"

}

Here’s the code ready to run in the browser console:

Note: Since this is running in the browser you might need to play around with the script to get over 100 items at a time as the governance would run out. Alternatively you could use an Ajax call instead of nlapiRequestURL.

function getItemsToProcess() {

var results = [];

var filters = [];

var columns = [];

filters.push(new nlobjSearchFilter('type', null, 'anyof', ['SalesOrd']));

filters.push(new nlobjSearchFilter('trandate', null, 'within', ['12/1/2016', '12/1/2016'])); //Change this as needed

filters.push(new nlobjSearchFilter('type', 'item', 'anyof', ['Assembly', 'InvtPart']));

columns.push(new nlobjSearchColumn('transactionnumber', null, null));

columns.push(new nlobjSearchColumn('location', null, null));

columns.push(new nlobjSearchColumn('subsidiary', null, null));

columns.push(new nlobjSearchColumn('quantity', null, null));

columns.push(new nlobjSearchColumn('item', null, null));

columns.push(new nlobjSearchColumn('type', 'item', null));

results = nlapiSearchRecord('salesorder', null, filters, columns);

var itemsToProcess = [];

for (var i in results) {

var result = results[i];

var row = {};

row.transactionnumber = result.getValue(columns[0]);

row.location = result.getValue(columns[1]);

row.subsidiary = result.getValue(columns[2]);

row.quantity = result.getValue(columns[3]);

row.item = result.getValue(columns[4]);

row.type = result.getValue(columns[5]);

itemsToProcess.push(row);

}

return itemsToProcess;

}

function getItemAvailability(itemId, itemType, itemLocation, itemQty, subsidiary){

if (itemType != 'InvtPart' && itemType != 'Assembly'){

return "UNSUPPORTED";

}

var myURL = '/app/accounting/inventory/atp/checkitemavailability.nl?item=' + itemId + '&location=' + itemLocation + '&quantity=' + itemQty + '&subsidiary=' + subsidiary;

//myURL+= '&unit=' + unitOfMeasure; //Needs more work to pass this

var myResponse = nlapiRequestURL(myURL);

var myHTML = myResponse.getBody();

var parser = new DOMParser();

var doc = parser.parseFromString(myHTML, "text/html");

var availableDate = getEarliestAvailableDate(doc, itemId, itemQty, itemLocation);

return availableDate;

//**** HELPER FUNCTIONS ****//

function getEarliestAvailableDate(myDoc, itemId, itemQuantity, itemLocation) {

var quantity = parseFloat(itemQuantity) || 0;

var earliestavailabledate = '';

if (quantity && itemId && itemLocation) {

var numberOfLines = getOrderMachineSize(myDoc);

for (var i = 1; i <= numberOfLines; i++) {

if (quantity <= parseFloat(getOrderMachineValue(myDoc, 'hiddenavailability', i))) {

earliestavailabledate = getOrderMachineValue(myDoc, 'hiddendorderdate', i);

return earliestavailabledate;

}

var atpleadtime = myDoc.forms['main_form'].elements['atpleadtime'].value;

if (!atpleadtime || !earliestavailabledate) {

earliestavailabledate = nlapiDateToString(nlapiAddDays(new Date(), atpleadtime));

return earliestavailabledate;

}

return earliestavailabledate;

//******* HELPER FUNCTIONS ******//

function getOrderMachineValue(myDoc, fieldName, line) {

return myDoc.forms['order_form'].elements[fieldName + line].value;

}

function getOrderMachineSize(myDoc) {

var numberOfLines = 0;

while (myDoc.forms['order_form'].elements['hiddenavailability' + ++numberOfLines] != null);

return numberOfLines - 1;

}

function run(){

var itemsToProcess = getItemsToProcess();

var itemsProcessed = [];

for(var i in itemsToProcess){

var itemToProcess = itemsToProcess[i];

itemToProcess.availableDate = getItemAvailability(itemToProcess.item, itemToProcess.type, itemToProcess.location, itemToProcess.quantity, itemToProcess.subsidiary);

itemsProcessed.push(itemToProcess);

}

return itemsProcessed;

}