eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.FND_FLEX_HIERARCHY_COMPILER SQL Statements

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

Line: 83


g_insert_count           NUMBER := 0;

Line: 98


error_delete_hierarchies      NUMBER := -20102;

Line: 101


error_update_hierarchies      NUMBER := -20105;

Line: 400


   SELECT /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev ship $ */
     *
     INTO x_vset
     FROM fnd_flex_value_sets
     WHERE flex_value_set_id = To_number(p_flex_value_set);

Line: 414


         SELECT /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev ship $ */
           *
           INTO x_vset
           FROM fnd_flex_value_sets
           WHERE flex_value_set_name = p_flex_value_set;

Line: 443


   INSERT /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev ship $ */
     INTO fnd_flex_value_hierarchies
     (flex_value_set_id, parent_flex_value,
      child_flex_value_low, child_flex_value_high,
      last_update_date, last_updated_by,
      creation_date, created_by)
     VALUES
     (g_vset.flex_value_set_id*(-1), p_root_parent_flex_value,
      p_child_flex_value_low, p_child_flex_value_high,
      Sysdate, g_user_id,
      Sysdate, g_user_id);

Line: 455


   g_insert_count := g_insert_count + 1;

Line: 456


   IF (g_insert_count = g_commit_size) THEN
      COMMIT;

Line: 458


      g_insert_count := 0;

Line: 480


        SELECT /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev ship $ */
          parent_flex_value,
          child_flex_value_low,
          child_flex_value_high,
          range_attribute
          FROM fnd_flex_value_norm_hierarchy
          WHERE flex_value_set_id = g_vset.flex_value_set_id
          AND parent_flex_value >= p_child_flex_value_low
          AND parent_flex_value <= p_child_flex_value_high
          ORDER BY range_attribute, parent_flex_value,
          child_flex_value_low, child_flex_value_high;

Line: 570


        SELECT /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev ship $ */
          parent_flex_value,
          child_flex_value_low,
          child_flex_value_high,
          range_attribute
          FROM fnd_flex_value_norm_hierarchy
          WHERE flex_value_set_id = g_vset.flex_value_set_id
          ORDER BY range_attribute, parent_flex_value,
          child_flex_value_low, child_flex_value_high;

Line: 585


   g_insert_count := 0;

Line: 635


   IF (g_insert_count > 0) THEN
      COMMIT;

Line: 637


      g_insert_count := 0;

Line: 646


         DELETE /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev ship $ */
           FROM fnd_flex_value_hierarchies
           WHERE flex_value_set_id = g_vset.flex_value_set_id;

Line: 670


   INSERT /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev ship $ */
     INTO fnd_flex_value_hier_all
     (flex_value_set_id, parent_flex_value, range_attribute,
      child_flex_value_low, child_flex_value_high, hierarchy_level,
      last_update_date, last_updated_by,
      creation_date, created_by)
     VALUES
     (g_vset.flex_value_set_id*(-1), p_root_parent_flex_value, p_range_attribute,
      p_child_flex_value_low, p_child_flex_value_high, p_hierarchy_level,
      Sysdate, g_user_id,
      Sysdate, g_user_id);

Line: 682


   g_insert_count := g_insert_count + 1;

Line: 683


   IF (g_insert_count = g_commit_size) THEN
      COMMIT;

Line: 685


      g_insert_count := 0;

Line: 710


        SELECT /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev ship $ */
          parent_flex_value,
          child_flex_value_low,
          child_flex_value_high,
          range_attribute
          FROM fnd_flex_value_norm_hierarchy
          WHERE flex_value_set_id = g_vset.flex_value_set_id
          AND parent_flex_value >= p_child_flex_value_low
          AND parent_flex_value <= p_child_flex_value_high
          ORDER BY range_attribute, parent_flex_value,
          child_flex_value_low, child_flex_value_high;

Line: 798


        SELECT /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev ship $ */
          parent_flex_value,
          child_flex_value_low,
          child_flex_value_high,
          range_attribute
          FROM fnd_flex_value_norm_hierarchy
          WHERE flex_value_set_id = g_vset.flex_value_set_id
          ORDER BY range_attribute, parent_flex_value,
          child_flex_value_low, child_flex_value_high;

