Monday, December 28, 2009

Table Creations

This section describes specifications for how to define your tables and the required columns to add. It also covers the APPS registration.

While creating the table (with table and column comments), the developer needs to ensure the following things.

1. Normalization.
2. WHO Columns.
3. DFF (ATTRIBUTES) / KFF (SEGMENTS) enabling feature. (APPS initialization.)

1. Normalization

Data anomalies can add to problems with large consumption of storage space, slow execution times, etc. There are 3 types of data anomalies

There are 5 levels of simplification (forms)


2.WHO Columns

If you add special WHO columns to your tables, your application users can track changes made to their data. Oracle Application Object Library lets users differentiate between changes they make using forms and changes they make using concurrent programs. Columns that tell you information about the creation and the last update of a row are:

LAST_UPDATED_BY
LAST_UPDATE_DATE
LAST_UPDATE_LOGIN
CREATED_BY
CREATION_DATE

Add the following columns to tell you information about the concurrent program that updated a row:

REQUEST_ID
PROGRAM_APPLICATION_ID
PROGRAM_ID
PROGRAM_UPDATE_DATE

You should allow NULLs in these columns because they do not have values when a user inserts a new row using a form.

3. DFF (ATTRIBUTES) / KFF (SEGMENTS) enabling feature. (APPS initialization.)

If you are going to create KFF / DFF based on your custom table; you need to include flex field structured columns and should registered your table in APPS.

Flex fields and Oracle Alert are the only features or products that require the custom tables to be registered in Oracle Applications (Application Object Library) before they can be used.

Oracle provides AD_DD API for registering table (and their columns) in AOL.

Note: 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 AOL 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.

The list of procedures (AD_DD)

No comments:

Post a Comment