This chapter contains the following topics:
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 redesign of 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 in use 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.
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.
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 Oracle's 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 character pattern within the enterprise.
Provide a business view description with a maximum of 60 characters. 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. If 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.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 view 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.
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.
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, along 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 form. |
| 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. |
| Available GT (Generic Text) Data Structures | Enables you to locate generic text data structures and move them to the Selected GT (Generic Text) Data Structures form. GT data structures are used for text search business views only. |
| Selected GT (Generic Text) Data Structures | Displays the generic text data structures that you select to include in the business view. GT data structures are used for text search business views only. |
Consider these results when deleting business views and components of business views:
| Deletion | Results |
|---|---|
| Deleting a data item from a business view. | 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 business 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 business view will fail. If this occurs, you must select a new business view for the forms and reconnect all of the controls. |
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:Join five tables if all joins are simple joins.
Join three tables if any of the joins is an outer join or in the event of a table union.
See Also:
If you include more than one table in a business view, you must indicate which table is the primary table. You can then select the required data items to include in the business view. All of the data items that reside in the tables that you selected are available for the business view.
Select the data items that are required by the interactive or batch application to include in the business view. When you create an application, you do not have to use every item in the business view. Balance between keeping the business view small for maximum performance and including enough fields to allow for future business requirements.
If the required data item appears in multiple tables, you typically want to select the data item from the primary table. Selecting the same data item from multiple tables causes the data item to appear multiple times in the business view. Each data item that is added to the business view includes its own table reference to identify its origin.
Note:
If you include multiple tables in a joined business view, the primary key fields are automatically selected from the tables. You cannot remove the primary key fields from the business view.If you include multiple tables in the business view, you must join the tables using a table join. You typically want to join tables on common key fields. You might also need to join on additional fields. You should join on as many fields as necessary to ensure that the data is fetched properly for each record. The joins must be performed on fields of the same field type.
Important:
To ensure maximum system performance, do not include more than 256 columns in business views.See Also:
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, Oracle's 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 |
| 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 |
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:
In JD Edwards EnterpriseOne Business View Design Aid, select the primary table.
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.
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.
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.
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.
Reopen the V98EVDTL business view.
From the File menu, select Select Distinct.
Select Change Index to select the Key by Formtyp, Evtyp, Obj index from Available Indices, and then click OK.
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 business 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.
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 input-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 be 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.
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 are:
Equal (=)
Less than (<)
Greater than (>)
Less than or equal (< =)
Greater than or equal (> =)
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.
Enter a unique name for the business view. Use the recommended naming convention for naming JD Edwards EnterpriseOne objects.
Enter a meaningful description of the business view.
Select a user-defined code (UDC) (98/SY) from the range of values that are reserved for clients, 55–59.
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: 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
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.
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.
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.
Access the JD Edwards Business View Design Aid form.
On the Design Tools tab, click Start the Business View Design Aid.
On the Available Tables form, use the query by example (QBE) line to search for an appropriate table.
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 tables or working on a single table.
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.Access the JD Edwards Business View Design Aid form.
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 select each data item, the system displays it on the Selected Columns form.
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.
Access the JD Edwards Business View Design Aid form.
Select the primary table of the business view.
From the Table menu, select Distinct Mode.
From the Table menu, select Change Index.
This option changes the index of the primary table to a non-unique index.
Access the JD Edwards Business View Design Aid form.
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.
Click the line that joins the two columns.
Both fields are highlighted.
From the Join menu, select Types, and then select an appropriate join type.
Simple is the default join type.
From the Join menu, select Operators, and then select an appropriate operator.
Equal is the default join operator.
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.
Access the JD Edwards Business View Design Aid form.
From the Table menu, select Union Mode.
You can also click the Union Mode button on the toolbar.
The Union Mode feature is available only if all columns in one table also reside in the other table.
Select the tables for which you want to create a table union.