JIRA CODE: NMS-2602
Purpose
We need to know what is the quantity billed for each line PO.
Description
There is a custom line field in PO ‘Billed quantity’. A workflow action script is written to update this field. So we can know the quantity billed in each line. The script will run when a bill is saved. Script will run a vendor bill search to find the PO for the saved bill. The PO internal id obtained in this search is used to run the PO search. The PO search will give the quantity billed for each line items.
Code
/**
* @NApiVersion 2.x
* @NScriptType workflowactionscript
*/
define(['N/search', 'N/record'],
/**
* @param {N} N
* @param {auth} auth
* @param {config} config
* @param {crypto} crypto
* @param {currency} currency
* @param {email} email
* @param {encode} encode
* @param {error} error
* @param {file} file
* @param {format} format
* @param {http} http
* @param {https} https
* @param {plugin} plugin
* @param {portlet} portlet
* @param {record} record
* @param {redirect} redirect
* @param {render} render
* @param {runtime} runtime
* @param {search} search
* @param {sso} sso
* @param {task} task
* @param {transaction} transaction
* @param {ui} ui
* @param {dialog} dialog
* @param {message} message
* @param {serverWidget} serverWidget
* @param {url} url
* @param {workflow} workflow
* @param {xml} xml
*/
function(search, record) {
function checkif(singleitem) {
if (singleitem != "" && singleitem != null && singleitem != undefined && singleitem != '- None -' && singleitem != " " && singleitem != NaN && singleitem != 'NaN') {
return singleitem;
} else {
return 0;
}
}
function poSearch(filterobj) {
log.debug("filterobj", filterobj);
var purchaseorderSearchObj = search.create({
type: "purchaseorder",
filters: [
["type", "anyof", "PurchOrd"],
"AND",
filterobj,
"AND",
["applyingtransaction.type", "anyof", "VendBill"]
],
columns: [
//0
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "abs({applyingtransaction.quantity})",
label: "Formula (Numeric)"
}),
//1
search.createColumn({
name: "line",
summary: "GROUP",
label: "Line Uniq key PO"
}),
//2
search.createColumn({
name: "internalid",
summary: "GROUP",
label: "PO id"
}),
//3
search.createColumn({
name: "internalid",
join: "applyingTransaction",
summary: "GROUP",
label: "applying tran id"
}),
//4
search.createColumn({
name: "type",
join: "applyingTransaction",
summary: "GROUP",
label: "apllying type"
}),
//5
search.createColumn({
name: "line",
join: "applyingTransaction",
summary: "GROUP",
label: "Line ID"
}),
//6
search.createColumn({
name: "custbody_esw_bill_status",
join: "applyingTransaction",
summary: "GROUP",
label: "Apllying status"
}),
//7
search.createColumn({
name: "fxamount",
join: "applyingTransaction",
summary: "SUM",
label: "Amount (Foreign Currency)"
})
]
});
// log.debug("purchaseorderSearchObj", purchaseorderSearchObj);
return purchaseorderSearchObj;
}
function findPO(vid) {
var vendorbillSearchObj = search.create({
type: "vendorbill",
filters: [
["type", "anyof", "VendBill"],
"AND",
["internalid", "anyof", vid],
"AND",
["appliedtotransaction", "noneof", "@NONE@"]
],
columns: [
search.createColumn({ name: "internalid", label: "Internal ID" }),
search.createColumn({ name: "appliedtotransaction", label: "Applied To Transaction" }),
search.createColumn({
name: "internalid",
join: "appliedToTransaction",
label: "Internal ID"
}),
search.createColumn({
name: "line",
join: "appliedToTransaction",
label: "Line ID"
})
]
});
var searchResultCount = vendorbillSearchObj.runPaged().count;
var poId;
var itemLineId;
var itemArray = [];
// log.debug("vendorbillSearchObj result count", searchResultCount);
vendorbillSearchObj.run().each(function(result) {
// .run().each has a limit of 4,000 results
poId = result.getValue({ name: vendorbillSearchObj.columns[2] });
return true;
});
// log.debug("poId", poId);
return poId;
}
/**
* Definition of the Suitelet script trigger point.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.newRecord - New record
* @param {Record} scriptContext.oldRecord - Old record
* @Since 2016.1
*/
function onAction(scriptContext) {
try {
// log.debug("scriptContext.type", scriptContext.type);
var vid = scriptContext.newRecord.id;
//search for the related PO internal id
var poId = findPO(vid);
// if (scriptContext.type == 'create') {
// log.debug("poId",poId);
// }
//If the context is delete status should be takn from old record
if (scriptContext.type != 'delete') {
var status = scriptContext.newRecord.getValue({ fieldId: 'custbody_esw_bill_status' });
} else {
var status = scriptContext.oldRecord.getValue({ fieldId: 'custbody_esw_bill_status' });
}
//Need to run all the process if status is validated
// log.debug("status", status);
if (status == '2') {
// log.debug("status", status);
// log.debug("vid", vid);
var POLineId1 = [];
var POLineQty1 = [];
var POLineAmt1 = [];
var POLineIdBill = [];
var POLineQtyBill = [];
var POLineAmtBill = [];
var billId = [];
//PO search with filter=internal id of PO
var purchaseorderSearchObj = poSearch(["internalid", "anyof", poId]);
var searchResultCount1 = purchaseorderSearchObj.runPaged().count;
var searchResultCount = purchaseorderSearchObj.run().getRange({
start: 0,
end: 100
});
// var poId;
// log.debug("purchaseorderSearchObj result count", searchResultCount);
// log.debug("searchResultCount1", searchResultCount1);
//run the search result of PO search
purchaseorderSearchObj.run().each(function(result) {
// .run().each has a limit of 4,000 results
var billId = result.getValue(purchaseorderSearchObj.columns[3]);
var stat = result.getValue(purchaseorderSearchObj.columns[6]);
// log.debug("billId", billId);
// log.debug("stat", stat);
//puch to the arrays only if status is validated
if (stat == 2) {
POLineId1.push(result.getValue(purchaseorderSearchObj.columns[1]));
POLineQty1.push(result.getValue(purchaseorderSearchObj.columns[0]));
POLineAmt1.push(parseFloat(result.getValue(purchaseorderSearchObj.columns[7])) * -1);
}
//Also push to the arrays if the id is equal to the current bill records id
if (billId == vid && stat != 2) {
POLineIdBill.push(result.getValue(purchaseorderSearchObj.columns[1]));
POLineQtyBill.push(result.getValue(purchaseorderSearchObj.columns[0]));
POLineAmtBill.push(parseFloat(result.getValue(purchaseorderSearchObj.columns[7])) * -1);
}
return true;
});
};
// log.debug("POLineId1", POLineId1);
// log.debug("POLineQty1", POLineQty1);
// log.debug("POLineAmt1", POLineAmt);
//Group the array with the line number
var POLineId = [];
var POLineQty = [];
var POLineAmt = [];
for (var j = 0; j < POLineId1.length; j++) {
var index1 = POLineId.indexOf(POLineId1[j]);
if (index1 == -1) {
POLineId.push(POLineId1[j]);
POLineQty.push(POLineQty1[j]);
POLineAmt.push(POLineAmt1[j]);
} else {
POLineQty[index1] = parseFloat(POLineQty[index1]) + parseFloat(POLineQty1[j]);
POLineAmt[index1] = parseFloat(POLineAmt[index1]) + parseFloat(POLineAmt1[j]);
}
}
// log.debug("POLineId", POLineId);
// log.debug("POLineQty", POLineQty);
// log.debug("POLineIdBill", POLineIdBill);
// log.debug("POLineQtyBill", POLineQtyBill);
// log.debug("POLineAmtBill", POLineAmtBill);
// log.debug("POLineAmt", POLineAmt);
//combine the array of current record qty and other valid status record qty
if (POLineId.length > 0) {
// log.debug("POLineIdBill.legth", POLineIdBill.legth);
for (var k = 0; k < POLineIdBill.length; k++) {
var index = POLineId.indexOf(POLineIdBill[k]);
// log.debug("index", index);
if (index > -1) {
POLineQty[index] = parseFloat(POLineQty[index]) + parseFloat(POLineQtyBill[k]);
POLineAmt[index] = parseFloat(POLineAmt[index]) + parseFloat(POLineAmtBill[k]);
} else {
POLineQty.push(POLineQtyBill[k]);
POLineId.push(POLineIdBill[k]);
POLineAmt.push(POLineAmtBill[k]);
}
}
} else {
// log.debug("inelse", "inelse");
POLineId = POLineIdBill;
POLineQty = POLineQtyBill;
POLineAmt = POLineAmtBill;
}
//if the context is delete
if (scriptContext.type == 'delete') {
//do the PO search with the filter applying trans=the vendor bill id
var purchaseorderSearchObjD = poSearch(["applyingtransaction.internalid", "anyof", vid]);
var searchResultCount = purchaseorderSearchObjD.runPaged().count;
var POLineIdD = [];
var POLineQtyD = [];
var POLineAmtD = [];
// log.debug("purchaseorderSearchObj result count", searchResultCount);
purchaseorderSearchObjD.run().each(function(result) {
// .run().each has a limit of 4,000 results
POLineIdD.push(result.getValue(purchaseorderSearchObjD.columns[1]));
POLineQtyD.push(result.getValue(purchaseorderSearchObjD.columns[0]));
POLineAmtD.push(parseFloat(result.getValue(purchaseorderSearchObjD.columns[7])) * -1);
// log.debug("purchaseorderSearchObjD.columns", result.getValue(purchaseorderSearchObjD.columns[3]));
return true;
});
//substarct this vendor bill qty from the current qty
for (var k = 0; k < searchResultCount; k++) {
var index = POLineId.indexOf(POLineIdD[k]);
if (index > -1)
POLineQty[index] = parseFloat(POLineQty[index]) - parseFloat(POLineQtyD[k]);
POLineAmt[index] = parseFloat(POLineAmt[index]) - parseFloat(POLineAmtD[k]);
}
}
// log.debug("POLineId2", POLineId);
// log.debug("POLineQty2", POLineQty);
// log.debug("POLineIdD", POLineIdD);
// log.debug("POLineIdD", POLineIdD);
var objRecord = record.load({
type: record.Type.PURCHASE_ORDER,
id: poId,
isDynamic: false,
});
var numLines = objRecord.getLineCount({
sublistId: 'item'
});
// log.debug("numLinesPO", numLines);
for (var i = 0; i < numLines; i++) {
var itemLine = objRecord.getSublistValue({
sublistId: 'item',
fieldId: 'line',
line: i
});
// log.debug("itemLine", itemLine);
// log.debug('POLineId.indexOf(itemLine)', POLineId.indexOf(itemLine));
//set the qty to the correct item line
if (POLineId.indexOf(itemLine) > -1) {
objRecord.setSublistValue({
sublistId: 'item',
fieldId: 'custcol_esw_po_open_bill_count',
line: i,
value: parseFloat(POLineQty[POLineId.indexOf(itemLine)])
});
objRecord.setSublistValue({
sublistId: 'item',
fieldId: 'custcol_amount_billed',
line: i,
value: parseFloat(POLineAmt[POLineId.indexOf(itemLine)])
});
}
}
var recordId = objRecord.save({
enableSourcing: false,
ignoreMandatoryFields: true
});
} catch (err) {
log.debug("err", err);
}
}
return {
onAction: onAction
};
});