Instantly share code, notes, and snippets.

Embed
What would you like to do?
Automatically bill NetSuite sales orders when the deposit total is enough to pay the order
/*
Author: <mike@suitesync.io>
Description: Automatically bills sales orders (creates invoices) when the deposit
total is enough to pay the order.
Link: https://gist.github.com/iloveitaly/eb3fffa67c5ea08010d3de6e552f84d3/edit
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 scriptContext = nlapiGetContext();
if(scriptContext.getExecutionContext() != 'scheduledScript') {
log("execution context is not scheduled script, assuming true");
return true;
}
var hasUsageRemaining = scriptContext.getRemainingUsage() < 100;
if(!hasUsageRemaining) {
log("script terminating, not enough usage remaining");
return false;
}
return true;
}
function rescheduleScript() {
var scriptContext = nlapiGetContext();
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/
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';
@n0n0n0GH

This comment has been minimized.

Show comment
Hide comment
@n0n0n0GH

n0n0n0GH Jun 6, 2018

Hi Iloveitaly,

Thank you for sharing your hardwork. I'm a Netsuite user who is trying to streamline a daily repetitive task. I'm trying to understand your script so that I can apply to my situation. It seem that this script is one step ahead of what I'm trying to do. Basically, I'm trying to create invoices base on sales orders that have "pending bill" status before any customer deposits. Will you please give me some directions/advises?

Thank you for your time.

n0n0n0GH commented Jun 6, 2018

Hi Iloveitaly,

Thank you for sharing your hardwork. I'm a Netsuite user who is trying to streamline a daily repetitive task. I'm trying to understand your script so that I can apply to my situation. It seem that this script is one step ahead of what I'm trying to do. Basically, I'm trying to create invoices base on sales orders that have "pending bill" status before any customer deposits. Will you please give me some directions/advises?

Thank you for your time.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment