Important Application related queries

To check The concurrent request running
set pages 66
set line 132
-- spool progs1.lst
col user_name format a20 word_wrapped
column ProgName format a25 word_wrapped
column requestId format 9999999999
column StartDate format a20 word_Wrapped
column OS_PROCESS_ID format a6
column ETime format 99999999 word_Wrapped
col sid format 99999 word_Wrapped
select sess.sid, oracle_process_id OS_PROCESS_ID,
fusr.description user_name ,
fcp.user_concurrent_program_name progName,
to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate,
request_id RequestId,
(sysdate - actual_start_date)*24*60*60 ETime
from fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_user fusr,
v$session sess
where fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.language = 'US'
and fcr.phase_code = 'R'
and fcr.status_code = 'R'
and fcr.requested_by = fusr.user_id
and fcr.oracle_session_id = sess.audsid (+)
-- and p.addr = sess.paddr
order by 5 DESC;

Patch Consolidated Info from one databse of another:
ad_applied_patches
ad_patch_runs
ad_patch_drivers
ad_appl_tops
SELECT b.applications_system_name instance_name, d.patch_name, d.patch_type,
c.patch_abstract patch_description, a.patch_top,
a.end_date applied_date
FROM apps.ad_patch_runs@db_link_name a,
apps.ad_appl_tops@db_link_name b,
apps.ad_patch_drivers@db_link_name c,
apps.ad_applied_patches@db_link_name d
WHERE a.appl_top_id = b.appl_top_id
AND a.patch_driver_id = c.patch_driver_id
AND c.applied_patch_id = d.applied_patch_id
AND a.patch_top NOT LIKE '%autobuild%'
ORDER BY a.end_date ASC;

To compare the patches in 2 instances using minus:
create db link in second instance and , then execute in first instance:
set lines 100
col INSTANCE_NAME for a15
col PATCH_NAME for a10
col PATCH_TOP for a40
col APPLIED_DATE for a15
SELECT b.applications_system_name instance_name, d.patch_name,
a.patch_top,
a.end_date applied_date
FROM apps.ad_patch_runs a,
apps.ad_appl_tops b,
apps.ad_patch_drivers c,
apps.ad_applied_patches d
WHERE a.appl_top_id = b.appl_top_id
AND a.patch_driver_id = c.patch_driver_id
AND c.applied_patch_id = d.applied_patch_id
AND a.patch_top NOT LIKE '%autobuild%'
ORDER BY a.end_date ASC;
minus
SELECT b.applications_system_name instance_name, d.patch_name, d.patch_type,
c.patch_abstract patch_description, a.patch_top,
a.end_date applied_date
FROM apps.ad_patch_runs@db_link_name a,
apps.ad_appl_tops@db_link_name b,
apps.ad_patch_drivers@db_link_name c,
apps.ad_applied_patches@db_link_name d
WHERE a.appl_top_id = b.appl_top_id
AND a.patch_driver_id = c.patch_driver_id
AND c.applied_patch_id = d.applied_patch_id
AND a.patch_top NOT LIKE '%autobuild%'
ORDER BY a.end_date ASC;

Script to find out the Status and Patch set level for any modules:
select decode(nvl(a.APPLICATION_short_name,'Not Found'),
'SQLAP','AP','SQLGL','GL','OFA','FA',
'Not Found','id 'to_char(fpi.application_id),
a.APPLICATION_short_name) apps,
decode(fpi.status,'I','Installed','S','Shared',
'N','Inactive',fpi.status) status,
fpi.product_version,
nvl(fpi.patch_level,'-- Not Available --') Patchset,
to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
and fpi.oracle_id = o.oracle_id(+)
order by 1,2;

DB Link:
create database link "db_link_name"
connect to
identified by
using '';
GRANT CREATE DATABASE LINK TO ;
select * from global_name;
select sysdate from dual
select * from global_name@
SELECT 'connect ' owner '/$' owner '_PWD ' ' ' 'drop database link ' db_link ';'
,DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) ';' from dba_db_links a;