Purging Strategy in Oracle E-Business Suite -1- (FNDLGPRG)

Concurrent Program shortname is FNDLGPRG

Program name is;
"Purge Logs and Closed System Alerts" in R12
"Purge Debug Log and System Alerts" in 11i

This concurrent program purge tables listed below.

FND_EXCEPTION_NOTES

FND_OAM_BIZEX_SENT_NOTIF

FND_LOG_METRICS

FND_LOG_UNIQUE_EXCEPTIONS

FND_LOG_EXCEPTIONS

FND_LOG_MESSAGES

FND_LOG_TRANSACTION_CONTEXT

FND_LOG_ATTACHMENTS

Recommendation is scheduling this program every day with purging 10 days older data.

If these tables are very big as workaround truncate will be possible for these table

truncate table applsys.FND_EXCEPTION_NOTES;
truncate table applsys.FND_OAM_BIZEX_SENT_NOTIF;
truncate table applsys.FND_LOG_METRICS;
truncate table applsys.FND_LOG_UNIQUE_EXCEPTIONS;
truncate table applsys.FND_LOG_EXCEPTIONS;
truncate table applsys.FND_LOG_MESSAGES;
truncate table applsys.FND_LOG_TRANSACTION_CONTEXT;
truncate table applsys.FND_LOG_ATTACHMENTS;

Purging Strategy in Oracle E-Business Suite -2-(FNDCPPUR)

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';