Friday, December 11, 2020

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


Share: