APPS.IRC_COMMUNICATIONS_API SQL Statements

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

Line: 233

procedure UPDATE_COMM_PROPERTIES
(p_validate                      in   boolean  default false
  ,p_effective_date                in   date
  ,p_object_type                   in   varchar2
  ,p_object_id                     in   number
  ,p_default_comm_status           in   varchar2
  ,p_allow_attachment_flag         in   varchar2
  ,p_auto_notification_flag        in   varchar2
  ,p_allow_add_recipients          in   varchar2
  ,p_default_moderator             in   varchar2
  ,p_attribute_category            in   varchar2  default null
  ,p_attribute1                    in   varchar2  default null
  ,p_attribute2                    in   varchar2  default null
  ,p_attribute3                    in   varchar2  default null
  ,p_attribute4                    in   varchar2  default null
  ,p_attribute5                    in   varchar2  default null
  ,p_attribute6                    in   varchar2  default null
  ,p_attribute7                    in   varchar2  default null
  ,p_attribute8                    in   varchar2  default null
  ,p_attribute9                    in   varchar2  default null
  ,p_attribute10                   in   varchar2  default null
  ,p_information_category          in   varchar2  default null
  ,p_information1                  in   varchar2  default null
  ,p_information2                  in   varchar2  default null
  ,p_information3                  in   varchar2  default null
  ,p_information4                  in   varchar2  default null
  ,p_information5                  in   varchar2  default null
  ,p_information6                  in   varchar2  default null
  ,p_information7                  in   varchar2  default null
  ,p_information8                  in   varchar2  default null
  ,p_information9                  in   varchar2  default null
  ,p_information10                 in   varchar2  default null
  ,p_communication_property_id     in   number
  ,p_object_version_number         in out nocopy number
  ) is
  --
  -- Declare cursors and local variables
  --
  l_proc                  varchar2(72) := g_package||'UPDATE_COMM_PROPERTIES';

Line: 282

  savepoint UPDATE_COMM_PROPERTIES;

Line: 289

    irc_communications_bk2.update_comm_properties_b
    (p_effective_date          =>  p_effective_date
    ,p_object_type             =>  p_object_type
    ,p_object_id               =>  p_object_id
    ,p_default_comm_status     =>  p_default_comm_status
    ,p_allow_attachment_flag   =>  p_allow_attachment_flag
    ,p_auto_notification_flag  =>  p_auto_notification_flag
    ,p_allow_add_recipients    =>  p_allow_add_recipients
    ,p_default_moderator       =>  p_default_moderator
    ,p_attribute_category      =>  p_attribute_category
    ,p_attribute1              =>  p_attribute1
    ,p_attribute2              =>  p_attribute2
    ,p_attribute3              =>  p_attribute3
    ,p_attribute4              =>  p_attribute4
    ,p_attribute5              =>  p_attribute5
    ,p_attribute6              =>  p_attribute6
    ,p_attribute7              =>  p_attribute7
    ,p_attribute8              =>  p_attribute8
    ,p_attribute9              =>  p_attribute9
    ,p_attribute10             =>  p_attribute10
    ,p_information_category    =>  p_information_category
    ,p_information1            =>  p_information1
    ,p_information2            =>  p_information2
    ,p_information3            =>  p_information3
    ,p_information4            =>  p_information4
    ,p_information5            =>  p_information5
    ,p_information6            =>  p_information6
    ,p_information7            =>  p_information7
    ,p_information8            =>  p_information8
    ,p_information9            =>  p_information9
    ,p_information10           =>  p_information10
    ,p_communication_property_id => p_communication_property_id
    ,p_object_version_number     => p_object_version_number
    );

Line: 327

        (p_module_name => 'UPDATE_COMM_PROPERTIES'
        ,p_hook_type   => 'BP'
        );

