APPS.JTF_DSP_MSITE_SCT_SECT_PVT SQL Statements

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

Line: 43

PROCEDURE insert_row
  (
   p_mini_site_section_section_id       IN NUMBER,
   p_object_version_number              IN NUMBER,
   p_mini_site_id                       IN NUMBER,
   p_parent_section_id                  IN NUMBER,
   p_child_section_id                   IN NUMBER,
   p_start_date_active                  IN DATE,
   p_end_date_active                    IN DATE,
   p_level_number                       IN NUMBER,
   p_sort_order                         IN NUMBER,
   p_concat_ids                         IN VARCHAR2,
   p_creation_date                      IN DATE,
   p_created_by                         IN NUMBER,
   p_last_update_date                   IN DATE,
   p_last_updated_by                    IN NUMBER,
   p_last_update_login                  IN NUMBER,
   x_rowid                              OUT VARCHAR2,
   x_mini_site_section_section_id       OUT NUMBER
  )
IS
  CURSOR c IS SELECT rowid FROM jtf_dsp_msite_sct_sects
    WHERE mini_site_section_section_id = x_mini_site_section_section_id;

Line: 66

  CURSOR c2 IS SELECT jtf_dsp_msite_sct_sects_s1.nextval FROM dual;

Line: 81

  INSERT INTO jtf_dsp_msite_sct_sects
    (
    mini_site_section_section_id,
    object_version_number,
    mini_site_id,
    parent_section_id,
    child_section_id,
    start_date_active,
    end_date_active,
    level_number,
    sort_order,
    concat_ids,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by,
    last_update_login
    )
    VALUES
    (
    x_mini_site_section_section_id,
    p_object_version_number,
    p_mini_site_id,
    decode(p_parent_section_id, FND_API.G_MISS_NUM, NULL, p_parent_section_id),
    p_child_section_id,
    p_start_date_active,
    decode(p_end_date_active, FND_API.G_MISS_DATE, NULL, p_end_date_active),
    decode(p_level_number, FND_API.G_MISS_NUM, NULL, p_level_number),
    decode(p_sort_order, FND_API.G_MISS_NUM, NULL, p_sort_order),
    decode(p_concat_ids, FND_API.G_MISS_CHAR, NULL, p_concat_ids),
    decode(p_creation_date, FND_API.G_MISS_DATE, sysdate, NULL, sysdate,
           p_creation_date),
    decode(p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
           NULL, FND_GLOBAL.user_id, p_created_by),
    decode(p_last_update_date, FND_API.G_MISS_DATE, sysdate, NULL, sysdate,
           p_last_update_date),
    decode(p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
           NULL, FND_GLOBAL.user_id, p_last_updated_by),
    decode(p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.login_id,
           NULL, FND_GLOBAL.login_id, p_last_update_login)
    );

Line: 132

END insert_row;

Line: 138

PROCEDURE update_row
  (
  p_mini_site_section_section_id        IN NUMBER,
  p_object_version_number               IN NUMBER   := FND_API.G_MISS_NUM,
  p_start_date_active                   IN DATE,
  p_end_date_active                     IN DATE,
  p_level_number                        IN NUMBER,
  p_sort_order                          IN NUMBER,
  p_concat_ids                          IN VARCHAR2,
  p_last_update_date                    IN DATE,
  p_last_updated_by                     IN NUMBER,
  p_last_update_login                   IN NUMBER
  )
IS
BEGIN

  -- update base
  UPDATE jtf_dsp_msite_sct_sects SET
    object_version_number = object_version_number + 1,
    level_number = decode(p_level_number, FND_API.G_MISS_NUM,
                          level_number, p_level_number),
    sort_order = decode(p_sort_order, FND_API.G_MISS_NUM,
                        sort_order, p_sort_order),
    concat_ids = decode(p_concat_ids, FND_API.G_MISS_CHAR,
                        concat_ids, p_concat_ids),
    start_date_active = decode(p_start_date_active, FND_API.G_MISS_DATE,
                               start_date_active, p_start_date_active),
    end_date_active = decode(p_end_date_active, FND_API.G_MISS_DATE,
                             end_date_active, p_end_date_active),
    last_update_date = decode(p_last_update_date, FND_API.G_MISS_DATE,
                              sysdate, NULL, sysdate, p_last_update_date),
    last_updated_by = decode(p_last_updated_by, FND_API.G_MISS_NUM,
                             FND_GLOBAL.user_id, NULL, FND_GLOBAL.user_id,
                             p_last_updated_by),
    last_update_login = decode(p_last_update_login, FND_API.G_MISS_NUM,
                               FND_GLOBAL.login_id, NULL,
                               FND_GLOBAL.login_id, p_last_update_login)
    WHERE mini_site_section_section_id = p_mini_site_section_section_id
      AND object_version_number = decode(p_object_version_number,
                                         FND_API.G_MISS_NUM,
                                         object_version_number,
                                         p_object_version_number);

