Simple Steps to Find Password for an Application User in Oracle Applications

Step 1. Create get_password package specification, as shown below.

CREATE OR REPLACE PACKAGE get_password

AS

   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2;

END get_password;

/

Step 2. Create get_pwd package body, as shown below.

CREATE OR REPLACE PACKAGE BODY get_password
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_password;
/

Step 3. Query to get password for apps user.

SELECT (SELECT get_password.decrypt (
                  UPPER (
                     (SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))
                        FROM DUAL))
                , usertable.encrypted_foundation_password)
          FROM DUAL)
          AS apps_password
  FROM 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));

Step 4. Query to get password for any application user.

SELECT usertable.user_name
     , (SELECT get_password.decrypt (
                  UPPER (
                     (SELECT (SELECT get_password.decrypt (
                                        UPPER (
                                           (SELECT UPPER (
                                                      fnd_profile.VALUE (
                                                         'GUEST_USER_PWD'))
                                              FROM DUAL))
                                      , usertable.encrypted_foundation_password)
                                FROM DUAL)
                                AS apps_password
                        FROM 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 fnd_user usertable
 WHERE usertable.user_name LIKE UPPER ('&username');

How to block YOUKU ads

Recently Youku advertising began rounds, the Adblock Plus looks like a bar can not afford to, do not worry there are ways, as shown below

Step 1 :

1)Go to C:\Windows\System32\drivers\etc
2)open hosts File.
3)Add the below ipaddress to it.
127.0.0.1 atm.youku.com
127.0.0.1 Fvid.atm.youku.com
127.0.0.1 html.atm.youku.com
127.0.0.1 valb.atm.youku.com
127.0.0.1 valf.atm.youku.com
127.0.0.1 valo.atm.youku.com
127.0.0.1 valp.atm.youku.com
127.0.0.1 lstat.youku.com
127.0.0.1 speed.lstat.youku.com
127.0.0.1 urchin.lstat.youku.com
127.0.0.1 stat.youku.com
127.0.0.1 static.lstat.youku.com
127.0.0.1 valc.atm.youku.com
127.0.0.1 vid.atm.youku.com
127.0.0.1 walp.atm.youku.com

Step 2:

Follow the path to find static.youku.com folder, then delete the folder, create a new txt file, named static.youku.com (wood suffix, pay attention!), And then set to read-only and then theoretically fly

C: \ Users \ mufeng \ AppData \ Local \ Google \ Chrome \ User Data \ Default \ Pepper Data \ Shockwave Flash \ WritableRoot \ # SharedObjects \ LPT6CBCC

Validate or change Oracle Apps username and password

This script can be used for troubleshooting. In Oracle Applications there are two kinds of users -- the Oracle user and application user. The Oracle user password can be verified by direct login. But the applications login can't be directly verified by Oracle (SQLPlus) login. This is not possible because the application user is not created on the Oracle level or by login on self-service. You first have to verify that the guest password is good (it should match DBC file entry). This script will verify that. It has been tested in 11.5.7 and 8.1.7.4.

When running the following script, return Y password is correct. Otherwise change it to Using FNDCPASS.

Note: GUEST/ORACLE is defalt username/PASSWD

Sqlplus apps/apps select fnd_web_sec.validate_login('GUEST','ORACLE') from dual; exit  

Change applications user password: This example shows how to change sysadmin user password.

FNDCPASS apps/apps 0 Y system/manager USER SYSADMIN WELCOME

How to remove lock on a table. ERROR at line 1: ORA-00031: session marked for kill;

What is ORA-00031: session marked for kill , ALTER SYSTEM KILL SESSION, resource busy and acquire with NOWAIT

Problem Description: DBA wanted to kill a long running oracle job/process and he issued ALTER SYSTEM KILL SESSION ‘SID, serial#’ command. DBA got a message ORA-00031: session marked for kill. But, after killing the job, still he is not able to modify the table which was involved in the job or not able to add an index to the table. When he tries do perform any kind DDL change on the table, getting resource busy and acquire with NOWAIT error message.
Session cannot be killed immediate because the session is involved in a non-interruptible operation. Example, the transaction is getting rolled back or being blocked by a network operation.

Why Session Marked for Kill?

 I have a transaction table of 10 million records and I started batch job which update/manipulate the transaction table. The batch process takes 1hour and due to some reason I had to kill the job. I issued ALTER SYSTEM KILL SESSION ‘SID, serial#’ and I got a message ORA-00031: session marked for kill. Normally, huge table manipulation transactions required lot of oracle resources like redo to be used and in the middle of the transaction if you try to kill the process, oracle will take its own time to roll back the transaction. In such situations you get ORA-00031: session marked for kill message and in the background the rollback will start by oracle. The speed of the transaction rollback again depends on the system resources. Until the transaction fully get rolled back the status of the particular session in v$session dictionary view would be KILLED but the table will be locked by the killed session. If you try to do any kind of DDL operation on this table from different session you will get resource busy and acquire with NOWAIT message.

How to Monitor the Transaction Rollback?

Suppose I have session running with sid 14 and serial#787 with and data purge delete on the transaction table with 10million records.

To monitor the undo generated from v$transaction table. Please find the below mentioned query.

SQL> select used_ublk from v$transaction;

 USED_UBLK

----------

      5650

1 row selected.

It means, these much undo is generated.

I am going to kill the session using alter system kill session command.

SQL>alter system kill session '13,787';

Alter system kill session '13,787'

*

ERROR at line 1:

ORA-00031: session marked for kill

SQL> select username, status from v$session where sid=13;

SID                  STATUS

-------------------- ----------

13                   KILLED

1 row selected.

Again if you check the v$transaction table you can see the difference in value

SQL> select used_ublk from v$transaction;

 USED_UBLK

----------

      5298

1 row selected.

SQL> /

 USED_UBLK

----------

      4302

1 row selected.

SQL> select username, status from v$session where sid=13;

SID                  STATUS

-------------------- ----------

13                   KILLED

1 row selected.

SQL> select used_ublk from v$transaction;

 USED_UBLK

----------

      1349

SQL> /

 USED_UBLK

----------

     502

One thing we can notice that the used_ublk is coming down and the v$session status remains as KILLED.

SQL> /

no rows selected

This means the oracle finished it rolling back activity.

SQL> select username, status from v$session where sid=13;

no rows selected

This means the session removed from v$session view after the full rollback of the transaction.

Manual steps to release the lock on the table

In some cases the rolling back process will take long time may be for hours. In such situations if you wanted to perform any kind of update/insert /DDLs the table will not allow due to the lock by the killed session. You will get an error message says ORA-00054 resource busy and acquired with NOWAIT specified. In case if you don’t want to wait for long time until the rollback completes, there are some workaround to terminate the lock by killing process from OS side. You can use kill (UNIX) ORAKILL (WINDOWS) to terminate the process. This is not recommended.

UNIX

Select   p.spid from v$session s , v$process p

where  p.addr =s.paddr

And   s.sid=<> and serial#=<>;

$ kill <spid>      

WINDOWS

Select   p.spid from v$session s , v$process p

where  p.addr =s.paddr

And   s.sid=<> and serial#=<>;

Then use orakill at the DOS prompt:        

c:>orakill sid spid

Script to find Application Login URL from backend

 select home_url from icx_parameters;

Concurrent Processing - How to Find Database Session & Process Associated with a Concurrent Program Which is Currently Running

A concurrent program can be canceled either from "Submit Request Submission" form or from database side also. In case of custom concurrent programs, sometimes concurrent program do not release database session and process even though it has canceled from "Submit Request Submission" form. Active database process can be seen in running status. In those cases there is a need to manually kill that process to release CPU memory.

1.       Take the "request_id" of a Concurrent Program which is currently running or which is to be canceled from the database side.
2.       Connect to SQLPLUS as APPS User :

SQL> SELECT ses.sid,  
            ses.serial#  
       FROM v$session ses,  
            v$process pro  
           WHERE ses.paddr = pro.addr  
                AND pro.spid IN (SELECT oracle_process_id  
                                   FROM fnd_concurrent_requests
                                  WHERE request_id = &request_id);

Note: oracle_process_id is Unix PID and request_id is running concurrent program's request ID. If "sid" and "serial#" value is returning then it means that process is running at database level. When canceling a request from the "Submit Request Submission" form, then it should release associated database process and session but it doesn't mean that it will kill database process immediately. Database process will take their own time to validate concurrent program execution process that has been canceled and then it will kill database process. So ideally when canceling a request from "Submit Request Submission", wait for some time and then check associated database process.

3.      Connect to SQLPLUS as the SYSTEM user:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

Note the "sid" and "serial#" value returned from step 1.

How to use Dynamic Cursor in a PL/SQL

PL/SQL script to use a dynamic cursor.



CREATE OR REPLACE PROCEDURE XXX_dynamic_cursor (p_segment IN VARCHAR2)
IS
   v_sql            VARCHAR2 (2000) := NULL;
   v_segment        VARCHAR2 (50);
   l_segment_desc   VARCHAR2 (100);

   TYPE cur_typ IS REF CURSOR;

   c_seg_cur        cur_typ;
BEGIN
   v_sql :=
         'SELECT distinct ss.'
      || p_segment
      || ' , ffvl.description
             FROM XXXX_TEMP_GL ss,
                  fnd_id_flex_segments fifs,
                  fnd_flex_value_sets ffvs,
                  fnd_flex_values ffv,
                  fnd_flex_values_tl ffvl
            WHERE     ss.chart_of_accounts_id = fifs.id_flex_num
                  AND fifs.application_column_name = :p_segment AND fifs.flex_value_set_id = ffvs.flex_value_set_id
                  AND ffvs.flex_value_set_id = ffv.flex_value_set_id
                  AND ffv.flex_value_id = ffvl.flex_value_id
                  AND ss.'
      || p_segment
      || '   = ffv.flex_value
                  order by 1';

   OPEN c_seg_cur FOR v_sql USING p_segment;

   LOOP
      FETCH c_seg_cur
      INTO v_segment, l_segment_desc;

      EXIT WHEN c_seg_cur%NOTFOUND;
      DBMS_OUTPUT.put_line (v_segment || l_segment_desc);
   -- process row here
   END LOOP;

   CLOSE c_seg_cur;
END;
/

Useful SQL queries for support

Useful SQL queries for support

1. Delete a Concurrent Program from back end.


BEGIN
   apps.fnd_program.remove_from_group (
      program_short_name    => 'CONCURRENT_PROGRAM_SHORT_NAME',
      program_application   => 'PROGRAM_APPLICATION_SHORT_NAME',
      request_group         => 'REQUEST_GROUP_NAME',
      group_application     => 'REQUEST_GROUP_APPICATION');
   fnd_program.delete_program ('CONCURRENT_PROGRAM_SHORT_NAME', 'PROGRAM_APPLICATION_SHORT_NAME');
   fnd_program.delete_executable ('EXECUTABLE_SHORT_NAME', 'EXECUTABLE_APPLICATION_SHORT_NAME');
   COMMIT;
END;


2. To get the Concurrent requests details :


SELECT PARENT_REQUEST_ID, request_id, PHASE_CODE, STATUS_CODE, ARGUMENT_TEXT, req.logfile_name,req.outfile_name,
         ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE, RESP.RESPONSIBILITY_NAME, PGM.CONCURRENT_PROGRAM_NAME, FUS.USER_NAME
FROM APPS.FND_CONCURRENT_REQUESTS REQ, APPS.FND_CONCURRENT_PROGRAMS PGM, APPS.FND_RESPONSIBILITY_VL RESP, APPS.FND_USER FUS
WHERE REQ.CONCURRENT_PROGRAM_ID = PGM.CONCURRENT_PROGRAM_ID
AND PGM.APPLICATION_ID = REQ.PROGRAM_APPLICATION_ID
AND REQ.RESPONSIBILITY_ID = RESP.RESPONSIBILITY_ID
AND REQ.REQUESTED_BY = FUS.USER_ID
--AND PGM.CONCURRENT_PROGRAM_NAME = 'CONCURRENT_PROGRAM_SHORT_NAME'
--and req.request_id = REQUEST_ID
and FUS.USER_NAME = 'XXXXX'
order by req.request_id desc

3. Add Responsiblity to a User from backend :


BEGIN
   fnd_user_pkg.addresp (username         => 'USER_NAME',
                         resp_app         => 'RESPONSIBLITY_APP_SHORT_NAME',
                         resp_key         => 'RESPONSIBILITY_KEY',
                         security_group   => 'STANDARD',
                         description      => 'Auto Assignment',
                         start_date       => SYSDATE - 10,
                         end_date         => SYSDATE + 1000);
   COMMIT;
   DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
            ' Responsibility is not added due to '
         || SQLCODE
         || SUBSTR (SQLERRM, 1, 100));
      ROLLBACK;
