Automatically bill NetSuite sales orders when the deposit total is enough to pay the order. http://SuiteSync.io/
/* | |
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'; |
This comment has been minimized.
This comment has been minimized.
@n0n0n0GH if you are a SuiteSync (http://suitesync.io) user, reach out to support at support@suitesync.io. Otherwise, I'd recommend you checkout the OpenSuite community for answers to this sort of question: |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
n0n0n0GH commentedJun 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.