Line: 185

END update_row;

Line: 192

PROCEDURE delete_row
  (
   p_mini_site_section_section_id IN NUMBER
  )
IS
BEGIN

  DELETE FROM jtf_dsp_msite_sct_sects
    WHERE mini_site_section_section_id = p_mini_site_section_section_id;

Line: 206

END delete_row;

Line: 244

    update_row
      (
      p_mini_site_section_section_id        => p_mini_site_section_section_id,
      p_object_version_number               => p_object_version_number,
      p_start_date_active                   => p_start_date_active,
      p_end_date_active                     => p_end_date_active,
      p_level_number                        => p_level_number,
      p_sort_order                          => p_sort_order,
      p_concat_ids                          => p_concat_ids,
      p_last_update_date                    => sysdate,
      p_last_updated_by                     => l_user_id,
      p_last_update_login                   => 0
      );

Line: 262

       insert_row
       (
       p_mini_site_section_section_id       => p_mini_site_section_section_id,
       p_object_version_number              => l_object_version_number,
       p_mini_site_id                       => p_mini_site_id,
       p_parent_section_id                  => p_parent_section_id,
       p_child_section_id                   => p_child_section_id,
       p_start_date_active                  => p_start_date_active,
       p_end_date_active                    => p_end_date_active,
       p_level_number                       => p_level_number,
       p_sort_order                         => p_sort_order,
       p_concat_ids                         => p_concat_ids,
       p_creation_date                      => sysdate,
       p_created_by                         => l_user_id,
       p_last_update_date                   => sysdate,
       p_last_updated_by                    => l_user_id,
       p_last_update_login                  => 0,
       x_rowid                              => l_rowid,
       x_mini_site_section_section_id       => l_mini_site_section_section_id
       );

Line: 340

      SELECT mini_site_section_section_id INTO l_tmp_msite_sct_sect_id
        FROM  jtf_dsp_msite_sct_sects
        WHERE mini_site_id = p_mini_site_id
          AND parent_section_id = p_parent_section_id
          AND child_section_id = p_child_section_id;

Line: 348

      SELECT mini_site_section_section_id INTO l_tmp_msite_sct_sect_id
        FROM  jtf_dsp_msite_sct_sects
        WHERE mini_site_id = p_mini_site_id
          AND parent_section_id IS NULL
          AND child_section_id = p_child_section_id;

Line: 508

    SELECT msite_id INTO l_mini_site_id FROM jtf_msites_b
      WHERE msite_id = p_mini_site_id;

Line: 528

      SELECT section_id INTO l_parent_section_id FROM jtf_dsp_sections_b
        WHERE section_id = p_parent_section_id;

Line: 547

    SELECT section_id INTO l_child_section_id FROM jtf_dsp_sections_b
      WHERE section_id = p_child_section_id;

Line: 614

PROCEDURE Validate_Update
  (
   p_init_msg_list                  IN VARCHAR2 := FND_API.G_FALSE,
   p_validation_level               IN NUMBER   := FND_API.G_VALID_LEVEL_FULL,
   p_mini_site_section_section_id   IN NUMBER,
   p_object_version_number          IN NUMBER,
   p_start_date_active              IN DATE,
   p_end_date_active                IN DATE,
   p_level_number                   IN NUMBER,
   p_sort_order                     IN NUMBER,
   p_concat_ids                     IN VARCHAR2,
   x_return_status                  OUT VARCHAR2,
   x_msg_count                      OUT NUMBER,
   x_msg_data                       OUT VARCHAR2
  )
