Monday, January 7, 2013

FND User creation script


Oracle provides below API to create FND User.

/*procedure FND_USER_PKG.CreateUser (
  x_user_name                  in varchar2,
  x_owner                      in varchar2,
  x_unencrypted_password       in varchar2 default null,
  x_session_number             in number default 0,
  x_start_date                 in date default sysdate,
  x_end_date                   in date default null,
  x_last_logon_date            in date default null,
  x_description                in varchar2 default null,
  x_password_date              in date default null,
  x_password_accesses_left     in number default null,
  x_password_lifespan_accesses in number default null,
  x_password_lifespan_days     in number default null,
  x_employee_id                in number default null,
  x_email_address              in varchar2 default null,
  x_fax                        in varchar2 default null,
  x_customer_id                in number default null,
  x_supplier_id                in number default null)
*/

Below is an example of the above seeded API. You may add/remore parameters based on your requirement.  

DECLARE
   l_user_name             VARCHAR2(100)  := 'TEST_FND_USER';
   l_user_password         VARCHAR2(100)  := 'welcome1';
   l_password_date         VARCHAR2(100)  := SYSDATE;
   l_password_lifespan_days    NUMBER     := 90;
   l_email_address         VARCHAR2(100) := 'test@default.com';

BEGIN
  FND_USER_PKG.CREATEUSER
  (  x_user_name            => l_user_name,
     x_owner                => NULL,
     x_unencrypted_password => l_user_password,
     x_password_date        => l_password_date,
     x_password_lifespan_days   => l_password_lifespan_days,
     x_email_address        => l_email_address
 );

 COMMIT;

 DBMS_OUTPUT.PUT_LINE('Apps User ' || l_user_name || ' has been successfully created');

EXCEPTION
       WHEN OTHERS THEN
           ROLLBACK;
           DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;