Find out the Patch Level

Find out the Patch Level:-
------------------------------

You may need to find-out the patch level In some cases. Specially, this may required when you have created the tar with Oracle corp. The following statement is mostly run by DBA

select patch_level, application_name
from fnd_product_installations fpi
, fnd_application_tl fat
where patch_level is not null
and fpi.application_id = fat.application_id
order by application_name;

How to Select the Nth highest / lowest value from a table

How to Select the Nth highest / lowest value from a table:-

I am posting this script as lot of my friends have asked about this. They are lot of ways to do this. I am showing you in two best ways.

Note:- In 'N' place, provide your Nth number you want to findout.

How to Select the Nth highest value from a table:-
-------------------------------------------------

select level, max(sal) from scott.emp
where level = '&n'
connect by prior (sal) > sal
group by level;

How to select the Nth lowest value from a table:-
-------------------------------------------------------
select level, min(sal) from scott.emp
where level = '&n'
connect by prior (sal) < style="font-weight: bold;">N th Top Salary
-------------------

select a.ename,a.sal
from emp a
where n = (select count(distinct(b.sal))
from emp b where a.sal <= b.sal) N th Least Salary
---------------------
select a.ename,a.sal
from emp a
where n = (select count(distinct(b.sal))
from emp b
where a.sal >= b.sal)

udump path

To know the udump path:-
------------------------------

When the trace is ON from the Front end. The trace file is stored in some location on the server.There is a way to findout from the front end. But, there is a easy way to know from the backend as well with SELECT statement.

Use the following statement to know from the udump path:-
-----------------------------------------------------------

SELECT name,value from v$parameter WHERE name like 'user_dump_dest'

Compile all the objects in given schema

To compile all the objects in given schema :-
--------------------------------------------------

You may have the requirement to compile all the invalid objects. in particular schema like 'APPS' or 'SCOTT' etc. This can be done in may ways. There is option to compile the Invalid objects in APPS schema in Admin utility I have documented about the Adadmin utility in the Unix section. Check out the Unix for the Adadmin utility.

begin
SYS.UTL_RECOMP.RECOMP_PARALLEL('N', 'Schema_name');
end;
/

To compile all of the invalid objects owned by APPS
-------------------------------------------------------------

Note: must use UPPER case on the schema name

begin
dbms_utility.compile_schema('APPS');
end;
/

concurrent program attached to which responsibility

findout which concurrent program attached to which all responsibility:-
-------------------------------------------------------------------------------------

You know the concurrent program name, but you do not know to what all responsibilities it is attached to. In that case, you can use the following select statement to know the responsibilities names to which your concurrent program is attached.

select responsibility_name
from fnd_responsibility_tl rsp_tl, fnd_responsibility fr, --fnd_request_groups frg,
fnd_request_group_units frgu, fnd_concurrent_programs_tl fcpt
where rsp_tl.responsibility_id = fr.responsibility_id
--and frg.request_group_id = fr.request_group_id
and fr.request_group_id = frgu.request_group_id
and fcpt.concurrent_program_id = frgu.request_unit_id
and upper(fcpt.USER_CONCURRENT_PROGRAM_NAME) = upper('concurrent program name');

Example:-

select responsibility_name
from fnd_responsibility_tl rsp_tl, fnd_responsibility fr, --fnd_request_groups frg,
fnd_request_group_units frgu, fnd_concurrent_programs_tl fcpt
where rsp_tl.responsibility_id = fr.responsibility_id
--and frg.request_group_id = fr.request_group_id
and fr.request_group_id = frgu.request_group_id
and fcpt.concurrent_program_id = frgu.request_unit_id
and upper(fcpt.USER_CONCURRENT_PROGRAM_NAME) = upper('ALLOracleTech Test Program');

Invoker or Definer of package

To know whether the package is INVOKER or DEFINER In APPS Schema:-
-------------------------------------------------------------------------------------

You know package name and you want to know whether it is definer or Invoker from the select statement then you can know from the following satement.

Know more about the AUTHID

SELECT dbo.object_name,
(DECODE(SIGN(bitand(options,16)),1,'INVOKER','DEFINER')) "authid"
FROM dba_objects dbo,
sys.PROCEDURE$ p
WHERE p.obj# = dbo.object_id
AND dbo.object_name = 'package_name'
AND dbo.object_type = 'PACKAGE'
AND dbo.owner = 'APPS'

Example:-
-----------

SELECT dbo.object_name,
(DECODE(SIGN(bitand(options,16)),1,'INVOKER','DEFINER')) "authid"
FROM dba_objects dbo,
sys.PROCEDURE$ p
WHERE p.obj# = dbo.object_id
AND dbo.object_name = 'ASO_APR_WF_INT'
AND dbo.object_type = 'PACKAGE'
AND dbo.owner = 'APPS'

Check the version of the file

1) To check the version of file:-
--------------------------------

There might be so some file exisits in many Oracle Apps versions. For different version, code in the file might be different. Oracle Corp track this changes in the file with the version number of the file. We may need this information something when we raising the tar with oracle corp.

The following script will help you finding the version of any file which is executed for your Oracle Application version.

CREATE OR REPLACE PROCEDURE alloracletech_ver(file_name VARCHAR2) AS
l_file_id NUMBER;
l_file_version_id NUMBER;
l_version NUMBER;
l_file_name varchar2(20);
l_app_short_name varchar2(10);
BEGIN
l_file_name := trim(upper(file_name));
select file_id,app_short_name into l_file_id,l_app_short_name from ad_files
where upper(filename) = l_file_name and rownum =1;
select file_version_id into l_file_version_id from ad_check_files
where file_id= l_file_id and rownum = 1;
select version INTO l_version from ad_file_versions
where file_version_id=l_file_version_id
and file_id=l_file_id and rownum = 1;
dbms_output.put_line('version for the file '||file_name||' : '||l_version);
--insert into v_file_table values (file_name,l_version,l_app_short_name );
commit;
END alloracletech_ver;
/

begin
alloracletech_ver('file_name');
end;

Example:-

>set serverout on

begin
file_ver('POXGCHGS.pls');
end;
/