In this tutorial, you will learn how to query a database table and write the data to an EDI X12 document using version 4010 of the 850 Purchase Order Transaction Set from the Add-on Marketplace.
Create a New Project
To begin, create a new Project following the Getting Started with Projects tutorial.
SQL Select
From within the Project Designer, expand the Database folder in the Component Library, and then drag the SQL task to the Project Outline.
On the SQL task, select the Database Server Resource from the drop-down list.
Click the Add button to Add a Query to the SQL task. In this example, the table being queried is titled “purchase_order”. We will specifically query the Purchase Order Segment, PO1. The following is an image of the PO1 segment data contained in the purchase_order database table.
Type in your SQL select statement in the SQL Statement field. Alternatively, you can use the SQL Wizard for quickly building the SQL select statement. With the SQL Wizard, you can choose schemas (libraries), tables (physical files), columns (fields), column headings, "where" clauses, and "order by" criteria. To access the SQL Wizard for building an SQL select statement, click the browse icon on the right side of the SQL Statement field. See the SQL Wizard tutorial for more information.
Name the Output Variable that will contain that table data. For this example, we will name the variable “POdata” because it contains the Purchase Order segment data of the EDI X12 purchase order.
Write EDI X12
Expand the Data Translation folder in the Component Library and drag the Write EDI X12 task to the Project Outline.
On the Write EDI X12 task, specify values for the following attributes:
- Output File – The path and file name of the EDI X12 file to write.
- Sender ID – The identifier of the sender.
- Receiver ID – The identifier of the receiver.
- Control Number – The value used to uniquely identify the message.
- X12 Transaction Set - Select an EDI X12 Transaction Set from the list of those installed. In this example, select the 850 Purchase Order – 4010 Transaction Set. Transaction Sets can be downloaded from the Add-on Marketplace.
Click the Launch Data Mapping Wizard button on the Write EDI X12 task main panel to open the EDI X12 Data Mapper. When you first launch the EDI X12 Data Mapper, you will see an input RowSet container and an EDI X12 Transaction Set container. The input RowSet container should be named after the Output Variable defined in the SQL task. The order of the columns contained in the RowSet variable should mirror the order of those same columns in the database purchase_order table. When finished mapping Transaction Set elements to the input RowSet container, click Apply.
The following is an image of the Project Outline for the Write EDI X12 example.
Execute Project
Click the Execute button. The Project will save, validate, and then execute. Once the Project has finished executing, a View Job Log link is displayed. You can review the log or download it as a text file, which can then be emailed to tech support if requested.