APPS.AMS_ACTRESOURCE_PVT SQL Statements

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

Line: 81

   SELECT ams_act_resources_s.NEXTVAL
   FROM dual;

Line: 85

   SELECT start_date_time FROM ams_agendas_b
   WHERE agenda_id = id_in
   AND agenda_type = type_in;

Line: 90

   SELECT system_status_code FROM ams_user_statuses_v
   WHERE user_status_id = id_in;

Line: 191

   INSERT INTO AMS_ACT_RESOURCES
   (
      activity_resource_id,
      last_update_date,
      last_updated_by,
      creation_date,
      created_by,
      last_update_login,
      object_version_number,
      act_resource_used_by_id,
      arc_act_resource_used_by,
      resource_id,
      role_cd,
      user_status_id,
      SYSTEM_STATUS_CODE,
      start_date_time,
      end_date_time,
      description,
      --TOP_LEVEL_PARENT_ID
      --TOP_LEVEL_PARENT_TYPE
      attribute_category,
      attribute1,
      attribute2,
      attribute3,
      attribute4,
      attribute5,
      attribute6,
      attribute7,
      attribute8,
      attribute9,
      attribute10,
      attribute11,
      attribute12,
      attribute13,
      attribute14,
      attribute15
   )
   VALUES
   (
      l_act_resource_rec.activity_resource_id,
      sysdate,
      FND_GLOBAL.User_Id,
      sysdate,
      FND_GLOBAL.User_Id,
      FND_GLOBAL.Conc_Login_Id,
      1,  -- object_version_number
      l_act_Resource_rec.act_resource_used_by_id,
      l_act_Resource_rec.arc_act_resource_used_by,
      l_act_Resource_rec.resource_id,
      l_act_Resource_rec.role_cd,
      l_act_resource_rec.user_status_id,
      l_act_resource_rec.system_status_code,
      l_act_resource_rec.start_date_time,
      l_act_resource_rec.end_date_time,
      l_act_resource_rec.description,
      --l_act_resource_rec.top_level_parent_id,
      --l_act_resource_rec.top_level_parent_type,
      l_act_Resource_rec.attribute_category,
      l_act_Resource_rec.attribute1,
      l_act_Resource_rec.attribute2,
      l_act_Resource_rec.attribute3,
      l_act_Resource_rec.attribute4,
      l_act_Resource_rec.attribute5,
      l_act_Resource_rec.attribute6,
      l_act_Resource_rec.attribute7,
      l_act_Resource_rec.attribute8,
      l_act_Resource_rec.attribute9,
      l_act_Resource_rec.attribute10,
      l_act_Resource_rec.attribute11,
      l_act_Resource_rec.attribute12,
      l_act_Resource_rec.attribute13,
      l_act_Resource_rec.attribute14,
      l_act_Resource_rec.attribute15
   );

Line: 326

PROCEDURE Update_Act_Resource
( 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_act_Resource_rec IN  act_Resource_rec_type
) IS

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

Line: 348

   SELECT start_date_time FROM ams_agendas_b
   WHERE agenda_id = id_in
   AND agenda_type = type_in;

Line: 353

   SELECT system_status_code FROM ams_user_statuses_v
   WHERE user_status_id = id_in;

Line: 358

      SAVEPOINT Update_Act_Resource_PVT;

Line: 442

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

Line: 466

   update AMS_ACT_RESOURCES
   set
     last_update_date = sysdate
     ,last_updated_by =  FND_GLOBAL.User_Id
     ,last_update_login = FND_GLOBAL.Conc_Login_Id
     ,object_version_number = l_act_Resource_rec.object_version_number+1
     ,act_resource_used_by_id = l_act_resource_rec.act_resource_used_by_id
     ,arc_act_resource_used_by = l_act_resource_rec.arc_act_resource_used_by
     ,resource_id = l_act_resource_rec.resource_id
     ,role_cd = l_act_resource_rec.role_cd
     ,user_status_id = l_act_resource_rec.user_status_id
     ,system_status_code = l_act_resource_rec.system_status_code
     ,start_date_time    = l_act_resource_rec.start_date_time
     ,end_date_time      = l_act_resource_rec.end_date_time
     ,description = l_act_resource_rec.description
     --,top_level_parten_id = l_act_resource_rec.top_level_parten_id
     --,top_level_parten_type = l_act_resource_rec.top_level_parten_type
     ,attribute_category = l_act_Resource_rec.attribute_category
     ,attribute1 = l_act_Resource_rec.attribute1
     ,attribute2 = l_act_Resource_rec.attribute2
     ,attribute3 = l_act_Resource_rec.attribute3
     ,attribute4 = l_act_Resource_rec.attribute4
     ,attribute5 = l_act_Resource_rec.attribute5
     ,attribute6 = l_act_Resource_rec.attribute6
     ,attribute7 = l_act_Resource_rec.attribute7
     ,attribute8 = l_act_Resource_rec.attribute8
     ,attribute9 = l_act_Resource_rec.attribute9
     ,attribute10 = l_act_Resource_rec.attribute10
     ,attribute11 = l_act_Resource_rec.attribute11
     ,attribute12 = l_act_Resource_rec.attribute12
     ,attribute13 = l_act_Resource_rec.attribute13
     ,attribute14 = l_act_Resource_rec.attribute14
     ,attribute15 = l_act_Resource_rec.attribute15
   WHERE activity_resource_id = l_act_Resource_rec.activity_resource_id
   AND object_version_number = l_act_Resource_rec.object_version_number;

