Friday, February 4, 2011

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

  1. 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
  1. Go to that path (directory)
            i.e. cd /amer/oracle/vis51/vis51comn/java
  1. Find available files
            ls -al
  1. 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

No comments:

Post a Comment