Line: 811


   g_insert_count := 0;

Line: 865


   IF (g_insert_count > 0) THEN
      COMMIT;

Line: 867


      g_insert_count := 0;

Line: 875


         DELETE /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev ship $ */
           FROM fnd_flex_value_hier_all
           WHERE flex_value_set_id = g_vset.flex_value_set_id;

Line: 888


PROCEDURE delete_value_hierarchies
   --
   -- Delete the old data.
   --
IS
   l_row_count NUMBER;

Line: 897


     DELETE /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev ship $ */
        FROM fnd_flex_value_hierarchies
        WHERE flex_value_set_id = g_vset.flex_value_set_id and
        rownum < 1000;

Line: 906


         raise_exception_error(error_delete_hierarchies,
              'Unable to delete data in FND_FLEX_VALUE_HIERARCHIES table.');

Line: 908


END delete_value_hierarchies;

Line: 910


PROCEDURE delete_value_hierarchies_all
   --
   -- Delete the old data.
   --
IS
   l_row_count NUMBER;

Line: 919


     DELETE /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev ship $ */
        FROM fnd_flex_value_hier_all
        WHERE flex_value_set_id = g_vset.flex_value_set_id and
        rownum < 1000;

Line: 928


         raise_exception_error(error_delete_hierarchies,
             'Unable to delete data in FND_FLEX_VALUE_HIER_ALL table.');

Line: 930


END delete_value_hierarchies_all;

Line: 933


PROCEDURE update_value_hierarchies
   --
   -- Update the newly compiled data with the vset id.
   --
IS
   l_row_count NUMBER;

Line: 942


     UPDATE /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev ship $ */
        fnd_flex_value_hierarchies
        SET flex_value_set_id = g_vset.flex_value_set_id
        WHERE flex_value_set_id = g_vset.flex_value_set_id*(-1) and
        rownum < 1000;

Line: 952


         raise_exception_error(error_update_hierarchies,
              'Unable to update data in FND_FLEX_VALUE_HIERARCHIES table.');

Line: 954


END update_value_hierarchies;

Line: 956


PROCEDURE update_value_hierarchies_all
   --
   -- Update the newly compiled data with the vset id.
   --
IS
   l_row_count NUMBER;

Line: 965


     UPDATE /* $Header: AFFFCHYB.pls 120.2.12010000.1 2008/07/25 14:13:37 appldev ship $ */
        fnd_flex_value_hier_all
        SET flex_value_set_id=g_vset.flex_value_set_id
        WHERE flex_value_set_id = g_vset.flex_value_set_id*(-1) and
        rownum < 1000;

Line: 975


         raise_exception_error(error_update_hierarchies,
              'Unable to update data in FND_FLEX_VALUE_HIERARCHIES table.');

Line: 977


END update_value_hierarchies_all;

Line: 1017


     delete_value_hierarchies();

Line: 1018


     update_value_hierarchies();

Line: 1019


     delete_value_hierarchies_all();

Line: 1020


     update_value_hierarchies_all();

Line: 1103


    reinserting them in a normalized state. Hierarchy value rules
    are checked by security. The problem is that when
    all the hier rules are deleted and before they are reinserted there
    is a time frame where there is a security breach. Values that
    should be secured, will not be sec in that time frame. We recoded
    the logic so that we first insert the hierachy rules with a
    vsetid*(-1). Once done with the insert we create a lock so
    that no one can access the data, and then we delete the rows
    with orig vsetid and then we update the vsetid*(-1) with the
    orig vsetid. After that is done, we release the lock. If another
    process wants to read the hier security data it cannot until the
    lock is released. At this time the only code that is reading the
    hier rules data is fnd_flex_server.check_value_security and
    this is called in FND_FLEX_SERVER1.check_security. In the function
    check_security() we check to see if a lock exists and if there is a
    lock we do not process until the lock is released meaning the data
    is now updated and correct.
   *******************************************************************/
   --
   -- Finish hierarchy processing.
   --
    finish_hierarchy_processing();