Client Script
/**
* @NApiVersion 2.1
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
/**
* MAGT-637
* *********************************************************************************************************************************
* AUTHOR: Jobin and Jismi
*
* Date created: 16-April-2024
*
* Description: This script is used to run on the field change and the downlodascsv button click in the suitelet page JJ SL Order Item MAGT 637.
*
* REVISION HISTORY:
* *********************************************************************************************************************************
*/
define(['N/search', 'N/url', 'N/currentRecord'],
/**
* @param{currentRecord} currentRecord
* @param{https} https
* @param{search} search
* @param{url} url
*/
function (search, url, currentRecord) {
// /**
// * Function to be executed after page is initialized.
// *
// * @param {Object} scriptContext
// * @param {Record} scriptContext.currentRecord - Current form record
// * @param {string} scriptContext.mode - The mode in which the record is being accessed (create, copy, or edit)
// *
// * @since 2015.2
// */
// function pageInit(scriptContext) {
//
// }
/**
* Function to be executed when field is changed.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @param {string} scriptContext.sublistId - Sublist name
* @param {string} scriptContext.fieldId - Field name
* @param {number} scriptContext.lineNum - Line number. Will be undefined if not a sublist or matrix field
* @param {number} scriptContext.columnNum - Line number. Will be undefined if not a matrix field
*
* @since 2015.2
*/
function fieldChanged(scriptContext) {
try {
let tempStorage = window.onbeforeunload;
window.onbeforeunload = function () { };
let locField, vendorField, minQtyField, parentItem, recordObj = scriptContext.currentRecord;
if (scriptContext.fieldId == 'custpage_location' || scriptContext.fieldId == 'custpage_vendor' || scriptContext.fieldId == 'custpage_minqty' || scriptContext.fieldId == 'custpage_parent' || scriptContext.fieldId == 'custpage_indexlist') {
locField = scriptContext.currentRecord.getValue({
fieldId: 'custpage_location'
});
console.log("location", locField);
vendorField = scriptContext.currentRecord.getValue({
fieldId: 'custpage_vendor'
});
minQtyField = scriptContext.currentRecord.getValue({
fieldId: 'custpage_minqty'
});
parentItem = scriptContext.currentRecord.getValue({
fieldId: 'custpage_parent'
});
console.log("parentItem", parentItem);
index = scriptContext.currentRecord.getValue({
fieldId: 'custpage_indexlist'
});
console.log("index", index);
let suiteletUrl = url.resolveScript({
deploymentId: 'customdeploy_jj_sl_order_item_magt_637',
scriptId: 'customscript_jj_sl_order_item_magt_637',
params: {
'locval': locField,
'vendorval': vendorField,
'minqtyval': minQtyField,
'parent': parentItem,
'pageindex': index
}
});
window.location.href = suiteletUrl;
}
let flag = false;
if (scriptContext.fieldId == 'custpage_vendorlist' || scriptContext.fieldId == 'custpage_currency' || scriptContext.fieldId == 'custpage_rateforeign' || scriptContext.fieldId == 'custpage_boxno' || scriptContext.fieldId == 'custpage_totalqty') {
if (scriptContext.fieldId == 'custpage_vendorlist') {
let currentItem = recordObj.getCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_iteminternal'
});
console.log("currentItem", currentItem);
let currentVendor = recordObj.getCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_vendorlist'
});
console.log("currentVendor", currentVendor);
let currency = currencySearch(currentVendor);
console.log("currency", currency);
if (currentVendor && currency) {
recordObj.setCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_currency',
value: currency
});
recordObj.setCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_checkbox',
value: true
});
let price = purchasePriceSearch(currentItem, currentVendor, currency);
console.log("price", price);
if (price) {
recordObj.setCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_rateforeign',
value: parseFloat(price)
});
}
flag = true;
}
if (!currentVendor) {
let purchasePrice = purchaseSearch(currentItem);
console.log("purchasePrice", purchasePrice);
console.log("currentVendor", currentVendor);
recordObj.setCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_currency',
value: ""
});
if (purchasePrice) {
recordObj.setCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_rateforeign',
value: purchasePrice
});
}
recordObj.setCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_checkbox',
value: false
});
}
}
console.log("flag", flag);
if (scriptContext.fieldId == 'custpage_currency' && flag != true) {
let currentItem = recordObj.getCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_iteminternal'
});
console.log("currentItem", currentItem);
let currentVendor = recordObj.getCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_vendorlist'
});
console.log("currentVendor", currentVendor);
let currency = recordObj.getCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_currency'
});
console.log("currency", currency);
// recordObj.setCurrentSublistValue({
// sublistId: 'custpage_sublistid',
// fieldId: 'custpage_checkbox',
// value: true
// });
if (currentVendor && currency) {
recordObj.setCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_checkbox',
value: true
});
let price = purchasePriceSearch(currentItem, currentVendor, currency);
console.log("price", price);
if (price) {
recordObj.setCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_rateforeign',
value: parseFloat(price)
});
}
}
}
if (scriptContext.fieldId == 'custpage_boxno') {
console.log("in box no field change");
let boxNumber = recordObj.getCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_boxno'
});
console.log("boxNumber", boxNumber);
let boxItemNumber = recordObj.getCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_box'
});
console.log("boxItemNumber", boxItemNumber);
let isBoxQty = recordObj.getCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_boxcheckbox'
});
console.log("isBoxQty", isBoxQty);
if (isBoxQty) {
recordObj.setCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_totalqty',
value: parseFloat(boxItemNumber) * parseFloat(boxNumber)
});
}
}
console.log("scriptContext.fieldId", scriptContext.fieldId);
if (scriptContext.fieldId == 'custpage_rateforeign' || scriptContext.fieldId == 'custpage_totalqty') {
console.log("in custpage_rateforeign");
let qty = recordObj.getCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_totalqty'
});
let price = recordObj.getCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_rateforeign'
});
console.log("qty", qty);
if (qty && price) {
let line = recordObj.getCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_line'
});
console.log("line", line);
let totalForeignField = recordObj.getSublistField({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_totalforeign',
line: line
});
totalForeignField.isDisabled = false;
let totalField = recordObj.getSublistField({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_total',
line: line
});
totalField.isDisabled = false;
let rateField = recordObj.getSublistField({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_rate',
line: line
});
rateField.isDisabled = false;
recordObj.setCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_totalforeign',
value: (parseFloat(price) * parseFloat(qty)).toFixed(4)
});
recordObj.setCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_rate',
value: parseFloat(price).toFixed(2)
});
let priceRounded = parseFloat(price).toFixed(2);
recordObj.setCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_total',
value: (priceRounded * qty).toFixed(2)
});
totalForeignField.isDisabled = true;
totalField.isDisabled = true;
rateField.isDisabled = true;
}
else {
let line = recordObj.getCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_line'
});
console.log("line", line);
let totalForeignField = recordObj.getSublistField({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_totalforeign',
line: line
});
totalForeignField.isDisabled = false;
let totalField = recordObj.getSublistField({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_total',
line: line
});
totalField.isDisabled = false;
let rateField = recordObj.getSublistField({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_rate',
line: line
});
rateField.isDisabled = false;
recordObj.setCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_totalforeign',
value: '0.0000'
});
recordObj.setCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_rate',
value: ''
});
let priceRounded = parseFloat(price).toFixed(2);
recordObj.setCurrentSublistValue({
sublistId: 'custpage_sublistid',
fieldId: 'custpage_total',
value: '0.00'
});
totalForeignField.isDisabled = true;
totalField.isDisabled = true;
rateField.isDisabled = true;
}
}
flag = false;
}
window.onbeforeunload = tempStorage;
}
catch (Err) {
console.log('Error@fieldchanged', Err);
}
}
// /**
// * Function to be executed when field is slaved.
// *
// * @param {Object} scriptContext
// * @param {Record} scriptContext.currentRecord - Current form record
// * @param {string} scriptContext.sublistId - Sublist name
// * @param {string} scriptContext.fieldId - Field name
// *
// * @since 2015.2
// */
// function postSourcing(scriptContext) {
// }
// /**
// * Function to be executed after sublist is inserted, removed, or edited.
// *
// * @param {Object} scriptContext
// * @param {Record} scriptContext.currentRecord - Current form record
// * @param {string} scriptContext.sublistId - Sublist name
// *
// * @since 2015.2
// */
// function sublistChanged(scriptContext) {
// }
// /**
// * Function to be executed after line is selected.
// *
// * @param {Object} scriptContext
// * @param {Record} scriptContext.currentRecord - Current form record
// * @param {string} scriptContext.sublistId - Sublist name
// *
// * @since 2015.2
// */
// function lineInit(scriptContext) {
// }
// /**
// * Validation function to be executed when field is changed.
// *
// * @param {Object} scriptContext
// * @param {Record} scriptContext.currentRecord - Current form record
// * @param {string} scriptContext.sublistId - Sublist name
// * @param {string} scriptContext.fieldId - Field name
// * @param {number} scriptContext.lineNum - Line number. Will be undefined if not a sublist or matrix field
// * @param {number} scriptContext.columnNum - Line number. Will be undefined if not a matrix field
// *
// * @returns {boolean} Return true if field is valid
// *
// * @since 2015.2
// */
// function validateField(scriptContext) {
//
// }
// /**
// * Validation function to be executed when sublist line is committed.
// *
// * @param {Object} scriptContext
// * @param {Record} scriptContext.currentRecord - Current form record
// * @param {string} scriptContext.sublistId - Sublist name
// *
// * @returns {boolean} Return true if sublist line is valid
// *
// * @since 2015.2
// */
// function validateLine(scriptContext) {
// }
// /**
// * Validation function to be executed when sublist line is inserted.
// *
// * @param {Object} scriptContext
// * @param {Record} scriptContext.currentRecord - Current form record
// * @param {string} scriptContext.sublistId - Sublist name
// *
// * @returns {boolean} Return true if sublist line is valid
// *
// * @since 2015.2
// */
// function validateInsert(scriptContext) {
// }
// /**
// * Validation function to be executed when record is deleted.
// *
// * @param {Object} scriptContext
// * @param {Record} scriptContext.currentRecord - Current form record
// * @param {string} scriptContext.sublistId - Sublist name
// *
// * @returns {boolean} Return true if sublist line is valid
// *
// * @since 2015.2
// */
// function validateDelete(scriptContext) {
// }
// /**
// * Validation function to be executed when record is saved.
// *
// * @param {Object} scriptContext
// * @param {Record} scriptContext.currentRecord - Current form record
// * @returns {boolean} Return true if record is valid
// *
// * @since 2015.2
// */
// function saveRecord(scriptContext) {
// }
function currencySearch(currentVendor) {
try {
let currencyId;
let vendorSearchObj = search.create({
type: "vendor",
filters:
[
["internalidnumber", "equalto", currentVendor]
],
columns:
[
search.createColumn({ name: "currency", label: "Currency" })
]
});
// let searchResultCount = vendorSearchObj.runPaged().count;
// log.debug("vendorSearchObj result count", searchResultCount);
vendorSearchObj.run().each(function (result) {
currencyId = result.getValue(vendorSearchObj.columns[0]);
return true;
});
return currencyId;
}
catch (Err) {
console.log('Error@currencySearch', Err);
}
}
function purchasePriceSearch(item, vendor, currency) {
try {
let price;
let itemSearchObj = search.create({
type: "item",
filters:
[
["internalidnumber", "equalto", item],
"AND",
["othervendor", "anyof", vendor],
"AND",
["vendorpricecurrency", "anyof", currency]
],
columns:
[
search.createColumn({ name: "othervendor", label: "Supplier" }),
search.createColumn({ name: "vendorcostentered", label: "Supplier Price (Entered)" }),
search.createColumn({ name: "vendorpricecurrency", label: "Supplier Price Currency" })
]
});
// let searchResultCount = itemSearchObj.runPaged().count;
// log.debug("itemSearchObj result count", searchResultCount);
itemSearchObj.run().each(function (result) {
price = result.getValue(itemSearchObj.columns[1]);
return true;
});
log.debug("price", price);
return price;
}
catch (Err) {
console.log('Error@purchasePriceSearch', Err);
}
}
function purchaseSearch(item) {
try {
let purchasePrice;
let itemSearchObj = search.create({
type: "item",
filters:
[
["internalidnumber", "equalto", item]
],
columns:
[
search.createColumn({ name: "cost", label: "Purchase Price" })
]
});
itemSearchObj.run().each(function (result) {
purchasePrice = result.getValue(itemSearchObj.columns[0]);
return true;
});
return purchasePrice;
}
catch (Err) {
console.log("Error@purchaseSearch", Err);
}
}
function downloadasCSV() {
try {
let record = currentRecord.get();
let sublistData = sublistDataCollect(record);
console.log("sublistData", sublistData);
let csvContent = 'ufeff'; // Adding BOM for UTF-8 encoding
csvContent += '"Location","Department","Class","Item","Description","Vendor","Currency","Purchase Contract","Units","Quantity Available","Quantity Backordered","Quantity On Order","Reorder Point","Preferred Stock Level","Suggested Quantity","Number Of Items Per Box","Number Of Boxes To Order","Total Quantity","Total Quantity Based On Box Quantity","Rate(Foreign Currency)","Total(Foreign Currency)","Rate","Total"n';
for (let i = 0; i < sublistData.length; i++) {
console.log("i", i);
csvContent += '"' + sublistData[i].loc + '",';
csvContent += '"' + sublistData[i].dept + '",';
csvContent += '"' + sublistData[i].class + '",';
csvContent += '"' + sublistData[i].item + '",';
csvContent += '"' + sublistData[i].desc + '",';
csvContent += '"' + sublistData[i].vendor + '",';
csvContent += '"' + sublistData[i].currency + '",';
csvContent += '"' + sublistData[i].contract + '",';
csvContent += '"' + sublistData[i].unit + '",';
csvContent += '"' + sublistData[i].available + '",';
csvContent += '"' + sublistData[i].backordered + '",';
csvContent += '"' + sublistData[i].onorder + '",';
csvContent += '"' + sublistData[i].reorderpoint + '",';
csvContent += '"' + sublistData[i].stocklevel + '",';
csvContent += '"' + sublistData[i].suggestedqty + '",';
csvContent += '"' + sublistData[i].iteminbox + '",';
csvContent += '"' + sublistData[i].boxes + '",';
csvContent += '"' + sublistData[i].totalqty + '",';
csvContent += '"' + sublistData[i].qtyonboxes + '",';
csvContent += '"' + sublistData[i].rateforeign + '",';
csvContent += '"' + sublistData[i].totalforeign + '",';
csvContent += '"' + sublistData[i].rate + '",';
csvContent += '"' + sublistData[i].total + '"n';
}
let hiddenElement = document.createElement('a');
hiddenElement.href = 'data:text/csv;charset=utf-8,' + encodeURIComponent(csvContent);
hiddenElement.target = '_blank';
hiddenElement.download = 'Order_Items_Data.csv';
document.body.appendChild(hiddenElement); // Append the element to the DOM
hiddenElement.click();
document.body.removeChild(hiddenElement); // Clean up the DOM
}
catch (Err) {
console.log("Error@downloadasCSV", Err);
}
}
/**
* This function gets the data from the sublist of the page.
* @param {Object} scriptContext
* @returns {Array}
*/
function sublistDataCollect(record) {
try {
let resultArray = [];
let lineCount = record.getLineCount({
sublistId: "custpage_sublistid",
});
console.log("lineCount", lineCount);
for (let i = 0; i < lineCount; i++) {
let lineData = {};
let loc = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_location",
line: i,
});
if (loc) {
lineData.loc = loc;
}
else {
lineData.loc = '';
}
let dept = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_dept",
line: i,
});
if (dept) {
lineData.dept = dept;
}
else {
lineData.dept = '';
}
classvalue = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_class",
line: i,
});
if (classvalue) {
lineData.class = classvalue;
}
else {
lineData.class = '';
}
lineData.item = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_item",
line: i,
});
desc = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_description",
line: i,
});
if (desc) {
let replacedDesc = desc.replace(/"/g, '""');
lineData.desc = replacedDesc;
}
else {
lineData.desc = '';
}
vendor = record.getSublistText({
sublistId: "custpage_sublistid",
fieldId: "custpage_vendorlist",
line: i,
});
if (vendor) {
let replacedVendor = vendor.replace(/"/g, '""');
lineData.vendor = replacedVendor;
}
else {
lineData.vendor = '';
}
currency = record.getSublistText({
sublistId: "custpage_sublistid",
fieldId: "custpage_currency",
line: i,
});
if (currency) {
lineData.currency = currency;
}
else {
lineData.currency = '';
}
contract = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_contract",
line: i,
});
if (contract) {
lineData.contract = contract;
}
else {
lineData.contract = '';
}
unit = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_units",
line: i,
});
if (unit) {
lineData.unit = unit;
}
else {
lineData.unit = '';
}
lineData.available = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_available",
line: i,
});
lineData.backordered = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_backorder",
line: i,
});
lineData.onorder = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_order",
line: i,
});
lineData.reorderpoint = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_reorder",
line: i,
});
lineData.stocklevel = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_preferredstock",
line: i,
});
lineData.suggestedqty = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_qty",
line: i,
});
lineData.iteminbox = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_box",
line: i,
});
lineData.boxes = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_boxno",
line: i,
});
lineData.totalqty = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_totalqty",
line: i,
});
lineData.qtyonboxes = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_boxcheckbox",
line: i,
});
rateforeign = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_rateforeign",
line: i,
});
if (rateforeign) {
lineData.rateforeign = rateforeign;
}
else {
lineData.rateforeign = '';
}
totalforeign = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_totalforeign",
line: i,
});
if (totalforeign) {
lineData.totalforeign = totalforeign;
}
else {
lineData.totalforeign = '';
}
rate = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_rate",
line: i,
});
if (rate) {
lineData.rate = rate;
}
else {
lineData.rate = '';
}
total = record.getSublistValue({
sublistId: "custpage_sublistid",
fieldId: "custpage_total",
line: i,
});
if (total) {
lineData.total = total;
}
else {
lineData.total = '';
}
resultArray.push(lineData);
}
console.log("resultArray", resultArray);
return resultArray;
}
catch (Err) {
console.log("Error@sublistDataCollect", Err);
}
}
return {
// pageInit: pageInit,
fieldChanged: fieldChanged,
downloadasCSV: downloadasCSV
// postSourcing: postSourcing,
// sublistChanged: sublistChanged,
// lineInit: lineInit,
// validateField: validateField
// validateLine: validateLine,
// validateInsert: validateInsert,
// validateDelete: validateDelete,
// saveRecord: saveRecord
};
});
Suitelet Script
/**
* @NApiVersion 2.1
* @NScriptType Suitelet
*/
/**
* MAGT-637
* *********************************************************************************************************************************
* AUTHOR: Jobin and Jismi
*
* Date created: 16-April-2024
*
* Description: This script is used to create suitelet page similar to the order items page. It shows items to be purchased based on the reorder point only.
*
* REVISION HISTORY:
* *********************************************************************************************************************************
*/
define(['N/search', 'N/ui/serverWidget'],
/**
* @param{search} search
* @param{serverWidget} serverWidget
*/
(search, serverWidget) => {
/**
* Defines the Suitelet script trigger point.
* @param {Object} scriptContext
* @param {ServerRequest} scriptContext.request - Incoming request
* @param {ServerResponse} scriptContext.response - Suitelet response
* @since 2015.2
*/
const onRequest = (scriptContext) => {
try {
if (scriptContext.request.method === 'GET') {
const pageSize = 100;
let form = serverWidget.createForm({
title: 'Order Items (With Box Quantity)'
});
let downloadButton = form.addButton({
id: "custpage_downloadcsv",
label: "Download CSV",
functionName: 'downloadasCSV'
});
let location = form.addField({
id: 'custpage_location',
label: 'Location',
type: serverWidget.FieldType.SELECT,
source: 'location'
});
let vendor = form.addField({
id: 'custpage_vendor',
label: 'Vendor',
type: serverWidget.FieldType.SELECT,
source: 'vendor'
});
let prefrredVendor = form.addField({
id: 'custpage_preferredvendor',
label: 'INCLUDE ITEMS WITH NO PREFERRED VENDOR',
type: serverWidget.FieldType.CHECKBOX
});
prefrredVendor.defaultValue = 'T';
prefrredVendor.updateDisplayType({
displayType: serverWidget.FieldDisplayType.DISABLED
});
let parentItem = form.addField({
id: 'custpage_parent',
label: 'Parent ITEM',
type: serverWidget.FieldType.SELECT
});
let parentItemsResult = parentItemSearch();
parentItem.addSelectOption({
value: "",
text: "",
isSelected: true
});
for (let i = 0; i < parentItemsResult.length; i++) {
parentItem.addSelectOption({
value: parentItemsResult[i],
text: parentItemsResult[i]
});
}
let minQty = form.addField({
id: 'custpage_minqty',
label: 'Minimum Quantity',
type: serverWidget.FieldType.INTEGER
});
let tabFirst = form.addSubtab({
id: 'custpage_tabfirst',
label: 'Reorder Point Items'
});
let indexField = form.addField({
id: 'custpage_indexlist',
type: serverWidget.FieldType.SELECT,
label: 'Page',
container: 'custpage_tabfirst'
});
let sublist = form.addSublist({
id: 'custpage_sublistid',
type: serverWidget.SublistType.LIST,
label: 'Sublist1',
tab: 'custpage_tabfirst'
});
let selectBox = sublist.addField({
id: 'custpage_checkbox',
type: serverWidget.FieldType.CHECKBOX,
label: 'Select'
});
selectBox.updateDisplayType({
displayType: serverWidget.FieldDisplayType.DISABLED
});
let lineId = sublist.addField({
id: 'custpage_line',
type: serverWidget.FieldType.INTEGER,
label: 'Line'
});
lineId.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
sublist.addField({
id: 'custpage_location',
type: serverWidget.FieldType.TEXT,
label: 'Location'
});
sublist.addField({
id: 'custpage_dept',
type: serverWidget.FieldType.TEXT,
label: 'Department'
});
sublist.addField({
id: 'custpage_class',
type: serverWidget.FieldType.TEXT,
label: 'Class'
});
let item = sublist.addField({
id: 'custpage_item',
type: serverWidget.FieldType.TEXT,
label: 'Item'
});
item.updateDisplayType({
displayType: serverWidget.FieldDisplayType.DISABLED
});
let itemId = sublist.addField({
id: 'custpage_iteminternal',
type: serverWidget.FieldType.TEXT,
label: 'Item'
});
itemId.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
sublist.addField({
id: 'custpage_description',
type: serverWidget.FieldType.TEXT,
label: 'Description'
});
sublist.addField({
id: 'custpage_vendorlist',
type: serverWidget.FieldType.SELECT,
label: 'Vendor',
source: 'vendor'
});
sublist.addField({
id: 'custpage_currency',
type: serverWidget.FieldType.SELECT,
label: 'Currency',
source: 'currency'
});
sublist.addField({
id: 'custpage_contract',
type: serverWidget.FieldType.SELECT,
label: 'Purchase Contract',
source: 'purchasecontract'
});
sublist.addField({
id: 'custpage_units',
type: serverWidget.FieldType.TEXT,
label: 'Units'
});
sublist.addField({
id: 'custpage_available',
type: serverWidget.FieldType.INTEGER,
label: 'Available'
});
sublist.addField({
id: 'custpage_backorder',
type: serverWidget.FieldType.INTEGER,
label: 'Back Ordered'
});
sublist.addField({
id: 'custpage_order',
type: serverWidget.FieldType.INTEGER,
label: 'On Order'
});
sublist.addField({
id: 'custpage_reorder',
type: serverWidget.FieldType.FLOAT,
label: 'Reorder Point'
});
sublist.addField({
id: 'custpage_preferredstock',
type: serverWidget.FieldType.FLOAT,
label: 'Preferred Stock Level'
});
let qtyField = sublist.addField({
id: 'custpage_qty',
type: serverWidget.FieldType.FLOAT,
label: 'Suggested Quantity'
});
let boxField = sublist.addField({
id: 'custpage_box',
type: serverWidget.FieldType.FLOAT,
label: 'Box Quantity (Number of items per box)'
});
boxField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.ENTRY
});
let boxNoField = sublist.addField({
id: 'custpage_boxno',
type: serverWidget.FieldType.FLOAT,
label: 'Number of boxes to Order'
});
boxNoField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.ENTRY
});
let totalqtyField = sublist.addField({
id: 'custpage_totalqty',
type: serverWidget.FieldType.FLOAT,
label: 'Total Quantity'
});
totalqtyField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.ENTRY
});
let boxCheckBox = sublist.addField({
id: 'custpage_boxcheckbox',
type: serverWidget.FieldType.CHECKBOX,
label: 'Total Quantity based on Box Quantity'
});
boxCheckBox.updateDisplayType({
displayType: serverWidget.FieldDisplayType.DISABLED
});
let rateForeginField = sublist.addField({
id: 'custpage_rateforeign',
type: serverWidget.FieldType.FLOAT,
label: 'Rate (FOREIGN CURRENCY)'
});
rateForeginField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.ENTRY
});
let totalForeignField = sublist.addField({
id: 'custpage_totalforeign',
type: serverWidget.FieldType.FLOAT,
label: 'Total (FOREIGN CURRENCY)'
});
totalForeignField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.ENTRY
});
let rateField = sublist.addField({
id: 'custpage_rate',
type: serverWidget.FieldType.FLOAT,
label: 'Rate'
});
rateField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.ENTRY
});
let totalField = sublist.addField({
id: 'custpage_total',
type: serverWidget.FieldType.FLOAT,
label: 'Total'
});
totalField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.ENTRY
});
form.clientScriptFileId = 5587688;
let locationSelected = scriptContext.request.parameters.locval;
let vendorSelected = scriptContext.request.parameters.vendorval;
let minqtySelected = scriptContext.request.parameters.minqtyval;
let parentItemSelected = scriptContext.request.parameters.parent;
let pageIndex = scriptContext.request.parameters.pageindex;
if (locationSelected) {
location.defaultValue = locationSelected;
}
if (minqtySelected) {
minQty.defaultValue = minqtySelected;
}
if (vendorSelected) {
vendor.defaultValue = vendorSelected;
}
if (parentItemSelected) {
log.debug("parentItemSelected in if onrequest", parentItemSelected);
parentItem.defaultValue = parentItemSelected;
}
log.debug("locationSelected", locationSelected);
log.debug("minqtySelected", minqtySelected);
log.debug("vendorSelected", vendorSelected);
log.debug("parentItemSelected", parentItemSelected);
log.debug("pageIndex", pageIndex);
let searchObj = orderitemSearch(locationSelected, vendorSelected, minqtySelected, parentItemSelected);
log.debug("searchObj", searchObj);
let pagedData = searchObj.runPaged({ pageSize: pageSize });
let totalLines = pagedData.count;
log.debug("totalLines", totalLines);
let totalPages = Math.ceil(totalLines / pageSize);
log.debug("totalPages", totalPages);
for (let i = 0; i < totalPages; i++) {
let startIndex = (i * pageSize) + 1;
let endIndex = Math.min((i + 1) * pageSize, totalLines);
indexField.addSelectOption({
value: i,
text: startIndex + '-' + endIndex + ' of ' + totalLines,
isSelected: (i == pageIndex)
});
}
let unitAbbreviation = unitAbbreviationSearch();
if (totalLines != 0) {
if (pageIndex < 0 || pageIndex >= totalPages || !pageIndex) {
pageIndex = 0;
}
// Get the search results for the current page
let resultArray = [];
let currentPage = pagedData.fetch({ index: pageIndex });
currentPage.data.forEach(function (result) {
let resultObj = {};
resultObj.location = result.getText(searchObj.columns[0]);
resultObj.dept = result.getText(searchObj.columns[2]);
resultObj.itemclass = result.getText(searchObj.columns[3]);
resultObj.name = result.getValue(searchObj.columns[4]);
resultObj.description = result.getValue(searchObj.columns[5]);
resultObj.unit = result.getText(searchObj.columns[7]);
resultObj.price = result.getValue(searchObj.columns[8]);
resultObj.available = result.getValue(searchObj.columns[9]);
resultObj.backordered = result.getValue(searchObj.columns[10]);
resultObj.onorder = result.getValue(searchObj.columns[11]);
resultObj.reorder = result.getValue(searchObj.columns[12]);
resultObj.preferred = result.getValue(searchObj.columns[13]);
resultObj.suggestedqty = result.getValue(searchObj.columns[16]);
resultObj.box = result.getValue(searchObj.columns[17]);
resultObj.boxNumber = result.getValue(searchObj.columns[18]);
resultObj.totalqty = result.getValue(searchObj.columns[19]);
resultObj.internal = result.getValue(searchObj.columns[20]);
resultArray.push(resultObj);
return true;
});
settingSublist(resultArray, unitAbbreviation, sublist, boxField, totalForeignField, rateField, totalField);
}
scriptContext.response.writePage(form);
}
}
catch (Err) {
log.error("Error@onrequest", Err);
}
/**
* This function creates search in which the result resembles the data in the standard order item pages's reorder point subtab.
* @returns {Array}
*/
function orderitemSearch(locationSelected, vendorSelected, minqtySelected, parentItemSelected) {
try {
let resultArray = [];
let itemSearchObj = search.create({
type: "item",
filters:
[
["type", "anyof", "InvtPart", "Assembly"],
"AND",
["formulanumeric: case when NVL({locationreorderpoint},0)-NVL({locationquantityavailable},0)-NVL({locationquantityonorder},0)+NVL({locationquantitybackordered},0) >= 0 AND NVL({locationpreferredstocklevel},{locationreorderpoint})-NVL({locationquantityavailable},0)-NVL({locationquantityonorder},0)+NVL({locationquantitybackordered},0)> 0 then ROUND(NVL({locationpreferredstocklevel},{locationreorderpoint})-NVL({locationquantityavailable},0)-NVL({locationquantityonorder},0)+NVL({locationquantitybackordered},0)) else 0 end", "notequalto", "0"],
"AND",
["locationreorderpoint", "greaterthanorequalto", "0"],
"AND",
["supplyreplenishmentmethod", "anyof", "REORDER_POINT"]
],
columns:
[
search.createColumn({ name: "inventorylocation", label: "Inventory Location" }),
search.createColumn({
name: "subsidiary",
join: "inventoryLocation",
label: "Location Subsidiary"
}),
search.createColumn({ name: "departmentnohierarchy", label: "Department" }),
search.createColumn({ name: "classnohierarchy", label: "Class" }),
search.createColumn({ name: "itemid", label: "Name" }),
search.createColumn({ name: "purchasedescription", label: "Description" }),
search.createColumn({ name: "vendor", label: "Preferred Supplier" }),
search.createColumn({ name: "unitstype", label: "Primary Units Type" }),
search.createColumn({ name: "cost", label: "Purchase Price" }),
search.createColumn({ name: "locationquantityavailable", label: "Location Available" }),
search.createColumn({ name: "locationquantitybackordered", label: "Location Back Ordered" }),
search.createColumn({ name: "locationquantityonorder", label: "Location On Order" }),
search.createColumn({ name: "locationreorderpoint", label: "Location Reorder Point" }),
search.createColumn({ name: "locationpreferredstocklevel", label: "Location Preferred Stock Level" }),
search.createColumn({ name: "locationminimumorderquantity", label: "Location Minimum Order Quantity" }),
search.createColumn({
name: "formulanumeric",
formula: "NVL({locationquantityavailable},0) + NVL({locationquantityonorder},0) - NVL({locationquantitybackordered},0)",
label: "ATP"
}),
search.createColumn({
name: "formulanumeric",
formula: "CASE WHEN NVL({locationquantityavailable},0) + NVL({locationquantityonorder},0) - NVL({locationquantitybackordered},0) < NVL({locationreorderpoint},0) THEN NVL({locationpreferredstocklevel},0) - (NVL({locationquantityavailable},0) + NVL({locationquantityonorder},0) - NVL({locationquantitybackordered},0)) ELSE NVL({locationpreferredstocklevel},0) END",
label: "Suggested Quantity"
}),
search.createColumn({ name: "custitem_jj_box", label: "Box (no of items in one box)" }),
search.createColumn({
name: "formulanumeric",
formula: "CEIL((CASE WHEN NVL({locationquantityavailable},0) + NVL({locationquantityonorder},0) - NVL({locationquantitybackordered},0) < NVL({locationreorderpoint},0) THEN NVL({locationpreferredstocklevel},0) - (NVL({locationquantityavailable},0) + NVL({locationquantityonorder},0) - NVL({locationquantitybackordered},0)) ELSE NVL({locationpreferredstocklevel},0) END)/{custitem_jj_box})",
label: "Number of Box"
}),
search.createColumn({
name: "formulanumeric",
formula: "{custitem_jj_box}*(CEIL((CASE WHEN NVL({locationquantityavailable},0) + NVL({locationquantityonorder},0) - NVL({locationquantitybackordered},0) < NVL({locationreorderpoint},0) THEN NVL({locationpreferredstocklevel},0) - (NVL({locationquantityavailable},0) + NVL({locationquantityonorder},0) - NVL({locationquantitybackordered},0)) ELSE NVL({locationpreferredstocklevel},0) END)/{custitem_jj_box}))",
label: "Suggested Quantity (based on box)"
}),
search.createColumn({ name: "internalid", label: "Internal ID" })
]
});
// let searchResultCount = itemSearchObj.runPaged().count;
// log.debug("itemSearchObj result count", searchResultCount);
let searchFilter = itemSearchObj.filters;
log.debug("filter 1st", searchFilter);
log.debug("locationSelected", locationSelected);
log.debug("vendorSelected", vendorSelected);
log.debug("minqtySelected", minqtySelected);
log.debug("parentItemSelected", parentItemSelected);
if (locationSelected) {
log.debug("in location filter create");
searchFilter.push(search.createFilter({
name: "inventorylocation",
operator: "anyof",
values: locationSelected,
isor: false,
isnot: false,
leftparens: 0,
rightparens: 0
}));
}
if (vendorSelected) {
log.debug("in vendor filter create");
log.debug("vendorSelected", vendorSelected);
searchFilter.push(search.createFilter({
name: "othervendor",
operator: "anyof",
values: vendorSelected,
isor: false,
isnot: false,
leftparens: 0,
rightparens: 0
}));
}
if (minqtySelected) {
log.debug("in minqty filter create");
log.debug("minqtySelected", minqtySelected);
searchFilter.push(search.createFilter({
name: "formulanumeric",
operator: "greaterthanorequalto",
formula: "NVL({custitem_jj_box}*(CEIL((CASE WHEN NVL({locationquantityavailable},0) + NVL({locationquantityonorder},0) - NVL({locationquantitybackordered},0) < NVL({locationreorderpoint},0) THEN NVL({locationpreferredstocklevel},0) - (NVL({locationquantityavailable},0) + NVL({locationquantityonorder},0) - NVL({locationquantitybackordered},0)) ELSE NVL({locationpreferredstocklevel},0) END)/{custitem_jj_box})), CASE WHEN NVL({locationquantityavailable},0) + NVL({locationquantityonorder},0) - NVL({locationquantitybackordered},0) < NVL({locationreorderpoint},0) THEN NVL({locationpreferredstocklevel},0) - (NVL({locationquantityavailable},0) + NVL({locationquantityonorder},0) - NVL({locationquantitybackordered},0)) ELSE NVL({locationpreferredstocklevel},0) END)",
values: minqtySelected,
isor: false,
isnot: false,
leftparens: 0,
rightparens: 0
}));
}
if (parentItemSelected) {
log.debug("in else if parent filter create");
log.debug("parentItemSelected", parentItemSelected);
searchFilter.push(search.createFilter({
name: "name",
join: "parent",
operator: "is",
values: parentItemSelected,
isor: false,
isnot: false,
leftparens: 0,
rightparens: 0
}));
}
log.debug("filter", searchFilter);
return itemSearchObj;
}
catch (Err) {
log.error("Error@orderitemSearch", Err);
}
}
/**
* This function is to create the search for unit name and its abreviation name(plural).
* @returns {Array}
*/
function unitAbbreviationSearch() {
try {
let unitObj = {};
let unitstypeSearchObj = search.create({
type: "unitstype",
filters:
[
["baseunit", "is", "T"]
],
columns:
[
search.createColumn({ name: "pluralabbreviation", label: "Abbreviation Name(Plural)" }),
search.createColumn({ name: "unitname", label: "Unit Name" })
]
});
// let searchResultCount = unitstypeSearchObj.runPaged().count;
// log.debug("unitstypeSearchObj result count", searchResultCount);
unitstypeSearchObj.run().each(function (result) {
let abrevObject = {}, unit;
unit = result.getValue(unitstypeSearchObj.columns[1]);
abrevObject.abbreviation = result.getValue(unitstypeSearchObj.columns[0]);
unitObj[unit] = abrevObject;
return true;
});
log.debug("unit object", unitObj);
return unitObj;
}
catch (Err) {
log.error("Error@unitAbbreviationSearch", Err);
}
}
/**
* This function sets the sublist in the page.
* @param {Array} result
* @param {Array} unitAbbreviation
* @param {Object} sublist
* @param {Object} boxField
* @param {Object} totalForeignField
* @param {Object} rateField
* @param {Object} totalField
*/
function settingSublist(result, unitAbbreviation, sublist, boxField, totalForeignField, rateField, totalField) {
try {
for (let i = 0; i < result.length; i++) {
if (result[i].location) {
sublist.setSublistValue({
id: 'custpage_line',
line: i,
value: i
});
sublist.setSublistValue({
id: 'custpage_location',
line: i,
value: result[i].location
});
}
if (result[i].dept) {
sublist.setSublistValue({
id: 'custpage_dept',
line: i,
value: result[i].dept
});
}
if (result[i].itemclass) {
sublist.setSublistValue({
id: 'custpage_class',
line: i,
value: result[i].itemclass
});
}
if (result[i].name) {
sublist.setSublistValue({
id: 'custpage_item',
line: i,
value: result[i].name
});
}
if (result[i].internal) {
sublist.setSublistValue({
id: 'custpage_iteminternal',
line: i,
value: result[i].internal
});
}
if (result[i].description) {
sublist.setSublistValue({
id: 'custpage_description',
line: i,
value: result[i].description
});
}
log.debug("result[i].unit", result[i].unit);
if (result[i].unit) {
let abbreviation = unitAbbreviation[result[i].unit].abbreviation
log.debug("abbreviation", abbreviation);
sublist.setSublistValue({
id: 'custpage_units',
line: i,
value: abbreviation
});
}
if (result[i].available) {
sublist.setSublistValue({
id: 'custpage_available',
line: i,
value: result[i].available
});
}
else {
sublist.setSublistValue({
id: 'custpage_available',
line: i,
value: "0"
});
}
if (result[i].backordered) {
sublist.setSublistValue({
id: 'custpage_backorder',
line: i,
value: result[i].backordered
});
}
else {
sublist.setSublistValue({
id: 'custpage_backorder',
line: i,
value: "0"
});
}
if (result[i].onorder) {
sublist.setSublistValue({
id: 'custpage_order',
line: i,
value: result[i].onorder
});
}
else {
sublist.setSublistValue({
id: 'custpage_order',
line: i,
value: "0"
});
}
if (result[i].reorder) {
sublist.setSublistValue({
id: 'custpage_reorder',
line: i,
value: result[i].reorder
});
}
else {
sublist.setSublistValue({
id: 'custpage_reorder',
line: i,
value: "0"
});
}
if (result[i].preferred) {
sublist.setSublistValue({
id: 'custpage_preferredstock',
line: i,
value: result[i].preferred
});
}
else {
sublist.setSublistValue({
id: 'custpage_preferredstock',
line: i,
value: "0"
});
}
if (result[i].suggestedqty) {
sublist.setSublistValue({
id: 'custpage_qty',
line: i,
value: result[i].suggestedqty
});
}
else {
sublist.setSublistValue({
id: 'custpage_qty',
line: i,
value: "0"
});
}
if (result[i].box) {
sublist.setSublistValue({
id: 'custpage_box',
line: i,
value: result[i].box
});
boxField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.DISABLED
});
}
else {
sublist.setSublistValue({
id: 'custpage_box',
line: i,
value: "0"
});
boxField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.DISABLED
});
}
if (result[i].boxNumber) {
sublist.setSublistValue({
id: 'custpage_boxno',
line: i,
value: result[i].boxNumber
});
}
else {
sublist.setSublistValue({
id: 'custpage_boxno',
line: i,
value: "0"
});
}
if (result[i].totalqty) {
sublist.setSublistValue({
id: 'custpage_totalqty',
line: i,
value: result[i].totalqty
});
sublist.setSublistValue({
id: 'custpage_boxcheckbox',
line: i,
value: 'T'
});
}
else {
sublist.setSublistValue({
id: 'custpage_totalqty',
line: i,
value: result[i].suggestedqty
});
}
if (result[i].price) {
sublist.setSublistValue({
id: 'custpage_rateforeign',
line: i,
value: parseFloat(result[i].price || 0.00)
});
sublist.setSublistValue({
id: 'custpage_rate',
line: i,
value: parseFloat(result[i].price || 0.00)
});
}
sublist.setSublistValue({
id: 'custpage_totalforeign',
line: i,
value: (parseFloat(result[i].totalqty || result[i].suggestedqty) * parseFloat(result[i].price || 0.00)).toFixed(4)
});
sublist.setSublistValue({
id: 'custpage_total',
line: i,
value: (parseFloat(result[i].totalqty || result[i].suggestedqty) * parseFloat(result[i].price || 0.00)).toFixed(4)
});
totalForeignField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.DISABLED
});
rateField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.DISABLED
});
totalField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.DISABLED
});
}
}
catch (Err) {
log.error("Error@settingSublist", Err);
}
}
/**
* This function searches to items which are selected as parent of other items.
* @returns {Array}
*/
function parentItemSearch() {
try {
let parentArray = [];
let itemSearchObj = search.create({
type: "item",
filters:
[
["type", "anyof", "Assembly", "InvtPart"],
"AND",
["parent.internalidnumber", "isnotempty", ""],
"AND",
["parent.type", "noneof", "Assembly"]
],
columns:
[
search.createColumn({
name: "itemid",
join: "parent",
summary: "GROUP",
label: "Name",
sort: search.Sort.ASC
})
]
});
itemSearchObj.run().each(function (result) {
log.debug(" result.getValue(itemSearchObj.columns[0])", result.getValue(itemSearchObj.columns[0]));
parentArray.push(result.getValue(itemSearchObj.columns[0]));
return true;
});
log.debug("parentArray", parentArray);
return parentArray;
}
catch (Err) {
log.error("Error@parentItemSearch", Err);
}
}
}
return { onRequest }
});