netsuite_autobill_sales_order.js

## netsuite_autobill_sales_order.js
/*
Author: <mike@suitesync.io>
Description: Automatically bills sales orders (creates invoices) when the deposit
             total is enough to pay the order. This ensures the sales order is only
             billed when the resulting invoice would be paid in full.
Link: https://gist.github.com/iloveitaly/eb3fffa67c5ea08010d3de6e552f84d3

Installation:
  1. https://system.sandbox.netsuite.com/app/common/scripting/uploadScriptFile.nl
  2. User Event
  3. Name: Copy Order Fields to Transaction
  4. ID: _suitesync_copy_invoice_fields
  5. After Submit: afterSubmit
  6. Deployments: CustomerDeposit on Create, or CustomerPayment on Create

*/

// Start Utilities
// https://gist.github.com/iloveitaly/db7d532e772b67f5b81d0199d094301f

function isZero(obj) {
  return parseFloat(obj) == 0.0
}

function log(msg) {
  nlapiLogExecution('DEBUG', msg);
}

function error(msg) {
  nlapiLogExecution('ERROR', msg);
}

function isEmpty(obj) {
  return obj === undefined || obj === null || obj === "";
}

function absoluteDifference(a, b) {
  return Math.abs(parseFloat(a) - parseFloat(b))
}

function isScriptUsageRemaining() {
  var estimatedUsagePerIteration = 100;
  var scriptContext = nlapiGetContext();

  if(scriptContext.getExecutionContext() != 'scheduledScript') {
    log("execution context is not scheduled script, assuming true");
    return true;
  }

  var hasUsageRemaining = scriptContext.getRemainingUsage() > estimatedUsagePerIteration;

  if(!hasUsageRemaining) {
    log("script terminating, not enough usage remaining");
    return false;
  }

  return true;
}

function rescheduleScript() {
  var scriptContext = nlapiGetContext();

  // TODO look into nlapiYieldScript, be aware of serialization limitations before implementing!
  // https://stackoverflow.com/questions/38859322/how-to-prevent-execution-usage-limit-in-scheduled-scripts?rq=1

  var schStatus = nlapiScheduleScript(scriptContext.getScriptId(), scriptContext.getDeploymentId());
}

// End Utilities

function scheduled(type) {
  if(type != 'scheduled') return;

  processOpenSalesOrders();
}

function processOpenSalesOrders() {
  var filters = [
    // NOTE that only "Pending Billing" and "Sales Order:Pending Billing/Partially Fulfilled" are processed
    //      you may need to change

    // http://blog.prolecto.com/2013/08/30/netsuite-searchfilter-transaction-internal-status-list/
    // F = Pending Billing; E = Pending Billing/Partially Fulfilled
    new nlobjSearchFilter( 'status', null, 'anyOf', ['SalesOrd:F', 'SalesOrd:E']),
    new nlobjSearchFilter( 'mainline', null, 'is', 'T'),

    // NOTE limit search to records modified in the last 10 ten days to avoid picking up "bad" records
    //      i.e. records that can never be billed for one reason or another
    new nlobjSearchFilter( 'lastmodifieddate', null, 'after', 'tenDaysAgo')
  ];

  var search = nlapiSearchRecord('salesorder', null, filters, []);

  if(isEmpty(search)) {
    log("no search results");
    return;
  }

  for(var i = 0; i < search.length; i++) {
    if(!isScriptUsageRemaining()) {
      rescheduleScript();
      break;
    }

    var salesOrderId = search[i].getId();
    processSalesOrder(salesOrderId);
  }
}

function processSalesOrder(salesOrderInternalId) {
  log("processing order: " + salesOrderInternalId);

  var salesOrderTotal = nlapiLookupField('salesorder', salesOrderInternalId, 'total');
  var depositTotal = getCustomerDepositTotalForSalesOrder(salesOrderInternalId, null);

  // NOTE because of JS FPA arithemtic errors we treat less than 0.02 differences as equal
  if(parseFloat(depositTotal) >= parseFloat(salesOrderTotal) || absoluteDifference(depositTotal, salesOrderTotal) < 0.02) {
    billSalesOrder(salesOrderInternalId);
  } else {
    log("sales order does not have enough deposits: " + salesOrderInternalId + " deposit: " + depositTotal
				+ " salesOrderTotal: " + salesOrderTotal)
  }
}

// NOTE should be used after the caller is sure the SO has not been billed
// http://blog.prolecto.com/2015/10/11/use-netsuite-suitescript-to-automate-posting-customer-deposits-to-invoices/
// https://usergroup.netsuite.com/users/forum/platform-areas/customization/suitescript-custom-code/331641-returning-value-from-existing-saved-search
// https://usergroup.netsuite.com/users/forum/platform-areas/customization/suitescript-custom-code/421798-detecting-total-deposits-on-a-salesorder
function getCustomerDepositTotalForSalesOrder(salesOrderId, entityId) {
  // NOTE if a CustomerDeposit is created from a SalesOrder it is not possible to apply it against
  //      an invoice until it's been billed. This means that all deposit associated with a SalesOrder
  //      are an accurate representation of the total amount pre-paid

  // NOTE if a SalesOrder is billed it cannot be re-billed: if the amount is edited and the price increases
  //      a invoice or other record must be created to account for the additional billed amount

  if(isEmpty(entityId)) {
    entityId = nlapiLookupField('salesorder', salesOrderId, 'entity');
  }

  // if deposit balance is zero then there are no deposits on the user
  var depositBalance = nlapiLookupField('customer', entityId, 'depositbalance');
  if(isEmpty(depositBalance) || isZero(depositBalance)) {
    return 0;
  }

  var filters = [
    new nlobjSearchFilter('salesorder', null, 'anyof', salesOrderId)
  ];

  var columns = [
    new nlobjSearchColumn('total', null, 'sum')
  ];

  var results = nlapiSearchRecord(
    'customerdeposit',
    null,
    filters,
    columns
  );

  if(!results) {
    log("no deposits found for record: " + salesOrderId)
    return 0;
  }

  if(results.length > 1) {
    error("summary result should only have a single result")
  }

  return results[0].getValue('total', null, 'sum');
}

function billSalesOrder(salesOrderInternalId) {
  log("billing sales order: " + salesOrderInternalId);

  try {
    var billRecord = nlapiTransformRecord('salesorder', salesOrderInternalId, 'invoice');
  } catch(e) {
    error("error converting sales order to invoice: " + salesOrderInternalId);
  }

  try {
    nlapiSubmitRecord(billRecord);
  } catch (e) {
    error("error creating sales order: " + salesOrderInternalId)
  }
}

if(nlapiGetContext().getExecutionContext() == 'debugger') {
  processOpenSalesOrders();
}

// allows you set a breakpoint at the end of the script
'debug';