Mapping EDI Transactions

This chapter provides an overview of Electronic Data Interchange (EDI) transaction mapping and discusses how to:

• Define inbound maps.

• Define outbound maps.

• Create data mapping profiles.

• Use PeopleSoft EDI Manager for general data extraction.

Understanding EDI Transaction Mapping

This section discusses:

• EDI transaction mapping.

• New EDI transactions.

• PeopleSoft business document format.

EDI Transaction Mapping

PeopleSoft applications store transaction data in tables in the PeopleSoft database. For an application to process an EDI transaction, it needs the transaction data in its tables. So the first step in processing an EDI transaction is transferring the data from an incoming EDI Transaction Set file into the application tables. Similarly, the first step in generating an EDI transaction for delivery to a trading partner is getting the transaction data out of the application tables into an EDI Transaction Set file.

You specify how the EDI agent transfers data between EDI Transaction set files and application tables by creating electronic commerce maps. Two kinds of electronic commerce maps are available:

• Inbound maps, which transfer incoming transaction data into your PeopleSoft database.

• Outbound maps, which create outgoing EDI documents from transaction data in the database.

Map profiles specify the maps to which your trading partners have access.

New EDI Transactions

We deliver PeopleSoft applications with a number of EDI transactions already defined. You can use PeopleTools to add support for additional transactions. Here are the major steps in the process.

To process new EDI transactions:

1. Use PeopleTools to develop the application that processes the transaction.

   Create the database tables and the pages that users need to process the transaction.

2. Define a PeopleSoft Business Document format based on the EDI format for the transaction.

   At this step, think about how the data from the EDI document needs to map to the PeopleSoft application tables.

3. Write a translation program that converts EDI documents into PeopleSoft Business Document format.

   Usually, you will have an EDI translator program to do this step.

4. Create staging tables to serve as a temporary holding area between the PeopleSoft document and the application tables.

5. Create maps to copy data between the PeopleSoft business documents and the staging tables.

6. Write an application load procedure that transfers data from the staging tables to the application tables.

This chapter documents step 5 in this procedure.

PeopleSoft Business Document Format

A PeopleSoft business document is an ASCII file that contains the data for one or more EDI transactions. It contains transaction data and control information, which tells the system what type of transaction it is, who it came from, and how parts of the document relate to each other.

A PeopleSoft business document is divided into records, separated from each other with a carriage return/line feed (CR/LF) pair. Each record is divided into fixed-length fields. One of the fields in the record, usually the first one, is a record ID field that specifies the record type: a header, detail line, summary line, and so on. The remaining fields depend on the type of record.

The first record in a PeopleSoft business document is always a control record. The control record specifies the type of transaction contained in the following lines and the trading partners involved. Based on the control record, the EDI agent retrieves the mapping definition for the specified transaction and the data conversion options relevant to the trading partner.

The EDI agent reads the records in the document one at a time. For each record following the control record, the EDI agent reads the value in the record ID field and uses it to determine the PeopleSoft record definition to use to parse the rest of the record. The PeopleSoft record definition specifies the location, size, and data type of the remaining fields in the record. The EDI agent copies the data from the document into staging tables in the database, following the rules in the mapping definition.

When the EDI agent encounters a new control record, identified by the record ID 999 or 998, it retrieves the mapping definition for the new transaction type and repeats the process. Within the flat file, the control records:

• Enable a third-party translator or trading partner, upon receiving the file, to determine what the data contains (999 only).

• Enable PeopleSoft EDI Manager, upon receiving the file, to determine what the data contains (999 and 998).

Most transactions include a variety of record types: a header, detail lines, schedules, summary lines, and so on. The layout of the records within a PeopleSoft business document follows a logical pattern. All detail lines linked to a particular parent or header line must follow the parent record.

Note. The EDI agent processes transactions at the “unit of work” level, corresponding to the ST/SE level in X.12 format. Each transaction should be a unit of work that you want the EDI agent to commit or roll back as a unit, such as a single purchase order or invoice.

Let’s look at an example:

001 Header Line 1
002 Detail Line 1A
002 Detail Line 1B
002 Detail Line 1C
001 Header Line 2
002 Detail Line 2A
002 Detail Line 2B

This file has two header lines and five detail lines. The EDI agent determines which detail lines go with which header lines based entirely on their order.

