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

creating a user in Oracle E-Business Suite (EBS) R12 and assigning system responsibilities.

 Creating a user in Oracle E-Business Suite (EBS) R12 and assigning system responsibilities.

Step 1: Log in to Oracle EBS

1. Open a web browser and go to the Oracle EBS login page.
2. Enter your Username and Password.
3. Click Login.

---

Step 2: Navigate to User Management

1. Go to System Administrator responsibility.
2. Navigate to:
   Security → User → Define

---

Step 3: Create a New User

1. In the Users form, enter the following details:

   • User Name: (e.g., JOHNDOE)
   • Password: (Enter a temporary password; the user will change it on first login)
   • Description: (Optional, e.g., "Finance Team User")
   • Person: (If linked to an employee, click the LOV to select)
   • Customer/Supplier: (If applicable, choose the respective entity)
   • Email: (For notifications)
   • Effective Dates:
     • From: (Set to today’s date)
     • To: (Leave blank if the account is permanent)

2. Save the record (Ctrl + S or click the disk icon).

---

Step 4: Assign Responsibilities

1. In the same Users form, navigate to the Responsibilities block.
2. Click in the Responsibility Name field and enter the responsibility (use F11 to search if needed).
   • Examples:
     • System Administrator
     • Application Developer
     • General Ledger Super User
3. Save the record.

---

Step 5: Verify the User Account

1. Run the following SQL query in the database to verify the user:

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

2. Check 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 6: Inform the User

• Share the Username and Temporary Password with the user.
• Instruct them to log in and change the password at first login.

---

Step 7: Optional - Set Profile Options (If Needed)

To set profile options for the user:

1. Navigate to:
   System Administrator → Profile → System
2. Query the user and assign any required profile options (e.g., MO: Operating Unit).

---

Step 8: Validate Login and Responsibility Access

• The user should log in and verify they can access the assigned responsibilities.
• If any access issues arise, recheck responsibility assignments.

---

Troubleshooting

• If a user cannot log in:
  • Check if their account is Active (end_date should be NULL).
  • Reset their password in User Management.
  • Check for security rules restricting access.