REM $Header: BOMCHECK.sql 115.0 2002/1/25 18:31:27 skagarwa noship $
REM +=======================================================================+
REM |    Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA     |
REM |                         All rights reserved.                          |
REM +=======================================================================+
REM | FILENAME                                                              |
REM |     BOMCHECK.sql                                                      |
REM |                                                                       |
REM | DESCRIPTION                                                           |
REM |     This  script  can  be run to                                      |
REM |     gather information about an application for diagnostic            |
REM |     purposes.                                                         |
REM |                                                                       |
REM | NOTES                                                                 |
REM |     The script takes application id(s) as inputs  and then            |
REM |     generates a report for those applications.                        |
REM |                                                                       |
REM | HISTORY                                                               |
REM |     18-APR-2003              SKAGARWA              CREATED            | 
REM |                                                                       |
REM +=======================================================================+

clear buffer;

set heading on
set verify off
set feed off
set linesize 80
set pagesize 5000
set underline '='
set serveroutput on size 1000000

spool bomcheck.txt

prompt
accept 2 char prompt 'Please Enter Name of the Customer Running the Script : '
prompt

col app_name format a50 heading 'Application Name' ;
col app_s_name format a8 heading 'Short|Name' ;
col inst_status format a10 heading 'Installed?' ;
col app_id format 9990 heading 'Appl Id' ;

prompt
prompt Application Installation Details :
select 
  fav.application_name app_name
, fav.application_short_name app_s_name
, decode(fpi.status, 'I', 'Yes', 
                     'S', 'Shared', 
                     'N', 'No', fpi.status) inst_status
, fav.application_id app_id
from fnd_application_vl fav, fnd_product_installations fpi
where fav.application_id = fpi.application_id
order by 3;

prompt
prompt IMPORTANT: Please read ...
prompt
prompt ******************************************************************************
prompt
prompt By default this script generates  Diagnostic Report only for INV, ENG and
prompt BOM . Enter up to Five(5) additional Application Id(s) from above 
prompt list of Installed products. Or enter Application Id(s) from the list below of
prompt most common applications for which this report is generated.
prompt
prompt Application Name          Short Name Application Id
prompt ========================= ========== ==============
prompt Oracle Work In Progress   WIP                   706
prompt Oracle Configurator       CZ                    708
prompt Oracle Order Management   OM                    660
prompt Oracle Shipping           WSH                   665
prompt Oracle Pricing            QP                    661
prompt ******************************************************************************

prompt
accept 3 char prompt 'Enter Additional Application Id # 1 (See Above) : '
accept 4 char prompt 'Enter Additional Application Id # 2 (See Above) : '
accept 5 char prompt 'Enter Additional Application Id # 3 (See Above) : '
accept 6 char prompt 'Enter Additional Application Id # 4 (See Above) : '
accept 7 char prompt 'Enter Additional Application Id # 5 (See Above) : '
prompt

prompt ***************** Oracle Order Management Diagnostics Report *****************
prompt
prompt Diagnostic report generated for Customer &2.
prompt
prompt ******************************************************************************

col dtime format a25 heading 'Script run at Date/Time' ;
select to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') dtime from dual;

col db format a9 heading 'DB Name';
col created format a9 heading 'Created';

prompt
prompt 1. Database Name and Created Date :
select 
  name db
, created 
from V$DATABASE;

col ver format a64 heading 'Oracle RDBMS/Tools Version(s)';

prompt
prompt 2. Oracle Version(s) : 
select 
  banner ver 
from V$VERSION;

col parameter format a30 heading 'NLS Parameter Name';
col param_value format a45 heading 'Currently Set Value';

prompt
prompt 3. NLS Parameter Settings :
select 
  parameter
, value param_value 
from nls_session_parameters;

set linesize 120

prompt
prompt 4. Profile Option Values :
prompt

