Skip Headers
JD Edwards EnterpriseOne Tools Development Tools: Data Access Tools Guide
Release 8.98 Update 4

Part Number E14702-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

2 Designing Tables

This chapter contains the following topics:

2.1 Understanding JD Edwards EnterpriseOne Table Design Aid

The Oracle's JD Edwards EnterpriseOne system uses a relational database. Tables are related using common key fields.

Database tables store the data that is used by applications in columns and rows. Each column is a data item, and each row is a record. You can create custom tables for use in JD Edwards EnterpriseOne applications.

You create tables by selecting data items from the data dictionary and assigning key fields as indices. An index enables a database management system to sort and locate records quickly. You must define a table so that the JD Edwards EnterpriseOne software recognizes that the table exists.

Use JD Edwards EnterpriseOne Table Design Aid to generate the table whenever you:

2.2 Understanding Table Creation

When an existing or custom application requires that you create tables for the JD Edwards EnterpriseOne database, use JD Edwards EnterpriseOne Table Design Aid to create the custom tables. You can access JD Edwards EnterpriseOne Table Design Aid from the Oracle's JD Edwards EnterpriseOne Object Management Workbench.

To create tables:

  1. Create a table object.

    Use the recommended naming conventions when creating table objects.

  2. Design the table.

    Add data dictionary items as table columns and create indices. Include audit fields in the table.

  3. Generate the table.

  4. Add data to the table.

2.3 Creating Tables

This section discusses how to:

2.3.1 Creating Audit Trail Information

Before you add a new table to the JD Edwards EnterpriseOne system, determine whether an existing table contains the data items required. If an appropriate table does not exist, you must create a new table.

When you add a new table, you should include these audit trail columns:

  • User ID (USER)

  • Program ID (PID)

  • Machine Key (MKEY)

  • Date - Updated (UPMJ)

  • Time - Last Updated (UPMT)

2.3.2 Naming Tables

Use these naming conventions when adding tables:

The name of a table can be a maximum of eight characters and should be formatted as Fxxxxyyy, where:

F = Data table.

xx (second and third digits) = the system code, such as:

  • 00 - Foundation

  • 01 - Address Book

  • 03 - Accounts Receivable

xx (fourth and fifth digits) = the group type, such as:

  • 01 - Master

  • 02 - Balance

  • 1X - Transaction

yyy (sixth through eighth digits) = object version, such as programs that perform similar functions but vary distinctly in specific processing.

LA through LZ = Logical file.

JA through JZ = Table join.

Columns must include a two-character prefix that is used to uniquely identify the table columns. The first character must be alphabetic, while the second character can be alphanumeric. You cannot assign special characters to table columns: for example, $, #, or @. The data item alias follows the two-character column prefix. Typically, the column prefix indicates the type of data included in the table; for example, the prefix of the columns in the Address Book Master (F0101) table begin with AB.

A table description can be no more than 60 characters. Ensure that the table description is the topic of the table. If the table description comes from the iSeries, it should be the same name as the file that it represents, such as F0101 (Address Book Master) and F4101 (Item Master).

2.3.3 Using Index Guidelines

If an index includes only one field, use the field name as the index name: for example, Address Number.

If an index includes two fields, list them consecutively: for example, Address Number, Line Number ID.

If an index includes more than two fields, and the first two fields are the same as the first two fields of another index, list the first two fields and follow them by an alpha character: for example, Address Number, Line Number, A. Place a comma and space between each index field and between the last index field and the alpha character. Do not include more than 10 fields in an index.

The total length of the index name cannot exceed 19 characters. If you exceed 19 characters, the compiler displays a warning. This situation affects fetches that use the wrong index ID in business functions.

2.4 Working with JD Edwards EnterpriseOne Table Design Aid

This section discusses how to:

2.4.1 Using Table Design Aid Forms

JD Edwards EnterpriseOne Table Design Aid presents these forms within a single window:

  • Columns

    Displays the data items that you select to include in the table.

  • Data Dictionary Browser

    Provides a search method to locate the data items required for the table.

  • Indices

    Provides a method to define unique data items for quick sorting and updating of the table.

  • Object Properties

    Displays data item attributes for a data item that is selected in the Columns form.

