APPS.AMS_CELL_PVT SQL Statements

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

Line: 41

   SELECT ams_cells_all_b_s.NEXTVAL
     FROM DUAL;

Line: 45

   SELECT COUNT(*)
     FROM ams_cells_vl ACV
    WHERE ACV.cell_id = p_cell_id;

Line: 50

       SELECT user_status_id
       FROM ams_user_statuses_vl
       WHERE system_status_type = 'AMS_LIST_SEGMENT_STATUS'
       AND system_status_code = 'DRAFT'
       AND enabled_flag = 'Y'
       AND default_flag = 'Y';

Line: 120

   AMS_Utility_PVT.debug_message(l_full_name ||': insert');

Line: 123

   INSERT INTO ams_cells_all_b(
      cell_id,
      sel_type,
      last_update_date,
      last_updated_by,
      creation_date,
      created_by,
      last_update_login,
      object_version_number,
      cell_code,
      MARKET_SEGMENT_FLAG,
      ENABLED_FLAG,
      ORIGINAL_SIZE,
      PARENT_CELL_ID,
      owner_id,
      ORG_ID,
      user_status_id,
      status_code,
      status_date,
      country
    )
    VALUES(
      l_cell_rec.cell_id,
      l_cell_rec.sel_type,
      SYSDATE,
      FND_GLOBAL.user_id,
      SYSDATE,
      FND_GLOBAL.user_id,
      FND_GLOBAL.conc_login_id,
      1,  -- object_version_number
      l_cell_rec.cell_code,
      'Y', -- always to be true for segment
      NVL(l_cell_rec.enabled_flag,'Y'),
      l_cell_rec.ORIGINAL_SIZE  ,
      l_cell_rec.PARENT_CELL_ID,
      l_cell_rec.owner_id,--FND_GLOBAL.user_id,
      TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10)),
      NVL(l_cell_rec.user_status_id, 400),
      NVL(l_cell_rec.status_code, 'DRAFT'),
      SYSDATE,
      FND_PROFILE.value ('AMS_SRCGEN_USER_CITY')
   );

Line: 166

   INSERT INTO ams_cells_all_tl(
      cell_id,
      language,
      last_update_date,
      last_updated_by,
      creation_date,
      created_by,
      last_update_login,
      source_lang,
      cell_name,
      description
   )
   SELECT
      l_cell_rec.cell_id,
      l.language_code,
      SYSDATE,
      FND_GLOBAL.user_id,
      SYSDATE,
      FND_GLOBAL.user_id,
      FND_GLOBAL.conc_login_id,
      USERENV('LANG'),
      l_cell_rec.cell_name,
      l_cell_rec.description
   FROM fnd_languages l
   WHERE l.installed_flag in ('I', 'B')
   AND NOT EXISTS(
         SELECT NULL
         FROM ams_cells_all_tl t
         WHERE t.cell_id = l_cell_rec.cell_id
         AND t.language = l.language_code );

Line: 256

PROCEDURE delete_cell(
   p_api_version       IN  NUMBER,
   p_init_msg_list     IN  VARCHAR2 := FND_API.g_false,
   p_commit            IN  VARCHAR2 := FND_API.g_false,

   x_return_status     OUT NOCOPY VARCHAR2,
   x_msg_count         OUT NOCOPY NUMBER,
   x_msg_data          OUT NOCOPY VARCHAR2,

   p_cell_id           IN  NUMBER,
   p_object_version    IN  NUMBER
)
IS

   l_api_version CONSTANT NUMBER       := 1.0;

Line: 271

   l_api_name    CONSTANT VARCHAR2(30) := 'delete_cell';

Line: 277

   SAVEPOINT delete_cell;

Line: 297

   AMS_Utility_PVT.debug_message(l_full_name ||': delete');

Line: 299

        UPDATE ams_cells_all_b
      SET enabled_flag = 'N'
    WHERE cell_id = p_cell_id
      AND object_version_number = p_object_version;

Line: 330

      ROLLBACK TO delete_cell;

Line: 339

      ROLLBACK TO delete_cell;

Line: 348

      ROLLBACK TO delete_cell;

Line: 362

END delete_cell;

