Thursday, August 1, 2013

OATC StreetSmarts(R) book series is here! Checkout http://www.oatcinc.com/index_files/Blinks/OATCStreetSmartsBook1.pdf, or click on the link to the right, for the first book in the series "What Scripts". As Michael noted below, these are helpful scripts for your DBA and EBS Applications team. Check out the book and let us know what you think!

Friday, June 21, 2013

what scripts – what are they all about?


The “what scripts” are a series of operating system level scripts that interrogate the Oracle E-Business suite internal tables for information not easily obtained through other means. These scripts were created to provide information quickly, accurately, and in an easy to read format.

The “what scripts” are meant to assist System Administrators, Database Administrators, or really any IS/IT personnel looking for specific information such as what services are currently running to what users are logged in or what Concurrent Requests/Concurrent-Managers are busy or idle. There are a wide variety of scripts found within this book. Please review the table of contents and appendixes to find a “what” script that best meets your needs.

whatproductinfo


What EBusiness Suite Application Products are Installed/Shared/Inactive or Pseudo.


#---------------------------------------------------------------#
#- What (whatproductinfo)                                      -#
#---------------------------------------------------------------#

                 OATC (oratest) Apps Product Information
                         EBusiness Suite

App
Short                                                     Install
Name     Application  Name                                Status
-------- ------------------------------------------------ --------------
AD       Applications DBA                                 Shared
ADO      Oracle Applications Patch Wizard                 Pseudo
ADX      Rapid Install                                    Pseudo
AHL      Complex Maintenance Repair and Overhaul          Inactive
AK       Common Modules-AK                                Installed
ALR      Alert                                            Installed
AME      Approvals Management                             Inactive
AML      Leads Management                                 Pseudo
AMS      Marketing                                        Shared
AMV      Marketing Encyclopedia System                    Shared
.
.
.
XDO      XML Publisher                                    Installed
XDP      Provisioning                                     Inactive
XLA      Subledger Accounting                             Shared
XLE      Legal Entity Configurator                        Inactive
XLH      Financial Services Accounting Hub                Pseudo
XNB      Oracle Telecommunications Billing Integrator     Inactive
XNP      Number Portability                               Inactive
XTR      Treasury                                         Inactive
ZFA      Financial Analyzer                               Inactive
ZPB      Enterprise Planning and Budgeting                Inactive
ZSA      Sales Analyzer                                   Inactive
ZX       E-Business Tax                                   Inactive




#!/usr/bin/ksh
# whatproductinfo ()
#-----------------------------------------------------------------------#
#    Module Name:  whatproductinfo  (c) Copyright 2012                  #
#                                                                       #
#    Purpose:      Report on the Oracle eBusiness Suite Products.       #
#                                                                       #
#    Maintenance                                                        #
#    Date          Author        Description                            #
#    -----------   ------------  -------------------------------------  #
#    10-May-2012   B.Matthews    Module design/creation                 #
#-----------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
       
        clear
        echo
        echo  " #------------------------------------------------------#"
        echo  " # whatproductinfo:           Oracle eBusiness Products #"
        echo  " #------------------------------------------------------#"
        echo

        #---------------------------------------------------------------#
        #  Trap:  Set CNTL-C CNTL-D CNTL-\ Before Password Protection   #
        #---------------------------------------------------------------#
        trap        "stty echo; return"     2
        trap        "stty echo; return"     3
        stty -echo

        read PASSWD?" Please Enter the Oracle APPS Passwd: "
           
        stty echo
        echo
        #---------------------------------------------------------------#
        #  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection  #
        #---------------------------------------------------------------#
        trap        2
        trap        3

        clear
        ORANAM=${ORASID}

#-----------------------------------------------------------------------#
#  Initialize                                                           #
#-----------------------------------------------------------------------#
        SPOOLNM=/tmp/whatproductinfo_$$.txt

#-----------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                           #
#-----------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE

        CONNECT  APPS/$PASSWD

        SPOOL ${SPOOLNM}

        clear   BREAKS
        clear   BUFFER
        clear   COLUMNS
        clear   COMPUTES
        clear   SCREEN
        clear   SQL
        clear   TIMING

        set     feedback        off
        set     flush           on
        set     heading         on
        set     pause           off
        set     space           1
        set     termout         on
        set     verify          on
        set     linesize        80
        set     pagesize        999

        prompt #--------------------------------------------------------#
        prompt #- What (whatproductinfo)                               -#
        prompt #--------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  sname  format A8  HEADING 'App|Short|Name'    WORD_WRAPPED
        COLUMN  aname  format A48 HEADING 'Application  Name' WORD_WRAPPED
        COLUMN  stats  format A14 HEADING 'Install|Status'    WORD_WRAPPED


        TTITLE  CENTER  '${ORASID} (${HNAME}) Apps Product Info' skip 1 -
                CENTER  'eBusiness Suite'                        skip 2

        alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; 

        SELECT  UPPER(product_abbreviation)     sname,
                product_name                    aname,
                'Pseudo'                        stats
          FROM  ad_pm_product_info
        WHERE   pseudo_product_flag <> 'N'
        UNION
        SELECT decode(nvl(a.APPLICATION_short_name,'Not Found'),
                'SQLAP','AP','SQLGL','GL','OFA','FA',
                'Not Found','id '||to_char(fpi.application_id),
                a.APPLICATION_short_name)       sname,
                b.application_name              aname,
                decode(fpi.status,'I','Installed','S','Shared',
               'N','Inactive',fpi.status)       stats
        FROM    fnd_oracle_userid o,
                fnd_application a,
                fnd_product_installations fpi,
                fnd_application_tl b
        WHERE   fpi.application_id = a.application_id(+)
          AND   fpi.oracle_id = o.oracle_id(+)
          AND    b.application_id=a.application_id
        ORDER BY sname;

        EXIT;
