How to Get Oracle E-Bussiness Suite Tables list

You can use the following select statement to get the Oracle E-Bussiness Suite Tables List for each application:

SELECT A.APPLICATION_ID,
       A.APPLICATION_NAME,
       A.APPLICATION_SHORT_NAME,
       A.DESCRIPTION APPLICATION_DESCRIPTION,
       A.BASEPATH,
       A.PRODUCT_CODE,
       P.TABLE_NAME,
       P.USER_TABLE_NAME,
       P.DESCRIPTION
  FROM FND_TABLES P,
       FND_APPLICATION_VL A
 WHERE A.APPLICATION_ID = P.APPLICATION_ID
Advertisements

SQL Statement to Get Session Information Which Used any Database Object

To get the session information which used any database objects you can use the following sql statement:

SELECT S.SID,
       S.SERIAL#,
       S.USERNAME,
       TO_CHAR(S.LOGON_TIME,'MMDDYYYY:HH24:MI') LOGON_TIME,
       TYPE,
       STATUS,
       PROCESS,
       SQL_ADDRESS,
       SQL_HASH_VALUE,
       P.OPTIMIZER_MODE,
       P.HASH_VALUE,
       P.ADDRESS,
       P.CPU_TIME,
       P.ELAPSED_TIME,
       P.SQL_TEXT
  FROM V$SQLAREA P,
       V$SESSION S
 WHERE S.SQL_HASH_VALUE = P.HASH_VALUE
   AND S.SQL_ADDRESS    = P.ADDRESS
   AND S.USERNAME       IS NOT NULL
   AND UPPER(P.SQL_TEXT) like '%'||UPPER(:P_OBJ_NAME)||'%'

Oracle E-Business Suite Show Active Database Sessions Details (Machine, Program, Module, Username, Action,…… )

