Cacher is the code snippet organizer for pro developers

CREATE OR REPLACE PACKAGE APPS.get_pwd
AS
FUNCTION decrypt (
KEY IN VARCHAR2
,VALUE IN VARCHAR2
)
RETURN VARCHAR2;
END get_pwd;
/




CREATE OR REPLACE PACKAGE BODY APPS.get_pwd AS
FUNCTION decrypt (
KEY IN VARCHAR2
,VALUE IN VARCHAR2
)
RETURN VARCHAR2
AS
LANGUAGE JAVA
NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt
(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/

SELECT get_pwd.decrypt (fnd_web_sec.get_guest_username_pwd,
                        fu.encrypted_foundation_password
                       ) AS apps_password,
       get_pwd.decrypt (:apps_test_pwd, fu.encrypted_user_password)
                                                        AS user_password_test,
       get_pwd.decrypt (:apps_prod_pwd, fu.encrypted_user_password)
                                                        AS user_password_prod,
       get_pwd.decrypt (:apps_dev_pwd, fu.encrypted_user_password)
                                                         AS user_password_dev,
       get_pwd.decrypt (:apps_ut_pwd, fu.encrypted_user_password)
                                                          AS user_password_ut,
       fu.user_name,
       fu.EMAIL_ADDRESS
  FROM fnd_user fu
 WHERE fu.user_name =
          SUBSTR (fnd_web_sec.get_guest_username_pwd,
                  1,
                  INSTR (fnd_web_sec.get_guest_username_pwd, '/') - 1
                 )
    OR fu.user_name LIKE :user_name
    OR upper(fu.EMAIL_ADDRESS) like upper(:p_email_address)