Understanding Editioning

What is Edition?

• Edition is short for “Edition-Based Redefinition”.
• You can maintain multiple versions of the same database object.
• Put simply, it means you have two version of a package, procedure or view.

Are all objects “Editionable”?

• No, not all objects are “editionable”.
• Rule of thumb – Any object that holds data cannot be editioned.

Why edition in R12.2?

Editioning is the foundation for Online Patching (ADOP).

• 2 versions of the application are running: Run instance and Patch instance
• 2 versions of editioned objects
• 1 data set

Object List and Preferred Schema

Y = Yes

Tables

• A table name must be unique within the first 29 bytes.
• A table name must not end with the ‘#’ character.
• A table cannot be owned by APPS schema.
• A table must be owned by an Oracle E-Business Suite product or custom product schema.
• A table must have an editioning view.
• An APPS synonym (serving as the logical table name) must point to the editioning view.
• Table must not be “index-organized” (create table ... ORGANIZATION INDEX).
• Index-organized tables cannot be patched using online patching.
• DDL statements such as TRUNCATE will not work on an APPS table synonym that points to an editioning view.
• To truncate a table, you must supply the actual base table (owner.table_name) in the truncate command.

Views

A View name must be a non-quoted identifier. A View is an Editioned object type. Do not reference the implicit ROWID pseudo-column of a join view.

The availability of implicit ROWID from a join view may change without warning. Instead, use the explicit ROW_ID column that should be present in all join views.

Example of standard view in R12.2.6:

Materialized Views

• A Materialized View is a non-editioned object type, and therefore a materialized view cannot directly reference editioned objects.
• The materialized view implementation is automatically generated from the materialized view definition using the AD_ZD_MVIEW.UPGRADE procedure.
• Do not attempt to directly create or replace the materialized view implementation.
• A materialized view definition must specify a column alias for each item in the select list.

Recommended Approach:

Have views and MV in APPS to avoid giving grants to custom schema for accessing standard objects in APPS.

Synonyms

• Most APPS synonyms are automatically created by the ADOP patching tool. Do not create, modify, or drop automatically managed synonyms.
• A table synonym must point to the editioning view, not the table, if the editioning view exists.
• A synonym must point to an object.
• Do not reference tables directly
• Do not create public synonyms
• Use APPS synonyms to reference Oracle E-Business Suite tables

Sequences

Sequences are non-editioned objects.

Example of standard sequence in Oracle EBS R12.2.7:

Recommended Approach:

Have the sequence as Oracle E-Business Suite product schema and a synonym in APPS.

Indexes

• The index name must contain an underscore.
• The index key length should be less than 3125 bytes.
• The “CREATE INDEX ... ON ...” statement must specify the fully-qualified table name, not the APPS table synonym.

Good Example: create index SOME_TABLE_N1 on SCHEMA.SOME_TABLE ...

Bad Example: create index SOME_TABLE_N1 on SOME_TABLE ...

• The Index owner can be APPS/Oracle E-Business Suite product schema, but it should be on the table.

Triggers

The trigger name must be a non-quoted identifier. A table trigger must be on the editioning view (EV), not the table. Triggers on tables will be automatically moved to the corresponding editioning view during the upgrade.Triggers are mostly owned by APPS.

Example:

Functions, Procedures, Packages

The package name must be a non-quoted identifier.

There are no special considerations for usage standards.

Example:

Recommendation:

It is recommended to have the package/procedure and functions in APPS to avoid schema references and avoid giving grants to Custom schema for accessing all standard objects like Standard API’s, tables, etc.

References

Oracle® E-Business Suite Technical Planning Guide Release 12.2:
https://support.oracle.com/rs?type=doc&id=1585857.1

https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=366265365261036&id=1577661.1&_afrWindowMode=0&_adf.ctrl-state=9lt44f6si_4