Learn the NetSuite Script Pattern to Automatically Set Lot / Serial and Bin Numbers

This article is relevant if you are using NetSuite and you need to automatically set Lot (or Serial) and Bin numbers on Sales Order and Fulfillment Transactions.

Background

Recently, a client came to us looking for help to streamline their NetSuite based fulfillment operations. In their case, they had these basic requirements:

1. Determine item lot number using last-in-first-out (LIFO) techniques
2. Find the preferred bin location from an item record default
3. Set this information automatically during item fulfillment creation

I have spoken about this before in my article Assign NetSuite Lot Numbers to Automate Bulk Order Fulfillment.  To solve this challenge requires lookup of information both on item records and existing item receipt and inventory adjustment transactions. Furthermore, setting the item fulfillment to have this information is tricky as NetSuite prefers this information to be first applied in a Sales Order before generating the Item Fulfillment.

Working with one of our consultants, we were able to push through the considerations and solve the client’s concern. I was pleased with the results as she illustrated her capacity for technical depth and competency.

NetSuite SuiteScript Lot and Bin Lookup to Automatically Set During Transaction Creation

Reflecting on the solution, I thought about how frequently this comes up in our client discussions. Many times, the concept of NetSuite Lot numbers and Bins are not physical but logical constructs. When they are logical, we have an opportunity to use intelligent algorithms to automate and streamline the day-to-day work.

Accordingly, I lifted her code to provide to the community. Please consider that the code below is not exactly working. I generalized the script so that the bulk of the pattern is in place to help educate. Yet, my hope is that with some adaptation, you can make it work in your real life situation.

