APPS.PER_PL_PERSON_EXTRA_INFO SQL Statements

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

Line: 13

   select 1 from
      per_people_f pap,per_person_types ppf,per_person_type_usages_f ptu
   where pap.person_id = P_PERSON_ID and
         pap.person_id = ptu.person_id and
         ppf.person_type_id = ptu.person_type_id and
         ppf.SEEDED_PERSON_TYPE_KEY = 'CONTACT' and
         pap.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
         pap.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) and
         ptu.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
         ptu.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'));

Line: 50

  select 1 from per_people_extra_info where
      person_id = P_PERSON_ID and
      information_type = P_INFORMATION_TYPE and
      (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(PEI_INFORMATION1)
                                 and  nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
	        nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
                    between fnd_date.canonical_to_date(PEI_INFORMATION1)
                                 and  nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
                fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
                                nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
                nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
                                 fnd_date.canonical_to_date(P_FLEX_START_DATE) and
                               nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')));

Line: 64

cursor cur_inherit is select per_information2 from per_all_people_f where person_id =P_PERSON_ID
and effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE)
and effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
and per_information2 ='Y';

Line: 107

PROCEDURE UPDATE_CON_DATE_CHK(P_PERSON_EXTRA_INFO_ID     NUMBER
                             ,P_PEI_INFORMATION_CATEGORY VARCHAR2
                             ,P_FLEX_START_DATE          VARCHAR2
                             ,P_FLEX_END_DATE            VARCHAR2) IS


-- This cursor checks if the Start/End Dates are not overlapping with other records

cursor csr_overlap_upd_rec is
  select 1 from per_people_extra_info where
      pei_information_category = P_PEI_INFORMATION_CATEGORY and
      (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(PEI_INFORMATION1)
                                 and  nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
	        nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
                    between fnd_date.canonical_to_date(PEI_INFORMATION1)
                                 and  nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
                fnd_date.canonical_to_date(PEI_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
                                nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
                nvl(fnd_date.canonical_to_date(PEI_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
                                 fnd_date.canonical_to_date(P_FLEX_START_DATE) and
                               nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))) and
               person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID) and
               person_extra_info_id <> P_PERSON_EXTRA_INFO_ID;

Line: 136

   select 1 from
      per_people_f pap,per_person_types ppf,per_person_type_usages_f ptu
   where pap.person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID) and
         pap.person_id = ptu.person_id and
         ppf.person_type_id = ptu.person_type_id and
         ppf.SEEDED_PERSON_TYPE_KEY = 'CONTACT' and
         pap.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
         pap.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) and
         ptu.effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE) and
         ptu.effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'));

Line: 147

cursor cur_inherit is select per_information2 from per_all_people_f where
 person_id in (select person_id from per_people_extra_info where person_extra_info_id = P_PERSON_EXTRA_INFO_ID)
and effective_start_date <= fnd_date.canonical_to_date(P_FLEX_START_DATE)
and effective_end_date >= nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
and per_information2 ='Y';

Line: 207

END UPDATE_CON_DATE_CHK;

Line: 284

PROCEDURE UPDATE_PL_PERSON_EXTRA_INFO
   (P_PERSON_EXTRA_INFO_ID     in NUMBER
   ,P_PEI_INFORMATION_CATEGORY in VARCHAR2
   ,P_PEI_INFORMATION1         in VARCHAR2
   ,P_PEI_INFORMATION2         in VARCHAR2
   ,P_PEI_INFORMATION3         in VARCHAR2
   ,P_PEI_INFORMATION4         in VARCHAR2
   ,P_PEI_INFORMATION5         in VARCHAR2
   ,P_PEI_INFORMATION6         in VARCHAR2
   ,P_PEI_INFORMATION7         in VARCHAR2
   ,P_PEI_INFORMATION8         in VARCHAR2
   ,P_PEI_INFORMATION9         in VARCHAR2
   ,P_PEI_INFORMATION10        in VARCHAR2
   ,P_PEI_INFORMATION11        in VARCHAR2
   ,P_PEI_INFORMATION12        in VARCHAR2
   ,P_PEI_INFORMATION13        in VARCHAR2
   ,P_PEI_INFORMATION14        in VARCHAR2
   ,P_PEI_INFORMATION15        in VARCHAR2
   ,P_PEI_INFORMATION16        in VARCHAR2
   ,P_PEI_INFORMATION17        in VARCHAR2
   ,P_PEI_INFORMATION18        in VARCHAR2
   ,P_PEI_INFORMATION19        in VARCHAR2
   ,P_PEI_INFORMATION20        in VARCHAR2
   ,P_PEI_INFORMATION21        in VARCHAR2
   ,P_PEI_INFORMATION22        in VARCHAR2
   ,P_PEI_INFORMATION23        in VARCHAR2
   ,P_PEI_INFORMATION24        in VARCHAR2
   ,P_PEI_INFORMATION25        in VARCHAR2
   ,P_PEI_INFORMATION26        in VARCHAR2
   ,P_PEI_INFORMATION27        in VARCHAR2
   ,P_PEI_INFORMATION28        in VARCHAR2
   ,P_PEI_INFORMATION29        in VARCHAR2
   ,P_PEI_INFORMATION30        in VARCHAR2) IS

BEGIN
  /* Added for GSI Bug 5472781 */
IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
   hr_utility.trace('PL not installed.Leaving UPDATE_PL_PERSON_EXTRA_INFO');

Line: 327

     UPDATE_CON_DATE_CHK(P_PERSON_EXTRA_INFO_ID
                        ,P_PEI_INFORMATION_CATEGORY
                        ,P_PEI_INFORMATION1
                        ,P_PEI_INFORMATION2);

Line: 335

     UPDATE_CON_DATE_CHK(P_PERSON_EXTRA_INFO_ID
                        ,P_PEI_INFORMATION_CATEGORY
                        ,P_PEI_INFORMATION1
                        ,P_PEI_INFORMATION2);

Line: 342

END UPDATE_PL_PERSON_EXTRA_INFO;