JD Edwards EnterpriseOne Tools Table Conversion Guide

JD Edwards EnterpriseOne Tools

Table Conversion Guide

Release 9.1

E24236-01

December 2011

JD Edwards EnterpriseOne Tools Table Conversion Guide, Release 9.1

E24236-01

This software and related documentation are provided under a license agreement containing restrictions on

use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your

license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,

transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse

engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is

prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If

you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it

on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data

delivered to U.S. Government customers are "commercial computer software" or "commercial technical data"

pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As

such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and

license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of

the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software

License (December 2007). Oracle America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

This software or hardware is developed for general use in a variety of information management

applications. It is not developed or intended for use in any inherently dangerous applications, including

applications that may create a risk of personal injury. If you use this software or hardware in dangerous

applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other

measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages

caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of

their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks

are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD,

Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced

Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information on content, products,

and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly

disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle

Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your

access to or use of third-party content, products, or services.

iii

Contents

Preface ................................................................................................................................................................ vii

Audience...................................................................................................................................................... vii

Documentation Accessibility.................................................................................................................... vii

Related Documents .................................................................................................................................... vii

Conventions ............................................................................................................................................... viii

1 Introduction to JD Edwards EnterpriseOne Table Conversion

1.1 JD Edwards EnterpriseOne Table Conversion Overview..................................................... 1-1

1.2 JD Edwards EnterpriseOne Table Conversion Implementation Steps ............................... 1-1

2 Understanding Table Conversion

2.1 Table Conversions....................................................................................................................... 2-1

2.2 Types of Tables to Use................................................................................................................ 2-2

2.3 Business Views in Table Conversions...................................................................................... 2-2

2.4 Text Files in Table Conversions ................................................................................................ 2-3

2.5 Sort and Selection Criteria in Table Conversions................................................................... 2-3

2.6 Input and Output Environments.............................................................................................. 2-3

3 Setting Up Table Conversions

3.1 Understanding Table Conversion Setup ................................................................................. 3-1

3.2 Understanding the Table Conversion Process Flow.............................................................. 3-1

3.3 Adding Table Conversions........................................................................................................ 3-4

3.3.1 Understanding the Navigation Assistant......................................................................... 3-4

3.3.2 Prerequisites ......................................................................................................................... 3-4

3.3.3 Forms Used to Add New Table Conversions.................................................................. 3-5

3.3.4 Adding New Table Conversions....................................................................................... 3-5

3.3.5 Starting the Table Conversion Director............................................................................ 3-6

3.4 Converting Data.......................................................................................................................... 3-6

3.4.1 Understanding Data Conversion....................................................................................... 3-6

3.4.1.1 Data Conversion Example........................................................................................... 3-7

3.4.2 Forms Used to Convert Data.............................................................................................. 3-7

3.4.3 Defining External Data for Table Conversions ............................................................... 3-8

3.4.4 Defining Input and Output Environments for Table Conversions.............................. 3-8

3.4.5 Defining Table Conversion Input...................................................................................... 3-8

3.4.6 Defining Table Conversion Output................................................................................ 3-11

3.4.7 Mapping Inputs to Outputs ............................................................................................ 3-12

3.4.8 Specifying Table Conversion Logging Options ........................................................... 3-13

3.4.9 Reviewing Table Conversion Results ............................................................................ 3-14

3.5 Defining File Formats for Table Conversion Input or Output .......................................... 3-14

3.5.1 Understanding User-Defined Formats.......................................................................... 3-15

3.5.1.1 Importing and Exporting Text Files........................................................................ 3-15

3.5.1.2 Using User-Defined Formats as Input.................................................................... 3-16

3.5.1.3 Using User-Defined Formats as Output................................................................. 3-16

3.5.2 Prerequisite........................................................................................................................ 3-16

3.5.3 Defining Delimited File Formats with a Single Format.............................................. 3-16

3.5.4 Defining Delimited File Formats with Multiple Formats........................................... 3-17

3.5.5 Defining Fixed-Width File Formats with a Single Format ......................................... 3-19

3.5.6 Defining Fixed-Width File Formats with Multiple Formats ...................................... 3-20

3.6 Copying Data............................................................................................................................ 3-21

3.6.1 Understanding Copying Data......................................................................................... 3-21

3.6.2 Prerequisites ...................................................................................................................... 3-21

3.6.3 Defining Data Copy Actions........................................................................................... 3-21

3.7 Copying Data with Table Input............................................................................................. 3-23

3.7.1 Understanding How to Copy Data with Table Input ................................................. 3-23

3.7.2 Prerequisites ...................................................................................................................... 3-23

3.7.3 Defining Data Copy with Table Input Actions ............................................................ 3-24

3.8 Deleting Groups of Records ................................................................................................... 3-24

3.8.1 Understanding Batch Deletion ....................................................................................... 3-25

3.8.1.1 Example: Creating Purge Programs as a Batch Delete......................................... 3-25

3.8.2 Prerequisites ...................................................................................................................... 3-36

3.8.3 Defining Batch Deletion Actions .................................................................................... 3-37

3.9 Using Event Rules in Table Conversions ............................................................................. 3-38

3.9.1 Understanding Event Rules in Table Conversions...................................................... 3-38

3.9.1.1 Table Conversion System Functions....................................................................... 3-38

3.9.2 Creating Event Rules in Table Conversions .................................................................

3-39

4 Running Table Conversions

4.1 Understanding Table Conversion Processing ........................................................................ 4-1

4.2 Submitting Table Conversions.................................................................................................. 4-1

