Reset Application Password/PL/SQL Script to Reset User Password from Backend

/************************************************************

*PURPOSE: To change/reset password of a user from backend   *

*AUTHOR: PAVEL Ahammed                                 *

*************************************************************/

SET serveroutput ON;

DECLARE

  v_user_name    VARCHAR2(30):= UPPER('PAVEL');

  v_new_password VARCHAR2(30):= '123456';

  v_status       BOOLEAN;

BEGIN

  v_status   := fnd_user_pkg.ChangePassword ( username => v_user_name, 

                                              newpassword => v_new_password 

                                            );

  IF v_status  THEN

    dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);

    COMMIT;

  ELSE

    DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

    ROLLBACK;

  END IF;

END;

Get List of Responsibilities Assigned to a user

 select fu.user_name,

  frt.responsibility_name,

  furg.start_date,

  furg.end_date

from fnd_user fu ,

  fnd_user_resp_groups_direct furg ,

  fnd_responsibility_vl frt

where fu.user_id                 = furg.user_id

and frt.responsibility_id        = furg.responsibility_id

and frt.application_id           = furg.responsibility_application_id

and nvl(furg.end_date,sysdate+1) > sysdate

and nvl(frt.end_date,sysdate +1) > sysdate

and fu.user_name                   = :p_user_name

To Find USER Concurrent Request in Oracle

Currently running concurrent program details along with user and responsibility

SELECT fcp.user_concurrent_program_name,

  fcr.request_id,

  fcr.request_date,

  fu.user_name Requested_By ,

  fr.responsibility_name,

  TO_CHAR(fcr.actual_start_date,'DD-MON-YYYY HH24:MI:SS') actual_start_date

FROM fnd_concurrent_requests fcr ,

  fnd_concurrent_programs_tl fcp ,

  fnd_user fu ,

  fnd_responsibility_tl fr

WHERE 1                       =1

AND fcr.phase_code            ='R'

AND fcr.concurrent_program_id = fcp.concurrent_program_id

AND fcr.requested_by          = fu.user_id

AND FCR.RESPONSIBILITY_ID     = FR.RESPONSIBILITY_ID

Query To Find Concurrent Request,Requester,Parameter And Completion Time

 

SELECT   request_id, phase_code, status_code, responsibility_id,

         actual_start_date, actual_completion_date, completion_text,

         argument_text, program_short_name, requestor

    FROM apps.fnd_conc_req_summary_v

   WHERE program_short_name LIKE 'INV_STORE_STOCK_CONS_SUMM'

ORDER BY request_id DESC;

 

This query is based on oracle standard view.If you still want to drill down whats exactly joins and which tables are present, sharing below query with all details :

 

 SELECT r.ROWID row_id,

        r.request_id,

        r.phase_code,

        r.status_code,

        r.priority_request_id,

        r.priority,

        r.request_date,

        r.requested_by,

        r.requested_start_date,

        r.hold_flag,

        r.has_sub_request,

        r.is_sub_request,

        r.update_protected,

        r.queue_method_code,

        r.responsibility_application_id,

        r.responsibility_id,

        r.save_output_flag,

        r.last_update_date,

        r.last_updated_by,

        r.last_update_login,

        r.printer,

        r.print_style,

        r.parent_request_id,

        r.controlling_manager,

        r.actual_start_date,

        r.actual_completion_date,

        r.completion_text,

        r.argument_text,

        r.implicit_code,

        r.request_type,

        r.program_application_id,

        r.concurrent_program_id,

        pb.concurrent_program_name program_short_name,

        pb.execution_method_code,

        pb.enabled_flag enabled,

        DECODE(r.description,

               NULL,

               pt.user_concurrent_program_name,

               r.description || ' (' || pt.user_concurrent_program_name || ')') program,

        pb.printer_name fcp_printer,

        pb.output_print_style fcp_print_style,

        pb.required_style fcp_required_style,

        u.user_name requestor,

        s.user_printer_style_name user_print_style,

        r.description description,

        pt.user_concurrent_program_name user_concurrent_program_name

   FROM fnd_concurrent_programs_tl pt,

        fnd_concurrent_programs    pb,

        fnd_user                   u,

        fnd_printer_styles_tl      s,

        fnd_concurrent_requests    r

  WHERE pb.application_id = r.program_application_id

    AND pb.concurrent_program_id = r.concurrent_program_id

    AND pb.application_id = pt.application_id

    AND pb.concurrent_program_id = pt.concurrent_program_id

    AND pt.LANGUAGE = USERENV('LANG')

    AND u.user_id = r.requested_by

    AND s.printer_style_name(+) = r.print_style

    AND s.LANGUAGE(+) = USERENV('LANG');

Find Oracle APPS User Password From Backend

 

How to get Oracle Application User Password from database - oracle.apps.fnd.security.WebSessionManagerProc.decrypt

Step 1: Compile the below package in APPS schema

-- APPS SCHEMA

DROP PACKAGE get_pwd;

CREATE OR REPLACE PACKAGE get_pwd

AUTHID CURRENT_USER

IS

  FUNCTION decrypt(key   IN VARCHAR2,

                   value IN VARCHAR2

                                 ) return VARCHAR2;

end get_pwd;

/

CREATE OR REPLACE PACKAGE BODY get_pwd

AS

  FUNCTION decrypt_java(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';

  FUNCTION decrypt(key   IN VARCHAR2,

                   value IN VARCHAR2

                                 ) return VARCHAR2

  AS

  BEGIN

    IF user = 'APPSVIEWER' -- Schema from where password should not be shown

        THEN

          RETURN NULL;

        ELSE

      RETURN (decrypt_java(KEY,VALUE));

        END IF;

  END;

END get_pwd;

Step 2: Below query will help you to get password

SELECT usr.user_name,usr.user_id,usr.description,

    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 IN ('SYSADMIN');