--
-- Program          : Apps Health Checks
--                                                                     
-- Description      : ck_apps_health.sql
--                                                                    
-- Created By       : Lynne Paulus
--                                                                     
-- Change History   :                                                  
-- ............................................................ 
--        
-- ............................................................        


COLUMN db_name new_value dbname noprint
COLUMN today new_value runtime noprint

SET TERM OFF

SELECT name db_name FROM v$database;
SELECT TO_CHAR(sysdate,'YYYY/MM/DD HH24:MI') today FROM dual;

-- Spool file to desired location used to keep historical checks
SPOOL /tmp/ck_apps_health
 
SET pagesize 1000
SET linesize 80
COLUMN owner FORMAT a8
COLUMN object_type FORMAT a12
COLUMN object_name FORMAT a30    
COLUMN created FORMAT a9
COLUMN application_id on heading app_id FORMAT 99999
COLUMN profile_option_id on heading opt_id FORMAT 99999
COLUMN level_value on heading 'level|value' FORMAT 99999
COLUMN prof_value on heading 'Prof|value' FORMAT a15
COLUMN prof_name FORMAT a20 trunc
COLUMN user_prof_name FORMAT a40 trunc
COLUMN user_name FORMAT a10 trunc

PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT ++  Warning Section:    Should see no rows in this section
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT                                                                       
COLUMN obj_name FORMAT a18
COLUMN own FORMAT a5 

--    General Warnings:

PROMPT Confirm not in Maintenance Mode  - should see no rows
SELECT
   pot.user_profile_option_name profile_name
,v.PROFILE_OPTION_VALUE
,v.LEVEL_ID
,v.LAST_UPDATE_DATE
FROM
         apps.fnd_profile_options o
       , apps.fnd_profile_options_tl pot
       , apps.fnd_profile_option_values v
WHERE
         o.profile_option_name = pot.profile_option_name
AND      o.profile_option_id = v.profile_option_id (+)
AND      o.application_id    = v.application_id (+)
AND      nvl(v.level_id, 10001) = 10001
AND      pot.user_profile_option_name = 'Applications Maintenance Mode'
AND      v.profile_option_value != 'NORMAL'
/

--
--     Oracle EBusiness User Security
--
PROMPT User Security AND access Issues section:
PROMPT
PROMPT Show any logins whose password is NOT SET to expire in 90 days
COLUMN user_name FORMAT a20
SELECT user_id, user_name, password_lifespan_days
FROM   apps.fnd_user
WHERE  
       nvl(password_lifespan_days, 0) != 90
AND    user_id > 1007    -- exclude seeded special 11i logins
/
     
PROMPT Non-DBAs who have System Administrator or Applic Develop Responsibility
-- Modify to suit users and responsibilities 
SELECT
     fur.end_date,
     sysdate today,
     substr(fr.responsibility_name,1,35) Responsibility,
     fu.user_name 
FROM 
     apps.fnd_user fu,
     apps.fnd_user_resp_groups_direct fur,
     apps.fnd_responsibility_vl fr,
     apps.fnd_application_vl fa   
WHERE
     fur.user_id = fu.user_id
AND  fu.user_name NOT IN ('SYSADMIN')
AND  fur.responsibility_application_id    = fa.application_id
AND  fur.responsibility_application_id    = fr.application_id
AND  fur.responsibility_id = fr.responsibility_id
AND  fu.end_date is null    -- only show active users
AND  fur.end_date is null   -- only show active sysadmin resp assignment
AND  fr.responsibility_name in ('System Administrator','Application Developer',
                                'FICO Sys Admin Conversion')
/

--  Check for terminated employees
PROMPT Terminated employees who still have an active login to Oracle Apps

SELECT DISTINCT(fu.user_name)
, to_char(ppos.actual_termination_date, 'DD-Mon-YY') term_date
, period_of_service_id
FROM apps.fnd_user fu
,    apps.per_all_people_f papf
,    apps.per_periods_of_service ppos
WHERE fu.employee_id = papf.person_id
AND   papf.person_id = ppos.person_id
AND   fu.end_date IS NULL
AND   ppos.period_of_service_id = (SELECT MAX(period_of_service_id)
                                   FROM per_periods_of_service
                                   WHERE person_id = papf.person_id)
AND   ppos.actual_termination_date < TRUNC(SYSDATE)
/

PROMPT Profile Check Section:
PROMPT
--     Confirm Examine function is password protected in Production
--     If profile is Y can use Examine without password PROMPT so unprotected
PROMPT Check 'Utilities:Diagnostics' Profile Settings at non-user levels for Examine

