Someone changed apps password using 'alter user' which is not a good practice.
Now during a clone, password change is not working using FNDCPASS.
1) SEC_CASE_SENSITIVE_LOGON parameter is already set to FALSE
2) select fnd_web_sec.validate_login('GUEST','ORACLE') from dual;
FND_WEB_SEC.VALIDATE_LOGIN('GUEST','ORACLE')
--------------------------------------------------------------------------------
Y
3) There is no error on FNDCPASS but password change is not successful.
So, this is what we did as a fix,
Changed apps password to default apps/apps -- using 'alter user'.
Updated password in FND_ORACLE_USERID to apps using encrypted value.
and then re-tried FNDCPASS, this time it was successful.
EBSPROD> create table FND_ORACLE_USERID_BAK as (select * from FND_ORACLE_USERID);
Table created.
EBSPROD> create table FND_USER_BAK as (select * from FND_USER);
Table created.
SQL> ALTER USER apps IDENTIFIED BY apps;
User altered.
SQL> ALTER USER applsys IDENTIFIED BY apps;
User altered.
Below is the value for encrypted apps password --
SQL> update apps.FND_ORACLE_USERID set ENCRYPTED_ORACLE_PASSWORD = 'ZGA34EA20B5C4C9726CC95AA9D49EA4DBA8EDB705CB7673E645EED570D5447161491D78D444554655B87486EF537ED9843C8' where ORACLE_USERNAME in ('APPS', 'APPLSYS');
2 rows updated.
SQL> commit;
Commit complete.
#######################
col user_name for a30
select USER_NAME from APPS.fnd_user order by 1;
CREATE FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2)
RETURN VARCHAR2
AS
LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/
######################
SYSADMIN
=========
SELECT APPS.decrypt_pin_func('GUEST/ORACLE',(SELECT ENCRYPTED_FOUNDATION_PASSWORD from APPS.fnd_user where USER_NAME='GUEST')) from dual;
SELECT ENCRYPTED_USER_PASSWORD FROM APPS.FND_USER where USER_NAME='SYSADMIN';
SELECT APPS.decrypt_pin_func('&appspwd','&sysadminkey') from dual;
