APPS.PER_ZA_USER_HOOK_PKG SQL Statements

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

Line: 35

         SELECT typ.system_person_type
           FROM per_person_types typ
          WHERE typ.person_type_id     = p_person_type_id;

Line: 355

           SELECT  typ.system_person_type
           FROM    per_person_types typ
           WHERE   typ.person_type_id   = p_person_type_id;

Line: 650

  select pei.AEI_INFORMATION4,papf.person_id
  from PER_ASSIGNMENT_EXTRA_INFO pei, per_all_assignments_f paf, per_all_people_f papf
  where pei.assignment_id = paf.assignment_id
   and paf.person_id = papf.person_id
   and papf.employee_number = p_employee_number
   and paf.primary_flag = 'Y'
   and
       (
         SELECT max (paaf1.effective_start_date)
           FROM per_all_assignments_f paaf1
          WHERE paaf1.assignment_id = paf.assignment_id
        ) BETWEEN paf.effective_start_date AND paf.effective_end_date
   and paf.effective_start_date BETWEEN papf.effective_start_date
                                      AND papf.effective_end_date
   and pei.information_type = 'ZA_SPECIFIC_INFO';

Line: 672

         SELECT ORG_INFORMATION2
         FROM   HR_ORGANIZATION_INFORMATION
         WHERE  ORG_INFORMATION_CONTEXT = 'Business Group Information'
         AND    organization_id = p_business_group_id ;

Line: 785

                     update PER_ASSIGNMENT_EXTRA_INFO
                     set AEI_INFORMATION4 = p_per_information12
                     where assignment_id in (select paaf.assignment_id
                                               from per_all_assignments_f paaf
                                              where paaf.person_id = l_person_id
                                              and paaf.primary_flag ='Y'
                                              and
                                                 (
                                                  SELECT max (paaf1.effective_start_date)
                                                    FROM per_all_assignments_f paaf1
                                                   WHERE paaf1.assignment_id = paaf.assignment_id
                                                 ) BETWEEN paaf.effective_start_date AND paaf.effective_end_date)
                                              and information_type = 'ZA_SPECIFIC_INFO';

Line: 1030

      SELECT person_type_id
        FROM per_all_people_f
       WHERE person_id = p_person_id
         AND p_effective_date BETWEEN effective_start_date
                                  AND effective_end_date;

Line: 1037

      SELECT per_information4
        FROM per_all_people_f
       WHERE person_id = p_person_id
         AND p_effective_date BETWEEN effective_start_date
                                  AND effective_end_date;

Line: 1044

    select per_information12
          from per_all_people_f
     where person_id = p_person_id
    and p_effective_date BETWEEN effective_start_date
                                  AND effective_end_date;

Line: 1052

   select AEI_INFORMATION4
           from PER_ASSIGNMENT_EXTRA_INFO
          where assignment_id in (select paaf.assignment_id
                                    from per_all_assignments_f paaf
                                   where paaf.person_id = p_person_id
                                   and paaf.primary_flag ='Y'
                                   and
                                        (
                                         SELECT max (paaf1.effective_start_date)
                                           FROM per_all_assignments_f paaf1
                                          WHERE paaf1.assignment_id = paaf.assignment_id
                                        ) BETWEEN paaf.effective_start_date AND paaf.effective_end_date)
                                   and information_type = 'ZA_SPECIFIC_INFO';

Line: 1070

     select EMPLOYEE_NUMBER
     from   PER_ALL_PEOPLE_F
     where  person_id = p_person_id;

Line: 1082

         SELECT ORG_INFORMATION2
         FROM   HR_ORGANIZATION_INFORMATION
         WHERE  ORG_INFORMATION_CONTEXT = 'Business Group Information'
         AND    ORGANIZATION_ID = (SELECT BUSINESS_GROUP_ID
                                   FROM   PER_ALL_PEOPLE_F
                                   WHERE  PERSON_ID = p_person_id
				   AND    p_effective_date BETWEEN effective_start_date
                                                           AND     effective_end_date);    /* Bug 12674949 */

Line: 1250

                     update PER_ASSIGNMENT_EXTRA_INFO
                     set AEI_INFORMATION4 = l_person_nop
                     where assignment_id in (select paaf.assignment_id
                                               from per_all_assignments_f paaf
                                              where paaf.person_id = p_person_id
                                              and paaf.primary_flag ='Y'
                                              and
                                                (
                                                 SELECT max (paaf1.effective_start_date)
                                                   FROM per_all_assignments_f paaf1
                                                  WHERE paaf1.assignment_id = paaf.assignment_id
                                                ) BETWEEN paaf.effective_start_date AND paaf.effective_end_date)
                                              and information_type = 'ZA_SPECIFIC_INFO';

Line: 1421

                     SELECT paaf.primary_flag
                       INTO l_asg_primary_flag
                       FROM per_all_assignments_f paaf
                      WHERE paaf.assignment_id = P_ASSIGNMENT_ID
                        AND
                            (
                             SELECT max (paaf1.effective_start_date)
                               FROM per_all_assignments_f paaf1
                              WHERE paaf1.assignment_id = paaf.assignment_id
                            ) BETWEEN paaf.effective_start_date AND paaf.effective_end_date;

Line: 1434

                            SELECT per_information12
                              INTO l_person_nop
                              FROM per_all_people_f paf
                                  ,per_all_assignments_f paaf
                             WHERE paaf.assignment_id = P_ASSIGNMENT_ID
                               AND paaf.person_id = paf.person_id
                               AND paaf.primary_flag = 'Y'
                               AND
                                   (
                                    SELECT max (paaf1.effective_start_date)
                                      FROM per_all_assignments_f paaf1
                                     WHERE paaf1.assignment_id = paaf.assignment_id
                                   ) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
                               AND paaf.effective_start_date BETWEEN paf.effective_start_date
                                                                 AND paf.effective_end_date;

Line: 1451

                           fnd_message.set_name('PER', 'HR_ZA_UPDATE_NOP_PERSON_LEVEL');

Line: 1467

  procedure update_za_assign_extra_info(p_assignment_extra_info_id  in  number
                                        ,P_AEI_INFORMATION_CATEGORY in VARCHAR2
                                        , P_AEI_INFORMATION2 in VARCHAR2
                                        , P_AEI_INFORMATION4 in VARCHAR2
                                        , P_AEI_INFORMATION13 in VARCHAR2
                                        , P_AEI_INFORMATION14 in VARCHAR2) is


    l_person_nop varchar2(10);

Line: 1498

                         SELECT paaf.primary_flag
                           INTO l_asg_primary_flag
                           FROM per_all_assignments_f paaf
                               ,per_assignment_extra_info pai
                          WHERE pai.assignment_extra_info_id = P_ASSIGNMENT_EXTRA_INFO_ID
                            AND pai.assignment_id = paaf.assignment_id
                            AND
                                (
                                 SELECT max (paaf1.effective_start_date)
                                   FROM per_all_assignments_f paaf1
                                  WHERE paaf1.assignment_id = paaf.assignment_id
                                ) BETWEEN paaf.effective_start_date AND paaf.effective_end_date;

Line: 1512

                         SELECT paf.per_information12
                           INTO l_person_nop
                           FROM per_all_people_f paf
                               ,per_all_assignments_f paaf
                               ,per_assignment_extra_info pai
                          WHERE pai.assignment_extra_info_id = P_ASSIGNMENT_EXTRA_INFO_ID
                            AND pai.assignment_id = paaf.assignment_id
                            AND paf.person_id = paaf.person_id
                            AND
                                (
                                 SELECT max (paaf1.effective_start_date)
                                   FROM per_all_assignments_f paaf1
                                  WHERE paaf1.assignment_id = paaf.assignment_id
                                ) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
                            AND paaf.effective_start_date BETWEEN paf.effective_start_date AND paf.effective_end_date;

Line: 1529

                   fnd_message.set_name('PER', 'HR_ZA_UPDATE_NOP_PERSON_LEVEL');

Line: 1543

    end update_za_assign_extra_info;

Line: 2035

                    select count(*)
                    into   l_count
                    from   pay_personal_payment_methods_f
                    where  assignment_id = P_ASSIGNMENT_ID
                    and    personal_payment_method_id <>P_PERSONAL_PAYMENT_METHOD_ID
                    and    effective_start_date <= P_EFFECTIVE_END_DATE and effective_end_date >= P_EFFECTIVE_START_DATE
                    and    PPM_INFORMATION_CATEGORY in ('ZA_ACB','ZA_CHEQUE','ZA_CREDIT TRANSFER','ZA_MANUAL PAYMENT')
                    and    nvl(PPM_INFORMATION1,'N') in ('Y','0','7');

Line: 2056

    procedure validate_update_per_payment  (P_EFFECTIVE_DATE              IN  DATE
                                           ,P_EFFECTIVE_START_DATE        IN  DATE
                                           ,P_EFFECTIVE_END_DATE          IN  DATE
                                           ,P_PERSONAL_PAYMENT_METHOD_ID  IN  NUMBER
                                           ,P_PPM_INFORMATION1            IN  VARCHAR2)
    is
    l_count NUMBER;

Line: 2067

            select  assignment_id
            into    l_assignment_id
            from    pay_personal_payment_methods_f
            where   personal_payment_method_id = P_PERSONAL_PAYMENT_METHOD_ID
             and    P_EFFECTIVE_DATE between effective_start_date and effective_end_date;

Line: 2074

                    select count(*)
                    into   l_count
                    from   pay_personal_payment_methods_f
                    where  assignment_id = l_assignment_id
                    and    personal_payment_method_id <>P_PERSONAL_PAYMENT_METHOD_ID
                    and    PPM_INFORMATION_CATEGORY in ('ZA_ACB','ZA_CHEQUE','ZA_CREDIT TRANSFER','ZA_MANUAL PAYMENT')
                    and    effective_start_date <= P_EFFECTIVE_END_DATE and effective_end_date >= P_EFFECTIVE_START_DATE
                    and    PPM_INFORMATION1 in ('Y','0','7');

Line: 2089

     end validate_update_per_payment;