APPS.IRC_IOF_BUS SQL Statements

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

Line: 29

    select pbg.security_group_id
      from per_business_groups pbg
         , irc_offers iof
         , per_all_vacancies vac
     where iof.offer_id = p_offer_id
       and vac.vacancy_id = iof.vacancy_id
       and pbg.business_group_id = vac.business_group_id;

Line: 93

    select pbg.legislation_code
      from per_business_groups_perf     pbg
         , irc_offers iof
         , per_all_vacancies vac
     where iof.offer_id = p_offer_id
       and iof.vacancy_id = vac.vacancy_id
       and pbg.business_group_id = vac.business_group_id;

Line: 361

Procedure chk_non_updateable_args
  (p_effective_date               in date
  ,p_rec in irc_iof_shd.g_rec_type
  ) IS
--
  l_proc     varchar2(72) := g_package || 'chk_non_updateable_args';

Line: 428

End chk_non_updateable_args;

Line: 464

         select 1
           from per_all_assignments_f
          where assignment_id = p_applicant_assignment_id
            and assignment_type = 'A'
            and p_effective_date
        between effective_start_date
            and effective_end_date;

Line: 543

         select 1
           from per_all_assignments_f
          where assignment_id = p_offer_assignment_id
            and assignment_type = 'O';

Line: 549

         select 1
           from irc_offers
          where offer_assignment_id = p_offer_assignment_id;

Line: 643

         select paaf.vacancy_id
           from per_all_assignments_f paaf
          where paaf.assignment_id = p_applicant_assignment_id
            and p_effective_date
        between effective_start_date
            and effective_end_date;

Line: 733

         select budget_measurement_value
               ,budget_measurement_type
           from per_all_vacancies
          where vacancy_id = p_vacancy_id;

Line: 739

  select ios1.offer_status
        ,ios1.change_reason
    from irc_offer_status_history ios1
   where ios1.offer_id = p_offer_id
     and  EXISTS
       (SELECT 1
       FROM irc_offer_status_history iosh1
       WHERE iosh1.offer_id = ios1.offer_id
           AND iosh1.status_change_date > ios1.status_change_date
       )
     AND ios1.offer_status_history_id =
       (SELECT MAX(iosh2.offer_status_history_id)
       FROM irc_offer_status_history iosh2
       WHERE iosh2.offer_id = ios1.offer_id
           AND iosh2.status_change_date = ios1.status_change_date
       )
   AND 1 =
    (SELECT COUNT(*)
     FROM irc_offer_status_history ios3
     WHERE ios3.offer_id = ios1.offer_id
     AND ios3.status_change_date > ios1.status_change_date
    );

Line: 763

         select offer_status
           from irc_offers
          where offer_id = p_offer_id;

Line: 768

         select count(*)
           from irc_offers iof
               ,per_all_vacancies pav
               ,irc_offer_status_history iosh
          where pav.vacancy_id = p_vacancy_id
            and iof.vacancy_id = pav.vacancy_id
            and iosh.offer_id = iof.offer_id
            and iof.offer_id <> p_offer_id
            AND NOT EXISTS
                     (SELECT 1
                        FROM irc_offer_status_history iosh1
                       WHERE iosh1.offer_id = iosh.offer_id
                         AND iosh1.status_change_date > iosh.status_change_date
                     )
            AND iosh.offer_status_history_id =
                    (SELECT MAX(iosh2.offer_status_history_id)
                       FROM irc_offer_status_history iosh2
                      WHERE iosh2.offer_id = iosh.offer_id
                        AND iosh2.status_change_date = iosh.status_change_date
                    )
            and iof.latest_offer = 'Y'
            and ( iof.offer_status = 'EXTENDED'  or ( iof.offer_status = 'CLOSED' and   iosh.change_reason = 'APL_ACCEPTED'));

Line: 938

         select 1
           from fnd_user
          where user_id = p_respondent_id;

Line: 1205

         select adr.address_id
           from per_addresses adr
               ,per_all_assignments_f asg
          where asg.assignment_id = p_applicant_assignment_id
            and adr.person_id = irc_utilities_pkg.get_recruitment_person_id(asg.person_id,trunc(sysdate))
            and adr.address_type = 'REC'
            and p_effective_date
        between adr.date_from
            and nvl(adr.date_to, trunc(sysdate));

Line: 1295

         select 1
           from per_addresses
          where address_id = p_address_id;

Line: 1396

         select 1
           from xdo_templates_b
          where template_id = p_template_id
            and p_effective_date
        between start_date
            and nvl(end_date,p_effective_date);

