APPS.FII_FA_CAT_C SQL Statements

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

Line: 41

       FII_UTIL.Write_Log('Inserting DBI log items into FII_CHANGE_LOG');

Line: 48

   INSERT INTO FII_CHANGE_LOG (
          log_item,
          item_value,
          creation_date,
          created_by,
          last_update_date,
          last_update_login,
          last_updated_by)
   SELECT 'MAX_ASSET_CAT_ID',
          '0',
          sysdate,
          g_fii_user_id,
          sysdate,
          g_fii_login_id,
          g_fii_user_id
     FROM DUAL
    WHERE NOT EXISTS
          (SELECT 1
             FROM FII_CHANGE_LOG
            WHERE log_item = 'MAX_ASSET_CAT_ID');

Line: 70

      FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' log items into FII_CHANGE_LOG');

Line: 103

   SELECT DISTINCT id_flex_structure_name
     INTO l_structure_name
     FROM fnd_id_flex_structures_tl t
    WHERE application_id = 140
      AND id_flex_code   = 'CAT#'
      AND id_flex_num    = p_structure_id
      AND language       = g_current_language;

Line: 184

   select sys.category_flex_structure,
          fsav.application_column_name,
          seg.flex_value_set_id,
          fv.validation_type,
          PARENT_FLEX_VALUE_SET_ID
     into l_flex_structure_id,
          l_major_seg,
          l_major_flex_value_set_id,
          l_major_val_type_code,
          l_parent_value_set_id
     FROM fnd_id_flex_segments         seg,
          FND_SEGMENT_ATTRIBUTE_VALUES fsav,
          FA_SYSTEM_CONTROLS           sys,
          fnd_flex_value_sets          fv
    WHERE fsav.application_id          = 140
      AND fsav.id_flex_code            = 'CAT#'
      AND fsav.id_flex_num             = sys.category_flex_structure
      AND fsav.segment_attribute_type  = 'BASED_CATEGORY'
      AND fsav.attribute_value         = 'Y'
      AND seg.application_id           = 140
      AND seg.id_flex_code             = 'CAT#'
      AND seg.id_flex_num              = sys.CATEGORY_FLEX_STRUCTURE
      AND seg.APPLICATION_COLUMN_NAME  = fsav.application_column_name
      AND fv.flex_value_set_id         = seg.flex_value_set_id;

Line: 210

      select application_table_name,
             value_column_name,
             id_column_name,
             additional_where_clause
        into l_major_table_name,
             l_major_value_column_name,
             l_major_id_column_name,
             l_major_add_where_clause
        from fnd_flex_validation_tables
       where flex_value_set_id = l_major_flex_value_set_id;

Line: 265

      select fsav.application_column_name,
             seg.flex_value_set_id,
             fv.validation_type,
             PARENT_FLEX_VALUE_SET_ID
        INTO l_minor_seg,
             l_minor_flex_value_set_id,
             l_minor_val_type_code,
             l_parent_value_set_id
        FROM fnd_id_flex_segments         seg,
             FND_SEGMENT_ATTRIBUTE_VALUES fsav,
             FA_SYSTEM_CONTROLS           sys,
             fnd_flex_value_sets          fv
       WHERE fsav.application_id          = 140
         AND fsav.id_flex_code            = 'CAT#'
         AND fsav.id_flex_num             = sys.category_flex_structure
         AND fsav.segment_attribute_type  = 'MINOR_CATEGORY'
         AND fsav.attribute_value         = 'Y'
         AND seg.application_id           = 140
         AND seg.id_flex_code             = 'CAT#'
         AND seg.id_flex_num              = sys.CATEGORY_FLEX_STRUCTURE
         AND seg.APPLICATION_COLUMN_NAME  = fsav.application_column_name
         AND fv.flex_value_set_id         = seg.flex_value_set_id;

Line: 289

         select application_table_name,
                value_column_name,
                id_column_name,
                additional_where_clause
           into l_minor_table_name,
                l_minor_value_column_name,
                l_minor_id_column_name,
                l_minor_add_where_clause
           from fnd_flex_validation_tables
          where flex_value_set_id = l_minor_flex_value_set_id;

