Transfer Reports from Oracle NetSuite to SFTP Server in 6 Easy Steps
Oracle NetSuite, the popular all-in-one Enterprise Resource Planning (ERP) system, offers valuable insights into business processes by means of various reports. Many clients prefer to transfer these reports to an SFTP server and through the SFTP middleware to other enterprise systems such as SAP and Salesforce. While direct transfer from Oracle NetSuite to the different systems is not impossible, it is complex and requires time-consuming logic.
SFTP makes this job of transferring files from Oracle NetSuite particularly easy because the transfer can be automated. This is done through coding by utilizing the features of SuiteScript 2.0 SFTP Module. This would require login credentials and port number of the SFTP server.
For example, if we want to transfer the results of invoices created within Oracle NetSuite each day, we can write a schedule script 2.0 to execute every day at midnight to search for the invoices created the previous day and create a CSV file for the results obtained. This file will be uploaded to Oracle NetSuite file cabinet and then to the SFTP Server.
The 6 Steps to Transfer Reports from Oracle NetSuite to SFTP Server
1. Create Saved Search
Search can be created in SuiteScript 2.0 using the search module. It can be done using search.create API, where type indicates the record to be searched. Record types can be filtered using search.createFilter and the required field values from those records can be retrieved using search.createColumn. Finally, search can be executed using run() method on the search object.
A sample code snippet to create saved search is given below.
1define(['N/record', 'N/search'],
2
3function(record, search) {
4
5
6
7 function execute(scriptContext) {
8
9var customersearch = search.create({
10 type: "invoice",
11 filters: [
12
13 search.createFilter(
14
15
16 {
17 name: 'entity',
18 operator: 'anyof',
19 values:500
20 }),
21
22
23 ],
24 columns: [
25 search.createColumn({
26 name: "internalid",
27 sort: search.Sort.ASC
28 }),
29
30
31 ]
32 });
33
34var customersearchres = customersearch.run().getRange(0, 1000);
35
36
37}
38
39 return {
40 execute: execute
41 };
42
43});2. Generate CSV File Content for Search Result
Next, we generate the CSV file. A sample code snippet to generate CSV file is given below:
1function createcsvcontent(columns,firstsearchResult) {
2
3//Creating arrays that will populate results
4var content = new Array();
5
6var headers = new Array();
7var temp = new Array();
8var x = 0;
9
10for(var i=0; i< columns.length; i++)
11{
12headers[i] = columns[i].label;
13
14
15}
16
17content[x] = headers;
18x =1;
19
20for(var i=0;i<firstsearchResult.length;i++)
21{
22
23var result = firstsearchResult[i];
24for(var y=0; y< columns.length; y++){
25
26if(result.getText(columns[y])!=''&&result.getText(columns[y])!=' '&&result.getText(columns[y])!=null)
27{
28var searchResult = result.getText(columns[y]);
29if(searchResult!=null)
30{
31
32searchResult = searchResult.toString().replace("undefined", "");
33searchResult = searchResult.toString().replace("false", "No");
34searchResult = searchResult.toString().replace("true", "Yes");
35}
36
37temp[y] = searchResult;
38
39
40}
41else
42{
43
44
45var searchResult = result.getValue(columns[y]);
46if(searchResult!=null)
47{
48
49searchResult = searchResult.toString().replace("undefined", "");
50searchResult = searchResult.toString().replace("false", "No");
51searchResult = searchResult.toString().replace("true", "Yes");
52
53temp[y] = searchResult;
54
55
56}//searchResult!=null
57
58
59}//else
60
61
62} //y
63
64content[x] +=temp;
65x++;
66
67
68}//i
69
70var contents='';
71
72for(var z =0; z<content.length;z++){
73contents +=content[z].toString() + '\n';
74}
75log.debug('contents before',contents);
76if(contents!='')
77{
78contents = contents.toString().replace(/undefined/g, "");
79contents = contents.toString().replace(/’/g,'');
80}
81log.debug('contents after',contents);
82
83return contents;
84
85}The columns indicate the search columns produced in step 1 and firstsearchResult indicates the resultant array. Columns array can be used to read the labels first so that they can be placed at the header of the CSV file. The resultant array is then read to get the required values and they are added to the rows below the header in the CSV file.
3. Create CSV File and Upload to File Cabinet
This step is used to create the CSV file using the content generated in the previous step.
Sample code snippet:
1function createcsvfile(start_name,contents) {
2
3 var file_date = new Date();
4
5 file_date.setDate(file_date.getDate()-1);
6
7 var file_dd = file_date.getDate();
8
9 if(file_dd<10)
10 {
11
12 file_dd = '0'+file_dd;
13 }
14
15 var file_mm = file_date.getMonth();
16 file_mm++;
17 if(file_mm<10)
18 {
19
20 file_mm = '0'+file_mm;
21 }
22
23 var file_yy = file_date.getFullYear()-2000;
24 file_yy = parseFloat(file_yy).toFixed(0);
25
26 var final_file_name = start_name+file_mm+file_dd+file_yy;//start_name+file_dd+file_mm+file_yy;
27
28 var id = 0;
29
30 try
31 {
32 var fileObj = file.create({
33 name: final_file_name+'.csv',
34 fileType: file.Type.CSV,
35 contents: contents,
36 encoding: file.Encoding.UTF8,
37 //description: 'This is description',
38 folder: folderid//SFTP CSV
39 });
40
41 id = fileObj.save();
42 // log.debug('id',id );
43 }
44
45 catch (e) { // incase the folder is not present
46
47 var fileObj = file.create({
48 name: final_file_name+'.csv',
49 fileType: file.Type.CSV,
50 contents: contents,
51 encoding: file.Encoding.UTF8,
52
53 });
54
55 id = fileObj.save();
56 // TODO: handle exception
57 }
58 return id;
59
60
61 }
62The start_name indicates the prefix to be added in the file name and contents indicate the file content generated in the previous step. The CSV file is then created using the file module API file.create and it is placed in the file cabinet folder indicated by folder parameter.
4. Generate the Host-Key of SFTP Server
You can get your SFTP server’s host key by using the following command:
Ssh-keyscan hostname or IP address
5. Generate GUID in Oracle NetSuite Environment
GUID stands for Global Unique Identifier. This is a unique identifier generated in Oracle NetSuite by a particular algorithm. You can use this algorithm to generate a GUID for a specific domain and script.
A sample code snippet for generating a GUID is given below.
1define(['N/ui/serverWidget',N/log"],
2function (serverWidget, log) {
3function onRequest(context) {
4if (context.request.method === 'GET') {
5var form = serverWidget.createForm({
6title: 'Guid Form’
7});
8form.addField({
9id: 'username',
10type: serverWidget.FieldType.TEXT,
11label: 'Username'
12});
13form.addCredentialField({
14id: 'password',
15label: 'Password',
16restrictToScriptIds: 'customscript_id, //id of Script Using sftp
17restrictToDomains: ‘domain name'
18});
19form.addSubmitButton({
20label: 'Submit Button'
21});
22context.response.writePage(form);
23return;
24} else {
25var requset = context.request;
26var myPwdGuid = requset.parameters.password;
27log.debug("myPwdGuid", myPwdGuid);
28context.response.write(myPwdGuid);
29}
30}
31return {
32onRequest: onRequest
33};
34});
35
366. Create Connection and Upload to SFTP
In the final step, we create the connection to the SFTP server using its username, password, url, password GUID, hostkey, and port. Once the connection is established, the file generated via the previous steps is uploaded to the SFTP server.
1function upload_to_sftp(fileId) {
2
3 var connection = sftp.createConnection({
4 username: username,
5 passwordGuid: pwdguid,
6 url: sftpurl,
7 hostKey: hostkey,
8 port : 22
9 });
10
11 log.debug({title: 'DEBUG', details : 'connection : '+connection});
12
13 if(connection)
14 {
15
16
17 var fileObj = file.load({id: fileId});
18
19 log.debug({title: 'DEBUG', details : 'UPLOAD FILE : '+fileObj });
20
21 connection.upload({
22 //directory: '/',
23 directory: directory,
24 file: fileObj,
25 replaceExisting: true
26 });
27
28 log.debug({title: 'DEBUG', details : 'MOVED FILE : '+fileObj });
29 }//connection
30
31 }//upload_to_sftp function
32Conclusion
Files can be easily transferred from Oracle NetSuite to SFTP server using the above mentioned six steps. The codes can be replicated and modified based on the reports to be transferred.