END;

4. Active Users with Active responsiblities:


SELECT fu.user_name,
       frv.responsibility_name,
       TO_CHAR (furgd.start_date, 'DD-MON-RRRR') "START_DATE",
       TO_CHAR (furgd.end_date, 'DD-MON-RRRR') "END_DATE"
  FROM fnd_user fu,
       fnd_user_resp_groups_direct furgd,
       fnd_responsibility_vl frv
 WHERE     fu.user_id = furgd.user_id
       AND furgd.responsibility_id = frv.responsibility_id
       AND furgd.end_date IS NULL
--       AND fu.user_name = :user_name
       AND furgd.start_date <= SYSDATE
       AND NVL (furgd.end_date, SYSDATE + 1) > SYSDATE
       AND fu.start_date <= SYSDATE
       AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE
       AND frv.start_date <= SYSDATE
       AND NVL (frv.end_date, SYSDATE + 1) > SYSDATE
       AND FRV.RESPONSIBILITY_NAME = 'Application Developer'
       ORDER BY FRV.RESPONSIBILITY_NAME ;

5.Request Group of a Concurrent Program :


SELECT fa.application_short_name,
       frg.request_group_name,
       fe.execution_file_name,
       fe.executable_name
  FROM fnd_request_group_units frgu,
       fnd_concurrent_programs fcp,
       fnd_request_groups frg,
       fnd_executables fe,
       fnd_application fa
 WHERE     frgu.request_unit_id = fcp.concurrent_program_id
       AND frgu.request_group_id = frg.request_group_id
       AND fe.executable_id = fcp.executable_id
       AND FRG.application_id = fa.application_id
       AND fe.executable_name = '&CP_EXEC_NAME';

