Learn SuiteScript 2.0 Pattern for NetSuite Item Receipts with Lot / Serial Numbers

This article is relevant if you are a NetSuite SuiteScripts 2.0 developer and you need to understand the pattern to work with item receipts and lot or serial numbers.

Background

Our NetSuite Systems Integration Practice is leading a new NetSuite Advanced Manufacturing implementation for a client that seeks to get off the Infor Visual ERP system. When I am asked to develop a strategy to lead a complex implementation, it’s important to break the effort into shorter and simpler efforts. Thus the interim strategy is to integrate purchase orders and item receipts from Infor Visual while we get all the inventory and manufacturing planned, set up and ready to go. Thus, we leveraged a number of our existing tools (available free of license charge to our clients):

1. Apache Camel Integration: an open source middleware framework designed to use queued services to tie systems together. We previously used this framework to integrate scalable FTP services.
2. Prolecto Record Import Export Manager (RIEM): a tool that supercharges hands-free CSV (and other) data imports into NetSuite in a scalable fashion.

The application is targeting the purchase order for lot-controlled raw material goods. NetSuite Lot Control and Serial Numbers (as well as Bins) demand Subrecord management for all item receipt and item fulfillment operations. The architecture of the integration delivers purchase order and item receipt data to a NetSuite Platform using SuiteScript 2.0 to produce the business logic and database work.

SuiteScript 2.0 Code Pattern to Create Lot Based Item Receipts

When I am helping new software developers learn patterns and syntax, I believe it can be very helpful to see an existing set of scripts perform a complete logic operation. We found that NetSuite’s documented SuiteScript 2.0 code examples to work with lot numbers left a few open questions. Thus, I present the following SuiteScript program in my hopes to give you a head-start on your development challenge.

/* 
* NetSuite SuiteScript 2.0 Pattern to illustrate
* 	Purchase Order Transform to Item Receipt (assumes an existing purchase in the database)
* 	Access Item Receipt Lines and update values
* 	Assign Item Receipt Lot Numbers (pattern is very close for serial numbers)
* 
* Thanks to Boris M. and other term members for their years of efforts at Prolecto Resources, Inc.
*/

