eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IRC_UTILITIES_PKG SQL Statements

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

Line: 56


select fe.function_id
from fnd_menu_entries fe
where fe.function_id is not null
start with fe.menu_id=
(select resp.menu_id from fnd_responsibility resp
 where resp.responsibility_id=p_responsibility_id
 and resp.application_id=800)
connect by prior fe.sub_menu_id= fe.menu_id
             and fe.grant_flag='Y'
order by level,fe.entry_sequence;

Line: 68


select fff.function_name
from fnd_form_functions fff
where fff.function_id=p_function_id;

Line: 124


  SELECT empl.EMPLOYER_NAME
  FROM   PER_PREVIOUS_EMPLOYERS empl,
         PER_ALL_PEOPLE_F ppf,
         PER_ALL_PEOPLE_F ppf2
  WHERE  ppf.person_id = p_person_id
  AND    ppf.party_id = ppf2.party_id
  AND    p_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
  AND    p_date BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
  AND    ppf2.person_id = empl.person_id
  AND    p_date BETWEEN nvl(empl.START_DATE,trunc(SYSDATE))
                        and(nvl(empl.END_DATE,hr_api.g_eot));

Line: 160


  SELECT ppe.employer_name
    FROM per_all_people_f       ppf
        ,per_previous_employers ppe
   WHERE ppf.party_id = p_party_id
     AND p_eff_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
     AND ppe.person_id = ppf.person_id
   ORDER BY NVL(ppe.end_date,HR_GENERAL.end_of_time) DESC
           ,NVL(ppe.start_date,HR_GENERAL.start_of_time) DESC;

Line: 198


  SELECT QTYP.NAME,qtyp.rank
  FROM   PER_QUALIFICATION_TYPES QTYP,
         PER_QUALIFICATIONS QUAL,
         PER_ALL_PEOPLE_F ppf,
         PER_ALL_PEOPLE_F ppf2
  WHERE ppf.person_id = p_person
  AND ppf.party_id = ppf2.party_id
  AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
  AND trunc(sysdate) BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
  AND ppf2.person_id = QUAL.PERSON_ID
  AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
  order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;

Line: 213


  SELECT QTYP.NAME
  FROM   PER_QUALIFICATION_TYPES QTYP,
         PER_QUALIFICATIONS QUAL,
         PER_ESTABLISHMENT_ATTENDANCES ESTAB,
         PER_ALL_PEOPLE_F ppf,
         PER_ALL_PEOPLE_F ppf2
  WHERE ppf.person_id = p_person
  AND ppf.party_id = ppf2.party_id
  AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
  AND trunc(sysdate) BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
  AND ppf2.person_id = ESTAB.PERSON_ID
  AND estab.attendance_id=qual.attendance_id
  AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
  and nvl(qtyp.rank,-1)>=nvl(p_max_rank,-1)
  order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;

Line: 262


  SELECT QTYP.NAME
  FROM   PER_QUALIFICATION_TYPES QTYP,
         PER_QUALIFICATIONS QUAL,
         PER_ALL_PEOPLE_F ppf
  WHERE ppf.party_id = p_party_id
  AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
  AND ppf.person_id = QUAL.PERSON_ID
  AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
  order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;

Line: 273


  SELECT /*+ FIRST_ROWS */ QTYP.NAME
  FROM   PER_QUALIFICATION_TYPES QTYP,
         PER_QUALIFICATIONS QUAL,
         PER_ESTABLISHMENT_ATTENDANCES ESTAB,
         PER_ALL_PEOPLE_F ppf
  WHERE ppf.party_id = p_party_id
  AND trunc(sysdate) BETWEEN ppf.effective_start_date and ppf.effective_end_date
  AND ppf.person_id = ESTAB.PERSON_ID
  AND estab.attendance_id=qual.attendance_id
  AND QUAL.QUALIFICATION_TYPE_ID = QTYP.QUALIFICATION_TYPE_ID
  and not exists (select 1 from per_qualifications qual2,per_qualification_types qtyp2
                  where qual2.person_id=ppf.person_id
                  and qtyp2.qualification_type_id=qual2.qualification_type_id
                  and nvl(qtyp2.rank,-1)>nvl(qtyp.rank,-1))
  order by QTYP.RANK desc, qual.awarded_date desc, qual.creation_date desc;

