APPS.HZ_DSS_GROUPS_PKG SQL Statements

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

Line: 4

PROCEDURE Insert_Row (
    x_rowid                                 IN OUT NOCOPY VARCHAR2,
    x_dss_group_code                        IN     VARCHAR2,
    x_rank                                  IN     NUMBER,
    x_status                                IN     VARCHAR2,
    x_dss_group_name                        IN     VARCHAR2,
    x_description                           IN     VARCHAR2,
    x_bes_enable_flag                       IN     VARCHAR2,
    x_object_version_number                 IN     NUMBER
) IS

    l_success                               VARCHAR2(1) := 'N';

Line: 21

      INSERT INTO HZ_DSS_GROUPS_B (
        dss_group_code,
        rank,
        status,
        last_update_date,
        last_updated_by,
        creation_date,
        created_by,
        last_update_login,
        bes_enable_flag,
        object_version_number
      )
      VALUES (
        DECODE(x_dss_group_code,
               FND_API.G_MISS_CHAR, NULL, x_dss_group_code),
        DECODE(x_rank,
               FND_API.G_MISS_NUM, NULL,
               x_rank),
        DECODE(x_status,
               FND_API.G_MISS_CHAR, 'A',
               NULL, 'A',
               x_status),
        hz_utility_v2pub.last_update_date,
        hz_utility_v2pub.last_updated_by,
        hz_utility_v2pub.creation_date,
        hz_utility_v2pub.created_by,
        hz_utility_v2pub.last_update_login,
        DECODE(x_bes_enable_flag,
               FND_API.G_MISS_CHAR, NULL,
               x_bes_enable_flag),
        DECODE(x_object_version_number,
               FND_API.G_MISS_NUM, NULL,
               x_object_version_number)
      ) RETURNING
        rowid
      INTO
        x_rowid;

Line: 59

      INSERT INTO HZ_DSS_GROUPS_TL (
        dss_group_code,
        language,
        source_lang,
        dss_group_name,
        description,
        last_update_date,
        last_updated_by,
        creation_date,
        created_by,
        last_update_login
      )
        SELECT
        x_dss_group_code,
        L.LANGUAGE_CODE,
        B.LANGUAGE_CODE,
        x_dss_group_name,
        x_description,
        hz_utility_v2pub.last_update_date,
        hz_utility_v2pub.last_updated_by,
        hz_utility_v2pub.creation_date,
        hz_utility_v2pub.created_by,
        hz_utility_v2pub.last_update_login
        FROM
         FND_LANGUAGES L, FND_LANGUAGES B
        WHERE L.INSTALLED_FLAG in ('I', 'B')
         and B.INSTALLED_FLAG = 'B'
         and not exists
         (SELECT NULL
             FROM HZ_DSS_GROUPS_TL T
             WHERE T.dss_group_code = x_dss_group_code AND
             T.LANGUAGE = L.LANGUAGE_CODE);

Line: 99

END Insert_Row;

Line: 101

PROCEDURE Update_Row (
    x_rowid                                 IN OUT NOCOPY VARCHAR2,
 -- x_dss_group_code                        IN     VARCHAR2,
    x_rank                                  IN     NUMBER,
    x_status                                IN     VARCHAR2,
    x_dss_group_name                        IN     VARCHAR2,
    x_description                           IN     VARCHAR2,
    x_bes_enable_flag                       IN     VARCHAR2,
    x_object_version_number                 IN     NUMBER
) IS
l_dss_group_code varchar2(30);

Line: 114

    UPDATE HZ_DSS_GROUPS_B
    SET
      rank =
        DECODE(x_rank,
               NULL, rank,
               FND_API.G_MISS_NUM, NULL,
               x_rank),
      status =
        DECODE(x_status,
               NULL, status,
               FND_API.G_MISS_CHAR, NULL,
               x_status),
      last_update_date = hz_utility_v2pub.last_update_date,
      last_updated_by = hz_utility_v2pub.last_updated_by,
      creation_date = creation_date,
      created_by = created_by,
      last_update_login = hz_utility_v2pub.last_update_login,
      bes_enable_flag =
        DECODE(x_bes_enable_flag,
               NULL, bes_enable_flag,
               FND_API.G_MISS_CHAR, NULL,
               x_bes_enable_flag),
      object_version_number=
        DECODE(x_object_version_number,
               NULL, object_version_number,
               FND_API.G_MISS_NUM, NULL,
               x_object_version_number)
    WHERE rowid = x_rowid
    returning dss_group_code into l_dss_group_code  ;