//note, code below came from working example but refined for narrative and education
//SuiteScript 1.0 vs. 2.0 was used due to different use contexts that was actually required.
function createItemFufillmentWithLot(id) {
	var nsRecord, lineItems, locations;

	//create an item fulfillment from sales order and auto fill with the lot numbers using LIFO
	//also accommodate preferred bin assuming customization on item record to hold reference to a preferred bin
	nsRecord = nlapiTransformRecord('salesorder', id, 'itemfulfillment');
	lineItems = [];
	lotNumbers = [];
	locations = [];
	
	//build array of transaction item and locations to prepare for batch based searches
	for(var i = 1; i <= nsRecord.getLineItemCount('item'); i++){
		lineItems.push(nsRecord.getLineItemValue('item','item',i));

		if(nsRecord.getLineItemValue('item','location',i))
			locations.push(nsRecord.getLineItemValue('item','location',i));
	}

	//Gather information on Lot Numbers and Preferred BIN using Item Record
	var x = searchLotNumbersFromItem(lineItems);

	//Search LIFO Sequence of Lot Numbers from Historical Transactions
	var s = searchLotNumberSequence(lineItems,locations);

	//Set Sequence based from Dates using Preferred BIN
	for(var i = 0; i < s.length; i++){
		var itemId = s[i].getValue('item');
		var trandate = s[i].getValue('trandate');
		var locationId = s[i].getValue('location','inventoryDetail');
		var inventoryNumber = s[i].getValue('inventorynumber','inventoryDetail');
		var quantity = s[i].getValue('quantity','inventoryDetail');
		var binNumber = s[i].getValue('binnumber','inventoryDetail');

		binNumber = (binNumber == null || binNumber == '') ? '' : binNumber;

		//test for values
		if(!x[itemId]) continue;
		if(!x[itemId][locationId]) continue;
		if(!x[itemId][locationId].preferred.bin) continue;
		if(!x[itemId][locationId].preferred.inventory[inventoryNumber]) continue;

		//get structure in place
		if(x[itemId][locationId].dates[trandate] == null){
			x[itemId][locationId].dates[trandate] = {};
		}
		//get structure in place
		if(x[itemId][locationId].dates[trandate][binNumber] == null){
			x[itemId][locationId].dates[trandate][binNumber] = {}
		}

		x[itemId][locationId].dates[trandate][binNumber][inventoryNumber] = x[itemId][locationId].preferred.inventory[inventoryNumber];
	}

	//Set Sequence based from Dates
	for(var i = 0; i < s.length; i++){
		var itemId = s[i].getValue('item');
		var trandate = s[i].getValue('trandate');
		var locationId = s[i].getValue('location','inventoryDetail');
		var inventoryNumber = s[i].getValue('inventorynumber','inventoryDetail');
		var quantity = s[i].getValue('quantity','inventoryDetail');
		var binNumber = s[i].getValue('binnumber','inventoryDetail');

		binNumber = (binNumber == null || binNumber == '') ? '' : binNumber;

		if(!x[itemId]) continue;
		if(!x[itemId][locationId]) continue;
		if(!x[itemId][locationId].inventory[binNumber]) continue;
		if(!x[itemId][locationId].inventory[binNumber][inventoryNumber]) continue;

		if(x[itemId][locationId].dates[trandate] == null){
			x[itemId][locationId].dates[trandate] = {};
		}

		if(x[itemId][locationId].dates[trandate][binNumber] == null){
			x[itemId][locationId].dates[trandate][binNumber] = {}
		}

		x[itemId][locationId].dates[trandate][binNumber][inventoryNumber] = x[itemId][locationId].inventory[binNumber][inventoryNumber];
	}


	//now do the work to set the LOT Number and Preferred Bin on each item
	for(var i = 1; i <= nsRecord.getLineItemCount('item'); i++){
		
		nsRecord.selectLineItem('item', i);

		var invDetail = nsRecord.createCurrentLineItemSubrecord('item','inventorydetail');
		var itemId = nsRecord.getCurrentLineItemValue('item', 'item');
		var locationId = nsRecord.getCurrentLineItemValue('item', 'location'); 
		var qty = Number(nsRecord.getCurrentLineItemValue('item','quantityremaining'));

		//check if the item supports assignment
		if(invDetail && invDetail.getLineItemCount('inventoryassignment') <= 0){
			
			var dt = x[itemId][locationId].dates;
			var countedQty = 0;
			var lines = [];

			//prep structure so we can populate assignments across multiple layer ranges
			dateloop:
			for(var d in dt){ //Loop for Dates
				for(var l in dt[d]){ //Loop for Bin Numbers
					for(var b in dt[d][l]){ //Loop for Serial/Lot Numbers
						var binQty = dt[d][l][b];
						var q = (qty <= binQty) ? qty : binQty; 
						qty = qty - q;							//Deduct Bin Quantity on Total Quantity

						countedQty += q;
						lines.push({
							'number': b,
							'bin': l,
							'qty': q
						});
						if(qty <= 0)
						break dateloop;
					}
				}
			}

			
			//unload any assignments that may already be there
			var c = invDetail.getLineItemCount('inventoryassignment');
			for(var x = c; x > 0; x--){
				invDetail.removeLineItem('inventoryassignment',x);
			}

			//Set Quantity from Available
			nsRecord.setCurrentLineItemValue('item', 'quantity', countedQty);

			var c = 0;
			try{
				for(var l = 0; l < lines.length; l++){
					
						nlapiLogExecution('Debug', 'INV Detail', invDetail.getLineItemCount('inventoryassignment') + ' : ' + lines[l].number + ' | ' + lines[l].bin + ' | ' + lines[l].qty + ' / ' +countedQty);
						invDetail.selectNewLineItem('inventoryassignment');
						invDetail.setCurrentLineItemValue('inventoryassignment','issueinventorynumber', lines[l].number);
						invDetail.setCurrentLineItemValue('inventoryassignment','binnumber', lines[l].bin);
						invDetail.setCurrentLineItemValue('inventoryassignment','quantity', lines[l].qty);
						invDetail.commitLineItem('inventoryassignment');
					
				}
				invDetail.commit();
			}catch(e){
				nlapiLogExecution('Debug','Lot Status', e.toString());
			}	
			nsRecord.commitLineItem('item');
		}
	}
	id = nlapiSubmitRecord(nsRecord,null,true);
	return id;
}