Line: 1404

         select ita.template_id
           from irc_template_associations ita
               ,per_all_assignments_f ppaf
               ,irc_offers iof
          where ita.default_association = 'Y'
            and iof.offer_id = p_offer_id
            and iof.offer_assignment_id = ppaf.assignment_id
            and ita.job_id = ppaf.job_id;

Line: 1414

         select ita.template_id
           from irc_template_associations ita
               ,per_all_assignments_f ppaf
               ,irc_offers iof
          where ita.default_association = 'Y'
            and iof.offer_id = p_offer_id
            and iof.offer_assignment_id = ppaf.assignment_id
            and ita.position_id = ppaf.position_id;

Line: 1424

         select ita.template_id
           from irc_template_associations ita
               ,per_all_assignments_f ppaf
               ,irc_offers iof
          where ita.default_association = 'Y'
            and iof.offer_id = p_offer_id
            and iof.offer_assignment_id = ppaf.assignment_id
            and ita.organization_id = ppaf.organization_id;

Line: 1561

     select nvl(max(offer_version),0) + 1
     from   irc_offers
     where  (  applicant_assignment_id = nvl(p_applicant_assignment_id,-1)
                   OR
               applicant_assignment_id = nvl(irc_offers_api.g_src_apl_asg_id,-1)
                   OR
              applicant_assignment_id in
               (
                      select tgt_apl_asg_id from per_vac_linked_assignments
                      where src_apl_asg_id = irc_offers_api.g_src_apl_asg_id
                )
             );

Line: 1668

         select 1
           from irc_offers
          where latest_offer = 'Y'
            and applicant_assignment_id = p_applicant_assignment_id
            and offer_id <> p_offer_id;

Line: 1675

         select 1
           from irc_offers
          where latest_offer = 'Y'
            and applicant_assignment_id = p_applicant_assignment_id;

Line: 1823

    select 1
      from irc_offers
     where offer_version = p_offer_version
       and applicant_assignment_id = p_applicant_assignment_id;

Line: 2031

procedure chk_offer_status_update
  ( p_current_offer_record    in irc_iof_shd.g_rec_type
  )IS
  --
  l_proc           varchar2(72)  :=  g_package||'chk_offer_status_update';

Line: 2038

  l_mutiple_fields_updated      boolean;

Line: 2041

  select ios1.offer_status
    from irc_offer_status_history ios1
   where ios1.offer_id = p_current_offer_record.offer_id
     and  EXISTS
       (SELECT 1
       FROM irc_offer_status_history iosh1
       WHERE iosh1.offer_id = ios1.offer_id
           AND iosh1.status_change_date > ios1.status_change_date
       )
     AND ios1.offer_status_history_id =
       (SELECT MAX(iosh2.offer_status_history_id)
       FROM irc_offer_status_history iosh2
       WHERE iosh2.offer_id = ios1.offer_id
           AND iosh2.status_change_date = ios1.status_change_date
       )
   AND 1 =
    (SELECT COUNT(*)
     FROM irc_offer_status_history ios3
     WHERE ios3.offer_id = ios1.offer_id
     AND ios3.status_change_date > ios1.status_change_date
    );