declare
  l_appl_id3  number := to_number(nvl('&3', -1));
  l_appl_id4  number := to_number(nvl('&4', -1));
  l_appl_id5  number := to_number(nvl('&5', -1));
  l_appl_id6  number := to_number(nvl('&6', -1));
  l_appl_id7  number := to_number(nvl('&7', -1));

  l_user_id   varchar2(255);
  l_user_name varchar2(255);
  l_resp_id   varchar2(255);
  l_resp_name varchar2(255);
  l_appl_id   number := -1;
  l_pov       varchar2(60);
  l_lvl       varchar2(10);
  
  cursor profile_options
  is
  select 
    fpo.application_id
  , fpo.profile_option_id poi
  , substr(fpo.user_profile_option_name, 1, 60) upon
  from  fnd_profile_options_vl fpo
  where fpo.application_id in (401, 702, 703, l_appl_id3, l_appl_id4, l_appl_id5, l_appl_id6, l_appl_id7)
  and   fpo.start_date_active <= sysdate
  and  (nvl(fpo.end_date_active,sysdate) >= sysdate)
  order by fpo.application_id, fpo.user_profile_option_name;

  cursor profile_values(c_appl_id  number, c_po_id  number)
  is
  select 
    substr(fpov.profile_option_value, 1, 52) pov
  , decode(fpov.level_id, 10001, 'Site', 10002, 'Appl', 10003, 'Resp', 10004, 'User', 'None') lvl
  from  fnd_profile_option_values fpov
  where fpov.application_id    = c_appl_id
  and   fpov.profile_option_id = c_po_id
  and ((fpov.level_id = 10001 and fpov.level_value = 0)
   or  (fpov.level_id = 10002 and fpov.level_value = c_appl_id)
   or  (fpov.level_id = 10003 and fpov.level_value_application_id = c_appl_id and fpov.level_value = to_number(l_resp_id))
   or  (fpov.level_id = 10004 and fpov.level_value = to_number(l_user_id)))
  order by fpov.level_id desc ;

  cursor appl_name(c_appl_id  number)
  is
  select 
    substr(application_name, 1, 60) application_name
  from fnd_application_vl
  where application_id = c_appl_id;

begin

  -- Get the User Id/Name, Responsibility Id/Name.
  --
  fnd_profile.get('USER_ID', l_user_id);
  fnd_profile.get('USER_NAME', l_user_name);
  fnd_profile.get('RESP_ID', l_resp_id);
  fnd_profile.get('RESP_NAME', l_resp_name);

  dbms_output.put_line('Logged in as user '||l_user_name||'(Id : '||l_user_id||') with responsibility '||l_resp_name||'(Id : '||l_resp_id||')');

  for rec1 in profile_options loop
    -- if application has changed then change the header.
    if rec1.application_id != l_appl_id then
      for rec2 in appl_name(rec1.application_id) loop
        dbms_output.put_line(chr(10)||'=====================================================================================================');
        dbms_output.put_line('Profile Option Values listing for Application : '||rec2.application_name);
        dbms_output.put_line('=====================================================================================================');
        dbms_output.put_line(chr(10)||'User Profile Option Name                            Profile Option Value                                 Set At');
        dbms_output.put_line('============================================================ ==================================================== ======');
      end loop;
      l_appl_id := rec1.application_id;
    end if;

    open profile_values(rec1.application_id, rec1.poi);

    fetch profile_values into l_pov, l_lvl;

    if profile_values%notfound then
      l_pov := '** Not Set At Any Level **';
      l_lvl := '----';

    end if;
    
    close profile_values;

    dbms_output.put_line(rpad(rec1.upon, 60)||' '||rpad(l_pov, 52)||' '||rpad(l_lvl, 6));

  end loop;
  
end;
/

col owner format a5 heading 'Owner';
col table_owner format a5 heading 'Table|Owner';
col table_name format a30 heading 'Table Name';
col trigger_name format a30 heading 'Trigger Name';
col trigger_type format a16 heading 'Trigger Type';
col triggering_event format a26 heading 'Triggering Event';
col status format a8 heading 'Status';

prompt
prompt 5. Database Triggers :

select
  atrg.table_owner
, atrg.table_name
, atrg.trigger_name
, atrg.trigger_type
, atrg.triggering_event
, atrg.status
from  all_triggers atrg, fnd_application fa
where fa.application_id in (401, 702, 703, to_number(nvl('&3', -1)), to_number(nvl('&4', -1)), to_number(nvl('&5', -1)), to_number(nvl('&6', -1)), to_number(nvl('&7', -1)))
and   atrg.table_owner  = fa.application_short_name
order by atrg.table_owner, atrg.table_name, atrg.trigger_type;


col index_name format a30 heading 'Index Name';
col index_type format a12 heading 'Index Type';
                                         
prompt
prompt 6. Table Indexes :

select
  aind.table_owner
, aind.table_name
, aind.index_name
, aind.index_type
, aind.status 
from  all_indexes aind, fnd_application fa
where fa.application_id in (401, 702, 703, to_number(nvl('&3', -1)), to_number(nvl('&4', -1)), to_number(nvl('&5', -1)), to_number(nvl('&6', -1)), to_number(nvl('&7', -1)))
and   aind.table_owner = fa.application_short_name 
order by aind.table_owner, aind.table_name, aind.index_name;

set linesize 120

prompt
prompt 7. Package Versions :

