With the transition to Oracle E-Business Suite (EBS) R12.2, the technical landscape for customizations shifted significantly. The introduction of Online Patching (adop) and Edition-Based Redefinition (EBR) means that traditional DDL operations are no longer sufficient. To maintain system integrity and ensure zero-downtime during patch cycles, all custom tables must be “Online Patching Compliant.”
This guide outlines the mandatory architecture and deployment workflow for creating and maintaining custom tables in an R12.2 environment.
The Architecture of Compliance
In R12.2, an application never interacts with a base table directly. Instead, a three-layer abstraction model is required:
- The Base Table: Resides in the custom product schema.
- The Editioning View (EV): Provides an edition-aware logical layer over the base table.
- The APPS Synonym: Acts as the universal entry point for all code (PL/SQL, OAF, Forms, and Reports).
Step 1: Creating the Base Table
All development begins in the Run Edition. When creating the initial table in your custom schema (e.g., `XXCUST`), adhere to the following R12.2 standards:
Tablespace Selection: Utilize the `APPS_TS_TX_DATA` tablespace for transactional data.
Constraint Standards: Oracle recommends using Unique Indexes rather than Primary Key constraints to facilitate smoother patching transitions.
Initial State: Upon creation, the table is considered “non-compliant” as it lacks the necessary EBR metadata.
Step 2: Upgrading to EBR Compliance
To integrate the table into the Online Patching framework, you must “upgrade” it using the ‘AD_ZD_TABLE’ utility. Execute the following from the `APPS` schema:
— Upgrading the table to create the EV and APPS synonym
EXEC AD_ZD_TABLE.UPGRADE(‘<CUSTOM_SCHEMA>’, ‘XX_TABLE_NAME’);
What Happens when you run the above command?
Editioning View (EV): A view named `XX_TABLE_NAME#` is generated in the custom schema. This view automatically maps all columns from the base table.
APPS Synonym: A synonym named `XX_TABLE_NAME` is created in the `APPS` schema, pointing directly to the EV.
Dependency Management: All subsequent code must reference the APPS Synonym. This ensures that when the table structure changes in a future Patch Edition, the code continues to point to the correct logical version of the data.
Step 3: Deployment via XDF
In a professional EBS ecosystem, manual DDL is discouraged in downstream environments (UAT/Production). Instead, use XML Definition Files (XDF) to package your objects.
- Data Requirement: Insert at least one seed row into the table in your development instance. This is a technical prerequisite for the XDF utility to accurately capture the metadata.
- Generation: Execute `xdfgen.pl` to generate the `.xdf` file.
- Application: During the patch application, the `xdfcmp.pl` utility automatically invokes `AD_ZD_TABLE.UPGRADE`, ensuring the target environment remains compliant without manual intervention.
Step 4: Managing Structural Evolution
Table structures are rarely static. When requirements evolve (e.g., adding new columns), compliance must be maintained to avoid breaking the `adop` cycle.
Method A: XDF-Driven Updates (Recommended)
Modify the source XDF and deploy via the standard patching toolset. This is the most robust method for maintaining consistency across the landscape.
Method B: Manual DDL (Development Only)
If an `ALTER TABLE` command is executed manually during development, the Editioning View (EV) will become out of sync with the base table. To synchronize the layers, run:
EXEC AD_ZD_TABLE.PATCH(‘<CUSTOM_SCHEMA>’, ‘XX_TABLE_NAME’);
Conclusion
Adhering to the `AD_ZD` framework is not optional in Oracle EBS R12.2; it is a prerequisite for a healthy, patchable system. By standardizing the use of Editioning Views and XDF-based deployments, we ensure that our custom extensions remain performant and transparent during critical maintenance windows.