2.4.2 Selecting Data Items

Use data items to create table columns. Table columns store information used by applications. Data items must exist in the data dictionary before you can use them in a table. In JD Edwards EnterpriseOne Table Design Aid, you can locate data items using the query by example (QBE) line of the Data Dictionary Browser. To select data items, you can double-click them or drag them to the Columns form. All selected data items appear on the Columns form. Tables can contain data items from multiple system codes.

When you modify or delete data items, you must regenerate the table. Changes that you make to a table can affect business views and forms that reference that table.

Use the Generate Table feature to generate a newly modified table. Generating an existing table clears all data from the table.

Important:

If you delete a table or delete columns from a table, then business views that reference that table, or the deleted table columns, are invalid. The system displays error messages when you generate the application.

2.4.3 Defining Indices

Indices are used to locate specific records and to sort records faster. Table indices are like tabs in a card file. Each index is made up of one or more keys, which are individual data items. Use indices to access data in a simple manner, rather than to read the data sequentially.

Tables can have multiple indices, but every table must have only one primary index. The primary index is the one unique identifier for each record in the table. Additionally, you can use the primary index to build business views. The system does not allow you to save a table without defining a primary index.

When you modify or delete indices, you must regenerate the table. Changes that you make to a table can affect business views and forms that reference that table.

2.4.4 Reviewing Table Information

You can use JD Edwards EnterpriseOne Table Design Aid to review information regarding a table. You can review the table information online or send it to a printer. This table describes the table information:

Table Information Description
Description The description of each data item included in the table.
Type The field type of each data item included in the table.
Len The field length of each data item included in the table.
Alias The alias of each data item included in the table.
Data Item The name of each data item included in the table.
Table Column Prefix The prefix of the table columns.
Indices Information regarding all of the indices included in the table.

2.5 Working with Tables

This section discusses how to:

2.5.1 Generating Tables

You must generate tables to create the physical table in the database. Tables are created in the database based on the specifications that you defined. After the table is generated, you can add data to the table. Table generation also creates an .h file, or header file, that is used in business functions and table event rules.

JD Edwards EnterpriseOne Object Management Workbench provides a central location from which you can manage tables.

After you have selected data items for a table and assigned indices, you can configure the table for a specific data source. JD Edwards EnterpriseOne Object Management Workbench employs the Oracle's JD Edwards EnterpriseOne Object Configuration Manager (P986110) application to configure tables. You can configure the table within any existing data source. If you do not indicate a data source, the software automatically configures the table according to the default data source mapping. You can change the path code to generate tables in a different location. Doing so causes the system to perform a drop statement, similar to the remove table, after which the system recreates the table.

After you modify a table, you must regenerate it. If you regenerate an existing table, the table data is lost. To ensure that data is not lost, you must export the data, generate the table, and then copy the data back into the generated table.

2.5.2 Generating Indices

You must regenerate indices each time that you modify existing indices or create additional indices. Doing so modifies the .h file. When you regenerate indices, you do not lose existing data as you do when you regenerate the entire table.

2.5.3 Generating Header Files

Header files, or .h files, are used in business functions and table event rules. Occasionally, you might run across a table that does not include a header file. You can generate header files without having to generate the entire table.

Header files are located in the Include folder in the path code in which the file was generated (for example, E812\DV812\Include).

2.5.4 Copying Tables

Use the Copy Table feature to copy tables from one data source to another. However, doing so does not copy the table specifications. You can also use Table Conversion to copy tables from one data source to another.

2.5.5 Removing Tables

You cannot physically delete a table using JD Edwards EnterpriseOne Table Design Aid. If you delete a table from JD Edwards EnterpriseOne Table Design Aid, the system deletes only the specifications; it does not delete the physical table.

To completely remove a table from the system, you must use the Remove Table From Database feature.

2.6 Viewing the Data in Tables

You can view data in tables using:

