JD Edwards EnterpriseOne Tools Business View Design Guide

JD Edwards EnterpriseOne Tools

Business View Design Guide

Release 9.1.x

E21483-02

December 2014

1 Understanding Business Views

A business view is a selection of data items from one or more tables. After you create a

table, use Oracle's JD Edwards EnterpriseOne Business View Design to create a

business view that contains only the data items that are required for the application.

JD Edwards EnterpriseOne uses the business view that you create to generate the

appropriate SQL statements that are necessary to retrieve data from the database.

After you define a business view, you can attach it to a:

■ Form that updates data in an interactive application.

■ Report that displays data.

Business views increase performance by moving less data across the network because

you include only those data items that an application requires.

Business views are required for creating applications and generating reports. Business

views:

■ Link a JD Edwards EnterpriseOne application to one or more tables.

■ Contain all or a subset of data items from one or more tables.

■ Can use table joins to join multiple tables on common fields.

■ Are building blocks for text search indexes that enable full-text searching of data.

To make a business view available for full-text searching, select the Text Search

option on the properties form. Do not select this option if you do not plan to use

the business view for full-text searching; doing so can negatively affect

performance.

1.1 Table Joins

Use the table join feature to join multiple tables in a business view. Joining tables

enables you to combine fields from different tables for each record of the primary

table. Perform the join using fields that are common to the tables. Define the joined

fields to satisfy a join condition, such as when the records, or rows, have the same

value in the key fields. The primary table is the table where you initiate the join

(usually the table on the left in JD Edwards EnterpriseOne Table Design Aid) and the

secondary table is the table where you conclude the join (usually the table on the right

in JD Edwards EnterpriseOne Table Design Aid). Several types of joins exist, including

those described in the following table:

Join Type Description

Simple join, also known as inner join Includes only rows that match both the primary and

sec

ondary tables.

Right outer join Includes rows that are common to both the primary

and secondary tables, and unmatched rows from the

secondary table.

Left outer join Includes rows that are common to both the primary

and

secondary tables, and unmatched rows from the

primary table.

SQL 92 left outer join Includes rows that are common to both the primary

and

secondary tables, unmatched rows from the

primary table, and any rows with null values from the

secondary table—regardless of any Where clause

against the fields from the secondary table.

This diagram illustrates a simple table join:

Figure 1 Simple join

This diagram illustrates a right outer join:

Figure 2 Right outer join

This diagram illustrates a left outer join:

Figure 3 Left outer join

1.2 Table Unions

A table union joins entire tables. There are two options for table unions:

■ Union

■ Union All

With the Union option, the system first checks

for rows from the primary table, and

then for rows with corresponding columns from the secondary table. If the rows from

the two tables contain identical data, then only one of the records is retrieved in the

union. Unions include rows from the primary table and corresponding columns from

the secondary table.

With Union All, the system first checks for r

ows from the primary table, and then for

rows with corresponding columns from the secondary table. Duplicate records are

retrieved if the rows from the two tables contain identical data. Because Union All

does not scan for distinct records, the retrieval time is faster than when using the

Union option.

This diagram illustrates a table union, whether by Union or Union All:

Figure 4 Table unions

1.3 Select Distinct

If a business view includes the primary key fields of the primary table, every row of

the business view query is unique. The primary key field has a different value in each

row, or record, of the primary table. If the business view does not contain all primary

key fields of the primary table, then duplicate rows can occur during the business

view query. You can eliminate the duplicate rows in the output by using the Select

Distinct feature when designing the business view.

1.4 Distinct Union All

With Distinct Union All, Union All retrieves the distinct rows from the primary table

and the secondary table.

For example, this table shows the records from the primary table that are used for the

stinct Union All output:

Employee Name Employee ID Employment Date

Jeffrey Jones 123 1/5/2009

Shalini Kumar 345 1/5/2009

Lupe Garcia 456 2/8/2009

Dominique Bisset 789 2/8/2009

This table shows the records from the secondary table that are used for the Distinct

Union All output:

Employment Date Employee Name

1/5/2009 Jeffrey Jones

3/7/2009 Sheila Watkins

9/23/2009 Dominique Bisset