SELECT                                                                                        
         pot.user_profile_option_name prof_name              
       , substr(v.profile_option_value, 1,5) prof_value      
       , v.level_id                                          
       , v.level_value                                       
FROM                                                         
         apps.fnd_profile_options o                               
       , apps.fnd_profile_options_tl pot                          
       , apps.fnd_profile_option_values v                         
WHERE                                                        
         o.profile_option_name = pot.profile_option_name     
AND      o.profile_option_id = v.profile_option_id (+)       
AND      o.application_id    = v.application_id (+)          
AND      pot.user_profile_option_name like 'Utilities%Diag%' 
AND      v.profile_option_value != 'N'      -- N is protected setting
ORDER BY pot.user_profile_option_name                        
/                                                            

PROMPT  User Level 'Utilities:Diagnostics' Profile Settings for Examine
 
SELECT                                                       
         v.profile_option_id                                 
       , pot.user_profile_option_name prof_name              
       , substr(v.profile_option_value, 1,5) prof_value      
       , v.level_id                                          
       , v.level_value                                       
       , fu.user_name                                        
FROM                                                         
         apps.fnd_profile_options o                               
       , apps.fnd_profile_options_tl pot                          
       , apps.fnd_profile_option_values v                         
       , apps.fnd_user fu                                         
WHERE                                                        
         o.profile_option_name = pot.profile_option_name     
AND      o.profile_option_id = v.profile_option_id (+)       
AND      o.application_id    = v.application_id (+)          
AND      nvl(v.level_id, 1001) = 10004                       
AND      v.level_value = fu.user_id                          
AND      pot.user_profile_option_name like 'Utilities%Diag%' 
AND      v.profile_option_value != 'N'      -- N is protected setting
order by pot.user_profile_option_name                        
/                                                            

--     End User Security Warnings

--     Recent changes to objects with Custom Direct Changes - Research
--     Add standard Apps objects we Customized and will be lost when patched

PROMPT Recent changes to objects with Custom Direct Changes - usually None
PROMPT
PROMPT Any ADP views that have been changed recently

SELECT  owner, object_name, created, last_ddl_time 
FROM    dba_objects                                                        
WHERE   object_name like Upper('%ADP%')                                   
AND     owner = 'APPS'                                                      
AND     object_type = 'VIEW'                                                
AND     last_ddl_time > (sysdate -7)                                        
ORDER BY  object_type, owner, object_name                               
/

PROMPT Any changes to iExpense pkg where we have custom direct chg
SELECT  owner, object_name, created, last_ddl_time
FROM    dba_objects
WHERE   object_name = 'AP_WEB_DB_HR_INT_PKG'
AND     owner = 'APPS'
AND     object_type in ('PACKAGE SPEC', 'PACKAGE')
AND     last_ddl_time > (sysdate -7)
ORDER BY  object_type, owner, object_name
/

PROMPT Any OTL/iExpense views changed recently
SELECT owner, object_name, created, last_ddl_time 
FROM   dba_objects                                                        
WHERE  object_name in ('AP_WEB_PA_PROJECTS_V')   
AND    owner = 'APPS'                                                      
AND    object_type = 'VIEW'                                                
AND    last_ddl_time > (sysdate -7)                                        
ORDER BY  object_type, owner, object_name                               
/

PROMPT CRM custom view changed recently; usually should see none
SELECT owner, object_name, created, last_ddl_time 
FROM   dba_objects                                                        
WHERE  object_name in ('CS_SR_UWQ_GROUP_V')
AND    owner = 'APPS'                                                      
AND    object_type = 'VIEW'                                                
AND    last_ddl_time > (sysdate -5)                                        
ORDER BY  object_type, owner, object_name                               
/
--     End Custom Direct Changes Section

--     Module Specific Warning Section
PROMPT Module Specific Warnings:

--     Module: Receivables

PROMPT Check ar_journal_interim_all for rows left by ARRGTA report errors
PROMPT Search for 'AR Journal Entries Report' that ended in Error
PROMPT Confirm with Support okay truncate table ar_journal_interim_all 
PROMPT when have exclusive access or Con Mgr stopped
SELECT 
     request_id
,    date_created
,    COUNT(*)
FROM ar.ar_journal_interim_all 
GROUP BY request_id, date_created
/

-- Module: Projects
PROMPT  Check pa table for rows left by PAXMGURA report errors
PROMPT  Search for 'MGT: Unbilled Receivables Aging' that ended in Error
PROMPT  Check with Support to see if can truncate table 
PROMPT  pa_unbilled_rec_reporting as PA if any rows are orphaned rows
PROMPT  Do when have exclusive access or Con Mgr stopped

SELECT COUNT(*)
FROM   pa.pa_unbilled_rec_reporting
HAVING COUNT(*) > 1
/

--   End Module Specific Section

--   Std RDBMS Checks Section:

--   DB Security Warnings:
--   Check for any locked db user accounts 
COLUMN username FORMAT a14
COLUMN default_tablespace   heading 'DEFAULT TS' FORMAT a10
COLUMN temporary_tablespace heading 'TEMP'    FORMAT a4 
COLUMN profile FORMAT a13 
COLUMN account_status heading 'ACCT|STAT' FORMAT a6 trunc
PROMPT List Locked Oracle schema accounts 
PROMPT
SELECT  username 
       ,to_char(created, 'DD-MON-YY') created
       ,default_tablespace
       ,temporary_tablespace
       ,account_status
       ,to_char(lock_date, 'MM/DD/YY HH24:MI') lock_date
       ,profile
FROM  dba_users
WHERE lock_date IS NOT NULL  
AND   account_status NOT LIKE 'EXPIRED%'
/

PROMPT Check whether UNDO datafiles have autoextended
PROMPT May want to resize datafiles down lower after big AutoExtend

COLUMN sname   FORMAT          a12 heading 'Tablespace'          justify c
COLUMN ssize   FORMAT   999,999.99 heading 'Mb|Total'            justify c
COLUMN extents FORMAT     9,999    heading 'Free|Exts'           justify c
COLUMN mbytes  FORMAT     9,999.99 heading 'Largest|Free Ext'    justify c
COLUMN tbytes  FORMAT   999,999.99 heading 'Mb|Avail'            justify c
COLUMN mused   FORMAT   999,999.99 heading 'Mb|Used'             justify c
COLUMN pct     FORMAT       990.99 heading 'Percent|Free'        justify c
SELECT
  total.tablespace_name            sname,
  COUNT(free.bytes)                extents,
  max(free.bytes)/1048576          mbytes,
  total.ts_size/1048576            ssize,
  (total.ts_size/1048576) - (sum(free.bytes/1048576)) mused,
  sum(free.bytes)/1048576          tbytes,
  sum(free.bytes)/total.ts_size * 100  pct
FROM
   dba_free_space              free
,    (SELECT tablespace_name,
          sum(bytes) ts_size
      FROM   dba_data_files
      GROUP BY tablespace_name)  
                               total
WHERE
    total.tablespace_name = free.tablespace_name (+)
AND total.tablespace_name = 'APPS_UNDOTS1'
AND total.ts_size/1048576 > 2400   -- greater than 2,400 MB then autoextended
GROUP BY
  total.tablespace_name,
  total.ts_size
/

PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT                END Warning Section
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT                                                                       
PROMPT                                                                       

PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT               Begin General Health Checks Section
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT                                                                       
PROMPT                                                                       

-- Revise prompt as persistent invalid objects increase or reduce
PROMPT Invalid APPS objects, at least 4: MRP_
COLUMN object_name FORMAT a30    
SELECT 
     owner, object_name, object_type
,    to_char(created, 'DD-MON-YY') created
,    status
FROM dba_objects
WHERE Owner = 'APPS'                   
AND status = 'INVALID'
ORDER BY object_name
/

PROMPT Invalid objects owned by custom schema XXFI Account
-- Use any custom schemas here
SELECT 
      owner, object_name, object_type, created, status
FROM  dba_objects
WHERE Owner = 'XXFI'                   
AND   status = 'INVALID'
ORDER BY object_name
/

-- RDBMS Checks
PROMPT Check SYS audit table to determine number of failed DB logins 
SELECT 
     COUNT(*)
FROM sys.aud$ 
/

PROMPT List Expired schema accounts 
PROMPT
SELECT  username 
       ,to_char(created, 'DD-MON-YY') created
       ,default_tablespace
       ,temporary_tablespace
       ,account_status
       ,to_char(lock_date, 'MM/DD/YY HH24:MI') lock_date
       ,profile
FROM dba_users
WHERE lock_date is not null  
AND   account_status != 'OPEN'
/

PROMPT Usage Checks Section:

--     Check highest DB connections COUNT since last DB start 
--     Count variety of current Oracle connections  
PROMPT Current Oracle Connection Characteristics including highwater mark

COLUMN formsusers        HEADING "Forms connects"       FORMAT 99999
COLUMN selfservusers     HEADING "Self Service"         FORMAT 99999
COLUMN rundatetime       HEADING "TIME OF DAY"          FORMAT A16
COLUMN currsession       HEADING "Current Sess"         FORMAT 99999
COLUMN sesshighwater     HEADING "High Water"           FORMAT 99999
COLUMN jdbcthinclient    HEADING "JDBC Thin"            FORMAT 99999

SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI') rundatetime
--show currently logged in Forms connections, multiple connections per user 
--approximately half as many Forms users as DB forms connections
,(SELECT COUNT(*)
  FROM   apps.fnd_logins fl  -- only has Forms login rows
  ,      apps.fnd_user   fu
  ,      v$session       s
  ,      v$process       p 
  WHERE  fl.end_time                         IS NULL        
  AND    fl.user_id                          = fu.user_id(+)
  AND    fl.process_spid                     = p.spid        
  AND    fl.pid                              = p.pid
  AND    fl.serial#                          = p.serial#
  AND    p.addr                              = s.paddr
  AND    s.program                           IS NULL)            formsusers   
--
--
-- SelfServ Activity last 60 min based on icx_sessions
,(SELECT COUNT(*)
  FROM  apps.icx_sessions icx                                               
  ,     apps.fnd_user fu                                                    
  ,     apps.fnd_responsibility_vl frv                                      
  WHERE icx.user_id                         = fu.user_id(+)
  AND   icx.disabled_flag                   = 'N'
  AND   icx.last_connect                    > SYSDATE - 1/24 -- last hour
--Preferences
  AND   NVL(icx.responsibility_id,20873)    = frv.responsibility_id(+) 
  AND   icx.responsibility_application_id   = frv.application_id(+)           
-- only web self service responsibilities
  AND   frv.version(+) = 'W')                                    selfservusers 
--
--          
,(SELECT SESSIONS_CURRENT 
  FROM   v$license )                                             currsession
--
--
,(SELECT SESSIONS_HIGHWATER 
  FROM   v$license )                                             sesshighwater
--
--
,( SELECT COUNT(*) 
   FROM   sys.v_$session           
   WHERE  program = 'JDBC Thin Client')                          jdbcthinclient
FROM DUAL
/

PROMPT User Checks Section:
PROMPT
PROMPT Show users whose FND Login Account password has become INVALID (11.5.10.2)
COLUMN encrypted_password FORMAT a20
SELECT user_name
,      TO_CHAR(last_logon_date, 'DD-Mon-YY') last_logon
,      TO_CHAR(password_date, 'DD-Mon-YY') passwd_date
,      ENCRYPTED_USER_PASSWORD encrypted_password
,      TO_CHAR(end_date, 'DD-Mon-YY') end_date
FROM   apps.fnd_user
WHERE  encrypted_user_password = 'INVALID'      -- value when user is blocked
AND    user_name NOT IN ('ANONYMOUS', 'APPSMGR','XML_USER') --inactive seeded
ORDER BY user_name
/

--Show users whose mailpref is QUERY since causes Workflow Notification issues
PROMPT Show users whose mail preference is do not send
SELECT fu.user_name
FROM   apps.fnd_user_preferences fup
,      apps.fnd_user fu
WHERE  fup.preference_name = 'MAILTYPE'
AND    fup.preference_value = 'QUERY'
AND    fup.user_name = fu.user_name
AND    fu.end_date IS NULL
ORDER BY fu.user_name 
/

--     Profiles Section: 

PROMPT PROFILES Section: Check Selected PROFILE Settings:
PROMPT


COLUMN application_id on heading app_id FORMAT 99999
COLUMN profile_option_id on heading opt_id FORMAT 99999
COLUMN level_value on heading 'level|value' FORMAT 99999     
COLUMN prof_value on heading 'Prof|value' FORMAT a15 
COLUMN prof_name FORMAT a20 trunc
COLUMN user_prof_name FORMAT a40 trunc
COLUMN user_name FORMAT a10 trunc

PROMPT List Site level Profiles that have changed in last Month
SELECT 
         o.user_profile_option_name user_prof_name
       , substr(v.profile_option_value, 1,45) prof_value 
       , fu.user_name
       , to_char(v.last_update_date,'DD-Mon-YYYY') update_date
FROM     apps.fnd_profile_option_values v
       , apps.fnd_profile_options_vl o
       , apps.fnd_user fu
WHERE    o.profile_option_id = v.profile_option_id
AND      o.application_id    = v.application_id
AND      level_id = 10001
AND      v.last_updated_by = fu.user_id   
AND      (v.last_update_date >= sysdate - 30 -- updated in last 4 weeks 
          OR v.creation_date > sysdate -30)  -- created in the last 4 weeks
ORDER BY fu.user_name, o.user_profile_option_name
/

