Developer ToolsFree generatorPDF, Excel, Word exportOracle E-Business SuiteAll Oracle E-Business Suite tools

Oracle EBS FND_USER_PKG Script Generator: API to Create User, Add Responsibility and Reset Password

This generator writes the FND_USER_PKG calls Oracle E-Business Suite administrators use to script user maintenance: create a user, add a responsibility, reset a password and end-date a responsibility, with SQL to check the result. It targets EBS 12.1 and 12.2.

Fill in

User name

Initial / new password

Must meet your SIGNON_PASSWORD_LENGTH, SIGNON_PASSWORD_HARD_TO_GUESS and related profile rules.

Email address

Employee PERSON_ID (or NULL)

PER_ALL_PEOPLE_F.PERSON_ID, unquoted. Leave NULL for non-employees.

Start date

Responsibility application short name

Responsibility key

FND_RESPONSIBILITY.RESPONSIBILITY_KEY, not the display name.

Security group key

Assignment description

End date for removal (Block 4)

Used only in the commented ADDRESP call in Block 4 for a future end date; DELRESP itself end-dates as of today.

Generated code

Download:

Block 1: create the user

BEGIN
  FND_USER_PKG.CREATEUSER(
    x_user_name            => UPPER('JSMITH'),
    x_owner                => NULL,
    x_unencrypted_password => 'Welcome#2026',
    x_start_date           => TO_DATE('2026-10-22', 'YYYY-MM-DD'),
    x_email_address        => 'jsmith@example.com',
    x_employee_id          => NULL);
  COMMIT;
END;
/

Block 2: add a responsibility

BEGIN
  FND_USER_PKG.ADDRESP(
    username       => UPPER('JSMITH'),
    resp_app       => 'INV',
    resp_key       => 'XX_INV_INQUIRY',
    security_group => 'STANDARD',
    description    => 'Added by script, ticket CHG-1042',
    start_date     => TO_DATE('2026-10-22', 'YYYY-MM-DD'),
    end_date       => NULL);
  COMMIT;
END;
/

Block 3: reset the password

SET SERVEROUTPUT ON
DECLARE
  l_ok BOOLEAN;
BEGIN
  l_ok := FND_USER_PKG.CHANGEPASSWORD(
            username    => UPPER('JSMITH'),
            newpassword => 'Welcome#2026');
  IF l_ok THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Password changed');
  ELSE
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Password change failed: ' || FND_MESSAGE.GET);
  END IF;
END;
/

Block 4: end-date a responsibility

-- DELRESP end-dates the assignment (it does not delete the row)
BEGIN
  FND_USER_PKG.DELRESP(
    username       => UPPER('JSMITH'),
    resp_app       => 'INV',
    resp_key       => 'XX_INV_INQUIRY',
    security_group => 'STANDARD');
  COMMIT;
END;
/

-- To end-date on a future date (2026-12-31) instead, call ADDRESP again with end_date set:
-- FND_USER_PKG.ADDRESP(username => UPPER('JSMITH'), resp_app => 'INV', resp_key => 'XX_INV_INQUIRY',
--   security_group => 'STANDARD', description => 'Added by script, ticket CHG-1042',
--   start_date => TO_DATE('2026-10-22', 'YYYY-MM-DD'), end_date => TO_DATE('2026-12-31', 'YYYY-MM-DD'));

Verification SQL

SELECT u.user_id, u.user_name, u.start_date, u.end_date, u.email_address,
       u.employee_id, u.password_date, u.last_logon_date
FROM   fnd_user u
WHERE  u.user_name = UPPER('JSMITH');

SELECT u.user_name, r.responsibility_key, r.responsibility_name,
       d.start_date, d.end_date
FROM   fnd_user_resp_groups_direct d
JOIN   fnd_user u
       ON u.user_id = d.user_id
JOIN   fnd_responsibility_vl r
       ON  r.responsibility_id = d.responsibility_id
       AND r.application_id    = d.responsibility_application_id
WHERE  u.user_name = UPPER('JSMITH')
ORDER  BY r.responsibility_name;

