JIRA TASK :
ALGR-81] Margin Verification – JIRA – Jobin & Jismi IT Services LLP (atlassian.net)
[ALGR-121] Margin Verification – JIRA – Jobin & Jismi IT Services LLP (atlassian.net)
Scenario
We need to add total cost, margin and margin percentage in purchase order billing, which will allow finance team to monitor margin given by vendors.
Solution
/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
*/
/************************************************************************************
ALGR - 121 Margin Verification
************************************************************************************
*
* Author: Jobin & Jismi IT Services LLP
*
* Date Created : 5th-July-2022
*
* Description : Margin Verification
*
* REVISION HISTORY
*
***********************************************************************************/
define(['N/record', 'N/search'],
/**
* @param{record} record
* @param{search} search
*/
(record, search) => {
/**
* Function to check parameter values for custom duty, logistics, saber and other fee
* @param parameter
* @returns {boolean}
*/
const checkForParameter = function checkForParameter(parameter) {
if (parameter !== "" && parameter !== null && parameter !== undefined && parameter !== false && parameter !== "null" && parameter !== "undefined" && parameter !== " " && parameter !== 'false' ) {
return true;
}
}
// /**
// * Function check whether the object is empty
// * @param obj defines empty object
// * @returns {boolean}
// */
// function checkEmptyObject(obj){
// try{
//
// return JSON.stringify(obj) === '{}';
// }
// catch(err)
// {
// log.debug("error@CheckEmptyObject",err)
// }
// }
/**
* Function to find the UOM values
* @param {object[]}unitName defines the unit type of an item
* @returns {{}}
*/
function unitTypeSearch(unitName) {
try {
let filterObj = {};
for (let k = 0; k < unitName.length; k++) {
filterObj[unitName[k].item_unitType] = unitName[k].item_unitType
}
let unitIdArr = Object.keys(filterObj);
log.debug("unitIdArr", unitIdArr);
let filter = [];
filter.push(["unitname", "is", unitIdArr[0]])
for (let k = 1; k < unitIdArr.length; k++) {
filter.push("OR", ["unitname", "is", unitIdArr[k]])
}
log.debug("Unitfilter***", filter);
let unitstypeSearchObj = search.create({
type: "unitstype",
filters: filter,
columns:
[
search.createColumn({name: "internalid", label: "Internal ID"}),
search.createColumn({name: "unitname", label: "Unit Name"}),
search.createColumn({name: "conversionrate", label: "Rate"})
]
});
let searchResultCount = unitstypeSearchObj.runPaged().count;
log.debug('searchResultCount', searchResultCount)
if (searchResultCount > 0) {
let resultObj = {};
unitstypeSearchObj.run().each(function (result) {
let resObj = {};
let unitType = result.getValue({
name: "unitname", label: "Unit Name"
})
let conversionRate = result.getValue({
name: "conversionrate", label: "Rate"
})
// resObj.unitType = unitType
resObj.conversionRate = conversionRate;
resultObj[unitType] = resObj;
return true;
});
return resultObj;
} else
return {};
} catch (err) {
log.debug("error@unitTypeSearch", err)
return {};
}
}
/**
* Function to find the sales price of each item from item record
* @param {object []}itemArray
* @returns {{}}
*/
function itemSearch(itemArray) {
try {
let filterObj = {};
for (let k = 0; k < itemArray.length; k++) {
filterObj[itemArray[k].item_Id] = itemArray[k].item_Id;
}
let itemIdArr = Object.keys(filterObj);
log.debug("itemIdArr", itemIdArr);
let filter = [];
filter.push(["internalid", "is", itemIdArr[0]])
for (let k = 1; k < itemIdArr.length; k++) {
filter.push("OR", ["internalid", "is", itemIdArr[k]])
}
log.debug("itemfilter", filter);
let itemSearchObj = search.create({
type: "item",
filters:
[
["type", "anyof", "InvtPart", "NonInvtPart"],
"AND",
filter
],
columns:
[
search.createColumn({
name: "itemid",
sort: search.Sort.ASC,
label: "Name"
}),
search.createColumn({name: "displayname", label: "Display Name"}),
search.createColumn({name: "type", label: "Type"}),
search.createColumn({name: "baseprice", label: "Base Price"}),
search.createColumn({name: "internalid", label: "Internal ID"}),
search.createColumn({name: "saleunit", label: "Primary Sale Unit"}),
]
});
let searchResultCount = itemSearchObj.runPaged().count;
if (searchResultCount > 0) {
let itemObject = {};
itemSearchObj.run().each(function (result) {
let itemObj = {};
let itemID = result.getValue({
name: "internalid", label: "Internal ID"
})
let basePrice = result.getValue({
name: "baseprice", label: "Base Price"
});
let primarySaleUnit = result.getText({
name: "saleunit", label: "Primary Sale Unit"
});
// itemObj.item_Id = itemID;
itemObj.basePrice = basePrice;
itemObj.item_unitType = primarySaleUnit;
itemObject[itemID] = itemObj;
return true;
});
return itemObject;
} else
return {};
} catch (err) {
log.debug("error@itemSearch", err)
return {};
}
}
/**
* Function to get the item id, item rate and unit type
* @param {object}billRecord is the current Bill record
* @returns {*[]}
*/
function billRecordData(billRecord) {
try {
log.debug("billRecord", billRecord)
if (!billRecord)
return false;
let lineCount = billRecord.getLineCount({
sublistId: 'item'
});
log.debug("lineCount****", lineCount);
let mainArr = [];
for (let i = 0; i < lineCount; i++) {
let itemObj = {};
itemObj.item_Id = billRecord.getSublistValue({
sublistId: 'item',
fieldId: 'item',
line: i
});
itemObj.item_unitType = billRecord.getSublistValue({
sublistId: 'item',
fieldId: 'units_display',
line: i
});
itemObj.item_rate = billRecord.getSublistValue({
sublistId: 'item',
fieldId: 'rate',
line: i
});
mainArr.push(itemObj);
}
return mainArr;
} catch (err) {
log.debug("error@billREcordData", err)
return [];
}
}
/**
* Defines the function definition that is executed after 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 afterSubmit = (scriptContext) => {
try {
if (scriptContext.type === 'create' || scriptContext.type === 'edit') {
let currentRec = scriptContext.newRecord;
log.debug("currentRec", currentRec)
let billRecord = record.load({
type: record.Type.VENDOR_BILL,
id: currentRec.id
// isDynamic: true
});
let customDuty = billRecord.getValue({
fieldId: "landedcostamount1"
});
let logistics = billRecord.getValue({
fieldId: "landedcostamount2"
});
let saber = billRecord.getValue({
fieldId: "landedcostamount3"
});
let otherFee = billRecord.getValue({
fieldId: "landedcostamount4"
});
let totalLandedCost = 0;
if (checkForParameter(customDuty)) {
totalLandedCost = totalLandedCost + customDuty;
}
if (checkForParameter(logistics)) {
totalLandedCost = totalLandedCost + logistics;
}
if (checkForParameter(saber)) {
totalLandedCost = totalLandedCost + saber;
}
if (checkForParameter(otherFee)) {
totalLandedCost = totalLandedCost + otherFee;
}
log.debug("totalLandedCost", totalLandedCost);
let mainArr = billRecordData(billRecord);
log.debug("mainArr******", mainArr);
if (mainArr.length < 1)
return false;
let unitSearchData = unitTypeSearch(mainArr);
log.debug("unitSearchData", unitSearchData);
//log.debug("unitSearchData Length",Object.keys(unitSearchData).length)
if((Object.keys(unitSearchData).length) < 1)
return false;
let conversionRate, itemRate;
let costSum = 0;
/**
* Finding the total purchase rate of the bill record to find total cost
*/
for (let i = 0; i < mainArr.length; i++) {
if (mainArr[i].item_unitType !== "" && mainArr[i].item_rate !== 0) {
conversionRate = unitSearchData[mainArr[i].item_unitType]["conversionRate"];
itemRate = (mainArr[i].item_rate) / conversionRate;
costSum = (costSum + itemRate);
}
}
log.debug("costSum", costSum);
/**
* Finding Total Cost = Landed cost + Total purchase rate
* @type {number}
*/
let totalCost = (totalLandedCost + costSum);
log.debug("totalCost", totalCost);
billRecord.setValue({
fieldId: "custbody_jj_total_cost_algar_122",
value: totalCost
});
let itemSearchData = itemSearch(mainArr);
log.debug("itemSearchData", itemSearchData);
if ((Object.keys(itemSearchData).length) < 1)
return false;
let basePrice, itemBaseUnitPrice;
let salesConversionRate, salesConversionRateNumber;
let totalBasePrice = 0;
/**
* Adding all the item primary sales unit to an array
*/
let unitTypeArray = [];
for (let i = 0; i < mainArr.length; i++) {
let unitTypeObj = {};
salesConversionRate = itemSearchData[mainArr[i].item_Id]["item_unitType"];
unitTypeObj.item_unitType = salesConversionRate;
unitTypeArray.push(unitTypeObj);
}
log.debug("unitTypeArray", unitTypeArray)
let saleUnitData = unitTypeSearch(unitTypeArray);
log.debug("saleUnitData", saleUnitData);
if ((Object.keys(saleUnitData).length) < 1)
return false;
/**
* Finding the total base price of the items in the bill record
*/
for (let i = 0; i < mainArr.length; i++) {
if (!saleUnitData[unitTypeArray[i].item_unitType]) {
log.debug("No Primary Sale Unit for this item");
return false;
}
salesConversionRateNumber = saleUnitData[unitTypeArray[i].item_unitType]["conversionRate"];
basePrice = itemSearchData[mainArr[i].item_Id]["basePrice"];
itemBaseUnitPrice = basePrice / salesConversionRateNumber;
totalBasePrice = totalBasePrice + itemBaseUnitPrice;
}
log.debug("totalBasePrice", totalBasePrice);
/**
* Finding Margin = Total base price - total Cost
* @type {string}
*/
let margin = (totalBasePrice - totalCost).toFixed(2);
log.debug("margin", margin);
billRecord.setValue({
fieldId: "custbody_jj_margin_algar122",
value: margin
});
/**
* Calculating the margin percentage = (Margin/ Total base price)* 100
* @type {string}
*/
let marginPercentage = (Number(margin / totalBasePrice) * 100).toFixed(2);
log.debug("marginPercentage", marginPercentage);
if(marginPercentage === '-Infinity')
return false;
billRecord.setValue({
fieldId: "custbody_jj_margin_percentage",
value: marginPercentage
});
billRecord.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
}
} catch (err) {
log.debug("error@AfterSubmit", err)
}
}
return {afterSubmit}
});