IS
  l_api_name              CONSTANT VARCHAR2(30) := 'Validate_Update';

Line: 721

END Validate_Update;

Line: 817

    insert_row
      (
      FND_API.G_MISS_NUM,
      l_object_version_number,
      p_mini_site_id,
      p_parent_section_id,
      p_child_section_id,
      p_start_date_active,
      p_end_date_active,
      p_level_number,
      p_sort_order,
      p_concat_ids,
      SYSDATE,
      FND_GLOBAL.user_id,
      SYSDATE,
      FND_GLOBAL.user_id,
      FND_GLOBAL.login_id,
      l_rowid,
      x_mini_site_section_section_id
      );

Line: 839

       FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_MSS_INSERT_FAIL');

Line: 843

       FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_MSS_INSERT_FAIL');

Line: 892

PROCEDURE Update_MSite_Section_Section
  (
   p_api_version                    IN NUMBER,
   p_init_msg_list                  IN VARCHAR2 := FND_API.G_FALSE,
   p_commit                         IN VARCHAR2 := FND_API.G_FALSE,
   p_validation_level               IN NUMBER   := FND_API.G_VALID_LEVEL_FULL,
   p_mini_site_section_section_id   IN NUMBER   := FND_API.G_MISS_NUM,
   p_object_version_number          IN NUMBER,
   p_mini_site_id                   IN NUMBER   := FND_API.G_MISS_NUM,
   p_parent_section_id              IN NUMBER   := FND_API.G_MISS_NUM,
   p_child_section_id               IN NUMBER   := FND_API.G_MISS_NUM,
   p_start_date_active              IN DATE     := FND_API.G_MISS_DATE,
   p_end_date_active                IN DATE     := FND_API.G_MISS_DATE,
   p_level_number                   IN NUMBER   := FND_API.G_MISS_NUM,
   p_sort_order                     IN NUMBER   := FND_API.G_MISS_NUM,
   p_concat_ids                     IN VARCHAR2 := FND_API.G_MISS_CHAR,
   x_return_status                  OUT VARCHAR2,
   x_msg_count                      OUT NUMBER,
   x_msg_data                       OUT VARCHAR2
  )
IS
  l_api_name          CONSTANT VARCHAR2(30) := 'Update_MSite_Section_Section';

Line: 924

  SAVEPOINT  UPDATE_MSITE_SCT_SECT_PVT;

Line: 968

        SELECT mini_site_section_section_id INTO l_msite_sct_sect_id
          FROM jtf_dsp_msite_sct_sects
          WHERE mini_site_id = p_mini_site_id
            AND parent_section_id = p_parent_section_id
            AND child_section_id = p_child_section_id;

Line: 975

        SELECT mini_site_section_section_id INTO l_msite_sct_sect_id
          FROM jtf_dsp_msite_sct_sects
          WHERE mini_site_id = p_mini_site_id
            AND parent_section_id IS NULL
            AND child_section_id = p_child_section_id;

Line: 1019

  Validate_Update
    (
    p_init_msg_list                  => FND_API.G_FALSE,
    p_validation_level               => p_validation_level,
    p_mini_site_section_section_id   => l_msite_sct_sect_id,
    p_object_version_number          => p_object_version_number,
    p_start_date_active              => p_start_date_active,
    p_end_date_active                => p_end_date_active,
    p_level_number                   => p_level_number,
    p_sort_order                     => p_sort_order,
    p_concat_ids                     => p_concat_ids,
    x_return_status                  => l_return_status,
    x_msg_count                      => l_msg_count,
    x_msg_data                       => l_msg_data
    );

Line: 1038

    FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_MSS_INVALID_UPDATE');

Line: 1045

    update_row
      (
      l_msite_sct_sect_id,
      p_object_version_number,
      p_start_date_active,
      p_end_date_active,
      p_level_number,
      p_sort_order,
      p_concat_ids,
      SYSDATE,
      FND_GLOBAL.user_id,
      FND_GLOBAL.login_id
      );

Line: 1060

       FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_MSS_UPDATE_FAIL');

Line: 1064

       FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_MSS_UPDATE_FAIL');

