create or replace package body PKG_SYS_USER_SECURITY is
  FUNCTION GET_HASH(P_USER_CODE IN VARCHAR2, P_PASSWORD IN VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    RETURN DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => UPPER(P_USER_CODE) || '/' ||
                                                        UPPER(P_PASSWORD));
  END;

  PROCEDURE SP_ADD_USER(P_OUT_NUM           OUT NUMBER,
                        P_OUT_MSG           OUT VARCHAR2,
                        P_USER_ID           OUT NUMBER,
                        P_USER_CODE         IN VARCHAR2,
                        P_FIRSTNAME         IN VARCHAR2,
                        P_LASTNAME          IN VARCHAR2,
                        P_PASSWORD          IN VARCHAR2,
                        P_EMAIL             IN VARCHAR2,
                        P_DATACLEARANCE_ID  IN NUMBER,
                        P_APPGROUP_ID       IN NUMBER,
                        P_CUSTGROUP_ID      IN NUMBER,
                        P_PRODGROUP_ID      IN NUMBER,
                        P_VENDGROUP_ID      IN NUMBER,
                        P_USERSTATUS_CODE   IN VARCHAR,
                        P_UPDATEBYUSER_CODE IN VARCHAR2) AS
  
    V_USER_CODE_COUNT NUMBER;
  BEGIN
    SELECT COUNT(*) INTO V_USER_CODE_COUNT 
    FROM SYS_USER 
    WHERE USER_CODE = UPPER(P_USER_CODE);
  
    IF V_USER_CODE_COUNT <> 0 THEN
      P_OUT_NUM := 1;
      P_OUT_MSG := 'USER ALREADY EXISTS: ' || P_USER_CODE;
    
      PKG_SYS_LOG.SP_LOG_ACTIVITY(P_UPDATEBYUSER_CODE,
                                 'SYSINFO',
                                 'ERROR',
                                 'ADD_USER',
                                 P_OUT_MSG,
                                 P_OUT_NUM);
    ELSE
      INSERT INTO SYS_USER
        (USER_ID,
         USER_CODE,
         FIRSTNAME,
         LASTNAME,
         PASSWORD,
         EMAIL,
         DATACLEARANCE_ID,
         APPGROUP_ID,
         CUSTGROUP_ID,
         PRODGROUP_ID,
         VENDGROUP_ID,
         USERSTATUS_CODE,
         UPDATEDATE,
         UPDATEBYUSER_CODE,
         PWLASTCHANGEDDATE)
      VALUES
        ((SELECT MAX(USER_ID) + 1 FROM SYS_USER),
         UPPER(P_USER_CODE),
         P_FIRSTNAME,
         P_LASTNAME,
         GET_HASH(P_USER_CODE, P_PASSWORD),
         P_EMAIL,
         P_DATACLEARANCE_ID,
         P_APPGROUP_ID,
         P_CUSTGROUP_ID,
         P_PRODGROUP_ID,
         P_VENDGROUP_ID,
         P_USERSTATUS_CODE,
         SYSDATE,
         UPPER(P_UPDATEBYUSER_CODE),
         SYSDATE);
    
      COMMIT;
      
      SELECT MAX(USER_ID) INTO P_USER_ID FROM SYS_USER;
      -------------------------- Standard Return Block   - Success 
      P_OUT_NUM := 0;
      P_OUT_MSG := 'NEW USER ADDED: ' || P_USER_CODE;
    
      PKG_SYS_LOG.SP_LOG_ACTIVITY(P_UPDATEBYUSER_CODE,
                                 'SYSINFO',
                                 'SUCCESS',
                                 'ADD_USER',
                                 P_OUT_MSG,
                                 P_OUT_NUM);
    
      --------------------------------------  
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      P_OUT_NUM := SQLCODE;
      P_OUT_MSG := SUBSTR(SQLERRM, 1, 500);
      PKG_SYS_LOG.SP_LOG_ACTIVITY(P_UPDATEBYUSER_CODE,
                                 'SYSINFO',
                                 'ERROR',
                                 'ADD_USER',
                                 P_OUT_MSG,
                                 P_OUT_NUM);
  END;

  /* ******************************************************************************** */
  PROCEDURE SP_UPDATE_USER(P_OUT_NUM           OUT NUMBER,
                           P_OUT_MSG           OUT VARCHAR2,
                           P_USER_ID           IN NUMBER,
                           P_USER_CODE         IN VARCHAR2,
                           P_FIRSTNAME         IN VARCHAR2,
                           P_LASTNAME          IN VARCHAR2,
                           P_EMAIL             IN VARCHAR2,
                           P_DATACLEARANCE_ID  IN VARCHAR2,
                           P_APPGROUP_ID       IN VARCHAR2,
                           P_CUSTGROUP_ID      IN VARCHAR2,
                           P_PRODGROUP_ID      IN VARCHAR2,
                           P_VENDGROUP_ID      IN VARCHAR2,
                           P_USERSTATUS_CODE   IN VARCHAR2,
                           P_UPDATEBYUSER_CODE IN VARCHAR2) AS
  
    V_ROWID ROWID;
  
  BEGIN
    SELECT ROWID INTO V_ROWID 
    FROM SYS_USER 
    WHERE USER_ID = P_USER_ID FOR UPDATE;
  
    UPDATE SYS_USER
       SET FIRSTNAME         = P_FIRSTNAME,
           LASTNAME          = P_LASTNAME,
           USER_CODE         = P_USER_CODE,
           EMAIL             = P_EMAIL,
           DATACLEARANCE_ID  = P_DATACLEARANCE_ID,
           APPGROUP_ID       = P_APPGROUP_ID,
           CUSTGROUP_ID      = P_CUSTGROUP_ID,
           PRODGROUP_ID      = P_PRODGROUP_ID,
           VENDGROUP_ID      = P_VENDGROUP_ID,
           USERSTATUS_CODE   = P_USERSTATUS_CODE,
           UPDATEDATE        = SYSDATE,
           UPDATEBYUSER_CODE = UPPER(P_UPDATEBYUSER_CODE)
     WHERE ROWID = V_ROWID;
  
    COMMIT;
  
    ------------------------------------- Standard Return Block   - Success 
    P_OUT_NUM := 0;
    P_OUT_MSG := 'USER DETAILS CHANGED FOR: ' || P_USER_CODE;
    PKG_SYS_LOG.SP_LOG_ACTIVITY(P_UPDATEBYUSER_CODE,
                               'SYSINFO',
                               'SUCCESS',
                               'UPDATE_USER',
                               P_OUT_MSG,
                               P_OUT_NUM);
  
    --------------------------------------  
  
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      ROLLBACK;
      P_OUT_NUM := -20000;
      P_OUT_MSG := 'INVALID USERNAME';
      PKG_SYS_LOG.SP_LOG_ACTIVITY(P_UPDATEBYUSER_CODE,
                                 'SYSINFO',
                                 'ERROR',
                                 'UPDATE_USER',
                                 P_OUT_MSG,
                                 P_OUT_NUM);
    
    WHEN OTHERS THEN
      ROLLBACK;
      P_OUT_NUM := SQLCODE;
      P_OUT_MSG := SUBSTR(SQLERRM, 1, 500);
      PKG_SYS_LOG.SP_LOG_ACTIVITY(P_UPDATEBYUSER_CODE,
                                 'SYSINFO',
                                 'ERROR',
                                 'UPDATE_USER',
                                 P_OUT_MSG,
                                 P_OUT_NUM);
    
  END;
  /* ******************************************************************************** */
  PROCEDURE SP_CHANGE_PASSWORD(P_OUT_NUM           OUT NUMBER,
                               P_OUT_MSG           OUT VARCHAR2,
                               P_USER_CODE         IN VARCHAR2,
                               P_OLD_PASSWORD      IN VARCHAR2,
                               P_NEW_PASSWORD      IN VARCHAR2,
                               P_UPDATEBYUSER_CODE IN VARCHAR2) AS
    V_ROWID ROWID;
  BEGIN
    SELECT ROWID
      INTO V_ROWID
      FROM SYS_USER
     WHERE USER_CODE = UPPER(P_USER_CODE)
       AND PASSWORD = GET_HASH(P_USER_CODE, P_OLD_PASSWORD)
       FOR UPDATE;
  
    UPDATE SYS_USER
       SET PASSWORD = GET_HASH(P_USER_CODE, P_NEW_PASSWORD),
           PWLASTCHANGEDDATE = SYSDATE,
           UPDATEDATE = SYSDATE,
           UPDATEBYUSER_CODE = UPPER(P_UPDATEBYUSER_CODE)
     WHERE ROWID = V_ROWID;
    COMMIT;
  
    ------------------------------------- Standard Return Block - Success 
    P_OUT_NUM := 0;
    P_OUT_MSG := 'PASSWORD CHANGED FOR' || P_USER_CODE || ' SUCCESSFULLY.';
  
    PKG_SYS_LOG.SP_LOG_ACTIVITY(P_UPDATEBYUSER_CODE,