Line: 392

   SELECT cell_id
     FROM ams_cells_all_b
    WHERE cell_id = p_cell_id
      AND object_version_number = p_object_version
   FOR UPDATE NOWAIT;

Line: 399

   SELECT cell_id
     FROM ams_cells_all_tl
    WHERE cell_id = p_cell_id
      AND USERENV('LANG') IN (language, source_lang)
   FOR UPDATE NOWAIT;

Line: 508

PROCEDURE update_cell(
   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,

   x_return_status     OUT NOCOPY VARCHAR2,
   x_msg_count         OUT NOCOPY NUMBER,
   x_msg_data          OUT NOCOPY VARCHAR2,

   p_cell_rec          IN  cell_rec_type
)
IS

   l_api_version CONSTANT NUMBER := 1.0;

Line: 523

   l_api_name    CONSTANT VARCHAR2(30) := 'update_cell';

Line: 532

   SAVEPOINT update_cell;

Line: 563

         p_validation_mode => JTF_PLSQL_API.g_update,
         x_return_status   => l_return_status
      );

Line: 575

   AMS_Utility_PVT.debug_message(l_full_name ||': update' ||l_cell_rec.cell_id);

Line: 577

   UPDATE ams_cells_all_b SET
      sel_type = l_cell_rec.sel_type,
      last_update_date = SYSDATE,
      last_updated_by = FND_GLOBAL.user_id,
      last_update_login = FND_GLOBAL.conc_login_id,
      object_version_number = l_cell_rec.object_version_number + 1,
      cell_code =  l_cell_rec.cell_code,
      enabled_flag = NVL(l_cell_rec.enabled_flag,'Y'),
      parent_cell_id = l_cell_rec.parent_cell_id,
      original_size = l_cell_rec.original_size,
      user_status_id = l_cell_rec.user_status_id,
         status_code = l_cell_rec.status_code,
         status_date = l_cell_rec.status_date,
      owner_id = l_cell_rec.owner_id
   WHERE cell_id = l_cell_rec.cell_id
   AND object_version_number = l_cell_rec.object_version_number;

Line: 599

         AMS_Utility_PVT.debug_message(l_full_name ||': update b');

Line: 603

   update ams_cells_all_tl set
      cell_name = l_cell_rec.cell_name,
      description = l_cell_rec.description,
      last_update_date = SYSDATE,
      last_updated_by = FND_GLOBAL.user_id,
      last_update_login = FND_GLOBAL.conc_login_id,
      source_lang = USERENV('LANG')
   WHERE cell_id = l_cell_rec.cell_id
   AND USERENV('LANG') IN (language, source_lang);

Line: 617

            AMS_Utility_PVT.debug_message(l_full_name ||': updatetl');

Line: 638

      ROLLBACK TO update_cell;

Line: 647

      ROLLBACK TO update_cell;

Line: 656

      ROLLBACK TO update_cell;

Line: 670

END update_cell;

Line: 1021

   select  count(1)
   from ams_cells_all_b
   where cell_id in (select a.cell_id
                     from ams_cells_all_b a
                     connect by prior a.cell_id = a.parent_cell_id
                     start with parent_cell_id = l_id)
   and cell_id = l_child_id ;

Line: 1036

      SELECT COUNT(*) INTO l_active_children_count
        FROM ams_cells_vl
       WHERE parent_cell_id = p_cell_rec.cell_id
         AND (status_code = 'DRAFT' or status_code = 'AVAILABLE');

Line: 1155

   x_cell_rec.last_update_date := FND_API.g_miss_date;

Line: 1156

   x_cell_rec.last_updated_by := FND_API.g_miss_num;

Line: 1159

   x_cell_rec.last_update_login := FND_API.g_miss_num;

Line: 1192

   SELECT *
     FROM ams_cells_vl
    WHERE cell_id = p_cell_rec.cell_id;

Line: 1297

     SELECT workbook_owner_name, workbook_name, worksheet_name
       FROM ams_discoverer_sql
      WHERE discoverer_sql_id = l_discoverer_sql_id;

Line: 1306

     SELECT sql_string, sequence_order
          FROM ams_discoverer_sql
         WHERE workbook_name = p_workbook_name
           AND worksheet_name = p_worksheet_name
        AND workbook_owner_name = p_workbook_owner_name
         ORDER BY sequence_order;

