CREATE OR REPLACE
PROCEDURE XX_TABLE_REGISTRATION_PROC(
Errbuf OUT VARCHAR2,
Retcode OUT VARCHAR2,
l_appl_short_name IN VARCHAR2,
l_tab_name IN VARCHAR2)
IS
v_appl_short_name VARCHAR2 (50) := l_appl_short_name;
v_tab_name VARCHAR2 (30) := l_tab_name;
v_tab_type VARCHAR2 (30) := 'T'; --object type T for table
v_next_extent NUMBER := 514;
v_pct_free NUMBER;
v_pct_used NUMBER;
BEGIN
-- Unregister the custom
table if it exists
ad_dd.delete_table (p_appl_short_name => l_appl_short_name, p_tab_name => l_tab_name);
-- Register the custom table
FOR tab_details IN
(
SELECT
table_name,
tablespace_name,
pct_free,
pct_used,
ini_trans,
max_trans,
initial_extent,
next_extent
FROM
dba_tables
WHERE
table_name = v_tab_name
)
LOOP
AD_DD.REGISTER_TABLE
(P_APPL_SHORT_NAME => V_APPL_SHORT_NAME,
P_TAB_NAME => TAB_DETAILS.TABLE_NAME,
P_TAB_TYPE => V_TAB_TYPE,
P_NEXT_EXTENT => NVL (TAB_DETAILS.NEXT_EXTENT, 514),
P_PCT_FREE => NVL (TAB_DETAILS.PCT_FREE, 10),
P_PCT_USED => NVL (TAB_DETAILS.PCT_USED, 70)
);
END LOOP;
-- Register the columns of
custom table
FOR all_tab_cols IN
(
SELECT
column_name,
column_id,
data_type,
data_length,
nullable
FROM
all_tab_columns
WHERE
table_name = v_tab_name
)
LOOP
ad_dd.register_column
(p_appl_short_name => v_appl_short_name,
P_TAB_NAME => V_TAB_NAME,
P_COL_NAME => ALL_TAB_COLS.COLUMN_NAME,
P_COL_SEQ => ALL_TAB_COLS.COLUMN_ID,
P_COL_TYPE => ALL_TAB_COLS.DATA_TYPE,
P_COL_WIDTH => ALL_TAB_COLS.DATA_LENGTH,
P_NULLABLE => ALL_TAB_COLS.NULLABLE,
P_TRANSLATE => 'N',
P_PRECISION => NULL,
P_SCALE => NULL
);
END LOOP;
---Registration of primary
key
FOR all_keys IN
(
SELECT
constraint_name,
table_name,
constraint_type
FROM
all_constraints
WHERE
constraint_type = 'P'
AND table_name = v_tab_name
)
LOOP
ad_dd.register_primary_key
(P_APPL_SHORT_NAME => V_APPL_SHORT_NAME,
P_KEY_NAME => ALL_KEYS.CONSTRAINT_NAME,
P_TAB_NAME => ALL_KEYS.TABLE_NAME,
P_DESCRIPTION => 'Register primary key',
P_KEY_TYPE => 'S', --PK Type Surrogate
P_AUDIT_FLAG => 'N',
P_ENABLED_FLAG => 'Y'
);
FOR all_columns IN
(
SELECT
column_name,
POSITION
FROM
dba_cons_columns
WHERE
table_name = all_keys.table_name
AND constraint_name = all_keys.constraint_name
)
LOOP
AD_DD.REGISTER_PRIMARY_KEY_COLUMN
(P_APPL_SHORT_NAME => V_APPL_SHORT_NAME,
P_KEY_NAME =>
ALL_KEYS.CONSTRAINT_NAME,
P_TAB_NAME =>
ALL_KEYS.TABLE_NAME,
P_COL_NAME =>
ALL_COLUMNS.COLUMN_NAME,
P_COL_SEQUENCE =>
ALL_COLUMNS.POSITION
);
END LOOP;
END LOOP;
Fnd_File.Put_line(Fnd_File.Output ,'Table'||' '||v_tab_name||' '|| 'Registered successfully in' ||' '||v_appl_short_name||' '||'Schema');
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG ,'Error Occured during table
registration');
END;
No comments:
Post a Comment