APPS.FND_PROFILE_OPTION_VALUES_PKG SQL Statements

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

Line: 11

      select HIERARCHY_TYPE
      into L_HIERARCHY_TYPE
      from FND_PROFILE_OPTIONS
      where PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
      and APPLICATION_ID = X_APPLICATION_ID;

Line: 25

   /* This procedure is used to insert a row into fnd_profile_option_values.
   ** Due to the nature of profile option values having levels and granular
   ** values associated to its levels, this routine distinguishes between
   ** these levels to ensure data integrity.
   */
   procedure INSERT_ROW (
      X_ROWID in out nocopy VARCHAR2,
      X_APPLICATION_ID in NUMBER,
      X_PROFILE_OPTION_ID in NUMBER,
      X_LEVEL_ID in NUMBER,
      X_LEVEL_VALUE in NUMBER,
      X_CREATION_DATE in DATE,
      X_CREATED_BY in NUMBER,
      X_LAST_UPDATE_DATE in DATE,
      X_LAST_UPDATED_BY in NUMBER,
      X_LAST_UPDATE_LOGIN in NUMBER,
      X_PROFILE_OPTION_VALUE in VARCHAR2,
      X_LEVEL_VALUE_APPLICATION_ID in NUMBER,
      X_LEVEL_VALUE2 in NUMBER
   ) is

      -- Site level cursor
      cursor S is select ROWID from FND_PROFILE_OPTION_VALUES
      where APPLICATION_ID = X_APPLICATION_ID
      and PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
      and LEVEL_ID = X_LEVEL_ID
      and LEVEL_VALUE = 0;

Line: 54

      cursor ARSO is select ROWID from FND_PROFILE_OPTION_VALUES
      where APPLICATION_ID = X_APPLICATION_ID
      and PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
      and LEVEL_ID = X_LEVEL_ID
      and LEVEL_VALUE = X_LEVEL_VALUE
      and LEVEL_VALUE_APPLICATION_ID is null
      and LEVEL_VALUE2 is null;

Line: 63

      cursor R is select ROWID from FND_PROFILE_OPTION_VALUES
      where APPLICATION_ID = X_APPLICATION_ID
      and PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
      and LEVEL_ID = X_LEVEL_ID
      and LEVEL_VALUE = X_LEVEL_VALUE
      and LEVEL_VALUE_APPLICATION_ID = X_LEVEL_VALUE_APPLICATION_ID
      and LEVEL_VALUE2 is null;

Line: 72

      cursor SR is select ROWID from FND_PROFILE_OPTION_VALUES
      where APPLICATION_ID = X_APPLICATION_ID
      and PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
      and LEVEL_ID = X_LEVEL_ID
      and LEVEL_VALUE = X_LEVEL_VALUE
      and LEVEL_VALUE_APPLICATION_ID = X_LEVEL_VALUE_APPLICATION_ID
      and LEVEL_VALUE2 = X_LEVEL_VALUE2;

Line: 96

      profile option values inserted correctly.  For example, if, by some
      chance that, a site-level profile option value is being inserted with
      a non-null level_value (which does not apply), the level_value is
      overriden as well as any other non-applicable columns on insertion.
   */

      if (X_LEVEL_ID = 10001) then
         -- Site level
         insert into FND_PROFILE_OPTION_VALUES (
            APPLICATION_ID,
            PROFILE_OPTION_ID,
            LEVEL_ID,
            LEVEL_VALUE,
            LEVEL_VALUE_APPLICATION_ID,
            PROFILE_OPTION_VALUE,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            LAST_UPDATE_LOGIN,
            CREATION_DATE,
            CREATED_BY,
            LEVEL_VALUE2
         ) values (
            X_APPLICATION_ID,
            X_PROFILE_OPTION_ID,
            X_LEVEL_ID,
            0,    -- LEVEL_VALUE = 0 for Site level
            NULL, -- LEVEL_VALUE_APPLICATION_ID is not applicable
            X_PROFILE_OPTION_VALUE,
            X_LAST_UPDATE_DATE,
            X_LAST_UPDATED_BY,
            X_LAST_UPDATE_LOGIN,
            X_CREATION_DATE,
            X_CREATED_BY,
            NULL  -- LEVEL_VALUE2 is not applicable
         );