4.2.1 Understanding Submitting Table Conversions............................................................... 4-1

4.2.2 Understanding Overriding Table Conversion Properties ............................................. 4-2

4.2.2.1 Table Conversions on the Web Client ....................................................................... 4-2

4.2.3 Forms Used to Submit Table Conversions....................................................................... 4-2

4.2.4 Overriding Table Conversion Properties at Runtime .................................................... 4-3

4.2.5 Overriding Table Conversion Properties of New Versions........................................... 4-4

4.2.6 Overriding Table Conversion Locations .......................................................................... 4-5

4.2.7 Running Table Conversions from the Web Client.......................................................... 4-5

4.3 Testing Table Conversions ........................................................................................................ 4-5

4.3.1 Understanding Table Conversion Testing....................................................................... 4-5

4.3.1.1 Difference Between Logging Options and Debug Logging................................... 4-6

4.3.1.2 Trace Levels................................................................................................................... 4-6

4.3.2 Setting the Trace Level for Debug Logging on the Workstation.................................. 4-7

4.3.3 Setting the Trace Level for Debug Logging on the Server............................................. 4-8

4.3.4 Forcing Row-By-Row Conversion..................................................................................... 4-8

5 Preparing Foreign Tables for Table Conversion

5.1 Understanding Foreign Tables.................................................................................................. 5-1

5.2 Adding Data Sources.................................................................................................................. 5-2

5.2.1 Forms Used to Add Data Sources ..................................................................................... 5-2

5.2.2 Adding Data Sources .......................................................................................................... 5-2

5.3 Adding Environments................................................................................................................ 5-5

5.3.1 Forms Used to Add Environments ................................................................................... 5-5

5.3.2 Adding Environments ........................................................................................................ 5-5

5.4 Setting Up Default OCM Mappings......................................................................................... 5-6

5.4.1 Understanding OCM Mappings........................................................................................ 5-6

5.4.2 Forms Used to Set Up Default OCM Mappings.............................................................. 5-7

5.4.3 Setting Up Default OCM Mappings ................................................................................. 5-7

Index

vii

Preface

Welcome to the JD Edwards EnterpriseOne Tools Table Conversion Guide.

Audience

This guide is intended for system administrators and technical consultants who are

responsible for using the JD Edwards EnterpriseOne Table Conversion tool to rapidly

manipulate data in tables.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle

Accessibility Program website at

http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc

Access to Oracle Support

Oracle customers have access to electronic support through My Oracle Support. For

information, visit

http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info

visit

http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs

if you are hearing

impaired.

See Also:

■ Preparing Foreign Tables for Table Conversion.

Input and Output Environments

Understanding Table Conversion 2-3

A business view defines the relationship between two or more tables and joins the

data into a single view. You can use JD Edwards EnterpriseOne business views for

input to the table conversion only, not for output.

The system does not provide direct support for joining foreign tables. To use multiple

foreign tables as input to a conversion, you must first define them through JD

Edwards EnterpriseOne and then create a business view for them.

2.4 Text Files in Table Conversions

You can use table conversions to import directly from or export directly to a text file.

When you convert a text file, it is stored with a single, long text field. User-defined

formats are stored the same for a text file as for any table. With a text file conversion,

the table name includes the path and the file name. If you do not specify the path with

the file name, the default path is used.

2.5 Sort and Selection Criteria in Table Conversions

You can specify sort criteria in a table conversion. Sorting is used to process input rows

in a sequence that groups related records together. The sort and selection features

simplify the process of writing records to multiple tables in a typical one-to-many

conversion. For example, if you have a single table of customer information, you can

sort the data by area code and split the table into individual tables for each area code.

Similarly, you can specify selection criteria for the input table to convert only a subset

of that table.

JD Edwards EnterpriseOne Table Conversion Design Aid also enables you to add logic

to determine when a change should occur to the value in a field.

2.6 Input and Output Environments

An Oracle's JD Edwards EnterpriseOne environment consists of:

■ A path code.

■ Oracle's JD Edwards EnterpriseOne Object Configuration Manager (OCM)

mappings.

The path code of the environment is used to locate specification files for the

environment. This path code is usually a subdirectory of the JD Edwards

EnterpriseOne directory on a workstation. To reference JD Edwards EnterpriseOne

tables in an environment, the full path code must exist on the machines where the

conversion is created and run. Foreign tables can be referenced even if the path code

does not exist.

OCM Mappings indicate where JD Edwards EnterpriseOne objects reside. JD Edwards

EnterpriseOne Table Conversion Design Aid enables you to specify an input and

output environment, which is used to locate input and output tables. To locate foreign

tables, JD Edwards EnterpriseOne Table Conversion Design Aid uses the default OCM

mapping for tables.

JD Edwards EnterpriseOne Table Conversion Design Aid uses these three

environments when it processes a table conversion:

■ The environment that you are signed into.

Determines where the table conversion specifications are stored.

■ The environment where the input tables reside.

Input and Output Environments

2-4 JD Edwards EnterpriseOne Tools Table Conversion Guide

■ The environment where the output tables reside.

You can select one environment for the input tables (or business views) and one for the

converted output tables. The three environments can be the same or they can be

different.

When you use JD Edwards EnterpriseOne tables (or business views) in a table

conversion, the environment provides the details of each table or business view, such

as:

■ Column names

■ Data types

■ Descriptions

Because this information comes from the JD Edwards EnterpriseOne specification

tables, the system table does not have to exist in the database before you can design a

