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.

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's 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 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to setup a conversation.

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

| Category: Accounting, NetSuite, Technical | 5 Comments

5 Comments

  1. Ashish
    Posted March 19, 2018 at 7:22 am | Permalink

    Hi Marty,
    This is wonderful. I have two questions. Let’s say if I have to update Payment status from 3rd Party, I can do it using Scheduled Script. Is it possible in User Event Script? [I was thinking a mix of both.].

    Second one is , if I have to develop a suiteApp which interacts with 3rd party API’s, there are 3 approaches, one is to call API from Netsuite and everything sits in Netsuite, 2nd is to call using restlet and 3rd is using SOAP. However, reslet and SOAP is pain for the setup (going through multiple steps) compared to calling API’s from Netsuite(using N/http module). Let me know if my understanding is correct.

  2. Jagedeesan S
    Posted March 22, 2018 at 5:23 am | Permalink

    Dear Sir,

    Is this script will be deployed as User event or Client event … Then where i deploy the script

    Thanks,
    Jagedeesan.

  3. Posted March 24, 2018 at 3:26 pm | Permalink

    Hello Ashish,

    These are complex questions and off topic from the article. You have all kinds of options when working with the platform but there are important decisions to make. I recommend sending me a note here for more conversation.

    Marty

  4. Posted March 24, 2018 at 3:27 pm | Permalink

    In this article, this is set server side.

  5. no3
    Posted May 6, 2018 at 6:26 pm | Permalink

    i am currently working on a restlet integration using suitescript2.
    based on the code i can improve the structure for this.
    question.
    have you tried to fulfill or receive a transaction with default lot number using script2?
    ive tried to remove the inventory detail on it but i fail. using script2

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>