Line: 142

         insert into FND_PROFILE_OPTION_VALUES (
            APPLICATION_ID,
            PROFILE_OPTION_ID,
            LEVEL_ID,
            LEVEL_VALUE,
            LEVEL_VALUE_APPLICATION_ID,
            PROFILE_OPTION_VALUE,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            LAST_UPDATE_LOGIN,
            CREATION_DATE,
            CREATED_BY,
            LEVEL_VALUE2
         ) values (
            X_APPLICATION_ID,
            X_PROFILE_OPTION_ID,
            X_LEVEL_ID,
            X_LEVEL_VALUE,
            X_LEVEL_VALUE_APPLICATION_ID,
            X_PROFILE_OPTION_VALUE,
            X_LAST_UPDATE_DATE,
            X_LAST_UPDATED_BY,
            X_LAST_UPDATE_LOGIN,
            X_CREATION_DATE,
            X_CREATED_BY,
            X_LEVEL_VALUE2
         );

Line: 183

         insert into FND_PROFILE_OPTION_VALUES (
            APPLICATION_ID,
            PROFILE_OPTION_ID,
            LEVEL_ID,
            LEVEL_VALUE,
            LEVEL_VALUE_APPLICATION_ID,
            PROFILE_OPTION_VALUE,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            LAST_UPDATE_LOGIN,
            CREATION_DATE,
            CREATED_BY,
            LEVEL_VALUE2
         ) values (
            X_APPLICATION_ID,
            X_PROFILE_OPTION_ID,
            X_LEVEL_ID,
            X_LEVEL_VALUE,
            NULL,
            X_PROFILE_OPTION_VALUE,
            X_LAST_UPDATE_DATE,
            X_LAST_UPDATED_BY,
            X_LAST_UPDATE_LOGIN,
            X_CREATION_DATE,
            X_CREATED_BY,
            NULL  -- LEVEL_VALUE2 is not applicable
         );

Line: 222

         insert into FND_PROFILE_OPTION_VALUES (
            APPLICATION_ID,
            PROFILE_OPTION_ID,
            LEVEL_ID,
            LEVEL_VALUE,
            LEVEL_VALUE_APPLICATION_ID,
            PROFILE_OPTION_VALUE,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            LAST_UPDATE_LOGIN,
            CREATION_DATE,
            CREATED_BY,
            LEVEL_VALUE2
         ) values (
            X_APPLICATION_ID,
            X_PROFILE_OPTION_ID,
            X_LEVEL_ID,
            X_LEVEL_VALUE,
            X_LEVEL_VALUE_APPLICATION_ID,
            X_PROFILE_OPTION_VALUE,
            X_LAST_UPDATE_DATE,
            X_LAST_UPDATED_BY,
            X_LAST_UPDATE_LOGIN,
            X_CREATION_DATE,
            X_CREATED_BY,
            NULL  -- LEVEL_VALUE2 is not applicable
         );

Line: 262

         select PROFILE_OPTION_NAME
         into L_PROFILE_OPTION_NAME
         from FND_PROFILE_OPTIONS
         where APPLICATION_ID = X_APPLICATION_ID
         and PROFILE_OPTION_ID = X_PROFILE_OPTION_ID;

Line: 273

   end INSERT_ROW;

Line: 275

   /* This procedure is used to update profile option values at a given level,
    * (if it applies).  If the profile fails to update, it means that there is
    * no row to update.  If that occurs, INSERT_ROW is called to insert the
    * profile option value.
    */
   procedure UPDATE_ROW (
      X_APPLICATION_ID in NUMBER,
      X_PROFILE_OPTION_ID in NUMBER,
      X_LEVEL_ID in NUMBER,
      X_LEVEL_VALUE in NUMBER,
      X_LEVEL_VALUE_APPLICATION_ID in NUMBER,
      X_LEVEL_VALUE2 in NUMBER,
      X_PROFILE_OPTION_VALUE in VARCHAR2,
      X_LAST_UPDATE_DATE in DATE,
      X_LAST_UPDATED_BY in NUMBER,
      X_LAST_UPDATE_LOGIN in NUMBER
   ) is

      L_ROWID varchar2(20);

