APPS.AMS_CAMPAIGNRULES_PVT SQL Statements

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

Line: 29

PROCEDURE Update_Related_Source_Code(
   p_source_code                   IN  VARCHAR2,
   p_source_code_for_id            IN  NUMBER,
   p_source_code_for               IN  VARCHAR2,
   p_related_source_code           IN  VARCHAR2,
   p_related_source_code_for_id    IN  NUMBER,
   p_related_source_code_for       IN  VARCHAR2,
   x_return_status                 OUT NOCOPY VARCHAR2
) ;

Line: 63

   SELECT system_status_code
     FROM ams_user_statuses_b
    WHERE user_status_id = p_user_status_id
    --   Commented by ptendulk on 07-May-2001 as Program and campaign use the same api.
    --   AND system_status_type = 'AMS_CAMPAIGN_STATUS'
      AND enabled_flag = 'Y';

Line: 106

   SELECT rollup_type
     FROM ams_campaigns_vl
    WHERE campaign_id = p_parent_id;

Line: 164

   SELECT object_association_id,
          object_version_number,
          using_object_id,
          using_object_type
   FROM   ams_object_associations
   WHERE  master_object_type = 'CAMP'
   AND    master_object_id = p_campaign_id
   AND    using_object_type in ('EVEH', 'EVEO');

Line: 189

     SELECT master_object_id
     FROM   ams_object_associations
     WHERE  master_object_type = 'CAMP'
     AND    using_object_type = p_event_type
     AND    using_object_id = p_event_id;

Line: 213

      AMS_Associations_PVT.delete_association(
         p_api_version           =>  1.0,
         p_init_msg_list         =>  FND_API.g_false,
         p_commit                =>  FND_API.g_false,
         p_validation_level      =>  FND_API.g_valid_level_full,

         x_return_status         =>  x_return_status,
         x_msg_count             =>  l_msg_count,
         x_msg_data              =>  l_msg_data,

         p_object_association_id =>  l_obj_id,
         p_object_version        =>  l_obj_ver
      );

Line: 289

PROCEDURE update_camp_source_code(
   p_campaign_id      IN  NUMBER,
   p_source_code      IN  VARCHAR2,
   p_global_flag      IN  VARCHAR2,
   x_source_code      OUT NOCOPY VARCHAR2,
   p_related_source_object  IN    VARCHAR2 := NULL,
   p_related_source_id      IN    NUMBER   := NULL,
   x_return_status    OUT NOCOPY VARCHAR2
)
IS

   l_msg_data  VARCHAR2(2000);

Line: 313

   SELECT global_flag, source_code, custom_setup_id, status_code, rollup_type,
          related_event_id
   FROM   ams_campaigns_all_b
   WHERE  campaign_id = p_campaign_id;

Line: 319

   SELECT 1
     FROM DUAL
    WHERE EXISTS(
          SELECT 1
            FROM ams_campaign_schedules_b
           WHERE campaign_id = p_campaign_id
             AND active_flag = 'Y'
             AND use_parent_code_flag = 'Y' );

Line: 330

   SELECT source_code_id
   FROM   ams_source_codes
   WHERE  source_code = x_source_code
   AND    active_flag = 'Y';

Line: 370

         Update_Related_Source_Code(
            p_source_code                 => p_source_code,
            p_source_code_for_id          => p_campaign_id ,
            p_source_code_for             => 'CAMP',
            p_related_source_code         => l_related_source_code,
            p_related_source_code_for_id  => l_related_source_id,
            p_related_source_code_for     => l_related_source_object,
            x_return_status               => x_return_status
         );

Line: 380

         Update_Related_Source_Code(
            p_source_code                 => p_source_code,
            p_source_code_for_id          => p_campaign_id ,
            p_source_code_for             => 'CAMP',
            p_related_source_code         => l_related_source_code,
            p_related_source_code_for_id  => l_related_source_id,
            p_related_source_code_for     => l_related_source_object,
            x_return_status               => x_return_status
         );

Line: 416

         AMS_Utility_PVT.error_message('AMS_CAMP_UPDATE_SRC_STAT');

