Sunday, November 4, 2007

To create users in Bulk in oracle applications

Hi all,
We can fnd_user_pkg api to create users and attach responsibilities all at one shot..
Here i am providing the code which will update or create new users and their repsonsibilities
This script need to be improved a bit more to meet your changes
/******************************************************************************/

create or replace package body XX_FND_USER_PKG as
procedure create_user(p_user_name in varchar2,
p_emp_num in varchar2,
p_resp_id in number) as
v_user_present varchar2(1):='N';
v_err varchar2(1):='N';
v_user_id number;
v_resp_present varchar2(1):='N';
v_empnum_err varchar2(1):='N';
v_resp_key varchar2(50);
v_application_name varchar2(50);
v_person_id number;
v_erromsg varchar2(1000);
begin
begin
select user_id
into v_user_id
from fnd_user
where user_name=p_user_name and sysdate>nvl(end_Date,trunc(sysdate));
exception
when no_data_found then
v_user_present:='N';
end;
if v_user_id is null then
begin
select person_id
into v_person_id
from per_all_people_f papf
where employee_number=p_emp_num
and sysdate between effective_start_Date and effective_End_Date
and rownum=1;
exception
when others then
v_empnum_err :='Y';
end;
if v_empnum_err='N' then
fnd_user_pkg.createuser(x_user_name => p_user_name
,x_owner => ''
,x_unencrypted_password => 'ganjam123'
,x_employee_id=>v_person_id);
end if;
end if;
if v_user_id is not null then
begin
select 'Y'
into v_resp_present
from fnd_user_resp_groups_direct
where user_id=v_user_id
and responsibility_id=p_resp_id;
exception
when no_data_found then
v_resp_present:='N';
end;
end if;
if v_resp_present ='N' then
begin
select responsibility_key,fa.application_short_name
into v_resp_key,v_application_name
from FND_RESPONSIBILITY FR,
fnd_application FA
where FR.application_id=fa.application_id
and responsibility_id=p_resp_id
and rownum=1;
exception
when others then
v_err:='Y';
end;
if v_err ='N' then
fnd_user_pkg.addresp(username => p_user_name
,resp_app => v_application_name
,resp_key => v_resp_key
,security_group => 'STANDARD'
,description => 'Auto Assignment'
,start_date => SYSDATE
,end_date => null);
end if;
end if;

end;
end XX_FND_USER_PKG;/

4 comments:

Unknown said...

hi i am vishnu
code created by u to 'create bulk users and attaching responsibility programatically' was really good provide some more ............
thanq

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