DBA Data[Home] [Help]

APPS.OZF_ACT_OFFERS_PVT SQL Statements

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

Line: 34

   SELECT offer_code
     FROM ozf_offers
    WHERE qp_list_header_id = l_id;
Line: 51

   SELECT custom_setup_id
     FROM ams_custom_setups_vl
    WHERE object_type = 'OFFR'
      AND activity_type_code = p_act_offer_rec.offer_type;
Line: 57

   SELECT ozf_act_offers_s.NEXTVAL
     FROM DUAL;
Line: 61

   SELECT COUNT(*)
     FROM ozf_act_offers
    WHERE activity_offer_id = act_offer_id;
Line: 163

   INSERT INTO OZF_ACT_OFFERS
   (
      activity_offer_id,
      last_update_date,
      last_updated_by,
      creation_date,
      created_by,
      last_update_login,
      object_version_number,
      act_offer_used_by_id,
      arc_act_offer_used_by,
      primary_offer_flag,
--      offer_type,
      offer_code,
      active_period_set,
      active_period,
--      start_date,
--      end_date,
--      order_date_from,
--      order_date_to,
--      ship_date_from,
--      ship_date_to,
--      perf_date_from,
--      perf_date_to,
--      status_code,
--      status_date,
--      offer_amount,
--      lumpsum_payment_type,
      qp_list_header_id
   )
   VALUES
   (
      l_act_offer_rec.activity_offer_id,
      SYSDATE,
      FND_GLOBAL.user_id,
      SYSDATE,
      FND_GLOBAL.user_id,
      FND_GLOBAL.conc_login_id,
      1,
      l_act_offer_rec.act_offer_used_by_id,
      l_act_offer_rec.arc_act_offer_used_by,
      l_act_offer_rec.primary_offer_flag,
--      l_act_offer_rec.offer_type,
      l_offer_code,
      l_act_offer_rec.active_period_set,
      l_act_offer_rec.active_period,
--      l_act_offer_rec.start_date,
--      l_act_offer_rec.end_date,
--      l_act_offer_rec.order_date_from,
--      l_act_offer_rec.order_date_to,
--      l_act_offer_rec.ship_date_from,
--      l_act_offer_rec.ship_date_to,
--      l_act_offer_rec.perf_date_from,
--      l_act_offer_rec.perf_date_to,
--      l_act_offer_rec.status_code,
--      l_act_offer_rec.status_date,
--      l_act_offer_rec.offer_amount,
--      l_act_offer_rec.lumpsum_payment_type,
      l_act_offer_rec.qp_list_header_id
   );
Line: 351

PROCEDURE Update_Act_Offer
(
   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_offer_rec       IN  act_offer_rec_type
)
IS

   l_api_version     CONSTANT NUMBER := 1.0;
Line: 367

   l_api_name        CONSTANT VARCHAR2(30) := 'update_act_offer';
Line: 378

   SAVEPOINT update_act_offer;
Line: 401

         p_validation_mode => JTF_PLSQL_API.g_update,
         x_return_status    => l_return_status,
         p_act_offer_rec    => l_act_offer_rec
      );
Line: 445

   UPDATE ozf_act_offers SET
      last_update_date = SYSDATE,
      last_updated_by = FND_GLOBAL.user_id,
      object_version_number = l_act_offer_rec.object_version_number + 1,
      last_update_login = FND_GLOBAL.conc_login_id,
      act_offer_used_by_id = l_act_offer_rec.act_offer_used_by_id,
      arc_act_offer_used_by = l_act_offer_rec.arc_act_offer_used_by,
      primary_offer_flag = l_act_offer_rec.primary_offer_flag,
--      offer_type = l_act_offer_rec.offer_type,
--      offer_code = l_act_offer_rec.offer_code,
      active_period_set = l_act_offer_rec.active_period_set,
      active_period = l_act_offer_rec.active_period,
