This article explains how to retrieve price level mappings and fetch price change details for inventory items using a combination of saved search and SuiteQL queries in SuiteScript 2.x. The approach compares pricing records of different versions to highlight pricing updates.
Overview of Key Functions
- getPricelevel()
- Retrieves all price levels from NetSuite pricelevel record type and returns a map of internal ID to price level name for easy reference.
- getPricingWithVersionOne(itemIds)
- Uses SuiteQL to fetch pricing records for the specified items where the version is -1 (a baseline/initial version). Filters for price types 1 and 12 and quantity 0 (base price).
- getPricingWithMaxVersion(itemIds)
- Uses SuiteQL to get pricing records for the specified items with greatest version numbers excluding the version -1 records. Also retrieves the latest updated prices to compare against the version -1 baseline.
- getLatestVersionPrice(data)
- From an array of pricing records, filters and returns only the latest version record for each unique item and price type combination.
- combinePricingData(versionMinus1Data, greatestVersionData, updatedDetails)
- Compares the version -1 prices and the greatest version prices to discern price changes. Builds a combined dataset that includes old price, new price, version, update date, user, and highlights changes.
- getupdatedDetails(itemdetails)
- Runs a saved search on item system notes to get the latest update date and user who made price changes for the given item list.
- getDetails(filter)
- Runs a saved search to get item details filtered by system notes changes related to price lists.
- getPriceChangesDetails(itemId)
- Main function to get price change details for an item ID input. It fetches version -1 pricing, maximum version pricing, updated details, and combines them with price level names.
Usage Flow
- Call
getPriceChangesDetails(itemId)with an array or single item internal ID as input. - The function performs necessary saved searches and SuiteQL queries to retrieve pricing data and system note updates.
- It compares prices between versions to detect price changes, annotates with date and user info of change, and assigns human-readable price level names.
- Returns an array of combined price change records, each containing old and new prices, version info, date of change, user who made the change, and price level name.
Important Notes
- The queries filter for price types 1 and 12 and quantity equal to 0 to focus on list price types and base quantity.
- If there are discrepancies between the version -1 and max version prices, those records are flagged.
- This approach uses NetSuite’s SuiteQL for complex historical price queries, supplementing with saved search for system note audit info.
- Logging is included for debugging via
log.debugand error handling.
Sample Code:
/**
* @NApiVersion 2.1
* @NScriptType Suitelet // or Library / MapReduce helper as needed
*/
define([‘N/search’, ‘N/query’, ‘N/log’], (search, query, log) => {
/**
* Get map of pricelevel internalid -> name
*/
function getPricelevel() {
try {
const pricingSearchObj = search.create({
type: ‘pricelevel’,
filters: [],
columns: [
search.createColumn({ name: ‘name’, label: ‘Price Level’ }),
search.createColumn({ name: ‘internalid’, label: ‘Internal ID’ })
]
});
const priceLevelMap = {};
const searchResultCount = pricingSearchObj.runPaged().count;
log.debug(‘Pricing Search Result Count’, searchResultCount);
pricingSearchObj.run().each(result => {
const priceLevel = result.getValue({ name: ‘name’ });
const internalId = result.getValue({ name: ‘internalid’ });
if (priceLevel && internalId) {
priceLevelMap[internalId] = priceLevel;
}
return true;
});
log.debug(‘Price Level Map’, priceLevelMap);
return priceLevelMap;
} catch (error) {
log.error(‘Error @ getPricelevel’, error);
return {};
}
}
/**
* Fetch pricing details with VERSION = -1 (baseline).
* @param {string} itemIds comma-separated internalids for InvtItemPriceHistory.internalid
* @returns {Array<Object>}
*/
function getPricingWithVersionOne(itemIds) {
try {
if (!itemIds) return [];
const sql = `
SELECT
BUILTIN_RESULT.TYPE_INTEGER(InvtItemPriceHistory.item) AS item,
BUILTIN_RESULT.TYPE_STRING(InvtItemPriceHistory.item) AS itemname,
BUILTIN_RESULT.TYPE_INTEGER(InvtItemPriceHistory.pricetype) AS pricetype,
BUILTIN_RESULT.TYPE_FLOAT(InvtItemPriceHistory.quantity) AS quantity,
BUILTIN_RESULT.TYPE_CURRENCY(
InvtItemPriceHistory.price,
BUILTIN.CURRENCY(InvtItemPriceHistory.price)
) AS price,
BUILTIN_RESULT.TYPE_INTEGER(InvtItemPriceHistory.VERSION) AS VERSION,
BUILTIN_RESULT.TYPE_STRING(item.itemid) AS itemid
FROM
InvtItemPriceHistory,
item
WHERE
InvtItemPriceHistory.item = item.ID(+)
AND InvtItemPriceHistory.VERSION IN (‘-1’)
AND InvtItemPriceHistory.internalid IN (${itemIds})
AND InvtItemPriceHistory.pricetype IN (1, 12)
AND InvtItemPriceHistory.quantity = 0
ORDER BY
item.itemid ASC,
InvtItemPriceHistory.pricetype ASC,
InvtItemPriceHistory.quantity ASC
`;
const resultSet = query.runSuiteQL({ query: sql });
const rows = resultSet.asMappedResults();
log.debug(‘getPricingWithVersionOne resultSet’, rows);
return rows;
} catch (e) {
log.error(‘Error @ getPricingWithVersionOne’, e);
return [];
}
}
/**
* Fetch pricing details with VERSION != -1 and then keep the latest version per item/pricetype.
* @param {string} itemIds comma-separated internalids for InvtItemPriceHistory.internalid
* @returns {Array<Object>}
*/
function getPricingWithMaxVersion(itemIds) {
try {
if (!itemIds) return [];
const sql = `
SELECT
BUILTIN_RESULT.TYPE_INTEGER(historical.item) AS item,
BUILTIN_RESULT.TYPE_STRING(historical.item) AS itemname,
BUILTIN_RESULT.TYPE_INTEGER(historical.pricetype) AS pricetype,
BUILTIN_RESULT.TYPE_FLOAT(historical.quantity) AS quantity,
BUILTIN_RESULT.TYPE_CURRENCY(
historical.price,
BUILTIN.CURRENCY(historical.price)
) AS price,
BUILTIN_RESULT.TYPE_INTEGER(historical.VERSION) AS VERSION,
BUILTIN_RESULT.TYPE_STRING(item.itemid) AS itemid
FROM
InvtItemPriceHistory AS historical
LEFT JOIN item ON historical.item = item.ID
LEFT JOIN InvtItemPriceHistory AS latest ON
historical.item = latest.item
AND historical.pricetype = latest.pricetype
AND latest.VERSION = -1
AND latest.quantity = 0
WHERE
historical.VERSION != -1
AND historical.internalid IN (${itemIds})
AND historical.pricetype IN (1, 12)
AND historical.quantity = 0
ORDER BY
historical.VERSION DESC,
historical.item ASC
`;
const resultSet = query.runSuiteQL({ query: sql });
const rows = resultSet.asMappedResults();
log.debug(‘getPricingWithMaxVersion raw’, rows);
const latest = getLatestVersionPrice(rows);
log.debug(‘getPricingWithMaxVersion latest’, latest);
return latest;
} catch (e) {
log.error(‘Error @ getPricingWithMaxVersion’, e);
return [];
}
}
/**
* Get latest version record for each itemname-pricetype combo.
* @param {Array<Object>} data
* @returns {Array<Object>}
*/
function getLatestVersionPrice(data) {
try {
const latestPrices = {};
data.forEach(entry => {
const key = `${entry.itemname}-${entry.pricetype}`;
if (!latestPrices[key] || entry.version > latestPrices[key].version) {
latestPrices[key] = entry;
}
});
return Object.values(latestPrices);
} catch (e) {
log.error(‘Error @ getLatestVersionPrice’, e);
return [];
}
}
/**
* Combine baseline (-1) and latest version data + system note updates.
* @param {Array<Object>} versionMinus1Data
* @param {Array<Object>} greatestVersionData
* @param {Object} updatedDetails map: itemId -> { date, setBy }
* @returns {Array<Object>}
*/
function combinePricingData(versionMinus1Data, greatestVersionData, updatedDetails) {
try {
const combinedData = [];
// Compare baseline vs latest
versionMinus1Data.forEach(minus1Record => {
const matchingRecord = greatestVersionData.find(greatestRecord =>
greatestRecord.item === minus1Record.item &&
greatestRecord.quantity === minus1Record.quantity &&
greatestRecord.pricetype === minus1Record.pricetype
);
const updateDetails = updatedDetails[minus1Record.item] || {
date: ‘N/A’,
setBy: ‘N/A’
};
if (matchingRecord) {
const oldPrice = parseFloat(matchingRecord.price).toFixed(2);
const newPrice = parseFloat(minus1Record.price).toFixed(2);
if (oldPrice !== newPrice) {
combinedData.push({
item: minus1Record.item,
itemname: minus1Record.itemid,
pricetype: minus1Record.pricetype,
quantity: minus1Record.quantity,
version: matchingRecord.version,
oldprice: oldPrice,
newprice: newPrice,
date: updateDetails.date
? updateDetails.date.substring(
0,
updateDetails.date.indexOf(‘ ‘)
)
: ‘N/A’,
user: updateDetails.setBy,
highlight: true
});
}
} else {
// Only baseline exists – treat latest as 0
combinedData.push({
item: minus1Record.item,
itemname: minus1Record.itemid,
pricetype: minus1Record.pricetype,
quantity: minus1Record.quantity,
version: ‘N/A’,
oldprice: 0,
newprice: parseFloat(minus1Record.price).toFixed(2),
date: updateDetails.date
? updateDetails.date.substring(
0,
updateDetails.date.indexOf(‘ ‘)
)
: ‘N/A’,
user: updateDetails.setBy,
highlight: true
});
}
});
// Records that exist only in latest
greatestVersionData.forEach(greatestVersionRecord => {
const updateDetails = updatedDetails[greatestVersionRecord.item] || {
date: ‘N/A’,
setBy: ‘N/A’
};
const versionMinus1Record = versionMinus1Data.find(minus1Record =>
minus1Record.item === greatestVersionRecord.item &&
minus1Record.quantity === greatestVersionRecord.quantity &&
minus1Record.pricetype === greatestVersionRecord.pricetype
);
if (!versionMinus1Record) {
combinedData.push({
item: greatestVersionRecord.item,
itemname: greatestVersionRecord.itemid,
pricetype: greatestVersionRecord.pricetype,
quantity: greatestVersionRecord.quantity,
version: greatestVersionRecord.version,
oldprice: parseFloat(greatestVersionRecord.price).toFixed(2),
newprice: 0,
date: updateDetails.date
? updateDetails.date.substring(
0,
updateDetails.date.indexOf(‘ ‘)
)
: ‘N/A’,
user: updateDetails.setBy,
highlight: true
});
}
});
combinedData.sort((a, b) => {
if (a.item < b.item) return -1;
if (a.item > b.item) return 1;
if (a.pricetype < b.pricetype) return -1;
if (a.pricetype > b.pricetype) return 1;
return a.quantity – b.quantity;
});
return combinedData;
} catch (error) {
log.error(‘Error @ combinePricingData’, error);
return [];
}
}
/**
* System notes: last price update date + user per item.
* @param {Array<string>} itemdetails list of item internal IDs
* @returns {Object} map: itemId -> { date, setBy }
*/
function getupdatedDetails(itemdetails) {
try {
let filtersArray = [
[‘systemnotes.field’, ‘anyof’, ‘INVTITEM.PRICELIST’],
‘AND’,
[‘systemnotes.type’, ‘is’, ‘F’]
];
if (itemdetails && Array.isArray(itemdetails) && itemdetails.length > 0) {
filtersArray = filtersArray.concat([
‘AND’,
[‘internalid’, ‘anyof’, itemdetails]
]);
}
log.debug(‘getupdatedDetails filtersArray’, filtersArray);
const itemSearchObj = search.create({
type: ‘item’,
filters: filtersArray,
columns: [
search.createColumn({
name: ‘date’,
join: ‘systemNotes’,
summary: ‘MAX’,
sort: search.Sort.DESC,
label: ‘Date’
}),
search.createColumn({
name: ‘itemid’,
summary: ‘GROUP’,
label: ‘Name’
}),
search.createColumn({
name: ‘internalid’,
summary: ‘GROUP’,
label: ‘Internal ID’
}),
search.createColumn({
name: ‘name’,
join: ‘systemNotes’,
summary: ‘GROUP’,
label: ‘Set by’
})
]
});
const itemPriceUpdateMap = {};
itemSearchObj.run().each(result => {
const itemId = result.getValue({
name: ‘internalid’,
summary: ‘GROUP’
});
const date = result.getValue({
name: ‘date’,
join: ‘systemNotes’,
summary: ‘MAX’,
sort: search.Sort.DESC
});
const setBy = result.getText({
name: ‘name’,
join: ‘systemNotes’,
summary: ‘GROUP’
});
if (itemId && !itemPriceUpdateMap[itemId]) {
itemPriceUpdateMap[itemId] = {
date: date,
setBy: setBy
};
}
return true;
});
log.debug(‘Item Price Update Map’, itemPriceUpdateMap);
return itemPriceUpdateMap;
} catch (error) {
log.error(‘Error @ getupdatedDetails’, error);
return {};
}
}
/**
* Get list of item internal IDs for items that have pricelist changes.
* @param {Array} filter additional filter array for search
* @returns {Array<{itemId:string,itemName:string}>}
*/
function getDetails(filter) {
try {
let filtersArray = [
[‘systemnotes.field’, ‘anyof’, ‘INVTITEM.PRICELIST’],
‘AND’,
[‘systemnotes.type’, ‘is’, ‘F’]
];
if (filter && Array.isArray(filter) && filter.length > 0) {
filtersArray = filtersArray.concat(filter);
}
log.audit(‘getDetails filtersArray’, filtersArray);
const itemSearchObj = search.create({
type: ‘item’,
filters: filtersArray,
columns: [
search.createColumn({
name: ‘itemid’,
summary: ‘GROUP’,
label: ‘Name’
}),
search.createColumn({
name: ‘internalid’,
summary: ‘GROUP’,
label: ‘Internal ID’
})
]
});
const searchResultCount = itemSearchObj.runPaged().count;
log.debug(‘itemSearchObj result count’, searchResultCount);
if (searchResultCount > 0) {
const paged = runSearch(itemSearchObj, 3000);
return fetchSearchResult(paged, 0);
}
return [];
} catch (error) {
log.error(‘Error @ getDetails’, error);
return [];
}
}
function runSearch(searchObj, searchPageSize) {
try {
return searchObj.runPaged({ pageSize: searchPageSize });
} catch (error) {
log.error(‘Error @ runSearch’, error);
}
}
function fetchSearchResult(pagedData, pageIndex) {
try {
const searchPage = pagedData.fetch({ index: pageIndex });
const itemIds = [];
searchPage.data.forEach(result => {
const itemId = result.getValue({
name: ‘internalid’,
summary: ‘GROUP’
});
const itemName = result.getValue({
name: ‘itemid’,
summary: ‘GROUP’
});
if (itemId) {
itemIds.push({ itemId, itemName });
}
return true;
});
return itemIds;
} catch (e) {
log.error(‘error@fetchSearchResult’, e);
return [];
}
}
/**
* Main API: get price change details (Date, Price Level, Old Price, New Price, Version, User).
* @param {string} itemId comma-separated list of InvtItemPriceHistory.internalid
* (or a single internalid as string)
* @returns {Array<Object>}
*/
function getPriceChangesDetails(itemId) {
try {
// 1) Get items by item internalid filter (for system notes)
const filter = [‘AND’, [‘internalid’, ‘anyof’, itemId]];
const itemDetailsArr = getDetails(filter);
const itemInternalIds = itemDetailsArr.map(obj => obj.itemId);
// 2) Get system note update details
const updatedMap = itemInternalIds.length
? getupdatedDetails(itemInternalIds)
: {};
// 3) Pricing history (baseline & latest)
const versionOneResults = getPricingWithVersionOne(itemId);
const maxVersionResults = getPricingWithMaxVersion(itemId);
// 4) Combine & enrich
const combinedPricingData = combinePricingData(
versionOneResults,
maxVersionResults,
updatedMap
);
const priceLevelMap = getPricelevel();
combinedPricingData.forEach(record => {
record.pricetypeName =
priceLevelMap[record.pricetype] || ‘Unknown Price Level’;
});
log.debug(‘combinedPricingData’, combinedPricingData);
return combinedPricingData;
} catch (error) {
log.error(‘Error @ getPriceChangesDetails’, error);
return [];
}
}
return {
getPriceChangesDetails,
getPricelevel,
getPricingWithVersionOne,
getPricingWithMaxVersion,
getLatestVersionPrice,
combinePricingData,
getupdatedDetails,
getDetails
};
});