declare
  type PkgCurType IS REF CURSOR;
  l_pkg_cursor    PkgCurType;
  l_query         varchar2(10000);
  l_where         varchar2(2000);
  l_name          varchar2(30);
  l_type          varchar2(4);
  l_file_name     varchar2(20);
  l_version       varchar2(25);
  l_status        varchar2(7);

  cursor pkg_prefix
  is
  select distinct Nvl(substr(o.name, 1, instr(o.name, '_')),'NULL_')||'%' prefix
  from   sys.obj$ o, sys.tab$ t, sys.user$ u 
  where  u.name in
        (select fa.application_short_name
         from   fnd_application fa
         where  fa.application_id in (401, 702, 703, to_number(nvl('&3', -1)), to_number(nvl('&4', -1)), to_number(nvl('&5', -1)), to_number(nvl('&6', -1)), to_number(nvl('&7', -1))))
  and    o.owner# = u.user#
  and    t.obj#   = o.obj#;

begin

  l_query := 'select 
                o.name
              , ltrim(rtrim(substr(substr(s.source, instr(s.source,''Header: '')), instr(substr(s.source, instr(s.source,''Header: '')), '' '', 1, 1), instr(substr(s.source, instr(s.source,''Header: '')), '' '', 1, 2) - instr(substr(s.source, instr(s.source,''Header: '')), '' '', 1, 1) ))) file_name
              , ltrim(rtrim(substr(substr(s.source, instr(s.source,''Header: '')), instr(substr(s.source, instr(s.source,''Header: '')), '' '', 1, 2), instr(substr(s.source, instr(s.source,''Header: '')), '' '', 1, 3) - instr(substr(s.source, instr(s.source,''Header: '')), '' '', 1, 2) ))) file_version
              , decode(o.type#, 9, ''SPEC'', 11, ''BODY'', o.type#) type
              , decode(o.status, 0, ''N/A'', 1, ''VALID'', ''INVALID'') status
              from  sys.source$ s, sys.obj$ o, sys.user$ u
              where u.name   = ''APPS''
              and   o.owner# = u.user#
              and   s.obj#   = o.obj#
              and   s.line between 2 and 5
              and   s.source like ''%Header: %''';

  for pkg_prefix_rec in pkg_prefix loop
    if l_where is null then
      l_where := 'and ( o.name like '''||pkg_prefix_rec.prefix||'''';
    else
      l_where := l_where||' or  o.name like '''||pkg_prefix_rec.prefix||'''';
    end if;
  end loop;

  if l_where is not null then
    l_where := l_where||')';
    l_query := l_query||l_where;
  end if;

  l_query := l_query||' order by 1, 4';

  dbms_output.put_line('Name                           File Name            Version         Type Status ');
  dbms_output.put_line('============================== ==================== =============== ==== =======');
  open l_pkg_cursor for l_query;
  loop
    fetch l_pkg_cursor into l_name, l_file_name, l_version, l_type, l_status;
    exit when l_pkg_cursor%notfound;
    dbms_output.put_line(rpad(l_name, 30)||' '||rpad(l_file_name, 18)||' '||rpad(l_version, 18)||' '||rpad(l_type, 4)||' '||rpad(l_status, 7));
  end loop;
end;
/

spool off

set head off
set lines 120

prompt Searching Product Tops, Please wait ...

host echo '' >> bomcheck.txt
host echo '8. Product Tops :' >> bomcheck.txt
host echo '' >> bomcheck.txt

spool comm.lst
  select 'echo $'||basepath prod_top
  from   fnd_application
  where  application_id  in (401, 702, 703, to_number(nvl('&3', -1)), to_number(nvl('&4', -1)), to_number(nvl('&5', -1)), to_number(nvl('&6', -1)), to_number(nvl('&7', -1)));
spool off;

host chmod 777 comm.lst
host comm.lst >> bomcheck.txt

prompt Searching Product Forms, Please wait ...

host echo '' >> bomcheck.txt
host echo '9. Form Versions :' >> bomcheck.txt
host echo '' >> bomcheck.txt

spool comm.lst
  select 'strings -a $'||basepath||'/forms/US/*.fmx | grep ''$Header: ''' 
  from   fnd_application
  where  application_id  in (401, 702, 703, to_number(nvl('&3', -1)), to_number(nvl('&4', -1)), to_number(nvl('&5', -1)), to_number(nvl('&6', -1)), to_number(nvl('&7', -1)));
spool off

host chmod 777 comm.lst
host comm.lst >> bomcheck.txt

prompt Searching Product Libraries, Please wait ...

host echo '' >> bomcheck.txt
host echo '10. Library Versions :' >> bomcheck.txt
host echo '' >> bomcheck.txt

spool comm.lst
  select 'strings -a $AU_TOP/resource/'||decode(application_short_name, 'ONT', 'OE', application_short_name)||'*.plx | grep ''$Header: ''' 
  from   fnd_application
  where  application_id  in (401, 702, 703, to_number(nvl('&3', -1)), to_number(nvl('&4', -1)), to_number(nvl('&5', -1)), to_number(nvl('&6', -1)), to_number(nvl('&7', -1)));
spool off

host chmod 777 comm.lst
host comm.lst >> bomcheck.txt

prompt Searching Product Reports, Please wait ...

host echo '' >> bomcheck.txt
host echo '11. Reports Versions :' >> bomcheck.txt
host echo '' >> bomcheck.txt

spool comm.lst
  select 'strings -a $'||basepath||'/reports/US/*.rdf | grep ''$Header: '''
  from   fnd_application
  where  application_id  in (401, 702, 703, to_number(nvl('&3', -1)), to_number(nvl('&4', -1)), to_number(nvl('&5', -1)), to_number(nvl('&6', -1)), to_number(nvl('&7', -1)));
spool off

host chmod 777 comm.lst
host comm.lst >> bomcheck.txt

prompt Searching Listerner.ora, Please wait ...

host echo '' >> bomcheck.txt
host echo '12. Contents of Listener.ora :' >> bomcheck.txt
host echo '' >> bomcheck.txt

spool comm.lst
host cat $ORACLE_HOME/network/admin/listener.ora >> bomcheck.txt
spool off

host chmod 777 comm.lst
host cat comm.lst >> bomcheck.txt
host comm.lst >> bomcheck.txt

set head on
prompt Show Item Attribute Controls ...

host echo '' >> bomcheck.txt
host echo '13. Item Attribute Controls :' >> bomcheck.txt
host echo '' >> bomcheck.txt

spool comm.lst
  select attribute_name,control_level,status_control_code
  from   mtl_item_attributes
  order by attribute_name ;
spool off;

host chmod 777 comm.lst
host cat comm.lst >> bomcheck.txt

prompt Show organization list ...

host echo '' >> bomcheck.txt
host echo '14. Organization list :' >> bomcheck.txt
host echo '' >> bomcheck.txt

spool comm.lst
  select organization_code,organization_id,master_organization_id
  from   mtl_parameters
  order by master_organization_id,organization_id ;
spool off;

host chmod 777 comm.lst
host cat comm.lst >> bomcheck.txt

prompt ENABLED SYSTEM ITEMS SEGMENTS ...

host echo '' >> bomcheck.txt
host echo '15. System Items Segment list :' >> bomcheck.txt
host echo '' >> bomcheck.txt

spool comm.lst
  select segment_num SEGMENT_NUMBER,SEGMENT_NAME ,application_column_name COLUMN_NAME, REQUIRED_FLAG
  from   fnd_id_flex_segments_vl
  where application_id = 401
  and id_flex_code = 'MSTK'
  and id_flex_num = 101
  and enabled_flag = 'Y'
  order by segment_num ;
spool off;

host chmod 777 comm.lst
host cat comm.lst >> bomcheck.txt

prompt ENABLED STOCK LOCATORS SEGMENTS... 

host echo '' >> bomcheck.txt
host echo '16. Stock Locators Segment list :' >> bomcheck.txt
host echo '' >> bomcheck.txt

spool comm.lst
  select segment_num SEGMENT_NUMBER,SEGMENT_NAME ,application_column_name COLUMN_NAME, REQUIRED_FLAG
  from   fnd_id_flex_segments_vl
  where application_id = 401
  and id_flex_code = 'MTLL'
  and id_flex_num = 101
  and enabled_flag = 'Y'
  order by segment_num ;
spool off;

host chmod 777 comm.lst
host cat comm.lst >> bomcheck.txt

prompt Show Languages list ...

host echo '' >> bomcheck.txt
host echo '17. Language list :' >> bomcheck.txt
host echo '' >> bomcheck.txt

spool comm.lst
  select LANGUAGE_CODE,INSTALLED_FLAG
  from   fnd_languages
  order by INSTALLED_FLAG;
spool off;

host chmod 777 comm.lst
host cat comm.lst >> bomcheck.txt

host echo '' >> bomcheck.txt
host echo 'Report completed at Date/Time' >> bomcheck.txt
host date '+%d/%m/%y %H:%M:%S' >> bomcheck.txt
host echo '' >> bomcheck.txt
host echo 'End of Diagnostic Report' >> bomcheck.txt
host echo '' >> bomcheck.txt

prompt
prompt Diagnostic Report bomcheck.txt is now available in user's Current Directory.
prompt

exit;