9/23/2009 Tran Nguyen

This example shows the result set from the Distinct Union All joining of the two tables:

Employment Date

1/5/2009

2/8/2009

1/5/2009

9/23/2009

3/7/2009

1.5 Primary Key Fields

The fields that are included in the primary index of a table are displayed as key fields

in JD Edwards EnterpriseOne Business View Design Aid. These fields include a key

icon next to the field name. The primary key fields of a table are always included in

the business view. Business views then carry information from the table to an

application. To carry forward additional information other than the primary key fields,

select additional fields to include in the business view.

2 Designing Business Views

This section contains the following topics:

■ Section 2.1, "Understanding Business View Design Aid"

■ Section 2.2, "Understanding Business View Naming Conventions"

■ Section 2.3, "Understanding Business View Design Aid"

■ Section 2.4, "Creating Business Views"

2.1 Understanding Business View Design Aid

Before you begin designing a business view, consider the requirements. Determine the

purpose of the application that needs the business view and the data items that are

required. You can then identify the tables in which the required data items reside.

Adding a new business view does not affect performance; however, using an existing

business view that contains many columns that are not required by the application

might negatively affect performance.

Business views usually contain a few more fields than are used on the form, in the

grid, or in the batch application. The unused fields are related to the fields that are

required. If requirements change, these fields can be added to the application without

redesigning the business view.

You can modify business views to reflect changes in business requirements. You can

easily add fields to existing business views. Deleting fields from business views,

however, is more complicated. If you need to delete fields from a business view, ensure

that those fields are not currently used in an interactive or batch application.

You can use different business views for each form type that is included in an

interactive application. Typically, search & select forms include the minimum number

of fields that are required to keep them at a nominal size. Search & select forms should

include:

■ Only the fields that are necessary for filtering searches.

■ The associated output fields, such as descriptions.

Find browse and parent child forms include more fields and are typically larger than

search & select forms. However, you should still limit the number of fields on find

browse and parent child forms to include only those fields that are required for

filtering and displaying the associated output fields.

Input-capable forms are usually large and include all of the fields from the table. They

should include all of the fields that are necessary to add or update a record, including

audit information.

2.2 Understanding Business View Naming Conventions

When naming a business view object, guidelines indicate that the name can be a

maximum of eight characters and should be formatted as:

VzzzzzzA,where:

V is the business view.

zzzzzz is the characters that represent the primary table.

A is a letter that designates the sequence of the business view.

For example, V0101A is the first business view that is created over the F0101 table,

V0101B is the second business view that is created over the same table, and V0101C is

the third business view that is created.

2.2.1 External Developer Considerations for Naming Business Views

The term external development refers to applications that are created by developers who

are not employed by Oracle's JD Edwards, such as consultants who create custom

applications for clients. To prevent interference between JD Edwards EnterpriseOne

and non-JD Edwards EnterpriseOne objects, you must use caution when naming an

external business view. When you create business views for an application that is

created by external developers, format the business view name as:

Vssss9999,where:

V is the business view.

ssss is the system code for the enterprise.

9999 is a unique next number or chara

cter pattern within the enterprise.

Provide a business view description with a maximum of 60 charact

ers. It should reflect

the application description followed by the form type, for example, Item Master

Browse and Item Master Revisions.

Primary key fields should remain in the business view and should not be reorganized.

you try to remove a primary key field, you receive an error message.

Note: At least one business view for each table should include all

columns from the table. Only one business view is allowed for each

form type, except for a header detail form. You can use two business

views on header detail forms, one for the header portion of the form

and one for the detail portion.

2.2.2 Naming Joined Business Views

The description of a joined table business view should include the names of the tables

that are being joined. Place the primary table first, and separate the table names using

a forward slash (/).

For example, create a joined table business vi

ew by joining the F4101 (Item Master)

table and the F4102 (Item Branch) table. The F4101 table is the primary table, so the

business view should be named:

Item Master and Branch F4101/F4102.

2.3 Understanding Business View Design Aid

This section provides an overview of the JD Edwards EnterpriseOne Business View

Design Aid interface and discusses:

■ Tables for business views.

■ Data items for business views.

