how can we delete oracle 11i users

[smaamirj]

i want to know ,how to delete oracle 11i application user not the database user.

e.g. i created a user from security---> define---->user

now i want to delete this ,wat steps i have to follow

regards

[yemfola]

You cannot delete a user but you can disable by endating it
Hope this helpful

[smaamirj]

Thanks alot for ur reply ,is there is any specific reason oracle not allow deleting of user.
Because delete of users are common practice in other ERP's.

[ahmadbilal]

User Id is stored against every transaction Done in oracle ERP.this user id stored in standard who Column named "Created By" , "last Updaated By"

For Example we have created user A and done transaction in inventory such as misc receipt after transaction u delete (in case Deleteion Allowed) user when ever you query the transaction done by user A then system will raise "no data found" exception.


[quote]Thanks alot for ur reply ,is there is any specific reason oracle not allow deleting of user.
Because delete of users are common practice in other ERP's.



<i><div align="right">Originally posted by smaamirj

[smaamirj]

Thanks alot ,Nice to see your reply

[anu_uma]

Dear Ahmad,
There is a possibility to change the User name , on that occassion what happens with the user id? does the user id gets changed????

What will happen to the transaction done by the user with old names???



TIA

[ahmadbilal]

Change of user name have no effect on these transacations
transaction are linked with user id so there will no be no error in case change of user name

they always show current value from fnd_user table

[quote]Dear Ahmad,
There is a possibility to change the User name , on that occassion what happens with the user id? does the user id gets changed????

What will happen to the transaction done by the user with old names???



TIA

<i><div align="right">Originally posted by anu_uma

[ooaam]

for delete user


SQL> SELECT USER_ID,USER_NAME FROM FND_USER WHERE USER_NAME ='TEMP_USER';

USER_ID
---------
USER_NAME
-------------------------------------------------------------------------
1007919
TEMP_USER


SQL> DELETE FROM FND_USER WHERE USER_ID = 1007919;

1 row deleted.

SQL> COMMIT;

Commit complete.

[admin]

Any such activity from backend is strongly prohibited the best option is to disable by putting end date. Don't delete...........never delete.

Thanks

[ahmadbilal]

You can use FND_USER_PKG to modify a user from back end.

below is the fnd_user_pkg.removependinguser
--
-- RemovePendingUser (PUBLIC)
-- Delete this user from fnd_user table only if this is a pending user.
-- If this is not a valid username or is not a pending user, raise error.
-- Pending user is created when a user registers a user account through
-- UMX with an aproval process.
--
-- Usage example in pl/sql
-- begin fnd_user_pkg.removependinguser('SCOTT'); end;
--
-- Input (Mandatory)
-- username: User Name
--
procedure RemovePendingUser(username varchar2) is
l_user_id number;
begin

-- Added for Function Security Cache Invalidation
-- RSHEH: Need to have exception trapping for no_data_found exception
begin
select user_id into l_user_id
from fnd_user
where user_name = upper(username)
and to_char(start_date) = to_char(FND_API.G_MISS_DATE)
and to_char(end_date) = to_char(FND_API.G_MISS_DATE);

exception
when no_data_found then
fnd_message.set_name('FND', 'FND_INVALID_USER');
fnd_message.set_token('USER_NAME', username);
app_exception.raise_exception;
end;

-- Only allow to delete a PENDING user
delete from fnd_user
where user_name = upper(username)
and to_char(start_date) = to_char(FND_API.G_MISS_DATE)
and to_char(end_date) = to_char(FND_API.G_MISS_DATE);

if (sql%rowcount = 0) then
fnd_message.set_name('FND', 'FND_INVALID_USER');
fnd_message.set_token('USER_NAME', username);
app_exception.raise_exception;
else
-- Added for Function Security Cache Invalidation
fnd_function_security_cache.delete_user(l_user_id);
end if;

end RemovePendingUser;

i want to know ,how to delete oracle 11i application user not the database user.

e.g. i created a user from security---> define---->user

now i want to delete this ,wat steps i have to follow

regards

<i><div align="right">Originally posted by smaamirj - 03/05/2007 : 11:43:38 AM</div id="right"></i>