When the EDI agent processes this PeopleSoft business document, it works through the records in order. First, it processes header line 1 using the work record that’s appropriate for record ID 001. Next, it processes detail line 1A using the work record for record ID 002.

If you ask the EDI agent to calculate a value, such as the sum of all the detail lines, it determines which detail lines to group together based on their position. It calculates the value for all detail lines since the most recently encountered header line.

999 Record Format

This reserved record identifier acts as a control record to switch map definitions within a data file. The layout looks like this :

998 Record Format

This format allows the specification of a PeopleSoft map ID directly in the data contents. This type of map definition can be used when trading partner conversions are not needed or not available. All other data conversion functionality can be used.

Defining Inbound Maps

This section describes how to:

1. Create staging tables and work records.

2. Create inbound maps.

3. Define the inbound PeopleSoft business document format.

4. Specify staging tables.

You create one map for each EDI transaction.

Note. Electronic commerce maps help transfer data between PeopleSoft business documents and staging tables in a PeopleSoft database. They don’t apply to the application load or application extract processes, which copy data between the staging tables and the transactional tables.

Pages Used to Define Inbound Maps

Creating Staging Tables and Work Records

To create staging tables and work records:

1. In Application Designer, create the staging tables for the incoming data.

   Save the record definitions with the extension _EC to identify them as staging tables. Use SQL Create to create tables using the record definitions.

   The tables should be structured as an intermediate step between the PeopleSoft Business Document format that you want to translate and the structure of the transaction tables.

   The record definition for a staging table has three kinds of fields:

   • Required system fields, which must be key fields for the tables.

     Field Name	Description
     ECTRANSID	Transaction ID.
     ECQUEUEINSTANCE	Instance ID.
     ECTRANSINOUTSW	I or O, specifying an incoming or outgoing transaction.

   • Fields corresponding to the data fields in the PeopleSoft business documents.

   • (Optional) Extra fields for calculated data needed in the application tables, such as summary values, date stamps, or action codes.

2. In PeopleSoft Application Designer, create work records that mimic the structure of the records in the PeopleSoft business document that you want to translate.

   You need to create a work record for each physical record in the PeopleSoft business document file.

   One field in the work record needs to be designated as the record ID for the document record that it mimics. For most records, it’s the first field: the record ID in the PeopleSoft business document is usually the first data on the line.

   The EDI agent uses the record ID in the work record to match the appropriate work record with each record in the PeopleSoft business document. You can add the appropriate record ID as a default value for the field; PeopleSoft EDI Manager will automatically supply the correct record ID when you describe the business document format on the Business Document Layout page.

   The rest of the fields in the work record definition should match the corresponding fields in the PeopleSoft Business Document record. They should be in the same order and should have the same length. Add default values for any fields that have them.

   Save the work record with the extension _WD to identify it as a work document.

See Also

PeopleSoft Business Document Format

Creating Inbound Maps

Access the Inbound Maps - Description page.

To create an inbound map:

1. In the EC Transaction ID field, select the transaction to which this map applies.

2. Enter a description of the map.

   You can enter both a short description (in the Description text box) and a long description (in the larger box). These descriptions are for your information only; the system does not use them.

3. (Optional.) Copy the definition of a similar map.

   You can copy the details of an existing map definition into this component and change just the parts that are different.

   Select the existing map in the Source Map Definition field, and then click the Ec Copy Map DefnSwitch button. The system copies the information from the selected map into this page.

See Also

Using PeopleSoft EDI Manager

Defining the Inbound PeopleSoft Business Document Format

Access the Inbound Maps - Business Document Layout page.

You have already created work record definitions that mimic the structure of the PeopleSoft business documents. On this page, you specify which work record definition to use for each record in the PeopleSoft business documents and what type of data appears in each field.

To define the inbound format:

1. Select a work record definition from the Business Doc Record field, and then click the EC Get File Fields button.

   PeopleSoft EDI Manager copies data from the selected record definition into the Map group box, with one row for each field in the record.

2. Specify the record ID for this work record definition.

   If the first field in the work record definition is not the one for record IDs, browse the rows in the Map group box to find the record ID field. The EDI agent uses this field to match the work record definition to the appropriate records in PeopleSoft business documents.

   1. In the Map group box for the appropriate record ID, select the Record ID option in the Field Value Conversion group box. The EC File Row ID Value text box appears.

   2. In the text box, enter the record ID of the records whose structure this work record definition mimics. When the EDI agent finds a record in a PeopleSoft business document with this record ID, it uses this work record definition to parse it.

3. Select the next field in the work record definition.

4. Check the location of a field in the record.

   If you defined the work record definition properly, the correct field information appears in the text boxes, copied from the work record. You can verify the existing information rather than entering it. If the format of the PeopleSoft business document is slightly different than the work record definition, you can modify the formatting on this page to match the document without affecting the work record definition.

   The Seq (sequence) text box gives the number of the field in the record. The first field in the record is field 1, the second is field 2, and so on. The Name field is the field name from the work record, and the Field Type field indicates the data type of the field’s data.

   The Start and Length boxes specify the location and size of the field in the PeopleSoft business document. The start position is the number of characters from the beginning of the record to where the first character of this field’s data appears. The length is the number of characters reserved for this field; it’s also the number of characters that the system will transfer to the staging tables.

   Note. Length is defined in characters, not in bytes.

   For numeric data, the Dec (decimal positions) field indicates the number of characters to the right of the decimal point. If an incoming PeopleSoft business document uses implicit decimal format, that is, if it does not include decimal points in this field (in any row), the EDI agent inserts them (in all rows) at the position specified in this box. If the data already includes decimal points, the EDI agent does not change them or insert any other decimal points.

   For date information, you also need to enter a date format and delimiter. You use these text boxes to specify the format for dates in this field in the incoming file. Use standard date formatting conventions, as shown in this table:

   Date Value	Date Format	Delimiter
   19961226	YYYYMMDD	N (for none)
   1996/12/26	YYYYMMDD	/
   DEC–96–26	MMMYYDD	–
   26–DEC–1996	DDMMMYYYY	–
   26.12.1996	DDMMYYYY	–

5. Select an option in the Special EDI Attribute group box to specify whether the data in the current field is transaction data or an EDI control code.

   Options are:

   None	Normal transaction data.
   EC Entity Code	An entity code specifies the type of business entity that the trading partner ID for the current transaction refers to. The entity code tells the EDI agent that the table to use to find the list of valid IDs.
   EC Queue Control Number	A unique identifying number that you want to store in the EDI agent queue and refer to when reviewing EDI audit history. For example, in a purchase order, you might identify the PO number as the EC queue control number.
   Pri Evt Cd (primary event code) and Sec Evt Cd(secondary event code)	The primary event code, sometimes called the purpose code. The EDI agent uses event codes to determine the appropriate action code. If you identify a primary event code, you must also identify a secondary event code, sometimes called the transaction code, in the same record.

6. Specify how the EDI agent needs to convert the data as it copies data from the field into the staging tables.

   Field value conversion options are:

   None	The EDI agent copies the data exactly as it appears in the PeopleSoft business document field.
   TP Convert	The EDI agent converts data from this field according to the rules defined in a conversion type definition. When you select this option, a drop-down list appears to select a conversion type definition.
   Record ID	Use this option for the first field in the work record definition only. (See step 3.)
   Convert	The EDI agent converts the data as specified in the From and To boxes. When the field in the PeopleSoft business document contains the value in the From box, the EDI agent converts it into the value in the To box. Enter a row for each possible value in the field.

   Note. You can perform data conversion at several points during processing. Use the Convert option for conversions that apply to this map only and are valid for all trading partners.

7. Repeat steps 4 through 6 for each field in the work record definition.

8. Repeat steps 1 through 7 for each record in the PeopleSoft business document.

   To associate another work record definition with this map, put the cursor in the Business Doc Record field and press the F7 key.

See Also

Using PeopleSoft EDI Manager

Specifying Staging Tables

Access the Inbound Maps - Target Records page.

To specify staging tables:

1. Select a work record definition.

   Browse the rows in the File group box to select a work record definition that you added to this mapping on the previous page. The record definition name appears in the File Layout Model field.

2. In the Target Record field, select the staging table to copy data into, and then click the EC Get Recfields SW button.

   Field information is copied from the record definition into the page.

3. Select a field in the staging table record definition.

   Browse the rows in the Target Field Settings group box to select the field.

   The Sequence field gives the position of the field in the record. The field name is from the record definition, and the field type indicates the data type of the field’s data. All of this information comes from the record definition that you selected.

4. In the Set Field Equal To group box, select the value that you want the EDI agent to copy into the selected field.

   To copy the value from a field in the PeopleSoft business document, select the File Field Value option. Enter the name of the field whose value you want to copy in. Use the field name from the work record definition that appears as the file layout model at the top of the page.

   To enter a default value into the table, select the Default Value option and enter the value in the text box that appears.

   Note. You can specify both a file field to copy data from and a default value. The EDI agent enters the default value into the staging table only when the specified file field does not have a value.

   To enter a value that the EDI agent calculates as it copies data into the staging table, select the EC Agent Calc’d (EC agent calculated) option, and then select a calculation option from the drop-down list box that appears. Options include:

   Calculation Option	Value Written in the Field
   Action Code Conversion	An action code. The EDI agent gets the values from the fields in this record labeled Primary Event Code and Secondary Event Code, looks up the combination in the partner profile for the trading partner, and inserts the appropriate action code.
   Apportion Parent Value	A percentage of the value from the parent line. The EDI agent takes the numeric value from the preceding parent line, divides it by the number of child lines, and puts the resulting value in this field on each child line. You have to complete out the Related Record Info group box to identify the parent line.
   Average Summary Value	The average value from a particular field. The EDI agent adds up the values in this field from all occurrences of this record reporting to the same parent line, divides by the number of records, and enters the result in this field. You have to complete the Related Record Info group box to identify the field whose average you want.
   Business Document Level External TPID (business document level external trading partner ID)	The trading partner ID of the trading partner that generated the EDI transaction. The EDI agent gets the trading partner ID from the first record in the PeopleSoft business document.
   Business Document Level Internal TPID (business document level internal trading partner ID)	The trading partner ID of the internal group to which the EDI transaction is addressed. The EDI agent gets the trading partner ID from the first record in the PeopleSoft business document.
   Current Date	The date on which the EDI agent processes the PeopleSoft business document.
   Current Date and Time	The date and time when the EDI agent processes the PeopleSoft business document.
   Current Time	The time when the EDI agent processes the PeopleSoft business document.
   EC Queue Instance	The system-generated queue instance ID given to this PeopleSoft business document. Note. To create a valid mapping, you must map this value to the ECQUEUEINSTANCE field.
   EC Transaction ID	The transaction ID from the first record in the PeopleSoft business document. Note. To create a valid mapping, you must map this value to the EDTRANSID field.
   File Name/File ID	The name of the PeopleSoft business document file.
   Incremented Key Sequence Number	A system-generated sequence number. The EDI agent increments the number for each child row of a particular parent. It starts at one again when it reaches a new occurrence of the parent line. You have to complete the Related Record Info group box to identify the parent line.
   Inherit Parent Value	A value copied directly from the parent line. You have to complete the Related Record Info group box to identify the parent line.
   Maximum Summary Value	The maximum value of a field. The EDI agent checks all the occurrences of a specified record and field and copies the maximum value into this field. You have to complete the Related Record Info group box to identify the field whose maximum you want.
   Minimum Summary Value	The minimum value of a field. The EDI agent checks all the occurrences of a specified record and field, and copies the minimum value into this field. You have to complete the Related Record Info group box to identify the field whose minimum you want.
   Operator ID	The ID of the user who started the EDI agent.
   Process Instance	The system-generated instance ID from PeopleSoft Process Scheduler.
   Run Control ID	The run control ID of the run control used to start the EDI agent.
   Total/Accumulate Summary Value	The total of the values in a field. The EDI agent adds the values from a specified field in all occurrences of a record reporting to the same parent line and enters the result in this field. You have to complete the Related Record Info (related record information) group box to identify the field whose total you want.
   Trading Partner Conversion	The customer, vendor, or business unit that is associated with a trading partner ID.

5. Enter the related record information (for some calculated options only).

   For some of the calculation options that you just selected, you identify the row, record, and field to use in the calculation.

   For example, if you selected the Total/Accumulate Summary Value option, the EDI agent adds up the values from the specified row, record, and field.

   Note. If you selected the Increment Key Sequence Number option, only the Row field appears. You don’t need to select a record or field.

