API to Update User

DECLARE

   l_session_id   NUMBER := USERENV ('sessionid');

   CURSOR c1

   IS

      SELECT per.person_id, per.employee_number

        FROM per_people_x per, per_assignments_x paaf

       WHERE     per.person_id = paaf.person_id

             AND per.person_type_id IN (1126, 1127)

             AND paaf.assignment_type = 'E'

             AND paaf.assignment_status_type_id IN (1, 2)

             AND paaf.primary_flag = 'Y';

BEGIN

   FOR i IN c1

   LOOP

      BEGIN

         fnd_user_pkg.updateuser (x_user_name        => i.employee_number,

                                  x_owner            => NULL,

                                  x_session_number   => l_session_id,

                                  x_employee_id      => i.person_id);

         COMMIT;

         DBMS_OUTPUT.put_line (i.employee_number || ' has been updated !!!');

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.

             put_line (

                  'Inner Exception: '

               || i.employee_number

               || ' -'

               || SUBSTR (SQLERRM, 1, 1000));

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Failed' || SUBSTR (SQLERRM, 1, 1000));

END;