Typically, when there’s a change in the rate specified in purchase orders, the corresponding rates in previously generated item receipts won’t be automatically adjusted to the updated rate. To address this, we can employ the provided script to ensure that the rates in item receipts are synchronized with any changes made to the rate in the sales order.
/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
*/
/*************************************************************************************************************************
* BigRig Group
* Epic: BRGC-94
*
* BRGC-112 : Rate Synchronization Between Purchase orders (PO) and Item receipt (IR)
*
*********************************************************************************************************************
*
* Author: Jobin & Jismi IT Services
*
* Date Created : 1-November-2023
*
* Description :This is a user event script to sync the PO rate to IR.
* If the purchase order rate is modified, the item receipt rate will also be modified.
*
* REVISION HISTORY
*
* @version 1.0 BRGC-112 : 1-November-2023 : Created the initial build by JJ0053
*
**************************************************************************************************************************/
define(['N/record', 'N/search'],
/**
* @param{record} record
* @param{search} search
*/
(record, search) => {
const SUBSIDIARY = 17//Big Rig Tires CAN
/**
* function defined to fetch the IR with rate difference.
* This search will fetch only the IRs with rate different from PO
* @param {Number} poId
* @returns {Object}
*/
function getItemReceipts(poId) {
try {
let itemReceipts = {}
let purchaseorderSearchObj = search.create({
type: "purchaseorder",
filters:
[
["type", "anyof", "PurchOrd"],
"AND",
["applyingtransaction.type", "anyof", "ItemRcpt"],
"AND",
["internalid", "anyof", poId],
"AND",
["taxline", "is", "F"],
"AND",
["cogs", "is", "F"],
"AND",
["shipping", "is", "F"],
"AND",
["formulanumeric: CASE WHEN {fxrate}={applyingtransaction.fxrate} THEN 0 ELSE 1 END", "equalto", "1"],
"AND",
["item.type", "noneof", "NonInvtPart", "OthCharge", "Service"]
],
columns:
[
search.createColumn({ name: "internalid", label: "Internal ID" }),
search.createColumn({ name: "tranid", label: "Document Number" }),
search.createColumn({ name: "line", label: "Line ID" }),
search.createColumn({ name: "fxrate", label: "Item Rate" }),
search.createColumn({ name: "applyingtransaction", label: "Applying Transaction" }),
search.createColumn({
name: "line",
join: "applyingTransaction",
label: "Line ID"
}),
search.createColumn({
name: "fxrate",
join: "applyingTransaction",
label: "Item Rate"
})
]
});
purchaseorderSearchObj.run().each(function (result) {
// .run().each has a limit of 4,000 results
let irId = result.getValue({ name: "applyingtransaction", label: "Applying Transaction" });
let poRate = result.getValue({ name: "fxrate", label: "Item Rate" });
let irRate = result.getValue({
name: "fxrate",
join: "applyingTransaction",
label: "Item Rate"
});
let lineId = result.getValue({ name: "line", label: "Line ID" });
if (Number(poRate) - Number(irRate) != 0) {// only if there is rate difference in this item lines
if (!itemReceipts[irId])
itemReceipts[irId] = {};
itemReceipts[irId][lineId] = poRate;
}
return true;
});
return itemReceipts;
} catch (e) {
log.error('error@getItemReceipts', e);
return {};
}
}
/**
* function defined to update the IR with the new rate from PO
* @param {Object} itemReceipts
*/
function updateIrRate(itemReceipts) {
try {
for (let key in itemReceipts) {
try {
let irRateDiffLines = itemReceipts[key];
let irRecObj = record.load({ type: 'itemreceipt', id: key });
for (let l in irRateDiffLines) {
let lineNumber = irRecObj.findSublistLineWithValue({
sublistId: 'item',
fieldId: 'orderline',
value: l
});
if (lineNumber != -1)
irRecObj.setSublistValue({
sublistId: 'item',
fieldId: 'rate',
line: lineNumber,
value: irRateDiffLines[l]
});
}
irRecObj.save();
} catch (e) {
log.error('error@each item receipt', e)
}
}
} catch (e) {
log.error('error@updateIrRate', e);
}
}
/**
* 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 == 'edit') {
let newRecObj = scriptContext.newRecord;
let oldRecObj = scriptContext.oldRecord;
let subsidiary = newRecObj.getValue({ fieldId: 'subsidiary' });
if (subsidiary == SUBSIDIARY) {//Big Rig Tires CAN
let poId = newRecObj.id;
let itemReceipts = getItemReceipts(poId);
log.debug('itemReceipts', itemReceipts);
updateIrRate(itemReceipts);
}
}
} catch (e) {
log.error('error@afterSubmit', e);
}
}
return { afterSubmit }
});