Line: 455

         AMS_Utility_PVT.error_message('AMS_CAMP_UPDATE_SOURCE_CODE');

Line: 555

END update_camp_source_code;

Line: 567

PROCEDURE check_camp_update(
   p_camp_rec       IN  AMS_Campaign_PVT.camp_rec_type,
   p_complete_rec   IN  AMS_Campaign_PVT.camp_rec_type,
   x_return_status  OUT NOCOPY VARCHAR2
)
IS

   CURSOR c_resource IS
   SELECT resource_id
   FROM   ams_jtf_rs_emp_v
   WHERE  user_id = FND_GLOBAL.user_id ;

Line: 580

   SELECT 1
   FROM   DUAL
   WHERE  EXISTS(
          SELECT campaign_id
          FROM   ams_campaigns_vl
          WHERE  parent_campaign_id = p_camp_rec.campaign_id);

Line: 588

   SELECT *
     FROM ams_campaigns_vl
    WHERE campaign_id = p_camp_rec.campaign_id;

Line: 622

   l_access := AMS_Access_PVT.Check_Update_Access(p_object_id          => p_camp_rec.campaign_id ,
                                                  p_object_type        => l_rollup_type,
                                                  p_user_or_role_id    => l_resource,
                                                  p_user_or_role_type  => 'USER');

Line: 773

END Check_Camp_Update;

Line: 802

   SELECT template_flag
     FROM ams_campaigns_vl
    WHERE campaign_id = p_parent_id;

Line: 870

   SELECT media_type_code
     FROM ams_media_vl
    WHERE media_id = p_media_id
    AND enabled_flag = 'Y';

Line: 876

   SELECT 1
     FROM ams_media_channels
    WHERE channel_id = p_channel_id
    AND media_id = p_media_id;

Line: 882

   SELECT event_type_code
     FROM ams_event_headers_vl
    WHERE event_header_id = p_channel_id;

Line: 887

   SELECT event_type_code
     FROM ams_event_offers_vl
    WHERE event_offer_id = p_channel_id;

Line: 892

   SELECT 1
   FROM   DUAL
   WHERE  EXISTS(
          SELECT campaign_id
          FROM   ams_campaigns_vl
          WHERE  media_type_code = 'EVENTS'
          AND    arc_channel_from = p_arc_channel_from
          AND    channel_id = p_channel_id
          AND    (campaign_id <> p_campaign_id OR p_campaign_id IS NULL));

Line: 1026

   SELECT 1
     FROM ams_campaigns_vl
    WHERE campaign_id = p_fund_source_id;

Line: 1031

   SELECT 1
     FROM ams_event_headers_vl
    WHERE event_header_id = p_fund_source_id;

Line: 1036

   SELECT 1
     FROM ams_event_offers_vl
    WHERE event_offer_id = p_fund_source_id;

Line: 1115

   SELECT 1
   FROM   DUAL
   WHERE  EXISTS(
             SELECT 1
             FROM   gl_periods_v
             WHERE  period_set_name = p_campaign_calendar
          );

Line: 1124

   SELECT start_date, end_date
   FROM   gl_periods_v
   WHERE  period_set_name = p_campaign_calendar
   AND    period_name = p_start_period_name;

Line: 1130

   SELECT start_date, end_date
   FROM   gl_periods_v
   WHERE  period_set_name = p_campaign_calendar
   AND    period_name = p_end_period_name;

Line: 1243

   SELECT actual_exec_end_date
   FROM   ams_campaigns_vl
   WHERE  campaign_name = p_campaign_name
   -- AND    (city_id = p_city_id OR city_id IS NULL AND p_city_id IS NULL)
   AND    status_code = 'ACTIVE'
   --AND    (campaign_id <> p_campaign_id OR p_campaign_id IS NULL);

Line: 1253

   SELECT 1
   FROM   DUAL
   WHERE  EXISTS(
             SELECT 1
             FROM   ams_campaigns_vl
             WHERE  campaign_name = p_campaign_name
             -- AND    (city_id = p_city_id OR city_id IS NULL AND p_city_id IS NULL)
             AND    show_campaign_flag = 'Y'
             --AND    actual_exec_end_date < SYSDATE
             AND    (p_status_code <> 'CANCELLED' AND p_status_code <> 'ARCHIVED')
             AND    NVL(p_campaign_id,-20) <> campaign_id
             --AND    (campaign_id <> p_campaign_id OR p_campaign_id IS NULL)
          );

