How to find password of a User in Oracle Apps R12 - (Decrypting Password)

 To achieve this you need to create a small package and run a query :

 

Package Specification :

CREATE OR REPLACE PACKAGE get_user_pwd

AS

   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2;

END get_user_pwd;
/

Package Body:

CREATE OR REPLACE PACKAGE BODY get_user_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_user_pwd;

Query to execute :

 SELECT usr.user_name,

       get_pwd.decrypt

          ((SELECT (SELECT get_pwd.decrypt

                              (fnd_web_sec.get_guest_username_pwd,

                               usertable.encrypted_foundation_password

                              )

                      FROM DUAL) AS apps_password

              FROM 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 fnd_user usr

 WHERE usr.user_name = ':USER_NAME';
;

Create and Drop Sequences in Oracle

In Oracle, you can create an autonumber field using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

Syntax :

The syntax to create a sequence in Oracle:

CREATE SEQUENCE Sequence_Name
  MINVALUE value
  MAXVALUE value
  START WITH value
  INCREMENT BY value
  CACHE value;

Example :

CREATE SEQUENCE XX_PO_SEQ
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 50 / NONCACHE;

Note : 

1. If you exclude the MAXVALUE option, your sequence will automatically default to: MAXVALUE 999999999999999999999999999

1. The cache option specifies how many sequence values will be stored in memory for faster access. The downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not be used, will be "lost". This results in a "gap" in the assigned sequence values.

Retrieve the next value in the sequence  :

To retrieve next value in sequence user Sequence_Name.NEXTVAL
INSERT INTO XX_PO_TABLE
(PO_ID, PO_NUMBER)
VALUES
(XX_PO_SEQ.NEXTVAL, 'PO-1234');

Check Cuurent value in the sequence  :

To Check current value in sequence user Sequence_Name.CURRVAL
Select XX_PO_SEQ.NEXTVAL from dual;

Drop Sequence :

Syntax :
The syntax to a drop a sequence in Oracle is:

DROP SEQUENCE sequence_name;

DROP SEQUENCE XX_PO_SEQ;

Note :

To recover the lost sequence values or skip some values you can always execute an ALTER SEQUENCE command to reset the counter to the correct value.

Query to get concurrent program run details in oracle apps

SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
 round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
 a.request_id,a.parent_request_id,
 a.request_date,
 a.actual_start_date,
 a.actual_completion_date,
 (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
 (a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
 d.user_name,
 a.phase_code,
 a.status_code,
 a.argument_text,
 a.priority
FROM   apps.fnd_concurrent_requests a,apps.fnd_concurrent_programs b,apps.FND_CONCURRENT_PROGRAMS_TL c,apps.fnd_user d
WHERE  a.concurrent_program_id=b.concurrent_program_id
AND b.concurrent_program_id=c.concurrent_program_id
AND a.requested_by=d.user_id 
and d.user_id=:P_USER_ID
x