Requirement
We will be implementing an API to fetch the customer data from Netsuite to an application.
Request : GET
Script: Restlet
Parameters:
CompanyCode : Company code(Company code field in the subsidiary record corresponding to subsidiary field in customer record)- Mandatory
CustomerCode : Customer Code(id:custentity_code) field in the Customer record – Mandatory
Solution
/**
* @NApiVersion 2.1
* @NScriptType Restlet
*/
define(['N/currentRecord', 'N/https', 'N/record', 'N/runtime', 'N/search', 'N/url'],
/**
* @param{currentRecord} currentRecord
* @param{https} https
* @param{record} record
* @param{runtime} runtime
* @param{search} search
* @param{url} url
*/
(currentRecord, https, record, runtime, search, url) => {
/**
* Defines the function that is executed when a GET request is sent to a RESTlet.
* @param {Object} requestParams - Parameters from HTTP request URL; parameters passed as an Object (for all supported
* content types)
* @returns {string | Object} HTTP response body; returns a string when request Content-Type is 'text/plain'; returns an
* Object when request Content-Type is 'application/json' or 'application/xml'
* @since 2015.2
*/
//Customer Search created to retrieve data based on the customer code and the company code
function cust_search(comp_code, cust_code) {
var customerSearchObj = search.create({
type: "customer",
filters:
[
["entityid", "is", cust_code],
"AND",
["msesubsidiary.custrecord_company_code", "is", comp_code]
],
columns:
[
search.createColumn({
name: "entityid",
sort: search.Sort.ASC,
label: "ID"
}),
search.createColumn({
name: "custrecord_company_code",
join: "mseSubsidiary",
label: "Company Code"
}),
search.createColumn({name: "custentity_credit_limit_sppi", label: "CREDIT LIMIT FOR SPPI"}),
search.createColumn({name: "custentity_ce_pqn", label: "Credit Exposure For PQN"}),
search.createColumn({name: "unbilledorders", label: "Unbilled Orders"}),
search.createColumn({name: "balance", label: "Balance"}),
search.createColumn({name: "depositbalance", label: "Deposit Balance "}),
search.createColumn({name: "custentity_code", label: "Code"}),
search.createColumn({name: "internalid", label: "Internal ID"}),
search.createColumn({name: "custentity_credit_limit_pqn", label: "Credit Limit for PQN"})
]
});
var searchResultCount = customerSearchObj.runPaged().count;
log.debug("customerSearchObj result count", searchResultCount);
var searchResult = customerSearchObj.run().getRange({
start: 0,
end: 1000
});
return searchResult;
}
//Search created to retrieve the data based on the company code given in the params
function comp_code_search(compny_code) {
var subsidiarySearchObj = search.create({
type: "subsidiary",
filters:
[
["custrecord_company_code", "is", compny_code]
],
columns:
[
search.createColumn({name: "custrecord_company_code", label: "Company Code"})
]
});
var searchResultCount = subsidiarySearchObj.runPaged().count;
if (searchResultCount > 0) {
return true;
} else {
return false;
}
}
// Function created to check the parameter to null or not
function checkForParameter(parameter) {
if (
parameter != "" && parameter != null && parameter != undefined && parameter != "null" &&
parameter != "undefined" && parameter != " " && parameter != false
) {
return true;
} else {
return false;
}
}
function findSubsidiary(compCode) {
var subsidiarySearchObj = search.create({
type: "subsidiary",
filters:
[
["custrecord_company_code", "is", compCode]
],
columns:
[
search.createColumn({name: "namenohierarchy", label: "Name (no hierarchy)"}),
search.createColumn({name: "internalid", label: "Internal ID"})
]
});
var searchResultCount = subsidiarySearchObj.runPaged().count;
log.debug("subsidiarySearchObj result count", searchResultCount);
var subResult = subsidiarySearchObj.run().getRange({
start: 0,
end: 1000
});
return subResult;
}
const get = (requestParams) => {
try {
log.debug("requestParams", requestParams);
const requestObj = {
company_code: requestParams?.company_code,
customer_code: requestParams?.customer_code
};
log.debug('RequestObj', requestObj)
//Checking if company code is null or not
if (!checkForParameter(requestParams?.company_code)) {
return JSON.stringify({
"summary": {
"status": "ERROR",
"message": "COMPANY_CODE_IS_NULL",
"company_code": requestParams?.company_code,
"cust_no": requestParams?.customer_code
}
});
}
//Checking if Customer code is null or not
else if (!checkForParameter(requestParams?.customer_code)) {
return JSON.stringify({
"summary": {
"status": "ERROR",
"message": "CUSTOMER_CODE_IS_NULL",
"company_code": requestParams?.company_code,
"cust_no": requestParams?.customer_code
}
});
}
// Checking if the company code is invalid
else if (!(comp_code_search(requestParams?.company_code))) {
return JSON.stringify({
"summary": {
"status": "ERROR",
"message": "INVALID_COMPANY_CODE",
"company_code": requestParams?.company_code,
"cust_no": requestParams?.customer_code
}
});
}
var search_res = cust_search(requestParams?.company_code, requestParams?.customer_code)
log.debug("Search result", search_res);
var search_count = search_res.length;
log.debug('Count', search_count)
let results = [];
let cust_obj = {};
//Creating the object and setting customer data to it
if (search_count > 0) {
for (var i = 0; i < search_res.length; i++) {
var objRecord = record.load({
type: record.Type.CUSTOMER,
id: search_res[i].getValue({name: "internalid", label: "Internal ID"}),
isDynamic: true,
});
log.debug('Objrecord', objRecord)
var line_count = objRecord.getLineCount({
sublistId: 'submachine'
});
log.debug('Line count', line_count)
var subsidiaryCode = search_res[i].getValue({
name: "custrecord_company_code",
join: "mseSubsidiary",
label: "Company Code"
})
log.debug('Subsidiary code', subsidiaryCode)
for (var j = 0; j < line_count; j++) {
var subsidiary = objRecord.getSublistValue({
sublistId: "submachine",
fieldId: "subsidiary",
line: j
});
log.debug('Subsidiary', subsidiary)
var fieldLookUp = search.lookupFields({
type: search.Type.SUBSIDIARY,
id: subsidiary,
columns: ['custrecord_company_code']
}).custrecord_company_code;
log.debug('Fieldlookup', fieldLookUp)
if (fieldLookUp == subsidiaryCode) {
var unbilledOrders = objRecord.getSublistValue({
sublistId: "submachine",
fieldId: "unbilledorders",
line: j
}) || 0;
log.debug("unbilled orders", unbilledOrders);
var depositBalance = objRecord.getSublistValue({
sublistId: "submachine",
fieldId: "depositbalance",
line: j
}) || 0;
log.debug("depisotebalance", depositBalance);
var balance = objRecord.getSublistValue({
sublistId: "submachine",
fieldId: "balance",
line: j
}) || 0;
log.debug("balance", balance)
}
}
var credit_exp = Number(unbilledOrders) + Number(balance) - Number(depositBalance)
var pqn = search_res[i].getValue({
name: "custentity_credit_limit_pqn",
label: "Credit Limit for PQN"
}) || 0;
var sppi = search_res[i].getValue({
name: "custentity_credit_limit_sppi",
label: "CREDIT LIMIT FOR SPPI"
}) || 0;
var subsidiaryResult = findSubsidiary(requestParams?.company_code)
log.debug('subsidiary......................', subsidiaryResult[0].id)
var crdt_limit;
if (subsidiaryResult[0].id == 7) {
crdt_limit = Number(pqn)
} else if (subsidiaryResult[0].id == 5) {
crdt_limit = Number(sppi)
}
log.debug('Sum', credit_exp)
var credit_avl = crdt_limit - credit_exp
log.debug('Sum2', credit_avl)
log.debug('PQN', pqn)
log.debug('SPPI', sppi)
cust_obj = {
"customer": search_res[i].getValue({
name: "entityid",
sort: search.Sort.ASC,
label: "ID"
}),
"credit_ctrl_area": search_res[i].getValue({
name: "custrecord_company_code",
join: "mseSubsidiary",
label: "Company Code"
}),
// "credit_limit": `${pqn},${sppi}`,
"credit_limit": crdt_limit,
"acct_ref": search_res[i].getValue({name: "entityid", sort: search.Sort.ASC, label: "ID"}),
"sale_value": unbilledOrders,
"receivables": balance,
"rel_spec_liabilities": Number(search_res[i].getValue({
name: "depositbalance",
label: "Deposit Balance "
})) || 0,
"exceeded_date": "",
"change_obj": "",
"created_by": "",
"created_on": "",
"risk_cate": "",
"last_review": "",
"ind_blocked_credit": "",
"credit_rep_grp": "",
"next_review": "",
"credit_info_no": "",
"cust_credit_grp": "",
"changed_by": "",
"ref_date": "",
"cust_grp": "",
"paym_date": "",
"paym_amt": "",
"paym_curr": "",
"rec_credit_limit": "",
"rec_credit_limit_cur": "",
"secured_receivables": "",
"credit_exposure": credit_exp,
"credit_available": credit_avl,
"status": "success"
}
results.push(cust_obj)
log.debug('Customer array',)
}
log.debug('result', results)
//Returning the response
return JSON.stringify({
summary: {
status: "SUCCESS",
message: "RECORD_FOUND"
},
Lines: results
});
}
//Checking if the record does not exist with the given company code and customer code
else if (search_count === 0) {
return JSON.stringify({
"summary": {
"status": "ERROR",
"message": "RECORDS_NOT_FOUND",
"company_code": requestParams?.company_code,
"cust_no": requestParams?.customer_code
}
});
}
} catch (e) {
log.debug('Error@Get', e)
}
}
const put = (requestBody) => {
}
const post = (requestBody) => {
}
const doDelete = (requestParams) => {
}
return {get}
});