You can use the following SQL statement to get the full session information (SID, Serial#, Machine, Program, Module, Username, Responsibility,……….).

SELECT S.AUDSID        "AUDSID"  ,
       S.SID           "SID"     ,
       S.SERIAL#       "SERIAL#" ,
       I.INSTANCE_NAME "Instance",
       S.MACHINE       "Machine" ,
       S.TERMINAL      "Terminal",
       S.PROGRAM       "Program" ,
       S.MODULE        "Module"  ,
       S.USERNAME      "Username",
       S.ACTION        "Action"  ,
       S.OSUSER        "OS User" ,       
       S.PROCESS       "Process" ,
       S.STATUS        "Status",
       S.LOGON_TIME    "Logon Time"
  FROM GV$SESSION  S,
       GV$INSTANCE I
 WHERE S.AUDSID > 0
   AND S.INST_ID = I.INST_ID;

SQL to get the profile options list using specific criteria (profile option name, user profile option name, application name, application short name, level type {user, resp, app, site})

You can use the following statement to get a list of the profile option according to your criteria (profile option name, user profile option name, application name, application short name, level type {user, resp, app, site})

SELECT A.APPLICATION_NAME,
       A.APPLICATION_SHORT_NAME,
       K.PROFILE_OPTION_NAME,
       K.USER_PROFILE_OPTION_NAME,
       K.DESCRIPTION,
       K.SQL_VALIDATION,
       CASE WHEN K.SITE_UPDATE_ALLOWED_FLAG ='Y'
            THEN 'Yes'
            ELSE 'No' END ALLOWED_SITE_LEVEL,    
       CASE WHEN K.APP_UPDATE_ALLOWED_FLAG  ='Y'
            THEN 'Yes'
            ELSE 'No' END ALLOWED_APP_LEVEL,     
       CASE WHEN K.RESP_UPDATE_ALLOWED_FLAG ='Y'
            THEN 'Yes'
            ELSE 'No' END ALLOWED_RESP_LEVEL,                         
       CASE WHEN K.USER_UPDATE_ALLOWED_FLAG ='Y'
            THEN 'Yes'
            ELSE 'No'  END ALLOWED_USER_LEVEL,
       CASE WHEN K.SERVER_UPDATE_ALLOWED_FLAG ='Y'
            THEN 'Yes'
            ELSE 'No'  END ALLOWED_SERVER_LEVEL,
       CASE WHEN K.ORG_UPDATE_ALLOWED_FLAG ='Y'
            THEN 'Yes'
            ELSE 'No'  END ALLOWED_ORGANIZATION_LEVEL           
  FROM FND_PROFILE_OPTIONS_VL K,
       FND_APPLICATION_VL     A
 WHERE K.APPLICATION_ID = A.APPLICATION_ID
   AND UPPER(K.PROFILE_OPTION_NAME)      LIKE '%'||UPPER(:P_PROFILE_OPTION_NAME)||'%'
   AND UPPER(K.USER_PROFILE_OPTION_NAME) LIKE '%'||UPPER(:P_USER_PROFILE_OPTION_NAME)||'%'
   AND UPPER(A.APPLICATION_NAME)         LIKE '%'||UPPER(:P_APPLICATION_NAME)||'%'
   AND UPPER(A.APPLICATION_SHORT_NAME)   LIKE '%'||UPPER(:P_APPLICATION_SHORT_NAME)||'%'
   AND (
        (UPPER(:P_LEVEL_TYPE) LIKE '%USER%' AND K.USER_UPDATE_ALLOWED_FLAG   ='Y')
        OR 
        (UPPER(:P_LEVEL_TYPE) LIKE '%RESP%' AND K.RESP_UPDATE_ALLOWED_FLAG   ='Y')
        OR
        (UPPER(:P_LEVEL_TYPE) LIKE '%APP%'  AND K.APP_UPDATE_ALLOWED_FLAG    ='Y')
        OR
        (UPPER(:P_LEVEL_TYPE) LIKE '%SITE%' AND K.SITE_UPDATE_ALLOWED_FLAG   ='Y')
        OR
        (UPPER(:P_LEVEL_TYPE) LIKE '%SERV%' AND K.SERVER_UPDATE_ALLOWED_FLAG ='Y')
        OR
        (UPPER(:P_LEVEL_TYPE) LIKE '%ORG%'  AND K.ORG_UPDATE_ALLOWED_FLAG    ='Y')
        OR
        (:P_LEVEL_TYPE IS NULL)        
       )
 ORDER BY A.APPLICATION_NAME, 
          K.PROFILE_OPTION_NAME

SQL to get profile option values list using specific criteria (profile option name, user profile option name, application name, application short name, level type {user, resp, app, site}, level name {user name, resp name, app name, site name})

You can use the following statement to get a list of the profile option values according to your criteria (profile option name, user profile option name, application name, application short name, level type {user, resp, app, site}, level name {user name, resp name, app name, site name})

SELECT P.PROFILE_LEVEL_TYPE,
       P.PROFILE_LEVEL_NAME,
       P.PROFILE_OPTION_NAME,
       P.USER_PROFILE_OPTION_NAME,
       P.DESCRIPTION,
       P.PROFILE_OPTION_VALUE,              
       P.APPLICATION_NAME,
       P.APPLICATION_SHORT_NAME,              
       P.SQL_VALIDATION,
       P.LAST_UPDATED_BY,
       P.LAST_UPDATE_DATE   
  FROM (SELECT A.APPLICATION_NAME,
               A.APPLICATION_SHORT_NAME,
               K.PROFILE_OPTION_NAME,
               K.USER_PROFILE_OPTION_NAME,
               K.DESCRIPTION,
               K.SQL_VALIDATION,
               V.LEVEL_ID,
               V.LEVEL_VALUE,
               CASE WHEN V.LEVEL_ID = 10001
                    THEN 'Site'
                    WHEN V.LEVEL_ID = 10002
                    THEN 'Application'
                    WHEN V.LEVEL_ID = 10003
                    THEN 'Responsiblity'
                    WHEN V.LEVEL_ID = 10004
                    THEN 'User' 
                    WHEN V.LEVEL_ID = 10005
                    THEN 'Server'                    
                    WHEN V.LEVEL_ID = 10006
                    THEN 'Organization' END PROFILE_LEVEL_TYPE,       
               CASE WHEN V.LEVEL_ID = 10001
                    THEN ''
                    WHEN V.LEVEL_ID = 10002
                    THEN (SELECT P.APPLICATION_NAME FROM FND_APPLICATION_VL P WHERE P.APPLICATION_ID = V.LEVEL_VALUE)
                    WHEN V.LEVEL_ID = 10003
                    THEN (SELECT P.RESPONSIBILITY_NAME FROM FND_RESPONSIBILITY_VL P WHERE P.RESPONSIBILITY_ID = V.LEVEL_VALUE)
                    WHEN V.LEVEL_ID = 10004
                    THEN (SELECT P.USER_NAME FROM FND_USER P WHERE P.USER_ID = V.LEVEL_VALUE) 
                    WHEN V.LEVEL_ID = 10005
                    THEN (SELECT P.NODE_NAME FROM FND_NODES P WHERE P.NODE_ID = V.LEVEL_VALUE)                    
                    WHEN V.LEVEL_ID = 10006
                    THEN (SELECT P.NAME FROM HR_OPERATING_UNITS P WHERE P.ORGANIZATION_ID = V.LEVEL_VALUE) END PROFILE_LEVEL_NAME,
               V.PROFILE_OPTION_VALUE,
               (SELECT P.USER_NAME FROM FND_USER P WHERE P.USER_ID = V.LAST_UPDATED_BY) LAST_UPDATED_BY,             
               V.LAST_UPDATE_DATE
          FROM FND_PROFILE_OPTION_VALUES V,  
               FND_PROFILE_OPTIONS_VL    K,
               FND_APPLICATION_VL        A
         WHERE K.APPLICATION_ID    = A.APPLICATION_ID
           AND V.PROFILE_OPTION_ID = K.PROFILE_OPTION_ID)  P 
 WHERE UPPER(P.PROFILE_LEVEL_TYPE)       LIKE '%'||UPPER(:P_PROFILE_LEVEL_TYPE)||'%'
   AND UPPER(P.PROFILE_LEVEL_NAME)       LIKE '%'||UPPER(:P_PROFILE_LEVEL_NAME)||'%'           
   AND UPPER(P.PROFILE_OPTION_NAME)      LIKE '%'||UPPER(:P_PROFILE_OPTION_NAME)||'%'
   AND UPPER(P.USER_PROFILE_OPTION_NAME) LIKE '%'||UPPER(:P_USER_PROFILE_OPTION_NAME)||'%'
   AND UPPER(P.APPLICATION_NAME)         LIKE '%'||UPPER(:P_APPLICATION_NAME)||'%'  
   AND UPPER(P.APPLICATION_SHORT_NAME)   LIKE '%'||UPPER(:P_APPLICATION_SHORT_NAME)||'%'        
 ORDER BY 1,2,3,7