Line: 147

    UPDATE HZ_DSS_GROUPS_TL
    SET
      source_lang =
        DECODE(USERENV('LANG'),
               NULL, source_lang,
               FND_API.G_MISS_CHAR, NULL,
               USERENV('LANG') ),
      dss_group_name =
        DECODE(x_dss_group_name,
               NULL, dss_group_name,
               FND_API.G_MISS_CHAR, NULL,
               x_dss_group_name),
      description =
        DECODE(x_description,
               NULL, description,
               FND_API.G_MISS_CHAR, NULL,
               x_description),
      last_update_date = hz_utility_v2pub.last_update_date,
      last_updated_by = hz_utility_v2pub.last_updated_by,
      last_update_login = hz_utility_v2pub.last_update_login

    WHERE dss_group_code = l_dss_group_code AND
    USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);

Line: 175

END Update_Row;

Line: 182

    x_last_update_date                      IN     DATE,
    x_last_updated_by                       IN     NUMBER,
    x_creation_date                         IN     DATE,
    x_created_by                            IN     NUMBER,
    x_last_update_login                     IN     NUMBER,
    x_bes_enable_flag                       IN     VARCHAR2,
    x_object_version_number                 IN     NUMBER
) IS

    CURSOR c IS
      SELECT * FROM hz_dss_groups_b
      WHERE rowid = x_rowid
      FOR UPDATE NOWAIT;

Line: 203

      FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');

Line: 218

    AND ( ( Recinfo.last_update_date = x_last_update_date )
        OR ( ( Recinfo.last_update_date IS NULL )
          AND (  x_last_update_date IS NULL ) ) )
    AND ( ( Recinfo.last_updated_by = x_last_updated_by )
        OR ( ( Recinfo.last_updated_by IS NULL )
          AND (  x_last_updated_by IS NULL ) ) )
    AND ( ( Recinfo.creation_date = x_creation_date )
        OR ( ( Recinfo.creation_date IS NULL )
          AND (  x_creation_date IS NULL ) ) )
    AND ( ( Recinfo.created_by = x_created_by )
        OR ( ( Recinfo.created_by IS NULL )
          AND (  x_created_by IS NULL ) ) )
    AND ( ( Recinfo.last_update_login = x_last_update_login )
        OR ( ( Recinfo.last_update_login IS NULL )
          AND (  x_last_update_login IS NULL ) ) )
    AND ( ( Recinfo.bes_enable_flag = x_bes_enable_flag )
        OR ( ( Recinfo.bes_enable_flag IS NULL )
          AND (  x_bes_enable_flag IS NULL ) ) )
    AND ( ( Recinfo.object_version_number = x_object_version_number)
        OR ( ( Recinfo.object_version_number IS NULL )
          AND (  x_object_version_number IS NULL ) ) )
    ) THEN
      RETURN;

Line: 242

      FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');

Line: 248

PROCEDURE Select_Row (
    x_dss_group_code                        IN OUT NOCOPY VARCHAR2,
    x_rank                                  OUT    NOCOPY NUMBER,
    x_status                                OUT    NOCOPY VARCHAR2,
    x_dss_group_name                        OUT     NOCOPY VARCHAR2,
    x_description                           OUT    NOCOPY VARCHAR2,
    x_bes_enable_flag                       OUT    NOCOPY VARCHAR2,
    x_object_version_number                 OUT    NOCOPY NUMBER

) IS
x_dummy1 VARCHAR2(2000); x_dummy2 varchar2(2000);

Line: 261

    SELECT
      NVL(dss_group_code, FND_API.G_MISS_CHAR),
      NVL(rank, FND_API.G_MISS_NUM),
      NVL(status, FND_API.G_MISS_CHAR),
      NVL(bes_enable_flag, FND_API.G_MISS_CHAR),
      NVL(object_version_number, FND_API.G_MISS_NUM)
    INTO
      x_dss_group_code,
      x_rank,
      x_status,
      x_bes_enable_flag,
      x_object_version_number
    FROM HZ_DSS_GROUPS_B
    WHERE dss_group_code = x_dss_group_code;

Line: 277

    SELECT
      NVL(dss_group_code, FND_API.G_MISS_CHAR),
      NVL(dss_group_name, FND_API.G_MISS_CHAR),
      NVL(description, FND_API.G_MISS_CHAR)
    INTO
      x_dss_group_code, x_dummy1, x_dummy2
    FROM HZ_DSS_GROUPS_TL
    WHERE dss_group_code = x_dss_group_code AND
    USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);

Line: 295

END Select_Row;

Line: 297

PROCEDURE Delete_Row (
    x_dss_group_code                        IN     VARCHAR2
) IS
BEGIN

    DELETE FROM HZ_DSS_GROUPS_B
    WHERE dss_group_code = x_dss_group_code;

Line: 309

    DELETE FROM HZ_DSS_GROUPS_TL
    WHERE dss_group_code = x_dss_group_code;

Line: 316

END Delete_Row;

Line: 323

  delete from HZ_DSS_GROUPS_TL T
  where not exists
    (select NULL
    from HZ_DSS_GROUPS_B B
    where B.DSS_GROUP_CODE = T.DSS_GROUP_CODE
    );

