How To Get Those Customizations to Work Correctly with Oracle EBS R12.2 Editioning (EBR)

Oracle e-Business Suite R12.2 Overview of Edition-Based Redefinition (EBR) in 11gR2 databases.

Scenario:
Creating a new view based upon the AP.AP_SUPPLIERS table to reside in a new XXCUSTOMSCHEMA.

If you attempt the simple:

create or replace force view XXCUSTOMSCHEMA.AP_SUPPLIER_NEW_V
as
SELECT *
FROM ap.ap_suppliers ;

It works, but your data and table definition may change whenever patching editions are in-play.

If you attempt to just reference the editioned object instead:

create or replace force view XXCUSTOMSCHEMA.AP_SUPPLIER_NEW_V
as
SELECT *
FROM ap.ap_suppliers# /* or the preferred apps.ap_suppliers synonym */

You will receive the ominous ORA-38818: illegal reference to editioned object error message.

What’s missing? The XXCUSTOMSCHEMA isn’t recognized as an edition-friendly schema by Oracle’s e-Business Suite (nor the database), yet.

Create your XXCUSTOMSCHEMA in the usual way:

create user XXCUSTOMSCHEMA
identified by (password)
default tablespace USERS
temporary tablepace TEMP
profile DEFAULT
account UNLOCK;

/* as APPS user */

exec FND_ORACLE_USER_PKG.LOAD_ROW(‘XXCUSTOMSCHEMA’, ‘CUSTOM’, ‘INVALID’, NULL, ‘N’, ‘B’);

/* as SYSTEM user */

alter session set current_schema=APPS;
exec AD_ZD_PREP.ENABLE_CUSTOM_USER(‘XXCUSTOMSCHEMA’);

/* Create your SELECT grants to the editioned objects as APPS user */

grant select on ap.ap_suppliers#  to XXCUSTOMSCHEMA;

/* OR if you’re running newer than 12.2.5…the editioning security will cause hundreds of invalid editioned object stubs that need re-compilation unless you instead use */

exec AD_ZD.grant_privs(‘SELECT’, ‘AP_SUPPLIERS’, ‘XXCUSTOMSCHEMA’,X_GRANT_TO_TABLE=>TRUE)

PL/SQL procedure successfully completed.

SQL> exec AD_ZD.revoke_privs(‘SELECT’,’AP_SUPPLIERS’,’XXCUSTOMSCHEMA’);

/* Create your custom view referencing the APPS synonym – which points to the editioned object */

create or replace force view XXCUSTOMSCHEMA.AP_SUPPLIER_NEW_V
as
SELECT *
FROM apps.ap_suppliers ;

This is the same process when creating procedures and functions with the usual caveat that unless it’s a 12c database, you cannot create materialized views on the editioned objects.

For the 11gR2 EBS users, you would stick with the existing non-editioned table object reference:

CREATE MATERIALIZED VIEW APPS.XXAP_SUPPLIER_NEW_MV
(
vendor_id,
vendor_name
)
AS
SELECT aps.vendor_id, aps.vendor_name
FROM ap.ap_suppliers aps;

/* The owning schema for the materialized view must have CREATE TABLE, VIEW AND INDEX privileges on the destination tablespace */

Advertisement