How to create Apps Read Only (APPSRO) in EBS R12.2

Follow these steps to create APPS read only custom schema APPSRO in EBS R12.2:

1) Start an online patching cycle

. /u01/oracle/PROD/EBSapps.env run
adop phase=prepare

2) Connect as sysdba and create the database user to be used for apps read only schema

$ sqlplus "/ as sysdba"

SQL > create user APPSRO identified by appsro default tablespace APPS_TS_TX_DATA;
SQL> grant connect, resource to appsro;
SQL> grant create synonym to appsro;
SQL> exit;

3.1) Connect to the patch edition (source EBSapps.env patch)

. /u01/oracle/PROD/EBSapps.env patch
sqlplus apps/<apps_password>

3.2) Connect as APPS user and run the SQL commands

$ sqlplus apps/<apps_password>

SQL>set head off
SQL> set newpage none
SQL> set pagesize 9999

SQL> spool create_synonyms.sql
SQL> select 'create synonym ' || OBJECT_NAME || ' for ' || OWNER ||'.' ||OBJECT_NAME || ';' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');

SQL> spool off

SQL> spool grant_privs.sql
SQL> select 'exec AD_ZD.grant_privs (''SELECT'''||',' ||''''||OBJECT_NAME||''',' || ' ''APPSRO'');' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');
SQL> spool off

SQL> exit;

Confirm both the Spool files: create_synonyms.sql and grant_privs.sql are created.

3.3) Run create_synonym.sql script to create synonyms:

$ sqlplus "/as sysdba"

SQL> @create_synonyms.sql
SQL> @grant_privs.sql
SQL> exit;

4) The custom schema can be registered with the following APIs:

If Online Patching Enablement is not yet enabled (as suggested in ADZDPCUST.sql):
SQL> exec FND_ORACLE_USER_PKG.LOAD_ROW('CUSTOM_SCHEMA', 'CUSTOM', 'INVALID', NULL, 'N', 'B');

If Online Patching Enablement has already been enabled:
sqlplus SYSTEM
SQL> alter session set current_schema=APPS;
SQL> exec AD_ZD_PREP.ENABLE_CUSTOM_USER('CUSTOM_SCHEMA');

Example:
SQL> alter session set current_schema=APPS;
SQL> exec AD_ZD_PREP.ENABLE_CUSTOM_USER('APPSRO');

5) Create Synonyms for APPSRO user:

$ sqlplus appsro/appsro
SQL> @create_synonyms.sql
SQL> exit;

6) Complete the patching cycle (finalize, cutover, cleanup)

Test it in TEST environment before doing in Production environment