Thursday, October 22, 2015

Update People National Identifier using API


Bulk Update Employee national identifier using oracle hrms api

Declare
 Cursor c_emp_cur (cp_employee_number In Varchar2) Is
        Select papf.employee_number
              ,papf.effective_start_date
              ,papf.object_Version_number
              ,papf.person_id
        From per_All_people_f papf
        Where papf.business_group_id = <<Business Group Id>>
        And papf.person_type_id = <<Active Person Type Id>>
        And papf.employee_number = cp_employee_number
        And sysdate Between papf.effective_start_date And papf.effective_end_date;
  l_emp_rec c_emp_cur%rowtype;
  l_emp_no Varchar2(30);
  l_effective_start_date Date;
  l_effective_end_date Date;
  l_full_name per_all_people_f.employee_number%type;
  l_commnet_id Number;
  l_name_combination_warning Boolean;
  l_assign_payroll_warning Boolean;
  l_orig_hire_warning Boolean;
Begin
 -- initialize app session
 fnd_global.apps_initialize(<<AOL user id>>,<<HRMS Responsibility Id>>,HRMS Application Id (Default Id 800));
 For i In (Select  emp_no,
                           nat_no
              from xxhr_emp_nat )
  Loop
    Open c_emp_cur(cp_employee_number => i.emp_no);
    Fetch c_emp_cur Into l_emp_rec;
    Close c_emp_cur;
  l_emp_no := l_emp_rec.employee_number;
  hr_person_api.update_person
  (p_effective_date               => l_emp_rec.effective_start_date
  ,p_datetrack_update_mode        => 'CORRECTION'
  ,p_person_id                    => l_emp_rec.person_id
  ,p_object_version_number        => l_emp_rec.object_Version_number
  ,p_employee_number              => l_emp_no
  ,p_national_identifier          => i.nat_no
  ,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_commnet_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
  );
  End Loop;
  commit;
End;


1 comment:

Oracle Prodigy said...

Hi Sateesh, is there a way not to use a loop but a bulk operation instead?

i'm doing something similar like the one you're doing.

Thanks!