Line: 1268

   SELECT 1
   FROM   DUAL
   WHERE  EXISTS(
             SELECT 1
             FROM   ams_campaigns_vl
             WHERE  campaign_name = p_campaign_name
             AND    (city_id = p_city_id OR city_id IS NULL AND p_city_id IS NULL)
             -- 25-Aug-2005 mayjain version is no longer supported from R12
             --AND    version_no = p_version_no
             AND    (campaign_id <> p_campaign_id OR p_campaign_id IS NULL)
          );

Line: 1338

   SELECT user_status_id
   FROM   ams_campaigns_all_b
   WHERE  campaign_id = p_parent_id;

Line: 1381

   SELECT actual_exec_start_date,
          actual_exec_end_date
     FROM ams_campaigns_vl
    WHERE campaign_id = p_parent_id;

Line: 1514

   SELECT campaign_name AS campaign_name,
          actual_exec_start_date AS start_date,
          actual_exec_end_date AS end_date
     FROM ams_campaigns_vl
    WHERE parent_campaign_id = p_camp_id;

Line: 1600

   SELECT campaign_name AS campaign_name,
          business_unit_id
   FROM ams_campaigns_vl
   WHERE parent_campaign_id = p_camp_id;

Line: 1650

   SELECT business_unit_id
   FROM  ams_campaigns_all_b
   WHERE campaign_id = p_program_id;

Line: 1700

   SELECT event_header_name AS event_name,
          active_from_date AS start_date,
          active_to_date AS end_date
     FROM ams_event_headers_vl
    WHERE program_id = p_camp_id;

Line: 1707

   SELECT event_offer_name AS event_name,
          event_start_date AS start_date,
          event_end_date AS end_date
     FROM ams_event_offers_vl
    WHERE parent_type = 'RCAM'
    AND   parent_id = p_camp_id
    AND   event_standalone_flag = 'Y' ;

Line: 1843

   SELECT start_date_time AS start_date,
          end_date_time AS end_date
   FROM ams_campaign_schedules_b
   WHERE campaign_id = p_camp_id
   -- Following line of code is added by ptendulk on 25-May-2001
    AND   active_flag = 'Y' ;

Line: 1919

   SELECT cascade_source_code_flag,
          source_code,
          custom_setup_id,
          global_flag
     FROM ams_campaigns_vl
    WHERE campaign_id = p_camp_id;

Line: 2128

   SELECT rollup_type, media_type_code
     FROM ams_campaigns_vl
    WHERE campaign_id = p_camp_id
      AND active_flag = 'Y';

Line: 2173

   SELECT 1
     FROM ams_object_associations
    WHERE master_object_type = 'CAMP'
      AND master_object_id = p_camp_id
      AND using_object_type = 'DELV'
      AND using_object_id = p_deliv_id;

Line: 2215

   SELECT 1
     FROM ams_act_offers
    WHERE activity_offer_id = p_offer_id
      AND arc_act_offer_used_by = 'CAMP'
      AND act_offer_used_by_id = p_camp_id;

Line: 2258

   SELECT actual_exec_start_date,
          actual_exec_end_date
     FROM ams_campaigns_vl
    WHERE campaign_id = p_camp_id;

Line: 2331

   SELECT A.campaign_id,DECODE(a.rollup_type,'RCAM','RCAM','CAMP')
   FROM   ams_campaigns_vl A, ams_campaigns_vl B
   WHERE  B.campaign_id = p_campaign_id
   AND    A.campaign_name = B.campaign_name
   -- AND    (A.city_id = B.city_id OR A.city_id IS NULL AND B.city_id IS NULL)
   AND    A.show_campaign_flag = 'Y'
   AND    A.campaign_id <> p_campaign_id
   -- Following line is added by ptendulk on 19-Jun-2001
   AND    A.rollup_type = B.rollup_type
   AND    a.parent_campaign_id IS NOT NULL ;

