Home » Applications » Oracle Fusion Apps & E-Business Suite » Change end_date in fnd_user.updateuser
Change end_date in fnd_user.updateuser [message #536120] Mon, 19 December 2011 05:18 Go to next message
aadebayo
Messages: 35
Registered: August 2005
Member
I am currently trying to extend the end_date of all the external users of my application. I have created a procedure to update the end_date of evry user selected in a cursor. When I run the plsql procedure, the end_date is not updated. Please can anyone help? The procedure is below

procedure 
as
   CURSOR usercur
   IS
      SELECT fu.user_name, fu.start_date
        FROM apps.fnd_user fu
       WHERE user_name like '%EXT';
    DECLARE ld_end_date DATE;
BEGIN
ld_end_date = SYSDATE + 10000;
   FOR myuser IN usercur
      dbms_output.put_line('current user is   = ' || myuser.user_name|| 'Start date is  = ' || myuser.start_date);
   LOOP
      fnd_user_pkg.updateuser(
        x_user_name      => myuser.user_name
       ,x_owner    => 'CUST'   
       ,x_end_date       => ld_end_date
      );
    dbms_output.put_line('current user is   = ' || myuser.user_name|| 'Start date is  = ' || myuser.start_date);
    END LOOP; 
END;
Re: Change end_date in fnd_user.updateuser [message #536125 is a reply to message #536120] Mon, 19 December 2011 05:39 Go to previous messageGo to next message
Littlefoot
Messages: 19338
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know Oracle Apps., but - can't you do that simply in SQL*Plus, for example
update fnd_user set
  end_date = sysdate + 10000
where user_name like '%EXT%';


Anyway: are you sure that cursor's WHERE clause is correctly written? Is it '%EXT' or, perhaps, '%EXT%'?

Another possibility is that UPDATEUSER procedure doesn't do its job as you thought it would.

Finally: did you COMMIT?
Re: Change end_date in fnd_user.updateuser [message #536126 is a reply to message #536125] Mon, 19 December 2011 05:41 Go to previous messageGo to next message
aadebayo
Messages: 35
Registered: August 2005
Member
fnd_user is a table written by oracle and our support will be invalidated if I update it directly. Also I have run the sql statement in the cursor and it returned the required results. Thanks

[Updated on: Mon, 19 December 2011 05:42]

Report message to a moderator

Re: Change end_date in fnd_user.updateuser [message #536132 is a reply to message #536126] Mon, 19 December 2011 07:18 Go to previous messageGo to next message
Littlefoot
Messages: 19338
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I see.

So - did you commit?
Re: Change end_date in fnd_user.updateuser [message #536136 is a reply to message #536132] Mon, 19 December 2011 07:27 Go to previous message
aadebayo
Messages: 35
Registered: August 2005
Member
Littlefoot, thanks very much, that was my problem.
Previous Topic: Disabling Standard Functionality.
Next Topic: Suppliers cannot see technical response of other suppliers
Goto Forum:
  


Current Time: Thu Jul 31 05:47:44 CDT 2014

Total time taken to generate the page: 0.09646 seconds