Line: 374

    irc_communications_bk2.update_comm_properties_a
    (p_effective_date          =>  p_effective_date
    ,p_object_type             =>  p_object_type
    ,p_object_id               =>  p_object_id
    ,p_default_comm_status     =>  p_default_comm_status
    ,p_allow_attachment_flag   =>  p_allow_attachment_flag
    ,p_auto_notification_flag  =>  p_auto_notification_flag
    ,p_allow_add_recipients    =>  p_allow_add_recipients
    ,p_default_moderator       =>  p_default_moderator
    ,p_attribute_category      =>  p_attribute_category
    ,p_attribute1              =>  p_attribute1
    ,p_attribute2              =>  p_attribute2
    ,p_attribute3              =>  p_attribute3
    ,p_attribute4              =>  p_attribute4
    ,p_attribute5              =>  p_attribute5
    ,p_attribute6              =>  p_attribute6
    ,p_attribute7              =>  p_attribute7
    ,p_attribute8              =>  p_attribute8
    ,p_attribute9              =>  p_attribute9
    ,p_attribute10             =>  p_attribute10
    ,p_information_category    =>  p_information_category
    ,p_information1            =>  p_information1
    ,p_information2            =>  p_information2
    ,p_information3            =>  p_information3
    ,p_information4            =>  p_information4
    ,p_information5            =>  p_information5
    ,p_information6            =>  p_information6
    ,p_information7            =>  p_information7
    ,p_information8            =>  p_information8
    ,p_information9            =>  p_information9
    ,p_information10           =>  p_information10
    ,p_communication_property_id => p_communication_property_id
    ,p_object_version_number     => l_object_version_number
    );

Line: 412

        (p_module_name => 'UPDATE_COMM_PROPERTIES'
        ,p_hook_type   => 'AP'
        );

Line: 433

    rollback to UPDATE_COMM_PROPERTIES;

Line: 444

    rollback to UPDATE_COMM_PROPERTIES;

Line: 448

end UPDATE_COMM_PROPERTIES;

Line: 669

procedure update_communication
  (p_validate                      in     boolean  default false
  ,p_effective_date                in     date
  ,p_communication_property_id     in     number
  ,p_object_type                   in     varchar2
  ,p_object_id                     in     number
  ,p_status                        in     varchar2
  ,p_start_date                    in     date
  ,p_end_date                      in     date
  ,p_communication_id              in     number
  ,p_object_version_number         in out nocopy number
  ) is
  --
  -- Declare cursors and local variables
  --
  l_proc                  varchar2(72) := g_package||'UPDATE_COMMUNICATION';

Line: 693

  savepoint UPDATE_COMMUNICATION;

Line: 701

    irc_communications_bk4.update_communication_b
    (p_effective_date             => p_effective_date
    ,p_communication_property_id  => p_communication_property_id
    ,p_communication_id           => p_communication_id
    ,p_object_type                => p_object_type
    ,p_object_id                  => p_object_id
    ,p_status                     => p_status
    ,p_start_date                 => p_start_date
    ,p_end_date                   => l_end_date
    ,p_object_version_number      => p_object_version_number
    );

Line: 716

(p_module_name => 'UPDATE_COMMUNICATION'
,p_hook_type   => 'BP'
);

Line: 739

    irc_communications_bk4.update_communication_a
    (p_effective_date             => p_effective_date
    ,p_communication_property_id  => p_communication_property_id
    ,p_object_type                => p_object_type
    ,p_object_id                  => p_object_id
    ,p_status                     => p_status
    ,p_start_date                 => p_start_date
    ,p_end_date                   => l_end_date
    ,p_object_version_number      => l_object_version_number
    ,p_communication_id           => p_communication_id
    );

Line: 754

(p_module_name => 'UPDATE_COMMUNICATION'
,p_hook_type   => 'AP'
);

Line: 777

    rollback to UPDATE_COMMUNICATION;

Line: 788

    rollback to UPDATE_COMMUNICATION;

Line: 793

end UPDATE_COMMUNICATION;

Line: 831

    update_communication
    (p_effective_date             => p_effective_date
    ,p_communication_property_id  => p_communication_property_id
    ,p_object_type                => p_object_type
    ,p_object_id                  => p_object_id
    ,p_status                     => 'CLOSED'
    ,p_start_date                 => p_start_date
    ,p_end_date                   => l_end_date
    ,p_object_version_number      => l_object_version_number
    ,p_communication_id           => p_communication_id
    );

Line: 885

procedure delete_comm_properties
(
  p_validate                    in boolean    default false
, p_object_version_number       in number
, p_communication_property_id   in number
, p_effective_date              in date       default null
)
is
  --
  -- Declare cursors and local variables
  --
  l_proc                 varchar2(72) := g_package||'delete_comm_properties';

Line: 903

  savepoint DELETE_COMM_PROPERTIES;

Line: 934

    rollback to delete_comm_properties;

Line: 945

    rollback to delete_comm_properties;

Line: 948

end delete_comm_properties;