Run as APPS in a session that is not inside a running request. Confirm parameter names with SELECT object_name, argument_name, position FROM all_arguments WHERE owner = 'APPS' AND package_name = 'FND_USER_PKG' AND object_name IN ('CREATEUSER','ADDRESP','CHANGEPASSWORD','DELRESP'). Whether the user is forced to change the password at next login depends on PASSWORD_DATE; check it with the verification SQL rather than assuming.

Script your joiner-mover-leaver process

Send us your access request form; we map it to FND_USER_PKG calls with approvals and an audit trail for your instance.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Enter the user name, a password that satisfies your password profiles, email and, for employees, the HR PERSON_ID (unquoted, or NULL). For responsibilities, enter the application short name and RESPONSIBILITY_KEY, which differs from the display name; find it with SELECT responsibility_key, responsibility_name FROM fnd_responsibility_vl WHERE responsibility_name LIKE '...'. The security group is STANDARD unless you run security groups. Run only the blocks you need, then the verification SQL. Keep the script in your change ticket rather than editing users ad hoc.

CREATEUSER fails if the user name already exists
ADDRESP on an existing assignment updates its dates and description
Never paste production passwords into shared scripts; use a one-time password

Why the API and not table inserts

A user and their responsibilities are stored in FND_USER and FND_USER_RESP_GROUPS_DIRECT, but the API also maintains what the tables do not show: the encrypted password, and the Workflow directory roles and user role assignments that drive menus, notifications and approvals. Inserting rows directly leaves the Workflow directory out of step, so users see missing responsibilities or broken worklists, and Oracle Support will not help with it. FND_USER_PKG goes through the same code path as the Define User form, so audit columns, password rules and directory synchronisation are handled.

Worked example and checking the result

With the defaults, Block 1 creates JSMITH starting 22 October 2026 with an email and no employee link. Block 2 adds the XX_INV_INQUIRY responsibility under INV in the STANDARD security group. The first verification query shows the FND_USER row with START_DATE, EMPLOYEE_ID and PASSWORD_DATE; the second lists the direct responsibility assignments with their dates. After Block 4, the same row shows END_DATE set to today, and the user loses the responsibility at their next sign-on or responsibility switch. Indirect responsibilities granted through roles in User Management are not in FND_USER_RESP_GROUPS_DIRECT and are not touched by DELRESP.

Gotchas and audit

Run as APPS. User names are stored in upper case, so the blocks apply UPPER. Password rules come from profiles such as Signon Password Length and Signon Password Hard to Guess; CHANGEPASSWORD returns FALSE when a rule fails. EBS stores passwords encrypted and there is no supported way to read one back, so reset rather than look up. EBS does not support deleting users: end-date them (FND_USER_PKG.DISABLEUSER) to keep history. For audit, enable Sign-On: Audit Level and keep the ticket number in the assignment description.

Frequently Asked Questions

What is the API to create a user in Oracle EBS R12?

FND_USER_PKG.CREATEUSER. Pass x_user_name, x_owner (NULL or 'CUST'), x_unencrypted_password, x_start_date, x_email_address and optionally x_employee_id, then COMMIT. It creates the FND_USER row with an encrypted password and synchronises the Workflow directory, which a direct insert would not.

What is the API to add a responsibility to a user?

FND_USER_PKG.ADDRESP with username, resp_app (application short name), resp_key (RESPONSIBILITY_KEY), security_group (usually STANDARD), description, start_date and end_date. Calling it again for an existing assignment updates the dates, which is also how you set a future end date.

How do I reset an Oracle Apps user password from the backend?

Call FND_USER_PKG.CHANGEPASSWORD(username, newpassword), check the BOOLEAN result and COMMIT if TRUE. If it returns FALSE, read FND_MESSAGE.GET for the rule that failed. There is no supported query that returns the current password; reset it instead.

How do I end-date a responsibility with the API?

FND_USER_PKG.DELRESP(username, resp_app, resp_key, security_group) end-dates the direct assignment as of today; it does not delete it, so history and audit stay intact. For a future date, call ADDRESP with the end_date you want.

If you want access requests checked against your EBS roles and drafted as scripts for an administrator to approve, running on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools