DBA Data[Home] [Help]

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 1 = (select count(*)
                from irc_offer_status_history ios2
               where ios2.offer_id = p_offer_id
                 and ios2.offer_status_history_id > ios1.offer_status_history_id);
Line: 749

         select offer_status
           from irc_offers
          where offer_id = p_offer_id;
Line: 754

         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_status_history_id = (select max(offer_status_history_id) from irc_offer_status_history where offer_id = iof.offer_id)
            and iof.latest_offer = 'Y'
            and ( iof.offer_status = 'EXTENDED'  or ( iof.offer_status = 'CLOSED' and   iosh.change_reason = 'APL_ACCEPTED'));
Line: 911

         select 1
           from fnd_user
          where user_id = p_respondent_id;
Line: 1178

         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: 1268

         select 1
           from per_addresses
          where address_id = p_address_id;
Line: 1369

         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: 1377

         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: 1387

         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: 1397

         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: 1534

     select nvl(max(offer_version),0) + 1
     from   irc_offers
     where  applicant_assignment_id = p_applicant_assignment_id;
Line: 1631

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

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

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

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: 2001

  l_mutiple_fields_updated      boolean;
Line: 2004

  select ios1.offer_status
    from irc_offer_status_history ios1
   where ios1.offer_id = p_current_offer_record.offer_id
     and 1 = (select count(*)
                from irc_offer_status_history ios2
               where ios2.offer_id = p_current_offer_record.offer_id
                 and ios2.offer_status_history_id > ios1.offer_status_history_id);
Line: 2046

        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: 2095

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

end chk_offer_status_update;
Line: 2173

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: 2224

  l_update_count     number(2)     := 0;
Line: 2228

    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: 2309

    l_update_count := l_update_count + 1;
Line: 2324

    l_update_count := l_update_count + 1;
Line: 2338

    l_update_count := l_update_count + 1;
Line: 2352

    l_update_count := l_update_count + 1;
Line: 2366

    l_update_count := l_update_count + 1;
Line: 2380

    l_update_count := l_update_count + 1;
Line: 2394

    l_update_count := l_update_count + 1;
Line: 2408

    l_update_count := l_update_count + 1;
Line: 2422

    l_update_count := l_update_count + 1;
Line: 2435

    l_update_count := l_update_count + 1;
Line: 2447

    l_update_count := l_update_count + 1;
Line: 2461

    l_update_count := l_update_count + 1;
Line: 2475

    l_update_count := l_update_count + 1;
Line: 2489

    l_update_count := l_update_count + 1;
Line: 2503

    l_update_count := l_update_count + 1;
Line: 2517

    l_update_count := l_update_count + 1;
Line: 2531

    l_update_count := l_update_count + 1;
Line: 2545

    l_update_count := l_update_count + 1;
Line: 2559

    l_update_count := l_update_count + 1;
Line: 2573

    l_update_count := l_update_count + 1;
Line: 2587

    l_update_count := l_update_count + 1;
Line: 2601

    l_update_count := l_update_count + 1;
Line: 2615

    l_update_count := l_update_count + 1;
Line: 2629

    l_update_count := l_update_count + 1;
Line: 2643

    l_update_count := l_update_count + 1;
Line: 2657

    l_update_count := l_update_count + 1;
Line: 2671

    l_update_count := l_update_count + 1;
Line: 2685

    l_update_count := l_update_count + 1;
Line: 2699

    l_update_count := l_update_count + 1;
Line: 2713

    l_update_count := l_update_count + 1;
Line: 2727

    l_update_count := l_update_count + 1;
Line: 2741

    l_update_count := l_update_count + 1;
Line: 2755

    l_update_count := l_update_count + 1;
Line: 2769

    l_update_count := l_update_count + 1;
Line: 2783

    l_update_count := l_update_count + 1;
Line: 2797

    l_update_count := l_update_count + 1;
Line: 2811

    l_update_count := l_update_count + 1;
Line: 2825

    l_update_count := l_update_count + 1;
Line: 2839

    l_update_count := l_update_count + 1;
Line: 2853

    l_update_count := l_update_count + 1;
Line: 2867

    l_update_count := l_update_count + 1;
Line: 2881

    l_update_count := l_update_count + 1;
Line: 2895

    l_update_count := l_update_count + 1;
Line: 2909

    l_update_count := l_update_count + 1;
Line: 2923

    l_update_count := l_update_count + 1;
Line: 2937

    l_update_count := l_update_count + 1;
Line: 2944

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

       p_mutiple_fields_updated := true;
Line: 2954

       p_mutiple_fields_updated := false;
Line: 2962

End chk_multiple_fields_updated;
Line: 3207

   select offer_letter
     from irc_offers
    where offer_id = p_offer_id;
Line: 3377

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: 3521

End insert_validate;
Line: 3526

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: 3540

  chk_non_updateable_args
  (p_effective_date          => p_effective_date
  ,p_rec                     => p_rec
  );
Line: 3612

  chk_offer_status_update
  (p_current_offer_record   => p_rec
  );
Line: 3655

End update_validate;
Line: 3660

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

End delete_validate;