Line: 350

   INSERT INTO FII_FA_CAT_SEGMENTS(
       flex_structure_id,
       major_seg_name,
       major_val_type_code,
       major_table_name,
       major_value_column_name,
       major_id_column_name,
       major_add_where_clause,
       minor_seg_name,
       minor_val_type_code,
       minor_table_name,
       minor_value_column_name,
       minor_id_column_name,
       minor_add_where_clause,
       dependant_value_set_flag,
       CREATION_DATE,
       CREATED_BY,
       LAST_UPDATE_DATE,
       LAST_UPDATED_BY,
       LAST_UPDATE_LOGIN
   )
   VALUES(
       l_flex_structure_id,
       l_major_seg,
       l_major_val_type_code,
       l_major_table_name,
       l_major_value_column_name,
       l_major_id_column_name,
       l_major_add_where_clause,
       l_minor_seg,
       l_minor_val_type_code,
       l_minor_table_name,
       l_minor_value_column_name,
       l_minor_id_column_name,
       l_minor_add_where_clause,
       l_dependant_value_set_flag,
       sysdate,
       g_fii_user_id,
       sysdate,
       g_fii_user_id,
       g_fii_login_id
);

Line: 453

PROCEDURE INSERT_INTO_CAT_DIM (p_major_seg                IN VARCHAR2,
                               p_major_val_type_code      IN VARCHAR2,
                               p_major_table_name         IN VARCHAR2,
                               p_major_value_column_name  IN VARCHAR2,
                               p_major_id_column_name     IN VARCHAR2,
--                               p_major_add_where_clause   IN VARCHAR2,
                               p_minor_seg                IN VARCHAR2,
                               p_minor_val_type_code      IN VARCHAR2,
                               p_minor_table_name         IN VARCHAR2,
                               p_minor_value_column_name  IN VARCHAR2,
                               p_minor_id_column_name     IN VARCHAR2,
--                               p_minor_add_where_clause   IN VARCHAR2,
                               p_dependant_value_set_flag IN VARCHAR2,
                               p_max_cat_id               IN VARCHAR2) IS

   l_ins_stmt     long;

Line: 474

   select distinct
          dim1.major_value,
          dim2.major_id
     from fii_fa_cat_dimensions dim1,
          fii_fa_cat_dimensions dim2
    where dim1.major_id is null
      and dim1.major_value = dim2.major_value(+);

Line: 483

   select distinct
          dim1.minor_value,
          dim2.minor_id
     from fii_fa_cat_dimensions dim1,
          fii_fa_cat_dimensions dim2
    where dim1.minor_id is null
      and dim1.minor_value = dim2.minor_value(+);

Line: 499

   l_calling_fn   VARCHAR2(40) := 'FII_FA_CAT_C.INSERT_INTO_CAT';

Line: 508

      FII_UTIL.Write_Log('Inserting IDs in flex structure: ' ||
                         p_major_seg || ' - ' ||
                         p_minor_seg);

Line: 518

      FII_UTIL.Write_Log(l_calling_fn || ': p_max_cat_id                 before insert: ' || to_char(p_max_cat_id));

Line: 519

      FII_UTIL.Write_Log(l_calling_fn || ': p_major_seg                  before insert: ' || p_major_seg);

Line: 520

      FII_UTIL.Write_Log(l_calling_fn || ': p_major_table_name           before insert: ' || p_major_table_name);

Line: 521

      FII_UTIL.Write_Log(l_calling_fn || ': p_major_value_column_name    before insert: ' || p_major_value_column_name);

Line: 522

      FII_UTIL.Write_Log(l_calling_fn || ': p_major_id_column_name       before insert: ' || p_major_id_column_name);

Line: 523

      FII_UTIL.Write_Log(l_calling_fn || ': p_minor_seg                  before insert: ' || p_minor_seg);

Line: 524

      FII_UTIL.Write_Log(l_calling_fn || ': p_minor_table_name           before insert: ' || p_minor_table_name);

Line: 525

      FII_UTIL.Write_Log(l_calling_fn || ': p_minor_value_column_name    before insert: ' || p_minor_value_column_name);

Line: 526

      FII_UTIL.Write_Log(l_calling_fn || ': p_minor_id_column_name       before insert: ' || p_minor_id_column_name);

Line: 527

      FII_UTIL.Write_Log(l_calling_fn || ': p_dependant_value_set_flag   before insert: ' || p_dependant_value_set_flag);

Line: 528

      FII_UTIL.Write_Log(l_calling_fn || ': g_fii_user_id                before insert: ' || to_char(nvl(g_fii_user_id, -99)));

Line: 529

      FII_UTIL.Write_Log(l_calling_fn || ': g_fii_login_id               before insert: ' || to_char(nvl(g_fii_login_id, -99)));

Line: 534

          'INSERT INTO FII_FA_CAT_DIMENSIONS (
                  category_id,
                  flex_structure_id,
                  creation_date,
                  created_by,
                  last_update_date,
                  last_updated_by,
                  last_update_login,
                  major_id,
                  major_value,
                  minor_id,
                  minor_value ) ';               -- complete here

