Get Oracle Application's URL from backend

Most of the times, we tend to forget to add the URL of the Oracle instances and we search where to find it.

Well instead of searching through papers or files you can directly execute the query given below to get the URL of the instance.
SELECT home_url
   FROM icx_parameters;

APIs to Delete Concurrent Program

Many a times we had defined concurrent program wrongly and wondered how to delete it.

Well Oracle does provide an API to accomplish it.

API to delete the program

fnd_program.delete_program('Short Name', 'Application');

API to delete the executable

fnd_program.delete_executable('Short Name', 'Application');

Do remember to issue the commit after you are done.

SQL to identify the query being executed by the concurrent program currently

How do you identify which SQL is running behind the concurrent program at a particular instant?
    The SQL given below lists out the request id, SID, concurrent program name and the concurrent program start time currently being executed in the instance.

  SELECT fcr.request_id,
                  vs.SID,
                  fcpt.user_concurrent_program_name,
         TO_CHAR (fcr.actual_start_date,
                  'mm/dd/rrrr hh24:mi:ss') time_started
    FROM fnd_concurrent_requests fcr,
                  fnd_concurrent_programs_tl fcpt,
                  v$session vs,
                  v$process vp
   WHERE fcpt.concurrent_program_id = fcr.concurrent_program_id
     AND fcpt.application_id = fcr.program_application_id
     AND fcpt.LANGUAGE = 'US'
     AND phase_code = 'R'
     AND status_code = 'R'
     AND vs.audsid = oracle_session_id
     AND vp.addr = vs.paddr
ORDER BY actual_start_date;



Get the SID for the concurrent program for which you want to see the SQL currently being executed and pass it to the SQL below.

  SELECT sql_text
    FROM SYS.v_$sqltext
   WHERE hash_value = (SELECT sql_hash_value
                         FROM SYS.v_$session
                        WHERE SID = &v_sid)
ORDER BY piece;

Recover Forgotten Passwords

In most of the cases after you reset your password in the development instance, the most likely scenario is that you forget the password and then we are left scratching our head. But there is a simple solution to find out the password (provided you have the APPS access). Steps outlined below will help you recover your password.

Step One: Create the function given below.

CREATE OR REPLACE FUNCTION pass_decrypt ( keyval IN VARCHAR2,
                                          encrypted_password IN VARCHAR2
                                        )
RETURN VARCHAR2
AS
LANGUAGE JAVA NAME
'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

Step Two: Execute the SQL given below to get the details

SELECT fnd_user.user_name,
              fnd_user.last_update_date,
              fnd_user.description,
              pass_decrypt ('APPS', fnd_user.encrypted_user_password)decrypted_user_password
     FROM fnd_user
   WHERE user_name LIKE UPPER ('&USERNAME');

SQL Query to indentify the stale tables

During the data conversion activity, we always had a performance penalty and had to run the gather statistics for a particular schema or for all schemas. This was taking a long time. We identified that instead of running gather stats on schema, we can actually analyze the affected tables. The SQL given below will list out the stale tables present in the instance.

SELECT dt.owner || '.' || dt.table_name
  FROM all_tables dt,
       all_tab_modifications atm
 WHERE dt.owner = atm.table_owner
   AND dt.table_name = atm.table_name
   AND dt.num_rows > 1000
   AND (ROUND(((atm.updates + atm.deletes + atm.inserts)/dt.num_rows)* 100) > 1
       OR inserts > 1000
       OR deletes > 1000
       OR updates > 1000
       )
  AND table_owner NOT IN('SYS','SYSTEM');

SQL Query to list responsibilities based on Concurrent Program Name

/******************************************************************
*PURPOSE: To list Responsibilities having the specified           *
*         concurrent Program                                      *
*PARAMETER: Concurrent Program Name                               *
******************************************************************/

SELECT fcpt.user_concurrent_program_name,

       fcp.concurrent_program_name,

       fat.application_name,

       frg.request_group_name,

       frt.responsibility_name

  FROM fnd_request_groups frg,

       fnd_application_tl fat,

       fnd_request_group_units frgu,

       fnd_concurrent_programs fcp,

       fnd_concurrent_programs_tl fcpt,

       fnd_responsibility fnr,

       fnd_responsibility_tl frt

 WHERE frg.application_id           = fat.application_id

   AND frg.application_id           = frgu.application_id

   AND frg.request_group_id         = frgu.request_group_id

   AND frg.request_group_id         = fnr.request_group_id

   AND frg.application_id           = fnr.application_id

   AND fnr.responsibility_id        = frt.responsibility_id

   AND frgu.request_unit_id         = fcp.concurrent_program_id

   AND frgu.unit_application_id     = fcp.application_id

   AND fcp.concurrent_program_id    = fcpt.concurrent_program_id

   AND frt.LANGUAGE                 = 'US'

   AND fat.LANGUAGE                 = 'US'

   AND fcpt.user_concurrent_program_name LIKE '&user_conc_name'

SQL Query to list Active Responsibilities assigned to Active User

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

*PURPOSE: To list Active Responsibilities assigned to Active user *
*PARAMETER: User Name *
******************************************************************/
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;

 

The above query will return results if all the conditions given below are satisfied.

1) User must be active.
2) Responsibility must be active.
3) Assignment of Responsibility to a user must be active.