Designing Tables

This chapter provides overviews of Oracle's JD Edwards EnterpriseOne Table Design Aid and table creation and discusses how to:

• Create tables.

• Work with JD Edwards EnterpriseOne Table Design Aid.

• Work with tables.

• View the data in tables.

• Design custom tables.

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:

• Create new tables.

• Add or delete data items.

• Add or modify an index.

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.

Creating Tables

This section discusses how to:

• Create audit trail information.

• Name tables.

• Use index guidelines.

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)

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

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.

Working with JD Edwards EnterpriseOne Table Design Aid

This section discusses how to:

• Use Table Design Aid forms.

• Select data items.

• Define indices.

• Review table information.

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.

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.

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.

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:

Working with Tables

This section discusses how to:

• Generate tables

• Generate indices

• Generate header files

• Copy tables

• Remove tables

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

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.

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

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.

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.

Viewing the Data in Tables

You can view data in tables using:

• Oracle's JD Edwards EnterpriseOne Data Browser on the web client.

• Oracle's JD Edwards EnterpriseOne Universal Table Browser (UTB) on the Microsoft Windows client.

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.

See Also

Managing Data Browser Security

Using the Universal Table Browser

Viewing the Data in Tables and Business Views

Designing Custom Tables

This section discusses how to:

• Add table objects.

• Modify table objects.

• Select data items for tables.

• Define indices.

• Preview tables.

• Generate tables.

• Generate indices.

• Generate header files.

• Copy tables.

• Remove tables.

• View the data in tables.

Forms Used to Create Tables

Adding Table Objects

Access the 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.

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.

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.

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.

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.

Generating Tables

Access the 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.

Generating Indices

Access the Generate Indexes form.

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.

Copying Tables

Access the 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.

Removing Tables

Access the Remove Table form.

Viewing the Data in Tables

Access the Universal Table Browser form.