There are a lot of considerations for this saved search and the accuracy needs to be verified
some considerations are below
-Item type
-Service item then they can be fulfilled check box need to be checked
-Dropship lines need to be excluded
The export of the script for that is shown below
var salesorderSearchObj = search.create({
type: “salesorder”,
filters:
[
[“type”,”anyof”,”SalesOrd”],
“AND”,
[“mainline”,”is”,”F”],
“AND”,
[“location”,”anyof”,”1″],
“AND”,
[“status”,”anyof”,”SalesOrd:B”,”SalesOrd:E”,”SalesOrd:D”],
“AND”,
[“memorized”,”is”,”F”],
“AND”,
[“taxline”,”is”,”F”],
“AND”,
[“shipping”,”is”,”F”],
“AND”,
[“applyinglinktype”,”noneof”,”DropShip”],
“AND”,
[“purchaseorder.appliedtolinktype”,”noneof”,”DropShip”],
“AND”,
[“custbodyast_cusprojord”,”is”,”F”],
“AND”,
[“trandate”,”before”,”today”],
“AND”,
[“formulanumeric: CASE WHEN ( {item.type} IN (‘Non-inventory Item’,’Other Charge’) AND {item.isfulfillable}= ‘T’ AND ({quantity}!={quantitybilled})) THEN 1 ELSE (CASE WHEN NVL({quantitycommitted},0)>=0 THEN 1 ELSE 0 END) END”,”equalto”,”1″],
“AND”,
[[[“shipcomplete”,”is”,”F”]],”OR”,[[“shipcomplete”,”is”,”T”],”AND”,[“item.type”,”anyof”,”InvtPart”,”Kit”,”Group”]]],
“AND”,
[“custbody_customer”,”noneof”,”8516″,”7855″,”1616″,”6″,”8544″],
“AND”,
[“max(quantitycommitted)”,”greaterthan”,”0″],
“AND”,
[“max(formulanumeric: CASE WHEN MAX(DECODE({shipcomplete},’T’,0,1))=0 THEN DECODE(SUM({quantity}),SUM({quantitycommitted}),1,0) ELSE 1 END )”,”equalto”,”1″]
],
columns:
[
search.createColumn({
name: “trandate”,
summary: “GROUP”,
label: “Date”
}),
search.createColumn({
name: “type”,
summary: “GROUP”,
label: “Type”
}),
search.createColumn({
name: “tranid”,
summary: “GROUP”,
label: “Document Number”
}),
search.createColumn({
name: “entity”,
summary: “GROUP”,
label: “Name”
}),
search.createColumn({
name: “shipmethod”,
summary: “GROUP”,
label: “Ship Via”
}),
search.createColumn({
name: “shipcomplete”,
summary: “GROUP”,
label: “Ship Complete”
}),
search.createColumn({
name: “category”,
join: “customer”,
summary: “GROUP”,
label: “Category”
}),
search.createColumn({
name: “quantitycommitted”,
summary: “MAX”,
label: “Quantity Committed”
}),
search.createColumn({
name: “formulanumeric”,
summary: “MAX”,
formula: “CASE WHEN MAX(DECODE({shipcomplete},’T’,0,1))=0 THEN DECODE(SUM({quantity}),SUM({quantitycommitted}),1,0) ELSE 1 END “,
label: “Ship complete check”
}),
search.createColumn({
name: “formulanumeric”,
summary: “MAX”,
formula: “DECODE(SUM({quantity}),SUM({quantitycommitted}),1,0)”,
label: “Formula (Numeric)”
}),
search.createColumn({
name: “formulatext”,
summary: “MAX”,
formula: “SUM({quantitycommitted})||MAX({shipcomplete})||SUM({quantity})”,
label: “Formula (Text)”
}),
search.createColumn({
name: “estgrossprofit”,
summary: “SUM”,
label: “Est. Gross Profit (Line)”
})
]
});
var searchResultCount = salesorderSearchObj.runPaged().count;
log.debug(“salesorderSearchObj result count”,searchResultCount);
salesorderSearchObj.run().each(function(result){
// .run().each has a limit of 4,000 results
return true;
});
/*
salesorderSearchObj.id=”customsearch1678711407763″;
salesorderSearchObj.title=”JJ Blenheim Warehouse B4Today Qty sales orders Pending Fulfilment (copy)”;
var newSearchId = salesorderSearchObj.save();
*/
Saved Search model 2
var salesorderSearchObj = search.create({
type: “salesorder”,
filters:
[
[“type”,”anyof”,”SalesOrd”],
“AND”,
[“mainline”,”is”,”F”],
“AND”,
[“location”,”anyof”,”1″],
“AND”,
[“status”,”anyof”,”SalesOrd:B”,”SalesOrd:E”,”SalesOrd:D”],
“AND”,
[“memorized”,”is”,”F”],
“AND”,
[“taxline”,”is”,”F”],
“AND”,
[“shipping”,”is”,”F”],
“AND”,
[“applyinglinktype”,”noneof”,”DropShip”],
“AND”,
[“purchaseorder.appliedtolinktype”,”noneof”,”DropShip”],
“AND”,
[“custbodyast_cusprojord”,”is”,”F”],
“AND”,
[“trandate”,”within”,”today”],
“AND”,
[“formulanumeric: CASE WHEN ( {item.type} IN (‘Non-inventory Item’,’Other Charge’) AND {item.isfulfillable}= ‘T’ AND ({quantity}!={quantitybilled})) THEN 1 ELSE (CASE WHEN NVL({quantitycommitted},0)>0 THEN 1 ELSE 0 END) END”,”equalto”,”1″],
“AND”,
[[[“shipcomplete”,”is”,”F”]],”OR”,[[“shipcomplete”,”is”,”T”],”AND”,[“item.type”,”anyof”,”InvtPart”,”Kit”]]],
“AND”,
[“max(quantitycommitted)”,”greaterthan”,”0″],
“AND”,
[“max(formulanumeric: CASE WHEN MAX(DECODE({shipcomplete},’T’,0,1))=0 THEN DECODE(SUM({quantity}),SUM({quantitycommitted}),1,0) ELSE 1 END )”,”equalto”,”1″]
],
columns:
[
search.createColumn({
name: “trandate”,
summary: “GROUP”,
label: “Date”
}),
search.createColumn({
name: “type”,
summary: “GROUP”,
label: “Type”
}),
search.createColumn({
name: “tranid”,
summary: “GROUP”,
label: “Document Number”
}),
search.createColumn({
name: “entity”,
summary: “GROUP”,
label: “Name”
}),
search.createColumn({
name: “shipmethod”,
summary: “GROUP”,
label: “Ship Via”
}),
search.createColumn({
name: “shipcomplete”,
summary: “GROUP”,
label: “Ship Complete”
}),
search.createColumn({
name: “category”,
join: “customer”,
summary: “GROUP”,
label: “Category”
}),
search.createColumn({
name: “quantitycommitted”,
summary: “MAX”,
label: “Quantity Committed”
}),
search.createColumn({
name: “formulanumeric”,
summary: “MAX”,
formula: “CASE WHEN MAX(DECODE({shipcomplete},’T’,0,1))=0 THEN DECODE(SUM({quantity}),SUM({quantitycommitted}),1,0) ELSE 1 END “,
label: “Ship complete check”
})
]
});
var searchResultCount = salesorderSearchObj.runPaged().count;
log.debug(“salesorderSearchObj result count”,searchResultCount);
salesorderSearchObj.run().each(function(result){
// .run().each has a limit of 4,000 results
return true;
});
/*
salesorderSearchObj.id=”customsearch1678711294890″;
salesorderSearchObj.title=”JJ Blenheim WarehouseToday’s Qty sales orders Pending Fulfilment (copy)”;
var newSearchId = salesorderSearchObj.save();
*/