Line: 2372

      UPDATE ams_campaigns_all_b
      SET    show_campaign_flag = 'N'
      WHERE  campaign_id IN(
                SELECT A.campaign_id
                FROM   ams_campaigns_vl A, ams_campaigns_vl B
                WHERE  B.campaign_id = p_campaign_id
                AND    A.campaign_name = B.campaign_name
                AND    (A.city_id = B.city_id OR A.city_id IS NULL AND B.city_id IS NULL)
                AND    A.show_campaign_flag = 'Y'
                AND    A.campaign_id <> p_campaign_id
                -- Following line is added by ptendulk on 19-Jun-2001
                AND    A.rollup_type = B.rollup_type
             );

Line: 2407

PROCEDURE update_camp_status(
   p_campaign_id      IN  NUMBER,
   p_user_status_id   IN  NUMBER,
   p_budget_amount    IN  NUMBER,
   p_parent_id        IN  NUMBER
)
IS

   l_budget_exist      NUMBER;

Line: 2425

   SELECT user_status_id, object_version_number,DECODE(rollup_type,'RCAM','RCAM','CAMP') rollup_type,
          status_code,custom_setup_id
   FROM   ams_campaigns_all_b
   WHERE  campaign_id = p_campaign_id;

Line: 2431

   SELECT 1
   FROM   DUAL
   WHERE  EXISTS(
          SELECT 1
          FROM   ozf_act_budgets
          WHERE  arc_act_budget_used_by = 'CAMP'
          AND    act_budget_used_by_id = p_campaign_id);

Line: 2440

   SELECT   status_code
   FROM     ams_campaigns_all_b
   WHERE    campaign_id = p_parent_id ;

Line: 2446

   SELECT 1
   FROM   ams_campaigns_all_b
   WHERE  parent_campaign_id = p_campaign_id
   AND    status_code = 'ACTIVE' ;

Line: 2770

   update_status(p_campaign_id      =>   p_campaign_id,
                 p_new_status_id    =>   l_new_status_id,
                 p_new_status_code  =>   AMS_Utility_PVT.get_system_status_code(l_new_status_id)
                                 ) ;

Line: 2777

       Use the common update api to update the campaign
   UPDATE ams_campaigns_all_b
   SET    user_status_id = l_new_status_id,
          status_code = AMS_Utility_PVT.get_system_status_code(l_new_status_id),
          status_date = SYSDATE
   WHERE  campaign_id = p_campaign_id;

Line: 2789

      UPDATE ams_campaigns_all_b
      SET    private_flag = 'N'
      WHERE  campaign_id = p_campaign_id ;

Line: 2798

END update_camp_status;

Line: 2868

   SELECT parent_campaign_id,
          media_type_code
     FROM ams_campaigns_vl
    WHERE campaign_id = l_parent_id;

Line: 2908

   SELECT parent_campaign_id
   FROM   ams_campaigns_vl
   WHERE  campaign_id = l_camp_id;

Line: 2953

   SELECT 1
     FROM ams_object_attributes
    WHERE object_type = 'CAMP'
      AND object_id = p_camp_id
      AND object_attribute = p_attribute;

Line: 3030

   SELECT COUNT(campaign_id)
   FROM   ams_campaigns_vl
   WHERE  parent_campaign_id = p_campaign_id
   AND    active_flag = 'Y'
   AND    private_flag = 'N'
   AND    show_campaign_flag = 'Y' ;

Line: 3077

PROCEDURE Update_Owner(
   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_object_type       IN  VARCHAR2 := NULL ,
   p_campaign_id       IN  NUMBER,
   p_owner_id          IN  NUMBER   )
IS
   CURSOR c_owner IS
   SELECT owner_user_id
   FROM   ams_campaigns_all_b
   WHERE  campaign_id = p_campaign_id ;

Line: 3095

   SELECT *
   FROM   ams_campaign_schedules_vl
   WHERE  campaign_id = p_campaign_id;

