1. To create a custom table in custom schema – XXCUSTOM
CREATE TABLE XXCUSTOM.TABLE_NAME (COL1 NUMBER,….);
2. To generate editioning view and synonym for the table execute below script
exec AD_ZD_TABLE.UPGRADE('XXCUSTOM','TABLE_NAME');
This will create two new objects:
(i) An editioned view (having # in the end) in XXCUSTOM schema (e.g. XXCUSTOM.TABLE_NAME#)
(ii) A synonym (same as table_name) in APPS schema (APPS.TABLE_NAME)
3. If you alter the table definition in future then after running the alter table command, run below script to regenerate the editioning view and sync the table changes -
exec AD_ZD_TABLE.PATCH('XXCUSTOM','TABLE_NAME');
4. To see the objects across all editions, please query all_objects_ae or user_objects_ae
SELECT * FROM all_objects_ae WHERE OBJECT_NAME like 'TABLE_NAME%';
5. To issue Grants/Revokes use below commands (Please request DBA team to execute below commands):
Connect to XXCUSTOM schema and run below commands -
grant <grant> on XXCUSTOM.TABLE_NAME to APPS WITH GRANT OPTION;
grant <grant> on XXCUSTOM.TABLE_NAME# to APPS WITH GRANT OPTION;
Connect to APPS and run below commands to perform Grant or Revoke operations –
execute APPS.AD_ZD.GRANT_PRIVS('<grant>','TABLE_NAME','<SCHEMA_RECEIVING_GRANT');
execute
APPS.AD_ZD.REVOKE_PRIVS('<grant>', 'TABLE_NAME','SCHEMA_TOBE_REVOKED');