--
-- Program          : Apps Health Checks
--                                                                     
-- Description      : ck_apps_health.sql
--                   
-- Purpose          : Run a variety of Oracle Apps Health Checks. 
--                    Warning section at beginning should usually return no rows.
--                                                  
-- Created By       : Lynne Paulus
--                                                                     
-- Change History   :
-- ............................................................        
--
-- ........................................................................... 
SET linesize 80
SET space 1

COLUMN db_name new_value dbname noprint
COLUMN today new_value runtime noprint

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

SET pagesize 1000
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

--
--     Health Check Logic starts Here
--
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'
/

PROMPT Confirm fnd_oracle_userid table has correct value, see MetaLink #235634.1
PROMPT When this value is 0 it can cause ORA-1403 errors in CRM 

select oracle_username, install_group_num, read_only_flag
from apps.fnd_oracle_userid
where oracle_username = 'APPS'
and (install_group_num = 0   -- value should be 1
 or read_only_flag != 'U')   -- should be U
/

--
--     Oracle EBusiness User Security
--

PROMPT Warn of 11i login accounts that should not become locked out like SYSADMIN
PROMPT 
SELECT user_name
, TO_CHAR(last_logon_date, 'DD-Mon-YY') last_logon
, ENCRYPTED_USER_PASSWORD
, TO_CHAR(password_date, 'DD-Mon-YY') passwd_date
FROM   apps.fnd_user
WHERE ENCRYPTED_USER_PASSWORD = 'INVALID'
AND USER_NAME IN ('SYSADMIN')
ORDER BY user_name
/

PROMPT User Security AND Access Issues section:
PROMPT Show any logins whose password is NOT SET to expire in 90 days
COLUMN user_name FORMAT a20
COLUMN encrypted_password 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 other powerful Responsibilities

SELECT
     substr(fr.responsibility_name,1,35) Responsibility
    ,fu.user_name 
    ,fur.end_date
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 ('YOUR_DBA_NAME')                          
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  nvl(fur.end_date, sysdate+1) > sysdate  -- only show active resp assignment
AND  fr.responsibility_name in ('System Administrator'
                                ,'Application Developer'
                                ,'Application Developer Common Modules'
                                ,'CRM HTML Administration'
                                ,'FICO Sys Admin Conversion'
                                ,'FICO System Administration GUI' 
                                ,'Functional Administrator' -- can chg Profiles
                                ,'Functional Developer'
                                ,'Knowledge Administrator'    --use FICO instead
                                ,'Oracle Sales Administrator' --use FICO instead
                                ,'Sales Administrator'        --use FICO instead
                                ,'SFM System Administrator'
                                ,'TeleSales Administration' --use FICO instead
                               )        
/

COL emp_num format a7
COL per_serv_id format 999999

--  Check for terminated employees
PROMPT Terminated employees who still have an active login to Oracle Apps
SELECT DISTINCT(fu.user_name)
, papf.employee_number emp_num
, to_char(ppos.actual_termination_date, 'DD-Mon-YY') term_date
, period_of_service_id per_serv_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 Show users whose Responsibility roles have early end date in future
--     To remove, Use Define User Form toggle fnd_user end_date by 
--     adding end_date, commit, remove user end_date, commit. Some time lag
--     Remove this logic when Oracle no longer end dates resp roles early

select distinct user_name
     , user_end_date
from   apps.wf_local_user_roles
where  user_end_date >= sysdate  -- in future
and    user_end_date <= sysdate + 7 -- within next week
and    user_orig_system != 'WF_LOCAL_USERS'
and    to_char(user_end_date,'DD-MON-YYYY') != '31-DEC-4712'
order by user_end_date, user_name
/

-- Duplicate email addresses have security issues with workflow notifications
-- Two people should not have same email address since one user can get
-- another user's email for instance when doing password reset 
COL email_address FORMAT a50
PROMPT Show fnd_users who have same email address
SELECT 
  fu.email_address
FROM   apps.fnd_user fu
WHERE fu.email_address is not null
AND end_date is null
GROUP BY 
         fu.email_address
HAVING count(*) > 1
/

PROMPT Show active fnd_users tied to the same HR employee (shows person_id)
SELECT
  fu.employee_id 
, count(*)
FROM   apps.fnd_user fu
WHERE fu.employee_id   is not null -- this is person_id in per_all_people_f
AND  fu.end_date is null
GROUP BY fu.employee_id
HAVING count(*) > 1
/

PROMPT Workflow Section:
PROMPT

