Results 1 to 10 of 10
  1. #1
    Join Date
    May 2013
    Posts
    5

    Unanswered: Adding created_by in the user creation script

    Hi everybody,
    I have written a small script to add user to FND_USER table.

    DECLARE
    v_user_name VARCHAR2 (30) := UPPER ('mario5');
    v_password VARCHAR2 (30) := 'ebspro';
    v_session_id NUMBER := USERENV ('sessionid');
    v_email VARCHAR2 (30) := UPPER ('oracle.pro@gmail.com');
    BEGIN
    FND_USER_PKG.createuser (x_user_name => v_user_name,
    x_owner => NULL,
    x_unencrypted_password => v_password,
    x_session_number => v_session_id,
    x_start_date => SYSDATE,
    x_end_date => NULL,
    x_email_address => v_email
    );
    COMMIT;
    DBMS_OUTPUT.put_line ('Success');
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ('Failed'|| SUBSTR (SQLERRM, 1, 100));
    ROLLBACK;
    END;
    But I want to add created_by field with value as 1 but I'm not able to do add it in the above script as it gives an error PLS-00306: wrong number or types of arguments in call to 'CREATEUSER'
    This is what I tried.I declared it add passed it in the function ( v_created_by number := fnd_profile.VALUE ('USER_ID')

    How else can I add that attribute? Please help me get past this issue.

    Regards,
    Kirthana

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    can you modify FND_USER_PKG.createuser source code?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2013
    Posts
    5
    No.. I wouldn't want to do that..

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This looks like an Oracle Apps. question ("FND_USER" and stuff). Is it? If so, perhaps you'd rather NOT mess it up, but create your own table which would store additional information you are interested in, for example
    Code:
    create table user_log
      (user_name varchar2(30),
       date_created date,
       created_by varchar2(30)
      );
    When to populate that table? After you execute FND_USER_PKG.CREATEUSER procedure or, optionally, by creating a database trigger on FND_USER table.

  5. #5
    Join Date
    May 2013
    Posts
    5
    @littlefoot. Yes this is oracle apps.
    I cant create another table due to some restrictions.
    You said "or, optionally, by creating a database trigger on FND_USER table."
    How do I do this?

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I want to add created_by field
    vs.
    I cant create another table
    How do these two go together? You can't CREATE TABLE (which is rather harmless), but you can ALTER TABLE (and - possibly - compromise Oracle Apps. scripts that deal with that table)?

    Or, maybe I didn't understand what you are saying. Does that column (which is supposed to contain information you're interested in) exist in FND_USER table, or not?

  7. #7
    Join Date
    May 2013
    Posts
    5
    Created_by attribute is present in the table FND_USER table but CreateUser procedure does not have it. I hope it is clear.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK then.

    I don't use Oracle Apps. and I don't know how FND_USER table looks like, so I'm creating my own table. I hope you'll get the idea.
    Code:
    SQL> show user
    USER is "SCOTT"
    SQL> create table my_fnd_user
      2    (user_name  varchar2(30),
      3     created_on date,
      4     created_by varchar2(30)
      5    );
    
    Table created.
    
    SQL> create or replace trigger trg_bi_fndu
      2    before insert on my_fnd_user
      3    for each row
      4  begin
      5    :new.created_by := user;
      6  end;
      7  /
    
    Trigger created.
    
    SQL> insert into my_fnd_user (user_name, created_on) values ('LF', sysdate);
    
    1 row created.
    
    SQL> select * from my_fnd_user;
    
    USER_NAME                      CREATED_ON CREATED_BY
    ------------------------------ ---------- ------------------------------
    LF                             15.05.2013 SCOTT
    
    SQL>

  9. #9
    Join Date
    May 2013
    Posts
    5
    I'm doing this by logging in as apps/apps. And I log in to sqldeveloper to do this.
    So it somehow treats it as an anonymous login and sets the created_by atttribute to -1. But I want to set it as 0- sysadmin, as we login to oracle apps using sysadmin. But I cant login to sql developer using sysadmin. This is where I'm stuck....

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As far as I can tell, all you have to do is to create a trigger.

    Then, log on to the application and do whatever you do (including "create user"). When anyone, logged under his/her username, inserts data into the FND_USER table, trigger will fire and set CREATED_BY column to reflect currently logged user.

    Once again: as I don't use Oracle Apps., it might behave differently, but that's something I can't assist.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •