Learn the SuiteScript Pattern to Generate Target Purchase Orders from Sales Orders

Background

We have clients that are in industries that use both drop ship purchase orders and third party assembly operations. In many of these operations, the sales process is sufficiently complicated where every sales order requires consideration of one or more suppliers that will ultimately produce (an element of) the fulfillment. When creating the sales order, the target vendor is identified including the purchase order price for the item that will be delivered to the customer.

NetSuite has a great feature to generate purchase orders (typically drop shipped or special order) based on the line items that are on a sales order. The challenge is that the information on the line to generate a purchase order is based on the item record and may not be what you intend. NetSuite wants to use the preferred item purchase vendor and related price to act as defaults to automatically drive the creation of drop shipped / special order purchase orders.

In our use case, we do not want to use those “defaults”. Instead, we need to take control and target the information we want on the related purchase order(s).

Pattern to Target Purchase Orders from the Sales Orders

The first thing that is needed is two custom transaction columns which will be part of the Sales Order line definition:

1. Target Vendor: link to a vendor that will supply the good / service.
2. Target Price: price agreed to be paid to the vendor

Of course, more information can be captured on the line that will drive the behavior of the target purchase order. Next, instead of using NetSuite’s automatic purchase order generation features, we will instead produce the purchase orders after the Sales Order is committed via SuiteScript.

NetSuite SuiteScript Code Pattern to Generate Linked Purchase Orders from a Sales Order

The innovation takes advantage of some powerful NetSuite API capacities that are not well known or understood. In this case, we are going to create a drop ship purchase order and provide a target vendor and linked sales order under our control. NetSuite will “overreach” and assume that all of our sales order lines are to be fulfilled by a single vendor.  We will review all the lines on the purchase order and remove the ones that are not supposed to be supplied by the vendor. We will keep doing this for all the sales order lines and related vendors in question until we are satisfied.

Here is the code pattern:

function prolecto_SalesOrder_AfterSubmit(type) {
	//note, code has been adapted from working application and is for educational purposes only
	nlapiLogExecution('AUDIT', 'prolecto_SalesOrder_AfterSubmit starting', type);

	//only act on relevant records
	if (type=='delete') return;
	if (!(type=='create' || type=='edit' || type=='approve')) return;

	var CONST_FORM_PURCH = "98";  // 98 is standard po

	var tran_id = nlapiGetRecordId();
	var order = nlapiLoadRecord('salesorder', tran_id);
	var customer_id = order.getFieldValue('entity');
	nlapiLogExecution('DEBUG', 'salesorder:'+tran_id + ' customerid:'+customer_id, 'customform:'+CONST_FORM_PURCH);

	// get the order so we can create a unique list of target vendors
	var vendor_array = new Array();
	var lines = order.getLineItemCount('item');
	for (var l=1; l<=lines; l++){
		//note, we have a custom column on the transaction line to hold the target vendor
		var vendor = order.getLineItemValue('item', 'custcol_prolecto_vendor', l).toString();

		if (vendor.length > 0){
			vendor = '_' + vendor; //helpful to ensure that our array is not using numbers
			if (!(vendor in vendor_array)){
				vendor_array[vendor] = "1";
			}
		}
	}
	// create the purchase orders; our goal is to create one PO for each vendor
	// using the create PO syntax with parameters for special order will link it to the sales order;
	// by default, netSuite will link all items to the a vendor when the po is initially created.  Not quite right
	// We will delete incorrectly placed vendor rows and keep creating another po until we have a po for all vendors in play

	// produce an array list vendors
	var keys = Object.keys(vendor_array);
	var olen = keys.length;
	nlapiLogExecution('DEBUG', 'vendor array list length', olen);

	if (olen > 0){
		for (var o = 0; o < olen; o++){
			var key = keys[o];
			var vendor_id = key.substring(1); //remove the '_';
			nlapiLogExecution('DEBUG', o + '. vendor vendor_id:'+vendor_id);

			// this is the special syntax to setup the po with a target vendor and link it to the SO
			var new_po = nlapiCreateRecord('purchaseorder',
				{ customform:CONST_FORM_PURCH,
				soid:tran_id,
				shipgroup:"1",
				dropship:"T",
				custid:customer_id,
				entity:vendor_id } ); 

			// now delete the lines that are not part of the target vendor

			// as each po is created, it has one less vendor than the previous one for the one that already was created
			// therefore we can delete the lines that don't match the vendor or if the vendor is empty
			var num_lines = new_po.getLineItemCount('item');
			nlapiLogExecution('DEBUG', 'analyze vendors num_lines:' + num_lines);

			for (var d = num_lines; d >= 1; d--){
				var vendortarget = new_po.getLineItemValue('item', 'custcol_prolecto_vendor', d).toString();
				if (vendortarget.length == 0 || vendortarget != vendor_id){
					//throw away the line; this target vendor is not in the current PO; we'll get it next time around
					new_po.removeLineItem('item', d);
				} else {
					// right vendor; set the correct target rate as indicated on our sales order line
					var rate = new_po.getLineItemValue('item', 'custcol_prolecto_item_rate', d);
					new_po.setLineItemValue('item', 'rate', d, rate);
					}
				}
			}

			//commit the work
			var num_lines = new_po.getLineItemCount('item');
			if (num_lines > 0){
				var po_id = nlapiSubmitRecord(new_po, false, true);
				nlapiLogExecution('DEBUG', 'created a po', po_id);
			}
			new_po = null;
		}
	}
}