table conversion; however, it must be created before the conversion is run. If you use a

foreign table as input, you need to create it before you design the table conversion. The

JD Edwards EnterpriseOne Table Conversion tool obtains its information about the

table directly from the database where the foreign table resides. The environment also

provides a default path for text files.

See Also:

■ JD Edwards EnterpriseOne Tools Table Design Guide.

■ Preparing Foreign Tables for Table Conversion.

■ "Setting Up Path Codes" in the JD Edwards EnterpriseOne Tools

Configurable Network Computing Implementation Guide.

■ "Setting Up Environments" in the JD Edwards EnterpriseOne Tools

Configurable Network Computing Implementation Guide.

■ "Setting Up Data Sources" in the JD Edwards EnterpriseOne Tools

Configurable Network Computing Implementation Guide.

Setting Up Table Conversions 3-1

Setting Up Table Conversions

This chapter contains the following topics:

■ Section 3.1, "Understanding Table Conversion Setup"

■ Section 3.2, "Understanding the Table Conversion Process Flow"

■ Section 3.3, "Adding Table Conversions"

■ Section 3.4, "Converting Data"

■ Section 3.5, "Defining File Formats for Table Conversion Input or Output"

■ Section 3.6, "Copying Data"

■ Section 3.7, "Copying Data with Table Input"

■ Section 3.8, "Deleting Groups of Records"

■ Section 3.9, "Using Event Rules in Table Conversions"

3.1 Understanding Table Conversion Setup

Oracle's JD Edwards EnterpriseOne Table Conversion Design Aid uses a director to

guide you through the steps of setting up table conversion templates. Using the

director, you can design table conversions for:

■ Converting data.

■ Copying tables between locations.

■ Deleting records within tables.

You can also define user-defined formats for use as input or output tables in table

conversion templates. User-defined formats are flat-file tables that store data as a

continuous string of information, such as bank tapes.

After you set up a table conversion template, you can save it, create a version, and run

the version multiple times.

3.2 Understanding the Table Conversion Process Flow

When you process a table conversion, the system triggers events that are similar to the

events that are triggered when a report or application is run. These events are specific

to the table conversion that you defined. Events provide pauses in the processing of

the table conversion where you can attach logic.

See Also:

■ Understanding User-Defined Formats.

Understanding the Table Conversion Process Flow

3-2 JD Edwards EnterpriseOne Tools Table Conversion Guide

In general, the event flow is the same for all table conversion types because these

conversion types are all subsets of a data conversion:

■ Data Copy.

This conversion type does not include input and output tables; all actions are

accomplished through the Process Begin event.

■ Data Copy with Table Input.

This conversion type does not include output tables; all actions are accomplished

through the Process Begin, Process End, and Row Fetched events.

■ Batch Delete.

As with the Data Copy with Table Input type, this conversion type does not

include output tables; all actions are accomplished through the Process Begin,

Process End, and Row Fetched events.

The flexibility of events enables you to use table conversion types within other

conversion types, if necessary.

Depending on the type of conversion that you define, some events might not be

triggered. This diagram illustrates all events that can be triggered in a table

conversion; events are indicated by yes or no decisions:

Understanding the Table Conversion Process Flow

Setting Up Table Conversions 3-3

Figure 3–1 Table conversion event flow.

This table describes when each event occurs during the table conversion process:

Event Description

Process Begin Before fetching records from the input table, the system

invokes the Process Begin event. At this point, you can

attach logic that needs to run only once at the beginning

of a table conversion, or attach any value that does not

change for each record. This event is useful for mapping

output fields that do not change for each record.

Open input and

output tables

Set selection and

sequencing on

input table

Clear

output tables

Process

begin event

System fetches

records from the

input table

Data changed

events

Data

sequencing?

User defined

formats?

Data

changed in sort

column?

Yes

Row fetched

Appropriate format

fetched event

Yes

records?

Return to fetch

record

Process

end event

YesNo

Adding Table Conversions

3-4 JD Edwards EnterpriseOne Tools Table Conversion Guide

3.3 Adding Table Conversions

This section provides an overview of the navigation assistant, lists prerequisites, and

discusses how to:

■ Add new table conversions.

■ Start the Table Conversion director.

3.3.1 Understanding the Navigation Assistant

The JD Edwards EnterpriseOne Table Conversion director includes a navigation

assistant. The companion Navigation Assistant tracks where you are in the table

conversion development process. You can right-click the Navigation Assistant to hide

it for the current design process. The navigation assistant provides a visual indicator of

the progress in designing the table conversion. Click a step on the navigation assistant

to move forward or backward to another step in the process.

3.3.2 Prerequisites

Before you use the table conversion process, ensure that you:

■ Set up a data source and environment for any foreign tables from which you might

be importing data.

See Preparing Foreign Tables for Table Conversion.

■ Create a joined table business view if you are converting data from multiple tables.

See "Table Joins" in the JD Edwards EnterpriseOne Tools Business View Design Guide.

■ Create a business function to perform validation if you want the data items within

a table to validate against the data dictionary as part of the conversion process.

The Table Conversion tool does not provide automatic data dictionary validation

for inputs or outputs.

Data Changed When you use data sequencing, the system invokes a

Data Changed event for any sequenced field that

changes. Data Changed events are not cascaded or

hierarchical. For example, you can attach an event rule to

this event to total a field or group of values.

Format Fetched When you use user-defined formats (also known as flat

files) in the input table, the system invokes a Format

Fetched event for each record that is fetched from the

input table. If you use multiple user-defined formats in a

table conversion, the Format Fetched event is called for

the particular format that is found in the record.

Row Fetched An input table invokes a Row Fetched event after each

row is fetched from the input table.

Process End After the processing of all records is complete, the system

invokes the Process End event. Attach event rules to the

Process End event when you want the system to process

logic after all input records have been read; for example,

to write a total record to an output table or to write a

record to a log file to record the status of the table

conversion.

Event Description

Adding Table Conversions

Setting Up Table Conversions 3-5

See "Using Business Functions" in the JD Edwards EnterpriseOne Tools APIs and

Business Functions Guide.

3.3.3 Forms Used to Add New Table Conversions

3.3.4 Adding New Table Conversions

Access the Add Object form.

Object Name

Enter a unique name for the table conversion. Use the recommended naming

convention for JD Edwards EnterpriseOne objects.

Description

Enter a meaningful description of the table conversion.

Product Code

Select a user-defined code (UDC) (98/SY) from the range of values that is reserved for

clients, 55–59.

Product System Code

Select a UDC (98/SY) that represents the Oracle's JD Edwards EnterpriseOne system

where the data is used. This value is used for reporting and description override

purposes. Example values include:

01: Oracle's JD Edwards EnterpriseOne Address Book

03B: Oracle's JD Edwards EnterpriseOne Accounts Receivable

04: Oracle's JD Edwards EnterpriseOne Accounts Payable

09: Oracle's JD Edwards EnterpriseOne General Accounting

11: Oracle's JD Edwards EnterpriseOne Multicurrency

Form Name FormID Navigation Usage

Object Management

Workbench

W98220A EnterpriseOne Life

Cycle Tools,

Application

Development

(GH902), Object

Management, Object

Management

Workbench

Select and view

objects in projects, and

access the JD Edwards

EnterpriseOne design

tools.

Add EnterpriseOne

Object to the Project

W98220C Click the Objects node

of a project and click

Add. on the Object

Management

Workbench form.

Add a new object to a

project.

Add Object W9861AF Select Batch

Application and click

OK on the Add

EnterpriseOne Object

to the Project form.

Create a batch

application object.

Batch Application

Design

W9860AL Complete the object

information and click

OK on the Add Object

form.

Access the Table

Conversion director.

Converting Data

3-6 JD Edwards EnterpriseOne Tools Table Conversion Guide

Object Use

Select a UDC (98/FU) that indicates the use of the object. For example, the object

might be used to create a program, a master file, or a transaction journal. A 132 value

represents a data file conversion.

Object Type

Displays an abbreviation that identifies the type of object that is being created. The

object type for a table conversion is UBE. This field is populated by the system based

on the type of object that you selected on the Add Object form.

Table Conversion

Select to enable JD Edwards EnterpriseOne Table Conversion Design Aid. If you do

not select this option, Oracle's JD Edwards EnterpriseOne Report Design Aid is

launched by default.

3.3.5 Starting the Table Conversion Director

Click OK on the Add Object form:

1. Select the Design Tools tab, and click Start Table Conversion Design Aid.

The system displays the introduction form for the JD Edwards EnterpriseOne

Table Conversion director.

2. Proceed to the task for the type of conversion that you want to perform.

3.4 Converting Data

This section provides an overview of data conversion and discusses how to:

■ Define external data for table conversions.

■ Define input and output environments for table conversions.

■ Define table conversion input.

■ Define table conversion output.

■ Map inputs to outputs.

■ Specify table conversion logging options.

■ Review table conversion results.

3.4.1 Understanding Data Conversion

Use the Data Conversion feature on the JD Edwards EnterpriseOne Table Conversion

director to move data into tables from:

■ A single table.

■ Multiple tables that are defined in a business view.

■ A single text file.

See Also:

■ Converting Data.

■ Copying Data.

■ Copying Data with Table Input.

■ Deleting Groups of Records.

Converting Data

Setting Up Table Conversions 3-7

3.4.1.1 Data Conversion Example

This data conversion example illustrates how to copy employee records from the

F0101 table in the Login Environment to the F0101 table in the PY812 environment.

1. On the Introduction form of the JD Edwards EnterpriseOne Table Conversion

director, select Data Conversion, and click Next.

2. On the External Data form, accept the default selections, and click Next.

3. On the Select Environments form, select <LOGIN ENV> as the input and PY812

as the output environment, and click Next.

4. On the Select Input form, drag the Address Book Master table to the Description

column on the Table tab, and click Next.

5. On the Sequencing form, accept the default selection, and click Next.

6. On the Data Selection form, define the data selection as Where AT1 (Search Type)

(F0101) is equal to E to select current employees, and click Next.

7. On the Select Outputs form, drag the Address Book Master table to the

Description column on the Tables tab, and click Next.

8. On the Table Options form, clear the Run Currency Triggers option because the

Address Book Master file does not contain currency information, and click Next.

9. On the Mapping form, select the Row Fetched event because you are converting

data from similar files, click Map Same, and click Next.

The system automatically maps data elements with the same aliases.

10. On the Logging Options form, select Log All Errors and Log Outputs, and click

Next.

These options enable you to review the conversion after it is complete.

11. On the Finish form, select No, I will create a version of this table conversion later,

and click Finish.

12. On the Table Conversion form, click Save before quitting.

3.4.2 Forms Used to Convert Data

Form Name FormID Navigation Usage

Object Management

Workbench

W98220A EnterpriseOne Life

Cycle Tools,

Application

Development

(GH902), Object

Management, Object

Management

Workbench

Select and view

objects in projects, and

access the JD Edwards

EnterpriseOne design

tools.

Add EnterpriseOne

Object to the Project

W98220C Click the Objects node

of a project and click

Add. on the Object

Management

Workbench form.

Add a new object to a

project.

Add Object W9861AF Select Batch

Application and click

OK on the Add

EnterpriseOne Object

to the Project form.

Create a batch

application object.

Converting Data

3-8 JD Edwards EnterpriseOne Tools Table Conversion Guide

3.4.3 Defining External Data for Table Conversions

Access the JD Edwards EnterpriseOne Table Conversion Director.

1. Select Data Conversion, and click Next.

2. On the External Data form, click Select to attach a predefined processing option

template to the table conversion.

3. On the Select Processing Option Template form, select an appropriate processing

option template, and click OK.

4. On the External Data form, click Define to create a data structure.

5. On the Report Data Structure form, define the table conversion data structure, and

click OK.

Data structures contain a list of parameters that can be used to pass data into the

table conversion when called through a report interconnect.

6. Click Next.

3.4.4 Defining Input and Output Environments for Table Conversions

Click Next on the Introduction form of the JD Edwards EnterpriseOne Table

Conversion director to access the Select Environments form.

1. Select the input and output environments to use.

2. When creating a table conversion to be run in a different environment from the

one where you create it, and the <LOGIN ENV> is not appropriate for the type of

conversion that you are creating, select either Force Version to Override Input

Environment or Force Version to Override Output Environment, and click Next.

For example, if you create a conversion to be used by a client who does not have

the environments that you have, use these options. When the conversion is

invoked at the client site, the table conversion does not run until the user selects

the appropriate environments in which to run it.

3.4.5 Defining Table Conversion Input

Click Next on the Select Environments form to access the Select Input form.

Batch Application

Design

W9860AL Complete the object

information and click

OK on the Add Object

form.

Access the Table

Conversion director.

See Also:

■ "Understanding Data Structures" in the JD Edwards EnterpriseOne

Tools Data Structure Design Guide.

Note: Select <LOGIN ENV> if you are creating table conversions for

a user who might not have the same environments that you have. This

option ensures that the table conversion uses the environment that the

user is signed in to.

Form Name FormID Navigation Usage

Converting Data

Setting Up Table Conversions 3-9

Select the appropriate tab based on whether the input is a table, a business view, a

foreign table, or a text file.

2. Use the query by example (QBE) line to search for the appropriate table, business

view, or foreign table.

You can select only one table or one business view per table conversion. If the

input consists of multiple tables, you must create a joined table business view.

3. Enter the name of the text file on the Text File tab, or use the Browse button to

locate the file.

If you want to rename the selected file, enter the new file name, and click Rename.

For the IBM i, input text files are stored in the Integrated File System (IFS). Enter

the path to the IFS before the file name.

If you are using a text file, or if you need to define a format for a table or business

view, select the User Defined Format option.

4. If the input is a table or a business view, drag it to the Description column.

If you change a table or business view, the system warns you that deleting tables

removes all mappings from the table conversion.

5. To delete an input file, select it and press Delete.

6. Click Next.

7. On the Sequencing form, click Data Sequencing to define data sequencing for a

table or business view.

If you specify a text file for input, you cannot define data sequencing or data

selection for that file.

8. Click Next.

9. On the Data Selection form, click in the Operator column, and select an

appropriate operator.

On the Data Selection form, Where is the default value in the Operator column for

the first set of criteria. For subsequent statements, And and Or become the

available values for the Operator column.

10. Click the Left Operand column, and select from a list of available objects.

Note: Text files are the only type of file that you can rename.

Note: When you define data sequencing, you create new events that

are available to you in the Mapping section of the director. One new

event is created for each sequence column that you define. The event

is called XXXX Data Changed, where XXXX is the column alias, for

example, ALPH Data Changed. Each time the value in one of these

columns changes from its previous value, the Data Changed event is

invoked for that column. This event is similar to a level break in report

templates, except that the Data Changed events are not related to each

other. Invoking one does not invoke the others.

Converting Data

3-10 JD Edwards EnterpriseOne Tools Table Conversion Guide

11.

Click the Comparison column, and select one of these comparison operators:

– is equal to.

– is equal to or empty.

– is greater than.

– is greater than or equal to.

– is less than.

– is less than or equal to.

– is not equal to.

12. Click the Right Operand column, and select from a list of available objects, special

values, or variables.

The options in this column depend on the selection that you made in the

Comparison column. Some of these options might be available:

– Blank

Enters a blank (space) value.

– Literal

Enables you to enter specific values, as described in the next step.

– Null

Indicates that no value is associated with the field.

– Zero

Enters a value of 0.

– IC

Indicates input table columns.

– RI

Indicates values passed through a report interconnect to this table conversion.

– PO

Indicates processing option values for this table conversion.

– SL

Indicates system literals.

13. If you enter a literal in the Right Operand column, you can enter values on these

tabs of the Single value form:

– Single value

Enables you to enter a single value. An example might be a value that

indicates a specific company.

– Range of values

Note: You can define selection criteria for database table columns

only. User-defined format columns are not available because they do

not exist in the database.

Converting Data

Setting Up Table Conversions 3-11

Enables you to enter a range of values. An example might include companies

from 00001 to 00060. When you are using a range of values, only the is equal to,

is equal to or empty, and is not equal to operators are valid.

– List of values

Enables you to enter a list of values. Enter each value individually in the field,

and click Add after each entry.

An example list of values might include several user-defined codes for search

