APPS.IRC_OFFER_NOTIFICATIONS_PKG SQL Statements

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

Line: 14

  select
   fnd.employee_id as applicant_id
  from
   fnd_user fnd
  where fnd.person_party_id in
     (select party_id
      from per_all_people_f
      where person_id = p_person_id
      and trunc(sysdate) between effective_start_date and effective_end_date
     );

Line: 32

  select
   usr.employee_id
  from
   fnd_user usr
  where usr.user_id = p_user_id;

Line: 44

  select max(ioh.status_change_date)
  from   irc_offer_status_history ioh
  where  ioh.offer_id = p_offer_id
  and    ioh.offer_status ='EXTENDED';

Line: 55

  select iof.applicant_assignment_id
        ,vac.name as vacancy_name
        ,vac.manager_id as manager_id
        ,job.name as job_title
        ,asg.person_id as applicant_id
        ,ppf.full_name as applicant_name
        ,iof.created_by as creator_id
  from  irc_offers iof
       ,per_all_vacancies vac
       ,per_jobs_vl job
       ,per_all_assignments_f asg
       ,per_all_people_f ppf
  where
      iof.offer_status = 'EXTENDED'
  and iof.vacancy_id = vac.vacancy_id
  and vac.job_id = job.job_id(+)
  and asg.assignment_id = iof.applicant_assignment_id
  and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
  and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
  and ppf.person_id = asg.person_id
  and iof.offer_id = p_offer_id;

Line: 83

  select vac.name as vacancy_name
        ,asg.supervisor_id as manager_id
        ,asg.recruiter_id as recruiter_id
        ,iof.created_by as creator
        ,job.name as job_title
        ,asg.person_id as applicant_id
        ,ppf.full_name as applicant_name
        ,ipc.name as job_posting_title
        ,iof.last_updated_by as last_updated
  from  irc_offers iof
       ,per_all_vacancies vac
       ,per_jobs_vl job
       ,per_all_assignments_f asg
       ,per_all_people_f ppf
       ,irc_posting_contents_vl ipc
  where
      iof.offer_status = 'CLOSED'
  and iof.vacancy_id = vac.vacancy_id
  and vac.job_id = job.job_id(+)
  and asg.assignment_id = iof.offer_assignment_id
  and asg.effective_start_date = (select max(effective_start_date)
                               from per_assignments_f asg2 where
                       asg.assignment_id=asg2.assignment_id
                       and asg2.effective_start_date <= trunc(sysdate))
  and trunc(sysdate) between ppf.effective_start_date
                           and ppf.effective_end_date
  and ppf.person_id = asg.person_id
  and iof.offer_id = p_offer_id
  and ipc.posting_content_id(+) = vac.primary_posting_id;

Line: 119

  select iof.offer_id
        ,nvl((iof.offer_extended_method),
	      (fnd_profile.VALUE('IRC_OFFER_SEND_METHOD'))) extended_method
        ,iof.applicant_assignment_id
        ,vac.name as vacancy_name
        ,vac.manager_id as manager_id
        ,job.name as job_title
        ,asg.person_id as applicant_id
        ,ppf.full_name as applicant_name
	,iof.expiry_date
        ,iof.created_by as creator_id
  from irc_offers iof
       ,per_all_vacancies vac
       ,per_jobs_vl job
       ,per_all_assignments_f asg
       ,per_all_people_f ppf
  where
      iof.offer_status = 'EXTENDED'
  and iof.latest_offer = 'Y'
  and iof.vacancy_id = vac.vacancy_id
  and vac.job_id = job.job_id(+)
  and asg.assignment_id = iof.applicant_assignment_id
  and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
  and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
  and ppf.person_id = asg.person_id
  and (iof.expiry_date between trunc(sysdate) + 0 and
                                      trunc(sysdate) + p_number_of_days);

Line: 153

  select iof.offer_id
        ,vac.name as vacancy_name
        ,vac.manager_id as manager_id
        ,job.name as job_title
        ,asg.person_id as applicant_id
        ,ppf.full_name as applicant_name
        ,iof.created_by as creator_id
  from irc_offers iof
       ,per_all_vacancies vac
       ,per_jobs_vl job
       ,per_all_assignments_f asg
       ,per_all_people_f ppf
  where
     iof.vacancy_id = vac.vacancy_id
  and iof.offer_status = 'EXTENDED'
  and iof.latest_offer = 'Y'
  and vac.job_id = job.job_id(+)
  and asg.assignment_id = iof.applicant_assignment_id
  and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
  and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
  and ppf.person_id = asg.person_id
  and iof.expiry_date < trunc(sysdate) + 0;

