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

This article is relevant if you are seeking to produce purchase orders from a sales order but you need control over which vendors and prices are used when NetSuite’s default rules are not ideal.

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;
		}
	}
}

Take Control over NetSuite and Drive it Your Way

The NetSuite platform is meant to be tailored to fit your business requirements.  The key is to understand how it works by default, how the API works, and then the rest is up to your imagination.  I have two offers.  If you use NetSuite and you want to work with a team of high caliber individuals that can produce the innovations you know are possible, let’s have a conversation on your challenges.  If you are a NetSuite developer who wants to be appreciated for holding high standards of care, let’s have a conversation to see if you are a fit.

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

| Tags: , , , , | Category: Accounting, NetSuite, Technical | 14 Comments

13 Comments

  1. Posted March 20, 2016 at 7:49 pm | Permalink

    Hi Marty, this is a great article in regards to creating a dropship purchase order linked to a sales order but what about the Intercompany dropship process? I would like to automate the creation of the Paired intercompany Sales Order along with the dropship Purchase Order, it seems that I cannot set the fields intercostatus and intercotransaction I always get a currency error and I don’t know the intercostatus value translations either. Any help would be much appreciated.

  2. Posted April 5, 2016 at 3:44 am | Permalink

    Hi Daniel,

    The truth is that you need to keep playing with the dynamic values to get this to work. I haven’t tried the intercompany one. But in theory, if you can reproduce via the UI, we should be able to get to it via the API. intercompany orders may have some dependencies on certain control customers / vendors that may make it impossible. Do let us know what you find.

    Marty

  3. Christine Bergold
    Posted May 24, 2016 at 8:25 am | Permalink

    Hi Marty, Is their a way to attach credit memos to a sales order? Ideally, we would like to show a running balance of invoiced items and sales order amounts to show how much is left to invoice.

  4. Posted May 25, 2016 at 4:18 am | Permalink

    Hi Christine,

    Credit memos are typically attached to Return Authorizations which often are generated from a Sales Order. Or the Credit Memos may be generated from an Invoice which again may have been generated from a Sales Order. This data relationship is a bit distant for NetSuite to search due to one hop. You might be able to target all open invoices / credit memos and bring in credit memos in one search. However, using our CRE tool, we could easily get the data via multiple searches, if needed, and summarize. See this article for a primer on the tool: http://blog.prolecto.com/2015/06/01/supercharge-netsuite-advanced-pdfhtml-templates/

  5. Denny
    Posted June 1, 2016 at 2:50 am | Permalink

    Hi marty,
    Great article. I specifically interested in this part

    var new_po = nlapiCreateRecord(‘purchaseorder’,
    { customform:CONST_FORM_PURCH,
    soid:tran_id,
    shipgroup:”1″,
    dropship:”T”,
    custid:customer_id,
    entity:vendor_id } );

    In my case it doesn’t copy over the line item to the new PO, and when i saved that PO, the created from is empty.

    Did i miss something ?
    Thanks

  6. Posted June 3, 2016 at 6:57 pm | Permalink

    Hello Denny,

    Did you replace the values tran_id, customer_id, and vendor_id with the proper integer references?

    Marty

  7. Jordan
    Posted September 23, 2016 at 4:50 am | Permalink

    Hi Marty,

    Like Denny, i am also interested in this piece of code:

    var new_po = nlapiCreateRecord(‘purchaseorder’,{ customform:CONST_FORM_PURCH,
    soid:tran_id,
    shipgroup:”1″,
    dropship:”T”,
    custid:customer_id,
    entity:vendor_id } );

    This was the first time i have seen these initialisation values. Do you know of a resource with all the possible initialisation properties?

    I am looking for suitable properties for creating a IC SO from a IC PO.

    Regards,
    Jordan

  8. Denny
    Posted September 25, 2016 at 8:31 pm | Permalink

    Hi marty,
    Just managed to reply now, no notification popup in my email, not sure why 😀
    There is 1 properties missing in the example, here is the working piece of code for me. The poentity is missing

    var newPo = nlapiCreateRecord(‘purchaseorder’,{
    customform:78,
    soid:soId,
    shipgroup:”1″,
    dropship:”T”,
    custid:customerId,
    entity:vid,
    poentity:vid,
    });

  9. Taras
    Posted April 11, 2017 at 6:02 am | Permalink

    Hello Marty. Your blog is very useful. Thanks a lot for your great job!

    Question:

    var newPo = nlapiCreateRecord(‘purchaseorder’,{
    customform:78,
    soid:soId,
    shipgroup:”1″,
    dropship:”T”,
    custid:customerId,
    entity:vid,
    poentity:vid,
    });

    How can i do the same with netsuite phptoolkit?
    I mean add items to Sales Order and generate Purchase Order(s) with items linked bw SO and PO.
    Thank You!

  10. Posted April 12, 2017 at 4:54 am | Permalink

    I haven’t worked with the phptoolkit. Yet I understand that the toolkit is really working with Web Services behind the scenes. Thus, the key is to understand if you can indeed send in default parameters during the record create function. I suspect these parameters could be set during your add or upsert operation. But I haven’t tried to confirm we can get the results that we have from SuiteScript.

    Marty

  11. Taras
    Posted April 12, 2017 at 5:56 am | Permalink

    Thanks a lot. Unfortunately i could not find this neither in phptoolkit docs nor in phptoolkit sources.

    I have another question now. Where i can find all the list of this parameters?

    And one more. I’ve created SO. Later I had to add two items in it. Both are of the same preferred vendor. But first with createPo=”_dropShipMent” and the second with createPo=”_specialOrder”. I want separate POs for each of this items. How can i do it using the code above (nlapiCreateRecord….). Or any othe way.
    Thanks beforehand.

  12. Posted April 13, 2017 at 5:00 am | Permalink

    We had to experiment to find the parameters. Basically, study the URL in the UI to learn how NetSuite is passing parameters around.

    This article demonstrates how to create multiple purchase orders through the mechanism of looping and throwing away lines. You should be able to distinguish line elements to know if you should be keeping or deleting the NetSuite generated line items. You then iterate until all your purchase orders are generated to your satisfaction.

  13. Taras
    Posted April 14, 2017 at 2:57 am | Permalink

    Yes in url for creating PO from SO you can find all that options (soid, custid,entity and so). But i could not find parameter, which allows to pass item line numbers.(

    I did it that way. Add one item to so. Update so (which hooks userevent with this part with nlapiCreateRecord…). Then add another item and again update SO (and same userevent script). Works like a charm. But i thought maybe there’s more accurate way.

One Trackback

  1. […] Accrual process and it will automatically create a linked purchase order.  See my article, “Learn the SuiteScript Pattern to Generate Target Purchase Orders from Sales Orders”,  on the power of generating your own related purchase orders from Sales […]

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>