6. Repeat steps 3 through 5 for each field in the staging table.

   To create a valid mapping, you must enter the appropriate values in the three required system fields:

   • ECTRANSID: Use the EC Transaction ID option to have the EDI agent put the transaction ID in this field.

   • ECQUEUEINSTANCE: Use the EC Queue Instance ID option to have the EDI agent put the instance ID in this field

   • ECTRANSINOUTSW: Enter I to specify an incoming transaction.

7. Repeat steps 2 through 6 for each staging table that you want to copy data into.

8. Repeat steps 1 through 7 for each work record definition.

Defining Outbound Maps

This section discusses how to:

1. Create outbound maps.

2. Specify source records.

3. Define the outbound PeopleSoft Business Document format.

Pages Used to Define Outbound Maps

Creating Outbound Maps

Access the Outbound Maps - Description page.

The initial procedure for creating an outbound map is the same as that for creating an inbound map.

See Creating Inbound Maps.

Specifying Source Records

Access the Outbound Maps - Source Records page.

To specify source records:

1. In the Source Record field, select the record definition that retrieves the data that you want from the staging tables.

   You might select the record definition used to define the table or a view that retrieves just the data that you want.

   Add lines to the PeopleSoft business document in a logical unit of work order: header information before detail line information, parent lines before child lines, and so on.

   Note. You don’t need to create a control record for the PeopleSoft business document (the 999 or 998 record). The EDI agent creates one automatically from information in the queue that stores pending outbound transactions.

2. Select a parent record (child lines only).

   If the record definition that you selected in the previous step retrieves data for a record that is related to a preceding parent record, such as the detail line under a header, select the record definition for the parent record in the Parent Record field.

   The parent record is typically the record definition that you used for the previous row in the outbound mapping.

3. Enter the SQL Where clause to specify which rows to retrieve.

   When you’re creating a header line, the Where clause needs to select the header information for a single transaction. You identify a single transaction using the transaction ID and queue instance ID. The Where clause should look like this:

   WHERE ECTRANSID = $ECTRANSID$ AND ECQUEUEINSTANCE = $ECQUEUEINSTANCE$

   The two values inside dollar signs are system variables that the EDI agent replaces with the transaction ID and queue instance ID, respectively, of the first transaction in the pending outbound queue. This Where clause retrieves only the data for one transaction.

   If you’re extracting the data for child lines, the Where clause must include a link to the parent record to tell the EDI agent how to join the tables. The Where clause needs to include a statement of this form:

   WHERE fieldname = $record.fieldname$

   The first fieldname is a field from the source record definition; record.fieldname is a field in the parent record definition. You must include the dollar signs around record.fieldname.

4. Repeat steps 1 to 3 to specify the record definition for each record to go into the PeopleSoft business document.

Defining the Outbound PeopleSoft Business Document Format

Access the Outbound Maps - Target Business Doc Layout page.

For each of the source record definitions that you added on the previous page, you identify an associated work record definition that specifies how the EDI agent writes the staging table data into PeopleSoft business documents.

To define the outbound format:

1. Select the work record definition that specifies the format of the output record, and then click the Get File Fields SW button.

   The File Row ID field identifies a source record definition from the previous page. In the Model File Layout field, select the work record definition that the EDI agent will use to write data from the source record into PeopleSoft business documents.

   When you click the Get File Fields SW button, the system copies field information from the work record definition into the page. The Target Record Field Info group box displays information about where in the record it will write each field’s data.

   Depending on the data type of the record field, the Target Record Field Info group box offers additional formatting options so that you can format the data in the outgoing PeopleSoft business document.

   • For any field, enter in the Strip Chars field any characters that you don’t want the outbound EDI agent to include when it copies data into the outbound transaction file. For example, you may want to strip the hyphens from phone numbers and Social Security numbers, or the decimal from a currency amount. When the outbound EDI agent writes data from a field into an outbound transaction file, it first removes all characters in the Strip Chars field.

     Note. The Strip Chars field contains a list of the individual characters to remove, not a string to remove. For example, if the field data is 455-67-8898 and the Strip Chars field has - (a hyphen) in it, the EDI Agent writes 455678898 to the file. If the Strip Chars field has the string -5 in it, the EDI Agent writes the field data to the file as 4678898.

   • For character fields, select the Convert to Upper Case check box to tell the outbound EDI agent to write the contents of the field in all uppercase letters.

   • For character fields or numeric fields, use the Pad Character field to specify a character for the outbound EDI agent to use to pad data so that it is the full length of the field. For character fields, the outbound EDI agent adds the specified character to the left of the existing field information. For numeric fields, the most typical use is to pad a number with initial zeros.

   • For date fields, you can specify in the Date Fmt (date format) and Delimiter fields how to format dates in this field in the business document. Use standard date formatting conventions, as illustrated in the procedure for creating inbound maps.

     See Defining the Inbound PeopleSoft Business Document Format.

