DBA Data[Home] [Help]

APPS.PER_PEOPLE12_PKG SQL Statements

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

Line: 11

    SELECT 'Y'
     INTO   l_action_chk
     FROM   sys.dual
    WHERE  exists
         (SELECT null
          FROM   pay_payroll_actions pac,
                 pay_assignment_actions act
          WHERE  act.assignment_id =
           (select assignment_id
              from per_all_assignments_f
             where person_id = p_person_id
               and p_effective_date between
                   effective_start_date and effective_end_date
               and primary_flag = 'Y'
               and assignment_type = 'E')
            AND  pac.payroll_action_id = act.payroll_action_id
	    AND  pac.action_type not in ('X','BEE') -- Bug 2898318. Exclude BEE
						    -- actions
            AND  pac.effective_date >=  p_effective_date);
Line: 37

procedure update_row1(p_rowid VARCHAR2
   ,p_person_id NUMBER
   ,p_effective_start_date DATE
   ,p_effective_end_date DATE
   ,p_business_group_id NUMBER
   ,p_person_type_id NUMBER
   ,p_last_name VARCHAR2
   ,p_start_date DATE
   ,p_applicant_number IN OUT NOCOPY VARCHAR2
   ,p_comment_id NUMBER
   ,p_current_applicant_flag in  VARCHAR2
   ,p_current_emp_or_apl_flag VARCHAR2
   ,p_current_employee_flag VARCHAR2
   ,p_date_employee_data_verified DATE
   ,p_date_of_birth DATE
   ,p_email_address VARCHAR2
   ,p_employee_number IN OUT NOCOPY VARCHAR2
   ,p_expense_check_send_to_addr VARCHAR2
   ,p_first_name VARCHAR2
   ,p_full_name VARCHAR2
   ,p_known_as VARCHAR2
   ,p_marital_status VARCHAR2
   ,p_middle_names VARCHAR2
   ,p_nationality VARCHAR2
   ,p_national_identifier VARCHAR2
   ,p_previous_last_name VARCHAR2
   ,p_registered_disabled_flag VARCHAR2
   ,p_sex VARCHAR2
   ,p_title VARCHAR2
   ,p_suffix VARCHAR2
   ,p_vendor_id NUMBER
   ,p_work_telephone VARCHAR2
   ,p_request_id NUMBER
   ,p_program_application_id NUMBER
   ,p_program_id NUMBER
   ,p_program_update_date DATE
   ,p_a_cat VARCHAR2
   ,p_a1 VARCHAR2
   ,p_a2 VARCHAR2
   ,p_a3 VARCHAR2
   ,p_a4 VARCHAR2
   ,p_a5 VARCHAR2
   ,p_a6 VARCHAR2
   ,p_a7 VARCHAR2
   ,p_a8 VARCHAR2
   ,p_a9 VARCHAR2
   ,p_a10 VARCHAR2
   ,p_a11 VARCHAR2
   ,p_a12 VARCHAR2
   ,p_a13 VARCHAR2
   ,p_a14 VARCHAR2
   ,p_a15 VARCHAR2
   ,p_a16 VARCHAR2
   ,p_a17 VARCHAR2
   ,p_a18 VARCHAR2
   ,p_a19 VARCHAR2
   ,p_a20 VARCHAR2
   ,p_a21 VARCHAR2
   ,p_a22 VARCHAR2
   ,p_a23 VARCHAR2
   ,p_a24 VARCHAR2
   ,p_a25 VARCHAR2
   ,p_a26 VARCHAR2
   ,p_a27 VARCHAR2
   ,p_a28 VARCHAR2
   ,p_a29 VARCHAR2
   ,p_a30 VARCHAR2
   ,p_last_update_date DATE
   ,p_last_updated_by NUMBER
   ,p_last_update_login NUMBER
   ,p_created_by NUMBER
   ,p_creation_date DATE
   ,p_i_cat VARCHAR2
   ,p_i1 VARCHAR2
   ,p_i2 VARCHAR2
   ,p_i3 VARCHAR2
   ,p_i4 VARCHAR2
   ,p_i5 VARCHAR2
   ,p_i6 VARCHAR2
   ,p_i7 VARCHAR2
   ,p_i8 VARCHAR2
   ,p_i9 VARCHAR2
   ,p_i10 VARCHAR2
   ,p_i11 VARCHAR2
   ,p_i12 VARCHAR2
   ,p_i13 VARCHAR2
   ,p_i14 VARCHAR2
   ,p_i15 VARCHAR2
   ,p_i16 VARCHAR2
   ,p_i17 VARCHAR2
   ,p_i18 VARCHAR2
   ,p_i19 VARCHAR2
   ,p_i20 VARCHAR2
   ,p_i21 VARCHAR2
   ,p_i22 VARCHAR2
   ,p_i23 VARCHAR2
   ,p_i24 VARCHAR2
   ,p_i25 VARCHAR2
   ,p_i26 VARCHAR2
   ,p_i27 VARCHAR2
   ,p_i28 VARCHAR2
   ,p_i29 VARCHAR2
   ,p_i30 VARCHAR2
   ,p_app_ass_status_type_id NUMBER
   ,p_emp_ass_status_type_id NUMBER
	,p_system_person_type in VARCHAR2
   ,p_s_system_person_type VARCHAR2
   ,p_hire_date DATE
   ,p_s_hire_date DATE
   ,p_s_date_of_birth DATE
   ,p_status in out nocopy VARCHAR2
   ,p_new_primary_id in out nocopy NUMBER
   ,p_update_primary in out nocopy VARCHAR2
   ,p_legislation_code VARCHAR2
   ,p_vacancy_id IN OUT NOCOPY NUMBER
   ,p_session_date date
   ,p_end_of_time date
   ,p_work_schedule VARCHAR2
   ,p_correspondence_language VARCHAR2
   ,p_student_status VARCHAR2
   ,p_fte_capacity NUMBER
   ,p_on_military_service VARCHAR2
   ,p_second_passport_exists VARCHAR2
   ,p_background_check_status VARCHAR2
   ,p_background_date_check DATE
   ,p_blood_type VARCHAR2
   ,p_last_medical_test_date DATE
   ,p_last_medical_test_by VARCHAR2
   ,p_rehire_recommendation VARCHAR2
   ,p_rehire_reason VARCHAR2
   ,p_resume_exists VARCHAR2
   ,p_resume_last_updated DATE
   ,p_office_number VARCHAR2
   ,p_internal_location VARCHAR2
   ,p_mailstop VARCHAR2
   ,p_honors VARCHAR2
   ,p_pre_name_adjunct VARCHAR2
   ,p_hold_applicant_date_until DATE
   ,p_benefit_group_id NUMBER
   ,p_receipt_of_death_cert_date DATE
   ,p_coord_ben_med_pln_no VARCHAR2
   ,p_coord_ben_no_cvg_flag VARCHAR2
   ,p_uses_tobacco_flag VARCHAR2
   ,p_dpdnt_adoption_date DATE
   ,p_dpdnt_vlntry_svce_flag VARCHAR2
   ,p_date_of_death DATE
   ,p_original_date_of_hire DATE
   ,p_adjusted_svc_date DATE
   ,p_s_adjusted_svc_date DATE
   ,p_town_of_birth VARCHAR2
   ,p_region_of_birth VARCHAR2
   ,p_country_of_birth VARCHAR2
   ,p_global_person_id VARCHAR2
   ,p_npw_number IN OUT NOCOPY VARCHAR2
   ,p_current_npw_flag VARCHAR2

) is
  l_sys_per_type varchar2(150);
