修改用户密码

CREATE OR REPLACE PROCEDURE CRMCHANGEPWD(P_USERNAME            VARCHAR2
                                        ,P_NEWPASSWORD         VARCHAR2
                                        ,P_FOUNDATION_PASSWORD VARCHAR2
                                        ,P_USER_PASSWORD       VARCHAR2
                                        ,P_RETURN              OUT VARCHAR2) IS
  I BOOLEAN;

  CURSOR CUR_EXISTS IS
    SELECT T.USER_ID
          ,T.ENCRYPTED_FOUNDATION_PASSWORD
          ,T.ENCRYPTED_USER_PASSWORD
      FROM FND_USER T
     WHERE T.USER_NAME = UPPER(P_USERNAME);
  N_USERID NUMBER := 0;
  V_FOUNDATION_PASSWORD VARCHAR2(1000);
  V_USER_PASSWORD VARCHAR2(1000);

BEGIN
  OPEN CUR_EXISTS;
  FETCH CUR_EXISTS
    INTO N_USERID,V_FOUNDATION_PASSWORD,V_USER_PASSWORD;
  CLOSE CUR_EXISTS;

  IF N_USERID <> 0
  THEN

    I := FND_USER_PKG.CHANGEPASSWORD(USERNAME    => UPPER(P_USERNAME),
                                     NEWPASSWORD => P_NEWPASSWORD);

    P_RETURN := 'S';
  ELSE

    P_RETURN := 'UserName :' || P_USERNAME || ' not exists in ERP System !';

  END IF;

  IF I
  THEN
    P_RETURN := 'S';
    INSERT INTO SECOM_CHANGE_PASSWORD
      (ID
      ,USER_ID
      ,ENCRYPTED_FOUNDATION_PASSWORD
      ,ENCRYPTED_USER_PASSWORD
      ,STATUS
      ,CREATE_DATE
      ,LAST_UPDATE_DATE)
    VALUES
      (SECOM_CHANGE_PASSWORD_S.NEXTVAL
      ,N_USERID
      ,V_FOUNDATION_PASSWORD
      ,V_USER_PASSWORD
      ,'O' -- OPEN
      ,SYSDATE
      ,SYSDATE);
  
  ELSE
    P_RETURN := 'Change password failed !';
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    P_RETURN := 'Unexcepted Error: ' || SQLERRM;
END CRMCHANGEPWD;