PROMPT User Level Profile Settings relating to Trace or Debug
--
COLUMN application_id on heading appl_id FORMAT 999999
COLUMN profile_option_id on heading prof_id FORMAT 99999
COLUMN level_value on heading 'level|value' FORMAT 99999     
COLUMN prof_value on heading 'Prof|value' FORMAT a5
COLUMN prof_name FORMAT a25 trunc
COLUMN user_name FORMAT a15

PROMPT List Profile options WHERE debug/trace is SET on
SELECT   
         v.profile_option_id
       , v.level_id
       , user_name
       , pot.user_profile_option_name prof_name
       , substr(v.profile_option_value, 1,45) prof_value 
FROM   
         apps.fnd_profile_options o
       , apps.fnd_profile_options_tl pot
       , apps.fnd_profile_option_values v
       , apps.fnd_user fu
WHERE    
         o.profile_option_name = pot.profile_option_name
AND      o.profile_option_id = v.profile_option_id (+)
AND      o.application_id    = v.application_id (+)
AND      level_id = 10004  -- user level option
AND      v.level_value = fu.user_id
AND      fu.end_date is null  -- only look at active logins
AND      nvl(v.profile_option_value, 'N') = 'Y'   
AND      (pot.user_profile_option_name like '%Trace%'   
         OR  pot.user_profile_option_name like '%Debug%')   
ORDER BY pot.user_profile_option_name
/

PROMPT Non-User Level Profile Settings relating to Trace or Debug
SELECT   
         v.profile_option_id
       , v.level_id
       , v.level_value
       , pot.user_profile_option_name prof_name
       , substr(v.profile_option_value, 1,45) prof_value 
FROM   
         apps.fnd_profile_options o
       , apps.fnd_profile_options_tl pot
       , apps.fnd_profile_option_values v
WHERE    
         o.profile_option_name = pot.profile_option_name
AND      o.profile_option_id = v.profile_option_id (+)
AND      o.application_id    = v.application_id (+)
AND      level_id != 10004                    -- not user level option
AND      nvl(v.profile_option_value, 'N') = 'Y'   
AND      (pot.user_profile_option_name like '%Trace%'   
         OR  pot.user_profile_option_name like '%Debug%')   
order by pot.user_profile_option_name
/

PROMPT Module Specific Checks Section:
PROMPT 
PROMPT Module: Order Mgmt

PROMPT 'Check OE Processing Msgs for Number AND types of rows'
PROMPT If too many rows check whether 'Message Purge' Con Pgm is scheduled
SELECT 
 request_id
, substr(opmt.message_text,1,50) msg
, COUNT(*)
FROM ont.oe_processing_msgs opm
, ont.oe_processing_msgs_tl opmt
WHERE opm.transaction_id = opmt.transaction_id
group by request_id,opmt.message_text
/

PROMPT Module: IProcurement 
PROMPT
PROMPT Check for stuck Web requisitions. These get cleaned by weekly Conc Pgm
PROMPT 'Purge System Saved Requisition', when older than 30 days
PROMPT No reason to take action unless notice one user with many stuck reqs

COLUMN segment1 FORMAT a9
COLUMN user_name FORMAT a15
COLUMN authorization_status FORMAT a15 on heading auth_status

SELECT prh.segment1, prh.creation_date, prh.created_by
,      fu.user_name
,      prh.authorization_status
FROM apps.po_requisition_headers_all prh
,    apps.fnd_user fu
WHERE authorization_status = 'SYSTEM_SAVED'
AND   prh.created_by = fu.user_id
order by fu.user_name, prh.segment1
/

PROMPT Module: General Ledger
PROMPT Monitor number of gl consolidation tables that may be orphaned.
-- There are also synonyms to cleanup when/if these tables get dropped
-- this bug might be fixed in future or else could cleanup periodically
-- but cannot drop without researching each table with GL functional users
SELECT owner, object_name, object_type
,      to_char(created, 'DD-MON-YY') created
,      status
FROM   dba_objects
WHERE  object_name like 'GL_CONS_INTERFACE%'
AND    object_type = 'TABLE'
ORDER BY  created desc
/

PROMPT Module: Sales Online
PROMPT Monitor volume of data in Sales Leads audit log tables CANNOT TRUNCATE
SELECT COUNT(*) FROM apps.as_leads_log;
SELECT COUNT(*) FROM apps.as_lead_lines_log;
PROMPT Monitor volume of data in Fullfillment history table
SELECT COUNT(*) FROM apps.JTF_FM_REQUEST_HISTORY;

PROMPT End Module specific Checks Section
PROMPT 

SPOOL OFF
SET TERM OFF
SET TERM ON
CLEAR COLUMNS
EXIT