■ Select Distinct.

■ Table joins.

■ Table unions.

2.3.1 Understanding the Business View Design Aid Interface

This table describes the forms presented in the single JD Edwards EnterpriseOne

Business View Design Aid window:

Form Description

Table Joins Displays the tables that you select, alo

ng with the columns

that are included in the tables. A key icon appears next to

the primary key fields. The primary key fields are fields

that are included in the primary index of the table. The

primary table is where an application begins a search.

Available Tables Enables you to locate tables and move them to the Table

Joins fo

rm.

Selected Columns Displays the data items that you select from the table to

include in the business view.

Object Properties Displays the properties of a data item that is selected in the

Selected Columns form.

Consider these results when deleting business views and components of business

views:

Deletion Results

Deleting a data item from a business

ew.

If the data item is used in an application, you receive an

error message when you attempt to run the application.

If this occurs, you must open the application and delete

the data item from the application.

Deleting an entire table from a

siness view.

You cannot run any of the applications that use the

business view. If this occurs, you must open the

application and delete all items that reside in the deleted

table or attach a different business view to the

application and reconnect all of the controls.

Deleting an entire business view. Any forms that use the busines

s view will fail. If this

occurs, you must select a new business view for the

forms and reconnect all of the controls.

2.3.2 Tables for Business Views

When creating custom business views, you must first select appropriate tables. The

tables that you select must include the fields that are required to meet the business

purpose. Although you can create one large business view to retrieve and update the

columns that are required by the application, performance is negatively affected.

Whenever possible, consider using table joins rather than creating one large business

view that contains many data items.

Note: To ensure maximum performance in applications, use these

guidelines for the number of tables that are joined in a business view:

■ Fifteen tables if all joins are simple joins.

■ Fifteen tables if any of the joins is an outer join or in the event of a

table union.

See Also:

■ Creating Table Joins.

2.3.4 Select Distinct

When a business view includes the primary key fields of the primary table, every row

of the business view query is unique. If for some reason the business view does not

contain all primary key fields of the primary table, then duplicate rows can occur

during the business view query.

For example, JD Edwards EnterpriseOne Journal Entry is unique by line number and

document number

. However, each document can contain multiple lines. If you need to

display only one record per document, you can use Select Distinct to fetch only the

first occurrence of the document number, not all of the detail lines within the

document.

Business views that include a primary table that

contains any of the following

columns, which are used for currency support and security, might cause the Select

Distinct feature to display duplicate values:

Column Description

CO Company

2.3.4.1 Example: Select Distinct Feature The business view that is used for this example,

Event Detail Business View (V98EVDTL), uses the primary index of the primary table,

F89EVDTL (Event Detail File), by default. The primary index of a JD Edwards

EnterpriseOne table must be unique. A unique primary index ensures that the system

does not return duplicate values when the business view query is generated. However,

JD Edwards EnterpriseOne Business View Design Aid enables you to select any other

index of the primary table when you process the business view.

These steps illustrate how the Select Distinct feature works:

1. In JD Edwards EnterpriseOne Business View Design Aid, select the primary table.

2. From the Table menu, select Change Index to change the primary table index.

Change Index is available only for the primary table.

The system displays a warning indicating that the selected column list will be

changed.

3. Click Yes to continue.

The Available Indices form appears. The first edit field on the form displays the

current index of the table that is used by the business view. The default is the

primary index.

4. For this example, select Key by Formtyp, Evtype, Obj from Available Indices, and

click OK.

The Table Joins form and the Selected Columns form reflect the keys of the new

index.

5. Save the changes and quit JD Edwards EnterpriseOne Business View Design Aid.

If you run an application that uses the V98EVDTL business view with Select

Distinct disabled and the changed business view index (Key by Formtyp, Evtyp,

Obj), the generated SQL statement is:

SELECT EDOBJTYPE, EDEVTYPE, EDFORMTYPE FROM PVC. F98EVDTL

Using this example, you might now have 281 rows of data from table F98EVDTL.

6. Reopen the V98EVDTL business view.

CRCD Currency Code - From

CRDC Currency Code - To

CRCX Currency Code - Denominated In