Line: 1085

     ROLLBACK TO UPDATE_MSITE_SCT_SECT_PVT;

Line: 1092

     ROLLBACK TO UPDATE_MSITE_SCT_SECT_PVT;

Line: 1099

     ROLLBACK TO UPDATE_MSITE_SCT_SECT_PVT;

Line: 1111

END Update_MSite_Section_Section;

Line: 1114

PROCEDURE Delete_MSite_Section_Section
  (
   p_api_version                  IN NUMBER,
   p_init_msg_list                IN VARCHAR2    := FND_API.G_FALSE,
   p_commit                       IN VARCHAR2    := FND_API.G_FALSE,
   p_validation_level             IN NUMBER      := FND_API.G_VALID_LEVEL_FULL,
   p_mini_site_section_section_id IN NUMBER      := FND_API.G_MISS_NUM,
   p_mini_site_id                 IN NUMBER      := FND_API.G_MISS_NUM,
   p_parent_section_id            IN NUMBER      := FND_API.G_MISS_NUM,
   p_child_section_id             IN NUMBER      := FND_API.G_MISS_NUM,
   x_return_status                OUT VARCHAR2,
   x_msg_count                    OUT NUMBER,
   x_msg_data                     OUT VARCHAR2
  )
IS
  l_api_name          CONSTANT VARCHAR2(30)  := 'Delete_MSite_Section_Section';

Line: 1135

  SAVEPOINT  DELETE_MSITE_SCT_SECT_PVT;

Line: 1177

        SELECT mini_site_section_section_id INTO l_msite_sct_sect_id
          FROM jtf_dsp_msite_sct_sects
          WHERE mini_site_id = p_mini_site_id
            AND parent_section_id = p_parent_section_id
            AND child_section_id = child_section_id;

Line: 1184

        SELECT mini_site_section_section_id INTO l_msite_sct_sect_id
          FROM jtf_dsp_msite_sct_sects
          WHERE mini_site_id = p_mini_site_id
            AND parent_section_id IS NULL
            AND child_section_id = child_section_id;

Line: 1226

  delete_row(l_msite_sct_sect_id);

Line: 1231

     ROLLBACK TO DELETE_MSITE_SCT_SECT_PVT;

Line: 1238

     ROLLBACK TO DELETE_MSITE_SCT_SECT_PVT;

Line: 1245

     ROLLBACK TO DELETE_MSITE_SCT_SECT_PVT;

Line: 1257

END Delete_MSite_Section_Section;

Line: 1259

PROCEDURE Update_MSite_Section_Sections
  (
   p_api_version                    IN NUMBER,
   p_init_msg_list                  IN VARCHAR2 := FND_API.G_FALSE,
   p_commit                         IN VARCHAR2 := FND_API.G_FALSE,
   p_validation_level               IN NUMBER   := FND_API.G_VALID_LEVEL_FULL,
   p_msite_section_section_ids      IN JTF_NUMBER_TABLE,
   p_object_version_numbers         IN JTF_NUMBER_TABLE,
   p_start_date_actives             IN JTF_DATE_TABLE,
   p_end_date_actives               IN JTF_DATE_TABLE,
   p_sort_orders                    IN JTF_NUMBER_TABLE,
   x_return_status                  OUT VARCHAR2,
   x_msg_count                      OUT NUMBER,
   x_msg_data                       OUT VARCHAR2
  )
IS
  l_api_name          CONSTANT VARCHAR2(30) := 'Update_MSite_Section_Sections';

Line: 1286

  SAVEPOINT  UPDATE_MSITE_SCT_SECTS_PVT;

Line: 1310

    Update_MSite_Section_Section
      (
      p_api_version                    => p_api_version,
      p_init_msg_list                  => FND_API.G_FALSE,
      p_commit                         => FND_API.G_FALSE,
      p_validation_level               => p_validation_level,
      p_mini_site_section_section_id   => p_msite_section_section_ids(i),
      p_object_version_number          => p_object_version_numbers(i),
      p_mini_site_id                   => FND_API.G_MISS_NUM,
      p_parent_section_id              => FND_API.G_MISS_NUM,
      p_child_section_id               => FND_API.G_MISS_NUM,
      p_start_date_active              => p_start_date_actives(i),
      p_end_date_active                => p_end_date_actives(i),
      p_level_number                   => FND_API.G_MISS_NUM,
      p_sort_order                     => p_sort_orders(i),
      p_concat_ids                     => FND_API.G_MISS_CHAR,
      x_return_status                  => l_return_status,
      x_msg_count                      => x_msg_count,
      x_msg_data                       => x_msg_data
      );

