Search This Blog

Saturday, January 26, 2008

API to Create User

The code below is very useful if you want to create a user without logging into application and avoid entering same information again and again. Also if the instances are refresed frequently and the user does not exist in production the script can be very handy.
The script below will prompt for user name and employee name. A commit is required at the end to have affect in the application.


DECLARE
v_session_id INTEGER := userenv('sessionid');
v_user_name VARCHAR2(30) := upper('&User_Name');
v_employee_name per_all_people_f.full_name%TYPE := '&employee_name';
v_employee_id NUMBER;
v_email_address per_all_people_f.email_address%TYPE;
BEGIN
BEGIN
SELECT person_id, email_address
INTO v_employee_id
, v_email_address
FROM per_all_people_f
WHERE upper(full_name) LIKE Upper('%v_employee_name%')
GROUP BY person_id
,email_address;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Employee '||v_employee_name
||' does not exist');
WHEN OTHERS THEN
dbms_output.put_line('Unexpected Error '||SQLERRM);
dbms_output.put_line('while selected person_id');
END;
fnd_user_pkg.createuser(x_user_name => v_user_name
,x_owner => ''
,x_unencrypted_password => 'welcome1'
,x_session_number => v_session_id
,x_start_date => SYSDATE - 10
,x_end_date => SYSDATE + 100
,x_last_logon_date => SYSDATE - 10
,x_description => v_user_name||' Created using API'
,x_password_date => SYSDATE - 10
,x_password_accesses_left => 10000
,x_password_lifespan_accesses => 10000
,x_password_lifespan_days => 10000
,x_employee_id => v_employee_id
,x_email_address => v_email_address
,x_fax => ''
,x_customer_id => ''
,x_supplier_id => '');
fnd_user_pkg.addresp(username => v_user_name
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'System Administrator'
,start_date => SYSDATE - 10
,end_date => SYSDATE + 1000);
-- Created by Suresh Vaishya
END;
/

10 Comments:

David said...

Great Idea - as a developer, I'm never added to a clients Production instance and then I have to wait until I'm reset as a user in a cloned instance...
Thanks!

Couple of notes:
line 4
"v_employee_name per_all_people_f.full_name%full_name := &employee_name;"
should it be:
"v_employee_name per_all_people_f.full_name%TYPE := '&employee_name';"
- Note %TYPE and quotes around employee_name PROMPT.
LINE 13
"WHERE upper(full_name) LIKE '%' || upper(v_emp_name) || '%'"
v_emp_name needs to be: v_employee_name per declaration

Thanks again!
dave@dmcdconsulting.com

Anonymous said...

Hi Suresh,

Wat r the parameters do we need to pass to the API (PO_ACTIONS.CLOSE_PO) to close the PO from back-end.
Plz give me the details with an example. I am confuse for 'p_return_code'.

Appreciate the help.

Thx N Regs,

M. M. Khan

Suresh Vaishya said...

p_return_code is the out parameter that returns the status of your call to the API.
If p_return_code = 'S' then the call was successful.

Regards,
Suresh

Anonymous said...

Hi Suresh,

Can you plz tell me where I am wrong with a clear example.....
close_po is a function which returns a boolean value.

DECLARE
l_return_status Boolean;
l_return_status1 Boolean;
BEGIN
FND_GLOBAL.apps_initialize('111','222','333'); -- ( user_id, Resp_id, Appli_id )
--call the Close API
l_return_status := po_actions.close_po(1205813,-- p_docid
'PO',-- p_doctyp
'STANDARD',-- p_docsubtyp
null,-- p_lineid
null,-- p_shipid
'CLOSE',-- p_action
'TEST',-- p_reason
'PO',-- p_calling_mode
'N',-- p_conc_flag
l_return_status1,-- p_return_code
'Y',-- p_auto_close
SYSDATE,-- p_action_date
null); -- p_origin_doc_id
-- Get any messages returned by the Cancel API
DBMS_OUTPUT.put_line('l_return_status :'||l_return_status);
end;


This is very urgent, Appreciate ur help,

Thx N Regs,
Khan.

ved said...
This comment has been removed by the author.
ved said...

Hi Suresh,
I am new in Oracle Apps.can you send me important documents of WIP,BOM and manufacturing.my email is
ved.prakash.mehta@gmail.com

thanks
Ved Prakash

rajeshbalakrishnan said...

Hi Suresh
You are doing grt work by sharing your knowledge with us.hats off to you.
I have requirement frm my client to create to create new pricebreak for the existing item in pricelist and also needs to update the existing pricebreak data based on the user changes like change in unit price,quantity etc.Could you pls let me know if have any idea on this subject.Also it will be very grateful if you are having readymade code with you for this requirement.

Please let me know if you have any questions.your valuable inputs will be highly appreciated.Thanks once again.


Regards

Rajesh

Suresh Vaishya said...

I think you can achieve this using modifiers.

rajesh said...

Hi Suresh,
Since am new bee to oracle apps ,can you pls elaborate the concept of modifiers and how can i proceed with my requirement using modifiers.

Abdul Bijur V.A. said...

Isn't the sales order import code a part of the standard oracle distribution as examples? If so, this is copyrighted material and should NOT be published without permissions/copyrights.

Regards

Copyright (c) All rights reserved. Presented by Suresh Vaishya