--      start_date = l_act_offer_rec.start_date,
--      end_date = l_act_offer_rec.end_date,
--      order_date_from = l_act_offer_rec.order_date_from,
--      order_date_to = l_act_offer_rec.order_date_to,
--      ship_date_from = l_act_offer_rec.ship_date_from,
--      ship_date_to = l_act_offer_rec.ship_date_to,
--      perf_date_from = l_act_offer_rec.perf_date_from,
--      perf_date_to = l_act_offer_rec.perf_date_to,
--      status_code = l_act_offer_rec.status_code,
--      status_date = l_act_offer_rec.status_date,
--      offer_amount = l_act_offer_rec.offer_amount,
--      lumpsum_payment_type = l_act_offer_rec.lumpsum_payment_type,
      qp_list_header_id = l_act_offer_rec.qp_list_header_id
   WHERE activity_offer_id = l_act_offer_rec.activity_offer_id
   AND object_version_number = l_act_offer_rec.object_version_number;
Line: 495

      ROLLBACK TO update_act_offer;
Line: 505

      ROLLBACK TO update_act_offer;
Line: 515

      ROLLBACK TO update_act_offer;
Line: 526

END Update_Act_Offer;
Line: 535

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

   l_api_version    CONSTANT NUMBER := 1.0;
Line: 551

   l_api_name       CONSTANT VARCHAR2(30) := 'delete_act_offer';
Line: 558

   SELECT act_offer_used_by_id, arc_act_offer_used_by
     FROM ozf_act_offers
    WHERE activity_offer_id = p_act_offer_id;
Line: 563

   SELECT 1
     FROM ozf_act_offers
    WHERE act_offer_used_by_id = l_used_by_id
      AND arc_act_offer_used_by = l_used_by;
Line: 571

   SAVEPOINT delete_act_offer;
Line: 595

   DELETE FROM OZF_ACT_OFFERS
   WHERE activity_offer_id = p_act_offer_id
   AND object_version_number = p_object_version;
Line: 651

      ROLLBACK TO delete_act_offer;
Line: 661

      ROLLBACK TO delete_act_offer;
Line: 671

      ROLLBACK TO delete_act_offer;
Line: 682

END Delete_Act_Offer;
Line: 711

   SELECT activity_offer_id
     FROM OZF_ACT_OFFERS
    WHERE activity_offer_id = p_act_offer_id
      AND object_version_number = p_object_version
   FOR UPDATE OF activity_offer_id NOWAIT;
Line: 943

      AND p_validation_mode = JTF_PLSQL_API.g_update
   THEN
      IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
         FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_NO_ACT_OFFER_ID');
Line: 956

      AND p_validation_mode = JTF_PLSQL_API.g_update
   THEN
      IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
         FND_MESSAGE.set_name('OZF', 'OZF_API_NO_OBJ_VER_NUM');
Line: 1035

  ELSIF p_validation_mode = JTF_PLSQL_API.g_update
      AND p_act_offer_rec.activity_offer_id IS NOT NULL
   THEN
      l_uk_flag := OZF_Utility_PVT.check_uniqueness
                         (
		    'OZF_ACT_OFFERS',
		    ' activity_offer_id <> '|| p_act_offer_rec.activity_offer_id ||
		    ' AND qp_list_header_id = ' || p_act_offer_rec.qp_list_header_id ||
		    ' AND act_offer_used_by_id = ' || p_act_offer_rec.act_offer_used_by_id ||
		    ' AND arc_act_offer_used_by = ' ||p_act_offer_rec.arc_act_offer_used_by
                         );
Line: 1370

      SELECT count(*) FROM AMS_CAMPAIGNS_VL
      WHERE campaign_id = used_by_id
      AND (actual_exec_end_date IS NULL
      -- changed by rssharma for bug fixing on 01/26/2001
      OR actual_exec_end_date >= trunc(SYSDATE) );
Line: 1378

      SELECT COUNT(*) FROM OZF_ACT_OFFERS
      WHERE act_offer_used_by_id = used_by_id
         AND arc_act_offer_used_by = used_by
         AND primary_offer_flag = 'Y';
