APPS Queries

1.To find the patch set level of an application (module):

For this the steps to be followed sequentially are enumerated as follows:

1. Get the application ID

select application_id , application_name from fnd_application_tl where application_name like <application_name>

for eg. if Application Name is ‘Oracle Quoting’

select * from fnd_application_tl where application_name like '%Oracle Quoting%'

2. Get the Patch Set Level for the application (field: PATCH_LEVEL).

Select * from fnd_product_installations where application_id = <application_id>

 For e.g. Application_id of Oracle Quoting is 880

Select * from fnd_product_installations where application_id = 880

2)         Finding all installed applications

The query to be used for obtaining this information is as follows:

SELECT application_name, application_short_name, decode (status, 'I', 'Installed', 'S', 'Shared', 'Not Installed') status, a.application_id

FROM fnd_application_all_view a, fnd_product_installations i

WHERE a.application_id = i.application_id

order by 1;

3)         To find the location of a file

If we know that a file beginning with hello and ending with a .c extension is located in one of the subdirectories of the current working directory, we can type:

find . -name "hello*.c" –print

find . -name "*pg.xml"  –print

4)         To find file version

In order to obtain the file version, the command to be run is as follows:

adident Header 'filename'

5)         Location of the .odf file

In order to check for the location of the .odf file, we need to check the following directory after patch application,

$PO_TOP/patch/115/odf

This file also exists in $PO_TOP/admin

6)         To get the file version of concurrent programs

For UNIX, the command is as follows:

---------

$strings -a $PO_TOP/bin/POXCON | grep '$Header'

For Windows NT

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

In the DOS prompt, we get into the directory path of RVCACR.exe and use the following command.

find \i RVCACR.exe "$Header"

7)         To find the file versions associated with .exe file

The relevant command is as follows:

cd $PO_TOP/bin

strings -a RVCTP | grep -i '$Header'

8)         Location of .pls files

The relevant command is as follows:

PO_TOP/ADMIN/SQL  

PO_TOP/patch/115/sql

AU_TOP/resource

9)         To find .lc file

1. Please go to FND_TOP/bin
2. Then type in the following command

            strings -a f60webmx | grep fdf | grep Head

            Here fdf in the grep can be replaced by the file initials

3. You will get a list of files, with their version,

             In this case you will get the version of "fdfdfu.lc"

10)       To find .lct file

The relevant command is as follows:

FND_TOP/patch/115/import

strings -a 'afsload.lct'| grep 'Header'

11)       To find .lpc file

.lpc are generally part of .exe files eg. rvtbm.lpc. The relevant command is as follows:

strings -a RVCTP | grep 'Header' | grep 'rvtbm'

This gives the version of rvtbm.lpc

12)       To find .pld file

These are stored as .pll files in AU_TOP/resource. The relevant command is as follows:

strings -a RCVCOTRX.pll | grep 'Header'

strings -a RCVRCERL.pll | grep 'Header'

find . -name poemp.odf

13)       To find .ppc file

.ppc are generally part of .exe files eg. inltpu.ppc. The relevant command is as follows:

cd $INV_TOP/bin

strings -a INVLIBR | grep -i inltpu

This will give the version of inltpu.ppc. Here INVLIBR is the exe file which has this .ppc file Therefore it is important to know which .exe holds this file also.

14)       To find JAVA Files version

1. Find where the JAVA_TOP is

            {How to find : env | grep 'JAVA_TOP'}

            This command will give you the path to JAVA_TOP

             e.g. JAVA_TOP=/amer/oracle/vis51/vis51comn/java

2. Go to that path (directory)

            i.e. cd /amer/oracle/vis51/vis51comn/java

3. Find available files

            ls -al

4. You'll find a file called apps.zip

            Here you have two options to get the version of ReassignmentRule.java

            A). string apps.zip | grep 'ReassignmentRule.java'

            B). adident Header apps.zip | grep 'ReassignmentRule'

            (Note : with adident, do not give the file extension .java)

            You will get the file version e.g. ReassignmentRule.java 115.xxx

15)       To find version of the Self Service Framework

Execute the following command to determine the version of OA.jsp :

ident $FND_TOP/html/OA.jsp

and

ident $OA_HTML/OA.jsp and

The version of OA.jsp in $FND_TOP should be the same as the one in $OA_HTML. 

Use following to figure out the version of the Framework installation,

OA.jsp Version	OA Framework Version	ARU Patch Number
115.36	5.7	<<Patch:2771817>>.
115.27	5.6	<<Patch:2278688>>.

16)       To check Locks on a Table

The relevant query is as follows:

Select object_id, session_id, oracle_username, os_user_name,

Process, locked_mode

From sys.v_$locked_object;

Select a.object_name, b.oracle_username

From all_objects a, v$locked_object b

Where a.object_id = b.object_id

And a.object_name like 'po%';

17)       Find an Object’s Type:

           

The relevant query is as follows:

SELECT object_type

FROM   user_objects

WHERE object_name = ‘<OBJECT_NAME>’;

18)       Check for Existence Of Any Object In The Database:

The relevant query is as follows:

SELECT owner, object_type

FROM     all_objects

WHERE  object_name = ‘<OBJECT_NAME>’;

ie. object_name = PO_HEADERS_ALL

            OWNER                        OBJECT_TYPE

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

            APPS_APPDEMO          SYONONYM

            PODEMO                      TABLE

19)       Check Database for Invalid Objects:

The relevant query is as follows:

SELECT owner, object_name, object_type

FROM     all_objects

WHERE  status = ‘INVALID’;

SELECT count(*)