--Show users whose mailpref is QUERY since causes Workflow Notification issues
PROMPT Users whose mail preference is 'do not send' since causes WF notif issues
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 
/

PROMPT Show any wf adhoc roles that have wrong notification_preference 
SELECT notification_preference
     , COUNT(*)
FROM apps.wf_local_roles
WHERE  orig_system = 'WF_LOCAL_ROLES'
AND notification_preference != 'MAILHTML'
GROUP BY notification_preference
/

PROMPT Show if workflow mailer setting PROCESSOR_READ_TIMEOUT_CLOSE has changed
PROMPT to something other than Y.  Should not see any rows
SELECT p.parameter_name
,      v.last_update_date
,      u.user_name
FROM apps.fnd_svc_comp_param_vals v
,    apps.fnd_svc_comp_params_b   p
,    apps.fnd_svc_components      c
,    apps.fnd_user                u
WHERE c.component_type  = 'WF_MAILER' 
AND   c.component_id    = v.component_id   
AND   v.parameter_id    = p.parameter_id 
AND   p.parameter_name  = 'PROCESSOR_READ_TIMEOUT_CLOSE'
AND   v.parameter_value <> 'Y'
AND   v.last_updated_by = u.user_id
/

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 Profile 'Utilities:Diagnostics' for Examine set to unprotected
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                                       
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 means password  protected
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 -- user level Profile 
AND      v.level_value = fu.user_id                          
AND      pot.user_profile_option_name like 'Utilities%Diag%' 
AND      v.profile_option_value != 'N'         
order by pot.user_profile_option_name                        
/                                                            

--  List Profile options for personalization related settings
--
PROMPT User level profile for Personalization related Profiles
SELECT   
         user_name
       , pot.user_profile_option_name prof_name
       , v.profile_option_value 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      pot.user_profile_option_name in
                                   (
                                    'AMS : Oracle Personalization Enabled'
                                   ,'Create Seeded Personalizations'
                                   ,'FND: Personalization Region Link Enabled'
                                   ,'FND: Personalization Seeding Mode'
                                   ,'HR: Enable User Personalization'
                                   ,'OSO : Enable Admin Personalization Feature'
                                   ,'Personalize Self-Service Defn'
                                   )
ORDER BY   
         pot.user_profile_option_name 
       , user_name
/

prompt Find Personalization related profiles at other levels
prompt
prompt ***  Site level  ***

select t.user_profile_option_name, profile_option_value
from apps.fnd_profile_options o
    ,apps.fnd_profile_option_values v
    ,apps.fnd_profile_options_tl t
where o.profile_option_id = v.profile_option_id
   and o.application_id = v.application_id
   and profile_option_value = 'Y'
   and start_date_active <= SYSDATE
   and nvl(end_date_active,SYSDATE) >= SYSDATE
   and o.profile_option_name = t.profile_option_name
   and level_id=10001
   and (upper(t.user_profile_option_name) like '%PERSONALIZ%'
   and upper(t.user_profile_option_name) not like '%PERSONALIZ%WYSIWYG%'
       )
   order by user_profile_option_name;

prompt
prompt ***  Application level  ***

select t.user_profile_option_name, profile_option_value, application_name
from apps.fnd_profile_options o
    ,apps.fnd_profile_option_values v
    ,apps.fnd_profile_options_tl t
    ,apps.fnd_application_tl a
where o.profile_option_id = v.profile_option_id
   and o.application_id = v.application_id
   and profile_option_value = 'Y'
   and start_date_active <= SYSDATE
   and nvl(end_date_active,SYSDATE) >= SYSDATE
   and o.profile_option_name = t.profile_option_name
   and a.application_id = level_value
   and level_id=10002
   and t.language = a.language
   and upper(t.user_profile_option_name) like '%PERSONALIZ%'
   order by user_profile_option_name, application_name
/

prompt
prompt ***  Responsibility level  ***

select t.user_profile_option_name, profile_option_value,
       responsibility_name
from apps.fnd_profile_options o
    ,apps.fnd_profile_option_values v
    ,apps.fnd_profile_options_tl t
    ,apps.fnd_responsibility_tl r
where o.profile_option_id = v.profile_option_id
   and o.application_id = v.application_id
   and profile_option_value = 'Y'
   and start_date_active  <= SYSDATE
   and nvl(end_date_active,SYSDATE) >= SYSDATE
   and o.profile_option_name = t.profile_option_name
   and responsibility_id = level_value
   and level_id=10003
   and t.language = r.language
   and upper(t.user_profile_option_name) like '%PERSONALIZ%'
   order by user_profile_option_name, responsibility_name
/
COLUMN prof_value on heading 'Prof|value' FORMAT a5
PROMPT  Confirm self service diagnostics are not turned on 
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
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 'FND%Diag%'
AND      nvl(v.level_id, 1001) != 10004  -- skip user level options
ANd      v.profile_option_value != 'N'  -- when Y then can use ss diagnostics
order by pot.user_profile_option_name
/

PROMPT Confirm only dba and sysadmin users have access to SS Diagnostics 
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  -- user level options
AND      fu.user_name NOT IN ('YOUR_DBA_NAME')
AND      v.level_value = fu.user_id
AND      pot.user_profile_option_name like 'FND%Diag%'
order by pot.user_profile_option_name
/

PROMPT User level profile for 'ICX: Numeric characters' set to 10.000,00
PROMPT Should be 10,000.00
SELECT
         user_name
       , pot.user_profile_option_name prof_name
       , substr(v.profile_option_value, 1,20) 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      pot.user_profile_option_name = 'ICX: Numeric characters'
AND      v.profile_option_value = ',.'  -- this value causes problems in OTL
/

PROMPT Look for Password Profiles set at user level
SELECT
         v.profile_option_id
       , pot.user_profile_option_name prof_name
       , fu.user_name
       , substr(v.profile_option_value, 1,5) prof_value
       , v.level_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      nvl(v.level_id, 1001) = 10004
AND      v.level_value = fu.user_id
AND      pot.user_profile_option_name like '%Password%'
order by pot.user_profile_option_name
/

--     End User Security Warnings

PROMPT Warnings about Concurrent Programs and Concurrent Manager
column user_concurrent_program_name on heading Program format a32 trunc
column concurrent_program_name on heading Program format a10 trunc
column description             on heading Description format a35 trunc
column user_name format a10

PROMPT Show any requests by FISCHED that ended in error in last 5 days
PROMPT
select  request_id Request
        ,fu.user_name User_Name
        ,to_char(cr.actual_start_date, 'DD HH24:MI') When_start
        ,to_char(cr.actual_completion_date, 'DD HH24:MI') When_end
	,cp.concurrent_program_name Program
        ,phase_code
        ,status_code
from
	apps.fnd_concurrent_programs cp,
        apps.fnd_user fu,
	apps.fnd_concurrent_requests cr
where
	cr.concurrent_program_id   = cp.concurrent_program_id
and     cr.program_application_id  = cp.application_id
and 	cr.actual_completion_date is not null
and     cr.requested_by = fu.user_id
and     fu.user_name = 'FISCHED'
and     cr.status_code = 'E'   -- requests that ended in error
and trunc(cr.actual_start_date) >= trunc(sysdate-5) -- started recently
order by cr.actual_start_date desc
/

PROMPT Show conc programs that have trace enabled. Normally should be none
PROMPT
-- To Turn off: Go to Define Con Pgm form and uncheck the Enable Trace field
col prog_name format a35
col enable_trace format a15
SELECT concurrent_program_name prog_name
     , enable_trace
     , last_update_date
FROM apps.fnd_concurrent_programs fcp
WHERE NVL(enable_trace,'N') = 'Y'  
/

PROMPT Look for programs that should usually be disabled based on description
PROMPT
SELECT
	cp.concurrent_program_name
,	cp.user_concurrent_program_name
,       cp.description
FROM 
	apps.fnd_concurrent_programs_vl cp
WHERE
	UPPER(cp.description) like '%USUALLY%DISABLED%'
AND     cp.enabled_flag = 'Y'   -- should be N to be disabled
/

-- Confirm Alert Periodic Scheduler is pending so that alerts will run

PROMPT Confirm Alert Periodic Scheduler is okay, should see no rows   
select 'PROBLEM: No pending Alert Periodic Scheduler request' from dual
where not exists (
        select
        cp.concurrent_program_name Program,
	cr.request_id Request,
	phase_code S,
	status_code C,
	cr.argument1||', '||
	cr.argument2||', '||
	cr.argument3||', '||
	cr.argument4||', '||
	cr.argument5||', '||
	cr.argument6||', '||
	cr.argument7||', '||
	cr.argument8 Arguments
   from
	apps.fnd_concurrent_requests cr,
	apps.fnd_concurrent_programs cp
   where
           cr.concurrent_program_id = cp.concurrent_program_id
   and     cp.application_id >= 0  -- force on index leading edge
   and     cr.program_application_id        = cp.application_id
   and	   cp.concurrent_program_name = 'ALEPPE' -- Alert scheduler con pgm
   and     cr.phase_code = 'P'
   and     cr.status_code != 'H'                -- not on hold
   )
/

--     End Con Program section

--     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 PA Gen Draft Rev parm we want disabled 
col short_name format a10
col executable_name format a10
col parameter format a10

SELECT   SUBSTR(fcp.user_concurrent_program_name,1,60)   concurrent_program_name
,        fcp.concurrent_program_name                      short_name          
,        SUBSTR(fe.executable_name,1,25)                  executable_name
,        SUBSTR(fl_em.meaning,1,18)                       execution_method
,        LPAD(TO_CHAR(fdfcu.column_seq_num,'fm990'),4)    " SEQ"
,        fdfcu.end_user_column_name                       parameter
,        RPAD(fdfcu.enabled_flag,8)                       "ENABLED?"
,        SUBSTR(ffvs.flex_value_set_name,1,45)            validation_value_set 
,        SUBSTR(ffvs.description,1,75)                    value_set_description 
,        SUBSTR(fl_dt.meaning,1,13)                       default_type
,        RPAD(fdfcu.required_flag,5)                      "REQD?"
--       If the default value is populated and more than 80 chars
--       then trim it to 80 for purposes of this report.
,        RPAD(DECODE(SIGN(NVL(LENGTH(fdfcu.default_value),0) - 80)
              ,      +1, SUBSTR(fdfcu.default_value,1,80) || '...'
              ,      fdfcu.default_value),83)             default_value
,        RPAD(fdfcu.display_flag,10)                      "DISPLAYED?"
FROM     apps.fnd_application              fa
,        apps.fnd_application_tl           fat
,        apps.fnd_concurrent_programs_vl   fcp
,        apps.fnd_executables              fe
,        apps.fnd_descr_flex_column_usages fdfcu
,        apps.fnd_descr_flex_col_usage_tl  fdfcut
,        apps.fnd_flex_value_sets          ffvs
,        apps.fnd_lookups                     fl_dt
,        apps.fnd_lookups                     fl_em
WHERE    USERENV('LANG')                     = fat.language
AND      fat.application_id                  = fa.application_id
AND      fa.application_id                   = fcp.application_id
AND      fcp.concurrent_program_name = 'PARGDR'                -- PA program
AND      fdfcu.end_user_column_name  = 'Release Draft Revenue' -- this parameter
AND      fdfcu.enabled_flag  = 'Y'  -- has become enabled when it should not
AND      fcp.executable_application_id       = fe.application_id
AND      fcp.executable_id                   = fe.executable_id
AND      fcp.application_id                  = fdfcu.application_id
AND      '$SRS$.'
         || fcp.concurrent_program_name      = fdfcu.descriptive_flexfield_name
AND      fdfcu.application_id                = fdfcut.application_id
AND      fdfcu.descriptive_flexfield_name    = fdfcut.descriptive_flexfield_name
AND      fdfcu.descriptive_flex_context_code = fdfcut.descriptive_flex_context_code
AND      fdfcu.application_column_name       = fdfcut.application_column_name
AND      USERENV('LANG')                     = fdfcut.language
AND      fdfcu.flex_value_set_id             = ffvs.flex_value_set_id
AND      fdfcu.default_type                  = fl_dt.lookup_code (+)
AND      'FLEX_DEFAULT_TYPE'                 = fl_dt.lookup_type (+)
AND      fcp.execution_method_code           = fl_em.lookup_code (+)
AND      'CP_EXECUTION_METHOD_CODE'          = fl_em.lookup_type (+)
ORDER BY fcp.user_concurrent_program_name
,        fdfcu.column_seq_num
/

--     End Custom Direct Changes Section

--     Module Specific Warning Section

PROMPT Module Specific Warnings:

PROMPT Module = Applimation

PROMPT Confirm selected Applimation DB triggers that cause problems are off

select count(*)
from dba_triggers
where trigger_name like 'AM_WF_LOCAL_USER_ROLES%'
and status != 'DISABLED'  -- these triggers cause severe perf problems when on
/

--     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
/

PROMPT Check HZ_PARTIES.STATUS incorrectly set to I when acct is A
COLUMN cstatus HEAD "hz_cust_accounts" FORMAT A20
COLUMN pstatus HEAD "hz_parties"  FORMAT A20