Both tools enable you to verify the existence of data in tables, as well as determine the structure of the table. You can use both tools to view data in all JD Edwards EnterpriseOne supported databases, independent of the type of database that you use.

UTB is an executable application that is part of the JD Edwards EnterpriseOne Microsoft Windows client install. You cannot use JD Edwards EnterpriseOne security to control user permissions for UTB. However, you can apply form security to the Table and Data Source Selection form (W98TAMC). This action secures UTB because the executable cannot function without this form. All column and row security that you set up through the Oracle's JD Edwards EnterpriseOne application applies to UTB.

JD Edwards EnterpriseOne Data Browser is part of the Oracle's JD Edwards EnterpriseOne web client product. You can set up security for the JD Edwards EnterpriseOne Data Browser using JD Edwards EnterpriseOne Security Workbench.

2.7 Designing Custom Tables

This section discusses how to:

2.7.1 Forms Used to Create Tables

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 new objects to a project.
Add Object W9861AF Select Table and click OK on the Add EnterpriseOne Object to the Project form. Enter the table name, description, product code, product system code, object use, and column prefix.
Table Design Aid W9860AL Complete the object information and click OK on the Add Object form. Access Table Design Aid, generate header files, generate tables, generate indexes, remove tables from the database, and copy tables.
Generate Table W9866E Select the Table Operations tab, and click Generate Table on the Table Design form. Generate tables.
Generate Indexes W9866J Select the Table Operations tab, and click Generate Indexes on the Table Design form. Generate indices.
Copy Table W9866M Select the Table Operations tab, and click Copy Table on the Table Design form. Copy tables.
Remove Table W9866D Select the Table Operations tab, and click Remove Table from Database on the Table Design form. Remove tables.
Universal Table Browser NA EnterpriseOne Life Cycle Tools, Application Development Tools (GH902), Object Management View the data in tables.

2.7.2 Adding Table Objects

Access the Add Object form.

Figure 2-1 Add Object form

Description of Figure 2-1 follows
Description of "Figure 2-1 Add Object form"

Object Name

Enter a unique name for the new table. Use the recommended naming convention for naming JD Edwards EnterpriseOne objects.

Description

Enter a meaningful description of the table.

Product Code

Select a user-defined code (UDC) (98/SY). Use one of the values from the range of values reserved for clients: 55–59.

Product System Code

Select a UDC (98/SY) that represents the JD Edwards EnterpriseOne system where the data is used. This value is used for reporting and jargon 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

Object Use

Select a UDC that indicates the use of the object. For example, the object may be used to create a program, a master file, or a transaction journal. A 2, or any other value from the 200 series, represents a table.

Object Type

The system displays an abbreviation that identifies the type of object being created. The object type for a table is TBLE. This field is populated by the system based on the type of object that you selected on the Add Object form.

Column Prefix

Enter a two-character prefix to be used for the table column names.

2.7.3 Modifying Table Objects

Either click OK on the Add Object form or select a table in a project and click Design to access the Table Design form.

  1. Select the Summary tab and revise these fields as appropriate:

    • Description

    • Product Code

    • Product System Code

    • Object Use

  2. Select the Attachments tab and enter information in the text area to document the table.

2.7.4 Selecting Data Items for Tables

Select a table in a project and access JD Edwards EnterpriseOne Table Design Aid.

  1. On the Data Dictionary Browser form, use the query by example (QBE) line to locate the required data dictionary items to include in the table.

  2. Drag each required data dictionary item from the Data Dictionary Browser to the Columns form.

  3. To remove a column from a table, select the column and select Delete from the Edit menu.

2.7.5 Defining Indices

Select a table in a project and access JD Edwards EnterpriseOne Table Design Aid.

  1. Click the indices form so that it is active, and the indices menu is visible.

  2. From the indices menu, select Add New.

    You can also drag indices from the Columns form onto the Indices form.

    The index description is Untitled, and the index is marked with a key icon that displays the letter P to indicate a primary index.

  3. Enter a name for the index, and press Enter.

  4. On the Columns form, drag appropriate columns to the index.

    A unique index is marked with a single key. You can right-click the index and select Unique from the Index menu to toggle the unique status. The Unique Primary Index cannot be changed to a non-unique status.

  5. Right-click the data item and select or clear the Ascending option to indicate the sort order of ascending or descending for an index column.

    An upward-pointing arrow indicates that the index column is sorted in ascending order.