Enable Diagnostics in Oracle apps

How to enable Oracle apps Diagnostics-> Examine, for certain users?

Steps 1

Navigate to System Administrator responsibility> Profile> System>

  

Steps 2

Enter profile name: Utilities:Diagnostics

Enter Application User for whom you want to enable Diagnostics-> Examine

Steps 3

Give Yes at User level and Save the Changes

Note –

You can set Yes at Site level also if you want to enable this option for all Oracle application users

Steps 4

Again navigate to System Administrator responsibility> Profile> System>

Enter profile name: Hide Diagnostics menu entry

Enter Application User for whom you do not want to hide Diagnostics menu entry

Steps 5

Give No at User level and Save the Changes

Note –

You can set No at Site level also if you do not want to hide menu entry option for all Oracle application users

Steps 6

Congratulations you have successfully enabled Diagnostics-> Examine

Logout from Oracle Application and login again. Now can see Diagnostics-> Examine option

How To Add System Administrator Responsibility To A User from backend

Most of the time oracle apps developers are given only limited access to the front end application for development, in such cases you would like to add a responsibility from back end.

By using the below PL/SQL script/code you can add any responsibility to a user.

Syntax:

BEGIN

   fnd_user_pkg.addresp (username         => 'SXMARGAM',

                                        resp_app         => 'SYSADMIN',

                                        resp_key         => 'SYSTEM_ADMINISTRATOR',

                                        security_group   => 'STANDARD',

                                        description      => 'Auto Assignment',

                                        start_date       => SYSDATE - 10,

                                        end_date         => SYSDATE + 1000);

   COMMIT;

   DBMS_OUTPUT.put_line ('Responsibility Added Successfully');

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (‘Responsibility is not added due to ' || SQLCODE || SUBSTR (SQLERRM, 1, 100));

      ROLLBACK;

END;