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.4 2011/11/08 19:05:50 hgeorgi 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.4 2011/11/08 19:05:50 hgeorgi 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.4 2011/11/08 19:05:50 hgeorgi 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.4 2011/11/08 19:05:50 hgeorgi 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.4 2011/11/08 19:05:50 hgeorgi 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.4 2011/11/08 19:05:50 hgeorgi ship $ */
           FROM fnd_flex_value_hierarchies
           WHERE flex_value_set_id = g_vset.flex_value_set_id;

Line: 670

   INSERT /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi 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.4 2011/11/08 19:05:50 hgeorgi 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.4 2011/11/08 19:05:50 hgeorgi 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.4 2011/11/08 19:05:50 hgeorgi 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.4 2011/11/08 19:05:50 hgeorgi 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.4 2011/11/08 19:05:50 hgeorgi 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 delete_sec_rule_allowed_vals
   --
   -- Delete the old data.
   --
IS
   l_row_count NUMBER;

Line: 942

     DELETE /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
        FROM fnd_flex_sec_rule_allowed_vals
        WHERE flex_value_set_id = g_vset.flex_value_set_id and
        rownum < 1000;

Line: 951

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

Line: 953

END delete_sec_rule_allowed_vals;

Line: 959

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

Line: 968

     UPDATE /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi 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: 978

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

Line: 980

END update_value_hierarchies;

Line: 982

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

Line: 991

     UPDATE /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi 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: 1001

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

Line: 1003

END update_value_hierarchies_all;

Line: 1007

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

Line: 1016

     UPDATE /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
        fnd_flex_sec_rule_allowed_vals
        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: 1026

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

Line: 1028

END update_sec_rule_allowed_vals;

Line: 1045

     INSERT /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
     into fnd_flex_sec_rule_allowed_vals
     (FLEX_VALUE_SET_ID,
      FLEX_VALUE_RULE_ID,
      FLEX_VALUE,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      CREATION_DATE,
      CREATED_BY)
     SELECT
       FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID*(-1),
       RS.FLEX_VALUE_RULE_ID,
       FND_FLEX_VALUES_VL.FLEX_VALUE,
       SYSDATE, 0, sysdate, 0
       FROM FND_FLEX_VALUES_VL, FND_FLEX_VALUE_RULES RS
       WHERE
       FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id
       AND FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID = RS.FLEX_VALUE_SET_ID
       AND FND_FLEX_VALUES_VL.ENABLED_FLAG = 'Y'
       AND NOT EXISTS
       (SELECT NULL FROM FND_FLEX_VALUE_RULE_LINES L, FND_FLEX_VALUE_RULES R
        WHERE L.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id
        AND L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID
        AND R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID
        AND L.INCLUDE_EXCLUDE_INDICATOR = 'E'
        AND  fnd_number.canonical_to_number(FND_FLEX_VALUES_VL.FLEX_VALUE)
        BETWEEN
             fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_LOW, FND_FLEX_VALUES_VL.FLEX_VALUE))
        AND  fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_HIGH, FND_FLEX_VALUES_VL.FLEX_VALUE))) AND
        NOT EXISTS (SELECT NULL FROM
          FND_FLEX_VALUE_HIER_ALL H,
          FND_FLEX_VALUE_RULE_LINES L,
          FND_FLEX_VALUE_RULES R
          WHERE
          L.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
          H.FLEX_VALUE_SET_ID = L.FLEX_VALUE_SET_ID  AND
          L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID AND
          R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID AND
          L.INCLUDE_EXCLUDE_INDICATOR = 'E' AND
          fnd_number.canonical_to_number(FND_FLEX_VALUES_VL.FLEX_VALUE)
          BETWEEN
          fnd_number.canonical_to_number(H.CHILD_FLEX_VALUE_LOW) AND
          fnd_number.canonical_to_number(H.CHILD_FLEX_VALUE_HIGH) AND
          fnd_number.canonical_to_number(H.PARENT_FLEX_VALUE)
          BETWEEN
          fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_LOW, H.PARENT_FLEX_VALUE)) AND
          fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_HIGH, H.PARENT_FLEX_VALUE))) AND
          (NOT EXISTS (SELECT NULL FROM FND_FLEX_VALUE_RULES R
          WHERE R.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
          R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID AND
          NOT EXISTS (SELECT NULL
          FROM FND_FLEX_VALUE_RULE_LINES L WHERE
          L.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
          L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID AND
          L.INCLUDE_EXCLUDE_INDICATOR = 'I' AND
          fnd_number.canonical_to_number(FND_FLEX_VALUES_VL.FLEX_VALUE)
          BETWEEN
          fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_LOW, FND_FLEX_VALUES_VL.FLEX_VALUE)) AND
          fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_HIGH, FND_FLEX_VALUES_VL.FLEX_VALUE))))
         OR  NOT EXISTS
          (SELECT NULL FROM FND_FLEX_VALUE_RULES R
          WHERE R.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
          R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID AND
         NOT EXISTS (SELECT NULL FROM FND_FLEX_VALUE_HIER_ALL H, FND_FLEX_VALUE_RULE_LINES L
         WHERE H.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id  AND
         H.FLEX_VALUE_SET_ID = L.FLEX_VALUE_SET_ID AND
         L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID AND
         L.INCLUDE_EXCLUDE_INDICATOR = 'I' AND
         fnd_number.canonical_to_number(FND_FLEX_VALUES_VL.FLEX_VALUE) BETWEEN
         fnd_number.canonical_to_number(H.CHILD_FLEX_VALUE_LOW) AND
         fnd_number.canonical_to_number(H.CHILD_FLEX_VALUE_HIGH) AND
         fnd_number.canonical_to_number(H.PARENT_FLEX_VALUE) BETWEEN
         fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_LOW, H.PARENT_FLEX_VALUE)) AND
         fnd_number.canonical_to_number(NVL(L.FLEX_VALUE_HIGH, H.PARENT_FLEX_VALUE)))))
         ORDER BY
         FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID,
         RS.FLEX_VALUE_RULE_ID,
         fnd_number.canonical_to_number(FND_FLEX_VALUES_VL.FLEX_VALUE);