Line: 330

  update HZ_DSS_GROUPS_TL T set (
      DSS_GROUP_CODE,
      DESCRIPTION
    ) = (select
      B.DSS_GROUP_NAME,
      B.DESCRIPTION
    from HZ_DSS_GROUPS_TL B
    where B.DSS_GROUP_CODE = T.DSS_GROUP_CODE
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.DSS_GROUP_CODE,
      T.LANGUAGE
  ) in (select
      SUBT.DSS_GROUP_CODE,
      SUBT.LANGUAGE
    from HZ_DSS_GROUPS_TL SUBB, HZ_DSS_GROUPS_TL SUBT
    where SUBB.DSS_GROUP_CODE = SUBT.DSS_GROUP_CODE
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.DSS_GROUP_NAME <> SUBT.DSS_GROUP_NAME
      or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
      or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
      or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
  ));

Line: 354

  insert into HZ_DSS_GROUPS_TL (
   DSS_GROUP_CODE,
   DSS_GROUP_NAME,
    DESCRIPTION,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG
  ) select
    B.DSS_GROUP_CODE,
    B.DSS_GROUP_NAME,
    B.DESCRIPTION,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATED_BY,
    B.CREATION_DATE,
    B.CREATED_BY,
    B.LAST_UPDATE_LOGIN,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from HZ_DSS_GROUPS_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  and not exists
    (select NULL
    from HZ_DSS_GROUPS_TL T
    where T.DSS_GROUP_CODE = B.DSS_GROUP_CODE
    and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 393

  X_LAST_UPDATE_DATE in DATE,
  X_CUSTOM_MODE in VARCHAR2,
  X_RANK in NUMBER,
  X_STATUS in VARCHAR2,
  X_BES_ENABLE_FLAG  IN     VARCHAR2,
  X_OBJECT_VERSION_NUMBER IN     NUMBER
) is
  l_f_luby    number;  -- entity owner in file

Line: 401

  l_f_ludate  date;    -- entity update date in file

Line: 403

  l_db_ludate date;    -- entity update date in db

Line: 416

  l_f_ludate := nvl(x_last_update_date, sysdate);

Line: 419

    select LAST_UPDATED_BY, LAST_UPDATE_DATE
         into l_db_luby, l_db_ludate
         from HZ_DSS_GROUPS_B
         where DSS_GROUP_CODE = x_dss_group_code;

Line: 434

      hz_dss_groups_pkg.update_row (
        X_ROWID                    => L_ROWID,
      --X_DSS_GROUP_CODE           => X_DSS_GROUP_CODE,
        X_RANK                     => X_RANK,
        X_STATUS                   => X_STATUS,
        X_DSS_GROUP_NAME           => X_DSS_GROUP_NAME,
        X_DESCRIPTION          => X_DESCRIPTION,
        X_BES_ENABLE_FLAG      => X_BES_ENABLE_FLAG,
        X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER
      );

Line: 449

      hz_dss_groups_pkg.insert_row(
          x_rowid                => l_rowid,
          x_dss_group_code       => X_DSS_GROUP_CODE,
          x_rank                 =>  x_rank,
          x_status               =>  x_status,
          x_dss_group_name       => X_DSS_GROUP_NAME,
          x_description          => X_DESCRIPTION,
          x_bes_enable_flag      => x_bes_enable_flag,
          x_object_version_number => x_object_version_number
      );

Line: 468

  X_LAST_UPDATE_DATE in DATE,
  X_CUSTOM_MODE in VARCHAR2
) is
  l_f_luby    number;  -- entity owner in file

Line: 472

  l_f_ludate  date;    -- entity update date in file

Line: 474

  l_db_ludate date;    -- entity update date in db

Line: 484

  l_f_ludate := nvl(x_last_update_date, sysdate);

Line: 487

    select LAST_UPDATED_BY, LAST_UPDATE_DATE
         into l_db_luby, l_db_ludate
         from HZ_DSS_GROUPS_TL
         where DSS_GROUP_CODE = x_dss_group_code
           and LANGUAGE = userenv('LANG');

Line: 503

      update HZ_DSS_GROUPS_TL
         set DSS_GROUP_NAME        = nvl(X_DSS_GROUP_NAME,DSS_GROUP_NAME),
             DESCRIPTION       = nvl(X_DESCRIPTION,DESCRIPTION),
             LAST_UPDATE_DATE  = l_f_ludate,
             LAST_UPDATED_BY   = l_f_luby,
             LAST_UPDATE_LOGIN = 0,
             SOURCE_LANG       = userenv('LANG')
       where DSS_GROUP_CODE = X_DSS_GROUP_CODE
         and userenv('LANG') in (LANGUAGE, SOURCE_LANG);