Line: 3100

   SELECT *
   FROM ams_act_access
   WHERE arc_act_access_to_object = 'CSCH'
   AND   user_or_role_id = p_owner
   AND   arc_user_or_role_type = 'USER'
   AND   act_access_to_object_id = p_schedule_id;

Line: 3126

        AMS_Access_PVT.update_object_owner(
           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     => x_return_status,
           x_msg_count         => x_msg_count,
           x_msg_data          => x_msg_data,
           p_object_type       => nvl(p_object_type,'CAMP'),
           p_object_id         => p_campaign_id,
           p_resource_id       => p_owner_id,
           p_old_resource_id   => l_old_owner
        );

Line: 3170

                        l_access_rec.delete_flag := 'N';

Line: 3212

                        Ams_Access_pvt.delete_access(
                              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     => x_return_status,
                              x_msg_count         => x_msg_count,
                              x_msg_data          => x_msg_data,

                              p_access_id         => c_schedule_access_rec.activity_access_id,
                              p_object_version    => c_schedule_access_rec.object_version_number
                           );

Line: 3253

                        l_access_rec.delete_flag := 'N';

Line: 3289

END Update_Owner ;

Line: 3310

   SELECT event_header_id
   FROM   ams_event_headers_all_b
   WHERE  event_header_id = p_related_event_id ;

Line: 3315

   SELECT event_offer_id
   FROM   ams_event_offers_all_b
   WHERE  event_offer_id = p_related_event_id
   --AND    event_standalone_flag = 'N';

Line: 3322

   SELECT event_offer_id
   FROM   ams_event_offers_all_b
   WHERE  event_offer_id = p_related_event_id
   --AND    event_standalone_flag = 'Y';

Line: 3374

PROCEDURE Update_Related_Source_Code(
   p_source_code                   IN  VARCHAR2,
   p_source_code_for_id            IN  NUMBER,
   p_source_code_for               IN  VARCHAR2,
   p_related_source_code           IN  VARCHAR2,
   p_related_source_code_for_id    IN  NUMBER,
   p_related_source_code_for       IN  VARCHAR2,
   x_return_status                 OUT NOCOPY VARCHAR2
) IS

   CURSOR c_sc_from_source_codes IS
      SELECT source_code_id
      FROM ams_source_codes
      WHERE  source_code = p_source_code
      AND source_code_for_id = p_source_code_for_id
      AND arc_source_code_for = p_source_code_for;

Line: 3413

END Update_Related_Source_Code;

Line: 3431

      SELECT schedule_id , object_version_number
      FROM ams_campaign_schedules_b
      WHERE  campaign_id = p_campaign_id
      AND (status_code = 'COMPLETED' OR status_code = 'CANCELLED') ;

Line: 3447

      UPDATE   ams_campaign_schedules_b
      SET      status_code = 'ARCHIVED',
               user_status_id = l_user_status_id ,
               status_date = SYSDATE ,
               object_version_number = l_obj_version + 1
      WHERE    schedule_id = l_schedule_id
      AND      object_version_number = l_obj_version ;

Line: 3483

      SELECT campaign_id , object_version_number,rollup_type
      FROM ams_campaigns_all_b
      WHERE  parent_campaign_id = p_program_id
      AND (status_code = 'COMPLETED' OR status_code = 'CANCELLED') ;

Line: 3503

         UPDATE ams_campaigns_all_b
         SET      status_code = 'ARCHIVED',
                  user_status_id = l_program_status_id ,
                  status_date = SYSDATE ,
                  object_version_number = l_obj_version + 1
         WHERE    campaign_id = l_campaign_id
         AND      object_version_number = l_obj_version ;

Line: 3513

         UPDATE   ams_campaigns_all_b
         SET      status_code = 'ARCHIVED',
                  user_status_id = l_campaign_status_id ,
                  status_date = SYSDATE ,
                  object_version_number = l_obj_version + 1
         WHERE    campaign_id = l_campaign_id
         AND      object_version_number = l_obj_version ;

Line: 3549

      SELECT campaign_id , object_version_number,rollup_type
      FROM ams_campaigns_all_b
      WHERE  parent_campaign_id = p_program_id
      AND status_code = DECODE(rollup_type,'RCAM','NEW','AVAILABLE') ;

