This script calculates the ETA and the quantity that will be available on the ETA. This script calculates ETA for both inventory items and Kit items.
Git- ETA calculation
/**
* @NApiVersion 2.1
* @NScriptType Suitelet
*/
define(['N/search'],
/**
* @param{search} search
*/
(search) => {
/**
* 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) => {
if (scriptContext.request.method == "GET") {
try {
let adobeId = scriptContext.request.parameters.productId
let locationObj = locationSerach()
let itemDetilsObj = itemSearchForQtyAvailableAndArrivalDate(adobeId);
let itemArr = Object.values(itemDetilsObj)
let type = itemArr[0][0].type
let id = itemArr[0][0].id
let memberObj = {}
let kitItemDetailsObj = {}
let itemArrivalDetails = {}
if (type == "Kit") {
memberObj = memeberItem(adobeId)
let memberArr = Object.keys(memberObj)
kitItemDetailsObj = itemSearchForQtyAvailableAndArrivalDate(memberArr)
itemArrivalDetails = arrivalDatePO(memberArr)
} else {
itemArrivalDetails = arrivalDatePO(adobeId)
}
let fullArrivalDetails = {}
for (let i = 0; i < itemArr.length; i++) {//for each location repeat this function
let obj = {}
let itemDetails = itemArr[i][0];
let location = itemDetails.location
let arrivalDetails = itemArrivalDetails[location] ? itemArrivalDetails[location] : []
if (type == "InvtPart") {
if (itemDetails.freeStockOnOrder > 0) {
let backOrderedQuantity = Number(itemDetails.qtyOnOrder) - Number(itemDetails.freeStockOnOrder)
let totalArrivalQuantity = 0;
for (let index = 0; index < arrivalDetails.length; index++) {
let arrivalQty = arrivalDetails[index].arrivalQty
totalArrivalQuantity += Number(arrivalQty)
if (Number(totalArrivalQuantity) > Number(backOrderedQuantity)) {
obj.nextArrivalQty = Number(totalArrivalQuantity) - Number(backOrderedQuantity)
obj.nextArrivalDate = arrivalDetails[index].arrivalDate
break;
} else {
obj.nextArrivalQty = 0
obj.nextArrivalDate = ""
}
}
} else {
obj.nextArrivalQty = 0
obj.nextArrivalDate = ""
}
} else {
if (itemDetails.freeStockOnOrder > 0) {
let kititemArr = kitItemDetailsObj[location]
let maximumArrivalDate = '';
let minArrivalQty = -1
let memberArr = []
for (let j = 0; j < kititemArr.length; j++) {
let memberItemDetails = kititemArr[j]
let memberId = memberItemDetails.id
let memberQuantity = memberObj[memberId]
let backOrderedQuantity = Number(memberItemDetails.qtyOnOrder) - Number(memberItemDetails.freeStockOnOrder)
function singleItem(poItem) {
return Number(poItem.id) == Number(kititemArr[j].id)
}
let memberArrivalDetails = arrivalDetails.filter(singleItem)
let totalArrivalQuantity = 0;
let remaianAvailQty=0;
let availQty=0;
if(memberItemDetails.qtyAvail) {
totalArrivalQuantity += Number(memberItemDetails.qtyAvail)
}
let inMaximumArrivalDate = '';
for (let index = 0; index < memberArrivalDetails.length; index++) {
let member_Obj = {}
if (Number(memberQuantity) <= Number(totalArrivalQuantity) && Number(totalArrivalQuantity) > Number(backOrderedQuantity)) {
member_Obj.nextArrivalDate = ''
member_Obj.id = memberId
member_Obj.memberQty = memberQuantity
member_Obj.nextDatetime = ''
member_Obj.backOrderedQuantity = backOrderedQuantity
member_Obj.qtyAvail =Number(memberItemDetails.qtyAvail)
memberArr.push(member_Obj)
break;
}
let arrivalQty = memberArrivalDetails[index].arrivalQty
totalArrivalQuantity += Number(arrivalQty)
if (Number(memberQuantity) <= Number(totalArrivalQuantity) && Number(totalArrivalQuantity) > Number(backOrderedQuantity)) {
member_Obj.nextArrivalDate = memberArrivalDetails[index].arrivalDate
member_Obj.id = memberId
member_Obj.memberQty = memberQuantity
member_Obj.nextDatetime = memberArrivalDetails[index].datetime
member_Obj.backOrderedQuantity = backOrderedQuantity
member_Obj.qtyAvail =Number(memberItemDetails.qtyAvail)
memberArr.push(member_Obj)
if (!inMaximumArrivalDate)
inMaximumArrivalDate = memberArrivalDetails[index]
else if (inMaximumArrivalDate.datetime < memberArrivalDetails[index].datetime)
inMaximumArrivalDate = memberArrivalDetails[index]
break;
} else {
member_Obj.nextArrivalDate = ""
member_Obj.id = memberId
member_Obj.memberQty = memberQuantity
member_Obj.nextDatetime = ''
member_Obj.backOrderedQuantity = backOrderedQuantity
member_Obj.qtyAvail =Number(memberItemDetails.qtyAvail)
inMaximumArrivalDate = ""
}
memberArr.push(member_Obj)
}
if (!inMaximumArrivalDate && !(Number(memberQuantity) <= Number(memberItemDetails.qtyAvail))) {
maximumArrivalDate = ''
minArrivalQty = 0
break;
} else {
if (!maximumArrivalDate)
maximumArrivalDate = inMaximumArrivalDate
else if (Number(maximumArrivalDate.datetime) < Number(inMaximumArrivalDate.datetime))
maximumArrivalDate = inMaximumArrivalDate
}
}
for (let j = 0; j < memberArr.length; j++) {
let memberItemDetails = memberArr[j]
let memberId = memberItemDetails.id
function singleItem(poItem) {
return Number(poItem.id) == Number(memberArr[j].id)
}
let memberArrivalDetails = arrivalDetails.filter(singleItem)
let totalArrivalQuantity = 0;
let remaianAvailQty=0;
if(memberItemDetails.qtyAvail) {
totalArrivalQuantity += Number(memberItemDetails.qtyAvail)
}
if(Number(memberArr[j].backOrderedQuantity))
totalArrivalQuantity = Number(totalArrivalQuantity) - Number(memberArr[j].backOrderedQuantity)
let maxQty = -1;
for (let index = 0; index < memberArrivalDetails.length; index++) {
let arrivalQty = memberArrivalDetails[index].arrivalQty
if (memberArrivalDetails[index].datetime && Number(memberArrivalDetails[index].datetime) != 0 && Number(memberArrivalDetails[index].datetime) <= Number(maximumArrivalDate.datetime)) {
totalArrivalQuantity += Number(arrivalQty)
memberArr[j].totalArrivalQty = totalArrivalQuantity
memberArr[j].kitQty = Math.floor(Number(totalArrivalQuantity) / Number(memberArr[j].memberQty))
if (maxQty == -1 && Number(memberArr[j].kitQty))
maxQty = memberArr[j].kitQty
else if (Number(memberArr[j].kitQty) > Number(maxQty))
maxQty = memberArr[j].kitQty
}
}
if(maxQty==-1&&totalArrivalQuantity && Number(totalArrivalQuantity)>0){
memberArr[j].totalArrivalQty = totalArrivalQuantity
memberArr[j].kitQty = Math.floor(Number(totalArrivalQuantity) / Number(memberArr[j].memberQty))
if (maxQty == -1 && Number(memberArr[j].kitQty))
maxQty = memberArr[j].kitQty
else if (Number(memberArr[j].kitQty) > Number(maxQty))
maxQty = memberArr[j].kitQty
}
if (minArrivalQty == -1 && Number(maxQty) && Number(maxQty)>0)
minArrivalQty = maxQty
else if (Number(maxQty) < Number(minArrivalQty))
minArrivalQty = maxQty
}
if (maximumArrivalDate)
maximumArrivalDate = maximumArrivalDate.arrivalDate
if (minArrivalQty != -1)
obj.nextArrivalQty = minArrivalQty
else
obj.nextArrivalQty = 0
obj.nextArrivalDate = maximumArrivalDate
} else {
obj.nextArrivalQty = 0
obj.nextArrivalDate = ''
}
}
fullArrivalDetails[locationObj[location]] = {
"quantity": obj.nextArrivalQty,
"expected_deliverable_dates": obj.nextArrivalDate
}
}
scriptContext.response.write(JSON.stringify(fullArrivalDetails))
} catch
(e) {
log.error('e@onRequest', e)
scriptContext.response.write(JSON.stringify({}))
}
}
}
function memeberItem(adobeId){
try {
let memberObj = {}
var kititemSearchObj = search.create({
type: "kititem",
filters:
[
["type", "anyof", "Kit"],
"AND",
["custitem_jj_adobe_item_id", "is", adobeId]
],
columns:
[
search.createColumn({name: "memberitem", label: "Member Item"}),//0
search.createColumn({
name: "type",
join: "memberItem",
label: "Type"
}),//1
search.createColumn({name: "memberquantity", label: "Member Quantity"})//2
]
});
var searchResultCount = kititemSearchObj.runPaged().count;
log.debug("kititemSearchObj result count", searchResultCount);
kititemSearchObj.run().each(function (result) {
// .run().each has a limit of 4,000 results
let memberId = result.getValue(kititemSearchObj.columns[0])
let memberQty = result.getValue(kititemSearchObj.columns[2])
memberObj[memberId] = memberQty;
return true;
});
return memberObj;
}catch (e) {
log.error('e@memeberItem',e)
}
}
function arrivalDatePO(id) {
try {
let arrivalPo = {}
let filters = [
["type", "anyof", "PurchOrd"],
"AND",
["mainline", "is", "F"],
"AND",
["taxline", "is", "F"],
"AND",
["shipping", "is", "F"],
"AND",
["item", "noneof", "@NONE@"],
// "AND",
// ["applyingtransaction", "anyof", "@NONE@"],
"AND",
["expectedreceiptdate", "onorafter", "today"],
"AND",
["status","noneof","PurchOrd:F","PurchOrd:G","PurchOrd:H","PurchOrd:C","PurchOrd:A"],
"AND",
["closed","is","F"],
"AND",
["quantity","notequalto","0"]
// "AND",
// ["item.custitem_jj_adobe_item_id", "is", "70"]
]
if (id && Array.isArray(id))
filters = filters.concat(["AND", ["item.internalid", "anyof", id]])
else
filters = filters.concat(["AND", ["item.custitem_jj_adobe_item_id", "is", id]])
var purchaseorderSearchObj = search.create({
type: "purchaseorder",
filters: filters,
columns:
[
search.createColumn({
name: "item",
summary: "GROUP",
label: "Item"
}),//0
search.createColumn({
name: "expectedreceiptdate",
summary: "GROUP",
sort: search.Sort.ASC,
label: "Expected Receipt Date"
}),//1
search.createColumn({
name: "formulatext",
summary: "GROUP",
// formula: "TO_CHAR({expectedreceiptdate} ,'MM-DD-YYYY HH24:MI:SS')",
formula: "TO_CHAR({expectedreceiptdate} ,'MM-DD-YYYY')",
label: "Arrival Date"
}),//2
search.createColumn({
name: "location",
summary: "GROUP",
label: "Location"
}),//3
search.createColumn({
name: "quantity",
summary: "SUM",
label: "Quantity"
}),//4
search.createColumn({
name: "formulanumeric",
summary: "GROUP",
formula: "TO_CHAR({expectedreceiptdate} ,'YYYYMMDD')",
label: "Formula (Numeric)"
}),//5
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "NVL({quantity},0)-NVL({quantityshiprecv},0)",
label: "NotReceivedQty"
})//6
]
});
var searchResultCount = purchaseorderSearchObj.runPaged().count;
log.debug("purchaseorderSearchObj result count", searchResultCount);
purchaseorderSearchObj.run().each(function (result) {
// .run().each has a limit of 4,000 results
let itemObj = {}
let id = result.getValue(purchaseorderSearchObj.columns[0])
let arrivalDate = result.getValue(purchaseorderSearchObj.columns[2])
let arrivalLocation = result.getValue(purchaseorderSearchObj.columns[3])
let qty = result.getValue(purchaseorderSearchObj.columns[4])
let datetime = result.getValue(purchaseorderSearchObj.columns[5])
let arrivalQty= result.getValue(purchaseorderSearchObj.columns[6])
if (!arrivalPo[arrivalLocation])
arrivalPo[arrivalLocation] = []
itemObj.id = id
itemObj.arrivalDate = arrivalDate
itemObj.arrivalQty = arrivalQty
itemObj.arrivalLocation = arrivalLocation
itemObj.datetime = datetime
arrivalPo[arrivalLocation].push(itemObj)
return true;
});
return arrivalPo;
}catch (e) {
log.error('e@arrivalDatePO',e)
}
}
function itemSearchForQtyAvailableAndArrivalDate(id){
log.debug('id',id)
try {
let itemDetailsObj = {}
let filters = [["type", "anyof", "InvtPart", "Kit"]]
if (id && Array.isArray(id))
filters = filters.concat(["AND", ["internalid", "anyof", id]])
else
filters = filters.concat(["AND", ["custitem_jj_adobe_item_id", "is", id]])
log.debug('filters',filters)
var itemSearchObj = search.create({
type: "item",
filters: filters,
columns:
[
search.createColumn({
name: "internalid",
summary: "GROUP",
label: "Internal ID"
}),//0
search.createColumn({
name: "itemid",
summary: "GROUP",
sort: search.Sort.ASC,
label: "Name"
}),//1
search.createColumn({
name: "displayname",
summary: "GROUP",
label: "Display Name"
}),//2
search.createColumn({
name: "salesdescription",
summary: "GROUP",
label: "Description"
}),//3
search.createColumn({
name: "type",
summary: "GROUP",
label: "Type"
}),//4
search.createColumn({
name: "inventorylocation",
summary: "GROUP",
label: "Inventory Location"
}),//5
search.createColumn({
name: "inventorylocation",
join: "memberItem",
summary: "GROUP",
label: "Inventory Location"
}),//6
search.createColumn({
name: "formulanumeric",
summary: "MIN",
formula: "FLOOR(NVL(MIN(CASE WHEN {type}='Inventory Item' THEN (NVL({locationquantityonorder},0)) ELSE (NVL({memberitem.locationquantityonorder},0)/{memberquantity}) END),0))",
label: "QuantityOnOrder"
}),//7
search.createColumn({
name: "formulanumeric",
summary: "MIN",
formula: "FLOOR(NVL(MIN(CASE WHEN (CASE WHEN {type}='Inventory Item' THEN ((NVL({locationquantityonorder},0))- (NVL({locationquantitybackordered},0))) ELSE ((NVL({memberitem.quantityavailable},0)/NULLIF({memberquantity},0))+(NVL((REMAINDER(NVL({memberitem.quantityavailable},0), NULLIF({memberquantity},0))+NVL({memberitem.locationquantityonorder},0)),0)/NULLIF({memberquantity},0))-(NVL({memberitem.locationquantitybackordered},0)/NULLIF({memberquantity},0))) END) <0 THEN 0 ELSE (CASE WHEN {type}='Inventory Item' THEN ((NVL({locationquantityonorder},0))- (NVL({locationquantitybackordered},0))) ELSE ((NVL({memberitem.quantityavailable},0)/NULLIF({memberquantity},0))+(NVL((REMAINDER(NVL({memberitem.quantityavailable},0), NULLIF({memberquantity},0))+NVL({memberitem.locationquantityonorder},0)),0)/NULLIF({memberquantity},0))-(NVL({memberitem.locationquantitybackordered},0)/NULLIF({memberquantity},0))) END) END),0))",
label: "FreeStockOnOrder"
}),//8
search.createColumn({
name: "formulanumeric",
summary: "MIN",
formula: "NVL({quantityavailable}, 0)",
label: "Formula (Numeric)"
})//9
]
});
var searchResultCount = itemSearchObj.runPaged().count;
log.debug("itemSearchObj result count", searchResultCount);
itemSearchObj.run().each(function (result) {
// .run().each has a limit of 4,000 results
let itemObj = {}
let id = result.getValue(itemSearchObj.columns[0])
let type = result.getValue(itemSearchObj.columns[4])
let qtyOnOrder = result.getValue(itemSearchObj.columns[7])
let freeStockOnOrder = result.getValue(itemSearchObj.columns[8])
// if(!itemDetailsObj[id])
// itemDetailsObj[id]=[]
// log.debug('type', type)
let location = ''
let qtyAvail= ''
if (type == "InvtPart") {
location = result.getValue(itemSearchObj.columns[5])
qtyAvail=result.getValue(itemSearchObj.columns[9])
} else {
location = result.getValue(itemSearchObj.columns[6])
}
if (!itemDetailsObj[location])
itemDetailsObj[location] = []
itemObj.location = location
itemObj.type = type;
itemObj.id = id;
itemObj.qtyOnOrder = qtyOnOrder;
itemObj.freeStockOnOrder = freeStockOnOrder;
if(qtyAvail)
itemObj.qtyAvail = qtyAvail;
itemDetailsObj[location].push(itemObj)
return true;
});
return itemDetailsObj;
}catch (e) {
log.error("E@itemSearchForQtyAvailableAndArrivalDate",e)
}
}
function locationSerach() {
try {
let locObj = {}
var locationSearchObj = search.create({
type: "location",
filters:
[],
columns:
[
search.createColumn({name: "custrecord_jj_adobe_source_code", label: "Adobe Source Code"}),//0
search.createColumn({name: "internalid", label: "Internal ID"})//1
]
});
var searchResultCount = locationSearchObj.runPaged().count;
log.debug("locationSearchObj result count", searchResultCount);
locationSearchObj.run().each(function (result) {
// .run().each has a limit of 4,000 results
let id = result.getValue(locationSearchObj.columns[1])
let code = result.getValue(locationSearchObj.columns[0])
locObj[id] = code;
return true;
});
return locObj
}catch (e) {
log.error("e@locationSerach",e)
}
}
return {onRequest}
});