FROM   from dba_objects

WHERE  status = ‘INVALID’;

20)       To Find Dependent Objects Of An Invalid Object:

The relevant query is as follows:

SELECT owner, object_type, object_name

FROM     dba_objects

WHERE  status = ‘INVALID’

AND       object_name IN (SELECT referenced_name

                                                               FROM    dba_dependencies

                                                               WHERE  name = ‘<INVALID_OBJECT_NAME>’);

This will bring results down only one level of dependencies.

21)       Finding Columns and Tables

The all_tab_columns is a useful table that can be queried for such information.

For example:

SELECT  table_name, column_name

FROM   all_tab_columns

WHERE column_name like 'PO_HEADER%’;

           

TABLE_NAME                                      COLUMN_NAME

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

PO_ACCRUAL_RECONCILE_TEMP        PO_HEADER_ID

PO_ACCRUAL_WRITE_OFFS               PO_HEADER_ID

INVFV_MOVEMENT_STATISTICS                  PO_HEADER_NUMBER

MTL_MOVEMENT_STATISTICS_V                 PO_HEADER_ID

FINANCIALS_PURGES_V                                 PO_HEADERS

You can also find all tables from,

select * from all_objects

where object_name like '%PO_HEADERS_ALL%'

and object_type = 'TABLE'

22)       Finding and Deleting Duplicate Rows

The following statement will find and display all duplicate rows in a table, except the row with the maximum rowid:

SELECT *

FROM <tableA> a

WHERE rowid  <>  (SELECT max(rowid)

FROM <tableB> b

WHERE a.<column1> = b.<column1>

AND a.<column2> = b.<column2> -- make sure all columns are compared

AND a.<column3> = b.<column3>;

Note:  Duplicate rows which contain only NULL values will not be identified by above statement.

23)       Get The Code For a View:

The relevant query is as follows:

SQL>     set long 10000

SELECT text

FROM    user_views

WHERE view_name = ‘<VIEW_NAME>’;

24)       Checking Version of Database Package:

The relevant query is as follows:

SELECT text

FROM     dba_source

WHERE  name = ‘<PACKAGE_NAME >’

AND        text LIKE ‘%Header:%’;

For example, look for the 2 files that make up the package “PO_INQ_SV’:

TEXT

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

/*   $Header:  POXPOVPS.pls   80.1.7016.2  98/02/24   20:00:59  porting ship $   */

/*   $Header:  POXPOVPB.pls   80.3.7016.4  98/07/01   16:47:41  porting ship $   */

Notice the results give us the file names that make up the Header/Spec and Body of the package. The B and the S at the end of the 7 letters indicate the Body and Spec file.

25)       How to Determine the File Name for a Package

The relevant query is as follows:

Select text from dba_source where name = 'xxx_xxx_xxx' and text like '%$Header:%';

or

strings -a aprcvmtb.pls | grep 'CREATE'

This will return a string of the package name like:

CREATE OR REPLACE PACKAGE BODY AP_RECT_MATCH_PKG AS

26)       View Code for a Package:

The relevant query is as follows:

SQL>     set long 10000

SELECT text

FROM    user_source

WHERE name = ‘<PACKAGE_NAME>’;

27)       Display Package Errors:

The relevant query is as follows:

SELECT text

FROM    dba_errors

WHERE name = ‘<PACKAGE_NAME>’;

28)       Check to see what Indexes are on a Table:

The relevant query is as follows:

SELECT index_name

FROM    all_indexes

WHERE table_name = ‘<TABLE_NAME>’;

29)       Check to see what Columns are Indexed on a Table:

The relevant query is as follows:

SELECT column_name, index_name

FROM     all_ind_columns

WHERE  table_name = ‘<TABLE_NAME>’;

U = unique index

N = non-unique index

30)       Find Triggers on a Table:

The relevant query is as follows:

SELECT trigger_name

FROM   all_triggers

WHERE table_name = ‘<TABLE_NAME>”;

31)       Find Directory where Trace Files are Stored:

The relevant query is as follows:

SELECT value

FROM     v$parameter

WHERE  name = ‘user_dump_dest’;

User_dump_dest  is an initialization parameter defined in the init.ora file.

32)       Check for Multiple/Duplicate Logins

The relevant query is as follows:

1. Get the person_id for the employee

    select person_id     from per_people_f

    where last_name like &LASTNAME

2. Now use the person_id in the following SQL

    select count(*) from wf_users

    where orig_system_id = &PERSON_ID

    and status = 'ACTIVE'

If count is greater than one, you got duplicates

33)       Obtaining Org_Id and Setting Context

Please launch the applications and then use Help -> Diagnostics ->

Examine and then select $Profile in block and then org_id as field

Get this value and then set the context using SQL:

SQL> Exec dbms_application_info.set_client_info(&org_id)

34)       To check if multi-org is setup

The relevant query is as follows:

Select multi_org_flag from FND_PRODUCT_GROUPS;

35)       To find out details of concurrent request submitted with certain conditions

The relevant query is as follows:

Select request_id, req.CONCURRENT_PROGRAM_ID, prog.DESCRIPTION, Request_date, printer, number_of_copies, usr.user_name 

from fnd_concurrent_requests req,  fnd_concurrent_programs_tl prog, fnd_user usr

where printer <> 'noprint'

and requested_start_date > '14-AUG-06'

and status_code = 'C'

and number_of_copies > 0

and prog.CONCURRENT_PROGRAM_ID = req.CONCURRENT_PROGRAM_ID

and prog.language = 'US'

and req.requested_by = usr.user_id

order by request_date desc