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';
Oracle E-Business Suite Tech
Random stuff about Oracle E-Business Suite Technology
Friday, 25 October 2019
Wednesday, 16 March 2016
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 ;
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 ;
Tuesday, 8 March 2016
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:
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'
Subscribe to:
Posts (Atom)