SELECT rac.customer_number,
       rac.party_id,
       ca.status cstatus,
       p.status  pstatus,
       p.last_update_date,
       u.user_name
FROM   apps.ra_customers rac,
       apps.hz_cust_accounts ca,
       apps.hz_parties p,
       apps.fnd_user u
WHERE  ca.party_id=rac.party_id
AND    p.party_id=rac.party_id
AND    u.user_id=p.last_updated_by
AND    ca.status='A'
AND    p.status='I'
/

-- Module: Projects
PROMPT No rows should return with 'Y' which would mean we have begun to run
PROMPT Summarization or Utilization in old or new model
select count(*)
from apps.pa_forecast_item_details
where pji_summarized_flag  = 'Y'
/
select count(*)
from apps.pa_forecast_item_details
where util_summarized_code = 'Y'
/

PROMPT Check for duplicate PA resource attributes 
select resource_id, person_id 
from apps.pa_resource_txn_attributes txn 
where
 txn.person_id in (select  person_id from apps.pa_resource_txn_attributes
                   group by person_id 
                   having count(*)>1  -- emp should not have > 1 resource row
                   and person_id is not null)
 and (txn.resource_id not in 
                  (select resource_id from apps.pa_resources_denorm
                  )
     )
/

prompt PA Forecast Item assignments and resources that have an end date other 
prompt than Friday.  Expect zero rows returned 
SELECT count (*)               
FROM   apps.pa_forecast_items
WHERE  TO_CHAR(global_exp_period_end_date,'d') != 6  -- non-Friday dates
AND    delete_flag = 'N'
/

PROMPT Show any projects stuck in wf_status_code but no wf activity rows still
SELECT ppa.project_id
,      ppa.name
,      ppa.wf_status_code
FROM   apps.pa_projects_all ppa
WHERE  wf_status_code = 'IN_ROUTE'
AND NOT EXISTS (SELECT NULL
                FROM  apps.wf_item_activity_statuses_v wfias
                WHERE item_type                  = 'PAPROWF'
                AND   wfias.activity_status_code = 'ACTIVE'
                AND   ppa.segment1               = wfias.user_key)
/

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
/

PROMPT Check for PA projects having schedule gaps
SELECT DISTINCT tmp2.project_id, 
                tmp2.assignment_id,
                tmp2.assignment_name,
                tmp2.resource_id,
                r.name
FROM 
       ( SELECT assg.project_id, 
                assg.assignment_id ,
                assg.assignment_name,
                assg.resource_id, 
                sch.schedule_id , 
                sch.start_date, 
                sch.end_date, 
                LAG(TRUNC(sch.end_date)) OVER 
                   (PARTITION BY assg.assignment_id ORDER BY 
                    TRUNC(sch.start_date)) "LAG_DATE" 
         FROM apps.pa_project_assignments assg , 
              apps.pa_schedules sch 
         WHERE assg.assignment_id = sch.assignment_id 
         ORDER BY assg.assignment_id ,TRUNC(sch.start_date) 
        ) tmp2,
        apps.pa_resources r 
WHERE ( (trunc(start_date) > trunc(END_DATE)) 
OR      (trunc(start_date) - trunc(LAG_DATE)) > 1 )
AND   r.resource_id(+) = tmp2.resource_id
UNION 
SELECT assig1.project_id, 
       assig1.assignment_id,
       assig1.assignment_name,
       assig1.resource_id,
       r.name
FROM 
       (SELECT sche.project_id, 
               sche.assignment_id ,
               min(sche.Start_date) minsd , 
               max(sche.end_date) maxed 
        FROM   apps.pa_schedules sche 
        GROUP BY sche.project_id, 
                 sche.assignment_id) temp1 , 
        apps.pa_project_assignments assig1 ,
        apps.pa_resources r
WHERE assig1.assignment_id = temp1.assignment_id 
AND   (TRUNC(assig1.start_date) <> TRUNC(temp1.minsd) 
OR    TRUNC(assig1.end_date) <> TRUNC(temp1.maxed))
AND   r.resource_id(+) = assig1.resource_id;
/

PROMPT Check for stuck rows in workflow PARMATRX 

SELECT  COUNT(*)
FROM    apps.pa_wf_process_details
WHERE   item_type='PARMATRX'
AND     object_id1=0
/

PROMPT Find problem where the customer on an agreement was changed after
PROMPT funding and invoice generation. 

SELECT DISTINCT
       ppf.project_id,
       paa.customer_id "Agreements CUSTOMER ID",
       ppc.customer_id "Project Customer CUSTOMER_ID"
