Register Table in Oracle Apps

Register Table in Oracle Apps

--===================================================
select * from XXSD.XXSD_EVENT_BASE_ALERT_TL

drop table XXSD.XXSD_EVENT_BASE_ALERT_TL

--===================================
Create Table
--===================================

create table XXSD.XXSD_EVENT_BASE_ALERT_TL
( HDR_ID NUMBER,
 NAME  VARCHAR2(100),
 EMP_NO VARCHAR2(100),
 GRADE VARCHAR(2),
 DOB DATE
 )

--===================================
Check Table
--===================================

 select * from XXSD.XXSD_EVENT_BASE_ALERT_TL

--===================================
Check  Table is created in Database
--===================================

 select * from all_objects
 where object_name = 'XXSD_EVENT_BASE_ALERT_TL'


--===================================
Check Table is register or Not
--===================================

 select * from fnd_tables
 where table_name = 'XXSD_EVENT_BASE_ALERT_TL'


--===================================
Check Table Columns are register or Not
--===================================

 select * from fnd_columns
 where table_id = ( select table_id from fnd_tables
 where table_name = 'XXSD_EVENT_BASE_ALERT_TL')

--=================================================================

LAB

--=================================================================

------------------------------------------------------------------------------

--REGISTER TABLE-- 

------------------------------------------------------------------------------

DECLARE 

CURSOR C1 IS 

SELECT (select APPLICATION_SHORT_NAME from fnd_application where APPLICATION_ID  = 

(select APPLICATION_ID from fnd_application_tl where APPLICATION_NAME = 'XXSD Custom Application')) APP_SHORT_NAME,

A.OBJECT_NAME, 'T' TABLE_T,  A.OWNER

FROM DBA_OBJECTS A

WHERE 1=1

AND TRIM(A.OBJECT_NAME) NOT IN (SELECT TABLE_NAME FROM FND_TABLES) 

AND A.OBJECT_NAME LIKE 'XXSD_EVENT_BASE_ALERT_TL'

--AND A.OBJECT_TYPE NOT IN ('SYNONYM') 

;

BEGIN 

    FOR I IN C1 LOOP

        AD_DD.REGISTER_TABLE (I.APP_SHORT_NAME,I.OBJECT_NAME,I.TABLE_T);

    END LOOP;

END; 

------------------------------------------------------------------------------

--REGISTER_COLUMN--

------------------------------------------------------------------------------

DECLARE 

CURSOR C1 IS 

    SELECT (SELECT APPLICATION_SHORT_NAME FROM FND_APPLICATION WHERE APPLICATION_ID  = 

    (SELECT APPLICATION_ID FROM FND_APPLICATION_TL WHERE APPLICATION_NAME = 'XXSD Custom Application')) APP_SHORT_NAME,

   TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,COLUMN_ID,NULLABLE,OWNER 

    FROM DBA_TAB_COLUMNS

    WHERE TABLE_NAME IN ( SELECT OBJECT_NAME FROM DBA_OBJECTS A WHERE A.OBJECT_NAME LIKE 'XXSD_EVENT_BASE_ALERT_TL') 

    AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM FND_TABLES WHERE TABLE_ID  IN (SELECT TABLE_ID FROM FND_COLUMNS));

    

BEGIN

    FOR I IN C1 LOOP

        AD_DD.REGISTER_COLUMN (I.APP_SHORT_NAME, I.TABLE_NAME,I.COLUMN_NAME, I.COLUMN_ID, I.DATA_TYPE, I.DATA_LENGTH, 'N', 'N'); 

         

    END LOOP;

END; 

------------------------------------------------------------------------------

--REGISTER PRIMARY KEY and Primary Column -- 

------------------------------------------------------------------------------

DECLARE 

CURSOR C1 IS 

    SELECT (SELECT APPLICATION_SHORT_NAME FROM FND_APPLICATION WHERE APPLICATION_ID  = 

    (SELECT APPLICATION_ID FROM FND_APPLICATION_TL WHERE APPLICATION_NAME = 'XXSD Custom Application')) APP_SHORT_NAME,

    TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,COLUMN_ID,NULLABLE,OWNER 

    FROM DBA_TAB_COLUMNS

    WHERE TABLE_NAME IN ( SELECT OBJECT_NAME FROM DBA_OBJECTS A WHERE A.OBJECT_NAME LIKE 'XXSD_EVENT_BASE_ALERT_TL') 

    AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM FND_TABLES WHERE TABLE_ID  IN (SELECT TABLE_ID FROM FND_PRIMARY_KEYS))

    AND COLUMN_NAME = 'HDR_ID'

    ;

BEGIN 

    FOR I IN C1 LOOP

        AD_DD.REGISTER_PRIMARY_KEY(I.APP_SHORT_NAME,I.COLUMN_NAME,I.TABLE_NAME,I.DATA_TYPE,'S','Y','Y'); 

        AD_DD.REGISTER_PRIMARY_KEY_COLUMN(I.APP_SHORT_NAME, I.COLUMN_NAME, I.TABLE_NAME, I.COLUMN_NAME, 1);

    END LOOP;

END;