Some frequently used queries for oracle apps developers

Get all the views that refer a particular table:

SELECT o.object_name, o.status, o.last_ddl_time

  FROM SYS.dba_objects o

 WHERE o.object_type = 'VIEW'

   AND EXISTS (

              SELECT 'x'

                   FROM SYS.dba_dependencies d

           WHERE d.referenced_type = 'TABLE'

                 AND d.TYPE = 'VIEW'

                    AND d.owner = 'APPS'

             AND d.NAME = o.object_name

                 AND d.referenced_name LIKE '<TABLE_NAME>');

Get all the tables with the column name:

SELECT

  TABLE_NAME

FROM

  all_tab_columns

WHERE

  column_name = '<COLUMN_NAME>'

Get SQL help from the Data base itself:

If you need any syntax of a sql command or its details, you don’t need to search for it in google. what you just need to do is use the below sql command from the data base

In iSQL*Plus, click the Help button to display iSQL*Plus help. Or Use the below command line help system. In some DB system schema may not be available, then use sys.help instead.

select *

from   system.help

where  upper(topic)=upper('string')

Find from which responsibility a concurrent program can be run:

SELECT distinct

  a.user_concurrent_program_name,

  a.description,

  request_group_name,

  e.responsibility_name

FROM

  fnd_concurrent_programs_tl a,

  fnd_request_groups b,

  fnd_request_group_units c,

  fnd_responsibility d,

  fnd_responsibility_tl e

WHERE

  a.concurrent_program_id = c.request_unit_id     

and b.request_group_id = c.request_group_id

and b.request_group_id = d.request_group_id

and d.responsibility_id = e.responsibility_id

and a.application_id = b.application_id

and b.application_id = c.application_id

and d.application_id = e.application_id

and a.concurrent_program_id = :p_conc_program_id

Find all running concurrent requests with their run times:

The below query is useful for analyzing concurrent program run times and identifying their performance

select request_id,

         fcpt.user_concurrent_program_name,

         completion_text,

         actual_start_date,

         actual_completion_date,

         to_date((actual_completion_date - actual_start_date), 'HH:MM:SS') duration

 from fnd_concurrent_requests fcr,

         fnd_concurrent_programs fcp,

         fnd_concurrent_programs_tl fcpt

where fcr.concurrent_program_id = fcp.concurrent_program_id

    and fcp.concurrent_program_id = fcpt.concurrent_program_id

    and fcr.actual_start_date > sysdate - 1

    order by actual_completion_date - actual_start_date desc

Search all packages for a line of code:

SELECT  *

FROM    ALL_SOURCE

WHERE   TYPE IN ('PACKAGE', 'PACKAGE BODY')

AND     TEXT LIKE '%XXXX%'

The below table shows all the data base object information:

select * from dba_objects where object_name like '%HEADER%' and object_type = 'TABLE'

Sequal to get the concurrent program file name (procedure/pkg name) based on the concurrent program name

select fct.user_concurrent_program_name,

          fcp.concurrent_program_name,

          fe.execution_file_name,

          fl.meaning execution_method

from fnd_concurrent_programs_tl fct,

        fnd_concurrent_programs fcp,

        fnd_executables fe,

        fnd_lookups fl

where

     upper(fct.user_concurrent_program_name) = upper('concurrent program')

and     fct.concurrent_program_id = fcp.concurrent_program_id

and     fe.executable_id = fcp.executable_id

and     fl.lookup_code = fe.execution_method_code

and     fl.lookup_type = 'CP_EXECUTION_METHOD_CODE'

List of all active workflow users and their roles

select  wu.name user_name,

        wr.name role_name

from    wf_users wu,

        wf_user_roles wur,

        wf_roles wr

where   wu.name = wur.user_name

and     wur.role_name = wr.name

and     wu.status = 'ACTIVE'

and     wr.status = 'ACTIVE'

and     wr.orig_system = 'WF_LOCAL_ROLES'

order by wu.name,

        wr.name

Concurrent requests raised in the last day

select request_id,

       decode(parent_request_id, -1, 'None') as parent_request_id,

       fcpt.user_concurrent_program_name,

       decode(  fcr.status_code,

                'A', 'Waiting',

                'B', 'Resuming',

                'C', 'Normal',

                'D', 'Cancelled',

                'E', 'Error',

                'F', 'Scheduled',

                'G', 'Warning',

                'H', 'On Hold',

                'I', 'Normal',

                'M', 'No Manager',

                'Q', 'Standby',

                'R', 'Normal',

                'S', 'Suspended',

                'T', 'Terminating',

                'U', 'Disabled',

                'W', 'Paused',

                'X', 'Terminated',

                'Z', 'Waiting') as status,       

       decode(  fcr.phase_code,

                'C', 'Completed',

                'I', 'Inactive',

                'P', 'Pending',

                'R', 'Running') as phase,

       fu.user_name,

       fr.responsibility_name,              

       completion_text,

       argument_text,

       request_date,

       requested_start_date,

       actual_start_date,

       actual_completion_date,

       fcp.concurrent_program_name,

       fe.executable_name,

       fe.execution_file_name

from   fnd_concurrent_requests fcr,

       fnd_concurrent_programs fcp,

       fnd_concurrent_programs_tl fcpt,

       fnd_executables fe,

       fnd_responsibility_vl fr,

       fnd_user fu

where  fcr.concurrent_program_id = fcp.concurrent_program_id

and    fcp.concurrent_program_id = fcpt.concurrent_program_id

and    fcp.executable_id = fe.executable_id

and    fcr.responsibility_id = fr.responsibility_id

and    fcr.requested_by = fu.user_id

and    fcr.request_date > sysdate - 1

--and    fcr.concurrent_program_id = XXXX

order by request_id desc