CRCA Currency Code - A/B Amounts

LT Ledger Type

AID Account ID

MCU Business Unit

KCOO Order Company (Company Code)

EMCU Business Unit Header

MMCU Branch

AN8 Address Number

Column Description

7. From the File menu, select Select Distinct.

8. Select Change Index to select the Key by Formtyp, Evtyp, Obj index from

Available Indices, and then click OK.

9. Save the business view, and quit JD Edwards EnterpriseOne Business View Design

Aid.

You might need to quit the software and sign in again. The software stores the

busine

ss view in cache memory. Even though you change a business view, the

previous business view runs until it is cleared from cache.

Generate and rerun the same application using

the V98EVDTL business view with

Select Distinct activated. The generated SQL statement is now:

SELECT DISTINCT EDOBJTYPE, EDEVTYPE, EDFORMTYPE FROM PVC. F98EVDTL_Continue2

Using this example, you might now have only 53 rows of data from table

F98EVDTL.

2.3.5 Table Joins

Create table joins in business views to access data from multiple tables in a single

application.

You typically use table joins for forms that are not inpu

t-capable, such as find browse

forms, and reports. You do not usually use joins for forms that update and add to the

database. When you are updating the database, the relationship between the records

must be precise. If you must use a table join for an input-capable form, only use a join

where the relationship between the two tables is simple.

If a business view uses multiple tables, link the

tables by establishing joins between

columns in those tables. The links define how rows from one table correspond to rows

in another table.

When you join a column in one table to a column in another table, both columns must

of the same data type. You can use the Object Properties form to view attributes for

a column to determine whether you can use it in a join. The Object Properties form

displays the attributes of the data item that you have highlighted on the Selected

Columns form.

Review each table and determine how the data in one table is related to the data in the

other

tables. You might need to add columns or build new indices in a table, or even

create new tables. If you build new indices, consider your business needs carefully

before you do so.

Note: To ensure maximum performance in applications, use these

guidelines for the number of tables joined in a business view:

■ Five tables if all joins are simple joins.

■ Three tables if any of the joins is an outer join or in the event of a

table union.

Note: To ensure maximum performance in applications, use these

guidelines for the number of tables joined in a business view (starting

in Tools Release 9.1.5):

■ Fifteen tables if all joins are simple joins.

■ Fifteen tables if any of the joins is an outer join or in the event of a

table union.

To join columns in a table join business view, use the mouse cursor to draw a line from

a column in the primary table to the associated column in a secondary table. When

you click the line that you drew, you can define the join by selecting a join type and an

operator from the Join menu. These menu options are not available until you have

clicked the line joining the columns.

The default join type is simple, and the default operator is equal. Available operators

■ Equal (=)

■ Not Equal (<>) (starting in Tools Release 9.1.5)

■ Less than (<)

■ Greater than (>)

■ Less than or equal (< =)

■ Greater than or equal (> =)

2.3.6 Table Unions

Use table unions to pull rows from tables that have the same structure. Table unions

pull rows that exist in both tables. The Union option is available only if the rows in one

table are also included in the other table.

Union All retrieves all the rows, even the duplicate rows, from the primary and

secondary t

ables.

2.4 Creating Business Views

This section discusses how to:

■ Add business views.

■ Select tables for business views.

■ Select data items for business views.

■ Use Select Distinct.

■ Create table joins.

■ Create table unions.

2.4.1 Forms Used to Add Business Views

Form Name Form ID Navigation Usage

Object

nagement

Workbench

W98220A EnterpriseOne Life Cycle

ools, Application

Development (GH902),

Object Management, Object

Management Workbench

Select and view objects

hat are included in

projects, and access the

JD Edwards

EnterpriseOne design

tools.

Add EnterpriseOne

ject to the

Project

W98220C Click the Objects node of a

oject, and click Add on

the Object Management

Workbench form.

Add a new object to a

project.

Add Object W9861AF Select Business View, and

click OK on the

Add

EnterpriseOne Object to

the Project form.

Add business views.

Business View

Design Aid

W9860AL Complete the object

information, and click OK

on the Add Object form.

Access Business View

Design Aid, and create a

type definition.

