Jira Code: UMAR-107, 109
Create an invoice, bill, commission invoice using a custom button from the custom record. Invoice is creating by transforming the SO and the bill is creating by transforming the PO. But recalculating the base values as per the current rate on creation. And sending the response to the client script once the creation is successful.
/**
* @NApiVersion 2.x
* @NScriptType Suitelet
* @NModuleScope SameAccount
*/
/**
* Script Description: This script for Creating the invoice, bill, commision invoice
*/
/*******************************************************************************
* * UMAR WSR * *
* **************************************************************************
* Date:13/2/18
* Script name: NetU SL Create Flow Invoice
* Script id: customscript_netu_sl_create_flow_invoice
* Deployment id: customdeploynetu_sl_create_flow_invoice
******************************************************************************/
define(['N/search', 'N/record', 'N/format', 'N/https', 'N/url', 'N/redirect'],
function(search, record, format, https, url, redirect) {
function checkif(singleitem) {
if (singleitem == "" || singleitem == null ||
singleitem == undefined) {
return 0;
} else {
return singleitem;
}
}
/**
* Definition of the Suitelet script trigger point.
*
* @param {Object} context
* @param {ServerRequest} context.request - Encapsulation of the incoming request
* @param {ServerResponse} context.response - Encapsulation of the Suitelet response
* @Since 2015.2
*/
function onRequest(context) {
try {
var recId = context.request.parameters.recId;
var invoiceType = context.request.parameters.type;
var subjobStatus = context.request.parameters.status;
var st = null;
//creating invoices or bill based on invoice type
if (invoiceType == 1) {
var orderId = [];
var type = ['purchaseorder', 'salesorder'];
var filtertype = ['PurchOrd', 'SalesOrd'];
for (var f = 0; f < 2; f++) {
var purchaseorderSearchObj1 = search.create({
type: type[f],
filters: [
["type", "anyof", filtertype[f]],
"AND",
["custbody_netu_subjob", "anyof", recId],
"AND",
["mainline", "is", "T"]
],
columns: [
search.createColumn({ name: "internalid", label: "Internal ID" })
]
});
var searchResultCount = purchaseorderSearchObj1.runPaged().count;
if (searchResultCount != 0) {
var Purchaseresults = purchaseorderSearchObj1.run().getRange({
start: 0,
end: 1
});
orderId[f] = Purchaseresults[0].getValue({
name: 'internalid'
});
}
}
createSalesInvoice(orderId[1], context, orderId[0], subjobStatus, recId);
} else if (invoiceType == 2) {
var purchaseorderSearchObj1 = search.create({
type: "purchaseorder",
filters: [
["type", "anyof", 'PurchOrd'],
"AND",
["custbody_netu_subjob", "anyof", recId],
"AND",
["mainline", "is", "T"]
],
columns: [
search.createColumn({ name: "internalid", label: "Internal ID" })
]
});
var searchResultCount = purchaseorderSearchObj1.runPaged().count;
if (searchResultCount != 0) {
var Purchaseresults = purchaseorderSearchObj1.run().getRange({
start: 0,
end: 1
});
poNo = Purchaseresults[0].getValue({
name: 'internalid'
});
}
createBill(poNo, context, subjobStatus, recId);
} else if (invoiceType == 3) {
//finding the corresponding po and so.
var orderId = [];
var type = ['purchaseorder', 'salesorder'];
var filtertype = ['PurchOrd', 'SalesOrd'];
for (var f = 0; f < 2; f++) {
var purchaseorderSearchObj1 = search.create({
type: type[f],
filters: [
["type", "anyof", filtertype[f]],
"AND",
["custbody_netu_subjob", "anyof", recId],
"AND",
["mainline", "is", "T"]
],
columns: [
search.createColumn({ name: "internalid", label: "Internal ID" })
]
});
var searchResultCount = purchaseorderSearchObj1.runPaged().count;
if (searchResultCount != 0) {
var Purchaseresults = purchaseorderSearchObj1.run().getRange({
start: 0,
end: 1
});
orderId[f] = Purchaseresults[0].getValue({
name: 'internalid'
});
}
}
createCommissionInvoice(orderId[0], orderId[1], context, subjobStatus, recId);
}
/* if (subjobStatus == 17) {
changeStatus(recId, st);
}
*/
} catch (e) {
log.debug({
title: e.name,
details: e.message
});
}
}
return {
onRequest: onRequest
};
// Function to change the sub job status in all related records
function changeStatus(subjobId, st) {
try {
var subJobRecord = record.load({
type: 'customrecord_netu_sub_job',
id: subjobId,
isDynamic: true
});
record.submitFields({
type: 'customrecord_netu_sub_job',
id: subjobId,
values: {
'custrecord_netu_subjob_status': st
}
});
var reqId = subJobRecord.getSublistValue({
sublistId: 'recmachcustrecord_netu_sub_job_number',
fieldId: 'id',
line: 0
});
//Change the Sub-job status field value in Purchase Requisition
record.submitFields({
type: 'customrecord_netu_purchase_requisition',
id: reqId,
values: {
'custrecord_netu_req_sub_job_status': st
}
});
var transactionSearchObj = search.create({
type: "transaction",
filters: [
["custbody_netu_subjob", "anyof", subjobId],
"AND",
["mainline", "is", "T"]
],
columns: [
"internalid",
"tranid",
"recordtype",
"custbody_netu_purchase_requisition",
search.createColumn({
name: "internalid",
join: "CUSTBODY_NETU_PURCHASE_REQUISITION"
})
]
});
var transsearchResultCount = transactionSearchObj.runPaged().count;
var transDtls = transactionSearchObj.run().getRange({
start: 0,
end: 1000
});
for (var j = 0; j < transsearchResultCount; j++) {
var recType = transDtls[j].getValue({
name: 'recordtype'
});
var recId = transDtls[j].getValue({
name: 'internalid'
});
var recMap = {
estimate: record.Type.ESTIMATE,
salesorder: record.Type.SALES_ORDER,
purchaseorder: record.Type.PURCHASE_ORDER
}
record.submitFields({
type: recMap[recType],
id: recId,
values: {
'custbody_netu_subjob_status': st
}
});
}
} catch (e) {
log.debug({
title: e.name,
details: e.message
});
}
}
//creating sales invoice
function createSalesInvoice(so_id, context, po_no, subjobStatus, recId) {
try {
// transform SO to invoice
var rec = record.transform({
fromType: record.Type.SALES_ORDER,
fromId: so_id,
toType: record.Type.INVOICE,
isDynamic: true
});
rec.setValue({
fieldId: 'customform',
value: '155'
});
rec.setValue({
fieldId: 'custbody_netu_subjob_status',
value: 18
});
var subjobNo = rec.getValue({
fieldId: 'custbody_netu_subjob'
});
var purchorderdtls = search.lookupFields({
type: record.Type.PURCHASE_ORDER,
id: po_no,
columns: ['custbody_netu_invoiced_customer', 'custbody_netu_invoicing_company']
});
var invoCust = null;
if (purchorderdtls.custbody_netu_invoiced_customer[0] != null) {
invoCust = purchorderdtls.custbody_netu_invoiced_customer[0].value;
rec.setValue({
fieldId: 'custbody_netu_invoiced_customer',
value: invoCust
});
}
var invoCmpny = null;
if (purchorderdtls.custbody_netu_invoicing_company[0] != null) {
invoCmpny = purchorderdtls.custbody_netu_invoicing_company[0].value;
rec.setValue({
fieldId: 'custbody_netu_invoicing_company',
value: invoCmpny
});
}
//getting and setting the freight amount with freight(logistics) if it is not empty. Otherwise setting from header.
var subjobDtls = search.lookupFields({
type: 'customrecord_netu_sub_job',
id: subjobNo,
columns: ['custrecord_netu_freight_charge_logistics']
});
log.debug('subjobDtls.custrecord_netu_freight_charge_logistics', subjobDtls.custrecord_netu_freight_charge_logistics);
if (subjobDtls.custrecord_netu_freight_charge_logistics != null) {
freightLO = subjobDtls.custrecord_netu_freight_charge_logistics;
}
freightLO = checkif(freightLO);
log.debug('freightLO', freightLO);
if (freightLO != 0) {
rec.setValue({
fieldId: 'custbody_netu_freight',
value: freightLO
});
var netAmt = rec.getValue({
fieldId: 'custbody_netu_sales_net_amount'
});
var pack = rec.getValue({
fieldId: 'custbody_netu_packing_charges'
});
var afetrchrege = parseFloat(netAmt) + parseFloat(pack) + parseFloat(freightLO);
rec.setValue({
fieldId: 'custbody_netu_sales_net_amnt_charges',
value: afetrchrege
});
var count = rec.getLineCount({
sublistId: 'item'
});
var line = rec.selectLine({
sublistId: 'item',
line: count - 2
});
//inserting a new line.
rec.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'rate',
value: freightLO,
ignoreFieldChange: true
});
rec.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'amount',
value: freightLO,
ignoreFieldChange: true
});
rec.commitLine({
sublistId: 'item'
});
}
var exRate = rec.getValue({
fieldId: 'exchangerate'
});
var salesfields = ['custbody_netu_sales_gross_amount',
'custbody_netu_sales_discount_amount',
'custbody_netu_sales_net_amount',
'custbody_netu_freight',
'custbody_netu_packing_charges',
'custbody_netu_sales_net_amnt_charges',
'custbody_netu_cost_net_amnt_charges'
];
var setSalesFields = [
'custbody_netu_sales_gross_amount_b',
'custbody_netu_sales_discount_amount_b',
'custbody_netu_sales_net_amount_b',
'custbody_netu_freight_b',
'custbody_netu_packing_charges_b',
'custbody_netu_sales_net_amnt_charges_b',
'custbody_netu_cost_net_amnt_charges_b'
];
for (var j = 0; j < 7; j++) {
var values = rec.getValue({
fieldId: salesfields[j]
});
values = checkif(values);
var setvalues = parseFloat(values) *
parseFloat(exRate);
rec.setValue({
fieldId: setSalesFields[j],
value: setvalues
});
}
rec.setValue({
fieldId: 'custbody_netu_view_mode',
value: true
});
var recordId = rec.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
// var strObj = { id: '1' };
var recordUrl = url.resolveRecord({
recordType: record.Type.INVOICE,
recordId: recordId,
isEditMode: true
});
var response = {
recordurl: recordUrl
}
if (recordId) {
st = 18;
if (subjobStatus == 17) {
changeStatus(recId, st);
}
}
context.response.write(JSON.stringify(response));
} catch (e) {
var response = {
errorMessage: e.message
}
context.response.write(JSON.stringify(response));
log.debug({
title: e.name,
details: e.message
});
}
}
//creating bill
function createBill(po_id, context, subjobStatus, recId) {
try {
var rec = record.transform({
fromType: record.Type.PURCHASE_ORDER,
fromId: po_id,
toType: record.Type.VENDOR_BILL,
isDynamic: true
});
var exRate = rec.getValue({
fieldId: 'exchangerate'
});
var subjobNo = rec.getValue({
fieldId: 'custbody_netu_subjob'
});
log.debug('subjobNo', subjobNo);
rec.setValue({
fieldId: 'custbody_netu_subjob_status',
value: 18
});
//getting and setting the freight amount with freight(logistics) if it is not empty. Otherwise setting from header.
var subjobDtls = search.lookupFields({
type: 'customrecord_netu_sub_job',
id: subjobNo,
columns: ['custrecord_netu_freight_charge_logistics']
});
log.debug('subjobDtls.custrecord_netu_freight_charge_logistics', subjobDtls.custrecord_netu_freight_charge_logistics);
if (subjobDtls.custrecord_netu_freight_charge_logistics != null) {
freightLO = subjobDtls.custrecord_netu_freight_charge_logistics;
}
freightLO = checkif(freightLO);
log.debug('freightLO', freightLO);
if (freightLO != 0) {
rec.setValue({
fieldId: 'custbody_netu_v_freight',
value: freightLO
});
var netAmt = rec.getValue({
fieldId: 'custbody_netu_cost_net_amount'
});
var pack = rec.getValue({
fieldId: 'custbody_netu_v_packing_charges'
});
var afetrchrege = parseFloat(netAmt) + parseFloat(pack) + parseFloat(freightLO);
rec.setValue({
fieldId: 'custbody_netu_cost_net_amnt_charges',
value: afetrchrege
});
var count = rec.getLineCount({
sublistId: 'item'
});
var line = rec.selectLine({
sublistId: 'item',
line: count - 2
});
//inserting a new line.
rec.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'amount',
value: freightLO,
ignoreFieldChange: true
});
rec.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'amount',
value: freightLO,
ignoreFieldChange: true
});
rec.commitLine({
sublistId: 'item'
});
}
// setting cost and sales base values
var fields = ['custbody_netu_cost_gross_amount',
'custbody_netu_cost_discount_amount',
'custbody_netu_cost_commission_amount',
'custbody_netu_cost_net_amount',
'custbody_netu_v_packing_charges',
'custbody_netu_v_freight',
'custbody_netu_cost_net_amnt_charges'
];
var setFields = ['custbody_netu_cost_gross_amount_base',
'custbody_netu_cost_discount_amount_b',
'custbody_netu_cost_commission_amount_b',
'custbody_netu_cost_net_amount_b',
'custbody_netu_v_packing_charges_b',
'custbody_netu_v_freight_b',
'custbody_netu_cost_net_amnt_charges_b'
];
for (var i = 0; i < 7; i++) {
var value = rec.getValue({
fieldId: fields[i]
});
value = checkif(value);
var setvalue = parseFloat(value) * parseFloat(exRate);
rec.setValue({
fieldId: setFields[i],
value: setvalue
});
}
rec.setValue({
fieldId: 'custbody_netu_view_mode',
value: true
});
var recordId = rec.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
var recordUrl = url.resolveRecord({
recordType: record.Type.VENDOR_BILL,
recordId: recordId,
isEditMode: true
});
var response = {
recordurl: recordUrl
}
if (recordId) {
st = 18;
if (subjobStatus == 17) {
changeStatus(recId, st);
}
}
context.response.write(JSON.stringify(response));
} catch (e) {
var response = {
errorMessage: e.message
}
context.response.write(JSON.stringify(response));
log.debug({
title: e.name,
details: e.message
});
}
}
//creating commission invoice via get and set
function createCommissionInvoice(po_id, so_id, context, subjobStatus, recId) {
try {
var purchaseorderSearchObj2 = search.create({
type: "purchaseorder",
filters: [
["type", "anyof", "PurchOrd"],
"AND",
["internalidnumber", "equalto", po_id],
"AND",
["mainline", "is", "T"]
],
columns: [
search.createColumn({ name: "location", label: "Location" }),
search.createColumn({ name: "class", label: "Class" }),
search.createColumn({ name: "custbody_netu_customer", label: "Customer" }),
search.createColumn({ name: "custbody_netu_vessel", label: "Vessel" }),
search.createColumn({ name: "custbody_netu_subjob", label: "Sub Job Number" }),
search.createColumn({ name: "department", label: "Department" }),
search.createColumn({ name: "custbody_netu_cost_gross_amount", label: "Cost Gross Amount" }),
search.createColumn({ name: "custbody_netu_v_discount_perc", label: "V.Discount %" }),
search.createColumn({ name: "custbody_netu_cost_discount_amount", label: "Cost Discount Amount" }),
search.createColumn({ name: "custbody_netu_v_commission_perc", label: "V.Commission %" }),
search.createColumn({ name: "custbody_netu_v_add_commission_perc", label: "V.Additional Commission %" }),
search.createColumn({ name: "custbody_netu_cost_commission_amount", label: "Cost Commission Amount" }),
search.createColumn({ name: "custbody_netu_cost_net_amount", label: "Cost Net Amount" }),
search.createColumn({ name: "custbody_netu_invoiced_customer", label: "Invoiced Customer" }),
search.createColumn({ name: "custbodynetu_invoiced_owner", label: "Invoiced Owner" }),
search.createColumn({ name: "custbody_netu_main_job", label: "Main Job" }),
search.createColumn({ name: "custbody_netu_purchase_requisition", label: "Purchase Requ. #" }),
search.createColumn({ name: "custbody_netu_quotation_number", label: "Quotation #" }),
search.createColumn({ name: "custbody6", label: "Sales Order #" }),
search.createColumn({ name: "custbody_netu_invoicing_company", label: "Invoicing Company" })
]
});
var prResultCount = purchaseorderSearchObj2.runPaged().count;
var mainFieldValue = [];
var soFieldValue = [];
var POvalue = null;
if (prResultCount > 0) {
var prDetailsResults = purchaseorderSearchObj2.run().getRange({
start: 0,
end: 1
});
var mainField = ['custbody_netu_customer', 'class', 'location',
'custbody_netu_subjob', 'custbody_netu_vessel', 'department',
'custbody_netu_cost_gross_amount', 'custbody_netu_cost_discount_amount', 'custbody_netu_v_commission_perc',
'custbody_netu_cost_net_amount', 'custbody_netu_v_discount_perc', 'custbody_netu_v_add_commission_perc',
'custbody_netu_cost_commission_amount',
'custbody_netu_main_job', 'custbody_netu_purchase_requisition', 'custbody_netu_quotation_number',
'custbody6', 'custbody_netu_invoicing_company'
];
for (var k = 0; k < 18; k++) {
mainFieldValue[k] = prDetailsResults[0].getValue({
name: mainField[k]
})
}
var salesorderSearchObj = search.create({
type: "salesorder",
filters: [
["type", "anyof", "SalesOrd"],
"AND",
["internalidnumber", "equalto", so_id],
"AND",
["mainline", "is", "T"]
],
columns: [
search.createColumn({ name: "currency", label: "Currency" }),
search.createColumn({ name: "custbody_netu_default_currency", label: "Default Currency" }),
search.createColumn({ name: "custbody_netu_default_curr_exch_rate", label: "Default Currency Exchange Rate" }),
search.createColumn({ name: "custbody_default_currency_total", label: "Default Currency Total" }),
search.createColumn({ name: "custbody_netu_vendor", label: "Vendor" }),
search.createColumn({ name: "subsidiary", label: "Subsidiary" })
]
});
var soResultCount = salesorderSearchObj.runPaged().count;
if (soResultCount > 0) {
var soDetailsResults = salesorderSearchObj.run().getRange({
start: 0,
end: 1
});
var soField = ['currency', 'custbody_netu_default_currency', 'custbody_netu_default_curr_exch_rate',
'custbody_default_currency_total'
];
for (var b = 0; b < 4; b++) {
soFieldValue[b] = soDetailsResults[0].getValue({
name: soField[b]
})
}
var vendor = soDetailsResults[0].getText({
name: "custbody_netu_vendor"
})
var subsidiary = soDetailsResults[0].getText({
name: "subsidiary"
})
}
log.debug(subsidiary);
log.debug(vendor);
var custEntityId = null;
var customerSearchObj = search.create({
type: "customer",
filters: [
["entityid", "is", vendor],
"AND",
["msesubsidiary.name", "is", subsidiary]
// ["msesubsidiary.legalname", "is", "UMAR Shipping Services Ltd"]
],
columns: [
search.createColumn({
name: "entityid",
sort: search.Sort.ASC,
label: "Name"
}),
search.createColumn({ name: "internalid", label: "Internal ID" })
]
});
var searchResultCountcust = customerSearchObj.runPaged().count;
if (searchResultCountcust > 0) {
var custResults = customerSearchObj.run().getRange({
start: 0,
end: 1
});
custEntityId = custResults[0].getValue({
name: "internalid"
})
} else {
custEntityId = 22327; //228
}
//Creating invoice Record
var formrec = record.create({
type: record.Type.INVOICE,
isDynamic: true,
defaultValues: {
entity: custEntityId //mainFieldValue[0]
}
});
//setting the commission invoice form
formrec.setValue({
fieldId: 'customform',
value: 156
});
formrec.setValue({
fieldId: 'custbody_netu_subjob_status',
value: 18
});
//setting main values from SO
for (var b = 0; b < 4; b++) {
formrec.setValue({
fieldId: soField[b],
value: soFieldValue[b]
});
}
//setting the cost values from PO
for (var k = 0; k < 18; k++) {
if ((k == 8) || (k == 10) || (k == 11)) {
commi = mainFieldValue[k].split("%");
mainFieldValue[k] = commi[0];
}
formrec.setValue({
fieldId: mainField[k],
value: mainFieldValue[k]
});
}
var exRate = formrec.getValue({
fieldId: 'exchangerate'
});
log.debug('exRate', exRate);
//setting base values
var purField = ['custbody_netu_cost_gross_amount', 'custbody_netu_cost_discount_amount',
'custbody_netu_cost_commission_amount', 'custbody_netu_cost_net_amount'
];
var baseValues = ['custbody_netu_cost_gross_amount_base', 'custbody_netu_cost_discount_amount_b',
'custbody_netu_cost_commission_amount_b', 'custbody_netu_cost_net_amount_b'
];
for (var b = 0; b < 4; b++) {
POvalue = prDetailsResults[0].getValue({
name: purField[b]
})
POvalue = checkif(POvalue);
var baseValue = parseFloat(POvalue) * parseFloat(exRate);
formrec.setValue({
fieldId: baseValues[b],
value: baseValue
});
}
}
//finding the commission item corresponding to the class and setting
var customrecord_netu_class_revenue_mapSearchObj = search.create({
type: "customrecord_netu_class_revenue_map",
filters: [
["custrecord23", "is", "T"],
"AND",
["custrecord_netu_map_class.internalidnumber", "equalto", mainFieldValue[1]]
],
columns: [
search.createColumn({ name: "custrecord_netu_item_code", label: "Item Code" }),
search.createColumn({ name: "internalid", label: "Internal ID" })
]
});
var resultCount = customrecord_netu_class_revenue_mapSearchObj.runPaged().count;
if (resultCount > 0) {
var detailsResults = customrecord_netu_class_revenue_mapSearchObj.run().getRange({
start: 0,
end: 1
});
commiItemCode = detailsResults[0].getValue({
name: "custrecord_netu_item_code"
});
//setting item code in a new line
formrec.selectNewLine({
sublistId: 'item'
});
formrec.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'item',
value: commiItemCode
});
formrec.setCurrentSublistValue({
sublistId: 'item',
fieldId: 'amount',
value: mainFieldValue[12]
});
formrec.commitLine({
sublistId: 'item'
});
}
formrec.setValue({
fieldId: 'custbody_netu_view_mode',
value: true
});
var recordId = formrec.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
var recId = context.request.parameters.recId;
//Getting the Invoice Type
var sjRecDtls = search.lookupFields({
type: 'customrecord_netu_sub_job',
id: recId,
columns: ['custrecord_netu_subjob_invoice_type']
});
var inType = null;
if (sjRecDtls.custrecord_netu_subjob_invoice_type != null) {
inType = sjRecDtls.custrecord_netu_subjob_invoice_type[0].value;
}
log.debug({
title: 'inType',
details: inType
});
//Making the SO and PO Transaction status Closed
if (recordId) {
if (inType == 2) {
var poRecord = record.load({
type: record.Type.PURCHASE_ORDER,
id: po_id,
isDynamic: false
});
var numLines = poRecord.getLineCount({
sublistId: 'item'
});
var i = 0;
for (i = 0; i < numLines; i++) {
//Setting sublist column Closed to true
poRecord.setSublistValue({
sublistId: 'item',
fieldId: 'isclosed',
line: i,
value: true
});
}
poRecord.save();
//setting subjob and transaction status in sales order
var soRecord = record.load({
type: record.Type.SALES_ORDER,
id: so_id,
isDynamic: false
});
var numLines = soRecord.getLineCount({
sublistId: 'item'
});
var i = 0;
for (i = 0; i < numLines; i++) {
//Setting sublist column Closed to true
soRecord.setSublistValue({
sublistId: 'item',
fieldId: 'isclosed',
line: i,
value: true
});
}
soRecord.save();
}
st = 18;
if (subjobStatus == 17) {
changeStatus(recId, st);
}
}
var recordUrl = url.resolveRecord({
recordType: record.Type.INVOICE,
recordId: recordId,
isEditMode: true
});
var response = {
//recordurl: recordUrl+ "&viewFlag=T"
recordurl: recordUrl
}
context.response.write(JSON.stringify(response));
} catch (e) {
var response = {
errorMessage: e.message
}
context.response.write(JSON.stringify(response));
log.debug({
title: e.name,
details: e.message
});
}
}
});