Query To Get FND User Details

Download(fnduserstat.sql)

REM +======================================================================+
REM                    
REM File Name: fnduserstat.sql
REM 
REM Description:
REM   Query To Get FND User Details
REM   
REM Notes:
REM   Usage: sqlplus <apps_user/apps_passwd> @fnduserstat.sql 
REM 
REM Input Required:
REM    USER_NAME : FND User Name
REM   
REM +======================================================================+

ACCEPT USER_NAME PROMPT "Enter FND User Name: ";

SET lines 180
COL USER_NAME for a15
COL LAST_UPDATED_BY for a15
COL user_status for a12
COL password_status for a20
SELECT user_id, user_name, start_date, end_date, CASE
   WHEN end_date IS NULL OR end_date > SYSDATE
     THEN 'ACTIVE'
   ELSE 'INACTIVE'
    END user_status, CASE
   WHEN encrypted_user_password = 'INVALID'
     THEN 'LOCKED'
   ELSE 'OPEN'
    END password_status, TO_CHAR (last_update_date, 'DD-MON-YYYY HH24:MI:SS') "LAST_UPDATE_DATE", 
    (SELECT fu.user_name
   FROM apps.fnd_user fu
  WHERE fu.user_id = (a.last_updated_by)) "LAST_UPDATED_BY"
  FROM apps.fnd_user a
 WHERE user_name = upper('&USER_NAME');

undef USER_NAME

Query To Check FND Languages Installed in Oracle EBS

Download(fndlangs.sql)

REM +======================================================================+
REM                    
REM File Name: fndlangs.sql
REM 
REM Description:
REM   Query To Check FND Languages Installed in Oracle EBS 
REM   
REM Notes:
REM   Usage: sqlplus <apps_user/apps_passwd> @fndlangs.sql 
REM   
REM +======================================================================+

clear columns
set lines 180
set pages 50
col LANGUAGE_CODE for a15
col NLS_LANGUAGE for a20
col ISO_LANGUAGE for a15
col INSTALLED_FLAG for a15
SELECT LANGUAGE_CODE,NLS_LANGUAGE, ISO_LANGUAGE,INSTALLED_FLAG 
FROM APPS.FND_LANGUAGES 
WHERE INSTALLED_FLAG IN ('B','I');

Query To Get FND NODES Details

Download(fndnodes.sql)

REM +======================================================================+
REM                    
REM File Name: fndnodes.sql
REM 
REM Description:
REM   Query To Get FND NODES Details 
REM   
REM Notes:
REM   Usage: sqlplus <apps_user/apps_passwd> @fndnodes.sql 
REM   
REM +======================================================================+

clear columns
set lines 180
set pages 50
col NODE_NAME for a15 WORD_WRAPPED
COL SUPPORT_DB for a12
COL SUPPORT_CP for a12
COL SUPPORT_ADMIN for a15
COL SUPPORT_FORMS for a15
COL SUPPORT_WEB for a12
COL HOST for a15 WORD_WRAPPED
Col STATUS for a10
col DOMAIN for a20 WORD_WRAPPED
select NODE_NAME,SUPPORT_DB,SUPPORT_CP,SUPPORT_ADMIN,SUPPORT_WEB,SUPPORT_FORMS,STATUS,HOST,DOMAIN 
from apps.fnd_nodes;

Query To Check Oracle EBS Version, OATM Status and other Basic Details

Download(ebsbasic.sql)

REM +======================================================================+
REM                    
REM File Name: ebsbasic.sql
REM 
REM Description:
REM   Query To Check Oracle EBS Version, OATM Status and other Basic Details
REM   
REM Notes:
REM   Usage: sqlplus <apps_user/apps_passwd> @ebsbasic.sql 
REM   
REM +======================================================================+

clear columns
set lines 180
 set pages 50
 col RELEASE_NAME for a12
 col MULTI_ORG_FLAG for a15
 col APPLICATIONS_SYSTEM_NAME for a25
 col "IS_NEW_TS_MODE(OATM_ENABLED)" for a30
 select APPLICATIONS_SYSTEM_NAME,RELEASE_NAME,version "DB_VERSION",MULTI_ORG_FLAG,IS_NEW_TS_MODE "IS_NEW_TS_MODE(OATM_ENABLED)" 
 from apps.fnd_product_groups,v$instance;

Query To Get Oracle SID Details for a Given Concurrent Request ID

Download(sidfrmreqid.sql)

REM +======================================================================+
REM                    
REM File Name: sidfrmreqid.sql
REM 
REM Description:
REM   Query To Get Oracle SID Details for a Given Concurrent Request ID
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @sidfrmreqid.sql 
REM 
REM Input Required:
REM    REQID : Concurrent Request ID
REM   
REM +======================================================================+

SELECT s.inst_id,a.request_id,s.sid,s.serial#,c.spid
  FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s
    WHERE a.request_id in ('&REQID')
   AND s.paddr = c.addr
         AND a.oracle_process_id = c.spid
         AND a.phase_code = UPPER ('R');

Query To Get Concurrent Request ID for a Given Oracle SID

Download(reqidfrmsid.sql)

REM +======================================================================+
REM                    
REM File Name: reqidfrmsid.sql
REM 
REM Description:
REM   Query To Get Concurrent Request ID for a Given Oracle SID
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @reqidfrmsid.sql 
REM 
REM Input Required:
REM    SID : Oracle SID
REM   
REM +======================================================================+

SELECT s.inst_id,a.request_id,s.sid,s.serial#,c.spid
  FROM apps.fnd_concurrent_requests a, gv$process c, gv$session s
   WHERE s.sid in ('&SID')
  AND s.paddr = c.addr
  AND a.oracle_process_id = c.spid
  AND a.phase_code = UPPER ('R');

Query To Analyze a Concurrent Request

REM +======================================================================+
REM                    
REM File Name: analyzereq.sql
REM 
REM Description:
REM   SQL Script To Analyze a Concurrent Request
REM   
REM Notes:
REM   Usage: sqlplus <apps_user/apps_passwd> @analyzereq.sql request_id
REM
REM   This Query Requires Request ID as an Input to Analyze
REM
REM +======================================================================+

Click on below link to download and view SQL Script

Download(analyzereq.sql)