types, such as C for customers, E for employees, and V for vendors. When you

are using the list of values, only is equal to, is equal to or empty, and is not equal to

are valid operators.

To delete a value, select the value and click Delete.

14. To delete a line of criteria on the Data Selection form, select the row header and

click Delete.

15. To change the order of the criteria, select the row header, and click the Up or Down

button.

16. Click Next.

3.4.6 Defining Table Conversion Output

Click Next on the Select Input form to access the Select Outputs form.

1. Drag the table (or tables) that you want to use as outputs to the Description

column, and click Next.

2. For text file conversions, from the Text File tab, select the file to use as the output,

and click Use.

If you want to rename the selected file, enter the new file name, and click Rename.

For the IBM i, input text files are stored in the IFS. Enter the path to the IFS before

the file name.

If you are using a text file, or if you need to define a format for a table or business

view, select the User Defined Format option.

3. Click Next.

4. To delete an output, select the row and press Delete.

If you are using a text file, or if you need to define a user-defined format for a table

or business view, click User Defined Format.

5. Click Next.

6. On the Table Options form, select from these options:

– Run Currency Triggers

Select this option if a JD Edwards EnterpriseOne table that is included in the

table conversion contains currency triggers. If a table contains currency fields

and you do not select this option, the system cannot determine where the

decimal should be placed within a field. If you do not select the currency

trigger option, and the source or destination fields are currency fields that are

used in a calculation, you might receive unexpected results.

Note: Text files are the only type of file that you can rename.

Converting Data

3-12 JD Edwards EnterpriseOne Tools Table Conversion Guide

Do not select the Run Currency Triggers option if the input and output data

sources are the same type (for example, Oracle, IBM i, or SQL Server) and no

calculations are performed. Selecting this option results in slower

performance.

You should not use currency triggers for an environment that has a different

path code from the sign-in environment.

– Clear Output Tables

Select this option to clear the output table before the table conversion runs.

– Force Row by Row Processing

Select this option to test the table conversion or to ensure that the conversion

always runs in row-by-row mode.

You can test a conversion to ensure that the mapping logic is correct. In this

case, specify the number of rows to process. The number of rows can be

defined in the jde.ini under [TCEngine] or when you submit the table

conversion.

Select this option if you know that the values in the input table will produce

duplicate keys in the output, and you want only the non-duplicate keys to be

inserted.

Selecting this option results in slower processing.

– Buffer Inserts To Output Tables

Select this option to improve conversion performance if the conversion does

not include any event rules to process insertion errors and you are processing

row by row.

7. Click Next.

3.4.7 Mapping Inputs to Outputs

Click Next on the Select Outputs form to access the Mapping form.

1. Select the event on which mapping will occur.

In most cases, you use either the Row Fetched event or Format Fetched event. Use

the Format Fetched event if you are working with a user-defined format.

2. Click Advanced ER to include additional event rule logic in the table conversion.

The Event Rules Design form appears for the event that you selected on the

Mapping form.

3. Click Map Same to allow the system to map the inputs directly to the outputs.

If the input and outputs share some of the same data, these fields are a direct map.

For JD Edwards EnterpriseOne tables, the system maps fields by data dictionary

item. For foreign tables, the system maps by column name.

4. Drag inputs to outputs to manually map the inputs to the outputs.

5. If multiple output files exist, select each file from the outputs list, and map the

appropriate input columns to the appropriate output columns.

Note: Click Delete to erase the mapping for a selected output. Click

Delete All to erase the mapping for all outputs.

Converting Data

Setting Up Table Conversions 3-13

To define advanced output, double-click an output column.

The Advanced Outputs form enables you to define literals, calculations, and other

mappings without using Advanced ER. You can use an advanced input to add a

literal value into a field. You can also define a calculation to populate an output

field, such as adding two input fields together.

7. On the Advanced Outputs form, select one of these tabs, and add the appropriate

input:

– Available objects

Select the appropriate object, and click Apply.

– Literal

Enter the appropriate value, and click Apply.

– Defaults

Select Use Dictionary Defaults, and click Apply.

Select this option to use the default value in the data dictionary at runtime. If

no default values exist in the data dictionary, the system displays a warning

message.

– Calculation

Click Define Calculation, create a calculation in Expression Manager, and click

Apply.

8. Click Close on the Advanced Outputs form.

9. On the Mapping form, select Issue a Write for this Event? to insert a row to the

selected output after performing all column mappings for this event.

When you select Issue a Write for this Event?, the system attaches the TC Insert

Row event system function. This system function is automatically inserted at the

end of the event rules. You cannot move it to another area. To specify when and

where a row is inserted, attach the User Insert Row system function using

Advanced ER, and move it to wherever it is needed.

10. Click Next.

3.4.8 Specifying Table Conversion Logging Options

Click Next on the Mapping form to access the Logging Options form.

1. Select from these options:

– Log All Errors.

Select this option to log every error, regardless of the logging option for type

of operation that failed.

– Log Every Input Record.

Select this option to log every input record in the table conversion log file.

– Log Outputs.

Select this option to log every output record in the table conversion log file.

See Also:

■ Understanding the Table Conversion Process Flow.

■ Using Event Rules in Table Conversions.

Defining File Formats for Table Conversion Input or Output

3-14 JD Edwards EnterpriseOne Tools Table Conversion Guide

– Log Deletes.

Select this option to log every record that is deleted in the table conversion log

file.

– Log Updates.

Select this option to log every record that is updated in the table conversion

log file.

– Log Copy Table Actions.

