Creating and maintaining Custom Tables in R12.2.x

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');