Oracle EBS R12 Update Employee Details

 DECLARE

  --In Variables for Update Employee API

  l_employee_number            per_all_people_f.employee_number%TYPE;-- := 'XX1635';

  l_object_version_number      per_all_people_f.object_version_number%TYPE;

  l_person_id                  per_all_people_f.person_id%TYPE;

  l_dt_ud_mode                 VARCHAR2 (200) := 'CORRECTION';

  l_effective_date             DATE := trunc ( SYSDATE);

  --l_marital_status             per_all_people_f.marital_status%TYPE := :p_marital_status;

  --l_suffix                     per_all_people_f.marital_status%TYPE := :p_suffix;

  -- Out Variables for Update Employee API

  -- -----------------------------------------------------------

  o_effective_start_date       DATE;

  o_effective_end_date         DATE;

  o_full_name                  per_all_people_f.full_name%TYPE;

  o_comment_id                 per_all_people_f.comment_id%TYPE;

  o_name_combination_warning   BOOLEAN;

  o_assign_payroll_warning     BOOLEAN;

  o_orig_hire_warning          BOOLEAN;

  

  cursor c1 is 

   SELECT PPX.PERSON_ID, EMP_NUMBER , OTHERS1 FROM  XXFUJ_UPLOAD_TICKET XXFUJ, PER_PEOPLE_X PPX

WHERE 

EMPLOYEE_NUMBER = EMP_NUMBER

--AND EMP_NUMBER IN ( 'xx1635')-- and person_id is not null;

;

BEGIN

  -- Get person_id and object_version_number for employee_number

  for i in c1 loop

  BEGIN

    --

    SELECT person_id, object_version_number, employee_number

    INTO   l_person_id, l_object_version_number,l_employee_number

    FROM   per_all_people_f

    WHERE  employee_number = i.emp_number

    AND    sysdate BETWEEN effective_start_date AND effective_end_date

    AND    business_group_id = 81;

  --

  EXCEPTION

    WHEN OTHERS THEN

      NULL;

  END;

  --

  

  --

  -- Issue a savepoint.

  --

  SAVEPOINT update_person;

  --

  hr_person_api.update_person ( -- Input Data Elements

                               -- ------------------------------

                               p_effective_date             => l_effective_date

                              ,p_datetrack_update_mode      => l_dt_ud_mode

                              ,p_person_id                  => l_person_id

                              ,p_per_information3                    => i.OTHERS1

                              --,p_marital_status             => l_marital_status

                              --,p_suffix                     => l_suffix

                              , -- Output Data Elements

                                -- ----------------------------------

                               p_employee_number            => l_employee_number

                              ,p_object_version_number      => l_object_version_number

                              ,p_effective_start_date       => o_effective_start_date

                              ,p_effective_end_date         => o_effective_end_date

                              ,p_full_name                  => o_full_name

                              ,p_comment_id                 => o_comment_id

                              ,p_name_combination_warning   => o_name_combination_warning

                              ,p_assign_payroll_warning     => o_assign_payroll_warning

                              ,p_orig_hire_warning          => o_orig_hire_warning);

  --

  COMMIT;

  dbms_output.put_line ('Employee # ' || l_employee_number || ' has been updated successfully!');

--

end loop;

EXCEPTION

  --

  WHEN OTHERS THEN

    ROLLBACK TO update_person;

    dbms_output.put_line (sqlerrm);

  --

END;

FND_USER_PKG.UPDATEUSER