2.7.6 Previewing Tables

Select a table in a project and access JD Edwards EnterpriseOne Table Design Aid.

  1. Click the Columns form so that it is active, and select Print Preview from the File menu.

    A preview of the table appears on the Columns form in place of the column names.

  2. On the Columns form, click Zoom In to enlarge the preview.

  3. Click Print to send the information to the printer.

2.7.7 Generating Tables

Access the Generate Table form.

Figure 2-2 Generate Table form

Description of Figure 2-2 follows
Description of "Figure 2-2 Generate Table form"

Table Name

Displays the unique name of the table. This field is populated by the system based on the name that you gave the table when you created it.

Data Source

Enter the name of the data source where the database resides.

Object Owner ID

Enter the owner ID of the database that resides in the defined data source.

Password

Enter the database password that corresponds to the owner ID.

2.7.8 Generating Indices

Access the Generate Indexes form.

Table Name

Displays the unique name of the table. This field is populated by the system based on the name that you gave the table when you created it.

Data Source

Enter the name of the data source where the database resides.

Object Owner ID

Enter the owner ID of the database that resides in the defined data source.

Password

Enter the database password that corresponds to the owner ID.

2.7.9 Generating Header Files

Access the JD Edwards Table Design Aid form.

  1. Select the Design Tools tab, and click Generate Header File.

  2. The system generates a .h, or header, file.

2.7.10 Copying Tables

Access the Copy Table form.

Figure 2-3 Copy Table form

Description of Figure 2-3 follows
Description of "Figure 2-3 Copy Table form"

Table Name

Displays the unique name of the table. This field is populated by the system based on the name that you gave the table when you created it.

Source Data Source

Enter the name of the source data source, that is the data source to copy from.

Destination Data Source

Enter the name of the target data source, that is the data source to copy to.

Object Owner ID

Enter the owner ID of the database that resides in the defined data source.

Password

Enter the database password that corresponds to the owner ID.

2.7.11 Removing Tables

Access the Remove Table form.

Table Name

Displays the unique name of the table. This field is populated by the system based on the name that you gave the table when you created it.

Data Source

Enter the name of the data source where the database resides.

Object Owner ID

Enter the owner ID of the database that resides in the defined data source.

Password

Enter the database password that corresponds to the owner ID.

2.7.12 Viewing the Data in Tables

Access the Universal Table Browser form.

Table

Enter the name that identifies a table in JD Edwards EnterpriseOne. For example, F0101 is the name of the Address Book master table. You can use the visual assist to search for a table

Data Source

Enter the name of the data source where the table resides. The default value is obtained from the JD Edwards EnterpriseOne Object Configuration Manager (OCM) settings for the environment in which the user is signed on. Use the visual assist to select from a list of valid JD Edwards EnterpriseOne data sources.

Format Data

Select this option to indicate whether UTB should format the data once it is fetched from the database. JD Edwards EnterpriseOne Data Browser on the web does not provide this option. It always formats data after it is fetched from the database. The options are:

Formatted: UTB formats data according to the specifications of the JD Edwards EnterpriseOne data dictionary item. For example, assume that the data item PROC is a numeric field with a size of 15 and includes four display decimals. For a PROC value of 56.2185, UTB displays a formatted value (using the data dictionary editing) as 56.2185, even though the value is stored in the database as 562185.

Unformatted: UTB displays the data according to the specification of the database and the data item type (such as numeric). For example, assume that the table data item PROC is a numeric field stored in the database. Depending on the type of database, the default for this field might be 32 with a precision of 15. Because JD Edwards EnterpriseOne does not store the decimals in the database, a PROC value of 56.2185 would be stored by the database as 562185.000000000000000 and would appear the same in UTB.