The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*), application_id into cnt, p_apps_id
from fnd_application
where application_short_name = upper(p_apps_short_name)
group by application_id;
select table_id into p_table_id
from fnd_tables
where application_id = p_apps_id
and table_name = p_tab_name;
select column_id into l_col_id
from fnd_columns
where application_id = p_appl_id
and table_id = p_table_id
and column_name = p_col_name;
select count(*)
into l_tmp
from fnd_primary_keys
where application_id = p_appl_id
and table_id = p_table_id
and primary_key_name <> upper(p_key_name)
and primary_key_type = 'D';
select primary_key_id
into l_key_id
from fnd_primary_keys
where application_id = p_appl_id
and table_id = p_table_id
and primary_key_name = upper(p_key_name);
procedure insert_update_primary_key
(p_mode in varchar2,
p_appl_short_name in varchar2,
p_key_name in varchar2,
p_tab_name in varchar2,
p_description in varchar2,
p_enabled_flag in varchar2,
p_key_type in varchar2,
p_audit_flag in varchar2)
is
l_table_id number := null;
if (p_mode = 'INSERT' and l_key_id is not null) then
return;
elsif (p_mode = 'UPDATE' and l_key_id is null) then
raise_application_error(-20000,
'Cannot find the key_id for key: "'||p_key_name||'" for table "'||
p_tab_name||'" with application_short_name "'||
p_appl_short_name||'"');
if ((p_mode = 'INSERT' and p_key_type not in ('S', 'D')) or
(p_mode = 'UPDATE' and nvl(p_key_type, 'S') not in ('S', 'D'))) then
raise_application_error(-20000, 'Invalid value for primary key type : '||
p_key_type);
if ((p_mode = 'INSERT' and p_audit_flag not in ('Y', 'N')) or
(p_mode = 'UPDATE' and nvl(p_audit_flag, 'Y') not in ('Y', 'N'))) then
raise_application_error(-20000, 'Invalid value for audit flag : '||
p_audit_flag);
if ((p_enabled_flag = 'INSERT' and p_enabled_flag not in ('Y', 'N'))
or
(p_enabled_flag = 'UPDATE' and nvl(p_enabled_flag,'Y') not in ('Y','N')))
then
raise_application_error(-20000, 'Invalid value for enabled flag : '||
p_enabled_flag);
if (p_mode = 'INSERT' and l_key_id is null) then
insert into fnd_primary_keys (
APPLICATION_ID,
TABLE_ID,
PRIMARY_KEY_ID,
PRIMARY_KEY_NAME,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN,
PRIMARY_KEY_TYPE,
AUDIT_KEY_FLAG,
DESCRIPTION,
ENABLED_FLAG)
select l_appl_id,
l_table_id,
fnd_primary_keys_s.nextval,
p_key_name,
to_date('01/01/1990', 'DD/MM/YYYY'), 1,
to_date('01/01/1990', 'DD/MM/YYYY'), 1,
0,
p_key_type,
p_audit_flag,
p_description,
p_enabled_flag
from dual
where not exists (
select 'x'
from fnd_primary_keys
where application_id = l_appl_id
and table_id = l_table_id
and primary_key_name = upper(p_key_name));
elsif (p_mode = 'UPDATE' and l_key_id is not null) then
update fnd_primary_keys
set primary_key_type = nvl(p_key_type, primary_key_type),
audit_key_flag = nvl(p_audit_flag, audit_key_flag),
description = nvl(p_description, description),
enabled_flag = nvl(p_enabled_flag, enabled_flag)
where application_id = l_appl_id
and table_id = l_table_id
and primary_key_id = l_key_id;
insert into fnd_tables (
APPLICATION_ID ,
TABLE_ID ,
TABLE_NAME ,
USER_TABLE_NAME ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
AUTO_SIZE ,
TABLE_TYPE ,
INITIAL_EXTENT ,
NEXT_EXTENT ,
MIN_EXTENTS ,
MAX_EXTENTS ,
PCT_INCREASE ,
INI_TRANS ,
MAX_TRANS ,
PCT_FREE ,
PCT_USED )
select p_appl_id,
fnd_tables_s.nextval,
up_tab_name,
up_tab_name,
to_date('01-01-1990', 'DD-MM-YYYY') ,
1,
to_date('01-01-1990', 'DD-MM-YYYY') ,
1,
0,
new_auto_size,
new_tab_type,
4,
new_next_extent,
1,
50,
0,
1,
255,
p_pct_free,
p_pct_used
from sys.dual
where not exists ( select 'x'
from fnd_tables
where application_id = p_appl_id
and table_name = up_tab_name);
insert into fnd_columns
(APPLICATION_ID,
TABLE_ID,
COLUMN_ID,
COLUMN_NAME,
USER_COLUMN_NAME,
COLUMN_SEQUENCE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
COLUMN_TYPE,
WIDTH,
NULL_ALLOWED_FLAG,
TRANSLATE_FLAG,
FLEXFIELD_USAGE_CODE,
PRECISION,
SCALE)
select p_appl_id,
p_table_id,
fnd_columns_s.nextval,
upper(p_col_name),
upper(p_col_name),
p_col_seq,
to_date('01-01-1990', 'DD-MM-YYYY'),
1,
to_date('01-01-1990', 'DD-MM-YYYY'),
1,
0,
new_col_type,
p_col_width,
upper(p_nullable),
upper(p_translate),
'N',
p_precision,
p_scale
from sys.dual
where not exists (select 'x' from fnd_columns
where application_id = p_appl_id
and table_id = p_table_id
and column_name = upper(p_col_name));
procedure delete_table
(p_appl_short_name in varchar2,
p_tab_name in varchar2)
is
up_tab_name varchar2(40);
delete from fnd_columns
where application_id = p_appl_id and table_id = p_table_id;
delete from fnd_tables
where application_id = p_appl_id and table_id = p_table_id;
end delete_table;
procedure delete_column
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2)
is
p_table_id number := null;
delete from fnd_columns
where application_id = p_appl_id
and table_id = p_table_id
and column_name = upper(p_col_name);
end delete_column;
insert_update_primary_key('INSERT',
p_appl_short_name,
upper(p_key_name),
upper(p_tab_name),
p_description,
upper(p_enabled_flag),
upper(p_key_type),
upper(p_audit_flag));
procedure update_primary_key
(p_appl_short_name in varchar2,
p_key_name in varchar2,
p_tab_name in varchar2,
p_description in varchar2 default null,
p_key_type in varchar2 default null,
p_audit_flag in varchar2 default null,
p_enabled_flag in varchar2 default null)
is
begin
insert_update_primary_key('UPDATE',
p_appl_short_name,
upper(p_key_name),
upper(p_tab_name),
p_description,
upper(p_enabled_flag),
upper(p_key_type),
upper(p_audit_flag));
insert into fnd_primary_key_columns(
APPLICATION_ID,
TABLE_ID,
PRIMARY_KEY_ID,
PRIMARY_KEY_SEQUENCE,
COLUMN_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN)
select l_appl_id,
l_table_id,
l_key_id,
p_col_sequence,
l_col_id,
to_date('01/01/1990', 'DD/MM/YYYY'), 1,
to_date('01/01/1990', 'DD/MM/YYYY'), 1,
0
from dual
where not exists (
select 'x'
from fnd_primary_key_columns
where application_id = l_appl_id
and table_id = l_table_id
and primary_key_id = l_key_id
and column_id = l_col_id);
procedure delete_primary_key_column
(p_appl_short_name in varchar2,
p_key_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2 default null)
is
l_table_id number := null;
delete from fnd_primary_key_columns
where application_id = l_appl_id
and table_id = l_table_id
and primary_key_id = l_key_id
and column_id = decode(p_col_name, null, column_id, l_col_id);