APPS.AMS_CHANNEL_PVT SQL Statements

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

Line: 65

   SELECT ams_channels_b_s.NEXTVAL
     FROM DUAL;

Line: 69

   SELECT COUNT(*)
     FROM ams_channels_vl
    WHERE channel_id = chan_id;

Line: 140

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

Line: 149

   INSERT INTO ams_channels_b(
      channel_id,
      last_update_date,
      last_updated_by,
      creation_date,
      created_by,
      last_update_login,
      object_version_number,
      channel_type_code,
      order_sequence,
      managed_by_person_id,
      outbound_flag,
      inbound_flag,
      active_from_date,
      active_to_date,
      rating,
      preferred_vendor_id,
      party_id,
      attribute_category,
      attribute1,
      attribute2,
      attribute3,
      attribute4,
      attribute5,
      attribute6,
      attribute7,
      attribute8,
      attribute9,
      attribute10,
      attribute11,
      attribute12,
      attribute13,
      attribute14,
      attribute15,
      --rrajesh added on 12/07/00
      country_id
      -- Rahul Sharma  removed on 01/18/2001
      --internal_resource
      --end 12/07/00
	)
	VALUES(
      l_chan_rec.channel_id,
      SYSDATE,
      FND_GLOBAL.user_id,
      SYSDATE,
      FND_GLOBAL.user_id,
      FND_GLOBAL.conc_login_id,
      1,  -- object_version_number
      l_chan_rec.channel_type_code,
      l_chan_rec.order_sequence,
           -- no validation for order.This column is for upgrade purposes only
      l_chan_rec.managed_by_person_id,

      -- added by rrajesh on 12/07/00
      --NVL(l_chan_rec.outbound_flag,'Y'),
      --NVL(l_chan_rec.inbound_flag,'N'),
      NVL(l_chan_rec.outbound_flag,'Y'),
      NVL(l_chan_rec.inbound_flag,'Y'),
      --end 12/07/00

      NVL(l_chan_rec.active_from_date,SYSDATE),
            -- no validation for active_for_date .This column is for upgrade purposes only
      l_chan_rec.active_to_date,
            -- no validation for active_to_date .This column is for upgrade purposes only
      l_chan_rec.rating,
      l_chan_rec.preferred_vendor_id,
      l_chan_rec.party_id,
      l_chan_rec.attribute_category,
      l_chan_rec.attribute1,
      l_chan_rec.attribute2,
      l_chan_rec.attribute3,
      l_chan_rec.attribute4,
      l_chan_rec.attribute5,
      l_chan_rec.attribute6,
      l_chan_rec.attribute7,
      l_chan_rec.attribute8,
      l_chan_rec.attribute9,
      l_chan_rec.attribute10,
      l_chan_rec.attribute11,
      l_chan_rec.attribute12,
      l_chan_rec.attribute13,
      l_chan_rec.attribute14,
      l_chan_rec.attribute15,
      --added by rrajesh 12/07/00
      l_chan_rec.country_id
      -- Rahul Sharma removed on 01/18/2001
      --l_chan_rec.internal_resource
      --end 12/07/00
	);

Line: 239

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

Line: 339

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

   l_api_version CONSTANT NUMBER       := 1.0;

Line: 354

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

Line: 359

   SELECT media_id
   FROM   ams_media_channels
   WHERE  channel_id = p_chan_id
   AND    (active_to_date > SYSDATE OR active_to_date IS NULL);

Line: 366

       SELECT marketing_medium_id
       FROM ams_campaign_schedules_b
       WHERE marketing_medium_id = p_chan_id;

Line: 375

   SAVEPOINT delete_channel;

Line: 403

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

Line: 413

         FND_MESSAGE.set_name('AMS', 'AMS_CHAN_CANNOT_DELETE');

Line: 436

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

Line: 439

   DELETE FROM ams_channels_b
   WHERE channel_id = p_chan_id
   AND object_version_number = p_object_version;

Line: 452

   DELETE FROM ams_channels_tl
   WHERE channel_id = p_chan_id;

Line: 486

      ROLLBACK TO delete_channel;

Line: 495

      ROLLBACK TO delete_channel;

Line: 504

      ROLLBACK TO delete_channel;

Line: 518

END delete_channel;

Line: 548

   SELECT channel_id
     FROM ams_channels_b
    WHERE channel_id = p_chan_id
      AND object_version_number = p_object_version
   FOR UPDATE NOWAIT;

Line: 555

   SELECT channel_id
     FROM ams_channels_tl
    WHERE channel_id = p_chan_id
      AND USERENV('LANG') IN (language, source_lang)
   FOR UPDATE NOWAIT;

Line: 675

PROCEDURE update_channel(
   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_chan_rec          IN  chan_rec_type
)
IS

   l_api_version CONSTANT NUMBER := 1.0;

Line: 690

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

Line: 699

   SAVEPOINT update_channel;

Line: 736

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

Line: 769

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

Line: 779

   UPDATE ams_channels_b SET
      last_update_date = SYSDATE,
      last_updated_by = FND_GLOBAL.user_id,
      last_update_login = FND_GLOBAL.conc_login_id,
      object_version_number = l_chan_rec.object_version_number + 1,
      channel_type_code =  l_chan_rec.channel_type_code,
      order_sequence    = l_chan_rec.order_sequence,
      managed_by_person_id = l_chan_rec.managed_by_person_id,
      --rrajesh added 12/07/00
      --outbound_flag =  NVL(l_chan_rec.outbound_flag,'Y'),
      --inbound_flag = NVL(l_chan_rec.inbound_flag,'N'),
      outbound_flag =  NVL(l_chan_rec.outbound_flag,'Y'),
      inbound_flag = NVL(l_chan_rec.inbound_flag,'Y'),
      --end 12/07/00
      active_from_date  = l_chan_rec.active_from_date,
      active_to_date = l_chan_rec.active_to_date,
      rating = l_chan_rec.rating,
      preferred_vendor_id = l_chan_rec.preferred_vendor_id,
      party_id =  l_chan_rec.party_id,
      attribute_category = l_chan_rec.attribute_category,
      attribute1 = l_chan_rec.attribute1,
      attribute2 = l_chan_rec.attribute2,
      attribute3 = l_chan_rec.attribute3,
      attribute4 = l_chan_rec.attribute4,
      attribute5 = l_chan_rec.attribute5,
      attribute6 = l_chan_rec.attribute6,
      attribute7 = l_chan_rec.attribute7,
      attribute8 = l_chan_rec.attribute8,
      attribute9 = l_chan_rec.attribute9,
      attribute10 = l_chan_rec.attribute10,
      attribute11 = l_chan_rec.attribute11,
      attribute12 = l_chan_rec.attribute12,
      attribute13 = l_chan_rec.attribute13,
      attribute14 = l_chan_rec.attribute14,
      attribute15 = l_chan_rec.attribute15,
      --rrajesh added 12/07/00
      country_id = l_chan_rec.country_id
      -- Rahul Sharma removed 01/18/2001
      --internal_resource = l_chan_rec.internal_resource
      -- end 01/18/2001
      --end 12/07/00
   WHERE channel_id = l_chan_rec.channel_id
   AND object_version_number = l_chan_rec.object_version_number;

Line: 831

   update ams_channels_tl set
      channel_name = l_chan_rec.channel_name,
      description = l_chan_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 channel_id = l_chan_rec.channel_id
   AND USERENV('LANG') IN (language, source_lang);

Line: 871

      ROLLBACK TO update_channel;

Line: 880

      ROLLBACK TO update_channel;

Line: 889

      ROLLBACK TO update_channel;

Line: 903

END update_channel;

Line: 1135

	SELECT COUNT(1)
	FROM ams_channels_vl
	WHERE UPPER(channel_name) = UPPER(p_channel_name) ;*/

Line: 1139

	SELECT COUNT(1)
	FROM ams_channels_vl
	WHERE UPPER(channel_name) = UPPER(p_channel_name)
	AND country_id = p_country_id;

Line: 1145

	SELECT COUNT(1)
	FROM ams_channels_vl
	WHERE UPPER(channel_name) = UPPER(p_channel_name)
	AND channel_id <> p_channel_id ;*/

Line: 1150

	SELECT COUNT(1)
	FROM ams_channels_vl
	WHERE UPPER(channel_name) = UPPER(p_channel_name)
	AND country_id = p_country_id
	AND channel_id <> p_channel_id ;

Line: 1600

   x_chan_rec.last_update_date := FND_API.g_miss_date;

Line: 1601

   x_chan_rec.last_updated_by := FND_API.g_miss_num;

Line: 1604

   x_chan_rec.last_update_login := FND_API.g_miss_num;

Line: 1658

   SELECT *
     FROM ams_channels_vl
    WHERE channel_id = p_chan_rec.channel_id;

Line: 1830

      SELECT party_name
      FROM   hz_parties
      WHERE  party_id = p_party_id;

Line: 1865

      SELECT party_number
      FROM   hz_parties
      WHERE  party_id = p_party_id;

Line: 1899

      SELECT party_type
      FROM   hz_parties
      WHERE  party_id = p_party_id;

Line: 1932

      SELECT vendor_name
      FROM   po_vendors
      WHERE  vendor_id = p_vendor_id;

Line: 1965

      SELECT location_area_name
      FROM   jtf_loc_areas_vl
      WHERE location_area_id = p_country_id
      AND location_type_code = 'COUNTRY';