Line: 1316

        select ams_act_discoverer_all_s.NEXTVAL
          from DUAL;

Line: 1419

   AMS_Utility_PVT.debug_message(l_full_name ||': insert');

Line: 1425

   INSERT INTO ams_act_discoverer_all(
      activity_discoverer_id,
      last_update_date,
      last_updated_by,
      creation_date,
      created_by,
      last_update_login,
      object_version_number,
      workbook_name,
      workbook_owner,
      act_discoverer_used_by_id,
      arc_act_discoverer_used_by,
      discoverer_sql_id,
      worksheet_name
   )
   VALUES(
      l_act_disc_id,
      SYSDATE,
      FND_GLOBAL.user_id,
         SYSDATE,
      FND_GLOBAL.user_id,
      FND_GLOBAL.conc_login_id,
      1,
      l_discoverer_cell_rec.workbook_name,
      l_discoverer_cell_rec.workbook_owner_name,
      l_cell_id,
      'CELL',
      l_discoverer_sql_id,
      l_discoverer_cell_rec.worksheet_name
      );

Line: 1731

   SELECT workbook_name, worksheet_name, workbook_owner
     FROM ams_act_discoverer_all
    WHERE arc_act_discoverer_used_by = 'CELL'
      AND act_discoverer_used_by_id = p_cell_id;

Line: 1741

   SELECT sql_string, sequence_order
     FROM ams_discoverer_sql
    WHERE workbook_name = p_workbook_name
         AND worksheet_name = p_worksheet_name
         AND workbook_owner_name = p_workbook_owner_name
    ORDER BY sequence_order;

Line: 1775

   SELECT sel_type into l_sel_type
     FROM ams_cells_all_b
    WHERE cell_id = l_cell_id;

Line: 1805

      SELECT query INTO l_sql_string
        FROM ams_list_queries_all
       WHERE upper(arc_act_list_query_used_by) = 'CELL'
         AND act_list_query_used_by_id = l_cell_id;

Line: 1964

   SELECT parent_cell_id INTO l_parent_cell_id
     FROM ams_cells_all_b
    WHERE cell_id = l_cell_id;

Line: 2021

      SELECT parent_cell_id INTO l_parent_cell_id
        FROM ams_cells_all_b
       WHERE cell_id = l_temp_cell_id;

Line: 2169

     x_string := concat('SELECT DISTINCT PARTY_ID ',
                         substr(l_string, instr(upper(l_string), 'FROM')));

Line: 2193

       x_string := 'SELECT DISTINCT ' || l_party_id_str || ' '|| substr(l_string, instr(upper(l_string), 'FROM'));

Line: 2229

   SELECT workbook_name, worksheet_name, workbook_owner_name
     FROM ams_discoverer_sql
    WHERE discoverer_sql_id = p_disc_sql_id;

Line: 2238

   SELECT sql_string, sequence_order
     FROM ams_discoverer_sql
    WHERE workbook_name = p_workbook_name
         AND worksheet_name = p_worksheet_name
         AND workbook_owner_name = p_workbook_owner_name
    ORDER BY sequence_order;

Line: 2412

   l_sql_string := 'SELECT COUNT(*) FROM (' || l_sql_string || ')';

Line: 2578

   l_sql_tbl_new(l_count) := 'select count(*) from ( ';

Line: 2739

   l_cell_rec.last_update_date  := l_sql_cell_rec.last_update_date;

Line: 2740

   l_cell_rec.last_updated_by   := l_sql_cell_rec.last_updated_by;

Line: 2743

   l_cell_rec.last_update_login := l_sql_cell_rec.last_update_login;

Line: 2777

   AMS_Utility_PVT.debug_message(l_full_name ||': add selection');

Line: 2881

PROCEDURE update_sql_cell(
   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,

   x_return_status     OUT NOCOPY VARCHAR2,
   x_msg_count         OUT NOCOPY NUMBER,
   x_msg_data          OUT NOCOPY VARCHAR2,

   p_sql_cell_rec      IN  sqlcell_rec_type
)
IS

   l_api_version CONSTANT NUMBER       := 1.0;

Line: 2896

   l_api_name    CONSTANT VARCHAR2(30) := 'update_sql_cell';