Line: 1126

     INSERT /* $Header: AFFFCHYB.pls 120.4 2011/11/08 19:05:50 hgeorgi ship $ */
     into fnd_flex_sec_rule_allowed_vals
     (FLEX_VALUE_SET_ID,
      FLEX_VALUE_RULE_ID,
      FLEX_VALUE,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      CREATION_DATE,
      CREATED_BY)
     SELECT
       FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID*(-1),
       RS.FLEX_VALUE_RULE_ID,
       FND_FLEX_VALUES_VL.FLEX_VALUE,
       SYSDATE, 0, sysdate, 0
       FROM FND_FLEX_VALUES_VL, FND_FLEX_VALUE_RULES RS
       WHERE
       FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id
       AND FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID = RS.FLEX_VALUE_SET_ID
       AND FND_FLEX_VALUES_VL.ENABLED_FLAG = 'Y'
       AND NOT EXISTS
       (SELECT NULL FROM FND_FLEX_VALUE_RULE_LINES L, FND_FLEX_VALUE_RULES R
        WHERE L.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id
        AND L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID
        AND R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID
        AND L.INCLUDE_EXCLUDE_INDICATOR = 'E'
        AND  FND_FLEX_VALUES_VL.FLEX_VALUE
        BETWEEN  NVL(L.FLEX_VALUE_LOW, FND_FLEX_VALUES_VL.FLEX_VALUE)
        AND  NVL(L.FLEX_VALUE_HIGH, FND_FLEX_VALUES_VL.FLEX_VALUE)) AND
        NOT EXISTS (SELECT NULL FROM
          FND_FLEX_VALUE_HIER_ALL H,
          FND_FLEX_VALUE_RULE_LINES L,
          FND_FLEX_VALUE_RULES R
          WHERE
          L.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
          H.FLEX_VALUE_SET_ID = L.FLEX_VALUE_SET_ID  AND
          L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID AND
          R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID AND
          L.INCLUDE_EXCLUDE_INDICATOR = 'E' AND
          FND_FLEX_VALUES_VL.FLEX_VALUE
          BETWEEN  H.CHILD_FLEX_VALUE_LOW AND  H.CHILD_FLEX_VALUE_HIGH AND
          H.PARENT_FLEX_VALUE
          BETWEEN  NVL(L.FLEX_VALUE_LOW, H.PARENT_FLEX_VALUE) AND
          NVL(L.FLEX_VALUE_HIGH, H.PARENT_FLEX_VALUE)) AND
          (NOT EXISTS (SELECT NULL FROM FND_FLEX_VALUE_RULES R
          WHERE R.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
          R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID AND
          NOT EXISTS (SELECT NULL
          FROM FND_FLEX_VALUE_RULE_LINES L WHERE
          L.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
          L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID AND
          L.INCLUDE_EXCLUDE_INDICATOR = 'I' AND
          FND_FLEX_VALUES_VL.FLEX_VALUE BETWEEN  NVL(L.FLEX_VALUE_LOW,
          FND_FLEX_VALUES_VL.FLEX_VALUE)
              AND NVL(L.FLEX_VALUE_HIGH, FND_FLEX_VALUES_VL.FLEX_VALUE)))
         OR  NOT EXISTS
          (SELECT NULL FROM FND_FLEX_VALUE_RULES R
          WHERE R.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id AND
          R.FLEX_VALUE_RULE_ID=RS.FLEX_VALUE_RULE_ID AND
         NOT EXISTS (SELECT NULL FROM FND_FLEX_VALUE_HIER_ALL H, FND_FLEX_VALUE_RULE_LINES L
         WHERE H.FLEX_VALUE_SET_ID = g_vset.flex_value_set_id  AND
         H.FLEX_VALUE_SET_ID = L.FLEX_VALUE_SET_ID AND
         L.FLEX_VALUE_RULE_ID = R.FLEX_VALUE_RULE_ID AND
         L.INCLUDE_EXCLUDE_INDICATOR = 'I' AND
         FND_FLEX_VALUES_VL.FLEX_VALUE BETWEEN  H.CHILD_FLEX_VALUE_LOW AND
       H.CHILD_FLEX_VALUE_HIGH
         AND H.PARENT_FLEX_VALUE BETWEEN  NVL(L.FLEX_VALUE_LOW,
       H.PARENT_FLEX_VALUE)
         AND NVL(L.FLEX_VALUE_HIGH, H.PARENT_FLEX_VALUE))))
         ORDER BY
         FND_FLEX_VALUES_VL.FLEX_VALUE_SET_ID,
         RS.FLEX_VALUE_RULE_ID,
         FND_FLEX_VALUES_VL.FLEX_VALUE;

Line: 1204

         raise_exception_error(error_update_hierarchies,
              'Unable to insert data in FND_FLEX_SEC_RULE_ALLOWED_VALS table.');

Line: 1248

     delete_value_hierarchies();

Line: 1249

     update_value_hierarchies();

Line: 1250

     delete_value_hierarchies_all();

Line: 1251

     update_value_hierarchies_all();

Line: 1253

     delete_sec_rule_allowed_vals();

Line: 1254

     update_sec_rule_allowed_vals();

Line: 1351

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

Line: 1499

   ('SELECT  /* Header: AFFFSV2B.pls 120.2.12000000.1 2007/01/18 13:18:43 appldev ship $ */ ' ||
    ' v.flex_value_set_id ' ||
    ' FROM  fnd_flex_value_sets v ' ||
    ' WHERE EXISTS ' ||
         ' (SELECT null ' ||
            ' FROM fnd_flex_value_norm_hierarchy h ' ||
           ' WHERE h.flex_value_set_id = v.flex_value_set_id)');