Line: 201

  update_row1(p_rowid
   ,p_person_id
   ,p_effective_start_date
   ,p_effective_end_date
   ,p_business_group_id
   ,p_person_type_id
   ,p_last_name
   ,p_start_date
   ,p_applicant_number
   ,p_comment_id
   -- # 2264569:
   ,l_current_apl
   --
   ,p_current_emp_or_apl_flag
   ,p_current_employee_flag
   ,p_date_employee_data_verified
   ,p_date_of_birth
   ,p_email_address
   ,p_employee_number
   ,p_expense_check_send_to_addr
   ,p_first_name
   ,p_full_name
   ,p_known_as
   ,p_marital_status
   ,p_middle_names
   ,p_nationality
   ,p_national_identifier
   ,p_previous_last_name
   ,p_registered_disabled_flag
   ,p_sex
   ,p_title
   ,p_suffix
   ,p_vendor_id
   ,p_work_telephone
   ,p_request_id
   ,p_program_application_id
   ,p_program_id
   ,p_program_update_date
   ,p_a_cat
   ,p_a1
   ,p_a2
   ,p_a3
   ,p_a4
   ,p_a5
   ,p_a6
   ,p_a7
   ,p_a8
   ,p_a9
   ,p_a10
   ,p_a11
   ,p_a12
   ,p_a13
   ,p_a14
   ,p_a15
   ,p_a16
   ,p_a17
   ,p_a18
   ,p_a19
   ,p_a20
   ,p_a21
   ,p_a22
   ,p_a23
   ,p_a24
   ,p_a25
   ,p_a26
   ,p_a27
   ,p_a28
   ,p_a29
   ,p_a30
   ,p_last_update_date
   ,p_last_updated_by
   ,p_last_update_login
   ,p_created_by
   ,p_creation_date
   ,p_i_cat
   ,p_i1
   ,p_i2
   ,p_i3
   ,p_i4
   ,p_i5
   ,p_i6
   ,p_i7
   ,p_i8
   ,p_i9
   ,p_i10
   ,p_i11
   ,p_i12
   ,p_i13
   ,p_i14
   ,p_i15
   ,p_i16
   ,p_i17
   ,p_i18
   ,p_i19
   ,p_i20
   ,p_i21
   ,p_i22
   ,p_i23
   ,p_i24
   ,p_i25
   ,p_i26
   ,p_i27
   ,p_i28
   ,p_i29
   ,p_i30
   ,p_app_ass_status_type_id
   ,p_emp_ass_status_type_id
    -- # 2264569
	,l_sys_per_type
   ,p_s_system_person_type
   ,p_hire_date
   ,p_s_hire_date
   ,p_s_date_of_birth
   ,p_status
   ,p_new_primary_id
   ,p_update_primary
   ,p_legislation_code
   ,p_vacancy_id
   ,p_session_date
   ,p_end_of_time
   ,p_work_schedule
   ,p_correspondence_language
   ,p_student_status
   ,p_fte_capacity
   ,p_on_military_service
   ,p_second_passport_exists
   ,p_background_check_status
   ,p_background_date_check
   ,p_blood_type
   ,p_last_medical_test_date
   ,p_last_medical_test_by
   ,p_rehire_recommendation
   ,p_rehire_reason
   ,p_resume_exists
   ,p_resume_last_updated
   ,p_office_number
   ,p_internal_location
   ,p_mailstop
   ,p_honors
   ,p_pre_name_adjunct
   ,p_hold_applicant_date_until
   ,p_benefit_group_id
   ,p_receipt_of_death_cert_date
   ,p_coord_ben_med_pln_no
   ,p_coord_ben_no_cvg_flag
   ,p_uses_tobacco_flag
   ,p_dpdnt_adoption_date
   ,p_dpdnt_vlntry_svce_flag
   ,p_date_of_death
   ,p_original_date_of_hire
   ,p_adjusted_svc_date
   ,p_s_adjusted_svc_date
   ,p_town_of_birth
   ,p_region_of_birth
   ,p_country_of_birth
   ,p_global_person_id
   ,p_npw_number
   ,p_current_npw_flag
   -- #2264569
   ,l_table
   ,null
   ,null
   ,null);
Line: 364

end update_row1;
Line: 367

procedure update_row1(p_rowid VARCHAR2
   ,p_person_id NUMBER
   ,p_effective_start_date DATE
   ,p_effective_end_date DATE
   ,p_business_group_id NUMBER
   ,p_person_type_id NUMBER
   ,p_last_name VARCHAR2
   ,p_start_date DATE
   ,p_applicant_number IN OUT NOCOPY VARCHAR2
   ,p_comment_id NUMBER
   -- *** Start commented code for bug 2264569 *****
   --,p_current_applicant_flag VARCHAR2
   -- *** End commented code for bug 2264569 *******
   --
   -- *** Start new code for bug 2264569 ***********
   -- should be in out parameter
   ,p_current_applicant_flag IN OUT NOCOPY VARCHAR2
   -- *** End new code for bug 2264569 *************
   ,p_current_emp_or_apl_flag VARCHAR2
   ,p_current_employee_flag VARCHAR2
   ,p_date_employee_data_verified DATE
   ,p_date_of_birth DATE
   ,p_email_address VARCHAR2
   ,p_employee_number IN OUT NOCOPY VARCHAR2
   ,p_expense_check_send_to_addr VARCHAR2
   ,p_first_name VARCHAR2
   ,p_full_name VARCHAR2
   ,p_known_as VARCHAR2
   ,p_marital_status VARCHAR2
   ,p_middle_names VARCHAR2
   ,p_nationality VARCHAR2
   ,p_national_identifier VARCHAR2
   ,p_previous_last_name VARCHAR2
   ,p_registered_disabled_flag VARCHAR2
   ,p_sex VARCHAR2
   ,p_title VARCHAR2
   ,p_suffix VARCHAR2
   ,p_vendor_id NUMBER
   ,p_work_telephone VARCHAR2
   ,p_request_id NUMBER
   ,p_program_application_id NUMBER
   ,p_program_id NUMBER
   ,p_program_update_date DATE
   ,p_a_cat VARCHAR2
   ,p_a1 VARCHAR2
   ,p_a2 VARCHAR2
   ,p_a3 VARCHAR2
   ,p_a4 VARCHAR2
   ,p_a5 VARCHAR2
   ,p_a6 VARCHAR2
   ,p_a7 VARCHAR2
   ,p_a8 VARCHAR2
   ,p_a9 VARCHAR2
   ,p_a10 VARCHAR2
   ,p_a11 VARCHAR2
   ,p_a12 VARCHAR2
   ,p_a13 VARCHAR2
   ,p_a14 VARCHAR2
   ,p_a15 VARCHAR2
   ,p_a16 VARCHAR2
   ,p_a17 VARCHAR2
   ,p_a18 VARCHAR2
   ,p_a19 VARCHAR2
   ,p_a20 VARCHAR2
   ,p_a21 VARCHAR2
   ,p_a22 VARCHAR2
   ,p_a23 VARCHAR2
   ,p_a24 VARCHAR2
   ,p_a25 VARCHAR2
   ,p_a26 VARCHAR2
   ,p_a27 VARCHAR2
   ,p_a28 VARCHAR2
   ,p_a29 VARCHAR2
   ,p_a30 VARCHAR2
   ,p_last_update_date DATE
   ,p_last_updated_by NUMBER
   ,p_last_update_login NUMBER
   ,p_created_by NUMBER
   ,p_creation_date DATE
   ,p_i_cat VARCHAR2
   ,p_i1 VARCHAR2
   ,p_i2 VARCHAR2
   ,p_i3 VARCHAR2
   ,p_i4 VARCHAR2
   ,p_i5 VARCHAR2
   ,p_i6 VARCHAR2
   ,p_i7 VARCHAR2
   ,p_i8 VARCHAR2
   ,p_i9 VARCHAR2
   ,p_i10 VARCHAR2
   ,p_i11 VARCHAR2
   ,p_i12 VARCHAR2
   ,p_i13 VARCHAR2
   ,p_i14 VARCHAR2
   ,p_i15 VARCHAR2
   ,p_i16 VARCHAR2
   ,p_i17 VARCHAR2
   ,p_i18 VARCHAR2
   ,p_i19 VARCHAR2
   ,p_i20 VARCHAR2
   ,p_i21 VARCHAR2
   ,p_i22 VARCHAR2
   ,p_i23 VARCHAR2
   ,p_i24 VARCHAR2
   ,p_i25 VARCHAR2
   ,p_i26 VARCHAR2
   ,p_i27 VARCHAR2
   ,p_i28 VARCHAR2
   ,p_i29 VARCHAR2
   ,p_i30 VARCHAR2
   ,p_app_ass_status_type_id NUMBER
   ,p_emp_ass_status_type_id NUMBER
   -- *** Start commented code for bug 2264569 ***
   --,p_system_person_type VARCHAR2
   -- *** End commented code for bug 2264569******
   --
   -- Start new code for bug 2264569 *************
   -- should be in out parameter
   ,p_system_person_type IN OUT NOCOPY VARCHAR2
   -- End new code for bug 2264569 **************
   --
   ,p_s_system_person_type      VARCHAR2
   ,p_hire_date                 DATE
   ,p_s_hire_date               DATE
   ,p_s_date_of_birth           DATE
   ,p_status in out nocopy             VARCHAR2
   ,p_new_primary_id in out nocopy     NUMBER
   ,p_update_primary in out nocopy     VARCHAR2
   ,p_legislation_code          VARCHAR2
   ,p_vacancy_id IN OUT NOCOPY         NUMBER
   ,p_session_date date
   ,p_end_of_time date
   ,p_work_schedule VARCHAR2
   ,p_correspondence_language VARCHAR2
   ,p_student_status VARCHAR2
   ,p_fte_capacity NUMBER
   ,p_on_military_service VARCHAR2
   ,p_second_passport_exists VARCHAR2
   ,p_background_check_status VARCHAR2
   ,p_background_date_check DATE
   ,p_blood_type VARCHAR2
   ,p_last_medical_test_date DATE
   ,p_last_medical_test_by VARCHAR2
   ,p_rehire_recommendation VARCHAR2
   ,p_rehire_reason VARCHAR2
   ,p_resume_exists VARCHAR2
   ,p_resume_last_updated DATE
   ,p_office_number VARCHAR2
   ,p_internal_location VARCHAR2
   ,p_mailstop VARCHAR2
   ,p_honors VARCHAR2
   ,p_pre_name_adjunct VARCHAR2
   ,p_hold_applicant_date_until DATE
   ,p_benefit_group_id NUMBER
   ,p_receipt_of_death_cert_date DATE
   ,p_coord_ben_med_pln_no VARCHAR2
   ,p_coord_ben_no_cvg_flag VARCHAR2
   ,p_uses_tobacco_flag VARCHAR2
   ,p_dpdnt_adoption_date DATE
   ,p_dpdnt_vlntry_svce_flag VARCHAR2
   ,p_date_of_death DATE
   ,p_original_date_of_hire DATE
   ,p_adjusted_svc_date DATE
   ,p_s_adjusted_svc_date DATE
   ,p_town_of_birth VARCHAR2
   ,p_region_of_birth VARCHAR2
   ,p_country_of_birth VARCHAR2
   ,p_global_person_id VARCHAR2
   ,p_npw_number IN OUT NOCOPY VARCHAR2
   ,p_current_npw_flag VARCHAR2
   -- Start new code for bug 2264569 ****************************
   -- added pl/sql table
   ,p_tab IN OUT NOCOPY HR_EMPLOYEE_APPLICANT_API.t_ApplTable
   -- End new code for bug 2264569 ******************************
   ,p_order_name     IN VARCHAR2
   ,p_global_name    IN VARCHAR2
   ,p_local_name     IN VARCHAR2
) is
--
   l_period_of_service_id number; -- Period of Service id.
Line: 562

select PAY_PROPOSAL_ID
from per_pay_proposals
where change_date = p_s_hire_date
and   assignment_id = (select assignment_id
from per_assignments_f
where person_id = p_person_id
and   primary_flag = 'Y'
and   effective_start_date = p_hire_date
and   assignment_type = 'E'
);
Line: 576

    select party_id
    from   per_all_people_f
    where  person_id = p_person_id;
Line: 583

    select *
    from   per_all_people_f
    where  person_id = p_person_id
    and    p_effective_start_date
           between effective_start_date
           and     effective_end_date;
Line: 596

    select current_applicant_flag
      from  per_startup_person_types
     where system_person_type = cp_person_type;
Line: 603

    select email_address
    from   per_all_people_f
   where   rowid = p_rowid;
Line: 625

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',5);
Line: 646

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',10);
Line: 665

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',15);
Line: 673

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',20);
Line: 676

            select pps.period_of_service_id
            into   l_period_of_service_id
            from   per_periods_of_service pps
            where  pps.person_id = p_person_id
            and    pps.date_start = p_s_hire_date;
Line: 682

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',25);
Line: 691

                  hr_utility.set_message_token('PROCEDURE','Update_row');
Line: 699

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',30);
Line: 709

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',35);
Line: 721

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',40);
Line: 722

        per_people9_pkg.update_old_person_row(p_person_id =>p_person_id
                              ,p_session_date => p_session_date
                              ,p_effective_start_date=>p_effective_start_date);
Line: 766

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',55);
Line: 769

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',60);
Line: 772

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',65);
Line: 775

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',70);
Line: 790

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',75);
Line: 808

         hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',80);
Line: 871

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',95);
Line: 888

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',100);
Line: 895

            hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',101);
Line: 898

            hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',102);
Line: 904

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',105);
Line: 908

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',110);
Line: 927

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',115);
Line: 933

      per_people9_pkg.insert_employee_rows(p_person_id => p_person_id
         ,p_effective_start_date => p_effective_start_date
         ,p_effective_end_date => p_effective_end_date
         ,p_business_group_id =>p_business_group_id
         ,p_emp_ass_status_type_id => p_emp_ass_status_type_id
         ,p_employee_number => p_employee_number
         ,p_request_id => p_request_id
         ,p_program_application_id => p_program_application_id
         ,p_program_id => p_program_id
         ,p_program_update_date => p_program_update_date
         ,p_last_update_date => p_last_update_date
         ,p_last_updated_by => p_last_updated_by
         ,p_last_update_login => p_last_update_login
         ,p_created_by => p_created_by
         ,p_creation_date => p_creation_date
         ,p_adjusted_svc_date => p_adjusted_svc_date);
Line: 950

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',120);
Line: 960

 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',1201);
Line: 965

         ,p_datetrack_update_mode => 'CORRECTION'
         );
Line: 973

 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',1202);
Line: 978

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',121);
Line: 989

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',125);
Line: 1018

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',130);
Line: 1030

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',135);
Line: 1039

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',140);
Line: 1048

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',145);
Line: 1051

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',150);
Line: 1054

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',155);
Line: 1056

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',160);
Line: 1064

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',165);
Line: 1067

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',170);
Line: 1075

       hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',175);
Line: 1078

        hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',180);
Line: 1082

        hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',185);
Line: 1179

   hr_utility.set_location('update_row - b4 CHOOSE_VAC',1);
Line: 1181

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',240);
Line: 1189

   hr_utility.set_location('update_row - b4 HIRE',1);
Line: 1194

         hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',245);
Line: 1197

         if p_update_primary = 'Y'
            and future_pactid_exists(p_person_id, p_effective_start_date)
         then
            hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',246);
Line: 1211

            hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',250);
Line: 1214

            hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',255);
Line: 1220

         hr_utility.set_location('update_row - b4 hrhirapl',1);
Line: 1223

            hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',257);
Line: 1229

         hr_utility.set_location('update_row - b4 hrhirapl',2);
Line: 1233

         hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',259);
Line: 1239

                                  ,p_last_updated_by
                                  ,p_last_update_login
                                  ,p_effective_start_date
                                  ,p_end_of_time
                                  ,p_last_update_date
                                  ,p_update_primary
                                  ,p_employee_number
                                  ,l_set_of_books_id
                                  ,l_employ_emp_apl
                                  ,p_adjusted_svc_date
                                  ,p_session_date -- Bug 3564129
                                  -- **** Start new code for bug 2264569 ******
                                  ,p_tab
                                  -- **** End new code for bug 2264569  *******
                                  );
Line: 1254

   hr_utility.set_location('update_row - after hrhirapl',2);
Line: 1267

            hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',260);
Line: 1289

 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',1211);
Line: 1294

         ,p_datetrack_update_mode => 'CORRECTION'
         );
Line: 1302

 hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',1212);
Line: 1306

       hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',260);
Line: 1309

           hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',262);
Line: 1328

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',265);
Line: 1330

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',270);
Line: 1408

   hr_utility.set_location('update_row - b4 update',1);
Line: 1409

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',272);
Line: 1410

    hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 '||p_person_type_id,272);
Line: 1411

    hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 '||p_s_system_person_type,272);
Line: 1412

    hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 '||p_system_person_type,272);
Line: 1413

    hr_utility.set_location('PER_PEOPLE12_PKG.update_row1 '||hr_person_type_usage_info.get_default_person_type_id(
	 p_business_group_id,p_system_person_type),272);
Line: 1418

   update per_all_people_f ppf
   -- Bug 6196362 Ends.
   set ppf.person_id = p_person_id
   ,ppf.effective_start_date = p_effective_start_date
   ,ppf.effective_end_date = p_effective_end_date
   ,ppf.business_group_id = p_business_group_id
--   ,ppf.person_type_id = p_person_type_id
   --,ppf.person_type_id =hr_person_type_usage_info.get_default_person_type_id(	 p_business_group_id	,p_system_person_type) bug 6848958
   ,ppf.person_type_id =decode( p_system_person_type,'CWK',
   	hr_person_type_usage_info.get_default_person_type_id(
	 p_business_group_id,'OTHER'),
     hr_person_type_usage_info.get_default_person_type_id(
	 p_business_group_id,p_system_person_type))  -- fix for bug6848958
   ,ppf.last_name = p_last_name
   ,ppf.start_date = p_start_date
   ,ppf.applicant_number = p_applicant_number
   ,ppf.comment_id = p_comment_id
   ,ppf.current_applicant_flag = p_current_applicant_flag
   ,ppf.current_emp_or_apl_flag = p_current_emp_or_apl_flag
   ,ppf.current_employee_flag = p_current_employee_flag
   ,ppf.date_employee_data_verified = p_date_employee_data_verified
   ,ppf.date_of_birth = p_date_of_birth
   ,ppf.email_address = p_email_address
   ,ppf.employee_number = p_employee_number
   ,ppf.expense_check_send_to_address = p_expense_check_send_to_addr
   ,ppf.first_name = p_first_name
   ,ppf.full_name = p_full_name
   ,ppf.known_as = p_known_as
   ,ppf.marital_status = p_marital_status
   ,ppf.middle_names = p_middle_names
   ,ppf.nationality = p_nationality
   ,ppf.national_identifier = p_national_identifier
   ,ppf.previous_last_name = p_previous_last_name
   ,ppf.registered_disabled_flag = p_registered_disabled_flag
   ,ppf.sex = p_sex
   ,ppf.title = p_title
   ,ppf.suffix = p_suffix
   ,ppf.vendor_id = p_vendor_id
--   ,ppf.work_telephone = p_work_telephone
   ,ppf.request_id = p_request_id
   ,ppf.program_application_id = p_program_application_id
   ,ppf.program_id = p_program_id
   ,ppf.program_update_date = p_program_update_date
   ,ppf.attribute_category = p_a_cat
   ,ppf.attribute1 = p_a1
   ,ppf.attribute2 = p_a2
   ,ppf.attribute3 = p_a3
   ,ppf.attribute4 = p_a4
   ,ppf.attribute5 = p_a5
   ,ppf.attribute6 = p_a6
   ,ppf.attribute7 = p_a7
   ,ppf.attribute8 = p_a8
   ,ppf.attribute9 = p_a9
   ,ppf.attribute10 = p_a10
   ,ppf.attribute11 = p_a11
   ,ppf.attribute12 = p_a12
   ,ppf.attribute13 = p_a13
   ,ppf.attribute14 = p_a14
   ,ppf.attribute15 = p_a15
   ,ppf.attribute16 = p_a16
   ,ppf.attribute17 = p_a17
   ,ppf.attribute18 = p_a18
   ,ppf.attribute19 = p_a19
   ,ppf.attribute20 = p_a20
   ,ppf.attribute21 = p_a21
   ,ppf.attribute22 = p_a22
   ,ppf.attribute23 = p_a23
   ,ppf.attribute24 = p_a24
   ,ppf.attribute25 = p_a25
   ,ppf.attribute26 = p_a26
   ,ppf.attribute27 = p_a27
   ,ppf.attribute28 = p_a28
   ,ppf.attribute29 = p_a29
   ,ppf.attribute30 = p_a30
   ,ppf.last_update_date = p_last_update_date
   ,ppf.last_updated_by = p_last_updated_by
   ,ppf.last_update_login = p_last_update_login
   ,ppf.created_by = p_created_by
   ,ppf.creation_date = p_creation_date
   ,ppf.per_information_category = p_i_cat
   ,ppf.per_information1 = p_i1
   ,ppf.per_information2 = p_i2
   ,ppf.per_information3 = p_i3
   ,ppf.per_information4 = p_i4
   ,ppf.per_information5 = p_i5
   ,ppf.per_information6 = p_i6
   ,ppf.per_information7 = p_i7
   ,ppf.per_information8 = p_i8
   ,ppf.per_information9 = p_i9
   ,ppf.per_information10 = p_i10
   ,ppf.per_information11 = p_i11
   ,ppf.per_information12 = p_i12
   ,ppf.per_information13 = p_i13
   ,ppf.per_information14 = p_i14
   ,ppf.per_information15 = p_i15
   ,ppf.per_information16 = p_i16
   ,ppf.per_information17 = p_i17
   ,ppf.per_information18 = p_i18
   ,ppf.per_information19 = p_i19
   ,ppf.per_information20 = p_i20
   ,ppf.per_information21 = p_i21
   ,ppf.per_information22 = p_i22
   ,ppf.per_information23 = p_i23
   ,ppf.per_information24 = p_i24
   ,ppf.per_information25 = p_i25
   ,ppf.per_information26 = p_i26
   ,ppf.per_information27 = p_i27
   ,ppf.per_information28 = p_i28
   ,ppf.per_information29 = p_i29
   ,ppf.per_information30 = p_i30
      ,ppf.work_schedule  = p_work_schedule
   ,ppf.correspondence_language  = p_correspondence_language
   ,ppf.student_status  = p_student_status
   ,ppf.fte_capacity  = p_fte_capacity
   ,ppf.on_military_service  = p_on_military_service
   ,ppf.second_passport_exists  = p_second_passport_exists
   ,ppf.background_check_status  = p_background_check_status
   ,ppf.background_date_check  = p_background_date_check
   ,ppf.blood_type  = p_blood_type
   ,ppf.last_medical_test_date  = p_last_medical_test_date
   ,ppf.last_medical_test_by  = p_last_medical_test_by
   ,ppf.rehire_recommendation  = p_rehire_recommendation
   ,ppf.rehire_reason  = p_rehire_reason
   ,ppf.resume_exists  = p_resume_exists
   ,ppf.resume_last_updated  = p_resume_last_updated
   ,ppf.office_number  = p_office_number
   ,ppf.internal_location  = p_internal_location
   ,ppf.mailstop  = p_mailstop
   ,ppf.honors  = p_honors
   ,ppf.pre_name_adjunct  = p_pre_name_adjunct
   ,ppf.hold_applicant_date_until = p_hold_applicant_date_until
   ,ppf.benefit_group_id = p_benefit_group_id
   ,ppf.receipt_of_death_cert_date = p_receipt_of_death_cert_date
   ,ppf.coord_ben_med_pln_no = p_coord_ben_med_pln_no
   ,ppf.coord_ben_no_cvg_flag = p_coord_ben_no_cvg_flag
   ,ppf.uses_tobacco_flag = p_uses_tobacco_flag
   ,ppf.dpdnt_adoption_date = p_dpdnt_adoption_date
   ,ppf.dpdnt_vlntry_svce_flag = p_dpdnt_vlntry_svce_flag
   ,ppf.date_of_death = p_date_of_death
   ,ppf.original_date_of_hire = p_original_date_of_hire
   ,ppf.town_of_birth    = p_town_of_birth
   ,ppf.region_of_birth  = p_region_of_birth
   ,ppf.country_of_birth = p_country_of_birth
   ,ppf.global_person_id = p_global_person_id
   ,ppf.party_id         = l_party_id
   ,ppf.npw_number       = p_npw_number
   ,ppf.current_npw_flag = p_current_npw_flag
   ,ppf.order_name       = p_order_name  -- #3889584
   ,ppf.global_name      = p_global_name
   ,ppf.local_name       = p_local_name
   where ppf.rowid = p_rowid;
Line: 1578

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',275);
Line: 1591

   per_hrtca_merge.update_tca_person(p_Rec => l_person);
Line: 1593

   hr_utility.set_location('update_row - after update',1);
Line: 1601

                             p_action   => 'UPDATE');
Line: 1608

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',280);
Line: 1617

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',282);
Line: 1622

                            ,p_start_date => p_last_update_date
                            );
Line: 1624

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',285);
Line: 1627

   hr_utility.set_location('update_row - after update',2);
Line: 1645

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',290);
Line: 1647

      per_people3_pkg.update_period(p_person_id =>p_person_id
                              ,p_hire_date => p_s_hire_date
                              ,p_new_hire_date =>p_hire_date
                              ,p_adjusted_svc_date => p_adjusted_svc_date);
Line: 1652

      hr_utility.set_location('update_row - after update',3);
Line: 1663

      hr_utility.set_location('update_row - after update',4);
Line: 1664

      hr_date_chk.update_hire_records(p_person_id
          ,p_applicant_number
          ,p_hire_date
          ,p_s_hire_date
          ,p_last_updated_by
          ,p_last_update_login);
Line: 1672

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',295);
Line: 1707

      hr_utility.set_location('update_row - after update',5);
Line: 1725

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',305);
Line: 1739

   hr_utility.set_location('PER_PEOPLE12_PKG.update_row1',310);
Line: 1763

                             p_action   => 'UPDATE');
Line: 1766

   p_status := 'END'; -- Status required to end update loop on server
Line: 1767

   hr_utility.set_location('Leaving PER_PEOPLE12_PKG.update_row1',315);
Line: 1769

end update_row1;
Line: 1779

select '1'
from sys.dual
where exists (
              select 'future assignment exists'
              from   per_people_f ppf
              where  ppf.person_id = p_person_id
              and    ppf.effective_start_date > p_effective_start_date
             );
Line: 1805

select 'Y'
from per_assignments_f paf
where paf.assignment_type = 'E'
and   paf.supervisor_id = p_person_id
and   p_new_hire_date > paf.effective_start_date
and   paf.effective_end_date  >= p_old_hire_date ;
Line: 1829

select 1
from  per_periods_of_service pps
where pps.person_id = p_person_id
and   pps.actual_termination_date is not null;
Line: 1842

select pps.last_standard_process_date,
       pps.final_process_date
--
-- 115.67 (END)
--
from  per_periods_of_service pps
where pps.person_id  = p_person_id
and   pps.date_start = (select max(date_start)
                        from   per_periods_of_service pps1
                        where  pps1.person_id = pps.person_id
                        and    pps1.date_start = p_start_date;
Line: 1869

  SELECT bus.legislation_code
  FROM per_people_f per
      ,per_business_groups bus
 WHERE per.person_id = p_person_id
   AND per.business_group_id+0 = bus.business_group_id
   AND p_start_date BETWEEN per.effective_start_date
                       AND per.effective_end_date;
Line: 1994

select asg.assignment_id
from per_assignments_f asg
where asg.person_id = p_person_id
and   asg.payroll_id is not null;
Line: 2035

    select 'Y'
    into l_warn
    from dual
    where exists
    (select null
     from pay_element_entries_f ee,
          pay_element_links_f el,
          pay_element_types_f et
     where ee.assignment_id in
       (select assignment_id
        from per_assignments_f asg
        where asg.person_id = p_person_id
        and asg.effective_start_date between l_earlier_date and l_later_date)
     and ee.element_link_id = el.element_link_id
     and el.element_type_id = et.element_type_id
     and et.processing_type = 'R');
Line: 2064

select *
from per_all_people_f
where person_id = c_person_id
order by effective_start_date asc;
Line: 2070

select 'Y'
from dual
where exists(
	select 1
	from per_all_people_f per
	where per.person_id = c_person_id
	and c_effective_date between per.effective_start_date and per.effective_end_date
	);
Line: 2080

select ppt.system_person_type
from per_person_types ppt,
     per_all_people_f ppf
where ppf.person_id = c_person_id
and ppf.person_type_id = ppt.person_type_id;
Line: 2087

select address_id
from per_addresses
where person_id = c_person_id
and date_from = c_date_from;
Line: 2093

select ppt.person_type_id
from per_person_types ppt
where ppt.business_group_id = c_business_group_id
and ppt.default_flag = 'Y'
and ppt.active_flag = 'Y'
and ppt.system_person_type = 'OTHER';
Line: 2131

      update per_addresses
      set date_from = p_cov_date_start
      where address_id = l_address_id.address_id;
Line: 2138

      update per_all_people_f
      set effective_start_date = p_cov_date_start,
      start_date = p_cov_date_start
      where person_id = p_person_id
      and effective_start_date = l_per_rec.effective_start_date;
Line: 2146

      update per_all_people_f
      set    start_date = p_cov_date_start
      where  person_id = p_person_id;
Line: 2152

      update per_person_type_usages_f ptu
      set ptu.effective_start_date = p_cov_date_start
      where ptu.person_id = p_person_id
      and ptu.person_type_id in (select ppt.person_type_id
                                 from per_person_types ppt
                                 where ppt.system_person_type = 'OTHER');
Line: 2174

      insert into per_all_people_f(
          person_id,
          effective_start_date,
          effective_end_date,
          business_group_id,
          person_type_id,
          last_name,
          start_date,
          comment_id,
          current_applicant_flag,
          current_emp_or_apl_flag,
          current_employee_flag,
          date_of_birth,
          first_name,
          full_name,
          middle_names,
          sex,
          title,
	  pre_name_adjunct,
	  suffix,
          national_identifier,
          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,
          per_information_category,
          per_information1,
          per_information2,
          per_information3,
          per_information4,
          per_information5,
          per_information6,
          per_information7,
          per_information8,
          per_information9,
          per_information10,
          per_information11,
          per_information12,
          per_information13,
          per_information14,
          per_information15,
          per_information16,
          per_information17,
          per_information18,
          per_information19,
          per_information20,
          per_information21,
          per_information22,
          per_information23,
          per_information24,
          per_information25,
          per_information26,
          per_information27,
          per_information28,
          per_information29,
          per_information30,
          known_as
         )
      values(
          l_per_rec.person_id,
          l_per_rec.effective_start_date,
          l_per_rec.effective_end_date,
          l_per_rec.business_group_id,
          l_per_rec.person_type_id,
          l_per_rec.last_name,
          l_per_rec.start_date,
          l_per_rec.comment_id,
          l_per_rec.current_applicant_flag,
          l_per_rec.current_emp_or_apl_flag,
          l_per_rec.current_employee_flag,
          l_per_rec.date_of_birth,
          l_per_rec.first_name,
          l_per_rec.full_name,
          l_per_rec.middle_names,
          l_per_rec.sex,
          l_per_rec.title,
	  l_per_rec.pre_name_adjunct,
	  l_per_rec.suffix,
          l_per_rec.national_identifier,
          l_per_rec.attribute_category,
          l_per_rec.attribute1,
          l_per_rec.attribute2,
          l_per_rec.attribute3,
          l_per_rec.attribute4,
          l_per_rec.attribute5,
          l_per_rec.attribute6,
          l_per_rec.attribute7,
          l_per_rec.attribute8,
          l_per_rec.attribute9,
          l_per_rec.attribute10,
          l_per_rec.attribute11,
          l_per_rec.attribute12,
          l_per_rec.attribute13,
          l_per_rec.attribute14,
          l_per_rec.attribute15,
          l_per_rec.attribute16,
          l_per_rec.attribute17,
          l_per_rec.attribute18,
          l_per_rec.attribute19,
          l_per_rec.attribute20,
          l_per_rec.attribute21,
          l_per_rec.attribute22,
          l_per_rec.attribute23,
          l_per_rec.attribute24,
          l_per_rec.attribute25,
          l_per_rec.attribute26,
          l_per_rec.attribute27,
          l_per_rec.attribute28,
          l_per_rec.attribute29,
          l_per_rec.attribute30,
          l_per_rec.per_information_category,
          l_per_rec.per_information1,
          l_per_rec.per_information2,
          l_per_rec.per_information3,
          l_per_rec.per_information4,
          l_per_rec.per_information5,
          l_per_rec.per_information6,
          l_per_rec.per_information7,
          l_per_rec.per_information8,
          l_per_rec.per_information9,
          l_per_rec.per_information10,
          l_per_rec.per_information11,
          l_per_rec.per_information12,
          l_per_rec.per_information13,
          l_per_rec.per_information14,
          l_per_rec.per_information15,
          l_per_rec.per_information16,
          l_per_rec.per_information17,
          l_per_rec.per_information18,
          l_per_rec.per_information19,
          l_per_rec.per_information20,
          l_per_rec.per_information21,
          l_per_rec.per_information22,
          l_per_rec.per_information23,
          l_per_rec.per_information24,
          l_per_rec.per_information25,
          l_per_rec.per_information26,
          l_per_rec.per_information27,
          l_per_rec.per_information28,
          l_per_rec.per_information29,
          l_per_rec.per_information30,
          l_per_rec.known_as
          );
Line: 2349

      update per_all_people_f
      set    start_date = l_per_rec.start_date
      where  person_id = l_per_rec.person_id;
Line: 2355

      select per_person_type_usages_s.nextval into l_ptu_nextval
      from sys.dual;
Line: 2359

      insert into per_person_type_usages_f(
          PERSON_TYPE_USAGE_ID,
          PERSON_ID,
          PERSON_TYPE_ID,
          EFFECTIVE_START_DATE,
          EFFECTIVE_END_DATE,
          OBJECT_VERSION_NUMBER
          )
      values
         (l_ptu_nextval,
          l_per_rec.person_id,
          l_per_rec.person_type_id,
          l_per_rec.effective_start_date,
          l_per_rec.effective_end_date,
          l_object_version_number
          );
Line: 2387

select contact_person_id, min(nvl(date_start,hr_api.g_sot)) date_start
from per_contact_relationships
where person_id = c_person_id
group by contact_person_id;
Line: 2393

select ctr.person_id, min(nvl(ctr.date_start,hr_api.g_sot)) date_start
from per_contact_relationships ctr
    ,per_person_type_usages_f ptu
    ,per_person_types ppt
where ctr.contact_person_id = c_contact_person_id
and ctr.person_id = ptu.person_id
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'EMP'
group by ctr.person_id;
Line: 2404

select max(date_start)
from per_periods_of_service
where person_id = c_person_id;