APPS.IRC_PARTY_PERSON_UTL SQL Statements

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

Line: 3

procedure update_party_records(p_mode varchar2) is

  type t_number is table of number index by binary_integer;

Line: 27

  select party_id,party_id person_id
  from hz_parties
  where orig_system_reference='PER:IRC'
  union
  select party_id,person_id
  from irc_notification_preferences;

Line: 35

  select party_id,party_id person_id
  from hz_parties
  where orig_system_reference='PER:IRC'
  union
  select party_id,person_id
  from irc_notification_preferences
  union
  select party_id,person_id
  from per_addresses
  where party_id is not null
  and person_id is null
  union
  select party_id,parent_id person_id
  from per_phones
  where party_id is not null
  and parent_id is null
  union
  select party_id,person_id
  from per_previous_employers
  where party_id is not null
  and person_id is null
  union
  select party_id,person_id
  from per_qualifications
  where party_id is not null
  and person_id is null
  union
  select party_id,person_id
  from per_establishment_attendances
  where party_id is not null
  and person_id is null
  union
  select party_id,person_id
  from irc_documents
  where party_id is not null
  and person_id is null
  union
  select party_id,person_id
  from per_competence_elements
  where party_id is not null
  and person_id is null
  union
  select party_id,person_id
  from irc_job_basket_items
  where party_id is not null
  and person_id is null
  union
  select object_id party_id, object_id person_id
  from irc_search_criteria
  where object_type='WORK'
  union
  select party_id,person_id
  from irc_vacancy_considerations
  where party_id is not null
  and person_id is null;

Line: 92

  select hzpp.person_first_name
  ,hzpp.person_last_name
  ,hzpp.date_of_birth
  ,hzpp.person_title
  ,hzpp.gender
  ,hzpp.marital_status
  ,hzpp.person_previous_last_name
  ,hzpp.person_name_suffix
  ,hzpp.person_middle_name
  ,hzpp.known_as
  ,hzpp.person_first_name_phonetic
  ,hzpp.person_last_name_phonetic
  ,hzp.creation_date
  from hz_person_profiles hzpp
  ,    hz_parties hzp
  where hzpp.party_id=p_party_id
  and   hzp.party_id=hzpp.party_id
  and sysdate between hzpp.effective_start_date and nvl(hzpp.effective_end_date,sysdate);

Line: 112

  select usr1.email_address,usr1.start_date
  from fnd_user usr1
  where usr1.customer_id=p_party_id
  and usr1.start_date < sysdate
  union
  select usr1.email_address,usr1.start_date
  from fnd_user usr1
  ,per_all_people_f per1
  where usr1.employee_id=per1.person_id
  and usr1.start_date < sysdate
  and per1.party_id=p_party_id
  and trunc(sysdate) between per1.effective_start_date
  and per1.effective_end_date
  order by 2 desc;

Line: 133

  select legislation_code
  from per_business_groups
  where business_group_id=l_business_group_id;

Line: 140

  select person_id,effective_start_date
  from per_all_people_f
  where party_id=p_party_id
  and business_group_id=l_business_group_id
  order by effective_start_date asc;

Line: 147

  select email_address,object_version_number
  from per_all_people_f
  where person_id=p_person_id
  and trunc(sysdate) between effective_start_date and effective_end_date;

Line: 153

  select 1
  from per_person_type_usages_f ptu
  ,    per_person_types ppt
  where ptu.person_id=p_person_id
  and ptu.person_type_id=ppt.person_type_id
  and ppt.system_person_type='IRC_REG_USER';

Line: 161

  select user_name,employee_id
  from fnd_user
  where customer_id=p_party_id
  and employee_id is null
  and sysdate between start_date and nvl(end_date,sysdate);

Line: 172

  select org_information8
  from hr_organization_information
  where organization_id=l_business_group_id
  and ORG_INFORMATION_CONTEXT='BG Recruitment';

Line: 178

  select 1 from irc_search_criteria
  where object_id=p_person_id
  and object_type='WPREF';

Line: 183

  select 1 from irc_notification_preferences
  where party_id=p_party_id;

Line: 393

        hr_person_api.update_person
        (p_effective_date=>l_start_date
        ,p_datetrack_update_mode=>'CORRECTION'
        ,p_person_id=>l_person_ids(i)
        ,p_object_version_number=>l_object_version_number
        ,p_party_id =>l_party_ids(i)
        ,p_employee_number=> l_employee_number
        ,p_effective_start_date          => l_effective_start_date
	,p_effective_end_date            => l_effective_end_date
	,p_full_name                     => l_full_name
	,p_comment_id                    => l_comment_id
	,p_name_combination_warning      => l_name_combination_warning
        ,p_assign_payroll_warning        => l_assign_payroll_warning
	,p_orig_hire_warning             => l_orig_hire_warning);