Line: 525

           ROLLBACK TO Update_Act_Resource_PVT;

Line: 534

           ROLLBACK TO Update_Act_Resource_PVT;

Line: 543

           ROLLBACK TO Update_Act_Resource_PVT;

Line: 556

END Update_Act_Resource;

Line: 572

PROCEDURE Delete_Act_Resource
( 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_act_Resource_id  IN     NUMBER,
  p_object_version   IN     NUMBER
) IS

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

Line: 593

   SAVEPOINT Delete_Act_Resource_PVT;

Line: 615

    DELETE FROM ams_act_resources
    WHERE ACTIVITY_RESOURCE_ID = p_act_Resource_id
    AND p_object_version  = p_object_version;

Line: 642

           ROLLBACK TO Delete_Act_Resource_PVT;

Line: 650

           ROLLBACK TO Delete_Act_Resource_PVT;

Line: 659

           ROLLBACK TO Delete_Act_Resource_PVT;

Line: 673

END Delete_Act_Resource;

Line: 705

     SELECT activity_resource_id
     FROM AMS_ACT_RESOURCES
     WHERE activity_resource_id = p_act_resource_id
     AND object_version_number = p_object_version
     FOR UPDATE of activity_resource_id NOWAIT;

Line: 987

   SELECT coordinator_id
   FROM ams_agendas_v
   WHERE agenda_id = l_session_id;

Line: 1362

   SELECT start_date_time, end_date_time
   FROM   AMS_agendas_b
   WHERE  agenda_id = id_in
   AND    agenda_TYPE = type_in;

Line: 1368

   SELECT event_start_date_time, event_end_date_time
   FROM   ams_event_offers_all_b
   WHERE  event_offer_id = id_in
   AND    event_object_type = type_in;

Line: 1374

   SELECT count(event_offer_id)
   FROM  ams_event_offers_all_b
   WHERE system_status_code IN ('COMPLETED', 'CANCELLED', 'ON_HOLD','ARCHIVED','CLOSED')
   AND event_offer_id = p_act_Resource_rec.act_resource_used_by_id;

Line: 1380

   SELECT count(event_offer_id)
   FROM  ams_event_offers_all_b
   WHERE system_status_code IN ('COMPLETED', 'CANCELLED', 'ON_HOLD','ARCHIVED','CLOSED')
   AND event_offer_id = ( SELECT parent_id
                          FROM   ams_agendas_b
                          WHERE  agenda_id = ( SELECT parent_id
                                               FROM   ams_agendas_b
                                               WHERE  agenda_id = p_act_Resource_rec.act_resource_used_by_id));

Line: 1690

           SELECT 1 into l_count
           FROM   ams_act_resources_v
           WHERE  act_resource_used_by_id  =  p_act_Resource_rec.act_resource_used_by_id
           AND arc_act_resource_used_by  =  p_act_Resource_rec.arc_act_resource_used_by
           AND resource_id  =  p_act_Resource_rec.resource_id
           AND system_status_code = 'CONFIRMED'
         --  AND system_status_code  =  p_act_Resource_rec.system_status_code
           AND
           (start_date_time  BETWEEN  p_act_Resource_rec.start_date_time AND  p_act_Resource_rec.end_date_time
             OR
            end_date_time  BETWEEN  p_act_Resource_rec.start_date_time AND  p_act_Resource_rec.end_date_time
             OR
            p_act_Resource_rec.start_date_time  BETWEEN  start_date_time AND  end_date_time);

Line: 1724

   end update by dbiswas on Apr 28, 2003
*/
           x_return_status := FND_API.G_RET_STS_SUCCESS;

Line: 1752

   x_act_Resource_rec.last_update_date          := FND_API.g_miss_date;

Line: 1753

   x_act_Resource_rec.last_updated_by           := FND_API.g_miss_num;