Select this option to log the tables copied in the table conversion log file.

– Log Details of Copy Table Actions.

Select this option to log all of the details of each Copy Table action that is

performed.

– Run in Proof Mode.

Select this option to run the conversion in proof mode. In proof mode, the

conversion is simulated and all actions are logged, but no data is changed.

2. Click Next.

3.4.9 Reviewing Table Conversion Results

Click Next on the Logging Options form to access the Finish form.

1. Select one of these options:

– Yes, create a version of this table conversion.

If you select yes, enter the version name.

– No, I will create a version of this table conversion later.

2. Click Finish to complete the process.

3. On the Table Conversion form, select the Table Conversion Mappings form to

review the mapping.

4. Make changes, as necessary, by selecting the appropriate option from the View

menu, and then click Save.

5. To review the runtime conversion mode, select Check Conversion Mode from the

Conversion menu.

A dialog box provides you with details of the conversion mode that is defined.

6. From the Conversion menu, select Exit.

3.5 Defining File Formats for Table Conversion Input or Output

This section provides an overview of user-defined formats, lists a prerequisite, and

discusses how to:

Note: Proof mode is not an absolute proof mode. In some situations,

the proof output might differ from the real output. If you insert the

same record twice, for example, it may seem as though it worked in

proof mode but, in reality, only one of the inserts works when you run

the conversion in final mode.

Defining File Formats for Table Conversion Input or Output

Setting Up Table Conversions 3-15

■ Define delimited file formats with a single format.

■ Define delimited file formats with multiple formats.

■ Define fixed-width file formats with a single format.

■ Define fixed-width file formats with multiple formats.

3.5.1 Understanding User-Defined Formats

JD Edwards EnterpriseOne Table Conversion Design Aid uses user-defined formats to

handle fixed-width or comma separated value (CSV) files in a table or text file. These

files are collectively known as flat files because they do not have relationships defined

like relational database tables. Usually, flat files are text files that are stored on a

workstation or server. They are used to import or export data from applications that

have no other means of interaction. For example, you might need to share information

between JD Edwards EnterpriseOne and an external application. If the external

application does not support one of the same databases that JD Edwards

EnterpriseOne supports, flat files might be the only way to transfer data between the

two applications.

In a flat file, records are stored as a continuous string of information. The user-defined

format provides instructions on how data is presented.

This table illustrates a single database record with five columns of data: Last, First,

Addr, City, and Phone:

This record can be formatted as a comma-delimited string:

Doe, John, 123 Main, Anytown, 5551234

This record can also be formatted as a fixed-width column format, in which each

column is eight characters long.

3.5.1.1 Importing and Exporting Text Files

When you select a text file for input or output in a table conversion and do not specify

a path, a default path is used. Table conversions that use the default path run on any

platform. If an explicit path or IBM i library name is indicated for the file, then the file

is created exactly as specified. Table conversions that are stored in this way might not

work on other platforms, depending on the nature of the file system on each platform.

The default paths on non-IBM i platforms are:

■ path code\Import\file name

■ path code\Export\file name

You cannot specify a default path for the IBM i. Rather, the default is always the

Import or the Export directory under the path code of the input or output

environment. For example, if you are running a table conversion against the DV812

environment, the path in the file system might be \E812\DV812\import\myfile.txt.

If the conversion specifies a file name that includes anything other than the file name

and extension, such as \mytextfiles\myfile.txt, the conversion attempts to open the

file as specified.

Last First Addr City Phone

Doe John 123 Main Anytown 5551234

Defining File Formats for Table Conversion Input or Output

3-16 JD Edwards EnterpriseOne Tools Table Conversion Guide

3.5.1.2 Using User-Defined Formats as Input

If you use user-defined input formats, add event rules on the Format Fetched event.

Without these event rules, the system ignores the format, and the data from the input

table is never made available to the table conversion. If the table conversion does not

require event rule logic, add comments in the Format Fetched event in Event Rules

Design.

User-defined formats work with text files and tables.

Because the procedure for importing and exporting data is database-specific, consult a

database administrator for details.

3.5.1.3 Using User-Defined Formats as Output

If you use user-defined output formats, add event rules on the Format Fetched event.

Without these event rules, the system ignores the format, and the data from the output

table is never made available to the table conversion. If the table conversion does not

require event rule logic, add comments in the Format Fetched event in Event Rules

Design.

User-defined formats work with text files and tables.

Because the procedure for importing and exporting data is database-specific, you

should consult a database administrator for details.

3.5.2 Prerequisite

Before you begin defining delimited file formats, ensure that you have selected the

input and output files, as appropriate.

3.5.3 Defining Delimited File Formats with a Single Format

From the JD Edwards EnterpriseOne Table Conversion director, access the Select Input

form or the Select Outputs form.

1. Select User Defined Format, and then click Define Format(s).

2. On the User Defined Format - Type form, select the Delimited - Characters such as

commas or tabs separate each column option.

3. Select the Single Format - All rows have the same format option, and click Next.

4. On the User Defined Format - Column Delimiter form, select the delimiter that

separates the columns in the file:

– Tab

– Comma

– Semicolon

– Space

– Other

5. Select the textual qualifier that is used to enclose a string of text:

– None

Select this option if no character is used to qualify text in this user-defined file.

– Single Quotations

Defining File Formats for Table Conversion Input or Output

Setting Up Table Conversions 3-17

Select this option if a single quotation mark is used to qualify text in this

user-defined file.

– Double Quotations

Select this option if a double quotation mark is used to qualify text in this