Line: 311

        update FND_PROFILE_OPTION_VALUES
        set    PROFILE_OPTION_VALUE = X_PROFILE_OPTION_VALUE,
               LAST_UPDATE_DATE     = X_LAST_UPDATE_DATE,
               LAST_UPDATED_BY      = X_LAST_UPDATED_BY,
               LAST_UPDATE_LOGIN    = X_LAST_UPDATE_LOGIN
        where  PROFILE_OPTION_ID    = X_PROFILE_OPTION_ID
        and    APPLICATION_ID       = X_APPLICATION_ID
        and    LEVEL_ID             = X_LEVEL_ID
        and    LEVEL_VALUE          = X_LEVEL_VALUE
        and    LEVEL_VALUE2         = X_LEVEL_VALUE2
        and    (nvl(X_LEVEL_VALUE_APPLICATION_ID, -1) = -1
                or LEVEL_VALUE_APPLICATION_ID = X_LEVEL_VALUE_APPLICATION_ID);

Line: 330

        update FND_PROFILE_OPTION_VALUES
        set    PROFILE_OPTION_VALUE = X_PROFILE_OPTION_VALUE,
               LAST_UPDATE_DATE     = X_LAST_UPDATE_DATE,
               LAST_UPDATED_BY      = X_LAST_UPDATED_BY,
               LAST_UPDATE_LOGIN    = X_LAST_UPDATE_LOGIN
        where  PROFILE_OPTION_ID    = X_PROFILE_OPTION_ID
        and    APPLICATION_ID       = X_APPLICATION_ID
        and    LEVEL_ID             = X_LEVEL_ID
        and    LEVEL_VALUE          = X_LEVEL_VALUE
        and    (nvl(X_LEVEL_VALUE_APPLICATION_ID, -1) = -1
                or LEVEL_VALUE_APPLICATION_ID = X_LEVEL_VALUE_APPLICATION_ID);

Line: 345

        FND_PROFILE_OPTION_VALUES_PKG.INSERT_ROW(
          L_ROWID,
          X_APPLICATION_ID,
          X_PROFILE_OPTION_ID,
          X_LEVEL_ID,
          X_LEVEL_VALUE,
          sysdate,           -- X_CREATION_DATE
          X_LAST_UPDATED_BY, -- X_CREATED_BY
          sysdate,           -- X_LAST_UPDATE_DATE
          X_LAST_UPDATED_BY,
          X_LAST_UPDATE_LOGIN,
          X_PROFILE_OPTION_VALUE,
          X_LEVEL_VALUE_APPLICATION_ID,
          X_LEVEL_VALUE2
        );

Line: 364

         select PROFILE_OPTION_NAME
         into L_PROFILE_OPTION_NAME
         from FND_PROFILE_OPTIONS
         where APPLICATION_ID = X_APPLICATION_ID
         and PROFILE_OPTION_ID = X_PROFILE_OPTION_ID;

Line: 375

   end UPDATE_ROW;

Line: 377

   /* Overloaded UPDATE_ROW */
   procedure UPDATE_ROW(
      X_APPLICATION_ID in NUMBER,
      X_PROFILE_OPTION_ID in NUMBER,
      X_LEVEL_ID in NUMBER,
      X_LEVEL_VALUE in NUMBER,
      X_LEVEL_VALUE_APPLICATION_ID in NUMBER,
      X_PROFILE_OPTION_VALUE in VARCHAR2,
      X_LAST_UPDATE_DATE in DATE,
      X_LAST_UPDATED_BY in NUMBER,
      X_LAST_UPDATE_LOGIN in NUMBER
   ) is

   begin

      if (X_LEVEL_ID <> 10007) then
        /* Call UPDATE_ROW passing NULL for LEVEL_VALUE2 if
           level_id <> 10007
         */
         UPDATE_ROW(
            X_APPLICATION_ID,
            X_PROFILE_OPTION_ID,
            X_LEVEL_ID,
            X_LEVEL_VALUE,
            X_LEVEL_VALUE_APPLICATION_ID,
            NULL,
            X_PROFILE_OPTION_VALUE,
            X_LAST_UPDATE_DATE,
            X_LAST_UPDATED_BY,
            X_LAST_UPDATE_LOGIN);

