APPS.IRC_ASG_STATUS_API SQL Statements

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

Line: 14

procedure dt_update_irc_asg_status
  (
    p_validate                  in  boolean  default false
  , p_datetrack_mode            in  varchar2
  , p_assignment_id             in  number
  , p_assignment_status_type_id in  number
  , p_status_change_date        in  date
  , p_status_change_reason      in  varchar2 default hr_api.g_varchar2
  , p_assignment_status_id      out nocopy number
  , p_object_version_number     out nocopy number
  , p_status_change_comments    in  varchar2 default hr_api.g_varchar2
  ) IS
--
cursor csr_after_date is
    select assignment_status_id, object_Version_number
      from irc_assignment_statuses
      where assignment_id = p_assignment_id
      and trunc(status_change_date) > trunc(p_status_change_date);

Line: 34

    select assignment_status_type_id
     from irc_assignment_statuses
     where assignment_id = p_assignment_id
       and status_change_date = (select max(status_change_date)
                                     from irc_assignment_statuses
                                     where assignment_id = p_assignment_id);

Line: 45

l_proc    varchar2(72) := g_package||'dt_update_irc_asg_status';

Line: 48

  if p_datetrack_mode = 'UPDATE_OVERRIDE' then
    for c_rec in csr_after_date loop
      delete_irc_asg_status
        (p_assignment_status_id   => c_rec.assignment_status_id,
         p_object_version_number  => c_rec.object_version_number);

Line: 69

          update_irc_asg_status
               (p_validate                   => p_validate
               ,p_status_change_date         => p_status_change_date
               ,p_status_change_reason       => l_status_change_reason
               ,p_assignment_status_id       => p_assignment_status_id
               ,p_object_version_number      => p_object_version_number
               ,p_status_change_comments     => p_status_change_comments);

Line: 107

procedure dt_delete_irc_asg_status
  ( p_validate                  in  boolean  default false
  , p_assignment_status_id      in  number
  , p_object_version_number     in  number
  , p_effective_date            in  date
  , p_datetrack_mode            in  varchar2) IS
--
l_assignment_id      irc_assignment_statuses.assignment_id%type;

Line: 116

    select assignment_id from irc_assignment_statuses
     where assignment_status_id = p_assignment_status_id;

Line: 120

    select assignment_status_id, object_Version_number
      from irc_assignment_statuses
      where assignment_id = l_assignment_id
      and (trunc(status_change_date) > trunc(p_effective_date)
       or p_datetrack_mode <> 'FUTURE_CHANGE')
      and (trunc(status_change_date) >= trunc(p_effective_date)
       or p_datetrack_mode <> 'DELETE')
      and (p_datetrack_mode  <> 'DELETE_NEXT_CHANGE'
       or trunc(status_change_date) = (select trunc(min(status_change_date))
                                         from irc_assignment_statuses
                                        where assignment_id = l_assignment_id
                                          and status_change_date >
                                             p_effective_date));

Line: 140

delete_irc_asg_status
  (p_assignment_status_id   => c_rec.assignment_status_id,
   p_object_version_number  => c_rec.object_version_number);

Line: 174

    select per_system_status
    from per_assignment_status_types
    where assignment_status_type_id = p_assignment_status_type_id;

Line: 178

    select max(status_change_date)
    from irc_assignment_statuses
    where assignment_id = p_assignment_id;

Line: 181

  PROCEDURE UPDATE_INTERVIEW(
       p_assignment_id in NUMBER
      ,p_assignment_status_type_id in NUMBER
  ) is
    iid_rec irc_interview_details%rowtype;

Line: 189

    select iid.*
      from irc_interview_details iid
           ,per_events pe
     where iid.event_id = pe.event_id
       and iid.status not in ('COMPLETED','CANCELLED')
       and sysdate between iid.start_date and iid.end_date
       and pe.assignment_id = p_assignment_id;

Line: 199

        IRC_INTERVIEW_DETAILS_SWI.UPDATE_IRC_INTERVIEW_DETAILS(
           P_STATUS                   => 'CANCELLED'
          ,P_FEEDBACK                 => IID_REC.FEEDBACK
          ,P_NOTES                    => IID_REC.NOTES
          ,P_NOTES_TO_CANDIDATE       => IID_REC.NOTES_TO_CANDIDATE
          ,P_CATEGORY                 => IID_REC.CATEGORY
          ,P_RESULT                   => IID_REC.RESULT
          ,P_IID_INFORMATION_CATEGORY => IID_REC.IID_INFORMATION_CATEGORY
          ,P_IID_INFORMATION1         => IID_REC.IID_INFORMATION1
          ,P_IID_INFORMATION2         => IID_REC.IID_INFORMATION2
          ,P_IID_INFORMATION3         => IID_REC.IID_INFORMATION3
          ,P_IID_INFORMATION4         => IID_REC.IID_INFORMATION4
          ,P_IID_INFORMATION5         => IID_REC.IID_INFORMATION5
          ,P_IID_INFORMATION6         => IID_REC.IID_INFORMATION6
          ,P_IID_INFORMATION7         => IID_REC.IID_INFORMATION7
          ,P_IID_INFORMATION8         => IID_REC.IID_INFORMATION8
          ,P_IID_INFORMATION9         => IID_REC.IID_INFORMATION9
          ,P_IID_INFORMATION10        => IID_REC.IID_INFORMATION10
          ,P_IID_INFORMATION11        => IID_REC.IID_INFORMATION11
          ,P_IID_INFORMATION12        => IID_REC.IID_INFORMATION12
          ,P_IID_INFORMATION13        => IID_REC.IID_INFORMATION13
          ,P_IID_INFORMATION14        => IID_REC.IID_INFORMATION14
          ,P_IID_INFORMATION15        => IID_REC.IID_INFORMATION15
          ,P_IID_INFORMATION16        => IID_REC.IID_INFORMATION16
          ,P_IID_INFORMATION17        => IID_REC.IID_INFORMATION17
          ,P_IID_INFORMATION18        => IID_REC.IID_INFORMATION18
          ,P_IID_INFORMATION19        => IID_REC.IID_INFORMATION19
          ,P_IID_INFORMATION20        => IID_REC.IID_INFORMATION20
          ,P_EVENT_ID                 => IID_REC.EVENT_ID
          ,P_INTERVIEW_DETAILS_ID     => IID_REC.INTERVIEW_DETAILS_ID
          ,P_START_DATE               => IID_REC.START_DATE
          ,P_END_DATE                 => IID_REC.END_DATE
          ,P_OBJECT_VERSION_NUMBER    => IID_REC.OBJECT_VERSION_NUMBER
          ,P_RETURN_STATUS            => l_return_status
	  );

Line: 245

  end UPDATE_INTERVIEW;

Line: 344

    UPDATE_INTERVIEW(p_assignment_id              => p_assignment_id
                  ,p_assignment_status_type_id    => p_assignment_status_type_id
                  );

Line: 420

procedure update_irc_asg_status
  (
    p_validate                  in  boolean  default false
  , p_status_change_reason      in  varchar2 default hr_api.g_varchar2
  , p_status_change_date        in  date
  , p_assignment_status_id      in  number
  , p_object_version_number  in out nocopy number
  , p_status_change_comments    in  varchar2 default hr_api.g_varchar2
  ) is
  --
  -- Declare cursors and local variables
  --
  l_proc                varchar2(72) := g_package||'update_irc_asg_status';

Line: 442

  savepoint update_irc_asg_status;

Line: 463

    irc_asg_status_bk2.update_irc_asg_status_b
    (
      p_status_change_reason      => l_status_change_reason
    , p_status_change_date        => l_status_change_date
    , p_assignment_status_id      => p_assignment_status_id
    , p_object_version_number     => l_object_version_number
    , p_status_change_comments    => p_status_change_comments
    );

Line: 474

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

Line: 493

   irc_asg_status_bk2.update_irc_asg_status_a
     (
       p_status_change_reason   => l_status_change_reason
     , p_status_change_date     => l_status_change_date
     , p_assignment_status_id   => p_assignment_status_id
     , p_object_version_number  => l_object_version_number
     , p_status_change_comments => p_status_change_comments
     );

Line: 504

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

Line: 527

    rollback to update_irc_asg_status;

Line: 540

    rollback to update_irc_asg_status;

Line: 546

end update_irc_asg_status;

Line: 551

procedure delete_irc_asg_status
  (
    p_validate                  in  boolean  default false
  , p_assignment_status_id      in  number
  , p_object_version_number     in  number
  ) is
  --
  -- Declare cursors and local variables
  --
  l_proc           varchar2(72) := g_package||'delete_irc_asg_status';

Line: 567

  savepoint delete_irc_asg_status;

Line: 572

    irc_asg_status_bk3.delete_irc_asg_status_b
      (
        p_assignment_status_id      => p_assignment_status_id
      , p_object_version_number     => p_object_version_number
      );

Line: 580

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

Line: 595

    irc_asg_status_bk3.delete_irc_asg_status_a
      (
        p_assignment_status_id      => p_assignment_status_id
      , p_object_version_number     => p_object_version_number
      );

Line: 603

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

Line: 623

    rollback to delete_irc_asg_status;

Line: 634

    rollback to delete_irc_asg_status;

Line: 637

end delete_irc_asg_status;

Line: 660

  select user_id
  from per_all_assignments_f paf, fnd_user usr, per_all_people_f ppf,
  per_all_people_f linkppf
  where p_effective_date between paf.effective_start_date and
  paf.effective_end_date
  and p_effective_date between usr.start_date and
  nvl(usr.end_date,p_effective_date)
  and p_effective_date between ppf.effective_start_date and
  ppf.effective_end_date
  and p_effective_date between linkppf.effective_start_date and
  linkppf.effective_end_date
  and usr.employee_id=linkppf.person_id
  and ppf.party_id = linkppf.party_id
  and ppf.person_id = paf.person_id
  and paf.assignment_id= p_assignment_id
  and usr.user_id = fnd_global.user_id;