2 Designing Tables
This chapter contains the following topics:
-
Section 2.1, "Understanding JD Edwards EnterpriseOne Table Design Aid"
-
Section 2.4, "Working with JD Edwards EnterpriseOne Table Design Aid"
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:
-
Create new tables.
-
Add or delete data items.
-
Add or modify an index.
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:
-
Create a table object.
Use the recommended naming conventions when creating table objects.
-
Design the table.
Add data dictionary items as table columns and create indices. Include audit fields in the table.
-
Generate the table.
-
Add data to the table.
2.3 Creating Tables
This section discusses how to:
-
Create audit trail information.
-
Name tables.
-
Use index guidelines.
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:
-
Use Table Design Aid forms.
-
Select data items.
-
Define indices.
-
Review table information.
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:
-
Generate tables
-
Generate indices
-
Generate header files
-
Copy tables
-
Remove tables
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:
-
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.
2.7 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.
2.7.1 Forms Used to Create Tables
2.7.2 Adding Table Objects
- 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.
-
Select the Summary tab and revise these fields as appropriate:
-
Description
-
Product Code
-
Product System Code
-
Object Use
-
-
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.
-
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.
-
Drag each required data dictionary item from the Data Dictionary Browser to the Columns form.
-
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.
-
Click the indices form so that it is active, and the indices menu is visible.
-
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.
-
Enter a name for the index, and press Enter.
-
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.
-
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.
-
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.
-
On the Columns form, click Zoom In to enlarge the preview.
-
Click Print to send the information to the printer.
2.7.7 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.
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.
-
Select the Design Tools tab, and click Generate Header File.
-
The system generates a .h, or header, file.
2.7.10 Copying Tables
- 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
- 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.