Tag Archives: custom

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.
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;

/* 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 */

Advertisements

R12.2 Upgrade – Registering New Application Modules – r122_new_custsch.ksh

Note: This script is based upon an OEL 5.9 environment with a completed first-stage R12.2 e-Business Suite upgrade from 12.1.3. Using a pre-existing registered module (ZFA – Oracle Financial Analyzer in our case), this clones the settings required to fulfill the R12.2 upgrade pre-requisite check for additonal applications, which had been added since the 12.1.3 release.) It is referenced in the primary 12.2.3 Upgrade Post and fully listed herein.

cat /mnt/nfs/config/r122_new_custsch.ksh

#!/bin/ksh
# Create new Custom Schema registration files
# Use r122_new_custsch.ksh <APP> <APPID> <APPL_NAME>
# e.g. r122_new_custsch.ksh ABC 123 My_New_App
if ! [[ ${#} = 3 ]]; then
echo -e “\n Use ${0} <APP> <APPID> <APPL_NAME>\n”
echo -e “e.g. r122_new_custsch.ksh ABC 123 My_New_App\n”
exit
fi
if [[ $RUN_BASE = ” ]]; then
echo -e “\n RUN_BASE not set! Cannot continue. Exiting…\n”
exit
fi
echo -e “\n RUN_BASE: ” $RUN_BASE
cd $RUN_BASE/EBSapps/appl/admin/
OLDAPPL_TOP=/ptcharmk/apps/apps_st/appl
NEWAPPL=${1}
NEWAPPL_LC=`echo ${NEWAPPL} | tr ‘[A-Z]’ ‘[a-z]’`
NEWAPPLID=${2}
NEWAPPL_NAME=${3}
echo -e “\nCreating new ${NEWAPPL_LC}prod.txt and ${NEWAPPL_LC}terr.txt”
echo -e “for Application ${NEWAPPL} which is APPLICATION_ID = ${NEWAPPLID}\n”
cp zfaprod.txt ${NEWAPPL_LC}prod.txt
cp zfaterr.txt ${NEWAPPL_LC}terr.txt
echo -e “Creating driver directories…\n”
mkdir -p $RUN_BASE/EBSapps/appl/${NEWAPPL_LC}/12.0.0/admin/driver
mkdir -p $RUN_BASE/EBSapps/appl/${NEWAPPL_LC}/12.0.0/admin/sql
mkdir -p $RUN_BASE/EBSapps/appl/${NEWAPPL_LC}/12.0.0/sql
echo -e “Copying dummy driver files…\n”
cp $OLDAPPL_TOP/xxad/12.0.0/admin/driver/xxadfile.drv \
$RUN_BASE/EBSapps/appl/${NEWAPPL_LC}/12.0.0/admin/driver/${NEWAPPL_LC}file.drv
cp $OLDAPPL_TOP/xxad/12.0.0/sql/XXADNLINS.sql \
$RUN_BASE/EBSapps/appl/${NEWAPPL_LC}/12.0.0/sql/${NEWAPPL}NLINS.sql
cp $OLDAPPL_TOP/xxad/12.0.0/admin/sql/XXADNLADD.sql \
$RUN_BASE/EBSapps/appl/${NEWAPPL_LC}/12.0.0/admin/sql/${NEWAPPL}NLADD.sql
echo -e “Replacing key values…\n”
perl -p -i.bak -e “s:zfa:${NEWAPPL_LC}:g” $RUN_BASE/EBSapps/appl/admin/${NEWAPPL_LC}prod.txt
perl -p -i.bak -e “s:ZFA:${NEWAPPL}:g” $RUN_BASE/EBSapps/appl/admin/${NEWAPPL_LC}prod.txt
perl -p -i.bak -e “s:505:${NEWAPPLID}:g” $RUN_BASE/EBSapps/appl/admin/${NEWAPPL_LC}prod.txt
perl -p -i.bak -e “s:zfa:${NEWAPPL_LC}:g” $RUN_BASE/EBSapps/appl/admin/${NEWAPPL_LC}terr.txt
perl -p -i.bak -e “s:ZFA:${NEWAPPL}:g” $RUN_BASE/EBSapps/appl/admin/${NEWAPPL_LC}terr.txt
perl -p -i.bak -e “s:505:${NEWAPPLID}:g” $RUN_BASE/EBSapps/appl/admin/${NEWAPPL_LC}terr.txt
perl -p -i.bak -e “s:Oracle_Financial_Analyzer:${NEWAPPL_NAME}:g” \
$RUN_BASE/EBSapps/appl/admin/${NEWAPPL_LC}terr.txt
echo -e “\nFinished creating new ${NEWAPPL_LC}prod.txt and ${NEWAPPL_LC}terr.txt\n”
# echo -e “Contents ${NEWAPPL_LC}prod.txt:\n”
# cat ${NEWAPPL_LC}prod.txt
# echo -e “Contents ${NEWAPPL_LC}terr.txt:\n”
# cat ${NEWAPPL_LC}terr.txt