Line: 3569

         UPDATE ams_campaigns_all_b
         SET      status_code = 'ACTIVE',
                  user_status_id = l_program_status_id ,
                  status_date = SYSDATE ,
                  object_version_number = l_obj_version + 1
         WHERE    campaign_id = l_campaign_id
         AND      object_version_number = l_obj_version ;

Line: 3577

         UPDATE   ams_campaigns_all_b
         SET      status_code = 'ACTIVE',
                  user_status_id = l_campaign_status_id ,
                  status_date = SYSDATE ,
                  object_version_number = l_obj_version + 1
         WHERE    campaign_id = l_campaign_id
         AND      object_version_number = l_obj_version ;

Line: 3616

      SELECT campaign_id, object_version_number, rollup_type
      FROM ams_campaigns_all_b
      WHERE  parent_campaign_id = p_program_id
      AND status_code = DECODE(p_system_status_code,'ACTIVE','ON_HOLD','ACTIVE') ;

Line: 3636

         UPDATE ams_campaigns_all_b
         SET      status_code = p_system_status_code,
                  user_status_id = l_program_status_id ,
                  status_date = SYSDATE ,
                  object_version_number = l_obj_version + 1
         WHERE    campaign_id = l_campaign_id
         AND      object_version_number = l_obj_version ;

Line: 3644

         UPDATE   ams_campaigns_all_b
         SET      status_code = p_system_status_code,
                  user_status_id = l_campaign_status_id ,
                  status_date = SYSDATE ,
                  object_version_number = l_obj_version + 1
         WHERE    campaign_id = l_campaign_id
         AND      object_version_number = l_obj_version ;

Line: 3685

      SELECT schedule_id,object_version_number,status_code
      FROM ams_campaign_schedules_b
      WHERE  campaign_id = p_campaign_id
      AND status_code <> 'CANCELLED' ;

Line: 3703

         UPDATE ams_campaign_schedules_b
         SET    status_code = 'CANCELLED',
                status_date = SYSDATE,
                user_status_id = l_status_id,
                object_version_number = object_version_number + 1
         WHERE  schedule_id = l_schedule_id
         AND    object_version_number = l_obj_version ;

Line: 3733

      SELECT count(*)
      FROM ams_campaign_schedules_b
      WHERE  campaign_id = p_campaign_id
      AND status_code <> 'CANCELLED'
      AND status_code <> 'ARCHIVED';

Line: 3766

         UPDATE ams_campaigns_all_b
         SET      status_code = 'CANCELLED',
                  user_status_id = l_program_status_id ,
                  status_date = SYSDATE ,
                  -- object_version_number = l_obj_version + 1
         WHERE    campaign_id = l_campaign_id ;

Line: 3808

      SELECT campaign_id, object_version_number, rollup_type, status_code
      FROM ams_campaigns_all_b
      WHERE  parent_campaign_id = p_program_id
      AND    status_code <> 'CANCELLED' ;

Line: 3830

            UPDATE ams_campaigns_all_b
            SET      status_code = 'CANCELLED',
                     user_status_id = l_program_status_id ,
                     status_date = SYSDATE ,
                     object_version_number = l_obj_version + 1
            WHERE    campaign_id = l_campaign_id
            AND      object_version_number = l_obj_version ;

Line: 3850

            UPDATE   ams_campaigns_all_b
            SET      status_code = 'CANCELLED',
                     user_status_id = l_campaign_status_id ,
                     status_date = SYSDATE ,
                     object_version_number = l_obj_version + 1
            WHERE    campaign_id = l_campaign_id
            AND      object_version_number = l_obj_version ;

Line: 3883

      SELECT count(*)
      FROM ams_campaigns_all_b
      WHERE  parent_campaign_id = p_program_id
      AND    status_code <> 'CANCELLED'
      AND    status_code <> 'ARCHIVED';

Line: 3920

         UPDATE ams_campaigns_all_b
         SET      status_code = 'CANCELLED',
                  user_status_id = l_program_status_id ,
                  status_date = SYSDATE ,
                  -- object_version_number = l_obj_version + 1
         WHERE    campaign_id = l_campaign_id ;

