HRMS Miscellaneous

My client: Hey Anil, we need a script to do the following:-
A. Update fnd_user records for some 1000 users, to reset their passwords?
B: To assign all these 1000 users with a new responsibility, which happens to be a new HRMS Self Service responsibility.
C. We must not reset passwords of the employees which are active on that test environment.

Myself: Why do we need this done?
My Client: Well, we need to load test the system by simulating 1000 concurrent self service HR users...

That's the end of our talk. Now let me give you some background.
Self Service HRMS kicks off a workflow each time a user logs onto system to either view or update their personal information.
The reason for this approach is that SSHR uses workflow to manage the state of its transaction.

Question: Why does Oracle Self Service HR use workflow to manage its state?
Answer: Because SSHR was developed before OAF was invented by Oracle. Using OA Framework, it is possible to manage the state of a web based applications via something known as AM(Application Module).

Question : Are you saying that Self Service HR in Oracle does not use OA Framework
Answer: Not saying that. In fact Oracle has re-written most of its SSHR to use the OAF, but for some reasons(which I believe are legacy), the underlying workflow has been retained. I am not saying that this is bad design, but yet this begs to be load tested before goLive.

Question: Why is my client so concerned about load testing self service HR?
Answer: Workflows have their overheads, but beyond that SSHR uses some staging tables to capture before and after state of the personal information, while the user is updating the same. This alongwith workflow overhead may hit the system hard, hence the need for load test.

Question: OK, how do you go about reseting 1000 passwords.
Answer: Well, we not only have to reset passwords of fnd_user, but also need to assign each of those users a Self Service HR responsibility. This is how we do it:-
Scripts steps:-
Loop for latest employee records in fnd_user, excluding the recently logged in UAT users.
  ...inside the loop call fnd_user_pkg.updateuser
  ....again inside the loop call fnd_user_resp_groups_api.insert_assignment (to assign new SSHR Responsibility)
  ----within the loop, handle exception in case the user already has that responsibility

Question: How do you write that code?
Answer: Here you go....

After the script has completed, you can spool the data from temp table ....
NOTE: DO NOT RUN THIS ON PRODUCTION

DECLARE
  duplicate_responsibility EXCEPTION;
  PRAGMA EXCEPTION_INIT(duplicate_responsibility
                       ,-20001);
  i INTEGER := 0;
  no_action_required EXCEPTION;

  CURSOR c_get IS
    SELECT *
    FROM fnd_responsibility_vl
    WHERE responsibility_name = 'XX HR Employee Self Service';

  p_get c_get%ROWTYPE;
BEGIN
  OPEN c_get;
  FETCH c_get
    INTO p_get;
  CLOSE c_get;

  DELETE xxschema.fu_4_which_pwd_reset;
  FOR p_rec IN (SELECT user_name, user_id
                FROM fnd_user
                WHERE user_name NOT IN (SELECT fu.user_name
                                        FROM fnd_user fu, fnd_logins fl
                                        WHERE fl.start_time > SYSDATE - 40
                                        AND fu.user_id = fl.user_id
                                        GROUP BY fu.user_name, fu.user_id
                                        )
                AND user_name NOT LIKE '00%'
                AND end_date IS NULL
                AND employee_id > 0
                ORDER BY user_id DESC)
  LOOP
    i := i + 1;
    --First 1000 users only
    IF i = 1001
    THEN
      RAISE no_action_required;
    END IF;
    fnd_user_pkg.updateuser(x_user_name            => p_rec.user_name
                           ,x_owner                => 'SEED'
                           ,x_unencrypted_password => 'abcd0123'
                           ,x_password_date        => SYSDATE + 500);
 
    BEGIN
      fnd_user_resp_groups_api.insert_assignment(user_id                       => p_rec.user_id
                                                ,responsibility_id             => p_get.responsibility_id
                                                ,responsibility_application_id => p_get.application_id
                                                ,security_id             => 0
                                                ,start_date                    => SYSDATE - 1
                                                ,end_date                      => NULL
                                                ,description                   => 'Load testing SSHR on Test environment');
    EXCEPTION
      WHEN duplicate_responsibility THEN
        fnd_user_resp_groups_api.update_assignment(user_id                       => p_rec.user_id
                                                  ,responsibility_id             => p_get.responsibility_id
                                                  ,responsibility_application_id => p_get.application_id
                                                  ,security_id             => 0
                                                  ,start_date                    => SYSDATE - 1
                                                  ,end_date                      => NULL
                                                  ,description                   => 'Access Reinstated via Load testing SSHR on Test environment');
    END;
    INSERT INTO xxschema.fu_4_which_pwd_reset
      (user_id
      ,user_name)
    VALUES
      (p_rec.user_id
      ,p_rec.user_name);
  END LOOP;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    COMMIT;
END;

Anil Passi

Add comment