Requirement
Calculate the landed cost for vendor bill based on the volume on vendor bill.
Solution
/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
*/
define(['N/record', 'N/search', 'N/ui/serverWidget', 'N/runtime'],
/**
* @param{record} record
* @param{search} search
* @param{serverWidget} serverWidget
* @param{runtime} runtime
*/
(record, search, serverWidget, runtime) => {
/**
* Defines the function definition that is executed before record is loaded.
* @param {Object} scriptContext
* @param {Record} scriptContext.newRecord - New record
* @param {string} scriptContext.type - Trigger type; use values from the context.UserEventType enum
* @param {Form} scriptContext.form - Current form
* @param {ServletRequest} scriptContext.request - HTTP request information sent from the browser for a client action only.
* @since 2015.2
*/
const beforeLoad = (scriptContext) => {
try {
let currentRecord = scriptContext.newRecord;
if (scriptContext.type == scriptContext.UserEventType.COPY) {
// let currentRecord = scriptContext.newRecord;
// Uncheck the checkbox field value
currentRecord.setValue({
fieldId: "custbody_jj_cost_alloc_vol_lalt2",
value: false
});
}
if ((scriptContext.type == scriptContext.UserEventType.CREATE) || (scriptContext.type == scriptContext.UserEventType.EDIT) || (scriptContext.type == scriptContext.UserEventType.COPY)) {
let objForm = scriptContext.form;
let subList = objForm.getSublist({ id: 'recmachcustrecord_jj_landed_cost' });
let billTransField = subList.addField({
id: 'custpage_bill_transaction',
label: 'Bill Transaction',
type: serverWidget.FieldType.SELECT
});
billTransField.addSelectOption({ //add options to the field
value: '',
text: ''
});
let billTransactions = filterBillTransaction()
let billCounts = []
if (billTransactions.length > 0) {
for (let num = 0; num < billTransactions.length; num++) {
let checkOtherBills = checkIfBillExistsInOtherBills(billTransactions[num].id)
if (checkOtherBills) {
let bills = {}
bills.id = billTransactions[num].id
bills.name = billTransactions[num].name
billCounts.push(bills)
}
}
}
log.audit("billCounts", billCounts)
for (let j = 0; j < billCounts.length; j++) {
billTransField.addSelectOption({ //add select options to the field
value: billCounts[j].id,
text: 'Bill #' + billCounts[j].name
});
}
let billFieldValue = subList.getField({ id: 'custrecord_jj_bill_transaction_lalt2' });
billFieldValue.updateDisplayType({ displayType: serverWidget.FieldDisplayType.HIDDEN });
let lineCount = currentRecord.getLineCount({
sublistId: 'recmachcustrecord_jj_landed_cost'
});
for (let i = 0; i < lineCount; i++) {
let billValue = currentRecord.getSublistValue({
sublistId: 'recmachcustrecord_jj_landed_cost',
fieldId: 'custrecord_jj_bill_transaction_lalt2',
line: i
});
if (checkForParameter(billValue)) {
currentRecord.setSublistValue({
sublistId: 'recmachcustrecord_jj_landed_cost',
fieldId: 'custpage_bill_transaction',
line: i,
value: billValue
});
}
}
}
}
catch (err) {
log.error("error@beforeLoad", err)
}
}
/**
* Defines the function definition that is executed before record is submitted.
* @param {Object} scriptContext
* @param {Record} scriptContext.newRecord - New record
* @param {Record} scriptContext.oldRecord - Old record
* @param {string} scriptContext.type - Trigger type; use values from the context.UserEventType enum
* @since 2015.2
*/
const beforeSubmit = (scriptContext) => {
try {
let billRecord = scriptContext.newRecord;
if (scriptContext.type == scriptContext.UserEventType.CREATE || scriptContext.type == scriptContext.UserEventType.EDIT) {
let context = runtime.executionContext;
let lineCount = billRecord.getLineCount({
sublistId: 'recmachcustrecord_jj_landed_cost'
});
if (context == 'USERINTERFACE') {
for (let i = 0; i < lineCount; i++) {
let billValueToSet = billRecord.getSublistValue({
sublistId: 'recmachcustrecord_jj_landed_cost',
fieldId: 'custpage_bill_transaction',
line: i
});
billRecord.setSublistValue({
sublistId: 'recmachcustrecord_jj_landed_cost',
fieldId: 'custrecord_jj_bill_transaction_lalt2',
line: i,
value: billValueToSet
});
}
}
let costAllocationVolume = billRecord.getValue({
fieldId: 'custbody_jj_cost_alloc_vol_lalt2'
});
if (costAllocationVolume == true) {
if (billRecord.getValue({
fieldId: 'landedcostperline'
}) == false) {
billRecord.setValue({
fieldId: 'landedcostperline',
value: true
});
}
let landedCostCount = billRecord.getLineCount({
sublistId: 'recmachcustrecord_jj_landed_cost'
});
let itemCount = billRecord.getLineCount({
sublistId: 'item'
});
//let landedCostValueArray = [];
let landedCostArrays = []
if ((landedCostCount > 0) && (itemCount > 0)) {
for (let landedCostItr = 0; landedCostItr < landedCostCount; landedCostItr++) {
let landedeCostObj = {};
landedeCostObj.costCategory = billRecord.getSublistValue({
sublistId: 'recmachcustrecord_jj_landed_cost',
fieldId: 'custrecord_jj_cost_category_lalt2',
line: landedCostItr
});
landedeCostObj.landedCostValue = billRecord.getSublistValue({
sublistId: 'recmachcustrecord_jj_landed_cost',
fieldId: 'custrecord_jj_landed_cost_lalt2',
line: landedCostItr
});
if (checkForParameter(landedeCostObj.costCategory) && ((checkForParameter(landedeCostObj.landedCostValue) && (landedeCostObj.landedCostValue != 0)))) {
// landedCostValueArray.push(landedeCostObj);
landedCostArrays.push(landedeCostObj)
}
}
//Code to add the landed cost values based on the cost category in landed cost volume sublist.
const landedCostValueArray = landedCostArrays.reduce((acc, obj) => {
const found = acc.find(item => item.costCategory === obj.costCategory);
if (found) {
found.landedCostValue += obj.landedCostValue;
} else {
acc.push({ costCategory: obj.costCategory, landedCostValue: obj.landedCostValue });
}
return acc;
}, []);
// }
let totalCbmVolume = 0
let cbmVolume = []
if (landedCostValueArray.length > 0) {
for (let items = 0; items < itemCount; items++) {
cbmVolume.push(billRecord.getSublistValue({
sublistId: 'item',
fieldId: 'custcol_jj_cbm',
line: items
}))
totalCbmVolume = Number(totalCbmVolume) + Number(cbmVolume[items]);
}
updateLandedCostSubrecord(billRecord, totalCbmVolume, landedCostValueArray, itemCount, cbmVolume);
}
}
}
}
}
catch (error) {
log.error("error@beforeSubmit", error);
}
}
/**
* The function is to update the landed cost subrecord in bill record
* @param {*} billRecord : Bill record
* @param {*} totalCbmVolume : Total CBM volume value
* @param {*} landedCostValueArray : Landed cost values
* @param {*} itemCount : Number of items
* @param {*} cbmVolume : CBM Values of each line
*/
function updateLandedCostSubrecord(billRecord, totalCbmVolume, landedCostValueArray, itemCount, cbmVolume) {
try {
for (let items = 0; items < itemCount; items++) {
let itemId = billRecord.getSublistValue({
sublistId: 'item',
fieldId: 'item',
line: items
});
let checkItemType = itemTypeSearch(itemId)
if (checkItemType) {
let landedCostSubRecord = billRecord.getSublistSubrecord({
sublistId: 'item',
fieldId: 'landedcost',
line: items
});
let subRecSublistCount = landedCostSubRecord.getLineCount({
sublistId: 'landedcostdata',
})
if (subRecSublistCount > 0) {
for (let num = subRecSublistCount; num > 0; num--) {
landedCostSubRecord.removeLine({
sublistId: "landedcostdata",
line: num - 1
})
}
}
if ((checkForParameter(cbmVolume[items]) && cbmVolume[items] != 0)) {
for (let landedCostNum = 0; landedCostNum < landedCostValueArray.length; landedCostNum++) {
let calcualtedVal = landedCostValueArray[landedCostNum]['landedCostValue'] / totalCbmVolume
try {
if (checkForParameter(calcualtedVal) && (calcualtedVal != 0)) {
landedCostSubRecord.setSublistValue({
sublistId: 'landedcostdata',
fieldId: 'costcategory',
line: landedCostNum,
value: landedCostValueArray[landedCostNum]['costCategory']
});
landedCostSubRecord.setSublistValue({
sublistId: 'landedcostdata',
fieldId: 'amount',
line: landedCostNum,
value: calcualtedVal * cbmVolume[items]
});
}
}
catch (err) {
log.error("error@landedcostCalculation", err)
}
}
}
}
}
}
catch (error) {
log.error("error@updateLandedCostSubrecord", error);
}
}
/**
* The function is to check if the parameter satidfies the criteria
* @param {} parameter the parameter that is to be checked
* @returns true if conditon is satisfied
*/
function checkForParameter(parameter) {
if (parameter !== "" && parameter !== null && parameter !== undefined && parameter !== false && parameter !== "null" && parameter !== "undefined" && parameter !== " " && parameter !== 'false') {
return true;
}
}
/**
* This function is to check if the type of item is inventory, kit item or non-invnetory item
* @param {*} itemId Internal Id of the item
* @returns true if it is invnetory , kit or non- inventory items. Otherwise returns false
*/
function itemTypeSearch(itemId) {
try {
let itemSearchObj = search.create({
type: "item",
filters:
[
// ["type", "anyof", "InvtPart", "Kit", "NonInvtPart"],
// "AND",
["tracklandedcost", "is", "T"],
"AND",
["internalid", "anyof", itemId]
],
columns:
[
search.createColumn({ name: "internalid", label: "Internal ID" })
]
});
let searchResultCount = itemSearchObj.runPaged().count;
if (searchResultCount > 0) {
return true
}
else {
return false
}
}
catch (err) {
log.error("error@itemTypeSearch", err)
}
}
/**
* The function is called to run a asaved search to get the bills having the landed cost items
* @returns the bills having the landed cost items
*/
function filterBillTransaction() {
try {
var vendorbillSearchObj = search.create({
type: "vendorbill",
filters:
[
["type", "anyof", "VendBill"],
"AND",
// ["item.internalid", "anyof", "7218", "7217", "7216"]
["item.internalid", "anyof", "7421", "7422"]
],
columns:
[
search.createColumn({
name: "internalid",
summary: "GROUP",
label: "Internal ID"
}),
search.createColumn({
name: "formulatext",
summary: "GROUP",
formula: "NVL ({tranid}, {transactionnumber})",
label: "Formula (Text)"
})
]
});
var searchResultCount = vendorbillSearchObj.runPaged().count;
var searchResult = vendorbillSearchObj.run().getRange(0, 1000);
var resultArray = []
if (searchResultCount > 0) {
for (var count = 0; count < searchResultCount; count++) {
let billRec = {}
billRec.id = searchResult[count].getValue({
name: "internalid",
summary: "GROUP",
label: "Internal ID"
})
billRec.name = searchResult[count].getValue({
name: "formulatext",
summary: "GROUP",
formula: "NVL ({tranid}, {transactionnumber})",
label: "Formula (Text)"
})
resultArray.push(billRec)
}
}
return resultArray
}
catch (err) {
console.log("error@filterBillTransaction", err)
return []
}
}
/**
* The funciton executes a saved search to check if the bill is already seelcted in any other vendor bills in landed cost sublist
* @param {} billIds Internal Id of bill record
* @returns true if it in other bills otherswise returns false
*/
function checkIfBillExistsInOtherBills(billIds) {
try {
let vendorbillSearchObj = search.create({
type: "vendorbill",
filters:
[
["type", "anyof", "VendBill"],
"AND",
["custrecord_jj_landed_cost.custrecord_jj_bill_transaction_lalt2", "anyof", billIds]
],
columns:
[
search.createColumn({
name: "internalid",
summary: "GROUP",
label: "Internal ID"
})
]
});
let searchResultCount = vendorbillSearchObj.runPaged().count;
if (searchResultCount > 0) {
return false
}
else {
return true
}
}
catch (err) {
log.error("error@checkIfBillExistsInOtherBills", err)
return true
}
}
return { beforeLoad, beforeSubmit }
});