Line: 2907

   SELECT *
     FROM ams_cells_sel_all_v
    WHERE cell_id = p_sql_cell_rec.cell_id;

Line: 2918

      SELECT object_version_number
        FROM AMS_LIST_QUERIES_ALL
       WHERE list_query_id = p_list_query_id;

Line: 2925

   SAVEPOINT update_sql_cell;

Line: 2954

   AMS_Utility_PVT.debug_message(l_full_name ||': update_cell');

Line: 2958

   l_cell_rec.last_update_date  := l_sql_cell_rec.last_update_date;

Line: 2959

   l_cell_rec.last_updated_by   := l_sql_cell_rec.last_updated_by;

Line: 2962

   l_cell_rec.last_update_login := l_sql_cell_rec.last_update_login;

Line: 2977

   update_cell(
      p_api_version        => l_api_version,
      p_init_msg_list      => p_init_msg_list,
      p_validation_level   => p_validation_level,
      p_commit             => FND_API.g_false,
      x_return_status      => l_return_status,
      x_msg_count          => x_msg_count,
      x_msg_data           => x_msg_data,
      p_cell_rec           => l_cell_rec
   );

Line: 2995

   AMS_Utility_PVT.debug_message(l_full_name ||': update selection');

Line: 3013

            DELETE FROM ams_act_discoverer_all
             WHERE act_discoverer_used_by_id = l_sql_cell_rec.cell_id
               AND arc_act_discoverer_used_by = 'CELL';

Line: 3022

               AMS_Utility_PVT.debug_message(l_full_name ||': delete from ams_act_discoverer_all');

Line: 3047

         DELETE FROM ams_act_discoverer_all
          WHERE act_discoverer_used_by_id = l_sql_cell_rec.cell_id
            AND arc_act_discoverer_used_by = 'CELL';

Line: 3056

            AMS_Utility_PVT.debug_message(l_full_name ||': delete from ams_act_discoverer_all');

Line: 3127

      AMS_UTILITY_PVT.debug_message('l_full_name: update_list_query');

Line: 3129

      AMS_List_Query_PVT.Update_List_Query(
            p_api_version_number     => l_api_version,
            p_init_msg_list          => p_init_msg_list,
            p_commit                 => FND_API.g_false,
            p_validation_level       => p_validation_level,
            x_return_status          => l_return_status,
            x_msg_count              => x_msg_count,
            x_msg_data               => x_msg_data,
            p_list_query_rec         => l_list_query_rec,
            x_object_version_number  => l_object_version_number
      );

Line: 3165

      ROLLBACK TO update_sql_cell;

Line: 3173

      ROLLBACK TO update_sql_cell;

Line: 3182

      ROLLBACK TO update_sql_cell;

Line: 3195

END update_sql_cell;

Line: 3213

PROCEDURE Update_Segment_Size
(   p_cell_id        IN    NUMBER DEFAULT NULL,
    x_return_status  OUT NOCOPY   VARCHAR2,
    x_msg_count      OUT NOCOPY   NUMBER,
    x_msg_data       OUT NOCOPY   VARCHAR2
)
IS
   l_api_name         CONSTANT VARCHAR2(30) := 'Update_Segment_Size';

Line: 3238

   SELECT cell_id, object_version_number
     FROM ams_cells_vl;

Line: 3251

     SAVEPOINT Update_Segment_Size;

Line: 3274

     AMS_Utility_PVT.Debug_Message(l_api_name||' update_cell');

Line: 3276

     UPDATE ams_cells_all_b
        SET object_version_number  = object_version_number + 1 ,
              original_size        = l_segment_size
      WHERE cell_id = l_cell_id;

Line: 3287

     SAVEPOINT Update_Segment_Size;

Line: 3328

       UPDATE ams_cells_all_b
          SET object_version_number  = t_object_version_number(i) ,
              original_size          = t_segment_size(i)
        WHERE cell_id = t_cell_id(i);

Line: 3342

      ROLLBACK TO Update_Segment_Size;

Line: 3353

      ROLLBACK TO Update_Segment_Size;

Line: 3364

      ROLLBACK TO Update_Segment_Size;

Line: 3376

END Update_Segment_Size;