This script summarizes on creating XML files for Purchase Orders and save to an SFTP server. A saved search is used to find the Purchase Orders to be send. The passGUID and hostkey mentioned in the below code should be generated using the SFTP helper tools suitelet.
var SENDFOLDER = 12345;
//Creating connection to sFTP
var connection = sftp.createConnection({
username: userName,
passwordGuid: passGUID,
url: URL,
directory: '/',
hostKey: hostKey_string
});
//Search loaded from Netsuite to get the purchase orders
var poSearch = search.load({
id: 'customsearch_jj_po_sendto_naiva_otgn1483'
});
poSearch.run().each(function(result) {
var poID = result.getValue({ name: 'internalid' });
var poNumber=result.getValue({ name: 'tranid' });
var approvalstatus=result.getText({ name: 'approvalstatus' });
var createdfrom=result.getText({ name: 'createdfrom' });
var currency=result.getText({ name: 'currency' });
var department=result.getText({ name: 'department' });
var createddate=result.getValue(result.columns[20]);
var duedate=result.getValue(result.columns[19]);
var orderdate=result.getValue(result.columns[21]);
var exfactorydate=result.getValue(result.columns[22]);
var eta=result.getValue(result.columns[19]);
var origin=result.getValue({ name: 'custbody_jj_origin_unlocode' });
var destination=result.getValue({ name: 'custbody_jj_destination_unlocode' });
var suppliername=result.getValue({ name: "entityid",join: "vendor" });
var shippingmethod=result.getText({ name: "custbody_shipping_method_po" });
var containermode=result.getText({ name: "custbody_container_mode_po" });
var shipmentnumber=result.getValue({ name: "custbody31" });
var location=result.getText({ name: "location" });
var incoterm=result.getText({ name: "incoterm" });
var unique_suppliercode=result.getText({ name: "internalid",join: "vendor"});
var supplieraddress=result.getValue({ name: "address",join: "vendor"});
var suppliercountry=result.getText({ name: "country",join: "vendor"});
var deliveryaddress=result.getValue({ name: "address1",join: "location"});
var deliverycity=result.getValue({ name: "city",join: "location"});
// search to get PO item lines
var itemArray = [];
var purchaseorderSearchObj = search.create({
type: "purchaseorder",
filters:
[
["type","anyof","PurchOrd"],
"AND",
["internalidnumber","equalto",poID],
"AND",
["shipping","is","F"],
"AND",
["taxline","is","F"],
"AND",
["item.name","isnotempty",""]
],
columns:
[
search.createColumn({name: "item", label: "Item"}),
search.createColumn({name: "quantity", label: "Quantity"}),
search.createColumn({name: "rate", label: "Item Rate"}),
search.createColumn({name: "linesequencenumber", label: "Line Sequence Number"}),
search.createColumn({
name: "upccode",
join: "item",
label: "UPC Code"
}),
search.createColumn({
name: "salesdescription",
join: "item",
label: "Description"
})
]
});
purchaseorderSearchObj.run().each(function(result){
var line = result.getValue({ name: "linesequencenumber" });
var sku = result.getText({ name: "item" });
var description = result.getValue({ name: "salesdescription",join: "item" });
var qty = result.getValue({ name: "quantity" });
var unitprice = result.getValue({ name: "rate" });
var barcode = result.getValue({ name: "upccode", join: "item" });
var itemObj = {};
itemObj.sku = sku;
itemObj.line = line;
itemObj.qty = qty;
itemObj.description = description;
itemObj.barcode = barcode;
itemObj.unitprice = unitprice;
itemArray.push(itemObj);
return true;
});
//Getting current datetimestamp
var now = new Date();
var year = now.getFullYear();
var month = now.getMonth() + 1;
var day = now.getDate();
var hour = now.getHours();
var minute = now.getMinutes();
var second = now.getSeconds();
if (month.toString().length == 1) {
month = '0' + month;
}
if (day.toString().length == 1) {
day = '0' + day;
}
if (hour.toString().length == 1) {
hour = '0' + hour;
}
if (minute.toString().length == 1) {
minute = '0' + minute;
}
if (second.toString().length == 1) {
second = '0' + second;
}
var date = year + '' + month + '' + day;
var time = hour + '' + minute + '' + second;
let datetimestamp=date+time;
// create item list xml
var itemxml='<itemList>\n';
for(var j=0;j< itemArray.length;j++)
{
itemxml=itemxml + '<line>\n'+ '<lineno>'+ itemArray[j].line+'</lineno>\n'+
'<sku>'+ itemArray[j].sku+ '</sku>\n'+
'<qty>'+itemArray[j].qty+ '</qty>\n' +
'<description>'+itemArray[j].description+ '</description>\n'+
'<barCode>'+itemArray[j].barcode+ '</barCode>\n'+
'<unitPrice>'+itemArray[j].unitprice+ '</unitPrice>\n'+
'</line>\n';
}
itemxml=itemxml+'</itemList>';
// create PO xml
var poData = '<?xml version="1.0" encoding="utf-8"?>\n' +
'<purchaseOrder>\n' +
'<datetimestamp>' + datetimestamp + '</datetimestamp>\n' +
'<poNumber>' + poNumber + '</poNumber>\n' +
'<approvalStatus>' + approvalstatus + '</approvalStatus>\n' +
'<createdDate>' + createddate + '</createdDate>\n' +
'<createdFrom>' + createdfrom + '</createdFrom>\n' +
'<currency>'+ currency+'</currency>\n' +
'<department>' + department + '</department>\n' +
'<dueDate>' + duedate + '</dueDate>\n' +
'<orderDate>' + orderdate + '</orderDate>\n' +
'<exFactoryDate>' + exfactorydate + '</exFactoryDate>\n' +
'<eta>' + eta + '</eta>\n' +
'<originLocation>' + origin + '</originLocation>\n' +
'<destinationLocation>' + destination + '</destinationLocation>\n' +
'<supplierName>' + escape_for_xml(suppliername, xml) + '</supplierName>\n' +
'<shippingMethod>' + shippingmethod + '</shippingMethod>\n' +
'<containerMode>' + containermode + '</containerMode>\n' +
'<shipmentNumber>' + shipmentnumber + '</shipmentNumber>\n' +
'<location>' + location + '</location>\n' +
'<incoterm>' + incoterm + '</incoterm>\n' +
'<uniqueSupplierCode>' + unique_suppliercode + '</uniqueSupplierCode>\n' +
'<supplierAddress>' + escape_for_xml(supplieraddress, xml) + '</supplierAddress>\n' +
'<supplierCountry>' + suppliercountry + '</supplierCountry>\n' +
'<deliveryAddress>' + escape_for_xml(deliveryaddress, xml) + '</deliveryAddress>\n' +
'<deliveryCity>' + deliverycity + '</deliveryCity>\n' +
itemxml + '\n' +
'</purchaseOrder>';
// create xml file
var XMLfileName = poNumber+'_'+datetimestamp + '.xml';
//copy of file is saved to file Cabinet
var fileToSend = file.create({
fileType: file.Type.XMLDOC,
name: XMLfileName,
contents: poData,
folder: SENDFOLDER
});
let fileId = fileToSend.save();
var poFileToUpload = file.create({
fileType: file.Type.XMLDOC,
name: XMLfileName,
contents: poData,
});
//File is uploaded to FTP
connection.upload({
directory: '/input',
file: poFileToUpload,
filename: XMLfileName,
replaceExisting: true
});