Using the Online Patching Readiness Report in Oracle E-Business Suite Release 12.2 (Doc ID 1531121.1)

This document introduces the Global Standards Compliance Checker (GSCC) and Readiness Report, and outlines how it is used to help prepare for using Oracle E-Business Suite Release 12.2 with online patching, as described in the applicable version of Oracle E-Business Suite Upgrade Guide, available in Oracle E-Business Suite Documentation Web Library Release 12.2. This document contains the following sections: Section 1: Prerequisites for Oracle E-Business Suite Online Patching Readiness and GSCC Report Patch Section 2: Using the Global Standards Compliance Checker (GSCC) and Readiness Report Section 3: Check for Data Dictionary Corruption / Time Stamp Mismatch Section 4: Run Script to Reduce Upgrade Time Section 5: Patch Readme References There is a change log at the end of this document. Section 1: Prerequisites for Oracle E-Business Suite Online Patching Readiness and GSCC Report Patch You need to start by identifying and applying the correct Online Patching Readiness and GSCC Report patch for the codelevel from which you are upgrading. Apply whichever of the following patches is relevant to your Oracle E-Business Suite release. Oracle E-Business Suite Release Online Patching Readiness and GSCC Report Patch 12.2.3 or higher Patch 31026891:R12.AD.C 12.1 Patch 31026891:R12.AD.B 12.0 Patch 31026891:R12.AD.A 11i Patch 31026891 The patch delivers a new set of Online Patching Development Standards for Oracle E-Business Suite Release 12.2, plus a set of reports that will help you analyze the dependencies between database objects as specified by the new rules used by the Edition Based Redefinition (EBR) feature that was introduced in Oracle Database Release 11gR2. You can then proceed to use the Global Standards Compliance Checker (GSCC) and Readiness Report, as described in Section 2 below. Section 2: Using the Global Standards Compliance Checker (GSCC) and Readiness Report This patch delivers scripts to automatically check that custom code complies with Oracle E-Business Suite online-patching coding standards. The implementation of online patching in Oracle E-Business Suite Release 12.2 relies on the Oracle Database 11gR2 EBR feature, and adds a new logical view over the database objects in Oracle E-Business Suite. Access to these database objects must be via the logical layer, and new coding standards help to ensure that code does this correctly. The implementation of the logical layer is such that the majority of application code already follows the new standards: however, this patch delivers scripts to scan for and identify many compliance issues that may exist. The Global Standards Compliance Checker (GSCC) delivered in this patch consists of the main engine script, $FND_TOP/bin/gscc.pl, plus a variety of enforcement code (EFC) modules in $FND_TOP/perl/GSCC/OpenEFC that check for common standards issues. Section 3: Check for Data Dictionary Corruption / Time Stamp Mismatch If if not already applied, apply the correct Online Patching Readiness and GSCC Report Patch for the codelevel from which you are upgrading. (Refer to Section 1 and Section 2 of this document.) 3.1 Check for Data Dictionary Corruption Run the $AD_TOP/patch/115/sql/adzddtsfix.sql script, following the usage instructions provided. Run the $AD_TOP/sql/ADZDDBCC.sql script to identify whether logical data dictionary corruption is present. If no corruption is found, proceed with the upgrade. If corruption is present, follow Step 3.2 below. 3.2 Fix Data Dictionary Corruption (Conditional) Note: Follow these steps only as a fix when logical data dictionary corruption (missing parent) is present. Copy the script $AD_TOP/patch/115/sql/adzddmpfix.sql to the database tier. On the database tier, connect to the database as SYSDBA. Note: If you are on Oracle E-Business Suite Release 12.1.3 or Release 12.2 with a multitenant architecture, to connect to a multitenant architecture database as SYSDBA, run the following commands to set the database SID field to , the pluggable database name for your Oracle E-Business Suite database: $ source _.env $ export ORACLE_PDB_SID=; $ sqlplus "/ as sysdba" Run the $AD_TOP/patch/115/sql/adzddmpfix.sql script. On the application tier, run the $AD_TOP/sql/ADZDDBCC.sql script again to identify whether data dictionary corruption is still present. If no corruption is found, proceed with the upgrade or patching cycle. If corruption is still present, proceed to Step 3.3 below. 3.3 Additional Actions for Data Dictionary Corruption (Conditional) Note: Follow these steps only when data dictionary corruption is present after following the steps in 3.2 above. On the database tier, go to the $ORACLE_HOME/rdbms/admin directory. Run the utlirp.sql script , following the usage instructions provided. Run the utlrp.sql script, following the usage instructions provided. On the application tier, run the $AD_TOP/sql/ADZDDBCC.sql script again to identify whether data dictionary corruption is still present. If no corruption is found, proceed with the upgrade or patching cycle. If corruption is still present, contact Oracle Support and request a bug to be logged. Section 4: Run Script to Reduce Upgrade Time To reduce the overall upgrade time, you can optionally run the ADZDMVREFRESHNEEDED.SQL script before starting the 12.2.0 upgrade driver. Section 5: Patch Readme ============================================================================== R1222: STANDALONE READINESS REPORT AND GLOBAL STANDARDS COMPLIANCE CHECKER (GSCC) ============================================================================== Update - 31026891 Product - Applications DBA Release - R12 Platform - Generic Platform Built - AUG-03-2020 06:27:15 Instructions For Applying This Patch ============================================================================== Preparation Tasks ============================================================================== The tasks in this section can be completed without taking any Applications services or users offline. There are no tasks to be performed in this section. Pre-install Tasks ============================================================================== For 12.0.X / 12.1.X / pre-upgrade patches (using adpatch), you must shut down all Application tier services before performing the tasks in this section. For 12.2.X patches (using adop), you can perform the tasks in this section without shutting down the Application tier services. There are no tasks to be performed in this section. Apply The Patch ============================================================================== 1. Apply patch This patch contains the following unified driver file to be applied with ADOP (AD Online patch): u22200487.drv (This is the unified driver) Post-install Tasks ============================================================================== For 12.0.X / 12.1.X patches, you must complete the tasks in this section before starting up Application tier services. For 12.2.X patches, you may complete the tasks in this section at any time after the update, without taking any services or users offline. There are no tasks to be performed in this section. Finishing Tasks ============================================================================== You may complete the tasks in this section at any time after the update, without taking any services or users offline. There are no tasks to be performed in this section. Additional Information ============================================================================== There are no tasks to be performed in this section. ============================================================================== Description ============================================================================== Oracle E-Business Suite Online Patching Readiness and GSCC Report Patch ======================================================================= The Oracle E-Business Suite Release 12.2 includes a capability to apply patches online. This feature relies on the Edition Based Redefinition feature of the Oracle Database introduced in Release 11gR2. This database feature added some new rules for implementing dependencies between database objects. The purpose of this patch is to deliver a set of reports that will help you analyze these dependencies as well as a new set of Online Patching Development standards. ADZDPSUM.sql Lists schemas containing objects that reference EBS objects that are not editioned. If the schemas are registered with the application, the Online Patching Enablement patch will enable editioning on those schemas avoiding many EBR rule violations (Non-Editioned objects referencing Editioned objects). This report also provides instructions on how to register your custom schemas, you are adviced to re-run the script after registering your schemas in order to reduce the number of violations in the next report. ADZDPMAN.sql Lists objects that violate EBR standards and that require manual intervention in order to address the violations. It groups the objects per violation type and provides instrutions on how to address those violations. Make sure you address all the violations in this report before proceeding with the next reports. ADZDPAUT.sql Lists objects that violate Online Patching Enablement standards. The objects in this report will be adjusted automatically by the Online Patching Enablement patch. This report is provided for information purposes only, no action is required on the output of this report. ADZDDBCC.sql Lists objects that violate Online Patching Development standards and that require manual intervention in order to address the violations. It groups the objects per violation type and provides instrutions on how to address those violations. Apply patch =========== 1. Apply E-Business Suite:Online Patching Readiness and GSCC Report patch 31026891 applicable to your EBS instance's release version 2. Once it is applied, Auto-patch will copy SQL files from patch to $AD_TOP/sql/ADZDPSUM.sql $AD_TOP/sql/ADZDPAUT.sql $AD_TOP/sql/ADZDPMAN.sql $AD_TOP/sql/ADZDDBCC.sql $AD_TOP/sql/ADZDPCUST.sql location. STEP#1: Run $AD_TOP/sql/ADZDPSUM.sql ==================================== This script should be run by SYSTEM schema of ORACLE database. For Example: sqlplus system/@DB @$AD_TOP/sql/ADZDPSUM.sql OR sqlplus system@DB @$AD_TOP/sql/ADZDPSUM.sql In latter case, sql script will prompt for SYSTEM schema password. The execution-time of this script varies from machine to machine and may take approximately 2-3 hours to complete. Note: The SQL script only queries the data dictionary, it does not query any customer tables, or perform any data dictionary updates. Make sure you review the instructions in the report, perform the recommended actions and re-run the report until you have no more pending actions. STEP#2: and STEP#3: ================== In the same way as STEP#1 has been performed for @$AD_TOP/sql/ADZDPSUM.sql, it should be repeated for @$AD_TOP/sql/ADZDPMAN.sql and @$AD_TOP/sql/ADZDPAUT.sql also STEP#4: Run ADZDDBCC.sql ======================== This script should be run by APPS schema of E-business Suite. For Example: sqlplus APPS/@DB @$AD_TOP/sql/ADZDDBCC.sql NOTE: Currently these scripts share common objects names, hence should be run sequentially. Review generated report ======================= - The ADZDPSUM.sql SQL script generates report file adzdpsum.txt. Each section of this report should be reviewed carefully and take appropriate actions. - Review adzdpman.txt for detail and follow given instructions and may review adzdpaut.txt(optional) also but these violations would be fixed automatically when your instance is enabled for Online Patching. - Review database compliance-checker report adzddbcc.lst Global Standards Compliance Checker (GSCC) ========================================== This patch delivers scripts to automatically check that custom code complies with E-Business Suite (EBS) online-patching coding standards. The implementation of Online Patching in Oracle E-Business Suite Release 12.2 relies on the Oracle Database 11gR2 Editioning feature and adds a new logical view over the database objects in Oracle E-Business Suite. Attempts to access these database objects must go through the logical layer and new coding standards help to insure that code does this correctly. The implementation of the logical layer has been done such that the majority of application code already follows the new standards; however, this patch delivers scripts to scan and identify many compliance issues if they exist. The Global Standards Compliance Checker (GSCC) delivered in this patch consists of the main engine script $FND_TOP/bin/gscc.pl and a variety of enforcement code (EFC) modules in $FND_TOP/perl/GSCC/OpenEFC that check for common standards issues. The following table lists the messages that can appear in the output of the GSCC script along with a reference to the applicable Development Standards sections which are included at the end of this file. GSCC Error Associated Coding Standard Standards Section ---------- -------------------------- ----------------- File.Sql.80 VPD Policy must be on the Editioning View or VPD Table Synonym, not the table. File.Gen.34 Table Name must not use '#' character. Table (Ordinary) File.Gen.35 Table must be owned by an EBS product schema Table (Ordinary) and not APPS. File.Gen.36 Base Column Name may only use '#' as the last Table (Ordinary) character. File.Xdf.4 Base Column name should be 28 bytes or less. Table (Ordinary) File.Gen.37 Column Type must be a built-in type or a Table (Ordinary) user-defined type owned by a non-editioned user. File.Xdf.4 Column Type must not be LONG or LONG RAW. Table (Ordinary) File.Xdf.4 Column Type should not be ROWID Table (Ordinary) File.Gen.41 Query/DML statements must access Tables via the Table (Ordinary) Table Synonym or Editioning View. File.Gen.38 Do not modify application-managed tables in an Table (Ordinary) Online Patch. File.Sql.81 Patch the table definition using ODF or XDF. Table (Ordinary) File.Gen.39 Index Name must contain an underscore ('_'). Index File.Sql.81 Deliver constraint definition using ODF or XDF. Integrity Constr. File.Sql.82 Deliver Materialized View using XDF. Materialized View Using GSCC ========== The Global Standards Compliance Checker (GSCC) delivered in this patch consists of the main, engine script $FND_TOP/bin/gscc.pl and a variety of standards enforcement code (EFC) modules in $FND_TOP/perl/GSCC/OpenEFC/ that check for common standards issues. After applying this patch to install the GSCC code, source the applications environment file and then run the GSCC on a set files under a directory tree like this: cd /home/yourdir $FND_TOP/bin/gscc.pl -f '/home/yourdir/your-code/*' In this example, gscc.pl will check all of the files located under the your-code/ directory using the EFC modules located in $FND_TOP/perl/GSCC/OpenEFC/ and generate a report named gscc-out.log in the current working directory (/home/yourdir/ in this example). Invoking gscc.pl without arguments will print a usage message with additional information. Reading GSCC Output ------------------- GSCC output looks like this example (sans line numbers): 01: --------------------------------------------------------------------------- 02: GSCC Compliance Test Results: 03: --------------------------------------------------------------------------- 04: File: /home/yourdir/your-code/yourfile.xdf 05: 06: Errors (3): 07: File.Gen.34 (Code Reviewers: CODE_REVIEWER) 08: 341 - Table Name must not use '#' character. 09: File.Gen.35 (Code Reviewers: CODE_REVIEWER) 10: 340 - Table must be owned by an EBS product schema, not APPS. 11: File.Gen.36 (Code Reviewers: CODE_REVIEWER) 12: 387, 443, 518 - Base Column Name may only use '#' as last character 13: 14: --------------------------------------------------------------------------- 15: GSCC Totals: 16: Passes: 19 17: Fails: 3 18: Warnings: 0 19: Under Review: 0 20: GSCC Errors: 0 21: 22: --* Failed GSCC Compliance Test with Code Review Failures *-- Lines 07:, 09:, and 11: in the example report name the coding standards that GSCC identified as potential problems in the file being checked (line 04:, yourfile.xdf in this example). Lines 08:, 10:, and 12: in the report then give the specific lines within yourfile.xdf (lines 341, 340, 387, 443, and 518) where potential standards compliance problems were found. To correct the errors shown in this example, the table being created on line 341 of yourfile.xdf would need to be renamed to remove the '#' character from the table name, the table would need to be created in an EBS schema other than APPS, and the table columns created on lines 387, 443, and 518 of yourfile.xdf would need to be renamed to remove the '#' character from their names. Sections of the Database Object Development Standards ----------------------------------------------------- The following sections of the Database Object Development Standards have checked by automated GSCC standards. Virtual Private Database (VPD) Policy ------------------------------------- VPD Definition Standards: VPD Policy must be on the Editioning View or Table Synonym, not the table. VPD Dynamic DDL Standards: No special considerations VPD Online Patching Compliance Standards: No special considerations. VPD Tip: You can add or drop VPD policies by calling the FND_ACCESS_CONTROL_UTIL package from a SQL script (&phase=plb). For more information on Oracle Virtual Private Database, see: Oracle Database Security Guide 11g Release 2 (11.2). Table (Ordinary) ---------------- An Ordinary Table is created/altered/dropped during application patching, In contrast, a Dynamic Table is created/altered/dropped by application runtime. The standards in this section only apply to Ordinary Tables. In order to implement effectively-editioned support for ordinary tables, the online patching technology installs and maintains a new Editioning View layer over each table. The editioning view maps logical column names used by the application to the actual storage columns used to hold those attributes in each edition. Developers must follow new procedures in order to create and alter ordinary tables in a development database, and patch those changes to other systems. Table Definition Standards: An Ordinary Table is a table that is used by Oracle E-Business Suite runtime, and patched via Online Patching Table Name must not use '#' character. (GSCC FIle.Gen.34) Table Name must be unique within the first 29 bytes. Table must be owned by an Oracle E-Business Suite product schema or custom product schema, not APPS. (GSCC File.Gen.35) Base Column Name may only use '#' as last character. (GSCC File.Gen.36) Base Column Name should be 28 bytes or fewer. (GSCC File.Xdf.4) Note: Online Patching currently does not support patching columns that violate this standard. If a violating column must be patched, then it must be replaced with compliant column (shorter name) as part of the patch. Application code will need to be updated to reference the new shorter column name. Column Type must be a built-in type or a user-defined type owned by a non-editioned user. (GSCC File.Gen.37) Column Type must not be LONG or LONG RAW. For more information on this standard, see: Oracle Database SecureFiles and Large Objects Developer's Guide 11g Release 2 (11.2). Also see: LONG to CLOB Conversion Procedures. (GSCC File.Xdf.4) Column Type should not be ROWID. (GSCC File.Xdf.4) ROWID references can become invalid if the target table is patched, loaded, or rebuilt. It is not safe to store ROWID references across an online patching boundary. Table Usage Standards: Query/DML statements must access Tables via the Table Synonym or Editioning View. (GSCC File.Gen.41) If you query, display, or store table column names in your application runtime code, you should use Logical Column Names rather than Physical Column Names in most cases. Follow the Logical versus Physical Column Guidelines for runtime application code. Warning: Some dictionary views (such as ALL_TAB_COLUMNS) contain information for both Logical and Physical table columns, depending on whether you query the editioning view or base table data. Consult Logical versus Physical Column Guidelinesfor details. Review the section Oracle E-Business Suite Objects that Reference ALL_TAB_COLUMNS or DBA_TAB_COLUMNS and ensure that your query is getting the correct logical or physical column information for your purposes. 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. Table Dynamic DDL Standards: Application-managed tables are tables that are created and maintained by application logic during normal application runtime: Application-managed (dynamic) tables must not have an Editioning View. Do not modify application-managed tables in an Online Patch. (GSCC File.Gen.38) Ordinary tables are created and maintained by Online Patching (and will have an editioning view) If the application logic modifies an ordinary table at runtime, it must use the AD_DDL interface to execute the dynamic DDL. Do not modify ordinary tables in the Run Edition while a Patch Edition exists Table Online Patching Compliance Standards: Patch the table definition using ODF or XDF. (GSCC File.Sql.81) (Automatic) The table will be covered by an editioning view of the name: substrb(table_name, 1, 29)||'#' (Automatic) The editioning view will map each logical column name to the latest version of that column. Example: Table "FND_PROFILE_OPTIONS" has an editioning view called "FND_PROFILE_OPTIONS#" Do not update existing columns in a way that is incompatible with the running application. Instead, create Revised Columns and store updated data in the Revised Columns: A Revised Column Name has the form: # Version_tag is a string of the form: [0-9A-Z]+ Version tags are compared using the SQL '>' operator. A "greater" tag is a later version. Example: Column "AMOUNT" is replaced by revised column "AMOUNT#1" Example: Column "AMOUNT#1" is replaced by revised column "AMOUNT#2" Populate a Revised Column using a Forward Cross-Edition Trigger (FCET). The FCET must be owned by APPS. The FCET name should be of the form _F. Example: "FND_PROFILE_OPTIONS_F3" The FCET must be created with the "disable" option. The FCET for the second and subsequent change to a given table must specify that it "follows " in the trigger definition. For information on the FOLLOW clause, see: Oracle Database PL/SQL Language Reference. The FCET must be created using a DBDRV command that executes the create script in phase=ccet. The FCET must be applied using a DBDRV command that calls AD_ZD_TABLE_APPLY . in phase=ccet. Tip: Use the Forward Cross-Edition Trigger template. Do not drop an existing table until the Cleanup phase of patch execution. For an example of a deferred drop, see: Code Example of a Deferred Drop. Do not drop an existing column. Columns that are replaced by new revised columns will be dropped automatically at cleanup. Dropping a logical column is not supported. Do not rename an existing table. For more information on guidelines for managing tables, see: Oracle Database Administrator's Guide 11g Release 2 (11.2).For more information on using XDF (XML Definition File) features, see: XML Definition File Utility. Index ----- Index Definition Standards: Index Name must contain an underscore ('_'). (GSCC File.Gen.39) The Unique Index on a Seed Data Table must include ZD_EDITION_NAME. Note: This will be implemented automatically when you call "ad_zd_seed.upgrade" on your seed data table, but if you add a new unique index to an existing seed data table you will need to include the ZD_EDITION_NAME column in your index definition. The Unique Index on a Seed Data Table should have at least one not-null column besides ZD_EDITION_NAME. Note: If the unique index has all nullable columns, then we expect each row in the table to have at least one non-null column value for the indexed columns. You must ensure that this is true as part of the Oracle E-Business Suite 12.2 upgrade (select rows where all indexed columns are null and either delete or update as needed to meet this standard). Index Key Length should be less than 3125 bytes. Index Key Length is the sum of the column lengths for each column in the index, plus one byte for each column. If the Index Key Length is greater than 3125 bytes, then the index cannot be revised using "online" index definition, and a full table lock will be held during index revision. A function-based index must not reference editioned Oracle E-Business Suite objects (built-in database functions such as "UPPER()" are acceptable). Index Dynamic DDL Standards: "CREATE INDEX ... ON ..." statement must specify the fully qualified table name, not the APPS table synonym. Good: create index SOME_TABLE_N1 on SCHEMA.SOME_TABLE ... Bad: create index SOME_TABLE_N1 on SOME_TABLE ... Index Online Patching Compliance Standards: Deliver the index definition using ODF or XDF. (Automatic) New or revised indexes will be initially created disabled and with an alternate name. These indexes will be enabled and renamed during cutover processing. Do not drop an existing index until the Cleanup phase of patch execution. For an example of a deferred drop, see: Code Example of a Deferred Drop. For more information on indexes, see: Oracle Database Concepts 11g Release 2 (11.2). For more information on using XDF (XML Definition File) features, see: XML Definition File Utility. Integrity Constraint -------------------- Integrity Constraint Definition Standards: Constraint Name must contain an underscore ('_'). Foreign Key Constraint should not be created, and must not reference a Seed Data Table. Integrity Constraint Dynamic DDL Standards: If you dynamically create a primary or unique constraint against a Seed Data Table, the constraint must include the ZD_EDITION_NAME column. Integrity Constraint Online Patching Compliance Standards: Deliver constraint definition using ODF or XDF. (GSCC File.Sql.81) New or revised constraints will be initially created as disabled, and will be enabled during Cutover. For more information on integrity constraints, see: Oracle Database Concepts 11g Release 2 (11.2). For more information on using XDF (XML Definition File) features, see: XML Definition File Utility. Materialized View (MV) ---------------------- The Materialized View is a non-editioned object type, and therefore a materialized view cannot directly reference editioned objects. To avoid this limitation, Oracle E-Business Suite Online Patching technology implements a new Effectively Editioned Materialized View compound object. Application developers create and maintain the Materialized View Definition (query) in an ordinary view. The Online Patching technology then automatically maintains a corresponding Materialized View Implementation that is legal for editioned databases. MV Definition Standards: A Materialized View Name must be unique within the first 29 bytes. A Materialized View Definition must be stored in an ordinary view called MV_NAME||'#'. Create or replace the Materialized View Definition as an ordinary view calledmv_name||'#'. Test the MV Definition for accuracy before generating the MV Implementation. For example: create or replace view FND_EXAMPLE_MV# as select ... ; select * from fnd_example_mv#; The Materialized View Implementation is automatically generated from the MV Definition using the AD_ZD_MVIEW.UPGRADE procedure. The syntax is exec ad_zd_mview.upgrade(, ) Do not attempt to directly create or replace the Materialized View Implementation Query. To recreate an MV Implementation, call the AD_ZD_MVIEW.UPGRADE procedure. A Materialized View Definition must specify a column alias for each item in the select list. Failure to specify a column alias may cause the error ORA-00998 "must name this expression with a column alias" Example: change select sum(EMP.SALARY), ... to select sum(EMP.SALARY) SUM_EMP_SALARY, ... A Materialized View Query must not reference editioned PL/SQL functions. If the MV definition references an editioned PL/SQL function, the MV Implementation will fail to generate and the MV will be unusable. For examples of replacing PL/SQL function calls with equivalent SQL in Materialized Views, see: Examples of SQL Replacements for PL/SQL Functions A Materialized View should use 'REFRESH FORCE' instead of 'REFRESH FAST'. The 'FORCE' option allows the MV to fall back to using a complete refresh in situations where the fast refresh is not possible. See: Oracle Database SQL Language Reference 11g Release 2 (11.2) for more information on the "REFRESH FORCE" option. If the MV Implementation content must be automatically refreshed after patching, include the '/*AUTOREFRESH*/' comment tag in the MV Definition query. Do not specify the /*AUTOREFRESH*/ tag for large MVs that will take a long time to refresh. For these cases use a concurrent program to refresh the MV after patching cutover. Example: create or replace view FND_EXAMPLE_MV# as select /*AUTOREFRESH*/ ... ; MV Usage Standards: Do not assume that Fast Refresh is always possible. After an online patch, Complete Refresh may be required. When refreshing a Materialized View, us the 'FORCE' clause instead of 'FAST'. See: Oracle Database SQL Language Reference 11g Release 2 (11.2) for more information on the 'FORCE' option. MV Dynamic DDL Standards: Use AD_MV to execute Dynamic DDL for materialized views. Here is an example of creating a materialized view using the AD_MV package: -- -- Code Example: Create a materialized view using AD_MV interface. -- -- Note: -- when executed in the Run Edition, the MV is created immediately. -- when executed in the Patch Edition, the MV is generated at CUTOVER. -- begin -- Create MV ad_mv.create_mv('FND_EXAMPLE_MV', 'create materialized view FND_EXAMPLE_MV '|| ' tablespace '||ad_mv.g_mv_data_tablespace||' '|| ' build deferred refresh on demand as '|| 'select /*AUTOREFRESH*/ '|| ' upper(oracle_username) USERNAME '|| ' , decode(read_only_flag,''C'',''pub'',''E'',''applsys'',''U'',''apps'') USERTYPE '|| 'from fnd_oracle_userid '|| 'where read_only_flag in (''C'',''E'',''U'') '); end; -- End of Code Example. MV Online Patching Compliance Standards: Deliver Materialized View using XDF. (GSCC File.Sql.82) Do not drop an existing materialized view until the Cleanup phase of patch execution. Do not attempt to upgrade, refresh or access a Materialized View Implementation in the Patch Edition. Although the MV implementation is visible to the Patch Edition, it continues to implement the Run Edition of the definition until the cutover phase. MV implementations are automatically regenerated as needed at the cutover phase. If an online patch must manually refresh the MV Implementation contents, submit a concurrent request to do the refresh. The concurrent request will be run after cutover and therefore after the MV implementation has been regenerated. If the MV Definition specifies the /*AUTOREFRESH*/ comment tag, then the MV Contents will be automatically refreshed whenever the MV implementation is regenerated. Do not drop an obsolete materialized view until the Cleanup phase of patch execution. For more information on materialized views, see: Oracle Database Concepts 11g Release 2 (11.2). For more information on using XDF (XML Definition File) features, see: XML Definition File Utility. Examples of SQL Replacements for PL/SQL Functions ------------------------------------------------- To "Editions-enable" the APPS schema, non-Editionable objects must not depend on Editionable objects (NE !-> E). To meet this requirement, the database object development standards specify that Materialized Views (Materialized Views, or MVs, are non-Editionable) must not call PL/SQL functions (which are Editionable). The examples below demonstrate how to replace frequently- used Oracle Applications Technology PL/SQL function calls with an equivalent SQL in Materialized Views. You may continue to call built-in PL/SQL functions such as "upper()". fnd_profile.value() replaced with a SQL sub-select: Before: fnd_profile.value('MSC_HUB_REGION_INSTANCE') After: (select profile_option_value from fnd_profile_option_values where level_id = 10001 and (profile_option_id, application_id) = (select profile_option_id, application_id from fnd_profile_options where profile_option_name = 'MSC_HUB_REGION_INSTANCE')) Notes: This replacement is valid ONLY in a Materialized View. For other uses of fnd_profile.value(), continue using the normal PL/SQL call. The general case for fetching profile option values is very complex, that is why there is a PL/SQL package dedicated to doing it. But materialized views results have to be valid in any context, so profile options referenced in materialized views should only have site-level values, and the replacement SQL only needs to support fetching the site level value. This replacement SQL will only use the profile option value set at the site level. fnd_message.get_string() replaced with a SQL sub-select Before: fnd_message.get_string('MSC','MSC_HUB_UNASSIGNED') After: (select substrb(REPLACE(message_text, '&&', '&'),1,2000) from fnd_new_messages m, fnd_application a where m.message_name = 'MSC_HUB_UNASSIGNED' and m.language_code = 'US' and a.application_short_name = 'MSC' and m.application_id = a.application_id) Notes: This replacement is valid ONLY in a Materialized View. For other uses of fnd_message.get_string(), continue using the normal PL/SQL call. This replacement SQL will only retrieve the US language message text and is not sensitive to any session language settings. MV queries cannot contain a sub-SELECT within the main SELECT clause; therefore, the replacement SQL is a bit trickier if the function call was used in the MV SELECT clause. Before: select fnd_message.get_string('FND', 'CANCEL') from dual where 1=1 / After: select fmgs.result from dual , (select substrb(REPLACE(message_text, '&&', '&'),1,2000) result from fnd_new_messages m, fnd_application a where m.message_name = 'CANCEL' and m.language_code = 'US' and a.application_short_name = 'FND' and m.application_id = a.application_id) fmgs where 1=1 / fnd_global.lookup_security_group() replaced with a SQL sub-select Before: fnd_global.lookup_security_group('INTEREST_STATUS', 279) After: (select nvl(max(lt.security_group_id), 0) from fnd_lookup_types lt where lt.view_application_id = 279 and lt.lookup_type = 'INTEREST_STATUS' and lt.security_group_id in ( 0, to_number(decode(substrb(userenv('CLIENT_INFO'),55,1), ' ', '0', null, '0', substrb(userenv('CLIENT_INFO'),55,10))))) Note: This replacement is valid ONLY in a Materialized View. For other uses of fnd_global.security_group(), continue using the normal PL/SQL call. Example of a Deferred Drop Here is a code example of a deferred drop. This example is for a table: REM dbdrv: sql ~PROD ~PATH ~FILE \ REM dbdrv: none none none sqlplus &phase=last \ REM dbdrv: checkfile:~PROD:~PATH:~FILE &un_fnd -- Example logic to drop a table under online patching -- -- Note: This script drops example table "APPLSYS.FND_EXAMPLE_TABLE". -- To use this logic for another table, you must substitute "&un_fnd" with the -- actual table owner token ("&un_"), and -- "FND_EXAMPLE_TABLE" with the actual table name. -- -- Usage -- @TABLE_DROP_SCRIPT drop synonym FND_EXAMPLE_TABLE; drop view &1..FND_EXAMPLE_TABLE#; exec ad_zd.load_ddl('CLEANUP', 'drop table &1..FND_EXAMPLE_TABLE') The contents of the Readme (included with the patch) are as follows: References For more information on the operation of Oracle E-Business Suite Online Patching, refer to Chapter 3, Patching Procedures, in Oracle E-Business Suite Maintenance Guide.