dba_editions/patch editions

Actualize the old database editions after online patching to avoid possible performance issues:

During Online patching (ADOP) : An additional column ZD_EDITION_NAME is populated in the seed tables.This happens during th prepare phase.
Online patching doesn't modify run-time seed data by the use of editioned data storage. Creating a (patch) copy of the seed data

Every time we perform online patching , there will an old database edition entry and this will accumulate as and when we do more online patching's.
Oracle suggests that we perform actualize_all after this reaches a count of 25. However it would be time consuming to perform the cleanup after the count has increased.

select count(1) from dba_editions
we also check it ZD_EDITION_NAME in -- FND_PROFILE_OPTION_VALUES

If the number of these grows too large, system performance will start to be affected. When the number of old database editions reaches 25 or more, you should consider dropping all old database editions by running the adop actualize_all phase and then performing a full cleanup.


Steps to remove the editons/full cleanup mode

$ adop phase=prepare
$ adop phase=actualize_all
$ adop phase=finalize finalize_mode=full
$ adop phase=cutover mtrestart=no ?(Manual restart) or adop phase=cutover
$ adop phase=cleanup cleanup_mode=full

Scripts required for 12.2.X custom

1) For new table creations - after creating the table in custom schema execute below script to generate editoning view and synonym for it in APPS schema.

exec AD_ZD_TABLE.UPGRADE('XXCUST','XX_CUST_TBL1');

2) For table alteratuons - after running the DDL run below script to regenerate the editioning view for syncing any table changes.

exec AD_ZD_TABLE.PATCH('XXCUST','XX_CUST_TBL1')

3) Table grants must be done through API to avoid invalids.

exec AD_ZD.GRANT_PRIVS('SELECT','XX_CUST_TBL1','XX_ROLE0')

4) To view objects in all editions..add _ae to the data dictionary views

select * from user_objects_ae;

5) To update seed data tables in Patch edition, execute prepare command.

example:
exec ad_zd_seed.prepare('WF_MESSAGES');

6)exec AD_ZD.compile ('') -- to compile the objects

The prepare will create a edition based storage for the run edition data for which the updates will be made. During cutover, the run time data will be synced using forward/reverse cross edition triggers.

How to Add a Column:

Altering a table – Adding Columns and Indexes

1. Alter table in custom schema the usual way.
alter table cust_table add cust_info varchar2(1) NOT NULL;

Regenerate the editioning view using AD_ZD_TABLE.PATCH
SQL> exec ad_zd_table.patch(table_owner, table_name);

NB: Once the table is altered with the table name, “exec ad_zd_table.patch(‘XXSDS’, ‘cust_table’)” needs to be applied every time

Then:
exec ad_zd_table.upgrade(table_owner, table_name);

RUN EBS Online Patching Database Compliance Checker"(ADZDDBCC.sql)

file location $AD_TOP/sql

for reference check
https://smartdogservices.com/navigating-the-new-waters-of-r12-2-development-customization-part-2/