define(["N/record", "N/format", "N/search", "../utils/Const"],
function(record, format, search, Const) {
    
    // -- RecordHandler as part of Prolecto's Record Import / Export Manager (scalable import processing; available to those that ask)
    function RecordHandler() {
    }
    
    //find Purchase Order via lookup 
    function findPOid(tranid) {
    	var searchObj = search.create({
            type : "purchaseorder",
            filters : [ "tranid", "is", tranid ],
            columns : [ "internalid"]
        });
        
        var searchResult = {};
        searchObj.run().each(function(result) {
            searchResult = {poid : result.getValue(result.columns[0])};
            return true;
        });
        
        if (searchResult.poid) {
        	return searchResult.poid;
        } else {
        	return;
        }
    }
    
    //find Item receipt
    function findIRid(tranid) {
    	var searchObj = search.create({
            type : "itemreceipt",
            filters : [ "tranid", "is", tranid ],
            columns : [ "internalid"]
        });
        
        var searchResult = {};
        searchObj.run().each(function(result) {
            searchResult = {irid : result.getValue(result.columns[0])};
            return true;
        });
        
        if (searchResult.irid) {
        	return searchResult.irid;
        } else {
        	return;
        }
    }
    
    //source data needs to have string data formatted 
    function getDateValue(dateAsString) {
    	try {
    		var trandate = format.parse({
    			value: dateAsString,
    			type: format.Type.DATE
    		});
    	} catch (e) {
    		log.error('IR RecordHandler', 'Parse date error: ' + e.message);
    	}
    	return trandate;
    }
    
    //function to lookup item to assess if it is lot managed
    function lookupItemId(itemid) {
    	var searchObj = search.create({
            type : "item",
            filters : [ "internalid", "is", itemid ],
            columns : [ "internalid", "islotitem"]
        });
        
        var searchResult = {};
        searchObj.run().each(function(result) {
            searchResult = {internalid : result.getValue(result.columns[0]), islotitem: result.getValue(result.columns[1])};
            return true;
        });
        
        if (searchResult.internalid) {
        	log.debug('lookupItemId', 'Found['+ itemid +']:=' + searchResult.internalid + ':lot:' + searchResult.islotitem);
        	return searchResult;
        } else {
        	log.debug('lookupItemId', 'Not Found['+ itemid +']');
        	return;
        }
    }
    
    
    //here is where the RecordHandle does the real work
    //payloadData expects a hot object -- not string.
    RecordHandler.prototype.handle = function(payloadData, isTest) {
    	log.debug('IR RecordHandler', 'Start:' + JSON.stringify(payloadData));
    	var result = {};
    	
	//in this example, our payloadData assumes a shape not show here;  follow along via variable naming
    	try {
    		var header = payloadData[0];
    		var poId = header.purcorderid;
    		var poInternalId = findPOid(poId);
			var irId = header.id; //item receipt
    		
			//only move forward if we found an existing purchase order in NetSuite
    		if (poInternalId) {
    			
			//see if we already gave this item receipt versus creating one
    			var irRecId = findIRid(irId);
    			
    			if (irRecId) {
    				// Reconcile / Update
        			log.debug('IR RecordHandler', 'Update');
        			result.type = 'UPDATE';
        			
        			var irRec = record.load({type: record.Type.ITEM_RECEIPT, id: irRecId});
        			result.tranId = irId;
        			result.recordId = irRecId;
        			// update logic - enter start logic here
					
        			// update logic - end
    			} else {
    				
					// here we need to create an item receipt
					log.debug('IR RecordHandler', 'Insert');
 					
					//primary logic to transform the purchase order to an itme receipt
					var irRec = record.transform({
        				fromType : record.Type.PURCHASE_ORDER,
        				fromId : poInternalId,
        				toType : record.Type.ITEM_RECEIPT
        			});
        			
				//set the item receipt ID as we want it the same as our external system
        			irRec.setValue("tranid", header.id);
        			
				//item receipt data will default today; use the date provided with the payload
				if (header.receiveddate) {
            			    var recDate = getDateValue(header.receiveddate);
            			    if (recDate) {
            			        irRec.setValue("trandate", recDate);
            			}
            		}
        			
        			//we expect lines on the item receipt for everything yet to receive
				var irLineCount = irRec.getLineCount({
        	            sublistId : "item"
        	        });
        			log.debug('IR RecordHandler', 'Line count: ' + irLineCount);
        			
        			// First un-select all as we will manually perform the operation
        			for (var i = 0; i < irLineCount; i++) {
        				irRec.setSublistValue({sublistId: "item", fieldId: "itemreceive", value: false, line : i});
        			}
        			
        			// Second select only matching by payload line; in our case, we have our own custom line number to help
					// us synchronize between the external system
    				for (var j = 0; j < payloadData.length; j++) {
    					
						//hunt for the respective line
    					var lineNumber = irRec.findSublistLineWithValue({
    	                    sublistId: 'item',
    	                    fieldId: 'custcol_cd_vis_line_number',
    	                    value: payloadData[j].purcorderlineno + ""
    	                });
    	                
					//only act if we found the line
    					if (lineNumber != -1) {
    						log.debug('IR RecordHandler', 'LineIndex: ' + lineNumber + ' match payload line: ' + (j+1));
    						
				// act on the item receipt line 
        	                irRec.setSublistValue({sublistId: 'item', fieldId: 'quantity', value: payloadData[j].receivedqty, line : lineNumber});
        	                irRec.setSublistValue({sublistId: 'item', fieldId: 'custcol_cd_vis_line_number', value: payloadData[j].lineno, line : lineNumber});
        	                irRec.setSublistValue({sublistId: "item", fieldId: "itemreceive", value: true, line : lineNumber});
        	                
        	                // get itemid and check if we need to create inventory detail [lot number action]
        	                var itemid = irRec.getSublistValue({sublistId: 'item', fieldId: 'item', line: lineNumber});
        	                log.debug('IR RecordHandler', 'LineIndex: ' + lineNumber + ' item: ' + itemid);
        	                
				var itemIdRef = lookupItemId(itemid);  // our custom function to see if this is lot based
        	                log.debug('IR RecordHandler', 'LineIndex: ' + lineNumber + ' item: ' + itemid + ' : ' + itemIdRef.islotitem);
        	                
							// if this is a lot based item, we need to do the Subrecord work
							if (itemIdRef && itemIdRef.islotitem == true) {
        	                	//in our application, we are defaulting the location based on a constant [may have defaulted from PO]
								irRec.setSublistValue({sublistId: 'item', fieldId: 'location', value: Const.DEFAULT_LOCATION, line : lineNumber});
        	                	
								//since this is an insert, the operation is straight forward; we operate on the inherit line zero; otherwise we need to loop
								var objSubRecord = irRec.getSublistSubrecord({
        	    	                sublistId: 'item',
        	    	                fieldId: 'inventorydetail',
        	    	                line: lineNumber
        	    	            });
        	    				//in this operation, we are going to default the LOT number as 'VISUAL'; any kind of algorithm can be used; 
								objSubRecord.setSublistValue({sublistId: 'inventoryassignment', fieldId: 'quantity', line: 0, value: payloadData[j].receivedqty});
								objSubRecord.setSublistValue({sublistId: 'inventoryassignment', fieldId: 'receiptinventorynumber', line: 0, value: 'VISUAL'});
        	                }
    	                    log.debug('IR RecordHandler','End line');
    					}
    				}
        			
					//finally, save the item receipt and get the resulting ID
        			var irRecId = irRec.save();
        			result.recordId = irRecId;
            		result.tranId = irId;
            		log.debug('IR RecordHandler', 'Success: ' + irId + ' : ' + irRecId);
    			}
    		} else {
    			// We have an external item receipt against a PO that is not yet in NetSuite (your business logic will vary)
    			// appropriate return for Record Import Export Manager to try again
    			log.debug('IR RecordHandler', 'Not PO: ' + poId + ' found for IR: ' + irId);
    			
    			result.tranId = irId;
    			result.skip = true;
    			result.errorMessage = 'No PO:' + poId + 'found.' 
    		}
    	} catch (e) {
    		log.error('IR RecordHandler', e.message);
    		result.isError = true;
    		result.errorMessage = e.message;
    	}
    	log.debug('IR RecordHandler', 'handle end.');
    	return result;
    }
    
    return {
        RecordHandler : RecordHandler
    };
});

Belong to a Professional Team of NetSuite Innovators

The goal of this article is to help you become more powerful in the work you do. If you are passionate about the NetSuite business platform and hold high standards for care and seek to be recognized for your unique ability to listen to concerns so you can turn that challenge into innovative offers for help, then perhaps we should start a conversation about becoming a contributing member of our team.

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