Introduction
Oracle user is the base of Oracle EBS ERP; Like creating a user from the front-end (Application), sometimes we need to create users from the backend (database) using API (Application programming interface).
This article will give an API code example to create user in oracle apps r12 from backend.
Goal
Create an FND user using API in Oracle APPS.
Standard API name: “FND_USER_PKG.CREATEUSER“.
Forms Or Pages
API Parameters Description
CreateUser (PUBLIC)
- Insert a new user record into the FND_USER table. If that user exists already, an exception is raised with the error message. There are three input arguments that must be provided. All the other columns in the FND_USER table can take the default value.
*** NOTE: This version accepts the old customer_id/employee_id keys and foreign keys to the “person”. Use CreateUserParty to create a user with the new person_party_id key.
Input (Mandatory)
- x_user_name: The name of the new user
- x_owner: ‘SEED’ or ‘CUST'(customer)
- x_unencrypted_password: The password for this new user
API Code Snippet
DECLARE
L_USER_NAME VARCHAR2 (100) := 'ORAASK';
L_USER_PASSWORD VARCHAR2 (100) := 'Oraask1234';
L_USER_START_DATE DATE := SYSDATE;
L_USER_END_DATE VARCHAR2 (100) := NULL;
L_PASSWORD_LIFESPAN_DAYS NUMBER := 90;
L_DESCRIPTION VARCHAR2 (150) := 'This is a test user created by Hassan for Oraask.Com';
L_EMAIL_ADDRESS VARCHAR2 (100) := 'email@oraask.com';
L_USER_CNT NUMBER;
L_MSG_DATA VARCHAR2 (3000);
BEGIN
DBMS_OUTPUT.PUT_LINE ('********************************');
DBMS_OUTPUT.PUT_LINE ('Start FND_USER_PKG.CREATEUSER API');
FND_USER_PKG.CREATEUSER (X_USER_NAME => L_USER_NAME
,X_OWNER => NULL
,X_UNENCRYPTED_PASSWORD => L_USER_PASSWORD
,X_START_DATE => L_USER_START_DATE
,X_END_DATE => L_USER_END_DATE
,X_DESCRIPTION => L_DESCRIPTION
,X_PASSWORD_LIFESPAN_DAYS => L_PASSWORD_LIFESPAN_DAYS
,X_EMAIL_ADDRESS => L_EMAIL_ADDRESS);
SELECT COUNT (USER_ID)
INTO L_USER_CNT
FROM FND_USER
WHERE USER_NAME = L_USER_NAME;
IF (L_USER_CNT > 0) THEN
DBMS_OUTPUT.PUT_LINE ('New User has been created Successfully!!');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('New User creation failed');
END IF;
DBMS_OUTPUT.PUT_LINE ('End FND_USER_PKG.CREATEUSER API');
DBMS_OUTPUT.PUT_LINE ('********************************');
EXCEPTION
WHEN OTHERS THEN
L_MSG_DATA := SUBSTR (SQLERRM, 0, 1000);
DBMS_OUTPUT.PUT_LINE ('***************************');
DBMS_OUTPUT.PUT_LINE ('There is an exception has been raised from API with error message: ' || L_MSG_DATA);
DBMS_OUTPUT.PUT_LINE ('***************************');
END;Output
Start FND_USER_PKG.CREATEUSER API
New User has been created Successfully!!
End FND_USER_PKG.CREATEUSER API
You might also want to check Add responsibility to user in Oracle APPS using API
Final Result
Conclusion
This article gives an example of creating an FND user in Oracle EBS using API from the backend.
Please don’t use this code directly on production environment, instead test it on test environment first to make sure that API working correctly as per your requirements.
Hopefully, it was clear and concise. Share to Spread Knowledge.