Line: 2097

        IRC_IOF_BUS.chk_multiple_fields_updated
        (     p_offer_id                     => p_current_offer_record.offer_id
             ,p_offer_status                 => p_current_offer_record.offer_status
             ,p_discretionary_job_title      => p_current_offer_record.discretionary_job_title
             ,p_offer_extended_method        => p_current_offer_record.offer_extended_method
             ,p_expiry_date                  => p_current_offer_record.expiry_date
             ,p_proposed_start_date          => p_current_offer_record.proposed_start_date
             ,p_offer_letter_tracking_code   => p_current_offer_record.offer_letter_tracking_code
             ,p_offer_postal_service         => p_current_offer_record.offer_postal_service
             ,p_offer_shipping_date          => p_current_offer_record.offer_shipping_date
             ,p_applicant_assignment_id      => p_current_offer_record.applicant_assignment_id
             ,p_offer_assignment_id          => p_current_offer_record.offer_assignment_id
             ,p_address_id                   => p_current_offer_record.address_id
             ,p_template_id                  => p_current_offer_record.template_id
             ,p_offer_letter_file_type       => p_current_offer_record.offer_letter_file_type
             ,p_offer_letter_file_name       => p_current_offer_record.offer_letter_file_name
             ,p_attribute_category           => p_current_offer_record.attribute_category
             ,p_attribute1                   => p_current_offer_record.attribute1
             ,p_attribute2                   => p_current_offer_record.attribute2
             ,p_attribute3                   => p_current_offer_record.attribute3
             ,p_attribute4                   => p_current_offer_record.attribute4
             ,p_attribute5                   => p_current_offer_record.attribute5
             ,p_attribute6                   => p_current_offer_record.attribute6
             ,p_attribute7                   => p_current_offer_record.attribute7
             ,p_attribute8                   => p_current_offer_record.attribute8
             ,p_attribute9                   => p_current_offer_record.attribute9
             ,p_attribute10                  => p_current_offer_record.attribute10
             ,p_attribute11                  => p_current_offer_record.attribute11
             ,p_attribute12                  => p_current_offer_record.attribute12
             ,p_attribute13                  => p_current_offer_record.attribute13
             ,p_attribute14                  => p_current_offer_record.attribute14
             ,p_attribute15                  => p_current_offer_record.attribute15
             ,p_attribute16                  => p_current_offer_record.attribute16
             ,p_attribute17                  => p_current_offer_record.attribute17
             ,p_attribute18                  => p_current_offer_record.attribute18
             ,p_attribute19                  => p_current_offer_record.attribute19
             ,p_attribute20                  => p_current_offer_record.attribute20
             ,p_attribute21                  => p_current_offer_record.attribute21
             ,p_attribute22                  => p_current_offer_record.attribute22
             ,p_attribute23                  => p_current_offer_record.attribute23
             ,p_attribute24                  => p_current_offer_record.attribute24
             ,p_attribute25                  => p_current_offer_record.attribute25
             ,p_attribute26                  => p_current_offer_record.attribute26
             ,p_attribute27                  => p_current_offer_record.attribute27
             ,p_attribute28                  => p_current_offer_record.attribute28
             ,p_attribute29                  => p_current_offer_record.attribute29
             ,p_attribute30                  => p_current_offer_record.attribute30
             ,p_mutiple_fields_updated       => l_mutiple_fields_updated
        );

Line: 2146

        if ( l_mutiple_fields_updated = true )
        then
           --
           hr_utility.set_location(l_proc,45);

Line: 2197

end chk_offer_status_update;

Line: 2224

Procedure chk_multiple_fields_updated
  ( p_offer_id                     in   number
   ,p_offer_status                 in   varchar2  default null
   ,p_discretionary_job_title      in   varchar2  default null
   ,p_offer_extended_method        in   varchar2  default null
   ,p_expiry_date                  in   date      default null
   ,p_proposed_start_date          in   date      default null
   ,p_offer_letter_tracking_code   in   varchar2  default null
   ,p_offer_postal_service         in   varchar2  default null
   ,p_offer_shipping_date          in   date      default null
   ,p_applicant_assignment_id      in   number    default null
   ,p_offer_assignment_id          in   number    default null
   ,p_address_id                   in   number    default null
   ,p_template_id                  in   number    default null
   ,p_offer_letter_file_type       in   varchar2  default null
   ,p_offer_letter_file_name       in   varchar2  default null
   ,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_attribute11                  in   varchar2  default null
   ,p_attribute12                  in   varchar2  default null
   ,p_attribute13                  in   varchar2  default null
   ,p_attribute14                  in   varchar2  default null
   ,p_attribute15                  in   varchar2  default null
   ,p_attribute16                  in   varchar2  default null
   ,p_attribute17                  in   varchar2  default null
   ,p_attribute18                  in   varchar2  default null
   ,p_attribute19                  in   varchar2  default null
   ,p_attribute20                  in   varchar2  default null
   ,p_attribute21                  in   varchar2  default null
   ,p_attribute22                  in   varchar2  default null
   ,p_attribute23                  in   varchar2  default null
   ,p_attribute24                  in   varchar2  default null
   ,p_attribute25                  in   varchar2  default null
   ,p_attribute26                  in   varchar2  default null
   ,p_attribute27                  in   varchar2  default null
   ,p_attribute28                  in   varchar2  default null
   ,p_attribute29                  in   varchar2  default null
   ,p_attribute30                  in   varchar2  default null
   ,p_mutiple_fields_updated       out nocopy boolean
  ) IS
--
  l_proc             varchar2(72)  := g_package || 'chk_multiple_fields_updated';

Line: 2275

  l_update_count     number(2)     := 0;

Line: 2279

    select
       offer_id
      ,offer_version
      ,latest_offer
      ,offer_status
      ,discretionary_job_title
      ,offer_extended_method
      ,respondent_id
      ,expiry_date
      ,proposed_start_date
      ,offer_letter_tracking_code
      ,offer_postal_service
      ,offer_shipping_date
      ,applicant_assignment_id
      ,offer_assignment_id
      ,address_id
      ,template_id
      ,offer_letter_file_type
      ,offer_letter_file_name
      ,attribute_category
      ,attribute1
      ,attribute2
      ,attribute3
      ,attribute4
      ,attribute5
      ,attribute6
      ,attribute7
      ,attribute8
      ,attribute9
      ,attribute10
      ,attribute11
      ,attribute12
      ,attribute13
      ,attribute14
      ,attribute15
      ,attribute16
      ,attribute17
      ,attribute18
      ,attribute19
      ,attribute20
      ,attribute21
      ,attribute22
      ,attribute23
      ,attribute24
      ,attribute25
      ,attribute26
      ,attribute27
      ,attribute28
      ,attribute29
      ,attribute30
      ,object_version_number
    from        irc_offers
    where       offer_id = p_offer_id;

Line: 2360

    l_update_count := l_update_count + 1;

Line: 2375

    l_update_count := l_update_count + 1;

Line: 2389

    l_update_count := l_update_count + 1;

Line: 2403

    l_update_count := l_update_count + 1;

Line: 2417

    l_update_count := l_update_count + 1;

Line: 2431

    l_update_count := l_update_count + 1;

Line: 2445

    l_update_count := l_update_count + 1;

Line: 2459

    l_update_count := l_update_count + 1;

Line: 2473

    l_update_count := l_update_count + 1;

Line: 2486

    l_update_count := l_update_count + 1;

Line: 2498

    l_update_count := l_update_count + 1;

Line: 2512

    l_update_count := l_update_count + 1;

Line: 2526

    l_update_count := l_update_count + 1;

Line: 2540

    l_update_count := l_update_count + 1;

Line: 2554

    l_update_count := l_update_count + 1;

Line: 2568

    l_update_count := l_update_count + 1;

Line: 2582

    l_update_count := l_update_count + 1;

Line: 2596

    l_update_count := l_update_count + 1;

Line: 2610

    l_update_count := l_update_count + 1;

Line: 2624

    l_update_count := l_update_count + 1;

Line: 2638

    l_update_count := l_update_count + 1;

Line: 2652

    l_update_count := l_update_count + 1;

Line: 2666

    l_update_count := l_update_count + 1;

Line: 2680

    l_update_count := l_update_count + 1;

Line: 2694

    l_update_count := l_update_count + 1;

Line: 2708

    l_update_count := l_update_count + 1;

Line: 2722

    l_update_count := l_update_count + 1;

Line: 2736

    l_update_count := l_update_count + 1;

Line: 2750

    l_update_count := l_update_count + 1;

Line: 2764

    l_update_count := l_update_count + 1;

Line: 2778

    l_update_count := l_update_count + 1;

Line: 2792

    l_update_count := l_update_count + 1;

Line: 2806

    l_update_count := l_update_count + 1;

Line: 2820

    l_update_count := l_update_count + 1;

Line: 2834

    l_update_count := l_update_count + 1;

Line: 2848

    l_update_count := l_update_count + 1;

Line: 2862

    l_update_count := l_update_count + 1;

Line: 2876

    l_update_count := l_update_count + 1;

Line: 2890

    l_update_count := l_update_count + 1;

Line: 2904

    l_update_count := l_update_count + 1;

Line: 2918

    l_update_count := l_update_count + 1;

Line: 2932

    l_update_count := l_update_count + 1;

Line: 2946

    l_update_count := l_update_count + 1;

Line: 2960

    l_update_count := l_update_count + 1;

Line: 2974

    l_update_count := l_update_count + 1;

Line: 2988

    l_update_count := l_update_count + 1;

Line: 2995

    if l_update_count > 1
    then
       --
       hr_utility.set_location(l_proc,520);

Line: 3000

       p_mutiple_fields_updated := true;

Line: 3005

       p_mutiple_fields_updated := false;

Line: 3013

End chk_multiple_fields_updated;

Line: 3258

   select offer_letter
     from irc_offers
    where offer_id = p_offer_id;

Line: 3428

Procedure insert_validate
  (p_effective_date               in date
  ,p_rec                          in out nocopy irc_iof_shd.g_rec_type
  ) is
--
  l_proc  varchar2(72) := g_package||'insert_validate';

Line: 3575

End insert_validate;

Line: 3580

Procedure update_validate
  (p_effective_date               in date
  ,p_rec                          in out nocopy irc_iof_shd.g_rec_type
  ) is
--
  l_proc  varchar2(72) := g_package||'update_validate';

Line: 3594

  chk_non_updateable_args
  (p_effective_date          => p_effective_date
  ,p_rec                     => p_rec
  );

Line: 3666

  chk_offer_status_update
  (p_current_offer_record   => p_rec
  );

Line: 3709

End update_validate;

Line: 3714

Procedure delete_validate
  (p_rec                          in irc_iof_shd.g_rec_type
  ) is
--
  l_proc  varchar2(72) := g_package||'delete_validate';

Line: 3726

End delete_validate;