Line: 328


    SELECT ttl.user_person_type
      FROM per_person_types_tl ttl
          ,per_person_types typ
          ,per_person_type_usages_f ptu
          ,PER_ALL_PEOPLE_F ppf
          ,PER_ALL_PEOPLE_F ppf2
    WHERE ppf.person_id = p_person_id
    AND ppf.party_id = ppf2.party_id
    AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
    AND p_eff_date BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
    AND ppf2.person_id = ptu.person_id
    AND ttl.language = userenv('LANG')
    AND ttl.person_type_id = typ.person_type_id
    AND typ.system_person_type IN ('EMP','EX_EMP')
    AND typ.person_type_id = ptu.person_type_id
    AND p_eff_date BETWEEN ptu.effective_start_date
                          AND ptu.effective_end_date
       order by typ.system_person_type ASC, ptu.effective_start_date DESC;

Line: 372


    SELECT ttl.user_person_type
      FROM per_person_types_tl ttl
          ,per_person_types typ
          ,per_person_type_usages_f ptu
          ,PER_ALL_PEOPLE_F ppf
    WHERE ppf.party_id = p_party_id
    AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
    AND ppf.person_id = ptu.person_id
    AND ttl.language = userenv('LANG')
    AND ttl.person_type_id = typ.person_type_id
    AND typ.system_person_type IN ('EMP','EX_EMP')
    AND typ.person_type_id = ptu.person_type_id
    AND p_eff_date BETWEEN ptu.effective_start_date
                          AND ptu.effective_end_date
       order by typ.system_person_type ASC, ptu.effective_start_date DESC;

Line: 418


    SELECT GET_EMP_UPT_FOR_PERSON (ppf.person_id,p_eff_date)
      FROM per_people_f ppf
     WHERE ppf.person_id = p_person_id
       AND p_eff_date BETWEEN ppf.effective_start_date
                          AND ppf.effective_end_date;

Line: 426


    SELECT ttl.user_person_type
      FROM per_person_types_tl ttl
          ,per_person_types typ
          ,per_person_type_usages_f ptu
          ,PER_ALL_PEOPLE_F ppf
          ,PER_ALL_PEOPLE_F ppf2
    WHERE ppf.person_id = p_person_id
    AND ppf.party_id = ppf2.party_id
    AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
    AND p_eff_date BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
    AND ppf2.person_id = ptu.person_id
    AND ttl.language = userenv('LANG')
    AND ttl.person_type_id = typ.person_type_id
    AND typ.system_person_type IN ('APL','EX_APL')
    AND typ.person_type_id = ptu.person_type_id
    AND p_eff_date BETWEEN ptu.effective_start_date
                       AND ptu.effective_end_date
    ORDER BY typ.system_person_type ASC, ptu.effective_start_date DESC;

Line: 480


    SELECT ttl.user_person_type
      FROM per_person_types_tl ttl
          ,per_person_types typ
          ,per_person_type_usages_f ptu
          ,PER_ALL_PEOPLE_F ppf
    WHERE ppf.party_id = p_party_id
    AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
    AND ppf.person_id = ptu.person_id
    AND ttl.language = userenv('LANG')
    AND ttl.person_type_id = typ.person_type_id
    AND typ.system_person_type IN ('APL','EX_APL')
    AND typ.person_type_id = ptu.person_type_id
    AND p_eff_date BETWEEN ptu.effective_start_date
                          AND ptu.effective_end_date
       order by typ.system_person_type ASC, ptu.effective_start_date DESC;