Line: 1384

      SELECT COUNT(*) FROM OZF_ACT_OFFERS
      WHERE act_offer_used_by_id = used_by_id
         AND arc_act_offer_used_by = used_by
         AND activity_offer_id <> act_offer_id
         AND primary_offer_flag = 'Y';
Line: 1503

   SELECT *
     FROM ozf_act_offers
    WHERE activity_offer_id = p_act_offer_rec.activity_offer_id;
Line: 1639

   x_act_offer_rec.last_update_date := FND_API.g_miss_date;
Line: 1640

   x_act_offer_rec.last_updated_by := FND_API.g_miss_num;
Line: 1643

   x_act_offer_rec.last_update_login := FND_API.g_miss_num;
Line: 1976

PROCEDURE Update_Offer
(
   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_offer_rec       IN  act_offer_rec_type,
   p_list_header_id      IN  NUMBER   := FND_API.g_miss_num,
   p_offer_name          IN  VARCHAR2 := FND_API.g_miss_char,
   p_currency_code       IN  VARCHAR2 := FND_API.g_miss_char,
   p_start_date          IN  DATE     := FND_API.g_miss_date,
   p_end_date            IN  DATE     := FND_API.g_miss_date,
   p_active_flag         IN  VARCHAR2 := FND_API.g_miss_char,
   p_automatic_flag      IN  VARCHAR2 := FND_API.g_miss_char,

   x_message_type        OUT NOCOPY VARCHAR2    -- OE / FND
)
IS

   l_api_name      CONSTANT VARCHAR2(30)  := 'Update_Offer';
Line: 2008

   SAVEPOINT Update_Offer;
Line: 2048

           p_mode              =>  'UPDATE',

           x_list_header_id    =>  l_list_header_id
           );
Line: 2064

   OZF_Utility_Pvt.Debug_Message('Update Activity Offer');
Line: 2065

   Update_Act_Offer
          (
           p_api_version         => p_api_version,
           p_init_msg_list       => p_init_msg_list,
           p_commit              => p_commit,
           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_act_offer_rec       => p_act_offer_rec
           ) ;
Line: 2118

	        ROLLBACK TO Update_Offer;
Line: 2137

	        ROLLBACK TO Update_Offer;
Line: 2157

	        ROLLBACK TO Update_Offer;
Line: 2184

END Update_Offer;
Line: 2208

PROCEDURE Delete_Offer
(
   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_act_offer_id        IN  NUMBER,
   p_object_version      IN  NUMBER,
   p_list_header_id      IN  NUMBER   := FND_API.g_miss_num,
   p_offer_name          IN  VARCHAR2 := FND_API.g_miss_char,
   p_currency_code       IN  VARCHAR2 := FND_API.g_miss_char,
   p_start_date          IN  DATE     := FND_API.g_miss_date,
   p_end_date            IN  DATE     := FND_API.g_miss_date,
   p_active_flag         IN  VARCHAR2 := FND_API.g_miss_char,
   p_automatic_flag      IN  VARCHAR2 := FND_API.g_miss_char,

   x_message_type        OUT NOCOPY VARCHAR2    -- OE / FND
)
IS

   l_api_name      CONSTANT VARCHAR2(30)  := 'Delete_Offer';
Line: 2240

   SAVEPOINT Delete_Offer;
Line: 2279

           p_mode              =>  'DELETE',

           x_list_header_id    =>  l_list_header_id
           );
Line: 2295

   OZF_Utility_Pvt.Debug_Message('Delete Activity Offer');
Line: 2296

   Delete_Act_Offer
          (
           p_api_version         => p_api_version,
           p_init_msg_list       => p_init_msg_list,
           p_commit              => p_commit,

           x_return_status       => l_return_status ,
           x_msg_count           => x_msg_count,
           x_msg_data            => x_msg_data,

           p_act_offer_id        => p_act_offer_id,
           p_object_version      => p_object_version
           ) ;
Line: 2349

	        ROLLBACK TO Delete_Offer;
Line: 2368

	        ROLLBACK TO Delete_Offer;
Line: 2388

	        ROLLBACK TO Delete_Offer;
Line: 2415

END Delete_Offer;