2. Specify the data that you want the EDI agent to enter in the field.

   In the Conversion Processing group box, select the value that you want the EDI agent to enter into the selected field.

   • To copy the value from a field in the source record, select the Source Field option. Enter the name of the field whose value you want to copy in.

     Note. You need to specify a source field for all fields in the work record definition except those for which you provide a default value. You must select a source field even if you select another option as well.

   • To enter a default value into the document, select the Default option and enter the value in the field that appears.

     You can specify both a file field to copy data from and a default value. If you do, the EDI agent enters the default value into the document only when the specified field does not have a value.

   • If you select TP Conversion,, the EDI agent converts data from the specified source field according to the rules defined in a conversion type definition. When you select this option, a drop-down list box appears to select a conversion type definition.

   • If you select Convert, the EDI agent converts the data in the source field as specified in the From and To fields that appear. When the field in the PeopleSoft business document contains the value in the From field, the EDI agent converts it into the value in the To box. Enter a row for each possible value in the field.

   • To enter a value that the EDI agent calculates as it copies data into the staging table, select the Agent Value option, and then select a calculation option from the drop-down list box that appears. The following table describes the available calculation options.

     Calculation Option	Value Written in the Field
     Action Code/Primary Event Code	An action code. The EDI agent gets the values from the fields in this record labeled Primary Event Code and Secondary Event Code, looks up the combination in the partner profile for the trading partner, and inserts the appropriate action code.
     Current Date	The date on which the EDI agent creates the PeopleSoft business document.
     Current Datetime	The date and time when the EDI agent creates the PeopleSoft business document.
     Current Time	The time when the EDI agent creates the PeopleSoft business document.
     EC Entity Code Flag	Flags this field as an entity code field to use in determining trading partner conversion logic. The values specify whether the entity is a customer, vendor, or business unit.
     EC Queue Instance	The system-generated queue instance ID given to this PeopleSoft business document.
     EC Trans ID (EC transaction ID)	The transaction ID.
     Incremented Sequence Nbr (incremented sequence number)	A system-generated sequence number. The EDI agent increments the number for each child row of a particular parent. It starts at 1 again when it reaches a new occurrence of the parent line.
     Secondary Event Code	The secondary event code from the action code conversion.
     Trading Partner Conversion	The trading partner ID associated with the customer, vendor, or business unit.

3. Move to the next field in the record definition and repeat step 2.

4. Scroll to the next source record definition and repeat steps 1 through 3.

5. Save the map.

6. Run the Outbound EDI Agent Preparer to create the Outbound EDI Agent SQC.

   After you create or modify outbound maps, you need to run a special compilation process that prepares the maps for use by the EDI agent.

   See Monitoring EDI Processing.

   Note. Run the Outbound EDI Agent Preparer only after you’ve made all your changes to outbound maps. You only need to run it only once to prepare all outbound maps.

See Also

Defining the Inbound PeopleSoft Business Document Format

Creating Data Mapping Profiles

This section provides an overview of data mapping profiles and discusses how to create data mapping profiles.

Understanding Data Mapping Profiles

When you add a trading partner, you assign to it a map profile, which lists the electronic commerce maps that the EDI agent can use to process transactions from the partner. The map profile serves two purposes:

• It restricts a trading partner’s access to transactions that they aren’t authorized to exchange with your company.

• By assigning different map profiles to different trading partners, the EDI agent processes the same transaction differently for different partners.

Page Used to Create Data Mapping Profiles

Creating Data Mapping Profiles

Access the Ec Map Profile Def (Map Profile Definition) page.

To create a map profile, add the maps for all the transactions that you want partners with this map profile to use. Select a map ID in the EC Map ID field, and then add a new row to add another map. Don’t forget to add both inbound and outbound maps.

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 and Description.

   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 and DESCR.

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, select Default in the Conversion 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.

   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.