Requirement
There is a custom record named “Bin Enhancement” associated with the inventory items. We can create multiple custom record for a single item. The custom record is used to store the bin number in a particular location from the item record.
We have a scheduled script to automate the creation or updation of this custom record under the following conditions:
CREATE : When no active custom record is created for a particular item.
UPDATE : When the bin numbers in item record and custom record are different.
So, we need to have a search which should return the custom record internal id when the bin numbers are differetn in both records and also need to return the custom reocrd internal id as zero when we need to create a new custom record for the item. So, in both cases the search should return the result.
Solution
/**
* Function to search for the list of inventory items for RF-SMART Item Bin Enhancement custom record creation or updation
* @returns {Array} - inventoryItemSearchArray - Array of objects containg inventory item details
*/
fetchInventoryItems1() {
try {
let inventoryItemSearchArray = [];
let inventoryItemSearchObj = search.create({
type: "item",
filters:
[
["type", "anyof", "InvtPart"],
"AND",
["isinactive", "is", "F"],
"AND",
["preferredbin", "is", "T"],
"AND",
["formulanumeric: Case when {custrecord_rfs_replenishment_rule_item.internalid} is null then 1 else (case when {custrecord_rfs_replenishment_rule_item.isinactive}= 'F' then 1 else 0 end) end", "equalto", "1"],
"AND",
["formulatext: {binnumber}", "doesnotstartwith", "Retail"],
"AND",
["formulatext: {binnumber}", "doesnotstartwith", "MF"],
"AND",
["formulatext: {binnumber}", "doesnotstartwith", "Mezz"],
"AND",
["binnumber.inactive", "is", "F"],
"AND",
["formulatext: {name}", "doesnotstartwith", "PRT"],
"AND",
["binnumber.location", "anyof", "7"],
"AND",
["custitemtmg_program_name.custrecord_closedprogram", "is", "F"],
"AND",
["custitemtmg_program_name", "noneof", "102", "11", "103", "89"],
"AND",
[["quantityonhand", "greaterthan", "0"], "OR", ["custitem_ninetydaysales", "greaterthan", "0"]],
"AND",
["custitem26.isinactive", "is", "F"],
// "AND",
// ["internalid", "anyof", "387526", "888491", "888492"],
"AND",
["max(formulanumeric: DECODE(MAX(case when {custrecord_rfs_replenishment_rule_item.internalid} IS NOT NULL THEN 1 ELSE 0 END),1, DECODE(MAX({custrecord_rfs_replenishment_rule_item.custrecord_rfs_replenishment_rule_bin}) KEEP(DENSE_RANK LAST ORDER BY {custrecord_rfs_replenishment_rule_item.internalid}),MAX({binnumber}),null,MAX({custrecord_rfs_replenishment_rule_item.internalid})), 0,0))", "isnotempty", ""]
],
columns:
[
search.createColumn({ name: "internalid", summary: "GROUP", label: "Internal ID" }),
search.createColumn({ name: "islotitem", summary: "GROUP", label: "Is Lot Numbered Item" }),
search.createColumn({ name: "isserialitem", summary: "GROUP", label: "Is Serialized Item" }),
search.createColumn({ name: "internalid", join: "CUSTRECORD_RFS_REPLENISHMENT_RULE_ITEM", summary: "MAX", label: "Internal ID" }),
search.createColumn({
name: "formulanumeric",
summary: "MAX",
formula: " DECODE(MAX(case when {custrecord_rfs_replenishment_rule_item.internalid} IS NOT NULL THEN 1 ELSE 0 END) ,1, DECODE(MAX({custrecord_rfs_replenishment_rule_item.custrecord_rfs_replenishment_rule_bin}) KEEP(DENSE_RANK LAST ORDER BY {custrecord_rfs_replenishment_rule_item.internalid}),MAX({binnumber}),null,MAX({custrecord_rfs_replenishment_rule_item.internalid})), 0,0)",
label: "Bin Internal Id"
}),
search.createColumn({ name: "binnumber", summary: "GROUP", label: "Preferred Bin" }),
search.createColumn({ name: "custrecord_classmin", join: "CUSTITEM26", summary: "GROUP", label: "Min" }),
search.createColumn({ name: "custrecord192", join: "CUSTITEM26", summary: "GROUP", label: "Max" }),
]
});
if (inventoryItemSearchObj.runPaged().count > 0) {
let searchResultCount = inventoryItemSearchObj.runPaged();
// Processing more than 4,000 results
searchResultCount.pageRanges.forEach(function (pageRange) {
let myPage = searchResultCount.fetch({ index: pageRange.index });
myPage.data.forEach(function (result) {
let singleItemObj = {};
singleItemObj.itemInternalId = result.getValue({ name: "internalid", summary: "GROUP", label: "Internal ID" });
singleItemObj.isLotItem = result.getValue({ name: "islotitem", summary: "GROUP", label: "Is Lot Numbered Item" });
singleItemObj.isSerialItem = result.getValue({ name: "isserialitem", summary: "GROUP", label: "Is Serialized Item" });
singleItemObj.binEnhancementRecInternalId = result.getValue({
name: "formulanumeric",
summary: "MAX",
formula: " DECODE(MAX(case when {custrecord_rfs_replenishment_rule_item.internalid} IS NOT NULL THEN 1 ELSE 0 END) ,1, DECODE(MAX({custrecord_rfs_replenishment_rule_item.custrecord_rfs_replenishment_rule_bin}) KEEP(DENSE_RANK LAST ORDER BY {custrecord_rfs_replenishment_rule_item.internalid}),MAX({binnumber}),null,MAX({custrecord_rfs_replenishment_rule_item.internalid})), 0,0)",
label: "Bin Internal Id"
});
singleItemObj.itemBinNumber = result.getValue({ name: "binnumber", summary: "GROUP", label: "Preferred Bin" });
singleItemObj.itemClassMin = result.getValue({ name: "custrecord_classmin", join: "CUSTITEM26", summary: "GROUP", label: "Min" });
singleItemObj.itemClassMax = result.getValue({ name: "custrecord192", join: "CUSTITEM26", summary: "GROUP", label: "Max" });
inventoryItemSearchArray.push(singleItemObj);
});
});
}
return inventoryItemSearchArray;
}
catch (e) {
log.error("Error @ fetchInventoryItems1", e);
return [];
}
},