2.4.2 Adding Business Views

Access the Add Object form.

Object Name

Enter a unique name for the business view. Use the recommended naming convention

for naming JD Edwards EnterpriseOne objects.

Description

Enter a meaningful description of the business view.

Product Code

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

for clients (55–59), or for business partners (L00 through L99, M00 through M99, and

P00 through P99).

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 for description override purposes.

Example values include:

01: J

D Edwards EnterpriseOne Address Book

03B: JD Edwar

ds EnterpriseOne Accounts Receivable

04: J

D Edwards EnterpriseOne Accounts Payable

09: JD Ed

wards EnterpriseOne General Accounting

11: JD E

dwards EnterpriseOne Multicurrency

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. The value 300

represents business views.

Object Type

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

object type for a business view is BSVW. This field is populated by the system based

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

Text Search

Select this option to indicate that the business view is used by the runtime engine

during a text search. When designing the business view, you must also add at least one

GT (general text) data structure to the business view so that the runtime engine can

use it in text searches.

2.4.3 Selecting Tables for Business Views

Access the JD Edwards Business View Design Aid form.

1. On the Design Tools tab, click Start the Business View Design Aid.

2. On the Available Tables form, use the query by example (QBE) line to search for an

appropriate table.

3. Select one or more tables, and drag them to the Table Joins form.

This form is called Table Joins regardless of whether you are joining multiple

tab

les or working on a single table.

4. If more than one table is selected, double-click the title bar of the appropriate table

to designate it as the primary table.

If the business view contains multiple tables,

the system automatically designates

the first table that is added to the Table Joins form as the primary table. A crown

icon appears in the upper-left corner of the primary table. If a business view

contains only one table, that table is the primary table by default.

Note: To delete a table from a business view, select the table and

select Delete from the Table menu. You can also right-click the table

and select Delete from the pop-up menu.

2.4.4 Selecting Data Items for Business Views

Access the JD Edwards Business View Design Aid form.

1. On the Table Joins form, double-click the data items to include in the business

view.

Selected data items appear with a check mark on the Table Joins form. As you

lect each data item, the system displays it on the Selected Columns form.

2. To remove data items from a business view, double-click the data item either on

the Table Joins form or on the Selected Columns form.

2.4.5 Using Select Distinct

Access the JD Edwards Business View Design Aid form.

1. Select the primary table of the business view.

2. From the Table menu, select Distinct Mode.

3. From the Table menu, select Change Index.

This option changes the index of the prima

ry table to a non-unique index.

2.4.6 Creating Table Joins

Access the JD Edwards Business View Design Aid form.

1. On the Tables Joins form, click and draw a line that connects a column in the

primary table to an associated column in a secondary table.

Although the column names do not have to be the same, the attributes for Data

Type and Decimals must be identical before you can create a table join between

two columns. To determine whether data items are candidates for a join, click a

data item on the Table Joins form and view the data item attributes that are

displayed on the Object Properties form.

2. Click the line that joins the two columns.

Both fields are highlighted.

3. From the Join menu, select Types, and then select an appropriate join type.

Simple is the default join type.

4. From the Join menu, select Operators, and then select an appropriate operator.

Equal is the default join operator.

5. To delete a join, click the line that connects the two fields, and then select Delete

from the Join menu.

You can also right-click the join and select Delete from the pop-up menu.

2.4.7 Creating Table Unions

Access the JD Edwards Business View Design Aid form.

1. From the Table menu, select either Union Mode or Union All Mode.

You can also click the Union or Union All button on the toolbar.

The Union and Union All features are available only if all columns in one table

also reside in the other table.

2. Select the tables for which you want to create a table union.

2.4.8 Creating a Distinct Union All Business View

Access the JD Edwards Business View Design Aid form.

1. From the Table menu, select Union All Mode.

You can also click the Union All button on the toolbar.

2. From the Table menu, select Distinct Mode.

You can also click the Distinct button on the toolbar. The Distinct Union All Mode

feature is available only if all columns in one table also reside in the other table.

3. Select the tables for the business view.

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

JD Edwards EnterpriseOne Tools Business View Design Guide, Release 9.1.x

E21483-02

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.