Create a user in Oracle E-Business Suite (EBS) R12 from the backend (database) and assign system responsibilities

 Create a user in Oracle E-Business Suite (EBS) R12 from the backend (database) and assign system responsibilities 

Step 1: Connect to the Database

Log in to the Oracle EBS database using SQL*Plus or any SQL client:

sqlplus apps/apps@EBSDB

---

Step 2: Create the User in FND_USER

Run the following SQL statement to create a new user:

INSERT INTO fnd_user 
    (user_id, user_name, description, password, start_date, end_date, created_by, last_updated_by, last_update_login)
VALUES 
    (fnd_user_id_s.NEXTVAL, 'JOHNDOE', 'Finance User', fnd_web_sec.validate_login('Welcome123'),
     SYSDATE, NULL, 0, 0, 0);

COMMIT;

Explanation:

• user_name = 'JOHNDOE' → Replace with the actual username.
• password → Encrypted using fnd_web_sec.validate_login('password').
• start_date = SYSDATE → User is active from today.
• end_date = NULL → User does not have an expiration date.

---

Step 3: Assign Responsibilities

To assign a responsibility, find the responsibility_id first:

SELECT responsibility_id, responsibility_name 
FROM fnd_responsibility 
WHERE responsibility_name LIKE '%System Administrator%';

Then, use the retrieved responsibility_id (e.g., 20420) to assign it to the user:

INSERT INTO fnd_user_resp_groups_direct 
    (user_id, responsibility_id, responsibility_application_id, security_group_id, start_date, end_date, created_by, last_updated_by, last_update_login)
VALUES 
    ((SELECT user_id FROM fnd_user WHERE user_name = 'JOHNDOE'), 
     20420, 1, 0, SYSDATE, NULL, 0, 0, 0);

COMMIT;

Explanation:

• responsibility_id = 20420 → Replace with the correct responsibility ID.
• responsibility_application_id = 1 → Usually Application Object Library (AOL).
• security_group_id = 0 → Default security group.
• start_date = SYSDATE → Responsibility is active immediately.

---

Step 4: Verify User and Responsibilities

Check if the user has been created:

SELECT user_id, user_name, start_date, end_date 
FROM fnd_user 
WHERE user_name = 'JOHNDOE';

Verify assigned responsibilities:

SELECT fu.user_name, fr.responsibility_name 
FROM fnd_user fu
JOIN fnd_user_resp_groups_direct frg ON fu.user_id = frg.user_id
JOIN fnd_responsibility fr ON frg.responsibility_id = fr.responsibility_id
WHERE fu.user_name = 'JOHNDOE';

---

Step 5: Force Password Reset (Optional)

If you want the user to change their password at the first login, update:

UPDATE fnd_user 
SET password_date = NULL 
WHERE user_name = 'JOHNDOE';

COMMIT;

---

Step 6: Inform the User

• Provide the username (JOHNDOE) and the initial password (Welcome123).
• The user should log in via EBS and change their password.

---

Troubleshooting

1. User Cannot Log In?

   • Ensure the user is not end-dated:

     SELECT user_name, end_date FROM fnd_user WHERE user_name = 'JOHNDOE';

   • Ensure the responsibility is active:

     SELECT * FROM fnd_user_resp_groups_direct 
     WHERE user_id = (SELECT user_id FROM fnd_user WHERE user_name = 'JOHNDOE') 
     AND end_date IS NULL;
     

2. Incorrect Password?

   • Reset the password manually:

     UPDATE fnd_user 
     SET password = fnd_web_sec.validate_login('NewPass123') 
     WHERE user_name = 'JOHNDOE';
     
     COMMIT;
     

---

Summary

✅ Created user in FND_USER
✅ Assigned responsibility in FND_USER_RESP_GROUPS_DIRECT
✅ Verified the user and permissions
✅ Informed the user