APPS.AD_DD SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 11

  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;

Line: 33

  select table_id into p_table_id
    from  fnd_tables
    where application_id = p_apps_id
      and table_name = p_tab_name;

Line: 52

  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;

Line: 74

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

Line: 96

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

Line: 110

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;

Line: 146

  if    (p_mode = 'INSERT' and l_key_id is not null) then
     return;

Line: 148

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

Line: 168

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

Line: 174

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

Line: 180

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

Line: 189

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

Line: 222

  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;

Line: 292

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

Line: 428

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

Line: 473

procedure delete_table
            (p_appl_short_name in varchar2,
             p_tab_name        in varchar2)
is
  up_tab_name varchar2(40);

Line: 502

  delete from fnd_columns
    where application_id = p_appl_id and table_id = p_table_id;

Line: 505

  delete from fnd_tables
    where application_id = p_appl_id and table_id = p_table_id;

Line: 508

end delete_table;

Line: 510

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;

Line: 542

  delete from fnd_columns
    where application_id = p_appl_id
      and table_id = p_table_id
      and column_name = upper(p_col_name);

Line: 547

end delete_column;

Line: 560

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

Line: 570

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

Line: 637

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

Line: 665

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;

Line: 705

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