NetSuite EDI Integration

End-to-end integration is an important component of any EDI implementation. The real power of EDI can only be realized when data seamlessly flows from application to partner application, automating important business processes.

This article shows an example of how you can seamlessly handle NetSuite EDI integration with Arc. We provide a walk-through for configuring Arc to receive an EDI Purchase Order, inject an EDI Purchase Order into NetSuite, retrieve an invoice, and prepare an invoice as an EDI Invoice for sending to your trading partner.

Setting up the NetSuite EDI Integration Sample

To begin, download and install the Arc application (free 30-day trial). Then, download the sample project and extract and copy the contents to the "data" folder in the installation location for Arc. For Windows installations, this will likely be C:\Program Files\CData\Arc. Please consult the documentation for the Java build of Arc for Linux/Unix/Mac installations.

Next, modify the X12 document in the sample to refer to an existing Customer and existing Items in your NetSuite account. We are using X12 here, but an EDIFACT document would work similarly.

Use a text editor to open the 850.x12 file located in the Send subfolder of the X12_Wayne_Tech_IN folder. Next, find the placeholder values FILLER_CUSTOMER_ID, FILLER_ITEM_ID1, and FILLER_ITEM_ID2. Replace them with an actual Customer Id and Item Ids from your NetSuite account. The values can be found in the Customer and Item tables exposed by the CData ODBC Driver for Netsuite.

The last step for setting up the sample is editing the Sales Order template for the MAP_850_IN Connector (located in the MAP_850_IN subfolder of the data folder). Open the file in a text editor and replace the text FILLER_SUBSIDIARY_ID with an existing Subsidiary Id from your NetSuite account. The Subsidiary Id should be associated with the Customer and Items used in the 850 (purchase order) document. You can find this value in the Subsidiary table exposed by the ODBC Driver. If you wish, you can add some logic to the MAP Connector in order to determine the Subsidiary Id based on the incoming Customer. If you have questions about this process, please reach out to our Support Team.

Once you have filled in the X12 document with actual values from your NetSuite account and edited the Sales Order template, you need to ensure that you have configured your connection to NetSuite properly.

Connecting to NetSuite

If you have not done so already, you will need to install the CData ODBC Driver for NetSuite. You can download a free, 30-day trial here. If you just downloaded and installed the provider while Arc was running, please restart the application before proceeding.

With the ODBC Driver installed, the next step is to configure your connection to NetSuite as an ODBC data source. You can create one from the ODBC administration tool for your operating system or use an existing DSN. The configuration for our NetSuite Driver is highly customizable. As such, there are several properties that you will need to set when configuring the DSN in order to integrate NetSuite into your EDI solution efficiently. The default authentication properties will need to be set: Account Id, User, Password, and Role Id (optional). Additionally, you will need to configure the driver to use child tables and list aggregate data (in order to properly process the individual items in the Sales Order and Invoice). To do so, configure the following properties:

• Include Child Tables (Authentication): Set this property to True.
• Use Sessions (Authentication): Set this property to False to prevent the driver from creating lingering NetSuite sessions.
• Aggregate Column Mode (Misc): Set this property to List in order to properly insert items in the Sales Order.

Finally, click the Connectors tab in Arc and select the NetSuite Database Connector from the connector drop-down menu. On the Settings tab, you can configure your connection to your NetSuite instance. Click the ODBC radio button for the Connection Type and select the DSN you just configured from the drop-down menu for the Data Source Name (DSN). To confirm your connection, you can click the Test Connection button.

Document Translation / Data Integration Walk-Through

With the inbound X12 document, Sales Order template, and connection to NetSuite properly configured, you are now ready to begin the walk-through.

Translate an X12 Document to XML

As we work through the sample, we start with the assumption that your trading partner has just sent you an 850 document (purchase order) in X12 format. This document is sitting in the Send folder of the X12_WayneTech_IN Connector, and the document is ready to be processed. When you select the document and click the Send button on the Send tab of the X12_WayneTech_IN Connector, Arc will translate the standard X12 format into an XML format and place it in the Send folder for the MAP_850_IN Connector.

Translate an XML Purchase Order to an XML Sales Order

The MAP_850_IN Connector is a scripting connector designed to translate the 850 document (in XML) from the X12_IN Connector into a Sales Order (in XML) that can be processed by the NetSuite Database Connector.

Now that you have the XML file for the 850 document (a purchase order), you can use the script and template included in the sample to translate the document into a Sales Order formatted for use by the NetSuite database connector. The XML elements used in the mapping template are derived directly from the column listing for the Sales Order table in NetSuite, as exposed by the ODBC Driver.

How the Translation Works

The TranslationScript.rsb file maps the values at specific XPaths in the XML document to new fields, which will be used to create a new document based on the SALESORDER.template file.

Performing the Translation

To translate the XML 850 document, simply select the 850.xml document on the Send tab of the MAP_850_IN Connector and click Send. This will create a new document, SALESORDER_TIMESTAMP.xml, and place it in the Send folder of the NetSuite Connector.

Push a Sales Order to NetSuite and Retrieve an Invoice

The NetSuite Connector uses the CData ODBC Driver to add the Sales Order, which is ready for processing, to the NetSuite account. The NetSuite Connector can also be used to later retrieve the related Invoice(s) from the NetSuite account when these invoices are ready to be sent to your trading partner.

Now that you have a Sales Order in the Send folder for your NetSuite Connector, you can inject the Sales Order into NetSuite, generate a new Invoice, retrieve the new Invoice from NetSuite, and prepare the Invoice to be sent out.

Push the Sales Order to NetSuite

To insert the Sales Order into NetSuite, select the SALESORDER_TIMESTAMP item in the Send folder and click Send.

Generate a New Invoice

To generate a new Invoice, open your NetSuite account and navigate to the Sales Order that you just inserted. Once you have found the Sales Order, you will need to go through the process of fulfilling the Sales Order and Invoicing the fulfilled Sales Order. With that process completed, you are ready to go back to the Arc app and retrieve the Invoice that you will ultimately send back to your trading partner. For more information on processing a Sales Order and creating an Invoice in NetSuite, refer to the NetSuite Help documentation.

Retrieve the Invoice from NetSuite

Once the Invoice has been created in NetSuite, you can trigger the NetSuite Connector in Arc to retrieve it and place it, prepared to be translated and sent to the trading partner, in the Send folder for the MAP_810_OUT Connector. To do this, you will need to first edit the INVOICE Output Template. On the Settings tab for the NetSuite Connector, click the Edit button in the row for the INVOICE template in the Output Templates table. Replace the 'FILLER_CUSTOMER_ID' and the datetime in the WHERE clause of the selectQuery attribute with the Customer Id you used earlier and a datetime that falls just before the time you created the new Invoice for the Sales Order.

After you edit the INVOICE template, you can click the play () button to execute the template. Executing the template retrieves the newly created Invoice and creates an XML document ready to be processed in the MAP_810_OUT Connector. The XML elements used in the mapping template are directly derived from the column listing for the Invoice table in NetSuite, as exposed by the ODBC Driver.

Translate an XML Invoice to an XML 810 Document

Like the MAP_850_IN Connector, the MAP_810_OUT Connector is a scripting connector. In this case, the connector is designed to translate an Invoice in XML based on its NetSuite structure into XML that relates to the X12 format.

Because you already have the XML for the Invoice, you can use the script and templates included in the sample to translate the document into an XML format that is more closely related to the 810 document expected by your trading partner.

How the Translation Works

The TranslationScript.rsb file maps the values at specific XPaths in the XML document from the NetSuite Database Connector to new fields, which will be used to create a new document based on the 810 template file.

Performing the Translation

To translate the XML Invoice, simply select the INVOICE_TIMESTAMP.xml document in the Send folder of the MAP_810_OUT Connector and click Send. This will create a new document, 810_TIMESTAMP.xml, and place it in the Send folder of the X12_WayneTech_Out Connector.

Translate an XML 810 Document to an X12 810 Document

The last piece of the sample is the process of translating the 810 in XML format into X12 format. This is accomplished by clicking the 810_TIMESTAMP.xml file on the Send tab for the X12_WayneTech_OUT Connector and clicking Send. It is at this point that the 810/Invoice is ready for transport to your trading partner. In an end-to-end integration, you would then use a communication connector like AS2 or SFTP to transmit the data to the trading partner.

More Information & Next Steps

As you can see, Arc and CData Software make it possible to automate your business document exchange and seamlessly handle NetSuite EDI integration. For more information about Arc, visit our home page. For more information on the NetSuite ODBC Driver available from CData Software, visit the CData Software website. You can also see a full list of the data sources CData Software connects to with ODBC Drivers. Download your free, 30-day trial and start controlling your business document exchange today!