Line: 409

   end UPDATE_ROW;

Line: 411

   procedure DELETE_ROW (
      X_APPLICATION_ID in NUMBER,
      X_PROFILE_OPTION_ID in NUMBER,
      X_LEVEL_ID in NUMBER,
      X_LEVEL_VALUE in NUMBER,
      X_LEVEL_VALUE_APPLICATION_ID in NUMBER,
      X_LEVEL_VALUE2 in NUMBER
   ) is

      L_HIERARCHY_TYPE VARCHAR2(8);

Line: 430

         delete from FND_PROFILE_OPTION_VALUES
         where  PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
         and    APPLICATION_ID    = X_APPLICATION_ID
         and    LEVEL_ID          = X_LEVEL_ID
         and    LEVEL_VALUE       = X_LEVEL_VALUE
         and    LEVEL_VALUE2      = X_LEVEL_VALUE2
         and    (nvl(X_LEVEL_VALUE_APPLICATION_ID, -1) = -1
         or LEVEL_VALUE_APPLICATION_ID = X_LEVEL_VALUE_APPLICATION_ID);

Line: 446

         delete from FND_PROFILE_OPTION_VALUES
         where  PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
         and    APPLICATION_ID    = X_APPLICATION_ID
         and    LEVEL_ID          = X_LEVEL_ID
         and    LEVEL_VALUE       = X_LEVEL_VALUE
         and    (nvl(X_LEVEL_VALUE_APPLICATION_ID, -1) = -1
         or LEVEL_VALUE_APPLICATION_ID = X_LEVEL_VALUE_APPLICATION_ID);

Line: 457

         delete from FND_PROFILE_OPTION_VALUES
         where  PROFILE_OPTION_ID = X_PROFILE_OPTION_ID
         and    APPLICATION_ID    = X_APPLICATION_ID
         and    LEVEL_ID          = X_LEVEL_ID
         and    LEVEL_VALUE       = X_LEVEL_VALUE
         and    (nvl(X_LEVEL_VALUE_APPLICATION_ID, -1) = -1
         or LEVEL_VALUE_APPLICATION_ID = X_LEVEL_VALUE_APPLICATION_ID);

Line: 471

   end DELETE_ROW;

Line: 473

   /* Overloaded DELETE_ROW */
   procedure DELETE_ROW(
      X_APPLICATION_ID in NUMBER,
      X_PROFILE_OPTION_ID in NUMBER,
      X_LEVEL_ID in NUMBER,
      X_LEVEL_VALUE in NUMBER,
      X_LEVEL_VALUE_APPLICATION_ID in NUMBER
   ) is

   begin

      if (X_LEVEL_ID <> 10007) then
        /* Call DELETE_ROW passing NULL for LEVEL_VALUE2 if
           level_id <> 10007
         */
        DELETE_ROW (
           X_APPLICATION_ID,
           X_PROFILE_OPTION_ID,
           X_LEVEL_ID,
           X_LEVEL_VALUE,
           X_LEVEL_VALUE_APPLICATION_ID,
           NULL);

Line: 497

   end DELETE_ROW;

Line: 500

    * FND_PROFILE_OPTIONS_PKG.DELETE_ROW which deletes profile option
    * definitions.  This procedure ensures that there will be no dangling
    * references in FND_PROFILE_OPTION_VALUES to the profile option being
    * deleted, i.e. if a profile is being deleted, it should have no rows
    * for profile option values.
    */
   procedure DELETE_PROFILE_OPTION_VALUES (X_PROFILE_OPTION_NAME in VARCHAR2) is
      L_PROFILE_OPTION_ID number;

Line: 512

      select profile_option_id, application_id
      into L_PROFILE_OPTION_ID, L_APPLICATION_ID
      from fnd_profile_options
      where profile_option_name = X_PROFILE_OPTION_NAME;

Line: 524

      delete from fnd_profile_option_values
      where profile_option_id = L_PROFILE_OPTION_ID
      and application_id = L_APPLICATION_ID;

Line: 535

   end DELETE_PROFILE_OPTION_VALUES;