user-defined file.

6. If the user-defined format incudes column headings in the first row, select The

first row contains column headers, and click Next.

7. On the User Defined Format - Column Layout form, select a format from the list of

available formats.

You define the columns for the format so that the system can parse the information

from the file.

8. For each column, click Add to define the column.

9. On the New Column Properties form, modify these fields as required, and click

OK.

– Name

– Length

– Type

10. To edit an existing column, select it, click Edit, and modify the properties on the

Column Properties form.

11. To model the columns after an existing table, business view, or foreign table, click

Model, select the appropriate tab, and then select the table or business view that

you want to use as a model for the user-defined format.

12. Click OK, and then click Next.

The system copies the format from the model that you selected and places it into

the column layout grid.

13. On the User Defined Format - Finish form, click Finish.

3.5.4 Defining Delimited File Formats with Multiple Formats

From the JD Edwards EnterpriseOne Table Conversion director, access the Select Input

form or the Select Outputs form.

1. Select User Defined Format, and then click Define Format(s).

2. On the User Defined Format - Type form, select the Delimited - Characters such as

commas or tabs separate each column option.

3. Select the Multiple Formats - Rows are in two (2) or more formats option, and click

Next.

4. On the User Defined Format - Column Delimiter form, select the delimiter that

separates the columns in the file:

– Tab

– Comma

Note: You cannot model the columns after an existing object unless

the layout of the two objects match.

Defining File Formats for Table Conversion Input or Output

3-18 JD Edwards EnterpriseOne Tools Table Conversion Guide

– Semicolon

– Space

– Other

5. Select the textual qualifier that is used to enclose a string of text, and click Next:

– None

Select this option if no character is used to qualify text in this user-defined file.

– Single Quotations

Select this option if a single quotation mark is used to qualify text in this

user-defined file.

– Double Quotations

Select this option if a double quotation mark is used to qualify text in this

user-defined file.

6. On the User Defined Format - Multiple Format Definition form, enter the number

of formats that are included in the user-defined format.

7. To define the character length of the Form Designator Column, enter a value in the

Length field, and click Next.

8. On the User Defined Format - Multiple Format Names form, name each format

and define the values for each using the Name and Designator columns, and then

click Next.

The Designator name should describe the data in the user-defined formats.

For example, suppose that a text file contains purchase order information. Lines in

the table have information for a whole purchase order with the first field

designated as POH; lines with a first field designated as POI contain information

about individual items in the purchase order; and lines in the table with a first

field designated as POT contain information about purchase order totals. In this

scenario, you would enter POH as the designator of the first format, POI as the

designator of the second format, and POT as the designator of the third format.

9. On the User Defined Format - Column Layout form, select a format from the list of

available formats.

You define the columns for the format so that the system can parse the information

from the file.

10. To add additional columns, click Add to define the column.

11. On the New Column Properties form, modify these fields as required, and click

OK:

Note: You can rename the columns for each format to help you

remember the formats with which you are working. For example, you

can rename the columns according to their function in the file, such as

Header, Detail, and Total. These names appear in the Inputs list on the

Mapping form of the table conversion.

Note: To move rows, including the Format Designator, select the row

and drag it to a new location.

Defining File Formats for Table Conversion Input or Output

Setting Up Table Conversions 3-19

– Name

– Length

– Type

12. To edit an existing column, select it, click Edit, and change the properties on the

Column Properties form.

13. To model the columns after an existing table, business view, or foreign table, click

Model, select the appropriate tab, and then select the table or business view that

you want to use as a model for the user-defined format.

14. Click OK, and then click Next.

The system copies the format from the model that you selected and places it into

the column layout grid.

15. On the User Defined Format - Finish form, click Finish.

3.5.5 Defining Fixed-Width File Formats with a Single Format

Access the Select Input form or the Select Outputs form.

1. Select User Defined Format. and then click Define Format(s).

2. On the User Defined Format - Type form, select the Fixed Width - Columns are

aligned at specific positions with specific widths option.

3. Select the Single Format - All rows have the same format option, and click Next.

4. On the User Defined Format - Column Layout form, click Add to define the

column layout.

5. On the New Column Properties form, modify these fields as required, and click

OK:

– Name

– Start

– Length

– Type

6. To edit an existing column, select the column, click Edit, and change the properties

on the Column Properties form.

7. To model the columns after an existing table, business view, or foreign table, click

Model, select the appropriate tab, and then select the table or business view that

you want to use as a model for the user-defined format.

8. Click OK, and then click Next.

The system copies the format from the model that you selected, and places it into

the column layout grid.

Note: You cannot model the columns after an existing object unless

the layout of the two objects match.

Note: You cannot model the columns after an existing object unless

the layout of the two objects match.

Defining File Formats for Table Conversion Input or Output

3-20 JD Edwards EnterpriseOne Tools Table Conversion Guide

On the User Defined Format - Finish form, click Finish.

3.5.6 Defining Fixed-Width File Formats with Multiple Formats

Access the Select Input form or the Select Outputs form.

1. Select User Defined Format, and then click Define Format(s).

2. On the User Defined Format - Type form, select the Fixed Width - Columns are

aligned at specific positions with specific widths option.

3. Select the Multiple Formats - Rows are in two (2) or more formats option, and click

Next.

4. On the User Defined Format - Multiple Format Definition form, enter the number

of formats that are included in the user-defined format, and click Next.

To define the character length of the Designator column, complete the Length

field.

5. On the User Defined Format - Multiple Format Names form, enter a name for each

format in the Name column, and click Next.