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

 

Transfer Reports from Oracle NetSuite to SFTP Server in 6 Easy Steps

 

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.

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:

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:

The 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.

6. 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.

Conclusion

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.