Line: 523


    SELECT typ.system_person_type
      FROM per_person_types typ
          ,per_person_type_usages_f ptu
          ,PER_ALL_PEOPLE_F ppf
          ,PER_ALL_PEOPLE_F ppf2
    WHERE ppf.person_id = p_person_id
    AND ppf.party_id = ppf2.party_id
    AND p_eff_date BETWEEN ppf.effective_start_date and ppf.effective_end_date
    AND p_eff_date BETWEEN ppf2.effective_start_date and ppf2.effective_end_date
    AND ppf2.person_id = ptu.person_id
    AND typ.system_person_type IN ('EMP','EX_EMP')
    AND typ.person_type_id = ptu.person_type_id
    AND p_eff_date BETWEEN ptu.effective_start_date
                       AND ptu.effective_end_date
    order by typ.system_person_type ASC, ptu.effective_start_date DESC;

Line: 568


  SELECT inp.person_id
  FROM PER_ALL_PEOPLE_F PPF,
       IRC_NOTIFICATION_PREFERENCES INP,
       PER_ALL_PEOPLE_F PPF2
  WHERE ppf.person_id = p_person_id
  AND p_effective_date BETWEEN ppf.effective_start_date
                        AND ppf.effective_end_date
  AND ppf.party_id = ppf2.party_id
  AND p_effective_date BETWEEN ppf2.effective_start_date
                        AND ppf2.effective_end_date
  AND ppf2.person_id = inp.person_id;

Line: 606


  select 1
  from per_all_people_f per_per
  where per_per.party_id=p_party_id
  and p_eff_date between per_per.effective_start_date and per_per.effective_end_date
  and per_per.current_employee_flag='Y';

Line: 613


  select 1
  from per_all_people_f per_per
  ,per_all_assignments_f per_asg
  ,per_vacancies per_vac
  where per_per.party_id=p_party_id
  and p_eff_date between per_per.effective_start_date and per_per.effective_end_date
  and per_per.person_id=per_asg.person_id
  and p_eff_date between per_asg.effective_start_date and per_asg.effective_end_date
  and per_asg.vacancy_id=per_vac.vacancy_id;

Line: 657


  select 1
  from per_all_people_f per_per
  where per_per.person_id=p_person_id
  and p_eff_date between per_per.effective_start_date and per_per.effective_end_date
  and (per_per.current_employee_flag='Y' or per_per.current_npw_flag='Y');

Line: 664


  select 1
  from per_all_people_f per_per
  ,    per_all_people_f per_per2
  where per_per.person_id=p_person_id
  and p_eff_date between per_per.effective_start_date and per_per.effective_end_date
  and per_per.party_id is not null
  and per_per2.party_id=per_per.party_id
  and p_eff_date between per_per2.effective_start_date and per_per2.effective_end_date
  and (per_per2.current_employee_flag='Y' or per_per2.current_npw_flag='Y');

Line: 708


select employee_id
from fnd_user
where upper(email_address)=upper(p_email_address);

Line: 745


select employee_id
from fnd_user
where user_name=upper(p_user_name);

Line: 800


  SELECT pqt.name
    FROM per_qualifications         pq
        ,per_qualification_types_vl pqt
   WHERE pq.party_id = p_party_id
     AND pq.attendance_id IS NOT NULL
     AND pq.awarded_date  IS NOT NULL
     AND pqt.qualification_type_id = pq.qualification_type_id
   ORDER BY pq.awarded_date DESC;

Line: 859


                                   ,p_update_mode                  in     varchar2
                                   ,p_details_version              out nocopy number
                                   ,p_effective_start_date         out nocopy date
                                   ,p_effective_end_date           out nocopy date
                                   ,p_object_version_number        out nocopy number
                                   )is
 pragma autonomous_transaction;

Line: 878


 l_update_mode               varchar2(30) := p_update_mode;

Line: 884


    select *
      from irc_assignment_details_f
     where assignment_id =p_assignment_id
       and sysdate between effective_start_date and effective_end_Date
       and latest_details='Y';

Line: 891


    select business_group_id
      from per_all_assignments_f
     where assignment_id = p_assignment_id
       and trunc(sysdate) between effective_start_date and effective_end_date;

Line: 897


    select nvl(ORG_INFORMATION11,'N')
      from HR_ORGANIZATION_INFORMATION
     where organization_id = l_business_grp_id
       and org_information_context = 'BG Recruitment';

Line: 935


        hr_utility.set_location('p_update_mode::'||p_update_mode,50);

Line: 940


        irc_assignment_details_api.update_assignment_details (
          p_validate                      =>     false
         ,p_effective_date               =>     l_effective_date
         ,p_datetrack_update_mode        =>     l_update_mode
         ,p_assignment_id                =>     l_assignment_id
         ,p_attempt_id                   =>     l_attempt_id
         ,p_qualified                    =>     l_qualified
         ,p_considered                   =>     l_considered
         ,p_assignment_details_id        =>     l_assignment_details_id
         ,p_object_version_number        =>     l_object_version_number
         ,p_details_version              =>     l_details_version
         ,p_effective_start_date         =>     l_effective_start_date
         ,p_effective_end_date           =>     l_effective_end_date
         );

Line: 954


       hr_utility.set_location('Updated assignment details record',60);

Line: 987


 l_update_mode               varchar2(30);

Line: 992


 cursor c_bg_id is select business_group_id from per_all_assignments_f where assignment_id=p_assignment_id and trunc(sysdate) between effective_start_date and effective_end_date;

Line: 993


 cursor c_assgn_details_row is select * from irc_assignment_details_f where assignment_id =p_assignment_id and latest_details='Y';

Line: 994


 cursor c_assgn_details is select 1 from irc_assignment_details_f where assignment_id =p_assignment_id and sysdate between effective_start_date and effective_end_Date and latest_details='Y';

Line: 995


 cursor c_appln_tracking is select nvl(ORG_INFORMATION11,'N') from HR_ORGANIZATION_INFORMATION where organization_id=l_business_grp_id and org_information_context='BG Recruitment';

Line: 1033


   l_update_mode:='UPDATE';

Line: 1035


   l_update_mode:='CORRECTION';

Line: 1040


   irc_assignment_details_api.update_assignment_details (
      p_validate                      =>     false
     ,p_effective_date               =>     l_effective_date
     ,p_datetrack_update_mode        =>     l_update_mode
     ,p_assignment_id                =>     l_assignment_id
     ,p_attempt_id                   =>     l_assgn_details_row.attempt_id
     ,p_qualified                    =>     l_assgn_details_row.qualified
     ,p_considered                   =>     l_considered
     ,p_assignment_details_id        =>     l_assignment_details_id
     ,p_object_version_number        =>     l_object_version_number
     ,p_details_version              =>     l_details_version
     ,p_effective_start_date         =>     l_effective_start_date
     ,p_effective_end_date           =>     l_effective_end_date
    );

Line: 1067


SELECT history.transaction_history_id
      ,irc_xml_util.valueOf(histstate.transaction_document,'/Transaction/TransCtx/pAMETranType')
      ,irc_xml_util.valueOf(histstate.transaction_document,'/Transaction/TransCtx/pAMEAppId')
FROM   pqh_ss_transaction_history history,
       pqh_ss_trans_state_history histstate
WHERE history.transaction_identifier = 'OFFER'
  AND history.transaction_history_id = ( SELECT min(transaction_history_id)
                                           FROM pqh_ss_step_history
                                          WHERE api_name = 'IRC_OFFERS_SWI.PROCESS_OFFERS_API'
                                            AND pk1 = c_offerId )
  AND histstate.transaction_history_id = history.transaction_history_id;