Line: 1355

     ROLLBACK TO UPDATE_MSITE_SCT_SECTS_PVT;

Line: 1362

     ROLLBACK TO UPDATE_MSITE_SCT_SECTS_PVT;

Line: 1369

     ROLLBACK TO UPDATE_MSITE_SCT_SECTS_PVT;

Line: 1381

END Update_MSite_Section_Sections;

Line: 1383

PROCEDURE Update_Delete_Sct_Scts
  (
   p_api_version                    IN NUMBER,
   p_init_msg_list                  IN VARCHAR2 := FND_API.G_FALSE,
   p_commit                         IN VARCHAR2 := FND_API.G_FALSE,
   p_validation_level               IN NUMBER   := FND_API.G_VALID_LEVEL_FULL,
   p_msite_section_section_ids      IN JTF_NUMBER_TABLE,
   p_object_version_numbers         IN JTF_NUMBER_TABLE,
   p_start_date_actives             IN JTF_DATE_TABLE,
   p_end_date_actives               IN JTF_DATE_TABLE,
   p_sort_orders                    IN JTF_NUMBER_TABLE,
   p_delete_flags                   IN JTF_VARCHAR2_TABLE_300,
   x_return_status                  OUT VARCHAR2,
   x_msg_count                      OUT NUMBER,
   x_msg_data                       OUT VARCHAR2
  )
IS
  l_api_name          CONSTANT VARCHAR2(30) := 'Update_Delete_Sct_Scts';

Line: 1411

  SAVEPOINT  UPDATE_DELETE_SCT_SCTS_PVT;

Line: 1436

    IF (p_delete_flags(i) = 'Y') THEN

      Delete_MSite_Section_Section
        (
        p_api_version                  => p_api_version,
        p_init_msg_list                => FND_API.G_FALSE,
        p_commit                       => FND_API.G_FALSE,
        p_validation_level             => FND_API.G_VALID_LEVEL_FULL,
        p_mini_site_section_section_id => p_msite_section_section_ids(i),
        p_mini_site_id                 => FND_API.G_MISS_NUM,
        p_parent_section_id            => FND_API.G_MISS_NUM,
        p_child_section_id             => FND_API.G_MISS_NUM,
        x_return_status                => x_return_status,
        x_msg_count                    => x_msg_count,
        x_msg_data                     => x_msg_data
        );

Line: 1461

      Update_Msite_Section_Section
        (
        p_api_version                    => p_api_version,
        p_init_msg_list                  => FND_API.G_FALSE,
        p_commit                         => FND_API.G_FALSE,
        p_validation_level               => p_validation_level,
        p_mini_site_section_section_id   => p_msite_section_section_ids(i),
        p_object_version_number          => p_object_version_numbers(i),
        p_mini_site_id                   => FND_API.G_MISS_NUM,
        p_parent_section_id              => FND_API.G_MISS_NUM,
        p_child_section_id               => FND_API.G_MISS_NUM,
        p_start_date_active              => p_start_date_actives(i),
        p_end_date_active                => p_end_date_actives(i),
        p_level_number                   => FND_API.G_MISS_NUM,
        p_sort_order                     => p_sort_orders(i),
        p_concat_ids                     => FND_API.G_MISS_CHAR,
        x_return_status                  => l_return_status,
        x_msg_count                      => x_msg_count,
        x_msg_data                       => x_msg_data
        );

Line: 1508

     ROLLBACK TO UPDATE_DELETE_SCT_SCTS_PVT;

Line: 1515

     ROLLBACK TO UPDATE_DELETE_SCT_SCTS_PVT;

Line: 1522

     ROLLBACK TO UPDATE_DELETE_SCT_SCTS_PVT;

Line: 1534

END Update_Delete_Sct_Scts;