Line: 1086

  ,p_deleted_flag                 in     varchar2
  ,p_communication_message_id     out nocopy number
  ,p_object_version_number        out nocopy number
  ) is
  --
  -- Declare cursors and local variables
  --
  l_proc                  varchar2(72) := g_package||'CREATE_MESSAGE';

Line: 1122

    ,p_deleted_flag              => p_deleted_flag
    );

Line: 1147

    ,p_deleted_flag              => p_deleted_flag
    ,p_communication_message_id  => l_communication_message_id
    ,p_object_version_number     => l_object_version_number
    );

Line: 1166

    ,p_deleted_flag              => p_deleted_flag
    ,p_communication_message_id  => l_communication_message_id
    ,p_object_version_number     => l_object_version_number
    );

Line: 1224

procedure update_message
  (p_validate                     in     boolean  default false
  ,p_effective_date               in     date
  ,p_deleted_flag                 in     varchar2
  ,p_communication_message_id     in     number
  ,p_object_version_number        in out nocopy number
  ) is
  --
  -- Declare cursors and local variables
  --
  l_proc          varchar2(72) := g_package||'UPDATE_MESSAGE';

Line: 1242

  savepoint UPDATE_MESSAGE;

Line: 1247

  irc_communications_bk7.update_message_b
  (p_effective_date               => p_effective_date
  ,p_deleted_flag                 => p_deleted_flag
  ,p_communication_message_id     => p_communication_message_id
  ,p_object_version_number        => l_object_version_number
    );

Line: 1257

(p_module_name => 'UPDATE_MESSAGE'
,p_hook_type   => 'BP'
);

Line: 1267

    ,p_deleted_flag               => p_deleted_flag
    ,p_communication_message_id   => p_communication_message_id
    ,p_object_version_number      => l_object_version_number
    );

Line: 1275

    irc_communications_bk7.update_message_a
    (p_effective_date             => p_effective_date
    ,p_deleted_flag               => p_deleted_flag
    ,p_communication_message_id   => p_communication_message_id
    ,p_object_version_number      => l_object_version_number
    );

Line: 1285

(p_module_name => 'UPDATE_MESSAGE'
,p_hook_type   => 'AP'
);

Line: 1308

    rollback to UPDATE_MESSAGE;

Line: 1320

    rollback to UPDATE_MESSAGE;

Line: 1326

end update_message;

Line: 1475

   select icp.object_id
   from
   irc_comm_properties icp,
   irc_communications ic,
   irc_comm_topics ict
   where
       ict.communication_topic_id = p_topic_id
   and ic.communication_id = ict.communication_id
   and icp.communication_property_id = ic.communication_property_id
   and icp.object_type = 'VACANCY'  ;

Line: 1488

   select pav.recruiter_id
          ,pav.manager_id
   from
   per_all_vacancies pav
   where
   pav.vacancy_id = p_vacancy_id;

Line: 1497

   select team.person_id
   from
   irc_rec_team_members team
   where
   team.vacancy_id = p_vacancy_id;

Line: 1505

   select paf.person_id
   from
   per_all_assignments_f paf,
   irc_communications ic,
   irc_comm_topics ict
   where
       ict.communication_topic_id = p_topic_id
   and ic.communication_id = ict.communication_id
   and paf.assignment_id  = ic.object_id
   and ic.object_type = 'APPL';

Line: 1518

   select msg.communication_topic_id
   from
   irc_comm_messages msg
   where
   msg.communication_message_id = p_message_id;

Line: 1526

   select icr.communication_object_id
   from
   irc_comm_recipients icr
   where
       icr.communication_recipient_id = p_recipient_id
   and icr.communication_object_type = 'TOPIC';

Line: 1535

   select ppf.full_name
   from per_all_people_f ppf
   where
       ppf.person_id = p_person_id
    and trunc(sysdate) between Effective_start_date and Effective_end_date;

Line: 1543

   select pov.vendor_name
   from
   po_vendors pov
   where
     pov.vendor_id = l_recipient_id;

Line: 1585

   query_str := ' select icr.recipient_id, icr.recipient_type' ||
                ' from' ||
                ' irc_comm_recipients icr'||
                ' where icr.communication_object_id = :1' ||
                ' and   icr.communication_object_type= ''TOPIC''';

Line: 1608

     query_str := ' select icm.sender_id, icm.sender_type' ||
                  ' from' ||
                  ' irc_comm_messages icm'||
                  ' where icm.communication_message_id =:1' ;

Line: 1617

       query_str :=' select icr.recipient_id,icr.recipient_type' ||
                   ' from' ||
                   ' irc_comm_messages icm,' ||
                   ' irc_comm_recipients icr' ||
                   ' where' ||
                   '     icm.communication_message_id= :1' ||
                   ' and  icr.communication_object_id   = icm.communication_topic_id' ||
                   ' and icr.communication_object_type = ''TOPIC''' ||
                   ' and icr.recipient_id<>icm.sender_id' ||
                   ' and icm.message_post_date' ||
                           ' between ' ||
                           ' icr.start_date_active'||
                           ' and  nvl(icr.end_date_active,icm.message_post_date)'||
                   ' order by icr.communication_recipient_id';

Line: 1635

       query_str :=' select icr.recipient_id,icr.recipient_type' ||
                   ' from' ||
                   ' irc_comm_recipients icr' ||
                   ' where' ||
                   ' icr.communication_recipient_id =:1';

Line: 1824

    select hr_lookups.meaning
      from hr_lookups
     where lookup_code  = p_lookup_code
       and lookup_type  = p_lookup_type
       and enabled_flag = 'Y'
       and  sysdate between
            nvl(start_date_active, sysdate)
            and nvl(end_date_active, sysdate);

Line: 1947

  select communication_id, status, object_version_number, object_type, start_date from irc_communications
  where object_type ='APPL' and object_id = p_assignmentIdIn;

Line: 1983

  select communication_property_id from irc_comm_properties where
   object_id = (select distinct vacancy_id from per_all_assignments_f where
                   assignment_id =p_assignment_id )
   and object_type ='VACANCY'  ;

Line: 1989

  select default_comm_status from irc_comm_properties where
   object_id = (select distinct vacancy_id from per_all_assignments_f where
                   assignment_id =p_assignment_id )
   and object_type ='VACANCY'  ;

Line: 2031

        update_communication
        (
          p_effective_date             => trunc(sysdate)
         ,p_communication_property_id  => l_comm_property_id
         ,p_object_type                => l_object_type
         ,p_object_id                  => l_assignmentId
         ,p_status                     => 'OPEN'
         ,p_start_date                 => l_start_date
         ,p_end_date                   => null
         ,p_communication_id           => l_communication_id
         ,p_object_version_number      => l_object_version_number
        );

Line: 2101

  select communication_property_id from irc_comm_properties where
   object_id = (select distinct vacancy_id from per_all_assignments_f where
                   assignment_id =p_assignment_id )
   and object_type ='VACANCY'  ;

Line: 2108

  select default_comm_status from irc_comm_properties where
   object_id = (select distinct vacancy_id from per_all_assignments_f where
                   assignment_id =p_assignment_id )
   and object_type ='VACANCY';

Line: 2149

        update_communication
        (
          p_effective_date             => trunc(sysdate)
         ,p_communication_property_id  => l_comm_property_id
         ,p_object_type                => l_object_type
         ,p_object_id                  => l_assignmentId
         ,p_status                     => 'CLOSED'
         ,p_start_date                 => l_start_date
         ,p_end_date                   => null
         ,p_communication_id           => l_communication_id
         ,p_object_version_number      => l_object_version_number
        );

Line: 2231

                                                  X_last_update_login=> fnd_global.login_id
                                                  );

Line: 2235

     fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>'commDummyMsgMap',X_pk1_value=>p_dummy_attachment_id,X_delete_document_flag=>'Y');

Line: 2264

select * from irc_communications
where object_id = src_asgn_id;

Line: 2268

select * from irc_comm_recipients
where communication_object_id = comm_topic_id;

Line: 2272

select * from irc_comm_topics
where communication_id = comm_id;

Line: 2276

select * from irc_comm_messages
where communication_topic_id = topic_id;

Line: 2283

  select person_id into l_src_person_id from per_all_assignments_f paf
  where assignment_id = l_src_asgn_id and l_eff_date between paf.effective_start_date and paf.effective_end_date;

Line: 2286

  select person_id into l_trg_person_id from per_all_assignments_f paf
  where assignment_id = l_target_asgn_id and l_eff_date between paf.effective_start_date and paf.effective_end_date;

Line: 2342

              ,p_deleted_flag                 =>    commMessageRec.deleted_flag
              ,p_communication_message_id     =>    l_comm_msg_id
              ,p_object_version_number        =>    l_obj_version_number
              );