Create ADI by using TABLE

Create ADI by using TABLE

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

--Create TABLE

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

CREATE TABLE XXSD_TABLE_TYPE_ADI_TL

(

 EMPLOYEE_NUMBER VARCHAR2(100)

,FIRST_NAME VARCHAR2(100)

,LAST_NAME VARCHAR2(100)

,DATE_OF_BIRTH DATE

,GRADE VARCHAR2(10)

)

select * from XXSD_TABLE_TYPE_ADI_TL

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

--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_TABLE_TYPE_ADI_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 A

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

    AND not exists (select 1 from FND_COLUMNS Z where Z.COLUMN_NAME = A.COLUMN_NAME and Z.TABLE_ID = ( select table_id from fnd_tables where table_name = 'XXSD_TABLE_TYPE_ADI_TL') );

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;

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