Line: 182

  select vac.name as vacancy_name
        ,iof.created_by as creator_id
        ,job.name as job_title
        ,ppf.full_name as applicant_name
  from  irc_offers iof
       ,per_all_vacancies vac
       ,per_jobs_vl job
       ,per_all_assignments_f asg
       ,per_all_people_f ppf
  where
      iof.offer_status = 'HOLD'
  and iof.vacancy_id = vac.vacancy_id
  and vac.job_id = job.job_id(+)
  and asg.assignment_id = iof.applicant_assignment_id
  and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
  and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
  and ppf.person_id = asg.person_id
  and iof.offer_id = p_offer_id;

Line: 204

  select
   usr.user_name
  from
   fnd_user usr
  where usr.user_id = p_user_id;

Line: 311

  select note.note_text
    from irc_notes note
   where note.offer_status_history_id = p_offer_status_history_id
   order by creation_date desc;

Line: 904

l_last_updated_by number;

Line: 913

  select note.note_text
    from irc_notes note
   where note.offer_status_history_id = p_offer_status_history_id
   order by creation_date desc;

Line: 919

  select meaning
    from hr_lookups
   where lookup_type = 'IRC_OFFER_DECLINE_REASON' and
         lookup_code = p_decline_reason;

Line: 925

  select iri.source_person_id
    from irc_referral_info iri,
         irc_offers iof
   where iri.object_id = iof.APPLICANT_ASSIGNMENT_ID
     and iri.object_type = 'APPLICATION'
     and iof.offer_id = p_offer_id;

Line: 968

      fetch csr_send_apl_resp into l_vacancy_name,l_manager_id,l_recruiter_id,l_creator_id,l_job_title,l_applicant_id,l_applicant_name,l_job_posting_title,l_last_updated_by;

Line: 1328

l_last_updated_by number;

Line: 1329

l_last_updated_emp_id number;

Line: 1353

  select note.note_text
    from irc_notes note
   where note.offer_status_history_id = p_offer_status_history_id
   order by creation_date desc;

Line: 1359

  select meaning
    from hr_lookups
   where lookup_type = 'IRC_OFFER_WITHDRAWAL_REASON' and
         lookup_code = p_withdrawal_reason;

Line: 1365

  select offer_status,change_reason
    from irc_offer_status_history
   where offer_id = p_offer_id
     and status_change_date = (select max(status_change_date)
                                 from irc_offer_status_history
				where offer_id = p_offer_id
				  and offer_status_history_id <> p_offer_status_history_id);

Line: 1376

  select ppf.full_name
  from  per_all_people_f ppf
  where ppf.person_id = p_person_id
    and sysdate between effective_start_date and effective_end_date;

Line: 1419

      fetch csr_send_apl_resp into l_vacancy_name,l_manager_id,l_recruiter_id,l_creator_id,l_job_title,l_applicant_id,l_applicant_name,l_job_posting_title,l_last_updated_by;

Line: 1511

        hr_utility.set_location('l_last_updated_by' || l_last_updated_by, 20);

Line: 1513

        open csr_get_user_employee_id(l_last_updated_by);

Line: 1514

        fetch csr_get_user_employee_id into l_last_updated_emp_id;

Line: 1517

        hr_utility.set_location('l_last_updated_emp_id' || l_last_updated_emp_id, 20);

Line: 1519

        open csr_get_name(l_last_updated_emp_id);

Line: 1637

l_last_updated_by number;

Line: 1659

  select note.note_text
    from irc_notes note
   where note.offer_status_history_id = p_offer_status_history_id
   order by creation_date desc;

Line: 1665

  select meaning
    from hr_lookups
   where lookup_type = 'IRC_OFFER_DECLINE_REASON' and
         lookup_code = p_decline_reason;

Line: 1671

  select offer_status,change_reason
    from irc_offer_status_history
   where offer_id = p_offer_id
     and status_change_date = (select max(status_change_date)
                                 from irc_offer_status_history
				where offer_id = p_offer_id
				  and offer_status_history_id <> p_offer_status_history_id);

Line: 1682

  select ppf.full_name
  from  per_all_people_f ppf
  where ppf.person_id = p_person_id
    and sysdate between effective_start_date and effective_end_date;

Line: 1720

      fetch csr_send_apl_resp into l_vacancy_name,l_manager_id,l_recruiter_id,l_creator_id,l_job_title,l_applicant_id,l_applicant_name,l_job_posting_title,l_last_updated_by;