Using PeopleSoft EDI Manager for General Data Extraction

The primary purpose of PeopleSoft EDI Manager is to create PeopleSoft business documents, which contain business transaction data and are subsequently translated into X.12 or EDIFACT format and transmitted to a trading partner. However, you can also use it to extract data from database tables into a text file.

To create a text file from PeopleSoft database data:

1. Using Application Designer, create a view that extracts the data that you want and includes three EDI control fields.

   For example, to print a list of the countries in COUNTRY_TBL, create a view with the three EDI control fields described in the following table, plus the fields that you want from COUNTRY_TBL, perhaps Country andDescription.

   The EDI control fields must be the first three fields in the view. The fields are:

   Field	Description
   ECTRANSID	The transaction ID for the transaction that you want to perform. You’ll probably want to define a special transaction ID for this data extraction transaction.
   ECQUEUEINSTANCE	The instance ID.
   ECTRANSINOUTSW	Enter a value of O to specify an outgoing transaction.

   The Select statement for the view looks something like this:

   SELECT
   ‘CNTRY’,
   0,
   ‘O’,
   COUNTRY,
   DESCR
   FROM
   COUNTRY_TBL

   In this example, the transaction ID is CNTRY, the queue instance ID is always 0, and the inbound/outbound switch is always O (outbound). Because the queue instance is the same for all records, the EDI agent processes them as a single transaction.

   Note: For this example to work, you must use PeopleSoft EDI Manager to define the transaction ID CNTRY.

2. Create a record definition that specifies the layout of the PeopleSoft business document file.

   This record definition typically consists of the ECFILEROWID field and all the fields from the table (for the example,COUNTRY andDESCR.

3. Define the outbound map.

   In the example, you would specify CNTRY as the transaction ID on the first page in the Outbound Map Definition component. On the second page, the source record is the view that you created in step 1 and the Where clause is:

   WHERE ECTRANSID = ‘CNTRY’

   On the third page, the model file layout is the record definition that you created in step 2. After you’ve selected the record definition from the list, click the <Icon or Button Name> button to copy the field information into the page. You can use the default values for all the fields except ECFILEROWID. For ECFILEROWID, selectDefault in theConversion Processing group box and enter whatever value you want to appear in the first column of the text file.

4. Run the Outbound EDI Agent Preparer process.

5. Add an entry to the ECQUEUE table.

   The EDI agent uses the ECQUEUE table to determine the transactions that are ready to be processed. The fields in the table are.

   Field or Control	Definition
   ECTRANSID	The transaction ID, which tells the EDI agent which map definition to use. In the example, the transaction ID is CNTRY.
   ECQUEUEINSTANCE	The instance ID. The example uses a single instance ID, 0 (zero), for all the data.
   ECTRANSINOUTSW	The inbound/outbound switch. Extracting data is an outbound transaction, so the value is O (the letter O).
   ECBUSDOCID	The business document ID. Because you are not creating an EDI transaction, you can specify 0 (zero) in this field.
   ECQUEUESTATUS	The status of the current record. The EDI agent processes records with the status L.

   The remaining fields in the table (BUSINESS_UNIT, ECENTITYCD_BU, ECCUSTVNDRVAL, and ECENTITYCD_EXT) relate to trading partner information. Because you are not creating an EDI transaction, you can use generic partner information. Use the default value GENR in all these fields.

6. Schedule the outbound EDI agent to run.

   The outbound EDI agent extracts your data and processes any other transactions on the ECQUEUE table with the status L.

   The status field ECQUEUESTATUS changes to P (Processed). To repeat the extraction, change the status back to L.