END_FILE

print
print "Please see /tmp/whatproductinfo_$$.txt"
print
}

whatinstalled


What EBusiness Suite Application Products are Installed/Not-Installed/Shared.


#---------------------------------------------------------------#
#- What (whatinstalled)                                        -#
#---------------------------------------------------------------#

10-JUN-13          OATC (oratest) Apps Installed Products
21:50:21                  EBusiness Suite

App
Short                                                     Install
Name     Application  Name                                Status
-------- ------------------------------------------------ --------------
AD       Applications DBA                                 Shared
AHL      Complex Maintenance Repair and Overhaul          Not Installed
AK       Common Modules-AK                                Installed
ALR      Alert                                            Installed
AME      Approvals Management                             Not Installed
AMS      Marketing                                        Shared
AMV      Marketing Encyclopedia System                    Shared
AMW      Internal Controls Manager                        Not Installed
AR       Receivables                                      Installed
AS       Sales Foundation                                 Installed
ASF      Sales Online                                     Not Installed
ASG      CRM Gateway for Mobile Devices                   Not Installed
.
.
.
XDO      XML Publisher                                    Installed
XDP      Provisioning                                     Not Installed
XLA      Subledger Accounting                             Shared
XLE      Legal Entity Configurator                        Not Installed
XNB      Oracle Telecommunications Billing Integrator     Not Installed
XNP      Number Portability                               Not Installed
XTR      Treasury                                         Not Installed
ZFA      Financial Analyzer                               Not Installed
ZPB      Enterprise Planning and Budgeting                Not Installed
ZSA      Sales Analyzer                                   Not Installed
ZX       E-Business Tax                                   Not Installed




#!/usr/bin/ksh
# whatinstalled ()
#-----------------------------------------------------------------------#
#    Module Name:  whatinstalled  (c) Copyright 2012                    #
#                                                                       #
#    Purpose:      Report on the eBusiness Suite Installed Products.    #
#                                                                       #
#    Maintenance                                                        #
#    Date          Author        Description                            #
#    -----------   ------------  -------------------------------------  #
#    10-May-2012   M.Barone      Module design/creation                 #
#-----------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
       
        clear
        echo
        echo  " #------------------------------------------------------#"
        echo  " # whatinstalled:  Oracle eBusiness Installed Products  #"
        echo  " #------------------------------------------------------#"
        echo

        #---------------------------------------------------------------#
        #  Trap:  Set CNTL-C CNTL-D CNTL-\ Before Password Protection   #
        #---------------------------------------------------------------#
        trap        "stty echo; return"     2
        trap        "stty echo; return"     3
        stty -echo

        read PASSWD?" Please Enter the Oracle _[7mAPPS_[0m Passwd: "
           
        stty echo
        echo
        #---------------------------------------------------------------#
        #  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection  #
        #---------------------------------------------------------------#
        trap        2
        trap        3

        clear
        ORANAM=${ORASID}

#-----------------------------------------------------------------------#
#  Initialize                                                           #
#-----------------------------------------------------------------------#
        SPOOLNM=/tmp/whatinstalled_$$.txt
       
#-----------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                           #
#-----------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE

        CONNECT  APPS/$PASSWD

        SPOOL ${SPOOLNM}

        clear   BREAKS
        clear   BUFFER
        clear   COLUMNS
        clear   COMPUTES
        clear   SCREEN
        clear   SQL
        clear   TIMING

        set     feedback        off
        set     flush           on
        set     heading         on
        set     pause           off
        set     space           1
        set     termout         on
        set     verify          on
        set     linesize        80
        set     pagesize        999

        prompt #--------------------------------------------------------#
        prompt #- What (whatinstalled)                                 -#
        prompt #--------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  sname  format A8  HEADING 'App|Short|Name'    WORD_WRAPPED
        COLUMN  aname  format A48 HEADING 'Application  Name' WORD_WRAPPED
        COLUMN  stats  format A14 HEADING 'Install|Status'    WORD_WRAPPED


        TTITLE  CENTER  '${ORASID} (${HNAME}) Apps Installed Products '  -
                LEFT    DATEVAR                                  skip 1  -
                CENTER  'eBusiness Suite'                                -
                LEFT    TIMEVAR                                  skip 2

        alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
  
        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
                application_short_name          sname,
                application_name                aname,
                DECODE(status,  'I', 'Installed',
                                'S', 'Shared',
                                'N', 'Not Installed',
                        status)                 stats
        FROM    fnd_application_vl fa,
                fnd_product_installations fpi
        WHERE   fpi.application_id = fa.application_id
        ORDER BY 1;

        EXIT;
END_FILE

print
print "Please see /tmp/whatinstalled_$$.txt"
print
}