FROM   apps.pa_project_fundings ppf,
       apps.pa_agreements_all paa,
       apps.pa_projects_all ppa,
       apps.pa_project_customers ppc,
       apps.pa_project_statuses pps
WHERE  ppc.project_id = ppa.project_id
AND    ppa.project_id = ppf.project_id
AND    paa.agreement_id = ppf.agreement_id
AND    paa.customer_id != ppc.customer_id
AND    ppa.project_status_code = pps.project_status_code
AND    pps.status_type = 'PROJECT'
AND    pps.project_system_status_code !=  'CLOSED'
AND    ppc.customer_bill_split > 0
ORDER BY 1
/

-- Module: HR and OTL 
PROMPT Audit Check for OTL preferences (TimeCard Status Allowing Edits)
PROMPT This is should always be 2 unless there is an early timecard deadline
SELECT hfhv.parent_pref_hierarchy bg_name 
,      hfhv.attribute6 past_days
FROM  apps.hxc_pref_hierarchies_v hfhv
WHERE hfhv.pref_hierarchy 
      = 'Timecard Status Allowing Edits'
AND   hfhv.attribute6 < 2
/

--   End Module Specific Section

--   Std RDBMS Checks Section

PROMPT Watch for HighWaterMark higher than 1000 since may need to incr processes
PROMPT

SELECT SESSIONS_HIGHWATER 
  FROM   v$license                                              sesshighwater
where SESSIONS_HIGHWATER > 1000
/

-- Check for datafiles that have autoextended to their max so stuck
column bytes        format 999999999
column file_name    format a35
column TSPACE       format a12
prompt  
prompt Datafiles with autoextend ON that have reached their max so STUCK
select tablespace_name TSPACE 
,      file_name 
,      bytes/1048576 file_size 
,      maxbytes / 1048576 maxsize_MB  -- convert to MB
from   dba_data_files
where autoextensible = 'YES'
and bytes >= maxbytes  -- datafile already extended to max
order by tablespace_name
/

--   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 > 8600   -- greater than 8600 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
-- See SharePoint Invalid Object Log for details about these objects
PROMPT Invalid APPS objects, at least 6: FND_OID_DIAG FND_TS_SIZE and 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
/

-- Add any custom schemas here
PROMPT Invalid objects owned by custom schema XXFI Account
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 audit rows       
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
--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 Users whose FND Login Account password has become INVALID (11.5.10.2)
PROMPT and whose account is not end dated
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
AND    end_date is null  -- only look at active logins in this query
ORDER BY user_name
/

PROMPT Non-Terminated people with end dated fnd_user plus INVALID Password
PROMPT We assume that locked users should not be both end dated and INVALID
SELECT DISTINCT(fu.user_name)
, ENCRYPTED_USER_PASSWORD encrypted_password
, fu.end_date
FROM apps.fnd_user fu
,    apps.per_all_people_f papf
WHERE fu.encrypted_user_password = 'INVALID'      -- value when user is blocked
AND   fu.end_date IS NOT NULL
AND   fu.employee_id = papf.person_id
AND   papf.current_employee_flag = 'Y'
AND   trunc(sysdate) between papf.effective_start_date 
                         and papf.effective_end_date
/

--     Profiles Section: 

PROMPT PROFILES Section: Check Selected PROFILE Settings:
PROMPT
PROMPT List Site level Profiles that have changed in last Month

COLUMN application_id on heading app_id FORMAT 99999
COLUMN profile_option_id on heading 'opt|id' FORMAT 9999
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 Site Level Profile Settings
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 level_id on heading 'level|id' 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: Advanced Supply Chain Planning
PROMPT We run Purge ATP Temp Tables but it leaves rows with null creation_date
PROMPT Need to truncate mrp.mrp_atp_schedule_temp periodically when too many
PROMPT rows due to the null problem.
SELECT 
     COUNT(*)
FROM mrp.mrp_atp_schedule_temp 
/

PROMPT Module: AOL
PROMPT We run Purge FND_STATS History Records but it leaves some rows.
PROMPT Need to truncate apps.fnd_stats_hist periodically when too many rows
SELECT 
     COUNT(*)
FROM apps.fnd_stats_hist        
/

PROMPT End Module specific Checks Section
PROMPT 

PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PROMPT ++  End of ck_apps_health.sql Logic 
PROMPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SPOOL OFF

CLEAR COLUMNS
EXIT