Line: 440

          hr_person_api.update_person
          (p_effective_date=>sysdate
          ,p_datetrack_update_mode=>'CORRECTION'
          ,p_person_id=>l_person_ids(i)
          ,p_object_version_number=>l_object_version_number
          ,p_employee_number=> l_employee_number
          ,p_email_address=>l_email_address
          ,p_effective_start_date          => l_effective_start_date
          ,p_effective_end_date            => l_effective_end_date
	  ,p_full_name                     => l_full_name
	  ,p_comment_id                    => l_comment_id
	  ,p_name_combination_warning      => l_name_combination_warning
          ,p_assign_payroll_warning        => l_assign_payroll_warning
	  ,p_orig_hire_warning             => l_orig_hire_warning);

Line: 462

        fnd_user_pkg.updateUser
        (x_user_name=>usr_rec.user_name
        ,x_owner=>'CUST'
        ,x_employee_id=>l_person_ids(i)
        ,x_customer_id=>fnd_user_pkg.null_number);

Line: 472

      update per_phones
      set parent_id=l_person_ids(i)
      ,parent_table='PER_ALL_PEOPLE_F'
      where party_id=l_party_ids(i)
      and parent_id is null;

Line: 482

      update per_addresses
      set person_id=l_person_ids(i)
      ,business_group_id=l_business_group_id
      where party_id=l_party_ids(i)
      and person_id is null
      and address_type='REC';

Line: 492

      update per_previous_employers
      set person_id=l_person_ids(i)
      ,business_group_id=l_business_group_id
      where party_id=l_party_ids(i)
      and person_id is null;

Line: 501

      update per_qualifications
      set person_id=l_person_ids(i)
      ,business_group_id=l_business_group_id
      where party_id=l_party_ids(i)
      and person_id is null;

Line: 511

      update per_establishment_attendances
      set person_id=l_person_ids(i)
      ,business_group_id=l_business_group_id
      where party_id=l_party_ids(i)
      and person_id is null;

Line: 520

      update per_competence_elements
      set person_id=l_person_ids(i)
      ,business_group_id=l_business_group_id
      where party_id=l_party_ids(i)
      and person_id is null;

Line: 530

      update irc_documents
      set person_id=l_person_ids(i)
      where party_id=l_party_ids(i)
      and person_id is null;

Line: 539

      update irc_job_basket_items
      set person_id=l_person_ids(i)
      where party_id=l_party_ids(i)
      and person_id is null;

Line: 547

      update irc_search_criteria
      set object_id=l_person_ids(i)
      ,object_type='PERSON'
      where object_type = 'PARTY'
      and object_id=l_party_ids(i);

Line: 555

      update irc_search_criteria
      set object_id=l_person_ids(i)
      ,object_type='WPREF'
      where object_type = 'WORK'
      and object_id=l_party_ids(i);

Line: 570

        insert into irc_search_criteria
        (search_criteria_id
        ,object_id
        ,object_type
        ,employee
        ,contractor
        ,employment_category
        ,match_competence
        ,match_qualification
        ,salary_period
        ,last_update_date
        ,last_updated_by
        ,last_update_login
        ,created_by
        ,creation_date
        ,object_version_number)
        values
        (irc_search_criteria_s.nextval
        ,l_person_ids(i)
        ,'WPREF'
        ,'Y'
        ,'Y'
        ,'FULLTIME'
        ,'Y'
        ,'Y'
        ,'ANNUAL'
        ,sysdate
        ,1
        ,1
        ,1
        ,sysdate
        ,1);

Line: 607

      update irc_vacancy_considerations
      set person_id=l_person_ids(i)
      where party_id=l_party_ids(i)
      and person_id is null;

Line: 619

      update  irc_notification_preferences inp
      set person_id=l_person_ids(i)
      where party_id=l_party_ids(i)
      and person_id is null;

Line: 625

      insert into irc_notification_preferences
      (notification_preference_id
      ,party_id
      ,person_id
      ,matching_jobs
      ,matching_job_freq
      ,receive_info_mail
      ,allow_access
      ,last_update_date
      ,last_updated_by
      ,last_update_login
      ,created_by
      ,creation_date
      ,object_version_number)
      values
      (irc_notification_prefs_s.nextval
      ,l_party_ids(i)
      ,l_person_ids(i)
      ,'N'
      ,'1'
      ,'N'
      ,'N'
      ,sysdate
      ,1
      ,1
      ,1
      ,sysdate
      ,1);

Line: 657

    l_person_ids.delete;

Line: 658

    l_party_ids.delete;

Line: 668

end update_party_records;

Line: 670

procedure update_party_conc(errbuf  out nocopy varchar2
                           ,retcode out nocopy varchar2) is
--
l_proc varchar2(72) := 'irc_party_person_utl.update_party_conc';

Line: 677

irc_party_person_utl.update_party_records;

Line: 689

end update_party_conc;