function searchLotNumbersFromItem(itemID){
	var x = {};
	var s = nlapiSearchRecord('item',null,
		[
			new nlobjSearchFilter('internalid',null,'anyof',itemID),
			new nlobjSearchFilter('quantityavailable','inventorynumber','greaterthan',0)
		],
		[
			new nlobjSearchColumn('custitem_prolecto_preferredbin'), //this is a custom field on item to a preferred bin pointer 
			new nlobjSearchColumn('binnumber','inventorynumberbinonhand'),
			new nlobjSearchColumn('location','inventorynumberbinonhand'),
			new nlobjSearchColumn('inventorynumber','inventorynumberbinonhand'),
			new nlobjSearchColumn('quantityavailable','inventorynumberbinonhand')
		]

	)
	
	//build a structure that will later be used to help setup preferred bin and lot numbers based on 
	//transactional dates outside this function
	if(s){
		for(var i = 0; i < s.length; i++){
			var itemId = s[i].id;
			var preferredBin = s[i].getValue('custitem_prolecto_preferredbin');
			var binNumber = s[i].getValue('binnumber','inventorynumberbinonhand');
			var locationId = s[i].getValue('location','inventorynumberbinonhand');
			var inventoryNumber = s[i].getValue('inventorynumber','inventorynumberbinonhand');
			var qtyAvailable = Number(s[i].getValue('quantityavailable','inventorynumberbinonhand'));

			if(x[itemId] == null){
				x[itemId] = {};
			}

			if(x[itemId][locationId] == null){
				x[itemId][locationId] = {
					'inventory':{},
					'dates':{},
					'preferred': {
						'bin':preferredBin,
						'inventory':{},
						'dates':{}
					}
				};
			}

			if(x[itemId][locationId].inventory[binNumber]== null){
				x[itemId][locationId].inventory[binNumber] = {};
			}

			if(x[itemId][locationId].inventory[binNumber][inventoryNumber] == null){
				if(preferredBin && preferredBin == binNumber){
					x[itemId][locationId].preferred.inventory[inventoryNumber] = qtyAvailable;
				}else{
					x[itemId][locationId].inventory[binNumber][inventoryNumber] = qtyAvailable;
				}
			}
		}
	}
	return x;
}

function searchLotNumberSequence(itemID, locationID){
	//Search related transaction using Serial/Lot Number Internal IDs for LIFO approach
	var s = nlapiSearchRecord('transaction',null,
		[
			['type','anyof',['ItemRcpt', 'InvAdjst']],'AND',
			['mainline','is','F'],'AND',
			['quantity','greaterthan',0],'AND',
			['itemnumber.location','anyof', locationID],'AND',
			['itemnumber.quantityavailable','greaterthan',0],'AND',
			['item','anyof',itemID]
		],
		[
			new nlobjSearchColumn('item'),
			new nlobjSearchColumn('trandate').setSort(true), //true for LIFO, false for FIFO
			new nlobjSearchColumn('internalid','inventoryDetail'),
			new nlobjSearchColumn('location','inventoryDetail'),
			new nlobjSearchColumn('inventorynumber','inventoryDetail'),
			new nlobjSearchColumn('quantity','inventoryDetail'),
			new nlobjSearchColumn('binnumber','inventoryDetail')
		]
	);

	return (s == null) ? [] : s;
}

Work with NetSuite Experts

In my mind, the real power of the NetSuite system comes together when you take a problem and solve it with the platform. NetSuite offers us so much to start with — but with ingenuity, tenacity, and know-how, you can make a big difference to lower costs and ultimately drive profit. That’s competitive! Perhaps you would like to be part of a team that holds high standards and appreciates senior capacities? If so, let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube