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_group_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_group_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;

Comments (8)

Subscribe to this comment's feed

...
written by satish , April 18, 2007

Can we activate/deactivate the responsibilities for all the users depending on their last login date. If possible, then please advise what are the tables involved and how to go about that. Thanks.

...
written by BASANTA , August 13, 2007

Hi Anil,

Great job man !

You have been doing better then something special.

Anil, I have been facing a serious problem here with FND_USER table. I am just wondering, the table has been getting empty contineously. Couldn't get the root cause yet. First time when it was happened, we restored the table and the all went well. Initially we thought, it might have done by mistake. And again when it happened second time, then it started getting very serious. We have some scheduled jobs throuh an external application and also some with concurrent manager. We have been looking into the table FND_CONCURRENT_REQUESTS, and could find out after which job the table has been truncated but could't recognise where it does the same. Even I saw the dba table called V_$SQL, here also I couldn't find anything which can help me. Could you please put some light into my problem and get me any clue for the same. Waiting for your response.

Basant.

...
written by Anil Passi , August 19, 2007

Hi Basant

FND_USER records are never deleted by Oracle Apps Code.

Please search your custom code dictionary.
You could also trace the DB Sessions but the amount of trace files will be huge.

For the time being, to stop this happening, try doing below

1. Write a DB Trigger before delete on FND_USER
2. From this DB Trigger, raise an unhandled exception.
3. Search your codebase where truncate of this table might be happening.

Thanks,
Anil Passi

...
written by ramesh , August 28, 2007

hi anil,
this is ramesh,can i get any API inbound interface.for inserting data of employee and their address with some other columns.

thanks & regards
ramesh

APPS DBA/Unix Admin
written by Subhan , October 30, 2007

Hello Anil,

We took a clone of HRMS 11.5.10.2, on 9.2.0 Database for TESTing purposes, upgraded to DB 10.2.0, accessed apps successfully, plus applied some patches, and have been unale to access apps since. fixed a number of mistakes, and now the fnd_user table does not have any data in it. now the fnd_user is a synonym, and we'd like to know what is the best way for us to export data from the PRODUCTION to TEST?
Your Advice will be invaluable.

Regards,

Subhan

...
written by Anil Passi , October 30, 2007

Subhan

DB upgrade should never cleanse the FND_USER table.
You should raise an SR with Oracle

Thanks
Anil

How to End Date FND_User for Ex-Employee other than using update API.
written by Anil Verma , November 05, 2007

Hi,

Really nice way of solving probs and efforts putting towards is highly appreciated.

This has been dignosed after a long that there should be some auto program/seeded profile by which if some employee becomes Ex-employee then the corresponding FND_USER should be end dated with the termination date of the same.

Thanks,
Anil V

Excellent Work Anil
written by Siva G , January 24, 2008

Anil,

Really Hats off to your work and Articles that you are publishing on your Site. I just searched in Google to get some basic idea of Payroll and HRMS and when i referred your site....it was just mind blowing....Thanks for helping others....

Thanks,

Siva G