8 Understanding Table Conversion

This chapter contains the following topics:

8.1 Table Conversions

Table conversion is a type of batch process that enables you to rapidly manipulate the data in tables. This table describes the conversion types that the Oracle's JD Edwards EnterpriseOne Table Conversion tool uses to manipulate data:

Conversion Type Description
Data Conversion Enables you to transfer or copy data from an input table or business view into output tables using the logic that is necessary to perform the transfer. You can also use Data Conversion to update records in a table or business view.
Data Copy Enables you to copy tables from one data source or environment to another data source or environment when the tables are identical.
Data Copy with Table Input Enables you to copy tables based on information from an input table. For example, the input table might provide information about which tables are copied, where they are copied, and so on.
Batch Delete Enables you to delete records from a table or business view.

The JD Edwards EnterpriseOne Table Conversion tool uses Oracle's JD Edwards EnterpriseOne tables, business views, and text files. The tool can also use non-JD Edwards EnterpriseOne tables, also referred to as foreign tables, as long as they reside in a database that is supported by JD Edwards EnterpriseOne software, such as:

  • Oracle

  • Access

  • iSeries

  • SQL Server

When creating a table conversion, you:

  • Set up a table conversion template.

  • Review the template.

  • Create versions of the template.

    Like batch applications, table conversions include a template and versions. You can override some of the properties within a version at runtime.

  • Process the table conversion.

    Table conversions can be saved and run multiple times. You can test the table conversion by running it in proof mode.

Oracle's JD Edwards EnterpriseOne Table Conversion Design Aid enables you to access any available environment for both input and output. The environments that you select determine which tables and business views are available for the conversion and where the tables reside. The environments that you select also determine the specifications, or descriptions, of tables and business views.

8.2 Types of Tables to Use

You can use these table types in table conversions:

Table Type Description
JD Edwards EnterpriseOne tables Tables that exist in the JD Edwards EnterpriseOne database and can be accessed from Oracle's JD Edwards EnterpriseOne Object Management Workbench. You can design and modify them using Oracle's JD Edwards EnterpriseOne Table Design Aid. At design time, only the specifications are needed to reference the table. At runtime, the table must be generated. An instance of the table must occur in a particular database.
Foreign tables Tables that do not have a JD Edwards EnterpriseOne definition but reside in a database that is supported by JD Edwards EnterpriseOne. You must set up a data source and environment in JD Edwards EnterpriseOne to point to the location of foreign tables.

8.3 Business Views in Table Conversions

You must create a business view to establish a relationship among the input tables when you need to transfer data from:

  • Multiple tables to a single table.

  • Multiple tables to multiple tables.

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.

8.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.

8.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.

8.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.

  • 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.