This section describes specifications for how to define database objects. It includes information for defining your tables and the required columns to add. It also covers special data types and declarative constraints.
Oracle E-Business Suite uses Oracle Cost-Based Optimization (CBO) instead of the Rule-Based Optimization (RBO) used in previous versions. All new code should be written to take advantage of Cost-Based Optimization. Where your custom application code was tuned to take advantage of Rule-Based Optimization, you may need to retune that code for Cost-Based Optimization.
For additional information, refer to Oracle E-Business Suite and Query Optimization, Oracle E-Business Suite Maintenance Guide and the Oracle database tuning documentation.
The Record History (WHO) feature reports information about who created or updated rows in Oracle E-Business Suite tables. Oracle E-Business Suite upgrade technology relies on Record History (WHO) information to detect and preserve customizations.
If you add special WHO columns to your tables and WHO logic to your forms and stored procedures, your users can track changes made to their data. By looking at WHO columns, users can differentiate between changes made by forms and changes made by concurrent programs.
You represent each of the WHO columns as hidden fields in each block of your form (corresponding to the WHO columns in each underlying table). Call FND_STANDARD.SET_WHO in PRE-UPDATE and PRE-INSERT to populate these fields.
The following table lists the standard columns used for Record History (WHO), the column attributes and descriptions, and the sources for the values of those columns. Set the CREATED_BY and CREATION_DATE columns only when you insert a row (using FND_STANDARD.SET_WHO for a form).
| Column Name | Type | Null? | Foreign Key? | Description | Value |
|---|---|---|---|---|---|
| CREATED_BY | NUMBER(15) | NOT NULL | FND_ USER | Keeps track of which user created each row | TO_NUMBER (FND_ PROFILE. VALUE ('USER_ID')) |
| CREATION_ DATE | DATE | NOT NULL | n/a | Stores the date on which each row was created | SYSDATE |
| LAST_ UPDATED_BY | NUMBER(15) | NOT NULL | FND_ USER | Keeps track of who last updated each row | TO_NUMBER (FND_ PROFILE. VALUE ('USER_ID')) |
| LAST_UPDATE_ DATE | DATE | NOT NULL | n/a | Stores the date on which each row was last updated | SYSDATE |
| LAST_UPDATE_ LOGIN | NUMBER(15) | n/a | FND_ LOGINS | Provides access to information about the operating system login of the user who last updated each row | TO_NUMBER (FND_ PROFILE. VALUE ('LOGIN_ ID')) |
Any table that may be updated by a concurrent program also needs additional columns. The following table lists the concurrent processing columns used for Record History, the column attributes and descriptions, and the sources for the values of those columns.
| Column Name | Type | Null? | Foreign Key to Table? | Description |
|---|---|---|---|---|
| REQUEST_ID | NUMBER(15) | FND_ CONCURRENT_ REQUESTS | Keeps track of the concurrent request during which this row was created or updated | |
| PROGRAM_ APPLICATION_ ID | NUMBER(15) | FND_ CONCURRENT_ PROGRAMS | With PROGRAM_ID, keeps track of which concurrent program created or updated each row | |
| PROGRAM_ID | NUMBER(15) | FND_ CONCURRENT_ PROGRAMS | With PROGRAM_ APPLICATION_ID, keeps track of which concurrent program created or updated each row | |
| PROGRAM_ UPDATE_DATE | DATE | PROGRAM_ UPDATE_ DATE | Stores the date on which the concurrent program created or updated the row |
Some operations that must be done at commit time do not seem designed for a table handler. For example, event handlers are preferred to table handlers for setting Record History information for a record, or determining a sequential number. The logic for these operations may be stored in a PRE_INSERT and/or PRE_UPDATE event handler, which is called from PRE-INSERT and PRE-UPDATE block-level triggers during inserts or updates.
See: FND_STANDARD: Standard APIs
Apply the CREATION_OR_LAST_UPDATE_DATE property class to the form fields CREATION_DATE and LAST_UPDATE_DATE. This property classes sets the correct attributes for these fields, including the data type and width.
Never use Record History columns to qualify rows for processing. Never depend on these columns containing correct information.
In general, you should not attempt to resolve Record History columns to HR_EMPLOYEES; if you must attempt such joins, they must be outer joins.
For blocks that are based on a table, but do not have Record History information, disable the menu entry HELP->ABOUT_THIS_RECORD (all other cases are handled by the default menu control).
Code a block-level WHEN-NEW-BLOCK-INSTANCE trigger (style "Override") with these lines:
app_standard.event('WHEN-NEW-BLOCK-INSTANCE');
app_special.enable('ABOUT', PROPERTY_OFF);
See: APP_SPECIAL: Menu and Toolbar Control
This section discusses the declarative constraints the Oracle database permits on tables, and when to use each feature with your Oracle E-Business Suite tables.
For the most part, any constraint that is associated with a table should be duplicated in a form so that the user receives immediate feedback if the constraint is violated.
Warning: You should not create additional constraints on Oracle E-Business Suite tables at your site, as you may adversely affect Oracle E-Business Suite upgrades. If you do create additional constraints, you may need to disable them before upgrading Oracle E-Business Suite.
Use wherever appropriate. Declare the corresponding fields within Oracle Forms as "Required" = True.
In general, do not use this feature due to potential locking problems with Oracle Forms. You may be able to use this feature with tables that are not used by forms (for example, those used by batch programs), or tables that contain columns that are not maintained by forms. For example, defaulting column values can make batch programs simpler. Possible default values are SYSDATE, USER, UID, USERENV(), or any constant value.
Use wherever appropriate. A unique key may contain NULLs, but the key is still required to be unique. The one exception is that you may have any number of rows with NULLS in all of the key columns.
In addition, to implement a uniqueness check in a form, create a PL/SQL stored procedure which takes ROWID and the table unique key(s) as its arguments and raises an exception if the key is not unique. Only fields that the user can enter should have a uniqueness check within the form; system-generated unique values should be derived from sequences which are guaranteed to be unique.
See: Uniqueness Check
Use this feature to check if a column value is valid only in simple cases when the list of valid values is static and short (i.e., 'Y' or 'N').
CHECK provides largely duplicate functionality to database triggers but without the flexibility to call PL/SQL procedures. By using triggers which call PL/SQL procedures instead, you can share constraints with forms and coordinate validation to avoid redundancy.
CHECK does provide the assurance that all rows in the table will pass the constraint successfully, whereas database triggers only validate rows that are inserted/updated/deleted while the trigger is enabled.
This is not usually a concern, since Oracle E-Business Suite database triggers should rarely be disabled. Some triggers (such as Alert events) are disabled before an upgrade and re-enabled at the end of the upgrade.
We strongly advise against the use of database triggers.
Define a Primary Key for your tables as necessary.
Do not use the Declarative Cascade Delete or the Foreign Key Constraint when defining tables. Cascade Delete does not work across distributed databases, so you should program cascade delete logic everywhere it is needed.
To implement a referential integrity check, create a PL/SQL stored procedure which takes the table unique key(s) as its argument(s) and raises an exception if deleting the row would cause a referential integrity error.
See: Integrity Checking
Avoid creating tables with the LONG, LONG RAW, or RAW datatypes. Within Oracle Forms, you cannot search using wildcards on any column of these types. Also, a table column cannot be of type LONG and LONG RAW for use with Online Patching. Use CLOB for LONG, and BLOB for RAW and LONG RAW columns instead.
As stated earlier, table column cannot be of type LONG or LONG RAW in Oracle E-Business Suite Release 12.2. With Online Patching, LONG and LONG RAW columns cannot be referenced in a database trigger. This restriction means that: LONG and LONG RAW columns cannot be patched via an online patch as the feature uses crossedition triggers to upgrade data. Changes to seed data in the RUN edition cannot be propagated to the PATCH edition because crossedition triggers are used to synchronize the changes.
This section describes some of the conversion procedures to change usages of LONG to CLOB.
For each table column that has been changed from LONG to CLOB, any form block item that has references to the column will need to have its Oracle Forms data type changed from 'Char' to 'Long'. Remember that CLOB is the database column type and 'Long' is the Forms item data type. To change the form's data type, open your form in the Forms Builder and open the property sheet (Property Palette) for the item that references the CLOB.
Scan your form and form library code for any references to the modified form item. Since the form item is now a Forms Long data type, functions like LENGTH(), LENGTHB(), SUBSTR() may behave differently. Thoroughly test your form to exercise the logic referencing the Forms Long data type.
If you are binding a LONG or LONG RAW that is being changed to a CLOB, then you should change the bind from SQLT_LNG to SQLT_CLOB. Otherwise, an unknown datatype error will be thrown.
If you are using UPI code, ensure that you have applied the RDBMS patch 13259364.
Check all packages to ensure that all affected variables are changed from LONG to CLOB.
Examples (with updated variables):
PROCEDURE insert_flex_validation_events( flex_value_set_id IN NUMBER, event_code IN varchar2, user_exit IN CLOB)
document_long_text CLOB;
document_long_text fnd_documents_long_text.long_text%type;
In JDBC 2.0 and 3.01, you should fetch the data from a CLOB column using ResultSet.getClob() to obtain a reference to the column, and then obtain a character input stream object to read the contents of the CLOB field in a parallel fashion. Because Oracle Database 11.2.0.4 (and later) JDBC drivers fully implement getString() for CLOBs, no program conversion should be necessary.
BC4J and UIX data binding is very sensitive to data types. As such, the recommendation is to perform the following steps for queries that are affected by the data type change.
Model
Entity Objects (EO) - The datatype of the attributes should be changed.
Attribute type - Should be changed to CLOBDOMAIN.
Database column field for the attribute - The type should be changed to CLOB.
Read-only View Objects (VO) (not associated to an EO): The datatype of the attributes should be changed.
Attribute type - Should be changed to CLOBDOMAIN.
Database column field for the attribute - The type should be changed to CLOB.
VOImpl.java, EOImpl.java, AMImpl.java - Changes should be made to custom methods (that are not part of the standard definition of the superclass object). This convention is in case attributes are manipulated and the real datatype (CLOBDOMAIN) needs to be used.
After your modifications, perform suitable tests using the BC4J tester object.
View
Attribute type of the item (messageTextInput) was changed from VARCHAR2 (compatible with LONG) to CLOB.
Controllers were modified to replace references to datatypes when there is string manipulation.
Export button - If there is any manipulation of standard data handling with the export bean, it should be modified to reference the correct data types.
Refer to the Oracle Application Framework documentation for more information on Oracle Application Framework development.
If a table contains a column named with a PL/SQL or an Oracle Forms reserved word, you must create a view over that table that aliases the offending column to a different name. Since this view does not join to other tables, you can still INSERT, UPDATE, and DELETE through it.
In general, complex blocks are based on views while simple setup blocks are based on tables. The advantages to using views include:
Network traffic is minimized because all foreign keys are denormalized on the server
You do not need to code any POST-QUERY logic to populate non-database fields
You do not need to code PRE-QUERY logic to implement query-by-example for non-database fields
You should also base your Lists of Values (LOVs) on views. This allows you to centralize and share LOV definitions. An LOV view is usually simpler than a block view, since it includes fewer denormalized columns, and contains only valid rows of data.
See: Example LOV
Whenever performance is an issue and your table has foreign keys, you should define a view to improve performance. Views allow a single SQL statement to process the foreign keys, reducing parses by the server, and reducing network traffic.
Any object available in the database promotes modularity and reuse because all client or server side code can access it. Views are extremely desirable because:
They speed development, as developers can build on logic they already encapsulated
They modularize code, often meaning that a correction or enhancement can be made in a single location
They reduce network traffic
They are often useful for reporting or other activities
They can be easily and centrally patched at a customer site
Avoid creating views that are used by only one SQL statement. Creating a view that is only used by a single procedure increases maintenance load because both the code containing the SQL statement and the view must be maintained.
The first column your view should select is the ROWID pseudo-column for the root table, and the view should alias it to ROW_ID. Your view should then include all of the columns in the root table, including the WHO columns, and denormalized foreign key information.
Tip: You only need to include the ROWID column if an Oracle Forms block is based on this view. The Oracle Forms field corresponding to the ROW_ID pseudo-column should use the ROW_ID property class.
In Oracle Forms, you need to change the block Key Mode property to Non-Updatable to turn off Oracle Forms default ROWID references for blocks based on views. Specify the primary keys for your view by setting the item level property Primary Key to True.
For example, a view based on the EMP table has the columns ROW_ID, EMPNO, ENAME, DEPTNO, and DNAME. Set the Key Mode property of block EMP_V to Non-Updatable, and set the Primary Key property of EMPNO to True.
If your block is based on a table, the block Key Mode should be Unique.
When basing a block on a view, you must code ON-INSERT, ON-UPDATE, ON-DELETE, and ON-LOCK triggers to insert, update, delete, and lock the root table instead of the view.
Single table views do not require triggers for inserting, updating, deleting and locking. Set the block Key Mode to Unique. Single table views do not require a ROW_ID column.
Do not use the CHR() function (used to define a character by its ASCII number) on the server side. This causes problems with server-side platforms that use EBCDIC, such as MVS. You should not need to embed tabs or returns in view definitions.
This section discusses standards for creating and using sequences.
Use each sequence to supply unique ID values for one column of one table.
Do not create sequences that wrap using the CYCLE option or that have a specified MAXVALUE. The total range of sequences is so great that the upper limits realistically are never encountered.
In general, do not design sequences that wrap or have limited ranges.
Use a NUMBER datatype to store sequence values within PL/SQL.
If you need to handle a sequence generate a sequence value in your C code, do not assume that a sequence-generated value will fit inside a C long variable. The maximum value for an ascending sequence is 10^27, whereas the maximum value for a C signed long integer is 10^9. If 10^9 is not a reasonable limit for your sequence, you may use a double instead of a long integer. Remember that by using a double for your sequence, you may lose some precision on fractional values. If you do not need to do arithmetic, and simply need to fetch your sequence either to print it or store it back, consider retrieving your sequence in a character string.
Do not rely on the FND_UNIQUE_IDENTIFIER_CONTROL table to supply sequential values. Use a sequence or the sequential numbering package instead. The FND_UNIQUE_IDENTIFIER_CONTROL table is obsolete and should not have any rows for objects in your product.
Additionally, do not create application-specific versions of the FND table to replace the FND_UNIQUE_IDENTIFIER_CONTROL table.
You register your custom application tables using a PL/SQL routine in the AD_DD package.
Flexfields, Oracle Alert, and Oracle Web Applications Desktop Integrator are the only features or products that depend on this information. Therefore you only need to register those tables (and all of their columns) that will be used with flexfields, Oracle Alert, or Oracle Web Applications Desktop Integrator. You can also use the AD_DD API to delete the registrations of tables and columns from Oracle Application Object Library tables should you later modify your tables.
If you alter the table later, then you may need to include revised or new calls to the table registration routines. To alter a registration you should first delete the registration, then reregister the table or column. You should delete the column registration first, then the table registration.
You should include calls to the table registration routines in a PL/SQL script. Though you create your tables in your own application schema, you should run the AD_DD procedures against the APPS schema. You must commit your changes for them to take effect.
The AD_DD API does not check for the existence of the registered table or column in the database schema, but only updates the required FND tables. You must ensure that the tables and columns registered actually exist and have the same format as that defined using the AD_DD API. You need not register views.
procedure register_table (p_appl_short_name in varchar2, p_tab_name in varchar2, p_tab_type in varchar2, p_next_extent in number default 512, p_pct_free in number default 10, p_pct_used in number default 70); procedure register_column (p_appl_short_name in varchar2, p_tab_name in varchar2, p_col_name in varchar2, p_col_seq in number, p_col_type in varchar2, p_col_width in number, p_nullable in varchar2, p_translate in varchar2, p_precision in number default null, p_scale in number default null); procedure delete_table (p_appl_short_name in varchar2, p_tab_name in varchar2); procedure delete_column (p_appl_short_name in varchar2, p_tab_name in varchar2, p_col_name in varchar2);
| Variable | Description |
|---|---|
| p_appl_short_ name | The application short name of the application that owns the table (usually your custom application). |
| p_tab_name | The name of the table (in uppercase letters). |
| p_tab_type | Use 'T' if it is a transaction table (almost all application tables), or 'S' for a "seed data" table (used only by Oracle E-Business Suite products). |
| p_pct_free | The percentage of space in each of the table's blocks reserved for future updates to the table (1-99). The sum of p_pct_free and p_pct_used must be less than 100. |
| p_pct_used | Minimum percentage of used space in each data block of the table (1-99). The sum of p_pct_free and p_pct_used must be less than 100. |
| p_col_name | The name of the column (in uppercase letters). |
| p_col_seq | The sequence number of the column in the table (the order in which the column appears in the table definition). |
| p_col_type | The column type ('NUMBER', 'VARCHAR2', 'DATE', etc.). |
| p_col_width | The column size (a number). Use 9 for DATE columns, 38 for NUMBER columns (unless it has a specific width). |
| p_nullable | Use 'N' if the column is mandatory or 'Y' if the column allows null values. |
| p_translate | Use 'Y' if the column values will be translated for an Oracle E-Business Suite product release (used only by Oracle E-Business Suite products) or 'N' if the values are not translated (most application columns). |
| p_next_extent | The next extent size, in kilobytes. Do not include the 'K'. |
| p_precision | The total number of digits in a number. |
| p_scale | The number of digits to the right of the decimal point in a number. |
Here is an example of using the AD_DD package to register a flexfield table and its columns:
EXECUTE ad_dd.register_table('FND', 'CUST_FLEX_TEST', 'T', 8, 10, 90);
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'APPLICATION_ID', 1, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ID_FLEX_CODE', 2, 'VARCHAR2', 30, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'LAST_UPDATE_DATE', 3, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'LAST_UPDATED_BY', 4, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'UNIQUE_ID_COLUMN', 5, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'UNIQUE_ID_COLUMN2', 6, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SET_DEFINING_COLUMN', 7, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SUMMARY_FLAG', 8, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ENABLED_FLAG', 9, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'START_DATE_ACTIVE', 10, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'END_DATE_ACTIVE', 11, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT1', 12, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT2', 13, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT3', 14, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT4', 15, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT5', 16, 'VARCHAR2', 60, 'Y', 'N');