Line: 548

         ' SELECT distinct cat.category_id,         -- use distinct for id based table validated sets
                  sys.category_flex_structure,
                  sysdate,
                  ' ||g_fii_user_id || ',
                  sysdate,
                  ' || g_fii_user_id || ',
                  ' || g_fii_login_id || ' , ';     -- completed below

Line: 685

      FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' records into FII_FA_CAT_DIMENSIONS');

Line: 721

      update fii_fa_cat_dimensions
         set major_id    = nvl(l_major_id_tbl(i), fii_fa_cat_dimensions_s.nextval)
       where major_value = l_major_value_tbl(i);

Line: 739

      update fii_fa_cat_dimensions
         set minor_id    = nvl(l_minor_id_tbl(i), fii_fa_cat_dimensions_s1.nextval)
       where minor_value = l_minor_value_tbl(i);

Line: 758

Error occured in Procedure: INSERT_INTO_CAT_DIM
Message: ' || sqlerrm);

Line: 762

END INSERT_INTO_CAT_DIM;

Line: 796

   g_phase := 'SELECT FROM fii_fa_cat_dimensions';

Line: 798

   SELECT MAX(category_id)
     INTO l_tmp_max_cat_id
     FROM fii_fa_cat_dimensions;

Line: 813

   g_phase := 'UPDATE fii_change_log';

Line: 816

   UPDATE fii_change_log
      SET item_value        = to_char(g_new_max_cat_id),
          last_update_date  = SYSDATE,
          last_update_login = g_fii_login_id,
          last_updated_by   = g_fii_user_id
    WHERE log_item          = 'MAX_ASSET_CAT_ID';

Line: 824

      FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_change_log');

Line: 871

   SELECT item_value
     INTO g_max_cat_id
     FROM fii_change_log
    WHERE log_item = 'MAX_ASSET_CAT_ID';

Line: 887

   SELECT max(category_id)
     INTO g_new_max_cat_id
     FROM fa_categories;

Line: 924

PROCEDURE INSERT_NEW_CAT IS

   CURSOR sss_list IS
   SELECT DISTINCT major_seg_name,
                   major_val_type_code,
                   major_table_name,
                   major_value_column_name,
                   major_id_column_name,
                   minor_seg_name,
                   minor_val_type_code,
                   minor_table_name,
                   minor_value_column_name,
                   minor_id_column_name,
                   dependant_value_set_flag
     FROM FII_FA_CAT_SEGMENTS;

Line: 940

   l_calling_fn  varchar2(40) := 'FII_FA_CAT_ID_C.INSERT_NEW_CAT';

Line: 954

   SELECT item_value
     INTO g_max_cat_id
     FROM fii_change_log
    WHERE log_item = 'MAX_ASSET_CAT_ID';

Line: 983

      g_phase := 'UPDATE fii_change_log';

Line: 985

      UPDATE fii_change_log
         SET item_value        = '0',
             last_update_date  = sysdate,
             last_update_login = g_fii_login_id,
             last_updated_by   = g_fii_user_id
       WHERE log_item          = 'MAX_ASSET_CAT_ID';

Line: 993

         FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in fii_change_log');

Line: 1000

   g_phase := 'SELECT FROM fa_categories';

Line: 1002

   SELECT max(category_id)
     INTO g_new_max_cat_id
     FROM fa_categories_b;

Line: 1008

      g_phase := 'Insert new CAT IDs into FII_FA_CAT_DIMENSIONS table';

Line: 1027

         g_phase := 'INSERT INTO FII_FA_CAT_SEGMENTS';

Line: 1044

         g_phase := 'Call INSERT_INTO_CAT_ID_DIM';

Line: 1046

         INSERT_INTO_CAT_DIM(
               sss.major_seg_name,
               sss.major_val_type_code,
               sss.major_table_name,
               sss.major_value_column_name,
               sss.major_id_column_name,
               sss.minor_seg_name,
               sss.minor_val_type_code,
               sss.minor_table_name,
               sss.minor_value_column_name,
               sss.minor_id_column_name,
               sss.dependant_value_set_flag,
               g_max_cat_id
            );

Line: 1100

       UPDATE fii_change_log
          SET item_value        = '0',
              last_update_date  = sysdate,
              last_update_login = g_fii_login_id,
              last_updated_by   = g_fii_user_id
        WHERE log_item          = 'MAX_ASSET_CAT_ID';

Line: 1115

Error occured in Procedure: INSERT_NEW_CAT
Phase: ' || g_phase || '
Message: ' || sqlerrm);

Line: 1121

END INSERT_NEW_CAT;

Line: 1348

   g_phase := 'Call INSERT_NEW_CAT';

Line: 1350

   INSERT_NEW_CAT;