Line: 1756

   x_act_Resource_rec.last_update_login         := FND_API.g_miss_num;

Line: 1787

   SELECT *
   FROM ams_act_resources
   WHERE activity_resource_id = p_act_Resource_rec.activity_resource_id;

Line: 1819

   IF p_act_Resource_rec.last_updated_by = FND_API.g_miss_num THEN
      x_act_Resource_rec.last_updated_by := l_act_Resource_rec.last_updated_by;

Line: 1823

   IF p_act_Resource_rec.last_update_date = FND_API.g_miss_date THEN
      x_act_Resource_rec.last_update_date := l_act_Resource_rec.last_update_date;

Line: 1980

   SELECT count(*)
   FROM ams_act_resources
   WHERE resource_id = id_in
   AND arc_act_resource_used_by = 'SESSION'
   AND system_status_code = 'CONFIRMED'
   AND role_cd <> 'COORDINATOR'
   AND( p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
   OR  p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
   OR  start_date_time BETWEEN  p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);

Line: 1990

   CURSOR C_check_sessions_update IS
   SELECT count(*)
   FROM ams_act_resources
   WHERE resource_id = p_act_Resource_rec.resource_id
   AND activity_resource_id <> p_act_Resource_rec.activity_resource_id
   AND arc_act_resource_used_by = 'SESSION'
   AND system_status_code = 'CONFIRMED'
   AND role_cd <> 'COORDINATOR'
   AND (p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
   OR  p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
   OR  start_date_time BETWEEN  p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);

Line: 2003

   SELECT count(*)
   FROM ams_act_resources
   WHERE resource_id = p_act_Resource_rec.resource_id
   AND act_resource_used_by_id <>  ( SELECT parent_id
                                     FROM ams_agendas_b
                                     WHERE agenda_id = (SELECT parent_id
                                                        FROM ams_agendas_b
                                                        WHERE agenda_id = p_act_Resource_rec.act_resource_used_by_id))
   AND arc_act_resource_used_by IN ('EVEO', 'EONE')
   AND system_status_code = 'CONFIRMED'
   AND role_cd <> 'COORDINATOR'
   AND (p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
   OR  p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
   OR  start_date_time BETWEEN  p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);

Line: 2020

   SELECT count(*)
   FROM ams_act_resources
   WHERE resource_id = p_act_Resource_rec.resource_id
   AND arc_act_resource_used_by IN ('EVEO', 'EONE')
   AND system_status_code = 'CONFIRMED'
   AND role_cd <> 'COORDINATOR'
   AND (p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
   OR  p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
   OR  start_date_time BETWEEN  p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);

Line: 2030

   CURSOR C_check_events_update  IS
   SELECT count(*)
   FROM ams_act_resources
   WHERE resource_id = p_act_Resource_rec.resource_id
   AND arc_act_resource_used_by IN ('EVEO', 'EONE')
   AND activity_resource_id <> p_act_Resource_rec.activity_resource_id
   AND system_status_code = 'CONFIRMED'
   AND role_cd <> 'COORDINATOR'
   AND (p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
   OR  p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
   OR  start_date_time BETWEEN  p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);

Line: 2044

   SELECT count(*)
   FROM ams_act_resources
   WHERE  arc_act_resource_used_by = 'SESSION'
   AND system_status_code = 'CONFIRMED'
   AND role_cd <> 'COORDINATOR'
   AND act_resource_used_by_id IN ( SELECT agenda_id
                                   FROM ams_agendas_b
                                   WHERE parent_id <> p_act_Resource_rec.act_resource_used_by_id
                                   AND  parent_type IN ('EVEO', 'EONE'))
   AND( p_act_Resource_rec.start_date_time BETWEEN start_date_time AND end_date_time
   OR  p_act_Resource_rec.end_date_time BETWEEN start_date_time AND end_date_time
   OR  start_date_time BETWEEN  p_act_Resource_rec.start_date_time AND p_act_Resource_rec.end_date_time);

Line: 2075

          ELSIF(p_validation_mode = Jtf_Plsql_Api.g_update)
          THEN
              /* checking across all the sessions excluding itself for date overlap */
              OPEN  C_check_sessions_update;

Line: 2079

              FETCH C_check_sessions_update INTO l_count;

Line: 2080

              CLOSE C_check_sessions_update;

Line: 2135

          ELSIF(p_validation_mode = Jtf_Plsql_Api.g_update)
          THEN
              /* checking across all the events except itself, for date overlap */
              OPEN  C_check_events_update;

Line: 2139

              FETCH C_check_events_update INTO l_count;

Line: 2140

              CLOSE C_check_events_update;