Find Password in Oracle E-Business Suite 11i and R12

This scripts allows to view the passwords which are stored encrypted in fnd_user table.
You can use different sqls to find apps password and application user passwords.

Create Package
CREATE OR REPLACE PACKAGE XXARTO_GET_PWD
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END XXARTO_GET_PWD;

CREATE OR REPLACE PACKAGE BODY XXARTO_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 XXARTO_GET_PWD;

Find apps password in 11i
SELECT (SELECT XXARTO_GET_PWD.Decrypt (
                  UPPER( (SELECT UPPER (Fnd_Profile.VALUE ('Guest_User_Pwd'))
                            FROM DUAL)),
                  Usertable.Encrypted_Foundation_Password)
          FROM DUAL)
          AS Apps_Password
  FROM applsys.Fnd_User Usertable
 WHERE Usertable.User_Name LIKE
          UPPER( (SELECT SUBSTR (
                            Fnd_Profile.VALUE ('Guest_User_Pwd'),
                            1,
                            INSTR (Fnd_Profile.VALUE ('Guest_User_Pwd'), '/')
                            - 1)
                    FROM DUAL));

Find apps password in R12
 SELECT (SELECT XXARTO_GET_PWD.Decrypt (
                  Fnd_Web_Sec.Get_Guest_Username_Pwd,
                  Usertable.Encrypted_Foundation_Password)
          FROM DUAL)
          AS Apps_Password
  FROM applsys.Fnd_User Usertable
 WHERE Usertable.User_Name LIKE
          (SELECT SUBSTR (
                     Fnd_Web_Sec.Get_Guest_Username_Pwd,
                     1,
                     INSTR (Fnd_Web_Sec.Get_Guest_Username_Pwd, '/') - 1)
             FROM DUAL);

Find application user password (for exaample SYSADMIN) in 11i
SELECT Usertable.User_Name,
       (SELECT XXARTO_GET_PWD.Decrypt (
                  UPPER( (SELECT (SELECT XXARTO_GET_PWD.Decrypt (
                                            UPPER( (SELECT UPPER(Fnd_Profile.VALUE('Guest_User_Pwd'))
                                                      FROM DUAL)),
                                            Usertable.Encrypted_Foundation_Password)
                                    FROM DUAL)
                                    AS Apps_Password
                            FROM applsys.Fnd_User Usertable
                           WHERE Usertable.User_Name LIKE
                                    UPPER( (SELECT SUBSTR (
                                                      Fnd_Profile.VALUE (
                                                         'Guest_User_Pwd'),
                                                      1,
                                                      INSTR (
                                                         Fnd_Profile.VALUE (
                                                            'Guest_User_Pwd'),
                                                         '/')
                                                      - 1)
                                              FROM DUAL)))),
                  Usertable.Encrypted_User_Password)
          FROM DUAL)
          AS Encrypted_User_Password
  FROM Applsys.Fnd_User Usertable
 WHERE Usertable.User_Name LIKE UPPER ('&Username');

Find application user password (for exaample SYSADMIN) in R12
SELECT Usr.User_Name,
       Usr.Description,
       XXARTO_GET_PWD.Decrypt (
          (SELECT (SELECT XXARTO_GET_PWD.Decrypt (
                             Fnd_Web_Sec.Get_Guest_Username_Pwd,
                             Usertable.Encrypted_Foundation_Password)
                     FROM DUAL)
                     AS Apps_Password
             FROM applsys.Fnd_User Usertable
            WHERE Usertable.User_Name =
                     (SELECT SUBSTR (
                                Fnd_Web_Sec.Get_Guest_Username_Pwd,
                                1,
                                INSTR (Fnd_Web_Sec.Get_Guest_Username_Pwd,
                                       '/')
                                - 1)
                        FROM DUAL)),
          Usr.Encrypted_User_Password)
          Password
  FROM applsys.Fnd_User Usr
 WHERE Usr.User_Name = '&User_Name';

How to find Foreign Keys references to a Table

In order to find all the Foreign Keys that point to a certain Table, you can use the SQL bellow:

SELECT table_name,
  constraint_name,
  status,
  owner
FROM all_constraints
WHERE r_owner          = :owner
AND constraint_type    = 'R'
AND r_constraint_name IN
  (SELECT constraint_name
  FROM all_constraints
  WHERE constraint_type IN ('P', 'U')
  AND table_name         = :table_name
  AND owner              = :owner
  )
ORDER BY table_name,
  constraint_name ;

Add and Remove LOV entries in Forms

When editing LOV definitions, if you want to add or remove entries, you can use the following keyboard shortcuts:

• For deleting a line: ctrl+<
• For adding a new line: ctrl+> (ctrl+shift+<)

Split Comma-Separated List into multiple Rows

WITH t AS (SELECT 'aaaaa,bbbbbb,cccccc' cs_values FROM dual) select regexp_substr(cs_values, '[^,]+', 1, rownum) values_list FROM t CONNECT BY ROWNUM <= LENGTH(regexp_replace(cs_values, '[^,]'))+1 ;

Return Multiple Rows as a Single Comma-Separated Row

You can use the following SQL to return multiple rows as a single row:

select listagg(<column>, ',') within group (order by <column>) as list

FROM <table>

Check if string value is numeric

To test a string for numeric characters, you could use a combination of the LENGTH, TRIM, AND TRANSLATE functions in the following way:

LENGTH(TRIM(TRANSLATE('this_string', ' +-.0123456789', ' ')))

'this_string' is the string value that you are testing.

This function will return a null value if 'the_string' is numeric. It will return a number greater than 0 if 'the_string' contains any non-numeric characters.

Examples:

SQL	Result
LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' ')))	1
LENGTH(TRIM(TRANSLATE('a123b', ' +-.0123456789',' ')))	2
LENGTH(TRIM(TRANSLATE('1256.54', ' +-.0123456789',' ')))	null
LENGTH(TRIM(TRANSLATE ('-56', ' +-.0123456789',' ')))	null

List Responsibilities that can access a form

select rt.responsibility_name, metl.prompt

from fnd_responsibility_tl rt

,    fnd_responsibility r

,    fnd_menus m

,    fnd_menu_entries me

,    fnd_menu_entries_tl metl

,    fnd_form_functions ff

,    fnd_form f

where f.form_id = ff.form_id

and   ff.function_id = me.function_id

and   m.menu_id = me.menu_id

and   m.menu_id = metl.menu_id

and   me.entry_sequence = metl.entry_sequence

and   r.menu_id = m.menu_id

and   r.responsibility_id = rt.responsibility_id

and   metl.language = 'US'

and   f.form_name = 'FORM_NAME'

and   ff.function_name = 'FUNCTION_NAME'