Jira Code: DI-31
Description
Bill amount field:
Create a field in vendor bill to connect the project item list in the bill record. Add the corresponding project item list in ṭhis field.
Create a bill amount field in the item list.
Add the bill amount to this field when we create a new bill for this item
Subtract from the bill amount from this field if we delete the bill.
Update the bill amount field if the bill is edited.
Create a total bill amount field in the project item list. This will sum up all the bill amount in the item list.
Other charge items:
Created a new sublist for entering expense category which is coming in the vendor bill.
When the other charge item is coming in a bill, create it in the item list.
The expenses can be entered in the expense line, as an expense category. This can be entered in project item list as Other expense lines.
The expense items can be created as an item in the item list.
Expense category can be created as the other expense line.
Subtract the other expense item amount when a bill is deleted.
The update should be also reflected in the item list/other expense lines.
And in the expense category sublist/item list, we need the Bill ID, and the PO number to show.
Total of the other charge should be shown in the project item list.
Total other charges from bill= expense category sublist amount+item list amount for freight and sales tax
/**
* @NApiVersion 2.x
* @NScriptType UserEventScript
* @NModuleScope SameAccount
*/
/*******************************************************************************
*
* **************************************************************************
*
* Date: 23-05-2019
*
* Author: Jobin & Jismi IT Services LLP
*
*****************************************************************************
**/
define(['N/format', 'N/url', 'N/search', 'N/runtime', 'N/record', 'N/https', 'N/ui/serverWidget'],
function(format, url, search, runtime, record, https, serverWidget) {
var main = {
afterSubmit: function(scriptContext) {
log.debug("scriptContext", scriptContext)
var newRecId = scriptContext.newRecord.id;
if (scriptContext.type == "create") {
var BillRec = record.load({
type: "vendorbill",
id: newRecId,
isDynamic: false
})
var projectitem = BillRec.getValue({
fieldId: "custbody_jj_di_27_relat_prj_itm_list"
})
var categoryArray = [];
var itemList = [];
var expensecategoryId;
if (projectitem) {
var projectItemList = record.load({
type: "customrecord_jj_di2_pjct_item_list",
id: projectitem,
isDynamic: false
})
var numLines = BillRec.getLineCount({
sublistId: 'item'
});
var itemid;
var amount;
var venditemType;
var poid;
var totalBillamount = 0;
var expenseTotal = 0;
if (numLines > 0) {
for (var k = 0; k < numLines; k++) {
itemid = BillRec.getSublistValue({
sublistId: 'item',
fieldId: 'item',
line: k
});
amount = BillRec.getSublistValue({
sublistId: 'item',
fieldId: 'amount',
line: k
});
venditemType = BillRec.getSublistValue({
sublistId: 'item',
fieldId: 'itemtype',
line: k
});
poid = BillRec.getSublistValue({
sublistId: 'purchaseorders',
fieldId: 'poid',
line: 0
});
if (venditemType == "otherchargeitem") {
var otherchargeitemRec = record.load({
type: "otherchargeitem",
id: itemid,
isDynamic: false
})
var otherchargeitem = itemid;
var otherchargeitemList = record.create({
type: record.Type.customrecord_jj_di2_item_list,
isDynamic: true,
defaultValues: {
custrecord_di2_item_internalid: otherchargeitem,
custrecord_jj_di2_items: otherchargeitem,
custrecord2: projectitem,
custrecordjj_di_31_po_id_item: poid,
custrecordjj_di_31_bill_id_item: newRecId
}
})
var recordId = otherchargeitemList.save();
otherlist.push({
'id': itemid,
'amount': amount
})
} else {
itemList.push({
'id': itemid,
'amount': amount
});
}
}
var itemListUpdate = main.checkitemProject(projectitem, itemList);
main.billAmountcreate(itemListUpdate)
for (var l = 0; l < otherlist.length; l++) {
itemListUpdate.push({
'id': otherlist.id[i],
'amount': otherlist.amount[i]
})
}
for (var j = 0; j < itemlist.length; j++) {
totalBillamount += itemListUpdate[j].amount;
}
}
var numLinesExpense = BillRec.getLineCount({
sublistId: 'expense'
});
if (numLinesExpense > 0) {
for (var m = 0; m < numLinesExpense; m++) {
var expensecategoryId = BillRec.getSublistValue({
sublistId: 'expense',
fieldId: 'category',
line: m
});
var expensecategoryAmount = BillRec.getSublistValue({
sublistId: 'expense',
fieldId: 'amount',
line: m
});
categoryArray.push({
'id': expensecategoryId,
'amount': expensecategoryAmount,
'billid': newRecId,
'poid': poid
});
expenseTotal += expensecategoryAmount;
main.expensecategoryCreate(projectitem, categoryArray);
}
}
totalBillamount += expenseTotal
projectItemList.setValue({
fieldId: custrecordcustrecord_jj_di_31_total_bill,
value: totalBillamount
})
}
}
},
billAmountcreate: function(list) {
for (var i = 0; i < list.length; i++) {
var itemlistRec = record.load({
type: customrecord_jj_di2_item_list,
id: list[i].id,
isDynamic: false
})
var itemPoAmount = itemlistRec.getValue({
fieldId: 'custrecord_jj_di_27_po_amount',
});
var amountnew = list[i].amount;
var amountupdate = parseFloat(itemPoAmount) + parseFloat(amountnew);
var setamount = itemlistRec.setValue({
fieldId: 'custrecord_jj_di_27_po_amount',
value: amountupdate
});
var recordId = itemlistRec.save();
log.debug('createrecdId', recordId)
}
},
expensecategoryCreate: function(projectId, categoryArray) {
for (var i = 0; i < categoryArray.length; i++) {
var otherchargeitemList = record.create({
type: record.Type.customrecord_jj_pil_other_expense,
isDynamic: true,
defaultValues: {
custrecord4: projectId,
custrecord_jj_pil_expense_category: categoryArray[i].id,
custrecordjj_di_31_bill_id: categoryArray[i].billid,
custrecordjj_di_31_po_id: categoryArray[i].poid,
custrecordjj_di_31_bil_amount_expense: categoryArray[i].amount
}
});
var recordId = otherchargeitemList.save();
log.debug('createrecexpensedId', recordId)
}
},
checkitemProject: function(projectId, itemlist, getVal) {
var ItemInternalid;
var internalID;
var ItemInternalidArray = [];
var internalIDArray = [];
var itemarray = [];
var billidArray = [];
var poidArray = [];
var objVal;
var catIdArray = [];
var categoryArray = [];
var customrecord_jj_di2_pjct_item_listSearchObj = search.create({
type: "customrecord_jj_di2_pjct_item_list",
filters: [
["internalid", "is", projectId]
],
columns: [
search.createColumn({
name: "id",
sort: search.Sort.ASC,
label: "ID"
}),
search.createColumn({
name: "custrecord_di2_item_internalid",
join: "CUSTRECORD2",
label: "Item Internal id"
}),
search.createColumn({
name: "internalid",
join: "CUSTRECORD2",
label: "Internal ID"
}),
search.createColumn({
name: "custrecordjj_di_31_bill_id_item",
join: "CUSTRECORD2",
label: "BILL ID"
}),
search.createColumn({
name: "custrecordjj_di_31_po_id_item",
join: "CUSTRECORD2",
label: "PO ID"
}), search.createColumn({
name: "custrecord_jj_pil_expense_category",
join: "CUSTRECORD4",
label: "Category"
}),
search.createColumn({
name: "internalid",
join: "CUSTRECORD4",
label: "Internal ID"
}),
search.createColumn({
name: "custrecordjj_di_31_bill_id",
join: "CUSTRECORD4",
label: "BILL ID"
})
]
});
var searchResultCount = customrecord_jj_di2_pjct_item_listSearchObj.runPaged().count;
log.debug("customrecord_jj_di2_pjct_item_listSearchObj result count", searchResultCount);
customrecord_jj_di2_pjct_item_listSearchObj.run().each(function(result) {
// .run().each has a limit of 4,000 results
ItemInternalid = (result.getValue(customrecord_jj_di2_pjct_item_listSearchObj.columns[1]));
internalID = result.getValue(customrecord_jj_di2_pjct_item_listSearchObj.columns[2]);
var billid = result.getValue(customrecord_jj_di2_pjct_item_listSearchObj.columns[3]);
var poid = result.getValue(customrecord_jj_di2_pjct_item_listSearchObj.columns[4]);
var categoryId = result.getValue(customrecord_jj_di2_pjct_item_listSearchObj.columns[5]);
var catId = result.getValue(customrecord_jj_di2_pjct_item_listSearchObj.columns[6]);
ItemInternalidArray.push(ItemInternalid);
billidArray.push(billid);
poidArray.push(poid);
catIdArray.push(catId);
categoryArray.push(categoryId);
internalIDArray.push(internalID)
return true;
});
if (getVal == true) {
for (var j = 0; j < itemlist.length; j++) {
for (var i = 0; i < ItemInternalidArray.length; i++) {
if (itemlist[j].id == ItemInternalidArray[i])
itemarray.push({
'id': internalIDArray[i],
'amount': itemlist[j].amount,
'billid': billidArray[i],
'poid': poidArray[i],
'type': itemlist[j].type
})
}
}
} else {
for (var j = 0; j < itemlist.length; j++) {
for (var i = 0; i < ItemInternalidArray.length; i++) {
if (itemlist[j].id == categoryArray[i])
itemarray.push({
'id': catIdArray[i],
})
}
}
}
return itemarray;
},
beforeSubmit: function(scriptContext) {
if (scriptContext.type == "edit") {
var oldRec = scriptContext.oldRecord;
var newRec = scriptContext.newRecord;
var projectitem = oldRec.getValue({
fieldId: "custbody_jj_di_27_relat_prj_itm_list"
})
var projectItemList = record.load({
type: "customrecord_jj_di2_pjct_item_list",
id: projectitem,
isDynamic: false
})
var numLines = oldRec.getLineCount({
sublistId: 'item'
})
var numLines1 = newRec.getLineCount({
sublistId: 'item'
})
var projectitem = oldRec.getValue({
fieldId: "custbody_jj_di_27_relat_prj_itm_list"
})
var objOldVal = {};
var objNewVal = {};
var newArray = [];
if (projectitem) {
var itemcategoryArray = [];
if (numLines > 0) {
for (var k = 0; k < numLines; k++) {
var itemid = oldRec.getSublistValue({
sublistId: 'item',
fieldId: 'item',
line: k
});
var amount = oldRec.getSublistValue({
sublistId: 'item',
fieldId: 'amount',
line: k
});
var itemType = newRec.getSublistValue({
sublistId: 'item',
fieldId: 'itemtype',
line: k
});
itemcategoryArray.push({
'id': itemid,
'amount': amount,
'type': itemType
})
}
}
var oldBillTotal = 0;
var itemlistOld = main.checkitemProject(projectitem, itemcategoryArray, true);
for (var n = 0; n < itemlistOld.length; n++) {
oldBillTotal += itemlistOld[n].amount;
}
for (var j = 0; j < itemlistOld.length; j++) {
objOldVal[itemlistOld[j].id] = {
item: itemlistOld[j].id,
amount: itemlistOld[j].amount,
billId: itemlistOld[j].billid,
type: itemlistOld[j].type
};
}
var itemcategoryupdateArray = [];
if (numLines1 > 0) {
for (var i = 0; i < numLines1; i++) {
var itemid1 = newRec.getSublistValue({
sublistId: 'item',
fieldId: 'item',
line: i
});
var amount1 = newRec.getSublistValue({
sublistId: 'item',
fieldId: 'amount',
line: i
});
var itemType1 = newRec.getSublistValue({
sublistId: 'item',
fieldId: 'itemtype',
line: i
});
itemcategoryupdateArray.push({
'id': itemid1,
'amount': amount1,
'type': itemType1
})
}
}
var itemlistNew = main.checkitemProject(projectitem, itemcategoryupdateArray, true);
for (var j = 0; j < itemlistOld.length; j++) {
objNewVal[itemlistOld[j].id] = {
item: itemlistOld[j].id,
amount: itemlistOld[j].amount,
billId: itemlistOld[j].billid,
type: itemlistOld[j].type
};
}
var allowed = [];
var key;
for (key in objNewVal) {
allowed.push(key);
}
console.log(allowed);
var filtered = Object.keys(objOldVal).filter(function(key) {
return allowed.includes(key);
}).reduce(function(obj, key) {
if (objOldVal[key].type != 'otherchargeitem') {
obj[key] = {
'id': key,
'amount': objNewVal[key].amount - objOldVal[key].amount
}; //return obj;
}
}, {});
for (key in obj) {
newArray.push({
'id': obj[key].id,
'amount': obj[key].amount
})
}
var itemList = main.filterTwoarray(itemcategoryArray, itemcategoryupdateArray, true);
for (var i = 0; i < itemList.length; i++) {
newArray.push({
'id': list1[i].id,
'amount': list1[i].amount
});
}
main.billAmountcreate(newArray)
}
var totalBillamount = 0;
for (var j = 0; j < itemlist.length; j++) {
totalBillamount += newArray[j].amount;
}
var expenseTotal = 0;
var numLinesExpense = oldRec.getLineCount({
sublistId: 'expense'
});
var expenseTotal1 = 0
if (numLinesExpense > 0) {
for (var m = 0; m < numLinesExpense; m++) {
var expensecategoryId = oldRec.getSublistValue({
sublistId: 'expense',
fieldId: 'category',
line: m
});
var expensecategoryAmount = oldRec.getSublistValue({
sublistId: 'expense',
fieldId: 'amount',
line: m
});
categoryOldArray.push({
'id': expensecategoryId,
'amount': expensecategoryAmount,
'billid': newRecId,
'poid': poid
});
expenseTotal1 += expensecategoryAmount;
}
}
oldBillTotal += expenseTotal1
var numLinesExpense1 = newRec.getLineCount({
sublistId: 'expense'
});
if (numLinesExpense1 > 0) {
for (var m = 0; m < numLinesExpense1; m++) {
var expensecategoryId = newRec.getSublistValue({
sublistId: 'expense',
fieldId: 'category',
line: m
});
var expensecategoryAmount = newRec.getSublistValue({
sublistId: 'expense',
fieldId: 'amount',
line: m
});
categoryNewArray.push({
'id': expensecategoryId,
'amount': expensecategoryAmount,
'billid': newRecId,
'poid': poid
});
expenseTotal += expensecategoryAmount;
var createExpen = main.filterTwoarray(categoryOldArray, categoryNewArray, false);
main.expensecategoryCreate(projectitem, createExpen);
}
}
var oldTotal = projectItemList.getValue({
fieldId: custrecordcustrecord_jj_di_31_total_bill,
})
totalBillamount += expenseTotal;
var amountnew = totalBillamount - oldBillTotal;
var amountupdate = parseFloat(oldTotal) + parseFloat(amountnew);
projectItemList.setValue({
fieldId: custrecordcustrecord_jj_di_31_total_bill,
value: amountupdate
})
} else if (scriptContext.type == "delete") {
var delRec = scriptContext.newRecord;
var projectitem = delRec.getValue({
fieldId: "custbody_jj_di_27_relat_prj_itm_list"
})
var projectItemList = record.load({
type: "customrecord_jj_di2_pjct_item_list",
id: projectitem,
isDynamic: false
})
var numLines = delRec.getLineCount({
sublistId: 'item'
})
var projectitem = delRec.getValue({
fieldId: "custbody_jj_di_27_relat_prj_itm_list"
})
var newArray = [];
var categoryNewArray = [];
if (projectitem) {
var itemcategoryArray = [];
var itemotherCategory = [];
if (numLines > 0) {
for (var k = 0; k < numLines; k++) {
var itemid = delRec.getSublistValue({
sublistId: 'item',
fieldId: 'item',
line: k
});
var amount = delRec.getSublistValue({
sublistId: 'item',
fieldId: 'amount',
line: k
});
var itemType = delRec.getSublistValue({
sublistId: 'item',
fieldId: 'itemtype',
line: k
});
if (itemType != 'otherchargeitem') {
itemcategoryArray.push({
'id': itemid,
'amount': amount,
'type': itemType
})
} else
itemotherCategory.push({
'id': itemid,
'amount': amount,
'type': itemType
})
}
}
var itemlistDel = main.checkitemProject(projectitem, itemcategoryArray, true);
var itemListUpdate = [];
for (var l = 0; l < otherlist.length; l++) {
if (itemlistDel[i].type != 'otherchargeitem') {
itemListUpdate.push({
'id': itemlistDel[i].id,
'amount': -(itemlistDel[i].amount)
})
} else {
itemotherCategory.push({
'id': itemlistDel[i].id,
'amount': -(itemlistDel[i].amount)
})
}
}
main.deleteOther(projectitem, itemotherCategory, true);
main.billAmountcreate(itemListUpdate);
}
var numLinesExpense1 = newRec.getLineCount({
sublistId: 'expense'
});
if (numLinesExpense1 > 0) {
for (var m = 0; m < numLinesExpense1; m++) {
var expensecategoryId = newRec.getSublistValue({
sublistId: 'expense',
fieldId: 'category',
line: m
});
var expensecategoryAmount = newRec.getSublistValue({
sublistId: 'expense',
fieldId: 'amount',
line: m
});
categoryNewArray.push({
'id': expensecategoryId,
'amount': expensecategoryAmount
});
var itemlistDel = main.checkitemProject(projectitem, categoryNewArray, false);
main.deleteOther(projectitem, itemlistDel, false);
}
}
}
},
deleteOther: function(id, list, getVal) {
if (getVal == true) {
for (var i = 0; i < list.length; i++) {
var featureRecord = record.delete({
type: 'customrecord_jj_di2_item_list',
id: list[i].id,
});
}
} else {
for (var i = 0; i < list.length; i++) {
var featureRecord = record.delete({
type: 'customrecord_jj_pil_other_expense',
id: list[i].id,
});
}
}
},
filterTwoarray: function(list1, list2, getVal) {
var result = [];
if ((list1.length > 0) && (getVal == true)) {
for (var i = 0; i < list1.length; i++) {
var index = list2.indexOf(list1[i])
if (index == -1) {
result.push({
'id': list1[i].id,
'amount': -(list1[i].amount)
});
}
}
return result;
} else if ((list2.length > 0) && (getVal == false)) {
for (var i = 0; i < list2.length; i++) {
var index = list1.indexOf(list2[i])
if (index == -1) {
result.push({
'id': list2[i].id,
'amount': list2[i].amount,
'billid': list2[i].billid,
'poid': list2[i].poid
});
}
}
return result;
} else
return false
}
}
for (var key in main) {
if (typeof main[key] === 'function') {
main[key] = trycatch(main[key], key);
}
};
function trycatch(myfunction, key) {
return function() {
try {
return myfunction.apply(this, arguments);
} catch (e) {
log.error("e in " + key, e);
return false;
}
}
};
return main;
});