Line: 3961

      SELECT schedule_id,object_version_number,status_code, activity_type_code, related_event_id, source_code
      FROM ams_campaign_schedules_b
      WHERE  campaign_id = p_campaign_id
      -- asaha added more status check for bug 3142886
      AND status_code NOT IN ('COMPLETED','CANCELLED','CLOSED','ARCHIVED') ;

Line: 3969

      SELECT count(*)
      FROM ams_campaign_schedules_b
      WHERE  campaign_id = p_campaign_id
      AND status_code IN ('COMPLETED','CLOSED') ;

Line: 3976

      SELECT count(1)
      FROM ams_campaign_schedules_b
      WHERE  campaign_id = p_campaign_id ;

Line: 4006

         UPDATE ams_campaign_schedules_b
         SET    status_code = 'COMPLETED',
                status_date = SYSDATE,
                user_status_id = l_status_id,
                object_version_number = object_version_number + 1
         WHERE  schedule_id = l_schedule_id
         AND    object_version_number = l_obj_version ;

Line: 4084

      SELECT campaign_id, object_version_number, rollup_type, status_code
      FROM ams_campaigns_all_b
      WHERE  parent_campaign_id = p_program_id
      AND    status_code <> 'COMPLETED' ;

Line: 4108

            UPDATE ams_campaigns_all_b
            SET      status_code = 'COMPLETED',
                     user_status_id = l_program_status_id ,
                     status_date = SYSDATE ,
                     object_version_number = l_obj_version + 1
            WHERE    campaign_id = l_campaign_id
            AND      object_version_number = l_obj_version ;

Line: 4131

            UPDATE   ams_campaigns_all_b
            SET      status_code = 'COMPLETED',
                     user_status_id = l_campaign_status_id ,
                     status_date = SYSDATE ,
                     object_version_number = l_obj_version + 1
            WHERE    campaign_id = l_campaign_id
            AND      object_version_number = l_obj_version ;

Line: 4175

   SELECT source_code
   FROM ams_event_headers_all_b
   WHERE  event_header_id = p_event_id ;

Line: 4180

   SELECT source_code
   FROM ams_event_offers_all_b
   WHERE event_offer_id = p_event_id
   --AND  event_standalone_flag = 'N';

Line: 4187

   SELECT source_code
   FROM ams_event_offers_all_b
   WHERE  event_offer_id = p_event_id
   --AND    event_standalone_flag = 'Y';

Line: 4226

PROCEDURE Update_Rollup(
   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_campaign_id       IN  NUMBER,
   p_parent_id         IN  NUMBER   )
IS
   CURSOR c_parent IS
   SELECT parent_campaign_id, DECODE(rollup_type,'RCAM','RCAM','CAMP')
   FROM   ams_campaigns_all_b
   WHERE  campaign_id = p_campaign_id ;

Line: 4247

   AMS_Utility_PVT.Debug_message('Start Update rollup ');

Line: 4286

END Update_Rollup ;

Line: 4302

PROCEDURE update_status(         p_campaign_id             IN NUMBER,
                                 p_new_status_id           IN NUMBER,
                                 p_new_status_code         IN VARCHAR2
                                 )
IS

BEGIN
   UPDATE ams_campaigns_all_b
   SET    user_status_id = p_new_status_id,
          status_code = p_new_status_code, -- AMS_Utility_PVT.get_system_status_code(p_new_status_id),
          status_date = SYSDATE
          -- private_flag = DECODE(p_new_status_code,'ACTIVE','N',private_flag)
   WHERE  campaign_id = p_campaign_id;

Line: 4320

END update_status;

Line: 4342

   SELECT campaign_id
   FROM ams_Campaigns_all_B
   WHERE active_flag = 'Y'
   START WITH campaign_id = p_campaign_id
   CONNECT BY PRIOR campaign_id = parent_campaign_id ;

Line: 4383

      SELECT count(1)
      FROM ams_campaign_schedules_b
      WHERE  campaign_id = p_campaign_id
      AND status_code IN ('ACTIVE','AVAILABLE');