This script sample outlines a 3-Way Matching in Vendor Bill to match the rate, amount, and quantity of the Purchase Order, Item Receipt and Vendor Bill. This workflow action script will be added as an action to an existing approval workflow for those bills created from a purchase order.
/**
* @NApiVersion 2.1
* @NScriptType WorkflowActionScript
*/
/*
* SteriTek-USA-NS
* STERI-955: 3-Way Matching in Vendor Bill
* **************************************************************************
* Date created : 13-01-2025
*
* Author: Jobin & Jismi IT Services LLP
*
* Script Description : This script is to set the status of the bill as approved when the 3 way matching is a success.The 3-way matching refers to the process of matching the rate, amount and quantity of the items in the purchase order, item receipt and the bill.
*
*
* REVISION HISTORY
*/
define(['N/record', 'N/search'],
/**
* @param{record} record
* @param{search} search
*/
(record, search) => {
/**
* Defines the WorkflowAction script trigger point.
* @param {Object} scriptContext
* @param {Record} scriptContext.newRecord - New record
* @param {Record} scriptContext.oldRecord - Old record
* @param {string} scriptContext.workflowId - Internal ID of workflow which triggered this action
* @param {string} scriptContext.type - Event type
* @param {Form} scriptContext.form - Current form that the script uses to interact with the record
* @since 2016.1
*/
const onAction = (scriptContext) => {
try {
let automateApproval = false;
log.audit('scriptContext.type', scriptContext.type);
if (scriptContext.type == 'create') {
let billRecord = scriptContext.newRecord; // Vendor Bill record
log.audit('in onAction function');
log.audit('billRecord.id', billRecord.id);
// let purchaseOrderId = billRecord.getValue('createdfrom'); // Source Purchase Order
let billDeatils = billRecordSearch(billRecord.id);
log.debug('billDeatils', billDeatils);
if (billDeatils != false) {
let itemReceiptCount = itemReceiptCountSearch(billDeatils.createdfrom);
log.debug('itemReceiptCount', itemReceiptCount);
let billCount = billCountSearch(billDeatils.createdfrom);
log.debug('billCount', billCount);
if (itemReceiptCount && billCount) {
let itemReceiptResults = itemReceiptSearch(billDeatils.createdfrom);
log.debug('itemReceiptResults', itemReceiptResults);
if (itemReceiptResults != false) {
let purchaseOrderResults = purchaseOrderSearch(billDeatils.createdfrom);
log.audit('purchaseOrderResults', purchaseOrderResults);
let includeInThreeWayMatching = threeWayMatchingFunction(billDeatils, purchaseOrderResults, itemReceiptResults);
if (includeInThreeWayMatching.billMatchesItemReceipt && includeInThreeWayMatching.billMatchesPurchaseOrder) {
billRecord.setValue({ fieldId: 'approvalstatus', value: '2' }); // Set the status of the bill as approved
automateApproval = true;
}
}
}
}
}
return automateApproval;
}
catch (Err) {
log.error("Error@onAction", Err);
}
}
function billRecordSearch(billId) {
try {
let billRecordObj = {};
let itemArray = [];
let flag = false;
let vendorbillSearchObj = search.create({
type: "vendorbill",
filters:
[
["type", "anyof", "VendBill"],
"AND",
["mainline", "is", "F"],
"AND",
["taxline", "is", "F"],
"AND",
["cogs", "is", "F"],
"AND",
["shipping", "is", "F"],
"AND",
["internalidnumber", "equalto", billId],
"AND",
// ["item", "noneof", "@NONE@"],
// "AND",
["createdfrom", "noneof", "@NONE@"],
"AND",
// ["createdfrom.status", "anyof", "PurchOrd:F"]
["createdfrom.status", "anyof", "PurchOrd:G", "PurchOrd:F"]
],
columns:
[
search.createColumn({ name: "internalid", label: "Internal ID" }),
search.createColumn({ name: "createdfrom", label: "Created From" }),
search.createColumn({ name: "statusref", label: "Status" }),
search.createColumn({ name: "item", label: "Item" }),
search.createColumn({ name: "rate", label: "Item Rate" }),
search.createColumn({ name: "total", label: "Amount (Transaction Total)" }),
search.createColumn({ name: "quantity", label: "Quantity" }),
search.createColumn({ name: "amount", label: "Amount" }),
search.createColumn({
name: "line",
join: "appliedToTransaction",
label: "orderline"
})
]
});
let searchResultCount = vendorbillSearchObj.runPaged().count;
log.debug("vendorbillSearchObj result count", searchResultCount);
if (searchResultCount > 0) {
vendorbillSearchObj.run().each(function (result) {
let itemObj = {};
let item = result.getValue({ name: 'item' });
let orderLine = result.getValue({ name: 'line', join: 'appliedToTransaction' });
if (item) {
let createdfrom = result.getValue({ name: 'createdfrom' });
let amount = Math.abs(result.getValue({ name: 'total' }));
billRecordObj["createdfrom"] = createdfrom;
billRecordObj["amount"] = amount;
if (!billRecordObj["items"]) {
billRecordObj["items"] = [];
}
if (!itemObj[orderLine]) {
itemObj[orderLine] = {};
}
itemObj[orderLine].rate = item;
itemObj[orderLine].rate = Math.abs(result.getValue({ name: 'rate' }));
itemObj[orderLine].amount = Math.abs(result.getValue({ name: 'amount' }));
itemObj[orderLine].quantity = Math.abs(result.getValue({ name: 'quantity' }));
// itemArray.push(itemObj);
billRecordObj["items"].push(itemObj);
flag = true;
return true;
}
else {
flag = false;
return false;
}
});
if (flag) {
return billRecordObj;
}
else {
return false;
}
}
else {
return false;
}
}
catch (Err) {
log.error("Error@billRecordSearch", Err);
}
}
function purchaseOrderSearch(internalId) {
try {
let purchaseOrderObj = {};
let purchaseorderSearchObj = search.create({
type: "purchaseorder",
filters:
[
["type", "anyof", "PurchOrd"],
"AND",
["internalidnumber", "equalto", internalId],
"AND",
["mainline", "is", "F"],
"AND",
["taxline", "is", "F"],
"AND",
["shipping", "is", "F"],
"AND",
["cogs", "is", "F"]
],
columns:
[
search.createColumn({ name: "item", label: "Item" }),
search.createColumn({ name: "rate", label: "Item Rate" }),
search.createColumn({ name: "amount", label: "Amount" }),
search.createColumn({ name: "total", label: "Amount (Transaction Total)" }),
search.createColumn({ name: "quantity", label: "Quantity" }),
search.createColumn({ name: "line", label: "Line ID" })
]
});
let searchResultCount = purchaseorderSearchObj.runPaged().count;
log.debug("purchaseorderSearchObj result count", searchResultCount);
if (searchResultCount > 0) {
purchaseorderSearchObj.run().each(function (result) {
let totalAmount = result.getValue({ name: 'total' });
purchaseOrderObj["totalAmount"] = totalAmount;
let item = result.getValue({ name: 'item' });
let lineId = result.getValue({ name: 'line' });
if (!purchaseOrderObj[lineId]) {
purchaseOrderObj[lineId] = {};
}
purchaseOrderObj[lineId].rate = result.getValue({ name: 'rate' });
purchaseOrderObj[lineId].amount = result.getValue({ name: 'amount' });
purchaseOrderObj[lineId].quantity = result.getValue({ name: 'quantity' });
purchaseOrderObj[lineId].item = item;
return true;
});
return purchaseOrderObj;
}
else {
return false;
}
}
catch (Err) {
log.error("Error@purchaseOrderSearch", Err);
}
}
function itemReceiptSearch(purchsaeOrderId) {
try {
let itemReceiptObj = {};
// Search for Item Receipt linked to the Purchase Order
let itemreceiptSearchObj = search.create({
type: "itemreceipt",
filters:
[
["type", "anyof", "ItemRcpt"],
"AND",
["createdfrom", "anyof", purchsaeOrderId],
"AND",
["mainline", "is", "F"],
"AND",
["shipping", "is", "F"],
"AND",
["taxline", "is", "F"],
"AND",
["cogs", "is", "F"]
],
columns:
[
search.createColumn({ name: "item", label: "Item" }),
search.createColumn({ name: "rate", label: "Item Rate" }),
search.createColumn({ name: "amount", label: "Amount" }),
search.createColumn({ name: "quantity", label: "Quantity" }),
search.createColumn({
name: "line",
join: "appliedToTransaction",
label: "orderline"
})
]
});
let searchResultCount = itemreceiptSearchObj.runPaged().count;
log.debug("itemreceiptSearchObj result count", searchResultCount);
if (searchResultCount > 0) {
itemreceiptSearchObj.run().each(function (result) {
let internalId = result.getValue({ name: 'item' });
let orderLine = result.getValue({ name: 'line', join: 'appliedToTransaction' });
if (!itemReceiptObj[orderLine]) {
itemReceiptObj[orderLine] = {};
}
itemReceiptObj[orderLine].quantity = Math.abs(result.getValue({ name: 'quantity' }));
itemReceiptObj[orderLine].item = internalId;
return true;
});
return itemReceiptObj;
}
else {
return false;
}
}
catch (Err) {
log.error("Error@itemReceiptSearch", Err);
}
}
function threeWayMatchingFunction(billRecordDeails, purchaseOrderResults, itemReceiptResults) {
try {
let billMatchesPurchaseOrder = false;
let billMatchesItemReceipt = false;
// Check if the bill matches the Purchase Order
if (billRecordDeails.amount == purchaseOrderResults.totalAmount) {
for (let item in billRecordDeails.items) {
let billItem = billRecordDeails.items[item];
let lineId = Object.keys(billItem)[0];
let itemDetails = billItem[lineId];
log.debug('billItem', billItem);
log.debug('itemDetails', itemDetails);
let purchaseOrderItem = purchaseOrderResults[lineId];
log.debug('purchaseOrderItem', purchaseOrderItem);
if (itemDetails.rate == purchaseOrderItem.rate && itemDetails.amount == purchaseOrderItem.amount && itemDetails.quantity == purchaseOrderItem.quantity) {
log.audit('in PO match if');
billMatchesPurchaseOrder = true;
}
else {
log.audit('in PO match else');
billMatchesPurchaseOrder = false;
break;
}
}
}
else {
//tolerance limit calculation
const toleranceLimit = 100;
let totalAmount = parseFloat(purchaseOrderResults.totalAmount);
log.debug('totalAmount', totalAmount);
log.debug("totalAmount", typeof totalAmount);
let billAmount = parseFloat(billRecordDeails.amount);
log.debug('billAmount', billAmount);
log.debug("billAmount", typeof billAmount);
let toleranceAmount = parseFloat(((totalAmount * 0.5) / 100).toFixed(2));
log.debug('toleranceAmount', toleranceAmount);
if (toleranceAmount >= toleranceLimit) {
toleranceAmount = toleranceLimit;
}
log.debug('toleranceAmount after', toleranceAmount);
let lowerLimit = parseFloat((totalAmount - toleranceAmount).toFixed(2));
log.debug('lowerLimit', lowerLimit);
let upperLimit = parseFloat((totalAmount + toleranceAmount).toFixed(2));
log.debug('upperLimit', upperLimit);
if (billAmount >= lowerLimit && billAmount <= upperLimit) {
for (let item in billRecordDeails.items) {
let billItem = billRecordDeails.items[item];
let lineId = Object.keys(billItem)[0];
let itemDetails = billItem[lineId];
log.debug('billItem', billItem);
log.debug('itemDetails', itemDetails);
let purchaseOrderItem = purchaseOrderResults[lineId];
log.debug('purchaseOrderItem', purchaseOrderItem);
if (itemDetails.quantity == purchaseOrderItem.quantity) {
log.audit('in PO TOLERANCE match if');
billMatchesPurchaseOrder = true;
}
else {
log.audit('in PO TOLERANCE match else');
billMatchesPurchaseOrder = false;
break;
}
}
}
else {
log.debug('Bill amount does not match with Purchase Order amount');
billMatchesPurchaseOrder = false;
}
}
// Check if the bill matches the Item Receipt
for (let item in billRecordDeails.items) {
let billItem = billRecordDeails.items[item];
let lineId = Object.keys(billItem)[0];
let itemDetails = billItem[lineId];
log.debug('billItem', billItem);
let itemReceiptItem = itemReceiptResults[lineId];
log.debug('itemReceiptItem', itemReceiptItem);
if (itemDetails.quantity == itemReceiptItem.quantity) {
log.audit('in IR match if');
billMatchesItemReceipt = true;
}
else {
log.audit('in IR match else');
billMatchesItemReceipt = false;
break;
}
}
log.audit('billMatchesPurchaseOrder', billMatchesPurchaseOrder);
log.audit('billMatchesItemReceipt', billMatchesItemReceipt);
return { billMatchesPurchaseOrder, billMatchesItemReceipt };
}
catch (Err) {
log.error("Error@threeWayMatchingFunction", Err);
}
}
function itemReceiptCountSearch(purchaseOrderId) {
try {
let count;
let itemreceiptSearchObj = search.create({
type: "itemreceipt",
filters:
[
["type", "anyof", "ItemRcpt"],
"AND",
["createdfrom", "anyof", purchaseOrderId]
],
columns:
[
search.createColumn({
name: "internalid",
summary: "COUNT",
label: "Internal ID"
})
]
});
let searchResultCount = itemreceiptSearchObj.runPaged().count;
log.debug("itemReceiptCountSearch result count", searchResultCount);
itemreceiptSearchObj.run().each(function (result) {
count = result.getValue({
name: "internalid",
summary: "COUNT"
});
return true;
});
log.audit('count', count);
if (count > 1) {
return false;
}
else {
if (count == 0) {
return false;
}
else {
return true;
}
}
}
catch (Err) {
log.error("Error@itemReceiptCountSearch", Err);
}
}
function billCountSearch(purchaseOrderId) {
try {
let count;
let vendorbillSearchObj = search.create({
type: "vendorbill",
filters:
[
["type", "anyof", "VendBill"],
"AND",
["createdfrom", "anyof", purchaseOrderId],
],
columns:
[
search.createColumn({
name: "internalid",
summary: "COUNT",
label: "Internal ID"
})
]
});
let searchResultCount = vendorbillSearchObj.runPaged().count;
log.debug("vendorbillSearchObj result count", searchResultCount);
vendorbillSearchObj.run().each(function (result) {
count = result.getValue({
name: "internalid",
summary: "COUNT"
});
return true;
});
log.audit('count', count);
if (count > 1) {
return false;
}
else {
return true;
}
}
catch (Err) {
log.error("Error@billCountSearch", Err);
}
}
return { onAction };
});