Wednesday, May 27, 2015

Useful scripts

--Script to reset password of application user with APPS password
declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_new_password varchar2(30):='&Enter_New_Password';
v_status boolean;
begin
v_status:= fnd_user_pkg.ChangePassword (
    username => v_user_name,
    newpassword => v_new_password
);
if v_status =true then
dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
commit;
else
DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
rollback;
END if;
end;
 List of responsibilities a application user has
------------------------------------------------------------------ ------------------------------------------------------------------
SELECT frt.RESPONSIBILITY_NAME, furg.end_date
FROM
fnd_user_resp_groups fur,
FND_RESPONSIBILITY fr,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.user_name = '&username'
AND fu.user_id = fur.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
ORDER BY 1

 How to activate user by setting end_date as Null

 ----------------------------------------------------------------- ------------------------------------------------------------------

DECLARE
   CURSOR usercur
   IS
      SELECT fu.user_name
        FROM apps.fnd_user fu
       WHERE user_name = 'TEST_ACCOUNT';
BEGIN

   FOR myuser IN usercur
   LOOP
      fnd_user_pkg.updateuser(
         x_user_name      => myuser.user_name
       , x_owner          => 'CUST'

        ---- This is 1/2/4712. Oracle keeps end date as null
        , x_end_date       =>  to_date('2', 'J')
      );
   END LOOP;

END;

No comments: