Search This Blog

Wednesday, February 27, 2013

Monitor Concurrent program running SQL


select SQL_TEXT from v$sql where sql_id = (
select d.sql_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = 2450673
AND a.phase_code = 'R');

Friday, February 22, 2013

Update email address on FND_USER (Useful for data scrambling)


Declare
  v_action_UorR VARCHAR2(1) := 'U'; -- 'U' for Update email address and 'R' for revert the update
  V_Session_Id INTEGER := Userenv('sessionid');
  result       BOOLEAN;
  v_user_id    INTEGER;
  CURSOR Cur_User
  IS
    Select * From Fnd_User
    Where User_Name Like 'VMALLAREDDY%'
    AND email_address is not null;
Begin
  IF &v_action_UorR = 'U' THEN
  FOR Rec_User IN Cur_User
  LOOP
    Fnd_User_Pkg.Updateuser(X_User_Name => Rec_User.User_Name ,
                            X_Owner => 'SEED' ,
                            X_Description => Rec_User.Description,
                            X_Email_Address=> Rec_User.Email_Address||'_XX'
                            );
    Dbms_Output.Put_Line ( 'User updated '||rec_user.user_name ) ;
  END LOOP;

  Else
    FOR Rec_User IN Cur_User
  LOOP
    Fnd_User_Pkg.Updateuser(X_User_Name => Rec_User.User_Name ,
                            X_Owner => 'SEED' ,
                            X_Description => Rec_User.Description,
                            X_Email_Address=> replace(Rec_User.Email_Address,'_XX','')
                            );
    Dbms_Output.Put_Line ( 'User updated '||rec_user.user_name ) ;
  End Loop;
  END IF;
  Commit;
EXCEPTION
   WHEN OTHERS THEN
        Dbms_Output.Put_Line ('Failed'|| Substr (Sqlerrm, 1, 100));
        Rollback;
END;
/